Error using copy table function

Hi, i'm using the HeidiSQL version 10.3.0.5813, from today the function to copy table (Right-click on the table name --> New --> Copy table) does not work and always returns the error popup. ErrorSQL (1064): You have an error in your SQL syntax;........

Please report the server type, and be sure to run the latest HeidiSQL release.

The HeidiSQL version is 10.3.0.5813 (64 bit) running on Windows 10 professional (64 bit), the server is mysql 5.1.66 (x86_64).

Should be fixed in the next build.

There may be more bugs like this in the current builds, as I am working on a larger code enhancement for parsing a table structure, and on SQLite in general.

Having the same problem here. I've relied on right-click -> Create new -> table copy for years & years - it's a fast, easy way to make a quick ad hoc backup.

But in the last few 10.3 builds, I'm getting the same error as andrea.petracca on just about every (MySQL) copy:

SQL Error (1064): You have an error... near 'USING BTREE, UNIQUE INDEX `serialno` (`serialno`) USING BTREE) COLLATE 'ut' at line 208

I'm on the latest build as of today (Windows/10.3.0.5855) and still getting the error.

It must be some issue with INDEXes, as I've been able to work around this problem by copying without the indexes, then recreating them on the copy.

I don't get that error here, on MariaDB 10.3. Could you please report the CREATE code of that particular table here?

CREATE TABLE `serialnumbers` (
    `id` MEDIUMINT(9) NOT NULL,
    `serialno` VARCHAR(8) NOT NULL DEFAULT '0' COLLATE 'utf8_general_ci',
    `description` VARCHAR(150) NULL COLLATE 'utf8_general_ci'
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPACT
;

Indexes on id and serialno

And what server version is it? I am asking because I don't get any error here, on MariaDB 10.3.

I have the same problem

I use MySQL

I'm also getting an error for table copy, when copying a table with a FULLTEXT index on MySQL 8.0.15. in every version after 5771. Works fine on MySQL 8.0.18. tho.

I'm also getting an error for table copy, when copying a table with a FULLTEXT index on MySQL 8.0.15. in every version after 5771. Works fine on MySQL 8.0.18. tho.

My mistake, it doesn't.

Would be nice if we had the exact SQL query here, with the bad part in it. Could you just copy the query and the error here, as text? (take it from the SQL log at the bottom)

Of course

`CREATE TABLE `users` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `typ_powiadom` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `typ_powiadom` (`typ_powiadom`) USING FULLTEXT
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
ROW_FORMAT=DYNAMIC
AUTO_INCREMENT=352
;`

******************************
/* Błąd SQL (1146): Table 'gus_teryt_kopie.users_copy' doesn't exist */
CREATE TABLE `gus_teryt_kopie`.`users_copy` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `typ_powiadom` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `typ_powiadom` (`typ_powiadom`) USING FULLTEXT
)
 COLLATE 'utf8_unicode_ci' ENGINE=InnoDB ROW_FORMAT=Dynamic AUTO_INCREMENT=352;
/* Błąd SQL (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULLTEXT
)
 COLLATE 'utf8_unicode_ci' ENGINE=InnoDB ROW_FORMAT=Dynamic AUTO_IN' at line 5 */

PRIMARY KEY (`id`) USING BTREE,
    UNIQUE INDEX `code` (`code`) USING BTREE,
    INDEX `name` (`name`) USING FULLTEXT
)
 COLLATE 'utf8mb4_0900_ai_ci' ENGINE=InnoDB ROW_FORMAT=Dynamic AUTO_INCREMENT=27785;
/* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULLTEXT
)
 COLLATE 'utf8mb4_0900_ai_ci' ENGINE=InnoDB ROW_FORMAT=Dynamic AUTO' at line 120 */

Should be fixed in the next build.

I have the same problem. I think it has to do with the DEFAULT vars. Can you check that part please?

Example create code; YES has no quotes :

ENUM('YES','NO') NULL DEFAULT YES COLLATE

@gokhan I cannot reproduce that. Please make sure you installed the latest HeidiSQL version, from the nightly builds section: https://www.heidisql.com/download.php#nightlybuilds

@ansgar I have installed the latest version and tried again. Still the same with Version 10.3.0.5858a

I guess the server version is also relevant here, so which one is it?

I guess the server version is also relevant here, so which one is it?

The server is mysql 5.7.21

Just upgraded to 10.3.0.5896 (Win x64), and the problem still exists.

I can right-click any table and select Create New > Table copy...

• If I uncheck [ ] Indexes in Elements to create in new table, the table copies fine (w/o indexes)
• If I check [X] Indexes, the operation fails with SQL Error 1064 every single time

Same problem here with v10.3.0.5806 using latest MySQL. Seems to happen only on some tables where they have COLLATE utf8mb4_0900_ai_ci for a column but removing it and regenerating the table and trying again, it's back.

I see that a slightly newer build is available (v10.3.0.5909) so I updated but it gives the same error when trying to copy a table. Exporting the table to SQL, then running it locally does not give that error (there are others, though) but the resulting table has no data either and running the same query on the live server does give the COLLATE utf8mb4_0900_ai_ci error

Build 5909 fixes an error with unquoted default strings in CREATE TABLE .. code. That should at least fix what gokhan reported. Could you please post the whole error message here @PCHome ?