Autor Zpráva
ondra256
Profil
Zdravím.

mám dumplou MYSQL databází do sql souboru a potřebuju ji u sebe na localu nahrát. Používám adminer.

Když spustím import onoho dump souboru tak to při tvorbě tabulky spadne na chybě:

Chyba v dotazu (1215): Cannot add foreign key constraint

Skript vypadá takto:

DROP TABLE IF EXISTS `goods`;

CREATE TABLE `goods` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL COMMENT 'Majitek',
  `category_id` int(10) unsigned NOT NULL COMMENT 'Kategorie',
  PRIMARY KEY (`id`),
  KEY `category_fk_idx` (`category_id`),
  KEY `user_fk_idx` (`user_id`),
  CONSTRAINT `goods_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=217 DEFAULT CHARSET=utf8;


Pokud umažu řádek:
CONSTRAINT `goods_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)

tak se skript v pořádku provede. Tabulka Users na kterou se skript odkazuje zatím ještě nebyla vytvořena.


Proč adminer vyexportoval databázi tak, že skript spadne? Je nějaký program na exportování mysql databází, který by to vyexportoval tak aby šla databáze naimportovat?
juriad
Profil
To je divné, zrovna jsem to zkusil a funguje to, když na začátku skriptu ponechám příkaz:
SET foreign_key_checks = 0;

A když ho odmažu, dostávám jinou chybu:
Error in query (1005): Can't create table `test2`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
ondra256
Profil
Když ponechám: SET foreign_key_checks = 0; tak mně to padá na: Chyba v dotazu (1215): Cannot add foreign key constraint, ikdyž to tam není, nemůžu si pomoct.
Verze MySQL 5.7.9
Kajman
Profil
Nemáte cílový db server nějak netradičně nakonfigurovaný?

Co vypíší dotazy?
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;
ondra256
Profil
Příkazy vypíší:
NO_ENGINE_SUBSTITUTION
NO_ENGINE_SUBSTITUTION
Kajman
Profil
A příkaz?
show engines
juriad
Profil
ondra256:
Co říká následující v sekci „LATEST FOREIGN KEY ERROR“?
SHOW ENGINE INNODB STATUS
ondra256
Profil
show engines
Engine    Support    Comment    Transactions    XA    Savepoints
InnoDB    YES    Supports transactions, row-level locking, and foreign keys    YES    YES    YES
MRG_MYISAM    YES    Collection of identical MyISAM tables    NO    NO    NO
MEMORY    YES    Hash based, stored in memory, useful for temporary tables    NO    NO    NO
BLACKHOLE    YES    /dev/null storage engine (anything you write to it disappears)    NO    NO    NO
MyISAM    DEFAULT    MyISAM storage engine    NO    NO    NO
CSV    YES    CSV storage engine    NO    NO    NO
ARCHIVE    YES    Archive storage engine    NO    NO    NO
PERFORMANCE_SCHEMA    YES    Performance Schema    NO    NO    NO
FEDERATED    NO    Federated MySQL storage engine    NULL    NULL    NULL


SHOW ENGINE INNODB STATUS
Type    Name    Status
InnoDB         
=====================================
2016-04-16 17:35:36 0x1268 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 49 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 21 srv_active, 0 srv_shutdown, 257 srv_idle
srv_master_thread log flush and writes: 278
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 341
OS WAIT ARRAY INFO: signal count 303
RW-shared spins 0, rounds 398, OS waits 129
RW-excl spins 0, rounds 3843, OS waits 38
RW-sx spins 106, rounds 1908, OS waits 15
Spin rounds per wait: 398.00 RW-shared, 3843.00 RW-excl, 18.00 RW-sx
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2016-04-16 17:34:45 0x18c8 Error in foreign key constraint of table d119842_main/goods:
 FOREIGN KEY (`user_id`) REFERENCES `user` (`id`),
  CONSTRAINT `goods_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `goods_category` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8:
Cannot resolve table name close to:
 (`id`),
  CONSTRAINT `goods_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `goods_category` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
------------
TRANSACTIONS
------------
Trx id counter 145958
Purge done for trx's n:o < 145958 undo n:o < 0 state: running but idle
History list length 559
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283546948516504, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283546948515632, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
692 OS file reads, 1229 OS file writes, 642 OS fsyncs
0.14 reads/s, 16384 avg bytes/read, 1.14 writes/s, 0.51 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 62, seg size 64, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 4441, node heap has 1 buffer(s)
Hash table size 4441, node heap has 1 buffer(s)
Hash table size 4441, node heap has 0 buffer(s)
Hash table size 4441, node heap has 1 buffer(s)
Hash table size 4441, node heap has 1 buffer(s)
Hash table size 4441, node heap has 1 buffer(s)
Hash table size 4441, node heap has 1 buffer(s)
Hash table size 4441, node heap has 1 buffer(s)
0.00 hash searches/s, 0.92 non-hash searches/s
---
LOG
---
Log sequence number 5805327746
Log flushed up to   5805327746
Pages flushed up to 5805327746
Last checkpoint at  5805327737
0 pending log flushes, 0 pending chkp writes
249 log i/o's done, 0.31 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 17170432
Dictionary memory allocated 7101845
Buffer pool size   1024
Free buffers       761
Database pages     256
Old database pages 0
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 661, created 411, written 674
0.14 reads/s, 0.02 creates/s, 0.78 writes/s
Buffer pool hit rate 969 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 256, unzip_LRU len: 0
I/O sum[45]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1744, Main thread ID=4284, state: sleeping
Number of rows inserted 420, updated 0, deleted 0, read 170
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Vaše odpověď

Mohlo by se hodit


Prosím používejte diakritiku a interpunkci.

Ochrana proti spamu. Napište prosím číslo dvě-sta čtyřicet-sedm: