Bug #72296 | Select works but update does not | ||
---|---|---|---|
Submitted: | 9 Apr 2014 19:35 | Modified: | 10 Apr 2014 11:54 |
Reporter: | Stephen Vernon | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server | Severity: | S3 (Non-critical) |
Version: | 5.1.73-cll - MySQL Community Server (GPL | OS: | Linux |
Assigned to: | CPU Architecture: | Any | |
Tags: | Notworking, SELECT, UPDATE |
[9 Apr 2014 19:35]
Stephen Vernon
[9 Apr 2014 19:49]
MySQL Verification Team
Please provide a dump with some data insert to test. Thanks.
[9 Apr 2014 20:38]
Peter Laursen
I import your structure only dump and next INSERT INTO `bug72296`.`passwords` (`id`) VALUES ('165'); UPDATE passwords SET OWNER = 'Bills' WHERE `id`=165 -- 1 row(s) affected, 1 warning(s) -- Warning Code : 1265 -- Data truncated for column 'owner' at row 1 Next I execute: SHOW VARIABLES LIKE 'sql_mode'; -- what returns Variable_name Value ------------- ----------------------- sql_mode NO_AUTO_VALUE_ON_ZERO -- what was the sql_mode specified in the dump from php_mydadmin (all what I am doing is happening in a single thread/connection using SQLyog - not phpmyAdmin). Now look at table definition: .. owner enum('Dom','Steve','Household') .. .. how do you expect that you can insert the value ''Bills''?? For me it works as expected (truncation+error in 'non-strict mode'. My best guess is that phpMyAdmin does something weird with this warning and truncation. Now there are many different versions of phpMyAdmin! But even I experience a strange issue what I reproted here: http://bugs.mysql.com/bug.php?id=72297 (so what we have esperienced may be different manifestations of same bug - materializing differently on different client environments)
[9 Apr 2014 20:51]
Peter Laursen
I closed my own bug. It was an issue (or a feature, maybe! :-) ) with the editor control in the client I used. With your test case I can not prodcue anything unexpected on my environment. But I refuse to use phpMyAdmin!
[9 Apr 2014 21:13]
Stephen Vernon
Hello Peter and Godofredo, Thank you for your help, adding the enum option, seemed to get it to all work. Still not sure if this is a bug or not. Using PHP and PhpMyAdmin (as above I can't access MySQL directly on shared hosting account), with the initial setup, I didn't get any error returned it just says 0 rows updated. Possibly this is more a php issue, the way it uses MySQL rather than MySQL. Thank you again. Steve
[9 Apr 2014 21:30]
Peter Laursen
You should get an error (in 'strict mode') or a warning+truncation of the inserted data (in 'non-strict mode'). Undoubtedly MySQL returns it, but I don't know how phpMyAdmin communicates errors and warnings to users. There may be configuration settings for this. What happens if you execute an obviously erroneous atatement (example: "SELECT SELECT;"). Do you get an error returned?
[10 Apr 2014 11:54]
MySQL Verification Team
Hello Stephen, Thank you for the report. Imho - this is not a bug, you are trying to insert an invalid value into an ENUM (that is, in your case string 'Bills' not present in the list of permitted values i.e 'Dom','Steve','Household'), but SQL mode seems to be non-strict and hence it is set to the reserved enumeration value of 0, which is displayed as an empty string in string context. If strict SQL mode is enabled, attempts to insert invalid ENUM values result in an error e.g ERROR 1265 (01000): Data truncated for column 'ColumnName' at row <Number> Thanks, Umesh
[24 Jul 2014 6:04]
Abdul Majid P
Hai, My site having an issue, "Bad SubDomain SQL query". We are using PrestaShop, as per forum we tried this code in sql tab, 1.DROP TABLE 'PREFIX_subdomain'; then, 2.CREATE TABLE 'ps_subdomain' ( 'id_subdomain' int(10) unsigned NOT NULL auto_increment, 'name' varchar(16) NOT NULL, PRIMARY KEY ('id_subdomain') ) ENGINE=ENGINE_TYPE DEFAULT CHARSET=utf8; INSERT INTO 'ps_subdomain' ('id_subdomain', 'name') VALUES (1, 'www'); But it shows error like " #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''ps_subdomain'' at line 1" We are using, Server: Localhost via UNIX socket Server type: MySQL Server version: 5.1.73-cll - MySQL Community Server (GPLv2) Protocol version: 10 User: streampt@localhost Server charset: UTF-8 Unicode (utf8) Kindly Please help, our business is slows down last 2 days.
[24 Jul 2014 7:59]
Peter Laursen
@Abdul .. try instead: INSERT INTO `ps_subdomain` (`id_subdomain`, `name`) VALUES (1, 'www'); Note that identifiers are `backquoted` and strings are 'singlequoted' Besides this bugs system is not the right place to ask support. -- Peter -- not a MySQL/Oracle persn
[24 Jul 2014 8:40]
Abdul Majid P
@peter..,Thanks. We solved the issue.And that code run well. But the site remain the same state(Bad SubDomain SQL query.).