Re: excessive time spent in "statistics" status

On Fri, Sep 14, 2007 at 01:33:51AM -0700, Jeremy Cole wrote: > Hi Shawn, Lucio, > > >SELECT STRAIGHT_JOIN > >FROM ... > >LEFT JOIN ... > >WHERE ... > >... > > Just to correct a point here... if a query uses only LEFT JOIN or RIGHT > JOIN, the join order is fixed by the query's order itself,

Re: "segment fault" when using mysql++

He's saying that instead of this: >fprintf (fp1, r["content"]); You at least want something like this: fprintf (fp1, "%s", r["content"]); if you're going to use fprintf, or, if you want something more c++-like, you'd use a function besides fprintf altogether. Ot

Re: su-like functionality

On Tue, Jul 24, 2007 at 02:18:21AM +0200, Mogens Melander wrote: > > On Mon, July 23, 2007 10:19, Carlo Sogono wrote: > > Is there a way for mysql to login as an administrator and "su" to a > > normal user? > > > > What I'd like to achieve is a way to log in to our clients' accounts (we > > are a

Re: My bin.log directory is getting full

Perhaps the "expire_logs_days" variable does what you're looking for. --Pete On Thu, Apr 26, 2007 at 09:01:58PM -0400, Tim Lucia wrote: > # cat /etc/cron.mysql/20-purgemasterlogs > #!/bin/sh > /usr/bin/mysql --defaults-file=/root/.my.cnf -e 'show master logs; purge > master logs before date_sub(n

Bug in 4.1.21 with "between" comparing datetime and dates?

eval2; If datetimeval is cast to a date in the where clause, the row is returned in both versions. --Pete ------ Pete Harlan ArtSelect, Inc. [EMAIL PROTECTED] http://www.artselect.com ArtSelect is a subsidiary of a21, Inc. -- MySQL General Mailing List For list ar

Re: swapping column values in update

On Wed, Aug 02, 2006 at 12:35:30AM +0200, Martin Jespersen wrote: > I just ran the following sql (on mysql 4.1.20): > > update tbl set col1=col2, col2=col1 I went through this recently with the MySQL folks and the long and short of it is that the above statement is undefined in MySQL. It may s

Re: MySQL 5.0.18 crashing on AMD64

On Tue, Mar 28, 2006 at 10:12:58AM +0200, Sander Smeenk wrote: > Hello! > > I have a dual Opteron 250 system with 4GB memory running Debian with > MySQL version 5.0.18 and unfortunately it keeps crashing at (somewhat) > random intervals with messages like: > > | Mar 14 00:32:59 zwart mysqld[29820

Re: Question about interactive timeout

On Wed, Mar 15, 2006 at 05:42:40PM +0100, Mechain Marc wrote: > I have a Mysql Server (4.1.8) where some sessions stay connected for a > value greater than "Interactive timeout" value. > > Here is an abstract of the "show processlist" command: > > | 129996 | fret | mtt04.back:33598 | fret | Sle

Re: display a hierarchic tree

On Mon, Jan 30, 2006 at 03:04:20PM +0200, Gleb Paharenko wrote: > Hello. > > This is not an exact answer on your question, however it could be > interesting for you: > http://dev.mysql.com/tech-resources/articles/hierarchical-data.html This is a good article. One thing it leaves out of the dis

Re: Help with SQL DELETE issue

Agreed. OTOH, I would recommend 4.1.15 until they solve the problem with updates in 4.1.16 apparently not using index prefixes. --Pete On Sun, Jan 15, 2006 at 05:07:08PM +, Jocelyn Fournier wrote: > Hi, > > Excepted if he found a bug in an older version of MySQL, it's of course > false !

4.1.16: updates not using index prefixes

FYI, 4.1.16 appears not to be using prefixes of compound indexes when doing updates. Reverting to 4.1.15, or adding an index consisting of only the desired field, restores reasonable behavior. I have added feedback to a possibly-related bug, , but wanted t

Re: R: MySQL 5.0 : error using max(idrow) on a null value

> > select ( select max(c.idrow)+1 from provasql c ) , 'This is only a test'; Does changing "max(c.idrow)+1" to "coalesce(max(c.idrow),0)+1" solve your problem? --Pete On Fri, Nov 04, 2005 at 04:56:26PM +0100, AESYS S.p.A. [Enzo Arlati] wrote: > > > For a while my application should support b

Re: Per-thread memory use question

he memory as soon as they're done with it or hold onto it for (probable) future use. If I had to guess from reading that page, I'd say they probably free it, but if I had to guess from the memory use of our db server, I'd say they don't. --Pete > Pete Harlan <[EMAIL PROTE

Per-thread memory use question

s the, e.g., sort, is finished, then I only have to figure out how many threads are likely to need a sort_buffer at any given time. I looked through the manual, various online documentation, and the source, but haven't been able to determine an answer. Thanks, -- Pete Harlan [EMAIL PRO

Re: Problems with x86_64 mysql-standard-4.1.12 [SOLVED]

On Mon, May 23, 2005 at 11:52:50PM -0700, Kevin Burton wrote: > Pete Harlan wrote: > > >In addition to failing the tests, I deployed the server on Machine 1 > >for a while and it failed quickly, with a simple insert hanging up and > >"kill " being unable to

Problems with x86_64 mysql-standard-4.1.12

ine 1 for a while and it failed quickly, with a simple insert hanging up and "kill " being unable to kill it. (The thread's state was "Killed", but it didn't go away and continued to block other threads from accessing the (MyISAM) table.) Any help would be appre

Re: bug or feature, <> 'blah' does NOT work with null records

On Thu, Oct 28, 2004 at 11:50:12AM +0200, Jigal van Hemert wrote: ... > Fortunately there is function COALESCE() that will return the first argument > that is not NULL. In case of NULL values you can use a default value for an > expression: COALESCE( `col`*2, 14) will produce 14 if `col` is NULL.

Trouble compiling 4.0.21 under amd64 Debian unstable

export CC=gcc-3.4 ./configure --prefix=/usr/local/mysql \ --exec-prefix=/usr/local/mysql \ --with-mysqld-ldflags=-all-static \ --disable-shared --enable-thread-safe-client \ --with-extra-charsets=all ========

Re: mysql 4.0.20 uses just one CPU on Gentoo (now: glibc patches)

On Wed, Aug 18, 2004 at 08:03:36AM +0400, Mike Blazer wrote: > Mike Blazer wrote: > > In addition to my previous posting - on this machine I have glibc.2.3.2 > which was installed using the Gentoo emerge native installer. Dunno, the > mysql manual says a lot about various bugs and patches for glib

Re: what os to use for mysql on amd64?

On Thu, Jul 29, 2004 at 06:26:23PM +0300, Egor Egorov wrote: ... > No. I've forgot to tell that the -Max binary is linked dynamically > because it uses SSL. Is there a reason the SSL libraries can't also be linked statically? Do you recommend against running the -Max binary, because it doesn't us

Re: NULL problem

It might help if you say what version of MySQL you're using, give the table schema, etc. I couldn't reproduce the behavior you describe here. --Pete On Mon, Jul 19, 2004 at 12:13:47PM -0500, Deepak Vishwanathan wrote: > Hi, > > > > I have a table with a column that has the Unique key constr

Re: Between Operator

On Fri, Jul 09, 2004 at 09:39:02AM -0500, Craig Hoffman wrote: > Style: Traditional > Area: Yosemite > Rating: From: 5.5 To: 5.10c ... > "SELECT * FROM routes, users WHERE area='$area' AND style='$style' > BETWEEN rating='[$rating1]' AND rating='[$rating2]' GROUP BY route > ORDER BY rating ASC

Re: Help with apostrophe and FTS

On Fri, Jun 11, 2004 at 03:34:14PM +0300, Egor Egorov wrote: > "Andrea Gangini" <[EMAIL PROTECTED]> wrote: > > > > > Well yes, it's an option. I really need this functionality. But > > on mysql site, under source downloads, there's this warning: " For > > maximum stability and performance, we re

Re: MySQL on Linux

The 2GB (not 2 Mb) file size limitation on Linux went away years ago. Unless your distribution is very old you won't have a problem. --Pete On Tue, Apr 06, 2004 at 05:05:59PM -0300, Ronan Lucio wrote: > Hi All, > > I always worked with MySQL on FreeBSD systems. > > Now I need to install am MyS

Re: Why can't I use an "AS" value in the WHERE clause.

On Mon, Apr 05, 2004 at 08:03:33PM -0500, Paul DuBois wrote: > At 17:29 -0700 4/5/04, Daevid Vincent wrote: > >I'm curious when will I be able to do something like this: > > > >SELECT *, IF(( (unix_timestamp()-unix_timestamp(last_seen)) < 600),1,0) as > >active FROM wifi_table WHERE active = 1; >

Re: How stable is 4.0.18?

My two cents: Just upgrade. We hammer on it pretty hard and the transition has not only been smooth, it's been a delight. The query cache, on our load, handles 60% of the queries, which I never imagined would happen. 4.0.18 feels as boringly stable as most released MySQLs :) --Pete On Thu, Ma

Re: Performance Koan

Do a mysqldump -d on both machines to make sure the schema, and the indexes in particular, are exactly the same. Run "analyze table" on all tables. Make sure the MySQL conf files (e.g., /etc/my.cnf) are the same. Do an 'explain ' on both machines; the output should be the same. 4.0.1 isn't the

Re: *Forcing* use of TCP/IP by clients for "localhost"

In this case it might be easier for you to just modify the MySQL source to disable looking for the socket, and treat localhost as 127.0.0.1. --Pete On Fri, Jan 30, 2004 at 06:33:22AM -0800, [EMAIL PROTECTED] wrote: > --- [EMAIL PROTECTED] wrote --- > > i've never set up tunnels and such, but are

Re: max_user_connections problem after upgrading

What does "show processlist" say when the connections are maxed out? (You may have to leave a client logged in to reserve a slot so you can submit this query.) If it shows only a few connections, then there's something seriously wrong. If it shows a ton of idle connections, it should tell you whi

Re: Strategies for optimizing a read-only table

On Mon, Nov 10, 2003 at 05:03:35PM +0100, Harald Fuchs wrote: > In article <[EMAIL PROTECTED]>, > gerald_clark <[EMAIL PROTECTED]> writes: > > > Matt W wrote: > > >> Hi Jeremy, > >> > >> Sorry, it seems like I'm saying this a lot lately. Is it not true that > >> if the whole table will fit in [f

Re: InnoDB and raw tablespace

On Wed, Nov 05, 2003 at 12:08:29PM +1100, Chris Nolan wrote: > To my knowledge, ext2 does have the [2GB filesize] limitation but > ext3 does not. ext2 does not have this limitation. It was never a limitation of the filesystem, only kernel/glibc. On 64bit architectures ext2 has been handling larg

Re: Hardware Raid and 2 Gig Limit

On Fri, Oct 31, 2003 at 05:44:02AM -0500, David T-G wrote: > % > % Does the 2 Gig file size limit on Linux get broken when I have a hardware > % raid controller? > > The limit applies only to ext2 filesystems, and not all of them at that; > ext3 and reiserfs (and others) can happily write much la

Re: Error 11 and Linux 2.4 (LONG with details included)

Your ulimit -a output shows 256 max processes; mysql threads each count as one process. If you're wondering why you get 245 and not 256, perhaps some of the kernel threads take up the remaining slots. If you're wondering why you can't get 500 threads, have you tried doing a "ulimit -u 500" before

Re: Using SQL variables

You have to initialize @var to something first, or it's just null. So try "set @var := 0;" before your query. Also, you don't say which version of MySQL you're using, but I'm using 4.0.14 and I can't say "... as number, * from ...", but have to say "... as number, tableName.* from ...". HTH, --

Re: ERROR 2013:Lost connection to MySQL server

On Wed, Jul 02, 2003 at 12:55:38AM +0300, Heikki Tuuri wrote: ... > 4.0.13 has better diagnostics. Please upgrade to it if you are not using > MySQL replication. Is there something wrong with 4.0.13's replication, or does it not replicate properly with 4.0.12? --Pete -- MySQL General Mailing Li

Upgraded 3.23 to 4.0: No problems at all.

to another packaged version, but clearly the problems aren't at the database end of things. Good work. Thanks again, -- Pete Harlan harlan @artselect.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SQL no-op?

On Fri, Jun 06, 2003 at 05:19:20PM -0500, Paul DuBois wrote: > At 15:11 -0700 6/6/03, Jeremy Zawodny wrote: > >On Fri, Jun 06, 2003 at 04:15:29PM -0500, Mark Rages wrote: > >> I need a placeholder statement that does nothing. > >> > >> Is there something more elegant than > >> SELECT FROM ... WHERE

Re: [More Info] Unique compound index slower than non-unique?

Following up to my own question. Some more information. SLOW: Create unique compound index on a table, do a query. FAST: Create same index, only non-unique, do a query. FAST: Do slow method, but ANALYZE TABLE before doing query. It looks like the key-distribution information that's stored by ANA

Unique compound index slower than non-unique?

Hi, When querying a largish (370,000 rows) table, a unique compound index on its three int columns performs slower (as slow as no index at all) than when I use the same index created without the "unique" keyword. I've repeated it dozens of times: Create the index unique, and it's slow, create it

Re: Using ssh tunnel and mysql

On Mon, Mar 10, 2003 at 11:32:06AM -0600, Paul DuBois wrote: > Whether it's a feature or not, it's not always so easy to figure out > what to do. If you specify -h localhost, it can be argued that you > really want the socket even if you specify the port. It can be > argued conversely that if you

Re: Using ssh tunnel and mysql

On Fri, Mar 07, 2003 at 05:43:29PM -0800, Jeremy Zawodny wrote: > On Fri, Mar 07, 2003 at 05:37:38PM -0800, LZ Orders wrote: > > Hi. I wanted to connect from a client machine to a MySQL server using > > ssh. I execute the following on the local machine (the server is > > foo.bar.com): > > > > %

Re: Linux Filesystem Type and Performance

If you have that many files in a directory, I would try a filesystem that indexes directories. Reiserfs does, and there's a patch somewhere for ext2 (and probably for ext3). I don't know about the other filesystems. Otherwise the application has to do a linear search through the directory every

Re: Row numbers

has to put the results in an array of some kind I believe those numbers are unknown when generating the row values. -- Pete Harlan, who doesn't speak for the MySQL developers of course. [EMAIL PROTECTED] - Before posting, pl

Re: MySQL 4.0.9 is released

On Tue, Jan 14, 2003 at 12:42:18AM +0200, Jani Tolonen wrote: ... >* Added join operator `FORCE INDEX (key_list)'. This acts likes `USE > INDEX (key_list)' but with the addition that a table scan is > assumed to be VERY expensive. One bad thing with this is that it > makes `FORC

Re: InnoDB and auto_increment fields

> stored? Why isn't this done automatically as it is for [ISAM] tables? As for the "why", I'm not a MySQL developer, but I believe the reason goes something like this: When ISAM tables were implemented, they did it the "wrong" way. When other table types came along, they fixed this "bug" and do

Re: How to see whether a field is contained in a string (reverse of LIKE)

Or you could just reverse the arguments to LIKE, so your field is on the right and your string is on the left. You may have to surround your field with concat('%', field_name, '%') (or just use regexp), but LIKE is a binary string comparison operator and doesn't care which, if either, arguments a

Re: Re: Max NB of MyISAM tables / DB ( Ext3 & linux )

On Wed, Sep 25, 2002 at 08:49:43PM +0300, Iikka Meril?inen wrote: > Hello, > > If the number of files is your concern, have you considered using InnoDB? It > spans tables across any number of data files you want. The performance is > great, too. The .frm files are still there, though, one per fi

Re: Max NB of MyISAM tables / DB ( Ext3 & linux )

> If not, i know that ext3 can have ten of thousands files in a directory. > But commande like 'ls' will become slower and slower ... > Is this also slowing mysql ? I believe it would have to. There is a patch somewhere (I don't know if it's maintained) for adding indexed directories to ext2/ext

Re: 3.23.52 hangs sometimes

Some people have had problems with the binary of 3.23.5x. We had that problem, and when we went back to a self-compiled 3.23.46 things worked normally again. The bad behavior looked like normal operation for anywhere from two hours to five days, followed by a CPU meltdown with loads over 200, wh

Re: Rename Table in Replication failed / command missing in slave log

If you're using InnoDB tables, replication stops the slaves from running. Heikki said he'd try to get this fixed for 3.23.52. --Pete On Mon, Aug 12, 2002 at 04:05:11PM +0200, Lutz Maibach wrote: > Hi, > > today I noticed a strange behaviour in MySQL 3.23.49a-Replication I can't explain. > A

Re: Re: InnoDB: Looong pause when log file is full?

Hi Heikki, Thank you for responding. > > > (http://www.innodb.com/ibman.html#InnoDB_tuning), but am getting bit > > > when the log files are full and the buffer pool is checkpointed. > > InnoDB does 'fuzzy checkpoints'. That means modified database pages in the > buffer pool are flushed to disk

Re: Re: InnoDB: Looong pause when log file is full?

Thanks for your feedback (and your general untiring devotion to the cause...) On Thu, Aug 01, 2002 at 04:30:10PM -0700, Jeremy Zawodny wrote: > > about what we can do to alleviate this? Instead of having three 150mb > > log files, would we be better off with 30 15mb log files? > > It shouldn't

InnoDB: Looong pause when log file is full?

a separate disk? Alternately, is there a way to trigger this action at night, so we can avoid it happening during the day? It shut us down for about five minutes today. Details follow. Many thanks, --Pete Harlan [EMAIL PROTECTED] Possibly relevant details: 1. Binary mysql-max-3.23.51-pc-linux-gnu

InnoDB, replication and create table w/3.23.51?

I just switched to using InnoDB tables (Linux, using binary mysql mysql-max-3.23.51, autocommit on), and now my replication dies when I rename a table, with: ERROR: 1192 Can't execute the given command because you have active locked tables

Re: NUMERIC field contents

On Tue, Sep 18, 2001 at 01:45:30PM -0700, Dana Powers wrote: > And my question is, if you've defined your column to have (10,2) precision, > why would you try to insert a higher precision number? Perhaps he's writing a report, and the application needs to know the size of the data to expect. It'

Re: How to make this code pretty?

> Doesn't doing it that way preclude using $dbh->quote? That could mess up > if the name had a single quote in it. Placeholders remove the need for manual quoting. One of their benefits. --Pete > On 16 Aug 2001, Harald Fuchs wrote: > > > I'd do it like that: > > > > my $sql = q{ > > REPLACE

Re: Creating Table with a Default Datetime field

TIMESTAMP is not the solution to his problem; he wants the date to default to now() when the record is created, not updated. There's currently no way to do this in MySQL; default values must be constants. --Pete > hi. > > check out the TIMESTAMP column type...maybe TIMESTAMP(14) as the colum

Re: mysqldump bug during regenerate enum field.

It would seem that there is a bug, and it's that "create table" accepts an 'illegal' definition. It should either convert the integer to a literal (making the definition legal and dumpable), or it should barf on it. IMO, of course. --Pete [Charset iso-8859-1 unsupported, filtering to ASCII...

Re: Re: Plase Let us know if there is a Racialism for Mysql productand

ounds that it isn't confusing to a native English speaker is silly: It isn't English, and it's slightly ambiguous. I thought Yusuf's explanation of why it was confusing was clear, if not the King's English. -

Re: backup mysql using crontab

> Probably because you need to redirect your output into the file AFTER all > the options. Rewrite your command like this: In which shell? In SunOS /bin/sh, or in bash, the shell strips out the redirection, and the program sees what's left. It doesn't matter where the redirection is; it can eve

Re: am I alone? (scale)

table corruption. We don't even use precompiled binaries ;) Obviously one person's good experience doesn't mean there's not a problem somewhere, but we sure haven't had any trouble, and it's not for

Re: Quoting numbers? (was Re:MySQL Tables)

hould not ever matter to quote numeric literals, then treat as bugs the cases where it turns out to. -- Pete Harlan [EMAIL PROTECTED] (Is "Antwort" Swedish?) > > Tis true Rolf but you can bet your bottom dollar that at somepoint > > a confusion will arise when it is

Quoting numbers? (was Re:MySQL Tables)

> I think you'll get better results if you don't quote your numbers. Quotes > should be used for text and dates (depending) but not numbers. Out of curiosity, why? We use quotes for numbers all the time here, for consistency's sake; the programmer doesn't have to worry about the representation

Bug in .32: inconsistent truncation of decimal places

The selects should produce the same values; if it can be fixed either way, I prefer the behavior of the first select (non-truncation). Thanks for looking at this, and for MySQL generally! -- Pete Harlan [EMAIL PROTECTED] - Befor

Re: forcing tcp/ip connect when connecting to localhost

>> can I force the mysql clients to use a tcp/ip connect if connecting to >> 'localhost'? > Normally connecting to the true DNS hostname or the ip address of > the machine will do the trick. Connecting to localhost will connect > via the loopback address of 127.0.0.1. If you connect to the true

Re: Bug in MySQL TRIM() Function! ?

I think it would have to be considered a simple bug. If you're not convinced by the 'both' example he gave, here's one that's even more obviously a bug: mysql> select trim(leading 'foo' from 'foo'); ++ | trim(leading 'foo' from 'foo') |

BLOB usage

Posted for "De Q. Vo" <[EMAIL PROTECTED]>, who can't post to the list because the list cop program doesn't like the netzero trailer: Hello, Is there an INDIRECT data type which can fetch and store data directly from a file system? I wanted to build a dynamic web site with contents derived from a

Re: Default value for DATE column

MySQL only supports constant default values. You'll have to insert the date explicitly when you create the record if you want to simulate initialization to now(). Timestamps will update themselves whenever any other field in the record changes, unless you explictly tell them not to. So they are