Doctrinebundle: How to use server_version config option?

[peter-gribanov]

The problem with the server version added a lot of problems.

And this problem, unfortunately, has not been solved by me to the end.

I have two conections. One connection to MySQL server and one connetcion to Sphinx for use SphinxQL.
With MySQL everything is clear. Take the value from the console

mysql -V

But there are exceptions for MariaDB.

For the Sphinx, i also found out.

But the format of the version may be different and it is not clear what to use.
I decided to climb inside the Doctrine and find out how it gets the version if it isn't configured and i found this.

public function getServerVersion()
{
    return PDO::getAttribute(PDO::ATTR_SERVER_VERSION);
}

I wrote a simple script to get the server version from PDO.

server_version

#!/usr/bin/env php
<?php

use Doctrine\DBAL\Driver\PDOConnection;
use Symfony\Component\Dotenv\Dotenv;

include __DIR__.'/../vendor/autoload.php';

if (!isset($_SERVER['APP_ENV'])) {
    (new Dotenv())->load(__DIR__.'/../.env');
}

echo 'Server version'.PHP_EOL;

// MySQL
$mysql_connection = new PDOConnection(
    sprintf(
        'mysql:host=%s;port=%d;dbname=%s',
        $_SERVER['MYSQL_HOST'] ?? '127.0.0.1',
        $_SERVER['MYSQL_PORT'] ?? 3306,
        $_SERVER['MYSQL_DATABASE'] ?? ''
    ),
    $_SERVER['MYSQL_USER'] ?? 'root',
    $_SERVER['MYSQL_PASSWORD'] ?? ''
);
echo sprintf('MySQL: %s', $mysql_connection->getServerVersion()).PHP_EOL;

// Sphinx
$sphinx_connection = new PDOConnection(
    sprintf(
        'mysql:host=%s;port=%d',
        $_SERVER['SPHINX_HOST'] ?? '127.0.0.1',
        $_SERVER['SPHINX_PORT'] ?? 9306
    )
);
echo sprintf('SphinxQL: %s', $sphinx_connection->getServerVersion()).PHP_EOL;

Result on the local machine

$ ./bin/server_version
Server version
MySQL: 5.7.21-0ubuntu0.16.04.1
SphinxQL: 2.2.9-id64-release (rel22-r5006)
$ mysql -V
mysql  Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using  EditLine wrapper
$ indexer
Sphinx 2.2.9-id64-release (rel22-r5006)
Copyright (c) 2001-2015, Andrew Aksyonoff
Copyright (c) 2008-2015, Sphinx Technologies Inc (http://sphinxsearch.com)

Result on the production server

$ sudo ./bin/server_version
Server version
MySQL: 5.5.5-10.1.32-MariaDB
SphinxQL: 2.2.10-id64-release (2c212e0)
$ mysql -V
mysql  Ver 15.1 Distrib 10.1.32-MariaDB, for Linux (x86_64) using readline 5.1
$ indexer
Sphinx 2.2.10-id64-release (2c212e0)
Copyright (c) 2001-2015, Andrew Aksyonoff
Copyright (c) 2008-2015, Sphinx Technologies Inc (http://sphinxsearch.com)

If the values for the Sphinx are the same, then the MySQL is very different.
And they are very different from what is described in the documentation.

So, what value i should to use?

[weaverryan]

Hey there!

For the normal MySQL one, use 5.5. That's the version you have on production. I don't think there's any need to use the exact version number.

For Sphinx, it looks like the SphinxQL basically "fakes" a MySQL server. In that case, you'd technically want whatever "version of MySQL" that they claim they are implementing. And honestly, it won't matter much. Just use 5.5 again. For Mysql, the server_version doesn't matter at all, unless you use 5.7 or higher. If you use 5.7 or higher, then you get access to just a couple of new features (like JSON fields). You won't be trying to use any of those new features, so play it safe, and just use the lower version.

Cheers!

[peter-gribanov]

@weaverryan but what about MariaDB?

If you are running a MariaDB database, you must prefix the server_version value with mariadb- (e.g. server_version: mariadb-10.2.12).

[peter-gribanov]

For Sphinx, it looks like the SphinxQL basically "fakes" a MySQL server. In that case, you'd technically want whatever "version of MySQL" that they claim they are implementing. And honestly, it won't matter much. Just use 5.5 again.

There is a value which version to use. On the local machine MySQL - 5.7.21 and SphinxQL - 2.2.9. If i use 5.7 for Sphinx, i get error:

An exception occurred while executing 'SELECT id FROM product ORDER BY id DESC LIMIT 24 OFFSET 24 OPTION max_matches=200000,field_weights=(name=40, profile_description=20)':

SQLSTATE[42000]: Syntax error or access violation: 1064 sphinxql: syntax error, unexpected IDENT, expecting $end near 'OFFSET 24 OPTION max_matches=200000,field_weights=(name=40, profile_description=20)'

If i use 5.5 or 2.2, then everything is fine. This can be important.

[alcaeus]

This is probably the stupidest thing doctrine has ever done. There is no incentive anymore to use that DBAL if you have to hard code the server version. What a pity

You have to hardcode the version if you are running different versions in production than from your development environment (which you shouldn’t). You also need to set it if you want to avoid connecting to your database server to figure it out.

Also, thanks for the compliment. I was under the impression we did dumber things than that, but that’s good to know. 🤷‍♂️

[mrtnzagustin]

I have problems with endless migration files when dbal is not detecting nulls and generate a lot of sql sentences with alter table set XX default null, etc. The problem is with doctrine/dbal 2.9.2 and mariadb 1.3.x and 1.4.x. Tried everything

[khusseini]

This is probably the stupidest thing doctrine has ever done. There is no incentive anymore to use that DBAL if you have to hard code the server version. What a pity

You have to hardcode the version if you are running different versions in production than from your development environment (which you shouldn’t). You also need to set it if you want to avoid connecting to your database server to figure it out.

Also, thanks for the compliment. I was under the impression we did dumber things than that, but that’s good to know. man_shrugging

That was probably the dumbest comment I ever made. I apologize for that.

[mrtnzagustin]

UPDATE: (at least, for me). We were having issues with this problem (have to set the server_version to the right name and version) and also problems with defaults values. So, all people who are having this problem: double check -> default values typos and server_version issue. Thank you all, doctrine rocks.