This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-15. For the latest, interactive version please visit https://mariadb.com/kb/.

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 :

NameDatatypeLength/SetUnsignedAllow NULLZerofillDefault
idINT11AUTOINCREMENT
TransDateTimeTIMESTAMP
TransCountINT110
SourceVARCHAR50No 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.

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.