BDB storage engine
Hi, There is an extensive documentation for InnoDB, but I can't find any extensive for BDB (except http://dev.mysql.com/doc/mysql/en/bdb-storage-engine.html ). Can I change the isolation level in the same way than with InnoDB ? Can I also do SELECT FOR UPDATE ? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How does a multi-row INSERT work?
Ok. I believe you got your answer... for the syntax... I just want to add that this is faster because... using this you only modify the index file once. Lets see for: INSERT INTO x VALUES (a,b); INSERT INTO x VALUES (c,d); The server does: open table INSERT INTO x VALUES (a,b); update index close table open table INSERT INTO x VALUES (c,d); update index close table But for INSERT INTO x VALUES (a,b),(c,d); The server does: open table INSERT INTO x VALUES (a,b),(c,d); update index close table Now it's clear why multi-row INSERT is faster. Of course THIS IS a faulty explanation (grosso modo in latin) but show somehow what's going on ! Gabriel PREDA - Original Message - From: Chris W. Parker [EMAIL PROTECTED] Subject: How does a multi-row INSERT work? Hello, I searched the archives, looked through the manual, and searched google for info on how to actually perform a multi-row INSERT but didn't find an answer. Would someone please show me the syntax for this please? I could just do a loop and INSERT the data that way but according to the manual, a multi-row INSERT is faster. Thanks, Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Can't create a new thread (errno 11). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug'
Hi, Yes - am using the standard binaries and have even upgraded to mysql-standard-4.1.10a-pc-linux-gnu-i686. I'm still getting this error - does anyone have any ideas? Cheers, Andrew -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Thu 31 March 2005 02:31 To: mysql@lists.mysql.com Subject: Re: Can't create a new thread (errno 11). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug' Hello. I strongly recommend you to upgrade to the latest release. Do you use official binaries? See: http://dev.mysql.com/doc/mysql/en/crashing.html Andrew Braithwaite [EMAIL PROTECTED] wrote: Hi, I'm getting this strange error when there are more than 1100 mysql connections connected to the same server. [EMAIL PROTECTED] mysql]# bin/mysql bin/mysql: connect to server at 'localhost' failed error: 'Can't create a new thread (errno 11). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug' I've had this running fine in the past with MySQL 4.0.17 and Red Hat 7.3 (linux 2.4..) but with the same hardware and MySQL versions using Fedora core 2 (linux 2.6) I am getting these problems. I have checked max_connections and others in my.cnf and all is good. I'm running 'out of the box' linux and 'out of the box' MySQL binaries. Has anyone had this before? I would love to hear your thoughts and ideas.. Cheers for the help, Andrew SQL, Query -- 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]
MySQL inserts and disk full - how to handle gracefully?
Hi All, When you do a insert into a MySQL database and the disk is full, the insert just hangs waiting for that table to become available. This is fine for applications that care about data integrity. In this case I care more about availability and speed and would prefer it if the inserts gracefully returned a nice error instead of waiting forever. However - if the system is running a bit slow and the inserts are queuing and taking a few seconds I wouldn't want them to fail in that case. Does anyone have any experience in this kind of circumventing the data-integrity protecting hang-on disk-full condition? I would love to hear your thoughts and ideas.. Cheers for the help, Andrew SQL, Query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
can't drop innodb table
Hello, I've got an innodb that I try to drop: drop table markets; ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails Ok, I check with show innodb status : 050401 11:13:41 Cannot drop table `dabase/markets` because it is referenced by `dabase/last` then : drop table last; ERROR 1051 (42S02): Unknown table 'last' I'm a bit lost there, how can I drop this table ? (I want to recreate it with innodb_file_per_table option afterwards) -- 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]
Mysql installation information
Hi, I have gone through the sites which you have recomended where it is suggested that the exact version depends on the requirement and the hardware. According to our hardware software specification which is given below, I think we should use (Linux (x86, glibc-2.2, static, gcc),Standard 4.1.10a ) version of mysql server. I have no previous experience regarding installing mysql so it will be a great help if let me know your openion about it. Can you please review the URL http://dev.mysql.com/downloads/mysql/4.1.html and recomend the exact version which will go best with the server. Server Configuration - Server: Dual Xeon 2.8 GHz ( X86,32 bit,533MHz FSB,512KB L2 cache) Primary HDD: 73 GB SCSI RAM: ECC Registered 1024MB RAM Bandwidth: 2000 GB Bandwidth Uplink Port Speed: 100 Mbps Uplink Operating System: Red Hat Enterprise Linux, Version 3 (recommended) Drive Controller: SCSI Chassis Control: DRAC Card With Regards, Suryya
Re: Mysql installation information
download the source tar ball and try installing mysql from the source the configure script does it all you , u dont need to worry, for custom compilation use ./configure --help Regards Digvijoy Chatterjee Suryya Ghosh wrote: Hi, I have gone through the sites which you have recomended where it is suggested that the exact version depends on the requirement and the hardware. According to our hardware software specification which is given below, I think we should use (Linux (x86, glibc-2.2, static, gcc),Standard 4.1.10a ) version of mysql server. I have no previous experience regarding installing mysql so it will be a great help if let me know your openion about it. Can you please review the URL http://dev.mysql.com/downloads/mysql/4.1.html and recomend the exact version which will go best with the server. Server Configuration - Server: Dual Xeon 2.8 GHz ( X86,32 bit,533MHz FSB,512KB L2 cache) Primary HDD: 73 GB SCSI RAM: ECC Registered 1024MB RAM Bandwidth: 2000 GB Bandwidth Uplink Port Speed: 100 Mbps Uplink Operating System: Red Hat Enterprise Linux, Version 3 (recommended) Drive Controller: SCSI Chassis Control: DRAC Card With Regards, Suryya -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql installation information
If the Redhat Enterprise edition is anything like the SuSE Enterprise then you have Mysql installed already. It won't be 4.1.10a most likely 4.0.x. Just down load the 4.1.10a rpm and install it over the top of the existing version. Kevin Cowley Product Development Alchemetrics Ltd SMARTER DATA , FASTER Tel: 0118 902 9000 (swithcboard) Tel: 0118 902 9099 (direct) Web: www.alchemetrics.co.uk Email: [EMAIL PROTECTED] -Original Message- From: Suryya Ghosh [mailto:[EMAIL PROTECTED] Sent: 01 April 2005 12:05 To: mysql@lists.mysql.com Subject: Mysql installation information Hi, I have gone through the sites which you have recomended where it is suggested that the exact version depends on the requirement and the hardware. According to our hardware software specification which is given below, I think we should use (Linux (x86, glibc-2.2, static, gcc),Standard 4.1.10a ) version of mysql server. I have no previous experience regarding installing mysql so it will be a great help if let me know your openion about it. Can you please review the URL http://dev.mysql.com/downloads/mysql/4.1.html and recomend the exact version which will go best with the server. Server Configuration - Server: Dual Xeon 2.8 GHz ( X86,32 bit,533MHz FSB,512KB L2 cache) Primary HDD: 73 GB SCSI RAM: ECC Registered 1024MB RAM Bandwidth: 2000 GB Bandwidth Uplink Port Speed: 100 Mbps Uplink Operating System: Red Hat Enterprise Linux, Version 3 (recommended) Drive Controller: SCSI Chassis Control: DRAC Card With Regards, Suryya ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
error in installing
hello, i have windows 2000 i was not able to finish the server configuration i read in one of the site that sc.exe should be present i searched but could not get the same can anyone help me regards p rao -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.296 / Virus Database: 266.9.0 - Release Date: 3/31/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't create a new thread (errno 11). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug'
Hello. Please switch to the mysql-debug-4.1.10a version and send the error log with resolved stack trace. Include the output of the following statement as well: SHOW VARIABLES; I'm getting this strange error when there are more than 1100 mysql connections connected to the same server. What about ulimits and free memory of your system? Andrew Braithwaite [EMAIL PROTECTED] wrote: Hi, Yes - am using the standard binaries and have even upgraded to mysql-standard-4.1.10a-pc-linux-gnu-i686. I'm still getting this error - does anyone have any ideas?=20 Cheers, Andrew -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Thu 31 March 2005 02:31 To: mysql@lists.mysql.com Subject: Re: Can't create a new thread (errno 11). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug' Hello. I strongly recommend you to upgrade to the latest release. Do you use official binaries? See: http://dev.mysql.com/doc/mysql/en/crashing.html Andrew Braithwaite [EMAIL PROTECTED] wrote: Hi, =20 I'm getting this strange error when there are more than 1100 mysql=20 connections connected to the same server. =20 [EMAIL PROTECTED] mysql]# bin/mysql bin/mysql: connect to server at 'localhost' failed error: 'Can't create a new thread (errno 11). If you are not out of=20 available memory, you can consult the manual for a possible=20 OS-dependent bug' =20 I've had this running fine in the past with MySQL 4.0.17 and Red Hat=20 7.3 (linux 2.4..) but with the same hardware and MySQL versions using=20 Fedora core 2 (linux 2.6) I am getting these problems. =20 I have checked max_connections and others in my.cnf and all is good. I'm running 'out of the box' linux and 'out of the box' MySQL binaries. =20 Has anyone had this before? =20 I would love to hear your thoughts and ideas.. =20 Cheers for the help, =20 Andrew =20 SQL, Query =20 =20 -- For technical support contracts, goto https://order.mysql.com/?ref=3Densita 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] -- 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: can't drop innodb table
Hello. I think that SET FOREIGN_KEY_CHECKS = 0; could help you. Philippe Poelvoorde [EMAIL PROTECTED] wrote: Hello, I've got an innodb that I try to drop: drop table markets; ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails Ok, I check with show innodb status : 050401 11:13:41 Cannot drop table `dabase/markets` because it is referenced by `dabase/last` then : drop table last; ERROR 1051 (42S02): Unknown table 'last' I'm a bit lost there, how can I drop this table ? (I want to recreate it with innodb_file_per_table option afterwards) -- 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: error in installing
Hello. Usually it is possible to install MySQL without sc.exe. See: http://dev.mysql.com/doc/mysql/en/windows-troubleshooting.html prathima rao [EMAIL PROTECTED] wrote: hello, i have windows 2000 i was not able to finish the server configuration i read in one of the site that sc.exe should be present i searched but could not get the same can anyone help me regards p rao -- 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]
Strange behavior
Hi, after extending our MySQL 4.0.23a installation to master-slave configuration two specific queries sended from our JBoss are 25-30 times slower. In our J2EE application which runs under JBoss 3.2.2 we are generating own queries by using a connection from JBoss connection pool. This are prepared statements: 1. select count(distinct m.media_id) from category_tree c_tree, media_2_category m2c, media m, magix_product mp, media_type_2_magix_product mt2mp, media_file mf where c_tree.mandant_id = ? and c_tree.partner_id = ? and c_tree.category_tree_id = m2c.category_tree_id and m2c.media_id = m.media_id and mp.magix_product_id = ? and mp.magix_product_id = mt2mp.magix_product_id and m.media_type_id = mt2mp.media_type_id and mf.media_id = m.media_id and (mf.language_id = ? or mf.language_id is null) and mf.media_file_quality_id = ? and (c_tree.category_tree_id = ? or c_tree.parent_id = ? or c_tree.path like ?) 2. select distinct m.media_id from category_tree c_tree, media_2_category m2c, media m, media_2_partner m2p, magix_product mp, media_type_2_magix_product mt2mp, media_file mf where c_tree.mandant_id = ? and c_tree.partner_id = ? and c_tree.category_tree_id = m2c.category_tree_id and m2c.media_id = m.media_id and m2p.media_id = m.media_id and m2p.partner_id = ? and mp.magix_product_id = ? and mp.magix_product_id = mt2mp.magix_product_id and m.media_type_id = mt2mp.media_type_id and mf.media_id = m.media_id and (mf.language_id = ? or mf.language_id is null) and mf.media_file_quality_id = ? and (c_tree.category_tree_id = ? or c_tree.parent_id = ? or c_tree.path like ?) order by m2p.priority desc limit ?, ? Times: 1. - needed from JBoss 450-500 millis - nedded from normal Java application 15-25 millis 2. needed from JBoss 500-800 millis - nedded from normal Java application 19 millis - nedded from normal Java application 20-30 millis All other sql statements generated by JBoss for entity beans are fast like bevore switching to master-slave configuration. Thru this queries the speed of our service is 2-3 times slower. After spend some hours checking our system, I have no more idea where is the problem. This is our my.cnf: [mysqld] datadir=/drbd/mysql log-bin server-id=20 set-variable= key_buffer=128M set-variable= table_cache=512 set-variable= sort_buffer=8M set-variable= join_buffer_size=8M set-variable= query_cache_size=32M set-variable= record_buffer=4M set-variable= thread_cache_size=400 set-variable= max_connections=300 set-variable= long_query_time=10 log_long_format log_slow_queries innodb_data_file_path = ibdata1:2048M;ibdata2:10M:autoextend #innodb_buffer_pool_size = 384M innodb_buffer_pool_size = 1228M innodb_additional_mem_pool_size = 20M innodb_log_file_size = 100M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 Regards, Rafal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error in installing
hello gleb, thanks for the suggestion i could solve the problem in installing mysql on win 2000 the solution is below for reference Error: Cannot create Windows service for MySql. Error: 0 This error is encountered when you re-install or upgrade MySQL without first stopping and removing the existing MySQL service and install MySQL using the MySQL Configuration Wizard. This happens because when the Configuration Wizard tries to install the service it finds an existing service with the same name. One solution to this problem is to choose a service name other than mysql when using the configuration wizard. This will allow the new service to be installed correctly, but leaves the outdated service in place. While this is harmless it is best to remove old services that are no longer in use. To permanently remove the old mysql service, execute the following command as a user with administrative privileges, on the command-line: C:\sc delete mysql [SC] DeleteService SUCCESSregardsprathima rao - Original Message - From: prathima rao [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, April 01, 2005 6:09 AM Subject: error in installing hello, i have windows 2000 i was not able to finish the server configuration i read in one of the site that sc.exe should be present i searched but could not get the same can anyone help me regards p rao -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hide password when running mysqldump from a batch
No solution for this? Thanks --- rds [EMAIL PROTECTED] wrote: --- What you really want to avoid is having the password on the commandline. File permissions won't matter at all if you end up running a command that puts your password in the output of 'ps'! Command lines are always public information. Put the password for mysqldump in the running user's ~/.my.cnf instead, and tighten the permissions on *that* file. [client] password=Your password goes here I tried that; it does work with mysql but does not appear to work with mysqldump. Is there a way to supply the password to mysqldump when running dump from a batch scipt and avoid showing it on the command line? Thanks in advance -- __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Grants not entirely propagated to slaves?
Hi, it seems my Grants are not entirely propagated from the master to the slave (some are active, some are not). The slave is configured to replicate all databases, and the replication client has all privileges on the master. What is necessary to propagate every single grant? Thanks, -- Nico Sabbi - Officine Digitali - Bologna Tel. 051 - 4187565 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What's up with this syntax?
- Original Message - From: Daniel Kasak [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, March 30, 2005 11:24 PM Subject: What's up with this syntax? update _cached_LinesNotTolling LNT inner join TelecomLinePosting TLP on LNT.Line=TLP.Line inner join TelecomAccountPosting TAP on TLP.TelecomLinePostingID=TAP.DanPK inner join PhoneTypes on TLP.LineType=PhoneTypes.ID set AnnualService=sum(TLP.Service)/1*12, LNT.PhoneType=SitRepDesc, MaxOfInvDate=InvDate where TAP.DanPK=41675 group by TLP.Line It's giving me: 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 'group by TLP.Line' Looks right to me... According to the manual - http://dev.mysql.com/doc/mysql/en/update.html - you can't put a GROUP BY in an UPDATE. Rhino -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 01/04/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can't drop innodb table
Gleb Paharenko wrote: Hello. I think that SET FOREIGN_KEY_CHECKS = 0; could help you. Thanks, I didn't think about it. I've I dumped the DB and recreate it instead. Philippe Poelvoorde [EMAIL PROTECTED] wrote: Hello, I've got an innodb that I try to drop: drop table markets; ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails Ok, I check with show innodb status : 050401 11:13:41 Cannot drop table `dabase/markets` because it is referenced by `dabase/last` then : drop table last; ERROR 1051 (42S02): Unknown table 'last' I'm a bit lost there, how can I drop this table ? (I want to recreate it with innodb_file_per_table option afterwards) -- Philippe Poelvoorde COS Trading Ltd. +44.(0)20.7376.2401 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
deadlock with innodb
Hello,
here is a snippet of my code :
BEGIN
SELECT ... FROM table1, table2 ... FOR UPDATE
is_present = false
if ( we have results ) {
for ( all results ) {
SELECT COUNT(*) FROM table1 ... FOR UPDATE
if ( match all conditions )
is_present = true
}
}
if ( is_present == false ) {
INSERT INTO table1 VALUES ()
INSERT INTO table2 VALUES ()
}
COMMIT
in all errors I do a rollback.
This code is intended to insert a component into 2 tables and must
ensure that the component is unique before inserting. This code is the
same across several clients that try to do the same at the same time.
If I do it by hand with two mysql client, it works (one mysqlclient wait
on the SELECT ... FOR UPDATE while i can insert with the other one, then
the SELECT .. FOR UPDATE returns with the first mysqlclient), but with
my applications, I sometimes get an error 1213 (DEADLOCK) from innodb.
The documentation state that the transaction should be rerun. If I do
it, it works fine. What does cause this deadlock ?
If I trace my queries I could see the inserts going _twice_ and one does
fails on this deadlock. I don't really understand why the two
applications try to insert data since I've specified the FOR UPDATE in
the SELECT to lock insertion of new record. Is there anything I'm
mistaking ?
Thanks for your help,
--
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]
Need help with Stored Procedures iin MySQL 5.0.3-beta
Hi, I am looking to migrate an Informix database to MySQL and trying to write a simple stored procedure in MySQL 5.0.3. I referred to few examples posted and used the statements from them but i get errors while creating this procedure. CREATE PROCEDURE sp_test ( IN user_idinteger, IN seg_id integer ) BEGIN declare p_rows int; set p_rows = 0; select count(*) into p_rows from test where user_id = user_id and seg_id = seg_id; if p_rows = 1 then update test set visits = visits+1 where user_id = user_id and seg_id = seg_id; else INSERT INTO test VALUES (user_id,seg_id, 1); end if; END ERROR: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 7 ERROR 1193 (HY000): Unknown system variable 'p_rows' ERROR 1327 (42000): Undeclared variable: p_rows ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'if p_rows = 1 then update test set visits = visits+1 wher' at line 1 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'else INSERT INTO test VALUES (user_id,seg_id, 1)' at line 1 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'end if' at line 1 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1 Any help would be appreciated. Thanks Dinesh
Re: Need help with Stored Procedures iin MySQL 5.0.3-beta
Dinesh, Do you have something like DELIMITER | before the CREATE PROCEDURE call, and | DELIMITER ; after it? Peter Brawley http://www.artfulsoftware.com - Anchan, Dinesh wrote: Hi, I am looking to migrate an Informix database to MySQL and trying to write a simple stored procedure in MySQL 5.0.3. I referred to few examples posted and used the statements from them but i get errors while creating this procedure. CREATE PROCEDURE sp_test ( IN user_idinteger, IN seg_id integer ) BEGIN declare p_rows int; set p_rows = 0; select count(*) into p_rows from test where user_id = user_id and seg_id = seg_id; if p_rows = 1 then update test set visits = visits+1 where user_id = user_id and seg_id = seg_id; else INSERT INTO test VALUES (user_id,seg_id, 1); end if; END ERROR: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 7 ERROR 1193 (HY000): Unknown system variable 'p_rows' ERROR 1327 (42000): Undeclared variable: p_rows ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'if p_rows = 1 then update test set visits = visits+1 wher' at line 1 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'else INSERT INTO test VALUES (user_id,seg_id, 1)' at line 1 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'end if' at line 1 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1 Any help would be appreciated. Thanks Dinesh No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 4/1/2005 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 4/1/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help with Stored Procedures iin MySQL 5.0.3-beta
Yes it is the delimiter which was giving the errors. I had tried putting those statements inside the script, which didn't work. But when i set it from the command line before executing the script to create the procedure it worked. I am still not clear on how to use delimiter without much pain but i will get there. Thanks for your help. Dinesh From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Friday, April 01, 2005 12:03 PM To: Anchan, Dinesh Cc: mysql@lists.mysql.com Subject: Re: Need help with Stored Procedures iin MySQL 5.0.3-beta Dinesh, Do you have something like DELIMITER | before the CREATE PROCEDURE call, and | DELIMITER ; after it? Peter Brawley http://www.artfulsoftware.com - Anchan, Dinesh wrote: Hi, I am looking to migrate an Informix database to MySQL and trying to write a simple stored procedure in MySQL 5.0.3. I referred to few examples posted and used the statements from them but i get errors while creating this procedure. CREATE PROCEDURE sp_test ( IN user_idinteger, IN seg_id integer ) BEGIN declare p_rows int; set p_rows = 0; select count(*) into p_rows from test where user_id = user_id and seg_id = seg_id; if p_rows = 1 then update test set visits = visits+1 where user_id = user_id and seg_id = seg_id; else INSERT INTO test VALUES (user_id,seg_id, 1); end if; END ERROR: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 7 ERROR 1193 (HY000): Unknown system variable 'p_rows' ERROR 1327 (42000): Undeclared variable: p_rows ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'if p_rows = 1 then update test set visits = visits+1 wher' at line 1 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'else INSERT INTO test VALUES (user_id,seg_id, 1)' at line 1 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'end if' at line 1 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1 Any help would be appreciated. Thanks Dinesh No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 4/1/2005
Re: Need help with Stored Procedures iin MySQL 5.0.3-beta
What I do is prepare the CREATE PROC script in a text editor template which starts with DELIMITER | and ends with | DELIMITER ; PB - Anchan, Dinesh wrote: Yes itis the delimiter which was giving the errors. I had tried putting those statements inside the script, which didn't work. But when i set it from the command line before executing the script to create the procedure it worked. I am still not clear on how to use delimiter without much pain but i will get there. Thanks for your help. Dinesh From: Peter Brawley [mailto:[EMAIL PROTECTED]] Sent: Friday, April 01, 2005 12:03 PM To: Anchan, Dinesh Cc: mysql@lists.mysql.com Subject: Re: Need help with Stored Procedures iin MySQL 5.0.3-beta Dinesh, Do you have something like DELIMITER | before the CREATE PROCEDURE call, and | DELIMITER ; after it? Peter Brawley http://www.artfulsoftware.com - Anchan, Dinesh wrote: Hi, I am looking to migrate an Informix database to MySQL and trying to write a simple stored procedure in MySQL 5.0.3. I referred to few examples posted and used the statements from them but i get errors while creating this procedure. CREATE PROCEDURE sp_test ( IN user_idinteger, IN seg_id integer ) BEGIN declare p_rows int; set p_rows = 0; select count(*) into p_rows from test where user_id = user_id and seg_id = seg_id; if p_rows = 1 then update test set visits = visits+1 where user_id = user_id and seg_id = seg_id; else INSERT INTO test VALUES (user_id,seg_id, 1); end if; END ERROR: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 7 ERROR 1193 (HY000): Unknown system variable 'p_rows' ERROR 1327 (42000): Undeclared variable: p_rows ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'if p_rows = 1 then update test set visits = visits+1 wher' at line 1 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'else INSERT INTO test VALUES (user_id,seg_id, 1)' at line 1 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'end if' at line 1 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1 Any help would be appreciated. Thanks Dinesh No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 4/1/2005 No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 4/1/2005 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 4/1/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't create a new thread (errno 11). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug'
Hi, For me it sounds like a glibc issue. BTW, currently the 4.1.10a build is compiled against glibc-2.2, does MySQL plan to build next releases against glibc-2.3 which seems to handle much better a high number of simultaneous connected threads ? Thanks ! Jocelyn Gleb Paharenko wrote: Hello. Please switch to the mysql-debug-4.1.10a version and send the error log with resolved stack trace. Include the output of the following statement as well: SHOW VARIABLES; I'm getting this strange error when there are more than 1100 mysql connections connected to the same server. What about ulimits and free memory of your system? Andrew Braithwaite [EMAIL PROTECTED] wrote: Hi, Yes - am using the standard binaries and have even upgraded to mysql-standard-4.1.10a-pc-linux-gnu-i686. I'm still getting this error - does anyone have any ideas?=20 Cheers, Andrew -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Thu 31 March 2005 02:31 To: mysql@lists.mysql.com Subject: Re: Can't create a new thread (errno 11). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug' Hello. I strongly recommend you to upgrade to the latest release. Do you use official binaries? See: http://dev.mysql.com/doc/mysql/en/crashing.html Andrew Braithwaite [EMAIL PROTECTED] wrote: Hi, =20 I'm getting this strange error when there are more than 1100 mysql=20 connections connected to the same server. =20 [EMAIL PROTECTED] mysql]# bin/mysql bin/mysql: connect to server at 'localhost' failed error: 'Can't create a new thread (errno 11). If you are not out of=20 available memory, you can consult the manual for a possible=20 OS-dependent bug' =20 I've had this running fine in the past with MySQL 4.0.17 and Red Hat=20 7.3 (linux 2.4..) but with the same hardware and MySQL versions using=20 Fedora core 2 (linux 2.6) I am getting these problems. =20 I have checked max_connections and others in my.cnf and all is good. I'm running 'out of the box' linux and 'out of the box' MySQL binaries. =20 Has anyone had this before? =20 I would love to hear your thoughts and ideas.. =20 Cheers for the help, =20 Andrew =20 SQL, Query =20 =20 -- For technical support contracts, goto https://order.mysql.com/?ref=3Densita 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Making Slave a Master
On Thu, 31 Mar 2005 [EMAIL PROTECTED] wrote: We have one master and one slave database and use the slave for reads. If for some reason our master goes down, we would like to make our slave the master and use it for both writes and reads and then switch to the original configuration when the master is up, which includes updating the master copy. Limited downtime/locking of the second database is OK. Is this something that is easy to do or recommended? If so, what steps we need to go through or where can I find isome nformation regarding this? If not, what other approachs are there (assuming we only have two machines w/ above configuration). Hi, it's not easy, but if you take great precaution and have a thorough understanding of replication you will be able to pull it off. Have you considered a master - master (dual master) replication setup? It's covered in the High Performance MySQL book by Derek Balling and Jeremy Zawodny: http://dev.mysql.com/books/hpmysql-excerpts/ch07.html However, be very careful if you have any AUTO_INCREMENT fields. Good luck! :) Atle - Flying Crocodile Inc, Unix Systems Administrator -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
newbie question create table
hi seniors, I'm trying to create a table, here my table properties, create table user ( UserID int primary, Password varchar (20), User_stats int multi ); i'm still confuse in User_stats properti's that is multi, what really use 'multi' is ? Thx before the guide, Aji __ Yahoo! Messenger Show us what our next emoticon should look like. Join the fun. http://www.advision.webevents.yahoo.com/emoticontest -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: stored procedure calling another database
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Is it possible to have a stored procedure query another database? I have two databases where the second (B) uses information from (A) to make decisions. It would be great if the stored procedure on database B could query A, so that it can make decisions. I am using mysql 5.0.3 if it matters. Thanx. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCTbkVikQgpVn8xrARAk3tAJ9hgTrZZktxWhQc7JsH5jObS9NbcACfZBo0 NmYnKlIeJEzBiqUpaYsdTzg= =eDx8 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: stored procedure calling another database
James Black [EMAIL PROTECTED] wrote on 04/01/2005 04:11:49 PM: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Is it possible to have a stored procedure query another database? I have two databases where the second (B) uses information from (A) to make decisions. It would be great if the stored procedure on database B could query A, so that it can make decisions. I am using mysql 5.0.3 if it matters. Thanx. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCTbkVikQgpVn8xrARAk3tAJ9hgTrZZktxWhQc7JsH5jObS9NbcACfZBo0 NmYnKlIeJEzBiqUpaYsdTzg= =eDx8 -END PGP SIGNATURE- If you mean can you query another database on the same server, the answer is YES. If you mean query another database on a different server, I don't know for certain but I don't think so. All you have to do to query a table in any database on your server is to qualify the table's name with the name of the database it is in. For instance, I can see a list of all of the user accounts of the server I am logged into if I say SELECT * from mysql.user; and because I put the database's name in front of the table name, it doesn't matter which database I run it from (which database I USE-ed last). I don't expect that behavior to change just because a query is inside a stored procedure. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Newbie: MYSQL nested query question
I upgraded my local mysql version to 4.1.10a and the below query finally works :) How can I now amend the query so it works on my remote server running mysql 3.23.58 ? From one headache to another ;) SELECT ( SELECT City FROM Cities WHERE CityID = N.CityID ), N.Distance FROM Cities C JOIN Nbc N ON C.CityID = N.PrimaryCityID WHERE C.City = 'Los Angeles' AND N.Distance 20 many many thanks to all those that replied :) g On Mar 31, 2005, at 11:49 PM, Philip M. Gollucci wrote: Graham Anderson wrote: What is the proper way to say this ? SELECT C.City, N.Distance FROM Cities C JOIN Nearbycities N ON C.CityId =ci N.PrimaryCityId WHERE N.CityId = (SELECT Cities.CityId FROM Cities WHERE Cities.city = 'Los Angeles') AND N.distance 20 I am trying to enter in a city and get all the nearby cites with 20 miles Somehow, I need to join NearbyCities.PrimaryCityId, Cities.CityId, and Cities.city learning :) Unless I missed something... Why did you make it so hard ? SQL is meant to be easy :) SELECT c.city, n.distance FROM Cities c, Nearbycities n WHERE c.cityid = n.primarycityid AND c.city = 'Los Angeles' AND n.distance 20 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: stored procedure slower than not using stored procedure?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I am curious if this should be the norm, that the stored procedure took 879 ms when I called it 9 times, with slightly different values, and the non-stored procedure test took 512ms with also slightly different values. I am using jdk1.5 and mysql 5.0.3 on Solaris 8. Thanx. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCTcI+ikQgpVn8xrARAic6AJ0QiAlSYq/MGpNNLj7sEfHabKUkPQCdEjIO Ccq+YOUiTNeXI/wF0xar+fM= =namZ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie: MYSQL nested query question
What was wrong with Graham's simpler query? PB - Graham Anderson wrote: I upgraded my local mysql version to 4.1.10a and the below query finally works :) How can I now amend the query so it works on my remote server running mysql 3.23.58 ? From one headache to another ;) SELECT ( SELECT City FROM Cities WHERE CityID = N.CityID ), N.Distance FROM Cities C JOIN Nbc N ON C.CityID = N.PrimaryCityID WHERE C.City = 'Los Angeles' AND N.Distance 20 many many thanks to all those that replied :) g On Mar 31, 2005, at 11:49 PM, Philip M. Gollucci wrote: Graham Anderson wrote: What is the proper way to say this ? SELECT C.City, N.Distance FROM Cities C JOIN Nearbycities N ON C.CityId =ci N.PrimaryCityId WHERE N.CityId = (SELECT Cities.CityId FROM Cities WHERE Cities.city = 'Los Angeles') AND N.distance 20 I am trying to enter in a city and get all the nearby cites with 20 miles Somehow, I need to join NearbyCities.PrimaryCityId, Cities.CityId, and Cities.city learning :) Unless I missed something... Why did you make it so hard ? SQL is meant to be easy :) SELECT c.city, n.distance FROM Cities c, Nearbycities n WHERE c.cityid = n.primarycityid AND c.city = 'Los Angeles' AND n.distance 20 -- 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 outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 4/1/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie: MYSQL nested query question
In the simple query... the city field showed the result 'Los Angeles' in every row the distance field showed incorrect results to :( City| Distance Los Angeles 18 Los Angeles 5 Los Angeles 7 ... On Apr 1, 2005, at 1:59 PM, Peter Brawley wrote: What was wrong with Graham's simpler query? PB - Graham Anderson wrote: I upgraded my local mysql version to 4.1.10a and the below query finally works :) How can I now amend the query so it works on my remote server running mysql 3.23.58 ? From one headache to another ;) SELECT ( SELECT City FROM Cities WHERE CityID = N.CityID ), N.Distance FROM Cities C JOIN Nbc N ON C.CityID = N.PrimaryCityID WHERE C.City = 'Los Angeles' AND N.Distance 20 many many thanks to all those that replied :) g On Mar 31, 2005, at 11:49 PM, Philip M. Gollucci wrote: Graham Anderson wrote: What is the proper way to say this ? SELECT C.City, N.Distance FROM Cities C JOIN Nearbycities N ON C.CityId =ci N.PrimaryCityId WHERE N.CityId = (SELECT Cities.CityId FROM Cities WHERE Cities.city = 'Los Angeles') AND N.distance 20 I am trying to enter in a city and get all the nearby cites with 20 miles Somehow, I need to join NearbyCities.PrimaryCityId, Cities.CityId, and Cities.city learning :) Unless I missed something... Why did you make it so hard ? SQL is meant to be easy :) SELECT c.city, n.distance FROM Cities c, Nearbycities n WHERE c.cityid = n.primarycityid AND c.city = 'Los Angeles' AND n.distance 20 -- 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 outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 4/1/2005 -- 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: Need help coverting MDB SQL
I would recommend that you use perl. 1. Install perl on your Windows box. I use Active State's distribution. 2. Install DBI, DBD::ODBC, and DBD::mysql. The commands you use are: ppm install DBI ppm install DBD-ODBC ppm install DBD-mysql 3. Create a DSN for your Access database. 4. Now you can write perl scripts to read data from one database and write to the second database. I would expect that you will have some data conversions issues that you will have to work through. For example, as I remember Access has a weird format for dates. You put a '#' in literals when you specify them. The good think about using perl is that you can handle all of these problems. Hope that helps, Bill +--- | Bill MacAllister | 14219 Auburn Road | Grass Valley, CA 95949 --On Saturday, March 26, 2005 10:35:04 AM -0500 Ricky Groleau [EMAIL PROTECTED] wrote: Microsofts Acesss that makes ASP pages. The databsae is saved as MDB. Yes, it does have a uplink, my problem is lack of experience with SQL. It asks for localhost, but of course that i smy computer and I dont have any SQL or anything on it. I tried to send it to my sever, but I am lost..is it the IP or web address? Where is SQL hidden on a linux/apache server? ---Original Message--- From: Rhino Subject: Re: Need help coverting MDB SQL Sent: Mar 26 2005 10:26:58 - Original Message - From: Ricky Groleau To: Sent: Saturday, March 26, 2005 10:13 AM Subject: Need help coverting MDB SQL Hello, I have tried and i have failed. I am trying to help out a non-profit site in their move. The old site had a forum run by MDB and in the transfer as you know...it won't work. I have used PHPBB and I like it and want to use it. We need to convert the MDB to SQL. I have DL 2 converters...and MySql..again I have failed. The database is almost 9 meg. I need someone that can convert this for me and then explain how to upload since it is so big. I have read you cannot to it thru the web/mysql? Any help...guidancePLEASE! I'm not familiar with MDB but I'm assuming that it is some kind of database, like DB2 or Oracle. Does MDB have any kind of export facility? All of the good databases - and even a lot of lousy ones - have some way to convert the database format into one or more text files. There might be a single file for all of the data or a separate file for each table in the database. If MDB has such a facility, you can almost certainly use MySQLs import facilities to convert the export files from MDB into MySQL tables. Does MDB have some kind of export capability? If you don't know, can you point me to where the MDB documentation is located online and perhaps I can figure it out for you. Rhino -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.8.3 - Release Date: 25/03/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] ---Original Message--- +--- | Bill MacAllister | 14219 Auburn Road | Grass Valley, CA 95949 | 530-272-8555 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie: MYSQL nested query question
strangely, the query works intermittently :( SELECT ( SELECT City FROM Cities WHERE CityId = N.CityId ), N.Distance FROM Cities C JOIN Nbc N ON C.CityId = N.PrimaryCityId WHERE C.City = 'Los Angeles' AND N.Distance 20 sometimes it works...other times it gives the mysql query error: show keys from tbl_properties.php: Missing parameter: table huh ? On Apr 1, 2005, at 2:21 PM, Graham Anderson wrote: In the simple query... the city field showed the result 'Los Angeles' in every row the distance field showed incorrect results to :( City| Distance Los Angeles 18 Los Angeles 5 Los Angeles 7 ... On Apr 1, 2005, at 1:59 PM, Peter Brawley wrote: What was wrong with Graham's simpler query? PB - Graham Anderson wrote: I upgraded my local mysql version to 4.1.10a and the below query finally works :) How can I now amend the query so it works on my remote server running mysql 3.23.58 ? From one headache to another ;) SELECT ( SELECT City FROM Cities WHERE CityID = N.CityID ), N.Distance FROM Cities C JOIN Nbc N ON C.CityID = N.PrimaryCityID WHERE C.City = 'Los Angeles' AND N.Distance 20 many many thanks to all those that replied :) g On Mar 31, 2005, at 11:49 PM, Philip M. Gollucci wrote: Graham Anderson wrote: What is the proper way to say this ? SELECT C.City, N.Distance FROM Cities C JOIN Nearbycities N ON C.CityId =ci N.PrimaryCityId WHERE N.CityId = (SELECT Cities.CityId FROM Cities WHERE Cities.city = 'Los Angeles') AND N.distance 20 I am trying to enter in a city and get all the nearby cites with 20 miles Somehow, I need to join NearbyCities.PrimaryCityId, Cities.CityId, and Cities.city learning :) Unless I missed something... Why did you make it so hard ? SQL is meant to be easy :) SELECT c.city, n.distance FROM Cities c, Nearbycities n WHERE c.cityid = n.primarycityid AND c.city = 'Los Angeles' AND n.distance 20 -- 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 outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 4/1/2005 -- 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]
Hex data in VARBINARY fields -- Is it me, or MySQL?
OK so... I'm having this problem where I'm trying to store (rather small (36-byte)) hex values in MySQL, but some of them end up getting truncated, therefore breaking my app... I'm using 4.1.10, with --default-table-type=InnoDB... Or what ever option that is anyway... point is, all of these tables are InnoDB... Here's the problem.. mysql CREATE TABLE `table` (`field` VARBINARY(36) DEFAULT NULL); Query OK, 0 rows affected (0.01 sec) mysql INSERT INTO `table` SET field = 0xdee96318a69c8ba3208e1b2e91233725f5bb99a4708df7ad367c7285041bbb17652a0f20; Query OK, 1 row affected (0.00 sec) mysql SELECT HEX(`field`) FROM `table` WHERE `field` = 0xdee96318a69c8ba3208e1b2e91233725f5bb99a4708df7ad367c7285041bbb17652a0f20; Empty set (0.00 sec) mysql SELECT HEX(`field`) FROM `table`; ++ | HEX(`field`) | ++ | DEE96318A69C8BA3208E1B2E91233725F5BB99A4708DF7AD367C7285041BBB17652A0F | ++ 1 row in set (0.00 sec) mysql SELECT HEX(`field`) FROM `table` WHERE `field` = 0xdee96318a69c8ba3208e1b2e91233725f5bb99a4708df7ad367c7285041bbb17652a0f; ++ | HEX(`field`) | ++ | DEE96318A69C8BA3208E1B2E91233725F5BB99A4708DF7AD367C7285041BBB17652A0F | ++ 1 row in set (0.00 sec) AND... to make things worse I have a unique index on the column, so it breaks even more mysql CREATE UNIQUE INDEX `index` ON `table` (`field`); Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql INSERT INTO `table` SET field = 0xdee96318a69c8ba3208e1b2e91233725f5bb99a4708df7ad367c7285041bbb17652a0f20; ERROR 1062 (23000): Duplicate entry '??c ?#7%p???6|r?e*' for key 1 I'd appreciate any Ideas anyone has any ideas/suggestions... I'd hate to unnecessarily submit a bug report, if it's something on my end. THANKS!!! --Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Hex data in VARBINARY fields -- Is it me, or MySQL?
On Friday, April 01, 2005 17:57, Adam Wilson wrote: OK so... I'm having this problem where I'm trying to store (rather small (36-byte)) hex values in MySQL, but some of them end up getting truncated, therefore breaking my app... I'm using 4.1.10, with --default-table-type=InnoDB... Or what ever option that is anyway... point is, all of these tables are InnoDB... Here's the problem.. -- mysql CREATE TABLE `table` (`field` VARBINARY(36) DEFAULT NULL); Query OK, 0 rows affected (0.01 sec) mysql INSERT INTO `table` SET field = 0xdee96318a69c8ba3208e1b2e91233725f5bb99a4708df7ad367c7285041bbb17652a0f20; Query OK, 1 row affected (0.00 sec) mysql SELECT HEX(`field`) FROM `table` WHERE `field` = 0xdee96318a69c8ba3208e1b2e91233725f5bb99a4708df7ad367c7285041bbb17652a0f20; Empty set (0.00 sec) mysql SELECT HEX(`field`) FROM `table`; ++ |HEX(`field`) ++ | DEE96318A69C8BA3208E1B2E91233725F5BB99A4708DF7AD367C7285041BBB17652A0F ++ 1 row in set (0.00 sec) ...[snip]... I'd appreciate any Ideas anyone has any ideas/suggestions... I'd hate to unnecessarily submit a bug report, if it's something on my end. THANKS!!! --Adam You need to you a blob column type instead. Varbinary strips trailing spaces (0x20). Refer to the folowing page for further explanation. http://dev.mysql.com/doc/mysql/en/blob.html Quote from page: There is no trailing-space removal for BLOB and TEXT columns when values are stored or retrieved. Before MySQL 5.0.3, this differs from VARBINARY and VARCHAR, for which trailing spaces are removed when values are stored. -- 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]
UNION ALL and GROUP BY
Hi all, I've got 3 or 4 queries UNIONed together in a single query. I want to GROUP the UNIONed result, by one field, and SUM() another field. Is that possible in one query, or will I need to use temporary table and group it from that? Thanks, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: UNION ALL and GROUP BY
On Friday, April 01, 2005 19:27, Chris wrote: Hi all, I've got 3 or 4 queries UNIONed together in a single query. I want to GROUP the UNIONed result, by one field, and SUM() another field. Is that possible in one query, or will I need to use temporary table and group it from that? Thanks, Chris I don't think you can tacka group by directly on the end of a union. Try something like this: select groupcol, sum(sumcol) from (select col1 as groupcol, col2 as sumcol from table1 UNION select col1 as groupcol, col2 as sumcol from table2) as tmptable group by groupcol; Obviously you can add in your where clause. This is messy but it should work. This is basically using a temp table without 2-stepping it. If you look at the explain it will say using temporary. -- 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]
Re: UNION ALL and GROUP BY
Tom Crimmins wrote: On Friday, April 01, 2005 19:27, Chris wrote: Hi all, I've got 3 or 4 queries UNIONed together in a single query. I want to GROUP the UNIONed result, by one field, and SUM() another field. Is that possible in one query, or will I need to use temporary table and group it from that? Thanks, Chris I don't think you can tacka group by directly on the end of a union. Try something like this: select groupcol, sum(sumcol) from (select col1 as groupcol, col2 as sumcol from table1 UNION select col1 as groupcol, col2 as sumcol from table2) as tmptable group by groupcol; Obviously you can add in your where clause. This is messy but it should work. This is basically using a temp table without 2-stepping it. If you look at the explain it will say using temporary. Oh, that's cool. I didn't know you could do that. Thanks. The power of the sub-query never ceases to amaze me. Don't know how I lived without it Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to group records by using regular expression?
Hi Everybody I need to group records by regular expression Example: Table has column - names (just example) records are: idnames time 1 John, Max 12:15 2 Max, Jeff 15:55 3 Ken 20:45 I need to get grouped records by name Max in column names SELECT COUNT(*) FROM my_table GROUP BY reg_expression Could somebody help me? Thanx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to group records by using regular expression?
On Saturday, April 02, 2005 01:51, ON.KG wrote: Hi Everybody I need to group records by regular expression Example: Table has column - names (just example) records are: idnames time 1 John, Max 12:15 2 Max, Jeff 15:55 3 Ken 20:45 I need to get grouped records by name Max in column names SELECT COUNT(*) FROM my_table GROUP BY reg_expression Are you just looking for a count of names that contain max? SELECT COUNT(*) FROM my_table WHERE names REGEXP 'max'; or SELECT COUNT(*) FROM my_table WHERE names LIKE '%max%'; If you are really trying to group the names you can do this with a regex but this will only give you at most three rows (two if the column doesn't allow nulls). This is because the regex can only evaluate to true, false, or null. If you want just the ones that contain Max grouped together and everything else separate you could rig it with an if. SELECT names, COUNT(*) FROM my_table GROUP BY if(names REGEXP 'max', -1, names) The -1 would need to be replaced by a value that will not exist in the table. This is why I said it is rigged. Maybe if you could explain why you are trying to do this, someone could help you come up with a better solution. Could somebody help me? Thanx -- 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]
Re[2]: How to group records by using regular expression?
Hi Everybody Thank you, Tom! I really trying to group the names But I think I found another way - I made PHP-script which groups results from database after selection And I suspect it will be a little complicated for MySQL to group all this data, 'cause variants in reg.exp. pattern is too large something like (yes|no|may be|can not|uknown|.) or MySQL are able to group it fast enough? faster than PHP? Thanx TC On Saturday, April 02, 2005 01:51, ON.KG wrote: Hi Everybody I need to group records by regular expression Example: Table has column - names (just example) records are: idnames time 1 John, Max 12:15 2 Max, Jeff 15:55 3 Ken 20:45 I need to get grouped records by name Max in column names SELECT COUNT(*) FROM my_table GROUP BY reg_expression TC Are you just looking for a count of names that contain max? TC SELECT COUNT(*) FROM my_table WHERE names REGEXP 'max'; TC or TC SELECT COUNT(*) FROM my_table WHERE names LIKE '%max%'; TC If you are really trying to group the names you can do this with TC a regex but this will only give you at most three rows (two if TC the column doesn't allow nulls). This is because the regex can TC only evaluate to true, false, or null. If you want just the ones TC that contain Max grouped together and everything else separate TC you could rig it with an if. TC SELECT names, COUNT(*) FROM my_table TC GROUP BY if(names REGEXP 'max', -1, names) TC The -1 would need to be replaced by a value that will not exist TC in the table. This is why I said it is rigged. TC Maybe if you could explain why you are trying to do this, someone TC could help you come up with a better solution. Could somebody help me? Thanx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie :create table multi, index
hi seniors, I'm trying to create a table, here my table properties, create table user ( UserID int primary, Password varchar (20), User_stats int multi ); i'm still confuse in User_stats properti's that is multi, what really use 'multi' is ? and what the conection between primary key and index Thx before the guide, Aji __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
