Re: Granting all to a user with a db name prefix
Paco Zarabozo A. wrote: Hello All, I'm trying to GRANT ALL to a user only on DBs that math a prefix, but i can't find the way to so it on the documentation. Let's assume the username is john. I want him to have all privileges only on databases with the prefix john, so he can: - create and drop databases starting ONLY with john (like john_sessions, john123, john_mytest, john_mail, etc) - have any kind of privileges on such databases According to the documentation, i can use % and _ as wildcards. However, mysql gives my an error if i try to use % wildcard. Only _ wildcard is accepted, but the following example: GRANT ALL ON JOHN_.* to 'JOHN'@'%' IDENTIFIED BY 'foo'; ..only allows user john to create databases starting with john, followed by ONE single character. Using this, i can give 32 different grants in order to allow up to 32 characters after 'john', but i'm sure that's not the way. If i try the wildcard %, i get an error. I've tried the following: GRANT ALL ON JOHN%.* to 'JOHN'@'%' IDENTIFIED BY 'foo'; GRANT ALL ON 'JOHN%'.* to 'JOHN'@'%' IDENTIFIED BY 'foo'; GRANT ALL ON JOHN%.* to 'JOHN'@'%' IDENTIFIED BY 'foo'; GRANT ALL ON 'JOHN%'.'*' to 'JOHN'@'%' IDENTIFIED BY 'foo'; GRANT ALL ON JOHN*.* to 'JOHN'@'%' IDENTIFIED BY 'foo'; GRANT ALL ON 'JOHN*'.* to 'JOHN'@'%' IDENTIFIED BY 'foo'; ..and almost all similar ways. Am i missing something? I temporarily fixed the problem by directly editing mysql.db to change the wildcard _ for % in the respective record, and it works fine. However, i really want to know the right way to do it. I hope someone there gives me the answer. Thanks a lot, have fun. Francisco If you look closely, the answer is in the example at the end of the paragraph you cite from the manual: GRANT ... ON `foo\_bar`.* TO ... You need to quote with backticks, the one thing you didn't try. Hence, GRANT ALL ON `JOHN%`.* to 'JOHN'@'%' IDENTIFIED BY 'foo'; should work. And yes, I would agree that's poorly documented. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stored procedures
Hi list I'm trying to make stored procedures use parameters for limit and tables, I guess this is either me using the wrong datatype or it's not possible. I'm having the same issue with seting the table for the query: CREATE PROCEDURE sp_test1 (IN some_limit int) select * from some_table limit some_limit; and CREATE PROCEDURE sp_test2 (IN some_table table) select * from some_table; Both fail with ERROR 1064 (42000). Can someone please shed some light on this for me? Is this a problem with procedures not being able to do this or is it wrong datatypes or something completely different? Thanks in advance Jon
MySQL Cluster
Hi ALL I want to implement MySQL Cluster, are there any step by step guide to implement it Thanks and Regards Kaushal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Cluster
Hi Kaushal, I hav the strong impression you did not look at all to find the answers you seek. A quick search on www.mysql.com gave me 836 hits. My advice would be: Go start reading some documentation regarding Clusters in general and the use of MySQL in such a configuration. Kind Regards, -- Peter M. Groen Open Systems Development Klipperwerf 12 2317 DZ Leiden T : +31-(0)71-5216317 M : +31-(0)6-29563390 E : [EMAIL PROTECTED] Skype : peter_m_groen quote who=Kaushal Shriyan Hi ALL I want to implement MySQL Cluster, are there any step by step guide to implement it Thanks and Regards Kaushal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message was scanned with clamAV version 0.88, clamav-milter version 0.87. and is guaranteed free of viruses. -- This message was scanned with clamAV version 0.88, clamav-milter version 0.87. and is guaranteed free of viruses. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Cluster
Hello Kaushal, You can get the MySQL clustering details from the following link. http://dev.mysql.com/doc/refman/5.0/en/mysql-cluster-quick.html http://dev.mysql.com/doc/refman/5.0/en/index.html Thanks, ViSolve DB Team - Original Message - From: Kaushal Shriyan [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, July 25, 2006 3:11 PM Subject: MySQL Cluster Hi ALL I want to implement MySQL Cluster, are there any step by step guide to implement it Thanks and Regards Kaushal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.10.4/396 - Release Date: 7/24/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored procedures
Hello Jon. Could you tell me the version of MySql ?. You can find the version by excuting the command SELECT version() If the version is below 5, the stored procedure feature would not work . Thanks Visolve DB Team. - Original Message - From: Jon [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, July 25, 2006 2:40 PM Subject: Stored procedures Hi list I'm trying to make stored procedures use parameters for limit and tables, I guess this is either me using the wrong datatype or it's not possible. I'm having the same issue with seting the table for the query: CREATE PROCEDURE sp_test1 (IN some_limit int) select * from some_table limit some_limit; and CREATE PROCEDURE sp_test2 (IN some_table table) select * from some_table; Both fail with ERROR 1064 (42000). Can someone please shed some light on this for me? Is this a problem with procedures not being able to do this or is it wrong datatypes or something completely different? Thanks in advance Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Cluster
On 7/25/06, Kaushal Shriyan [EMAIL PROTECTED] wrote: Hi ALL I want to implement MySQL Cluster, are there any step by step guide to implement it Thanks and Regards Kaushal Hi Is cluster suite is available only in version of MySQL 5 and above. Regards Kaushal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: undefined reference to `mysqlpp::Connection::Connection(bool)'
hi all.and tank you mr logan.i read the ldconfig man page but cant find any things. when i execute following command: #ldconfig -v | grep mysql the following lines appeared: ldconfig: Can't stat /usr/X11R6/lib/Xaw95: No such file or directory ldconfig: Can't stat /usr/X11R6/lib/Xaw3d: No such file or directory ldconfig: Can't stat /usr/i486-linux/lib: No such file or directory ldconfig: Can't stat /usr/i486-linux-libc5/lib: No such file or directory ldconfig: Can't stat /usr/i486-linux-libc6/lib: No such file or directory ldconfig: Can't stat /usr/i486-linuxaout/lib: No such file or directory ldconfig: Can't stat /usr/i386-suse-linux/lib: No such file or directory ldconfig: Can't stat /usr/openwin/lib: No such file or directory ldconfig: Can't stat /opt/kde/lib: No such file or directory ldconfig: Can't stat /opt/kde2/lib: No such file or directory ldconfig: Can't stat /opt/gnome2/lib: No such file or directory libmysqlpp.so - libmysqlpp.so /usr/local/mysql/lib/mysql: libmysqlclient.so.15 - libmysqlclient.so.15.0.0 whats the meaning of the last line?i think it tell me libmysqlclient are available.that's right? Logan, David (SST - Adelaide) [EMAIL PROTECTED] wrote: Hi Ali, I'll repeat what I've already said, it is the ld command that is having the problem. If you don't know what the ld command is or does, you need to look elsewhere for the answer as this is beyond the scope of this list. As I have previously mentioned, try $ man ldconfig Your problem is as it states /usr/lib/gcc/i586-suse-linux/4.0.2/../../../../i586-suse-linux/bin/ld: cannot find -lmysqlclient -- here is the problem collect2: ld returned 1 exit status The linker, ld, does not know where to find the library libmysqlclient.so. You need to tell the linker using either ldconfig (man ldconfig) or LD_LIBRARY_PATH variable. eg. export LD_LIBRARY_PATH= Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: ali asghar torabi parizy [mailto:[EMAIL PROTECTED] Sent: Monday, 24 July 2006 7:29 PM To: mysql@lists.mysql.com Subject: RE: undefined reference to `mysqlpp::Connection::Connection(bool)' Hi.thanks logan. I am begeener to MySQL. I have installed Suse10 and MySQL and mysql++ in my pc. i trying too many pathes in gcc cammand but that error continued. I am getting the following error. # gcc -o custom1 -lmysqlclient -L /usr/local/mysql/lib/mysql/ custom1.cpp #gcc -o custom1 -lmysqlclient -L /usr/local/mysqlpp/include/mysql++ custom1.cpp gcc -o custom1 -lmysqlclient -L /usr/local/mysql/lib/mysql/ -L /usr/local/mysqlpp/include/mysql++ custom1.cpp for all statements following error continued:( # /usr/lib/gcc/i586-suse-linux/4.0.2/../../../../i586-suse-linux/bin/ld: cannot find -lmysqlclient collect2: ld returned 1 exit status a-toraby:~/c/example # gcc -o custom1 -lmysqlclient -L /usr/local/mysql/lib/mysql/ custom1.cpp /tmp/ccPQEF5p.o: In function `main': custom1.cpp:(.text+0x2f): undefined reference to `mysqlpp::Connection::Connection(bool)' custom1.cpp:(.text+0x46): undefined reference to `connect_to_db(int, char**, mysqlpp::Connection, char const*)' custom1.cpp:(.text+0x7f): undefined reference to `mysqlpp::Connection::query()' custom1.cpp:(.text+0x96): undefined reference to `std::basic_ostream std::char_traits std::operator (std::basic_ostream , char const*)'... ... ... ... ... ...std::allocator (std::vector , mysqlpp::SQLQueryParms, mysqlpp::query_reset)]+0x50): undefined reference to `std::basic_string, std::allocator ::c_str() const' custom1.cpp:(.gnu.linkonce.t._ZN7mysqlpp5Query7storeinISt6vectorI5stockSaIS3_vRT_RNS_13SQLQueryParmsENS_11query_resetE[void mysqlpp::Query::storein (std::vector , mysqlpp::SQLQueryParms, mysqlpp::query_reset)]+0x71): undefined reference to `std::basic_string, std::allocator ::~basic_string()' custom1.cpp:(.gnu.linkonce.t._ZN7mysqlpp5Query7storeinISt6vectorI5stockSaIS3_vRT_RNS_13SQLQueryParmsENS_11query_resetE[void mysqlpp::Query::storein std::allocator (std::vector , mysqlpp::SQLQueryParms, mysqlpp::query_reset)]+0x88): undefined reference to `std::basic_string, std::allocator ::~basic_string()'
Re: Stored procedures
On 7/25/06, Visolve DB Team [EMAIL PROTECTED] wrote: Hello Jon. Hi there Team :) And thanks for the quick reply Could you tell me the version of MySql ?. You can find the version by excuting the command SELECT version() If the version is below 5, the stored procedure feature would not work . The version shown is 5.0.21-standard (from the rpm MySQL-server-standard-5.0.21-1.rhel3). I have no problem with other sp, like: CREATE PROCEDURE sp_test3 (IN value int) select count(*) from some_table where foo value; It's just defining table and limit I've had problems with (there is also one mentioning this in the manual about creating sp http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html ) So could it be that it's not possible? /Jon Thanks Visolve DB Team. - Original Message - From: Jon [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, July 25, 2006 2:40 PM Subject: Stored procedures Hi list I'm trying to make stored procedures use parameters for limit and tables, I guess this is either me using the wrong datatype or it's not possible. I'm having the same issue with seting the table for the query: CREATE PROCEDURE sp_test1 (IN some_limit int) select * from some_table limit some_limit; and CREATE PROCEDURE sp_test2 (IN some_table table) select * from some_table; Both fail with ERROR 1064 (42000). Can someone please shed some light on this for me? Is this a problem with procedures not being able to do this or is it wrong datatypes or something completely different? Thanks in advance Jon
Re: MySQLHotCopy
Jesse wrote: I read in the manual that MySQLHotCopy would be better than MySQLDump for backing up MyISAM tables. However, I cannot find a .exe in the bin directory by that name. I found a script by that name in a 4.1 installation that I have, but I don't think it's the latest version. Is this a script? If so, does anyone know where can I get the latest copy? I've checked MySQL.com, but can't seem to find it there. Thanks, Jesse Sorry pal: It runs on Unix and NetWare. What version are you using? I assume Windows at least ( if still looking for an .exe :-) ) Then please read this: http://dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html 8.13. mysqlhotcopy — A Database Backup Program mysqlhotcopy is a Perl script that was originally written and contributed by Tim Bunce. It uses LOCK TABLES, FLUSH TABLES, and cp or scp to make a database backup quickly. It is the fastest way to make a backup of the database or single tables, but it can be run only on the same machine where the database directories are located. mysqlhotcopy works only for backing up MyISAM and ARCHIVE tables. It runs on Unix and NetWare. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need Restore Help
Jesse wrote: I have a backup that was created by a MySQL 5 server using MySQLDump. When I try to restore the database using the following command: mysql -u root -p -D BPA c:\backup\mydata.sql I get the error: ERROR 1064 (42000) at line 29765: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/' at line 1 Here is what line 29765 says in the backup file: /*!50003 CREATE TRIGGER `AlumniAddDate` BEFORE INSERT ON `alumni` FOR EACH ROW SET NEW.AddDate=Now() */;; This is obviously one of the triggers that I've created. I don't know why it's commented them out in the backup, but I don't seem to be able to overcome this. I'd rather it ignore these lines anyway. How do I get passed this? Thanks, Are you sure this is the right line - I mean the whole statement? Also please read (with comments): http://dev.mysql.com/doc/refman/5.0/en/comments.html If you add a version number after the ‘!’ character, the syntax within the comment is executed only if the MySQL version is greater than or equal to the specified version number. The TEMPORARY keyword in the following comment is executed only by servers from MySQL 3.23.02 or higher: CREATE /*!32302 TEMPORARY */ TABLE t (a INT); ...perhaps as some comments suggest try to remove single apostrophes /which sometimes puzzle parser/ and see if it works. What puzzles me personally is double ;; at the end?! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQLHotCopy
Sorry pal: It runs on Unix and NetWare. What version are you using? I assume Windows at least ( if still looking for an .exe :-) ) Then please read this: http://dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html 8.13. mysqlhotcopy — A Database Backup Program Aaah. I missed that. Shows how much I read. I went to mysql.com and did a search for MySQLHotCopy, and it turned up this document. I glanced over it quickly and saw that it was just telling me how to run it, but I didn't catch the Pearl Script thing at the very start of the document. I am running Windows XP Pro on my development, and Windows Server 2003 on our actual production machine. While I've got access to Linux here on my development network, I do not on our server. So, I will stick to MySQLDump for the backup then. Thanks for the help. Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need Restore Help
Are you sure this is the right line - I mean the whole statement? You are right, I did not include the whole statement. Here's the entire section: /*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/; DELIMITER ;; /*!50003 SET SESSION SQL_MODE=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION */;; /*!50003 CREATE TRIGGER `AlumniAddDate` BEFORE INSERT ON `alumni` FOR EACH ROW SET NEW.AddDate=Now() */;; DELIMITER ; /*!50003 SET SESSION [EMAIL PROTECTED] */; SELECT VERSION(); on my server returns 5.0.15-nt. This appears to be sufficient to execute the statement. ...perhaps as some comments suggest try to remove single apostrophes /which sometimes puzzle parser/ and see if it works. What puzzles me personally is double ;; at the end?! As you can probably see from the rest of the statement that I've included above, ;; ends the current line becuase the delimiter was changed before hand. Any ideas why this won't execute? I could go through the entire backup file and remove the comments, but this would take quite a while, and I'd rather it execute properly to begin with, but not sure why it's not executing now. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Cluster
On 7/25/06, Kaushal Shriyan [EMAIL PROTECTED] wrote: On 7/25/06, Kaushal Shriyan [EMAIL PROTECTED] wrote: Hi ALL I want to implement MySQL Cluster, are there any step by step guide to implement it Thanks and Regards Kaushal Hi Is cluster suite is available only in version of MySQL 5 and above. Regards Kaushal Hi ALL Is cluster suite is available only in version of MySQL 5 and above. Regards Kaushal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Cluster
Hello, MySQL Cluster has been available since version 4.1. For production purposes we recommend the GA version of 5.0. For the testing of new features (Disk-Data, Replication, etc) take a look at the latest 5.1 version. Thanks, Jimmy Guerrero Sr Product Manager MySQL, Inc -Original Message- From: Kaushal Shriyan [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 25, 2006 8:33 AM To: mysql@lists.mysql.com Subject: Re: MySQL Cluster On 7/25/06, Kaushal Shriyan [EMAIL PROTECTED] wrote: On 7/25/06, Kaushal Shriyan [EMAIL PROTECTED] wrote: Hi ALL I want to implement MySQL Cluster, are there any step by step guide to implement it Thanks and Regards Kaushal Hi Is cluster suite is available only in version of MySQL 5 and above. Regards Kaushal Hi ALL Is cluster suite is available only in version of MySQL 5 and above. Regards Kaushal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Stored procedures
If I understand correctly, what you need is prepared statements. http://dev.mysql.com/doc/refman/5.0/en/sqlps.html Dan. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jon Sent: Tuesday, July 25, 2006 7:44 AM To: Visolve DB Team Cc: mysql@lists.mysql.com; Sena Subject: Re: Stored procedures On 7/25/06, Visolve DB Team [EMAIL PROTECTED] wrote: Hello Jon. Hi there Team :) And thanks for the quick reply Could you tell me the version of MySql ?. You can find the version by excuting the command SELECT version() If the version is below 5, the stored procedure feature would not work . The version shown is 5.0.21-standard (from the rpm MySQL-server-standard-5.0.21-1.rhel3). I have no problem with other sp, like: CREATE PROCEDURE sp_test3 (IN value int) select count(*) from some_table where foo value; It's just defining table and limit I've had problems with (there is also one mentioning this in the manual about creating sp http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html ) So could it be that it's not possible? /Jon Thanks Visolve DB Team. - Original Message - From: Jon [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, July 25, 2006 2:40 PM Subject: Stored procedures Hi list I'm trying to make stored procedures use parameters for limit and tables, I guess this is either me using the wrong datatype or it's not possible. I'm having the same issue with seting the table for the query: CREATE PROCEDURE sp_test1 (IN some_limit int) select * from some_table limit some_limit; and CREATE PROCEDURE sp_test2 (IN some_table table) select * from some_table; Both fail with ERROR 1064 (42000). Can someone please shed some light on this for me? Is this a problem with procedures not being able to do this or is it wrong datatypes or something completely different? Thanks in advance Jon -- This message has been scanned for viruses by TechTeam's email gateway. --- This e-mail transmission is strictly confidential and intended solely for the person or organization to whom it is addressed. It may contain privileged and confidential information and if you are not the intended recipient, you must not copy, distribute or take any action in reliance on it. If you have received this e-mail in error, please notify the sender as soon as possible and delete the e-mail message and any attachment(s). This message has been scanned for viruses by TechTeam's email gateway. ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Make Error with libz.la / configure Warning with term.h/ptem.h on Solaris 9
Hi Claude, all! [EMAIL PROTECTED] wrote: Hi, I am compiling/installing MySQL 5.0.22 on Solaris 9 (patched). I received the following error message . Making all in zlib make[2]: Entering directory `/tmp/mysql-5.0.22/zlib' /bin/bash ../libtool --preserve-dup-deps --tag=CC --mode=link gcc -O3 -DDBUG_OFF -D_FILE_OFFSET_BITS=64 -DHAVE_RWLOCK_T -o libz.la [[...]] (cd .libs rm -f libz.so.1 ln -s libz.so.1.2.3 libz.so.1) (cd .libs rm -f libz.so ln -s libz.so.1.2.3 libz.so) false cru .libs/libz.a adler32.o compress.o crc32.o deflate.o gzio.o infback.o inffast.o inflate.o inftrees.o trees.o uncompr.o zutil.o make[2]: *** [libz.la] Error 1 [[...]] I can verbatim copy from a mail of mine to this list, sent 2006-Feb-8: | The last command line quoted really looks suspicious: | false cru .libs/libz.a adler32.o compress.o ... | | A false command is bound to fail! | | Looking at the parameters, this should certainly be | ar cru .libs/libz.a adler32.o compress.o ... | | I guess you have some setting like AR=false or similar, | check and correct that. That other request was about MySQL 5.0.18 on Solaris 10, but I do not remember any answer how that continued. I have no idea why libtool causes such problems on Solaris. Do other calls libtool . --mode=link succeed in your build ? Which version of libtool do you have installed ? Typical versions we use are 1.4.3 and 1.5.6, depending on the hosts, but I still suspect somehow AR has got a wrong value. HTH, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored procedures
But the scope of a prepared statement is only the session? I want a stored procedure to avoid some sql in clientside code...Or do you mean a prep in the stored? /Jon On 7/25/06, Burke, Dan [EMAIL PROTECTED] wrote: If I understand correctly, what you need is prepared statements. http://dev.mysql.com/doc/refman/5.0/en/sqlps.html Dan. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jon Sent: Tuesday, July 25, 2006 7:44 AM To: Visolve DB Team Cc: mysql@lists.mysql.com; Sena Subject: Re: Stored procedures On 7/25/06, Visolve DB Team [EMAIL PROTECTED] wrote: Hello Jon. Hi there Team :) And thanks for the quick reply Could you tell me the version of MySql ?. You can find the version by excuting the command SELECT version() If the version is below 5, the stored procedure feature would not work . The version shown is 5.0.21-standard (from the rpm MySQL-server-standard-5.0.21-1.rhel3). I have no problem with other sp, like: CREATE PROCEDURE sp_test3 (IN value int) select count(*) from some_table where foo value; It's just defining table and limit I've had problems with (there is also one mentioning this in the manual about creating sp http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html ) So could it be that it's not possible? /Jon Thanks Visolve DB Team. - Original Message - From: Jon [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, July 25, 2006 2:40 PM Subject: Stored procedures Hi list I'm trying to make stored procedures use parameters for limit and tables, I guess this is either me using the wrong datatype or it's not possible. I'm having the same issue with seting the table for the query: CREATE PROCEDURE sp_test1 (IN some_limit int) select * from some_table limit some_limit; and CREATE PROCEDURE sp_test2 (IN some_table table) select * from some_table; Both fail with ERROR 1064 (42000). Can someone please shed some light on this for me? Is this a problem with procedures not being able to do this or is it wrong datatypes or something completely different? Thanks in advance Jon -- This message has been scanned for viruses by TechTeam's email gateway. --- This e-mail transmission is strictly confidential and intended solely for the person or organization to whom it is addressed. It may contain privileged and confidential information and if you are not the intended recipient, you must not copy, distribute or take any action in reliance on it. If you have received this e-mail in error, please notify the sender as soon as possible and delete the e-mail message and any attachment(s). This message has been scanned for viruses by TechTeam's email gateway. ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Make Error with libz.la / configure Warning with term.h/ptem.h on Solari
Hi Joerg, Thank for your answer. Her e are some details that you requested. That other request was about MySQL 5.0.18 on Solaris 10, but I do not remember any answer how that continued. I have no idea why libtool causes such problems on Solaris. I already red this email, but this has not explain/given a clear solution. I can confirm that the AR value in my Makefile (or I am not right? ) is setted to false. I tried to set this AR field to true (or blank) , but it has nothing brought. Do other calls libtool . --mode=link succeed in your build ? The make try some Entering directory /tmp/mysql-5.0.22 Nothing to be done Leaving , until the /zlib directory. It seems in my make that is the first call and after that the make stopped Which version of libtool do you have installed ? The libtool used is 1.5.20 . Typical versions we use are 1.4.3 and 1.5.6, depending on the hosts, but I still suspect somehow AR has got a wrong value. I hope that is help you Regards Claude -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql + LVS
--- Jimmy Guerrero [EMAIL PROTECTED] wrote: Hello, Just to make sure we separate the two... mysql cluster that preforms reads on all the slaves, and writes only on the master. The above can be used to describe a typical use case for MySQL Replication. In a MySQL Cluster there is no need to load-balance your reads and writes. Thanks, Jimmy Guerrero MySQL, Inc -Original Message- From: Winn Johnston [mailto:[EMAIL PROTECTED] Sent: Monday, July 24, 2006 3:26 PM To: mysql@lists.mysql.com Subject: mysql + LVS How stable has it become to run an instance of mysql ontop of a LVS cluster to produce a massively parrallel system to be used for huge databases? Back in the day i remember tyring to do it with mosix, not sure where they are now a days. but either way i am looking for some current information on the subject. I know this differs from the idea that you can have a mysql cluster that preforms reads on all the slaves, and writes only on the master. thanks winn johnston ok so LVS is not what i am looking for. I have been reading about some software hacks that allow mysql to use shared memory spread out over a few machines. scenerio, Huge database, over 7 million records. And a high ratio of writes vs reads. Idealy i would like to have the entire database in RAM. Does anyone have any experience running a database this big? can anyone offer a suitalbe setup? I have taken a look at the wikipedia setup http://meta.wikimedia.org/wiki/Wikimedia_servers#Overall_system_architecture thanks -winn johnston __ 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]
Re: Stored procedures
On Tuesday 25 July 2006 02:10 am, Jon wrote: CREATE PROCEDURE sp_test1 (IN some_limit int) select * from some_table limit some_limit; and CREATE PROCEDURE sp_test2 (IN some_table table) select * from some_table; Well, first off with stored procedures the format is: DELIMITER $$ CREATE PROCEDURE name () BEGIN .. END $$ DELIMITER ; DELIMITER is done so you can use ;'s within the stored procedure. The other thing too is that you're trying to select a table by a variable. That doesn't quite work, and I've tried a dozen or so variations myself hoping it would. You know, I'd almost LIKE someone to go No you're wrong, you just need to do this... ;) -- Chris White PHP Programmer/DBlast Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored procedures
On 7/25/06, Chris White [EMAIL PROTECTED] wrote: On Tuesday 25 July 2006 02:10 am, Jon wrote: CREATE PROCEDURE sp_test1 (IN some_limit int) select * from some_table limit some_limit; and CREATE PROCEDURE sp_test2 (IN some_table table) select * from some_table; Well, first off with stored procedures the format is: DELIMITER $$ CREATE PROCEDURE name () BEGIN .. END $$ DELIMITER ; DELIMITER is done so you can use ;'s within the stored procedure. no need for using blocks and setting delimiter when it's only a single query The other thing too is that you're trying to select a table by a variable. That doesn't quite work, and I've tried a dozen or so variations myself hoping it would. You know, I'd almost LIKE someone to go No you're wrong, you just need to do this... ;) Well, that's kinda what I want to hear. A simple yes or no for both tables and limits. I take it tables are a nono considering you've tried so many things. Anyone who knows if the same is true for limits?
yes or no checkbox
know this is probably a simple question but if im going to setup a yes or no checkbox on a table feild on MySQL thru phpmyadmin, how do i do that ?? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: yes or no checkbox
I think if you do it as a enum field, it shows as a checkbox in phpmysql define it as enum 'Yes','No' -Original Message- From: Brian E Boothe [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 25, 2006 11:07 AM To: mysql@lists.mysql.com Subject: yes or no checkbox know this is probably a simple question but if im going to setup a yes or no checkbox on a table feild on MySQL thru phpmyadmin, how do i do that ?? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Granting all to a user with a db name prefix
Thanks a lot Michael. I can't believe i didn't see that little detail... that happens when you're just exhausted, i guess. Thanks a lot. :-) Francisco - Original Message - From: Michael Stassen To: Paco Zarabozo A. Cc: Sent: Tuesday, July 25, 2006 1:50 AM Subject: Re: Granting all to a user with a db name prefix Paco Zarabozo A. wrote: Hello All, I'm trying to GRANT ALL to a user only on DBs that math a prefix, but i can't find the way to so it on the documentation. Let's assume the username is john. I want him to have all privileges only on databases with the prefix john, so he can: - create and drop databases starting ONLY with john (like john_sessions, john123, john_mytest, john_mail, etc) - have any kind of privileges on such databases According to the documentation, i can use % and _ as wildcards. However, mysql gives my an error if i try to use % wildcard. Only _ wildcard is accepted, but the following example: GRANT ALL ON JOHN_.* to 'JOHN'@'%' IDENTIFIED BY 'foo'; ..only allows user john to create databases starting with john, followed by ONE single character. Using this, i can give 32 different grants in order to allow up to 32 characters after 'john', but i'm sure that's not the way. If i try the wildcard %, i get an error. I've tried the following: GRANT ALL ON JOHN%.* to 'JOHN'@'%' IDENTIFIED BY 'foo'; GRANT ALL ON 'JOHN%'.* to 'JOHN'@'%' IDENTIFIED BY 'foo'; GRANT ALL ON JOHN%.* to 'JOHN'@'%' IDENTIFIED BY 'foo'; GRANT ALL ON 'JOHN%'.'*' to 'JOHN'@'%' IDENTIFIED BY 'foo'; GRANT ALL ON JOHN*.* to 'JOHN'@'%' IDENTIFIED BY 'foo'; GRANT ALL ON 'JOHN*'.* to 'JOHN'@'%' IDENTIFIED BY 'foo'; ..and almost all similar ways. Am i missing something? I temporarily fixed the problem by directly editing mysql.db to change the wildcard _ for % in the respective record, and it works fine. However, i really want to know the right way to do it. I hope someone there gives me the answer. Thanks a lot, have fun. Francisco If you look closely, the answer is in the example at the end of the paragraph you cite from the manual: GRANT ... ON `foo\_bar`.* TO ... You need to quote with backticks, the one thing you didn't try. Hence, GRANT ALL ON `JOHN%`.* to 'JOHN'@'%' IDENTIFIED BY 'foo'; should work. And yes, I would agree that's poorly documented. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Make Error with libz.la / configure Warning with term.h/ptem.h on Solari
Hi Claude, all! [EMAIL PROTECTED] wrote: Hi Joerg, Thank for your answer. Her e are some details that you requested. That other request was about MySQL 5.0.18 on Solaris 10, but I do not remember any answer how that continued. I have no idea why libtool causes such problems on Solaris. I already red this email, but this has not explain/given a clear solution. I can confirm that the AR value in my Makefile (or I am not right? ) is setted to false. Ok, that is causing all following effects. I tried to set this AR field to true (or blank) , but it has nothing brought. No, it cannot help - the correct command is (as I wrote) ar cru .libs/libz.a adler32.o compress.o ... so you need a setting AR=ar This should be done by configure when it searches your machine for ar, so I suspect you have no ar installed. If you do not know what ar is used for, you need a basic text about compiling, building libraries, and linking: ar is a program to combine several object modules (adler32.o, compress.o, ...) into one library or archive (libz.a), which the linker will use later. You should verify that type ar or which ar gives the name of an executable program, maybe /usr/bin/ar or so. If you have no program ar installed, the build will fail. So either ensure that your PATH includes ar, or install it. Do other calls libtool . --mode=link succeed in your build ? The make try some Entering directory /tmp/mysql-5.0.22 Nothing to be done Leaving , until the /zlib directory. It seems in my make that is the first call and after that the make stopped Ok, at least consistent. Which version of libtool do you have installed ? The libtool used is 1.5.20 . Sounds ok. Typical versions we use are 1.4.3 and 1.5.6, depending on the hosts, but I still suspect somehow AR has got a wrong value. I hope that is help you It is you who needs the help - I hope this here does. Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Row count discrepancy when converting from MyISAM to InnoDB
I have a table of type MyISAM that is reporting 47 million rows when I do a SELECT COUNT(*). When I convert this table to InnoDB, running a SELECT COUNT(*) returns only 19 million rows. The conversion confirms 19 million rows were inserted and reports no warnings or duplicates. I have done the conversion to InnoDB using the following ways 1. by dumping all the data in a text file and loading it. 2. by using ALTER TABLE Why is the record count so low after conversion to InnoDB? Who should I believe: InnoDB or MyISAM? Any ideas as to what can be done to avoid loss of this many rows? I will be posting output from my latest conversion attempt in some time. --Frank
Re: Random Hangs, Linux AMD 64, 5.0.22 AB Binaries
On 7/13/06, Jim Winstead [EMAIL PROTECTED] wrote: Sounds like the well-known problem with Debian stable's glibc on x86_64: http://hashmysql.org/index.php?title=Opteron_HOWTO#pthread_rwlock_wrlock_hang_with_nptl Just for the record, this resolved it for us. We applied the patch mentioned to the debian libc6 sources and tested. Apologies for the delay in replying thanks again for a super fast pointer to the cause of the problem. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slave Replication issues
Hi, we have been losing slaves due to errors in replication. See error
message below. Would appreciate if anyone could share if they have seen same
issues. It seems something may be getting corrupted in the binary log.
Thanks.
The error reads:
Last_error: Error 'Duplicate entry '12312942' for key 1' on query 'INSERT
INTO permissions (user_id, journal_id, date_granted, start_date, end_date,
type) VALUES ('1503443', '94', NOW(), '-00-00 00:00:00', '-00-00
00:00:00', 'author')'. Default database: 'manuscript_central_1_1'
Dave Nance
ScholarOne, Inc.
375 Greenbrier Dr. Suite 200
Charlottesville VA 22901-1618
P: (434) 817-2040 Ext 236
F: (434) 817-2020
[EMAIL PROTECTED]
www.scholarone.com http://www.scholarone.com/
RE: mysql + LVS highjacked (mysql + NFS ramfs)
--- Winn Johnston [EMAIL PROTECTED] wrote: --- Jimmy Guerrero [EMAIL PROTECTED] wrote: Hello, Just to make sure we separate the two... mysql cluster that preforms reads on all the slaves, and writes only on the master. The above can be used to describe a typical use case for MySQL Replication. In a MySQL Cluster there is no need to load-balance your reads and writes. Thanks, Jimmy Guerrero MySQL, Inc -Original Message- From: Winn Johnston [mailto:[EMAIL PROTECTED] Sent: Monday, July 24, 2006 3:26 PM To: mysql@lists.mysql.com Subject: mysql + LVS How stable has it become to run an instance of mysql ontop of a LVS cluster to produce a massively parrallel system to be used for huge databases? Back in the day i remember tyring to do it with mosix, not sure where they are now a days. but either way i am looking for some current information on the subject. I know this differs from the idea that you can have a mysql cluster that preforms reads on all the slaves, and writes only on the master. thanks winn johnston ok so LVS is not what i am looking for. I have been reading about some software hacks that allow mysql to use shared memory spread out over a few machines. scenerio, Huge database, over 7 million records. And a high ratio of writes vs reads. Idealy i would like to have the entire database in RAM. Does anyone have any experience running a database this big? can anyone offer a suitalbe setup? I have taken a look at the wikipedia setup http://meta.wikimedia.org/wiki/Wikimedia_servers#Overall_system_architecture after talking to a few people on the #mysql irc someone suggested using NFS to create a ramfs to get 100GB+ RAM shared memory to load the entire database into the RAM. Can anyone offer any Pros or Cons to this setup, drawing from personal expierence? thanks -winn johnston __ 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]
Re: Row count discrepancy when converting from MyISAM to InnoDB
On Jul 25, 2006, at 11:55 AM, Frank wrote: Why is the record count so low after conversion to InnoDB? Who should I believe: InnoDB or MyISAM? Any ideas as to what can be done to avoid loss of this many rows? InnoDB doesn't keep a count on number of rows, like MyISAM does. InnoDB only maintains an estimate of the number of rows in each table. This is why select count(*) from table takes a long time on big InnoDB tables. Usually the InnoDB count will be off by 50% or so. http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html -- David Hillman LiveText, Inc 1.866.LiveText x235
Re: Slave Replication issues
On Jul 25, 2006, at 12:47 PM, David Nance wrote:
Hi, we have been losing slaves due to errors in replication. See error
message below. Would appreciate if anyone could share if they have
seen same
issues. It seems something may be getting corrupted in the binary log.
Thanks.
The error reads:
Last_error: Error 'Duplicate entry '12312942' for key 1' on query
'INSERT
INTO permissions (user_id, journal_id, date_granted, start_date,
end_date,
type) VALUES ('1503443', '94', NOW(), '-00-00 00:00:00',
'-00-00
00:00:00', 'author')'. Default database: 'manuscript_central_1_1'
One of the indexes on your 'permissions' table in defined to be
'unique', and your application is trying to insert a row with a
duplicate value. This doesn't seem like a replication error, unless
there really isn't a unique index on permissions... but I bet there is.
--
David Hillman
LiveText, Inc
1.866.LiveText x235
RE: mysql + LVS highjacked (mysql + NFS ramfs)
At 02:05 PM 7/25/2006, Winn Johnston wrote: after talking to a few people on the #mysql irc someone suggested using NFS to create a ramfs to get 100GB+ RAM shared memory to load the entire database into the RAM. Can anyone offer any Pros or Cons to this setup, drawing from personal expierence? thanks -winn johnston Do you really mean 100Gb RAM? Is that actually possible today? Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Row count discrepancy when converting from MyISAM to InnoDB
Thank you to everyone who replied. It turned out I had index corruption and after running an OPTIMIZE TABLE I was able to convert all the records to InnoDB. Thanks, Frank
Re: arrays in stored procedures - pl. help
Chris, thank you for the response, but that was not my question. My question is how do I send multiple sets of data into a stored procedure without doing the things I had outlined. On 7/17/06, Chris [EMAIL PROTECTED] wrote: L P wrote: Folks, say I have a need to add multiple rows at the same time. for instance, say I'm collecting customer information and I want to add 3 addresses and 3 phone numbers at the same time for a customer. The above is quite straightforward to accomplish when there is only one set of data to deal with (one address / one phone number) - with simple data types passed in as parameters. What alternatives / options do I have to accomplish storing multiple sets of data? insert into table(field1, field2, field3) values (value1, value2, value3), (value4, value5, value6); http://dev.mysql.com/doc/refman/5.1/en/insert.html Don't use arrays for storage, you'll lose a lot of performance.
Re: arrays in stored procedures - pl. help
My question is how do I send multiple sets of data into a stored procedure without doing the things I had outlined. MySQL doesn't have arrays. Pass the data in a comma-delimited string and PREPARE the statement, or pass it as a temp memory table. PB - L P wrote: Chris, thank you for the response, but that was not my question. My question is how do I send multiple sets of data into a stored procedure without doing the things I had outlined. On 7/17/06, Chris [EMAIL PROTECTED] wrote: L P wrote: Folks, say I have a need to add multiple rows at the same time. for instance, say I'm collecting customer information and I want to add 3 addresses and 3 phone numbers at the same time for a customer. The above is quite straightforward to accomplish when there is only one set of data to deal with (one address / one phone number) - with simple data types passed in as parameters. What alternatives / options do I have to accomplish storing multiple sets of data? insert into table(field1, field2, field3) values (value1, value2, value3), (value4, value5, value6); http://dev.mysql.com/doc/refman/5.1/en/insert.html Don't use arrays for storage, you'll lose a lot of performance. No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.10.4/396 - Release Date: 7/24/2006 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.10.4/396 - Release Date: 7/24/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
change a empty value for 0
Hi all ! i have two tables ingenio and detalle_tanque, detalle_tanque has a foreign key to ingenio, i want to show all the ingenio values and a sum of the cantidad field in the detalle_tanque table for each value in ingenio, but one of the ingenio's values it doesn't exist in detalle_tanque, i get this mysql select id_ingenio, (select case when sum(cantidad) is null then 0 else sum(cantidad) end from detalle_transaccion where id_ingenio=ingenio.id_ingenio group by id_ingenio) as cantidad from ingenio LIMIT 5; ++--+ | id_ingenio | cantidad | ++--+ | 1 | NULL | | 2 | NULL | | 3 | NULL | | 4 | 2622.77 | | 5 | NULL | ++--+ 5 rows in set (0.01 sec) i want to change de value of null to 0, but i don't know why i can't do it with sum(cantidad) is null then 0 else sum(cantidad) end thanks in advanced ... any help it will be good for me ... !!! -- http://www.obed.org.mx --- blog -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: change a empty value for 0
++--+ | id_ingenio | cantidad | ++--+ | 1 | NULL | | 2 | NULL | | 3 | NULL | | 4 | 2622.77 | | 5 | NULL | ++--+ 5 rows in set (0.01 sec) You can use DEFAULT 0 to set a column to 0 by default. You can also do it in one pass through: (test with this first): SELECT id_ingenio,cantidad FROM detalle_transaccion WHERE cantidad IS NULL; if that gives you the correct result set then: UPDATE detalle_transaccion SET cantidad = 0 WHERE cantidad IS NULL; -- Chris White PHP Programmer/DBamBam Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: change a empty value for 0
Try this: select id_ingenio, (select sum(coalesce(cantidad,0)) from detalle_transaccion where id_ingenio=ingenio.id_ingenio group by id_ingenio) as cantidad from ingenio LIMIT 5; obed [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] Hi all ! i have two tables ingenio and detalle_tanque, detalle_tanque has a foreign key to ingenio, i want to show all the ingenio values and a sum of the cantidad field in the detalle_tanque table for each value in ingenio, but one of the ingenio's values it doesn't exist in detalle_tanque, i get this mysql select id_ingenio, (select case when sum(cantidad) is null then 0 else sum(cantidad) end from detalle_transaccion where id_ingenio=ingenio.id_ingenio group by id_ingenio) as cantidad from ingenio LIMIT 5; ++--+ | id_ingenio | cantidad | ++--+ | 1 | NULL | | 2 | NULL | | 3 | NULL | | 4 | 2622.77 | | 5 | NULL | ++--+ 5 rows in set (0.01 sec) i want to change de value of null to 0, but i don't know why i can't do it with sum(cantidad) is null then 0 else sum(cantidad) end thanks in advanced ... any help it will be good for me ... !!! -- http://www.obed.org.mx --- blog -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: change a empty value for 0
Hi. thanks ! but nop... SELECT id_ingenio,cantidad FROM detalle_transaccion WHERE cantidad IS NULL; Empty set (0.00 sec) and with coalesce nop :-( it's the same problem mysql select id_ingenio, (select case when sum( coalesce(cantidad,NULL) ) is null then 0 else sum(cantidad) end from detalle_transaccion where id_ingenio=ingenio.id_ingenio group by id_ingenio) as cantidad from ingenio LIMIT 5; ++--+ | id_ingenio | cantidad | ++--+ | 1 | NULL | | 2 | NULL | | 3 | NULL | | 4 | 2622.77 | | 5 | NULL | ++--+ 5 rows in set (0.00 sec) the problem is that my sub select returns a empty result, it isn't a null value, but somthing straing is happening if i make only the sub select look mysql select case when sum(cantidad) is null then 0 else sum(cantidad) end as a from detalle_transaccion where id_ingenio=1 group by id_ingenio; Empty set (0.00 sec) i think that the group by is doing this... becouse look select case when sum(cantidad) is null then 0 else sum(cantidad) end as a from detalle_transaccion where id_ingenio=1; +--+ | a| +--+ |0 | +--+ 1 row in set (0.01 sec) what can i do ? thanks -- http://www.obed.org.mx --- blog -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: change a empty value for 0
Isn't this over-complicated select id_ingenio, sum(cantidad) from ingenio, detalle_transaccion where ingenio.id_ingenio = detalle_transaccion.id_ingenio group by ingenio.id_ingenio or select id_ingenio, sum(cantidad) from ingenio left join detalle_transaccion on ingenio.id_ingenio = detalle_transaccion.id_ingenio group by ingenio.id_ingenio or select id_ingenio, sum(ifnull(cantidad,0)) from ingenio left join detalle_transaccion on ingenio.id_ingenio = detalle_transaccion.id_ingenio group by ingenio.id_ingenio or am I missing something? Quentin -Original Message- From: obed [mailto:[EMAIL PROTECTED] Sent: Wednesday, 26 July 2006 11:12 a.m. To: João Cândido de Souza Neto Cc: mysql@lists.mysql.com Subject: Re: change a empty value for 0 Hi. thanks ! but nop... SELECT id_ingenio,cantidad FROM detalle_transaccion WHERE cantidad IS NULL; Empty set (0.00 sec) and with coalesce nop :-( it's the same problem mysql select id_ingenio, (select case when sum( coalesce(cantidad,NULL) ) is null then 0 else sum(cantidad) end from detalle_transaccion where id_ingenio=ingenio.id_ingenio group by id_ingenio) as cantidad from ingenio LIMIT 5; ++--+ | id_ingenio | cantidad | ++--+ | 1 | NULL | | 2 | NULL | | 3 | NULL | | 4 | 2622.77 | | 5 | NULL | ++--+ 5 rows in set (0.00 sec) the problem is that my sub select returns a empty result, it isn't a null value, but somthing straing is happening if i make only the sub select look mysql select case when sum(cantidad) is null then 0 else sum(cantidad) end as a from detalle_transaccion where id_ingenio=1 group by id_ingenio; Empty set (0.00 sec) i think that the group by is doing this... becouse look select case when sum(cantidad) is null then 0 else sum(cantidad) end as a from detalle_transaccion where id_ingenio=1; +--+ | a| +--+ |0 | +--+ 1 row in set (0.01 sec) what can i do ? thanks -- http://www.obed.org.mx --- blog -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Returning results as a field name
Hello MySQL List I have a simple table that outputs results like so: select name, question_id, answer from table; +---+--+-+ |NAME |QUESTION_ID |ANSWER | +---+--+-+ |Mark |100 |Yes | |Mark |101 |No | |Leigh |100 |Yes | |Leigh |101 |No | +---+--+-+ Is there a way to query things so the result looks like this: select ??? ++---+---+ |NAME|100|101| ++---+---+ |Mark|Yes|No | |Leigh |No |Yes| ++---+---+ cheers Mark Dale [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can Innodb reuse the deleted rows disk space?
hi, Dilipkumar Thank you very much! I think I know the fact: The Innodb can't reuse the deleted rows' disk space. And a solution is: dump the data; shutdown mysql; delete the files; restart mysql; import the data. Regards, Leo Huang 2006/7/24, [EMAIL PROTECTED] [EMAIL PROTECTED]: Hi, Try using the optimize table tablename ,but this will keep the data accordingly,but really if it is a disk space constraint you can go with re-org process in which you will have to get a down time for mysql db.Process is something like . Dump all the Innodb tables drop the existing innodb tables and shutdown mysql, clear the Innodb log-space as ibdata1 indata2 iblogfile0 iblogfile1 and also the redo logs of the innodb. Then start the mysql this will create innodb logs 1 innodb2 as what u have mentioned in ur cnf file and import the dump . In this case u can able to reduce the space usage of innodb. Try this it might help u out. With Regards Dilipkumar [EMAIL PROTECTED]: Hi, all I know the Innodb use MVCC to achieve very high concurrency. Can Innodb reuse the deleted rows disk space? I have an database which have many update operation. If Innodb can\'t reuse the space of deleted rows, I worry about that MySQL will exhaust our disk space very quickly. Any recommend will be welcome! Regards, Leo Huang -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can Innodb reuse the deleted rows disk space?
leo huang wrote: hi, Dilipkumar Thank you very much! I think I know the fact: The Innodb can't reuse the deleted rows' disk space. And a solution is: dump the data; shutdown mysql; delete the files; restart mysql; import the data. InnoDB does re-use the space inside the database, it's the logfiles that are growing. The logs are needed in case you need to replay transactions. I suggest you read this page: http://dev.mysql.com/doc/refman/5.1/en/adding-and-removing.html and this page: http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html Specify 2-3 entries in the innodb_data_file_path and mysql should (if I'm reading it properly) rotate between the files and keep size under control. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Will UPDATE block on SELECT?
Ratheesh K J wrote: I would like to know whether a SELECT query would block an Update on the same table. The table is of InnoDB type. Since InnoDB tables apply row level locks should the Update queries be blocked until the select query completes? I experienced such a scenario wherein an update query had to wait until the select query completed. Also how different is the locking when there is a CREATE TEMPORARY TABLE tblname AS SELECT * FROM TBL_TEST and an Update on the table TBL_TEST simultaneously? http://dev.mysql.com/doc/refman/5.1/en/innodb-transaction-model.html and http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html might give you some ideas about what's going on. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to control the number of sql dameons?
Hi All, Could you please explain why some one needs multiple sql daemons and how to control the number of dameons. Thanks, Prem
Re: Returning results as a field name
Mark Dale wrote: Hello MySQL List I have a simple table that outputs results like so: select name, question_id, answer from table; +---+--+-+ |NAME |QUESTION_ID |ANSWER | +---+--+-+ |Mark |100 |Yes | |Mark |101 |No | |Leigh |100 |Yes | |Leigh |101 |No | +---+--+-+ Is there a way to query things so the result looks like this: select ??? ++---+---+ |NAME|100|101| ++---+---+ |Mark|Yes|No | |Leigh |No |Yes| ++---+---+ No, but you can get it to look like this: mysql select n.name, n.question_id, n2.answer from blah n, blah n2 where n.name=n2.name and n.answer=n2.answer; +---+-++ | name | question_id | answer | +---+-++ | chris | 1 | yes| | chris | 2 | no | +---+-++ 2 rows in set (0.00 sec) You need to do a self-join on all but one column so you can get the rows to become columns. Make sure you have indexes on all joining columns otherwise it will be slow. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to control the number of sql dameons?
At 9:09 AM +0530 7/26/06, Prem wrote: Hi All, Could you please explain why some one needs multiple sql daemons and how to control the number of dameons. Do you believe that you have multiple daemons? If so, why? If you're looking at the output of ps or top and you see a bunch of mysqld processes, that doesn't necessarily indicate that you have multiple multiple daemons. For example, on Linux, you're see one mysqld per thread, but there is still just one mysqld. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to control the number of sql dameons?
Prem wrote: Hi All, Could you please explain why some one needs multiple sql daemons and how to control the number of dameons. It forks so it can handle multiple queries at the same time. The main process (mysqld_safe) handles starting up the other processes and various other things (I assume user authentication), shutting down the service and so on. I'm not sure if you can control the number though, maybe it has something to do with max_connections? Does the manual say anything? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is this privilege case even possible
One database, multiple tables. I need to limit access to one table per user, but also allow all other tables to be accessed by all users. For example: Say I have 5 users: usera, userb, userc, userd, usere Table 1 - all 5 users, select, inset, update, delete Table 2 - all 5 users, select, inset, update, delete Table 3 - only userc, select, inset, update, delete When I add in a new table, such as table 4, I would like it to be all 5 users. So basically, I just want to lock out one table, and allow only one user to that table. I have tried several ways, and read over the docs, and I am just not seeing how to make this work. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
