0

pt-online-schema-change is not able to alter table. It is failing at the step where it copies rows from the original table to the new table and is throwing the error: copying rows caused a MySQL error 1048: Level: warning Code: 1048 Message: column 'trans_datetime' cannot be null

column definition for 'trans_datetime' from show create table >> trans_datetime datetime DEFAULT NULL

which clearly tells that it can have null values. In fact, all the rows in the table which I am trying to alter is having null for 'trans_datetime'

I am running following pt-osc command:

pt-online-schema-change --recursion-method=none --user=test --password=xxxxx 
  --socket=/mysqldata/mysql.sock --nocheck-replication-filters 
  --nocheck-alter --execute 
  --alter "CHANGE COLUMN id id BIGINT(20) NOT NULL, drop primary key, 
           add primary key (order_id, trans_datetime)"
  D=testing,t=temp_test_table

Please let me know if you need any more info. Any help on this will be highly appreciated

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
user5594148
  • 1
  • 4
  • Once you make trans_datetime part of the primary key, it cannot be null. – Honeyboy Wilson Apr 23 '19 at 16:13
  • Thank you so much. I was debugging it at the pt-osc level, never thought even once to check the alter queries. Thank you again !! – user5594148 Apr 23 '19 at 16:53
  • I edited the pt-osc command so we wouldn't have to scroll so much to read it on one line. – Bill Karwin Apr 23 '19 at 23:41
  • @HoneyboyWilson, now I am trying to make the trans_datetime column as default '0000-00-00 00:00:00', so that I could make it part of PK, but again getting the same error. pt-osc command >> pt-online-schema-change --recursion-method=none --user=test --password=xxxxx --socket=/mysqldata/mysql.sock --nocheck-replication-filters --nocheck-alter --execute --alter "modify trans_datetime datetime not null default '0000-00-00 00:00:00'" D=dbadmin,t=temp_driver_external_payment_history – user5594148 Apr 24 '19 at 10:17
  • @HoneyboyWilson, I think why it is failing now is that pt-osc is first altering the column definition in the new table and then trying to copy the table data from old to new table. while copying it finds that the column still contains null and hence throwin g the error? Is my understanding correct? If yes, any workaround? – user5594148 Apr 24 '19 at 12:46

0 Answers0