Trouble upgrading from 2.7 beta to 2.8.0

Management and Administration
1

shortname wrote on Friday, January 13, 2006:

Since I last posted I have switched from OpenSuSE 10.0 to Gentoo 2005.1 to solve some bizarre problems  with PostNuke: but I am now having trouble moving an openemr database. I need to move a database from an old machine (running 2.7 beta for mandrake) to a new machine (Gentoo 2005.1 with OpenEMR 2.8.0). After I set up OpenEMR 2.8.0, I moved the database from the old machine into /var/lib/mysql on the new machine, overwriting the OpenEMR database that was created during setup. At first I encounter no problems (after making sure that sqlconf.php reflects the proper username and password), until I try to log in (standard "admin" and "pass") at which point I get:

Warning: Missing argument 2 for helpfuldie() in /var/www/localhost/htdocs/openemr/library/sql.inc on line 113

ERROR: insert failed: insert into log (date, event, user, groupname, comments) values (NOW(), ‘login’,‘admin’,‘DavidAugust’,‘failure’) (Table ‘log’ is read only)

I’m not sure what the problem is, seeing as the entire OpenEMR database has the correct permissions (I copied the old dbase using cp -RP). Is there some way to make the tables writeable again?

Thanks,

Jeremy

2

drbowen wrote on Friday, January 13, 2006:

I don’t think what you’ve just done is going to be successful.  There are usually significant changes to the table structures from one release to the next.  You can’t just copy and paste the old tables over the new at /var/lib/mysql.

Take a look at:

~webroot/openemr/sql/2_7_2-to-2_7_3_upgrade.sql

This is not a complete list of changes.

The only sure way to upgrade between versions if to carefully compare the table structures and either modify the 2.7beta tables (risky) or to write a script that uses SQL commands to read the data from each 2.7beta table to the new 2.8.0 structure. (safer)

Only the tables that have data need to be transferred this way.  (If a table is empty there is no real reason to try to import it into the new version).

There is currently no cookbook way to make these upgrades.  I am currenlty writing a script to make an upgrade from 2.7.2 to 2.8.0.  A typical example would be (in pseudocode):

Open connectio to 2.7beta

While data exits in table1.openemr2.7 {

   select * from table1;

   insert (name the variable) into table12.8 (wild cards);

}

Close MySQL connection:

This would have to be repeated for each table or a group of similar statements could be executed until all relevant tables were updated.

You may have make allowances for changes in format in the new tables.  This can be executed with any scripting or programming language that you are comfortable with. (PERL, Python, PHP, etc.)

In any case make a full hard copy of your data before you start.

Sam Bowen, MD

3

andres_paglayan wrote on Friday, January 13, 2006:

copying tables as files is not a good procedure,
it’s better to dump and source the contents.

4

sunsetsystems wrote on Friday, January 13, 2006:

There are actually three potential problems at work here.

One is, you’re moving a MySQL database from one distribution to another.  Permissions (users and groups) used by MySQL are likely to be different, causing unpredictable errors.

Secondly, you might be switching from one version of MySQL to another.  You can’t just copy the database files, they may not be compatible.

Third, you are moving from one release of OpenEMR to another.  For this to work you must run the appropriate update script(s) to deal with the fact that there are new and/or changed tables.

The first and second problems have the same solution: you must dump and restore the MySQL database.

To fix the third problem, it should be sufficient to run the relevant *_upgrade.sql scripts through the mysql command line utility.  It’s possible that something was left out of these, but I’m not aware of anything offhand.

– Rod
www.sunsetsystems.com

5

shortname wrote on Saturday, January 14, 2006:

The only programming language in which I know how to write more than "Hello world" would be C++ (definitely not suitable for scripting). Furthermore, I am very new to mysql, and I have no idea how I would dump and restore databases: further help would be greatly appreciated.

6

sunsetsystems wrote on Saturday, January 14, 2006:

Type ‘man mysqldump’ at the command line.  This includes some examples of both dumping and restoring.

– Rod
www.sunsetsystems.com

7

shortname wrote on Saturday, January 14, 2006:

Ok. Thanks: but how do I run the update scripts?

With "mysql --exec=name-of-script.sql"?

And should I run the update scripts before dumping and restoring the 2.7 beta database to the 2.8.0 database?

8

sunsetsystems wrote on Saturday, January 14, 2006:

Something like this:

mysql < name-of-script.sql

but you may need some parameters for authentication also.  ‘man mysql’ for details.

I suggest running the update scripts on the restored database, not the original one; no sense in messing up the original.

– Rod
www.sunsetsystems.com

9

drbowen wrote on Saturday, January 14, 2006:

Rod,

Dumping data using phpmyAdmin escapes the field names by using singles quotes.  In the past the cli mysqldump command does not do this.  Using reserved names for field names breaks the restoration of the mysqldump by cli.

Have you run a successful mysqldump command and backed up successfully without having to fix the reserved table names? 

Was this done from the command line or using phpMyAdmin? 

Sam Bowen

10

shortname wrote on Sunday, January 15, 2006:

It is a very small database, so I can safely break it, since I have copies backed up. So perhaps I should run the update scripts first?

Are there any advantages to using phpMyAdmin?

Thanks,

Jeremy

11

drbowen wrote on Sunday, January 15, 2006:

The command line:

shell> mysqldump --opt -u root -p yourdatabasename | bzip2 -c > yourdatabasename.sql.bz2

This will give a rapid dump and compress it with bzip2.  Type in the MySQL root password when prompted.

If you don’t want a compressed file leave off:

| bzip2 -c > yourdatabasename.sql.bz2

–opt is on by default and is the same as adding these options:

–add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset

__________________________________________

The pma_history table has a column named:

table

and the prescriptions table has a column named:

interval

both of these column names must enclosed in single quotes before the import of the database will work correctly.

The phpMyAdmin is the most convienient way to back up the database.  The main advantage of using the phpMyAdmin is that it escapes out (adds the single quotes) these two MySQL reserved names as its default behavior.

These two column names will need to be fixed prior to reloading the data either by using phpMyAdmin to dump the data, or opening the dump file and adding the single quotes by hand.

phpMyAdmin is included by default in all of the recent releases and is in 2.7 beta Mandrake.

from the main screen click

Administration --> Database Reporting

Under database reporting you will see the phpMyAdmin console.

Click on:

export

About 1/2 way down the screen click on:

Save as file

Fill in your favorite yourdatabasename.sql in the nearby text box.

If you want compression click on your desired type:

none
zipped (good for windows users)
gzipped
bzipped

Once your know you have correctly escaped the two reserved names:

To restore the database:

shell> mysql -u root -p db_name < yourdatabasename.sql

and type the root password when prompted.

The sql conversion files

2_6_5-to-2_7_0_upgrade.sql
2_7_0-to-2_7_2_upgrade.sql
2_7_2-to-2_7_3_upgrade.sql

should work the same way.

You need to know what your database structure looks like to know which upgrades to apply.  They need to be applied in order.

I never personally used the 2.7beta but its structure is likely the same as 2.7.0.  Check the table desriptions to be sure.

Sam Bowen

12

sunsetsystems wrote on Sunday, January 15, 2006:

mysqldump has a --quote-names option to fix this problem.  See http://sunsetsystems.com/tips.php for a couple of examples of how I have used mysqldump.

– Rod
www.sunsetsystems.com

13

shortname wrote on Sunday, January 15, 2006:

I’m rather confused as to what needs to be escaped with single quotes and how to deal with this. Initially I decided that I wouldn’t bother with the quotes, and I simly dumped and sourced the database and then ran the upgrade scripts. While this allows me to get into OpenEMR without any complaints, it doesn’t appear to be letting me schedule patients: could this have something to do with the single quotes escaping the names of the aforementioned columns? Or has something happened to the permissions of the user I am logging in as? (e.g. I no longer have the proper permissions to schedule patients)?

Thanks,

Jeremy

14

shortname wrote on Sunday, January 15, 2006:

One other thing:

I figured out what was keeping me from scheduling patients and setting office hours(I just needed to refresh the page after every change), but I now have another problem:

I cannot seem to schedule patients or set office hours past today (15th)!! Any ideas why this might be?

Thanks,

Jeremy

15

drbowen wrote on Sunday, January 15, 2006:

The appointsment scheduler uses pop-ups.  Is your browser blocking pop-ups?

I found that I could schedule with Konquerer but not Firefox.  After reinstalling Firefox and deleting my old settings I could start shceduling corrctly.

16

shortname wrote on Sunday, January 15, 2006:

Firefox is allowing popups: it’s just that when I go to save an appointment, after clicking save the appointment does not show up. If I refresh the page I get returned to the current date (in the current year). I cannot see office hours or scheduled patients unless I scroll through each day using the forward button. If I try to select a date and then hit “Go,” nothing shows up as being scheduled for that day.

17

sunsetsystems wrote on Monday, January 16, 2006:

I’ve not seen that problem before.  Are you using PHP5 by any chance?  Currently PHP 4.x is required.  Also make sure your PHP errors are being logged and that you know how to view the log; changes to php.ini may be required for this.

– Rod
www.sunsetsystems.com

18

shortname wrote on Tuesday, January 17, 2006:

No, I’m running PHP 4.4.0-r4 on a Gentoo machine. I am using the following USE flags (those with a + are being used, those with a - are not):

+X +berkdb +crypt +curl -debug +doc (-fdftk) (-firebird) -flash -freetds +gd -gd-external -gdbm -gmp -hardenedphp -imap (-informix) +ipv6 -java +jpeg -kerberos -ldap -mcal -memlimit -mssql +mysql +ncurses +nls -oci8 -odbc +pam +png +postgres +readline -snmp +spell +ssl +tiff +truetype +xml2 -yaz

Do I need to enable anything else?

p.s. Ok: I’ll try and figure out how to view the log…

19

shortname wrote on Tuesday, January 17, 2006:

Ok. I believe I may have figured something out:

I now notice two other “INSERT ERROR” messages while trying to create new patients and write encounters. They both complain about nonexistent tables. I  think this is caused by (as someone mentioned earlier) these tables needing to be prefixed with a single quote (which phpMyAdmin does). As soon as I can get access to the machine with original database on it, I’ll try dumping the data using phpMyAdmin instead of simply copying openemr out of /var/lib/mysql. Perhaps this will help?

20

shortname wrote on Wednesday, January 18, 2006:

The two “ERROR: insert failed” errors I get complain of unknown columns in ‘field list.’ Could I fix this by adding the unknown columns? If so, can someone please show me how? (I’m new to mysql).