MySQL ODBC connector: build and run tests

Январь 29th, 2013 | Posted by elenst in Pensieve | Testing - (Комментарии отключены)

I spent some time on it, and I have a feeling I’ll have to do it again some day, so here it goes, assuming I’ve downloaded the source package of the connector (in my case it was 5.2.3) and either installed MySQL client library, or have a binaries and headers somewhere.

I will of course need different stuff for building, and in addition to that, unixODBC and unixODBC-devel (that’s how they are called for Fedora’s yum, I didn’t check others yet).

Then, if we have MySQL client library installed system-wide, I run
cmake -G "Unix Makefiles" -DWITH_UNIXODBC=1

And if we have it in a secret place, I run
cmake -G "Unix Makefiles" -DWITH_UNIXODBC=1 -DMYSQL_INCLUDE_DIR=<secret folder with MySQL/MariaDB include files> -DMYSQL_LIB_DIR=<secret folder with MySQL/MariaDB client libs>

Either way, just hope it will find all it needs. What it needs is another story: I’ve heard today that the connector is *supposed* to link statically, only it does not. It links dynamically if it can, which kind of supported by the connectors manual. It’s rather unfortunate, since it doesn’t seem to work all that well with the current MySQL client library:

$ ldd -r lib/libmyodbc5w.so
linux-vdso.so.1 => (0x00007fffb49ff000)
libodbc.so.2 => /lib64/libodbc.so.2 (0x00007f5d1fdb8000)
libmysqlclient.so.18 => /mysql-5.5.29/lib/libmysqlclient.so.18 (0x00007f5d1f959000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f5d1f73d000)
libm.so.6 => /lib64/libm.so.6 (0x00007f5d1f442000)
libodbcinst.so.2 => /lib64/libodbcinst.so.2 (0x00007f5d1f22f000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007f5d1f02b000)
libc.so.6 => /lib64/libc.so.6 (0x00007f5d1ec74000)
libltdl.so.7 => /lib64/libltdl.so.7 (0x00007f5d1ea6a000)
librt.so.1 => /lib64/librt.so.1 (0x00007f5d1e862000)
/lib64/ld-linux-x86-64.so.2 (0x000000353c200000)
undefined symbol: dynstr_append_os_quoted (lib/libmyodbc5w.so)
undefined symbol: dynstr_append (lib/libmyodbc5w.so)
undefined symbol: strfill (lib/libmyodbc5w.so)
undefined symbol: init_dynamic_string (lib/libmyodbc5w.so)
undefined symbol: dynstr_realloc (lib/libmyodbc5w.so)
undefined symbol: dynstr_free (lib/libmyodbc5w.so)
undefined symbol: dynstr_append_mem (lib/libmyodbc5w.so)

Anyway, as long as it builds, we can run at least some tests from the test folder. There is even a perl script, but it doesn’t work for me, and neither does make test. I don’t know why, and for now I don’t care. I just need to get something run.

So, I set 2 variables just like the manual says:

ODBCINI=/mysql-connector-odbc-5.2.3-src/test/odbc.ini
ODBCSYSINI=/mysql-connector-odbc-5.2.3-src/test

The second one looks stupid, but I set it anyway. Whatever it takes.

In odbc.ini, I add

[elenstdsn]
Driver=/mysql-connector-odbc-5.2.3-src/lib/libmyodbc5w.so
SERVER=localhost
UID=root
DATABASE=test
PORT=3306
Password=test

In odbcinst.ini, I add

[elenstODBC]
Driver=/mysql-connector-odbc-5.2.3-src/lib/libmyodbc5.so
UsageCount=1

Now, if I didn’t forget to start the server (importantly, on /tmp/mysql.sock, otherwise tests fail somewhere in the middle, even though they get the socket as a parameter), I can run

./test/my_basics elenstdsn root test /tmp/mysql.sock

I can’t say it works, it would be exaggeration, but at least it does something marginally sensible.

MySQL type sizes

Август 27th, 2012 | Posted by elenst in Pensieve - (Комментарии отключены)

Another piece of trivia which sometimes is not easy to find fast, and which easily gets demoted into the cold cache (if that) of my brain when isn’t used.

CHAR(M) 255 characters
VARCHAR(M) 65,535 characters
TINYTEXT 255 characters
TEXT 65,535 characters
MEDIUMTEXT 16,777,215 characters
LONGTEXT 4,294,967,295 characters
BINARY 255 bytes
VARBINARY 65,535 bytes
TINYBLOB 255 bytes
BLOB 65,535 bytes
MEDIUMBLOB 16,777,215 bytes
LONGBLOB 4,294,967,295 bytes
TINYINT -128 — 127
TINYINT UNSIGNED 255
SMALLINT -32,768 — 32,767
SMALLINT UNSIGNED 65,535
MEDIUMINT -8,388,608 — 8,388,607
MEDIUMINT UNSIGNED 16,777,215
INT -2,147,683,648 — 2,147,683,647
INT UNSIGNED 4,294,967,295
BIGINT -9,223,372,036,854,775,808 — 9,223,372,036,854,775,807
BIGINT UNSIGNED 18,446,744,073,709,551,615
FLOAT precision 0..23
DOUBLE precision 24..53
DECIMAL 65 digits
ENUM 65,635 members
SET 64 members
BIT 1..64 bits
DATE 1000-01-01 — 9999-12-31
TIME -838:59:59 — 838:59:59
DATETIME 1000-01-01 00:00:00 — 9999-12-31 23:59:59
TIMESTAMP 1970-01-01 00:00:00, 1970-01-01 00:00:01 — 2038-01-19 03:14:07 UTC
YEAR(4) 1901 — 2155
YEAR(2) 1970 — 2069

Mysterious completion_type, also known as “do something on transaction commit”

Август 12th, 2012 | Posted by self in Pensieve - (Комментарии отключены)

While with most MySQL variables, if you have an idea what it does or what it’s for, but don’t remember the exact name, you can make an educated guess, this one always gets me in a trouble. I do know what it does, and yet, when I need the name, there is no way I can ever find it on the list of MySQL options, and it’s not even good for googling, as the description one can give is too generic (try to google for “transaction commit rollback” and see what happens).

So, completion_type.
And although I remember it now, I can forget in the future, so here are the values:

NO_CHAIN (or 0)

COMMIT and ROLLBACK are unaffected. This is the default value.

CHAIN (or 1)

COMMIT and ROLLBACK are equivalent to COMMIT AND CHAIN and ROLLBACK AND CHAIN, respectively. (A new transaction starts immediately with the same isolation level as the just-terminated transaction.)

RELEASE (or 2)

COMMIT and ROLLBACK are equivalent to COMMIT RELEASE and ROLLBACK RELEASE, respectively. (The server disconnects after terminating the transaction.)

And this is important, too, as I tend to forget and waste a lot of time trying to figure out why “nothing works”:

completion_type affects transactions that begin with START TRANSACTION or BEGIN and end with COMMIT or ROLLBACK. It does not apply to implicit commits [...] It also does not apply for XA COMMIT, XA ROLLBACK, or when autocommit=1.

Btw, the latter is not exactly true, or I don’t know what they meant by that. Surely it works when autocommit=1, otherwise why would we even need BEGIN or START TRANSACTION?..