export null

[just4fun]

hi 🙂

if i create columpn (exp: datestr) with atribute `data` and then export in file – i have:

insert into testtable values('1','');

insert into testtable values('2','');

and then when i'm import sql file with sqlyog, i have 0000-00-00

[Ritesh]

Can you cut-n-paste the create table statement for the table?

[peterlaursen]

I have an experience that might be related to it. When using SQLyog ODBC import wizard to import from Access TIMESTAMPs that are NULL with the Access database become 0000-00-00-000000 . When exporting from Access into MySQL values are kept as NULL.

However the two different procedures use different ODBC-drivers (M$ ODBC for Access and MyODBC respectively) so I am not sure whether this is an issue with SQLyog or with the M$ ODBC-driver.

[just4fun]

Code:

/*
SQLyog Enterprise v4.06 RC1
Host – 4.1.7-max : Database – test
*********************************************************************
Server version : 4.1.7-max
*/

/*Data for the table `tablename1` */

insert into `tablename1` values (1,'');
insert into `tablename1` values (2,'');
insert into `tablename1` values (3,'');
insert into `tablename1` values (4,'');
insert into `tablename1` values (5,'');

[peterlaursen]

@just4fun:

I think you exported a DB using “data only”. You must check “structure and data” (or copy the table definition from objects-pane)

@Ritesh: I can't reproduce the issue I thought I had, and I can't remeber details. So no bug report from here unless it comes back one day. But it could have been my fault!

[peterlaursen]

I can now reproduce my issue. it was not an issue with SJA (that works perfectly in this respect) but with export/import facility.

Se pic…

[peterlaursen]

Exporting and importing results in: see new pic …

SQL file looks like

/*

SQLyog Enterprise v4.07 BETA 2

Host – 5.0.7-beta-nt-max : Database – test

*********************************************************************

Server version : 5.0.7-beta-nt-max

*/

create database if not exists `test`;

USE `test`;

/*Table structure for table `test2` */

drop table if exists `test2`;

CREATE TABLE `test2` (

`id` bigint(20) NOT NULL auto_increment,

`text1` varchar(50) default NULL,

`text2` varchar(50) default NULL,

`mytime` timestamp NULL default NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*Data for the table `test2` */

insert into `test2` values (1,'first','','2020-05-05 05:05:05');

insert into `test2` values (2,'second','','');

insert into `test2` values (3,'third','','');

There is not written “NULL” to the export file as I think there should be, but empty strings instead.

Result is NULL strings becoming empty strings ('') and NULL timestamps becoming 0000-00-00-000000 (and NULL numbers probably 0's (zero's).

So I must say: bug confirmed !

[peterlaursen]

in PPL (Peter's Programming Language 😀 ) it would be just …

if not content_of_field IS NULL

then

// your current code here //

else

write('NULL')

endif

SQL-file should look like:

/*

SQLyog Enterprise v4.07 BETA 2

Host – 5.0.7-beta-nt-max : Database – test

*********************************************************************

Server version : 5.0.7-beta-nt-max

*/

create database if not exists `test`;

USE `test`;

/*Table structure for table `test2` */

drop table if exists `test2`;

CREATE TABLE `test2` (

`id` bigint(20) NOT NULL auto_increment,

`text1` varchar(50) default NULL,

`text2` varchar(50) default NULL,

`mytime` timestamp NULL default NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*Data for the table `test2` */

insert into `test2` values (1,'first',NULL,'2020-05-05 05:05:05');

insert into `test2` values (2,'second',NULL,NULL);

insert into `test2` values (3,'third',NULL,NULL);

That imports right (see pic)

… and Note: last line should NOT be

“insert into `test2` values (3,'third','NULL','NULL');” 😎

Agreed ??

[peterlaursen]

pic comes here

[just4fun]

Code:

/*
SQLyog Enterprise v4.06 RC1
Host – 4.1.7-max : Database – test
*********************************************************************
Server version : 4.1.7-max
*/

/*Table structure for table `tablename1` */

drop table if exists `tablename1`;

CREATE TABLE `tablename1` (
 `id` int(11) NOT NULL auto_increment,
 `datestr` date default NULL,
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*Data for the table `tablename1` */

insert into `tablename1` values (1,'');
insert into `tablename1` values (2,'');
insert into `tablename1` values (3,'');
insert into `tablename1` values (4,'');
insert into `tablename1` values (5,'');

[peterlaursen]

exactly the same – empty strings instead of NULL's – last line should read

Code:

insert into `tablename1` values (5,NULL);

and the other lines same way!

[just4fun]

1. ok, bug confirmed.

2. tell me please, why i can't edit text type field?:

Code:

select * from `test`.tablename1 where id in(1,2);

[peterlaursen]

It looks like a bug too! 🙁

I can't either from RESULT-pane , but I can from DATA-pane (remember to uncheck the “set NULL”-box)!

Besides I think you should consider using TEXT-types for character data …

[Ritesh]

just4fun wrote on Jun 24 2005, 10:17 AM:

1. ok, bug confirmed.

2. tell me please, why i can't edit text type field?:

Code:

select * from `test`.tablename1 where id in(1,2);

If you look carefully, the result is still Read Only as shown in the combo box. You have to select the correct table from the combo dropdown to edit the result.

[Ritesh]

Bug #1 confirmed and fixed in BETA 3 development tree.

[peterlaursen]

Quote:

Select the correct table from the combo dropdown

of course!

[Ritesh]

So are you able to edit the result?

[peterlaursen]

Quote:

So are you able to edit the result?

If that was a Q for me the A is “Yes”.

I should have known better, but must have been too tired …

[peterlaursen]

correction: Not quite functioning yet!

1)

With text-data there is an almost cosmetical issue: when opening the BLOB-viever, you'll have to click in the window to activate the keyboard with the window. Cursor should be active in the BLOB-viewer window when the window opens. But apart from that I like that you are able to se the beginning of the string from the table view itself.

2)

However some functionality with graphics data now seems broken:

— graphics is not lookin good! there are som artifacts and proportions are changed. See attached pic.

— but most important: import and export of graphics data does not work! (it only doesfrom DATA-pane with a newly created table that has not yet been saved).

— the displaying of the first characters in table view does not make sense with graphics data. I think you should remove it when data are graphics!

Did you totally forget the graphics ?? 🙄

BTW: is it still in the TO-DO list to let BLOB-viewer dislay a binary/hex view of data ?

[peterlaursen]

above is what it should look like.

Below is what it looks like in BLOB-viewer

[peterlaursen]

and …

when graphics has been saved to the database you can't open it in BLOB-viewer! Not from DATA-pane either.

Conclusion: graphics functionality with the BLOB-viewer is broken with 4.07!

[peterlaursen]

the graphics functionality was broken with 4.06 too!

I don't have older versions installed anymore, so I can't test when it happened!

[just4fun]

Quote:

So are you able to edit the result?

sorry, that's ok.

p.s. can you make blob field viewer resiziable?

p.p.s. and add checkbox or button “view in html”

[peterlaursen]

BTW –

1) would it be a big deal to make the BLOB-viewer window resizable ?

(edited: I see that just4fun has put forward the same suggestion <_< ) 2) would it be a big deal to have an option to choose how wide the BLOB/TEXT column opens in DATA- and RESULT -panes 3) what about a NEXT and PREVIOUS -button in BLOB-viewer (should follow the sort order of DATA or RESULT-pane depending on which one is active) ?

[peterlaursen]

One thing more: BLOB-viewer lets you insert graphics into a TEXT-field. It shouldn't! See pic.

The funny string 'y0ya' with various accents is how a jpg-file starts.

I guess it must reformat the data – if not MySQL server shouldn't accept that input to a TEXT-field!

I believe I can make out that this formatting (binary >> character) takes place even with BLOB-fields and that this is the root of the problem.

Further after saving data an attempt to open the TEXT-field containing (reformatted) jpg-data crashes SQLyog.

(the other way around: character-data into a BLOB-field probably should be accepted, since it's common practice – though a bad idea in my opinion!)

[peterlaursen]

I attach a DUMP of last example here

[peterlaursen]

one more issue … not a bug but just something to consider for the future:

With MySQL 5.x varchars can be around 55.000 characters long. The SQLyog GUI can't handle this a reasonable way (no GUI will ever …)

So I propose that BLOB-viewer be activated for varchars exceeding a certain length (that should be user settable since it would depend on screen resolution what is practical for each). It should – I believe – be implemeted no later than ver. 4.2 where support for MySQL 5.x features are planned (as I believe I have read here recently).

With MYSQL 5.x chars can still be “only” 255 characters. But some users might want to activate the same option for chars exceeding a certain length somewhat below the 255. So this option might as well be available for chars as well.

That would also take some consideration on how to handle “trailing blanks” in varchars . They are NOT stripped in MySQL 5.0 (and maybe not 4.1 ?? – I'm not sure). Actually it already is a problem with the DATA-pane and RESULT-pane (chars not affected since “trailing blanks” still are stripped from chars). Some “background-colouring” (not pink please !) could be one way …

I shall go to bed now and not bother you anymore for a while <_<

[Ritesh]

Quote:

With text-data there is an almost cosmetical issue: when opening the BLOB-viever, you'll have to click in the window to activate the keyboard with the window.

Fixed in SQLyog 4.07 BETA 4 development tree.

Quote:

graphics is not lookin good! there are som artifacts and proportions are changed. See attached pic.

This is due to the fact that SQLyog shows the image in the available dimension of the window. The distraction is due to showing a large image in small dimension. Dont worry though – the data inside remains the same.

Quote:

would it be a big deal to make the BLOB-viewer window resizable

Its in the TO-DO list of SQLyog.

Quote:

BTW: is it still in the TO-DO list to let BLOB-viewer dislay a binary/hex view of data ?

Yes.

[peterlaursen]

I still believe some functionality is broken! I can't open grapics data in the viewer if they have been saved to the DB!

Try import a pic .. save it .. exit SQLyog .. open SQLyog .. and try if the viewer let's you see the graphics data. It displays a grey pane only! And it lets you save graphics to a TEXT field. However if you try to open, SQLyog crashes.

And if you choose save as a file from that grey pane SQLyog and even Windows' Explorer hangs.

[Ritesh]

I have forwarded the issue to my development team. They will look into it tomorrow.

[peterlaursen]

That's great.

Nobody has complained about 4.06, so a few days more probably won't make any difference …

[Ritesh]

peterlaursen wrote on Jun 25 2005, 09:23 PM:

One thing more: BLOB-viewer lets you insert graphics into a TEXT-field. It shouldn't! See pic.

From the MySQL Docs at:

http://dev.mysql.com/doc/mysql/en/blob.html

There is no restriction that TEXT column cannot have an image data. As they say:

Quote:

BLOB columns have no character set, and sorting and comparison is based on the numeric values of the bytes in column values. TEXT columns have a character set, and values are sorted and compared based on the collation of the character set assigned to the column

[peterlaursen]

Sorry – but I read it quite opposite

Quote:

TEXT columns have a character set

what's the character set and collation (that should be or should not be) used by for instance jpg-data ??

the other way around

Quote:

BLOB columns have no character set

Well, yes using BLOB with LONG character data is comparable to using BINARY (VAR)CHARs. But since BLOBs can't be used with fulltext indexes I don't see much use of it. But yes – it's legal.

And after all SQLyog 4.06 and 4.07 crashes when it tries to open graphics data that it has inserted itself into a TEXT column! At least it does with latin1 charset (a single byte charset) on MySQL 5.07. And what would happen for instance it you used graphics data with a double-byte character set (Chinese, Japanese, Korean)? And will it work too with both ucs and utf and … And with all available storage engines and mySQL versions ?

I shall not insist on anything with issue. I'm balancing on the edge of my knowledge! And maybe on the wrong side of the edge! But then make it work with SQLyog! Then I shall shut up!

😀

[Ritesh]

Quote:

And after all SQLyog 4.06 and 4.07 crashes when it tries to open graphics data that it has inserted itself into a TEXT column! At least it does with latin1 charset (a single byte charset) on MySQL 5.07. And what would happen for instance it you used graphics data with a double-byte character set (Chinese, japanes, Korean)? And will it work too with both ucs and utf and … And with all available storage engines and mySQL versions ?

This bug has been reproduced by our developers. We are working on it. The next v4.07 BETA will have the bug-fix.

[Author]

Posts