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, so using STRAIGHT_JOIN should have no effect whatsoever. True, because you say only left or right joins. Equally important, since the join order is fixed when you use LEFT JOIN, you *must* list the joins in the correct order when writing the query, otherwise you will see very poor performance. Only is missing from here, which could be misleading. MySQL will reorder the t0, t1 and t2 joins in: select ... fromt0 join t1 on ... join t2 on ... left join t3 on ... where ... MySQL's optimizer cannot reorder the joins because it has the potential to change the result of the query. Do have an example in mind? Thanks, --Pete -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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.
Otherwise, if your r[content] happens to return a string that
contains %s or some other thing that causes fprintf to look for more
data on the stack, you'll get output you weren't expecting.
--Pete
On Thu, Aug 02, 2007 at 11:04:13AM +0800, wangxu wrote:
actually I am using this piece of code,but I replaced fprintf to printf
just to simplify the problem.
and the field content actually stores string content.
Shall this mean some thing different from your explanation?
while (r = res.fetch_row()) {
FILE *fp1;
char *fname=new char[50];
fname[0]='\0';
strcat(fname,HTML_HOME.c_str());
strcat(fname,id);
strcat(fname,.html);
fp1 = fopen(fname, w);
fprintf (fp1, r[content]);
fclose(fp1);
delete fname;
}
Warren Young wrote:
I'm replying to you both personally and to the MySQL++ mailing list,
where this message is on topic. Please reply only on the list, not to
me directly.
wangxu wrote:
below is my code;these code works very fine until, the length of the
field content exceeds 30,
How certain are you about this threshold? It seems a very odd number
for a computer to care about. If you'd said 65536 bytes, or 16.7 MB,
I'd put a lot more credence in your assertion.
mysqlpp::Row r;
while (r = res.fetch_row()) {
printf (r[content]);
}
I'm not sure it's the problem, but you really shouldn't use printf()
for this. The main reason is that printf() will scan the resulting
string for % signs and try to interpret them as formatting options.
If it finds any, it will then try to find varargs, and fail; this
would easily explain your segfault. The byte count does affect the
chances that this will happen, so maybe that's where your perception
that it's data size related comes from.
Other reasons not to use printf() with MySQL++ data types are
inefficiency and type safety.
See examples/cgi_jpeg.cpp for the right way to emit bulk MySQL++ to
stdout.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 web-hosting company) without having to use their passwords. Having to su keeps ownerships and stuff like that in check. Thanks in advance, Carlo ??? I'm having a hard time imagine what you want to do with that ??? You, being the administrator (root), you should be able to do whatever, on any DB on your server, without being recognzied as anybody. Please elaborate. It wasn't my question, but I imagine this would be useful when you want to verify that you have set up their permissions correctly. E.g., a user says, I tried to do X but it wouldn't let me, and you go in as them, repeat the problem behavior, fix it, test your fix, and let them know it is fixed. (Or, similarly, verify for yourself that their user is unable to do things you don't want them to do.) --Pete -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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(now(), interval 30 day); show master logs;' /var/log/20-purgemasterlogs.log 21 This purges anything older than 30 days. HTH, Tim -Original Message- From: Brown, Charles [mailto:[EMAIL PROTECTED] Sent: Thursday, April 26, 2007 12:03 PM To: mysql@lists.mysql.com Subject: My bin.log directory is getting full Hello All. My bin.log directory is getting full with bin.log files. We are running out of space. What can I do in the short term? Is there a command that I can issue that will get rid of old bin log files not needed? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bug in 4.1.21 with between comparing datetime and dates?
I just filed bug #22317 about this. The following script fails to
return a row under 4.1.21 (on x86_64, anyway), but works correctly on
4.1.20 (and .18):
drop table if exists test1;
create table test1
(
datetimeval datetime,
dateval1 date,
dateval2 date
);
insert into test1
(datetimeval, dateval1, dateval2)
values
('2006-09-13 08:47:32', '2006-01-01', '2007-01-01');
select datetimeval
from test1
where datetimeval between dateval1 and dateval2;
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 archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 seem to work one way consistently (left-to-right evaulation of assignments, as you noticed), but they are free to change it whenever they want. The case that bit me was when I converted something like the above to use a multiple-table update, and at that point it evaulated all right-hand sides in a context where none of the assignments had yet been done. The answer was that neither case is guaranteed or defined in MySQL. The SQL standard seemed to me to define the all right-hand sides are evaulated in a context where none of the assignments have been done behavior. Perhaps someday MySQL will work that way, but until they say it does you can't count on any specific behavior. I suppose that means: begin transaction update tbl set tmp=col1, col2=col1 update tbl set col1=tmp commit --Pete -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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]: *** glibc detected *** double | free or corruption (!prev): 0x012b1ab0 *** | Mar 15 02:24:17 zwart mysqld[13255]: *** glibc detected *** free(): | invalid next size (normal): 0x012d3d30 *** | Mar 16 00:32:51 zwart mysqld[17749]: *** glibc detected *** double | free or corruption (!prev): 0x01333540 *** | Mar 16 14:44:07 zwart mysqld[471]: *** glibc detected *** double | free or corruption (!prev): 0x013e4160 *** You don't say which versions of glibc or the kernel you're running, but if you're running stock Debian Sarge, that's a problem because of its known-buggy glibc 2.3.2. We are still using MySQL 4.0.x, but have had good luck running Debian testing, and I expect sid would be good too if you don't need to run much more than a db server on this machine. I'd also use a recent kernel, though we've been running on a 2.6.13.1 kernel on a dual opteron with 6gb ram since last September without a problem, so the kernel doesn't have to be that recent. You can either manually install the later glibc's (2.3.5 or 2.3.6) from testing/sid, or just update the whole kit and kaboodle. --Pete -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 | Sleep | 61756 | | 129998 | fret | mtt04.back:33599 | fret | Sleep | 61759 | | 12 | ets | mtt04.back:33600 | ets | Sleep | 61759 | | 13 | ets | mtt04.back:33601 | ets | Sleep | 61759 | | 130001 | tls | mtt04.back:33602 | tls | Sleep | 61755 | The show variables command gives me: Interactive_timeout 28800 Wait_timeout 28800 Why those connections do still remains on the server with a value of 61700s while in a Sleep Command? It is rather strange for me; they normally should have disappeared after 28800s of inactivity. Could you give me a clue? Marc. Perhaps it's this: http://bugs.mysql.com/bug.php?id=16995 That's when using NPTL (Native Posix Thread Library) under the 2.6 Linux kernel. The bug report says killing one of the threads releases them all. --Pete -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 discussion of the adjacency-lists model is the use of an auxilliary transitive- closure table that makes querying the adjacency-list data efficient and straightforward. (The Celko treatment of the subject referred to in the article covers this.) For some applications where the data set is small and static enough, this can be a good solution too in the right situation, and one that avoids the mathematical hackery of nested sets. --Pete Jochen Kaechelin wrote: I have the following table: mysql select * from link_categories; ++---+-+---+---+-+ | id | level | category_id | category | parent_id | deleted | ++---+-+---+---+-+ | 1 | 1 |1000 | Software | 0 | 0 | | 2 | 1 |2000 | Harware | 0 | 0 | | 3 | 2 |1001 | Virenscanner | 1000 | 0 | | 4 | 2 |1003 | Packprogramme | 1000 | 0 | | 5 | 3 |1004 | Linux | 1001 | 0 | | 6 | 3 |1005 | Windows | 1001 | 0 | | 7 | 4 |1006 | Windows XP| 1005 | 0 | | 8 | 2 |1007 | Sniffer | 1000 | 0 | | 9 | 4 |1008 | Debian Woody | 1004 | 0 | | 10 | 1 | 10 | Vermischtes | 0 | 0 | ++---+-+---+---+-+ 10 rows in set (0.24 sec) and I want to display a tree like: Software Virenscanner Linux Debian Woody Windows Windowsd XP Packprogramm Sniffer Hardware Vermischtes Can someone give me hint how to build a query? I run MySQL 4.1.x and 5.0.x and I use PHP. Thanx. -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 ! (it would be a major issue which would make MySQL just unusable) Regards, Jocelyn David Rabinowitz a ??crit : Hi, We are using MySQL 4.1.16, recently upgraded from 4.0.18. On the old server we tried not to delete records, as their is a common belief that deleting records will corrupt the table's index and we will have to call repair table. I couldn't find any documentation on that. Unfortunately he is not working here any more, so we cannot ask him where he heard about it. Can someone confirm or deny this? Regards, David -- 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]
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, http://bugs.mysql.com/bug.php?id=15935, but wanted to send a message on the list in case someone is having trouble with 4.1.16 and doesn't know why, or is thinking of upgrading and might want to wait, or knows a solution to this problem besides downgrading. [Linux, MySQL-compiled x86_64 binary, InnoDB or MyISAM tables.] --Pete -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 both mysql 4 and 5 ( teh same copy on different sites of course ) so I should keep using a soluting wich should works well on both revision. I also have a couple of server with their database configured as master slave, so I don't trust to use autoincrement. When I can leave ther revision 4 at all I think to use the autoincrement using a trigger and mybe something like the oracle sequence . regards, Enzo Mysql 5 is much more picky on things you shouldnt have been able to do in the first place.. Just change idrow to auto_increment and stop doing max.. Its not needed. innodb properly handles auto_increment now. Jeff -Messaggio originale- Da: Jeff Smelser [mailto:[EMAIL PROTECTED] Inviato: venerd? 4 novembre 2005 16.42 A: mysql@lists.mysql.com Oggetto: Re: MySQL 5.0 : error using max(idrow) on a null value On Friday 04 November 2005 07:30 am, AESYS S.p.A. [Enzo Arlati] wrote: Hi, I'm trying to migrate from MySQL 4.1.11 to MySQL 5.0 and I get a problem with the new release. I have this table... provasql CREATE TABLE `provasql` `idrow` bigint(20) unsigned NOT NULL default '0', `descr` varchar(50) default NULL, PRIMARY KEY (`idrow`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ...this is the SQL command... insert into provasql ( idrow, descr ) select ( select max(c.idrow)+1 from provasql c ) , 'This is only a test'; ...and this is the error: ERROR 1263 (22004): Column set to default value; NULL supplied to NOT NULL colum n 'idrow' at row 1 With MySQL 4.1.11 I'd never get this error message, but it happens with the 5.0.15 version. Can anyone help me? Mysql 5 is much more picky on things you shouldnt have been able to do in the first place.. Just change idrow to auto_increment and stop doing max.. Its not needed. innodb properly handles auto_increment now. Jeff -- 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: Per-thread memory use question
On Fri, Aug 26, 2005 at 10:39:16AM +0300, Gleb Paharenko wrote: Hello. Have a look here: http://dev.mysql.com/doc/mysql/en/memory-use.html Hi, I had looked there, and other pages like it, but haven't found anything that says specifically whether threads free the 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 PROTECTED] wrote: Hi, This formula shows up in a few places (this is from http://dev.mysql.com/books/hpmysql-excerpts/ch06.html): min_memory_needed = global_buffers + (thread_buffers * max_connections) where thread_buffers includes the following: sort_buffer myisam_sort_buffer read_buffer join_buffer read_rnd_buffer My question is, once one of the buffers (e.g., sort_buffer) is needed by a thread, does the thread hold onto it in case it needs it again, or does the thread free it as soon as it can? I'm using 4.1.13. I'm trying to figure out an optimum value for max_connections. If the threads don't release their memory, then I really do have to account for the fact that each thread over time will probably be holding each of those buffers. If threads give up the memory as soon as 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Per-thread memory use question
Hi, This formula shows up in a few places (this is from http://dev.mysql.com/books/hpmysql-excerpts/ch06.html): min_memory_needed = global_buffers + (thread_buffers * max_connections) where thread_buffers includes the following: sort_buffer myisam_sort_buffer read_buffer join_buffer read_rnd_buffer My question is, once one of the buffers (e.g., sort_buffer) is needed by a thread, does the thread hold onto it in case it needs it again, or does the thread free it as soon as it can? I'm using 4.1.13. I'm trying to figure out an optimum value for max_connections. If the threads don't release their memory, then I really do have to account for the fact that each thread over time will probably be holding each of those buffers. If threads give up the memory as soon as 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 PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 threadID 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 appreciated, and please let me know if I can provide further information. See the Opteron HOWTO: http://hashmysql.org/index.php?title=Opteron_HOWTO Also.. are you running NPTL or Linux Threads? If you have the libc6-i686 package installed you have NPTL (not sure if the mysql binary needs support for this or not). I'd also highly recommend installing a glibc 2.3.2 which is what ships on debian. glibc-2.3.5 is in experimental and its what we're running. What a difference a library makes...that was it, thank you! I had read the Opteron HOWTO, and tried that library with another problem I was having and it hadn't made a difference, so I reverted to 2.3.2 and forgot to try it here. [To answer your other questions: NPTL, I don't think libc6-i686 is for 64-bit, and there was no disk i/o either.] Thanks again! --Pete Kevin -- Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. See irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- 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]
Problems with x86_64 mysql-standard-4.1.12
Hi, MySQL is not getting very far through make test on 64-bit Debian, MySQL 4.1.12. I've tried precompiled and self-compiled, and on two different machines, both of which have been in use for a long time and both of which run MySQL 4.0 (and its tests) without a problem. On one machine: ~/mysql-standard-4.1.12-unknown-linux-gnu-x86_64-glibc23/mysql-test: ./mysql-test-run Installing Test Databases Removing Stale Files Installing Master Databases running ../bin/mysqld --no-defaults --bootstrap --skip-grant-tables --basedir=.. --datadir=mysql-test/var/master-data --skip-innodb --skip-ndbcluster --skip-bdb Installing Slave Databases running ../bin/mysqld --no-defaults --bootstrap --skip-grant-tables --basedir=.. --datadir=mysql-test/var/slave-data --skip-innodb --skip-ndbcluster --skip-bdb Manager disabled, skipping manager start. Loading Standard Test Databases Starting Tests TESTRESULT --- alias [ pass ] alter_table[ pass ] analyse[ pass ] ansi [ pass ] archive[ pass ] and then it never comes back, presumably from the auto_increment test. If I run the auto_increment test alone (i.e., ./mysql-test-run auto_increment), it fails in this same way. When it's hung, mysqld isn't using any CPU. If I manually run the commands that constitute the auto_increment test on a running 4.1.12 server they complete, and the output appears normal to me. On another machine, make test gets as far as the delete test before hanging. The first machine doesn't successfully complete the delete test either, if run directly (i.e., ./mysql-test-run delete). The machines are running Debian amd64 (the standard archive), and are: Machine 1: Debian Sid, Athlon 3500+, 1GB ram. Kernel 2.6.12-rc4. Machine 2: Debian Sarge, Dual Opteron 248, 6GB ram. Production 4.0.x server, in use for six months. Kernel 2.6.11-ac7. 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 threadID 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 appreciated, and please let me know if I can provide further information. Thanks, -- Pete Harlan [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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. FWIW, IFNULL() does the same thing, with a clearer (to me) name, e.g., IFNULL(sum(qty), 0). --Pete -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trouble compiling 4.0.21 under amd64 Debian unstable
A heads-up and a call for advice for anyone compiling from source on Linux amd64: The latest Debian amd64 unstable upgrade of libc6-dev to version 2.3.2.ds1-17 broke compiling MySQL 4.0.21 from source using gcc-3.4. (Also breaks similarly with the default gcc for this platform, gcc-3.3.4.) Specifically, the comment at the top of /usr/include/pthread.h no longer begins with the word LinuxThreads, so configure no longer thinks LinuxThreads is there at all and the configure process stops. Guessing that it's just a comment change and not a capability change, I forced configure to believe LinuxThreads was there, and configured and compiled normally, but then make test quickly ran into a problem: TEST RESULT -- alias [ pass ] ./mysql-test-run: line 1119: 25903 Segmentation fault (core dumped) $@ $CUR_MYERR 21 (wd: /usr/src/mysql-4.0.21) alter_table[ fail ] Prior to the libc6-dev upgrade, configuration and compilation with gcc-3.4 worked smoothly, passed all tests, and has been working well for us. The MySQL binaries work for us. We were compiling with gcc-3.4 because the precompiled binaries section of the manual says it uses gcc-3.2.1 for amd64, and as I understand it gcc-3.4 handles the Opteron better than 3.2. Perhaps LinuxThreads really isn't there? Perhaps something else changed about threading? Maybe there's a more robust test for LinuxThreads than the comment in pthread.h? My configure line looks like: === # Comment these out to use default compiler. export CXX=g++-3.4 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 === Any advice appreciated. Thanks, -- Pete Harlan [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 glibc (that influence the threads behavior) but it is all up to 2.2.2. Not a word about 2.3.2 in the searchable manual at http://dev.mysql.com/doc/mysql/en/index.html Thanks! It would be nice if the manual were updated to say something about it, but here's the answer I got when I asked a similar question a few weeks ago. HTH, --Pete Egor Egorov [EMAIL PROTECTED] writes: The section of your manual describing what some of the glibc problems are is helpful; do you know if the linuxthreads-2.2.2 patch made it into glibc at some point? It's for 2.2.2, while we're running 2.2.5 (on Debian Stable) and 2.3.2 (on Debian Testing). No, thread stack size is still too big in latest 2.2.x glibc branch. :( It's not clear what's about thread stack size in glibc 2.3.x. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 use your customized library fixes? The section of your manual describing what some of the glibc problems are is helpful; do you know if the linuxthreads-2.2.2 patch made it into glibc at some point? It's for 2.2.2, while we're running 2.2.5 (on Debian Stable) and 2.3.2 (on Debian Testing). We're moving to 64-bit soon, primarily to be able to run MySQL with more RAM. Thanks, --Pete -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 constraint on it. In the table definition that column has a default NULL specification too. So, when I ran the query select * from table where col is NULL; I get only 1 row returned, when I have 23 such rows with NULL values. This might be because of the Unique Key constraint. Is there a query that will return all those 23 rows. Thanks, Deepak -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 ; For some reason which I am not seeing, this query is not doing what it should be doing. Does anyone have any suggestions? For starters your between syntax isn't correct (but is parsable in ways you didn't want). You probably want: select * fromroutes, users where area = '$area' and style = '$style'and rating between '$rating1' and '$rating2' group by route order by rating As others have pointed out, your ratings aren't something MySQL will know how to order. That's a separate problem (and more difficult to solve), but the between syntax is also one. --Pete -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 recommend that you use the binaries we provide. Is it really true? Absolutely. There are too many ways to build MySQL so that it will not work well... buggy compilers, thread stack size, etc. Especially this applies to Linux, because MySQL build is statically linked with a bit patched glibc version. And glibc compilation by itself is tricky. While I'm sure this is true, for the last 6+ years we've essentially always compiled our own MySQLs under Debian and never had a problem that I wasn't able to reproduce with the precompiled version. [I say essentially always because we did use a precompiled version for a while once, but it had a bug that went away when we compiled it ourselves. That was years ago and was a rare case where the precompiled version did have a problem with its static libs that was disovered by MySQL and fixed shortly thereafter.] I'm not saying this to contradict the good folks at MySQL or to disparage their fine binary builds, only to say that their (and Debian's) software is so good that in my experience you shouldn't be *that* worried about compiling it yourself. (Of course, if you have problems and expect any help with it, paying for support sounds like a good idea.) --Pete So if you need rock stability on Linux and still need to have a custom built binary you can subscribe to Primary support or Login installation packages. Both include custom binaries build. Click on the link below to learn more. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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; I think you'll never be able to do it. The stuff after the SELECT is calculated based on the rows selected by the WHERE. The WHERE therefore cannot be based on the stuff after the SELECT. The parser has seen the 'as', though, and could expand it in the where clause so the user doesn't have to do it (and do it correctly, and maintain it in parallel). The problem is MySQL can't just start doing this without breaking queries that depend on it not happening. (If the 'as' alias is the same as a field name, for example.) So I doubt it will happen, but not because it couldn't be done. --Pete -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 MySQL with InnoDB and MyISAM tables in ta Linux RH system. So, do I need to care about the Linux file size limitation of 2 Mb? Or MySQL deal this situation with Linux FS? In other words, will my MySQL stop working when the database get major then 2 Mb? Or such situation won?t happen? thanks Ronan -- 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: 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, Mar 25, 2004 at 11:52:47PM +0100, Mark wrote: Hello, I am long since using MySQL 3.23.58; and it works perfectly. In fact, it works so well, that I have always been very hesitant to upgrade to the 4.x series. Especially, since 4.x, a year ago or so, was still rather unstable. But I like the new query-cache. So, I wonder, how stable is 4.0.18 really? (compared to 3.23.58). Since I read that I need to upgrade DBD:mysql for all Perl clients as well, I am not too keen to just give it a whirl, only to find out I upgraded to a potential lemon. Counting on an honest assessment (and not the promo rap), how stable is it, really? Can I risk upgrading? Or is it still too flaky? I run a news server on it, and server mail client databases; so I really cannot afford an unstable product. Thanks, - Mark -- 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: 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 query' on both machines; the output should be the same. 4.0.1 isn't the latest, so I assume you're using something later. Regardless, I wouldn't expect the performance on the two machines to be so different unless the one has so much ram that everything is running from memory while the other has to thrash the disk. --Pete On Sun, Mar 21, 2004 at 11:05:49AM -0800, Gene H. Dreher wrote: I've got 2 Redhat 8 machines with 4.0.1 (? latest) database. Machine1 is a P3-750, Machine2 is a P4-1.6 .. The same database is deployed to both machines using myisam tables. Query on Machine 1 takes almost 2 min to produce data. Same query on Machine2 returns in 5 seconds... ? Why? (I don't expect the same, but close would be nice.) thanks, ghd -- 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: *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 you sure that your ssh tunnel is also listening on localhost (i.e. 127.0.0.1)? i once had a problem like that with my apache, and it turned out that it only listened on 'real' IPs Yeah, the tunnels work perfectly if I do this: mysql -u root -p -P 3306 -h 127.0.0.1 database_name Blazing fast (same subnet), almost as fast as a local connection. The server load on the client end dropped like a rock too when I stopped the local MySQL copy (the tunnel to the remote MySQL is what I want to use :). It's just that the scripts and client aren't poking 127.0.0.1 via TCP/IP -- they check sockets and die when the socket isn't there. cheers and good luck, Thanks! -- 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: 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 which machines they are coming from and which users, and that should help you track down who's holding connections open. --Pete On Wed, Nov 12, 2003 at 01:15:54AM +0100, Henrik Skotth wrote: Hello! I have tested this now, and that isn't the case. Any other ideas? -- Henrik Michael McTernan skrev: Hi, Have you tried netstat -a on the box with the MySQL server? This command (Linux) will show what is connected to where, and will help you double check that there really aren't any open connections to the server. Thanks, Mike -Original Message- From: Henrik Skotth [mailto:[EMAIL PROTECTED] Sent: 10 November 2003 18:54 To: [EMAIL PROTECTED] Subject: Re: max_user_connections problem after upgrading That's new for 4.0? Hadn't noticed it... Anyway, it's set to 0 so there is no limit to exceed. Also, we aren't getting the error messages ALL the time, they start to appear after a day or two and gets more and more frequent untill I restart mysql. Any other ideas? -- Henrik gerald_clark skrev: Are you sure you are net exceeding the setting for maximum connections per hour for that user? Henrik Skotth wrote: Hi! What I meant was that even if there are currently only two user connections being used, and the limit is 300, we still get the already more than max_user_connections error... -- Henrik gerald_clark skrev: Henrik Skotth wrote: Hello all, We recently upgraded to 4.0, it went very well and the performance gains have been great. But now the server has started to act strangely. Every few days, the server starts to refuse connections, saying that there is already more than max_user_connections, but there is really only one or two active connections and our max_user_connections is 300. I have to take down and restart the server to solve the problem, and it keeps happening over and over again every few days... Am I the only one having this problem? Any suggestions? Regards, -- Henrik Skotth, Hogwarts.nu Are there 298 or 299 inactive connections? If so, why are they not being closed? -- 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] -- 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] -- 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: 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 [free] RAM, that the OS will cache the file data and there is no need for a RAM disk. I don't really see how performance would be any different than using a RAM disk. Either way, you will still have the overhead of the filesystem calls, even if data isn't actually read from disk, unlike with a HEAP table. Just because a file is in the cache now, it doesn't follow that it will be in the cache 5 minutes from now. If it gets thrown out of the FS cache, you don't have enough RAM, and then a ramdisk won't help much. A ramdisk can help even when you have enough RAM. Sometimes the OS isn't smart enough to know not to cache something that doesn't need caching, and can clear out data you wouldn't want it clearing out. Perhaps someone does a full table scan of a large table once in a while, or whatnot. (Maybe someone copies a db backup file from the machine, and in the process wipes out the cache.) A ramdisk makes sure the disk is never hit, effectively locking the pages in a type of cache. (Hopefully OSs are smart enough not to further cache ramdisk pages.) I haven't used a ramdisk in a db server yet; I don't have any one table that simply MUST be read out of memory every time, and the OS does a pretty good job of managing things. YMMV. --Pete -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 large files for the past eight(?) years. On 32 bit architectures the kernel and libc have been handling large files on ext2 for at least two years. I hate to keep posting the same thing to this list, but I keep seeing the same misinformation that ext2 can't handle large files. It can. Cheers, --Pete -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 larger files. The 2GB filesize limit was due to glibc and the linux kernel, not the ext2 filesystem. Any linux distro from the past year or so should be able to handle 2GB files on any filesystem. Hardware raid is invisible to Linux, so won't affect the maximum usable filesize. --Pete -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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, --Pete On Tue, Sep 23, 2003 at 04:54:03PM +0300, Director General: NEFACOMP wrote: Can you run this query and tell me if it works on your machine? SELECT (@var := @var + 1) AS Number, * FROM any_table_with_records; As per the manual, this should give something like: +-- NumberFieldOne 1Value 2Value 3Value ... But when I run it the error is :You have an error in your SQL syntax near 'NULL @var + 1) AS Number BLAH BLAH BLAH I have read everything about Using Variables (in the manual) and I apply what it says but it can't run. Even the simple example given by MySQL This is the example MySQL has given in the manual: SELECT @t1:=(@t2:=1)[EMAIL PROTECTED]:=4,@t1,@t2,@t3; When I run this query, the error message is: You have an error in your SQL syntax near '(@t2NULL1)[EMAIL PROTECTED],@t1,@t2,@t3' at line 1 Where do you think these NULL stuffs are coming from? Do I have to change the character set? Thanks Emery - Original Message - From: Petr Vileta [EMAIL PROTECTED] To: Director General: NEFACOMP [EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 01:45 Subject: Re: Using SQL variables Can someone tell me what's wrong with my query? My Query was: SELECT (@num := @num + 1) as RecNum, AnotherField FROM tbl WHERE condition. When I change the := into =, the query returns results with a non changing RecNum. Because = mean some IS EQUIAL to another but := mean SET MYSQL VARIABLE to some value Consult your MySQL manual and look for Using variables :-) Petr Vileta, Czech republic -- 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: 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 List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgraded 3.23 to 4.0: No problems at all.
You guys are great! Thanks! :) We compile from source, under Debian, and the transition from 3.23.xx to 4.0.13 was perfectly seamless. I've never had an upgrade go more smoothly. There are so many messages about bumps on the road when people upgrade from a vendor's packaged version 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 0=1; ? I need this because PHP throws an error when an empty statement (or just a ';') is passed to MySQL. SELECT 1; ? -- That returns a row, though. :-) SET @ignore_me = 0; ? --Pete -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 ANALYZE TABLE is done (and stored) when creating a non-unique index, but not when creating a unique one. Tested in 4.0.12 and 3.23.56. I have a simple test case if needed. (There's way too much data in the test case to present it here.) --Pete On Thu, Mar 27, 2003 at 08:39:41PM -0600, wrote: 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 non-unique and it's fast. The EXPLAINs look the same for both. Is this to be expected under some circumstances, or do I get to isolate this messy situation (it's an ugly query) for a bug report? MySQL-3.23.55, Linux, MyISAM. Thanks, --Pete -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 non-unique and it's fast. The EXPLAINs look the same for both. Is this to be expected under some circumstances, or do I get to isolate this messy situation (it's an ugly query) for a bug report? MySQL-3.23.55, Linux, MyISAM. Thanks, --Pete -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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): % ssh -n -N -L 3307:foo.bar.com:3306 foo.bar.com I then try to connect from the local machine with: % mysql -h localhost -p 3307 --user=me --password But after prompting me for my password, MySQL denies me access. What if you use 127.0.0.1 instead of localhost? Jeremy And what he's referring to is that the mysql client library will connect using the local socket if you say -h localhost, even though you specified a port. The port argument will be silently ignored. For some reason the developers consider this a feature. If you want to connect to localhost via TCP/IP, you have to specify the host by IP address (127.0.0.1), not as localhost. The developers pointed out to me (two years ago, when I submitted a patch to fix this bug) that what you'd really want to do is tunnel the *socket* from one machine to the other, not the port. If you tunnel the port, you have to be sure to block access to that port from the outside, because outside connections to that port (on the tunneling client) will be tunneled too (under Linux anyway; I didn't try it elsewhere). That's potentially dangerous and not completely obvious side-effect of tunneling SSH ports. SSH doesn't allow tunneling sockets unfortunately. --Pete - 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
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 specify the port, you should use TCP/IP even if you specify -h localhost. That is, if the user specifies both, it's ambiguous what the user really wants. But localhost is a DNS name that only happens to resolve to 127.0.0.1. MySQL breaks this DNS abstraction by treating localhost as a keyword, different from 127.0.0.1, which goes against the principle of least surprise. (It confused me, anyway.) I think it would have been less confusing to treat localhost the same as its resolved IP address, and decide whether to use a local socket vs. a port number on the basis of a different argument (-l for local, perhaps, which would be the default when connecting to 127.0.0.1 (or localhost, or any other name that resolved to this IP)). Wild speculation: The current design was chosen back when MySQL didn't have different sections in /etc/my.cnf, so the port specification for the server was also used for the client. In that case the client usually read a port specification (in /etc/my.cnf), but in the common case the client wouldn't obey it, which typically did the right thing. (Now that there are separate [client] and [server] sections in my.cnf things are less jumbled.) The client could be made to obey a command-line argument, while still silently ignoring the my.cnf argument. (That's what my patch did.) It's not just the mysql client, it's the client library, which is what bit us. You specify a port in the DBI-connect() routine as something like ';host=localhost;port=2000' and it's silently ignored there too. I'm very glad that ssh no longer (as of three years ago!) forwards externally-connected-to ports by default. --Pete - 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
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 time it operates on a filename. As to why you're hitting a wall, perhaps you're blowing through a cache in MySQL or the kernel. Check number of allowed open files (ulimit -n), and whatever tweaks there are in the kernel (in /proc probably) for size of directory caches (can't help you there but I'm sure someone can). --Pete On Tue, Mar 04, 2003 at 09:04:20PM -0500, Gary Huntress wrote: Greetings, I've been running a public MySQL server for a couple of years that now supports several thousand users. I used to run this on a lowly PII-350 and it ran quite fine with CPU loads usually hanging well under 0.20. The only hitch was the time necessary to connect seemed to jump up to a few seconds when the number of databases exceeded 2000 or so. It appeared to not be linear, performance was good below 2000 databases and the connection time went to a couple of seconds (as measured by a trivial php page that makes a connection). I suspected that the connection speed was an ext2 filesystem bottleneck when it had to deal with the large number of files in the mysql data directory. I modernised a few months ago to an athlon 1.3GHz with the same ram and a faster hard drive. I also upgraded to Linux 7.3 using an ext3 filesystem. I thought that ext3 might handle a large number of files better, and I also thought that the faster box and newer kernel would allow me to have more databases on one server without suffering the same connection speed penalty. However, I seem to have hit this same wall at roughly the same number of DBs (closer to 3000 this time) To summarize, it appears to me that there is a reasonable upper bound on the number of databases without suffering connection performance, probably limited by the filesystem. My questions are 1) Are these observations expected? (I was a bit disappointed that the new box didn't perform better...the cpu load is usually idle) 2) Is there a way to mitigate this at the linux admin level? (kernel parameters? ext3 parameters? change to reiserfs? 3) Finally, are there MySQL parameters that can be tuned to enhance performance for a large number of databases (note, not concurrent users...that is a small number) Regards, Gary SuperID Huntress === FreeSQL.org offering free database hosting to developers Visit http://www.freesql.org - 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 - 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
Re: Row numbers
On Fri, Feb 14, 2003 at 01:04:00PM -0500, Luc Foisy wrote: I didn't say it had nothing to do with the data, I said it had nothing to do with the data in the database. The data in the recordset returned from the database referenced by row is important. [...] 1. There are a lot of functions that return values that have nothing to do with data contained in the database. Math functions for one, they calculate return values using data that is in the database. And many many other functions. Why not one more. You're not just asking for a function whose input doesn't come from the database, you're asking for one whose input comes from a later pass in data processing. After the row is inserted into the final result set, this value has to be updated. In the 4.x series the developers implemented SQL_CALC_FOUND_ROWS and FOUND_ROWS(), something that could just have easily been handled by external programming... I'm sure they weighed the utility of the change (saves a lot of traffic and time, when you need it) against the complexity of its implementation (probably very little, given what it does) and decided it was worth it. Every addition to a language or library has to be made very carefully because they generally will be there forever. The function you describe requires a row to be updated where currently there is no updating going on, adding complexity where there is none now. 2. There is a use for it. since... 3. Going through the archives, I have seen many many people ask for it. I've seen people ask for a ROWNUM() function or somesuch, which reveals the implementation's ordering of records in the source table; that request shows up once in a while (and usually reveals a misunderstanding of rdbms, imo). But in the result set? We're talking about $resultNum++ as you loop through the results. (Or the @x solutions other people have mentioned.) I've never heard someone ask for the functionality you want before. Even if there were many such requests, assigning serial numbers to sequential output, if that's what you want, is what general purpose languages are for, not what relational databases are for. Imo. Extreme example: Why not add SELECT ... INTO EMAIL [EMAIL PROTECTED] to the language so the server can email you results? (You may find examples where the folks at MySQL have chosen to add non-rdbms features into their server, but that doesn't mean they want to add anything whatsoever and then maintain it forever.) 4. Those numbers probably already exist, how else does it ORDER BY, it 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, 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
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 `FORCE' a reserved word. Would it be possible then to choose a naming convention for MySQL extensions that is less likely to cause a conflict? Something like __FORCE, FORCE_INDEX, or even MYSQL_FORCE (or MYSQL(FORCE))? (Assuming FORCE this isn't standard SQL.) You could then add new extensions without worrying about breaking existing programs. I had to change a lot of code when WHEN became a reserved word (it makes a good name for a timestamp-type field), and it wasn't pleasant. (Though that in that 'case' it was a standard sql reserved word iirc.) I don't have any fields named force, but surely someone does. Just a thought. Thanks for the great sql, database :) --Pete - 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
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 it the right way. You seem to disagree about which is better, but I believe the above is the explanation for the switch in behavior. As for why the MyISAM/InnoDB way is right, I believe it has to do with the idea that autoincrement values are very commonly used in situations where you want not just a key that's unique in the table, but one that will never be used again. Specifically, in ISAM tables, when you delete the highest-keyed record, that key will be given out again with the next insert. Depending on how your application is written, that could lead to unpleasant race conditions that would be easily avoided by simply not reusing key values at all. --Pete sql, db - 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
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 are fields or constants.
(It might not use your indexes there, or be the best way to do it...
The below looks better to me, or use a join table to do it really
properly. But in answer to your question, the 'reverse' of LIKE is
LIKE with its arguments reversed.)
--Pete
On Mon, Oct 14, 2002 at 05:26:13PM -0400, Keith C. Ivey wrote:
On 14 Oct 2002, at 20:29, Tim Kerch wrote:
For example, I have a string
Administration,Advertising,Direction,Media,Research and I want to see
whether a SECTOR field in a row is contained in the above string.
You could construct a query something like this:
SELECT * FROM table_name WHERE sector IN ('Administration',
'Advertising', 'Direction', 'Media', 'Research');
sql
-
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
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 file. Maybe they're not used during the operation of the db...? I don't know about that. --Pete sql,query - 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
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, which would typically settle back down by itself after ten or fifteen minutes of (seemingly) nothing progressing. The MySQL guys have said they think it was a version of glibc that they linked with; compiling your own from source may fix the problem. I'd be interested to know if it does. OTOH, in the five or six years I've used MySQL, this is the first time behavior like that was the fault of the server---it's almost always something in our code instead. InnoDB is supposed to reduce pathalogical behavior in a running system, so you might try that. The above-described problem with the MySQL binaries happened with either table type. Good luck, --Pete On Wed, Sep 25, 2002 at 09:24:02AM -0700, Andrew Maltsev wrote: On Wed, Sep 25, 2002 at 10:02:52AM -0500, Philip Molter wrote: On Tue, Sep 24, 2002 at 08:21:36PM -0700, Andrew Maltsev wrote: : Any suggestions how to approach the problem? How and what to test? It : happens randomly, can work for a day or two with no problems and then : hang three times in one hour. And obviously I can't reproduce it in my : test environment however hard I stress test it. When it happens, what does the system look like? Is CPU pegged? Is MySQL using a lot of CPU. What does iostat tell you in terms of drive activity. Are the drives actively seeking or does the system seem relatively quiet? You'll have to do *some* troubleshooting. Sad thing is the system usually gets rebooted by support personel before I can get my hands on it because long downtime is not acceptable. I've seen it first hand only once and had just about a minute to look at it. It is not swapping, the system has adequate amount of memory and generally there is no significant disk activity, no disk bound processes at all. Show processlist responds with one process being in writing to network state and others sleeping. But killing it does not work and even killing the MySQL itself with SIGTERM does not work either, it has to be killed with -9 and then some indexes are corrupted and need myisamchk. Well, I got my answer I guess -- it was a long shot, I kind of hoped that somebody would respond with something like I get the same damn thing with 3.23.52 all the time, try x.xx.xx. As it is not the case I'll have to try to investigate it myself :) Andrew. - 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 - 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
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/ext3 to
help this problem, or you could use a different filesystem, such as
ReiserFS that indexes directories out of the box. In an environment
where directory accesses far outnumber directory modifications, you
should see a good improvement if you have 20K files in the directory.
('ls' slows down for reasons besides reading the directory; it usually
sorts its output, and it's often set up to guess file type for each of
the files. Turn those off and it usually goes quite quickly.)
--Pete
-
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
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 perl-script which controls whether master- and replication-tables are equal showed me, that a master-table was missing on both slaves (two backups are better than one :-) ) When I controlled the master update-log I saw that the master-table was renamed with the following command: ALTER TABLE ad_429_t RENAME adprj_7; This command was missing in the slave-updatelogs. The sql-statements before and right after the missing one were present and no error was written down in the mysql-error-log. Trying to find out whether the Rename-Statement works right or not I did some tests with renaming a table but all actions were performed on the slaves too. Has someone experienced the same? I'm now a bit unsure whether the replication is working right. Greetings from Germany Lutz Maibach - 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 - 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
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 in small batches. The time when a physical log file becomes full does not affect this continuous background checkpointing activity, since InnoDB sees all the log files as one catenated log file. That's good to know---so that isn't it. The pauses you experience are probably caused by high load in general, probably too much disk i/o. You should study your queries, use the InnoDB Monitor, watch 'top', adjust buffer pool size, spread disk i/o. Respectfully, I don't think that's it. Both times I've seen the database do this it's been in periods of relatively low traffic. Until we switched to InnoDB tables, the only times the database was frozen up like this was when someone did an ill-advised select that took forever and gummed everything up. That's the main reason we switched to InnoDB tables---so a long select wouldn't hold up the db (by an update blocking, which would block subsequent selects). In this case, when the (inno-)db was 'pausing', all of the queries in the (growing) queue looked normal. And there was a lot of free ram. I didn't see anything useful in the InnoDB monitor, but then I'm new to reading it. Then all of a sudden all queries finished and there were no more in the queue and the problem didn't recur for five days. I thought it must be related to the log files, because when I looked at them it looked like one was just finished being written to. (Because its modification time was the previous minute, while the next (zeroth) log file was now the active one.) I know without a repeatable test case, there's not much you can do. If I get more information I'll let you know. It helps to hear that this behavior isn't normal, though. --Pete sql - 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
InnoDB: Looong pause when log file is full?
Hi, I've read the performance tuning tips for InnoDB (http://www.innodb.com/ibman.html#InnoDB_tuning), but am getting bit when the log files are full and the buffer pool is checkpointed. By 'geting bit', I mean for several minutes the db server basically stops, and our website stops serving pages. Does anyone have advice 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? Our log files are on the same raid array as the data, but would it really make that much difference to move them to 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-i686.tar.gz. 2. Linux 2.4.19-rc3, though has happened with other kernels. Debian woody. 3. Dual Intel 933mhz, 2gb ram, 15krpm scsi raid. Here's the /etc/my.cnf file we use. Except for this pausing, performance is generally great, and we're not disk bound or running out of ram (500mb free (used for caching) while machine is pausing): /etc/my.cnf: [mysqld] log-slow-queries skip-locking innodb_data_home_dir = /usr/local/mysql/data/innodb/data innodb_data_file_path = ibdata1:2000M;ibdata2:2000M;ibdata3:50M:autoextend set-variable = innodb_buffer_pool_size = 1200M set-variable = innodb_additional_mem_pool_size = 20M innodb_log_group_home_dir = /usr/local/mysql/data/innodb/logs innodb_log_arch_dir = /usr/local/mysql/data/innodb/logs innodb_log_archive = 0 set-variable = innodb_log_files_in_group = 3 set-variable = innodb_log_file_size = 150M set-variable = innodb_log_buffer_size = 4M innodb_flush_log_at_trx_commit = 0 set-variable = innodb_file_io_threads = 4 set-variable = innodb_lock_wait_timeout = 50 set-variable = record_buffer = 1M set-variable = sort_buffer = 2M set-variable = key_buffer=150M set-variable = tmp_table_size=4M set-variable = table_cache=500 set-variable = myisam_sort_buffer_size=64M set-variable = thread_cache=8 set-variable = thread_concurrency=4 default-table-type=InnoDB set-variable = long_query_time=5 log-bin server-id=1 - 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
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 matter how many files you have. InnoDB sees them as one striped file anyway. That's good to know. You could increase the size of your logs. That'll increase recovery time if there's ever a crash, but it should give InnoDB more breathing room. Won't that only make for fewer long pauses, rather than schedule or shorten them? It won't fly with the powers that be if our system shuts down in the middle of the day and my response is Oh yeah, our server does that every three weeks and there's nothing we can do about it. Someone else suggested setting innodb_flush_log_at_trx_commit to 1, which should make for many frequent flushes rather than one monster occasional flush. Would that not take care of it? (I set it to 0 because the manual suggested that as a big performance improvement at the expense of a small measure of recoverability in the unlikely event of a crash.) [Is there a way to set that variable with 3.23.51 without shutting down the server? I couldn't find a way.] Thanks, --Pete sql,query - 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
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 or an active transaction 020730 14:53:49 Slave: error running query 'rename table foo to foobar' 020730 14:53:49 Error running query, slave aborted. Fix the problem, and re-start the slave thread with mysqladmin start-slave. We stopped at log 'db-bin.003' position 1024192196 020730 14:53:49 Slave thread exiting, replication stopped in log 'db-bin.003' at position 1024192196 I type mysqladmin start-slave as it suggests, and replication picks up where it left off: 020730 14:54:27 Slave: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'db-bin.003' at position 1024192196 I can reproduce the problem with (on the master): create table foo (a int); rename table foo to foobar; Then the slave dies. Is this a known problem? I couldn't find anything at google or the list archives, but I've been off the list for a while. Is there a workaround? Additionally, table renames seem to be outside the transaction space. So if I do: set autocommit = 0; begin; rename table foobar to foo; rollback; the rename is done anyway, and seen by other clients immediately after the rename command. Is this normal? Thanks for any help, --Pete - 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
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's not unreasonable to expect that if the database says a field is a certain width, then you won't get data wider than that. Sure, someone inserted data into the database that was too wide. But for the database to give that data back, wider than it says is allowed, is a plain ol' bug. --Pete - 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
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 INTO services
SET friendly = ?,
parent = ?,
intentional = ?,
address = INET_ATON(?),
port = ?,
timeout = ?,
priority = ?
};
$dbh-do ($sql, undef,
$friendly, $parent, $intentional, $address,
$port, $timeout, $priority);
-
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
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 column type? you can then format the date accordingly with...uh...DATE_FORMAT() in your select statements... hth. -ravi -Original Message- From: Richard Bates [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 26, 2001 3:00 PM To: [EMAIL PROTECTED] Subject: Creating Table with a Default Datetime field How do I get a default date time in the create table clause... I have tried Create table( CreationDateDATETIMEDEFAULT NOW(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT NOW(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT SYSDATE(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT SYSDATE(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT CURRENT_DATE(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT CURRENT_DATE(), Infovarchar(100) ); All without the quote return with invalid syntax. With the quote inserts 000-00-00 00:00:00 meaining invalid date. == === Richard Bates === TELEHOUSE America == - 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 - 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 - 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
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...] But my problem is perhaps simply in the concept: When MySQL accept a create table in entry (as bad as it is like this one from a guy in my team), mysqldump should regenerate from this table a create table statement accepted by MySQL too. no? it's not logic? ;-) Regards, Le Mon, Jun 18, 2001 at 09:34:27AM -0400, John Cichy a _crit: Sinisa, A good way to prove that mysqldump is doing what it should, might be to do a describe TESTBADDUMP; I think this will prove that mysqldump is just dumping what is in the table def, which is all you can expect. Have a great day... John On Monday 18 June 2001 09:46, Sinisa Milivojevic wrote: Guillaume Fougnies writes: ok so it's a failure from mysqldump. I know this declaration is not really correct but mysqldump must regenerate a correct sql statement (good when pipe in mysql client i mean at least ;) ). It's not the case. Regards, -- Guillaume FOUGNIES Research Development Profile For You Ltd. No, it is not mysqldump failure. If you specify integers where literals are expected, then it is your fault. Use quotes and a problem will dissapear. Regards, -- For technical support contracts, go to https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - 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 -- Have a great day... John -- Guillaume FOUGNIES Research Development Profile For You Ltd. - 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 - 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
Re: Re: Plase Let us know if there is a Racialism for Mysql productand
Why not change the sql documentation to say: windows-directory\my.ini (e.g., c:\windows\my.ini) Global options instead of: windows-system-directory\my.ini Global options It's understandable why the author might have originally thought to write it that way, but to defend it on the grounds 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. -- Pete Harlan [EMAIL PROTECTED] - 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
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 even be before the call to mysqldump. --Pete mysqldump --flush-logs --add-drop-table -u root -p markloky shuncheong /backup/shuncheong.sql See if that works. The way you had it written, you weren't passing the -u and -p options to mysqldump. So, mysqldump was connecting as an unprivileged user. On 4/2/01 9:08 AM, "Mark Lo (3)" [EMAIL PROTECTED] wrote: Hi, I would like to know how to backup mysql using crontab or cron jog. I have add a line in my crontab file : mysqldump --flush-logs --add-drop-table shuncheong /backup/shuncheong.sql -u root -pmarkloky; but I got nothing in shuncheong.sql file. Thank you for your help Mark Lo - 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
Re: am I alone? (scale)
We've had fairly bad luck with Linux 2.2.X and SMP for any kind of high-end system. The DAC 960 SCSI controllers proved to be fairly problematic in this environment. Sometimes updating the drivers helps. We have a couple of systems that run much better when we do not use one of the CPUs. Just to offer another data point, we've had great luck with SMP Linux 2.2.x and the DAC 960 controller (a Mylex AcceleRAID 352). Debian Potato Linux 2.2.17 SMP, up 134 days. MySQL 3.23.32 up 39 days, Queries/second avg: 753. It's a dual 933 PIII w/2gb ram. No 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 lack of hammering on it. -- Pete Harlan [EMAIL PROTECTED] - 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
Re: Quoting numbers? (was Re:MySQL Tables)
Nah! I think I'd prefer to do it myself and have the compiler/interpreter
come up saying "hey buddy, what ya trying to do? This ain't no number"
rather than it changing the number into string and happily continue on
running through the rest of the programming. Takes you longer to figure out
you used the letter "O" instead of the number 0.
Strong typing and weak typing each have their merits.
When MySQL compares numbers and strings, it does so by converting the
string to a number, rather than the number to a string (which would
seem more natural in a type-heirarchy sense, but would be confusing).
So my question is, if it 'demotes' strings to numbers when comparing
them with or assigning them to numeric fields, then why would it ever
make a difference to quote numeric literals?
And in practice, it has appeared to me to make no difference. (Until
some recent bug report regarding bigints where it did appear to make a
difference.)
It was pointed out to me that there is an efficiency difference:
select benchmark(100, 1+1);= 0.34 sec
select benchmark(100, 1+'1'); = 1.81 sec
which, though true, would usually not matter.
My personal preference would be that if it could ever matter to quote
numeric literals, then disallow that (or at least provide a switch
that disallows it (and one that only warns), so as not to break a lot
of code). If the policy is that it should 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 most inconvenient. There is
always a conversion somewhere in the code even if it is not
visible.
-Original Message-
From: Rolf Hopkins [SMTP:[EMAIL PROTECTED]]
Sent: 23 February 2001 02:09
To: Julian Strickland; [EMAIL PROTECTED]
Subject: Re: Quoting numbers? (was Re:MySQL Tables)
That's very true but these days, some languages/databases allow
for strings to be assigned to numbers and vice versa without the
need for conversion. PHP is one such language, not that I'm
bagging it or anything as I use it myself. I just call it bad
type checking.
- Original Message -
From: "Julian Strickland" [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, February 22, 2001 22:35
Subject: RE: Quoting numbers? (was Re:MySQL Tables)
It's all to do with data types, traditionally and across most
languages quotes are used to delimit STRINGS and a string is
NOT a number although may represent one when displayed.
-----Original Message-
From: Pete Harlan [SMTP:[EMAIL PROTECTED]]
Sent: 21 February 2001 20:51
To: [EMAIL PROTECTED]
Subject: 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 of, say, a salesman_id, but just
reads/displays/stores it in the database.
Aside from the fact that leaving them off is possible, is
there a standards/compatibility/other reason to do so?
(An example of a good reason not to use them would be if the
db engine weren't smart enough to use an index when you say
select * from table_name where numeric_key = '1234'
but possibly (probably?) all dbms's are that smart.)
--Pete
-
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
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 of, say, a salesman_id, but just reads/displays/stores it in the database. Aside from the fact that leaving them off is possible, is there a standards/compatibility/other reason to do so? (An example of a good reason not to use them would be if the db engine weren't smart enough to use an index when you say select * from table_name where numeric_key = '1234' but possibly (probably?) all dbms's are that smart.) --Pete - 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
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 DNS hostname or the external IP address, the connection will go through that external interface, which will be slower than using the loopback interface (I believe). As long as you use any other name for the loopback interface than 'localhost' you're okay. The most natural is to use '127.0.0.1' as the hostname. I've got a patch that will make mysql use a --port argument if there is no --socket argument specified, even for localhost, but it's not popular and it's not getting into the official sources. Let me know if you want it. (I posted two versions to this list, but the current patch is better than those.) (The patch is undesirable because it doesn't totally solve the problem when used with many my.cnf configurations, including the default ones.) Monty has pointed out that if you tunnel through ssh using port-forwarding like that: ssh -L 3306:localhost:3306 [EMAIL PROTECTED] sleep 10m then other machines can follow the tunnel if they connect to your port 3306. If you don't wish for that to happen, you should block that port from outside access with firewalling. (He also pointed out that ssh could be patched to forward a socket connection through the tunnel, which would be faster than using a port as well as more naturally secure.) Hope this helps, --Pete - 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
