Форум MySQL

Вот махонький скриптик:

USE sbyte; DROP TABLE IF EXISTS tableFKModificationQuerry; CREATE TEMPORARY TABLE tableFKModificationQuerry   SELECT CONCAT('ALTER TABLE sbyte.', TC.TABLE_NAME, ' DROP FOREIGN KEY ', TC.CONSTRAINT_NAME, ';') AS FooBar   FROM information_schema.TABLE_CONSTRAINTS TC   WHERE (TABLE_SCHEMA = 'sbyte') AND (CONSTRAINT_TYPE = 'FOREIGN KEY') UNION   SELECT CONCAT('ALTER TABLE sbyte.', KCU.TABLE_NAME, ' ADD FOREIGN KEY (', KCU.COLUMN_NAME, ') ',                 'REFERENCES ', KCU.REFERENCED_TABLE_NAME, '(', KCU.REFERENCED_COLUMN_NAME, ') ',                 'ON DELETE CASCADE ON UPDATE CASCADE;') AS FooBar    FROM information_schema.TABLE_CONSTRAINTS TC, information_schema.KEY_COLUMN_USAGE KCU   WHERE (TC.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA) AND         (TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME) AND         (TC.TABLE_SCHEMA = KCU.TABLE_SCHEMA) AND         (TC.TABLE_NAME = KCU.TABLE_NAME) AND                 (KCU.CONSTRAINT_SCHEMA = 'sbyte') AND         (KCU.TABLE_SCHEMA = 'sbyte') AND         (TC.CONSTRAINT_TYPE = 'FOREIGN KEY'); DROP PROCEDURE IF EXISTS FKModification; DELIMITER // CREATE PROCEDURE FKModification() LANGUAGE SQL NOT DETERMINISTIC SQL SECURITY DEFINER BEGIN   DECLARE querry CHAR(192);   DECLARE is_end INT DEFAULT 0;   DECLARE crAutoIncr CURSOR FOR SELECT * FROM tableFKModificationQuerry;   DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_end = 1;   OPEN crAutoIncr;   wet: LOOP     FETCH crAutoIncr INTO querry;     IF is_end THEN LEAVE wet;     END IF;     set @q = querry;     PREPARE stmt FROM @q;     EXECUTE stmt;   END LOOP wet;   CLOSE crAutoIncr; END // DELIMITER ; CALL FKModification(); DROP PROCEDURE IF EXISTS FKModification; DROP TABLE IF EXISTS tableFKModificationQuerry;

В ответ сервер MySQL ругается:
ERROR 1005 (HY000) at line 4: Can't create table 'c:\window\temp\#sql934_a2_0.frm' (errno: 150)

Запрос

SELECT CONCAT('ALTER TABLE sbyte.', TC.TABLE_NAME, ' DROP FOREIGN KEY ', TC.CONSTRAINT_NAME, ';') AS FooBar   FROM information_schema.TABLE_CONSTRAINTS TC   WHERE (TABLE_SCHEMA = 'sbyte') AND (CONSTRAINT_TYPE = 'FOREIGN KEY') UNION   SELECT CONCAT('ALTER TABLE sbyte.', KCU.TABLE_NAME, ' ADD FOREIGN KEY (', KCU.COLUMN_NAME, ') ',                 'REFERENCES ', KCU.REFERENCED_TABLE_NAME, '(', KCU.REFERENCED_COLUMN_NAME, ') ',                 'ON DELETE CASCADE ON UPDATE CASCADE;') AS FooBar    FROM information_schema.TABLE_CONSTRAINTS TC, information_schema.KEY_COLUMN_USAGE KCU   WHERE (TC.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA) AND         (TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME) AND         (TC.TABLE_SCHEMA = KCU.TABLE_SCHEMA) AND         (TC.TABLE_NAME = KCU.TABLE_NAME) AND                 (KCU.CONSTRAINT_SCHEMA = 'sbyte') AND         (KCU.TABLE_SCHEMA = 'sbyte') AND         (TC.CONSTRAINT_TYPE = 'FOREIGN KEY');

выполняется без проблем...

Т.е. дело в том, что MySQL не может сразу заполнить таблицу (любую, не обязательно временную) результатом объединения, тогда как результатом одного запроса - заполняет без проблем.
В то же время запрос-объединение корректен - выполняется без проблем...

Неужели лень затестить простенький запрос ?...

У меня запрос выполнился на ура... У вас MySQL имеет право создавать файлы во временной директории c:\window\temp\? Она из под какого пользователя в системе работает?

Пользователь root с админскими правами.
По поводу временного файла: в случае с одиночным запросом (без объединения) проблем не возникает, т.е. временный файл либо не создаётся, либо создаётся без проблем. Скорее всего не создаётся. Тогда почему MySQL при использовании UNION создаёт временный файл - ему что - оперативки не хватает ?

У тебя, cheops, наверное, по умолчанию стоит MyISAM. У меня - innodb.
Собственно, проблема связана с таблицами innodb. Вот состояние innodb сервера после выполнения запроса (команда show engine innodb status):

Status
\n=====================================\n071129 11:55:06 INNODB MONITOR OUTPUT\n=====================================\nPer second averages calculated from the last 20 seconds\n----------\nSEMAPHORES\n----------\nOS WAIT ARRAY INFO: reservation count 36, signal count 36\nMutex spin waits 0, rounds 620, OS waits 14\nRW-shared spins 18, OS waits 9; RW-excl spins 13, OS waits 13\n------------------------\nLATEST FOREIGN KEY ERROR\n------------------------\n071129 11:41:58 Error in foreign key constraint of table sbyte/tablefkmodificationquerry:\nFOREIGN KEY (', KCU.COLUMN_NAME, ') ',\n 'REFERENCES ', KCU.REFERENCED_TABLE_NAME, '(', KCU.REFERENCED_COLUMN_NAME, ') ',\n 'ON DELETE CASCADE ON UPDATE CASCADE;') AS FooBar \n FROM information_schema.TABLE_CONSTRAINTS TC, information_schema.KEY_COLUMN_USAGE KCU\n WHERE (TC.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA) AND\n (TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME) AND\n (TC.TABLE_SCHEMA = KCU.TABLE_SCHEMA) AND\n (TC.TABLE_NAME = KCU.TABLE_NAME) AND \n (KCU.CONSTRAINT_SCHEMA = 'sbyte') AND\n (KCU.TABLE_SCHEMA = 'sbyte') AND\n (TC.CONSTRAINT_TYPE = 'FOREIGN KEY'):\nCannot resolve column name close to:\n, KCU.COLUMN_NAME, ') ',\n 'REFERENCES ', KCU.REFERENCED_TABLE_NAME, '(', KCU.REFERENCED_COLUMN_NAME, ') ',\n 'ON DELETE CASCADE ON UPDATE CASCADE;') AS FooBar \n FROM information_schema.TABLE_CONSTRAINTS TC, information_schema.KEY_COLUMN_USAGE KCU\n WHERE (TC.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA) AND\n (TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME) AND\n (TC.TABLE_SCHEMA = KCU.TABLE_SCHEMA) AND\n (TC.TABLE_NAME = KCU.TABLE_NAME) AND \n (KCU.CONSTRAINT_SCHEMA = 'sbyte') AND\n (KCU.TABLE_SCHEMA = 'sbyte') AND\n (TC.CONSTRAINT_TYPE = 'FOREIGN KEY')\n------------\nTRANSACTIONS\n------------\nTrx id counter 0 79037\nPurge done for trx's n:o < 0 78961 undo n:o < 0 0\nHistory list length 16\nTotal number of lock structs in row lock hash table 0\nLIST OF TRANSACTIONS FOR EACH SESSION:\n---TRANSACTION 0 0, not started, OS thread id 3968\nMySQL thread id 132, query id 1122 localhost 127.0.0.1 root\nshow engine innodb status\n--------\nFILE I/O\n--------\nI/O thread 0 state: wait Windows aio (insert buffer thread)\nI/O thread 1 state: wait Windows aio (log thread)\nI/O thread 2 state: wait Windows aio (read thread)\nI/O thread 3 state: wait Windows aio (write thread)\nPending normal aio reads: 0, aio writes: 0,\n ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0\nPending flushes (fsync) log: 0; buffer pool: 0\n217 OS file reads, 1028 OS file writes, 700 OS fsyncs\n0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s\n-------------------------------------\nINSERT BUFFER AND ADAPTIVE HASH INDEX\n-------------------------------------\nIbuf: size 1, free list len 0, seg size 2,\n0 inserts, 0 merged recs, 0 merges\nHash table size 103867, used cells 99, node heap has 1 buffer(s)\n0.00 hash searches/s, 0.00 non-hash searches/s\n---\nLOG\n---\nLog sequence number 0 49799887\nLog flushed up to 0 49799887\nLast checkpoint at 0 49799887\n0 pending log writes, 0 pending chkp writes\n683 log i/o's done, 0.00 log i/o's/second\n----------------------\nBUFFER POOL AND MEMORY\n----------------------\nTotal memory allocated 34348094; in additional pool allocated 1734016\nBuffer pool size 1600\nFree buffers 1343\nDatabase pages 256\nModified db pages 0\nPending reads 0\nPending writes: LRU 0, flush list 0, single page 0\nPages read 207, created 49, written 339\n0.00 reads/s, 0.00 creates/s, 0.00 writes/s\nNo buffer pool page gets since the last printout\n--------------\nROW OPERATIONS\n--------------\n0 queries inside InnoDB, 0 queries in queue\n1 read views open inside InnoDB\nMain thread id 2872, state: waiting for server activity\nNumber of rows inserted 111, updated 0, deleted 0, read 3394\n0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s\n----------------------------\nEND OF INNODB MONITOR OUTPUT\n============================\n


Не понятно, откуда берутся вторичные ключи... Явно я их не создаю...
В таблице tablefkmodificationquerry не может быть никаких вторичных ключей...

Сильно упрощаю задачу:
Запрос

CREATE TABLE tableFKModificationQuerry SELECT KCU.TABLE_NAME, ' ADD FOREIGN KEY (', KCU.COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE KCU;

даёт ошибку:

ERROR 1005 (HY000) at line 1: Can't create table 'c:\window\temp\#sql934_a2_0.frm' (errno: 150)

При этом в состоянии innodb (команда show engine innodb status) пишет:

Cannot resolve column name close to:, KCU.COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE KCU

Т.е. серверу не нравится имя столбца ' ADD FOREIGN KEY ('. Так и есть. Если эту строку изменить на ADD FOREIGN1 KEY (', запрос выполнится без проблем. Или любую другую строку записать - будет всё нормально. Т.е. сервер лезет внутрь строки и что-то с ней пытается делать (выполнить - что-ли). При этом добавление AS FooBar не помогает.
Единственный выход - использовать двойные кавычки вместо одинарных. так запрос выполняется на ура...
Довольно странно...