1

When I dump the mysql db schema with phpmyadmin (5.2.0), I would like to get something like this:

CREATE TABLE `MEASURE_TABLE` (
  `measureID` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `sensorID` int(10) UNSIGNED NOT NULL,
  `timestamp` int(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`measureID`),
  FOREIGN KEY (`sensorID`) REFERENCES `LOGICAL_SENSOR_TABLE` (`sensorID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Instead I get something like this (key defined via "ALTER TABLE"):

DROP TABLE IF EXISTS `MEASURE_TABLE`;
CREATE TABLE `MEASURE_TABLE` (
  `measureID` bigint UNSIGNED NOT NULL,
  `sensorID` int UNSIGNED NOT NULL,
  `timestamp` int UNSIGNED NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

ALTER TABLE `MEASURE_TABLE`
  ADD PRIMARY KEY (`measureID`),
  ADD KEY `timestamp` (`timestamp`),
  ADD KEY `sensorID` (`sensorID`);

ALTER TABLE `MEASURE_TABLE`
  ADD CONSTRAINT `MEASURE_TABLE_ibfk_1` FOREIGN KEY (`sensorID`) REFERENCES `LOGICAL_SENSOR_TABLE` (`sensorID`);

I'm pretty sure they are equivalent, but for me the first version is much more readable. I had a previous schema dump from phpmyadmin (not done by me), that was in the first format. It was a different version though (4.6.6deb5).

I couldn't find the option in the export menu, is it possible to change it?

2
  • FKs need to be applied in a specific order, hence leaving them until the end. I do not understand why the PK is not included with the CREATE.
    – Rick James
    Commented May 24, 2022 at 21:36
  • In this answer: dba.stackexchange.com/questions/254089/… they say that indexes are added at the end for speeding up the insert (CREATE, INSERT, INDEX), but in this case I have no data. If I select the option to add IF NOT EXIST for table creation, keys are added inside CREATE TABLE. Foreign key constraints are still outside
    – ptrchv
    Commented May 25, 2022 at 9:10

1 Answer 1

0

I, too, wish the dump had the entire table definition in one spot.

To get the entire definition afterwards, do

SHOW CREATE TABLE tablename

Such is often very helpful in programming questions about MySQL/MariaDB.

Some lame explanations for what you are seeing:

The PRIMARY KEY is an index and a uniqueness constraint; in most situations, it would be faster (for INSERTs) to have it already declared in the table. FULLTEXT and SPATIAL are probably required to be created after loading the data. For other INDEXes (including UNIQUE), the speed is more debatable; it works either way.

In particular, ALTERing the PRIMARY KEY always requires copying the table! Very inefficient.

This forum (and others) are littered with the same question about FOREIGN KEYs -- an error message because the referenced table has not been declared before the FK is declared. The simple fix is to push them to after declaring all tables.

I guess that the tool you are asking about did something simple that made it work, but without thinking about performance. File a bug report against the tool, if you like. (and add a link here)

3
  • If you look what mysqldump/mariadb-dump do, both set UNIQUE_CHECKS=0,FOREIGN_KEY_CHECKS=0 in the beginning of the session and restore this at the end of the dump.
    – danblack
    Commented May 26, 2022 at 4:12
  • @danblack - Can you justify all the ALTERs being separate from the CREATE? (This is one of the mysteries I have not figured out yet.)
    – Rick James
    Commented May 26, 2022 at 15:47
  • The primary key not being in the original CREATE is odd as I'd assume the data is dump in PK order and therefore an insert is going to be really quick. Even if its not, the cost is a possible a little more disk space but will save time. Secondary keys are going to be a more unordered generally so there might be a case for creating secondary indexes once all the data is there. FK constraints, at the same time as secondary or primary would be better than a separate post import, if actual relations are checked, but the cases for doing FK key checks on a complete import are fairly rate.
    – danblack
    Commented May 26, 2022 at 22:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.