Export of DB's containing Views is Buggy

User, date Message
Written by maeeh
3 years ago
Category: General
13 posts since Wed, 01 Dec 10 		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.
Written by maeeh
3 years ago
13 posts since Wed, 01 Dec 10 		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)
Written by ansgar
3 years ago
4020 posts since Fri, 07 Apr 06 		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.
Written by maeeh
3 years ago
13 posts since Wed, 01 Dec 10 		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
);
Written by ansgar
3 years ago
4020 posts since Fri, 07 Apr 06 		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.
Written by maeeh
3 years ago
13 posts since Wed, 01 Dec 10 		Hi Ansgar,

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

Greetz

Axel
Written by ansgar
3 years ago
4020 posts since Fri, 07 Apr 06 		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.
Written by frigoref
2 years ago
22 posts since Sun, 14 Mar 10 		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.
Written by ansgar
2 years ago
4020 posts since Fri, 07 Apr 06 		And you created the export with the newer HeidiSQL build?
Written by frigoref
2 years ago
22 posts since Sun, 14 Mar 10 		In deed I did.
Written by ansgar
2 years ago
4020 posts since Fri, 07 Apr 06 		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!
Written by frigoref
2 years ago
22 posts since Sun, 14 Mar 10 		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.
Written by frigoref
2 years ago
22 posts since Sun, 14 Mar 10
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).
Written by ansgar
2 years ago
4020 posts since Fri, 07 Apr 06 		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?
Written by ansgar
2 years ago
4020 posts since Fri, 07 Apr 06 		Oh seems you prepended something to the SHOW CREATE TABLE ... query. Please just the

SHOW CREATE TABLE `mytable`

- nothing more.
Written by frigoref
2 years ago
22 posts since Sun, 14 Mar 10

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.
Written by frigoref
2 years ago
22 posts since Sun, 14 Mar 10 		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;
Written by frigoref
2 years ago
22 posts since Sun, 14 Mar 10 		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;
Written by frigoref
2 years ago
22 posts since Sun, 14 Mar 10 		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
Written by frigoref
2 years ago
22 posts since Sun, 14 Mar 10 		Will there be a fix for this bug?
Written by ansgar
2 years ago
4020 posts since Fri, 07 Apr 06 		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 ''
Written by frigoref
2 years ago
22 posts since Sun, 14 Mar 10 		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.
Written by ansgar
2 years ago
4020 posts since Fri, 07 Apr 06 		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 .
Written by frigoref
2 years ago
22 posts since Sun, 14 Mar 10 		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?
Written by ansgar
2 years ago
4020 posts since Fri, 07 Apr 06 		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" ?
Written by ansgar
2 years ago
4020 posts since Fri, 07 Apr 06 		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.
Written by frigoref
2 years ago
22 posts since Sun, 14 Mar 10 		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?
Written by ansgar
2 years ago
4020 posts since Fri, 07 Apr 06 		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.
Written by frigoref
2 years ago
22 posts since Sun, 14 Mar 10 		If you cut out the lines 1929-1939 it works too - confused me anyway why you have to set a default type.
Written by ansgar
2 years ago
4020 posts since Fri, 07 Apr 06 		Please update to r3650 - should be fixed now.
Written by frigoref
2 years ago
22 posts since Sun, 14 Mar 10 		It works!
 

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