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.

MySQL Bug #11918

In the software industry, every now and then you come accross some indescribably dense acts of stupidity that cost you hours of debugging time. When it’s not your own code that’s the problem, you get to rant about it on your blog 😉

Today I spent the last four hours trying to create a MySQL stored procedure to do something which I thought was quite basic. It just had to take an integer as input and return that number of rows from a specific table and at the end delete the rows. Kind of like a stack data structure (the rows ‘pop’ out), but with a database. So I sat down and wrote this code:

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`testGet`$$
CREATE PROCEDURE `test`.`testGet` (IN numRecords INT)
BEGIN
CREATE TEMPORARY TABLE tmpTable
SELECT * FROM test LIMIT numRecords;

DELETE FROM test WHERE idTest IN (SELECT idTest FROM tmpTable);

SELECT * FROM tmpTable;

DROP TABLE tmpTable;
END$$

DELIMITER ;

So after saving this code and trying to run it I get this cryptic error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘numRecords;

DELETE FROM test WHERE idTest IN (SELECT idTest FROM tmpTable);

‘ at line 4 (errno: 1064)

I spent the next two hours trying to figure out what MySQL was complaining about, pulling my hair out, trying to see which part of the code was screwing up…. finally after much googling, I came across this bug report on the MySQL bug tracker, the title of which simply reads “SP does not accept variables in LIMIT clause”. The bug’s been sitting in the bug tracker since the 13th of July 2005, FOUR YEARS this last July. The bug report is full of comments with detailed workarounds, most of which end up being versions of the ‘EXECUTE STMT USING’ statement. There’s a big discussion in the comments as to whether this is a bug fix or a feature request, meanwhile Oracle and SQL Server continue to dominate the marketplace. Nice going guys. I’m stupified that this bug has been hanging around for four years and is still not fixed. I’m tempted to download the source and have a look at how difficult it would be to patch.

So there’s only web development…

So, after talking to a friend of mine he came up with a statement that I believe sums up the New Zealand IT industry pretty well. “Its all web development”. The industry here contains a few large companies, which hire some CS/SE graduates, but the vast majority will find themselves after university working on some aspect of developing essentially web pages.

For someone who doesn’t particularly enjoy web development, this is a rather bitter pill to swallow, learning that if you want to work in any other aspect of IT, you should probably move to Australia/UK/America.

What’s even more disappointing is that during my 5 and a half years at university, there was hardly any emphasis placed on web development. I think in total there were 2 courses that dealt with any kind of web development. This leaves a large gap in my education, as people that are hiring are looking for experience with CSS, Javascript, AJAX, technologies which the university does not teach at all, and PHP/ASP.NET, MySQL/MSSQL which the university does a poor job of teaching. In fact looking at the curriculum the only thing that the Computer Science degree seems to prepare you for is more Computer Science.

So anyway… It’s no use bitching about the past now. If I’m doomed to become a web developer then I might as well become the best fucking web developer this side of the equator. But this is going to involve a lot of learning. The type of learning I dread and generally avoid. Learning by yourself, in your own spare time. Having to force yourself to read another chapter after coming home from work tired and worn out from your shitty job. The really hard kind of learning… fuck.

But I do in fact have a plan. And having a plan keeps me from having a total nervous breakdown, something which I’ve been really close to this last month.

My plan essentially involves learning everything about web technology, from the ground up, from setting up a LAMP server to AJAX. This is an ambitious goal for me, one which might take over a year or more to complete as I don’t know how much time I will be able to commit to my “2nd education”. But I do know roughly what it will involve.

Step 1: Wiring up my house. That is to say put in ethernet cables connecting the bedrooms of the house, the living room and the garage. This will allow me to put my de facto webserver (old HP pentium 4 my girlfriend was going to throw out) into the garage and to have it running full time. This computer is a mixture of web server and storage server (after adding a 320GB HDD) and will serve files to the internet and to the different devices around the house (in the future I could have a separate machine as a file server and a webserver) This should also give me experience on how to set up a network for a SOHO (Small Office Home Office).

Step 2: Domain Name. Opening an account with a dynamic dns provider and setting up a domain name. Probably going to use dyndns.com as it seems to be quite popular.

Step 3: Learning about webservers, file servers, ssh servers, nfs servers, ftp servers, proxies etc… Because I want my web/storage server to be universally accessible it needs to be able to serve files across a wide range of protocols. For each of the types of server I need to

  1. Install the software
  2. Configure the software i.e. get it to do what I want securely

Luckily I have found a resource that deals with these issues and a lot more. The resource being www.linuxhomenetworking.com. The website is well written, easy to read and up to date. It’s also free.

[NOTE: I should mention now that I intend to use only OSS software as a part of this education, for reasons which I will probably write about later]

Step 4: Install software for the management of my server. This includes things like Webmin, MySQL Administrator, PHPMyAdmin and perhaps some software to configure Apache (is there a decent GUI frontend for Apache or does it come down to editing config files?)

Step 5: Installing existing CMSs’ and understanding how they work. Looking at software such as Joomla!, Drupal, WordPress, Blogger (is the blogger source code available?), Silverstripe (Go NZ!) etc… and looking at how they are made, paying particular attention to how they handle extensibility (add-ons, extensions) and theming. However for the basic ideas on how to create a CMS I’ll probably start with php-mysql-tutorial.com. It should be interesting to see how much difference in the designs of these CMSs’ there is and the benefits/disadvantages of each approach.

Step 6: Learn Cascading Style Sheets. I have a real love/hate relationship with CSS. That is to say I f***ing hate CSS. As far as I’m concerned, CSS is a great idea (separation of content and presentation), implemented in a totally illogical, counter-intuitive, overly complex way. But to be fair, that’s what I thought of a lot of programming languages I learned until I “got” them. So maybe sometime in the future I will really love CSS, but I wouldn’t bet money on it. I’m probably gonna go with the tutorials from W3Schools or failing that get a book from the university library on CSS.

Step 7: Learn Javascript. Again, I feel like I’m 10 years behind the learning curve with this one, but thanks to university (and my own lack of interest in web development) this is another area which I have hardly any experience with. With this one I’m gonna follow the W3Schools tutorials and if needed get a book from the uni library.

Step 8: Learn XHTML. Another one from W3Schools. Basically need to learn along with Javascript to be able to understand AJAX in step 9.

Step 9: Learn AJAX. Start with W3Schools tutorial on AJAX, then can move on to other examples on the internet. There’s so many on the web that the biggest problem learning is going to be information overload. A book might be useful as well.

So then the question arises as to what to do with all of this new web development knowledge and the server sitting in my garage. The thing to do I guess would be to make a website to showcase my talents which could be a point of reference for people seeking examples of my skills/knowledge.

Also the idea of creating a photo gallery CMS based on the work I did with LaPhotographie is an idea I’ve had for a while. Such a system would, of course be open source and perhaps some day take off and become more than a pet project.

And the possibility of turning the setup in my garage into a web hosting company is there as well.

So there you go, that’s my plan. It helps me to keep busy and ignore the fact that I’ll probably end up doing web development for a long, long time.