tag:blogger.com,1999:blog-27068099669823234092024-03-07T21:22:44.729-08:00Yet Another MySQL BlogUnknownnoreply@blogger.comBlogger10125tag:blogger.com,1999:blog-2706809966982323409.post-78824456692092621752015-06-09T01:29:00.001-07:002015-06-09T01:29:46.057-07:00Generate random utf8 string in MySQL<div dir="ltr" style="text-align: left;" trbidi="on">
Generate random utf8 string in mysql :<br />
-- please note that probably it isn't fully correct, because char_length() doesn't show expected number<br />
-- additionally - performance may be not acceptable at all for heavy tests<br />
-- but it was useful enough for my purposes<br />
-- use like this : select gen_utf8(128); (up to 2000 characters - increase in code if needed)<br />
<br />
<span style="font-size: x-small;">set names utf8;</span><br />
<span style="font-size: x-small;">drop function if exists gen_utf8char;</span><br />
<span style="font-size: x-small;">drop function if exists gen_utf8;</span><br />
<span style="font-size: x-small;"><br /></span>
<span style="font-size: x-small;">delimiter //</span><br />
<span style="font-size: x-small;"><br /></span>
<span style="font-size: x-small;">create function gen_utf8char()</span><br />
<span style="font-size: x-small;">returns char(1) character set utf8</span><br />
<span style="font-size: x-small;">begin</span><br />
<span style="font-size: x-small;">declare res char(3) character set binary;</span><br />
<span style="font-size: x-small;"><br /></span>
<span style="font-size: x-small;">case ceil(rand()*3)</span><br />
<span style="font-size: x-small;">when 1 then return char(floor(rand()*127));</span><br />
<span style="font-size: x-small;">when 2 then return char(b'11000000' | 2 + floor(14*rand()), b'10000000' | floor(32*rand()));</span><br />
<span style="font-size: x-small;">else return char(b'11100000' | 1 + floor( 7*rand()), b'10000000' | floor(32*rand()), b'10000000' | floor(32*rand()));</span><br />
<span style="font-size: x-small;">end case;</span><br />
<span style="font-size: x-small;">end//</span><br />
<span style="font-size: x-small;"><br /></span>
<span style="font-size: x-small;"><br /></span>
<span style="font-size: x-small;">create function gen_utf8(n int)</span><br />
<span style="font-size: x-small;">returns varchar(2000) character set utf8</span><br />
<span style="font-size: x-small;">begin</span><br />
<span style="font-size: x-small;"><br /></span>
<span style="font-size: x-small;">declare res varchar(2000) character set utf8 default "";</span><br />
<span style="font-size: x-small;"><br /></span>
<span style="font-size: x-small;">label1: LOOP</span><br />
<span style="font-size: x-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>set res = concat(res, gen_utf8char());</span><br />
<span style="font-size: x-small;"><br /></span>
<span style="font-size: x-small;"> IF n > 1 THEN</span><br />
<span style="font-size: x-small;"> SET n = n - 1;</span><br />
<span style="font-size: x-small;"> ITERATE label1;</span><br />
<span style="font-size: x-small;"> END IF;</span><br />
<span style="font-size: x-small;"><br /></span>
<span style="font-size: x-small;"> LEAVE label1;</span><br />
<span style="font-size: x-small;">END LOOP label1;</span><br />
<span style="font-size: x-small;"><br /></span>
<span style="font-size: x-small;">return res;</span><br />
<span style="font-size: x-small;">end//</span><br />
<span style="font-size: x-small;"><br /></span>
<span style="font-size: x-small;">delimiter ;</span><br />
<div>
<br /></div>
</div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-2706809966982323409.post-68995668421566497292013-08-08T07:10:00.005-07:002013-08-08T07:10:57.359-07:00"-- The C compiler identification is unknown" when building MySQL on WinIt 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.<br />
<br />
In particular, my "-- The C compiler identification is unknown" error was solved by removing ComponentModelCache in<br />
C:\Users\user\AppData\Local\Microsoft\VisualStudio\11.0\<br />
<br />
CMake worked smoothly afterward.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-2706809966982323409.post-4592871753834503872012-09-25T10:29:00.002-07:002012-09-25T10:33:24.398-07:00MySQL 'Cannot create thread' on LinuxIt may be not obvious, but on Linux <b style="background-color: #f8f8f8; color: #502000; font-family: monospace, courier;">RLIMIT_NPROC</b> limits not number of processes, but number of threads.<br />
Here is proof:<br />
<br />
<span style="font-family: 'Courier New', Courier, monospace;">[a@a-lnx ~]$ cat thread_test.c </span><br />
<span style="font-family: 'Courier New', Courier, monospace;"></span><br />
<span style="font-family: 'Courier New', Courier, monospace;">#include pthread.h</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">#include string.h</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">#include stdio.h</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">#include stdlib.h</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">#include unistd.h</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">#include errno.h</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">#include ctype.h</span><br />
<br />
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span>
<span style="font-family: 'Courier New', Courier, monospace;">static void *</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">thread_start(void *arg)</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">{</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> long int n = (long int) arg;</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span>
<span style="font-family: 'Courier New', Courier, monospace;"> sleep(10000000);</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span>
<span style="font-family: 'Courier New', Courier, monospace;"> printf("Thread %d: exited\n", n);</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span>
<span style="font-family: 'Courier New', Courier, monospace;"> return 0;</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">}</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span>
<span style="font-family: 'Courier New', Courier, monospace;">int</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">main(int argc, char *argv[])</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">{</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> long int i;</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span>
<span style="font-family: 'Courier New', Courier, monospace;">/* Create threads until get error */</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> for (i=0; ; i++) {</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span>
<span style="font-family: 'Courier New', Courier, monospace;"> if (pthread_create(malloc(sizeof(pthread_t)), NULL, &thread_start, (void*)i) != 0)</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> {</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> printf("Error after %d threads\n", i);</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> exit(i);</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> }</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> }</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span>
<span style="font-family: 'Courier New', Courier, monospace;"> exit(EXIT_SUCCESS);</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">}</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">[a@a-lnx ~]$ gcc -pthread thread_test.c</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">[a@a-lnx ~]$ ./a.out</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">Error after 906 threads</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">[a@a-lnx ~]$ ulimit -u</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">1024</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">[a@a-lnx ~]$ ulimit -u 2000</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">[a@a-lnx ~]$ ./a.out</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">Error after 1883 threads</span><br />
<div>
<br /></div>
<div>
<br /></div>
<div>
So make sure it is set high enough, otherwise MySQL can generate 'Cannot create thread' errors. </div>
<div>
Since this limit applies to user (not to process), this parameter is especially important if multiple mysql instances are running (with the same user).</div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-2706809966982323409.post-34440691975171080572011-10-27T09:10:00.000-07:002011-10-27T09:10:00.638-07:00"X--tag=CC: command not found"If you ever get this error when compiling sysbench, try following commands before make:<br />
<br />
echo=echo<br />
export echo<br />
<br />
Error should gone now :)Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-2706809966982323409.post-23915922645205742352011-02-28T02:17:00.000-08:002011-02-28T02:21:05.602-08:00Vieing other's session variables or injecting admin code<b>PART 1: Vieing other's session variables</b><br />
<br />
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.<br />
<br />
Official way is that such feature is not supported, but there situations when we still able to make it.<br />
<br />
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):<br />
<br />
<br />
<span style="background-color: #eeeeee; font-family: "Courier New",Courier,monospace;">delimiter ;;</span><br />
<br />
<span style="background-color: #eeeeee; font-family: "Courier New",Courier,monospace;">create procedure export_session_vars(connid int)</span><br />
<span style="background-color: #eeeeee; font-family: "Courier New",Courier,monospace;">begin</span><br />
<span style="background-color: #eeeeee; font-family: "Courier New",Courier,monospace;"> declare exit handler for SQLEXCEPTION begin end;</span><br />
<span style="background-color: #eeeeee; font-family: "Courier New",Courier,monospace;"> if CONNECTION_ID() = connid and @hack_export_happened is null then </span><br />
<span style="background-color: #eeeeee; font-family: "Courier New",Courier,monospace;"> select * from information_schema.session_variables order by 1 into outfile '/tmp/output.txt';</span><br />
<span style="background-color: #eeeeee; font-family: "Courier New",Courier,monospace;"> set @hack_export_happened = 1;</span><br />
<span style="background-color: #eeeeee; font-family: "Courier New",Courier,monospace;"> end if;</span><br />
<span style="background-color: #eeeeee; font-family: "Courier New",Courier,monospace;">end;;</span><br />
<br />
<br />
<span style="background-color: #eeeeee; font-family: "Courier New",Courier,monospace;">create trigger hack after insert on t for each row call export_session_vars(2);</span><br />
<br />
<br />
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.<br />
<br />
<br />
<i>Note 1:</i> 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.<br />
<br />
<i>Note 2:</i> In 5.0 we don't have session_variables table in information_schema, so we should use SELECT with implicit variables values, e.g.:<br />
<span style="background-color: #cccccc; font-family: "Courier New",Courier,monospace; font-size: small;">SELECT "auto_increment_increment" as variable_name, @auto_increment_increment as variable_value<br />
UNION<br />
SELECT "auto_increment_offset" as variable_name, @auto_increment_offset as variable_value<br />
UNION</span><span style="font-size: small;"><br style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;" /><span style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">...</span></span><br />
<br />
<br />
In case if we don't know involved tables or if session does only reads - I don't know any solution.<br />
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).<br />
<br />
<br />
<br />
<b>PART 2: Injecting Admin Code.</b><br />
<br />
So considerations above lead to possible useful feature in future of MySQL. (Not sure if similar feature exists in other RDBMS).<br />
<br />
Perhaps we have table mysql.injection similar to mysql.event, but with two additional columns `CONNECTION_ID` and `RESULT`.<br />
<br />
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). <br />
<br />
<i>Note 1:</i> 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.<br />
<i>Note 2:</i> Details may differ, but I hope general idea is clear and shouldn't be difficult to implement.<br />
<br />
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).<br />
<br />
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.Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-2706809966982323409.post-6565063850097917552011-02-24T11:36:00.000-08:002011-02-28T11:52:47.757-08:00Com_flush vs Flush_commandsMySQL status variables has two variables with similar name (and description), but quite different meaning.<br />
<br />
<span style="font-weight: bold;">Flush_commands</span> - internal version of tables (incremented when tables are actually flushed, i.e. in SQL command FLUSH TABLES or in COM_REFRESH packet).<br />
<span style="font-weight: bold;">Com_flush</span> - incremented on each FLUSH command and in each COM_REFRESH packet.<br />
<br />
<span style="font-style: italic;">Difference 1:</span> at startup <span style="font-weight: bold;">Flush_commands</span> has initial value 1. So name of variable is somewhat wrong, because "1" means no commands were executed yet.<br />
<br />
<span style="font-style: italic;">Difference 2:</span> 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).<br />
So following condition is always true on GLOBAL level of MySQL:<br />
<br />
Com_flush >= (Flush_commands -1)<br />
<br />
<br />
<span style="font-style: italic;">Difference 3:</span> logical conclusion is that <span style="font-weight: bold;">Flush_commands</span> is only GLOBAL variable, while <span style="font-weight: bold;">Com_flush </span>has both SESSION and GLOBAL values.<br />
<br />
<br />
This looks quite complex, but looking into history will explain wrong name of <span style="font-style: italic;">"Flash_commands"</span> variable. Initially there was no SQL command <span style="font-weight: bold;">FLUSH</span> in MySQL and <span style="font-weight: bold;">COM_REFRESH</span> packet had no parameters - so every <span style="font-weight: bold;">COM_REFRESH</span> did increment this variable. <span style="font-weight: bold;"><br />
</span>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-2706809966982323409.post-35007870564396701122010-04-18T13:28:00.000-07:002010-04-18T13:31:20.736-07:00Building 5.5.4-m3 in Visual Studio 10 beta 2I've got new Window7 system with Visual Studio 10 beta 2, so decided to build MySQL from source.<br /><br />mysql-5.5.4-m3.tar.gz<br /><br />First problem was cmake, which reported<br />"Make Error: Could not create named generator Visual Studio 10"<br /><br />It was solved by installing nightly build of cmake from http://www.cmake.org/files/dev/<br /><br />I've managed generate MySQL.sln file, so next step is compiling.<br />It gave plenty errors like:<br /><br />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)<br /><br />I eliminated these errors by ugly fix with adding following lines right before statements which generate the error in my_global.h .<br /><br />#ifndef SIZEOF_CHARP<br />#define SIZEOF_CHARP SIZEOF_LONG<br />#endif<br /><br />Afterward I got error shared access writing to Debug\gen_lex_hash.exe , but rebuild finally showed "no errors".<br /><br />Conclusion:<br />mysql-5.5.4-m3 was finally built and started with Visual Studio 10 Beta 2 after couple manual fixes.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-2706809966982323409.post-7132173521555489702009-05-13T04:21:00.000-07:002009-05-13T04:26:42.647-07:00Number of rows in InnoDB tableSince "select count(*) from table_name" may be extremely heavy operation for huge InnoDB tables, you may execute EXPLAIN to get approximate number of rows.<br /><br />EXPLAIN select count(*) from table_name;<br /><br />This will work also for checking how many rows matching to some condition if covering index exists:<br /><br />EXPLAIN select count(*) from table_name where index_column = v;Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-2706809966982323409.post-80368601842188693092009-05-12T14:56:00.000-07:002009-05-13T04:28:35.973-07:00Building ha_innodb.dllIf anyone is looking for guidelines to build ha_innodb.dll:<br />try googling INNODB_DYNAMIC_PLUGIN keyword.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-2706809966982323409.post-15758615846016931942009-03-16T02:18:00.000-07:002009-05-13T04:41:40.384-07:00Import / Export BLOB with native mysql environmentSometimes it is needed to load binary file into BLOB column. I know there are a lot of useful tools, but they aren't always available: perhaps you work on customer site or in exotic OS.<br /><br />Basic approach is using LOAD_FILE function:<br /><br /><pre class="programlisting"><strong class="userinput"><code>UPDATE t</code></strong> <strong class="userinput"><code>SET blob_col=LOAD_FILE('/tmp/picture')</code></strong> <strong class="userinput"><code>WHERE id=1;</code></strong><br /></pre><br />Using LOAD DATA INFILE command also does this job. Usually it fails working with BLOBs because of special characters, but providing dummy string in <span style="font-weight: bold;">fields terminated by</span> and empty string for other options <span style="font-weight: bold;">enclosed by '' escaped by '' lines terminated by ''</span> does the trick. Here are example steps:<br /><br /><span style="font-weight: bold;"><span style="font-family:courier new;">create temporary table<br />test.blobtable (bcolumn blob) engine=myisam;</span><br /></span><br /><span style="font-weight: bold;"><span style="font-family:courier new;">load data infile '/tmp/image.jpg'<br />into table test.blobtable character set binary<br />fields terminated by 'this is dummy string'<br />enclosed by ''<br />escaped by ''<br />lines terminated by '';</span><br /></span><br />well, data is in database so you can run:<br /><br /><span style="font-weight: bold;"><span style="font-family:courier new;">update mytable, test.blobtable<br />set mytable.bcolumn = test.blobtable.bcolumn<br />where mytable.id = 1;</span><br /></span><br /><br />Now, let's verify data imported properly: use md5:<br /><br /><span style="font-weight: bold;"><span style="font-family:courier new;">mysql> select md5(bcolumn) from mytable where id=1;</span><br /><span style="font-family:courier new;">+----------------------------------+</span><br /><span style="font-family:courier new;">| md5(bcolumn) |</span><br /><span style="font-family:courier new;">+----------------------------------+</span><br /><span style="font-family:courier new;">| aced13be21aa488bf40cf41e14c3f662 |</span><br /><span style="font-family:courier new;">+----------------------------------+</span><br /><br /><span style="font-family:courier new;">shell> md5sum /tmp/image.jpg</span><br /><span style="font-family:courier new;">aced13be21aa488bf40cf41e14c3f662 */tmp/image.jpg</span><br /></span><br /><br />Well done!<br />If BLOB is really huge and extra-copying should be avoided, I think following steps will work:<br /><br />1. Insert data at the beginning of binary file your ID and 'dummy string which should not happen in binary file'.<br />2. use<br /><span style="font-weight: bold;font-family:courier new;" >LOAD INFILE 'file' mytable<br />REPLACE ...<br />fields terminated by 'dummy string which should not happen in binary file' ...</span><br /><br />Steps above assume the file and mysql instance are on the same host privileges set properly. Refer documentation or mysql support if have related problems.<br /><br />I tested steps above with mysql 5.0.77 and 6.0.9 and hope they will help someone. Please provide any feedback especially if the same result can be achieved easier.<br /><br />BTW if you need to export BLOB in binary format use <span style="font-family:courier new;"><span style="font-weight: bold;">SELECT ... INTO <span style="font-style: italic;">DUMPFILE</span></span> </span>Unknownnoreply@blogger.com0