Pt-online-schema-change errors on altering default value

MySQL & MariaDB
,
1

Hi
Trying to alter the default value to current_timestamp.
The column name is datetime (not my idea) and this is a AWS RDS cluster version 5.7.mysql_aurora.2.10.2
ex. --alter “ALTER datetime SET DEFAULT CURRENT_TIMESTAMP”
I’ve tried all different kinds of syntax and getting this error:
DBD::mysql::db do failed: 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 ‘‘datetime’ SET DEFAULT CURRENT_TIMESTAMP’ at line 1 [for Statement “ALTER TABLE <db>._<table> ALTER ‘datetime’ SET DEFAULT CURRENT_TIMESTAMP”] at /usr/bin/pt-online-schema-change line 9490.

The current column definition:
datetime datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
and it’s indexed
Any help appreciated.

Peter

2

Never mind - I see you can’t use current_timestamp with datetime

1 Like
3

@psteinheuser
Yes you can

CREATE TABLE `times_tests` (
  `id` int NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)

Test Suite: Online PHP/Java/C++... editor and compiler | paiza.IO

1 Like
4

So I obviously have some kind of syntax error here - what am I doing wrong?
pt-online-schema-change --dry-run --max-load Threads_connected --critical-load Threads_running=20 --alter “ALTER column datetime SET DEFAULT CURRENT_TIMESTAMP” D=skoutdb_1,t=sh_userproperties,h=skoutdb1-test-instance-0.csog6rjwxnsr.us-west-2.rds.amazonaws.com --user root --password $MYSQL_PWD
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Starting a dry run. skoutdb_1.sh_userproperties will not be altered. Specify --execute instead of --dry-run to alter the table.
Creating new table…
Created new table skoutdb_1._sh_userproperties_new OK.
Altering new table…
2022-05-27T09:05:54 Dropping new table…
2022-05-27T09:05:55 Dropped new table OK.
Dry run complete. skoutdb_1.sh_userproperties was not altered.
(in cleanup) Error altering new table skoutdb_1._sh_userproperties_new: DBD::mysql::db do failed: 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 ‘CURRENT_TIMESTAMP’ at line 1 [for Statement “ALTER TABLE skoutdb_1._sh_userproperties_new ALTER column datetime SET DEFAULT CURRENT_TIMESTAMP”] at /usr/bin/pt-online-schema-change line 9490.

1 Like
5

pt-online-schema-change 3.2.1
All the examples I see in Percona or mysql use “create table” or “add column” - I’m trying to change an existing column. I’m having trouble finding useful doc for this.
In pt-online-schema-change, I’ve tried alter, modify, change in every permutation I can think with no success. I notice if I use pt-online-schema-change and ADD a column, it seems to work.
–alter “add test1 datetime default current_timestamp not null”
Is this some pt-online-schema-change bug or not supported. Or is there a workaround?
I apologize if I’m missing some obvious examples.

1 Like
6

Maybe this is an AWS “thing” since this is aurora mysql and I can’t even directly alter the table.
mysql> alter table sh_userproperties alter datevalue SET DEFAULT CURRENT_TIMESTAMP;
ERROR 1064 (42000): 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 ‘CURRENT_TIMESTAMP’ at line 1

1 Like
7

Hi @psteinheuser

Have you tried looking at the debug output? For example:

PTDEBUG=1 pt-online-schema-change ...
1 Like
9

Your SQL is wrong. You need to use the correct SQL syntax for modifying a column.

1 Like