RE: MySQL Community Server 5.1.63 has been released

What language are you working in? (Not all have binding) PREPARE might be using mysql_real_escape_string behind the scenes. :( Caching too much of the "prepare" would defeat one important "feature" of MySQL: It's ability to use a different query plan when given different constants. > -Or

RE: Deadlock due lockwait. How can I tell mysql to wait longer?

... WHERE id IN (...) -- This will (I think) sort the IN list. Therefore, if two queries have the same (or overlapping) IN values, there cannot be a deadlock. (I am assuming nothing else being touched.) If, on the other hand, you try to get a list of rows by other means, and the order of the

Re: Performance question

Am 14.05.2012 23:05, schrieb Nicolas Rannou: > *1* to create 3 tables:* > user - info about a user > images - info about an image > user_image_mapping > > *2* to create 2 tables* > user - info about a user > -> a field would contain a list which represents the ids of the images > the user can

Performance question

Hi all, We are currently designing a database for our application (python/mysql) and we have some performance concern: We would have "users" and "images". "users" can view some "images". "images" can be viewed by several "users". (n to m mapping) Which would be most efficient practice (regarding

RE: drop partitions

Note that in many (too many) cases, _all_ partitions are opened, even if only one is really needed. > -Original Message- > From: louis liu [mailto:yloui...@gmail.com] > Sent: Monday, May 14, 2012 7:02 AM > To: Johan De Meersman > Cc: mysql@lists.mysql.com; Rick James > Subject: Re: drop p

RE: MySQL Community Server 5.1.63 has been released

What you do recommend in place of mysql_real_escape_string()? > -Original Message- > From: Govinda [mailto:govinda.webdnat...@gmail.com] > Sent: Monday, May 14, 2012 7:34 AM > To: Johan De Meersman > Cc: mysql@lists.mysql.com > Subject: Re: MySQL Community Server 5.1.63 has been released

Re: Deadlock due lockwait. How can I tell mysql to wait longer?

Andrés, with pleasure. Imagine a website that is used to search, just for example, hotel rooms for booking. It is possible that a programmer would: 1) issue a select that returns the IDs the rooms matching the criteria 2) do a loop in the code scanning each ID of the resultset and for each ID i

Re: Deadlock due lockwait. How can I tell mysql to wait longer?

Claudio, would you please extend the example to the use of in? On Mon, May 14, 2012 at 10:08 AM, Claudio Nanni wrote: > In my experience if you have a poor designed code that run the same query > for hundreds or thousands of times in a very short timespan (like some > programmers do in for-loop

Re: MySQL Community Server 5.1.63 has been released

- Original Message - > From: "Reindl Harald" > > but what about the dramatical reduced query-cache hits i see > in some peace of software switching to prepared statements? > > dbmail2 as example had around 300 sql-actions per second > dbmail3 using prepared statements currently around

RE: MySQL slowlog - only in file?

Alternatively, you could use MySQL Enterprise Monitor (Oracle) and capture the queries for very easy analysis. I have found it very effective, especially when you have very high number of queries per second. In turning on this capture, I have not noticed any impact on database performance. Da

Re: Deadlock due lockwait. How can I tell mysql to wait longer?

In my experience if you have a poor designed code that run the same query for hundreds or thousands of times in a very short timespan (like some programmers do in for-loop instead of using a IN for example) you can put mysql on its knees, in some cases it may be the practical implementation of some

Re: MySQL Community Server 5.1.63 has been released

Am 14.05.2012 16:50, schrieb Johan De Meersman: > - Original Message - >> From: "Govinda" >> >> 1.) Is anyone *who knows what he is doing* still using >> mysql_real_escape_string()? Ever? > > I seem to vaguely remember someone showing me some code that would bypass > escaping; but I d

Re: Mysql is toying me... why sometimes an insert or update can be slow!? I getting bald cuz this

I used to have these issues in mysql version 5.0.41. On Mon, May 14, 2012 at 8:13 PM, Johan De Meersman wrote: > - Original Message - > > From: "Ananda Kumar" > > > > If numeric, then why are u using quotes. With quotes, mysql will > > ignore the index and do a full table scan > > Will

Re: MySQL Community Server 5.1.63 has been released

- Original Message - > From: "Govinda" > > 1.) Is anyone *who knows what he is doing* still using > mysql_real_escape_string()? Ever? I seem to vaguely remember someone showing me some code that would bypass escaping; but I didn't really pay a lot of attention, to be honest :-) Person

Re: Deadlock due lockwait. How can I tell mysql to wait longer?

Argh. I meant to send this to the list but it doesn't have the reply-to set as I expect... On Mon, May 14, 2012 at 10:46 AM, Baron Schwartz wrote: > Johan, > > On Mon, May 14, 2012 at 9:27 AM, Johan De Meersman wrote: >> What I fail to understand, Baron, is how there can be a deadlock here - bo

Re: Mysql is toying me... why sometimes an insert or update can be slow!? I getting bald cuz this

- Original Message - > From: "Ananda Kumar" > > If numeric, then why are u using quotes. With quotes, mysql will > ignore the index and do a full table scan Will it? Common sense dictates that it would convert to the column's native type before comparing; and a quick explain seems to co

Re: MySQL Community Server 5.1.63 has been released

Bugs Fixed * Security Fix: Bug #64884 was fixed. * Security Fix: Bug #59387 was fixed. >>> >>> Anyone want to elaborate on the nature or severity of the security >>> problem? Both are private / inaccessible to me. >> >> Bug #64884 was apparently also applicable to, and fixed

Re: Mysql is toying me... why sometimes an insert or update can be slow!? I getting bald cuz this

If numeric, then why are u using quotes. With quotes, mysql will ignore the index and do a full table scan On Mon, May 14, 2012 at 7:31 PM, Andrés Tello wrote: > > > Yes, I'm using indexes, accountid is the primary key, and is numeric and > autoincrement. The process doing the deadlock is no lo

Re: Mysql is toying me... why sometimes an insert or update can be slow!? I getting bald cuz this

Yes, I'm using indexes, accountid is the primary key, and is numeric and autoincrement. The process doing the deadlock is no longer done... The structure of the inserted database has changed. Originaly it was a single table with 219millions rows, now I partitioned the hable in... 60 tables, 1 for

Re: drop partitions

not actually , first partition to have been dropped is no longer in use ,we dropped partition from old -> new 2012/5/14 Johan De Meersman > - Original Message - > > From: "Rick James" > > > > If you have 14 partitions in each of 390 tables, and if you have most > > of the tables '

Re: New Fast MySQL Compatible Server (Take 2)

- Original Message - > From: "Hiromichi Watari" > > I uploaded the technology whitepaper to the website. Hmm, interesting idea, to allocate a thread per table - I can imagine it's going to be pretty heavy on the thread cache, though :-) How does it respond to self-joins, subselects and

RE: Deadlock due lockwait. How can I tell mysql to wait longer?

Going on a limb here...: I believe I have occurred similar issue (i.e. two transactions go into an indefinite wait).Though, very infrequent occurrence. My only explanation at that time was that there is some "loophole" when the deletes/inserts had some impact also on the table indexes. In

Re: multiple instances in win 7 -- any idea

On 5/13/2012 6:53 PM, Brown, Charles wrote: > I'm trying to install multiple instances of mysql on windows 7, 64bit. 3hrs into the job, I'm not making progress. Does anyone have an idea? 1) The installers are designed to work on single-instance installs or upgrades. 2) You only need one inst

Re: Deadlock due lockwait. How can I tell mysql to wait longer?

- Original Message - > From: "Baron Schwartz" > > Because it can be resolved by rolling back just one of them. Why > destroy ALL the work people are trying to accomplish, if you could > just throw away some of it? What I fail to understand, Baron, is how there can be a deadlock here - bo

Re: Mysql is toying me... why sometimes an insert or update can be slow!? I getting bald cuz this

is accountid a number or varchar column On Sat, May 12, 2012 at 7:38 PM, Andrés Tello wrote: > While doning a batch process... > > show full processlist show: > > | 544 | prod | 90.0.0.51:51262 | tmz2012 | Query |6 | > end | update `account` set `balance`= 0.00 + >

ANN: 70% Ten Year Anniversary discount on all our products!

Upscene Productions is celebrating it's 10 year anniversary with a massive discount on all our products: 70% discount until the end of May. Don't forgot to blog and twitter about this! We produce database development, management and testing tools for: * Oracle * Microsoft SQL Server * MySQL * I

Re: drop partitions

- Original Message - > From: "Rick James" > > If you have 14 partitions in each of 390 tables, and if you have most > of the tables 'active', then you are possibly thrashing in the > table_open_cache. A distinct possibility. > Compute (SHOW STATUS): > Opened_tables / Uptime -- don't wan

RE: MySQL slowlog - only in file?

Alternatively, you could use MySQL Enterprise Monitor (Oracle) and capture the queries for very easy analysis. I have found it very effective, especially when you have very high number of queries per second. I have not noticed any impact on database performance. David. -Original Message--

Re: MySQL Community Server 5.1.63 has been released

- Original Message - > > > From: "Baron Schwartz" > > > >> Bugs Fixed > >> * Security Fix: Bug #64884 was fixed. > >> * Security Fix: Bug #59387 was fixed. > > > > Anyone want to elaborate on the nature or severity of the security > > problem? Both are private / inaccessible t

RE: Mysql is toying me... why sometimes an insert or update can be slow!? I getting bald cuz this

> -Original Message- > From: Andrés Tello [mailto:mr.crip...@gmail.com] > Sent: May 12, 2012 10:08 AM > To: mysql > Subject: Mysql is toying me... why sometimes an insert or update can be > slow!? I getting bald cuz this > > While doning a batch process... > > show full processlist show:

Re: MySQL slowlog - only in file?

Alternatively, you can copy the data into another table easily: http://www.bitbybit.dk/carsten/blog/?p=115 Best, / Carsten On 14.05.2012 09:34, P.R.Karthik wrote: Hi Rafal, If there are more slow queries in your server and logging them into a table will increase the IO of the server. It is b

MySQL 5.1.59 - slow_log purge problem.

Hi all. I write a script to delete rows from slow_log older than 2 weeks. #!/bin/bash if [ $# -ne 1 ]; then echo "Usage: $0 mysql_config_file" exit 1 fi SELECTQUERY="select * from slow_log where start_time < DATE_ADD(NOW(),INTERVAL - 2 WEEK)" DELETEQUERY="delete from slow_log where start_time <

Re: MySQL slowlog - only in file?

Hi Rafal, If there are more slow queries in your server and logging them into a table will increase the IO of the server. It is better to be in a file. The slow query log file can be processed easily by pt-query-digest . Regard