MySQL Bugs: #25037: CREATE TEMPORARY TABLE from MERGE and read-only access

Bug #25037	CREATE TEMPORARY TABLE from MERGE and read-only access
Submitted:	13 Dec 2006 12:12	Modified:	28 Dec 2006 12:23
Reporter:	Nick Kostirya	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Merge storage engine	Severity:	S2 (Serious)
Version:	4.1.22	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
We use one and the same database (MyISAM and MERGE tables), files of which
have reado-only access at the file ststem level (OS is Linux).
Everything worked perfectly until we used MySQL versions up to 4.1.20.
And having switched to MySQL version 4.1.22 we have found out that
when a TEMPORARY TABLE is created on the basis of a MERGE table,
MySQL requires write access to database files. If there are no rights
to write, then the following error appears:

"ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist".

When creating a TEMPORARY TABLE basing on MyISAM such a thing doesn't
happen, everything works fine.

Thus, here is the question: is this a normal situation or a bug in
version 4.1.22?

How to repeat:
see description

Thank you for the report.

Please provide output of SHOW CREATE TABLE statement for your TEMPORARY MERGE table and output of ls -la in the directory contains underlying table.

CREATE TABLE `foo1` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `foo` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1; 

CREATE TABLE `foo2` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `foo` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1; 

CREATE TABLE `foo_merge` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `foo` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`foo1`,`foo2`); 

sam:/mega/data# ls -la -d bug_test
drwxr-x---  2 mysql mysql 4096 2006-12-27 09:06 bug_test

sam:/mega/data# ls -la bug_test
total 60
drwxr-x---  2 mysql mysql 4096 2006-12-27 09:06 .
drwxr-xr-x  9 mysql mysql 4096 2006-12-27 09:01 ..
-rw-r-----  1 mysql mysql   65 2006-12-27 09:01 db.opt
-rw-r-----  1 mysql mysql 8584 2006-12-27 09:03 foo1.frm
-rw-r-----  1 mysql mysql    0 2006-12-27 09:03 foo1.MYD
-rw-r-----  1 mysql mysql 1024 2006-12-27 09:03 foo1.MYI
-rw-r-----  1 mysql mysql 8584 2006-12-27 09:04 foo2.frm
-rw-r-----  1 mysql mysql    0 2006-12-27 09:04 foo2.MYD
-rw-r-----  1 mysql mysql 1024 2006-12-27 09:04 foo2.MYI
-rw-r-----  1 mysql mysql 8584 2006-12-27 09:06 foo_merge.frm
-rw-r-----  1 mysql mysql   10 2006-12-27 09:06 foo_merge.MRG

sam:/mega/data# ps ax | grep mysqld
31732 ?        S      0:00 /bin/sh ./bin/mysqld_safe --defaults-file=/mega/data/my3306.cnf --datadir=/mega/data --basedir=/usr/local/mysql --user=mysql --skip-external-locking --socket=/var/run/mysqld/mysqld.sock --port=3306 --pid-file=/var/run/mysqld/mysql.pid --err-log=/mega/log/3306.err
31770 ?        S      0:00 /usr/local/mysql/libexec/mysqld --defaults-file=/mega/data/my3306.cnf --basedir=/usr/local/mysql --datadir=/mega/data --user=mysql --pid-file=/var/run/mysqld/mysql.pid --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock --skip-external-locking
31773 ?        S      0:00 /bin/sh ./bin/mysqld_safe --defaults-file=/mega/data3307/my3307.cnf --datadir=/mega/data3307 --basedir=/usr/local/mysql --user=mysql3307 --skip-external-locking --socket=/var/run/mysqld/mysqld3307.sock --port=3307 --pid-file=/var/run/mysqld/mysql3307.pid --err-log=/mega/log/3307.err
31811 ?        S      0:00 /usr/local/mysql/libexec/mysqld --defaults-file=/mega/data3307/my3307.cnf --basedir=/usr/local/mysql --datadir=/mega/data3307 --user=mysql3307 --pid-file=/var/run/mysqld/mysql3307.pid --skip-external-locking --port=3307 --socket=/var/run/mysqld/mysqld3307.sock --skip-external-locking

sam:~> echo 'CREATE TEMPORARY TABLE foo_temp SELECT foo, Count(*) FROM foo_merge GROUP BY foo' | mysql -u nick -h 10.0.0.14 -p -P3307 bug_test
ERROR 1168 (HY000) at line 1: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist

Port 3307 is port of read-only mysql (--user=mysql3307)

P.S.
Света, счастливого Нового года. И горячий привет с Украины.
Коля.

sam:/mega/data# groups mysql
mysql : mysql
sam:/mega/data# groups mysql3307
mysql3307 : mysql

sam:/mega/data# grep tmpdir my3307.cnf
tmpdir          = /mega/tmp
sam:/mega/data# ls -lad /mega/tmp
drwxrwxrwx  2 root staff 4096 2006-12-27 09:18 /mega/tmp

I can not repeat it on Linux using last BK sources:

ssmirnova@shella ~/build/mysql-4.1
$libexec/mysqld --defaults-file=support-files/my-small.cnf --datadir=./data --skip-external-locking &
[1] 32470

ssmirnova@shella ~/build/mysql-4.1
$bin/mysql -uroot --socket=/tmp/mysql_ssmirnova.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.23-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database bug25037;
Query OK, 1 row affected (0.04 sec)

mysql> use bug25037;
Database changed
mysql> CREATE TABLE `foo1` (
    ->   `id` int(10) unsigned NOT NULL auto_increment,
    ->   `foo` varchar(100) NOT NULL default '',
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1; 
varchar(100) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1; 

Query OK, 0 rows affected (0.12 sec)

mysql> CREATE TABLE `foo2` (
    ->   `id` int(10) unsigned NOT NULL auto_increment,
    ->   `foo` varchar(100) NOT NULL default '',
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1; 
id` int(10) unsigned NOT NULL auto_increment,
  `foo` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`foo1`,`foo2`);
Query OK, 0 rows affected (0.21 sec)

mysql> CREATE TABLE `foo_merge` (
    ->   `id` int(10) unsigned NOT NULL auto_increment,
    ->   `foo` varchar(100) NOT NULL default '',
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`foo1`,`foo2`); 
Query OK, 0 rows affected (0.08 sec)

mysql> insert into foo1 (foo) values('foo1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo2 (foo) values('foo2');
Query OK, 1 row affected (0.01 sec)

mysql> select * from foo_merge;
+----+------+
| id | foo  |
+----+------+
|  1 | foo1 |
|  1 | foo2 |
+----+------+
2 rows in set (0.00 sec)

mysql> \q
Bye

ssmirnova@shella ~/build/mysql-4.1
$chmod 640 data/bug25037/*

ssmirnova@shella ~/build/mysql-4.1
$ls -la data/bug25037/
total 68
drwx------  2 ssmirnova ssmirnova 4096 Dec 27 11:57 .
drwx------  5 ssmirnova ssmirnova 4096 Dec 27 11:55 ..
-rw-r-----  1 ssmirnova ssmirnova   65 Dec 27 11:55 db.opt
-rw-r-----  1 ssmirnova ssmirnova 8584 Dec 27 11:57 foo1.frm
-rw-r-----  1 ssmirnova ssmirnova   20 Dec 27 11:57 foo1.MYD
-rw-r-----  1 ssmirnova ssmirnova 2048 Dec 27 11:57 foo1.MYI
-rw-r-----  1 ssmirnova ssmirnova 8584 Dec 27 11:57 foo2.frm
-rw-r-----  1 ssmirnova ssmirnova   20 Dec 27 11:57 foo2.MYD
-rw-r-----  1 ssmirnova ssmirnova 2048 Dec 27 11:57 foo2.MYI
-rw-r-----  1 ssmirnova ssmirnova 8584 Dec 27 11:57 foo_merge.frm
-rw-r-----  1 ssmirnova ssmirnova   10 Dec 27 11:57 foo_merge.MRG

ssmirnova@shella ~/build/mysql-4.1
$bin/mysql -uroot --socket=/tmp/mysql_ssmirnova.sock bug25037
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 4.1.23-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TEMPORARY TABLE foo_temp SELECT foo, Count(*) FROM foo_merge GROUP BY foo;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from foo_temp;
+------+----------+
| foo  | Count(*) |
+------+----------+
| foo1 |        1 |
| foo2 |        1 |
+------+----------+
2 rows in set (0.00 sec)

mysql>

It looks like your MySQL server is allowed “writing” the files of the database.
MySql executed under user's account ssmirnova.

This error occurs if it allowed “reading” the files only.
Please execute the below commands and then repeat the request.

cd ~/build/mysql-4.1
chmod 440 data/bug25037/*

echo 'CREATE TEMPORARY TABLE foo_temp SELECT foo, Count(*) FROM' | $bin/mysql -uroot --socket=/tmp/mysql_ssmirnova.sock bug25037

mysql account from your output has write access too.

Result of test:

ssmirnova@shella ~/build/mysql-4.1
$chmod 440 data/bug25037/*

ssmirnova@shella ~/build/mysql-4.1
$ls -la data/bug25037/
total 68
drwx------  2 ssmirnova ssmirnova 4096 Dec 27 11:57 .
drwx------  5 ssmirnova ssmirnova 4096 Dec 27 12:11 ..
-r--r-----  1 ssmirnova ssmirnova   65 Dec 27 11:55 db.opt
-r--r-----  1 ssmirnova ssmirnova 8584 Dec 27 11:57 foo1.frm
-r--r-----  1 ssmirnova ssmirnova   20 Dec 27 11:57 foo1.MYD
-r--r-----  1 ssmirnova ssmirnova 2048 Dec 27 12:11 foo1.MYI
-r--r-----  1 ssmirnova ssmirnova 8584 Dec 27 11:57 foo2.frm
-r--r-----  1 ssmirnova ssmirnova   20 Dec 27 11:57 foo2.MYD
-r--r-----  1 ssmirnova ssmirnova 2048 Dec 27 12:11 foo2.MYI
-r--r-----  1 ssmirnova ssmirnova 8584 Dec 27 11:57 foo_merge.frm
-r--r-----  1 ssmirnova ssmirnova   10 Dec 27 11:57 foo_merge.MRG

ssmirnova@shella ~/build/mysql-4.1
$echo 'CREATE TEMPORARY TABLE foo_temp SELECT foo, Count(*) FROM foo_merge GROUP BY foo; select * from foo_temp;' | bin/mysql -uroot -h127.0.0.1 -P34122  bug25037
foo     Count(*)
foo1    1
foo2    1

Yes, but the server was run under another user account.

Thank you, we'll test last BK sources after holidays.