Re: Please help: recovering db from crash
Thanks for coming to the rescue, Mark and Bruce. Mark Leith wrote: This is actually for Linux/Unix, not Windows. What error do you get from MySQL when trying to log in? Does the mysqld(-nt) process show within Task Manager? What does the new error log say? You may need to reset permissions: http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html Or you may need to set the appropriate datadir / basdir etc. depending on how you set up MySQL: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html Best regards Mark Adrian Bruce wrote: not sure, but it may be worth trying the following run the script: mysql_install_db --user=root In the installation dir this should change ownership and make mysql recognise the data dir. good luck Ade Foo Ji-Haw wrote: Hi all, My Windows-based database server crashed (no fault of MySQL. probably OS or hardware), and I managed to copy out the data files. I am using version 5.0 of the Essentials package. I tried to install a similar setup on another server, then copy the data\ folder over. The MySQL service starts, but I am not able to login, even as root. Is there anyone who can advise me on the recovery steps? Appreciate your feedback! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Please help: recovering db from crash
Hi all, My Windows-based database server crashed (no fault of MySQL. probably OS or hardware), and I managed to copy out the data files. I am using version 5.0 of the Essentials package. I tried to install a similar setup on another server, then copy the data\ folder over. The MySQL service starts, but I am not able to login, even as root. Is there anyone who can advise me on the recovery steps? Appreciate your feedback! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Discussion: the efficiency in using foreign keys
I've always been a believer in avoiding sql procedures, for the main reason that I want to be as database-independent as possible. I know it is less efficient, but being able to switch between MySQL, Postgre, and the new freebies from IBM, Oracle, and Microsoft is a strong advantage from the business perspective (of total cost to the customer, and customer preference). Of course, this is a discussion point. I'd love to hear from the community on their experiences. [EMAIL PROTECTED] wrote: I'd also like to add that if you have a choice between doing something in the application logic vs. MySQL's SQL statements, then it is probably more efficient to use SQL statements, constructs and related functions, to get the job done, rather than in the application logic if possible. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Discussion: the efficiency in using foreign keys
Hello Keith,
Thanks for responding. I was actually referring to the subject of using
foreign keys, as opposed to leaving it to the calling application to do
the necessary checks. In particular issues of performance, efficiency
etc. IMHO, foreign keys add 'strictness' to the consistency of related
tables. But for me, I have not bothered to do so, preferring to do the
checking from the application instead.
Appreciate your detailed example on normalising the database, which I
agree with you that it is much preferred against a mega table.
[EMAIL PROTECTED] wrote:
IMHO I think you will find that there is a balance between
the speed of opening and reading/writing several related
smaller tables connected by FK's, rather than one mega-sized
gigantic table.
How do you normalise a table without using FK's.
Your right, MySQL does not currently do any checking for
FK's, but this does not mean that you cannot still use them
in MyISAM tables.
Eg.
/* table to store quiz questions */
CREATE TABLE `quiz_question` (
`ID` mediumint UNSIGNED NOT NULL auto_increment,
`question_text` text NOT NULL,
PRIMARY KEY `ID` (`ID`)
) TYPE=MyISAM AUTO_INCREMENT=1;
/* table to store quiz answers */
CREATE TABLE `quiz_answer` (
`ID` mediumint UNSIGNED NOT NULL auto_increment,
`answer_text` text NOT NULL,
`status` enum('wrong', 'right') NOT NULL,
`questionID` mediumint UNSIGNED NOT NULL default '0',
PRIMARY KEY `ID` (`ID`),
KEY `questionID` (`questionID`)
) TYPE=MyISAM AUTO_INCREMENT=1;
/* table to track quiz questions with user answers */
CREATE TABLE `quiz_result` (
`ID` mediumint UNSIGNED NOT NULL auto_increment,
`user` char(32) NOT NULL default '',
`questionID` mediumint UNSIGNED NOT NULL default '0',
`answerID` mediumint UNSIGNED NOT NULL default '0',
PRIMARY KEY `ID` (`ID`),
KEY `questionID` (`questionID`),
KEY `answerID` (`answerID`)
) TYPE=MyISAM AUTO_INCREMENT=1;
So in the quiz_result table above questionID is a
column holding the primary key of each question_text column
in the quiz_question table. It is a foreign key.
answerID is a foreign key that points to the primary
key of the answer submitted by the user doing the quiz.
When the user has finished doing the quiz, the quiz_result
table is scanned for the user session ID, 'user', and then
the question and the user's chosen answer are picked from
the quiz_question and quiz_answer tables, using the foreign
keys in the result table.
I find it helps me to think of foreign keys as unique
pointers to rows in other related tables.
HTH
Keith
In theory, theory and practice are the same;
in practice they are not.
On Mon, 20 Mar 2006, Foo Ji-Haw wrote:
To: mysql@lists.mysql.com
From: Foo Ji-Haw <[EMAIL PROTECTED]>
Subject: Discussion: the efficiency in using foreign keys
Hi all,
This is a fundamental concept in RDBMS: the use of foreign keys in
database design.
I'd just like to poll the community here, on whether it is a best
practice, or practically essential to 'link' related tables by use of
foreign keys.
For myself, I usually do all the validity checking when adding a new
record that references a record id from another table. I understand that
this may not be efficient because it becomes 2 database calls (and db
calls are expensive in high-load environments).
What are the advantages/ disadvantages in using foreign keys? In MySQL,
this means one cannot use MyISAM. Do you place a lot of triggers as well?
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]
Re: what is the sql command to export the whole database ?
tony vong wrote: What is the sql command to export the whole database ? I use: mysqldump -u [username] -p[password] [database name] > [filename] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what is the sql command to export the whole database ?
tony vong wrote: What is the sql command to export the whole database ? I use: mysqldump -u [username] -p[password] [database name] > [filename] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Discussion: the efficiency in using foreign keys
Hi all, This is a fundamental concept in RDBMS: the use of foreign keys in database design. I'd just like to poll the community here, on whether it is a best practice, or practically essential to 'link' related tables by use of foreign keys. For myself, I usually do all the validity checking when adding a new record that references a record id from another table. I understand that this may not be efficient because it becomes 2 database calls (and db calls are expensive in high-load environments). What are the advantages/ disadvantages in using foreign keys? In MySQL, this means one cannot use MyISAM. Do you place a lot of triggers as well? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: performance between InnoDB vs MyISAM
Hey there Ady, Philip, Thanks for the suggestions for the phenomenon. I also notice something along the course of optimisation: 1. Sorting records with huge fields (ie: blobs, text) is significantly slower than if you extract the blobs/ text fields into a separate table. The record size makes a difference in the sorting performance? 2. Sorting by int desc, int desc is much slower than int asc, int asc, where 'int' is an integer field. If (1) is generally true, perhaps to accommodate the potential increase of the table into a million records, I have to break the table into 2 tables: the original table will hold only enough fields for sorting, and the huge fields in the other. What do you guys think? Ady Wicaksono wrote: Hi Foo, MyISAM impress me on insert speed, however on many case MyISAM is not better than Innodb. If you can't use combination of them, better your break down your need to decide which one to use. AFAIK, sub query is better in innodb rather than myisam, and if you have only 200.000 records with huge amount of text, innodb is good enough, just make sure you have enough memory to increase performance. Do you need fulltext SEARCH? If yes, myisam is support this :D not innodb. Foo Ji-Haw wrote: Hi all, Just want to share and confirm my findings on a performance issue I've been experiencing. My database is strictly non-transactional, but it's got about 200,000 records in this particular table. The table has a primary index, and 2 integers - one for the date and the other for the time. Among the other fields there's a text field which usually stores a huge amount of text. One thing I notice, is that under MyISAM running the following sql: select id from mytable where id in (#subselect to extract a set of ids) order by mydate desc, mytime desc The time taken is really bad, like > 90 secs. But in InnoDB it is usually <8 secs. The time difference is too crazy to ignore. Can anyone explain this? Is there something in InnoDB that creates the magic? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
performance between InnoDB vs MyISAM
Hi all, Just want to share and confirm my findings on a performance issue I've been experiencing. My database is strictly non-transactional, but it's got about 200,000 records in this particular table. The table has a primary index, and 2 integers - one for the date and the other for the time. Among the other fields there's a text field which usually stores a huge amount of text. One thing I notice, is that under MyISAM running the following sql: select id from mytable where id in (#subselect to extract a set of ids) order by mydate desc, mytime desc The time taken is really bad, like > 90 secs. But in InnoDB it is usually <8 secs. The time difference is too crazy to ignore. Can anyone explain this? Is there something in InnoDB that creates the magic? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to convert InnoDB to MyISAM in 5.0?
hi guys, it's a simple thing (I think), but I can't find the docu on this from the mysql site. Thanks in advance for the help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Surviving MySQL crash
15 * * * * /usr/local/mysql/bin/mysql -u root -pxx -e 'FLUSH TABLES' This would run a FLUSH TABLES once every hour at 15 minutes past. If you are using Windows, sorry I'm not sure how to do it there. Easier to just set flush_time=900 in my.cnf :) Thanks guys. Will give it a shot. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Surviving MySQL crash
Heikki Tuuri wrote: Ji-Haw, if the OS crashes do not corrupt files, then InnoDB tables would survive an OS crash without a problem. Thanks for the reply Heikki. Let me rephrase my problem: the data remains intact, but I suspect the indexes are corrupted. Basically I have to run myisamcheck and mysqlcheck to get my tables operational again. No data loss though. Logan (thanks Logan) suggested a periodic flush call. I am wondering if auto flushing is available? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Surviving MySQL crash
Hi all, I have a MySQL server servng low-load applications. Problem is, the environment is sometimes unstable, leading the entire OS to crash. I notice that even in low-load situations the MySQL tables can be corrupted during crashes. My question is: is there a way for MySQL to flush when idle for x minutes, or some other way to avoid MySQL crashing (other than moving it out of the environment)? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL will timeout on connection handles
Hi all, I'd like to clarify on this odd issue. It seems to me that MySQL will automatically drop database handles, assumably if the handle has not been used for some time. Is this true? If so, how can I disable timeout? This is an unexpected behavior, because I have tried MSSQL and Postgre, and they don't exhibit this behavior. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
