On Jul 23, 2005, at 1:58 PM, Dan Tappin wrote:
On Jul 23, 2005, at 10:14 AM, Ware Adams wrote:
Has your mysql crashed or have your restarted the machine without
first shutting down mysql manually? We only saw this error when
mysql was not shut down normally. You can look in your .err file
which should be in your data directory. Every mysql sartup
sequence should be preceded by a line saying "mysqld ended." If
not it's crashing and that's the issue to address.
Nope... nothing like that... just lots of:
[ERROR] Got error 127 when reading table
No crashes at all.
Then I don't know what to say about the MyISAM error, though I'd
watch InnoDB pretty carefully as I think MyISAM is pretty robust on
OS X too. We did also see these when a disk is failing, but I assume
you've run disk utility. I guess it could be failing and not showing
up in disk utility. If it's an XServe the SMART data might show you
that, or if you have DiskWarrior I think it might monitor SMART data
for you.
2.) Try Innodb
This is a MyISAM error, but if mysqld is crashing or being
shutdown abnormally InnoDB too will have to go through a crash
recovery at startup (it's just automatic, but you'll see it in
the .err file).
so the repair is manual on MyISAM and auto on start-up for Innodb?
Yes, but that's after a crash. When you start InnoDB after a crash
you'll see:
050722 16:26:38 mysqld started
050722 16:26:40 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
050722 16:27:09 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 2875 3750200540.
InnoDB: Doing recovery: scanned up to log sequence number 2875
3755443200
...
InnoDB: Doing recovery: scanned up to log sequence number 2875
3878800255
050722 16:27:18 InnoDB: Starting an apply batch of log records to
the database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38
39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61
62 63 64 65 66 67 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
050722 16:30:11 InnoDB: Flushing modified pages from the buffer pool...
050722 16:30:33 InnoDB: Started; log sequence number 2875 3878800255
/usr/local/mysql/bin/mysqld: ready for connections.
Version: '4.1.12-standard-log' socket: '/tmp/mysql.sock' port:
3306 MySQL Community Edition - Standard (GPL)
But that's after a power failure when and InnoDB could tell it had
issues when we restarted. The thing I would be concerned about in
your case is what's going to happen if the same thing that causes
issues for MyISAM has an impact on InnoDB. If it were crashes InnoDB
can handle that pretty well, but if it's some underlying disk issue
then InnoDB could wind up with it's table space corrupt just like
MyISAM.
The issue with InnoDB is that the tablespace files are more dependent
on each other. So if you have a corrupt MyISAM table you could
literally shut mysqld down, delete the files for that table and
everything else is fine. You'd lose the data for that table but not
anything else.
With InnoDB you have the transaction logs, the shared tablespace
files and if you are using file_per_table a tablespace (.ibd) file
for each table. At least the first two of these are completely
dependent on each other. If you wind up with a corrupted shared
tablespace file due to a disk error you can potentially lose
everything. The transaction logs can be replaced, but only if there
are no uncommitted transactions in them. If you lose a .ibd file in
file_per_table you would just lose that table.
I probably didn't explain that very well, you might want to read the
sections on InnoDB's various files in the InnoDB manual
http://www.innodb.com/ibman.php
In short, InnoDB deals better than MyISAM with crashes of the mysqld
server or the OS, but I don't think it's necessarily any better (nor
maybe could it be) with dealing with an underlying disk corruption
issue that keeps occurring.
I am also wondering about back-ups. The docs seem to indicate
that you can still run mysqldump... but how does this effect
restoring. In my case I can afford to shutdown the server in the
wee hours of the morning and back-up the data dir manually.
1) Shut down and manually copy, but make sure you get all the
InnoDB parts...data files and transaction logs. One is pretty
much useless without the other.
2) mysqldump, look at the options. This with InnoDB is pretty
nice b/c you can use --single-transaction and get a point in time
copy of the table without disrupting other users. In particular
the comments on the mysql documentation page for mysqldump detail
a good set of flags for InnoDB.
3) The (non-free) ibbackup which takes a backup while running
without disturbing users
Im my situation I'm ok with shutting everyone out (via PHP which
the only access to the db) and running either type of back-up.
Well that's a very safe way to do it if the tablespace is OK.
So if I read you correctly mysqldump locks every one out and dumps.
Yes, with MyISAM.
But with Innodb you can add '--single-transaction' and the dump
the data at that point in time which users continue to work with
the database?
Yes, but only with InnoDB b/c it is a transactional database. Here's
the manual description for --single-transaction:
This option issues a BEGIN SQL statement before dumping data from
the server. It is useful only with transactional tables such as
InnoDB and BDB, because then it dumps the consistent state of the
database at the time when BEGIN was issued without blocking any
applications.
So using this you'll have a consistent point in time copy of the
table, and other users can continue to select and insert from the
table without issue. That's the nice thing about InnoDB.
Using mysqldump you will also get a text backup, and will be much
more likely to discover tablespace corruption early on. If you can
select all the records successfully from the table and write them out
to disk you're a little more certain your tablespace is OK, and you
know that you can reimport that table into a fresh tablespace on a
fresh disk if need be. By just copying the InnoDB tablespace with
everything shut down you might just be copying the corruption.
I don't think there's any fundamental issue with MySQL (either
myisam or innodb) on OS X, but I do think that MySQL is something
that doesn't do well when run only through Apple's front end. It
has two many options, configuration details, etc... if you are
doing anything more than a few small tables. Plus, if you are
relying on it all you'll want to be able to take advantage of bug
fixes faster than Apple's MySQL updates allow you too.
Fortunately, it's easy enough to download the mysql binary and use/
configure it independently. We've had quite good luck with MySQL
on OS X with a pretty big install, but there are a lot of details
to learn so it can seem tough to work out at times.
Well Apple does not really have a front end for MySQL at all. I've
gone away from Apple's updates and install my own version from the
source packages provided by MySQL.
That's the problem in my view. They've got that little application
that will get it up and running for you and that's it. So at first
it feels like you'll have control over it like they give you control
over apache, but you really don't. And for any moderately serious
use you have to have pretty full control over MySQL and you'll want
to be able to do bug-fix upgrades. Apple seems to let people get a
bunch of valuable data into MySQL and then not give them the tools to
have it perform or upgrade it (like the issue with OS X releases
requiring a full MySQL dump).
I think they should warn people that if you're going to use MySQL for
anything serious you should do a custom install and just maintain it
yourself.
Good luck,
Ware
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
