big table corruption
Hi, I have a big table (900k rows, 200M MYD, 200M MYI) with a fulltext index on it. The table gets corrupted every 1 week or so and I have to repair it. I've tried upgrading to newer versions of mysql 3 times but the problem persists. I think it may have something to do with the raid 1 mirror but I can't really tell. Does anyone have any ideas? Thanks, - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: big table corruption
Thanks Heikki,
check table gives me this:
mysql check table resourcesback;
++---+--++
| Table | Op| Msg_type | Msg_text |
++---+--++
| edplanet.resourcesback | check | warning | Table is marked as crashed |
| edplanet.resourcesback | check | error| Found 91296 keys of 91297 |
| edplanet.resourcesback | check | error| Corrupt|
++---+--++
mysql version is: mysql-standard-4.0.21-pc-linux-i686
but I had similar problems with:
mysql-standard-4.0.18-pc-linux-i686 and
mysql-standard-4.1.1-alpha-pc-linux-i686
show create table says:
CREATE TABLE `resourcesback` (
`id` int(11) NOT NULL auto_increment,
`url` varchar(255) NOT NULL default '',
`title` varchar(255) NOT NULL default '',
`keywords` text NOT NULL,
`description` varchar(255) NOT NULL default '',
`rating` tinyint(4) NOT NULL default '0',
`grade` set('Pre-K','K-2','3-5','6-8','9-12','Higher Ed') default NULL,
`media` varchar(255) NOT NULL default '',
`image` varchar(255) NOT NULL default '',
`imageexists` enum('Y','N') NOT NULL default 'N',
`imagewidth` int(11) NOT NULL default '0',
`imageheight` int(11) NOT NULL default '0',
`controversial` enum('Y','N') NOT NULL default 'N',
`contents` set('audio','video','images') default NULL,
`timelinestart` int(11) NOT NULL default '0',
`timelineend` int(11) NOT NULL default '0',
`reviewer` varchar(255) default NULL,
`day` date default NULL,
`status` varchar(255) NOT NULL default 'active',
PRIMARY KEY (`id`),
UNIQUE KEY `url` (`url`),
KEY `media` (`media`),
FULLTEXT KEY `title` (`title`,`description`,`keywords`)
) TYPE=MyISAM
OS is RedHat 9 - 2.4.20-8smp
I'd rather not upload the crashed table because it is really big and it
has some sensitive data.
Thanks,
- Mark
Heikki Tuuri wrote:
Mark,
- Original Message - From: Mark Maggelet
[EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Monday, January 03, 2005 8:36 PM
Subject: big table corruption
Hi,
I have a big table (900k rows, 200M MYD, 200M MYI) with a fulltext index
on it. The table gets corrupted every 1 week or so and I have to repair
it. I've tried upgrading to newer versions of mysql 3 times but the
problem persists. I think it may have something to do with the raid 1
mirror but I can't really tell.
Does anyone have any ideas?
it should not get corrupt. Please file a very detailed bug report to
bugs.mysql.com, if you are willing to upload the corrupt files via ftp
for further analysis.
What does CHECK TABLE print about the corruption? What MySQL version?
What is SHOW CREATE TABLE like? What OS?
Thanks,
- Mark
Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up
MyISAM tables
http://www.innodb.com/order.php
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: big table corruption
Thanks Mike, It's hardware raid, but I don't know the manufacturer. I could try to find out but if the problem is the hardware there isn't much I can do about it anyway without turning it into a big project. I'm not really expecting to solve this, I'm just hoping for some advice on what the problem is most likely to be (raid,kernel or mysql) or maybe there is a variable that I can tweak. The thing that makes me think it's mysql is that it always happens to the same table (out of 300 or so). The table it happens to has the most reads by far but not many writes. any other tips are appreciated. thanks, - Mark mos wrote: At 12:37 PM 1/3/2005, you wrote: Hi, I have a big table (900k rows, 200M MYD, 200M MYI) with a fulltext index on it. The table gets corrupted every 1 week or so and I have to repair it. I've tried upgrading to newer versions of mysql 3 times but the problem persists. I think it may have something to do with the raid 1 mirror but I can't really tell. Does anyone have any ideas? Thanks, - Mark Mark, This might help http://www.google.com/search?hl=enq=mysql+raid+corruptionbtnG=Google+Searchmeta= You can also check the groups http://groups-beta.google.com/groups?q=mysql+raid+corruptionstart=10hl=enlr=; You may want to try and narrow down the results by also supplying your Raid hardware/software that you're using. It could also be processor/OS related. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.1.2 myisamchk chokes on a fulltext
Hi. I'm having this problem on 4.1.2, when I run myisamchk -o on my table I get a bunch of these lines: Duplicate key 3 for record at 56134200 against new record at 244828223 then it segfaults. key 3 is a fulltext. I tried the same thing on the same data with 4.0.14 and it worked fine, I also tried dropping the fulltext and adding it again. myisamchk -e doesn't complain for some reason. Any help very appreciated /thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.1.2 myisamchk chokes on a fulltext
Hi. I'm having this problem on 4.1.2, when I run myisamchk -o on my table I get a bunch of these lines: Duplicate key 3 for record at 56134200 against new record at 244828223 then it segfaults. key 3 is a fulltext. I tried the same thing on the same data with 4.0.14 and it worked fine, I also tried dropping the fulltext and adding it again. myisamchk -e doesn't complain for some reason. Any help very appreciated /thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trouble with Query
I think you want to try 'having mo.date IS NULL' after the order by. Daren wrote: I'm query for a list of offers from a table, but am trying to do a Left Join on the table that keeps track of which members have completed which offers (so that the query will not return offers that the member has already completed). Query: select distinct(ol.id) as id, mo.date from offers_listings ol left join member_offers mo on (mo.member_id = 1) and (ol.id = mo.offer_id) where (ol.location_id = 2) and (ol.active = 'Y') order by ol.weight desc limit 3; Results: +++ | id | date | +++ | 1 | 2004-06-04 | | 2 | NULL | | 3 | NULL | +++ So, member_id 1 has completed offer_id 1, but not offer_id's 2 or 3. I would assume that I simply need to add one more where clause to only return results with a non-null date. I tried: select distinct(ol.id) as id, mo.date from offers_listings ol left join member_offers mo on (mo.member_id = 1) and (ol.id = mo.offer_id) where (ol.location_id = 2) and (ol.active = 'Y') and (mo.date IS NULL) order by ol.weight desc limit 3; However, this does not work - I get an empty result. Which is *really* weird, because if I change the query to only return non-null values, like so: select distinct(ol.id) as id, mo.date from offers_listings ol left join member_offers mo on (mo.member_id = 1) and (ol.id = mo.offer_id) where (ol.location_id = 2) and (ol.active = 'Y') and (mo.date IS NOT NULL) order by ol.weight desc limit 3; It works perfectly: +++ | id | date | +++ | 1 | 2004-06-04 | +++ Of course, this is the opposite of what I want, so I'm quite confused. Can anyone point me in the right direction? TIA! __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlbinlog: unknown command errors
Hi Sasha, Thanks for getting back to me on this. I was hoping to use the binlogs as an alternative to rollback.commit in the event that someone for example hoses a table by doing an update and forgetting the where clause. I want to be able to load a snapshot, dump the binlogs out and find and delete the query that hosed the table and then run the rest through mysql. I don't think I can do this with a master/slave setup. Maybe I would have better luck with version 5? Thanks, - Mark I don't think I can do something li Sasha Pachev wrote: Mark Maggelet wrote: Since I didn't get an answer to this, I'll try asking it another way: has anybody gotten a binlog with binary data (images) to load from one server to another? And if so, what version are you using? Thanks, - Mark Mark: If the latest does not work, it is a bug in mysql command line client that needs to be reported. However, mysqlbinlog has a few other issues and in a general case will not produce a reliable query playback. If you have binlog on a remote server you want played back, a better way to do it is to set up your server as a replication slave to the one containing the binlog, and point it to the start of the binlog with CHANGE MASTER TO -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlbinlog: unknown command errors
Since I didn't get an answer to this, I'll try asking it another way: has anybody gotten a binlog with binary data (images) to load from one server to another? And if so, what version are you using? Thanks, - Mark On Mon, 1 Mar 2004 21:09:22 -0800, Mark Maggelet wrote: Hi, I'm having problems running queries in my binlog that contain binary data. Apparently the / character is being interpreted as a mysql command and I get errors. The queries must have gone through on the original machine because they're there in the binlog. Specs are: original server: mysqld Ver 4.0.14-standard for pc-linux on i686 update server: mysqld Ver 4.0.18-standard for pc-linux on i686 (Official MySQL-standard binary) This is a problem I've had for a while and I've never really gotten the binlog to work the way it's supposed to. Any help greatly appreciated. Thanks, - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dream MySQL Server?
Scanning a PC manufacturer's website, it seems easy to get 4x2.5GHz Xeon, 1Mb L3, 8Gb ram, dual 15000 rpm Scsi with Raid 1 (for performance as well as reliability). Does this sound balanced for a MySQL engine? Or what would other people advise? I think you're better off with 4hd's and 2cpu's then the other way around since that's where your bottleneck will probably be, double the hd's should cut read times in half (but maybe add to write times) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bookings
here's a quick and dirty way to do it:
1) make the seasons table look like this:
dayofyear int
season enum('winter','spring','summer','fall')
rate decimal(5,2)
2) populate 'seasons' with 1-366 for dayofyear and the corresponding
season and daily rate.
3) to get the total rate do a query like:
select sum(seasons.rate) as total,sum(1) as daysbooked from
bookings,seasons where
bookings.id=$id and
seasons.dayofyear BETWEEN DATE_FORMAT(bookingFromDate,%j) AND
DATE_FORMAT(bookingToDate,%j)
I don't know how you're charging weekly rates, but you could just
check if daysbooked=7 and knock a percentage off total.
gl,
- Mark
On Wed, 3 Mar 2004 22:28:20 +1100, Kevin Waterson wrote:
I am (trying) to make a booking systems.
Currently I have a table with 3 timestamps that record bookingDate,
bookingFromDate and bookingToDate
I have another table that records 'seasons'.
This table contains two timestamps that record
seasonStartDate and seasonEndDate
also I have the rates as seasonRateWeekly and seasonRateNightly
What I need to do, is INSERT INTO bookings the bookingFromDate and
bookingToDate, no problem there, but I need to know what rate to
charge them. and if the booking dates overlap seasons, the
appropriate rate needs to be applied.
All replies greatfully recieved,
Kevin
--
__
(_ \
_) )
| / / _ ) / _ | / ___) / _ )
| | ( (/ / ( ( | |( (___ ( (/ /
|_| \) \_||_| \) \)
Kevin Waterson
Port Macquarie, Australia
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlbinlog: unknown command errors
Hi, I'm having problems running queries in my binlog that contain binary data. Apparently the / character is being interpreted as a mysql command and I get errors. The queries must have gone through on the original machine because they're there in the binlog. Specs are: original server: mysqld Ver 4.0.14-standard for pc-linux on i686 update server: mysqld Ver 4.0.18-standard for pc-linux on i686 (Official MySQL-standard binary) This is a problem I've had for a while and I've never really gotten the binlog to work the way it's supposed to. Any help greatly appreciated. Thanks, - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [MySQL] Inserting images into a MySQL DB
On Thu, 16 Aug 2001 14:11:59 -0500, Tim Thorburn
([EMAIL PROTECTED]) wrote:
Hi,
I'm attempting to store jpg images into a MySQL DB using PHP. I've
found a
few sample scripts that should do this for me, however, they don't
seem to
be working. Could someone take a look at this and see if you notice
any
problems?
Thanks
-Tim
?php
if ($submit) {
MYSQL_CONNECT(localhost,user,pass);
mysql_select_db(binary_date);
$data = addslashes(fread(fopen($form_data, r),
filesize($form_data)));
well for one thing the r should be rb since jpegs are binary, and
for another thing you might not want to use addslashes, theres a fn
called mysql_escape or something, that might be more what you want.
$result=MYSQL_QUERY(INSERT INTO binary_date
(description,bin_data,filename,filesize,filetype) .
VALUES
('$form_description','$data','$form_data_name','$form_data_size','$fo
rm_data_type'));
$id= mysql_insert_id();
print pThis file has the following Database ID: b$id/b;
MYSQL_CLOSE();
} else {
?
form method=post action=?php echo $PHP_SELF; ?
enctype=multipart/form-data
File Description:br
input type=text name=form_description size=40
INPUT TYPE=hidden name=MAX_FILE_SIZE value=100
brFile to upload/store in database:br
input type=file name=form_data size=40
pinput type=submit name=submit value=submit
/form
?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 mysql-unsubscribe-
[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: What does this mean
On Fri, 16 Mar 2001 13:35:18 -0500, Hardy Merrill ([EMAIL PROTECTED]) wrote: Sure it's easier, but which is safer? I don't have Charles first message on this topic, but I believe his error message was from logrotate which is a cron job. If Charles put mysqladmin -uuser -ppassword into a cron job, then the user's(root?) password would be in open view to anybody who could view the crontab - granted, you would have to be root to view the root crontab, but putting the password to the MySQL user right in the cron command is just a little too "loose" for me. A safer method would be to "hide" the user/pw in the Unix user's home directory in the ..my.cnf file, as I outlined below. Uh... root can read those too. If you're imagining a situation where someone just rooted you in order to get access to MySQL, I guess it's possible that doing it this way will slow him down by about 5 minutes, but I don't really think it's an issue. - 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 vs Oracle
On Mon, 22 Jan 2001 10:49:28 -0800, Ann Ricchiazzi ([EMAIL PROTECTED]) wrote: Dear mySQL Users, I am trying to choose between mySQL and Oracle for a Linux server. My specific questions are: 1) Will mySQL handle 3000 hits/day well? I'm on a site that does 90,000 database-heavy pages a day and it's doing great. 2) Does mySQL handle multi-media file formats? For example, if I want to store audio clips, or Flash movie clips, or PowerPoint presentations, can I do so? sure, but it's usually easier to keep them on a filesystem, and just put the filename in the database. - Mark - 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
