Differences between MySQL and PostgreSQL with INSERT

So, recently when investigating an issue with a MySQL to PostgreSQL migration, I found a surprising difference in behaviour between the two databases.

The difference has to do with how each behave when explicitly passed NULL values in an INSERT statement to columns with defaults or auto-increments (called “serials” in Postgres)

What do I actually mean? To compare, in MySQL you can do this:

mysql> create table baz(whattimeisit TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.02 sec)
mysql> show create table baz;
+-------+---------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                              |
+-------+---------------------------------------------------------------------------------------------------------------------------+
| baz   | CREATE TABLE `baz` (
  `whattimeisit` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO baz (whattimeisit) VALUES (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select * from baz;
+---------------------+
| whattimeisit        |
+---------------------+
| 2019-08-25 20:35:47 |
+---------------------+
1 row in set (0.00 sec)

Where it decides to revert to the “DEFAULT” value when passed a NULL.

In Postgres, we get the following error:

postgres=# CREATE TABLE baz (whatsthetime TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL);
CREATE TABLE
postgres=# \d baz
                                 Table "public.baz"
    Column    |           Type           | Collation | Nullable |      Default      
--------------+--------------------------+-----------+----------+-------------------
 whatsthetime | timestamp with time zone |           | not null | CURRENT_TIMESTAMP

postgres=# INSERT INTO baz(whatsthetime) VALUES (NULL);
ERROR:  null value in column "whatsthetime" violates not-null constraint
DETAIL:  Failing row contains (null).

Funnily enough, when it comes to PostgreSQL if you don’t pass the column at all, the default is used and you don’t get the same error.

Turning on MySQL general logging

Sometimes you want to see what queries are hitting the server. It turns out that turning on MySQL logging is as easy as adding two options to the server config:

mysql.server start --general_log=1 --general_log_file=mysqllog.txt

Afterwards, you can see the contents of the log file:

$ cat /usr/local/var/mysql/mysqllog.txt
/usr/local/Cellar/mysql/5.7.9/bin/mysqld, Version: 5.7.9-log (Homebrew). started with:
Tcp port: 0  Unix socket: (null)
Time                 Id Command    Argument
2015-11-20T08:35:40.881750Z    2 Connect    root@localhost on  using Socket
2015-11-20T08:35:40.881971Z    2 Query    select @@version_comment limit 1
2015-11-20T08:35:45.181450Z    2 Query    show databases
2015-11-20T08:35:51.492888Z    2 Query    SELECT DATABASE()
2015-11-20T08:35:51.494023Z    2 Init DB    mysql
2015-11-20T08:35:51.495439Z    2 Query    show databases
2015-11-20T08:35:51.495893Z    2 Query    show tables
2015-11-20T08:35:51.496365Z    2 Field List    columns_priv
2015-11-20T08:35:51.496621Z    2 Field List    db
2015-11-20T08:35:51.496997Z    2 Field List    event
2015-11-20T08:35:51.497403Z    2 Field List    func
2015-11-20T08:35:51.497583Z    2 Field List    general_log
2015-11-20T08:35:51.503601Z    2 Field List    help_category
2015-11-20T08:35:51.505020Z    2 Field List    help_keyword
2015-11-20T08:35:51.506380Z    2 Field List    help_relation
2015-11-20T08:35:51.507734Z    2 Field List    help_topic
2015-11-20T08:35:51.509058Z    2 Field List    innodb_index_stats
2015-11-20T08:35:51.511149Z    2 Field List    innodb_table_stats
2015-11-20T08:35:51.512539Z    2 Field List    ndb_binlog_index
2015-11-20T08:35:51.513863Z    2 Field List    plugin
2015-11-20T08:35:51.514021Z    2 Field List    proc
2015-11-20T08:35:51.515487Z    2 Field List    procs_priv
2015-11-20T08:35:51.515698Z    2 Field List    proxies_priv
2015-11-20T08:35:51.515898Z    2 Field List    servers
2015-11-20T08:35:51.516114Z    2 Field List    slave_master_info
2015-11-20T08:35:51.519151Z    2 Field List    slave_relay_log_info
2015-11-20T08:35:51.521693Z    2 Field List    slave_worker_info
2015-11-20T08:35:51.524316Z    2 Field List    slow_log
2015-11-20T08:35:51.530419Z    2 Field List    tables_priv
2015-11-20T08:35:51.530690Z    2 Field List    time_zone
2015-11-20T08:35:51.530885Z    2 Field List    time_zone_leap_second
2015-11-20T08:35:51.531070Z    2 Field List    time_zone_name
2015-11-20T08:35:51.531213Z    2 Field List    time_zone_transition
2015-11-20T08:35:51.531398Z    2 Field List    time_zone_transition_type
2015-11-20T08:35:51.531641Z    2 Field List    user
2015-11-20T08:35:55.758983Z    2 Query    show tables
2015-11-20T08:36:00.748956Z    2 Query    select * from user
2015-11-20T08:36:12.604808Z    2 Query    select Host,User,Password from user
2015-11-20T08:36:15.348311Z    2 Quit

NOTE: I have no idea how turning this on affects server performance, so, please don’t turn it on in production, especially in a high volume environment.

NOTE2: Also, disk space, this has the potential to flood your disk.

Grails part 3 – database and deployment

In this, part 3 of the series on Grails, I am going to talk about how to configure our web application to use the MySQL database as our default permanent store and then how to create a war file and deploy to Tomcat.

Firstly, we’re going to install MySQL. Luckily the state of Linux package management has advanced to the point where this is as simple as:

sudo apt-get install mysql-server

It should ask what you want to set the root user password to and then install the database. Once it has installed, the database server should be started. To check this, you can run the ps aux | grep mysql command. You should see a line in the output with:

mysql 1004 0.0 0.6 178908 27960 ? Ssl 21:50 0:00 /usr/sbin/mysqld

Then we need to create the database:

CREATE DATABASE Bookstore_dev;

Now that we have our database ready to go, we need to get the MySQL driver so that our grails application can connect to the database. Go to http://www.mysql.com/downloads/ and download the Connector/J package from the MySQL connectors section. Uncompress the package and copy and paste the mysql-connector-java-5.1.15-bin.jar file into the BookStore/lib folder.

Next we’ll need to modify our BookStore/grails-app/conf/DataSource.groovy file to specify that we want grails to use the MySQL database instead of the typical HSQLDB that is used. The DataSource.groovy file has three different sections “development”, “test” and “production”, corresponding to the three different stages of the development process and the three different environments that you can work with in grails. You can define a different database for each stage/environment. There is also a default “dataSource” section at the top, which is used unless the values are overwritten in each of the different sections. To start with, we’re going to specify that the development environment should use the MySQL database. We can do this by modifying the development section to look like:

development {
dataSource {
dbCreate = “create”
url = “jdbc:mysql://localhost/Bookstore_dev”
driverClassName = “com.mysql.jdbc.Driver”
username = “root”
password = “password”
}
}

Ofcourse you’ll need to change the username and password to whatever you’ve set them to. I’ll also point out that it’s not good practice to use the root user to access our database, because if our application gets hacked, our whole database would be compromised. It would be best from a security standpoint to create a new user with privileges limited to the “Bookstore_dev” database. However, since this is just our development database and we’re only making it available to our local computer network for the time being it should be ok.

If we now start up our application using the grails run-app command, and once it’s started browse to http://localhost:8080/BookStore, we should be able to see our application. We can then add some dummy data to check that it’s getting saved to the database. I’ve gone ahead and added the authors “Stephen King” and “Robert A. Heinlein” and the books “Pet Cemetery”, “Stranger in a Strange Land”, “The Moon is a Harsh Mistress” (associating them with their respective authors). If you log into the database and have a look at it’s contents you can see that the values have been added:

mysql> USE Bookstore_dev;
mysql> SHOW TABLES;
+————————-+
| Tables_in_Bookstore_dev |
+————————-+
| author |
| book |
+————————-+
2 rows in set (0.01 sec)

mysql> SELECT * FROM author;
+—-+———+——————–+
| id | version | name |
+—-+———+——————–+
| 1 | 0 | Stephen King |
| 2 | 0 | Robert A. Heinlein |
+—-+———+——————–+
2 rows in set (0.01 sec)

mysql> SELECT * FROM book;
+—-+———+———–+—————————-+
| id | version | author_id | title |
+—-+———+———–+—————————-+
| 1 | 0 | 1 | Pet Cemetery |
| 3 | 0 | 2 | Starship Troopers |
| 4 | 0 | 2 | Stranger in a Strange Land |
+—-+———+———–+—————————-+
3 rows in set (0.00 sec)
So, we can see that the data is being saved to the database and that the association between the Author and Book object is represented with the ‘author_id’ field in the Book table. It’s also worth noting the “version” field which is updated by grails every time any of the fields in the row are modified.

So now that we’ve got an application which uses a database it’s time to deploy it to our *production* server. We’re going to modify our DataSource.groovy file to ensure that the production environment (the one we’re going to deploy) also uses the MySQL database:

production {
dataSource {
dbCreate = “update”
url = “jdbc:mysql://localhost/Bookstore_dev”
driverClassName = “com.mysql.jdbc.Driver”
username = “root”
password = “password”
}
}

Make sure you’ve executed the run-app command with dbCreate set to “create” before deploying this production code as dbCreate = “update” expects the tables to already be created in the database.

Now we can create the war file which we’re going to upload to Tomcat through the manager web-app by running the command grails prod war. This generates a production environment war file. The production environment is optimized for code efficiency, while the development and testing environments are optimized for developer productivity. Once the command finishes executing we should have our war file under BookStore/target/BookStore-0.1.war. The 0.1 is the application version number and can be changed in the BookStore/application.properties file.

Now we can log into our Tomcat manager application (found at http://localhost:8080/manager/html if you’ve setup Tomcat according to the previous post), go to the Deploy section, select our WAR file and hit ‘Deploy’. Once the page refreshes we should see our BookStore app in the list of applications and the column “Running” should be set to “true”. We can now click on the link in the Path column to go to our web-app and start using it.

As an alternative way to deploy your application, you can also make use of the tomcat grails plugin. In order to do this you need to add a few variables to the BookStore/grails-app/conf/Config.groovy file, namely:

tomcat.deploy.username = “[tomcat manager user]”
tomcat.deploy.password = “[tomcat manager password]”
tomcat.deploy.url = “http://localhost:8080/manager”

Deploying the application is now achieved from the command line with:

grails prod tomcat deploy

This code essentially does the same thing that we did, makes a war file and deploys it, but might be preferable as it is only one step instead of two.

So there you have it, we’ve taken our simple web application, configured it to use a permanent datastore and deployed it to our Tomcat webserver.

NOTE: If you need to update your deployed application, the way to do it is to first “undeploy” the application from Tomcat, which can be done with “grails prod tomcat undeploy”

MySQL Stored Procedure tip

The other day I was writing a stored procedure, when after making a change it just wouldn’t save and kept giving me an error, saying something useful like “There is an error in your SQL syntax…”.

After pulling my hair out for a day trying to work it out, I realised that when dealing with MySQL stored procedures you must put all of your declare statements at the start of the sproc.