user tables converted to innodb changes not committed

Discussion regarding Joomla! 1.5 Performance issues.

Moderator: General Support Moderators

Forum rules
Forum Rules
Absolute Beginner's Guide to Joomla! <-- please read before posting, this means YOU.
Security and Performance FAQs
Forum Post Assistant - If you are serious about wanting help, you will use this tool to help you post.
Locked
screel
Joomla! Fledgling
Joomla! Fledgling
Posts: 2
Joined: Tue Jan 10, 2017 10:44 am

user tables converted to innodb changes not committed

Post by screel » Tue Jan 10, 2017 12:30 pm

I have a java based integration package which is used to pull data from external sources and update various tables in our joomla database. One of the projects updates the jos_users table and the associated jos_core_acl_aro and jos_core_acl_groups_aro_map tables. I was finding that while the user integration was running, the mysqld process was grabbing 100% of the CPU and users were unable to login to the site. I decided that switching some of the tables from MyISAM to Innodb should help due to row level locking.

- stopped mysqld and deleted /var/lib/mysql/ibdata1 and the ib_logfiles
- added innodb_buffer_pool_size = 2000M to /etc/my.cnf
- started mysqld and ran ALTER TABLE partners.jos_core_acl_aro ENGINE=InnoDB; ALTER TABLE partners.jos_core_acl_groups_aro_map ENGINE=InnoDB; ALTER TABLE partners.jos_users ENGINE=InnoDB;

I'm now finding that my integration is running but the jos_users table changes are not being written. I've turned on general logging to look at the queries coming in and it looks like the java mysql connector is turning off autocommit and not committing changes at the end of the query. Can anyone suggest how this might be fixed?

Code: Select all

11 Query     /* mysql-connector-java-5.1.12 ( Revision: ${bzr.revision-id} ) */SELECT @@session.auto_increment_increment
                   11 Query     SHOW COLLATION
                   11 Query     SET NAMES latin1
                   11 Query     SET character_set_results = NULL
                   11 Query     SET autocommit=1
                   11 Query     SET sql_mode='STRICT_TRANS_TABLES'
                   11 Query     SET autocommit=0
                   11 Query     insert into `jos_users`(`company`, `block`, `email`, `gid`, `lastvisitDate`, `name`, `password`, `registerDate`, `username`,
`usertype`, `activation`, `params`, `sendEmail`) values ('MyCo', 0, '[email protected]', 37, '2013-11-29 17:47:16', 'Bill
 Gates', 'password6b771e8d2f266975c04f8:SALTjG9DDqqxsCZ91bWm6jDfc4D5wP1k', '1900-01-01 00:00:00', '[email protected]', 'UserType', '', '', 0)

12 Connect   [email protected] on dbname
                   12 Query     /* mysql-connector-java-5.1.12 ( Revision: ${bzr.revision-id} ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_
name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_n
ame = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_n
ame = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'sys
tem_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name =
'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect'
                   12 Query     /* mysql-connector-java-5.1.12 ( Revision: ${bzr.revision-id} ) */SELECT @@session.auto_increment_increment
                   12 Query     SHOW COLLATION
                   12 Query     SET NAMES latin1
                   12 Query     SET character_set_results = NULL
                   12 Query     SET autocommit=1
                   12 Query     SET sql_mode='STRICT_TRANS_TABLES'
                   12 Query     SET autocommit=0
                   12 Query     commit

itoctopus
Joomla! Virtuoso
Joomla! Virtuoso
Posts: 4025
Joined: Mon Nov 25, 2013 4:35 pm
Location: Montreal, Canada
Contact:

Re: user tables converted to innodb changes not committed

Post by itoctopus » Wed Jan 11, 2017 5:58 pm

Is this only affecting the jos_users table, or all InnoDB tables? In any case, can you please share the Java code that issues the query to the database so that we can examine it.

Typically, JDBC will automatically set autocommit to zero in the beginning of transaction, and will then set it to 1 when it ends the transaction. It seems to me that the problem is that you are not properly closing the transaction (that wasn't an issue when using MyISAM because MyISAM is not ACID compliant).
http://www.itoctopus.com - Joomla consulting at its finest
https://twitter.com/itoctopus - Follow us on Twitter

screel
Joomla! Fledgling
Joomla! Fledgling
Posts: 2
Joined: Tue Jan 10, 2017 10:44 am

Re: user tables converted to innodb changes not committed

Post by screel » Thu Jan 12, 2017 7:23 pm

I think the problem is with the auto incrementing jos_users.id field and upserts into InnoDB tables. I've found a workaround by converting the jos_users table back to MyISAM so I can upsert and once that is finished I've written a script which rebuilds jos_core_acl_aro and jos_core_acl_groups_aro_map.

Thanks for the reply


Locked

Return to “Performance - Joomla! 1.5”