> But when I execute a complex query, it hangs there forever. I don't have
> a chance to see the result because I need to go home to have dinner. The > same query run in a FreeBSD system in Mysql5.01 just takes 2 seconds. Please, execute this statements while the huge query is running, and send us the output: show processlist; show variables; show status; Send us an output of SHOW CREATE TABLE your_table, for each table you use in your queries. Send us the actual query. Please, run this statements using mysql command line client. > The questions I would like to ask is how to make sure mysqld is start up > properly? Because it seems that the start script that come with the Usually MySQL writes errors to error log. If you find something there, please, send it us. To check that MySQL server is running you may use 'mysqladmin ping'. Sometimes server started normally, but it's threads dies during the queries, messages about this are written into error log. See: http://dev.mysql.com/doc/mysql/en/error-log.html > The second question is how to make sure all indexes are in-placed after > the data restore? I used the following script backup all data from Usually indexes are created normally, and if you see them with show index then everything should be ok. After receiving the information, which I asked, we can make more exact conlusions. sam wun <[EMAIL PROTECTED]> wrote: > Hi, > > I have removed all old data and restore the backup data to the directory > /usr/local/mysql/data > > I also created root password with mysqladmin and flush its previliges... > Execute the following perl+dbi script seems fine, it retrieves all data > from the customer table in DB which I just restored: > #!/usr/bin/perl > > use DBI; > > $database = "DB"; > $username = "me"; > $pw = "me0901"; > $dbh = DBI->connect("DBI:mysql:$database",$username,$pw); > die "Unable to connect: $DBI::errstr\n" unless (defined $dbh); > $sth = $dbh->prepare(q{SELECT * from customer}) or die "Unable to prep > our query:".$dbh->errstr."\n"; > $rc = $sth->execute or die "Unable to execute our query:".$dbh->errstr."\n"; > while ($aref = $sth->fetchrow_arrayref){ > for ($i=0; $i < $sth->{NUM_OF_FIELDS};$i++){ > print $sth->{NAME}->[$i].": " . $aref->[$i] . "\n"; > } > } > $sth->finish; > $dbh->disconnect; > > But when I execute a complex query, it hangs there forever. I don't have > a chance to see the result because I need to go home to have dinner. The > same query run in a FreeBSD system in Mysql5.01 just takes 2 seconds. > The MySQL 5.01 in FreeBSD is the source of the backup. I restore this > backup to MySQL 4.1.10 in Redhat9.0. > > I have checked the DB's indexes on the most complex table, it shows > indexes are being indexed. > mysql> show index from transaction; > +-------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ > | Table | Non_unique | Key_name | Seq_in_index | Column_name | > Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | > +-------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ > | transaction | 0 | PRIMARY | 1 | transcode | > A | 161629 | NULL | NULL | | BTREE | | > | transaction | 1 | custcode | 1 | custcode | > A | 715 | NULL | NULL | | BTREE | | > | transaction | 1 | prodcode | 1 | prodcode | > A | 3367 | NULL | NULL | | BTREE | | > | transaction | 1 | date | 1 | date | > A | 1197 | NULL | NULL | | BTREE | | > | transaction | 1 | netsales | 1 | netsales | > A | 23089 | NULL | NULL | | BTREE | | > | transaction | 1 | salesvolume | 1 | salesvolume | > A | 206 | NULL | NULL | | BTREE | | > | transaction | 1 | custcode_2 | 1 | custcode | > A | 788 | NULL | NULL | | BTREE | | > | transaction | 1 | custcode_2 | 2 | date | > A | 53876 | NULL | NULL | | BTREE | | > | transaction | 1 | prodcode_2 | 1 | prodcode | > A | 923 | NULL | NULL | | BTREE | | > | transaction | 1 | prodcode_2 | 2 | date | > A | 161629 | NULL | NULL | | BTREE | | > +-------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ > 10 rows in set (0.47 sec) > > One strange thing I found is when the query was running, I use top > command to check the loading of mysqld, but it is 0%. This is usually > 9x% when the same perl script running agains MySQL5.0.1 in FreeBSD. The > result of running the top command in Redhat against mysqld is shown as > below: > 19:26:21 up 14 days, 20:38, 1 user, load average: 0.00, 0.00, 0.00 > 1 processes: 1 sleeping, 0 running, 0 zombie, 0 stopped > CPU states: 0.0% user 0.1% system 0.0% nice 0.0% iowait 99.8% idle > Mem: 513852k av, 506580k used, 7272k free, 0k shrd, 124196k > buff > 370448k actv, 0k in_d, 10116k in_c > Swap: 2132888k av, 15556k used, 2117332k free 270220k > cached > > PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND > 2893 mysql 15 0 50560 44M 3008 S 0.0 8.8 0:00 0 mysqld > > But I confirmed that all mysqld instances are running in the system as > shown below: > > # netstat -a | grep mysql > tcp 0 0 *:mysql *:* LISTEN > unix 2 [ ACC ] STREAM LISTENING 127441 /tmp/mysql.sock > [EMAIL PROTECTED] root]# !ps > ps -auxww | grep mysql > root 2873 0.0 0.1 4360 1016 ? S 14:16 0:00 /bin/sh > ./S98mysqld > mysql 2893 0.0 8.8 302680 45340 ? S 14:16 0:00 [mysqld] > mysql 2894 0.0 8.8 302680 45340 ? S 14:16 0:00 [mysqld] > mysql 2895 0.0 8.8 302680 45340 ? S 14:16 0:00 [mysqld] > .... > > The questions I would like to ask is how to make sure mysqld is start up > properly? Because it seems that the start script that come with the > binary files from mysql4.1.10 can';t be started by typing "mysql.server > start", and I don't see a /etc/my.conf is installed in the system, so I > copied the support-files/my-huge.cnf to /etc/my.cnf. > > The second question is how to make sure all indexes are in-placed after > the data restore? I used the following script backup all data from > MySQL5.0.1: > DBNAMES="`mysql --user=$USERNAME --password=$PASSWORD --host=$DBHOST > --batch --skip-column-names -e "show databases"| > sed 's/ /%/g'`" > > Thank you in advance > Sam. > > > mel list_php wrote: > >> >>> Yeah, I have looked at it, but not sure whether I need to repopulate >>> the mysql.host db file. If I do that, I may be also need to recreate >>> all db passwords as well. >>> The error is: >>> InnoDB: log sequence number 0 43634. >>> InnoDB: Doing recovery: scanned up to log sequence number 0 43634 >>> 050302 16:36:43 InnoDB: Flushing modified pages from the buffer pool... >>> 050302 16:36:43 InnoDB: Started; log sequence number 0 43634 >>> 050302 16:36:43 [ERROR] Fatal error: Can't open privilege tables: >>> Table 'mysql.host' doesn't exist >>> 050302 16:36:43 mysqld ended >>> >> >> I don't know how you made your backup, apparently you missed the >> mysql.host table. >> Usually when making a new install a mysql database containing all the >> privileges is created, maybe you erased this when copying your files? >> In an other mail you say you used the --all-databases option so I >> suppose the error is somewhere else. >> To identify the problem you may try to recreate the mysql db, see if >> it works, and if yes check your dump. >> >> >> About your indexes I usually use mysqldump and the indexes are >> exported as well, you can check with show index but I'm not sure that >> is what you are looking for. >> http://dev.mysql.com/doc/mysql/en/mysqldump.html >> >> >> >> >> >> >>> Since all DB data in this server are restored from the FreeBSD system >>> in MySQL 5.0. I don't know how to recreate all indexes for db. >>> Is there simple way for recreating all indexes? or check whether >>> indexes are in-placed? >>> >>> Thanks >>> Sam >>> >>>> Good luck! >>>> >>>>> From: sam wun <[EMAIL PROTECTED]> >>>>> To: mysql@lists.mysql.com >>>>> Subject: Silly mistake >>>>> Date: Wed, 02 Mar 2005 15:58:28 +0800 >>>>> >>>>> Hi, >>>>> >>>>> I admit I m silly to shutdown mysqld with the killall command in >>>>> the Redhat server, I can't restart mysql service now. Most of the >>>>> reason is because the script mysql.server come with the mysql >>>>> 4.1.10 does not like mysql.server start or mysql.server stop, so I >>>>> need to start it up use & and shut it down with killall. >>>>> Anyway, after killall mysql, I got the following error when I tried >>>>> to restart it. >>>>> Here is the error: >>>>> ./mysqld >>>>> Starting mysqld daemon with databases from /usr/local/mysql/data >>>>> STOPPING server from pid file /usr/local/mysql/data/vivaserver.pid >>>>> 050302 15:52:05 mysqld ended >>>>> >>>>> How can I start mysqld now? >>>>> >>>>> Thanks >>>>> Sam >>>>> >>>>> >>>>> > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
