MySQL/Mariadb: ERROR 1030 (HY000): Got error 140 "Wrong create options" from storage engine MyISAM

I run a few small databses (to be used in a LAMP environment).

Somewhere in the begin of March I got empty lists in my pages when consulting the database.
It was rather obvious that some update must have done this. Not much time to investigate until now.
Entering mysql as administrator works until I want to extract records from one of the databases:

ERROR 1030 (HY000): Got error 140 "Wrong create options" from storage engine MyISAM

Looking in the zypper history I found out that on 2020-03-07 mariadb 10.2.31 was installed. Before that on 2020-01-10 version 10.2.29 was installed. I reverted to the 10.2.29 (and then blocked the packages involved: mariadb, mariadb-client, mariadb-errormessages and libmysqld19).

Now everything works fine again, thus I have a by-pass.

Further to the error message. I am not creating a database at al, thus why the “Wrong create options”?
I had such an error earlier when I made a jump from openSUSE 31.1 to 42.x. I then had to make several adaptions to my MySQL scripts and PHP-MySQL interface scripts. One of the things was that I got a similar error because during database creation I had the clauses

DATA DIRECTORY = '/home/databases/reisboeken'
INDEX DIRECTORY = '/home/databases/reisboeken'

These are valid for the MyISAM engine, but invalid for the InnoDB egine. As the default engine was changed in the new version of Mariadb I only had to add

ENGINE = MyISAM

to cure this problem. But in that case it was during creation. and now it isn’t!

Before I try to search through web for this problem, I would like to ask if anybody here has an idea.

Reported a bug: https://bugzilla.opensuse.org/show_bug.cgi?id=1169815

Sorry for the late reply, Henk. But I only today remembered I ran into this issue ~1 year ago, when trying to use some old database. I ended up with a working situation by saving the tables as csv, then create a new db, import the csv’s. That worked fine. Found the advice on stackoverflow IIRC.

Thanks for the heads-up.

I doubt this is the same. The only thing I did was rolling back to the 10.2.29 versions and all works fine. Thus I will first try to find out if it is some bug made in the 10.2.31 version.

I can easily export the databases (there are a few of them), I do that e.g. as an ultimate backup. And I used that backup to load the database the last time I made a major step in versions. That was also when I got a “Wrong create options” from the storage engine, but reported in a different MySQL error. Remark that then:

  • I was realy using the CREATE statement;
  • the solution was that I had no ENGINE = and that the default engine changed between those versions, thus the message was grom the InnoDB engine which I had never used. Adding ENGINE = MyISAM solved this.

But I will keep this in mind and try to recreate the whole when all elese fails.

And forgot to add that the error for the InnoDB engine was because I use DATA DIRECTORY and INDEX DIRECTORY during the creation, which are not allowed for InnoDB.

You could easily work around that by creating a/some symlink(s) from /var/lib/mysql/dbname to /home/user/databases/dbname or whatever.

In fact:

boven:~ # ls -l /var/lib/mysql/spoorwegen/
total 8
lrwxrwxrwx 1 mysql mysql   35 Feb 24  2019 boek.MYD -> /home/databases/spoorwegen/boek.MYD
lrwxrwxrwx 1 mysql mysql   35 Feb 24  2019 boek.MYI -> /home/databases/spoorwegen/boek.MYI
-rw-rw---- 1 mysql mysql 2974 Feb 24  2019 boek.frm
-rw-rw---- 1 mysql mysql   61 Feb 24  2019 db.opt
boven:~ # 

So that is exactly the result of my statements.

But I am not sure it is better for the product (any product) to work around every bug that is introduced instead of trying to get the product repaired by reporting.

I am not sure however that in this new case the symlinks are giving the trouble. As said the error message is not very clear, I am NOT creating anything, the links are there (as you propose as a work around).

I’ll post, though assuming you already saw this: https://mariadb.com/kb/en/innodb-encryption-troubleshooting/

Thanks. Yes, I saw it. (In fact I was searching for a users forum. Found nothing until now.)

I am not using InnoDB, nor encryption. The error 140 “Wrong create options” seems to be a sort of general error for all that can go wrong >:(.

What I find strange though is that this error when found on the web seems always to be an InnoDB error (like in my earlier case). But now it is from MyISAM (as it faithfully reports).

The message not related to your databases, but to the mysql internal ones??? That was my impression after a quick read of that URL

Exactly. The error “Wrong create options” is from the storage engine.
And then MySQP puts a wrapper around it, making it in ERROR 1030 (HY000): Got error 140 “Wrong create options” from storage engine MyISAM.

To recap.

About 10-15 uears ago I started this project, putting books and colour slides in databases. Using LAMP.
Being a noob in MySQL, I used the default “storage engine” which appears to be MyISAM at that time. I never tried to assess which engine would be best in my case.

On the last jump in openSUSE I made (from 13.1 to 42.?) it was a clean install on a new system, so I could test if my LAMP still worked. There was a lot to do as well as for Apache, as for PHP and also for MySQL.

One of the MySQL problem being that on creating the database fresh from the backup of tab separeted CSVs, I got

ERROR 1005 (HY000) at line 10: Can't create table `spoorwegen`.`boek` (errno: 140 "Wrong create options")

After much searching I found out that the DATA DIRECTORY and INDEX DIRECTORY were not allowed with engine InnoDB. But I was thinking I used MyISAM. Then I found that the default was changed to InnoDB. Thus explicit configuration that I want to use MySQL solved the problem. Looked all logical in afterthought (exept that I hate changing defaults).

Now we are some time later and after a YaST Online Update (thus security and recommended patches only) I have this problem. Not something to expect in a stable LEAP.

https://stackoverflow.com/questions/6479655/how-do-i-set-myisam-as-default-table-handler-in-mysql

Shows how to set MyISAM as the default engine.

I repeat,

  • I have set MyISAM it as my engine since the jump to 42.? (as explained above)
  • It is working since then until this last update.
  • The message confirms that MyISAM is used.

Nevertheless I will try to add

default-table-type=myisam

as advised there.

But I can test this only by updating again. Will do that tomorrow. And post back.

You did not read far enough, This should for later MySQL/MariaDB versions be


default-storage-engine=MyISAM

OK, I did not read far enough. I now did and also went to the link there pointing to section 15.1 Setting the Storage Engine.

They all talk about MySQL version 5.7 and above. We are now talking about 10.2.29 and 10.2.31. The question is wat has changed between the latter (only a minor version step), not at 5.7, that is already done with long ago.

Section 15.1 starts with explaining how to create a new table that uses a specific engine. That is exactly what was done in creating all of my tables.

In the problem I have now the error message does say nothing about InnoDB, it only talks about MySQL.

The only reason we are discussing InnoDB is because the error displayed by the engine (140) is very much the same to one I got in September 2018. And why I got that one is explained and solved by switching to MyISAM. Since then there is no usage of InnoDB here.

BTW, what may further confuse me is the fact that that documentation is about MySQL 5.7. We are apparently at MariaDB 10.2.x What is the relation?

In any case, I worked a round it for the time being by sticking to 10.2.29. And I am willing to wait what my Bugzilla report will bring. As it is weekend, I expect no answer before begin next week.

FWIW, only the first MariaDB major release had the same version number as MySQL’s. MySQL’s latest major release is 8.0, and even though MariaDB started as a fork, the code is by no means the same anymore as MySQL’s. So a different version numbering isn’t that odd. Not setting the engine in my.cnf allows MariaDB ( and probably MySQL ) to work with databases that can have InnoDB as well as MyISAM engined tables. Which IMHO is a good thing. I did not search for discussion material, I searched for a solution for your problem, since I lack the knowledge to even have such a discussion. Yet, I don’t consider this a bug, and don’t expect the openSUSE packagers to fix this. A one liner in your my.cnf would fix your issues, and even though my searches were quite extensive I did not find loads of others running into the same issue.

Some analogy: There were changes in apache, which needed adjusting vhost configs. Those were announced, documented, needed for ( a,o, ) security reasons, and despite all that many webadmins decided that sticking to the old version was the best option. Results I’ve seen professionaly: Need to freeze php to version X, huge trouble when https became semi-required thanks to governments hyping it ( letsencrypt on old apache versions is horrible ), so sticking to http on interactive sites, browser warnings or outright refusal to load websites.

FWIW2: I haven’t seen separate table/index folders for at least 15 years. And still have no idea why/when that choice was made.

Ik have

default-storage-engine=MyISAM

added to /etc/my.cnf

After restarting mysql:

MariaDB [spoorwegen]> SHOW ENGINES ;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                          | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| MRG_MyISAM         | YES     | Collection of identical MyISAM tables                                            | NO           | NO   | NO         |
| CSV                | YES     | Stores tables as CSV files                                                       | NO           | NO   | NO         |
| SEQUENCE           | YES     | Generated tables filled with sequential values                                   | YES          | NO   | YES        |
| MyISAM             | DEFAULT | Non-transactional engine with good performance and small data footprint          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                        | NO           | NO   | NO         |
| InnoDB             | YES     | Supports transactions, row-level locking, foreign keys and encryption for tables | YES          | YES  | YES        |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                           | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                               | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)

MariaDB [spoorwegen]> 

So it is now the default (which does not change much IMHO, because it may not have been the default, but I used it explicitly).

Updated, all is now back to 10.2.31. Did an extra stop/start.

After that, stil the same problem:

mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.2.31-MariaDB SUSE package

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB (none)]> SET NAMES 'utf8' ;
Query OK, 0 rows affected (0.00 sec)

MariaDB (none)]> connect spoorwegen ;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Connection id:    9
Current database: spoorwegen

MariaDB [spoorwegen]> show tables ;
+----------------------+
| Tables_in_spoorwegen |
+----------------------+
| boek                 |
+----------------------+
1 row in set (0.00 sec)

MariaDB [spoorwegen]> SELECT * FROM boek ;
ERROR 1030 (HY000): Got error 140 "Wrong create options" from storage engine MyISAM
MariaDB [spoorwegen]> 

Now going back to 10.2.29, because I want my db running. :frowning:

Well, let’s see what a bug report results in.

For those interested.

This was the last advice from the bug report:

But I saw that you asked on openSUSE forum [1] as well. In comment #7 you mentioned that you have your db in /home. This can be a problem as we recently enabled ProtectHome=true systemd option in the mariadb.service which prevents from accessing /home. Does ProtectHome=false make any change for you?

And indeed that fixed the problem.

So it was not in MySQL/MariaDB itself, but in the enveloping systemd configuration.

I have no idea why the default was changed. Changing defaults is mostly more dangerous just adding new parameters or values of parameters because people just use defaults and never think about them again. It is also something that is notorious difficult to communicate. When such things are done together with an openSUSE release, you can put an item in the Release Notes, but this is not something to be expected of a mere security fix on stable LEAP.