How to corrupt a database please???

Hi all, I am hiring a few new junior DBA's and I want to put them thru a
simple db repair training. Does anyone know how I can deliberately
corrupt a MyISAM and InnoDB database in different ways please? So what
I want to do is corrupt 3 MyISAM 100gb databases, 3 InnoDB 100gb
databases - ALL WITH DIFFERENT ERROR MESSAGES and have these newbies fix
them in a 2 hour period :-) I have fixed oodles of db's but NEVER
thought I would see the say where I would WANT to corrupt a db on
purpose, but that day is here and am looking for advise please.

Thanks...

Nunu

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: How to corrupt a database please???

--00504502bd91bc73ea048486528a
Content-Type: text/plain; charset=ISO-8859-1

open the file and remove some data and close it for both data file and index
files, So the tables will be corrupted when access.


On Sun, Apr 18, 2010 at 9:55 PM, Nurudin Javeri wrote:

> Hi all, I am hiring a few new junior DBA's and I want to put them thru a
> simple db repair training. Does anyone know how I can deliberately corrupt
> a MyISAM and InnoDB database in different ways please? So what I want to do
> is corrupt 3 MyISAM 100gb databases, 3 InnoDB 100gb databases - ALL WITH
> DIFFERENT ERROR MESSAGES and have these newbies fix them in a 2 hour period
> :-) I have fixed oodles of db's but NEVER thought I would see the say where
> I would WANT to corrupt a db on purpose, but that day is here and am looking
> for advise please.
>
> Thanks...
>
> Nunu
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=sureshkumarilu@gmail.com
>
>


--
Thanks
Suresh Kuna
MySQL DBA

--00504502bd91bc73ea048486528a--

Re: How to corrupt a database please???

--0016e6dab06346eeb9048486b8e5
Content-Type: text/plain; charset=ISO-8859-1

You can remove the innodb logs and/or the innodb data file. You can also
remove some of the individual .idb files (if you're using file-per-table
option).

On Sun, Apr 18, 2010 at 11:25 AM, Nurudin Javeri wrote:

> Hi all, I am hiring a few new junior DBA's and I want to put them thru a
> simple db repair training. Does anyone know how I can deliberately corrupt
> a MyISAM and InnoDB database in different ways please? So what I want to do
> is corrupt 3 MyISAM 100gb databases, 3 InnoDB 100gb databases - ALL WITH
> DIFFERENT ERROR MESSAGES and have these newbies fix them in a 2 hour period
> :-) I have fixed oodles of db's but NEVER thought I would see the say where
> I would WANT to corrupt a db on purpose, but that day is here and am looking
> for advise please.
>
> Thanks...
>
> Nunu
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=jlyons4435@gmail.com
>
>


--
Jim Lyons
Web developer / Database administrator
http://www.weblyons.com

--0016e6dab06346eeb9048486b8e5--

Re: How to corrupt a database please???

On Sun, Apr 18, 2010 at 10:39 AM, Suresh Kuna wr=
ote:
> open the file and remove some data and close it for both data file and in=
dex
> files, So the tables will be corrupted when access.
>
>
> On Sun, Apr 18, 2010 at 9:55 PM, Nurudin Javeri wrote:
>
>> Hi all, I am hiring a few new junior DBA's and I want to put them thru a
>> simple db repair training. =A0Does anyone know how I can deliberately co=
rrupt
>> a MyISAM and InnoDB database in different ways please? =A0So what I want=
to do
>> is corrupt 3 MyISAM 100gb databases, 3 InnoDB 100gb databases - ALL WITH
>> DIFFERENT ERROR MESSAGES and have these newbies fix them in a 2 hour per=
iod
>> :-) =A0I have fixed oodles of db's but NEVER thought I would see the say=
where
>> I would WANT to corrupt a db on purpose, but that day is here and am loo=
king
>> for advise please.
>>
>> Thanks...
>>
>> Nunu
>>

Umm, shouldn't you train your Junion DBA to:
1. fail off of the corrupted servers.
2. restore from backup.
3. or at least get a non-junior dba and then have them shadow?

I have a problem with the idea of "repairing" Innodb. Depending on
where the corruption (checksum mismatch) has occurred it can be very
difficult to get all the original data out. Don't get me wrong, there
are way to do it, but it is a nasty endeavor.
For that mater I don't trust "repairing" MyISAM all that much either.
I try my very best to keep MyISAM out of production. In my opinion
MyISAM should be treated as something one step higher than the
blackhole engine. Put data in and you might be able to pull it out
later. (don't get me wrong, packed myisam has its place...)

--=20
Rob Wultsch
wultsch@gmail.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: How to corrupt a database please???

On Sun, Apr 18, 2010 at 11:07 AM, Jim Lyons wrote:
> You can remove the innodb logs and/or the innodb data file. =A0You can al=
so
> remove some of the individual .idb files (if you're using file-per-table
> option).
>

He originally asked about how to provide a training excise about
repairing a db. How the hell do you repair from not having data files?
For that matter the recovery from lacking log files (and assuming a
crashed state) is imho ugly as hell.

--=20
Rob Wultsch
wultsch@gmail.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: How to corrupt a database please???

--0016363b806c6fcfa6048486ceea
Content-Type: text/plain; charset=ISO-8859-1

What if the DBA ask for the backup?

And those recommendations can be "fixed" or they have a very high chance of
making recovery impossible?





On Sun, Apr 18, 2010 at 1:09 PM, Rob Wultsch wrote:

> On Sun, Apr 18, 2010 at 10:39 AM, Suresh Kuna
> wrote:
> > open the file and remove some data and close it for both data file and
> index
> > files, So the tables will be corrupted when access.
> >
> >
> > On Sun, Apr 18, 2010 at 9:55 PM, Nurudin Javeri
> wrote:
> >
> >> Hi all, I am hiring a few new junior DBA's and I want to put them thru a
> >> simple db repair training. Does anyone know how I can deliberately
> corrupt
> >> a MyISAM and InnoDB database in different ways please? So what I want
> to do
> >> is corrupt 3 MyISAM 100gb databases, 3 InnoDB 100gb databases - ALL WITH
> >> DIFFERENT ERROR MESSAGES and have these newbies fix them in a 2 hour
> period
> >> :-) I have fixed oodles of db's but NEVER thought I would see the say
> where
> >> I would WANT to corrupt a db on purpose, but that day is here and am
> looking
> >> for advise please.
> >>
> >> Thanks...
> >>
> >> Nunu
> >>
>
> Umm, shouldn't you train your Junion DBA to:
> 1. fail off of the corrupted servers.
> 2. restore from backup.
> 3. or at least get a non-junior dba and then have them shadow?
>
> I have a problem with the idea of "repairing" Innodb. Depending on
> where the corruption (checksum mismatch) has occurred it can be very
> difficult to get all the original data out. Don't get me wrong, there
> are way to do it, but it is a nasty endeavor.
> For that mater I don't trust "repairing" MyISAM all that much either.
> I try my very best to keep MyISAM out of production. In my opinion
> MyISAM should be treated as something one step higher than the
> blackhole engine. Put data in and you might be able to pull it out
> later. (don't get me wrong, packed myisam has its place...)
>
> --
> Rob Wultsch
> wultsch@gmail.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=mr.criptos@gmail.com
>
>

--0016363b806c6fcfa6048486ceea--

Re: How to corrupt a database please???

A couple good tests are.

1. Corrupt a relay log. For this you can stop the sql thread, cat
/dev/urandom over the newest relay log, start the sql thread and watch
it fail.
2. Change the innodb_log_file_size in my.cnf without going through the
proper procedure to remove the old log files. In 5.0 this will cause
incorrect information in frm file errors for queries which will take a
little bit of work to track down.
3. Can some random data over myisam files and run a check tables so
mysql marks them as crashed.

On Sun, Apr 18, 2010 at 11:13 AM, Rob Wultsch wrote:
> On Sun, Apr 18, 2010 at 11:07 AM, Jim Lyons wrote:
>> You can remove the innodb logs and/or the innodb data file. =A0You can a=
lso
>> remove some of the individual .idb files (if you're using file-per-table
>> option).
>>
>
> He originally asked about how to provide a training excise about
> repairing a db. How the hell do you repair from not having data files?
> For that matter the recovery from lacking log files (and assuming a
> crashed state) is imho ugly as hell.
>
> --
> Rob Wultsch
> wultsch@gmail.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Deric.bergen@g=
mail.com
>
>



--=20
Eric Bergen
eric.bergen@gmail.com
http://www.ebergen.net

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: How to corrupt a database please???

On Sun, Apr 18, 2010 at 11:13 AM, Andr=E9s Tello wro=
te:
> What if the DBA ask for the backup?
>
> And those recommendations can be "fixed" or they have a very high chance =
of
> making recovery impossible?
>

Who is the dba going to ask for a backup? Himself? The guy that puts
backups on tape? One way or another the DBA damn well better know how
to get a backup.

Failing off of a server gets you on to a slave which should be sync'd
with the master. If you restore from backup then you can run a pitr .
In my opinion both of these options are usually superior to running
repair table on a production server. That is if you like uptime.

For the record innodb corruption is quite rare, at least in comparison
to MyISAM corruption. If I get a call at 2AM and find a server having
died due to innodb corruption I would fail off of the server. No ifs,
no ands, not buts. I would assume:
1. Possible, perhaps even probably hardware issues if there is Innodb
corruptions.
2. A failover takes a set amount of time. Repairing corruptions will
usually take longer, perhaps much much longer.

--=20
Rob Wultsch
wultsch@gmail.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: How to corrupt a database please???

Rob Wultsch wrote:
> On Sun, Apr 18, 2010 at 11:13 AM, Andr=E9s Tello =
wrote:
>> What if the DBA ask for the backup?
>>
>> And those recommendations can be "fixed" or they have a very high chan=
ce of
>> making recovery impossible?
>>
>=20
> Who is the dba going to ask for a backup? Himself? The guy that puts
> backups on tape? One way or another the DBA damn well better know how
> to get a backup.
>=20
> Failing off of a server gets you on to a slave which should be sync'd
> with the master. If you restore from backup then you can run a pitr .
> In my opinion both of these options are usually superior to running
> repair table on a production server. That is if you like uptime.
>=20
> For the record innodb corruption is quite rare, at least in comparison
> to MyISAM corruption. If I get a call at 2AM and find a server having
> died due to innodb corruption I would fail off of the server. No ifs,
> no ands, not buts. I would assume:
> 1. Possible, perhaps even probably hardware issues if there is Innodb
> corruptions.
> 2. A failover takes a set amount of time. Repairing corruptions will
> usually take longer, perhaps much much longer.
>=20

I agree with Rob. InnoDB failures are nearly always caused by OS-level=20
or HW-level failures. The worst-case scenario is to need to rebuild part =

of your data from whatever information remains in the corrupted file. It =

is much better to restore from backup or rebuild from a slave than to go =

through the pain of rebuilding a corrupted tablespace.

But, here are some ideas on ways to screw one up:

1) Put it on an NFS drive then read from it using another user's account =

while the database is trying to write to it.

2) Scan it with an antivirus program while it is online and actively=20
making changes.

3) Use a hex editor and manually zero out a page of data or index

4) Delete the active log file (or both of them)

5) Turn on two MySQL instances to the same files at the same time.

6) Delete the .frm file for a table

7) Take a backup of the tablespace, change a few things, the restore the =

tablespace but not the logs.

While I can't predict what "kind" of problem you will create for=20
yourself, these are all things that have created problems for others in=20
the past.

--=20
Shawn Green
MySQL Principle Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: DBA questions to ask WAS: How to corrupt a database please???

> -----Original Message-----
> From: Nurudin Javeri [mailto:nsjaveri@idh.com]
> Sent: Sunday, April 18, 2010 9:25 AM
> To: mysql@lists.mysql.com
> Subject: How to corrupt a database please???
>
> Hi all, I am hiring a few new junior DBA's and I want to put
> them thru a
> simple db repair training. Does anyone know how I can deliberately
> corrupt a MyISAM and InnoDB database in different ways
> please? So what
> I want to do is corrupt 3 MyISAM 100gb databases, 3 InnoDB 100gb
> databases - ALL WITH DIFFERENT ERROR MESSAGES and have these
> newbies fix
> them in a 2 hour period :-) I have fixed oodles of db's but NEVER
> thought I would see the say where I would WANT to corrupt a db on
> purpose, but that day is here and am looking for advise please.
>
> Thanks...
>
> Nunu

100GB! That's a HUGE database to "play with" and can take more than hours
to repair. I would tone it down. You just want them to fix it and know what
the commands/steps are, not spend hours sitting there watching a blinking
cursor. We have almost a BILLION rows in our DB and it's only 70GB and we
cringe if we have to alter a table as we know it's going to take a LONG
time.

I would focus on JUNIOR DBA tasks, such as:

How do you start/stop the mysql server?
sudo /etc/init.d/mysql stop|start|restart

Where are the mysql logs found?
(Trick Q: On ubuntu they're stupidly in /var/log/messages !?)

GRANTing permissions (figure out why the script isn't writing to the DB, or
find the security issue with this user, etc.)

Execute a mysql command from the bash command line (not the mysql CLI)

How do you find a slow query (slow query log)

Setup a master/slave and then deliberately write to the slave. This causes
replication to fail then. How do you fix it?

mysql> show slave status\G

Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_Error: Error 'Duplicate key name 'id_operator'' on query.
Default database: 'core'. Query: 'ALTER TABLE `user_has_notification` ADD
INDEX `id_operator` (`id_operator`)'

mysql> stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave; show
slave status\G

What if /var/log/mysql growing too large?
mysql> PURGE BINARY LOGS;
(The command RESET MASTER is not intended while replication slaves are
running.)

Foreign Key Failures?
mysql> show innodb status;

------------------------
LATEST FOREIGN KEY ERROR
------------------------
090604 0:50:37 Cannot drop table `core/city`
because it is referenced by `core/state`

How do you make a backup?
mysqldump -uroot -p --opt --add-drop-database --complete-insert
--quote-names --comments --verbose --databases mydatabase | gzip -c >
~/mydatabase.sql.gz

How would you load that backup back in?
gunzip < ~/mydatagbase.sql.gz | mysql -uroot -p

How do you load a comma separated file with a column header line?
load data infile '/home/prod/user-batch.csv' ignore into table
invitation_request fields terminated by ',' ignore 1 lines;

What if you have lost/forgotten the mysql root password?
http://dev.mysql.com/doc/refman/5.1/en/resetting-permissions .html#resetting
-permissions-unix

You could throw these in for good measure:
mysqlcheck --user=root --password=XXXXXX --check --auto-repair mydatabase
mysqlcheck --user=root --password=XXXXXX --analyze --auto-repair mydatabase

Ask about "mytop" and various field lengths:

# BIGINT UNSIGNED = 8 Byte = FFFFFFFFFFFFFFFF = 18446744073709551615
# INT UNSIGNED = 4 Byte = FFFFFFFF = 4294967295
# MEDIUMINT UNSIGNED = 3 Byte = FFFFFF = 16777215
# SMALLINT UNSIGNED = 2 Byte = FFFF = 65535
# TINYINT UNSIGNED = 1 Byte = FF = 255

# BIGINT SIGNED = -9223372036854775808 to 9223372036854775807
# INT SIGNED = -2147483648 to 2147483647
# MEDIUMINT SIGNED = -8388608 to 8388607
# SMALLINT SIGNED = -32768 to 32767
# TINYINT SIGNED = -128 to 127

# TINYTEXT = 255
# TEXT = 65535
# MEDIUMTEXT = 16777215
# LONGTEXT = 4294967295

# TEXT fields are NOT case sensitive, whereas BLOB fields are.
# Always try to use UNSIGNED integers whenever possible.

Anyways, you get the idea. Don't set the JUNIOR interviewee up for failure
from the start. The questions should be something in the realm of what
they'll be doing on a daily basis. The rest is stuff that Google will solve
if and when that time comes. You want to make sure they have a solid grasp
of mysql, not an expert in it. Don't blast them if they don't have this
stuff memorized. I sure as hell don't, that's what our Wiki is for. You
want people that can find answers, not memorize them.

Daevid.
http://daevid.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org