LOAD INDEX INTO CACHE doesn't!
Hi :) I'm having trouble using LOAD INDEX INTO CACHE. It isn't loading any index blocks! I am using 4.1.7-standard, for pc-linux on i686 on a redhat box. mysqladmin debug reports this, before a LOAD INDEX: Key caches: default Buffer_size: 2147483648 Block_size: 1024 Division_limit: 100 Age_limit: 300 blocks used: 0 not flushed: 0 w_requests: 0 writes: 0 r_requests: 0 reads: 0 So I do this: mysql load index into cache records_x - ; +--+--+--+--+ | Table | Op | Msg_type | Msg_text | +--+--+--+--+ | test.records_x | preload_keys | status | OK | +--+--+--+--+ 1 row in set (0.01 sec) And then no index appears to have been loaded. Despite the index file being 800MB in size, after issuing this command there appears to be no disk IO, and no cpu used by mysql. It also returns immediately (0.01 sec) when it should take at least 10 seconds or so to read 800MB from the disk. Key caches: default Buffer_size: 2147483648 Block_size: 1024 Division_limit: 100 Age_limit: 300 blocks used: 0 not flushed: 0 w_requests: 0 writes: 0 r_requests: 0 reads: 0 I have tried creating specific caches too instead of using the default one, but that makes no difference. Myisamcheck -dvi reports: MyISAM file: records_x Record format: Fixed length Character set: latin1_swedish_ci (8) File-version: 1 Creation time: 2004-09-20 0:59:10 Recover time: 2004-10-17 3:54:44 Status: checked,analyzed Data records: 36322509 Deleted blocks: 0 Datafile parts: 36322509 Deleted data: 0 Datafile pointer (bytes): 4 Keyfile pointer (bytes): 3 Datafile length: 435870108 Keyfile length: 814886912 Max datafile length: 51539607550 Max keyfile length: 17179868159 Recordlength: 12 table description: Key Start Len Index Type Rec/key Root Blocksize 1 2 4 unique unsigned long 1 1024 1024 2 9 4 multip. unsigned long 2244 163126272 1024 Field Start Length Nullpos Nullbit Type 1 1 1 2 2 4 3 6 3 4 9 4 Other perhaps pertinant information via mysqladmin debug is: Memory status: Non-mmapped space allocated from system: 3780612 Number of free chunks: 8 Number of fastbin blocks: 0 Number of mmapped regions: 11 Space in mmapped regions: -2100240384 Maximum total allocated space: 0 Space available in freed fastbin blocks: 0 Total allocated space: 3747644 Total free space: 32968 Top-most, releasable space: 11488 Estimated memory (with thread stack): -2096332796 Status information: Current dir: /var/lib/mysql/ Running threads: 2 Stack size: 126976 Cheers! Andrew. __ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
rlimit and rlimit64 problems when compiling with ICC 8.1
Hello, I was able to compile mysql 4.0 and 4.1 (static) successfully with intel compiler 8.0 on an IA32 system running redhat enterprise linux 3.0. However when I tried to compile with intel compiler 8.1 with the same options (same as ones used by mysql AB, except i link statically) I kept getting the following errors: mysqld.cc(1912): error: argument of type rlimit * is incompatible with parameter of type const rlimit64 * if (setrlimit(RLIMIT_CORE, rl) global_system_variables.log_warnings) It seems something is wrong between rlimit and rlimit64. Since I'm using an IA32 system, I wonder why it has something to do with rlimit64. I tried to post the problem in the intel forum with no luck. google didn't return any similar stories either. Any suggestions is greatly appreciated. -- Best regards, MQ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bug or feature, 'blah' does NOT work with null records
In article [EMAIL PROTECTED], matt_lists [EMAIL PROTECTED] writes: I cant tell if this is a bug or a feature. Select from table where col 'blah' I use this all the time with other databases, works great, gives me everything that's not blah If those other databases return also NULL values, they're broken. but in mysql, it wont work if there's null records in the table I have to do this, select from table where ( col 'blah or isnull(col) ) Yes, of course. NULL 'blah' returns NULL, and that's perfectly standards-conformant. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT... SET - SQL standard?
Is the INSERT ... SET syntax (http://dev.mysql.com/doc/mysql/en/INSERT.html) MySQL specific or does it belong to a SQL (89, 92...?) standard? Thanks in advance Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bug or feature, 'blah' does NOT work with null records
From: Harald Fuchs [EMAIL PROTECTED] Yes, of course. NULL 'blah' returns NULL, and that's perfectly standards-conformant. Furthermore, it's quite logical. NULL is meant to indicate that the value is unknown. If a value is unknown it can be anything. So, in the example `col` 'blah', col can be anything, including 'blah'. If you take that into consideration the only outcome of `col` 'blah' if `col` = NULL *must* be NULL! Fortunately there is function COALESCE() that will return the first argument that is not NULL. In case of NULL values you can use a default value for an expression: COALESCE( `col`*2, 14) will produce 14 if `col` is NULL. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT... SET - SQL standard?
Frederic Wenzel wrote: Is the INSERT ... SET syntax (http://dev.mysql.com/doc/mysql/en/INSERT.html) MySQL specific or does it belong to a SQL (89, 92...?) standard? http://dev.mysql.com/doc/mysql/en/Extensions_to_ANSI.html In general usefull: http://developer.mimer.com/validator/index.htm Friedhelm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: run mysql service in Linux
Hi. Look at permissions on /var/lib/mysql (ls -l /var/lib/mysql). And, btw, it's not /var/liv, but /var/lib. MySQL should have ability to read this directory. See INSTALL-BINARY in the distribution directory and follow instructions in it exactly. Don't forget about mysql_install_db! Regards. Cecep Rosuludin [EMAIL PROTECTED] wrote: dear All Master of Mysql, I'm new in Mysql, and I'd just move to mysql Linux version..!and i have pro= blem to activate the service. I have installed Mysql linux ver. with this C= ommand tar-zxvf mysql-standard-4.0.21-pc-linux-i686.tar when i try to Configure ./configure ,there is a note that i don't have t= o configure because the mysql file type is Binary..! after that I create a = usr (groupadd mysql and usradd -g mysql mysql) then, i change of owner of data directory chown -R root /var/liv/mysql chown -R mysql /var/liv/mysql chgrp -R mysql /var/liv/mysql then, I try to start the service with this command mysqld_safe \ --user=3Dmysql [EMAIL PROTECTED] bin]# Starting mysqld daemon with databases from /var/lib/mys= ql STOPPING server from pid file /var/lib/mysql/server6.cma-cgm.com.pid 041028 09:10:27 mysqld ended [EMAIL PROTECTED] bin]# ./mysqld start 041028 9:42:55 Warning: Asked for 196608 thread stack, but got 126976 041028 9:42:55 Can't find messagefile '/usr/local/mysql/share/mysql/englis= h/errmsg.sys' 041028 9:42:55 Aborting --=20 ___ Find what you are looking for with the Lycos Yellow Pages http://r.lycos.com/r/yp_emailfooter/http://yellowpages.lycos.com/default.as= p?SRC=3Dlycos10 -- 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]
Re: Should I change the MySQL client library
Of course, it is good to upgrade to much more stable version. Karam Chand [EMAIL PROTECTED] wrote: Hello, MySQL has released 4.1.7 http://lists.mysql.com/announce/226 As you can see there have been some bugfixes in libmysqlclient. I am using libmysql.dll provided with 4.1.1 in my app. Do I need to upgrade it to the one provided with 4.1.7? Regards, Karam __ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail -- 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]
Re: query stopped working
Hi. Uncommon situation. Please tell me, what OS do you use, MySQL version and how was it compiled? Is there any clues at the end of .err file? J S [EMAIL PROTECTED] wrote: Hi, I have this query which used to work really fast but now it just seems to lock up the tables. I tried running it with EXPLAIN but it just hangs at the command prompt. I also tried adding the USE INDEX directives but still no luck. I've run myisamchk -m on both tables involved and no errors were reported so I'm really at a loss to why this has gone wrong. Can anybody help me out please? Thanks, JS. mysql SELECT DISTINCT uv.urlid FROM url_visit uv USE INDEX(url_server_ID), url_servers us - use index(ID) WHERE us.server LIKE %java% AND uv.url_server_ID=us.ID; mysql desc url_servers; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | ID | int(10) unsigned | | MUL | NULL| auto_increment | | server | varchar(255) | | PRI | || ++--+--+-+-++ 2 rows in set (0.00 sec) mysql show indexes from url_servers; +-++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-++--+--+-+---+-+--++--++-+ | url_servers | 0 | PRIMARY |1 | server | A | 279599 | NULL | NULL | | BTREE | | | url_servers | 1 | ID |1 | ID | A | 279599 | NULL | NULL | | BTREE | | +-++--+--+-+---+-+--++--++-+ 2 rows in set (0.00 sec) mysql desc url_visit; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | urlid | int(10) unsigned | | PRI | NULL| auto_increment | | url_scheme_ID | tinyint(3) unsigned | | | 0 | | | url_server_ID | int(10) unsigned | | MUL | 0 | | | url_path_ID | int(10) unsigned | | | 0 | | | url_query_ID| int(10) unsigned | | | 0 | | | url_category_ID | smallint(5) unsigned | | | 0 | | +-+--+--+-+-++ 6 rows in set (0.01 sec) mysql show indexes from url_visit; +---++---+--+---+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++---+--+---+---+-+--++--++-+ | url_visit | 0 | PRIMARY |1 | urlid | A |25881342 | NULL | NULL | | BTREE | | | url_visit | 0 | url_server_ID |1 | url_server_ID | A | 278294 | NULL | NULL | | BTREE | | | url_visit | 0 | url_server_ID |2 | url_path_ID | A |12940671 | NULL | NULL | | BTREE | | | url_visit | 0 | url_server_ID |3 | url_query_ID | A |25881342 | NULL | NULL | | BTREE | | | url_visit | 0 | url_server_ID |4 | url_scheme_ID | A |25881342 | NULL | NULL | | BTREE | | +---++---+--+---+---+-+--++--++-+ 5 rows in set (0.00 sec) -- 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
Mysql Sysem files and packages
Hi, I am doing a dbconversion form oracle to mysql. I would like to know the equivivalents in mysql for 1) Parameter file like init.ora in oracle 2) system package like diutil in oracle Thanks, Narasimha Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Ho do I backup
Hi there, I am a novice with mysql on the terminal side using shell. Basically I need to back up a database and have it saved locally from my server. I have tried using phpmyadmin but it fails as I think the data is too large. I have tried the following: mysqldump -u admin -p --databases yabbse yabbsebackup.sql It seemed to work, but where can I find the yabbsebackup.sql file? Not very experienced with backing up. Any help appreciated. Thanks Barry -- Barry Zimmerman Administrator Fishingvenues.com http://www.fishingvenues.com -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bug or feature, 'blah' does NOT work with null records
In article [EMAIL PROTECTED], Jigal van Hemert [EMAIL PROTECTED] writes: NULL is meant to indicate that the value is unknown. If a value is unknown it can be anything. So, in the example `col` 'blah', col can be anything, including 'blah'. If you take that into consideration the only outcome of `col` 'blah' if `col` = NULL *must* be NULL! Fortunately there is function COALESCE() that will return the first argument that is not NULL. In case of NULL values you can use a default value for an expression: COALESCE( `col`*2, 14) will produce 14 if `col` is NULL. This idiom, albeit terse, is not something you should get used to, because it precludes index usage. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bug or feature, 'blah' does NOT work with null records
From: Harald Fuchs [EMAIL PROTECTED] In article [EMAIL PROTECTED], Jigal van Hemert [EMAIL PROTECTED] writes: Fortunately there is function COALESCE() that will return the first argument that is not NULL. In case of NULL values you can use a default value for an expression: COALESCE( `col`*2, 14) will produce 14 if `col` is NULL. This idiom, albeit terse, is not something you should get used to, because it precludes index usage. I didn't say you should use it in a WHERE clause or other condition on large tables! ;-P I used a lot of COALESCE's to calculate some kind of score for each selected record and that was pretty fast. In this particular case I could find NULL values, but the score should still be a number. The COALESCE function came in handy to turn NULL values in some kind of default. But you're right when it comes to fast queries one should only compare columns to other columns or constants (these could be calculated constants). Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select with an IF statements
[snip] That's up to you of course. Personally, I think some things are better handled by the database and some are better handled by the programming language. Things like conditional logic tend to be best handled by the programming language in my view. Your mileage may vary ;-) [/snip] AT the risk of starting a religious war, I disagree to a degree. If the conditional logic on the programming language side does not cause or invoke more calls to the database that would be OK, but generally the less you have to go to the DB the better. If the conditional logic in the query causes less information to be returned you gain efficiency on both sides of the coin generally. Note that I said generally. Sometimes it is much more efficient to retunr larger datasets to the application and work the magic from there. If your databases are formed well, indexed for the job at hand, and the queries are written smertly you are better off putting more of the conditional logic in the query. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysqldump very slow
mysqldump running very slow. what might be the reason Server details === Dell RAM : 4 GB Innodb_buffer_pool_size : 1000MB i am using --quick option in mysqldump Thanks Anil DBA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: phpMyAdmin and MySQL 4.1
On Oct 27, 2004, at 10.26, Schalk Neethling wrote: Greetings After installing the new release of MySQL ( i.e. MySQL 4.1), I know get the following error from phpMyAdmin when using any of the auth types: Error #1251 - Client does not support authentication protocol requested by server; consider upgrading MySQL client. I get this whether I am using cconfig, http or cookie based authentication. Any ideas why this is happening? Is there a config setting in MySQL I should set to support the protocol? -- Kind Regards Schalk Neethling Web Developer.Designer.Programmer.President Volume4.Development.Design.Branding emotionalize.conceptualize.visualize.realize Tel: +27125468436 Fax: +27125468436 email:[EMAIL PROTECTED] Global: www.volume4.com i struggled w/ this for a while, before learning that even though i had mysql 4.1 installed, php4 was being compiled w/ it's own built-in mysql libraries, which are pre-4.1, hence the error. compiling php5 will solve the problem, as it's included mysql libraries are apparently newer, or compiling php4, and pointing configure to your mysql libraries (--with-mysql=/path/to/mysql) will also work. -ben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query stopped working
After I ran some database updates last night, it seems to be working OK again today. Not sure what happened! Thanks anyway. JS. Hi. Uncommon situation. Please tell me, what OS do you use, MySQL version and how was it compiled? Is there any clues at the end of .err file? J S [EMAIL PROTECTED] wrote: Hi, I have this query which used to work really fast but now it just seems to lock up the tables. I tried running it with EXPLAIN but it just hangs at the command prompt. I also tried adding the USE INDEX directives but still no luck. I've run myisamchk -m on both tables involved and no errors were reported so I'm really at a loss to why this has gone wrong. Can anybody help me out please? Thanks, JS. mysql SELECT DISTINCT uv.urlid FROM url_visit uv USE INDEX(url_server_ID), url_servers us - use index(ID) WHERE us.server LIKE %java% AND uv.url_server_ID=us.ID; mysql desc url_servers; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | ID | int(10) unsigned | | MUL | NULL| auto_increment | | server | varchar(255) | | PRI | || ++--+--+-+-++ 2 rows in set (0.00 sec) mysql show indexes from url_servers; +-++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-++--+--+-+---+-+--++--++-+ | url_servers | 0 | PRIMARY |1 | server | A | 279599 | NULL | NULL | | BTREE | | | url_servers | 1 | ID |1 | ID | A | 279599 | NULL | NULL | | BTREE | | +-++--+--+-+---+-+--++--++-+ 2 rows in set (0.00 sec) mysql desc url_visit; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | urlid | int(10) unsigned | | PRI | NULL| auto_increment | | url_scheme_ID | tinyint(3) unsigned | | | 0 | | | url_server_ID | int(10) unsigned | | MUL | 0 | | | url_path_ID | int(10) unsigned | | | 0 | | | url_query_ID| int(10) unsigned | | | 0 | | | url_category_ID | smallint(5) unsigned | | | 0 | | +-+--+--+-+-++ 6 rows in set (0.01 sec) mysql show indexes from url_visit; +---++---+--+---+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++---+--+---+---+-+--++--++-+ | url_visit | 0 | PRIMARY |1 | urlid | A |25881342 | NULL | NULL | | BTREE | | | url_visit | 0 | url_server_ID |1 | url_server_ID | A | 278294 | NULL | NULL | | BTREE | | | url_visit | 0 | url_server_ID |2 | url_path_ID | A |12940671 | NULL | NULL | | BTREE | | | url_visit | 0 | url_server_ID |3 | url_query_ID | A |25881342 | NULL | NULL | | BTREE | | | url_visit | 0 | url_server_ID |4 | url_scheme_ID | A |25881342 | NULL | NULL | | BTREE | | +---++---+--+---+---+-+--++--++-+ 5 rows in set (0.00 sec) -- 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:
Re: ulimit problem
That failed with an err27 as well. How do I set the debug flag on myisamchk ? --debug=/tmp/debug doesn't work. JS. Hi. Sounds OK, but don't forget to make a backup. J S [EMAIL PROTECTED] wrote: Thanks for your reply Gleb. I tried running with --safe-recover instead of -qr but that caused some other processes running on the box to hang and coredump so I had to kill it off. I'll try running: myisamchk -qr --tmpdir=/proxydb/mysql/tmp -O sort=16M -O key=16M -O read=1M -O write=1M --sort-index --analyze internet_usage Sound OK? JS. Hi. Usually myisamchk doesn't have the suid bit set, and it seems to be ran as root. See http://dev.mysql.com/doc/mysql/en/Table_maintenance.html Especially http://dev.mysql.com/doc/mysql/en/myisamchk_memory.html J S [EMAIL PROTECTED] wrote: Hi, I've run myisampack to compress a table and am now trying to recreate the index using myisamchk (as root) but I get an error 27. # myisamchk -rq --sort-index --analyze --tmpdir=/proxydb/mysql/tmp internet_usage.MYI - check record delete-chain - recovering (with sort) MyISAM-table 'internet_usage.MYI' Data records: 30972875 - Fixing index 1 myisamchk: Error writing file '/proxydb/mysql/tmp/ST3hTDMa' (Errcode: 27) myisamchk: error: 27 when fixing table MyISAM-table 'internet_usage.MYI' is not fixed because of errors Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag # perror 27 Error code 27: A file cannot be larger than the value set by ulimit. The ulimits are set as follows: default: fsize = 2097151 core = 2097151 cpu = -1 data = 262144 rss = 65536 stack = 65536 nofiles = 2000 root: fsize = -1 core = -1 cpu = -1 data = -1 rss = -1 stack = -1 nofiles = 4000 When I run myisamchk, is it running with root privilidges or as user mysql? By the way this is a large enabled filesystem. Thanks, JS. -- 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] -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error message
If I attempt to insert a record that has a duplicate key on a unique index, I get an error message of the form: ERROR 1062: Duplicate entry '1' for key 2 I'd like to get a little more information so as to provide the user with a meaningful message. Specifically: what index is Key 2? Is there a way to retrieve this information without hard coding IXxxx is key 1, IXyyy is key2, etc. It seems that modifications to the table could break all the error messages if I hard code this. tia ---Michael pgpQYXGkkWdcn.pgp Description: PGP signature
Re: Corrupted relay log
Paul Fierro wrote: One of my slaves stopped running due to a corrupted relay log. The error file shows: Error writing file 'dbs2.log' (errno: 28) Error in Log_event::read_log_event(): 'read error', data_len: 160, event_type: 2 $ perror 28 Error code 28: No space left on device You are out of disk space. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ho do I backup
- Original Message - From: Barry Zimmerman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 28, 2004 7:24 AM Subject: Ho do I backup Hi there, I am a novice with mysql on the terminal side using shell. Basically I need to back up a database and have it saved locally from my server. I have tried using phpmyadmin but it fails as I think the data is too large. I have tried the following: mysqldump -u admin -p --databases yabbse yabbsebackup.sql It seemed to work, but where can I find the yabbsebackup.sql file? Not very experienced with backing up. Any help appreciated. Thanks I suspect most people would use the -r (or --result-file) option to indicate where they want the backup written. That's what I do. I haven't tried running mysqldump without one of those options to see what it would do. My guess is that the backup gets written to whatever directory you were in when you ran the command but that could be wildly wrong. Your file system probably has a command that will help you find the file via its name; in Unix/Linux the 'find' command would do that and in Windows, there is also a 'find' or 'search' feature, depending on which version of Windows you are running, built into the GUI. Your best bet would be to run the backup again using the -r or --result-file option and identify where you want the backup written, then verify that it was actually written to the desired location. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql docs
Hi all!!! in what part of the documentation can i find references to this configure options: --with-example-storage-engine Enable the Example Storage Engine --with-archive-storage-engine Enable the Archive Storage Engine --with-csv-storage-engine Enable the CSV Storage Engine Best Regards Vic. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql docs
At 9:32 -0400 10/28/04, Victor Medina wrote: Hi all!!! in what part of the documentation can i find references to this configure options: --with-example-storage-engine Enable the Example Storage Engine --with-archive-storage-engine Enable the Archive Storage Engine --with-csv-storage-engine Enable the CSV Storage Engine None yet. It's still on the to-do list. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A DB Design issue
Maybe I am just being dense this morning but I am confused why you think that your nodes are different things depending on how many children they have (nodes, groups, entities, ahhh!)... If you have two sets of hierarchies that share the same nodes, you may want to build two trees rather than trying to make one tree pull double duty. Keep each tree in their own table and reference a record or records in the Nodes table from each node in the tree. That way both trees have their own structure and you only have one copy of each node. Perhaps a normal tree and a groups tree. Do your groups even need a tree? Could it be a table of groups and a second table that relates nodes to groups? Like I said, I could have really missed the point and if I did I humbly apologize. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Chris [EMAIL PROTECTED] wrote on 10/27/2004 10:08:46 PM: Hi all, I'm designing a small database, it's essentially a tree-structure. I'm probably going to use a Modified Preorder Tree Traversal (On a side note, how is it different than an Unmodified Preorder Tree Traversal?). Each node will have children etc, and those will have children, etc. But I want to apply data to groups of nodes. So each child will either be an actual entity, or a group of entities, which brings me to my problem. If I have groups in one table, and entities in another, and a child can be either a group or an entity I can't store that 'ID' in the same column. What are my options at approaching this? I've thought about writing so each child is a group, and every child is in a group (even if it's a group of one). That would solve my problem, but it seems like there could be a better solution. Thanks, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysqldump very slow
You won't get any reasonable answers like this. How big is your db? How long does mysqldump actually take? If you tell this, people can tell you if this is like expected or too slow. Good answers depend on good questions. But a hint: You can't expect mysqldump to run in fractions of seconds. Depending on database size, it is quite normal for it to take some minutes. Stefan Am Thursday 28 October 2004 13:48 schrieb Anil Doppalapudi: mysqldump running very slow. what might be the reason Server details === Dell RAM : 4 GB Innodb_buffer_pool_size : 1000MB i am using --quick option in mysqldump Thanks Anil DBA -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql docs
OKI! =) is there any place where i can get some info about them? On Thu, 2004-10-28 at 09:47, Paul DuBois wrote: At 9:32 -0400 10/28/04, Victor Medina wrote: Hi all!!! in what part of the documentation can i find references to this configure options: --with-example-storage-engine Enable the Example Storage Engine --with-archive-storage-engine Enable the Archive Storage Engine --with-csv-storage-engine Enable the CSV Storage Engine None yet. It's still on the to-do list. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
logs
Hi!!! How can i specify the location of the log files in the my.cnf file? (general, error, and bin log) Best Regards Vic -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ho do I backup
On Thursday 28 October 2004 08:22 am, Rhino wrote: - Original Message - From: Barry Zimmerman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 28, 2004 7:24 AM Subject: Ho do I backup Hi there, I am a novice with mysql on the terminal side using shell. Basically I need to back up a database and have it saved locally from my server. I have tried using phpmyadmin but it fails as I think the data is too large. I have tried the following: mysqldump -u admin -p --databases yabbse yabbsebackup.sql It seemed to work, but where can I find the yabbsebackup.sql file? Not very You might want to specify the full path to write your backup. for example: mysqldump -u admin -p yabbse /backups/yabbsetbackup.sql This would put the backup into directory /backups. Substitute the directory of your choice. pgpw0fUc5o6t4.pgp Description: PGP signature
Re: mysql docs
At 10:57 -0400 10/28/04, Victor Medina wrote: OKI! =) is there any place where i can get some info about them? The example engine is just a stub for developers to see how to get started writing a storage engine. If you use ENGINE=EXAMPLE, the table is created, but you can't actually store anything in it. CSV stores data as comma-separated-values lines. I don't know much about the archive engine yet. On Thu, 2004-10-28 at 09:47, Paul DuBois wrote: At 9:32 -0400 10/28/04, Victor Medina wrote: Hi all!!! in what part of the documentation can i find references to this configure options: --with-example-storage-engine Enable the Example Storage Engine --with-archive-storage-engine Enable the Archive Storage Engine --with-csv-storage-engine Enable the CSV Storage Engine None yet. It's still on the to-do list. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: phpMyAdmin and MySQL 4.1
i struggled w/ this for a while, before learning that even though i had mysql 4.1 installed, php4 was being compiled w/ it's own built-in mysql libraries, which are pre-4.1, hence the error. compiling php5 will solve the problem, as it's included mysql libraries are apparently newer, or compiling php4, and pointing configure to your mysql libraries (--with-mysql=/path/to/mysql) will also work. FWIW, I have been using PHP5 for a while now with MySQL 4.1.14-gamma. If I compile PHP5 with MySQL 4.1.14 libraries things go crazy and I get reproducible and strange errors during queries. Compile PHP5 against the old MySQL 4.0.x libraries and everything is ok. Everything works fine and you end up with a working apache/php that works just fine accessing a running 4.1.14 MySQL. I have not tried compiling against 4.1.17 (production) so my comments may be irrelevant but I HTH you in case the problem is not yet fixed and you do decide to try compiling PHP5 against MySql 4.1.x. I did have to change my default collation in phpMyAdmin when I upgraded to 4.1 via the PMA front page. Andrew. --- btb [EMAIL PROTECTED] wrote: On Oct 27, 2004, at 10.26, Schalk Neethling wrote: Greetings After installing the new release of MySQL ( i.e. MySQL 4.1), I know get the following error from phpMyAdmin when using any of the auth types: Error #1251 - Client does not support authentication protocol requested by server; consider upgrading MySQL client. I get this whether I am using cconfig, http or cookie based authentication. Any ideas why this is happening? Is there a config setting in MySQL I should set to support the protocol? -- Kind Regards Schalk Neethling Web Developer.Designer.Programmer.President Volume4.Development.Design.Branding emotionalize.conceptualize.visualize.realize Tel: +27125468436 Fax: +27125468436 email:[EMAIL PROTECTED] Global: www.volume4.com i struggled w/ this for a while, before learning that even though i had mysql 4.1 installed, php4 was being compiled w/ it's own built-in mysql libraries, which are pre-4.1, hence the error. compiling php5 will solve the problem, as it's included mysql libraries are apparently newer, or compiling php4, and pointing configure to your mysql libraries (--with-mysql=/path/to/mysql) will also work. -ben __ Do you Yahoo!? Read only the mail you want - Yahoo! Mail SpamGuard. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Live in Puget Sound?
I am looking for someone that uses mysql and lives in the Puget Sound area. I am willing to pay someone for a little personalized help in getting started with the basics. I know very little about databases, but know I need to have the capability on my web site for many reasons. I would like this person to be willing to come to my home in Des Moines, WA. I will pay travel time also. If interested contact Bill Cory at 253-946-0114
Re: Ho do I backup
On Thu, 28 Oct 2004 12:24:02 +0100, Barry Zimmerman [EMAIL PROTECTED] wrote: I have tried the following: mysqldump -u admin -p --databases yabbse yabbsebackup.sql It seemed to work, but where can I find the yabbsebackup.sql file? Not very experienced with backing up. Barry, The yabbsebackup.sql file will reside in whatever directory you were in when you issued the mysqldump command. The symbol indicates you want the output of mysqldump redirected to a file instead of STDOUT. What you place after the symbol is the filename. If you do not specify a path (i.e /path-to/some-dir/yabbsebackup.sql) , the file will be created in the current working directory. -- Kev. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error making mysql-4.1.5-gamma
I choosed to use mysql-4.1.5-gamma due to the UTF-8 support but failing to compile in on Solaris 8 (Sparc) Earlier I got problem with not finiding gCC and I created a symbolic link to the gcc in the /usr/local/bin and the compilation went on fine till I hit the error below. mkdir .libs gCC -O -DDBUG_OFF -O3 -felide-constructors -fno-exceptions -fno-rtti -mcpu=v8 -Wa -xarch=v8plusa -D_FILE_OFFSET_BITS=64 -DHAVE_CURSES_H -I/export/home/e020568/mysql-4.1.5-gamma/include -DHAVE_RWLOCK_T -o .libs/mysql mysql.o readline.o sql_string.o completion_hash.o ../cmd-line-utils/libedit/libedit.a -lcurses ../libmysql/.libs/libmysqlclient.so -lposix4 -lcrypt -lgen -lsocket -lnsl -lm -lz gCC: mysql.o: No such file or directory gCC: readline.o: No such file or directory gCC: sql_string.o: No such file or directory gCC: completion_hash.o: No such file or directory gCC: language arch=v8plusa not recognized gCC: language arch=v8plusa not recognized *** Error code 1 make: Fatal error: Command failed for target `mysql' Current working directory /export/home/user/mysql-4.1.5-gamma/client *** Error code 1 make: Fatal error: Command failed for target `all-recursive' Current working directory /export/home/user/mysql-4.1.5-gamma ** Error code 1 make: Fatal error: Command failed for target `all' The .lib did get created in the /export/home/user/mysql-4.1.5-gamma/client but is empty. Suggestion appreciated.. Regards Aman RAheja -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
column choices for certain data
Hi... I have a db that I'm writing. It's for a business directory and one of the fields/columns in the table needs to have a list of business types in it (i.e. retail, auto, computer and so on). Since there may be more than one category that a business fits under I was wondering if SET is the best choice for that?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ulimit problem
Hi. Thank you! It looks like you've found a bug: http://bugs.mysql.com/bug.php?id=6309 Seems, you have myisamchk compiled without debug support. See: http://dev.mysql.com/doc/mysql/en/Compiling_for_debugging.html http://dev.mysql.com/doc/mysql/en/MyODBC_Unix_debugging_options.html And I think the best way for you is to download MySQL with enabled debug support. Then try it with --debug, look at the trace, or send it to us. Please tell me, what OS do you use, MySQL version and how was it compiled? Is there any clues at the end of .err file? J S [EMAIL PROTECTED] wrote: That failed with an err27 as well. How do I set the debug flag on myisamchk ? --debug=/tmp/debug doesn't work. JS. Hi. Sounds OK, but don't forget to make a backup. J S [EMAIL PROTECTED] wrote: Thanks for your reply Gleb. I tried running with --safe-recover instead of -qr but that caused some other processes running on the box to hang and coredump so I had to kill it off. I'll try running: myisamchk -qr --tmpdir=/proxydb/mysql/tmp -O sort=16M -O key=16M -O read=1M -O write=1M --sort-index --analyze internet_usage Sound OK? JS. Hi. Usually myisamchk doesn't have the suid bit set, and it seems to be ran as root. See http://dev.mysql.com/doc/mysql/en/Table_maintenance.html Especially http://dev.mysql.com/doc/mysql/en/myisamchk_memory.html J S [EMAIL PROTECTED] wrote: Hi, I've run myisampack to compress a table and am now trying to recreate the index using myisamchk (as root) but I get an error 27. # myisamchk -rq --sort-index --analyze --tmpdir=/proxydb/mysql/tmp internet_usage.MYI - check record delete-chain - recovering (with sort) MyISAM-table 'internet_usage.MYI' Data records: 30972875 - Fixing index 1 myisamchk: Error writing file '/proxydb/mysql/tmp/ST3hTDMa' (Errcode: 27) myisamchk: error: 27 when fixing table MyISAM-table 'internet_usage.MYI' is not fixed because of errors Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag # perror 27 Error code 27: A file cannot be larger than the value set by ulimit. The ulimits are set as follows: default: fsize = 2097151 core = 2097151 cpu = -1 data = 262144 rss = 65536 stack = 65536 nofiles = 2000 root: fsize = -1 core = -1 cpu = -1 data = -1 rss = -1 stack = -1 nofiles = 4000 When I run myisamchk, is it running with root privilidges or as user mysql? By the way this is a large enabled filesystem. Thanks, JS. -- 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] -- 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] -- 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]
mysqld no longer starts after update 4.0 - 4.1
Hi! I searched near and far but could not find the slightest hint about what is causing my MySQL problems. I have a couple of servers running 3.23 and 4.0x just perfect but after updating one machine to 4.1, I can no longer start mysqld. Here is the error message: == # /etc/init.d/mysql start [ERROR] bdb: unable to initialize mutex: Function not implemented [ERROR] bdb: process-private: unable to initialize environment lock: Function not implemented [ERROR] Can't init databases [ERROR] Aborting [NOTE] /usr/sbin/mysqld: Shutdown complete == It is a Debian server which was running MySQL 3.23 perfectly. Now I need one of the new functions in v4.1 so I followed the manual which says I should update to 4.0 first. I did (4.0.21) and ran the script to fix the privileges. The I restarted mysqld -- tested everything and found that it worked perfect. Then I updated to 4.1.5 which brought me to the current situation. Help anybody??? Best regards, Stefan Wolf. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Corrupted relay log
On 10/28/2004 8:07 AM, gerald_clark [EMAIL PROTECTED] wrote: Paul Fierro wrote: One of my slaves stopped running due to a corrupted relay log. The error file shows: Error writing file 'dbs2.log' (errno: 28) Error in Log_event::read_log_event(): 'read error', data_len: 160, event_type: 2 $ perror 28 Error code 28: No space left on device You are out of disk space. At the time of the error this may have been the case, but I have plenty of disk space now. Is there a way to bypass/skip the corrupted relay log file? Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A DB Design issue
Heh, I don't think you're being dense. I can barely understand what I
meant. I'll try to clarify I bit more.
What I have is a sequence of entities (for this example the entities
will be letters). I'm trying to use MySQL to hold the possible
combinations of entities.
The unique entities in a sequence always appear in the same order, but
they have different rules as to when they are allowed to be present.
For example:
The poissble entities/order for this example is ABCDEF
'A' must always begin the sequence, and can only occur once, 'B', if
present, must occur immediately after 'A' and be followed immediately by
'C' and, finally, 'D', if present must come next, then be immediately
follwed by 'EF'.
The BC 'group' is allowed to be repeated indefinitely. So here are some
possible sequences:
A
ABC
ABCDEF
ABCBCBCBCBCBC
ABCBCDEF
And some illegal sequences
BC (A is not present)
ADEFDEF (DEF isn't allowed to be repeated)
ABCB (B muyst be followed by C, always)
So this isn't exactly a tree per se, but I am trying to put those
instructions in a database.
Here is how I think it would look in the DB, I'll use Perl Regex syntax
to show repetition
A{1}
B{0,}
C{1}
D{0,1}
E{1}
F{1}
Heh, I'm not sure if anyone will understand that, but I think it's a lot
clearer in my head now.
If you want any more clarification, I'd be glad to, though it seems like
I'm on the right track now.
Chris
[EMAIL PROTECTED] wrote:
Maybe I am just being dense this morning but I am confused why you think
that your nodes are different things depending on how many children they
have (nodes, groups, entities, ahhh!)...
If you have two sets of hierarchies that share the same nodes, you may
want to build two trees rather than trying to make one tree pull double
duty. Keep each tree in their own table and reference a record or records
in the Nodes table from each node in the tree. That way both trees have
their own structure and you only have one copy of each node. Perhaps a
normal tree and a groups tree. Do your groups even need a tree? Could
it be a table of groups and a second table that relates nodes to groups?
Like I said, I could have really missed the point and if I did I humbly
apologize.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysqldump very slow
Hi Stefan, Sorry for not giving complete details it a very small database around 12 Gb data. But on my other servers which are low in configuration i am getting dump very fast for me it is taking nearly 5 Hrs to compleate dump Thanks Anil DBA -Original Message- From: Stefan Kuhn [mailto:[EMAIL PROTECTED] Sent: Thursday, October 28, 2004 8:13 PM To: [EMAIL PROTECTED] Subject: Re: Mysqldump very slow You won't get any reasonable answers like this. How big is your db? How long does mysqldump actually take? If you tell this, people can tell you if this is like expected or too slow. Good answers depend on good questions. But a hint: You can't expect mysqldump to run in fractions of seconds. Depending on database size, it is quite normal for it to take some minutes. Stefan Am Thursday 28 October 2004 13:48 schrieb Anil Doppalapudi: mysqldump running very slow. what might be the reason Server details === Dell RAM : 4 GB Innodb_buffer_pool_size : 1000MB i am using --quick option in mysqldump Thanks Anil DBA -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- 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]
detailed summary of data, average, min, max
Hi, I have a table of grades like this: title, section, instructor, letter_grade, number_grade, uid With data that would look like this: English, 1, Smith, B, 88, 1 English, 1, Smith, B, 86, 1 English, 1, Smith, B+, 89, 1 Math, 1, Jones, A, 95, 2 Math, 1, Jones, B, 85, 2 Math, 2, Smith, C, 75, 3 Math, 2, Smith, B-, 82, 3 I want a query that will give me something like this: uid, average_grade, A_count, B+_count, B_count, B-_count, C_count 1, 87.67, 0,1, 2,0, 0 2, 90, 1,0, 1,0, 0 3, 78.50,0, 0,1, 1 I can do this is a separate query for each grade, but that makes a lot of little queries. Is there a way to do this in one query? Or am I just going to have to break the average out and do the counts in one query and the average in another? Thanks, bob == Bob Ramsey SYSTEMS ADMINISTRATION AND SYSTEMS PROGRAMMING III MA, Management of Information Systems 2004 MA, English Literature, 1992 ph: 1(319)335-9956 187 Boyd Law Building fax: 1(319)335-9019 University of Iowa College of Law mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113 For Hardware and Software questions, call 5-9124 == -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
strange difference in creating views (mysql 5.0.1)
I'm trying to create a view from two tables address and address_association. So I did the following and expect they are the same: CREATE VIEW associated_address AS select t0.association_id AS association_id, t0.property_dict AS property_dict, t0.type_id AS type_id, t0.owner_id AS owner_id, t0.owner_class_name AS owner_class_name, t0.status_code AS asso_status, t0.flag AS flag, t1.* from address_association t0, address t1 where (t0.address_id = t1.address_id); CREATE VIEW vaa AS select t0.association_id AS association_id, t0.property_dict AS property_dict, t0.type_id AS type_id, t0.owner_id AS owner_id, t0.owner_class_name AS owner_class_name, t0.status_code AS asso_status, t0.flag AS flag, t1.* from address_association t0 left join address t1 on (t0.address_id = t1.address_id); But you see the differences: mysql select count(1) from vaa; +--+ | count(1) | +--+ | 1443 | +--+ 1 row in set (7.30 sec) mysql select count(1) from associated_address; +--+ | count(1) | +--+ | 1441 | +--+ 1 row in set (3.32 sec) I have one row in address_association which address_id value not found in table address. Does this cause the above difference?
Re: mysql 4.1.5 source
Aman Raheja wrote: Latest realease is 4.1.7 in 4.1.x but I am looking for 4.1.5 source download. Pointers will be helpful. Thanks Aman http://downloads.mysql.com/archives.php?p=mysql-4.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: detailed summary of data, average, min, max
Something like SELECT uid, AVG(number_grade) AS average_grade, SUM(IF(letter_grade = 'A', 1, 0)) AS A_count, SUM(IF(letter_grade = 'B+', 1, 0)) AS B+_count, SUM(IF(letter_grade = 'B', 1, 0)) AS B_count, SUM(IF(letter_grade = 'B-', 1, 0)) AS B-_count, SUM(IF(letter_grade = 'C', 1, 0)) AS C_count, FROM grades_table GROUP BY uid; should do. Michael Bob Ramsey wrote: Hi, I have a table of grades like this: title, section, instructor, letter_grade, number_grade, uid With data that would look like this: English, 1, Smith, B, 88, 1 English, 1, Smith, B, 86, 1 English, 1, Smith, B+, 89, 1 Math, 1, Jones, A, 95, 2 Math, 1, Jones, B, 85, 2 Math, 2, Smith, C, 75, 3 Math, 2, Smith, B-, 82, 3 I want a query that will give me something like this: uid, average_grade, A_count, B+_count, B_count, B-_count, C_count 1, 87.67, 0,1, 2,0, 0 2, 90, 1,0, 1,0, 0 3, 78.50,0, 0,1, 1 I can do this is a separate query for each grade, but that makes a lot of little queries. Is there a way to do this in one query? Or am I just going to have to break the average out and do the counts in one query and the average in another? Thanks, bob == Bob Ramsey SYSTEMS ADMINISTRATION AND SYSTEMS PROGRAMMING III MA, Management of Information Systems 2004 MA, English Literature, 1992 ph: 1(319)335-9956 187 Boyd Law Building fax: 1(319)335-9019 University of Iowa College of Law mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113 For Hardware and Software questions, call 5-9124 == -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: go back machine
I'd like to setup an application to store equipment configuration data in a MySql database. The basic setup is that each equipment has a certain number of parameters which may change over time. I'd like to track these changes over time and want to be able to create queries which determine the status a certain date. I guess the easiest approach is to have one column per parameter and simply store the value of all parameters whenever I read up the configuration data using the Equipment Id and Date as keys. However, I'd like to store only the delta information, I.e. data changed between different dates. To give an idea, I need to be able to track around 100 parameters for roughly 1 different equipments so performance is an issue. Assuming now I create the following tables; CREATE TABLE `eq` ( `eqid` int(11) NOT NULL auto_increment, `eqname` char(10) default NULL, PRIMARY KEY (`eqid`) ) CREATE TABLE `eq_params` ( `paramid` int(4) NOT NULL default '0', `paramname` char(10) default NULL, PRIMARY KEY (`paramid`) ) CREATE TABLE `eq_deltalist` ( `eqid` int(4) NOT NULL default '0', `paramid` int(11) NOT NULL default '0', `lastmodified` datetime NOT NULL default '-00-00 00:00:00', `value` double(15,3) default NULL, PRIMARY KEY (`eqid`,`paramid`,`lastmodified`) ) If I define my equipments in eq, the different parameters in eq_params and each change of given parameter in eq_deltalist, how do I query for the valid parameters a certain date (i.e. when the lastmodified date is closest to the date in question) ?? Thankful for any good ideas. BR // Niklas
many-to-many query
Hi, I'm totally new at this so have no idea whether I'm asking for something easy or quite difficult. I am working in MySQL 3.23.58/PHP and am trying to construct a rather complex query. I have three tables: resources (containing resid, descr, title, url), topicdir (containing topicid, resid) and topic (containing topicid, topic, parentid). Each resource is associated with numerous topics. Initially I wanted to get just all the resources associated with a particular topic (in this example #36), so had the following SQL statement: SELECT resources.title, resources.descr, resources.url FROM resources, topic_dir WHERE topic_dir.topic_id = 36 AND topic_dir.res_id = resources.res_id ORDER BY resources.title Now it gets a bit more complicated: I need all resources associated with topic 36 (or whatever) but also all topics with parentid 998 associated with the resources associated with topic 36. Because I'm working in an earlier version of MySQL, I can't use a subquery. Any ideas/solutions/help would be most appreciated! Many thanks, Emily -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innodb log file
Hi, How can i increase the size of the log file and log buffer size? i tried to change it on my.cnf file but it return this error message: InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes InnoDB: than specified in the .cnf file 0 67108864 bytes! 041028 0:10:49 Can't init databases 041028 0:10:49 Aborting thanks in advance Alejandro
many-to-many query
http://dev.mysql.com/doc/mysql/en/JOIN.html -Original Message- From: Emily Lena Jones [mailto:[EMAIL PROTECTED] Sent: Thursday, October 28, 2004 1:05 PM To: [EMAIL PROTECTED] Subject: many-to-many query Hi, I'm totally new at this so have no idea whether I'm asking for something easy or quite difficult. I am working in MySQL 3.23.58/PHP and am trying to construct a rather complex query. I have three tables: resources (containing resid, descr, title, url), topicdir (containing topicid, resid) and topic (containing topicid, topic, parentid). Each resource is associated with numerous topics. Initially I wanted to get just all the resources associated with a particular topic (in this example #36), so had the following SQL statement: SELECT resources.title, resources.descr, resources.url FROM resources, topic_dir WHERE topic_dir.topic_id = 36 AND topic_dir.res_id = resources.res_id ORDER BY resources.title Now it gets a bit more complicated: I need all resources associated with topic 36 (or whatever) but also all topics with parentid 998 associated with the resources associated with topic 36. Because I'm working in an earlier version of MySQL, I can't use a subquery. Any ideas/solutions/help would be most appreciated! Many thanks, Emily -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A DB Design issue
Your system sounds more like BNF (Backus-Naur Form) expression evaluator
than a regular expression evaluator. Both are similar in that you can
specify sequences of things (letters or words or symbols) to appear in
certain orders and in certain quantities. Once a BNF or regex expression
is parsed, it is usually represented internally as a decision tree (I once
had to port a regex library from one language to another) so it sounds
like you want to store that parsed structure in the database, thus saving
the parsing step on repeat uses?
If you look at the source code for a BNF or regex evaluator (several are
open sourced), you should get some excellent clues about how to structure
each node (with one node = one row of data, a node's structure will
closely resemble your table structure). Then after you add to each node
the two or three extra fields you need for a modified preorder traversal
data structure you should be very close to what you will need.
Sorry I can't be more specific but if you understand the needs of your
sequence evaluator and you understand the theory of the MPT tables then
you already have the pieces you need to get this working.
best of luck!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Chris [EMAIL PROTECTED] wrote on 10/28/2004 02:04:34 PM:
Heh, I don't think you're being dense. I can barely understand what I
meant. I'll try to clarify I bit more.
What I have is a sequence of entities (for this example the entities
will be letters). I'm trying to use MySQL to hold the possible
combinations of entities.
The unique entities in a sequence always appear in the same order, but
they have different rules as to when they are allowed to be present.
For example:
The poissble entities/order for this example is ABCDEF
'A' must always begin the sequence, and can only occur once, 'B', if
present, must occur immediately after 'A' and be followed immediately by
'C' and, finally, 'D', if present must come next, then be immediately
follwed by 'EF'.
The BC 'group' is allowed to be repeated indefinitely. So here are some
possible sequences:
A
ABC
ABCDEF
ABCBCBCBCBCBC
ABCBCDEF
And some illegal sequences
BC (A is not present)
ADEFDEF (DEF isn't allowed to be repeated)
ABCB (B muyst be followed by C, always)
So this isn't exactly a tree per se, but I am trying to put those
instructions in a database.
Here is how I think it would look in the DB, I'll use Perl Regex syntax
to show repetition
A{1}
B{0,}
C{1}
D{0,1}
E{1}
F{1}
Heh, I'm not sure if anyone will understand that, but I think it's a lot
clearer in my head now.
If you want any more clarification, I'd be glad to, though it seems like
I'm on the right track now.
Chris
[EMAIL PROTECTED] wrote:
Maybe I am just being dense this morning but I am confused why you
think
that your nodes are different things depending on how many children
they
have (nodes, groups, entities, ahhh!)...
If you have two sets of hierarchies that share the same nodes, you may
want to build two trees rather than trying to make one tree pull double
duty. Keep each tree in their own table and reference a record or
records
in the Nodes table from each node in the tree. That way both trees have
their own structure and you only have one copy of each node. Perhaps a
normal tree and a groups tree. Do your groups even need a tree?
Could
it be a table of groups and a second table that relates nodes to
groups?
Like I said, I could have really missed the point and if I did I humbly
apologize.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: column choices for certain data
Oops, I meant to copy the list on this reply too. Rhino - Original Message - From: Rhino [EMAIL PROTECTED] To: Andy B [EMAIL PROTECTED] Sent: Thursday, October 28, 2004 4:00 PM Subject: Re: column choices for certain data - Original Message - From: Andy B [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 28, 2004 1:15 PM Subject: column choices for certain data Hi... I have a db that I'm writing. It's for a business directory and one of the fields/columns in the table needs to have a list of business types in it (i.e. retail, auto, computer and so on). Since there may be more than one category that a business fits under I was wondering if SET is the best choice for that?? I wouldn't use SET if I were you. I have never used the 'SET' column type in MySQL and had to look it up in the manual to see what it did. However, I've worked with relational databases for 20+ years (mostly DB2) and it doesn't have a 'SET' column type in its repertoire; I've gotten used to doing things without 'SET' so maybe I'm just being stodgy ;-) The chief advantage of 'SET', as far as I can tell from the manual, is that it lets you control the specific values which can be in a column without having to write application lookups to verify that the value you are supplying is one that is valid for the 'SET' column. Therefore, if you had only 3 business types, sole proprietorship, partnership, and corporation, you could put those 3 values in the set and be sure that those are the only 3 values that would ever be allowed in the column. That's fine as far as it goes and is a very useful thing. However, on the negative side, there is a fixed maximum of 64 values in the set. While that may be sufficient for your immediate needs, I don't think you can be certain that it will be sufficient for your long term needs. For example, if this is an eclectic business that combines a lot of lines of business, you may find that it sells groceries, operates a dry cleaner, contains a movie theatre, and umpteen other things all under the same business name. You may find that 64 values isn't enough once you start making the set include all the different functions of the business. The second negative is that I don't think 'SET' is a datatype found in most other databases. Therefore, if you eventually port this table over to another database, you may have to rework the design somewhat to get the same effect, which could be a pain. The third negative is that putting multiple values in a single column of a single row violates Codd's Rules, which are the foundation of all relational databases. Codd is probably rolling in his grave at the mere thought of doing this ;-) Therefore, let me suggest this, which should give you the same benefits without the 64 value limitation while being portable to other databases: store the business type in a separate table, even if there is only one possible value for business type for most rows in your directory. For example, create one table to hold the basic information about your business: create table businesses (registration_number int not null, business_name char(50) not null, business_location char(100) not null, [etc.] primary key(registration_number)); Sample Contents: registration_numberbusiness_namebusiness_location 1 Smitty's123 Main Street 2 Bob's 456 Park Street create table business_types (registration_number, business_type char(20) not null, primary key(registration_number,business_types) foreign key(business_type) references business_types_lookup(business_type))TYPE=InnoDB; Sample Contents: registration_numberbusiness_type 1pool hall 1dry cleaner 2restaurant create table business_types_lookup (business_type char(20) not null, business_type_description char(200) not null, primary key(business_type)); Sample Contents: business_typebusiness_type_description pool hallgambling establishment or other den of iniquity restaurant eating establishment that can serve alcohol Do you see how this works? Every time you add a new business to your database, you add one row to the Businesses table, assigning a registration number to the business and recording company name, location, etc. Then, for each of the possible business types that apply to that business, you add a row to the Business_Types table. For example, Smitty's is both a pool hall and a dry cleaner so you add one row for each business type, linking it back to the Businesses table via the registration number that uniquely identifies one business. Any given business can have as many business types as you like, not just a maximum of 64. The third table is not strictly necessary but it is
Re: strange difference in creating views (mysql 5.0.1)
I suspect that you actually have two records in address_association that don't match up with a record in address. Yes, either a data mismatch or data duplication would be suspected in a case like this. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Elim Qiu [EMAIL PROTECTED] wrote on 10/28/2004 02:12:54 PM: I'm trying to create a view from two tables address and address_association. So I did the following and expect they are the same: CREATE VIEW associated_address AS select t0.association_id AS association_id, t0.property_dict AS property_dict, t0.type_id AS type_id, t0.owner_id AS owner_id, t0.owner_class_name AS owner_class_name, t0.status_code AS asso_status, t0.flag AS flag, t1.* from address_association t0, address t1 where (t0.address_id = t1.address_id); CREATE VIEW vaa AS select t0.association_id AS association_id, t0.property_dict AS property_dict, t0.type_id AS type_id, t0.owner_id AS owner_id, t0.owner_class_name AS owner_class_name, t0.status_code AS asso_status, t0.flag AS flag, t1.* from address_association t0 left join address t1 on (t0.address_id = t1.address_id); But you see the differences: mysql select count(1) from vaa; +--+ | count(1) | +--+ | 1443 | +--+ 1 row in set (7.30 sec) mysql select count(1) from associated_address; +--+ | count(1) | +--+ | 1441 | +--+ 1 row in set (3.32 sec) I have one row in address_association which address_id value not found in table address. Does this cause the above difference?
Re: Innodb log file
Check out this section in the manual on innodb log files. http://dev.mysql.com/doc/mysql/en/Adding_and_removing.html -Eric On Thu, 28 Oct 2004 15:47:27 -0500, Oropeza Querejeta, Alejandro [EMAIL PROTECTED] wrote: Hi, How can i increase the size of the log file and log buffer size? i tried to change it on my.cnf file but it return this error message: InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes InnoDB: than specified in the .cnf file 0 67108864 bytes! 041028 0:10:49 Can't init databases 041028 0:10:49 Aborting thanks in advance Alejandro -- Eric Bergen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: column choices for certain data
Hi... Thanks for the info/help here. I understand everything up to the part where it talks about referencing more than 1 table to each other and the foreign keys and all that stuff... basically I got lost. Not because you explained it wrong but because I haven't got the foggiest clue what keys/table linking is or how it works... Is it possible we can start with a simpler example with linking/keys/foreign keys and stuff so maybe I can follow it easier?? And of course if not valid for the list maybe we can take further talking to private email?? (sorry I'm very new at this stuff)... I want to try and take this 1 step at a time until I get it... let me know how I/we should go on from here. (the other side note) is I eventually have to work this db system into a php driven application (not like it matters on this list but...) anyways let me know how to continue with the matter... tnx for the help... - Original Message - From: Rhino [EMAIL PROTECTED] To: Andy B [EMAIL PROTECTED] Sent: Thursday, October 28, 2004 4:00 PM Subject: Re: column choices for certain data - Original Message - From: Andy B [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 28, 2004 1:15 PM Subject: column choices for certain data Hi... I have a db that I'm writing. It's for a business directory and one of the fields/columns in the table needs to have a list of business types in it (i.e. retail, auto, computer and so on). Since there may be more than one category that a business fits under I was wondering if SET is the best choice for that?? I wouldn't use SET if I were you. I have never used the 'SET' column type in MySQL and had to look it up in the manual to see what it did. However, I've worked with relational databases for 20+ years (mostly DB2) and it doesn't have a 'SET' column type in its repertoire; I've gotten used to doing things without 'SET' so maybe I'm just being stodgy ;-) The chief advantage of 'SET', as far as I can tell from the manual, is that it lets you control the specific values which can be in a column without having to write application lookups to verify that the value you are supplying is one that is valid for the 'SET' column. Therefore, if you had only 3 business types, sole proprietorship, partnership, and corporation, you could put those 3 values in the set and be sure that those are the only 3 values that would ever be allowed in the column. That's fine as far as it goes and is a very useful thing. However, on the negative side, there is a fixed maximum of 64 values in the set. While that may be sufficient for your immediate needs, I don't think you can be certain that it will be sufficient for your long term needs. For example, if this is an eclectic business that combines a lot of lines of business, you may find that it sells groceries, operates a dry cleaner, contains a movie theatre, and umpteen other things all under the same business name. You may find that 64 values isn't enough once you start making the set include all the different functions of the business. The second negative is that I don't think 'SET' is a datatype found in most other databases. Therefore, if you eventually port this table over to another database, you may have to rework the design somewhat to get the same effect, which could be a pain. The third negative is that putting multiple values in a single column of a single row violates Codd's Rules, which are the foundation of all relational databases. Codd is probably rolling in his grave at the mere thought of doing this ;-) Therefore, let me suggest this, which should give you the same benefits without the 64 value limitation while being portable to other databases: store the business type in a separate table, even if there is only one possible value for business type for most rows in your directory. For example, create one table to hold the basic information about your business: create table businesses (registration_number int not null, business_name char(50) not null, business_location char(100) not null, [etc.] primary key(registration_number)); Sample Contents: registration_numberbusiness_namebusiness_location 1 Smitty's123 Main Street 2 Bob's 456 Park Street create table business_types (registration_number, business_type char(20) not null, primary key(registration_number,business_types) foreign key(business_type) references business_types_lookup(business_type))TYPE=InnoDB; Sample Contents: registration_numberbusiness_type 1pool hall 1dry cleaner 2restaurant create table business_types_lookup (business_type char(20) not null, business_type_description char(200) not null, primary key(business_type)); Sample Contents: business_typebusiness_type_description pool hallgambling establishment or other den
Re: many-to-many query
- Original Message - From: Emily Lena Jones [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 28, 2004 4:05 PM Subject: many-to-many query Hi, I'm totally new at this so have no idea whether I'm asking for something easy or quite difficult. I am working in MySQL 3.23.58/PHP and am trying to construct a rather complex query. I have three tables: resources (containing resid, descr, title, url), topicdir (containing topicid, resid) and topic (containing topicid, topic, parentid). Each resource is associated with numerous topics. Initially I wanted to get just all the resources associated with a particular topic (in this example #36), so had the following SQL statement: SELECT resources.title, resources.descr, resources.url FROM resources, topic_dir WHERE topic_dir.topic_id = 36 AND topic_dir.res_id = resources.res_id ORDER BY resources.title Now it gets a bit more complicated: I need all resources associated with topic 36 (or whatever) but also all topics with parentid 998 associated with the resources associated with topic 36. Because I'm working in an earlier version of MySQL, I can't use a subquery. Any ideas/solutions/help would be most appreciated! MySQL is perfectly capable of joining together three or more tables in a single query, so rest assured that this is not a problem. I see that someone else, 'none none', has just pointed you to the article about 'join' in the MySQL manual. However, you may have an issue of database design to confront. I can't tell from your question whether you have actually designed your tables to implement true many-to-many relationships. If you have, I would strongly recommend that you think very hard before proceeding any further. The approach that almost every database professional uses is to break each many-to-many relationship into two one-to-many relationships via an association table. For instance, given a many-to-many relationship between employees and projects - an employee can be working on multiple projects and each project can involve many employees - the normal way to design the tables that describe this relationship is: create table employee (emp_id int not null, lastname char(20) not null, [etc.] primary key(emp_id)) Type=InnoDB; create table project (proj_id char(5) not null, projname char(20) not null, [etc.] primary key(proj_id)) Type=InnoDB; create table emp_proj (emp_id int not null, proj_id char(5) not null, primary key (emp_id, proj_id) foreign key(emp_id) references employee(emp_id), foreign key(proj_id) references project(proj_id)) Type=InnoDB; The resulting tables would look like this when populated with data: Employee --- emp_idlastname... 1Smith 2Jones 3Black 4Green Project proj_idprojname A0001Mortgage System B0002Payroll System C0003Warehouse System D0004Finance System Emp_Proj --- emp_idproj_id 1A0001 2C0003 3A0001 4B0002 2B0002 Therefore, employee 1 is working on Project A0001, employee 2 is working on Projects C0003 and B0002, employee 3 is working on Project A0001, and employee 4 is working on Project B0002. We can also infer that no one is working on Project D0004 because no employees in the Emp_proj table are recorded as being working on that project. If the tables are sufficiently small, you may know the names associated with the employee and project IDs off by heart, in which case you don't need to look at any table other than Emp_proj to determine which employees are on which projects or vice versa. If the volumes of data are too large to memorize the IDs, you can do joins between the Emp_proj, Employee, and Project tables to combine all of this data. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Live in Puget Sound?
Hey come n visit Dhaka + Learn( free) MySQL, have a holiday too.!!! Bill wrote: I am looking for someone that uses mysql and lives in the Puget Sound area. I am willing to pay someone for a little personalized help in getting started with the basics. I know very little about databases, but know I need to have the capability on my web site for many reasons. I would like this person to be willing to come to my home in Des Moines, WA. I will pay travel time also. If interested contact Bill Cory at 253-946-0114 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sub queries
Hi there, I have Mysql 4.1 on my development machine, I have been trying to test out if I am going to be able to do this. What I would like to do is return a one to many resultset but without the duplicated results in the first query. Hows is this going to be possible ? I would like to get all the records out of the second table from a key from the first table. I got this using 4.1.5 mysql select * from shotlist s limit 1 union select * from sources ss where ss.sourceID IN (select sourceID from shotlist s limit 1); ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT IN/ALL/ANY/SOME subquery' I have also notice union joins add one resultset after the other, how do you mix this in, or add that resultset as a column in the row instead of one after the other ? Also I am trying to push for 4.1 to be installed on the servers i build web apps on. When will be a possible date to say that gamma which is practically production quality, to actually say production quality ? heheh. Our systems guy will only trust it if it says that, god only knows that latest versions are always feature rich and bug fixed darn. Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: column choices for certain data
See my remarks interspersed below. Rhino - Original Message - From: Andy B [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 28, 2004 5:09 PM Subject: Re: column choices for certain data Hi... Thanks for the info/help here. No problem; that's what the list is for ;-) I understand everything up to the part where it talks about referencing more than 1 table to each other and the foreign keys and all that stuff... basically I got lost. Not because you explained it wrong but because I haven't got the foggiest clue what keys/table linking is or how it works... Fair enough. I don't know what you know or don't know so I just made an assumption and guessed your experience was more extensive than it is. Is it possible we can start with a simpler example with linking/keys/foreign keys and stuff so maybe I can follow it easier?? And of course if not valid for the list maybe we can take further talking to private email?? (sorry I'm very new at this stuff)... Actually, the concept is not that hard. I'll try to explain it a little more clearly. The whole idea of both the SET datatype and the lookup table I showed you is to limit the values which can be stored in a given column to specific values. If you just defined your business type column as char(50) or something like that, you could put *any* SINGLE value you liked into the column as long as it didn't exceed 50 characters; if you added 'not null' to the column description, you would additionally be forced to put *something* in the column, even if that was a single blank. Other than those limitations though (and possibly some limitations with regards to specific characters, depending on the code page, such as accented letters or whatever), you could supply any SINGLE value you want. In your case, you want to limit the valid values for the business type to a few specific values. Also, you want to be able to store MULTIPLE business types for the same business. Now, I'm not precisely sure what values you are envisioning for this business type but let's say, for argument's sake, that it is a short description of the business, like pool hall or dry cleaner. You may have a slightly different idea of business type in mind; if so, substitute the values that you envision for my examples. Whatever the values you want, you've led me to believe that a given business could have more than one business type so I'm picturing a business that has a pool hall but also takes in dry cleaning as one example. (By the way, if I'm misunderstanding this and you really only want a business type column to contain ONE value for any given row, then you don't really need SET at all. Remember, the idea of SET is that you could store up to 64 values in the same column for a given row, so that you could, in theory, say that Smitty's is a combination pool hall and dry cleaner (plus up to 62 other things if the owner has some additional sidelines, like selling gardening supplies or magazines). Okay then, let's get Codd's objections to this out of the way first. If you built a table using SET and a company had just these two business types, the row would look like this: registration_numberbusiness_namebusiness_locationbusiness_type 1 Smitty's123 Main Street pool hall, dry cleaner If the business had 5 sidelines, the row would look like this: registration_numberbusiness_namebusiness_locationbusiness_type 1 Smitty's123 Main Street pool hall, dry cleaner, gardening supplies, magazines, gun repair [forgive the line wrapping; all 5 values should appear in the same line/row] E.F. Codd, the guy who came up with the theory upon which all relational databases, like DB2 and MySQL, are based, would object strongly to this because his theory said that you should NEVER have more than one value in a column of a given row. The use of SET clearly violates that rule. (That may be why I've never seen it in another relational database, like DB2). Instead, we are going to satisfy Codd's theory by putting the business types in a separate table, the Business_types table. That table is going to have one row for each business type for each business. I've shown you that table earlier; here it is again: registration_numberbusiness_type 1pool hall 1dry cleaner 2restaurant As you can see, it indicates that the business whose registration number is 1 is carrying on two different business types, pool hall and dry cleaner. The business whose registration number if 2 is carrying on only one business type, restaurant. We could stop there and probably satisfy your requirements quite nicely with just the Businesses and Business_types tables. These two tables would let us record any number of businesses in the directory and they would allow us to record any number of
Upgrading MySQL erased all data
Installing MySQL 4.1.7 (upgrading from 4.1.3) on MacOS X erased the contents of /usr/local/mysql/data -- the privs and data of the previous installation. FYI Luckily (and unfortunately) we have a backup of that database from last week. (The guy that did it here in the office is still in a bit of a frenzy though). -s -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading MySQL erased all data
So, do you have a question or are you just bragging? ;-) Rhino - Original Message - From: Steven Roussey [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 28, 2004 7:20 PM Subject: Upgrading MySQL erased all data Installing MySQL 4.1.7 (upgrading from 4.1.3) on MacOS X erased the contents of /usr/local/mysql/data -- the privs and data of the previous installation. FYI Luckily (and unfortunately) we have a backup of that database from last week. (The guy that did it here in the office is still in a bit of a frenzy though). -s -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sub queries
- Original Message - From: electroteque [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 28, 2004 6:33 PM Subject: Sub queries Hi there, I have Mysql 4.1 on my development machine, I have been trying to test out if I am going to be able to do this. What I would like to do is return a one to many resultset but without the duplicated results in the first query. Hows is this going to be possible ? I would like to get all the records out of the second table from a key from the first table. I got this using 4.1.5 mysql select * from shotlist s limit 1 union select * from sources ss where ss.sourceID IN (select sourceID from shotlist s limit 1); ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT IN/ALL/ANY/SOME subquery' The keyword UNION should ensure that all duplicate rows are removed from the combined result set. If you use UNION ALL instead of UNION, the duplicates are left in the result set. I think the reason for the error message you are getting is that you have the 'limit 1' clause in the query twice, once in each select. You *may* be allowed to have the 'limit' clause in the subquery of the second SELECT although I doubt it, based on the text of the error message. More likely, you have to remove the 'limit' clause from the subquery. I think the only other place you can have it is after the last SELECT that is UNIONed together. Something like this: select * from shotlist s union select * from sources ss where ss.sourceID IN (select sourceID from shotlist s limit 1) limit 1; The final 'limit' clause affects the final result set, which is a combination of the result sets from both queries. Unfortunately, I don't have a 4.1.x system to try this on so I am strictly guessing based on my work with DB2. I have also notice union joins add one resultset after the other, how do you mix this in, or add that resultset as a column in the row instead of one after the other ? Also I am trying to push for 4.1 to be installed on the servers i build web apps on. When will be a possible date to say that gamma which is practically production quality, to actually say production quality ? heheh. Our systems guy will only trust it if it says that, god only knows that latest versions are always feature rich and bug fixed darn. I have no idea about this but others will probably have an idea when we can expect gamma code. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dumping Select statement output onto a text file
Hi List, How do I dump the data from console to the text file from the output generated by SELECT statement ? Thanks, Nikhil.
Re: many-to-many query
Quoting Rhino [EMAIL PROTECTED]: I wasn't sure if you understood the concept of association tables so forgive me if I told you things you already knew; I didn't mean to be patronizing. No problem--you weren't patronizing at all, and I did tell you I was new at this! With respect to your queries, it would be a lot easier to help if you showed us a little bit of the data from each table, the queries that you are running, the error messages (or incorrect results) you are getting and the results that you *wanted* to get. Otherwise, it is very hard to envision what you are trying to accomplish. Okay, here's some more detail: what I'm working with is, essentially, a catalog of web resources. For each resource there is a title, a description, and a URL, as well as a primary key. That's the resources table (containing resid, title, description, and url). In addition, the resources are all associated with numerous categories of different types (those are in the topic table, which has the following fields: topicid, topic, parentid, and type). The types of categories included are topic (all of these have a parentid of NULL), subtopic (all of these have a parentid equivalent to the topicid of their parent), resource type (all of these have a parentid of 998), and a few others. (I know this is a little confusing--I've inherited this format, it's there for other reasons). Then there's the topic_dir table, which is my association table linking resources and topic tables. What I'm trying to do is this: get the resource type of all resources with the topicid of 36. If I were working in Access, I would save a query on topicid, and then use that to build the final query. Is there a way to do this in mySQL, or is there a better way to accomplish the same end? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sub queries
electroteque wrote: Also I am trying to push for 4.1 to be installed on the servers i build web apps on. When will be a possible date to say that gamma which is practically production quality, to actually say production quality ? heheh. Our systems guy will only trust it if it says that, god only knows that latest versions are always feature rich and bug fixed darn. It's already production ready, apparently: http://www.mysql.com/news-and-events/press-release/release_2004_32.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
why aren't my PRIMARY KEYs being used?
I've created two temporary tables: CREATE TEMPORARY TABLE `activenodes` ( `id` smallint(6) unsigned NOT NULL default '0', `name` varchar(50) NOT NULL default '', `rsrcc` bigint(21) NOT NULL default '0', PRIMARY KEY (`id`) ); CREATE TEMPORARY TABLE `activelayers` ( `id` int(10) unsigned NOT NULL default '0', `lid` tinyint(3) unsigned NOT NULL default '0', `rsrcc` bigint(21) NOT NULL default '0', PRIMARY KEY (`id`,`lid`) ); I've also got two non-temporary tables: CREATE TABLE `nrsrc` ( `id` smallint(6) unsigned NOT NULL default '0', `rsrc` smallint(6) unsigned NOT NULL default '0', PRIMARY KEY (`id`,`rsrc`), KEY `rsrc` (`rsrc`) ); CREATE TABLE `lrsrc` ( `id` int(10) unsigned NOT NULL default '0', `lid` tinyint(3) unsigned NOT NULL default '0', `rsrc` smallint(6) unsigned NOT NULL default '0', PRIMARY KEY (`id`,`lid`,`rsrc`), KEY `rsrc` (`rsrc`) ); I'm attempting to perform the following join: SELECT activelayers.id,activelayers.lid,activelayers.rsrcc,COUNT(lrsrc.rsrc=nrsrc.rsrc) as matchcount,activenodes.name,activenodes.rsrcc FROM activelayers LEFT JOIN lrsrc ON lrsrc.id=activelayers.id AND lrsrc.lid=activelayers.lid RIGHT JOIN nrsrc ON lrsrc.rsrc=nrsrc.rsrc RIGHT JOIN activenodes ON nrsrc.id=activenodes.id GROUP BY activelayers.id,activelayers.lid,activenodes.id HAVING matchcount=activelayers.rsrcc ORDER BY activelayers.lid DESC; My EXPLAIN tells me that I will not be using either of the primary keys in my temporary tables: ++-+--++---+-+-+-+--+-+ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--++---+-+-+-+--+-+ | 1 | SIMPLE | activenodes | ALL| NULL | NULL|NULL | NULL|3 | Using temporary; Using filesort | | 1 | SIMPLE | nrsrc| ref| PRIMARY | PRIMARY | 2 | sherman.activenodes.id |2 | Using index | | 1 | SIMPLE | activelayers | ALL| NULL | NULL|NULL | NULL|6 | | | 1 | SIMPLE | lrsrc| eq_ref | PRIMARY,rsrc | PRIMARY | 7 | sherman.activelayers.id,sherman.activelayers.lid,sherman.nrsrc.rsrc |1 | Using index | ++-+--++---+-+-+-+--+-+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A DB Design issue
Ok thanks a bunch, I'll take this information and see what I can come up with. Chris [EMAIL PROTECTED] wrote: Your system sounds more like BNF (Backus-Naur Form) expression evaluator than a regular expression evaluator. Both are similar in that you can specify sequences of things (letters or words or symbols) to appear in certain orders and in certain quantities. Once a BNF or regex expression is parsed, it is usually represented internally as a decision tree (I once had to port a regex library from one language to another) so it sounds like you want to store that parsed structure in the database, thus saving the parsing step on repeat uses? If you look at the source code for a BNF or regex evaluator (several are open sourced), you should get some excellent clues about how to structure each node (with one node = one row of data, a node's structure will closely resemble your table structure). Then after you add to each node the two or three extra fields you need for a modified preorder traversal data structure you should be very close to what you will need. Sorry I can't be more specific but if you understand the needs of your sequence evaluator and you understand the theory of the MPT tables then you already have the pieces you need to get this working. best of luck! Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: run mysql service in Linux
Dear Gleb, [EMAIL PROTECTED] mysql-standard-4.0.21-pc-linux-i686]# chown -R root /var/lib/mysql [EMAIL PROTECTED] mysql-standard-4.0.21-pc-linux-i686]# chown -R mysql /var/lib/mysql [EMAIL PROTECTED] mysql-standard-4.0.21-pc-linux-i686]# chgrp -R root /var/lib/mysql [EMAIL PROTECTED] bin]# ls comp_err* mysqlaccess* mysqldump* mysql_setpermission* isamchk* mysqlaccess.conf*mysqldumpslow* mysqlshow* make_sharedlib_distribution* mysqladmin* mysql_explain_log* mysql_tableinfo* make_win_binary_distribution* mysqlbinlog* mysql_find_rows* mysqltest* make_win_src_distribution* mysqlbug*mysql_fix_extensions* mysql_waitpid* msql2mysql*mysqlcheck* mysql_fix_privilege_tables* mysql_zap* myisamchk* mysql_config*mysqlhotcopy* pack_isam* myisam_ftdump* mysql_convert_table_format* mysqlimport* perror* myisamlog* mysqld* mysqlmanager* replace* myisampack*mysqld_multi*mysqlmanagerc* resolveip* my_print_defaults* mysqld_safe* mysqlmanager-pwgen* resolve_stack_dum [EMAIL PROTECTED] bin]# mysqld_safe [1] 12486 [EMAIL PROTECTED] bin]# Starting mysqld daemon with databases from /var/lib/mysql STOPPING server from pid file /var/lib/mysql/server6.cma-cgm.com.pid 041029 08:02:51 mysqld ended - Original Message - From: Gleb Paharenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: run mysql service in Linux Date: Thu, 28 Oct 2004 07:17:38 +0300 Hi. Look at permissions on /var/lib/mysql (ls -l /var/lib/mysql). And, btw, it's not /var/liv, but /var/lib. MySQL should have ability to read this directory. See INSTALL-BINARY in the distribution directory and follow instructions in it exactly. Don't forget about mysql_install_db! Regards. Cecep Rosuludin [EMAIL PROTECTED] wrote: dear All Master of Mysql, I'm new in Mysql, and I'd just move to mysql Linux version..!and i have pro= blem to activate the service. I have installed Mysql linux ver. with this C= ommand tar-zxvf mysql-standard-4.0.21-pc-linux-i686.tar when i try to Configure ./configure ,there is a note that i don't have t= o configure because the mysql file type is Binary..! after that I create a = usr (groupadd mysql and usradd -g mysql mysql) then, i change of owner of data directory chown -R root /var/liv/mysql chown -R mysql /var/liv/mysql chgrp -R mysql /var/liv/mysql then, I try to start the service with this command mysqld_safe \ --user=3Dmysql [EMAIL PROTECTED] bin]# Starting mysqld daemon with databases from /var/lib/mys= ql STOPPING server from pid file /var/lib/mysql/server6.cma-cgm.com.pid 041028 09:10:27 mysqld ended [EMAIL PROTECTED] bin]# ./mysqld start 041028 9:42:55 Warning: Asked for 196608 thread stack, but got 126976 041028 9:42:55 Can't find messagefile '/usr/local/mysql/share/mysql/englis= h/errmsg.sys' 041028 9:42:55 Aborting --=20 ___ Find what you are looking for with the Lycos Yellow Pages http://r.lycos.com/r/yp_emailfooter/http://yellowpages.lycos.com/default.as= p?SRC=3Dlycos10 -- 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] -- ___ Find what you are looking for with the Lycos Yellow Pages http://r.lycos.com/r/yp_emailfooter/http://yellowpages.lycos.com/default.asp?SRC=lycos10 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.1 Crashing upon running mysqld_Safe
Hi all , I am trying to install the latest 4.17-standard Linux release on our server and and it gets caught in an endless loop of crashes. I have read the error logs , and followed the stack trace advice from the docs , but to be honest Im not sure how to interpret what it reports. Here is what resolve_stack_dump says: 0x808af93 func_name__C12Item_sum_std + 3 0x82d6de8 _end + 1123528 0x82c0b95 _end + 1032821 0x807d384 (?) 0x80b9aa0 check_for_max_user_connections__FPCciT0 + 244 0x80e1e2d replace__t13List_iterator1Zt4List1Z4ItemRt4List1Zt4List1Z4Item + 57 0x808c550 val__15Item_func_round + 228 0x82deed4 _end + 1156532 0x8048101 (?) Does anyone know what this means exactly? If it helps , we're running Red Hat 6.2 , Linux 2.2.19-6.2.12smp . I got it running on a similarly configured machine with no problems , so Im kinna stumped. Thanks! Aaron
Re: Sub queries
The keyword UNION should ensure that all duplicate rows are removed from the combined result set. If you use UNION ALL instead of UNION, the duplicates are left in the result set. Yeh right, funny, early versions of 4.0, UNION had in the docs this was only avail in 4.1, i am so sure i read that, and i'm pretty sure I tried union and didnt work and then I go back and it said in the docs it was avail in 4.0 + hehe. I think the reason for the error message you are getting is that you have the 'limit 1' clause in the query twice, once in each select. You *may* be allowed to have the 'limit' clause in the subquery of the second SELECT although I doubt it, based on the text of the error message. More likely, you have to remove the 'limit' clause from the subquery. I think the only other place you can have it is after the last SELECT that is UNIONed together. Something like this: select * from shotlist s union select * from sources ss where ss.sourceID IN (select sourceID from shotlist s limit 1) limit 1; The final 'limit' clause affects the final result set, which is a combination of the result sets from both queries. Ok what I was more after was select * from shotlist s union select * from sources ss where ss.sourceID IN (s.sourceID) so it gets the results of sourceID from the first table, it doesnt seem to like that. I want to prevent programatically having to do a second query and loop within the script hehehe. Unfortunately, I don't have a 4.1.x system to try this on so I am strictly guessing based on my work with DB2. Heh I currently have a DB2 jobby soon, doing jasper reports out of a system that uses db2, whats the syntax like, is it a pain ? I have no idea about this but others will probably have an idea when we can expect gamma code. I dont think this made it to the list from Mat Scales http://www.mysql.com/news-and-events/press-release/release_2004_32.html yippy. Well my binary of 4.1.7 didnt say gamma so ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dumping Select statement output onto a text file
There are several ways to get output to a text file, from the Unix shell you can: mysql -e select * from table database output.txt or if you're looking for more of a log of what happened in the MySQL client, from the MySQL shell: tee output.txt select * from table; Mulley, Nikhil wrote: Hi List, How do I dump the data from console to the text file from the output generated by SELECT statement ? Thanks, Nikhil. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Odd build problem since 4.1.2
Hi all, I had no problem with compiling 4.1.2-alpha (or 4.0.x) and it has been working fine. But for both 4.1.5-gamma and 4.1.7, when I get to make test, all the tests fail. Normally, alias (the first one) fails and the whole thing stops, but using --force, they all fail. I get a log error about not being able to connect to localhost via the socket. What is odd is that when I recompile 4.1.2 on the same partition on the same machine, make test works fine. So sockets and localhost lookup seem to work there. I have installed 4.1.5-gamma on another machine with no problems, with the same configure options. The machine that is failing is a slackware 8.0 linux distribution that has been heavily upgraded, running gcc 3.2.2. I have tried different configure options and upgraded several other applications (bison, notably), but no luck. I have a feeling that I am missing a new dependency somewhere or something glaringly obvious, but after having scoured the manual, list archives, and google for several weeks, still cannot figure out what it might be. There are no configure errors or compile errors to indicate that there is a new requirement missing. Many thanks in advance for any thoughts on what has changed between 4.1.2 and the later versions, or what else might be tripping me. m PS The configure script I am using is for all three versions: _ #!/bin/sh CFLAGS=-O3 -mcpu=pentiumpro CXX=g++ \ CXXFLAGS=-O3 -mcpu=pentiumpro -felide-constructors -fno-exceptions -fno-rtti \ ./configure --prefix=/usr/local/mysql \ --enable-thread-safe-client \ --with-openssl \ --with-extra-charsets=ucs2 \ --with-mysqld-user=mysql \ --enable-assembler \ make make test Also tried using gcc in place of g++. ___ Do you Yahoo!? Express yourself with Y! Messenger! Free. Download now. http://messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql online administration docs equivalent of the book?
Hello, I was wondering if people consider the on line docs at the mysql web site to be equivalent to their books regarding the database administration. Does the book go into more detail, or is all the contents of that book online? Thanks for the clarification, and pointers to good online mysql administration docs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
next autoindex
Hello! I have situation where I need to find the next autoindex of a table - not just max(field) because the next autoindex may not jive with the number created by max(field) Any ideas? Am I making sense? Thanks! R. Whitney Transcend Development Producing the next phase of your internet presence http://xend.net Premium Quality Web Hosting http://hosting.xend.net [EMAIL PROTECTED] 310-943-6498 602-288-5340 The day this country abandons God is the day God will abandon this country Linux is like a wigwam, no windows, no gates, apache inside. - borrowed from Sharon Kimble off of the Fedora mailing list -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with mysqldump
In mysql version 4.1.7 running on Redhat 3ES I get the following error from mysql dump. output $ mysqldump -u root -p govern test.sql Enter password: mysqldump: Got error: 1017: Can't find file: './govern/VT_SY_LANGUAGE.frm' (errno: 24) when using LOCK TABLES /output This file does in fact exist and mysql has permission to access it. I can query this table and lock this table when running mysql client. I've found that people have had similar problems, but I have yet to find a solution. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sub queries
- Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 28, 2004 9:26 PM Subject: Re: Sub queries The keyword UNION should ensure that all duplicate rows are removed from the combined result set. If you use UNION ALL instead of UNION, the duplicates are left in the result set. Yeh right, funny, early versions of 4.0, UNION had in the docs this was only avail in 4.1, i am so sure i read that, and i'm pretty sure I tried union and didnt work and then I go back and it said in the docs it was avail in 4.0 + hehe. I think the reason for the error message you are getting is that you have the 'limit 1' clause in the query twice, once in each select. You *may* be allowed to have the 'limit' clause in the subquery of the second SELECT although I doubt it, based on the text of the error message. More likely, you have to remove the 'limit' clause from the subquery. I think the only other place you can have it is after the last SELECT that is UNIONed together. Something like this: select * from shotlist s union select * from sources ss where ss.sourceID IN (select sourceID from shotlist s limit 1) limit 1; The final 'limit' clause affects the final result set, which is a combination of the result sets from both queries. Ok what I was more after was select * from shotlist s union select * from sources ss where ss.sourceID IN (s.sourceID) so it gets the results of sourceID from the first table, it doesnt seem to like that. I want to prevent programatically having to do a second query and loop within the script hehehe. There are obviously many possible variations of your query; I just stated one that was pretty close to your original query. It's really not clear to me yet what you were trying to do so I just wanted to show you typical syntax. Unfortunately, I don't have a 4.1.x system to try this on so I am strictly guessing based on my work with DB2. Heh I currently have a DB2 jobby soon, doing jasper reports out of a system that uses db2, whats the syntax like, is it a pain ? It really depends on what you already know. I think DB2 is pretty easy to use but 've been using DB2 for 20 years; I don't know what you will think, because I don't know anything about you. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: next autoindex
-- Original message from Richard Whitney [EMAIL PROTECTED]: -- Hello! I have situation where I need to find the next autoindex of a table - not just max(field) because the next autoindex may not jive with the number created by max(field) http://dev.mysql.com/doc/mysql/en/SHOW_TABLE_STATUS.html SHOW TABLE STATUS will return a field called Auto_Increment that holds the next auto increment value. There might be a more elegant way to accomplish this, but I can't remember off the top of my head. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with mysqldump
perror 24 Error code 24: Too many open files Looks like you've hit a system limit. Michael Tom Crimmins wrote: In mysql version 4.1.7 running on Redhat 3ES I get the following error from mysql dump. output $ mysqldump -u root -p govern test.sql Enter password: mysqldump: Got error: 1017: Can't find file: './govern/VT_SY_LANGUAGE.frm' (errno: 24) when using LOCK TABLES /output This file does in fact exist and mysql has permission to access it. I can query this table and lock this table when running mysql client. I've found that people have had similar problems, but I have yet to find a solution. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with mysqldump
-- Original message -- From: Tom Crimmins [EMAIL PROTECTED] output $ mysqldump -u root -p govern test.sql Enter password: mysqldump: Got error: 1017: Can't find file: './govern/VT_SY_LANGUAGE.frm' (errno: 24) when using LOCK TABLES /output perror says 24 is too many open files http://dev.mysql.com/doc/mysql/en/Not_enough_file_handles.html The manual says how to fix the problem. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sub queries
There are obviously many possible variations of your query; I just stated one that was pretty close to your original query. It's really not clear to me yet what you were trying to do so I just wanted to show you typical syntax. Ok sorry ppl I should have given a typical example in my script. qsuedocode : select * from shotlist while row in result select * from sources where sourceID IN (row[sourceID]) while row in result append sources to string here end while output results to template row end while so it would be in a datagrid Title Sources my title heresource1, source2 etc ... If i were to do that in a normal join the rows would duplicate from a 1 to many. It really depends on what you already know. I think DB2 is pretty easy to use but 've been using DB2 for 20 years; I don't know what you will think, because I don't know anything about you. woah 20 years ? you are an SQL veteran then :) I'm still a green grasshopper of 5 years. Ok i'msure you dont know anything about me, and has made me suggest to everyone of a listee profilespage heheheh. Little bit about me, I am a PHP/Mysql/Java/Unix/Flash Actionscript 2 developer fora Tv station in Sydney building business level web applications. Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
rlimit and rlimit64 problems when compiling with ICC 8.1
Hello, I was able to compile mysql 4.0 and 4.1 (static) successfully with intel compiler 8.0 on an IA32 system running redhat enterprise linux 3.0. However when I tried to compile with intel compiler 8.1 with the same options (same as ones used by mysql AB, except i link statically) I kept getting the following errors: mysqld.cc(1912): error: argument of type rlimit * is incompatible with parameter of type const rlimit64 * if (setrlimit(RLIMIT_CORE, rl) global_system_variables.log_warnings) It seems something is wrong between rlimit and rlimit64. Since I'm using an IA32 system, I wonder why it has something to do with rlimit64. I tried to post the problem in the intel forum with no luck. google didn't return any similar stories either. Any suggestions is greatly appreciated. -- Best regards, MQ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
