Inserting data from other databases in SQLite

I was given a table of additional data that needed to be added to an existing table in a SQLite database. Since I was processing CSV files exported from the database, I thought I would just concatenate the files – until I realized that the ID columns would contain duplicates. Now, this wasn’t crucial for my case but it could have caused confusion at some point in the future. Therefore I decided to do the appending in the database, and then export the combined data into a new CSV file.

The first step was to be able to read both database files. I began by just opening the original database:

sqlite3 my_existing_db.sqlite

and then “attaching” the new database with the data I wanted to append (the quotes are necessary):

sqlite> attach 'my_other_db.sqlite' as db2;

(Of course, both tables must have exactly the same schema!)

My reading around had suggested that it would be easy to import the new data with a simple insert into ... select * from ... command. However, this gave me an error because the values in the ID column (which is the Primary Key) were not unique:

Error: UNIQUE constraint failed: table1.ID

I scratched my head, and then decided to just select the columns with data. But that didn’t work either because the list of columns being inserted didn’t match the list of columns in the original table (well, d’uh!):

Error: table table1 has 10 columns but 9 values were supplied

I scratched my head some more, and then read the documentation about autoincrementing primary keys in SQLite. It turns out that a ROWID must be specified in the list for an insert statement. A lightbulb switched on as I read about how new ROWIDs are assigned in SQLite if a NULL value is given. This prompted me to try:

sqlite> insert into table1 select NULL, col1, col2, ... from db2.table1;

Success! I now had my combined table, which I exported to CSV and continued with my analysis.

In short, SQLite advises against setting up an autoincrementing primary key, and just allows the user to give an explicit NULL value to allocate the next available number.

It may be noted that the above was all done via the SQLite command line interface. It seems that this is the way to go: my attempts to attach a database in DB Browser for SQLite, for example, failed with a message about not being able to attach a database in a transaction. That, and all the examples on Stackoverflow showed the command-line being used. Other graphical browsers may differ.

Recovering MySQL after improper shutdown

As an amateur DBA I recently made a classic amateur DBA mistake: I restarted my computer without first shutting down MySQL. (The restart in this case was the result of updating macOS to 10.12.4.) After the reboot I couldn’t work out why XAMPP wasn’t launching MySQL again – apache started up fine – so it was time to dig into the log files.

A quick check of var/mysql/hostnameredacted.local.err in the XAMPP installation directory revealed a string of errors and a stacktrace. Reading it carefully I realized the key error message was this:

InnoDB: Operating system error number 2 in a file operation

The other clue was a few lines earlier in the logfile:

InnoDB: Database was not shutdown normally!

As ever, Google to the rescue which led me to a handy blog post which suggested adding the following line to the [mysqld] section of etc/my.cnf:

innodb_force_recovery = 1

I saved the file, and held my breath as I attempted to restart MySQL. Success!

I went back and removed that line again (actually just commented it out – there’s an outside chance I’ll repeat this error at some point in the future!) and re-restarted MySQL. Firing up SequelPro and I was relieved to be able to see all my tables as expected, and ran a few test queries to check it out. Everything looks OK. Phew!

Lesson learned: remember to shutdown MySQL properly before a reboot.