mysqldump -u USER -p -h OLD_MARIADB_DATABASE \
--column-statistics=0 \
--opt \
--extended-insert=FALSE \
--hex-blob \
--default-character-set=utf8mb4 \
company_database customer_table > customer_table_new.sqlThis week the company did a large migration from our old MariaDB to MySql. We’re doing this for support and there’s more people who know MySql. So today I opened slack and found a few emails from a coworker saying that something wasn’t right. Numbers were way down across the board. I did a quick check of the old vs new and yes she is right we’re missing about 60% of our records. Why and why just on this one database table? Looking into it looks like the migration script failed and booted out thousands of records because of poorly escaped single quotes in the conversion file.
First thing I tried was to rerun the migration using NaviCat my editor of choice for all things MySql/MariaDB. That failed, again on the quotes… ok right haven’t changed anything and problem still exists
Next I tried doing an export to sql and then an import. Again fail. Hunting around in Navicat there are no options which I can use to tell it the amount of records
Next tried doing a simple mysqldump. Failed … mysqldump –extended-insert=FALSE so that each record in the table gets it’s own insert line, that way I can hunt for ‘ if it’s just one. Quickly found more than 10000. That won’t work, plus working with a 20gig sql file isn’t the fastest thing.
Finally asking Gemini how to fix and it spit out that query at the top. Worked 2nd time I tried import it since LOCK TABLES isn’t something I granted the service user.
