Re: innodb_lock_wait_timeout
On 11.10.2012 14:43, Johan De Meersman wrote: - Original Message - From: Markus Falb markus.f...@fasel.at But why is retrying better than raising the value? So what is better, adjusting the timeout or retrying application side and why? Well, raising the timeout would probably help, but may cause more concurrent connections to stay open until they block the server. The idea of retrying is that everything you've done before that particular transaction has already been committed, so you (probably) can't easily undo it. A retry gives you a chance to still finish what you were doing with no other loss than some time waiting. Regardless of how many retries fail, you will still be in the same position as you were when the first attempt failed. With a low timeout the connection will be terminated sooner, but if the application retries another connection is taken. I could have raised the timeout with the same effect on the db side (1 process is waiting) but maybe more performant (no new connection necessary) and with simpler logic on the application side (no retry logic) Maybe you imply that there is some kind of sleep before the retry, so that other statements could be fulfilled? I still don't get it. -- Kind Regards, Markus Falb signature.asc Description: OpenPGP digital signature
Re: innodb_lock_wait_timeout
Am 12.10.2012 15:39, schrieb Markus Falb:
With a low timeout the connection will be terminated sooner, but if the
application retries another connection is taken. I could have raised the
timeout with the same effect on the db side (1 process is waiting) but
maybe more performant (no new connection necessary) and with simpler
logic on the application side (no retry logic)
Maybe you imply that there is some kind of sleep before the retry, so
that other statements could be fulfilled?
I still don't get it
usually if you implement a db-layer with reconnect on error
you will also make a sleep before re-connect
below the relevant snippet of my since years used mysql-layer
this is from the connect-method, the query()-method itself
does disconect/connect on recoverable errors and try the same
query again after a succesfull re-connect
the intention here was to allow restart mysqld at every time
without breaking webserver-requests, usually you do not recognize
the short lag, and yes - this sort of error-handling relaxes locks
$rw = @mysqli_real_connect($this-conn, $this-host, $this-user,
$this-pwd, $this-db, $this-port, '', $flags);
if(!$rw)
{
for($retry=1; $retry=240; $retry++)
{
$this-conn = @mysqli_init();
if($this-ssl)
{
if($this-ssl_crt === '')
{
$this-ssl_crt = 'dummy.crt';
}
/** SSL aktivieren */
$this-conn-ssl_set($this-ssl_key, $this-ssl_crt, $this-ssl_ca,
NULL, NULL);
}
$rw = @mysqli_real_connect($this-conn, $this-host, $this-user,
$this-pwd, $this-db, $this-port, '',
$flags);
if($rw)
{
$this-conn = @mysqli_init();
if($this-ssl)
{
if($this-ssl_crt === '')
{
$this-ssl_crt = 'dummy.crt';
}
$this-conn-ssl_set($this-ssl_key, $this-ssl_crt, $this-ssl_ca,
NULL, NULL);
}
$rw = @mysqli_real_connect($this-conn, $this-host, $this-user,
$this-pwd, $this-db, $this-port, '',
$flags);
break;
}
usleep(62500);
}
if(!$rw)
{
$this-conn = 0;
$this-error(mysqli_connect_error());
}
}
signature.asc
Description: OpenPGP digital signature
innodb_lock_wait_timeout and replication
I encountered an error MySQL Error: Lock wait timeout exceeded; try restarting transaction MySQL Error No: 1205 For this very statement an entry in the binlog was filed ... # Query_time: 52 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 ... Why is there an entry in the binlog if the statement failed? If it is in the binlog it will be replicated to a slave and on the slave it will succeed (maybe). Does this mean that a Lock wait timeout exceeded potentially drive the slave out of sync? -- Kind Regards, Markus Falb signature.asc Description: OpenPGP digital signature
Re: innodb_lock_wait_timeout
In my implementation we found no need to establish a new connection after a
lock timeout but just retried on the existing connection. We did instigate
a sleep timeout of 10 ms which theoretically increased on each iteration
but we never had to try a third time even under very heavy load.
On 2012-10-12 10:02 AM, Reindl Harald h.rei...@thelounge.net wrote:
Am 12.10.2012 15:39, schrieb Markus Falb:
With a low timeout the connection will be terminated sooner, but if the
application retries anot...
usually if you implement a db-layer with reconnect on error
you will also make a sleep before re-connect
below the relevant snippet of my since years used mysql-layer
this is from the connect-method, the query()-method itself
does disconect/connect on recoverable errors and try the same
query again after a succesfull re-connect
the intention here was to allow restart mysqld at every time
without breaking webserver-requests, usually you do not recognize
the short lag, and yes - this sort of error-handling relaxes locks
$rw = @mysqli_real_connect($this-conn, $this-host, $this-user,
$this-pwd, $this-db, $this-port, '', $flags);
if(!$rw)
{
for($retry=1; $retry=240; $retry++)
{
$this-conn = @mysqli_init();
if($this-ssl)
{
if($this-ssl_crt === '')
{
$this-ssl_crt = 'dummy.crt';
}
/** SSL aktivieren */
$this-conn-ssl_set($this-ssl_key, $this-ssl_crt, $this-ssl_ca,
NULL, NULL);
}
$rw = @mysqli_real_connect($this-conn, $this-host, $this-user,
$this-pwd, $this-db, $this-port, '',
$flags);
if($rw)
{
$this-conn = @mysqli_init();
if($this-ssl)
{
if($this-ssl_crt === '')
{
$this-ssl_crt = 'dummy.crt';
}
$this-conn-ssl_set($this-ssl_key, $this-ssl_crt,
$this-ssl_ca, NULL, NULL);
}
$rw = @mysqli_real_connect($this-conn, $this-host, $this-user,
$this-pwd, $this-db, $this-port, '',
$flags);
break;
}
usleep(62500);
}
if(!$rw)
{
$this-conn = 0;
$this-error(mysqli_connect_error());
}
}
Extract text from string
Hi, Is there such a way in a MySQL query to extract the text this is a test from the following strings as a example stronga href=http://www.domain.com/; class=linkthis is a test/a/strong stronga href=http://www.domain.com/; title=this is a test class=linklink/a/strong Thanks Neil
RE: innodb_lock_wait_timeout and replication
I get all your mails for yearscould you not help me and suppress my name from your contact thanks To: mysql@lists.mysql.com From: markus.f...@fasel.at Subject: innodb_lock_wait_timeout and replication Date: Fri, 12 Oct 2012 16:08:42 +0200 I encountered an error MySQL Error: Lock wait timeout exceeded; try restarting transaction MySQL Error No: 1205 For this very statement an entry in the binlog was filed ... # Query_time: 52 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 ... Why is there an entry in the binlog if the statement failed? If it is in the binlog it will be replicated to a slave and on the slave it will succeed (maybe). Does this mean that a Lock wait timeout exceeded potentially drive the slave out of sync? -- Kind Regards, Markus Falb
Re: innodb_lock_wait_timeout and replication
You are subscribed to the MySQL mailing list. You will need to unsubscribe yourself. On Fri, Oct 12, 2012 at 6:58 PM, Hubert de Donnea hubertdedon...@hotmail.com wrote: I get all your mails for yearscould you not help me and suppress my name from your contact thanks To: mysql@lists.mysql.com From: markus.f...@fasel.at Subject: innodb_lock_wait_timeout and replication Date: Fri, 12 Oct 2012 16:08:42 +0200 I encountered an error MySQL Error: Lock wait timeout exceeded; try restarting transaction MySQL Error No: 1205 For this very statement an entry in the binlog was filed ... # Query_time: 52 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 ... Why is there an entry in the binlog if the statement failed? If it is in the binlog it will be replicated to a slave and on the slave it will succeed (maybe). Does this mean that a Lock wait timeout exceeded potentially drive the slave out of sync? -- Kind Regards, Markus Falb
RE: Extract text from string
No. Or if there is, it is too ugly to contemplate. You are better off doing such in a real programming language. MySQL will locate row(s) containing this is a test via LIKE, RLIKE, or FULLTEXT (with caveats). You need to take over from there. -Original Message- From: Neil Tompkins [mailto:neil.tompk...@googlemail.com] Sent: Friday, October 12, 2012 9:56 AM To: [MySQL] Subject: Extract text from string Hi, Is there such a way in a MySQL query to extract the text this is a test from the following strings as a example stronga href=http://www.domain.com/; class=linkthis is a test/a/strong stronga href=http://www.domain.com/; title=this is a test class=linklink/a/strong Thanks Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Extract text from string
Of course... There's many libraries for REGEX which you can use, link it with an UDF and boom! http://dev.mysql.com/doc/refman/5.1/en/adding-udf.html S On Fri, Oct 12, 2012 at 12:56 PM, Neil Tompkins neil.tompk...@googlemail.com wrote: Hi, Is there such a way in a MySQL query to extract the text this is a test from the following strings as a example stronga href=http://www.domain.com/; class=linkthis is a test/a/strong stronga href=http://www.domain.com/; title=this is a test class=linklink/a/strong Thanks Neil
Re: innodb_lock_wait_timeout and replication
Am 12.10.2012 19:58, schrieb Hubert de Donnea: I get all your mails for yearscould you not help me and suppress my name from your contact what the hell why do people subscribe to a mailing-list without use google to understand what a mailing-list is? http://en.wikipedia.org/wiki/Mailing_list and now read your mail-headers and use the List-Unsubscribe: included in every message of any mailing-list on this world before you disgrace yourself more signature.asc Description: OpenPGP digital signature
