Updating msql Script Fail

Clif_Caldwell · April 11, 2017, 5:43pm

Please see error below:

INSERT INTO lbt_data SELECT id, ‘body’ , body FROM transactions WHERE body != ‘’

ERROR: query failed: INSERT INTO lbt_data SELECT id, ‘body’ , body FROM transactions WHERE body != ‘’

Error: Duplicate entry ‘1-body’ for key ‘PRIMARY’

C:\xampp\htdocs\openemr\library\sql_upgrade_fx.php at 675:sqlStatement
C:\xampp\htdocs\openemr\sql_upgrade.php at 56:upgradeFromSqlFile(4_2_0-to-4_2_1_upgrade.sql)

Had to go back and restore system to bring 3 weeks of records forward.
Upgraded from v. 4.1.2 to 5.0.

Previous issues with this script were solved by Brady but now stuck on this error.
Thank you in advance,
Clif Caldwell

brady.miller · April 12, 2017, 8:31am

Looks like this is the code that is throwing the error:

github.com

openemr/openemr/blob/master/sql/4_2_0-to-4_2_1_upgrade.sql#L12374-L12453


#IfColumn transactions                                body
INSERT INTO lbt_data SELECT id, 'body'              , body               FROM transactions WHERE body               != '';
ALTER TABLE transactions DROP COLUMN                  body;
#EndIf
#IfColumn transactions                                refer_date
INSERT INTO lbt_data SELECT id, 'refer_date'        , refer_date         FROM transactions WHERE refer_date         IS NOT NULL;
ALTER TABLE transactions DROP COLUMN                  refer_date;
#EndIf
#IfColumn transactions                                refer_from
INSERT INTO lbt_data SELECT id, 'refer_from'        , refer_from         FROM transactions WHERE refer_from         != 0;
ALTER TABLE transactions DROP COLUMN                  refer_from;
#EndIf
#IfColumn transactions                                refer_to
INSERT INTO lbt_data SELECT id, 'refer_to'          , refer_to           FROM transactions WHERE refer_to           != 0;
ALTER TABLE transactions DROP COLUMN                  refer_to;
#EndIf
#IfColumn transactions                                refer_diag
INSERT INTO lbt_data SELECT id, 'refer_diag'        , refer_diag         FROM transactions WHERE refer_diag         != '';
ALTER TABLE transactions DROP COLUMN                  refer_diag;
#EndIf

This file has been truncated. show original

Sounds like you already have maybe migrated date from transactions columns to lbt_data, but still have the transactions columns. What do you mean specifically by “Had to go back and restore system to bring 3 weeks of records forward.”?

-brady

Clif_Caldwell · April 12, 2017, 8:56am

After last time I had three weeks of records to bring up to speed. I backed up the old system again since we had been using it including a new dump of the old DB. I then restored the five directories from sites/default/documents etc… by simply replacing those directories.

Then restored the database with the mysql openemr<dump_file.sql and then ran the script.

Clif Caldwell

Clif_Caldwell · April 12, 2017, 9:36am

If a fix would involve a re-install that would be OK hoping for something more elegant.
I figured incorrectly that when I replaced the openemr DB with the dump file that it was a complete replacement sending me back to a 4.1.2 structured DB.

Obviously my ignorance knows no bounds.
Clif

Clif_Caldwell · April 13, 2017, 1:47am

Brady,
My fault. I thought that when I restored the database (mysql openemr<dump
file.sql) that this would revert the DB back to 4.1.2 so I needed to re-run
the script to upgrade. Obviously this was my ignorance. If the smartest
thing to do is re-install I can do that. Shucks I have plenty of back ups
now.

Thank you,
Clif Caldwell

brady.miller · April 13, 2017, 5:14am

Hi Clif,
The sql backup will be in whatever version the sql dump was on. Thus if you were in version 5.0 when you did the sql dump, then the sql dump will stay with that (and then no need for the sql_upgrade script).
-brady

Clif_Caldwell · April 13, 2017, 12:53pm

I apologize that I was not more clear. The old box (Version 4.1.2) was used
for real work over the last 3 weeks. I took the back up and the sql dump
from there (4.1.2 box) and then restored to the 5.0 box. The 5.0 box
already had a 3 week old version of the data and the sql dump file
installed. So essentially it was a mysql openemr_5.0_modified <
dump_file_4-1-2.sql

Regards,
Clif

brady.miller · April 14, 2017, 9:03am

Hi Clif,

Did you completely dump the database(ie. remove all tables and data) in the 5.0 box before importing in the 4.1.2 database file? If not, then should of done this since from your explanation there is no needed data in the 5.0 box. And then would run the sql upgrade script on it after the 4.1.2 data import. Otherwise without dumping the database in the 5.0 box, lots of weird things will happen.

-brady

Clif_Caldwell · April 14, 2017, 3:25pm

That is the issue. Will clean out the old database (in the v 5.0 box) and then restore from the old box (v4.1.2) and run the script. As usual you diagnosed and treated the problem with great aplomb.
Thanks