MYSQL - set default as NULL to all columns, where default is not set

Question

I have about 12 databases, each with 50 tables and most of them with 30+ columns; the db was running in strict mode as OFF, but now we had to migrate the db to cleardb service which by default has strict mode as ON. all the tables that had "Not Null" constraint, the inserts have stopped working, just because the default values are not being passed; while in case of strict mode as OFF if the value are not provided, the MYSQL will presume the default value of the column datatype. Is there a script I can use to get the metadata about all the columns of all tables and generate a script to alter all the tables with such columns to change the default to "Null"

I am not asking for any off site resource @Shadow I am looking for guidance on how to do it, not that I am sitting here twiddling my thumbs, while someone helps me. — Brij Raj Singh - MSFT
– Brij Raj Singh - MSFT, Commented Aug 22, 2016 at 12:22
Moreover, setting the default field value to null on fields that are defined as not null is probably not the best idea. Your question implies that you are looking for a complete existing solution. ("Is there a script..."). You have not demonstrated any effort in solving the question, therefore it is unlikely that your question will fly in its current format. — Shadow
– Shadow, Commented Aug 22, 2016 at 12:26
which is right, but sometimes one has to work with what is given; the person designing this db probably didn't gave a thought about the strict mode. Setting Not Null and not providing any value sounds crazy already. — Brij Raj Singh - MSFT
– Brij Raj Singh - MSFT, Commented Aug 22, 2016 at 12:29
You did not get my point: if a field has a not null restriction and then you set its default value to null, then the 2 settings will contradict each other. — Shadow
– Shadow, Commented Aug 22, 2016 at 12:33

O. Jones · Accepted Answer · 2016-08-22 12:47:37Z

2

You should consider using the information_schema tables to generate DDL statements to alter the tables. This kind of query will get you the list of offending columns.

SELECT CONCAT_WS('.',TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME) col  
  FROM information_schema.COLUMNS
 WHERE IS_NULLABLE = 0
   AND LENGTH(COLUMN_DEFAULT) = 0 
   AND TABLE_SCHEMA IN ('db1', 'db2', 'db3')

You can do similar things to generate ALTER statements to change the tables. But beware, MySQL likes to rewrite tables when you alter certain things. It might take time.

DO NOT attempt to UPDATE the information_schema directly!

You could try changing the strict_mode setting when you connect to the SaaS service, so your software will work compatibly.

This is a large project and is probably important business for cleardb. Why not ask them for help in changing the strict_mode setting?

edited Aug 22, 2016 at 12:47

answered Aug 22, 2016 at 12:44

O. Jones

109k17 gold badges134 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Brij Raj Singh - MSFT Over a year ago

information schema idea is good, I am working on the same lines, Cleardb won't do it for me, unless we switch to the dedicated servers.

O. Jones Over a year ago

Tell cleardb you're going to AWS, Rackspace, or Azure if they don't help you. Migrating to cloud services is hard enough without having to gratuitously change your business logic.

Brij Raj Singh - MSFT · Accepted Answer · 2016-08-23 08:33:40Z

This is what I came up with on the base of @Ollie-jones script

https://gist.github.com/brijrajsingh/efd3c273440dfebcb99a62119af2ecd5

SELECT CONCAT_WS('.',TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME) col,CONCAT('alter table ',TABLE_NAME,' MODIFY COLUMN ', COLUMN_NAME,' ',DATA_TYPE,'(',CHARACTER_MAXIMUM_LENGTH,') NULL DEFAULT NULL') as script_col
  FROM information_schema.COLUMNS
 WHERE is_nullable=0 
 and length(COLUMN_DEFAULT) is NULL and
 CHARACTER_MAXIMUM_LENGTH is not NULL and 
 table_schema = 'immh' 
 Union
 SELECT CONCAT_WS('.',TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME) col,CONCAT('alter table ',TABLE_NAME,' MODIFY COLUMN ', COLUMN_NAME,' ',DATA_TYPE,' NULL DEFAULT NULL') as script_col
  FROM information_schema.COLUMNS
 WHERE is_nullable=0 
 and length(COLUMN_DEFAULT) is NULL and
 CHARACTER_MAXIMUM_LENGTH is NULL and 
 table_schema = 'immh'

Collectives™ on Stack Overflow

MYSQL - set default as NULL to all columns, where default is not set

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related