Duplicate entry error upgrading from 4.0.1 to 4.3.2

[Heather Feuerhelm]

I am attempting to upgrade from 4.0.1 to 4.3.2 following the instructions in the documentation. I uploaded all the files, overwriting existing. Then in the browser I began the upgrade process. However, I get the following error:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '5-13' for key 'PRIMARY'

I have tried various searches, but I can't find that key and the only place I can find any version of '5-13' is in dates in data fields. I am now stuck in mid upgrade! Can someone help me figure out how to fix this?

NOTE: I have already tried reverting the database back to the version backed up just prior to the upgrade, but I get stuck at the same place.

This morning I tried doing the update from SSH command line. This expanded on the error a bit more:

[2018-09-13 17:21:12] general.ERROR: exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '5-13' for key 'PRIMARY'' in /home/setupserver/applications/clientarea/public/vendors/minphp/db/src/PdoConnection.php:196 
Stack trace: 
#0 /home/setupserver/applications/clientarea/public/vendors/minphp/db/src/PdoConnection.php(196): PDOStatement->execute(Array) 
#1 /home/setupserver/applications/clientarea/public/components/upgrades/tasks/upgrade4_1_1.php(100): Minphp\Db\PdoConnection->query('INSERT INTO `se...', Array) 
#2 /home/setupserver/applications/clientarea/public/components/upgrades/tasks/upgrade4_1_1.php(54): Upgrade4_1_1->addServiceInvoiceAssociations() 
#3 /home/setupserver/applications/clientarea/public/components/upgrades/upgrades.php(266): Upgrade4_1_1->process('addServiceInvoi...') 
#4 /home/setupserver/applications/clientarea/public/components/upgrades/upgrades.php(101): Upgrades->processObject(Object(Upgrade4_1_1), Array) 
#5 /home/setupserver/applications/clientarea/public/app/controllers/admin_upgrade.php(141): Upgrades->start('4.0.0-b6', '4.3.2', Array) 
#6 /home/setupserver/applications/clientarea/public/app/controllers/admin_upgrade.php(92): AdminUpgrade->processCli() 
#7 /home/setupserver/applications/clientarea/public/vendors/minphp/bridge/src/Lib/Dispatcher.php(143): AdminUpgrade->index() 
#8 /home/setupserver/applications/clientarea/public/vendors/minphp/bridge/src/Lib/Dispatcher.php(61): Dispatcher::dispatch('/admin/upgrade/', true) 
#9 /home/setupserver/applications/clientarea/public/index.php(24): Dispatcher::dispatchCli(Array) 
#10 {main} [] []

Can this help with troubleshooting?

[Tyson]

I'm sorry to see upgrading isn't going very well for you today. Normally there aren't so many issues.

It looks like the database server is rejecting a query attempting to add a new date column to the support_tickets table without being null because it's running in strict mode.

I suggest restoring and reattempting the upgrade again, but before you do, update your config file (/config/blesta.php) and change the sql_mode to a blank string.

i.e. find and change:

'sqlmode_query' => "SET sql_mode='TRADITIONAL'",

to

'sqlmode_query' => "SET sql_mode=''",

(remove the word "TRADITIONAL")

After the upgrade completes, revert this config file change.

[Tyson]

Make sure that you do not have a service_invoices table in your database prior to upgrading. If you do, drop the table, then run the upgrade again.

If you still receive the duplicate key error, run the following query on your database:

SELECT COUNT(`invoice_lines`.`invoice_id`)
FROM `invoice_lines`
INNER JOIN `invoices` ON `invoices`.`id` = `invoice_lines`.`invoice_id`
WHERE `invoices`.`status` IN ('active', 'proforma')
AND `invoice_lines`.`service_id` IS NOT NULL
AND `invoices`.`id` = 5
AND `invoice_lines`.`service_id` = 13
GROUP BY `invoice_lines`.`invoice_id`, `invoice_lines`.`service_id`

If the result is anything greater than "1" then your database server may not be grouping the result set properly, which explains the duplicate key error since there should be no duplicates in the result. I can't readily explain why that would be, but you could update the upgrade script to ignore duplicates, which should have no adverse effects with the upgrade script.

To do so:

1. Update /components/upgrades/tasks/upgrade4_1_1.php
2. At the bottom of that file should be a line:

   'INSERT INTO `service_invoices` (`invoice_id`, `service_id`) (' . $sql . ');',

    

3. Change that to:

   'INSERT IGNORE INTO `service_invoices` (`invoice_id`, `service_id`) (' . $sql . ');',

   That is, just add the word "IGNORE" after "INSERT".

Then run the upgrade again and see if it is successful. If so, take a look at the service_invoices table to ensure you have some records and everything should be fine.

[Heather Feuerhelm]

Thank you. I did as you directed and dropped the table. Reran the upgrade and now get the following:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'Clients.delete-\Blesta\Core\Util\Events\Handlers\ClientAccount' for key 'PRIMARY'

 

[Tyson]

Sounds like another table (i.e. system_events) exists, probably from a previous incomplete upgrade. I would recommend restoring the database backup from earlier, and then running the upgrade again. You'll want to make sure that you don't have either the service_invoices table or the system_events table prior to upgrading.

[Heather Feuerhelm]

Hi Tyson. I followed your instructions and reverted the database and then reuploaded the most recent version of files. I then ran the updater in my SSH Client. I am posting here the complete report:

10/10 [========================================] 100%
1/1 [========================================] 100%
6/6 [========================================] 100%
1/1 [========================================] 100%
1/1 [========================================] 100%
14/14 [========================================] 100%
6/13 [==================>                     ] 46 %[2018-09-13 22:30:35] general.ERROR: exception 'PDOException' with message 
'SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'date_updated' at row 1' in /home/setupserver/applications/clientarea/public/vendors/minphp/db/src/PdoConnection.php:196 
Stack trace: #0 /home/setupserver/applications/clientarea/public/vendors/minphp/db/src/PdoConnection.php(196): PDOStatement->execute(Array) 
#1 /home/setupserver/applications/clientarea/public/plugins/support_manager/support_manager_plugin.php(784): Minphp\Db\PdoConnection->query('ALTER TABLE `su...') 
#2 /home/setupserver/applications/clientarea/public/app/models/plugin_manager.php(298): SupportManagerPlugin->upgrade('2.11.1', '7') 
#3 /home/setupserver/applications/clientarea/public/components/upgrades/upgrades.php(364): PluginManager->upgrade('7') 
#4 /home/setupserver/applications/clientarea/public/components/upgrades/upgrades.php(115): Upgrades->processExtension('plugin', Array) 
#5 /home/setupserver/applications/clientarea/public/app/controllers/admin_upgrade.php(141): Upgrades->start('4.0.0-b6', '4.3.2', Array) 
#6 /home/setupserver/applications/clientarea/public/app/controllers/admin_upgrade.php(92): AdminUpgrade->processCli() 
#7 /home/setupserver/applications/clientarea/public/vendors/minphp/bridge/src/Lib/Dispatcher.php(143): AdminUpgrade->index() 
#8 /home/setupserver/applications/clientarea/public/vendors/minphp/bridge/src/Lib/Dispatcher.php(61): Dispatcher::dispatch('/admin/upgrade/', true) 
#9 /home/setupserver/applications/clientarea/public/index.php(24): Dispatcher::dispatchCli(Array) 
#10 {main} [] []

 

[Heather Feuerhelm]

Interesting. I was able to get into admin and everything looks fine. I then went to Settings > Company > Plugins > Installed. Some required an upgrade and all did fine until I got to Support Manager. That's where the error got thrown:

SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'send_ticket_received'

So how should I handle that plugin?

[Tyson]

11 minutes ago, Heather Feuerhelm said:

Interesting. I was able to get into admin and everything looks fine. I then went to Settings > Company > Plugins > Installed. Some required an upgrade and all did fine until I got to Support Manager. That's where the error got thrown:

SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'send_ticket_received'

So how should I handle that plugin?

Since that plugin's upgrade script failed in the middle of execution, not all of the database changes were made. In this case, you should do two things:

1. Update your config file as I mentioned in my previous post
2. Run the following query on the database:

   UPDATE `plugins` SET `version` = '2.13.0'
   WHERE `dir` = 'support_manager'

    

..then manually upgrade the plugin again.

[Heather Feuerhelm]

I reverted the database and edited config/blesta.php. Everything ran and once I updated the cron, everything is green and good. Thanks!!