| Bug #20380 | MySQL acting totally weird when SELECTing data from not-USEd databases. | ||
|---|---|---|---|
| Submitted: | 11 Jun 2006 10:59 | Modified: | 16 Jul 2006 12:22 |
| Reporter: | Sven Gebhardt | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 4.1.20 (Gentoo ebuild), 5.0.22 (Gentoo) | OS: | Linux (Gentoo Linux 2.6.14, 2.6.12) |
| Assigned to: | CPU Architecture: | Any | |
[11 Jun 2006 11:11]
Valeriy Kravchuk
Thank you for a problem report. Please, try to repeat with binaries provided by MySQL and inform about the results. Send exact command line used to connect. Send the results of select host, user from mysql.user; (USER, not USERS!)
[11 Jun 2006 11:14]
Sven Gebhardt
That's the output of my gentoo installation, I'll try the binaries now.
pepsi ~ # mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 24 to server version: 5.0.22-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select host, user from mysql.user;
ERROR 1146 (42S02): Table '¼
.user' doesn't exist
mysql> use mysql;
Database changed
mysql> select host, user from user;
+-----------+------+
| host | user |
+-----------+------+
| localhost | root |
+-----------+------+
1 row in set (0.00 sec)
[11 Jun 2006 11:34]
Valeriy Kravchuk
Please, reopen this report when you'll have results with our binaries.
[12 Jun 2006 12:39]
Sven Gebhardt
The binary version works perfectly: pepsi mysql-standard-5.0.22-linux-i686-glibc23 # mysql -uroot -h127.0.0.1 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 5.0.22-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> select host, user from mysql.user; +-----------+------+ | host | user | +-----------+------+ | localhost | | | localhost | root | | pepsi | | | pepsi | root | +-----------+------+ 4 rows in set (0.06 sec)
[16 Jun 2006 10:12]
Miihhii /
Master-Ghetto,Mc_Linkin_Park ,ThE-LoRd , is multis bittte speeren mfg die spielen bei berlin in reloaded.ghettogame.net mit
[16 Jun 2006 12:22]
Valeriy Kravchuk
So, maybe you can just use our binaries and let Gentoo people find what they did wrong in that ebuild (gcc version, -O3, configure options... I do not know)? I see no bug in MySQL code here.
[14 Jul 2006 15:57]
Pascal O.
I'm currently also stuck with this problem, but in a more parcticular case.
Machine: Linux sql 2.6.15-gentoo-r7 #1 SMP Sat Mar 11 15:32:16 CET 2006 x86_64 Intel(R) Xeon(TM) CPU 2.80GHz GenuineIntel GNU/Linux
Server version: 5.0.22-log (from ebuild)
utf8 everywhere
Gentoo Make opts
CFLAGS="-march=nocona -O2 -pipe"
CHOST="x86_64-pc-linux-gnu"
CXXFLAGS="${CFLAGS}"
MAKEOPTS="-j3"
When selecting data with user-defined functions there occurs from time to time an error like "#42S02 Table "_*-.units' doesn't exist" (i had to change these fuzzy characters, since they have ascii hex values like 70 2B 36 _01_!! 2E which might confuse browsers ;-)
CREATE TABLE `test`.`units` (
`id` varchar(127) NOT NULL,
`archivcode` varchar(127) NOT NULL,
`label` text,
`hint` text,
`datein` datetime NOT NULL,
`dateout` datetime default NULL,
`datedestroy` datetime default NULL,
`ordered` tinyint(1) default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Inside this table there are many, really many tuples. At the moment about 2 million. I also know thats quite an ugly table with weird types and so on, but dont let take this into account by now ;-)
Basic queries in that relation work like expected, but when i use custom functions like these...
DELIMITER $$
CREATE FUNCTION `astate`(datein datetime, dateout datetime) RETURNS varchar(25)
DETERMINISTIC
BEGIN
IF (dateout = '1001-01-01 00:00:00')
THEN RETURN 'New';
ELSEIF (datein >= dateout)
THEN RETURN 'Returns';
ELSEIF (datein < dateout)
THEN RETURN 'Access';
ELSE RETURN 'Undefined';
END IF;
END $$
DELIMITER ;
... and then select *, mine.astate(datein,dateout) state from units, this error message #42S02 occurs.
I don't know whats wrong there, but i consider it WEIRD (sorry for shouting).
I already changed cflags to
CFLAGS="-march=nocona -O0 -pipe"
and recompiled - without any noticable cure.
Will now try the binary release. Hope that'll do for a while, but I still prefer using ebuilds.
[16 Jul 2006 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: I noticed that my users couldn't access their tables from within phpMyAdmin, which lead to some debugging and finally to the origin of the problem. When I try to SELECT tables from databases I didn't activate by USEing them before, MySQL returns weird errors (on two different systems): mysql> SELECT * FROM mysql.users; ERROR 1146 (42S02): Table '¼ .users' doesn't exist mysql> USE mysql; Database changed mysql> SHOW TABLES; ERROR 1146 (42S02): Table 'mysql.TABLE_NAMES' doesn't exist After I first upgraded to 5.0.22 (from 4.1.20) I got many problems with mysql_install_db. The mysqld-process spawned by that script just froze. After killing it, I had pointless logfiles in /var/lib/mysql/, telling me nothing, a mysql/ and a test/-directory, both empty. It finally worked out when I copied the mysql-database-folder from another (working) installation. Now it's getting even more weird: The same errors than with 4.1.20 are occuring and now a SELECT from a USEd table doesn't work, too. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 to server version: 5.0.22-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> USE mysql; SELECT * FROM users; Database changed ERROR 1146 (42S02): Table 'mysql.users' doesn't exist That table has to exist as I wouldn't be able to login (with password) if it didn't. How to repeat: I have no idea.