MySQL Compatibility

[expired user #5312]'s profile image [expired user #5312] posted 13 years ago in Import/Export Permalink
Hi - First off, I came to HeidiSQL after the demise of MySQLFront & I think its great. Better for my usage than several commercial apps I've tried. I'm not a heavyweight SQL user but the only thing I think Heidi lacks is a way to export/import between Sql4 & 5 without editing the generated SQL to remove all the 'ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1' which 4 doesnt like. Or am I missing a way to do this??
ansgar's profile image ansgar posted 13 years ago Permalink
ENGINE and AUTO_INCREMENT should also work in MySQL 4 and below. Only they implemented the whole charset stuff in 4.1 so CHARSET does not work on such old servers. This is mainly because MySQL returns these statements via SHOW CREATE TABLE, and there is no compatibility mode in HeidiSQL which surrounds such stuff with conditional comments. If you ask me, the solution is to upgrade such old servers. Such downward hacks make the HeidiSQL code complex and prone to new errors, so I probably won't do that.
[expired user #5312]'s profile image [expired user #5312] posted 13 years ago Permalink
Thanks for the prompt response. Quite understand your inclination to look forward rather than back. Upgrading is out of my hands as many of my sites are on shared servers but hey ho. Its still a great tool - like I say, better tan anything else I've tried so far for my requirements...

ansgar's profile image ansgar posted 13 years ago Permalink
There is a way to work around that backward problem, without having to edit some huge dump file:
- Make two dumps, first one only with CREATE structure, and "No data" selected. Second one without structure, only data.
- The structure dump should be small and can be edited in a few minutes according to your needs
- Import both on the target server, first the edited structure, then the data output.
[expired user #5312]'s profile image [expired user #5312] posted 13 years ago Permalink
Thats a neat idea - I occasionally have to move data between a 4 and a 5 server where one table has over 20,000 rows so that trick will help a lot - ta!

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