Re: CREATE FUNCTION
Victoria Reznichenko wrote: Sameh, Sunday, July 14, 2002, 3:09:53 PM, you wrote: SA Can anybody help me figuring how to control granting users SA permissions to created functions in MySQL like we do for SELECT, INSERT, SA ...etc or it is not possible and every one logged in to MySQL could SA simply use them like any other native function? SA Regards Take a look at: http://www.mysql.com/doc/C/R/CREATE_FUNCTION.html You must have INSERT and DELETE privileges on the database 'mysql' to create and drop functions. Victoria, Thanx for your reply. My point is not permiting users to create or drop functions but mine is: 1 - I need to create certain functions say f1, f2, f3, ... fn 2 - I need to authrorize users using them; i.e. not every user has the right to run functions from f1 to fn. But user u1 authorised f1 and f2, user u2 authorised f3 and so on Regards Sameh -- Sameh Attia Senior System Engineer T.E. Data -- __ __ _ _ _/ /_/ /_(_)___ _ / ___/ __ `/ __/ __/ / __ `/ (__ ) /_/ / /_/ /_/ / /_/ / //\__,_/\__/\__/_/\__,_/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
LOAD DATA in LOCAL
Hi, Im just wondering why MySQL ver 3.23.51 is not supporting LOAD DATA INFILE syntax, I downgrade my server to the older one which is MySQL ver 3.23.44, is there any bug about using it in the latest version? R.B.Roa Traffic Management Engineer PhilCom Corporation Tel. No.(Office) (088) 858-1028 (Home) (088) 858-8889 Mobile No. (63) (919-3085267) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Foreign key bug in InnoDB?
Hi, I'm using MySQL-Max-4.0.1-alpha on WinNT4SP6 with InnoDB as default table type. I have a database creation script like this: //Section A CREATE TABLE AParents ( Id int unsigned not null auto_increment, Parent int unsigned not null, primary key (Id), index Parent (Parent) ) TYPE = InnoDB; CREATE TABLE AChildren ( Id int unsigned not null auto_increment, Parent int unsigned not null, primary key (Id), index Parent (Parent), foreign key (Parent) references AParents (Id) ) TYPE = InnoDB; //Section B CREATE TABLE BParents ( Id int unsigned not null auto_increment, Parent int unsigned not null, primary key (Id), index Parent (Parent) ) TYPE = InnoDB; CREATE TABLE BChildren ( Id varchar(13) not null default '', Parent int unsigned not null, primary key (Id), index Parent (Parent), foreign key (Parent) references BParents (Id) ) TYPE = InnoDB; When I run the section A and B in any order, MySQL crashes and InnoDB tablespaces go wrong and they cannot be repaired (as I restart MySQL). The crash appears when I create the second table with a foreign key constraint. Do I do something wrong or this is a real bug in InnoDB? Thx, Dani -- http://www.mailbox.hu - Mert levelezni kell... - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
query
Hi, I want to know one thing about MYSQl, Why does this software always give the unix-socket problem when compiled and installed. I am installing on redhat 7.1. Is there a remedy to this or this will continue forever. Any help will be appreciated. Thanks __ Do You Yahoo!? Yahoo! Autos - Get free new car price quotes http://autos.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: LOAD DATA in LOCAL
Is there any problem in my.cnf file? [mysqld] log-bin server-id=165 skip-bdb skip-name-resolve skip-innodb flush port=3306 socket=/tmp/mysql.sock set-variable = key_buffer_size=16M set-variable = max_allowed_packet=1M set-variable = flush_time=300 I only use MyISAM...LOAD DATA INFILE syntax I used in MySQL ver 3.23.44 in working fine...and my first suspect about this is the LIBMYSQL.DLL, maybe they added some feature to MySQL ver 3.23.51 which affect the LOAD syntax...I got an error when I execute the command...it says that its not supported... R.B.Roa Traffic Management Engineer PhilCom Corporation Tel. No.(Office) (088) 858-1028 (Home) (088) 858-8889 Mobile No. (63) (919-3085267) -Original Message- From: Jan Peuker [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, July 16, 2002 3:15 PM To: [EMAIL PROTECTED] Subject:Re: LOAD DATA in LOCAL Hi , check this: In MySQL 3.23.49 and MySQL 4.0.2 LOCAL will only work if you have not started mysqld with --local-infile=0 or if you have not enabled your client to support LOCAL. See section 4.2.4 Security issues with LOAD DATA LOCAL. maybe that's what you are looking for. see: http://www.mysql.com/doc/L/O/LOAD_DATA.html and: http://www.mysql.com/doc/L/O/LOAD_DATA_LOCAL.html just my 0,02 jan - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, July 16, 2002 8:55 AM Subject: LOAD DATA in LOCAL Hi, Im just wondering why MySQL ver 3.23.51 is not supporting LOAD DATA INFILE syntax, I downgrade my server to the older one which is MySQL ver 3.23.44, is there any bug about using it in the latest version? R.B.Roa Traffic Management Engineer PhilCom Corporation Tel. No. (Office) (088) 858-1028 (Home) (088) 858-8889 Mobile No. (63) (919-3085267) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: LOAD DATA in LOCAL
On Tuesday, 16. July 2002 08:55, [EMAIL PROTECTED] wrote: Hi, Before posting, please check: http://www.mysql.com/manual.php (the manual) http://www.mysql.com/doc/L/O/LOAD_DATA_LOCAL.html Regards Georg - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql wil lnot load
On Tuesday, 16. July 2002 07:11, Aaron Axelsen wrote: Hi, 020715 23:40:08 mysqld started 020715 23:40:08 /usr/local/mysql/libexec/mysqld: Can't create/write to file '/usr/local/mysql/var/Alpha.pid' (Errcode: 13) 020715 23:40:08 /usr/local/mysql/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 020715 23:40:08 /usr/local/mysql/libexec/mysqld: Error on delete of '/usr/local/mysql/var/Alpha.pid' (Errcode: 13) 020715 23:40:08 mysqld ended any suggestions? perror 13 == Error code 13: Permission denied So you have to change the file privileges, or you should run mysqld under another user. Regards Georg - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Weighted average
Nope, the SQL query I suggested earlier is incorrect because it biases the display by AVG of votes already. You do need to divide the average by all votes, as suggested by Mark. In what environment do you want to use this Ian? Can it not be done with two queries...if it is in some programming language for instance? In which case you dont need the temporary variable of totalVotes in Mark's example.. Shanx - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: LOAD DATA in LOCAL
I got iti wasn't able to add the local-infile option in my server configthanks for the help R.B.Roa Traffic Management Engineer PhilCom Corporation Tel. No.(Office) (088) 858-1028 (Home) (088) 858-8889 Mobile No. (63) (919-3085267) -Original Message- From: Jan Peuker [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, July 16, 2002 4:02 PM To: [EMAIL PROTECTED] Subject:Re: LOAD DATA in LOCAL Hi, * I am not a professional, but I think there is no error in your config. The statement runs on my .49. Change in .50 was - Fixed binary builds to use --enable-local-infile. (this affects nt versions, which means your dll!) Nothing on .51. * I think you get the message: The used command is not allowed with this MySQL version like in 4.2.4. Security Issues Bulletin? Maybe you have to connect to your server using mysql --local-infile. I guess... * Do you use just load data infil 'some.txt' into mytable; or are there any other commands? please refer to: http://www.mysql.com/doc/T/O/TODO_future.html * Have you tried mysqlimport already? just my 0,01 ;) jan - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, July 16, 2002 9:41 AM Subject: RE: LOAD DATA in LOCAL Is there any problem in my.cnf file? [mysqld] log-bin server-id=165 skip-bdb skip-name-resolve skip-innodb flush port=3306 socket=/tmp/mysql.sock set-variable = key_buffer_size=16M set-variable = max_allowed_packet=1M set-variable = flush_time=300 I only use MyISAM...LOAD DATA INFILE syntax I used in MySQL ver 3.23.44 in working fine...and my first suspect about this is the LIBMYSQL.DLL, maybe they added some feature to MySQL ver 3.23.51 which affect the LOAD syntax...I got an error when I execute the command...it says that its not supported... R.B.Roa Traffic Management Engineer PhilCom Corporation Tel. No. (Office) (088) 858-1028 (Home) (088) 858-8889 Mobile No. (63) (919-3085267) -Original Message- From: Jan Peuker [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, July 16, 2002 3:15 PM To: [EMAIL PROTECTED] Subject: Re: LOAD DATA in LOCAL Hi , check this: In MySQL 3.23.49 and MySQL 4.0.2 LOCAL will only work if you have not started mysqld with --local-infile=0 or if you have not enabled your client to support LOCAL. See section 4.2.4 Security issues with LOAD DATA LOCAL. maybe that's what you are looking for. see: http://www.mysql.com/doc/L/O/LOAD_DATA.html and: http://www.mysql.com/doc/L/O/LOAD_DATA_LOCAL.html just my 0,02 jan - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, July 16, 2002 8:55 AM Subject: LOAD DATA in LOCAL Hi, Im just wondering why MySQL ver 3.23.51 is not supporting LOAD DATA INFILE syntax, I downgrade my server to the older one which is MySQL ver 3.23.44, is there any bug about using it in the latest version? R.B.Roa Traffic Management Engineer PhilCom Corporation Tel. No. (Office) (088) 858-1028 (Home) (088) 858-8889 Mobile No. (63) (919-3085267) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Performance Monitoring/Counters
Hi all, Which tools would you recommend to performance monitoring of a MySQL database server? I'm thinking of the following: System: CPU utilization, disk I/O. RDBMS: buffer cache hit ratio, transactions/sec, connections. Yours -- Lasse Laursen [EMAIL PROTECTED] - Systems Developer NetGroup A/S, St. Kongensgade 40H, DK-1264 København K, Denmark Phone: +45 3370 1526 - Fax: +45 3313 0066 - Web: www.netgroup.dk - We don't surf the net, we make the waves. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Performance Monitoring/Counters
Hi, perhaps Mytop will be good for you ... http://jeremy.zawodny.com/mysql/mytop/ David - Original Message - From: Lasse Laursen [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, July 16, 2002 10:56 AM Subject: Performance Monitoring/Counters Hi all, Which tools would you recommend to performance monitoring of a MySQL database server? I'm thinking of the following: System: CPU utilization, disk I/O. RDBMS: buffer cache hit ratio, transactions/sec, connections. Yours -- Lasse Laursen [EMAIL PROTECTED] - Systems Developer NetGroup A/S, St. Kongensgade 40H, DK-1264 København K, Denmark Phone: +45 3370 1526 - Fax: +45 3313 0066 - Web: www.netgroup.dk - We don't surf the net, we make the waves. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Foreign key bug in InnoDB?
Daniel, are you using a non-latin1 character set? Have you looked in the error log mysql.err? What, if anything, did mysqld-max print when it crashed? From the manual http://www.innodb.com/ibman.html#InnoDB_history: ... MySQL/InnoDB-3.23.49, February 17, 2002 ... Fixed a bug: if one defined a non-latin1 character set as the default character set, then definition of foreign key constraints could fail in an assertion failure in dict0crea.c, reporting an internal error 17. ... Please upgrade to 4.0.2. Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com - Original Message - From: Kiss Daniel [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Tuesday, July 16, 2002 10:12 AM Subject: Foreign key bug in InnoDB? Hi, I'm using MySQL-Max-4.0.1-alpha on WinNT4SP6 with InnoDB as default table type. I have a database creation script like this: //Section A CREATE TABLE AParents ( Id int unsigned not null auto_increment, Parent int unsigned not null, primary key (Id), index Parent (Parent) ) TYPE = InnoDB; CREATE TABLE AChildren ( Id int unsigned not null auto_increment, Parent int unsigned not null, primary key (Id), index Parent (Parent), foreign key (Parent) references AParents (Id) ) TYPE = InnoDB; //Section B CREATE TABLE BParents ( Id int unsigned not null auto_increment, Parent int unsigned not null, primary key (Id), index Parent (Parent) ) TYPE = InnoDB; CREATE TABLE BChildren ( Id varchar(13) not null default '', Parent int unsigned not null, primary key (Id), index Parent (Parent), foreign key (Parent) references BParents (Id) ) TYPE = InnoDB; When I run the section A and B in any order, MySQL crashes and InnoDB tablespaces go wrong and they cannot be repaired (as I restart MySQL). The crash appears when I create the second table with a foreign key constraint. Do I do something wrong or this is a real bug in InnoDB? Thx, Dani -- http://www.mailbox.hu - Mert levelezni kell... - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How can I make and execute .sql file
Jenny Christy wrote: Hello All, Can anybody tell me the query or procedure by which i can generate the .sql file. So that I dont need to write the query again and again on prompt. Just by executing the .sql file(or script language), I will able to create table, insert the data on table, update the data..means whatever operation i need i can write on it. Plz help me to make it. Rgds, jenny rtfm @ http://www.mysql.com/doc/B/a/Batch_Commands.html r - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How can I make and execute .sql file
Jenny, Tuesday, July 16, 2002, 9:09:30 AM, you wrote: JC Can anybody tell me the query or procedure by which i JC can generate the .sql file. So that I dont need to JC write the query again and again on prompt. JC Just by executing the .sql file(or script language), I JC will able to create table, insert the data on table, JC update the data..means whatever operation i need i can JC write on it. Create file which contains your SQL statements, f.e: CREATE TABLE test( id int primary key ); INSERT INTO test VALUES (1),(2),(3); SELECT * FROM test; and then run mysql in batch mode: mysql database_name test.sql Look at: http://www.mysql.com/doc/B/a/Batch_mode.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MyODBC Error
Nitesh, Tuesday, July 16, 2002, 3:46:39 AM, you wrote: ND I have my MySql 3.23 running, I installed MyODBC in my Win98 PC and created a DSN for MySql. ND When I use MS Access to connect to MySql I get an Error saying Host 192.168.1.103 is not allowed to connect to this MySql Server. ND I also tried connecting using telnet 192.168.1.109 3306 I get the same error. You don't have permissions to connect from host 192.168.1.103. Take a look at: http://www.mysql.com/doc/G/R/GRANT.html http://www.mysql.com/doc/P/r/Privileges.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [ error on start mysql ]
Elby, Tuesday, July 16, 2002, 3:58:15 AM, you wrote: EV i installed MySql -Max 3.23.51 on Winodows 2000, but when I try initiate EV it appears the following error: EVMySql service cant be initialized EVSystem error 1067 EVThe process finished suddenly EV Some help please? Run mysqld with --standalone --console options, not as a service You can see more details about errors in the output. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Foreign key bug in InnoDB?
Kiss, Tuesday, July 16, 2002, 10:11:45 AM, you wrote: KD I'm using MySQL-Max-4.0.1-alpha on WinNT4SP6 with InnoDB as default KD table type. KD I have a database creation script like this: [skip] KD When I run the section A and B in any order, MySQL crashes and InnoDB KD tablespaces go wrong and they cannot be repaired (as I restart MySQL). KD The crash appears when I create the second table with a foreign key KD constraint. KD Do I do something wrong or this is a real bug in InnoDB? Do you use other than latin1 default character set? If so, this bug was fixed in 3.23.49: http://www.innodb.com/ibman.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: pulling information from 2 tables based on information from firstselect
Tim Luoma wrote: newbie warning I'm trying to write my first shell script using MySQL. I've checked Welling Thomson and Google but I think I'm searching the wrong keywords or something. The shell script will be non-interactive, but interactively this is what I would do: mysql select * from specialdays where month=6 and day=5; +---+-+-+-++ | month | day | who | uid | type | +---+-+-+-++ | 6 | 5 | Homer Burns | 8 | Birthday | +---+-+-+-++ Then I take the UID and search the 'names' table to get the corresponding email address mysql select email from names where uid=8; +--+ | email| +--+ | [EMAIL PROTECTED] | +--+ Now what I would really like to get is something that looks like this: 6/5 Homer Burns [EMAIL PROTECTED] Birthday Try: SELECT concat(s.month,'/',s.day) AS date, s.who, n.email, s.type FROM names n LEFT JOIN specialdays s USING (uid) WHERE month = 6 AND day = 5 ; I'd appreciate any tips on how to proceed I'm stumped. TjL - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Ralf Narozny SPLENDID Internet GmbH Co KG Skandinaviendamm 212, 24109 Kiel, Germany fon: +49 431 660 97 0, fax: +49 431 660 97 20 mailto:[EMAIL PROTECTED], http://www.splendid.de - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysql 4.0.1/4.0.2 Max Allowed Packet
I am currently trying to insert images which are roughly around 140Mb each into my database using the data type longblob. The problem I seem to be having it that I cannot set the max_allowed_packet to go over 67107840. In my /etc/my.cnf I have the following line: set-variable= max_allowed_packet=256M I have also tried changing this value lower but it seems to make no difference. Are they any other variables which also need to be increased which may affect max_allowed_packet? below is the output from show variables; +-+- --+ | Variable_name | Value | +-+- --+ | back_log| 50 | | basedir | /usr/local/mysql/ | | binlog_cache_size | 32768 | | character_set | latin1 | | character_sets | latin1 dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 | | concurrent_insert | OFF | | connect_timeout | 5 | | datadir | /usr/local/mysql/var/ | | delay_key_write | OFF | | delayed_insert_limit| 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | flush | OFF | | flush_time | 0 | | ft_min_word_len | 4 | | ft_max_word_len | 254 | | ft_max_word_len_for_sort| 20 | | ft_boolean_syntax | + -()~*:| | | have_bdb| NO | | have_innodb | YES | | have_isam | YES | | have_raid | NO | | have_symlink| YES | | have_openssl| NO | | have_query_cache| YES | | init_file | | | innodb_additional_mem_pool_size | 1048576 | | innodb_buffer_pool_size | 8388608 | | innodb_data_file_path | ibdata1:10M:autoextend | | innodb_data_home_dir| | | innodb_file_io_threads | 4 | | innodb_force_recovery | 0 | | innodb_thread_concurrency | 8 | | innodb_flush_log_at_trx_commit | OFF | | innodb_fast_shutdown| ON | | innodb_flush_method | | | innodb_lock_wait_timeout| 50 | | innodb_log_arch_dir | | | innodb_log_archive | OFF | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size| 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_mirrored_log_groups | 1 | | interactive_timeout | 28800 | | join_buffer_size| 131072 | | key_buffer_size | 268431360 | | language| /usr/local/mysql/share/mysql/english/ | | large_files_support | ON | | locked_in_memory| OFF | | log | OFF | | log_update | OFF | log_bin | ON | | log_slave_updates | OFF | | log_slow_queries| OFF | | long_query_time | 10 | | low_priority_updates| OFF | | lower_case_table_names | 0 | | max_allowed_packet | 67107840 | | max_binlog_cache_size | 4294967295 | | max_binlog_size | 1073741824 | | max_connections | 100 | | max_connect_errors | 10 | | max_delayed_threads | 20 | | max_heap_table_size | 134216704 | | max_join_size | 4294967295 | | max_sort_length | 1024 | | max_user_connections| 0 | | max_tmp_tables | 32 | | max_write_lock_count| 4294967295 | | myisam_bulk_insert_tree_size| 8388608 | | myisam_max_extra_sort_file_size | 256 | | myisam_max_sort_file_size | 2047 | | myisam_recover_options | OFF | | myisam_sort_buffer_size | 134217728 | | net_buffer_length | 1047552 | | net_read_timeout| 30 | | net_retry_count | 10 | | net_write_timeout | 60 | | open_files_limit| 0 | | pid_file| /usr/local/mysql/var/hostname.pid | | port| 3306 | | protocol_version| 10 | | record_buffer | 8384512 | | record_rnd_buffer | 8384512 | | rpl_recovery_rank | 0 | | query_buffer_size | 0 | | query_cache_limit | 1048576 | | query_cache_size
Re: Re: Foreign key bug in InnoDB?
Heikki, Yes, I'm using Hungarian character-set as default. I've checked the mysql.err, and it contains nothing important. The full content of mysql.err right after starting mysqld-max, runnig the script and crash: --- MySql: ready for connections --- Maybe, because MySQL crashed before it could write in the log file. Anyway, the Hungarian sort order is not proper. And I think it never will be the way of using those charset conf files. It's much more difficult than just comparing characters. Sometimes you have to compare two chars, or there is some chars that have the same value althought they are different. :-) Will it be realy good any time??? Thx, niel -- http://www.mailbox.hu - Mert levelezni kell... - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
innodb problem
I am a new user of mysql I installed 3.23.51 on my nt this is my my.ini files. [mysqld] innodb_data_home_dir = innodb_data_file_path = ibdata1:30M:autoextend default-table-type=innodb [WinMySQLAdmin] Server=C:/mysql/bin/mysqld-max-nt.exe When I use the winmysqladmin it report in HAVE_INNODB disables; When I create a table mysql create if always with myisam format. Which is my error ? Massimo Petrini c/o Omt spa Via Ferrero 67/a 10090 Cascine Vica (TO) Tel.+39 011 9505334 Fax +39 011 9575474 E-mail [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqldump question
On Tue, 2002-07-16 at 02:31, Bill Bernat wrote: Question: is there anything I need to be aware of when writing dump files to a local directory for my user, I'm having the following problem. 1. I create a directory in my own home directory (linux, red hat 7.2) ~/dumps and give it 777 permissions 2. I run /usr/local/mysql/bin/mysqldump -uroot -ppass --tab ~/dumps dbname table1 table2 3. The .sql file for table1 gets created, correctly, in the dumps directory fine. 4. mysqldump pukes when trying to write the .txt file: /usr/local/mysql/bin/mysqldump: Got error: 1: Can't create/write to file '/home/bbernat/dumps/customer_relations_database.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE' QUESTION: What have I done wrong and how can I fix it? Hi, I was curious about your problem and decided to try it. As user root (in the filesystem, redhat7.2), i created the dir ~/dumps with 777 permissions and have run root@cartman:~# mysqldump -uroot -p --tab ~/dumps/ test (root mysqluser has FILE permissions). It gave me the same error. Then i tried with a common filesystem user (dsoares): dsoares@cartman:~$ mysqldump -uroot -p --tab ~/dumps/ test And it worked! I went to see my /root permissions and it was: dsoares@cartman:~$ ll -d /root/ drwxr-x--- 31 root root 4096 Jul 16 11:09 /root/ So the problem was there! mysql user doesn't have permissions to access /root directory. Maybe this is your problem. (also as root i tried with /tmp/dumps directory, 777 permissions, and it worked.) -- Diana Soares - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
DB design tool for mySQL
Can anyone suggest free database desiging tool that supports mySQL. Case studio 2.9 demo version supports mySQL but it does not allow more than 6 Tables to be designed. Moreover demo version does not give option for referential integrity constraints. i.e. scripts are generated without Foreign keys. Regards, Hassan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Referential Integrity in mySQL
Hello all, I am having a problem in referential Integrity in mySQL. I have created a parent table which have deptno from 1-10. When i insert a row in child table that has deptno as foreign key it accept a value 11 which is actually not present in parent table. Can anybody tell the solution for this. regards, Adnan Yaqoob. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Mysql wil lnot load
The instructions where followed from the website exaactly and all the file premissions are correct, but I still get the following errors in ther log file: 020716 05:23:59 mysqld started ^G/usr/local/mysql/libexec/mysqld: File './Alpha-bin.1' not found (Errcode: 13) 020716 5:23:59 Could not use Alpha-bin for logging (error 13) 020716 5:23:59 /usr/local/mysql/libexec/mysqld: Can't create/write to file '/usr/local/mysql/var/Alpha.pid' (Errcode: 13) 020716 5:23:59 /usr/local/mysql/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 020716 5:23:59 /usr/local/mysql/libexec/mysqld: Error on delete of '/usr/local/mysql/var/Alpha.pid' (Errcode: 13) 020716 05:23:59 mysqld ended --- Aaron Axelsen AIM: AAAK2 Email: [EMAIL PROTECTED] URL: www.amadmax.com It said, Insert disk #3, but only two will fit! One picture is worth 128K words. -Original Message- From: Georg Richter [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 16, 2002 3:29 AM To: Aaron Axelsen; [EMAIL PROTECTED] Subject: Re: Mysql wil lnot load On Tuesday, 16. July 2002 07:11, Aaron Axelsen wrote: Hi, 020715 23:40:08 mysqld started 020715 23:40:08 /usr/local/mysql/libexec/mysqld: Can't create/write to file '/usr/local/mysql/var/Alpha.pid' (Errcode: 13) 020715 23:40:08 /usr/local/mysql/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 020715 23:40:08 /usr/local/mysql/libexec/mysqld: Error on delete of '/usr/local/mysql/var/Alpha.pid' (Errcode: 13) 020715 23:40:08 mysqld ended any suggestions? perror 13 == Error code 13: Permission denied So you have to change the file privileges, or you should run mysqld under another user. Regards Georg - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Mysql wil lnot load
Error code 13: Permission denied Check if the user that runs mysql (usually user mysql) has permissions on those directories. Usually, the ownership is mysql. An example (i also have the mysql group): [root@cartman mysql]# pwd /var/lib/mysql [root@cartman mysql]# ll -dh . .. *.* mysql drwxr-xr-x 21 mysqlmysql Jul 1 22:44 . drwxr-xr-x 15 root root Apr 1 15:55 .. -rw-rw1 mysqlmysql May 23 17:54 cartman-bin.009 -rw-rw1 mysqlmysql May 23 18:15 cartman-bin.010 -rw-rw1 mysqlmysql May 23 18:20 cartman-bin.011 -rw-rw1 mysqlmysql May 23 18:15 cartman-bin.index -rw-rw-r--1 mysqlmysql Jul 8 14:43 cartman.err -rw-rw-r--1 mysqlmysql Jan 16 20:10 cartman.log -rw-rw1 mysqlmysql Jun 27 03:05 cartman.pid -rw-rw-r--1 mysqlmysql Jul 16 11:10 cartman-slow.log -rw-rw1 mysqlmysql Jun 27 03:05 master.info drwx--x--x2 mysqlmysql Dec 5 2001 mysql srwxrwxrwx1 mysqlmysql Jun 27 03:05 mysql.sock [root@cartman mysql]# On Tue, 2002-07-16 at 11:25, Aaron Axelsen wrote: The instructions where followed from the website exaactly and all the file premissions are correct, but I still get the following errors in ther log file: 020716 05:23:59 mysqld started ^G/usr/local/mysql/libexec/mysqld: File './Alpha-bin.1' not found (Errcode: 13) 020716 5:23:59 Could not use Alpha-bin for logging (error 13) 020716 5:23:59 /usr/local/mysql/libexec/mysqld: Can't create/write to file '/usr/local/mysql/var/Alpha.pid' (Errcode: 13) 020716 5:23:59 /usr/local/mysql/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 020716 5:23:59 /usr/local/mysql/libexec/mysqld: Error on delete of '/usr/local/mysql/var/Alpha.pid' (Errcode: 13) 020716 05:23:59 mysqld ended --- Aaron Axelsen AIM: AAAK2 Email: [EMAIL PROTECTED] URL: www.amadmax.com It said, Insert disk #3, but only two will fit! One picture is worth 128K words. -Original Message- From: Georg Richter [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 16, 2002 3:29 AM To: Aaron Axelsen; [EMAIL PROTECTED] Subject: Re: Mysql wil lnot load On Tuesday, 16. July 2002 07:11, Aaron Axelsen wrote: Hi, 020715 23:40:08 mysqld started 020715 23:40:08 /usr/local/mysql/libexec/mysqld: Can't create/write to file '/usr/local/mysql/var/Alpha.pid' (Errcode: 13) 020715 23:40:08 /usr/local/mysql/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 020715 23:40:08 /usr/local/mysql/libexec/mysqld: Error on delete of '/usr/local/mysql/var/Alpha.pid' (Errcode: 13) 020715 23:40:08 mysqld ended any suggestions? perror 13 == Error code 13: Permission denied So you have to change the file privileges, or you should run mysqld under another user. Regards Georg - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Diana Soares Websolut - Soluções Internet Email: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: CREATE FUNCTION
Sameh, Tuesday, July 16, 2002, 9:26:27 AM, you wrote: SA Victoria Reznichenko wrote: SA SA SA Can anybody help me figuring how to control granting users SA SA permissions to created functions in MySQL like we do for SELECT, SA INSERT, SA SA ...etc or it is not possible and every one logged in to MySQL could SA SA simply use them like any other native function? SA SA Regards SA SA Take a look at: SA http://www.mysql.com/doc/C/R/CREATE_FUNCTION.html SA SA You must have INSERT and DELETE privileges on the database 'mysql' to SA create and drop functions. SA Victoria, SA Thanx for your reply. My point is not permiting users to create or SA drop functions but mine is: SA 1 - I need to create certain functions say f1, f2, f3, ... fn SA 2 - I need to authrorize users using them; i.e. not every user has SA the right to run functions from f1 to fn. But user u1 authorised f1 and SA f2, user u2 authorised f3 and so on No, unfortunatly you can't specify privileges for functions :( -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: RE: Mysql wil lnot load
Aaron, Tuesday, July 16, 2002, 1:25:23 PM, you wrote: AA The instructions where followed from the website exaactly and all the AA file premissions are correct, but I still get the following errors in AA ther log file: AA 020716 05:23:59 mysqld started AA ^G/usr/local/mysql/libexec/mysqld: File './Alpha-bin.1' not found AA (Errcode: 13) AA 020716 5:23:59 Could not use Alpha-bin for logging (error 13) AA 020716 5:23:59 /usr/local/mysql/libexec/mysqld: Can't create/write to AA file '/usr/local/mysql/var/Alpha.pid' (Errcode: 13) AA 020716 5:23:59 /usr/local/mysql/libexec/mysqld: Can't find file: AA './mysql/host.frm' (errno: 13) AA 020716 5:23:59 /usr/local/mysql/libexec/mysqld: Error on delete of AA '/usr/local/mysql/var/Alpha.pid' (Errcode: 13) AA 020716 05:23:59 mysqld ended MySQL must be owner of the data dir, must have permissions to create socket file. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: sql: create duplicate record
Aman, Monday, July 15, 2002, 8:35:16 PM, you wrote: AR I want to write sql query to duplicate a row. It has a PRI key called ID. AR The MySQL database looks like AR IDNameAddress AR 345 Aman42, sb AR 346 AR 347... AR I want to duplicate the ID=345. AR Any suggestions ? You can have duplicate values in the columns that specified as PRIMARY KEY or UNIQUE. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Referential Integrity in mySQL
Adnan, Tuesday, July 16, 2002, 2:18:22 PM, you wrote: AY I am having a problem in referential Integrity in mySQL. I have AY created a parent table which have deptno from 1-10. When i insert a row in AY child table that has deptno as foreign key it accept a value 11 which is AY actually not present in parent table. AY Can anybody tell the solution for this. Are you sure that your tables are InnoDB? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: How to display the warning message
Xinguang, Monday, July 15, 2002, 6:01:35 PM, you wrote: I have been trying to use the LOAD data command to load the data XS instead of using the sql insert into table. It works fine but I have XS got some warnings, how can I see these warnings, is there a log for XS it, how can I see these warnings, is there a log for it? No, MySQL doesn't store warning messagew anywhere. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: RE: Interesting
Nick, Monday, July 15, 2002, 5:58:31 PM, you wrote: NM I'm using version 3.23.53 on Win 2k. NM The same things happens to me. NM I've also noticed that if you don't specify a WHERE clause and you have a NM Auto-incrementing ID field, it is reset to zero and the next record you NM create starts at 1 again. Because MySQL simply re-create table in this case, that is why auto_increment field starts at 1 again. NM Surely this is wrong as well? In Other RDBMS's after DELETE * Table. The ID NM field still remembers the last ID so the next (first) record after NM performing a delete all, will increment from the last ID. NM BTW, I've just installed MySQL-Front. It got me up and running in no time! NM :-) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: innodb problem
Massimo, Tuesday, July 16, 2002, 1:02:15 PM, you wrote: MP I am a new user of mysql MP I installed 3.23.51 on my nt MP this is my my.ini files. MP [mysqld] MP innodb_data_home_dir = MP innodb_data_file_path = ibdata1:30M:autoextend MP default-table-type=innodb MP [WinMySQLAdmin] MP Server=C:/mysql/bin/mysqld-max-nt.exe MP When I use the winmysqladmin it report in HAVE_INNODB disables; MP When I create a table mysql create if always with myisam format. MP Which is my error ? Look at the manual: If you specify innodb_data_home_dir as an empty string, then you can give absolute paths to your data files in innodb_data_file_path. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Weighted average
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Ian M. Evans wrote: | snip | SELECT @totalVotes:=COUNT(titleid) FROM movieratings | /snip | What version of MySQL are you using? I just tried this on MySQL-4.0.2 and it worked. If your version of MySQL doesn't support variables (they go pretty far back, 3.23.x supports them), then just issue a similar query and store the result yourself... SELECT COUNT(titleid) as totalVotes FROM movieratings Then use whatever programming language you are using to retrieve that value, and then stuff it back into the second query. -Mark - -- For technical support contracts, visit https://order.mysql.com/?ref=mmma ~__ ___ ___ __ ~ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] ~ / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java ~ /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ~___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6-2 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE9NA1OtvXNTca6JD8RAhO+AJ44Ai7bAPdBDPmcdAUjriBvq/o9FQCgtLyD PQpYyRaCfcZHF5bUNJSazEk= =GagT -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Calculations in mysql
[snip] sqltext2 = Select sum(orders.amount * products.price) AS total from orders,products where orders.product=products.productid AND cluster = ' cluster 'and orderdate ' printdate '; [/snip] If the query conditioals are the same for both queries you can just include sum(orders.amount * products.price) AS total in your SELECT statement. If the conditionals are not, you would have to modify. An example; SELECT orderID, product, quantity, price, SUM(quantity * price) AS Extended FROM orders, products WHERE orderID etc., etc, etc... Your returned items would be orderID, product, quantity, price, and Extended. HTH! Jay - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: How can I make and execute .sql file
[snip] Can anybody tell me the query or procedure by which i can generate the .sql file. So that I dont need to write the query again and again on prompt. [/snip] Create a text file containing your query commands and then save it as filename.sql (calling it whatever you wish of course) and then call that text file from the command line. HTH! Jay sql, mysql, query Watch out for the idiot behind me * * Want to meet other PHP developers * * in your area? Check out: * * http://php.meetup.com/* * No developer is an island ... * * - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: sql: create duplicate record
AR I want to write sql query to duplicate a row. It has a PRI key called ID. AR The MySQL database looks like AR IDNameAddress AR 345 Aman42, sb AR 346 AR 347... AR I want to duplicate the ID=345. AR Any suggestions ? VR You can have duplicate values in the columns that specified as PRIMARY VR KEY or UNIQUE. Oops! Sorry, my typo :( I mean you can't have duplicate values .. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: --bind-address
Chris, Tuesday, July 16, 2002, 2:13:57 AM, you wrote: CK Can MySQL bind to two or more IP addresses? Nope. CK i.e. --bind-address 127.0.0.1 --bind-address x.x.x.x --bind-address y.y.y.y CK ? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Load problems with 3.23.51 ( same here )
Hi list, Same thing here, mysql 3.23.51 works well during about 2 hours then suddently load average grow up to 200 and more ... Load average is less than 4 with 3.23.49. We're using binary tar.gz non max versions from Mysql.com OS : Linux Red hat 7.2 kernel : 2.4.10 1 Go RAM 2 * Intel 1 Ghz During the load pic i've run a ps -eo pid,tt,user,fname,tmout,f,wchan and that's an extract of the output : [...] 29309 ?mysqlmysqld - 040 rt_sigsuspend 29347 ?mysqlmysqld - 040 rt_sigsuspend 29390 ?mysqlmysqld - 040 rt_sigsuspend 29391 ?mysqlmysqld - 040 - 29417 ?mysqlmysqld - 040 rt_sigsuspend 29418 ?mysqlmysqld - 040 rt_sigsuspend 29420 ?mysqlmysqld - 040 rt_sigsuspend 29423 ?mysqlmysqld - 040 rt_sigsuspend 29424 ?mysqlmysqld - 040 rt_sigsuspend 29425 ?mysqlmysqld - 040 rt_sigsuspend 29428 ?mysqlmysqld - 040 rt_sigsuspend 29432 ?mysqlmysqld - 040 - 29433 ?mysqlmysqld - 040 rt_sigsuspend 29434 ?mysqlmysqld - 040 rt_sigsuspend 29436 ?mysqlmysqld - 040 rt_sigsuspend 29439 ?mysqlmysqld - 040 rt_sigsuspend 29442 ?mysqlmysqld - 040 rt_sigsuspend 29443 ?mysqlmysqld - 040 rt_sigsuspend 29445 ?mysqlmysqld - 040 rt_sigsuspend 29448 ?mysqlmysqld - 040 rt_sigsuspend 29450 ?mysqlmysqld - 040 rt_sigsuspend 29451 ?mysqlmysqld - 040 rt_sigsuspend 29453 ?mysqlmysqld - 040 rt_sigsuspend 29454 ?mysqlmysqld - 040 rt_sigsuspend 29455 ?mysqlmysqld - 040 rt_sigsuspend 29458 ?mysqlmysqld - 040 rt_sigsuspend 29462 ?mysqlmysqld - 040 rt_sigsuspend 29463 ?mysqlmysqld - 040 rt_sigsuspend 29464 ?mysqlmysqld - 040 rt_sigsuspend 29465 ?mysqlmysqld - 040 rt_sigsuspend 29468 ?mysqlmysqld - 040 rt_sigsuspend [...] So we come back to 3.23.49 wich have a glibc bug and memory eating bug but we can't upgrade to 3.23.51 :( Thanks again. David NB : this is the my.cnf : [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking skip-name-resolve set-variable= key_buffer=128M set-variable= back_log=100 set-variable= record_buffer=1M set-variable= sort_buffer=1M set-variable= max_allowed_packet=1M set-variable= thread_stack=128K set-variable= max_connections=700 set-variable= max_connect_errors=100 set-variable= table_cache=256 set-variable= net_read_timeout=180 set-variable= net_write_timeout=180 set-variable= wait_timeout=3600 # Start logging # log [...] - Original Message - From: Steven Roussey [EMAIL PROTECTED] To: 'Mysql' [EMAIL PROTECTED] Sent: Friday, July 12, 2002 6:17 PM Subject: RE: Load problems with 3.23.51 Just a note: I tried MySQL 4.0.2 and it works fine. Seems to be only 3.23.51 built by MySQL itself that has the issue. Releases before, and now a release after (albeit a 4.0.x version) work fine. Sincerely, Steven Roussey http://Network54.com/?pp=e I have MySQL 3.23.47 running on our sever. I skipped 48 through 50 and tried 51. No dice. It does not handle load, CPU and the load average go through the roof. I'm using Red Hat Linux 7.2 and the official mysql binaries. It appears to be slow to connect, causing 0.5 to 1.0 second delay on connection. Using persistent connections from PHP does not make much of a difference. I thought it might be the hostname lookup changes so I chose skip-grant-tables. This doesn't actually skip the hostname lookup though and had no effect. Most queries are shorter than 1 second so this problem causes catastrophic problems by making queries last a multiple times longer, which make the number of concurrent queries jump exponentially. This is a bad thing. And sadly makes 3.23.51 unusable. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MyISAM v. InnoDB
Of the data? Or of the queries? Not the data. Just your query, the output from explain and a create table statement. Query: SELECT DISTINCT cards_crypt.* FROM user_haves, cards_crypt WHERE cards_crypt.name LIKE %harrod% AND user_haves.card_name = cards_crypt.name AND user_haves.total_have 0 AND user_haves.user != joe_bob_briggs Explain: *** 1. row *** table: user_haves type: ALL possible_keys: card_name,total_have key: NULL key_len: NULL ref: NULL rows: 79370 Extra: where used; Using temporary *** 2. row *** table: cards_crypt type: eq_ref possible_keys: name key: name key_len: 255 ref: user_haves.card_name rows: 1 Extra: where used 2 rows in set (0.00 sec) Create Table data: CREATE TABLE user_haves ( card_name varchar(100) NOT NULL default '', total_have int(11) unsigned NOT NULL default '0', user varchar(50) NOT NULL default '', available int(11) unsigned NOT NULL default '0', record_num int(11) unsigned NOT NULL auto_increment, PRIMARY KEY (record_num), KEY user (user), KEY card_name (card_name), KEY total_have (total_have), KEY available (available) ) TYPE=ISAM PACK_KEYS=1; CREATE TABLE cards_crypt ( record_num int(11) unsigned NOT NULL auto_increment, name varchar(255) NOT NULL default '', clan varchar(100) NOT NULL default '', capacity int(10) NOT NULL default '0', disciplines varchar(100) default '', superior varchar(100) default '', inferior varchar(100) default '', title varchar(100) default '', bonus_penalty text, release_set varchar(100) NOT NULL default '', rarity varchar(50) default '', artist varchar(100) default '', have int(10) default '0', need int(10) default '0', notes text, alternate_name varchar(255) default '', card_monger_url varchar(255) default '', PRIMARY KEY (record_num), UNIQUE KEY name (name), KEY clan (clan), KEY capacity (capacity), KEY release_set (release_set) ) TYPE=ISAM PACK_KEYS=1; If you need any more information to provide assistance, please let me know and I'll post it. Chris MySQL - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL security
Hello I have a question for all you MySQL people out there We are a group of people planning to make a small open source ERP/accounting/finance program. We have earlier used MySQL to great satisfaction in other areas and would like to use it here. My question is: Do you think MySQL is secure enough to keep peoples bookkeeping in? is it safe enough to use without risk of losing important data? Is it necessary to do anything to make it secure? (eg. use of transactions/backup/power failure security) Any comments are welcome :) /Mathias - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
3.23.51 bug ? ( was RE: Load problems with 3.23.51 )
Hi list, Same thing here, mysql 3.23.51 works well during about 2 hours then suddently load average grow up to 200 and more ... Load average is less than 4 with 3.23.49. We're using binary tar.gz non max versions from Mysql.com OS : Linux Red hat 7.2 kernel : 2.4.10 1 Go RAM 2 * Intel 1 Ghz During the load pic i've run a ps -eo pid,tt,user,fname,tmout,f,wchan and that's an extract of the output : [...] 29309 ?mysqlmysqld - 040 rt_sigsuspend 29347 ?mysqlmysqld - 040 rt_sigsuspend 29390 ?mysqlmysqld - 040 rt_sigsuspend 29391 ?mysqlmysqld - 040 - 29417 ?mysqlmysqld - 040 rt_sigsuspend 29418 ?mysqlmysqld - 040 rt_sigsuspend 29420 ?mysqlmysqld - 040 rt_sigsuspend 29423 ?mysqlmysqld - 040 rt_sigsuspend 29424 ?mysqlmysqld - 040 rt_sigsuspend 29425 ?mysqlmysqld - 040 rt_sigsuspend 29428 ?mysqlmysqld - 040 rt_sigsuspend 29432 ?mysqlmysqld - 040 - 29433 ?mysqlmysqld - 040 rt_sigsuspend 29434 ?mysqlmysqld - 040 rt_sigsuspend 29436 ?mysqlmysqld - 040 rt_sigsuspend 29439 ?mysqlmysqld - 040 rt_sigsuspend 29442 ?mysqlmysqld - 040 rt_sigsuspend 29443 ?mysqlmysqld - 040 rt_sigsuspend 29445 ?mysqlmysqld - 040 rt_sigsuspend 29448 ?mysqlmysqld - 040 rt_sigsuspend 29450 ?mysqlmysqld - 040 rt_sigsuspend 29451 ?mysqlmysqld - 040 rt_sigsuspend 29453 ?mysqlmysqld - 040 rt_sigsuspend 29454 ?mysqlmysqld - 040 rt_sigsuspend 29455 ?mysqlmysqld - 040 rt_sigsuspend 29458 ?mysqlmysqld - 040 rt_sigsuspend 29462 ?mysqlmysqld - 040 rt_sigsuspend 29463 ?mysqlmysqld - 040 rt_sigsuspend 29464 ?mysqlmysqld - 040 rt_sigsuspend 29465 ?mysqlmysqld - 040 rt_sigsuspend 29468 ?mysqlmysqld - 040 rt_sigsuspend [...] So we come back to 3.23.49 wich have a glibc bug and memory eating bug but we can't upgrade to 3.23.51 :( Thanks again. David NB : this is the my.cnf : [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking skip-name-resolve set-variable= key_buffer=128M set-variable= back_log=100 set-variable= record_buffer=1M set-variable= sort_buffer=1M set-variable= max_allowed_packet=1M set-variable= thread_stack=128K set-variable= max_connections=700 set-variable= max_connect_errors=100 set-variable= table_cache=256 set-variable= net_read_timeout=180 set-variable= net_write_timeout=180 set-variable= wait_timeout=3600 # Start logging # log [...] - Original Message - From: Steven Roussey [EMAIL PROTECTED] To: 'Mysql' [EMAIL PROTECTED] Sent: Friday, July 12, 2002 6:17 PM Subject: RE: Load problems with 3.23.51 Just a note: I tried MySQL 4.0.2 and it works fine. Seems to be only 3.23.51 built by MySQL itself that has the issue. Releases before, and now a release after (albeit a 4.0.x version) work fine. Sincerely, Steven Roussey http://Network54.com/?pp=e I have MySQL 3.23.47 running on our sever. I skipped 48 through 50 and tried 51. No dice. It does not handle load, CPU and the load average go through the roof. I'm using Red Hat Linux 7.2 and the official mysql binaries. It appears to be slow to connect, causing 0.5 to 1.0 second delay on connection. Using persistent connections from PHP does not make much of a difference. I thought it might be the hostname lookup changes so I chose skip-grant-tables. This doesn't actually skip the hostname lookup though and had no effect. Most queries are shorter than 1 second so this problem causes catastrophic problems by making queries last a multiple times longer, which make the number of concurrent queries jump exponentially. This is a bad thing. And sadly makes 3.23.51 unusable. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: query
Difficult to answer without knowing what the unix-socket problem is. p shah wrote: Hi, I want to know one thing about MYSQl, Why does this software always give the unix-socket problem when compiled and installed. I am installing on redhat 7.1. Is there a remedy to this or this will continue forever. Any help will be appreciated. Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Use MySQL and Visual Basic 6.0
Hi, This is not related with your issue but if you want to do serious development, don't use Windows 98 in any of its versions. It is going to give a lot of problems. Bye and Good Luck. --- Matthew Scarrow [EMAIL PROTECTED] wrote: I've built a whole program with vb6 and mysql database back end. Never had any problems. I used the mysql odbc for windows to access the database. What are you using? Matthew Scarrow ComIT Solutions Inc. www.comit.ca Phone: 519-442-0100 Fax: 519-442-0429 -Original Message- From: Carlos Rovetto [mailto:[EMAIL PROTECTED]] Sent: Sunday, July 14, 2002 9:18 PM To: [EMAIL PROTECTED] Subject: Use MySQL and Visual Basic 6.0 Hi. I am begining using MySQL with Visual Basic 6.0 in Windows 98 first edition. Some times i to receive this message but i don't know why. Error on rename of '.\video\products.MYI' to '.\video\#sql2-fffa6015-3.MYI' (Errcode: 13) The SQL instruction is: alter table products add (unitprice double(7,2), precio2 double(7,2)) I don't know if use MySQL with VB and Windows is good idea. Thanks Carlos _ MSN. Más Útil cada Día. http://www.msn.es/intmap/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Do You Yahoo!? Yahoo! Autos - Get free new car price quotes http://autos.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
UPDATE command..
I have two collumns in my mysql database like this +--++ |id|domain | +--++ |user@domain1com |domain1.com | |[EMAIL PROTECTED] |domain1.com | |[EMAIL PROTECTED] |domain1.com | +--++ in a table users. How do I construct an sql update statement like update users set id =. where domain = domain1.com so that the id column becomes +--+ |id| +--+ |[EMAIL PROTECTED] | |[EMAIL PROTECTED] | |[EMAIL PROTECTED] | +--+ thanks. Patrick J Okui - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Calculating rows in a result set
Hi everybody, is there a way to calculate the number of rows retrieved by a mysql query, without actually performing it? I want to make a paginated result like in most search engines, and for that I need to display the number of pages the result would have, but I do not want to actually retrieve all the rows to be able to count them, because this is a loss of performance, in particular if the result set retrieves 10 rows and 100 people make a search; this would end in 1 x 100 rows just to say how many pages the result will have, I think there must be a better way to do it, isn't it? thanks to everybody in advance W.D.F. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
problems with gr-secured kernel
Hi! I found out, that the MySQL-daemon /usr/sbin/mysqld (Version 4.02, the rpm version), installed under RedHat 7.3 (i386) doesn't like the option Randomize mmap() base from the Grsecurity kernel patch suite... Greetings, tobias gablunsky __ Telefon +49 (30) 59 00 69-0 Telefax +49 (30) 59 00 69-99 COM.BOX Internet Service GmbH Potsdamer Strasse 96 - D-10785 Berlin Amtsgericht Berlin-Charlottenburg - HRB 71171 Geschäftsführer: Lutz Treutler http://www.combox.de/ - mailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL security
We use MySQL to store financial data and have never had a problem. The things you mentioned (transactions/backup/power failure security) should be used with ANY database that stores critical information that cannot be lost. No database is completely bullet proof. Regular backups stored at an offsite location, UPS power supply protection, transactions, and LOTS of testing before going live are the best things you can do. Regards, Daniel Lamb - Original Message - From: Mathias Bertelsen [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, July 16, 2002 8:51 AM Subject: MySQL security Hello I have a question for all you MySQL people out there We are a group of people planning to make a small open source ERP/accounting/finance program. We have earlier used MySQL to great satisfaction in other areas and would like to use it here. My question is: Do you think MySQL is secure enough to keep peoples bookkeeping in? is it safe enough to use without risk of losing important data? Is it necessary to do anything to make it secure? (eg. use of transactions/backup/power failure security) Any comments are welcome :) /Mathias - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Beginner's question
Try adding a primary key to the table. alter table webpage add autoid INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT This will help the database functions search through the table quicker. I think another area where the query is getting slowed down is in the translation of the Java. Java is a translated language meaning it gets compiled when it is run unlike other languages like c++ so you will see more delay when dealing with java as the interface between you and the database. Maybe running the query in the mysqlclient program and see what the response time is compared to jsp. If you see a big difference in response then it has something to do with the middle man. I can't think of anything else that will help you. Matthew Scarrow ComIT Solutions Inc. www.comit.ca Phone: 519-442-0100 Fax: 519-442-0429 -Original Message- From: Kyle and Beth Kelly [mailto:[EMAIL PROTECTED]] Sent: Monday, July 15, 2002 8:00 PM To: [EMAIL PROTECTED] Subject: Re: Beginner's question Question 1. I am using Java Server Pages on my search engine. Here is a link (http://66.190.209.122/arachnid/search.jsp) Question 2. The query is created on the fly, but the general format is: select w1.webpage, ... sum(w1.count and wn.count) as count from word as w1, ...word as wn where (w1.word='something' and wn.word='somethingelse' and w1.webpage=w2.webpage ... and wn-1.webpage=wn.webpage) group by webpage order by 2 desc Question 3. I dont know what full text indexing means. Question 4. The table structure is the following: table webpage ( webpagevarchar(166) not null, hostvarchar(166) not null, file varchar(255) not null, levelnumeric not null, title varchar(166), descriptionvarchar(255), pagetext not null, daydate not null, primary key (host, file), index(title,webpage,host), ); - Original Message - From: Matthew Scarrow [EMAIL PROTECTED] To: Kyle and Beth Kelly [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, July 14, 2002 7:35 PM Subject: RE: Beginner's question Can you provide more information like are you using cgi to get the code and what language. What's your query look like? Are you full text indexing your text field? Do you have an index on the table? What's the table structure look like? Can you send a sample record? How many records in the table? I query on text fields that hold html code all the time and use c++ cgi. It takes less then a second to return my query. So probably going to need more info. Thanks. Matthew Scarrow ComIT Solutions Inc. www.comit.ca Phone: 519-442-0100 Fax: 519-442-0429 -Original Message- From: Kyle and Beth Kelly [mailto:[EMAIL PROTECTED]] Sent: Sunday, July 14, 2002 7:16 PM To: [EMAIL PROTECTED] Subject: Beginner's question I am writting a search engine using Mysql. I am trying to return a text object value ( which contains all of the html), but it is taking about 10 seconds to return each text value matching my query. Is there any way that a text value can be optimized? www.cs.uno.edu/~kkelly - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: UPDATE command..
Patrick J Okui wrote: I have two collumns in my mysql database like this +--++ |id|domain | +--++ |user@domain1com |domain1.com | |[EMAIL PROTECTED] |domain1.com | |[EMAIL PROTECTED] |domain1.com | +--++ in a table users. How do I construct an sql update statement like update users set id =. where domain = domain1.com so that the id column becomes +--+ |id| +--+ |[EMAIL PROTECTED] | |[EMAIL PROTECTED] | |[EMAIL PROTECTED] | +--+ How about: UPDATE table SET id = concat(left(id, locate(id, '@')), 'domain2.com') WHERE domain = domain1.com; -- Ralf Narozny SPLENDID Internet GmbH Co KG Skandinaviendamm 212, 24109 Kiel, Germany fon: +49 431 660 97 0, fax: +49 431 660 97 20 mailto:[EMAIL PROTECTED], http://www.splendid.de - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Calculating rows in a result set
Not without actually performing SOME kind of query. You could select count(*) from... and insert the rest of your query there. This would give you the count. Then store this in a session variable (PHP) for subsequent pages. Most of the time though, I just put a NEXT button. ADODB has some great pagination tools to make this stuff easy. =C= * * Cal Evans * The Virtual CIO * http://www.calevans.com * -Original Message- From: Walter D. Funk [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 16, 2002 9:02 AM To: [EMAIL PROTECTED] Subject: Calculating rows in a result set Hi everybody, is there a way to calculate the number of rows retrieved by a mysql query, without actually performing it? I want to make a paginated result like in most search engines, and for that I need to display the number of pages the result would have, but I do not want to actually retrieve all the rows to be able to count them, because this is a loss of performance, in particular if the result set retrieves 10 rows and 100 people make a search; this would end in 1 x 100 rows just to say how many pages the result will have, I think there must be a better way to do it, isn't it? thanks to everybody in advance W.D.F. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Calculating rows in a result set
Walter D. Funk wrote: Hi everybody, is there a way to calculate the number of rows retrieved by a mysql query, without actually performing it? I want to make a paginated result like in most search engines, and for that I need to display the number of pages the result would have, but I do not want to actually retrieve all the rows to be able to count them, because this is a loss of performance, in particular if the result set retrieves 10 rows and 100 people make a search; this would end in 1 x 100 rows just to say how many pages the result will have, I think there must be a better way to do it, isn't it? No way! Should the database guess on it? ;-) SELECT count(*) FROM ... WHERE ...; Just like your query looks like is the only way. -- Ralf Narozny SPLENDID Internet GmbH Co KG Skandinaviendamm 212, 24109 Kiel, Germany fon: +49 431 660 97 0, fax: +49 431 660 97 20 mailto:[EMAIL PROTECTED], http://www.splendid.de - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: UPDATE command..
update table name set id = replace(id, '1', '2'); hope this helps anil - Original Message - From: Patrick J Okui [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, July 16, 2002 9:47 AM Subject: UPDATE command.. I have two collumns in my mysql database like this +--++ |id|domain | +--++ |user@domain1com |domain1.com | |[EMAIL PROTECTED] |domain1.com | |[EMAIL PROTECTED] |domain1.com | +--++ in a table users. How do I construct an sql update statement like update users set id =. where domain = domain1.com so that the id column becomes +--+ |id| +--+ |[EMAIL PROTECTED] | |[EMAIL PROTECTED] | |[EMAIL PROTECTED] | +--+ thanks. Patrick J Okui - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: UPDATE command..
well, #1, I would revision my data model. since user and domain are 2
distinct pieces of information, I would suggest that you consider pulling
them apart and placing them in separate fields.
#2
update users set is = concat(left(id,position('@',id)),'domain2.com') where
domain = domain1.com
or something along those lines. Check out
http://www.mysql.com/doc/S/t/String_functions.html
=C=
*
* Cal Evans
* The Virtual CIO
* http://www.calevans.com
*
-Original Message-
From: Patrick J Okui [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 16, 2002 8:48 AM
To: [EMAIL PROTECTED]
Subject: UPDATE command..
I have two collumns in my mysql database like this
+--++
|id|domain |
+--++
|user@domain1com |domain1.com |
|[EMAIL PROTECTED] |domain1.com |
|[EMAIL PROTECTED] |domain1.com |
+--++
in a table users.
How do I construct an sql update statement like
update users set id =. where domain = domain1.com
so that the id column becomes
+--+
|id|
+--+
|[EMAIL PROTECTED] |
|[EMAIL PROTECTED] |
|[EMAIL PROTECTED] |
+--+
thanks.
Patrick J Okui
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re:UPDATE command..
Patrick J Okui [EMAIL PROTECTED] wrote on 16.07.2002 15:47:50: I have two collumns in my mysql database like this +--++ |id|domain | +--++ |user@domain1com |domain1.com | |[EMAIL PROTECTED] |domain1.com | |[EMAIL PROTECTED] |domain1.com | +--++ in a table users. How do I construct an sql update statement like update users set id =. where domain = domain1.com so that the id column becomes +--+ |id| +--+ |[EMAIL PROTECTED] | |[EMAIL PROTECTED] | |[EMAIL PROTECTED] | +--+ thanks. Patrick J Okui Hi Patrick, try UPDATE users SET id=REPLACE(id,'domain1','domain2') WHERE domain='domain1. com'; Bye Oliver -- Good programming is 40% experience, 30% skill, 20% RTFM, 10% caffeine, and 5% attention to detail. Oliver Six, CEO CAHOS GmbH, Cimbernstr. 51, Germany 81377 Muenchen Phone +49 89 71 01 93 41, Fax +49 89 71 01 93 42 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
version
Please tell me if the MySQL 4.0.2-alpha Windows 95/98/NT/2000/XP (17.2M) supports the subselect command. Thank you ! _ Narcis GRATIANU ICQ#: 13344551 More ways to contact me: http://wwp.icq.com/13344551 _ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL FreeBSD LinuxThreads
Hi Oleg, I have similar problems on FreeBSD with one or two slow queries hogging the CPU. I am running native threads. I am looking at a parameter in the kernel (/usr/src/sys/i386/conf/LINT). It is the HZ parameter and has to do with polling frequency. The default is 100, I am experimenting with values from 1000 to 5000. You might give this a try. You will need to build a new kernel. I am not sure this will have any effect on MySQL. This is only a suggestion. Best of Luck, Ken - Original Message - From: Oleg Borodkin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, July 15, 2002 3:24 PM Subject: MySQL FreeBSD LinuxThreads Hello, I have noticed that MySQL server almost hangs up the system while processing a big table (perl script using fetchrow). I didn't experienced this before - may be it's because of LinuxThreads? MySQL launches 38 running processes, but shows only 10 mysql query threads (they are all sleeping except 2 or 3). Thanking you in advance, Oleg Borodkin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Beginner's question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Matthew Scarrow wrote: | Try adding a primary key to the table. | | alter table webpage add autoid INT UNSIGNED NOT NULL PRIMARY KEY | AUTO_INCREMENT | | This will help the database functions search through the table quicker. Probably not. If the primary key is not used anywhere else, then it won't. Besides, this table already has a primary key. | I think another area where the query is getting slowed down is in the | translation of the Java. Java is a translated language meaning it gets | compiled when it is run unlike other languages like c++ so you will see more | delay when dealing with java as the interface between you and the database. Sorry, wrong. Now-a-days, Java is not interpreted, and in fact in most web-based applications it keeps equal if not better footing than native code. With web-based applications, you're waiting on I/O most of the time, so native code doesn't go any faster than Java code. The JDBC driver for MySQL is just about as fast as the C-library in most cases (honest). Correctly indexing and tuning queries can give anywhere from a 0%-400% or more speedup, you get 0 if it's already optimized or can't be optimized, and the larger number if you've made conceptual mistakes in your schema, indexing, or query writing. In real-world webapps, Java and native code are less than 10% apart in performance, the middleman does not matter here, unless you've used it incorrectly. | Maybe running the query in the mysqlclient program and see what the response | time is compared to jsp. If you see a big difference in response then it has | something to do with the middle man. | | I can't think of anything else that will help you. Issuing an explain SELECT ., will help us help you, as well as giving the _full_schema_ (you've left out the 'word' table that you're joining to). My guess is because of the compound-index on webpage, or lack of indexes on the 'word' table, no indexes are being used to compute the join, which will of course take a long time, as MySQL will look at _every_ row in the cartesian product to compute the result. -Mark - -- For technical support contracts, visit https://order.mysql.com/?ref=mmma ~__ ___ ___ __ ~ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] ~ / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java ~ /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ~___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6-2 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE9NDqGtvXNTca6JD8RAqcnAJsHc8yuViYS5JAbDPFGc7XGsvLM+gCgou/b A/rGt5o0TVWoIylpJw4TNH8= =7DxQ -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Calculating rows in a result set
Got it, Thanks to all of your replies! W.D.F. - Original Message - From: Cal Evans [EMAIL PROTECTED] To: Walter D. Funk [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, July 16, 2002 11:44 AM Subject: RE: Calculating rows in a result set Not without actually performing SOME kind of query. You could select count(*) from... and insert the rest of your query there. This would give you the count. Then store this in a session variable (PHP) for subsequent pages. Most of the time though, I just put a NEXT button. ADODB has some great pagination tools to make this stuff easy. =C= * * Cal Evans * The Virtual CIO * http://www.calevans.com * -Original Message- From: Walter D. Funk [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 16, 2002 9:02 AM To: [EMAIL PROTECTED] Subject: Calculating rows in a result set Hi everybody, is there a way to calculate the number of rows retrieved by a mysql query, without actually performing it? I want to make a paginated result like in most search engines, and for that I need to display the number of pages the result would have, but I do not want to actually retrieve all the rows to be able to count them, because this is a loss of performance, in particular if the result set retrieves 10 rows and 100 people make a search; this would end in 1 x 100 rows just to say how many pages the result will have, I think there must be a better way to do it, isn't it? thanks to everybody in advance W.D.F. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ERA model generator
Hello, don't somebody know about an application which allows to create the ERA model of a database using GUI and generate SQL code which makes this model real? eg: GUI: [table1]--[table2] [table1] |-attr1 |-attr2 ... SQL code: create table1 (table1_id datatype,attr1 datatype1,attr2 datatype2...) The perfect candidate: - is free - can generate SQL compliant with both MySQL and PostgreSQL - can be run on Linux (might run on Win*) - has adjustable SQL code output Since we don't live in the perfect world I'm ready to compromise... Thanx in advance. -- Best regards, Dusan Juhas - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL C API: Timeouts
Hi, Im using the MySQL C API for my clients and MyISAM tables for the database (clients and server on W2k). I would now like to be sure that the client never hangs when inserting tuples into a table (using mysql_real_query()). This could happen if some other client holds (and doesnt release) a read lock on the table. If I use INSERT, the client hangs if there are free blocks in the table (precluding the concurrency of INSERTS and SELECTS), and if I use INSERT DELAYED, the client hangs when the delayed insert queue becomes full. Is there a way to introduce a timeout for the call to mysql_real_query()? Alternatively, is there a way to check how full the delayed insert queue is for an individual table (rather than the server-wide not_flushed_delayed_rows variable so that I could detect when a subsequent insert might hang? Thanks a lot for your help. Juerg Wadsworth Center, Albany, NY [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
help me with this query
Hi All The Table design goes like this Company Table companyid integer name varchar(100) Services Table ServiceIDinteger Servicevarchar(50) Company Services --- companyidinteger - References Company(companyid) serviceid integer - References Services(ServiceID) The Values in Company table are companyidname 1'ABC' 2'XYZ' 3'CDE' The Values in Services table are serviceidservice 1'Ebiz' 2'MainFrames' 3'CAD' 4'Maintenance' The Values in Company Services table are companyidserviceid 1 1 1 2 1 3 2 1 2 2 3 4 I have a List Box where the user selects his Need for Services. Suppose the User selects 1 , 2 and 3 , then i need to show him the company which provides him all 1 , 2 and 3 services . By our data , its company 1 I cant search by using IN coz it would select a record even if the company is providing any one of the services. I did this by using self join for ex: select distinct(c.companyid) , c.name from company c , companyservices cs1, companyservices cs2, companyservices cs3 where cs1.serviceid = 1 AND cs2.serviceid = 2 AND cs3.serviceid = 3 AND c.companyid = cs1.companyid Is there any other way , i could achive the result without using a self join -Arul sql , query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Calculating rows in a result set
You said: -- is there a way to calculate the number of rows retrieved by a mysql query, without actually performing it? I want to make a paginated result like in most search engines, and for that I need to display the number of pages the result would have, but I do not want to actually retrieve all the rows to be able to count them, because this is a loss of performance, in particular if the result set retrieves 10 rows and 100 people make a search; this would end in 1 x 100 rows just to say how many pages the result will have, I think there must be a better way to do it, isn't it? - If you add SQL_CALC_FOUND_ROWS to the SELECT statement with LIMIT (as explained at http://www.mysql.com/doc/S/E/SELECT.html ) you can then use the FOUND_ROWS() function described at http://www.mysql.com/doc/M/i/Miscellaneous_functions.html to return the numer of rows that would have been returned without a LIMIT statement. This statement is only available from MySQL 4.0, and presumably depends for its performance on the V4 query cacheing (I.e. as you repeatedly re-query with different LIMITs, it doen't redo the select because it recognises a familar request). Alec Cawley - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: UPDATE command..
sql,query IMHO, I'd look at creating a view that postfix looks at instead of the table. This way you could concat the data for the view but keep it separate in the table. rename the table users_table or something and create a view called users. Other than that, the concat statement I and others posted should point you in the right direction. =C= * * Cal Evans * The Virtual CIO * http://www.calevans.com * -Original Message- From: Patrick J Okui [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 16, 2002 10:16 AM To: Cal Evans Cc: [EMAIL PROTECTED] Subject: RE: UPDATE command.. On Tue, 16 Jul 2002, Cal Evans wrote: well, #1, I would revision my data model. since user and domain are 2 distinct pieces of information, I would suggest that you consider pulling them apart and placing them in separate fields. snip The only problem is that this is the required structure of this table because I use it for authentication with postfix (among other things)... Thanks all the same... Patrick J Okui Systems Administrator One2net Limited - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: version
Please tell me if the MySQL 4.0.2-alpha Windows 95/98/NT/2000/XP (17.2M) supports the subselect command. No version of MySQL currently supports subselects. From http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html Subselects are currently being implemented in the 4.1 development tree. But 4.1 is probably quite a way ahead. Alec - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
help me with this query
Hi All The Table design goes like this Company Table companyid integer name varchar(100) Services Table ServiceIDinteger Servicevarchar(50) Company Services --- companyidinteger - References Company(companyid) serviceid integer - References Services(ServiceID) The Values in Company table are companyidname 1'ABC' 2'XYZ' 3'CDE' The Values in Services table are serviceidservice 1'Ebiz' 2'MainFrames' 3'CAD' 4'Maintenance' The Values in Company Services table are companyidserviceid 1 1 1 2 1 3 2 1 2 2 3 4 I have a List Box where the user selects his Need for Services. Suppose the User selects 1 , 2 and 3 , then i need to show him the company which provides him all 1 , 2 and 3 services . By our data , its company 1 I cant search by using IN coz it would select a record even if the company is providing any one of the services. I did this by using self join for ex: select distinct(c.companyid) , c.name from company c , companyservices cs1, companyservices cs2, companyservices cs3 where cs1.serviceid = 1 AND cs2.serviceid = 2 AND cs3.serviceid = 3 AND c.companyid = cs1.companyid Is there any other way , i could achive the result without using a self join -Arul sql , query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: ERA model generator
http://www.thekompany.com/products/dataarchitect/ It's not free but it is cross platform and I think it supports pgsql. =C= * * Cal Evans * The Virtual CIO * http://www.calevans.com * -Original Message- From: Dusan Juhas [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 16, 2002 10:33 AM To: [EMAIL PROTECTED] Subject: ERA model generator Hello, don't somebody know about an application which allows to create the ERA model of a database using GUI and generate SQL code which makes this model real? eg: GUI: [table1]--[table2] [table1] |-attr1 |-attr2 ... SQL code: create table1 (table1_id datatype,attr1 datatype1,attr2 datatype2...) The perfect candidate: - is free - can generate SQL compliant with both MySQL and PostgreSQL - can be run on Linux (might run on Win*) - has adjustable SQL code output Since we don't live in the perfect world I'm ready to compromise... Thanx in advance. -- Best regards, Dusan Juhas - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Create ... select bug
Dear Dirs, I think I' ve found a bug. Please run the following query (I know you don' t have my db, try something similar) CREATE TABLE order_cross SELECT date_format(data_iniz_num,'%y') as anno,ord_tmp.soc,ord_tmp.ml, ord_tmp.cod_agente, ord_tmp.linea, CASE WHEN month(data_ordine) = 1 then qty else 0 END as jan, CASE WHEN month(data_ordine) = 2 then qty else 0 END as feb, CASE WHEN month(data_ordine) = 3 then qty else 0 END as mar, CASE WHEN month(data_ordine) = 4 then qty else 0 END as apr, CASE WHEN month(data_ordine) = 5 then qty else 0 END as may, CASE WHEN month(data_ordine) = 6 then qty else 0 END as jun, CASE WHEN month(data_ordine) = 7 then qty else 0 END as jul, CASE WHEN month(data_ordine) = 8 then qty else 0 END as aug, CASE WHEN month(data_ordine) = 9 then qty else 0 END as sep, CASE WHEN month(data_ordine) = 10 then qty else 0 END as oct, CASE WHEN month(data_ordine) = 11 then qty else 0 END as nov, CASE WHEN month(data_ordine) = 12 then qty else 0 END as 'dec' FROM ord_tmp; ERROR GIVEN : ERROR 1105 at line xx : Unknown error I get the same error with queries like this (create - select - case ). With version 4.0.1 these queries used to work perfectly. Workaround : create the query , then insert-select :) KERNEL : 2.4 VERSION :MySQL-4.0.2-2.i386.rpm VARIABLES : back_log 50 basedir / binlog_cache_size 32768 character_set latin1 character_sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 concurrent_insert OFF connect_timeout 5 datadir /home/mysql/ delay_key_write OFF delayed_insert_limit 100 delayed_insert_timeout 300 delayed_queue_size 1000 flush OFF flush_time 0 ft_min_word_len 4 ft_max_word_len 254 ft_max_word_len_for_sort 20 ft_boolean_syntax + -()~*:| have_bdb NO have_innodb NO have_isam YES have_raid NO have_symlink YES have_openssl NO have_query_cache YES init_file interactive_timeout 28800 join_buffer_size 131072 key_buffer_size 16773120 language /usr/share/mysql/english/ large_files_support ON locked_in_memory OFF log OFF log_update OFF log_bin OFF log_slave_updates OFF log_slow_queries ON long_query_time 10 low_priority_updates OFF lower_case_table_names 0 max_allowed_packet 1047552 max_binlog_cache_size 4294967295 max_binlog_size 1073741824 max_connections 100 max_connect_errors 10 max_delayed_threads 20 max_heap_table_size 16777216 max_join_size 4294967295 max_sort_length 1024 max_user_connections 0 max_tmp_tables 32 max_write_lock_count 4294967295 myisam_bulk_insert_tree_size 8388608 myisam_max_extra_sort_file_size 256 myisam_max_sort_file_size 2047 myisam_recover_options OFF myisam_sort_buffer_size 8388608 net_buffer_length 7168 net_read_timeout 30 net_retry_count 10 net_write_timeout 60 open_files_limit 0 pid_file /var/lib/mysql/mandrake.pid port 3306 protocol_version 10 record_buffer 131072 record_rnd_buffer 131072 rpl_recovery_rank 0 query_buffer_size 0 query_cache_limit 1048576 query_cache_size 0 query_cache_startup_type 1 safe_show_database OFF server_id 1 slave_net_timeout 3600 skip_locking ON skip_networking OFF skip_show_database OFF slow_launch_time 2 socket /var/lib/mysql/mysql.sock sort_buffer 524280 sql_mode 0 table_cache 64 table_type MYISAM thread_cache_size 0 thread_stack 65536 transaction_isolation READ-COMMITTED timezone EDT tmp_table_size 33554432 tmpdir /home/mysql/tmp/ version 4.0.2-alpha-log wait_timeout 28800 Note : You did a great job with multi-table delete !!! THX for existing! Best Regards Andrea Forghieri Project Manager Emmegi S.p.A. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: bug with like, latin1 and umlaut ü
Victoria,
I see the problem is with sql/share/charsets/latin1.conf
because German umlaut 'ü' and 'Ü' is mapped to 59 'Y' in the
sort_order array. This is very much unlike the treatment
for the other umlauts ('ä' -- 5C and 'ö' -- 5D)
german1.conf does map 'ü' -- 55 'U' giving a correct sort order (ü == u
for sorting) but then LIKE does not distinguish between 'schwül' and
'schwul' which have a completely different meaning.
I did not yet check for latin1_de.
Here is a diff to make latin1 work for German but I don't know what it
breaks for other languages (anyone else using 'ü' ???):
--- latin1.conf.ori Tue Jul 16 16:37:05 2002
+++ latin1.conf Tue Jul 16 17:14:39 2002
@@ -58,17 +58,17 @@
# sort_order array (must have 256 elements)
00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F
10 11 12 13 14 15 16 17 18 19 1A 1B 1C 1D 1E 1F
20 21 22 23 24 25 26 27 28 29 2A 2B 2C 2D 2E 2F
30 31 32 33 34 35 36 37 38 39 3A 3B 3C 3D 3E 3F
40 41 42 43 44 45 46 47 48 49 4A 4B 4C 4D 4E 4F
50 51 52 53 54 55 56 57 58 59 5A 5B 5C 5D 5E 5F
60 41 42 43 44 45 46 47 48 49 4A 4B 4C 4D 4E 4F
50 51 52 53 54 55 56 57 58 59 5A 7B 7C 7D 7E 7F
80 81 82 83 84 85 86 87 88 89 8A 8B 8C 8D 8E 8F
90 91 92 93 94 95 96 97 98 99 9A 9B 9C 9D 9E 9F
A0 A1 A2 A3 A4 A5 A6 A7 A8 A9 AA AB AC AD AE AF
B0 B1 B2 B3 B4 B5 B6 B7 B8 B9 BA BB BC BD BE BF
41 41 41 41 5C 5B 5C 43 45 45 45 45 49 49 49 49
- 44 4E 4F 4F 4F 4F 5D D7 D8 55 55 55 59 59 DE DF
+ 44 4E 4F 4F 4F 4F 5D D7 D8 55 55 55 5E 59 DE DF
41 41 41 41 5C 5B 5C 43 45 45 45 45 49 49 49 49
- 44 4E 4F 4F 4F 4F 5D F7 D8 55 55 55 59 59 DE FF
+ 44 4E 4F 4F 4F 4F 5D F7 D8 55 55 55 5E 59 DE FF
Regards,
Thomas
On Thu, 11 Jul 2002, Victoria Reznichenko wrote:
Thomas,
Wednesday, July 10, 2002, 3:40:40 PM, you wrote:
TS On Wed, 10 Jul 2002, Victoria Reznichenko wrote:
oliver,
Wednesday, July 10, 2002, 2:33:06 AM, you wrote:
Description:
o I was trying to find every row in a table which contains the umlaut
o ü (ue). MySQL listed every row which contained the umlaut and rows
o which contained the letter y, but no umlaut... Client and server
o characterset is latin1.
As far as I understand you want to enable correct German sorting order?
If so, since 4.0.0. MySQL supports latin_de character set.
http://www.mysql.com/doc/G/e/German_character_set.html
TS Hi Victoria,
TS unfortunately this is not about sort order but appears to be a bug. I
TS checked it with 4.0.2 using charset latin1 and indeed, Oliver's results
TS are reproducible. We don't want a hit on 'y' when we search for
TS LIKE '%ü%'.
Tomas, character set influence the result of LIKE operator.
latin1 character set supports only some umlauts. Can you test it with
charsets for german language, german1 or latin_de?
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL security
On Tue, Jul 16, 2002 at 02:51:59PM +0200, Mathias Bertelsen wrote: Hello Do you think MySQL is secure enough to keep peoples bookkeeping in? is it safe enough to use without risk of losing important data? Is it necessary to do anything to make it secure? (eg. use of transactions/backup/power failure security) It is. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 47 days, processed 1,016,208,690 queries (246/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
replication works but error log full of junk
I've set up replication and the slave is updating any changes made to the master, but these errors keep showing up in the error log. Any ideas?? 020716 12:57:26 Slave: Failed reading log event, reconnecting to retry, log 'walt-bin.002' position 265 020716 12:57:26 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'walt-bin.002' at position 265 020716 12:57:56 Error reading packet from server: (server_errno=1159) Thanks for any help! sql, query -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 If it's not broketweak it - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Create ... select bug
Dear Dirs, I think I' ve found a bug. Please run the following query (I know you don' t have my db, try something similar) CREATE TABLE order_cross SELECT date_format(data_iniz_num,'%y') as anno,ord_tmp.soc,ord_tmp.ml, ord_tmp.cod_agente, ord_tmp.linea, CASE WHEN month(data_ordine) = 1 then qty else 0 END as jan, CASE WHEN month(data_ordine) = 2 then qty else 0 END as feb, CASE WHEN month(data_ordine) = 3 then qty else 0 END as mar, CASE WHEN month(data_ordine) = 4 then qty else 0 END as apr, CASE WHEN month(data_ordine) = 5 then qty else 0 END as may, CASE WHEN month(data_ordine) = 6 then qty else 0 END as jun, CASE WHEN month(data_ordine) = 7 then qty else 0 END as jul, CASE WHEN month(data_ordine) = 8 then qty else 0 END as aug, CASE WHEN month(data_ordine) = 9 then qty else 0 END as sep, CASE WHEN month(data_ordine) = 10 then qty else 0 END as oct, CASE WHEN month(data_ordine) = 11 then qty else 0 END as nov, CASE WHEN month(data_ordine) = 12 then qty else 0 END as 'dec' FROM ord_tmp; ERROR GIVEN : ERROR 1105 at line xx : Unknown error I get the same error with queries like this (create - select - case ). With version 4.0.1 these queries used to work perfectly. Workaround : create the query , then insert-select :) KERNEL : 2.4 VERSION :MySQL-4.0.2-2.i386.rpm VARIABLES : back_log 50 basedir / binlog_cache_size 32768 character_set latin1 character_sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 concurrent_insert OFF connect_timeout 5 datadir /home/mysql/ delay_key_write OFF delayed_insert_limit 100 delayed_insert_timeout 300 delayed_queue_size 1000 flush OFF flush_time 0 ft_min_word_len 4 ft_max_word_len 254 ft_max_word_len_for_sort 20 ft_boolean_syntax + -()~*:| have_bdb NO have_innodb NO have_isam YES have_raid NO have_symlink YES have_openssl NO have_query_cache YES init_file interactive_timeout 28800 join_buffer_size 131072 key_buffer_size 16773120 language /usr/share/mysql/english/ large_files_support ON locked_in_memory OFF log OFF log_update OFF log_bin OFF log_slave_updates OFF log_slow_queries ON long_query_time 10 low_priority_updates OFF lower_case_table_names 0 max_allowed_packet 1047552 max_binlog_cache_size 4294967295 max_binlog_size 1073741824 max_connections 100 max_connect_errors 10 max_delayed_threads 20 max_heap_table_size 16777216 max_join_size 4294967295 max_sort_length 1024 max_user_connections 0 max_tmp_tables 32 max_write_lock_count 4294967295 myisam_bulk_insert_tree_size 8388608 myisam_max_extra_sort_file_size 256 myisam_max_sort_file_size 2047 myisam_recover_options OFF myisam_sort_buffer_size 8388608 net_buffer_length 7168 net_read_timeout 30 net_retry_count 10 net_write_timeout 60 open_files_limit 0 pid_file /var/lib/mysql/mandrake.pid port 3306 protocol_version 10 record_buffer 131072 record_rnd_buffer 131072 rpl_recovery_rank 0 query_buffer_size 0 query_cache_limit 1048576 query_cache_size 0 query_cache_startup_type 1 safe_show_database OFF server_id 1 slave_net_timeout 3600 skip_locking ON skip_networking OFF skip_show_database OFF slow_launch_time 2 socket /var/lib/mysql/mysql.sock sort_buffer 524280 sql_mode 0 table_cache 64 table_type MYISAM thread_cache_size 0 thread_stack 65536 transaction_isolation READ-COMMITTED timezone EDT tmp_table_size 33554432 tmpdir /home/mysql/tmp/ version 4.0.2-alpha-log wait_timeout 28800 Note : You did a great job with multi-table delete !!! THX for existing! Best Regards Andrea Forghieri Project Manager Emmegi S.p.A. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysqlgui
Please Do you have some information for install mysqlgui in Linux, i do not how do it? Thank you... Luis R. De Freitas R. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Errors 1159 in replication of 3.23.51 not dangerous; will be fixed in .52
Hi! If the master is idle, then a slave of MySQL release 3.23.51 prints: ... 020715 12:05:33 Error reading packet from server: (server_errno=1159) 020715 12:06:03 Slave: Failed reading log event, reconnecting to retry, log 'mysql.001' position 73 020715 12:06:03 Slave: reconnected to master 'slave@venus:24641',replication resumed in log 'mysql.001' at position 73 ... every 30 seconds. You can reduce the number of messages by setting in my.cnf: ... set-variable = net_read_timeout=3600 ... The prints in the error log are not a symptom of any problem, they are a normal read timeout. Version 3.23.52 will suppress these unnecessary prints. Best regards, Heikki Innobase Oy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Create ... select bug
[snip] CREATE TABLE order_cross SELECT date_format(data_iniz_num,'%y') as anno,ord_tmp.soc,ord_tmp.ml, ord_tmp.cod_agente, ord_tmp.linea, CASE WHEN month(data_ordine) = 1 then qty else 0 END as jan, CASE WHEN month(data_ordine) = 2 then qty else 0 END as feb, CASE WHEN month(data_ordine) = 3 then qty else 0 END as mar, CASE WHEN month(data_ordine) = 4 then qty else 0 END as apr, CASE WHEN month(data_ordine) = 5 then qty else 0 END as may, CASE WHEN month(data_ordine) = 6 then qty else 0 END as jun, CASE WHEN month(data_ordine) = 7 then qty else 0 END as jul, CASE WHEN month(data_ordine) = 8 then qty else 0 END as aug, CASE WHEN month(data_ordine) = 9 then qty else 0 END as sep, CASE WHEN month(data_ordine) = 10 then qty else 0 END as oct, CASE WHEN month(data_ordine) = 11 then qty else 0 END as nov, CASE WHEN month(data_ordine) = 12 then qty else 0 END as 'dec' FROM ord_tmp; ERROR GIVEN : ERROR 1105 at line xx : Unknown error [/snip] Is it an error that each of your month listings have no single quotes around them, but dec does? Just checking - unable to test at the moment. HTH! Jay sql, mysql, query Football incorporates the two worst elements of American society: violence punctuated by committee meetings. -George Will * * Want to meet other PHP developers * * in your area? Check out: * * http://php.meetup.com/* * No developer is an island ... * * - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BIGINT - INT in ODBC
Someone has a pretty lousy filter installed At 02:56 PM 07/16/2002, you wrote: Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: Is it possible to export 32-bit unsigned values stored as a BIGINT in unix as a usable value in Access? there doesnt seem to be an unsigned option. Dennis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: processors
A load of 2? Heck we have systems that run a load of 20 or more without issues. If you are doing a lot of connections you might look at optimizing your code. Also using indexes can increase the speed as well. --- Brad Dameron Network Account Executive TSCNet Inc. www.tscnet.com Silverdale, WA. 1-888-8TSCNET -Original Message- From: Adam Nelson [mailto:[EMAIL PROTECTED]] Sent: Monday, July 15, 2002 1:41 PM To: [EMAIL PROTECTED] Subject: processors we are considering moving to a 4-way system as the load average on our 2-way system is at 2. Does anyone have recommendations? Is the larger 1MB cache on some xeon chips worth the extra $$$? I'm thinking of 2 raid1 arrays, one for the os and logs, the other for the db. The data is relatively small, but gets much in the way of select statements. sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
??? Simple sql-question: SELECT iSession FROM O_Sessions GROUP BY iUser
I want the latest (highest) iSession to be selected: SELECT iSession FROM O_Sessions GROUP BY iUser Because of GROUP BY, allways the first(!) recordset for iUser is selected. But i want the last recordset to be selected :-( How can this be done ? (mySQL) (iSession ist the primary key = latest=highest. filling the primary key from 4294967295 down to 0 does not work) greetings from germany (getting rather dark), roland (Bollmann) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problems with high-use tables
Hi - I've been using MySQL for a variety of functions with great success over the last year. However, I recently rebuilt my log database server after a crash, and I've been having a lot of trouble with two tables getting corrupted. One table stores my syslog entries from Kiwi Syslod Daemon, and the other stores the actual firewall logs extracted from the syslog entries. Every five minutes I run a process which reads in all the syslog entries, extracts the firewall logs from them, then deletes the entries from the syslog table. My problem is I keep getting corrupted data files, which brings the whole system to a halt. I'm running MySQL v3 (latest build) on a WinNT4 box. I'm using Perl MyODBC to process the data. Any thoughts? -Chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL on a Cobalt RAQ4
I plan to attempt installing MySQL on a Cobalt Raq4 this week - anyone have experience/advice to share? My plan is to download the RPM from mysql.com and follow the directions on the site, but I have read some things on the internet that say to stay away from the rpm... Kristy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
safe_mysqld - my.cnf weird foo
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 i configure/make/make install mysql-3.23.51 on sparc solaris using gcc-2.95.2. i used the configure option --sysconfdir=/usr/local/etc. i ran the mysql_install_db script, i performed all the chown/chgrp operations, i copied mysql.server to /etc/rc3.d/S99mysql. i copied my-huge.cnf to /usr/local/etc/my.cnf and edited this to add some different options. when i start mysql, the my.cnf file was not loading. i could tell because some of the options i specified were not being run. i copied my.cnf to /etc/my.cnf and tried again. everything worked. why? i changed the sysconfdir during configuration. thanks, shon -BEGIN PGP SIGNATURE- Version: PGPfreeware 7.0.3 for non-commercial use http://www.pgp.com iQA/AwUBPTR5NBerBYVUKJeKEQJT4QCg4ArpWivNyS7NcFDH7ZzvwxeOBNMAnRmz s7PW6dmrp8CiS1VmksYtTPQx =iJNl -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 3.23.51 bug ? ( was RE: Load problems with 3.23.51 )
David, Tuesday, July 16, 2002, 3:55:46 PM, you wrote: DB Same thing here, mysql 3.23.51 works well during about 2 hours then DB suddently load average grow up to 200 and more ... DB Load average is less than 4 with 3.23.49. DB We're using binary tar.gz non max versions from Mysql.com DB OS : Linux Red hat 7.2 DB kernel : 2.4.10 DB 1 Go RAM DB 2 * Intel 1 Ghz We know about that bug and we are investigating it. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
slow count queries
I have an INNODB table which has 4 records in it. customer_number is the primary key. If I run select count(cutomer_number) from customer; It takes about 15 seconds to return the number of rows. I ran explain on the query and it's using the unique key index on customer_number. If I run SHOW TABLE STATUS LIKE 'cutomer'; it only takes about 1.52 seconds for innodb to tell me number of rows. Is there a faster way to get the number of rows without using show table status ? I ran the same query on an oracle db with 1762058 rows and it took 8 seconds. Thanks! -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 If it's not broketweak it - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql db - rsync
Hello. On Sat 2002-07-13 at 05:03:43 -0700, [EMAIL PROTECTED] wrote: If you are on the same OS and rsync happens fine, then you should not loose any data. But only if he also somehow assures that no updates take place on the master while doing the rsync. Additionally, he should do a flush tables beforehand to assure that all modified data is actually written to disk. A more graceful way might be too look into setting up a slave server which will be in sync with the master. Absolutely. Greetings, Benjamin. [...] Can i do rsync with the mysql db as a whole from one machine to other. If i can do then will there be any loss of data while doing rsync. Thanks in advance. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Beta training program
Hello. On Mon 2002-07-15 at 15:44:11 +0200, [EMAIL PROTECTED] wrote: Can someone plz send me the posting from mysqlab concerning the beta training , I deleted it accidentally... Thanks You can find the same information here: http://www.mysql.com/training/certification.html Greetings, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: RE: Mysql wil lnot load
[root@Alpha mysql]# pwd /usr/local/mysql [root@Alpha mysql]# ls -al total 48 drwxr-xr-x 12 root mysql4096 Jul 15 01:31 . drwxr-xr-x 14 root root 4096 Jul 15 01:27 .. drwxr-xr-x2 root mysql4096 Jul 15 01:57 bin drwxr-xr-x3 root mysql4096 Jul 15 01:27 include drwxr-xr-x2 root mysql4096 Jul 15 01:57 info drwxr-xr-x3 root mysql4096 Jul 15 01:27 lib drwxr-xr-x2 root mysql4096 Jul 15 01:57 libexec drwxr-xr-x3 root mysql4096 Jul 15 01:28 man drwxr-xr-x6 root mysql4096 Jul 15 01:57 mysql-test drwxr-xr-x3 root mysql4096 Jul 15 01:28 share drwxr-xr-x7 root mysql4096 Jul 15 01:57 sql-bench drwx--4 root mysql4096 Jul 15 23:38 var [root@Alpha mysql]# cd var [root@Alpha var]# ls -al total 28 drwx--4 root mysql4096 Jul 15 23:38 . drwxr-xr-x 12 root mysql4096 Jul 15 01:31 .. -rw---1 mysqlmysql9555 Jul 16 05:23 Alpha.err drwx-- 25 root mysql4096 Jul 15 23:38 mysql drwx--2 root mysql4096 Jul 15 23:38 test [root@Alpha var]# Mysql is the owner of the directories .. I really don't know why this isnt working --- Aaron Axelsen AIM: AAAK2 Email: [EMAIL PROTECTED] URL: www.amadmax.com It said, Insert disk #3, but only two will fit! One picture is worth 128K words. -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 16, 2002 6:48 AM To: [EMAIL PROTECTED] Subject: Re: RE: Mysql wil lnot load Aaron, Tuesday, July 16, 2002, 1:25:23 PM, you wrote: AA The instructions where followed from the website exaactly and all AA the file premissions are correct, but I still get the following AA errors in ther log file: AA 020716 05:23:59 mysqld started AA ^G/usr/local/mysql/libexec/mysqld: File './Alpha-bin.1' not found AA (Errcode: 13) AA 020716 5:23:59 Could not use Alpha-bin for logging (error 13) AA 020716 5:23:59 /usr/local/mysql/libexec/mysqld: Can't create/write AA to file '/usr/local/mysql/var/Alpha.pid' (Errcode: 13) 020716 AA 5:23:59 /usr/local/mysql/libexec/mysqld: Can't find file: AA './mysql/host.frm' (errno: 13) 020716 5:23:59 AA /usr/local/mysql/libexec/mysqld: Error on delete of AA '/usr/local/mysql/var/Alpha.pid' (Errcode: 13) 020716 05:23:59 AA mysqld ended MySQL must be owner of the data dir, must have permissions to create socket file. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Permissions problems
USually all the databases sets up fine BUT recently, I have had to adjust databases to 0777 in order to get them to work and change the ownership. I prefer the more secure model of mysql. How can I fix this? -- contact: Dave Yadallee NetKnow The Internet Knowledge Company [EMAIL PROTECTED] http://www.nl2k.ab.ca 990-3244 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Transactions, Deadlocks, User Variables and Replication
Hi, I'm developing a system which will use transactions with InnoDB. In principle, when you use transactions, there is some risk that a deadlock will happen and MySQL will abort the transaction. In this situation, you should generally try to do the transaction again. I'm wondering * Which error codes are an indication that a transaction should be retried? (Just a deadlock, or anything else? What about a failure on COMMIT?) * Is there some easy sequence of transactions I can do that will make MySQL deadlock for testing purposes? (Or should I just screw around with it until I can make it deadlock?) * In section 4.10.4 it says Update queries that use user variables are not replication-safe (yet) -- Is this still the case? -- If so, will this be fixed in the near future? My plan is to store a list of update statements that together will form a transaction. Since I want to centralize the logic for redoing failed transactions, I'd rather like to pass a list of queries to a function which will do all the updates in a transaction and redo them. The only kind of read access to the database that these transactions will need will be getting the LAST_INSERT_ID(). User variables would be a convenient way to do this, although I could certainly find another way to do it if that's not an option. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL on a Cobalt RAQ4
Hi Dont use the rpm, go for the package at http://pkgmaster.com/packages/raq/4/ and install it from there, just point and click! Peter --- Excellence in internet and open source software --- Sunmaia www.sunmaia.net [EMAIL PROTECTED] tel. 0121-242-1473 --- -Original Message- From: K Frey [mailto:[EMAIL PROTECTED]] Sent: 16 July 2002 20:39 To: [EMAIL PROTECTED] Subject: MySQL on a Cobalt RAQ4 I plan to attempt installing MySQL on a Cobalt Raq4 this week - anyone have experience/advice to share? My plan is to download the RPM from mysql.com and follow the directions on the site, but I have read some things on the internet that say to stay away from the rpm... Kristy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ??? Simple sql-question: SELECT iSession FROM O_Sessions GROUP BY iUser
On 16 Jul 2002, at 21:39, Robo wrote: I want the latest (highest) iSession to be selected: SELECT iSession FROM O_Sessions GROUP BY iUser Because of GROUP BY, allways the first(!) recordset for iUser is selected. But i want the last recordset to be selected :-( I'm not sure what you want, but the query could be SELECT iUser, MAX(iSession) FROM O_Sessions GROUP BY iUser; If that's not it, you'll need to explain more. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MyISAM v. InnoDB
Hello. On Tue 2002-07-16 at 07:47:33 -0500, [EMAIL PROTECTED] wrote: Of the data? Or of the queries? Not the data. Just your query, the output from explain and a create table statement. Query: SELECT DISTINCT cards_crypt.* FROM user_haves, cards_crypt WHERE cards_crypt.name LIKE %harrod% AND user_haves.card_name = cards_crypt.name AND user_haves.total_have 0 AND user_haves.user != joe_bob_briggs Explain: *** 1. row *** table: user_haves type: ALL possible_keys: card_name,total_have key: NULL key_len: NULL ref: NULL rows: 79370 Extra: where used; Using temporary *** 2. row *** table: cards_crypt type: eq_ref possible_keys: name key: name key_len: 255 ref: user_haves.card_name rows: 1 Extra: where used 2 rows in set (0.00 sec) This tells, that for the first table no index can be used, but all rows have to be read in. And that for each row, one row of the second table is read, using the index name, which has a length of 255 chars. Reading only one row is good, an index having 255 chars is bad. The first table has to be read completely, because the beginning % in %harrod% prevents the use of any kex (see http://www.mysql.com/doc/M/y/MySQL_indexes.html). I suggest to try the following, in the listed order, until you are satisfied with the speed: - Use the numeric primary key to join the tables, i.e. replace user_haves.card_name with a user_haves.card_num, which refers to cards_crypt.record_num and then change the where clause accordingly SELECT DISTINCT cards_crypt.* FROM user_haves, cards_crypt WHERE cards_crypt.name LIKE %harrod% AND user_haves.card_num = cards_crypt.record_num AND user_haves.total_have 0 AND user_haves.user != joe_bob_briggs The will have the additional effect of making user_have smaller and more normalized. - If possible and reasonable for your application, create an FULLTEXT index over the card names and use its boolean(!) search instead of LIKE. The SELECT would look like (from mind): SELECT DISTINCT cards_crypt.* FROM user_haves, cards_crypt WHERE MATCH(cards_crypt.name) AGAINST (+harrod IN BOOLEAN MODE) AND user_haves.card_num = cards_crypt.record_num AND user_haves.total_have 0 AND user_haves.user != joe_bob_briggs - Alternatively, if using FULLTEXT indexes is not feasable, split up cards_crypt.name into words (like harrod), create a seperate table with these words and a second table which assigns the words to card names, i.e. CREATE TABLE card_words ( record_num int unsigned NOT NULL auto_increment PRIMARY KEY, word varchar(255), UNIQUE word (word) ); CREATE TABLE words_to_cards ( word_num int unsigned NOT NULL, card_num int unsigned NOT NULL, PRIMARY KEY (word_num, card_num) ); Then the SELECT would look like SELECT DISTINCT cards_crypt.* FROM user_haves, cards_crypt, card_words, words_to_cards WHERE card_words.word = harrod AND words_to_cards.word_num = card_words.record_num AND cards_crypt.record_num = words_to_cards.card_num AND user_haves.card_num = cards_crypt.record_num AND user_haves.total_have 0 AND user_haves.user != joe_bob_briggs Greetings, Benjamin. [...] CREATE TABLE user_haves ( card_name varchar(100) NOT NULL default '', total_have int(11) unsigned NOT NULL default '0', user varchar(50) NOT NULL default '', available int(11) unsigned NOT NULL default '0', record_num int(11) unsigned NOT NULL auto_increment, PRIMARY KEY (record_num), KEY user (user), KEY card_name (card_name), KEY total_have (total_have), KEY available (available) ) TYPE=ISAM PACK_KEYS=1; CREATE TABLE cards_crypt ( record_num int(11) unsigned NOT NULL auto_increment, name varchar(255) NOT NULL default '', clan varchar(100) NOT NULL default '', capacity int(10) NOT NULL default '0', disciplines varchar(100) default '', superior varchar(100) default '', inferior varchar(100) default '', title varchar(100) default '', bonus_penalty text, release_set varchar(100) NOT NULL default '', rarity varchar(50) default '', artist varchar(100) default '', have int(10) default '0', need int(10) default '0', notes text, alternate_name varchar(255) default '', card_monger_url varchar(255) default '', PRIMARY KEY (record_num), UNIQUE KEY name (name), KEY clan (clan), KEY capacity (capacity), KEY release_set (release_set) ) TYPE=ISAM PACK_KEYS=1; -- [EMAIL PROTECTED] - Before posting, please
customizing user table
Hi, will it work if I add some custom fields to the mysql user table? Regards -jm - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: slow count queries
On Tue, Jul 16, 2002 at 03:52:35PM -0400, walt wrote: I have an INNODB table which has 4 records in it. customer_number is the primary key. If I run select count(cutomer_number) from customer; It takes about 15 seconds to return the number of rows. I ran explain on the query and it's using the unique key index on customer_number. That's documented. InnoDB must read the whole table. If I run SHOW TABLE STATUS LIKE 'cutomer'; it only takes about 1.52 seconds for innodb to tell me number of rows. That's an estimate of the number of rows. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 47 days, processed 1,019,366,418 queries (246/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: slow count queries
On Tuesday 16 July 2002 05:05 pm, Jeremy Zawodny wrote: On Tue, Jul 16, 2002 at 03:52:35PM -0400, walt wrote: I have an INNODB table which has 4 records in it. customer_number is the primary key. If I run select count(cutomer_number) from customer; It takes about 15 seconds to return the number of rows. I ran explain on the query and it's using the unique key index on customer_number. That's documented. InnoDB must read the whole table. If I run SHOW TABLE STATUS LIKE 'cutomer'; it only takes about 1.52 seconds for innodb to tell me number of rows. That's an estimate of the number of rows. Jeremy Thanks Jeremy! After I sent this email, I changed the primary key to a unique index and it took under 2 seconds to return the number of rows. Do you know of any advantage a primary key would have over a unique index? The unique index still enforces the uniqueness of the column. Kinda funny that the error message from violating the unique index is Duplicate entry '104191' for key 1 :-) Thanks! -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 If it's not broketweak it - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
[ date format ]
Hi, the date format of MySql is /mm/dd (2002/07/16) right? How I do to insert the date typing in my form dd/mm/ (16/07/2002)? It is possible? Thanks, Elby _ MSN Photos é a maneira mais fácil e prática de editar e compartilhar sua fotos: http://photos.msn.com.br - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: ??? Simple sql-question: SELECT iSession FROM O_Sessions GROUP BY iUser
Well if you want the latest and greatest iSession irrespective of the user use select max(iSession) from O_Sessions; If it is to be grouped by user, then select user, max(iSession) from O_Sessions group by user; This will give you the max iSession for a user. Regards Satish -Original Message- From: Robo [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 16, 2002 12:39 PM To: [EMAIL PROTECTED] Subject: ??? Simple sql-question: SELECT iSession FROM O_Sessions GROUP BY iUser I want the latest (highest) iSession to be selected: SELECT iSession FROM O_Sessions GROUP BY iUser Because of GROUP BY, allways the first(!) recordset for iUser is selected. But i want the last recordset to be selected :-( How can this be done ? (mySQL) (iSession ist the primary key = latest=highest. filling the primary key from 4294967295 down to 0 does not work) greetings from germany (getting rather dark), roland (Bollmann) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: slow count queries
Walt, InnoDb physically counts all the rows in the table (unlike MyISAM tables which stores the row count in a separate location). So in your case it went through all 99,994 rows. The more rows you have in the table, the longer it will take. I don't know how accurate Show table status like ... is. It may only be updated occasionally. Mike At 02:52 PM 7/16/2002, you wrote: I have an INNODB table which has 4 records in it. customer_number is the primary key. If I run select count(cutomer_number) from customer; It takes about 15 seconds to return the number of rows. I ran explain on the query and it's using the unique key index on customer_number. If I run SHOW TABLE STATUS LIKE 'cutomer'; it only takes about 1.52 seconds for innodb to tell me number of rows. Is there a faster way to get the number of rows without using show table status ? I ran the same query on an oracle db with 1762058 rows and it took 8 seconds. Thanks! -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL on a Cobalt RAQ4
two pieces of advice. 1: Stop posting over and over again. 2: Check the archives. This is has been discussed in the past 3 months. There is probably some very good advice there. =C= * * Cal Evans * The Virtual CIO * http://www.calevans.com * -Original Message- From: K Frey [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 16, 2002 2:39 PM To: [EMAIL PROTECTED] Subject: MySQL on a Cobalt RAQ4 I plan to attempt installing MySQL on a Cobalt Raq4 this week - anyone have experience/advice to share? My plan is to download the RPM from mysql.com and follow the directions on the site, but I have read some things on the internet that say to stay away from the rpm... Kristy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
not so simple sql-question?: SELECT iSession FROM O_Sessions ORDER BY iSession DESC GROUP BY iUser
thank's for your replies, but that did not help :-( this is the setting (also look below for example table): O_Sessions contains multilpe recordsets for iUser (let' say iUser=42). I want the last RECORDSET of this iUser=42, not the first. With GROUP BY, all the various recordsets with iUser=42 are thrown together, and mysql simply takes the first one (=lowest iSession, as iSession is the increasing primary index.) But i need the last one of iUser=42 and the last one of iUser=42 and so on. SELECT iUser, MAX(iSession) FROM O_Sessions GROUP BY iUser; will not do, it will only select the greatest iSession for every iUser, not the latest RECORDSET. I need the recordset, as there is more data in O_Sessions like sSession, which holds the name for the session... SELECT iSession FROM O_Sessions GROUP BY iUser ORDER BY iUser DESC will also not do, as it only orderes the output (first iUser=42 then iUser=41, ...) and does nothing about the pre-selection during grouping. Maybe I could do with a sub-select, but mysql does not offer that ? And the querry would blow up anyway. I have demands like this from time to time, and have never found a satisfying solution. I fear, that sql does not support my wants and there is no way to tell the sql server which recordset to select when grouping the table. I think, grouping is implemented by simply advancing the recordsets an ignoring iUser=42 when one is found. That way, allways the first recordset is selected. But is there no way to tell the system to search the table downwards ? Something like this: SELECT iSession FROM O_Sessions ORDER BY iSession DESC GROUP BY iUser (not sql) well, i am currently making a halt in programming, hoping for you to find a solution :-) now entirely dark in Germany, but stomach still quite full with self-cooked chinese food. Roland :-) -- O_Sessions: | iSession | iUser | sSession| | 1 | 41 | not wanted | | 2 | 41 | wanted | | 3 | 42 | not wanted | | 4 | 42 | wanted | | 5 | 43 | not wanted | | 6 | 43 | wanted | --- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Lost connection to mysql server
I have a build an ASP page to enter data in a mysql database. When I open the page I get the following error : Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80004005) [TCX][MyODBC]Lost connection to MySQL server during query /centraladmin/products.asp, line 88 When I refresh it works fine. It seems to me it's like a Timeout thing. This is my connection to the database set oconn = server.createobject(Adodb.connection) oconn.connectionstring = server=192.168.1.73;driver=MySQL;db=supplies;uid=myname;pwd=mypwd oconn.open Any ideas? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
