Showing entries 1 to 10 of 24
10 Older Entries »
Displaying posts with tag: MySQL / MariaDB (reset)
Binary Serializers

DBMS client applications need to store SQL query results in local memory or local files. The format is flat and the fields are ordered -- that's "serialization". The most important serializer format uses human-readable markup, like
[start of field] [value] [end of field]
and the important ones in the MySQL/MariaDB world are CSV (what you get with SELECT ... INTO OUTFILE or LOAD INFILE), XML (what you get with --xml or LOAD XML), and JSON (for which there are various solutions if you don't use MySQL 5.7).

The less important serializer format uses length, like
[length of value] [value]
and this, although it has the silly name "binary …

[Read more]
Releasing ocelotgui 1.0.0

Today ocelotgui, the Ocelot Graphical User Interface for MySQL and MariaDB, version 1.0.0, is generally available for Linux. Read the manual, see the screenshots, and download binary (.deb or .rpm) packages and GPL-licensed C++ source here.

Client-Side Predictive Parsing of MySQL/MariaDB Grammar

The Ocelot GUI client for MySQL/MariaDB is now beta. The final feature is client-side predictive parsing of every SQL clause and statement. Readers who only care how we did it can skip to the section Recursive Descent Parsers". I'll start by illustrating why this is a good feature.

Error Checks

Compare this snapshot from mysql client:

with this from ocelotgui:

The GUI advantage is that the error message is more clear and the error location is more definite. This is not always true. However, anybody who dislikes the famous message "You have an error ..." should like that there is another way to hear the bad news. It's like …

[Read more]
Comments in SQL Statements

First I will say what the comment syntax is for various vendors' dialects. Then I will get specific about some matters that specifically affect MySQL or MariaDB.

Syntax Table

Oracle 12c YES YES NO NO YES
[Read more]
Privileges in MySQL and MariaDB: The Weed Of Crime Bears Bitter Fruit

Let's look at how well MySQL and MariaDB support privileges (part of "access control" in standard terms), compared to other DBMSs, and consequences thereof.

Count the Privileges

I go to the DBMS manuals (here and starting here and here) and I count the privileges. This is like judging a town by the number of traffic lights it claims to have, but I'm trying to get an indicator for how granular the DBMS's "authorization" is.

Number of privileges listed in the manuals

MySQL/MariaDB  Oracle 12c     DB2 9.7   SQL Server 2014
31             240            52        124

Pretty …

[Read more]
Generated columns in MariaDB and MySQL

It has been seven years since the last time I blogged about generated columns, and a lot has happened -- now both MariaDB and MySQL support them. So it's time to look again, see how well they're doing, and compare to the SQL standard's Optional feature T175 Generated columns.

This is not an introductory description or an explanation why you'd want to use generated columns rather than (say) triggers and views. For that, I'd recommend the relevant manuals or the blog posts by Alexander Rubin and Anders Karlsson.

The Generation Clause

Standard …
[Read more]
Send messages between sessions on MySQL or MariaDB

Suppose you want to send a message from one SQL session to one or more other sessions, like "Hey, session#13, here is the latest figure for your calculation, please acknowledge". I'll say what Oracle and EnterpriseDB and DB2 do, then rate the various ways to implement something similar in MySQL and MariaDB, and finish with a demo of the procedure that we use, which is available as part of an open-source package.

The syntax was established by Oracle, with EnterpriseDB and IBM following suit. The details are in the Oracle 12c manual and the DB2 9.7 manual. The oversimplification is this:

  SET status = DBMS_PIPE.SEND_MESSAGE('channel#1');
  SET status = …
[Read more]
MYSQL_HISTFILE and .mysql_history

The MySQL manual says:
"mysql Logging
On Unix, the mysql client logs statements executed interactively to a history file. By default, this file is named .mysql_history in your home directory. To specify a different file, set the value of the MYSQL_HISTFILE environment variable."
The trouble with that is: it doesn't tell you what you don't need to know. So I'll tell you.


The history-file concept that MySQL and MariaDB are following is indeed "on Unix" and specifically is like the GNU History Library. There is a dependence on external libraries, Readline or …

[Read more]
Connecting to MySQL or MariaDB with sockets on Linux

The MySQL manual says

--socket=file_name, -S file_name ... On Unix, the name of the Unix socket file to use, for connections made using a named pipe to a local server.
The default Unix socket file name is /tmp/mysql.sock.

which might surprise folks who've had to contend with the error message
"Can't connect to local MySQL server through socket '[something-other-than-/tmp/mysql.sock]'".

I'll try to explain here why the name is often something quite different, how to know what the MySQL server is really listening for, what the fixes are for either users or application developers, and why it still matters.

Why the name is not always /tmp/mysql.sock

First, the Linux Foundation publishes a document "Filesystem Hierarchy Standard". Version 2.3 says …

[Read more]
The ocelotgui debugger

I have merged a debugger for MySQL/MariaDB stored procedures and functions into our GUI client and posted the source and binaries on github. It allows breakpoint, clear, continue, next, skip, step, tbreakpoint, and variable displays. Features which are rare or missing in other debuggers include:
its current platform is Linux;
it allows breakpoints on functions which are invoked within SQL statements;
it never changes existing stored procedures or functions;
it is integrated with a general GUI client;
it allows commands like gdb and allows menu items / shortcut keys like ddd;
it is available on github as C++ source with GPL licence.

It's alpha and it's fragile but it works. Here is a demo.

Start the client and connect to a running server, as root. Actually the required privileges are merely for creation of certain objects and SUPER, but …

[Read more]
Showing entries 1 to 10 of 24
10 Older Entries »