Bug: Exporting Table Data As Sql On Varchar Fields.

Tagged: bug, export, varchar

This topic is empty.

Viewing 6 reply threads

Author

Posts

March 11, 2015 at 5:35 pm #13331

Diuter
Member

Hello everyone,

I’m having some trouble exporting tables as .sql.

Here is the table I’m exporting:

And here is the result I get form exporting table data as sql:

/*

SQLyog Community v12.02 (64 bit)

MySQL – 5.1.63-community

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

*/

/*!40101 SET NAMES utf8 */;

create table `bma600` (

`BLQ_NOSSO_NUM` Decimal (18),

`BLQ_DIG_NOS_NUM` char (6),

`BLQ_TAM_NN` Decimal (2),

`BLQ_COD_BCO` Decimal (4),

`BLQ_DIG_BCO` Decimal (2),

`BLQ_COD_AGE` Decimal (5),

`BLQ_DIG_AGE` Decimal (2),

`BLQ_COD_CONV` Decimal (19),

`BLQ_LIN_DIG` varchar (150),

`BLQ_COD_BAR` varchar (150),

`BLQ_LOC_PAG` varchar (300),

`BLQ_COD_CED` Decimal (18),

`BLQ_DIG_CED` Decimal (2),

`BLQ_CNPJ_CED` Decimal (16),

`BLQ_NOME_CED` varchar (120),

`BLQ_DT_DOC` Decimal (9),

`BLQ_DT_PROC` Decimal (9),

`BLQ_DT_VEN` Decimal (9),

`BLQ_QTD_MOE` Decimal (11),

`BLQ_VALOR` Decimal (13),

`BLQ_VAL_DOC` Decimal (13),

`BLQ_VAL_DES` Decimal (13),

`BLQ_VAL_DED` Decimal (13),

`BLQ_VAL_MORA` Decimal (13),

`BLQ_VAL_ACR` Decimal (13),

`BLQ_VAL_COB` Decimal (13),

`BLQ_TPO_INS` char (3),

`BLQ_CNPJ_CPF_SAC` Decimal (16),

`BLQ_NOME_SAC` varchar (120),

`BLQ_END_SAC` varchar (120),

`BLQ_BAI_SAC` varchar (90),

`BLQ_CEP_SAC` Decimal (6),

`BLQ_CCEP_SAC` Decimal (4),

`BLQ_CID_SAC` varchar (90),

`BLQ_EST_SAC` varchar (6),

`BLQ_NUM_DOC` Decimal (11),

`BLQ_ESP_DOC` varchar (6),

`BLQ_ACEITE` char (3),

`BLQ_USO_BCO` varchar (75),

`BLQ_COD_CART` Decimal (4),

`BLQ_MOEDA` varchar (9),

`BLQ_NOME_AVAL` varchar (120),

`BLQ_COD_EMP` Decimal (6),

`BLQ_COD_POR` Decimal (6),

`BLQ_PER_MULTA` Decimal (7),

`BLQ_COD_EST` Decimal (4),

`BLQ_OBS_RET` varchar (765)

);

Does anyone know why the field size is wrong on most fields, specially on the varchar?

Thanks
March 12, 2015 at 10:27 am #35238

Kartik Grover
Spectator

Hi

This is strange. Please send us the result of SHOW CREATE TABLE.

Regards
March 12, 2015 at 1:41 pm #35239

peterlaursen
Participant

something is crazy! If Import your sql and export again I get

SQLyog Ultimate v12.09 (64 bit)

MySQL – 5.6.23-log

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

*/

/*!40101 SET NAMES utf8 */;

create table `bma600` (

`BLQ_NOSSO_NUM` Decimal (19),

`BLQ_DIG_NOS_NUM` char (18),

`BLQ_TAM_NN` Decimal (3),

`BLQ_COD_BCO` Decimal (5),

`BLQ_DIG_BCO` Decimal (3),

`BLQ_COD_AGE` Decimal (6),

`BLQ_DIG_AGE` Decimal (3),

`BLQ_COD_CONV` Decimal (20),

`BLQ_LIN_DIG` varchar (450),

`BLQ_COD_BAR` varchar (450),

`BLQ_LOC_PAG` varchar (900),

`BLQ_COD_CED` Decimal (19),

`BLQ_DIG_CED` Decimal (3),

`BLQ_CNPJ_CED` Decimal (17),

`BLQ_NOME_CED` varchar (360),

`BLQ_DT_DOC` Decimal (10),

`BLQ_DT_PROC` Decimal (10),

`BLQ_DT_VEN` Decimal (10),

`BLQ_QTD_MOE` Decimal (12),

`BLQ_VALOR` Decimal (14),

`BLQ_VAL_DOC` Decimal (14),

`BLQ_VAL_DES` Decimal (14),

`BLQ_VAL_DED` Decimal (14),

`BLQ_VAL_MORA` Decimal (14),

`BLQ_VAL_ACR` Decimal (14),

`BLQ_VAL_COB` Decimal (14),

`BLQ_TPO_INS` char (9),

`BLQ_CNPJ_CPF_SAC` Decimal (17),

`BLQ_NOME_SAC` varchar (360),

`BLQ_END_SAC` varchar (360),

`BLQ_BAI_SAC` varchar (270),

`BLQ_CEP_SAC` Decimal (7),

`BLQ_CCEP_SAC` Decimal (5),

`BLQ_CID_SAC` varchar (270),

`BLQ_EST_SAC` varchar (18),

`BLQ_NUM_DOC` Decimal (12),

`BLQ_ESP_DOC` varchar (18),

`BLQ_ACEITE` char (9),

`BLQ_USO_BCO` varchar (225),

`BLQ_COD_CART` Decimal (5),

`BLQ_MOEDA` varchar (27),

`BLQ_NOME_AVAL` varchar (360),

`BLQ_COD_EMP` Decimal (7),

`BLQ_COD_POR` Decimal (7),

`BLQ_PER_MULTA` Decimal (8),

`BLQ_COD_EST` Decimal (5),

`BLQ_OBS_RET` varchar (2295)

);

So first row 17,0 becomes 18 becomes 19 .. “

But if I use “Backup as SQL-dump” I get (2nd time)

CREATE TABLE `bma600` (

`BLQ_NOSSO_NUM` decimal(18,0) DEFAULT NULL,

`BLQ_DIG_NOS_NUM` char(6) DEFAULT NULL,

`BLQ_TAM_NN` decimal(2,0) DEFAULT NULL,

`BLQ_COD_BCO` decimal(4,0) DEFAULT NULL,

`BLQ_DIG_BCO` decimal(2,0) DEFAULT NULL,

`BLQ_COD_AGE` decimal(5,0) DEFAULT NULL,

`BLQ_DIG_AGE` decimal(2,0) DEFAULT NULL,

`BLQ_COD_CONV` decimal(19,0) DEFAULT NULL,

`BLQ_LIN_DIG` varchar(150) DEFAULT NULL,

`BLQ_COD_BAR` varchar(150) DEFAULT NULL,

`BLQ_LOC_PAG` varchar(300) DEFAULT NULL,

`BLQ_COD_CED` decimal(18,0) DEFAULT NULL,

`BLQ_DIG_CED` decimal(2,0) DEFAULT NULL,

`BLQ_CNPJ_CED` decimal(16,0) DEFAULT NULL,

`BLQ_NOME_CED` varchar(120) DEFAULT NULL,

`BLQ_DT_DOC` decimal(9,0) DEFAULT NULL,

`BLQ_DT_PROC` decimal(9,0) DEFAULT NULL,

`BLQ_DT_VEN` decimal(9,0) DEFAULT NULL,

`BLQ_QTD_MOE` decimal(11,0) DEFAULT NULL,

`BLQ_VALOR` decimal(13,0) DEFAULT NULL,

`BLQ_VAL_DOC` decimal(13,0) DEFAULT NULL,

`BLQ_VAL_DES` decimal(13,0) DEFAULT NULL,

`BLQ_VAL_DED` decimal(13,0) DEFAULT NULL,

`BLQ_VAL_MORA` decimal(13,0) DEFAULT NULL,

`BLQ_VAL_ACR` decimal(13,0) DEFAULT NULL,

`BLQ_VAL_COB` decimal(13,0) DEFAULT NULL,

`BLQ_TPO_INS` char(3) DEFAULT NULL,

`BLQ_CNPJ_CPF_SAC` decimal(16,0) DEFAULT NULL,

`BLQ_NOME_SAC` varchar(120) DEFAULT NULL,

`BLQ_END_SAC` varchar(120) DEFAULT NULL,

`BLQ_BAI_SAC` varchar(90) DEFAULT NULL,

`BLQ_CEP_SAC` decimal(6,0) DEFAULT NULL,

`BLQ_CCEP_SAC` decimal(4,0) DEFAULT NULL,

`BLQ_CID_SAC` varchar(90) DEFAULT NULL,

`BLQ_EST_SAC` varchar(6) DEFAULT NULL,

`BLQ_NUM_DOC` decimal(11,0) DEFAULT NULL,

`BLQ_ESP_DOC` varchar(6) DEFAULT NULL,

`BLQ_ACEITE` char(3) DEFAULT NULL,

`BLQ_USO_BCO` varchar(75) DEFAULT NULL,

`BLQ_COD_CART` decimal(4,0) DEFAULT NULL,

`BLQ_MOEDA` varchar(9) DEFAULT NULL,

`BLQ_NOME_AVAL` varchar(120) DEFAULT NULL,

`BLQ_COD_EMP` decimal(6,0) DEFAULT NULL,

`BLQ_COD_POR` decimal(6,0) DEFAULT NULL,

`BLQ_PER_MULTA` decimal(7,0) DEFAULT NULL,

`BLQ_COD_EST` decimal(4,0) DEFAULT NULL,

`BLQ_OBS_RET` varchar(765) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Two problems here

1) decimal length increments by one. This we have never noticed before. We will check why it happens.

2) char/varchar length gets multiplied with 3. This a known issue if charset is UTF8. The statement we use for “export as SQL” returns the *byte-length* – not the *character length*. I don’t remember internals right now. But I remember clearly that I filed a bug report to MySQL about it.

Actually we only introduced “Export as SQL” very reluctantly many years agp. phpMyAdmin has/had the same and it was a massive request by users taht SQLyog should have exactly the same opton. Maybe we should change it so that it executes SHOW CREATE TABLE. But .. but .. please let us check internals and see if can we can remember something from old days.

Is it posible that you could use “Backup as SQL-dump” for as long?

March 12, 2015 at 1:50 pm #35240

peterlaursen

Participant

For “Export as SQL” we don’t execute SHOW CREATE TABLE but SHOW FULL FIELDS FROM ..

Try “SHOW FULL FIELDS FROM `bma600`;”



Field             Type           Collation        Null    Key     Default  Extra   Privileges                       Comment  

 

 

 

 

 

 

 

 

BLQ_NOSSO_NUM     decimal(18,0)  (NULL)           YES             (NULL)           select,insert,update,references           

BLQ_DIG_NOS_NUM   char(6)        utf8_general_ci  YES             (NULL)           select,insert,update,references           

BLQ_TAM_NN        decimal(2,0)   (NULL)           YES             (NULL)           select,insert,update,references           

BLQ_COD_BCO       decimal(4,0)   (NULL)           YES             (NULL)           select,insert,update,references           

BLQ_DIG_BCO       decimal(2,0)   (NULL)           YES             (NULL)           select,insert,update,references           

BLQ_COD_AGE       decimal(5,0)   (NULL)           YES             (NULL)           select,insert,update,references           

BLQ_DIG_AGE       decimal(2,0)   (NULL)           YES             (NULL)           select,insert,update,references           

BLQ_COD_CONV      decimal(19,0)  (NULL)           YES             (NULL)           select,insert,update,references           

BLQ_LIN_DIG       varchar(150)   utf8_general_ci  YES             (NULL)           select,insert,update,references           

BLQ_COD_BAR       varchar(150)   utf8_general_ci  YES             (NULL)           select,insert,update,references           

BLQ_LOC_PAG       varchar(300)   utf8_general_ci  YES             (NULL)           select,insert,update,references           

BLQ_COD_CED       decimal(18,0)  (NULL)           YES             (NULL)           select,insert,update,references           

BLQ_DIG_CED       decimal(2,0)   (NULL)           YES             (NULL)           select,insert,update,references           

BLQ_CNPJ_CED      decimal(16,0)  (NULL)           YES             (NULL)           select,insert,update,references           

BLQ_NOME_CED      varchar(120)   utf8_general_ci  YES             (NULL)           select,insert,update,references           

BLQ_DT_DOC        decimal(9,0)   (NULL)           YES             (NULL)           select,insert,update,references           

BLQ_DT_PROC       decimal(9,0)   (NULL)           YES             (NULL)           select,insert,update,references           

BLQ_DT_VEN        decimal(9,0)   (NULL)           YES             (NULL)           select,insert,update,references           

BLQ_QTD_MOE       decimal(11,0)  (NULL)           YES             (NULL)           select,insert,update,references           

BLQ_VALOR         decimal(13,0)  (NULL)           YES             (NULL)           select,insert,update,references           

BLQ_VAL_DOC       decimal(13,0)  (NULL)           YES             (NULL)           select,insert,update,references           

BLQ_VAL_DES       decimal(13,0)  (NULL)           YES             (NULL)           select,insert,update,references           

BLQ_VAL_DED       decimal(13,0)  (NULL)           YES             (NULL)           select,insert,update,references           

BLQ_VAL_MORA      decimal(13,0)  (NULL)           YES             (NULL)           select,insert,update,references           

BLQ_VAL_ACR       decimal(13,0)  (NULL)           YES             (NULL)           select,insert,update,references           

BLQ_VAL_COB       decimal(13,0)  (NULL)           YES             (NULL)           select,insert,update,references           

BLQ_TPO_INS       char(3)        utf8_general_ci  YES             (NULL)           select,insert,update,references           

BLQ_CNPJ_CPF_SAC  decimal(16,0)  (NULL)           YES             (NULL)           select,insert,update,references           

BLQ_NOME_SAC      varchar(120)   utf8_general_ci  YES             (NULL)           select,insert,update,references           

BLQ_END_SAC       varchar(120)   utf8_general_ci  YES             (NULL)           select,insert,update,references           

BLQ_BAI_SAC       varchar(90)    utf8_general_ci  YES             (NULL)           select,insert,update,references           

BLQ_CEP_SAC       decimal(6,0)   (NULL)           YES             (NULL)           select,insert,update,references           

BLQ_CCEP_SAC      decimal(4,0)   (NULL)           YES             (NULL)           select,insert,update,references           

BLQ_CID_SAC       varchar(90)    utf8_general_ci  YES             (NULL)           select,insert,update,references           

BLQ_EST_SAC       varchar(6)     utf8_general_ci  YES             (NULL)           select,insert,update,references           

BLQ_NUM_DOC       decimal(11,0)  (NULL)           YES             (NULL)           select,insert,update,references           

BLQ_ESP_DOC       varchar(6)     utf8_general_ci  YES             (NULL)           select,insert,update,references           

BLQ_ACEITE        char(3)        utf8_general_ci  YES             (NULL)           select,insert,update,references           

BLQ_USO_BCO       varchar(75)    utf8_general_ci  YES             (NULL)           select,insert,update,references           

BLQ_COD_CART      decimal(4,0)   (NULL)           YES             (NULL)           select,insert,update,references           

BLQ_MOEDA         varchar(9)     utf8_general_ci  YES             (NULL)           select,insert,update,references           

BLQ_NOME_AVAL     varchar(120)   utf8_general_ci  YES             (NULL)           select,insert,update,references           

BLQ_COD_EMP       decimal(6,0)   (NULL)           YES             (NULL)           select,insert,update,references           

BLQ_COD_POR       decimal(6,0)   (NULL)           YES             (NULL)           select,insert,update,references           

BLQ_PER_MULTA     decimal(7,0)   (NULL)           YES             (NULL)           select,insert,update,references           

BLQ_COD_EST       decimal(4,0)   (NULL)           YES             (NULL)           select,insert,update,references           

BLQ_OBS_RET       varchar(765)   utf8_general_ci  YES             (NULL)           select,insert,update,references

March 12, 2015 at 1:51 pm #35241

peterlaursen
Participant

So the char/varchar issue is inherited from MySQl. The decimal issue looks like a bug in SQLyog.
March 12, 2015 at 2:12 pm #35242

peterlaursen
Participant

More precisely SHOW FULL FIELDS does not have any information about the storage engine. So we assume *worst case* for strings (what was utf8 – using up to 3 bytes per character – before utf8mb4 was introduced). We should probably query I_S about the storage engine for the column or completely change the logic.

The whole code dates back to MySQL 3.23/4.0 -dasy before unicode was introduced and I_S did not exist. The only change ws taht with MySQL 4.1 we added the worst case* assumption. Code/logicis museum-worthy relly
March 12, 2015 at 6:09 pm #35243

Diuter
Member

The problem that i’m really having here is that I use this table as a temp for some crystal reports.

And randomly I get a crash saying that there’s no available memory.

I think that Crystal Reports 9 uses internally the same method as the SQLYog uses, that’s why sometimes that crash happens.

What I did to solve this was change the varchar field length to 100, down from 255.

I know it’s not the best way to deal with this, but stopped the crashes 😀

Thanks for the quick answers.

Author

Posts

Viewing 6 reply threads

You must be logged in to reply to this topic.

Search Forums

Recent Topics

Recent Replies