CategoriesDatabaseMariaDBMySQL

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.

CategoriesMicrosoft Dot Net On Linux

MS SQL Server on Linux <=> Management Studio (SSMS) on Windows (via ssh)

If you use MS SQL Server for your database needs, probably you also use SQL Server Management Studio (SSMS) for several day to day database management needs. SSMS is a common tool for managing MS SQL Server whether you need to do DDL, DML or DCL related tasks. While all these things can be done using plain T-SQL and SQLCMD utility, SSMS does things beautifully without us worrying about the SQL syntax and falling into the complexities of queries.

MS SQL Server’s support for Linux was very much awaited among its users. Most of the .Net Framework developers generally uses MS SQL Server for database but the windows-only support not only limited the usage but also increases of hosting cost as compared to linux based db servers.

Continue reading
CategoriesMicrosoft Dot Net On Linux

.Net Framework ❤ Linux – a beautiful love affair

Since beginning of my days as computer enthusiast, Linux have always fascinated me and motivated to start my journey in computer technologies. One of the best thing about Linux is that it has no limits. Almost all of the breaking technologies that we see today are directly or indirectly related to linux!! Whether it is Mac or Android, Drones or any IOT, linux is everywhere.

Back in 2005, when computer systems & information technology was still evolving, the ease of use & user friendly experience made Windows a very popular operating system. So by the time I got my B.Tech degree, I developed a few experimental apps for myself and for college projects using Microsoft .Net Framework, which was picking up lot of buzz during that time.

Its really amazing to be able to develop both standalone and web application by using same set of concepts and tools. Microsoft .Net Framework does exactly that. So in no time, it became source of my bread and butter.

In-spite of being awesome, .Net Framework was hiding a little shame within itself – ‘Linux’ 🙁

Over past few years, several technologies like PHP & Python have evolved many fold. One of the primary reason of their success is their support for Linux and ‘Open’ Culture, on which, .Net Framework was not very good.

Continue reading
Categoriescomputer security

Ransomware attack? Here’s what can be done about it.

Ok, so we are here. Admit it or not, the most scary thing as a computer user is when you start your PC and suddenly all your files have been renamed to something cryptic text and there is a “ReadMe” file in all your folders mentioning that your files have been encrypted and you need to deposit some bitcoins to get them back. Well, if you have encountered it try remembering when was the last time you’ve gone wild on your pc (if you know what i mean ;-)) and regret. That’s the only thing you can do if you’re hit by a Ransomware attack. If there is no backup and the data meant lot, you’re screwed. Literally.

Continue reading