MySQL Server automatic update followed by restart: ERROR: 1064 You have an error in your SQL syntax; mysql.user add column

Asked by Tinel Barb

At first time in the morning I was looking to "show engine innodb status" and noticed low values in SEMAPHORES, which leads to the conclusion of an unauthorized restart.
After research I've found that Ubuntu Server made an update to MySQL Server 5.5 package and initiated a restart of the MySQL service.
Some errors was issued after update:

ERROR: 1064 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 'ALTER TABLE user ADD column Show_view_priv enum('N','Y') CHARACTER SET utf8 NOT ' at line 1
150422 6:30:27 [ERROR] Aborting

Searching the web I've found that the same error appears to others and came from:
mysql-server-5.5.postinst
even in year 2012, 2013, 2014.
Besides that, another errors came after the restart issued after the above error:
ERROR: 1364 Field 'ssl_cipher' doesn't have a default value
ERROR: 1050 Table 'plugin' already exists

so... HEY, what's going on?? Why are you messing things up with old UNVERIFIED update scripts??? And why are you doing restarts without admin's consent?
At least, put a question before the update:
"An update is pending for MySQL Server 5.5. DO YOU FEEL LUCKY, PUNK? [yN]"

Best regards!
Tinel Barb

PS1: the columns the script tried to add were already implemented since installation.
PS2: as a test, I'm curious what you'll find important for a reply ;)

PS3: Bob Proulx <bob[antiposix[proulx.com> suggested at https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=708176 the folowing code in order to catch exceptions while updating the structure on mysql.user. I think that is a very good code to avoid braking updates.

***CODE***
    new_mysql_columns="
        Create_view_priv
        Show_view_priv
        Create_routine_priv
        Alter_routine_priv
        Create_user_priv
        Event_priv
        Trigger_priv
        Create_tablespace_priv
        "
    for col in $new_mysql_columns; do
      printf "USE mysql;\n ALTER TABLE user ADD column $col enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N';\n" | $MYSQL_BOOTSTRAP 2>&1 | sed "/^ERROR:.*Duplicate column name '$col'/d;/Aborting/d" | $ERR_LOGGER
    done
***END-CODE***

Revision history for this message
actionparsnip (andrew-woodhead666) said :
#1

I suggest you report a bug

Wise admins take a snapshot of the server to test effects of updates rather then updating and hoping. If it is a VM snapshot you can easily wind back to before the update.

Was this not done?

Revision history for this message
Tinel Barb (tinelbarb) said :
#2

It's not a VM, but a dedicated machine.
"Wise admins take a snapshot of the server to test effects of updates" -> the update was initiated without my knowledge.
"I suggest you report a bug" -> I don't see a reason to submit a bug since this bug was reported earlier back in 2012, 2013 and 2014.
Anyway, I would like Ubuntu representatives to respond, so the thread would not be filled with "too wise" questions.

Revision history for this message
actionparsnip (andrew-woodhead666) said :
#3

Updates don't happen on their own unless you have added some functionality to do this. The system can check for updates and tell you if they exist (usually via not when you SSH in or open a terminal). An administrative user has to use apt-get to install the new packages in a standard install.

Revision history for this message
Tinel Barb (tinelbarb) said :
#4

$ sudo apt-get changelog mysql-server

>
mysql-5.5 (5.5.43-0ubuntu0.14.04.1) trusty-security; urgency=medium

  * SECURITY UPDATE: Update to 5.5.43 to fix security issues (LP: #1444616)
    - http://www.oracle.com/technetwork/topics/security/cpuapr2015-2365600.html
    - CVE-2015-0433
    - CVE-2015-0441
    - CVE-2015-0499
    - CVE-2015-0501
    - CVE-2015-0505
    - CVE-2015-2568
    - CVE-2015-2571
    - CVE-2015-2573

 -- Marc Deslauriers <email address hidden> Mon, 20 Apr 2015 09:30:47 -0400
>

There are crons which can do unattended upgrades (update-notifier-common and other means), so I'm forced to repeat myself:
I'm NOT addressing to community, I'm addressing ONLY Ubuntu tech and representatives.

Revision history for this message
Manfred Hampl (m-hampl) said :
#5

"I'm NOT addressing to community, I'm addressing ONLY Ubuntu tech and representatives"

In that case you are wrong here. The Launchpad answers area is dealt with by the community.

Developers usually look into the bug area. So please create a bug report.

Can you help with this problem?

Provide an answer of your own, or ask Tinel Barb for more information if necessary.

To post a message you must log in.