Re: mysql.h errors
Steve, Steve Davies sagte: Sinisa Milivojevic wrote: On Mon, 08 Sep 2003 19:57:25 +0100 Steve Davies [EMAIL PROTECTED] wrote: Hi All I'm currently trying to learn C so that I can recode some php/mysql apps I've got but I've run into probs right at the start. I'm trying to compile the examples PDuBois MySQL (myclient.c the really easy one ;-( ) and get the errors below. Yeah tried that - same error with includes from the tarball. Maybe I'm missing something when I call it - I'm using gcc -c -I/usr/include/mysql myclient.c I haven't followed this thread at all, so I don't have a clue what exactly the problem is. But I thought I should just post my gcc-command that I had used long time ago. I remember I had a lot of trouble compiling my first C-API. Here we go: gcc -L/usr/lib/ -I/usr/include -o prg_name src_name.c -lmysqlclient It might work for you, it might not. Check the paths first though. Cheers, Sebastian -- Once a problem is defined - it is half way solved. (Henry Ford) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mySQL on a PDA
Hi; Can anyone tell me whether there is a version of mysql that will run on a handheld PDA, and if so what are the hardware/software specifications? Fraser -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SUM and GROUP BY with UNION
I have this query which looks something like, ( SELECT count(*) AS tCount, amount, block FROM table_a GROUP BY amount, block ) UNION ( SELECT count(*) AS tCount, amount, block FROM table_b GROUP BY amount, block ) ORDER BY amount; tCountamountblock 3400A-- from table_a 1400A-- from table_b 2400B-- from table_a 5600A-- from table_a 5600A-- from table_b 5600B-- from table_a 5600B-- from table_b how can i do a SUM on tCount and GROUP BY amount and block with this two set of record? result should be like this tCountamountblock 4 400A 2400B 10 600A 10 600B Thanx Jimmy
Re: How to get previous and next result
Maria, Maria Garcia Suarez sagte: Hi there! I'm currently developing a web where we let users create their own forums. All the messages (wherever they are posted) are stored in the same mysql table. When users read a certain message I would like to display the previous and next message in that forum. Since all the messages (of the different forums) are stored in the very same table I don't find how to guess what's the ID of the previous and next message. Is there any way to find those IDs? Thanks. Kisses, Maria What I do is that I assign each message its ID (of course) plus its parent_ID in case of a reply (zero in case of thread-starting message) plus a thread_ID which is equal to the ID of the message which started the thread. Then I select all messages with the same thread_ID. There I have the messages that belong to one thread. I store the unique ID's in an array which I use to link to the next / previous message. You might want to assign each message a forum_ID, too. Did I get the idea across? If you are interested I can look for some code later. (Don't have any here at work) Let me know. Cheers, Sebastian -- Once a problem is defined - it is half way solved. (Henry Ford) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign key update?
Daevid Vincent [EMAIL PROTECTED] wrote: Thanks Victoria for the pointer. I should have looked there first. Duh! Now for the help... I tried: ALTER TABLE rep_table ADD FOREIGN KEY (`rep_company_code`) REFERENCES `company_table` (`company_code`) ON UPDATE CASCADE; But get ERROR 1216: Cannot add a child row: a foreign key constraint fails It means that you have row(s) in the child table that doesn't have parent row. I'm running version: 3.23.56-Max on RedHat 8 via RPM. I have data in both these tables, so dropping the tables and redoing schema is really not a graceful option if I can help it. Here are the two tables in their entirety. What have I done wrong? And just to clarify, I want this to work such that a change of the company_table.company_code will trigger the same change to the rep_table.rep_company_code. Ideally I'd also like it to be that if I change the company_table.company_code, then any other company that has a company_referal_code will change to the new company_code too... Does that make sense? Is that possible to have a self referencing foreign key like that? I thought I read that it isn't possible, but thought I'd ask. You should fix your data first: find rows in the rep_table that don't have parent row in the the company_table, then add parent row(s) to the company_table for those rows or delete child row(s) from rep_table. After that you can create FOREIGN KEY with ON UPDATE CASCADE. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
OS X Installation and Setup
I'm rather new to OS X after not using a Mac since grade school. I have a bit of UNIX experience so its not too complicated but having never administered to either I've been having some difficulty setting mySQL up after the auto-install. I'm interning for a company and the computer I'm using is a consultants' computer where I'm just one of many users but only I need to be able to play with mySQL and my boss is the administrator. I can log in as 'root' if need be. After the install I get errors like: [Sipa-Consultantss-Computer:~] andrewcallan% /usr/local/mysql/bin/mysqladmin -u root password [password] /usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failed error: 'Can't c onnect to local MySQL server through socket '/tmp/mysql.sock'(2)' Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists! [Sipa-Consultantss-Computer:~] andrewcallan% /usr/local/mysql/bin/mysql ERROR 2002: Can't connect to local MySQL server through socket '/tmp/ mysql.sock' (2) I've tried installing, deleting, and reinstalling several times to try setting it up in different environments (e.g. as myself, logged in as root, autheticating as root when i'm logged in as myself) without success. I was almost there one time I think but could not figure out what the mysqladmin -u root -h 'hostname' password password wanted for hostname, now I presume I should type exactly 'localhost' --is that correct? Any assistance as to what user I should be logged in as and what the exact sequence of commands i should run to set up the package (including deleting previous install) would be great, many thanks in advance. Andy Callan -- Andy Callan St. Thomas More College Rm M112 Mounts Bay Rd. Crawley, WA 6009 IM: CALid05 www.nd.edu/~acallan1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldev user ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Wed, 10 Sep 2003, Jason Frisvold wrote: Aha... I think I figured it out... I have the MySQL-4.0.15-0.src.rpm in the same directory... I'll bet that an rpm -Fvh automatically installs the source.. The source is owned by mysqldev, isn't it... :) Yes, that might be the cause. I can't really find a reason for this without having more details. But in any case, it's probably harmless. Yup, appears to be... Thanks for the info though! My pleasure. I'm glad it's nothing serious :) Bye, LenZ - -- Lenz Grimmer [EMAIL PROTECTED] Senior Production Engineer MySQL GmbH, http://www.mysql.de/ Hamburg, Germany For technical support contracts, visit https://order.mysql.com/?ref=mlgr -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE/YDNGSVDhKrJykfIRAqqvAJ400GjUi30dmrAoQrjSLHd1C5gljwCfXaYC 7P4cPGWYR5DG6rSa2m5cPcg= =HPor -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Solved (ish)Re: mysql.h errors
Sebastian Haag wrote: Steve, Steve Davies sagte: Sinisa Milivojevic wrote: On Mon, 08 Sep 2003 19:57:25 +0100 Steve Davies [EMAIL PROTECTED] wrote: Hi All I'm currently trying to learn C so that I can recode some php/mysql apps I've got but I've run into probs right at the start. I'm trying to compile the examples PDuBois MySQL (myclient.c the really easy one ;-( ) and get the errors below. Yeah tried that - same error with includes from the tarball. Maybe I'm missing something when I call it - I'm using gcc -c -I/usr/include/mysql myclient.c I haven't followed this thread at all, so I don't have a clue what exactly the problem is. But I thought I should just post my gcc-command that I had used long time ago. I remember I had a lot of trouble compiling my first C-API. Here we go: gcc -L/usr/lib/ -I/usr/include -o prg_name src_name.c -lmysqlclient It might work for you, it might not. Check the paths first though. Cheers, Sebastian I've been meaning to upgrade to 4 for a while so I installed it on a spare box and everything seems to work now!! still haven't got anywhere with 3 though ;-( steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
OS X Installation and Setup
I'm rather new to OS X after not using a Mac since grade school. I have a bit of UNIX experience so its not too complicated but having never administered to either I've been having some difficulty setting mySQL up after the auto-install. I'm interning for a company and the computer I'm using is a consultants' computer where I'm just one of many users but only I need to be able to play with mySQL and my boss is the administrator. I can log in as 'root' if need be. After the install I get errors like: [Sipa-Consultantss-Computer:~] andrewcallan% /usr/local/mysql/bin/mysqladmin -u root password [password] /usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failed error: 'Can't c onnect to local MySQL server through socket '/tmp/mysql.sock'(2)' Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists! [Sipa-Consultantss-Computer:~] andrewcallan% /usr/local/mysql/bin/mysql ERROR 2002: Can't connect to local MySQL server through socket '/tmp/ mysql.sock' (2) I've tried installing, deleting, and reinstalling several times to try setting it up in different environments (e.g. as myself, logged in as root, autheticating as root when i'm logged in as myself) without success. I was almost there one time I think but could not figure out what the mysqladmin -u root -h 'hostname' password password wanted for hostname, now I presume I should type exactly 'localhost' --is that correct? Any assistance as to what user I should be logged in as and what the exact sequence of commands i should run to set up the package (including deleting previous install) would be great, many thanks in advance. Andy Callan -- Andy Callan St. Thomas More College Rm M112 Mounts Bay Rd. Crawley, WA 6009 IM: CALid05 www.nd.edu/~acallan1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql 3.23 and use index
Hello! My Mysql version is 3.23.56. I want to use specific index, but when I use use index EXPLAIN gives me that key not used. And when I'don use use index mysql uses other index. Where I test this in 4.0.14 with force index it works and the query time is 4x faster with index I choose, then the index mysql use. Is anybody can tell me how to fix this problem. May be the way(sequence) of creating indexes has matter? Thank you! Kosyo __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql 3.23 and use index
Hello! My Mysql version is 3.23.56. I want to use specific index, but when I use use index EXPLAIN gives me that key not used. And when I'don use use index mysql uses other index. Where I test this in 4.0.14 with force index it works and the query time is 4x faster with index I choose, then the index mysql use. Is anybody can tell me how to fix this problem. May be the way(sequence) of creating indexes has matter? Thank you! Kosyo __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql 3.23 and use index
Hello! My Mysql version is 3.23.56. I want to use specific index, but when I use use index EXPLAIN gives me that key not used. And when I'don use use index mysql uses other index. Where I test this in 4.0.14 with force index it works and the query time is 4x faster with index I choose, then the index mysql use. Is anybody can tell me how to fix this problem. May be the way(sequence) of creating indexes has matter? Thank you! Kosyo __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems with (FTS)
I need help understanding how Mysql processes the simbol (ampersand) in full text search; I've a text field in a table with this data: kw kwz kwzw att Of course I've instructed mysql to index words of 2 chars or greater; here's my findings: - MATCH (mycolumn) AGAINST ( 'kw' IN BOOLEAN MODE ) returns kw, kwz, kwzw - MATCH (mycolumn) AGAINST ( 'kw' IN BOOLEAN MODE ) (note the double quotes) returns kw, kwz, kwzw... seems somewhat strange, since I asked for an exact phrase match; - MATCH (mycolumn) AGAINST ( 'att' IN BOOLEAN MODE ) returns nothing. I think that the words with the symbol are splitted while indexing; that explains why ATT is never found (AT is a stopword and T is less than 2 character). Is it possible to instruct mysql to treat words with like they were only one token? -- Andrea Gangini [EMAIL PROTECTED] Mimesi Srl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't open...
Dear all, what is the meaning of Can't open file: 'products_description.MYI'. (errno: 145) in mysql? Regards, _DT -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with (FTS)
Hi! On Sep 11, Andrea Gangini wrote: I need help understanding how Mysql processes the simbol (ampersand) in full text search; I've a text field in a table with this data: kw kwz kwzw att Of course I've instructed mysql to index words of 2 chars or greater; here's my findings: - MATCH (mycolumn) AGAINST ( 'kw' IN BOOLEAN MODE ) returns kw, kwz, kwzw - MATCH (mycolumn) AGAINST ( 'kw' IN BOOLEAN MODE ) (note the double quotes) returns kw, kwz, kwzw... seems somewhat strange, since I asked for an exact phrase match; - MATCH (mycolumn) AGAINST ( 'att' IN BOOLEAN MODE ) returns nothing. I think that the words with the symbol are splitted while indexing; that explains why ATT is never found (AT is a stopword and T is less than 2 character). That's correct. Is it possible to instruct mysql to treat words with like they were only one token? Unfortunately for now - only by recompiling :( See ft_parser.c file, misc_word_char macro. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OS X Installation and Setup
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Thu, 11 Sep 2003, Andy Callan wrote: I can log in as 'root' if need be. After the install I get errors like: /usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock'(2)' Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists! MySQL seems to be installed (as you can call mysqladmin, but the MySQL server itself does not seem to be running. Did you follow the instructions in the README? The MySQL server needs to be started first. I've tried installing, deleting, and reinstalling several times to try setting it up in different environments (e.g. as myself, logged in as root, autheticating as root when i'm logged in as myself) without success. Does the installation of the PKG itself finish successful? I was almost there one time I think but could not figure out what the mysqladmin -u root -h 'hostname' password password wanted for hostname, now I presume I should type exactly 'localhost' --is that correct? If you enter the command as directed in the manual, you will notice that the hostname command is enclosed in backticks (`). This means that it will automatically take the output of the hostname command as the value for the hostname field. However, MySQL needs to be started before you can work with mysqladmin! Any assistance as to what user I should be logged in as and what the exact sequence of commands i should run to set up the package (including deleting previous install) would be great, many thanks in advance. I assume you installed the official MySQL PKG packages from mysql.com. Then the installation should have put the files in /usr/local/mysql. Try to follow the instructions given in the README or at http://www.mysql.com/doc/en/Mac_OS_X_installation.html It's essential to start up mysqld before you continue with using mysqladmin. Bye, LenZ - -- Lenz Grimmer [EMAIL PROTECTED] Senior Production Engineer MySQL GmbH, http://www.mysql.de/ Hamburg, Germany For technical support contracts, visit https://order.mysql.com/?ref=mlgr -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE/YFNCSVDhKrJykfIRAr39AJ9gvkpAuDrV8haifjYk+UwYplD8ggCeL21g Ufu8sperzi2TNVYAdEXld4A= =v88B -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't open...
Deependra b. Tandukar [EMAIL PROTECTED] wrote: what is the meaning of Can't open file: 'products_description.MYI'. (errno: 145) in mysql? $ perror 145 Error code 145: Unknown error 145 145 = Table was marked as crashed and should be repaired You should repair table with REPAIR TABLE statement or with myisamchk: http://www.mysql.com/doc/en/Repair.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't open...
Deependra b. Tandukar wrote: Dear all, what is the meaning of Can't open file: 'products_description.MYI'. (errno: 145) in mysql? Regards, _DT $ perror 145 145 = Table was marked as crashed and should be repaired Hope this helps -- Joseph Bueno -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: Can't open...
Hi There, errno 145 means: 'Table was marked as crashed and should be repaired' You can Type on the commandline (dosbox, bash, etc-): perror ErrorNumber to get information about errors. prosit Klaus P.S.:REPAIR TABLE (manual chapter 4.4.5) might help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Temporary tables
Hi, I've found the phrase in MySQL documentation http://www.mysql.com/doc/en/Temporary_table_problems.html You can't use temporary tables more than once in the same query. For example, the following doesn't work. mysql SELECT * FROM temporary_table, temporary_table AS t2; Does it mean that I can't use THE SAME temporary table twice in THE SAME query? Or does it mean that I can't use more than 1 temporary table in the query at all? Thanks in advance, Mikhail. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL standalone and Java
Are there packages for java to include a standalone mysql database? Or are there plans for such? Luc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Compling on RedHat 9
I couldn't find an appropriate mailing list for compiling MySQL, so I thought I'd try here. anyone successfully compile MySQL 4 on RedHat 9? I keep getting errors on libmysql.c during the make step. Google doesn't reveal a whole lot about compiling MySQL on RedHat which leaves me to believe most people use RPMs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Writing a NULL in a CHAR field
Are BLOB column types the only columns that will let you write a NULL (Hexidecimal 0) and still write beyond that? CHAR, CHAR BINARY, VARCHAR, and VARCHAR BINARY all seem to truncate the string and stop at the first null even if I escape the string being updated. I don't want to use a BLOB column because I'm only writing 8 characters. extreme waste of space. Thanks for any help. Best Regards, Andrew Sql, query
AW: How To Create Users In MySQL?
http://www.mysql.com/doc/en/Adding_users.html First, use the mysql program to connect to the server as the MySQL root user: shell mysql --user=root mysql Then you can add new users by issuing GRANT statements: mysql GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED] - IDENTIFIED BY 'some_pass' WITH GRANT OPTION; mysql GRANT ALL PRIVILEGES ON *.* TO monty@'%' - IDENTIFIED BY 'some_pass' WITH GRANT OPTION; mysql GRANT RELOAD,PROCESS ON *.* TO [EMAIL PROTECTED]; mysql GRANT USAGE ON *.* TO [EMAIL PROTECTED] Dear Caroline Jen, Sometimes this is not quite clear explained in All text books, But the above snip does the trick, Under windows XP. If security is a very important topic for you, A different Operating system could offer you more safety. But please take into consideration that mysql behaves different under Linux and windows. The commands are not exactly the same. Even if it is the same MySQL version. Yours sincerely Morten Gulbrandsen -Ursprüngliche Nachricht- Von: Caroline Jen [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 10. September 2003 22:33 An: [EMAIL PROTECTED] Betreff: How To Create Users In MySQL? I have the MySQL-3.23.55 installed in my PC. Therefore, I am the DBA without the required DBA knowledge. First, how do I create users in the MySQL database? Second, how do I grant table creation privilege to users? Is GRANT ALL PRIVILEGES ON databasename TO someuser IDENTIFIED BY 'somepassword'; the correct command? Thanks for your guidance. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Writing a NULL in a CHAR field
On 11 Sep 2003 at 11:04, Andrew Kuebler wrote: Are BLOB column types the only columns that will let you write a NULL (Hexidecimal 0) and still write beyond that? CHAR, CHAR BINARY, VARCHAR, and VARCHAR BINARY all seem to truncate the string and stop at the first null even if I escape the string being updated. Are you sure it's not your application, rather than MySQL, that's truncating the string. If you select LENGTH(column_name), do you get the full length of the untruncated string? I've just tried it with a VARCHAR and had no problems. If I insert 'abc\0def', the length is 7, and RIGHT(column_name, 3) is 'def'. It's true that the characters after the 0 byte don't show up in the mysql client, but that's not the way I'd actually be using them anyway. What are you using? C? Also, how are you inserting the strings? Are you writing the binary 0 as '\0'? (It's confusing to call the character 'NULL' on a list about databases, since here people expect NULL to mean something completely different.) I don't want to use a BLOB column because I'm only writing 8 characters. extreme waste of space. Not an extreme waste of space. If you use TINYBLOB, it's only 1 extra byte to store the length, so you'll be using 9 bytes instead of 8. Still, it doesn't seem like the best idea. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to enable General Query_log?
Dear Miguel: Firstly I found the path for mysqld, in my system (Linux RH7.3), it is in /usr/libexec/ Then I write this command: /usr/libexec/mysqld --log=/var/mysqldquery.log -u root however the system give me this message: bash: mysqld: command not found I don`t understand what is happen. Could you help me with this issue, please? I`d like to get the mysql error logs and query logs. Thank you, Edwin Limachi N. DATACOM - Instalaciones La Paz Tel.: 591-2-212-3978 Cel.: 591-715-29967 Fax.: 591-2-212-3975 miguel solorzano [EMAIL PROTECTED] 10/09/2003 20:11 Para [EMAIL PROTECTED], [EMAIL PROTECTED] cc Asunto Re: How to enable General Query_log? At 17:07 10/9/2003 -0400, [EMAIL PROTECTED] wrote: Hi, Notice the option syntax --log: /usr/local/mysql/libexec/mysqld --log=/usr/local/mysql/var/mysqlquery.log -uroot 030910 20:03:54 InnoDB: Started /usr/local/mysql/libexec/mysqld: ready for connections. Version: '4.0.15-debug-log' socket: '/tmp/mysql.sock' port: 3306 # tail /usr/local/mysql/var/mysqlquery.log /usr/local/mysql/libexec/mysqld, Version: 4.0.15-debug-log, started with: Tcp port: 3306 Unix socket: /tmp/mysql.sock Time Id CommandArgument 030910 20:07:05 1 Connect [EMAIL PROTECTED] on 030910 20:07:17 1 Query select version() Dear list friends: I`d like to get your help about this issue. I have a Linux box RedHat 7.3 running the MySQL v3.23.56. In this box I`m running a TACACS server for authenticating my users to access to cisco routers. TACACS is using a Mysql database where i configured the user accounts, however TACACS can not get this info from this data base. I`d like to see the query log for the MySQL. The manual says that for it we have to start mysql with a query log. I typed: shell#mysqld - - log[=/var/log/mysqlquery.log] but I get this message: bash: myslqd command not found What is the correct syntax and where have I to write it for start mysql with a query log? Thank you for your help, -- Regards, For technical support contracts, visit https://order.mysql.com/ Are you MySQL certified?, http://www.mysql.com/certification/ Miguel Angel Solórzano [EMAIL PROTECTED] São Paulo - Brazil
Re: How to enable General Query_log?
Dear Mohamed: I tried with your suggestion but when I like to see the /var/ mysql_query.log file my system says that this file doesn`t exist. What can I do? Thank you, Edwin Limachi N. DATACOM - Instalaciones La Paz Tel.: 591-2-212-3978 Cel.: 591-715-29967 Fax.: 591-2-212-3975 Mohamed Lrhazi [EMAIL PROTECTED] 11/09/2003 08:19 Para [EMAIL PROTECTED] cc Asunto Re: How to enable General Query_log? Edit /etc/my.cnf add the line for log to the mysqld section log=/var/log/mysql_query.log or whetever... then run: /etc/init.d/mysqld restart Mohamed~ On Wed, 2003-09-10 at 17:07, [EMAIL PROTECTED] wrote: Dear list friends: I`d like to get your help about this issue. I have a Linux box RedHat 7.3 running the MySQL v3.23.56. In this box I`m running a TACACS server for authenticating my users to access to cisco routers. TACACS is using a Mysql database where i configured the user accounts, however TACACS can not get this info from this data base. I`d like to see the query log for the MySQL. The manual says that for it we have to start mysql with a query log. I typed: shell#mysqld - - log[=/var/log/mysqlquery.log] but I get this message: bash: myslqd command not found What is the correct syntax and where have I to write it for start mysql with a query log? Thank you for your help,
DESPERATE - strange access behavior after creating user priviledge
Dear Friends, After days of struggling with the 2013 error which no one seems to know how to fix. I decided to install a 3.xx version of mysql, and then a new 4.0.15 version as well from binary this time. (even tried the RPM and src versions) Installation is easy Mysql Server is up I then try to connect to the server from a remote location and get ERROR 1130: Host 'xxx.yyy.zzz' is not t allowed to connect to this MySQL server which is normal since i did not create a user yet I do this: GRANT ALL PRIVILEGES ON *.* TO stew@'%' IDENTIFIED BY '123' WITH GRANT OPTION; flush privileges; and I get this again when i try to connect using mysql -h the_host_name -u stew -p ERROR 2013: Lost connection to MySQL server during query In other words, the server was not able to connect the first time because it did not have permission, but the second time it did, and i get this lousy error again. However, I am able to do the same command and connect from my XP workstation where mysql client is installed. so I have no clue why it is not working from the server where I really need it to work. I am getting really desperate after 3 weeks of this same error. Can you please help thank you Stew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
my.cnf memory specifications
I've looked and looked but really can't find an answer to this question. In my my.cnf file, these are some of the things that are specified in [mysqld]: set-variable = key_buffer=256M set-variable = table_cache=64 set-variable = sort_buffer=512K set-variable = max_connections=1000 set-variable = thread_concurrency=10 set-variable = innodb_buffer_pool_size=40M set-variable = innodb_additional_mem_pool_size=40M On FreeBSD, I only have one process running which makes this information pretty straight forward. On my linux boxes, there are many different processes running (on one box, there is 31). Does this mean that *each* of those 31 processes attempts to allocate the above amount of resources, or that the above resources are being shared between the 31 processes? If anyone could straighten me out here, I would truly appreciate it! Cheers, Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to enable General Query_log?
I haven't used 9.0, I'm using 7.3, but I suspect it should be very similar, as root, edit the /etc/my.cnf file and add a couple of lines to the top section: it should look similar to this [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log=/var/log/mysql/mysqllog log-update=/var/log/mysql/mysqlupdate the two log etries probably aren't there, add them and save the file then run these commands: cd /var/log mkdir mysql chown mysql.mysql mysql chmod 700 mysql then you should be able to restart the mysql server with this command service mysql restart this should restart the server and then the logs should be filling up with all the queries and most importantly the update log will record all the actual changes that occur in the database, so you can reconstruct it from a known point, or revert back from a backup and bring the database back to a point where something bad happened, human or machine wise.. :) and then when you have this running, you probably should run the mysqldump program from the command line, and use the mysqldump --opt --flush-logs --all-databases 20030911.mysqldump.sql and that should give you a good backup from right now and the update log files will be restarted from this point in time.. Kelley [EMAIL PROTECTED] wrote: Dear Miguel: Firstly I found the path for mysqld, in my system (Linux RH7.3), it is in /usr/libexec/ Then I write this command: /usr/libexec/mysqld --log=/var/mysqldquery.log -u root however the system give me this message: bash: mysqld: command not found I don`t understand what is happen. Could you help me with this issue, please? I`d like to get the mysql error logs and query logs. Thank you, Edwin Limachi N. DATACOM - Instalaciones La Paz Tel.: 591-2-212-3978 Cel.: 591-715-29967 Fax.: 591-2-212-3975 miguel solorzano [EMAIL PROTECTED] 10/09/2003 20:11 Para [EMAIL PROTECTED], [EMAIL PROTECTED] cc Asunto Re: How to enable General Query_log? At 17:07 10/9/2003 -0400, [EMAIL PROTECTED] wrote: Hi, Notice the option syntax --log: /usr/local/mysql/libexec/mysqld --log=/usr/local/mysql/var/mysqlquery.log -uroot 030910 20:03:54 InnoDB: Started /usr/local/mysql/libexec/mysqld: ready for connections. Version: '4.0.15-debug-log' socket: '/tmp/mysql.sock' port: 3306 # tail /usr/local/mysql/var/mysqlquery.log /usr/local/mysql/libexec/mysqld, Version: 4.0.15-debug-log, started with: Tcp port: 3306 Unix socket: /tmp/mysql.sock Time Id CommandArgument 030910 20:07:05 1 Connect [EMAIL PROTECTED] on 030910 20:07:17 1 Query select version() Dear list friends: I`d like to get your help about this issue. I have a Linux box RedHat 7.3 running the MySQL v3.23.56. In this box I`m running a TACACS server for authenticating my users to access to cisco routers. TACACS is using a Mysql database where i configured the user accounts, however TACACS can not get this info from this data base. I`d like to see the query log for the MySQL. The manual says that for it we have to start mysql with a query log. I typed: shell#mysqld - - log[=/var/log/mysqlquery.log] but I get this message: bash: myslqd command not found What is the correct syntax and where have I to write it for start mysql with a query log? Thank you for your help, -- Regards, For technical support contracts, visit https://order.mysql.com/ Are you MySQL certified?, http://www.mysql.com/certification/ Miguel Angel Solórzano [EMAIL PROTECTED] São Paulo - Brazil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Personal Servers
Nicola Hartland wrote: I am not sure this is the right place for this question but I am trying to find a personal server that I can install on my PC, I run XP that I can have to test the web pages. Does anyone have any suggestions and how to configure them. thanks in advance Nicci http://sourceforge.net/projects/miniserver/ No developer (who has to run windows) should be without it. =C= -- * Cal Evans * http://www.eicc.com * We take care of your IT, * So you can take care of your business. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
joinig tables(non-equal join)
Hi, I´m trying to join two tables with the follow statement SELECT t1.name,t2.name FROM table1 t1,table2 t2 WHERE t1.namet2.name. I supose the expected result must be the non-matching rows of both tables, but it´s not what I get. ¿What it´s wrong with that query?. I hope do you understand my doubt. Thanks in advanced. Alex Sent by Medscape Mail: Free Portable E-mail for Professionals on the Move http://www.medscape.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my.cnf memory specifications
At 9:29 AM -0700 9/11/03, Mark Kaufer wrote: I've looked and looked but really can't find an answer to this question. In my my.cnf file, these are some of the things that are specified in [mysqld]: set-variable = key_buffer=256M set-variable = table_cache=64 set-variable = sort_buffer=512K set-variable = max_connections=1000 set-variable = thread_concurrency=10 set-variable = innodb_buffer_pool_size=40M set-variable = innodb_additional_mem_pool_size=40M On FreeBSD, I only have one process running which makes this information pretty straight forward. On my linux boxes, there are many different processes running (on one box, there is 31). Does this mean that *each* of those 31 processes attempts to allocate the above amount of resources, or that the above resources are being shared between the 31 processes? Linux reports the threads of a process as processes. You really only have one process, with 31 threads. (Probably because a bunch of clients have connected.) So your resources are shared among the threads of the server process. If anyone could straighten me out here, I would truly appreciate it! Cheers, Mark -- 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]
Re: my.cnf memory specifications
On Thu, Sep 11, 2003 at 09:29:27AM -0700, Mark Kaufer wrote: I've looked and looked but really can't find an answer to this question. In my my.cnf file, these are some of the things that are specified in [mysqld]: set-variable = key_buffer=256M set-variable = table_cache=64 set-variable = sort_buffer=512K set-variable = max_connections=1000 set-variable = thread_concurrency=10 set-variable = innodb_buffer_pool_size=40M set-variable = innodb_additional_mem_pool_size=40M On FreeBSD, I only have one process running which makes this information pretty straight forward. On my linux boxes, there are many different processes running (on one box, there is 31). No, it's the same on all boxes. You have many threads. The difference is in how each OS presents them. As an interesting exercise, build your MySQL using LinuxThreads and notice the difference. Does this mean that *each* of those 31 processes attempts to allocate the above amount of resources, or that the above resources are being shared between the 31 processes? The sort buffer is per-connection, all the others you list are global (shared) chunks of memory. There's a section in the MySQL manual that describes how MySQL uses memory. http://www.mysql.com/doc/en/Memory_use.html Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 4 days, processed 156,095,869 queries (450/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: enum for bool in the future
Abs wrote:
hi
i know this has been discussed before, storing bools
in the right column type. in the case of enum as:
enum(N,Y) or n,y, etc. it might be easy to
read when u're looking at the database table itself,
but if u had to communicate this properly to other
programmers designing a front-end, they wouldn't be
checking for BOOLS, rather for: if ($value==Y) { }
else { }
(add to this, the case of string or char, which would
matter more when using a C program or any other
language that doesn't type cast as per the convenience
of the statement)
is there any combination that would facilitate using
it as just: if ($value) { //true } else { //false } ?
Yes. I declare the column as tinyint, then use 0 for false and 1 for
true. This works well because mysql, perl, php, etc. treat 0 as false
and 1 (or any non-zero value) as true. For example:
mysql describe testa;
+---++--+-+-++
| Field | Type | Null | Key | Default | Extra |
+---++--+-+-++
| a | int(11)| | PRI | NULL| auto_increment |
| name | char(10) | YES | | NULL||
| flag | tinyint(4) | YES | | NULL||
+---++--+-+-++
3 rows in set (0.00 sec)
mysql select * from testa;
+---+--+--+
| a | name | flag |
+---+--+--+
| 1 | Smith|1 |
| 2 | Jones|0 |
| 5 | Williams |0 |
| 6 | Simpson |1 |
+---+--+--+
4 rows in set (0.00 sec)
mysql select * from testa where flag;
+---+-+--+
| a | name| flag |
+---+-+--+
| 1 | Smith |1 |
| 6 | Simpson |1 |
+---+-+--+
2 rows in set (0.00 sec)
mysql select * from testa where not flag;
+---+--+--+
| a | name | flag |
+---+--+--+
| 2 | Jones|0 |
| 5 | Williams |0 |
+---+--+--+
2 rows in set (0.00 sec)
i think enum(NULL, ) is the only option since a NULL
would imply false or 'not true' in most languages. the
second value could be 1 for clarity (as opposed to
empty strings). but i was looking for a better option.
checking for isnull, etc. is almost the same problem
as the first case, which is easier to read if we had
to.
thanks
abs
I don't think this is a good idea. In SQL, NULL does not mean false, it
means unknown or no value. NULL is treated as neither true nor
false. For example:
mysql update testa set flag=null where a=5;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql select * from testa;
+---+--+--+
| a | name | flag |
+---+--+--+
| 1 | Smith|1 |
| 2 | Jones|0 |
| 5 | Williams | NULL |
| 6 | Simpson |1 |
+---+--+--+
4 rows in set (0.00 sec)
mysql select * from testa where flag;
+---+-+--+
| a | name| flag |
+---+-+--+
| 1 | Smith |1 |
| 6 | Simpson |1 |
+---+-+--+
2 rows in set (0.00 sec)
mysql select * from testa where not flag;
+---+---+--+
| a | name | flag |
+---+---+--+
| 2 | Jones |0 |
+---+---+--+
1 row in set (0.00 sec)
As you can see, the row with flag=NULL was not returned in either case.
(This is why I usually use tinyint not null for boolean, unless I'm
really sure I want to allow this.)
Of course, how this is handled in your external programming language is
no doubt dependent on the language and the method of interfacing with mysql.
In any case, it seems to me that the value of using an enum column for
boolean is to give the values human-readable names, at the cost of a
little extra testing. Once you drop the human-readable values, I see no
advantage of using enum over using tinyint.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DESPERATE - strange access behavior after creating user priviledge
I then try to connect to the server from a remote location and get ERROR 1130: Host 'xxx.yyy.zzz' is not t allowed to connect to this MySQL server which is normal since i did not create a user yet I do this: GRANT ALL PRIVILEGES ON *.* TO stew@'%' IDENTIFIED BY '123' WITH GRANT OPTION; flush privileges; and I get this again when i try to connect using mysql -h the_host_name -u stew -p ERROR 2013: Lost connection to MySQL server during query In other words, the server was not able to connect the first time because it did not have permission, but the second time it did, and i get this lousy error again. However, I am able to do the same command and connect from my XP workstation where mysql client is installed. so I have no clue why it is not working from the server where I really need it to work. I am getting really desperate after 3 weeks of this same error. Can you please help Assuming mysql server is running on a unix box, does the connecting client have permissions to mysqld in the hosts.allow file? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my.cnf memory specifications
On Thu, Sep 11, 2003 at 12:06:23PM -0500, Paul DuBois wrote: Linux reports the threads of a process as processes. You really only have one process, with 31 threads. (Probably because a bunch of clients have connected.) So your resources are shared among the threads of the server process. Some versions of Linux act more like Solaris now. When I tested MySQL on RH9 using NPTL, I found that it reported a single process in top/ps/etc. That was a bit of a surprise... Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 4 days, processed 156,290,200 queries (450/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my.cnf memory specifications
At 10:17 AM -0700 9/11/03, Jeremy Zawodny wrote: On Thu, Sep 11, 2003 at 12:06:23PM -0500, Paul DuBois wrote: Linux reports the threads of a process as processes. You really only have one process, with 31 threads. (Probably because a bunch of clients have connected.) So your resources are shared among the threads of the server process. Some versions of Linux act more like Solaris now. When I tested MySQL on RH9 using NPTL, I found that it reported a single process in top/ps/etc. That was a bit of a surprise... A pleasant surprise! Thanks. By the way, Jeremy's original answer was more correct than mine, because he noted where I did not that the sort buffer is a per-client resource and is allocated once for each client -- or at least for each client that issues queries requiringn sorting, such as those with ORDER BY clauses. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 4 days, processed 156,290,200 queries (450/sec. avg) -- 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]
Re: joinig tables(non-equal join)
xander xxx wrote: Hi, I´m trying to join two tables with the follow statement SELECT t1.name,t2.name FROM table1 t1,table2 t2 WHERE t1.namet2.name. I supose the expected result must be the non-matching rows of both tables, but it´s not what I get. ¿What it´s wrong with that query?. I hope do you understand my doubt. Thanks in advanced. I'm not sure what you mean by the non-matching rows of both tables... are you expecting to find all rows in each table that have no matches in the other? If so, this query won't do it -- for each row in t1, it will return every t2 row except for one (if any) which does not match the t1 row. Bruce Feist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query optimizer decision to use index depends on data?
I have two tables which are exactly the same but have different set of data on them. I wrote a query and used EXPLAIN to see how it would run on each table. It turn out that the same query will use index on one table but not the other. So I delete all data on the bad table (the one that won't use index) and copy data from the good table over. Now it says it will use index. Is this a bug? Anan Tongprasith [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Foreign key update and Error :: 1217
I really appreciate your help with this. Hmmm... So, while SET FOREIGN_KEY_CHECKS allowed me to ALTER the table, now I can't UPDATE the company that has a rep with the same company_code because: Error :: 1217 :: Cannot delete a parent row: a foreign key constraint fails UPDATE company_table SET company_name = 'InteractNetworks, Inc', company_code = 'TEST1234', ... However, I can update a company that doesn't have a company_code set (and so no reps either). I can also manually change the rep_company_code. But once again, if I try to change the company that has a code that a rep also has the same code, I get that error 1217 above. You should fix your data first: find rows in the rep_table that don't have parent row in the the company_table, then add parent row(s) to the company_table for those rows or delete child row(s) from rep_table. After that you can create FOREIGN KEY with ON UPDATE CASCADE. I don't follow you here. I don't think I have rows in the child (rep) table that don't have a match in the parent (company) table. mysql select rep_id, rep_fname, rep_company_code from rep_table; ++-+--+ | rep_id | rep_fname | rep_company_code | ++-+--+ | 1 | Daevid | bunk4321 | | 5 | Test| interact | | 8 | Mark| interact | | 9 | Joe | interact | [ snip ] | 10 | Tony| interact | | 15 | Bob | interact | | 17 | John| interact | ++-+--+ 17 rows in set (0.00 sec) There are holes because some reps were deleted. But as I'm just building this portion of the CRM, I have only used interact and bunk4321 as the rep_company_code and also in the company_table as company_code. I had set these manually in each table. mysql select company_id, company_name, company_code from company_table limit 5; ++-+--+ | company_id | company_name| company_code | ++-+--+ | 7 | City Of Seattle | | | 8 | Port of Seattle | | | 9 | Test Company| | | 10 | Metropolitian | | | 11 | DIO, Inc. | | | 91 | Bunk Company| bunk4321 | [snip] |276 | InteractNetworks, Inc | interact | ++-+--+ -Original Message- Daevid Vincent [EMAIL PROTECTED] wrote: ALTER TABLE rep_table ADD FOREIGN KEY (`rep_company_code`) REFERENCES `company_table` (`company_code`) ON UPDATE CASCADE; But get ERROR 1216: Cannot add a child row: a foreign key constraint fails It means that you have row(s) in the child table that doesn't have parent row. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query optimizer decision to use index depends on data?
I don't think so. Based on what I've read, MySQL always assumes and equal distribution of data for an index. Based on this assumption it may determine that it's just not worth it to use the index, like if your search will return most of the records. You should periodically optimize your indexes, at which time MySQL will reevaluate the distribution of data and thus it's optimization rules. You can optimize you indexes with the myisamchk command and the --analyze parameter. If you are using InnoDB tables, I don't think there is a way to do this. On Thursday, September 11, 2003, at 01:30 PM, Tongprasith, Anan wrote: I have two tables which are exactly the same but have different set of data on them. I wrote a query and used EXPLAIN to see how it would run on each table. It turn out that the same query will use index on one table but not the other. So I delete all data on the bad table (the one that won't use index) and copy data from the good table over. Now it says it will use index. Is this a bug? -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my.cnf memory specifications
By the way, Jeremy's original answer was more correct than mine, because he noted where I did not that the sort buffer is a per-client resource and is allocated once for each client -- or at least for each client that issues queries requiringn sorting, such as those with ORDER BY clauses. Thanks Paul and Jeremy for the replies. That helped out a great deal and I very well may recompile the MySQL installs on my FreeBSD boxes using LinuxThreads. Now I'm wondering just how optimised (or non-optimised as the case may be) my configurations are. Below are some settings specified in the my.cnf of a linux box with 2Gb of memory that I'd say roughly 75%-80% of its purpose in life is dedicated to MySQL: set-variable= key_buffer=256M set-variable= max_allowed_packet=1M set-variable= table_cache=64 set-variable= sort_buffer=2M set-variable= net_buffer_length=8K set-variable= myisam_sort_buffer_size=2M set-variable= max_connections=1000 set-variable= thread_concurrency=10 innodb_data_file_path = ibdata1:10M:autoextend set-variable= innodb_buffer_pool_size=40M set-variable= innodb_additional_mem_pool_size=40M set-variable= innodb_log_file_size=5M set-variabl e = innodb_log_buffer_size=5M innodb_flush_log_at_trx_commit=1 set-variable= innodb_lock_wait_timeout=50 Am I allocating too little memory to table_cache, sort_buffer_size, and innodb_buffer_pool_size given the 2Gb of memory? So I can also adjust these settings on other boxes, is there a way to mathematically determine what percentage of totally memory to set these variables to? Thanks again. Cheers, Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DESPERATE - strange access behavior after creating user priviledge
Thanks for your answer, this is what I ended up putting in my /etc/hosts.allow file, just to remove any doubt ALL : ALL : allow auth : ALL : allow mysqld: ALL : allow At 10:17 AM 9/11/2003 -0700, Jennifer Goodie wrote: Assuming mysql server is running on a unix box, does the connecting client have permissions to mysqld in the hosts.allow file? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
The Compilation Error
I try to compile MySQL/InnoDB 4.1.1 ( loaded from BK ) My Configure Line is: export CFLAGS=-O6 -mpentiumpro export CXX=gcc export CXXFLAGS=-O6 -mpentiumpro -felide-constructors -fno-exceptions -fno-rtti ./configure --with-innodb --without-isam --without-docs --without-bench --with-charset=latin1 --without-bdb --without-debug --enable-assembler --enable-local-infile --with-mysqld-user=mysql --with-gnu-ld --prefix=/usr/local/mysql4 The error is: make[2]: Entering directory `/temp/mysql-development/mysql-4.1-win/sql' bison -y -d sql_yacc.yy mv y.tab.c sql_yacc.cc conflicts: 365 shift/reduce sql_yacc.yy:5357: fatal error: maximum table size (32767) exceeded make[2]: *** [sql_yacc.cc] Error 1 make[2]: Leaving directory `/temp/mysql-development/mysql-4.1-win/sql' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/temp/mysql-development/mysql-4.1-win' make: *** [all] Error 2 scribthree:/temp/mysql-development/mysql-4.1-win# What is the error ? InnoDB,MySQL,QUery,Help ! - ++ Dyego Souza do Carmo ++ Dep. Desenvolvimento - E S C R I B A I N F O R M A T I C A - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 --ICQ : 1647350 $ look into my eyes Phone : +55 041 296-2311 look: cannot open my eyes Fax : +55 041 296-6640 - Reply: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RE: joinig tables(non-equal join)
ok... I think this'll work... select unique t1.* from table1 as t1 left outer join table2 as t2 on t2.name = t1.name where t2.name is null -Original Message- From: xander xxx [mailto:[EMAIL PROTECTED] Sent: Thursday, September 11, 2003 2:56 AM To: Dan Greene Subject: Re: RE: joinig tables(non-equal join) Yes, that´s exactly what i get, but that´s not what i want. I wanna get all nanes in table1 that don´t appear in table2, and all names in table2 that don´t appear in table1. That´s why i use in the query. If i use = instead of i get all names in table1 that appear in table2, then, How can i get the oposite? Thanks, and please, forgive my bad english. Alex Sent by Medscape Mail: Free Portable E-mail for Professionals on the Move http://www.medscape.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to choose distinct items from two similar tables
Dear Listmembers, I have two tables, one is an updated version of the other. How can I find only the new items on the newer table? Table newtest mom flowers dad tools dave video matt magazine Table newtest2 mom flowers dad tools dave video matt magazine gram book erin flower Query: SELECT newtest2.Name,newtest2.Item FROM newtest2,newtest WHERE newtest2.Name != newtest.Name GROUP BY Name; This query returns only the 4 common records when I use = but returns all 6 records when I use or !=. I want only the last 2 items from Table newtest2. What am I doing wrong? Thanks, Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UNION
Hi, I'm trying to do a simple UNION in MySQL (4.0.11a on Linux). I am trying the following ... If I do SELECT THIS.PRODUCT_ID,net.ajsoft.WebShop.Inventory.Product AS JPOXMETADATA,THIS.ID FROM PRODUCT THIS LEFT OUTER JOIN BOOK SUBCLASS0 ON THIS.PRODUCT_ID = SUBCLASS0.BOOK_ID WHERE SUBCLASS0.BOOK_ID IS NULL AND THIS.PRICE .15E3 i get ++--+---+ | PRODUCT_ID | JPOXMETADATA | ID| ++--+---+ | 3 | net.ajsoft.WebShop.Inventory.Product | P_005 | ++--+---+ If I also do SELECT THIS.PRODUCT_ID,net.ajsoft.WebShop.Inventory.Products.Book AS JPOXMETADATA,THIS.ID FROM PRODUCT THIS INNER JOIN BOOK SUBCLASS ON THIS.PRODUCT_ID= SUBCLASS.BOOK_ID WHERE THIS.PRICE .15E3; i get +++---+ | PRODUCT_ID | JPOXMETADATA | ID| +++---+ | 1 | net.ajsoft.WebShop.Inventory.Products.Book | P_003 | | 2 | net.ajsoft.WebShop.Inventory.Products.Book | P_004 | +++---+ Yet when I do SELECT THIS.PRODUCT_ID,net.ajsoft.WebShop.Inventory.Product AS JPOXMETADATA,THIS.ID FROM PRODUCT THIS LEFT OUTER JOIN BOOK SUBCLASS0 ON THIS.PRODUCT_ID = SUBCLASS0.BOOK_ID WHERE SUBCLASS0.BOOK_ID IS NULL AND THIS.PRICE .15E3 UNION SELECT THIS.PRODUCT_ID,net.ajsoft.WebShop.Inventory.Products.Book AS JPOXMETADATA,THIS.ID FROM PRODUCT THIS INNER JOIN BOOK SUBCLASS ON THIS.PRODUCT_ID= SUBCLASS.BOOK_ID WHERE THIS.PRICE .15E3; i get ++--+---+ | PRODUCT_ID | JPOXMETADATA | ID| ++--+---+ | 3 | net.ajsoft.WebShop.Inventory.Product | P_005 | | 1 | net.ajsoft.WebShop.Inventory.Product | P_003 | | 2 | net.ajsoft.WebShop.Inventory.Product | P_004 | ++--+---+ Notice that the JPOXMETADATA column is incorrect for PRODUCT_ID=1 and 2. Any ideas anyone ? -- Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql dump speed
I have a bit of an issue with mysqldumps and inserting it back into mysql. Granted, I have 1 table that has over 1Million entries, but generating these tables takes about 3 hours with a java app. But when I do a mysql -u root -p the mysql dump.dmp it is taking about 28 hours to complete the load. Can anyone tell me what is taking so long? Any thoughts as how to speed it up? Thanks in advance. Dan orlic
RE: Query optimizer decision to use index depends on data?
I think you are right. I try adding and deleting data one by one and found no particular data that will always turn off index in all circumstances. myisamchk --analyze doesn't help. Is there a way to force using index? I don't think optimizer is smart enough. My table has more than 30,000 rows. The query returns only 6,000 rows. I modify the query's where clause to use indexed column only (to see the ratio between hit and miss on index). It returns 7,000 rows. Yet MySQL refuses to use index. I tried use index in the query and it didn't help. Anan T. -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: Thursday, September 11, 2003 2:18 PM To: Tongprasith, Anan Cc: [EMAIL PROTECTED] Subject: Re: Query optimizer decision to use index depends on data? I don't think so. Based on what I've read, MySQL always assumes and equal distribution of data for an index. Based on this assumption it may determine that it's just not worth it to use the index, like if your search will return most of the records. You should periodically optimize your indexes, at which time MySQL will reevaluate the distribution of data and thus it's optimization rules. You can optimize you indexes with the myisamchk command and the --analyze parameter. If you are using InnoDB tables, I don't think there is a way to do this. On Thursday, September 11, 2003, at 01:30 PM, Tongprasith, Anan wrote: I have two tables which are exactly the same but have different set of data on them. I wrote a query and used EXPLAIN to see how it would run on each table. It turn out that the same query will use index on one table but not the other. So I delete all data on the bad table (the one that won't use index) and copy data from the good table over. Now it says it will use index. Is this a bug? -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign key update and Error :: 1217
Daevid, there is really one update you need to do first: upgrade to MySQL-4.0.14. http://www.innodb.com/ibman.html#InnoDB_foreign_keys Starting from version 3.23.50, you can also associate the ON DELETE CASCADE or ON DELETE SET NULL clause with the foreign key constraint. Corresponding ON UPDATE options are available starting from 4.0.8. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL - Original Message - From: Daevid Vincent [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, September 11, 2003 9:10 PM Subject: RE: Foreign key update and Error :: 1217 I really appreciate your help with this. Hmmm... So, while SET FOREIGN_KEY_CHECKS allowed me to ALTER the table, = now I can't UPDATE the company that has a rep with the same company_code because: Error :: 1217 :: Cannot delete a parent row: a foreign key constraint = fails UPDATE company_table SET company_name =3D 'InteractNetworks, Inc', company_code =3D 'TEST1234', ... However, I can update a company that doesn't have a company_code set = (and so no reps either). I can also manually change the rep_company_code. But once again, if I try to change the company that has a code that a = rep also has the same code, I get that error 1217 above. You should fix your data first: find rows in the rep_table=20 that don't have parent row in the the company_table, then add=20 parent row(s) to the company_table for those rows or delete=20 child row(s) from rep_table. After that you can create=20 FOREIGN KEY with ON UPDATE CASCADE. I don't follow you here. I don't think I have rows in the child (rep) = table that don't have a match in the parent (company) table. mysql select rep_id, rep_fname, rep_company_code from rep_table;= =20 ++-+--+ | rep_id | rep_fname | rep_company_code | ++-+--+ | 1 | Daevid | bunk4321 | | 5 | Test| interact | | 8 | Mark| interact | | 9 | Joe | interact | [ snip ] | 10 | Tony| interact | | 15 | Bob | interact | | 17 | John| interact | ++-+--+ 17 rows in set (0.00 sec) There are holes because some reps were deleted. But as I'm just building this portion of the CRM, I have only used interact and bunk4321 as the rep_company_code and also in the company_table as company_code. I had set these manually in each table. mysql select company_id, company_name, company_code from company_table limit 5; ++-+--+ | company_id | company_name| company_code | ++-+--+ | 7 | City Of Seattle | | | 8 | Port of Seattle | | | 9 | Test Company| | | 10 | Metropolitian| | | 11 | DIO, Inc. | | | 91 | Bunk Company| bunk4321 | [snip] |276 | InteractNetworks, Inc | interact | ++-+--+ -Original Message- Daevid Vincent [EMAIL PROTECTED] wrote: ALTER TABLE rep_table ADD FOREIGN KEY (`rep_company_code`)=20 REFERENCES `company_table` (`company_code`) ON UPDATE CASCADE; =20 But get ERROR 1216: Cannot add a child row: a foreign key=20 constraint fails =20 It means that you have row(s) in the child table that doesn't=20 have parent row. -- 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: Query optimizer decision to use index depends on data?
Never mind. New MySQL has force index option. Thanks for your help Brent. Anan T. -Original Message- From: Tongprasith, Anan Sent: Thursday, September 11, 2003 3:32 PM To: Brent Baisley Cc: [EMAIL PROTECTED] Subject: RE: Query optimizer decision to use index depends on data? I think you are right. I try adding and deleting data one by one and found no particular data that will always turn off index in all circumstances. myisamchk --analyze doesn't help. Is there a way to force using index? I don't think optimizer is smart enough. My table has more than 30,000 rows. The query returns only 6,000 rows. I modify the query's where clause to use indexed column only (to see the ratio between hit and miss on index). It returns 7,000 rows. Yet MySQL refuses to use index. I tried use index in the query and it didn't help. Anan T. -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: Thursday, September 11, 2003 2:18 PM To: Tongprasith, Anan Cc: [EMAIL PROTECTED] Subject: Re: Query optimizer decision to use index depends on data? I don't think so. Based on what I've read, MySQL always assumes and equal distribution of data for an index. Based on this assumption it may determine that it's just not worth it to use the index, like if your search will return most of the records. You should periodically optimize your indexes, at which time MySQL will reevaluate the distribution of data and thus it's optimization rules. You can optimize you indexes with the myisamchk command and the --analyze parameter. If you are using InnoDB tables, I don't think there is a way to do this. On Thursday, September 11, 2003, at 01:30 PM, Tongprasith, Anan wrote: I have two tables which are exactly the same but have different set of data on them. I wrote a query and used EXPLAIN to see how it would run on each table. It turn out that the same query will use index on one table but not the other. So I delete all data on the bad table (the one that won't use index) and copy data from the good table over. Now it says it will use index. Is this a bug? -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- 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: Query optimizer decision to use index depends on data?
On 11 Sep 2003 at 15:31, Tongprasith, Anan wrote: I think you are right. I try adding and deleting data one by one and found no particular data that will always turn off index in all circumstances. myisamchk --analyze doesn't help. Is there a way to force using index? Yes, 'USE INDEX (index_name)' in the SELECT statement after the table name should do it, though MySQL still won't use the index if it's not useful. I don't think optimizer is smart enough. My table has more than 30,000 rows. The query returns only 6,000 rows. I modify the query's where clause to use indexed column only (to see the ratio between hit and miss on index). It returns 7,000 rows. Yet MySQL refuses to use index. I tried use index in the query and it didn't help. I think you may be confused about indexes. Whether an index is being used or not has no effect on the number of rows returned, just on how fast they're returned. But maybe I'm misunderstanding. Can you post the output of SHOW CREATE TABLE for your table and EXPLAIN for your queries? The answer to your initial question is that, yes, the optimizer is affected by the data in the table. It's not a bug. Otherwise it wouldn't be mouch of an optimizer. It still does make mistakes, though, and USE INDEX should solve the problem in those cases. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Temporary tables
Mikhail Entaltsev [EMAIL PROTECTED] wrote: Hi, I've found the phrase in MySQL documentation http://www.mysql.com/doc/en/Temporary_table_problems.html You can't use temporary tables more than once in the same query. For example, the following doesn't work. mysql SELECT * FROM temporary_table, temporary_table AS t2; Does it mean that I can't use THE SAME temporary table twice in THE SAME query? Yes. Or does it mean that I can't use more than 1 temporary table in the query at all? No. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to enable General Query_log?
Dear Kelley: We tryed with your suggestion, thank you very much. We can see the mysqlupdate and 20030911.mysqldump files. However when we connect an do some queries to our Mysql server, we can not see anything, the columns Time, Id Command and Argument are in blank. Is there something additional variables that we have to configure ? Thank you for your support, EDWIN LIMACHI N. DATACOM - Instalaciones TSE - INFONET BOLIVIA Phone. 591-2-2123978 Movil: 591-715-29967 Fax: 591-2-2123975 Kelley Lingerfelt [EMAIL PROTECTED] 11/09/2003 12:39 Para [EMAIL PROTECTED], [EMAIL PROTECTED] cc Asunto Re: How to enable General Query_log? I haven't used 9.0, I'm using 7.3, but I suspect it should be very similar, as root, edit the /etc/my.cnf file and add a couple of lines to the top section: it should look similar to this [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log=/var/log/mysql/mysqllog log-update=/var/log/mysql/mysqlupdate the two log etries probably aren't there, add them and save the file then run these commands: cd /var/log mkdir mysql chown mysql.mysql mysql chmod 700 mysql then you should be able to restart the mysql server with this command service mysql restart this should restart the server and then the logs should be filling up with all the queries and most importantly the update log will record all the actual changes that occur in the database, so you can reconstruct it from a known point, or revert back from a backup and bring the database back to a point where something bad happened, human or machine wise.. :) and then when you have this running, you probably should run the mysqldump program from the command line, and use the mysqldump --opt --flush-logs --all-databases 20030911.mysqldump.sql and that should give you a good backup from right now and the update log files will be restarted from this point in time.. Kelley [EMAIL PROTECTED] wrote: Dear Miguel: Firstly I found the path for mysqld, in my system (Linux RH7.3), it is in /usr/libexec/ Then I write this command: /usr/libexec/mysqld --log=/var/mysqldquery.log -u root however the system give me this message: bash: mysqld: command not found I don`t understand what is happen. Could you help me with this issue, please? I`d like to get the mysql error logs and query logs. Thank you, Edwin Limachi N. DATACOM - Instalaciones La Paz Tel.: 591-2-212-3978 Cel.: 591-715-29967 Fax.: 591-2-212-3975 miguel solorzano [EMAIL PROTECTED] 10/09/2003 20:11 Para [EMAIL PROTECTED], [EMAIL PROTECTED] cc Asunto Re: How to enable General Query_log? At 17:07 10/9/2003 -0400, [EMAIL PROTECTED] wrote: Hi, Notice the option syntax --log: /usr/local/mysql/libexec/mysqld --log=/usr/local/mysql/var/mysqlquery.log -uroot 030910 20:03:54 InnoDB: Started /usr/local/mysql/libexec/mysqld: ready for connections. Version: '4.0.15-debug-log' socket: '/tmp/mysql.sock' port: 3306 # tail /usr/local/mysql/var/mysqlquery.log /usr/local/mysql/libexec/mysqld, Version: 4.0.15-debug-log, started with: Tcp port: 3306 Unix socket: /tmp/mysql.sock Time Id CommandArgument 030910 20:07:05 1 Connect [EMAIL PROTECTED] on 030910 20:07:17 1 Query select version() Dear list friends: I`d like to get your help about this issue. I have a Linux box RedHat 7.3 running the MySQL v3.23.56. In this box I`m running a TACACS server for authenticating my users to access to cisco routers. TACACS is using a Mysql database where i configured the user accounts, however TACACS can not get this info from this data base. I`d like to see the query log for the MySQL. The manual says that for it we have to start mysql with a query log. I typed: shell#mysqld - - log[=/var/log/mysqlquery.log] but I get this message: bash: myslqd command not found What is the correct syntax and where have I to write it for start mysql with a query log? Thank you for your help, -- Regards, For technical support contracts, visit https://order.mysql.com/ Are you MySQL certified?, http://www.mysql.com/certification/ Miguel Angel Solórzano [EMAIL PROTECTED] São Paulo - Brazil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with make test for 4.0.14
Hi again, Does any of you have an idea on this? I get the same error in 4.0.15. I could find that the problematic code was in mysql-test/mysql-test-run.sh: sleep_until_file_created $MASTER_MYPID $wait_for_master wait_for_master=$SLEEP_TIME_FOR_SECOND_MASTER MASTER_RUNNING=1 I guess I could probably disable that code from the test program and compile anyway, but would the resulting MySQL installation work? Also, there are currently RPMs of MySQL installed on the server. Should these be removed before compiling? Any help would be greatly appreciated. Pierre-Luc Soucy Pierre-Luc Soucy wrote: Hi, I'm trying to compile MySQL 4.0.14 on a server which was previously using RPMs, and although the ./configure (with no arguments) and make steps went fine, make test does not want to work. The error I am getting is : /home/programs/mysql/4.0.14/mysql-4.0.14/mysql-test/var/run/master.pid was not created in 30 seconds; Aborting (full make test output at the bottom of this message). What's wrong, and what should I do to correct this? Thanks! Pierre-Luc Soucy Full make test output: [EMAIL PROTECTED] mysql-4.0.14]# make test cd mysql-test ; ./mysql-test-run Installing Test Databases Removing Stale Files Installing Master Databases running ../sql/mysqld --no-defaults --bootstrap --skip-grant-tables --basedir=. --datadir=./var/master-data --skip-innodb --skip-bdb --language=../sql/share/english/ 030905 22:18:12 ../sql/mysqld: Shutdown Complete Installing Slave Databases running ../sql/mysqld --no-defaults --bootstrap --skip-grant-tables --basedir=. --datadir=./var/slave-data --skip-innodb --skip-bdb --language=../sql/share/english/ 030905 22:18:12 ../sql/mysqld: Shutdown Complete Manager disabled, skipping manager start. Loading Standard Test Databases Starting Tests TEST RESULT -- alias [ pass ] alter_table[ pass ] analyse[ pass ] ansi [ pass ] auto_increment [ pass ] backup [ pass ] bdb-alter-table-1 [ skipped ] bdb-alter-table-2 [ skipped ] bdb-crash [ skipped ] bdb-deadlock [ skipped ] bdb[ skipped ] bdb_cache [ skipped ] bench_count_distinct [ pass ] bigint [ pass ] binary [ pass ] bool [ pass ] bulk_replace [ pass ] case [ pass ] cast [ pass ] check [ pass ] comments [ pass ] compare[ pass ] constraints[ pass ] convert[ pass ] count_distinct [ pass ] count_distinct2[ pass ] create [ pass ] ctype_cp1251 [ pass ] ERROR: /home/programs/mysql/4.0.14/mysql-4.0.14/mysql-test/var/run/master.pid was not created in 30 seconds; Aborting make: *** [test] Error 1 [EMAIL PROTECTED] mysql-4.0.14]# -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql dump speed
Hi Dan, - Original Message - From: dan orlic [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 11, 2003 2:30 PM Subject: mysql dump speed I have a bit of an issue with mysqldumps and inserting it back into mysql. Granted, I have 1 table that has over 1Million entries, but generating these tables takes about 3 hours with a java app. But when I do a mysql -u root -p the mysql dump.dmp it is taking about 28 hours to complete the load. Can anyone tell me what is taking so long? Any thoughts as how to speed it up? Thanks in advance. Was the dump done using the --opt option with mysqldump? This should make reloading faster. What options *were* used? How is your application doing INSERTs? LOCK TABLES and/or multi-line INSERT statements? Is it a MyISAM table? If it's InnoDB, I think you'll need to do the INSERTs in one transaction to get more speed. Maybe your app does this and mysqldump isn't. Just some thoughts. :-) Hope that helps. Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql dump speed
Have tried using the --opt parameter on mysqldump? This is supposed optimize the dump speed and create a file optimized for reloading. I haven't played with this option, so I don't know what kind of difference it makes. On Thursday, September 11, 2003, at 03:30 PM, dan orlic wrote: I have a bit of an issue with mysqldumps and inserting it back into mysql. Granted, I have 1 table that has over 1Million entries, but generating these tables takes about 3 hours with a java app. But when I do a mysql -u root -p the mysql dump.dmp it is taking about 28 hours to complete the load. Can anyone tell me what is taking so long? Any thoughts as how to speed it up? Thanks in advance. Dan orlic -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: my.cnf memory specifications
From what I understand about InnoDB, you want to make that InnoDB buffer pool as big as 60-80% of your allowable RAM. If you use only InnoDB, then the other settings won't help you to give much ram. If you use no InnoDB, then don't bother giving InnoDB buffer pool any real ram. If it works correctly, the InnoDB buffer pool should take the big chunk of ram and use that and therefore speed up your queries and such because it goes to disk less. I can't get my 4G machine to take any more than .5G of ram which is really annoying me. -Original Message- From: Mark Kaufer [mailto:[EMAIL PROTECTED] Sent: Thursday, September 11, 2003 11:30 AM To: [EMAIL PROTECTED] Subject: Re: my.cnf memory specifications By the way, Jeremy's original answer was more correct than mine, because he noted where I did not that the sort buffer is a per-client resource and is allocated once for each client -- or at least for each client that issues queries requiringn sorting, such as those with ORDER BY clauses. Thanks Paul and Jeremy for the replies. That helped out a great deal and I very well may recompile the MySQL installs on my FreeBSD boxes using LinuxThreads. Now I'm wondering just how optimised (or non-optimised as the case may be) my configurations are. Below are some settings specified in the my.cnf of a linux box with 2Gb of memory that I'd say roughly 75%-80% of its purpose in life is dedicated to MySQL: set-variable= key_buffer=256M set-variable= max_allowed_packet=1M set-variable= table_cache=64 set-variable= sort_buffer=2M set-variable= net_buffer_length=8K set-variable= myisam_sort_buffer_size=2M set-variable= max_connections=1000 set-variable= thread_concurrency=10 innodb_data_file_path = ibdata1:10M:autoextend set-variable= innodb_buffer_pool_size=40M set-variable= innodb_additional_mem_pool_size=40M set-variable= innodb_log_file_size=5M set-variabl e = innodb_log_buffer_size=5M innodb_flush_log_at_trx_commit=1 set-variable= innodb_lock_wait_timeout=50 Am I allocating too little memory to table_cache, sort_buffer_size, and innodb_buffer_pool_size given the 2Gb of memory? So I can also adjust these settings on other boxes, is there a way to mathematically determine what percentage of totally memory to set these variables to? Thanks again. Cheers, Mark -- 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: mysql dump speed
Use the --opt option when performing the dump. It can make a tremendous difference. -JF -Original Message- From: dan orlic [mailto:[EMAIL PROTECTED] Sent: Thursday, September 11, 2003 12:31 PM To: [EMAIL PROTECTED] Subject: mysql dump speed I have a bit of an issue with mysqldumps and inserting it back into mysql. Granted, I have 1 table that has over 1Million entries, but generating these tables takes about 3 hours with a java app. But when I do a mysql -u root -p the mysql dump.dmp it is taking about 28 hours to complete the load. Can anyone tell me what is taking so long? Any thoughts as how to speed it up? Thanks in advance. Dan orlic -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld crash: assertion in my_seek.c
our current situation is a mysqld crash, it hits an assertion in my_seek.c. Assertion failed: pos != (~(my_off_t) 0), file c:\build\build\mysql-4.0.14\mysys\my_seek.c, line 31 This is with a myisam table containing variable length text fields. I'm not expecting any solution from this list, but is this a common problem or is it completely unknown? If common, what workarounds are available? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my.cnf memory specifications
On Thu, Sep 11, 2003 at 11:30:20AM -0700, Mark Kaufer wrote: By the way, Jeremy's original answer was more correct than mine, because he noted where I did not that the sort buffer is a per-client resource and is allocated once for each client -- or at least for each client that issues queries requiringn sorting, such as those with ORDER BY clauses. Thanks Paul and Jeremy for the replies. That helped out a great deal and I very well may recompile the MySQL installs on my FreeBSD boxes using LinuxThreads. You'll probably find (as we have) that LinuxThreads/MySQL on FreeBSD easily outperforms FreeBSD's native threads. Now I'm wondering just how optimised (or non-optimised as the case may be) my configurations are. Below are some settings specified in the my.cnf of a linux box with 2Gb of memory that I'd say roughly 75%-80% of its purpose in life is dedicated to MySQL: set-variable= key_buffer=256M set-variable= max_allowed_packet=1M set-variable= table_cache=64 set-variable= sort_buffer=2M set-variable= net_buffer_length=8K set-variable= myisam_sort_buffer_size=2M set-variable= max_connections=1000 set-variable= thread_concurrency=10 innodb_data_file_path = ibdata1:10M:autoextend set-variable= innodb_buffer_pool_size=40M set-variable= innodb_additional_mem_pool_size=40M set-variable= innodb_log_file_size=5M set-variabl e = innodb_log_buffer_size=5M innodb_flush_log_at_trx_commit=1 set-variable= innodb_lock_wait_timeout=50 Am I allocating too little memory to table_cache, sort_buffer_size, and innodb_buffer_pool_size given the 2Gb of memory? That depends... How much data do you have in InnoDB and MyISAM tables? How many tables do you have in each? So I can also adjust these settings on other boxes, is there a way to mathematically determine what percentage of totally memory to set these variables to? Sort of, but it's not as scientific as you'd probably like. Take, for example, the InnoDB Buffer Pool. That's the chunk of memory that InnoDB reserves for caching data and indexes. You'd generally want to make it as big as you can without adversely affecting system performance overall. But if you only have 100MB of InnoDB data, there's little point in a 1GB buffer pool. Also, you find that even if you have 100GB in InnoDB, the buffer pool never gets very utilized because the actual working set of data is quite small. For adjusting the table_cache, I tend to look at the SHOW STATUS output to see how often MySQL is opening and closing tables. If it's happening a lot, then I'd think about increasing the value. What's a lot? Well, that also depends. On a low volume system 10,000 might be a lot but on a really busy system that's been up for half a year, maybe 100,000 is a lot. There's no magic formula with this stuff. It's more often a case of making simple, careful changes and watching how they affect system performance, queries per second, etc. You might also look at an interactive tool that can give you a slightly different view of what's going on. I wrote mytop partly for that purpose. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 4 days, processed 164,286,296 queries (451/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to enable General Query_log?
I'm not sure, the update files probably won't show anything unless you actually write values to the database, SELECT statements won't generate anything in the update files, but there should be another file in the directory, mysqllog and it should show all the queries and such that are taking place on the server... I bet you don't have it running as the user mysql, check the passwd file and see if a mysql user exists, and also look in the /etc/my.cnf file and see who the server is running as, I think the standard RPM from RedHat has it as mysql, and the RPM normally creates the user when the RPM is installed. in the /etc/my.cnf file look at the next section, the orginal looks similar to this. [mysql.server] user=mysql basedir=/var/lib set-variable = max_connections=1024 set-variable = wait_timeout=10 note the user, make sure whoever it designates as the user, actually exists in the passwd file and if not create the user, whoever the user is, needs to be the owner of the log directory you created, when I had you do the chown mysql.mysql mysql that was changing the ownership of that directory to the mysql user and group, make sure the user specified in the my.cnf file matches the owner of that directory, and maybe that will fix it, I bet it creates the files and then isn't able to write to them, that's what it sounds like to me... just make sure that the user in the my.cnf file is the owner of the log directory... just make sure if you do a directory listing of the /var/log/mysql directory, that the files are owned by the mysql user or whatever user was set up in the /etc/my.cnf file. Maybe that will get it, I'm not sure how to get all the pertinent info from mysql, it's the case of I've never had problems, so I'm not adept at solving problems syndrome, MySQL has always just worked like a charm for me. Kelley [EMAIL PROTECTED] wrote: Dear Kelley: We tryed with your suggestion, thank you very much. We can see the mysqlupdate and 20030911.mysqldump files. However when we connect an do some queries to our Mysql server, we can not see anything, the columns Time, Id Command and Argument are in blank. Is there something additional variables that we have to configure ? Thank you for your support, EDWIN LIMACHI N. DATACOM - Instalaciones TSE - INFONET BOLIVIA Phone. 591-2-2123978 Movil: 591-715-29967 Fax: 591-2-2123975 Kelley Lingerfelt [EMAIL PROTECTED], [EMAIL PROTECTED] Para [EMAIL PROTECTED] 11/09/2003 12:39 cc Asunto Re: How to enable General Query_log? I haven't used 9.0, I'm using 7.3, but I suspect it should be very similar, as root, edit the /etc/my.cnf file and add a couple of lines to the top section: it should look similar to this [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log=/var/log/mysql/mysqllog log-update=/var/log/mysql/mysqlupdate the two log etries probably aren't there, add them and save the file then run these commands: cd /var/log mkdir mysql chown mysql.mysql mysql chmod 700 mysql then you should be able to restart the mysql server with this command service mysql restart this should restart the server and then the logs should be filling up with all the queries and most importantly the update log will record all the actual changes that occur in the database, so you can reconstruct it from a known point, or revert back from a backup and bring the database back to a point where something bad happened, human or machine wise.. :) and then when you have this running, you probably should run the mysqldump program from the command line, and use the mysqldump --opt --flush-logs --all-databases 20030911.mysqldump.sql and that should give you a good backup from right now and the update log files will be restarted from this point in time.. Kelley [EMAIL PROTECTED] wrote: Dear Miguel: Firstly I found the path for mysqld, in my system (Linux RH7.3), it is in /usr/libexec/ Then I write this command: /usr/libexec/mysqld --log=/var/mysqldquery.log -u root however the system give me this message: bash: mysqld: command not found I don`t understand what is happen. Could you help me with this issue, please? I`d like to get the mysql error logs and query logs. Thank you, Edwin Limachi N. DATACOM - Instalaciones La Paz Tel.: 591-2-212-3978 Cel.: 591-715-29967 Fax.: 591-2-212-3975 miguel solorzano [EMAIL PROTECTED] 10/09/2003 20:11 Para [EMAIL PROTECTED], [EMAIL PROTECTED] cc Asunto Re: How to enable General Query_log? At 17:07 10/9/2003 -0400, [EMAIL PROTECTED] wrote: Hi, Notice the option syntax --log: /usr/local/mysql/libexec/mysqld --log=/usr/local/mysql/var/mysqlquery.log -uroot 030910 20:03:54 InnoDB: Started /usr/local/mysql/libexec/mysqld: ready for connections
Re: set auto commit
On Thu, Sep 11, 2003 at 02:30:59PM +1000, Daniel Rossi wrote: when is it a good time to do the set auto commit=0 , while doing the update or insert query or sometime before that in the application ? Whenever you need explicit transactions. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 4 days, processed 165,074,846 queries (451/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with make test for 4.0.14
On Thu, Sep 11, 2003 at 04:10:21PM -0400, Pierre-Luc Soucy wrote: Hi again, Does any of you have an idea on this? I get the same error in 4.0.15. I could find that the problematic code was in mysql-test/mysql-test-run.sh: sleep_until_file_created $MASTER_MYPID $wait_for_master wait_for_master=$SLEEP_TIME_FOR_SECOND_MASTER MASTER_RUNNING=1 Interestingly, a friend of mine gets that on his single CPU Debian Linux box. But using the *exact same* source tree on my faster, dual CPU Debian Linux box doesn't trigger the bug. I haven't looked into it in any depth yet, but it sure smells like a race condition to me... Jeremy Pierre-Luc Soucy Full make test output: [EMAIL PROTECTED] mysql-4.0.14]# make test cd mysql-test ; ./mysql-test-run Installing Test Databases Removing Stale Files Installing Master Databases running ../sql/mysqld --no-defaults --bootstrap --skip-grant-tables --basedir=. --datadir=./var/master-data --skip-innodb --skip-bdb --language=../sql/share/english/ 030905 22:18:12 ../sql/mysqld: Shutdown Complete Installing Slave Databases running ../sql/mysqld --no-defaults --bootstrap --skip-grant-tables --basedir=. --datadir=./var/slave-data --skip-innodb --skip-bdb --language=../sql/share/english/ 030905 22:18:12 ../sql/mysqld: Shutdown Complete Manager disabled, skipping manager start. Loading Standard Test Databases Starting Tests TEST RESULT -- alias [ pass ] alter_table[ pass ] analyse[ pass ] ansi [ pass ] auto_increment [ pass ] backup [ pass ] bdb-alter-table-1 [ skipped ] bdb-alter-table-2 [ skipped ] bdb-crash [ skipped ] bdb-deadlock [ skipped ] bdb[ skipped ] bdb_cache [ skipped ] bench_count_distinct [ pass ] bigint [ pass ] binary [ pass ] bool [ pass ] bulk_replace [ pass ] case [ pass ] cast [ pass ] check [ pass ] comments [ pass ] compare[ pass ] constraints[ pass ] convert[ pass ] count_distinct [ pass ] count_distinct2[ pass ] create [ pass ] ctype_cp1251 [ pass ] ERROR: /home/programs/mysql/4.0.14/mysql-4.0.14/mysql-test/var/run/master.pid was not created in 30 seconds; Aborting make: *** [test] Error 1 [EMAIL PROTECTED] mysql-4.0.14]# -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 4 days, processed 165,115,387 queries (451/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about InnoDB and external locking
Ok, here's another question. Given effective external locking by the OS, could MyISAM tables achieve this? If so, do you know any operating systems that would have reliable external locking? Mike Quoting Jeremy Zawodny [EMAIL PROTECTED]: On Wed, Sep 10, 2003 at 06:00:55PM -0600, Mike Hillyer wrote: Hi All; First of all, I think this will probably be a question for Heikki. If I remember correctly, InnoDB and the MySQL external locking flag are unrelated as InnoDB tables are unaffected by external locks. Now the question: is it possible for two MySQL servers to access the same tablespace in a shared disk cluster? I'm not Heikki, but I'm also pretty sure you can't do that today with InnoDB tables. Not only would performance suffer, I don't believe there's a mechanism in place for a shared transaction log... Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 4 days, processed 165,040,351 queries (451/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL standalone and Java
On Thu, Sep 11, 2003 at 10:08:18AM -0400, Luc Foisy wrote: Are there packages for java to include a standalone mysql database? No. Or are there plans for such? Last time I saw Mark discuss it, no. There was significant overhead in coming up with a Java-to-embeeded-MySQL bridge. Check the archives. But maybe Mark will chime in to tell us the situation has changed... Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 4 days, processed 165,149,625 queries (451/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb deadlock issue
I noticed this deadlock in show innodb status on a mysql 4.0.14 box today. Both transactions seem to be trying to execute the a query on the same record, which I don't think should cause a deadlock. What am I missing? 030911 10:12:45 *** (1) TRANSACTION: TRANSACTION 0 153127600, ACTIVE 53 sec, process no 18814, OS thread id 484323467 starting index read, thread declared inside InnoDB 500 LOCK WAIT 7 lock struct(s), heap size 1024, undo log entries 4 MySQL thread id 1166811, query id 35192298 plaxo.com Updating update plx_user set sendlist_edit_counter = 53, modified = now() where user_id = 190864 and is_deleted = 0 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 6829218 n bits 72 table user/plx_user index PRIMARY trx id 0 153127600 lock_mode X waiting Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73757072656d756d00; asc supremum.;; *** (2) TRANSACTION: TRANSACTION 0 153131209, ACTIVE 8 sec, process no 17896, OS thread id 480604175 starting index read, thread declared inside InnoDB 0 3 lock struct(s), heap size 320 MySQL thread id 1165903, query id 35188878 plaxo.com Updating update plx_user set edit_counter = edit_counter + 1, modified = now() where user_id = 190864 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 6829218 n bits 72 table user/plx_user index PRIMARY trx id 0 153131209 lock_mode X Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73757072656d756d00; asc supremum.;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 6829214 n bits 96 table user/plx_user index PRIMARY trx id 0 153131209 lock_mode X locks rec but not gap waiting Record lock, heap no 2 RECORD: info bits 0 0: len 4; hex 8002e990; asc ;; 1: len 6; hex 09208ab0; asc ... ..;; *** WE ROLL BACK TRANSACTION (2) -- Joe Shear [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb feature request
I'd like to be able to look at show innodb status, notice that a query is waiting on a lock to be released, and then determine which tx is holding that lock. I know you can use the innodb_lock_monitor table, but this would make things a lot faster especially when there are a lot of active transactions. -- Joe Shear [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about InnoDB and external locking
On Thu, Sep 11, 2003 at 03:41:21PM -0700, [EMAIL PROTECTED] wrote: Ok, here's another question. Given effective external locking by the OS, could MyISAM tables achieve this? Yes. If so, do you know any operating systems that would have reliable external locking? I'm not sure what the state of file locking is in various OSes. I believe it's generally not a problem unless you also throw NFS into the mix... Out of curiosity, why do you need to do this? I've found that it's a rare need. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lots of FULLTEXT stuff (suggestions)
Hi Sergei!
Thanks for your reply and taking time to read and consider my
suggestions. :-) I didn't reply sooner because I was deciding what to
say in this message. ;-)
I joined the list specifically for posting these suggestions, and, with
your reply, I wanted to say that it's great to have direct contact with
the developers like this!
I'll try to keep my observations/ideas below as short and simple to
understand as possible. :-)
- Original Message -
From: Sergei Golubchik [EMAIL PROTECTED]
Sent: Wednesday, August 27, 2003 3:31 PM
Subject: Re: Lots of FULLTEXT stuff (suggestions)
Hi!
First: thanks for ideas - I'm adding them to my todo :)
About dates - it's very difficult to say when a particular feature
will
be implemented. Anyway, first I'm going to finish with this 2-level
index structure - to implement optimizations that rely on it.
Any speed/optimization improvements are welcome for gigs of data,
especially with IN BOOLEAN MODE (e.g. automagically sorted by
relevance
like a natural language query, although this is probably difficult
if a
wildcard* is used?).
It's not possible - at least I don't know to do it.
In natural language mode the fulltext search is done in in Fulltext
initialization stage - as you noticed. So an engine can sort
documents
on relevance. In boolean mode each found document is returned at
once -
that's why this search mode is faster, it need not support/keep the
list
of all matched documents.
Yeah, it would be really nice though if boolean searches could auto-sort
by relevance (see below).
I have 2 speed vs. usefulness issues -- 1 with boolean mode and another
with natural language mode:
Sure, boolean mode is faster in *some* cases, since, as you said, it
doesn't need the list of all matched documents. From my experience,
it's [only] faster in searches like ' some words ' (no boolean
operators; and yes, I know some is a stopword ;-)), especially with
LIMIT, but it just returns the first documents it finds with any single
word. I think this is pretty useless as you will get many rows that
would be low relevance. If you use boolean mode without boolean
operators, you should've just used natural language and, if combined
with LIMIT, you would fairly quickly get the most relevant documents
with any of the words -- and the ones that contain more of the words
will usually be ranked highest from my experience. The results are MUCH
better than boolean mode without boolean operators, even though it may
take slightly longer.
And now the issue with natural language mode: If the needed parts of the
datafile aren't cached by the OS, all the random disk seeks are KILLING
performance! However, once the query is run once and the OS has cached
the datafile parts, the search is VERY quick (with LIMIT to prevent a
ton of rows). I would say faster than any but the simplest ' no boolean
operator ' boolean searches. And the results are relevance sorted!
It would be great if these disk seeks could be optimized to read a chunk
of rows at a time *in row order* as it seems right now that each row is
read one-at-a-time in relevance order. Like if you could take a chunk
of, say, 1000 row pointers which are in relevance order, sort them in
datafile row order, and then read them like that. Wouldn't this cause
fewer random seeks since you keep moving in the same direction in the
file? Of course you'd need to get that chunk of rows back in relevance
order if they were read in row order. :-) If you can't hold those rows
in memory to put back in order, maybe you could read/discard,
read/discard, and so on, the rows in row order, then when you read them
in random relevance order, the data would at least be cached by the OS.
Just something I, who doesn't know much about file access, was thinking
about! :-) Boolean mode seems to read the datafile faster because, at
least in my fresh table, the results are found/read in datafile order.
Back to boolean mode. I don't think it's faster than natural language
(especially on subsequent same queries) when you have a search like '
+some +words ' or ' some words '. When some and words appear in
many documents, but rarely, if ever, appear in the same document. It
uses lots of CPU time finding one word in the index and failing on the
boolean criteria.
Right now if I want all words in my application, I'm favoring using a
natural language query with LIMIT 1000 or so and then running another
query with LIKE to check those 1000 document IDs to see if they contain
all words. And the documents that do will almost certainly be in the
first rows returned. e.g. once they're not for more than a few documents
in a row, they probably won't all appear in a document again in the
lower relevance results.
Heck, even if I want to simulate (' some +words ' IN BOOLEAN MODE) with
natural language, I can use (' some words words '). By specifying
words multiple times, it gives it higher relevance, so I can again
check with LIKE in another query that the
Re: Compling on RedHat 9
On Thu, Sep 11, 2003 at 10:22:13AM -0400, Peter Koutsoulias wrote: I couldn't find an appropriate mailing list for compiling MySQL, so I thought I'd try here. anyone successfully compile MySQL 4 on RedHat 9? I keep getting errors on libmysql.c during the make step. Google doesn't reveal a whole lot about compiling MySQL on RedHat which leaves me to believe most people use RPMs. A while ago, when I had access to a RH9 box I compiled it without much trouble--at least none that I still remebmer. You might post compilation the errors you're seeing... Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 4 days, processed 165,947,775 queries (450/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FreeBSD 4.8 runaway MySQL 4.0.14
On Thu, Aug 28, 2003 at 04:20:50PM -0400, Nick Gaugler wrote: Ken, Thanks for the response. I really wish MySQL AB would compile FreeBSD binaries with LinuxThreads. It's really a pain when you find a bug, the first thing they ask is have you compiled this yourself when in this case you have no choice but to compile it yourself. Good point. That does suck, doesn't it? It's always felt like FreeBSD became a second-class platform at MySQL AB when Tim left a few years back. At the time, he was the only MySQL developer using FreeBSD. But I don't know what the current state is there. Maybe Jeremy could compile unofficial but recommended binaries that MySQL AB would support more like official binaries? :) If he's got nothing better to do of course, he already puts in so much time to the MySQL community as is. I'll need to first confirm that our Yahoo FreeBSD is close enough to normal FreeBSD that the binaries would work. I suspect it wouldn't be a problem... But, yeah, it's not a bad idea come think of it. Or maybe we can strong arm the MySQL build folks (Hi, Lenz) into doing it? I'm not sure if they're thinking that FreeBSD users already use ports, so it's no big deal. But if so, that's not really compatible with their standard are you using our binaries response... Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 4 days, processed 166,016,658 queries (450/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb deadlock issue
Joe, what does SHOW CREATE TABLE give as the table definition? What do EXPLAIN SELECT ... where user_id = 190864 and is_deleted = 0; and EXPLAIN SELECT ... where user_id = 190864; say? If user_id is not the full primary key, then an index scan will happen and deadlocks of the type below are possible. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Joe Shear [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, September 12, 2003 1:53 AM Subject: innodb deadlock issue I noticed this deadlock in show innodb status on a mysql 4.0.14 box today. Both transactions seem to be trying to execute the a query on the same record, which I don't think should cause a deadlock. What am I missing? 030911 10:12:45 *** (1) TRANSACTION: TRANSACTION 0 153127600, ACTIVE 53 sec, process no 18814, OS thread id 484323467 starting index read, thread declared inside InnoDB 500 LOCK WAIT 7 lock struct(s), heap size 1024, undo log entries 4 MySQL thread id 1166811, query id 35192298 plaxo.com Updating update plx_user set sendlist_edit_counter = 53, modified = now() where user_id = 190864 and is_deleted = 0 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 6829218 n bits 72 table user/plx_user index PRIMARY trx id 0 153127600 lock_mode X waiting Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73757072656d756d00; asc supremum.;; *** (2) TRANSACTION: TRANSACTION 0 153131209, ACTIVE 8 sec, process no 17896, OS thread id 480604175 starting index read, thread declared inside InnoDB 0 3 lock struct(s), heap size 320 MySQL thread id 1165903, query id 35188878 plaxo.com Updating update plx_user set edit_counter = edit_counter + 1, modified = now() where user_id = 190864 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 6829218 n bits 72 table user/plx_user index PRIMARY trx id 0 153131209 lock_mode X Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73757072656d756d00; asc supremum.;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 6829214 n bits 96 table user/plx_user index PRIMARY trx id 0 153131209 lock_mode X locks rec but not gap waiting Record lock, heap no 2 RECORD: info bits 0 0: len 4; hex 8002e990; asc ;; 1: len 6; hex 09208ab0; asc ... ..;; *** WE ROLL BACK TRANSACTION (2) -- Joe Shear [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Access denied error
I have a fresh install of mysql 4.0.11 on a linux box. When I try to open mysql as a [EMAIL PROTECTED], I get the following error: mysql -u [EMAIL PROTECTED] -p db_name Enter password: ERROR 1045: Access denied for user: '[EMAIL PROTECTED]@localhost' (Using password: YES) However if just do this, everything is fine: mysql -u user -p db_name Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 to server version: 4.0.11a-gamma Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql exit ** Any thoughts on where the issue may be? TIA -- Jeff Roberts -- [EMAIL PROTECTED] What are you talking about? Never mind, eat the fruit. You know, this place almost looks like the Garden of Eden. Eat the fruit. Sounds quite like it too. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb deadlock issue
user_id is the primary key.. relevant parts of show create table: | plx_user |CREATE TABLE `plx_user` ( `user_id` int(11) NOT NULL auto_increment, `is_deleted` int(11) NOT NULL default '0', `edit_counter` int(11) NOT NULL default '0', `sendlist_edit_counter` int(11) NOT NULL default '0', PRIMARY KEY (`user_id`), ) TYPE=InnoDB | and from the explains: mysql EXPLAIN SELECT * from plx_user where user_id = 190864 and is_deleted = 0; +--+---+---+-+-+---+--+---+ | table| type | possible_keys | key | key_len | ref | rows | Extra | +--+---+---+-+-+---+--+---+ | plx_user | const | PRIMARY | PRIMARY | 4 | const |1 | | +--+---+---+-+-+---+--+---+ EXPLAIN SELECT * from plx_user where user_id = 190864; +--+---+---+-+-+---+--+---+ | table| type | possible_keys | key | key_len | ref | rows | Extra | +--+---+---+-+-+---+--+---+ | plx_user | const | PRIMARY | PRIMARY | 4 | const |1 | | +--+---+---+-+-+---+--+---+ 1 row in set (0.05 sec) On Thu, 2003-09-11 at 16:36, Heikki Tuuri wrote: Joe, what does SHOW CREATE TABLE give as the table definition? What do EXPLAIN SELECT ... where user_id = 190864 and is_deleted = 0; and EXPLAIN SELECT ... where user_id = 190864; say? If user_id is not the full primary key, then an index scan will happen and deadlocks of the type below are possible. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Joe Shear [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, September 12, 2003 1:53 AM Subject: innodb deadlock issue I noticed this deadlock in show innodb status on a mysql 4.0.14 box today. Both transactions seem to be trying to execute the a query on the same record, which I don't think should cause a deadlock. What am I missing? 030911 10:12:45 *** (1) TRANSACTION: TRANSACTION 0 153127600, ACTIVE 53 sec, process no 18814, OS thread id 484323467 starting index read, thread declared inside InnoDB 500 LOCK WAIT 7 lock struct(s), heap size 1024, undo log entries 4 MySQL thread id 1166811, query id 35192298 plaxo.com Updating update plx_user set sendlist_edit_counter = 53, modified = now() where user_id = 190864 and is_deleted = 0 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 6829218 n bits 72 table user/plx_user index PRIMARY trx id 0 153127600 lock_mode X waiting Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73757072656d756d00; asc supremum.;; *** (2) TRANSACTION: TRANSACTION 0 153131209, ACTIVE 8 sec, process no 17896, OS thread id 480604175 starting index read, thread declared inside InnoDB 0 3 lock struct(s), heap size 320 MySQL thread id 1165903, query id 35188878 plaxo.com Updating update plx_user set edit_counter = edit_counter + 1, modified = now() where user_id = 190864 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 6829218 n bits 72 table user/plx_user index PRIMARY trx id 0 153131209 lock_mode X Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73757072656d756d00; asc supremum.;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 6829214 n bits 96 table user/plx_user index PRIMARY trx id 0 153131209 lock_mode X locks rec but not gap waiting Record lock, heap no 2 RECORD: info bits 0 0: len 4; hex 8002e990; asc ;; 1: len 6; hex 09208ab0; asc ... ..;; *** WE ROLL BACK TRANSACTION (2) -- Joe Shear [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Joe Shear [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Compling on RedHat 9
i'm about to do this tonight as i'm setting up a desktop system to do my local development on, all i can say is redhat and gnome is slow as hell !, i only have 160 meg ram on the machine and after loading a few apps it only has 4 meg ram left ! plus the apt package manager for redhat is slow as hell much quucker to rpm in the console although i usually do mysql from source anyway, but this tool is meant to get dependancies it gets the packages fine but installing could take up to 40 mins. seriously it hasnt won me over from windoze as yet, unless someone would care to tell me what x windoze i should use and what settings i should to to optimise the system for a celeron 500 :\. Anyway i'll let you know how i go tonight. Btw its best if you can paste the configure/make messages from the console On Thu, Sep 11, 2003 at 10:22:13AM -0400, Peter Koutsoulias wrote: I couldn't find an appropriate mailing list for compiling MySQL, so I thought I'd try here. anyone successfully compile MySQL 4 on RedHat 9? I keep getting errors on libmysql.c during the make step. Google doesn't reveal a whole lot about compiling MySQL on RedHat which leaves me to believe most people use RPMs. A while ago, when I had access to a RH9 box I compiled it without much trouble--at least none that I still remebmer. You might post compilation the errors you're seeing... Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 4 days, processed 165,947,775 queries (450/sec. avg) -- 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: MySQL standalone and Java
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jeremy Zawodny wrote: On Thu, Sep 11, 2003 at 10:08:18AM -0400, Luc Foisy wrote: Are there packages for java to include a standalone mysql database? No. Or are there plans for such? Last time I saw Mark discuss it, no. There was significant overhead in coming up with a Java-to-embeeded-MySQL bridge. Check the archives. But maybe Mark will chime in to tell us the situation has changed... Jeremy Situation still hasn't changed (JNI access to libmysqld would still be slow), but you can 'embed' the standard MySQL server pretty easily from Java...Many people have done it...MySQL doesn't really require much of an installer, it can be as simple as just shipping the mysqld binary and your data along with your app and starting it with Runtime.exec() There's some a classe in MySQL Connector/J 3.1 which help you do just that, 'com.mysql.jdbc.util.ServerController'. Most of it would be applicable to Connector/J 3.0, it just was created after that branch went stable. Regards, -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 557 2388 www.mysql.com Are you MySQL Certified? http://www.mysql.com/certification/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQE/YQ80tvXNTca6JD8RAqa3AJ0Rf83nhZTtC5X/biXL404sx24kogCeLntF OhYXzeOQMwfWg6Znea+kibs= =boCK -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb deadlock issue
Joe, I think I see why this can happen. Even though the search condition is unique, the search may end up on the B-tree leaf node which immediately precedes the right node. That is why Transaction 1 is waiting for a lock on a 'supremum' record. It is the supremum of the previous leaf. Hmm... I may improve the B-tree search algorithm to avoid this. Or relax lock wait conditions on 'supremums'. Thank you for the bug report, Heikki - Original Message - From: Joe Shear [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, September 12, 2003 2:43 AM Subject: Re: innodb deadlock issue user_id is the primary key.. relevant parts of show create table: | plx_user |CREATE TABLE `plx_user` ( `user_id` int(11) NOT NULL auto_increment, `is_deleted` int(11) NOT NULL default '0', `edit_counter` int(11) NOT NULL default '0', `sendlist_edit_counter` int(11) NOT NULL default '0', PRIMARY KEY (`user_id`), ) TYPE=InnoDB | and from the explains: mysql EXPLAIN SELECT * from plx_user where user_id = 190864 and is_deleted = 0; +--+---+---+-+-+---+--+- --+ | table| type | possible_keys | key | key_len | ref | rows | Extra | +--+---+---+-+-+---+--+- --+ | plx_user | const | PRIMARY | PRIMARY | 4 | const |1 | | +--+---+---+-+-+---+--+- --+ EXPLAIN SELECT * from plx_user where user_id = 190864; +--+---+---+-+-+---+--+- --+ | table| type | possible_keys | key | key_len | ref | rows | Extra | +--+---+---+-+-+---+--+- --+ | plx_user | const | PRIMARY | PRIMARY | 4 | const |1 | | +--+---+---+-+-+---+--+- --+ 1 row in set (0.05 sec) On Thu, 2003-09-11 at 16:36, Heikki Tuuri wrote: Joe, what does SHOW CREATE TABLE give as the table definition? What do EXPLAIN SELECT ... where user_id = 190864 and is_deleted = 0; and EXPLAIN SELECT ... where user_id = 190864; say? If user_id is not the full primary key, then an index scan will happen and deadlocks of the type below are possible. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Joe Shear [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, September 12, 2003 1:53 AM Subject: innodb deadlock issue I noticed this deadlock in show innodb status on a mysql 4.0.14 box today. Both transactions seem to be trying to execute the a query on the same record, which I don't think should cause a deadlock. What am I missing? 030911 10:12:45 *** (1) TRANSACTION: TRANSACTION 0 153127600, ACTIVE 53 sec, process no 18814, OS thread id 484323467 starting index read, thread declared inside InnoDB 500 LOCK WAIT 7 lock struct(s), heap size 1024, undo log entries 4 MySQL thread id 1166811, query id 35192298 plaxo.com Updating update plx_user set sendlist_edit_counter = 53, modified = now() where user_id = 190864 and is_deleted = 0 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 6829218 n bits 72 table user/plx_user index PRIMARY trx id 0 153127600 lock_mode X waiting Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73757072656d756d00; asc supremum.;; *** (2) TRANSACTION: TRANSACTION 0 153131209, ACTIVE 8 sec, process no 17896, OS thread id 480604175 starting index read, thread declared inside InnoDB 0 3 lock struct(s), heap size 320 MySQL thread id 1165903, query id 35188878 plaxo.com Updating update plx_user set edit_counter = edit_counter + 1, modified = now() where user_id = 190864 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 6829218 n bits 72 table user/plx_user index PRIMARY trx id 0 153131209 lock_mode X Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73757072656d756d00; asc supremum.;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 6829214 n bits 96 table user/plx_user index PRIMARY trx id 0 153131209 lock_mode X locks rec but not gap waiting Record lock, heap no 2 RECORD: info bits 0 0: len 4; hex 8002e990; asc ;; 1: len 6; hex 09208ab0; asc ... ..;; *** WE ROLL BACK TRANSACTION (2) -- Joe Shear [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Joe Shear [EMAIL PROTECTED] -- MySQL
RE: Question about InnoDB and external locking
At this point it is pure academic curiousity. I am putting together a cheap cluster to play with shared drive failover, and thought I would see what happens when I point two MySQL machines at the same drive while I'm at it. Mike -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Behalf Of Jeremy Zawodny Sent: Thursday, September 11, 2003 5:17 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; Jeremy Zawodny Subject: Re: Question about InnoDB and external locking On Thu, Sep 11, 2003 at 03:41:21PM -0700, [EMAIL PROTECTED] wrote: Ok, here's another question. Given effective external locking by the OS, could MyISAM tables achieve this? Yes. If so, do you know any operating systems that would have reliable external locking? I'm not sure what the state of file locking is in various OSes. I believe it's generally not a problem unless you also throw NFS into the mix... Out of curiosity, why do you need to do this? I've found that it's a rare need. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Foreign key update and Error :: 1217 with v4.0.15
Heikki, I took your advice and installed the RPM updates to no avail: [root]# rpm -qa | grep mysql -i MySQL-server-4.0.15-0 mod_auth_mysql-1.11-1 MySQL-devel-4.0.15-0 MySQL-shared-4.0.15-0 php-mysql-4.1.2-7.2.6 MySQL-client-4.0.15-0 MySQL-shared-compat-4.0.15-0 And it seems to be running... mysql \s -- mysql Ver 12.21 Distrib 4.0.15, for pc-linux (i686) Connection id: 1 Current database: mysql Current user: [EMAIL PROTECTED] SSL:Not in use Current pager: stdout Using outfile: '' Server version: 4.0.15-standard Protocol version: 10 Connection: Localhost via UNIX socket Client characterset:latin1 Server characterset:latin1 UNIX socket:/tmp/mysql.sock Uptime: 1 min 53 sec Also, I noticed that my phpinfo() shows Client API version = 3.23.56 -- is that normal? But oddly enough, my phpMyAdmin page shows MySQL 4.0.15-standard running on localhost as [EMAIL PROTECTED] Anyways, back to the real problem... Updating via my web page produced the error 1217, so thinking that the PHP/mySQL version mentioned above might be a factor, I just logged into the mysql CLI program (Ver 12.21 Distrib 4.0.15, for pc-linux (i686)) and exectued the query directly there. However I still get the same error... mysql UPDATE company_table SET company_code = 'bunk1234' WHERE company_id = '91' LIMIT 1; ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails I looked at some InnoDB status log that phpMyAdmin has, and it shows some debug info that doesn't make sense to me. It complains that there is a record, but of course there is a record. That's the record I want to update?! LATEST FOREIGN KEY ERROR 030911 17:41:07 Transaction: TRANSACTION 0 913922, ACTIVE 0 sec, process no 24665, OS thread id 36874 updating or deleting, thread declared inside InnoDB 499 mysql tables in use 1, locked 1 4 lock struct(s), heap size 320, undo log entries 1 MySQL thread id 1, query id 3118 localhost root Updating UPDATE company_table SET company_code = 'bunk1234', Foreign key constraint fails for table crimson/rep_table: , CONSTRAINT `0_359` FOREIGN KEY (`rep_company_code`) REFERENCES `company_table` (`company_code`) Trying to delete or update in parent table, in index company_code tuple: 0: len 8; hex 62756e6b34333231; asc bunk4321;; 1: len 3; hex 5b; asc ..[;; But in child table crimson/rep_table, in index rep_company_code, there is a record: RECORD: info bits 0 0: len 8; hex 62756e6b34333231; asc bunk4321;; 1: len 2; hex 0001; asc ..;; -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Thursday, September 11, 2003 12:45 PM To: [EMAIL PROTECTED] Subject: Re: Foreign key update and Error :: 1217 Daevid, there is really one update you need to do first: upgrade to MySQL-4.0.14. http://www.innodb.com/ibman.html#InnoDB_foreign_keys Starting from version 3.23.50, you can also associate the ON DELETE CASCADE or ON DELETE SET NULL clause with the foreign key constraint. Corresponding ON UPDATE options are available starting from 4.0.8. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL - Original Message - From: Daevid Vincent [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, September 11, 2003 9:10 PM Subject: RE: Foreign key update and Error :: 1217 I really appreciate your help with this. Hmmm... So, while SET FOREIGN_KEY_CHECKS allowed me to ALTER the table, = now I can't UPDATE the company that has a rep with the same company_code because: Error :: 1217 :: Cannot delete a parent row: a foreign key constraint = fails UPDATE company_table SET company_name =3D 'InteractNetworks, Inc', company_code =3D 'TEST1234', ... However, I can update a company that doesn't have a company_code set = (and so no reps either). I can also manually change the rep_company_code. But once again, if I try to change the company that has a code that a = rep also has the same code, I get that error 1217 above. You should fix your data first: find rows in the rep_table=20 that don't have parent row in the the company_table, then add=20 parent row(s) to the company_table for those rows or delete=20 child row(s) from rep_table. After that you can create=20 FOREIGN KEY with ON UPDATE CASCADE. I don't follow you here. I don't think I have rows in the child (rep) = table that don't have a match in the parent (company) table. mysql select rep_id, rep_fname, rep_company_code from rep_table;= =20 ++-+--+ | rep_id | rep_fname | rep_company_code | ++-+--+ | 1 | Daevid
Re: mysql dump speed
On Thu, Sep 11, 2003 at 04:27:10PM -0400, Brent Baisley wrote: Have tried using the --opt parameter on mysqldump? This is supposed optimize the dump speed and create a file optimized for reloading. I haven't played with this option, so I don't know what kind of difference it makes. It makes a very big difference. --opt tells mysqldump to use MySQL's extended insert syntax, so you're insering the data for many rows with a single query. That can often give you a speed boost of 10x or more. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 4 days, processed 168,707,245 queries (449/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Compling on RedHat 9
At 10:22 AM -0400 9/11/03, Peter Koutsoulias wrote: I couldn't find an appropriate mailing list for compiling MySQL, so I thought I'd try here. anyone successfully compile MySQL 4 on RedHat 9? I keep getting errors on libmysql.c during the make step. Google doesn't reveal a whole lot about compiling MySQL on RedHat which leaves me to believe most people use RPMs. It would help if you showed your configure command and also the resulting errors. I keep getting errors isn't really very specific, and you're less likely to get any useful response that way than if you provide details. -- 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]
CocoaMySQL
Good news for OS X users. CocoaMySQL has just been updated and it now supports CSV imports among other improvements. It's freeware too. http://www.MacUpdate.com/info.php/id/10573 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CocoaMySQL
is there such a gui tool for linux ? i like sqlyog alot so anything similar would be excellent. Good news for OS X users. CocoaMySQL has just been updated and it now supports CSV imports among other improvements. It's freeware too. http://www.MacUpdate.com/info.php/id/10573 -- 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: Compling on RedHat 9
OK, here are the last few lines from make. I configured using the following: ./configure --prefix=/usr/local/mysql --with-mysqld-user=mysql errors: libmysql.c: In function `mysql_real_connect': libmysql.c:1790: warning: passing arg 5 of `gethostbyname_r' from incompatible pointer type libmysql.c:1790: too few arguments to function `gethostbyname_r' libmysql.c:1790: warning: assignment makes pointer from integer without a cast make[2]: *** [libmysql.lo] Error 1 make[1]: *** [all-recursive] Error 1 make: *** [all] Error 2 I tried ./configure without the --with-mysqld-user flag. still the same errors. Coincidently, I spent the entire day trying to figure out why I couldn't compile qmail on my RedHat 9 box and found the answer. Apparently, do to changes in the new glibc, some older practices become incompatible with glibc and cause a compile error. With qmail, it was a simple fix, just find the 3 affected files and add the #include errno.h header for each file. Link if you're interested in also compiling qmail on RedHat 9 (I love qmail, sendmail sucks): http://article.gmane.org/gmane.mail.qmail.general/13960 I wonder if the new glibc is also at play here. I'm not educated on the art of compiling, I just know how to untar packages, configure, and make them. And, if I run into a problem, google's my only friend. In this case, I couldn't find much. I've got the binaries working ok for now but sometimes it's just the principle...I can't stand being beaten :) At 10:22 AM -0400 9/11/03, Peter Koutsoulias wrote: I couldn't find an appropriate mailing list for compiling MySQL, so I thought I'd try here. anyone successfully compile MySQL 4 on RedHat 9? I keep getting errors on libmysql.c during the make step. Google doesn't reveal a whole lot about compiling MySQL on RedHat which leaves me to believe most people use RPMs. It would help if you showed your configure command and also the resulting errors. I keep getting errors isn't really very specific, and you're less likely to get any useful response that way than if you provide details. -- 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]
RE: Compling on RedHat 9
OK, here are the last few lines from make. I configured using the following: ./configure --prefix=/usr/local/mysql --with-mysqld-user=mysql errors: libmysql.c: In function `mysql_real_connect': libmysql.c:1790: warning: passing arg 5 of `gethostbyname_r' from incompatible pointer type libmysql.c:1790: too few arguments to function `gethostbyname_r' libmysql.c:1790: warning: assignment makes pointer from integer without a cast make[2]: *** [libmysql.lo] Error 1 make[1]: *** [all-recursive] Error 1 make: *** [all] Error 2 I tried ./configure without the --with-mysqld-user flag. still the same errors. If i get it i'll let you know, all i know is a fresh install of rh is missing alot of libraries therefore its breaks on compiling stuff. Coincidently, I spent the entire day trying to figure out why I couldn't compile qmail on my RedHat 9 box and found the answer. Apparently, do to changes in the new glibc, some older practices become incompatible with glibc and cause a compile error. With qmail, it was a simple fix, just find the 3 affected files and add the #include errno.h header for each file. Link if you're interested in also compiling qmail on RedHat 9 (I love qmail, sendmail sucks): http://article.gmane.org/gmane.mail.qmail.general/13960 qmail ? i hear postfix is better i'd prob suggest just getting the binaries, save you the pain, you arent doing extreme configure lines anyway. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
check table type
hi i was wondering if there is a way to check for a table type ? ie i have an authentication class, in some situations it uses innodb and sometimes its myisam and currently its not autocomitting -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
lock error
hi there i keep getting an error on a table Lock wait timeout exceeded; Try restarting transaction i intitally had made the update query but it wasnt committed, as it was using a myisam query function i have gone back to it with and innodb query function which starts a transaction , i have tried committing aswell, whats going on -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL Help please
SQL guru's, I could use some help writing a bit of SQL. There's 3 tables: orderable_parts partID varchar, topCatID int, # top level category ID ... part_attributes partID varchar, attName varchar, attValue varchar, ... topcatattributevalues tcavID int, topCatID int, attName varchar, attValue varchar, ... orderable_parts has parts available on our web site. Theres about 40,000 of them part_attributes are related to parts. Color, size etc. ~150,000 rows topcatattributevalues is a list of all the distinct part attributes available in each top level category. They're used for web searches and for product managers to edit orderable_parts, ~100,000 rows I'm trying to build a function to delete records from topcatattributevalues that are not used in any orderable_part. Either 1 delete statement or an update status=-1 and a delete where status=-1 would work. But it looks to me like I need a 3 way outer join and I can't seem to get it to work. I tried a variety of statements that look something like: update topcatattributevalues t left outer join orderable_parts o on (t.topCatID=o.topCatID), left outer join part_attributes p on (t.attName=p.attName and t.attValue=p.attValue and o.partID=p.partID) set t.status=-1 where o.partID is null I can't seem to get it. Any assistance would be greatly appreciated. Thanks. Dave. _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
weird transaction issues
hi there, i am trying to use transactions on some innodb tables although a few things i am experiencing, on one query the value appears, i refresh the page and the value dissapears again, i am using persistant connections in php, the value is actually there in the console, what could be the prob ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql 4.0.15: configure fails
I have Mac OS X 10.2.6 with December 2002 Dev Tools and the Dec 2002 gcc updater, which means I'm using gcc 3.3. Following the directions in INSTALL-SOURCE, I ran configure with: CC=gcc \ CFLAGS=-O3 -fno-omit-frame-pointer \ CXX=gcc \ CXXFLAGS=-O3 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti ./configure --prefix=/usr/local/mysql \ --localstatedir=/usr/local/mysql/data \ --with-extra-charsets=complex \ --enable-thread-safe-client \ --enable-local-infile \ --disable-shared Configure identified my system as powerpc-apple-darwin6.6, chugged along for awhile, then died with: checking for char... no checking size of char... 0 configure: error: No size for char type. A likely cause for this could be that there isn't any static libraries installed. You can verify this by checking if you have libm.a in /lib, /usr/lib or some other standard place. If this is the problem, install the static libraries and try again. If this isn't the problem, examine config.log for possible errors. If you want to report this, use 'scripts/mysqlbug' and include at least the last 20 rows from config.log! After some time spent learning that the warning about libm.a was a red herring, I looked in config.log and found the following: configure:14729: checking for char configure:14756: gcc -c -O -DDBUG_OFF -O3 -fno-omit-frame-pointer -traditional-cpp -DHAVE_DARWIN _THREADS -D_P1003_1B_VISIBLE -DSIGNAL_WITH_VIO_CLOSE -DSIGNALS_DONT_BREAK_READ -DHAVE_BROKEN_REAL PATH -DFN_NO_CASE_SENCE conftest.c 5 In file included from configure:14760: /usr/include/gcc/darwin/3.3/inttypes.h:39: error: syntax error at '#' token /usr/include/gcc/darwin/3.3/inttypes.h:39: error: parse error before if /usr/include/gcc/darwin/3.3/inttypes.h:40: error: syntax error at '#' token /usr/include/gcc/darwin/3.3/inttypes.h:41: error: syntax error at '#' token /usr/include/gcc/darwin/3.3/inttypes.h:42: error: syntax error at '#' token It seems the test didn't fail so much as conftest.c didn't even compile. Looking back through config.log, I saw the same thing happened in the tests for off_t and stat.st_rdev. Then I noticed that configure added -traditional-cpp to the CFLAGS for these tests. A simple test revealed that gcc 3.3 likes inttypes.h without -traditional-cpp, but thinks it's badly broken with -traditional-cpp. Looking through configure, I found (starting on line 12763) *darwin6*) if test $ac_cv_c_compiler_gnu = yes then FLAGS=-traditional-cpp -DHAVE_DARWIN_THREADS -D_P1003_1B_VISIBLE -DSIGNAL_WITH_VIO_CLOSE -DSIGNALS_DONT_BREAK_READ -DHAVE_BROKEN_REALPATH -DFN_NO_CASE_SENCE CFLAGS=$CFLAGS $FLAGS CXXFLAGS=$CXXFLAGS $FLAGS MAX_C_OPTIMIZE=-O fi ;; I deleted -traditional-cpp from the FLAGS in configure, then reran it as before. Configure completed successfully, after which I was able to build with make. Running `make test` resulted in all tests passed. I've installed it, and it seems to be working. Unless someone tells me otherwise, I'm going to conclude that this was a configure bug and that removing -traditional-cpp was the correct fix. In that case, configure ought to be fixed for everyone. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
