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 is empty.
Viewing 6 reply threads
  • 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.

Viewing 6 reply threads
  • You must be logged in to reply to this topic.