MySQL Forums - InnoDB

about compress table (no replies)

h h — Fri, 17 May 2013 08:05:30 +0000

hi,all
i readed something about compress table from doc ,it says if the number of compress failure great than innodb_compression_failure_threshold_pct mysql will leave more free space within each new compressed page,

my question is:
1, how to calculate innodb_compression_failure_threshold_pct, the number of compress failure/ what?? = innodb_compression_failure_threshold_pct
2, how can mysql leave more free space to compressed page?
from doc context ,i guess mysql will split the old compress page to two new compress page
3, how can i monitor the times which the number of compress failure and the number of page split? so i can adjust the variable according the situation

java.sql.SQLException: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level (1 reply)

Darshana Samaraweera — Tue, 14 May 2013 17:25:25 +0000

Hello,

MySql Version - 5.5.25

I have a Servoy application which use MySql server as a Database. All of my tables are created using InnoDB engine. It was working without any issues but one day it threw below error message. As per the error I changed the binlog_format to RAW and also MIXED but none of it worked then I decided to change the engine to MyISAM and application works fine again.

Before this error happens the only thing I was trying to do create some indexes.
I tried to create index on a table but I had to kill it because it was struck in the middle. I thought it was due to connections to the DB. Then I restarted the service and created the indexes successfully. This error occur after that incident but it was running without any issues before.

So I decided to remove all the indexes and run the application but never success. It was only success after I changed storage engine to MyISAM.

Could anyone help me to figure out this issue?

java.sql.SQLException: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED. Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

Thank You

moving binlog_format in M-M while running (1 reply)

kevin foote — Wed, 15 May 2013 03:21:52 +0000

Hi all

MySQL Community 5.1.41
RHEL-v5

I'm getting a "Transaction level 'READ-COMMITTED' in InnoDB...." Error while writing to a new DB in my M-M configuration.

I've followed these references for this particular bug/issue.

here -> http://bugs.mysql.com/bug.php?id=47842 &
here -> http://bugs.mysql.com/bug.php?id=40360

I've decided to move binlog_format to MIXED which should eliminate my issue.

It would be best to do this on the fly with no restart / of mysql.
So I'm wondering if its safe in a M-M environment to issue the SET GLOBAL binlog_format='MIXED'; command to both servers at the same time.

This will obviously have an effect on the current running transactions as well as those in the immediate pipeline.. Will this cause any ill effects to data being served?

Relevant current innodb and bin-log sections of my.cnf below..

## INNODB SETTINGS
innodb_data_home_dir = /var/lib/mysql/ibdata
innodb_data_file_path = ib_data1:100M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/ibdata_logs
innodb_log_file_size = 256M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 32M
innodb_buffer_pool_size = 4G
innodb_additional_mem_pool_size = 20M
innodb_support_xa = 1
#innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_max_dirty_pages_pct = 80
#innodb_thread_concurrency = 16
#innodb_commit_concurrency = 8

## BINLOG SETTINGS
log-bin = /var/log/mysql/my2-bin
log-bin-index = /var/log/mysql/my2-bin.index
expire_logs_days = 4
sync_binlog = 1

- kpf

how to recover the lost innodb table due to inode full (no replies)

Justin Xu — Mon, 13 May 2013 10:34:16 +0000

Hi ,
the OS is solaris , today the file system inode was full , so the Mysql error happened. and one table's ibd file lost .
The error log is below.

130513 15:43:00 [ERROR] /data1/local/mysql-5.5.27-solaris10-x86_64/bin/mysqld: Sort aborted: Can't create/write to file '/usr/local/mysql5527/data/MYjsl9bl' (Errcode: 28)
130513 15:43:01 InnoDB: cannot calculate statistics for table mydb55/bustab_201305
InnoDB: because the .ibd file is missing. For help, please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html

130513 15:43:01 InnoDB: Error:
InnoDB: MySQL is trying to use a table handle but the .ibd file for
InnoDB: table mydb55/bustab_201305 does not exist.
InnoDB: Have you deleted the .ibd file from the database directory under
InnoDB: the MySQL datadir, or have you used DISCARD TABLESPACE?
InnoDB: Look from
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html
InnoDB: how you can resolve the problem.
130513 15:43:01 [ERROR] /data1/local/mysql-5.5.27-solaris10-x86_64/bin/mysqld: Got error -1 from storage engine
130513 15:43:01 [ERROR] /data1/local/mysql-5.5.27-solaris10-x86_64/bin/mysqld: Sort aborted: Got error -1 from storage engine
130513 15:43:13 [ERROR] /data1/local/mysql-5.5.27-solaris10-x86_64/bin/mysqld: Sort aborted: Can't create/write to file '/usr/local/mysql5527/data/MYksl9bl' (Errcode: 28)
130513 15:43:13 [ERROR] /data1/local/mysql-5.5.27-solaris10-x86_64/bin/mysqld: Sort aborted: Can't create/write to file '/usr/local/mysql5527/data/MYlsl9bl' (Errcode: 28)

Thanks.

how to upgrade database schema in M-M environment (2 replies)

h h — Tue, 14 May 2013 03:05:08 +0000

hi:
i have a mysql master-master (active/passive) product environment,developer want to upgrade their app to new version,so lots of change need to be apply on database schema,and the database must keep online,how can i do the upgrade process? anyone can help? thanks.

Query stuck on null state (1 reply)

Eric Coll — Thu, 09 May 2013 02:46:58 +0000

We have a query that executes in less than a second.

The same query executed dynamically from a store procedures that is run by an external java process gets stuck holding locks with state null and no progress.

We found a related bug around the query cache, but setting it to zero makes no difference.

Is this a know bug? any work arounds?

ibdata growing too fast (1 reply)

Henjo van Rees — Thu, 09 May 2013 02:56:45 +0000

I have inherited a running MySQL production server (5.5.19) which seems to be running out of diskspace rapidly (5GB free).

MySQL datadir = /data/mysql5519_prd/

/data/mysql5519_prd/ibdata is now 42GB, 2 days ago 40GB so growing fast.

All database tables already use separate .ibd files, those are present next to the .frm files.

my.conf contains these relevant innodb properties
innodb_data_home_dir = /data/mysql5519_prd
innodb_data_file_path = ibdata:512M:autoextend
innodb_log_group_home_dir = /data/mysql5519_prd
innodb_buffer_pool_size = 512M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 100M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_file_per_table

So, both innodb_file_per_table is present AND a tablespace file.

This seems superfluous, and I want to get rid of the large ibdata file.

I know that it's normally only possible to get rid of this ibdata file by dumping and reimporting in a clean mysql instance.

However, since innodb_file_per_table is already present, is there a simpler way?

AFTER INSERT Triggers execute in serial or in parallel? (4 replies)

Pedro Guillem — Thu, 09 May 2013 03:03:29 +0000

Hi all.

I'm trying to process CDR records (bill phone calls) using an AFTER INSERT trigger. I figured this is best than having a third party app do it each minute.

The sequence is as follows:

1) A phone call is finished and a new call record is inserted on table 1.

2) A trigger is fired AFTER inserting a row in table 1.

3) The trigger performs 5 select statements to bill the call and inserts the billed record on table 2.

4) The trigger updates the balance of the account owner of the call and finishes.

The biggest problem i´m seeing here is i will be billing at least 50 calls per second and i don´t know the exact behavior this will cause; since the balance of the account is being read before the process and updated afterwards.

My question is simple: If 50 cdrs are created in 1 second, will the fired triggers execute in parallel? or serialized?... This is vital, as if they execute in parallel the balance of the account may not be updated in order.

Out of the 5 queries performed to calculate the call, 2 of them are performed over a 40.000 row table holding the rates. This is taking 0.230 seconds per inner query, but the balance is updated at the very end of the script.

If two fired triggers start at the same time they will start processing the call with the same balance, and it will be useless.

Any toughts?

5-6-10 - DDL ONLINE for ALTER TABLE and temp tables (1 reply)

Didier Dieudonne — Sat, 04 May 2013 19:39:44 +0000

Hello all,

I have several questions about MySQL5.6-10 version and online DDL.
I have several quite big tables (several Gb) with several hundreds of INSERTs per second on them and some reads in parallel.
Problem is coming from the modification of table structure online.
I can't prevent transactions from reading or updating the data stored in theses tables when I am obliged to change the table structure (i.e. add 1 or more columns in).
With older versions of MySQL (< 5.5), creation (or deletion) of 1 or more columns implied that an exclusive lock was done on table during the ALTER command was running.
I wanted to test the Online DDL feature of MySQL 5.6.10 (with InnoDB engine 1.2.10), that seems to be improved from this point of view, allowing the updates/reads during the ALTER TABLE ADD col thanks to the option LOCK.
(http://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-syntax.html)
Some quick tests (add 1 simple varchar(2) column at the end of table) on a small table effectively confirm that an insert can be performed while the ALTER command is still processing.
Some other tests on a bigger table of 5 Gb were more difficult.
But, it seems I have a problem with this feature:
1) ALTER TABLE creates a temporary table in the same directory as the original table (http://dev.mysql.com/doc/refman/5.6/en/temporary-files.html).
While the documentation says that tempo tables created by ALTER TABLE are in the same directory that original table, if tmpdir variable is not set in the my.cnf (so the server uses /tmp as default underUnix), the ALTER TABLE command aborts after several seconds with an error saying that my table is full. In fact, my file system /tmp is too small (512Mb) and can't contain what I think to be the copy of the changed table or part of it. If I use a bigger file system by specifying it in the tmpdir variable, The ALTER TABLE finishes OK.
I would conclude that the documentation does not reflect the reality, else what is created in the temporary area tmpdir ?
I even added the option ALGORITHM=INPLACE to be sure , but result is the same.
2) For question of performances, we wanted to install this tempo area on RAMDisk. But, if all my tempo tables created by ALTER TABLE also go on RAM Disk, this will not be very efficient and we have a risk to saturate the RAMDisk
Is there a possibility to differenciate tempo zones used by ALTER TABLE than those used for other operations?

If you have any suggestions ... don't hesitate !
Thanks in advance.
DD

InnoDB on RAMdisk optimization for specific purpose (2 replies)

Alexey Asemov — Sat, 04 May 2013 07:58:05 +0000

Hello, everyone.

Can someone recommend some parameters to be tuned if the InnoDB logs/tablespaces are stored on RAMdisk? Especially to use less memory for MySQL server having the tables located in RAM in mind.

Yes, I know what I am doing. It is a network appliance that does not require tables to persist across reboots and the entire database is recreated at system start by the application code. I need tables to persist if the MySQL server crashes by some accident so I cannot use MEMORY tables. I need transaction control and MVCC as well for a decent number of tps.

Is decreasing of InnoDB buffer pool to 16Mb or less is viable here, or will it cause severe performance loss? Are there some other parameters that can be tuned to reduce MySQL memory usage in such a case without sacrificing much performance?

Does anyone have any experience running InnoDB on RAMdisk as well? If so, can you please share any of it? I have already tuned parameters by myself, but there is probably still enough space to do it even better.

Can I set innodb O_DIRECT for ZFS file system ? (no replies)

Justin Xu — Sat, 27 Apr 2013 02:23:17 +0000

I'm using the mysql 5.5.30 innodb engine , the data are saved in a RAID5 storage array , the partition of storage is configured as ZFS file system, Solaris 10 OS.

Can I set innodb_flush_method parameter to O_DIRECT ? From some article , the ZFS doesn't support O_DIRECT , is it correct ?

please advise , thanks.

Justin

mysqldump skips table for no apparent reason (2 replies)

Pipo K — Wed, 08 May 2013 08:36:02 +0000

i am having trouble making a mysqldump import work.
what i want to do is to set up a slave so i run a mysqldump command (with the flag 'single-transaction' and 'master-data=1').

the problem is that from the export that is created, a (very big) table is always missing.
the server's version is 5.1 and the storage engine used in the specific database that i want to export is InnoDB.

have you ever faced such an issue? what could be the cause of this?

thx in advance for your help

Concurrent INSERT/DELETE (5 replies)

Jean-Philippe Signorino — Fri, 03 May 2013 19:21:24 +0000

Hi,

I'm trying to solve concurrent locking on my application. I identified a scenario and I don't know how t solve it.

I have a single table T with a column 'id'.
Transaction are in READ COMMITED mode.

First, I start a transaction where I execute:
INSERT INTO T (`id`) VALUES (1);

Then I start a second transaction where i execute :
delete from T where id in (3,4,5,6);

The last query is blocked par locks . If I execute "delete from T where id=3" the query is not locked.

SHOW ENGINE INNODB STATUS give me :

------- TRX HAS BEEN WAITING 10 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 3776 n bits 72 index `PRIMARY` of table T trx id 171E2D lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

Is there a way to prevent query to be locked ?

MySQL - How to create tablespaces for innodb engine (2 replies)

vasudevan dandey — Thu, 25 Apr 2013 03:55:43 +0000

How can we create/configure tablespace for innodb engine?
We want to enable 'file per table' mode so that it creates .ibd file and saves data for all the tables - instead of using shared tablespace (default).
When we tried using set commands like as follows, its giving errors:

SET @df_path = 'file_dat';
SET @tbl_space_name ='file_dat';

CREATE tablespace @tbl_space_name
add datafile "@df_path"
engine = InnoDB;

Please help us regarding how we can create tablespaces using scripts.

Measuring memory used by temp_table_size (4 replies)

Bryan Welch — Fri, 26 Apr 2013 16:25:32 +0000

What's a good way to measure the average and max amount of memory being used by temp_table size? For example, if I have temp_table_size = 512MB and allow up to 300 database connections, it looks like 150GB could theoretically be used. In reality, not every query uses the max amount of temp table space. I'd like to measure and see how much non-buffer pool memory is being by temp tables on average and at peak times.

thanks,
Bryan




mysql> show variables like '%buffer%';


+------------------------------+-------------+


| Variable_name                | Value       |


+------------------------------+-------------+


| bulk_insert_buffer_size      | 67108864    |


| innodb_buffer_pool_instances | 1           |


| innodb_buffer_pool_size      | 42949672960 |


| innodb_change_buffering      | all         |


| innodb_log_buffer_size       | 8388608     |


| join_buffer_size             | 67108864    |


| key_buffer_size              | 67108864    |


| myisam_sort_buffer_size      | 67108864    |


| net_buffer_length            | 16384       |


| preload_buffer_size          | 32768       |


| read_buffer_size             | 8388608     |


| read_rnd_buffer_size         | 33554432    |


| sort_buffer_size             | 67108864    |


| sql_buffer_result            | OFF         |


+------------------------------+-------------+

130422 InnoDB : Error : unable to create temporary file (1 reply)

Harikrishna Ravipati — Tue, 23 Apr 2013 03:15:20 +0000

Hi All,

I am new to Mysql, I am trying to install mysql in linux 5 CUI mode. I started with a) Setting up the basic environment
b) MySQL Installing MYSQL Database.

while running scripts/mysql_install_db --user=mysql i am getting errors and i am not able to run bin/mysqld safe --user=mysql & i am getting Issues.

I check the logs and i found below errors,

#cd /db/mysql/data

#tail -f test-lsm-r .err

/db/mysql/app/bin/mysqld: can't create/write to file '/db/mysql/tmp/ibDPBVqd' (Errorcode : 2)
130422 11:28:41 InnoDB: Error : Unable to create temporary file; errorno: 2
130422 11:28:41 [Error] Plugin 'InnoDB' init function returned error.
130422 11:28:41 [Error] plugin 'InnoDB' registration as a STORAGE ENGINE failed.
130422 11:28:41 [Error] Unkwon/unsupported storage engine: InnoDB
130422 11:28:41 [ERROR] Aborting

Kindly need any one of your support.

Note: I check /etc/init.d/
in that mysqld is not found it will come after DB create or before.

Regards,
Hari

What this message 'The InnoDB memory heap is disabled' means? (1 reply)

Flavio Tobias — Tue, 23 Apr 2013 05:59:17 +0000

Hi I just installed the MySql 5.6 and when I start it the following message appear in the log file:
[Note] InnoDB: The InnoDB memory heap is disabled

The database is working fine but I would like to know what this message means, I have googled it but I did not find a definitive answer.
I'm using the Debian 6 64 bits.

Many thanks

Data Organization in InnoDB (no replies)

Edwin Desouza — Fri, 19 Apr 2013 14:55:57 +0000

Data Organization in InnoDB
https://blogs.oracle.com/mysqlinnodb/entry/data_organization_in_innodb

Help with detected deadlock (1 reply)

Danny Lade — Sun, 21 Apr 2013 00:34:57 +0000

We are running a big delete statement with IN-clause to cleanup much older data. But the insert-statement shall insert only data with much newer Id's therefore the rows are not in the same ID-space.

Lets say:
ID 1 ... 20 - will be removed
ID 30 - is inserted
at the same time but at different rows.

We're locking on a row/rec basis, therefore (IMHO) it shouldn't be running in a deadlock - but it does.

We've read a lot of internet articles but non of this describes how the locking really works at innodDB nor how this special deadlock can happen.
(maybe we're reading the wrong docs and there is one we missed ...)

Help please,
Danny

------------------------
LATEST DETECTED DEADLOCK
------------------------
130418 10:39:00
*** (1) TRANSACTION:
TRANSACTION 3130F19, ACTIVE 0 sec, process no 22672, OS thread id 1080166736 fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 11 lock struct(s), heap size 3112, 1859 row lock(s), undo log entries 1850
MySQL thread id 554692, query id 47035826 10.188.14.235 test_user updating
DELETE FROM BIGOBJECT WHERE id IN (11811161272,11811161273,11811161274,11811161275,11811161276,11811161277,11811161278,11811161279,11811161271,11811161310,11811161311,11811161308,11811161309,11811161306,11811161307,11811161304,11811161305,11811161302,11811161303,11811161300,11811161301,11811161298,11811161299,11811161296,11811161297,11811161295,11811161294,11811161293,11811161292,11811161291,11811161290,11811161289,11811161288,11811161287,11811161286,11811161285,11811161284,11811161283,11811161282,11811161281,11811161280,11811161320,11811161317,11811161316,11811161319,11811161318,11811161313,11811161312,11811161315,11811161314,11811161107,11811161106,11811161105,11811161104,11811161110,11811161109,11811161108,11811161090,11811161091,11811161088,11811161089,11811161094,11811161095,11811161092,11811161093,11811161098,11811161099,11811161096,11811161097,11811161102,11811161103,11811161100,11811161101,11811161175,11811161174,1181
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 31468 n bits 424 index `PRIMARY` of table `testdb`.`BIGOBJECT` trx id 3130F19 lock_mode X waiting
Record lock, heap no 64 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 8; hex 80000002c000109e; asc ;;
1: len 6; hex 000003130f13; asc ;;
2: len 7; hex 800000016001e8; asc ` ;;
3: len 8; hex 80000002c0001099; asc ;;
4: len 8; hex 80000002c0000061; asc a;;
5: SQL NULL;
6: SQL NULL;

*** (2) TRANSACTION:
TRANSACTION 3130F13, ACTIVE 1 sec, process no 22672, OS thread id 1106278736 inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 28
MySQL thread id 554650, query id 47035835 10.188.14.235 test_user update
INSERT INTO BIGOBJECT (ID, CHILDID, PARENTID, SOURCEID, TARGETID) VALUES (11811164317, 11811164314, 11811160161, null, null)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 31468 n bits 424 index `PRIMARY` of table `testdb`.`BIGOBJECT` trx id 3130F13 lock_mode X locks rec but not gap
Record lock, heap no 64 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 8; hex 80000002c000109e; asc ;;
1: len 6; hex 000003130f13; asc ;;
2: len 7; hex 800000016001e8; asc ` ;;
3: len 8; hex 80000002c0001099; asc ;;
4: len 8; hex 80000002c0000061; asc a;;
5: SQL NULL;
6: SQL NULL;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 31468 n bits 424 index `PRIMARY` of table `testdb`.`BIGOBJECT` trx id 3130F13 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 64 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 8; hex 80000002c000109e; asc ;;
1: len 6; hex 000003130f13; asc ;;
2: len 7; hex 800000016001e8; asc ` ;;
3: len 8; hex 80000002c0001099; asc ;;
4: len 8; hex 80000002c0000061; asc a;;
5: SQL NULL;
6: SQL NULL;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 3130F97
Purge done for trx's n:o < 3130F2C undo n:o < 0
History list length 7
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started, process no 22672, OS thread id 1088567632
MySQL thread id 555789, query id 47040041 localhost root
show innodb status
---TRANSACTION 3130F96, not started, process no 22672, OS thread id 1093847376
MySQL thread id 555580, query id 47039724 localhost root
---TRANSACTION 3130F86, not started, process no 22672, OS thread id 1075808592
MySQL thread id 555311, query id 47039184 localhost root
---TRANSACTION 0, not started, process no 22672, OS thread id 1076336976
MySQL thread id 553985, query id 47036033 localhost root
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set, 1 warning (0.00 sec)

Mysql does not use a best index (1 reply)

Percy Xu — Sat, 20 Apr 2013 04:20:02 +0000

I have 2 indices:
KEY `I_member_id` (`member_id`),
KEY `I_admin_id` (`admin_id`,`member_id`),

When I make a sql query:
describe select * from bet_1366128000 where admin_id=1 and member_id=4741
The output is:

'1', 'SIMPLE', 'bet_1366128000', 'index_merge', 'I_member_id,I_admin_id', 'I_member_id,I_admin_id', '4,8', NULL, '114974', 'Using intersect(I_member_id,I_admin_id); Using where'

It uses an "index_merge" of 'I_member_id,I_admin_id'. But why doesn't it use only I_admin_id? The I_admin_id is the best one to speed up this query.

unable connect mysql server RedHat (1 reply)

chin tiau tan — Fri, 19 Apr 2013 14:38:10 +0000

Hi MYSQL expert

i've installed mysql in linux redhat , i can start the mysql deamon

[root@localhost mysql]# service mysql status
MySQL running (7240) [ OK ]

, but i failed to connect mysql server

i run the command " mysqld -u root "

the error message as below :

2013-04-18 12:18:33 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2013-04-18 12:18:33 7817 [Note] Plugin 'FEDERATED' is disabled.
2013-04-18 12:18:33 7817 [Note] InnoDB: The InnoDB memory heap is disabled
2013-04-18 12:18:33 7817 [Note] InnoDB: Mutexes and rw_locks use InnoDB's own implementation
2013-04-18 12:18:33 7817 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-04-18 12:18:33 7817 [Note] InnoDB: CPU does not support crc32 instructions
2013-04-18 12:18:33 7817 [Note] InnoDB: Using Linux native AIO
2013-04-18 12:18:33 7817 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2013-04-18 12:18:33 7817 [Note] InnoDB: Completed initialization of buffer pool
2013-04-18 12:18:33 7817 [Note] InnoDB: Highest supported file format is Barracuda.
2013-04-18 12:18:33 7817 [Note] InnoDB: The log sequence numbers 1626007 and 1626007 in ibdata files do not match the log sequence number 1626027 in the ib_logfiles!
2013-04-18 12:18:33 7817 [Note] InnoDB: Database was not shutdown normally!
2013-04-18 12:18:33 7817 [Note] InnoDB: Starting crash recovery.
2013-04-18 12:18:33 7817 [Note] InnoDB: Reading tablespace information from the .ibd files...
2013-04-18 12:18:33 7817 [Note] InnoDB: Restoring possible half-written data pages
2013-04-18 12:18:33 7817 [Note] InnoDB: from the doublewrite buffer...
2013-04-18 12:18:33 7817 [ERROR] InnoDB: Unable to lock ./mysql/innodb_table_stats.ibd, error: 11
2013-04-18 12:18:33 7817 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2013-04-18 12:18:33 b7fb16d0 InnoDB: Assertion failure in thread 3086685904 in file fil0fil.cc line 870
InnoDB: Failing assertion: ret
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
19:18:33 UTC - 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=8388608
read_buffer_size=131072
max_used_connections=0
max_threads=151
thread_count=0
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 67580 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
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 = 0 thread_stack 0x30000
mysqld(my_print_stacktrace+0x33)[0x855a2a3]
mysqld(handle_fatal_signal+0x43e)[0x828498e]
[0x1ab420]
[0x1ab402]
/lib/libc.so.6(gsignal+0x50)[0xa15d10]
/lib/libc.so.6(abort+0x101)[0xa17621]
mysqld[0x86fc429]
mysqld[0x86fc6f3]
mysqld[0x87073ad]
mysqld[0x86b8dcf]
mysqld[0x85c3474]
mysqld[0x85c9e0c]
mysqld[0x8654574]
mysqld[0x8578f48]
mysqld(_Z24ha_initialize_handlertonP13st_plugin_int+0x4e)[0x819901e]
mysqld[0x831ac2d]
mysqld(_Z11plugin_initPiPPci+0x9dc)[0x831edcc]
mysqld[0x818fa1b]
mysqld(_Z11mysqld_mainiPPc+0x3d9)[0x8190aa9]
mysqld(main+0x32)[0x8184622]
/lib/libc.so.6(__libc_start_main+0xdc)[0xa02dec]
mysqld(__gxx_personality_v0+0x3c5)[0x8184531]
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.

please help

A very slow query on InnoDb 5.1.67 (15 replies)

Willem van Schevikhoven — Sun, 28 Apr 2013 03:47:07 +0000

Hello everyone,
I have an InnoDB database which we use on our webshop written in PHP. MySql version 5.1.67.

The problem is a very slow query, and overall database performance. The query in question retrieves sales orders from the db based on properties like percentage invoiced, shipped etc. , this said, the query is quite long consisting of several joins and subqueries. I will include the query at the end of the message.

The reason I am writing this message is, that when we switched to a more powerful server which now has apache and mysql on the same machine, the execution time of this query has slowly crept from 20 seconds to 49 seconds, while overall performance of the site is a bit better or the same.

This query only takes a long time after the database has been updated. A second query uses the caches and the data is retrieved in a blink of an eye. The problem is that the orders table is the most frequently updated table and every other query takes 49 seconds. This is starting to be very hard to tolerate.

The data is also quite long, so providing test data without actually dumping the database is next to impossible. Would uploading a dump (with important information replaced) be a good idea?

Table sizes (rows):
rmm_sales_order: 5357
rmm_sales_order_row: 18885
rmm_sales_invoice:3016
rmm_sales_invoice_row: 7266
rmm_sales_shipment: 2846
rmm_sales_shipment_row:9870
rmm_customers:3578

Other tables involved in the query have < 100 rows. So in other words, the amount of data is very small and shouldn’t be the direct cause of this issues if I am correct.
I have looked at an explain for the query, but to be honest I am not experienced enough to determine if things are going right or wrong.

***So, what additional information should I provide in order to start getting to the bottom of this? I am not sure what the problem is: mysql settings or query design (quite heavy), or perhaps both. I am quite unexperienced, i have only built this webshop as a side project at work***

***Query:***

SELECT SQL_CALC_FOUND_ROWS
FINALRESULT.sales_order_id,
FINALRESULT.sales_order_status,
DATE(FINALRESULT.sales_order_date) as sales_order_date,
FINALRESULT.method_shipment_id,
FINALRESULT.method_payment_id,

FINALRESULT.customer_firstname,
FINALRESULT.customer_lastname,
FINALRESULT.customer_company,

FINALRESULT.payment_method_name,
FINALRESULT.shipment_method_name,

#
# Sum part for result where actual amounts are compiled
ROUND(COALESCE(SUM(IF(FINALRESULT.is_cancelled=false, FINALRESULT.sales_order_row_total, 0)), 0), 2) as o_sum_total,
ROUND(COALESCE(SUM(IF(FINALRESULT.is_cancelled=false, FINALRESULT.sales_order_row_quantity, 0)), 0), 2) as o_sum_qty,

ROUND(COALESCE(SUM(FINALRESULT.sales_invoice_row_paid), 0), 2) as o_sum_paid,
ROUND(COALESCE(SUM(FINALRESULT.sales_invoice_row_total), 0), 2) as o_sum_invoiced,
ROUND(COALESCE(SUM(FINALRESULT.sales_shipment_row_qty), 0), 2) as o_sum_shipped,

#
# Percentage part for result where percentages are made.
# A Zero row and Zero shipped order will result in a 100% shipped order because there is nothing to ship.
# The same goes for invoices: if the row totals are zero and the invoiced amount is zero too, the invoiced amount is 100% because there is nothing to invoice.

# Invoiced percentage. Divide invoiced with total, and coalesce zero values to 1. Then multiply by 100.
ROUND(COALESCE(
COALESCE(SUM(FINALRESULT.sales_invoice_row_total), 0) /
COALESCE(SUM(IF(FINALRESULT.is_cancelled=false, FINALRESULT.sales_order_row_total, 0)), 0)
,1) * 100, 0) AS o_percentage_invoiced,

# Paid percentage. Divide paid with total, and coalesce zero values to 1. Then multiply by 100.
ROUND(COALESCE(
COALESCE(SUM(FINALRESULT.sales_invoice_row_paid), 0) /
COALESCE(SUM(IF(FINALRESULT.is_cancelled=false, FINALRESULT.sales_order_row_total, 0)), 0)
, 1) * 100, 0) AS o_percentage_paid,

# Shipped percentage. Divide sent rows by total rows, coalesce zero to 1, then multiply by 100.
ROUND(COALESCE(
COALESCE(SUM(FINALRESULT.sales_shipment_row_qty), 0) /
COALESCE(SUM(IF(FINALRESULT.is_cancelled=false, FINALRESULT.sales_order_row_quantity, 0)), 0)
, 1) * 100, 0) AS o_percentage_shipped

FROM
#
# The finalresult combines invoice row and order row subqueries into a result where the shipped, paid and invoiced amounts are
# returned per order row. No duplicate order rows are returned, so individual invoice and shipment rows are grouped by sales order rows
# in their respective subqueres.
# Customer, modules etc. 1on1 joins are also included, but they dont affect aggregate functions like sum like many to many joins.
#
(
# This query results in the FINALRESULT, which combines the queries into one result for clarity in final aggreagates grouped by order_id.
SELECT
INVOICES.sales_invoice_row_total,
INVOICES.sales_invoice_row_paid,
ORDERS_SHIPPED.sales_order_id,
ORDERS_SHIPPED.sales_order_status,
ORDERS_SHIPPED.sales_order_date,
ORDERS_SHIPPED.method_shipment_id,
ORDERS_SHIPPED.method_payment_id,

ORDERS_SHIPPED.sales_order_row_id,
ORDERS_SHIPPED.sales_order_row_quantity,
ORDERS_SHIPPED.sales_order_row_total,
ORDERS_SHIPPED.is_cancelled,

ORDERS_SHIPPED.customer_firstname,
ORDERS_SHIPPED.customer_lastname,
ORDERS_SHIPPED.customer_company,

ORDERS_SHIPPED.sales_shipment_row_qty,
ORDERS_SHIPPED.payment_method_name,
ORDERS_SHIPPED.shipment_method_name
FROM
#
# Invoices & their paid amounts query start
# This subquery returns unique sales order rows which have an invoice and a possible payment attached.
# This is joined to all sales order rows, giving a result where sales order rows seem to have an extra column with the paid and invoiced amount.
(
# Group by sales order row id
SELECT
INVOICES_PRE.sales_order_row_id,
SUM(INVOICES_PRE.sales_invoice_row_total) as sales_invoice_row_total,
SUM(INVOICES_PRE.sales_invoice_row_paid) as sales_invoice_row_paid
FROM
(
# Group by sales invoice row. This might still result in duplicate sales order rows if the order row
# has been invoiced on multiple invoices.
SELECT
rmm_sales_invoice_row.sales_order_row_id,
rmm_sales_invoice_row.sales_invoice_row_id,
rmm_sales_invoice_row.sales_invoice_row_total as sales_invoice_row_total,
COALESCE((rmm_sales_invoice_row.sales_invoice_row_total * INVOICES_PAID.paid / INVOICES_PAID.total), 0) AS sales_invoice_row_paid
FROM
(
# This query combines the remittance query union for credit and sales remittances.
SELECT IQ1.sales_invoice_id, SUM(paid1) as paid, total FROM
(
SELECT
rmm_sales_invoice.sales_invoice_id,
SUM(rmm_sales_invoice_row.sales_invoice_row_total) / COUNT(*) * COUNT(DISTINCT rmm_sales_invoice_row.sales_invoice_row_id) as total,
COALESCE(ROUND(SUM(rmm_sales_remittance.amount) / COUNT(*) * COUNT(DISTINCT rmm_sales_remittance.remittance_id), 2),0) AS paid1
FROM
rmm_sales_invoice LEFT JOIN rmm_sales_remittance ON
rmm_sales_invoice.sales_invoice_id = rmm_sales_remittance.sales_invoice_id
LEFT JOIN rmm_sales_invoice_row ON rmm_sales_invoice_row.sales_invoice_id = rmm_sales_invoice.sales_invoice_id
GROUP BY rmm_sales_invoice.sales_invoice_id

UNION

SELECT
rmm_sales_invoice.sales_invoice_id,
SUM(rmm_sales_invoice_row.sales_invoice_row_total) / COUNT(*) * COUNT(DISTINCT rmm_sales_invoice_row.sales_invoice_row_id) as total,
COALESCE(ROUND(SUM(-rmm_sales_remittance.amount) / COUNT(*) * COUNT(DISTINCT rmm_sales_remittance.remittance_id), 2),0) AS paid1
FROM
rmm_sales_invoice LEFT JOIN rmm_sales_remittance ON
rmm_sales_invoice.sales_invoice_id = rmm_sales_remittance.credit_invoice_id
LEFT JOIN rmm_sales_invoice_row ON rmm_sales_invoice_row.sales_invoice_id = rmm_sales_invoice.sales_invoice_id
GROUP BY rmm_sales_invoice.sales_invoice_id
) AS IQ1
GROUP BY IQ1.sales_invoice_id) AS INVOICES_PAID
JOIN
rmm_sales_invoice_row ON rmm_sales_invoice_row.sales_invoice_id = INVOICES_PAID.sales_invoice_id
) AS INVOICES_PRE
GROUP BY INVOICES_PRE.sales_order_row_id) AS INVOICES

RIGHT JOIN
# Sales order rows with shipped quantity
#
# Returns sales order rows where shipment rows are grouped by so the
# shipped quantity per row is already taken care of
(
SELECT
rmm_sales_order.sales_order_id,
rmm_sales_order.sales_order_status,
rmm_sales_order.sales_order_date,
rmm_sales_order.method_shipment_id,
rmm_sales_order.method_payment_id,

rmm_sales_order_row.sales_order_row_id,
rmm_sales_order_row.sales_order_row_quantity,
rmm_sales_order_row.sales_order_row_total,
rmm_sales_order_row.is_cancelled,

rmm_customers.customer_firstname,
rmm_customers.customer_lastname,
rmm_customers.customer_company,

SHIPMENTS.sales_shipment_row_qty,
rmm_modules_payment.method_name as payment_method_name,
rmm_modules_shipment.method_name as shipment_method_name

FROM
rmm_sales_order JOIN rmm_sales_order_row ON
rmm_sales_order.sales_order_id = rmm_sales_order_row.sales_order_id

LEFT JOIN rmm_customers ON rmm_customers.customer_id = rmm_sales_order.billing_customer_id

LEFT JOIN rmm_modules_payment
ON rmm_sales_order.method_payment_id = rmm_modules_payment.method_id

LEFT JOIN rmm_modules_shipment
ON rmm_sales_order.method_shipment_id = rmm_modules_shipment.method_id

LEFT JOIN

# Sales shipments and their rows query which returns sales order rows with their aggregated (SUM) shipped quantity.
# Sales shipment rows are grouped by the sales order row id, so no duplicate sales order rows are returned.
(
SELECT
rmm_sales_shipment_row.sales_order_row_id,
SUM(IF(rmm_sales_shipment.sales_shipment_type='return', -rmm_sales_shipment_row.sales_shipment_row_qty, rmm_sales_shipment_row.sales_shipment_row_qty)) AS sales_shipment_row_qty
FROM
rmm_sales_shipment JOIN rmm_sales_shipment_row ON rmm_sales_shipment.sales_shipment_id = rmm_sales_shipment_row.sales_shipment_id
GROUP BY rmm_sales_shipment_row.sales_order_row_id
) AS SHIPMENTS
ON SHIPMENTS.sales_order_row_id = rmm_sales_order_row.sales_order_row_id
) AS ORDERS_SHIPPED
ON ORDERS_SHIPPED.sales_order_row_id = INVOICES.sales_order_row_id
) AS FINALRESULT

GROUP BY FINALRESULT.sales_order_id
ORDER BY FINALRESULT.sales_order_id DESC LIMIT 0, 30

Can't start the MySQL service - Error with InnoDB (1 reply)

L Lopes — Wed, 17 Apr 2013 18:53:35 +0000

Hello,

I have an installation of MySQL 5.6 which has been working fine, until today.
When I started windows the MySQL service did not start, so I tried to start it from the console with mysqld. From there I got these errors and warnings:

-------------------------------------

C:\Users\NE-MK>mysqld
2013-04-16 09:50:51 0 [Warning] TIMESTAMP with implicit DEFAULT value is depreca
ted. Please use --explicit_defaults_for_timestamp server option (see documentati
on for more details).
2013-04-16 09:50:51 1800 [Warning] Can't create test file C:\Program Files\MySQL
\MySQL Server 5.6\data\NE-MK-PC.lower-test
2013-04-16 09:50:51 1800 [Warning] Can't create test file C:\Program Files\MySQL
\MySQL Server 5.6\data\NE-MK-PC.lower-test
2013-04-16 09:50:51 1800 [Note] Plugin 'FEDERATED' is disabled.
2013-04-16 09:50:51 1800 [Note] InnoDB: The InnoDB memory heap is disabled
2013-04-16 09:50:51 1800 [Note] InnoDB: Mutexes and rw_locks use Windows interlo
cked functions
2013-04-16 09:50:51 1800 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-04-16 09:50:51 1800 [Note] InnoDB: CPU does not support crc32 instructions
2013-04-16 09:50:51 1800 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2013-04-16 09:50:51 1800 [Note] InnoDB: Completed initialization of buffer pool
2013-04-16 09:50:51 1800 [ERROR] InnoDB: read can't be opened in .\ibdata1 mode
2013-04-16 09:50:51 1800 [ERROR] InnoDB: The system tablespace must be writable!

2013-04-16 09:50:51 1800 [ERROR] Plugin 'InnoDB' init function returned error.
2013-04-16 09:50:51 1800 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGIN
E failed.
2013-04-16 09:50:51 1800 [ERROR] Unknown/unsupported storage engine: InnoDB
2013-04-16 09:50:51 1800 [ERROR] Aborting

2013-04-16 09:50:51 1800 [Note] Binlog end
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'partition'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'INNODB_FT_INSERTED'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD
'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'INNODB_METRICS'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET
'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'INNODB_CMPMEM'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'INNODB_CMP'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'INNODB_LOCKS'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'INNODB_TRX'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'BLACKHOLE'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'ARCHIVE'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'MRG_MYISAM'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'MyISAM'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'MEMORY'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'CSV'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'sha256_password'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'mysql_old_password'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'mysql_native_password'
2013-04-16 09:50:51 1800 [Note] Shutting down plugin 'binlog'
2013-04-16 09:50:51 1800 [Note] mysqld: Shutdown complete

------------------------------

I have googled for similar errors. I tried for example deleting iblogfile0 and iblogfile1, but it didn't change anything.

Anyway, until yesterday everything was working fine. Yesterday all I did was to create some new databases, provide some extra privileges to a user, insert some data to one of the databases and change the charset of annother one from UTF8 to latin1 (with collation latin1_swedish_ci).

I would appreciate any help with this issue.

Thanks.

Filipe

Unable to lock ./ibdata1, error: 11 (1 reply)

Prashant Tekriwal — Fri, 19 Apr 2013 02:33:51 +0000

Hi,

I am a newbie as far as mysql administration is concerned.

Let me first explain the background of how we are using mysqld and then I will explain the issue.

Background:

We have a tool using which QA engineers run their tests by specifying their test scripts. This each run is called a 'job'.

Now we start a new mysql server for each job and when the job finishes we shut it down, which means we don't have a persistent server.

Issue:

Recently one of our users faced an issue where he has hit an error just twice so far, on two different machines i.e. the issue is intermittent but not reproducible at will.

The error we get is 'Unable to lock ./ibdata1, error: 11'. I have pasted the whole mysqld log at end of the post.

The solution that I have found till now around this error is moving the ibdata file, but in our case first of all the ibdata file is newly created and since we don't use persistent server moving ibdata files is of no use because the error is coming when starting a new server on totally new set of mysql files.

I am not able to find any help around this and it will be great if somebody can give some insight to help me fix this.

Here are some of the details you might be interested in:

OS : CentOS release 5.5
Arch : 64 bit
Mysql Version : Mariadb Distribution Version 5.3.5
Engine used : xtradb (Mariadb's varient for innodb)

Let me know if more information is needed.

Here is the output of mysqld.log
===============================================================================

InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
130319 16:28:02 InnoDB: Initializing buffer pool, size = 128.0M
130319 16:28:02 InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
130319 16:28:02 InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
130319 16:28:03 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
130319 16:28:03 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
130319 16:28:04 InnoDB: Assertion failure in thread 47644993965344 in file fil/fil0fil.c line 796
InnoDB: Failing assertion: ret
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
2d2d2d d:2d:2d [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.

To report this bug, see http://kb.askmonty.org/en/reporting-bugs

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.

Server version: 5.3.5-MariaDB-ga
key_buffer_size=134213632
read_buffer_size=131072
max_used_connections=0
max_threads=10001
thread_count=0
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 22022775 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
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 = 0 thread_stack 0x48000
.../mariadb/5.3.5a/libexec/mysqld(my_print_stacktrace+0x24) [0x868d44]
/usr/software/test/share/mariadb/5.3.5a/libexec/mysqld(handle_fatal_signal+0x426) [0x710f26]
/lib64/libpthread.so.0 [0x341fc0eb10]
/lib64/libc.so.6(gsignal+0x35) [0x341f030265]
/lib64/libc.so.6(abort+0x110) [0x341f031d10]
.../mariadb/5.3.5a/lib/mysql/plugin/ha_xtradb.so [0x2aaabbd0792e]
.../mariadb/5.3.5a/lib/mysql/plugin/ha_xtradb.so [0x2aaabbd0a902]
.../mariadb/5.3.5a/lib/mysql/plugin/ha_xtradb.so [0x2aaabbda4d1c]
.../mariadb/5.3.5a/lib/mysql/plugin/ha_xtradb.so [0x2aaabbd27305]
.../mariadb/5.3.5a/libexec/mysqld(ha_initialize_handlerton(st_plugin_int*)+0x31) [0x70a691]
.../mariadb/5.3.5a/libexec/mysqld [0x791d4d]
.../mariadb/5.3.5a/libexec/mysqld(plugin_init(int*, char**, int)+0x6a2) [0x795562]
.../mariadb/5.3.5a/libexec/mysqld [0x5da787]
.../mariadb/5.3.5a/libexec/mysqld(main+0x3a5) [0x5dcc35]
/lib64/libc.so.6(__libc_start_main+0xf4) [0x341f01d994]
.../mariadb/5.3.5a/libexec/mysqld [0x4f2639]
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.
=================================================================================

[Mysql 5.5.30] Slow simple UPDATE query - "query end" and context switching (no replies)

Lech Groblewicz — Mon, 15 Apr 2013 14:38:23 +0000

Hello,

I have problem with slow UPDATE queries. Every couple of minutes some similar update queries take more than 2 seconds. In where clause there is only PK check, there shouldn't be any queries locking the whole table.

Sample query looks as follows:

UPDATE `users` set last_portal_login = NOW(), ip = 1464635301, browser = "chrome" where uid = 2021

Table structure:

CREATE TABLE users (
  uid int(10) unsigned NOT NULL,
  login char(50) COLLATE utf8_polish_ci NOT NULL,
  hide_me tinyint(1) unsigned NOT NULL DEFAULT '0',
  addon int(10) unsigned NOT NULL DEFAULT '0',
  protocol float unsigned NOT NULL DEFAULT '0',
  mode enum('firefox','lite','chrome','opera') COLLATE utf8_general_ci DEFAULT NULL,
  browser enum('firefox','chrome','opera','ie','safari') COLLATE utf8_general_ci DEFAULT NULL,
  last_login datetime DEFAULT NULL,
  last_portal_login datetime DEFAULT NULL,
  ip int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (uid),
  UNIQUE KEY login (login)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci$$

Table contains ~2000 rows.

It gets stuck on "query end" phase with high Context_voluntary value (200+) according to SHOW PROFILE ALL command.

Mysql version: 5.5.30 dotdeb.org

What can I do to fix this?

PS:
DBMS works on VPS with 768M RAM burstable to 1.5G
InnoDB configuration:

'innodb_adaptive_flushing', 'ON'
'innodb_adaptive_hash_index', 'ON'
'innodb_additional_mem_pool_size', '8388608'
'innodb_autoextend_increment', '8'
'innodb_autoinc_lock_mode', '1'
'innodb_buffer_pool_instances', '1'
'innodb_buffer_pool_size', '268435456'
'innodb_change_buffering', 'all'
'innodb_checksums', 'ON'
'innodb_commit_concurrency', '0'
'innodb_concurrency_tickets', '500'
'innodb_data_file_path', 'ibdata1:10M:autoextend'
'innodb_data_home_dir', ''
'innodb_doublewrite', 'ON'
'innodb_fast_shutdown', '1'
'innodb_file_format', 'Antelope'
'innodb_file_format_check', 'ON'
'innodb_file_format_max', 'Antelope'
'innodb_file_per_table', 'OFF'
'innodb_flush_log_at_trx_commit', '2'
'innodb_flush_method', 'O_DIRECT'
'innodb_force_load_corrupted', 'OFF'
'innodb_force_recovery', '0'
'innodb_io_capacity', '200'
'innodb_large_prefix', 'OFF'
'innodb_lock_wait_timeout', '50'
'innodb_locks_unsafe_for_binlog', 'OFF'
'innodb_log_buffer_size', '8388608'
'innodb_log_file_size', '67108864'
'innodb_log_files_in_group', '2'
'innodb_log_group_home_dir', './'
'innodb_max_dirty_pages_pct', '75'
'innodb_max_purge_lag', '0'
'innodb_mirrored_log_groups', '1'
'innodb_old_blocks_pct', '37'
'innodb_old_blocks_time', '0'
'innodb_open_files', '300'
'innodb_print_all_deadlocks', 'OFF'
'innodb_purge_batch_size', '20'
'innodb_purge_threads', '0'
'innodb_random_read_ahead', 'OFF'
'innodb_read_ahead_threshold', '56'
'innodb_read_io_threads', '4'
'innodb_replication_delay', '0'
'innodb_rollback_on_timeout', 'OFF'
'innodb_rollback_segments', '128'
'innodb_spin_wait_delay', '6'
'innodb_stats_method', 'nulls_equal'
'innodb_stats_on_metadata', 'ON'
'innodb_stats_sample_pages', '8'
'innodb_strict_mode', 'OFF'
'innodb_support_xa', 'ON'
'innodb_sync_spin_loops', '30'
'innodb_table_locks', 'ON'
'innodb_thread_concurrency', '0'
'innodb_thread_sleep_delay', '10000'
'innodb_use_native_aio', 'ON'
'innodb_use_sys_malloc', 'ON'
'innodb_version', '5.5.30'
'innodb_write_io_threads', '4'

SHOW TABLE STATUS:

+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+----------------+----------+----------------+---------+
| Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation      | Checksum | Create_options | Comment |
+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+----------------+----------+----------------+---------+
| InnoDB |      10 | Compact    | 2241 |            124 |      278528 |               0 |       245760 |  88080384 |           NULL | 2013-04-14 23:32:21 | NULL        | NULL       | utf8_polish_ci |     NULL |           |         |
+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+----------------+----------+----------------+---------+
1 row in set (0.06 sec)

InnoDB Bug (1 reply)

Mar Antonio Bacalla — Fri, 19 Apr 2013 02:46:35 +0000

I am currently experiencing a problem in MySQL Database in which I cannot start the MySQL Service. When I look at the .err file on the data dictionary, the following errors were displayed.

130415 9:29:53 [Note] Plugin 'FEDERATED' is disabled.
130415 9:29:53 InnoDB: Initializing buffer pool, size = 25.0M
130415 9:29:53 InnoDB: Completed initialization of buffer pool
InnoDB: Error: auto-extending data file .\ibdata1 is of a different size
InnoDB: 0 pages (rounded down to MB) than specified in the .cnf file:
InnoDB: initial 640 pages, max 0 (relevant if non-zero) pages!
InnoDB: Could not open or create data files.
InnoDB: If you tried to add new data files, and it failed here,
InnoDB: you should now edit innodb_data_file_path in my.cnf back
InnoDB: to what it was, and remove the new ibdata files InnoDB created
InnoDB: in this failed attempt. InnoDB only wrote those files full of
InnoDB: zeros, but did not yet use them in any way. But be careful: do not
InnoDB: remove old data files which contain your precious data!
130415 9:29:53 [ERROR] Plugin 'InnoDB' init function returned error.
130415 9:29:53 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
130415 9:29:53 [ERROR] Unknown/unsupported table type: INNODB
130415 9:29:53 [ERROR] Aborting

130415 9:29:53 [Note] C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: Shutdown complete

Upon checking the ibdata1 file, it has 0 kilobytes meaning it is empty.

I tried to move the ib* files to other folder as a back-up and start the service again to create new ib* files and it went well, however, I cannot access any of my tables in my database. It is there but when I run a query on a specific table, it says table does not exists.

Is this a MySQL Server Bug or am I just missing something here?

Please help me resolve this issue ASAP since this is very critical on our company. Any suggestions would be greatly appreciated.

Also, please take note that I am using Windows XP so my configuration file is my.ini, not my.cnf.

Thanks and Best Regards,

Mar Antonio Bacalla
Software Solutions Specialists

Strange SELECT lock / timeout (3 replies)

Zen Itt — Thu, 18 Apr 2013 05:24:05 +0000

I'm having a strange problem with a SELECT in an Innodb table, it never returns.

CREATE TABLE `example` (
`id` int(11) NOT NULL,
`done` tinyint(2) NOT NULL DEFAULT '0',
`agent` tinyint(4) NOT NULL DEFAULT '0',
`text` varchar(256) NOT NULL,
PRIMARY KEY (`id`),
KEY `da_idx` (`done`,`agent`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

The query that I can't obtain the results is:
SELECT id, text FROM example WHERE done = 0 AND agent = 0 LIMIT 120;

First I thought in some index optimization or lock problem, I was some time researching that but then I found this:

SELECT id FROM example WHERE done = 0 AND agent = 0 LIMIT 120;
...
...
...
120 rows in set (0.27 sec)

SELECT text FROM example WHERE done = 0 AND agent = 0 LIMIT 120;
...
...
...
120 rows in set (0.83 sec)

Now I'm lost, how obtaining id or text column separately with exactly the same query (same WHERE and LIMIT) works perfect and then obtaining both of them not??

Executing the "SELECT id, text..." again after that two queries have the same effect, never returns.

Update query Crashes the server (1 reply)

Support Test — Thu, 11 Apr 2013 04:01:43 +0000

Hello,

We have three node XtraDB cluster, the cluster was working fine for past few weeks. Today our cluster completely crashed, while checking the logs I found this issue created due to the update. Please find the error below. Any idea why the insertion failure leads to complete server crash.

Thanks in advance for your help.

130409 8:07:21 [Note] WSREP: cleaning up ec4f0d2c-a0f1-11e2-0800-b39cacad59c1 (tcp://10.34.38.140:4567)
len 224; hex a85432b5662b0000792f2fea652b000000fc0d8c622b000000000000000000000000000000000000000000000000000003000000000000000200000000000000010000000000000000000000000000000000000000000000000000000000000001000000000000000000000000000000b1dbfe8c8d48a225000000000000000001000000000000008344510700000000deaf2f2c000000000200000000000000010000000000000000fc0d8c622b0000e2cd00000000000060e1117700000000020000000000000058471c60622b0000d8af2f2c000000001800000000000000; asc T2 f+ y// e+ b+ H % DQ /, b+ ` w XG `b+ /, ;
TRANSACTION 3844623, ACTIVE 0 sec fetching rows
mysql tables in use 2, locked 1
69 lock struct(s), heap size 14776, 181 row lock(s), undo log entries 6
MySQL thread id 2214011, OS thread handle 0x2b66b20b1940, query id 84509749 10.34.38.153 dbadmin preparing
UPDATE `news` SET `app_status` = 'D', `load_status` = 'D' WHERE ( guid IN (SELECT DISTINCT gallery_id FROM gallery WHERE app_status='A' OR app_status='N') AND feed_id !=21)
130409 8:07:35 InnoDB: Assertion failure in thread 47720073730368 in file btr0pcur.c line 257
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
12:07:35 UTC - 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.
Please help us make Percona Server better by reporting any
bugs at http://bugs.percona.com/

key_buffer_size=268435456
read_buffer_size=1048576
max_used_connections=193
max_threads=1250
thread_count=146
connection_count=146
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 2837954 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x2b62604aa460
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 = 2b66b20b10c8 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x35)[0x7c51e5]
/usr/sbin/mysqld(handle_fatal_signal+0x4a4)[0x69e894]
/lib64/libpthread.so.0[0x3ecb40eca0]
/lib64/libc.so.6(gsignal+0x35)[0x3eca4302c5]
/lib64/libc.so.6(abort+0x110)[0x3eca431d70]
/usr/sbin/mysqld[0x858dcc]
/usr/sbin/mysqld[0x8062a8]
/usr/sbin/mysqld[0x806bea]
/usr/sbin/mysqld[0x7d7b3d]
/usr/sbin/mysqld(_ZN30subselect_indexsubquery_engine4execEv+0x13d)[0x70bbed]
/usr/sbin/mysqld(_ZN14Item_subselect4execEv+0x4d)[0x70acbd]
/usr/sbin/mysqld(_ZN17Item_in_subselect8val_boolEv+0x20)[0x70b370]
/usr/sbin/mysqld(_ZN17Item_in_optimizer7val_intEv+0x152)[0x6c0552]
/usr/sbin/mysqld(_ZN4Item8val_boolEv+0xac)[0x6ab4bc]
/usr/sbin/mysqld(_ZN13Item_cond_and7val_intEv+0x3a)[0x6c17ca]
/usr/sbin/mysqld(_Z12mysql_updateP3THDP10TABLE_LISTR4ListI4ItemES6_PS4_jP8st_ordery15enum_duplicatesbPySB_+0xa80)[0x607bc0]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x25c7)[0x5941a7]
/usr/sbin/mysqld(_ZN18Prepared_statement7executeEP6Stringb+0x3b9)[0x5a6599]
/usr/sbin/mysqld(_ZN18Prepared_statement12execute_loopEP6StringbPhS2_+0x7b)[0x5a9e7b]
/usr/sbin/mysqld(_Z19mysqld_stmt_executeP3THDPcj+0x21e)[0x5aa4ae]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x174d)[0x59a02d]
/usr/sbin/mysqld(_Z10do_commandP3THD+0x169)[0x59a799]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x15f)[0x6356df]
/usr/sbin/mysqld(handle_one_connection+0x51)[0x6358a1]
/lib64/libpthread.so.0[0x3ecb40683d]
/lib64/libc.so.6(clone+0x6d)[0x3eca4d503d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (2b62601341e0): is an invalid pointer
Connection ID (thread ID): 2214011
Status: KILL_QUERY

linking filesystem data with database in a specific colunm (1 reply)

Tunde kassim — Fri, 05 Apr 2013 15:45:31 +0000

Hello

I have table which contain different columns, two of this column store binary file information but i want to this binary file on separate disk and create a link to it from my database. How do I go about it? thanks

Please help me diagnose a possible mem leak (no replies)

Michael Rubin — Thu, 04 Apr 2013 02:33:40 +0000

I'm seeing what looks like a memleak once I increased my buffer-pool size beyond 2-4G (before I did this, the server could run fine for months). The symptoms are: mysqld starts filling up gigs of swap space; free memory on the machine goes towards 0 (including file i/o buffering); yet looking at `top`, mysqld usage (both rss and virt) are nowhere near what `free` is showing. If I shut down mysqld, the swap space clears out to ~0, but there are gigs of unaccounted-for usage that don't free up. If I reboot the machine, that usage clears up (but if I put mysqld under some load, it starts filling up RAM again).

Please let me know if there are any tricks to diagnosing this further. Thank you very much for any assistance!

Version: 5.5.30-log - MySQL Community Server (GPL) by Remi
Machine: CentOS 6.3 x86_64, 16G of RAM, 4cpu

My config is as follows:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
# Will log MySQL queries, This log will grow quickly
#log=/var/log/mysql-query.log
slow-query-log = 1
slow_query_log_file = /var/log/mysql-slow.log
long_query_time = 2

innodb_file_per_table

# Set buffer pool size to 50-80% of your computer's memory
innodb_buffer_pool_size=2144M # setting this to 8G or even 6G seems to leak
innodb_additional_mem_pool_size=20M
innodb_buffer_pool_instances=4

# Set the log file size to about 25% of the buffer pool size
innodb_log_file_size=192M
innodb_log_buffer_size=8M

#innodb_flush_log_at_trx_commit=0
innodb_rollback_on_timeout=1

#innodb_file_io_threads=16
#innodb_thread_concurrency=8

log-bin=1
binlog-format=MIXED
expire_logs_days=14
server-id=1
relay-log=mysqld-relay-bin

innodb_purge_threads=1

sort_buffer_size=128M
join_buffer_size=128M
read_rnd_buffer_size=32M
bulk_insert_buffer_size=32M
tmp_table_size=256M
max_heap_table_size=256M
table_cache=1024
max_connections=512
max_allowed_packet=16M

key_buffer_size=16M # myisam only
read_buffer_size=16M # myisam only
myisam_sort_buffer_size=16M
myisam_max_sort_file_size=1G

#thread_cache_size = 16
#thread_cache = 8
#thread_concurrency = 8

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# Will log MySQL queries, This log will grow quickly
#log=/var/log/mysql-query.log

log-slow-queries = /var/log/mysql-slow.log
long_query_time = 2