BUG: SQL query

[crivera]

Found this weird bug when running an SQL query:

(case sensitive)

SELECT * FROM table WHERE Id=1;

The above will display (square box) in the column header in the result tab.

The column headers are displayed properly if you change any of the UPPER CASE word in the query or if you add to SQL query (see below).

(case sensitive)

SELECT * FROM table WHERE Id=1 ORDER BY Id;

or

SELECT * FROM table WHERE Id=1 OR Id=2;

The above will display the column headers properly.

:huh: Enjoy 😀

[Ritesh]

Can you upload screenshots?

Information about the table structure would be helpful.

[crivera]

1 of 2 screenshots

[crivera]

2 of 2 screenshots

Lemme know if you need additional info.

[peterlaursen]

I believe that one possibiliy is that what is here displayed as squares are nonprintable characters (ansi 0-32). They display like that in many programs, for instance Notebook. I have no idea of how they get there …

[peterlaursen]

Here a jpg-file opened in Notebook and a hex-editor. Actually here a chr(0) displays as but chr(1) for instance shows as . and many more.

[peterlaursen]

@crivera

BTW – I have experienced something similar once with some “pop-upkiller” or “adblocker”. Try to disable all you got of this ..

Attached pic show one of my programs displaying something similar, where it should display some graphics and text. It is the Ad-killer of Norton Internet Security that does this.

[crivera]

Note that both queries are the same from Image 1 & 2. However, the “RESULT” is different because of the column header information.

The only difference is the SELECT statement:

UPPER CASE in the 1st query

LOWER CASE in the 2nd query.

I could change all my SQL queries to lower case … 😉

[Ritesh]

Can you send me the table structure and sample data?

[crivera]

Just emailed you the information you requested.

[Ritesh]

Yup I have received it. I will work on it as soon as possible.

[Ritesh]

Very strange. It works for me.

[Ritesh]

This one too. 😮

Can you tell me your MySQL and Windows versions?

[peterlaursen]

I have two comments:

1) I can see from your screenshots that the SQLyog version is not the most recent. Which one? You should try 4.07.

2) I also can see that the string-types are displayed by your SQLyog as BLOB. Now, SQLyog display does not distinguish between BLOB and TEXT types, so I guess that they are defined as TEXTs (??). But do you need so many characters as a TEXT can hold ? Won't a varchar(255) do ? And BTW char/varchars are more effective with MySQL than are TEXTs.

You could easily make a copy of your database with SQLyog, use the ALTER TABLE function to convert TEXTs to VARCHARs an see if the problem persists. I may sound as a crazy idea, but it'll only take 5 minutes to try. And I think we are getting at the point where we need more information.

You also should try DUMPing your DB and importing it. That should fix charset/collation related problems with the data! Are the data created with your current MySQL-version? The reason that Ritesh can't reproduce your problem could simply be because data have been DUMPed and imported!

[crivera]

I will try the new version and see if the problem is fixed.

Quote:

2) I also can see that the string-types are displayed by your SQLyog as BLOB. Now, SQLyog display does not distinguish between BLOB and TEXT types, so I guess that they are defined as TEXTs (??). But do you need so many characters as a TEXT can hold ? Won't a varchar(255) do ? And BTW char/varchars are more effective with MySQL than are TEXTs.

Yes, I could have used varchar(255) however from the MySQL manual, MySQL would have converted those fields to TEXT anyways.

Quote:

[NATIONAL] VARCHAR(M) [BINARY]

A variable-length string. M represents the maximum column length. The range of M is 0 to 255 characters (1 to 255 prior to MySQL 4.0.2). Note: Trailing spaces are removed when VARCHAR values are stored, which differs from the standard SQL specification. From MySQL 4.1.0 on, a VARCHAR column with a length specification greater than 255 is converted to the smallest TEXT type that can hold values of the given length. For example, VARCHAR(500) is converted to TEXT, and VARCHAR(200000) is converted to MEDIUMTEXT. This is a compatibility feature. However, this conversion affects trailing-space removal. VARCHAR is shorthand for CHARACTER VARYING. As of MySQL 4.1.2, the BINARY attribute is shorthand for specifying the binary collation of the column character set. Before 4.1.2, BINARY attribute causes the column to be treated as a binary string. In either case, sorting and comparisons become case sensitive.

[peterlaursen]

I believe you are wrong!

Quote:

From MySQL 4.1.0 on, a VARCHAR column with a length specification greater than 255 is converted to the smallest TEXT type that can hold values of the given length.

with MySQL 4.1.x varchars bigger than 255 characters ONLYare converted to text. With MySQL ver 5.x “true varchars” can hold approx. 55.000 characters.

The important difference between TEXT and CHAR/VARCHAR is that char/varchar data are stored in the table itself. TEXT values themselves are not stored in the table. Only a pointer is stored. That's why char/varchar is more efficient than TEXT.

[crivera]

Tried the new version 4.07 and got the same results.

I understand that the cell content may contain non-displayable characters. However, I do not think that this should be the case for the column names.

Here are the 4 queries from the screenshots. Note: All 4 queries are the same and should produce the same results. Query #2: changed “SELECT” to “select”. Query #4: Changed “FROM” to “from”.

SELECT * FROM hhdesigns_pages LEFT JOIN hhdesigns__templates ON hhdesigns_pages.Template=hhdesigns__templates.Template LEFT JOIN hhdesigns__templatescontent ON hhdesigns__templates.TemplateContent=hhdesigns__templatescontent.TemplateContent WHERE hhdesigns_pages.Page=1001 ORDER BY hhdesigns__templates.TemplateIndex ASC;

select * FROM hhdesigns_pages LEFT JOIN hhdesigns__templates ON hhdesigns_pages.Template=hhdesigns__templates.Template LEFT JOIN hhdesigns__templatescontent ON hhdesigns__templates.TemplateContent=hhdesigns__templatescontent.TemplateContent WHERE hhdesigns_pages.Page=1001 ORDER BY hhdesigns__templates.TemplateIndex ASC;

SELECT * FROM hhdesigns_pages LEFT JOIN hhdesigns__templates ON hhdesigns_pages.Template=hhdesigns__templates.Template LEFT JOIN hhdesigns__templatescontent ON hhdesigns__templates.TemplateContent=hhdesigns__templatescontent.TemplateContent WHERE hhdesigns_pages.Page=1001 ORDER BY hhdesigns__templates.TemplateIndex ASC;

SELECT * from hhdesigns_pages LEFT JOIN hhdesigns__templates ON hhdesigns_pages.Template=hhdesigns__templates.Template LEFT JOIN hhdesigns__templatescontent ON hhdesigns__templates.TemplateContent=hhdesigns__templatescontent.TemplateContent WHERE hhdesigns_pages.Page=1001 ORDER BY hhdesigns__templates.TemplateIndex ASC;

As I mentioned in my earlier post, I could change my SQL syntax to lowercase and get the correct column names.

Image 1 of 4

[crivera]

Image 2 of 4

[crivera]

Image 3 of 4

[crivera]

image 4 of 4

[peterlaursen]

Quote:

I understand that the cell content may contain non-displayable characters. However, I do not think that this should be the case for the column names.

I totally agree! And this is crazy. And much more since it is not reproducable! But my point just was that Win32 API's (at least in some cases) will display nonprintable characters as squares. Now, column headers are graphics and are built by Win32 APIs contained in system dll's. So that/those dll(s) must be sent nonprintable characters (or it must “think” that it is getting non-printable character)s. At least that is one possibility. But I still don't have any idea of how they get there!

Do you have a chance to try with some other computer having MySQL installed? I believe it is a problem with your system only! What is the OS-version? And has that OS-version been installed over a previous OS-version?

[peterlaursen]

Hey … have a look at your own screen-dumps!

Even numerical colums sometimes display as BLOBs.

I believe your MySQL installation or at least the DB is SICK!

What does some other clients (command-line client, Query Browser) say?

SQLyog does not AT ALL change the query that you enter in the SQL pane. It simply is sent to the server without any change.

[crivera]

Ok, I ran the queries from the MySQL client tool (**sigh**) and saved the results to text files. Ran a file compare and the results were identical.

FYI: I am running XP Pro Sp2, MySQL 4.1.11, Apache 2.0.53, IIS 6.0

I will upgrade to MySQL 4.1.12 and keep you posted.

[peterlaursen]

Quote:

Apache 2.0.53, IIS 6.0

at the same time ?? and maybe even php installed as a module with both of them ?

I might have asked about this before, but direct/tunnelled connection ??

But no error with command-line client ? really strange!

But if LOWERCASE/UPPERCASE keywords was a common issue with SQLyog it would have been reported many times before!

[peterlaursen]

actually you can have 4.1.13 from here

http://dev.mysql.com/get/Downloads/MySQL-4…/mirrors/mysql/

[crivera]

Quote:

QUOTE

Apache 2.0.53, IIS 6.0

at the same time ?? and maybe even php installed as a module with both of them ?

I might have asked about this before, but direct/tunnelled connection ??

Actually I run IIS on port 80 and Apache on port 8080 which allows me to test my PHP scripts on either type of server.

Anyways, I think that this issue is not going anywhere. I am pretty sure that nothing is wrong with my system nor my database setup.

I am also pretty sure that any reserved word in the query statement is case insensitive however for some obscure reason your software does not think so.

The only thing I can add is that this bug only occurs during some complex SQL queries that I have written (i.e. joins, unions, and subqueries) when all the reserved words in the query are in UPPERCASE. It's not a big deal — I just change one word in my query and I ready to look at the results. 😀

BTW, your software is really great and a significant improvement from what I have been using — phpMyAdmin. Keep up the good work.

[peterlaursen]

Quote:

I think that this issue is not going anywhere

At least here we agree! To get closer simply more information is needed! And no one seems to be able to find the “key” to that information.

But remeber: Ritesh was not able to reproduce the error with the same query and you data.

I would not mind testing too and a lot of people probably would not either …

Nobody else reported something similar. And I don't believe the querries are unusually complex.

[Ritesh]

What happens if you execute the query from MySQL Command Line Tool?

[Author]

Posts