MySQL >= 5.7.5 breaks Piwik <= 2.15.0


#1

We discovered this morning that in addition to many headaches suffered by upgrading beyond 5.7.4 of MySQL (community-release), the piwik core:archive scripts croak while trying to execute a GET_LOCK call on the MySQL database. The logging visitors function works just fine, making this bug particularly insiduous as I, the campus OS DBA, was verifying the application’s migration readiness through the “visitor log” page. Other basic Piwik functions ceased to work, a fact brought to my attention only in the wee hours of the morning, which after an all-nighter, you can imagine, is a bit too exhilarating.

Again, the problem arises in 5.7.5 and is politely “documented” on this MySQL Developer page. Deep within that page, it is stated:

[center]MySQL 5.7.5 and later enforces a maximum length on lock names of 64 characters. Previously, no limit was enforced[/center]

It just so happens, that in our installation, which is pretty vanilla, the string generated by Piwik is always 65 characters. pauses for geeky laughter

The show-stopping, 65-character labels look like:
deletePreviousArchiveStatus.piwik_archive_numeric_2015_03.5267868

The numbers at the end are different each time. I don’t have a long-enough query history to know what else might change here. In theory, we can alleviate this problem by renaming all the database objects from “piwik_xxxx” to “pwk_xxxx”, but I fear that would introduce more problems. Also, I’m not particularly keen on rename 60 tables by hand, nor do I want to spend the time and energy validating that everything works again.

Luckily, the Piwik source code references this GET_LOCK() call exactly once, from core/db.php, and which is referenced by acquireArchiveTableLock and releaseArchiveTableLock, both in DataAccess/Model.php. I solved our problem – temporarily mind you! We look to you for long-term support! – by hacking the call at code/db.php and wrapping the label in a call to md5(). Ingenious perhaps, but I hate the idea of wasting more CPU cycles.

Just two lines of code are changed, and the rest is pasted for convenience. Hopefully, we can expect the next release to include a patch of related goodness.


    public static function getDbLock($lockName, $maxRetries = 30)
    {
        /*
         * the server (e.g., shared hosting) may have a low wait timeout
         * so instead of a single GET_LOCK() with a 30 second timeout,
         * we use a 1 second timeout and loop, to avoid losing our MySQL
         * connection
         */
        $sql = 'SELECT GET_LOCK(?, 1)';

        $db = self::get();

        while ($maxRetries > 0) {
            if ($db->fetchOne($sql, array(md5($lockName))) == '1') {
                return true;
            }
            $maxRetries--;
        }

        return false;
    }

    /**
     * Releases a named lock.
     *
     * @param string $lockName The lock name.
     * @return bool `true` if the lock was released, `false` if otherwise.
     */
    public static function releaseDbLock($lockName)
    {
        $sql = 'SELECT RELEASE_LOCK(?)';

        $db = self::get();
        return $db->fetchOne($sql, array(md5($lockName))) == '1';
    }

[sub]Submitted as issue 9131 https://github.com/piwik/piwik/issues/9131[/sub]


#2

Submitter created following issue report: MySQL >= 5.7.5 breaks Piwik <= 2.15.0 · Issue #9131 · matomo-org/piwik · GitHub