Options for MySQL or MariaDB Connector C libraries
Some Connector/C options can have better explanations than what’s in the MySQL or MariaDB manuals. Here they are.
Files
Putting together the MySQL and MariaDB Connector/C manuals’ words, Connector/C reads these files in this order:
/etc/my.cnf MYSQL always. MariaDB only if DEFAULT_SYSCONFDIR not defined /etc/mysql/my.cnf MySQL always. MariaDB only if DEFAULT_SYSCONFDIR not defined SYSCONFDIR/my.cnf MYSQL if SYSCONFDIR defined. + MariaDB if DEFAULT_SYSCONFDIR defined $MYSQL_HOME/my.cnf MySQL always. MariaDB always. defaults-extra-file MySQL always. MariaDB never (but they say maybe soon) ~/.my.cnf MySQL always. MariaDB always ~/.mylogin.cnf MySQL always. MariaDB never
What they don’t say is …
If $MYSQL_HOME is null, then they won’t look in $MYSQL_HOME/my.cnf.
If $MYSQL_HOME is non-null but also $MARIADB_HOME is non-null, then MariaDB will look only in $MARIADB_HOME/my.cnf.
What really happens with environment variables is in this snippet from MariaDB’s source file ma_default.c:
/* CONC-537: Read configuration files from MYSQL_HOME directory only if
MARIADB_HOME was not set */
if (!(env= getenv("MARIADB_HOME")))
env= getenv("MYSQL_HOME");
if (env && add_cfg_dir(configuration_dirs, env))
goto error;
Similarly, you can cause a working mysql client to fail thus:
#undefine the $HOME environment variable unset HOME #look for ~/.my.cnf -- this will work ls ~/.my.cnf #connect -- this will fail iff ~/.my.cnf has special options mysql
In other words, what decides matters may be ~/.my.cnf, but may be $HOME.
And they don’t really tell you how to know if SYSCONFDIR is defined. Sure, it’s “usually” e.g. /etc if the connector was built on Red Hat, and you can see what their own statically-linked programs do by saying –help or –print-defaults, but those aren’t guarantees that the library you’re loading was built with the same cmake.
And beware, my.cnf might be symlinked. For example, I see this on Ubuntu 20:
$ ls -l /etc/mysql/my.cnf lrwxrwxrwx 1 root root 11 May 26 2024 /etc/mysql/my.cnf -> mariadb.cnf
So, rather than believe the manuals, believe the strace utility. For example, I see this when I run a program optiontest that dynamically loads a connector and connects:
$ strace 2>&1 ./optiontest | grep my.cnf
stat("/etc/my.cnf", 0x7fff913b7bb0) = -1 ENOENT (No such file or directory)
stat("/etc/mysql/my.cnf", {st_mode=S_IFREG|0644, st_size=1146, ...}) = 0
openat(AT_FDCWD, "/etc/mysql/my.cnf", O_RDONLY|O_CLOEXEC) = 3
stat("/home/pgulutzan/.my.cnf", {st_mode=S_IFREG|0644, st_size=749, ...}) = 0
openat(AT_FDCWD, "/home/pgulutzan/.my.cnf", O_RDONLY|O_CLOEXEC) = 3
Value quirks
The MySQL manual will tell you “When you use a startup option to set a variable that takes a numeric value, the value can be given with a suffix of K, M, G, T, P, or E (either uppercase or lowercase) to indicate a multiplier of 1024, 10242, 10243, 10244, 10245, or 10246; that is, units of kilobytes, megabytes, gigabytes, terabytes, petabytes, or ettabytes, respectively.” (They mean “exabytes”.)
That doesn’t apply to options in an options file. If I say option=1K, the connector sees 1 and stops evaluating when it encounters a non-numeric.
The MariaDB manual even contains an example that contains “–max_allowed_packet=1GB”, though I can’t see where “GB” is documented.
And a connector even accepts this in a my.cnf file:
max-allowed-packet = "+1111111111"
It will change the hyphens to underscores and strip the quotes (single or double quotes both work) and ignore the sign.
This is all okay, therefore it is bad. Don’t be lax re syntax of a max.
Incompatible ABI
In MySQL 5.7 and MariaDB current, the value of option MYSQL_REPORT_DATA_TRUNCATION is 19. In MySQL current, it is 14. This happens because MySQL skipped some values when upgrading to version 8.0, which you can see by comparing mysql.h before-and-after. MariaDB did not make this change. Here’s part of MySQL 8.3 mysql.h on the left, part of MariaDB 12.0 mysql.h on the right:
enum mysql_option { enum mysql_option {
MYSQL_OPT_CONNECT_TIMEOUT, MYSQL_OPT_CONNECT_TIMEOUT,
MYSQL_OPT_COMPRESS, MYSQL_OPT_COMPRESS,
MYSQL_OPT_NAMED_PIPE, MYSQL_OPT_NAMED_PIPE,
MYSQL_INIT_COMMAND, MYSQL_INIT_COMMAND,
MYSQL_READ_DEFAULT_FILE, MYSQL_READ_DEFAULT_FILE,
MYSQL_READ_DEFAULT_GROUP, MYSQL_READ_DEFAULT_GROUP,
MYSQL_SET_CHARSET_DIR, MYSQL_SET_CHARSET_DIR,
MYSQL_SET_CHARSET_NAME, MYSQL_SET_CHARSET_NAME,
MYSQL_OPT_LOCAL_INFILE, MYSQL_OPT_LOCAL_INFILE,
MYSQL_OPT_PROTOCOL, MYSQL_OPT_PROTOCOL,
MYSQL_SHARED_MEMORY_BASE_NAME, MYSQL_SHARED_MEMORY_BASE_NAME,
MYSQL_OPT_READ_TIMEOUT, MYSQL_OPT_READ_TIMEOUT,
MYSQL_OPT_WRITE_TIMEOUT, MYSQL_OPT_WRITE_TIMEOUT,
MYSQL_OPT_USE_RESULT, MYSQL_OPT_USE_RESULT,
MYSQL_REPORT_DATA_TRUNCATION, MYSQL_OPT_USE_REMOTE_CONNECTION,
MYSQL_OPT_RECONNECT, MYSQL_OPT_USE_EMBEDDED_CONNECTION,
MYSQL_PLUGIN_DIR, MYSQL_OPT_GUESS_CONNECTION,
MYSQL_DEFAULT_AUTH, MYSQL_SET_CLIENT_IP,
MYSQL_OPT_BIND, MYSQL_SECURE_AUTH,
MYSQL_OPT_SSL_KEY, MYSQL_REPORT_DATA_TRUNCATION,
MYSQL_OPT_SSL_CERT, MYSQL_OPT_RECONNECT,
MYSQL_OPT_SSL_CA, MYSQL_OPT_SSL_VERIFY_SERVER_CERT,
MYSQL_OPT_SSL_CAPATH, MYSQL_PLUGIN_DIR,
MYSQL_OPT_SSL_CIPHER, MYSQL_DEFAULT_AUTH,
MYSQL_OPT_SSL_CRL, MYSQL_OPT_BIND,
... ...
The skipped values were MYSQL_OPT_USE_REMOTE_CONNECTION, MYSQL_OPT_USE_EMBEDDED_CONNECTION, MYSQL_OPT_GUESS_CONNECTION, MYSQL_SET_CLIENT_IP, MYSQL_SECURE_AUTH, and later MYSQL_OPT_SSL_VERIFY_SERVER_CERT. In “Changes in MySQL 8.0” the wording is “not binary compatible”. MariaDB deserves thanks for doing nothing.
This means life would be easier if mysql.h and Connector/C and the server all had the same version and vendor. Possibly somebody can do some subtracting of the skipped values either within the connector or before calling it, thus: call mysql_get_client_info() to determine whether it returns a MySQL version such as “9.x” or a MariaDB-Connector-C version such as “3.x” (unfortunately it won’t say “MariaDB” but you can tell from the number) (mysql_get_client_info is legal immediately after mysql_init so you don’t need to set options or connect); check #ifdef MYSQL_REPORT_DATA_TRUNCATION and if it’s 14 to determine whether mysql.h came from MySQL or MariaDB; if mysql.h and the connector are from different vendors then call mysql_options(…, option_value+-offset, …) instead of mysql_options(…, option_value, …).
However, a fewer-surprises solution is to get the connector to process the option files directly, so you don’t have to call mysql_options() for all possible numeric values. You can persuade the connector to do that with MYSQL_READ_DEFAULT_FILE (option value 4 in both connectors) or MYSQL_READ_DEFAULT_GROUP (option value 5 in both connectors).
MYSQL_READ_DEFAULT_FILE
The MySQL manual says this is a directive to “Read options from the named option file instead of from my.cnf.” (They mean “instead of any other file”.)
This would be useful, except that it’s exclusive — if you call it twice then the effects of one of the calls are erased.
With MariaDB only, if you pass mysql_options(…MYSQL_READ_DEFAULT_FILE, “”) then the connector reads all option files as if blanks are wildcards. This is not useful, though, because you can get the same effect with either MySQL or MariaDB by passing MYSQL_READ_DEFAULT_GROUP instead.
MYSQL_READ_DEFAULT_GROUP
This too is exclusive but at least you can hope that the connector reads all the default files.
And it’s not exclusive about groups, that is, if you ask for group “x” you get not only group “x” but also the default groups. (Alas, this may not be true for included files.) Or you can ask for group “” and get nothing but the default groups.
MariaDB handles the default groups [client] [client-server] [client-mariadb]. MySQL handles the default group [client] — [mysql] is supposed to be for the mysql client only. It follows then that, if you want your .cnf to work with either MySQL or MariaDB, you have to say
[client] #options that apply for MySQL [client-mariadb] #options that apply for MariaDB
… a MySQL connector will ignore [client-mariadb]. A MariaDB connector will not ignore [client] but will override [client] options with the [client-mariadb] options because they come later.
Since the effects of one of the calls are erased if you call with MYSQL_READ_DEFAULT_GROUP twice, you’re limited to a maximum of one non-default group. Unless you can add one more with –defaults-group-suffix. This is something that MySQL has now and MariaDB might have soon.
Combining file options with command-line options
Suppose you want to let MYSQL_READ_DEFAULT_GROUP handle what’s in the option files, but you also want to process a command line that says –connect_timeout=100.
Seems easy enough, eh? You determine that “connect_timeout” is associated with MYSQL_OPT_CONNECT_TIMEOUT, you put the value after the “=” in an unsigned int, and call:
int mysql_option_result;
mysql_option_result= mysql_options(mysql, MYSQL_READ_DEFAULT_GROUP, "client");
printf("d after MYSQL_READ_DEFAULT_GROUP %d\n", mysql_option_result);
mysql_option_result= mysql_options(mysql, MYSQL_OPT_CONNECT_TIMEOUT, &connect_timeout);
printf("**** d2 after mysql_options MYSQL_OPT_CONNECT_TIMEOUT %d\n", mysql_option_result);
(The option numbers in these two cases are the same for all connectors.)
You see mysql_option_result is 0 both times, you check later with mysql_get_option() and find that the value is 100, and all seems well. But it’s not. Because: the order of option execution is not necessarily the order that you call mysql_options(). In my tests, if a my.cnf file just happens to contain “connect_timeout=200”, that overrides. Even if that’s okay (to me it doesn’t look okay), it’s still undocumented behaviour.
(Aside: if the only command-line options are host|user|password|database|port|socket, these control mysql_real_connect() arguments so you don’t need to worry about an inability to use mysql_options() for them.)
A decent solution is: explain that command-line options might not take effect, and print warnings if mysql_get_option(…MYSQL_OPT_CONNECT_TIMEOUT… ) result is not 100. And yet that’s not a great solution — people might expect that command-line options are trumps. Any way to force that?
Well, I can think of two ways:
1. Open ~/.my.cnf for appending, strip the –s from the arguments and put them in, call mysql_options with MYSQL_READ_DEFAULT_FILE, call mysql_real_connect, then remove what you appended.
2. Create a temporary file, make the first line something like this (replacing “$HOME” with your full path) !include $HOME/.my.cnf, then strip the –s from the arguments and put them in, call mysql_options with MYSQL_READ_DEFAULT_FILE, call mysql_real_connect, then remove what you appended.
But: you might not have write privileges (even if you do, there might be a sticky bit as if you’re in a sandbox).Or: you might conflict with some other program that wants the same files Or: you’d be missing the rest of the default option files besides ~/.my.cnf.
More option files
MySQL’s ability to handle defaults-extra-file is an advantage over MariaDB. But it might not last forever: Add option that corresponds to –defaults-extra-file MariaDB command-line option is marked as something that might happen in version 3.5 (as I write this, the latest version is 3.4.3).
MySQL’s ability to handle ~/.mylogin.cnf is an advantage over MariaDB. But read my earlier post and you’ll see that I supply a decrypter as part of the ocelotgui package.
MYSQL_OPT_INIT_COMMAND
Assuming you have a client that works with default option files for either MariaDB or MySQL, you can run a quick test.
Start the client. Make this simple sample table:
CREATE DATABASE w; CREATE TABLE w.t (etc INT, home INT); INSERT INTO w.t VALUES (0, 0);
Exit.
Add these two lines at the end of ~/.my.cnf:
init_command = "UPDATE w.t SET etc = etc + 1;" init_command = "UPDATE w.t SET home = home + 1;"
Start the client again and say
SELECT * FROM w.t;
The result will look like this:
Now realize: this result proves that both UPDATE statements happened. The init_command option is an undocumented example of what one could call “multi-element” options. An ordinary option overwrites (cancels) any earlier occurrence. A multi-element option, if you say it twice, there are two things to execute (and the order of execution isn’t guaranteed).
This could be unpleasant if the first occurrence is hidden in /etc/my.cnf and you only see the second occurrence in ~/.my.cnf and think it’s all there is. Add that to the fact that the updates might not appear in the client’s log.
So if your client supports anything like “SOURCE file-with-statements”, and the statements are for the initial connect (not auto-reconnect), it’s probably better to use SOURCE instead of init_command.
MYSQL_OPT_CONNECT_ATTR_ADD
With MySQL’s mysql_options4 this looks like:
mysql_options4(mysql, MYSQL_OPT_CONNECT_ATTR_ADD, "program-name", "my_wonderful_client");
The MariaDB connector will translate mysql_options4() to mysql_optionsv().
There are other programs that will add “program-name” here, so you might as well too. It will appear in a performance_schema table, so you might as well have performance_schema on. It’s a way to put a comment in a my.cnf file that will be visible with SELECT.
Like init_command, MYSQL_OPT_CONNECT_ATTR_ADD is a multi-element option. So to avoid duplication you might want to say MYSQL_OPT_CONNECT_ATTR_DELETE first.
mysql_get_option
MariaDB is recommending mysql_get_optionv() but mysql_get_option() still works.
Or rather, it works after mysql_real_connect(). It’s useless to call mysql_get_option() until you’re connected, and it’s undefined-results-time if the option wasn’t set earlier.
I thought that the manuals’ examples for strings looked odd, so I supply this example instead (notice the &):
const char *set_charset_name= NULL;
int option_result= mysql_get_option(mysql, MYSQL_SET_CHARSET_NAME, &set_charset_name);
if (set_charset_name == NULL) printf("(null)\n");
else printf("**** %d, MYSQL_SET_CHARSET_NAME=%s\n", option_result, set_charset_name);
Some mysql_get_option() variations might fail even though the option is set correctly and the call is in the right place. This might tempt people to look at session variables instead, but they’re tricky too. For example, mysql_get_option(…MYSQL_OPT_MAX_ALLOWED_PACKET…) does not return the same thing as SELECT @@session.max_allowed_packet.
socket
Suppose, given some of what I’ve described, you decide it’s better to use MariaDB Connector/C when connecting to a MariaDB server, and use MySQL Connector/C when connecting to a MySQL server. So how do you know what you’re connecting to, before you connect?
Well, on my local network I have instances of both servers. For one of them I can use telnet localhost port-3309
~$ telnet 192.168.1.68 3309
Trying 192.168.1.68...
Connected to 192.168.1.68.
Escape character is '^]'.
I
9.3.0$xPUPGNw
�99PRRRp!caching_sha2_password2#08S01Got timeout reading communication packetsConnection closed by foreign host.
For the other I can use telnet localhost port-3310
$ telnet 192.168.1.68 3310 Trying 192.168.1.68... Connected to 192.168.1.68. Escape character is '^]'. R 12.0.1-MariaDB&J%xPd*@�=Dr5Ng?ExQh<.mysql_native_passwordConnection closed by foreign host.
The important value on port-3309 is “9.3.0” so I know it’s MySQL 9.3.0. The important value on port-3310 is “12.0.1-MariaDB” so I know it’s MariaDB 12.0.1.
I can read these things within a program via the socket() utility, and discard. Then with pgfindlib I can pick the connector on my machine that’s best for that port.
But I don’t.
For now, I find it simpler to just suggest in the manual that one of the options on the the command line or in an option file should say what the expected server is.
documentation bugs
In June 2025 I submitted a bug report with 15 complaints about MariaDB documentation. I don’t mean to suggest that the MySQL documentation is better.
ocelotgui
If you’re wondering “Will Peter Gulutzan follow his own advice?” — oh yes, I intend to implement the suggestions I’ve made here, in ocelotgui. I’m just going to wait a week or two in case somebody finds a flaw. Meantime, ocelotgui version 2.5 is not doing a wonderful job of connecting, which I acknowledge. As always, source and executables are downloadable from github.
Errors?
I might be mistaking or omitting things, so read this blog post again after a while, in case I’ve had to make corrections. If you find a particular mistake or omission, comment on this post or send email, my name is pgulutzan and my domain is ocelot.ca.
