Re: Got error 124 from table handler.
Is there a list off MySQL error-codes avalible? Something like Oracle's oerr. program? perror We are having trouble with one of our MySQL databases. The error is: Got error 124 from table handler. chris@entropy:~$ perror 124 Error code 124: Wrong medium type 124 = Wrong index given to function - 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: Tuning my production MySQL server (EMERGENCY)
So i issue the following commands: c:\ cd mysql\bin start the serverc:\mysqld That would start the server. set the variablec:\mysqld -O back_log=200 --help That doesn't set the variable. It just shows what the variables would be like if you had started mysqld with that parameter. To start with a back_log of 200, you must do: c:\mysqld -O back_log 200 when mysqld is not running, then run mysqladmin variables to see if it was updated. -- Chris Bolt [EMAIL PROTECTED] http://www.bolt.cx/ - 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: seeking partner
Of course, the SAME spam we saw before appears again. No surprise. Does anyone still believe this will go away by itself? It won't - it will only get worse - unless the list administrators actually *do* something about it! The filters DO NOT WORK. Is there any reason why the list can't just be set to require that posters are subscribed? database,sql,query,table - 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 Problem in Slackware 8.0
when as root I give command /usr/bin/safe_mysqld It gives me following messages: Starting mysqld daemon with databases from /var/lib/mysql and on the next line it says mysqld ended. What does /var/lib/mysql/`hostname`.err say? - 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: Updating a field with the result of a select
Is it possible to update a field with the result of a select. For example: Try: SELECT @newbalance := ordr_order.total_cost - sum(ordr_payment.amount) FROM ordr_order LEFT JOIN ordr_payment on ordr_payment.ordr_order_id = ordr_order.id WHERE ordr_order.id = 1; UPDATE ordr_order SET balance = @newbalance WHERE id = 1; UPDATE ordr_order SET balance = ('SELECT ordr_order.total_cost - sum(ordr_payment.amount) FROM ordr_order LEFT JOIN ordr_payment on ordr_payment.ordr_order_id = ordr_order.id WHERE ordr_order.id = 1') WHERE id = 1 This doesn't work because it's setting balance to the text of the query, not the result of the query itself. Since balance is probably not a string type, it gets converted to a number which probably ends up making it zero. If MySQL supported subselects it might work except without the single quotes around the query. - 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_format
#2 All I have read on the web/books suggests that this is what to do $result = mysql_query (SELECT title, description, url, author, date_format(datefield, %M %D %Y') FROM documents); This just leaves out all dates. You may want to try: $result = mysql_query(SELECT title, description, url, author, date_format(datefield, '%M %D %Y') AS datefield FROM documents); You can also do $result = mysql_query(SELECT title, description, url, author, unix_timestamp(datefield) AS datefield FROM documents); then use php's date() function. - 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: Syntax error with a JOIN
Here's the simplified query: SELECT members.name, group.id, group.name FROM members LEFT JOIN members AS group ON group.id = members.group_id WHERE members.id = 6 And here's the error I get: You have an error in your SQL syntax near 'group ON group.id = members.group_id WHERE members.id = 6' at line 3 group is a reserved word. Use a different alias for the table. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Most Performance, Opinions ?
regarding mysql_pconnect() we haven't tried that, but the nature of the site suggests it would need like 500 apache deamons connected all the time. And that's a lot of sort buffers and stuff beeing allocated for each one of those ? Not if you disable keepalive requests, which I do on most web servers with heavy PHP and MySQL usage. It doesn't impact performance on the client side much since we keep our images on another server which can have keepalives enabled. You said you had messageboards, guestbooks, etc. Do any of these scripts use something like... SELECT table1.stuff, table2.stuff, table3.stuff FROM table1, table2, table3 WHERE (required constraints for joins) AND table1.messageboardid = '#' ORDER BY table1.timestamp DESC LIMIT 5; ? On large tables, this will lock the table for a long time since MySQL can't follow indices in reverse order, so it ends up having to do a full table scan. I have found that breaking it into two separate queries has improved performance considerably: SELECT id FROM table1 WHERE messageboardid = '#' ORDER BY timestamp DESC LIMIT 5; SELECT table1.stuff, table2.stuff, table3.stuff FROM table1, table2, table3 WHERE (required constraints for joins) AND table1.id IN (ids taken from previous query, separated by commas); This reduces the amount of time MySQL will lock the table, since with the first query it will do the full table scan on all 3 tables involved in the join and will keep all 3 tables locked for that time. I have seen times where the first method with one query takes 10 to 15 seconds to execute, while the second method with two separate queries both take 0.00 sec according to the MySQL command line client. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: UPDATE command, adding to exisiting record
I am looking to update a record in a table, but take the exsisting value and adding the new value to it... I.e. Exsisting record = 150, new record being inputed = 250 for a total of 400 to be entered into the database. UPDATE table SET row = row + 250 WHERE id = #; - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Fresh Install Already Contains Password--What is it?
Our brand new, fresh install of 3.23.46 won't let us set the password for the root user because one seems to already be set. Even after wiping the ...mysql/data/mysql directory clean and rerunning mysql_install_db, we still get an Enter password: prompt when attempting to set the root user password for the first time. mysqladmin does not prompt for a password if it needs it, you must give it the -p option to make it prompt for a password. If it really needed a password: $ mysqladmin -u root variables would show an access denied error, it wouldn't prompt for a password. So drop the -p option when you try setting the password. - 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 auto_increment wrap?
Only with ISAM tables which reuse deleted auto_increment numbers. MyISAM tables will give you Duplicate entry errors if you hit the maximum. I hadn't realized this and tested it out in MySQL 3.23.43, but in my tests ISAM tables also returned a Duplicate entry error when I hit the max. Was this feature eliminated? It must have been. I get the same behaviour, but the manual mentions that ISAM tables used to reuse numbers: ...This will make AUTO_INCREMENT columns faster (at least 10 %) and old numbers will not be reused as with the old ISAM. Note that when an AUTO_INCREMENT is defined on the end of a multi-part-key the old behavior is still present. from http://www.mysql.com/doc/M/y/MyISAM.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: Newsgroups
It puts the messages in a tree and makes viewing pertinent threads a breeze, My mail client puts messages in a tree and makes viewing pertinent threads a breeze. And even with the archives: http://marc.theaimsgroup.com/?t=10043869449w=2r=1 Doesn't look hard to me... database,sql,query,table,damn,this,thing,is,lame - 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 auto_increment wrap?
quickie: can auto_increment wrap? Only with ISAM tables which reuse deleted auto_increment numbers. MyISAM tables will give you Duplicate entry errors if you hit the maximum. - 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 Query in PHP
I am a newbie at this and I cannot find the damn answer to it! I want to display the table in an html format with PHP but I cannot get it to work! With MS SQL Server it was sooo easy! With PHP and mySQL it's a pain in the butt! I cannot find any documentation on this and I order 3 books on this stuff, but they won't be here till at least Monday. ? $dblink = mysql_pconnect(localhost, username, password); mysql_select_db(database); echo(table\n); $res = mysql_query(SELECT * FROM table); while ($row = mysql_fetch_array($res, MYSQL_ASSOC)) { echo(tr\n); foreach ($row as $col) { echo(\ttd$col/td\n); } echo(/tr\n); } echo(/table\n); ? http://www.php.net/manual/en/html/ref.mysql.html Doesn't seem so hard to me... - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Error 127 from table handler.
I'm using 3.22.27 and am getting an error when I submit queries. I do a select * from table where... And it gives me: ERROR 1030: Got error 127 from table handler chris@entropy:~$ perror 127 Error code 127: Unknown error 127 127 = Record-file is crashed Run isamchk -r on the table. - 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: Length limit of 500 on primary keys?
Is there a way to raise this limit? We have some tables with columns that are VARCHAR(200), and need to make a primary key based on combinations of these columns (in some cases, upto 4 columns), and MySQL complains for all of these table definitions that Specified key was too long. Max key length is 500. Why do you need to index the entire column? You can just index a prefix of the column, like the first 125 bytes of each column. - 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: binding to more than one ip address.
I have been trying to get mysqld to run under 2 ip addresses (localhost and an external ip address). Using the bind-address option twice doesnt seem to work, nor does lisiting 2 ip addresses in the option. I hope mysqld has this functionality to be able to run under certain ip addresses without binding to EVERY ip address, and not just ONE ip address. Make sure you don't have skip-locking and run 2 mysqlds, 1 for each 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: Mzximum character count
I would like to find out what record in the table has the largest amount of characters. This way I could possibly decrease the varchar size (currently 100) to speed up the queries. My table is as below. CREATE TABLE competitor ( CompetitorsName varchar(35) not null, Website varchar(100), ID int auto_increment not null primary key ) SELECT MAX(LENGTH(Website)) FROM competitor; - 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 getting backlogged
Have you also increased your master maxfiles as well? If not, you're not getting the number of file handles you expect you are. I'd also consider upgrading to 4.3, as it's much faster than 3.x. Try this -- sysctl -w kern.maxfiles=16424 kern.maxfiles is already the same as .maxfilesperproc sql,query,database,table,select,stupid,spam,filter - 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: starting problems. . .
I am currently using Slackware 8 (linux) and I have some problems. There is mysql package in the slackware installation, which I installed it but never manage to run it successfully. Everytime I type the following safe_mysqld and I get the following message: Starting mysqld daemon with databases from /var/lib/mysql 010912 15:13:45 mysqld ended which means that for a strange reason the server doesn't starts. Do you have any idea what might be the problem ? I also dowload your docs from the site but couldn't manage to solve it out. Look at the .err file in /var/lib/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
MySQL getting backlogged
Right now MySQL is getting really backlogged but I have no idea why. There are 169 concurrently running queries, and 153 of them are in show the status as Opening tables. A processlist looks something like this (formatted by Status, Seconds query has been running, and query): Opening tables 15 SELECT users.username, user... Opening tables 15 SELECT users.username, user... Opening tables 15 SELECT users.username, user... Opening tables 13 SELECT users.username, da_i... Opening tables 13 SELECT users.username, user... Opening tables 13 SELECT users.username, user... Opening tables 13 SELECT users.username, user... Opening tables 13 SELECT users.username, user... Opening tables 9SELECT users.username, user... Opening tables 8SELECT users.username, user... Opening tables 8SELECT users.username, user... Opening tables 8SELECT users.username, user... Opening tables 8SELECT users.username, user... Opening tables 8SELECT users.username, user... The status line from the mysql client looks like this: Threads: 190 Questions: 9272 Slow queries: 311 Opens: 868 Flush tables: 1 Open tables: 862 Queries per second avg: 31.753 Threads: 204 Questions: 11381 Slow queries: 311 Opens: 871 Flush tables: 1 Open tables: 865 Queries per second avg: 30.349 What I don't get is why all those queries are at Opening tables. My table_cache is currently set to 2048. It's a FreeBSD 3.4-RELEASE box. $ sysctl kern.maxfilesperproc kern.maxfilesperproc: 16424 I just reloaded the processlist and now half of the queries are in closing tables state (with running times from 16 to 48 seconds) and the other half are in Opening tables state, with running times all at 1 second. Why are threads closing tables if the table cache isn't even full? - 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: 1030: Got error 127 from table handler
Hello, I was working with Mysql an nerver had any problem, the suddenly from one moment to the other, some tables didn't work correctly any more. Any select command on them returns me : 1030: Got error 127 from table handler. But if I do a select command on a other table with a join on one of those tables everything works fine. chris@phantasm:~$ perror 127 Error code 127: Unknown error: 127 127 = Record-file is crashed Try running myisamchk on the table, or REPAIR TABLE tblname with 3.23 - 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: 1030: Got error 127 from table handler
Thankx for reply. But if I use Table Repair it says : The handler for te table doesn't support Check/Repair. What does SHOW TABLE STATUS LIKE 'tablename'; say for the Type column? - 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: search with wildcards
I need to search a mysql database using wildcards. I know about the * and the _ wildcards (any number of any character and 1 character of any kind, respectively) but I need to match any number, any letter and thing like that. An SQL book claimed it could be done using brackets and ranges like [1-9], but I can't get that to work. Does anyone have any ideas how I can solve my little problem? Regular expressions: http://www.mysql.com/doc/S/t/String_comparison_functions.html http://www.mysql.com/doc/P/a/Pattern_matching.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: SQL question
SQL question which I am new to. How would I count the number of times a specific entry is in field. For instance I have a table that has the following two fields numberhostname 1 10.1.1.1 2 10.1.1.2 3 10.1.1.3 4 10.1.1.1 and I want the query to return something like the following 2 10.1.1.1 1 10.1.1.2 1 10.1.1.3 SELECT count(*) AS count, hostname FROM yourtable GROUP BY hostname - 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: Way way OT
I am looking build my own E-mail Server for my Domain and would like some pointers Is there an E-mail Server that uses MySQL Exim, Postfix, and Sendmail can all use MySQL to varying degrees. There are probably others. qmail; http://cr.yp.to/qmail.html, http://www.lifewithqmail.org/ - 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: newbie password issue
now i changed the root password just fine but now i have to enter it every time i run mysqladmin even mysqladmin version reqires a -p tell me there's a simpler way http://www.mysql.com/doc/C/o/Connecting.html * You can specify connection parameters in the [client] section of the `.my.cnf' configuration file in your home directory. The relevant section of the file might look like this: [client] host=host_name user=user_name password=your_pass - 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: Reply-to and this list
A quick question: How come the mailing list software doesn't add a reply-to header? Is this intentional to keep replies off the mysql list, and if so why? http://www.unicom.com/pw/reply-to-harmful.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: Primary Key pairs and auto-incrementing
I want to create a table with two Primary Keys. The first key is a category field, and the second is auto-incremented. Example: Key 1=Chapter name, Key 2 =section number. For example, (Chapter1, 1) (Chapter1, 2) (Chapter1, 3) then with a new Cheaper, I want to restart the auto-incremented field back to 1 (Chapter2, 1) (Chapter2, 2). How do I get the auto-incrementer to restart with each new chapter? Try this: mysql CREATE TABLE test (chapter INT NOT NULL, section INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (chapter, section)); Query OK, 0 rows affected (0.00 sec) mysql INSERT INTO test (chapter) VALUES (1); Query OK, 1 row affected (0.00 sec) mysql INSERT INTO test (chapter) VALUES (2); Query OK, 1 row affected (0.00 sec) mysql INSERT INTO test (chapter) VALUES (2); Query OK, 1 row affected (0.00 sec) mysql SELECT * FROM test; +-+-+ | chapter | section | +-+-+ | 1 | 1 | | 2 | 1 | | 2 | 2 | +-+-+ 3 rows in set (0.00 sec) You can't have two primary keys, but you can have a single primary key on two columns. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Error 12 from table handler
I'am having this error code (Error 12 from table handler) when i am trying to create an index on a table that has about 20 records (it's not a big table)... What does this error code means?. chris@illusion:~$ perror 12 Error code 12: Cannot allocate memory chris@illusion:~$ - 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: Newbie questions
And is there some way to download the previous posts to this list? I found one list admin function that will download up to 100 posts concatenated into one email, but I'd like to be able to d/l them individually, with their own subject line and threading info. I just think it would be easier to review and catch up. http://marc.theaimsgroup.com/?l=mysqlr=1w=2 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Error 127 from table handler
Go error 127 from table handler.. fixed itself in about 10 minutes.. don't know what this is or why it happened.. anyone know? It happened while doing an update SQL command to a table. I'm not even sure what it means.. can't find any error codes. Try running perror 127 - 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 with UPDATE statement
update cdrequest set albumfill = hot shots II, releasedatefill = 20010708 where requstID = 2; i get: You have an error in your SQL syntax near '\hot shots II\, releasedatefill = 20010708 where requstID = 2' at line 1 I can't see any reason for the error. Your web based interface is mangling your query. part 2: when I flip to properties for the cdrequest just to make sure i'm spelling the columns corectly and cut and paste the exact same query on that page IT WORKS Why would it work on that page but not the other. both are supposed to be places where you can enter a normal sql query.. Because your web based interface is mangling your query. Try using the mysql command line client. It also doesn't work in my PHP script which is where I really want it to work. Paste source. - 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't find my my.cnf file -- not there by default?
I'm still testing out MySQL and I'm trying to find the my.cnf file referred to at http://www.mysql.com/doc/O/p/Option_files.html ... but none of these files seem to exist on my machine. ... MySQL came pre-installed on that machine. Is it possible (or, indeed, normal) that the MySQL installation process didn't create a my.cnf file? It is normal. (In that case I'd recommending adding that info to http://www.mysql.com/doc/O/p/Option_files.html , if the author of those pages is on the list. I'll submit a comment there anyway based on what the list replies.) That's what the commenting feature is there for :-) - 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: connecting by knowing someone's scrambled password?
How is that possible? Even if you do know someone's scrambled password, when you connect to the MySQL server pretending to be that user, it will ask you for their non-scrambled password. After you type it in, the server will scramble it and check that the scrambled value matches the scrambled value stored in the database -- but you can't intercept that part of the process and insert the known scrambled password to be checked. It's called bruteforcing... knowing the scrambled password, you can encrypt every possible password and compare it to the scrambled password to find the original password. Either that or the scrambled password is encrypted by the client then sent for comparison, but I doubt 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: programmatically getting field list from a table
Is there any way -- using either MySQL directly, or (ideally) using the DBI perl module -- to get a list of fields from a (possibly empty) table? (If the table is nonempty, you can just use a perl function to get the first record, and then apply keys() to the returned reference to get the field names.) http://www.mysql.com/doc/D/E/DESCRIBE.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: integerchar problem
i have a problem in query. My field type is integer. The table is empty and i want to select max number from the field but it display error message.Then i try to change the type to char ,it's can but, the max is until 9 only.is there anyone can help me. What query? What table? What error message? We can't help you if you don't give us enough information. - 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: Adding a [mysql] to the subject line
I am not sure if this has been addressed before, I think it would be neat if the list admin could put a [mysql] in the subject line for messages. I get a lot of email and it would help having that in the subject line. it would also help me in filtering my mail. Please let me know your comments. Outlook can easily filter on the Delivered-To header which is common to all messages sent to this list: Delivered-To: mailing list [EMAIL PROTECTED] [mysql] in the subject line is bad because filtering based on that will also filter offlist replies where the sender did not change the subject to remove the prefix. - 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: OR in QUERY
is there any way turn the following query to all ands instead of using an or? select f1,f2,f3 from table1 where (f1 ='monday' or f2 ='monday') and f3='34'; SELECT f1,f2,f3 FROM table1 WHERE 'monday' IN (f1, f2) AND f3 = '34'; - 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: Limit in SELECT
Lets say i have 10.000 rows in a table and im searching for only one record(one row). Which one is faster: SELECT ... LIMIT 1 or just SELECT .. The former, since MySQL stops after finding the first row. In the second query, MySQL prepares the entire result set even if you only fetch the first row. - 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: Whitespace in file names
Example I have a file name stored in database as: john's - song.mp3 When I click on the link to the song in a web browser I get file not found. http://www.php.net/rawurlencode - 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: joining two databases
i have 2 mysql servers, i need to run a query that joins two tables from two databases on the two servers, how can i do this? You can't. - 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: Invalid SQL..?
hi all.. this error came out after i make backup for my database using mysqldump what this error say is wrong... maybe my database is corrupt or crash or something like that.. any command can fix this error..?. any idea what i should do. Please paste the output of typing DESCRIBE policy; into the mysql client. Database error: Invalid SQL: select id, subid from policy where id='2' and subid='0' MySQL Error: 1054 (Unknown column 'id' in 'field list') Session halted. Now there's a pointless SQL query... heh - 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: Compressed column support?
Hi, I have looked through the documentation (http://www.mysql.com/docs) and the mailing list archives (http://www.geocrawler.com/lists/3/Databases/8/0/) and find no mention of a feature that I was hoping was available: * text or varchar columns that are automatically compressed/uncompressed myisampack (http://www.mysql.com/doc/C/o/Compressed_format.html) will compress an entire table and mysql will decompress it when needed, but the table must be unpacked if you want to modify it in any way. - 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: isp complains about query...
SELECT * FROM listaemail WHERE id LIKE %% OR nome LIKE %% OR empresa LIKE %% OR email LIKE %% OR ativo LIKE %% ORDER BY nome; Time: 11 Lock_time: 0 Rows_sent: 30567 Rows_examined: 30568 before anyone jumps out and points it out, it is far from being optimized. it should (and will, as soon as i update the generator) read: select * from listaemail order by nome; my question is: could there possibly be a problem in the execution of such a query??? my isp's system administrator states this query is causing the server to become unstable. i firmly believe there is no harm in executing such query, even if it's not optimal. How often is this query being executed? Why do you need to fetch all 30k 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
RE: Invalid SQL..?
Database error: Invalid SQL: select id, subid from policy where id='2' and subid='0' MySQL Error: 1054 (Unknown column 'id' in 'field list') Session halted. the output look like this.. mysql desc policy; +++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +++--+-+-+---+ | service_charge | int(2) | | | 0 | | +++--+-+-+---+ 1 row in set (0.01 sec) The error message is quite self explanatory, there's no id or subid column in the table you are querying. - 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
I am currently trying to examine the benefits of continuing to use MySQL over Oracle. I need to know what MySQL's features are vs. those of Oracle. http://www.mysql.com/information/crash-me.php Also I am looking for performance data. Oracle prohibits benchmarks, so good luck finding them. What kind of scalability does MySQL have vs. that of Oracle. Is there (or will there be) any support in MySQL for something akin to Oracle's Parallel Server? I think MySQL's replication is what you would be looking for (http://www.mysql.com/doc/R/e/Replication.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: searching multiple text fields
I need to build a search utility which can query records based on multiple text parameter, my table will have about 10 fields and users can search on 3-5 parameters in one query. is there any way to achieve this other than doing a full table scan. http://www.mysql.com/doc/F/u/Fulltext_Search.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: Is last_insert_id reliable?
$sql4 = SELECT hits.hit_id as pixels from hits WHERE hit_id=LAST_INSERT_ID();; $result = mysql_query ($sql4, $connection) or die(error # . mysql_errno() . : . mysql_error()); $row = mysql_fetch_object($result); $pixels = number_format($row-pixels); It's staggering the difference in speed. But if I sit there and hit refresh on the browser, I get wildly different values for $pixels. It jumps around the actual number by +-10 or 12. The first method is precisely the same every time (unless a hit is recorded in the interim). Am I doing something wrong here? Can I not count on MySQL to know how many rows it has recorded in a table? Any idea why that select statement would select a different row each time? From http://www.mysql.com/doc/M/i/Miscellaneous_functions.html: The last ID that was generated is maintained in the server on a per-connection basis. Since LAST_INSERT_ID() works on a per-connection basis, and since you are most likely using persistent connections, each reload of your web browser returns a different instance of the http server and therefore a different persistent connection. The best way to do it would probably be to update the last id in the table right after inserting the row (update othertable set row = last_insert_id() where yadda yadda yadda). - 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: Next Newbie Question
mysql select vend_city+', '+vend_state+' '+vend_zip - from Vendors - order by vend_name - ; ... 4 rows in set (0.01 sec) How do I do this in MySQL? SELECT CONCAT(vend_city, ', ', vend_state, ' ', vend_zip) FROM Vendors ORDER BY vend_name; - 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: Security problems - Very Newbie!
C:\mysql grant all on *.* to administrator@sara identified by delboy Try this: C:\mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 463668 to server version: 3.23.39-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql grant all on *.* to administrator@sara identified by delboy; Query OK, 0 rows affected (0.01 sec) - 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: INSERT INTO ... SET ...
3. Is this a MySQL feature / bug or do other DBs support this? It doesn't work in Postgres. - 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
Bounces from rtmglobal.com
Am I the only one getting these? Someone really needs to fix their mail server... -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, July 13, 2001 4:10 AM To: [EMAIL PROTECTED] Subject: No valid command found Your message does not contain a valid command for this mail server to process. No action has been taken. Message-Id: [EMAIL PROTECTED] From: [EMAIL PROTECTED] Time-Stamp: Fri, 15 Jun 2001 13:31:46 -0600 : Message contains [1] file attachments
RE: wildcard date searching
Hello, I am still a beginner, but I am trying to learn MySQL. If you have a date field, and you are using the date field in your where clause, I understand how to use : WHERE date=-mm-dd; I am trying to find out if there is anyway to use the wildcard character % in dates like you can in text fields. I wanted to be able to say, WHERE date=-mm-%% or something to that effect. WHERE date LIKE -mm-% - 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: String and integer in SQL statement
I create a table, one field is integer type. I don't know why the following statements work : select * from table1 where field1 = 3; select * from table1 where field1 = '3'; In the second case, MySQL automatically casts '3' to 3. - 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: unique
umm when i declare a unique constraint on 2 fields in a table like so unique (col1,col2), if i insert data into the table will it check them individually or as a composite? for example if i inserted the values col1 | col2 1, A 1, B 2, B 2, A if the above unique constraint is declared will it give me errors when inserting? No. A good way of finding out is trying :-) mysql create table test (col1 int not null, col2 char(1) not null, unique (col1, col2)); Query OK, 0 rows affected (0.01 sec) mysql insert into test values (1, 'A'); Query OK, 1 row affected (0.00 sec) mysql insert into test values (1, 'B'); Query OK, 1 row affected (0.00 sec) mysql insert into test values (2, 'B'); Query OK, 1 row affected (0.00 sec) mysql insert into test values (2, 'A'); Query OK, 1 row affected (0.00 sec) mysql insert into test values (2, 'A'); ERROR 1062: Duplicate entry '2-A' for key 1 neither col1 or col2 are a primary key but together they could be considered a composite candidate key pair - 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 not incrementing
What error did you get? Did you try using NULL for the id, or leaving the auto_increment column out of the insert (insert into table (PartNumber, ..., StatusQtyWorked) values (...))? Hello, When I submit data it will not submit data into the database unless I put some kind of number in the ID field. This is not supose to be. Here is what my schema looks like. CREATE TABLE Parts( PartNumber INT(6) NOT NULL, QtyOnHand INT(2) NOT NULL, QtyOnOrder INT(2) NOT NULL, DateNextQtyComplete VARCHAR(10) NOT NULL, StatusQtyWorked VARCHAR(10) NOT NULL, ID INT(5) NOT NULL AUTO_INCREMENT, PRIMARY KEY (ID) ); Does anyone know why the auto_increment doesn't work? I'm using mysql Ver 8.0 Distrib 3.22.32, for pc-linux-gnu on i686 - 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: Re[2]: LIMIT with mysql_num_rows
He wants to know the maximum, like on a search for mysql at google it says Results 1 - 10 of about 2,580,000. He wants to know that 2,580,000 number without doing another query. I don't think that's possible. Of course it is. That's what I do all the time. That's what I was talking about. I am looking at the result of my query. Then I know how many I have. I won't and cannot show all of them, if there are too many. So I check and repost that query with a LIMIT constraint. I said without doing another query :-) Sure you can do count(*) beforehand... Also, you can't really do select * from table, get the number of rows, and just stop fetching rows after your limit is reached, cause that would waste the resources of preparing the entire result set. One thing I would like to know is how Google does about 2,540,000 instead of an exact number (does it just get the exact number and round it off, or does it do an estimation that is somehow less resource intensive... then again I think they have some funky system so that their database of words fits in ram). - 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: Comress table
Hi, I want to compress my tables but I gets an error: C:\mysql-data\dtim9tmyisampack wi_abbreviation.MYI Compressing wi_abbreviation.MYD: (7221 records) - Calculating statistics - Compressing file myisampack: Error on delete of 'wi_abbreviation.MYD' (Errcode: 13) Aborting: wi_abbreviation.MYD is not compressed C:\mysql-data\dtim9t' How can I solve this? 13 is permission denied. In a command prompt, run net stop mysql before you use myisampack, then run net start mysql when you're done. - 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: Accesses
Just wondering how many accesses can MYSQL handle... let's say per hour. I have about 500 accesses per hour, 8 clicks per second... does anyone know about this issue? thanks It really depends on what kind of queries you're running, your hardware, your configuration, your operating system, etc. The most I've seen so far is Jeremy's box which is doing 87 queries per second, and my busiest box is currently at 60 queries per second with a system load of 0.69 (and I can't see any queries in a processlist so they're all running quickly). - 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/MySQL Parse Error
Group; There is something missing in my understanding of the php mysql_connect statement because I am getting a parse error as follows: http://cxkop.com/Jobs/job_list.php Parse error: parse error in /home/virtual/cxkop1365/home/httpd/html/Jobs/job_list.php on line 31 This is a php problem, not a mysql one. The database exists and is populated. I am doing the Devshed tutorial jobs: http://www.devshed.com/Server_Side/MySQL/PerfectJob/page5.html The example code is thus: // $connection = mysql_connect($hostname, $user, $pass) or die (Unable to connect!); My attempts are: (this is line 31, line 30 is commented out above) $connection = mysql_connect($cxkop@localhost, $cxkop, $!@@!) or die (Unable to connect!); The first parameter is a hostname, not a username@hostname; just use localhost. You must also quote localhost: $connection = mysql_connect(localhost, $cxkop, $!@@!) or die (Unable to connect!); - 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: Importing a complete database
mysqldump databasename | gzip databasename.sql.gz transfer that file to the other server mysqladmin create databasename zcat databasename.sql.gz | mysql databasename Hi Is it possible to import a complete database from a server to another one? Thanks Eugénio - 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: file usage
Done, an email is now sent after an order is submitted. This is what it I have found a large number of files which have names like this www-bin.040 www-bin.041 www-bin.042 www-bin.043 www-bin.044 which are using up huge amounts of disk space on my server. It looks to me like they are logs all of the queries that are run with a certain connection. They are logs of all queries that are run which change data (UPDATEs, INSERTs, but not SELECTs) Since my server averages just over 9 queries a second these logs are astronomical is size and they are also not necessary to me. Heh, you should see how big they get on a server which averages 60 queries per second... Is there a way to get rid of this that any one knows of? Use the PURGE MASTER LOGS command (http://www.mysql.com/doc/R/e/Replication_SQL.html) to clean up old files, or disable binary logging (remove the line with log-bin in /etc/my.cnf), restart mysqld, then delete www-bin.* - 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: Turning off logging for certain download queries
We do a few large daily and monthly downloads from an Oracle database server. Is there a way to exclude these specific queries from the update/binary log?? If the queries only affect one table or database, use the replication ignore options listed here: http://www.mysql.com/doc/R/e/Replication_Options.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: binding to ips
sorry im a mysql newbie...how do you bind mysql to two particular ip addresses? i have it bound to my loopback but would like to bind it a private ip but it doesn't seem to like the bind-address directive more than once. Run two separate mysql daemons with a different --bind-address parameter for each one. Just make sure you don't use --skip-locking. - 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: Join syntax
Is it possible to join two columns from different databases Yes. located on differents hosts? No. Why do you want to do this? - 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: use of RENAME???
No. To rename a database, rename the directory in mysql's data dir. There is no SQL command to rename a database. [mysql] is this how to change the the name of an existing populated db? RENAME DATABASE old_name TO new_name; TR - 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 users.
Why does it matter? They can't access them, so what harm is there? I am sure that it has been asked before. I am running a hosting company and I am running mysql on a win2k server. We don't want any customer to see other db's of any other customer, we just want them to see their own db's only. But even if you connect with a username who has access to only one db, when you type show databases you can see all other databases easily. Is there any solution to this problem? Omer Barlas [EMAIL PROTECTED] www.emedia.gen.tr - 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: different subdir for database
Hi, I have mysql on NT4 (soon to be win2000). one question is that the database files all reside under drive c. NT does not have symbolic link I seem to recall Microsoft making a big deal out of the fact that they invented symbolic links and added them to Win2000. Anyone used them with MySQL? I haven't used them with MySQL but they would work perfectly, since they are completely transparent to programs (TreeSize thinks my 40 GB drive C: has 70 GB of data because of them). They are called junctions and only work with folders on NTFS5 partitions. To create or delete junctions, you can use this tool: http://www.sysinternals.com/ntw2k/source/misc.shtml#junction And they look like this from cmd.exe: 06/30/2001 03:02a DIR incoming 03/11/2001 10:30a JUNCTION movies - 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: system error 1067
Try looking in mysql\data\mysql.err or running mysqld --standalone, it may provide more information. Hello everyone, I have mysql-3.23.39 installed on a Win2k Pro box (not sure why!). It had been running just fine, then all of a sudden stopped working. When I try a 'NET START mysql', I get the below error: ---Begin Error The MySql service is starting. The MySql service could not be start A system error has occurred. System error 1067 has occurred. The process terminated unexpectedly. ---End Error Has this happened to anyone else? Or am I just the unlucky one? - 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: Creating and dropping foreign key constraints
MySQL doesn't support foreign keys. Did you bother checking the manual? http://www.mysql.com/doc/M/i/Missing_Foreign_Keys.html Hello Does mysql support creating and dropping of foreign key constraints? i tried it.While creating foreign key constraints it doesn't dive any error but while dropping i do get some syntax errors. what is the reason? any suggetions? - 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: Delete if fields are older than x months
DELETE FROM mbtable WHERE timecolumn DATE_SUB(NOW(), INTERVAL 3 MONTH); Hello, I've a messageboard. Now, I want to delete all messages older than 3 months. Can I use a MySQL query to delete all messages or do I have to use mktime()? - 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 + Linux
Hi! Does anybody know how do i change the 'root' password for mysql server? I installation guide they say that it's done by typing '/usr/local/bin/mysqladmin -u root -p password new-password', but how would i know the old passsword? Am i mising anything? Leave out -p - 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: DISTINCT AND NON DISTINCT #2
Your question has been answered already. http://marc.theaimsgroup.com/?l=mysqlm=99348999221821w=2 Hi All, I've been reading up on how to do joins on the mysql site. Is there not an inverse function for DISTINCT, so that I can pull out duplicate data as opposed to DISTINCT data...or will I have to get this information by using joins? - 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 Commands - DJW
http://www.mysql.com/doc/ HI Everyone I am looking for a summary/examples of the syntaxes and operators one may use in standard sql statements For example select name from epm_tble where name like 'abc%' select x, count(*) from table_where_x_is_in group by x having count(*)1; - 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: Sub-selects in a insert statement - is it possible to rewrite?
This is how I'd like the query to work: insert into host (host_id, host_IP) values ((select from mail mail_id where mail_addr = [EMAIL PROTECTED]), '127.0.0.1') Try: insert into host (host_id, host_IP) select mail_id, '127.0.0.1' from mail where mail_addr = '[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: how to create table with unique 2 column tuple
ALTER TABLE tblname ADD UNIQUE (column1, column2); The index will automatically be used if you use it in your WHERE clause in that order. If you want to do fulltext searching on those columns, repeat the query with FULLTEXT in place of UNIQUE. Hello, I'm trying to create a table with columns A, B, C, D such that values in columns A and B are not unique but if you consider them as a paired tuple, then for every row, value in col A, value in col B is unique. Furthermore, I'd like to create a index to use for searching through the database using this unique paired tuple. Does anyone know how to do this without actually merging columns A and B together? 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: Can't connect error 111
Hi, I am not able to connect mysql server through other machine in a network. I have already assign the user using the command of Grant all on *.* to user@ipaddress identified by password. Then, try to connect using mysql -u user@ipaddress -h firstserver -ppassword.But, I got the following error. Warning: MySQL Connection Failed: Can't connect to MySQL server on 'dns1.3dsources.com' (111) in /home/trade-revenues/require/password.php on line 1 Hi, I am not able to connect mysql server through other machine in a network. I have already assign the user using the command of Grant all on *.* to user@ipaddress identified by password. Then, try to connect using mysql -u user@ipaddress -h firstserver -ppassword.But, I got the following error. Warning: MySQL Connection Failed: Can't connect to MySQL server on 'dns1.3dsources.com' (111) in /home/trade-revenues/require/password.php on line 1 You tried to connect using the mysql client, and got an error from... php... Try mysql -u user -h firstserver -ppassword, the @ipaddress is your own ip address and is automatically detected, it is NOT part of the username. oh I forgot to tell, I have used a firewall ipchains. what port do I have to open for mysql: tcp or udp Port 3306, tcp. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: bind to specific address
Hi, How to bind mysql to specific address if I am using my.cnf file. etc. [mysqld] --bind-address=xxx.xxx.xxx.xxx Is the above syntax correct ?? Close. [mysqld] bind-address=xxx.xxx.xxx.xxx - 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: NT 2000
Try net start mysql at a command prompt Hi Team, Pls help, I'm new to mysql. I got the latest ver installed on NT2000 workstation. Upon I tried the activate the daemon, under c:\mysql\bin\mysqld, it won't work. Any advise. Thanks Regards KA - 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: Console Remote connect Commands
As a long time DB2.. Sybase.. Postgresql user upon turning to MySQL I find only some functionalities not intuitive. 1. What command syntax to use and connect to a database on another host. You've got to be kidding me, it's exactly the same as Postgresql: chris@phantasm:~$ mysql --help ... -h, --host=...Connect to host. chris@phantasm:~$ psql --help ... -h host Specify database server host (default: domain socket) 2. When I add a Database.., to use it I must add a user ??? and must I again add to the host table with reference to user and database?? http://www.google.com/search?q=cache:IXS6vmvnhHI:www.mysql.com/doc/G/R/GRANT .html+www/mysql.com/doc/G/r/hl=en I have searched the mailing lists and Documentation but my searches have not turned up the answer.. - 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: Download of mysql for Windows 2000
USA: http://mysql.he.net/ UK: http://mysql.omnipotent.net/ I have been trying to access www.mysql.com but can not seem to access it. Does anyone know a mirror site I could go to to download mysql for Windows 2000? Thank you! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: complete delete to begin again, instructions please
Use http://www.mysql.com/doc/R/e/Resetting_permissions.html to reset your password. could someone tell me how to make a clean sweep of MySQL from my system so i can start over? (i need to toss phpMyAdmin temporarily too i get asked for a password at every bend and none of the ones i use' hardly ever' work ;) - 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 - connection problem
Download and install http://www.perl.com/CPAN-local/authors/id/J/JW/JWIED/DBD-mysql-2.0901.tar.gz Hi, I am a newbie and hope someone can she light on why I can't get the DBD::mysql connection working in my perl script. The script is a test one and very simple. I have the mysql daemon running and mysqladmin works, and DBI is installed too. I went through the installation process for DBD and have also placed the /usr/local/mysql path reference in the environment. These are the errors below: --- Can't locate DBD/mysql.pm in @INC (@INC contains: /opt/perl5/lib/5.6.0/PA-RISC1.1 /opt/perl5/lib/5.6.0 /opt/perl5/lib/site_perl/5.6.0/PA-RISC1.1 /opt/perl5/lib/site_perl/5.6.0 /opt/perl5/lib/site_perl .) at create_table.pl line 8. BEGIN failed--compilation aborted at create_table.pl line 8. --- - 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 indexes
Just a couple of questions regarding indexes... Can I have multiple indexes on the same column? Yes, but it's kind of pointless. Will this decrease performance when inserting? Yes. Can you create and remove indexes after the creation of the table? Yes. - 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: Upgrading MySQL on Windows Platform
Yes, just stop the mysql service before you begin installation by running something like net stop mysql from a command prompt. Hi everyone, I'm kinda new with MySQL and I have a question about upgrading MySQL to a newer version on Windows 2000 OS. Is there any special command that I should run or should I just re-install the latest version 3.23.39. Will it overwrite the old version automatically while preserving all the tables and databases? Please help. 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: Is there an archive?
http://marc.theaimsgroup.com/?l=mysqlr=1w=2 Is there an archive to this list? My MS Access ODBC is not sending the user name to MySQL, and I don't doubt that it is a known 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: Syncing two databases
http://www.mysql.com/doc/R/e/Replication.html Hi all, I'm new to this list, fairly new to PHP and mySQL and I have a question about syncing two databases. I search all over the web for this, including a win32 version of rsync, but that's not suitable in my case, so here it goes: - 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: Long blob insert
The default max_allowed_packet is 1 meg http://www.mysql.com/doc/P/a/Packet_too_large.html hi Can anyone please help with regards to long blob data insert. I have a data which more than 1mb which MySQL long blob field refuse to insert. However if my data is less than 1mb there if no problem with insert. What parameter do i need to set up.. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Error creating a fulltext
What are the types of the columns? Hi all, ... running this query alter table item_dettagli add fulltext(categoria_prezzo,settore,movimento,soggetto,lingua) I get this error: 1005 - Can't create table '.\pickwick\#sql-3d0_2.frm' (errno: 140) Query: alter table item_dettagli add fulltext(categoria_prezzo,settore,movimento,soggetto,lingua) what does it means?? (vers. 3.23.37 on win2k box) many thanks in advance max - 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: copying tables between databases in mySQL 3.22.32
http://www.mysql.com/doc/R/E/RENAME_TABLE.html says: As long as two databases are on the same disk you can also rename from one database to another: RENAME TABLE current_database.table_name TO other_database.table_name; And as the user comments say, you can use ALTER TABLE with 3.22. Copying can be done as well, just CREATE the table with the same table definition, then use INSERT ... SELECT (using database.table instead of just table). Hi, I am using mySQL 3.22.23 on a Linux platform. How can I copy or move Tables between different Databases? Is there a simple command for that? We do not have the mysqlhotcopy command; is it safe to download and use for our mySQL version, and can it do what I want it to do? I cannot find anything definite in the manual about this, so any suggestions are welcome! - 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! functions don't work...
Fatal error: Call to undefined function: dayname() in /home/ccrb/ccrb-www/index.html on line 8 This indicates that I'm missing most all of the functions. Is there a standard include that I need, or is my php installation not complete? Here is the line of code in question: ?php printf(test for jon... dayname is %s,dayname(2001-06-17)); They're mysql functions, not php functions... ? $dblink = mysql_pconnect(localhost, user, pass); $res = mysql_query(SELECT dayname('2001-06-17')); list($dayname) = mysql_fetch_row($res); echo($dayname); ? http://www.php.net:8000/manual/en/html/ref.mysql.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: .dump files
thank you. that's sort of what i thought. now for the silly question: i know how to do it with 'monitor' (CLI), but where/how do you import using phpMyAdmin, please? (this i'd love to know :) i looked through the manual but didn't see anything for import db. Click a database on the left frame and it's under the query textarea (there's a browse button and a go button). - 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: Number of Word Matches
Hi there, I'm using select queries like SELECT * FROM table_name WHERE col_name LIKE '%some_word%' to find rows that have col_name match to some_word. But I would also like to find the number of word matches for each row so that I can order these rows according to their relevances and word matches. How can I do that?? http://www.mysql.com/doc/F/u/Fulltext_Search.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: gnu tar
I have downloaded the mySql distribution as well as 2 different versions of gnu tar that available at the site. gtar-solaris-4.1.4-sparc.gz and gtar-sparc.gz The reason I downloaded 2 different versions of the gnu tar is that I can't get anyone of these to deflate using gzip. I has successfully deflated the mySql distribution but all attempts at deflating the gnu tar stuff results in gzip: name_of_gzipped_file: not in gzip format If you are downloading them via FTP, are you using binary mode? If so, try compiling GNU tar from source (ftp://ftp.gnu.org/gnu/tar/tar-1.13.tar). I just extracted both of those .gz files successfully so the copies on the server are fine. - 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: .dump files
That is a .sql file renamed to a .dump file, there's no difference. Import it with phpMyAdmin or mysql -u username -ppassword dbname whatever.dump i had some others that i browsed into phpmyadmin and they seemed to work, but others, like this one, showed a Query error -but that method of imported population was probably wrong anyway, i guess. thanks here's the file: (clipped) - 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: .dump files
What format are they in? Please paste a few sample lines. hello, i have some files from a Cd that are .dump files, how do i get these db into mysql? (is it possible using phpMyAdmin). again, these are .dump files, not .sql 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: order by rand() question
I don't think the answer has changed since last week :-) IMHO it's not really slow and inefficient, anyway - this script: (clipped) produces a table with 1 rows each containing an integer. Then we do: mysql SELECT * FROM mytable ORDER BY RAND() LIMIT 5; (clipped) 5 rows in set (0.08 sec) Right, that's *less than a tenth of a second*, and that's on a crufy old P200 that also happens to be serving our company Intranet at the same time as doing this little experiment. So what's so inefficient? I can't blink in 0.08 seconds, let alone think a query is running too slowly. Watch out though. Sure, it may be that fast when you're selecting one row out of 10,000 ints on a lightly loaded server. However MySQL works this way: - SELECT * FROM mytable; - Order all found rows by rand() - Send back the first 5 It's not that inefficient when you just have one column, since that's all you'll be getting anyways, however I have a table with 38 columns, 141000 rows and lots of data you are making MySQL get a lot of data it doesn't end up using. This is how fast your fast query runs on it (granted this is on a server with medium to heavy load and ~50 queries per second): mysql select * from users order by rand() limit 1; ... 1 row in set (1 min 29.48 sec) You can make this a little faster: mysql select id from users order by rand() limit 1; ... 1 row in set (10.10 sec) mysql select * from users where id = 123456; ... 1 row in set (0.12 sec) But it's still quite inefficient. The best way would be to generate the random numbers from your application. If you wanted five rows, generate 10 numbers (just to be really safe in case a few reference deleted rows) between 1 and max(id) (assuming you have an auto_increment column named id) and do: mysql select max(id) from users; ... 1 row in set (0.12 sec) mysql select * from users where id in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) limit 5; ... 5 rows in set (0.33 sec) - 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: Collapsing queries
SELECT cont.msg_id, cont.msg_text, CONCAT_WS(', ', a1.address, a2.address) FROM CONTENT AS cont, ADDRESS AS a1, ADDRESS AS a2 WHERE cont.msg_id = a1.msg_id AND cont.msg_id = a2.msg_id; Many thanks to salle in efnet #mysql for teaching me this trick ;-) Hello, I am relatively new with MySQL (and SQL for that matter) and I couldn't find much reference to my problem in earlier posts. Let's say I have 2 tables, CONTENT: msg_id, msg_text ADDRESS: msg_id, address and I receive the msg Hello World addressed to the twins Foo and Bar. now my tables look like this: CONTENT: 1 Hello World ADDRESS: 1 Foo 1 Bar I am desperatly looking for a sql statement that would produce 1 | Hello World | Foo, Bar Is there such a thing?? - 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 compression?
Look up myisampack in the mysql manual at http://www.mysql.com/doc/. The only drawback is you can't modify the table. I have a table that has massive amounts of text. Just plain text, stuff that would compress REALLY well. Does mysql have any sort of compression internally for the table data that it stores? A simple gzip wouldn't add too much overhead to the system, and you could still have clear-text indexes. - 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