Error importing SQL file into new MariaDB database

Question

I'm switching from WAMP to WPN-XM as my local development setup. I learned that it uses MariaDB instead of MYSQL. Serve is setup and MariaDB is running.

I exported my old WAMP DB into an SQL file. When I use WPN-XM's phpmyadmin to import the db, I get a red error MYSQL has gone away. ???

So I try and import from mysql client source c:/myfile.sql and it imports but I get many errors like

ERROR 1231 (4200 at line 31613 in file 'c:/myfile.sql: variable 'character_set_client' can't be set to a value of 'null'

ERROR 1075 (42000) at line 31476 in file: 'c:\nycgmc-dev.sql': Incorrect table definition; there can be only one auto column and it must be defined as a key
ERROR 1075 (42000) at line 31481 in file: 'c:\nycgmc-dev.sql': Incorrect table definition; there can be only one auto column and it must be defined as a key

Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

ERROR 1075 (42000) at line 31491 in file: 'c:\nycgmc-dev.sql': Incorrect table definition; there can be only one auto column and it must be defined as a key
ERROR 1075 (42000) at line 31496 in file: 'c:\nycgmc-dev.sql': Incorrect table definition; there can be only one auto column and it must be defined as a key
ERROR 1075 (42000) at line 31501 in file: 'c:\nycgmc-dev.sql': Incorrect table definition; there can be only one auto column and it must be defined as a key
ERROR 1075 (42000) at line 31506 in file: 'c:\nycgmc-dev.sql': Incorrect table definition; there can be only one auto column and it must be defined as a key
ERROR 1075 (42000) at line 31511 in file: 'c:\nycgmc-dev.sql': Incorrect table definition; there can be only one auto column and it must be defined as a key
ERROR 1075 (42000) at line 31516 in file: 'c:\nycgmc-dev.sql': Incorrect table definition; there can be only one auto column and it must be defined as a key

I also get a lot of what should be there like

Query OK, 7 rows affected (0.10 sec)

when I view in phpMyAdmin I see all the tables but many are o bytes meaning no content, likely due to the errors.

I thought MySQL and MariaDB were pretty much the same and I should be able to import an SQL file from one to the other. Why am I having errors with a simple export and import?

Any help appreciated.

ok, I found out through Googling that you can't go from my MYSQL 5.36 to my new MariaDB 5.536 as the versions have to be the same. they have to be the same version or the definitions will be different. Which is why im getting the errors. makes. So unless there is a better idea, im off to figure out how to upgrade my WAMP's MYSQL to 5.5.36. — Norman Bird
– Norman Bird, Commented May 9, 2014 at 20:07
I always thought that SQL was a standard and could move data from one version to another, I guess not. — Norman Bird
– Norman Bird, Commented May 9, 2014 at 20:13
And I actually am going from MYSQL 5.5.16 to MariaDB 5.5.36. SOmewhere along the way my WAMP appears to now be version 5.5.16, not sure how that happened. Seems close enough versions to not be causing so many errors — Norman Bird
– Norman Bird, Commented May 9, 2014 at 20:18

knoxgon · Accepted Answer · 2018-03-12 10:32:13Z

6

I was able to import from Mysql to MariaDB simply by adjusting the export SQL settings in PHPMyAdmin.

I changed from the default statement e.g:

INSERT INTO tbl_name (col_A,col_B) VALUES (1,2,3), (4,5,6), (7,8,9)

to:

INSERT INTO tbl_name VALUES (1,2,3)

It seems like this was the issue that caused the error. Once I re-exported, it imported into Maria DB fine.

edited Mar 12, 2018 at 10:32

knoxgon

1,2693 gold badges19 silver badges33 bronze badges

answered May 9, 2014 at 20:29

Norman Bird

6924 gold badges10 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DannyTheDev Over a year ago

I know old question - but I stumbled across this, I found I had to disable compression as well as the above technique

Collectives™ on Stack Overflow

Error importing SQL file into new MariaDB database

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related