- discuss - lists.mariadb.org

[Maria-discuss] Good GUI
by md＠rpzdesign.com 30 Aug '11

30 Aug '11

Hello: I have been using Navicat to administer my Oracle/Sun based MySQL servers for years. (www.navicat.com) Now I am switched over to MariaDB 2.8 using ARIA engine. What do MariaDB users recommend of good GUI front end that allows me to manage ARIA Create Table Transactional=0|1 Row_format=PAGE setting. Most Tools do not support all of the MariaDB settings in the table setup dialogs. Suggestions appreciated... md

3 2

[Maria-discuss] GUI/Workbench for MariaDB Aria?
by Daniel Bartholomew 30 Aug '11

30 Aug '11

New question on the Knowledgebase: http://kb.askmonty.org/en/guiworkbench-for-mariadb-aria Does anybody know a good GUI/Work bench tool to handle ARIA transactional=1/0 and row_format=PAGE situations? Thanks. -- Daniel Bartholomew MariaDB - http://mariadb.org Monty Program - http://montyprogram.com AskMonty Knowledgebase - http://kb.askmonty.org

3 2

[Maria-discuss] Frequently running Event overlapping itself
by Brian Evans 17 Aug '11

17 Aug '11

I have an issue where a fast triggering event, running every 30 seconds, is occasionally running multiple times without each finishing. This is causing duplicate data as things are marked done one at a time. I am trying to figure a solution such that this event can only run singly, but what is the best way? DISABLE at start and ENABLE at end? Do SESSION variables work between event invocations?

2 2

[Maria-discuss] Installation issues with PHP5
by Daniel Bartholomew 15 Aug '11

15 Aug '11

A few comments were posted yesterday to the following Knowledgebase article: https://kb.askmonty.org/en/installation-issues-with-php5 The poster is getting mysql_connect errors. He's using CentOS 5 64bit, PHP 5.2, and recently upgraded from MySQL 5.0.77 to MariaDB 5.3. If you have any experience in this area, please consider jumping in and replying to the comments. Thanks! -- Daniel Bartholomew MariaDB - http://mariadb.org Monty Program - http://montyprogram.com AskMonty Knowledgebase - http://kb.askmonty.org

1 0

[Maria-discuss] Bug reporting
by planas 06 Aug '11

06 Aug '11

How does one post a bug? install issue with MariaDB-server-5.2 and 5.3 - fatal, crash on install Ubuntu 11.04 see below for terminal reports for both sudo apt-get install mariadb-server-5.2 Reading package lists... Done Building dependency tree Reading state information... Done The following extra packages will be installed: libdbd-mysql-perl libmariadbclient16 libmysqlclient16 mariadb-client-5.2 mariadb-client-core-5.2 mariadb-server-core-5.2 mysql-common Suggested packages: mailx mariadb-test The following NEW packages will be installed: libdbd-mysql-perl libmariadbclient16 libmysqlclient16 mariadb-client-5.2 mariadb-client-core-5.2 mariadb-server-5.2 mariadb-server-core-5.2 mysql-common 0 upgraded, 8 newly installed, 0 to remove and 2 not upgraded. Need to get 21.4 MB/21.5 MB of archives. After this operation, 51.2 MB of additional disk space will be used. Do you want to continue [Y/n]? y Get:1 http://ftp.osuosl.org/pub/mariadb/repo/5.2/ubuntu/ natty/main mysql-common all 5.2.7-mariadb101~natty [10.6 kB] Get:2 http://ftp.osuosl.org/pub/mariadb/repo/5.2/ubuntu/ natty/main libmariadbclient16 amd64 5.2.7-mariadb101~natty [1,454 kB] Get:3 http://ftp.osuosl.org/pub/mariadb/repo/5.2/ubuntu/ natty/main libmysqlclient16 amd64 5.2.7-mariadb101~natty [4,300 B] Get:4 http://ftp.osuosl.org/pub/mariadb/repo/5.2/ubuntu/ natty/main mariadb-client-core-5.2 amd64 5.2.7-mariadb101~natty [93.7 kB] Get:5 http://ftp.osuosl.org/pub/mariadb/repo/5.2/ubuntu/ natty/main mariadb-client-5.2 amd64 5.2.7-mariadb101~natty [1,759 kB] Get:6 http://ftp.osuosl.org/pub/mariadb/repo/5.2/ubuntu/ natty/main mariadb-server-core-5.2 amd64 5.2.7-mariadb101~natty [4,474 kB] Get:7 http://ftp.osuosl.org/pub/mariadb/repo/5.2/ubuntu/ natty/main mariadb-server-5.2 amd64 5.2.7-mariadb101~natty [13.6 MB] Fetched 21.4 MB in 6s (3,166 kB/s) Preconfiguring packages ... Selecting previously deselected package mysql-common. (Reading database ... 458443 files and directories currently installed.) Unpacking mysql-common (from .../mysql-common_5.2.7-mariadb101~natty_all.deb) ... Selecting previously deselected package libmariadbclient16. Unpacking libmariadbclient16 (from .../libmariadbclient16_5.2.7-mariadb101~natty_amd64.deb) ... Selecting previously deselected package libmysqlclient16. Unpacking libmysqlclient16 (from .../libmysqlclient16_5.2.7-mariadb101~natty_amd64.deb) ... Selecting previously deselected package libdbd-mysql-perl. Unpacking libdbd-mysql-perl (from .../libdbd-mysql-perl_4.016-1_amd64.deb) ... Selecting previously deselected package mariadb-client-core-5.2. Unpacking mariadb-client-core-5.2 (from .../mariadb-client-core-5.2_5.2.7-mariadb101~natty_amd64.deb) ... Selecting previously deselected package mariadb-client-5.2. Unpacking mariadb-client-5.2 (from .../mariadb-client-5.2_5.2.7-mariadb101~natty_amd64.deb) ... Selecting previously deselected package mariadb-server-core-5.2. Unpacking mariadb-server-core-5.2 (from .../mariadb-server-core-5.2_5.2.7-mariadb101~natty_amd64.deb) ... Processing triggers for man-db ... Processing triggers for menu ... Setting up mysql-common (5.2.7-mariadb101~natty) ... Selecting previously deselected package mariadb-server-5.2. (Reading database ... 458622 files and directories currently installed.) Unpacking mariadb-server-5.2 (from .../mariadb-server-5.2_5.2.7-mariadb101~natty_amd64.deb) ... Processing triggers for man-db ... Processing triggers for ureadahead ... Setting up libmysqlclient16 (5.2.7-mariadb101~natty) ... Setting up libmariadbclient16 (5.2.7-mariadb101~natty) ... Setting up libdbd-mysql-perl (4.016-1) ... Setting up mariadb-client-core-5.2 (5.2.7-mariadb101~natty) ... Setting up mariadb-client-5.2 (5.2.7-mariadb101~natty) ... Setting up mariadb-server-core-5.2 (5.2.7-mariadb101~natty) ... Setting up mariadb-server-5.2 (5.2.7-mariadb101~natty) ... ' * Stopping MariaDB database server mysqld [ OK ] 110805 19:21:20 [Note] Plugin 'PBXT' is disabled. 110805 19:21:20 [Note] Plugin 'InnoDB' is disabled. * Starting MariaDB database server mysqld [fail] invoke-rc.d: initscript mysql, action "start" failed. dpkg: error processing mariadb-server-5.2 (--configure): subprocess installed post-installation script returned error exit status 1 Processing triggers for libc-bin ... ldconfig deferred processing now taking place Processing triggers for menu ... Errors were encountered while processing: mariadb-server-5.2 E: Sub-process /usr/bin/dpkg returned an error code (1) sudo apt-get install mariadb-server-5.3 Reading package lists... Done Building dependency tree Reading state information... Done The following extra packages will be installed: libdbd-mysql-perl libmariadbclient16 libmysqlclient16 mariadb-client-5.3 mariadb-client-core-5.3 mariadb-server-core-5.3 mysql-common Suggested packages: mailx mariadb-test The following NEW packages will be installed: libdbd-mysql-perl libmariadbclient16 libmysqlclient16 mariadb-client-5.3 mariadb-client-core-5.3 mariadb-server-5.3 mariadb-server-core-5.3 mysql-common 0 upgraded, 8 newly installed, 0 to remove and 2 not upgraded. Need to get 22.8 MB of archives. After this operation, 56.5 MB of additional disk space will be used. Do you want to continue [Y/n]? y Get:1 http://ftp.osuosl.org/pub/mariadb/repo/5.3/ubuntu/ natty/main mysql-common all 5.3.0-mariadb101~natty [10.7 kB] Get:2 http://ftp.osuosl.org/pub/mariadb/repo/5.3/ubuntu/ natty/main libmariadbclient16 amd64 5.3.0-mariadb101~natty [1,517 kB] Get:3 http://us.archive.ubuntu.com/ubuntu/ natty/main libdbd-mysql-perl amd64 4.016-1 [140 kB] Get:4 http://ftp.osuosl.org/pub/mariadb/repo/5.3/ubuntu/ natty/main libmysqlclient16 amd64 5.3.0-mariadb101~natty [4,356 B] Get:5 http://ftp.osuosl.org/pub/mariadb/repo/5.3/ubuntu/ natty/main mariadb-client-core-5.3 amd64 5.3.0-mariadb101~natty [94.1 kB] Get:6 http://ftp.osuosl.org/pub/mariadb/repo/5.3/ubuntu/ natty/main mariadb-client-5.3 amd64 5.3.0-mariadb101~natty [1,760 kB] Get:7 http://ftp.osuosl.org/pub/mariadb/repo/5.3/ubuntu/ natty/main mariadb-server-core-5.3 amd64 5.3.0-mariadb101~natty [4,719 kB] Get:8 http://ftp.osuosl.org/pub/mariadb/repo/5.3/ubuntu/ natty/main mariadb-server-5.3 amd64 5.3.0-mariadb101~natty [14.5 MB] Fetched 22.8 MB in 6s (3,556 kB/s) Preconfiguring packages ... Selecting previously deselected package mysql-common. (Reading database ... 458566 files and directories currently installed.) Unpacking mysql-common (from .../mysql-common_5.3.0-mariadb101~natty_all.deb) ... Selecting previously deselected package libmariadbclient16. Unpacking libmariadbclient16 (from .../libmariadbclient16_5.3.0-mariadb101~natty_amd64.deb) ... Selecting previously deselected package libmysqlclient16. Unpacking libmysqlclient16 (from .../libmysqlclient16_5.3.0-mariadb101~natty_amd64.deb) ... Selecting previously deselected package libdbd-mysql-perl. Unpacking libdbd-mysql-perl (from .../libdbd-mysql-perl_4.016-1_amd64.deb) ... Selecting previously deselected package mariadb-client-core-5.3. Unpacking mariadb-client-core-5.3 (from .../mariadb-client-core-5.3_5.3.0-mariadb101~natty_amd64.deb) ... Selecting previously deselected package mariadb-client-5.3. Unpacking mariadb-client-5.3 (from .../mariadb-client-5.3_5.3.0-mariadb101~natty_amd64.deb) ... Selecting previously deselected package mariadb-server-core-5.3. Unpacking mariadb-server-core-5.3 (from .../mariadb-server-core-5.3_5.3.0-mariadb101~natty_amd64.deb) ... Processing triggers for man-db ... Processing triggers for menu ... Setting up mysql-common (5.3.0-mariadb101~natty) ... Selecting previously deselected package mariadb-server-5.3. (Reading database ... 458747 files and directories currently installed.) Unpacking mariadb-server-5.3 (from .../mariadb-server-5.3_5.3.0-mariadb101~natty_amd64.deb) ... Processing triggers for man-db ... Processing triggers for ureadahead ... Setting up libmysqlclient16 (5.3.0-mariadb101~natty) ... Setting up libmariadbclient16 (5.3.0-mariadb101~natty) ... Setting up libdbd-mysql-perl (4.016-1) ... Setting up mariadb-client-core-5.3 (5.3.0-mariadb101~natty) ... Setting up mariadb-client-5.3 (5.3.0-mariadb101~natty) ... Setting up mariadb-server-core-5.3 (5.3.0-mariadb101~natty) ... Setting up mariadb-server-5.3 (5.3.0-mariadb101~natty) ... * Stopping MariaDB database server mysqld [ OK ] 110805 19:01:37 [Note] Plugin 'PBXT' is disabled. 110805 19:01:37 [Note] Plugin 'InnoDB' is disabled. * Starting MariaDB database server mysqld [fail] invoke-rc.d: initscript mysql, action "start" failed. dpkg: error processing mariadb-server-5.3 (--configure): subprocess installed post-installation script returned error exit status 1 Processing triggers for libc-bin ... ldconfig deferred processing now taking place Processing triggers for menu ... Errors were encountered while processing: mariadb-server-5.3 E: Sub-process /usr/bin/dpkg returned an error code (1) I have spent days trying to fully resolve this. One should be able uninstall and reinstall a package as needed without tracking down bugs in the installer or uninstaller scripts. Logs have been blank or just repeat the above information. -- Jay Lozier jslozier(a)gmail.com

2 1

[Maria-discuss] Cannot install Mariadb-server-5.2
by planas 04 Aug '11

04 Aug '11

Hi I have been struggling to get mariadb to work. My problems have been either the server is very erratic, usually will not start it. After trying a complete reinstall I can not install the mariadb-server-5.2 package either from the repository or from a deb. I am using Ubuntu/Pinguy 11.04. The installation results from apt-get or aptitude are sudo aptitude install mariadb-server-5.2 The following NEW packages will be installed: mariadb-server-5.2 0 packages upgraded, 1 newly installed, 0 to remove and 21 not upgraded. Need to get 13.6 MB of archives. After unpacking 30.8 MB will be used. Get:1 http://ftp.osuosl.org/pub/mariadb/repo/5.2/ubuntu/ natty/main mariadb-server-5.2 amd64 5.2.7-mariadb101~natty [13.6 MB] Fetched 13.6 MB in 4s (2,733 kB/s) Preconfiguring packages ... (Reading database ... 427308 files and directories currently installed.) Unpacking mariadb-server-5.2 (from .../mariadb-server-5.2_5.2.7-mariadb101~natty_amd64.deb) ... Aborting downgrade from (at least) 5.3 to 5.2. If are sure you want to downgrade to 5.2, remove the file /var/lib/mysql/debian-*.flag and try installing again. dpkg: error processing /var/cache/apt/archives/mariadb-server-5.2_5.2.7-mariadb101~natty_amd64.deb (--unpack): subprocess new pre-installation script returned error exit status 1 Errors were encountered while processing: /var/cache/apt/archives/mariadb-server-5.2_5.2.7-mariadb101~natty_amd64.deb E: Sub-process /usr/bin/dpkg returned an error code (1) A package failed to install. Trying to recover: I tried MySQL 5.1.7 from the Ubuntu repository and got the same results. Searching various forums/online information has not turned up an answer including MySQL and Ubuntu. -- Jay Lozier jslozier(a)gmail.com

2 1

[Maria-discuss] A couple of new questions in the Knowledgebase
by Daniel Bartholomew 02 Aug '11

02 Aug '11

All, While I was out on vacation it appears a couple of new questions were posted to the Knowledgebase. If you have some time, please take a look. Thanks! Missing mysqld.sock file http://kb.askmonty.org/en/missing-mysqldsock-file and myisam_use_mmap does not use mmap after a massive insert http://kb.askmonty.org/en/myisam_use_mmap-does-not-use-mmap-after-a-massive… -- Daniel Bartholomew MariaDB - http://mariadb.org Monty Program - http://montyprogram.com AskMonty Knowledgebase - http://kb.askmonty.org

2 1

[Maria-discuss] MariaDB 5.3 on lenny crashing about every 8 minutes
by Carsten Bernhard 02 Aug '11

02 Aug '11

Hi, I have installed MariaDB 5.3 from the apt packages on a lenny amd64 machine. Initial setup went fine and the database is working correctly. When I give some load to the machine I get crashes about every 8 minutes. This is what the log shows: Jul 29 00:36:17 svn mysqld: 110729 0:36:17 [ERROR] mysqld got signal 11 ; Jul 29 00:36:17 svn mysqld: This could be because you hit a bug. It is also poss ible that this binary Jul 29 00:36:17 svn mysqld: or one of the libraries it was linked against is cor rupt, improperly built,Jul 29 00:36:17 svn mysqld: or misconfigured. This error can also be caused by m alfunctioning hardware. Jul 29 00:36:17 svn mysqld: We will try our best to scrape up some info that will hopefully help diagnose Jul 29 00:36:17 svn mysqld: the problem, but since we have already crashed, some thing is definitely wrongJul 29 00:36:17 svn mysqld: and this may fail. Jul 29 00:36:17 svn mysqld: Jul 29 00:36:17 svn mysqld: key_buffer_size=0 Jul 29 00:36:17 svn mysqld: read_buffer_size=2097152 Jul 29 00:36:17 svn mysqld: max_used_connections=13 Jul 29 00:36:17 svn mysqld: max_threads=102 Jul 29 00:36:17 svn mysqld: threads_connected=1 Jul 29 00:36:17 svn mysqld: It is possible that mysqld could use up toJul 29 00:36:17 svn mysqld: key_buffer_size + (read_buffer_size + sort_buffer_si ze)*max_threads = 627994 K Jul 29 00:36:17 svn mysqld: bytes of memoryJul 29 00:36:17 svn mysqld: Hope that's ok; if not, decrease some variables in t he equation. Jul 29 00:36:17 svn mysqld: Jul 29 00:36:17 svn mysqld: Thread pointer: 0x7f1740013780Jul 29 00:36:17 svn mysqld: Attempting backtrace. You can use the following info rmation to find outJul 29 00:36:17 svn mysqld: where mysqld died. If you see no messages after this , something went Jul 29 00:36:17 svn mysqld: terribly wrong... Jul 29 00:36:17 svn mysqld: stack_bottom = 0x41614110 thread_stack 0x48000Jul 29 00:36:17 svn mysqld: /usr/sbin/mysqld(my_print_stacktrace+0x2e) [0x7f176c 0dc1de]Jul 29 00:36:17 svn mysqld: /usr/sbin/mysqld(handle_segfault+0x448) [0x7f176bce3 de8] Jul 29 00:36:17 svn mysqld: /lib/libpthread.so.0 [0x7f176a281a80]Jul 29 00:36:17 svn mysqld: /usr/sbin/mysqld(JOIN::optimize()+0x22) [0x7f176bd68 c52] Jul 29 00:36:17 svn mysqld: /usr/sbin/mysqld(mysql_derived_optimize(THD*, st_lex*, TABLE_LIST*)+0x12e) [0x7f176be52fbe] Jul 29 00:36:17 svn mysqld: /usr/sbin/mysqld(mysql_handle_single_derived(st_lex*, TABLE_LIST*, unsigned int)+0x10e) [0x7f176be53c3e] Jul 29 00:36:17 svn mysqld: /usr/sbin/mysqld(st_select_lex::handle_derived(st_lex*, unsigned int)+0x48) [0x7f176bc07528] Jul 29 00:36:17 svn mysqld: /usr/sbin/mysqld(JOIN::optimize()+0xb2) [0x7f176bd68ce2] Jul 29 00:36:17 svn mysqld: /usr/sbin/mysqld(mysql_derived_optimize(THD*, st_lex*, TABLE_LIST*)+0x12e) [0x7f176be52fbe] Jul 29 00:36:17 svn mysqld: /usr/sbin/mysqld(mysql_handle_single_derived(st_lex*, TABLE_LIST*, unsigned int)+0x10e) [0x7f176be53c3e] Jul 29 00:36:17 svn mysqld: /usr/sbin/mysqld(st_select_lex::handle_derived(st_lex*, unsigned int)+0x48) [0x7f176bc07528] Jul 29 00:36:17 svn mysqld: /usr/sbin/mysqld(JOIN::optimize()+0xb2) [0x7f176bd68ce2] Jul 29 00:36:17 svn mysqld: /usr/sbin/mysqld(mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*)+0x104) [0x7f176bd70a84] Jul 29 00:36:17 svn mysqld: /usr/sbin/mysqld(handle_select(THD*, st_lex*, select_result*, unsigned long)+0x171) [0x7f176bd714c1] Jul 29 00:36:17 svn mysqld: /usr/sbin/mysqld [0x7f176bcef8fb] Jul 29 00:36:17 svn mysqld: /usr/sbin/mysqld(mysql_execute_command(THD*)+0x1ef9) [0x7f176bcf2239] Jul 29 00:36:17 svn mysqld: /usr/sbin/mysqld(mysql_parse(THD*, char*, unsigned int, char const**)+0x2c1) [0x7f176bcf6181] Jul 29 00:36:17 svn mysqld: /usr/sbin/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0xde1) [0x7f176bcf8941] Jul 29 00:36:17 svn mysqld: /usr/sbin/mysqld(do_command(THD*)+0x100) [0x7f176bcf9230] Jul 29 00:36:17 svn mysqld: /usr/sbin/mysqld(handle_one_connection+0xe4) [0x7f176bceacf4] Jul 29 00:36:17 svn mysqld: /lib/libpthread.so.0 [0x7f176a279fc7] Jul 29 00:36:17 svn mysqld: /lib/libc.so.6(clone+0x6d) [0x7f176985764d] Jul 29 00:36:17 svn mysqld: Jul 29 00:36:17 svn mysqld: Trying to get some variables. Jul 29 00:36:17 svn mysqld: Some pointers may be invalid and cause the dump to abort. Jul 29 00:36:17 svn mysqld: Query (0x7f176ec35988): SELECT * FROM ( Jul 29 00:36:17 svn mysqld: SELECT @rn := if( @lc = l.l_contentid, @rn +1, 0 ) AS rnum, @lc := l_contentid, l.* Jul 29 00:36:17 svn mysqld: FROM vb3_vbseo_likes l, (SELECT @rn :=0, @lc :=0) r Jul 29 00:36:17 svn mysqld: WHERE l_ctype = 1 AND l_cgroup =39820 AND l_contentid IN (289195,289196 ) Jul 29 00:36:17 svn mysqld: ORDER BY l_contentid Jul 29 00:36:17 svn mysqld: ) as nest Jul 29 00:36:17 svn mysqld: where rnum<3 Jul 29 00:36:17 svn mysqld: Connection ID (thread ID): 511 Jul 29 00:36:17 svn mysqld: Status: NOT_KILLED Jul 29 00:36:17 svn mysqld: Jul 29 00:36:17 svn mysqld: The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains Jul 29 00:36:17 svn mysqld: information that should help you find out what is causing the crash. Jul 29 00:36:17 svn mysqld_safe: Number of processes running now: 0 Jul 29 00:36:17 svn mysqld_safe: mysqld restarted Just to confirm the installed packages: dpkg --get-selections | grep maria libmariadbclient16 install mariadb-client-5.3 install mariadb-client-core-5.3 install mariadb-server-5.3 install mariadb-server-core-5.3 install Any pointers? Cheers, Carsten

3 2

[Maria-discuss] MariaDB on Windows and Aria Temp Tables
by Brian Evans 01 Aug '11

01 Aug '11

I recently installed MariaDB 5.2.7 Win-x64 to a machine from http://downloads.askmonty.org/mariadb/. When I did 'SELECT @@aria_used_for_temp_tables;', it returned 0. I know this a compile time option and was wondering if this was intended. Several references I've found point that it is meant to be enabled by default. Thanks

2 1

[Maria-discuss] Help with RPM src build
by bwellsnc 22 Jul '11

22 Jul '11

I have gone over every document concerning building mariadb using src rpm. I am trying to build mariadb against RHEL 6. I have run rpmbuild against the src rpm and I get this error everytime: error: configure: error: C compiler cannot create executables The instructions, I have to admit, are not very good about building using the source rpm. If there is anyone who has a better howto, I would really appreciate it. Thanks! Brent

3 11

[Maria-discuss] mariadb runs at lowest priority (nice -n 19)
by Panayot Belchev 13 Jun '11

13 Jun '11

Hi, I just noticed that all our MariaDB 5.2 servers show the same: $ ps -eo ni,comm |grep mysql 9 mysqld_safe 19 mysql_isam_spac <defunct> 19 mysqld While servers with Oracle MySQL are at nice -n 0 I tried setting niceness=-10 in /usr/bin/mysqld_safe but with no effect. Is there a reason to run MariaDB at the lowest possible priority and is there a way to change that?

4 7

[Maria-discuss] XtraDB using the Windows Installer
by Jake Drew 09 Jun '11

09 Jun '11

I have a couple of questions regarding XtraDB. 1. I am curious, if XtraDB is turned on / installed by default when MariaDB is installed using the windows installer on a Windows 2008 R2 server? 2. Is this default behavior the same in 5.1.53 and 5.2.6? 3. How can I tell if XtraDB is currently installed on any given MariaDB install? i.e. Is there a SHOW or other similar command I can run to confirm that XtraDB is installed?

5 4

[Maria-discuss] Calculating Maximum Bytes Per Record
by Jake Drew 07 Jun '11

07 Jun '11

*Hello, * * * *I have recently been doing some capacity planning exercises for a new database we are developing in MariaDB. One of the exercises I completed was to create a database with all tables loaded with maximum width records at 100,000, 500,000, and 1,000,000 records. I noticed that when I did this it seems that the larger the tables grow, the less bytes per record are required. I am curious what causes this to happen? These are all InnoDB tables saved to their own tablespaces.* * Max Bytes Per Record Record Count Record Count Record Count 100,000 500,000 1,000,000 acct_activation 241 176 164 acct_deactivated 419 336 327 disk_usage 136 75 63 keywords 713 646 633 pods 77,511 56,698 53,126 pods_joined 178 101 96 pod_activity 461 369 357 pod_files 78,224 57,220 53,615 sessions 168 92 80 users 1,426 1,351 1,338 usr_profile 78,392 57,343 53,730 * *I also created a query (using Information_Schema.Columns) to calculate maximum possible bytes per record / table (SQL attached below). I ran the query and compared the estimates to the actual values produced above. The query is close, but I would like to make it a little more accurate if possible. * *TABLE_SCHEMA* *TABLE_NAME* *PK_BYTES_TOT* *FIELD_BYTES_TOT* * IX_FIELD_BYTES_TOT* *IX_FIELD_COUNT* *IX_OVERHEAD_BYTES* *IX_BYTES_TOT* * TABLE_BYTES_TOT* pods acct_activation 0 73 33 1 29 62 *189* pods acct_deactivated 8 269 0 0 0 0 *388* pods devices 1 273 0 0 0 0 *384* pods disk_usage 8 32 0 0 0 0 *56* pods keywords 0 264 264 2 58 322 *820* pods pods 8 65,821 16 1 29 45 *92,224* pods pods_joined 0 16 16 2 58 74 *126* pods pod_activity 0 275 9 1 29 38 *438* pods pod_files 8 66,376 56 2 58 114 *93,097* pods sessions 0 10 10 2 58 68 *109* pods users 8 1,112 41 1 29 70 *1,666* pods usr_profile 8 66,628 0 0 0 0 *93,290* *Does anyone have any suggestions for my calculations in the code below to make it more accurate?* * * *Basic Assumptions * * * - Calculated field widths are defined by data type in the CASE logic below. - Primary key bytes = The sum of all defined PK calculated field widths - Index Bytes = Primary key bytes + Index calculated field width + 29 bytes overhead (for each index defined) - 1 byte is added to each field that is Nullable. - All bytes defined above are multiplied by 1.4 for BTREE overhead.* * * * *Current SQL to calculate MAX record bytes by table:* * * SELECT B.TABLE_SCHEMA , B.TABLE_NAME , SUM(PK_BYTES) AS PK_BYTES_TOT , SUM(FIELD_BYTE_SPACE) AS FIELD_BYTES_TOT , SUM(IX_BYTES) AS IX_FIELD_BYTES_TOT , SUM(CASE WHEN IX_BYTES > 0 THEN 1 ELSE 0 END) AS IX_FIELD_COUNT , SUM(CASE WHEN IX_BYTES > 0 THEN 1 ELSE 0 END) * 29 AS IX_OVERHEAD_BYTES , (SUM(CASE WHEN IX_BYTES > 0 THEN 1 ELSE 0 END) * 29) + SUM(IX_BYTES) AS IX_BYTES_TOT , (SUM(PK_BYTES) + SUM(FIELD_BYTE_SPACE) + (SUM(CASE WHEN IX_BYTES > 0 THEN 1 ELSE 0 END) * 29) + SUM(IX_BYTES)) * 1.4 AS TABLE_BYTES_TOT FROM ( SELECT A.* , CASE WHEN COLUMN_KEY = 'PRI'THEN FIELD_BYTE_SPACE ELSE 0 END AS PK_BYTES , CASE WHEN A.COLUMN_KEY <> 'PRI' AND A.COLUMN_KEY <> '' THEN (PK_BYTE_SPACE + FIELD_BYTE_SPACE) ELSE 0 END AS IX_BYTES FROM ( SELECT PK_SP.TABLE_SCHEMA , PK_SP.TABLE_NAME , PK_SP.COLUMN_NAME , DATA_TYPE , CHARACTER_MAXIMUM_LENGTH , NUMERIC_PRECISION , IS_NULLABLE , COLUMN_KEY , CHARACTER_SET_NAME , CHARACTER_OCTET_LENGTH , (CASE -- CHARACTER FIELDS WHEN DATA_TYPE = 'varchar' THEN CHARACTER_MAXIMUM_LENGTH + 1 WHEN DATA_TYPE = 'char' THEN CHARACTER_MAXIMUM_LENGTH WHEN DATA_TYPE = 'tinyblob' OR DATA_TYPE = 'tinytext' THEN CHARACTER_MAXIMUM_LENGTH + 1 WHEN DATA_TYPE = 'blob' OR DATA_TYPE = 'text' THEN CHARACTER_MAXIMUM_LENGTH + 2 WHEN DATA_TYPE = 'mediumblob' OR DATA_TYPE = 'mediumtext' THEN CHARACTER_MAXIMUM_LENGTH + 3 WHEN DATA_TYPE = 'largeblob' OR DATA_TYPE = 'largetext' THEN CHARACTER_MAXIMUM_LENGTH + 4 -- NUMERIC FIELDS WHEN DATA_TYPE = 'tinyint' THEN 1 WHEN DATA_TYPE = 'smallint' THEN 2 WHEN DATA_TYPE = 'mediumint' THEN 3 WHEN DATA_TYPE = 'int' OR DATA_TYPE = 'integer' THEN 4 WHEN DATA_TYPE = 'bigint' THEN 8 WHEN DATA_TYPE = 'float' AND (NUMERIC_PRECISION <= 24 OR NUMERIC_PRECISION IS NULL) THEN 4 WHEN DATA_TYPE = 'float' AND NUMERIC_PRECISION > 24 THEN 8 WHEN DATA_TYPE = 'bit' THEN (NUMERIC_PRECISION + 7) / 8 WHEN DATA_TYPE = 'double' OR DATA_TYPE = 'numeric' THEN (FLOOR(NUMERIC_PRECISION/9)*4) + ROUND((NUMERIC_PRECISION- FLOOR(NUMERIC_PRECISION/9)*9)*.5,0) -- DATETIME FIELDS WHEN DATA_TYPE = 'date' OR DATA_TYPE = 'time' THEN 3 WHEN DATA_TYPE = 'datetime' THEN 8 WHEN DATA_TYPE = 'timestamp' THEN 4 WHEN DATA_TYPE = 'year' THEN 1 ELSE 999999999999999 END) + (CASE WHEN IS_NULLABLE = 'YES' THEN 1 ELSE 0 END) AS FIELD_BYTE_SPACE , CASE WHEN PK_BYTE_SPACE IS NULL THEN 0 ELSE PK_BYTE_SPACE END AS PK_BYTE_SPACE FROM information_schema.columns AS PK_SP LEFT OUTER JOIN (SELECT TABLE_SCHEMA , TABLE_NAME , SUM((CASE -- CHARACTER FIELDS WHEN DATA_TYPE = 'varchar' THEN CHARACTER_MAXIMUM_LENGTH + 1 WHEN DATA_TYPE = 'char' THEN CHARACTER_MAXIMUM_LENGTH WHEN DATA_TYPE = 'tinyblob' OR DATA_TYPE = 'tinytext' THEN CHARACTER_MAXIMUM_LENGTH + 1 WHEN DATA_TYPE = 'blob' OR DATA_TYPE = 'text' THEN CHARACTER_MAXIMUM_LENGTH + 2 WHEN DATA_TYPE = 'mediumblob' OR DATA_TYPE = 'mediumtext' THEN CHARACTER_MAXIMUM_LENGTH + 3 WHEN DATA_TYPE = 'largeblob' OR DATA_TYPE = 'largetext' THEN CHARACTER_MAXIMUM_LENGTH + 4 -- NUMERIC FIELDS WHEN DATA_TYPE = 'tinyint' THEN 1 WHEN DATA_TYPE = 'smallint' THEN 2 WHEN DATA_TYPE = 'mediumint' THEN 3 WHEN DATA_TYPE = 'int' OR DATA_TYPE = 'integer' THEN 4 WHEN DATA_TYPE = 'bigint' THEN 8 WHEN DATA_TYPE = 'float' AND (NUMERIC_PRECISION <= 24 OR NUMERIC_PRECISION IS NULL) THEN 4 WHEN DATA_TYPE = 'float' AND NUMERIC_PRECISION > 24 THEN 8 WHEN DATA_TYPE = 'bit' THEN (NUMERIC_PRECISION + 7) / 8 WHEN DATA_TYPE = 'double' OR DATA_TYPE = 'numeric' THEN (FLOOR(NUMERIC_PRECISION/9)*4) + ROUND((NUMERIC_PRECISION- FLOOR(NUMERIC_PRECISION/9)*9)*.5,0) -- DATETIME FIELDS WHEN DATA_TYPE = 'date' OR DATA_TYPE = 'time' THEN 3 WHEN DATA_TYPE = 'datetime' THEN 8 WHEN DATA_TYPE = 'timestamp' THEN 4 WHEN DATA_TYPE = 'year' THEN 1 ELSE 999999999999999 END) + (CASE WHEN IS_NULLABLE = 'YES' THEN 1 ELSE 0 END)) AS PK_BYTE_SPACE FROM information_schema.columns COL_SP WHERE COLUMN_KEY = 'PRI' AND TABLE_SCHEMA = 'PODS' GROUP BY TABLE_SCHEMA , TABLE_NAME) AS IX_SP ON PK_SP.TABLE_SCHEMA = IX_SP.TABLE_SCHEMA AND PK_SP.TABLE_NAME = IX_SP.TABLE_NAME WHERE PK_SP.TABLE_SCHEMA = 'PODS') AS A ) AS B GROUP BY B.TABLE_SCHEMA , B.TABLE_NAME * *

1 0

[Maria-discuss] Reqeust for SHOW FULL FIELDS FROM and virtual tables in MariaDB
by Peter Laursen 06 Jun '11

06 Jun '11

Refer http://kb.askmonty.org/v/virtual-columns - the example CREATE TABLE table1 ( a INT NOT NULL, b VARCHAR(32), c INT AS (a MOD 10) virtual, d VARCHAR(5) AS (LEFT(b,5)) persistent); now SHOW FULL FIELDS FROM table1; Field Type Collation Null Key Default Extra Privileges Comment ------ ----------- ----------------- ------ ------ ------- ------- ------------------------------- ------- a int(11) (NULL) NO (NULL) select,insert,update,references b varchar(32) latin1_swedish_ci YES (NULL) select,insert,update,references c int(11) (NULL) YES (NULL) VIRTUAL select,insert,update,references d varchar(5) latin1_swedish_ci YES (NULL) VIRTUAL select,insert,update,references I request that 'extra' column should return either "PERSISTENT" or " VIRTUAL, PERSISTENT" for column `d`. The reason is that the output from SHOW FULL FIELDS returns an *array* (that will not need to be parsed) as opposite to SHOW CREATE TABLE. Accordingly SHOW FULL FIELDS is much easier to use for an 'unintelligent client' as opposed to an 'intelligent (or ignorant for that sake :-) ) human user to decide on special properties for a column. Peter Webyog

3 4

[Maria-discuss] New Knowledgebase Question: MariaDB xtradb installation problem
by Daniel Bartholomew 03 Jun '11

03 Jun '11

Hello everyone, there's a new question in the Knowledgebase: http://kb.askmonty.org/v/mariadb-xtradb-installation-problem Thanks. -- Daniel Bartholomew MariaDB - http://mariadb.org Monty Program - http://montyprogram.com AskMonty Knowledgebase - http://kb.askmonty.org

1 0

[Maria-discuss] Support for MariaDB Configuration Options in MySQL Workbench
by Jake Drew 26 May '11

26 May '11

It looks like the MySQL Workbench team has decided to place support for MariaDB configuration file options in the next release of MySQL Workbench! (Configuration file option tabs currently display blank for any MariaDB versions greater than 5.1 in MySQL Workbench) http://bugs.mysql.com/bug.php?id=61219

1 0

[Maria-discuss] UserStats = 1
by Jake Drew 18 May '11

18 May '11

I am interested in turning on user statistics as described here: http://kb.askmonty.org/v/user-statistics. However, when I add the line userstats = 1 to the my.ini file under the [mysqld] section, the server will not restart. I am running MariaDB 5.1.53 on Windows Server 2008 R2. Is there anything else I need to install before I am able to use this option?

3 3

[Maria-discuss] 5.2.6 .msi isntaller questions issues
by Peter Laursen 14 May '11

14 May '11

I think this is for @wlad! :-) I just installed 5.2.6 using the new .msi installer. This was an upgrade 'on top' of the previous (5.2.3 or 5.2.4) .msi build distributed by wlad in mailing list. The installer worked fine, The previos 5.2 instance was upgraded (ie. executables and similar files replaced, old my.ini and datadir is still used etc.). So far so good. The problem is with the configuration wizard. What is it supposed to do? Run 'mysql_upgrade'? More than this or something else? Let me explain my setup. Except for this MariaDB instance (running as service 'maria52') I have both MySQL/Oracle 5.0, 5.1, 5.5 and 5.6 (running as services 'MySQL50, 'MySQL51' , 'MySQL55' , 'MySQL56') . The config wizard prompts me which instance should be upgraded an gives me a choice of 'MySQL50 and 'MySQL51' but not 'MySQL55', 'MySQL56' and 'maria52'. 1) Now if the wizard starts 'mysql_upgrade' it makes perfect sense not to offer to upgrade 'MySQL55' and 'MySQL56' (as MySQL51/MariaDb52 are 'lower' than those - it would be a 'downgrade' - not an 'upgrade' then - what 'mysql_upgrade' does not handle). But ideally I think they should be displayed but 'greyed out' or otherwise indicated that upgrading MySQL 5.5/5.6 --> MariaDB 5.2 is not possible. 2) More important: the instance that I *really* wanted to upgrade ('maria52') was not displayed. I assume that the services' list is filtered by the prefix 'mysql'. Am I right? In that case I suggest the filtering is done on both (OR'ed) prefixes 'mysql' and 'maria'. Anyway after just exiting the wizard before executing it everything seems fine. But the wizard GUI can still be 'polished' a little I think. -- Peter Laursen -- Webyog

2 1

[Maria-discuss] New Knowledgebase Question
by Daniel Bartholomew 11 May '11

11 May '11

Here's a new question in the Knowlegebase about running MariaDB and MySQL side-by-side on the same server. http://kb.askmonty.org/v/mariadb-coexist-with-mysql The text of the question: ---------------------------------------------------------------- Hello, I'm currently using MySQL and would like to test MariaDB. I only have one server, and don't wish to overwrite the current MySQL instance. Is it possible to have both MySQL and MariaDB to coexist within the same environment? How can I define the mysql client on which db to connect to? by port? Many thanks! ---------------------------------------------------------------- Anyone with real-world experience in running (or trying to run) MariaDB side-by-side with MySQL want to add a comment to (or answer) this question? I thought I remembered seeing an email thread on this topic a while back but my search-fu is weak today and I can't find it (well, either that or I'm imagining things). Thanks. -- Daniel Bartholomew MariaDB - http://mariadb.org Monty Program - http://montyprogram.com AskMonty Knowledgebase - http://kb.askmonty.org

2 1

[Maria-discuss] How to fix the fact that MariaDB runs as 'mysql', instead of 'mysqld' now.
by Bradley D. Thornton 07 May '11

07 May '11

-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 I've run into a problem with implementations of MariaDB where I get messages like "mysqld: unrecognized service". This threw me for a loop at first because everything seems to be fine on the surface, drop in replacement as usual, etc. The problem, seems to be related to something I hadn't noticed before, which is that MariaDB runs as 'mysql', instead of 'mysqld'. Is there a tutorial or howto related to this issue? I would imagine that it is a common occurrence (not a bug, obviously, yet still an issue that might need to be addressed on a regular basis when performing installations). Although I usually use Slackware, on the particular CentOS 5.6 box that is giving me the problem, the executibles/scripts that I have are located: /usr/bin/mysqld_multi /usr/bin/mysqld_safe /usr/bin/mysqldump /usr/bin/mysqldumpslow /usr/sbin/mysqld To be specific, I installed PiaF via a script onto a running host under VMware ESXI, that I prepared from scratch, had some libraries, yet no real install of MySQL. That made the installation of MariaDB even easier than usual, until I had prepped the box and ran the installation script, which at one point pulls binaries from the pbxinaflash.com site as a bundle. I've been sifting through the code, to see if I can correct this, yet figure that I'll have endless headaches when, for example, I perform updates to the PBX system. Therefore, I'm thinking that symlinking or something of that nature is the most effective solution, as part of my standard EllisonCO eradication philosophy, to avoid such issues in the future. Any assistance in this regard would be greatly appreciated, and go a long way towards me completing a complete removal of all MySQL Servers under my purview. Kindest regards, - -- Bradley D. Thornton Manager Network Services NorthTech Computer TEL: +1.760.666.2703 (US) TEL: +44.203.318.2755 (UK) http://NorthTech.US -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Find this cert at x-hkp://pool.sks-keyservers.net iQEcBAEBAwAGBQJNxNIRAAoJEE1wgkIhr9j3S1IH/iZHW2yYwI6K0S5nOwIMVj+C OVqNG5W5+wdzN6XAIBWhoh6PO7o9YDxoow59qdaHN4yw9fmGRFVadSg9PHcQZd7g yRVyTnuPALFWJ5YSOurC2rhyYKUQfV+oqEV6AXfznF7YOZSr47fJG1Mrw7IBO2dl GGjxmq7P/e1SsBAcOe7c6Okj5MxKlAk5S/gzRqeIcLthtRaKoNBIQhDaG36tnUNn rXWvhe1nU8vz7E9ZbFOvWE9Qe40j/P6nNtmcTeUTlP+0wc3Jue0h6+Nk7sWpZe4a 15KZx4AZHT417m+OpEpsn4CdNCkNVcdH606ijo60A/FVyyeTSZb2d5tI2bM13wM= =ng1q -----END PGP SIGNATURE-----

2 2

[Maria-discuss] Hibernate & MariaDB
by Daniel Bartholomew 21 Apr '11

21 Apr '11

New question in the Knowledgebase: http://kb.askmonty.org/v/hibernate-mariadb ---------------------------------------------------------------------- Hello, what settings do I need in a Hibernate (/Spring) configuration file (XML) for MariaDB? The same as in MySQL? Which URL, JDBC driver and Hibernate dialect I have to use? Thanks in advance! Greetings: Anke ---------------------------------------------------------------------- Anyone with any experience with Hibernate and Spring want to jump in and comment on (or answer) this question? If not I will just answer with the standard "If it works with MySQL it will work with MariaDB...." Thanks. -- Daniel Bartholomew MariaDB - http://mariadb.org Monty Program - http://montyprogram.com AskMonty Knowledgebase - http://kb.askmonty.org

2 1

[Maria-discuss] dynamic columns interface
by MARK CALLAGHAN 19 Apr '11

19 Apr '11

Will dynamic columns always require the position (column_nr argument)? I prefer an interface in which that is not required. -- Mark Callaghan mdcallag(a)gmail.com

3 3

[Maria-discuss] New comment in the Knowledgebase
by Daniel Bartholomew 13 Apr '11

13 Apr '11

All, A new comment ( http://kb.askmonty.org/v/about-xtradb#comment_76 ) was just posted on the Knowledgebase on the About XtraDB page ( http://kb.askmonty.org/v/about-xtradb ). The text of the comment is: ------------------------------------- How do I activate XtraDB to replace InnoDB? I have searched this website and Percona and have not found this question or answer. I have a clean, rpm install on openSuse 11.3 (x86_64) of mariadb-5.1.51-8.1.x86_64. I copied over /etc/my.cnf which works with MySQL 5.1.43sp1. It has several options like innodb_file_per_table under section [mysqld], yet when I try to run "mysql_install_db --user=mysql", I get errors for every InnoDB option like: [ERROR] /usr/sbin/mysqld: unknown option '--innodb_file_per_table' ------------------------------------- Any thoughts or comments from the list? Please reply here or directly to the comment in the Knowledgebase. Thanks! -- Daniel Bartholomew MariaDB - http://mariadb.org Monty Program - http://montyprogram.com AskMonty Knowledgebase - http://kb.askmonty.org

1 0

[Maria-discuss] MariaDB 5.6
by Peter Laursen 12 Apr '11

12 Apr '11

I noticed this on planet.mysql: http://kb.askmonty.org/v/plans-for-56 I *again* strongly want to discourage a major version number identical with a MySQL/Oracle release. MySQL plans a 5.6 too and I believe that there is already a source-tree available on launchpad. I think I understand that MariaDB 5.6 is planned to use MySQL 5.5 codebase. Am I correct? The problem arises with *generic clients* (ie clients that are supposed to work with not only one specific server version but a broad range of server versions) - including all GUI clients. Such clients will execute a "SELECT VERSION()" after connection to decide what features in the client should be enabled or not (necessary as "SHOW VARIABLES LIKE 'have_***"" and "SELECT ... FROM I_S" cannot always be used due to incomplete instrumentation with those) I have now posted this complaint 2 or 3 times (including the times I complained about the use of 5.2 for a mariaDB based on MySQL 5.1 code as there is alose a (now abandoned) MySQL 5.2 tree. I never had a reply from a MariaDB 'decisionmaker'. Could I at least request a reply this time? Please! :-) I repeat: It is really causing problems for *generic clients* if there are too different 5.6's with different feature sets. What solution would you suggest for a client that shall be able to connect to both MySQL5.6 and MariaDB 5.6 without confusing the features of one server with the other? Peter Webyog

3 5

[Maria-discuss] FreeBSD BuildBot Slave - I can offer one
by Jakob Lorberblatt 07 Apr '11

07 Apr '11

Hello, I noticed that you do not have a regular build of FreeBSD for either common architecture (x86_64 or i386) I would be able to provide that for you, I have equipment to spare and would be willing to lend this to the project for building assistance; in addition I am fairly familiar with FreeBSD compilation in general so I can also help with any issues that arise. I can begin configuring this host for the service, if I could have some direction towards what needs to be done in order for the MariaDB project at large to be able to make use of it. I believe I used to have some bookmarks of resources regarding the matter. However I don't have the references off the top of my head. -- Jakob Lorberblatt President / Technical Lead Beowolf Information Technology Email: jakob(a)native-intelligence.net

3 2

[Maria-discuss] MariaDB and Sun CoolThread
by Alexandre Almeida 05 Apr '11

05 Apr '11

Hi all, I am installing MariaDB on a server Sun/Oracle T5140 with CoolThread/ CMT technology. This server has two physical Sparc CPU with 48 1165MHZ virtual processors each. However, it's look like that MariaDB is performing much better on my MacBook Pro when compared against this server. As far as I can see MariaDB stay locked/running on a single virtual CPU and MariaDB doesn't take advantage of CoolThread/CMT technologies, I mean it can not run on more than one virtual CPU same time. Result: poor performance. Anybody knows if there is something can I do to spread ;-) MariaDB all over cpus? Am I missing something? Thank you all Alex

5 5

Re: [Maria-discuss] Dynamic columns Library
by Michael Widenius 05 Apr '11

05 Apr '11

Hi! I am cc:ing maria-discuss as you accidently forgot to also email the patch there. >>>>> "Oleksandr" == Oleksandr Byelkin <sanja(a)askmonty.org> writes: Oleksandr> Hi! Oleksandr> Here it is dynamic columns library. I do not think that you will have time to review it completely, but please look at it. Oleksandr> Some notes: Oleksandr> - I changed indices from int to uint + some other minor changes in prototypes. Oleksandr> - dynamic_column_exists() has no way to report error (what to do?) I assume that same problem exists for all the other functions. Two options: - Change function type from my_bool to int where -1 means something went bad (like bad format for the blob). - Add an 'error' argument to all functions that is set to <> 0 if something goes wrong. I think we should go with first option, as this is how many other MariaDB functions work. Oleksandr> - inability to get length without keeping data in the same order as headers (because we store only offsets) lead to +1 memove call (i.e we prefer size to speed of update) I think this is ok. We can proably avoid the +1 memmove call by combining the call when we add things to the header to when we place the data in it's right place. For example, on insert <h1><h3><data1><data3> When adding h2 we would first do: <h1><h3><data1><data3> -> <h1><h3><data1><place for h2 + data2><data3> -> <h1><h3><data1><place for h2><data2><data3> -> <h1><h2><h3><data1><data2><data3> In other words, one memove of all data (trough 2 calls) Oleksandr> - sorry for absence of comment but I prefer unit testing and finishing the library in time. ok. > === added file 'include/ma_dyncol.h' > --- include/ma_dyncol.h 1970-01-01 00:00:00 +0000 > +++ include/ma_dyncol.h 2011-03-27 22:36:38 +0000 > @@ -0,0 +1,87 @@ > +#ifndef ma_dyncol_h > +#define ma_dyncol_h > + > +#include <my_global.h> You can assume that my_global.h is done. > +#include <my_sys.h> > +#include <m_string.h> > +#include <decimal.h> > +#include <my_decimal_limits.h> > +#include <mysql_time.h> > + > +typedef DYNAMIC_STRING DYNAMIC_COLUMN; > + > +enum enum_dynamic_column_type > +{ > + DYN_COL_NULL= 0, > + DYN_COL_INT, > + DYN_COL_UINT, > + DYN_COL_DOUBLE, > + DYN_COL_STRING, > + DYN_COL_DECIMAL, > + DYN_COL_DATETIME, > + DYN_COL_DATE, > + DYN_COL_TIME > +}; > + > +typedef enum enum_dynamic_column_type DYNAMIC_COLUMN_TYPE; > + > +struct st_dynamic_column_value > +{ > + DYNAMIC_COLUMN_TYPE type; > + union > + { > + long long long_value; > + unsigned long long ulong_value; > + double double_value; > + struct { > + LEX_STRING string_value; > + CHARSET_INFO *charset; > + }; > + struct { > + decimal_digit_t decimal_buffer[DECIMAL_BUFF_LENGTH]; > + decimal_t decimal_value; > + }; > + MYSQL_TIME time_value; > + }; > +}; > + > +typedef struct st_dynamic_column_value DYNAMIC_COLUMN_VALUE; > + > +my_bool dynamic_column_create(DYNAMIC_COLUMN *str, > + uint column_nr, > + DYNAMIC_COLUMN_VALUE *value); > + > +my_bool dynamic_column_create_many(DYNAMIC_COLUMN *str, > + uint column_count, > + uint *column_numbers, > + DYNAMIC_COLUMN_VALUE *values); > + > +my_bool dynamic_column_update(DYNAMIC_COLUMN *org, uint column_nr, > + DYNAMIC_COLUMN_VALUE *value); > + > +my_bool dynamic_column_delete(DYNAMIC_COLUMN *org, uint column_nr); > + > +/* Returns 1 if value exists */ > +my_bool dynamic_column_exists(DYNAMIC_COLUMN *org, uint column_nr); > + > +/* List of not NULL columns */ > +my_bool dynamic_column_list(DYNAMIC_COLUMN *org, DYNAMIC_ARRAY *array_of_uint); > + > +/* > + TODO: ask - return 1 on error, if column do not exists it is NULL > +*/ Yes! Same for all the above functions. > +my_bool dynamic_column_get(DYNAMIC_COLUMN *org, int column_nr, > + DYNAMIC_COLUMN_VALUE *store_it_here); > + > +#define dynamic_column_column_free(V) dynstr_free(V) > + > +#define dynamic_column_value_init(V) (V)->type= DYN_COL_NULL > + > +void dynamic_column_value_free(DYNAMIC_COLUMN_VALUE *value); > + > +/* > + Prepare value for using as decimal > +*/ > +void dynamic_column_prepare_decimal(DYNAMIC_COLUMN_VALUE *value); > + > +#endif > === modified file 'mysys/Makefile.am' > --- mysys/Makefile.am 2010-11-19 21:33:47 +0000 > +++ mysys/Makefile.am 2011-03-24 13:54:12 +0000 > @@ -57,7 +57,7 @@ > my_handler.c my_netware.c my_largepage.c \ > my_memmem.c stacktrace.c \ > my_windac.c my_access.c base64.c my_libwrap.c \ > - wqueue.c > + wqueue.c ma_dyncol.c > libmysys_la_LDFLAGS = $(AM_LDFLAGS) @WRAPLIBS@ > libmysys_la_LIBADD = $(ZLIB_LIBS) > === added file 'mysys/ma_dyncol.c' > --- mysys/ma_dyncol.c 1970-01-01 00:00:00 +0000 > +++ mysys/ma_dyncol.c 2011-03-27 22:36:38 +0000 > @@ -0,0 +1,1084 @@ > +#include <ma_dyncol.h> > + > +/* > + Flag byte bits > + > + 2 bits which determinate size of offset in the header > + > + 1 byte > + 2 byte > + 3 byte > + 3 byte > +*/ > +#define DYNCOL_FLG_OFFSET1 0 > +#define DYNCOL_FLG_OFFSET2 1 > +#define DYNCOL_FLG_OFFSET3 2 > +#define DYNCOL_FLG_OFFSET4 3 I don't see where you use the above or understand how you would use this. > +/* mask to get above bits */ > +#define DYNCOL_FLG_OFFSET 3 > +/* All known flags mask */ > +#define DYNCOL_FLG_KNOWN 3 > + > + > +/* dynamic column size reserve */ > +#define DYNCOL_SYZERESERVE 80 > + > + > +static my_bool dynamic_column_time_add(DYNAMIC_COLUMN *str, > + MYSQL_TIME *value); > +static my_bool dynamic_column_date_add(DYNAMIC_COLUMN *str, > + MYSQL_TIME *value); > +static my_bool > +dynamic_column_time_read_int(DYNAMIC_COLUMN_VALUE *store_it_here, > + uchar *data, size_t length); > +static my_bool > +dynamic_column_date_read_int(DYNAMIC_COLUMN_VALUE *store_it_here, > + uchar *data, size_t length); > + > + > +my_bool dynamic_column_init_str(DYNAMIC_COLUMN *str, size_t size) > +{ > + if (!size) > + size= DYNCOL_SYZERESERVE; > + /* > + Make string with no fields (empty header) > + - First \0 is flags > + - other four \0 is 0 fileds counter > + */ > + if (init_dynamic_string(str, NULL, > + size + 5, DYNCOL_SYZERESERVE)) > + return TRUE; > + return dynstr_append_mem(str, "\0\0\0\0\0", 5); > +} Why is an empty data 5 bytes instead of 1 or even 0? > + > +/* > + How many bytes needed to store val as variable length integer where > + fist bit indicate continuation of the sequence > +*/ > +size_t dynamic_column_var_uint_bytes(ulonglong val) > +{ > + size_t len= 0; > + do > + { > + len++; > + val>>= 7; > + } while (val); > + return len; > +} > + > +my_bool dynamic_column_var_uint_add(DYNAMIC_COLUMN *str, ulonglong val) > +{ > + if (dynstr_realloc(str, 10)) // max what we can use > + return TRUE; > + > + do > + { > + ulonglong rest= val >> 7; > + str->str[str->length++]= ((val & 0x7f) | (rest ? 0x80 : 0x00)); > + val= rest; > + } while (val); > + return FALSE; > +} > +ulonglong dynamic_column_var_uint_get(uchar *data, size_t *len) > +{ > + ulonglong val= 0; > + *len= 0; > + for(;;) > + { > + val+= (((ulonglong)(data[len[0]] & 0x7f)) << (len[0]*7)); > + if (!(data[len[0]] & 0x80)) > + break; > + len[0]++; > + } > + len[0]++; > + return val; > +} > + > +/* > + How many bytes needed to store val as unsigned > +*/ > + > +size_t dynamic_column_uint_bytes(ulonglong val) > +{ > + size_t len= 0; > + do > + { > + len++; > + val>>= 8; > + } while (val); > + return len; > +} > + > +my_bool dynamic_column_uint_add(DYNAMIC_COLUMN *str, ulonglong val) > +{ > + if (dynstr_realloc(str, 8)) // max what we can use > + return TRUE; > + do > + { > + str->str[str->length++]= val & 0xff; > + val>>= 8; > + } while (val); > + return FALSE; > +} Better to change all the _add' columns to take a pointer where to store the data, not a dynamic column. This makes it easier to do things like only one memmove In other words: void dynamic_column_uint_add(uchar *data, ulonglong val) { for ( ; val ; val>>= 8) *data++= val & 0xff; return FALSE; } We must test for val first to get 0 stored in 0 bytes A nice change is that no of the _add primitives can fail anymore, so we can make all of them void. > +my_bool dynamic_column_uint_read(DYNAMIC_COLUMN_VALUE *store_it_here, > + uchar *data, > + size_t length) > +{ > + ulonglong value= 0; > + size_t i; > + for (i= 0; i < length; i++) > + { > + value+= ((ulonglong)data[i]) << (i*8); > + } > + store_it_here->ulong_value= value; > + return FALSE; > +} Send pointer to store_it_here->ulong_value to the function. (Makes it more general) > + > +/* > + How many bytes needed to store val as signed in following encoding: > + 0 -> 0 > + -1 -> 1 > + 1 -> 2 > + -2 -> 3 > + 2 -> 4 > + ... > +*/ > + > +size_t dynamic_column_sint_bytes(longlong val) > +{ > + return dynamic_column_uint_bytes((val << 1) ^ > + (val < 0 ? ULL(0xffffffffffffffff) : 0)); > +} > + > +my_bool dynamic_column_sint_add(DYNAMIC_COLUMN *str, longlong val) > +{ > + return dynamic_column_uint_add(str, > + (val << 1) ^ > + (val < 0 ? ULL(0xffffffffffffffff) : 0)); > +} > +my_bool dynamic_column_sint_read(DYNAMIC_COLUMN_VALUE *store_it_here, > + uchar *data, > + size_t length) > +{ > + ulonglong val; > + dynamic_column_uint_read(store_it_here, data, length); > + val= store_it_here->ulong_value; > + if (val & 1) > + val= (val >> 1) ^ ULL(0xffffffffffffffff); > + else > + val>>= 1; > + store_it_here->long_value= (longlong)(val); > + return FALSE; > +} Quite clever. The idea I had to store these are: - Store positive integers as DYN_COL_UINT - Store negative numbers as DYN_COL_INT, which just makes them positive and uses the DYN_COL_UINT functions to store the. > + > +size_t dynamic_column_value_len(DYNAMIC_COLUMN_VALUE *value) > +{ > + switch (value->type) { > + case DYN_COL_NULL: > + return 0; > + case DYN_COL_INT: > + return dynamic_column_sint_bytes(value->long_value); > + case DYN_COL_UINT: > + return dynamic_column_uint_bytes(value->ulong_value); > + case DYN_COL_DOUBLE: > + return 8; > + case DYN_COL_STRING: > + return (dynamic_column_var_uint_bytes(value->charset->number) + > + value->string_value.length); > + case DYN_COL_DECIMAL: > + return (dynamic_column_var_uint_bytes(value->decimal_value.intg) + > + dynamic_column_var_uint_bytes(value->decimal_value.frac) + > + decimal_bin_size(value->decimal_value.intg + > + value->decimal_value.frac, > + value->decimal_value.frac)); We should probably store the value of dynamic_column_var_uint_bytes(...) cols in a parameter (size_t lengths[3]) to make things easier for the caller. > + case DYN_COL_DATETIME: > + /* date+time in bits: 14 + 4 + 5 + 5 + 6 + 6 + 20 + 1 61bits = 8 bytes */ > + return 8; > + case DYN_COL_DATE: > + /* date in dits: 14 + 4 + 5 = 23bits ~= 3bytes*/ > + return 3; > + case DYN_COL_TIME: > + /* time in bits: 5 + 6 + 6 + 20 + 1 = 38bits ~= 5bytes*/ > + return 5; > + default: > + DBUG_ASSERT(0); > + return 0; > + } > +} > + > +my_bool dynamic_column_double_add(DYNAMIC_COLUMN *str, double val) > +{ > + if (dynstr_realloc(str, 8)) > + return TRUE; > + float8store(str->str + str->length, val); > + str->length+= 8; > + return FALSE; > +} See comment for adding to integer for how to change this > + > +my_bool dynamic_column_double_read(DYNAMIC_COLUMN_VALUE *store_it_here, > + uchar *data, > + size_t length) > +{ > + if (length != 8) > + return TRUE; > + float8get(store_it_here->double_value, data); > + return FALSE; > +} > + > + > +my_bool dynamic_column_string_add(DYNAMIC_COLUMN *str, LEX_STRING *string, > + CHARSET_INFO *charset) > +{ > + if (dynamic_column_var_uint_add(str, charset->number)) > + return TRUE; > + return dynstr_append_mem(str, string->str, string->length); > +} If we just send data, and not DYNAMIC_COLUMN's, this should be changed to: my_bool dynamic_column_string_add(uchar *data, LEX_STRING *string, uint charset_number, size_t charset_storage_size) { dynamic_column_var_uint_add(data, charset_number)) memmove(data + charset_storage_size, string->str, string->length); } > + > +my_bool dynamic_column_string_read(DYNAMIC_COLUMN_VALUE *store_it_here, > + uchar *data, > + size_t length) > +{ > + size_t len; > + store_it_here->charset= get_charset(dynamic_column_var_uint_get(data, &len), > + MYF(0)); > + if (store_it_here->charset == NULL || len > length) > + return FALSE; Should return -1 > + data+= len; > + store_it_here->string_value.length= (length-= len); > + if (!(store_it_here->string_value.str= malloc(length + 1))) > + return FALSE; > + store_it_here->type= DYN_COL_STRING; // just to correctly destroy the value > + memcpy(store_it_here->string_value.str, data, length); > + store_it_here->string_value.str[length]= '\0'; // just to be safe Don't malloc it. Just return a pointer to the string in the dynamic data! (Yes, now \0, but we can live with that for the additional speed of not having to do malloc for every access). > + return TRUE; > +} > + > + > +my_bool dynamic_column_decimal_add(DYNAMIC_COLUMN *str, > + decimal_t *value) > +{ > + uint bin_size= decimal_bin_size(value->intg + value->frac, value->frac); > + if (dynamic_column_var_uint_add(str, value->intg) || > + dynamic_column_var_uint_add(str, value->frac) || > + dynstr_realloc(str, bin_size)) > + return TRUE; > + decimal2bin(value, (uchar *)str->str + str->length, value->intg + value->frac, > + value->frac); > + str->length+= bin_size; > + return FALSE; > +} To change this, we need to know the sizes for the intg and frac lengths. Can be done by storing them in an array when we calculate them. C > + > +void dynamic_column_prepare_decimal(DYNAMIC_COLUMN_VALUE *value) > +{ > + value->decimal_value.buf= value->decimal_buffer; > + value->decimal_value.len= DECIMAL_BUFF_LENGTH; > + // just to be safe > + value->type= DYN_COL_DECIMAL; > + decimal_make_zero(&value->decimal_value); > +} > + > +my_bool dynamic_column_decimal_read(DYNAMIC_COLUMN_VALUE *store_it_here, > + uchar *data, > + size_t length __attribute__((unused))) > +{ > + size_t len; > + uint intg, frac; > + > + dynamic_column_prepare_decimal(store_it_here); > + intg= dynamic_column_var_uint_get(data, &len); > + data+= len; > + frac= dynamic_column_var_uint_get(data, &len); > + data+= len; > + if (bin2decimal(data, &store_it_here->decimal_value, intg + frac, frac) != > + E_DEC_OK) > + return TRUE; > + return FALSE; > +} > + > + > +my_bool dynamic_column_date_time_add(DYNAMIC_COLUMN *str, MYSQL_TIME *value) > +{ > + /* > + 0<----year----><mn><day>00!<hr-><min-><sec-><---microseconds---> > + 12345678901234123412345 11234512345612345612345678901234567890 > + <123456><123456><123456><123456><123456><123456><123456><123456> > + */ > + return (dynamic_column_date_add(str, value) || > + dynamic_column_time_add(str, value)); > +} > + > + > +my_bool dynamic_column_date_time_read(DYNAMIC_COLUMN_VALUE *store_it_here, > + uchar *data, > + size_t length) > +{ > + /* > + 0<----year----><mn><day>00!<hr-><min-><sec-><---microseconds---> > + 12345678901234123412345 11234512345612345612345678901234567890 > + <123456><123456><123456><123456><123456><123456><123456><123456> > + */ > + if (length != 8) > + goto err; > + store_it_here->time_value.time_type= MYSQL_TIMESTAMP_DATETIME; > + if (dynamic_column_date_read_int(store_it_here, data, 3) || > + dynamic_column_time_read_int(store_it_here, data + 3, 5)) > + goto err; > + return FALSE; > + > +err: > + store_it_here->time_value.time_type= MYSQL_TIMESTAMP_ERROR; > + return TRUE; > +} > + > + > +my_bool dynamic_column_time_add(DYNAMIC_COLUMN *str, MYSQL_TIME *value) > +{ > + uchar *buf= ((uchar *)str->str) + str->length; > + if (dynstr_realloc(str, 5)) > + return TRUE; > + if (value->time_type == MYSQL_TIMESTAMP_NONE || > + value->time_type == MYSQL_TIMESTAMP_ERROR || > + value->time_type == MYSQL_TIMESTAMP_DATE) > + value->neg= value->second_part= value->hour= > + value->minute= value->second= 0; > + DBUG_ASSERT(value->hour <= 23); note that for time arguments, hour can be bigger than 23 ! (as this is elapsed time, not a wall-clock time) > + DBUG_ASSERT(value->minute <= 59); > + DBUG_ASSERT(value->second <= 59); > + DBUG_ASSERT(value->second_part <= 999999); > + /* > + 00!<hr-><min-><sec-><---microseconds---> > + 11234512345612345612345678901234567890 > + <123456><123456><123456><123456><123456> > + */ > + buf[0]= (value->second_part & 0xff); > + buf[1]= ((value->second_part & 0xff00) >> 8); > + buf[2]= (((value->second & 0xf) << 4) | > + ((value->second_part & 0xf0000) >> 16)); > + buf[3]= ((value->minute << 2) | ((value->second & 0x30) >> 4)); > + buf[4]= ((value->neg ? 0x20 : 0) | value->hour); Good but hard to read and verify. Another way would to store this in an uint and then just use int5store() to store it. We need to store at least the same precision of time as MariaDB is doing in a normal field. This means a hour up to TIME_MAX_HOUR which is 838, which means you need to use another byte for the hour. > + str->length+= 5; > + return FALSE; > +} > + > + > +my_bool dynamic_column_time_read(DYNAMIC_COLUMN_VALUE *store_it_here, > + uchar *data, > + size_t length) > +{ > + store_it_here->time_value.year= store_it_here->time_value.month= > + store_it_here->time_value.day= 0; No reason to reset the parts. Either the function should work or we should get an error. > + store_it_here->time_value.time_type= MYSQL_TIMESTAMP_TIME; > + return dynamic_column_time_read_int(store_it_here, data, length); > +} > + > + > +my_bool dynamic_column_time_read_int(DYNAMIC_COLUMN_VALUE *store_it_here, > + uchar *data, > + size_t length) > +{ > + if (length != 5) > + goto err; > + /* > + 00!<hr-><min-><sec-><---microseconds---> > + 11234512345612345612345678901234567890 > + <123456><123456><123456><123456><123456> > + */ > + store_it_here->time_value.second_part= (data[0] | > + (data[1] << 8) | > + ((data[2] & 0xf) << 16)); > + store_it_here->time_value.second= ((data[2] >> 4) | > + ((data[3] & 0x3) << 4)); > + store_it_here->time_value.minute= (data[3] >> 2); > + store_it_here->time_value.hour= (data[4] & 0x1f); > + store_it_here->time_value.neg= ((data[4] & 0x20) ? 1 : 0); > + if (store_it_here->time_value.second > 59 || > + store_it_here->time_value.minute > 59 || > + store_it_here->time_value.hour > 23 || > + store_it_here->time_value.second_part > 999999) > + goto err; > + return FALSE; Hour should not be checked. > + > +err: > + store_it_here->time_value.time_type= MYSQL_TIMESTAMP_ERROR; > + return TRUE; > +} > + > + > +my_bool dynamic_column_date_add(DYNAMIC_COLUMN *str, MYSQL_TIME *value) > +{ > + uchar *buf= ((uchar *)str->str) + str->length; > + if (dynstr_realloc(str, 3)) > + return TRUE; > + if (value->time_type == MYSQL_TIMESTAMP_NONE || > + value->time_type == MYSQL_TIMESTAMP_ERROR || > + value->time_type == MYSQL_TIMESTAMP_TIME) > + value->year= value->month= value->day = 0; > + DBUG_ASSERT(value->year <= 9999); > + DBUG_ASSERT(value->month <= 12); > + DBUG_ASSERT(value->day <= 31); > + /* > + 0<----year----><mn><day> > + 012345678901234123412345 > + <123456><123456><123456> > + */ > + buf[0]= (value->day | > + ((value->month & 0x7) << 5)); > + buf[1]= ((value->month >> 3) | ((value->year & 0x7F) << 1)); > + buf[2]= (value->year >> 7); > + str->length+= 3; > + return FALSE; > +} > + > +my_bool dynamic_column_date_read(DYNAMIC_COLUMN_VALUE *store_it_here, > + uchar *data, > + size_t length) > +{ > + store_it_here->time_value.neg= store_it_here->time_value.second_part= > + store_it_here->time_value.hour= store_it_here->time_value.minute= > + store_it_here->time_value.second= 0; Don't reset the above. > + store_it_here->time_value.time_type= MYSQL_TIMESTAMP_DATE; > + return dynamic_column_date_read_int(store_it_here, data, length); > +} > + > +my_bool dynamic_column_date_read_int(DYNAMIC_COLUMN_VALUE *store_it_here, > + uchar *data, > + size_t length) > +{ > + if (length != 3) > + goto err; > + /* > + 0<----year----><mn><day> > + 12345678901234123412345 > + <123456><123456><123456> > + */ > + store_it_here->time_value.day= (data[0] & 0x1f); > + store_it_here->time_value.month= (((data[1] & 0x1) << 3) | > + (data[0] >> 5)); > + store_it_here->time_value.year= ((((uint)data[2]) << 7) | > + (data[1] >> 1)); > + if (store_it_here->time_value.day > 31 || > + store_it_here->time_value.month > 12 || > + store_it_here->time_value.year > 9999) > + goto err; > + return FALSE; > + > +err: > + store_it_here->time_value.time_type= MYSQL_TIMESTAMP_ERROR; > + return TRUE; > +} > + > + > +my_bool data_add(DYNAMIC_COLUMN *str, DYNAMIC_COLUMN_VALUE *value) > +{ Change to take uchar *data instead of DYNAMIC_COLUMN > + switch (value->type) { > + case DYN_COL_INT: > + return dynamic_column_sint_add(str, value->long_value); > + case DYN_COL_UINT: > + return dynamic_column_uint_add(str, value->ulong_value); > + case DYN_COL_DOUBLE: > + return dynamic_column_double_add(str, value->double_value); > + case DYN_COL_STRING: > + return dynamic_column_string_add(str, &value->string_value, > + value->charset); > + case DYN_COL_DECIMAL: > + return dynamic_column_decimal_add(str, &value->decimal_value); > + case DYN_COL_DATETIME: > + /* date+time in bits: 14 + 4 + 5 + 5 + 6 + 6 40bits = 5 bytes */ > + return dynamic_column_date_time_add(str, &value->time_value); > + case DYN_COL_DATE: > + /* date in dits: 14 + 4 + 5 = 23bits ~= 3bytes*/ > + return dynamic_column_date_add(str, &value->time_value); > + case DYN_COL_TIME: > + /* time in bits: 5 + 6 + 6 = 17bits ~= 3bytes*/ > + return dynamic_column_time_add(str, &value->time_value); > + default: > + DBUG_ASSERT(0); > + return TRUE; > + } > +} > + > + > +size_t dynamic_column_offset_bytes(size_t data_length) > +{ > + if (data_length < 0x1f) // all 1 value is reserved > + return 1; > + if (data_length < 0x1fff) // all 1 value is reserved > + return 2; > + if (data_length < 0x1fffff) // all 1 value is reserved > + return 3; > + if (data_length < 0x1fffffff) // all 1 value is reserved > + return 4; > + return 5; > +} > + > + > +void type_and_offset_store(uchar *place, size_t offset_size, > + DYNAMIC_COLUMN_TYPE type, > + size_t offset) > +{ > + ulong val = (((ulong) offset) << 3) | (type - 1);; Make 3 a define. Remove double ; > + DBUG_ASSERT(type != DYN_COL_NULL); > + DBUG_ASSERT(((type - 1) & (~7)) == 0); // fit in 3 bits type <= 8 would also work... > + switch (offset_size) { > + case 1: > + DBUG_ASSERT(offset < 0x1f); // all 1 value is reserved > + place[0]= (uchar)val; > + break; > + case 2: > + DBUG_ASSERT(offset < 0x1fff); // all 1 value is reserved > + int2store(place, val); > + break; > + case 3: > + DBUG_ASSERT(offset < 0x1fffff); // all 1 value is reserved > + int3store(place, val); > + break; > + case 4: > + DBUG_ASSERT(offset < 0x1fffffff); // all 1 value is reserved > + int4store(place, val); > + break; > + default: > + DBUG_ASSERT(0); // impossible > + } > +} > + > + > +void type_and_offset_read(DYNAMIC_COLUMN_TYPE *type, > + size_t *offset, > + uchar *place, size_t offset_size) > +{ > + ulong val; > + switch (offset_size) { > + case 1: > + val= (ulong)place[0]; > + break; > + case 2: > + val= uint2korr(place); > + break; > + case 3: > + val= uint3korr(place); > + break; > + case 4: > + val= uint4korr(place); > + break; > + default: > + DBUG_ASSERT(0); // impossible > + } > + *type= (val & 0x7) + 1; > + *offset= val >> 3; > +} > + > + > +int column_sort(const void *a, const void *b) > +{ > + return **((uint **)a) - **((uint **)b); > +} > + > +#define FIXED_HEADER_SIZE 5 > + > +my_bool dynamic_new_column_add(DYNAMIC_COLUMN *str, > + size_t header_size, > + size_t offset_size, > + uint column_count, > + uint non_null_count, > + size_t data_size, > + uint *column_numbers, > + DYNAMIC_COLUMN_VALUE *values) > +{ > + uchar *header_end; > + uint **columns_order; > + uint i; > + uint entry_size= 2 + offset_size; > + my_bool err= TRUE; > + bzero(str, sizeof(DYNAMIC_COLUMN)); // just to make dynstr_free() working > + if (!(columns_order= malloc(sizeof(uint*)*column_count))) > + return TRUE; > + if (dynamic_column_init_str(str, > + data_size + header_size + DYNCOL_SYZERESERVE)) > + goto err; > + > + /* sort columns for the header */ > + for (i= 0; i < column_count; i++) > + columns_order[i]= column_numbers + i; > + qsort(columns_order, (size_t)column_count, sizeof(uint*), &column_sort); > + /* check numbers */ -> Change to 'ensure that there is no duplicates' > + for (i= 0; i < column_count - 1; i++) > + if (columns_order[i][0] == columns_order[i+1][0]) > + goto err; > + > + DBUG_ASSERT(str->length == FIXED_HEADER_SIZE); > + str->str[0]|= (offset_size - 1); // size of offset > + int4store(str->str + 1, non_null_count); // columns number Lets limit the number of columns to 65536 (2 bytes) Don't understand why we store non_null_count in header Don't we need to store total number of columns ? I think we should store also null's in the dynamic columns as someone may want to distinguish between a non existing columns and a column with not a set value. We could store this either as an extended type or as datetime with size 0. > + DBUG_ASSERT(str->max_length > str->length + header_size); > + str->length+= header_size; // reserve place for header > + header_end= (uchar *)str->str + FIXED_HEADER_SIZE; > + for (i= 0; i < column_count; i++) > + { > + uint ord= columns_order[i] - column_numbers; > + if (values[ord].type != DYN_COL_NULL) > + { Better to not skip these. > + int2store(header_end, column_numbers[ord]); > + type_and_offset_store(header_end + 2, offset_size, make '2' a define > + values[ord].type, > + str->length - header_size - FIXED_HEADER_SIZE); > + if (data_add(str, values + ord)) > + goto err; Add a note that this can only fail for wrong data, not for memory allocation as everything is already allocated. > + header_end+= entry_size; > + } > + } > + err= FALSE; > +err: > + free(columns_order); > + return err; > +} > + > + > +my_bool dynamic_column_create_many(DYNAMIC_COLUMN *str, > + uint column_count, > + uint *column_numbers, > + DYNAMIC_COLUMN_VALUE *values) > +{ > + size_t data_size= 0; > + size_t header_size, offset_size; > + uint i; > + int not_null_column_count= column_count; > + for (i= 0; i < column_count; i++) > + { > + data_size+= dynamic_column_value_len(values + i); It would be good to store the parts lengths for the values somewhere. Same as for the 'order' parts. One nice way to do this would be to add some 'private' members to the DYANMIC_COLUMN_VALUE struct: uint length[2] /* To store part lengths when packing values */ uint column_number; /* Temp value for column number */ > + if (values[i].type == DYN_COL_NULL) > + not_null_column_count--; > + } > + if ((offset_size= dynamic_column_offset_bytes(data_size)) >= 5) > + { > + /* TODO: error - no more space for data */ > + return TRUE; > + } > + /* header entry is column number + offset&type */ > + header_size= not_null_column_count * (offset_size + 2); > + > + return dynamic_new_column_add(str, > + header_size, offset_size, > + column_count, > + not_null_column_count, > + data_size, > + column_numbers, values); > +} > + > + > +my_bool dynamic_column_create(DYNAMIC_COLUMN *str, uint column_nr, > + DYNAMIC_COLUMN_VALUE *value) > +{ > + return dynamic_column_create_many(str, 1, &column_nr, value); > +} We also need functions: Initialize the DYNAMIC_COLUMN. (Basicly just a call to init_dynamic_string()) > + > +int header_compar(const void *a, const void *b) > +{ > + uint va= uint2korr((uchar*)a), vb= uint2korr((uchar*)b); > + return (va > vb ? 1 : (va < vb ? -1 : 0)); > +} > + /* If the column doesn't exist, *entry_pos contains the place in the header where the data should be stored. */ > +my_bool find_column(DYNAMIC_COLUMN_TYPE *type, uchar **data, size_t *length, > + uchar *header, size_t offset_size, uint column_count, > + uchar *data_end, uint num, uchar **entry_pos) > +{ > + uchar *entry; > + size_t offset, offset_next; > + size_t header_size, entry_size; > + DYNAMIC_COLUMN_TYPE type_next; > + uchar key[2+4]; > + if (!entry_pos) > + entry_pos= &entry; > + > + entry_size= 2 + offset_size; > + header_size= entry_size * column_count; > + int2store(key, num); > + entry= bsearch(key, header, (size_t)column_count, entry_size, > + &header_compar); Change to: uint mid, start, end; int flag; start= 0; end= column_count -1; mid= 1; while (start != end) { uint val; mid= (start + end) / 2; val= uint2korr(header+ mid * entry_size); if ((flag= CMP_NUM(num, val)) >= 0) end= mid; else start= mid+1; } if (start != mid) { val= uint2korr(header+ start * entry_size); flag= CMP_NUM(num, val); } entry= header+ start * entry_size; if (flag < 0) entry+= entry_size; /* Point at next bigger key */ > + if (!entry) This should now be flag != 0 > + { > + *type= DYN_COL_NULL; > + *entry_pos= NULL; > + return FALSE; > + } > + type_and_offset_read(type, &offset, entry + 2, offset_size); > + *data= header + header_size + offset; > + DBUG_ASSERT(*data < data_end); Should not be an assert, but an error condition. Add /* If not last entry */ > + if (entry + entry_size < header + header_size) > + { > + type_and_offset_read(&type_next, &offset_next, entry + entry_size + 2, > + offset_size); > + *length= offset_next - offset; > + } > + else > + *length= data_end - *data; > + *entry_pos= entry; > + return FALSE; > +} > + > +my_bool dynamic_column_get(DYNAMIC_COLUMN *str, int column_nr, > + DYNAMIC_COLUMN_VALUE *store_it_here) > +{ > + uchar *data; > + size_t offset_size, length; > + uint column_count; > + > + if (!str || str->length < FIXED_HEADER_SIZE || > + (str->str[0] & (~DYNCOL_FLG_KNOWN))) > + goto err; Having a length 0 string should be seen as ok. > + > + offset_size= (str->str[0] & DYNCOL_FLG_OFFSET) + 1; > + column_count= uint4korr(str->str + 1); > + > + if (find_column(&store_it_here->type, &data, &length, (uchar*)str->str + 5, > + offset_size, column_count, (uchar*)str->str + str->length, > + column_nr, NULL)) > + goto err; > + > + switch (store_it_here->type) > + { > + case DYN_COL_INT: > + dynamic_column_sint_read(store_it_here, data, length); > + break; > + case DYN_COL_UINT: > + dynamic_column_uint_read(store_it_here, data, length); > + break; > + case DYN_COL_DOUBLE: > + dynamic_column_double_read(store_it_here, data, length); > + break; > + case DYN_COL_STRING: > + dynamic_column_string_read(store_it_here, data, length); > + break; > + case DYN_COL_DECIMAL: > + dynamic_column_decimal_read(store_it_here, data, length); > + break; > + case DYN_COL_DATETIME: > + dynamic_column_date_time_read(store_it_here, data, length); > + break; > + case DYN_COL_DATE: > + dynamic_column_date_read(store_it_here, data, length); > + break; > + case DYN_COL_TIME: > + dynamic_column_time_read(store_it_here, data, length); > + break; > + case DYN_COL_NULL: > + break; > + default: > + goto err; > + } > + return FALSE; > + > +err: > + store_it_here->type= DYN_COL_NULL; Wouldn't it be better to set: store_it_here->type= DYN_COL_ERROR; > + return TRUE; > +} > + > + > +void dynamic_column_value_free(DYNAMIC_COLUMN_VALUE *value) > +{ > + switch (value->type) > + { > + case DYN_COL_INT: > + case DYN_COL_UINT: > + case DYN_COL_DOUBLE: > + case DYN_COL_DECIMAL: > + case DYN_COL_DATETIME: > + case DYN_COL_DATE: > + case DYN_COL_TIME: > + case DYN_COL_NULL: > + break; // no need cleanup; > + case DYN_COL_STRING: > + free(value->string_value.str); No need for the above as we will point directly to the data. > + value->string_value.str= NULL; //just to be safe > + value->type= DYN_COL_NULL; > + break; > + default: > + DBUG_ASSERT(0); > + } > +} > + > + For this one we proably need a couple of different return values: -1 wrong data in DYNAMIC_COLUMN 0 Column was not part of data 1 Column was part of data and is now deleted. > +my_bool dynamic_column_delete(DYNAMIC_COLUMN *str, uint column_nr) > +{ > + uchar *data, *header_entry, *read, *write; > + size_t offset_size, new_offset_size, length, entry_size, new_entry_size, > + header_size, new_header_size, data_size, new_data_size, > + deleted_entry_offset; > + uint column_count, i; > + DYNAMIC_COLUMN_TYPE type; > + > + if (!str || str->length < FIXED_HEADER_SIZE || > + (str->str[0] & (~DYNCOL_FLG_KNOWN))) > + return TRUE; > + > + offset_size= (str->str[0] & DYNCOL_FLG_OFFSET) + 1; > + column_count= uint4korr(str->str + 1); > + > + if (column_count == 0) > + return FALSE; // no columns > + > + if (find_column(&type, &data, &length, (uchar*)str->str + 5, > + offset_size, column_count, (uchar*)str->str + str->length, > + column_nr, &header_entry)) > + return TRUE; We need to have find_column to have different return values for wrong data, did not find it, found it. With this we can do the above as: if ((res= find_column()) <= 0) return res; > + > + if (type == DYN_COL_NULL) > + return FALSE; // no such column Which allows us to skip the above. > + if (column_count == 1) > + { > + /* delete the only column */ > + str->length= FIXED_HEADER_SIZE; > + bzero(str->str, FIXED_HEADER_SIZE); > + return FALSE; Better to make the string 0 size. > + } > + > + entry_size= 2 + offset_size; > + header_size= entry_size * column_count; > + data_size= str->length - FIXED_HEADER_SIZE - header_size; > + > + new_data_size= data_size - length; > + new_offset_size= dynamic_column_offset_bytes(new_data_size); > + DBUG_ASSERT(new_offset_size <= offset_size); > + new_entry_size= 2 + new_offset_size; > + new_header_size= new_entry_size * (column_count - 1); > + > + deleted_entry_offset= (data - (uchar*)str->str) - > + header_size - FIXED_HEADER_SIZE; > + > + > + /* rewrite header*/ > + str->str[0]|= (new_offset_size - 1); // size of offset > + int4store(str->str + 1, column_count - 1); // columns number > + for (i= 0, write= read= (uchar *)str->str + FIXED_HEADER_SIZE; > + i < column_count; > + i++, read+= entry_size, write+= new_entry_size) > + { > + size_t offs; > + uint nm; > + DYNAMIC_COLUMN_TYPE tp; > + if (read == header_entry) > + { > +#ifndef DBUG_OFF > + nm= uint2korr(read); > + type_and_offset_read(&tp, &offs, read + 2, > + offset_size); > + DBUG_ASSERT(nm == column_nr); > + DBUG_ASSERT(offs == deleted_entry_offset); > +#endif > + write-= new_entry_size; // do not move writer > + continue; // skip removed field > + } > + > + nm= uint2korr(read), > + type_and_offset_read(&tp, &offs, read + 2, > + offset_size); > + > + if (offs > deleted_entry_offset) > + offs-= length; // data stored after removed data > + > + int2store(write, nm); > + type_and_offset_store(write + 2, new_offset_size, tp, offs); > + } Please optimize that if new_offset_size == old_offset_size then we should start from header_entry. You should also make the above a function as update also needs to do this! > + > + /* move data */ > + { > + size_t first_chunk_len= (data - (uchar *)str->str) - > + FIXED_HEADER_SIZE - header_size; > + size_t second_chunk_len= new_data_size - first_chunk_len; > + if (first_chunk_len) > + memmove(str->str + FIXED_HEADER_SIZE + new_header_size, > + str->str + FIXED_HEADER_SIZE + header_size, > + first_chunk_len); > + if (second_chunk_len) > + memmove(str->str + > + FIXED_HEADER_SIZE + new_header_size + first_chunk_len, > + str->str + > + FIXED_HEADER_SIZE + header_size + first_chunk_len + length, > + second_chunk_len); > + } > + > + /* fix str length */ > + DBUG_ASSERT(str->length >= > + FIXED_HEADER_SIZE + new_header_size + new_data_size); > + str->length= FIXED_HEADER_SIZE + new_header_size + new_data_size; > + > + return FALSE; > +} > + > + > +my_bool dynamic_column_update(DYNAMIC_COLUMN *str, uint column_nr, > + DYNAMIC_COLUMN_VALUE *value) > +{ > + size_t offset_size, new_offset_size, data_size, new_data_size, > + add_data_size, entry_size, new_entry_size, > + header_size, new_header_size, additional_size, next_offs, > + data_ins_offset; > + uint column_count, i; > + uchar *read, *write; > + my_bool added; > + > + /* TODO: optimize update without deleting */ > + if (dynamic_column_delete(str, column_nr)) > + return TRUE; The problem with doing delete + insert later is that the whole function needs to be rewritten and you can't use any of the old code (for columns that exists). So I suggest you write this ASAP! > + > + if (value->type == DYN_COL_NULL) > + return FALSE; Should be stored. > + > + if (!str || str->length < FIXED_HEADER_SIZE || > + (str->str[0] & (~DYNCOL_FLG_KNOWN))) > + return TRUE; length of 0 should be regarded as ok. > + offset_size= (str->str[0] & DYNCOL_FLG_OFFSET) + 1; > + column_count= uint4korr(str->str + 1); > + > + add_data_size= dynamic_column_value_len(value); > + > + if (column_count == 0) > + { > + /* offset size of one record could be minimal */ > + return dynamic_new_column_add(str, 1 * (1 + 2), 1, 1, 1, > + new_data_size, &column_nr, value); > + } > + > + entry_size= 2 + offset_size; > + header_size= entry_size * column_count; > + data_size= str->length - FIXED_HEADER_SIZE - header_size; > + > + new_data_size= data_size + add_data_size; > + new_offset_size= dynamic_column_offset_bytes(new_data_size); > + DBUG_ASSERT(new_offset_size >= offset_size); > + new_entry_size= 2 + new_offset_size; > + new_header_size= new_entry_size * (column_count + 1); > + > + additional_size= add_data_size + (new_header_size - header_size); > + > + if (dynstr_realloc(str, additional_size)) > + return TRUE; > + > + /* move data to the string end */ > + str->length+= additional_size; > + memmove(str->str + FIXED_HEADER_SIZE + new_header_size + add_data_size, > + str->str + FIXED_HEADER_SIZE + header_size, > + data_size); Data should be placed on it's right position, not at end! You can do now do that with find_column() ! > + /* rewrite header */ > + next_offs= str->length - FIXED_HEADER_SIZE - new_header_size; > + for (i= column_count, > + read= (uchar *)str->str + FIXED_HEADER_SIZE + header_size - entry_size, > + write= ((uchar *)str->str + FIXED_HEADER_SIZE + > + (new_entry_size * column_count)); > + i > 0; > + i--, read-= entry_size, write-= new_entry_size) > + { > + size_t offs; > + uint nm; > + DYNAMIC_COLUMN_TYPE tp; > + > + nm= uint2korr(read); > + type_and_offset_read(&tp, &offs, read + 2, offset_size); > + > + if (!added && column_nr > nm) > + { > + /* place to put the new column */ > + int2store(write, column_nr); > + type_and_offset_store(write + 2, new_offset_size, > + value->type, > + (data_ins_offset= next_offs - add_data_size)); > + /* move previous data */ > + memmove(str->str + FIXED_HEADER_SIZE + new_header_size, > + str->str + FIXED_HEADER_SIZE + new_header_size + add_data_size, > + next_offs - add_data_size); > + added= TRUE; > + write-= new_entry_size; > + } > + int2store(write, nm); > + if (!added) > + offs+= add_data_size; > + type_and_offset_store(write + 2, new_offset_size, tp, offs); > + next_offs= offs; > + } We should just have one function to rewrite the headers! To make life easer, maybe we should assume that header size change operation is a very unlikely one and accept to do an extra memmove when header offset changes? This would allow us to have a simple function that just rewrites the header (from a given point) and another that increases/decreases the header size + all data ? Then all operations are basicly: - If offset_pointer_size changes, fix data - Add / remove things from header and data (with new offset size) - Update header The whole things above would then be very trivial: - realloc str->length if it needs to grow - If offset_pointer_size changes, fix data and update entry_pos - Update data (and create new header entry last) - memmove(entry, entry + entry_size, header_end - entry); - Write data at entry > + if (!added) > + { > + /* put the new column before others */ > + int2store(write, column_nr); > + type_and_offset_store(write + 2, new_offset_size, > + value->type, (data_ins_offset= 0)); > + } > + str->str[0]|= (new_offset_size - 1); // size of offset > + int4store(str->str + 1, column_count + 1); // columns number > + > + { > + /* insert data */ > + size_t save_len= str->length; > + str->length= data_ins_offset + FIXED_HEADER_SIZE + new_header_size; > + if (data_add(str, value)) > + return TRUE; > + str->length= save_len; > + } > + > + return FALSE; > +} > + > + > +my_bool dynamic_column_exists(DYNAMIC_COLUMN *str, uint column_nr) > +{ > + uchar *data; > + size_t offset_size, length; > + uint column_count; > + DYNAMIC_COLUMN_TYPE type; > + > + if (!str || str->length < FIXED_HEADER_SIZE || > + (str->str[0] & (~DYNCOL_FLG_KNOWN))) > + return TRUE; length 0 should return FALSE > + > + offset_size= (str->str[0] & DYNCOL_FLG_OFFSET) + 1; > + column_count= uint4korr(str->str + 1); > + > + if (column_count == 0) > + return FALSE; // no columns > + > + if (find_column(&type, &data, &length, (uchar*)str->str + 5, > + offset_size, column_count, (uchar*)str->str + str->length, > + column_nr, NULL)) > + return FALSE; // error but we can't report it > + > + return type != DYN_COL_NULL; > +} > + > +my_bool dynamic_column_list(DYNAMIC_COLUMN *str, DYNAMIC_ARRAY *array_of_uint) > +{ > + uchar *read; > + size_t offset_size, entry_size; > + uint column_count, i; > + > + if (!str || str->length < FIXED_HEADER_SIZE || > + (str->str[0] & (~DYNCOL_FLG_KNOWN))) > + return TRUE; > + > + offset_size= (str->str[0] & DYNCOL_FLG_OFFSET) + 1; > + column_count= uint4korr(str->str + 1); > + entry_size= 2 + offset_size; > + > + if (init_dynamic_array(array_of_uint, sizeof(uint), > + column_count, 10 CALLER_INFO)) > + return TRUE; -> my_init_dynamic_array2(array_of_uint, sizeof(uint), NULL, column_count,0) > + > + for (i= 0, read= (uchar *)str->str + FIXED_HEADER_SIZE; > + i < column_count; > + i++, read+= entry_size) > + { > + uint nm= uint2korr(read); > + if (insert_dynamic(array_of_uint, (uchar *)&nm)) > + { > + delete_dynamic(array_of_uint); > + return TRUE; > + } > + } > + return FALSE; > +} > === modified file 'sql/my_decimal.h' > --- sql/my_decimal.h 2010-11-24 22:57:34 +0000 > +++ sql/my_decimal.h 2011-03-24 16:30:27 +0000 > @@ -32,32 +32,7 @@ > #include <decimal.h> > C_MODE_END > -#define DECIMAL_LONGLONG_DIGITS 22 > -#define DECIMAL_LONG_DIGITS 10 > -#define DECIMAL_LONG3_DIGITS 8 > - > -/** maximum length of buffer in our big digits (uint32). */ > -#define DECIMAL_BUFF_LENGTH 9 > - > -/* the number of digits that my_decimal can possibly contain */ > -#define DECIMAL_MAX_POSSIBLE_PRECISION (DECIMAL_BUFF_LENGTH * 9) > - > - > -/** > - maximum guaranteed precision of number in decimal digits (number of our > - digits * number of decimal digits in one our big digit - number of decimal > - digits in one our big digit decreased by 1 (because we always put decimal > - point on the border of our big digits)) > -*/ > -#define DECIMAL_MAX_PRECISION (DECIMAL_MAX_POSSIBLE_PRECISION - 8*2) > -#define DECIMAL_MAX_SCALE 30 > -#define DECIMAL_NOT_SPECIFIED 31 > - > -/** > - maximum length of string representation (number of maximum decimal > - digits + 1 position for sign + 1 position for decimal point) > -*/ > -#define DECIMAL_MAX_STR_LENGTH (DECIMAL_MAX_POSSIBLE_PRECISION + 2) > +#include <my_decimal_limits.h> > /** > maximum size of packet length. > === modified file 'unittest/mysys/Makefile.am' > --- unittest/mysys/Makefile.am 2010-11-30 21:11:03 +0000 > +++ unittest/mysys/Makefile.am 2011-03-25 07:46:32 +0000 > @@ -25,4 +25,4 @@ > EXTRA_DIST = CMakeLists.txt > noinst_PROGRAMS = bitmap-t base64-t my_atomic-t lf-t waiting_threads-t \ > - my_vsnprintf-t > + my_vsnprintf-t ma_dyncol-t > === added file 'unittest/mysys/ma_dyncol-t.c' > --- unittest/mysys/ma_dyncol-t.c 1970-01-01 00:00:00 +0000 > +++ unittest/mysys/ma_dyncol-t.c 2011-03-27 22:53:44 +0000 > @@ -0,0 +1,690 @@ > +#include <my_global.h> > +#include <ma_dyncol.h> > +#include <tap.h> > + > + > + > +void test_value_single_null() > +{ > + int rc= FALSE; > + DYNAMIC_COLUMN_VALUE val, res; > + DYNAMIC_COLUMN str; > + /* init values */ > + val.type= DYN_COL_NULL; > + dynamic_column_value_init(&res); > + /* create column */ > + if (dynamic_column_create(&str, 1, &val)) > + goto err; > + dynstr_append(&str, "\1"); str.length--; //check for overflow > + /* read column */ > + if (dynamic_column_get(&str, 1, &res)) > + goto err; > + rc= (res.type == DYN_COL_NULL); > +err: > + ok(rc, "%s", "NULL"); > + /* cleanup */ > + dynamic_column_column_free(&str); > + dynamic_column_value_free(&val); > + dynamic_column_value_free(&res); > +} > + > +void test_value_single_uint(ulonglong num, const char *name) > +{ > + int rc= FALSE; > + DYNAMIC_COLUMN_VALUE val, res; > + DYNAMIC_COLUMN str; > + /* init values */ > + val.type= DYN_COL_UINT; > + val.ulong_value= num; > + dynamic_column_value_init(&res); > + /* create column */ > + if (dynamic_column_create(&str, 1, &val)) > + goto err; > + dynstr_append(&str, "\1"); str.length--; //check for overflow > + /* read column */ > + if (dynamic_column_get(&str, 1, &res)) > + goto err; > + rc= (res.type == DYN_COL_UINT) && (res.ulong_value == num); > + num= res.ulong_value; > +err: > + ok(rc, "%s - %llu", name, num); > + /* cleanup */ > + dynamic_column_column_free(&str); > + dynamic_column_value_free(&val); > + dynamic_column_value_free(&res); > +} > + > +void test_value_single_sint(longlong num, const char *name) > +{ > + int rc= FALSE; > + DYNAMIC_COLUMN_VALUE val, res; > + DYNAMIC_COLUMN str; > + /* init values */ > + val.type= DYN_COL_INT; > + val.long_value= num; > + dynamic_column_value_init(&res); > + /* create column */ > + if (dynamic_column_create(&str, 1, &val)) > + goto err; > + dynstr_append(&str, "\1"); str.length--; //check for overflow > + /* read column */ > + if (dynamic_column_get(&str, 1, &res)) > + goto err; > + rc= (res.type == DYN_COL_INT) && (res.long_value == num); > + num= res.ulong_value; > +err: > + ok(rc, "%s - %lld", name, num); > + /* cleanup */ > + dynamic_column_column_free(&str); > + dynamic_column_value_free(&val); > + dynamic_column_value_free(&res); > +} > + > + > +void test_value_single_double(double num, const char *name) > +{ > + int rc= FALSE; > + DYNAMIC_COLUMN_VALUE val, res; > + DYNAMIC_COLUMN str; > + /* init values */ > + val.type= DYN_COL_DOUBLE; > + val.double_value= num; > + dynamic_column_value_init(&res); > + /* create column */ > + if (dynamic_column_create(&str, 1, &val)) > + goto err; > + dynstr_append(&str, "\1"); str.length--; //check for overflow > + /* read column */ > + if (dynamic_column_get(&str, 1, &res)) > + goto err; > + rc= (res.type == DYN_COL_DOUBLE) && (res.double_value == num); > + num= res.ulong_value; > +err: > + ok(rc, "%s - %lf", name, num); > + /* cleanup */ > + dynamic_column_column_free(&str); > + dynamic_column_value_free(&val); > + dynamic_column_value_free(&res); > +} > + > +void test_value_single_decimal(const char *num) > +{ > + char *end= (((char*)num) + strlen(num)); > + char buff[80]; > + int rc= FALSE; > + int length= 80; > + DYNAMIC_COLUMN_VALUE val, res; > + DYNAMIC_COLUMN str; > + > + /* init values */ > + dynamic_column_prepare_decimal(&val); // special procedure for decimal!!! > + if (string2decimal(num, &val.decimal_value, &end) != E_DEC_OK) > + goto err; > + dynamic_column_value_init(&res); > + > + /* create column */ > + if (dynamic_column_create(&str, 1, &val)) > + goto err; > + dynstr_append(&str, "\1"); str.length--; //check for overflow > + /* read column */ > + if (dynamic_column_get(&str, 1, &res)) > + goto err; > + rc= ((res.type == DYN_COL_DECIMAL) && > + (decimal_cmp(&res.decimal_value, &val.decimal_value) == 0)); > + decimal2string(&res.decimal_value, buff, &length, 0, 0, ' '); > +err: > + ok(rc, "%s - %s", num, buff); > + /* cleanup */ > + dynamic_column_column_free(&str); > + dynamic_column_value_free(&val); > + dynamic_column_value_free(&res); > +} > + > +static CHARSET_INFO *charset_list[]= > +{ > +#ifdef HAVE_CHARSET_big5 > + &my_charset_big5_chinese_ci, > + &my_charset_big5_bin, > +#endif > +#ifdef HAVE_CHARSET_euckr > + &my_charset_euckr_korean_ci, > + &my_charset_euckr_bin, > +#endif > +#ifdef HAVE_CHARSET_gb2312 > + &my_charset_gb2312_chinese_ci, > + &my_charset_gb2312_bin, > +#endif > +#ifdef HAVE_CHARSET_gbk > + &my_charset_gbk_chinese_ci, > + &my_charset_gbk_bin, > +#endif > +#ifdef HAVE_CHARSET_latin1 > + &my_charset_latin1, > + &my_charset_latin1_bin, > +#endif > +#ifdef HAVE_CHARSET_sjis > + &my_charset_sjis_japanese_ci, > + &my_charset_sjis_bin, > +#endif > +#ifdef HAVE_CHARSET_tis620 > + &my_charset_tis620_thai_ci, > + &my_charset_tis620_bin, > +#endif > +#ifdef HAVE_CHARSET_ujis > + &my_charset_ujis_japanese_ci, > + &my_charset_ujis_bin, > +#endif > +#ifdef HAVE_CHARSET_utf8 > + &my_charset_utf8_general_ci, > +#ifdef HAVE_HAVE_UCA_COLLATIONS > + &my_charset_utf8_unicode_ci, > +#endif > + &my_charset_utf8_bin, > +#endif > +}; > + > + > +void test_value_single_string(const char *string, size_t len, > + CHARSET_INFO *cs) > +{ > + int rc= FALSE; > + DYNAMIC_COLUMN_VALUE val, res; > + DYNAMIC_COLUMN str; > + > + /* init values */ > + val.type= DYN_COL_STRING; > + val.string_value.str= (char*)string; > + val.string_value.length= len; > + val.charset= cs; > + dynamic_column_value_init(&res); > + > + /* create column */ > + if (dynamic_column_create(&str, 1, &val)) > + goto err; > + dynstr_append(&str, "\1"); str.length--; //check for overflow > + /* read column */ > + if (dynamic_column_get(&str, 1, &res)) > + goto err; > + rc= ((res.type == DYN_COL_STRING) && > + (res.string_value.length == len) && > + (memcmp(res.string_value.str, string, len) == 0) && > + (res.charset->number == cs->number)); > +err: > + ok(rc, "'%s' - '%s' %u %u-%s", string, > + res.string_value.str, (uint)res.string_value.length, > + (uint)res.charset->number, res.charset->name); > + /* cleanup */ > + val.string_value.str= NULL; // we did not allocated it > + dynamic_column_column_free(&str); > + dynamic_column_value_free(&val); > + dynamic_column_value_free(&res); > +} > + > +void test_value_single_date(uint year, uint month, uint day, const char *name) > +{ > + int rc= FALSE; > + DYNAMIC_COLUMN_VALUE val, res; > + DYNAMIC_COLUMN str; > + /* init values */ > + val.type= DYN_COL_DATE; > + val.time_value.time_type= MYSQL_TIMESTAMP_DATE; > + val.time_value.year= year; > + val.time_value.month= month; > + val.time_value.day= day; > + dynamic_column_value_init(&res); > + /* create column */ > + if (dynamic_column_create(&str, 1, &val)) > + goto err; > + dynstr_append(&str, "\1"); str.length--; //check for overflow > + /* read column */ > + if (dynamic_column_get(&str, 1, &res)) > + goto err; > + rc= ((res.type == DYN_COL_DATE) && > + (res.time_value.time_type == MYSQL_TIMESTAMP_DATE) && > + (res.time_value.year == year) && > + (res.time_value.month == month) && > + (res.time_value.day == day)); > +err: > + ok(rc, "%s - %04u-%02u-%02u", name, year, month, day); > + /* cleanup */ > + dynamic_column_column_free(&str); > + dynamic_column_value_free(&val); > + dynamic_column_value_free(&res); > +} > + > +void test_value_single_time(uint neg, uint hour, uint minute, uint second, > + uint mic, const char *name) > +{ > + int rc= FALSE; > + DYNAMIC_COLUMN_VALUE val, res; > + DYNAMIC_COLUMN str; > + /* init values */ > + val.type= DYN_COL_TIME; > + val.time_value.time_type= MYSQL_TIMESTAMP_TIME; > + val.time_value.neg= neg; > + val.time_value.hour= hour; > + val.time_value.minute= minute; > + val.time_value.second= second; > + val.time_value.second_part= mic; > + dynamic_column_value_init(&res); > + /* create column */ > + if (dynamic_column_create(&str, 1, &val)) > + goto err; > + dynstr_append(&str, "\1"); str.length--; //check for overflow > + /* read column */ > + if (dynamic_column_get(&str, 1, &res)) > + goto err; > + rc= ((res.type == DYN_COL_TIME) && > + (res.time_value.time_type == MYSQL_TIMESTAMP_TIME) && > + (res.time_value.neg == (int)neg) && > + (res.time_value.hour == hour) && > + (res.time_value.minute == minute) && > + (res.time_value.second == second) && > + (res.time_value.second_part == mic)); > +err: > + ok(rc, "%s - %c%02u:%02u:%02u.%06u", name, (neg ? '-' : '+'), > + hour, minute, second, mic); > + /* cleanup */ > + dynamic_column_column_free(&str); > + dynamic_column_value_free(&val); > + dynamic_column_value_free(&res); > +} > + > + > +void test_value_single_datetime(uint neg, uint year, uint month, uint day, > + uint hour, uint minute, uint second, > + uint mic, const char *name) > +{ > + int rc= FALSE; > + DYNAMIC_COLUMN_VALUE val, res; > + DYNAMIC_COLUMN str; > + /* init values */ > + val.type= DYN_COL_DATETIME; > + val.time_value.time_type= MYSQL_TIMESTAMP_DATETIME; > + val.time_value.neg= neg; > + val.time_value.year= year; > + val.time_value.month= month; > + val.time_value.day= day; > + val.time_value.hour= hour; > + val.time_value.minute= minute; > + val.time_value.second= second; > + val.time_value.second_part= mic; > + dynamic_column_value_init(&res); > + /* create column */ > + if (dynamic_column_create(&str, 1, &val)) > + goto err; > + dynstr_append(&str, "\1"); str.length--; //check for overflow > + /* read column */ > + if (dynamic_column_get(&str, 1, &res)) > + goto err; > + rc= ((res.type == DYN_COL_DATETIME) && > + (res.time_value.time_type == MYSQL_TIMESTAMP_DATETIME) && > + (res.time_value.neg == (int)neg) && > + (res.time_value.year == year) && > + (res.time_value.month == month) && > + (res.time_value.day == day) && > + (res.time_value.hour == hour) && > + (res.time_value.minute == minute) && > + (res.time_value.second == second) && > + (res.time_value.second_part == mic)); > +err: > + ok(rc, "%s - %c %04u-%02u-%02u %02u:%02u:%02u.%06u", name, (neg ? '-' : '+'), > + year, month, day, hour, minute, second, mic); > + /* cleanup */ > + dynamic_column_column_free(&str); > + dynamic_column_value_free(&val); > + dynamic_column_value_free(&res); > +} > + > + > +void test_value_multi(ulonglong num0, > + longlong num1, > + double num2, > + const char *num3, > + const char *string4, size_t len4, CHARSET_INFO *cs4, > + uint year5, uint month5, uint day5, > + uint neg6, uint hour6, uint minute6, > + uint second6, uint mic6, > + uint neg7, uint year7, uint month7, uint day7, > + uint hour7, uint minute7, uint second7, > + uint mic7, > + uint *column_numbers, > + const char *name) > +{ > + char *end3= (((char*)num3) + strlen(num3)); > + int rc= FALSE; > + uint i; > + DYNAMIC_COLUMN_VALUE val[9], res[9]; > + DYNAMIC_COLUMN str; > + /* init values */ > + val[0].type= DYN_COL_UINT; > + val[0].ulong_value= num0; > + val[1].type= DYN_COL_INT; > + val[1].long_value= num1; > + val[2].type= DYN_COL_DOUBLE; > + val[2].double_value= num2; > + dynamic_column_prepare_decimal(val + 3); // special procedure for decimal!!! > + if (string2decimal(num3, &val[3].decimal_value, &end3) != E_DEC_OK) > + goto err; > + val[4].type= DYN_COL_STRING; > + val[4].string_value.str= (char*)string4; > + val[4].string_value.length= len4; > + val[4].charset= cs4; > + val[5].type= DYN_COL_DATE; > + val[5].time_value.time_type= MYSQL_TIMESTAMP_DATE; > + val[5].time_value.year= year5; > + val[5].time_value.month= month5; > + val[5].time_value.day= day5; > + val[6].type= DYN_COL_TIME; > + val[6].time_value.time_type= MYSQL_TIMESTAMP_TIME; > + val[6].time_value.neg= neg6; > + val[6].time_value.hour= hour6; > + val[6].time_value.minute= minute6; > + val[6].time_value.second= second6; > + val[6].time_value.second_part= mic6; > + val[7].type= DYN_COL_DATETIME; > + val[7].time_value.time_type= MYSQL_TIMESTAMP_DATETIME; > + val[7].time_value.neg= neg7; > + val[7].time_value.year= year7; > + val[7].time_value.month= month7; > + val[7].time_value.day= day7; > + val[7].time_value.hour= hour7; > + val[7].time_value.minute= minute7; > + val[7].time_value.second= second7; > + val[7].time_value.second_part= mic7; > + val[8].type= DYN_COL_NULL; > + for (i= 0; i < 9; i++) > + dynamic_column_value_init(res + i); > + /* create column */ > + if (dynamic_column_create_many(&str, 9, column_numbers, val)) > + goto err; > + dynstr_append(&str, "\1"); str.length--; //check for overflow > + /* read column */ > + for (i= 0; i < 9; i++) > + if (dynamic_column_get(&str, column_numbers[i], res + i)) > + goto err; > + rc= ((res[0].type == DYN_COL_UINT) && > + (res[0].ulong_value == num0) && > + (res[1].type == DYN_COL_INT) && > + (res[1].long_value == num1) && > + (res[2].type == DYN_COL_DOUBLE) && > + (res[2].double_value == num2) && > + (res[3].type == DYN_COL_DECIMAL) && > + (decimal_cmp(&res[3].decimal_value, &val[3].decimal_value) == 0) && > + (res[4].type == DYN_COL_STRING) && > + (res[4].string_value.length == len4) && > + (memcmp(res[4].string_value.str, string4, len4) == 0) && > + (res[4].charset->number == cs4->number) && > + (res[5].type == DYN_COL_DATE) && > + (res[5].time_value.time_type == MYSQL_TIMESTAMP_DATE) && > + (res[5].time_value.year == year5) && > + (res[5].time_value.month == month5) && > + (res[5].time_value.day == day5) && > + (res[6].type == DYN_COL_TIME) && > + (res[6].time_value.time_type == MYSQL_TIMESTAMP_TIME) && > + (res[6].time_value.neg == (int)neg6) && > + (res[6].time_value.hour == hour6) && > + (res[6].time_value.minute == minute6) && > + (res[6].time_value.second == second6) && > + (res[6].time_value.second_part == mic6) && > + (res[7].type == DYN_COL_DATETIME) && > + (res[7].time_value.time_type == MYSQL_TIMESTAMP_DATETIME) && > + (res[7].time_value.neg == (int)neg7) && > + (res[7].time_value.year == year7) && > + (res[7].time_value.month == month7) && > + (res[7].time_value.day == day7) && > + (res[7].time_value.hour == hour7) && > + (res[7].time_value.minute == minute7) && > + (res[7].time_value.second == second7) && > + (res[7].time_value.second_part == mic7) && > + (res[8].type == DYN_COL_NULL)); > +err: > + ok(rc, "%s", name); > + /* cleanup */ > + val[4].string_value.str= NULL; // we did not allocated it > + dynamic_column_column_free(&str); > + for (i= 0; i < 9; i++) > + { > + dynamic_column_value_free(val + i); > + dynamic_column_value_free(res + i); > + } > +} > + > + > +void test_value_multi_same_num() > +{ > + int rc= FALSE; > + uint i; > + DYNAMIC_COLUMN_VALUE val[5]; > + uint column_numbers[]= {3,4,5,3,6}; // same column numbers > + DYNAMIC_COLUMN str; > + /* init values */ > + for (i= 0; i < 5; i++) > + val[i].type= DYN_COL_NULL; > + /* create column */ > + if (!dynamic_column_create_many(&str, 5, column_numbers, val)) > + goto err; > + rc= TRUE; > +err: > + ok(rc, "%s", "same column numbers check"); > + /* cleanup */ > + dynamic_column_column_free(&str); > + for (i= 0; i < 5; i++) > + dynamic_column_value_free(val + i); > +} > + > + > +void test_update_multi(uint *column_numbers, uint *column_values, > + my_bool *null_values, int only_add, int all) > +{ > + int rc= FALSE; > + int i, j; > + DYNAMIC_COLUMN str; > + DYNAMIC_COLUMN_VALUE val; > + > + val.type= DYN_COL_UINT; > + val.ulong_value= column_values[0]; > + if (dynamic_column_create(&str, column_numbers[0], &val)) > + goto err; > + dynamic_column_value_free(&val); > + for (i= 1; i < all; i++) > + { > + val.type= (null_values[i] ? DYN_COL_NULL : DYN_COL_UINT); > + val.ulong_value= column_values[i]; > + if (dynamic_column_update(&str, column_numbers[i], &val)) > + goto err; > + dynamic_column_value_free(&val); > + > + /* check value(s) */ > + for (j= i; j >= (i < only_add ? 0 : i); j--) > + { > + if (dynamic_column_get(&str, column_numbers[j], &val)) > + goto err; > + if (null_values[j]) > + { > + if (val.type != DYN_COL_NULL || > + dynamic_column_exists(&str, column_numbers[j])) > + goto err; > + } > + else > + { > + if (val.type != DYN_COL_UINT || > + val.ulong_value != column_values[j] || > + !dynamic_column_exists(&str, column_numbers[j])) > + goto err; > + } > + dynamic_column_value_free(&val); > + } > + if (i < only_add) > + { > + DYNAMIC_ARRAY num; > + if (dynamic_column_list(&str, &num)) > + goto err; > + /* cross check arrays */ > + if ((int)num.elements != i + 1) > + { > + delete_dynamic(&num); > + goto err; > + } > + for(j= 0; j < i + 1; j++) > + { > + int k; > + for(k= 0; > + k < i + 1 && column_numbers[j] != > + *dynamic_element(&num, k, uint*); > + k++); > + if (k >= i + 1) > + { > + delete_dynamic(&num); > + goto err; > + } > + for(k= 0; > + k < i + 1 && column_numbers[k] != > + *dynamic_element(&num, j, uint*); > + k++); > + if (k >= i + 1) > + { > + delete_dynamic(&num); > + goto err; > + } > + } > + delete_dynamic(&num); > + } > + } > + > + rc= TRUE; > +err: > + ok(rc, "%s", "add/delete/update"); > + /* cleanup */ > + dynamic_column_column_free(&str); > + dynamic_column_value_free(&val); > +} > + > + > +int main(int argc __attribute__((unused)), char **argv) > +{ > + uint i; > + MY_INIT(argv[0]); > + plan(53); > + char *big_string= malloc(1024*1024); > + if (!big_string) > + exit(1); > + for (i= 0; i < 1024*1024; i++) > + big_string[i]= ('0' + (i % 10)); > + test_value_single_null(); > + test_value_single_uint(0, "0"); > + test_value_single_uint(0xffffffffffffffff, "0xffffffffffffffff"); > + test_value_single_uint(0xaaaaaaaaaaaaaaaa, "0xaaaaaaaaaaaaaaaa"); > + test_value_single_uint(0x5555555555555555, "0x5555555555555555"); > + test_value_single_uint(27652, "27652"); > + test_value_single_sint(0, "0"); > + test_value_single_sint(1, "1"); > + test_value_single_sint(-1, "-1"); > + test_value_single_sint((longlong)0x7fffffffffffffff, > + "0x7fffffffffffffff"); > + test_value_single_sint((longlong)0xaaaaaaaaaaaaaaaa, > + "0xaaaaaaaaaaaaaaaa"); > + test_value_single_sint((longlong)0x5555555555555555, > + "0x5555555555555555"); > + test_value_single_sint((longlong)0x8000000000000000, > + "0x8000000000000000"); > + test_value_single_double(0.0, "0.0"); > + test_value_single_double(1.0, "1.0"); > + test_value_single_double(-1.0, "-1.0"); > + test_value_single_double(1.0e100, "1.0e100"); > + test_value_single_double(1.0e-100, "1.0e-100"); > + test_value_single_double(9999999999999999999999999999999999999.0, > + "9999999999999999999999999999999999999.0"); > + test_value_single_double(-9999999999999999999999999999999999999.0, > + "-9999999999999999999999999999999999999.0"); > + test_value_single_decimal("0"); > + test_value_single_decimal("1"); > + test_value_single_decimal("-1"); > + test_value_single_decimal("9999999999999999999999999999999"); > + test_value_single_decimal("-9999999999999999999999999999999"); > + test_value_single_decimal("0.9999999999999999999999999999999"); > + test_value_single_decimal("-0.9999999999999999999999999999999"); > + test_value_single_string("", 0, charset_list[0]); > + test_value_single_string("1234567890", 10, charset_list[0]); > + test_value_single_string("nulls\0\0\0\0\0", 10, charset_list[0]); > + sprintf(big_string, "%x", 0x7a); > + test_value_single_string(big_string, 0x7a, charset_list[0]); > + sprintf(big_string, "%x", 0x80); > + test_value_single_string(big_string, 0x80, charset_list[0]); > + sprintf(big_string, "%x", 0x7ffa); > + test_value_single_string(big_string, 0x7ffa, charset_list[0]); > + sprintf(big_string, "%x", 0x8000); > + test_value_single_string(big_string, 0x8000, charset_list[0]); > + sprintf(big_string, "%x", 1024*1024); > + test_value_single_string(big_string, 1024*1024, charset_list[0]); > + free(big_string); > + test_value_single_date(0, 0, 0, "zero date"); > + test_value_single_date(9999, 12, 31, "max date"); > + test_value_single_date(2011, 3, 26, "some date"); > + test_value_single_time(0, 0, 0, 0, 0, "zero time"); > + test_value_single_time(1, 23, 59, 59, 999999, "min time"); > + test_value_single_time(0, 23, 59, 59, 999999, "max time"); > + test_value_single_time(0, 21, 36, 20, 28, "some time"); > + test_value_single_datetime(0, 0, 0, 0, 0, 0, 0, 0, "zero datetime"); > + test_value_single_datetime(1, 9999, 12, 31, 23, 59, 59, 999999, > + "min datetime"); > + test_value_single_datetime(0, 9999, 12, 31, 23, 59, 59, 999999, > + "max datetime"); > + test_value_single_datetime(0, 2011, 3, 26, 21, 53, 12, 3445, > + "some datetime"); > + { > + uint column_numbers[]= {100,1,2,3,4,5,6,7,8}; > + test_value_multi(0, 0, 0.0, "0", > + "", 0, charset_list[0], > + 0, 0, 0, > + 0, 0, 0, 0, 0, > + 0, 0, 0, 0, 0, 0, 0, 0, > + column_numbers, > + "zero data"); > + } > + { > + uint column_numbers[]= {10,1,12,37,4,57,6,76,87}; > + test_value_multi(0xffffffffffffffff, 0x7fffffffffffffff, > + 99999999.999e120, "9999999999999999999999999999999", > + big_string, 1024*1024, charset_list[0], > + 9999, 12, 31, > + 0, 23, 59, 59, 999999, > + 0, 9999, 12, 31, 23, 59, 59, 999999, > + column_numbers, > + "much data"); > + } > + { > + uint column_numbers[]= {101,12,122,37,24,572,16,726,77}; > + test_value_multi(37878, -3344, > + 2873.3874, "92743.238984789898", > + "string", 6, charset_list[0], > + 2011, 3, 26, > + 1, 23, 23, 20, 333, > + 0, 2011, 3, 26, 23, 23, 53, 334, > + column_numbers, > + "zero data"); > + } > + test_value_multi_same_num(); > + { > + uint column_numbers[]= {1,2,3,4,5,6,7,2, 3, 4}; > + uint column_values[]= {1,2,3,4,5,6,7,0,30,40}; > + my_bool null_values[]= {0,0,0,0,0,0,0,1, 0, 0}; > + > + test_update_multi(column_numbers, column_values, null_values, 7, 10); > + } > + { > + uint column_numbers[]= {4,3,2,1, 1,2,3,4}; > + uint column_values[]= {4,3,2,1, 0,0,0,0}; > + my_bool null_values[]= {0,0,0,0, 1,1,1,1}; > + > + test_update_multi(column_numbers, column_values, null_values, 4, 8); > + } > + { > + uint column_numbers[]= {4,3,2,1, 4,3,2,1}; > + uint column_values[]= {4,3,2,1, 0,0,0,0}; > + my_bool null_values[]= {0,0,0,0, 1,1,1,1}; > + > + test_update_multi(column_numbers, column_values, null_values, 4, 8); > + } > + return 0; > +} Regards, Monty

2 2

[Maria-discuss] NHibernate and MariaDB
by Jake Drew 05 Apr '11

05 Apr '11

Hello, I have recently been converting 3 SQL Server Databases to MariaDB for a website our company is repackaging. One of the developers is requesting that during our updates we implement HHibernate as an ORM tool for each database. I am not familiar with NHibernate. I am also wondering, if anyone has used this technology with MariaDB or otherwise? If so, what are the performance implications and/or benefits within the context of MariaDB? Sent from my iPhone

3 2

[Maria-discuss] Does server reads /root/.my.cnf
by Πρεκατές Αλέξανδρος 05 Apr '11

05 Apr '11

Hi. # less .my.cnf [mysqld_safe] log-error=/var/log/mysql/error.log [mysqld] log-error=/var/log/mysql/error.log Also i have disabled syslog. But after restarting the server no errol.log is created. So does mysqld reads /root/.my.cnf ? Alexandros

3 3

[Maria-discuss] Bundle more relevant cnf files in MariaDB package
by Haidong Ji 04 Apr '11

04 Apr '11

Hi, I alluded to this in IRC maybe a couple of weeks back. That is, it is really to have intelligent default for MariaDB servers. In particular, the bundled sample my.cnf files, under /usr/local/mysql/support-files, such as my-small.cnf, my-medium.cnf, my-large.cnf, and my-huge.cnf, are laughable in today's world. In the my-huge.cnf, it talks about memory between 1G and 2G as huge. It will be good that MariaDB can bundle a different set, like what has been mentioned here: http://datastrangler.com/windpipe/2011/02/24/custom-mysql-config-files-to-e… I think the sample cnf files in the above blog post is a good starting point. Thanks, Haidong "Alex" Ji PS: I modified http://kb.askmonty.org/v/installing-mariadb-binary-tarballs with the addition of modifying $PATH so a casual user will be able to use various MariaDB clients after the install.

6 14

[Maria-discuss] The Plugins & Storage Engines Summit for MySQL/MariaDB/Drizzle 2011
by Colin Charles 01 Apr '11

01 Apr '11

Hi! Are you a developers who is writing a storage engines or plugins, knowing the plugin architecture of either MySQL, the extensions in MariaDB as well as the differences in Drizzle? Or are you writing User defined functions (UDF)? Or are you interested in developing something related to the MariaDB ecosystem? Then you should be at the summit on April 15 2011 Find out more at: http://kb.askmonty.org/v/storage-engine-summit-2011 -- Colin Charles, http://bytebot.net/blog/ | twitter: @bytebot | skype: colincharles MariaDB: Community developed. Feature enhanced. Backward compatible. Download it at: http://www.mariadb.org/ Open MariaDB/MySQL documentation at the Knowledgebase: http://kb.askmonty.org/

2 1

[Maria-discuss] Segfaulting with signal 6
by Lance Albertson 30 Mar '11

30 Mar '11

I've run into another weird problem where MariaDB is segfaulting and dieing on Gentoo Hardened Linux. I can't figure out if its a weird situation with my Gentoo environment on the machine or something with MariaDB itself. I don't have this problem on the other machine running MariaDB. It seems to happen under load about once or twice a day. I have a nearly identical machine that doesn't have any of these problems. I'm running mariadb-5.1.50 under Hardened Gentoo. The compiled flags are: --- Configuration summary for MariaDB Server version 5.1.50-MariaDB * Installation prefix: /usr * System type: pc-linux-gnu * Host CPU: x86_64 * C Compiler: x86_64-pc-linux-gnu-gcc (Gentoo Hardened 4.4.4-r2 p1.2, pie-0.4.5) 4.4.4 * C++ Compiler: x86_64-pc-linux-gnu-g++ (Gentoo Hardened 4.4.4-r2 p1.2, pie-0.4.5) 4.4.4 * Debug enabled: no * Community Features: yes --- $ ./configure --prefix=/usr --build=x86_64-pc-linux-gnu --host=x86_64-pc-linux-gnu --mandir=/usr/share/man --infodir=/usr/share/info --datadir=/usr/share --sysconfdir=/etc --localstatedir=/var/lib --libexecdir=/usr/sbin --sysconfdir=/etc/mysql --localstatedir=/var/lib/mysql --sharedstatedir=/usr/share/mysql --libdir=/usr/lib64/mysql --includedir=/usr/include/mysql --with-low-memory --with-client-ldflags=-lstdc++ --enable-thread-safe-client --with-comment=Gentoo Linux mariadb-5.1.50 --without-docs --without-big-tables --enable-local-infile --with-extra-charsets=all --with-mysqld-user=mysql --with-server --with-unix-socket-path=/var/run/mysqld/mysqld.sock --without-libwrap --enable-shared --enable-static --without-debug --with-charset=utf8 --with-collation=utf8_general_ci --without-embedded-privilege-control --without-embedded-server --with-ssl=/usr --enable-assembler --with-geometry --with-readline --with-zlib-dir=/usr/ --without-pstack --with-plugindir=/usr/lib64/mysql/plugin --enable-community-features --disable-profiling --with-maria-tmp-tables --with-libevent --with-plugins=csv,myisam,myisammrg,heap,archive,blackhole,maria,xtradb --without-plugin-example --without-plugin-ibmdb2i --without-plugin-partition --without-plugin-federated --without-plugin-ndbcluster --without-plugin-pbxt I haven't opened a bug with Gentoo yet (I'm actually developer myself). I figured I'd ask here first. Any help would be great. Thanks! Here's the output I have from the log file: mysqld: malloc.c:5153: malloc_consolidate: Assertion `nextchunk->fd_nextsize->bk_nextsize == nextchunk' failed. 110329 13:11:25 [ERROR] mysqld got signal 6 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=134217728 read_buffer_size=262144 max_used_connections=376 max_threads=401 threads_connected=80 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 445322 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd: 0x3518dccbaa0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 0x3519ebd3ea0 thread_stack 0x3c000 /usr/lib64/mysql/libmysys.so.0(my_print_stacktrace+0x3c) [0x3547393ab0c] /usr/sbin/mysqld(handle_segfault+0x3cc) [0x7d0ebd1c1c] /lib/libpthread.so.0(+0xf010) [0x35473f86010] /lib/libc.so.6(gsignal+0x35) [0x35472264135] /lib/libc.so.6(abort+0x180) [0x35472265550] /lib/libc.so.6(+0x72c6a) [0x354722a4c6a] /lib/libc.so.6(+0x72f2d) [0x354722a4f2d] /lib/libc.so.6(+0x751e4) [0x354722a71e4] /lib/libc.so.6(__libc_malloc+0x70) [0x354722a9900] /usr/sbin/mysqld(+0x5ad9cf) [0x7d0ee529cf] /usr/sbin/mysqld(+0x5f3bfb) [0x7d0ee98bfb] /usr/sbin/mysqld(+0x5ed71b) [0x7d0ee9271b] /usr/sbin/mysqld(+0x588aa3) [0x7d0ee2daa3] /usr/sbin/mysqld(rr_sequential(READ_RECORD*)+0x29) [0x7d0ecc4b69] /usr/sbin/mysqld(sub_select(JOIN*, st_join_table*, bool)+0x99) [0x7d0ec430b9] /usr/sbin/mysqld(+0x39e524) [0x7d0ec43524] /usr/sbin/mysqld(JOIN::exec()+0xbd7) [0x7d0ec53b47] /usr/sbin/mysqld(mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st _order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*)+0x1b2) [0x7d0ec551a 2] /usr/sbin/mysqld(handle_select(THD*, st_lex*, select_result*, unsigned long)+0x19c) [0x7d0ec55c5c] /usr/sbin/mysqld(+0x33955a) [0x7d0ebde55a] /usr/sbin/mysqld(mysql_execute_command(THD*)+0x405) [0x7d0ebe07f5] /usr/sbin/mysqld(mysql_parse(THD*, char*, unsigned int, char const**)+0x2f3) [0x7d0ebe5cf3] /usr/sbin/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0x53a) [0x7d0ebe6b2a] /usr/sbin/mysqld(libevent_thread_proc+0x182) [0x7d0ebda682] /lib/libpthread.so.0(+0x68c4) [0x35473f7d8c4] /lib/libc.so.6(clone+0x6d) [0x35472302b4d] Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0x3518424f018 is an invalid pointer thd->thread_id=14399 thd->killed=NOT_KILLED The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. -- Lance Albertson Systems Administrator / Architect Open Source Lab Network Services Oregon State University

3 3

Re: [Maria-discuss] client library shared library version changed in 5.5.10
by Sergei Golubchik 28 Mar '11

28 Mar '11

Hi, Monty! On Mar 18, Michael Widenius wrote: > >>>>> "MARK" == MARK CALLAGHAN <mdcallag(a)gmail.com> writes: > > > I think there was discussion about how client library shared library > > versions during the Lisbon meeting. > > >> From http://dev.mysql.com/doc/refman/5.5/en/news-5-5-10.html > >>>> > > Incompatible Change: The shared library version of the client library > > was increased to 18 to reflect ABI changes, and avoid compatibility > > problems with the client library in MySQL 5.1. Note that this is an > > incompatible change between 5.5.10 and earlier 5.5 versions, so client > > programs that use the 5.5 client library should be recompiled against > > the 5.5.10 client library. > > Serg, as you have now probably have full insight into the MariaDB 5.5 > code, is it possible to keep the API compatible with MariaDB 5.1 / > MySQL 5.1 to not force all applications to have to be recompiled when > upgrading to 5.5 ? The API incompatibility was caused by migration to CMake. If you want to keep libraries compatible, if's something that Wlad may need to look into. Regards, Sergei

4 6

[Maria-discuss] Suggested sample my.cnf for MariaDB package
by Haidong Ji 25 Mar '11

25 Mar '11

All, I proposed updating the packaged my.cnf sample files before. After some thinking and deliberation, here are my thoughts on the sample my.cnf: 1. There are so many factors that affect settings in my.cnf so it is impossible to please everybody. There is a lot of debates on this. 2. There are values in having just ONE sample my.cnf to get beginners started 3. It should be short 4. It should incorporate best practices, to the extent that best practices can be agreed 5. It should benefit as many people as possible My proposal is pasted below. Comments welcome. At the same time, constant back-and-forth bickering won't benefit much either, in my opinion. Thanks, Haidong "Alex" Ji # MariaDB/Percona/Oracle MySQL programs look for option files in a set of # locations which depend on the deployment platform. # You can copy this option file to one of those # locations. For information about these locations, see: # http://dev.mysql.com/doc/mysql/en/option-files.html # # For detailed definition of settings, see URL below: # http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html # Please do your own research, consult various resources # and experts for optimal settings for your own needs. # Some basic ones are provided below to get you started. # The following options will be passed to all MySQL clients [client] port = 3306 socket = /var/lib/mysql/mysql.sock # The MySQL server [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock user=mysql log-bin = mysql-bin log-slow-queries sync_binlog = 1 expire_logs_days= 10 wait_timeout = 30 #server-id = last octet of IP address #character-set-server = utf8 [mysqldump] quick

4 6

Re: [Maria-discuss] Suggested sample my.cnf for MariaDB package
by Haidong Ji 20 Mar '11

20 Mar '11

Hi Monty, > The problem is that a too simple my.cnf will please very few (mainly > desktop users) and leave the a bit more advanced user to search on > the net and get 'anything' that looks remotely right. > I respectfully disagree. I think the sample my.cnf is targeted for desktop users, medium-sized company users, and small business owners whose expertise is low and frustration level can run high. A generic, simple, and short my.cnf file serves that constituents well, gains their confidence, and is conducive in building up their comfort and skill level gradually, that they would seek information themselves on the web and/or ask expert for help as the need for performance/scalability rises. But that is AFTER they are comfortable enough with the ease, power, and simplicity of MariaDB. In other words, my view is that its target audience is not big size social network sites, Facebook, Google, and other money-rich financial companies. Those guys probably don't need the built-in sample anyway. Personally, I feel a minimalistic file works the best. This is not a debate of enterprise-worthiness of MariaDB/Percona/MySQL. It is. It's just that I think the sample's target is small scale users, relatively speaking. > I prefer the other approach taken by most unix programs (like postfix) > have taken where you have most important options in the config file, > but the not common ones are commented, with a good explanation of when > to enable the option. This is makes it much faster to get good > options without having to go trough a lot of manuals and web sites... This will make it long, which tends to cause confusion. A typical user/developer will be overwhelmed by the slew of jargons, which is a turn-off, IMHO. Given the importance of key_buffer_cache, however, perhaps that should be mentioned as a comment. > I also personally like the original approach with small, medium, large and > huge default config files, but now with many storage engines these are > also not as valid as they originally where. (And I agree with Arjen > that many of the values needs to be updates). I actually went ahead with that assumption as well initially, but after some thinking, I feel that contradict with the simplicity rule, so I stopped. But the idea is still good, in my mind. So perhaps we can put my-1gb.cnf, my-4gb.cnf, my-32gb.cnf on the knowledgebase? > > What should (at least) be done is to create new sections to the my.cnf > files that one can easily enable if one uses different storage > engines. I would also like to see more options and better comments in > the defaults file. > I agree, but I think we should NOT put in samples for every storage engine under the sun. Once again, simplicity rules in my mind, so we need only cover 2: MyISAM (Aria when Aria is mature enough to phase out MyISAM) and InnoDB. I do realize that in the sample I sent out, I didn't put any for InnoDB, which is probably not a good idea. > I have this week actually added to MariaDB 5.2 some new startup > options and groups to be able to make the option files smaller. > > For example, now in 5.2 both the server and clients are reading the > 'client-server' group, so one doesn't have to specify the socket and > port twice. > > I also added and --log-basedir so that one can specify the name for > all log files with one command (instead of having to use 8+ different > options) and new groups [mariadb] and [client-mariadb] so that one can > use the same config file for MySQL and MariaDB running on the same > computer. > That's fantastic! In summary, it appears that we disagree quite a bit on this. And the main point of contention, I think, is who is the target audience. There is definitely a need to replace/update the current ones, though. What about we package a minimalist sample my.cnf, and put a line in it pointing to a web page on KnowledgeBase that has more samples available with better explanation. Better yet, it is a wiki so the knowledge can be crowd-sourced. Haidong

1 0

[Maria-discuss] client library shared library version changed in 5.5.10
by MARK CALLAGHAN 18 Mar '11

18 Mar '11

I think there was discussion about how client library shared library versions during the Lisbon meeting. >From http://dev.mysql.com/doc/refman/5.5/en/news-5-5-10.html >>> Incompatible Change: The shared library version of the client library was increased to 18 to reflect ABI changes, and avoid compatibility problems with the client library in MySQL 5.1. Note that this is an incompatible change between 5.5.10 and earlier 5.5 versions, so client programs that use the 5.5 client library should be recompiled against the 5.5.10 client library. >>> -- Mark Callaghan mdcallag(a)gmail.com

1 0

[Maria-discuss] Installation problems of 5.2 in debian squueze
by Πρεκατές Αλέξανδρος 13 Mar '11

13 Mar '11

Hi. in debian squeeze trying to install mariaDB5.2 i get a conflict with mysql- core used by aconandi. what should i do? Thanks. aprekates

2 4

[Maria-discuss] askmonty.org vs kb.askmonty.org
by MARK CALLAGHAN 13 Mar '11

13 Mar '11

Why must I have separate logins for askmonty.org and kb.askmonty.org. I was confused by this and assume others will be confused by it. This isn't efficient. After logging into kb.askmonty.org and going to my profile page, there is no option to change my password. I am "Mdcallag" on both kb.askmonty.org and askmonty.org After logging into kb.askmonty.org I get "403 ERROR: Access denied" on everything including: http://kb.askmonty.org/v/plans-for-56 http://kb.askmonty.org/v/legal http://kb.askmonty.org/v/about -- Mark Callaghan mdcallag(a)gmail.com

1 0

Re: [Maria-discuss] Bundle more relevant cnf files in MariaDB package
by Haidong Ji 12 Mar '11

12 Mar '11

cool, Kurt von Finck, I will do it. I couldn't find a good enough translation in English of the “遣将不如激将” phrase, but I like your message. I will dig around the source code base in Launchpad, and get this damn thing started. I bet those config files are old relic from 10 years ago, or even more. 可八方，好，“遣将不如激将”，我会按你的建议去做！ On Fri, Mar 11, 2011 at 5:23 PM, Kurt von Finck <kurt(a)montyprogram.com> wrote: > On Fri, Mar 11, 2011 at 2:56 PM, Haidong Ji <haidong.ji(a)gmail.com> wrote: >> Hi, >> >> I alluded to this in IRC maybe a couple of weeks back. That is, it is >> really to have intelligent default for MariaDB servers. >> >> In particular, the bundled sample my.cnf files, under >> /usr/local/mysql/support-files, such as my-small.cnf, my-medium.cnf, >> my-large.cnf, and my-huge.cnf, are laughable in today's world. In the >> my-huge.cnf, it talks about memory between 1G and 2G as huge. It will >> be good that MariaDB can bundle a different set, like what has been >> mentioned here: >> >> http://datastrangler.com/windpipe/2011/02/24/custom-mysql-config-files-to-e… >> >> I think the sample cnf files in the above blog post is a good starting point. >> >> Thanks, >> Haidong "Alex" Ji >> >> PS: I modified http://kb.askmonty.org/v/installing-mariadb-binary-tarballs >> with the addition of modifying $PATH so a casual user will be able to >> use various MariaDB clients after the install. > > Not to sound snarky, but MariaDB is an open project. I would encourage > you to create sample my.cnf files, and submit them with bzr for review > and approval by a maria-captain. > > The real beauty of an open project is the ability to DIY for the > improvements and changes you think are important. Just Do It. :) > > -- > ./k > > Kurt von Finck > Chief Community And Communications Officer > Monty Program > http://montyprogram.com >

1 0

[Maria-discuss] MariaDB High Availability Solutions
by Daniel Bartholomew 03 Mar '11

03 Mar '11

A new question on the KB: http://kb.askmonty.org/v/mariadb-high-availability-solutions -- Daniel Bartholomew MariaDB - http://mariadb.org Monty Program - http://montyprogram.com AskMonty Knowledgebase - http://kb.askmonty.org

1 0

[Maria-discuss] Manning a MariaDB booth in Santa Clara April 12-13 2011
by Colin Charles 01 Mar '11

01 Mar '11

Hi! This is a call for volunteers. MariaDB is planning to have a booth at the O'Reilly MySQL Conference & Expo 2011 happening April 11-14 2011 in Santa Clara, California, USA. Volunteers are needed to man the booth April 12-13 2011. Are you local to the area? Do you dig MariaDB? This is your chance to come join a few of us and help spread the good word More information at: http://en.oreilly.com/mysql2011 Please respond to this email (either on list, or privately) if you are available and local to the area Thanks cheers, -c -- Colin Charles, http://bytebot.net/blog/ | twitter: @bytebot | skype: colincharles MariaDB: Community developed. Feature enhanced. Backward compatible. Download it at: http://www.mariadb.org/ Open MariaDB/MySQL documentation at the Knowledgebase: http://kb.askmonty.org/

1 0

[Maria-discuss] Installing MariaDB using MySQL Workbench on Windows
by Jake Drew 21 Feb '11

21 Feb '11

Hello everyone, I am trying to install MariaDB locally on a Windows XP desktop machine for testing purposes. We have a server coming (eventually), but I would like to get started in the meantime. There does not seem to be much information on installing and configuring MariaDB on Windows for use with the MySQL Workbench. *Here are the steps I have taken so far:* *Installed MariaDB on windows:* 1. Downloaded and ran MariaDB-5.2.4-win32-alt.exe *Created MariaDB my.ini configuration file* 2. Copied C:\Program Files\MariaDB 5.2.4\my-large.ini 3. Pasted to C:\Program Files\MariaDB 5.2.4\*my.ini* with no modifications ** *Created a Windows Service for MariaDB named MySQL and start that service on Windows:* 4. Start > Run > cmd.exe (open a command prompt) 5. cd C:\Program Files\MariaDB 5.2.4\bin 6. mysqld.exe --install MariaDB --defaults-file="C:\Program Files\MariaDB 5.2.4\my.ini" (got message "service was installed successfully", also see service listed in the "Services" window. 7. net start MariaDB (Service shows as started in "Services" window) *Installed MySQL Workbench:* 8. Downloaded and ran mysql-workbench-gpl-5.2.31a-win32.msi *Set up MariaDB Server on MySQL Workbench:* 9. Start MySQL Workbench and select "Create New Server Instance" 10. Made sure "Path to configuration file" under "Windows Management" pointed to: C:\Program Files\MariaDB 5.2.4\my.ini 11. Named the new server instance "MariaDB" 12. Double clicked the new server instance "MariaDB" after it was created. *Got what appear to be success messages:* 2011-02-17 15:23:47 - Workbench will use cmd shell commands to start/stop this instance 2011-02-17 15:23:47 - Status check of service 'MariaDB' returned running 2011-02-17 15:23:48 - Server is running 2011-02-17 15:34:10 - Status check of service 'MariaDB' returned running *Questions:* 1. Is there anything else I should do to configure MariaDB for use under Windows? 2. Almost all tabs under the "Configuration" section are blank in MySQL Workbench for the ner server instance MariaDB. This was not the case when I was running MySQL 5.1.53 on Windows. Is this normal? If not, can it be resolved? Thanks! Jake Drew

5 5

[Maria-discuss] Error on starting MariaDB
by Joshua Partogi 18 Feb '11

18 Feb '11

Hi all, I am getting this error when starting mariadb-5.2.4: [ERROR] Aria engine is not enabled or did not start. The Aria engine must be enabled to continue as mysqld was configured with --with-aria-tmp-tables I compiled mariadb from source. Can anybody give me a shed of light on what I need to do to resolve the problem? Thanks heaps. -- http://twitter.com/jpartogi

2 1

[Maria-discuss] Question about FORCE INDEX and records_in_range
by MARK CALLAGHAN 27 Jan '11

27 Jan '11

We suspect that our servers do too much extra disk IO in ha_innobase::records_in_range to determine selectivity for indexes that don't get used in a query. For example when there are multiple indexes that might be used, I assume that the MySQL optimizer calls records_in_range for each and I know that InnoDB does two index lookups per call to find the leaf blocks for the start and stop predicates of the index scan. For the index that is used on the query any disk IO done in records_in_range isn't wasted. We can think of it as prefetch. But for the other indexes that disk IO is likely to be wasted. I rarely touch or even read optimizer code in MySQL. How difficult would it be to have an option to either not call records_in_range when a hint is used (such as FORCE INDEX or REALLY FORCE INDEX)? The alternative is to figure out in records_in_range when the special hint has been used for an index other than the one for which records_in_range was called and return a large value without doing index lookups for all but the hinted index. -- Mark Callaghan mdcallag(a)gmail.com

3 4

[Maria-discuss] Glibc 2.3 support in MariaDB builds ?
by Jocelyn Fournier 19 Dec '10

19 Dec '10

Hi, I'm trying to push my company to adopt MariaDB 5.2. Unfortunately, the currently available binaries (at least for the x86-64 version) requires a glibc 2.4, where we have only a 2.3 version (debian etch) installed on our server. MySQL 5.5 standard binaries, as well as Percona's one perfectly support glibc 2.3, so is there any plan to provide glibc 2.3 compatible binaries for MariaDB ? Thanks and regards, Jocelyn Fournier

2 2

[Maria-discuss] SphinxSE missing plug.in
by Brian Evans 17 Dec '10

17 Dec '10

MariaDB 5.2.4 tarball is missing the SphinxSE plug.in file in storage/sphinx. I'm not sure if this was intentional or not, so I thought I might point it out.

2 1

Re: [Maria-discuss] Per-db binlogging
by Weldon Whipple 14 Dec '10

14 Dec '10

Sergei, Thank you very much for your reply! A later post by Henrik Ingo seems to say that I should cc maria-discuss(a)lists.launchpad.net. I'll try it and see if it bounces--I THINK I'm registered with launchpad.net :-) Let me know if sending to both lists annoys anyone, and I will quit the cc :-) See my responses below On Wed, Sep 15, 2010 at 1:53 PM, Sergei Golubchik <serg(a)askmonty.org> wrote: > Hi, Weldon! > > On Sep 13, Weldon Whipple wrote: <snip/> >> Is it plausible to identify all places where a binlog addition might >> occur? In those places, if the singleton USER_BIN_LOG_MRG exists, ask >> it if the current DB is being binlogged. If so, send the request to >> the DB_BIN_LOG instance. > > By "addition" you mean when an event is written to binlog? Yes! (exactly)--any event relating to that database that is written to binlog. (Hmm ... I wonder if there might ever be more "global" events that should be written ... Maybe we don't care about them??). Also, since these databases are "tied" to a user on a box, we probably don't care about renaming a DB ... > See where mysql_bin_log.write is called. Excellent information! (I was hoping it might be that easy ???). If I succeed in making a DB_BINLOG class derive from MYSQL_BIN_LOG (with an instance of DB_BINLOG for each database that is currently binlogging), I could--in theory, at least--just call something like db_binlog->write () at those places. > >> Questions I Have. >> ---------------- >> >> 1. I notice that binlog is in the list of engines and plugins >> (displayed by SHOW ...). The bottom of log.cc has >> "mysql_declare_plugin(binlog)". Do I need one for db_plugin? >> (probably not?) > > depends. > binlog needs to be declated a plugin, to be able to pretent being a > storage engine, to be able to force two phase commit (2PC). > > 2PC is used to commit or rollback a transaction atomically in more than > one engine. And MySQL only uses 2PC when a transaction spans more than > one engine. But we want a transaction to be committed in the engine and > written to a binlog - and it should be done atomically too. So, binlog > pretends to be an engine, pretends to participate in a transaction, > which forces MySQL to use 2PC which can guarantee that a transaction > is either committed and written to a binlog - or not committed and not > written. Reliably, even if MySQL crashes in the middle. > > So, if you don't use innodb, or don't use > innodb_flush_log_at_trx_commit=1 or don't use sync_binlog=1 > you probably don't need to support 2PC as your setup is not crash proof > anyway. > > If you care about innodb<->binlog consistency in the presence of > crashes, you should support 2PC in your binlog, as explained above. We DO have a small percentage of innodb databases. If 2PC commits (rolls back, etc.) atomically to more than one engine, does that mean TWO (only)? ... or could it happen for three (specifically, innodb, MYSQL_BIN_LOG's binlog, and DB_BIN_LOG's binlog)? The two kinds of binlog files would have the same format (except that one is for only one database). Are they considered two storage engines/plugins? (I notice that the "mysql_declare_plugin(binlog)" declaration is at the bottom of log.cc. Most of the other storage engines' corresponding declarations are in files in the storage subdirectories. If I were to need a "mysql_declare_plugin(db_binlog)" declaration, could it also go at the bottom of log.cc? ... Or (since a DB_BINLOG is AKO [a kind of] MYSQL_BIN_LOG [in the OO sense]), are MYSQL_BIN_LOGs and DB_BINLOGs both kinds of binlog plugins ...? >> 2. I've diagrammed the binary log event taxonomy--the 24 classes that >> inherit from Log_event (in log_event.cc/h). That was my main >> motivation for wanting to inherit from MYSQL_BIN_LOG. > > Was that a question? :) No. Just justification for my desire to simplify by having DB_BINLOG extend the MYSQL_BIN_LOG class. (I realize that I MIGHT have to add "virtual" in front of a method or two--possibly. Hopefully I would need to make no--or minimal-- changes to MYSQL_BIN_LOG.) >> 3. On the other hand, I wonder if inheriting from MYSQL_BIN_LOG might >> be overkill for "master only" binlogging--without the >> slave/replication part of it. > > No, I think inheriting from MYSQL_BIN_LOG is correct. > At least it's much easier to build on existing binlogging than to > implement everything from scratch. Amen!! >> 4. What version should I be working on? (We're currently running >> 5.1.47.) What should I check out of bzr/launchpad? (Is that still >> what we're doing with the changes in ownership?) > > What version you plan to use? > If it'll be 5.1 - you have to work in 5.1, use lp:mysql-server/5.1 > branch: > > bzr branch lp:mysql-server/5.1 We TRY to keep as current as possible (at the company). At some point, we'll need to move to the next major release--whatever it ends up being. (Then there is also the unknown [to me] of MySQL vs. MariaDB ... :-) We'll use bzr branch lp:mysql-server/5.1 > >> 5. I see that THD is involved in binlogging. (Several THD methods are >> defined in log.cc.) Are there any caveats here? > > There is a binlog cache (or buffer) in THD that stores binlog events for > the current transaction. They all are written to disk together, when a > transaction is committed. You need to decide what you want to do when > the current database is changed in the middle of a transaction - what > binlog the transaction should be written to. By "the current database is changed", I imagine you refer to the "USE 'database';" command. That's a good question. I THINK we have decided to ignore (for the present, at least), queries (and also transactions?) that involve multiple databases. Because we are doing per-DB binlogging, if multiple databases are involved, we (almost) consider it an anomaly at our ISP ... (I'm thinking that we might need to consider THD's binlog cache only for transactions that involve innodb and no more than one database ...) >> 6. What files should I use? A new h/cc pair? ... or just add to the >> hierarchy in log.h/cc? > > As you like. > New pair of files is cleaner. But it may require you to move some > declarations from log.cc to a header file, or to make some static > functions extern. All my prototypes have just expanded what is in log.cc and log.h. (I came VERY close to creating db_binlog.cc/h, however, which I would have done in some of my past--non-MySQL--projects). However, noticing most of the logging hierarchy in the single log.cc/h pair, I decided the implementors must have done it for a reason, and have decided to follow suit--for now, at least. > >> What I've Implemented So Far (about 13 Prototypes). >> -------------------------------------------------- >> >> (PRELIMINARY NOTE:) >> >> Our initial plan was to call it "Per-User Binlogging". The user here >> is a cPanel [control panel] user. cPanel's database naming convention >> is to have all of a user's database names start with the username, >> followed by an underscore, followed by a distinctive database >> name. Thus all the commands above start with >> >> <verb> USER ... >> >> Originally most of them could end either "DATABASE <db>" or "USER >> <user>" (with user meaning cPanel user). Thus, in the original plan >> the first command above was: >> >> START USER BINLOG FOR (USER <user> | DATABASE <db>) >> >> We have realized that per-database binlogging is all we really need. >> We imagine that others in our situation might want something similar. >> Since not everyone uses cPanel, we should forget per[cPanel]-user >> binlogging, focusing on per-database binlogging. > > If every user is a different MySQL account - that it a different row in > the mysql.user table - than per-user binlogging is much cleaner and > better defined concept. > > A statement can affect more than one database (say, UPDATE db1.t1, > db2.t2 SET ...), the current database may be changed in the middle of > transaction, and so on. On the other hand, any statement or a > transaction is always executed in the context of as single user - you > cannot change user in the middle of transaction. (*) > > (*) changing security context only affect privileges, but not the > destination binlog. Actually (and this is because of the way cPanel does things--I have been "bitten" by this many times!!), each database has a (different) corresponding user in the mysql.user table (!!!). So (for example), if a user signs up with our ISP with a user name of elvispre every one of his (perhaps hundreds/thousands of) databases will have names something like: elvispre_friends elvispre_earlyyears elvispre_hitsongs . . . And (here's the surprising part)--each one of those databases will have a MATCHING ROW IN mysql.user!! So, for us to migrate our customer elvispre to another physical server, we DO (ultimately) need to binlog all of the databases/users that match/are like 'elvispre\_%'; . In some of my earlier prototypes, the START/FLUSH-LOCK/SHOW/UNLOCK/STOP commands have allowed for "like" clauses at the end. Maybe I need to go that route? ALL of my prototypes have had binlogging PER DATABASE. I wonder how difficult/feasible/desirable/useful it would be to create separate binlog files for databases (or users) whose name matches a "like" clause. ... Probably more important: Would this feature be of general interest to the MySQL user community at large? (... and, if so, should it be database- or user- or database_pattern- or user_pattern-based). (My initial idea is that per [single] database/user might be easiest to define/implement??) > > Regards, > Sergei Thanks again for your reply!! Weldon

2 4

[Maria-discuss] Using MariaDB in production?
by Colin Charles 09 Dec '10

09 Dec '10

Hi! In February 2010, we released MariaDB 5.1. In November 2010, we released MariaDB 5.2. In a span of those months, we've seen a 4x increase in downloads of MariaDB. I've heard many stories of people running it on their blog, and there are people running it on single server instances. I myself run it on about six servers of mine in production... And Daniel will attest to running all the Monty Program infrastructure on MariaDB :) I'm curious to know where MariaDB is going. Where its getting installed. Are only software developers playing with it, or is it getting into production environments? Please don't hesitate to discuss it here on the maria-discuss mailing list so we can all get a better idea of where MariaDB is being used. If its being used in "secretive" environments, don't hesitate to drop me a private email Thanks! cheers, -c p/s: If you're able to, don't hesitate to display the badges: http://badges.mariadb.org/ -- Colin Charles, http://bytebot.net/blog/ | twitter: @bytebot | skype: colincharles MariaDB: Community developed. Feature enhanced. Backward compatible. Download it at: http://www.mariadb.org/ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Browse the Internet in a safer fashion. Download Web of Trust: http://www.mywot.com/ Cross-platform mobile application development from MoSync: http://www.mosync.com/

3 2

Re: [Maria-discuss] [Maria-developers] Problem with MariaDb as a Windows service
by Peter Laursen 25 Nov '10

25 Nov '10

Same problem with MariaDB 5.1.51. --Peter On Sun, Nov 21, 2010 at 02:43, Vladislav Vaintroub < vladislav.vaintroub(a)oracle.com> wrote: > Hi, > It is likely this bug http://bugs.mysql.com/bug.php?id=56821 , was for > some > time in MySQL, already fixed. > > -----Original Message----- > From: maria-developers-bounces+wlad=sun.com(a)lists.launchpad.net > [mailto:maria-developers-bounces+wlad <maria-developers-bounces%2Bwlad>= > sun.com(a)lists.launchpad.net] On Behalf > Of Philip Stoev > Sent: Samstag, 20. November 2010 20:04 > To: Peter Laursen > Cc: maria-discuss(a)lists.launchpad.net; > maria-developers(a)lists.launchpad.net > Subject: Re: [Maria-developers] [Maria-discuss] Problem with MariaDb as a > Windows service > > Hi, > > It seems that the service requires the --console option to operate > properly. > > Here is how I could make it work: > > 1. In the Services Control panel, open up the properties for the service 2. > In the "Start Parameters" box, type "--console" without the quotation marks > 3. In the same dialog box, hit Start > > Philip Stoev > > ----- Original Message ----- > From: "Peter Laursen" <peter_laursen(a)webyog.com> > To: <maria-discuss(a)lists.launchpad.net>; > <maria-developers(a)lists.launchpad.net> > Sent: Saturday, November 20, 2010 3:36 PM > Subject: [Maria-discuss] Problem with MariaDb as a Windows service > > > > Some person claimed here around one week ago that MariaDB would not > > run as service after "mysqld --install". > > I replied that it worked for me - but it does not with the latest > > stable version 5.2.3 (it did with the 5.2.3 RC) (so my apology to this > > person!) > > > > Windows console output: > > > > C:\maria52\bin>sc delete maria52 > > [SC] DeleteService LYKKEDES > > > > C:\maria52\bin>mysqld --install maria52 Service successfully > > installed. > > > > C:\maria52\bin>net start maria52 > > Tjenesten maria52 starter... > > Tjenesten maria52 kunne ikke startes. > > > > Der opstod en systemfejl. > > > > Systemfejlen 1067 opstod. > > > > (From Danish --> service could not start --> the system error 1067 > > occurred) > > > > I have tried with both old datadir and the empty one shipped with > > mariaDB and also my old my.ini as well as a fresh one. > > Also I tried to add --defaults-dir to the registry (just in case I had > > overlooked a my.ini copy in C:\ or C:\Windows) and adding doublequotes > > key manually like "C:\maria52\bin\mysqld" > > --defaults-file="C:\maria52\my.ini" maria52 > > > > I double checked there are no conflicts wit all settings. > > > > The command > > "C:\maria52\bin\mysqld" --defaults-file="C:\maria52\my.ini" > > .. starts the server (also when --defaults-file is omitted) .. but not > > as a service of course. > > > > > > Was it tested with the 5.2.3 GA release that it will run as a Windows > > service? > > The RC worked as a charm - but the GA will not. Any idea? > > (I am using Win7, 64 bit, Home Premium) > > > > > > Peter > > (Webyog) > > > > > > ---------------------------------------------------------------------------- > ---- > > > > _______________________________________________ > > Mailing list: https://launchpad.net/~maria-discuss > > Post to : maria-discuss(a)lists.launchpad.net > > Unsubscribe : https://launchpad.net/~maria-discuss > > More help : https://help.launchpad.net/ListHelp > > > > > _______________________________________________ > Mailing list: https://launchpad.net/~maria-developers > Post to : maria-developers(a)lists.launchpad.net > Unsubscribe : https://launchpad.net/~maria-developers > More help : https://help.launchpad.net/ListHelp > >

3 2

Re: [Maria-discuss] [Maria-developers] Problem with MariaDb as a Windows service
by Alex Budovski 24 Nov '10

24 Nov '10

> Right, this was subsequently fixed in MySQL in > http://lists.mysql.com/commits/118824 > Yes, I noticed that too late.

1 0

[Maria-discuss] Problem with MariaDb as a Windows service
by Peter Laursen 20 Nov '10

20 Nov '10

Some person claimed here around one week ago that MariaDB would not run as service after "mysqld --install". I replied that it worked for me - but it does not with the latest stable version 5.2.3 (it did with the 5.2.3 RC) (so my apology to this person!) Windows console output: C:\maria52\bin>sc delete maria52 [SC] DeleteService LYKKEDES C:\maria52\bin>mysqld --install maria52 Service successfully installed. C:\maria52\bin>net start maria52 Tjenesten maria52 starter... Tjenesten maria52 kunne ikke startes. Der opstod en systemfejl. Systemfejlen 1067 opstod. (From Danish --> service could not start --> the system error 1067 occurred) I have tried with both old datadir and the empty one shipped with mariaDB and also my old my.ini as well as a fresh one. Also I tried to add --defaults-dir to the registry (just in case I had overlooked a my.ini copy in C:\ or C:\Windows) and adding doublequotes key manually like "C:\maria52\bin\mysqld" --defaults-file="C:\maria52\my.ini" maria52 I double checked there are no conflicts wit all settings. The command "C:\maria52\bin\mysqld" --defaults-file="C:\maria52\my.ini" .. starts the server (also when --defaults-file is omitted) .. but not as a service of course. Was it tested with the 5.2.3 GA release that it will run as a Windows service? The RC worked as a charm - but the GA will not. Any idea? (I am using Win7, 64 bit, Home Premium) Peter (Webyog)

2 6

[Maria-discuss] virtual columns
by Federico Razzoli 13 Nov '10

13 Nov '10

Hello I've read in the documentation that any legal SQL expression SHOULD be allowed in the Virtual Columns definition. I'm trying to use a (deterministic) stored function, but I get an error: ERROR 1642 (HY000): Function or expression is not allowed for column 'vc1' Here's my create table: CREATE TABLE t1 (c1 int, c2 int, vc1 INT AS (c1+functest()) VIRTUAL); Here's how functest() is defined: CREATE FUNCTION functest() RETURNS TINYINT DETERMINISTIC BEGIN UPDATE t2 SET c1 = c1 + 1; RETURN 0; END I think this clearly explains what I am trying to do: a function which logs accesses to a certain column. Why do I get error 1642? Is the bug in the documentation or in the software? Will future MariaDB versions allow us to create virtual columns which call stored functions? Federico

5 6

[Maria-discuss] Install as service on Windows 7 64x
by Francisco Ernesto Teixeira 11 Nov '10

11 Nov '10

Hello, First I have to say that it's a great idea. I looked for replace my MySQL servers to MariaDB, but I can't loose everything. First, when I run the downloaded MariaDB 5.2.3 as "C:\Program Files (x86)\MariaDB 5.2.3\bin\mysqld.exe" --standalone everything is allrigth. When I follow these steps on Prompt: 1. "C:\Program Files (x86)\MariaDB 5.2.3\bin\mysqld.exe" --install 2. net start mysql Says: O serviço de MySQL está sendo iniciado..... O serviço de MySQL não pôde ser iniciado. Erro de sistema. Erro de sistema 1067. O processo foi finalizado de forma inesperada. BUT, don't exists error log on Data folder or log!!! Thank you.

2 1

[Maria-discuss] Non-root install of MariaDB
by Arun Jagatheesan 05 Nov '10

05 Nov '10

Hi, Is it mandatory to be a privileged user for MariaDB? (hope not so). Is there an existing documentation or any suggestions for a non-root instal of MariaDB on Linux (CentOS)? Thanks, Arun

2 2

Re: [Maria-discuss] How to lock individual tables in 1-db-per-file binlogging (Was: Re: Per-db binlogging)
by Weldon Whipple 28 Oct '10

28 Oct '10

Dmitry, Thank you very much for your response! (I apologize for the slowness of my reply. I've been fighting a cold for several days.) See below: On Thu, Oct 21, 2010 at 11:15 PM, Dmitry Lenev <Dmitry.Lenev(a)oracle.com> wrote: > Hello Weldon! > > * Weldon Whipple <weldon(a)whipple.org> [10/10/20 00:26]: >> Thanks for the great feedback. It got me looking at the capabilities >> of MySQL 5.5. >> >> It's POSSIBLE that the new syntax: >> >> FLUSH TABLES t1, t2, t3, ... , tn WITH READ LOCK >> >> is PRECISELY the functionality we need! >> >> Our (perl) migration script MIGHT (for example) do the following: >> >> Issue the following command (implemented by me ...): >> >> 1. START BINLOG FOR DATABASE <db>; >> >> (for each database a given user owns. [Hmmm... Maybe my implementation >> allows multiple <db> arguments. I need to check.] Each database would >> start binlogging to its own file. [We will configure--in my.cnf--the >> 1-database-per-file binlogs to be stored on a very fast solid-state >> drive.]) >> >> Then the script creates a list of all tables in all the user's >> databases being migrated to another server and issues the command (new >> to MySQL 5.5): >> >> 2. FLUSH TABLES t1, t2, t3, ..., tn WITH READ LOCK; >> >> (where the tables t1, t2, ..., tn are the tables being migrated.) >> >> Then issue the following command (implemented by me ...): > > AFAIU you also want to prohibit creation of tables in this database > after this point as otherwise you can't guarantee binlog stability. > Also what about non-table objects like stored routines - don't you > want to block their modification as well? The above statement won't > provide this as it locks only *existing* *tables*. > > OTOH in my opinion it should be fairly easy to implement functionality > that you need (i.e. blocking of all DDL and DML that changes data in > database) on the basis of FLUSH TABLES <table_list> WITH READ LOCK > implementation. You just need to take database-scope S metadata lock > in addition to locking and flushing all tables in database. Such lock > will block creation of new tables and any DDL on stored routines in > the database. Maybe I need to implement a new command (something like FLUSH DATABASE db WITH READ LOCK) that--conceptually, at least--read locks all the tables in the database (like FLUSH TABLES ... WITH READ LOCK) *as well as* blocks all DDL and DML that changes data in database. I'll look through the code and find where FLUSH TABLES ... WITH READ LOCK is implemented. If I can't figure out how to "take database-scope S metadata lock," I'll get back with you... (I suppose I'll need a corresponding UNLOCK DATABASES command as well.) I've observed (and read) that the read locks are held as long as the client connection remains open, and that the UNLOCK applies to all the locks created during the same connection that created the read locks. I guess that means that our migration tool will need to maintain the connection with mysqld while it dumps (or scp's, in the case of MyISAM tables) the database snapshot. (That shouldn't necessarily be a problem--it's just something we need to be aware of. It makes sense for it to work that way.) > >> >> BUG IDENTIFIED. >> <snip/> > I have taken the liberty to report this issue as bug #57649 > (http://bugs.mysql.com/bug.php?id=57649) > > Thanks for pointing it out! Thanks for reporting it. > >> >> QUESTION: >> I read that Larry Ellison stated (at a conference) that 5.5 would be >> out by year end. How likely is that? Our company generally waits for >> some point releases before changing our customer boxes to the next >> release. >> >> Should I try to back port those locks to 5.1 in the interim? ... or >> leave well enough alone? > > I don't think it makes sense to backport metadata locks to 5.1 as it is > likely to require back-porting of too much other changes from 5.5. > > OTOH implementing what you need on top of infrastructure already present > in 5.1 is likely to require significant efforts as well. > > So I think it is better to base your work on 5.5 tree. I agree. There are quite a few major changes in 5.5. I haven't noticed meta-data-locks (MDL) before. The PSI additions to the mutex and conditions also look new. (I'm sure I'm missing lots of other changes.) (Regarding the PSI_mutex_key, PSI_rwlock_key and similar additions: I've tried to follow the patterns in 5.5 as I added my own mutexes. I'm assuming [hoping?] that they work when multiple instances of an object all refer to the same-named PSI_xxx-key ??? ...) (Is there any externally available documentation on MySQL's use of PSI?) > > Best regards, > -- > Dmitry Lenev, Software Developer > Oracle Development SPB/MySQL, www.mysql.com > > Are you MySQL certified? http://www.mysql.com/certification Not that I know of. Weldon Whipple weldon(a)whipple.org

2 2

Re: [Maria-discuss] italian translation
by Bryan Alsdorf 25 Oct '10

25 Oct '10

Hi, On 09/14/2010 11:24 PM, Colin Charles wrote: > Hi! > > On 12 Sep 2010, at 07:51, Federico Razzoli wrote: > >> Hello. >> >> I appreciate so much your new Knowledge Base. The pages that are already on line >> are very clear and complete, as the MySQL docs. But, unlike the MySQL docs, the >> Knowledge Base has free licenses. For these reasons, I would like to translate >> into italian - which is my natural language. >> >> Am I allowed to do that? As far as I understand, both the licenses used (CC >> BY-SA and GNU FDL) allow translations, as long as they have the same licenses >> used by the original work. But I want to be sure before starting. >> >> I can't translate all the articles, so I'll only translate some of the most >> interesting documents. But, as the documentation grows, maybe I may try to set >> up a group of volounteers. We'll see. > > > You are absolutely allowed to do that. You can translate it to Italian with no issue at all > > Currently (and I think, by design) it seems that kb.askmonty.org will be in the English language. Bryan (CC'ed in this email) is the right person to help setup an instance for the Italian language (something like it.kb.askmonty.org) if you were planning on making a larger translation project. It would be great if you could harness a group of volunteers We actually want to setup a structure to support translations inside our KB, I just haven't had the time to design or implement it. My two concerns are how to properly display content in another language and how to keep translated articles up to date when the English version is updated (or vice versa). If you have any ideas, please let me know. > In the meantime however, as you'd like to just translate interesting documents, just go ahead, translate them, and it would rock if you'd point folk to the English version at the askmonty KB > > Good luck with your translation work and tell us if you need any help! Yes please let us know if there is anything we can do to help. I will try to work on supporting other languages soon since people are actually translating articles. Best Regards, -- Bryan Alsdorf, Lead Web Developer Monty Program AB. http://montyprogram.com

4 4

Re: [Maria-discuss] How to lock individual tables in 1-db-per-file binlogging (Was: Re: Per-db binlogging)
by Weldon Whipple 19 Oct '10

19 Oct '10

Davi, Sergei (and everyone else), Thanks for the great feedback. It got me looking at the capabilities of MySQL 5.5. It's POSSIBLE that the new syntax: FLUSH TABLES t1, t2, t3, ... , tn WITH READ LOCK is PRECISELY the functionality we need! Our (perl) migration script MIGHT (for example) do the following: Issue the following command (implemented by me ...): 1. START BINLOG FOR DATABASE <db>; (for each database a given user owns. [Hmmm... Maybe my implementation allows multiple <db> arguments. I need to check.] Each database would start binlogging to its own file. [We will configure--in my.cnf--the 1-database-per-file binlogs to be stored on a very fast solid-state drive.]) Then the script creates a list of all tables in all the user's databases being migrated to another server and issues the command (new to MySQL 5.5): 2. FLUSH TABLES t1, t2, t3, ..., tn WITH READ LOCK; (where the tables t1, t2, ..., tn are the tables being migrated.) Then issue the following command (implemented by me ...): 3. SHOW DATABASE BINLOG STATUS; which shows--for each database being binlogged to its own file: a. the database name b. whether binlogging is active c. the name of the current binlog file d. the binlog position in that file. The script captures the coordinates, then 4. Creates a copy of the database(s) using mysqldump or scp/tar/gzip, etc. (MyISAM databases will probably just be copied "raw," because it is much faster. Innodb will probably have to use mysqldump or something similar.) 5. UNLOCK TABLES: On the new server, the copied/mysqldump'ed files will be used to bring up the mysql on the new server. At some future time--hopefully SOON--the tables could be locked again. For any where the coordinates have changed: The binlog file(s) would be copied to the new server, "played" by mysqlbinlog, and the new server would be brought online on the new box and taken offline on the old. When the migration is determined to be complete, issue this command (implemented by me) on the old box: 6. STOP BINLOG FOR DATABASE <db>; for all the user's databases ... and the migration is done. QUESTIONS/ASSUMPTIONS: 1. In playing with the new FLUSH TABLES t1, t2, ..., tn command, I've noticed that if the connection drops, the locks are all unlocked. ... So our scripts will have to make sure the connection stays up. (ANY SUGGESTIONS?) 2. The UNLOCK TABLES command unlocks all the tables locked earlier in the same session. (So it doesn't need the variable argument list of tables to unlock ...) A post on dev.mysql.com shows the following possible sequence of commands: FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; SYSTEM xfs_freeze -f /var/lib/mysql; SYSTEM YOUR_SCRIPT_TO_CREATE_SNAPSHOT.sh; SYSTEM xfs_freeze -u /var/lib/mysql; UNLOCK TABLES; EXIT; (The FLUSH TABLES command would correspond to #2 above. SHOW MASTER STATUS would correspond to #3 above. UNLOCK TABLES would correspond to #5 above. The SYSTEM commands would have to be replaced by our own commands/scripts.) Would running (sub-)scripts from SYSTEM commands inside MySQL be sufficient to hold the locks? BUG IDENTIFIED. I noticed the following mysqld-crashing bug--in 5.5.6 rc as well as the one I modified: 1. FLUSH TABLES t1, t2, t3, ..., tn WITH READ LOCK; 2. FLUSH TABLES; The second FLUSH TABLES (with no added tokens) crashes at mdl.cc:1563 : DBUG_ASSERT(mdl_request->type != MDL_EXCLUSIVE || is_lock_owner(MDL_key::GLOBAL, "", "", MDL_INTENTION_EXCLUSIVE)); because is_lock_owner() returns NULL when FLUSH TABLES is called immediately after FLUSH TABLES [with additional args]; QUESTION: I read that Larry Ellison stated (at a conference) that 5.5 would be out by year end. How likely is that? Our company generally waits for some point releases before changing our customer boxes to the next release. Should I try to back port those locks to 5.1 in the interim? ... or leave well enough alone? Thanks in advance for your feedback/help/advice!! We.don Whipple On Mon, Oct 18, 2010 at 12:48 PM, Davi Arnaut <davi.arnaut(a)oracle.com> wrote: > On 10/18/10 3:16 PM, Weldon Whipple wrote: >> >> Well, I finished a working prototype that does everything except the >> locking and unlocking of databases. Someone mentioned that it's now >> possible to lock individual tables. Would you--anyone--be willing to >> point me toward places in the 5.5 code that use that >> locking/unlocking? >> > > Do you want to lock the database, or individual tables, or both? Anyway, > take a look at mysql_rm_db as the locking is all very similar. Roughly, the > algorithm of a DROP DATABASE is: > > // Protect against other schema operations > lock_schema_name("db1"); > // Build list of tables in the db1 directory > table_list = .. > // Initialize metadata lock request > table_list->mdl_request.init(MDL_key::TABLE, > table_list->db, > table_list->table_name, MDL_EXCLUSIVE); > // Lock tables > lock_table_names(thd, tables, NULL ...) > // Remove from cache and drop the table... > > The best thing at this point is to study (and debug) a similar statement and > understand how metadata locks work (mdl prefix) or how tables are > open/locked. > > Regards, > > Davi > > -- > MySQL Internals Mailing List > For list archives: http://lists.mysql.com/internals > To unsubscribe: http://lists.mysql.com/internals?unsub=weldon@whipple.org > >

1 0

Re: [Maria-discuss] How to lock individual tables in 1-db-per-file binlogging (Was: Re: Per-db binlogging)
by Weldon Whipple 18 Oct '10

18 Oct '10

Davi, Thanks for the pointers! I'd like to lock an entire database. (I guess I can iterate through the tables in database if that's the only way. [I suppose there might be possible locking issues to deal with ...]) I'll look at the source you recommend ... Weldon weldon(a)whipple.org On Mon, Oct 18, 2010 at 12:48 PM, Davi Arnaut <davi.arnaut(a)oracle.com> wrote: > On 10/18/10 3:16 PM, Weldon Whipple wrote: >> >> Well, I finished a working prototype that does everything except the >> locking and unlocking of databases. Someone mentioned that it's now >> possible to lock individual tables. Would you--anyone--be willing to >> point me toward places in the 5.5 code that use that >> locking/unlocking? >> > > Do you want to lock the database, or individual tables, or both? Anyway, > take a look at mysql_rm_db as the locking is all very similar. Roughly, the > algorithm of a DROP DATABASE is: > > // Protect against other schema operations > lock_schema_name("db1"); > // Build list of tables in the db1 directory > table_list = .. > // Initialize metadata lock request > table_list->mdl_request.init(MDL_key::TABLE, > table_list->db, > table_list->table_name, MDL_EXCLUSIVE); > // Lock tables > lock_table_names(thd, tables, NULL ...) > // Remove from cache and drop the table... > > The best thing at this point is to study (and debug) a similar statement and > understand how metadata locks work (mdl prefix) or how tables are > open/locked. > > Regards, > > Davi > > -- > MySQL Internals Mailing List > For list archives: http://lists.mysql.com/internals > To unsubscribe: http://lists.mysql.com/internals?unsub=weldon@whipple.org > >

1 0

[Maria-discuss] How to lock individual tables in 1-db-per-file binlogging (Was: Re: Per-db binlogging)
by Weldon Whipple 18 Oct '10

18 Oct '10

Thanks, Sergei and everyone else, for your feedback/ideas! Well, I finished a working prototype that does everything except the locking and unlocking of databases. Someone mentioned that it's now possible to lock individual tables. Would you--anyone--be willing to point me toward places in the 5.5 code that use that locking/unlocking? I'm interested in sharing the changes with the community if there is any interest. (Any suggestions about whether I should prefer launchpad versus forge.mysql.com or some other place to share? [I once owned a project on SourceForge--I think it's still there, but not very active ...]. :-) A few comments in response to your comments: On Thu, Sep 16, 2010 at 6:34 AM, Sergei Golubchik <serg(a)askmonty.org> wrote: > Hi, Weldon! > > On Sep 15, Weldon Whipple wrote: >> >> > See where mysql_bin_log.write is called. >> >> Excellent information! (I was hoping it might be that easy ???). If I >> succeed in making a DB_BIN_LOG class derive from MYSQL_BIN_LOG (with an >> instance of DB_BIN_LOG for each database that is currently binlogging), >> I could--in theory, at least--just call something like >> db_binlog->write () at those places. > > Better make this write method virtual and implement it in your derived > class. I did implement it as a derived class, but it was a bit more work than just reimplementing the ::write() method. (I use separate instances of DB_BINLOG--the derived class--for each database that is binlogging to its separate binlog file [sequence].) I had to have a few additional methods (extremely small) to set up for opening files named <database>-bin.<seq#>, <database>-bin.index, etc. I also maintain a db_binlog.info file that remembers the databases being binlogged across restarts. One major change was in the way the global MYSQL_BIN_LOG class is referenced. In the original code, it comes into existence at the top of binlog.cc. Before I started making my modifications, I changed MYSQL_BIN_LOG to be a pointer to MYSQL_BIN_LOG, declared in mysqld.cc (and extern'ed in mysqld.h). At the point in mysqld.cc where mysql_bin_log is first referenced I "new" the global instance of MYSQL_BIN_LOG*. I went through all the files that reference mysql_bin_log and made the necessary changes so that mysql_bin_log works as a pointer. (Example: Change "mysql_bin_log.is_active()" to "mysql_bin_log->is_active().) Also, before, referencing mysql_bin_log*, I always make sure it is non-NULL ... Then I made my changes for 1 db-per-file binlogging, using #ifdef HAVE_DB_BINLOG/#endif blocks to allow building with or without 1-db-per-file binlogging. As I'm typing this post, I'm running mysql-test-run.pl on a build without HAVE_DB_BINLOG defined and it seems to be passing all the tests. I need to write additional test cases for when HAVE_DB_BINLOG is defined ... Thanks in advance for ideas/suggestions (as well as for the ideas in the past). Weldon Whipple weldon(a)whipple.org (I'll leave the conversations below to help refresh your memory on this thread's past.) > >> >> Questions I Have. >> >> ---------------- >> >> >> >> 1. I notice that binlog is in the list of engines and plugins >> >> (displayed by SHOW ...). The bottom of log.cc has >> >> "mysql_declare_plugin(binlog)". Do I need one for db_plugin? >> >> (probably not?) >> > >> > depends. >> > binlog needs to be declated a plugin, to be able to pretent being a >> > storage engine, to be able to force two phase commit (2PC). >> > >> > 2PC is used to commit or rollback a transaction atomically in more >> > than one engine. And MySQL only uses 2PC when a transaction spans >> > more than one engine. But we want a transaction to be committed in >> > the engine and written to a binlog - and it should be done >> > atomically too. So, binlog pretends to be an engine, pretends to >> > participate in a transaction, which forces MySQL to use 2PC which >> > can guarantee that a transaction is either committed and written to >> > a binlog - or not committed and not written. Reliably, even if MySQL >> > crashes in the middle. >> > >> > So, if you don't use innodb, or don't use >> > innodb_flush_log_at_trx_commit=1 or don't use sync_binlog=1 you >> > probably don't need to support 2PC as your setup is not crash proof >> > anyway. >> > >> > If you care about innodb<->binlog consistency in the presence of >> > crashes, you should support 2PC in your binlog, as explained above. >> >> We DO have a small percentage of innodb databases. If 2PC commits >> (rolls back, etc.) atomically to more than one engine, does that mean >> TWO (only)? ... or could it happen for three (specifically, innodb, >> MYSQL_BIN_LOG's binlog, and DB_BIN_LOG's binlog)? > > See http://en.wikipedia.org/wiki/Two-phase_commit_protocol > > It's two phases, not two engines, any number of engines can participate > in a transaction. > > But! All of them but one have to support two-phase commit. Binlog does > not support it. Apparently, DB_BIN_LOG will not support it either - > adding two-phase commit support to binlog will surely be beyond the > scope of your work. > > It means, you cannot have InnoDB, binlog, and DB_BIN_LOG to participate > in a two-phase commit. But you can disable MYSQL_BIN_LOG altogether and > only use DB_BIN_LOG - in that case you can have a two-phase commit > between DB_BIN_LOG and InnoDB. > >> The two kinds of binlog files would have the same format (except that >> one is for only one database). Are they considered two storage >> engines/plugins? > > If you want to write to both - yes. A transaction will need to be > written to both independently, so you want them to be in sync, and MySQL > uses 2PC to synchronize different data storages. > > You can solve that by > > 1) not using MYSQL_BIN_LOG. In this case there's only InnoDB and > DB_BIN_LOG to keep in sync, and InnoDB can do 2PC. > > 2) not making DB_BIN_LOG independent from MYSQL_BIN_LOG. For example, > everything is written to binlog only. Then you have a background > thread (or even a separate process) that reads binlog and writes your > per-db binlogs. In that case you have only InnoDB and MYSQL_BIN_LOG > to keep in sync, and MySQL already does that. Your background thread > is only catching up, it is allowed to fall behind. > >> (I notice that the "mysql_declare_plugin(binlog)" declaration is at >> the bottom of log.cc. Most of the other storage engines' corresponding >> declarations are in files in the storage subdirectories. If I were to >> need a "mysql_declare_plugin(db_binlog)" declaration, could it also go >> at the bottom of log.cc? ... Or (since a DB_BIN_LOG is AKO [a kind of] >> MYSQL_BIN_LOG [in the OO sense]), are MYSQL_BIN_LOGs and DB_BIN_LOGs >> both kinds of binlog plugins ...? > > if you want to have two plugins in one file you still need to use one > mysql_declare_plugin(), not two. See example at the end of > http://www.packtpub.com/article/look-inside-mysql-daemon-plugin > > Regards, > Sergei >

1 0

[Maria-discuss] WL #47
by Oleg Tsarev 13 Oct '10

13 Oct '10

Hello Maria! https://code.launchpad.net/~percona-dev/percona-server/release-5.1.49-12-rnt When i build this branch: cd Percona-Server/ && ./BUILD/autorun.sh && ./configure --prefix=/usr/local/mysql --exec-prefix=/usr/local/mysql --bindir=/usr/local/mysql/bin --sbindir=/usr/local/mysql/bin --sysconfdir=/etc --datadir=/usr/local/mysql/share --includedir=/usr/local/mysql/include --libdir=/usr/local/mysql/lib --libexecdir=/usr/local/mysql/bin --with-client-ldflags=-all-static --localstatedir=/var/lib/mysql --sharedstatedir=/usr/local/mysql/com --mandir=/usr/local/mysql/man --infodir=/usr/local/mysql/share/info --enable-shared --enable-static --enable-assembler --enable-local-infile --with-mysqld-user=mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock --with-pic --with-extra-charsets=complex --with-ssl --enable-thread-safe-client --with-readline --without-plugin-ndbcluster --without-plugin-maria --without-plugin-innobase --with-plugin-innodb_plugin --with-plugin-partition --with-plugin-csv --with-plugin-archive --with-plugin-blackhole --with-plugin-federated --without-embedded-server --without-bench --with-zlib-dir=bundled --with-big-tables && make -j3 And then run tests: cd mysql-test && ./mysql-test-run main.ctype_cp932_binlog_stm I get test error: worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009 main.ctype_cp932_binlog_stm [ fail ] Found warnings/errors in server log file! Test ended at 2010-10-13 08:02:25 line 101013 18:02:25 [ERROR] Error in Log_event::read_log_event(): 'Event too big', data_len: 50724864, event_type: 0 ^ Found warnings in /mnt/data/home/oleg.tsarev/12687/Percona-Server/mysql-test/var/log/mysqld.1.err ok - saving '/mnt/data/home/oleg.tsarev/12687/Percona-Server/mysql-test/var/log/main.ctype_cp932_binlog_stm/' to '/mnt/data/home/oleg.tsarev/12687/Percona-Server/mysql-test/var/log/main.ctype_cp932_binlog_stm/' ------------------------------------------------------------ The servers were restarted 0 times Spent 0.000 of 1 seconds executing testcases mysql-test-run: WARNING: Got errors/warnings while running tests, please examine '/mnt/data/home/oleg.tsarev/12687/Percona-Server/mysql-test/var/log/warnings' for details. Completed: Failed 1/1 tests, 0.00% were successful. Failing test(s): main.ctype_cp932_binlog_stm I found, what reason of this bug - our patch wl47.patch. I try to fix this bug, but i know replication not enough for this. Do you know about this trouble with WL #47? Best regards, Oleg --- Oleg Tsarev, Software Engineer, Percona Inc.

1 0

[Maria-discuss] improving the thread/connection pool
by MARK CALLAGHAN 11 Oct '10

11 Oct '10

I think more work needs to be done on the MariaDB thread/connection pool. Does a worklog exist for that? The problems are: * multi-core performance * stalled threads stall the pool This is based on my review and testing of the feature in MySQL 6.0 and quickly browsing the code in MariaDB. I also fixed multi-core performance as a proof of concept but don't have access to that patch. The multi-core performance problem occurs because only one event_base instance is used (see sql/scheduler.cc). Note that memcache uses one per CPU core. This is a big source of mutex contention. An additional problem is that EPOLLONESHOT is not used. I don't know if libevent supports that yet. Were it used one less interaction with libevent would be needed per query. I reported the performance issue at http://bugs.mysql.com/bug.php?id=42288 An interesting decision when there are multiple event_base instances is whether the mapping between an event_base instance and connections is fixed. If it is fixed then there is less libevent work to do per-query and there will be less mutex contention. But if connections cannot migrate between event_base instances per query then there will be cases where too many pending queries wait for one and no pending queries exist for others. I think that stalls are another problem to fix. When a user connection blocks on a row lock wait or another internal locks (and calls THD::enter_cond), then this is an opportunity to count this connection as not running and schedule another thread. This isn't a trivial problem to fix because it might require a new thread to be created. There is also the issue of what to do when another connection has been scheduled. In that case does the thread that just became runnable get to run immediately? If it was just granted a row lock or other lock then we want it to run right away. But that might cause X+1 threads to be running in the thread pool when the limit is X. The difference between X and X+1 is small. But this could lead to 2*X threads running. What is the limit? -- Mark Callaghan mdcallag(a)gmail.com

1 0

[Maria-discuss] MariaDB 5.2.2-gamma
by Daniel Bartholomew 29 Sep '10

29 Sep '10

All, MariaDB 5.2.2-gamma source and binary tarballs, and packages for various operating systems and Linux distributions are now available for download: http://askmonty.org/wiki/MariaDB:Download:MariaDB_5.2.2-gamma See also: Release Notes: http://kb.askmonty.org/v/mariadb-522-release-notes Changelog: http://kb.askmonty.org/v/mariadb-522-changelog Thanks. -- Daniel Bartholomew Monty Program - http://askmonty.org

3 3

[Maria-discuss] MariaDB Knowledgebase typo
by Yousif Masoud 16 Sep '10

16 Sep '10

Hi, I'd like to thank everyone working on this project. MariaDB is currently installed on one of my Dev environments, I am looking forward to switching to it. I noticed there was a minor typo on: http://kb.askmonty.org/v/compiling-with-the-innodb-plugin-from-oracle First sentence reads: Normally MariaDB comes with XtraDB <http://kb.askmonty.org/v/xtradb-storage-engine>, an enhanced version **if** the InnoDB plugin that comes from Oracle. I believe it should be: Normally MariaDB comes with XtraDB <http://kb.askmonty.org/v/xtradb-storage-engine>, an enhanced version **of** the InnoDB plugin that comes from Oracle. Many thanks, Yousif PS. I don't know if there is a formal method for reporting this. Please accept my apologies if this is not the best way to do it.

2 1

Re: [Maria-discuss] Seeking command syntax feedback (Still Re: Per-db binlogging)
by Weldon Whipple 15 Sep '10

15 Sep '10

On Wed, Sep 15, 2010 at 2:00 PM, Sergei Golubchik <serg(a)askmonty.org> wrote: > Hi, Weldon! Hi, Sergei! > > On Sep 15, Weldon Whipple wrote: <snip/> >> 2. FLUSH TABLES WITH READ LOCK FOR DATABASE dbname; >> >> (I just added an optional 'FOR DATABASE dbname' to the already >> existing command--which still works, BTW. Is this a misuse of the word >> "FOR"?) >> This is the command that locks the tables before someone (some >> program) dumps/copies the tables that will be moved to another server. >> It flushes and locks only one database. > > This would be difficult to implement. > > FLUSH TABLES WITH READ LOCK is not implemented in a way that can be > generalized to support per-database locks :( I had guessed as much. (The method in my DB_BINLOG_MGR method that implements that functionality consists of a mutex lock, a comment about some sort of miracle happening, then a mutex unlock.) I wonder if there might be a way of inhibiting some user database (temporarily) from changing ... Alternately, I suppose we could do a global FLUSH TABLE WITH READ LOCK/ UNLOCK TABLES. Or ... (???) > Besides, I personally wouldn't worry much about the syntax. It's > something you can easily change anytime - e.g. replace BINLOG with > BINLOGGING or FOR with something else. Yeah, every time I write a new prototype I tweak the syntax a bit. At the moment I'm just trying to settle on an implementation that would work for our company (and still be of some sort of general use). > > Regards, > Sergei > Thanks, Weldon

1 0

Re: [Maria-discuss] Per-db binlogging
by MARK CALLAGHAN 15 Sep '10

15 Sep '10

+maria-discuss as they are working in replication too +robert.hodges as tungsten replicator has features that could be used here I am very interested in a subset of this. I want one SQL thread per database on a slave (stream per database on the slave). However I don't need to split the binlog on the master to get that and writing a binlog file per database on the master might complicate the sync_binlog option that keeps the binlog and InnoDB in sync during crash recovery. How will this work in that case? Note that a stream (SQL slave thread) per database on the slave will break rpl_transaction_enabled. Some of us rely on that to keep slaves consistent during crash recovery. I assume you don't use that feature. MySQL has worklogs to use db tables in place of relay-log.info and when that code is production ready, then this is not a problem. On Mon, Sep 13, 2010 at 1:52 PM, Weldon Whipple <weldon(a)whipple.org> wrote: > Greetings! > > My employer has asked me to implement per-DB binlogging for > MySQL. I've been working on prototypes for 3-4 weeks now, and decided > it's time to post to this list to ask for comments, suggestions, > condolences, etc. (I've been a "lurker" for quite awhile. I hope this > post isn't too far off the mark) The assignment is *only* for master-side > binlogging. (It doesn't require a slave/replication-side implementation.) > > > Why I Want to Do It. > ------------------- > > I work for an ISP that hosts (last I heard--I HOPE I'm not lying) over > 2 million domains. Customers are allowed to have (almost) unlimited > MySQL databases. Migrating an account from one physical server to > another is a daunting task. (A typical server has easily 2000+ > [sometimes far more] databases.) Global mysqld binlogging isn't > feasible when we want to migrate (for example) a single account to > another server. > > Our proposed scenario goes something like this: > > 1. START USER BINLOG FOR DATABASE <db> > 2. FLUSH USER TABLES WITH READ LOCK FOR DATABASE <db> > 3. SHOW USER BINLOG STATUS [WHERE DATABASE LIKE <pattern>] > > SHOW output might look something like: > ================================= > mysql> show user binlog status; > +----------------------+----------+---------------------------------+------------+ > | Database | User | Binlog_file | > Binlog_pos | > +----------------------+----------+---------------------------------+------------+ > | booklibs | wwhipple | booklibs-bin.000003 | > 248 | > | charmins_gravestones | charmins | charmins_gravestones-bin.000001 | > 4 | > +----------------------+----------+---------------------------------+------------+ > 2 rows in set (0.01 sec) > > The "where" clause also works: > > mysql> show user binlog status where user like 'charmins'; > +----------------------+----------+---------------------------------+------------+ > | Database | User | Binlog_file | > Binlog_pos | > +----------------------+----------+---------------------------------+------------+ > | charmins_gravestones | charmins | charmins_gravestones-bin.000001 | > 4 | > +----------------------+----------+---------------------------------+------------+ > 1 row in set (0.01 sec) > ================================= > > At this point the migration tools note the binlog coordinates and > start copying the databases to the new server. (For myisam tables, the > tools writers want to just copy [scp?] the files. For innodb, they > will use something like mysqldump.) > > (Note: We might want to "lose" the "User" column in the above table] > in our recent designs.) > > Then the sequence of MySQL commands resumes: > > 4. UNLOCK USER BINLOG FOR DATABASE <db> > > Finally, when migration is complete: > > 5. STOP USER BINLOG FOR DATABASE <db> > > A high percentage of users (infrequent bloggers, etc.) go several days > (sometimes even weeks or months) without updating their databases. For > them--after migrating the files to the new server, a final check of > the binlog coordinates on the old box can verify that nothing has > changed. > > If it HAS changed, migration can copy the binlog file(s) to the new > box and use the mysqlbinlog command (specifying the starting > coordinates) to bring the new database in sync with the old one. > > (This scenario is probably oversimplified, ignoring things like DNS > caching, TTL, etc., etc. Hopefully it doesn't include too many lies.) > > > How I Plan to Do It. > ------------------- > > I've noticed that: > > 1. The class MYSQL_BIN_LOG seems to be involved in most binlogging > 2. MYSQL_BIN_LOG inherits from TC_LOG and MYSQL_LOG. It is created in > mysqld.cc at startup. > > My current partial implementation has: > > 1. Class DB_BIN_LOG inheriting from MYSQL_BIN_LOG, adding members like > db_name > db_binlog_name > db_index_name > and methods to access them, etc. > > 2. Singleton class USER_BIN_LOG_MGR. (Should probably be DB_BIN_LOG_MGR), > which maintains a db_binlog_hash of information about each DB being > binlogged. A DB_BIN_LOG_ENTRY (in the hash) contains: > dbname > locked (bool)--has it been locked by FLUSH ... TABLES WITH READ LOCK? > binlog file > binlog_pos > is_active > a pointer to a DB_BIN_LOG instance > > USER_BINLOG_MGR has a rw lock to protect access to its hash of > currently binlogging db's. (Most accesses will probably be > read-only?) > > Is it plausible to identify all places where a binlog addition might > occur? In those places, if the singleton USER_BIN_LOG_MRG exists, ask > it if the current DB is being binlogged. If so, send the request to > the DB_BIN_LOG instance. > > > Questions I Have. > ---------------- > > 1. I notice that binlog is in the list of engines and plugins > (displayed by SHOW ...). The bottom of log.cc has > "mysql_declare_plugin(binlog)". Do I need one for db_plugin? > (probably not?) > 2. I've diagrammed the binary log event taxonomy--the 24 classes that > inherit from Log_event (in log_event.cc/h). That was my main > motivation for wanting to inherit from MYSQL_BIN_LOG. > 3. On the other hand, I wonder if inheriting from MYSQL_BIN_LOG might > be overkill for "master only" binlogging--without the > slave/replication part of it. > 4. What version should I be working on? (We're currently running > 5.1.47.) What should I check out of bzr/launchpad? (Is that still > what we're doing with the changes in ownership?) > 5. I see that THD is involved in binlogging. (Several THD methods are > defined in log.cc.) Are there any caveats here? > 6. What files should I use? A new h/cc pair? ... or just add to the > hierarchy in log.h/cc? > > In an ideal world, it would be nice to inherit polymorphically from > MYSQL_BIN_LOG, overriding methods that set the names of the binlog > file and index file (etc.) then just call the methods and have it do > the "right thing." On the other hand, I get the impression that I > might be oversimplifying. > > I welcome your advice!! > > What I've Implemented So Far (about 13 Prototypes). > -------------------------------------------------- > > (PRELIMINARY NOTE:) > > Our initial plan was to call it "Per-User Binlogging". The user here > is a cPanel [control panel] user. cPanel's database naming convention > is to have all of a user's database names start with the username, > followed by an underscore, followed by a distinctive database > name. Thus all the commands above start with > > <verb> USER ... > > Originally most of them could end either "DATABASE <db>" or "USER > <user>" (with user meaning cPanel user). Thus, in the original plan > the first command above was: > > START USER BINLOG FOR (USER <user> | DATABASE <db>) > > We have realized that per-database binlogging is all we really need. > We imagine that others in our situation might want something similar. > Since not everyone uses cPanel, we should forget per[cPanel]-user > binlogging, focusing on per-database binlogging. > > I have several versions of lex and bison/yacc files (sql_yacc.yy, > lex.h) to implement the above (and earlier variants). The recent > versions avoid increasing the %expect'ed 169 shift/reduce conflicts > specified in sql_yacc.yy. > > (END OF PRELIMINARY NOTE) > > I currently have 13 different Subversion branches with different > (partial) implementations. > > 1. All have some lex/bison grammer defined. (I'm not completely > satisfied with any of them.) > > 2. Per-db binlogging is enabled by two my.cnf (or command-line) > options: > > user_binlog_dir=/full/directory/path > user_binlog_persist_file=user_binlog.info > > Our servers have fast solid-state drives that we will use for per-db > binlogging. We want to specify a separate directory to store all the > per-db binlogs > > The persist_file stores information about the current state of > db-binlogging--to survive a restart. > > Just about every instance of "user_binlog" in this note should > probably be changed to "db_binlog" ... (???) > > 3. I have added cases in sql_parse.cc for > > SQLCOM_START_USER_BINLOG_DB: > SQLCOM_FLUSH_AND_LOCK_DB: > SQLCOM_SHOW_USER_BINLOG_STATUS: > SQLCOM_UNLOCK_DB: > SQLCOM_STOP_USER_BINLOG_DB: > > (as well as some "USER" variants like SQLCOM_START_USER_BINLOG_USER:, > which should probably abandoned.) > > They should probably be changed to something like: > > SQLCOM_START_DB_BINLOG: > SQLCOM_FLUSH_AND_LOCK_DB: > SQLCOM_SHOW_DB_BINLOG_STATUS: > SQLCOM_UNLOCK_DB: > SQLCOM_STOP_DB_BINLOG: > > ----------------- > > Now that you've read it, please feel free to throw darts, etc. At this > point I'm willing to discard everytning and start over. (In the worst > case--if everyone on this list thinks per-db binlogging is a terrible > idea--I guess I'll be forced to create our own patch [sigh].) > > On the other hand, I would love to help contribute to this effort. (If > per-db binlogging is already underway, let me know, and I'll likely help.) > Thanks in advance for feedback/suggestions/help. Feel free to contact > me privately if you want to. > > Weldon Whipple > weldon(a)whipple.org > > -- > MySQL Internals Mailing List > For list archives: http://lists.mysql.com/internals > To unsubscribe: http://lists.mysql.com/internals?unsub=mdcallag@gmail.com > > -- Mark Callaghan mdcallag(a)gmail.com

3 2

[Maria-discuss] italian translation
by Federico Razzoli 12 Sep '10

12 Sep '10

Hello. I appreciate so much your new Knowledge Base. The pages that are already on line are very clear and complete, as the MySQL docs. But, unlike the MySQL docs, the Knowledge Base has free licenses. For these reasons, I would like to translate into italian - which is my natural language. Am I allowed to do that? As far as I understand, both the licenses used (CC BY-SA and GNU FDL) allow translations, as long as they have the same licenses used by the original work. But I want to be sure before starting. I can't translate all the articles, so I'll only translate some of the most interesting documents. But, as the documentation grows, maybe I may try to set up a group of volounteers. We'll see. Regards, Federico

2 1

[Maria-discuss] Mac package (was Re: Contact Form - []: jeremy ellison)
by Hakan Kuecuekyilmaz 26 Aug '10

26 Aug '10

Hi Jeremy, all, On 16.08.2010, at 12:47, Bo Thorsen wrote: > Den 14-08-2010 05:25, Contact Form skrev: >> Looks interesting, why do you not have a mariadb package for Apple >> Mac OS X? This is the basis for my blog, bb, and server backend. If >> you have no Mac support it's not a drop in replacement for MYSQL. > > Hi Jeremy, > > The reason is that no one has offered to do it yet. MariaDB isn't a product sold by Monty Program, but an open source community project. Us Monty Program guys do a lot of the work, though. > > If someone will contribute the necessary work to get Mac packages, they will be included. Until then, it's source code only. Should you be interested in doing this, or know someone who will, then you should ask about the necessary work on the mailing list > maria-discuss(a)lists.launchpad.net. I read about your request for a MariaDB package for Mac OS X on my vacation and did some research. Back from vacation I have an alpha version of MariaDB package for Mac OS X for you and for our community to test. Caution: this is the first installer I wrote on Mac, so do not use it on a production system. Please test the installer and provide us with feedback. Known issues in the MariaDB installer: * The Preferences Pane app for starting/stopping the server instance is missing * The installer for setting up MariaDB as a Startup Item is missing. Side node: while digging into the Mac installer I found two bugs in the MySQL Mac OS X installer. * http://bugs.mysql.com/bug.php?id=56279 Mac installer does not work as documented * http://bugs.mysql.com/bug.php?id=56280 Mac installer's postflight script does not work all the time You can grab the package from here: http://lisas.de/~hakan/file/mariadb-5.1.49-osx10.6-x86.pkg Best, Hakan -- Hakan Küçükyılmaz, QA/Benchmark Engineer, Stuttgart/Germany Monty Program Ab, http://askmonty.org/ Skype: hakank_ Phone: +49 171 1919839

1 0

[Maria-discuss] Clustering with Maria/Aria engine
by Joshua Partogi 26 Aug '10

26 Aug '10

Hi all, Does Maria/Aria engine supports clustering? Or do we still have to use NDB for this? Thanks for your help. Kind regards, Joshua. -- http://twitter.com/scrum8

3 4

[Maria-discuss] mariadb-mysql command-line problem on CentOS 5.5 i386
by Daniel Bartholomew 24 Aug '10

24 Aug '10

I saw a comment today from Eric Koh on the Knowledgebase article for the mysql Command-line Client: http://kb.askmonty.org/v/mysql-command-line-client --------------------------------------------------------------------- mariadb-mysql command-line problem on CentOS 5.5 i386 I tried these 2 RPMs MariaDB-client-5.1.42-68.el5.i386.rpm MariaDB-client-5.1.49-82.el5.i386.rpm I get this message when starting the mysql client mysql: symbol mysql_get_server_name, version libmysqlclient_16 not defined in file libmysqlclient.so.16 with link time reference running ldconfig/rebooting doesnt help. The RPMs for the server are fine though. I am thus using MariaDB-server and MariaDB-shared together with MySQL-client-community --------------------------------------------------------------------- Anyone know what might be going on here? Thanks. -- Daniel Bartholomew Monty Program - http://askmonty.org

4 6

[Maria-discuss] my_atomic_add64
by Oleg Tsarev 24 Aug '10

24 Aug '10

Hello, I need my_atomic_add64 in mysql. Can i simple add following macros, or i need more advanced tricks? tsarev@main:/storage/project/percona/rtd_2$ diff -Nur ../rtd/c/include/my_atomic.h c/include/my_atomic.h --- ../rtd/c/include/my_atomic.h 2010-07-09 16:35:11.000000000 +0400 +++ c/include/my_atomic.h 2010-08-17 18:57:07.648819066 +0400 @@ -96,25 +96,30 @@ make_atomic_cas( 8) make_atomic_cas(16) make_atomic_cas(32) +make_atomic_cas(64) make_atomic_cas(ptr) make_atomic_add( 8) make_atomic_add(16) make_atomic_add(32) +make_atomic_add(64) make_atomic_load( 8) make_atomic_load(16) make_atomic_load(32) +make_atomic_load(64) make_atomic_load(ptr) make_atomic_store( 8) make_atomic_store(16) make_atomic_store(32) +make_atomic_store(64) make_atomic_store(ptr) make_atomic_swap( 8) make_atomic_swap(16) make_atomic_swap(32) +make_atomic_swap(64) make_atomic_swap(ptr) #undef make_atomic_add --- Oleg Tsarev, Software Engineer, Percona Inc.

3 4

[Maria-discuss] Decimal operations
by Oleg Tsarev 23 Aug '10

23 Aug '10

Hello, I don't understand how right work with decimal type. As you talk, i change column types from CHAR(...) to DECIMAL in my patch: http://www.percona.com/docs/wiki/patches:response_time_distribution Now i have trouble with decimal precision/scale. Here is my definition of system table INFORMATION_SCHEMA.QUERY_RESPONSE_TIME: ST_FIELD_INFO query_response_time_fields_info[] = { {"TIME", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0, MY_I_S_UNSIGNED, "time", SKIP_OPEN_TABLE }, {"COUNT", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, MY_I_S_UNSIGNED, "count", SKIP_OPEN_TABLE }, {"TOTAL", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0, MY_I_S_UNSIGNED, "total", SKIP_OPEN_TABLE }, {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, SKIP_OPEN_TABLE } }; So, i want use DECIMAL(20,6) for columns time and total, and DECIMAL(14,0) for columns count. 1) Does above definiton correct, or should include some information about precision/scale? I collect time of query execution in: 1) Column total - in int64, microseconds count 2) Column count - in int64, just count Here is source code, for fill this table: for(int i= 0, count= m_utility.bound_count() + 1 /* with overflow */; count > i; ++i) { const uint64 time = m_utility.bound(i); const int64 count = m_time.count(i); const int64 all = m_time.all(i); my_decimal time_d; my_decimal count_d; my_decimal all_d; my_decimal million_d; my_decimal temp_d; ulonglong2decimal(MILLION,&million_d); ulonglong2decimal(time, &temp_d); my_decimal_div(E_DEC_OK,&time_d,&temp_d,&million_d,SCALE); longlong2decimal(count,&count_d); longlong2decimal(all, &temp_d); my_decimal_div(E_DEC_OK,&all_d,&temp_d,&million_d,SCALE); fields[0]->store_decimal(&time_d); fields[1]->store_decimal(&count_d); fields[2]->store_decimal(&all_d); if (schema_table_store_record(thd, table)) { DBUG_RETURN(1); } } I want store some value to decimal type, than divide by MILLION: microsecond_count => convert to decimal => / 1000000 = result. But as result i get: SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; TIME COUNT TOTAL 0 0 0 0 0 0 0 1 0 0 0 0 0 1 0 0 0 0 1 11 4 10 8 13 100 0 0 1000 0 0 10000 0 0 ...................... Instead of wanted: SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; TIME COUNT TOTAL 0.000001 0 0 0.00001 0 0 0.0001 1 0 0.001 0 0 0.01 1 0 0.1 0 0 1 11 4 10 8 13 100 0 0 1000 0 0 10000 0 0 ...................... 2) What i do incorrect? How fix it? Best regards, Oleg

2 1

Re: [Maria-discuss] Full Replacement of MySQL (innoDB, mysql Workbench, data, etc)
by Daniel Bartholomew 17 Aug '10

17 Aug '10

On Mon, 16 Aug 2010 16:25:00 -0000 Enrique Daniel <enridp(a)yahoo.com.ar> wrote: Enrique> Hi !! Hello! I'm CC'ing this message to the maria-discuss mailing list in case anyone else wants to comment. I recommend you join the list. It's a good resource. Enrique> I'm really new with MariaDB, I have discovered it yesterday at Enrique> really :S And I have not tested it yet, but it looks really Enrique> promising. Thanks! We've done lots of testing to make sure MariaDB works as seamlessly as possible for those moving from MySQL to MariaDB. As with any upgrade, I would definitely recommend making a backup and testing MariaDB on your end to ensure everything works with your applications and use-cases before putting it into production. Enrique> Anyway, there are a few things that are braking me to make the Enrique> change. I hope somebody can help me: Enrique> Enrique> 1) I'm using mySQL Workbench to design my DB, how can we use Enrique> it with MariaDB? (Aria, XtraDB engine, etc.) I haven't used MySQL Workbench much, but using it with MariaDB should be exactly the same as using it with MySQL. Enrique> 2) My tables ar InnoDB, but I was reading about XtraDB and it Enrique> looks much better, how can I make the change from innoDB to Enrique> XtraDB? if I have a SQL file, is enough changing ENGINE=INNODB Enrique> with ENGINE=XTRADB (or something like that). Enrique> I want to preserve my FK, with cascades, and comments. For compatibility purposes, XtraDB identifies itself to the server as InnoDB, so there's no need to change ENGINE= in any SQL files. Any existing InnoDB tables will start using XtraDB automatically after the upgrade. Enrique> 3) What happens if I have my DB with data and working with Enrique> MySQL and InnoDB right now? can I change to MariaDB (and Enrique> XtraDB) preserving all my scheme, tables, data, relations, Enrique> comments, indexes, etc? Yes. All of our testing indicates you can upgrade from MySQL to MariaDB and all of your schemas, tables, data, relations, comments, indexes, and so on will "just work" like they did with MySQL without you having to change anything. Of course, as with any upgrade, I would recommend testing on a non-production server before putting MariaDB into production. One last note. If you do find anything that doesn't work, please file a bug report: https://bugs.launchpad.net/maria/+filebug Thanks. -- Daniel Bartholomew Monty Program - http://askmonty.org

4 5

Re: [Maria-discuss] Full Replacement of MySQL (innoDB, mysql Workbench, data, etc)
by Henrik Ingo 16 Aug '10

16 Aug '10

I'm forwarding this to the mailing list since of all the places it was sent to, lp did not send it to the list... Please take care of this, nice MariaDB people: henrik On Mon, Aug 16, 2010 at 7:25 PM, Enrique Daniel <enridp(a)yahoo.com.ar> wrote: > Hi !! > I'm really new with MariaDB, I have discovered it yesterday at really :S > And I have not tested it yet, but it looks really promising. > > Anyway, there are a few things that are braking me to make the change. > I hope somebody can help me: > > 1) I'm using mySQL Workbench to design my DB, how can we use it with > MariaDB? (Aria, XtraDB engine, etc.) > > 2) My tables ar InnoDB, but I was reading about XtraDB and it looks much > better, how can I make the change from innoDB to XtraDB? if I have a SQL > file, is enough changing ENGINE=INNODB with ENGINE=XTRADB (or something > like that). > I want to preserve my FK, with cascades, and comments. > > 3) What happens if I have my DB with data and working with MySQL and > InnoDB right now? can I change to MariaDB (and XtraDB) preserving all my > scheme, tables, data, relations, comments, indexes, etc? > > Thanks !!! > Enrique. > -- > This message was sent from Launchpad by the user > Enrique Daniel (https://launchpad.net/~enridp) > using the "Contact this team" link on the Maria Discuss > team page to each member directly. > For more information see > https://help.launchpad.net/YourAccount/ContactingPeople > -- email: henrik.ingo(a)avoinelama.fi tel: +358-40-5697354 www: www.avoinelama.fi/~hingo book: www.openlife.cc

1 0

Re: [Maria-discuss] Locale settings and fractal number
by Oleg Tsarev 16 Aug '10

16 Aug '10

Hi, Sergey! I use string column, because i need fixed-point number (time column): 14_digit_to_second.6_digit_to_microsecond 123456.654321 as example. Big number, because i have not only "time" column and also "sum" column too. I didn't find any type what good for this, and i use string. Typically this columns use only by hand or in scripts, and don't used in any another queries. When i prepare output, i should print number and i don't know what the delimiter should i use. What the internal function should i call for get correct delimeter? Best regards, Oleg. 2010/8/16 Sergei Golubchik <serg(a)askmonty.org>: > Hi, Oleg! > > On Aug 15, Oleg Tsarev wrote: >> >> Hello all, >> >> I have feature >> http://www.percona.com/docs/wiki/patches:response_time_distribution >> Now i want respect locale settings, and use in "time" column >> locale-depended delimiter instead of current hardcoded ".". >> I investigate source code about that, and also bugzilla, >> documentation, but not found answer to my question - where this >> delimieter setup. >> >> What the "official" mysql policy? Use only "."? Or use native-locale? >> Or use some internal mysql function? >> Please, resolve my confuse. >> I want understand how this questions solved in mysql. > > As far as I understand, MySQL typically does not uses system locale > information for formatting numbers or dates. > > Anyway, you shouldn't care about locale - as you have two columns > of numbers, you need to have two numeric columns, say, DOUBLE and > BIGINT. > > Regards, > Sergei >

3 2

[Maria-discuss] Locale settings and fractal number
by Oleg Tsarev 15 Aug '10

15 Aug '10

Hello all, I have feature http://www.percona.com/docs/wiki/patches:response_time_distribution Now i want respect locale settings, and use in "time" column locale-depended delimiter instead of current hardcoded ".". I investigate source code about that, and also bugzilla, documentation, but not found answer to my question - where this delimieter setup. What the "official" mysql policy? Use only "."? Or use native-locale? Or use some internal mysql function? Please, resolve my confuse. I want understand how this questions solved in mysql. Thank you very much. --- Oleg Tsarev, Software Engineer, Percona Inc.

2 1

[Maria-discuss] Google docs now allows collaborative graphics and schemas
by Henrik Ingo 14 Aug '10

14 Aug '10

Hi I didn't try it yet, but Google docs just advertised the addition of a collaborative graphics app and schema drawing app. It's not open source of course, but it's better than nothing. Especially if they turn out to support ODF fileformats. henrik (This was a topic discussed at the devmeeting in Iceland.) -- email: henrik.ingo(a)avoinelama.fi tel: +358-40-5697354 www: www.avoinelama.fi/~hingo book: www.openlife.cc

1 0

[Maria-discuss] Wiki to Knowledgebase Move
by Daniel Bartholomew 13 Aug '10

13 Aug '10

All, I have started (and over the course of the next week I will continue) moving several pages from http://askmonty.org/wiki to http://kb.askmonty.org With the Knowledgebase now in beta it is time to move the content over. The procedure I plan on using for this is as follows: 1. Copy the content of a page from the wiki into the Knowledgebase. 2. Add a link to the top of the wiki page notifying of the move and giving a link to the new location of the page on the KB. 3. Add a redirect to the webserver config which automatically sends you to the KB page if you click on a link to the wiki page. I wanted to let you know about it now so that you are not surprised when you click on a link in the wiki and end up in the Knowledgebase. After most of the pages which are slated to be moved have been moved I will start deleting the pages from the wiki. The pages won't actually be gone (because of MediaWiki's undelete functionality) but having them officially deleted brings closure to the page and prevents it being accidentally viewed and/or edited. The pages slated to be moved include all of the ones in the "Manual:" namespace and several of the ones in the "MariaDB:" section. An easy way to think of it is that all documentation-type pages on the wiki are going to move to the Knowledgebase. Pages the developers use for planning releases (such as the 5.2 TODO page) will not move. Pages developers use to document new features will be moved when the developer indicates the page has become stable and ready to be used as documentation. In the future, some features will likely be documented first on the KB, but I'm sure there will always be some which begin life on the wiki. The redirects will stay in place indefinitely since keeping them around is easy and painless. The wiki will not go away, it is going to continue to be a site the MariaDB devs can use for notes, plans, and so on. The look will also change to better fit this mission. The corporate info, for example, has already been moved to http://montyprogram.com so the navigation links at the top of every page (About Us, Business, Partners, etc...) are no-longer necessary. Let me know if you have any questions or suggestions. Thanks. -- Daniel Bartholomew Monty Program - http://askmonty.org

1 0

[Maria-discuss] What is the log_bin and log_bin_index config for?
by Joshua Partogi 10 Aug '10

10 Aug '10

Hi all, Can anyone advise what is the log_bin and log_bin_index configuration in my.cnf for? When I enable both of this config and did and alter table on a table with PBXT engine, I encounter this error: MariaDB [bar]> alter table foo rename to tmp; ERROR 1598 (HY000): Binary logging not possible. Message: Statement-based format required for this statement, but not allowed by this combination of engines I am using MariaDB 5.1.47. Kind regards, Joshua. -- http://twitter.com/scrum8

2 1

[Maria-discuss] MySQL error code - forward compatible
by Oleg Tsarev 05 Aug '10

05 Aug '10

Dear MariaDB, Both Percona-Server/XtraDB and MariaDB experience problems concerning forward compatible error codes. 1) Both servers have a lot of features backported from mysql 5.5. Some features add new error (new error codes from MySQL 5.5). 2) Some Percona-Server/XtraDB and MariaDB features add new error code. In the first case we should save error code. For example, Percona has patch query_cache_totally_disable.patch (http://www.percona.com/docs/wiki/patches:query_cache_totally_disable) This patch adds error ER_QUERY_CACHE_DISABLED - error code number 1651 in mysql 5.5 (http://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html#error_er_…) We must be consistent with error code numbers, because they may be used in scripts. After migrating PerconaServer/XtraDB and MariaDB to mysql 5.5, users might experience troubles because of this. In the second case we should add error code numbers, unique for our errors, as we cannot use any numbers from mysql 5.5. For example: http://www.percona.com/docs/wiki/patches:slave_type_conversions_error_on_tr… MySQL has a tool "comp_err" that generates 1) errmsg.sys files 2) header file include/mysqld_error.h 3) header file include/mysqld_ername.h from the file errmsg.txt To keep error numbers consistent, we should add some fictive errors to errmsg.txt, because comp_err assigns error code numbers sequentially, without gaps. I propose patch to comp_err (see attachment error_pad.patch). This patch allows usage of a new syntax, with prefix "PADD_", for example: PADD_QUERY_CACHE_DISABLED 1651 eng "ER_QUERY_CACHE_DISABLED padding to 1651 error" ER_QUERY_CACHE_DISABLED eng "Query cache is disabled; restart the server with query_cache_type=1 to enable it" comp_err with my patch padds empty intervals (from last error code number to 1651) by error message "ER_QUERY_CACHE_DISABLED padding to 1651 error", i.e. and ER_QUERY_CACHE_DISABLED now has error code 1651 (as desired). I propose to use this patch for Percona's and Maria's errors, for example: PADD_PERCONA_NEW_ERROR_CODE 4000 end "Padd empty space to error code number 4000 (Percona error codes)" ...some percona error codes... PADD_MARIA_DB_NEW_ERROR_CODE 5000 eng "Padd empty space to error code number 5000 (MariaDB error codes)" ...some maria error codes... Patch only adds prefix "PADD_" and padds error in sys files. All other mysql code (load*.sys files, my_error, etc) works as old one. Please, let me know what you think of it. Best regards, Oleg.

1 0

[Maria-discuss] Full text search in MariaDB
by Joshua Partogi 04 Aug '10

04 Aug '10

Hi all, We are currently using MariaDB in one of our products. One thing that we are wondering, does Maria engine currently supports Fulltext search like MyISAM? If currently not implemented yet, will there be any plan to implement fulltext support in MariaDB? Thank you very much in advance for your assistance. Kind regards, Joshua. -- http://twitter.com/scrum8

6 8

[Maria-discuss] New error code
by Олег Царев 03 Aug '10

03 Aug '10

Hi people, I solve trouble from previous letter by insert fictive errors. Now i have one more trouble - should add new error message to 5.1, bug i should respect forward compatible with mysql 5.5 What the error number get? Does mysql have reserved intervals for third-party patches? Thank you, Oleg.

2 1

[Maria-discuss] Error messages and Error codes (backport 5.5 => 5.1)
by Олег Царев 01 Aug '10

01 Aug '10

Hello all, I have troubles with backport some errors from 5.5 to 5.1 http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_… http://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html#error_er_… Last error in mysql 5.1.49 - 1640. If i just add ER_QUERY_CACHE_DISABLED --- a/sql/share/errmsg.txt 2010-07-09 16:35:08.000000000 +0400 +++ b/sql/share/errmsg.txt 2010-07-31 20:41:47.244015659 +0400 @@ -6213,3 +6213,7 @@ ER_DEBUG_SYNC_HIT_LIMIT eng "debug sync point hit limit reached" ger "Debug Sync Point Hit Limit erreicht" + +ER_QUERY_CACHE_DISABLED + eng "Query cache is disabled; restart the server with query_cache_type=1 to enable it" + then error code of ER_DEBUG_SYNC_HIT_LIMIT is 1641 (instead of 1651 what i need). If i try: --- a/sql/share/errmsg.txt 2010-07-09 16:35:08.000000000 +0400 +++ b/sql/share/errmsg.txt 2010-07-31 20:41:47.244015659 +0400 @@ -6213,3 +6213,8 @@ ER_DEBUG_SYNC_HIT_LIMIT eng "debug sync point hit limit reached" ger "Debug Sync Point Hit Limit erreicht" + +start-error-number 1010 +ER_QUERY_CACHE_DISABLED + eng "Query cache is disabled; restart the server with query_cache_type=1 to enable it" + then mysql doesn't start with error in bootstrap.log like: "mysqld can't srart because errmsg.sys has only 642 errors, when last error number is ...." How can i backport error from mysql 5.5 to mysql 5.1 right? Now i solve trouble like: --- a/sql/share/errmsg.txt 2010-07-09 16:35:08.000000000 +0400 +++ b/sql/share/errmsg.txt 2010-07-31 20:41:47.244015659 +0400 @@ -6213,3 +6213,27 @@ ER_DEBUG_SYNC_HIT_LIMIT eng "debug sync point hit limit reached" ger "Debug Sync Point Hit Limit erreicht" + +ER_QUERY_CACHE_DISABLED_WRITE_SPACE_01 + eng "Fix error number for ER_QUERY_CACHE_DISABLED" +ER_QUERY_CACHE_DISABLED_WRITE_SPACE_02 + eng "Fix error number for ER_QUERY_CACHE_DISABLED" +ER_QUERY_CACHE_DISABLED_WRITE_SPACE_03 + eng "Fix error number for ER_QUERY_CACHE_DISABLED" +ER_QUERY_CACHE_DISABLED_WRITE_SPACE_04 + eng "Fix error number for ER_QUERY_CACHE_DISABLED" +ER_QUERY_CACHE_DISABLED_WRITE_SPACE_05 + eng "Fix error number for ER_QUERY_CACHE_DISABLED" +ER_QUERY_CACHE_DISABLED_WRITE_SPACE_06 + eng "Fix error number for ER_QUERY_CACHE_DISABLED" +ER_QUERY_CACHE_DISABLED_WRITE_SPACE_07 + eng "Fix error number for ER_QUERY_CACHE_DISABLED" +ER_QUERY_CACHE_DISABLED_WRITE_SPACE_08 + eng "Fix error number for ER_QUERY_CACHE_DISABLED" +ER_QUERY_CACHE_DISABLED_WRITE_SPACE_09 + eng "Fix error number for ER_QUERY_CACHE_DISABLED" +ER_QUERY_CACHE_DISABLED_WRITE_SPACE_10 + eng "Fix error number for ER_QUERY_CACHE_DISABLED" +ER_QUERY_CACHE_DISABLED + eng "Query cache is disabled; restart the server with query_cache_type=1 to enable it" + But this is ugly hack. What do you think about my trouble and ways for solve? Best regards, Oleg.

2 1

[Maria-discuss] Possible CVE from upstream
by Brian Evans 29 Jul '10

29 Jul '10

This seems to affect the released versions of MariaDB as well since they are based on 5.1.47 upstream as far as I can tell. CVE-2010-2008 (http://nvd.nist.gov/nvd.cfm?cvename=CVE-2010-2008) MySQL before 5.1.48 allows remote authenticated users with alter database privileges to cause a denial of service (server crash and database loss) via an ALTER DATABASE command with a #mysql50# string followed by a . (dot), .. (dot dot), ../ (dot dot slash) or similar sequence, and an UPGRADE DATA DIRECTORY NAME command, which causes MySQL to move certain directories to the server data directory.

1 0

[Maria-discuss] where are the buildbot results for valgrind and the InnoDB plugin?
by MARK CALLAGHAN 25 Jul '10

25 Jul '10

As described in http://mysqlha.blogspot.com/2010/07/building-mariadb-with-innodb-plugin.html I did some of the QA checks that I do for any new code that I might use. I have yet to find buildbot results for valgrind and the InnoDB plugin or XtraDB. Are they not run for buildbot? -- Mark Callaghan mdcallag(a)gmail.com

2 5

[Maria-discuss] (no subject)
by Justin Swanhart 16 Jun '10

16 Jun '10

http://wafejqh.wifethen.ru -- -- Justin Swanhart, Consultant, Percona Inc. Bay Area, USA (PDT, GMT -7) +1 888 401 3401 | Justin x503 | 24x7 Help x911 Our Services: http://www.percona.com/services.html Our Blog: http://www.mysqlperformanceblog.com/

3 2

[Maria-discuss] OpenSQLCamp EU Call for Papers
by Henrik Ingo 31 May '10

31 May '10

Giuseppe Maxia wanted me to bring to your attention, that the Call for Papers for OpenSQLCamp EU is now open: http://opensqlcamp.org/Events/2010/Call_for_Participation "The European OpenSQLCamp 2010 will take place in parallel to the Free and Open Source Conference 2010 (FrOSCon) on Saturday 21st and Sunday 22nd August at the Fachhochschule Bonn-Rhein-Sieg in St. Augustin, Germany. St. Augustin is located close to Bonn and Cologne. " henrik -- Henrik Ingo Project Manager and COO, Monty Program Ab hingo(a)askmonty.org, skype:henrik.ingo, +358405697354 http://askmonty.org/wiki/index.php/About_Us What's up with MariaDB? http://askmonty.org/wiki/index.php/MariaDB

1 0

[Maria-discuss] Please help me clear this simple but critical issue [SELECT INTO OUTFILE LOCAL]
by Cool Guy 19 May '10

19 May '10

MySQL is awesome! I am currently involved in a major server migration and previously, our small database used to be hosted on the same server as the client. So we used to do this : SELECT * INTO OUTFILE .... LOAD DATA INFILE .... Now, we moved the database to a different server and SELECT * INTO OUTFILE .... no longer works, understandable - security reasons I believe. But, interestingly LOAD DATA INFILE .... can be changed to LOAD DATA LOCAL INFILE .... and bam, it works. I am not complaining nor am I expressing disgust towards MySQL. The alternative to that added 2 lines of extra code and a system call form a .sql script. All I wanted to know is why LOAD DATA LOCAL INFILE works and why is there no such thing as SELECT INTO OUTFILE LOCAL? I did my homework, couldn't find a direct answer to my questions above. I couldn't find a feature request @ MySQL either. If someone can clear that up, that had be awesome! I am planning on suggesting MariaDB to our company cos' it is not under Oracle's admin. Does MariaDB already have this "SELECT INTO OUTFILE LOCAL .." feature implemented or is it in the wishlist/to-do list? Thank you, Kiran

3 14

[Maria-discuss] Someone from MariaDB to talk at ODTUG conference?
by Henrik Ingo 11 May '10

11 May '10

Hi all I just had a talk with Sheeri about an upcoming Oracle Developer Tools User Group conference in June in Washington. There will be a 1 day MySQL track on Sunday, plus possibly some other MySQL talks: http://www.odtugkaleidoscope.com/agenda.html Sheeri was asking if someone from Monty Program (or, imho any MariaDB activist) wanted to present. Things to note: - The conference is application developer oriented, so things like optimizer internals or tuning InnoDB buffer size is not interesting. - We don't want to talk about MariaDB per se. We don't want to introduce this crowd to the reality of there being so many MySQL forks. The previous points pretty much rule out everything we presented at the MySQL conference :-) Good topics could be: - Efficient and inefficient SQL queries in MySQL - UDF tutorial and examples The first suggestion is imho good. These Oracle experts need to learn to avoid certain things like most subqueries, and how to work around that. But on the other hand this is a natural topic for someone from us to present, since we could then also mention that correlated subqueries will work creat in MariaDB 5.3, which may even be beta by this point? The second suggestion is an example of something that would be considered very technical topic in this conference. Now the question is who to send there? This is not significant enough that someone from Europe would fly in - heh, most of us would probably not volunteer themselves at this point :-) In US I see Bryan or Daniel as candidates for this, Igor possibly for the UDF kind of talk more than the app developer talk. And, it needn't be a Monty Program employee, let me know if you're interested. Colin, will you pick this up from here? henrik -- email: henrik.ingo(a)avoinelama.fi tel: +358-40-5697354 www: www.avoinelama.fi/~hingo book: www.openlife.cc

1 1

[Maria-discuss] PBXT issues
by Alexandre Almeida 06 May '10

06 May '10

Hi Guys, I am setting an enviroment with MariaDB + PBXT, but, I am pretty much disappointed. Do you know if this time (see below) makes sense? Five hours to create a PBXT table with 10mio records... It's too much!!! See my steps: create table telpbxt like telinno; Query OK, 0 rows affected (0,50 sec) fis-> alter table telpbxt engine = pbxts; Query OK, 0 rows affected, 1 warning (0,07 sec) Records: 0 Duplicates: 0 Warnings: 0 fis-> alter table telpbxt engine = pbxt; Query OK, 0 rows affected (0,26 sec) Records: 0 Duplicates: 0 Warnings: 0 fis-> show create table telpbxt; +--------- +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------- +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | telpbxt | CREATE TABLE `telpbxt` ( `cpf` bigint(20) DEFAULT NULL, `nome` varchar(100) DEFAULT NULL, `log` varchar(100) DEFAULT NULL, `endereco` varchar(100) DEFAULT NULL, `comp1` varchar(100) DEFAULT NULL, `comp2` varchar(100) DEFAULT NULL, `bairro` varchar(100) DEFAULT NULL, `cidade` varchar(100) DEFAULT NULL, `cep` varchar(100) DEFAULT NULL, `ddd` varchar(100) DEFAULT NULL, `telefone` varchar(100) DEFAULT NULL, KEY `idx2` (`nome`(10)) ) ENGINE=PBXT DEFAULT CHARSET=latin1 | +--------- +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0,00 sec) fis-> insert into telpbxt select * from tel; Query OK, 10262048 rows affected (5 hours 2 min 22,14 sec) Records: 10262048 Duplicates: 0 Warnings: 0

4 11

[Maria-discuss] bug or not bug? ;-)
by Alexandre Almeida 05 May '10

05 May '10

Guys, everytime I run the following command: show global status like 'created%tmp%tables' the created_tmp_tables is incremented by 1. Is that right? I mean, which relationship between a show global (that will perform a minimum select at information_schema) and a new temporary table? I would appreciate your comments. Alex

2 2

[Maria-discuss] New Maria Docs list
by Daniel Bartholomew 05 May '10

05 May '10

All, I've created a new "Maria Docs" Launchpad team and mailing list for documentation-related discussions (including things like documentation for current and upcoming releases of MariaDB, planning and discussion of the askmonty.org knowledgebase, and so on). The group is open to everyone. https://launchpad.net/~maria-docs Thanks! -- Daniel Bartholomew Monty Program - http://askmonty.org

1 0

[Maria-discuss] MariaDB shutdowns silently
by Joshua Partogi 23 Apr '10

23 Apr '10

Dear all, I am currently running MariaDB in production and I often found it would shutdown itself silently after inactivity. The application itself is using Ruby on Rails. I have disabled the max_timeout settings in my.cnf but the problem still occurs. Is there anything else I should watch out for to prevent this issue continuing to occur? Thank you very much for your assistance. Kind regards, Joshua -- http://twitter.com/scrum8

6 8

[Maria-discuss] What is the future of MariaDB
by Joshua Partogi 20 Apr '10

20 Apr '10

All, Many people were feeling uncertain whether to use MySQL when Sun was bought by Oracle. I was one of those people and that is why I chose MariaDB for my product now. But I need to know what is the plan for MariaDB in the future? Would there be any chance that MariaDB will be sold to another company just like MySQL got sold to Sun? Or would MariaDB always be a community owned? Kind regards, Joshua -- http://twitter.com/projexion

5 4

Re: [Maria-discuss] Fwd: MySQL Open Space
by Kristian Nielsen 09 Apr '10

09 Apr '10

Henrik Ingo <hingo(a)askmonty.org> writes: > Didn't you have something about Buildbot you wanted to do? Maybe even have > someone from Drizzle join - depending on what you had planned to talk about. I have not planned anything. But I'm happy to do a session going through various aspects of Buildbot if there is an interest. - Kristian. > ---------- Forwarded Message ---------- > > Subject: MySQL Open Space > Date: Friday 09 April 2010 > From: Colin Charles <byte(a)bytebot.net> > To: "Sheeri K. Cabral" <awfief(a)gmail.com>, Brian Aker <brian(a)tangent.org>, > Henrik Ingo <hingo(a)askmonty.org>, Giuseppe Maxia <giuseppe(a)mysql.com>, Peter > Zaitsev <pz(a)percona.com>, Arjen Lentz <arjen(a)openquery.com> > > Hi! > > You know there is a MySQL open space on the schedule for the > conference, in the same room that the MySQL Camp was held last year > > For the space to be properly utilised, I was wondering if people would > like to run side sessions there. > > Its on Tuesday (http://en.oreilly.com/mysql2010/public/schedule/detail/12348 > ), Wednesday (http://en.oreilly.com/mysql2010/public/schedule/detail/12349 > ) and also on Thursday (but possibly in a different room). > > http://forge.mysql.com/wiki/MySQL_Conference_Open_Space_2010 > > That should help, I think *grin* > > Please feel free to pass it around and see if we get lots of side > content. Things that weren't accepted. Things that require more than a > BoF. > > Cheers, > -c

2 1

[Maria-discuss] Generic Linux Build requirements
by Brian Evans 06 Apr '10

06 Apr '10

http://askmonty.org/wiki/index.php/Building_MariaDB:Generic_Linux says: * gcc 2.95.2 or later However, according to [1] , Gentoo claims that GCC 4.3 is required due the use of c++ templates in the server. [2] also states that GCC-4 is absolutely required with a failing build log. [1] http://bugs.gentoo.org/show_bug.cgi?id=307251#c5 [2] http://bugs.gentoo.org/show_bug.cgi?id=284946

3 4

[Maria-discuss] MariaDB 5.1.44 has been released
by Colin Charles 06 Apr '10

06 Apr '10

Dear MariaDB users, MariaDB 5.1.44, a new branch of the MySQL database which includes all major open source storage engines, myriad bug fixes, and many community patches, has been released. This release is based on MySQL 5.1.44. In includes performance improvements with Maria temporary tables, removal of mutexes and the aim of removing compiler errors is being achieved quite well! For an overview of what's new in MariaDB 5.1.44, please see: http://askmonty.org/wiki/Manual:MariaDB_5.1.44_Release_Notes For information on installing MariaDB 5.1.44 on new servers or upgrading to MariaDB 5.1.44 from previous releases, please see: http://askmonty.org/wiki/Manual:Installation MariaDB is available in source and binary form for a variety of platforms and is available from the download pages at: http://askmonty.org/wiki/MariaDB:Download:MariaDB_5.1.44 It is also our pleasure to announce that we have a partnership with Webyog to offer their tools for trial and at a discounted rate if purchased within 30 days. Find out more at: http://askmonty.org/downloads/links/webyog http://askmonty.org/wiki/MariaDB:Download#Software_Partner_Downloads We welcome and appreciate your feedback, bug reports, bug fixes, patches, and participation on our mailing list. Find out more at: http://askmonty.org/wiki/MariaDB#How_can_I_participate_in_the_development_o… Enjoy! -- Colin Charles, http://bytebot.net/blog/ "First they ignore you, then they laugh at you, then they fight you, then you win." -- Mohandas Gandhi

4 3

[Maria-discuss] Problems with Foreign Key
by Alexandre Almeida 06 Apr '10

06 Apr '10

Hi all, Any idea why I am getting this error: A) ERROR ALTER TABLE login -> ADD CONSTRAINT fk_db_perfil FOREIGN KEY (id_perfil) -> REFERENCES db_perfil (id_perfil) -> ON DELETE NO ACTION -> ON UPDATE NO ACTION;ERROR 1005 (HY000): Can't create table 'dbhomo_aa.#sql-c1f_281' (errno: 150)dbhomo_aa-> dbhomo_aa-> B) HERE IT WORKS dbhomo_aa-> ALTER TABLE login ADD CONSTRAINT fk_db_perfil FOREIGN KEY (id_perfil) REFERENCES dbhomo.db_perfil (id_perfil) ON DELETE NO ACTION ON UPDATE NO ACTION; Query OK, 35199 rows affected (3,27 sec) Records: 35199 Duplicates: 0 Warnings: 0 Note that when I change the database to the original database the command works as expected. Both tables are innodb, the permissions are fine. Thanks, Alex

2 1

[Maria-discuss] Reliable tarball source
by Brian Evans 02 Apr '10

02 Apr '10

I just noticed that MariaDB 5.1.44 was posted on askmonty.org but not lauchpad. What will be the most reliable URL to pull the latest source tarball when it is released? I'd prefer to make it automated as Gentoo can simply relate a build script name to a version.

2 1

[Maria-discuss] Why isn't SO_SNDTIMEO used?
by MARK CALLAGHAN 21 Mar '10

21 Mar '10

I describe how read/write is done for sockets in mysqld. http://www.facebook.com/note.php?note_id=122655300932 Can someone explain the history of this code? -- Mark Callaghan mdcallag(a)gmail.com

4 7

[Maria-discuss] Maria on Solaris X86
by Jim Grill 18 Mar '10

18 Mar '10

Hello, I’m having trouble getting MariaDB 5.1.42 working on Solaris 10x86. I’ve tried both the Binary package (mariadb-5.1.42-solaris11-i386) and building from source (mariadb-5.1.42). I don't know if it's something in the binary package or source code, something goofy on my system, or something goofy with how I'm building it. I'm assuming the latter, but thought I might bring it up anyway just in case it might help others running Solaris. :-) I can’t get the binary dist to startup. Here’s the error log from the binary dist (note the hostname missing from the error log name)… $ cat data/.err 100212 09:54:33 mysqld_safe Starting mysqld daemon with databases from /data01/mydata/mariadb-bin-tst/data ld.so.1: mysqld: fatal: relocation error: file bin/mysqld: symbol strnlen: referenced symbol not found 100212 09:54:33 mysqld_safe mysqld from pid file /data01/mydata/mariadb-bin-tst/mysql.pid ended I’m using SunStudio 12.1 to build from source. Here’s my configure and make: export PATH=/product01/sunstudio12.1/bin:${PATH} export LD_LIBRARY_PATH=/usr/lib:/usr/local/lib make distclean ASFLAGS="-m64" \ CC="cc-5.0" \ CFLAGS="-Xa -xstrconst -g -mt -D_FORTEC_ -m64" \ CXX="CC" \ CXXFLAGS="-noex -g -mt -D_FORTEC_ -m64" \ LIBS=-lmtmalloc \ ./configure \ --prefix=/product01/mysql/mariadb-5.1.42 \ --localstatedir=/product01/mysql/mariadb-5.1.42/data \ --libexecdir=/product01/mysql/mariadb-5.1.42/bin \ --with-extra-charsets=all \ --enable-thread-safe-client \ --enable-local-infile \ --with-zlib-dir=bundled \ --with-big-tables \ --with-readline \ --with-named-curses-libs=-lcurses \ --enable-assembler \ --enable-static \ --enable-shared /usr/sfw/bin/gmake [SNIP] gmake[1]: Entering directory `/home/mysql/jgrill/mariadb-5.1.42/dbug' source='dbug.c' object='dbug.o' libtool=no \ DEPDIR=.deps depmode=none /bin/bash ../depcomp \ cc-5.0 -DHAVE_CONFIG_H -I. -I. -I../include -I../include -I../include -Xa -xstrconst -g -mt -D_FORTEC_ -m64 -DHAVE_RWLOCK_T -DUNIV_SOLARIS -c dbug.c source='sanity.c' object='sanity.o' libtool=no \ DEPDIR=.deps depmode=none /bin/bash ../depcomp \ cc-5.0 -DHAVE_CONFIG_H -I. -I. -I../include -I../include -I../include -Xa -xstrconst -g -mt -D_FORTEC_ -m64 -DHAVE_RWLOCK_T -DUNIV_SOLARIS -c sanity.c rm -f libdbug.a ar cru libdbug.a dbug.o sanity.o ranlib libdbug.a source='my_main.c' object='my_main.o' libtool=no \ DEPDIR=.deps depmode=none /bin/bash ../depcomp \ cc-5.0 -DHAVE_CONFIG_H -I. -I. -I../include -I../include -I../include -Xa -xstrconst -g -mt -D_FORTEC_ -m64 -DHAVE_RWLOCK_T -DUNIV_SOLARIS -c my_main.c source='factorial.c' object='factorial.o' libtool=no \ DEPDIR=.deps depmode=none /bin/bash ../depcomp \ cc-5.0 -DHAVE_CONFIG_H -I. -I. -I../include -I../include -I../include -Xa -xstrconst -g -mt -D_FORTEC_ -m64 -DHAVE_RWLOCK_T -DUNIV_SOLARIS -c factorial.c /bin/bash ../libtool --preserve-dup-deps --tag=CC --mode=link cc-5.0 -Xa -xstrconst -g -mt -D_FORTEC_ -m64 -DHAVE_RWLOCK_T -DUNIV_SOLARIS -o factorial my_main.o factorial.o libdbug.a ../mysys/libmysys.a ../strings/libmystrings.a -lpthread -lthread -lposix4 -lsocket -lnsl -lm -lmtmalloc -lpthread -lthread libtool: link: cc-5.0 -Xa -xstrconst -g -mt -D_FORTEC_ -m64 -DHAVE_RWLOCK_T -DUNIV_SOLARIS -o factorial my_main.o factorial.o libdbug.a ../mysys/libmysys.a ../strings/libmystrings.a -lpthread -lthread -lposix4 -lsocket -lnsl -lm -lmtmalloc -lpthread -lthread -mt ld: warning: file libdbug.a ignored: unable to locate archive symbol table ld: warning: file ../mysys/libmysys.a ignored: unable to locate archive symbol table ld: warning: file ../strings/libmystrings.a ignored: unable to locate archive symbol table Undefined first referenced symbol in file my_thread_global_init my_main.o ld: fatal: Symbol referencing errors. No output written to factorial gmake[1]: *** [factorial] Error 1 gmake[1]: Leaving directory `/home/mysql/jgrill/mariadb-5.1.42/dbug' gmake: *** [all-recursive] Error 1 Here's my OS release information: # cat /etc/release Solaris 10 10/09 s10x_u8wos_08a X86 Copyright 2009 Sun Microsystems, Inc. All Rights Reserved. Use is subject to license terms. Assembled 16 September 2009 Thanks for taking the time to read this far. :-) Any help would be greatly appreciated. I've tried everything I can think of and have spent many hours doing so. I'm pretty excited to try out XtraDB, PBXT, and, of course, MariaDB. -- - Jim Grill

2 2