Getting warnings on import from a hedisql exported file

I have the lastest version of heidisql. Although this happens on the older versions. I regularly export the format and data from my live database then import the resultant file into my test database. I have been getting lots of warning - deprecated stuff, too long fields using integer (11) and the like. I always believed it was my fault from when I originally defined those tables and values a long time ago. However over Xmas I resolved to go through every one, edit the sql import file until there were no imported errors. Took a long time. I then did a fresh export and imported the resultant file into but it did the same set of errors as before. How come I can use heidisql to generate a sql file and then get warnings when I then use the same heidisql instance to import the produced file?

I even forced it to drop and rebuild the database itself. Any suggestions or ideas? Many thanks.

I should have said I use mariadb

The version of mariadb is relevant here. I assume the source server is older than the target server?

Anyway, if you get warnings, ignore them. MariaDB and MySQL itself produce CREATE TABLE statements which give some harmless warnings.

If you get errors, post them here. That should definitely not happen, at least of the server versions are similar.

You can do nothing about it. It does not help to recreate the tables. The server does not store the syntax you were using when you create a table. Instead it gets streamlined to something which should be guranteed to be compatible.

Note that HeidiSQL gets the CREATE TABLE queries from the server, with a SHOW CREATE TABLE xyz;. So even in HeidiSQL there is nothing I can do about such warnings.

Yes, I get it . I’ll focus on the mariadb on the live system. The one on the dev system is a very new install.

Thanks

Ha, figured it out. I was completely on the wrong side of track. Actually, wampserver had installed both Maria and MySQL on my local system and it had defaulted to MySQL. Flipping that off and switching mariadb port to 3306 and rerunning the import it worked fine with no issues ( but see below). So the import errors were actually being generated by MySQL objecting to the heidisq/mariadbd export data..

On a linked item - just a note for anyone else seeing this thread.- there was one field coming across which because it had some special characters in it for some reason the import objects to the apparent extended length of the data for that field. - if you want details I can add them later.

But the real,issue was how to turn off the stop on error batch flag. There is an icon on the heidisql tool bar, but very poor up to show when it is on or off - had to do trial and error

for anyone interested in the sym problem above - show create from source table - CREATE TABLE tblcurr ( curr char(3) NOT NULL DEFAULT '', curr2 varchar(50) DEFAULT NULL, id int(11) NOT NULL AUTO_INCREMENT, sym varchar(3) DEFAULT NULL, rate double DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

select * from tblcurr "curr","curr2","id","sym","rate" "GBP","GBP","1","Ãâ","1" "EUR","EUR","2","E","1.5" "USD","USD","3","$","2"

exported file ( export table as sql) DROP TABLE IF EXISTS tblcurr;

CREATE TABLE tblcurr ( curr char(3) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT '', curr2 varchar(50) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL, id int NOT NULL AUTO_INCREMENT, sym varchar(10) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL, rate double DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

insert into tblcurr values
( 'GBP','GBP','1','ÃÃ','1') ,( 'EUR','EUR','2','E','1.5') ,( 'USD','USD','3','$','2') ; DROP TABLE IF EXISTS tbldates;

before anyone points it out, i know the source file cur2 field data is rubbish, it should £ but along the line it got corrupted to the 3 odd chars shown. correcting the data value to £ solves the complete problem.

You should also consider upgrading the table's charset away from latin1, to utf8mb4.

thank you