Replication error in my.ini

For complex topics that regular users would not be interested in. For power users and database administrators.

Replication error in my.ini

Postby drawab » Fri Jun 22, 2018 6:34 am

For a day One of my replication server seems to totally lock up - when I start the replication on the server - the OpenDental Freezes at the point of logging in to OD - I have figured that the problem may lie in the my.ini file - as when I restore it to default Opendental - with the default my.ini the server runs fine - but when I add the lines of code for the replication process the Opendental freezes at the login prompt on the same computer - looking at the MySQL Workbench there are loads of simultaneous connections overflooding the MySQL connection working at 100%

my present my.ini file is - this is on the second server only for some odd reason

Code: Select all
[mysqld]
basedir="C:/Program Files (x86)/MySQL/MySQL Server 5.6/"
datadir="D:/DATA/"
default-storage-engine=MyISAM
max_allowed_packet=40M
log-bin=mysql-bin
server-id=2
log-slave-updates
replicate-do-db=OPENDENTAL
relay-log=SERVER2-relay-bin
skip-name-resolve
auto_increment_increment=2
auto_increment_offset=2


The reason why MySQL server is 5.6 is because somewhere in a year or so back i downloaded and installed using TrialDownload 15.1.26 which had MySQL 5.6 and it unknowing to me i upgraded the entire setup to MySQL 5.6 - I think im now stuck with this or should I roll back using the later 17.2.43 that is available on your site today

But my question is - is there a potneital bug in the MY.INI or it is something else

Thank
drawab
 
Posts: 39
Joined: Tue Apr 01, 2008 4:57 am

Re: Replication error in my.ini

Postby jsalmon » Fri Jun 22, 2018 9:00 am

I'm most curious as to what your simultaneous connections are doing; Can you run the following query in MySQL Workbench when it's grinding to a halt and provide us the results?
Code: Select all
SHOW PROCESSLIST


Also, maybe you simply just have that many workstations needing to connect and need to increase the amount of connections that MySQL will allow by adding the following line to your my.ini.
Note: this will instantly (well, after you restart the MySQL service) eat up more RAM (for MySQL v5.6+) so if your server doesn't have enough memory you might consider a smaller value.
Code: Select all
max_connections = 500


drawab wrote:The reason why MySQL server is 5.6 is because somewhere in a year or so back i downloaded and installed using TrialDownload 15.1.26 which had MySQL 5.6 and it unknowing to me i upgraded the entire setup to MySQL 5.6 - I think im now stuck with this or should I roll back using the later 17.2.43 that is available on your site today

We rolled out v5.6 because it is much more efficient than v5.5 and we still plan on doing so. However, they changed how they store some data types and it was causing problems with our conversion department and with users trying to connect to old databases that were in the v5.5 format (not possible BTW). You might need to run the "mysql_upgrade.exe" within a command prompt which can be found in bin folder of the MySQL Server 5.6 folder if you haven't already.
Note: this will upgrade all databases on your server, not just the Open Dental database.
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com
User avatar
jsalmon
 
Posts: 1486
Joined: Tue Nov 30, 2010 1:33 pm

Re: Replication error in my.ini

Postby cmcgehee » Mon Jun 25, 2018 9:43 am

I've seen some offices get stuck at the login screen when their primary key range has been used up in the securitylog table. Are you using random primary keys and if so, can you post the result of these two queries?
SELECT * FROM replicationserver;
SELECT COUNT(*) FROM securitylog;
Chris McGehee
Open Dental Software
http://www.opendental.com
User avatar
cmcgehee
 
Posts: 599
Joined: Tue Aug 25, 2015 5:06 pm
Location: Salem, Oregon

Re: Replication error in my.ini

Postby drawab » Thu Jun 28, 2018 1:47 pm

Thank you for the suggestion

cmcgehee wrote:I've seen some offices get stuck at the login screen when their primary key range has been used up in the securitylog table. Are you using random primary keys

This is exactly what was up - I had 1,000,000 - 1,999,999 and it took approximately 2-1/2 years to use these keys up - thats what solved the confusion

Much appreciated
drawab
 
Posts: 39
Joined: Tue Apr 01, 2008 4:57 am


Return to Advanced Topics

Who is online

Users browsing this forum: No registered users and 4 guests