Let's see if we can get it straight.
Server: Apache 2.2.14
Database: MySQL 5.1.41
If I export the whole database and try to import it via an SQL script I get the following error message:
SQL Error (1067): Invalid default value for 'gueltig_bis'
I tried to figure out which part exactly causes the error. It is the following statment:
CREATE TABLE `postenverlauf` (
`IDPosten` INT(11) UNSIGNED NOT NULL DEFAULT '0',
`Postenname` VARCHAR(20) NOT NULL DEFAULT '' COLLATE 'latin1_swedish_ci',
`gueltig_von` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`gueltig_bis` DATETIME NOT NULL DEFAULT '',
`EK_Einheit` INT(11) UNSIGNED NULL DEFAULT NULL,
`EK_Preis` DOUBLE(6,2) NULL DEFAULT NULL,
`VK_Preis` DOUBLE(5,2) NULL DEFAULT NULL,
`Bestand` INT(11) UNSIGNED NULL DEFAULT NULL,
`Erlaeuterung` VARCHAR(50) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`Aenderungsvermerk` VARCHAR(50) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci'
`postenverlauf` should be a view, but as I read somewhere there will be first some temporary tables for views in order to handle dependencies. So this here is the create statement of the temporary table for the view `postenverlauf`.
Following the source code of HeidiSQL we found the place where it goes wrong. It is in \source\mysql_connection.pas starting at line 1906 where the create statement for the temporary table for views is created. In order to do so a 'SHOW /*!32332 FULL */ COLUMNS FROM <view name>' is used (the output for `postenverlauf` is the comma separated post above).
I described the way the program goes in my case above:
1929 if Results.IsNull('Default') then will handle else case for 'gueltig_bis'
1934 if Col.DataType.Index = dtTimestamp then will also go into the else case. which is
1935 Col.DefaultType := cdtText;
(Line numbers can be messed up as they come from my own fixed one.)
What I changed in order to make it work is that I've put another else case after line 1934
which changes the DefaultType value for the data type Datetime:
else if Col.DataType.Index = dtDatetime then
Col.DefaultType := cdtNothing
Are there any more clarifications necessary?