CentOS 6.5 x-86-64 running MariaDB if cmake uses options -DUSER_ARIA_FOR_TMP_TABLES=OFF and -DWITH_ARIA_STORAGE_ENGINE=ON. Noticed when running Joomla application.

Failure occurs in (or beneath) method ha_myisam::open (in storage/my_isam/ha_myisam.cc) in call to mi_open on line 743, which returns (MI_INFO *) 0x0

I suggested that the customer not specify -DUSER_ARIA_FOR_TMP_TABLES=OFF -DWITH_ARIA_STORAGE_ENGINE=ON as cmake options. Might a possible "fix" be to disallow those options?

Hi,

Do you have any other cnf/opt files in your betterlinux suite, generic ones that are applied to all tests?
With the configuration from the archive, MTR can't work at all, regardless values of cmake options.

It turned out that the test configs are not important. Here is the reduced version of the test case:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a VARCHAR(255) CHARSET utf8, b TEXT, c VARCHAR(512), d VARCHAR(512), e VARCHAR(512));

INSERT INTO t1 VALUES (1,'foo','foo','foo','foo'),(2,'bar','bar','bar','bar');

SELECT DISTINCT a, b, c, d, e, a FROM t1;

EXPLAIN EXTENDED

SELECT DISTINCT a, b, c, d, e, a FROM t1;

DROP TABLE t1;

The problem is reproducible on current 5.5 tree:
cmake . -DUSE_ARIA_FOR_TMP_TABLES=OFF -DWITH_ARIA_STORAGE_ENGINE=ON && make -j3

query 'SELECT DISTINCT a, b, c, d, e, a FROM t1' failed: 126: Incorrect key file for table '/data/repo/bzr/5.5-bug/mysql-test/var/tmp/mysqld.1/#sql_2d4f_0'; try to repair it

But it seems to work all right on 10.0 tree. Since the problem is not critical, and it doesn't show up in the latest stable release, I don't think it's necessary to fix it in 5.5 tree. Please comment if you disagree, otherwise I'm closing it as fixed in 10.0 (I will set 10.0.6 because it's the earliest available release on the list, even though it was apparently fixed long before that).

Thank you, Elena!

Weldon

From my iPhone

Thank you very much for responding to JIRA MDEV-6600! ... Also, thank
you for the simplified test case!

I had another discussion with the ISP that was having a problem with
MariaDB 5.5.x and Joomla, and they said they will continue to use MySQL 5.5
(and possibly 5.6?) for the time being.

I didn't realize when I opened the JIRA bug that a priority of Minor would
result in the bug never being fixed--instead of just being fixed more
slowly. (I probably should have given more thought to the priority when I
assigned it.)

Here is the ISP's problem:

They have hundreds/thousands of shared servers, each with hundreds of MySQL
users. Some of those users CAN'T use Aria (yet) because some of their
applications require characteristics not (yet?) present in Aria.

Unfortunately, run-time options for disabling Aria on an executable that is
built WITH Aria, don't seem to work--at least, we haven't found anything
that works:

We've tried (for example)

skip-aria
aria=OFF
aria_used_for_temp_tables=NO

as hinted by the MariaDB documentation.

Unfortunately, all are mysqld-killing syntax errors. (The third one is a
read-only variable.)

We would love to have them use MariaDB 5.5.3x, but can't until this is
fixed or (at some future date) they are ready to move to MariaDB
10.x--after CentOS moves to 10.x

I asked the ISP why they don't just completely disable Aria via CMAKE
options. They reply that if they completely disable Aria, there is no
reason to consider MariaDB. They would REALLY like it if MariaDB would
allow them to enable/disable Aria via runtime options instead of compiled
in options. That would allow them to easily enable (in my.cnf, for example)
Aria on certain boxes that don't have the Joomla bug.

If I had more time, I might try to fix the code myself. (Perhaps just
adding startup or my.cnf otpions MIGHT suffice ...?)

ww

On Sat, Sep 27, 2014 at 4:52 AM, Elena Stepanova (JIRA) <

Hi Weldon,

So, the problem happens when the ISP chooses to use Aria, but NOT for internal temporary tables.
If they build this way, MyISAM will be used for internal temporary tables instead. Everything else stays the same.
Now,

Some of those users CAN'T use Aria (yet) because some of their applications require characteristics not (yet?) present in Aria.

First, choice of a storage engine for temporary tables is pretty much transparent for users, they normally don't even know which one is it.
Secondly, what are the characteristics that MyISAM has and Aria does not? I can't think of any off the top of my head, so, I'm very interested in the details.

I have a feeling that the ISP gets it all wrong or can't explain properly what they want to achieve, because the description is rather confusing.

Even if Aria is present, it does not mean that it's the default storage engine for user tables, applications won't be using it (except for internal tmp tables) unless the users specifically make them to.
Also, their reasoning for having/not having MariaDB is strange. If they think that Aria is a sole reason for having MariaDB, then how come they are trying to disable it at runtime? If it were possible, doing so would have had the same effect as not building with it in the first place. Why do they insist on having binaries compiled with Aria, but not having it available for actual usage?

All these contradictions were the reason I decided the problem was not critical. If they have a proper explanation, the decision whether or not fix it in 5.5 can be reconsidered.

when i moved some apps from myisam to aria, insert delayed stoped working, i don't know if myisam or mariadb support delayed anymore, that was the only feature that aria don't have (i think), the other one is some a bug with query cache that dba must leave aria running without query cache in a production server

Closed, as per above.
Of you disagree, feel free to reopen this issue.