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:
None 
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
Description:
Hello,

I am trying to carry out an update statement on both phpMyAdmin and through a webpage through PHP. This table is only small with about 170 rows.

A select statement for the same id works and returns the item. But the UPDATE does not error and updates 0 rows.

I can't update the server MySQL version, but I can suggest to hosting provider need to. Tried looking for a suitable bug, but can't find anything.

Server: Localhost via UNIX socket
Server type: MySQL
Server version: 5.1.73-cll - MySQL Community Server (GPLv2)
Protocol version: 10
User: zackit@localhost

Row statistics
Format	dynamic
Collation	latin1_swedish_ci
Rows	134
Row length	76 B
Row size	191 B
Next autoindex	169
Creation	Apr 09, 2014 at 07:02 PM
Last update	Apr 09, 2014 at 07:57 PM
Last check	Apr 09, 2014 at 07:50 PM

How to repeat:
SELECT * FROM `passwords` WHERE id=165

   Works and returns the row with data

UPDATE passwords SET owner = 'Bills' WHERE `id`=165

    0 rows affected. (Query took 0.0002 sec)

-- phpMyAdmin SQL Dump
-- version 4.1.8
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Apr 09, 2014 at 08:30 PM
-- Server version: 5.1.73-cll
-- PHP Version: 5.4.23

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `zackit_HRMS`
--

-- --------------------------------------------------------

--
-- Table structure for table `passwords`
--

CREATE TABLE IF NOT EXISTS `passwords` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `typepassword` enum('Bills','Shopping','Personal','Email') NOT NULL DEFAULT 'Personal',
  `sitename` varchar(100) NOT NULL,
  `url` enum('Y','N') NOT NULL DEFAULT 'Y',
  `owner` enum('Dom','Steve','Household') NOT NULL DEFAULT 'Steve',
  `username` varchar(100) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  `password` varchar(100) DEFAULT NULL,
  `comments` text,
  `incomingserver` varchar(100) DEFAULT NULL,
  `outgoingserver` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sitename_2` (`sitename`,`owner`),
  UNIQUE KEY `passwordid_2` (`id`),
  KEY `passwordid` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=169 ;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Suggested fix:
Investigate why it fails. Happy to try other options.
[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.).