MariaDB 10.2 – Field ‘xxxxxxx’ doesn’t have a default value Error

I update one of my production server MariaDB 10.1 database server to 10.2 thinking everything will be ok, I did a live test in one of my VM machines and everything went smoothly…. well… on the outside that is.
As I finished updating the server, I started getting java.sql.SQLException: Field ‘xxxxxx’ doesn’t have a default value in my logs.
This didn’t happen before.
I soon found out from MariaDB document page….

Since MariaDB 10.2.4, SQL_MODE is by default set to NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO. In earlier versions of MariaDB 10.2, and since MariaDB 10.1.7, SQL_MODE is by default set to NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER. For earlier versions of MariaDB 10.1, and MariaDB 10.0 and before, no default is set.

So, this means that as soon as I updated my server to MariaDB 10.2, my sql_mode was set to “strict” mode by default internally.

The work around(?)/fix…go to the MariaDB config file (my.cnf) and change the sql_mode line to

sql_mode                = NO_ENGINE_SUBSTITUTION

Hope this helps those who ran into this problem.
(Note: Another fix is to manually set all table fields to have a default value – too long, too tiresome, Also you can change your code to set all default values – also too long and tiresome )

Link to other sources:
Here

MariaDB Unknown/unsupported storage engine: InnoDB

I had this problem re-occurring in one of my servers. MariaDB wont start due to this error.

To fix my problem, remove these log files…

/var/lib/mysqld/ib_logfile0
/var/lib/mysql/ib_logfile1
/var/lib/mysql/aria_log_control

ib_logfile may be 0 or 1

If you want to be safe.. just rename it to another file temporarily to see if it works.

Sources for the fix can be found Here and Here

Enable Ubuntu 16.04 SSH Tunneling at Boot Time

I wanted to connect to a remote server and setup a tunnel for my MariaDB replication ( yes I migrated to MariaDB ),
the only problem was that sometimes the net is erratic and can’t connect right away on boot time.
Found a code online to check for ping reply, if it does, continue with the tunneling.
I modified this to suit my needs.

In summary, the script checks for a ping reply from Google infinitely. Once it receives a response, it then creates the tunnel.
Source for the code here

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
#!/bin/bash
((count = 0))
while [[ $count -ne 1 ]] ; do
    ping -c 1 8.8.8.8
    rc=$?
    if [[ $rc -eq 0 ]] ; then
        ((count = 1))                      # If okay, flag to exit loop.
    fi
 
done
 
if [[ $rc -eq 0 ]] ; then                  # Make final determination.
 
    screen -dm -S tunnel autossh -M 0 -o "ServerAliveInterval 60" -o "ServerAliveCountMax 3" -p <PORT> <IP_ADDRESS> -L <LOCAL_TUNNEL_PORT>:localhost:<MYSQL_PORT>
 
 
    echo `date` >> log.txt
 
else
    echo "Tunnel Timeout...." `date` >> log.txt
fi