Transfer Database between servers - mysql import error #1005 - Can't create table - errno: 140 "Wrong create options")

[orange]

Hi,

I'm transferring a site from an old server to a new server and am having trouble importing the database to the new server via SSH. When I import, I get the following error:

ERROR 1005 (HY000) at line 3755: Can't create table `let7_mag5`.`catalog_product_relation` (errno: 140 "Wrong create options")

Checking Google, someone else had the same problem as me (https://stackoverflow.com/questions...cant-create-table-errno-140-wrong-create-opti) and they solved it by adding the following to their my.ini file:

innodb_file_format = Barracuda # For dynamic and compressed InnoDB tables

I tried adding this to both /etc/my.cnf & /usr/local/directadmin/conf/my.cnf but it didn't work. I still keep getting this error when I try and import the database to the new server.

Another suggestion on the other persons page was to remove ROW_FORMAT = DYNAMIC from the phpmyadmin import script but I can't use phpmyadmin as the database is too big for phpmyadmin and needs to be done by SSH.

Any other know a solution?

Thanks a lot

 

[Richard G]

/usr/local/directadmin/conf/my.cnf

Certainly don't put it there as this is only for password so better remove it there again.

I presume you are using Mariadb on your new server. When using MariaDB 10.2 then strict mode is in effect by default (which was not the case on 10.1 and older and Mysql) which also can cause this error.

I had a look for you on Google and found a bug report about your error here:

[MDEV-11305] MariDB 10.2.2 errno: 140 "Wrong create options", when creating an innodb Table with 'ROW_FORMAT=FIXED' options - Jira

jira.mariadb.org

As a solution this came up in that bug report (which was not a bug)

In InnoDB 5.7 (both in MySQL 5.7 and MariaDB 10.2) innodb_strict_mode=1 by default, so both of them fail to create a table with the wrong ROW_FORMAT.
If you want the old behavior, set innodb_strict_mode=0.

You might also use this in /etc/my.cnf under [mysqld]

sql_mode=

which also disables strict mode. Don't forget to restart mysql/mariadb afterwards.

Hope it helps you fixing this.

By the way, I also use SSH to backup and restore databases, works the best and the quickest.
However, your customers maybe can't use SSH. If somebody ever has the same issue but is a customer, advise them to use Bigdump, which can do big database imports without issues.

GitHub - fadlee/bigdump: Staggered MySQL Dump Importer

Staggered MySQL Dump Importer. Contribute to fadlee/bigdump development by creating an account on GitHub.

github.com

 

[orange]

Thanks a lot for your reply. I was able to import all of my databases except one which is giving me another problem (timeout setting?).

I presume you are using Mariadb on your new server.

Yes, I used the auto-install script on a new Centos 8 server.
Server type: MariaDB
Documentation Server version: 10.4.14-MariaDB - MariaDB
Server Protocol version: 10

You were right that the problem was the strict mode. However, I couldn't disable the strict mode with the "sql_mode=" addition in the /etc/my.cnf file. However, I did get it disabled by adding "innodb_strict_mode = OFF" to the file. This solved the problem

But, I have one database (the largest one) which I don't believe has been imported successfully as it time's out. i.e After I enter the password, the import process never finishes. I notice in general on this new server that the SSH times out at around 10 minutes, so I presume the problem is the SSH times out before the process has finished?

Doing some googling suggested I should change the ClientAliveInterval & ClientAliveCountMax in /etc/ssh/sshd_config. These settings weren't in this file and the file suggested to create a .conf file in /etc/ssh/ssh_config.d/ with those settings which I did. But this didn't work.

Any ideas? Thanks a lot.

 

[Richard G]

so I presume the problem is the SSH times out before the process has finished?

That is a good possibility.

There are indeed 2 options which you can use to have the process finished.
1.) What you already did but it should be present by default in the /etc/sshd/sshd_config file. So odd it wasn't in there. I always have them in there by default, they are just marked. So I unmark them and use this:
ClientAliveInterval 120
ClientAliveCountMax 30

Maybe you used = character like ClientAliveInterval=120, maybe that is the cause it's not working?
And I presume you restarted SSH.

2.) If you can't get this to work, there is always the "screen" command.
It's even better to use that anyway when working via SSH, because if your connection get's closed for whatever reason, even your home ISP has an issue or something, then you won't be able to see what's going on or how far you got on return.
When using the screen command, if you loose connection and you're able to connect later on, you will always be able to reconnect to the screen you were working on and see what's happening.
So even if you are thrown out on an SSH timeout, then screen windows will keep working, so you just have to be patient that the restore is done.

Here's a good howto:

How To Use Linux Screen

Screen or GNU Screen, is a terminal multiplexer. In other words, it means that you can a start a screen session and then open any number of windows (virtual terminals) inside that session.

linuxize.com

Also check with less or nano or vi (maybe less is best to use) the last lines of the mysql backup file. When finished, you can check if the content is present in the database after restore so you're sure that all is back.

As for forums and sites, advise is to use file and attachment on file/hd and not in database, because that this can cause growth in database until a point it gets giving issues.

 

[orange]

That is a good possibility.

There are indeed 2 options which you can use to have the process finished.
1.) What you already did but it should be present by default in the /etc/sshd/sshd_config file. So odd it wasn't in there. I always have them in there by default, they are just marked. So I unmark them and use this:
ClientAliveInterval 120
ClientAliveCountMax 30

Maybe you used = character like ClientAliveInterval=120, maybe that is the cause it's not working?
And I presume you restarted SSH.dd

Thanks a lot for your help Richard. I had mistakenly opened ssh_config instead of sshd_config. Un-commenting the lines and adding the 120 & 30 values fixed the issue.

 

[Richard G]

You're welcome. Glad to see it's fixed now.