Upgrading the database.

[dqadri]

Hello!

I have been in the process of upgrading my MariaDB install. I had started with Open Dental when MySQL 5.0 was still in vogue, and upgraded to MariaDB sometime after the Mysql-MariaDB fork was established. Recently I found myself upgrading hardware and went to check my database version, and I was still on 10.5. I have now upgraded all the way up to 10.11.16 without any issues. I'm exploring moving up to a current GA release (currently 12.2.2).

Back when my database was originally created, utf8 was the default character set and life was simple. Now with emojis and other unicode text floating around, utf8mb4 is the default for new databases and new installations, but I have not yet converted my old database to the modern character set.

I'm going to do a conversion on a test server with a full copy of my data, and then test the Open Dental client against that server. Does anyone have any insight into this process, questions or concerns? It seems like it has to be done table by table like mentioned below:

https://stackoverflow.com/questions/728 ... to-utf8mb4

[saumilshah]

You are already doing the two things that matter — a full copy on a test server and running the actual client against it. So the rest is mostly about knowing in advance where it will stop.

The one that catches people mid-conversion is index key length. utf8mb3 reserves three bytes per character and utf8mb4 reserves four, and index limits are counted in bytes rather than characters. An indexed VARCHAR(255) is 765 bytes today and 1,020 after conversion. MyISAM's ceiling is 1,000 bytes; InnoDB's is 767 unless the table is DYNAMIC or COMPRESSED, where it is 3,072. So a table-by-table script tends to run happily for a while and then stop dead on whichever table has an index on a 255-character column. Worth listing those before you start rather than meeting them at table forty — joining information_schema.STATISTICS to information_schema.COLUMNS and looking for indexed char columns whose maximum length times four exceeds your engine's limit will find them in one pass.

The quieter one is sql_mode. Check what the server is actually running in before the conversion rather than after. In non-strict mode something that should be an error arrives as a warning, the statement reports success, and you find out weeks later. We lost real time to exactly that on an unrelated change. Running the conversion under strict mode at minimum makes the run honest with you.

Third, row size. The 65,535-byte row limit counts declared bytes rather than stored ones, so a wide table full of VARCHARs can be perfectly fine at three bytes per character and refuse to convert at four.

And when it finishes, verify by comparing rather than by absence of errors — row counts per table and a checksum on the big ones, against the copy you started from. "Nothing errored" and "the data is the same" are different claims, and on a database with that much history it is worth being able to make the second one.