- discuss - lists.mariadb.org

[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 08 Jun '11

08 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 04 Jun '11

04 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