Re: find a particular record.
I am doing this to move data from one table to other and getting below error. How can check this particular record at this row number 921888 in dats_tem table. insert into reports1.dats1 select * from dats_tem; ERROR 1292 (22007): Incorrect datetime value: '-00-00 00:00:00' for column 'P_LAST_SOLD_DATE' at row 921888 An extremely quick and dirty solution: mysqldump -uusername -p -t --skip-opt database_name dats_tem | grep INSERT | head -921888 | tail -1 replacing username with your username and database_name with your database name, of course. -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: General Query Log -- No Timestamp
On 6/26/07, Brown, Charles [EMAIL PROTECTED] wrote: The MYSQL general query log does not include timestamp of queries that it logs because queries are logged many many seconds before they are executed. Which version of MySQL are you running? I'm running 5.0.22 on my desktop, but I'm fairly sure that older versions included timestamps in the general query log. I just enabled mine to test this (by adding log = /var/log/mysql.log to /etc/my.cnf) and it looks something like: 070629 8:17:44 6 Connect [EMAIL PROTECTED] on monitoring 6 Query set autocommit=0 6 Query select * from urls where active=y 070629 8:17:45 6 Query INSERT INTO results VALUES (NULL,5,now(),0.5833,35267) 6 Query INSERT INTO results VALUES (NULL,6,now(),0.0137,0) 6 Query INSERT INTO results VALUES (NULL,8,now(),0.7762,28130) 6 Query INSERT INTO results VALUES (NULL,9,now(),0.0348,4217) -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error on ndbcluster database restore - Can't find record on table
Dear All, I use this command [/usr/bin/ndb_mgm -e START BACKUP WAIT COMPLETED] to backup mysql cluster. Backup is successful, when I restore my database using [ndb_restore -m -b 3 -n 2] and [ndb_restore -r -b 3 -n 2], restore also is successful. But when I select or browse the tables have column text field, problem is Can't find record on table. The table character set is UTF-8. My database is about 500MB. What is my problem? How can I solve this problem? Is there any way to backup or restore my cluster databases? Thanks for your help. Toan Dang
Re: Error on ndbcluster database restore - Can't find record on table
Toan. Dang Anh wrote: Dear All, I use this command [/usr/bin/ndb_mgm -e START BACKUP WAIT COMPLETED] to backup mysql cluster. Backup is successful, when I restore my database using [ndb_restore -m -b 3 -n 2] and [ndb_restore -r -b 3 -n 2], restore also is successful. Hi Toan, You need to do -r on node 2 as well, you have only restored half the data. -- Andrew Hutchings - LinuxJedi - http://www.linuxjedi.co.uk/ Windows is the path to the darkside...Windows leads to Blue Screen. Blue Screen leads to downtime. Downtime leads to suffering...I sense much Windows in you... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
peformance help: preventing 'using temporary; using filesort'
Hello all. I'm looking for help with the query below. Is there anyway to prevent the temporary and filesort? I've tried about as many combinations as I could think of, but can't seem to prevent it. I'm sure that's the reason, when run on a table of around 750k records, it takes in excess of 20 seconds. There are indexes on sourceID in both tables as well as the date field in the first table. Thanks for any ideas. SELECT t1.sourceID as sourceID, count(t1.sourceID) as clicks, sum(t1.converted) as conversions, (sum(t1.converted)/count(t1.sourceID)) * 100 as conv_rate, count(t1.sourceID) * t2.cost as cost, sum(t1.revenue) as revenue, (sum(t1.revenue)) - (count(t1.sourceID) * ifnull(t2.cost,0)) as margin, ( ((sum(t1.revenue)) - (count(t1.sourceID) * t2.cost)) / sum(t1.revenue) ) * 100 as gm, (count(t1.sourceID) * t2.cost) / sum(t1.converted) as cpl, (sum(t1.revenue)) / sum(t1.converted) as rpl, t2.cost as cpc FROM source_site_quality as t1 LEFT JOIN rpt_cpc as t2 ON (t1.sourceID = t2.sourceID) WHERE t1.date = '2007-06-26' AND t1.date = '2007-06-28' GROUP BY sourceID ORDER BY clicks desc, conversions desc; When using EXPLAIN: ++-+---+---+---+--+-+--+--+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| ++-+---+---+---+--+-+--+--+--+ | 1 | SIMPLE | t1| range | idx_date | idx_date | 3 | NULL | 4612 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | t2| ALL | NULL | NULL | NULL | NULL | 1351 | | ++-+---+---+---+--+-+--+--+--+ -- rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: peformance help: preventing 'using temporary; using filesort'
Rich Brant wrote: Hello all. I'm looking for help with the query below. Is there anyway to prevent the temporary and filesort? I've tried about as many combinations as I could think of, but can't seem to prevent it. I'm sure that's the reason, when run on a table of around 750k records, it takes in excess of 20 seconds. There are indexes on sourceID in both tables as well as the date field in the first table. Thanks for any ideas. SELECT t1.sourceID as sourceID, count(t1.sourceID) as clicks, sum(t1.converted) as conversions, (sum(t1.converted)/count(t1.sourceID)) * 100 as conv_rate, count(t1.sourceID) * t2.cost as cost, sum(t1.revenue) as revenue, (sum(t1.revenue)) - (count(t1.sourceID) * ifnull(t2.cost,0)) as margin, ( ((sum(t1.revenue)) - (count(t1.sourceID) * t2.cost)) / sum(t1.revenue) ) * 100 as gm, (count(t1.sourceID) * t2.cost) / sum(t1.converted) as cpl, (sum(t1.revenue)) / sum(t1.converted) as rpl, t2.cost as cpc FROM source_site_quality as t1 LEFT JOIN rpt_cpc as t2 ON (t1.sourceID = t2.sourceID) WHERE t1.date = '2007-06-26' AND t1.date = '2007-06-28' GROUP BY sourceID ORDER BY clicks desc, conversions desc; When using EXPLAIN: ++-+---+---+---+--+-+--+--+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| ++-+---+---+---+--+-+--+--+--+ | 1 | SIMPLE | t1| range | idx_date | idx_date | 3 | NULL | 4612 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | t2| ALL | NULL | NULL | NULL | NULL | 1351 | | ++-+---+---+---+--+-+--+--+--+ -- rich rpt_cpc needs an index on sourceID. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
