Database MariaDB MySQL
amitm  

MariaDB ERROR 1075 “Incorrect table definition; there can be only one auto column and it must be defined as a key”

Like many developers around, I was also struggling with MySQL vs MariaDB dilemma lately and decided to stick with MariaDB where ever possible and keep using MySQL for legacy projects.

Recently my company (Bitss Techniques) partnered with Jelastic to provide “Multi-Cloud Platform-as-a-Service”, Bitss Cloud, enabling developers and startups implement scalable hosting with just a few click and a lot cheaper than Amazon or other cloud providers. So we decided to move all vps hosted projects to Bitss Cloud so we can scale hardware resources, both horizontally and vertically as and when required.

Almost all of our legacy projects’ database was on MySQL, hence we had a tough choice to make, keep using MySQL or move them to MariaDB. Specifically one of the db was having more than 15000 tables, sized about 4GB and growing!! Anyways, we decided to move everything to MariaDB, and in no time, we realized the magnitude of mess we got in.

First we tried to export MySQL database using mysqldump command like this:

mysqldump -u dbuser -p dbname > dbfile.sql

Above command usually work flawlessly but somehow this time mysqldump was incredibly slow. The CPU kept racing to 100% usage but only around 100 mb of data got exported into the sql file!! Even after spending hours researching about this on Google & SO, practically nothing helped which could speed up the export process. My intuition kept hinting me that this has something to do with mysqldump being single threaded, so i googled about using mysqldump in multithreaded way, and that is when i came to know about ‘mysqlpump‘. Next thing we tried was this:

mysqlpump -u dbuser -pdbpass dbname > dbfile.sql

Mysqlpump unbelievably dumped 2.5 GB of database in merely 60 seconds!! Thanks Oracle, for the great utility!!

After we got our MySQL db dump, we zipped it up, copied it to the newly launched MariaDB instance (through NFS), extracted it, then ran this command to import:

mysql -u dbuser -p dbname < dbfile.sql

Well, looks like the cosmos was not in mood to favor us. The import failed with error:

Unknown Collasion utf8mb4_0900_ai_ci

Apparently, MariaDB does not support all MySQL colliasions and manually changing it on a 2.5 GB of SQL file was an impossible task. Linux have an excellent ‘sed’ utility to rescue in situations like this. We ran this sed command:

sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g' dbfile.sql

That command replaced all occurrences of ‘utf8mb4_0900_ai_ci’ with ‘utf8mb4_general_ci’. Nice & Easy.

Well, not so easy. The import failed again with this error:

ERROR 1075 (42000) at line 863: Incorrect table definition; there can be only one auto column and it must be defined as a key

We had no idea what went wrong so we decided to investigate further by looking at line 863 of sql file. But looking at 1 line of code inside a 1.4 GB file was impossible for any text editor, but linux’s “sed” command came to rescue again. This command helped us to see one line inside the large file in linux:

 sed -n 863,900p dbfile.sql 
CREATE TABLE `db`.`wK4df1Ga1o_101_nf3_action_meta` (
`id` int NOT NULL AUTO_INCREMENT,
`parent_id` int NOT NULL,
`key` longtext NOT NULL,
`value` longtext,
`meta_key` longtext,
`meta_value` longtext
) ENGINE=InnoDB AUTO_INCREMENT=119 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
;
-- insert into (.....)
ALTER TABLE `db`.`wK4df1Ga1o_101_nf3_action_meta` ADD UNIQUE KEY `id` (`id`);

The create table statement generated by mysqlpump was causing the problem. Mariadb requires an auto increment column to be a key (unique or primary). Mysqlpump does set the auto increment (id) column as unique key later using alter statement, but the error is issued before alter statement is ever executed.

We ran these two commands to edit the sql queries so unique key is created during create table statement.

 sed -i 's/`id` int NOT NULL AUTO_INCREMENT,/`id` int NOT NULL AUTO_INCREMENT unique key,/g' dbfile.sql
 sed -i 's/` ADD UNIQUE KEY `id` (`id`);/`;/g' dbfile.sql

We got the same error a couple of more times for columns of different datatype, just small edit to above command was needed like this:

sed -i 's/`session_id` bigint unsigned NOT NULL AUTO_INCREMENT,/`session_id` bigint unsigned NOT NULL AUTO_INCREMENT unique key,/g' dbfile.sql
sed -i 's/` ADD UNIQUE KEY `session_id` (`session_id`);/`;/g' dbfile.sql

Then we tried to import the database again with our fingers crossed. And…… Import was successful!! Hooray…..

That’s how we imported MySQL database to MariaDB.

To summarize, we got 2 main errors:

  1. Unknown Collasion utf8mb4_0900_ai_ci.
  2. Incorrect table definition; there can be only one auto column and it must be defined as a key.

Somehow, mysqldump was unable to export the db, so we used mysqlpump, but its sql queries were not welcomed by MariaDB. Editing the sql file through sed command helped to resolve the issues.

I hope our experience will help someone to fix the import issues.

Are you getting some other error while importing from MySQL to MariaDB, do share in comments and I’ll try to help.

0 0 votes
Article Rating
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x