Database Warnings Confusion

[movieseals]

OK.  After a lot of trials and errors, I was able to migrate from 1.3 to 1.5.  Really impressed with my tests on 1.5, especially the server cache, which is what finally made me want to make the jump despite the potential upheaval.  It took me a while but I figured out a proper way now to migrate relatively easily.

My question now is all the warnings about the DB.  I had tons.  Some are due to modules and I am aware of those.  Some are due to our customizations, I am aware of those as well.  But some are puzzling, as I always had these functionalities before so I am not sure if they are modules related or just stuff that got dropped along the way.


 

I fixed everything I knew about.  Some above are modules related.  But some I have no clue if they were there before and no longer useful or remnants of modules we no longer use, etc.

I will list below the ones that are still puzzling me:

- Extra column id_last_order in table tb_employee.
- Extra column id_last_customer_message in table tb_employee.

- Extra column id_last_customer in table tb_employee.

I believe those are from a module we no longer use but I am not 100% sure.  Just want to know if it is safe to fix as I no longer have a module that targets employees.

- Extra column order_note in table tb_orders.
Always had this functionality as far as I can remember.  So not sure about this one.

- Extra column updated_var_price in table tb_product and Extra column final_price in table tb_product_shop.

I thought that was in the older TB database.  Does not seem connected to any modules.

And this one:

- Extra column hide_host_mode in table tb_tab.

I have no idea what this one if for.

Any help is appreciated.
 

 

Edited November 1, 2023 by movieseals
Typos

[datakick]

45 minutes ago, movieseals said:

I will list below the ones that are still puzzling me:

• Extra column id_last_order in table tb_employee.
• Extra column id_last_customer_message in table tb_employee.
• Extra column id_last_customer in table tb_employee.
• Extra column hide_host_mode in table tb_tab.

These can be safely removed - remnants of core functionality that is no longer used.

45 minutes ago, movieseals said:

• Extra column order_note in table tb_orders.
• Extra column updated_var_price in table tb_product
• Extra column final_price in table tb_product_shop.

No idea, probably some module columns. Try to search in modules and overrides directory for these column names. If nothing comes up, you can probably safe to remove them.

If these columns have some default value, then it's also safe to keep them in the database table.

If they don't have default value, then they can cause troubles during data insertion. 

You can check for by looking at tb structure:

show create table tb_orders;

outputs something like

CREATE TABLE `tb_orders` (
  `id_order` int unsigned NOT NULL AUTO_INCREMENT,
  `reference` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `id_shop_group` int unsigned NOT NULL DEFAULT '1',
  `id_shop` int unsigned NOT NULL DEFAULT '1',
  `id_carrier` int unsigned NOT NULL,
  `id_lang` int unsigned NOT NULL,
  `id_customer` int unsigned NOT NULL,
  `id_cart` int unsigned NOT NULL,
  `id_currency` int unsigned NOT NULL,
  `id_address_delivery` int unsigned NOT NULL,
  `id_address_invoice` int unsigned NOT NULL,
  `current_state` int unsigned NOT NULL,
  `secure_key` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '-1',
  `payment` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `conversion_rate` decimal(13,6) NOT NULL DEFAULT '1.000000',
  `module` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `recyclable` tinyint unsigned NOT NULL DEFAULT '0',
  `gift` tinyint unsigned NOT NULL DEFAULT '0',
  `gift_message` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `mobile_theme` tinyint(1) NOT NULL DEFAULT '0',
  `shipping_number` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `total_discounts` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `total_discounts_tax_incl` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `total_discounts_tax_excl` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `total_paid` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `total_paid_tax_incl` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `total_paid_tax_excl` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `total_paid_real` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `total_products` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `total_products_wt` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `total_shipping` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `total_shipping_tax_incl` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `total_shipping_tax_excl` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `carrier_tax_rate` decimal(10,3) NOT NULL DEFAULT '0.000',
  `total_wrapping` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `total_wrapping_tax_incl` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `total_wrapping_tax_excl` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `round_mode` tinyint(1) NOT NULL DEFAULT '2',
  `round_type` tinyint(1) NOT NULL DEFAULT '1',
  `invoice_number` int unsigned NOT NULL DEFAULT '0',
  `delivery_number` int unsigned NOT NULL DEFAULT '0',
  `invoice_date` datetime NOT NULL,
  `delivery_date` datetime NOT NULL,
  `valid` int unsigned NOT NULL DEFAULT '0',
  `date_add` datetime NOT NULL,
  `date_upd` datetime NOT NULL,
  PRIMARY KEY (`id_order`),
  KEY `current_state` (`current_state`),
  KEY `date_add` (`date_add`),
  KEY `id_address_delivery` (`id_address_delivery`),
  KEY `id_address_invoice` (`id_address_invoice`),
  KEY `id_carrier` (`id_carrier`),
  KEY `id_cart` (`id_cart`),
  KEY `id_currency` (`id_currency`),
  KEY `id_customer` (`id_customer`),
  KEY `id_lang` (`id_lang`),
  KEY `id_shop` (`id_shop`),
  KEY `id_shop_group` (`id_shop_group`),
  KEY `invoice_number` (`invoice_number`),
  KEY `reference` (`reference`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

 As long as there is DEFAULT at the column definition, it's fine.

 

[movieseals]

Thank you so much!

[datakick]

Happy to help.

There is a feature in backlog for coreupdater module to detect expected database differences, and automatically migrate these during update.

Hopefully soon.