MySQL vs MsSQL or about Views and Stored Procedures
Hi Community! I'm producing Databasedriven Webapplications here in Austria using Databasesystems and PHP - sometimes even C++ So thats what my situation looks like! One of the most interesting Databaseserver is MySQL ... It's quite fast ... you can do nearly everything u can do with commercial Databases and ... its free! The is just only little problem to be solved: On our old MsSQL Database server there is defined a set of Database Views and Stored Procedures which arn't supported right now! Yesterday i've read a manual which told me that you plan to support Views at Version 4.1. But there wasn't given any date when you are planing to release it? Is there any? And when do you support Stored procedures and Triggers if you don't actually? Many Thx! Peter Austria - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Why I can't run Mysql program ? Please Help !
Dear Marek, Phillip and all, Frist I would like to say thanks to Marek, Phillip and someone help me before ! Finially, the server is running ( need to use the command #mysql --host=192.10.0.113). But I still have some question would like to ask, if you don't mind ! How I can set a different user to run MySQL (only root can run this program now) ? bash-2.03$ ./mysql --host=192.10.0.113 bash: ./mysql: Permission denied Thanks a lot, Kevin Chan Phillip B. Bruce Wrote Kevin, If you have forgotten the root user password for MySQL, you can restore it with the following procedure: 1.Take down the mysqld server by sending a kill (not kill -9) to the mysqld server. The pid is stored in a .pid file, which is normally in the MySQL database directory: kill `cat /mysql-data-directory/hostname.pid` You must be either the Unix root user or the same user the server runs as to do this. 2.Restart mysqld with the --skip-grant-tables option. 3.Connect to the mysqld server with mysql -h hostname mysql and change the password with a GRANT command. See section 4.3.1 GRANT and REVOKE Syntax. You can also do this with mysqladmin -h hostname -u user password 'new password' 4.Load the privilege tables with: mysqladmin -h hostname flush-privileges or with the SQL command FLUSH PRIVILEGES. Note that after you started mysqld with --skip-grant-tables, any usage of GRANT commands will give you an Unknown command error until you have executed FLUSH PRIVILEGES. Marek Zaradzki Wrote mysql --host=192.10.0.113 or mysqladmin --host=192.10.0.113 status - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Bind on unix socket: Permission denied
Hello All, I am new to Mysql. I installed Mysql-3.23.46 source in RedHat-7.1linux. i run mysql_insyall_db and when i started safe_mysql # ./safe_mysqld --user=mysql [1] 19554 # Starting mysqld daemon with databases from /var/lib/mysql 011228 12:32:19 mysqld ended mysqld.log shows 011228 12:18:51 mysqld started 011228 12:18:51 Can't start server : Bind on unix socket: Permission denied 011228 12:18:51 Do you already have another mysqld server running on socket: /var/lib/mysql/mysql.sock ? 011228 12:18:51 Aborting 011228 12:18:51 /usr/local/libexec/mysqld: Shutdown Complete 011228 12:18:51 mysqld ended I read document that it is because of port already in use. but no application using the port. i tried with configuring different tcp port but got same error. Please guide me to over come this problem. Thanks Sam _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: GUI for Windows
MySQL Manager, http://www.mysqlmanager.com/ A lot of features including Export/Import Data, Visual Query Builder, Extract/Print Metadata, Syntax HighLight, BLOB Editor and so on. HTH, Vadim - Original Message - From: John Mayson [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Tuesday, December 25, 2001 5:41 AM Subject: GUI for Windows Eventually my databases will be on my Linux system. In the meantime, is there a Windows program that builds a quick and dirty GUI interface for my SQL databases? Thanks, John -- John Mayson KC4VJO Linux/Perl/HP-UX/ICT Austin, Texas 78729 30.4560N 97.7851W [EMAIL PROTECTED] http://www.nyx.net/~jmayson - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Bind on unix socket: Permission denied
i run mysql_insyall_db and when i started safe_mysql If you were able to run mysql_instal_db, then mysqld was already running. # ./safe_mysqld --user=mysql [1] 19554 # Starting mysqld daemon with databases from /var/lib/mysql 011228 12:32:19 mysqld ended mysqld.log shows 011228 12:18:51 mysqld started 011228 12:18:51 Can't start server : Bind on unix socket: Permission denied 011228 12:18:51 Do you already have another mysqld server running on socket: /var/lib/mysql/mysql.sock ? You did. 011228 12:18:51 Aborting 011228 12:18:51 /usr/local/libexec/mysqld: Shutdown Complete 011228 12:18:51 mysqld ended I read document that it is because of port already in use. but no application using the port. i tried with configuring different tcp port but got same error. This is not a TCP port; it's a socket. Please guide me to over come this problem. Try running $ ps -A | grep mysqld - and see if mysqld isn't already running. I don't think you have a problem: mysqld is already running and waiting for you to use it! / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqldump --tab crashes when dumping DB with many tables
Hi! Benjamin == Benjamin Pflugmann [EMAIL PROTECTED] writes: Benjamin Hi. Benjamin On Wed, Dec 19, 2001 at 05:03:25PM -0700, [EMAIL PROTECTED] wrote: Description: This problem pertains to the Sun Solaris distributions, I have not tried others. Description follows: mysqldump --tab runs up against user resource limits on the number of open file descriptors when the number of tables in the database being dumped is high enough. Have reproduced this problem with the latest stable release, binary package: mysql-max-3.23.46-sun-solaris2.8-sparc cut Benjamin Well, yes. There is a function to output the table structure and in Benjamin case of --tab it opens itself the file in question (instead of using Benjamin the file it gets passed), and the function is left without closing the Benjamin file. Benjamin A (hopefully) working patch: Benjamin --- mysql-3.23.46/client/mysqldump.cThu Nov 29 14:52:18 2001 Benjamin +++ mysql-h-3.23.46/client/mysqldump.c Fri Dec 28 06:30:54 2001 Benjamin @@ -864,6 +864,8 @@ Benjamin if (!extended_insert) Benjaminstrpos=strmov(strpos,(); Benjamin} Benjamin + if (path) Benjamin +my_fclose(sql_file, MYF(MY_WME)); BenjaminDBUG_RETURN(numFields); Benjamin } /* getTableStructure */ Benjamin Benjamin At least it compiles and doesn't break anything obvious for me. I Benjamin didn't create the test environment, though, so I cannot say, whether Benjamin it really fixes the bug completely, although it IMHO should. Thanks! Yes, it should fix the bug. I did apply it, with the following small change: if (sql_file != md_result_file) my_fclose(sql_file, MYF(MY_WME)); As the above is a little more safer if someone changes some of the code above. Regards, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MS Access to MySQL convert
Steve, Doug Gordan, Thanks for taking the time to help me. Billy - Original Message - From: Steve Rapaport [EMAIL PROTECTED] To: Billy Reed [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, December 28, 2001 2:17 AM Subject: Re: MS Access to MySQL convert Instead of TOP 5 DistKm FROM SCLeague you can probably use DistKm SORTED BY DistKm DESCENDING LIMIT 5 The rest about temp files is probably best to read Goran Bobic's mail. Billy Reed wrote: I am new to MySQL and am trying to convert : SELECT SUM(DistKm) AS Distance FROM (SELECT TOP 5 DistKm FROM XCLeague WHERE LoginID='billyreed' AND HGPG='PG' ORDER BY DistKm DESC) This is a subselect which selects the top 5 cross country flight distances of hang glider pilots (for a specific pilot) and then sums the result for each pilot. Subsequent code writes this to a new table showing who is in the lead !! Can anyone suggest how this is done in MySQL. It doesnt seem to like the TOP 5 syntax. I have been able to convert the rest of my site from MS Access to MySQL but am struggling with this. many thanks in anticipation. Billy Reed - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Steve Rapaport World Citizen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Error 'Can't connect to local MySQL server through socket (111)'
Hi, I just download MySQL and MySQLGui. I got MySQL running and loaded with some data, but MySQLGui refuses to work with the error I quoted in the subject. The versionnumbers are: MySQL: 3.23.41-1, MySQLGui: 1.7.5 Other programs, like mysql commandline find the socket no problem. What can I do to let MySQL find the correct socket? Thanks, Guus Bonnema. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Bind on unix socket: Permission denied
Hello Caryen, Thanks ps -A | grep mysqld shows no process running on that name. but i did not get any errot on log files. wiating for solution thanks -Sam - Original Message - From: Carsten H. Pedersen [EMAIL PROTECTED] To: sam [EMAIL PROTECTED]; [EMAIL PROTECTED] [EMAIL PROTECTED] Sent: Friday, December 28, 2001 3:51 PM Subject: RE: Bind on unix socket: Permission denied i run mysql_insyall_db and when i started safe_mysql If you were able to run mysql_instal_db, then mysqld was already running. # ./safe_mysqld --user=mysql [1] 19554 # Starting mysqld daemon with databases from /var/lib/mysql 011228 12:32:19 mysqld ended mysqld.log shows 011228 12:18:51 mysqld started 011228 12:18:51 Can't start server : Bind on unix socket: Permission denied 011228 12:18:51 Do you already have another mysqld server running on socket: /var/lib/mysql/mysql.sock ? You did. 011228 12:18:51 Aborting 011228 12:18:51 /usr/local/libexec/mysqld: Shutdown Complete 011228 12:18:51 mysqld ended I read document that it is because of port already in use. but no application using the port. i tried with configuring different tcp port but got same error. This is not a TCP port; it's a socket. Please guide me to over come this problem. Try running $ ps -A | grep mysqld - and see if mysqld isn't already running. I don't think you have a problem: mysqld is already running and waiting for you to use it! / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
When were user variables introduced ?
Hi I'm trying to use user variables such as @myvar within a query which I've got running perfectly on a 3.23.35a server , however, i can't get the same query to run on 3.22.32 server :( Does anyone know if 3.22.32 supports user variables ? Thanks Girish - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysql processes not dying
Is there any reason for mysqld processes not dying? Any try to kill it result in a 'defunct' processes (accoarding to ps aux). And what is a defunct process? The kernel prints the message: mm: critical shortage of bounce buffers. Any ideia what is it´s relation with Mysql? The machine has no special devices and has 1GB of ram. Thanks for any help. Pedro Furlanetto ocarteiro.com team - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: When were user variables introduced ?
Hi I'm trying to use user variables such as @myvar within a query which I've got running perfectly on a 3.23.35a server , however, i can't get the same query to run on 3.22.32 server :( Does anyone know if 3.22.32 supports user variables ? 3.23.6 was the first version to support UDVs. / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to make use of mysql++ with gcc 2.96
Hi! Sinisa == Sinisa Milivojevic [EMAIL PROTECTED] writes: Sinisa Trond Eivind Glomsrød writes: Yes, install gcc 2.95.2 or 3.0.3 That is very much _NOT_ recommended. Both have serious issues, none are compatible with the other libraries installed. As long as you install either of them in their own directory, like in /usr/gnu or /usr/local/gnu, the user should be fine. We know that MySQL works VERY good with gcc 2.95.2; Compiled with gcc 3.0.x MYSQL also appears to work good. Trond, with a compile it's very hard to generalize when it works or when it doesn't work. Just because it can't be used to compile a certain program (the Linux kernel ?), it doesn't mean that it isn't the best compiler for a lot of other programs... If you do know of any serious problems with gcc 3.0.x, please email me privately; I would really like to know about these! (Library conflicts is not a problem) Regards, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Error 'Can't connect to local MySQL server through socket (111)'
A.J. Bonnema writes: Hi, I just download MySQL and MySQLGui. I got MySQL running and loaded with some data, but MySQLGui refuses to work with the error I quoted in the subject. The versionnumbers are: MySQL: 3.23.41-1, MySQLGui: 1.7.5 Other programs, like mysql commandline find the socket no problem. What can I do to let MySQL find the correct socket? Thanks, Guus Bonnema. All you have to do is follow instructions from README. This is a relevant excerpt: When you start mysqlgui for the first time, you'll need to tell it how to connect to the MySQL server. To do this, click on the ``Options'' button. Select the ``Server'' tab and fill in the fields that indicate where to find the server. On Unix, if you have problems with mysqlgui not knowing the location of the socket file for connections to localhost, enter the full pathname of the socket file in the ``SQL command on the start-up'' field. On Windows, this field is used if you wish to specify the named pipe option. Then select the ``Client'' tab and fill in your MySQL user name. Take care to select the right value for the ``Ask for password'' button, depending on whether or not you want mysqlgui to prompt you for your MySQL password. After you have filled in the server and client values, click on the ``Save'' button. From then on, each time mysqlgui starts up, it will connect to the MySQL server automatically. If you have problems locating your socket file on Unix, you can use find utility, like this: find / -name `mysql\.sock' -print On Windows you can use Find utility from the menu to locate named pipe file. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Error 'Can't connect to local MySQL server through socket (111)'
Sinisa Milivojevic wrote: A.J. Bonnema writes: Hi, I just download MySQL and MySQLGui. I got MySQL running and loaded with some data, but MySQLGui refuses to work with the error I quoted in the subject. The versionnumbers are: MySQL: 3.23.41-1, MySQLGui: 1.7.5 Other programs, like mysql commandline find the socket no problem. What can I do to let MySQL find the correct socket? Thanks, Guus Bonnema. Hi Sinisa, This did it! Obviously I should have read the Readme. Thanks for the hint how to find the socket file. I had no idea such a file existed. Entering this file into the options dialog at SQL command on the start-up did the trick! Thanks. Guus Bonnema. All you have to do is follow instructions from README. This is a relevant excerpt: When you start mysqlgui for the first time, you'll need to tell it how to connect to the MySQL server. To do this, click on the ``Options'' button. Select the ``Server'' tab and fill in the fields that indicate where to find the server. On Unix, if you have problems with mysqlgui not knowing the location of the socket file for connections to localhost, enter the full pathname of the socket file in the ``SQL command on the start-up'' field. On Windows, this field is used if you wish to specify the named pipe option. Then select the ``Client'' tab and fill in your MySQL user name. Take care to select the right value for the ``Ask for password'' button, depending on whether or not you want mysqlgui to prompt you for your MySQL password. After you have filled in the server and client values, click on the ``Save'' button. From then on, each time mysqlgui starts up, it will connect to the MySQL server automatically. If you have problems locating your socket file on Unix, you can use find utility, like this: find / -name `mysql\.sock' -print On Windows you can use Find utility from the menu to locate named pipe file. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
maximum heap table size allowed
Hello, Can anyone tell me if the maximum table sizes given in Section 1.2.4 of the documentation apply equally to HEAP tables in v. 4.0? Or, rather, if memory and address space were unlimited, how large could a heap table possibly be in v. 4.0? TIA Tim Banach State Street Corporation Westwood, MA - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: maximum heap table size allowed
Banach, Timothy P writes: Hello, Can anyone tell me if the maximum table sizes given in Section 1.2.4 of the documentation apply equally to HEAP tables in v. 4.0? Or, rather, if memory and address space were unlimited, how large could a heap table possibly be in v. 4.0? TIA Tim Banach State Street Corporation Westwood, MA Hi! It is the same. Just use MAX_ROWS -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Query Problem - need some help here or is it just impossible the way i want it to work?
Hi there,
I've been trying around for some time now and i just don't see a solution to
my problem:
I have a table called feiertage (Holidays):;
mysql select * from feiertage;explain feiertage;
++
| datum |
++
| 2001-12-24 |
| 2001-12-25 |
| 2001-12-26 |
| 2002-01-01 |
++
4 rows in set (0.81 sec)
+---+--+--+-++---+
| Field | Type | Null | Key | Default| Extra |
+---+--+--+-++---+
| datum | date | | PRI | -00-00 | |
+---+--+--+-++---+
1 row in set (0.81 sec)
now i want to know the last date, that is not listed in that table and that
is not on a weekend and is earlier or the same than a given date:
assume 2001-12-29 is the given date, then i tried around with a loop in perl
increasing the INTERVAL.
select DATE_SUB('2001-12-29', INTERVAL 0 DAY) as tag from feiertage where
datum = DATE_SUB('2001-12-29', INTERVAL 0 DAY) or
WEEKDAY(DATE_SUB('2001-12-29', INTERVAL 0 DAY))4;
tells me that this date is on a weekend or a holiday. that doesn't rellay
help me, what i need is a query that gives me 2001-12-28 (which is the
friday). i played around for 2 hours now but have no idea how to make it work with
just one query. I could think of ways to do it with perl and loops and stuff
like that, increasing the INTERVAL until i get zero rows which would mean i
stepped backwards enough days, but then i would need another query to
actually get the date in the right form 2001-12-28 which i need. But i have still
hope there is a way without the overhead of loops and stuff...
has anybody some suggestion?
thanks a lot for any hints
Richard
--
GMX - Die Kommunikationsplattform im Internet.
http://www.gmx.net
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: FW: Replication reset
Here is how you can do the above: * make sure slave runs with log-bin. * before you direct updates to the slave, record the binlog offset and position on the slave by running SHOW MASTER STATUS ( on the slave not on the master!) Whoops! When the failover occurs, there is nobody at the console to record the offset! It's an automatic failover when the master fails! It might have happened at 4 in the morning yesterday. How do we automatically know the master has failed and record that offset? You need to have your system set up in such a way that failover event will trigger the execution of a script, which will do all the magic. -- MySQL Development Team For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sasha Pachev [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA ___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Increasing max data file length?
Hi, I have a table that has now reached 4gb but I thought that shouldn't be a problem since the table is running with raid. I noticed that the Datafile length has reached the Max Datafile length and I tried to increase it with myisamchk -r --data-file-length=8589934588 with no success. Do I need to clean the table up bigtime or can I increase this somehow? Can anyone please help me? :) Best regards, Gudmundur uname -a: Linux cartman 2.4.9-6smp #1 SMP Thu Oct 18 09:22:57 EDT 2001 i686 unknown myisamchk -dv: MyISAM file: data Record format: Packed Character set: latin1 (8) File-version:1 Creation time: 2001-06-28 13:29:11 Recover time:2001-12-28 15:18:12 Status: changed Auto increment key: 1 Last value:450036 RAID:Type: 1 Chunks: 6 Chunksize: 65536 Data records: 295294 Deleted blocks: 0 Datafile parts: 295294 Deleted data: 0 Datafile pointer (bytes):4 Keyfile pointer (bytes):4 Datafile length:4294912120 Keyfile length: 8341504 Max datafile length:4294967294 Max keyfile length: 4398046510079 Recordlength: 131 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
table full on innodb-tables?
hi could anybody give me a hint what a table is full-error on an InnoDB-Table means? I had converted some MyISAM-Tables (about 300.000 rows each, 10-23 columns of different integer-types, 3-6 indexes) on a test-system because i hoped for better performance since i have lots of updates and reads (200-300 queries/second) and InnoDB supports row level locking... But after everything having worked fine for 2 days i suddenly got table full errors on every simple INSERT or UPDATE on these Tables. There was no diskspace- or RAM-Problem. I had to switch the tables back to MyISAM to be able to continue the development of my webapplication, but i might be able to switch it back to InnoDB for more testing on this weird thing after my application is online on some other production-system. I just wondered if this is a know problem with innodb under heavy load with large tables and if there's anything i should look for. If this is a unknown problem, i might be able to report some useful debug-data in 1 or 2 weeks. Thanks Richard -- GMX - Die Kommunikationsplattform im Internet. http://www.gmx.net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
invalid DATETIME=UNIX_TIMESTAMP()
I mistakenly set the value of a DATETIME column to the value of UNIX_TIMESTAMP(). I know that this is not good practice and I would have even expected it to fail. But what has happened was totally unexpected for me, invalid date values like '2000-10-09 55:17:71' were inserted. As I could not set the column to the same value manually I think this is an error and should be fixed. Regards Tom PS: Absolutely unrelated words needed to circumvent spam filter: database,sql,query,table Press any key to continue or any other key to quit. -- T h o m a s Z e h e t b a u e r ( TZ251 ) PGP encrypted mail preferred - KeyID 96FFCB89 mail [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Another join question and float question
Thanks to all who have helped me with my mySQL questions. I think I'm getting the hang of it now. I do have another question. How can I join more than two tables? I have two mySQL books and neither give examples, they just say it can be done. I have floating numbers in my tables. I want to display them to four decimal places (e.g. 460.1 should show as 460.1000). Under Linux I was able to use the ROUND function to accomplish this. This doesn't work under mysqlfront. Is there a better way to display floating numbers to a certain precision? Thanks, John -- John Mayson KC4VJO Linux/Perl/HP-UX/ICT Austin, Texas 78729 30.4560N 97.7851W [EMAIL PROTECTED] http://www.nyx.net/~jmayson - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL 3.23.47 is released
Hi! MySQL 3.23.47 is now released. This is a minor bug fix release. If you have not hit any of the listed problems, you only have to consider upgrading if you are using replication and the RELEASE_LOCK() command. In this case you should either upgrade just the slaves or the master and the slaves. Changes in release 3.23.47 -- * Fixed in when using the following construct: `SELECT ... WHERE key=@var_name OR $key=@var_name' * Restrict InnoDB keys to 500 bytes. * InnoDB now supports `NULL' in keys. * Fixed shutdown problem on HPUX. (Introduced in 3.23.46) * Fixed core-dump bug in replication when using SELECT RELEASE_LOCK(); * Added new statement DO expression,[expression]. * Added `slave-skip-errors' option * Added statistics variables for all MySQL commands. (`SHOW STATUS' is now much longer). * Fixed default values for InnoDB tables. * Fixed that `GROUP BY expr DESC' works. * Fixed bug when using `t1 LEFT JOIN t2 ON t2.key=constant'. * `mysql_config' now also work with binary (relocated) distributions. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: To Mr Bob Hall (and everybody else who wants to read it)
. So, as there's no way of making MySQL read data in MS Access Object, is there a way to avoid stupid PopUp forms asking for confirm to an Action Query? . From a Macro: SetWarnings False From VBA: DoCmd.SetWarnings False - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: FW: Replication reset
How do we automatically know the master has failed and record that offset? You need to have your system set up in such a way that failover event will trigger the execution of a script, which will do all the magic. Good idea! I didn't think it was possible to put arbitrary mysql commands like SHOW MASTER STATUS into a script. I had assumed I was limited to the shell-level utilities like mysqlimport and mysqldump. How exactly do I put the interactive commands into a script? I'm on Linux and I can use PHP, Perl, shell, or whatever. Thanks! Steve -- Steve Rapaport World Citizen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL 4.01 slave of MySQL 3.23 crashes on startup
Description: This is on a 4.01 server. That is a slave of a 3.23.44 server. Below is the log file from the crash. 011228 09:18:09 mysqld restarted 011228 9:18:09 InnoDB: Started /usr/sbin/mysqld: ready for connections 011228 9:18:10 Slave: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'spode-bin.001' at position 251797980 mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=8388600 record_buffer=131072 sort_buffer=2097144 max_used_connections=0 max_connections=100 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 225791 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Stack range sanity check OK, backtrace follows: 0x807db7f 0x823d60a 0x8204407 0x821bb96 0x820bb01 0x80d07ef 0x80b0439 0x8086da7 0x808a222 0x80b72ba 0x80edcb4 0x80ee72d Stack trace seems successful - bottom reached Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x841a955 = # /home/services/tias/cgi-bin/kovelsYP.fcgi (adams.tias.com 12002) UPDATE tums.user SETname='[EMAIL PROTECTED]', email='[EMAIL PROTECTED]', homedir=NULL, realname='Bonnie Barker', address='PO Box 243', city='Topock', state='AZ', zip='86436', country='US' WHERE userid='312362' thd-thread_id=1 Successfully dumped variables, if you ran with --log, take a look at the details of what thread 1 did to cause the crash. In some cases of really bad corruption, the values shown above may be invalid. The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0 011228 09:18:10 mysqld restarted 0x807db7f handle_segfault__Fi + 383 0x823d60a pthread_sighandler + 154 0x8204407 _mi_compare_text + 71 0x821bb96 _mi_ft_cmp + 158 0x820bb01 mi_update + 721 0x80d07ef update_row__9ha_myisamPCcPc + 67 0x80b0439 mysql_update__FP3THDP13st_table_listRt4List1Z4ItemT2P4ItemP8st_orderUl15enum_duplicates13thr_lock_type + 2473 0x8086da7 mysql_execute_command__Fv + 5723 0x808a222 mysql_parse__FP3THDPcUi + 270 0x80b72ba exec_event__15Query_log_eventP14st_master_info + 402 0x80edcb4 exec_event__FP3THDP6st_netP14st_master_infoi + 604 0x80ee72d handle_slave__FPv + 2625 How-To-Repeat: I can supply the database tables and log files if necessary but they are about 5GB. Fix: Don't know. Submitter-Id: submitter ID Originator:Mike Wexler Organization: MySQL support: licence Synopsis: MySQL 4.01 is crashing replicating an update Severity: critical Priority: high Category: mysql Class: sw-bug Release: mysql-4.0.1-alpha (Official MySQL RPM) Environment: System: Linux noritake.tias.com 2.4.2-2smp #1 SMP Sun Apr 8 20:21:34 EDT 2001 i686 unknown Architecture: i686 Some paths: /usr/bin/mh/perl /usr/bin/mh/make /usr/bin/mh/gmake /usr/bin/mh/gcc /usr/bin/mh/cc GCC: Reading specs from /usr/bin/mh/../lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-85) Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='gcc' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 May 12 2001 /lib/libc.so.6 - libc-2.2.2.so -rwxr-xr-x1 root root 1236396 Apr 6 2001 /lib/libc-2.2.2.so -rw-r--r--1 root root 26350254 Apr 6 2001 /usr/lib/libc.a -rw-r--r--1 root root 178 Apr 6 2001 /usr/lib/libc.so Configure command: ./configure --disable-shared --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --without-berkeley-db --with-innodb --enable-assembler --with-mysqld-user=mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/ --with-extra-charsets=complex --exec-prefix=/usr --libexecdir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --localstatedir=/var/lib/mysql --infodir=/usr/info --includedir=/usr/include --mandir=/usr/man
Re: FW: Replication reset
On Friday 28 December 2001 10:18 am, Steve Rapaport wrote: I didn't think it was possible to put arbitrary mysql commands like SHOW MASTER STATUS into a script. ?I had assumed I was limited to the shell-level utilities like mysqlimport and mysqldump. How exactly do I put the interactive commands into a script? they work like a regular SELECT as far as the client interface is concerned. -- MySQL Development Team For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sasha Pachev [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA ___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SSL connection question.
I'm Sorry if this question has been posted often. but i cannot find answer. I have installed openssl and MySQL 4.0.1 with --with-openssl --with-vio And... mysql SHOW VARIABLES LIKE '%ssl%' ; +---+---+ | Variable_name | Value | +---+---+ | have_openssl | YES | +---+---+ 1 row in set (0.10 sec) and I granted somebody with REQUIRE SSL. But He cannot connect to MySQL using mysql . mysql client does not support SSL connection? How can he/she connect to MySQL with SSL? about PHP and MySQL? I'm very pleased if give me any Information ! Thank you for advanced Answer! Homepage = http://www.nnr.or.kr/inos/ ICQ # = 123534385 Member of DSN(database.sarang.net), NNR(nnr.or.kr) --MIME Multi-part separator-- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
InnoDB in MySQL-3.23.47
Hi! InnoDB is a table type which provides transactions, row level locking, and foreign key constraints to MySQL. InnoDB is included in all versions of MySQL-4.0 and in MySQL-Max-3.23. InnoDB in MySQL-Max-3.23.47 is identical to MySQL-4.0.1 Changes from 3.23.46: * InnoDB allows now several similar key values in a UNIQUE secondary index if those values contain SQL NULLs. Thus the convention is now the same as in MyISAM tables. * InnoDB gives a better row count estimate for a table which contains BLOBs. * In a FOREIGN KEY constraint InnoDB is now case-insensitive to column names, and in Windows also to table names. * InnoDB allows a FOREIGN KEY column of CHAR type to refer to a column of VARCHAR type, and vice versa. MySQL silently changes the type of some columns between CHAR and VARCHAR, and these silent changes do not hinder FOREIGN KEY declaration any more. * Recovery has been made more resilient to corruption of log files. * Unnecessary statistics calculation has been removed from queries which generate a temporary table. Some ORDER BY and DISTINCT queries will now run much faster. * MySQL now knows that the table scan of an InnoDB table is done through the primary key. This will save a sort in some ORDER BY queries. * The maximum key length of InnoDB tables is again restricted to 500 bytes. The MySQL interpreter is not able to handle longer keys. Best regards and a Happy New Year! Heikki Tuuri Innobase Oy [EMAIL PROTECTED] http://www.innodb.com --- Order commercial MySQL/InnoDB support at https://order.mysql.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Forcing Table Types
This is getting really annoying. No matter what type of table I create, it ends up being MyISAM. I _must_ have at least transactions and foreign key support would be nice. Also, does anyone know how I can have transactions on create table statements? Here the interaction with MySQL that is driving me crazy. It's kind of messy, but you'll notice my foo table ends up being MyISAM. The same thing happens for Berkley tables. mysql create table foo ( - foo_id int auto_increment not null, - whatever text, - primary key(foo_id) - ) type=InnoDB; Query OK, 0 rows affected (0.00 sec) mysql show create table foo; +---+-+ | Table | Create Table | +---+-+ | foo | CREATE TABLE `foo` ( `foo_id` int(11) NOT NULL auto_increment, `whatever` text, PRIMARY KEY (`foo_id`) ) TYPE=MyISAM | - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Forcing Table Types
Ken, When you configured/compiled MySQL, did you use the --with-innodb option? What version of MySQL are you running? FWIW, I wasn't real impressed with the Berkeley tables, but the InnoDB tables work very well. --Walt Weaver Bozeman, Montana -Original Message- From: Ken Kinder [mailto:[EMAIL PROTECTED]] Sent: Friday, December 28, 2001 1:09 PM To: [EMAIL PROTECTED] Subject: Forcing Table Types This is getting really annoying. No matter what type of table I create, it ends up being MyISAM. I _must_ have at least transactions and foreign key support would be nice. Also, does anyone know how I can have transactions on create table statements? Here the interaction with MySQL that is driving me crazy. It's kind of messy, but you'll notice my foo table ends up being MyISAM. The same thing happens for Berkley tables. mysql create table foo ( - foo_id int auto_increment not null, - whatever text, - primary key(foo_id) - ) type=InnoDB; Query OK, 0 rows affected (0.00 sec) mysql show create table foo; +---+--- --+ | Table | Create Table | +---+--- --+ | foo | CREATE TABLE `foo` ( `foo_id` int(11) NOT NULL auto_increment, `whatever` text, PRIMARY KEY (`foo_id`) ) TYPE=MyISAM | - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Forcing Table Types
I am using the RPM's, but it didn't give an error so I'm assuming it is properly configured. The MySQL version is: [ken@ken ken]$ mysql --version mysql Ver 11.15 Distrib 3.23.44, for pc-linux-gnu (i686) Sorry -- I should have included that. I'm assuming there's something wrong with my syntax as I can't create Berkley tables either. Frankly I don't care what table type I use, I just want transactions -- and if I can get them, foreign keys w/ cascade deletes, etc... On Friday 28 December 2001 01:25 pm, Weaver, Walt wrote: Ken, When you configured/compiled MySQL, did you use the --with-innodb option? What version of MySQL are you running? FWIW, I wasn't real impressed with the Berkeley tables, but the InnoDB tables work very well. --Walt Weaver Bozeman, Montana -Original Message- From: Ken Kinder [mailto:[EMAIL PROTECTED]] Sent: Friday, December 28, 2001 1:09 PM To: [EMAIL PROTECTED] Subject: Forcing Table Types This is getting really annoying. No matter what type of table I create, it ends up being MyISAM. I _must_ have at least transactions and foreign key support would be nice. Also, does anyone know how I can have transactions on create table statements? Here the interaction with MySQL that is driving me crazy. It's kind of messy, but you'll notice my foo table ends up being MyISAM. The same thing happens for Berkley tables. mysql create table foo ( - foo_id int auto_increment not null, - whatever text, - primary key(foo_id) - ) type=InnoDB; Query OK, 0 rows affected (0.00 sec) mysql show create table foo; +---+-- - --+ | Table | Create Table +---+-- - --+ | foo | CREATE TABLE `foo` ( `foo_id` int(11) NOT NULL auto_increment, `whatever` text, PRIMARY KEY (`foo_id`) ) TYPE=MyISAM | - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MSVC++ and MYSQL++ query problem
with this code: Query myQuery = pDatabase-query(); myQuery Select * from repdata; myQuery.store(); I get an Debug Assertion Failed in file dbgheap.c line 1017 Expression: _BLOCK_TYPE_IS_VALIDE(pHead-nBlockUse) pDatabase is a pointer to a connection object. I am connected to the database and there is no problem with the query, I have run it on MySQL administrator with out any errors Please help me understand what is causing this error. Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Forcing Table Types
Hi! On Dec 28, Ken Kinder wrote: This is getting really annoying. No matter what type of table I create, it ends up being MyISAM. I _must_ have at least transactions and foreign key support would be nice. Also, does anyone know how I can have transactions on create table statements? Try SHOW VARIABLES LIKE 'have_%'; It'll resolve your question. (you'll see have_innodb=NO, have_bdb=NO) Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Forcing Table Types
Run the following query: SHOW VARIABLES LIKE 'have_%'; You should see something like this: +---+---+ | Variable_name | Value | +---+---+ | have_bdb | NO| | have_gemini | NO| | have_innodb | YES | | have_isam | YES | | have_raid | NO| | have_openssl | NO| +---+---+ 6 rows in set (0.00 sec) It will tell you if BDB and/or InnoDB is active. It might say disabled, if so you will need to enable in my.cnf -Original Message- From: Ken Kinder [mailto:[EMAIL PROTECTED]] Sent: Friday, December 28, 2001 12:26 PM To: Weaver, Walt; [EMAIL PROTECTED] Subject: Re: Forcing Table Types I am using the RPM's, but it didn't give an error so I'm assuming it is properly configured. The MySQL version is: [ken@ken ken]$ mysql --version mysql Ver 11.15 Distrib 3.23.44, for pc-linux-gnu (i686) Sorry -- I should have included that. I'm assuming there's something wrong with my syntax as I can't create Berkley tables either. Frankly I don't care what table type I use, I just want transactions -- and if I can get them, foreign keys w/ cascade deletes, etc... On Friday 28 December 2001 01:25 pm, Weaver, Walt wrote: Ken, When you configured/compiled MySQL, did you use the --with-innodb option? What version of MySQL are you running? FWIW, I wasn't real impressed with the Berkeley tables, but the InnoDB tables work very well. --Walt Weaver Bozeman, Montana -Original Message- From: Ken Kinder [mailto:[EMAIL PROTECTED]] Sent: Friday, December 28, 2001 1:09 PM To: [EMAIL PROTECTED] Subject: Forcing Table Types This is getting really annoying. No matter what type of table I create, it ends up being MyISAM. I _must_ have at least transactions and foreign key support would be nice. Also, does anyone know how I can have transactions on create table statements? Here the interaction with MySQL that is driving me crazy. It's kind of messy, but you'll notice my foo table ends up being MyISAM. The same thing happens for Berkley tables. mysql create table foo ( - foo_id int auto_increment not null, - whatever text, - primary key(foo_id) - ) type=InnoDB; Query OK, 0 rows affected (0.00 sec) mysql show create table foo; +---+--- --- - --+ | Table | Create Table +---+--- --- - --+ | foo | CREATE TABLE `foo` ( `foo_id` int(11) NOT NULL auto_increment, `whatever` text, PRIMARY KEY (`foo_id`) ) TYPE=MyISAM | - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Forcing Table Types
You can see if you're allowed to create Berkeley and InnoDB by using the show variables command. Just I guess, but the rpm's probably aren't configured for Berkeley and InnoDB tables. You're probably going to have to download the tarball and configure it yourself. If you'd like I can send you the configure script I used to set up MySQL for Berkeley and InnoDB. --Walt -Original Message- From: Ken Kinder [mailto:[EMAIL PROTECTED]] Sent: Friday, December 28, 2001 1:26 PM To: Weaver, Walt; [EMAIL PROTECTED] Subject: Re: Forcing Table Types I am using the RPM's, but it didn't give an error so I'm assuming it is properly configured. The MySQL version is: [ken@ken ken]$ mysql --version mysql Ver 11.15 Distrib 3.23.44, for pc-linux-gnu (i686) Sorry -- I should have included that. I'm assuming there's something wrong with my syntax as I can't create Berkley tables either. Frankly I don't care what table type I use, I just want transactions -- and if I can get them, foreign keys w/ cascade deletes, etc... On Friday 28 December 2001 01:25 pm, Weaver, Walt wrote: Ken, When you configured/compiled MySQL, did you use the --with-innodb option? What version of MySQL are you running? FWIW, I wasn't real impressed with the Berkeley tables, but the InnoDB tables work very well. --Walt Weaver Bozeman, Montana -Original Message- From: Ken Kinder [mailto:[EMAIL PROTECTED]] Sent: Friday, December 28, 2001 1:09 PM To: [EMAIL PROTECTED] Subject: Forcing Table Types This is getting really annoying. No matter what type of table I create, it ends up being MyISAM. I _must_ have at least transactions and foreign key support would be nice. Also, does anyone know how I can have transactions on create table statements? Here the interaction with MySQL that is driving me crazy. It's kind of messy, but you'll notice my foo table ends up being MyISAM. The same thing happens for Berkley tables. mysql create table foo ( - foo_id int auto_increment not null, - whatever text, - primary key(foo_id) - ) type=InnoDB; Query OK, 0 rows affected (0.00 sec) mysql show create table foo; +---+-- - --+ | Table | Create Table +---+-- - --+ | foo | CREATE TABLE `foo` ( `foo_id` int(11) NOT NULL auto_increment, `whatever` text, PRIMARY KEY (`foo_id`) ) TYPE=MyISAM | - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Programming for referential integrity
Hello, Forgive me for what is probably a fairly novice question as I am still fairly new to MySQL. I have constructed the following database with three tables: CREATE TABLE members ( member_id int DEFAULT '0' NOT NULL AUTO_INCREMENT PRIMARY KEY, first varchar(20) NOT NULL, last varchar(20) NOT NULL, position varchar(20), organization varchar(35), address varchar(255), postal_code varchar(7), county varchar(15) NOT NULL, phone varchar(20), fax varchar(20), email varchar(40), website varchar(50), member_type varchar(20) NOT NULL, on_directory char(1) NOT NULL DEFAULT 'N', on_website char(1) NOT NULL DEFAULT 'N', description BLOB ); CREATE TABLE skills ( skills_id int default '0' not null auto_increment primary key, member_id int not null references members(member_id), name varchar(20) NOT NULL, schooling varchar(20) NOT NULL, certifications varchar (20) NOT NULL, decription BLOB ); CREATE TABLE committees ( committees_id int DEFAULT '0' NOT NULL AUTO_INCREMENT PRIMARY KEY, member_id int not null references members, name varchar(25) ); I would like the member_id field present in all of the tables to link the skills and commitees to a specific member_id. I was looking at the last_insert_id() function as a possible way to do this as initially all of this information will be entered at once. What is the best way to find the member_id that was just created in the members table and insert it into the skills table and the committees table when adding the information about those members? I am using PHP and MySQL 3.23.38 running on FreeBSD / Apache(PhP4). Thanks. Matt Rudderham Bonum volens duceris in Tartarum - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL starts using a lot of CPU resources
Hi all, For a few month I was using mysql-3.23.39 without any problems but today the load went up to 25 and higher. I've tried some finetuning (like shorten timeouts to 5 minutes) and upgraded to mysql-3.23.46 but in vain. Still after a few minutes the load is back to 25 and this is the output of 'mysqladmin status': Uptime: 778 Threads: 85 Questions: 14882 Slow queries: 209 Opens: 2838 Flush tables: 1 Open tables: 416 Queries per second avg: 19.129 Does someone has any clue? The developer of the website told me that he didn't start to use strange queries... Thanks in advance! Best regards, Jurrien Here is my my.cnf: [mysqld] port= 3306 socket = /var/run/mysqld/mysql.sock skip-locking set-variable= key_buffer=256M set-variable= max_allowed_packet=1M set-variable= table_cache=256 set-variable= sort_buffer=1M #set-variable = record_buffer=1M set-variable= net_buffer_length=8K set-variable= myisam_sort_buffer_size=64M #set-variable = thread_cache=8 #set-variable = thread_concurrency=2 #set-variable = max_connections=500 set-variable= interactive_timeout=300 set-variable= wait_timeout=300 #log-bin server-id = 1 And this is how I compiled mysqld: ./configure --prefix=/usr/local --localstatedir=/var/lib/mysql --with-unix-socket-path=/var/run/mysqld/mysql.sock --enable- assembler --with-mysqld-ldflags=-all-static --without-bench Some general details of the host: Debian potato stable release, Linux 2.4.9, 512 MB, SCSI harddrive, database is 50mb, Pentium3/1.0 Ghz, gcc version 2.95.2 2220 (Debian GNU/Linux) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Programming for referential integrity
Matt, Being that I am a PHP developer, the answer is right there in front of you. Since you are getting the member_id after you insert the first bunch of information, the best way you can do this in my experience is to create a search group and reference the information that you just got, so you can get your member_id. From there you can now store that member_id into a variable and use it at will. If there is a better way of doing this, I would LOVE to know it. -- Mike Eggleston -- [EMAIL PROTECTED] - Original Message - From: Matt Rudderham [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, December 28, 2001 2:05 PM Subject: Programming for referential integrity Hello, Forgive me for what is probably a fairly novice question as I am still fairly new to MySQL. I have constructed the following database with three tables: CREATE TABLE members ( member_id int DEFAULT '0' NOT NULL AUTO_INCREMENT PRIMARY KEY, first varchar(20) NOT NULL, last varchar(20) NOT NULL, position varchar(20), organization varchar(35), address varchar(255), postal_code varchar(7), county varchar(15) NOT NULL, phone varchar(20), fax varchar(20), email varchar(40), website varchar(50), member_type varchar(20) NOT NULL, on_directory char(1) NOT NULL DEFAULT 'N', on_website char(1) NOT NULL DEFAULT 'N', description BLOB ); CREATE TABLE skills ( skills_id int default '0' not null auto_increment primary key, member_id int not null references members(member_id), name varchar(20) NOT NULL, schooling varchar(20) NOT NULL, certifications varchar (20) NOT NULL, decription BLOB ); CREATE TABLE committees ( committees_id int DEFAULT '0' NOT NULL AUTO_INCREMENT PRIMARY KEY, member_id int not null references members, name varchar(25) ); I would like the member_id field present in all of the tables to link the skills and commitees to a specific member_id. I was looking at the last_insert_id() function as a possible way to do this as initially all of this information will be entered at once. What is the best way to find the member_id that was just created in the members table and insert it into the skills table and the committees table when adding the information about those members? I am using PHP and MySQL 3.23.38 running on FreeBSD / Apache(PhP4). Thanks. Matt Rudderham Bonum volens duceris in Tartarum - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to optimize this
How to optimize this query: SELECT col1, col2, MAX(col3) FROM Table WHERE col42 AND col5 = 2 GROUP BY col3 LIMIT 1; The best that i figured out is to set the index on col3. I was unable to find out the working combination for multi-column indexing. Please advice. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
example scripts
forgive the newbieness of this question, but is there some example databases to play with on mysql?...sort of like the northwind traders example database for m$ access...i have only been playing with mysql for a couple of days, and so far impressed overall, but the easiest way for me to learn things, on a computer especially is to toy with something completed to see what makes it tick, taking the clock apart and inspecting the gears so to speak if anyone knows of a place to get ahold of such databases, i'd appreciate the tip thanks -bobby - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
DBD::mysql HElp
Hi, I'm trying to install DBD::MYSQL on Windows 2000 Pro. I've already install DBI Using ppm for ActivePerl I downloaded DBI and extracted it to a folder. Then i ran ppm intall DBI It installed fine. I did the exact same thing for DBD::MYSQL but i get the following error Error: Package 'DBD::mysql' not found. Please 'search' for ot first. Do you know why it is not finding it? Please help! Thank you Clinton - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to optimize this
In the last episode (Dec 28), Goran Krajacic said: How to optimize this query: SELECT col1, col2, MAX(col3) FROM Table WHERE col42 AND col5 = 2 GROUP BY col3 LIMIT 1; Try a compound index on (col5,col4), or if you really want speed, (col5,col4,col3,col2,col1), which will let mysql use the index for the SELECT fields as well :) Your query can also be written SELECT col1, col2, col3 FROM Table WHERE col42 AND col5 = 2 ORDER BY col3 DESC LIMIT 1; , which might be faster. -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 4.0.1 bug reports. Has it been released yet?
Hi. On Fri, Dec 28, 2001 at 01:14:00AM -0600, [EMAIL PROTECTED] wrote: In the last episode (Dec 28), Benjamin Pflugmann said: On Thu, Dec 20, 2001 at 09:19:00AM +, [EMAIL PROTECTED] wrote: I've seen various bug reports for mysql 4.0.1 but no sign of it on the site. Has it been released yet? I think the 'IN BOOLEAN MODE' modifier for a fulltext search may solve all my problems. 4.0.1 is not released yet. Any bug reports for 4.0.1 are probably from people who are living on the edge and install from the development source tree (http://www.mysql.com/doc/I/n/Installing_source_tree.html). You probably missed Monty's announcement on Monday, then: Obviously :-) I did not catch up with the mails lately. But announcements go to my main folder, so I wonder how I missed that, too. The really funny part is that I had a quick look at the web page and checked which version is current and must have dreamed while doing so. ;-) Bye, Benjamin. Date: Mon, 24 Dec 2001 08:37:43 +0200 (EET) From: Michael Widenius [EMAIL PROTECTED] Subject: MySQL 4.0.1 released I haven't tried boolean searches yet, but the rest seems to work :) -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Error compiling
The Compaq CC compiler (V6.4) has slightly different prototype for strtoll than does the version with mysql. The difference is not that big and would not affect to execution but the compiler thinks this as a bad thing and gives You an error. I've included the global.h with a fix to this problem. /Esko -- x---x x Esko Ilola mailto:[EMAIL PROTECTED] x x phone: +358 2040 60681 x x +358 9 2212211x x mobile:+358 40 5575 977 x x---x global.zip Description: Zip archive - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL starts using a lot of CPU resources
Hi all, For a few month I was using mysql-3.23.39 without any problems but today the load went up to 25 and higher. I've tried some finetuning (like shorten timeouts to 5 minutes) and upgraded to mysql-3.23.46 but in vain. Still after a few minutes the load is back to 25 and this is the output of 'mysqladmin status': Uptime: 778 Threads: 85 Questions: 14882 Slow queries: 209 Opens: 2838 Flush tables: 1 Open tables: 416 Queries per second avg: 19.129 209 slow queries in 778 seconds is Not Good(tm). It doesn't take many slow queries to have everything backing up waiting for tables to be released. Try enabling --log-slow-queries, then have a peek at the log file to see what's causing these. / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL starts using a lot of CPU resources
related question: Does MySQL label as slow (as I believe PostgreSQL does) any query that is not referencing an indexed table? -Original Message- From: Carsten H. Pedersen [mailto:[EMAIL PROTECTED]] Sent: Friday, December 28, 2001 2:42 PM To: Jurrien Wijlhuizen; [EMAIL PROTECTED] Subject: RE: MySQL starts using a lot of CPU resources Hi all, For a few month I was using mysql-3.23.39 without any problems but today the load went up to 25 and higher. I've tried some finetuning (like shorten timeouts to 5 minutes) and upgraded to mysql-3.23.46 but in vain. Still after a few minutes the load is back to 25 and this is the output of 'mysqladmin status': Uptime: 778 Threads: 85 Questions: 14882 Slow queries: 209 Opens: 2838 Flush tables: 1 Open tables: 416 Queries per second avg: 19.129 209 slow queries in 778 seconds is Not Good(tm). It doesn't take many slow queries to have everything backing up waiting for tables to be released. Try enabling --log-slow-queries, then have a peek at the log file to see what's causing these. / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL starts using a lot of CPU resources
related question: Does MySQL label as slow (as I believe PostgreSQL does) any query that is not referencing an indexed table? No. Any query that takes longer than long_query_time, e.g. 10 seconds on most systems, to execute. Use SHOW VARIABLES to see it. / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Newbie trying to optimizing a join search
I'm new to mysql and have gotten most things to work satisfactorily thus far. One of the things that is stumping me at the moment is how to perform queries using joins and how to optimize them for speed. the query is structured as follows: SELECT products.return FROM products,prodcat WHERE products.prodno=prodcat.prodno AND products.Store=0001 AND products.Class=0002 AND prodcat.category=Animal AND prodcat.subcategory=Bear ORDER BY products.Title The above query takes approximately 3 seconds to complete (which is better than the 27 seconds without indexes, i admit but still a little slow). Is there a way to optimize this kind of search? Setup of the tables used in this particular area are below: Setup: products table with the following fields: -ID (primary key) -prodno -store -class -title -designer -sugretail -discount price -return prodcat (contains categories associated with each product) with the following fields: -ID (primary key) -prodno -category -subcategory All fields except for ID fields and sugretail and discount price fields are varchar fields. Category and subcategory fields are longest at 50 chars each. In products, the ID field has it's own index, product number, store, class, designer, and title are inside an index called index_product. In prodcat, product number, category, and subcategory are part of an index called index_category. Thanks in advance for your attention and time. Charley - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MS Access and ODBC - have I got this right?
seem to be half remembering hearing somewhere that when Access opens a form (from any database, be it a jet database or MySQL linked via ODBC) it pulls down all the records that the form may want to look at as opposed to the one record you are looking at. Is this correct? If so can some one point me in the right direction of what code to insert and where to ensure that the only data accessed is that for the current record. Thanks Alex --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.310 / Virus Database: 171 - Release Date: 19/12/2001 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Query Speed problem...
I just added a fulltext index to a table, and MATCH queries on the table
are timing out. Can anyone offer any insight on this?
The table has 2000 rows of data in it, and phpMyAdmin reports it as
having a total size of 244,228 bytes.
Table Structure:
CREATE TABLE Customers (
ID mediumint(9) NOT NULL auto_increment,
FirstName varchar(255) NOT NULL default '',
LastName varchar(255) NOT NULL default '',
Street1 varchar(255) NOT NULL default '',
Street2 varchar(255) NOT NULL default '',
City varchar(255) NOT NULL default '',
State varchar(50) NOT NULL default 'Utah',
Zipcode varchar(15) NOT NULL default '',
Phone1 varchar(50) NOT NULL default '',
Phone2 varchar(50) NOT NULL default '',
Fax varchar(50) NOT NULL default '',
Email varchar(255) NOT NULL default '',
Type varchar(255) NOT NULL default 'Customer',
Memo varchar(255) NOT NULL default '',
PRIMARY KEY (ID),
FULLTEXT KEY Name (FirstName,LastName)
) TYPE=MyISAM;
Query:
SELECT * FROM Customers WHERE MATCH (FirstName,LastName) AGAINST
('walker')
Matthew Walker
Ecommerce Project Manager
Mountain Top Herbs
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.310 / Virus Database: 171 - Release Date: 12/19/2001
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ODBC DRIVERS for MYSQL
A newbie question: DESIRED FUNCTION: to import data from MySQL db to MS word through webquery ODBC I was guided to install myODBC by going to http://www.mhysql.com --- downloads -API's --MyODBC myODBC 2.5.39 DOWNLOADED, UNIZPPED AND INSTALLED I was told to add a NEW SYSTEM DSN for MySQL PROBLEM there is NO SYSTEM DSN offered to add under the add button under the SYSTEM DSN tab QUESTION How can I set up the required DSN for my SQL? Any help is greatle appreciated! Aloha, Peter - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: ODBC DRIVERS for MYSQL
PROBLEM there is NO SYSTEM DSN offered to add under the add button under the SYSTEM DSN tab After clicking the Add button under the System DSN tab, you scroll to and select MySQL then continue. Xi2 -Original Message- From: Peter Reck [mailto:[EMAIL PROTECTED]] Sent: Friday, December 28, 2001 3:31 PM To: [EMAIL PROTECTED] Subject: ODBC DRIVERS for MYSQL A newbie question: DESIRED FUNCTION: to import data from MySQL db to MS word through webquery ODBC I was guided to install myODBC by going to http://www.mhysql.com --- downloads -API's --MyODBC myODBC 2.5.39 DOWNLOADED, UNIZPPED AND INSTALLED I was told to add a NEW SYSTEM DSN for MySQL PROBLEM there is NO SYSTEM DSN offered to add under the add button under the SYSTEM DSN tab QUESTION How can I set up the required DSN for my SQL? Any help is greatle appreciated! Aloha, Peter - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: ODBC DRIVERS for MYSQL
-Original Message- From: Peter Reck [mailto:[EMAIL PROTECTED]] Sent: Friday, December 28, 2001 3:31 PM To: [EMAIL PROTECTED] Subject: ODBC DRIVERS for MYSQL A newbie question: DESIRED FUNCTION: to import data from MySQL db to MS word through webquery ODBC I was guided to install myODBC by going to http://www.mhysql.com --- downloads -API's --MyODBC myODBC 2.5.39 DOWNLOADED, UNIZPPED AND INSTALLED I was told to add a NEW SYSTEM DSN for MySQL PROBLEM there is NO SYSTEM DSN offered to add under the add button under the SYSTEM DSN tab QUESTION How can I set up the required DSN for my SQL? Any help is greatle appreciated! Are you sure there is no ADD button under System DSN ? Anyway, try creating USER DSN. For more information, refer to: http://www.mysql.com/doc/O/D/ODBC.html or, refer to the following link on how to create User DSN for MyODBC. http://www.devshed.com/Server_Side/MySQL/ODBC/page4.html Regards, Venu -- For technical support contracts, go to https://order.mysql.com __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Venu [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Developer /_/ /_/\_, /___/\___\_\___/ California, USA ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: example scripts
You get quite a few examples with the documentation which is very good (I had nil experience with SQL before reading the MySQL docs and had no problems learning by it -- and I'm an learn-by-example man myself). Plus I don't think I'm wrong if I say a Google search on MySQL tutorial will return a zillion results. :-) Bogdan Bobby Brooks wrote: forgive the newbieness of this question, but is there some example databases to play with on mysql?...sort of like the northwind traders example database for m$ access...i have only been playing with mysql for a couple of days, and so far impressed overall, but the easiest way for me to learn things, on a computer especially is to toy with something completed to see what makes it tick, taking the clock apart and inspecting the gears so to speak if anyone knows of a place to get ahold of such databases, i'd appreciate the tip - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Most Performance, Opinions ?
Hello! I'm looking for opionions or advice about configuration options and table type choices. We're into performance, we want to have the maximum possible querys per/second, or as low resource usage as possible, for the choosen codebase/system (linux+apache+php4+mysql). Our primary (sql) server is a AMD 1.4GHz, 1.5GB RAM, 18GB SCSI. Also a secondary server used for internal calculations. (crontab etc) which replicates from the primary server. Our application is Community style (messages, guestbooks, messageboards..) where every .php-request generates at least one connection and one or more selects, and possibly one or two inserts depending on the users action(s). We have two load balanced webservers connecting to the primary sql server. On these we have about 1000 simultaneus users on the site, which gives about 150 Queries per second avg. at peak times. We have heavy congestion on one table, the one with all user accounts in. This gives a lot of processes waiting for the table locks, and if we set a low max_connections this will generate errors. with a large max_connections there will be like 300 threads waiting for the same table once in a while. We have tried running with InnoDB in this table, but this only creates new problems, as we have indexed TEXT columns, for example with the usernames in. We end up having a huge load average and 250 threads in sending data state. What choice do we have exept a full code-rewrite and a redesign of the entire database ? We need no transactions, or any data integrity at all. We can afford to loose some data in trade for performance. (like a few hours or something, which seems like a lot even if the replication whould fail). Regards Johan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Optimizing compound indexes on big base
I need the collective experience brains of the group for this one. My app is a phone book -- I need to search by name, city, province, etc. But there are over 22 million entries. I like using compound indexes, but they make the database VERY big, and slow to load. The last 2 million entries took over 24 hours to load using mysqlimport, normally a fast program. The data file has swollen from 580M to 2G. The index file from 180M to over 3G. I can't help but think that a 3G index file defeats the purpose of speedy indexing; for one thing, I can't keep it all in memory at once anyway! The indexes i'm using look like this: ALTER TABLE newdb.White ADD INDEX phone_no (phone_no); ALTER TABLE newdb.White ADD INDEX lfc (last_name(12),first_name(8),city(12)); ALTER TABLE newdb.White ADD INDEX lcs (last_name(12),city(12),street_short(10)); ALTER TABLE newdb.White ADD INDEX lps (last_name(12),province,street_short(10)); ALTER TABLE newdb.White ADD INDEX loc (city,street_no(10)); ALTER TABLE newdb.White ADD INDEX postal_code (postal_code); Is there some guideline to a trade-off between index selectivity and size here? Should I, for example, just use 3 characters of each field instead of 8 or 12? Hints? Steve -- Steve Rapaport World Citizen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Most Performance, Opinions ?
Hi. My question is similar to yours in some ways, perhaps we can help each other. You say We need no transactions, or any data integrity at all. We can afford to loose some data in trade for performance. (like a few hours or something, which seems like a lot even if the replication whould fail). If that's the case, why are you doing table locks at all? In most cases they won't be needed, and in the few cases where data might be overwritten, you can sacrifice that data for performance, no? Also why are you reconnecting? I think you can use mysql_pconnect() to create a connection pool and save the connection overhead. -- Steve Rapaport World Citizen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
query error
Hi, all When I query data from a table ,the following error occurs: ERROR 1030: Got error 127 from table handler. Would you please tell me the reason to cause the error and how to deal with it? Thank you! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
UPDATE command, adding to exisiting record
I am looking to update a record in a table, but take the exsisting value and adding the new value to it... I.e. Exsisting record = 150, new record being inputed = 250 for a total of 400 to be entered into the database. I have looked at the man pages on this but all it gives is static numbers, I need something that is being fed from a form and the new values will always be different. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Most Performance, Opinions ?
- Original Message - From: Johan Wahlström [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, December 29, 2001 2:39 AM Subject: Re: Most Performance, Opinions ? Hello! Can I actually turn off locks all the way, so that updates never locks a MyISAM table at all ? regarding mysql_pconnect() we haven't tried that, but the nature of the site suggests it would need like 500 apache deamons connected all the time. And that's a lot of sort buffers and stuff beeing allocated for each one of those ? regards, Johan - Original Message - From: Steve Rapaport [EMAIL PROTECTED] To: Johan Wahlström [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Saturday, December 29, 2001 2:16 AM Subject: Re: Most Performance, Opinions ? Hi. My question is similar to yours in some ways, perhaps we can help each other. You say We need no transactions, or any data integrity at all. We can afford to loose some data in trade for performance. (like a few hours or something, which seems like a lot even if the replication whould fail). If that's the case, why are you doing table locks at all? In most cases they won't be needed, and in the few cases where data might be overwritten, you can sacrifice that data for performance, no? Also why are you reconnecting? I think you can use mysql_pconnect() to create a connection pool and save the connection overhead. -- Steve Rapaport World Citizen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Most Performance, Opinions ?
regarding mysql_pconnect() we haven't tried that, but the nature of the site suggests it would need like 500 apache deamons connected all the time. And that's a lot of sort buffers and stuff beeing allocated for each one of those ? Not if you disable keepalive requests, which I do on most web servers with heavy PHP and MySQL usage. It doesn't impact performance on the client side much since we keep our images on another server which can have keepalives enabled. You said you had messageboards, guestbooks, etc. Do any of these scripts use something like... SELECT table1.stuff, table2.stuff, table3.stuff FROM table1, table2, table3 WHERE (required constraints for joins) AND table1.messageboardid = '#' ORDER BY table1.timestamp DESC LIMIT 5; ? On large tables, this will lock the table for a long time since MySQL can't follow indices in reverse order, so it ends up having to do a full table scan. I have found that breaking it into two separate queries has improved performance considerably: SELECT id FROM table1 WHERE messageboardid = '#' ORDER BY timestamp DESC LIMIT 5; SELECT table1.stuff, table2.stuff, table3.stuff FROM table1, table2, table3 WHERE (required constraints for joins) AND table1.id IN (ids taken from previous query, separated by commas); This reduces the amount of time MySQL will lock the table, since with the first query it will do the full table scan on all 3 tables involved in the join and will keep all 3 tables locked for that time. I have seen times where the first method with one query takes 10 to 15 seconds to execute, while the second method with two separate queries both take 0.00 sec according to the MySQL command line client. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: UPDATE command, adding to exisiting record
I am looking to update a record in a table, but take the exsisting value and adding the new value to it... I.e. Exsisting record = 150, new record being inputed = 250 for a total of 400 to be entered into the database. UPDATE table SET row = row + 250 WHERE id = #; - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mySQL Issues
I run a site that gets a LOT of traffic and uses mySQL. But for some reason, mySQL is making MAJOR load on my server. I have tried it on several servers, and they all end up with load averages like 10+! We have optimized our code as much as possible and STILL are getting super high loads! Is there ANYWAY to optimize mySQL so it doesn't run s much load?? Sincerely, Matthew Hale Get paid cash every time you receive email! Sign up FREE at: http://www.MintMail.com/?m=1825549 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Syntax error with a JOIN
I'm pulling my hair out about a query that should be simple. I have a table members with columns id, group_id, and a few more that aren't important here. group_id refers to another row in the same members table, but not all members have a group_id. What I want to do is select, for a particular member, the member's name and (if the member is in a group) the group's ID and name. Here's the simplified query: SELECT members.name, group.id, group.name FROM members LEFT JOIN members AS group ON group.id = members.group_id WHERE members.id = 6 And here's the error I get: You have an error in your SQL syntax near 'group ON group.id = members.group_id WHERE members.id = 6' at line 3 Can anybody explain to me how I can get this to work? Is there some kind of trick to joining a table to itself? Thanks, Keegan Miller - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
NetBSD versus RedHat versus Solaris x86
Hello all, I just ordered a new server to act as a database-only server for my website (wheresgeorge.com). I'm currently running mysql 3.22.29 (I'm stuck there due to my web hosting provider). I plan to upgrade to the latest stable version of mysql, but have the following questions about the choice of Operating System. The hardware is a Dell PowerEdge 2550 Dual 1GHZ Pentium/1GB Memory, 3x18GB SCSI disks and will be set up as RAID 5 via a PERC3/Di card. I also require large file size support (2GB). My current database has over 16 million records and processes about 80 queries per second (on average). Peak usage could be around 300 queries/second (estimate). It is running on Linux on a dual PIII-800. Performance is very good, but I'm going to hit the 2GB limit soon, and need to expand beyond what my ISP can provide with their managed solutions. So I am going to co-locate this new DB server as a backend to the current webserver. On to the questions: I was considering Net/FreeBSD, but read about threading problems in the archives, so I don't know if they have been resolved. I was then thinking about RedHat7.2 with the 2.4 kernel (large file size support, I think). I then read about Solaris x86.. which is where I'm leaning, but I was told Solaris x86 might be slower than Linux of Free/NetBSD. I'm also thinking of installing Oracle 8i as a side-by-side comparison to mySQL running on the same HW/OS. Anyway, I'm not a UNIX whiz/admin by any stretch... I know enough to *NIX to manipulate files, jobs, processes, etc. I've even compiled a few packages in the past and got PHP/mysql installed and running on a linux box last year. I can write rudimentary shell scripts. I'm looking for general comments regarding the current status of any of these choices of OS on this hardware in relation to running a fast and stable mySQL installation. Are there any pitfalls I need to watch out for on any one OS? Is any one significantly more favorable for mysql that the other? As I've said, I have searched the archives, but can not find any real-current comments on OS selection. Thanks in advance, -Hank Eskin ([EMAIL PROTECTED]) __ Do You Yahoo!? Send your FREE holiday greetings online! http://greetings.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Hi. Here is the An-san City of the South Korea.
Hi. Here is the An-san City of the South Korea. We are the company which is developing the programs #8211; ERP(ENTERPRISE RESOURCE PLANNING) and has been concerned with MY_SQL DATABASE. Recently, we suggested to the client of Korea that we want to develop the programs(ERP, MIS, MRP) with adapting your MY_SQL DB, but we are rejected by the client. We took the response to the client ,after we discussed the problem with the client, that if we offer to the client an efficiency test table of MY_SQL and the rest of data, the client will examine this data. Thus, we would like to take the data with the following questions from yours. - Brief yours and what is your adapted businesses? - If the ERP is developed with MY-SQL, is it possible for the ERP to adapt with MY_SQL ? - What is the representative company which is adapted? - User number of MY_SQL, speed, efficiency and stability (in the Window 2000 Server and Linux) - The price list for the user number (in the case of Window 2000 and Linux) - If the client agrees to our company¡¯s using your DB, can you recognize us to the main agency of Korea? So far, thank you for reading our letter. We wanna receive your response as soon as possible. 2001. 12.28 Arcline. Cor. A principal administrative officer Mun-choel Shin === ¿ì¸® ÀÎÅͳÝ, Daum http://www.daum.net - 2002 ´ëÀÔ 1:1»ó´ã,¿ø¼Á¢¼ö,ÇÕ°ÝÀÚ¹ßÇ¥ ¢Ñ http://hmm.daum.net/daeip_0112 - ÀÎÅͳݿ¡¼ 'ÅäÁ¤ºñ°á' º¸¼¼¿ä~ ¢Ñ http://hmm.daum.net/fortune_0112 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
