Consideration for 1.8 - support MYSQL 5.6

Submitted by Guest on Sat, 11/01/2014 - 12:33

General developer's discussion

http://dev.mysql.com/tech-resources/articles/whats-new-in-mysql-5.6.html

MySQL 5.6 Introduced some bug changes in the way it manages TIMESTAMP columns and they way they DEFAULT. As a result you cannot easily migrate a MYSQL database that containes TIMESTAMPS to 5.6

Openvpms uses TIMESTAMPS as well as DATE/TIME and other types of time columns. It can be dificult to get the data running on a database above 5.5

http://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html

The section regarding SQL and server storage is important.

MySQL 5.6 is now the GA release from oracle.

Should OPENVPMS 1.8 move to suppport data format/storage in 5.6?

Sincerely

Ben

Re: Consideration for 1.8 - support MYSQL 5.6

Submitted by tanderson on Sat, 11/01/2014 - 20:30.

The OpenVPMS distribution specifies 5.1 as the minimum version, so we would probably move to 5.5 before 5.6, as per http://dev.mysql.com/doc/refman/5.6/en/upgrading.html:

"For example, if you currently are running MySQL 5.1 and wish to upgrade to a newer series, upgrade to MySQL 5.5 first before upgrading to 5.6, and so forth."

-Tim

Re: Consideration for 1.8 - support MYSQL 5.6

Submitted by tgething (not verified) on Sun, 12/01/2014 - 07:55.

Note that we are happily running 5.5 [more by accident than design - I did not realise that 5.1 was the recommended version] so 5.1-->5.5 would appear to be easy, but 5.5-->5.6 looks scary. Note also that I never did a 5.1-->5.5 upgrade, during the conversion from RxWorks I just completely rebuilt the database for each conversion run.

The read-only support in 5.6 raises the possibility of holding old documents in a read-only area. I currently have a 12GB database, of which documents.ibd is 6.9GB. If I could hold all documents older than say 6 months in a read-only document archive, then I could halve the backup time and reduce the backup storage requirements.

Actually - looking at the date-modified values on my ibd files, it looks as though MySQL does not touch the files unless necessary. This means that supporting separating documents into current and historical storage would allow the backup process to skip the copy of the historical part on most occasions. This would thus give the advantages of minimising the backup requirements but not required a second read-only area.

Regards, Tim G