- discuss - lists.mariadb.org

[Maria-discuss] Silencing "Unsafe statement written to the binary log" log warnings
by Vincent Pelletier 25 Jun '13

25 Jun '13

Hi. We get 500k such lines per day: 130517 0:00:02 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE [...] Is there a way to silence them ? Intuitively, I would expect a companion option to innodb_locks_unsafe_for_binlog, but couldn't find one in the documentation. The queries triggering those warnings are harmless in our case because the update is rolled back at application level on restart (which necessarily happens in case of disaster recovery involving restoring incremental backups). If you want more details to understand what we are doing - shouldn't be needed for this discussion: The table involved is used as a shared task queue. The update happens when a process wants to process a bunch of tasks, up to a limit. Once a task is over, it is deleted by its primary key. So any task assigned to a worker's identifier upon worker startup can be safely assumed to have to be processed, and is released by that worker (effectively rolling back UPDATE's action). This only represents 2MB gzipped, so we can live with it if we must, but it causes undesired noise which may hide real issues. Regards, -- Vincent Pelletier ERP5 - open source ERP/CRM for flexible enterprises

3 3

[Maria-discuss] New Question: Issue with symlinked database directories loading under windows
by AskMonty KB 25 Jun '13

25 Jun '13

Hello, A new question has been asked in "Using MariaDB" by scottharman: -------------------------------- Hi all, I'm having trouble getting symlinked databases to work under 5.5.31 of MariaDB Currently the databases are held on a different drive, and I've tried both Symlinks and Junctions under windows to get them working under MariaDB Both methods work equally well under MySQL 5.5.31, and I've enabled symlink support in my.ini for Mariadb Any thoughts on why it wouldn't be working? -------------------------------- To view or answer this question please visit: http://kb.askmonty.org/en/issue-with-symlinked-database-directories-loading…

1 0

[Maria-discuss] New Question: Downloading MariaDB and starting the installation procedure
by AskMonty KB 23 Jun '13

23 Jun '13

Hello, A new question has been asked in "Getting, Installing, and Upgrading MariaDB" by tsperry: -------------------------------- I've downloaded the package several times but the installation feature won't start. What am I doing wrong? -------------------------------- To view or answer this question please visit: http://kb.askmonty.org/en/downloading-mariadb-and-starting-the-installation…

1 0

[Maria-discuss] New Question: Downloading MariaDB and starting the installation procedure
by AskMonty KB 23 Jun '13

23 Jun '13

Hello, A new question has been asked in "Getting, Installing, and Upgrading MariaDB" by tsperry: -------------------------------- I've downloaded the package several times but the installation feature won't start. What am I doing wrong? -------------------------------- To view or answer this question please visit: http://kb.askmonty.org/en/getting-installing-and-upgrading-mariadb-download…

1 0

[Maria-discuss] New Question: Windows Authentication on AD
by AskMonty KB 20 Jun '13

20 Jun '13

Hello, A new question has been asked in "Plugins" by nobi: -------------------------------- Hi all, I have a question concerning the authentication plugin interface in MariaDB. We would use MariaDB on a Windows OS and want to authenticate the users against Active Directory. Additionally the users, who have logged in to Windows, should be able to connect from client programs to the server based on the token information in their environment without specifying an additional password (Single-Sign-On functionallity). MySQL sells this function in the commercial MySQL Enterprise version http://www.mysql.com/products/enterprise/security.html I have seen articles about authentication with PAM in the linux environment but found nothing about authentication issues in Windows world. Does there exist something that can be used? Best regards, nobi -------------------------------- To view or answer this question please visit: http://kb.askmonty.org/en/windows-authentication-on-ad/

1 0

[Maria-discuss] New Question: MariaDB SSL connection issues
by AskMonty KB 19 Jun '13

19 Jun '13

Hello, A new question has been asked in "Getting Started" by bacterie: -------------------------------- Hi, After switching from Mysql to MariaDB ( Server version: 5.5.31-MariaDB-1~squeeze-log ) we lost the ability to connect via SSL to the server. We were using self signed certificates generated via the method described here and they worked well before the switch to MariaDB : http://dev.mysql.com/doc/refman/5.5/en/creating-ssl-certs.html We get the following error messages when trying to connect on the same machine having bot the client and the server. mysql -v -u user -p database --ssl-ca /etc/mysql/newcerts/ca-cert.pem --ssl-cert /etc/mysql/newcerts/client-cert.pem --ssl-key /etc/mysql/newcerts/client-key.pem Enter password: ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1) Any hint is appreciated. Thanks. -------------------------------- To view or answer this question please visit: http://kb.askmonty.org/en/mariadb-ssl-connection-issues/

1 0

[Maria-discuss] New Question: Aria Max Table Size for internal on-disk temporary tables
by AskMonty KB 17 Jun '13

17 Jun '13

Hello, A new question has been asked in "Aria" by trsystran: -------------------------------- Using MariaDB 5.5.31 (deb file for ubuntu 12.04 amd_64: 5.5.31+maria-1~precise). When a query requires an internal temporary tables, and this tables becomes too large to be kept in ram, the table is moved to disk using Aria engine. If the on-disk .MAD file reaches 4GB, an error occurs: {{{ [ERROR] mysqld: The table '/tmp/#sql_74e_0' is full }}} How can we change this limit? In https://kb.askmonty.org/en/aria-max-table-size/ it is recommended to change MAX_ROW, but it only applies for explicitly created temporary tables. Looking at the code I found this in maria_create(): {{{ data_file_length= ((((ulonglong) 1 << ((BLOCK_RECORD_POINTER_SIZE-1) * 8))/2 -1) * maria_block_size); }}} Which amounts to 4GB with default values (8192 for maria_block_size). I created a new DB with aria-block-size = 32768 (the max value), and it still failed at 4GB. Even if it worked, it would mean the max size is only 16GB, and setting a large block size may have performance penalties. So what is the proper fix to increase this limit? With MyISAM as on-disk temporary tables engine the limit seems to be much higher (at least 14GB from experiments). -------------------------------- To view or answer this question please visit: http://kb.askmonty.org/en/aria-max-table-size-for-internal-on-disk-temporar…

4 7

[Maria-discuss] New Question: compile mariadb from source
by AskMonty KB 17 Jun '13

17 Jun '13

Hello, A new question has been asked in "MariaDB community" by raph: -------------------------------- i try to compile mariadb from source and got error oqgraph not found, so i put option in cmake command -DWITHOUT_OQGRAPH_STORAGE_ENGINE=1 every thing work fine. my os is opensuse11.4 . how do i compile with oqgraph support. thanks -------------------------------- To view or answer this question please visit: http://kb.askmonty.org/en/compile-mariadb-from-source/

1 0

[Maria-discuss] New Question: Executing a command file from within a command file?
by AskMonty KB 17 Jun '13

17 Jun '13

Hello, A new question has been asked in "mysql client" by JohnD: -------------------------------- I have a bunch of things I'd like to execute one after the other. A simple example would be: - drop table x; - drop table y; - create table y by executing create_table_y.sql - create table x by executing create_table_x.sql With the mysql command line, I can execute "drop table x;", and I can execute a SQL script with -e "source create_table_x.sql". But what is the good way to bundle all these together into a script which will do all the actions? The only way I can think of is to create a batch file which will call out to mysql for each action. And pass the password as an argument to the batch file. Like this: mysql -u root -p mypassword -e "drop table x;" mysql -u root -p mypassword -e "drop table y;" mysql -u root -p mypassword -e "source create_table_y.sql" mysql -u root -p mypassword -e "source create_table_x.sql" But this seems fairly clunky. Is there a better, or even best practice? John D. -------------------------------- To view or answer this question please visit: http://kb.askmonty.org/en/executing-a-command-file-from-within-a-command-fi…

1 0

[Maria-discuss] Memory Leak?
by Steven Barre 14 Jun '13

14 Jun '13

I'm testing out MariaDB 5.5 as a replacement for MySQL, but it seems to be leaking memory. I can't figure out what is causing the RAM usage to keep growing until oom-killer kills it. Here is the output in TOP. System has 8G RAM. PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 7411 mysql 20 0 8861m 7.1g 5212 S 43.9 92.5 89:12.19 mysqld There are no active connections # mysql -e "SHOW PROCESSLIST\G" *************************** 1. row *************************** Id: 853 User: root Host: localhost db: NULL Command: Query Time: 0 State: NULL Info: SHOW PROCESSLIST Progress: 0.000 System Details # uname -a Linux idxdb-carets 2.6.32-358.6.2.el6.x86_64 #1 SMP Thu May 16 20:59:36 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux # cat /etc/redhat-release CentOS release 6.4 (Final) # rpm -qa MariaDB-server MariaDB-server-5.5.31-1.x86_64 # cat /etc/my.cnf [mysqld] aria_pagecache_buffer_size = 32M aria_sort_buffer_size = 32M datadir = /var/lib/mysql default_storage_engine = InnoDB innodb = FORCE innodb_buffer_pool_size = 2G innodb_file_format = barracuda innodb_file_per_table innodb_flush_log_at_trx_commit = 2 innodb_log_file_size = 128M innodb_log_files_in_group = 2 key_buffer_size = 32M log_error = /var/log/mysql/mysql.log log_queries_not_using_indexes = 1 max_allowed_packet = 16M max_connect_errors = 1000000 max_connections = 100 max_heap_table_size = 32M myisam-recover = FORCE,BACKUP open-files-limit = 65535 pid-file = /var/run/mysql/mysql.pid query_cache_limit = 1M query_cache_size = 16M query_cache_type = 2 slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log socket = /var/lib/mysql/mysql.sock sysdate_is_now = 1 table_definition_cache = 1024 table_open_cache = 2048 thread_cache_size = 8 tmp_table_size = 32M user = mysql [mysqld_safe] nice = 0 pid-file = /var/run/mysql/mysql.pid socket = /var/lib/mysql/mysql.sock I just don't understand how it is getting up to 7G when innodb_buffer_pool_size = 2G ? There is a cron running every hour that seems to cause the memory to grow when it runs a query like > REPLACE INTO t1 SELECT * FROM t2; And it would be fine if the memory went up during that and went back down after, but it stays high. Any ideas where I should start digging?

1 0