Re: which jdbc to use?
There are really only two. mm.mysql (varisous version) and the twz one. I suggest the mm.mysql one. They are both type 4 I believe there is no reason to choose the twz one over the mm one. I would use the latest 2.0 version unless you have code that would have a problem with it then you can use 1.2c. e.res wrote: hey all! i have a linux box with sdk2 java, apache,tomcat and mysql. i am planning to do some servlets/database development and was wondering what jdbc driver to utilize for that. mysql.com has several of them available for download. i also have j2ee distribution which i am planning to install (doesn't it come with jdbc? if that's the case this should be sufficient to get basic database tasks done between java and mysql? am ia on the right track here? ) can someone shed some light on this? thanks a lot! james __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.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 - 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: Question for a MYSQL expert (I assume this list has some)...
What you are asking to do is not built into MySQL :( Mysql matintains its own internal database locking that would not be shared amongs the other computers, if you have multiple machines reading a writing to the same database file you will corrupt it in short order. Your best bet in such an environment (IMHO) would be to better arrange your database(s) so that they can be logicaly split amongst multiple computers. You can still use the common drive array as long as you don't have multiple computers assigned to the same database. For example we have some systems that devide sessiontracking, order management, catalog images, separately. We have another system that that has databases split along a logical divider and a single database that acts as an index server. For example if you are managing a collaboration software type database break your projects to different servers by the letter they start with, or simply round robin their creation across multiple servers and maintain a single server that has the index containing which project is on which server. Back up all of the servers independantly and you're ready to do.. You can get pretty big doing it like this. Maintenance is a little higher than for a single master database, but again, mysql doesn't do that, so if you want to use mysql think differently. It may be possible to maintain multiple read servers and a single master write/update server while attached to the same database, but the way I see it this wont' work either given the way MySQL caches indexes and the servers will cache the file system (databases). Quite frankly ebay, Microsoft, apple and amazon are not using MySQL in environments that require multiple servers to accomplish single data management tasks. Kenneth Kopelson wrote: I am hoping there are people who use MySQL for serious business applications on this list. If so, please answer my question :) If not, will someone please direct me to a source of expert information? HERE IS MY QUESTION: I have need for multiple servers to share the same database files. We are implementing a site that will have hundreds of thousands of users at the same time, so we need a large pool of Linux servers that are load-balanced. Then, all these servers, each of which will run Apache and MySQL (or another database if MySQL just can't handle the load), needs to access the single large RAID disk array through a giga-bit network connection. Certainly, this is not uncommon, as any large database driven website needs to have a similar setup (like eBay, Microsoft, Apple, Amazon, etc.). The servers need to be setup in a cluster to provide the required high-availability. Also, replication will NOT work, as we need changes to the database to be immediately available to everyone else, and managing 100 or more replicated databases would be a nightmare. So, how do we get support in MySQL so that multiple instances of the mysqld daemon running on separate machines can all access the same database files located on a central server? I can see how the file system of the central database server could be exported through NFS (or something similar) so that all the machines in the cluster can access the database files. I also imagine the daemons would need to have some sort of locking mechanism to avoid stepping on each other. Any help in this matter would be greatly appreciated. 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 - 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: Question for a MYSQL expert (I assume this list has some)...
Kenneth Kopelson wrote: So, how do we get support in MySQL so that multiple instances of the mysqld daemon running on separate machines can all access the same database files located on a central server? I can see how the file system of the central database server could be exported through NFS (or something similar) so that all the machines in the cluster can access the database files. I also imagine the daemons would need to have some sort of locking mechanism to avoid stepping on each other. Also... MySQL is open source, so the easiest way to get support for a feature it doesn't have is to get together a group of people to write it. I would start with InnoDB format tables since table level locking would probably kill you on a such a large distributed environment. - 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 databases over two drives
Look up link or something in the manual. There is a way to do virtual symbolic links in NT with mysql without installing anything, it has to do with adding paths to the my.cnf file, see the docs for details. Tracy A. Mitchell wrote: You can do this with NT Server as well. It is called Distributed File System (DFS). You may have to download it from Microsoft but it is free. I think it is built into Win 2000. You will have to muck around in the documentation for the details. HTH Tracy -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 29, 2001 6:54 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: mySQL databases over two drives This isn't awfully useful to you, but it's dead easy to do this on a Unix system. All you do is copy the data over wherever you like (to a big, fat, drive), and then link the new directory from the /usr/local/mysql/data/ directory (ln -s). OTOH, I guess you can't link a file/directory in Windows/DOS. - 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: Securely access a mysql server over the net
Cyber Monkey wrote: I have found an isp that will leave port 3306 open for me to talk to the mysql server, my question is Is anything sent over this connection secure, if not is there some way of encrypting it such as ssl? Yes.. you ssh like here www.ssh.com www.openssh.org and do not communicate with the server over port 3306 but over a tunneled connection via ssh. - 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 performance question
VVM Ravikumar Sarma Chengalvala wrote: Joshua, Comparing Oracle vs. MySQL myisam tables, MySQL will save disk space, and provide you with much faster queries, but you have to be aware of the table locking issues if you are doing updates/inserts/deletes mixed with reads. MySql documentation says that mysql enforces table level locking if no explicit locking is there.If I am using non transaction sensitive tables can I go ahead without providing any explicit locking?I am using myISAM. Regards, Ravi You don't need to provide explicit lockin in most cases. But if you do not and mysql needs to make a lock for an update it will lock the table against other reads until the update is complete, it is quick, but if you are pushing the server with many updates and reads at the same time it can be very slow, or if you have updates that take a long time you can create a large queue of waiting readers. - 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: Microsoft Access and Mysql
Chris Wilkinson wrote: I am attempting to upload an Access db to a remote mysql server - at dsvr in the UK. I get connected (I think) but receive the following error message: Microsoft Access ODBC - call failed [Microsoft][ODBC Driver Manager #0] Can you point me in the right direction for this or offer some help. Thanks CHRIS -- Chris Wilkinson You need to explain better what you are doing. You cannot upload an Access DB to a MySQL server. You can upload and insert the data, you can write some program to make calls to both DBs and insert the data but you can't just upload the database? And where are you getting the error from, that error is from Access not mysql? - 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: Microsoft Access and Mysql
On the mysql.com website under downloads: contributed there is a chunk of VBA code you can put in an access database to make it export to a SQL text file, use that, you are better off, otherwise export from Access to text (comma separated for example) and use LOAD DATA in mysql to load the file. Chris Wilkinson wrote: Steve Thanks for replying. I have an Access 2000 database called webugnet and I am attempting to Export it. I have installed MyODBC and configured a mysql connection to the sql server on DSVR. So, in the table window right click on the database and select Export This brings up an Export table to: window In Save As select ODBC and this brings up a window Export webugnet to webugnet in ODBC database. Click OK and this brings up a window Select Data Source and on this select Machine Data Source Select the mysql connection with the correct database and click OK Then up comes the error. I previously got a wrong user name and password error but solved that one by correct configuration. Hope you can follow what I am doing or doing wrong from this. CHRIS In message [EMAIL PROTECTED], Steve Ruby [EMAIL PROTECTED] writes Chris Wilkinson wrote: I am attempting to upload an Access db to a remote mysql server - at dsvr in the UK. I get connected (I think) but receive the following error message: Microsoft Access ODBC - call failed [Microsoft][ODBC Driver Manager #0] Can you point me in the right direction for this or offer some help. Thanks CHRIS -- Chris Wilkinson You need to explain better what you are doing. You cannot upload an Access DB to a MySQL server. You can upload and insert the data, you can write some program to make calls to both DBs and insert the data but you can't just upload the database? And where are you getting the error from, that error is from Access not mysql? -- Chris Wilkinson - 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: Microsoft Access and Mysql
there is no command line.. you go to the mysql.com site, download the access converter on the contributed programs page, copy the code into an access module (macro), edit the code to tell it where to save the result and run it. It will produce a text file, then you can do mysql mydatabase mytextfile_fromaccess Chris Wilkinson wrote: Can you give me a sample command line please CHRIS In message [EMAIL PROTECTED], Steve Ruby [EMAIL PROTECTED] writes Chris Wilkinson wrote: I am attempting to upload an Access db to a remote mysql server - at dsvr in the UK. I get connected (I think) but receive the following error message: Microsoft Access ODBC - call failed [Microsoft][ODBC Driver Manager #0] Can you point me in the right direction for this or offer some help. Thanks CHRIS -- Chris Wilkinson You need to explain better what you are doing. You cannot upload an Access DB to a MySQL server. You can upload and insert the data, you can write some program to make calls to both DBs and insert the data but you can't just upload the database? And where are you getting the error from, that error is from Access not mysql? -- Chris Wilkinson - 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: table options and performance
Steve Ruby wrote: Steve Ruby wrote: I'm inserting 49,990 rows via file containing individual inserts (1 insert per sql statement). The table contains 7 columns and 6 indexes. Why is there so much difference on the time taken to insert the files? averages: MyISAM45 seconds INNODB1000 seconds BDB 600 seconds For anybody that was curious. As per the InnoDB developer's suggestion I wrapped the inserts for innodb into a transaction. result INNODB 31 seconds !! for the above 49,990 rows - 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 tablespace tools
Is there a plan to have any tablespace management tools for innobase? I personaly dont' care if these are via a separate program or through the mysql SQL interface but the following would be very handy. move tables between tablespaces list tables in a tablespace shrink tablespace It is very handy with BDB and MyISAM tables to be able to back them up directly or move them across different machines. If I have a large database server with gigs of InnoDB tablespace and we need to move a single database or table to another server it would require mysqldump and reloading the tables, which can be very slow for large tables. This seems to be the main disadvantage of the table space method. It would be nice if one could move a binary table with the following steps. - shutdown the server - copy a table into a new tablespace by itself - move the portable table space to the new server - expand the tablespace for growing room or copy the table to an existing tablespace I realize these are major things, I'm just curious if there are plans for any of the above sort of tools. - 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 and concurrent operations
There seem to be some operations that cannot be performed on the server whie InnoDB is doing something. For example I inserted about 200,000 rows in to a table from a file (mysql database file) the file starts with begin; and contains many inserts, I cancelled (ctrl-c) in the middle of the operation which caused InnoDB to rollback (as expected) but the mysql database whas unresponsive for about 10 minutes while innoDB did the rollback. The server would not respond to any processlist requests, connections or anything. This is not so good for the increased read/write concurrency expected from innodb. Also.. If a new table space has been added, it can take a while for innoDB to format 1Gig of tablespace (a long while on a 400mhz pentium). Is there a reason why the whole mysql database must be unresponsive durring this time? Why can't the system only lock out InnoDB format tables if need be and allow MySQL to startup and respond to questions. - 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: InnoDB and concurrent operations
Heikki Tuuri wrote: Steve, on what operating system you are running? linux, intel 2.2.12 Was the rollback (and the table load) disk bound? Did you configure much memory to the InnoDB buffer pool? I would say the rollback was processor bound, the machine was running with almost no CPU idletime and this event was about the only thing taking place. the machine as 192 megs ram and buffer pool=20M Was MySQL responsive during the table loading? Yes, very responsive for other MyISAM tables, select count(*) from the table I was loading was taking about 4 minutes each, but simple select queries looking for data that had not been commited were much faster. Is MySQL responsive if you do a big rollback without cancelling (ctrl-c) the client process? If the file ends in ROLLBACK; then it works fine, it rolls back and mysql is reponsive through the whole process. If the file ends with an insert and there is never a commit or rollback then mysql is unresponsive even if the load is not cancelled (ctrl-c). I will have to test further but it seems that if I do only 100,000 rows and then the file terminates, then it is okay but for 200,000 rows it can take 20 minutes or more to clean up while mysql is unresponsive. The unresponsiveness may be because MySQL reserves some semaphore while it is aborting the client connection. Or it may be due to the operating system which lets other threads starve. I'm not familiar with OS level thread control in the process itself, but the machine (other than mysql) was very responsive.. Unlike when I try to start mysql with a 1 gig table space to be built, then the load goes up to about 15 and the machine is very slow to respond (obviously). The above questions help track the reason for the unresponsiveness. If the problem is the operating system, I could try adding some calls to pthread_yield inside the InnoDB code and that might help. When InnoDB writes the 1 GB data file full, then MySQL is unresponsive because it does the initialization of the database with just one thread before creating other, listener threads. Regards, Heikki http://www.innobase.fi There seem to be some operations that cannot be performed on the server whie InnoDB is doing something. For example I inserted about 200,000 rows in to a table from a file (mysql database file) the file starts with begin; and contains many inserts, I cancelled (ctrl-c) in the middle of the operation which caused InnoDB to rollback (as expected) but the mysql database whas unresponsive for about 10 minutes while innoDB did the rollback. The server would not respond to any processlist requests, connections or anything. This is not so good for the increased read/write concurrency expected from innodb. Also.. If a new table space has been added, it can take a while for innoDB to format 1Gig of tablespace (a long while on a 400mhz pentium). Is there a reason why the whole mysql database must be unresponsive durring this time? Why can't the system only lock out InnoDB format tables if need be and allow MySQL to startup and respond to questions. - 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: table options and performance
Heikki Tuuri wrote: Steve, you should set autocommit=0 and only call commit after the whole batch of inserts. so then if I'm inserting via a mysqldump file I should append commit; to the last line of the file? If you use autocommit=1 and insert each row in a separate transaction, the database has to flush the log physically to disk after each insert, which means only some 50-200 insertions per second, depending on the rotation speed of your disk. I believe with BDB that if I inserted 700megs of data via one transaction that I would have a 700 meg log file that would have to be stuffed into the database on commit; it is not the same with INNODB? If I only have 3 50meg log files is this going to fail? I will look into the log flush behavior of InnoDB. I now call fsync (= file flush) twice after each log write, and the write is done with a separate i/o thread. I have to try doing the write with the same thread, and calling fsync only once. Ideally, we should be able to make one commit for each rotation of the disk. What is your operating system? Are you using an IDE disk? linux 2.2.12, yes, this machine has only one IDE disk on it. Regards, Heikki http://www.innobase.fi I've been doing some testing to determine tradeoffs with performance for using the Innobase or BDB tables. The testing application contains bulk inserts which must be processed as quickly as possible. The results I'm getting look very bad for innobase. Am I doing something wrong? I'm inserting 49,990 rows via file containing individual inserts (1 insert per sql statement). The table contains 7 columns and 6indexes. Why is there so much difference on the time taken to insert thefiles? averages: MyISAM45 seconds INNODB1000 seconds BDB 600 seconds The machine is a development testing machine and is memory challenged (192megs ram) these inserts were the only significant process on the machine at the time (each ran 4 times).Here are my innodb settings --- innodb_data_file_path = ibdata:800M innodb_data_home_dir = /usr/mysql/ibdata set-variable = innodb_mirrored_log_groups=1 innodb_log_group_home_dir = /usr/mysql/iblogs set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=50Mset-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1innodb_log_arch_dir = /usr/mysql/iblogs innodb_log_archive=0set-variable = innodb_buffer_pool_size=20M set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 - I expected some loss over MyISAM but the magnitude is much higher than expected, especially in light of the fact that others have been seeing innodb to be as fast or faster than MyISAM. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list 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: InnoDB, BDB, and Gemini
InnoDB definately provides more features that BDB, and is probably going to be faster, Gemini isn't available yet. BDB might be more complete/stable. So I'd say Gemini is out of the picture unless you are looking longer term. I'd do some testing with both we found some bugs in the 3.23.37 distribution with regard to BDB, Monty sais they are fix in .38 Kevin McBrearty wrote: I have re-read the appropriate manual sections several times, trying to decide what is the best option for my database table types. I am using 3.32.37 on Linux, and am torn between InnoDB, BDB, and Gemini table types. Transaction handling is very important for my application (commit, rollback, etc.). I am leaning toward InnoDB at this point but was wondering if anyone could offer any information, other than what is in the manual, regarding the pros and cons of these table types. TIA Kevin McBrearty ATG Automation Technologies Group Ltd. - 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: simultaneous connections
Don Pro wrote: Hi, I'm reading in my documentation (I have a book) that MySQL allows up to 101 simultaneous connections. Gadzooks! How can MySQL claim to be at the Enterprise level with this limit? Is there any way around this? Thanks, Don http://www.mysql.com/doc/T/o/Too_many_connections.html The limit is build in to protect people from themselves. If you wish to change the limit, have at it, note that you may also need to ajust parameters on your system including number of open files as well as mysql memory allocation. It's pretty much just saying 100 is safe for any normal machine, if you have the resources to go beyond that, read the manual, and change it. - 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: simultaneous connections
Don Pro wrote: Hi, I'm reading in my documentation (I have a book) that MySQL allows up to 101 simultaneous connections. Gadzooks! How can MySQL claim to be at the Enterprise level with this limit? Is there any way around this? Thanks, Don http://www.mysql.com/doc/T/o/Too_many_connections.html The limit is build in to protect people from themselves. If you wish to change the limit, have at it, note that you may also need to ajust parameters on your system including number of open files as well as mysql memory allocation. It's pretty much just saying 100 is safe for any normal machine, if you have the resources to go beyond that, read the manual, and change it. Don: fix you mail program, your reply-to is messed, up, how can you expect any replies. - 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 options and performance
I've been doing some testing to determine tradeoffs with performance for using the Innobase or BDB tables. The testing application contains bulk inserts which must be processed as quickly as possible. The results I'm getting look very bad for innobase. Am I doing something wrong? I'm inserting 49,990 rows via file containing individual inserts (1 insert per sql statement). The table contains 7 columns and 6 indexes. Why is there so much difference on the time taken to insert the files? averages: MyISAM45 seconds INNODB1000 seconds BDB 600 seconds The machine is a development testing machine and is memory challenged (192megs ram) these inserts were the only significant process on the machine at the time (each ran 4 times). Here are my innodb settings --- innodb_data_file_path = ibdata:800M innodb_data_home_dir = /usr/mysql/ibdata set-variable = innodb_mirrored_log_groups=1 innodb_log_group_home_dir = /usr/mysql/iblogs set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=50M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_arch_dir = /usr/mysql/iblogs innodb_log_archive=0 set-variable = innodb_buffer_pool_size=20M set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 - I expected some loss over MyISAM but the magnitude is much higher than expected, especially in light of the fact that others have been seeing innodb to be as fast or faster than 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: my sql database and unique keys
VVM Ravikumar Sarma Chengalvala wrote: Hi, How can I have an UNIQUE key in MYSQL which differentiates between case sensitive strings.i.e If I want to store XYZ,XYz and xyz differently.Currently mySQl is not accepting this type of strings and treating them as the same. Regards, Ravi You need to use the binary keyword when creating the column http://www.mysql.com/doc/C/H/CHAR.html or you can alter the column and add the binary key word, otherwise char and varchar are not compared with consideration of case - 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
BDB table handler error 30996
when a BDB table is being written to and another thread issues a query I sometimes get this error code. Ideas? The table is fresh it was created by the dumpfile that another thread is loading in while I'm running the count query. this is mysql-max 3.23.37 mysql select count(*) from _data; +--+ | count(*) | +--+ | 125917 | +--+ 1 row in set (26.58 sec) mysql select count(*) from _data; ERROR 1030: Got error -30996 from table handler both queries were run while the table was loading the data, the data is in the extedned-insert format. - 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: Using where xxxx in (select ...... )
green jon wrote: Hello, I have recently converted my Java Servlets to run against mySQL where previously I was using Microsoft Access. I use Tomcat and have swapped drivers etc and everything works fine, more or less. However, I have one big problem which is as follows :- http://www.mysql.com/doc/M/i/Missing_Sub-selects.html - 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: looking for information
Your Name wrote: dear sir/madam i'm currentlly doing a research on MYSQL and i would like to know more information about it :where does MYSQL come from who created it ... 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: char - varchar ?
Alec Solway wrote: Hi, In various create table statements char columns are converted to varchar.. I was wondering why this was happening. Here's an example: CREATE TABLE realtors(name VARCHAR(255) not null, address VARCHAR(255) not null, city VARCHAR(255) not null, state CHAR(2) not null, country CHAR(2) not null, zip CHAR(5) not null, phone VARCHAR(50) not null, cell VARCHAR(50) not null, fax VARCHAR(50) not null, nvio VARCHAR(25) not null, estlead CHAR(5) not null, servicearea VARCHAR(255) not null); Query OK, 0 rows affected (0.11 sec) Here, zip and estlead have been converted to varchar(5). Thanks. Alec see the manual www.mysql.com/doc There is no problem here, that is standard be behaviour, by having any variable size columns on a row you defeat the advantage of fixed length rows so the db might as well use varchar to save space, so it does. - 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: bug report
For over a year the topic of mathematical operations on date values
has been disussed. It has never been possible to perform such math
on a date value without using DATE_ADD or converting the to days or
seconds first.
Why is this now all the sudden a reasonable bug?
Aurelian:
I suggest that you use DATE_ADD, I'm quite sure what you have found
is not a bug
20010501 - 1 = 20010500
MySQL sees CURRENT_DATE as the value 20010501 and performs your subtraction
as requested, if you want to subtract a day from today I suggest
DATE_ADD( CURRENT_DATE, INTERVAL -1 DAY )
Or the previously suggested TO_DAYS, FROM_DAYS method.
Sinisa Milivojevic wrote:
Aurelian Dumitru writes:
Please record the following bug identified on the MySQL server:
1. Hardware: SUN Ultra 10
2. Operating system: Sun Solaris 2.7
2. MySQL server version: 3.23.33
3. Error description:
- The following SQL statement returns incorrect results when is executed
using the 'mysql' client: SELECT @myvar := ( CURRENT_DATE - 1) ;
- I ran this query every day. It worked fine untill May 1st, 2001, when the
above query returned the following value: 20010500 . It should have returned
20010430.
Aurelian Dumitru
[EMAIL PROTECTED]
Hi!
Thank you for a repeatable bug report.
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers 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
-
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: asian charatersets in mysql?
http://www.mysql.com/doc/c/o/configure_options.html http://www.mysql.com/doc/A/d/Adding_character_set.html Luis Hoeks wrote: I would be very pleased, if someone could tell me if MYSQL supports asian character sets like big5 or thai charctersets. If yes, what do I need to install to get it running. Where can I find any help? Any advice is very much appreciated! Kind regards from Luis _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.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 - 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: Viewing data from the previous row
Roger Karnouk wrote: Is it possible in a Select query to get values from the previously read row in order to do things like cumulative sums or other formulas which require the previous value in order to calculate the current value. ex. day current sales cumu_total - --- 1 120 120 2 60 180 3 125305 4 40 345 I'd like to be able to do this in one select statement is this even possible? Roger Karnouk [EMAIL PROTECTED] You can totalize all numbers but there isn't really any way in sql to do cross-row type functions as you indicated? Why can't you do the running some in whatever program is generating the output? - 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 and trigger/transaction support
Matthew Hafner wrote: does mySql support triggers or transactions Matthew Hafner Web Applications Engineer The Ohana Foundation 1099 Alakea st 22nd floor Honolulu, HI, 96813 1-877-61-ohana [EMAIL PROTECTED] RTFM 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: Info on using MySQL with Java
Hi all, I'm looking for some pointers towards some good information on accessing and using MySQL with Java/Javascript. Can anyone point me towards a book you like, or some good web resources? Thanks much, /Rob You'll need a servlet and/or JSP engine such as listed here http://www.servlets.com/engines/ I've used tomcat, resin and jetty with no problems with mysql, obviously you will need a JDBC driver, the mm one seems to be the best available see www.mysql.com and then downloads and follow the links for JDBC. You'll have to decide on JSP or servlets or some like template systems best see: www.webmacro.org jakarta.apache.com/turbine jakarta.apache.org/velocity jakarta.apache.org/struts - 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 commands from Linux shell
Fabio Galarraga wrote: Hi to all: I have a linux script and I need it can execute some mysql commands. Is it possible? If possible, how? Best regards, Fabio Galarraga [EMAIL PROTECTED] see mysql --help you probably want mysql -e "SQL code here" dbname - 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: today type function for TIMESTAMP col?
Andrew Warner wrote: how do you query on a TIMESTAMP column to return all records of date today? I've been using "where date = 20010418 AND date 20010419" syntax and changing the dates as necessary, but I'd like to replace this with some kind of today() function. Andrew Use now() and DATE_FORMAT to do something similar to what you are doing above, see the manual at www.mysql.com for details. - 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: uninstalling MySQL
Probably should change that first line to rpm -qa | grep -i mysql Roel Vanhout wrote: rpm -qa | grep mysql if nothing found, rm -rf /usr/local/var rm -rf `find /usr/local/bin/ | grep mysql` rm -rf /etc/my.cnf This should catch about everything. cheers, roel On Sun, Apr 08, 2001 at 12:24:08AM -0400, David Loszewski wrote: how do I uninstall MySQL 3.22 after installing it? worst part is that I installed it so long ago that I can't remember if I did it by .rpm or .tar help please, Dave - 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: Converting Access to MySQL
Luis wrote: Good morning all, last night I installed MyODBC on my laptop. What i dont understand is how do i connect threw access now? I'm a little confuse, can anyone help me understand how MyODBC works. luis It is an ODBC driver for Mysql. Create a databsae in mysql go to the ODBC control pannel create a Datasource that usign my odbc and points to your mysql database now you can use anything that understands ODBC to work with your database, for example you can link it to access. - 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: No Stored Procedures - Big Deal
How come nobody has mentioned that complex data manipulation can be performed on the server without waistuful and slow transmission of unwanted data to the client? that seems like the biggest advantage to me. Meaning that if I have to do something to the data that SQL doesn't allow without SPs I have to send large datasets to the client to be manipulated, with SPs data manipulation can be done on the server (which is typicaly more powerful) and then only the smaller answer data is returned to the client. Cal Evans wrote: Stored procedures offer several advantages over embedded SQL. 1: It is easier to write, debug and modify SQL code in a stored procedure with a tool designed to do that. You can write PHP from the command line also but not many people do it. 2: Placing the code in a stored procedure allows you to divorce it from your other code and debug it separately. 3: It allows for easier division of labor. In our shop, we routinely hand off the SP's to the data team. 4: Stored procedures allow for a greater degree of code reuse. But the biggest advantage. 5: Stored procedures are normally (Oracle, MS SQL, Interbase, etc.) compiled code. They are compiled once and used in their compiled state until they are modified. This offers faster execution than embedded sql, which much be compiled each time it is submitted. IMHO, Cal http://www.calevans.com -Original Message- From: John Dean [mailto:[EMAIL PROTECTED]] Sent: Friday, March 30, 2001 7:50 AM To: Gary Huntress; Mysql (E-mail) Subject: Re: No Stored Procedures - Big Deal Hi I must agree with you on this point, after what can be done in a stored procedure can also be done with SQL. The only real advantage I can see to provide a centralized store of commonly used SQL, but then again these can be stored in a text file. The disadvantage is a down grade in performance, which is exactly what MySQL tries to avoid. On Friday 30 March 2001 14:19, Gary Huntress wrote: I don't mean to be argumentative, but what is everyones love affair with stored procedures? I like putting my logic in the mid-tier and I'm constantly battling my Sybase Admin at work who maligns me whenever they spot a query in my codeeven a very simple select "oh, I can make that a stored procedurebecause what if I change something?" me "then my code will break and I'll fix it.*somebody* always has to change something!" Triggers I can understand, they take away much of my validation headaches (and make me correspondingly lazy), but what am I missing about the panacea | paradise | emerald city of stored procedures? Regards, Gary "SuperID" Huntress === FreeSQL.org offering free database hosting to developers Visit http://superid.dyndns.org:8080/freesql/index.php -- Regards John MySQL Development Team __ ___ __ __ / |/ /_ __/ __/ __ \/ / John Dean [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\/ Mansfield, England, UK ___/ - 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: Looking only for uniqueness
"Alexey V. Litvinov" wrote: Hello JCampell, Friday, December 22, 2000, 11:25:51 AM, you wrote: RTFM about SELECT and about DISTINCT option J I want to know if it is possible to build a MySQL query which will only J return the unique values of a specific column. Ie if I had this table: J id | name |date___ J 1 | Jon | 2001-01-03 J 2 | Bob | 2001-01-04 J 3 | Jon | 2001-01-05 J Is there any SQL query that will only return 1 row for each value in the J name column, or would I have to do that in PHP? You will have to do that in PHP or use group by. Distinct will only return rows that are unique for all columns not just one as you wish.. If you want one day per unique user you will have to tell the database which row you want for Jon. RTFM on GROUP BY :) - 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: SQL query
Pawan Kanda wrote:
Hi , i have the following method:
public Object getValueAt(int row, int column){
// in here i want to construct a SQL query which gives the object at a
certain
row and column
}
Do you know how to do it?
Thanks
Pawan
Maybe make your sql statement read
sql = "your query here " + " LIMIT " + row + ",1";
.. run your query ..
then you would just return
myrecordset.getwhatever(column);
if you need to make many calls to this get value it would be very slow,
you should maybe run the query and set it to some object in the class
so you can just pull from it (like an array), unless it is HUGE, but
then there are trade-offs, the above will be required to run the query
every time you access the method.
-
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 Database and NAS
Patrick Calkins wrote: Hello all; Are there any known problems with storing the database files on a NAS (network attached storage) device?? also, can you run multiple MySQL daemons pointing to one common database, so you could have a "cluster" of database servers and one database?? Thanks! Patrick You can only have one data repository and multiple servers if you never write to your database.. Unfortunately unless you use replication mysql doesn't handle mutliple servers pointing at the same data, one server has no way of knowing that cached indexes are out of date based on updates by another server. - 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: win to linux
rozakdemir wrote: Hello, I am using MySQL on windows platform. How can I port mt datas from MySQL server on windows to MySQl server on Linux? Thanks for all. You should search the sarchives and manual, this is a very FAQ... tables extension is like .MY* you can just copy them, if not you need mysqldump.. for details see mail archives. - 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: apostrophe ....
Randy Johnson wrote: When a text with an apostrophe in it has a \' in it. How can I fix this? example randy's is stored in the DB as randy\'s thanks Randy If you just want to fix that one instance just do update tablename set columname = 'randy\'s' where somekey... - 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: JDBC-related query!!
gnanas wrote: Dear Sir, We are from Chennai, India. We are holding one site constructed using JSP pages. We couldn't connect to the mysql database using myodbc driver! The specified driver is not installed in the virtual server the hosting company! Do we have any otehr alternative? Please respond to this asap! Thanking You, Yours truly, MediCreations. It is just a jar file it doesn't really have to be installed, just put the driver jar somewhere where the jsp runner can see it (the lib directory or something), depends on which servlet/jsp engine you are running, if it is servlet 2.2 compatible you should be able to put it in WEB-INF/lib directory - 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: problem/BUG with create table statment
"Gupta, Sanjeev" wrote: Hi, I have tried to create a table with two fields. One is type CHAR and other is VARCHAR and it convert the CHAR to VARCHAR automatically. IS it a bug or i need to do some setting to work properly. I am working on WINNT machine. http://www.mysql.com/doc/S/i/Silent_column_changes.html - 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: Need help optimizing this (simple) query
Jordan Russell wrote: Everything I've seen on this list indicates that using LEFT JOIN negates using an index. (WARNING: This is 3rd hand info and as such should be viewed skeptically!) Have you tried: SELECT files.id, dirs.name FROM files where files.dir_id=dirs.id ORDER BY files.date DESC LIMIT 1; If so, what were your results? Thanks.. I had to add ", dirs" for that to work: SELECT files.id, dirs.name FROM files, dirs where files.dir_id=dirs.id ORDER BY files.date DESC LIMIT 1; But unfortunately the results were exactly the same: 0.03 sec query time, and EXPLAIN SELECT showed the same output as when I used a LEFT JOIN -- no index, and "using filesort." I also tried adding "USE INDEX", but still the exact same results: SELECT files.id, dirs.name FROM files USE INDEX (date), dirs where files.dir_id=dirs.id ORDER BY files.date DESC LIMIT 1; And I tried reversing the column/table order; that didn't help either: SELECT dirs.name, files.id FROM dirs, files where dirs.id=files.dir_id ORDER BY files.date DESC LIMIT 1; Jordan Russell Do you have an index on files that starts with dir_id and an index on dirs that stats with id and having date in the files index (after dir_id) might be helpful although I'm not sure. you could provide the output of show keys from files; show keys from dirs; it would be helpful.. - 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: Big Tables
Roger Westin wrote: Hi, Have a problem with big tables, Cant get them over 2GB Using mysql 3.23.33 and ReadHat Linux 6.0 Kernel: 2.2.5 Need to biuld a table atleast 70Gb so Anyone? switch to a file system that supports very large files The problem should be with linux no mysql. there is an option to split tables to different files (see with-raid in the manual) but it won't split indexes, and if you have 70gig of data you probably have a very large index as well... - 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: Connect Apache
[EMAIL PROTECTED] wrote: On Fri, 9 Mar 2001, Jon Haworth wrote: Not sure exactly what you're after... MySQL isn't an Apache module, it's a separate program. Where can I find Apache module for mySQL ?? maybe "mysql module for apache"? regards -- You mean mod_auth_mysql, try here: http://www.mysql.com/downloads/contrib.html - 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 backup mySQL database?
Keneth wrote: "When I try to dump sql database it gives me some errors 1.Error 1045 mysqldump --opt database backup-accp.sql mysqldump: Got error: 1045: Access denied for user: 'accp@localhost' (Using pass word: NO) " can you help? with best regards, Keneth Read the manual, read "mysqldump --help" you probably need to specify username and password switches. - 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: optimising joins: where vs. using/on
Christian Hammers wrote: Hello list While browsing old sources by a former employee I realised that he always did comma seperated joins and then a "where" FROM tablea a, tableb b, tablec c WHERE a.id=b.id and b.nr=c.nr whereas I learned to do FROM tablea a LEFT JOIN table b USING (id) LEFT JOIN table c USING (nr) Is one of the two ways preferred about the other? I like my way as it is easier to read and understand but would use anything to gain some speed in my 5+ hour selects... bye, -christian- He is doing an inner join, you are doing a left join, they (potentialy) do not produce the same results. If you have 5 hour selects you are probably missing some vital indexes, or you are using a long of like '%blah' or "OR" - 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: optimising joins: where vs. using/on
Steve Ruby wrote: Christian Hammers wrote: Hello list While browsing old sources by a former employee I realised that he always did comma seperated joins and then a "where" FROM tablea a, tableb b, tablec c WHERE a.id=b.id and b.nr=c.nr whereas I learned to do FROM tablea a LEFT JOIN table b USING (id) LEFT JOIN table c USING (nr) Is one of the two ways preferred about the other? I like my way as it is easier to read and understand but would use anything to gain some speed in my 5+ hour selects... bye, -christian- He is doing an inner join, you are doing a left join, they (potentialy) do not produce the same results. If you have 5 hour selects you are probably missing some vital indexes, or you are using a long of like '%blah' or "OR" Depending on the data in 'a' and 'b' and the exact WHERE clause it is possible in many cases that the inner join (commas) will be faster than the left join. If 'b' has fewer id values than 'a' for example the database has fewer rows to search with the inner join. - 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: Converting a FreeBSD UNIX Mysql Database
Tom Flanagan wrote: Does anyone know how to convert a FreeBSD UNIX MYSql database to Windows NT/2000 MYSql Database? Thanks, Tom Flanagan If you are using MyISAM tables (3.23.x) you can simply copy them from one computer to the other. If you are using ISAM tables like in 3.22 you will need to do something like: On FreeBSD: mysqldump --opt database dumpfile then on windows mysql database dumpfile you should check the manual for details on mysqldump because you may need to pass it a password, you might not want --opt, etc etc. - 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: optimising joins: where vs. using/on
Christian Hammers wrote: On Thu, Mar 08, 2001 at 10:29:20AM -0700, Steve Ruby wrote: He is doing an inner join, you are doing a left join, they (potentialy) do not produce the same results. Hmm have to think about it... inner means fields where the right table has a NULL value in the condition are left out, right? If you have 5 hour selects you are probably missing some vital indexes, or you are using a long of like '%blah' or "OR" ... or using 1.5GB big tables... thanks, -christian- See the manual... I think you have them backwards, left join means that lines from 'a' without corresponding lines in 'b' WILL be shown with NULL Inner join (commas) means you will only get lines where there exists a b.id corresponding to every a.id 1.5 GB tables to not produce 5 hour selects unless you are lacking indexes or using OR or '%like' or other criteria that doesn't use indexes in mysql. - 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: optimising joins: where vs. using/on
Christian Hammers wrote: On Thu, Mar 08, 2001 at 10:29:20AM -0700, Steve Ruby wrote: He is doing an inner join, you are doing a left join, they (potentialy) do not produce the same results. Hmm have to think about it... inner means fields where the right table has a NULL value in the condition are left out, right? OOPS I didn't notice you said LEFT OUT, yes you have it right, inner join will return only lines that exist in both tables, sorry for the earlier confusion. - 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: Cold Fusion
Andrew Staples wrote: New to list, and to mySQL. If there are archives available, please let me know--I'll search them. I have a client who is building a new win2k server, and wants to use ColdFusion to connect to a mySQL db, instead of MS SQL7.0. Docs show it will work with myODBC, but gives examples for Unix. 1. Can CF work with mySQL via myODBC on win2k? 2. Is there a better way to use CF and mySQL? 3. Any other gotchas? Thanks, Andrew Staples Snow Day -- stay home. Link to archves at the bottom of the page. 1. Yes 2. Not that I know of as win2k CF supports OLEDB but there is no driver for mysql 3. You will have to add the datasources through the control pannel and then set the CF settings in the CF administrator, you can't add datasource in the administrator like you can for Access and MS-SQL, other than that it works great I mean I'm not a fan of CF anymore but I've got several sites that still run CF and Mysql with no problems at all. This is definately do-able. It has come up (see archives) before buut I don't recall anybody having problems other than setting the DSN. - 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
kill bad query
If I have a connection that executes a bad query shouldn't I ALWAYS be able to kill it with "kill connect num"? I have a query that keeps getting stuck, (who knows what it is doing) but that State is "statistics" I can't ever kill it without taking down the server.. - 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: kill bad query
Steve Ruby wrote: If I have a connection that executes a bad query shouldn't I ALWAYS be able to kill it with "kill connect num"? I have a query that keeps getting stuck, (who knows what it is doing) but that State is "statistics" I can't ever kill it without taking down the server.. PS.. this is using 3.23.33 on Linux.. I find that the query is very slow but will eventually complete, still, can I not kill a query that will lock tables for hours? - 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: Balanced mysql-connections
Kilian Looser wrote: Hi, i've the following setup: LoadBalancer - five Webserver with Apache and mysql - Database Now i got a problem. i've user's they insert records in the database. Now the insert is going throu, for example, webserver1. Now i'm doing a select throu webserver2 and i didn't get the information inserted by webserver1. i've to wait for about two or three seconds, till the tupel is present in the database. Is this a cache problem? any idea? thx Kilian If you are saying that you have 5 mysql server pointing to a single set of data files then this is not good mysql is not design to work that way.. the reason you are having this problem is that each of the fiver servers have an OS disk cache that has something in it different that what is actually in the file, mysql itself doesn't have a data cache, it expects the file system to do that. You really should have 5 replicas of the database and send all updates through one server and use all the others for reads. The scheme that you mention above (assuming NFS or something pointing to the single data files?) works fine but only in READ ONLY situations. In addition to the dirty read problem you see above you will probably corrupt yoru data in short order. - 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: synopsis of the problem (one line)
Jonathan Dugan wrote: your documentation is simply wrong. NONE OF THE USER PERMISSION materal makes sense. I am read ing here http://www.mysql.com/doc/D/e/Default_privileges.html and http://www.mysql.com/doc/A/d/Adding_users.html and I follow what is says: shell mysql -u root mysql ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO) whatever. any suggestions please send them to dugan@bowser I'm running Linux 2.2.17 rpm version of MySQL: MySQL-3.22.25-1.i386.rpm MySQL-client-3.22.25-1.i386.rpm The Online documentation represents the most current version of mysql as you are trying to use an old version you should look at the documentation distributed with the version you have... - 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: Hard Drive Space
It depends on your table type, check the manual, you will want myisamchk or isamchk if you are using 3.23.x you can do SQL REPAIR TABLE so long as your table is MyISAM. Dave Loisel wrote: I have filled my hard drive by inserting data into a table (15 gigs). The table was then corrupted. I have freed 1 gigs to repair the table and there is nothing else to delete. How can I repair my table? __ Dave Loisel DBA Wanted technologies [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 - 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: Problems with MySQLGUI 1.7 and ssh
"Todd A. Jacobs" wrote: I downloaded and compiled the latest version of MySQLGUI and am connecting to MySQL 3.22.32 on OpenBSD. When I try to tunnel MySQLGUI through the following ssh tunnel: ssh -x -g -L 3306:localhost:3306 mysql I get this error message from the MySQLGUI client: Can't connect to local MySQL server through \ socket '/tmp/mysql.sock' (111) What connection parameters are you using in MySQLGUI? You need to make sure you are specifying the PORT and HOST (as localhost) if you don't specify a port mysql doesn't use TCP/IP but unix sockets, your tunnel only works with TCP/IP - 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: Key Word Query
Dale Frohman wrote: Is there anyway to do a query and search for the most frequent word that appears in a particular field in a table? ie: ITEM DESCRIPTION Blue Dog Red Dog Yellow Cat I would want to spit out "Dog" as being the most reoccuring word. Thanks that's a little beyond the scope of sql.. you'd have to write your own stuff to make several queries and find the most used word.. - 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: date comparison?
Cindy wrote: SELECT Foster_Dogs.dog_name, Foster_Dogs.dog_status, Foster_Dogs.date_resolved, Foster_Dogs.adopting_family, Foster_Dogs.foster_paperwork, Foster_Dogs.dog_id, Adoption_Applications.firstname, Adoption_Applications.lastname FROM Foster_Dogs, Adoption_Applications WHERE dog_status = "Adopted" AND adopting_family = app_id AND (date_resolved DATE_SUB(NOW(), INTERVAL 6 MONTH)) bad line ORDER BY dog_name you can't compare time date fields directly like that, you need to convert them both to days or minutes or some common single number AND ( to_days(date_resolved) to_days(date_sub(now(),interval 6 month)) ) - 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: date comparison?
Cindy wrote: SELECT Foster_Dogs.dog_name, Foster_Dogs.dog_status, Foster_Dogs.date_resolved, Foster_Dogs.adopting_family, Foster_Dogs.foster_paperwork, Foster_Dogs.dog_id, Adoption_Applications.firstname, Adoption_Applications.lastname FROM Foster_Dogs, Adoption_Applications WHERE dog_status = "Adopted" AND adopting_family = app_id AND (date_resolved DATE_SUB(NOW(), INTERVAL 6 MONTH)) bad line ORDER BY dog_name Ignore my hasty and wrong response from moments ago. you should be fine if date_resolved is a datetime type column if it is only a date you will need to use date_format to convert your date_sub(now()) to a date, otherwise the later returns datetime and cannot be compared to just date. - 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 question to ask
His balance is a running balance... Joseph, you will have to keep the running balance in some thing that you write to get the query.. you can't do a running sum in SQL directly. You can only use the examples people provided to get the net per line. Joseph Bueno wrote: [EMAIL PROTECTED] a crit : Dear all, Sorry to ask question again, or maybe i am just a beginner. I wanna ask: If there are 2 records Name Income Out Data1 1000 700 Data2970500 If i want to have the output: Name Income Out Balance Data1 1000 700 300 Data2970 500770 How to do that? Thx Yours sincerely, Jason Chan Hi, Have you tried select income,out,income-out as balance from table ? Assuming 770=970-500 (strange math isn't it ;) Regards -- Joseph Bueno NetClub/Trader.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 - 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: auto_increment
John Tsangaris wrote: If I turn on auto increment and let it do it's thing and then later on delete a few of the entries (let's say 1, 2, 3) and the current high entry has an id of 45.. why does mysql continue with 46 instead of using up 1, 2, and 3 first? Is there a way to bypass this? John To avoid duplication of those keys in many cases. For example if you archived 1,2,3 and removed them from the table you may want to add them back in and you don't want their ID's to be taken. Or you may have references to 1,2,3 in other tables, if you delete them it would be wrong for the references to point to the new 1,2,3. Of course you should probably delete the references but if you didn't it would be confusing that you had pointers to the wrong lines. The point is that auto_increment generates UNIQUE values, and those values should be unique for the life of the table, not pending deletes. - 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: So, what do you do with a tarball anyways.
"Matthew P. Marino" wrote: So, what do you do with a "tarball" anyways. Since compiling the source is hopless( "sql_yacc.cc"), I'm looking for a binary. then download the binary.. Or at least do a better job of describing your problem... - 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: So, what do you do with a tarball anyways.
"Matthew P. Marino" wrote: If you go to http://www.mysql.com/downloads/mysql-3.23.html, you will see the binary(tarball) distributions. They don't end in .tar.gz but I supposed that was a technical oversight. Of course I feel like a (_?_) (i.e dumb a**) not being able to track down the right files. I blame HTML. I'm much more comfortable at an ftp command line, just weren't sure where to go. I just went there and they all end in .tar.gz http://www.mysql.com/Downloads/MySQL-3.23/mysql-3.23.33-unknown-freebsdelf4.2-i386.tar.gz - 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: SELECT * EXCEPT field1, field2
Jack Dempsey wrote: Hi all, I'm wondering if it's possible to do something like that select line in the subject...often i want to select * but not one or two fields...i found something about this in the to do list for mysql4.0, but i'm guessing that's not exactly what i'm thinking...is there an easy way to do this? thanks so much...hope this isn't to dumb of a question... Jack Dempsey No - 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: Can this query not be done in MySQL.
The problem is that the value returned for a column requested in a group query without an agregate function is undefined, There is NO associated link between some column that you didn't put in the function and one that does not appear in the group by statement.. In fact usually (in my databases including mysql this will result in an error). Look at it this way.. What if you had two rows that corresponded to the max, how would it know which one to pick.. What if you were taking the Average instead of the max, and it returned 1.5 which row is it supposed to give you? As you can see there are many examples that illustrate why you can't do that query. Mr. Van Engen did have solutions for you posted to the list, I will forward you his response. It isn't that mysql can't perform the query you want, it is that the query you have shown is not compatible with SQL itself. If it were me I would do it in two queries... Get the max and then get the City corresponding to the flight and the max, in your case you should never have two cities that corespond to the max, but sql is a general language and cannot make such an assumption in all cases. Gerald Clark also posted another suggestion, another alternative, but you're not going to be able to do this with the query you state and it isn't mysql to blame... You can blame mysql for not haveing subqueries though :) Richard Reina wrote: Steve, I never received Mr. Van Engen's response. I appreciate your response. However, my question remains unanswered. If you put "sequence" in the GROUP BY it does not give you the value that correspond to the MAXIMUM sequence. As a matter of fact I can't find any combination of values that you can put in the GROUP BY clause that will give you the values that correspond with MAX. What good is an aggregate function like MAX if it does not give you the value ( and only that ) that corresponds to that aggregate function. In this case shouldn't (MAX(sequence)) give you only those values that correspond to the maximum sequence for each flight which would be: +---+ |flight_no |sequence|City | | 127 | 2 |Boston | | 391 | 1 |Miami | +---+ The documentation in the documentation under GROUP BY functions (section 7.3.12) seems to advocate this syntax -- specifically with their example of querying orders by MAX(payments) -- although they don't show the results of their examples. If this cannot be done in MySQL will someone with authority on the subject -- perhaps Sasha or Monty -- simply say that MySQL cannot perform this type of query. If it can be done will someone demonstrate how it can be done in MySQL. I believe I can do it in other DBMSs with the following subselect: SELECT soo.flight_no, soo.sequence, soo.city FROM stop_offs soo /* That's "stop_offs outer" */ WHERE soo.sequence = (select max(soi.sequence) FROM stop_offs soi /* "stop_offs inner" */ where soi.flight_no = soo.flight_no ) however, it is my understanding that MySQL does not support subselects. I have once again included a copy of the table below. Once again, thank you for your attention in this matter. Here is the table: +--+ | STOP_0FFS | +--+ |FLIGHT_NO |CITY | SEQUENCE| |127 |Chicago |1| |127 |Boston |2| |391 |Miami|1| +--+ SELECT flight_no, MAX(sequence), city FROM stop_offs GROUP BY flight_no; gives the result: +---+ |flight_no |sequence|City | | 127 | 2 |Chicago| | 391 | 1 |Miami | +---+ However Chicago is the city of the first stop off (sequence 1) not the value that corresponds with a sequence of 2 which would be 'Boston'. The desired result is the following: +---+ |flight_no |sequence|City | | 127 | 2 |Boston | | 391 | 1 |Miami | +-------+ Steve Ruby wrote: Your question was answered already after you posted it on Saturday by Fred van Engen... Basicaly you should not be able to do a group by without perfoming an aggregate function (max, avg, sum, count) on columsn that do not appear on the "GROUP BY" clause. Richard Reina wrote: I posted this question to this list on Saturday. I realize that not everyone on the list knows whether this query is possible using MySQL. However, if I could please here back from someone who does know, I would appreciate it. I'm getting flack from informix ( and other DBMS ) users on my perl user list who are now doubting whether I can get it to work in MySQL. I ha
Re: Can this query not be done in MySQL.
Your question was answered already after you posted it on Saturday by Fred van Engen... Basicaly you should not be able to do a group by without perfoming an aggregate function (max, avg, sum, count) on columsn that do not appear on the "GROUP BY" clause. Richard Reina wrote: I posted this question to this list on Saturday. I realize that not everyone on the list knows whether this query is possible using MySQL. However, if I could please here back from someone who does know, I would appreciate it. I'm getting flack from informix ( and other DBMS ) users on my perl user list who are now doubting whether I can get it to work in MySQL. I have great confidence in MySQL and believe there must be a way to get the query to work. I have searched through my MySQL/mSQL O'Reilly book and in the user manual (specifically GROUP BY functions (section 7.3.12)). Can someone please help me out? - 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 CRM
Does anybody know of any commercial or opensource CRM systems that run with MySQL on Linux (and/or FreeBSD)? Mainly I need canned responses and ticket management and some other standard features. - 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: returning PID
PID is stored in a file in the datadir named hostname.pid [root@georgia data]# cat /usr/local/mysql/data/georgia.pid 19805 Teddy A Jasin wrote: Hi, wat command to type under linux to get the PID of the mysqld that is running? i tried : 'ps aux | grep mysqld' but this will list the mysqld information but ijust want it to show the PID thanks Teddy - 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: Help with extremely slow queries - PLEASE!
You may be looking too far for your problem... Your join_buffer and sort_buffer are probably a little large if you have man concurrent users. Must you use an old version of mysql? You should analyize or post EXPLAIN of your query so that you/we can see that it is properly indexed.. you should be able to do many joins on very large tables and have results that respond in fractions of seconds unless you are missing some indexes or are trying to do some LIKE '%value%'. if it is writing to temp that means it has to create temporary tables to complete your query, this can be the case if your are missing indexes and it is having to search all tables to complete your join. Neil Streeter wrote: Hi all, I've a bit of an issue with mysqld -- errr is it? Here's the configuration Dell PowerEdge 6300 - 1GB ram (actually 2GB but no bigmem support at the moment - I'll get to that) - RH linux 6.1 running 2.2.12-smp (needs updating, I know - but hard to find good 'down time') - 4 10gig UW-SCSI drives - in hardware raid 5 - AMI raid... Running: Apache -- 1.3.4 (Unix) W/ DBI and CGI.pm Mysql -- Ver 3.22.21 for pc-linux-gnu on i686 with options: wait_timeout=31536000 join_buffer=10M key_buffer=64M sort_buffer=8M table_cache=81 tmp_table_size=64M --big-tables - Here's the problem / questions --- This 'snuck' up on us as usage increased, we never noticed it before It seems that certian queries in mysql make the daemon write to /tmp table... This process takes a long, long, time and does not generate any errors in the log - actually, If you're patient enough, it will complete The processlist plainly shows that it's writing to /tmp while running top, and generating one of these queries, I notice that a mysql thread (or two) jumps to the top taking up to 50% (or as much as is available) of the CPU - and quite a bit of mem... More than it seems it should What causes a querry to be sent to a tmp table? Realisticly, it seems that this querry should be able to run in no more than 5 seconds But we're talking minutes, here It's just a join querry, and the tables are really not 'that big' It also seems that when one of these queries is running, quite a few other mysql processes become 'locked'... What might cause this? I've been doing quite a bit of reading on mysql and the linux kernel... I've found a couple of possible answers to my own question, but I'd really like to hear form others who may have experianced the same situation. One possible answer comes from the linux specific segment of the docs, and speaks of a: Version 2.2 kernel "feature" that penalizes a process for forking or cloning a child in an attempt to prevent a fork bomb attack. This will cause MySQL not to scale well as you increase the number of concurrent clients. On single CPU systems, we have seen this manifested in a very slow thread creation The other possiblity I found deals with Parameters in /proc/sys/vm/bdflush... I don't know a lot about these, so any help here is mucho appreciated It seems though, that modifying the ??nfract?? (is that right?) paramater may help (on systems with lots of RAM) - as disk IO will be buffered up to the limit specified??? thus causing 'slow' writes??? Am I on here, or way off??? Also, I'm considering VALinux 7.0.1 (modified redhat) which has built in bigmem support, and unbuffered diskIO (rawIO)... I would also immediatly upgrade to kernel 2.4.x as I understand that the "anti fork-bomb feature" is fixed in the 2.4 kernels... does anyone have any other suggestions, possible fixes (to current kernel)? or ideas? Please help, I've almost no hair left :-0 ns - 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: Queries
[EMAIL PROTECTED] wrote: Hello, When you create a query, how do you save it? Is there a special command to save a query? Thanks Mark Query is just text... if you are in unix under mysql monitor you can do \e on any line after you run the query (and before you type a new one) and it will open the editor you have configured, you can then save it from the editor to wherever you want. - 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: Finding Duplicates and Deleting
"Carsten H. Pedersen" wrote: Does anyone know how to find duplicates in a table and deleting them. I can't figure out how to structure the query. Linsen http://www.bitbybit.dk/mysqlfaq/faq.html#ch7_6_0 / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq I wouldn't recommend that solution for a large table, it will take forever and require enough disk space for a potentialy very large index.. not to mention it has to modify your existing index file since indexes are not in separate containers... Not to mention the fact that alter table must copy the entire table to another temp table and lock out writters.. If you have very small tables I suppose that is a reasonable solution. - 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
optimize table LOCK
If OPTIMIZE TABLE appears to be writting to a temporary file while it works (.TMM).. Why must it lock readers out of the original table 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
No Key on LIKE% (was: Performance issues.)
Quentin Bennett wrote: Hi, For an indexed column, the index is used if the start of the string is used: LIKE 'a string of text%' may use an index LIKE '%any old string%' will not, since the start of the string is unknown. The index will only be used if the server decides that it will be quicker than a full table scan. Have you got the results of 'explain select ' to see if your index is actually being used. Regards Quentin While we are (were) on the subject.. Any thoughts why like would not be using an index in this case? mysql explain select * from _data where datatime like '12:00:%'; +---+--+---+--+-+--+-++ | table | type | possible_keys | key | key_len | ref | rows| Extra | +---+--+---+--+-+--+-++ | _data | ALL | dataTime | NULL |NULL | NULL | 5751070 | where used | +---+--+---+--+-+--+-++ 1 row in set (0.06 sec) mysql explain select * from _data where datatime like '12%'; +---+--+---+--+-+--+-++ | table | type | possible_keys | key | key_len | ref | rows| Extra | +---+--+---+--+-+--+-++ | _data | ALL | dataTime | NULL |NULL | NULL | 5751070 | where used | +---+--+---+--+-+--+-++ 1 row in set (0.00 sec) There is a key on dataTime and there are only 94 unique values for datatime... So why in 5.7mil rows it doesn't use key? I have the same issue on a datadatetime column which has 191,000 unique values and it doesn't use the index either - 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 vs Access; you'd *think* the choice is obvious...
Quickling wrote: Hi, Question 1: --- We've got a server app that does a lot of 'small' database reads and writes. We were originally using MS Access via DAO (Jet Engine) and we wanted to tighten up DB performance, so we've written a general ODBC database wrapper object, but mainly just to connect to MySQL. I figured there'd be ODBC overhead, but its a lot worse than I imagined. I want to know: does it make sense that our original system, connecting via 'Jet-engine' to Access, is actually much faster than connecting to MySQL via ODBC? This seems to be what's happened. I'm wondering if it has to do with the overhead of connecting to a server-based database via a tcp socket (even on localhost) rather than the direct-to-disk Jet engine; maybe because we do so many small reads/updates it's actually faster with Access? Any thoughts? Is it worth my time to look into using MySQL directly instead of thru ODBC? I'm obviously working on Windows (NT), connecting at ODBC version 2.0 to MySQL server 3.23, using a database converted directly from Access to MySQL using the cool (but unstable) DBTools GUI, which kindly retained all keys and indexes (which have been reviewed for speed). If your tables are very small. I've found the same thing that you did, Access was actually faster on some small queries.. When your tables get bigger (and it didin't take much 100,000+ rows) then Access started to go down-hill quickly, occasional corruption, unable to repair, and very slow on many queries. Question 2: --- Is there any way in MySQL to compute, inline SQL, the difference between two datetime values? I couldn't find a function which could do anything but subtract intervals from datetimes... what I need is the interval! Many thanks in advance, Jesse UNIX_TIMESTAMP(DATE2) - UNIX_TIMESTAMP(DATE1) = seconds TO_DAYS(DATE2) - TO_DAYS(Date1) = days - 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: table too big to dump?
Brian Reichert wrote: On Wed, Feb 07, 2001 at 05:00:12PM -0500, Brian Reichert wrote: When I try to do a mysqldump on the localhost, I get an out-of-memory error: # mysqldump -uroot -proot customer audit_trail_271 mysqldump: Out of memory (Needed 8164 bytes) mysqldump: Got error: 2008: MySQL client run out of memory Sorry about a rely to myself; on a lark, I did some exploring, and the use of the '--opt' flag made the difference. What is that [not] doing, that changed the behavior of eveything? --opt (and -q) force the data to go directly to standard out. without either of those it goes to RAM first. --opt also does some other stuff you may or may not want, check the manual for details on that. - 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: Multiple AND on many-many-table.
Bob Hall wrote: I want to do a select like select id from test where id in (select id from test where value in (1,2,3) and id in (select id from test where value in (4,6,7) and id in (select id from test where value in (1,6,25) Even if MySQL supported subqueries, this query would always return the empty set. I'm really unsure about what you're trying to do, but I think the following comes close. SELECT id FROM test WHERE value IN (1, 2, 3, 4, 6, 7, 25); No the query I wrote would return the intersection of three lists of user ids where each list contained any users that had the values listed. Your query would return all user ID's who had any of the number listed which is not what I need... but 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: Select a range from 0-9 or A-z
"Ung, Seng" wrote: hi: Here is my SQL statement to select a range of number from 0-9 Is there a short cut to this? id like '1%' or id like '2%' or id like '3%' or id like '4%' or id like '5%' or id like '6%' or id like '7%' or id like '8%' or id like '9%' or id like '0%' see REGEXP in the manual you want id regexp '^[0-9]' - 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: Newbit: ADO and MySQL/MyODBC
Henrik Lebtien Mohr wrote:
Hi there
I use ASP and ADO 2.5 to access the data in my MySQL RDBMS.
I get an error "Multiple-step error" when I try to update a recordset like
the following:
with rs
.fields("date") = null 'timestamp datatype
.fields("nOnline") = .fields("nOnline") + 1 'int datatype
.update
end with
Any ideas??
Kind regards,
Henrik Mohr - Denmark
There is no server-side cursor handling on mysql so unless you
are using something other than MyODBC to emulate cursors you must
treat all recordsets as static.
-
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: Multiple AND on many-many-table.
Steve Ruby wrote: Does anybody know what is the fastest way to run a large multiple AND type query on a many-many table and the least memory hungry. For example if I have create table test (id int, value int); which has many-to-many on id, and value I want to do a select like select id from test where id in (select id from test where value in (1,2,3) and id in (select id from test where value in (4,6,7) and id in (select id from test where value in (1,6,25) So I must return users that qualify for at least one value in several lists, obviously this is somewhat easy with subselects... Without subselects I'm doing this, can anybody tell me of a better way? create table temp (id int not null, key (id)) type=heap; insert into temp select id from test where value in (1,2,3); insert into temp select id form test where value in (4,6,7); insert inot temp select id from test where value in (1,6,25); select id from temp, count(*) as numlines from temp group by id having numlines = 3; drop table temp; there will be anywhere between 1 and 10 of the above lists for this query so then I return the qulified useres by doing this on the heap table where the having part is equal to the number of lists that were checked. Obviously the temp table can get very large depending the query, currently this seems pretty fast, but I'm concerned that it will blow up on me as I get more id values anybody know a better way? A self join comes to mind but if there are many users and say 10 questions then the length of the self join result before the WHERE is userlines^10 which can be a very big number. Not knowing how HAVING is optimized i've decided for now to go with one table per question (each of the ANDs) and do a join (max of 10way) against a temp table for each question... still doesn't sound like the best route but it will have the same amount of data in it as if I did one big table and seems like it would be quicker to update the keys on the smaller tables better ideas appreciated. - 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: query over CD-ROMs?
Kuokai Shyu wrote: Hi, Can MySQL do the queries on the databases stored in the CD-ROM? For example, when switching data CD-ROMs, is it necessary to shutdown mysql server, and restarting server after mounting another CD-ROM? Thanks in advance. Sincerely, Kuokai I assume if you issue "flush tables;" or "mysqladmin flush-tables" between CD's you should be okay as long as nothing tries to run a query while you have no CD mounted. - 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: Default users privileges
Jang wrote: Hello, I'm new in this MySQL list. I only want to know what are the default privileges that are required, so that a normal user (wihtout any special priv.) can acces only to his DB, create tables, manage, insert, ..., his tables. I have try some differents config, but never have reach the right privs. Please, detail me also how and where (/MySQL tables) to create this default user, and the strict needed privs... Thanks very much. (Sorry for my bad english, i'm french, and it's difficult to find GOOD french docum. about MySQL) Best regards Jang create database user_database grant all privileges on user_database.* to user@host identified by 'password' See GRANT syntax in manual for more information. There should be no need to directly modify the mysql tables. - 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: Connectivity to MySQL DB
Sanjeev Kabra wrote: Hi, I installed MyODBC and even configured it. MySQL db running on Solaris is running under the root user. While defining the DSN using MyODBC in the username I tried putting root user and the password for the same but I keep getting error "can't connect to MySQL Server". The instance for MySQL is running. Is there any way to test the login process. I can logon MySQL db on using machine. Basically we are planning to user Actualte report writing tool and MySQL db. If you know any body using combination please let me know. Thanks. Sanjeev You must grant privileges to users on remote machines. See the manual for sintax on the GRANT command.. For example if you wanted users on a machine with a specific IP to have all access to a specific database you could do mysql grant all privileges on table.* to [EMAIL PROTECTED] identified by 'password' if the server is on a "trusted" network and you just want to allow access from any machine using a specific account you would o mysql grant all privileges on *.* to username@% identified by 'password' bascialy when you install mysql it is only set up to alllow access using user=root from the "localhost" - 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: Large text searches
Geoff Coffey wrote: I am new here... I am in the process of migrating a site built with FileMaker Pro to MySQL and PHP mostly because filemaker's web capabilities are too limiting. In one situation, we have a database of about 20,000 rows that contains two text columns. One is the full text of a resume and the other is the full text of a cover letter. I'm trying to decide how to enable searching from a web form. I've not actually tried it, but I suspect using "LIKE" will be lousy. In Sybase it certainly would be. I found a thread on this list discussing just this, and the suggestion was made that I create a few tables to store keywords and map them to record ids etc. My problem with this is it makes it impossible to do searches on a phrase. I think the users would often search for "Microsoft Word" for instance, and it would find every candidate that mentions "Microsoft" and "Word", but not necessarily together. But it would be acceptable to limit it to word searches if need be (filemaker doesn't support this now in a fast way anyway). Are there any other options than this? You can support phrases, using the keyword method you mention by first generating a list of all docs that have your words using they keyword method, you can then use LIKE '%Microsoft Word%' against the narrowed list of documents. - 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: duplicating rows
Aaron Williams wrote: Hello list, After searching the documentation on the site, as well as the archives, I have yet to find the answer to this question. If I have overlooked it somewhere, just point me in the right direction. The question: I have two tables, set up exactly the same, except the names of the tables are different. Table 1 is called: active_users Table 2 is called: deleted_users When "joe_smith" wishes to be deleted, I want to remove his entry from the active_users table, and place it into the delete_users table, with exactly the same information. Something like: replicate * from active_users where user_name = "joe_smith" into table deleted_users; All entries in Table 1 are unique by user_name (joe_smith in this case), but such is not the case in the deleted_users (there is more than one joe_smith in the world, if one cancels, we need to allow another one to signup), which is why I can't just add an extra column to tell if the user is active or not. I could do a select from active_users, store the information, then insert into deleted_users, then delete from active_users, but that is three steps when it seems there should be an easier (more efficient) way. Any ideas or links to archives or documentation is appreciated. Can be done in two steps, but not in one: insert into deleted_users select * from active_where where username='joe'; delete form active_users where username='joe'; what you mention about usernames not being unique in the deleted_users table is not a problem as long as you don't have a primary or unique key on username.. but you should make some primary key in the delete_users table, an auto_increment or something for maintenance purposes. - 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: Can I have two (or more) different SQL-databases on the same server?
SED wrote: Hi, Can I have two (or more) different SQL-databases running at the same time on the same server (or same computer)? Does it varies between the systems? And if I can, are they all available to PHP at the same time? Regards, Sumarlidi Einar Dadason See "create database" in the manual (search for it) you can have as many databases as youant (within reason) on the same server. Not only can you access them all from php but you can run queries that access multiple databases in the same query.. See the manual but basicaly you can do select * from db1.tablea, db2.tableb blah - 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: MyODBC - but how?
No, mysql cannot connect to other database servers, it can't even connect to other mysql servers except through replication. John Halladay wrote: Connecting to the tables in MySQL works fine. For example I can connect to the MySQL tables using MS Access. I would like to know if it is possible to connect to the tables of another database from MySQL. So instead of going from MS Access to MySQL I would go from MySQL to an AS400. Do I somehow use the command CONNECT [] to change the database? -Original Message- From: Scott Baker To: John Halladay; MySQL List (E-mail) Sent: 1/31/2001 2:09 PM Subject: Re: MyODBC - but how? You just create a DSN in the control panel, and tell your ODBC compliant software to use that data source. At 11:46 AM 1/31/2001 -0700, John Halladay wrote: I have just installed MyODBC on my machine (Windows ME) and everything is working fine, but I've looked through the manual and I can't find anything showing commands on how to connect to other databases. Can someone point me in the right direction? Thanks. John Halladay - Scott Baker - Webster Internet - Network Technician 503.266.8253 - [EMAIL PROTECTED] "Children today are tyrants. They contradict their parents, gobble their food, and tyrannize their teachers." - Socrates - 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: Backup?
James Ervin wrote: Can you restore from a backup of the mysql data directory or do you need to dump the database in order to be able to restore? I have seen some traffic on the list that suggests that a dump is the way to go. I backup the data directory each night, but I would hate to find out that that is not the way to go if/when things go bad. James Ervin Restore from a copy of the directory is the fastest way to recover (short of replication). Just make sure that when you copy the files you do some intellegent copying. shutdown the server before copying OR use mysqlhotcopy OR write your own scheme to lock the flush and lock the tabkes you need before copying. - 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: Backup?
Scott Baker wrote: That's the way that I do. I setup my server to shutdown the MySQL process at 3am, zip up my var directory, and then restart the process. Can you copy the data with the server running? If you are shutting down the server, why don't you just copy the files out to the backup and then restart? Why would you want to copy them back in? Save the disk IO and don't remove them in the first place, it would be faster and you wouldn't have to worry about Yes, you can copy them back in but you should have locks on them through some thread so that something doesn't try to write to them.. still I'm not sure I see the need since you are backing up the data and should not be removing it. - 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: Very slow to connect
Leonardo Dias wrote: I bet your webserver and database server are seperate machine. Make sure the hostname of web and db are in the /etc/hosts on both machine, it's very slow to use DNS to resolve everything That doesn't matter. We use IP to connect, not hostnames. But if you use IP the mysql server may still try to resolve the host name to for access checks. So make sure you have the IP in the /etc/hosts of the server for the machine you are connecting from . - 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: ORDER BY problem and possibly others..
"J.M. Roth" wrote: Hello, I just installed the newest MySQL (3.23.32) with PHP 4.0.4pl1 (shared module) on an Apache 1.3.12 (Linux). Some SQL syntaxes that worked before don't anymore. E.g.: $query = "SELECT * FROM $userstable ORDER BY when DESC LIMIT 0, 3"; doesn't work: Warning: Supplied argument is not a valid MySQL result resource in /home/FV/aal/public_html/frame1.php3 on line 168 empty set http://www.mysql.com/doc/R/e/Reserved_words.html http://www.mysql.com/doc/N/e/News-3.23.2.html ... sorry, somtimes adding new features ads reserved words - 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: recovery help
Elekes Attila wrote: Steve Ruby wrote: Elekes Attila wrote: Hi! The mysql server (version 3.22.32-4) has hanged up, and the restarting causes a part of the *.ISM and *.ISD files are gone. Unfortuanetly, I have not got a backup from these files :( Is there any method to recover the mysql tables from the remaining *.MYD, *MYI and *.frm files ? Thanks for ANY help!!! Attila I may have confused things here.. sorry... If you have version 3.22.32 you Cannot use the MYI and MYD files those are the MyISAM tables, the ISM and ISD files are ISAM tables which were the starndard format for pre 3.23 versions... If you are running 3.22.32 how did you get MYI and MYD files? You should have only one or the other depending on the type fo the tables and with 3.22.x you must have only the ISM/ISD files. Can you explain what happend? If you converted the tables from ISAM to MyISAM format the MYD .IS* filew will go away, but you will be required to use 3.23.x version with the .MY* files - 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: automation question: How do I copy the data from one table to another table with a time stamp every night?
Chuck Barnett wrote: How do I copy the data from one table to another table with a time stamp every night? example: table A contains x,y,z. at midnight, I want to copy table A's contents to table B (B has same fields, just adds a date field) and reset x,y,z to zero. what do you mean by "reset to zero" If you want to copy the files from A to B you can just do insert into B select x,y,z,now() from A; delete form a; if your date field in B is a TIMESTAMP type you can avoid the now() part and do inesrt into B (x,y,z) select x,y,z from A; delete from a; See the manual about the insert statement for more info. - 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: automation question: How do I copy the data from one table to another table with a time stamp every night?
Chuck Barnett wrote: Thanks for replying. I know the SQL commands, I want to know how to automate the whole sequence. I've always written php pages that do the calls. I want to write a script to do it on the server as a cron job or something. Ahhh, pardon my confusion, since this the MySQL list I presumed you were asking a mysql question. Your question is a cron/unix/php question not a mysql question.. there are many options one would be to wite a PHP script that does exactly what you need it to do when you "hit" it then simply put a cron job in that looks like lynx url to my php script If you don't know cron you'll have to read the man crontab to understand the format of the crontab file or cron on your system, if you are using redhat you can simply put an executeable script with the above line in it in the /etc/cron.X directory where X is the interval you want it to run, daily for example. - 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-Front
I thought I should send this to the regular mysql list as there are many mysql users who run DB on unix but must use windows for a workstation and remote administration. This is an excellent front-end.. It has the best UI of any of the other windows front-ends I've seen, it is very fast, this is a good program, this is the first I've heard of it but I'll be investigating it's further... Steve - Original Message - From: "Ansgar Becker" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, January 23, 2001 7:49 AM Subject: MySQL-Front Hi everybody, I'd like to add MySQL-Front (look at http://my.anse.de/ for download) to the list of downloads at mysql.com. On the page http://www.mysql.com/downloads/contrib.html you can find any clients for MySQL. Since I released version 1.16 of MySQL-Front, it is relative stable and good enough to be added to this list (I hope so). What do you think? Greetings, Ansgar Becker [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: mySQL vs Oracle
Ann Ricchiazzi wrote: Dear mySQL Users, I am trying to choose between mySQL and Oracle for a Linux server. My specific questions are: 1) Will mySQL handle 3000 hits/day well? 2) Does mySQL handle multi-media file formats? For example, if I want to store audio clips, or Flash movie clips, or PowerPoint presentations, can I do so? We make the decision this decision at 3:00 today. Your advice will be most helpful. Thanks, Ann 1) is VERY dependant on how many of the hits access the database how many read/write querires.. etc etc.. To answer the question of load you should try to get it down to queries/minute at least.. It also depends on hardware, optimization of queries, etc etc. 2) Yes, you can put anything you want into a mysql database, mysql doesn't know the difference bewteen Falsh and PowerPoint but it doesn't care if you put them in there. - 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: CHMOD CHOWN broke MySQL operations
GATOR-root wrote: Description: We had an NFS incident and used CHMOD and CHOWN to repair it. Shortly there after, we noticed that our MySql under WebRT did not work. We presume that we corrupted the ownership and permissions of files and programs within MySQL. How-To-Repeat: use command CHOWN and CHMOD in a global way Fix: We expected to find a script that would repair this sort of thing. Alternatively, we expected "make fix_perms" or similar. Either would read the current environment and ferret out all of the files and folders, settings ownership and permissions appropriately. They could also report what they found in a way that instructs the operator about how things ought to be configured. We expected to find something about this in the 500+ page PDF manual but we did not. This is more of a unix administration issue than a myslq bug, isn't it? The user mysql is running under must have permission to read write (and list for directories under the datadir.. Simple as that. what could a script do for you that chmod and chown can't? There is no variation on a per table or per file basis, all database user permisions are handled internally. if your mysql user is 'mysql' and you don't want any body but the database messing with the files you can do chown -R mysql datadir chgrp -R root datadir chmod 770 `find datadir -type d` chmod 660 `find datadir -type f` This assumes you dont' have permission problems on any of your mysql executeables, and that you didn't mess up the permissions on the socket file or anything but the datafiles. - 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: [PHP] Database Connections - permanent or something else?
PHP handles persistent connections by leaving them open for some other identical connection request. The next request will check for a free connection. see http://www.php.net/manual/en/features.persistent-connections.php In other words, you don't need to worry about when they are closed php will handle that for you mOrP wrote: There's one thing, that I don't understand about permanent connections. When will they be closed? I could use a logout-page, but there is no garanty for the use of it. Any explanation would be appreciated. mOrP -Original Message- From: Ignacio Vazquez-Abrams [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 16, 2001 6:17 PM To: Sam Cc: '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]' Subject: Re: [PHP] Database Connections - permanent or something else? On Tue, 16 Jan 2001, Sam wrote: Hi, I have several web pages that are built with php and mySQL. I use a new connection for each script. Should I be using a permanent connection? Or is there a better way around this? Regards, Sam Rose Persistent connections are more efficient in that they don't need to open the connection each time. Unless you have an overwhelming need to limit concurrent connections to your MySQL server, you should probably be using persistent connections. -- Ignacio Vazquez-Abrams [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-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 - 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: Index ? Key ?
Martin Thoma wrote: Hello ! I'm a database-newbee. What I need is a column that makes the record unique. The content should be generated autoamtically and it should be able to get a lot of records (BIGINT would be nice). But I don't know if I should use PRIMERY KEY, KEY, INDEX or UNIQUE. Perhaps some could tell me the difference and what would be best for my purpose. Thanks a lot ! Martin You shold have atleast one column (or combination) that is unique to each row in your table, this would be the PRIMARY KEY. If you need the database to enforce the uniqueness of other columns (or combinations) you would use a UNIQUE KEY. KEY and INDEX are synonyms. That work? If you want it generated automaticaly what you want is an auto_increment column something like create table blah (id bigint unsigned not null auto_increment, rest of table columns) would hold a VERY large number that would increase for each entry. probably an 'int unsigned' is big enough for most things. - 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
