Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

Error using copy table function

andrea.petracca's profile image andrea.petracca posted 1 month ago in General Permalink

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;........

ansgar's profile image ansgar posted 1 month ago Permalink

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

andrea.petracca's profile image andrea.petracca posted 1 month ago Permalink

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).

Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
Code modification/commit 245e033 from Ansgar Becker <anse@heidisql.com>, 1 month ago, revision 5814
Detect empty string without quotes as text default type. See https://www.heidisql.com/forum.php?t=35365
ansgar's profile image ansgar posted 1 month ago Permalink

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.

rhyno's profile image rhyno posted 4 weeks ago Permalink

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.

ansgar's profile image ansgar posted 4 weeks ago Permalink

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

rhyno's profile image rhyno posted 3 weeks ago Permalink
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

ansgar's profile image ansgar posted 3 weeks ago Permalink

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

tomkerc's profile image tomkerc posted 3 weeks ago Permalink

I have the same problem

1 attachment(s):
  • problem
Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
tomkerc's profile image tomkerc posted 3 weeks ago Permalink

I use MySQL

1 attachment(s):
  • problem2
croshad's profile image croshad posted 3 weeks ago Permalink

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.

croshad's profile image croshad posted 3 weeks ago Permalink

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.

ansgar's profile image ansgar posted 3 weeks ago Permalink

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)

tomkerc's profile image tomkerc posted 3 weeks ago Permalink

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 */
croshad's profile image croshad posted 3 weeks ago Permalink
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 */
Code modification/commit c6ee86e from Ansgar Becker <anse@heidisql.com>, 3 weeks ago, revision 5869
Detect MySQL fulltext keys, and fix wrong USING <algorithm>. See https://www.heidisql.com/forum.php?t=35365
ansgar's profile image ansgar posted 3 weeks ago Permalink

Should be fixed in the next build.

gokhan's profile image gokhan posted 3 weeks ago Permalink

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

ansgar's profile image ansgar posted 3 weeks ago Permalink

@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

gokhan's profile image gokhan posted 3 weeks ago Permalink

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

2 attachment(s):
  • DEFAULT-var
  • HS-Version
ansgar's profile image ansgar posted 3 weeks ago Permalink

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

gokhan's profile image gokhan posted 2 weeks ago Permalink

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

The server is mysql 5.7.21

Please login to leave a reply, or register at first.