Generate random utf8 string in MySQL

Generate random utf8 string in mysql :
-- please note that probably it isn't fully correct, because char_length() doesn't show expected number
-- additionally - performance may be not acceptable at all for heavy tests
-- but it was useful enough for my purposes
-- use like this : select gen_utf8(128); (up to 2000 characters - increase in code if needed)

set names utf8;
drop function if exists gen_utf8char;
drop function if exists gen_utf8;

delimiter //

create function gen_utf8char()
returns char(1) character set utf8
begin
declare res char(3) character set binary;

case ceil(rand()*3)
when 1 then return char(floor(rand()*127));
when 2 then return char(b'11000000' | 2 + floor(14*rand()), b'10000000' | floor(32*rand()));
else        return char(b'11100000' | 1 + floor( 7*rand()), b'10000000' | floor(32*rand()), b'10000000' | floor(32*rand()));
end case;
end//


create function gen_utf8(n int)
returns varchar(2000) character set utf8
begin

declare res varchar(2000) character set utf8 default "";

label1: LOOP
set res = concat(res, gen_utf8char());

    IF n > 1 THEN
      SET n = n - 1;
      ITERATE label1;
    END IF;

    LEAVE label1;
END LOOP label1;

return res;
end//

delimiter ;

"-- The C compiler identification is unknown" when building MySQL on Win

It may be nightmare to troubleshoot if CMake shows errors on Windows. Various meaningless errors were shown, but the most important thing is to try to troubleshoot errors in CMakeFiles/CMakeError.log file.

In particular, my "-- The C compiler identification is unknown" error was solved by removing ComponentModelCache in
C:\Users\user\AppData\Local\Microsoft\VisualStudio\11.0\

CMake worked smoothly afterward.

MySQL 'Cannot create thread' on Linux

It may be not obvious, but on Linux RLIMIT_NPROC  limits not number of processes, but number of threads.
Here is proof:

[a@a-lnx ~]$ cat thread_test.c 

#include pthread.h
#include string.h
#include stdio.h
#include stdlib.h
#include unistd.h
#include errno.h
#include ctype.h


static void *
thread_start(void *arg)
{
    long int n = (long int) arg;

    sleep(10000000);

    printf("Thread %d: exited\n", n);

    return 0;
}

int
main(int argc, char *argv[])
{
    long int i;

/* Create threads until get error */
    for (i=0; ; i++) {

        if (pthread_create(malloc(sizeof(pthread_t)), NULL, &thread_start, (void*)i) != 0)
        {
            printf("Error after %d threads\n", i);
            exit(i);
        }
    }

    exit(EXIT_SUCCESS);
}
[a@a-lnx ~]$ gcc -pthread thread_test.c
[a@a-lnx ~]$ ./a.out
Error after 906 threads
[a@a-lnx ~]$ ulimit -u
1024
[a@a-lnx ~]$ ulimit -u 2000
[a@a-lnx ~]$ ./a.out
Error after 1883 threads

So make sure it is set high enough, otherwise MySQL can generate 'Cannot create thread' errors. 

Since this limit applies to user (not to process), this parameter is especially important if multiple mysql instances are running (with the same user).

"X--tag=CC: command not found"

If you ever get this error when compiling sysbench, try following commands before make:

echo=echo
export echo

Error should gone now :)

Vieing other's session variables or injecting admin code

PART 1: Vieing other's session variables

Perhaps we want to see details of context for some existing MySQL session. In particular session variables or session status details, or even want to change sonfiguration of others session. This may be the most useful for replication session or any other long running job.

Official way is that such feature is not supported, but there situations when we still able to make it.

The easiest case is one when we know that the session updates some table, and this table has some BEFORE/AFTER trigger slot empty. The trick is to create trigger and check connection_id() inside it. Example below demonstrates idea, (invovling stored procedure):


delimiter ;;

create procedure export_session_vars(connid int)
begin
    declare exit handler for SQLEXCEPTION begin end;
    if CONNECTION_ID() = connid and @hack_export_happened is null then
        select * from information_schema.session_variables order by 1 into outfile '/tmp/output.txt';
        set @hack_export_happened = 1;
    end if;
end;;


create trigger hack after insert on t for each row call export_session_vars(2);


Voila, when session with SESSION_ID=2 inserts some row in table `t`, we have magically created file '/tmp/output.txt' with session variables values. We can also insert results into some table instead of file if we wish. Now we can drop trigger to avoid useless performance penalty from it.


Note 1: Creating such triggers may work but also may break your system completely. I cannot make any guarantee, so do it only if you are sure what you do and only on your own risk.

Note 2: In 5.0 we don't have session_variables table in information_schema, so we should use SELECT with implicit variables values, e.g.:
SELECT "auto_increment_increment" as variable_name, @auto_increment_increment as variable_value
UNION
SELECT "auto_increment_offset" as variable_name, @auto_increment_offset as variable_value
UNION
...


In case if we don't know involved tables or if session does only reads - I don't know any solution.
If we would be able to create similar trigger on slow_log table, that would solve problem. But triggers on system tables are not allowed. (Theoretically we even could ALTER mysql.slow_log to be FEDERATED to test.slow_log and create such trigger on test.slow_log, but it will not help anyway).



PART 2: Injecting Admin Code.

So considerations above lead to possible useful feature in future of MySQL. (Not sure if similar feature exists in other RDBMS).

Perhaps we have table mysql.injection similar to mysql.event, but with two additional columns `CONNECTION_ID` and `RESULT`.

Now each session, before handling next query, checks table mysql.injection and executes commands with matching CONNECTION_ID, (e.g. if `RESULT` column is NULL).

Note 1: Introducing additional (automatic) column `READONLY` may be necessary: 'READONLY' injections may happen on next query execution, while others may happen only if no tables are locked and after transaction is completed.
Note 2: Details may differ, but I hope general idea is clear and shouldn't be difficult to implement.

This tool will be really powerful, and shouldn't give any performance penalty if implemented properly (e.g. dedicated thread may read new entries, parse and place them into memory, so other sessions will not bother to actually select from table).

Conclusion: First part of this post brings idea about using triggers to inject code into other sessions. Second part brings an idea of implementing general Injection of admin code in RDBMS.

Com_flush vs Flush_commands

MySQL status variables has two variables with similar name (and description), but quite different meaning.

Flush_commands - internal version of tables (incremented when tables are actually flushed, i.e. in SQL command FLUSH TABLES or in COM_REFRESH packet).
Com_flush - incremented on each FLUSH command and in each COM_REFRESH packet.

Difference 1: at startup Flush_commands has initial value 1. So name of variable is somewhat wrong, because "1" means no commands were executed yet.

Difference 2: Both FLUSH command and COM_REFRESH packet internally call reload_acl_and_cache() function, but not every call will actually flush tables (e.g. it may flush only privileges or only hosts).
So following condition is always true on GLOBAL level of MySQL:

Com_flush >= (Flush_commands -1)


Difference 3: logical conclusion is that Flush_commands is only GLOBAL variable, while Com_flush has both SESSION and GLOBAL values.


This looks quite complex, but looking into history will explain wrong name of "Flash_commands" variable. Initially there was no SQL command FLUSH in MySQL and COM_REFRESH packet had no parameters - so every COM_REFRESH did increment this variable.

Building 5.5.4-m3 in Visual Studio 10 beta 2

I've got new Window7 system with Visual Studio 10 beta 2, so decided to build MySQL from source.

mysql-5.5.4-m3.tar.gz

First problem was cmake, which reported
"Make Error: Could not create named generator Visual Studio 10"

It was solved by installing nightly build of cmake from http://www.cmake.org/files/dev/

I've managed generate MySQL.sln file, so next step is compiling.
It gave plenty errors like:

mysql-5.5.4-m3\include\my_global.h(1174): fatal error C1189: #error : sizeof(void *) is neither sizeof(int) nor sizeof(long) nor sizeof(long long)

I eliminated these errors by ugly fix with adding following lines right before statements which generate the error in my_global.h .

#ifndef SIZEOF_CHARP
#define SIZEOF_CHARP SIZEOF_LONG
#endif

Afterward I got error shared access writing to Debug\gen_lex_hash.exe , but rebuild finally showed "no errors".

Conclusion:
mysql-5.5.4-m3 was finally built and started with Visual Studio 10 Beta 2 after couple manual fixes.