[martin.lester]

Thanks Sudhindra, that solved my problem. Should have thought of that myself  🙄 

 

As for the ancient version of MySQL we use, we have wanted to move forward for 5+ years but those that make the decisions say the cost/risk analysis shows it doesn’t make sense. We have a vast amount of code with lots of SQL that would need to be tested with a new version and less than 1/2 the number of people on the team so it looks like we’ll be sticking with 5.1 for a while longer yet!

[martin.lester]

[attachment=1973:SQLyog_displaybug3.png]

 

The list view of the same display_bad column.

[martin.lester]

[attachment=1971:SQLyog_displaybug1.png]

[attachment=1972:SQLyog_displaybug2.png]

 

In the first image you can see where the text from the GROUP_CONCAT(CONCAT()) is getting truncated

[martin.lester]

This is definitely a bug in SQLyog only. All the data comes back correctly from the MySQL server. Also the data is correctly displayed in some places of the UI. The issue I have is how the “Grid View” displays the GROUP_CONCAT( CONCAT () ) field I have created. 

SELECT

    GROUP_CONCAT( CONCAT(title, ' (', testid, ')') ORDER BY title SEPARATOR ', ') AS display_bad,

    GROUP_CONCAT( title ORDER BY title SEPARATOR ', ') AS display_ok,

    CONCAT('123456789012345678901234567890123456789012345678901234567890-', title, ' (', testid, ')') AS `concat`

FROM test

GROUP BY

    category

This query produces 3 columns of data. The first column of data is truncated in the “Grid View” at exactly 64 characters for all rows. So for the first column I see exactly this (I wish I knew how to post an image here!):

display_bad

SOME-LONG-NAME-A (1), SOME-LONG-NAME-F (6), SOME-LONG-NAME-J (10            [134B]

SOME-LONG-NAME-B (2), SOME-LONG-NAME-G (7), SOME-LONG-NAME-K (11            [111B]

SOME-LONG-NAME-C (3), SOME-LONG-NAME-H (8), SOME-LONG-NAME-L (12            [ 65B]

SOME-LONG-NAME-D (4), SOME-LONG-NAME-I (9)                                  [ 42B]

SOME-LONG-NAME-E (5)                                                        [ 20B]

Note there are no … at the end of any of the fields. Also you can see that the truncation is happening at exactly 64 characters, wherever there are more than that nothing more is displayed however wide the field is made. Incidentally if I reduce the column width so less than 64 chars are visible I do start to get the … at the end of each line. The number of bytes displayed is correct.

 

If I change to “List View” I get the following displayed for that column:

display_bad

SOME-LONG-NAME-A (1), SOME-LONG-NAME-F (6), SOME-LONG-NAME-J (10), SOME-LONG-NAME-M (13), SOME-LONG-NAME-O (15), SOME-LONG-NAME-Q (17)

SOME-LONG-NAME-B (2), SOME-LONG-NAME-G (7), SOME-LONG-NAME-K (11), SOME-LONG-NAME-N (14), SOME-LONG-NAME-P (16)

SOME-LONG-NAME-C (3), SOME-LONG-NAME-H (8), SOME-LONG-NAME-L (12)

SOME-LONG-NAME-D (4), SOME-LONG-NAME-I (9)

SOME-LONG-NAME-E (5)

As you can see the data set in SQLyog is correct, it is just the way SQLyog is displaying that specific field in the “Grid View”.

 

This is how similar columns are displayed.First display_ok, a normal GROUP_CONCAT:

display_ok                                                                                                

SOME-LONG-NAME-A, SOME-LONG-NAME-F, SOME-LONG-NAME-J, SOME-LONG-NAME-M, SOME-LONG-NAME-O...[106B]

SOME-LONG-NAME-B, SOME-LONG-NAME-G, SOME-LONG-NAME-K, SOME-LONG-NAME-N, SOME-LONG-NAME-P   [ 88B]               

SOME-LONG-NAME-C, SOME-LONG-NAME-H, SOME-LONG-NAME-L                                       [ 52B]               

SOME-LONG-NAME-D, SOME-LONG-NAME-I                                                         [ 34B]               

SOME-LONG-NAME-E                                                                           [ 16B] 

Note, on the column that gets truncated the UI has correctly replaced the last few chars with … and if I extend the column width more these are replaced with the correct data so that all the data is visible. 

Lastly the `concat` column. this is a CONCAT with 81 characters.

concat                                                                             

123456789012345678901234567890123456789012345678901234567890-SOME-LONG-NAME-A (1)  

123456789012345678901234567890123456789012345678901234567890-SOME-LONG-NAME-B (2)  

123456789012345678901234567890123456789012345678901234567890-SOME-LONG-NAME-C (3)  

123456789012345678901234567890123456789012345678901234567890-SOME-LONG-NAME-D (4)  

123456789012345678901234567890123456789012345678901234567890-SOME-LONG-NAME-E (5)  

If I change the width of the column I do not get any … and I also do not see the byte size of the field. The field is simply chopped at the point where the column width stops. Unlike the other 2 columns this truncates the field to the pixel. i.e. I can get the “Grid View” to display half a character depending on the exact width of the column.

 

So in summary:

  GROUP_CONCAT : Displays as much data in the field as I have space on screen. When it truncates it places a … at the end. It always shows the number of bytes at the end of the Grid View cell.

  CONCAT : Displays as much of the data as I have space on the screen. Shows as much of the text as is it can fit in the cell truncating it to the pixel. It never shows the byte size.

  GROUP_CONCAT(CONCAT()) : Never shows more than the first 64 characters of any cell regardless of how wide the column is. When cell width is smaller than 64 characters it does truncate and places a … at the end. It always shows the correct number of bytes at the end of the cell.

 

It seems to me there is an odd UI behaviour here in the Grid View. If I extend the column to full screen width (100’s of chars wide) and I show a GROUP_CONCAT or a CONCAT that has many characters in it, I will see the full set of data for each cell. If I put a CONCAT inside a GROUP_CONCAT I will only see 64 characters.

 

 

p.s. In answer to your question we have increased group_concat_max_len on all DB servers to 1M. It shows in the column info for both GROUP_CONCAT columns as “Length:     1048576″. This query is nowhere near that limit!

[martin.lester]

I posted a note to your page. You can do that script in 1 query.

SELECT

CONCAT('ALTER TABLE ', information_schema.TABLES.TABLE_SCHEMA, '.', information_schema.TABLES.TABLE_NAME, ' ENGINE = InnoDB;') AS alter_engine,

CONCAT('ALTER TABLE ', information_schema.TABLES.TABLE_SCHEMA, '.', information_schema.TABLES.TABLE_NAME, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') AS alter_char

FROM information_schema.TABLES

WHERE

information_schema.TABLES.TABLE_SCHEMA = ''

AND (

information_schema.TABLES.ENGINE != 'InnoDB'

OR information_schema.TABLES.TABLE_COLLATION != 'utf8_general_ci'

)

ORDER BY

information_schema.TABLES.TABLE_SCHEMA

;

[martin.lester]

If I added the following to the previous SELECT:

    CONCAT(‘123456789012345678901234567890123456789012345678901234567890’, title, ‘ (‘, testid, ‘)’) AS `concat`

 

I ran the query on the command line with –column-type-info this is the column info:

 

Field   1:  `display_bad`

Catalog:    `def`

Database:   “

Table:      “

Org_table:  “

Type:       BLOB

Collation:  binary (63)

Length:     1048576

Max_length: 134

Decimals:   0

Flags:      BINARY

 

Field   2:  `display_ok`

Catalog:    `def`

Database:   “

Table:      “

Org_table:  “

Type:       BLOB

Collation:  latin1_swedish_ci (8)

Length:     1048576

Max_length: 106

Decimals:   0

Flags:

 

Field   3:  `concat`

Catalog:    `def`

Database:   “

Table:      “

Org_table:  “

Type:       VAR_STRING

Collation:  binary (63)

Length:     8192

Max_length: 80

Decimals:   31

Flags:      BINARY

 

In the Grid View UI the display_ok and `concat` columns display correctly i.e. by expanding the columns all the data is visible. However when I mix the 2 together I get something that truncates in the UI:

 

Type:       BLOB

Collation:  binary (63)

 

For a reason I can’t see. The collation of the GROUP_CONCAT blob has been changed from latin1_swedish_ci (8) to binary (63) because of the presence of the CONCAT. The Grid View is treating this oddly and is truncating the lines at 63 bytes.

 

 

I also tried adding COLLATE to the display_bad SELECT statement:

  GROUP_CONCAT( CONCAT(title, ‘ (‘, testid, ‘)’) ORDER BY title SEPARATOR ‘, ‘) COLLATE latin1_swedish_ci AS display_bad

 

However this gives the error:

  Error Code: 1253

  COLLATION ‘latin1_swedish_ci’ is not valid for CHARACTER SET ‘binary’

 

This is the same error I get if I try to add COLLATE to the `concat` field.

[martin.lester]

Thank you for the prompt reply.

 

SELECT

    GROUP_CONCAT( CONCAT(title, ‘ (‘, testid, ‘)’) ORDER BY title SEPARATOR ‘, ‘) AS display_bad,

    GROUP_CONCAT( title ORDER BY title SEPARATOR ‘, ‘) AS display_ok

FROM test

GROUP BY

    category

;

 

So why does display_bad get truncated and display_ok not get truncated?

Why does the internal CONCAT cause the fields to be truncated in the grid view?

 

I understand the data is there and that I can see it all if I open the blob viewer, but expanding the width of the column has no effect. The characters get cut at exactly 63 chars for display_bad, but for display_ok they all show fine. As I say this is ONLY for the grid view (and form view) the list view is fine.

 

If you post a link on how to upload images I could show you what I see on my screen.

 

One thing I noticed on your command line output is:

  Collation:  binary (63)

 

Is that 63 something I can change? It seems too much of a coincidence that the truncation happens at 63 bytes too.

[Author]

Posts