[MDEV-10289] update statement causes table row to partially update Created: 2016-06-26  Updated: 2016-07-03  Resolved: 2016-07-03

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Update
Affects Version/s: 5.5.47-galera
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Amin Ahmad Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Ubuntu 14.04.4 LTS


 Description   

Running from a Java 8 environment, I execute a SQL update against a table (definition given below). Most of the time, the row updates correctly. But sporadically (10%, but sometimes as high as 70% of the time), only some of the columns in the row update.

First, here is the update statement (Java prepared statement syntax):

UPDATE train SET dt_comp=NOW(), status=?, error=?, location=? WHERE id=?

The table definition is given below. The location column always updates. However, dt_comp (which isn't even set by my program, but rather uses the built-in NOW() function) and status will sporadically fail to update as indicated previously.

I've tried all manner of fiddling with auto-commit settings. I tried removing column dt_comp from the update query, but then, as before, status would sporadically fail to update.

As far as I can tell, this is a (serious) issue on the server side.

CREATE TABLE IF NOT EXISTS `train` (
 
  `id` int(10) NOT NULL AUTO_INCREMENT,
 
  `install_id` int(10) unsigned NOT NULL DEFAULT '0',
 
  `dt_req` datetime DEFAULT NULL,
 
  `dt_comp` datetime DEFAULT NULL,
 
  `total_downloads` smallint(5) unsigned DEFAULT '0',
 
  `dt_last_accessed` datetime DEFAULT NULL,
 
  `status` enum('SUCCESS','FAILED','QUEUE','IN_PROCESS','WRITE_QUEUE') DEFAULT NULL,
 
  `error` text,
 
  `location` varchar(256) DEFAULT NULL,
 
  `estimated_f1` float DEFAULT NULL,
 
  `train_ms` int(10) unsigned DEFAULT NULL,
 
  `zipped_size` int(10) unsigned DEFAULT NULL,
 
  PRIMARY KEY (`id`),
 
  KEY `FK_train_install` (`install_id`),
 
  KEY `IDX_req_date` (`dt_req`,`status`,`install_id`) USING BTREE,
 
  CONSTRAINT `FK_train_install` FOREIGN KEY (`install_id`) REFERENCES `install` (`id`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



 Comments   
Comment by Elena Stepanova [ 2016-06-29 ]

Which connector are you using?
You've indicated 5.5.47-galera – are you really using the cluster, and if so, are you sure the table is not concurrently modified by other nodes?

I assume if the problem happens 10% of the time, it's fairly easy to reproduce.
I also assume since you've played with different auto-commit settings and the query, you are able to modify the application.

Please do the following.

  • enable general_log on the server if it's not enabled yet 

    SET GLOBAL general_log=1;
    		 
    SELECT @@general_log_file;

  • open a new connection from your Java application;
  • run SELECT CONNECTION_ID(), retrieve the result, print it;
  • run your UPDATE in a loop as many times as needed, every time after UPDATE run a query which you use to determine whether all rows have been updated or not, retrieve the result, print it;
  • after your encountered the problem, store the output from the application and attach it to this issue along with the general log (file from @@general_log_file) and your cnf file(s).

Thanks.

Comment by Amin Ahmad [ 2016-07-01 ]

My apologies. By turning on the general log file, I was able to determine that the problem was an actually an error in my application logic, caused when a separate process updated the same row out-of-sequence.

Which connector are you using?

{{$ md5sum mariadb-java-client-1.4.3.jar
005184552fcb0900ffc340961c809268 mariadb-java-client-1.4.3.jar}}

Generated at Thu Feb 08 07:41:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.