OpenStack Neutron db_sync error – Cannot add a NOT NULL column with default value NULL [Solved]

If you are stuck with an error Cannot add a NOT NULL column with default value NULL while populating or syncing neutron database, then here’s how you can fix it. Before we see the solution, have a look at the snapshot of the error:

# su -s /bin/sh -c "neutron-db-manage --config-file /etc/neutron/neutron.conf --config-file /etc/neutron/plugins/ml2/ml2_conf.ini upgrade head" neutron
 sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) Cannot add a NOT NULL column with default value NULL [SQL: u'ALTER TABLE address_scopes ADD COLUMN ip_version INTEGER NOT NULL']

The error is strange because, my OpenStack installation uses MySQL database, but the error says SQLite.

Solution:

As my installation supposed to use MySQL database, I quickly verified neutron.conf file to see if the connection variable was set properly. Surprisingly, there was an extra connection variable pointing to SQLite and that was the culprit in my case.

# vim /etc/neutron/neutron.conf

[database]
 connection = mysql+pymysql://neutron:osneutron@controller/neutron
 The SQLAlchemy connection string to use to connect to the database. (string
 # value)
 # Deprecated group/name - [DEFAULT]/sql_connection
 # Deprecated group/name - [DATABASE]/sql_connection
 # Deprecated group/name - [sql]/connection
 connection = sqlite:////var/lib/neutron/neutron.sqlite

So the solution was to comment out the connection variable that’s pointing to sqlite.

# connection = sqlite:////var/lib/neutron/neutron.sqlite

That’s it! If you are interested to know why sqlite was not able to add a column address_scopes with a constraint as NOT NULL to an existing table, then here’s the reason.

According to SQLite ALTER TABLE doc, if NOT NULL constraint is specified then the column must have a default value other than NULL.