errno24 - open_files_limit
Hi! We have that huge query against a mysql 4.1 (myisam) to retrieve rows. The query is built dynamically using a loop, as we are searching in an external database built to store a tree with the nested tree model. When we do this search with a normal world, it's fine: the query is around 4 lines. If we search with a very frequent word, we get a lot of hits, and the query becomes very long (probably around 300 lines), and we get the errno24 Looking at the doc: http://dev.mysql.com/doc/refman/4.1/en/not-enough-file-handles.html so apparently I don't have enough file handlers. Ok, don't mind increasing that.They say to modify the open_files_limit variable. #open_files_limit The number of files that the operating system allows mysqld to open. This is the real value allowed by the system and might be different from the value you gave using the --open-files-limit option to mysqld or mysqld_safe. The value is 0 on systems where MySQL can't change the number of open files. This variable was added in MySQL 3.23.20. 2 questions: - any idea of the value I should put there? mysqld is running on a machine I don't manage, what should I ask for? - is it possible to modify this value in the conf file and to reload that file without stopping/re starting the server? Additionnally, any pointer on a doc explaining why and when is mysql opening a new file? Thanks, melanie _ Are you using the latest version of MSN Messenger? Download MSN Messenger 7.5 today! http://join.msn.com/messenger/overview -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query help-multiple joins
Hi! I'm stuck with a join query 2 tables, term and relation, the first one with definition of terms the second one with the relations between them. CREATE TABLE `term` ( `term_id` int(11) unsigned NOT NULL auto_increment, `name` varchar(250) default NULL, ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ; and CREATE TABLE `relationTerm` ( `relation_id` int(11) unsigned NOT NULL auto_increment, `term_id1` int(11) unsigned NOT NULL default '0', `term_id2` int(11) unsigned NOT NULL default '0', `type_id` int(11) NOT NULL default '0', PRIMARY KEY (`relation_id`), UNIQUE KEY `uniqueRelation` (`term_id1`,`term_id2`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ; in the second table term_id1 and term_id2 are referencing term_id of the first table. Example of data: INSERT INTO `term` VALUES (1, 'A'); INSERT INTO `term` VALUES (2, 'B'); INSERT INTO `relationTerm` VALUES (1, 1, 2, 1); I want to obtain: A 1 B I managed to obtain A 1 2, but I don't know how to replace the 2 with the actual name of the entry, which would be B. My query is: SELECT term_id, name, type_id, term_id2 FROM term LEFT JOIN relationTerm ON term_id1 = term_id I should be able to somehow join again on term_id2 but just don't see how to do it... Thanks for any help, melanie _ Be the first to hear what's new at MSN - sign up to our free newsletters! http://www.msn.co.uk/newsletters -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query help-multiple joins
Perfect! I tried aliasing the field names but didn't think about the table, and was just stuck looking at that query without any idea... Thanks a lot for your help. melanie From: Johan Höök [EMAIL PROTECTED] To: mel list_php [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: query help-multiple joins Date: Tue, 23 May 2006 13:16:33 +0200 Hi, I guess you should be able to do something like: SELECT t1.term_id, t1.name, r.type_id, t2.term_id, t2.name FROM term t1 LEFT JOIN relationTerm r ON r.term_id1 = t1.term_id LEFT JOIN term t2 ON r.term_id2 = t2.term_id /Johan mel list_php skrev: Hi! I'm stuck with a join query 2 tables, term and relation, the first one with definition of terms the second one with the relations between them. CREATE TABLE `term` ( `term_id` int(11) unsigned NOT NULL auto_increment, `name` varchar(250) default NULL, ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ; and CREATE TABLE `relationTerm` ( `relation_id` int(11) unsigned NOT NULL auto_increment, `term_id1` int(11) unsigned NOT NULL default '0', `term_id2` int(11) unsigned NOT NULL default '0', `type_id` int(11) NOT NULL default '0', PRIMARY KEY (`relation_id`), UNIQUE KEY `uniqueRelation` (`term_id1`,`term_id2`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ; in the second table term_id1 and term_id2 are referencing term_id of the first table. Example of data: INSERT INTO `term` VALUES (1, 'A'); INSERT INTO `term` VALUES (2, 'B'); INSERT INTO `relationTerm` VALUES (1, 1, 2, 1); I want to obtain: A 1 B I managed to obtain A 1 2, but I don't know how to replace the 2 with the actual name of the entry, which would be B. My query is: SELECT term_id, name, type_id, term_id2 FROM term LEFT JOIN relationTerm ON term_id1 = term_id I should be able to somehow join again on term_id2 but just don't see how to do it... Thanks for any help, melanie _ Be the first to hear what's new at MSN - sign up to our free newsletters! http://www.msn.co.uk/newsletters -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ The new MSN Search Toolbar now includes Desktop search! http://join.msn.com/toolbar/overview -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable to duplicate a database at home, possible encoding problem
Hi Dave, To try and explain it better: if you used Synaptic, you use what is called a package manager, basically a program that will get everything you need and install it for you. (the .rpm) But in that case as you said you can't use 'configure'. The other option you have is to install from what is called the source, meaning you download the source code of a program and accomplish manually the commands to have it running. The three steps you usually follow when doing that are configure, make and then make install. The whole instructions to do that are there: http://dev.mysql.com/doc/refman/5.1/en/installing-source.html It's the one I used when I started to try and install manually programs, and they are very clear (even if it may look a bit hard at the beginning, really, don't worry, go slowly step by step and it will work). At some stage you will then be asked: shell ./configure --prefix=/usr/local/mysql this is where you would add an extra option: shell ./configure --prefix=/usr/local/mysql --with-charset=utf8 (you have several configure options, you may want to take a look at them before: http://dev.mysql.com/doc/refman/5.1/en/configure-options.html) don't forget to do the post-installation as described here: http://dev.mysql.com/doc/refman/5.1/en/unix-post-installation.html About mysqld --character-set-server=utf8, you have an error meaning that you probably didn't stop your mysql server. mysqld --character-set-server=utf8 is used to start the server, so if an other one is already running it will fail. try stopping your server:(from the mysql directory) shell bin/mysqladmin -u root shutdown and restart it with shell bin/mysqld_safe --character-set-server=utf8 I think it's really worth spending the time on the installation form the source,it's a bit long the first time, but you'll get used to it, and if you ever want to install an other program from the sources you'll know what to do! hth, melanie From: Dave M G [EMAIL PROTECTED] To: mel list_php [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: Unable to duplicate a database at home, possible encoding problem Date: Wed, 15 Feb 2006 11:44:41 +0900 The precise instructions are here: http://dev.mysql.com/doc/refman/5.0/en/charset-syntax.html ...but if it is your local home installation you could also recompile mysql specifying utf8 when you run configure. Thank you for the very helpful advice. I understand what it is you're saying I can accomplish, but I'm fuzzy on the specifics, because, if it weren't already apparent, I'm a bit of a newbie at this stuff. I get that I can use commands with the --with-charset=utf8 perameter to rebuild (?) my server with utf8 as the default encoding. But you see, I installed MySQL from within a program called Synaptic within Ubuntu. On the upside, it was very easy to get my MySQL installation up and running. On the downside, I didn't see or do any configuration settings our source code building, which means I'm clueless as to where this stuff happens. Being the exploratory newbie that I am, I tried the following: [EMAIL PROTECTED]:~$ mysqld --character-set-server=utf8 060215 11:40:58 [Warning] Can't create test file /var/lib/mysql/ubuntu1.lower-test 060215 11:40:58 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use 060215 11:40:58 [ERROR] Do you already have another mysqld server running on port: 3306 ? 060215 11:40:58 [ERROR] Aborting 060215 11:40:58 [Note] mysqld: Shutdown complete [EMAIL PROTECTED]:~$ ./configure --with-charset=utf8 bash: ./configure: No such file or directory I kind of knew it wasn't going to work when I tried it, but I thought I'd dive in anyway. Is it possible I can get a slighly more newbie-friendly set of instructions on how I can either reconfigure my MySQL server, or at least permanently alter the system variables? -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Are you using the latest version of MSN Messenger? Download MSN Messenger 7.5 today! http://messenger.msn.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable to duplicate a database at home, possible encoding problem
Hi, The precise instructions are here: http://dev.mysql.com/doc/refman/5.0/en/charset-syntax.html You can start your server specifying the character set (http://dev.mysql.com/doc/refman/5.0/en/charset-server.html), and alter the databases (http://dev.mysql.com/doc/refman/5.0/en/charset-database.html), but if it is your local home installation you could also recompile mysql specifying utf8 when you run configure. That's the option I chose for my personal home installation and I updated the server version at the same time :-) hth, melanie From: Dave M G [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Re: Unable to duplicate a database at home, possible encoding problem Date: Wed, 15 Feb 2006 02:54:00 +0900 detailed info here: http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html Thank you for that link. I think this is the heart of the issue. Following the advice on the web page, I ran these commands: SET NAMES utf8; SET CHARACTER_SET utf8; But I can check my MySQL system variables in phpMyAdmin, and here's what it says: VariableSession valueGlobal value character set clientutf8 latin1 character set connection utf8 latin1 character set database latin1 latin1 character set results utf8 latin1 character set server latin1 latin1 character set system utf8 utf8 collation connection utf8_general_ci latin1_swedish_ci collation database latin1_swedish_ci latin1_swedish_ci collation server latin1_swedish_ci latin1_swedish_ci What I want to do is change *all* of these to utf8. That's the only encoding I ever work in, and I want to make my system as consistent as possible. What commands do I run to permanently fix all of these collation and character set variables to utf8? -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Are you using the latest version of MSN Messenger? Download MSN Messenger 7.5 today! http://messenger.msn.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable to duplicate a database at home, possible encoding problem
Hi, I'm not very good at encoding either, but from what I understood, the client specifies the encoding expected from the database,if you do a show variables like '%char%', you will have a line character_set_client. In your case this is probably set to latin_1. If this is the case, mysql is sending the result declared as latin1 encoded. The 'numbers' (hexadecimals value of your characters) are ok, but the utf8 encoded ones can't be represented properly thus the question marks. If this is the case you could try adding SET NAMES 'UTF8' before your queries. detailed info here: http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html I think the second user comment may also be helpful. hth, melanie From: Dave M G [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Re: Unable to duplicate a database at home, possible encoding problem Date: Tue, 14 Feb 2006 00:06:42 +0900 MySQL List, With the off list help of a member of this community, I have solved the issue of accessing my database data from within PHP. It was an issue related to PHP's register_globals setting. However, the encoding issue remains. I've taken the text that pertains only to that, and reproduced it here in hopes that someone can give me some advice which will enable me to transport my utf8 encoded data from my hosting service to my home machine. I can take the .sql file that I have exported from my hosting service, open it in OpenOffice Write as a text encoded file, and verify that it is encoded in utf-8. Most of the Japanese text shows up readable. Some of it, however, shows up as coded numbers (I'm not sure what the term is when utf displays this way): #12513;#12540;#12531;#12539; When I import the .sql file into MySQL, I can look at it in phpMyAdmin and see that the text that displayed correctly as Japanese in OpenOffice still displays correctly as Japanese. The text that was in number form is also still in number form when viewed through phpMyAdmin. In short, phpMyAdmin sees it after import the same way that OpenOffice did before import. But, then when I view a PHP file in FireFox, and it accesses the database that way, the situation changes. The text that is encoded as numbers displays as correct Japanese. The text that displays as actual Japanese text in OpenOffice and phpMyAdmin now displays as quesiton marks. Again, just to be clear, all Japanese characters and all database data display correctly when viewed from the hosting service. I hope someone can shed some light on this. Thank you. -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ The new MSN Search Toolbar now includes Desktop search! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
latin1 -utf8 conversion
Hi list, I guess this is a classic problem...! I found that on the web: http://www.oreillynet.com/pub/wlg/9022?wlg=yes, where basically the guy did dump data, change the charset in the table definition and reinsert the records into an utf8 database and ended up with some problems... I saw somewhere in the mysql doc (unable to find the link back though) that converting between charsets can be tricky, especially if you're not sure of what you actually have. Which is my problem: the tables are latin1, but some people may have executed queries from the command line (utf8) and inserted data (which are then utf8 right?), some may have used an utf8 phpmyadmin (producing utf8 data?) and some a old isolatin one.(which would then give latin1 data?) I think the majority of the data are latin1, but there may be some utf8 at some places. I have mostly basic characters, and a few names with accents. I saw somewhere that you can convert to binary before to be sure of keeping things right. From my understanding, the database itself never do any conversion, meaning if you insert utf8 data into tables declared as latin1 it doesn't really matter if you retrieve the data as utf8 on the client side(true?) I strongly suspect that I'm kind of intolerant to encodings and how to manage them, I just don't get it. Does anybody knows what is the best way to do? Would a dump be enough? Does the dump itself need to be utf8 encoded to be loaded properly? Do I need to load it through an utf8 interface? I have an old iso latin shell, an utf8 one, and 2 versions of phpmyadmin, one latin1 and one utf8: does it matter where I will load the dump from? Any help more than welcome! Thanks, melanie _ Be the first to hear what's new at MSN - sign up to our free newsletters! http://www.msn.co.uk/newsletters -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exporting utf-8 data problems
Hi, Maybe your problem is linked to phpmyadmin? I was having issues when trying to use an 'old' version (2.5.7) because the interface itself of phpmyadmin was set up as iso-8859-1, and that was causing all my data to be entered as isolatin even if the tables were declared as utf8. By updating to 2.6.4 I've been able to set up the language in phpmyadmin as en-utf-8, and now when I use this interface the data are actually utf8 in the tables. hth, melanie From: Dave M G [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Re: Exporting utf-8 data problems Date: Thu, 05 Jan 2006 00:54:44 +0900 Thank you for the advice. In order to isolate the problem, I have checked to see if the text file that I have exported from the MySQL server on my virtual host is, in fact, in UTF-8. By opening it in OpenOffice and selecting UTF-8 encoding, it displays correctly. Not entirely without problems. Most of the Japanese text shows up correctly. But about 10% of it shows like this: #12503;#12523;#12539;#12522;#12540;#12501;#12391; I believe this is because there is probably some Japanese text that was entered in sometime before the server upgraded MySQL to 4.1. But, ignoring the 10% of legacy text, it seems that at the very least, I do have a proper UTF-8 encoded text file with which to import into my home MySQL server. Instead of importing the data as an SQL file (which successfully imported, but with faulty Japanese characters), I copied the text and pasted them in as a straight SQL query. But it returns an error. Can anyone enlighten me as to why the file would import into SQL as an SQL file, but the text won't work as an import statement? Here is the error output: SQL query: # phpMyAdmin MySQL-Dump # version 2.3.3pl1 # http://www.phpmyadmin.net/ (download page) # # Host: localhost # Generation Time: Jan 04, 2006 at 10:04 AM # Server version: 3.23.37 # PHP Version: 4.3.11 # Database : `signup` # # # Table structure for table `event_groups` # CREATE TABLE event_groups( egid int( 11 ) NOT NULL AUTO_INCREMENT , GROUP int( 11 ) NOT NULL default '0', event int( 11 ) NOT NULL default '0', PRIMARY KEY ( egid ) ) TYPE = MYISAM MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group int(11) NOT NULL default '0', event int(11) NOT NULL de (the error message cuts abruptly, as shown here) Any advice would be much appreciated. -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ The new MSN Search Toolbar now includes Desktop search! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
global search
Hi, I was wondering if there is any way to do a global search through all the tables in mysql? (not taking into account the performance issue) If for example I want to search ABC in all the columns of one table: select * from table where [whatever the column] like '%ABC%' or globally select * from [whatever the table] where [whatever the column] like '%ABC%' I think that if something like that is possible it should also send back the name of the table. Last: I prefixed my tables by the step of the project they refer to, for ex step1_table,step1_table2, step2_table Is it possible to use wildchars for the search, something like select * from step1_* ? It's probably possible to do a small script describing all the columns then building a query to search in, but I was wondering if there was an already existing mechanism. Thanks, melanie _ Be the first to hear what's new at MSN - sign up to our free newsletters! http://www.msn.co.uk/newsletters -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: global search
Hi Gleb,all, Thanks for the reply, but unfortunatly we are still under 4.0 and I have absolutely no control on that.(and I tried!!) So basically except the external script no option? Anything for the wildchars? :-( Last: I prefixed my tables by the step of the project they refer to, for ex step1_table,step1_table2, step2_table Is it possible to use wildchars for the search, something like select * from step1_* ? Thanks, melanie From: Gleb Paharenko [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Re: global search Date: Wed, 14 Dec 2005 12:48:22 +0200 Hello. It's probably possible to do a small script describing all the columns then building a query to search in, but I was wondering if there was an already existing mechanism. Another approach is not to use external scripts, but rather stored routines and INFORMATION_SCHEMA databases in MySQL 5.0. mel list_php wrote: Hi, I was wondering if there is any way to do a global search through all the tables in mysql? (not taking into account the performance issue) If for example I want to search ABC in all the columns of one table: select * from table where [whatever the column] like '%ABC%' or globally select * from [whatever the table] where [whatever the column] like '%ABC%' I think that if something like that is possible it should also send back the name of the table. Last: I prefixed my tables by the step of the project they refer to, for ex step1_table,step1_table2, step2_table Is it possible to use wildchars for the search, something like select * from step1_* ? It's probably possible to do a small script describing all the columns then building a query to search in, but I was wondering if there was an already existing mechanism. Thanks, melanie _ Be the first to hear what's new at MSN - sign up to our free newsletters! http://www.msn.co.uk/newsletters -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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] _ Are you using the latest version of MSN Messenger? Download MSN Messenger 7.5 today! http://messenger.msn.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
user to start/stop mysql without db access?
Hi, This may be a strange question, but I was wondering if it is possible to have a user starting/stopping the mysql server who wouldn't have access to the data. To explain: I want to set up a server as user Groupinstaller for example. This is a virtual user to allow other people of my group to start/stop the server by becoming Groupinstaller. But I don't want this user to be able to see the different databases of the different people, nor touch the data directory. For example, I have a database called db1 and my coworker has a db called db2. I am root of mysql, so I have access to all the databases, but he is just a user with access to his db and not to mine. If no other solution, I could eventually do with a read access, but I really don't want to allow write. I was thinking at replacing the typical mysql user by the Groupinstaller one, but this user owns the data directory... Does anybody know if it is possible, and how? thanks, melanie _ The new MSN Search Toolbar now includes Desktop search! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie Question on Update
Hi, In your query you try to update on the condition column 1 = number2 and column1 = number3. I think what you want is column 1 = number2 or column1 = number3 . If you use and it will try and found a record in column which has a value = number2 and at the same time = number3. hth, melanie From: Kraer, Joseph [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Newbie Question on Update Date: Wed, 30 Nov 2005 11:58:56 -0500 I am trying to update a couple of rows in a table by doing the following: update table set column5 = number1 where column 1 = number2 and column1 = number3 but it is not working. I tried listing the conditions separated by commas (where column 1 = number2, column1 = number3) and also didn't work. What am I doing wrong? Thanks in advance, Joseph Tito Kraer Business Systems Analyst Taylor, Bean Whitaker Mortgage Corp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Be the first to hear what's new at MSN - sign up to our free newsletters! http://www.msn.co.uk/newsletters -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Insert query problem
You can use backticks ( ` ) to escape the names: INSERT INTO `tickets` ( `id` , `from` , `departement` . hth, melanie From: Jeff [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Insert query problem Date: Tue, 29 Nov 2005 11:42:34 -0500 All, I can't get this query to run, it keeps compaining that there is a problem: The Query: insert into tickets(id,from,department,subject,body,lastaction,lastpost,priority,sta tus,created,fromname,lastpostname,attach,ct,uniq,notify,replyto) values(null,'jmckeon','1','test','test test',unix_timestamp(now()),'[EMAIL PROTECTED] [EMAIL PROTECTED]','Medium','Open',unix_timestamp(now()),'Jeff McKeon','Jeff McKeon','','','ks5hslajdfasd','1','[EMAIL PROTECTED]') The error: You have an error in your SQL syntax near 'from,department,subject,body,lastaction,lastpost,priority,status,create d,fromnam' at line 1 I suspect it doesn't like the fact that I have a field named from but I KNOW it's possible to write to this table and that field, I just can't figure out the correct syntax to get it to accept the fieldname from. Thanks, Jeff _ MSN Messenger 7.5 is now out. Download it for FREE here. http://messenger.msn.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql_fix_privileges
hi, i just inherited a mysql server 4.1 and need to run mysql_fix_privileges on it. However one of the databases hosted on it is production and I'd rather avoid it to be down. I ran the script on an 4.0 server test that I had installed and which was running when I ran the script. I had no problem with it, and the server is still up without downtime. I hope everything will be fine as well for the production server, but in case of how can I back up my data to revert if any problem? If I do a dump of mysql database will it be enough?meaning if there is any problem during the execution of the script will it be enough to reload the mysql database? I also had no problem with the old_passwords on my test db, shall I expect any problem with the clients who would like to connect after I run the script? Last, one of the server on which I have no control (4.0.9-gamma) but that I have to use would need the update of the privileges tables as well: what are the consequences/risks in not running it? Thanks for your help. melanie _ Be the first to hear what's new at MSN - sign up to our free newsletters! http://www.msn.co.uk/newsletters -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Changing range of identity column for future inserts
I think you want that: When you add an AUTO_INCREMENT column, column values are filled in with sequence numbers for you automatically. For MyISAM tables, you can set the first sequence number by executing SET INSERT_ID=value before ALTER TABLE or by using the AUTO_INCREMENT=value table option. From MySQL 5.0.3, you can use the ALTER TABLE ... AUTO_INCREMENT=value table option for InnoDB tables to set the sequence number for new rows if the value is greater than the maximum value in the AUTO_INCREMENT column. (from http://dev.mysql.com/doc/refman/5.0/en/alter-table.html) hth, melanie From: Dan Buettner [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Changing range of identity column for future inserts Date: Tue, 15 Nov 2005 08:50:03 -0600 I would like to change one of my tables so that future inserts use a higher range of numbers for the primary key values. I have a table 'event' with column: id int unsigned not null auto_increment primary key Currently new records are going into the table with id column values in the 3,000,000-range. I'd like to change that so that new records get id column values in the 13,000,000 range or so ... but without changing the values of existing records. This is so I can combine data into another, existing table with 12 million entries, without overlapping id numbers or assigning different ones. I can't find how to do this on the MySQL site. Any suggestions? Thanks, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Be the first to hear what's new at MSN - sign up to our free newsletters! http://www.msn.co.uk/newsletters -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[OT]Re: character sets.....(missing info)
For information for other people who would have the same problem: Warning Do not issue the query 'set names' with Connector/J, as the driver will not detect that the character set has changed, and will continue to use the character set detected during the initial connection setup. To allow multiple character sets to be sent from the client, the UTF-8 encoding should be used, either by configuring utf8 as the default server character set, or by configuring the JDBC driver to use UTF-8 through the characterEncoding property. (from http://dev.mysql.com/doc/refman/5.0/en/cj-character-sets.html) I changed the test db in utf8 and reinserted the data, but still the xml export is not ok, the accentuated characters are still wrong,even using the characterEncoding property of the driver. So I think the problem is probably not with mysql encoding but may be with the xml export from java, and I found that on the web to ensure you export in the right encoding: //Open an Output Stream Writer to set encoding OutputStream fout = new FileOutputStream(fileName); OutputStream bout = new BufferedOutputStream(fout); OutputStreamWriter out = new OutputStreamWriter(bout,UTF-8); Just wanted to give some info who may help somebody who is not a java developper (like me!). If somebody around knows if this has any chance to work or if there is an other problem please let me know! Thanks to everybody for your help. melanie _ MSN Messenger 7.5 is now out. Download it for FREE here. http://messenger.msn.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: character sets.....(missing info)
Hi András, Thanks for the explanation, I will try the SET NAMES to export my XML file in utf8 (I actually want it to be in utf8) I'm sorry if this is a basic question, but as I said before I'm kind of lost with the encodings From what you tell me, if the client utf8 send data to mysql isolatin1, there is no conversion done, and mysql will stil be happy to export utf8 (if asked to do so with a set names). So what is the role of the character set and collation at the database level?From what I understand the collation is used when you want to order results for example, case sensitive or not for example.Is that correct and then what is the character set used for? I'm building a new database with utf8 to be able to store chinese and cyrillic characters, and I will probably have to change that old isolatin1 to utf8 as well, but i'd like to understand what I'm doing thanks for your help, melanie If you write an hungarian article about your test, maybe you will plan an english translation?or is hungarian hard to learn? :-) From: BÁRTHÁZI András [EMAIL PROTECTED] To: mel list_php [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: character sets.(missing info) Date: Tue, 08 Nov 2005 19:55:30 +0100 Hi, if the character_set_client is by default latin1, does that mean that the java application is sending latin1?or is it changed at runtime? kind of lost again. As I understand, the charset of the column/table/database is irrelevant, there will be no conversion if you query from or insert into the table. There *is* conversion, but it depends on the communication settings - and it is detailed in the documentation. The character_set_* variables are your friend: *_client, *_connection and *_results as I know. If you want to solve your problem, then just run a set names utf8 SQL query before querying the data for the *XML* file, and you will get the data in UTF-8. Or just put a header into the XML file that says your data in Latin1 (I think it's not so easy in Java). Anyway, I don't know, how Java handles the character sets related to MySQL at the client side, but it works well for the console and for PHP and Perl clients. Bye, Andras ps: The column charset will be used for collations, and maybe for string functions, as I know. I'm planning to test it to make sure, and write a Hungarian article about it, but I think you won't be happy with it. :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Be the first to hear what's new at MSN - sign up to our free newsletters! http://www.msn.co.uk/newsletters -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
character sets.....
Hi guys, I don't really understand the character set in mysql from the doc. Tu summarize my problem: users upload xml files (UTF-8). part of the data is then inserted into MySQL (CHARSET=latin1) at that point, when I browse via phpmyadmin (which is isolatin1) or export to an html page no problem.(the application itself is in java) when i want to allow users to download the xml file reconstructed in UTF-8 some of the characters are wrong. I just inherited this application, so that's basically what I know about it. Is there any conversion done when data are inserted into the database? Should I convert the db itself in utf8? Or should I try to export it in isolatin1 when html and utf8 when xml?and how? I found that:(http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html) Example: Suppose that column1 is defined as CHAR(5) CHARACTER SET latin2. If you do not say SET NAMES or SET CHARACTER SET, then for SELECT column1 FROM t, the server sends back all the values for column1 using the character set that the client specified when it connected. If I understand well, my java client is connecting sending utf8.I don't use set names or character set, so when my java client asks select.. the result should be sent back in utf8 even if the db is latin1. Is that correct? and if it is, how does the html isolatin1 interprets the utf8? any help/explanation very welcome...! thanks a lot melanie _ The new MSN Search Toolbar now includes Desktop search! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
character sets.....(missing info)
sorry i should have put the result of the show variables: *** 5. row *** Variable_name: character_set_client Value: latin1 *** 6. row *** Variable_name: character_set_connection Value: latin1 *** 7. row *** Variable_name: character_set_database Value: latin1 *** 8. row *** Variable_name: character_set_results Value: latin1 *** 9. row *** Variable_name: character_set_server Value: latin1 *** 10. row *** Variable_name: character_set_system Value: utf8 *** 12. row *** Variable_name: collation_connection Value: latin1_swedish_ci *** 13. row *** Variable_name: collation_database Value: latin1_swedish_ci *** 14. row *** Variable_name: collation_server Value: latin1_swedish_ci if the character_set_client is by default latin1, does that mean that the java application is sending latin1?or is it changed at runtime? kind of lost again. From: mel list_php [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: character sets. Date: Tue, 08 Nov 2005 17:30:28 + Hi guys, I don't really understand the character set in mysql from the doc. Tu summarize my problem: users upload xml files (UTF-8). part of the data is then inserted into MySQL (CHARSET=latin1) at that point, when I browse via phpmyadmin (which is isolatin1) or export to an html page no problem.(the application itself is in java) when i want to allow users to download the xml file reconstructed in UTF-8 some of the characters are wrong. I just inherited this application, so that's basically what I know about it. Is there any conversion done when data are inserted into the database? Should I convert the db itself in utf8? Or should I try to export it in isolatin1 when html and utf8 when xml?and how? I found that:(http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html) Example: Suppose that column1 is defined as CHAR(5) CHARACTER SET latin2. If you do not say SET NAMES or SET CHARACTER SET, then for SELECT column1 FROM t, the server sends back all the values for column1 using the character set that the client specified when it connected. If I understand well, my java client is connecting sending utf8.I don't use set names or character set, so when my java client asks select.. the result should be sent back in utf8 even if the db is latin1. Is that correct? and if it is, how does the html isolatin1 interprets the utf8? any help/explanation very welcome...! thanks a lot melanie _ The new MSN Search Toolbar now includes Desktop search! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Be the first to hear what's new at MSN - sign up to our free newsletters! http://www.msn.co.uk/newsletters -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
hierarchical db/depth?
hi! I'm still trying to organize an hierachical db (I saw the last article on mysql.com: http://dev.mysql.com/tech-resources/articles/hierarchical-data.hml). I began working with what the author calls adjency list model (I think the nested set model is a bit too complex for what I want to do.In addition I found an equivalent schema in an other application which is close to mineand working fine) Basically, I have a table term (term_id, name), relation (relation_id,term_id1,term_id2,type_id) and a last table to identify the relations type id, relation_type (type_id,type_name). I can retrieve all the info I need with that query: SELECT t1.term_id2 AS lev0, term0.name AS parent0, trel1.type_name AS rel1, t1.term_id1 AS lev1, term1.name AS parent1, trel2.type_name AS rel2, t2.term_id1 AS lev2, term2.name AS parent2, trel3.type_name AS rel3, t3.term_id1 AS lev3, term3.name AS parent3 FROM relation AS t1 LEFT JOIN relation_type AS trel1 USING ( type_id ) LEFT JOIN term AS term0 ON ( t1.term_id2 = term0.term_id ) LEFT JOIN relation AS t2 ON t2.term_id2 = t1.term_id1 LEFT JOIN relation_type AS trel2 USING ( type_id ) LEFT JOIN term AS term1 ON ( t2.term_id2 = term1.term_id ) LEFT JOIN relation AS t3 ON t3.term_id2 = t2.term_id1 LEFT JOIN relation_type AS trel3 USING ( type_id ) LEFT JOIN term AS term2 ON ( t3.term_id2 = term2.term_id ) LEFT JOIN term AS term3 ON t3.term_id1 = term3.term_id WHERE t1.term_id2 =1 It's just adding a new sub-part to the query for each level. This is working fine on my data sample, because I know that I have exactly 4 levels of depth. I would like to know if there is a way to have a kind of auto-extension of the query if there is for example a fifth level? Thanks for any help or tip, melanie _ Be the first to hear what's new at MSN - sign up to our free newsletters! http://www.msn.co.uk/newsletters -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
hierarchical relations / innodb
Hi, I want to buil a hierarchical database, with different kind of relations. I have differents elements which are linked between them by different kinds of relations. Ex: element 1 IS A element 72 element 22 IS PART OF element 36 I want to have a table elements,for several reasons: I want to keep each element unique and indexed, if the definition of element 72 has to been modified is relation to element 1 wouldn't be modified as I'm working with indexes only to express the relationship and if element 72 has an other relation with something else it is updated at once. Then I think about having a table relations, something like, id_child, id_parent, kind of relation. that would give for example 1,72,IS A 22,36, PART OF ... Until here I think this is the right way to proceed, because it's the more flexible approach and will allow all the possible interactions. For the final depending application, we want to output a graphical tree of the relations/elements. I think this is possible with that design with performances ok as we won't have huge degrees of depth and we won't have a huge number of elements. Now the problem: One user want to delete element 72 for example. 2 options: it's impossible because element 72 as a child or we decide to warn the user and delete the childs at the same time. We haven't made the final decision yet, but the mechanism is still the same:deleting an element should check for existing children in the table relations. I'm used to work with myIsam, I could easily do somthing like select * from relations where id_parent=72, but I would like to know if it is possible to implement that with innoDB? I also would like to delete the element (or store it somewhere else) if it is orphaned, eg if element 1 was only the child of 72 and 72 is deleted the record in the relations table as to be deleted but the record in the elements table should be flaged or moved in an orphan table. In addition, if a user is working on element 72 we want to lock all the children. Sorry for the long explanation, I hope it is clear enough 2 questions: - do you see any problem with the design I've choosen?efficiency in building the tree for example,problem to establish relations? - do you think it is possible to use innodb in an efficient way for that (constraints and cascaded delete and locks) and do you have any good pointer on how to do it?or is it better to keep myIsam and manually do the checks? Thank you very much for taking time to read this, hope you will have any idea/comment! melanie _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 5.0.6-beta has been released
Hi, I downloaded and tried to install from the sources. ./configure, make seem ok, however the make install exit very quickly with an error code 127 (perror gives record-file is crashed, no idea of what that means!). My old 5.0.4 istall is working fine, so I don't think the system is in cause,but in case of it's a mandriva cooker. Anybody has any clue? Thanks Melanie From: Matt Wagner [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com mysql@lists.mysql.com, [EMAIL PROTECTED] Subject: MySQL 5.0.6-beta has been released Date: Tue, 31 May 2005 18:01:48 -0500 Hi, A new version of MySQL Community Edition 5.0.6-beta Open Source database management system has been released. This version includes support for Stored Procedures, Triggers, Views and many other features. It is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up-to-date at this point. If you cannot find this version on a particular mirror, please try again later or choose another download site. This is the third published Beta release in the 5.0 series. All attention will now be focused on fixing bugs and stabilizing 5.0 for later production release. NOTE: This Beta release, as any other pre-production release, should not be installed on ``production'' level systems or systems with critical data. It is good practice to back up your data before installing any new version of software. Although MySQL has done its best to ensure a high level of quality, protect your data by making a backup as you would for any software beta release. Please refer to our bug database at http://bugs.mysql.com/ for more details about the individual open and resolved bugs in this version. Changes in release 5.0.6: Functionality added or changed: * INCOMPATIBLE CHANGE: `MyISAM' and `InnoDB' tables created with `DECIMAL' columns in MySQL 5.0.3 to 5.0.5 will appear corrupt after an upgrade to MySQL 5.0.6. Dump such tables with `mysqldump' before upgrading, and then reload them after upgrading. (The same incompatibility will occur for these tables created in MySQL 5.0.6 after a downgrade to MySQL 5.0.3 to 5.0.5.) (Bug #10465, Bug #10625) * Added `REFERENCED_TABLE_SCHEMA', `REFERENCED_TABLE_NAME', and `REFERENCED_COLUMN_NAME' columns to the `KEY_COLUMN_USAGE' table of `INFORMATION_SCHEMA'. (Bug #9587) * Added a `--show-warnings' option to `mysql' to cause warnings to be shown after each statement if there are any. This option applies to interactive and batch mode. In interactive mode, `\w' and `\W' may be used to enable and disable warning display. (Bug #8684) * Removed a limitation that prevented use of FIFOs as logging targets (such as for the general query log). This modification _does not apply_ to the binary log and the relay log. (Bug #8271) * Added a `--debug' option to `my_print_defaults'. * When the server cannot read a table because it cannot read the `.frm' file, print a message that the table was created with a different version of MySQL. (This can happen if you create tables that use new features and then downgrade to an older version of MySQL.) (Bug #10435) * `SHOW VARIABLES' now shows the `slave_compresed_protocol', `slave_load_tmpdir' and `slave_skip_errors' system variables. (Bug #7800) * Removed unused system variable `myisam_max_extra_sort_file_size'. * Changed default value of `myisam_data_pointer_size' from 4 to 6. This allows us to avoid `table is full' errors for most cases. * The variable `concurrent_insert' now takes 3 values. Setting this to 2 changes MyISAM to do concurrent inserts to end of table if table is in use by another thread. * New `/*' prompt for `mysql'. This prompt indicates that a `/* ... */' comment was begun on an earlier line and the closing `*/' sequence has not yet been seen. (Bug #9186) * If strict SQL mode is enabled, `VARCHAR' and `VARBINARY' columns with a length greater than 65,535 no longer are silently converted to `TEXT' or `BLOB' columns. Instead, an error occurs. (Bug #8295, Bug #8296) * The `INFORMATION_SCHEMA.SCHEMATA' table now has a `DEFAULT_COLLATION_NAME' column. (Bug #8998) * `InnoDB': When the maximum length of `SHOW INNODB STATUS' output would be exceeded, truncate the beginning of the list of active transactions, instead of truncating the end of the output. (Bug #5436) * `InnoDB': If `innodb_locks_unsafe_for_binlog' option is set and the isolation level of the transaction is not set to serializable then `InnoDB' uses a consistent read for select in clauses like `INSERT INTO ... SELECT' and `UPDATE ... (SELECT)' that do not specify `FOR UPDATE' or `IN
Re: mysqlxml
Thank you very much, it worked fine!!! I just had a problem with aclocal which was version 1.4-p6, but by updating the automake to 1.7 everything was ok (in case of somebody trying to install and having the same problem). Could someone explain me what's the difference between adding an UDF, a native function or a patch? Is it the same but the format is different depending on the developper's choice? Thanks a lot for your help, Melanie From: Alexander Barkov [EMAIL PROTECTED] To: mel list_php [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: mysqlxml Date: Mon, 16 May 2005 22:35:54 +0500 Hello! The patch which can be found at http://mysql.r18.ru/~bar/myxml/ adds XPATH support into MySQL. It is not an UDF. It is a patch for main MySQL sources. In order to build MySQL with XPATH please do the following: 1. Unpack MySQL source tar ball, say mysql-5.0.4.tar.gz: tar -zxf mysql-5.0.4.tag.gz mysql-5.0.4 directory will appear, containing MySQL sources. 2. cd mysql-5.0.4 3. Unpack mysql-xml.tar.gz: tar -zxf mysql-xml.tar.gz After this step item_xmlfunc.cc and item_xmlfunc.h should appear in sql directory. Make sure they have appeared: ls sql/item_xmlfunc.* 4. Apply xml.diff: patch -p1 xml.diff 5. Run: aclocal autoheader automake autoconf 6. Build MySQL in usuall way, for example: ./confugure --prefix=/usr/local/mysql make make install If everything went wrong, you should have two new functions ExtractValue() and UpdateXML(). Enjoy. mel list_php wrote: Ok, I've been trying to install that function this morning and no luck I downloaded 5.0.4beta , compiled it . everything working fine. Trying the udf_example function (make udf_example.so) no problem. Trying to gcc -shared -o item_xmlfunc.so item_xmlfunc.cc it has been complaining about missing files (my_time.h .) and I managed to solve that by downloading the binary and copying the missing file. All is now in /usr/local/include. I'm trying to complie from /usr/local/mysql-5.0.4-beta/sql, but I don't think there's any path problem as it finds for example my_time.h. But know it finds an error in item_func.h about string2mydecimal which is not defined. Questions: - did anybody succeed to install that function (item_xmlfunc.cc) - any special tip about that? - is there an official repository of headers file wheer I could download the whole *.h files stable? - any solution to the error? thanks, Melanie Here is the whole error output: [EMAIL PROTECTED] sql]# gcc -shared -o item_xmlfunc.so item_xmlfunc.cc In file included from item.h:1381, from mysql_priv.h:457, from item_xmlfunc.cc:22: item_func.h: In member function `virtual my_decimal* Item_func_udf_str::val_decimal(my_decimal*)': item_func.h:968: error: `string2my_decimal' undeclared (first use this function) item_func.h:968: error: (Each undeclared identifier is reported only once for each function it appears in.) item_xmlfunc.cc: At global scope: item_xmlfunc.cc:56: error: use of enum `my_xml_node_type' without previous declaration item_xmlfunc.cc:56: error: ISO C++ forbids declaration of `type' with no type item_xmlfunc.cc: In member function `void Item_nodeset_func::prepare(String*)': item_xmlfunc.cc:167: error: 'class Item' has no member named 'val_nodeset' item_xmlfunc.cc: In member function `virtual Item::Type Item_nodeset_func::type() const': item_xmlfunc.cc:172: error: `XPATH_NODESET' undeclared (first use this function) item_xmlfunc.cc: In member function `virtual String* Item_nodeset_func::val_str(String*)': item_xmlfunc.cc:176: error: `val_nodeset' undeclared (first use this function) item_xmlfunc.cc:188: error: `MY_XML_NODE_TEXT' undeclared (first use this function) item_xmlfunc.cc: In member function `virtual longlong Item_xpath_cast_bool::val_int()': item_xmlfunc.cc:362: error: `XPATH_NODESET' undeclared (first use this function) item_xmlfunc.cc:364: error: 'class Item' has no member named 'val_nodeset' item_xmlfunc.cc: In member function `virtual longlong Item_func_xpath_position::val_int()': item_xmlfunc.cc:408: error: 'class Item' has no member named 'val_nodeset' item_xmlfunc.cc: In member function `virtual longlong Item_func_xpath_count::val_int()': item_xmlfunc.cc:426: error: 'class Item' has no member named 'val_nodeset' item_xmlfunc.cc: In member function `virtual double Item_func_xpath_sum::val_real()': item_xmlfunc.cc:443: error: 'class Item' has no member named 'val_nodeset' item_xmlfunc.cc:458: error: `MY_XML_NODE_TEXT' undeclared (first use this function) item_xmlfunc.cc: In member function `virtual Item::Type Item_nodeset_to_const_comparator::type() const': item_xmlfunc.cc:481: error: `XPATH_NODESET_CMP' undeclared (first use this function) item_xmlfunc.cc: In member function `virtual longlong Item_nodeset_to_const_comparator::val_int()': item_xmlfunc.cc:488: error: 'class Item' has no member named 'val_nodeset' item_xmlfunc.cc:503: error: `MY_XML_NODE_TEXT' undeclared (first use
Re: mysqlxml
' is not a member of `Item' item_xmlfunc.cc: In function `Item* create_func_count(MY_XPATH*, Item**, uint)': item_xmlfunc.cc:1110: error: `XPATH_NODESET' is not a member of `Item' item_xmlfunc.cc: In function `Item* create_func_sum(MY_XPATH*, Item**, uint)': item_xmlfunc.cc:1118: error: `XPATH_NODESET' is not a member of `Item' item_xmlfunc.cc: In function `int my_xpath_parse_UnionExpr(MY_XPATH*)': item_xmlfunc.cc:1818: error: `XPATH_NODESET' is not a member of `Item' item_xmlfunc.cc:1822: error: `XPATH_NODESET' is not a member of `Item' item_xmlfunc.cc: At global scope: item_xmlfunc.cc:2284: error: `Item_xml_str_func' has not been declared item_xmlfunc.cc: In function `void fix_length_and_dec()': item_xmlfunc.cc:2290: error: `nodeset_func' undeclared (first use this function) item_xmlfunc.cc:2292: error: `collation' undeclared (first use this function) item_xmlfunc.cc:2292: error: `args' undeclared (first use this function) item_xmlfunc.cc:2292: error: `arg_count' undeclared (first use this function) item_xmlfunc.cc:2292: error: `agg_arg_charsets' undeclared (first use this function) item_xmlfunc.cc:2315: error: `pxml' undeclared (first use this function) item_xmlfunc.cc:2334: error: `max_length' undeclared (first use this function) item_xmlfunc.cc: In function `uint xml_parent_tag(MY_XML_NODE*, uint, uint)': item_xmlfunc.cc:2366: error: `MY_XML_NODE_TAG' undeclared (first use this function) item_xmlfunc.cc:2367: error: `MY_XML_NODE_ATTR' undeclared (first use this function) item_xmlfunc.cc: In function `int xml_enter(MY_XML_PARSER*, const char*, uint)': item_xmlfunc.cc:2399: error: 'struct xml_stack_st' has no member named 'current_node_type' item_xmlfunc.cc: In function `int xml_value(MY_XML_PARSER*, const char*, uint)': item_xmlfunc.cc:2429: error: `MY_XML_NODE_TEXT' undeclared (first use this function) item_xmlfunc.cc: At global scope: item_xmlfunc.cc:2474: error: `Item_xml_str_func' has not been declared item_xmlfunc.cc: In function `String* parse_xml(String*, String*)': item_xmlfunc.cc:2484: error: 'struct MY_XML_PARSER' has no member named 'flags' item_xmlfunc.cc:2484: error: `MY_XML_FLAG_RELATIVE_NAMES' undeclared (first use this function) item_xmlfunc.cc:2484: error: `MY_XML_FLAG_SKIP_TEXT_NORMALIZATION' undeclared (first use this function) item_xmlfunc.cc:2493: error: 'struct MY_XML_PARSER' has no member named 'current_node_type' item_xmlfunc.cc:2493: error: `MY_XML_NODE_TAG' undeclared (first use this function) item_xmlfunc.cc: At global scope: item_xmlfunc.cc:2504: error: `Item_func_xml_extractvalue' has not been declared item_xmlfunc.cc: In function `String* val_str(String*)': item_xmlfunc.cc:2507: error: `nodeset_func' undeclared (first use this function) item_xmlfunc.cc:2508: error: `args' undeclared (first use this function) item_xmlfunc.cc:2509: error: `pxml' undeclared (first use this function) item_xmlfunc.cc:2511: error: `null_value' undeclared (first use this function) item_xmlfunc.cc:2514: error: `tmp_value' undeclared (first use this function) item_xmlfunc.cc: At global scope: item_xmlfunc.cc:2519: error: `Item_func_xml_update' has not been declared item_xmlfunc.cc: In function `String* val_str(String*)': item_xmlfunc.cc:2520: error: redefinition of `String* val_str(String*)' item_xmlfunc.cc:2505: error: `String* val_str(String*)' previously defined here item_xmlfunc.cc:2523: error: `nodeset_func' undeclared (first use this function) item_xmlfunc.cc:2524: error: `args' undeclared (first use this function) item_xmlfunc.cc:2525: error: `tmp_value3' undeclared (first use this function) item_xmlfunc.cc:2526: error: `pxml' undeclared (first use this function) item_xmlfunc.cc:2527: error: `tmp_value2' undeclared (first use this function) item_xmlfunc.cc:2529: error: `null_value' undeclared (first use this function) item_xmlfunc.cc:2547: error: `tmp_value' undeclared (first use this function) item_xmlfunc.cc:2548: error: `collation' undeclared (first use this function) item_xmlfunc.cc:2549: error: `MY_XML_NODE_TAG' undeclared (first use this function) From: Bastian Balthazar Bux [EMAIL PROTECTED] To: mel list_php [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: mysqlxml Date: Thu, 12 May 2005 11:42:32 +0200 mel list_php wrote: Hi guys, I was trying to download the mysqlxml patch for mysql 5.0 but didn't succeed from the url: http://d.udm.net/bar/myxml/mysqlxml.tar.g does anybody know where I could find it? Did anybody tried to use it or have any link to a doc/tutorial in addition to the presentation of Alexander Barkov (http://mysql.r18.ru/~bar/myxml/XMLXpathSupportInMySQL.pdf)? Thanks, Melanie try http://mysql.r18.ru/~bar/myxml/ _ Be the first to hear what's new at MSN - sign up to our free newsletters! http://www.msn.co.uk/newsletters -- . These pages are best viewed by coming to my house and looking at . . my monitor. [S
mysqlxml
Hi guys, I was trying to download the mysqlxml patch for mysql 5.0 but didn't succeed from the url: http://d.udm.net/bar/myxml/mysqlxml.tar.g does anybody know where I could find it? Did anybody tried to use it or have any link to a doc/tutorial in addition to the presentation of Alexander Barkov (http://mysql.r18.ru/~bar/myxml/XMLXpathSupportInMySQL.pdf)? Thanks, Melanie _ Be the first to hear what's new at MSN - sign up to our free newsletters! http://www.msn.co.uk/newsletters -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LIKE question - is it possible?
I think that if you explode your words with AND it should work in any order: SELECT * FROM table WHERE c LIKE '%word1%' AND c LIKE '%word2%' AND c LIKE '%word3%'; but there's maybe something better to do! From: Micha Berdichevsky [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: LIKE question - is it possible? Date: Thu, 14 Apr 2005 13:53:31 +0200 Hi group. I have a table with a varchar(250) column in it (let's call it c) I want to select values that contain a number of given words in them (three or more), in any words order I currently use SELECT * FROM table WHERE c LIKE %word1%word2%word3%; I was wandering if it is possible to use a query where the LIKE (or anything else) searches for my given strings in any order. I'm using MySQL 4.1.11 on windows XP, if it matters. Thanks. Micha. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: design: table depending on a column
Hi Gordon, hi list! Thank you for your help. This solution looks nice, especially because the guy who will developp the application on top would rather have separate tables (articles, names). That would give a schema like: # # Table structure for table `model` # CREATE TABLE `model` ( `modelId` int(11) NOT NULL auto_increment, `modelName` varchar(250) NOT NULL default '', PRIMARY KEY (`modelId`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; # # Table structure for table `element` # CREATE TABLE `element` ( `elementId` int(11) NOT NULL auto_increment, `modelId` int(11) NOT NULL default '0', `databaseName` varchar(50) NOT NULL default '', `annotationID` int(11) NOT NULL default '0', PRIMARY KEY (`elementId`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; # # # Table structure for table `database1` # CREATE TABLE `database1` ( `databaseId` int(11) NOT NULL default '0', `name` varchar(250) NOT NULL default '', PRIMARY KEY (`databaseId`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; # # # Table structure for table `database2` # CREATE TABLE `database2` ( `databaseId` mediumint(11) NOT NULL default '0', `title` varchar(250) NOT NULL default '', `author` varchar(250) NOT NULL default '', `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`databaseId`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; # Each time an user wants to display all the information regarding one model, he has to retrieve all the elementsID belonging to that model and for each of that elementID looking in the databaseX table depending on the databaseName in the elements table. The problem is that they want to reference something like 20 external DBs. This will be available on the web, so it has to be fast enough to build the webpage for the user. I'm just worrying about the 20 something joins that may be needed. The huge advantage is for the search:the user knows in which external DB he wants to look, so the search will be only a query to the dedicated table (database2 for ex if the user wants to retrieve articles) Do you think this kind of schema will be ok for the display of information? Thank you very much for your time, Melanie From: Gordon [EMAIL PROTECTED] To: 'mel list_php' [EMAIL PROTECTED], mysql@lists.mysql.com Subject: RE: design: table depending on a column Date: Tue, 12 Apr 2005 10:32:17 -0500 As long as articles.annotationID can be made distinct from names.annotationID why not use 2 left joins. You may have to test annotationType in the select section to map the fields. Something like the following. SELECT elements.annotationID, CASE annotationType WHEN 'names' THEN names.name WHEN 'articles' THEN articles.title ELSE '' END AS FIELD1, CASE annotationType WHEN 'names' THEN '' WHEN 'articles' THEN articles.author ELSE '' END AS FIELD2 FROM elements LEFT JOIN articles USING (annotationID) LEFT JOIN names USING (annotationID) -Original Message- From: mel list_php [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 12, 2005 8:59 AM To: mysql@lists.mysql.com Subject: design: table depending on a column Hi list, I have a design problem, I'd like to know if there is a nice way to solve it I have elements that can be annotated, an annotation is basic info and a link on an other database. For example: my element id 3, called testElement, is annotated. the annotation depends on the foreign database, sometimes it's articles so i'd like to have id, title, author,abstract, sometimes it's just a name so in that case I would have id and name.In both id is the id required to find the information in the foreign db. The goal is to search for a string in these annotations and retrieve the element id. At the beginning we will know in which foreign database we want to search (articles or name) but these could be extended later on. So my ideas: -the trivial approach having everything in one table is not realistic because I have other attributes (elementName,elementOrigin) for each elementID that I don't want to repeat. - having a table with elementID,annotationID and an other table with annotationID, title, author,name what I don't like here is having only one table for all the annotations in all the databases, if I know in which db to search merging everythin will slow down a string search -having a table with elementID,annotationID,annotationType, and depending on the annotationType searching in the right table: table articles (annotationID,title, author) or table names (annotationID,name). what I don't like in that case is that I have to retrieve the value of the attribute annotationType and then do the search depending on that value. (is there a way to join with a table
RE: design: table depending on a column
When you mention external databses, I got curious. Do you mean external as in not on that MySQL server? Or, do you mean external as in same server, different database? If you are designing your site correctly, the user should know nothing about your data storage. That means that the user never knows which database to look in for details but your application will. I would not try to JOIN 20 tables together just to avoid writing a SQL statement in my application code. Since you say you have 20 separate classes of additional (external) information, it would make better sense to me to query the primary record then query the appropriate source of your external information and merge the two recordsets in the applicaiton layer to produce the appropriate output. No co-mingling of data is required except on the finished page. That way your external data can actually come from ANY source (not just the same MySQL server). Shawn Green Database Administrator Unimin Corporation - Spruce Pine external means different server, different databases (actually most of them are oracle). the user knows nothing about the storage, it's currently xindice and we'd like to migrate to mysql. We have complex model, a model is constituted of several elements, each of them can have one or several annotation. These annotation are part of the model, something like externalDB=articles, accession=1234. We want to display the model and allow the user to download the xml. In xindice we are storing the xml directly, the queries trhough xpath are easy and the display is just a call of the xml file. At the moment this is ok because we only have a few models, but we will soon be limitated. If I understand you well, you suggest something like retrieving all the elements ID and then having a loop looking for each of them for the complete info rather than joining all the tables at the beginning. The external information won't be always available/accessible (it may be a database to which we have no direct access, or soe of them agreed for us to interanlly retrieve the data but don't want external access, or some who agreed for us to have one access one time and not several ones because their server wouldn't stand the charge.in summary we can't trust the availability of the sources) I have to store a minimal information (the one that is part of the model) to allow my user to download the model and provide a link to the complete ressource.(available or not, the model is still complete) So I can't get rid of the tables database1 to database20. The last solution is to display only the minimal information to the user (annotationID and databaseName) and if he asks for more querying the dedicated table. But I think there should be a way to arrange it to display the complete information from the beginning? Thanks for your help! Melanie _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: design: table depending on a column
OK, just to make sure: ALL of the data actually resides within MySQL and on the same server (regardless of it's original source)? That is good as MySQL does not permit retrieving data from other servers in a query (yet). So you have a table for the primary Model information, a second table for the Elements information and a table of the Annotations of an Element and a bunch of other tables that the Annotations information actually points to, right? That means your database schema looks something like this, right? Model | +-ElementsExternal Info tables | | +-Annotations-+ On the Annotations table are the ID of the Element it belongs to a field that identifies what kind of annotation it is (which you can use to identify which table of outside information you need to link to) and the PK of the row in the correct table that contains the information in the Annotation. Have I grasped the problem correctly? My first thought would be to homogenize your external data into the fewest number of tables possible (one is preferred). That means that you do more work importing the data from your external source but it makes internal maintenance and the queries you are trying to write much easier. The problem is that each kind of annotation potentially has a different record structure. That means you literally have up to 20 different column formats to accommodate. Can you not keep the raw Annotation information in one (or more) table(s) and put a summarized version of each annotation into just one combined table?(In my picture above, Annotations would be a good candidate for the summarized info table) If you need the additional information available from the raw or original annotations, you can make another trip to the database to get it. If you can get by with just the summary info, so much the better. To get a full (raw-info) results, you will need to somehow combine the results of querying the 20 separate source tables. You can't do that within a single UNION query unless you can make them all appear to have the same column structure. And if you can do that, you can achieve the single homogenized (not summarized) Annotations table I mentioned before. Otherwise you will have to run up to 20 separate joins and use your application's code to make the separate results appear unified to the user. You can look for ways to save trips to the server by consolidating several queries to the same source table into one. If you consolidate correctly, you will need to combine only 20 resultsets (at most). Usually you will get away with fewer queries. Can you provide actual table structures (SHOW CREATE TABLE xxx\G)and some sample data for a complete record? You don't need to but it may make things a lot easier to understand. Because this list only accepts posts up to 3 bytes, you may need to start a new thread to make it all fit. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Yes, all the information will be stored in mysql. You are right for the db structure, except that in my case my elements have only the annotation property so I merged them into one table. Here are the tables: # # Table structure for table `database1` # CREATE TABLE `database1` ( `databaseId` int(11) NOT NULL default '0', `name` varchar(250) NOT NULL default '', PRIMARY KEY (`databaseId`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; # # # Table structure for table `database2` # CREATE TABLE `database2` ( `databaseId` mediumint(11) NOT NULL default '0', `title` varchar(250) NOT NULL default '', `author` varchar(250) NOT NULL default '', `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`databaseId`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; # # # Table structure for table `element` # CREATE TABLE `element` ( `elementId` int(11) NOT NULL auto_increment, `modelId` int(11) NOT NULL default '0', `databaseName` varchar(50) NOT NULL default '', `annotationID` int(11) NOT NULL default '0', PRIMARY KEY (`elementId`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; # # # Table structure for table `model` # CREATE TABLE `model` ( `modelId` int(11) NOT NULL auto_increment, `modelName` varchar(250) NOT NULL default '', PRIMARY KEY (`modelId`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; I don't have any data sample sorry I think having one table for the external databases won't be a good idea because the data are heterogeneous and if we decide to add a completly different db we will have to modify the structure. And my programmer definitly prefer the exploded version, because one of the main purpose will be to retrieve a model depending on his annotation, for example search all in the table database1 where name like '%name%', and he will know in which table
design: table depending on a column
Hi list, I have a design problem, I'd like to know if there is a nice way to solve it I have elements that can be annotated, an annotation is basic info and a link on an other database. For example: my element id 3, called testElement, is annotated. the annotation depends on the foreign database, sometimes it's articles so i'd like to have id, title, author,abstract, sometimes it's just a name so in that case I would have id and name.In both id is the id required to find the information in the foreign db. The goal is to search for a string in these annotations and retrieve the element id. At the beginning we will know in which foreign database we want to search (articles or name) but these could be extended later on. So my ideas: -the trivial approach having everything in one table is not realistic because I have other attributes (elementName,elementOrigin) for each elementID that I don't want to repeat. - having a table with elementID,annotationID and an other table with annotationID, title, author,name what I don't like here is having only one table for all the annotations in all the databases, if I know in which db to search merging everythin will slow down a string search -having a table with elementID,annotationID,annotationType, and depending on the annotationType searching in the right table: table articles (annotationID,title, author) or table names (annotationID,name). what I don't like in that case is that I have to retrieve the value of the attribute annotationType and then do the search depending on that value. (is there a way to join with a table which name would be retrieved?something like select * from elements left join (select annotationType from elements) on annotationID?) I think the second solution is much slower, but it seems more clear for me. The right way (one of the irght way!) is probably intermediate between both, but I can't see it. I have to be careful about the design because the searches will be a lot of text, so I'd like to optimize it. Thanks for any help, Melanie _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
float type / concat
Hi list,
I'm using php/mysql, I was updating a table through phpmyadmin then I saw
that to update all the columns which type is declared to float the
developper of phpmyadmin have added a concat.
Something like:
UPDATE `tableInduction` SET `inductionType` = 'screening' AND CONCAT(
`volume` ) = '1' AND `growthVessel` = '24-96 well plates' AND CONCAT(
`pH_start` ) = '2'.
the column volume and pH_start are of type float, and it's the same for all
the columns I declare as float.
I suppose that this has some purpose, but I don't understand.
concat is an operator to merge 2 strings, for example concat
('string1','string2') should return string1string2.
What's the point of doing that for column's name?Is it a kind of cast
because '1' is considered as string because of the quotes?
In the manual I saw:
select concat(14.3) gives as a result '14.3'.
Is that the same kind of cast?
Why isn't that done for integers?
Additionally, I want to store 1 or 2 decimals floating point number, what is
the best?float?double?
I saw that using float may give you some unexpected problems, as all
calculations in MySQL are done with double-precision.
Thanks for your advice,
Melanie
_
It's fast, it's easy and it's free. Get MSN Messenger today!
http://www.msn.co.uk/messenger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Resetiing indexes
Hi, I think you are looking for something like that: ALTER TABLE auto_increment=1 (or whatever value you want) melanie From: Mário Gamito [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Resetiing indexes Date: Thu, 24 Mar 2005 11:41:30 + Hi, I'm using MySQL on a project, and during the development stage, the number of the (auto incrmenting) indexes went increasing. Now that the deed is done, how can i reset them back to 1, without having to throw away the database and rebuilding it from scratch ? Any help would be apreciated. Warm Regards, Mário Gamito -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
search through one/several tables
Hi list, I would like to search for something into one or several tables. My first idea was to retrieve the tables' names, then for each of them retrieve the columns' name and have a look in each of this column. Is there a more elegant (fast) way to do that with mysql? Somebody has some tips/doc where I could look for search engines?My problem is that I don't have one big table with all the data but several little ones with few fields, so I don't think solutions like Lucene could work. Thanks for any help, Melanie _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: search through one/several tables
Unfortunatly they are not, I have something like 30 tables, with I would say 10 to 15 fields per table. The number of row per table is quite low, i think it won't exceed 500-1000/table. But I may sometimes have to search into dna sequences (around 5000 atcg characters in any order), so that is quite heavy. Maybe for that field a fulltext index would be helpful? Thanks for your help, Melanie From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 10:44:53 + mel list_php [EMAIL PROTECTED] wrote on 23/03/2005 10:14:07: Hi list, I would like to search for something into one or several tables. My first idea was to retrieve the tables' names, then for each of them retrieve the columns' name and have a look in each of this column. Is there a more elegant (fast) way to do that with mysql? Somebody has some tips/doc where I could look for search engines?My problem is that I don't have one big table with all the data but several little ones with few fields, so I don't think solutions like Lucene could work. If your tables are all identical, which it sounds like, you want to create a Merge Table: see http://dev.mysql.com/doc/mysql/en/merge-storage-engine.html Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: search through one/several tables
I can find the data, I was just wondering if mysql provides a kind of generic scan of a whole table. I could provide an advanced search, and ask the user what kind of info he wants to retrieve but I first would like a quickSearch that may retrieve too much info but is more intuitive. As I said, my first idea was to create a script to go through all my tables and scan the relevant columns (I don't want to scan the id keys for example), but I was just wondering if given a table it is possible to use mysql to scan all its columns: *** 1. row *** Table: dbLab Create Table: CREATE TABLE `dbLab` ( `labId` tinyint(5) NOT NULL auto_increment, `name` varchar(50) NOT NULL default '', `fullName` varchar(250) default NULL, `adress` varchar(150) NOT NULL default '', `city` varchar(50) NOT NULL default '', `country` varchar(50) NOT NULL default '', `url` varchar(100) NOT NULL default '', PRIMARY KEY (`labId`) ) TYPE=MyISAM COMMENT='list of partners' 1 row in set (0.00 sec) If I search for Smith I want to retrieve the name Smith, the fullname Smith, the address or city containing Smithbasically look for the word in all the columns, a shortener for select * from dbLab where name like'%Smith%' or fullName like '%Smith%' or adress like '%Smith%'. But maybe this is not a good way to do things? Melanie From: [EMAIL PROTECTED] To: mel list_php [EMAIL PROTECTED] CC: [EMAIL PROTECTED],mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 09:55:23 -0500 mel list_php [EMAIL PROTECTED] wrote on 03/23/2005 06:00:08 AM: Unfortunatly they are not, I have something like 30 tables, with I would say 10 to 15 fields per table. The number of row per table is quite low, i think it won't exceed 500-1000/table. But I may sometimes have to search into dna sequences (around 5000 atcg characters in any order), so that is quite heavy. Maybe for that field a fulltext index would be helpful? Thanks for your help, Melanie From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 10:44:53 + mel list_php [EMAIL PROTECTED] wrote on 23/03/2005 10:14:07: Hi list, I would like to search for something into one or several tables. My first idea was to retrieve the tables' names, then for each of them retrieve the columns' name and have a look in each of this column. Is there a more elegant (fast) way to do that with mysql? Somebody has some tips/doc where I could look for search engines?My problem is that I don't have one big table with all the data but several little ones with few fields, so I don't think solutions like Lucene could work. If your tables are all identical, which it sounds like, you want to create a Merge Table: see http://dev.mysql.com/doc/mysql/en/merge-storage-engine.html Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] I agree in priciple with Alec. Good database design can and should promote good data retreival. However, it sounds from your original question that you need to find all records on any table with some bit of information in any column. That problem is just too generic to provide specific help for. If you can't find certain pieces of data, it _may_ be that you haven't organized your data to the point that there is a place for everything and everything is in its place. There is always (except for the most trivial cases) more than one way to organize any set of data, however different schemas offer different advantages (size, speed, ease of use, etc). Which one will work best for you depends on the nature of your data, your database server's limitations (hardware, software, and operating system limits), and what you need to get from the data once it's organized. Some schemas make it harder to add or update data but make finding it a breeze; others are just the opposite. This sounds like a good time to honestly review your current schema to make sure it's going to meet your usage needs not just your data storage requirements. Ask the people who need to use the data you are storing what they need to find and adjust your schemas to fit. Sometimes it means asking them to look by using a different method as a compromise (you give a little, they give a little). Eventually you can work it out. If you have a schema design issue or if you have a more specific set of tables (please post their SHOW CREATE TABLE... results) and a sample query that you cannot solve (even if it's
Re: search through one/several tables
Unfortunatly I can't use the alias txt in the where clause: Standard SQL doesn't allow you to refer to a column alias in a WHERE clause. This is because when the WHERE code is executed, the column value may not yet be determined. For example, the following query is illegal: SELECT id, COUNT(*) AS cnt FROM tbl_name WHERE cnt 0 GROUP BY id; http://dev.mysql.com/doc/mysql/en/problems-with-alias.html From: Peter Brawley [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mel list_php [EMAIL PROTECTED], mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 10:46:10 -0600 Shawn, Mel I am not aware of any SQL dialect that supports a query of the kind you are asking about. If there were such a query it _might_ look something like this SELECT column list FROM table reference WHERE ANY_COLUMN(column name list) LIKE search parameter Right, but Mel can emulate your ANY_COLUMN with something like SELECT CONCAT( col1, ..., colN ) AS txt FROM tbl WHERE txt LIKE '%foobar%'; PB - [EMAIL PROTECTED] wrote: I am not aware of any SQL dialect that supports a query of the kind you are asking about. If there were such a query it _might_ look something like this SELECT column list FROM table reference WHERE ANY_COLUMN(column name list) LIKE search parameter but like I said, I can't think of any SQL server that has a function like ANY_COLUMN() or its equivalent. Sorry! I think you will need to do a column-by-column comparison (especially if you want to use LIKE or RLIKE). Shawn Green Database Administrator Unimin Corporation - Spruce Pine mel list_php [EMAIL PROTECTED] wrote on 03/23/2005 11:08:56 AM: I can find the data, I was just wondering if mysql provides a kind of generic scan of a whole table. I could provide an advanced search, and ask the user what kind of info he wants to retrieve but I first would like a quickSearch that may retrieve too much info but is more intuitive. As I said, my first idea was to create a script to go through all my tables and scan the relevant columns (I don't want to scan the id keys for example), but I was just wondering if given a table it is possible to use mysql to scan all its columns: *** 1. row *** Table: dbLab Create Table: CREATE TABLE `dbLab` ( `labId` tinyint(5) NOT NULL auto_increment, `name` varchar(50) NOT NULL default '', `fullName` varchar(250) default NULL, `adress` varchar(150) NOT NULL default '', `city` varchar(50) NOT NULL default '', `country` varchar(50) NOT NULL default '', `url` varchar(100) NOT NULL default '', PRIMARY KEY (`labId`) ) TYPE=MyISAM COMMENT='list of partners' 1 row in set (0.00 sec) If I search for Smith I want to retrieve the name Smith, the fullname Smith, the address or city containing Smithbasically look for the word in all the columns, a shortener for select * from dbLab where name like'%Smith%' or fullName like '%Smith%' or adress like '%Smith%'. But maybe this is not a good way to do things? Melanie From: [EMAIL PROTECTED] To: mel list_php [EMAIL PROTECTED] CC: [EMAIL PROTECTED],mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 09:55:23 -0500 mel list_php [EMAIL PROTECTED] wrote on 03/23/2005 06:00:08 AM: Unfortunatly they are not, I have something like 30 tables, with I would say 10 to 15 fields per table. The number of row per table is quite low, i think it won't exceed 500-1000/table. But I may sometimes have to search into dna sequences (around 5000 atcg characters in any order), so that is quite heavy. Maybe for that field a fulltext index would be helpful? Thanks for your help, Melanie From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 10:44:53 + mel list_php [EMAIL PROTECTED] wrote on 23/03/2005 10:14:07: Hi list, I would like to search for something into one or several tables. My first idea was to retrieve the tables' names, then for each of them retrieve the columns' name and have a look in each of this column. Is there a more elegant (fast) way to do that with mysql? Somebody has some tips/doc where I could look for search engines?My problem is that I don't have one big table with all the data but several little ones with few fields, so I don't think solutions like Lucene could work. If your tables are all identical, which it sounds like, you want to create a Merge Table: see http://dev.mysql.com/doc/mysql/en/merge-storage-engine.html Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http
RE: Getting error 136 during an update
Hi, error 136 = No more room in index file so I suppose it's a problem with the size needed for your keys, maybe that will help: http://dev.mysql.com/doc/mysql/en/key-space.html HTH, Melanie From: CheHax [EMAIL PROTECTED] Reply-To: CheHax [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Getting error 136 during an update Date: Tue, 22 Mar 2005 11:31:06 +0100 Hello there, I am receiving Got error 136 from storage engine and I need some light on this matter. I have a table with 23,000,000 records and I want to update it with datas from another one with the exact same amount of rows. The 2 tables can be linked by a primary key. Both tables are myISAM, and have not other key than the primary one. The server is a test server, on WinXP Pro. I run an UPDATE, like this one : UPDATE tbl1 INNER JOIN tbl2 USING (PKEY) SET tbl1.NUM = tbl2.COL1, tbl1.ADRESSE = tbl2.COL2, tbl1.CITY = tbl2.COL3; It actually updates 6 collumns but the problem does not seem to be here. When I run this query, it runs for a bit and then I got : Got error 136 from storage engine At first, I thought it would be the filesize limit of 2GB, but this file size is not valid because I have a NTFS file system. So I tried an update like that, just after my error : UPDATE tbl1 INNER JOIN tbl2 USING (PKEY) SET tbl1.NUM = tbl2.COL1, tbl1.ADRESSE = tbl2.COL2, tbl1.CITY = tbl2.COL3 WHERE tbl1.PKEY 2200; And it worked fine ! And the next one too : UPDATE tbl1 INNER JOIN tbl2 USING (PKEY) SET tbl1.NUM = tbl2.COL1, tbl1.ADRESSE = tbl2.COL2, tbl1.CITY = tbl2.COL3 WHERE tbl1.PKEY 1800 and tbl1.PKEY = 2200; So I am wondering where the problem comes from. It is an issue with the maximum_join_size ? If so, why didn't I get the real maximum_join_size error like it is described in the doc ? Thank you for your help! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help on query/group by
Hi,
Sorry for the late reply, out for the week-end!
Here is the information you asked for (I modified the columns' type as you
suggested)
mysql show create table matches\G
*** 1. row ***
Table: matches
Create Table: CREATE TABLE `matches` (
`protID` varchar(50) default NULL,
`drugID` int(11) default NULL,
`sentID` int(11) default NULL,
KEY `sentenceId` (`sentID`),
KEY `drugID` (`drugID`),
KEY `protID` (`protID`)
) TYPE=MyISAM
1 row in set (0.00 sec)
mysql show create table sentence\G
*** 1. row ***
Table: sentence
Create Table: CREATE TABLE `sentence` (
`id` int(10) unsigned NOT NULL auto_increment,
`text` text,
`pmid` int(11) default NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM
1 row in set (0.00 sec)
mysql show create table synonyms\G
*** 1. row ***
Table: synonyms
Create Table: CREATE TABLE `synonyms` (
`nameID` varchar(50) default NULL,
`syn` text,
UNIQUE KEY `c` (`nameID`(20),`syn`(20))
) TYPE=MyISAM
1 row in set (0.01 sec)
I think I used int as much as possible,here some data samples:
INSERT INTO `matches` VALUES ('Q9UP51', 202531, 4);
INSERT INTO `matches` VALUES ('SVC2_HUMAN', 202037, 5);
INSERT INTO `matches` VALUES ('PF2R_SHEEP', 202096, 6);
INSERT INTO `matches` VALUES ('CAQS_RAT', 202037, 7);
INSERT INTO `sentence` VALUES (4, 'Of NP10 contraceptives /NPtested ,
NPspan class=\'drug\'Ortho-Gynol/span /NPwas found to be the most
rapidly acting , followed by NPCooper Creme /NP, a
href=\'http://srs.ebi.ac.uk/srsbin/cgi-bin/wgetz?[UNIPROT-acc:(Q9UP51)]+-id+008+-view+UniprotDateView\'Lactikol-
B/a\n, NPVagi-Serol /NP, NPMarvosan /NP, NPClinicol /NP,
NPJelly-X /NP, NPBor-Oxyquin /NP, NPCellikol /NP, and
NPLanteen Blue Jelly /NPin NPthat order /NP. ', 12305459);
(the text is usually longer than 255 characters, so I think text is the only
choice, except longtext which can only be worst for indexation in my
opinion)
INSERT INTO `synonyms` VALUES ('202037', 'testosterone');
INSERT INTO `synonyms` VALUES ('Q91XV1', 'gonadotropin');
INSERT INTO `synonyms` VALUES ('202037', 'estradiol');
INSERT INTO `synonyms` VALUES ('Q91XV1', 'gonadotropin-suppressing');
I tried to run the query you gave me (with temporary tables) it is still too
long so I guess that my only solution now is to use indexes.
When modifying the columns' type from text to varchar, even if the text was
indexed as unique mysql complained about duplicates, and actually there
were few duplicates, I thought it was as you said because only the beginning
of the text field is indexed but I had 2 cases were the names were short
(less than 15 characters) and the same (even the spaces).Removing one and
the index on varchar was created. Any idea why?
I will try to play with the indexes, if I understand well I'd better index
the three columns in once, because that will automatically index each of
them?or am I wrong?
Anyway, thank you for all your advices, I'm really learning a lot of things
with that case!
Melanie
From: [EMAIL PROTECTED]
To: mel list_php [EMAIL PROTECTED]
CC: mysql@lists.mysql.com
Subject: Re: help on query/group by
Date: Fri, 18 Mar 2005 12:43:06 -0500
mel list_php [EMAIL PROTECTED] wrote on 03/18/2005 10:35:30 AM:
Hi again,
Thanks for the explanation about the join and the group by.
I wanted to test your query (almost a simple copy/paste :-)) ).
The first 2 queries are ok, but the third one still is too long :
mysql CREATE TEMPORARY TABLE tmpSynDrug( KEY ( nameID (20)) )
SELECT
nameID, max( syn ) AS drugSyn
- FROM synonyms
- WHERE syn
- LIKE 'a%'
- GROUP BY nameID;
Query OK, 9693 rows affected (1.07 sec)
Records: 9693 Duplicates: 0 Warnings: 0
mysql
mysql CREATE TEMPORARY TABLE tmpSynProt (key (nameID(20)))
- SELECT nameID, max(syn) as protSyn
- FROM synonyms
- WHERE syn LIKE 'a%'
- GROUP BY nameID;
Query OK, 9693 rows affected (1.03 sec)
Records: 9693 Duplicates: 0 Warnings: 0
mysql
mysql CREATE TEMPORARY TABLE tmpMatch( KEY ( sentID) ) SELECT
m.sentID,
m.drugID, m.protID, tsd.drugSyn, tsp.protSyn
- FROM matches m
- INNER JOIN tmpSynDrug tsd ON tsd.nameID = m.drugID
- INNER JOIN tmpSynProt tsp ON tsp.nameID = m.protID;
I've tried once this morning (GMT time), it ran for one hour nothing I
killed mysql (btw, how can I kill only one query when mysql freezes?I
aborted but then mysqladmin shutdown didn't work anymore..)
I thought it was maybe because I have other heavy stuff running, but I
tried
a second time now and it's been running for 2 hours now, with almost
nothing
else on the desktop.
So apparently the join between matches and the other tables is still too
heavy
Any idea?
snip
OK, I reviewed what you have posted so far and I found a performance
killer. On the table matches, the columns protID and drugID are
declared as text
Re: help on query/group by
Hi Shawn, Thank you very much, I'm impressed by the time you took to answer me, and the quality of the reply!!! I forwarded the answer to my friend. I'm wondering, I knew the mechanism of temporary tables, but as I've never used it I was trying the left join way. Here is a summary of my questions: - why using inner join here?is there any difference with using a left join?I thought using a left join would decrease the number of results. - do you know why without group by my query was running very fast and become so slow with the group by?when it does a group by it's scanning the whole table or an other reason? - I don't know if his version of mysql supports subqueries, but I was wondering if it is possible to replace the temporary tables by subqueries and keeping the same efficiency (my friend told me he would like to have only one sql query). Once again thank you very much for your help, I will give temporary tables an other chance!!! Melanie From: [EMAIL PROTECTED] To: mel list_php [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: help on query/group by Date: Wed, 16 Mar 2005 13:52:44 -0500 I have a favorite technique for improving the results of queries , like this, which involve fairly large JOINed tables. It's a form of divide-and-conquer in that you pre-compute what you can then make the JOINS you need to finish up the results. I agree that the AND s2.syn LIKE '%' in the ON clause of the second JOIN is worthless and can be eliminated however I recognize this as a common pattern for a two-term search and it may be harder to eliminate that clause than at first glance. This is how I would speed things up, Your friend really has 3 types of searches possible: a) search by drug name only b) search by protein name only c) search by both drug name and protein name Since the c) is the more complex situation, I will model it. It's almost trivial to clip out the unnecessary parts to make the other two queries. If I typed everything correctly, you should be able to cut and paste the whole thing into the MySQL client and have it execute. ### begin## CREATE TEMPORARY TABLE tmpSynDrug (key nameID) SELECT nameID, max(syn) as drugSyn FROM synonyms WHERE syn LIKE 'a%' GROUP BY nameID; CREATE TEMPORARY TABLE tmpSynProt (key nameID) SELECT nameID, max(syn) as protSyn FROM synonyms WHERE syn LIKE 'a%' GROUP BY nameID; CREATE TEMPORARY TABLE tmpMatch (key sentID) SELECT m.sentID, m.drugID, m.protID, tsd.drugSyn, tsp.protSyn FROM matches m INNER JOIN tmpSynDrugs tsd ON tsd.nameID = m.drugID INNER JOIN tmpSynProt tsp ON tsp.nameID = m.protID; # what we should have now is a nice small table that meets most of the # original query criteria. Now to summarize by publication by # joining through the sentence table SELECT tm.drugID, tm.protID, tm.drugSyn, tm.protSyn, COUNT(DISTINCT s.pmid) as publications FROM tmpMatch tm INNER JOIN sentence s ON s.id = tm.sentID GROUP BY 1,2,3,4 ; # I used a shortcut in the GROUP BY, I referenced the columns # by their positions and not by their names #Now that we have the data we wanted we can cleanup after ourselves: DROP TABLE tmpMatch, tmpSynProt, tmpSynDrug; end # By minimizing the number of records that needs to be JOINed at each stage of the query, we keep things moving along. This technique is very useful for queries whose JOIN products are somewhere in the hundreds of billions or records or more (which yours easily is). If you didn't want the names to be representative, but listed, you would change the first two queries to be like: CREATE TEMPORARY TABLE tmpSynDrug (key nameID) SELECT nameID, syn as drugSyn FROM synonyms WHERE syn LIKE 'a%'; If you didn't need names at all I would just say: CREATE TEMPORARY TABLE tmpSynDrug (key nameID) SELECT DISTINCT nameID FROM synonyms WHERE syn LIKE 'a%' and modify the other queries to not look for the name columns. HTH, Shawn Green Database Administrator Unimin Corporation - Spruce Pine == Original message ==from mel list_php [EMAIL PROTECTED] 03/16/2005 12:45 PM == Hi, A friend of mine asked me to have a look at one of his query, and I'm stuck Here was his query: SELECT drugID, protID, COUNT(DISTINCT pmid), MAX(s1.syn) AS o1, MAX(s2.syn) AS o2 FROM matches INNER JOIN synonyms AS s1 ON drugID=s1.nameID AND s1.syn LIKE 'a%' INNER JOIN synonyms AS s2 ON protID=s2.nameID AND s2.syn LIKE '%' INNER JOIN sentence ON sentID=id GROUP BY drugID, protID ORDER BY o1, o2 LIMIT 601 and this is his goal: The idea is quite simple: The table called 'matches' contains triples drugID, protID, sentID indicating a co-occurence of a drug and a protein in a sentence. The user of course searches for either drug name or protein name or both. In the above query, the user wants everything for all drugs starting with 'a'. The MAX() calls more or less arbitrarily choose one of the many names
Re: help on query/group by
Hi again, Thanks for the explanation about the join and the group by. I wanted to test your query (almost a simple copy/paste :-)) ). The first 2 queries are ok, but the third one still is too long : mysql CREATE TEMPORARY TABLE tmpSynDrug( KEY ( nameID (20)) ) SELECT nameID, max( syn ) AS drugSyn - FROM synonyms - WHERE syn - LIKE 'a%' - GROUP BY nameID; Query OK, 9693 rows affected (1.07 sec) Records: 9693 Duplicates: 0 Warnings: 0 mysql mysql CREATE TEMPORARY TABLE tmpSynProt (key (nameID(20))) - SELECT nameID, max(syn) as protSyn - FROM synonyms - WHERE syn LIKE 'a%' - GROUP BY nameID; Query OK, 9693 rows affected (1.03 sec) Records: 9693 Duplicates: 0 Warnings: 0 mysql mysql CREATE TEMPORARY TABLE tmpMatch( KEY ( sentID) ) SELECT m.sentID, m.drugID, m.protID, tsd.drugSyn, tsp.protSyn - FROM matches m - INNER JOIN tmpSynDrug tsd ON tsd.nameID = m.drugID - INNER JOIN tmpSynProt tsp ON tsp.nameID = m.protID; I've tried once this morning (GMT time), it ran for one hour nothing I killed mysql (btw, how can I kill only one query when mysql freezes?I aborted but then mysqladmin shutdown didn't work anymore..) I thought it was maybe because I have other heavy stuff running, but I tried a second time now and it's been running for 2 hours now, with almost nothing else on the desktop. So apparently the join between matches and the other tables is still too heavy Any idea? From: [EMAIL PROTECTED] To: mel list_php [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: help on query/group by Date: Fri, 18 Mar 2005 09:14:02 -0500 Responses embedded below mel list_php [EMAIL PROTECTED] wrote on 03/18/2005 05:57:29 AM: Hi Shawn, Thank you very much, I'm impressed by the time you took to answer me, and the quality of the reply!!! I forwarded the answer to my friend. I'm wondering, I knew the mechanism of temporary tables, but as I've never used it I was trying the left join way. Here is a summary of my questions: - why using inner join here?is there any difference with using a left join?I thought using a left join would decrease the number of results. The primary difference between a LEFT JOIN and an INNER JOIN is that with an INNER JOIN matching records MUST exist in both tables before they are considered for evaluation by the WHERE clause. You usually retrieve MORE records with a LEFT JOIN than an INNER JOIN but that depends on your data, too. In no case can an LEFT JOIN return fewer records than an INNER JOIN, all other conditions being equal. - do you know why without group by my query was running very fast and become so slow with the group by?when it does a group by it's scanning the whole table or an other reason? The GROUP BY clause requests that the engine make another processing pass through the records that satisfy your WHERE clause conditions in order to aggregate records according to the columns you specified. It's that second pass and the processing that occurs within it that makes a grouped query slower to finish than an ungrouped one. (NOTE: Some ungrouped query results are so large that a grouped result may actually be _useful_ sooner due to less data transfer between the server and your application) - I don't know if his version of mysql supports subqueries, but I was wondering if it is possible to replace the temporary tables by subqueries and keeping the same efficiency (my friend told me he would like to have only one sql query). I have found very few cases where subqueries outperformed temp (or special-purpose, permanent) tables especially when working with larger amounts of data. Of course, subquery performance varies according to the nature of the subquery (can it be evaluated just once or does it have to have to be evaluated for each and every row of the result), the complexity of the subquery, and the hardware your server is on. The only way to know for sure is to develop a subquery version of this query and test it with your/their hardware. Once again thank you very much for your help, I will give temporary tables an other chance!!! Melanie You are most welcome! Shawn Green Database Administrator Unimin Corporation - Spruce Pine BIG snip _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help on query/group by
Hi, A friend of mine asked me to have a look at one of his query, and I'm stuck Here was his query: SELECT drugID, protID, COUNT(DISTINCT pmid), MAX(s1.syn) AS o1, MAX(s2.syn) AS o2 FROM matches INNER JOIN synonyms AS s1 ON drugID=s1.nameID AND s1.syn LIKE 'a%' INNER JOIN synonyms AS s2 ON protID=s2.nameID AND s2.syn LIKE '%' INNER JOIN sentence ON sentID=id GROUP BY drugID, protID ORDER BY o1, o2 LIMIT 601 and this is his goal: The idea is quite simple: The table called 'matches' contains triples drugID, protID, sentID indicating a co-occurence of a drug and a protein in a sentence. The user of course searches for either drug name or protein name or both. In the above query, the user wants everything for all drugs starting with 'a'. The MAX() calls more or less arbitrarily choose one of the many names associated with drugID as a representative. With the COUNT() I want to find out how many different medline abstracts (not sentences) have a hit. The matches table is 1,247,508 rows, sentence is 817,255 rows and synonyms is 225,497 rows. First I think using inner join in that case is not helpful, because it is making a whole cartesian product on the tables, whereas a left join would limit the number of rows. The second line INNER JOIN synonyms AS s2 ON protID=s2.nameID AND s2.syn LIKE '%' is useless I think, because it just retrieves the not null values for protID. I also added indexes on the table (i'm not very familiar with indexes, so that is probably my problem) - on matches: index on protID,drugID and sentID - on sentence: index on id (primary key) - on synonyms: index on nameID,syn Here are the tables: mysql desc matches; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | protID | text | YES | MUL | NULL | | | drugID | text| YES | | NULL| | | sentID | int(11) | YES | MUL | NULL| | ++-+--+-+-+---+ 3 rows in set (0.00 sec) mysql desc sentence; +---+--+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+--+--+-+-++ | id| int(10) unsigned || PRI | NULL| auto_increment | | text | text | YES | | NULL || | pmid | int(11)| YES | | NULL || +---+--+--+-+-++ 3 rows in set (0.00 sec) mysql desc synonyms; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | nameID | text | YES | MUL | NULL| | | syn | text | YES || NULL| | ++--+--+-+-+---+ 2 rows in set (0.00 sec) I wanted to see how where used the indexes: mysql explain select * from matches left join synonyms on drugID=nameID; +--+--+---+--+-++-+---+ | table| type | possible_keys | key | key_len | ref | rows | Extra | +--+--+---+--+-++-+---+ | matches | ALL | NULL | NULL|NULL | NULL | 1247508 | | | synonyms | ref | c | c | 23| matches.drugID | 4 | | +--+--+---+--+-++-+---+ 2 rows in set (0.00 sec) mysql explain select * from matches left join synonyms on drugID=nameID left join sentence on sentID=id; +--++---+-+-++-+---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +--++---+-+-++-+---+ | matches | ALL | NULL | NULL |NULL | NULL | 1247508 | | | synonyms | ref | c | c | 23 | matches.drugID | 4 | | | sentence | eq_ref | PRIMARY | PRIMARY | 4 | matches.sentID | 1 | | +--++---+-+-++-+---+ 3 rows in set (0.00 sec) If I do a reverse query of the first one, no index is used ( select * from synonyms left join matches on drugID=nameID;) what I don't understand: I thought syn is indexed, so it will quickly find the ones beginning by 'a%' and then do the join with matches but on a limited number of records only, so it will be much faster. I finally manage to have something quite working: (but not the final result my friend is hoping!!) select * from matches left join synonyms
RE: MySql error...
From: S.Yousaf Shah [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: MySql error... Date: Tue, 8 Mar 2005 20:01:06 +0500 (PKT) Hi, I have installed mysql on my system using tar file. But when i start mysql it generates following error and shuts down. Plz help me out to run the MySql server as i have important data on it. [EMAIL PROTECTED] mysql-standard-4.0.21-pc-linux-i686]# ./bin/safe_mysqld Starting mysqld daemon with databases from /home/Yousaf/mysql-standard-4.0.21-pc-linux-i686/data STOPPING server from pid file /home/Yousaf/mysql-standard-4.0.21-pc-linux-i686/data/GDI.pid 050308 07:58:36 mysqld ended Regards, Syed Yousaf Shah. Bit-3B. Nust Institute of Information Technology. NIIT Distributed Grid Computing Research Group. Web Wizard Nust Institute of Information Technology. Chaklala Scheme 3 Rawalpindi. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySql error...
Hi, Have a look in the error file which is in mysql/data and called hostname.err to have more information on why it is shutting down. With that info you usually can find more help on the mysql website. Good luck! From: S.Yousaf Shah [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: MySql error... Date: Tue, 8 Mar 2005 20:01:06 +0500 (PKT) Hi, I have installed mysql on my system using tar file. But when i start mysql it generates following error and shuts down. Plz help me out to run the MySql server as i have important data on it. [EMAIL PROTECTED] mysql-standard-4.0.21-pc-linux-i686]# ./bin/safe_mysqld Starting mysqld daemon with databases from /home/Yousaf/mysql-standard-4.0.21-pc-linux-i686/data STOPPING server from pid file /home/Yousaf/mysql-standard-4.0.21-pc-linux-i686/data/GDI.pid 050308 07:58:36 mysqld ended Regards, Syed Yousaf Shah. Bit-3B. Nust Institute of Information Technology. NIIT Distributed Grid Computing Research Group. Web Wizard Nust Institute of Information Technology. Chaklala Scheme 3 Rawalpindi. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql crash - innodb not starting
I did ps -aux I see one mysqld running (mine, launched with --skip-innodb). I shut it down. ps -aux: nothing I tried to start it again, doesn't work. As it is running on a machine managed by the system service I went to see them after shutting it down again, they have no mysql process running. (still using ps-aux, but as root on that machine) From that I think it means I don't have any mysql running there, and I still get that error 11. Or is there an other to see if a file is already used by a mysql process? Do you think replacing the ibdata1 file by an empty file is possible? Or doing a new install , copying my isam tables from the data directory and not touching the innodb files? Doing a new install and setting up my tables (isam) with a backup dump is better? I would prefer to repair the existing installation, but have no idea where to begin Thanks a lot for your time. From: Heikki Tuuri [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Re: mysql crash - innodb not starting Date: Thu, 3 Mar 2005 23:20:56 +0200 Hi! Error 11 means that you already have a mysqld process running on the same files. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- Save the Pacific Northwest Tree Octopus! | http://zapatopi.net/treeoctopus.html | -- - Original Message - From: mel list_php [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, March 03, 2005 2:25 PM Subject: mysql crash - innodb not starting Hi, I just have a crash of my mysql 4.1.5 .(the machine where it was running just shutdown) Tried to restart it, and problem with innodb: 050303 11:58:46 [WARNING] Asked for 196608 thread stack, but got 126976 InnoDB: Unable to lock ./ibdata1, error: 11InnoDB: Error in opening ./ibdata1 050303 11:58:46 InnoDB: Operating system error number 11 in a file operation. InnoDB: Error number 11 means 'Resource temporarily unavailable'. InnoDB: See also section 13.2 at http://www.innodb.com/ibman.php InnoDB: about operating system error numbers. InnoDB: Could not open or create data files. InnoDB: If you tried to add new data files, and it failed here, InnoDB: you should now edit innodb_data_file_path in my.cnf back InnoDB: to what it was, and remove the new ibdata files InnoDB created InnoDB: in this failed attempt. InnoDB only wrote those files full of InnoDB: zeros, but did not yet use them in any way. But be careful: do not InnoDB: remove old data files which contain your precious data! 050303 11:58:46 [ERROR] Can't init databases 050303 11:58:46 [ERROR] Aborting 050303 11:58:46 [NOTE] ./bin/mysqld: Shutdown complete I'm sure I already saw that kind of problem, tried to go through the list but didn't find the thread again. In addition I'm not very familiar with that kind of thing. I don't use innodb at all, only myisam, so I was able to restart with --skip-innodb. About the error code ressource unavailable I tried later on no difference, the file ibdata1 is there with adequate permissions, I don't think that I have a my.cnf... To sum up I have no idea what to do, and I'm not a system guru! My first ideas: - removing the ibdata1 file and recreating an empty one - I saw a force-recovery option but have no idea how it works Hope somebody here can help me Thanks a lot _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- 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] _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql crash - innodb not starting
Hi Heikki, Still only one process with ps -fA. And mysql doesn't want to start normally. This morning I tried by deleting innodb files (ibdata1,ib_logfile0, ib_logfile1), and it didn't succeed in recreating them. I just tried once again now and everything is fine, mysql starts and recreates the files. Thank you very much for your help! Just for information: if I had an innodb table (I'm thinking about it), the data would have been in there. Any recovery method? Or I would have had to delete the files and restore the data from backup? Anyway, thanks a lot! Melanie From: Heikki Tuuri [EMAIL PROTECTED] To: mel list_php [EMAIL PROTECTED], mysql@lists.mysql.com Subject: Re: mysql crash - innodb not starting Date: Fri, 4 Mar 2005 15:50:26 +0200 Mel, do ps -fA Do you see more mysqld processes? If the ibdata1 file stays locked even though there is no mysqld process, then this is a bug in Linux. If you do not use InnoDB, you can delete ibdata1 and ib_logfiles. Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL Network from http://www.mysql.com/network/ - Alkuperäinen viesti - Lähettäjä: mel list_php [EMAIL PROTECTED] Vastaanottaja: mysql@lists.mysql.com Kopio: [EMAIL PROTECTED] Lähetetty: Friday, March 04, 2005 12:00 PM Aihe: Re: mysql crash - innodb not starting I did ps -aux I see one mysqld running (mine, launched with --skip-innodb). I shut it down. ps -aux: nothing I tried to start it again, doesn't work. As it is running on a machine managed by the system service I went to see them after shutting it down again, they have no mysql process running. (still using ps-aux, but as root on that machine) From that I think it means I don't have any mysql running there, and I still get that error 11. Or is there an other to see if a file is already used by a mysql process? Do you think replacing the ibdata1 file by an empty file is possible? Or doing a new install , copying my isam tables from the data directory and not touching the innodb files? Doing a new install and setting up my tables (isam) with a backup dump is better? I would prefer to repair the existing installation, but have no idea where to begin Thanks a lot for your time. From: Heikki Tuuri [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Re: mysql crash - innodb not starting Date: Thu, 3 Mar 2005 23:20:56 +0200 Hi! Error 11 means that you already have a mysqld process running on the same files. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- Save the Pacific Northwest Tree Octopus! | http://zapatopi.net/treeoctopus.html | -- - Original Message - From: mel list_php [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, March 03, 2005 2:25 PM Subject: mysql crash - innodb not starting Hi, I just have a crash of my mysql 4.1.5 .(the machine where it was running just shutdown) Tried to restart it, and problem with innodb: 050303 11:58:46 [WARNING] Asked for 196608 thread stack, but got 126976 InnoDB: Unable to lock ./ibdata1, error: 11InnoDB: Error in opening ./ibdata1 050303 11:58:46 InnoDB: Operating system error number 11 in a file operation. InnoDB: Error number 11 means 'Resource temporarily unavailable'. InnoDB: See also section 13.2 at http://www.innodb.com/ibman.php InnoDB: about operating system error numbers. InnoDB: Could not open or create data files. InnoDB: If you tried to add new data files, and it failed here, InnoDB: you should now edit innodb_data_file_path in my.cnf back InnoDB: to what it was, and remove the new ibdata files InnoDB created InnoDB: in this failed attempt. InnoDB only wrote those files full of InnoDB: zeros, but did not yet use them in any way. But be careful: do not InnoDB: remove old data files which contain your precious data! 050303 11:58:46 [ERROR] Can't init databases 050303 11:58:46 [ERROR] Aborting 050303 11:58:46 [NOTE] ./bin/mysqld: Shutdown complete I'm sure I already saw that kind of problem, tried to go through the list but didn't find the thread again. In addition I'm not very familiar with that kind of thing. I don't use innodb at all, only myisam, so I was able to restart with --skip-innodb. About the error code ressource unavailable I tried later on no difference, the file ibdata1 is there with adequate permissions, I don't think that I have a my.cnf... To sum up I have no idea what to do, and I'm not a system guru! My first ideas: - removing the ibdata1 file and recreating an empty one - I saw a force-recovery option but have no idea how it works Hope somebody here can help me Thanks a lot
mysql crash - innodb not starting
Hi, I just have a crash of my mysql 4.1.5 .(the machine where it was running just shutdown) Tried to restart it, and problem with innodb: 050303 11:58:46 [WARNING] Asked for 196608 thread stack, but got 126976 InnoDB: Unable to lock ./ibdata1, error: 11InnoDB: Error in opening ./ibdata1 050303 11:58:46 InnoDB: Operating system error number 11 in a file operation. InnoDB: Error number 11 means 'Resource temporarily unavailable'. InnoDB: See also section 13.2 at http://www.innodb.com/ibman.php InnoDB: about operating system error numbers. InnoDB: Could not open or create data files. InnoDB: If you tried to add new data files, and it failed here, InnoDB: you should now edit innodb_data_file_path in my.cnf back InnoDB: to what it was, and remove the new ibdata files InnoDB created InnoDB: in this failed attempt. InnoDB only wrote those files full of InnoDB: zeros, but did not yet use them in any way. But be careful: do not InnoDB: remove old data files which contain your precious data! 050303 11:58:46 [ERROR] Can't init databases 050303 11:58:46 [ERROR] Aborting 050303 11:58:46 [NOTE] ./bin/mysqld: Shutdown complete I'm sure I already saw that kind of problem, tried to go through the list but didn't find the thread again. In addition I'm not very familiar with that kind of thing. I don't use innodb at all, only myisam, so I was able to restart with --skip-innodb. About the error code ressource unavailable I tried later on no difference, the file ibdata1 is there with adequate permissions, I don't think that I have a my.cnf... To sum up I have no idea what to do, and I'm not a system guru! My first ideas: - removing the ibdata1 file and recreating an empty one - I saw a force-recovery option but have no idea how it works Hope somebody here can help me Thanks a lot _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Silly mistake
Hi Sam, To shutdown mysql I just use mysqladmin shutdown. http://dev.mysql.com/doc/mysql/en/server-shutdown.html About you restart problem, have a look in mysql/data/host.err file, it should give you a reason there. Last time I had that problem it's because I hadn't kill all the process. Good luck! From: sam wun [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Silly mistake Date: Wed, 02 Mar 2005 15:58:28 +0800 Hi, I admit I m silly to shutdown mysqld with the killall command in the Redhat server, I can't restart mysql service now. Most of the reason is because the script mysql.server come with the mysql 4.1.10 does not like mysql.server start or mysql.server stop, so I need to start it up use and shut it down with killall. Anyway, after killall mysql, I got the following error when I tried to restart it. Here is the error: ./mysqld Starting mysqld daemon with databases from /usr/local/mysql/data STOPPING server from pid file /usr/local/mysql/data/vivaserver.pid 050302 15:52:05 mysqld ended How can I start mysqld now? Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Silly mistake
Yeah, I have looked at it, but not sure whether I need to repopulate the mysql.host db file. If I do that, I may be also need to recreate all db passwords as well. The error is: InnoDB: log sequence number 0 43634. InnoDB: Doing recovery: scanned up to log sequence number 0 43634 050302 16:36:43 InnoDB: Flushing modified pages from the buffer pool... 050302 16:36:43 InnoDB: Started; log sequence number 0 43634 050302 16:36:43 [ERROR] Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist 050302 16:36:43 mysqld ended I don't know how you made your backup, apparently you missed the mysql.host table. Usually when making a new install a mysql database containing all the privileges is created, maybe you erased this when copying your files? In an other mail you say you used the --all-databases option so I suppose the error is somewhere else. To identify the problem you may try to recreate the mysql db, see if it works, and if yes check your dump. About your indexes I usually use mysqldump and the indexes are exported as well, you can check with show index but I'm not sure that is what you are looking for. http://dev.mysql.com/doc/mysql/en/mysqldump.html Since all DB data in this server are restored from the FreeBSD system in MySQL 5.0. I don't know how to recreate all indexes for db. Is there simple way for recreating all indexes? or check whether indexes are in-placed? Thanks Sam Good luck! From: sam wun [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Silly mistake Date: Wed, 02 Mar 2005 15:58:28 +0800 Hi, I admit I m silly to shutdown mysqld with the killall command in the Redhat server, I can't restart mysql service now. Most of the reason is because the script mysql.server come with the mysql 4.1.10 does not like mysql.server start or mysql.server stop, so I need to start it up use and shut it down with killall. Anyway, after killall mysql, I got the following error when I tried to restart it. Here is the error: ./mysqld Starting mysqld daemon with databases from /usr/local/mysql/data STOPPING server from pid file /usr/local/mysql/data/vivaserver.pid 050302 15:52:05 mysqld ended How can I start mysqld now? Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql index cardinality
Nobody to explain me that? From: mel list_php [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: mysql index cardinality Date: Fri, 25 Feb 2005 16:47:12 + Hi, A strange thing with index, I thought the cardinality was automatically updated (like for a primary key for exemple). When I use a primary key in a table, insert a row, the cardinality is increased of 1 as well. I just tried to do that with an INDEX, and the cardinality is none unless I update it with analyze table for example.(http://dev.mysql.com/doc/mysql/en/show-index.html) I also tried with KEY (which is supposed to be an alias of index) and after the first insertion it updated the cardinality but not later on. 1/any explanation?is there a kind of random update from time to time? 2/ is that cardinality important to know? I read that big cardinality will ensure that the index is used for joins for example. Does MySQL check the real cardinality before querying?Or do I have to run an analyze table from time to time? 3/ a primary key is just a peculiar index, so why is that value updated? Thanks for any explanation _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql index cardinality
Hi, A strange thing with index, I thought the cardinality was automatically updated (like for a primary key for exemple). When I use a primary key in a table, insert a row, the cardinality is increased of 1 as well. I just tried to do that with an INDEX, and the cardinality is none unless I update it with analyze table for example.(http://dev.mysql.com/doc/mysql/en/show-index.html) I also tried with KEY (which is supposed to be an alias of index) and after the first insertion it updated the cardinality but not later on. 1/any explanation?is there a kind of random update from time to time? 2/ is that cardinality important to know? I read that big cardinality will ensure that the index is used for joins for example. Does MySQL check the real cardinality before querying?Or do I have to run an analyze table from time to time? 3/ a primary key is just a peculiar index, so why is that value updated? Thanks for any explanation _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Authorizing Problem in MySQL 4 with Mandrake 10
Be careful maybe you are mistaking here: the mysql root user as nothing to see with the mandrake root user. For exemple I lauch the mysql server as root from command line whereas I'm just a user on my desktop. So the mandrake root password and the mysql root password are also completly different. maybe this can be helpful: http://dev.mysql.com/doc/mysql/en/unix-post-installation.html From: Prabath Ranasinghe [EMAIL PROTECTED] (by way of Prabath Ranasinghe [EMAIL PROTECTED]) Reply-To: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Authorizing Problem in MySQL 4 with Mandrake 10 Date: Wed, 23 Feb 2005 18:46:29 -0500 -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hash: SHA1 Hi All, I installed a fresh copy of Mandrake Linux 10.0 with MySQL. I tried to login to MySQL server ,But it says that authorization is failed. My hostname is localhost and user is root.I entered the root password. Here is the log :- [EMAIL PROTECTED] html]# mysql -h localhost -u root -p Enter password: ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) Please help me to solve this problem. Best Regards, Prabath. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFCHRXVIlUQPx/AjvYRAo2fAKCJ+DWXNgSbfW3RySeJGADUSytjWACgxHYt SHHwE2P22LankU/ovx/s28M= =k/T0 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT ERROR
If you want to compare the 2 tables you have to join them: select * from listings, fake where listings.id=fake.id; If you do your query SELECT * from listings where listings.id = fake.id; it simply doesn't know where to get fake.id From: Ed Curtis [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: SELECT ERROR Date: Wed, 23 Feb 2005 08:02:21 -0500 (EST) I'm trying to compare 2 tables and keep getting an error. SELECT * from listings where listings.id = fake.id; The error is Error 1109: Unknown table 'fake' in where clause or Error 1109: Unknown table 'listings' in where clause depending on the table order at the end of the query. Both tables do exist and I can select any or all contents from either of them seperately just not using the command above that practically comes straight from the documentation. Any ideas? Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a query please
what about your query returning all the users UNION your query returning user 101? SELECT U.User_ID, U.User_Firstname, U.User_Lastname FROM Users U, Allocations A WHERE (U.User_ID = A.User_ID) AND A.Project_ID = '12' AND ( U.User_Type = 'Staff' OR U.User_Type = 'Manager' OR U.User_Type = 'Administrator' ) ORDER BY User_Firstname UNION SELECT U.User_ID, U.User_Firstname, U.User_Lastname FROM Users U WHERE (U.User_ID = 101) not sure to understand what you want to do with the join if you anyway want to retrieve user 101 and select only the parameters from the first table From: shaun thornburgh [EMAIL PROTECTED] To: [EMAIL PROTECTED], mysql@lists.mysql.com Subject: Re: Help with a query please Date: Mon, 21 Feb 2005 13:24:55 + From: Rhino [EMAIL PROTECTED] To: shaun thornburgh [EMAIL PROTECTED],mysql@lists.mysql.com Subject: Re: Help with a query please Date: Mon, 21 Feb 2005 08:22:29 -0500 - Original Message - From: shaun thornburgh [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, February 21, 2005 7:28 AM Subject: Help with a query please Hi, I am having trouble with the following query: SELECT U.User_ID, U.User_Firstname, U.User_Lastname FROM Users U, Allocations A WHERE (U.User_ID = A.User_ID OR U.User_ID = 101) AND A.Project_ID = '12' AND ( U.User_Type = 'Staff' OR U.User_Type = 'Manager' OR U.User_Type = 'Administrator' ) ORDER BY User_Firstname; The query is meant to return all the users in the allocations table plus user 101, however the query returns 15 instances of the 101 user along with all the users in the allocations table... What's the problem then? It seems to be doing exactly what you want to do. Can you clarify how the actual result differs from the expected result? So far, they sound the same Rhino -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 266.1.0 - Release Date: 18/02/2005 Hi Rhino, Thanks for your reply, I would like the query to retun one instance of user 101 rather than 15! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MYSQL_RES ......
Hi,
I think you can find all the information you are looking for there:
http://dev.mysql.com/doc/mysql/en/c.html
You have a description of all the functions :
http://dev.mysql.com/doc/mysql/en/c-api-functions.html
For example:
http://dev.mysql.com/doc/mysql/en/mysql-fetch-row.html
MYSQL_ROW row;
unsigned int num_fields;
unsigned int i;
num_fields = mysql_num_fields(result);
while ((row = mysql_fetch_row(result)))
{
unsigned long *lengths;
lengths = mysql_fetch_lengths(result);
for(i = 0; i num_fields; i++)
{
printf([%.*s] , (int) lengths[i], row[i] ? row[i] : NULL);
}
printf(\n);
}
Maybe this is a good start point for you!
From: Mohsen Pahlevanzadeh [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Subject: MYSQL_RES ..
Date: Fri, 18 Feb 2005 16:44:06 -0800 (PST)
Dears,mysql_list_tables returns name of tables.(tables of a DB)
It returns MYSQL_RES type.
I want to split it to an array.
I'm newbie in SQL,Please guide me.
I'm working C.
yours,Mohsen
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
_
Want to block unwanted pop-ups? Download the free MSN Toolbar now!
http://toolbar.msn.co.uk/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN Problem
Would you mind giving me some additional explanation about outer join? In the mysql reference book I just found one line saying left outer join syntax exists only for compatibility with odbc. thanks! From: Michael Dykman [EMAIL PROTECTED] To: Albert Padley [EMAIL PROTECTED] CC: \MySQL List\ mysql@lists.mysql.com Subject: Re: JOIN Problem Date: Thu, 17 Feb 2005 12:20:44 -0500 On Thu, 2005-02-17 at 12:08, Albert Padley wrote: I have the following 2 tables: CREATE TABLE `division_info` ( `id` int(11) NOT NULL auto_increment, `division` varchar(50) NOT NULL default '', `spots` int(11) NOT NULL default '0', PRIMARY KEY (`id`), KEY `division` (`division`), KEY `spots` (`spots`) ) TYPE=MyISAM CREATE TABLE `team_info` ( `id` int(14) NOT NULL auto_increment, `division` varchar(50) NOT NULL default '', `application` varchar(9) NOT NULL default 'No', PRIMARY KEY (`id`), KEY `division` (`division`), ) TYPE=MyISAM I'm running the following query: SELECT division_info.division AS 'division', COUNT(team_info.division) AS 'count', division_info.spots as 'spots' FROM division_info LEFT JOIN team_info ON division_info.division = team_info.division WHERE application='ACCEPTED' GROUP BY division_info.division This query runs fine. However, it only returns divisions where there is at least 1 ACCEPTED team. I also need to show divisions where there are spots but not teams have yet been ACCEPTED. A little direction would be appreciated. Thanks. Al Padley SELECT division_info.division AS 'division', COUNT(team_info.division) AS 'count', division_info.spots as 'spots' FROM division_info LEFT == OUTER == JOIN team_info ON division_info.division = team_info.division WHERE application='ACCEPTED' GROUP BY division_info.division -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
subquery help for an update
Hi, I have one table tableNames ID,name, new_name. The columns ID and name are populated. I would like to update the field new_name from an other table tempName (ID,nameUpdated). The ID between the 2 tables are the same, but I don't have the info for all the ID. (so 568 rows in my first table only 550 in my second one) I saw in the MySQL cookbook different solutions, creating a new table from joining the 2 firsts, writing a script to do thisBut this book also says that with the subquery in MySQL 4.1 it should be possible through a direct query. As I updated to 4.1, I would like to give it a try. Here is what I want to do: update tableName set new_name= (select nameUpdated from tableName,tempName where tableName.ID=tempName.ID) First I have here the subquery error detailed in the manual: UPDATE t1 SET column2 = (SELECT MAX(column1) FROM t1); It's okay to use a subquery for assignment within an UPDATE statement, since subqueries are legal in UPDATE and DELETE statements as well as in SELECT statements. However, you cannot use the same table, in this case table t1, for both the subquery's FROM clause and the update target. and second how can I specify update for each ID? I tried the same with insert into tableName select nameUpdated from tableName, tempName where tableName.ID=tempName.ID, but of course the records are inserted at the end of the first table. I tried to find some help from the manual but saw nothing, and I think this is a common problem (as mentionned in the cookbook) but I can't find any solution and I'm becoming crazy trying to combine that 2 tables!!! If somebody could help on this query, but also give me some pointer to a good doc/book regarding subqueries? Thanks a lot for any help. _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: subquery help for an update
Found some help here: http://forums.mysql.com/read.php?10,10572,11064#msg-11064 UPDATE Table1 SET Table1.Field1 = ( SELECT count(*) FROM Table2 WHERE Table2.Code2=Table1.Code1 ) and that query works for me, great!! But I still don't understand why it updates properly without the WHERE clause. So if anybody has a link to a doc for subqueries? From: mel list_php [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: subquery help for an update Date: Wed, 16 Feb 2005 14:32:48 + Hi, I have one table tableNames ID,name, new_name. The columns ID and name are populated. I would like to update the field new_name from an other table tempName (ID,nameUpdated). The ID between the 2 tables are the same, but I don't have the info for all the ID. (so 568 rows in my first table only 550 in my second one) I saw in the MySQL cookbook different solutions, creating a new table from joining the 2 firsts, writing a script to do thisBut this book also says that with the subquery in MySQL 4.1 it should be possible through a direct query. As I updated to 4.1, I would like to give it a try. Here is what I want to do: update tableName set new_name= (select nameUpdated from tableName,tempName where tableName.ID=tempName.ID) First I have here the subquery error detailed in the manual: UPDATE t1 SET column2 = (SELECT MAX(column1) FROM t1); It's okay to use a subquery for assignment within an UPDATE statement, since subqueries are legal in UPDATE and DELETE statements as well as in SELECT statements. However, you cannot use the same table, in this case table t1, for both the subquery's FROM clause and the update target. and second how can I specify update for each ID? I tried the same with insert into tableName select nameUpdated from tableName, tempName where tableName.ID=tempName.ID, but of course the records are inserted at the end of the first table. I tried to find some help from the manual but saw nothing, and I think this is a common problem (as mentionned in the cookbook) but I can't find any solution and I'm becoming crazy trying to combine that 2 tables!!! If somebody could help on this query, but also give me some pointer to a good doc/book regarding subqueries? Thanks a lot for any help. _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
last_insert_id
Hi! I have a database where several users can connect and input data. I managed to have my insert queries as atomic, but I was wondering about one special case: I make one insert, and retrieve the last id inserted by mysql because I need to update an other table with that id. - if one user inserts and retrieves the id, but between both one other has inserted something the id returned will be the right one? - or do I have to lock my table, execute the query, retrieve the id, unlock the table? - is there a way to make an atomic query with this that would avoid me to use locks? Thanks a lot for any help, Melissa _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: last_insert_id
Yes that's what I mean I arranged to have single queries for the inserts. For example I avoid doing a select on criteria to retrieve that id an then an update of this id. thanks for help!!! From: Philippe Poelvoorde [EMAIL PROTECTED] Reply-To: 'mysql@lists.mysql.com ' mysql@lists.mysql.com To: mel list_php [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: last_insert_id Date: Tue, 15 Feb 2005 10:50:40 + mel list_php wrote: Hi! I have a database where several users can connect and input data. I managed to have my insert queries as atomic, but I was wondering about one special case: I make one insert, and retrieve the last id inserted by mysql because I need to update an other table with that id. - if one user inserts and retrieves the id, but between both one other has inserted something the id returned will be the right one? Last_insert_id() is consistent on a per-connection basis, meaning you don't need to use lock (hopefullly !) http://dev.mysql.com/doc/mysql/en/getting-unique-id.html btw, what do you mean by : I managed to have my insert queries as atomic. Because if you do : INSERT it's already suppose to be atomic. HIMH. -- Philippe Poelvoorde COS Trading Ltd. _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: last_insert_id
I just would ask for a precision: In my system, I include a connection file with my parameters (host,user,pass).This is the details of the account allowed to establish the connection with the mysql server. When 2 users are connecting to the database (through the web), they will use the same details (host,user,pass) for the connection. Does that mean that they are sharing the same mysql connection (and in that case will I need a lock?) or are they each of them opening their own connection? Can I check that somewhere? Thanks a lot. From: Philippe Poelvoorde [EMAIL PROTECTED] Reply-To: 'mysql@lists.mysql.com ' mysql@lists.mysql.com To: mel list_php [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: last_insert_id Date: Tue, 15 Feb 2005 10:50:40 + mel list_php wrote: Hi! I have a database where several users can connect and input data. I managed to have my insert queries as atomic, but I was wondering about one special case: I make one insert, and retrieve the last id inserted by mysql because I need to update an other table with that id. - if one user inserts and retrieves the id, but between both one other has inserted something the id returned will be the right one? Last_insert_id() is consistent on a per-connection basis, meaning you don't need to use lock (hopefullly !) http://dev.mysql.com/doc/mysql/en/getting-unique-id.html btw, what do you mean by : I managed to have my insert queries as atomic. Because if you do : INSERT it's already suppose to be atomic. HIMH. -- Philippe Poelvoorde COS Trading Ltd. _ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: last_insert_id
really sorry to bother you with my connections problems. I've made a test using select connection_id(), to see what was the current identifier for my connection. Each time I change of page, the connection_id is different, I suppose that is because I require my connection file at the beginning of each script. This seems fine to me as I don't want to lock the tables, and the last_insert_id is performed in the same file than the insert, so on the per connection basis it's perfect (thank you very much for your help!!). But it seems a bit strange to me to open so many connections. I know there is a limit somewhere, at the moment it is not a problem I don't have a lot of users and they are not coming often, but can it become one in the future? I tried to find in the documentation some information on when is a connection open or if it is possible to keep one connection per user, but found nothing. In addition I don't think it is possible because for the mysql server only one user gets connected, it doesn't care about the users I have in my authentication table. If I misunderstood something please point me to some doc or give me some advice... From: Philippe Poelvoorde [EMAIL PROTECTED] Reply-To: 'mysql@lists.mysql.com ' mysql@lists.mysql.com To: mel list_php [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: last_insert_id Date: Tue, 15 Feb 2005 10:50:40 + mel list_php wrote: Hi! I have a database where several users can connect and input data. I managed to have my insert queries as atomic, but I was wondering about one special case: I make one insert, and retrieve the last id inserted by mysql because I need to update an other table with that id. - if one user inserts and retrieves the id, but between both one other has inserted something the id returned will be the right one? Last_insert_id() is consistent on a per-connection basis, meaning you don't need to use lock (hopefullly !) http://dev.mysql.com/doc/mysql/en/getting-unique-id.html btw, what do you mean by : I managed to have my insert queries as atomic. Because if you do : INSERT it's already suppose to be atomic. HIMH. -- Philippe Poelvoorde COS Trading Ltd. _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
auto-increment field stops working
Hi list, I have a very strange (and worrying..!!!) problem with my tables. I'm running a 4.0.9 gamma (no choice for that) adn I had a MyIsam table with an auto-increment field. Everything worked fine friday, and today the auto-increment field is just not working anymore. When I want to insert a new value, the auto-index is blocked on number 127. Then it complains: duplicate entry for key 127. I deleted thatrecord, re insert a new row, and again same problem My table is basic taskId (int auto-increment) ownerId (int) targetId(int) date (date) My insert is just insert into tableTask (`ownerId`,`targetId`,`date`) values ($ownerId,$targetId,'$date') I think this syntax is correct, the auto_increment field should be generated automatically (it has always been until today) Does anybody ever had such a problem? I put a backup from friday on line it works fine, but of course I lost a few data. Can it come from a table corruption? any other ideas?I'm just worrying how I could know that kind of error, because it's one of my users who warned me I have a problem or I wouldn't have noticed it. Thanks a lot for your help. _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
auto-increment stops at 127
additional test, it is always bugging at the key 127... I put a backup online, with until 106. Added few test records, from key 127 it just doesn't want to increment the auto-increment field anymore. I'm completly lost here, any help would be greatly appreciated.. _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: php conection problems
You should try to do a : mysql_connect($host, $user, $password) or die(mysql_error()); to have an error message. It will be easier to find an answer from that I think.As I told you if it's the password problem you will have Client does not support authentication protocol for example. From: Jeff Mao [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: RE: php conection problems Date: Fri, 11 Feb 2005 11:29:17 -0500 HI All, I'm back,...I've removed MySQL completely and reinstalled it using the binaries for Mac OS X 10.3+ from the mysql website. I still have a Client API version of 3.23 in the phpinfo() while I see 4.1.9 from the command line. I can get things to work using the Old_Password suggested earlier,... Is this a Mac OS X thing? Or is this normal? Do all the rest of you have newer version of MySQL recognized by php/apache? Thoughts? Thanks in advance Jeff -- Jeff Mao [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: php conection problems
I'm not sure if it can be a password problem as you can connect through command line,but when upgrading to 4.1 I had the classical access deny error with phpmyadmin whereas everything else was ok. You can still have a look there: http://dev.mysql.com/doc/mysql/en/old-client.html but I think in that case you should have an explicit error message. (Client does not support authentication protocol). You can still try to update dbUser set password=old_password(***) for one of your user and give it a try. good luck! From: Jeff Mao [EMAIL PROTECTED] To: Jay Blanchard [EMAIL PROTECTED], mysql@lists.mysql.com Subject: RE: php conection problems Date: Thu, 10 Feb 2005 15:36:19 -0500 I've tried using a variety of very wide open permissions just to get something to connect including: using the root user/password in the php scripts declaring a user in mysql,... grant all on mydatabase.* to myuser identified by 'mypassword'; grant all on mydatabase.* to myuser@'%' identified by 'mypassword'; grant all on mydatabase.* to [EMAIL PROTECTED] identified by 'mypassword'; grant all on mydatabase.* to [EMAIL PROTECTED] identified by 'mypassword'; [my current IP] grant all on mydatabase.* to [EMAIL PROTECTED] identified by 'mypassword'; flushed privileges,..etc,...no go,... Thanks for any other ideas! Jeff At 2:09 PM -0600 2/10/05, Jay Blanchard wrote: [snip] In the past, I've always simply installed these two pieces as I mentioned above and everything was so easy,but it's been awhile since I last did this,did I miss something? [/snip] Permissions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jeff Mao [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
