INNODB

Discussions

SQL errors appearing after changing from MyISAM to INNODB

It was fixed with the cache clear multiple times and a MySQL Reboot.Since doing a database change I keep getting the following database error: Query:REPLACE INTO `bx_timeline_cache` SET `type` = 'owner',`context_id` = ,`profile_id` =...

Forum's Comments

It's great that you made it work with Aria DB engine, I think that UNA wasn't working with InnoDB in your case, because you had incorrect setting for transaction_isolation option, which you said you wasn't able to specify. I think for MariaDB this setting should be named tx_isolation instead of transaction_isolation

#innodb #mariadb

Finally!!!!! Somehow I always end up fixing it my own way. So changing to Aria has resolved the issue. There has to be an INNODB option that cause an issue but not sure at this time. Everything was the same though. At the time I will stick with Aria. Keep in mind trying Alex's fix for #INNODB for #MySQL did not work on #MariaDB for me. I would advise at this point unless tested and a resolution, do not use INNODB, but use #MyISAM or #Aria engines instead if you use MariaDB. If you have a fix please share.

Using #INNODB with MySQL So what I did so if anyone reads this in the future. I exported my SQL (Export Tab) from my database in PHPMyADMIN and then open it with my Notepad++ or just regular notepad works, but takes longer to load. I did a search for MyISAM and replaced all with INNODB. I then imported (Import Tab) and overwrote my tables in PHPMYADMIN. Then I did as Alex mentioned above adding the last section to my (my.cnf in my case) Mysql file and this is my end result. Alot of my changes I borrowed from others Configs here that worked great for them:

performance-schema=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
innodb_stats_on_metadata = off
innodb_buffer_pool_instances = 4
innodb_buffer_pool_size=134217728
transaction_isolation = READ-COMMITTED
lock_wait_timeout = 4
innodb_lock_wait_timeout = 4
innodb_rollback_on_timeout = 1
binlog_format = MIXED
innodb_log_file_size = 400M
max_allowed_packet=268435456
open_files_limit=10000
default-storage-engine=INNODB
innodb_file_per_table=1
key_buffer_size = 64M
#max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 192
max_connections = 250
table_open_cache = 100000
max_heap_table_size = 128M
tmp_table_size = 1024M
query_cache_limit = 2M
query_cache_size = 32M
query_cache_type = on

(This section I added especially for Froala in the past. So before you install Froala I would recommend this. Mine was already installed so I had to run a script to convert it. Took me a lot of Google searching)
collation-server = utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'
character-set-server=utf8mb4

If you have any other things I can trey with this MySQL CNF for better performance please share :)

It's possible to use InnoDB with UNA, it's functioning better in hight concurrency, but some server side tuning is needed to avoid deadlocks.

First set (or add) the following line into inc/header.inc.php

define('BX_DATABASE_ENGINE', 'INNODB'); ///< db engine

after

define('BX_DATABASE_NAME', 'xxxxxx'); ///< db name

Then the following server side settings for MySQL is needed in [mysqld] section:

innodb_stats_on_metadata = off
innodb_buffer_pool_instances = 4 # number of CPUs
innodb_buffer_pool_size = 4096M # ~80% of available RAM

transaction_isolation = READ-COMMITTED # or tx_isolation = READ-COMMITTED
lock_wait_timeout = 4
innodb_lock_wait_timeout = 4
innodb_rollback_on_timeout = 1
binlog_format = MIXED
innodb_log_file_size = 400M

#innodb

Empty

Followers

Empty

Post to INNODB