MariaDB

  1. Bug #1046882
  2. Comment #4

Comment 4 for bug 1046882

Revision history for this message
Daniel Heimann (denu) wrote :

hi Elena,

I went through the following steps (after turning general_log=1):

1. phpmyadmin -> bad-query (empty result-set)
2. cli -> bad-query (empty result-set)

3. cli -> good-query (non-empty result-set)
4. cli -> bad-query (non-empty result-set)

5. phpmyadmin -> bad (non-empty result-set)
6. phpmyadmin -> bad (empty result-set)

Then I reexecuted 1 - 6 with "EXPLAIN EXTENDED" in front of it.
Sorry, phpmyadmin generates some more queries in the log, to render its output.

general log is:

/usr/sbin/mysqld, Version: 5.5.25-MariaDB-mariadb1~squeeze (mariadb.org binary distribution). started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
120907 11:30:13 13572 Connect root@localhost as anonymous on
  13572 Query SET CHARACTER SET 'utf8'
  13572 Query SET collation_connection = 'utf8_general_ci'
  13572 Init DB mysql
  13572 Query SHOW TABLES LIKE 'user'
  13572 Init DB mysql
  13572 Query SHOW TABLE STATUS FROM `mysql` LIKE 'user%'
  13572 Query SHOW FULL COLUMNS FROM `mysql`.`user`
  13572 Quit
120907 11:30:31 13573 Connect root@localhost as anonymous on
  13573 Query SET CHARACTER SET 'utf8'
  13573 Query SET collation_connection = 'utf8_general_ci'
  13573 Init DB mysql
  13573 Init DB mysql
  13573 Query SELECT a.* FROM mysql.user a WHERE ( SELECT EXISTS ( SELECT 1 FROM mysql.user b WHERE b.user = a.user LIMIT 1 ) )
 LIMIT 0, 30
  13573 Query SELECT SQL_CALC_FOUND_ROWS a . * FROM mysql.user a WHERE ( SELECT EXISTS (
SELECT 1
FROM mysql.user b
WHERE b.user = a.user
LIMIT 1 ) ) LIMIT 1
  13573 Query SELECT FOUND_ROWS()
  13573 Init DB mysql
  13573 Query SHOW TABLES LIKE 'user'
  13573 Init DB mysql
  13573 Query SHOW TABLE STATUS FROM `mysql` LIKE 'user%'
  13573 Query SELECT COUNT(*) FROM `mysql`.`user`
  13573 Query SHOW FULL COLUMNS FROM `mysql`.`user`
  13573 Quit
120907 11:33:59 13571 Query SELECT a.* FROM mysql.user a WHERE ( SELECT EXISTS ( SELECT 1 FROM mysql.user b WHERE b.user = a.user LIMIT 1 ) )
120907 11:34:11 13571 Query SELECT a.* FROM mysql.user a WHERE ( EXISTS ( SELECT 1 FROM mysql.user b WHERE b.user = a.user LIMIT 1 ) )
120907 11:34:16 13571 Query SELECT a.* FROM mysql.user a WHERE ( SELECT EXISTS ( SELECT 1 FROM mysql.user b WHERE b.user = a.user LIMIT 1 ) )
120907 11:34:59 13574 Connect root@localhost as anonymous on
  13574 Query SET CHARACTER SET 'utf8'
  13574 Query SET collation_connection = 'utf8_general_ci'
  13574 Init DB mysql
  13574 Init DB mysql
  13574 Query SELECT a.* FROM mysql.user a WHERE ( SELECT EXISTS ( SELECT 1 FROM mysql.user b WHERE b.user = a.user LIMIT 1 ) )
 LIMIT 0, 30
  13574 Query SELECT SQL_CALC_FOUND_ROWS a . * FROM mysql.user a WHERE ( SELECT EXISTS (
SELECT 1
FROM mysql.user b
WHERE b.user = a.user
LIMIT 1 ) ) LIMIT 1
  13574 Query SELECT FOUND_ROWS()
  13574 Init DB mysql
  13574 Query SHOW TABLES LIKE 'user'
  13574 Init DB mysql
  13574 Query SHOW TABLE STATUS FROM `mysql` LIKE 'user%'
  13574 Query SELECT COUNT(*) FROM `mysql`.`user`
  13574 Query SHOW INDEX FROM `mysql`.`user`
  13574 Query SHOW FULL COLUMNS
        FROM `mysql`.`user`
  13574 Quit
120907 11:35:05 13575 Connect root@localhost as anonymous on
  13575 Query SET CHARACTER SET 'utf8'
  13575 Query SET collation_connection = 'utf8_general_ci'
  13575 Init DB mysql
  13575 Init DB mysql
  13575 Query SELECT a.* FROM mysql.user a WHERE ( SELECT EXISTS ( SELECT 1 FROM mysql.user b WHERE b.user = a.user LIMIT 1 ) )
 LIMIT 0, 30
  13575 Query SELECT SQL_CALC_FOUND_ROWS a . * FROM mysql.user a WHERE ( SELECT EXISTS (
SELECT 1
FROM mysql.user b
WHERE b.user = a.user
LIMIT 1 ) ) LIMIT 1
  13575 Query SELECT FOUND_ROWS()
  13575 Init DB mysql
  13575 Query SHOW TABLES LIKE 'user'
  13575 Init DB mysql
  13575 Query SHOW TABLE STATUS FROM `mysql` LIKE 'user%'
  13575 Query SELECT COUNT(*) FROM `mysql`.`user`
  13575 Query SHOW INDEX FROM `mysql`.`user`
  13575 Query SHOW FULL FIELDS FROM `user`
  13575 Query SHOW CREATE TABLE `mysql`.`user`
  13575 Query SHOW FULL COLUMNS
        FROM `mysql`.`user`
  13575 Query SHOW TRIGGERS FROM `mysql` LIKE 'user'
  13575 Quit
120907 11:37:51 13576 Connect root@localhost as anonymous on
  13576 Query SET CHARACTER SET 'utf8'
  13576 Query SET collation_connection = 'utf8_general_ci'
  13576 Init DB mysql
  13576 Query SHOW TABLES LIKE 'user'
  13576 Init DB mysql
  13576 Query SHOW TABLE STATUS FROM `mysql` LIKE 'user%'
  13576 Query SHOW FULL COLUMNS FROM `mysql`.`user`
  13576 Quit
120907 11:37:57 13577 Connect root@localhost as anonymous on
  13577 Query SET CHARACTER SET 'utf8'
  13577 Query SET collation_connection = 'utf8_general_ci'
  13577 Init DB mysql
  13577 Init DB mysql
  13577 Query EXPLAIN EXTENDED SELECT a.* FROM mysql.user a WHERE ( SELECT EXISTS ( SELECT 1 FROM mysql.user b WHERE b.user = a.user LIMIT 1 ) )
  13577 Init DB mysql
  13577 Query SHOW TABLES LIKE 'user'
  13577 Init DB mysql
  13577 Query SHOW TABLE STATUS FROM `mysql` LIKE 'user%'
  13577 Query SELECT COUNT(*) FROM `mysql`.`user`
  13577 Quit
120907 11:38:13 13571 Query EXPLAIN EXTENDED SELECT a.* FROM mysql.user a WHERE ( SELECT EXISTS ( SELECT 1 FROM mysql.user b WHERE b.user = a.user LIMIT 1 ) )
120907 11:38:24 13571 Query EXPLAIN EXTENDED SELECT a.* FROM mysql.user a WHERE ( EXISTS ( SELECT 1 FROM mysql.user b WHERE b.user = a.user LIMIT 1 ) )
120907 11:38:41 13571 Query EXPLAIN EXTENDED SELECT a.* FROM mysql.user a WHERE ( SELECT EXISTS ( SELECT 1 FROM mysql.user b WHERE b.user = a.user LIMIT 1 ) )
120907 11:39:02 13578 Connect root@localhost as anonymous on
  13578 Query SET CHARACTER SET 'utf8'
  13578 Query SET collation_connection = 'utf8_general_ci'
  13578 Init DB mysql
  13578 Query SHOW TABLES LIKE 'user'
  13578 Init DB mysql
  13578 Query SHOW TABLE STATUS FROM `mysql` LIKE 'user%'
  13578 Query SHOW FULL COLUMNS FROM `mysql`.`user`
  13578 Quit
120907 11:39:08 13579 Connect root@localhost as anonymous on
  13579 Query SET CHARACTER SET 'utf8'
  13579 Query SET collation_connection = 'utf8_general_ci'
  13579 Init DB mysql
  13579 Init DB mysql
  13579 Query EXPLAIN EXTENDED SELECT a.* FROM mysql.user a WHERE ( SELECT EXISTS ( SELECT 1 FROM mysql.user b WHERE b.user = a.user LIMIT 1 ) )
  13579 Init DB mysql
  13579 Query SHOW TABLES LIKE 'user'
  13579 Init DB mysql
  13579 Query SHOW TABLE STATUS FROM `mysql` LIKE 'user%'
  13579 Query SELECT COUNT(*) FROM `mysql`.`user`
  13579 Quit
120907 11:39:12 13580 Connect root@localhost as anonymous on
  13580 Query SET CHARACTER SET 'utf8'
  13580 Query SET collation_connection = 'utf8_general_ci'
  13580 Init DB mysql
  13580 Query SHOW TABLES LIKE 'user'
  13580 Init DB mysql
  13580 Query SHOW TABLE STATUS FROM `mysql` LIKE 'user%'
  13580 Query SHOW FULL COLUMNS FROM `mysql`.`user`
  13580 Quit
120907 11:39:15 13581 Connect root@localhost as anonymous on
  13581 Query SET CHARACTER SET 'utf8'
  13581 Query SET collation_connection = 'utf8_general_ci'
  13581 Init DB mysql
  13581 Init DB mysql
  13581 Query EXPLAIN EXTENDED SELECT a.* FROM mysql.user a WHERE ( SELECT EXISTS ( SELECT 1 FROM mysql.user b WHERE b.user = a.user LIMIT 1 ) )
  13581 Init DB mysql
  13581 Query SHOW TABLES LIKE 'user'
  13581 Init DB mysql
  13581 Query SHOW TABLE STATUS FROM `mysql` LIKE 'user%'
  13581 Query SELECT COUNT(*) FROM `mysql`.`user`
  13581 Quit
120907 11:39:40 13571 Query set global general_log=0

my.cnf (grep -vE "^(#.*|)$" my.cnf) is:

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
mysqld = mysqld
syslog
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc_messages_dir = /usr/share/mysql
lc_messages = en_US
skip-external-locking
skip-name-resolve
bind-address = 172.30.1.64
ft_min_word_len = 2
max_connections = 100
max_allowed_packet = 16M
thread_stack = 256K
thread_cache_size = 128
sort_buffer_size = 2M
bulk_insert_buffer_size = 8M
tmp_table_size = 256M
max_heap_table_size = 256M
join_buffer_size = 128K
connect_timeout = 10
wait_timeout = 600
table_open_cache = 20480
open_files_limit = 40960
myisam_recover = BACKUP
key_buffer_size = 128M
myisam_sort_buffer_size = 32M
concurrent_insert = AUTO
read_buffer_size = 2M
read_rnd_buffer_size = 1M
query_cache_limit = 1M
query_cache_size = 512M
query_cache_type = OFF
general_log_file = /var/log/mysql/mysql.log
general_log = 0
log_warnings = 2
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 10
log_slow_verbosity = query_plan
expire_logs_days = 10
max_binlog_size = 100M
default_storage_engine = InnoDB
innodb_buffer_pool_size = 1024M
innodb_log_buffer_size = 8M
innodb_file_per_table = ON
innodb_open_files = 1280
innodb_io_capacity = 400
innodb_flush_method = O_DIRECT
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_flush_log_at_trx_commit = 2
blackhole = OFF
performance_schema = 0
archive = OFF
federated = OFF
feedback = OFF
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 16M
!includedir /etc/mysql/conf.d/

included stuff in /etc/mysql/conf.d/* is just:
[client]
default-character-set = latin1
[mysqld_safe]
syslog

Thank you very much, for guidance. What else, is needed?