RE: Upgrading How To
Reindl, I am sorry, in my original post, I forgot to mention that the OLD box and the NEW box are the same physical machine. I need to be able to save all data into files on a memstick or portable disc and restore them to the newly staged machine (with the new version of mysql). -Grant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Upgrading How To
Shawn all, Thank you for taking to time to reply. So, to be clear, what I understand from your post is that replacing the new build's grant/system tables with the archived ones from the previous version, generally works fine, upgrade issues not withstanding. This is the answer I was hoping for. FWIW, I have already tested using some sample databases from the old version to the new one. Also, I'm not sure I mentioned, but I am moving from 5.1.39 to 5.6.17. I have already ran into the password hash issues on a number of tables, but, other than that things seem fine. Any other comments are welcome. -G
Upgrading How To
Hi all, I was wondering if anyone knows of a concise tutorial on how to upgrade (by moving from one box (old) to another box (new) mysql in a virtual environment (many mysql users, many databases). Example: Mysql 5.x setup on freebsd 8.x (x86/32b), call this box A. Want to move to a new box: Mysql 5.6.17 on freebsd 9.3 (x86/32b) call this box B. 1) How does one correctly move the users and all the permissions (grant tables), 2) How does one move the data. 3) Assume lots of backups have been done and there is no risk of permanently loosing data. Also, move/copy to be done using files (to memstick or external disk or NFS), not using ssh directly (rcopy etc). 4) Basically, dumpt the data and users and perms and put it on a new box J I suspect this question is trivial to a lot of admins out there, but, I suspect it would be helpful to many out there. Happy Holidays! -Grant
Re: munin MyISAM InnoDB issues
I seem to understand this is the check on the innodb main tablespace. Sounds like an overflow in munin check that builds the graph, due to the type of check (MySQL InnoDB free tablespace) I would not mind too much(There's no such critical thing like too much free space!), apart from patching the munin check to deal with numbers 2^32. To double check, look at: mysql SHOW TABLE STATUS LIKE 'any innodb table that was created in the main tablespace'\G Look at the Data_free: line, it is probably bigger than 2^31. Actually it's zero. Is that a problem? - Grant I have 4 out of 6 mysql graphs working in munin. MySQL isam/myisam table-space usage is a broken image and MySQL InnoDB free tablespace says: This service is in CRITICAL state because one of the values reported is outside the allowed range. Field Internal name TypeWarnCritInfo Bytes free freegauge 2147483648: 1073741824: I don't know enough about mysql (or munin) to figure out what's going on. I'm using both MyISAM and InnoDB tables. Can anyone help me out? - Grant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
munin MyISAM InnoDB issues
I have 4 out of 6 mysql graphs working in munin. MySQL isam/myisam table-space usage is a broken image and MySQL InnoDB free tablespace says: This service is in CRITICAL state because one of the values reported is outside the allowed range. Field Internal name TypeWarnCritInfo Bytes free freegauge 2147483648: 1073741824: I don't know enough about mysql (or munin) to figure out what's going on. I'm using both MyISAM and InnoDB tables. Can anyone help me out? - Grant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: munin MyISAM InnoDB issues
I have 4 out of 6 mysql graphs working in munin. MySQL isam/myisam table-space usage is a broken image and MySQL InnoDB free tablespace says: This service is in CRITICAL state because one of the values reported is outside the allowed range. Field Internal name TypeWarnCritInfo Bytes free freegauge 2147483648: 1073741824: I don't know enough about mysql (or munin) to figure out what's going on. I'm using both MyISAM and InnoDB tables. Can anyone help me out? - Grant I should add that I get the following: # munin-run mysql_isam_space_ # munin-run mysql_innodb free.value 0 - Grant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Postal code searching
That still leaves the question, what are the actual rules/business logic by which you want to group things and get their abbreviations? Are you adhering to Royal Mail/Post Office residual selection/direct selection rules, or do you have your own scheme? It seems like the latter ... the RM rules wouldn't abbreviate WC1H to WC1. Have you tried REGEX matching based on your rules? Fuzzy :-) On 24/04/2012 14:16, Neil Tompkins wrote: At the moment im concentrating on london postal codes but future would be us zip codes too On 24 Apr 2012, at 18:09, Rick James rja...@yahoo-inc.com wrote: Please be more precise about the rules. In the US, 12345-6789 would become 12345. This would follow a different rule. Is your rule stop after the first digit? That gets quite messy in SQL, and would be better done in an application code. See also http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index RLIKE can distinguish digits from letters, but won't help you isolate them. -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Tuesday, April 24, 2012 9:11 AM To: [MySQL] Subject: Postal code searching Hi I've a number of different postal codes in a system for example WC1B 5JA WC1H 8EJ W1J 7BX W1H 7DL NW1 1NY I can use like statements for example SELECT * FROM postal_codes WHERE zip LIKE 'W1%' giving me W1J 7BX W1H 7DL In addition I have a number of abbreviated postal codes like W1 WC1 WC2 NW1 Now, if I know the postal code W1J 7BX what is the best way using a MySQL query to get the abbreviated postal codes W1. Same if I have the postal code WC1H 8EJ, how do I get the abbreviated postal codes WC1 Can I use any matching patterns ? Thanks, Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Verifying security
without flags to tell it otherwise, nmap only scan ports 1-1024, higher numbered ports would have to be specified via nmap -p 1-5000 but see how much longer it takes. Bingo. That was it. I see now that I've had port 3306 open until recently. I'm working on verifying that the mysql users have passwords. - Grant I've got a couple security questions for you guys. Is there a way to verify I've set a password for mysql's root? I was previously running mysqld without --skip-networking until I noticed port: 3306 was referenced in mysqld.err. The strange thing is 'nmap localhost' never found an open 3306 port and I wasn't running a firewall on that system. Does this behavior make sense to anyone? - Grant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Verifying security
To verify that root has a password, do the following: 1) service mysql restart --skip-grant-tables 2) In MySQL, SELECT CONCAT(,user,'''@''',host,) mysql_user,password from mysql.user where user='root'; This will show every host that root can login as along with the PASSWORD function-encrypted of the root password 3) service mysql restart Make sure you have the user 'root'@'localhost'; I remembered my root mysql password and I was able to log into mysql with it. I tested for a mysql user with: SELECT CONCAT(,user,'''@''',host,) mysql_user,password from mysql.user where user='mysql'; and I got Empty set (0.00 sec). Can I issue a mysql command that will check for other mysql users? I just confirmed that I've had port 3306 open until recently. If mysql root has a strong password and I don't have any mysql users besides root, can I consider myself safe? Are there any other essential steps I should take for a secure mysql installation? Is it necessary for mysql root to have a password since I'm the only user on the system and port 3306 is closed? With regard to --skip-networking, keep in mind that this blocks TCP/IP DB Connections This will not block socket-based connections i.e., this will block 'root'@'127.0.0.1', but not 'root'@'localhost' So --skip-networking keeps me safe from remote attackers but not from local ones? - Grant I've got a couple security questions for you guys. Is there a way to verify I've set a password for mysql's root? I was previously running mysqld without --skip-networking until I noticed port: 3306 was referenced in mysqld.err. The strange thing is 'nmap localhost' never found an open 3306 port and I wasn't running a firewall on that system. Does this behavior make sense to anyone? - Grant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql_upgrade fails with Access denied
I'm trying to run mysql_upgrade but I get: # mysql_upgrade Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock' mysqlcheck: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect FATAL ERROR: Upgrade failed I've also tried specifying my root password with --password and also specifying -u mysql but it still fails with error 1045. My /etc/passwd file looks like there is no password for user mysql. What else should I try? You should use the password for the _MySQL_ root user, which is _not_ stored in /etc/passwd, but within MySQL. / Carsten Thanks everyone. I got mysqld to start with --skip-grant-tables by adding skip-grant-tables to /etc/mysql/my.cnf and restarting mysql. mysql_upgrade ran without a problem. - Grant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Verifying security
I've got a couple security questions for you guys. Is there a way to verify I've set a password for mysql's root? I was previously running mysqld without --skip-networking until I noticed port: 3306 was referenced in mysqld.err. The strange thing is 'nmap localhost' never found an open 3306 port and I wasn't running a firewall on that system. Does this behavior make sense to anyone? - Grant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mysql_upgrade fails with Access denied
I'm trying to run mysql_upgrade but I get: # mysql_upgrade Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock' mysqlcheck: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect FATAL ERROR: Upgrade failed I've also tried specifying my root password with --password and also specifying -u mysql but it still fails with error 1045. My /etc/passwd file looks like there is no password for user mysql. What else should I try? - Grant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql_upgrade fails with Access denied
I'm trying to run mysql_upgrade but I get: # mysql_upgrade Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock' mysqlcheck: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect FATAL ERROR: Upgrade failed I've also tried specifying my root password with --password and also specifying -u mysql but it still fails with error 1045. My /etc/passwd file looks like there is no password for user mysql. What else should I try? Is this 5.5.7? You have to run the server with --skip-grant-tables, then run mysql_upgrade. I'm running mysql-5.1.51 which is the latest marked stable in Gentoo. Does this still apply? I always use Gentoo's mysql initscript so I'm trying to find the correct command to start mysqld wtih --skip-grant-tables. - Grant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql_upgrade fails with Access denied
I'm running mysql-5.1.51 which is the latest marked stable in Gentoo. Does this still apply? No, just 5.5.7. What else should I try to prevent the 1045 error? - Grant I'm trying to run mysql_upgrade but I get: # mysql_upgrade Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock' mysqlcheck: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect FATAL ERROR: Upgrade failed I've also tried specifying my root password with --password and also specifying -u mysql but it still fails with error 1045. My /etc/passwd file looks like there is no password for user mysql. What else should I try? Is this 5.5.7? You have to run the server with --skip-grant-tables, then run mysql_upgrade. I'm running mysql-5.1.51 which is the latest marked stable in Gentoo. Does this still apply? I always use Gentoo's mysql initscript so I'm trying to find the correct command to start mysqld wtih --skip-grant-tables. - Grant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Mysql 4 to 5
Hi all, I recently migrated a script called perldesk from mysql 4 to mysql 5. When I envoke the script from the web, I am now getting an error. I suspect its a version syntax thing. Here is what I see: Couldn't execute statement: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'call = '1307' ORDER BY id' at line 1; stoppedContent-type: text/html PerlDesk: Script Error Perldesk was unable to launch due to the following errors: 1 at include/staff_subs.cgi line 1401. line 1401 is the third line below: $statemente = 'SELECT * FROM notes WHERE call = ? ORDER BY id;'; $sth = $dbh-prepare($statemente) or die print Couldn't prepare statement:$DBI::errstr; stopped; $sth-execute($trackedcall) or die print Couldn't execute statement: $DBI::errstr; stopped; Any help on how to fix this would be appreciated, -G
Re: Mysql 4 to 5
- Original Message - From: Michael Dykman mdyk...@gmail.com To: Grant Peel gp...@thenetnow.com Cc: mysql@lists.mysql.com Sent: Sunday, July 11, 2010 9:20 PM Subject: Re: Mysql 4 to 5 One problem you might be having is the column named 'call'.. It's a keyword, so perhaps you might get away with backticking it?ie. `call` Also, you shouldn't need that final semi-colon inside your statement string.. I have had preparedstatement interfaces give me grief about that. As I recall, DBI doesn't attempt to validate the statement until execute time. $statemente = 'SELECT * FROM notes WHERE call = ? ORDER BY id;'; $sth = $dbh-prepare($statemente) or die print Couldn't prepare statement:$DBI::errstr; stopped; $sth-execute($trackedcall) or die print Couldn't execute statement: $DBI::errstr; stopped; - md On Sun, Jul 11, 2010 at 7:50 PM, Grant Peel gp...@thenetnow.com wrote: Hi all, I recently migrated a script called perldesk from mysql 4 to mysql 5. When I envoke the script from the web, I am now getting an error. I suspect its a version syntax thing. Here is what I see: Couldn't execute statement: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'call = '1307' ORDER BY id' at line 1; stoppedContent-type: text/html PerlDesk: Script Error Perldesk was unable to launch due to the following errors: 1 at include/staff_subs.cgi line 1401. line 1401 is the third line below: $statemente = 'SELECT * FROM notes WHERE call = ? ORDER BY id;'; $sth = $dbh-prepare($statemente) or die print Couldn't prepare statement:$DBI::errstr; stopped; $sth-execute($trackedcall) or die print Couldn't execute statement: $DBI::errstr; stopped; Any help on how to fix this would be appreciated, -G -- - michael dykman - mdyk...@gmail.com May the Source be with you. Thanks Mike, I will try that :-) -G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysql 4 to 5
- Original Message - From: Grant Peel gp...@thenetnow.com To: Michael Dykman mdyk...@gmail.com Cc: mysql@lists.mysql.com Sent: Sunday, July 11, 2010 9:25 PM Subject: Re: Mysql 4 to 5 - Original Message - From: Michael Dykman mdyk...@gmail.com To: Grant Peel gp...@thenetnow.com Cc: mysql@lists.mysql.com Sent: Sunday, July 11, 2010 9:20 PM Subject: Re: Mysql 4 to 5 One problem you might be having is the column named 'call'.. It's a keyword, so perhaps you might get away with backticking it?ie. `call` Also, you shouldn't need that final semi-colon inside your statement string.. I have had preparedstatement interfaces give me grief about that. As I recall, DBI doesn't attempt to validate the statement until execute time. $statemente = 'SELECT * FROM notes WHERE call = ? ORDER BY id;'; $sth = $dbh-prepare($statemente) or die print Couldn't prepare statement:$DBI::errstr; stopped; $sth-execute($trackedcall) or die print Couldn't execute statement: $DBI::errstr; stopped; - md On Sun, Jul 11, 2010 at 7:50 PM, Grant Peel gp...@thenetnow.com wrote: Hi all, I recently migrated a script called perldesk from mysql 4 to mysql 5. When I envoke the script from the web, I am now getting an error. I suspect its a version syntax thing. Here is what I see: Couldn't execute statement: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'call = '1307' ORDER BY id' at line 1; stoppedContent-type: text/html PerlDesk: Script Error Perldesk was unable to launch due to the following errors: 1 at include/staff_subs.cgi line 1401. line 1401 is the third line below: $statemente = 'SELECT * FROM notes WHERE call = ? ORDER BY id;'; $sth = $dbh-prepare($statemente) or die print Couldn't prepare statement:$DBI::errstr; stopped; $sth-execute($trackedcall) or die print Couldn't execute statement: $DBI::errstr; stopped; Any help on how to fix this would be appreciated, -G -- - michael dykman - mdyk...@gmail.com May the Source be with you. Thanks Mike, I will try that :-) -G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gp...@thenetnow.com Mike, UPDATE: THe backtick worked, thanks for the help! -Grant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Updating 4 to 5
- Original Message - From: Rob Wultsch wult...@gmail.com To: Grant Peel gp...@thenetnow.com Cc: mysql@lists.mysql.com Sent: Friday, July 02, 2010 3:57 AM Subject: Re: Updating 4 to 5 On Thu, Jul 1, 2010 at 5:44 PM, Grant Peel gp...@thenetnow.com wrote: Hi all, I have serveral servers running mysql 4, and need to update to mysql 5. It would be good if mentioned what release of the various series you were using or wanting to upgrade to. I have version 5 setup on a new dev server and will be cloning that to the old servers, then restoring all the data from backups (mysql databases included). By restoring from backup for mysql a sql dump, or a filesystem backup? Once I have restored the data from backups, I will neeed to run mysqlupgrade. My question is, will the mysqlupgrade script update all the mysql tables, (grant tables etc), as well as update all the users databases, or will there be other things that need to be done? -Grant It depends. The way I generally do upgrades is the following: 1. Identify the backup point for the current server. Do a 'mysqldump --all-databases --complete-insert' from it using the mysqldump from the version of mysql I will be using after the upgrade and record the 'show slave status' while it is running. 2. Import the backup on to the new server after removing any commands that would perform ddl on the mysql schema. 3. Setup replicaton and fail over to the new server at an opportune time. So, you should keep in mind a few things: 1. Between version of MySQL the table format changes, and it is generally worthing while to take advantage of the changes. 2. mysqlupgrade runs REPAIR TABLE which acts differently in different versions of MySQL See http://www.mysqlperformanceblog.com/2010/05/14/mysql_upgrade-and-innodb-tables/. -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gp...@thenetnow.com Rob, Thanks for taking the time to reply, The mysql databases would be restored from a filesystem backup. The whole server, mysql and all, are FreeBSD 6.x I usually: Run a complete backup of all filesystems, (/,/var,/home,/user) to an NFS server, Build the new server, Restore all filesystems to the new disk (that is built with mysql 5), Run the mysql upgrade script, start the mysql servers, then fix any PHP issues (as php is upgraded from 4 to 5 as well). Comments please, -Grant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Updating 4 to 5
Hi all, I have serveral servers running mysql 4, and need to update to mysql 5. I have version 5 setup on a new dev server and will be cloning that to the old servers, then restoring all the data from backups (mysql databases included). Once I have restored the data from backups, I will neeed to run mysqlupgrade. My question is, will the mysqlupgrade script update all the mysql tables, (grant tables etc), as well as update all the users databases, or will there be other things that need to be done? -Grant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Updating from 4 to 5
nilnandan, I meant to say Mysqlupgrade :-) Further, I can use the old mysql tables, (the mysql gant tables, user/passwd/host tables etc) and the mysqlupgrade will update them as necessary? -Grant - Original Message - From: Nilnandan Joshi To: Grant Peel Cc: mysql@lists.mysql.com Sent: Tuesday, June 29, 2010 4:00 AM Subject: Re: Updating from 4 to 5 Grant, I think, you should run mysqlupgrade after copying old data in new servers. Regards, nilnandan Grant Peel wrote: Hi all, I am about to move from FreeBSD 6 to FreeBSD 8. With that, the mysql server version will be changed from 4 to 5. I am assuming I can load all the users tables, and the mysql database (grant tables and all) to the new server, then run mysqlupdate, and everything that needs to be updated will be. Any comments? -Grant
Updating from 4 to 5
Hi all, I am about to move from FreeBSD 6 to FreeBSD 8. With that, the mysql server version will be changed from 4 to 5. I am assuming I can load all the users tables, and the mysql database (grant tables and all) to the new server, then run mysqlupdate, and everything that needs to be updated will be. Any comments? -Grant
Re: Help with export and import into Oracle
machiel.richards wrote: Good day guys [snip] . Each item in the text field is added in the field by entering the country name then pressing enter and then entering the next, etc . When exporting the data to a file (even when enclosing each field within quotes) it still writes the control characters causing each item to be read as a different line and thus the import into Oracle fails. Any idea on how we can resolve this as the process needs to be cronned to run on a weekly basis and thus we need to get this process resolved. You haven't described what process you're using to read the file for the Oracle import - all of Oracle's interfaces (oci, SQL, PL/SQL, load utilities like SQL*Loader and imp/impdp, external tables, etc.) can handle multi-line records like this. Given you're dumping to a file, it's mostly likely you're using SQL*Loader (i.e. sqlldr). The INFILE clause for the control file includes an os_file_proc_clause which let's you set the record delimiter, and override the default end of line behaviour. See http://www.orafaq.com/wiki/SQL*Loader_FAQ#How_does_one_load_records_with_multi-line_fields.3F for an example. If you're not using SQL*Loader, then more info would be required. Ciao Fuzzy :-) Dazed and confused about technology for 20 years http://fuzzydata.wordpress.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Unable To Remove User
Carlos Williams wrote: On Tue, Sep 29, 2009 at 11:36 PM, mos mo...@fastmail.fm wrote: Why don't you just say Drop User carlos? Also are you logged in as root? In my original message to the board I demonstrated the error I get on my server when I attempt to run the 'drop user 'carlos'@'localhost'; command. Why can't I remove this user from MySQL? [ snip ] Database changed mysql select User, Host from user; +++ | User | Host | +++ | carlos | localhost | | root | localhost | +++ 2 rows in set (0.00 sec) Carlos, is this output exactly as it appeared when you ran the command? Specifically, that extra carriage return after localhost for the user carlos? I wonder if the host value is actually localhost followed by a carriage return, which is why it isn't found when you try to drop the user using the 'carlos'@'localhost' value. Try this select statement to confirm: select user, host from user where host = 'localhost'; If you don't see carlos returned as a user, this is potentially the problem. If that's the case, use Claudio's suggestion to remove the user manually. Ciao Fuzzy :-) Dazed and confused about technology for 20 years http://fuzzydata.wordpress.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MYSQL startup problem
You have ten half-open connections (in SYN_SENT state), and a new connection attempt is giving you error 10055 which is windows' way of saying it can't allocate a buffer for a new connection. Are you running XP SP2 or SP3 or Vista? Microsoft introduced a throttle on in these versions, if more than ten are in the process of handshaking (e.g. SYN_SENT state). In theory this is to limit SYN floods and other connection attacks. To confirm if this is your problem, check your windows event viewer for system events with event id 4226: TCP/IP has reached the security limit Your ten handshaking connections are all to port 445 ... which is microsoft-ds (aka Active Directory). If the above matches your scenario, shut down whatever services/apps are doing this on the box, and MySQL will be able to give you a connection. Ciao Fuzzy :-) Dazed and confused about technology for 20 years http://fuzzydata.wordpress.com/ BAJAJ POOJA wrote: Hi, Pls. find response below: C:\wamp\mysql\binnetstat -an Active Connections Proto Local Address Foreign AddressState TCP0.0.0.0:25 0.0.0.0:0 LISTENING TCP0.0.0.0:80 0.0.0.0:0 LISTENING TCP0.0.0.0:1350.0.0.0:0 LISTENING TCP0.0.0.0:4450.0.0.0:0 LISTENING TCP0.0.0.0:2701 0.0.0.0:0 LISTENING TCP0.0.0.0:2702 0.0.0.0:0 LISTENING TCP0.0.0.0:3306 0.0.0.0:0 LISTENING TCP0.0.0.0:3389 0.0.0.0:0 LISTENING TCP0.0.0.0:8081 0.0.0.0:0 LISTENING TCP0.0.0.0:13468 0.0.0.0:0 LISTENING TCP127.0.0.1:1074 0.0.0.0:0 LISTENING TCP172.21.138.200:139 0.0.0.0:0 LISTENING TCP172.21.138.200:1074172.21.136.21:139 TIME_WAIT TCP172.21.138.200:3389172.21.138.11:2590 ESTABLISHED TCP172.21.138.200:4534172.21.136.21:135 TIME_WAIT TCP172.21.138.200:4535172.21.136.21:1026 TIME_WAIT TCP172.21.138.200:4537172.21.136.21:135 TIME_WAIT TCP172.21.138.200:4538172.21.136.21:389 TIME_WAIT TCP172.21.138.200:4575172.21.13.139:445 SYN_SENT TCP172.21.138.200:4576172.21.13.140:445 SYN_SENT TCP172.21.138.200:4577172.21.13.141:445 SYN_SENT TCP172.21.138.200:4578172.21.13.142:445 SYN_SENT TCP172.21.138.200:4579172.21.13.143:445 SYN_SENT TCP172.21.138.200:4580172.21.13.144:445 SYN_SENT TCP172.21.138.200:4581172.21.13.145:445 SYN_SENT TCP172.21.138.200:4582172.21.13.146:445 SYN_SENT TCP172.21.138.200:4583172.21.13.147:445 SYN_SENT TCP172.21.138.200:4584172.21.13.148:445 SYN_SENT UDP0.0.0.0:69 *:* UDP0.0.0.0:445*:* UDP0.0.0.0:500*:* UDP0.0.0.0:1025 *:* UDP0.0.0.0:1026 *:* UDP0.0.0.0:1221 *:* UDP0.0.0.0:4500 *:* UDP0.0.0.0:8081 *:* UDP0.0.0.0:8082 *:* UDP127.0.0.1:123 *:* UDP127.0.0.1:1900 *:* UDP127.0.0.1:4542 *:* UDP172.21.138.200:123 *:* UDP172.21.138.200:137 *:* UDP172.21.138.200:138 *:* UDP172.21.138.200:1900*:* C:\wamp\mysql\binmysql -u root -p Enter password: * ERROR 2003: Can't connect to MySQL server on 'localhost' (10055) C:\wamp\mysql\binmysql -u root -p Enter password: ERROR 2003: Can't connect to MySQL server on 'localhost' (10055) -- Thanks Regards Pooja Bajaj Alcatel-Lucent, India Email:pooja.ba...@alcatel-lucent.com Phone:+124-413-3078 | Onnet: 2721-3078 From: Chandru [mailto:chandru@gmail.com] Sent: 12 December 2008 13:40 To: BAJAJ POOJA Cc: chaim.rie...@gmail.com; mysql@lists.mysql.com Subject: Re: MYSQL startup problem Hi, I think you may have to get me the output of netstat -an (this only tells the ports that are all listening). sorry for the confusion. can you connect using mysql -u root -p and also using mysql -u root -p -h 127.0.0.1 one thing that i find here is that you have missed the -p option which prompts for password. Regards, Chandru www.mafiree.com On Fri, Dec 12, 2008 at 1:39 PM, BAJAJ POOJA pooja.ba...@alcatel-lucent.com wrote: Hi, Pls. Find response below: C:\wamp\mysql\binMysql -h localhost -u root ERROR 2003: Can't connect to MySQL server on 'localhost' (10055) -- Thanks Regards Pooja Bajaj Alcatel-Lucent, India Email:pooja.ba...@alcatel-lucent.com mailto:email%3apooja.ba...@alcatel-lucent.com Phone:+124-413-3078 | Onnet: 2721-3078
Re: Importing MS SQL exported CSV files adds spaces
Ali, Saqib wrote: I exported a large data set from from Microsoft SQL server in CSV format. However whenever I try to import that data to a a mySQL server running on Linux, it adds a space between each character in each field. Essentially: Saqib Ali becomes S a q i b A l i I have tried to use the dos2unix cmd on linux, but that didn't help either. I am using the following SQL to import the data: LOAD DATA LOCAL INFILE '/home/saqib/data.csv' INTO TABLE apps FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\n'; You SQL Server export will be Unicode, UTF8 or UTF16 ... thus the appearance of the extra spaces. (That's just coincidence, they're not really extra spaces ... the text editor you're viewing the file with is treating the data as ASCII, and not recognising the multi-byte nature of the characters.) Change your MySQL character set for the apps table appropriately (i.e. make it match the character set of the data), and see what happens. Alternatively, create a staging table with the right character set to load the data into first, and then convert it using an insert ... cast... style statement into your apps table (basic ETL). Ciao Fuzzy :-) Dazed and confused about technology for 20 years http://fuzzydata.wordpress.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bin Files.
Hi all, I had a server pretty much locked up this morning due to the mysql bin logs filling up the /var filesystem. I had been investigating the my.cnf settings file a white back, got sidetracked, and never finished it. The bin logs are named: /var/db/mysql/myserver-bin.01 /var/db/mysql/myserver-bin.02 /var/db/mysql/myserver-bin.03 /var/db/mysql/myserver-bin.04 I assume a new one is created each time the mysql server is started. I had to shut down all services on the server, delete all these files and restart everything. Anywho, the two questions are: 1. Are these files used when 'fixing' tables, or, are they only used for the master/salve replication? 2. Is it safe to, and how do I, turn off the bin logs altogether (there are no slave servers), 3. What my.cnf settings would you all reccomend for: Mysql4, running on, FreeBSD 6, 1 GB Memory, var fs is 5 GB, 250 virtual domains on the server, of which 50 may be using mysql/php (for bulliten boards etc). (I am reading through the /usr/local/share/mysql *.cnf files ...)
Permissions
Hi all, I run a shared Apache, Perl, PHP, Mysql, on FreeBSD environment. Here is a question: IF I have a user, that has no permissions, but with a decent password, (in the mysql 'Users' table), AND that user only has access to his/her database through the local host (i.e. perl or PHP scripts), IS it safe to grant 'All' privs to that user in the database grants table? -Grant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help!
Hi all, I have a table I needed to prune because it had grown to 5 million rows and it appeared that that app that uses it couldnt anymore. I am in the process of: DELETE FROM mytable WHERE id 300; the above has been running for close than an hour and I am starting to get a little nervous. it is on freebsd 6.3, mysql 4.x on a Single core dell 2.8 GHx I think. I have shut down all process but necessary one so mysql has full access to the cpu. Should I be worried? Is there anything I can do to speed this up? -G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Do I need to use GROUP BY to do this?
Hi, nbsp; I have a table where I keep sales transactions, so I'm trying to do a query that will count the number of transactions per day. My test data looks like: -- -- Table structure for table `sales_activity` -- CREATE TABLE `sales_activity` ( nbsp; `sales_id` int(11) NOT NULL auto_increment, nbsp; `sales_date` datetime NOT NULL default '-00-00 00:00:00', nbsp; `sales_type` tinyint(4) NOT NULL default '0', nbsp; PRIMARY KEYnbsp; (`sales_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ; -- -- Dumping data for table `sales_activity` -- INSERT INTO `sales_activity` VALUES (1, '2008-06-15 13:00:00', 1); INSERT INTO `sales_activity` VALUES (2, '2008-06-15 13:00:00', 1); INSERT INTO `sales_activity` VALUES (3, '2008-06-15 13:00:00', 1); INSERT INTO `sales_activity` VALUES (4, '2008-06-15 13:00:00', 1); INSERT INTO `sales_activity` VALUES (5, '2008-06-15 13:00:00', 2); INSERT INTO `sales_activity` VALUES (6, '2008-06-15 13:00:00', 2); INSERT INTO `sales_activity` VALUES (7, '2008-06-16 13:00:00', 1); INSERT INTO `sales_activity` VALUES (8, '2008-06-16 13:00:00', 1); INSERT INTO `sales_activity` VALUES (9, '2008-06-17 13:00:00', 1); INSERT INTO `sales_activity` VALUES (10, '2008-06-17 13:00:00', 1); nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; I would like to get a count of the number of transactions where transaction id=1 for each date. ie, the result set should look like: 2008-06-15nbsp;nbsp;nbsp; 4 2008-06-16 nbsp;nbsp; 2 2008-06-17 nbsp;nbsp; 2 What type of query do I need to get that information? Thanks!
Re: MySQL 5 on OS X Leopard
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I found this a while back and it seems to work just fine. It uses launchd rather than the old /Library/StartupItems folder http://blog.tomatocheese.com/archives/2007/11/1/migrating_mysql_to_mac_os_x_leopard/ Regards, Grant Limberg [EMAIL PROTECTED] On Feb 11, 2008, at 6:34 PM, Unnsse Khan wrote: Hello there, I am having problems running MySQL 5 on OS X Leopard... Nothing happens when I try to start MySQL in System Preferences. From the command line, this is the error I receive (when trying to start MySQL): ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) On the MySQL 5 downloads site, it doesn't have a version for Leopard. Are there any workarounds for getting MySQL 5 running on Leopard? Happy programming, Unnsse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (Darwin) iD8DBQFHsSBWIAfARU4EW68RAkO2AJ4r6qedXM6zwuzVwsSNuoEnbuJbDQCeJdGT XC/14Qt+oiJtidm1u9p63Iw= =TzER -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: finding duplicate key
Something like this should help you find all of the dupes select email_address from table group by email_address having count(*)1; On Feb 11, 2008 4:23 PM, Ferindo Middleton [EMAIL PROTECTED] wrote: I have a table (customers) without a primary key. I want to make the email_address field the primary key, only problem is, several records already share the same email_address How do I write a query which will show me all the instances where email_address is duplicated throughout the table. -- Ferindo -- Grant Limberg [EMAIL PROTECTED]
Crash Recovery Problem
I've got MySQL 5.0.55a running on a Windows XP system. All tables in the active database on the system are innodb. The startup log says that innodb recovery completed and that connections are available. Meanwhile, mysql is writing to the hdd at around 2MB/sec according to process explorer. I can connect to the server fine until I run a query against the database. Then no other connections can issue any command whatsoever. The mysql client just freezes up, and I can't make any new connections to the server either. After a period of time, mysqld-nt.exe crashed and one thread is left running, still writing to the drive at approx 2MB/sec. Does anyone out there have any idea what's going wrong here? I'm stumped. -- Grant Limberg [EMAIL PROTECTED]
Re: Unable to restart after crash
Have you looked at the results of netstat -an ? -Grant - Original Message - From: Ross Crawford To: mysql@lists.mysql.com Sent: Saturday, January 12, 2008 5:12 PM Subject: Unable to restart after crash Hi, My mysql server crashed last night, and when it rebooted, was unable to restart. Here is the error log: Jan 13 00:12:54 localhost mysqld_safe[1324]: started Jan 13 00:12:55 localhost mysqld[1327]: 080113 0:12:55 InnoDB: Database was not shut down normally! Jan 13 00:12:55 localhost mysqld[1327]: InnoDB: Starting crash recovery. Jan 13 00:12:55 localhost mysqld[1327]: InnoDB: Reading tablespace information from the .ibd files... Jan 13 00:12:55 localhost mysqld[1327]: InnoDB: Restoring possible half-written data pages from the doublewrite Jan 13 00:12:55 localhost mysqld[1327]: InnoDB: buffer... Jan 13 00:12:56 localhost mysqld[1327]: 080113 0:12:56 InnoDB: Starting log scan based on checkpoint at Jan 13 00:12:56 localhost mysqld[1327]: InnoDB: log sequence number 0 111349. Jan 13 00:12:56 localhost mysqld[1327]: InnoDB: Doing recovery: scanned up to log sequence number 0 111349 Jan 13 00:12:56 localhost mysqld[1327]: InnoDB: Last MySQL binlog file position 0 3587, file name /var/log/mysql/mysql-bin.000489 Jan 13 00:12:56 localhost mysqld[1327]: 080113 0:12:56 InnoDB: Started; log sequence number 0 111349 Jan 13 00:12:56 localhost mysqld[1327]: 080113 0:12:56 [Note] Recovering after a crash using /var/log/mysql/mysql-bin Jan 13 00:12:56 localhost mysqld[1327]: 080113 0:12:56 [Note] Starting crash recovery... Jan 13 00:12:56 localhost mysqld[1327]: 080113 0:12:56 [Note] Crash recovery finished. Jan 13 00:12:57 localhost mysqld[1327]: 080113 0:12:57 [ERROR] Can't start server: Bind on TCP/IP port: Cannot assign requested address Jan 13 00:12:57 localhost mysqld[1327]: 080113 0:12:57 [ERROR] Do you already have another mysqld server running on port: 3306 ? Jan 13 00:12:57 localhost mysqld[1327]: 080113 0:12:57 [ERROR] Aborting Jan 13 00:12:57 localhost mysqld[1327]: Jan 13 00:12:57 localhost mysqld[1327]: 080113 0:12:57 InnoDB: Starting shutdown... Jan 13 00:12:59 localhost mysqld[1327]: 080113 0:12:59 InnoDB: Shutdown completed; log sequence number 0 111349 Jan 13 00:12:59 localhost mysqld[1327]: 080113 0:12:59 [Note] /usr/sbin/mysqld: Shutdown complete Jan 13 00:12:59 localhost mysqld[1327]: Jan 13 00:12:59 localhost mysqld_safe[1374]: ended And since then I am unable to start it. mysqld_safe aborts with: Starting mysqld daemon with databases from /var/lib/mysql mysqld_safe[3150]: started STOPPING server from pid file /var/run/mysqld/mysqld.pid mysqld_safe[3164]: ended And error file: Jan 13 06:03:06 localhost mysqld_safe[1318]: started Jan 13 06:03:08 localhost mysqld[1321]: 080113 6:03:08 InnoDB: Started; log sequence number 0 111349 Jan 13 06:03:08 localhost mysqld[1321]: 080113 6:03:08 [ERROR] Can't start server: Bind on TCP/IP port: Cannot assign requested address Jan 13 06:03:08 localhost mysqld[1321]: 080113 6:03:08 [ERROR] Do you already have another mysqld server running on port: 3306 ? Jan 13 06:03:08 localhost mysqld[1321]: 080113 6:03:08 [ERROR] Aborting Jan 13 06:03:08 localhost mysqld[1321]: Jan 13 06:03:08 localhost mysqld[1321]: 080113 6:03:08 InnoDB: Starting shutdown... Jan 13 06:03:11 localhost mysqld[1321]: 080113 6:03:11 InnoDB: Shutdown completed; log sequence number 0 111349 Jan 13 06:03:11 localhost mysqld[1321]: 080113 6:03:11 [Note] /usr/sbin/mysqld: Shutdown complete Jan 13 06:03:11 localhost mysqld[1321]: Jan 13 06:03:11 localhost mysqld_safe[1368]: ended Nothing is running on port 3306, telnet gets connection refused. No mysql processes are running. Does anyone have any ideas what might be wrong? Thanks ROSCO -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Total Control Panel Login To: [EMAIL PROTECTED] From: [EMAIL PROTECTED] Remove lists.mysql.com from my allow list You received this message because the domain lists.mysql.com is on your allow list.
Re: preferred way to backup a 20GB MySQL database
The MySQL Administration backup routine is just a front end for mysqldump On Nov 28, 2007 12:51 AM, J Trahair [EMAIL PROTECTED] wrote: What about the MySQL Administration backup routine, which can be scheduled for a convenient time? No-one has mentioned this one. We use it but perhaps there is something I should know...? Thanks Jonathan Trahair - Original Message - From: B. Keith Murphy To: Dan Buettner Cc: David Campbell ; MySql Sent: Wednesday, November 28, 2007 2:55 AM Subject: Re: preferred way to backup a 20GB MySQL database I would echo what Dan says. In addition, from the slave server, you might look at running the new mysql-parallel-dump tool that Baron Schwartz has developed. It essentially does a dump with a thread running (by default) for each CPU core you have. A dual core box will run two threads and dump roughly twice as fast as a normal mysqldump. In addition, it compresses the output making it much more compact. He has renamed the toolkit to Maatkit and it is available at http://maatkit.sourceforge.net/. Also, you might look into using an LVM snapshot to run the copy from. That way it doesn't interfere with your operations as much. I do that for some of our production slave servers myself. Keith Dan Buettner wrote: I'd strongly recommend setting up replication, and then taking your backups from the replica. mysqlhotcopy works great, I used it for years myself, but it does require freezing your database while the copy happens. And no matter how you do it, copying 20 GB takes a little bit of time. Dan On Nov 27, 2007 4:35 PM, David Campbell [EMAIL PROTECTED] wrote: Andras Kende wrote: Hi, What is the preferred way to backup a 20GB database daily, without taking offline ? MySQL 4.1 MyISAM - (will be updated to MySQL 5) 133 table(s) Sum 115,416,561 latin1_swedish_ci 20.1 GB Mysqlhotcopy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Grant Limberg [EMAIL PROTECTED]
Virtual Hosting Privileges
Hi all, This is my first post to this list so bear with me if I ramble on a bit. I have been reading as much as I can on this subject and am yet to feel confident about an answer so I thought I would throw it at this list and see what comes back. One thing I should mention up front is I am in no way a SQL power user, although, I have enough reference material that I can work my way through most problems. We run a small web hosting and design service and have been offering MySQL as part of the basic setup for quite a while without any problems. One of the things we offer it PHPMyAdmin which has server us well so far. Our clients seem to use it as a browsing tool more that anything, but there are a few that might be called 'Power Users that use it for more SQL intense purposes. (FYI, I am using the PMA user, who only has 'select' privileges to the mysql users table). Recently, I noticed something that caused me to reflect on the privileges that I currently have setup on the server, and was wondering if I could get some feedback and/or some light tutoring on the topic of privileges. It is perhaps prudent to start with some setup info: FreeBSD 6.2 - Not using jails. MySQL 4.1 PHP 4.4 - Soon to be upgraded to 5.x Apache 2.2 PHPMyAdmin 2.11.0 What I am looking for at the end of this discussion is if my current privileges are secure enough, and, give all users the maximum functionality within the security model. All mysql users are alos unix users. When I setup the accounts, I use scripts to setup the mysql user at the same time the UNIX users is setup. That having been said, Users can not log into a shell of the operating system, the unix user is setup to give ftp access, and access to thier back end admin panel. When a new mysql user is setup: 1. the user is assinged a password but is given no privileges in the mysql 'users' table, but is allowed to connect from localhost so thier scripts can run; sampleuser localhost XXX None 2. his/her blank database is setup, and that user is given access to is from localhost, although I have a few users that have requested, and were granted connections (via the 'hosts' table) remote access. sampleuser_database sampleuser localhost Select | Insert | Update | Delete | Create | Drop | Grant | References | Index | Alter I suspect that I want to remove the 'Grant' access from the privileges above. Here is where my confusion escelates If i remove it, can the user still add tables? I suspect that I should consider removing the 'Drop' privilege. If I do that will the user be able to drop tables that he/she created? i.e. ones that reside in thier own database? Hopefully, you all can see my confusion. At the end of the day, I gues the question is: What privileges can I grant each user that will only allow them to completely manange thier own database without allowing them access to others databases? or What priveleges should I grant users at setup time? Thanks a million, -Grant P.S. I will also keep reading. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration from Oracle to MySQL
Tangirala, Srikalyan wrote: Hi All: Could you provide some more information about Oracle limitations, MySQL limitations, Oracle vs. MySQL etc? Sure, let's play devil's advocate for a minute. Some things unique to MySQL that Oracle does not offer include: - Storage engines, choices like InnoDB, MyISAM Cluster, give you specialized transactional, search/read optimized and highly available engines for storing your data Storage engines are unique to MySQL? yes. Is that good? YMMV. Most of the purported benefits can be achieved with Oracle's features without the compromises of balkanised storage engines. You're right, they're not offered by Oracle, or anyone else ... there's a reason no other database bothers with storage engines - they got storage right the first time :-) (ooh ... the flames I'll get for that :-) ). Sure, non-volatile data in a MyISAM table can be read at the speed of light, and handle the odd insert. Funnily enough, a text file has the same properties. They both suck for non-trivial concurrent transactions. I'd suggest taking a look at parallel DML, nologging, MVs, partitioning, direct-path insert, appended insert, RAC, ASM, ASSM, etc. etc. etc. in Oracle for more perspective - Fast connections Nope, not unique. Prespawned connections in Oracle are about as fast as it gets for any db, short of using a cached connection. - Easy replication We'll, if by unique and not offered by Oracle you mean you get to experience the MySQL pain of sync'ing the data to start with by any one of numerous half-baked manual methods, sure. Silly old Oracle totally automates that, even giving you several handy GUI or sql options depending on your preference. MySQL definitely wins on the does half the job criteria. - Overall ease of use Easy for who? You're absolutely right for simple installs; a quick db to support a simple web page; the persistence layer for a million and one open source apps that could have chosen any db (mysql, postgres, sqllite, jet, bdb, isam, you name it). But have you ever tried to reorg your physical storage in MySQL with the system online? Get the optimiser to do something intelligent with subselects? Handle transaction semantics across storage engines? Easy isn't the word that springs to mind. (OK, I'm all suited up with the asbestos ... flame away :-) ). Ciao Fuzzy :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Convertion ORACLE query to MYSQL
On 11/9/06, ViSolve DB Team [EMAIL PROTECTED] wrote:
Hi Experts,
We are in the process of converting Oracle administration commands into MySQL
for some purposes. We dont know how to convert the following Oracle commands to
MySQL. How to do that..?
1. ALTER USER spec TEMPORARY TABLESPACE temp_ts;
There's no equivalent concept in MySQL to a temp tablespace. The key
with them in Oracle is actions performed in a temp tablespace aren't
logged (sorts and hashes that overflow the memory), and the files that
constitute them aren't required for recovery.
2. DROP USER jbossjms1 CASCADE;
No equivalent to the cascade option ... so create a script-generating
script, or drop the database if you've equated schema with database.
3. DROP TABLESPACE jbossjms1 INCLUDING CONTENTS;
You normally don't need to muck around with a storage engine's
low-level tablespace management. You might want to work out what
objects this would drop, and do that instead.
4. CREATE TABLESPACE jbossjms1DATAFILE '${JBOSSJMS1}' SIZE 100M REUSE
AUTOEXTEND ON MAXSIZE UNLIMITED;
Ensure the files in your innodb_data_file_path have the autoextend
property set, and total atleast 100MB in size. The Oracle syntax
above uses ASM-based storage, which has no equivalent in MySQL, so
forget that bit
5. CREATE USER jbossjms1 IDENTIFIED BY jbossjms1DEFAULT TABLESPACE jbossjms1;
As per normal user creation ... just ignore the tablespace bit.
Ciao
Fuzzy
:-)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to select data if not in both tables?
Thanks, This worked exactly like I had hoped. Grant Peter Brawley [EMAIL PROTECTED] wrote:Grant, If I want to select all the products that are in the product_table, but not in the sale_table, how to make the query? The product_table has all the products, but the sale table is a subset of the product_table. SELECT * FROM product_table p LEFT JOIN sale_table s USING (prod_id) WHERE s.prod_id IS NULL; PB Grant Giddens wrote: Hi, I have 2 tables like: product_table: prod_id item_name price data data etc sale_table: prod_id sale_price If I want to select all the products that are in the product_table, but not in the sale_table, how to make the query? The product_table has all the products, but the sale table is a subset of the product_table. I tried: SELECT item_name FROM product_table, sale_table WHERE product_table.prod_id != sale_table.prod_id but I'm not getting any results from that. Sorry this code isn't exact, I'm not att my development PC at the moment. Thanks, Grant - Brings words and photos together (easily) with PhotoMail - it's free and works with Yahoo! Mail. - No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.6/257 - Release Date: 2/10/2006 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.6/257 - Release Date: 2/10/2006 - What are the most popular cars? Find out at Yahoo! Autos
How to select data if not in both tables?
Hi, I have 2 tables like: product_table: prod_id item_name price data data etc sale_table: prod_id sale_price If I want to select all the products that are in the product_table, but not in the sale_table, how to make the query? The product_table has all the products, but the sale table is a subset of the product_table. I tried: SELECT item_name FROM product_table, sale_table WHERE product_table.prod_id != sale_table.prod_id but I'm not getting any results from that. Sorry this code isn't exact, I'm not att my development PC at the moment. Thanks, Grant - Brings words and photos together (easily) with PhotoMail - it's free and works with Yahoo! Mail.
Re: Column in field list is ambiguous
Thanks Michael, That was the solution. This was the first time I've seen that
error and now it makes plenty of sense.
Thanks,
Grant
Michael Stassen [EMAIL PROTECTED] wrote: Grant Giddens wrote:
Hi,
I'm using mysql 4.1.14 and and getting this error:
Column 'asin' in field list is ambiguous
This is the query that is giving me the error:
SELECT pn_pricecompare_searchdata.prod_id,
MATCH (asin,upc,...) AGAINST ('warcraft' IN BOOLEAN MODE) AS score
FROM pn_pricecompare_searchdata ps
LEFT JOIN pn_pricecompare_product pp
ON (pp.prod_id = ps.prod_id)
WHERE MATCH (asin,upc,...) AGAINST ('warcraft' IN BOOLEAN MODE)
ORDER BY score DESC LIMIT 0,10
The query works fine if I take out the LEFT JOIN
SELECT pn_pricecompare_searchdata.prod_id,
MATCH (asin,upc,...) AGAINST ('warcraft' IN BOOLEAN MODE) AS score
FROM pn_pricecompare_searchdata ps
LEFT JOIN pn_pricecompare_product pp
ON (pp.prod_id = ps.prod_id)
WHERE MATCH (asin,upc,...) AGAINST ('warcraft' IN BOOLEAN MODE)
ORDER BY score DESC LIMIT 0,10
The searchdata table is MyISAM with fulltext enabled on the fields. The
product table is an INNODB table.
I searched all over and can't find a solution. I did find one possible
bug report that might be related: http://bugs.mysql.com/bug.php?id=15607
Is there any workaround I can use?
Any suggestions would be greatly appreciated.
Thanks,
Grant
Is there a column named asin in the pn_pricecompare_product table? If so, try
qualifying asin with the correct table name in your query (use the alias).
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
-
Relax. Yahoo! Mail virus scanning helps detect nasty viruses!
Column in field list is ambiguous
Hi,
I'm using mysql 4.1.14 and and getting this error:
Column 'asin' in field list is ambiguous
This is the query that is giving me the error:
SELECT pn_pricecompare_searchdata.prod_id, MATCH
(asin,upc,isbn,ean,title,brand,manufacturer,mpn,model,artist,author,platform,format,label,studio,publisher)
AGAINST ('warcraft' IN BOOLEAN MODE) AS score FROM pn_pricecompare_searchdata
LEFT JOIN pn_pricecompare_product ON (pn_pricecompare_product.prod_id =
pn_pricecompare_searchdata.prod_id) WHERE MATCH
(asin,upc,isbn,ean,title,brand,manufacturer,mpn,model,artist,author,platform,format,label,studio,publisher)
AGAINST ('warcraft' IN BOOLEAN MODE) ORDER BY score DESC LIMIT 0,10
The query works fine if I take out the LEFT JOIN
SELECT pn_pricecompare_searchdata.prod_id, MATCH
(asin,upc,isbn,ean,title,brand,manufacturer,mpn,model,artist,author,platform,format,label,studio,publisher)
AGAINST ('warcraft' IN BOOLEAN MODE) AS score FROM pn_pricecompare_searchdata
WHERE MATCH
(asin,upc,isbn,ean,title,brand,manufacturer,mpn,model,artist,author,platform,format,label,studio,publisher)
AGAINST ('warcraft' IN BOOLEAN MODE) ORDER BY score DESC LIMIT 0,10
The searchdata table is MyISAM with fulltext enabled on the fields. The
product table is an INNODB table.
I searched all over and can't find a solution. I did find one possible bug
report that might be related: http://bugs.mysql.com/bug.php?id=15607
Is there any workaround I can use?
Any sugguestions would be greatly appreciated.
Thanks,
Grant
-
Brings words and photos together (easily) with
PhotoMail - it's free and works with Yahoo! Mail.
Re: innodb questions
Ware, Thanks so much for your help. This is what I'm planning on doing, please let me know if you see any problems here. This is on my test server, so losing data isn't the end of the world. This is what I plan on doing in order: 1. Backup the database via mysqldump 2. Purge the master logs via PURGE MASTER LOGS command. Maybe I should just delete them? It's a test server and I don't plan on going back to a previous snapshot. 3. Shut down the mysql server 4. Edit my.cnf to remove log-bin 5. Edit my.cnf to add innodb_file_per_table 6. Move the tablespace ( I guess this is the ib_logfile0, ib_logfile1, and ibdata1 files) 7. Start the mysql server. I think at this point the table space will be recreated. Am I right? 8. Re-import my data from my file at step 1. This leads me to several questions though: 1. My web host server is running CentOS linux. How do I know it's maximum filesize? Should I limit the ibdata1 type files such as: innodb_data_file_path = /ibdata/ibdata1:100M:autoextend:max:2000M What if my table is larger than 2G? 2. If I change my tables (add/drop index and such) do I need to do this mysqldump, recreate my tablespace and re-imporrt my data process each time? 3. What if I'm running multiple databases with innodb tables on one mysql server? I guess if I use innodb_file_per_table it will help me keep the file sizes down and make it easier to drop and reload individual databases. Am I correct here? Thanks, Grant Ware Adams [EMAIL PROTECTED] wrote: On Jan 31, 2006, at 9:54 PM, Grant Giddens wrote: Since changing these tables, I've noticed some large files in my / var/lib/mysql directory. This is on my test server and I'm running gentoo linux. The files in this directory look like: /var/lib/mysql/gentoo1-bin.01 (1 Gig in size) /var/lib/mysql/gentoo1-bin.01 (1 Gig in size) /var/lib/mysql/gentoo1-bin.01 (1 Gig in size) /var/lib/mysql/gentoo1-bin.01 (1 Gig in size) ... /var/lib/mysql/gentoo1-bin.60 (1 Gig in size) These all look like binary log files, they aren't exclusive to InnoDB. You must have enabled binary logging in your my.cnf file as well. This is covered pretty extensively in the manual. Basically they are used for replication and recovery. In the latter you can take a point in time snapshot from a known binary log position and then if need be apply the logs (which is basically re-running the queries in them) to that to get back to where you databases was at any point in time. If you don't need any of these functions you can either get rid of the files or turn off binary logging. However, you probably don't want to just delete them from the file system. Have a look at the 'show master logs' and 'purge master logs' commands. This will delete the files and keep mysqld's internal index of the binary logs accurate. /var/lib/mysql/ibdata1(10.0 Gig in size) This is your InnoDB table space, you need it. You also need your ib_logfile0 etc... files. 3. Can I limit the size of the ibdata1 file? Only by limiting data in your tables. Also, in the shared table space (which you are using) you can't shrink this file. You can switch to innodb_file_per_table (see InnoDB manual) so that when you drop a table you save it's space (but not on deletion of individual records). However, even doing this you cannot delete your ibdata1 file or any shared tablespace files. You can recreate your entire tablespace (see below), but even then you'll need a (small) shared ibdata file. Is it too late to resize it? Yes, but you could use mysqldump to dump all data to text files, delete (or move) the tablespace, redefine it and then re-import. 4. What can I set to reduce the size of these files? Use innodb_file_per_table turn off binary loggin if you don't need it make sure index and field types are appropriate My innodb variables are: These came through poorly spaced, but I think it would help a lot to read the InnoDB sections of the manual (it's pretty manageable in size). InnoDB is really fantastic for certain applications, including heavy write load to large tables with concurrent reads. We've used it for several years on several billion records with 170 qps 50% of which is writes. There's no way to do this in MySQL other than InnoDB, realistically. That said, it has it's own learning curve. It's really an entirely new database engine, so there's lots to learn even if you've used mysql/myisam for years. In particular the tablespace layout and dependency on files other than ones linked to a particular table is a little daunting. --Ware - Bring words and photos together (easily) with PhotoMail - it's free and works with Yahoo! Mail.
innodb questions
Hi, I recently converted some of my tables to innodb from myisam. I don't need transactions or rollback capability, I switched because I needed row-level locking. These are large tables with many rows and lots of INSERTS and UPDATES. Since changing these tables, I've noticed some large files in my /var/lib/mysql directory. This is on my test server and I'm running gentoo linux. The files in this directory look like: /var/lib/mysql/gentoo1-bin.01 (1 Gig in size) /var/lib/mysql/gentoo1-bin.01 (1 Gig in size) /var/lib/mysql/gentoo1-bin.01 (1 Gig in size) /var/lib/mysql/gentoo1-bin.01 (1 Gig in size) ... /var/lib/mysql/gentoo1-bin.60 (1 Gig in size) /var/lib/mysql/ibdata1(10.0 Gig in size) Since I'm new to innodb tables, I assume that's where these files came from. Based on these file sizes, I need to get rid of them to save some disk space. My questions are: 1. Do I need them? 2. Can I delete them? 3. Can I limit the size of the ibdata1 file? Is it too late to resize it? 4. What can I set to reduce the size of these files? My innodb variables are: Variable Session value Global value innodb additional mem pool size33554432 33554432 innodb autoextend increment8 8 innodb buffer pool awe mem mb 0 0 innodb buffer pool size 536870912 536870912 innodb data file path ibdata1:10M:autoextend ibdata1:10M:autoextend innodb data home dir innodb fast shutdown ON ON innodb file io threads4 4 innodb file per table OFF OFF innodb flush log at trx commit 1 1 innodb flush method innodb force recovery 0 0 innodb lock wait timeout50 50 innodb locks unsafe for binlogOFF OFF innodb log arch dir innodb log archive OFF OFF innodb log buffer size8388608 8388608 innodb log file size5242880 5242880 innodb log files in group 2 2 innodb log group home dir./ ./ innodb max dirty pages pct90 90 innodb max purge lag 0 0 innodb mirrored log groups 1 1 innodb open files300 300 innodb table locksON ON innodb thread concurrency 8 8 Please let me know if I need to post any more variables. Thanks, Grant - Yahoo! Autos. Looking for a sweet ride? Get pricing, reviews, more on new and used cars.
Re: Most efficient way to design this table
How did you get these byte calculations? I thought an INT took up 4 bytes and char(10) would take 10 bytes. http://dev.mysql.com/doc/refman/4.1/en/storage-requirements.html C.R.Vegelin [EMAIL PROTECTED] wrote: Hi Grant, I suggest to change both key fields to Integers. Numeric keys take less space and are faster. Your Product_Feature table then may have: - product_id INT unsigned - feature INT unsigned having a Primary Key of 8 bytes i.s.o. 141 bytes. Thus a smaller index, less disk reads and more speed. HTH, Cor Vegelin - Original Message - From: Grant Giddens To: Sent: Tuesday, January 17, 2006 2:28 AM Subject: Most efficient way to design this table Hi, I currently have a table with 2 columns, product_id and feature. Each product_id can have multiple features. My two columns are: product_id is type char(13) feature is type varchar(128) In order to make sure I don't have the same feature listed twice for a particular product, I have set the PRIMARY key on product_id and feature. I have lots of products and lots of features. Is design bloating my available key_buffer memory with too much data? Is there a better way to index this data? Thanks, Grant - Yahoo! Photos Ring in the New Year with Photo Calendars. Add photos, events, holidays, whatever. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] - Yahoo! Photos Got holiday prints? See all the ways to get quality prints in your hands ASAP.
Re: key_buffer_size vs innodb_buffer_pool_size
I don't know if I still fully understand. Say I have a database with a good mix of myisam and innodb type tables. If my server has 1 gig of ram and I set key_buffer_size=256M and innodb_buffer_pool_size=256M then wouldn't mysql be constrained to 512M of the ram and leave the remaining 512M to the other server services? For a large database, are they any guides that I can use to determine how much ram would be optimal for my particular database? Thanks, Grant Eric Bergen [EMAIL PROTECTED] wrote: The difference in recommendation size comes from the different techniques each storage engine uses for caching data. myisam (key_buffer_size) only stores indexes where innodb_buffer_pool_size stores both indexes and data. mysiam relies on the operating system to cache data in ram which is why you don't want to use all available memory for the key buffer. On 1/14/06, Grant Giddens wrote: Hi, After reading through the example my.cnf files (large, huge, etc), I started to wonder what the difference was between the isam key_buffer_size and the innodb innodb_buffer_pool_size. I realize that they are two different table types, but some of the docs says to set the key_buffer_size to 25%-50% of the overall system memory. The comments for the innodb_buffer_pool_size say that it can be set to 50%-80% of the overall system memory. Maybe I don't understand exactly the difference between the two because I don't understand why they have different memory recommendations. Is there any FAQs on the my.cnf file? How would you set these two variables if you had an even mix of isam and innodb tables? Where can I learn more about tweaking the my.cnf file? The mysql online documentation is good, but I need a more basic description of these two variables and all the other my.cnf settings. Thanks, Grant - Yahoo! Photos Got holiday prints? See all the ways to get quality prints in your hands ASAP. -- Eric Bergen [EMAIL PROTECTED] http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] - Yahoo! Photos Showcase holiday pictures in hardcover Photo Books. You design it and well bind it!
Most efficient way to design this table
Hi, I currently have a table with 2 columns, product_id and feature. Each product_id can have multiple features. My two columns are: product_id is type char(13) feature is type varchar(128) In order to make sure I don't have the same feature listed twice for a particular product, I have set the PRIMARY key on product_id and feature. I have lots of products and lots of features. Is design bloating my available key_buffer memory with too much data? Is there a better way to index this data? Thanks, Grant - Yahoo! Photos Ring in the New Year with Photo Calendars. Add photos, events, holidays, whatever.
key_buffer_size vs innodb_buffer_pool_size
Hi, After reading through the example my.cnf files (large, huge, etc), I started to wonder what the difference was between the isam key_buffer_size and the innodb innodb_buffer_pool_size. I realize that they are two different table types, but some of the docs says to set the key_buffer_size to 25%-50% of the overall system memory. The comments for the innodb_buffer_pool_size say that it can be set to 50%-80% of the overall system memory. Maybe I don't understand exactly the difference between the two because I don't understand why they have different memory recommendations. Is there any FAQs on the my.cnf file? How would you set these two variables if you had an even mix of isam and innodb tables? Where can I learn more about tweaking the my.cnf file? The mysql online documentation is good, but I need a more basic description of these two variables and all the other my.cnf settings. Thanks, Grant - Yahoo! Photos Got holiday prints? See all the ways to get quality prints in your hands ASAP.
Can I do a boolean search and get the row count in 1 SQL query?
Hi,
I have a web app where I am doing a boolean search. I only want
to return 10 results per page, but I'd also like to know how many total
rows match the search query. I'm currently performing this with 2
query statements:
1. (To get the actual rows via the search)
SELECT $product_column[title],
MATCH (title) AGAINST ('$q' IN BOOLEAN MODE)
AS score FROM $product_table
WHERE MATCH (title) AGAINST ('$q' IN BOOLEAN MODE)
ORDER BY score DESC
LIMIT $lower_limit,10
2. (To get the total number of results)
SELECT COUNT(*) as num_results
MATCH (title) AGAINST ('$q' IN BOOLEAN MODE)
AS score FROM $product_table
WHERE MATCH (title) AGAINST ('$q' IN BOOLEAN MODE)
ORDER BY score DESC
The queries might be a bit off as I don't have the exact code in
front of me right now.
Is there a way I can combine this into 1 query?
Thanks,
Grant
-
Yahoo! Photos
Ring in the New Year with Photo Calendars. Add photos, events, holidays,
whatever.
Can I go a boolean search and get the row count in 1 SQL query?
Hi,
I have a web app where I am doing a boolean search. I only want to return
10 results per page, but I'd also like to know how many total rows match the
search query. I'm currently performing this with 2 query statements:
1. (To get the actual rows via the search)
SELECT $product_column[title],
MATCH (title) AGAINST ('$q' IN BOOLEAN MODE)
AS score FROM $product_table
WHERE MATCH (title) AGAINST ('$q' IN BOOLEAN MODE)
ORDER BY score DESC
LIMIT $lower_limit,10
2. (To get the total number of results)
SELECT COUNT(*) as num_results
MATCH (title) AGAINST ('$q' IN BOOLEAN MODE)
AS score FROM $product_table
WHERE MATCH (title) AGAINST ('$q' IN BOOLEAN MODE)
ORDER BY score DESC
The queries might be a bit off as I don't have the exact code in front of me
right now.
Is there a way I can combine this into 1 query?
Thanks,
Grant
-
Yahoo! Photos
Ring in the New Year with Photo Calendars. Add photos, events, holidays,
whatever.
Re: Do I need to tweak my server variables for this SELECT statement?
Thanks Hank. I'll try to split up the query into 2 separate queries. I think that the reason the original query is so slow is that I don't have enough RAM allocated to mysql. When the original query takes place, I see a process Copying to tmp table on disk. I believe it's writing all the data to the disk and then sorting it. I'd like to try tweaking the my.cnf file to allow mysql to use more RAM.I just need someone to help me edit the file because I'm not quite sure what I'm doing... Thanks, Grant Hank [EMAIL PROTECTED] wrote: Now that I know what's causing the slow queries, what can I do to fix it? The only thing I can suggest is breaking up the query into two parts - the first part to retrieve just the product codes and salesrank, and sort and limit that.. save in a temp table or use application code to retrieve and print the rest of the product info. Sorting 300,000+ records in that huge result set is going to take some time (although it shouldn't take 10 minutes). -Hank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] - Yahoo! DSL Something to write home about. Just $16.99/mo. or less
Re: Do I need to tweak my server variables for this SELECT statement?
Thanks James. I'll definately give that a try. My test server has 1 gig of ram. In the my-huge.cnf example it says that it's mainly for servers that have mysql as the main process. On my production server, I have 1 gig of ram, but it also runs apache, mutliple webstites, mysql, DNS, ftp server, etc... It's a dedicated server that only hosts my sites so I can tweak the configuration. Should I use the my-large.cnf as a starting point, or should I be OK with my-huge.cnf? Thanks, Grant James Harvard [EMAIL PROTECTED] wrote:The reason I suggested that you just use the alternative my-huge.cnf file is because that is a ready-prepared config file optimised for systems with lots of RAM for MySQL to use. You don't need to know which variable to change - it's already done for you. You may want/need to tweak stuff later, of course, but my-huge.cnf is a better starting point than my.cnf. James Harvard At 6:45 am -0800 23/12/05, Grant Giddens wrote: I think that the reason the original query is so slow is that I don't have enough RAM allocated to mysql. When the original query takes place, I see a process Copying to tmp table on disk. I believe it's writing all the data to the disk and then sorting it. I'd like to try tweaking the my.cnf file to allow mysql to use more RAM. I just need someone to help me edit the file because I'm not quite sure what I'm doing... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] - Yahoo! Shopping Find Great Deals on Holiday Gifts at Yahoo! Shopping
Re: Do I need to tweak my server variables for this SELECT statement?
Hi James. Thanks for the tips. I tried your below SQL call of quering just one column. The query didn't speed up. I think I am running out of RAM and thus caching the temp table to disk. My server is currently using the default my.cnf file. I will try the large and huge example .cnf files to increate table cache limits. Like I mentioned before, I am tweaking the .cnf files by blind trial and error. I would appreciate anyone with experience looking over my current setup and proposed setup. I'm not sure how much RAM to allocate to each server variable. Here is a link to my current server variables: http://retailretreat.com/mysql/server_variables.php.htm Here is a link to my current my.cnf file: http://retailretreat.com/mysql/my.cnf.txt Here is a link to my proposed my.cnf file. I'm not sure if there are any errors or mistakes in the file. http://retailretreat.com/mysql/my-new.cnf.txt Thanks, Grant James Harvard [EMAIL PROTECTED] wrote:I've recently been doing some big table query optimisation, but after getting the query tweaked to hit the right index the query time is under very livable-with, despite the fact that MySQL seems to be examining more rows for my query than for yours. However the 'rows' column of thhe explain output is just an estimate. I'm not an expert on this subject, but I do have a couple of ideas that should only take you a couple of minutes to test, until a big-table-guru steps in. :-) You could try starting MySQL using the 'mysql/support-files/my-huge.cnf' example config file to see if that makes any difference. This is for a large system with memory of 1G-2G where the system runs mainly MySQL. It may be you're running into a limit on the size of temporary table MySQL will build in RAM, so it's doing it on disc instead. I would try this first. Also - and this really is just a guess - I wonder whether part of the problem is that you're retrieving a lot of data (lots of columns) and then sorting the resulting huge temporary table, only to use the first 10 rows. Try just selecting just one row to see if that helps. If it does then you could maybe use a sub-query or application code to feed the list of 10 prod_id values into a query that gets all the columns you need. SELECT pn_pricecompare_product.prod_id FROM pn_pricecompare_catprod, pn_pricecompare_product WHERE ((pn_pricecompare_catprod.category = '283155') AND (pn_pricecompare_catprod.asin = pn_pricecompare_product.asin)) ORDER BY pn_pricecompare_product.salesrank ASC LIMIT 0,10 It might be the case that you could create an index that MySQL could use to optimise the 'order by', retrieving the rows in sorted order, but I'm not up on that sort of optimisation and don't know for sure - you'd have to check out the relevant manual section. Another factor may be the table format - fixed versus dynamic row length. If you need to come back on this maybe we could see a 'show create table' for the two tables? Also if you enable the slow query log, it tells you the actual number of rows examined - might be useful if you continue to have trouble. HTH good luck, James Harvard For the first time, I'm working with a really large database. I have 1 SQL statement that brings my server to it's knees. This setup is currently on my home development PC, and not in production. The server is running apache, samba, and mysql under gentoo linux. I'm the only user, so there is no vitually load on the server. The server has 1 Gig of ram. I've got 2 tables, one that holds a list of product, the other holds a list of categories that the product is associated with. My SELECT statment just grabs 10 products that are associated with a specific category. The product table has 650,000 rows and the category table has 8,150,000 rows. My SELECT statement is: SELECT pn_pricecompare_catprod.category, pn_pricecompare_catprod.asin, pn_pricecompare_product.title, pn_pricecompare_product.prod_id, pn_pricecompare_product.image_small, pn_pricecompare_product.brand, pn_pricecompare_product.manufacturer, pn_pricecompare_product.mpn, pn_pricecompare_product.model, pn_pricecompare_product.artist, pn_pricecompare_product.author, pn_pricecompare_product.binding, pn_pricecompare_product.label, pn_pricecompare_product.audiencerating, pn_pricecompare_product.studio, pn_pricecompare_product.releasedate, pn_pricecompare_product.numberofpages, pn_pricecompare_product.pubdate, pn_pricecompare_product.publisher, pn_pricecompare_product.searchindex, pn_pricecompare_product.lowest_price, pn_pricecompare_product.num_merchants FROM pn_pricecompare_catprod, pn_pricecompare_product WHERE ((pn_pricecompare_catprod.category = '283155') AND (pn_pricecompare_catprod.asin = pn_pricecompare_product.asin)) ORDER BY pn_pricecompare_product.salesrank ASC LIMIT 0,10 Sometimes this takes 10 minutes
Re: Do I need to tweak my server variables for this SELECT statement?
Thank Hank. I will try this. When you say the table descriptions, do you mean for me to post my CREATE TABLE syntax of how I created the table? Thanks, Grant Hank [EMAIL PROTECTED] wrote: I don't think the problem is going to be solved with the my.cnf file. Here's what I would try.. 1- run and time the original query without the ORDER BY or LIMIT clauses 2- run and time the following breakdown queries, to see if the indexes are at least working correctly: -- test catprod SELECT pn_pricecompare_catprod.category, pn_pricecompare_catprod.asin FROM pn_pricecompare_catprod WHERE pn_pricecompare_catprod.category = '283155' -- test product SELECT pn_pricecompare_product.title, pn_pricecompare_product.prod_id, pn_pricecompare_product.image_small, pn_pricecompare_product.brand, pn_pricecompare_product.manufacturer, pn_pricecompare_product.mpn, pn_pricecompare_product.model, pn_pricecompare_product.artist, pn_pricecompare_product.author, pn_pricecompare_product.binding, pn_pricecompare_product.label, pn_pricecompare_product.audiencerating, pn_pricecompare_product.studio, pn_pricecompare_product.releasedate, pn_pricecompare_product.numberofpages, pn_pricecompare_product.pubdate, pn_pricecompare_product.publisher, pn_pricecompare_product.searchindex, pn_pricecompare_product.lowest_price, pn_pricecompare_product.num_merchants FROM pn_pricecompare_product WHERE pn_pricecompare_product.asin IN () ORDER BY pn_pricecompare_product.salesrank ASC LIMIT 0,10 -- test just getting a count of the join result SELECT count(*) FROM pn_pricecompare_catprod, pn_pricecompare_product WHERE pn_pricecompare_catprod.category = '283155' AND pn_pricecompare_catprod.asin = pn_pricecompare_product.asin The results of those queries should shed some light on where the problem is being introduced. Also, table descriptions of both tables would be helpful in locating the problem. -Hank __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: Do I need to tweak my server variables for this SELECT statement?
IN
('0684824906','140004314X','1594480001','0439784549','031615976X','0316172324','0060765313','0963679600','0743226712','0060817089')
ORDER BY pn_pricecompare_product.salesrank ASC
LIMIT 0,10
Execution time: 0.0019s (Fast!)
Explanation:
table typepossible_keyskey key_lenref
rowsExtra
pn_pricecompare_product range asin asin 10 NULL 10
Using where; Using filesort
-
Query to test the joins:
SELECT count(*)
FROM
pn_pricecompare_catprod,
pn_pricecompare_product
WHERE
pn_pricecompare_catprod.category = '283155' AND
pn_pricecompare_catprod.asin = pn_pricecompare_product.asin
Execution time: phpmyadmin didn't report a time, but it was pretty quick.
Explanation:
table typepossible_keyskey key_lenref
rowsExtra
pn_pricecompare_catprod ref PRIMARY,asin PRIMARY 4
const 369308 Using where; Using index
pn_pricecompare_product eq_ref asin asin 10
pn_pricecompare_catprod.asin 1 Using index
--
Basically it looks like it's the ORDER BY and LIMIT clause that makes my
original query take so long. All the other queries were pretty fast. Any
idea what is wrong with my original query?
Thanks for everyones help.
Grant
Hank [EMAIL PROTECTED] wrote: I don't think the problem is going to be
solved with the my.cnf file.
Here's what I would try..
1- run and time the original query without the ORDER BY or LIMIT clauses
2- run and time the following breakdown queries, to see if the
indexes are at least working correctly:
-- test catprod
SELECT
pn_pricecompare_catprod.category,
pn_pricecompare_catprod.asin
FROM pn_pricecompare_catprod
WHERE
pn_pricecompare_catprod.category = '283155'
-- test product
SELECT
pn_pricecompare_product.title,
pn_pricecompare_product.prod_id,
pn_pricecompare_product.image_small,
pn_pricecompare_product.brand,
pn_pricecompare_product.manufacturer,
pn_pricecompare_product.mpn,
pn_pricecompare_product.model,
pn_pricecompare_product.artist,
pn_pricecompare_product.author,
pn_pricecompare_product.binding,
pn_pricecompare_product.label,
pn_pricecompare_product.audiencerating,
pn_pricecompare_product.studio,
pn_pricecompare_product.releasedate,
pn_pricecompare_product.numberofpages,
pn_pricecompare_product.pubdate,
pn_pricecompare_product.publisher,
pn_pricecompare_product.searchindex,
pn_pricecompare_product.lowest_price,
pn_pricecompare_product.num_merchants
FROM pn_pricecompare_product
WHERE
pn_pricecompare_product.asin IN ()
ORDER BY pn_pricecompare_product.salesrank ASC
LIMIT 0,10
-- test just getting a count of the join result
SELECT count(*)
FROM
pn_pricecompare_catprod,
pn_pricecompare_product
WHERE
pn_pricecompare_catprod.category = '283155' AND
pn_pricecompare_catprod.asin = pn_pricecompare_product.asin
The results of those queries should shed some light on where the
problem is being introduced.
Also, table descriptions of both tables would be helpful in locating
the problem.
-Hank
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Re: Do I need to tweak my server variables for this SELECT statement?
It'll be a few hours before I get home and can do this query with the variations you mentioned. I don't know if I need the separate index key on `salesrank` or not. I thought it would speed up my query be having it. In the previous email I included the results from doing an EXPLAIN. It shows: tabletype possible_keys keykey_len refrowsExtra pn_pricecompare_catprod ref PRIMARY,asin PRIMARY 4 const 369308 Using where; Using index; Using temporary; Using f... pn_pricecompare_product eq_ref asin asin 10 pn_pricecompare_catprod.asin 1 The formatting is funky from my cut and paste, but it looks like 369,308 rows for this particular query. I think the product table has about 650,000 rows, and the category table has about 8,150,000 rows. Thanks, Grant Hank [EMAIL PROTECTED] wrote: Ok, so the next step would be to try the original query with just the LIMIT clause, and then just the ORDER BY (but not both). The results of select count(*) query would be helpful to know just how many records mysql is trying to sort and limit. And do you really need a separte index key on `salesrank` on the product table? -Hank __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: Do I need to tweak my server variables for this SELECT statement?
OK, I tried the following queries: 1. Use the ORDER BY and not the LIMIT -- still slow 2. Use the LIMIT and not the ORDER BY -- fast So it looks like it's building a whole temp table with all the items and then doing the ORDER BY. Just as a test, I removed the INDEX from the `salesrank` column. It didn't make a difference as my results were just like the ones above. Now that I know what's causing the slow queries, what can I do to fix it? Thanks, Grant Hank [EMAIL PROTECTED] wrote: Ok, so the next step would be to try the original query with just the LIMIT clause, and then just the ORDER BY (but not both). The results of select count(*) query would be helpful to know just how many records mysql is trying to sort and limit. And do you really need a separte index key on `salesrank` on the product table? -Hank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Do I need to tweak my server variables for this SELECT statement?
Hi, For the first time, I'm working with a really large database. I have 1 SQL statement that brings my server to it's knees. This setup is currently on my home development PC, and not in production. The server is running apache, samba, and mysql under gentoo linux. I'm the only user, so there is no vitually load on the server. The server has 1 Gig of ram. I've got 2 tables, one that holds a list of product, the other holds a list of categories that the product is associated with. My SELECT statment just grabs 10 products that are associated with a specific category. The product table has 650,000 rows and the category table has 8,150,000 rows. My SELECT statement is: SELECT pn_pricecompare_catprod.category, pn_pricecompare_catprod.asin, pn_pricecompare_product.title, pn_pricecompare_product.prod_id, pn_pricecompare_product.image_small, pn_pricecompare_product.brand, pn_pricecompare_product.manufacturer, pn_pricecompare_product.mpn, pn_pricecompare_product.model, pn_pricecompare_product.artist, pn_pricecompare_product.author, pn_pricecompare_product.binding, pn_pricecompare_product.label, pn_pricecompare_product.audiencerating, pn_pricecompare_product.studio, pn_pricecompare_product.releasedate, pn_pricecompare_product.numberofpages, pn_pricecompare_product.pubdate, pn_pricecompare_product.publisher, pn_pricecompare_product.searchindex, pn_pricecompare_product.lowest_price, pn_pricecompare_product.num_merchants FROM pn_pricecompare_catprod, pn_pricecompare_product WHERE ((pn_pricecompare_catprod.category = '283155') AND (pn_pricecompare_catprod.asin = pn_pricecompare_product.asin)) ORDER BY pn_pricecompare_product.salesrank ASC LIMIT 0,10 Sometimes this takes 10 minutes to execute. When this occurs, I can hear the hard drive thrashing. If I do an EXPLAIN, I get: table typepossible_keys keykey_len ref rowsExtra pn_pricecompare_catprod ref PRIMARY,asin PRIMARY 4 const 355416 Using where; Using index; Using temporary; Using f... pn_pricecompare_product eq_ref asin asin 10 pn_pricecompare_catprod.asin 1 When the query executes, and I check the processes, I see Copying to tmp table on disk After googling for this statement I found several pages that indicate I might have to tweak the my.cnf file. I checked my my.cnf file, and it's just the default file. I found the example huge, large, medium, and small .cnf files and plan on using them to try to optimize my my.cnf file. Other than trial and error, I really don't know what I'm doing. Here is a link to my current server variables: http://retailretreat.com/mysql/server_variables.php.htm Here is a link to my current my.cnf file: http://retailretreat.com/mysql/my.cnf.txt Here is a link to my proposed my.cnf file. I'm not sure if there are any errors or mistakes in the file. http://retailretreat.com/mysql/my-new.cnf.txt Am I right that I need to tweak the my.cnf file? I think I optimized my tables with INDEXes correctly already. What should I look for first? Thanks, Grant __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Can I weight full-text searches in boolean mode?
Hi, I have a table with consumer products. I have a row product name and brand. If I do a full-text search in boolean mode for a term like apple ipod I get results like: Apple iPod (brand: Apple) TuneDock for Apple iPod (brand: Belkin) Since both of these terms match apple and ipod they are returned with the same score. Since apple also matches the brand for the product from Apple, I'd like to give it a little boost when ranking the score so that it is returned with a higher score. I think this would give me better search results. Right now both of these are returned with a score of 2, since they both have 2 matching words. If I could weight it, I would want: Apple iPod (brand: Apple) (score=3) TuneDock for Apple iPod (brand: Belkin) (score=2) Is this possible? Thanks, Grant - Yahoo! Personals Single? There's someone we'd like you to meet. Lots of someones, actually. Try Yahoo! Personals
Can I weight full-text searches in boolean mode?
Hi, I have a table with consumer products. I have a row product name and brand. If I do a full-text search in boolean mode for a term like apple ipod I get results like: Apple iPod (brand: Apple) TuneDock for Apple iPod (brand: Belkin) Since both of these terms match apple and ipod they are returned with the same score. Since apple also matches the brand for the product from Apple, I'd like to give it a little boost when ranking the score so that it is returned with a higher score. I think this would give me better search results. Right now both of these are returned with a score of 2, since they both have 2 matching words. If I could weight it, I would want: Apple iPod (brand: Apple) (score=3) TuneDock for Apple iPod (brand: Belkin) (score=2) Is this possible? Thanks, Grant - Yahoo! Music Unlimited - Access over 1 million songs. Try it free.
Need help with fulltext search and left join
Hi,
I keep getting errors on this query and I'm not sure
why. I'm using mysql version 4.0.22.
Any ideas?
SELECT pn_coupons_store.store_name,
pn_coupons_store.store_name_short,
pn_coupons_coupons.store_id,
pn_coupons_coupons.coupon_id,
pn_coupons_dealtype.dealtype_name,
pn_coupons_coupons.coupon_desc,
pn_coupons_coupons.coupon_link,
pn_coupons_coupons.use_preurl,
pn_coupons_coupons.coupon_tracking,
pn_coupons_coupons.coupon_banner,
pn_coupons_coupons.coupon_drm,
pn_coupons_coupons.coupon_code,
pn_coupons_coupons.coupon_legal_text,
pn_coupons_coupons.coupon_submitter,
pn_coupons_coupons.coupon_added,
pn_coupons_coupons.coupon_start,
pn_coupons_coupons.coupon_exp,
pn_coupons_coupons.coupon_hits,
pn_coupons_network.network_id, MATCH (coupon_desc)
AGAINST ('electronics' IN BOOLEAN MODE) AS score FROM
pn_coupons_coupons WHERE MATCH (store_name) AGAINST
('electronics' IN BOOLEAN MODE), LEFT JOIN
pn_coupons_store ON (pn_coupons_store.store_id =
pn_coupons_coupons.store_id) LEFT JOIN
pn_coupons_dealtype ON
(pn_coupons_dealtype.dealtype_id =
pn_coupons_coupons.dealtype_id) LEFT JOIN
pn_coupons_network ON (pn_coupons_network.network_id =
pn_coupons_store.store_network_id) ORDER BY score DESC
Thanks,
Grant
__
Yahoo! FareChase: Search multiple travel sites in one click.
http://farechase.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
QUERY ordering clarification
Hi, If I do a query like: SELECT prodname, price FROM prod_table WHERE sku in ($sku1, $sku2, $sku3, $sku4) Will my results always be ordered in $sku1, $sku2, $sku3, $sku4 order? I can't really do a ORDER BY prodname or price here. I just want to make sure that that this type of query will always return the results in $sku1, $sku2, $sku3, $sku4 order. Thanks, Grant __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT help
Hi, I am tring to do a select from 2 tables. Table1: sku title Table 2: sku feature SELECT table1.title, table2.feature FROM table1, table2 WHERE table1.sku in ($sku1, $sku2, $sku3) ORDER BY FIELD(table1.sku, $sku1, $sku2, $sku3) ASC That seems to work to some extint, but I am getting way too many results (more than 3). It's returning all combinations of sku and feauture even if they don't share the same sku. I modified the select to: SELECT table1.title, table2.feature FROM table1, table2 WHERE (table1.sku = $table2.sku) AND table1.sku in ($sku1, $sku2, $sku3) ORDER BY FIELD(table1.sku, $sku1, $sku2, $sku3) ASC That seemed to work almost correctly. I have some items in table2 that don't have a feature and therefor don't have a row associated with them. For example, if I have 3 items in each table, the above select works fine. If I have 3 items in table1 and 2 items in table2 the above query only gives me 2 results. table1 will always be fully populated and table2 might be missing some features. How can I run my query to get 3 results and if the feature is missing still return the table.title and NULL for the feature? Thanks, Grant __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Retrieving partial field values
On Wed, 5 Jan 2005 08:19:07 -0800 (PST) Kentucky Families [EMAIL PROTECTED] wrote: This is a huge database so the option of using LIKE to bring up everything beginning with the search term will result in too many hits. I need a way to isolate these entries and search them on whole words. If you have control over input format then you could store multiple values as say, ':Stott::Statt::Stutt:' and then search thus: SELECT * FROM people WHERE surname LIKE '%:statt:%' which will only match whole words. Better would be regular expressions capable of matching word boundaries, like perl's \b and \B patterns, but MySQL doesn't do that AFAIK. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB: Error: tablespace size stored in header is 17024 pages,
On Thu, 6 Jan 2005 11:30:33 +0200 Heikki Tuuri [EMAIL PROTECTED] wrote: innodb_data_file_path = ibdata2:7M;ibdata1:10M:autoextend You have to add new data files to the end of the line, not to the start. Please follow the instructions at http://dev.mysql.com/doc/mysql/en/Adding_and_removing.html Thanks. I realised this when I woke up in the middle of the night! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Retrieving partial field values
On Wed, 05 Jan 2005 12:22:18 +
Stephen Moretti [EMAIL PROTECTED] wrote:
Kentucky Families wrote:
... If I use a VARCHAR or TINYEXT field to enter these values and
I want to be able to retrieve all records where the surname field
contains the whole word Stotts, how would I enter these values:
stotts or statts or stutts
stotts,statts,stutts
other?
You need to do an IN query.
SELECT column,list,here
FROM tblBMD
WHERE Surname IN ('stotts','statts','stutts')
This will not match any of the records with multiple transcriptions.
I think you have mis-understood the question. As I understand it she
asks how to enter multiple possible transcriptions into the field so
that they can be retrieved easily.
My answer is: since commas are unlikely in names, that is as good a
separator as any. To do the query use wildcard matching with LIKE e.g.
SELECT * FROM table WHERE surname LIKE '%stotts%';
will match a field 'Stotts,Statts,Stutts' or just 'Stotts' but note it
will also match e.g. 'Stottsford,Stattsford,Stuttsford'
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB: Error: tablespace size stored in header is 17024 pages, but
Dear Heikki Many thanks for your reply. On Mon, 3 Jan 2005 20:41:22 +0200 Heikki Tuuri [EMAIL PROTECTED] wrote: are you sure that you copied the complete ibdata1 file to the new place? It is strange how 7 MB can be missing from the file end. I have just checked the tar file I used to do the transfer and sure enough, the file length is right there, but not in my database directory. Obviously I didn't notice any error when I unpacked it, but it is definitely short now. So that (partly) explains what went wrong ... however we have new data in the database now so it is too late to go back tho that copy. What does the old .err log contain? Any message about disk space running out? The error below probably has not corrupted your tablespace. Best to run CHECK TABLE on some of your tables, though. I did that on all tables and they all show they are OK. When ibdata files are created, they are initially written full of zeros. You can probably fix the error by using another mysqld installation (or any method that makes a 7 MB zero-filled file) to create a new ibdata2 file that is 7 MB in size, and add that to the tablespace. Look from http://dev.mysql.com/doc/mysql/en/Adding_and_removing.html how to edit my.cnf then. Thanks. This is what I did: First I created ibdata2 with the command dd if=/dev/zero bs=1M count=7 of=ibdata2 Then I added it to the start of the tablespace path in my.cnf by changing innodb_data_file_path = ibdata1:10M:autoextend to innodb_data_file_path = ibdata2:7M;ibdata1:10M:autoextend Then I shut down: 050105 14:25:57 [Note] /local/own-1/rt/rt-3.2.2/libexec/mysqld: Normal shutdown 050105 14:25:57 InnoDB: Starting shutdown... 050105 14:26:01 InnoDB: Shutdown completed; log sequence number 0 407778847 050105 14:26:01 [Note] /local/own-1/rt/rt-3.2.2/libexec/mysqld: Shutdown complete 050105 14:26:01 mysqld ended ... and restarted MySQL but I got an error: 050105 14:26:04 mysqld started 050105 14:26:05 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 050105 14:26:05 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 407778847. InnoDB: Doing recovery: scanned up to log sequence number 0 407778847 InnoDB: Page directory corruption: supremum not pointed to followed by a page dump and a stack trace. I removed the entry for the new file in the data file path and it has started as it did before. Have I misunderstood your instructions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB: Error: tablespace size stored in header is 17024 pages, but ...
Dear MySQL people I have a question regarding the error: InnoDB: Error: tablespace size stored in header is 17024 pages, but InnoDB: the sum of data file sizes is only 16576 pages which appeared when I upgraded from 4.0.7 to 4.1.7. Somehow I am 448 pages (7M) short of table space. Can I fix this using the procedure outlined in http://archives.neohapsis.com/archives/mysql/2004-q3/3723.html or should I do a dump/restore? Here are the gory details: I was running MySQL Ver 12.22 Distrib 4.0.17 with the following /etc/my.cnf file: [mysqld] user=www datadir=/local/own-1/rt/mysql-data innodb_data_file_path = ibdata1:10M:autoextend Then I built a new MySQL Ver 14.7 Distrib 4.1.7 and decided to do binary logging so I changed /etc/my.cnf to read [mysqld] user=rt datadir=/local/own-1/rt/mysql-data innodb_data_file_path = ibdata1:10M:autoextend # Set buffer pool size to 50-80% of memory set-variable = innodb_buffer_pool_size=512M set-variable = innodb_additional_mem_pool_size=10M # Set the log file size to about 25% of the buffer pool size set-variable = innodb_log_file_size=64M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 # Turn on binary logging and sync set-variable = log_bin=1 set-variable = sync_binlog=1 Then I shut down the old database, copied the data files and deleted the ib_logfile* files and this is what I got in the logs on starting 4.1.7. I was a bit surprised at the 'not shut down normally' message, but hey. ... 041229 14:04:51 mysqld started 041229 14:04:51 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 64 MB InnoDB: Database physically writes the file full: wait... 041229 14:04:53 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 64 MB InnoDB: Database physically writes the file full: wait... 041229 14:04:55 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Resetting space id's in the doublewrite buffer 041229 14:04:55 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 405584396. InnoDB: Doing recovery: scanned up to log sequence number 0 405584396 041229 14:04:55 InnoDB: Flushing modified pages from the buffer pool... 041229 14:04:55 InnoDB: Started; log sequence number 0 405584396 InnoDB: You are upgrading to an InnoDB version which allows multiple InnoDB: tablespaces. Wait that purge and insert buffer merge run to InnoDB: completion... InnoDB: Full purge and insert buffer merge completed. InnoDB: You have now successfully upgraded to the multiple tablespaces InnoDB: format. You should NOT DOWNGRADE to an earlier version of InnoDB: InnoDB! But if you absolutely need to downgrade, see InnoDB: http://dev.mysql.com/doc/mysql/en/Multiple_tablespaces.html InnoDB: for instructions. 041229 14:04:57 [Warning] mysql.user table is not updated to new password format ; Disabling new password usage until mysql_fix_privilege_tables is run 041229 14:04:57 [Warning] Can't open and lock time zone table: Table 'mysql.time _zone_leap_second' doesn't exist trying to live without them /local/own-1/rt/rt-3.2.2/libexec/mysqld: ready for connections. Version: '4.1.7-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution But now when I start the database I get: 041230 10:04:37 mysqld started InnoDB: Error: tablespace size stored in header is 17024 pages, but InnoDB: the sum of data file sizes is only 16576 pages 041230 10:04:39 InnoDB: Started; log sequence number 0 405783741 041230 10:04:39 [Warning] mysql.user table is not updated to new password format ; Disabling new password usage until mysql_fix_privilege_tables is run 041230 10:04:39 [Warning] Can't open and lock time zone table: Table 'mysql.time _zone_leap_second' doesn't exist trying to live without them /local/own-1/rt/rt-3.2.2/libexec/mysqld: ready for connections. Version: '4.1.7-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help figuring out indexes for faster SELECT queries
I have a new project I'm working for and I was wondering if anyone could help me optimize my selects for speed. I have a table with about 500,000 entries. The table structure I'm using is (via my PHP commands): $sql = CREATE TABLE $store_data_table ( $store_data_column[sku] VARCHAR(10) NOT NULL PRIMARY KEY, $store_data_column[prod_name] VARCHAR(127), $store_data_column[thumb_url] VARCHAR(127), $store_data_column[msrp] DECIMAL(12,2) NOT NULL, $store_data_column[price] DECIMAL(12,2) NOT NULL, $store_data_column[disc] DECIMAL(12,2) NOT NULL, $store_data_column[cat1] VARCHAR(63) NOT NULL, $store_data_column[cat2] VARCHAR(63) NOT NULL, $store_data_column[cat3] VARCHAR(63) NOT NULL, $store_data_column[cat4] VARCHAR(63) NOT NULL, INDEX (`cat1`,`cat2`,`cat3`,`cat4`)); I do lots of queries like selecting the categories like: $sql = SELECT DISTINCT $store_data_column[cat1] FROM $store_data_table WHERE $store_data_column[cat1] != 'N/A' ORDER BY $store_data_column[cat1] ASC; and $sql = SELECT DISTINCT $store_data_column[cat2] FROM $store_data_table WHERE $store_data_column[cat1] = '$url_params[cat1]' AND $store_data_column[cat2] != 'N/A' ORDER BY $store_data_column[cat2] ASC; and $sql = SELECT DISTINCT $store_data_column[cat3] FROM $store_data_table WHERE $store_data_column[cat1] = '$url_params[cat1]' AND $store_data_column[cat2] = '$url_params[cat2]' AND $store_data_column[cat3] != 'N/A' ORDER BY $store_data_column[cat3] ASC; etc... Also, I do selects like: $sql = SELECT $store_data_column[sku], $store_data_column[prod_name], $store_data_column[price], $store_data_column[cat1], $store_data_column[cat2], $store_data_column[cat3], $store_data_column[cat4] FROM $store_data_table WHERE $store_data_column[cat1] = '$url_params[cat1] ORDER BY $order_by_clause LIMIT $item_offset, $max_items; all the way to: $sql = SELECT $store_data_column[sku], $store_data_column[prod_name], $store_data_column[price], $store_data_column[cat1], $store_data_column[cat2], $store_data_column[cat3], $store_data_column[cat4] FROM $store_data_table WHERE $store_data_column[cat1] = '$url_params[cat1]' AND $store_data_column[cat2] = '$url_params[cat2]' AND $store_data_column[cat3] = '$url_params[cat3]' AND $store_data_column[cat4] = '$url_params[cat4] ORDER BY $order_by_clause LIMIT $item_offset, $max_items; Originally I didn't have the INDEX on categories. Adding the index I have now sped up the queries greatly, but I was wondering if there were any other tips so squeeze some more performance out of the selects. __ Do you Yahoo!? Meet the all-new My Yahoo! - Try it today! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help optimizing full-text searches
Hi, I currently have a table with 2 text fields, both of which are marked as fulltext. I'm using the full-text boolean search method with fairly good results. My problems however are: 1. I am on a shared host provider so I can't tweak the full-text search options such as stop words or minimum query length. 2. I would like to be able to search for words as short as 2 characters, but I'm limited by the default mysql configuration to 4 characters. 3. I'd like to get around the stopwords/stop characters. A lot of people search for 802.11g , but that search always comes up empty because I think the . character is messing up my searches. 4. Since I'm using the full-text search with a small amount of data, I would like all the results to be returned, even if they keywords are in more or less than 50% of the rows. 5. I really like the +, -, and for searching in full-text boolean mode, so I'd like to preserve that functionality. Since I'm on a shared host provider and can't really optimize the mysql configuration, is there any suggestions to help me improve my search results? Thanks, Grant __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help with mysql search engine
Hi, I currently have a table with 2 text fields, both of which are marked as fulltext. I'm using the full-text boolean search method with fairly good results. My problems however are: 1. I am on a shared host provider so I can't tweak the full-text search options such as stop words or minimum query length. 2. I would like to be able to search for words as short as 2 characters, but I'm limited by the default mysql configuration to 4 characters. 3. I'd like to get around the stopwords/stop characters. A lot of people search for 802.11g , but that search always comes up empty because I think the . character is messing up my searches. 4. Since I'm using the full-text search with a small amount of data, I would like all the results to be returned, even if they keywords are in more or less than 50% of the rows. 5. I really like the +, -, and for searching in full-text boolean mode, so I'd like to preserve that functionality. Since I'm on a shared host provider and can't really optimize the mysql configuration, is there any suggestions to help me improve my search results? Thanks, Grant __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
conflicting messages
I had mysql running great on my Gentoo server, but there was some kind of a problem with my host and my machine had to be rebooted. I restart mysql with /etc/init.d/mysql start (Gentoo syntax), but I'm having some problems: localhost / # mysqladmin test mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)' Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists! localhost / # /etc/init.d/mysql start * WARNING: mysql has already been started. There is no /var/run/mysqld/mysqld.sock file, and mysql.err doesn't have anything to say. Can anyone help? - Grant __ Do you Yahoo!? Yahoo! Mail - You care about security. So do we. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Insert data if not duplicate based on order
Hi,
I have a table that has many rows. I want to be
able to insert a new row only if has a unique field of
the last 20 rows when I sort them by date.
Currently before I insert a new row, I will select a
specific field for 20 rows and run a for loop in php
looking for a match with the data I'm getting ready to
insert. If a match occurs I will skip the insert
command.
This normally works fine, but I have seen occasions
when 2 people visit my website at the same exact time.
In this case, sometimes I might insert the data twice
because I think I'm spinning in the for loop for 2
site visitors simultaneously.
I know this is bad syntax, but this is what I imagine
a insert query to look like:
INSERT INTO $table (datax, datay, dataz)
VALUES ('x', 'y', 'z') IF 'x' unique WHEN
ordering by 'z' DESC LIMIT 20
In this example, 'z' is a datetime field and 'x' is a
varchar. I don't mind duplicate entries in my table,
but I don't want duplicates entries to occur within 20
rows when sorting by date.
Is this possible to do with one INSERT command? I saw
the IGNORE statment in the mysql manual, but I don't
think it really aplies to what I'm trying to do here.
Any suggestions would be appreciated,
Thanks,
Grant
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECTing to Substitute Text for Refc Codes
For posterity (ie the archives) there's a comma missing in the FROM line: FROM contact AS cn, codes AS cd1, codes AS cd2 Roddie Grant on 25/5/04 8:25 am, Greg Willits at [EMAIL PROTECTED] wrote: Got an offline answer, so I'm all set. Gotta love SQL. for posterity... Try: SELECT cn.name, cd1.desc ,cd2.desc FROM contact AS cn, codes AS cd1 codes AS cd2 WHERE cn.city = cd1.code AND cn.state = cd2.code given: CREATE TABLE `codes` ( `id` int(11) NOT NULL auto_increment, `code` varchar(16) default NULL, `desc` varchar(64) default NULL, PRIMARY KEY (`id`) ) TYPE=MyISAM; CREATE TABLE `contact` ( `id` int(11) NOT NULL auto_increment, `name` varchar(32) default NULL, `city` varchar(16) default NULL, `state` varchar(16) default NULL, PRIMARY KEY (`id`) ) TYPE=MyISAM; -- gw On May 24, 2004, at 5:12 PM, Greg Willits wrote: Struggling with a query structure new to me... Most of my apps are not very complex at all (simple joins, few functions), but I've worked through a few tricky queries. However, this one is stumping me on how to even begin. I can't even decide what kind of query it is. As you can I wasn't even sure what to call it :-( I'll distill it to a structural example. Two tables: first table (contacts) has basic contact stuff (Name, city, State), second table (syscodes) is a arbitrary collection of codes and descriptions (code, desc). The contacts table uses codes from the sysycodes table instead of actual text for stuff like city and state. So, the contacts table might look like: Will Doolittle, 1543, 425 Robin Banks, 1800, 12897 And, the syscodes table is 425, California 1543, Anaheim 1800, Topeka 12897, Kansas In my realm this is a sadistic level of normalization, but I suppose it is pretty common and must be useful at some scale (I'm getting data from a large U.S. county). Either I'm thinking too hard, or really don't get it, but I don't know how to pull those together to get: Will Doolittle, Anaheim, California Robin Banks, Topeka, Kansas So starting with the basics: SELECT cntcName, cntcCity, cntcState FROM contacts, syscodes WHERE cntcID=x AND cntcCity=code AND cntcState=code (which I know is wrong) But how to connect which row from syscodes goes to which cntc field? The only way I can seem to visualize this is as a bunch of substitutions to alias with AS in the select phrase, but I don't see how. Unfortunately, this has to be compatible with 3.23.54. (when 4.1 hits beta then I will likely switch to it for this particular app as I need sub-selects for another task). All clues welcomed. -- greg willits -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
vb.net and mysql training
I am looking for a course in North America that provides mysql and vb.net or c# training or a good text book or dvds. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Finding Empty Records
I am trying to find all my empty records. I am looking for NULLs and Empty strings but is there a simple way to just as this? I thought MySql may have something built in. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQLDriverCS-n-EasyQueryTools-3.0.11
I am desperately looking for some programming examples for building custom gui's. And can't find any online using MySQLDriverCS. I have one built but I need to find better examples. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update is deleting data
This is on my development machine using Lasso 6.0.4, MacOS 10.2, MySQL 4.0.12-standard. Using Lasso: On one database I can add and update records. On another I can add, but updating sets all the specified fields to NULL in the target record (the key and non-referenced fields stay OK), even though they are set to NOT NULL. Updating works fine using Terminal, CocoaMySQL, and PHP. So, firstly, does anyone know what might be going on? I've checked every setting I can find in Lasso and MySQL without finding any differences between the two databases, but should I be looking for something in particular? Secondly, in order to find out what MySQL is receiving from Lasso someone (on the LassoTalk list) suggested using an update log. Using Terminal, I stopped MySQL with mysqladmin shutdown, and then restarted it with ./bin/mysqld_safe --log-update=roddie I get a message about a daemon being started, but then Terminal hangs (10 mins) and if I use Ctrl Z is says Suspended. Eventually I can quit Terminal and MySQL seems to be running unaffected. I can open the roddie.001 log file in Pico but it is empty. What should I be doing to get the update log working? Any advice will be much appreciated. Roddie Grant [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE..
on 15/12/03 12:13 pm, Graham Little at [EMAIL PROTECTED] wrote: +--+-+--+-+-+---+ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-+---+ | cou_id | int(11) | | PRI | 0 | | | cou_name | text| | | | | +--+-+--+-+-+---+ I am getting the following error message and i am not sure why? Error 1054: Unknown column 'id' in 'field list' Your field is called 'cou_id' not 'id' (and 'cou_name' not 'country'). Roddie Grant [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Connection Refused by Linux Server
I have RH9 running on a p4 machine. I have mysql 3.23.58 running on this linux machine. Through Websphere studio application developer on windows xp machine I am trying to connect to my MySql database on the Linux machine. The error I receive is: connection refused I then tried to telnet to the Linux machine, this also error'd with: Connection Refused On the Linux machine I can see and access the other computers on my network, however none of my Windows machines see the Linux Server through Network Places. I can however successfully ping the Linux server. Addtional info: Linux security is set to none. Logging into Linux server MySql DB with root/password. Do I need to activate or configure something on the Linux server in order to allow connections? Thank you for your assistance -EKG
Re: Mysql daemon dies on startup
OK thx to MATHAN's previous post. Solution: Executed the following commands.. # chown -R mysql /var/lib/mysql # chmod 700 /var/lib/mysql Then started the daemon. Thx -EKG From: ekg1 To: [EMAIL PROTECTED] Sent: Saturday, November 08, 2003 1:39 AM Subject: Mysql daemon dies on startup I have installed Redhat 9.0 and upgraded mysql thru RHN to 3.23.58-1.9 I executed the following commands: [EMAIL PROTECTED] linux]# mysql_install_db then [EMAIL PROTECTED] linux]# /usr/bin/safe_mysqld [1] 12018 [EMAIL PROTECTED] linux]# Starting mysqld daemon with databases from /var/lib/mysql 031108 01:30:55 mysqld ended No host*.err files are created. Thank you for your help. -EKG
Trouble installing MySql 4.0 or 4.1 on RH 9.0 Linux
I am having trouble installing the RPM files for MYSql 4.1 on RH 9.0. 1. I believe I have the MYSQLD daemon running, however have no idea how to talk to the server. 2. When I try to install the Client RPM for 4.1, the terminal window hangs and nothing is printed to the window. 3. When I tried to install 4.0 again the client failed by thowing a Segmentation Fault My goal is to get a version of MYSQL installed, and be able to use the GUI tools to administer and use the database. Thank you for you help. -EKG
SEC_TO_TIME not working?
SELECT DailyLog.TimeOut, DailyLog.TimeIn, SEC_TO_TIME(UNIX_TIMESTAMP(DailyLog.TimeOut) - UNIX_TIMESTAMP(DailyLog.TimeIn)) as test FROM DailyLog keeps on giving me a result as a date. Not time in years, days, hours, min. For example 9/9/2007 12:51:26 PM - 9/9/2007 12:51:26 PM gives me 12:00 PM. I want 0. The simple example select SEC_TO_TIME(88) gives me 12:01:28 AM. Is there a setting in mysql that I'm missing? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do you ORDER BY two columns as one?
on 13/9/03 9:57 am, Peter Nguyen at [EMAIL PROTECTED] wrote: Cause ORDER BY tbl_parent.parent_datetime AND tbl_child.child_datetime doesn't seem to work. Any help would be appreciated, thanks. The AND should be a , thus: ORDER BY tbl_parent.parent_datetime, tbl_child.child_datetime Roddie Grant [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
listing all people who have the same firstname and lastname
I'm trying to get a query to work by listing all the people in a row with the same last name and first name. key, fname, lname 1 ,John, Smith 4, John, Smith 5, Cody,Edwards 2, Cody, Edwards Don't list anyone that has a unique first name last name. Is this possible? Trying to use the Count command. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
array vs. more table cells
I'm building a program calendar describing each Year's courses. I have a field for a semester and it's current courses in one cell separated by a ,. When I go to print this out I create an array out of the cell than do it's sorting. Or Should I just take the time and create a new table and separate each course matching it with its course? This would make the database more complicated but less likely that dumb person will screw things up by misplacing a , or adding to many spaces. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is query possible? (Newbie)
I have 2 tables used for an online calendar... first table fields: primary_key , start_date, event_name, event_description second table fields: primary_key, end_date Tables fields are shortened and can't be changed. My second table only contains events that have a end date. I want to create a query that will take all the fields in. If no end_date exists then set to NULL. Been playing with it all day. Hoping some advance function exists. I thought of using a temp table but there must be a better way. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: unique identifier using mysql_connect
I am opening 4 different databases and was hoping when I do a mysql_query I could just grab the connection id rather than reconnecting everytime I want to query a new database. -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 30, 2003 9:08 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: unique identifier using mysql_connect At 12:43 -0700 7/30/03, Grant Cooper wrote: Can I get a unique identifier using the mysql_connect command by connecting to the same host, same user but different database during the life time of one script? I currently do a $conn_id = mysql_connect ($host_name, $user_name, $password); Then call a, mysql_select_db ($db_name,$conn_id) But I always get the same identifier. Why is this a problem? This is fairly typical behavior for PHP (which is what it looks like you're using). -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
unique identifier using mysql_connect
Can I get a unique identifier using the mysql_connect command by connecting to the same host, same user but different database during the life time of one script? I currently do a $conn_id = mysql_connect ($host_name, $user_name, $password); Then call a, mysql_select_db ($db_name,$conn_id) But I always get the same identifier. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Configuring Database Server
I was hoping someone could tell me a way to increase the productivity of a database server. It has nothing else installed other than the newest production version of mysql and the default packages from FreeBSD. Is there a toaster than can bring me up to speed. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: creating table type innodb
I am a newbie myself, so please someone correct me if I am wrong, but here is how I understand it: A MySQL installation can support several databases. The system tables are always in a system database that uses tables of type ISAM. You create another database of type InnoDB, in which you keep your InnoDB tables. With the ISAM database every table is stored in a separate file. With InnoDB all tables in the database are stored in one or more database files, and data from single table may be stored within several of these database files. To use InnoDB tables and other InnoDB features you must first create an InnoDB database, then copy your tables into new tables in the InnoDB database. Grant Q -Original Message- From: azamka [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 08, 2003 10:59 AM To: [EMAIL PROTECTED] Subject: creating table type innodb Hi everyone, I am trying to create tables with type innodb. I created a database and all the tables of type ISAM. Now I am at the point of making relations and establish refrential integrity. For that ofcourse I have to change the tables in the Innodb type. I did that with the commad alter table tablename type = innodb. Command runs fine but the table type doesnt change. I tried to create a new table of type innodb but its not working either. I dont know what the problem is. Do we have any command to enable the innodb property or what?? I am stuck badly. Please help. Thank you in advance Kamran -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RAID hardware suggestions/experience
RE: SCSI needed for best performance - While this is true in some cases, if you are using striping or any RAID level (RAID 5 for example) that splits reads and writes across drives, then there will be several IDE channels feeding data to the RAID card at a time. Two ATA100 IDE channels will accept and provide data faster than the PCI bus that the card is plugged into can. The result is that you can use cheap IDE drives and get the same performance as the very fastest SCSI drives. Of course if you are running on some of the high-end server platforms from IBM, SUN, or HP then there will not be a PCI bus in the loop, and if you can afford such a server then the cost of getting the fastest SCSI drives and custom RAID hardware will not be an issue. If you are looking at this type of high-end storage and want to save some money you should check out the fiber-channel RAID solutions from Adjile Systems http://www.adjile.com as part of you comparisons. For the best price/performance/reliability for my i686 Linux system (on a HP NetServer E60 Dual Pentium II box) I went with High Point Technologies Rocket RAID 404 card and Western Digital 180 GB drives. The documentation for setting up the card for Windows and Linux Lilo booting was complete, but seriously lacking for Linux GRUB boots. I worked out the GRUB issues with help from the suse-linux-e group at http://lists.suse.com last January and February. The setup was easy once I learned what to do. Best of Luck, Grant Q -Original Message- From: Gabriel Guzman [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 17, 2003 10:06 AM To: Bernd Jagla Cc: [EMAIL PROTECTED]; mysql Subject: Re: RAID hardware suggestions/experience Bernd, here is a good resource on the different types of RAIDs (0, 1, 10, 0+1 etc) http://www.acnc.com/04_01_00.html For high I/O, get a hardware RAID controller, several SCSI disks with 15,000 RPM and as much CACHE as you can afford and do RAID1. Better make sure you have a good backup plan though cause if one disk fails, you loose everything. RAID 10 or 0+1 might be a good compromise between data integrity and I/O performance. But for sheer speed, you will definitely want to go SCSI if you can afford it. RAID 5 will take a performance hit, especially on writing, I wouldn't reccomend it for what you are doing, definitely not if you will be using IDE drives... SLOW. Another idea would be to go with a disk array from a 3rd party vendor that you could attach to you DB box. Might be worth looking into at least. I've setup and maintained up to 1.5TB disk arrays in RAID 5 implementations (IDE and SCSI) and ide is definitely a slow solution for RAID5... good for backups, but not for I/O intensive applications. gabe. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: checking NULL or ''
I'm still a bit of newbie, but surely these are not the same query. The empty string is not the same thing as NULL. Roddie Grant [EMAIL PROTECTED] I tested these 2 queries: select * from mytable where myfield is null; select * from mytable where myfield = ''; The second one run faster. Is that true? Is checking NULL slower than checking a string without any character? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: money field
I would use Decimal (12,2). A Float may be smaller, but only because it rounds off the last several digits of large values. A single-precision float uses 23 bits for storing digits, meaning that for values over 8,388,607 you are loosing pennies. When storing a value around 100 Million you would loose about 12 cents. Not big losses, but when dealing with financial accounting it is generally an unacceptable inaccuracy. Grant Q -Original Message- From: Mojtaba Faridzad [mailto:[EMAIL PROTECTED] Sent: Monday, June 09, 2003 11:56 AM To: [EMAIL PROTECTED] Subject: money field Hi, Which type do you usually use to keep money values? Decimal (12,2) or Float? to keep the first one, system takes 12 bytes but for the second one, 8 bytes. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
updating database query
I'm trying to update an email field. Our company has changed from a .com to a .ca. I wrote a php script to make the changes but I was wondering if I could do this from the command line. Its a text field and I would like to replace. [EMAIL PROTECTED] to [EMAIL PROTECTED] Can someone help me out please. Thank You -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update works in Netscape, doesn't in Explorer
Working in Lasso Profession 6, OS X and My SQL 4.? I'm at the testing stage of a small survey website (http://www.myword.co.uk/iied/iied_introVw.lasso). When a visitor first comes to the site, a record is created and identified by a sessionID. The visitor can move from page to page, and the answers they give are recorded in the database, using the sessionID to identify the record. This all works fine. Visitors have the option of Pausing, then Resuming on another occasion. When they opt for Pause, the system gives them a reference number (not the sessionID - too long!) and they enter a password. They click Pause. The reference and password should now be written to the visitor's record (identified by the same sessionID). This works in Netscape (7.01), but not in Explorer (5.2.2). OSX 10.1.3. Explorer removes the NULLs from the fields, but does not write the new data. I have confirmed that the SQL is identical for both, and I am now baffled. I don't even know what to look for. This is mission-critical, so I'm open to any suggestions. Thanks Roddie Grant [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: Update works in Netscape, doesn't in Explorer
I'm using link based sessions, but I don't think that can be the culprit. The SQL statement includes WHERE sessID=whateveritis. Explorer appears to identify the correct record because it clears the NULLs; it just doesn't write the new data. Roddie on 28/2/03 9:45 am, Scott Haneda at [EMAIL PROTECTED] wrote: on 02/28/2003 1:37 AM, Roddie Grant at [EMAIL PROTECTED] wrote: Working in Lasso Profession 6, OS X and My SQL 4.? I'm at the testing stage of a small survey website (http://www.myword.co.uk/iied/iied_introVw.lasso). When a visitor first comes to the site, a record is created and identified by a sessionID. The visitor can move from page to page, and the answers they give are recorded in the database, using the sessionID to identify the record. This all works fine. Visitors have the option of Pausing, then Resuming on another occasion. When they opt for Pause, the system gives them a reference number (not the sessionID - too long!) and they enter a password. They click Pause. The reference and password should now be written to the visitor's record (identified by the same sessionID). This works in Netscape (7.01), but not in Explorer (5.2.2). OSX 10.1.3. Explorer removes the NULLs from the fields, but does not write the new data. I have confirmed that the SQL is identical for both, and I am now baffled. I don't even know what to look for. This is mission-critical, so I'm open to any suggestions. How do lasso sessions work, are they cookie based, or url appended? If they are cookie based, you probably are losing the cookie value. - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
storing a picture in a file or database (newbie)
Is it better to store a picture in mysql field or a link to the file on my server? I want to allow people to use a php script to upload a gif or a pic. Any pro's and con's would be 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: Access to MySQL
There are many tools. Use a gui. mysql front (free), premium soft (my pick but has a 29 day trial). There are also many small window applications to do this. Do a search at one of the famous downloads. +client +mysql +gui -Original Message- From: Charles Mabbott [mailto:[EMAIL PROTECTED]] Sent: Monday, December 16, 2002 1:45 PM To: [EMAIL PROTECTED]; '1Mysql' Subject: RE: Access to MySQL Yves, I am always open for cleaner method, but what I did was in MySQL created a table 'member' for data. In Access exported as .txt make sure tab sequential. Back in MySql I made database active that had 'member' And did a: mysql load data infile c:\\mysql\\tmp.txt into member; it actually went in quite cleanly. I am sure there are probably better ways, I am always willing to learn. But this is what worked for me today. Regards, Chuck -Original Message- From: Yves Arsenault [mailto:[EMAIL PROTECTED]] Sent: Monday, December 16, 2002 12:21 PM To: 1Mysql Subject: Access to MySQL Hello, Is there a small program that would transfer the contents of an Access database to MySQL? TIA, Yves Arsenault Carrefour Infotech 5,promenade Acadian Charlottetown, IPE C1C 1M2 [EMAIL PROTECTED] (902)368-1895 ext.242 ICQ #117650823 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: access-mysql
MySQL-Front was super and has a built in utility. Can do it remotely or locally. To bad they stopped supporting it. - Original Message - From: Sandeep Murphy [EMAIL PROTECTED] To: Crercio O. Silva [EMAIL PROTECTED]; MySQL List [EMAIL PROTECTED] Sent: Thursday, December 05, 2002 5:06 AM Subject: RE: access-mysql thnx to all! am checking this links out.. looks great! :) -Original Message- From: Crercio O. Silva [mailto:[EMAIL PROTECTED]] Sent: quinta-feira, 5 de Dezembro de 2002 12:30 To: MySQL List Subject: Re: access-mysql DBTools works great on this matter. You find it at http://www.dbtools.com.br/EN and is totally free. []s Crercio - Original Message - From: Sandeep Murphy [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Wednesday, December 04, 2002 12:39 PM Subject: access-mysql Hi, Can anyone please suggest a simple and effective method for converting a huge database i hv in Access xp to MySQL ?? hv tried various methods, including using dsns , a small script available at http://www.cynergi.net/exportsql/ etc.. they all work but not deliver a clean result... any tried and trusted method?? thnx in adv, sands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Connecting M. Access Forms to insert data into mysql
I am trying to find a package to insert data. And was wondering if I can use odbc drivers to connect Access to mysql in real time? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
