Export of DB's containing Views is Buggy

After performing an DB-Export of our databases (code only) i realized during the import, that heidi is writing views as tables.

Dumping structure for view sct_data.shp_event

DROP VIEW IF EXISTS `shp_event`;
# Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `shp_event` (
`shp_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT '' COMMENT 'Unique numeric key identifing a shipment.',

what is really nice, but unfortunately I get errors while the tmptable is to be created, due to not correctly set default values, the original shows:

CREATE TABLE IF NOT EXISTS `shp_event_org` (
`shp_id` bigint(20) unsigned NOT NULL COMMENT 'Unique numeric key identifing a shipment.',

This seems to happen occasionally with int-fields.

If needed i can provide more examples and the full structure to Anse.

sorry, forgot the version-infos:

Heidi: 6.0.0.3621
mySQL: 5.1.47-enterprise-gpl-advanced
WinXP SP 3 (finally we received the update)

Views are exported as tables first and in a second step these get dropped and recreated as views.

This is intentionally to overcome errors when a view is based on a table which is created later.

See also issue #2161 which describes the problem extensively.

Fine, but my problem is, that i am not able to reimport the export, as some default values for int, tinyint, bigint, enum and set are left empty (DEFAULT '') in the temp-table-creation, which is not allowed by mysql as shown below.
The bold part of the query is causing the problem during import!!!!

Original data coming from:

# Dumping structure for table sct_useradmin.client
DROP TABLE IF EXISTS `client`;
CREATE TABLE IF NOT EXISTS `client` (
`client_id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
`client_name` varchar(50) NOT NULL COMMENT 'name of the client',
...
) ENGINE=InnoDB DEFAULT CHARSET=latin1';

Creation of temp table

# Dumping structure for view sct_useradmin.client_logins
DROP VIEW IF EXISTS `client_logins`;
# Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `client_logins` (
`client_id` INT(11) UNSIGNED NOT NULL DEFAULT '',
`client_name` VARCHAR(50) NULL DEFAULT NULL COMMENT 'name of the client' COLLATE 'latin1_swedish_ci',
`userl_last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'datetime of the entry',
`views` BIGINT(21) NOT NULL DEFAULT '0'
) ENGINE=MyISAM;

# Dumping structure for view sct_useradmin.client_logins
DROP VIEW IF EXISTS `client_logins`;
# Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `client_logins`;
CREATE ALGORITHM=UNDEFINED DEFINER=`jhatlak-admin`@`%` SQL SECURITY 
DEFINER VIEW `sct_useradmin`.`client_logins` AS (
select 
`ul`.`client_id` AS `client_id`,
`c`.`client_name` AS `client_name`,
`ul`.`userl_last_update` AS `userl_last_update`,
count(0) AS `views` 
from (
`sct_useradmin`.`user_logging` `ul` 
left join 
`sct_useradmin`.`client` `c` on((`ul`.`client_id` = `c`.`client_id`))) 
group by 
`ul`.`client_id` order by `ul`.`userl_last_update` desc
);

The crux is probably to be found in the result of

SHOW FULL COLUMNS FROM `client_logins`

Please paste the result here, especially the "Default" column value for client_logins.client_id.

Hi Ansgar,

i will send you the whole export via email. The problem occurs in several places, not only at this point.

Greetz

Axel

Should be fixed in r3622.

The wrong thing was here that a field flagged as NOT NULL and at the same time having DEFAULT=(NULL). HeidiSQL misinterpreted this special case as DEFAULT='', r3622 changes this to no default at all, which is - as I believe - the right interpretion here.

It didn't help.

Version 6.0.0.3642
Compiled on: 2011-01-10 22:58:27

I also run into an error when I try to import the views:
SQL Error (1067): Invalid default value for ...

It tries to set the default value of a DATETIME column to ''. After removing the DEFAULT setting all worked fine.

And you created the export with the newer HeidiSQL build?

In deed I did.

Ok, now we're talking about a different column obviously than the initial comment says. Just to be able to analyze that - could you please send me the result of a

SHOW CREATE TABLE `mytable`

query? Thanks a lot!

I don't get what you want.
I just added "SHOW " before the create-statement that produces the error:
SQL Error (1064): ...

Syntax error.
As I said, the problem is the creation of a table which should be a view later. A default value '' for the data type DATETIME is set which is not possible.

I did some experiment on my own.
Consider the following lines in \source\mysql_connection.pas (as you said that should fix the problem, I started there):

if Results.IsNull('Default') then begin
if Col.AllowNull then
Col.DefaultType := cdtNull
else
Col.DefaultType := cdtNothing;
end else if Col.DataType.Index = dtTimestamp then
Col.DefaultType := cdtCurTSUpdateTS
else
Col.DefaultType := cdtText;
Results.Next;

The original program goes the bold marked way in my case and sets Col.DefaultType as cdtText which is ''. But it should set nothing for the data type Datetime. I guess the following lines should be there instead:

if Results.IsNull('Default') then begin
if Col.AllowNull then
Col.DefaultType := cdtNull
else
Col.DefaultType := cdtNothing;
end else if Col.DataType.Index = dtTimestamp then
Col.DefaultType := cdtCurTSUpdateTS
else if Col.DataType.Index = dtDatetime then
Col.DefaultType := cdtNothing
else
Col.DefaultType := cdtText;
Results.Next;

Now the program works fine for me (and makes more sense to me).

I wanted to see the CREATE TABLE query for the table in question so I can reproduce and fix that here. That's what SHOW CREATE TABLE `mytable` does - what error do you get?

Oh seems you prepended something to the SHOW CREATE TABLE ... query. Please just the

SHOW CREATE TABLE `mytable`

- nothing more.

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,
`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'
) ENGINE=MyISAM;

I think you can reproduce the error. If not, just say something and I reproduce it and post the message.

Sorry, that was the fixed version.
Here the one with the error:

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 '',
`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'
) ENGINE=MyISAM;

Last try:

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'
) ENGINE=MyISAM;

View: postenverlauf
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `postenverlauf` AS select `u`.`IDPosten` AS `IDPosten`,`u`.`Postenname` AS `Postenname`,`u`.`gueltig_von` AS `gueltig_von`,`u`.`gueltig_bis` AS `gueltig_bis`,`u`.`EK_Einheit` AS `EK_Einheit`,`u`.`EK_Preis` AS `EK_Preis`,`u`.`VK_Preis` AS `VK_Preis`,`u`.`Bestand` AS `Bestand`,`u`.`Erlaeuterung` AS `Erlaeuterung`,`u`.`Aenderungsvermerk` AS `Aenderungsvermerk` from `postenverlauf_union` `u` order by `u`.`IDPosten`,`u`.`gueltig_von` desc
character_set_client: utf8
collation_connection: utf8_general_ci

Will there be a fix for this bug?

If you send me the right SHOW CREATE TABLE output - yes. The one above is not the output of SHOW CREATE TABLE, but the code which is generated by HeidiSQL, right? If it was SHOW CREATE TABLE, that would mean the server also returns this erronous

`gueltig_bis` DATETIME NOT NULL DEFAULT ''

The output of HeidiSQL for the query
'SHOW CREATE TABLE postenverlauf'
is that what's in my post next to the last.
If this is still not what you want, then just tell me what you mean be 'SHOW CREATE TABLE' as this is not a defined term.

hmm that means the server returns a buggy CREATE TABLE code which I did not expect but who knows that's probably caused by some known bug. I'll go and search it on bugs.mysql.com .

You might want to see the result from:
'SHOW /*!32332 FULL */ COLUMNS FROM postenverlauf'
as this is what is done for views.
The output of this query is (hope the format works):

"Field";"Type";"Collation";"Null";"Key";"Default";"Extra";"Privileges";"Comment"
"IDPosten";"int(11) unsigned";NULL;"NO";"";"0";"";"select,insert,update,references";""
"Postenname";"varchar(20)";"latin1_swedish_ci";"NO";"";"";"";"select,insert,update,references";""
"gueltig_von";"timestamp";NULL;"NO";"";"0000-00-00 00:00:00";"";"select,insert,update,references";""
"gueltig_bis";"datetime";NULL;"NO";"";"0000-00-00 00:00:00";"";"select,insert,update,references";""
"EK_Einheit";"int(11) unsigned";NULL;"YES";"";NULL;"";"select,insert,update,references";""
"EK_Preis";"double(6,2)";NULL;"YES";"";NULL;"";"select,insert,update,references";""
"VK_Preis";"double(5,2)";NULL;"YES";"";NULL;"";"select,insert,update,references";""
"Bestand";"int(11) unsigned";NULL;"YES";"";NULL;"";"select,insert,update,references";""
"Erlaeuterung";"varchar(50)";"latin1_swedish_ci";"YES";"";NULL;"";"select,insert,update,references";""
"Aenderungsvermerk";"varchar(50)";"latin1_swedish_ci";"YES";"";NULL;"";"select,insert,update,references";""

The import line is highlighted.
The column gueltig_bis has the data type dateformat and NULL values are not allowed. Besides that the last source code I posted works, I was wondering why the original result of the column 'Default' of the query 'SHOW /*!32332 FULL */ COLUMNS FROM'-query is not used at all?

That looks like gueltig_bis has a default of "0000-00-00 00:00:00", not some empty string. In that case HeidiSQL creates this line:

`gueltig_bis` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

... and not this one from above:

`gueltig_bis` DATETIME NOT NULL DEFAULT '',

Or is it the view which is named also "postenverlauf" ?

This is highly confusing... sorry. I have just again seen that MySQL returns a CREATE TABLE with spaces as indentation, not tabs like the above last CREATE TABLE query. So that point me again to the guess that this did not came from the SHOW CREATE TABLE query but from HeidiSQL (which generates tabs). But this is probably some server version difference. Btw, which server version is it? And are you using the latest HeidiSQL build? Sorry if I have asked that before, I'm confused right now.

Let's see if we can get it straight.
Server: Apache 2.2.14
Database: MySQL 5.1.41
HeidiSQL: 6.0.0.3642

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'
) ENGINE=MyISAM;

`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?

Oh that clarifies indeed something - I thought we are talking about a table, but in fact it's a view with a temp table fix.

In that case the existing code looks like the cause:

if Col.DataType.Category <> dtcTemporal then
Col.DefaultText := Results.Col('Default');

Should not have any IF. Can't recall why I did that.

If you cut out the lines 1929-1939 it works too - confused me anyway why you have to set a default type.

Please update to r3650 - should be fixed now.

It works!

I still can not Export and Import views. Does it work for others?

It works for me.

Thank you.