Re: LOAD INDEX INTO CACHE problem
Hi! The change of key_buffer_size worked like a charm. Thank you ! CheHax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql tuning - server Crash 1
Donny, - Original Message - From: Donny Simonton [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, February 28, 2005 2:00 AM Subject: RE: Mysql tuning - server Crash 1 Heikki, I sent this to a few friends of mine who work on fedora quite a bit. As a general note, Fedora Cores are not considered stable. None of them wanted to officially comment, but just asked that you show proof. Especially since most of RH4 is Fedora. I am sorry, I fell victim of statements like this: http://fedora.redhat.com/ The Fedora Project is one of the sources for new technologies and enhancements that may be incorporated into Red Hat Enterprise Linux in the future. Red Hat Enterprise Linux is the solution that provides a robust, stable operating system supported by Red Hat, Inc. and a wide variety of independent software vendors (ISVs). For production use, support, service level agreements (SLAs), and ISV support, we proudly ask that you look at Red Hat Enterprise Linux. Now that I Googled fedora + mysql + crash, I noticed that Fedora does not seem to have any more problems than other Linux distros. This bug report suggests a bug in an early version of Fedora Core 1/NPTL/AMD64: bugs.mysql.com/bug.php?id=4570 Fedora moved to NPTL early. I know I've used FC1, FC2, and FC3. And on fc2 and fc3 the 32 and 64 bit versions. And besides certain ide problems which I would never use on a mysql server anyway, we've never had any problems with fedora at all. They wanted me to send you some of the benchmarks comparing Fedora, Suse, and some of the other distros using mysql. But the site they sent me which shows fedora beating all of them is currently down. Oh well. Donny Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Sunday, February 27, 2005 2:58 PM To: mysql@lists.mysql.com Subject: Re: Mysql tuning - server Crash 1 Max, - Original Message - From: Deluxe Web [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Sunday, February 27, 2005 2:17 PM Subject: Re: Mysql tuning - server Crash 1 Hi Heikki, http://lists.mysql.com/mysql/180583 http://lists.mysql.com/mysql/180581 Running fedora core1 Mysql: 4.0.16-Max-log If you check http://lists.mysql.com/mysql/180583 (.err) you will see errors. does that contain ALL relevant info? Let me know if you have any questions. You should upgrade to 4.1.10. We might get better error diagnostics then. As a general note, Fedora Cores are not considered stable. Thank you. I appreciate your help!! Max Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- 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: FULLTEXT Exact phrase search including quotes
Alright, my example phrase wasn't a good one. Let's try this now:
We have a table with company activities.
On of them is :
Plastique ou carton and some others are in Plastic, carton
Plastique Plastic (different language)
What we want, is to find exactly Plastique ou carton and not the other ones.
So here are some requests:
This first one obviously returns one good result:
SELECT *
FROM `tbltopic`
where match (title) AGAINST ('+plastique +ou +carton' in boolean mode)
This second one too :
SELECT *
FROM `tbltopic`
where match (title) AGAINST ('plastique ou carton' in boolean mode)
Now suppose that in my table, plastique ou carton are between
double quotes. I have a search engine in which users can enter an
exact phrase search. So in my code I insert this phrase they want to
find between double quotes in my fulltext search, just as in example
2. But what if they want to find plastique ou carton ?
If the value of my field is :
Fabrication de Plastique ou carton
and I want to search for this exact phrase, including double quotes,
what should my request be ?
Thanks !
CheHax
On Fri, 25 Feb 2005 16:32:33 +0200, Gleb Paharenko
[EMAIL PROTECTED] wrote:
Hello.
At first: from your phrase with default values for the FULLTEXT
parameters there is the only one meaningful word - football.
Because 'I', 'on', 'TV' has less than 3 characters. 'like' is
in the stopword list. Quotes '' - are skipped from the search.
What query do you use to search? Does my example work on your system:
mysql select * from ft where match(a) against('I football on TV');
+-+
| a |
+-+
| I like football on TV |
+-+
CREATE TABLE `ft` (
`a` text,
FULLTEXT KEY `a` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
mysql select * from ft ;
+--+
| a|
+--+
| clear manual |
| greate manual|
| caa0b1661f71f8c9395e2ab87a6f5245 |
| f99b66713ee69d05f153cc78846686f5 |
| 6a86aa82e04244a73f2139021d5fc723 |
| 6a86aa82e04244a73f2139021d5fc723 |
| 6a86aa82e04244a73f2139021d5fc723 |
| e5c26cd3db77db54fa3d98bfcca2d019 |
| e5c26cd3db77db54fa3d98bfcca2d019 |
| e5c26cd3db77db54fa3d98bfcca2d019 |
| e5c26cd3db77db54fa3d98bfcca2d019 |
| b9d9fe513c50cc9d3a391bc6d3ccc10d |
| b9d9fe513c50cc9d3a391bc6d3ccc10d |
| I like football on TV |
+--+
HMax [EMAIL PROTECTED] wrote:
Hi list,
I'm trying to figure out how to use the exact phrase search in
fulltext boolean mode when the phease to search includes double
quotes.
For instance, what if I want to search this exact phrase :
I like football on TV
I think I've tried all the solution I'm aware of without any results.
Any help would be appreciated!
Thanks
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql tuning - server Crash 1
Max, maybe the 640 connection piled up because mysqld was hung? It is not very likely that all those threads would have taken 3 MB of memory. Usually they take much less. The best advice is to upgrade to a recent 4.0.xx or 4.1.xx version of MySQL, which may print more info in a hang. An upgrade to a more recent Fedora Core might also help. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Original Message - From: Deluxe Web [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, February 28, 2005 9:13 AM Subject: Re: Mysql tuning - server Crash 1 In my .err. file I have key_buffer_size=8388600 read_buffer_size=131072 max_used_connections=652 max_connections=1000 threads_connected=640 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2184184 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. --- With my 2GB RAM I think I reached the maximum. Shall I consider linux swap memory, which is of 2GB? Please advise. Can you help me undertand if an increase of RAM will fix the problem. On Sun, 27 Feb 2005 18:00:29 -0600, Donny Simonton [EMAIL PROTECTED] wrote: Heikki, I sent this to a few friends of mine who work on fedora quite a bit. As a general note, Fedora Cores are not considered stable. None of them wanted to officially comment, but just asked that you show proof. Especially since most of RH4 is Fedora. I know I've used FC1, FC2, and FC3. And on fc2 and fc3 the 32 and 64 bit versions. And besides certain ide problems which I would never use on a mysql server anyway, we've never had any problems with fedora at all. They wanted me to send you some of the benchmarks comparing Fedora, Suse, and some of the other distros using mysql. But the site they sent me which shows fedora beating all of them is currently down. Oh well. Donny -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Sunday, February 27, 2005 2:58 PM To: mysql@lists.mysql.com Subject: Re: Mysql tuning - server Crash 1 Max, - Original Message - From: Deluxe Web [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Sunday, February 27, 2005 2:17 PM Subject: Re: Mysql tuning - server Crash 1 Hi Heikki, http://lists.mysql.com/mysql/180583 http://lists.mysql.com/mysql/180581 Running fedora core1 Mysql: 4.0.16-Max-log If you check http://lists.mysql.com/mysql/180583 (.err) you will see errors. does that contain ALL relevant info? Let me know if you have any questions. You should upgrade to 4.1.10. We might get better error diagnostics then. As a general note, Fedora Cores are not considered stable. Thank you. I appreciate your help!! Max Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- 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] -- Deluxe Web [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 tuning - server Crash 1
Hi I understand.. I should switch to debian :) but in the meantime what about the innodb buffer pull size Have you seen my variables. I had 8M? Can this be the bottle neck? On Mon, 28 Feb 2005 11:34:31 +0200, Heikki Tuuri [EMAIL PROTECTED] wrote: Max, maybe the 640 connection piled up because mysqld was hung? It is not very likely that all those threads would have taken 3 MB of memory. Usually they take much less. The best advice is to upgrade to a recent 4.0.xx or 4.1.xx version of MySQL, which may print more info in a hang. An upgrade to a more recent Fedora Core might also help. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Original Message - From: Deluxe Web [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, February 28, 2005 9:13 AM Subject: Re: Mysql tuning - server Crash 1 In my .err. file I have key_buffer_size=8388600 read_buffer_size=131072 max_used_connections=652 max_connections=1000 threads_connected=640 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2184184 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. --- With my 2GB RAM I think I reached the maximum. Shall I consider linux swap memory, which is of 2GB? Please advise. Can you help me undertand if an increase of RAM will fix the problem. On Sun, 27 Feb 2005 18:00:29 -0600, Donny Simonton [EMAIL PROTECTED] wrote: Heikki, I sent this to a few friends of mine who work on fedora quite a bit. As a general note, Fedora Cores are not considered stable. None of them wanted to officially comment, but just asked that you show proof. Especially since most of RH4 is Fedora. I know I've used FC1, FC2, and FC3. And on fc2 and fc3 the 32 and 64 bit versions. And besides certain ide problems which I would never use on a mysql server anyway, we've never had any problems with fedora at all. They wanted me to send you some of the benchmarks comparing Fedora, Suse, and some of the other distros using mysql. But the site they sent me which shows fedora beating all of them is currently down. Oh well. Donny -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Sunday, February 27, 2005 2:58 PM To: mysql@lists.mysql.com Subject: Re: Mysql tuning - server Crash 1 Max, - Original Message - From: Deluxe Web [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Sunday, February 27, 2005 2:17 PM Subject: Re: Mysql tuning - server Crash 1 Hi Heikki, http://lists.mysql.com/mysql/180583 http://lists.mysql.com/mysql/180581 Running fedora core1 Mysql: 4.0.16-Max-log If you check http://lists.mysql.com/mysql/180583 (.err) you will see errors. does that contain ALL relevant info? Let me know if you have any questions. You should upgrade to 4.1.10. We might get better error diagnostics then. As a general note, Fedora Cores are not considered stable. Thank you. I appreciate your help!! Max Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- 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] -- Deluxe Web [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] -- Deluxe Web [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql tuning - server Crash 1
Max, - Original Message - From: Deluxe Web [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, February 28, 2005 11:44 AM Subject: Re: Mysql tuning - server Crash 1 Hi I understand.. I should switch to debian :) but in the meantime what about the innodb buffer pull size Have you seen my variables. I had 8M? Can this be the bottle neck? yes it may be. Please see: http://dev.mysql.com/doc/mysql/en/innodb-configuration.html --Heikki On Mon, 28 Feb 2005 11:34:31 +0200, Heikki Tuuri [EMAIL PROTECTED] wrote: Max, maybe the 640 connection piled up because mysqld was hung? It is not very likely that all those threads would have taken 3 MB of memory. Usually they take much less. The best advice is to upgrade to a recent 4.0.xx or 4.1.xx version of MySQL, which may print more info in a hang. An upgrade to a more recent Fedora Core might also help. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Original Message - From: Deluxe Web [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, February 28, 2005 9:13 AM Subject: Re: Mysql tuning - server Crash 1 In my .err. file I have key_buffer_size=8388600 read_buffer_size=131072 max_used_connections=652 max_connections=1000 threads_connected=640 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2184184 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. --- With my 2GB RAM I think I reached the maximum. Shall I consider linux swap memory, which is of 2GB? Please advise. Can you help me undertand if an increase of RAM will fix the problem. On Sun, 27 Feb 2005 18:00:29 -0600, Donny Simonton [EMAIL PROTECTED] wrote: Heikki, I sent this to a few friends of mine who work on fedora quite a bit. As a general note, Fedora Cores are not considered stable. None of them wanted to officially comment, but just asked that you show proof. Especially since most of RH4 is Fedora. I know I've used FC1, FC2, and FC3. And on fc2 and fc3 the 32 and 64 bit versions. And besides certain ide problems which I would never use on a mysql server anyway, we've never had any problems with fedora at all. They wanted me to send you some of the benchmarks comparing Fedora, Suse, and some of the other distros using mysql. But the site they sent me which shows fedora beating all of them is currently down. Oh well. Donny -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Sunday, February 27, 2005 2:58 PM To: mysql@lists.mysql.com Subject: Re: Mysql tuning - server Crash 1 Max, - Original Message - From: Deluxe Web [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Sunday, February 27, 2005 2:17 PM Subject: Re: Mysql tuning - server Crash 1 Hi Heikki, http://lists.mysql.com/mysql/180583 http://lists.mysql.com/mysql/180581 Running fedora core1 Mysql: 4.0.16-Max-log If you check http://lists.mysql.com/mysql/180583 (.err) you will see errors. does that contain ALL relevant info? Let me know if you have any questions. You should upgrade to 4.1.10. We might get better error diagnostics then. As a general note, Fedora Cores are not considered stable. Thank you. I appreciate your help!! Max Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- 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] -- Deluxe Web [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] -- Deluxe Web [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]
how can I do instead of using subselects?
Hello everyone, I have a problem with a few sql-queries because I have written the questions with subselect statements and then will they not work on older MySQL DB:s. Can any one help me to say haw to solve it without subselect! Today looks the questions like this: SELECT (SELECT count(SUB.S_ID) FROM SUB LEFT JOIN SUBS ON SUBS.S_ID = SUB.S_ID) /(SELECT COUNT(*) from SUB); SELECT COUNT(SUBS.NEW_ID)+(SELECT COUNT(SUBS_D.NEW_ID) FROM SUBS_D WHERE SUBS.NEW_ID=SUBS_D.NEW_ID) FROM SUBS WHERE SUBS.NEW_ID=1; SELECT (SELECT count(*) from SUBS)-(SELECT count(SUBS.S_ID) FROM SUB LEFT JOIN SUBS ON SUBS.S_ID = SUB.S_ID)+(SELECT count(*) FROM SUBS_D); Another question is how do I do when I want to compare two tables, normally is it no problem to get a list with rows that exist i both tables, but how do I do if I want to to have the diffrence listed instead, the rows that only appears in one of the tables? Thanks in advance! /Joppe -- ___ Sign-up for Ads Free at Mail.com http://promo.mail.com/adsfreejump.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql tuning - server Crash 1
I have increased innodb buffer pull size to 1G (50% of my 2G ram) What do you suggest (size) for innodb_data_file_path I'm note sure about this variable usage in mysql performance. Thanks. On Mon, 28 Feb 2005 11:47:27 +0200, Heikki Tuuri [EMAIL PROTECTED] wrote: Max, - Original Message - From: Deluxe Web [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, February 28, 2005 11:44 AM Subject: Re: Mysql tuning - server Crash 1 Hi I understand.. I should switch to debian :) but in the meantime what about the innodb buffer pull size Have you seen my variables. I had 8M? Can this be the bottle neck? yes it may be. Please see: http://dev.mysql.com/doc/mysql/en/innodb-configuration.html --Heikki On Mon, 28 Feb 2005 11:34:31 +0200, Heikki Tuuri [EMAIL PROTECTED] wrote: Max, maybe the 640 connection piled up because mysqld was hung? It is not very likely that all those threads would have taken 3 MB of memory. Usually they take much less. The best advice is to upgrade to a recent 4.0.xx or 4.1.xx version of MySQL, which may print more info in a hang. An upgrade to a more recent Fedora Core might also help. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Original Message - From: Deluxe Web [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, February 28, 2005 9:13 AM Subject: Re: Mysql tuning - server Crash 1 In my .err. file I have key_buffer_size=8388600 read_buffer_size=131072 max_used_connections=652 max_connections=1000 threads_connected=640 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2184184 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. --- With my 2GB RAM I think I reached the maximum. Shall I consider linux swap memory, which is of 2GB? Please advise. Can you help me undertand if an increase of RAM will fix the problem. On Sun, 27 Feb 2005 18:00:29 -0600, Donny Simonton [EMAIL PROTECTED] wrote: Heikki, I sent this to a few friends of mine who work on fedora quite a bit. As a general note, Fedora Cores are not considered stable. None of them wanted to officially comment, but just asked that you show proof. Especially since most of RH4 is Fedora. I know I've used FC1, FC2, and FC3. And on fc2 and fc3 the 32 and 64 bit versions. And besides certain ide problems which I would never use on a mysql server anyway, we've never had any problems with fedora at all. They wanted me to send you some of the benchmarks comparing Fedora, Suse, and some of the other distros using mysql. But the site they sent me which shows fedora beating all of them is currently down. Oh well. Donny -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Sunday, February 27, 2005 2:58 PM To: mysql@lists.mysql.com Subject: Re: Mysql tuning - server Crash 1 Max, - Original Message - From: Deluxe Web [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Sunday, February 27, 2005 2:17 PM Subject: Re: Mysql tuning - server Crash 1 Hi Heikki, http://lists.mysql.com/mysql/180583 http://lists.mysql.com/mysql/180581 Running fedora core1 Mysql: 4.0.16-Max-log If you check http://lists.mysql.com/mysql/180583 (.err) you will see errors. does that contain ALL relevant info? Let me know if you have any questions. You should upgrade to 4.1.10. We might get better error diagnostics then. As a general note, Fedora Cores are not considered stable. Thank you. I appreciate your help!! Max Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- 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] -- Deluxe Web [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:
User that can create another user - which privileges must be granted
...to that user, that he can create new users, e.g. which administrative privileges must be granted to that user. Thanx. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange Issues
Hi, After spending about a week researching the use of hex [string] values I am still as lost as I was before. What is the point of placing Unicode data into a column if I have to convert it when going both in and out? All the documentation I read on MySQL indicates it supports Unicode I cannot afford the high overhead of having to parse each piece of text every time I need to display it on a page. Please I am lost! To me it should be a simple task. Insert 65k of text in Unicode or even simple ASCII format into a column of type 'text' by using a GUI client like SQLyog or MySQLCC and then extract it when needed by my web page without loosing everything I put in after a certain character or character sequence. So I have the following questions: 1] Does MySQL handle ASCII text above 128? 2] Does MySQL handle Unicode text? 3] how do I get it to work? If so how (detailed instructions) do I store the information so it does not truncate? Gleb Paharenko wrote: Use the latest release (4.1.10 now) and hexademical values. See: http://dev.mysql.com/doc/mysql/en/hexadecimal-values.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fedora Core 2: upgrade mysql 3 to mysql 4
Hello. I want to upgrade mysql 3 to mysql 4. At first, you should read: http://dev.mysql.com/doc/mysql/en/upgrade.html Search the MySQL lists archives about possible problems which you can expect. It is strongly recommended to make a backup. How to remove mysql3 and install mysql 4? It depends on what kind of distribution you have. Probably rpm -U would help for the rpm distribution. Jerry Swanson [EMAIL PROTECTED] wrote: I want to upgrade mysql 3 to mysql 4. Operating System is Fedore Core 2. Is there any package dependency? How to remove mysql3 and install mysql 4? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Linking problems
Hello. g++ -o sqltest sqltest.o -L/usr/lib/mysql -R/usr/lib/mysql -lmysqlclient -lm -lz You should specify the location of libmysqlpp.a and add -lmysqlpp to the list of libraries. Remove -R flag. Look into the mysql++-1.7.28/examples directory. Arthur [EMAIL PROTECTED] wrote: Hi there, guys. I'm new to MySQL++. When I try to compile the first simple example of mysql++ tutorial, the following error occurs: g++ -g -O -Wall -I/usr/include/mysql++ -I/usr/include/mysql -c sqltest.cc In file included from sqltest.cc:3: /usr/include/mysql++/sqlplus.hh:1:2: warning: #warning This header is obsolete.Please use mysql++.h instead. g++ -o sqltest sqltest.o -L/usr/lib/mysql -R/usr/lib/mysql -lmysqlclient -lm -lz g++: unrecognized option `-R/usr/lib/mysql' sqltest.o(.text+0x41): In function `main': /home/arthur/sqltest.cc:8: undefined reference to `mysqlpp::Connection::Connection(char const*, char const*, char const*, char const*, bool)' sqltest.o(.text+0x59):/home/arthur/sqltest.cc:16: undefined reference to `mysqlpp::Connection::query()' sqltest.o(.text+0x8e): In function `main': /usr/include/mysql++/query.h:90: undefined reference to `mysqlpp::Query::store(mysqlpp::SQLQueryParms)' sqltest.o(.text+0xa7):/usr/include/mysql++/query.h:82: undefined reference to `mysqlpp::SQLQuery::str(mysqlpp::SQLQueryParms)' sqltest.o(.text+0x1a1): In function `main': /usr/include/mysql++/row.h:244: undefined reference to `vtable for mysqlpp::Row' sqltest.o(.text+0x1ab):/usr/include/mysql++/row.h:244: undefined reference to `vtable for mysqlpp::Row' sqltest.o(.text+0x2c4):/usr/include/mysql++/row.h:276: undefined reference to `vtable for mysqlpp::Row' sqltest.o(.text+0x2ce):/usr/include/mysql++/row.h:276: undefined reference to `vtable for mysqlpp::Row' sqltest.o(.text+0x4db): In function `main': /usr/include/mysql++/result.h:159: undefined reference to `mysqlpp::ResUse::~ResUse()' sqltest.o(.text+0x8ee): In function `main': /usr/include/mysql++/row.h:276: undefined reference to `vtable for mysqlpp::Row' sqltest.o(.text+0x8f8):/usr/include/mysql++/row.h:276: undefined reference to `vtable for mysqlpp::Row' sqltest.o(.text+0xb15): In function `main': /usr/include/mysql++/result.h:159: undefined reference to `mysqlpp::ResUse::~ResUse()' sqltest.o(.text+0xf38): In function `main': /home/arthur/sqltest.cc:16: undefined reference to `mysqlpp::Connection::~Connection()' sqltest.o(.text+0xf4e):/home/arthur/sqltest.cc:16: undefined reference to `mysqlpp::Connection::~Connection()' sqltest.o(.gnu.linkonce.t._ZN7mysqlpp6ResultD1Ev+0x16): In function `mysqlpp::Result::~Result()': /usr/include/mysql++/result.h:159: undefined reference to `mysqlpp::ResUse::~ResUse()' sqltest.o(.gnu.linkonce.t._ZN7mysqlpp6ResultD0Ev+0x19): In function `mysqlpp::Result::~Result()': /usr/include/mysql++/result.h:159: undefined reference to `mysqlpp::ResUse::~ResUse()' sqltest.o(.gnu.linkonce.t._ZNK7mysqlpp6Result9fetch_rowEv+0xcd): In function `mysqlpp::Result::fetch_row() const': /usr/include/mysql++/row.h:244: undefined reference to `vtable for mysqlpp::Row' sqltest.o(.gnu.linkonce.t._ZNK7mysqlpp6Result9fetch_rowEv+0xd4):/usr/include/mysql++/row.h:244: undefined reference to `vtable for mysqlpp::Row' sqltest.o(.gnu.linkonce.t._ZN7mysqlpp3RowC1EPPcPKNS_6ResUseEPjb+0x1a): In function `mysqlpp::Row::Row(char**, mysqlpp::ResUse const*, unsigned int*, bool)': /usr/include/mysql++/row.h:247: undefined reference to `vtable for mysqlpp::Row' sqltest.o(.gnu.linkonce.t._ZN7mysqlpp3RowC1EPPcPKNS_6ResUseEPjb+0x21):/usr/include/mysql++/row.h:247: undefined reference to `vtable for mysqlpp::Row' sqltest.o(.gnu.linkonce.t._ZN7mysqlpp3RowC1EPPcPKNS_6ResUseEPjb+0x3aa):/usr/include/mysql++/row.h:253: undefined reference to `mysqlpp::Row::size() const' collect2: ld returned 1 exit status make: ** [sqltest] Erro 1 [EMAIL PROTECTED] ~ $ I've read on the FAQ that I need to link the libmysqlclient, but I don't know how to do it. Thank you. Arthur Maciel -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Access Denied For User
Hello. I successufully connected to MySQL server using your parameters in odbc.ini and odbcinst.ini files from php. [unixODBC][MySQL][ODBC 3.51 Driver]Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) You have specified the user and the password in the ODBC configuration files, but your scripts don't use them. I think the problem is in your connection string which you are specifying in the odbc_connect(). Dan Wareham [EMAIL PROTECTED] wrote: Hello, I have a Linux server running Apache, MySQL, unixODBC, MyODBC In my odbcinst.ini file I have the following: [MySQL] Description= ODBC for MySQL Driver = /usr/local/lib/libmyodbc3.so FileUsage = 1 I have setup the following system DSN in the odbc.ini file: [PbDatabase] Description = System DSN Driver = /usr/local/lib/libmyodbc3.so Host = localhost Server = localhost User = username Password = password Port = 3306 Database = Database01 Option = 3 However, when I use the System DSN name in my scripts I get the following error: 350 Native SQL Error Code [unixODBC][MySQL][ODBC 3.51 Driver]Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) The user apacheusr is the username that the Apache webserver is running under. I've tried adding this user to the MySQL user table but the error still shows. However, if I add the Username and Password along with the DSN name to my scripts within my HTML pages then the connection is made and records from the database returned. Any ideas why this might be happening?? Is it a permissions issue with MySQL or with unixODBC or Apache?? Any help would be greatly appreciated. Thanks in advance [EMAIL PROTECTED] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with system tables
Hello. /usr/sbin/mysqld: File '/var/mysql-bin.03' not found (Errcode: 13) [EMAIL PROTECTED] mysql-debug-4.1.10-pc-linux-gnu-i686]$ perror 13 Error code 13: Permission denied I suggest you to check the permissions on your directories. Leo [EMAIL PROTECTED] wrote: [-- text/plain, encoding 7bit, charset: us-ascii, 38 lines --] Hi, I'm working on a Linux Red Hat 7 (core 2.4.2-2), with Apache 2.0.53, PHP 5.0.3 and I'm trying to upgrade MySQL to version 4.1.10 with the rpms (I downloaded all of them). When i try to install, it reports a couple of errors here is screen shot sudo rpm --install -vv --force MySQL*.rpm [...] + mysql_install_db --rpm --user=mysql 050226 12:28:01 [Warning] Asked for 196608 thread stack, but got 126976 /usr/sbin/mysqld: File '/var/mysql-bin.03' not found (Errcode: 13) 050226 12:28:01 [ERROR] Could not use /var/mysql-bin for logging (error 13). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it. 050226 12:28:01 [ERROR] Aborting 050226 12:28:01 [Note] /usr/sbin/mysqld: Shutdown complete Installation of system tables failed! Examine the logs in /var/lib/mysql for more information. You can also try to start the mysqld daemon with: /usr/sbin/mysqld --skip-grant You can use the command line tool /usr/bin/mysql to connect to the mysql database and look at the grant tables: shell /usr/bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in /var/lib/mysql that may be helpful. The latest information about MySQL is available on the web at http://www.mysql.com Please consult the MySQL manual section: 'Problems running mysql_install_db', and the manual section that describes problems on your OS. Another information source is the MySQL email archive. Please check all of the above before mailing us! And if you do mail us, you MUST use the /usr/bin/mysqlbug script! + chown -R mysql /var/lib/mysql + chmod -R og-rw /var/lib/mysql/mysql + /etc/init.d/mysql start - Do you Yahoo!? Yahoo! Mail - Easier than ever with enhanced search. Learn more. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT Exact phrase search including quotes
Hello.
and I want to search for this exact phrase, including double quotes,
You can't do this, because fulltext search operates with words,
and double quotes not a word. Also an order of the sequence of
words doesn't have a sence for a fulltext search.
CheHax [EMAIL PROTECTED] wrote:
Alright, my example phrase wasn't a good one. Let's try this now:
We have a table with company activities.
On of them is :
Plastique ou carton and some others are in Plastic, carton
Plastique Plastic (different language)
What we want, is to find exactly Plastique ou carton and not the other on=
es.
So here are some requests:
=E2=80=A2 This first one obviously returns one good result:
SELECT *
FROM `tbltopic`
where match (title) AGAINST ('+plastique +ou +carton' in boolean mode)
=E2=80=A2 This second one too :
SELECT *
FROM `tbltopic`
where match (title) AGAINST ('plastique ou carton' in boolean mode)
=E2=80=A2 Now suppose that in my table, plastique ou carton are between
double quotes. I have a search engine in which users can enter an
exact phrase search. So in my code I insert this phrase they want to
find between double quotes in my fulltext search, just as in example
2. But what if they want to find plastique ou carton ?
=E2=80=A2 If the value of my field is :
Fabrication de Plastique ou carton
and I want to search for this exact phrase, including double quotes,
what should my request be ?
Thanks !
CheHax
On Fri, 25 Feb 2005 16:32:33 +0200, Gleb Paharenko
[EMAIL PROTECTED] wrote:
Hello.
=20
At first: from your phrase with default values for the FULLTEXT
=20
parameters there is the only one meaningful word - football.
=20
Because 'I', 'on', 'TV' has less than 3 characters. 'like' is
=20
in the stopword list. Quotes '' - are skipped from the search.
=20
What query do you use to search? Does my example work on your system:
=20
mysql select * from ft where match(a) against('I football on TV');
=20
+-+
=20
| a |
=20
+-+
=20
| I like football on TV |
=20
+-+
=20
CREATE TABLE `ft` (
=20
`a` text,
=20
FULLTEXT KEY `a` (`a`)
=20
) ENGINE=3DMyISAM DEFAULT CHARSET=3Dutf8
=20
mysql select * from ft ;
=20
+--+
=20
| a|
=20
+--+
=20
| clear manual |
=20
| greate manual|
=20
| caa0b1661f71f8c9395e2ab87a6f5245 |
=20
| f99b66713ee69d05f153cc78846686f5 |
=20
| 6a86aa82e04244a73f2139021d5fc723 |
=20
| 6a86aa82e04244a73f2139021d5fc723 |
=20
| 6a86aa82e04244a73f2139021d5fc723 |
=20
| e5c26cd3db77db54fa3d98bfcca2d019 |
=20
| e5c26cd3db77db54fa3d98bfcca2d019 |
=20
| e5c26cd3db77db54fa3d98bfcca2d019 |
=20
| e5c26cd3db77db54fa3d98bfcca2d019 |
=20
| b9d9fe513c50cc9d3a391bc6d3ccc10d |
=20
| b9d9fe513c50cc9d3a391bc6d3ccc10d |
=20
| I like football on TV |
=20
+--+
=20
=20
HMax [EMAIL PROTECTED] wrote:
=20
Hi list,
=20
=20
I'm trying to figure out how to use the exact phrase search in
=20
fulltext boolean mode when the phease to search includes double
=20
quotes.
=20
=20
For instance, what if I want to search this exact phrase :
=20
I like football on TV
=20
=20
I think I've tried all the solution I'm aware of without any results.
=20
Any help would be appreciated!
=20
=20
Thanks
=20
=20
--
For technical support contracts, goto https://order.mysql.com/?ref=3Densi=
ta
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
=20
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
=20
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: log-warnings
On 18 Feb 2005, at 16:05, Gleb Paharenko wrote: There is no direct way to load warnings into log files. Just for the archives, I reported this as a bug and it's in the MySQL bug db as having been verified, so I guess now we just hope/wait for a fix in a later version: http://bugs.mysql.com/bug.php?id=8684 Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://www.synchromedia.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
my.cnf and InnoDB
Can you help me figure out the max connections # for a 2GB ram (2GB swap mem) server running myslq 4.0.16-Max-log and fedora core1. [mysqld] max_connections= ? innodb_buffer_pool_size=1G The database size is approx 100 Mb with 1,700++ tables, increasing daily.. ulimit -a core file size(blocks, -c) 0 data seg size (kbytes, -d) unlimited file size (blocks, -f) unlimited max locked memory (kbytes, -l) unlimited max memory size (kbytes, -m) unlimited open files(-n) 1024 pipe size (512 bytes, -p) 8 stack size(kbytes, -s) 10240 cpu time (seconds, -t) unlimited max user processes(-u) 7168 virtual memory(kbytes, -v) unlimited Thank you. Max -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User that can create another user - which privileges must be granted
Hello. GRANT OPTION for adding user, and UPDATE on the mysql database to set the passwords for them. See: http://dev.mysql.com/doc/mysql/en/grant.html http://dev.mysql.com/doc/mysql/en/set-password.html Peter PeterDresden [EMAIL PROTECTED] wrote: ...to that user, that he can create new users, e.g. which administrative privileges must be granted to that user. Thanx. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange Issues
Hello. At first, we should check that there is nothing wrong with the character_set_xxx variables. Please send us the output of the following statement: show variables like '%char%'; Does the problem remain if you are making the query using a mysql command line client? Do you use the latest release (4.1.10 now)? Rob Cochrane [EMAIL PROTECTED] wrote: Hi, After spending about a week researching the use of hex [string] values I am still as lost as I was before. What is the point of placing Unicode data into a column if I have to convert it when going both in and out? All the documentation I read on MySQL indicates it supports Unicode I cannot afford the high overhead of having to parse each piece of text every time I need to display it on a page. Please I am lost! To me it should be a simple task. Insert 65k of text in Unicode or even simple ASCII format into a column of type 'text' by using a GUI client like SQLyog or MySQLCC and then extract it when needed by my web page without loosing everything I put in after a certain character or character sequence. So I have the following questions: 1] Does MySQL handle ASCII text above 128? 2] Does MySQL handle Unicode text? 3] how do I get it to work? If so how (detailed instructions) do I store the information so it does not truncate? Gleb Paharenko wrote: Use the latest release (4.1.10 now) and hexademical values. See: http://dev.mysql.com/doc/mysql/en/hexadecimal-values.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange Issues (UTF-8)
To me it should be a simple task. Insert 65k of text in Unicode or even simple ASCII format into a column of type 'text' by using a GUI client like SQLyog or MySQLCC and then extract it when needed by my web page without loosing everything I put in after a certain character or character sequence. You say you are using a web application; are you sure that you're sending the page as UTF-8? Which Content-type header are you sending? And if you are receiving form-submitted text(I don't know anything about SQLyog nor MySQLCC) perhaps this page might help: http://ppewww.ph.gla.ac.uk/~flavell/charset/form-i18n.html HTH, brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: how can I do instead of using subselects?
Please help, this must be easy for experinced MySQL users but for me as a newbie isn't it. I have tried to find the answer on the things below in older MySQL manuals but without any success... I have a problem with a few sql-queries because I have written the questions with subselect statements and then will they not work on older MySQL DB:s. Can any one help me to say haw to solve it without subselect! Today looks the questions like this and works fine in MySQL 4.x.x but not in 3.23.55 where I also need to use them: SELECT (SELECT count(SUB.S_ID) FROM SUB LEFT JOIN SUBS ON SUBS.S_ID = SUB.S_ID) /(SELECT COUNT(*) from SUB); SELECT COUNT(SUBS.NEW_ID)+(SELECT COUNT(SUBS_D.NEW_ID) FROM SUBS_D WHERE SUBS.NEW_ID=SUBS_D.NEW_ID) FROM SUBS WHERE SUBS.NEW_ID=1; SELECT (SELECT count(*) from SUBS)-(SELECT count(SUBS.S_ID) FROM SUB LEFT JOIN SUBS ON SUBS.S_ID = SUB.S_ID)+(SELECT count(*) FROM SUBS_D); Another question is how do I do when I want to compare two tables, normally is it no problem to get a list with rows that exist i both tables, but how do I do if I want to to have the diffrence listed instead, the rows that only appears in one of the tables? Thanks in advance! /Joppe -- ___ Sign-up for Ads Free at Mail.com http://promo.mail.com/adsfreejump.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
field varchar and char truncate the ended blank characters
I got this problem: I need to save in a varchar field a string wich can have the ended part filled with blank character, and I need to restore the string in the same way it was saved, ie. with the right number of ended blank characters. Mysql truncate tha last blank characaters of a field for example if I have a table like the one belowe and insert some field with blank characters at the end , like abc or middle , when I read thei field I get abc and middle. create table pippo ( codice int, messaggio varchar(20), a char(20)); insert into pippo values( 1, abc , 123 ); insert into pippo values( 2, middle , MIDDLE ); select codice, messaggio, length(messaggio), a, length(a) from pippo; 1 abc 3 123 3 2middle8 MIDDLE 7 There are some way to avoid the automatic deletion of the last blank characters ? Regards, Enzo Arlati [EMAIL PROTECTED] AESYS Via Artigiani, 41 24060 Brusaporto (BG) - Italy Tel. +39 (0)35.2924.182 Fax +39 (0)35 680030 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld got signal 11
Hi, I have a problem with some of my servers running 4.1.10. It is running on linux 2.6.10, Fedora Core 3 default installation. The mysql I use is from RPM package that mysql.com provides. About once a day, the servers will crash and I find mysqld got signal 11 error on the log file. These machines only run 1 mysql server per machine, and I only have 1 InnoDB table there. Is there anyone with the same experience? --- start: hostname.err --- mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=33554432 read_buffer_size=2093056 max_used_connections=51 max_connections=400 threads_connected=12 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 3209660 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x4da2ab50 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbfe7f5a8, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x808d8d7 0x82e68d8 0x80929a3 0x809af45 0x809b709 0x82e408c 0x830d95a New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at (nil) is invalid pointer thd-thread_id=10940509 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. pure virtual method called Fatal signal 6 while backtracing Number of processes running now: 0 --- end: hostname.err --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql index cardinality
Nobody to explain me that? From: mel list_php [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: mysql index cardinality Date: Fri, 25 Feb 2005 16:47:12 + Hi, A strange thing with index, I thought the cardinality was automatically updated (like for a primary key for exemple). When I use a primary key in a table, insert a row, the cardinality is increased of 1 as well. I just tried to do that with an INDEX, and the cardinality is none unless I update it with analyze table for example.(http://dev.mysql.com/doc/mysql/en/show-index.html) I also tried with KEY (which is supposed to be an alias of index) and after the first insertion it updated the cardinality but not later on. 1/any explanation?is there a kind of random update from time to time? 2/ is that cardinality important to know? I read that big cardinality will ensure that the index is used for joins for example. Does MySQL check the real cardinality before querying?Or do I have to run an analyze table from time to time? 3/ a primary key is just a peculiar index, so why is that value updated? Thanks for any explanation _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem with adding timestamp as a column
Hi, there, I have MySQL 4.1.10. I need to add a timestamp column to an existing table. I am having a problem of setting the newly added column to be the current time. This is what I did: CREATE TABLE try (id INTEGER); INSERT INTO try VALUES(1), (2), (3); ALTER TABLE try ADD timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP; SELECT * FROM try; +--+-+ | id | timestamp | +--+-+ |1 | -00-00 00:00:00 | |2 | -00-00 00:00:00 | |3 | -00-00 00:00:00 | +--+-+ I've read the on-line manual regarding the change in timestamp, still couldn't figure out why 0's got inserted instead of a meaningful current time stamp. I would greatly appreciate if someone can let me know what the correct way is. Unfortunately I cannot recreate the table. Thank you very much! Regards, Zhe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
alter table return error
Hi, I'm trying to add an 'unsigned' attribute to one column : alter table markets modify Id integer unsigned not null auto_increment; ERROR 1025: Error on rename of './eurex_dax/#sql-1d2c_2f' to './eurex_dax/markets' (errno: 150) The query runs without any problems on any others tables (all are innoDB), all permissions seems to be fine. I don't really know where to look for hints, any ideas ? -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: alter table return error
Did you try perror? ~: perror 150 Error code 150: Unknown error: 150 150 = Foreign key constraint is incorrectly formed Michael Philippe Poelvoorde wrote: Hi, I'm trying to add an 'unsigned' attribute to one column : alter table markets modify Id integer unsigned not null auto_increment; ERROR 1025: Error on rename of './eurex_dax/#sql-1d2c_2f' to './eurex_dax/markets' (errno: 150) The query runs without any problems on any others tables (all are innoDB), all permissions seems to be fine. I don't really know where to look for hints, any ideas ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with adding timestamp as a column
I'd suggest not using the keyword timestamp as a column name. I'd suggest using ts or tstamp or something like that. To update the records to the current timestamp: update try set tstamp=null; should do it. I don't know why the default isn't working, though. On Mon, 28 Feb 2005 10:05:32 -0500, Zhe Wang [EMAIL PROTECTED] wrote: Hi, there, I have MySQL 4.1.10. I need to add a timestamp column to an existing table. I am having a problem of setting the newly added column to be the current time. This is what I did: CREATE TABLE try (id INTEGER); INSERT INTO try VALUES(1), (2), (3); ALTER TABLE try ADD timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP; SELECT * FROM try; +--+-+ | id | timestamp | +--+-+ |1 | -00-00 00:00:00 | |2 | -00-00 00:00:00 | |3 | -00-00 00:00:00 | +--+-+ I've read the on-line manual regarding the change in timestamp, still couldn't figure out why 0's got inserted instead of a meaningful current time stamp. I would greatly appreciate if someone can let me know what the correct way is. Unfortunately I cannot recreate the table. Thank you very much! Regards, Zhe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- -Hank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb_buffer_pool_size - max_connections?
Can I assume that that with a innodb_buffer_pool_size of 1G I can accept only 500 max_connections (stack size 2M*500 connections). Also, I noticed that I can't set a innodb_buffer_pool_size 1G. Ideas? Max -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: alter table return error
Michael Stassen wrote: Did you try perror? ~: perror 150 Error code 150: Unknown error: 150 150 = Foreign key constraint is incorrectly formed Right, I forgot to alter one table that had a FK on this table. Thanks ;) -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
two-way replication
Hi, Is two-way replication possible with MySQL 5.x? Any good sites / docs describing this type of setup? -- Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange Issues
Hi Gleb, Ok here is the command line info which also answers the version question. Enter password: ** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.9-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show variables like '%char%'; +--++ | Variable_name| Value | +--++ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | utf8 | | character_set_results| latin1 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | C:\share\charsets/ | +--++ 7 rows in set (0.72 sec) mysql I am downloading v.10 right now. I believed we were using .10 but in verifying I have found not. I will re-test when the upgrade is installed. Many thanks Rob Gleb Paharenko wrote: At first, we should check that there is nothing wrong with the character_set_xxx variables. Please send us the output of the following statement: show variables like '%char%'; Does the problem remain if you are making the query using a mysql command line client? Do you use the latest release (4.1.10 now)? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: two-way replication
Chris Knipe [EMAIL PROTECTED] wrote on 02/28/2005 11:53:14 AM: Hi, Is two-way replication possible with MySQL 5.x? Any good sites / docs describing this type of setup? -- Chris. By two-way replication, do you mean changes to either database are synchronized to the other? This is a dual-master situation and not yet supported by MySQL. The problem is with cross-server locking. Right now (even with v5.0) MySQL does not know to lock a record on your other server to prevent someone over there from changing that record while you are editing it on this server. Same goes for inserts (auto_increment values are not coordinated) and deletes. Now, I have been monitoring the development lists on the MySQL Cluster and MySQL Federated lines and both are working on inter-server coordination but I have no idea if either of those products will fit your need. As of today, MySQL does support 1-way, and chained replication. There is no rule that prevents you from creating circular replication by making each server the child of the other. The danger with circular replication is that you need to ensure (by application-level logic, data partitioning, or any of other techniques) that insert collisions do not occur (no two new records get the same primary key from different servers) and that you can gracefully handle simultaneous updates (as they are not prevented at the server level). Search this list's and the other lists' archives (http://lists.mysql.com/) for some pretty clever workarounds to help stabilize circular replication. During your searches, you will find links to several books that are online, I highly recommend you read ALL of those links. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: problem with adding timestamp as a column
Hi! Am Mo, den 28.02.2005 schrieb Hank um 17:09: [top-posting reordered!] On Mon, 28 Feb 2005 10:05:32 -0500, Zhe Wang [EMAIL PROTECTED] wrote: Hi, there, I have MySQL 4.1.10. I need to add a timestamp column to an existing table. I am having a problem of setting the newly added column to be the current time. This is what I did: CREATE TABLE try (id INTEGER); INSERT INTO try VALUES(1), (2), (3); ALTER TABLE try ADD timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP; SELECT * FROM try; +--+-+ | id | timestamp | +--+-+ |1 | -00-00 00:00:00 | |2 | -00-00 00:00:00 | |3 | -00-00 00:00:00 | +--+-+ I've read the on-line manual regarding the change in timestamp, still couldn't figure out why 0's got inserted instead of a meaningful current They did not get inserted - see below. time stamp. I would greatly appreciate if someone can let me know what the correct way is. Unfortunately I cannot recreate the table. [[...]] I'd suggest not using the keyword timestamp as a column name. I'd suggest using ts or tstamp or something like that. I second that - avoid using keywords as identifiers! To update the records to the current timestamp: update try set tstamp=null; should do it. I don't know why the default isn't working, though. The default is a value that is used when a row is inserted but no value for this columnt is provided. It is applied at insert time, but not at select time - here simply the existing value is returned. ALTER TABLE ADD creates the new column, but it does no inserts. The general procedure for such situations is: 1) Add the new column to the table. 2) Add code handling this column to all your triggers, stored procedures, web interfaces, application programs, ... that enter data into your DB. 3) Use an Update command to set the default value into this column in all rows where it is still empty or NULL. (These are the rows inserted before the respective application provided a value.) 4) Add code handling this column to all data output functions. (Now you are sure all DB contents is valid.) If necessary: Repeat steps 2), 3), and 4) if you missed an application, or if your logic demanded you to have valid contents immediately. HTH, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Automatic server-id generation for slaves?
Right now one of the only reasons we can't put our entire config for our slaves in CVSup is that the config *requires* the ability to set a server-id for each machine. Seems like it would be pretty trivial to support a hostname based policy for this. You could simply look at the IP/hostname and set the value from this (though you might need a tracking table). Policies could include: - IP based server-id (IPs are 32bit) - parse the hostname for an ID (db4.server.com would yield a server-id of 4) - Adler32/SHA1 truncate the hashcode of the hostname The first two seem sufficient. This wouldn't be the default of course and would require an explicit config. Thoughts? Kevin -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: two-way replication
- Original Message - From: [EMAIL PROTECTED] To: Chris Knipe [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Monday, February 28, 2005 7:11 PM Subject: Re: two-way replication Chris Knipe [EMAIL PROTECTED] wrote on 02/28/2005 11:53:14 AM: Hi, Is two-way replication possible with MySQL 5.x? Any good sites / docs describing this type of setup? -- Chris. By two-way replication, do you mean changes to either database are synchronized to the other? This is a dual-master situation and not yet supported by MySQL. The problem is with cross-server locking. Right now (even with v5.0) MySQL does not know to lock a record on your other server to prevent someone over there from changing that record while you are editing it on this server. Same goes for inserts (auto_increment values are not coordinated) and deletes. Now, I have been monitoring the development lists on the MySQL Cluster and MySQL Federated lines and both are working on inter-server coordination but I have no idea if either of those products will fit your need. As of today, MySQL does support 1-way, and chained replication. There is no rule that prevents you from creating circular replication by making each server the child of the other. The danger with circular replication is that you need to ensure (by application-level logic, data partitioning, or any of other techniques) that insert collisions do not occur (no two new records get the same primary key from different servers) and that you can gracefully handle simultaneous updates (as they are not prevented at the server level). Search this list's and the other lists' archives (http://lists.mysql.com/) for some pretty clever workarounds to help stabilize circular replication. During your searches, you will find links to several books that are online, I highly recommend you read ALL of those links. Thanks Shawn, I will look into it as well as clustering. I think clustering may solve my problems, I am not sure myself to be honest. We basically operate a large WAN, and due to time critical queries on the databases (such as Radius AAA), I want to find a way to roll out multiple databases at strategic locations on the network. The problem comes in that all these databases MUST handle INSERT, SELECT, UPDATE, and DELETE propperly, and migrate any changes through all the databases servers on the network (for now, it will just be 2, but I'm sure this will become more soon). I have to admit, I haven't been following the MySQL developments too much since 4.x came out, so yes, I do have some reading to do. Thanks again for your input. -- Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Automatic server-id generation for slaves?
Kevin A. Burton [EMAIL PROTECTED] wrote on 28/02/2005 17:41:07: Right now one of the only reasons we can't put our entire config for our slaves in CVSup is that the config *requires* the ability to set a server-id for each machine. Seems like it would be pretty trivial to support a hostname based policy for this. You could simply look at the IP/hostname and set the value from this (though you might need a tracking table). Policies could include: - IP based server-id (IPs are 32bit) - parse the hostname for an ID (db4.server.com would yield a server-id of 4) - Adler32/SHA1 truncate the hashcode of the hostname The first two seem sufficient. This wouldn't be the default of course and would require an explicit config. Thoughts? Nice. At the moment, because I have a supervisory application, I have a table inside the database with hostname-serverid lookup. The machine starts up with the slave thread disabled, and the supervisory app reads the slave id from the database and sets it before enabling the slave thread. This could be replicated inside MySQL, with a hostname to slave ID table in the mysql database. Obviously, explicitly assigned slave IDs would override this. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with adding timestamp as a column
Hi, Joerg, Thank you very much for your clear reply. Now I completely understand what is going on behind the screen. Also a big thanks to all the people who gave me kind reply. Regards, Zhe Joerg Bruehe wrote: Hi! Am Mo, den 28.02.2005 schrieb Hank um 17:09: [top-posting reordered!] On Mon, 28 Feb 2005 10:05:32 -0500, Zhe Wang [EMAIL PROTECTED] wrote: Hi, there, I have MySQL 4.1.10. I need to add a timestamp column to an existing table. I am having a problem of setting the newly added column to be the current time. This is what I did: CREATE TABLE try (id INTEGER); INSERT INTO try VALUES(1), (2), (3); ALTER TABLE try ADD timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP; SELECT * FROM try; +--+-+ | id | timestamp | +--+-+ |1 | -00-00 00:00:00 | |2 | -00-00 00:00:00 | |3 | -00-00 00:00:00 | +--+-+ I've read the on-line manual regarding the change in timestamp, still couldn't figure out why 0's got inserted instead of a meaningful current They did not get inserted - see below. time stamp. I would greatly appreciate if someone can let me know what the correct way is. Unfortunately I cannot recreate the table. [[...]] I'd suggest not using the keyword timestamp as a column name. I'd suggest using ts or tstamp or something like that. I second that - avoid using keywords as identifiers! To update the records to the current timestamp: update try set tstamp=null; should do it. I don't know why the default isn't working, though. The default is a value that is used when a row is inserted but no value for this columnt is provided. It is applied at insert time, but not at select time - here simply the existing value is returned. ALTER TABLE ADD creates the new column, but it does no inserts. The general procedure for such situations is: 1) Add the new column to the table. 2) Add code handling this column to all your triggers, stored procedures, web interfaces, application programs, ... that enter data into your DB. 3) Use an Update command to set the default value into this column in all rows where it is still empty or NULL. (These are the rows inserted before the respective application provided a value.) 4) Add code handling this column to all data output functions. (Now you are sure all DB contents is valid.) If necessary: Repeat steps 2), 3), and 4) if you missed an application, or if your logic demanded you to have valid contents immediately. HTH, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert data
I sent this earlier, but it doesn't seem to have gone through. Apologies to
anyone who gets it twice.
=
From perldoc DBD::mysql
use DBI;
$dsn = DBI:mysql:database=$database;host=$hostname;port=$port;
$dbh = DBI-connect($dsn, $user, $password);
So it's not a syntax problem. Even if it were, we should detect the error
long before calling prepare or execute.
Perl is quite clearly telling you what is wrong. Originally, you got
Can't call method prepare on an undefined value.
for the line
my $sth = $dbh-prepare( $sql );
which means that $dbh is undefined at the time of the call to prepare.
Now, you are getting
Can't call method execute on an un undefined value
for the line
$sth-execute( $group_name, $me, $daily, $item, $unit, $qty, $amount,
$tax, $total ) or warn Cannot execute FIRST Statement!!\n$DBI::errstr;
which means that $sth is undefined at the time of the call to execute.
Are you showing us select lines of your code, rather than the actual code?
My best guess right now is that you haven't taken into account that my is
a scoping operator in perl, and in the lines you haven't showed us, the
variables in question ($dbh or $sth) go out of scope.
Michael
John Doe wrote:
Am Sonntag, 27. Februar 2005 22.19 schrieb Gerald Preston:
Hi Gerald
The object used:
my $dbh=DBI-connect( 'DBI:mysql:database=club', 'xxx, 'x', {
PrintError = 0} ) or die $DBI::errstr;
I didn't see this part in your first post :-)
Hmm... I've never seen a '=' in the first argument passed to DBI-connect...
Here's an functional example I'm using:
my $db ='database';
my $host ='hostname';
my $port ='1234';
$dbh=DBI-connect(DBI:mysql:$db:$host:$port,
'a_username',
'a_password',
{RaiseError=1,
AutoCommit=1})
or die $0: $DBI::errstr; }
So, try using club instead of database=club, and a hostname too.
greetings joe
[nothing new below]
-Original Message-
From: John Doe [mailto:[EMAIL PROTECTED]
Sent: Sunday, February 27, 2005 6:37 AM
To: mysql@lists.mysql.com
Subject: Re: insert data
Hi Gerald
I am trying to insert data for the first time using MySQL. In Oracle I
used the following:
# my $sql = insert into bar( group_name, me, daily, item, unit, qty,
amount, tax, total )
# values( ?, ?, ?, ?,?,?, ?,
?, ? ) ;
my $sth = $dbh-prepare( $sql );
die $dbh-errstr if $dbh-err;
$sth-execute( $group_name, $me, $daily, $item, $unit, $qty, $amount,
$tax, $total ) || die Cannot execute FIRST Statement!!\n$DBI::errstr;
I keep getting Can't call method prepare on an un undefined value.
All the name listed are correct by looking at MySQLAdmin1.3\4.
Apart from David Logan's answer:
You have to create the $dbh object first (man DBI); the undefined value
in the error message refers to that.
HTH
joe
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Edwin Limachi está ausente de la oficina.
Estaré ausente de la oficina desde el 28/02/2005 y no volveré hasta el 04/03/2005. Podré atender sus mensajes únicamente al final de la tarde. Para emergencias envie un mensaje a mi teléfono celular. Agradezco su comprensión. Exito para el Nuevo Año. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert data
From perldoc DBD::mysql
use DBI;
$dsn = DBI:mysql:database=$database;host=$hostname;port=$port;
$dbh = DBI-connect($dsn, $user, $password);
So it's not a syntax problem. Even if it were, we should detect the error
long before calling prepare or execute.
Perl is quite clearly telling you what is wrong. Originally, you got
Can't call method prepare on an undefined value.
for the line
my $sth = $dbh-prepare( $sql );
which means that $dbh is undefined at the time of the call to prepare.
Now, you are getting
Can't call method execute on an un undefined value
for the line
$sth-execute( $group_name, $me, $daily, $item, $unit, $qty, $amount,
$tax, $total ) or warn Cannot execute FIRST Statement!!\n$DBI::errstr;
which means that $sth is undefined at the time of the call to execute.
Are you showing us select lines of your code, rather than the actual code?
My best guess right now is that you haven't taken into account that my is
a scoping operator in perl, and in the lines you haven't showed us, the
variables in question ($dbh or $sth) go out of scope.
Michael
John Doe wrote:
Am Sonntag, 27. Februar 2005 22.19 schrieb Gerald Preston:
Hi Gerald
The object used:
my $dbh=DBI-connect( 'DBI:mysql:database=club', 'xxx, 'x', {
PrintError = 0} ) or die $DBI::errstr;
I didn't see this part in your first post :-)
Hmm... I've never seen a '=' in the first argument passed to DBI-connect...
Here's an functional example I'm using:
my $db ='database';
my $host ='hostname';
my $port ='1234';
$dbh=DBI-connect(DBI:mysql:$db:$host:$port,
'a_username',
'a_password',
{RaiseError=1,
AutoCommit=1})
or die $0: $DBI::errstr; }
So, try using club instead of database=club, and a hostname too.
greetings joe
[nothing new below]
-Original Message-
From: John Doe [mailto:[EMAIL PROTECTED]
Sent: Sunday, February 27, 2005 6:37 AM
To: mysql@lists.mysql.com
Subject: Re: insert data
Hi Gerald
I am trying to insert data for the first time using MySQL. In Oracle I
used the following:
# my $sql = insert into bar( group_name, me, daily, item, unit, qty,
amount, tax, total )
# values( ?, ?, ?, ?,?,?, ?,
?, ? ) ;
my $sth = $dbh-prepare( $sql );
die $dbh-errstr if $dbh-err;
$sth-execute( $group_name, $me, $daily, $item, $unit, $qty, $amount,
$tax, $total ) || die Cannot execute FIRST Statement!!\n$DBI::errstr;
I keep getting Can't call method prepare on an un undefined value.
All the name listed are correct by looking at MySQLAdmin1.3\4.
Apart from David Logan's answer:
You have to create the $dbh object first (man DBI); the undefined value
in
the error message refers to that.
HTH
joe
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how can I do instead of using subselects?
Joppe, I have a problem with a few sql-queries because I have written the questions with subselect statements and then will they not work on older MySQL DB:s. Can any one help me to say haw to solve it without subselect! Today looks the questions like this: SELECT (SELECT count(SUB.S_ID) FROM SUB LEFT JOIN SUBS ON SUBS.S_ID = SUB.S_ID) /(SELECT COUNT(*) FROM SUB); Two problems, the query doesn't parse and its intention isn't entirely clear. If it is meant to return the ratio... (no. of sub rows with non-null s_id values) / (total no. of sub rows) then this would work... SELECT COUNT(s_id) / COUNT(*) FROM sub; SELECT COUNT(SUBS.NEW_ID)+(SELECT COUNT(SUBS_D.NEW_ID) FROM SUBS_D WHERE SUBS.NEW_ID=SUBS_D.NEW_ID) FROM SUBS WHERE SUBS.NEW_ID=1; Same two problems, but if the intent is, as it appears, to sum all non-null subs rows where subs.new_id is not null plus all subs_d rows where new_id matches a row in subs and subs.new_id=1 then perhaps this is what you are looking for... SELECT COUNT(subs.new_id) + COUNT(subs_d.new_id) FROM subs LEFT JOIN subs_d USING(new_id) WHERE subs.new_id=1; SELECT (SELECT count(*) FROM SUBS) - (SELECT count(SUBS.S_ID) FROM SUB LEFT JOIN SUBS ON SUBS.S_ID = SUB.S_ID) + (SELECT count(*) FROM SUBS_D); I do not think you will be able to combine two COUNT(*) calls for different tables in one query. Try multiple queries. Peter Brawley http://www.artfulsoftware.com -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 266.5.2 - Release Date: 2/28/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fwd: how can I do instead of using subselects?
Joppe, Another question is how do I do when I want to compare two tables, normally is it no problem to get a list with rows that exist i both tables, but how do I do if I want to to have the diffrence listed instead, the rows that only appears in one of the tables? SELECT * FROM tblA LEFT JOIN tblB ON tblA.key=tblB.key WHERE tblB.key IS NULL; returns the rows of tblA for which there is no tblB row with a matching key value. Peter Brawley http://www.artfulsoftware.com -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 266.5.2 - Release Date: 2/28/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Fwd: Re: Strange Issues]
Original Message Subject: Re: Strange Issues Date: Mon, 28 Feb 2005 20:47:37 +0200 From: Rob Cochrane [EMAIL PROTECTED] Organization: By Rob.com To: Rob Cochrane [EMAIL PROTECTED] References: [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] Hi, I have now upgraded to .10 and nothing has changed. From the Command Line Client the character_set_xxx report exactly the same as before. However from SQLyog all report utf8 I am not at all au fait with the command line client or I would do more tests. thanks again Rob Rob Cochrane wrote: mysql show variables like '%char%'; +--++ | Variable_name| Value | +--++ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | utf8 | | character_set_results| latin1 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | C:\share\charsets/ | +--++ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [GENERAL] Reading from Mysql writting in PGsql
On Sun, Feb 27, 2005 at 03:27:48PM -0800, Mohsen Pahlevanzadeh wrote: Dears,I need to read 1 field with select command from mysql. Then Write it to pgsql. Please guide me. If you need to do this directly, look into dbi-link. http://pgfoundry.org/projects/dbi-link/ There are conversion tools in contrib: my2pg.pl and mysql2pgsql, and of course you can use the database-independent access in your favorite scripting language (DBI.pm in perl, for example) to attach to both databases, then stream from one to the other. HTH :) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Geologic Time
I'm working on a geologic time database and want to ask a question about geologic time. Can/should you apply MySQL's date function to geologic time? In other words, if I create a field for the number of years ago a certain geologic period began or ended - say 260 million years ago - could I designate that field Date? I'll probably just designate a varchar field and enter data like this: 345-250 Then I can add million years ago or mya in my PHP script. Some scientists also use the term BP (Before Present, I think). Another thing I have to deal with is units of thousands. For example, the Pleistocene Epoch (Ice Age) ended about 10,000 years ago. I just wondered if anyone had any suggestions for dealing with geologic time. Thanks. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql open connections question
Hello, I am writing a web-based application and incorporated a roll-your-own database pool into it. So far I am running well, but I have seen a couple of issues I would like to present. After being up for a couple of days, I noticed a Error cannot connect, too many connections error coming from Tomcat. So I would like to understand how to find out the following: How many connections are being used now? How much memory is mysql consuming? Do I have everything configured properly. I am concerned that maybe I have problem with my connection class, so it would be nice to create a connection, (check out how many connections are used) return it, and check again, to see if I screwed something up. Any help in this regards would be appreciated. Sincerely Scott
Re: ODD COUNT(*) Results on Self-Join (Bug?)
Tom:
I see your point, but the group by is necessary so I can walk through
all Song Title groups and get the total number of unique versions of
that song. If I do this:
SELECT DISTINCT file_details.Title,
file_details_1.CD,
file_details_1.mp3Name,
COUNT(*) AS cnt
FROM file_details LEFT JOIN file_details AS file_details_1
ON file_details.Title = file_details_1.Title
WHERE (((file_details.Type) Like 'Song%')
AND file_details.CD = 'Wasted Tears'
AND file_details_1.Type LIKE 'Song%'
AND file_details_1.FileName NOT LIKE '%_ds.php3'
AND file_details.Title = 'Seems I\'ll')
GROUP BY file_details.Title
ORDER BY file_details_1.Title;
I get this:
++--+---+-+
| Title | CD | mp3Name | cnt |
++--+---+-+
| Seems I'll | Wasted Tears | mp3/mp3s/seemsill.mp3 | 6 |
++--+---+-+
COUNT = 6; And, it's distinct. Watch what happens when I do a row
listing without the grouping;
SELECT DISTINCT file_details.Title,
file_details_1.CD,
file_details_1.mp3Name
FROM file_details LEFT JOIN file_details AS file_details_1
ON file_details.Title = file_details_1.Title
WHERE (((file_details.Type) Like 'Song%')
AND file_details.CD = 'Wasted Tears'
AND file_details_1.Type LIKE 'Song%'
AND file_details_1.FileName NOT LIKE '%_ds.php3'
AND file_details.Title = 'Seems I\'ll')
ORDER BY file_details_1.Title;
++-+-+
| Title | CD | mp3Name |
++-+-+
| Seems I'll | Fear of Success | mp3/mp3s/SeemsIll20031029.mp3 |
| Seems I'll | n/a | mp3/mp3s/SeemsIllUnpluggedMixed.mp3 |
| Seems I'll | Wasted Tears| mp3/mp3s/seemsill.mp3 |
++-+-+
So, here the listing is the group of records I want and there are 3, and
it's distinct.
Why when I put the group on this query (which is what I need) does it
double the count?
I think it's a bug.
Best Regards,
Van
=
http://www.dedserius.com/-Linux rocks!!!
=
Tom Crimmins wrote:
On Sunday, February 27, 2005 19:20, Van wrote:
Hi Van,
Greetings:
I've got a table that has the following fields that are relevant to my
self-join:
FileName | varchar(100) | | MUL |
|
Title | varchar(45) | | MUL |
|
Type | varchar(20) | | | HTML
|
mp3Name | varchar(100) | | |
|
CD| varchar(25) | | |
|
Here are the relevant values for the fields for the song Seems I'll:
++++--
FileName | Title | Type |
mp3Name | CD |
++++--
mp3/php3/seemsill.php3 | Seems I'll | Song |
mp3/mp3s/seemsill.mp3 | Wasted Tears|
mp3/php3/SeemsIllUnplugged.php3| Seems I'll | Song |
mp3/mp3s/SeemsIllUnpluggedMixed.mp3 | n/a |
mp3/php3/SeemsIll20031029.php3 | Seems I'll | Song |
mp3/mp3s/SeemsIll20031029.mp3 | Fear of Success |
lyrics/seemsill.php3 | Seems I'll | Lyrics |
mp3/php3/SeemsIll20031029_ds.php3 | Wasted Tears|
mp3/php3/seemsill_ds.php3 | Seems I'll | Song |
mp3/mp3s/seemsill.mp3 | Wasted Tears|
mp3/php3/SeemsIll20031029_ds.php3 | Seems I'll | Song |
mp3/mp3s/SeemsIll20031029.mp3 | Fear of Success |
mp3/php3/SeemsIllUnplugged_ds.php3 | Seems I'll | Song |
mp3/mp3s/SeemsIllUnpluggedMixed.mp3 | n/a |
++++--
Here is the query in question (I'm trying to get the count of all
versions of Seems I'll songs, which was originally on the CD Wasted
Tears, so I can display the other versions, including the one on
Wasted Tears {mp3/mp3s/seemsill.mp}):
SELECT file_details.Title,
file_details.Type,
file_details_1.CD,
file_details_1.mp3Name,
COUNT(*) AS cnt
FROM file_details LEFT JOIN file_details AS file_details_1
ON file_details.Title = file_details_1.Title
WHERE (((file_details.Type) Like 'Song%')
AND file_details.CD = 'Wasted Tears'
AND file_details_1.Type LIKE 'Song%'
AND file_details_1.FileName NOT LIKE '%_ds.php3'
AND file_details.Title = 'Seems I\'ll')
GROUP BY file_details.Title, file_details.Type, file_details_1.CD,
file_details_1.mp3Name
ORDER BY file_details_1.Title;
SQL help
Can someone help me with this? this statement: select Items.Name, Items.Detail, Items.ID, items_online.ID from Items left join items_online on items_online.ItemKey = Items.ID where (Name regexp 'ad') and AccountKey = 108 and Items.Active = 1; gives this: +---+--- -+--+--+ | Name | Detail | ID | ID | +---+--- -+--+--+ | Jade Arch Series | 3/4 thick beveled jade acrylic arch on a beveled jade acrylic base | 7015 | 437 | | Jade Arrow Series | 3/4 thick beveled jade acrylic arrow on beveled jade acrylic base. | 7016 | 438 | | Queen Jade| Unique and elegant shape makes a beautiful free standing award. Glass is jade color. | 7041 | 463 | | Octavia Jade | Beautiful jade glass octagon shaped award. | 7043 | 465 | | Jade Arresting Obelisk| Pristine, monumental style jade glass award. | 7045 | 467 | | Jade Autumn Leaf | Contemporary design derived from natural shape. Cut from jade glass. | 7047 | 469 | | Triad | Free standing clear acrylic with a thick triangular shape. | 7069 | NULL | | Long Format Shadowbox Style Clock | Walnut frame with red, maroon, blue, black, or light velour backgrounds. Retro style clock with new style design that allows it to hang in vertical or horizontal position. Large engravable black brass plate. Lifetime Guaranteed Quartz Movement | 7073 | 494 | | Traditional Wood and Glass Clock | Traditional clock with polished brass and cherry wood finished accents. Polished glass upright holds the clock. Black brass engravable plate. Lifetime Guaranteed Quartz Movement | 7087 | 508 | ... truncated for brevity +---+--- -+--+--+ 14 rows in set (0.06 sec) but this statement: select Items.Name, Items.Detail, Items.ID, items_online.ID from Items left join items_online on items_online.ItemKey = Items.ID where items_online.ID = NULL and (Name regexp 'ad') and AccountKey = 108 and Items.Active = 1; gives this: Empty set (0.00 sec) The only difference in the 2 statements is the 'where items_online.ID = NULL' part. Clearly in the first set, items_online.ID = NULL in record 7047 but when I look for it specifically, it is not found?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL help
correction in question below, the problem is not in record '7047' but in the record which starts with the name 'Triad' also ... I'm using 4.0.20-standard-log -Original Message- From: Rob Brooks [mailto:[EMAIL PROTECTED] Sent: Monday, February 28, 2005 2:56 PM To: mysql@lists.mysql.com Subject: SQL help Can someone help me with this? this statement: select Items.Name, Items.Detail, Items.ID, items_online.ID from Items left join items_online on items_online.ItemKey = Items.ID where (Name regexp 'ad') and AccountKey = 108 and Items.Active = 1; gives this: +---+--- -+--+--+ | Name | Detail | ID | ID | +---+--- -+--+--+ | Jade Arch Series | 3/4 thick beveled jade acrylic arch on a beveled jade acrylic base | 7015 | 437 | | Jade Arrow Series | 3/4 thick beveled jade acrylic arrow on beveled jade acrylic base. | 7016 | 438 | | Queen Jade| Unique and elegant shape makes a beautiful free standing award. Glass is jade color. | 7041 | 463 | | Octavia Jade | Beautiful jade glass octagon shaped award. | 7043 | 465 | | Jade Arresting Obelisk| Pristine, monumental style jade glass award. | 7045 | 467 | | Jade Autumn Leaf | Contemporary design derived from natural shape. Cut from jade glass. | 7047 | 469 | | Triad | Free standing clear acrylic with a thick triangular shape. | 7069 | NULL | | Long Format Shadowbox Style Clock | Walnut frame with red, maroon, blue, black, or light velour backgrounds. Retro style clock with new style design that allows it to hang in vertical or horizontal position. Large engravable black brass plate. Lifetime Guaranteed Quartz Movement | 7073 | 494 | | Traditional Wood and Glass Clock | Traditional clock with polished brass and cherry wood finished accents. Polished glass upright holds the clock. Black brass engravable plate. Lifetime Guaranteed Quartz Movement | 7087 | 508 | ... truncated for brevity +---+--- -+--+--+ 14 rows in set (0.06 sec) but this statement: select Items.Name, Items.Detail, Items.ID, items_online.ID from Items left join items_online on items_online.ItemKey = Items.ID where items_online.ID = NULL and (Name regexp 'ad') and AccountKey = 108 and Items.Active = 1; gives this: Empty set (0.00 sec) The only difference in the 2 statements is the 'where items_online.ID = NULL' part. Clearly in the first set, items_online.ID = NULL in record 7047 but when I look for it specifically, it is not found?? -- 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: insert data
Michael,
This is the actual code except for the :
my $dbh = DBI-connect( 'DBI:mysql:database=club', '', '', {
PrintError = 0 } ) or die $DBI::errstr;
my $sql = insert into wolfies( group_name, me, daily, item, unit, qty,
amount, tax, total )
values( ?, ?, ?, ?,?,?, ?,
?, ? ) ;
my $sth = $dbh-prepare( $sql ) or die $dbh-errstr if $dbh-err;
$sth-execute( $group_name, $me, $daily, $item, $unit, $qty, $amount,
$tax, $total ) or warn Cannot execute FIRST Statement!!\n$DBI::errstr;
Question? When I created the database club, is there anything I needed to
do concerning permissions or anything?
I am lost here. I have been writing code on a SUN Oracle systems for over
five years.
Regards,
Jerry
-Original Message-
From: Michael Stassen [mailto:[EMAIL PROTECTED]
Sent: Monday, February 28, 2005 9:29 AM
To: Gerald Preston
Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: Re: insert data
From perldoc DBD::mysql
use DBI;
$dsn = DBI:mysql:database=$database;host=$hostname;port=$port;
$dbh = DBI-connect($dsn, $user, $password);
So it's not a syntax problem. Even if it were, we should detect the error
long before calling prepare or execute.
Perl is quite clearly telling you what is wrong. Originally, you got
Can't call method prepare on an undefined value.
for the line
my $sth = $dbh-prepare( $sql );
which means that $dbh is undefined at the time of the call to prepare.
Now, you are getting
Can't call method execute on an un undefined value
for the line
$sth-execute( $group_name, $me, $daily, $item, $unit, $qty, $amount,
$tax, $total ) or warn Cannot execute FIRST
Statement!!\n$DBI::errstr;
which means that $sth is undefined at the time of the call to execute.
Are you showing us select lines of your code, rather than the actual code?
My best guess right now is that you haven't taken into account that my is
a scoping operator in perl, and in the lines you haven't showed us, the
variables in question ($dbh or $sth) go out of scope.
Michael
John Doe wrote:
Am Sonntag, 27. Februar 2005 22.19 schrieb Gerald Preston:
Hi Gerald
The object used:
my $dbh=DBI-connect( 'DBI:mysql:database=club', 'xxx, 'x', {
PrintError = 0} ) or die $DBI::errstr;
I didn't see this part in your first post :-)
Hmm... I've never seen a '=' in the first argument passed to
DBI-connect...
Here's an functional example I'm using:
my $db ='database';
my $host ='hostname';
my $port ='1234';
$dbh=DBI-connect(DBI:mysql:$db:$host:$port,
'a_username',
'a_password',
{RaiseError=1,
AutoCommit=1})
or die $0: $DBI::errstr; }
So, try using club instead of database=club, and a hostname too.
greetings joe
[nothing new below]
-Original Message-
From: John Doe [mailto:[EMAIL PROTECTED]
Sent: Sunday, February 27, 2005 6:37 AM
To: mysql@lists.mysql.com
Subject: Re: insert data
Hi Gerald
I am trying to insert data for the first time using MySQL. In Oracle I
used the following:
# my $sql = insert into bar( group_name, me, daily, item, unit, qty,
amount, tax, total )
# values( ?, ?, ?, ?,?,?, ?,
?, ? ) ;
my $sth = $dbh-prepare( $sql );
die $dbh-errstr if $dbh-err;
$sth-execute( $group_name, $me, $daily, $item, $unit, $qty, $amount,
$tax, $total ) || die Cannot execute FIRST Statement!!\n$DBI::errstr;
I keep getting Can't call method prepare on an un undefined value.
All the name listed are correct by looking at MySQLAdmin1.3\4.
Apart from David Logan's answer:
You have to create the $dbh object first (man DBI); the undefined value
in
the error message refers to that.
HTH
joe
--
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: insert data
Gerald Preston said:
Michael,
This is the actual code except for the :
my $dbh = DBI-connect( 'DBI:mysql:database=club', '', '', {
PrintError = 0 } ) or die $DBI::errstr;
my $sql = insert into wolfies( group_name, me, daily, item, unit,
qty,
amount, tax, total )
values( ?, ?, ?, ?,?,?,
?,
?, ? ) ;
my $sth = $dbh-prepare( $sql ) or die $dbh-errstr if $dbh-err;
$sth-execute( $group_name, $me, $daily, $item, $unit, $qty, $amount,
$tax, $total ) or warn Cannot execute FIRST Statement!!\n$DBI::errstr;
Question? When I created the database club, is there anything I needed
to do concerning permissions or anything?
I am lost here. I have been writing code on a SUN Oracle systems for
over five years.
Regards,
Jerry
Did you 'grant' user access to all the tables in database club?
-Original Message-
From: Michael Stassen [mailto:[EMAIL PROTECTED]
Sent: Monday, February 28, 2005 9:29 AM
To: Gerald Preston
Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: Re: insert data
From perldoc DBD::mysql
use DBI;
$dsn = DBI:mysql:database=$database;host=$hostname;port=$port;
$dbh = DBI-connect($dsn, $user, $password);
So it's not a syntax problem. Even if it were, we should detect the
error long before calling prepare or execute.
Perl is quite clearly telling you what is wrong. Originally, you got
Can't call method prepare on an undefined value.
for the line
my $sth = $dbh-prepare( $sql );
which means that $dbh is undefined at the time of the call to prepare.
Now, you are getting
Can't call method execute on an un undefined value
for the line
$sth-execute( $group_name, $me, $daily, $item, $unit, $qty, $amount,
$tax, $total ) or warn Cannot execute FIRST
Statement!!\n$DBI::errstr;
which means that $sth is undefined at the time of the call to execute.
Are you showing us select lines of your code, rather than the actual
code? My best guess right now is that you haven't taken into account
that my is a scoping operator in perl, and in the lines you haven't
showed us, the variables in question ($dbh or $sth) go out of scope.
Michael
John Doe wrote:
Am Sonntag, 27. Februar 2005 22.19 schrieb Gerald Preston:
Hi Gerald
The object used:
my $dbh=DBI-connect( 'DBI:mysql:database=club', 'xxx, 'x', {
PrintError = 0} ) or die $DBI::errstr;
I didn't see this part in your first post :-)
Hmm... I've never seen a '=' in the first argument passed to
DBI-connect...
Here's an functional example I'm using:
my $db ='database';
my $host ='hostname';
my $port ='1234';
$dbh=DBI-connect(DBI:mysql:$db:$host:$port,
'a_username',
'a_password',
{RaiseError=1,
AutoCommit=1})
or die $0: $DBI::errstr; }
So, try using club instead of database=club, and a hostname too.
greetings joe
[nothing new below]
-Original Message-
From: John Doe [mailto:[EMAIL PROTECTED]
Sent: Sunday, February 27, 2005 6:37 AM
To: mysql@lists.mysql.com
Subject: Re: insert data
Hi Gerald
I am trying to insert data for the first time using MySQL. In Oracle
I used the following:
# my $sql = insert into bar( group_name, me, daily, item, unit,
qty, amount, tax, total )
# values( ?, ?, ?, ?,?,?,
?, ?, ? ) ;
my $sth = $dbh-prepare( $sql );
die $dbh-errstr if $dbh-err;
$sth-execute( $group_name, $me, $daily, $item, $unit, $qty,
$amount,
$tax, $total ) || die Cannot execute FIRST
Statement!!\n$DBI::errstr;
I keep getting Can't call method prepare on an un undefined value.
All the name listed are correct by looking at MySQLAdmin1.3\4.
Apart from David Logan's answer:
You have to create the $dbh object first (man DBI); the undefined
value in
the error message refers to that.
HTH
joe
--
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL help
Properly, NULL values should be matched with 'foo IS NULL', as opposed to 'foo = NULL' which, by standard definition, always returns false regardless of the value of foo - michael dykman On Mon, 2005-02-28 at 16:02, Rob Brooks wrote: correction in question below, the problem is not in record '7047' but in the record which starts with the name 'Triad' also ... I'm using 4.0.20-standard-log -Original Message- From: Rob Brooks [mailto:[EMAIL PROTECTED] Sent: Monday, February 28, 2005 2:56 PM To: mysql@lists.mysql.com Subject: SQL help Can someone help me with this? this statement: select Items.Name, Items.Detail, Items.ID, items_online.ID from Items left join items_online on items_online.ItemKey = Items.ID where (Name regexp 'ad') and AccountKey = 108 and Items.Active = 1; gives this: +---+--- -+--+--+ | Name | Detail | ID | ID | +---+--- -+--+--+ | Jade Arch Series | 3/4 thick beveled jade acrylic arch on a beveled jade acrylic base | 7015 | 437 | | Jade Arrow Series | 3/4 thick beveled jade acrylic arrow on beveled jade acrylic base. | 7016 | 438 | | Queen Jade| Unique and elegant shape makes a beautiful free standing award. Glass is jade color. | 7041 | 463 | | Octavia Jade | Beautiful jade glass octagon shaped award. | 7043 | 465 | | Jade Arresting Obelisk| Pristine, monumental style jade glass award. | 7045 | 467 | | Jade Autumn Leaf | Contemporary design derived from natural shape. Cut from jade glass. | 7047 | 469 | | Triad | Free standing clear acrylic with a thick triangular shape. | 7069 | NULL | | Long Format Shadowbox Style Clock | Walnut frame with red, maroon, blue, black, or light velour backgrounds. Retro style clock with new style design that allows it to hang in vertical or horizontal position. Large engravable black brass plate. Lifetime Guaranteed Quartz Movement | 7073 | 494 | | Traditional Wood and Glass Clock | Traditional clock with polished brass and cherry wood finished accents. Polished glass upright holds the clock. Black brass engravable plate. Lifetime Guaranteed Quartz Movement | 7087 | 508 | ... truncated for brevity +---+--- -+--+--+ 14 rows in set (0.06 sec) but this statement: select Items.Name, Items.Detail, Items.ID, items_online.ID from Items left join items_online on items_online.ItemKey = Items.ID where items_online.ID = NULL and (Name regexp 'ad') and AccountKey = 108 and Items.Active = 1; gives this: Empty set (0.00 sec) The only difference in the 2 statements is the 'where items_online.ID = NULL' part. Clearly in the first set, items_online.ID = NULL in record 7047 but when I look for it specifically, it is not found?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql open connections question
On Mon, 2005-02-28 at 14:42, Scott Purcell wrote: Hi! you haven't mentioned your OS so some of these items will be a guess at how _you_ would actually do it.. I'm basing this on Linux or any modern *nix OS. Hello, I am writing a web-based application and incorporated a roll-your-own database pool into it. So far I am running well, but I have seen a couple of issues I would like to present. After being up for a couple of days, I noticed a Error cannot connect, too many connections error coming from Tomcat. So I would like to understand how to find out the following: How many connections are being used now? at the mysql prompt (from the console or any admin application) mysql show processlist; How much memory is mysql consuming? assuming your mysqld runs as user root. from the shell, try $ top -u mysql Do I have everything configured properly. I am concerned that maybe I have problem with my connection class, so it would be nice to create a connection, (check out how many connections are used) return it, and check again, to see if I screwed something up. It suggests your connection class is not letting go of open connections. May out some instrumentation into it so you can keep track. Your pool might also want some check for stale connections or, more wisely I think, the ability to close connections which have not been used for a certain period of time (5-10 minutes?) You can configure the number of connections which MySQL will accept in your my.cnf file with max_connections = 500 # or whatever value you like.. each connection does cost resources though (RAM, file handles) so don't treat this like it's unlimited. The default is 100; it's maximum is 4000 with static binaries provided by MySQL AB. -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL help
NULL is an unknown value. Consequently, you cannot compare NULLs the way you expect. Effectively, = NULL is always false. Instead of items_online.ID = NULL you have to use items_online.ID IS NULL Michael Rob Brooks wrote: correction in question below, the problem is not in record '7047' but in the record which starts with the name 'Triad' also ... I'm using 4.0.20-standard-log -Original Message- From: Rob Brooks [mailto:[EMAIL PROTECTED] Sent: Monday, February 28, 2005 2:56 PM To: mysql@lists.mysql.com Subject: SQL help Can someone help me with this? this statement: select Items.Name, Items.Detail, Items.ID, items_online.ID from Items left join items_online on items_online.ItemKey = Items.ID where (Name regexp 'ad') and AccountKey = 108 and Items.Active = 1; gives this: +---+--- -+--+--+ | Name | Detail | ID | ID | +---+--- -+--+--+ | Jade Arch Series | 3/4 thick beveled jade acrylic arch on a beveled jade acrylic base | 7015 | 437 | | Jade Arrow Series | 3/4 thick beveled jade acrylic arrow on beveled jade acrylic base. | 7016 | 438 | | Queen Jade| Unique and elegant shape makes a beautiful free standing award. Glass is jade color. | 7041 | 463 | | Octavia Jade | Beautiful jade glass octagon shaped award. | 7043 | 465 | | Jade Arresting Obelisk| Pristine, monumental style jade glass award. | 7045 | 467 | | Jade Autumn Leaf | Contemporary design derived from natural shape. Cut from jade glass. | 7047 | 469 | | Triad | Free standing clear acrylic with a thick triangular shape. | 7069 | NULL | | Long Format Shadowbox Style Clock | Walnut frame with red, maroon, blue, black, or light velour backgrounds. Retro style clock with new style design that allows it to hang in vertical or horizontal position. Large engravable black brass plate. Lifetime Guaranteed Quartz Movement | 7073 | 494 | | Traditional Wood and Glass Clock | Traditional clock with polished brass and cherry wood finished accents. Polished glass upright holds the clock. Black brass engravable plate. Lifetime Guaranteed Quartz Movement | 7087 | 508 | ... truncated for brevity +---+--- -+--+--+ 14 rows in set (0.06 sec) but this statement: select Items.Name, Items.Detail, Items.ID, items_online.ID from Items left join items_online on items_online.ItemKey = Items.ID where items_online.ID = NULL and (Name regexp 'ad') and AccountKey = 108 and Items.Active = 1; gives this: Empty set (0.00 sec) The only difference in the 2 statements is the 'where items_online.ID = NULL' part. Clearly in the first set, items_online.ID = NULL in record 7047 but when I look for it specifically, it is not found?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert data
Are you sure that the parameters in the execute are all
properly defined? Try this:
my $dbh = DBI-connect( 'DBI:mysql:database=club', '', '', {
PrintError = 0 } ) or die $DBI::errstr;
my $sql = insert into bar( group_name, me, daily, item, unit, qty, amount,
tax, total )
values( ?, ?, ?, ?,?,?, ?, ?, ? )
;
my $sth = $dbh-prepare($sql) or die $dbh-errstr;
# If not defined, set to '' to avoid warnings.
for ($group_name, $me, $daily, $item, $unit, $qty, $amount, $tax, $total) {
$_ ||= '' if ! defined $_;
}
$sth-execute($group_name, $me, $daily, $item, $unit, $qty, $amount, $tax,
$total) or warn Cannot execute FIRST Statement!!\n$DBI::errstr;
If this succeeds, review your code and make sure that each
of your execute parameters have some value prior to the
execute.
Eamon Daly
- Original Message -
From: Gerald Preston [EMAIL PROTECTED]
To: 'Michael Stassen' [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Monday, February 28, 2005 3:19 PM
Subject: RE: insert data
Michael,
This is the actual code except for the :
my $dbh = DBI-connect( 'DBI:mysql:database=club', '', '', {
PrintError = 0 } ) or die $DBI::errstr;
my $sql = insert into wolfies( group_name, me, daily, item, unit, qty,
amount, tax, total )
values( ?, ?, ?, ?,?,?,
?,
?, ? ) ;
my $sth = $dbh-prepare( $sql ) or die $dbh-errstr if $dbh-err;
$sth-execute( $group_name, $me, $daily, $item, $unit, $qty, $amount,
$tax, $total ) or warn Cannot execute FIRST Statement!!\n$DBI::errstr;
Question? When I created the database club, is there anything I needed to
do concerning permissions or anything?
I am lost here. I have been writing code on a SUN Oracle systems for over
five years.
Regards,
Jerry
-Original Message-
From: Michael Stassen [mailto:[EMAIL PROTECTED]
Sent: Monday, February 28, 2005 9:29 AM
To: Gerald Preston
Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: Re: insert data
From perldoc DBD::mysql
use DBI;
$dsn = DBI:mysql:database=$database;host=$hostname;port=$port;
$dbh = DBI-connect($dsn, $user, $password);
So it's not a syntax problem. Even if it were, we should detect the error
long before calling prepare or execute.
Perl is quite clearly telling you what is wrong. Originally, you got
Can't call method prepare on an undefined value.
for the line
my $sth = $dbh-prepare( $sql );
which means that $dbh is undefined at the time of the call to prepare.
Now, you are getting
Can't call method execute on an un undefined value
for the line
$sth-execute( $group_name, $me, $daily, $item, $unit, $qty, $amount,
$tax, $total ) or warn Cannot execute FIRST
Statement!!\n$DBI::errstr;
which means that $sth is undefined at the time of the call to execute.
Are you showing us select lines of your code, rather than the actual code?
My best guess right now is that you haven't taken into account that my
is
a scoping operator in perl, and in the lines you haven't showed us, the
variables in question ($dbh or $sth) go out of scope.
Michael
John Doe wrote:
Am Sonntag, 27. Februar 2005 22.19 schrieb Gerald Preston:
Hi Gerald
The object used:
my $dbh=DBI-connect( 'DBI:mysql:database=club', 'xxx, 'x', {
PrintError = 0} ) or die $DBI::errstr;
I didn't see this part in your first post :-)
Hmm... I've never seen a '=' in the first argument passed to
DBI-connect...
Here's an functional example I'm using:
my $db ='database';
my $host ='hostname';
my $port ='1234';
$dbh=DBI-connect(DBI:mysql:$db:$host:$port,
'a_username',
'a_password',
{RaiseError=1,
AutoCommit=1})
or die $0: $DBI::errstr; }
So, try using club instead of database=club, and a hostname too.
greetings joe
[nothing new below]
-Original Message-
From: John Doe [mailto:[EMAIL PROTECTED]
Sent: Sunday, February 27, 2005 6:37 AM
To: mysql@lists.mysql.com
Subject: Re: insert data
Hi Gerald
I am trying to insert data for the first time using MySQL. In Oracle I
used the following:
# my $sql = insert into bar( group_name, me, daily, item, unit, qty,
amount, tax, total )
# values( ?, ?, ?, ?,?,?,
?,
?, ? ) ;
my $sth = $dbh-prepare( $sql );
die $dbh-errstr if $dbh-err;
$sth-execute( $group_name, $me, $daily, $item, $unit, $qty, $amount,
$tax, $total ) || die Cannot execute FIRST Statement!!\n$DBI::errstr;
I keep getting Can't call method prepare on an un undefined value.
All the name listed are correct by looking at MySQLAdmin1.3\4.
Apart from David Logan's answer:
You have to create the $dbh object first (man DBI); the undefined value
in
the error message refers to that.
HTH
joe
--
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:
Innodb and Linux 2.6 Async I/O ??
Just found and read this study: http://www.distlab.dk/badger/Publications/report0403.ps and was curious to see if anyone has any additional thoughts as to the contents? Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL help
In article [EMAIL PROTECTED], Rob Brooks [EMAIL PROTECTED] writes: The only difference in the 2 statements is the 'where items_online.ID = NULL' part. Clearly in the first set, items_online.ID = NULL in record 7047 ... Nope. items_online.ID IS NULL for that record, but comparing anything = NULL ain't true, even for NULL. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Geologic Time
If you want to represent 290 million years as an integer (290,000,000): - An UNSIGNED INT can store 4,294,967,295 - A UNSIGNED BIGINT can store 18,446,744,073,709,551,615 In your schema, I'd use a start_period and end_period instead of a varchar. It's easier to sort and do math on. You could factor out 1,000,000 from your dates and use a float to represent the numbers. 290.00 could represent 290 mya, while 0.01 represents 10,000 years ago. Just make sure there is enough precision on your float. On Mon, 28 Feb 2005 11:16:55 -0800 (PST), David Blomstrom [EMAIL PROTECTED] wrote: I'm working on a geologic time database and want to ask a question about geologic time. Can/should you apply MySQL's date function to geologic time? In other words, if I create a field for the number of years ago a certain geologic period began or ended - say 260 million years ago - could I designate that field Date? I'll probably just designate a varchar field and enter data like this: 345-250 Then I can add million years ago or mya in my PHP script. Some scientists also use the term BP (Before Present, I think). Another thing I have to deal with is units of thousands. For example, the Pleistocene Epoch (Ice Age) ended about 10,000 years ago. I just wondered if anyone had any suggestions for dealing with geologic time. Thanks. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.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]
mysqldump specific tables from multiple databases?
all, is it possible to mysqldump specific tables from multiple databases in a single run? what I am trying to do is get replication slaves to a starting point but am somewhat challenged by the nature of our architecture. specifically, we have a large number of relatively-static (updated only a few times/yr w/plenty of advance notice) tables which are relatively big (~50GB among them) and a small number of tables that are relatively small (~1GB among them) but are updated continuously (several million DMLs/day). what I had done w/a single DB was dump/load the static tables to a new slave then dump/load the dynamic ones w/a --master-data which took ~1 min (acceptable off hours). that got me to a point I could do a change master... then slave start and be in business. I am now trying to merge in a small, secondary application (only ~50K DMLs/day) which is currently in a separate database. I can not (even off hours) lock the entire database (main one) long enough to do a full dump with locks so I see my options as: 1. hope someone here knows how to do my original question 2. get architecture to sign off on consolidating tables into a single database (in progress). 3. hope someone here know an approach I hadn't even thought of (paradigm shifting w/o a clutch). thoughts? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ODD COUNT(*) Results on Self-Join (Bug?)
On Monday, February 28, 2005 14:54, Van wrote: Tom: I see your point, but the group by is necessary so I can walk through all Song Title groups and get the total number of unique versions of that song. If I do this: SELECT DISTINCT file_details.Title, file_details_1.CD, file_details_1.mp3Name, COUNT(*) AS cnt FROM file_details LEFT JOIN file_details AS file_details_1 ON file_details.Title = file_details_1.Title WHERE (((file_details.Type) Like 'Song%') AND file_details.CD = 'Wasted Tears' AND file_details_1.Type LIKE 'Song%' AND file_details_1.FileName NOT LIKE '%_ds.php3' AND file_details.Title = 'Seems I\'ll') GROUP BY file_details.Title ORDER BY file_details_1.Title; I get this: ++--+---+-+ Title | CD | mp3Name | cnt | ++--+---+-+ Seems I'll | Wasted Tears | mp3/mp3s/seemsill.mp3 | 6 | ++--+---+-+ COUNT = 6; And, it's distinct. Watch what happens when I do a row listing without the grouping; SELECT DISTINCT file_details.Title, file_details_1.CD, file_details_1.mp3Name FROM file_details LEFT JOIN file_details AS file_details_1 ON file_details.Title = file_details_1.Title WHERE (((file_details.Type) Like 'Song%') AND file_details.CD = 'Wasted Tears' AND file_details_1.Type LIKE 'Song%' AND file_details_1.FileName NOT LIKE '%_ds.php3' AND file_details.Title = 'Seems I\'ll') ORDER BY file_details_1.Title; ++-+-+ Title | CD | mp3Name | ++-+-+ Seems I'll | Fear of Success | mp3/mp3s/SeemsIll20031029.mp3 | Seems I'll | n/a | mp3/mp3s/SeemsIllUnpluggedMixed.mp3 | Seems I'll | Wasted Tears| mp3/mp3s/seemsill.mp3 | ++-+-+ So, here the listing is the group of records I want and there are 3, and it's distinct. Why when I put the group on this query (which is what I need) does it double the count? I think it's a bug. If you displayed all the fields from both tables, I think you would understand the join better. This is somewhat difficult to explain, but because you are only saying file_details_1.FileName NOT LIKE '%_ds.php3' and not file_details.FileName NOT LIKE '%_ds.php3' as well, this will in effect double all of your groupings since you are not grouping by filename, and two rows from table 1 will be left after the where clause to be joined to the three rows left in table 2. This in effect gives you your six rows, or 3 groups of 2 with you group by clause. Like I said above, I would suggest showing all of the fields ie. SELECT file_details.*, file_details_1.* FROM ., so you can get a better idea of what is going on here. Also, there really isn't any reason to do a left join here. An inner join would work just fine since you are joining a table with itself on the same field there will always be a match. By the way this is a horrible explaination, maybe someone else can do a better job of it. Tom Crimmins wrote: On Sunday, February 27, 2005 19:20, Van wrote: Hi Van, Greetings: I've got a table that has the following fields that are relevant to my self-join: FileName | varchar(100) | | MUL | | Title | varchar(45) | | MUL | | Type | varchar(20) | | | HTML | mp3Name | varchar(100) | | | | CD| varchar(25) | | | | Here are the relevant values for the fields for the song Seems I'll: ++++-- FileName | Title | Type | mp3Name | CD | ++++-- mp3/php3/seemsill.php3 | Seems I'll | Song | mp3/mp3s/seemsill.mp3 | Wasted Tears| mp3/php3/SeemsIllUnplugged.php3| Seems I'll | Song | mp3/mp3s/SeemsIllUnpluggedMixed.mp3 | n/a | mp3/php3/SeemsIll20031029.php3 | Seems I'll | Song | mp3/mp3s/SeemsIll20031029.mp3 | Fear of Success | lyrics/seemsill.php3 | Seems I'll | Lyrics | mp3/php3/SeemsIll20031029_ds.php3 | Wasted Tears| mp3/php3/seemsill_ds.php3 | Seems I'll | Song | mp3/mp3s/seemsill.mp3 | Wasted Tears| mp3/php3/SeemsIll20031029_ds.php3 | Seems I'll | Song | mp3/mp3s/SeemsIll20031029.mp3 | Fear of Success | mp3/php3/SeemsIllUnplugged_ds.php3 | Seems I'll | Song |
Re: Geologic Time
David, The earliest possible MySQL date is around 1000CE, so you could not store geologic dates in MySQL date cols. Million years before present is the geologic time unit that would most likely cohere with other geo databases, isn't it? Then the Cambrian would show up around 580 mya, the beginning of recorded human history around .005 mya, c. A float, double or decimal col would handle such values. Peter Brawley http://www.artfulsoftware.com - David Blomstrom wrote: I'm working on a geologic time database and want to ask a question about geologic time. Can/should you apply MySQL's date function to geologic time? In other words, if I create a field for the number of years ago a certain geologic period began or ended - say 260 million years ago - could I designate that field Date? I'll probably just designate a varchar field and enter data like this: 345-250 Then I can add million years ago or mya in my PHP script. Some scientists also use the term BP (Before Present, I think). Another thing I have to deal with is units of thousands. For example, the Pleistocene Epoch (Ice Age) ended about 10,000 years ago. I just wondered if anyone had any suggestions for dealing with geologic time. Thanks. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 266.5.2 - Release Date: 2/28/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ODD COUNT(*) Results on Self-Join (Bug?)
Tom: The second file_details.FileName NOT LIKE '%_ds.php3' Was the culprit. Not a horrible explanation at all. Thanks for the extra pair of eyes! Regards, Van = http://www.dedserius.com/-Linux rocks!!! = Tom Crimmins wrote: On Monday, February 28, 2005 14:54, Van wrote: Tom: I see your point, but the group by is necessary so I can walk through all Song Title groups and get the total number of unique versions of that song. If I do this: SELECT DISTINCT file_details.Title, file_details_1.CD, file_details_1.mp3Name, COUNT(*) AS cnt FROM file_details LEFT JOIN file_details AS file_details_1 ON file_details.Title = file_details_1.Title WHERE (((file_details.Type) Like 'Song%') AND file_details.CD = 'Wasted Tears' AND file_details_1.Type LIKE 'Song%' AND file_details_1.FileName NOT LIKE '%_ds.php3' AND file_details.Title = 'Seems I\'ll') GROUP BY file_details.Title ORDER BY file_details_1.Title; I get this: ++--+---+-+ Title | CD | mp3Name | cnt | ++--+---+-+ Seems I'll | Wasted Tears | mp3/mp3s/seemsill.mp3 | 6 | ++--+---+-+ COUNT = 6; And, it's distinct. Watch what happens when I do a row listing without the grouping; SELECT DISTINCT file_details.Title, file_details_1.CD, file_details_1.mp3Name FROM file_details LEFT JOIN file_details AS file_details_1 ON file_details.Title = file_details_1.Title WHERE (((file_details.Type) Like 'Song%') AND file_details.CD = 'Wasted Tears' AND file_details_1.Type LIKE 'Song%' AND file_details_1.FileName NOT LIKE '%_ds.php3' AND file_details.Title = 'Seems I\'ll') ORDER BY file_details_1.Title; ++-+-+ Title | CD | mp3Name | ++-+-+ Seems I'll | Fear of Success | mp3/mp3s/SeemsIll20031029.mp3 | Seems I'll | n/a | mp3/mp3s/SeemsIllUnpluggedMixed.mp3 | Seems I'll | Wasted Tears| mp3/mp3s/seemsill.mp3 | ++-+-+ So, here the listing is the group of records I want and there are 3, and it's distinct. Why when I put the group on this query (which is what I need) does it double the count? I think it's a bug. If you displayed all the fields from both tables, I think you would understand the join better. This is somewhat difficult to explain, but because you are only saying file_details_1.FileName NOT LIKE '%_ds.php3' and not file_details.FileName NOT LIKE '%_ds.php3' as well, this will in effect double all of your groupings since you are not grouping by filename, and two rows from table 1 will be left after the where clause to be joined to the three rows left in table 2. This in effect gives you your six rows, or 3 groups of 2 with you group by clause. Like I said above, I would suggest showing all of the fields ie. SELECT file_details.*, file_details_1.* FROM ., so you can get a better idea of what is going on here. Also, there really isn't any reason to do a left join here. An inner join would work just fine since you are joining a table with itself on the same field there will always be a match. By the way this is a horrible explaination, maybe someone else can do a better job of it. Regards, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Help
I have a table that tracks events by month and year. The field names are (you guessed it) 'month' and 'year'. I need to do queries over some date range from variables beg_month, beg_year to end_month, end_year. How can I do this? SELECT state, SUM(borks) AS borkcount FROM borkstats WHERE GROUP BY state ORDER BY state In another table like this with month/year fields I once created a dummy date field that I populated with the date of the first of the month (1/month/year), just to facilitate queries like this. Is there another approach? Can I somehow create that dummy field on the fly within the query itself? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
EBCDIC collation sequence
Hi There doesn't appear to be any info. on the archives regarding this, so does anyone know what character set/collation sequence to use for EBCDIC rather than ASCII collation? Cheers Bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: insert data
William,
I tried GRANT ALL ON *.* and got error 1064 4200: You have an error
in your SQL syntax ??
Jerry
-Original Message-
From: William R. Mussatto [mailto:[EMAIL PROTECTED]
Sent: Monday, February 28, 2005 3:25 PM
To: mysql@lists.mysql.com
Subject: RE: insert data
Gerald Preston said:
Michael,
This is the actual code except for the :
my $dbh = DBI-connect( 'DBI:mysql:database=club', '', '', {
PrintError = 0 } ) or die $DBI::errstr;
my $sql = insert into wolfies( group_name, me, daily, item, unit,
qty,
amount, tax, total )
values( ?, ?, ?, ?,?,?,
?,
?, ? ) ;
my $sth = $dbh-prepare( $sql ) or die $dbh-errstr if $dbh-err;
$sth-execute( $group_name, $me, $daily, $item, $unit, $qty, $amount,
$tax, $total ) or warn Cannot execute FIRST Statement!!\n$DBI::errstr;
Question? When I created the database club, is there anything I needed
to do concerning permissions or anything?
I am lost here. I have been writing code on a SUN Oracle systems for
over five years.
Regards,
Jerry
Did you 'grant' user access to all the tables in database club?
-Original Message-
From: Michael Stassen [mailto:[EMAIL PROTECTED]
Sent: Monday, February 28, 2005 9:29 AM
To: Gerald Preston
Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: Re: insert data
From perldoc DBD::mysql
use DBI;
$dsn = DBI:mysql:database=$database;host=$hostname;port=$port;
$dbh = DBI-connect($dsn, $user, $password);
So it's not a syntax problem. Even if it were, we should detect the
error long before calling prepare or execute.
Perl is quite clearly telling you what is wrong. Originally, you got
Can't call method prepare on an undefined value.
for the line
my $sth = $dbh-prepare( $sql );
which means that $dbh is undefined at the time of the call to prepare.
Now, you are getting
Can't call method execute on an un undefined value
for the line
$sth-execute( $group_name, $me, $daily, $item, $unit, $qty, $amount,
$tax, $total ) or warn Cannot execute FIRST
Statement!!\n$DBI::errstr;
which means that $sth is undefined at the time of the call to execute.
Are you showing us select lines of your code, rather than the actual
code? My best guess right now is that you haven't taken into account
that my is a scoping operator in perl, and in the lines you haven't
showed us, the variables in question ($dbh or $sth) go out of scope.
Michael
John Doe wrote:
Am Sonntag, 27. Februar 2005 22.19 schrieb Gerald Preston:
Hi Gerald
The object used:
my $dbh=DBI-connect( 'DBI:mysql:database=club', 'xxx, 'x', {
PrintError = 0} ) or die $DBI::errstr;
I didn't see this part in your first post :-)
Hmm... I've never seen a '=' in the first argument passed to
DBI-connect...
Here's an functional example I'm using:
my $db ='database';
my $host ='hostname';
my $port ='1234';
$dbh=DBI-connect(DBI:mysql:$db:$host:$port,
'a_username',
'a_password',
{RaiseError=1,
AutoCommit=1})
or die $0: $DBI::errstr; }
So, try using club instead of database=club, and a hostname too.
greetings joe
[nothing new below]
-Original Message-
From: John Doe [mailto:[EMAIL PROTECTED]
Sent: Sunday, February 27, 2005 6:37 AM
To: mysql@lists.mysql.com
Subject: Re: insert data
Hi Gerald
I am trying to insert data for the first time using MySQL. In Oracle
I used the following:
# my $sql = insert into bar( group_name, me, daily, item, unit,
qty, amount, tax, total )
# values( ?, ?, ?, ?,?,?,
?, ?, ? ) ;
my $sth = $dbh-prepare( $sql );
die $dbh-errstr if $dbh-err;
$sth-execute( $group_name, $me, $daily, $item, $unit, $qty,
$amount,
$tax, $total ) || die Cannot execute FIRST
Statement!!\n$DBI::errstr;
I keep getting Can't call method prepare on an un undefined value.
All the name listed are correct by looking at MySQLAdmin1.3\4.
Apart from David Logan's answer:
You have to create the $dbh object first (man DBI); the undefined
value in
the error message refers to that.
HTH
joe
--
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061
--
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: insert data
Hi Gerald my last try... i'm not very lucky in helping in this list... This is the actual code except for the : [...] my $sth = $dbh-prepare( $sql ) or die $dbh-errstr if $dbh-err; Maybe this expression is the reason (combination of 'or' and 'if'). Example code: === my $no_error=0; # the wanted case :-) my $a=i_am_defined or die i died! if $no_error; print defined $a ? defined : not defined; === # this prints: not defined === I think the expression my $a=i_am_defined or die i died! is only evaluated if $dbh-err is false. I think you could just write: my $sth = $dbh-prepare( $sql ) or die $dbh-errstr; (omitting if $dbh-err) [...] greetings joe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: insert data
Hi Gerald,
There are some good tutorials on the web for DBI access via perl to
mysql.
http://www.wbluhm.com/MySQLTut.html
http://perl.about.com/od/installandusemysql/l/aa090803b.htm
http://dev.mysql.com/doc/mysql/en/perl.html
and also
http://search.cpan.org/~timb/DBI-1.47/DBI.pm
You should be able to find several examples of exactly what you are
trying to achieve in one of these. The first one has an almost identical
query to that you are trying to achieve. If you can't select from the
table, then you are unlikely to be able to insert. I would follow the
tutorials, even if they are selects, and make sure they work and then
all you have to do is to change the SELECT to an INSERT statement and
away you go.
These have a very thorough examination of the setting up of the dsn etc.
I would also suggest
http://dev.mysql.com/doc/mysql/en/privilege-system.html
This gives a good explanation of how the GRANT/REVOKE/privileges system
works with MySQL. It is slightly different than Oracle and would be well
worth a read.
Regards
David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia
+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax
-Original Message-
From: Gerald Preston [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 1 March 2005 10:10 AM
To: 'William R. Mussatto'; mysql@lists.mysql.com
Subject: RE: insert data
William,
I tried GRANT ALL ON *.* and got error 1064 4200: You have an
error
in your SQL syntax ??
Jerry
-Original Message-
From: William R. Mussatto [mailto:[EMAIL PROTECTED]
Sent: Monday, February 28, 2005 3:25 PM
To: mysql@lists.mysql.com
Subject: RE: insert data
Gerald Preston said:
Michael,
This is the actual code except for the :
my $dbh = DBI-connect( 'DBI:mysql:database=club', '', '', {
PrintError = 0 } ) or die $DBI::errstr;
my $sql = insert into wolfies( group_name, me, daily, item, unit,
qty,
amount, tax, total )
values( ?, ?, ?, ?,?,
?,
?,
?, ? ) ;
my $sth = $dbh-prepare( $sql ) or die $dbh-errstr if $dbh-err;
$sth-execute( $group_name, $me, $daily, $item, $unit, $qty,
$amount,
$tax, $total ) or warn Cannot execute FIRST
Statement!!\n$DBI::errstr;
Question? When I created the database club, is there anything I
needed
to do concerning permissions or anything?
I am lost here. I have been writing code on a SUN Oracle systems for
over five years.
Regards,
Jerry
Did you 'grant' user access to all the tables in database club?
-Original Message-
From: Michael Stassen [mailto:[EMAIL PROTECTED]
Sent: Monday, February 28, 2005 9:29 AM
To: Gerald Preston
Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: Re: insert data
From perldoc DBD::mysql
use DBI;
$dsn = DBI:mysql:database=$database;host=$hostname;port=$port;
$dbh = DBI-connect($dsn, $user, $password);
So it's not a syntax problem. Even if it were, we should detect the
error long before calling prepare or execute.
Perl is quite clearly telling you what is wrong. Originally, you got
Can't call method prepare on an undefined value.
for the line
my $sth = $dbh-prepare( $sql );
which means that $dbh is undefined at the time of the call to prepare.
Now, you are getting
Can't call method execute on an un undefined value
for the line
$sth-execute( $group_name, $me, $daily, $item, $unit, $qty,
$amount,
$tax, $total ) or warn Cannot execute FIRST
Statement!!\n$DBI::errstr;
which means that $sth is undefined at the time of the call to execute.
Are you showing us select lines of your code, rather than the actual
code? My best guess right now is that you haven't taken into account
that my is a scoping operator in perl, and in the lines you haven't
showed us, the variables in question ($dbh or $sth) go out of scope.
Michael
John Doe wrote:
Am Sonntag, 27. Februar 2005 22.19 schrieb Gerald Preston:
Hi Gerald
The object used:
my $dbh=DBI-connect( 'DBI:mysql:database=club', 'xxx, 'x', {
PrintError = 0} ) or die $DBI::errstr;
I didn't see this part in your first post :-)
Hmm... I've never seen a '=' in the first argument passed to
DBI-connect...
Here's an functional example I'm using:
my $db ='database';
my $host ='hostname';
my $port ='1234';
$dbh=DBI-connect(DBI:mysql:$db:$host:$port,
'a_username',
'a_password',
{RaiseError=1,
AutoCommit=1})
or die $0: $DBI::errstr; }
So, try using club instead of database=club, and a hostname too.
greetings joe
[nothing new below]
-Original Message-
From: John Doe [mailto:[EMAIL PROTECTED]
Sent: Sunday, February 27, 2005 6:37 AM
To: mysql@lists.mysql.com
Subject: Re: insert data
Hi Gerald
I am trying to insert data for the first time using MySQL. In
Oracle
I used the following:
# my $sql = insert into bar( group_name, me, daily, item, unit,
qty, amount, tax, total )
#
Re: Geologic Time
Peter Brawley wrote, The earliest possible MySQL date is around 1000CE, so you could not store geologic dates in MySQL date cols. Million years before present is the geologic time unit that would most likely cohere with other geo databases, isn't it? Then the Cambrian would show up around 580 mya, the beginning of recorded human history around .005 mya, c. A float, double or decimal col would handle such values. Yes, I was thinking of using decimals, especially if I can out a way to transform them into other numbers. For example, I might want to display 100,000 years rather than .1 mya in some instances. --- Gary Richardson [EMAIL PROTECTED] wrote: If you want to represent 290 million years as an integer (290,000,000): - An UNSIGNED INT can store 4,294,967,295 - A UNSIGNED BIGINT can store 18,446,744,073,709,551,615 In your schema, I'd use a start_period and end_period instead of a varchar. It's easier to sort and do math on. You could factor out 1,000,000 from your dates and use a float to represent the numbers. 290.00 could represent 290 mya, while 0.01 represents 10,000 years ago. Just make sure there is enough precision on your float. I haven't worked with floats yet, so I don't understand what you mean, but I'll look into it. I could also create two columns - one with decimals based on a billion (e.g. 1 billion = 1, while 100,000 = .1) and the other pegged to million (e.g. 1 million = 1, and 1 billion = 1,000). Fortunately, there aren't many geologic periods, epochs, etc. to work with, so this shouldn't be too hard. Thanks for all the tips! __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fetching tables
Dears ,I have following code segment:
MYSQL_ROW row;
ptrm.tblres=mysql_list_tables(ptrm.connection2db,%);
cout mysql_num_rows(ptrm.tblres);
while (row=mysql_fetch_row(ptrm.tblres))
for (i=0;imysql_num_rows(ptrm.tblres);i++)
{
cout row[i] endl;
} //end for
//
I need to print name of my tables in my database.
I have 3 tables.But it can print 2 tables, for printting of 3th table,it
generate a Segment Fault message.
Please help me.
Yours,Mohsen
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL and triggers
Hey. We are two girls who are new to using MySQL. We are using MySQL 4.1, and we wonder if this version supports the use of triggers? We have tried to find the answer ourself, but with no luck - can anyone help us? Best regards Marianne -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL and triggers
On Monday, February 28, 2005 18:35, [EMAIL PROTECTED] wrote: Hey. We are two girls who are new to using MySQL. We are using MySQL 4.1, and we wonder if this version supports the use of triggers? We have tried to find the answer ourself, but with no luck - can anyone help us? Not in 4.1. Triggers aren't supported until 5.0.2. http://dev.mysql.com/doc/mysql/en/using-triggers.html -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Transparent schema update for replicated installs
(Did I send this off to the list already? It doesn't seem to be updated! ) I've been thinking recently about supporting transparent schema update of production systems in order to deliver zero downtime. What I'm thinking of is a scenario where you use a load balancer to take one of the slaves, alter its schema, and then promote it to master. It should be possible to perform runtime updates of schema with the following steps: * Starting with a cluster of MySQL boxes replicated to a master as slaves. * Take one of the MySQL boxes which is curently setup to replicate from the master and run all ALTER TABLE commands on it. * Then let it catch up to the master so that its only a few seconds behind. * Then run the ALTER TABLE commands on the other MySQL slaves one at a time (or concurrently) (each after they've been taken out of production). * Promote the current slave to the current master by doing all the normal config of a master and then disabling the slave setup. * Change the load balancer to have the NEW master be the ALTER'd slave From: masterdb.server.com - 10.0.0.2 To: masterdb.server.com - 10.0.0.3 * This will allow new MySQL clients to start using the new master (which is now ready but has locks blocked). * Then lock the tables on the current master. * Kill ALL connections of the current master so that the slaves and clients reconnect. There are a few problems with this approach (but it shows some serious potential) * What if your clients are in the middle of a transaction? They'll have to failover correctly to the new master. * This really isn't LIVE alter as it would take a few seconds for everything to switch over. * The slaves will need their binary log settings fixed (or can I just run RESET MASTER) to correct these? * What other steps am I missing? -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: copy DB data from FreeBSD to Redhat9.0
Gleb Paharenko wrote: Hello. I also want to copy the user accounts in MySQL5.0 db over to MySQL4.1 in Redhat as well. All user accounts and rights are stored in the tables of mysql database. And fields of grant tables in 4.1.x version just a subset of fields in 5.0.x. You should set the values of fields in 4.1.x version to values of corresponding fields in 5.0.x. For example you may store 5.0.x mysql database under different name in 4.1.x and then move accounts using SQL statements. Hi, thanks for your help. I just able to backup all the database (with their tables I believe) from mysql 5.0. And now I stuck at restoring the database and their tables as well as user accounts rights to mysql4.1. I used the following command backup database: mysqldump --user=$USERNAME --password=$PASSWORD --host=$DBHOST --quote-names --opt I don't think this command backup user account info. Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
