| Bug #24201 | inserting data using encode() failed | ||
|---|---|---|---|
| Submitted: | 10 Nov 2006 20:17 | Modified: | 21 Nov 2006 11:08 |
| Reporter: | [ name withheld ] | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 4.1.18 | OS: | |
| Assigned to: | CPU Architecture: | Any | |
[11 Nov 2006 13:08]
Valeriy Kravchuk
Please, send the results of SHOW CREATE TABLE users; and show variables like 'char%'; As your test string is 7 characters long and 3*7=21 > 20, I suspect character sets-related problem.
[11 Nov 2006 21:48]
[ name withheld ]
Thank you. Here are they: mysql> show create table users; +-------+----------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -----------------------------------------------------------------------------+ | +-------+----------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -----------------------------------------------------------------------------+ | users | CREATE TABLE `users` ( `id` int(10) unsigned NOT NULL auto_increment, `user_name` varchar(30) NOT NULL default '', `disp_name` varchar(50) NOT NULL default '', `password` varchar(120) NOT NULL default '', `email` varchar(50) NOT NULL default '', `created` datetime NOT NULL default '0000-00-00 00:00:00', `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_ TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | +-------+----------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -----------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show variables like 'char%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 7 rows in set (0.01 sec) I have already enlarged the password field to varchar(120) but still not working. Thanks again for your help.
[20 Nov 2006 16:24]
[ name withheld ]
I have been waiting for an answer and I am running out of time. So, could you please give my any suggestion what I should do to make things work, event not the best way ? In the 12.9.2. Encryption and Compression Functions of the MySQL Reference Manual, it says 'The encryption and compression functions return binary strings. For many of these functions, the result might contain arbitrary byte values. If you want to store these results, use a BLOB column rather than a CHAR or (before MySQL 5.0.3) VARCHAR column to avoid potential problems with trailing space removal that would change data values. '. It is not very clear to me if encode() is one of the 'encryption and compression functions' mentioned as above, if so, shall I use a BLOB type for my password field ? Will it be too big for a maximum of 20 character field. Because I set the table to utf-8, the field might need more than 20 characters but BLOB still seems too big. I am not very experienced in using MySQL. Can you help me? Thank you!
[21 Nov 2006 11:08]
Valeriy Kravchuk
Sorry for a delay with this bug report. Yes, you had found a proper quote in the manual. Look:
mysql> CREATE TABLE `users` ( `id` int(10) unsigned NOT NULL auto_increment,
`user_name` varchar(30) NOT NULL default '', `disp_name` varchar(50) NOT NU
LL default '', `password` varchar(120) NOT NULL default '', `email` varchar
(50) NOT NULL default '', `created` datetime NOT NULL default '0000-00-00 00:
00:00', `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like 'char%';
+--------------------------+--------------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /home/openxs/dbs/4.1/share/mysql/charsets/ |
+--------------------------+--------------------------------------------+
7 rows in set (0.00 sec)
mysql> select encode('test123', '8887775553331111');
+---------------------------------------+
| encode('test123', '8887775553331111') |
+---------------------------------------+
| ☺дhўх^k |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> select length(encode('test123', '8887775553331111'));
+-----------------------------------------------+
| length(encode('test123', '8887775553331111')) |
+-----------------------------------------------+
| 7 |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> select hex(encode('test123', '8887775553331111'));
+--------------------------------------------+
| hex(encode('test123', '8887775553331111')) |
+--------------------------------------------+
| 01E468A2F55E6B |
+--------------------------------------------+
1 row in set (0.00 sec)
But it is a binary string, so:
mysql> insert into users (user_name, password) values('abc', encode('test123',
'8887775553331111'));
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level: Warning
Code: 1265
Message: Data truncated for column 'password' at row 1
1 row in set (0.00 sec)
mysql> select * from users;
+----+-----------+-----------+----------+-------+---------------------+---------
------------+
| id | user_name | disp_name | password | email | created | lastmodi
fied |
+----+-----------+-----------+----------+-------+---------------------+---------
------------+
| 1 | abc | | ☺ | | 0000-00-00 00:00:00 | 2006-11-
21 12:19:46 |
+----+-----------+-----------+----------+-------+---------------------+---------
------------+
1 row in set (0.01 sec)
as you already described. But you should use tinyblob datatype, for example:
mysql> delete from users;
Query OK, 1 row affected (0.00 sec)
mysql> alter table users modify password tinyblob;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into users (user_name, password) values('abc', encode('test123',
'8887775553331111'));
Query OK, 1 row affected (0.00 sec)
mysql> select * from users;
+----+-----------+-----------+----------+-------+---------------------+---------
------------+
| id | user_name | disp_name | password | email | created | lastmodi
fied |
+----+-----------+-----------+----------+-------+---------------------+---------
------------+
| 2 | abc | | ☺дhўх^k | | 0000-00-00 00:00:00 | 2006-11-
21 12:20:18 |
+----+-----------+-----------+----------+-------+---------------------+---------
------------+
1 row in set (0.00 sec)
mysql> show table status like 'users'\G
*************************** 1. row ***************************
Name: users
Engine: MyISAM
Version: 9
Row_format: Dynamic
Rows: 1
Avg_row_length: 28
Data_length: 28
Max_data_length: 4294967295
Index_length: 2048
Data_free: 0
Auto_increment: 3
Create_time: 2006-11-21 12:20:16
Update_time: 2006-11-21 12:20:18
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
Note correct value, and row size - it is not large. So, this is not a bug. You should just use proper datatype to store encode() results.
Description: I hava a user table which encludes a password field varchar(20). I used to insert password using encode('xxx', 'xxxxxxxx') and it worked perfectly in MYSQL 3 version. But now I am using MySQL 4.1.18, The same insertion failed and gave warning: 'data truncated for colum 'password'... I tried to enlarge the field length but didn't work. Anything changed in the 4.1.18 version? Thanks for help. How to repeat: test case: insert into users (user_name, password) values ('abc', encode('test123', '8887775553331111')); result: Version 3.23.55: the password inserted correctly no warning Version 4.1.18: the record get inserted but the password field was blank. warning saying -data truncated for column 'password'