NEWBIE!! need help with nested select query
Hi- I have 2 queries that I know work in SQL Server, but I can't figure out why they won't work with mySQL. SELECT email, comments, (select count(*) from guests where decision=3) as total, (select count(*) from guests where decision=2) as secondtotal FROM guests AND SELECT email, comments, NULL, NULL FROM guests UNION SELECT NULL, NULL, (SELECT count(*) from guests where decision=3) as total, (SELECT count(*) from guests where decision=2) as secondtotal FROM guests The guests table structure: guest_id int, PK, autoincrement email varchar(100) comments varchar(250) decision int Any help would be greatly appreciated!! Thanks, Becky -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: NEWBIE!! need help with nested select query
Subqueries aren't supported in MySql prior to ver 4.1. - Original Message - From: Becky OGrady [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, November 15, 2003 12:28 PM Subject: NEWBIE!! need help with nested select query Hi- I have 2 queries that I know work in SQL Server, but I can't figure out why they won't work with mySQL. SELECT email, comments, (select count(*) from guests where decision=3) as total, (select count(*) from guests where decision=2) as secondtotal FROM guests AND SELECT email, comments, NULL, NULL FROM guests UNION SELECT NULL, NULL, (SELECT count(*) from guests where decision=3) as total, (SELECT count(*) from guests where decision=2) as secondtotal FROM guests The guests table structure: guest_id int, PK, autoincrement email varchar(100) comments varchar(250) decision int Any help would be greatly appreciated!! Thanks, Becky -- 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]
mysqld crashes with certain SSL connections
Description: mysqld (4.0.16) built with SSL support (--with-vio --with-openssl) crashes if both of the following conditions are met: 1. Permissions for a user have been set with GRANT...REQUIRE SUBJECT or GRANT...REQUIRE ISSUER. 2. A client program attempts to connect to the server by calling mysql_real_connect() with the CLIENT_SSL flag but didn't call mysql_ssl_set() beforehand. When these conditions are met, mysqld crashes and logs the following message to the .err file: mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=16777216 read_buffer_size=131072 max_used_connections=0 max_connections=100 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 80383 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. In the client program, mysql_real_connect() fails with Lost connection to MySQL server during query. The crash doesn't occur if permissions were granted with REQUIRE SSL, REQUIRE X509, or REQUIRE CIPHER -- it occurs only with REQUIRE SUBJECT or REQUIRE ISSUER. The crash also doesn't occur if mysql_real_connect() doesn't have the CLIENT_SSL flag set, or if mysql_ssl_set() was called with valid arguments before mysql_real_connect(). I can consistently reproduce this problem with MySQL 4.0.16 on FreeBSD 4.9 running on a Pentium III (built from the ports collection) and on Solaris 9 running on a SPARC Ultra 1 (built from source). The compiler on the FreeBSD box is gcc 2.95.4; the compiler on the Solaris box is gcc 3.3.2. OpenSSL on both boxes is 0.9.7c. I've been unable to get a core dump of these crashes. I've added core-file to the mysqld section of /etc/my.cnf on both machines and mysqld logs Writing a core file when the crash happens, but no core file appears to be written. Resource limits on both systems allow unlimited size for coredumps and I'm running mysqld_safe with the --core-file-size=100 option, but to no avail. If the developers are unable to reproduce the bug, then I welcome suggestions on how to get a core dump for further debugging. How-To-Repeat: 1. Grant permissions to a test user with a command such as the following: GRANT USAGE ON * TO testuser IDENTIFIED BY 'password' REQUIRE SUBJECT '/CN=Test User/[EMAIL PROTECTED]'; 2. Compile and run the following program: Start program #include stdio.h #include stdlib.h #include mysql/mysql.h int main(void) { const char *host = localhost; const char *user = testuser; const char *pass = password; const char *db = test; unsigned intport = 0; const char *sock = NULL; unsigned long flag = CLIENT_SSL; MYSQL mysql, *conn; mysql_init(mysql); conn = mysql_real_connect(mysql, host, user, pass, db, port, sock, flag); if (conn == NULL) { fprintf(stderr, mysql_real_connect: %s\n, mysql_error(mysql)); return EXIT_FAILURE; } printf(connection succeeded\n); mysql_close(mysql); return EXIT_SUCCESS; } End program Fix: Unknown. Submitter-Id: submitter ID Originator:Michael Fuhr Organization: Michael Fuhr http://www.fuhr.org/~mfuhr/ MySQL support: none Synopsis: mysqld crashes with certain SSL connections Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-4.0.16 (Source distribution) C compiler:2.95.3 C++ compiler: 2.95.3 Environment: System: SunOS eeyore.fuhr.org 5.9 Generic_112233-08 sun4u sparc SUNW,Ultra-1 Architecture: sun4 Some paths: /usr/bin/perl /usr/ccs/bin/make /usr/local/bin/gcc GCC: Reading specs from /usr/local/lib/gcc-lib/sparc-sun-solaris2.9/3.3.2/specs Configured with: ../configure --with-as=/usr/ccs/bin/as --with-ld=/usr/ccs/bin/ld --disable-nls --disable-libgcj --enable-languages=c,c++ : (reconfigured) ../configure --with-as=/usr/ccs/bin/as --with-ld=/usr/ccs/bin/ld --disable-nls --disable-libgcj --enable-languages=c,c++ Thread model: posix gcc version 3.3.2 Compilation info: CC='gcc' CFLAGS='-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' CXX='ccache gcc' CXXFLAGS='-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors
Re: Some help with a complex query
Roger, * Elisenda [...] The explain select says as follows: [...] I re-formatted the query and the EXPLAIN output for readability: Sorry for not re-formatted the query, I've learn it for next time. Well, I've learn a lot with your lessons. Select FASE.PR_Date_Visita_2, CE.CE_Centro, CE.CE_Domicilio, CE.CE_CP, CE.CE_Poblacion, CE.CE_Capital, CE.CE_PROV, CE.CE_CCAA, CA.CA_Horario, PP.PP_Contacto, PP.PP_Cargo, AU.AU_A_M, AU.AU_A_F From FASE,CE,CA,PP,AU where FASE.SQL_ID_PY='P081' AND FASE.PR_FLAG='1' AND CA.CA_ID_IDIOMA_A='6' AND AU.AU_NIVEL='13.14' AND FASE.SQL_ID_CE=CE.CE_ID_CE AND FASE.SQL_ID_CE=CA.CA_ID_CE AND CE.CE_ID_CE=CA.CA_ID_CE AND FASE.PR_PP_ID_COORD=PP.PP_ID_PP AND FASE.AU_PR_AULA=AU.AU_AULA table type key key_len ref rows FASE ref Participa 12 const,const 1157 CA ref Centro 7FASE.SQL_ID_CE 1 PP ref PP_ID 7FASE.PR_PP_ID_Coord 1 CE ref Centro 7CA.CA_ID_CE10 AU ref AU_AULA 256 FASE.AU_PR_Aula 264 (I removed the possible_keys and Extra columns) The first thing the EXPLAIN output tells us is in what order the server will access the tables. In this case the FASE table is read first, then the CA and PP tables are read based on the columns SQL_ID_CE and PR_PP_ID_Coord from FASE (the 'ref' column), then the CE table is read based on the value of CA.CA_ID_CE, and finally the AU table is read based on FASE.AU_PR_Aula. So, the good way to write joins will be as follows, doesn't it? FASE.SQL_ID_CE=CA.CA_ID_CE AND FASE.PR_PP_ID_COORD=PP.PP_ID_PP AND CA.CA_ID_CE=CE.CE_ID_CE AND FASE.AU_PR_AULA=AU.AU_AULA The 'rows' column hows approximately how many rows the server will have to read. It is a goal to make/keep these numbers low, I don't know if you did a EXPLAIN before you created you indexes, in that case you will see that the numbers in the 'rows' column was higher, possibly as high as the row count of the respective tables. A way to calculate how 'heavy' a select query is, is to multiply these numbers. In the case above, the multiplum is 1157*1*1*10*264 = 3054480. In other words, the server must examine approximately 3 million rows to produce your result. (Note that this is an estimate, based on statistics stored in the server. Running OPTIMIZE TABLE will update these statistics, and this may also change the servers preferred plan.) So, when explain select says this CE ref Centro 7CA.CA_ID_CE10 , it isn't a good result for me, isn't it? Because CA it is supposed to have one record for each CE or the other way round. I have to review CA and CE. The 'ref' column for FASE says 'const,const'. This means the index used (Participa) is a combined index used to match two constants, presumably the SQL_ID_PY and PR_FLAG. Is the number 1157 close to correct? Yes, it is correct. I have 1.157 record which match SQL_ID_PY=P081 and PR_flag=1 What I know is that I have 753 records which match FASE.PR_flag=1 and FASE.SQL_ID_PY=P081 and CA.CA_ID_Idioma_A=6 and my result is 253 records. I don't understand... How many FASE records with PR_flag=1 and SQL_ID_PY='P081'? You say when you join FASE and CA on those criteria you get 253 rows, but you should get 753? I mean that in my database I have 753 records that match this critera (SQL_ID_PY=P081 and PR_flag=1 and CA_ID_Idioma=6) but the result of mysql gives me only 253. In general, if you get 'too few' rows on a multi-table join like the one you are doing here, it could be because some of the tables you join to does not have a corresponding row for the criteria. If that is the case, and you still want those rows to show up in the result, you can use LEFT JOIN for those tables. URL: http://www.mysql.com/doc/en/JOIN.html Does it have to be with my query? Or does it have to be with data in mysql, I mean I didnĀ¹t insert them allright? I don't know. Check each table separately, use SELECT COUNT(*) FROM ... WHERE ... to check how many rows match any given criteria. Try to use the output of EXPLAIN SELECT to manually do what the server will be doing, and see if you get any unexpected results. Run OPTIMIZE TABLE to refresh the index statistics. I will do it. URL: http://www.mysql.com/doc/en/OPTIMIZE_TABLE.html For further speed improvements: Your AU_AULA and CA.Centro indexes could be replaced by combined indexes, (AU_AULA,AU_NIVEL) and (CA_ID_CE,CA_ID_IDIOMA_A) respectively. Your column CA.CA_ID_IDIOMA_A is defined as an integer, in your WHERE clause you should check for integer 6, not string '6'. This also applies to FASE.PR_FLAG. The AU.AU_NIVEL column was not mentioned in your previous table description, so I don't know if it's numeric or a string. If it is numeric, you should not use quotes on the constant. It will work, but the server must convert from string to integer, this
Database Dump
Hi All I have just started working on MYSQL. Can ne one help on How to take a database Backup? and how does one transfer the MYSQL database from one Comp to another one? Regards Swati kalia http://www.mecklai.com where risk meets its match *** This message is intended for the individual or entity named above. If you are not the intended recipient, please do not read, copy, use or disclose this communication to others; also please notify the sender by replying to this message, and then delete it from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database Dump
Hi From: Swati K [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, November 15, 2003 1:40 PM I have just started working on MYSQL. Can ne one help on How to take a database Backup? Backup; shell # mysqldump table table.sql for details; http://www.mysql.com/doc/en/Backup.html Best Regards, DT. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Depth-first-traversal order?
I have the following columns in a table: posts.id posts.parentId REFERENCES posts.id This represents a tree that has a single root node, and each node can have 0 or more children. posts.id is the id number of a node, and posts.parentId is the id number of that node's parent (is NULL for the root node). Is there a way to do SELECT ... FROM posts ORDER BY magic where magic is something that will give me depth-first-traversal order? The best solution I've been able to think of so far, short of reading the entire tree into memory and sorting it with a procedural language such as Perl, is to make a new column called posts.order and do ORDER BY posts.order, and have some code that maintains posts.order whenever a new node is inserted into the tree. I was hoping there's a way MySQL could automatically keep track of depth-first-traversal order for me, though... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Grant privilege removed from root
Dear all Getting confused in the mysqlcc as a [EMAIL PROTECTED], I managed that I cannot grant privileges any more. Any hint how I can undo that? Thanks, W. Bauer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index Question
John Berman [EMAIL PROTECTED] wrote: Hi. using MYSql 3.28 There is no such version of MySQL :) I have a surname column with a standard index and this is the column were search are performed on, currently the filed only has the one name i.e.: Surname: smith I want to include other column data in the search i.e. fathersname, so I create an index on that column and in my search I use a statement like: surname = ' globsurname ' or fathersname = ' globsurname ' this does work but tends to slow the thing down, Because MySQL doesn't optimize search on two different keys with OR: http://www.mysql.com/doc/en/Searching_on_two_keys.html So Can I take the name from fathersname and include it in the Surname field so I have say: Surname: smith jones It does not seem to work for me, maybe I need a particular separator ? I want to go this way as each record my have several names that I want to be able to search on. -- 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: Database Dump
At 14:13 +0200 15-11-2003, Davut Topcan wrote: I have just started working on MYSQL. Can ne one help on How to take a database Backup? Backup; shell # mysqldump table table.sql I use a command-line PHP script (-rwx--, owned by root, executed with a cron job) to backup MySQL databases. Something along these lines: == #!/usr/bin/php -q ?php $user = 'root'; $password = 'secret'; $connection = mysql_connect('localhost', $user, $password); $databases = mysql_list_dbs($connection); while ($database_row = mysql_fetch_row($databases)) { $database = $database_row[0]; if (! file_exists(/tmp/backup/sql/$database)) mkdir(/tmp/backup/sql/$database, 0755); mysql_select_db($database, $connection); $tables = mysql_query(SHOW TABLES, $verbinding); while ($table_row = mysql_fetch_row($tables)) { $table = $table_row[0]; system(mysqldump -u $user --password=$password --quick --add-drop-table . escapeshellarg($database) . . escapeshellarg($table).. escapeshellarg(/tmp/backup/sql/$database/$table.sql)); } } ? === JP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQLCC - Garbage Display
Hello: I'm using mysqlcc version 0.9.3 on RH 9.0. When I upgraded from RH 7.2, the application seems to have changed into a bizarre, unreadable font. Does anyone know what could be the problem? Obviously, this renders the application entirely unusable. I suspect a QT problem, will look for source and try to compile, if no other solution offered. Thanks tim -- Tim Johnson [EMAIL PROTECTED] http://www.alaska-internet-solutions.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Depth-first-traversal order?
I don't think you can do what you want in one query if you use the adjacency list tree model (i.e. a self join to the parent node). See the article below comparing the adjacency list and the modified preorder tree traversal: http://www.sitepoint.com/article/1105 I'm not sure if that is what you want or not, but its a good article and worth a shot. For my trees I use a combination of the adjacency list and the modified preorder tree traversal to get the best of both models. Another good reference that compares these two models is SQL for Smarties by Joe Celko. Hope this helps, Jeremy _ MSN Shopping upgraded for the holidays! Snappier product search... http://shopping.msn.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Client library
Hi all, I have already asked it a few days ago, but I got no answer. Does anyone know how to obtain the newest version of libmysql.dll and the header file(s) for it? Thanks, niel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: max_user_connections problem after upgrading
Hi Joe, No, I don't know exactly. Releases usually come out at about the same interval. So going by previous releases, I would say 4.0.17 should be released within 2-4 weeks -- probably shortly after 4.1.1. Matt - Original Message - From: Joe Lewis Sent: Friday, November 14, 2003 11:28 AM Subject: Re: max_user_connections problem after upgrading Matt W wrote: I guess you'll have to see if it's fixed in the next release (4.0.17). Any clue as to when 4.0.17 will be released? Joe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backslash and full text searches
Hi Jason, Sorry for the late reply. If all of the 100,000 entries have extra backslashes, that can be fixed with a query. But then you have all your code. :-( Speaking of code, what language are you using? I always assume PHP, but with 500k lines I'm thinking you're using C? Is somebody\'s the only word you're trying to match? I just tried it and I can't get somebody\'s to match even as a phrase in BOOLEAN mode because the somebody part is a stopword. However, Matt\'s works. If you can't match a non-stopword, are you sure the correct amount of backslashes are making it to MySQL, or are they being lost as escape characters in your programming language first? My results: mysql CREATE TABLE test (test TEXT NOT NULL, FULLTEXT (test)); Query OK, 0 rows affected (0.01 sec) mysql INSERT INTO test VALUES ('This is Matt\\\'s text.'), ('Text by Matt.'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql SELECT * FROM test; +---+ | test | +---+ | This is Matt\'s text. | | Text by Matt. | +---+ 2 rows in set (0.00 sec) mysql -- This just matches the Matt part mysql SELECT * FROM test WHERE MATCH (test) AGAINST('Matt\\\'s' IN BOOLEAN MODE); +---+ | test | +---+ | This is Matt\'s text. | | Text by Matt. | +---+ 2 rows in set (0.00 sec) mysql -- Exact phrase mysql SELECT * FROM test WHERE MATCH (test) AGAINST(' Matt\\\'s ' IN BOOLEAN MODE); +---+ | test | +---+ | This is Matt\'s text. | +---+ 1 row in set (0.00 sec) Matt - Original Message - From: Jason Ramsey Sent: Thursday, November 13, 2003 6:41 PM Subject: RE: Backslash and full text searches Thanks for your answer, you can see my comments below. -Original Message- From: Matt W Sent: Thursday, November 13, 2003 3:36 PM To: Jason Ramsey; [EMAIL PROTECTED] Subject: Re: Backslash and full text searches Hi Jason, Of course somebody's isn't going to match somebody\'s. :-) somebody\'s should match somebody\'s -- because it's really matching the somebody part. ^ That makes sense to me, and the behavior I would have expected; except, it doesn't seem to work that way. Anything I try I can't get somebody\'s to match a field in the database. If you're using 4+, you can use IN BOOLEAN MODE to match somebody\'s exactly: MATCH (col) AGAINST (' somebody\\\'s ' IN BOOLEAN MODE) ^ Unfortunatley, this doesn't work. I've tried several variations, but can't seem to match useing MATCH, LIKE or even =. However, I get the feeling that you're not really wanting to match a word that has a backslash in it. e.g. You're getting extra backslashes. mysql_escape_string()/addslashes() *DOES NOT* store the backslashes in the table -- it's not even *possible* since they're only escape characters in the query. If you're getting any extra backslashes when retrieving data, too many were added during insertion -- you ran mysql_escape_string/addslashes too many times. This happens if PHP's stupid magic_quotes_gpc is on and you don't check for that. ^hmm. Well, that certainly is different than what I understood, and helpful information. It looks like magic_quotes_gpc is on. However, now I have a problem that we have a 100,000 entries in the database like this, and 500,000 lines of code expecting the backslash. I really need to find a solution so that I can search and somehow account for the \ in my searches. Any help would be appreciated. Your text should come out exactly the way it was intended. Never, ever any need for stripslashes(), etc. if it was inserted correctly. :-) Hope that helps. Matt - Original Message - From: Jason Ramsey Sent: Thursday, November 13, 2003 4:10 PM Subject: Backslash and full text searches We make extensive use of full text searches, but have run into some problems with backslashes. If a word like somebody's is entered into our database, we escape the string using mysql_escapes_string in php. So, mysql_escape_string(somebody's) becomes somebody\'s when it is saved in the database. The problem is, we don't seem to be able to match against this in the database. Let's say we saved somebody's in the data base. The following will match fine and pull up the results expected... SELECT * FROM Table WHERE MATCH (Field) AGAINST (somebody's) ... But if somebody\'s is stored in the database, there seems to be no way to match the \. We've tried all of the following... SELECT * FROM Table WHERE MATCH (Field) AGAINST (somebody's) SELECT * FROM Table WHERE MATCH (Field) AGAINST ('somebody's') SELECT * FROM Table WHERE MATCH (Field) AGAINST ('somebody's') SELECT * FROM Table WHERE MATCH (Field) AGAINST (somebody\'s) SELECT * FROM Table WHERE MATCH (Field) AGAINST
SQL: forbidden syntax search
Hi, I need a little help on my SQL syntax. I want to store forbidden inputs from the GUI in a table and query it on input from the user; simple layout: CREATE TABLE `forbidden_input` (`lfdnr` TINYINT (3) UNSIGNED DEFAULT '0' AUTO_INCREMENT, `input` VARCHAR (255) NOT NULL, PRIMARY KEY(`lfdnr`), UNIQUE(`input`)); Content is something like this: 1,'+' 2,'^' 3,'' 4,'' 5,'(' 6,')' 7,'~' 8,'' 9,'%' The Query shall be something like (Where 'Hello World' will later be substituted with the user's input by PHP.): SELECT `input` FROM lok_forbidden_input WHERE 'Hello World' like '%\%'; I get a strange behavior here, which I can't explain myself: I get either no results or all results. More bad: I originally wanted to compare the table content agains the user's input. So the query should rather look like this: SELECT `input` FROM lok_forbidden_input WHERE 'Ha%llo Welt' like '%\'+`input`+'%'; But here I get a problem with the backslash with should mask out something like '%' in table's row number 9. Where am i wrong here? Thanks for any help Maik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]