Re: commands out of sync, you can't run this command now'
On 1/8/2016 3:14, Nitin Mehta wrote: Looks like some kind of locking mechanism in the application. Should not be a database issue. On Friday, January 8, 2016 10:47 AM, geetanjali mehra wrote: Hi all, Suddenly I have started getting below error from our web site: 'commands out of sync, you can't run this command now' This error is generated sometimes when opening a page ,and immediately after refreshing the page, error gone. Can you please help me to troubleshoot this issue, so that above error disappears permanently? Is it a database issue or application side issue? http://dev.mysql.com/doc/refman/5.7/en/commands-out-of-sync.html PB - Best Regards, Geetanjali Mehra Senior Database Administrator -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: commands out of sync, you can't run this command now'
Am 08.01.2016 um 10:14 schrieb Nitin Mehta: Looks like some kind of locking mechanism in the application. Should not be a database issue. no true - this is a native mysql error message! honestly both of you should have used Google http://dev.mysql.com/doc/refman/5.7/en/commands-out-of-sync.html http://stackoverflow.com/questions/15798762/mysqli-commands-out-of-sync-you-cant-run-this-command-now On Friday, January 8, 2016 10:47 AM, geetanjali mehra wrote: Hi all, Suddenly I have started getting below error from our web site: 'commands out of sync, you can't run this command now' This error is generated sometimes when opening a page ,and immediately after refreshing the page, error gone. Can you please help me to troubleshoot this issue, so that above error disappears permanently? Is it a database issue or application side issue? signature.asc Description: OpenPGP digital signature
commands out of sync, you can't run this command now'
Hi all, Suddenly I have started getting below error from our web site: 'commands out of sync, you can't run this command now' This error is generated sometimes when opening a page ,and immediately after refreshing the page, error gone. Can you please help me to troubleshoot this issue, so that above error disappears permanently? Is it a database issue or application side issue? Best Regards, Geetanjali Mehra Senior Database Administrator
ANN: Data Sync for MySQL 15.3 released
Hi! SQL Maestro Group announces the release of Data Sync for MySQL 15.3, a powerful and easy-to-use tool for MySQL database contents comparison and synchronization. The new version is immediately available for download at http://www.sqlmaestro.com/products/mysql/datasync/ Top 5 new features: 1. An ability to select or disable certain synchronization operations (Insert, Update, and Delete) has been implemented. The corresponding command line switches have been added as well. 2. Now it is possible to specify SQL scripts to be executed before and/or after synchronizing data of each table. 3. Synchronization script file name and log file name are now stored in the project file. These settings can be overwritten with the appropriate command line options. 4. The first page of the wizard has been redesigned in order to provide more comfortable access to recently opened projects and display the latest company news. 5. The progress windows are redesigned in the modern style and become more informative. Full press-release with explaining screenshots is available at: http://www.sqlmaestro.com/news/company/data_sync_advanced_to_version_15_3/ Background information: --- SQL Maestro Group offers complete database admin, development and management tools for MySQL, SQL Server, PostgreSQL, Oracle, DB2, SQLite, SQL Anywhere, Firebird and MaxDB providing the highest performance, scalability and reliability to meet the requirements of today's database applications. Sincerely yours, The SQL Maestro Group Team http://www.sqlmaestro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Mysql Cluster Sync-UP
Hi Kevin, What do you mean by running MySQL in cluster mode - MySQL Cluster? If so then the data is stored in the data nodes rather than the MySQL Servers and so if bad data is written to one MySQL Server then that same bad data will be viewed through the other MySQL Server too. Regards, Andrew. > -Original Message- > From: Kevin Peterson [mailto:qh.res...@gmail.com] > Sent: 09 April 2013 04:58 > To: mysql@lists.mysql.com > Subject: Mysql Cluster Sync-UP > > Hi, > > I am running My-SQL in cluster mode with two machine. Want to know if > mysql database get corrupted on one of the machine will it force the > corruption on the other machine too or in this case sync between two > mysql instances will stop after the corruption. > > Thanks, > Kevin Peterson > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: how to sync mysql.user table between to two mysql instances
Hello johan Here is what did the trick for me: 1. in mysql-inst1, I created like table called test.user 2. I used mysqldump to pipe test.user table from mysql-inst1 to mysql-inst2 3. in mysql-inst2, I did insert into mysql.user select from test.user We are migrating from master-slave replication to mysql cluster. My sysprog person has been doing all the work. I was told in a clustered environment, replication should happen automatically -- I'm still waiting for my mysql.user table to automatically syncs itself. A long wait it has been. Thanks -Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Friday, March 16, 2012 5:00 PM To: Baron Schwartz Cc: MySql Subject: Re: how to sync mysql.user table between to two mysql instances - Original Message - > From: "Baron Schwartz" > > 1. With mysqldump. mysqldump -hmysql-inst1 mysql | mysql > -hmysql-inst2 mysql And then, of course, issue a FLUSH PRIVILEGES on mysql-inst2 :-) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: how to sync mysql.user table between to two mysql instances
- Original Message - > From: "Baron Schwartz" > > 1. With mysqldump. mysqldump -hmysql-inst1 mysql | mysql > -hmysql-inst2 mysql And then, of course, issue a FLUSH PRIVILEGES on mysql-inst2 :-) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: how to sync mysql.user table between to two mysql instances
I was trying to get a confirmation too. In any event, Charles, I'd try these three steps below and see what happens. I assume that information_schema is populated "on the fly". David. -Original Message- From: Brown, Charles [mailto:cbr...@bmi.com] Sent: Friday, March 16, 2012 2:59 PM To: David Lerer; Baron Schwartz; MySql Subject: RE: how to sync mysql.user table between to two mysql instances Hello David, Precisely, that's what my problem is. The users found in mysqlinst1 are not in mysqlinst2. There are about 30 users defined in inst1 and only 4 in inst2. I would like to sync these tables. How do you do it in short of creating 26 accounts in mysqlinst2 one at a time - too tedious. Help me I'm running out of time. -Original Message- From: David Lerer [mailto:dle...@us.univision.com] Sent: Friday, March 16, 2012 1:36 PM To: Baron Schwartz; MySql; Brown, Charles Subject: RE: how to sync mysql.user table between to two mysql instances As a follow up question, will it be ok to do the following: 1. mysqldump -hmysql-inst2 mysql 2. Backup mysql-inst1 and use the backup to restore to mysql-inst2 3. mysql -hmysql-inst2 mysql This way I hope to be able to refresh a DEV instance from a PROD database, but preserve the permissions, users, passwords, etc (Assuming of course that the schemas are identical on the two instances). Thanks, David. -Original Message- From: baron.schwa...@gmail.com [mailto:baron.schwa...@gmail.com] On Behalf Of Baron Schwartz Sent: Friday, March 16, 2012 2:20 PM To: MySql Subject: Re: how to sync mysql.user table between to two mysql instances Charles, 1. With mysqldump. mysqldump -hmysql-inst1 mysql | mysql -hmysql-inst2 mysql 2. With pt-table-sync from Percona Toolkit if you need something more sophisticated. On Fri, Mar 16, 2012 at 1:27 PM, Brown, Charles wrote: > Can someone instruct me on how to sync mysql.user table between to two mysql > instances. > In other words, I wouild like to copy mysql.user from mysql-inst1 to > mysql-inst2 + -- Baron Schwartz Win free MySQL conference tickets! http://goo.gl/mvZ4W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: how to sync mysql.user table between to two mysql instances
Hello David, Precisely, that's what my problem is. The users found in mysqlinst1 are not in mysqlinst2. There are about 30 users defined in inst1 and only 4 in inst2. I would like to sync these tables. How do you do it in short of creating 26 accounts in mysqlinst2 one at a time - too tedious. Help me I'm running out of time. -Original Message- From: David Lerer [mailto:dle...@us.univision.com] Sent: Friday, March 16, 2012 1:36 PM To: Baron Schwartz; MySql; Brown, Charles Subject: RE: how to sync mysql.user table between to two mysql instances As a follow up question, will it be ok to do the following: 1. mysqldump -hmysql-inst2 mysql 2. Backup mysql-inst1 and use the backup to restore to mysql-inst2 3. mysql -hmysql-inst2 mysql This way I hope to be able to refresh a DEV instance from a PROD database, but preserve the permissions, users, passwords, etc (Assuming of course that the schemas are identical on the two instances). Thanks, David. -Original Message- From: baron.schwa...@gmail.com [mailto:baron.schwa...@gmail.com] On Behalf Of Baron Schwartz Sent: Friday, March 16, 2012 2:20 PM To: MySql Subject: Re: how to sync mysql.user table between to two mysql instances Charles, 1. With mysqldump. mysqldump -hmysql-inst1 mysql | mysql -hmysql-inst2 mysql 2. With pt-table-sync from Percona Toolkit if you need something more sophisticated. On Fri, Mar 16, 2012 at 1:27 PM, Brown, Charles wrote: > Can someone instruct me on how to sync mysql.user table between to two mysql > instances. > In other words, I wouild like to copy mysql.user from mysql-inst1 to > mysql-inst2 + -- Baron Schwartz Win free MySQL conference tickets! http://goo.gl/mvZ4W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: how to sync mysql.user table between to two mysql instances
As a follow up question, will it be ok to do the following: 1. mysqldump -hmysql-inst2 mysql 2. Backup mysql-inst1 and use the backup to restore to mysql-inst2 3. mysql -hmysql-inst2 mysql This way I hope to be able to refresh a DEV instance from a PROD database, but preserve the permissions, users, passwords, etc (Assuming of course that the schemas are identical on the two instances). Thanks, David. -Original Message- From: baron.schwa...@gmail.com [mailto:baron.schwa...@gmail.com] On Behalf Of Baron Schwartz Sent: Friday, March 16, 2012 2:20 PM To: MySql Subject: Re: how to sync mysql.user table between to two mysql instances Charles, 1. With mysqldump. mysqldump -hmysql-inst1 mysql | mysql -hmysql-inst2 mysql 2. With pt-table-sync from Percona Toolkit if you need something more sophisticated. On Fri, Mar 16, 2012 at 1:27 PM, Brown, Charles wrote: > Can someone instruct me on how to sync mysql.user table between to two mysql > instances. > In other words, I wouild like to copy mysql.user from mysql-inst1 to > mysql-inst2 + -- Baron Schwartz Win free MySQL conference tickets! http://goo.gl/mvZ4W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: how to sync mysql.user table between to two mysql instances
Charles, 1. With mysqldump. mysqldump -hmysql-inst1 mysql | mysql -hmysql-inst2 mysql 2. With pt-table-sync from Percona Toolkit if you need something more sophisticated. On Fri, Mar 16, 2012 at 1:27 PM, Brown, Charles wrote: > Can someone instruct me on how to sync mysql.user table between to two mysql > instances. > In other words, I wouild like to copy mysql.user from mysql-inst1 to > mysql-inst2 + -- Baron Schwartz Win free MySQL conference tickets! http://goo.gl/mvZ4W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
how to sync mysql.user table between to two mysql instances
Can someone instruct me on how to sync mysql.user table between to two mysql instances. In other words, I wouild like to copy mysql.user from mysql-inst1 to mysql-inst2 + Thanks This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you.
ANN: Data Sync for MySQL released
Hi! SQL Maestro Group starts a new product family and announces the release of Data Sync for MySQL, a powerful and easy-to-use tool for MySQL database contents comparison and synchronization. http://www.sqlmaestro.com/products/mysql/datasync/ Key product features: 1. Comparing and deploying changes to database contents. 2. Automatic creation of error-free synchronization scripts. 3. Easy-to-read difference display. 4. Saving all options to a project file for instant re-execution. 5. Custom comparison keys and flexible auto-mapping tools. 6. Full control over the comparison and synchronization. 7. Powerful command-line interface for continuous integration. Full press-release is available at: http://www.sqlmaestro.com/news/company/datasync_product_family_started/ Background information: --- SQL Maestro Group offers complete database admin, development and management tools for MySQL, SQL Server, PostgreSQL, Oracle, DB2, SQLite, SQL Anywhere, Firebird and MaxDB providing the highest performance, scalability and reliability to meet the requirements of today's database applications. Sincerely yours, The SQL Maestro Group Team http://www.sqlmaestro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
[question]master-slave sync delay
Hi, all: There is a problem about master-slave sync delay, I find some infomations from 'show processlist', some like that: 1051system user nsConnect 10 update xxx what does Connect mean, mysql manual explained: Connect A replication slave is connected to its master. Then, There is 10s delay from master to slave. I want to know why does this happend, slow network I/O? or something wrong with this update SQL? Thanks Regards W.J
Replication Sync Error
Hi All, We are running semi-sync replication on production server, last week found out that replication suck at some event schedule created at master, as that was not important i skipped that started replication again, found out that slave is days behind at master, i invested gate more and found that it is read 200 MB of binary file and few more need to be read by salve get in sync, still i think 200 MB file should not take hours to read, any ways but in error logs i am keep on getting following error 110623 15:50:56 [Note] Semi-sync replication switched OFF. 110623 15:50:56 [Note] Semi-sync replication switched ON with slave (server_id: 2) at (master-bin.31, 15494716) 110623 15:51:02 [Warning] Timeout waiting for reply of binlog (file: master-bin.31, pos: 15505733), semi-sync up to file master-bin.31, position 15505129. can any one help on this. --Anupam
Re: Out of sync tables
Michael and Reindl Thank you both for you help and patience. I have inserted the $id = mysql_insert_id($connection) as well as the code in the INSERT clause and it seems to be working fine. One note is it turns out I did have AI on both tables, so that may have been adding a monkey wrench. I also took the advice to moved the honey pot code out of the middle of that code and moved it up. Again, thank you. Gary "Reindl Harald" wrote in message news:4da87902.9030...@thelounge.net... __ Information from ESET Smart Security, version of virus signature database 6045 (20110415) __ The message was checked by ESET Smart Security. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Out of sync tables
- Original Message - > From: "Gary" > > I'm not sure I undertand this, could you explain a little further for > me. This is what they're talking about: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Out of sync tables
Am 15.04.2011 18:52, schrieb Gary: > I'm sorry, I am unfamliar with an asc file, so I have not opened them. > > GAry > > > "Reindl Harald" wrote in message > news:4da87554.8030...@thelounge.net... you should not open them if your mail client would have gpg you would see a signed message and if not you have a useless attachment, ignore it and try to understand the message and questions i sent you congratulations: the second person in ten years who do not know mail-sigantures :-) signature.asc Description: OpenPGP digital signature
Re: Out of sync tables
I'm sorry, I am unfamliar with an asc file, so I have not opened them. GAry "Reindl Harald" wrote in message news:4da87554.8030...@thelounge.net... __ Information from ESET Smart Security, version of virus signature database 6044 (20110415) __ The message was checked by ESET Smart Security. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Out of sync tables
Am 15.04.2011 18:34, schrieb Gary: > Michael > > I'm sorry, I should have removed this code for the post, but this code is > part of a "honey pot". There is no 'address' input visible, it is used so > if a spam bot enters information into this field, it kills the form. Humans > cannot enter anything into this field. but why somewhere middle in the code instead making clean verifications at the begin? > ""Again, I see no reason you could not call last_insert_id() after the > first insert and use that value explicitly in the second. > "" > > I'm not sure I undertand this, could you explain a little further for me as long you never use the insert-id from the AI i wonder how there could be anything out of sync because there never can be anything in sync signature.asc Description: OpenPGP digital signature
Re: Out of sync tables
Michael
I'm sorry, I should have removed this code for the post, but this code is
part of a "honey pot". There is no 'address' input visible, it is used so
if a spam bot enters information into this field, it kills the form. Humans
cannot enter anything into this field.
if ($_POST['address'] != '' ) {
die("Changed field");
}
""Again, I see no reason you could not call last_insert_id() after the
first insert and use that value explicitly in the second.
""
I'm not sure I undertand this, could you explain a little further for me.
Again, thank you for your help.
Gary
"Michael Dykman" wrote in message
news:banlktimeyexp3lxozvgoz0fedrrf69r...@mail.gmail.com...
The first thing I notice browsing your code is this block stuck
immediately between your 2 insert statements:
if ($_POST['address'] != '' ) {
die("Changed field");
}
This guarantees that your 2 auto_increment sequences will fall out of
sync any time any client POSTs (and perhaps all gets?) to this script
without an 'address' parameter.
Again, I see no reason you could not call last_insert_id() after the
first insert and use that value explicitly in the second.
- michael dykman
On Fri, Apr 15, 2011 at 12:16 PM, Reindl Harald
wrote:
>
>
> Am 15.04.2011 17:59, schrieb Gary:
>> Michael, thank you for your reply
>>
>> ""Might I suggest, instead of the 2 part juggling act, you drop the
>> auto-increment property on your second table, and just use the value
>> derived from the first as the joining key in the second. Then there
>> is only one sequence to worry about with nothing to sync against
>> ""
>>
>> There is only one AI into the main page. This is the insert code, I have
>> probably left more in than you need to see.
>>
>> What I also did was to add some duplicate columns in the two tables
>> (email,
>> ip, timestamp) so in the event I need to manually to in I would be able
>> to
>> decifer who goes where.
>>
>> On second look, it would appear I am NOT using a join, but two
>> inserts I
>> don't recall why I did it that way
>
> this code is unreadable for me because of its coding-style and if i see
> "addslashes" for database inserts i start to fear and run away
>
> you are using two inserts so what do you do there and where can be
> anything
> out of sync on the database-level? where is the magic in your code without
> using mysql_insert_id() or LAST_INSERT_ID() - what should this code do?
>
> * insert in main table
> * fetch mysql_insert_id() what is thread-safe
> * use that value in the second table
>
>
> and please do not use such ugly hacks as in the begin of your code
> addslashes() has no useable security for user-input
>
> even mysql_escape_string() has not -> mysql_real_escape_string()
>
>
>
--
- michael dykman
- mdyk...@gmail.com
May the Source be with you.
__ Information from ESET Smart Security, version of virus signature
database 6044 (20110415) __
The message was checked by ESET Smart Security.
http://www.eset.com
__ Information from ESET Smart Security, version of virus signature
database 6044 (20110415) __
The message was checked by ESET Smart Security.
http://www.eset.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Out of sync tables
The first thing I notice browsing your code is this block stuck
immediately between your 2 insert statements:
if ($_POST['address'] != '' ) {
die("Changed field");
}
This guarantees that your 2 auto_increment sequences will fall out of
sync any time any client POSTs (and perhaps all gets?) to this script
without an 'address' parameter.
Again, I see no reason you could not call last_insert_id() after the
first insert and use that value explicitly in the second.
- michael dykman
On Fri, Apr 15, 2011 at 12:16 PM, Reindl Harald wrote:
>
>
> Am 15.04.2011 17:59, schrieb Gary:
>> Michael, thank you for your reply
>>
>> ""Might I suggest, instead of the 2 part juggling act, you drop the
>> auto-increment property on your second table, and just use the value
>> derived from the first as the joining key in the second. Then there
>> is only one sequence to worry about with nothing to sync against
>> ""
>>
>> There is only one AI into the main page. This is the insert code, I have
>> probably left more in than you need to see.
>>
>> What I also did was to add some duplicate columns in the two tables (email,
>> ip, timestamp) so in the event I need to manually to in I would be able to
>> decifer who goes where.
>>
>> On second look, it would appear I am NOT using a join, but two inserts I
>> don't recall why I did it that way
>
> this code is unreadable for me because of its coding-style and if i see
> "addslashes" for database inserts i start to fear and run away
>
> you are using two inserts so what do you do there and where can be anything
> out of sync on the database-level? where is the magic in your code without
> using mysql_insert_id() or LAST_INSERT_ID() - what should this code do?
>
> * insert in main table
> * fetch mysql_insert_id() what is thread-safe
> * use that value in the second table
>
>
> and please do not use such ugly hacks as in the begin of your code
> addslashes() has no useable security for user-input
>
> even mysql_escape_string() has not -> mysql_real_escape_string()
>
>
>
--
- michael dykman
- mdyk...@gmail.com
May the Source be with you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Out of sync tables
Am 15.04.2011 17:59, schrieb Gary: > Michael, thank you for your reply > > ""Might I suggest, instead of the 2 part juggling act, you drop the > auto-increment property on your second table, and just use the value > derived from the first as the joining key in the second. Then there > is only one sequence to worry about with nothing to sync against > "" > > There is only one AI into the main page. This is the insert code, I have > probably left more in than you need to see. > > What I also did was to add some duplicate columns in the two tables (email, > ip, timestamp) so in the event I need to manually to in I would be able to > decifer who goes where. > > On second look, it would appear I am NOT using a join, but two inserts I > don't recall why I did it that way this code is unreadable for me because of its coding-style and if i see "addslashes" for database inserts i start to fear and run away you are using two inserts so what do you do there and where can be anything out of sync on the database-level? where is the magic in your code without using mysql_insert_id() or LAST_INSERT_ID() - what should this code do? * insert in main table * fetch mysql_insert_id() what is thread-safe * use that value in the second table and please do not use such ugly hacks as in the begin of your code addslashes() has no useable security for user-input even mysql_escape_string() has not -> mysql_real_escape_string() signature.asc Description: OpenPGP digital signature
Re: Out of sync tables
Michael, thank you for your reply
""Might I suggest, instead of the 2 part juggling act, you drop the
auto-increment property on your second table, and just use the value
derived from the first as the joining key in the second. Then there
is only one sequence to worry about with nothing to sync against
""
There is only one AI into the main page. This is the insert code, I have
probably left more in than you need to see.
What I also did was to add some duplicate columns in the two tables (email,
ip, timestamp) so in the event I need to manually to in I would be able to
decifer who goes where.
On second look, it would appear I am NOT using a join, but two inserts I
don't recall why I did it that way
**
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}
$ipi_var_Recordset1 = "-1";
if (isset($_SERVER['REMOTE_ADDR'])) {
$em_var_Recordset1 = (get_magic_quotes_gpc()) ? $_SERVER['REMOTE_ADDR'] :
addslashes($_SERVER['REMOTE_ADDR']);
}
if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form")) {
$insertSQL = sprintf("INSERT INTO attorney (email, firm_name, ip) VALUES
(%s, %s, %s)",
GetSQLValueString($_POST['email'], "text"),
GetSQLValueString($_POST['firm_name'], "text"),
GetSQLValueString($_SERVER['REMOTE_ADDR'], "text"));
mysql_select_db($database_assess, $assess_remote);
$Result1 = mysql_query($insertSQL, $assess_remote) or die(mysql_error());
}
// checks if bot
if ($_POST['address'] != '' ){
die("Changed field");
}
$firmname_var_Recordset1 = "-1";
if (isset($_POST['firm_name'])) {
$firmname_var_Recordset1 = (get_magic_quotes_gpc()) ? $_POST['firm_name']
: addslashes($_POST['firm_name']);
}
$username_var_Recordset1 = "-1";
if (isset($_post['user_name'])) {
$username_var_Recordset1 = (get_magic_quotes_gpc()) ? $_POST['user_name']
: addslashes($_POST['user_name']);
}
$pw_var_Recordset1 = "-1";
if (isset($_POST['password'])) {
$pw_var_Recordset1 = (get_magic_quotes_gpc()) ? $_POST['password'] :
addslashes($_POST['password']);
}
$em_var_Recordset1 = "-1";
if (isset($_POST['email_2'])) {
$em_var_Recordset1 = (get_magic_quotes_gpc()) ? $_POST['email_2'] :
addslashes($_POST['email_2']);
}
$ip_var_Recordset1 = "-1";
if (isset($_SERVER['REMOTE_ADDR'])) {
$em_var_Recordset1 = (get_magic_quotes_gpc()) ? $_SERVER['REMOTE_ADDR'] :
addslashes($_SERVER['REMOTE_ADDR']);
}
mysql_select_db($database_assess, $assess_remote);
$query_Recordset1 = sprintf("SELECT law_firm.firm_id FROM law_firm WHERE
law_firm.firm_name = %s AND law_firm.user_name = %s AND law_firm.password=%s
AND law_firm.ip=%s" , GetSQLValueString($firmname_var_Recordset1,
"text"),GetSQLValueString($username_var_Recordset1,
"text"),GetSQLValueString($pw_var_Recordset1,
"text"),GetSQLValueString($em_var_Recordset1,
"text"),GetSQLValueString($ip_var_Recordset1, "text"));
$Recordset1 = mysql_query($query_Recordset1, $assess_remote) or
die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
$row = mysql_fetch_assoc($Recordset1);
$_SESSION['firm_id'] = $row['firm_id'];
echo $row['firm_id'];
if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form")) {
$insertSQL = sprintf("INSERT INTO law_firm (firm_name, user_name,
password, slsmn, email_2, ip) VALUES (%s, %s, %s, %s, %s, %s)" ,
GetSQLValueString($_POST['firm_name'], "text"),
GetSQLValueString($_POST['user_name'], "text"),
GetSQLValueString($_POST['password'], "text"),
GetSQLValueString($_POST['slsmn'], "text"),
GetSQLValueString($_POST['email_2'], "text"),
GetSQLValueString($_SERVER['REMOTE_ADDR'], "text"));
mysql_select_db($database_assess, $assess_remote);
$Result1 = mysql_query($insertSQL, $assess_remote) or die(mysql_error());
"Michael Dykman" wrote in message
news:BANLkTi=+fpW=-qbmb6tcfffhr1gb32w...@mail.gmail.com...
I presume you are inserting to both tables always at the same time
inside a transaction? We would need to see the code to see how you
are inserting them..
Might I suggest, instead of the 2 part juggling act, you drop the
auto-increment property on your second table, and just use
Re: Out of sync tables
I presume you are inserting to both tables always at the same time inside a transaction? We would need to see the code to see how you are inserting them.. Might I suggest, instead of the 2 part juggling act, you drop the auto-increment property on your second table, and just use the value derived from the first as the joining key in the second. Then there is only one sequence to worry about with nothing to sync against - michael dykman On Fri, Apr 15, 2011 at 10:33 AM, Gary wrote: > I have a innodb with a join on two tables. The foreign key is the id > column which is set to AI. I have been having an issue of the tables being > out of sync in that the id is not the same on the two tables. I have > corrected this a couple of times in phpmyadmin by resetting the > auto_increment to the same number in both tables, but it is re-occurring. > > I have tried to duplicate how this occurs, and I believe it is occurring on > a page refresh, but I am not positive. > > I don't know what information / code to post that someone would be able to > make a suggestion to a solution, so if someone could direct me to what to > post I would be appreciative. > > > -- > Gary > > > > __ Information from ESET Smart Security, version of virus signature > database 6044 (20110415) __ > > The message was checked by ESET Smart Security. > > http://www.eset.com > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com > > -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Out of sync tables
I have a innodb with a join on two tables. The foreign key is the id column which is set to AI. I have been having an issue of the tables being out of sync in that the id is not the same on the two tables. I have corrected this a couple of times in phpmyadmin by resetting the auto_increment to the same number in both tables, but it is re-occurring. I have tried to duplicate how this occurs, and I believe it is occurring on a page refresh, but I am not positive. I don't know what information / code to post that someone would be able to make a suggestion to a solution, so if someone could direct me to what to post I would be appreciative. -- Gary __ Information from ESET Smart Security, version of virus signature database 6044 (20110415) __ The message was checked by ESET Smart Security. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: running a mysql query inside a loop of another without a sync error
>From 1 feet, what you are attempting to do looks like it would be
very easily accomplished with a join, leaving you with 1 result set to
process. I realize that isn't the question, but it might be a
solution.
- michael dykman
On Wed, Jan 19, 2011 at 9:54 PM, Delan Azabani wrote:
> Hi all,
>
> I'm using MySQL with C in a CGI application. I hope this is the right
> list to ask for help.
>
> If I have this simplified code:
>
> MYSQL_RES *res;
> MYSQL_ROW row;
> mysql_query(mysql, "some select query");
> res = mysql_use_result(mysql);
> while (row = mysql_fetch_row(res)) {
> MYSQL_RES *res2;
> MYSQL_ROW row2;
> mysql_query(mysql, "some other select query using an id from the
> first");
> res2 = mysql_use_result(mysql);
> /* ... */
> mysql_free_result(res2);
> }
> mysql_free_result(res);
>
> Whenever I run the second query, inside the loop, I get the nasty
> 'commands out of sync' error. How can I run a select query while in a
> loop fetching rows from another select query? Or, do I have to fetch all
> the rows completely first and store them in memory (which wouldn't be
> very 'nice' to do)?
>
> If someone could help me with this problem, it would be greatly appreciated.
>
> --
> Thanks and best regards,
> Delan Azabani
> http://azabani.com/
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com
>
>
--
- michael dykman
- mdyk...@gmail.com
May the Source be with you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: running a mysql query inside a loop of another without a sync error
Hello Delan,
On 1/19/2011 21:54, Delan Azabani wrote:
Hi all,
I'm using MySQL with C in a CGI application. I hope this is the right
list to ask for help.
If I have this simplified code:
MYSQL_RES *res;
MYSQL_ROW row;
mysql_query(mysql, "some select query");
res = mysql_use_result(mysql);
while (row = mysql_fetch_row(res)) {
MYSQL_RES *res2;
MYSQL_ROW row2;
mysql_query(mysql, "some other select query using an id from the
first");
res2 = mysql_use_result(mysql);
/* ... */
mysql_free_result(res2);
}
mysql_free_result(res);
Whenever I run the second query, inside the loop, I get the nasty
'commands out of sync' error. How can I run a select query while in a
loop fetching rows from another select query? Or, do I have to fetch all
the rows completely first and store them in memory (which wouldn't be
very 'nice' to do)?
If someone could help me with this problem, it would be greatly appreciated.
The mysql object you are using for your connection can only have one
active query or result on it at a time. To have two sets of results
working, you need a second independent connection to the MySQL server
http://dev.mysql.com/doc/refman/5.5/en/c-api-data-structures.html
###
MYSQL
This structure represents a handle to one database connection. It is
used for almost all MySQL functions. You should not try to make a copy
of a MYSQL structure. There is no guarantee that such a copy will be
usable.
###
http://dev.mysql.com/doc/refman/5.5/en/mysql-real-connect.html
###
The first parameter should be the address of an existing MYSQL
structure. Before calling mysql_real_connect() you must call
mysql_init() to initialize the MYSQL structure. You can change a lot of
connect options with the mysql_options() call. See Section 22.9.3.49,
“mysql_options()”.
###
http://dev.mysql.com/doc/refman/5.5/en/threaded-clients.html
###
Two threads can't send a query to the MySQL server at the same time on
the same connection. In particular, you have to ensure that between
calls to mysql_query() and mysql_store_result() no other thread is using
the same connection.
###
This same rule applies to attempting to process more than one query on
the same connection. You must complete the first query before starting
the second or you must open a separate connection to handle the second
query.
Yours,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
running a mysql query inside a loop of another without a sync error
Hi all,
I'm using MySQL with C in a CGI application. I hope this is the right
list to ask for help.
If I have this simplified code:
MYSQL_RES *res;
MYSQL_ROW row;
mysql_query(mysql, "some select query");
res = mysql_use_result(mysql);
while (row = mysql_fetch_row(res)) {
MYSQL_RES *res2;
MYSQL_ROW row2;
mysql_query(mysql, "some other select query using an id from the
first");
res2 = mysql_use_result(mysql);
/* ... */
mysql_free_result(res2);
}
mysql_free_result(res);
Whenever I run the second query, inside the loop, I get the nasty
'commands out of sync' error. How can I run a select query while in a
loop fetching rows from another select query? Or, do I have to fetch all
the rows completely first and store them in memory (which wouldn't be
very 'nice' to do)?
If someone could help me with this problem, it would be greatly appreciated.
--
Thanks and best regards,
Delan Azabani
http://azabani.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: master-slave replication sync problems.
On Thu, Aug 26, 2010 at 6:04 AM, Norman Khine wrote: > hello, > i have a working master-slave replication, the problem i find is that > if i restart the MASTER there is a difference in the MASTER_LOG_FILE > and MASTER_LOG_POS on the SLAVE. > > what is the correct way to keep the two slaves in sync even after i > restart the server. the way i do it now is to: As one other stated, it should not lose position when you stop and start the master or slave in a controlled manner. And also stated was that the way you were doing it was causing a loss of data, and you'll need to use maatkit to fix it, or if you can afford some downtime, stop mysql on both and copy the whole database(s) from master to slave (and reconfigure replication of course). On the slave, I'd be curious what the result was when you did only a "STOP SLAVE;" and then "START SLAVE;" because in my estimation, chances are it would have picked right up where it was supposed to. Your issue is likely that your slave has a large "slave_net_timeout" value (default is 3600 seconds IIRC). That means your slave will take 1 hour to finally give up on the replication connection (that the master closed on it), close the connection, and then reopen it. Lower that setting to something smaller, I've gone as low as 30 seconds, but I would only do that on a reliable network. Try lowering it to 300 (5 minutes) on your slave. When you restart the master, the slave should timeout after only 5 minutes and then resume replication. -- Regards... Todd I seek the truth...it is only persistence in self-delusion and ignorance that does harm. -- Marcus Aurealius -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: master-slave replication sync problems.
Hello List, On 8/26/2010 3:00 PM, Daevid Vincent wrote: ssh to the slave mysql -uroot -pPASSWORD -P3306 -hlocalhost show slave status\G If the Slave IO is NOT Running, but SQL is, then simply try to restart the slave... *** 1. row *** Slave_IO_State: Master_Host: 10.10.10.45 Master_User: slave Master_Port: 3306 ... Slave_IO_Running: No Slave_SQL_Running: Yes start slave; show slave status\G Otherwise if it's a replication issue such as this, then you must skip over the bad SQL queries one at a time till the slave syncs. *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 10.10.10.41 Master_User: slave Master_Port: 3306 ... Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: agis_core_2008 Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1061 Last_Error: Error 'Duplicate key name 'id_operator'' on query. The SQL statement will give you an idea of where the master and slave went askew. If these are recent commands you did you can guess as to how much to increment the SKIP_COUNTER below, otherwise, you just have to do it one at a time until they sync again. mysql> stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave; show slave status\G Repeat the above statements over and over until you see two YES rows. *** 1. row *** Slave_IO_Running: Yes Slave_SQL_Running: Yes -Original Message- From: Norman Khine [mailto:nor...@khine.net] Sent: Thursday, August 26, 2010 6:05 AM To: mysql@lists.mysql.com Subject: master-slave replication sync problems. hello, i have a working master-slave replication, the problem i find is that if i restart the MASTER there is a difference in the MASTER_LOG_FILE and MASTER_LOG_POS on the SLAVE. what is the correct way to keep the two slaves in sync even after i restart the server. the way i do it now is to: [MASTER] mysql> show master status; +--+--+--+ --+ | mysql-bin.10 | 13405429 | upgrade,tracker,bugs | mysql,information_schema | +--+--+--+ --+ 1 row in set (0.00 sec) [SLAVE] mysql> stop slave; mysql> change master to MASTER_HOST='master.domain.com', MASTER_USER='replicator', MASTER_PASSWORD='xxx', MASTER_LOG_FILE='mysql-bin.10', MASTER_LOG_POS=13301215; mysql> start slave; is this correct or is there a better way to do this? thanks To me, it appears that many of you are not fully versed in the theory of operations for how MySQL replication actually functions. Granted, there are two formats for replication (ROW and STATEMENT) but the general process remains the same. Here is a nutshell summary of the process. ** on the master ** m1) The MySQL master is instructed to change some data. m2) The data is changed and the results are committed to disk. If you rollback the changes before you commit them, then there is nothing to replicate. Only the InnoDB engine supports this type of rollback. m3) The change committed in step 2 is written to the binary log (repeat from step m1 until the Master is shutdown) ** on the slave - the IO thread ** (assuming that the slave is already configured with compatible data, a starting position, and the proper credentials to act as a slave) o1) The SLAVE IO thread requests information from the master's binary logs. This information is identified by a file name and a byte offset from the start of that file o2) The SLAVE IO thread copies all available information from the master's binary logs into a local copy of those logs known as the relay logs. (repeat from o1 until the SLAVE IO thread is stopped(by error or by command) or the slave is shutdown) ** on the slave - the SQL thread ** s1) Once an unapplied change has been completely buffered into the relay logs, the SLAVE SQL thread attempts to apply the change to the slave's data. s2) If LOG SLAVE UPDATES is enabled, copy the applied change (using the correct format) into the slave's binary log. (repeat from s1 until the SLAVE SQL thread is stopped (by error or by command) or the slave is shutdown) ** As you can tell by this very simplified process description, there is no attempt to rectify one dataset to the other. Replication operates under the principle that if you perform identical changes to identical sets of
RE: master-slave replication sync problems.
ssh to the slave mysql -uroot -pPASSWORD -P3306 -hlocalhost show slave status\G If the Slave IO is NOT Running, but SQL is, then simply try to restart the slave... *** 1. row *** Slave_IO_State: Master_Host: 10.10.10.45 Master_User: slave Master_Port: 3306 ... Slave_IO_Running: No Slave_SQL_Running: Yes start slave; show slave status\G Otherwise if it's a replication issue such as this, then you must skip over the bad SQL queries one at a time till the slave syncs. *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 10.10.10.41 Master_User: slave Master_Port: 3306 ... Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: agis_core_2008 Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1061 Last_Error: Error 'Duplicate key name 'id_operator'' on query. The SQL statement will give you an idea of where the master and slave went askew. If these are recent commands you did you can guess as to how much to increment the SKIP_COUNTER below, otherwise, you just have to do it one at a time until they sync again. mysql> stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave; show slave status\G Repeat the above statements over and over until you see two YES rows. *** 1. row *** Slave_IO_Running: Yes Slave_SQL_Running: Yes > -Original Message- > From: Norman Khine [mailto:nor...@khine.net] > Sent: Thursday, August 26, 2010 6:05 AM > To: mysql@lists.mysql.com > Subject: master-slave replication sync problems. > > hello, > i have a working master-slave replication, the problem i find is that > if i restart the MASTER there is a difference in the MASTER_LOG_FILE > and MASTER_LOG_POS on the SLAVE. > > what is the correct way to keep the two slaves in sync even after i > restart the server. the way i do it now is to: > > [MASTER] > mysql> show master status; > > +--+--+--+ > --+ > | mysql-bin.10 | 13405429 | upgrade,tracker,bugs | > mysql,information_schema | > +--+--+--+ > --+ > 1 row in set (0.00 sec) > > [SLAVE] > mysql> stop slave; > mysql> change master to MASTER_HOST='master.domain.com', > MASTER_USER='replicator', MASTER_PASSWORD='xxx', > MASTER_LOG_FILE='mysql-bin.10', MASTER_LOG_POS=13301215; > mysql> start slave; > > is this correct or is there a better way to do this? > > thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: master-slave replication sync problems.
Quoting jitendra ranjan : Hi, The best way to use sync the master and slave is using mk-checksum tools. just google it and use the the tools for online sync of master and slave. Jeetendra Ranjan MySQL DBA Yes, data integrity isnt guaranteed with MySQL replication. So if you want to be sure good idea to use the maartkit tools... Tho this isnt necessarily answering the original question, which is to do with the master and slave not showing the same binlog file and log position. If replication is working at all, these will be in sync (assuming the changes on the master arent happening faster than replication can keep up). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: master-slave replication sync problems.
Quoting Ananda Kumar : Smith, I never said, this wont work.Some times, there are chances of lossing data. regards anandkl If you have experience of this fair enough. Theres no reason it should make any difference, as everything is based upon the binlog file and position... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: master-slave replication sync problems.
Hi,
The best way to use sync the master and slave is using mk-checksum tools. just
google it and use the the tools for online sync of master and slave.
Jeetendra Ranjan
MySQL DBA
--- On Thu, 26/8/10, Norman Khine wrote:
From: Norman Khine
Subject: master-slave replication sync problems.
To: mysql@lists.mysql.com
Date: Thursday, 26 August, 2010, 6:34 PM
hello,
i have a working master-slave replication, the problem i find is that
if i restart the MASTER there is a difference in the MASTER_LOG_FILE
and MASTER_LOG_POS on the SLAVE.
what is the correct way to keep the two slaves in sync even after i
restart the server. the way i do it now is to:
[MASTER]
mysql> show master status;
+--+--+--+--+
| mysql-bin.10 | 13405429 | upgrade,tracker,bugs |
mysql,information_schema |
+--+--+--+--+
1 row in set (0.00 sec)
[SLAVE]
mysql> stop slave;
mysql> change master to MASTER_HOST='master.domain.com',
MASTER_USER='replicator', MASTER_PASSWORD='xxx',
MASTER_LOG_FILE='mysql-bin.10', MASTER_LOG_POS=13301215;
mysql> start slave;
is this correct or is there a better way to do this?
thanks
--
˙uʍop ǝpısdn p,uɹnʇ pןɹoʍ ǝɥʇ ǝǝs noʎ 'ʇuǝɯɐן sǝɯıʇ ǝɥʇ puɐ 'ʇuǝʇuoɔ
ǝq s,ʇǝן ʇǝʎ
%>>> "".join( [ {'*':'@','^':'.'}.get(c,None) or
chr(97+(ord(c)-83)%26) for c in ",adym,*)&uzq^zqf" ] )
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=jitendra_ran...@yahoo.com
Re: master-slave replication sync problems.
Smith, I never said, this wont work.Some times, there are chances of lossing data. regards anandkl On Thu, Aug 26, 2010 at 8:48 PM, wrote: > Quoting Norman Khine : > > i see, so the best is to just stop slave and then check the master >> status, and when the master status syncs then i start the slave? >> >> >> >> > Well Im willing to hear from others experiences, but if you really shouldnt > have to do anything. If you want you can issue a stop slave before the > reboot and a start slave after, but it shouldnt make any difference. And Im > not sure what you refer to when you talk about the master status syncing. > The master will continue to increment the binlog when changes are made > regardless of whether the slave is up. If the slave has been rebooted and is > behind the master binlog pos it should automatically sync those changes when > started... > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com > >
Re: master-slave replication sync problems.
Quoting Norman Khine : i see, so the best is to just stop slave and then check the master status, and when the master status syncs then i start the slave? Well Im willing to hear from others experiences, but if you really shouldnt have to do anything. If you want you can issue a stop slave before the reboot and a start slave after, but it shouldnt make any difference. And Im not sure what you refer to when you talk about the master status syncing. The master will continue to increment the binlog when changes are made regardless of whether the slave is up. If the slave has been rebooted and is behind the master binlog pos it should automatically sync those changes when started... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: master-slave replication sync problems.
Yes,
You need to note down the master bin-log file name and position on the
slave, this is a must.
regards
anandkl
On Thu, Aug 26, 2010 at 7:34 PM, Norman Khine wrote:
> i see, so the best is to just stop slave and then check the master
> status, and when the master status syncs then i start the slave?
>
>
>
> On Thu, Aug 26, 2010 at 3:09 PM, wrote:
> > That is really bad, you will loose changes.
> >
> > You shouldnt have to do anything when rebooting either the master or
> slave.
> > If the master is down, then the slave recieves no updates. If the slave
> is
> > down, when it comes back up it checks the master log pos and plays thro
> any
> > changes that are necessary to bring it up to date...
> >
> >
> >
> >
> >
>
>
>
> --
> ˙uʍop ǝpısdn p,uɹnʇ pןɹoʍ ǝɥʇ ǝǝs noʎ 'ʇuǝɯɐן sǝɯıʇ ǝɥʇ puɐ 'ʇuǝʇuoɔ
> ǝq s,ʇǝן ʇǝʎ
> %>>> "".join( [ {'*':'@','^':'.'}.get(c,None) or
> chr(97+(ord(c)-83)%26) for c in ",adym,*)&uzq^zqf" ] )
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com
>
>
Re: master-slave replication sync problems.
i see, so the best is to just stop slave and then check the master
status, and when the master status syncs then i start the slave?
On Thu, Aug 26, 2010 at 3:09 PM, wrote:
> That is really bad, you will loose changes.
>
> You shouldnt have to do anything when rebooting either the master or slave.
> If the master is down, then the slave recieves no updates. If the slave is
> down, when it comes back up it checks the master log pos and plays thro any
> changes that are necessary to bring it up to date...
>
>
>
>
>
--
˙uʍop ǝpısdn p,uɹnʇ pןɹoʍ ǝɥʇ ǝǝs noʎ 'ʇuǝɯɐן sǝɯıʇ ǝɥʇ puɐ 'ʇuǝʇuoɔ
ǝq s,ʇǝן ʇǝʎ
%>>> "".join( [ {'*':'@','^':'.'}.get(c,None) or
chr(97+(ord(c)-83)%26) for c in ",adym,*)&uzq^zqf" ] )
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: master-slave replication sync problems.
True, But some times, this does not work and u need to know the master bin-log file and position to start, so that there is no loss of data. regards anandkl On Thu, Aug 26, 2010 at 6:39 PM, wrote: > That is really bad, you will loose changes. > > You shouldnt have to do anything when rebooting either the master or slave. > If the master is down, then the slave recieves no updates. If the slave is > down, when it comes back up it checks the master log pos and plays thro any > changes that are necessary to bring it up to date... > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com > >
Re: master-slave replication sync problems.
That is really bad, you will loose changes. You shouldnt have to do anything when rebooting either the master or slave. If the master is down, then the slave recieves no updates. If the slave is down, when it comes back up it checks the master log pos and plays thro any changes that are necessary to bring it up to date... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: master-slave replication sync problems.
since u r starting slave by postion beyond master, and if some of the
changes are already present on slave, it would error out with duplicate.
You need
show slave status\G;
slave stop;
show slave status\G;..wait for few min..than again
show slave status\G;just to make sure...slave is in complete sync with
master...
Here u will see the master log file and position.
U need to use this, next time u start ur slave to sync with master.
regards
anandkl
On Thu, Aug 26, 2010 at 6:34 PM, Norman Khine wrote:
> hello,
> i have a working master-slave replication, the problem i find is that
> if i restart the MASTER there is a difference in the MASTER_LOG_FILE
> and MASTER_LOG_POS on the SLAVE.
>
> what is the correct way to keep the two slaves in sync even after i
> restart the server. the way i do it now is to:
>
> [MASTER]
> mysql> show master status;
>
>
> +--+--+--+--+
> | mysql-bin.10 | 13405429 | upgrade,tracker,bugs |
> mysql,information_schema |
>
> +--+--+--+--+
> 1 row in set (0.00 sec)
>
> [SLAVE]
> mysql> stop slave;
> mysql> change master to MASTER_HOST='master.domain.com',
> MASTER_USER='replicator', MASTER_PASSWORD='xxx',
> MASTER_LOG_FILE='mysql-bin.10', MASTER_LOG_POS=13301215;
> mysql> start slave;
>
> is this correct or is there a better way to do this?
>
> thanks
>
> --
> ˙uʍop ǝpısdn p,uɹnʇ pןɹoʍ ǝɥʇ ǝǝs noʎ 'ʇuǝɯɐן sǝɯıʇ ǝɥʇ puɐ 'ʇuǝʇuoɔ
> ǝq s,ʇǝן ʇǝʎ
> %>>> "".join( [ {'*':'@','^':'.'}.get(c,None) or
> chr(97+(ord(c)-83)%26) for c in ",adym,*)&uzq^zqf" ] )
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com
>
>
master-slave replication sync problems.
hello,
i have a working master-slave replication, the problem i find is that
if i restart the MASTER there is a difference in the MASTER_LOG_FILE
and MASTER_LOG_POS on the SLAVE.
what is the correct way to keep the two slaves in sync even after i
restart the server. the way i do it now is to:
[MASTER]
mysql> show master status;
+--+--+--+--+
| mysql-bin.10 | 13405429 | upgrade,tracker,bugs |
mysql,information_schema |
+--+--+--+--+
1 row in set (0.00 sec)
[SLAVE]
mysql> stop slave;
mysql> change master to MASTER_HOST='master.domain.com',
MASTER_USER='replicator', MASTER_PASSWORD='xxx',
MASTER_LOG_FILE='mysql-bin.10', MASTER_LOG_POS=13301215;
mysql> start slave;
is this correct or is there a better way to do this?
thanks
--
˙uʍop ǝpısdn p,uɹnʇ pןɹoʍ ǝɥʇ ǝǝs noʎ 'ʇuǝɯɐן sǝɯıʇ ǝɥʇ puɐ 'ʇuǝʇuoɔ
ǝq s,ʇǝן ʇǝʎ
%>>> "".join( [ {'*':'@','^':'.'}.get(c,None) or
chr(97+(ord(c)-83)%26) for c in ",adym,*)&uzq^zqf" ] )
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysql Replication out of sync
Jose, Thank you very much...I found that fix a few minutes before I read your email...however I really appreciate your assistance. Just an FYI to anyone one else though...I had to skip a total of 2 queries (I executed SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; two times) because of the particular query I had problems with... Thanks again, Shain Jose Julian Buda wrote: maybe if you put on the slave server : mysql> STOP SLAVE; mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; mysql> START SLAVE; then just to check take a look at this lines : mysql> show slave status\G; ... Slave_IO_Running: Yes Slave_SQL_Running: Yes ... it will restart the replication, but you have to check then why this happened, just in case... - Original Message - From: Shain Miley To: mysql@lists.mysql.com Sent: Friday, January 09, 2009 3:17 PM Subject: Mysql Replication out of sync Hello all, I noticed that my two Mysql slave servers were running out of sync this morning. After looking into it...it appears that yesterday when I tried to change the username of a user on the master server...it caused some issues on the slaves. Here is what I was able to pull from the slave error logs: 090108 16:46:05 [ERROR] Slave SQL: Error 'Operation CREATE USER failed for 'root'@'127.0.0.1'' on query. Default database: 'mysql'. Query: 'CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY PASSWORD '*3313E27C7AD7F792A60D5D651029375E60'', Error_code: 1396 090108 16:46:05 [Warning] Slave: Operation CREATE USER failed for 'root'@'127.0.0.1' Error_code: 1396 090108 16:46:05 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'master-bin.08' position 970652826 Can anyone tell me if/how I can remove that statement from the log file...so I can restart the slave? Is there a better way to fix this? Thanks, Shain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=jb...@noticiasargentinas.com __ Información de NOD32, revisión 3749 (20090107) __ Este mensaje ha sido analizado con NOD32 antivirus system http://www.nod32.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysql Replication out of sync
maybe if you put on the slave server : mysql> STOP SLAVE; mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; mysql> START SLAVE; then just to check take a look at this lines : mysql> show slave status\G; ... Slave_IO_Running: Yes Slave_SQL_Running: Yes ... it will restart the replication, but you have to check then why this happened, just in case... - Original Message - From: Shain Miley To: mysql@lists.mysql.com Sent: Friday, January 09, 2009 3:17 PM Subject: Mysql Replication out of sync Hello all, I noticed that my two Mysql slave servers were running out of sync this morning. After looking into it...it appears that yesterday when I tried to change the username of a user on the master server...it caused some issues on the slaves. Here is what I was able to pull from the slave error logs: 090108 16:46:05 [ERROR] Slave SQL: Error 'Operation CREATE USER failed for 'root'@'127.0.0.1'' on query. Default database: 'mysql'. Query: 'CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY PASSWORD '*3313E27C7AD7F792A60D5D651029375E60'', Error_code: 1396 090108 16:46:05 [Warning] Slave: Operation CREATE USER failed for 'root'@'127.0.0.1' Error_code: 1396 090108 16:46:05 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'master-bin.08' position 970652826 Can anyone tell me if/how I can remove that statement from the log file...so I can restart the slave? Is there a better way to fix this? Thanks, Shain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=jb...@noticiasargentinas.com __ Información de NOD32, revisión 3749 (20090107) __ Este mensaje ha sido analizado con NOD32 antivirus system http://www.nod32.com
Mysql Replication out of sync
Hello all, I noticed that my two Mysql slave servers were running out of sync this morning. After looking into it...it appears that yesterday when I tried to change the username of a user on the master server...it caused some issues on the slaves. Here is what I was able to pull from the slave error logs: 090108 16:46:05 [ERROR] Slave SQL: Error 'Operation CREATE USER failed for 'root'@'127.0.0.1'' on query. Default database: 'mysql'. Query: 'CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY PASSWORD '*3313E27C7AD7F792A60D5D651029375E60'', Error_code: 1396 090108 16:46:05 [Warning] Slave: Operation CREATE USER failed for 'root'@'127.0.0.1' Error_code: 1396 090108 16:46:05 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'master-bin.08' position 970652826 Can anyone tell me if/how I can remove that statement from the log file...so I can restart the slave? Is there a better way to fix this? Thanks, Shain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: sync
yes, they should be identical , am thinking to use the howtoforge mysql_database_replication document. On Mon, Sep 1, 2008 at 4:58 PM, Ranjeet Walunj <[EMAIL PROTECTED]>wrote: > Mad Unix wrote: > >> I was wandering if anyone know a good way to sync 2mysql databases >> _one_way >> The mysql is alocated on a web php application >> > Do they (DB) need to be identical ? Have you checked replication ? > > Regards, > Ranjeet Walunj > > > -- Madunix_at_Gmail Sysadmin "Computers are useless. They can only give you answers" - Pablo Picasso "Never trust a computer you can't throw out a window." - Steve Wozniak
RE: sync
can you provide a testcase would justify 2 different DB schemas?If you're willing to stay with identical configurations take a look at http://dev.mysql.com/doc/refman/5.0/en/replication.htmlHTHMartin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. > Date: Mon, 1 Sep 2008 17:58:10 +0530> From: [EMAIL PROTECTED]> To: [EMAIL PROTECTED]> CC: mysql@lists.mysql.com> Subject: Re: sync> > Mad Unix wrote:> > I was wandering if anyone know a good way to sync 2mysql databases _one_way> > The mysql is alocated on a web php application> Do they (DB) need to be identical ? Have you checked replication ?> > Regards,> Ranjeet Walunj> > > > -- > MySQL General Mailing List> For list archives: http://lists.mysql.com/mysql> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]> _ Get thousands of games on your PC, your mobile phone, and the web with Windows®. http://clk.atdmt.com/MRT/go/108588800/direct/01/
Re: sync
Mad Unix wrote: I was wandering if anyone know a good way to sync 2mysql databases _one_way The mysql is alocated on a web php application Do they (DB) need to be identical ? Have you checked replication ? Regards, Ranjeet Walunj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sync
Hi, Have you tried mk-table-sync from the Maatkit toolset http://www.maatkit.org/tools.html Ewen On Mon, Sep 1, 2008 at 2:12 PM, Mad Unix <[EMAIL PROTECTED]> wrote: > I was wandering if anyone know a good way to sync 2mysql databases _one_way > The mysql is alocated on a web php application. > > Thanks > > > -- > Madunix_at_Gmail > Sysadmin > > "Computers are useless. They can only give you answers" - Pablo Picasso > "Never trust a computer you can't throw out a window." - Steve Wozniak > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sync
I was wandering if anyone know a good way to sync 2mysql databases _one_way The mysql is alocated on a web php application. Thanks -- Madunix_at_Gmail Sysadmin "Computers are useless. They can only give you answers" - Pablo Picasso "Never trust a computer you can't throw out a window." - Steve Wozniak
Re: Replication Falls Out Of Sync With LOAD DATA
On Nov 28, 2007 11:18 PM, B. Keith Murphy <[EMAIL PROTECTED]> wrote: > The reason I asked about version is that it looks like there is problem > replcating a load data infile command from some versions of 4.x to 5.x > slaves. Master and Slaves are 5.x. Hopefully I've figured out the issue. When the slave would fall out of sync, I would resync using rsync. In that rsync command I was using the --delete option which would delete the relay log from the slave machine. Since the relay log was being removed constantly, the replication was in a strange state. That problem is fixed so hopefully things should stay in sync. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Falls Out Of Sync With LOAD DATA
The reason I asked about version is that it looks like there is problem replcating a load data infile command from some versions of 4.x to 5.x slaves. Baron Schwartz wrote: What do you mean by "falls out of sync?" LOAD DATA INFILE hasn't been a problem for me, and I use it a LOT. It's so simple that I suspect something else. But then again, I don't know what you mean by "out of sync" :) On Nov 28, 2007 4:32 PM, B. Keith Murphy <[EMAIL PROTECTED]> wrote: What versions of MySQL are you using on both the master adn the slaves? Keith Michael Stearne wrote: We have replication set up for 1 master and 4 slaves. When resynced everything appears to work fine. Come back a couple hours later and the machines are out of sync. The only thing I can think of that could cause this is that we are inserting some data on the master (updates,inserts,deletes) using LOAD DATA INFILE. Does this cause a problem for replication? Thanks, Michael -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Falls Out Of Sync With LOAD DATA
What do you mean by "falls out of sync?" LOAD DATA INFILE hasn't been a problem for me, and I use it a LOT. It's so simple that I suspect something else. But then again, I don't know what you mean by "out of sync" :) On Nov 28, 2007 4:32 PM, B. Keith Murphy <[EMAIL PROTECTED]> wrote: > What versions of MySQL are you using on both the master adn the slaves? > > Keith > > Michael Stearne wrote: > > We have replication set up for 1 master and 4 slaves. When resynced > > everything appears to work fine. Come back a couple hours later and > > the machines are out of sync. The only thing I can think of that could > > cause this is that we are inserting some data on the master > > (updates,inserts,deletes) using LOAD DATA INFILE. Does this cause a > > problem for replication? > > > > Thanks, > > Michael > > > > > -- > Keith Murphy > > > editor: MySQL Magazine > http://www.mysqlzine.net > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Falls Out Of Sync With LOAD DATA
What versions of MySQL are you using on both the master adn the slaves? Keith Michael Stearne wrote: We have replication set up for 1 master and 4 slaves. When resynced everything appears to work fine. Come back a couple hours later and the machines are out of sync. The only thing I can think of that could cause this is that we are inserting some data on the master (updates,inserts,deletes) using LOAD DATA INFILE. Does this cause a problem for replication? Thanks, Michael -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication Falls Out Of Sync With LOAD DATA
We have replication set up for 1 master and 4 slaves. When resynced everything appears to work fine. Come back a couple hours later and the machines are out of sync. The only thing I can think of that could cause this is that we are inserting some data on the master (updates,inserts,deletes) using LOAD DATA INFILE. Does this cause a problem for replication? Thanks, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication vs. mysql-table-sync
Michael Stearne wrote: Is mysql-table-sync design to be used as a fix for when your replication is out of sync OR can it be used instead of replication? Thanks, Michael You need to use replication not mysql-table-sync for replication. mysql-table-sync is use to get it back in sync. keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication vs. mysql-table-sync
Is mysql-table-sync design to be used as a fix for when your replication is out of sync OR can it be used instead of replication? Thanks, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bounty on MySQL Table Sync features
Many of you have used MySQL Table Sync (or other tools from the MySQL Toolkit I maintain). It has become increasingly complicated and time-consuming to develop these tools, and I can't keep up with them in my spare time anymore. I am willing to take some time off work to fix bugs and add features, but not at my own expense. The tools are Free Software, so I'm passing the hat to help sponsor my time off work; this seems like a reasonable approach. It's how Vim's developer Bram Moolenaar sponsors Vim's development, for example. I'm sure everyone who uses the tools would appreciate your support. Details are at http://www.xaprb.com/blog/2007/10/31/mysql-table-sync-bounty-lets-do-it/ Thanks Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Oracle to Mysql Sync
I'd given some thought to this a while ago. The only way you are going to be able to tell if a row changes is to have a date column on every oracle table that indicates the last time the data changed. You'll need some program to start up that knows the last time it ran, and the current date, and look for any changed rows between those two dates/times. All dates/times need to come from the database (not the OS). After it finishes, the current date that the code generated needs to be saved somewhere for the next iteration. This won't work when rows are deleted from the database. If you are lucky, and this never happens, it's not an issue. If it only happens on a few tables, you can put triggers on that table (ON DELETE) to generate delete-statements to store in some log-type-table that your program can then execute on the mysql database (and then remove the rows from the log-type-table). If you have 500 tables that can have rows deleted, then you have a bit of work on your hands. This isn't an elegant or simple solution, but I don't know of any application or tool that can be used to watch Oracle tables and apply the changes to a MySQL table in real-time or near real time. On the flip side, because you will be batching updates/inserts/deletes that happen over a period of time (even if it's just 10-15 minutes) and apply them to MySQL all at once, you will be putting 10-15 minutes worth of Oracle processing onto MySQL all at once. Unfort, this isn't an apples-to-apples test. The Oracle statement might be, "update some_table set some_column = 'xyz' where some_other_column='abc' and some_third_column_id in (select some_value from some_other_table where some_column = 12) and the mysql statements would just be a bunch of updates keyed off the primary key. Oracle is doing way more work. If you need to do an oranges-to-oranges comparison, then unfortunately the only thing I can think of is to log statements in a centralized location (the Oracle database?) and then replay them in order on the MySQL database. Again, this could be quite a bit of work. The third option is to take the archived redo logs and extract the SQL from them, and replay that SQL (assuming your Oracle-SQL is ANSI-compliant and doesn't use (+) etc for outer joins, and isn't full of Oracle-specific functions, etc). This will only capture updates, inserts, deletes, however; any load on your database due to selects-statements won't be replayed on the MySQL cluster. Check out, http://download-east.oracle.com/docs/cd/A91202_01/901_doc/server.901/a90117/logminer.htm Note that you might have a tough time running this every 15 minutes. All that said, MySQL Cluster is definitely a different beast. No foreign keys, and potentially slow selects if the query needs to hit multiple clusters to retrieve data. You also need a lot of RAM (2.1 times your dataset, if I remember correctly), etc, etc. Some of this goes away in MySQL 5.1. There is a book coming out in a few weeks on the MySQL Cluster, and the MySQL Conference in Santa Clara has a set of talks devoted to MySQL Cluster. David Shain Miley wrote: Hello everyone, I had a quick question...I am looking to move away from our dependence on Oracle over to using a Mysql Cluster. Due to the complexity of the move it will have to happen over a period of time, what I would like to do is keep our mysql database in sync with our Oracle DBthis would allow us to prove that the MySQL db can deal with the load, provide the needed uptime,etc. Does anyone have any thoughts on this..I don't mean I need this done once..best case is every time something changes in an oracle table, the change will get send to Mysql...worst case...it needs to be out of sync by no more the 15 minutes...what do you thinkis it possible? Thanks in advance, SKM - Expecting? Get great news right away with email Auto-Check. Try the Yahoo! Mail Beta. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Oracle to Mysql Sync
Hello everyone, I had a quick question...I am looking to move away from our dependence on Oracle over to using a Mysql Cluster. Due to the complexity of the move it will have to happen over a period of time, what I would like to do is keep our mysql database in sync with our Oracle DBthis would allow us to prove that the MySQL db can deal with the load, provide the needed uptime,etc. Does anyone have any thoughts on this..I don't mean I need this done once..best case is every time something changes in an oracle table, the change will get send to Mysql...worst case...it needs to be out of sync by no more the 15 minutes...what do you thinkis it possible? Thanks in advance, SKM - Expecting? Get great news right away with email Auto-Check. Try the Yahoo! Mail Beta.
Script to perform unattended slave sync with master...
Has anyone built a script to add a new slave into a MySQL replication setup which can operate (for the most part) unattended? The set of operations is pretty straight forward but right now it's mostly a manual step which ends up taking a LONG time. The script would need to: * connect to a master or a slave * FLUSH TABLES WITH READ LOCK * record master replication position * take snapshot of myisam via mysqlhotcopy or simply CP the files into a temp directly * UNLOCK TABLES * SCP the files to the target slave * update replication positions on this box * setup correct permissions * startup replication * assert that the box is functioning correctly The transfer could be done unattended with SSH and ssh-agent. The CPU would be the bottleneck on gigabit ethernet but since it's unattended it shouldn't matter as much. One could even setup rsync with authentication if crypto was really the bottleneck. Thoughts? I don't want to have to write anything because I'm amazingly lazy ;) Kevin -- Founder/CEO Tailrank.com Location: San Francisco, CA AIM/YIM: sfburtonator Skype: burtonator Blog: feedblog.org Cell: 415-637-8078 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: anyone using subversion to sync mysql dbs ?
No, I don't generally go along with underhand political activity. :-) (but I expect that's an old joke - I haven't been MySQLing all that long, you see...) -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Any inaccuracies in this index may be explained by the fact that it has been sorted with the help of a computer. -- Donald Knuth -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: anyone using subversion to sync mysql dbs ?
yep, your're right thanks for clearing that up :) How do most mysql folks sync live and development databases ? As an alternative, I could use a PHP script on a late-night crontab. g On Sep 13, 2006, at 1:11 AM, Chris wrote: Graham Anderson wrote: Is anyone using subversion to sync live and development databases? If so, how? Is this desired or a best practice? Everything except my databases are under version control. In theory, I would like my databases to sync with the same subversion 'svn update' command. That way, all web content updates with one command. You mean schemas or the actual data? I wouldn't do that for data - if you create a "page" in your cms on your live system, you'd kill it off by doing an update. Doing a schema, sure, but data - no way. You'd need a script of some sort to handle database changes (eg adding/removing columns/indexes), no version control system will do this for you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: anyone using subversion to sync mysql dbs ?
For our Ruby on Rails app, during development, we're using SVN to store and sync a group of SQL load files, containing all the delete, insert and update commands necessary to fully populate the database. After doing an SVN update we then run a shell (or batch) script to pipe the SQL files into MySQL. You can use mysqldump to create your starting point load files, assuming you already have some content. It works well, provided you're willing to make updates to the SQL files only. Once we go live we plan to batch/script the mysqldump process as well to keep things up to date. Dan On 9/13/06, Chris <[EMAIL PROTECTED]> wrote: Graham Anderson wrote: > Is anyone using subversion to sync live and development databases? > > If so, how? > Is this desired or a best practice? > > Everything except my databases are under version control. > In theory, I would like my databases to sync with the same subversion > 'svn update' command. > That way, all web content updates with one command. You mean schemas or the actual data? I wouldn't do that for data - if you create a "page" in your cms on your live system, you'd kill it off by doing an update. Doing a schema, sure, but data - no way. You'd need a script of some sort to handle database changes (eg adding/removing columns/indexes), no version control system will do this for you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: anyone using subversion to sync mysql dbs ?
Graham Anderson wrote: Is anyone using subversion to sync live and development databases? If so, how? Is this desired or a best practice? Everything except my databases are under version control. In theory, I would like my databases to sync with the same subversion 'svn update' command. That way, all web content updates with one command. You mean schemas or the actual data? I wouldn't do that for data - if you create a "page" in your cms on your live system, you'd kill it off by doing an update. Doing a schema, sure, but data - no way. You'd need a script of some sort to handle database changes (eg adding/removing columns/indexes), no version control system will do this for you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
anyone using subversion to sync mysql dbs ?
Is anyone using subversion to sync live and development databases? If so, how? Is this desired or a best practice? Everything except my databases are under version control. In theory, I would like my databases to sync with the same subversion 'svn update' command. That way, all web content updates with one command. many thanks g -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data sync offline
Hello. Think about replication among other solutions. See: http://dev.mysql.com/doc/mysql/en/replication.html Alex Greg <[EMAIL PROTECTED]> wrote: > Hi, > > > Our company is considering migrating some tablesfrom MyISAM to InnoDB, > as it has row-level locking and other improvements over MyISAM. > > However, one of the things we do at the moment is rsync the MySQL data > directory to our development server every night over an 2Mbps ADSL > connection (as we have 40GB of data, downloading it all every night > isn't a viable option). > > I am aware that you can't copy the InnoDB binaries around to different > installations. Does anyone have any suggestions as to how we could > continue to do incremental downloads of this data over a slow > connection? > > > Regards, > > > -- Alex > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data sync offline
http://www.sitepoint.com/article/mysql-data-sqlyog-job-agent SJA is available for both Linux and Windows. Karam --- Alex Greg <[EMAIL PROTECTED]> wrote: > Hi, > > > Our company is considering migrating some tablesfrom > MyISAM to InnoDB, > as it has row-level locking and other improvements > over MyISAM. > > However, one of the things we do at the moment is > rsync the MySQL data > directory to our development server every night over > an 2Mbps ADSL > connection (as we have 40GB of data, downloading it > all every night > isn't a viable option). > > I am aware that you can't copy the InnoDB binaries > around to different > installations. Does anyone have any suggestions as > to how we could > continue to do incremental downloads of this data > over a slow > connection? > > > Regards, > > > -- Alex > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data sync offline
On Wed, 24 Aug 2005, Arno Coetzee wrote: Alex Greg wrote: Our company is considering migrating some tablesfrom MyISAM to InnoDB, as it has row-level locking and other improvements over MyISAM. However, one of the things we do at the moment is rsync the MySQL data directory to our development server every night over an 2Mbps ADSL connection (as we have 40GB of data, downloading it all every night isn't a viable option). I am aware that you can't copy the InnoDB binaries around to different installations. Does anyone have any suggestions as to how we could continue to do incremental downloads of this data over a slow connection? make a dump file of only the previous days data compress it , transfer the file , decompress it and import that days records into the other database. remember that you should have a complete copy of the the database on the other server before you start the daily process. http://dev.mysql.com/doc/mysql/en/backup.html you can rsync the bin logs. our server flushes 4 times a day for the backup script. -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - - - Jason Pyeron PD Inc. http://www.pdinc.us - - Partner & Sr. Manager 7 West 24th Street #100 - - +1 (443) 921-0381 Baltimore, Maryland 21218 - - - -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, purge the message from your system and notify the sender immediately. Any other use of the email by you is prohibited. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data sync offline
Alex Greg wrote: Hi, Our company is considering migrating some tablesfrom MyISAM to InnoDB, as it has row-level locking and other improvements over MyISAM. However, one of the things we do at the moment is rsync the MySQL data directory to our development server every night over an 2Mbps ADSL connection (as we have 40GB of data, downloading it all every night isn't a viable option). I am aware that you can't copy the InnoDB binaries around to different installations. Does anyone have any suggestions as to how we could continue to do incremental downloads of this data over a slow connection? Regards, -- Alex make a dump file of only the previous days data compress it , transfer the file , decompress it and import that days records into the other database. remember that you should have a complete copy of the the database on the other server before you start the daily process. -- Arno Coetzee Developer Flash Media Group Office : 2712 342 7595 Mobile : 2782 693 6180 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Data sync offline
Hi, Our company is considering migrating some tablesfrom MyISAM to InnoDB, as it has row-level locking and other improvements over MyISAM. However, one of the things we do at the moment is rsync the MySQL data directory to our development server every night over an 2Mbps ADSL connection (as we have 40GB of data, downloading it all every night isn't a viable option). I am aware that you can't copy the InnoDB binaries around to different installations. Does anyone have any suggestions as to how we could continue to do incremental downloads of this data over a slow connection? Regards, -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sync 2 live MySQL Databases
This is precisly the problem that I'm sitting with. It look like unless MySQL AB desides to build such a feature into one of the future releases this would beable to work. Mnr Cecil J.C. Brand Computer Services/ RekenaarDienste University of the Free State/ Universiteit van die Vrystaat PO Box 339 Bloemfontein 9300 South Africa Tel :401 2940 Email :[EMAIL PROTECTED] >>> Simon Garner <[EMAIL PROTECTED]> 5/26/2005 8:56:14 AM >>> On 26/05/2005 6:40 p.m., Cecil Brand wrote: > Hi, > > I was wandering if anyone know of a stable and reliabile way to sync 2 > live mysql databases, both ways. > I know I can use a master and slave, and yes have setup a few without > any problem, but as all > of us know this is just a one way downstream sync. I need to sync both > ways and basicly live, the every > 5 or 10 min cronjob sync with a perl/php/c++ app just won't cut it. > Unfortunately, the simple answer is you can't. While it is possible to set up multi-master replication with MySQL, this is not useful for the vast majority of applications. If you are sending write queries to both masters then you have the possibility of the two servers becoming out of sync. This is particularly true with auto_increment primary keys - if you have a table and you send one insert statement to each server, both at the same time, then they might both assign the same auto_increment number to each row, and then they will both be forced to ignore the replicated inserts they receive from each other - screwing your table. Your options are to use MySQL Cluster (not an option for a lot of people as your entire database has to be stored in RAM), or if you're just after high availability, set up one-way replication with dynamic master failover (which I am currently trying to figure out how to do myself). -Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ University of the Free State: This message and its contents are subject to a disclaimer. Please refer to http://www.uovs.ac.za/disclaimer for full details. Universiteit van die Vrystaat: Hierdie boodskap en sy inhoud is aan 'n vrywaringsklousule onderhewig. Volledige besonderhede is by http://www.uovs.ac.za/vrywaring beskikbaar. _ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sync 2 live MySQL Databases
On 26/05/2005 6:40 p.m., Cecil Brand wrote: Hi, I was wandering if anyone know of a stable and reliabile way to sync 2 live mysql databases, both ways. I know I can use a master and slave, and yes have setup a few without any problem, but as all of us know this is just a one way downstream sync. I need to sync both ways and basicly live, the every 5 or 10 min cronjob sync with a perl/php/c++ app just won't cut it. Unfortunately, the simple answer is you can't. While it is possible to set up multi-master replication with MySQL, this is not useful for the vast majority of applications. If you are sending write queries to both masters then you have the possibility of the two servers becoming out of sync. This is particularly true with auto_increment primary keys - if you have a table and you send one insert statement to each server, both at the same time, then they might both assign the same auto_increment number to each row, and then they will both be forced to ignore the replicated inserts they receive from each other - screwing your table. Your options are to use MySQL Cluster (not an option for a lot of people as your entire database has to be stored in RAM), or if you're just after high availability, set up one-way replication with dynamic master failover (which I am currently trying to figure out how to do myself). -Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sync 2 live MySQL Databases
Hi, I was wandering if anyone know of a stable and reliabile way to sync 2 live mysql databases, both ways. I know I can use a master and slave, and yes have setup a few without any problem, but as all of us know this is just a one way downstream sync. I need to sync both ways and basicly live, the every 5 or 10 min cronjob sync with a perl/php/c++ app just won't cut it. Any ideas, sugestion would be welcome. Thanks Cecil Mnr Cecil J.C. Brand Computer Services/ RekenaarDienste University of the Free State/ Universiteit van die Vrystaat PO Box 339 Bloemfontein 9300 South Africa Tel :401 2940 Email :[EMAIL PROTECTED] _ University of the Free State: This message and its contents are subject to a disclaimer. Please refer to http://www.uovs.ac.za/disclaimer for full details. Universiteit van die Vrystaat: Hierdie boodskap en sy inhoud is aan 'n vrywaringsklousule onderhewig. Volledige besonderhede is by http://www.uovs.ac.za/vrywaring beskikbaar. _ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slave out of sync
To answer the original poster of this thread, it would help if you could post more details about what happened such as error messages and mysqld versions. My hunch is that the binlog got corrupted and the slave choked on it. Did you try issuing 'SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1' (omit GLOBAL if your mysqld version is <4.0)? If the latter didn't help, you should be fine starting slaving again with 'CHANGE MASTER TO ...' and specifying the binlog and position manually, which is what you may have ended up doing. When the master daemon restarts (or crashes) the binlog automatically gets rotated (IE a new binlog is started). The first position in a new binlog is '4'. As for the other posters in this thread with frequent replication problems, I would be concerned if that's the case. I have found that it is very rare for replication to break just by itself, most of the time it's a misconfiguration or users sending updates directly to the slave. We have replication setups that generate up to 4gigs of binlog data daily, and none of them have ever broken just by themselves. It's always been a misconfiguration, IE user error. Hope this helps! Atle - Flying Crocodile Inc, Unix Systems Administrator -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slave out of sync
Jeff Smelser wrote: On Friday 08 April 2005 10:38 am, Chris Scheller wrote: i have replication going between 2 boxes. the master crashed a few days ago, and this morning i noticed that the slaves slave thread errored out. the binary log and offset had changed on the master and the slave couldn't sync up. i got the slave up and running again by changing the log file and offset in the slave. but now i am left with out of sync data. how do i get these back in sync, and how do i aviod this in the future? Why wouldn't they sync up? If you left off at master-005, pos 123 (lets say). It should pick up right there.. If your missing bin log files, then you need to set up the master to not delete logs the slave has not processed yet. This would assure your up to date, once the slave comes back alive.. Or am I missing something? this is what i thought as well. the error i got was for the position offset not matching up. when i looked at the master it was at file bin.003 and offset of something like 123,456. the slave was still at file bin.001 and offset 654,321. i don't delete the bin log files. i thought the slave would just catch up or am i doing something wrong here? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: slave out of sync
Often times when my replication breaks, it is a quasi monumental task to re-sync. Luckily, my replication is occurring on a data warehouse, where the tables represent each days worth of data. If replication fails, I can usually, get replication back on track by fixing whatever occurs in the data/.err file. If the files get out of sync, I have to abandon replication to get me back synced and reset the master "reset master", remove the master and relay files on the slave. Usually I will do a quick snapshot on the master and reset replication on the master, transfer files to the slave, and start slave replication, almost like doing a clean setup. Since I am out of sync by usually at most 24 hours, I only have to transfer a couple of tables to get back into sync. I guess the moral to this long winded story, is be very careful how you design your tables so recovering from replication failure is easier. David Norman Wells Fargo Services -Original Message- From: Jeff Smelser [mailto:[EMAIL PROTECTED] Sent: Thursday, April 14, 2005 1:16 PM To: mysql@lists.mysql.com Subject: Re: slave out of sync On Friday 08 April 2005 10:38 am, Chris Scheller wrote: > i have replication going between 2 boxes. the master crashed a few > days ago, and this morning i noticed that the slaves slave thread > errored out. the binary log and offset had changed on the master and > the slave couldn't sync up. i got the slave up and running again by > changing the log file and offset in the slave. but now i am left with > out of sync data. how do i get these back in sync, and how do i aviod > this in the future? Why wouldn't they sync up? If you left off at master-005, pos 123 (lets say). It should pick up right there.. If your missing bin log files, then you need to set up the master to not delete logs the slave has not processed yet. This would assure your up to date, once the slave comes back alive.. Or am I missing something? Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slave out of sync
On Friday 08 April 2005 10:38 am, Chris Scheller wrote: > i have replication going between 2 boxes. the master crashed a few days > ago, and this morning i noticed that the slaves slave thread errored > out. the binary log and offset had changed on the master and the slave > couldn't sync up. i got the slave up and running again by changing the > log file and offset in the slave. but now i am left with out of sync > data. how do i get these back in sync, and how do i aviod this in the > future? Why wouldn't they sync up? If you left off at master-005, pos 123 (lets say). It should pick up right there.. If your missing bin log files, then you need to set up the master to not delete logs the slave has not processed yet. This would assure your up to date, once the slave comes back alive.. Or am I missing something? Jeff pgpsJU5d3COjX.pgp Description: PGP signature
Re: slave out of sync
Hello. Replication shouldn't die if one of the participants goes offline. But unclean shutdowns of the master may cause inconsistencies between the content of tables and the binary log in master; this can be avoided by using InnoDB tables and the --innodb-safe-binlog option on the master. See Replication for part of the manual for more advices and switch to the latest release if you haven't it yet. > > think this is what i am going to have to do as i found more errors this > morning with the slave thread dead. now that i know how to setup > repilcation it won't be as painful this go around. my only remaining > problem with this is i thought that this shouldn't have happened in the > first place. am i going to face this problem any time the master goes > offline or for that matter the slave goes offline? > > > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slave out of sync
Gleb Paharenko wrote: > But if you had an updates and deletes after crashing the master your task became difficult. But the easiest way, as for me, > > is to clear the slave and setup replication again. You may use think this is what i am going to have to do as i found more errors this morning with the slave thread dead. now that i know how to setup repilcation it won't be as painful this go around. my only remaining problem with this is i thought that this shouldn't have happened in the first place. am i going to face this problem any time the master goes offline or for that matter the slave goes offline? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slave out of sync
Hello. I think that it is possible using mysqlbinlog with options --stop-position and --start-position to get missed data. But if you had an updates and deletes after crashing the master your task became difficult. But the easiest way, as for me, is to clear the slave and setup replication again. You may use LOAD DATA FROM MASTER to make your task as simple as possible. In feature I suggest you to make a regular snapshots of your master data. MySQL has ability for incremental backups, which could save you a lot of space. Chris Scheller <[EMAIL PROTECTED]> wrote: > i have replication going between 2 boxes. the master crashed a few days > ago, and this morning i noticed that the slaves slave thread errored > out. the binary log and offset had changed on the master and the slave > couldn't sync up. i got the slave up and running again by changing the > log file and offset in the slave. but now i am left with out of sync > data. how do i get these back in sync, and how do i aviod this in the > future? > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
slave out of sync
i have replication going between 2 boxes. the master crashed a few days ago, and this morning i noticed that the slaves slave thread errored out. the binary log and offset had changed on the master and the slave couldn't sync up. i got the slave up and running again by changing the log file and offset in the slave. but now i am left with out of sync data. how do i get these back in sync, and how do i aviod this in the future? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
bdb-no-sync
What are the implications of setting the "--bdb-no-sync" flag when using the BDB storage engine. I haven't found alot of documentation on this in the docs or via google. Does this mean that after every transaction, sync will not be called? When will sync be called then? Will setting this flag lead to "ACI" semantics, ie no durability? If so, when will transactions become durable? Will setting this flag increase the likelyhood of DB corruption? As happens in Postgresql with fsync off. I know in BDB (non MySQL storage engine), setting "DB_TXN_NOSYNC" on a BDB transaction leads to ACI semantics. It is then up to the app to call DB->sync when it wants durability. How does this map to the BDB/MSQL storage engine? Thanks for any help, Jaco -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
bdb-no-sync
What are the implications of setting the "--bdb-no-sync" flag when using the BDB storage engine. There isn't alot of documentation on this. Does this mean that after every transaction, sync will not be called? When will sync be called then? Will setting this flag lead to "ACI" semantics, ie no durability? If so, when will transactions become durable? Will setting this flag increase the likelyhood of DB corruption? As happens in Postgresql with fsync off. I know in BDB (non MySQL storage engine), setting "DB_TXN_NOSYNC" on a BDB transaction leads to ACI semantics. It is then up to the app to call DB->sync when it wants durability. How does this map to the BDB/MSQL storage engine? Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sync db
It's safe to implement two-way replication (properly called multimaster replication) in MySQL if each master is guaranteed to only update a unique subset of that data (ie data that no other master database changes). Each master database would be able to safely read all the data, however. For example, say you have a database that exists on two servers, with replicated data consisting of one table and four records, which I'll call A, B, C and D. If server 1 only ever updates records A and B, and server 2 only ever updates C and D, then there is no issue with multimaster replication. The issue is conflict resolution. In the example above, say record A was updated on server 1 at the same time record A was updated on server 2. Which change is correct? What if the conflict was not so trivial, but involved records on multiple tables with auto-incremented primary keys and foreign key constraints to other records inserted or updated at the same time? You could end up with a real mess. Some other database vendors do offer multimaster replication (Oracle being the big one), but I've heard that even their conflict resolution algorithms are not perfect. In your case, you need to ensure that the same record is not being updated (or deleted) on one database at the same time it is being accessed on another database. You have to figure out how to do that yourself, and implement it in whatever application is talking to the database(s). MySQL can't do it for you, at least not right now. Have you considered having read-only databases at the remote locations, and a single master that people write to which in turn sends changes to the 4 read-only databases? David Jim Grill wrote: Two way replication is possible. However, it does not work like you think it would. It would not be safe to modify data on both databases at the same time. See the FAQ on replication: http://dev.mysql.com/doc/mysql/en/Replication_FAQ.html There is some information regarding two-way replication that will shed some light on the subject. Jim Grill yeah, I seen that have. Have you tried that before? I need to do it two-way. But have not seen any special setup for that. Each site will be entering data and all sites will need to see the updated change. On Fri, 01 Oct 2004 10:38:07 -0400, Michael Stassen <[EMAIL PROTECTED]> wrote: Have you considered replication <http://dev.mysql.com/doc/mysql/en/Replication.html>? Michael spiv007 wrote: I want to know what to best way to keep a 4 mysql servers sync. I have 4 remote locations, I am thinking about putting a mysql server in each location and every hour have the 1 db that I need to sync to sync together. Is there away to do its in somewhat real time or even a delay maybe of an hour or two? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sync db
Two way replication is possible. However, it does not work like you think it would. It would not be safe to modify data on both databases at the same time. See the FAQ on replication: http://dev.mysql.com/doc/mysql/en/Replication_FAQ.html There is some information regarding two-way replication that will shed some light on the subject. Jim Grill > yeah, I seen that have. Have you tried that before? I need to do it > two-way. But have not seen any special setup for that. Each site > will be entering data and all sites will need to see the updated > change. > > > On Fri, 01 Oct 2004 10:38:07 -0400, Michael Stassen > <[EMAIL PROTECTED]> wrote: > > Have you considered replication > > <http://dev.mysql.com/doc/mysql/en/Replication.html>? > > > > Michael > > > > > > > > spiv007 wrote: > > > > > I want to know what to best way to keep a 4 mysql servers sync. > > > > > > I have 4 remote locations, I am thinking about putting a mysql server > > > in each location and every hour have the 1 db that I need to sync to > > > sync together. > > > > > > Is there away to do its in somewhat real time or even a delay maybe of > > > an hour or two? > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sync db
yeah, I seen that have. Have you tried that before? I need to do it two-way. But have not seen any special setup for that. Each site will be entering data and all sites will need to see the updated change. On Fri, 01 Oct 2004 10:38:07 -0400, Michael Stassen <[EMAIL PROTECTED]> wrote: > Have you considered replication > <http://dev.mysql.com/doc/mysql/en/Replication.html>? > > Michael > > > > spiv007 wrote: > > > I want to know what to best way to keep a 4 mysql servers sync. > > > > I have 4 remote locations, I am thinking about putting a mysql server > > in each location and every hour have the 1 db that I need to sync to > > sync together. > > > > Is there away to do its in somewhat real time or even a delay maybe of > > an hour or two? > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sync db
Have you considered replication <http://dev.mysql.com/doc/mysql/en/Replication.html>? Michael spiv007 wrote: I want to know what to best way to keep a 4 mysql servers sync. I have 4 remote locations, I am thinking about putting a mysql server in each location and every hour have the 1 db that I need to sync to sync together. Is there away to do its in somewhat real time or even a delay maybe of an hour or two? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sync db
I want to know what to best way to keep a 4 mysql servers sync. I have 4 remote locations, I am thinking about putting a mysql server in each location and every hour have the 1 db that I need to sync to sync together. Is there away to do its in somewhat real time or even a delay maybe of an hour or two? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Commands out of sync
At 4:26 PM +0200 9/16/03, VIVES Ariel wrote: Hello the list, sorry for my english, i'm french :-p i've got a php script with a mysql database. everything works fine. but today, i've got this message : Commands out of sync; You can't run this command now i've done NOTHING. nothing on my php scripts. nothing on the database server. Did someone update PHP? why ??? help please. thanks. -- Ariel VIVES Mail : [EMAIL PROTECTED] URL : http://www.arpadev.com -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Commands out of sync
On 16 Sep 2003, VIVES Ariel wrote: > i've got a php script with a mysql database. > everything works fine. > but today, i've got this message : > > Commands out of sync; You can't run this command now > > i've done NOTHING. > nothing on my php scripts. > nothing on the database server. > > why ??? > > help please. This may or may not help, but I have seen this problem when versions of mysql get mixed. mysql.h and the .a files. Check this. Good Luck, -- Boyd Gerber, Software Engineer & Support Specialist MySQL AB, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Commands out of sync
Hello the list, sorry for my english, i'm french :-p i've got a php script with a mysql database. everything works fine. but today, i've got this message : Commands out of sync; You can't run this command now i've done NOTHING. nothing on my php scripts. nothing on the database server. why ??? help please. thanks. -- Ariel VIVES Mail : [EMAIL PROTECTED] URL : http://www.arpadev.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
'Commands out of sync' when moving app from Linux to MacOS X
I have a complex web application consisting of several programs totalling about 100k lines of C code that has been running successfully under Linux. To improve security, I want to port the application to MacOS X, where considerably less effort goes into designing security breaches. When I try compiling my programs on MacOS X, they compile fine, but in many cases I am getting a "Commands out of Sync" error when previously I had no errors. They seem to fail consistently in the same place, but I'm having a really tough time telling why they're failing. The problem seems to happen in the following circumstances: * I have nested queries - that is, I run one query in a loop, and have the results from that query drive another. For instance, select id from menu where title = 'test' and then select id, title from submenu where menu_id = [menu ID from first query] * It appears to be linked to a function I have where I use mysql_fetch_fields or mysql_fetch_field to get all the field names in a query. At first I thought there might be some kind of cleanup required for those functions, but I could not find it. What I did notice was that switching from mysql_fetch_fields to mysql_fetch_field was able to solve the problem in some, but not all, cases. * My SQL query function that I use for all queries returning values uses mysql_store_result. I never use the use_result function, so that can be ruled out as a reason for the problem. I was wondering in what way this type of thing would work differently in MacOS X versus Linux, and what sorts of solutions I should look to to trouble shoot the problem. I have written mySQL C programs in MacOS X before, and none of them have given me this kind of difficulty. In fact, I normally have no trouble moving the programs between Linux, MacOS X and Irix ... except now. I have used the same basic functions in other projects and they have worked fine on all platforms, so I'm baffled as to what I might have done wrong here. One other thing that changed is that I'm accessing the mySQL database server remotely from the MacOS X box, while leaving the actual database on the Linux box, which for now is still running the working copy of the web application. Would that make any difference? Can anyone point me in the right direction or suggest some ways to approach the problem? Many thanks. D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to Track error - Commands Out of Sync
So take a look here : http://bugs.php.net/bug.php?id=19529 and upgrade your PHP version, since it's fixed in PHP 4.3.x ;) Jocelyn - Original Message - From: "Free Grafton" <[EMAIL PROTECTED]> To: "Jocelyn Fournier" <[EMAIL PROTECTED]> Sent: Tuesday, August 26, 2003 10:23 PM Subject: Re: How to Track error - Commands Out of Sync > Jocelyn, > We use PHP for our application. We are running version 4.2.3. Thanks for the > help. > > > On 8/26/03 2:16 PM, "Jocelyn Fournier" <[EMAIL PROTECTED]> wrote: > > > Hi, > > > > Do you use PHP ? If so, which version is installed ? > > > > Regards, > > Jocelyn > > - Original Message - > > From: "Free Grafton" <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]> > > Sent: Tuesday, August 26, 2003 10:05 PM > > Subject: How to Track error - Commands Out of Sync > > > > > >> We are currently running about 50 databases on MySQL 4.0.14. Occasionally > >> our users will get the error: > >> > >> Couldn't select database. Message: Commands out of sync; You can't run > > this > >> command now Message Number: 2014 > >> > >> When this happens to one customer, it affects them all. We end up having > > to > >> restart the MySQL services to clean things up. Our goal is to eliminate > >> whatever is causing this, but cannot determine the cause. > >> > >> I have checked the online help on MySQL and we are not calling the > > functions > >> normally associated with the error. > >> > >> Any thoughts would be greatly appreciated. > >> > >> Free Grafton > >> > >> > >> > >> -- > >> MySQL General Mailing List > >> For list archives: http://lists.mysql.com/mysql > >> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > >> > >> > > > > > > > Free Grafton > > Church Community Builder, Inc. > http://www.churchcommunitybuilder.com > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to Track error - Commands Out of Sync
Hi, Do you use PHP ? If so, which version is installed ? Regards, Jocelyn - Original Message - From: "Free Grafton" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, August 26, 2003 10:05 PM Subject: How to Track error - Commands Out of Sync > We are currently running about 50 databases on MySQL 4.0.14. Occasionally > our users will get the error: > > Couldn't select database. Message: Commands out of sync; You can't run this > command now Message Number: 2014 > > When this happens to one customer, it affects them all. We end up having to > restart the MySQL services to clean things up. Our goal is to eliminate > whatever is causing this, but cannot determine the cause. > > I have checked the online help on MySQL and we are not calling the functions > normally associated with the error. > > Any thoughts would be greatly appreciated. > > Free Grafton > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to Track error - Commands Out of Sync
We are currently running about 50 databases on MySQL 4.0.14. Occasionally our users will get the error: Couldn't select database. Message: Commands out of sync; You can't run this command now Message Number: 2014 When this happens to one customer, it affects them all. We end up having to restart the MySQL services to clean things up. Our goal is to eliminate whatever is causing this, but cannot determine the cause. I have checked the online help on MySQL and we are not calling the functions normally associated with the error. Any thoughts would be greatly appreciated. Free Grafton -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
high availability sync of 3 hosts
Hello, I am trying to find a solution for the following scenario. The topic is replication/high-availability/redundancy and sync. I have three computers, A, B and C. "A" and "B" are operating important hardware (astronomical telescopes TEL_A and TEL_B), and the operation parameters as well as logs of operations are stored in a MySQL database. I prefer having a central database (DB_C) for this purpose, in fact located on "C", from which I can overview parameters and logs of "A" and "B" simultaneously. During normal operation, "A" and "B" reads/writes only DB_C: C -> DB_C | | ---><--- ><- | | A -> TEL_A B -> TEL_B (|) (|) (|) (|) DB_A DB_B However, if for some reason the central "C" host goes down, or internet connection is broken, I would like to achieve the following: "A" and "B" to continues standalone operation (read-write) with the freshest possible local DB: C -> DB_C | | X X X X X X X X X X X | | A -> TELESCOPE_AB -> TEL_B | | DB_A DB_B I guess this has to be done with continuous slave-master replication between DB_A -><- DB_C and DB_B -><- DB_C while the internet connection is alive. When the internet connection is resumed, or the central DB is available again, I would like [to realize that DB_C is available again, and] DB_C to be syncronized with the changes made in DB_A and DB_B (the changes, of course, are different in the two replicas). In other words, we have a synced system of A, B and C, and we have the possibility of A and B slowly diverging from C temporarily. Then we'd like to sync up the system again. What would you recommend? Maybe there is a problem with my concept. All the best, Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
(2014) Commands out of sync; You can't run this command now
MySql, Hope you can help. I'm suddenly, after updgrading to MySQL 4.0.13-max-log Linux, getting the error: (2014) Commands out of sync; You can't run this command now On the second command I send to MySQL. First, no problem. I'm using MyC libruary, which has worked before. Various pages on the Internet surgest I use a mysql_connect() instead of mysql_pconnect() to fix this. However, MyC seems to use: mysql_real_connect (&mysql, host, name, pass, db, 0, NULL, 0) To which I cannot find any logical fix. There are lots of quotes of this error in, say, Google, but no reason or fixes I can find. I can disconnect and reconnect for every query, which does work, but is not a great idea. Can any member surgest anything I might be able to use? Thanks, ben. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
@hi, need sync mysql users password with LDAP3.x
Hi, I very need sync password for same mysql users with LDAP (or *nix passwd file). Any idea? -- Miroslav Ris System Administrator iNet System iNet WebMail System - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
