Re: Exporting a database from one PC to another using MySQL 5.0
Hi Eric, you can setup replication on your laptop (master) and your PC (slave). http://dev.mysql.com/doc/mysql/en/replication.html Best regards, Mikhail. - Original Message - From: Eric Dahlenburg [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, August 08, 2005 4:12 PM Subject: Exporting a database from one PC to another using MySQL 5.0 Hi, I am currently a student learning SQL. I have MySQL 5.0 installed at home and on my Laptop. How can I take a database that I have updated on my laptop and transfer it to my home PC so that they are both synchronized ? I tried looking on the forums for this info, but forums locks-up my Internet Explorer for some reason. Thanks, Eric Eric Dahlenburg Spacecoastsales.net [EMAIL PROTECTED] 321-453-7627 Voice/ Fax 321-917-9098 Cell This communication is intended solely for the use of the person(s) to whom it is addressed. This communication may contain confidential information or information otherwise subject to laws and regulations regarding its use, and any unauthorized use, dissemination, distribution or copying of this communication, or any portion thereof, may therefore be legally prohibited. If you are not the intended recipient of this communication you are not authorized to use, disseminate, distribute or copy this communication or any portion thereof, and are requested to notify the sender by return email and delete this communication from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql bug 9670 OPTIMIZE TABLE crashes
Matthijs, in the bug report Heikki Tuuri wrote: *** [13 Apr 20:59] Heikki Tuuri The bug was introduced in 4.0.24 and 4.1.10. The fix will be in 4.1.12 and 5.0.5. *** Best regards, Mikhail. - Original Message - From: Matthijs van der Klip [EMAIL PROTECTED] To: MySQL mailing list mysql@lists.mysql.com Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, August 01, 2005 4:08 PM Subject: mysql bug 9670 OPTIMIZE TABLE crashes Hi, I am experiencing assertion failures described in the following bugreport: http://bugs.mysql.com/bug.php?id=9670 The bug has been closed, but I'm running the most current 4.0.25 version of MySQL. The report mentions downgrading to 4.0.23 or upgrading to 4.1. Is this really neccessary or should this bug already be fixed in 4.0.25? Best regards, -- Matthijs van der Klip System Administrator Spill E-Projects The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date validation using mysql
Hi Anoop,
In order to validate a date I am using next query:
select date_format(date_sub(date_add('yourdate', interval 1 day), interval
1 day),'%Y%m%d') = date_format('yourdate','%Y%m%d');
It will give you 1 if date is valid.
Best regards,
Mikhail.
- Original Message -
From: Anoop kumar V [EMAIL PROTECTED]
To: Chris Ramsay [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Friday, April 29, 2005 6:10 PM
Subject: Re: Date validation using mysql
No problem - I followed up and found out that this function is not
available. THought probably you were referring to a later version of Mysql..
anyways...
We use Java - and maybe I could use that - I was just wondering if I could
help reinventing something already there.
Thanks so much for the suggestions/
Anoop
On 4/29/05, Chris Ramsay [EMAIL PROTECTED] wrote:
Anoop
snip
You could try checkdate()...
/snip
Apologies for erroneous advice - I am evidently suffering from list
psychosis...
Chris
--
Thanks and best regards,
Anoop
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why NOT NULL in PRIMARY key??
Jigal, create table YourTable ( id INT(11), name VARCHAR(32), value INT(11), PRIMARY KEY(id,name,value) ) let's assume that PRIMARY KEY works like you want (accept NULLs) and we have a row in your table: (id,name,value) = (1,NULL,12) Then you insert a new row: insert into YourTable (id,name,value) values (1,NULL,12). Before inserting MySQL will try to find a record with the same values. But since comparison with NULL value returns always FALSE MySQL will think that there is no such record in the table. After this point you will get 2 identical records in the table. Mikhail. - Original Message - From: Jigal van Hemert [EMAIL PROTECTED] To: Dawid Kuroczko [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, April 26, 2005 4:47 PM Subject: Re: why NOT NULL in PRIMARY key?? From: Dawid Kuroczko It can't have anything to do with the 'uniqueness' of the data, since I can have a lot of 'zero'-values in the column, as long as the combination of columns in the PRIMARY key results in unique values. Because it is a PRIMARY KEY. I mean phrase 'PRIMARY KEY' means a key with which each row can be explicitly addressed. So if you have 2000 rows in a table, you can write 2000 SELECT statemens which will use columns in primary key and each of these SELECT statements will return exactly one (different) row. With the NULL values included it will still uniquely identify each row... I would understand it if it would mean that the key as a whole could not be NULL, but the restriction that each column that is part of a PRIMARY KEY must have the NOT NULL constraint is not logical. If your PRIMARY KEY would allow NULL values, it would not be possible to address these rows with NULL values (*) and therefore it would not be a real primary key, by definiton. It would be a unique key. That would be true for the entire key, but not for each part of the key... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem in a very simple SQL statement
Missed FROM clause... ;) Best regards, Mikhail. - Original Message - From: Mário Gamito [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, March 11, 2005 1:49 PM Subject: Problem in a very simple SQL statement Hi, Why do i get an error on this statement: select count(email) where email='[EMAIL PROTECTED]'; The error is: ERROR 1064: You have an error in your SQL syntax near 'where email='[EMAIL PROTECTED]'' at line 1 The statement seems pretty clear to me, but obviously something's wrong. What can it be ? Any help would be apreciated. Warm Regards, Mário Gamito -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
surprise from mysql
Hi, Today I was surprised by MySQL... :) I have a table like create table MyTable (a int default 0 not null, b date); insert into MyTable (a, b) values (1, null), (1, null), (1, null); And today morning I executed update query: update MyTable set a = 0 and b = now() where a = 1; After that I have found that MySQL updated only first field 'a' and didn't update second one 'b'. It was a big surprise for me. 8-\ First thought was It is a bug in MySQL. But later I have found that the reason is a misprint that I made in 'update' query. Of course the query should be update MyTable set a = 0, b = now() where a = 1; So MySQL understood 0 and b = now() as expression and after calculation 0 and b = now() = 0 and 0 = 0. the query is: update MyTable set a = 0 where a = 1; Best regards, Mikhail. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A question of negative numbers..
abs() - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, September 01, 2004 12:59 PM Subject: A question of negative numbers.. Hi, I have a query that returns a list of numbers ranging from -10 to +10 I would like to be able to have a 2nd column where a result of 5 is 5 but -5 is also 5, so in effect all the negative (and only the negative) results are made positive to find the deviation from zero. so 5, 4, -3, 4, -1, 0 would become 5, 4, 3, 4, 1, 0 I have been searching for if then else in google so I could do if a 0 then a = 0-a but no joy. is there a function to make negative numbers positive? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Concat
select concat(ifnull(Field1,'NULL'), Filed2) from My Table; - Original Message - From: A Z [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 31, 2004 2:25 PM Subject: Concat Hi, Concat() returns Null if any field in the field list is Null. How can I use it to return a String regardless, or is there another function to do it? regards ___ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1 day 28 min insert
Could you execute show create table 321st_stat and show create table stat_in and send results back? Best regards, Mikhail. - Original Message - From: matt ryan [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, August 27, 2004 4:45 PM Subject: Re: 1 day 28 min insert Mikhail Entaltsev wrote: Hi, insert into 321st_stat select * from stat_in group by primary key fields from 321st_stat table; did you try to use this query? Best regards, Mikhail. Ran it, it took at least 24 hours, it finished but never gave me the total time, when I checked the server mysql dropped me back to the command prompt, with no time or number of records :( Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1 day 28 min insert
First of all, IMHO index 321st_stat.dic is useless since you have dic as the first field in 321st_stat.PRIMARY KEY. Second, I would recommend add KEY (`dic`,`niin`,`fr_ric`,`don`,`suf`,`dte_txn`,`sta`) on stat_in table and set PACK_KEYS=0 for stat_in table. Then measure execution time of select * from stat_in group by dic,niin,fr_ric,don,suf,dte_txn,sta; If it is relatively short then the problem is in number of keys and PACK_KEYS for 321st_stat table. Please, let me know about your results. Best regards, Mikhail. - Original Message - From: matt ryan [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, August 27, 2004 9:25 PM Subject: Re: 1 day 28 min insert Mikhail Entaltsev wrote: Could you execute show create table 321st_stat and show create table stat_in and send results back? I have no key's on the temp table, stat_in, do you think adding keys on the whole primary key would be faster? I wasnt sure if you could join mysql keys, the key is called primary key so would it be a.primary key = b.primary key ? mysql explain select a.* from stat_in a left outer join 321st_stat b on a.don=b.don and a.dic=b.dic and a.niin=b.niin and a.sta=b.sta and a.qty=b.qty and a.fr_ric=b.fr_ric and a.suf=b.suf and a.dte_txn =b.dte_txn where isnull(b.don); | id | select_type | table | type | possible_keys| key | key_len | ref| rows | Extra | | 1 | SIMPLE | a | ALL| NULL | NULL| NULL | NULL| 77269086 | | | 1 | SIMPLE | b | eq_ref | PRIMARY,don,niin,dic,dte_txn | PRIMARY |39 | finlog.a.dic,finlog.a.niin,finlog.a.fr_ric,finlog.a.don,finlog.a.suf,finlog. a.dte_txn,finlog.a.sta | 1 | Using where; Not exists | 2 rows in set (0.11 sec) ---+ | 321st_stat | CREATE TABLE `321st_stat` ( `dic` char(3) NOT NULL default '', `fr_ric` char(3) NOT NULL default '', `niin` char(11) NOT NULL default '', `ui` char(2) NOT NULL default '', `qty` char(5) NOT NULL default '', `don` char(14) NOT NULL default '', `suf` char(1) NOT NULL default '', `dte_txn` char(5) NOT NULL default '', `ship_to` char(3) NOT NULL default '', `sta` char(2) NOT NULL default '', `lst_sos` char(3) NOT NULL default '', `esd` char(4) NOT NULL default '', `stor` char(3) NOT NULL default '', `d_t` char(4) NOT NULL default '', `ctasc` char(10) NOT NULL default '', PRIMARY KEY (`dic`,`niin`,`fr_ric`,`don`,`suf`,`dte_txn`,`sta`), KEY `don` (`don`), KEY `niin` (`niin`), KEY `stor` (`stor`), KEY `dic` (`dic`), KEY `ctasc` (`ctasc`), KEY `dte_txn` (`dte_txn`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=900,000,000 PACK_KEYS=1 | 1 row in set (0.03 sec) | stat_in | CREATE TABLE `stat_in` ( `dic` char(3) NOT NULL default '', `fr_ric` char(3) NOT NULL default '', `niin` char(11) NOT NULL default '', `ui` char(2) NOT NULL default '', `qty` char(5) NOT NULL default '', `don` char(14) NOT NULL default '', `suf` char(1) NOT NULL default '', `dte_txn` char(5) NOT NULL default '', `ship_to` char(3) NOT NULL default '', `sta` char(2) NOT NULL default '', `lst_sos` char(3) NOT NULL default '', `esd` char(4) NOT NULL default '', `stor` char(3) NOT NULL default '', `d_t` char(4) NOT NULL default '', `ctasc` char(10) NOT NULL default '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=99,999,999 PACK_KEYS=1 | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1 day 28 min insert
You _could_ try adding an identical primary key to the stat_in table as you have on the 321st_stat table. However, since we need all of the rows from stat_in in the results, I am not sure that it will help speed up the join (because it's a left join). Even though I think the index would be ignored, it is worth a try to see if it would make a difference with a new EXPLAIN.. But it should speed up grouping by PRIMARY KEY in: insert into 321st_stat select * from stat_in group by dic,niin,fr_ric,don,suf,dte_txn,sta; Mikhail. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1 day 28 min insert
If that were a valid SQL statement, I would agree with you. What you wrote **only** functions through a MySQL-specific SQL extension that permits non-aggregated, non-grouped columns to exist in the SELECT clause. He is working with MySQL and that query is working in MySQL. So everything should be fine. :) But I agree that it is MySQL-specific query. I do not understand why you believe that a GROUP BY test will prove any sort of performance gain for his original INSERT problem. It took just over 24 hours to do an INSERT IGNORE to add just about 1000 rows to a 77 million row table. What metric of that performance will your GROUP BY test help to prove? Yes, it is unclear from my previous email. Let's look at that query: insert into 321st_stat select * from stat_in group by dic,niin,fr_ric,don,suf,dte_txn,sta; Why it could be slow? Either select part is slow, either insert or both. If select part is slow then adding KEY (`dic`,`niin`,`fr_ric`,`don`,`suf`,`dte_txn`,`sta`) on stat_in table should help. If select part is fast then IMHO the problem is in number of keys and PACK_KEYS for 321st_stat table. Right? Best regards, Mikhail. - Original Message - From: [EMAIL PROTECTED] To: Mikhail Entaltsev Cc: matt ryan ; [EMAIL PROTECTED] Sent: Friday, August 27, 2004 10:40 PM Subject: Re: 1 day 28 min insert If that were a valid SQL statement, I would agree with you. What you wrote **only** functions through a MySQL-specific SQL extension that permits non-aggregated, non-grouped columns to exist in the SELECT clause. I do not understand why you believe that a GROUP BY test will prove any sort of performance gain for his original INSERT problem. It took just over 24 hours to do an INSERT IGNORE to add just about 1000 rows to a 77 million row table. What metric of that performance will your GROUP BY test help to prove? Respecfully puzzled, Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1 day 28 min insert
Hi, insert into 321st_stat select * from stat_in group by primary key fields from 321st_stat table; did you try to use this query? Best regards, Mikhail. - Original Message - From: matt ryan [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, August 19, 2004 6:06 PM Subject: 1 day 28 min insert I think oracle parallel query is calling me 110,832,565 stat records 77,269,086 on weekly update, I get small daily files, but daily sql's dont work very well, and miss records, in this case it missed 563 records. mysql update stat_in set ctasc='321ST'; Query OK, 77269086 rows affected (24 min 17.60 sec) Rows matched: 77269086 Changed: 77269086 Warnings: 0 mysql insert ignore into 321st_stat select * from stat_in; Query OK, 563 rows affected (1 day 28 min 35.95 sec) Records: 77269086 Duplicates: 77268523 Warnings: 0 I just cant deal with speeds this slow, an insert onto a table with a primary key that tosses out almost all records shouldnt take this long to do -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max
Thank you for correction. You are absolutly right!
Best regards,
Mikhail.
- Original Message -
From: Brian Mansell [EMAIL PROTECTED]
To: Mikhail Entaltsev [EMAIL PROTECTED]
Cc: A Z [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Wednesday, May 12, 2004 10:11 AM
Subject: Re: Max
Just a minor correction...
select YourField from YourTable order by YourField DESC limit 1;
('DESC' in order to return the greatest value first)
On Tue, 11 May 2004 17:28:05 +0200, Mikhail Entaltsev
[EMAIL PROTECTED] wrote:
Hi,
select YourField from YourTable order by YourField limit 1;
Best regards,
Mikhail.
- Original Message -
From: A Z [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, May 11, 2004 3:01 PM
Subject: Max
Hi,
A field of type VarChar() with following syntax:
ABA1.
How to get the Max value. Bearing in mind that value
ABA10 comes before ABA2.
regards
Yahoo! Messenger - Communicate instantly...Ping
your friends today! Download Messenger Now
http://uk.messenger.yahoo.com/download/index.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL documentation
Hi, I have found that messages a.. Non-standard behavior of UNION statements has changed to the standard ones. So far, a table name in the ORDER BY clause was tolerated. From now on a proper error message is issued (Bug #3064). a.. Added max_insert_delayed_threads system variable as a synonym for max_delayed_threads. a.. Added query_cache_wlock_invalidate system variable. It allow emulation of MyISAM table write-locking behavior, even for queries in the query cache. (Bug #2693) are doubled in the documentation (http://dev.mysql.com/doc/mysql/en/News-4.0.19.html) Best regards, Mikhail. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: index change moving files to other computer?
Alan, Now I'd love to know why I can't upgrade :) You can upgrade to version 4.0.17 ;) It seems you hit the bug that was introduced in 4.0.16, and fixed in 4.0.17. Look at this: http://www.mysql.com/doc/en/News-4.0.17.html Fixed optimizer bug, introduced in 4.0.16, when REF access plan was preferred to more efficient RANGE on another column. Best regards, Mikhail. - Original Message - From: Alan [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 04, 2004 8:37 PM Subject: Re: index change moving files to other computer? On Wed, Feb 04, 2004 at 07:21:30PM +, [EMAIL PROTECTED] wrote: The older system is choosing to use a different index. I would suggest Any idea why it would choose this? Shouldn't mysql keep using the same indexes? running an analyze on your new tables and see if you can get the newer system to use the same Postsindex8 index. I ran myisamchk -a on this, which according to the documentation is the same. No changes. I did downgrade to 4.0.14 however and happy happy day it's back up to the speed that I was used to! Now I'd love to know why I can't upgrade :) Sorry for being such a lamer n00b, but it's not my DB and mysql has always just worked for me (though I don't use many 600k row tables :) Alan -- Alan [EMAIL PROTECTED] - http://arcterex.net There are only 3 real sports: bull-fighting, car racing and mountain climbing. All the others are mere games.-- Hemingway -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: max key length 500 in myisam index
Adam, I've got to create a table that has the following: CREATE TABLE access ( query VARCHAR(255) NOT NULL, INDEX (query) ); and mysql is telling that the max bytes allowed is 500 for key length. The docs say I can change this by recompiling, which I would like to avoid having to do. Is there any way around this to get a 255 character length field with index? Looks like you default character set is using 2 bytes per symbol. In this case varchar(255) using 510 bytes and it is more than 500. This is going to store weblog entries, so it's going to be a huge table. Is there a different table type / column type / index type I could use to achieve what I need? But you can create index that use only part of the query column: CREATE TABLE access ( query VARCHAR(255) NOT NULL, INDEX (query(200)) ); See more details here http://www.mysql.com/doc/en/CREATE_INDEX.html Best regards, Mikhail. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: key_reads key_read_requests
Hi, I am not sure, but may be you had Key_read_requests overflow. Best regards, Mikhail. - Original Message - From: John David Duncan [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, January 29, 2004 11:40 PM Subject: key_reads key_read_requests Hi, key_reads is usually a small fraction of key_read_requests, but in the case of the server below, key_reads is actually GREATER than key_read_requests. Can anyone explain what would cause that to happen? - JD mysql show status like 'key%'; ++---+ | Variable_name | Value | ++---+ | Key_blocks_used| 997521| | Key_read_requests | 42804277 | | Key_reads | 70150022 | | Key_write_requests | 236384514 | | Key_writes | 130961162 | ++---+ 5 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL and productivity
Hi, actually it depends on table type. Since you said that it will be temp table then I guess it will be MyISAM table. In this case it is better to use 2 way: 2-nd case: I create table, filling data and then create indexes? But if you will create InnoDB temp table that (according to Heikki Tuuri) better to use 1 way: 1-st case: I create table and create indexes immediately before filling data. Look at this email from Heikki Tuuri: Hi! You should always create the indexes BEFORE adding the data to an InnoDB table. In CREATE INDEX ... ON ..., MySQL rebuilds the whole table. Thus, it will be much slower to add the indexes afterwards. Many databases have an optimized index build procedure where adding an index afterwards is faster, but that is not the case for InnoDB. DISABLE KEYS has no effect on InnoDB. It is in the TODO to speed up index creation. Maybe in 2005 it will be faster to add the indexes afterwards :). Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ Best regards, Mikhail. - Original Message - From: Krasimir_Slaveykov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 30, 2004 3:14 PM Subject: SQL and productivity Hello , I must create a temp table with 5 or more million records. I need it for creating some reports, so I need to create indexes too. My question is: When total time for work will be smaller? : 1-st case: I create table and create indexes immediately before filling data. 2-nd case: I create table, filling data and then create indexes? -- Best regards, Krasimir_Slaveykov mailto: [EMAIL PROTECTED] [EMAIL PROTECTED] |-| |/ * * *** * ** /| | *** *** *** *** *** *** ** *** //| |/// *** *** * * *** **** *** ///| |// *** *** *** *** *** *** ** ****** | |/ *** *** *** * *** /| |--- www.office1.bg --| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL Security
Hi, I'm newbie in MYSQL. Would please tell me about Mysql Security ? Generally you can find a lot of information about MySQL on http://www.mysql.com/doc/en/ About your question you can read here: http://www.mysql.com/doc/en/Privilege_system.html Mikhail. - Original Message - From: Timotius Alfa [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 28, 2004 5:16 AM Subject: MYSQL Security Hi All, I'm newbie in MYSQL. Would please tell me about Mysql Security ? I used Windows2000 for mysql server. thank you - Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: available spaci in InnoDB data files !??!
Hi, show table status; In the last field (Comment) you could find it. Mikhail. - Original Message - From: Tile Tatonko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 28, 2004 10:48 AM Subject: available spaci in InnoDB data files !??! I need help (ASAP). I'm using MySQL (4.0.15) with InnoDB. QUESTION: How can I check (or monitor) the available space in my InnoDB data files ??? my.cnf: ... innodb_data_file_path=ibdata1:650M;ibdata2:650M ... _ STOP MORE SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: data file too big
Is there any chance of loosing any data if I do that. Its a production machine with over 200 thousand customer tickets don't think so... Is there any other suggestions ? If you don't use InnoDB too much (you don't have a lot of data in InnoDB tables) then you can change size of InnoDB data and log files in my.cnf file. But be careful. Use correct way. http://www.innodb.com/ibman.php#Adding_and_removing Mikhail. - Original Message - From: Asif Iqbal [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 23, 2004 8:18 PM Subject: Re: data file too big On Fri, 23 Jan 2004, Mikhail Entaltsev wrote: Date: Fri, 23 Jan 2004 09:34:52 +0100 From: Mikhail Entaltsev [EMAIL PROTECTED] To: Gregory Newby [EMAIL PROTECTED], Asif Iqbal [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: data file too big I believe that this will flush those logs: mysql reset master; Is there any chance of loosing any data if I do that. Its a production machine with over 200 thousand customer tickets No.. It won't shrink any of InnoDB datafiles: 101Mib_logfile0 101Mib_logfile1 1.9Gibdata1 1.5Gibdata2 It could help if you have some MySQL binary logs: hostname-bin.001 ... hostname-bin.00N I don't. Is there any other suggestions ? Best regards, Mikhail. - Original Message - From: Gregory Newby [EMAIL PROTECTED] To: Asif Iqbal [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, January 22, 2004 11:32 PM Subject: Re: data file too big I believe that this will flush those logs: mysql reset master; -- Greg On Thu, Jan 22, 2004 at 05:23:07PM -0500, Asif Iqbal wrote: Hi All My data file has all these files (root)@webrt:/usr/local/mysql/data# du -sh * 25K ib_arch_log_00 3.0Kib_arch_log_02 3.0Kib_arch_log_04 101Mib_logfile0 101Mib_logfile1 1.9Gibdata1 1.5Gibdata2 2.0Kmy.cnf 70K mysql 2.0Knewdb 39M rt3 12K test 67K webrt.err 1.0Kwebrt.pid Is there anyway I can remove some of them so I can get some space back ? I am using mysql -4.0.13 on Solaris 8 -- Asif Iqbal PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Asif Iqbal PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: data file too big
I believe that this will flush those logs: mysql reset master; No.. It won't shrink any of InnoDB datafiles: 101Mib_logfile0 101Mib_logfile1 1.9Gibdata1 1.5Gibdata2 It could help if you have some MySQL binary logs: hostname-bin.001 ... hostname-bin.00N Best regards, Mikhail. - Original Message - From: Gregory Newby [EMAIL PROTECTED] To: Asif Iqbal [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, January 22, 2004 11:32 PM Subject: Re: data file too big I believe that this will flush those logs: mysql reset master; -- Greg On Thu, Jan 22, 2004 at 05:23:07PM -0500, Asif Iqbal wrote: Hi All My data file has all these files (root)@webrt:/usr/local/mysql/data# du -sh * 25K ib_arch_log_00 3.0Kib_arch_log_02 3.0Kib_arch_log_04 101Mib_logfile0 101Mib_logfile1 1.9Gibdata1 1.5Gibdata2 2.0Kmy.cnf 70K mysql 2.0Knewdb 39M rt3 12K test 67K webrt.err 1.0Kwebrt.pid Is there anyway I can remove some of them so I can get some space back ? I am using mysql -4.0.13 on Solaris 8 -- Asif Iqbal PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparing Table IDs
select t2.* from TableTwo t2 left join TableOne t1 on (t2.ID = t1.ID) where t1.ID is NULL - Original Message - From: Phillip S. Baker [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 23, 2004 7:26 AM Subject: Comparing Table IDs Greetings all, I have 3-4 tables of members on a website. However over time I have table one that lists 14000 records and table two lists 14121 records. What kind of SQL call would I put out to compare the two tables and find out which records in table two do not have a corresponding ID number in table one. Make sense?? Thanks Phillip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: temporary table in 4.0.17 on Windows W2000 and NT4 from client running xp
Hi, 1) Try to reproduce the situation with pure myscl client (command-line client). 2) There is no any MySQL parameter that change life time of temporary tables. Only two parameter that defines life time for connection to mysql: - interactive_timeout The number of seconds the server waits for activity on an interactive connection before closing it. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option to mysql_real_connect(). See also wait_timeout. - wait_timeout The number of seconds the server waits for activity on a not interactive connection before closing it. On thread startup SESSION.WAIT_TIMEOUT is initialized from GLOBAL.WAIT_TIMEOUT or GLOBAL.INTERACTIVE_TIMEOUT depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option). See also interactive_timeout. But by default they are quite big: 28800 seconds. Mikhail. - Original Message - From: Massimo Petrini [EMAIL PROTECTED] To: Mikhail Entaltsev [EMAIL PROTECTED] Sent: Friday, January 23, 2004 5:11 PM Subject: Re: temporary table in 4.0.17 on Windows W2000 and NT4 from client running xp 1) yes, I am sure because the problem is reproducible also with a sql , without to use the msaccess; if you use mysqlfront you can create the table, insert data, view data. But if you move your point of view on another table you lost the connection. 2) to connect a temporary table in mysql from msaccess, it is necessary to create the table on server not temporary, connect the table from msaccess, drop the table on server; this is necessary only once; after use a sql statement from msaccess, via sql pass through to create the temporary. Now youn can insert data and use as a normal table. When you close the msaccess session the temporary table is dropped immediately; otherwise if you leave the session msaccess opened, but not in use, after some minutes the table is dropped. I think there is a variable to set in my.ini, but I don't understand which is the correct name . So I don't understand. - Original Message - From: Mikhail Entaltsev [EMAIL PROTECTED] To: Massimo Petrini [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, January 22, 2004 2:16 PM Subject: Re: temporary table in 4.0.17 on Windows W2000 and NT4 2 questions: 1. Are you sure that client or someone between client and MySQl server doesn't close connection? Because the client have still the result of the query on the screen is not a proof that connection is still active. 2. How do you link your msaccess application with MySQL server? Are you sure that your application or someone between doesn't use connections pool? Mikhail. - Original Message - From: Massimo Petrini [EMAIL PROTECTED] To: Mikhail Entaltsev [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, January 22, 2004 1:35 PM Subject: Re: temporary table in 4.0.17 on Windows W2000 and NT4 We have a server running NT4 with mysql 4.017 (with the my.ini as in previuous mail). In the network the client (the single pc ) run msaccess application linked to mysql database. In such case we use a temporary table, created as follow CREATE TEMPORARY TABLE wrk_AlzateIntervalli(Lotto CHAR(10) NOT NULL,Indice CHAR(3) NOT NULL,NMisura SMALLINT NOT NULL,IndiceIntv CHAR(1) NOT NULL,ValMin REAL NULL,ValMax REAL NULL, PRIMARY KEY (Lotto, Indice, NMisura, IndiceIntv)); The statement sql is lunched from the client to the server; it create a temporary table used only from the client creator. Is clear for you ? Thanks - Original Message - From: Mikhail Entaltsev [EMAIL PROTECTED] To: Massimo Petrini [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, January 22, 2004 11:12 AM Subject: Re: temporary table in 4.0.17 on Windows W2000 and NT4 no; because the client have still the result of the query on the screen and on the server the thread is still existent in sleep mode. could you explain in details what is your system configuration? what is the client of MySQL server? - Original Message - From: Massimo Petrini [EMAIL PROTECTED] To: Mikhail Entaltsev [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, January 22, 2004 11:08 AM Subject: Re: temporary table in 4.0.17 on Windows W2000 and NT4 no; because the client have still the result of the query on the screen and on the server the thread is still existent in sleep mode. - Original Message - From: Mikhail Entaltsev [EMAIL PROTECTED] To: Massimo Petrini [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, January 22, 2004 10:05 AM Subject: Re: temporary table in 4.0.17 on Windows W2000 and NT4 Hi Massimo, May be after some minutes of inactivity client closes connection to MySQL server
Re: Odd Rounding?
Hi, I have email from Georg Richter about this problem in my MySQL archive: From: Georg Richter [EMAIL PROTECTED] Hi, From http://www.mysql.com/doc/en/Mathematical_functions.html: Note that the behaviour of ROUND() when the argument is half way between two integers depends on the C library implementation. Some round to the nearest even number, always up, always down, or always toward zero. If you need one kind of rounding, you should use a well-defined function like TRUNCATE() or FLOOR() instead. Regards Georg Mikhail. - Original Message - From: Andrew Kuebler [EMAIL PROTECTED] To: 'David Brodbeck' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, January 23, 2004 6:33 PM Subject: RE: Odd Rounding? Actually, no, it rounds to .01233. -Original Message- From: Andrew Kuebler [mailto:[EMAIL PROTECTED] Can anyone explain why: SELECT ROUND(.012345, 5) - .01234 Why doesn't mysql round the 5 up to .01235? How do I get it to round up? Oftentimes standard practice is to round up if the digit before the 5 is odd, and round down if it's even. (This averages out the bias you otherwise get with 5/4 rounding.) To see if this is what you're seeing, try rounding 0.012335 to 5 places...I bet it'll round up to 0.01234. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: temporary table in 4.0.17 on Windows W2000 and NT4
Hi Massimo, May be after some minutes of inactivity client closes connection to MySQL server? In this case MySQL deletes all temp tables that were created by this client. Best regards, Mikhail. - Original Message - From: Massimo Petrini [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, January 22, 2004 7:30 AM Subject: temporary table in 4.0.17 on Windows W2000 and NT4 We have a problem to have a long life for the temporary table on innodb mode. We create the table, but after some minutes of inactivity the sistem drops the table. My.ini in as follow. Which is the parameter to correct the problem ? Tks Massimi - # Example mysql config file. # Copy this file to c:\my.cnf to set global options # # One can use all long options that the program supports. # Run the program with --help to get a list of available options # This will be passed to all mysql clients [client] #password=my_password port=3306 #socket=MySQL # Here is entries for some specific programs # The following values assume you have at least 32M ram # The MySQL server [mysqld] port=3306 #socket=MySQL skip-locking set-variable=key_buffer=16K set-variable=max_allowed_packet=16M set-variable=thread_stack=64K set-variable=table_cache=4 set-variable=sort_buffer=1024K set-variable=net_buffer_length=2K #PER REPLICA server-id=19 master-host=pissarro report-host=pissarro master-user=root #skip-slave-start replicate-wild-ignore-table=OMTWRK.WRK_% replicate-wild-ignore-table=omtwrk.wrk_% set-variable=slave-net-timeout=172800 # Uncomment the following if you want to log updates #log-bin # Uncomment the following rows if you move the MySQL distribution to another # location basedir=C:\mysql\ #datadir=C:\AnnaNoBck\DATI\MySQL\data\ datadir=D:\applicaz\mysql\Data\ # log-error=d:\applicaz\mysql\data\mysql.err # Uncomment the following if you are NOT using BDB tables skip-bdb # Uncomment the following if you are using Innobase tables innodb_data_file_path=\InnoData\HymnOMT:10M:autoextend #innodb_data_home_dir=C:\AnnaNoBck\DATI\MySQL\InnoDB #innodb_log_group_home_dir=C:\AnnaNoBck\DATI\MySQL\InnoDB\InnoLog #innodb_log_arch_dir=C:\AnnaNoBck\DATI\MySQL\InnoDB\InnoLog innodb_data_home_dir=D:\applicaz\mysql\InnoDB innodb_log_group_home_dir=D:\applicaz\mysql\InnoDB\InnoLog innodb_log_arch_dir=D:\applicaz\mysql\InnoDB\InnoLog set-variable=innodb_mirrored_log_groups=1 set-variable=innodb_log_files_in_group=3 set-variable=innodb_log_file_size=5M set-variable=innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable=innodb_buffer_pool_size=16M set-variable=innodb_additional_mem_pool_size=10M set-variable=innodb_file_io_threads=4 set-variable=innodb_lock_wait_timeout=50 default-table-type=innodb #solo =4.0.14 permette di mantenere alla definizione di max_binlog_size la dimensione del file di relay #set-variable=max_relay_log_size=0 [mysqldump] quick set-variable=max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable=key_buffer=8M set-variable=sort_buffer=8M [myisamchk] set-variable=key_buffer=8M set-variable=sort_buffer=8M [mysqlhotcopy] interactive-timeout [WinMySQLAdmin] Server=C:/mysql/bin/mysqld-max-nt.exe QueryInterval=10 - 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: temporary table in 4.0.17 on Windows W2000 and NT4
no; because the client have still the result of the query on the screen and on the server the thread is still existent in sleep mode. could you explain in details what is your system configuration? what is the client of MySQL server? - Original Message - From: Massimo Petrini [EMAIL PROTECTED] To: Mikhail Entaltsev [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, January 22, 2004 11:08 AM Subject: Re: temporary table in 4.0.17 on Windows W2000 and NT4 no; because the client have still the result of the query on the screen and on the server the thread is still existent in sleep mode. - Original Message - From: Mikhail Entaltsev [EMAIL PROTECTED] To: Massimo Petrini [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, January 22, 2004 10:05 AM Subject: Re: temporary table in 4.0.17 on Windows W2000 and NT4 Hi Massimo, May be after some minutes of inactivity client closes connection to MySQL server? In this case MySQL deletes all temp tables that were created by this client. Best regards, Mikhail. - Original Message - From: Massimo Petrini [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, January 22, 2004 7:30 AM Subject: temporary table in 4.0.17 on Windows W2000 and NT4 We have a problem to have a long life for the temporary table on innodb mode. We create the table, but after some minutes of inactivity the sistem drops the table. My.ini in as follow. Which is the parameter to correct the problem ? Tks Massimi - # Example mysql config file. # Copy this file to c:\my.cnf to set global options # # One can use all long options that the program supports. # Run the program with --help to get a list of available options # This will be passed to all mysql clients [client] #password=my_password port=3306 #socket=MySQL # Here is entries for some specific programs # The following values assume you have at least 32M ram # The MySQL server [mysqld] port=3306 #socket=MySQL skip-locking set-variable=key_buffer=16K set-variable=max_allowed_packet=16M set-variable=thread_stack=64K set-variable=table_cache=4 set-variable=sort_buffer=1024K set-variable=net_buffer_length=2K #PER REPLICA server-id=19 master-host=pissarro report-host=pissarro master-user=root #skip-slave-start replicate-wild-ignore-table=OMTWRK.WRK_% replicate-wild-ignore-table=omtwrk.wrk_% set-variable=slave-net-timeout=172800 # Uncomment the following if you want to log updates #log-bin # Uncomment the following rows if you move the MySQL distribution to another # location basedir=C:\mysql\ #datadir=C:\AnnaNoBck\DATI\MySQL\data\ datadir=D:\applicaz\mysql\Data\ # log-error=d:\applicaz\mysql\data\mysql.err # Uncomment the following if you are NOT using BDB tables skip-bdb # Uncomment the following if you are using Innobase tables innodb_data_file_path=\InnoData\HymnOMT:10M:autoextend #innodb_data_home_dir=C:\AnnaNoBck\DATI\MySQL\InnoDB #innodb_log_group_home_dir=C:\AnnaNoBck\DATI\MySQL\InnoDB\InnoLog #innodb_log_arch_dir=C:\AnnaNoBck\DATI\MySQL\InnoDB\InnoLog innodb_data_home_dir=D:\applicaz\mysql\InnoDB innodb_log_group_home_dir=D:\applicaz\mysql\InnoDB\InnoLog innodb_log_arch_dir=D:\applicaz\mysql\InnoDB\InnoLog set-variable=innodb_mirrored_log_groups=1 set-variable=innodb_log_files_in_group=3 set-variable=innodb_log_file_size=5M set-variable=innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable=innodb_buffer_pool_size=16M set-variable=innodb_additional_mem_pool_size=10M set-variable=innodb_file_io_threads=4 set-variable=innodb_lock_wait_timeout=50 default-table-type=innodb #solo =4.0.14 permette di mantenere alla definizione di max_binlog_size la dimensione del file di relay #set-variable=max_relay_log_size=0 [mysqldump] quick set-variable=max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable=key_buffer=8M set-variable=sort_buffer=8M [myisamchk] set-variable=key_buffer=8M set-variable=sort_buffer=8M [mysqlhotcopy] interactive-timeout [WinMySQLAdmin] Server=C:/mysql/bin/mysqld-max-nt.exe QueryInterval=10 - 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql
Re: does mysqldump take care of stored procedures?
Hi, since MySQL stores stored procs in mysql db you need to make backup of mysql db. But it would be nice to have a possibility to make backup of stored procs in readable format. Best regards, Mikhail. - Original Message - From: Bing Du [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 4:42 PM Subject: does mysqldump take care of stored procedures? I'm testing backup and restore on MySQL 5.0. I did mysqldump first and then dropped a table and a stored procedure on purpose. After doing 'mysql -h host -u user -p database dump-file', the dropped table could be restored back in the database. But the dropped procedure stayed missing. How should I do to get the stored procedures backed up as well? Any suggestions/ideas appreciated. Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Storing currency values
Hi Asbjørn,
try to use replace function:
http://www.mysql.com/doc/en/String_functions.html
update YourTable set YourMoney = REPLACE('255,55', ',', '.') where ... ;
Best regards,
Mikhail.
- Original Message -
From: Asbjørn Konstad [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, January 12, 2004 11:33 AM
Subject: Storing currency values
Hello.
I've spent some day(s) probing the web to find a solution to this problem:
A table-column for storing currency - float(10,2).
As my users are punches the currency value like this 255,55, with a comma
as decimal point, MySQL stores this value as zero (0.00).
I quess the reason for this is that MySQL uses . as decimal point for
float-types (or any decimal value).
Does anyone know of a workaround for this problem??. Is there any way of
changing the decimal separator in MySQL??
Reg.
A Konstad
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.558 / Virus Database: 350 - Release Date: 02.01.2004
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Timestamp Problems
Hi Leandro, It is absolutly correct. Please read in doc about timestamp data type http://www.mysql.com/doc/en/DATETIME.html The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is updated automatically. Automatic updating of the first TIMESTAMP column occurs under any of the following conditions: a.. The column is not specified explicitly in an INSERT or LOAD DATA INFILE statement. b.. The column is not specified explicitly in an UPDATE statement and some other column changes value. (Note that an UPDATE that sets a column to the value it already has will not cause the TIMESTAMP column to be updated, because if you set a column to its current value, MySQL ignores the update for efficiency.) c.. You explicitly set the TIMESTAMP column to NULL. TIMESTAMP columns other than the first may also be set to the current date and time. Just set the column to NULL or to NOW(). You can set any TIMESTAMP column to a value different from the current date and time by setting it explicitly to the desired value. This is true even for the first TIMESTAMP column. You can use this property if, for example, you want a TIMESTAMP to be set to the current date and time when you create a row, but not to be changed whenever the row is updated later: a.. Let MySQL set the column when the row is created. This will initialize it to the current date and time. b.. When you perform subsequent updates to other columns in the row, set the TIMESTAMP column explicitly to its current value. On the other hand, you may find it just as easy to use a DATETIME column that you initialize to NOW() when the row is created and leave alone for subsequent updates. Best regards, Mikhail. - Original Message - From: Leandro Saggin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 2:12 PM Subject: Timestamp Problems Hello, I am having problems with columns timestamp, what it happens is the following: I created the following table using timestamp and inserted data in this table: create table con(cod integer not null primary key auto_increment, cod_access integer, hr_con timestamp(14), hr_descon timestamp(14), status char); insert into con values(null,1,'2004010712','20040107120030','D'); when executing select in the table, the data are correct select * from con; ++---+-+ --+--+ | cod | cod_access | hr_con | hr_descon | status | +-+--+-+ --+--+ | 1 | 1 | 2004010712 | 20040107120030 | D | +-+--+-- +--+-+ 1 row in set (0.01 sec) then I make one update in the column hr_descon update con set hr_descon='20040107120100'; there it is the problem, when bringing up to date the column hr_descon for the value informed in update, the column hr_con is brought up to date automatically for current date/time select * from con; select * from con; ++---+-+ --+--+ | cod | cod_access | hr_con | hr_descon | status | +-+--+-+ --+--+ | 1 | 1 | 20040107101056 | 20040107120100 | D| +-+--+-- +--+-+ 1 row in set (0.01 sec) I am using Operational System Solaris 9 on Sparc Platform and I tested in versions 3.23.47, 3.23.58 and 4.0.14 of mysql. If somebody to know what happens please helps I. Thanks!!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: dropping multiple tables with one command question
Hi Tom, Look at the docs: http://www.mysql.com/doc/en/DROP_TABLE.html Happy New Year and best regards, Mikhail. - Original Message - From: tom poe [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, January 01, 2004 8:33 AM Subject: dropping multiple tables with one command question Happy New Year!!! What's the command for dropping multiple tables? Example: 12 tables that all start with: phpbb_ What? Well, the install isn't going as planned. I am having to reinstall as I try to work out script path (I think) or some other piece of info that isn't entered correctly, yet. Anyway, DROP TABLE tablename; is one table. I want to drop all the tables without typing them, but using DROP TABLE phpbb*; doesn't work. Any help appreciated. Happy New Year, Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: dropping multiple tables with one command question
But you can do DROP table1; DROP table2; DROP table3; etc.. You can do even better: drop table table1, table2, table3; :) Best regards, Mikhail. - Original Message - From: Arne K. Haaje [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 02, 2004 12:29 PM Subject: Re: dropping multiple tables with one command question fredag 02. januar 2004, 12:11, skrev Egor Egorov: tom poe [EMAIL PROTECTED] wrote: What's the command for dropping multiple tables? Example: 12 tables that all start with: phpbb_ What? Well, the install isn't going as planned. I am having to reinstall as I try to work out script path (I think) or some other piece of info that isn't entered correctly, yet. Anyway, DROP TABLE tablename; is one table. I want to drop all the tables without typing them, but using DROP TABLE phpbb*; doesn't work. You can't use wildcards in the DROP TABLE command. But you can do DROP table1; DROP table2; DROP table3; etc.. Arne -- Arne K. Haaje | www.drlinux.no Bregneveien 9 | 1825 Tomter | M: 92 88 44 66 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select distinct from two columns
Hi, try to use group by clause in your select, e.g. select name, city from mytable group by name, city order by name, city; Best regards, Mikhail. - Original Message - From: Veysel Harun Sahin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 02, 2004 4:42 PM Subject: select distinct from two columns Hello, The two columns of my table are name and city. I am trying to do a list which contains only the different names. This can be done this by distinct keyword. But all the same names which have different cities also have to be included in my list. So I can not do this with a distinct keyword. Any ideas? Thanks in advance. __ New! Unlimited Access from the Netscape Internet Service. Beta test the new Netscape Internet Service for only $1.00 per month until 3/1/04. Sign up today at http://isp.netscape.com/register Act now to get a personalized email address! Netscape. Just the Net You Need. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my.cnf
Hi, Look at /usr/local/mysql/support-files directory. There are several examples of my.cnf files for different configurations there (my-huge.cnf, my-large.cnf, ...). Best regards, Mikhail. - Original Message - From: Kirti S. Bajwa [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 02, 2004 8:52 PM Subject: my.cnf Hello: I am installing MySQL 5.0.0 (on a fresh install RH9). I am following binary installation as outlined in official MySQL documentation with default location /usr/local. I sure appreciate if somebody on this list is kind enough to email or list contents of my.cnf file. Thanks. Kirti -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 5.0 binary question
Hi Rick, I have installed binary alpha 5.0 for Linux x86. And I am able to create stored procs. But I found couple problems with stored procs: 1. I have stored proc sp_GetData(param char(31)). When I call stored proc first time (with any parameter) it works just fine, but if I change parameter to param2 and rerun it then it returns me empty resultset. I have tried to call sp with param2 after reconnection to MySQL, and it works perfect. 2. Sometimes MySQL server crashed when I call sp_GetData. Sorry for bad description of the problems. I am still working on getting repeatable case. Mikhail. - Original Message - From: Rick Robinson [EMAIL PROTECTED] To: 'Mikhail Entaltsev' [EMAIL PROTECTED]; 'Mysql' [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 2:27 AM Subject: RE: 5.0 binary question Hi Mikhail- Thanks for responding; the presentation is very helpful; However, I think I may not have been clear enough - I was asking whether the binary alpha 5.0 that is now available from the web site had stored procedure support. I don't think it does as I don't see a catalog table mysql.proc defined and I cannot create any procedures. It's a little odd that 5.0 would be made available as a binary without the primary feature to exercise built into it. Oh well. I still feel like I'm missing something because that just doesn't make sense. Regards, R -Original Message- From: Mikhail Entaltsev [mailto:[EMAIL PROTECTED] Sent: Monday, December 29, 2003 7:18 PM To: [EMAIL PROTECTED]; Mysql Subject: Re: 5.0 binary question Hi, I have found slides show http://mysql.progen.com.tr/events/uc2003/slides/stored-procedures.pdf and examples in mysql-5.0/mysql-test/t/ - sp.test - sp-error.test Best regards, Mikhail. - Original Message - From: Rick Robinson [EMAIL PROTECTED] To: Mysql [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 12:14 AM Subject: 5.0 binary question I downloaded the 5.0 preview alpha binary for Win32 and installed it - so far, so good. But I don't see anything in the doc on the stored procedure capability (how-to/syntax) and I can't create one within 5.0 (using SQL99 syntax - at least I think/hope so). Is stored procedure capability not available in the provided binaries (maybe it's only in the source tree?)? Just want to make sure I'm not missing something. Thanks, Rick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does 5.0 contain the features of 4.1 and 4.1.1?
Hi Terence, Just a dumb question perhaps, but I am looking at upgrading to 5.0, and as we are happy with the performance and features on 4.1, can we expect the same features to have the same level of stability on 5.0? I wouldn't expect the same level of stability if you would like to use stored procs. to have the same level of stability on 5.0? Also are the features of 4.1.1 also included? Heikki wrote: For MySQL users release 5.0.0 is a milestone: you can now write stored procedures in MySQL. InnoDB in this MySQL release is essentially the same as in 4.1.1, with the bug fixes of 4.0.17 included. Later 5.0.x versions will probably include new space saving table formats for InnoDB. IMPORTANT NOTE: if you upgrade to InnoDB-4.1.1 or higher (like to 5.0.0), you cannot downgrade to a version lower than 4.1.1 any more! That is because earlier versions of InnoDB are not aware of multiple tablespaces. Best regards, Mikhail. - Original Message - From: Terence [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 9:58 AM Subject: Does 5.0 contain the features of 4.1 and 4.1.1? Hi List, Just a dumb question perhaps, but I am looking at upgrading to 5.0, and as we are happy with the performance and features on 4.1, can we expect the same features to have the same level of stability on 5.0? Also are the features of 4.1.1 also included? I need to start looking at stored procedures and hence the question...thanks! Terence -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fwd: Re: MySQL 5.0.0 has been released]
Peter, Thank you for respond and sorry... It was my mistake. Today I have compared it once again: on 4.0.14 ~11.5 min on 5.0.0 ~13.0 min Thank you, Mikhail. - Original Message - From: Peter Zaitsev [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, December 29, 2003 8:46 PM Subject: [Fwd: Fwd: Re: MySQL 5.0.0 has been released] Mike, This is not what is generally expected. Could you please tell me which settings did you run the test with and what was CREATE TABLE for the table which had worse insert speed (at least one). You may also wish to send me test program you have been using as a test. - Forwarded message from Mikhail Entaltsev [EMAIL PROTECTED] - From: Mikhail Entaltsev [EMAIL PROTECTED] To: MySQL list [EMAIL PROTECTED] Subject: Re: MySQL 5.0.0 has been released Date: Mon, 29 Dec 2003 14:45:24 +0100 Hi, First of all, thanks a lot. I have installed MySQL 5.0.0 on my test machine (Suse Linux 8.2 kernel 2.4.20 i586) and have populated data (~40 InnoDB tables with max. 50 000 000 rows) So far it works stable and I didn't have any big problem, but I have found that speed of inserting data is ~3-5 times slower in comparison with version 4.0.14 (the same my.cnf config-file and the same PC). Is it because binary distribution of 5.0 version is not optimized? Thank you in advance, Mikhail. - End forwarded message - Regards, Sergei -- Peter Zaitsev, Full-Time Developer MySQL AB, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem Installation
Hi You need to install first rpm with dynamic client libraries (including 3.23.x libraries). You can find them on http://www.mysql.com/downloads/mysql-4.0.html Best regards, Mikhail. - Original Message - From: Carlos Andre Moura de Amorim [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 4:20 PM Subject: Problem Installation I have red hat 9.0, i don't to install mysql RPM appear: erro: Failed dependencies: libmysqlclient.so.10 is needed by perl-DBD-MySQL-2.1021-3 Please Help me!! -- *** Carlos Andre Moura de Amorim -- Funcionario UNCISAL -- Tecnico de Informatica CEFET-AL -- Acad. de Mecicina UNCISAL -- Fone: (0xx82) 9904-0117 *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem Installation
Try to change localhost in connection string to real IP address or 127.0.0.1. Actually it is not the fix of the problem it is a workaround. Best regards, Mikhail. - Original Message - From: Carlos Andre Moura de Amorim [EMAIL PROTECTED] To: Mikhail Entaltsev [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 4:42 PM Subject: Re: Problem Installation Thanks, I installed, but i don't to connect!! bashmysql bashERROR 2002: Can't connect to local MySQL server through socket 'var/lib/mysql.sock' (2) On Tue, 30 Dec 2003, Mikhail Entaltsev wrote: Hi You need to install first rpm with dynamic client libraries (including 3.23.x libraries). You can find them on http://www.mysql.com/downloads/mysql-4.0.html Best regards, Mikhail. - Original Message - From: Carlos Andre Moura de Amorim [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 4:20 PM Subject: Problem Installation I have red hat 9.0, i don't to install mysql RPM appear: erro: Failed dependencies: libmysqlclient.so.10 is needed by perl-DBD-MySQL-2.1021-3 Please Help me!! -- *** Carlos Andre Moura de Amorim -- Funcionario UNCISAL -- Tecnico de Informatica CEFET-AL -- Acad. de Mecicina UNCISAL -- Fone: (0xx82) 9904-0117 *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- *** Carlos Andre Moura de Amorim -- Funcionario UNCISAL -- Tecnico de Informatica CEFET-AL -- Acad. de Mecicina UNCISAL -- Fone: (0xx82) 9904-0117 *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.0 has been released
Hi, First of all, thanks a lot. I have installed MySQL 5.0.0 on my test machine (Suse Linux 8.2 kernel 2.4.20 i586) and have populated data (~40 InnoDB tables with max. 50 000 000 rows) So far it works stable and I didn't have any big problem, but I have found that speed of inserting data is ~3-5 times slower in comparison with version 4.0.14 (the same my.cnf config-file and the same PC). Is it because binary distribution of 5.0 version is not optimized? Thank you in advance, Mikhail. On Wed, 24 Dec 2003, Michael Widenius wrote: Hi, MySQL 5.0.0, a new version of the popular Open Source/Free Software Database Management System, has been released. It is now available in source and binary form for a number of platforms from our download pages at http://www.mysql.com/downloads/ and mirror sites. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 5.0 binary question
Hi, I have found slides show http://mysql.progen.com.tr/events/uc2003/slides/stored-procedures.pdf and examples in mysql-5.0/mysql-test/t/ - sp.test - sp-error.test Best regards, Mikhail. - Original Message - From: Rick Robinson [EMAIL PROTECTED] To: Mysql [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 12:14 AM Subject: 5.0 binary question I downloaded the 5.0 preview alpha binary for Win32 and installed it - so far, so good. But I don't see anything in the doc on the stored procedure capability (how-to/syntax) and I can't create one within 5.0 (using SQL99 syntax - at least I think/hope so). Is stored procedure capability not available in the provided binaries (maybe it's only in the source tree?)? Just want to make sure I'm not missing something. Thanks, Rick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW TABLE STATUS without LIKE
Hi, I have noticed also that SHOW TABLE STATUS became slower in 5 times at least (from 1 sec to 5-6 secs). Since I've switched to version 3.23.53 to version 4.0.14. But I have changed type of all my tables (~30 tables) from MyISAM to InnoDB as well. How long does an ls command in the database directory take? less than 1 sec. Thanks, Mikhail. - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: Jozsa Boti [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, November 24, 2003 5:36 PM Subject: Re: SHOW TABLE STATUS without LIKE Hi! Please reply to the list, not to me personally, so that others can follow this discussion. Thanks. At 13:21 +0200 11/21/03, Jozsa Boti wrote: Hi! How an i get the last Check-time of a specific table without using LIKE statements? There isn't another way. There's the SHOW TABLE STATUS command, but if there are many tables in a database this command is very slow, even if a specific table name is after the LIKE statment. Slow? How many tables are we talking about? I'm talking about nearly 20 000 tables in a database. In this case a SHOW TABLE STATUS commande takes about 20-30 seconds. Hm, that is pretty slow. How long does an ls command in the database directory take? Thanks, Boti -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: guru needed - large configuration MySQL-Max InnoDB - 4.0.16
Hi, innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend innodb_buffer_pool_size = 6G it looks like your InnoDB tablespace is less than 2G. If it is true then IMHO it doesn't make any sense to allocate 6G for innodb_buffer_pool. InnoDB won't use it. Best regards, Mikhail. - Original Message - From: nm [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 30, 2003 10:17 AM Subject: guru needed - large configuration MySQL-Max InnoDB - 4.0.16 Hi there I was wondering I you can send your comments and remarks for this my.cnf file o.s. redhat9 tables 20Mb mem: 8gb server dedicated to mysql max_connections=2000/3000 uses only innodb tables my.cnf [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 max_connections=2000 #max_connections=3000 innodb_data_home_dir = /var/lib/mysql/ innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend innodb_log_group_home_dir = /var/lib/mysql/ innodb_log_arch_dir = /var/lib/mysql/ innodb_buffer_pool_size = 6G innodb_additional_mem_pool_size = 20M innodb_log_file_size = 100M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 -- I found few documents on mysql tuning. Do you have any suggestions? Experience? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Troubles with InnoDB perfomance.
show statusHi all, I am running MySQL version 4.0.14-standard-log on Suse Linux 2.4.20 i686. All tables in my db are InnoDB tables. Today morning I've got troubles with perfomance... Generally all queries are slower in 4-5 times than usually. Also I see that number of deadlocks is in 10-15 times more than usually. I didn't change any code that is working with MySQL, didn't upgrade any software on the server, didn't change any configuration parameters. Last week everything worked perfectly... On weekend I've executed 3 commands: 1. OPTIMIZE TABLES list of all my tables 2. ANALYZE TABLE for every table 3. CHECK TABLE for every table No errors. I've checked /var/log/messages and MySQL log file as well. Everything looks good. I have no idea what's happend :( Any suggestions, comments, question are really appreciated. Thanks in advace, Mikhail. Below are outputs of 'show variables' and 'show status' commands. show variables (121 Records) Variable_nameValue back_log 50 basedir /usr/local/mysql-standard-4.0.14-pc-linux-i686/ binlog_cache_size 32768 bulk_insert_buffer_size 1048576 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 ON connect_timeout 5 convert_character_set datadir /usr/local/mysql/data/ default_week_format 0 delay_key_write OFF delayed_insert_limit 100 delayed_insert_timeout 300 delayed_queue_size 1000 flush OFF flush_time 0 ft_boolean_syntax + -()~*:| ft_min_word_len 4 ft_max_word_len 254 ft_max_word_len_for_sort 20 ft_stopword_file (built-in) have_bdb NO have_crypt YES 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 20971520 innodb_buffer_pool_size 335544320 innodb_data_file_path ibdata1:2000M;ibdata2:2000M;ibdata3:2000M;ibdata4:2000M;ibdata5:2000M;ibdata 6:10M:autoextend innodb_data_home_dir /usr/local/mysql/data/ innodb_file_io_threads 4 innodb_force_recovery 0 innodb_thread_concurrency 8 innodb_flush_log_at_trx_commit 1 innodb_fast_shutdown ON innodb_flush_method O_DSYNC innodb_lock_wait_timeout 50 innodb_log_arch_dir /usr/local/mysql/data/ innodb_log_archive OFF innodb_log_buffer_size 8388608 innodb_log_file_size 83886080 innodb_log_files_in_group 2 innodb_log_group_home_dir /usr/local/mysql/data/ innodb_mirrored_log_groups 1 innodb_max_dirty_pages_pct 90 interactive_timeout 28800 join_buffer_size 520192 key_buffer_size 2097152 language /usr/local/mysql-standard-4.0.14-pc-linux-i686/share/mysql/english/ large_files_support ON local_infile ON locked_in_memory OFF log OFF log_update OFF log_bin ON log_slave_updates OFF log_slow_queries ON log_warnings ON long_query_time 10 low_priority_updates OFF lower_case_table_names OFF 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_relay_log_size 0 max_seeks_for_key 4294967295 max_sort_length 1024 max_user_connections 0 max_tmp_tables 32 max_write_lock_count 4294967295 myisam_max_extra_sort_file_size 268435456 myisam_max_sort_file_size 2147483647 myisam_repair_threads 1 myisam_recover_options OFF myisam_sort_buffer_size 67108864 net_buffer_length 16384 net_read_timeout 30 net_retry_count 10 net_write_timeout 60 new OFF open_files_limit 0 pid_file /usr/local/mysql/data/blackcat.pid log_error port 3306 protocol_version 10 read_buffer_size 1044480 read_only OFF read_rnd_buffer_size 1044480 rpl_recovery_rank 0 query_cache_limit 1048576 query_cache_size 16777216 query_cache_type ON server_id 1 slave_net_timeout 3600 skip_external_locking ON skip_networking OFF skip_show_database OFF slow_launch_time 2 socket /tmp/mysql.sock sort_buffer_size 4194296 sql_mode 0 table_cache 256 table_type MYISAM thread_cache_size 8 thread_stack 126976 tx_isolation REPEATABLE-READ timezone GMT tmp_table_size 41943040 tmpdir /tmp/ version 4.0.14-standard-log wait_timeout 28800 show status (132 Records) Variable_nameValue Aborted_clients 1094 Aborted_connects 9 Bytes_received 3370224049 Bytes_sent 1615721075 Com_admin_commands 1536 Com_alter_table 0 Com_analyze 1 Com_backup_table 0 Com_begin 3360480 Com_change_db 625 Com_change_master 0 Com_check 1 Com_commit 3360480 Com_create_db 0 Com_create_function 0 Com_create_index 0 Com_create_table 362871 Com_delete 8 Com_delete_multi 0 Com_drop_db 0 Com_drop_function 0 Com_drop_index 0 Com_drop_table 245 Com_flush 0 Com_grant 0 Com_ha_close 0 Com_ha_open 0 Com_ha_read 0 Com_insert 407 Com_insert_select 362737 Com_kill 2 Com_load 0 Com_load_master_data 0 Com_load_master_table 0 Com_lock_tables 8 Com_optimize 0 Com_purge 0 Com_rename_table 0 Com_repair 0 Com_replace 0 Com_replace_select 0 Com_reset 2 Com_restore_table 0 Com_revoke 0 Com_rollback 0 Com_savepoint 0
Re: Troubles with InnoDB perfomance.
Forgot to mention... OS is not swapping... The load on MySQL server is the same... Usually my system has ~100 selects/sec and 0.2 inserts/sec. And I found that during inserts MySQL uses 100% of CPU, before it used ~5-10% CPU maximum... I understand that it's difficult to say what is going wrong without checking. But may be you can give me some advices or you can point me something that can help me to understand the reason of my troubles. I appreciate any help. Mikhail. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Time zones and grouping
Hi, If I understand you correctly the this query should help you: select date_format(date_add(DateTime, interval 7 hour), %Y%m%d) as Date, count(1) from Table1 group by Date order by Date Best regards, Mikhail. - Original Message - From: Graeme B. Davis [EMAIL PROTECTED] To: Graeme B. Davis [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, October 14, 2003 2:36 PM Subject: Time zones and grouping Does anyone know any way to be able to group by day/week/month for an adjusted date (GMT - EDT). I store dates as GMT in the db, but sometimes I want to count the number of rows per EDT day instead of GMT day. I can't figure out how to do this. Ideas? Thanks, Graeme - Original Message - From: Graeme B. Davis [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 10, 2003 2:12 PM Subject: Converting GMT stored data into other zones | I was wondering if anyone had any tricks on the best way to handle | localizing time zones in MySql? | | For example, all my datetime data is stored as GMT. I have written | tools to query the data based upon whatever local time zone you want | to use. So, for example if I query for 2003-10-1 to 2003-10-2 in EDT, | it would really query 2003-9-30 20:00:00 to 2003-10-1 20:00:00. | | BUT, I've run into a problem when you want to GROUP BY | DAYOFYEAR(datefield) for example, I want it to do the grouping by EDT | and not GMT -- is this possible? | | Regards, | | Graeme | | | -- | MySQL General Mailing List | For list archives: http://lists.mysql.com/mysql | To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] | | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database Design and Bianry Operations
Hi, I would create one field (char(200) or char(255)) for all criteria. After that I will store: in the first byte of that string Y if hotel has swimming pool, N - doesn't have, Q - no idea in the second byte of that string Y if hotel has TV, N - doesn't have, Q - no idea in the third byte of that string Y if hotel has fitness club, N - doesn't have, Q - no idea ... After that I will be able to select from that table data according to my criteria: select * from Hotel where Criteria like 'NY_' It means select all hotels that: swimming pool - don't have, TV - have fitness club - doesn't matter Best regards, Mikhail. - Original Message - From: Lee Denny [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 14, 2003 3:17 PM Subject: Database Design and Bianry Operations Hello, I'm after some advice on database design: I've got an object - for an example a hotel - and I want to keep information about this hotel, criteria that it either has or hasn't (TV, swimming pool etc). I want to search on criteria and return the most appropriate match. Bearing in mind I've currently got over 200 criteria and want to expand this, how should approach the design of my criteria table. Should I have a table with Hotel Id and then a char(1) (Y/N) field for each criteria and then a seperate look-up table for criteria name. I get the feeling there is a more efficient method using binary operations (only one field populated with zeros and ones) but I can't find anything to help in the manuals. Any thoughts? Lee Denny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Setting the timezone
Hi, I am using these lines in my.cnf at [mysqld_safe] section. [mysqld_safe] timezone = GMT It works fine for me. Best regards, Mikhail. - Original Message - From: Juan Antonio Ruiz Zwollo [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, October 13, 2003 5:38 PM Subject: Setting the timezone Hi. I am trying to change the timezone for MySQL (version 3.23.56) but it does not seem to work. Currently the timezone is set to MDT (I think its the default for MySQL). I added the following line to /etc/my.cnf at the [mysqld] section: set-variable = timezone=CST But when I try to stop and start the daemon, it does not want to start: --- Starting mysqld daemon with databases from /usr/local/mysql/var 031013 10:04:04 mysqld ended --- It will only start if I remove the timezone line from my.cnf, but then the timezone stays at MDT. Thanks for your time. Kind regards, Juan Antonio Ruiz Zwollo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Setting the timezone
Hi, Do you use version 4 or 3? I am using version 4.0.14. I thought that I need to use set-variable = timezone = CST for it to work under MySQL 3. MySQL docs: ...set-variable = variable=value This is equivalent to --set-variable variable=value on the command-line. Please note that --set-variable is deprecated since MySQL 4.0; as of that version, program variable names can be used as option names. On the command line, just use --variable=value. In an option file, use variable=value... http://www.mysql.com/doc/en/Option_files.html IMHO the problem is that you have put timezone variable into [mysqld] section, but you need to put it into [mysqld_safe] section. But I am not sure. Check it, please. Best regards, Mikhail. - Original Message - From: Juan Antonio Ruiz Zwollo [EMAIL PROTECTED] To: Mikhail Entaltsev [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, October 13, 2003 11:01 PM Subject: Re: Setting the timezone Hi: Do you use version 4 or 3? I thought that I need to use set-variable = timezone = CST for it to work under MySQL 3. Also, does anybody know if CST is recognized? Or do I have to use GMT-6 or something like that? Thanks for your time and help. King regards, Juan Antonio - Original Message - From: Mikhail Entaltsev [EMAIL PROTECTED] To: Juan Antonio Ruiz Zwollo [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, October 13, 2003 10:50 AM Subject: Re: Setting the timezone Hi, I am using these lines in my.cnf at [mysqld_safe] section. [mysqld_safe] timezone = GMT It works fine for me. Best regards, Mikhail. - Original Message - From: Juan Antonio Ruiz Zwollo [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, October 13, 2003 5:38 PM Subject: Setting the timezone Hi. I am trying to change the timezone for MySQL (version 3.23.56) but it does not seem to work. Currently the timezone is set to MDT (I think its the default for MySQL). I added the following line to /etc/my.cnf at the [mysqld] section: set-variable = timezone=CST But when I try to stop and start the daemon, it does not want to start: --- Starting mysqld daemon with databases from /usr/local/mysql/var 031013 10:04:04 mysqld ended --- It will only start if I remove the timezone line from my.cnf, but then the timezone stays at MDT. Thanks for your time. Kind regards, Juan Antonio Ruiz Zwollo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Does InnoDB use any of these variables?
Hi, Does InnoDB use any of these variables: bulk_insert_buffer_size join_buffer_size key_buffer_size read_buffer_size read_rnd_buffer_size sort_buffer_size table_cache thread_concurrency Thanks in advance, Mikhail. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: joining 4 tables
Hi, You need to use UNION, but it's not possible in version 3.23.* So it's better to upgrade MySQL server first. SELECT table1.cod FROM table1 LEFT JOIN table4 ON table1.cod=table4.cod WHERE table4.cod IS NULL UNION SELECT table2.cod FROM table2 LEFT JOIN table4 ON table2.cod=table4.cod WHERE table4.cod IS NULL UNION SELECT table3.cod FROM table3 LEFT JOIN table4 ON table3.cod=table4.cod WHERE table4.cod IS NULL Best regards, Mikhail. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Brent Baisley [EMAIL PROTECTED] Sent: Monday, September 15, 2003 1:15 PM Subject: Re: joining 4 tables Thank you all for replying. Brent your code is nice but I'ts not what I want. In where clause the equality is not true because its table has diferent cod fields anyway. Can you help me with that? - Original Message - From: Brent Baisley [EMAIL PROTECTED] To: $B-)*/2 (B $B#!32'2(B [EMAIL PROTECTED]) Cc: [EMAIL PROTECTED] Sent: Friday, September 12, 2003 3:43 PM Subject: Re: joining 4 tables SELECT table4.cod, table1.cod, table2.cod, table3.cod FROM table4 LEFT JOIN table1 ON table4.cod=table1.cod LEFT JOIN table1 ON table4.cod=table2.cod LEFT JOIN table1 ON table4.cod=table3.cod WHERE table1.cod IS NULL AND table2.cod IS NULL AND table3.cod IS NULL I think that should do it, I didn't test it. What you are doing is a left join for all the tables on table4. The left join will retain all the records of table4, and then you filter out any records that have no value in the fields for the other tables. On Friday, September 12, 2003, at 04:37 AM, $B-)*/2(B $B#!32'2(B wrote: Hello list I have a problem. I have 4 tables (in Mysql 3.23). All of them have a filed named cod. I want a query to find which cod of table1, table2, table3 is/are not in table4. cod values in table1, table2, table3 are diferent. Thank you Nikos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Simple Stored Procedure Emulation with PHP/mySQL
Hi, IMHO it's nice idea... http://www.ashleyit.com/blogs/brentashley/archives/000339.html Best regards, Mikhail. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to recovery the root password?
Edwin, check this link. Hope it will help you. http://www.mysql.com/doc/en/Resetting_permissions.html Best regards, Mikhail. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, September 12, 2003 7:22 PM Subject: How to recovery the root password? Dear List. This morning we made some changes in the user field of the mysql database, we changes the root password there, after that we can not get access to our mysql server with the new root password and with the old password neither. How can we do? Is there any procedures for recovery the password or to access to the server in this case? Thank you for your help. EDWIN LIMACHI N. DATACOM - Instalaciones TSE - INFONET BOLIVIA Phone. 591-2-2123978 Movil: 591-715-29967 Fax: 591-2-2123975 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Temporary tables
Hi, I've found the phrase in MySQL documentation http://www.mysql.com/doc/en/Temporary_table_problems.html You can't use temporary tables more than once in the same query. For example, the following doesn't work. mysql SELECT * FROM temporary_table, temporary_table AS t2; Does it mean that I can't use THE SAME temporary table twice in THE SAME query? Or does it mean that I can't use more than 1 temporary table in the query at all? Thanks in advance, Mikhail. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB slow?
Paul, if your MySQL server is runnign under Linux then try to play with innodb_flush_method variable. I've changed it to O_DSYNC and InnoDB became ~ 9 times faster (Suse 8.2 Linux 2.4.20-4GB i386). Also check that you didn't allocate too much memory (OS shouldn't swap). Best regards, Mikhail. - Original Message - From: Paul Gallier [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 2:57 AM Subject: InnoDB slow? I'm running a large database which is currently using MyISAM. There are approximately 300 million rows in about a dozen tables totaling 7GB of storage. The system is averaging 257 querries per second, probably peaking at around 500-600+ during busy times. We're running a single database with one programming doing insertions/updates and a web server doing only selects. The problem is that the insertions/updates tend to bog down a bit when the web side gets busy. I figured switching to InnoDB might help with the row-locking support, however, on my test system things ran very slow using InnoDB instead of MyISAM. This was just running the script handling the inserts/updates with no web access, but a test that took 2 minutes 34 seconds using MyISAM tables took 10 minutes 37 seconds using InnoDB. I setup enough disk space to hold the tables and had seemingly sufficient ammounts of mmeory configured for InnoDB, so I cannot understand the drastic slowdown. Any advise would be greatly appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB slow?
Paul, Where did you find information about 'littlesync' and 'nosync'? In InnoDB manual I found only ** This is only relevant on Unix. The default value for this is fdatasync. Then InnoDB uses fsync() to flush both the data and log files. If O_DSYNC is specified, InnoDB uses O_SYNC to open and flush the log files, but uses fsync() to flush the data files. If O_DIRECT is specified (available on some Linux versions starting from MySQL-4.0.14), InnoDB uses O_DIRECT to open the data files, and uses fsync() to flush both the data and log files. Note that InnoDB does not use fdatasync() or O_DSYNC because there have been problems with them on many Unix flavors. ** Mikhail. - Original Message - From: Paul Gallier To: Mikhail Entaltsev ; [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 4:24 AM Subject: Re: InnoDB slow? Thanks for the info. I'm running MySQL 4.0.14 under Redhat 8.0 / Linux 2.4.20. Here are the timings I ended up with from playing with innodb_flush_method: innodb_flush_method=fdatasync (default) 10 minutes 37 seconds innodb_flush_method=littlesync 10 minutes 22 seconds innodb_flush_method=O_DSYNC 5 minutes 18 seconds innodb_flush_method=nosync3 minutes 12 seconds MyISAM tables instead of InnoDB 2 minutes 34 seconds Now of course, the question is what potential harm am I looking at by using nosync or o_dsync? Mikhail Entaltsev wrote: Paul, if your MySQL server is runnign under Linux then try to play with innodb_flush_method variable. I've changed it to O_DSYNC and InnoDB became ~ 9 times faster (Suse 8.2 Linux 2.4.20-4GB i386). Also check that you didn't allocate too much memory (OS shouldn't swap). Best regards, Mikhail. - Original Message - From: Paul Gallier [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 2:57 AM Subject: InnoDB slow? I'm running a large database which is currently using MyISAM. There are approximately 300 million rows in about a dozen tables totaling 7GB of storage. The system is averaging 257 querries per second, probably peaking at around 500-600+ during busy times. We're running a single database with one programming doing insertions/updates and a web server doing only selects. The problem is that the insertions/updates tend to bog down a bit when the web side gets busy. I figured switching to InnoDB might help with the row-locking support, however, on my test system things ran very slow using InnoDB instead of MyISAM. This was just running the script handling the inserts/updates with no web access, but a test that took 2 minutes 34 seconds using MyISAM tables took 10 minutes 37 seconds using InnoDB. I setup enough disk space to hold the tables and had seemingly sufficient ammounts of mmeory configured for InnoDB, so I cannot understand the drastic slowdown. Any advise would be greatly appreciated.
InnoDB locking: Different behavior on 3.23.55 and 4.0.13
Hi I have 2 MySQL servers: Server1 is 3.23.55-max-log Server2 is 4.0.13-standard-log Let's assume that we have 2 connections (Conn1 and Conn2) and table test: CREATE TABLE `test` ( `id` int(3) NOT NULL auto_increment, `name` char(10) default '', PRIMARY KEY (`id`) ) TYPE=InnoDB; and put some data in it: insert into test (id, name) values (1, 'cat'); insert into test (id, name) values (2, 'dog'); insert into test (id, name) values (3, 'bird'); I execute queries in the order: Conn1: LOCK TABLES test WRITE; then Conn2: select * from test; On the Server1 Conn2 is locked and it will be locked even more than innodb_lock_wait_timeout (that is not correct, right?). But on the Server2 Conn2 returns results immediately. I guess that Server2 is wrong. What do you think? Thanks, Mikhail.
Re: InnoDB locking: Different behavior on 3.23.55 and 4.0.13
Heikki, I just tested this. You are probably using the query cache in 4.0. Then SELECT can return immediately without acquiring any locks. Thank you very much. You are absolutly right. Of course, it can be discussed if the query cache, too, should respect LOCK TABLES. I am forwarding this to Sanja. IMHO current behaviour is absolutly correct. Mikhail. - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, July 24, 2003 11:43 PM Subject: Re: InnoDB locking: Different behavior on 3.23.55 and 4.0.13 Mikhail, I just tested this. You are probably using the query cache in 4.0. Then SELECT can return immediately without acquiring any locks. Of course, it can be discussed if the query cache, too, should respect LOCK TABLES. I am forwarding this to Sanja. Thank you, Heikki - Original Message - From: Mikhail Entaltsev [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Thursday, July 24, 2003 3:44 PM Subject: InnoDB locking: Different behavior on 3.23.55 and 4.0.13 --=_NextPart_000_0119_01C351F2.03137C50 Content-Type: text/plain; charset=koi8-r Content-Transfer-Encoding: quoted-printable Hi I have 2 MySQL servers:=20 Server1 is 3.23.55-max-log Server2 is 4.0.13-standard-log Let's assume that we have 2 connections (Conn1 and Conn2) and table = test: CREATE TABLE `test` ( `id` int(3) NOT NULL auto_increment, `name` char(10) default '', PRIMARY KEY (`id`) ) TYPE=3DInnoDB; and put some data in it: insert into test (id, name) values (1, 'cat'); insert into test (id, name) values (2, 'dog'); insert into test (id, name) values (3, 'bird'); I execute queries in the order: Conn1:=20 LOCK TABLES test WRITE; then=20 Conn2: select * from test; On the Server1 Conn2 is locked and it will be locked even more=20 than innodb_lock_wait_timeout (that is not correct, right?). But on the Server2 Conn2 returns results immediately. I guess that Server2 is wrong. What do you think? Thanks, Mikhail. --=_NextPart_000_0119_01C351F2.03137C50-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: possible query?
Try to use 'concat()' function.
if ('stop' 'start', stop, concat(stop,'+++'))
Best regards,
Mikhail.
- Original Message -
From: Fabrizio Tivano [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, June 10, 2003 13:07
Subject: possible query?
hello dear all,
i need to make a select on mysql with IF funcion:
if ('stop' 'start', stop, '+++')
where start and stop are timestamps.
and [+++] is an error char.
Actually my select result are:
+--++
| data | start | stop |
+--+---++
| 20030301 | 74621 |+++ |
| 20030302 | 74840 | 181211 |
but i have a question:
Is possible, and if yes how,
to make so that query generate a result like:
+--++
| data | start | stop |
+--+---++
| 20030301 | 74621 |0020 +++ |
| 20030302 | 74840 | 181211 |
where: if the stop time is (less than) start time
i can print stop time and error char together.
Thanks in advance !
fabrizio
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bug: MySQL 4.0.13 crashes during simultaneous execution ALTER TABLE ... ENABLE KEYS and SHOW TABLE STATUS statements.
Hi all, My MySQL crashed during simultaneous execution of ALTER TABLE ... ENABLE KEYS and SHOW TABLE STATUS statements. There were 2 threads: #3 and #4. In thread #4 I executed: 4 Query DROP TABLE IF EXISTS History 4 Query CREATE TABLE History ... 4 Query ALTER TABLE History DISABLE KEYS 4 Query LOCK TABLES History WRITE 4 Query INSERT INTO History VALUES ... 4 Query ALTER TABLE History ENABLE KEYS In thread #3 I executed: 3 Query SHOW TABLE STATUS And it looks like thread #3 was locked until INSERT INTO History VALUES ... finished. After that SHOW TABLE STATUS statement executed and when Mysql tried to execute ALTER TABLE History ENABLE KEYS it crashed. Below there are error-log and query-log files: == /usr/local/mysql/bin/mysqld: ready for connections. Version: '4.0.13-standard-log' socket: '/tmp/mysql.sock' port: 3306 mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=402653184 read_buffer_size=2093056 sort_buffer_size=2097144 max_used_connections=3 max_connections=100 threads_connected=4 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 802415 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x87631a8 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbfe1e958, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x80702cb 0x8282488 0x8283a23 0x8280ca4 0x827ed89 0x80d028f 0x80d1636 0x807b487 0x807e166 0x80797ad 0x80791ed 0x8078a0f 0x827fc3c 0x82b53fa New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x8773ae8 = ALTER TABLE History ENABLE KEYS thd-thread_id=4 Successfully dumped variables, if you ran with --log, take a look at the details of what thread 4 did to cause the crash. In some cases of really bad corruption, the values shown above may be invalid. The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0 030606 09:00:37 mysqld restarted 030606 9:00:37 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 1885531442 InnoDB: Doing recovery: scanned up to log sequence number 0 1885549589 030606 9:00:37 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Last MySQL binlog file position 0 45770, file name ./saturn-bin.001 030606 9:00:38 InnoDB: Flushing modified pages from the buffer pool... 030606 9:00:38 InnoDB: Started /usr/local/mysql/bin/mysqld: ready for connections. Version: '4.0.13-standard-log' socket: '/tmp/mysql.sock' port: 3306 == /usr/local/mysql/bin/mysqld, Version: 4.0.13-standard-log, started with: Tcp port: 3306 Unix socket: /tmp/mysql.sock Time Id CommandArgument 4 Query DROP TABLE IF EXISTS History 4 Query CREATE TABLE History (...) TYPE=MyISAM 4 Query ALTER TABLE History DISABLE KEYS 4 Query LOCK TABLES History WRITE 4 Query INSERT INTO History VALUES (...) 3 Query SHOW TABLE STATUS 030606 9:00:37 4 Query ALTER TABLE History ENABLE KEYS /usr/local/mysql/bin/mysqld, Version: 4.0.13-standard-log, started with: Tcp port: 3306 Unix socket: /tmp/mysql.sock Time Id CommandArgument == Mikhail. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UPDATE doesn't work
Hi mysql SELECT prezzo, totale FROM ordini WHERE numordine=1157; mysql UPDATE ordini SET prezzo = prezzo/1.024, totale = totale/1.024 WHERE numordine1385 AND dataord'2003-06-01'; You are selecting data for numordine = 1157, but are updating all records that have numordine 1385. 1157 1385. So you are not updating these records. Best regards, Mikhail. - Original Message - From: PaT! [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, June 06, 2003 19:00 Subject: UPDATE doesn't work Hi I'm running MySQL 4.0.13 MS Windows XP Prof I've this problem: A db with a table called 'ordini' mysql desc ordini; +-+---+--+-++--- -+ | Field | Type | Null | Key | Default| Extra | +-+---+--+-++--- -+ | id | int(11) | | PRI | NULL | auto_increment | | numordine | int(10) | | | 0 | | | descrizione | varchar(150) | | || | | marca | varchar(250) | | || | | modello | varchar(250) | | || | | generica| varchar(250) | | || | | quantita| int(10) | | | 0 | | | prezzo | decimal(10,3) | | | 0.000 | | | type| varchar(10) | | || | | dataord | date | | | -00-00 | | | nominativo | varchar(200) | | || | | totale | decimal(10,3) | | | 0.000 | | | fromprev| int(11) | YES | | NULL | | | ptype | varchar(20) | YES | | NULL | | | idutente| int(11) | YES | | NULL | | | codcliente | varchar(8)| YES | | NULL | | | datamod | date | YES | | NULL | | +-+---+--+-++--- -+ 17 rows in set (0.00 sec) I need to update some values in two fields 'prezzo' and 'totale' These are the values before the UPDATE mysql SELECT prezzo, totale FROM ordini WHERE numordine=1157; +++ | prezzo | totale | +++ | 6.795 | 6.795 | | 13.835 | 13.835 | | 17.578 | 17.578 | | 62.500 | 62.500 | +++ 4 rows in set (0.02 sec) this is the UPDATE query mysql UPDATE ordini SET prezzo = prezzo/1.024, totale = totale/1.024 WHERE numordine1385 AND dataord'2003-06-01'; Query OK, 1649 rows affected (0.10 sec) Rows matched: 1650 Changed: 1649 Warnings: 0 These are the values after the UPDATE mysql SELECT prezzo, totale FROM ordini WHERE numordine=1157; +++ | prezzo | totale | +++ | 6.795 | 6.795 | | 13.835 | 13.835 | | 17.578 | 17.578 | | 62.500 | 62.500 | +++ 4 rows in set (0.02 sec) Why the values didn't update? The same query applied on a single row works perfectly. Any help is appreciated PaT! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Timestamp field in the InnoDB table
Devi, As I understand you need to update timestamp field in some tables. And you have only 4-5 milliseconds for it? Is it correct? What is the problem? Best regards, Mikhail. - Original Message - From: Devi Annisetty [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; Mikhail Entaltsev [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, October 21, 2002 3:08 PM Subject: RE: Timestamp field in the InnoDB table Hi, This is almost my requirement.I have to update the exact timestamps(most critical for me) and I will have only 4- 5 mts available to update different tables. So how can I do that with out much time difference. I am new to MYSQL. Any help would be greatly appreciated. Thanks Devi - 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: Timestamp field in the InnoDB table
Heikki, thank you for your response. Is this a big problem? Well, actually it is not very big problem for me now, because I already know about that feature. ;) But I would prefer to have timestamp that means the end of the transaction. Anyway it would be very useful to have some notices in the documentation. Thank you. Best regards, Mikhail. - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: Mikhail Entaltsev [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, October 21, 2002 1:20 PM Subject: Re: Timestamp field in the InnoDB table Mikhail, - Original Message - From: Mikhail Entaltsev [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, October 21, 2002 1:11 PM Subject: Timestamp field in the InnoDB table Hi, I have found one unclear place for me regarding to the timestamp field in the InnoDB table. Please, explain me am I correct or not. Let's say we have a table Test with 'timestamp' field: CREATE TABLE `Test` ( `id` int(3) NOT NULL auto_increment, `UpdateDate` timestamp(14) NOT NULL, PRIMARY KEY (`id`) ) TYPE=InnoDB; and I try to update one row in this table: update Test set UpdateDate = NULL where id = 1; Let's say I started 'update' statement at 15:00:00. But the row with id = 1 is blocked by another transaction, so 'update' statement needs to wait till the end of the transaction. After 10 sec the block on the record with id = 1 is released. So my 'update' finished. select UpdateDate from Test where id = 1 gives me 2002102115, but I would expect 20021021150010. So what do you think about it? looks like the MySQL interpreter assigns the clock time value to the timestamp field before calling the InnoDB backend. Is this a big problem? Thanks in advance. Best regards, Mikhail. Regards, Heikki 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
Timestamp field in the InnoDB table
Hi, I have found one unclear place for me regarding to the timestamp field in the InnoDB table. Please, explain me am I correct or not. Let's say we have a table Test with 'timestamp' field: CREATE TABLE `Test` ( `id` int(3) NOT NULL auto_increment, `UpdateDate` timestamp(14) NOT NULL, PRIMARY KEY (`id`) ) TYPE=InnoDB; and I try to update one row in this table: update Test set UpdateDate = NULL where id = 1; Let's say I started 'update' statement at 15:00:00. But the row with id = 1 is blocked by another transaction, so 'update' statement needs to wait till the end of the transaction. After 10 sec the block on the record with id = 1 is released. So my 'update' finished. select UpdateDate from Test where id = 1 gives me 2002102115, but I would expect 20021021150010. So what do you think about it? Thanks in advance. Best regards, Mikhail. 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: Update problem in MySQL
Juha, select dep.Ref, dep.ActionDate as Departure, arr.ActionDate as Arrival, dlv.ActionDate as Delivery from Table1 dep left join Table1 arr on (dep.Ref = arr.Ref and arr.Timestamp = 'ARR') left join Table1 dlv on (dep.Ref = dlv.Ref and dlv.Timestamp = 'DLV') where dep.Ref = arr.Ref and dep.Timestamp = 'DEP' Best regards, Mikhail. - Original Message - From: Morsky Juha [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 26, 2002 10:06 AM Subject: Update problem in MySQL Do anyone has a idea how to Insert (create SQLQuery) from onetable to an other table as decripted under: Here is the orginal Table: Table1: Ref | Timestamp | ActionDate ---+-+ 001 | DEP + 2002/01/02 ---+-+ 001 | ARR + 2002/01/04 ---+-+ 001 | DLV + 2002/01/15 ---+-+ 002 | ARR + 2002/02/02 ---+-+ 002 | DEP+ 2002/02/03 ---+-+ 002 | DLV + 2002/02/18 ---+-+ 003 | DEP + 2002/01/12 ---+-+ 003 | ARR + 2002/01/14 ---+-+ 003 | DLV + 2002/01/25 I should update Table one to Table2, which means that insted of 9 rows I should have row per Ref Table2: Ref | Departure | Arrival| Delivery ---+-+--+ 001 | 2002/01/02 + 2002/01/04 | 2002/01/15 ---+-+--+ 002 | 2002/02/02 + 2002/02/03 | 2002/02/18 ---+-+--+ 003 | 2002/01/12 + 2002/01/14 | 2002/01/25 ---+-+--+ Thanks for your help Juha - 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: JOIN-Question
Peter, If you would like to get such resultset namefield1 field2 field3field4 field5 field6 - test 12 5 6 9 10 test 12 5 6 1112 test2 34 NULLNULL 1516 then you need to use left join for t2, but based only on these information I couldn't help you to implement this query (I don't know how to group by rows). Please, give more information about why do you need exactly this resultset. Best regards, Mikhail. - Original Message - From: Peter Stöcker [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 19, 2002 11:03 AM Subject: JOIN-Question Hi there! I have a question on JOINs. First the system: table1: name: t1 entries: name field1 field2 test 1 2 test2 3 4 table2: name t2 entries: name field3 field4 test 5 6 test 7 8 table3: name t3 entries: name field5 field6 test 9 10 test 11 12 test2 13 14 test2 15 16 And here the problem: with the query SELECT a.*, b.*, c.* FROM t1 a INNER JOIN t2 b USING(name) INNER JOIN t3 c USING(name) WHERE b.field3=7 OR c.field6=16; I only get 1 entry with name=test. By using LEFT JOIN I only get name=test either. For sure, because in table2 there is no test2 entry. The only 2 ways I know to get also test2: 1. INSERT INTO t2 VALUES(test2,NULL,NULL) 2. temporary table But I don't want to have such dummy entries or a temporary table. Does anybody know what I have to do to with: SELECT a.*,b.*,c.* FROM ?? WHERE b.field3=5 OR c.field6=16 the result: namefield1 field2 field3field4 field5 field6 - test 12 5 6 9 10 test 12 5 6 1112 test2 34 NULLNULL 1516 I hope that someone can help me! Thank a lot, Peter __ WEB.DE MyPage - Ultimatives Kommunikationstool! Ihre Message sofort online! Domain aenderbar! http://www.das.ist.aber.ne.lustige.sache.ms/ - 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: Re: JOIN-Question
Peter, Try this query: SELECT a.name, a.field1, a.field2, b.field3, b.field4,c.field5, c.field6 FROM t1 as a left join t2 as b on (a.name = b.name) left join t3 as c on (a.name = c.name) having b.field3=5 OR c.field6=16 Best regards, Mikhail. - Original Message - From: Peter Stöcker [EMAIL PROTECTED] To: Mikhail Entaltsev [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, September 19, 2002 11:56 AM Subject: Re: Re: JOIN-Question Hi Mikhail! The query should be released automaticly and it shoulb be something like: SELECT a.*,b.*,c.* FROM ?? WHERE b.field3=5 OR c.field6=16 I want to have all entries wich fit to the condition. But at this time I don't know weather there is a entry with name=test or test2 or not. So when there is none it should return some NULLs otherwise the entries. I already tries using LEFT JOINs, but it doesn't work the way I want it to. Do you think there is a proper way to solve this problem? CU, Peter Mikhail Entaltsev [EMAIL PROTECTED] schrieb am 19.09.02 11:36:16: Peter, If you would like to get such resultset namefield1 field2 field3field4 field5 field6 - test 12 5 6 9 10 test 12 5 6 1112 test2 34 NULLNULL 1516 then you need to use left join for t2, but based only on these information I couldn't help you to implement this query (I don't know how to group by rows). Please, give more information about why do you need exactly this resultset. Best regards, Mikhail. - Original Message - From: Peter Stöcker [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 19, 2002 11:03 AM Subject: JOIN-Question Hi there! I have a question on JOINs. First the system: table1: name: t1 entries: name field1 field2 test 1 2 test2 3 4 table2: name t2 entries: name field3 field4 test 5 6 test 7 8 table3: name t3 entries: name field5 field6 test 9 10 test 11 12 test2 13 14 test2 15 16 And here the problem: with the query SELECT a.*, b.*, c.* FROM t1 a INNER JOIN t2 b USING(name) INNER JOIN t3 c USING(name) WHERE b.field3=7 OR c.field6=16; I only get 1 entry with name=test. By using LEFT JOIN I only get name=test either. For sure, because in table2 there is no test2 entry. The only 2 ways I know to get also test2: 1. INSERT INTO t2 VALUES(test2,NULL,NULL) 2. temporary table But I don't want to have such dummy entries or a temporary table. Does anybody know what I have to do to with: SELECT a.*,b.*,c.* FROM ?? WHERE b.field3=5 OR c.field6=16 the result: namefield1 field2 field3field4 field5 field6 - test 12 5 6 9 10 test 12 5 6 1112 test2 34 NULLNULL 1516 I hope that someone can help me! Thank a lot, Peter __ WEB.DE MyPage - Ultimatives Kommunikationstool! Ihre Message sofort online! Domain aenderbar! http://www.das.ist.aber.ne.lustige.sache.ms/ - 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 __ Jetzt testen für 1 Euro! Ihr All-in-one-Paket! https://digitaledienste.web.de/Club/?mc=021106 - 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: date field - default value = current date
Check TIMESTAMP type in the documentation. Best regards, Mikhail. - Original Message - From: neal [EMAIL PROTECTED] To: mySQL [EMAIL PROTECTED] Sent: Wednesday, September 18, 2002 10:32 AM Subject: date field - default value = current date Is there a wat to have mySQL auto fill in the current date into my dateCreated field? In SQLServer I would simply specify the getDate() method as a default value. I presume I would use the analogous MySQL function curDate() but it does not appear to work for me. Any suggestions? - 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: Timestamp issue
Prafulla, please check documentation http://www.mysql.com/doc/en/DATETIME.html ... The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is updated automatically. ... You need to change type of fields from timestamp to datetime. Best regards, Mikhail. - Original Message - From: Prafulla Girgaonkar [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, September 17, 2002 1:19 PM Subject: Timestamp issue Mysql Version:: Server version 3.23.47-nt Protocol version10 Connection . via named pipe UNIX socket MySQL OS:: Windows NT 4.0 Workstation Hello Following is the description of the problem being faced. Problem: The first timestamp column in a table is set to current date-time value as soon as we update one or more columns in the table. Example: The guest information is stored in a table named GUEST. The schema for it is as below. create table guest (guestID int, arrivalDate timestamp, departureDate timestamp, status char(1)); Above table is populated using following queries. insert into guest values (1, '2002-09-30', '2002-10-14', 'Y'); insert into guest values (2, '2002-09-20', '2002-10-01', 'Y'); insert into guest values (3, '2002-09-15', '2002-09-25', 'Y'); insert into guest values (4, '2002-09-12', '2002-09-20', 'Y'); If we use following query to update the status from Y to N, then arrivalDate column is set to CURRENT(system) date-time value. update guest set status = 'N'; Does anybody have any information on this issue? Thanx in advance. Prafulla - 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: error in left join
Roman, try to execute this query: select e.cid,d.amount,dd.amount from eee e left join ddd d on (d.cid=e.cid and d.c=2 and d.c=3) left join ddd dd on (dd.cid=e.cid and dd.c=3) and after that try to group by them manualy. What do you have as result? Best regards, Mikhail. - Original Message - From: Roman Menshikov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, September 04, 2002 9:47 AM Subject: error in left join Hello mysql team, Server info: Release: mysql-3.23.52 (Official MySQL RPM) Server: /usr/bin/mysqladmin Ver 8.23 Distrib 3.23.52, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.52 Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 1 day 14 hours 59 min 28 sec Threads: 1 Questions: 430690 Slow queries: 11 Opens: 58 Flush tables: 1 Open tables: 38 Queries per second avg: 3.068 System: Linux ic-tm-s-slave-0001.intercaf.ru 2.4.7-10enterprise #1 SMP Thu Sep 6 16:48:20 EDT 2001 i686 unknown Architecture: i686 Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc Let's say I have 2 tables: create table eee(cid int not null primary key, l varchar(10)); create table ddd(cid int not null, c int not null, amount int); And some data: insert into eee values(1,'1'); insert into eee values(2,'2'); insert into eee values(3,'3'); insert into eee values(4,'4'); insert into eee values(5,'5'); insert into ddd values(2,1,10); insert into ddd values(2,2,100); insert into ddd values(2,3,1020); insert into ddd values(4,1,1020); insert into ddd values(4,2,1020); insert into ddd values(4,3,1020); The problem is: when I issue the query: select e.cid,sum(d.amount),sum(dd.amount) from eee e left join ddd d on d.cid=e.cid and d.c=2 and d.c=3 left join ddd dd on dd.cid=e.cid and dd.c=3 group by e.cid; I've got: +-+---++ | cid | sum(d.amount) | sum(dd.amount) | +-+---++ | 1 | 0 | 0 | | 2 | 1120 | 2040 | | 3 | 0 | 0 | | 4 | 2040 | 2040 | | 5 | 0 | 0 | +-+---++ while I shuld get quite other result: +-+---++ | cid | sum(d.amount) | sum(dd.amount) | +-+---++ | 1 | 0 | 0 | | 2 | 1120 | 1020 | | 3 | 0 | 0 | | 4 | 2040 | 1020 | | 5 | 0 | 0 | +-+---++ See the difference in the 3rd columns??? -- Best regards, Roman 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 - 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: Order by date
Xavier, select ID, description, if (ifnull(date1, 20201231) ifnull(date2, 20201231), ifnull(date1, 20201231), ifnull(date2, 20201231)) as date from MyTable order by date I didn't test it, but it should work. Any questions? don't hesitate to ask. Best regards, Mikhail. - Original Message - From: Xavier NOPRE [EMAIL PROTECTED] To: Mailing list MySql [EMAIL PROTECTED] Sent: Tuesday, September 03, 2002 11:18 AM Subject: Order by date Hi, I have a query from tables that give me records with two date date1 and date2. How can I order the records by the most recent date (between the two date) for each records ? Example: Data: IDdescriptiondate1date2 1 aaa09/03/02 09/05/02 2 bbb 09/04/02 3 ccc 09/02/02 09/10/02 Result: IDdescriptiondate 3 ccc 09/02/02 1 aaa09/03/02 2 bbb09/04/02 Thanks Xavier - 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: SELECT into :varname
Wouter, set @ID = 173633; select @Name := Name from MyTable where ID = @ID; select @Name; Best regards, Mikhail. - Original Message - From: Wouter van Vliet [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Wouter @ Witbier [EMAIL PROTECTED] Sent: Friday, August 30, 2002 5:59 PM Subject: SELECT into :varname Heey Folks, I've been wondering about something for quite a while now.. I know that it's possible in some DBMS's to select a value in some variable, and then in some other query use it again. Is this also possiblein MySQL and if so, how? Thanks, Wouter - 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: sql-select
select m.Name, mp1.Points as Round1, mp2.Points as Round2, mp3.Points as Round3, mp4.Points as Round4, mp5.Points as Round5 from member m, member_points mp1, member_points mp2, member_points mp3, member_points mp4, member_points mp5 where m.Id = mp1.Member__id and m.Id = mp2.Member__id and m.Id = mp3.Member__id and m.Id = mp4.Member__id and m.Id = mp5.Member__id and m.Name = 'stefan' and mp1.Round = 1 and mp2.Round = 2 and mp3.Round = 3 and mp4.Round = 4 and mp5.Round = 5 Best regards, Mikhail. - Original Message - From: Stefan Sturm [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 27, 2002 12:31 PM Subject: sql-select Hello, I have a small(hopefully) sql-query problem. I have 2 table like this: Table member: Id number, Name varchar, Table member_points: Id number Member__id number (fk from member_table) Round number Points number Now one example: In table one is a member called stefan. In table member_points are 5 sets for member stefan with his points for every round. Now I need one select, to give me his Name and all his points in _one_ row like this: Name round 1 round 2 round 3 round 4 Stefan 10 15 8 12 Who can help me? Greetings and thanks, Stefan - 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 select query, need some clues to speed it up please ...
As first step, try to optimize table with help of OPTIMIZE TABLE MyTable command. Any progress? Best regards, Mikhail. - Original Message - From: David BORDAS [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 27, 2002 12:34 PM Subject: Slow select query, need some clues to speed it up please ... Hi all, I've discovered that we have a select query that blocked all others query to this table. 99% of query used indexs, no join ... But this one is a problem : SELECT Field1,Field2,Field3,Field4,Field5,Field6,Field7 FROM MyTable WHERE Field7=15 AND Field2=0 AND (Field3 LIKE '%John%' OR Field4 LIKE '%John%' OR Field5 LIKE '%John') ORDER BY Field6 LIMIT 0,20; I know that '(Field3 LIKE '%John%' OR Field4 LIKE '%John%' OR Field5 LIKE '%John')' part is the problem. Table desc : CREATE TABLE MyTable ( Field1 int(10) unsigned NOT NULL auto_increment, Field2 int(10) unsigned NOT NULL default '0', Field3 varchar(50) NOT NULL default '', Field4 varchar(50) NOT NULL default '', Field5 text NOT NULL, Field6 bigint(20) NOT NULL default '0', Field7 int(10) unsigned NOT NULL default '0', PRIMARY KEY (Field1), KEY ReplyTo_Numero (Field2,Field1), KEY indexF (Field7,Field2,Field6) ) TYPE=MyISAM PACK_KEYS=1; select count(*) from MyTabe ; 4381036 Table is growing about 10 000 record a day ... So, what can i do to have better performance ? Create a big index for Field3,Field4, Field5 ? Not a very good idea i think because it'll be a very very big index and Field 3-5 are varchar or text ... But why not ... Perhaps doing 3 select, one with Field3, one with Field4 and the other with Field5 and then doing some code in the app for joining the 3 select results. Or did i miss something in the select syntax that can help me ? Thanks David - 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 select query, need some clues to speed it up please ...
Already done something like optimize : myisamchk -v -a -S --sort-records=1 ../data/jeuxvideo/MyTable And? No result? Mikhail. P.S. Can you send a copy to mysql-list, please. - Original Message - From: David BORDAS [EMAIL PROTECTED] To: Mikhail Entaltsev [EMAIL PROTECTED] Sent: Tuesday, August 27, 2002 1:30 PM Subject: Re: Slow select query, need some clues to speed it up please ... ME As first step, try to optimize table with help of ME OPTIMIZE TABLE MyTable ME command. ME Any progress? Already done something like optimize : myisamchk -v -a -S --sort-records=1 ../data/jeuxvideo/MyTable David - 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 select query, need some clues to speed it up please ...
Check the query plan EXPLAIN SELECT Field1,Field2,Field3,Field4,Field5,Field6,Field7 FROM MyTable WHERE Field7=15 AND Field2=0 AND (Field3 LIKE '%John%' OR Field4 LIKE '%John%' OR Field5 LIKE '%John') ORDER BY Field6 LIMIT 0,20; Mikhail. - Original Message - From: David Bordas [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 27, 2002 1:34 PM Subject: Re: Slow select query, need some clues to speed it up please ... ME As first step, try to optimize table with help of ME OPTIMIZE TABLE MyTable ME command. ME Any progress? Already done something like optimize : myisamchk -v -a -S --sort-records=1 ../data/jeuxvideo/MyTable David 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 - 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 select query, need some clues to speed it up please ...
Can you send result of the query, please: select * from MyTable PROCEDURE ANALYSE(); Mikhail. - Original Message - From: David Bordas [EMAIL PROTECTED] To: Mikhail Entaltsev [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, August 27, 2002 2:13 PM Subject: Re: Slow select query, need some clues to speed it up please ... From: Mikhail Entaltsev [EMAIL PROTECTED] ME Check the query plan ME EXPLAIN SELECT Field1,Field2,Field3,Field4,Field5,Field6,Field7 FROM ME MyTable WHERE ME Field7=15 AND Field2=0 AND (Field3 LIKE '%John%' OR Field4 LIKE '%John%' OR ME Field5 LIKE '%John') ORDER BY Field6 LIMIT 0,20; | table | type | possible_keys | key| key_len | ref | rows | Extra | ++--+---++-+-+-- -++ | MyTable | ref | ReplyTo_Numero,indexF | indexF | 8| const,const | 51145 | where used | 1 row in set (0.01 sec) Table desc : CREATE TABLE MyTable ( Field1 int(10) unsigned NOT NULL auto_increment, Field2 int(10) unsigned NOT NULL default '0', Field3 varchar(50) NOT NULL default '', Field4 varchar(50) NOT NULL default '', Field5 text NOT NULL, Field6 bigint(20) NOT NULL default '0', Field7 int(10) unsigned NOT NULL default '0', PRIMARY KEY (Field1), KEY ReplyTo_Numero (Field2,Field1), KEY indexF (Field7,Field2,Field6) ) TYPE=MyISAM PACK_KEYS=1; Thanks David - 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 select query, need some clues to speed it up please ...
IMHO the problem is in this condition ...Field5 LIKE '%John'... Can you remove it from query and try again? Mikhail. - Original Message - From: David Bordas [EMAIL PROTECTED] To: Mikhail Entaltsev [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, August 27, 2002 2:49 PM Subject: Re: Slow select query, need some clues to speed it up please ... - Original Message - From: Mikhail Entaltsev [EMAIL PROTECTED] ME Can you send result of the query, please: ME select * from MyTable PROCEDURE ANALYSE(); Here you are : mysql select * from MyTable PROCEDURE ANALYSE(); +--+--+--++---+- --+-+--+ |Field_name |Min_length|Max_length|Empties_or_zeros|Nulls |Avg_value_or_avg_length|Std |Optimal_fieldtype | +--+--+--++---+- --+-+--+ |MyTable.Field1| 1| 7 | 0 | 0|3988722.5930 |0. |MEDIUMINT(7) UNSIGNED NOT NULL| |MyTable.Field2| 1| 7 |573688 | 0|3212734.0355 |0. |MEDIUMINT(7) UNSIGNED NOT NULL| |MyTable.Field3| 1| 47 | 204 | 0|8.2074 |NULL|VARCHAR(47) NOT NULL | |MyTable.Field4| 1| 50 |71 | 0|24.5052 |NULL|VARCHAR(50) NOT NULL | |MyTable.Field5| 1| 51723 | 1 | 0|174.5134 |NULL|TEXT NOT NULL | |MyTable.Field6| 12| 14 | 3809075 | 0|63688541485.2995 |0. |BIGINT(14) UNSIGNED NOT NULL | |MyTable.Field7| 1| 4 | 0 | 0|1646.5029 |2596.7715 |SMALLINT(4) UNSIGNED NOT NULL | +--+--+--++---+- --+-+--+ NB: Table desc : CREATE TABLE MyTable ( Field1 int(10) unsigned NOT NULL auto_increment, Field2 int(10) unsigned NOT NULL default '0', Field3 varchar(50) NOT NULL default '', Field4 varchar(50) NOT NULL default '', Field5 text NOT NULL, Field6 bigint(20) NOT NULL default '0', Field7 int(10) unsigned NOT NULL default '0', PRIMARY KEY (Field1), KEY ReplyTo_Numero (Field2,Field1), KEY indexF (Field7,Field2,Field6) ) TYPE=MyISAM PACK_KEYS=1; Thanks David - 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 select query, need some clues to speed it up please ...
As I understand... After removing Field5 LIKE '%John' condition the query works much faster. But it is different query... :) I mean that queries return different results. On other hand you can't change type of Field5 because you have a row with 51723 symbols in Filed5. :( So... question: Do you actually need this condition? ;) Best regards, Mikhail. - Original Message - From: David Bordas [EMAIL PROTECTED] To: Mikhail Entaltsev [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, August 27, 2002 3:31 PM Subject: Re: Slow select query, need some clues to speed it up please ... From: Mikhail Entaltsev [EMAIL PROTECTED] ME IMHO the problem is in this condition ME ...Field5 LIKE '%John'... ME Can you remove it from query and try again? In fact it was Field5 LIKE '%John%', but Field5 LIKE '%John' and Field5 LIKE '%John%' don't change query speed at all except about 0.01s .. I try remove it and i was surprised, speed grow and do not decrease. Perhaps because mysql has to analyse more rows before to have enough rows ... Thanks David - 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 select query, need some clues to speed it up please ...
As I understand... After removing Field5 LIKE '%John' condition the query works much faster. Nop, sorry i'm not clear, the query works mush slower ... :( That's strange. You removed condition (which can only _increase_ number of records in resultset) and query works much slower... May be occasionally anybody run another big query in parallel? in other case I have no idea... :( Mikhail. - Original Message - From: David Bordas [EMAIL PROTECTED] To: Mikhail Entaltsev [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, August 27, 2002 4:28 PM Subject: Re: Slow select query, need some clues to speed it up please ... From: Mikhail Entaltsev [EMAIL PROTECTED] As I understand... After removing Field5 LIKE '%John' condition the query works much faster. Nop, sorry i'm not clear, the query works mush slower ... But it is different query... :) I mean that queries return different results. Yep ... On other hand you can't change type of Field5 because you have a row with 51723 symbols in Filed5. :( I know that and that's a problem ... So... question: Do you actually need this condition? ;) I need it yes, but i'm thinking about doing 2 or 3 queries or modify some code of my app about this search ... Thanks David - 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 select query, need some clues to speed it up please ...
Try to remove two 'LIKE' conditions. query 3: SELECT Field1,Field2,Field3,Field4,Field5,Field6,Field7 FROM MyTable WHERE Field7=15 AND Field2=0 AND Field3 LIKE '%John%' ORDER BY Field6 LIMIT 0,20; What do you have now??? Mikhail. - Original Message - From: David Bordas [EMAIL PROTECTED] To: Mikhail Entaltsev [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, August 27, 2002 5:16 PM Subject: Re: Slow select query, need some clues to speed it up please ... ME In this case... ME Can you try again? Sure. query 1 : SELECT Field1,Field2,Field3,Field4,Field5,Field6,Field7 FROM MyTable WHERE Field7=15 AND Field2=0 AND (Field3 LIKE '%John%' OR Field4 LIKE '%John%' OR Field5 LIKE '% John%' ) ORDER BY Field6 LIMIT 0,20; query 2: SELECT Field1,Field2,Field3,Field4,Field5,Field6,Field7 FROM MyTable WHERE Field7=15 AND Field2=0 AND (Field3 LIKE '%John%' OR Field4 LIKE '%John%' ) ORDER BY Field6 LIMIT 0,20; Results : query | time 1 0.78s 2 1.20s 1 0.77s 2 1.21s 1 0.78s 2 1.22s IMHO there are 2 ways: 1. It will work much faster. 2. It won't change speed of execution significantly. Euh Third one ? Remove a condition slow the query ... Perhaps i've a problem somewhere, but where .. Perhaps with some cache variables ? I don't know. This is the my.cnf : # The MySQL server [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=2M 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 Server have got 1Go and run only mysql ... Table have 4M rows and index. Mytable.MYD : 1109586816 bytes Mytable.MYI : 93065216 Thanks David - 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: Exact word search
Robert, ... WHERE field LIKE '%search%' means everything that contains search. Examples: abcsearch, searchabc, abcsearchabc. But it doesn't mean abcearch, searcabc, searc. Best regards, Mikhail. - Original Message - From: Robert Macwange [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, August 21, 2002 4:18 PM Subject: Exact word search Hello y'all, I guess this is easy, but it is defeating me. I am having a problem with this query: $keyword = search; SELECT * FROM table WHERE field LIKE '%$keyword%' The problem is I don't want sear or earch but exactly search. What am I missing? .. Regards, Robert (Newbie) - 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: JOIN query with three tables.
Nicolas, select a.AnswerID,a.QuestionID,a.Value,a.AccountID,p.FirstName,p.LastName from Answer a left join Account c on (a.AccountID = c.AccountID), Person p where c.PersonID = p.PersonID Best regards, Mikhail. - Original Message - From: Nicolas Ivering [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, August 19, 2002 10:35 AM Subject: JOIN query with three tables. Hi, this is probably a simple query but I tried all I can think of without finding a solution. I have three tables, CREATE TABLE Answer ( AnswerID INTEGER NOT NULL AUTO_INCREMENT, QuestionID INTEGER NOT NULL, Value DECIMAL(15,4) NOT NULL, AccountID INTEGER, PRIMARY KEY (AnswerID) ) TYPE=InnoDB; CREATE TABLE Account ( AccountID INTEGER NOT NULL AUTO_INCREMENT, PersonID INTEGER NOT NULL, AccountGroupID INTEGER NOT NULL, UserName VARCHAR(50) NOT NULL, Password VARCHAR(20) NOT NULL, PRIMARY KEY (AccountID) ) TYPE=InnoDB; DROP TABLE Person; CREATE TABLE Person ( PersonID INTEGER NOT NULL AUTO_INCREMENT, FirstName VARCHAR(255) NOT NULL, LastName VARCHAR(255) NOT NULL, PRIMARY KEY (PersonID) ) TYPE=InnoDB; Basicly, Answer can have an Account and an Account always has a Person. What would want is a query that produces a resultset where each row contains all fields from Answer + FirstName and LastName from Person for the AccountID that corresponds to the AccountID field in Answer. If an Answer does not have an AccountID I want NULL-values. Example: AnswerID | QuestionID | Value | AccountID | FirstName | LastName 1 | 4 | 10.5 | 45 | John | Persson 2 | 3 | 12.6 | NULL | NULL | NULL FirstName and LastName is NULL because AccountID is NULL Oh, and I can not use version 4+. Help greatly appreciated. /Nicolas - 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: JOIN query with three tables.
Nicolas, select a.AnswerID,a.QuestionID,a.Value,a.AccountID,p.FirstName,p.LastName from Answer a left join Account c on (a.AccountID = c.AccountID), Person p where c.PersonID = p.PersonID It won't work. Sorry. select a.AnswerID,a.QuestionID,a.Value,a.AccountID,p.FirstName,p.LastName from Answer a left join Account c on (a.AccountID = c.AccountID) left join Person p on (c.PersonID = p.PersonID) :) Best regards, Mikhail. - Original Message - From: Mikhail Entaltsev [EMAIL PROTECTED] To: Nicolas Ivering [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, August 19, 2002 10:46 AM Subject: Re: JOIN query with three tables. Nicolas, select a.AnswerID,a.QuestionID,a.Value,a.AccountID,p.FirstName,p.LastName from Answer a left join Account c on (a.AccountID = c.AccountID), Person p where c.PersonID = p.PersonID Best regards, Mikhail. - Original Message - From: Nicolas Ivering [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, August 19, 2002 10:35 AM Subject: JOIN query with three tables. Hi, this is probably a simple query but I tried all I can think of without finding a solution. I have three tables, CREATE TABLE Answer ( AnswerID INTEGER NOT NULL AUTO_INCREMENT, QuestionID INTEGER NOT NULL, Value DECIMAL(15,4) NOT NULL, AccountID INTEGER, PRIMARY KEY (AnswerID) ) TYPE=InnoDB; CREATE TABLE Account ( AccountID INTEGER NOT NULL AUTO_INCREMENT, PersonID INTEGER NOT NULL, AccountGroupID INTEGER NOT NULL, UserName VARCHAR(50) NOT NULL, Password VARCHAR(20) NOT NULL, PRIMARY KEY (AccountID) ) TYPE=InnoDB; DROP TABLE Person; CREATE TABLE Person ( PersonID INTEGER NOT NULL AUTO_INCREMENT, FirstName VARCHAR(255) NOT NULL, LastName VARCHAR(255) NOT NULL, PRIMARY KEY (PersonID) ) TYPE=InnoDB; Basicly, Answer can have an Account and an Account always has a Person. What would want is a query that produces a resultset where each row contains all fields from Answer + FirstName and LastName from Person for the AccountID that corresponds to the AccountID field in Answer. If an Answer does not have an AccountID I want NULL-values. Example: AnswerID | QuestionID | Value | AccountID | FirstName | LastName 1 | 4 | 10.5 | 45 | John | Persson 2 | 3 | 12.6 | NULL | NULL | NULL FirstName and LastName is NULL because AccountID is NULL Oh, and I can not use version 4+. Help greatly appreciated. /Nicolas - 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: mysql console buzzer problem
Max, What's wrong with your PC speaker in mysql console?! I am using the same version and have no problem with it. Best regards, Mikhail. - Original Message - From: Max Morawski [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, August 16, 2002 10:36 AM Subject: mysql console buzzer problem Hello! I can't find a way to turn off buzzer (PC speaker) in mysql console. Am I missing something or is it impossible and I should report user interface bug to bugs mailing list? mysql console version : 11.18 distribution 3.23.51 System: Windows 2000 Max - 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 console buzzer problem
Max, It's making loud noise on each error. It is *extremely* annoying. Try to use these ways: 1. -s, --silent option in mysql console. I don't know what does it mean (in doculmentation I found only Be more silent.), but may be it will help you. 2. Generally, you can change settings of PC speaker in OS settings. 3. Just turn off speaker totally (remove it from your PC :) Best regards, Mikhail. - Original Message - From: Max Morawski [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, August 16, 2002 10:45 AM Subject: Re: mysql console buzzer problem Mikhail Entaltsev wrote: What's wrong with your PC speaker in mysql console?! I am using the same version and have no problem with it. It's making loud noise on each error. It is *extremely* annoying. Max - 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 console buzzer problem
Max, 2. Generally, you can change settings of PC speaker in OS settings. No change. Which OS do you use? Mikhail. - Original Message - From: Max Morawski [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, August 16, 2002 11:00 AM Subject: Re: mysql console buzzer problem Mikhail Entaltsev wrote: It's making loud noise on each error. It is *extremely* annoying. Try to use these ways: 1. -s, --silent option in mysql console. I don't know what does it mean (in doculmentation I found only Be more silent.), but may be it will help you. No change. 2. Generally, you can change settings of PC speaker in OS settings. No change. 3. Just turn off speaker totally (remove it from your PC :) In general it is useful when Mozilla beeps from time to time to inform me that new mail has arrived. I just don't want to annoy myself and all people around with long series of loud beeps when something goes wrong for some reason. Thanks, Max - 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 search
I'd like to find all the records that contains this : wr?? That's why I did this query : SELECT * FROM table WHERE rights LIKE 'wr??' Try to use this one: SELECT * FROM table WHERE rights LIKE 'wr%' (It means everything that begins from 'wr') Next point: '?' - it is not pattern in MySQL... Check this page for more information. http://www.mysql.com/doc/en/Pattern_matching.html Best regards, Mikhail. - Original Message - From: tristan Israel [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, August 16, 2002 10:48 AM Subject: mysql search Hello, I have a field named rights (varchar[9]) it contains some records like wrxwrxwrx or wr-w--w-x I'd like to find all the records that contains this : wr?? That's why I did this query : SELECT * FROM table WHERE rights LIKE 'wr??' and it doesn't work ! It extension, a simple search like : SELECT * FROM table WHERE name LIKE 'I*' doesn't return anything. Could you help me ? Thank you - 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: sql
* Pushkar Pradhan I'm trying to enforce one column of my table to contain strictly 6 chars. string. However I've some columns of type VARCHAR (e.g name, etc.) so mysql silently changes the column to VARCHAR. It is ok for MySQL. You can find some info here : http://www.mysql.com/doc/en/Silent_column_changes.html ... a.. If any column in a table has a variable length, the entire row is variable-length as a result. Therefore, if a table contains any variable-length columns (VARCHAR, TEXT, or BLOB), all CHAR columns longer than three characters are changed to VARCHAR columns. This doesn't affect how you use the columns in any way; in MySQL, VARCHAR is just a different way to store characters. MySQL performs this conversion because it saves space and makes table operations faster. See section 7 MySQL Table Types. ... Is there any way I can force the column to contain only 6 chars. No. But do you actually need it?! Mikhail. - Original Message - From: Roger Baklund [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Pushkar Pradhan [EMAIL PROTECTED] Sent: Thursday, August 15, 2002 9:46 AM Subject: Re: sql * Pushkar Pradhan I'm trying to enforce one column of my table to contain strictly 6 chars. string. However I've some columns of type VARCHAR (e.g name, etc.) so mysql silently changes the column to VARCHAR. Right. Is there any way I can force the column to contain only 6 chars. Yes, you can define it as VARCHAR(6)...? -- Roger 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
Re: HAVING Max(x) IS NULL always TRUE.
Oleh,
SELECT t1.id, Max(t2.data)
FROM test1 t1 left join test2 t2 ON t1.id = t2.parent_id
GROUP BY t1.id
HAVING Max(t2.data) IS NULL;
As temporary solution try to use next query:
SELECT t1.id, Max(t2.data) as MaxData
FROM test1 t1 left join test2 t2 ON t1.id = t2.parent_id
GROUP BY t1.id
HAVING MaxData IS NULL;
Mikhail.
- Original Message -
From: Oleh Khoma [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, August 14, 2002 10:09 PM
Subject: HAVING Max(x) IS NULL always TRUE.
Description:
HAVING Max(x) IS NULL always TRUE.
This has changed since 3.23.39, when if the x column had all Nulls,
Max(x) IS NULL was TRUE and FALSE otherwise.
How-To-Repeat:
CREATE TABLE test1 (
ID int unsigned auto_increment not null primary key,
data varchar(50)
);
INSERT INTO test1 (data) VALUES
('1'), ('2'), ('3'), ('4');
CREATE TABLES test2 (
ID int unsigned auto_increment not null primary key,
parent_id int unsigned not null references test1,
data enum('Y', 'N') null
);
INSERT INTO test2 (parent_id, data) values
(1, 'Y'), (1, 'N'), (1, Null),
(2, 'Y'), (2, Null),
(3, 'N'), (3, Null),
(4, Null);
SELECT t1.id, Max(t2.data)
FROM test1 t1 left join test2 t2 ON t1.id = t2.parent_id
GROUP BY t1.id
HAVING Max(t2.data) IS NULL;
Above query is returning only (4, Null) on 3.23.38-max and 3.23.39
but it returns (1, Y), (2, Y), (3, N), (4, Null) on 3.23.51
Fix:
Workaround:
HAVING Max(IfNull(t2.data, '')) = ''
Submitter-Id: submitter ID
Originator: Oleh Khoma
Organization:
FineStudio
MySQL support: none
Synopsis: HAVING Max(x) IS NULL always TRUE
Severity: serious
Priority: medium
Category: mysql
Class: sw-bug
Release: mysql-3.23.51 (Source distribution)
Environment:
RedHat Linux 7.0
System: Linux linux1303.dn.net 2.2.16-RAID #6 SMP Fri Jul 7 13:29:16 EDT
2000 i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs
gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-98)
Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=pentiumpro' CXX='gcc'
CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors' LDFLAGS='-static'
LIBC:
lrwxrwxrwx1 root root 13 Mar 20 17:07 /lib/libc.so.6 -
libc-2.2.4.so
-rwxr-xr-x1 root root 5723311 Dec 8 2001 /lib/libc-2.2.4.so
-rw-r--r--1 root root 27314296 Dec 8 2001 /usr/lib/libc.a
-rw-r--r--1 root root 178 Dec 8 2001 /usr/lib/libc.so
Configure command:
./configure --enable-assembler --with-other-libc=/usr/local/mysql
'CFLAGS=-O2 -mcpu=pentiumpro'
CXXFLAGS=-O2 -mcpu=pentiumpro -felide-constructors' CXX=gcc LDFLAGS=-static
-
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 and io read
Reinier, It seems that when i do a select and the result is minimal it uses the index(fast about 3 sec), but when the result is much more it is not using the index. this results in a lot of disk io and taking to much time. It is correct, because (http://www.mysql.com/doc/en/MySQL_indexes.html at the end) ... Note that in some cases MySQL will not use an index, even if one would be available. Some of the cases where this happens are: If the use of the index would require MySQL to access more than 30% of the rows in the table. (In this case a table scan is probably much faster, as this will require us to do much fewer seeks.) Note that if such a query uses LIMIT to only retrieve part of the rows, MySQL will use an index anyway, as it can much more quickly find the few rows to return in the result. ... Try to check query plan (use EXPLAIN command). My experience with msql7 is that it keeps thing in memory with results in performance improvement. Can this be done with mysql? IMHO MySQL is doing that in correct way too. Best regards, Mikhail. - Original Message - From: Reinier van Heusden [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, August 15, 2002 12:13 PM Subject: MySQL and io read Hello, I have a mysql database latest version from the freebsd ports running on a 350 Mhz with 385 MB memory and 1 ata 33 disk freebsd 4.6 stable server. I have a database with 5.5 million record in say 1 table. We use this read-only. It seems that when i do a select and the result is minimal it uses the index(fast about 3 sec), but when the result is much more it is not using the index. this results in a lot of disk io and taking to much time. My experience with msql7 is that it keeps thing in memory with results in performance improvement. Can this be done with mysql? When i use top i don't see mysql using much of my memory. I haven't changed any settings yet in my.cnf. [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 - 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: View current connections
...I'm really hoping to find is a GUI that updates itself on the fly, kind of like the Win2k Task Manager processes tab. Anybody got anything like this? I am really happy with this one http://www.anse.de/mysqlfront/ It can do that and a lot of other nice features. Best regards, Mikhail. - Original Message - From: Tab Alleman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, August 15, 2002 5:01 PM Subject: RE: View current connections Thanks for the reply Egor.. This helps, but what I'm really hoping to find is a GUI that updates itself on the fly, kind of like the Win2k Task Manager processes tab. Anybody got anything like this? -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 15, 2002 7:08 AM To: [EMAIL PROTECTED] Subject: Re: View current connections Tab, Wednesday, August 14, 2002, 11:06:36 PM, you wrote: TA Is there a tool out there for Windows that will let me monitor how TA many connections to my MySQL database are currently open at any TA given moment? Use SHOW PROCESSLIST command: http://www.mysql.com/doc/en/SHOW_PROCESSLIST.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 - 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: problem with mysqldump
I had the same problem. It occurs when you try to load (3.23.51 or higher) dump file to lower version of MySQL. Look at this line... /*!4 ALTER TABLE mobile_dev DISABLE KEYS */; As soon as version is not equal 4 everything in comments will be deleted. So we have ; empty query. Mikhail. - Original Message - From: Victoria Reznichenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, August 14, 2002 4:32 PM Subject: Re: problem with mysqldump jan, Tuesday, August 13, 2002, 12:34:23 PM, you wrote: jb Hello i got a problem with mysqldump, jb i'm forced to dump it to to place the database on a new laptop jb mysql -u root -p hardwaredb hardwaredbbackup.sql jb Enter password: jb ERROR 1065 at line 25: Query was empty jb and the corresponding line: jb ) TYPE=MyISAM; jb 25: /*!4 ALTER TABLE mobile_dev DISABLE KEYS */; jb what may i do to correct it, to dump the database? What is the version of your MySQL server? -- 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 - 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: Oracle .dmp to mySQL
Hi Mike, I am not sure, but as I remember Microsoft SQLServer has Import/Export utility (standard installation) that can import data from different sources (Oracle?! why not). Best regards, Mikhail. - Original Message - From: Mike Townend [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, August 12, 2002 3:09 PM Subject: Oracle .dmp to mySQL Hi all, We have just recently inherited a web project from another company who's DB was Oracle 8. And as such they have provided us with an exported .dmp file of the database they were using... We are using mySQL as our DB backend... Does anyone know of any tools that will allow me to import this file into mySQL (or maybe SQL Server which we can then import to mySQL) MTIA Mike - 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: help me with this query
select u.userid , c.companyname from users u left join company c on (u.companyid = c.companyid) where u.status = 'ACT' Best regards, Mikhail. - Original Message - From: Arul [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED] Sent: Tuesday, July 02, 2002 5:15 PM Subject: help me with this query Hi All I have two tables User and Company User tables has userid , companyid and status Company table has companyid and companyname It's not necessary that all users should have company.There can be users without company also and for them the companyid is null. so i need all the users whose status are active and if they have companyid , i need the companyname of their company In Oracle i can write this as select u.userid , (select companyname from company where companyid = u.companyid) from users u where u.status = 'ACT' how do i get this in mysql i wrote one query select u.userid , c.companyname from users u , company c where u.companyid = c.companyid AND u.status = 'ACT' This returned only the users who have company...how do i take the users also who doesnt have a company Regards -Arul - 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: Stupid backup/restore question
Jesse, try to make dump (mysqldump) with option --add-drop-table or --opt (it will be faster). Description of this options see here http://www.mysql.com/doc/m/y/mysqldump.html Best regards, Mikhail. - Original Message - From: Jesse Sheidlower [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, July 01, 2002 6:30 PM Subject: Stupid backup/restore question I have a working server and a development server. From time to time I'd like to refresh the content of my development server with what's on my working server. So I take one of my regular backups, that I get by doing mysqldump database dbbackupJuly1-02. Then I gzip this, ftp it over to my development server, gunzip it, and try mysql database dbbackupJuly1-02, and immediately get an ERROR 1050 at line 11: Table 'firsttable' already exists message. What should I be doing instead? The docs don't seem to specify this, and there doesn't seem to be an ignore or replace option for the mysql command. Do I actually have to drop all the tables on my development box before loading in from the backup? Jesse Sheidlower [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 - 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: efficiency of mysql 2.23
Marek, check this URL http://www.mysql.com/doc/S/H/SHOW_VARIABLES.html Best regards, Mikhail. - Original Message - From: Marek Wysmulek [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, July 01, 2002 6:56 PM Subject: efficiency of mysql 2.23 Dear group. I have on server Intel Pentium IV 1,8 GHz, 1GB RAM. I would like to set some efficiency settings into my my_multi.conf of mysql .3.23 BUT although I read docs (unclear for me ;-)) I don't know how to set proper values and where to set ones. Problem is that servers stops answering for ~2 or 3 seconds when a lot of users are connected. Stop answering - I mean that ex. when I'm working on console (doing another then selecting activity) ex. during opening config file server show me ingredience after 2 or 3 seconds. I don't tell about working on end user application because from consum of time during makeing selects point of view delays are not objective. OK. Let say there is such section It is common section. #[mysqld] #port = 3306 #socket = /tmp/mysql.sock #skip-locking #set-variable = key_buffer=16K #set-variable = max_allowed_packet=1M #set-variable = thread_stack=64K #set-variable = table_cache=4 #set-variable = sort_buffer=64K set-variable= net_buffer_length=2K #server-id = 1 It is commented because the final are like for 0 server [mysqld0] socket = /tmp/mysql.sock.0 port = 3306 pid-file = /usr/local/mysql/var2/hostname.pid.0 3datadir= /mnt/mysql language = /usr/local/mysql/share/mysql/english but nowhere was written if any efficency variables can be puted into. Please show me UNDERSTENDABLE information for meaning of those variables OR just tell me what values should be puted into config file to satisfy users regarding ability of my server machine. Another question is that whether storing on fisical the same drive system and mysql data base datas have influence on efficiency ? I can try if I will separate it and system will start from hda and datas will be stored on hdb for ex. would incease flexibility of data flow. --- Marek Wysmu³ek - 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: Problem with a query
N2 after that server will take only record that have Count(C.CompanyID) = 2. In this case it will take: C.CompanyIDC.B2bCount(C.CompanyID) 77N2 Best regards, Mikhail. - Original Message - From: Arul [EMAIL PROTECTED] To: Mikhail Entaltsev [EMAIL PROTECTED] Cc: MySQL [EMAIL PROTECTED] Sent: Saturday, June 29, 2002 07:32 Subject: Re: Problem with a query Hi Mikhail What i meant to say is that My First Query returns 3 rows satisfying two conditions Like CI.IndustryID IN (2,3) and Count(C.CompanyID) = 2 So when i add another OR say (CI.IndustryID IN (2,3) OR C.b2b = 'N') and Count(C.CompanyID) = 2 I guess it should aways have the possibilty of returning more rows or the same rows..I wonder how the number of rows returned is decreased with an OR Condition Regards, -Arul - Original Message - From: Arul [EMAIL PROTECTED] To: Mikhail Entaltsev [EMAIL PROTECTED] Cc: MySQL [EMAIL PROTECTED] Sent: Saturday, June 29, 2002 10:32 AM Subject: Re: Problem with a query No Mikhail I dont think i can agree with you . If you could see my first query which returned 3 rows also had a Having Clause. I just added an OR condition inside the query which should always increase the Number of Rows Returned..Am i correct..Check the Query plzz.. SELECT C.Companyid,C.B2b FROM Company C ,Company C1 , Company_Industries CI, Company_Type_Details CTD,Users U,User_Type_Details UTD Where C.Companyid=CTD.Companyid AND C.Companyid=U.Companyid AND UTD.UserId=U.UserId AND UTD.User_Typeid=2 AND CTD.Company_Typeid=3 AND CTD.App_Status='APP' AND U.UserID 2 AND C.Company_App_Status='APP' AND C.Company_Status='ACT' AND C.CompanyID = CI.CompanyID AND C.CompanyID = C1.CompanyID AND (CI.IndustryID IN (2,3) ) Group By C.CompanyID,C.B2b HAVING Count(C.CompanyID) = 2 This Query returns 3 rows. CompanyIDB2B 64Y 77N 78Y Then i thought of checking B2b = 'N' with an OR Condition.So My Query became like this SELECT C.Companyid,C.B2b FROM Company C ,Company C1 , Company_Industries CI, Company_Type_Details CTD,Users U,User_Type_Details UTD Where C.Companyid=CTD.Companyid AND C.Companyid=U.Companyid AND UTD.UserId=U.UserId AND UTD.User_Typeid=2 AND CTD.Company_Typeid=3 AND CTD.App_Status='APP' AND U.UserID 2 AND C.Company_App_Status='APP' AND C.Company_Status='ACT' AND C.CompanyID = CI.CompanyID AND C.CompanyID = C1.CompanyID AND (CI.IndustryID IN (2,3) OR C.B2b = 'N' ) Group By C.CompanyID,C.B2b HAVING Count(C.CompanyID) = 2 So in Theory this Query Should return more results if the B2b is 'N' Since this is an OR Query. But I got only two rows.The Result was CompanyIDB2B 64Y 78Y What happened to Company 77 whose B2B was 'N' Instead if i put an AND instead of OR , i get the result what i could judge ie: CompanyIDB2B 77N Can u please explain me what's happenning Regards, -Arul - Original Message - From: Mikhail Entaltsev [EMAIL PROTECTED] To: Arul [EMAIL PROTECTED]; Ralf Narozny [EMAIL PROTECTED] Cc: MySQL [EMAIL PROTECTED] Sent: Friday, June 28, 2002 8:17 PM Subject: Re: Problem with a query Arul, everything is correct. Let's assume you have this info: C.CompanyIDC.B2bCI.IndustryID 64Y2 64Y3 77N2 77N3 77N5 78Y2 78Y3 SELECT C.Companyid,C.B2b FROM Company C ,Company C1 , Company_Industries CI, Company_Type_Details CTD,Users U,User_Type_Details UTD Where C.Companyid=CTD.Companyid AND C.Companyid=U.Companyid AND UTD.UserId=U.UserId AND UTD.User_Typeid=2 AND CTD.Company_Typeid=3 AND CTD.App_Status='APP' AND U.UserID 2 AND C.Company_App_Status='APP' AND C.Company_Status='ACT' AND C.CompanyID = CI.CompanyID AND C.CompanyID = C1.CompanyID AND (CI.IndustryID IN (2,3) OR C.B2b = 'N
Re: Hidden rows in table.
Jonas, try to execute this command: SELECT count(1) FROM table_name; Result should be 28. Do you have 28? Mikhail. - Original Message - From: Jonas Olofsson [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, June 29, 2002 11:20 Subject: Hidden rows in table. Hi. I have just started to use MySQL and I have come up with a strange problem. I have a table with 28 rows. When I do 'SELECT * FROM table_name;' in the MySQL client it only shows 12 of these rows. But if i specify one of the rows that are not shown with the first command, like 'SELECT * FROM table_name WHERE id='25';' this row is shown. Any suggestions what might be wrong? // Jonas Olofsson - 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
