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:
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…
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
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
((count = 0))
while [[ $count -ne 1 ]] ; do
ping -c 1 184.108.40.206
if [[ $rc -eq 0 ]] ; then
((count = 1)) # If okay, flag to exit loop.
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
echo "Tunnel Timeout...." `date` >> log.txt
I ran into this problem after upgrading/intalling MariaDB, the config file was missing.
To regenerate the config file… try executing…
dpkg --force-confmiss -i /var/cache/apt/archives/mysql-common*.deb