Export table, alter table - if exists statement

how can i force heidi to add the "if not exists" part in the exports... also in the create/alter tab i cant see that... and in moste cases i actually want this to be present.

is there an option that has to be activated? i couldnt find anything.

best regards dwza

There is no setting for that.

The SQL exports already contain such a clause. At least in case of MySQL and MariaDB.

The table editor intentionally does not contain it, so if you try to create a table which exists throws an error which HeidiSQL then can handle with a dialog and giving the user to modify the bad name.

Actually I am a developer and from time to time I have to alter some tables and i do this in heidi. Since everyone need this changes on later update (i am coding local) i have to add all db changes to an sql file that other employees import/migrate... how ever, i allways have to edit the statements since it could be that some changes already have been made...

You saied that is in there with a clause... does this means there is a trigger? And it seems that it is in there but only in the description, not in the export it self or neither the tabs...

long story short, would it be possible to have an option in the settings to turn on "if not exists" on all exports and tabs?

I meant the SQL export already contains such a ... IF NOT EXISTS ... clause. Here's an example I just exported from a MySQL 8.0 database:

/*!40101 various comments */;

CREATE TABLE IF NOT EXISTS `curly` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `executed` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `url` text,
  `total_time` float DEFAULT NULL,
  `http_code` smallint DEFAULT NULL,
  `caller_ip` varbinary(50) DEFAULT NULL,
  `debug` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=729576 DEFAULT CHARSET=utf8mb3;

yes, thats true. But you can't export alter tables. This option i am talking about is more for the tabs. And i was talking about the exports for the ability to turn this behavior off... (if thats even needed in any case, so this is just secondary) :)

Logging ALTER and CREATE queries can be achieved using the DDL logging, which I implemented for issue #397. This is likely more exactly what you want.

See the "Advanced" tab on the session manager:

It acutally logs all the statements that i call on the db, that is nice... but there is no "exists" check in it.

This is what i get:

ALTER TABLE my_column ADD COLUMN test INT(1) NOT NULL DEFAULT '1' AFTER id;

But i would love to have:

ALTER TABLE my_column ADD IF NOT EXISTS test INT(1) NOT NULL DEFAULT '1' AFTER id;

Well these "migration" logs can just be deployed to other users or to a VCS, so there is no need for such a exist-check. Please read issue #397 about what the intention was.

Also, these ALTER TABLE queries were generated by the table editor if I see it right. My above comment still is meant serious here:

The table editor intentionally does not contain it, so if you try to create a table which exists throws an error which HeidiSQL then can handle with a dialog and giving the user the possibility to modify the bad name.