MySQL Bugs: #73815: Workbench Does not detects space/null between strings

Bug #73815	Workbench Does not detects space/null between strings
Submitted:	5 Sep 2014 10:21	Modified:	12 Feb 2016 9:38
Reporter:	Shahriyar Rzayev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S1 (Critical)
Version:	6.1.7	OS:	Linux (Ubuntu 14.04)
Assigned to:		CPU Architecture:	Any

Description:
Dear experts,
I have an interesting situation will provide with print screens:

Sample table:

CREATE TABLE IF NOT EXISTS `google_keywords` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(110) NOT NULL,
  `searches` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=801 ;

Sample data:

INSERT INTO `google_keywords` (`id`, `title`, `searches`) VALUES
(1, 'G\0e\0o\0r\0g\0e\0 \0W\0 \0B\0u\0s\0h', 170),
(2, 'G\0e\0o\0r\0g\0e\0 \0M\0a\0r\0t\0i\0n', 480),
(3, 'G\0e\0o\0r\0g\0 \0C\0a\0n\0t\0o\0r', 10),
(4, 'G\0e\0o\0r\0g\0e\0 \0F\0o\0x', 10),
(5, 'G\0e\0o\0r\0g\0e\0 \0M\0i\0k\0a\0n', 10),
(6, 'G\0e\0o\0r\0g\0e\0 \0B\0 \0M\0c\0C\0l\0e\0l\0l\0a\0n', 10),
(7, 'G\0e\0o\0r\0g\0e\0 \0F\0 \0K\0e\0n\0n\0a\0n', 10),
(8, 'G\0e\0o\0r\0g\0 \0F\0o\0r\0s\0t\0e\0r', 10),
(9, 'G\0e\0o\0r\0g\0e\0 \0P\0 \0B\0u\0r\0d\0e\0l\0l', 10),
(10, 'G\0e\0o\0r\0g\0e\0 \0I\0 \0o\0f\0 \0G\0r\0e\0e\0c\0e', 10),
(11, 'G\0e\0o\0r\0g\0e\0 \0T\0h\0o\0m\0a\0s\0 \0C\0o\0k\0e\0r', 0),
(12, 'A\0n\0n\0e\0 \0F\0r\0a\0n\0k', 390),
(13, 'A\0n\0n\0e\0 \0B\0o\0l\0e\0y\0n', 210),
(14, 'A\0n\0n\0e\0 \0o\0f\0 \0D\0e\0n\0m\0a\0r\0k', 10),
(15, 'A\0n\0n\0 \0B\0a\0n\0n\0o\0n', 10);

When i select from table with Workbench 6.1.7 there is nothing terrible with data:
Link to print screen:

http://s019.radikal.ru/i622/1409/af/ea86f326e38e.png

In fact from terminal it shows that there is a space between strings:

mysql> select * from google_keywords;
+-----+-----------------------------------------+----------+
| id  | title                                   | searches |
+-----+-----------------------------------------+----------+
|   1 | G e o r g e   W   B u s h               |      170 |
|   2 | G e o r g e   M a r t i n               |      480 |
|   3 | G e o r g   C a n t o r                 |       10 |
|   4 | G e o r g e   F o x                     |       10 |
|   5 | G e o r g e   M i k a n                 |       10 |
|   6 | G e o r g e   B   M c C l e l l a n     |       10 |
|   7 | G e o r g e   F   K e n n a n           |       10 |
|   8 | G e o r g   F o r s t e r               |       10 |
|   9 | G e o r g e   P   B u r d e l l         |       10 |
|  10 | G e o r g e   I   o f   G r e e c e     |       10 |
|  11 | G e o r g e   T h o m a s   C o k e r   |        0 |
|  12 | A n n e   F r a n k                     |      390 |
|  13 | A n n e   B o l e y n                   |      210 |
|  14 | A n n e   o f   D e n m a r k           |       10 |
|  15 | A n n   B a n n o n                     |       10 |

So Workbench does not detect such situation and it took me several time to figure out what is going on. Sample data extracted after examining from mysql console.

Another thing that, i want to show that in "Form Editor" with result of select * it show nothing:

http://s010.radikal.ru/i313/1409/8a/d70c1f483848.png

And in conclusion i want to show print screen from "Open Value in Editor":

http://s011.radikal.ru/i316/1409/fc/039a8d704823.png

It detects values with 'null'.

How to repeat:
All steps provided in description section.

Suggested fix:
Maybe it must show that string contains some nulls or something other error or warning.

Your data is wrong, you have your table defined as UTF8, but the data looks like UTF16.
Either make your table UTF16 or fix your data to be UTF8.
Otherwise, you'll have problems in places other than in Workbench.

Please see @Alfredo comment. Thanks.

Dear all,

Created table with utf16 and reinserted provided data:

mysql> show create table google_keywords;
+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table           | Create Table                                                                                                                                                                                                           |
+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| google_keywords | CREATE TABLE `google_keywords` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(110) NOT NULL,
  `searches` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=801 DEFAULT CHARSET=utf16 |
+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,03 sec)

Result is unchanged.
Still can not review data from "Form Editor" , it does not show the result with "Spaces" and there is "NULL"s from "Open Value In Editor".

From MySQL Console everything is OK.

Convert your data to UTF8 first, it's not supposed to have embedded 0s. The MySQL cmdline client output is not OK either, it's showing spaces as placeholders for the extraneous \0 characters.

And it is not possible to show space placeholders in Workbench as MySQL console?

The problem is: "Workbench shows this data as normal but in fact it is not a normal data as you say".

This is not a bug in MySQL Workbench, since it should not forcefully show incorrect data as correct.