Jump to content
  • 0

Duplicate entry error upgrading from 4.0.1 to 4.3.2


Heather Feuerhelm

Question

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?

Link to comment
Share on other sites

8 answers to this question

Recommended Posts

  • 0

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.

Link to comment
Share on other sites

  • 0

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.

Link to comment
Share on other sites

  • 0

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.

Link to comment
Share on other sites

  • 0

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} [] []

 

Link to comment
Share on other sites

  • 0

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?

Link to comment
Share on other sites

  • 0
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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...