mysql information_schema.tables inkonzisztencia

Adatbázis: SQL, XML DB

nem regota foglalkozom mysql-lel, de gondolom ez nem feature hanem bug:


SELECT t.TABLE_NAME
FROM information_schema.TABLES t
WHERE t.TABLE_SCHEMA = 'sema' AND
t.TABLE_NAME = 'table'

a fenti select hoz 1 db rekordot a tabla nevevel, eddig minden ok.


SELECT *
FROM information_schema.TABLES t
WHERE t.TABLE_SCHEMA = 'sema' AND
t.TABLE_NAME = 'table'

ez igy nem hoz semmit.

Az egyeduli kulonbseg a ket query kozott a selectben levo oszlopok. Ha beteszem a ROW_FORMAT, TABLE_ROWS, stb oszlopokat, akkor nem hoz eredményt. Szerintetek ez mitol lehet?

  • 5758 megtekintés

( BehringerZoltan | 2014. 06. 17., k – 14:18 )

Nekem működik [rootként], vagy valamit elnézek :-)

mysql> SELECT t.TABLE_NAME FROM information_schema.TABLES t WHERE t.TABLE_SCHEMA = 'aa' AND t.TABLE_NAME = 'album';
+------------+
| TABLE_NAME |
+------------+
| album |
+------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM information_schema.TABLES t WHERE t.TABLE_SCHEMA = 'aa' AND t.TABLE_NAME = 'album';
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------------+
| def | aa | album | BASE TABLE | InnoDB | 10 | Compact | 5 | 3276 | 16384 | 0 | 0 | 9437184 | 6 | 2013-12-19 09:19:53 | NULL | NULL | latin1_swedish_ci | NULL | | |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------------+
1 row in set (0.00 sec)

mysql> SELECT t.TABLE_NAME,t.ROW_FORMAT,t.TABLE_ROWS FROM information_schema.TABLES t WHERE t.TABLE_SCHEMA = 'aa' AND t.TABLE_NAME = 'album';
+------------+------------+------------+
| TABLE_NAME | ROW_FORMAT | TABLE_ROWS |
+------------+------------+------------+
| album | Compact | 5 |
+------------+------------+------------+
1 row in set (0.00 sec)

Server version: 5.5.34-0ubuntu0.12.10.1 (Ubuntu)

( petya | 2014. 06. 17., k – 14:59 )

Akkor lehet ilyen, hogyha nem ugyanazzal a userrel csinalod a 2 queryt, es a masodik esetben nincs joga a userednek azt a semat olvasni, amiben a tabla benne van.

Ez valoszinu dictionary corruption, tipikusan akkor fordul elo, ha drop table kozben kap a server egy kill -9-et. A lenyeg, hogy az innodb data dictionaryjeben ott van a tabla, de a filesystemben mar nincs, es ezert dropolni sem tudod. Amikor megprobalod letrehozni, vagy hozzaferni kene irnia valamit az error logba, legalabb log_warnings=2 eseten.