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
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]
FLUSH TABLE LOCK READ
Hi All, This question just for information sake. If i do flush table lock read; Any changes happening on tables during this period, will it be viewable by another user or will the user have to wait till the lock is released to see the new changes And once the lock is released, does mysql apply all the changes from bin-log to the respective tables. Thanks you all in advance. regards anandkl
Re: FLUSH TABLE LOCK READ
Hi, Ananda Kumar wrote: Hi All, This question just for information sake. If i do flush table lock read; I think you mean FLUSH TABLES WITH READ LOCK? Any changes happening on tables during this period, will it be viewable by another user or will the user have to wait till the lock is released to see the new changes And once the lock is released, does mysql apply all the changes from bin-log to the respective tables. The tables are locked. There will be no changes while they are locked. I don't think I really understand your question. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
selecting second last row
I have a table of publications. -- Table structure for news -- CREATE TABLE `news` ( `id` int(100) NOT NULL auto_increment, `content` longblob, `title` varchar(100) default NULL, `date` date default NULL, `display` varchar(10) default 'no', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- the column display = 'yes'; if the article is to be dispalyed on the homepage. I only have 2 yes columns equal to yes at any one time. SELECT * FROM news WHERE display='yes' ORDER BY id desc limit 1 Think this would get the lastest article that is to be displayed but how do I get the second one. R.
Re: selecting second last row
I have a table of publications. -- Table structure for news -- CREATE TABLE `news` ( `id` int(100) NOT NULL auto_increment, `content` longblob, `title` varchar(100) default NULL, `date` date default NULL, `display` varchar(10) default 'no', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- the column display = 'yes'; if the article is to be dispalyed on the homepage. I only have 2 yes columns equal to yes at any one time. SELECT * FROM news WHERE display='yes' ORDER BY id desc limit 1 Think this would get the lastest article that is to be displayed but how do I get the second one. R. The simple way: use LIMIT 1, 1. A variety of more general techniques are explained here: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: selecting second last row
SELECT * FROM news WHERE display='yes' ORDER BY id desc limit 1 Think this would get the lastest article that is to be displayed but how do I get the second one. Just add an offset to the LIMIT clause: SELECT * FROM news WHERE display='yes' ORDER BY id desc limit 1,1 Edward -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Does mysql support groups?
Does Mysql support groups of users? I am working on porting an application from Sybase to Mysql and it relies heavily on using groups to control access to the data. I am not seeing this mentioned in Mysql, but I might be overlooking something. So does it support groups? Thanks, Chris
mysql software appliance
I am pleased to announce the creation of a MySQL software appliance built using rPath's rBuilder tools. The appliance includes mysql server, the latest community edition. Give it a try, and leave feedback! Currently, there are install images for x86/x86_64, xen domU images for x86/x86_64, and images for VMware, VMware ESX, LiveCD, Virtual Iron, and even VHD. Get them all from http://mysql.rpath.org smithj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does mysql support groups?
Hi Chris, Chris Hoover wrote: Does Mysql support groups of users? I am working on porting an application from Sybase to Mysql and it relies heavily on using groups to control access to the data. I am not seeing this mentioned in Mysql, but I might be overlooking something. So does it support groups? No, just straightforward user accounts with individual GRANT and REVOKE. I too miss groups/roles, alas. If it helps, the way I tend to manage it is just assign everyone to the same user account, and name it as though it's a group -- e.g. analyst, developer, intern, and so forth. I know that's not the same thing and has obvious limitations, but for my purposes it's easier than having to maintain many accounts. If anyone has nifty ways to do interesting stuff with user accounts, I'd love to hear about them! Baron -- Baron Schwartz http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does mysql support groups?
Chris, Does Mysql support groups of users? It does not implement groups, but they are relatively easy to implement by linking a usergroups table to mysql.users. PB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FLUSH TABLE LOCK READ
Hi Baron, Its only a read lock, so changes on these table should be allowed, that is what the documentaion say. And the changes will be in bin-log till the READ lock is held. regards anandkl On 5/30/07, Baron Schwartz [EMAIL PROTECTED] wrote: Hi, Ananda Kumar wrote: Hi All, This question just for information sake. If i do flush table lock read; I think you mean FLUSH TABLES WITH READ LOCK? Any changes happening on tables during this period, will it be viewable by another user or will the user have to wait till the lock is released to see the new changes And once the lock is released, does mysql apply all the changes from bin-log to the respective tables. The tables are locked. There will be no changes while they are locked. I don't think I really understand your question. Baron
mysqldump --where condition.
Hi All, In mysqldump , we have a --where parameter. Is this parameter used only for a specific table or can we have list of tables or can this be for a specific database or all databases. regards anandkl
identifying rows that have changed
Hi, I'm working on a rewrite of a batch process that operates on a large InnoDB database. In the past, it would process the entire database every night, but now the size of the data is making that impossible, and there is a desire for the process to operate in near real-time, so I'm rewriting it to work incrementally. The idea is to run it from cron every 5 minutes and process the data from the last 5 minutes. The problem I'm having is identifying which rows have changed. I'm not concerned about deletes in this application, so I thought it would be enough to just use automatic timestamps and keep track of the last run time of the program, but then I realized that an uncommitted transaction could come in later with a timestamp from earlier. I haven't seen any way to make the timestamp reflect the actual commit time. I have a few ideas of how to handle this: 1) Put in a 5-minute delay and hope this is long enough for all transactions to commit. This is simple, but the delay is not desirable and there's no guarantee that transactions will all be finished in this time window (although if they didn't it would certainly indicate a problem for this system). 2) Use a boolean flag on every row in every table to indicate if it has been seen yet. This seems like a bad idea, since it would require the batch process to do tons of updates to the source data as it processes the rows. 3) Triggers to fill in a logging table. This seems to be a common approach. I'm not sure what the best way to structure the logging table is, since some of these tables have multi-column primary keys. Suggestions here would be welcome. This one is a lot of work, but sounds pretty safe. Can anyone offer advice or anecdotes about how you have handled similar situations? - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FLUSH TABLE LOCK READ
Hi Ananda, I think you are confused about locks and the binlog both. Locks lock tables. This disallows changes. READ locks prevent changes, but do not block anyone from READING from the tables, and if I recall correctly, also allow other people to get READ locks at the same time. WRITE locks block any access to the tables by other connections. You can test this yourself very easily. But regardless, you cannot change a table that is locked. As for the binlog, it records changes that have happened to the database. If I understand you correctly, you may be thinking of it as a buffer of changes not yet applied, which will be applied when the lock is released. That is not how it works. If you have a FLUSH TABLES WITH READ LOCK open, all tables are flushed to disk and locked, and there will be *no changes* because everything is locked. Therefore nothing other connections try to do will be written to the binlog while you have the locks open (unless you make those changes yourself, which I have never tried to do). Cheers Baron Ananda Kumar wrote: Hi Baron, Its only a read lock, so changes on these table should be allowed, that is what the documentaion say. And the changes will be in bin-log till the READ lock is held. regards anandkl On 5/30/07, Baron Schwartz [EMAIL PROTECTED] wrote: Hi, Ananda Kumar wrote: Hi All, This question just for information sake. If i do flush table lock read; I think you mean FLUSH TABLES WITH READ LOCK? Any changes happening on tables during this period, will it be viewable by another user or will the user have to wait till the lock is released to see the new changes And once the lock is released, does mysql apply all the changes from bin-log to the respective tables. The tables are locked. There will be no changes while they are locked. I don't think I really understand your question. Baron -- 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)
Merci Geoffroy, starting from the command prompt shows: # /usr/sbin/mysqld --console --verbose --basedir=/usr --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock 070530 18:01:28 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.0.38-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Gentoo Linux mysql-5.0.38 However running a ps already shows multiple mysqld processes. Something i had not noticed so far: # ps -ef | grep mysqld mysql25752 27831 0 18:01 pts/200:00:00 /usr/sbin/mysqld --console --verbose --basedir=/usr --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock root 16560 25752 0 18:01 pts/200:00:00 /usr/sbin/mysqld --console --verbose --basedir=/usr --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock root 16560 25752 0 18:01 pts/200:00:00 /usr/sbin/mysqld --console --verbose --basedir=/usr --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock root 23390 16560 0 18:01 pts/200:00:00 /usr/sbin/mysqld --console --verbose --basedir=/usr --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock(etc.) the first process created is the only one with mysql owner. All the subsequent processes are own by root, and have as parent one of the previously created processes (not always the first one). Not sure if that helps in understanding this though. Thanks anyway for your help. On 5/30/07, Geoffroy Cogniaux [EMAIL PROTECTED] wrote: 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
Re: identifying rows that have changed
Perrin, I like #3 the best. #1 - it's not a good approach to hope your database keeps up. There are fairly common situations that can come up where you never know how long something will take - unusually high traffic, table check and repair, a bulk load into the same or another database on that db host, etc. #2 - the flag is a good idea on the face of it, but in reality your process may end up doing large numbers of table scans to find rows with the flag set. #3 allows you to keep track of exactly which rows need post-processing, and potentially even track when the request came in vs. when it was processed, if desired. You could also get even closer to the near real-time desire by running the process constantly and having it idle for 30 seconds, check for new rows, idle, etc. One interesting gotcha would be trying to ensure that whatever updates your batch process does - do not cause additional entries in the needs to be post processed table, causing an endless loop... I'm sure there's a way around it, like an extra column called is_post_process and then your trigger doesn't do its thing if that equals 1 in the update or something like that. Or perhaps you only need an insert trigger - then you don't have that problem. Best, Dan On 5/30/07, Perrin Harkins [EMAIL PROTECTED] wrote: Hi, I'm working on a rewrite of a batch process that operates on a large InnoDB database. In the past, it would process the entire database every night, but now the size of the data is making that impossible, and there is a desire for the process to operate in near real-time, so I'm rewriting it to work incrementally. The idea is to run it from cron every 5 minutes and process the data from the last 5 minutes. The problem I'm having is identifying which rows have changed. I'm not concerned about deletes in this application, so I thought it would be enough to just use automatic timestamps and keep track of the last run time of the program, but then I realized that an uncommitted transaction could come in later with a timestamp from earlier. I haven't seen any way to make the timestamp reflect the actual commit time. I have a few ideas of how to handle this: 1) Put in a 5-minute delay and hope this is long enough for all transactions to commit. This is simple, but the delay is not desirable and there's no guarantee that transactions will all be finished in this time window (although if they didn't it would certainly indicate a problem for this system). 2) Use a boolean flag on every row in every table to indicate if it has been seen yet. This seems like a bad idea, since it would require the batch process to do tons of updates to the source data as it processes the rows. 3) Triggers to fill in a logging table. This seems to be a common approach. I'm not sure what the best way to structure the logging table is, since some of these tables have multi-column primary keys. Suggestions here would be welcome. This one is a lot of work, but sounds pretty safe. Can anyone offer advice or anecdotes about how you have handled similar situations? - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: identifying rows that have changed
On 5/30/07, Dan Buettner [EMAIL PROTECTED] wrote: #1 - it's not a good approach to hope your database keeps up. There are fairly common situations that can come up where you never know how long something will take - unusually high traffic, table check and repair, a bulk load into the same or another database on that db host, etc. Yes, I expect it would drift some over time. I might need to do a full reload weekly. It isn't really a problem for me to process the same rows twice, but if I make the time window really large it defeats the purpose of the incremental processing, i.e. keeping the working data set small. One interesting gotcha would be trying to ensure that whatever updates your batch process does - do not cause additional entries in the needs to be post processed table, causing an endless loop... In my case, this is easy, since I don't write anything back to the source tables. The results go into a separate database. Thanks for your input. - Perrin -- 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)
Oh and by the way mysql works just fine on that machine. You can run queries without any problems. Only it keeps spawning new processes over again. It takes about 3h before the machine starts having problems due to memory getting full of useless mysql processes. I just got a new machine for my site, and it's happenning there too (although on none of my 5 other boxes), which is good cause i can do some more extensive testing before i actually use it. Another thing is, mysql won't stop. /etc/init.d/mysql stop just hangs. There are some processes that need to be killed manually in the end. I'm guessing this could have to do with the process owner being root. Some similar problems are reported here though: http://forums.gentoo.org//viewtopic-t-544730-highlight-mysql.html On 5/30/07, Quentin Gouedard [EMAIL PROTECTED] wrote: Merci Geoffroy, starting from the command prompt shows: # /usr/sbin/mysqld --console --verbose --basedir=/usr --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock 070530 18:01:28 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.0.38-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Gentoo Linux mysql-5.0.38 However running a ps already shows multiple mysqld processes. Something i had not noticed so far: # ps -ef | grep mysqld mysql25752 27831 0 18:01 pts/200:00:00 /usr/sbin/mysqld --console --verbose --basedir=/usr --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock root 16560 25752 0 18:01 pts/200:00:00 /usr/sbin/mysqld --console --verbose --basedir=/usr --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock root 16560 25752 0 18:01 pts/200:00:00 /usr/sbin/mysqld --console --verbose --basedir=/usr --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock root 23390 16560 0 18:01 pts/200:00:00 /usr/sbin/mysqld --console --verbose --basedir=/usr --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock(etc.) the first process created is the only one with mysql owner. All the subsequent processes are own by root, and have as parent one of the previously created processes (not always the first one). Not sure if that helps in understanding this though. Thanks anyway for your help. On 5/30/07, Geoffroy Cogniaux [EMAIL PROTECTED] wrote: 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:
Re: mysql creating lots of processes (not threads, linux processes)
Hi Quentin, You say this machine is the same as all other machines. In my experience with Gentoo, this is hard to achieve. About the only way to do it, in my opinion, is to rsync the Portage snapshot from a machine you're happy with, rsync all the downloaded source tarballs, rsync the /etc/make.conf and other things that affect the USE flags, and then emerge --emptytree world. Or build binary packages on another machine and install them instead of compiling from source on every machine. One way or another, I think you have to clone all the machines from one you're happy with. Not saying that you didn't do that, or implying anything else about you or your setup, but I just wanted to float this out there for consideration. If you aren't really really sure the machines are the same, chances are you have something different, like built with a different GCC, different version of glibc, or something like that. Baron Quentin Gouedard wrote: Oh and by the way mysql works just fine on that machine. You can run queries without any problems. Only it keeps spawning new processes over again. It takes about 3h before the machine starts having problems due to memory getting full of useless mysql processes. I just got a new machine for my site, and it's happenning there too (although on none of my 5 other boxes), which is good cause i can do some more extensive testing before i actually use it. Another thing is, mysql won't stop. /etc/init.d/mysql stop just hangs. There are some processes that need to be killed manually in the end. I'm guessing this could have to do with the process owner being root. Some similar problems are reported here though: http://forums.gentoo.org//viewtopic-t-544730-highlight-mysql.html On 5/30/07, Quentin Gouedard [EMAIL PROTECTED] wrote: Merci Geoffroy, starting from the command prompt shows: # /usr/sbin/mysqld --console --verbose --basedir=/usr --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock 070530 18:01:28 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.0.38-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Gentoo Linux mysql-5.0.38 However running a ps already shows multiple mysqld processes. Something i had not noticed so far: # ps -ef | grep mysqld mysql25752 27831 0 18:01 pts/200:00:00 /usr/sbin/mysqld --console --verbose --basedir=/usr --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock root 16560 25752 0 18:01 pts/200:00:00 /usr/sbin/mysqld --console --verbose --basedir=/usr --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock root 16560 25752 0 18:01 pts/200:00:00 /usr/sbin/mysqld --console --verbose --basedir=/usr --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock root 23390 16560 0 18:01 pts/200:00:00 /usr/sbin/mysqld --console --verbose --basedir=/usr --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock(etc.) the first process created is the only one with mysql owner. All the subsequent processes are own by root, and have as parent one of the previously created processes (not always the first one). Not sure if that helps in understanding this though. Thanks anyway for your help. On 5/30/07, Geoffroy Cogniaux [EMAIL PROTECTED] wrote: 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 ?
selecting second last row
If there are only two rows that satisfy SELECT * FROM news WHERE display='yes' ORDER BY id desc then SELECT * FROM news WHERE display='yes' ORDER BY id asc limit 1 gives the second row. Am I missing something? Waldo Tumanut Database Analyst CONFIDENTIALITY NOTICE: This electronic mail transmission (including any accompanying attachments) is intended solely for its authorized recipient(s), and may contain confidential and/or legally privileged information. If you are not an intended recipient, or responsible for delivering some or all of this transmission to an intended recipient, be aware that any review, copying, printing, distribution, use or disclosure of the contents of this message is strictly prohibited. If you have received this electronic mail message in error, please contact us immediately by electronic mail at [EMAIL PROTECTED] and destroy the original and all copies of this transmission (including any attachments). Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
True multi user?
I have a mailmerge document set up with a datasource table of 'data' I want each user to create this table from a result of other tables. How can users create their own version without overwriting each others work ? Thanks -- View this message in context: http://www.nabble.com/True-multi-user--tf3841867.html#a10878875 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trigger question
Hey all, I asked this before (a while back) and have gotten some answers that solve my problem partially only. --- old message -- I have a table with 205 columns. When an update statement updates a row in this table I want a trigger that creates a record of the old row in a separate table. The following works fine when not too many columns need to be written into the other table CREATE TRIGGER track_table BEFORE UPDATE ON table FOR EACH ROW BEGIN INSERT INTO table_track (value1,value2) VALUES (OLD.value1,old.value2); END; Is the any way of using OLD for all columns or generally another way of doing this. --- old message -- One answer (thanks btw) suggested to do this: INSERT INTO table_track SELECT * FROM table WHERE key_col=OLD.key_col; This works fine, requires however that both tables have the same structure (nr of columns, order, etc). I would like to add an autoincrement ID and a timestamp to the record written by the trigger and do not want to have a timestamp field in the original table. Is there any other way of doing this? The only other idea I have (and I am not sure if this would even work) is to select the column headers from information_schema and loop through them building the actual insert statement. Thanks Olaf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Redirecting all queries to mysql dababase to another instance
Hi, I need to write a small middleware program that can capture, inspect and redirect all queries to an old instance of mysql to a new instance. Any help or pointers to get started would be greatly appreciated. thanks, Chike. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Redirecting all queries to mysql dababase to another instance
Hi, Chibuike Muoh wrote: Hi, I need to write a small middleware program that can capture, inspect and redirect all queries to an old instance of mysql to a new instance. Any help or pointers to get started would be greatly appreciated. Perhaps this will help: http://jan.kneschke.de/projects/mysql/mysql-proxy Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trigger question
Hi, Olaf Stein wrote: Hey all, I asked this before (a while back) and have gotten some answers that solve my problem partially only. --- old message -- I have a table with 205 columns. When an update statement updates a row in this table I want a trigger that creates a record of the old row in a separate table. The following works fine when not too many columns need to be written into the other table CREATE TRIGGER track_table BEFORE UPDATE ON table FOR EACH ROW BEGIN INSERT INTO table_track (value1,value2) VALUES (OLD.value1,old.value2); END; Is the any way of using OLD for all columns or generally another way of doing this. --- old message -- One answer (thanks btw) suggested to do this: INSERT INTO table_track SELECT * FROM table WHERE key_col=OLD.key_col; This works fine, requires however that both tables have the same structure (nr of columns, order, etc). I would like to add an autoincrement ID and a timestamp to the record written by the trigger and do not want to have a timestamp field in the original table. Is there any other way of doing this? The only other idea I have (and I am not sure if this would even work) is to select the column headers from information_schema and loop through them building the actual insert statement. If your columns are the same other than the ID and timestamp, and the two extra columns come last in the destination table, you could SELECT *, NULL, NOW() FROM Otherwise I think you will have to list the columns explicitly. You could write a script that will generate the triggers for you fairly easily. Maybe that will help ease the pain of maintaining things if your schema changes. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: True multi user?
You didn't specify how this table is to be created or used (what application). If it is all done within one connection, then you could use a temporary table. I have a feeling that's not what you need, though. Please give a little more detail about the application's interaction with MySQL. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: JugLugs [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 30, 2007 1:41 PM To: mysql@lists.mysql.com Subject: True multi user? I have a mailmerge document set up with a datasource table of 'data' I want each user to create this table from a result of other tables. How can users create their own version without overwriting each others work ? Thanks -- View this message in context: http://www.nabble.com/True-multi-user--tf3841867.html#a10878875 Sent from the MySQL - General mailing list archive at Nabble.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: Trigger question
Will a SELECT * FROM work there? I'm not using 5.x, so I haven't really investigated triggers; this is just a guess. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Olaf Stein [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 30, 2007 2:05 PM To: MySql Subject: Trigger question Hey all, I asked this before (a while back) and have gotten some answers that solve my problem partially only. --- old message -- I have a table with 205 columns. When an update statement updates a row in this table I want a trigger that creates a record of the old row in a separate table. The following works fine when not too many columns need to be written into the other table CREATE TRIGGER track_table BEFORE UPDATE ON table FOR EACH ROW BEGIN INSERT INTO table_track (value1,value2) VALUES (OLD.value1,old.value2); END; Is the any way of using OLD for all columns or generally another way of doing this. --- old message -- One answer (thanks btw) suggested to do this: INSERT INTO table_track SELECT * FROM table WHERE key_col=OLD.key_col; This works fine, requires however that both tables have the same structure (nr of columns, order, etc). I would like to add an autoincrement ID and a timestamp to the record written by the trigger and do not want to have a timestamp field in the original table. Is there any other way of doing this? The only other idea I have (and I am not sure if this would even work) is to select the column headers from information_schema and loop through them building the actual insert statement. Thanks Olaf -- 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: Trigger question
Baron, Thanks a lot Adding the columns to the end works... Olaf On 5/30/07 2:13 PM, Baron Schwartz [EMAIL PROTECTED] wrote: Hi, Olaf Stein wrote: Hey all, I asked this before (a while back) and have gotten some answers that solve my problem partially only. --- old message -- I have a table with 205 columns. When an update statement updates a row in this table I want a trigger that creates a record of the old row in a separate table. The following works fine when not too many columns need to be written into the other table CREATE TRIGGER track_table BEFORE UPDATE ON table FOR EACH ROW BEGIN INSERT INTO table_track (value1,value2) VALUES (OLD.value1,old.value2); END; Is the any way of using OLD for all columns or generally another way of doing this. --- old message -- One answer (thanks btw) suggested to do this: INSERT INTO table_track SELECT * FROM table WHERE key_col=OLD.key_col; This works fine, requires however that both tables have the same structure (nr of columns, order, etc). I would like to add an autoincrement ID and a timestamp to the record written by the trigger and do not want to have a timestamp field in the original table. Is there any other way of doing this? The only other idea I have (and I am not sure if this would even work) is to select the column headers from information_schema and loop through them building the actual insert statement. If your columns are the same other than the ID and timestamp, and the two extra columns come last in the destination table, you could SELECT *, NULL, NOW() FROM Otherwise I think you will have to list the columns explicitly. You could write a script that will generate the triggers for you fairly easily. Maybe that will help ease the pain of maintaining things if your schema changes. Baron - Olaf Stein DBA Center for Quantitative and Computational Biology Columbus Children's Research Institute 700 Children's Drive phone: 1-614-355-5685 cell: 1-614-843-0432 email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: True multi user?
There is a master document on a server which references a table called 'data' within database. I need users to query many tables and produce a data source called 'data' which the document can then reference. The problem is, that this 'data' table could easily get overwritten by another user. Creating a number of similar documents (one for each user) is not really practical as there may be an infinite number of users. Also this is a web based application and therefore I would have difficulty in writing a datasource (csv or something) to the users hard drive without user intervention. Regards JugLugs -- View this message in context: http://www.nabble.com/True-multi-user--tf3841867.html#a10879747 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Efficiently modeling sets and subsets in lattice-like structure
Apologies for the mass cross-posting: I haven't been able to find a single
answer or reference for the problem below (googling didn't help), and
was hoping someone could point me to something helpful. I'm convinced
there's a well-known answer here that I just can't find :(
We're modeling a collection of (finite mathematical) sets, where sets
may contain each other as subsets.
For example, set X may be defined as {7,11,15} + Y meaning X
contains 7, 11, 15, and all the members of set Y. Set Y could then be
{15,20,25} + Z, and Z might be {7,14,30} with no subsets.
Of course, no set includes itself, directly or indirectly.
However, one set may include many other subsets (eg, X may include
both Y and Z), and one set may be included in many others (eg, X may
be included in sets V and W).
If the sets were nodes in a directed graph, the graph would be
acyclic, but not a tree. I believe this is called a lattice(?).
How can we efficiently model this lattice of sets either using SQL
or some other technique? Specifically:
% Add or remove members/subsets from a set and have the changes
bubble up the lattice efficiently.
% Find all members of a set X, efficiently traversing subsets. In
other words, find all nodes/leaves of the subtree rooted at X
% For a given set X, find all sets that contain X as a subset,
directly or indirectly (eg, if Y contains X, and Z contains Y, return
both Y and Z). In other words, find all the nodes that have X as a
sub-node.
The most promising lead I'd found was:
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
(which is why I'm cc'ing the MySQL list), but this only works with
trees, and I couldn't figure out how to modify it for lattices.
--
We're just a Bunch Of Regular Guys, a collective group that's trying
to understand and assimilate technology. We feel that resistance to
new ideas and technology is unwise and ultimately futile.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: True multi user?
As Jerry mentioned, you can use temporary tables. Temp tables are unique to the login session, so each usr logged in could create a table called data with conflict. But temp tables are just that, once the session is done, the temp table is dropped. If you need persistant tables, which I think is an incredibly bad idea in your case, you can name the table based on the user name and just alias the table name in query: select * from usernametable as data; I think persistent tables is a very bad idea because you can very easily and quickly create thousands of tables that would eventually overwhelm mysql. Not to mentioned it's a bad idea to give create table privileges out to everyone. Another alternative is to create a single table called data with a field called userid. Then each use owns a certain set of records in that table that you can select on. You would need to setup something to periodically clean out old entries. Implementing this with merge tables would ease the cleanup process. - Original Message - From: JugLugs [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, May 30, 2007 2:27 PM Subject: RE: True multi user? There is a master document on a server which references a table called 'data' within database. I need users to query many tables and produce a data source called 'data' which the document can then reference. The problem is, that this 'data' table could easily get overwritten by another user. Creating a number of similar documents (one for each user) is not really practical as there may be an infinite number of users. Also this is a web based application and therefore I would have difficulty in writing a datasource (csv or something) to the users hard drive without user intervention. Regards JugLugs -- View this message in context: http://www.nabble.com/True-multi-user--tf3841867.html#a10879747 Sent from the MySQL - General mailing list archive at Nabble.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]
Replication Complication
Howdy, Having trouble getting Slave_IO_Running to start. The master-slave system was working before, but I think the master and slave binlogs have been reset (truncated). I'm unsure how to proceed. Am I SOL? David
stored procedure not working in legacy ASP
Hi How do you get multiple record sets from a stored procedure in legacy ASP? It doesn't seem to work for us. The question is how to return multiple record sets from a single stored procedure which myodbc doesn't seem to support? set rs = connection.execute(strSQLsp) If not rs.EOF then response.write rs(1) End if set rs = rs.NextRecordset If not rs.EOF then response.write rs(2) End if We only get the first response.write Thanks -- David Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: True multi user?
mmm I think i need some sort of compromise here... Yes setting up a table called data with a userid is easily done but can I 'filter' these within the master document? - perhaps 'feed' the document the userid (somehow) and then with a bit of manipulation via document code filter the result set ?? can i do that in a mailmerge ? Also i do have to consider that some users will be using microsoft to do the mailmerge but others will be using openoffice! I think this is one big can of worms - I think the only solution is to export the data to a csv, have the user save the file to a specified area and filename and then use this (data.csv) as the datasource. i am still hoping there is a better way to :) Thanks, Juglugs -- View this message in context: http://www.nabble.com/True-multi-user--tf3841867.html#a10881116 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Complication
Howdy, Having trouble getting Slave_IO_Running to start. The master-slave system was working before, but I think the master and slave binlogs have been reset (truncated). I'm unsure how to proceed. Am I SOL? mysql Load Data From Master; seems to have done it. David
RE: True multi user?
It appears that the master document is going to be processed on individual PCs. In that case, I think that downloading the merge data source is the best solution. It might require some training, but doing a mail merge in MS Word is not a huge challenge. You were thinking of using an ODBC connection as the data source? I don't know how you could possibly incorporate a user id into an MS Word master document (and not even into the document itself, but into the data source management) without a huge amount of programming. In fact, I think you'd wind up rewriting the merge engine in VBA before you were done. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: JugLugs [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 30, 2007 3:41 PM To: mysql@lists.mysql.com Subject: Re: True multi user? mmm I think i need some sort of compromise here... Yes setting up a table called data with a userid is easily done but can I 'filter' these within the master document? - perhaps 'feed' the document the userid (somehow) and then with a bit of manipulation via document code filter the result set ?? can i do that in a mailmerge ? Also i do have to consider that some users will be using microsoft to do the mailmerge but others will be using openoffice! I think this is one big can of worms - I think the only solution is to export the data to a csv, have the user save the file to a specified area and filename and then use this (data.csv) as the datasource. i am still hoping there is a better way to :) Thanks, Juglugs -- View this message in context: http://www.nabble.com/True-multi-user--tf3841867.html#a10881116 Sent from the MySQL - General mailing list archive at Nabble.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: True multi user?
I agree i will have to request that the user downloads a datasource and saves it as 'c:\data.csv' and point a document to c:\data.csv otherwise i will need to incorporate a lot of vba/c# into the document Thanks for your help just thought there mat be an 'easy' way Cheers Juglugs -- View this message in context: http://www.nabble.com/True-multi-user--tf3841867.html#a10881724 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help converting trigger
I have the following trigger in Postgresql, how can we do this in Mysql? CREATE TRIGGER tr_encounter_lab_order_upd AFTER UPDATE ON encounter_lab_order FOR EACH ROW EXECUTE PROCEDURE tr_encounter_lab_order_upd_trig_func(); CREATE OR REPLACE FUNCTION tr_encounter_lab_order_upd_trig_func() RETURNS trigger AS $BODY$ begin if (new.DX_CODE != old.DX_CODE or (new.DX_CODE is null and old.DX_CODEis not null) or ( new.DX_CODE is not null and old.DX_CODE is null)) then update encounter_order set dx_code = new.dx_code where encounter_id = new.encounter_id and order_id = new.order_id; end if; return new; end; $BODY$ LANGUAGE 'plpgsql' VOLATILE; Can Mysql handle this if logic needed by this trigger, or only fire if a specific column is updated? Thanks, Chris
Re: stored procedure not working in legacy ASP
Surely, you don't have legacy stored procedure in ASP under MySQL? are you sure this is the right list to be asking? - michael On 5/30/07, Critters [EMAIL PROTECTED] wrote: Hi How do you get multiple record sets from a stored procedure in legacy ASP? It doesn't seem to work for us. The question is how to return multiple record sets from a single stored procedure which myodbc doesn't seem to support? set rs = connection.execute(strSQLsp) If not rs.EOF then response.write rs(1) End if set rs = rs.NextRecordset If not rs.EOF then response.write rs(2) End if We only get the first response.write Thanks -- David Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Efficiently modeling sets and subsets in lattice-like structure
Hi Kelly,
I'm pretty sure there's no universal efficient solution to this
problem, this must be the well known answer
you are looking for. The pure mathematics of 'very hard' could be very
hard to express, though.
So before you choose a solution you need to find out more about the
actual graphs you're working
On the other hand there are plenty of possible approaches to the graph
storage problem.
They do seem to concentrate on trees (XML is one of the better ones).
So you could approach the graph representation
problem as a tree of trees, or as a set of separately stored trees
joined logically over common nodes.
I doubt you could find many solutions in that direction.
Then since a graph is a binary relation over a set of nodes you could
represent it as a 2 field relational table in an obvious way.
SQl isn't very good with trees, though.
In any case on model 'a Bunch Of Regular Guys' could code themselves (I
did it once!) is a representation
of the binary relation as a bit matrix. You'll need N*N bits where N is
the number of nodes so it's not going to scale well
but if you do happen to have fewer than 90K nodes (1G matrix) you could
use it directly. If you have more nodes but
the number of ones in the matrix is not too high you could compress the
matrix somewhat at the expense of extraction/update performance.
If you have many nodes with lots of connections you're out of luck
anyway.
For persistent storage in this case you could use a relational table in
few obvious ways, or just a file.
Example:
7 11 15 X Y 15 20 25 Z 14 30
1 2 3 4 5 6 7 8 9 10 11 12
maintaining this array is a separate issue (or non issue), here a two
field table in an RDBMS is definitely appropriate.
1 2 3 4 5 6 7 8 9 10 11 12
1 all zeroes
2 all zeroes
3 all zeroes
4 1 1 1 0 1 0 ... # 4 is X which is 7 11 15 Y
5 0 0 0 0 0 0 1 1 1 1 0 0 # 5 is Y which is 15 20 25 Z
6 and so on
7
8
9
10
11
12
now if your sets are ordered and {7,11} is not the same as {11,7} this
isn't going to work at all...
Thanks,
Michael
-Original Message-
From: Kelly Jones [EMAIL PROTECTED]Bunch Of Regular Guys
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED];
[EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Wed, 30 May 2007 2:53 pm
Subject: Efficiently modeling sets and subsets in lattice-like structure
Apologies for the mass cross-posting: I haven't been able to find a
single
answer or reference for the problem below (googling didn't help), and
was hoping someone could point me to something helpful. I'm convinced
there's a well-known answer here that I just can't find :(
We're modeling a collection of (finite mathematical) sets, where sets
may contain each other as subsets.
For example, set X may be defined as {7,11,15} + Y meaning X
contains 7, 11, 15, and all the members of set Y. Set Y could then be
{15,20,25} + Z, and Z might be {7,14,30} with no subsets.
Of course, no set includes itself, directly or indirectly.
However, one set may include many other subsets (eg, X may include
both Y and Z), and one set may be included in many others (eg, X may
be included in sets V and W).
If the sets were nodes in a directed graph, the graph would be
acyclic, but not a tree. I believe this is called a lattice(?).
How can we efficiently model this lattice of sets either using SQL
or some other technique? Specifically:
% Add or remove members/subsets from a set and have the changes
bubble up the lattice efficiently.
% Find all members of a set X, efficiently traversing subsets. In
other words, find all nodes/leaves of the subtree rooted at X
% For a given set X, find all sets that contain X as a subset,
directly or indirectly (eg, if Y contains X, and Z contains Y, return
both Y and Z). In other words, find all the nodes that have X as a
sub-node.
The most promising lead I'd found was:
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
(which is why I'm cc'ing the MySQL list), but this only works with
trees, and I couldn't figure out how to modify it for lattices.
-- We're just a Bunch Of Regular Guys, a collective group that's trying
to understand and assimilate technology. We feel that resistance to
new ideas and technology is unwise and ultimately futile.
-- MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
Check Out the new free AIM(R) Mail -- 2 GB of storage and
industry-leading spam and email virus protection.
=0
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Connection time out/slow response from server
Hi, Running 4.1.22 created from FreeBSD ports on FreeBSD 5.5 . I run under TCP Wrappers. Even if 1 foreign machine attempts to contact us on the tcp port, the database becomes incredibly sluggish or unresponsive at all. Is there something I can do to prevent this from happening? I'm using TCPWrappers to help control access to the database instead of going to firewall rules. I'd rather not do this, defeats what TCPWrappers was all about. Thanks, Tuc -- 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)
I know I've run into this before, and it was a build issue. We build from source on redhat linux, so it may not be related to your issue. Anyways, I just did a test and built mysql from source with the flag --with-mysqld-ldflags=-all-static. I end up with the process spawning mysqld: ps -eaf | grep -c my 12 I removed that flag and rebuilt again, and now I have 2 processes. Just for reference: /configure --prefix=/usr/pkg/mysql5_test \ --with-libwrap=/usr/pkg/tcpd \ --with-mysqld-user=mydaemon \ --without-debug \ --with-unix-socket-path=/var/mysql/mysql.sock \ --with-client-ldflags=-all-static \ --enable-shared \ --enable-thread-safe-client \ --with-extra-charsets=none Scott On Wed, 2007-05-30 at 18:29 +0200, Quentin Gouedard wrote: Oh and by the way mysql works just fine on that machine. You can run queries without any problems. Only it keeps spawning new processes over again. It takes about 3h before the machine starts having problems due to memory getting full of useless mysql processes. I just got a new machine for my site, and it's happenning there too (although on none of my 5 other boxes), which is good cause i can do some more extensive testing before i actually use it. Another thing is, mysql won't stop. /etc/init.d/mysql stop just hangs. There are some processes that need to be killed manually in the end. I'm guessing this could have to do with the process owner being root. Some similar problems are reported here though: http://forums.gentoo.org//viewtopic-t-544730-highlight-mysql.html On 5/30/07, Quentin Gouedard [EMAIL PROTECTED] wrote: Merci Geoffroy, starting from the command prompt shows: # /usr/sbin/mysqld --console --verbose --basedir=/usr --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock 070530 18:01:28 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.0.38-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Gentoo Linux mysql-5.0.38 However running a ps already shows multiple mysqld processes. Something i had not noticed so far: # ps -ef | grep mysqld mysql25752 27831 0 18:01 pts/2 00:00:00 /usr/sbin/mysqld --console --verbose --basedir=/usr --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock root 16560 25752 0 18:01 pts/2 00:00:00 /usr/sbin/mysqld --console --verbose --basedir=/usr --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock root 16560 25752 0 18:01 pts/2 00:00:00 /usr/sbin/mysqld --console --verbose --basedir=/usr --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock root 23390 16560 0 18:01 pts/2 00:00:00 /usr/sbin/mysqld --console --verbose --basedir=/usr --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock(etc.) the first process created is the only one with mysql owner. All the subsequent processes are own by root, and have as parent one of the previously created processes (not always the first one). Not sure if that helps in understanding this though. Thanks anyway for your help. On 5/30/07, Geoffroy Cogniaux [EMAIL PROTECTED] wrote: 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
Re: stored procedure not working in legacy ASP
The stored procedure is in MySQL, but when called using ASP it fails to return more than the first record. Anyone? -- Dave Michael Dykman wrote: Surely, you don't have legacy stored procedure in ASP under MySQL? are you sure this is the right list to be asking? - michael On 5/30/07, Critters [EMAIL PROTECTED] wrote: Hi How do you get multiple record sets from a stored procedure in legacy ASP? It doesn't seem to work for us. The question is how to return multiple record sets from a single stored procedure which myodbc doesn't seem to support? set rs = connection.execute(strSQLsp) If not rs.EOF then response.write rs(1) End if set rs = rs.NextRecordset If not rs.EOF then response.write rs(2) End if We only get the first response.write Thanks -- David Scott -- 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: stored procedure not working in legacy ASP
Would you care to send the source of that procedure plus tell us why you believe it works when called by something other than C#? On 5/30/07, Critters [EMAIL PROTECTED] wrote: The stored procedure is in MySQL, but when called using ASP it fails to return more than the first record. Anyone? -- Dave Michael Dykman wrote: Surely, you don't have legacy stored procedure in ASP under MySQL? are you sure this is the right list to be asking? - michael On 5/30/07, Critters [EMAIL PROTECTED] wrote: Hi How do you get multiple record sets from a stored procedure in legacy ASP? It doesn't seem to work for us. The question is how to return multiple record sets from a single stored procedure which myodbc doesn't seem to support? set rs = connection.execute(strSQLsp) If not rs.EOF then response.write rs(1) End if set rs = rs.NextRecordset If not rs.EOF then response.write rs(2) End if We only get the first response.write Thanks -- David Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: stored procedure not working in legacy ASP
[snip] The stored procedure is in MySQL, but when called using ASP it fails to return more than the first record. Anyone? [/snip] You need a while loop. Does the SP work from the command line properly? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Data types and reading in data
First of all I am very much a newbie with MySQL. I am trying to create some tables and then read in some data from a .csv file using load data infile 'filename.csv' The data in the .csv file has actually come from another database system. I have two problems which I haven't been able to find my way around. In many cases there are missing values for particular fields. If I declare that field or column to be CHAR(m) then I can read the data in using the load command. However in a number of cases I want the field to be INT of some sort. If in the create table statement I declare the column to be INT however, I am unable to successfully read the data in. This is curious because I know it was written out from the source database with an INT declaration. I have some dates which are in the incorrect format, as dd/mm/. Is there any way of having them recognised as dates? For both of these problems my intended workaround is to create a new column from the problem column which has the attributes I want. I am not sure how possible that is at this point, being very inexperienced with MySQL. Any advice would be most welcome, if only a pointer to the appropriate location in the documentation. David Scott _ David Scott Department of Statistics, Tamaki Campus The University of Auckland, PB 92019 Auckland 1142,NEW ZEALAND Phone: +64 9 373 7599 ext 86830 Fax: +64 9 373 7000 Email: [EMAIL PROTECTED] Graduate Officer, Department of Statistics Director of Consulting, Department of Statistics -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL in multi-threaded environment
Thanks! but I am already using table locks...any other thoughts? Cheers, Rajan On 5/29/07, sangprabv [EMAIL PROTECTED] wrote: Maybe need to lock the table Regards Willy - Original Message - From: Ace [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, May 30, 2007 12:22 PM Subject: MySQL in multi-threaded environment 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 in multi-threaded environment
Hi, What is your table engine? AFAIK it is recommended to use InnoDB than MyISAM. Regards Willy - Original Message - From: Ace To: sangprabv Cc: mysql@lists.mysql.com Sent: Thursday, May 31, 2007 11:48 AM Subject: Re: MySQL in multi-threaded environment Thanks! but I am already using table locks...any other thoughts? Cheers, Rajan On 5/29/07, sangprabv [EMAIL PROTECTED] wrote: Maybe need to lock the table Regards Willy - Original Message - From: Ace [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, May 30, 2007 12:22 PM Subject: MySQL in multi-threaded environment 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 in multi-threaded environment
Yes, I am using InnoDB. On 5/30/07, sangprabv [EMAIL PROTECTED] wrote: Hi, What is your table engine? AFAIK it is recommended to use InnoDB than MyISAM. Regards Willy - Original Message - *From:* Ace [EMAIL PROTECTED] *To:* sangprabv [EMAIL PROTECTED] *Cc:* mysql@lists.mysql.com *Sent:* Thursday, May 31, 2007 11:48 AM *Subject:* Re: MySQL in multi-threaded environment Thanks! but I am already using table locks...any other thoughts? Cheers, Rajan On 5/29/07, sangprabv [EMAIL PROTECTED] wrote: Maybe need to lock the table Regards Willy - Original Message - From: Ace [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, May 30, 2007 12:22 PM Subject: MySQL in multi-threaded environment 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 -- I may be miles away... but I am just a mail away so keep mailing...!!! Cheers, Rajan
