Update Statement for a Varchar Field Causes Separate TimeStamp Field to Update to Current TimeStamp
MariaDB 10.3.12 fresh install, Windows 10 Enterprise Version 1607, OS Build 14393.2791
I manually create a table named tps under the test database. , I am interested in running some simple "transaction per second" queries.
Example TransDateTime value : 2019-02-11 17:23:34
Here is the table column information as shown in HeidiSQL 9.4.0.5125 :
Name | Datatype | Length/Set | Unsigned | Allow NULL | Zerofill | Default |
---|---|---|---|---|---|---|
id | INT | 11 | AUTOINCREMENT | |||
TransDateTime | TIMESTAMP | |||||
TransCount | INT | 11 | 0 | |||
Source | VARCHAR | 50 | No default |
The only key on the table is the auto-increment id field.
I import a csv file successfully into the table. The records represent transactions from previous days and I verify that the TransDateTime contains each day's associated transactions. 2,446,322 records are imported successfully and the data in the table looks good.
Each TransDateTime value represents a 1-second interval and Count is how many requests were logged during that interval. Soure is the log server.
Upon executing this simple update statement :
update tps set Source = 'DCRequestApp6' where Source = 'DCRequest-app6'
the Source field is successfully updated to the desired value, however, the TransDateTime field for each row is also affected by the update.
Why is TransDateTime being changed? I'm sure I'm doing something wrong here, its my first time to use MariaDB. I have dropped and recreated the table mulitple times.
Any help is appreciated.
Thanks, Chris
Answer Answered by Ian Gilfillan in this comment.
You don't provide the table definition (DESC tps), but the timestamp field probably has the ON UPDATE CURRENT_TIMESTAMP clause. See TIMESTAMP. You can use DATETIME instead, remove the clause or use a second timestamp field.