RE: mysql-server-5.1.19 path variable error set on compile
Hi, Set this variable at command line or in your my.cnf. Geoffroy. -Message d'origine- De : David Southwell [mailto:[EMAIL PROTECTED] Envoyé : dimanche 17 juin 2007 17:33 À : mysql@lists.mysql.com Objet : mysql-server-5.1.19 path variable error set on compile # uname -a 6.1-RELEASE FreeBSD 6.1-RELEASE #0: Sun May 7 04:15:57 UTC 2006 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/SMP amd64 # Just upgraded mysql to include: # pkg_info |grep mysql mysql-client-5.1.19 Multithreaded SQL database (client) mysql-server-5.1.19 Multithreaded SQL database (server) p5-DBD-mysql51-4.005 MySQL 5.1 driver for the Perl5 Database Interface (DBI) php5-mysql-5.2.3The mysql shared extension for php # Seems we have an oddity here after compiling: Extract from show variables: show variables; +-+---+ | Variable_name | Value | +-+---+ | basedir | /usr/local/ | character_sets_dir | /usr/local/share/mysql/charsets/ | | datadir | /usr2/datadb/ | | general_log_file| /usr2/datadb/dns1.log | | language| /usr/local/share/mysql/english/ | | pid_file| /usr2/datadb//dns1.vizion2000.net.pid | ^^Why '//' | plugin_dir | /usr/local/lib/mysql | | slow_query_log_file | /usr2/datadb/dns1-slow.log| | socket | /tmp/mysql.sock | So I thought I would: (a) RESET the variable mysql set pid_file = /usr2/datadb/dns1.vizion2000.net.pid ; ERROR 1193 (HY000): Unknown system variable 'pid_file' mysql set GLOBAL pid_file = /usr2/datadb/dns1.vizion2000.net.pid ; ERROR 1193 (HY000): Unknown system variable 'pid_file' mysql set GLOBAL pid_file = '/usr2/datadb/dns1.vizion2000.net.pid' ; ERROR 1193 (HY000): Unknown system variable 'pid_file' mysql set @@GLOBAL pid_file = '/usr2/datadb/dns1.vizion2000.net.pid' ; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'pid_file = '/usr2/datadb/dns1.vizion2000.net.pid'' at line 1 mysql set @@GLOBAL.pid_file = '/usr2/datadb/dns1.vizion2000.net.pid' ; ERROR 1193 (HY000): Unknown system variable 'pid_file' mysql But I cannot seem to get the Syntax right - can someone please point me in the right direction. (b) Ask why this might be happening. It might be worth recording that /usr2/ is a seperate physical device and emphasize that the base-dir is /usr/local/. Thanks in advance 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]
Re: Can I undelete a dropped MyISAM table?
2007/6/16, mos [EMAIL PROTECTED]: At 09:39 AM 6/16/2007, Stanley wrote: Is it possible to undelete a dropped MyISAM table? Nope, not from within MySQL. Your operating system might allow you to undelete the file then you might have a chance of importing the data to a new table. Mike If your have a backup of your file system, you have only to restore those file in the mysql datadir : yourtable.FRM, yourtable.MYD, yourtable.MYI. No need to importing data to a new table. MySQL will see it as it were before dropping it. This is a notable difference between myisam and innodb for instance. Regards, Geoffroy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Installation problems with MySql 5.0.41 (source distribution)
Hi, You're running MySQL with a mysql linux user, verify that he have write access on mysql datadir. You should specify it with --datadir= at command line invoking mysql_install_db too. 2007/6/16, [EMAIL PROTECTED] [EMAIL PROTECTED]: Hi everybody, I am trying to compile/configure MySQl 5.0.41 on a Mandrake 10 linux box. In doing so, I am getting some errors with mysql_install_db (ERROR: 1049 Unknown database 'mysql', Installation of system tables failed!) Please let me know how to solve the problem. Thanks in advance for your time and help. Anand Here are the details pertaining to my problem: I used this as a guide: http://dev.mysql.com/doc/refman/5.0/en/quick-install.html: and followed all the steps. The following error(s) comes when I try using mysql_install_db /usr/local/mysql/bin/mysql_install_db --user=mysql Installing MySQL system tables... ERROR: 1049 Unknown database 'mysql' 070616 1:24:38 [ERROR] Aborting 070616 1:24:38 [Note] /usr/local/mysql/libexec/mysqld: Shutdown complete Installation of system tables failed! Examine the logs in /usr/local/mysql/var for more information. You can try to start the mysqld daemon with: /usr/local/mysql/libexec/mysqld --skip-grant and use the command line tool /usr/local/mysql/bin/mysql to connect to the mysql database and look at the grant tables: shell /usr/local/mysql/bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in /usr/local/mysql/var that may be helpful. --- And then, when I do /usr/local/mysql/libexec/mysqld --skip-grant as suggested in the above error message, it just aborts. Here is the architecture info. from mysqlbug -- Release: mysql-5.0.41 (Source distribution) C compiler:gcc (GCC) 3.3.2 (Mandrake Linux 10.0 3.3.2-6mdk) C++ compiler: g++ (GCC) 3.3.2 (Mandrake Linux 10.0 3.3.2-6mdk) Environment: machine, os, target, libraries (multiple lines) System: Linux 2.6.3-7mdk #1 Wed Mar 17 15:56:42 CET 2004 i686 unknown unknown GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i586-mandrake-linux-gnu/3.3.2/specs Configured with: ../configure --prefix=/usr --libdir=/usr/lib --with-slibdir=/lib --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --enable-long-long --enable-__cxa_atexit --enable-clocale=gnu --enable-languages=c,c++,ada,f77,objc,java,pascal --host=i586-mandrake-linux-gnu --with-system-zlib Thread model: posix gcc version 3.3.2 (Mandrake Linux 10.0 3.3.2-6mdk) Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Sep 13 2004 /lib/libc.so.6 - libc-2.3.3.so -rwxr-xr-x 1 root root 1281788 Feb 16 2004 /lib/libc-2.3.3.so -rw-r--r-- 1 root root 204 Feb 16 2004 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr/local/mysql' '--with-unix\ -socket-path=/usr/local/mysql/tmp/mysql.sock' -- -- 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: mysqldump for myisam tables.
2007/6/16, ViSolve DB Team [EMAIL PROTECTED]: -- this itself takes consistent backup. mysqldump utility by default locks the table. yes with --lock-tables option which do a FLUSH TABLES WITH READ LOCK and is a good consistent way for myisam tables. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Sorting by a list of possible results in a column....
Hi, You could use a case statement to implicitly convert your column to everything you want: select status, case when status = 'undefined' then 4 when status = 'Top Priority' then 1 ... End as ord_status from development order by ord_status Bye Geoffroy -Message d'origine- De : Mike Morton [mailto:[EMAIL PROTECTED] Envoyé : mardi 5 juin 2007 23:26 À : mysql@lists.mysql.com Objet : Sorting by a list of possible results in a column I am sure that this has been asked - but in searching through google and lists for about an hour - hopefully someone will indulge me a repeat question here. I have a query that selects a list of results, ordering them by the status field. However, I want to further sort that by the type of status, that is: Undefined Ready for Review Top Priority Priority Completed Etc... Every sort that I try, of course, sorts alphabetically. Is there a way to define how the sort function works in the order by? I know that I could do this in PHP after populating the results into an array, but that is (in my opinion) an unnecessary step that could be handled at the database query Thanks! Sample query: Select * from development order by status -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [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: MySQL 5.0 creates 100,000 Window handles
Hi, In fact, this is a good question. Therefore, I gonna try to answer. 1) What is MySQL using the handles for? Handles are nothing and all on Windows. Nothing because they are only pointers to hidden internal struct. And all, because, Handle are everywhere if you try to develop Win32 App. There are 3 kinds: Users: Window, Cursor, Menu,... GDI: all graphic objects such as Brush, Pen,... Kernel: Access token (ACL), Console input, Event, File, Heap, Mutex, Pipe, Process, Semaphore, Socket, Thread, Timer, ... A database server is therefore a great consumer of kernel Handles. 4) How does a MySQL server handle millions of queries on a large table? Will it hit an upper limit of Handles that it can allocate? The per-process theoretical limit on kernel handles is 2^24. However, handles are stored in the paged pool (kernel reserved memory), so the actual number of handles you can create is based on available memory. So, the number of handles that you can create on 32-bit Windows is significantly lower than 2^24. Example on Windows2000, max pool size is 300 MB (I don't know on most recent windows versions). But be sure MySQL server can handle millions of queries on a large table. 2) Is there any way to stop MySQL from consuming so many Windows resources? I investigated and can summarize with a simple test I made with a 5.0.37 compiled with all storage engines (and verified with a 4.1.21): MySQL allocates 43000 handles. I recompiled it without InnoDB and BDB, and MySQL allocates now 108 handles at startup. I'm not an indeep Innodb's specialist neither BDB, but I know they have row locking mechanism in difference of MyIsam but I'm sure that they are great consumers of Mutex. Windows of course runs slow with this many handles allocated. Yes, not because of number of handles (logical resources) but because of physical resources, especially RAM, and perhaps by-design in OS kernel. Regards, Geoffroy -Message d'origine- De : Geoffroy Cogniaux [mailto:[EMAIL PROTECTED] Envoyé : dimanche 27 mai 2007 23:13 À : 'mos'; mysql@lists.mysql.com Objet : RE: MySQL 5.0 creates 100,000 Window handles Hi, Try [EMAIL PROTECTED] instead. Bye. Geof. -Message d'origine- De : mos [mailto:[EMAIL PROTECTED] Envoyé : vendredi 25 mai 2007 06:41 À : mysql@lists.mysql.com Objet : MySQL 5.0 creates 100,000 Window handles I noticed if my program executes a lot of Select statements, Windows XP will slow down when the program completes. I did some investigating and mysqld-nt.exe has close to 100,000 handles created when my program ends (shown in Task Manager and SysInternals Process Explorer). As each Select statement is executed, 2 handles are created. These handles will stay allocated until the MySQL server is stopped (stopping my program won't free up the handles). Windows of course runs slow with this many handles allocated. 1) What is MySQL using the handles for? 2) Is there any way to stop MySQL from consuming so many Windows resources? 3) Is it like this on Linux? 4) How does a MySQL server handle millions of queries on a large table? Will it hit an upper limit of Handles that it can allocate? Note: it does not appear to allocate more handles if the query is found in the query cache. Mike -- 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: high physical and buffer reads.
Hi, I never heard about an Oracle's counter named HIGH PHYISCAL. But if you think about Scattered Read / Sequential Read / buffer busy waits, You can begin by reading this article: http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html Geoffroy. 2007/6/2, Baron Schwartz [EMAIL PROTECTED]: Hi, Ananda Kumar wrote: Hi All, Is it possible for us to know if a sql is doing HIGH PHYISCAL or BUFFER READS in mysql, something we can do in ORACLE. Just wanted to know, for the sake of sql tuning. I am no Oracle expert, so I'm not sure I know exactly what you mean. However, MySQL does expose many status counters and variables. Learning them all and learning how to make sense of them is a chore. Measuring them effectively is also somewhat tedious. I wrote MySQL Query Profiler to help me with this. As far as I know it is the only tool of its kind. You can get it from http://sourceforge.net/projects/mysqltoolkit/ Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql old 4.* query fails on 5.*
Hi, See 5.0 JOIN syntax at http://dev.mysql.com/doc/refman/5.0/fr/join.html Example: SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id Regards, Geoffroy -Message d'origine- De : Gmail User [mailto:[EMAIL PROTECTED] Envoyé : samedi 2 juin 2007 22:55 À : mysql@lists.mysql.com Objet : Re: mysql old 4.* query fails on 5.* Anyone know whats wrong here? Try as ... From (Klienter AS K, Tid As Td, Personal AS P) JOIN Uppdrag AS U ON K.Klient_ID = U.Klient_ID ... or ... From Tid As Td, Personal AS P, Klienter AS K JOIN Uppdrag AS U ON K.Klient_ID = U.Klient_ID ... This is the problem I had in one of my queries. The join is on the last table on the left side. Either use parentheses or put K table last. HTH, Ed -- 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: MySQL 5.0 creates 100,000 Window handles
You left in extreme tests. I would like to be able to answer that if it were so problematic, it would have gone up for a long time in a list of the known limits. But I do not know. It seems that if you're really falling in this extreme case (many many very small different queries in a big cache), regularly flushing query cache appears to be a good maintenance plan. It let me want to test what happens with a very big InnoDB table used in a complex transaction such as deep update. In any case, if you want to change your simple XP workstation into requested server, it will be heroic. Geoffroy -Message d'origine- De : mos [mailto:[EMAIL PROTECTED] Envoyé : samedi 2 juin 2007 23:49 À : mysql@lists.mysql.com Cc : [EMAIL PROTECTED] Objet : RE: MySQL 5.0 creates 100,000 Window handles Geoffroy, Thanks for the reply. Of course by now I figured out what's causing the large # of handles being allocated by Windoze XP. It's the query cache. Each query that gets added to the query cache uses 2 handles. If the query cache is large, say 150MB, as the cache fills up more and more handles are allocated. I was able to create a test program and generated hundreds of thousands of simple queries that returned a small # of rows from 1 large table (there were no cache hits because I wanted to fill the query cache up with as many unique queries as possible). I let it run overnight and in the morning Windows had allocated over 600,000 handles! It would have been more but the query cache was full by then. If I were to increase the query_cache_size then I'm sure I could get XP to allocate over 1 million handles. Flushing the query cache of course releases the handles. I'm not sure in the same thing happens in Linux or not. Does it? Should I be worried running MySQL on an XP box that has to run 24/7? Mike At 02:20 PM 6/2/2007, Geoffroy Cogniaux wrote: Hi, In fact, this is a good question. Therefore, I gonna try to answer. 1) What is MySQL using the handles for? Handles are nothing and all on Windows. Nothing because they are only pointers to hidden internal struct. And all, because, Handle are everywhere if you try to develop Win32 App. There are 3 kinds: Users: Window, Cursor, Menu,... GDI: all graphic objects such as Brush, Pen,... Kernel: Access token (ACL), Console input, Event, File, Heap, Mutex, Pipe, Process, Semaphore, Socket, Thread, Timer, ... A database server is therefore a great consumer of kernel Handles. 4) How does a MySQL server handle millions of queries on a large table? Will it hit an upper limit of Handles that it can allocate? The per-process theoretical limit on kernel handles is 2^24. However, handles are stored in the paged pool (kernel reserved memory), so the actual number of handles you can create is based on available memory. So, the number of handles that you can create on 32-bit Windows is significantly lower than 2^24. Example on Windows2000, max pool size is 300 MB (I don't know on most recent windows versions). But be sure MySQL server can handle millions of queries on a large table. 2) Is there any way to stop MySQL from consuming so many Windows resources? I investigated and can summarize with a simple test I made with a 5.0.37 compiled with all storage engines (and verified with a 4.1.21): MySQL allocates 43000 handles. I recompiled it without InnoDB and BDB, and MySQL allocates now 108 handles at startup. I'm not an indeep Innodb's specialist neither BDB, but I know they have row locking mechanism in difference of MyIsam but I'm sure that they are great consumers of Mutex. Windows of course runs slow with this many handles allocated. Yes, not because of number of handles (logical resources) but because of physical resources, especially RAM, and perhaps by-design in OS kernel. Regards, Geoffroy -Message d'origine- De : Geoffroy Cogniaux [mailto:[EMAIL PROTECTED] Envoyé : dimanche 27 mai 2007 23:13 À : 'mos'; mysql@lists.mysql.com Objet : RE: MySQL 5.0 creates 100,000 Window handles Hi, Try [EMAIL PROTECTED] instead. Bye. Geof. -Message d'origine- De : mos [mailto:[EMAIL PROTECTED] Envoyé : vendredi 25 mai 2007 06:41 À : mysql@lists.mysql.com Objet : MySQL 5.0 creates 100,000 Window handles I noticed if my program executes a lot of Select statements, Windows XP will slow down when the program completes. I did some investigating and mysqld-nt.exe has close to 100,000 handles created when my program ends (shown in Task Manager and SysInternals Process Explorer). As each Select statement is executed, 2 handles are created. These handles will stay allocated until the MySQL server is stopped (stopping my program won't free up the handles). Windows of course runs slow with this many handles allocated. 1) What is MySQL using the handles for? 2) Is there any way to stop MySQL from consuming so many Windows resources? 3) Is it like this on Linux? 4) How does a MySQL server handle millions of queries on a large table
Re: MySQL in multi-threaded environment
Hi, Assuming that you are using a multithread safe libmysql, I suggest you to have a look at your error code first: Error code 1064 suggests that you send a bad query to mysql, maybe your pool-query isn't indeed MT safe, so manipulating this variable requires a mutex. Error code 1062 suggests that you try to insert duplicate key in your table: remove this key ( bad suggestion ) or check that your pool doesn't send more than one time the same query ( perhaps, the mutex on pool-query is enough ) Try to limit mutexes only where it's necessary and be aware where you're locking and unlocking them. Regards, Geoffroy. 2007/5/30, Ace [EMAIL PROTECTED]: Hi Friends, I am facing problem while using MySQL in multi-threaded environment. I am using C lang for developement. I maintain MySQL connection pool between threads but with increase in number of requests, it started reporting following errors - == Server Errors: Error: 1064 :Parse error Error: 1062 :Duplicate entry == Then I used mutexes around MySQL API calls and it worked. But seems use of mutex impacts the performance, is this true? Any other solution to this than mutex or any out-of-box solution that might have worked? Thanks for your help!!! -- Cheers, Rajan
RE: mysql creating lots of processes (not threads, linux processes)
Try to start it with mysql_safe instead or try to start mysqld manually within a command prompt, without fork, to see what happen. ./mysqld --console --verbose --your_options Can you at least connect to mysql with a remote client on this server or not? Have a look on this page about starting issues: http://dev.mysql.com/doc/refman/5.0/en/unix-post-installation.html#starting- server Geoffroy -Message d'origine- De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] De la part de Quentin Gouedard Envoyé : mercredi 30 mai 2007 09:02 À : Scott Tanner Cc : mysql@lists.mysql.com Objet : Re: mysql creating lots of processes (not threads, linux processes) Nope, I'm using 5.0.38 on Gentoo, built via emerge in the exact same manner. Thanks for your answers guys. On 5/30/07, Scott Tanner [EMAIL PROTECTED] wrote: Sounds like your not using threaded libraries. Was mysql built differently, or are you using a different RPM on this server? Scott On Tue, 2007-05-29 at 22:49 +0200, Quentin Gouedard wrote: No, I have just collectd+mrtg, but i don't even use them to monitor mysql. I launch mysql via /etc/init.d/mysql start , and the script is the exact same as on the other servers. Even just after startup there's already 15-20 processes created. On 5/29/07, Geoffroy Cogniaux [EMAIL PROTECTED] wrote: Hi, It looks like automatic start-up called by a monitoring process (Nagios, ...). Have you such tools on your servers ? Geoffroy -Message d'origine- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] De la part de Quentin Gouedard Envoyé: mardi 29 mai 2007 16:41 À: mysql@lists.mysql.com Objet: mysql creating lots of processes (not threads, linux processes) Hi, So I use mysql as the DB for a large site (up to 1 concurrent users at peaks). I have a front server as a reverse proxy and multiple (7) backend machines serving the site. Each machine has data strictly similar in nature and quantity. On 6 of these machines, I have 1 single mysqld process (process in linux terms): # ps -ef | grep mysqld | wc -l 2 There are generally 5-8 threads (processes as mysql means it) running when i do a show processlist; Now, on one of those machines there are huge number of processes for mysql. # ps -ef | grep mysqld | wc -l 34 Running just ps shows for each of these processes: mysql25952 10073 0 16:25 ?00:00:02 /usr/sbin/mysqld --defaults-file=/etc/mysql/my.cnf --basedir=/usr --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock This machine has no particular data, is doing nothing different than the others. The show processlist command also returns 5-8 processes. So where are these myqsld processes from ? There's like 20 at startup (instantly after launching mysql), but it keeps increasing, until i restart mysql or the server runs out of memory. I have compared the mysql configuration of this machine and the 6 other, variable by variable, and they are strictly identical. How come this server behaves differently ? What can I do to have the single-process behaviour on that machine too ? Thanks, Quentin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql creating lots of processes (not threads, linux processes)
Hi, It looks like automatic start-up called by a monitoring process (Nagios, ...). Have you such tools on your servers ? Geoffroy -Message d'origine- De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] De la part de Quentin Gouedard Envoyé : mardi 29 mai 2007 16:41 À : mysql@lists.mysql.com Objet : mysql creating lots of processes (not threads, linux processes) Hi, So I use mysql as the DB for a large site (up to 1 concurrent users at peaks). I have a front server as a reverse proxy and multiple (7) backend machines serving the site. Each machine has data strictly similar in nature and quantity. On 6 of these machines, I have 1 single mysqld process (process in linux terms): # ps -ef | grep mysqld | wc -l 2 There are generally 5-8 threads (processes as mysql means it) running when i do a show processlist; Now, on one of those machines there are huge number of processes for mysql. # ps -ef | grep mysqld | wc -l 34 Running just ps shows for each of these processes: mysql25952 10073 0 16:25 ?00:00:02 /usr/sbin/mysqld --defaults-file=/etc/mysql/my.cnf --basedir=/usr --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock This machine has no particular data, is doing nothing different than the others. The show processlist command also returns 5-8 processes. So where are these myqsld processes from ? There's like 20 at startup (instantly after launching mysql), but it keeps increasing, until i restart mysql or the server runs out of memory. I have compared the mysql configuration of this machine and the 6 other, variable by variable, and they are strictly identical. How come this server behaves differently ? What can I do to have the single-process behaviour on that machine too ? Thanks, Quentin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: restore one database.
Yes it is. This is why I suggest dumping db per db. Bye. -Message d'origine- De : Ananda Kumar [mailto:[EMAIL PROTECTED] Envoyé : lundi 28 mai 2007 10:41 À : Geoffroy Cogniaux Cc : John Meyer; MySQL General Objet : Re: restore one database. Hi Geoffroy, Very true, restore depends on the kind of backup we do. I was just wondering if mysql has any option to restore just one database from the mysqldump having all the database. So, as of now mysql does not provided option to just restore just one database from dump having all database..Right? regards anandkl On 5/28/07, Geoffroy Cogniaux [EMAIL PROTECTED] wrote: Hi Ananda, Recovery strategy depends essentially of the backup strategy you choose. I think that if you want be able to proceed to a restoration, database per database, you should separate their backups. It can simply be done with this kind of script: for db in (`echo 'show databases;' | mysql -u user --password=pwd | grep -v ^Database$`); do mysqldump -u user --password=pwd $db /mybackupdir/$db.bak ; done; If you want to have all in one file, use tar after your backup: cd /mybackupdir tar -czf mybackup.tar.gz *.bak rm -fr *.bak Best regards, Geof. -Message d'origine- De: Ananda Kumar [mailto:[EMAIL PROTECTED] Envoyé: dimanche 27 mai 2007 13:52 À: John Meyer Cc: MySQL General Objet: Re: restore one database. Hi All, I think my question was not understood. All the database are important. Now that one of the database is accidently dropped, can i restore from that single database from my dump and use the bin log and recover till AS OF NOW. regards anandkl On 5/27/07, John Meyer [EMAIL PROTECTED] wrote: Ananda Kumar wrote: Hi Pelle, I dont have enough space on any other storage, so i was thinking if we would just restore one database from dump that would save lot of time , rather than restoring all the database. regards anandkl Well, if only one database is important enough to back up, then yes it will. But if you have multiple databases that you are actively using then you'll need to back them all up. You don't necessarily need to use an all databases dump, though. -- The NCP Revue -- http://www.ncprevue.com/blog -- 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: CSV import
Hi, It can be done easily with PhpMyAdmin, but it is not in .net 2007/5/28, Sharique uddin Ahmed Farooqui [EMAIL PROTECTED]: I want to import data from a CSV file in a table. MySql admin doesn't support import from CSV files. Format of data is different from structure of table. Is there any app/snippet written for this task in .net , which I can modify according to my need. -- Sharique uddin Ahmed Farooqui (C++/C# Developer, IT Consultant) A revolution is about to begin. A world is about to change. And you and me are the initiator.
RE: error message
Hi, Could we show your procedure to try to help you ? Geoffroy. -Message d'origine- De : Ananda Kumar [mailto:[EMAIL PROTECTED] Envoyé : vendredi 25 mai 2007 17:35 À : MySQL General Objet : error message Hi All, We are using 5.0.40-enterprise-gpl-log version on our production db. If we run a stored proc, we are getting the below error Query OK, 0 rows affected, 1 warning (0.01 sec) ERROR 1308 (42000): LEAVE with no matching label: SWL_return mysql show warnings; - // +---+--+--+ | Level | Code | Message | +---+--+--+ | Error | 1308 | LEAVE with no matching label: SWL_return | +---+--+--+ But on our qa with version 5.0.32-enterprise-gpl-log, the proc gets created and exeuctes also. Can you please help here. regards anandkl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: restore one database.
Hi Ananda, Recovery strategy depends essentially of the backup strategy you choose. I think that if you want be able to proceed to a restoration, database per database, you should separate their backups. It can simply be done with this kind of script: for db in (`echo 'show databases;' | mysql -u user --password=pwd | grep -v ^Database$`); do mysqldump -u user --password=pwd $db /mybackupdir/$db.bak ; done; If you want to have all in one file, use tar after your backup: cd /mybackupdir tar -czf mybackup.tar.gz *.bak rm -fr *.bak Best regards, Geof. -Message d'origine- De : Ananda Kumar [mailto:[EMAIL PROTECTED] Envoyé : dimanche 27 mai 2007 13:52 À : John Meyer Cc : MySQL General Objet : Re: restore one database. Hi All, I think my question was not understood. All the database are important. Now that one of the database is accidently dropped, can i restore from that single database from my dump and use the bin log and recover till AS OF NOW. regards anandkl On 5/27/07, John Meyer [EMAIL PROTECTED] wrote: Ananda Kumar wrote: Hi Pelle, I dont have enough space on any other storage, so i was thinking if we would just restore one database from dump that would save lot of time , rather than restoring all the database. regards anandkl Well, if only one database is important enough to back up, then yes it will. But if you have multiple databases that you are actively using then you'll need to back them all up. You don't necessarily need to use an all databases dump, though. -- The NCP Revue -- http://www.ncprevue.com/blog -- 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: restore one database.
Hi Ananda, Recovery strategy depends essentially of the backup strategy you choose. I think that if you want be able to proceed to a restoration, database per database, you should separate their backups. It can simply be done with this kind of script: for db in (`echo 'show databases;' | mysql -u user --password=pwd | grep -v ^Database$`); do mysqldump -u user --password=pwd $db /mybackupdir/$db.bak ; done; If you want to have all in one file, use tar after your backup: cd /mybackupdir tar -czf mybackup.tar.gz *.bak rm -fr *.bak Best regards, Geof. -Message d'origine- De : Ananda Kumar [mailto:[EMAIL PROTECTED] Envoyé : dimanche 27 mai 2007 13:52 À : John Meyer Cc : MySQL General Objet : Re: restore one database. Hi All, I think my question was not understood. All the database are important. Now that one of the database is accidently dropped, can i restore from that single database from my dump and use the bin log and recover till AS OF NOW. regards anandkl On 5/27/07, John Meyer [EMAIL PROTECTED] wrote: Ananda Kumar wrote: Hi Pelle, I dont have enough space on any other storage, so i was thinking if we would just restore one database from dump that would save lot of time , rather than restoring all the database. regards anandkl Well, if only one database is important enough to back up, then yes it will. But if you have multiple databases that you are actively using then you'll need to back them all up. You don't necessarily need to use an all databases dump, though. -- The NCP Revue -- http://www.ncprevue.com/blog -- 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: MySQL 5.0 creates 100,000 Window handles
Hi, Try [EMAIL PROTECTED] instead. Bye. Geof. -Message d'origine- De : mos [mailto:[EMAIL PROTECTED] Envoyé : vendredi 25 mai 2007 06:41 À : mysql@lists.mysql.com Objet : MySQL 5.0 creates 100,000 Window handles I noticed if my program executes a lot of Select statements, Windows XP will slow down when the program completes. I did some investigating and mysqld-nt.exe has close to 100,000 handles created when my program ends (shown in Task Manager and SysInternals Process Explorer). As each Select statement is executed, 2 handles are created. These handles will stay allocated until the MySQL server is stopped (stopping my program won't free up the handles). Windows of course runs slow with this many handles allocated. 1) What is MySQL using the handles for? 2) Is there any way to stop MySQL from consuming so many Windows resources? 3) Is it like this on Linux? 4) How does a MySQL server handle millions of queries on a large table? Will it hit an upper limit of Handles that it can allocate? Note: it does not appear to allocate more handles if the query is found in the query cache. Mike -- 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]
