Unsupported Screen Size: The viewport size is too small for the theme to render properly.

Bug: Exporting Table Data As Sql On Varchar Fields.

forums forums SQLyog SQLyog: Bugs / Feature Requests Bug: Exporting Table Data As Sql On Varchar Fields.

Tagged: , ,

This topic contains 5 replies, has 0 voices, and was last updated by  Diuter 4 years, 6 months ago.

  • Author
    Posts
  • #13331

    Diuter
    Member

    Hello everyone,

     

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

     

    Here is the table I’m exporting:

    table.jpg

     

    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

  • #35238

    Hi

     

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

     

    Regards

  • #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?

  • #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
  • #35241

    peterlaursen
    Participant

    So the char/varchar issue is inherited from MySQl.  The decimal issue looks like a bug in SQLyog.

  • #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

  • #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.

You must be logged in to reply to this topic.