HELP: FAILURE of sql_upgrade.php

sikorskj · September 28, 2018, 4:28am

I have a version of OpenEMR 4.1.0 on Windows xampp. I am able to do a mysqldump without an issue. I want to import the openemr mysql database into a new fresh install of OpenEMR 5.0.1 (fresh install via OpenEMR Cloud Express Plus in AWS).

Long story short, after I run " mysql -h 127.0.0.1 -u root -p openemr < openemr.sql", and try /sql_upgrade.php, the sql_upgrade.php page fails to load with a 500 error. Prior to the mysql import, I can at least load the page fine. The error I receive in the /var/log/apache2/error.log follows below:

PHP Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘t0.v_realpatch’ in ‘field list’ in /var/www/localhost/htdocs/openemr/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:104\nStack trace:\n#0 /var/www/localhost/htdocs/openemr/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php(104): PDO->query(‘SELECT t0.v_maj…’)\n#1 /var/www/localhost/htdocs/openemr/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(852): Doctrine\DBAL\Driver\PDOConnection->query(‘SELECT t0.v_maj…’)\n#2 /var/www/localhost/htdocs/openemr/vendor/doctrine/orm/lib/Doctrine/ORM/Persisters/Entity/BasicEntityPersister.php(884): Doctrine\DBAL\Connection->executeQuery(‘SELECT t0.v_maj…’, Array, Array)\n#3 /var/www/localhost/htdocs/openemr/vendor/doctrine/orm/lib/Doctrine/ORM/EntityRepository.php(181): Doctrine\ORM\Persisters\Entity\BasicEntityPersister->loadAll(Array, NULL, NULL, NULL)\n#4 /var/www/localhost/htdocs/openemr/vendor/doctrine/orm/lib/Doctrine/ORM/EntityRepository.php(164): Doctrine in /var/www/localhost/htdocs/openemr/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php on line 71

Is there anyone who can give me a clue about what to try next? Thanks, kindly.

growlingflea · September 28, 2018, 6:53pm

You need to run the sql_upgrade.php script. First, run the mysqldump

You always want to make sure that you make a backup of your data before running the sql_upgrade script.

In your sites/default/sqlconf.php file make sure you have the correct username, password, and database.

Then you want to run the sql_upgrade script. Use the drop down menu to choose which version you are upgrading from.

then you will need to wait. Sometimes it may take up to two hours to complete.

You will see something like this in your browser:

I hope this helps you with your upgrade.

Correction: If you are doing the upgrade from 422 you should see mostly black type. If you see RED, there was an issue.

sikorskj · September 28, 2018, 8:05pm

Thanks for your reply. I ran those steps as outlined prior to posting, but got the error mentioned. Can you clarify when you input the exported data into the new system? i.e. which step is a command like “mysql -h 127.0.0.1 -u root -p openemr < openemr.sql” ?

In my case the steps where:

mysqldump from 4.1.0 instance.
move file inside appropriate mysql docker container
Ensure that sql_upgrade.php is copied from github to the htdocs/openemr area inside the apache docker container. I can load this page just fine at this point.
Input data: i.e. mysql -h 127.0.0.1 -u root -p openemr < openemr.sql
Attempt to load sql_upgrade.php in the browser
FAILURE happens here, and there is a 500 error (with detailed output I previously sent). I can’t even load the page to be able to select anything in the version dropdown, or watch any output happen.

Are you implying that I need to run sql_upgrade.php before Step #4? That would seem strange.

Looking forward to any additional help you can provide. Thanks, kindly.

growlingflea · September 29, 2018, 12:49am

You do it a little differently than I do. For the purpose of this exercise, lets rename the newupgraded database to openemr_500. This assumes that you dumped your data to the following file: openemr.sql. Usually I go into mysql and do the following

go into the folder where your mysql dump is stored
Go into mysql
Enter the following code: CREATE DATABASE openemr_500; USE openemr_500; source openemr.sql;

Here is an example from one of my databases;

Here is the sqlconfig file

sikorskj · September 29, 2018, 1:16am

Thanks for your message. Whether I do it my way, or follow your instructions, I get the same error message when trying to load sql_upgrade.php in the browser, and hence, can’t begin to initiate the UI button/drop-down on the page. This makes me wonder whether there is a bug in sql_upgrade.php that causes a 500 before the page even fully loads. Looks like some kind of Doctrine issue from the error message. What next step would you recommend?

sikorskj · September 29, 2018, 1:39am

I found out what the problem was. Seems that the sql_upgrade.php assumes that you have a few columns, even if you are running an older version that doesn’t have them (i… 4.1.0).

I need to run these two statements first.

ALTER TABLE version ADD COLUMN v_realpatch int(11) NOT NULL DEFAULT 0;
ALTER TABLE version ADD COLUMN v_acl int(11) NOT NULL DEFAULT 0;

Then, I can visit sql_upgrade.php, have the page load without a 500, and then submit the 4.1.0 for upgrade.

Perhaps a patch would be useful for others that addresses this.

The sql_upgrade.php will load these lines amongst others in these two files, but after page posting/submission:

github.com

openemr/openemr/blob/6cf58036a3eea3aaede3cfffc6e79110d6f1b402/sql/4_1_0-to-4_1_1_upgrade.sql

--
--  Comment Meta Language Constructs:
--
--  #IfNotTable
--    argument: table_name
--    behavior: if the table_name does not exist,  the block will be executed

--  #IfTable
--    argument: table_name
--    behavior: if the table_name does exist, the block will be executed

--  #IfMissingColumn
--    arguments: table_name colname
--    behavior:  if the table exists but the column does not,  the block will be executed

--  #IfNotColumnType
--    arguments: table_name colname value
--    behavior:  If the table table_name does not have a column colname with a data type equal to value, then the block will be executed

--  #IfNotRow

This file has been truncated. show original

github.com

openemr/openemr/blob/6cf58036a3eea3aaede3cfffc6e79110d6f1b402/sql/4_1_1-to-4_1_2_upgrade.sql

--
--  Comment Meta Language Constructs:
--
--  #IfNotTable
--    argument: table_name
--    behavior: if the table_name does not exist,  the block will be executed

--  #IfTable
--    argument: table_name
--    behavior: if the table_name does exist, the block will be executed

--  #IfMissingColumn
--    arguments: table_name colname
--    behavior:  if the table exists but the column does not,  the block will be executed

--  #IfNotColumnType
--    arguments: table_name colname value
--    behavior:  If the table table_name does not have a column colname with a data type equal to value, then the block will be executed

--  #IfNotRow

This file has been truncated. show original

sikorskj · September 29, 2018, 4:16pm

Issue raised here so that a more generic bug fix can be completed, instead of the SQL command workaround in the previous reply. See: sql_upgrade fails if missing fields in version · Issue #1885 · openemr/openemr · GitHub

brady.miller · October 3, 2018, 4:33am

Hi,

Without doing the above workaround, does this mean that users will need to do an incremental upgrade when below 4.1.1 (or 4.1.2)?

Upgrade to 4.1.1
Upgrade to 4.1.2
Upgrade to most recent version

-brady

sikorskj · October 5, 2018, 2:11am

Without the workaround, I don’t see how you can use "sql_upgrade.php " in the browser solely by itself. However, if you directly ran server-side first:

4_1_0-to-4_1_1_upgrade.sql
4_1_1-to-4_1_2_upgrade.sql

And then used “sql_upgrade.php” on 4.1.2, I suppose that could work.

What do you think?

Jerod

whittech · May 4, 2020, 5:26pm

Old thread, but I am in a similar pickle. Trying an upgrade from 4.1.2 to 5.0.2. The sql_upgrade script gives an error of:

ERROR: query failed: SELECT mod_name FROM modules WHERE mod_active = 1 AND type = 1 ORDER BY mod_ui_order, date
Error: Table ‘openemr.modules’ doesn’t exist

before presenting any form to choose version.

Any suggestions as to how to get past it? Or do I need to incrementally upgrade? Thanks.

stephenwaite · May 4, 2020, 7:20pm

hi @whittech, did you choose converting from 4.1.2 in the dropdown?

whittech · May 4, 2020, 7:52pm

I was in the same boat as the original post - it never presents a dropdown, it just starts running and gives that error about the missing table. But, I was able to get past this.

For reference, here is what I did:

I have a SQL backup of our current 4.1.2 database and documents.
I stood up a fresh OS, LAMP install, etc. and installed the 5.0.2 code.
On this fresh install, I created an “openemr” database and restored our backup into it.
I pulled down the 4.2.0 code, unzipped it on the side, and grabbed the 4_1_2-to-4_2_0_upgrade.sql file.
I ran this sql script against the restored 4.1.2 database successfully.
I browsed to the sql_upgrade.php script. The script now asks for the version, so I selected 4.2.0.
The script gives this error:
Query Error
ERROR: query failed: INSERT INTO list_options ( list_id, option_id, title, seq) VALUES (‘Occupation’, ?, ?, ?)
Error: Duplicate entry ‘Occupation-unemployed - on unemployment co’ for key ‘PRIMARY’

I compared to our current production database, and these look like new fields with this version? I did a little looking and ended up just deleting the Occupation entry in question with:

delete from list_options where list_id = ‘Occupation’ and option_id = ‘unemployed - on Unemployment Co’;

I browsed again to the sql_upgrade.php script, selected 4.2.0, and it completed successfully.

Now, I am in 5.0.2, seeing our data, etc. and moving around OK. The “Occupation” issue was the only thing that seemed out of sorts. I have also applied patch 2 successfully.