[vox-tech] Need your help in recovering database.
Soumyadeep nandi
vox-tech@lists.lugod.org
Wed, 23 Apr 2003 23:55:27 -0700 (PDT)
Thank you All,
Thankyou very much for your cooperation. I doubt if I
am able to present you properly.
Replying to Tim:-
> > Didn't find any fields in table 'blast_genescan'
> Does "describe blast_genescan;" return the correct
> columns? If so, maybe the perl script needs a
> closer look.
> >
> > This for all the tables.
> Do you mean that "describe table2;", "describe
> table3;", and "describe table4;" all return no >
columns?
This is after the command "use Entamoeba".
After searching the solution in
"http://www.innodb.com/ibman.html"
I could get I must have specified "max" size. I've
specified the max size along with some server
variables viz.
set-variable = key_buffer=256M
set-variable = max_allowed_pack = 40M
...
set-variable = myisam_sort_buffer_size = 64M
set-variable = thread_cache = 8
set-variable = thread_concurrency = 4
...
innodb_data_file_path =
/ibdata/ibdata1:100M:autoextend:max:2000M
But then I could once get into the database
i.e. by command "use Entamoeba"
It flushed nothing only "database changed"
As I tried to select the rows from one of the four
tables it flushed
"Didn't find any fields in table 'blast_genescan'"
and was same for all other tables.
But after that I cant start server anymore its showing
error:
ERROR 2002: Can't connect to local MySQL server
through socket '/var/lib/mysql/mysql.sock' (2)
Replying to Jeff:-
> The fact that this exceeds 2GB is certainly
> suspicious. There is a warning in the InnoDB
> documentation [1] to beware of OS filesize
> limitations. They don't seem to be specific about >
the consequences, but I would not be surprised if >
the file was corrupt by now. I suspect that
> using multiple data files rather than the >
autoextend option will be necessary if you are >
dealing with large databases.
How would I do this if I have to rebuild the database.
> There are kernel patches (options by now?) that >
support large file sizes, but they require
But mine is 32 bit processor could I reconfigure the
kernel for 64-bit
> some cooperation on the part of the application >
(mysql compile options) before they will take >
effect.
How would I do this?
Is there any way I can recover or take back up of the
database "Entamoeba".
Replying to Tim again:-
> What is the output to "tail /var/log/myysqld.log"?
I am sending the content of mysqld.log file bellow
(after deleting the redundency):
Date in my system is wrong(Wed May 7 13:40:03 IST
2003)
030501 11:31:36 mysqld started
030501 11:31:38 Can't start server: Bind on TCP/IP
port: Address already in use
030501 11:31:38 Do you already have another mysqld
server running on port: 3306 ?
030501 11:31:38 Aborting
030501 11:31:38 /usr/local/mysql/bin/mysqld: Shutdown
Complete
030501 11:31:38 mysqld ended
030501 19:55:58 mysqld started
030501 19:56:03 InnoDB: Database was not shut down
normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 3373779155
InnoDB: Doing recovery: scanned up to log sequence
number 0 3373779155
030501 19:56:04 InnoDB: Flushing modified pages from
the buffer pool...
030501 19:56:04 InnoDB: Started
/usr/local/mysql/bin/mysqld: ready for connections
030501 23:50:12 /usr/local/mysql/bin/mysqld: Normal
shutdown
030501 23:50:12 InnoDB: Starting shutdown...
030501 23:50:16 InnoDB: Shutdown completed
030501 23:50:16 /usr/local/mysql/bin/mysqld: Shutdown
Complete
030501 23:50:19 mysqld ended
030504 01:11:08 mysqld started
InnoDB: The first specified data file ./ibdata1 did
not exist:
InnoDB: a new database to be created!
030504 1:11:15 InnoDB: Setting file ./ibdata1 size
to 10 MB
InnoDB: Database physically writes the file full:
wait...
030504 1:11:20 InnoDB: Log file ./ib_logfile0 did
not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 20 MB
InnoDB: Database physically writes the file full:
wait...
030504 1:11:29 InnoDB: Log file ./ib_logfile1 did
not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 20 MB
InnoDB: Database physically writes the file full:
wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
030504 1:11:42 InnoDB: Started
/usr/local/mysql/bin/mysqld: ready for connections
030504 1:12:28 InnoDB error:
Cannot find table Entamoeba/blast_genescan from the
internal data dictionary
of InnoDB though the .frm file for the table exists.
Maybe you
have deleted and recreated InnoDB data files but have
forgotten
to delete the corresponding .frm files of InnoDB
tables, or you
have moved .frm files to another database?
Look from section 15.1 of
http://www.innodb.com/ibman.html
how you can resolve the problem.
030504 1:12:28 InnoDB error:
Cannot find table Entamoeba/contig_detail from the
internal data dictionary
of InnoDB though the .frm file for the table exists.
Maybe you
have deleted and recreated InnoDB data files but have
forgotten
to delete the corresponding .frm files of InnoDB
tables, or you
have moved .frm files to another database?
Look from section 15.1 of
http://www.innodb.com/ibman.html
how you can resolve the problem.
030504 02:52:05 mysqld started
/usr/local/mysql/bin/mysqld: unrecognized option
`--skip_locking'
/usr/local/mysql/bin/mysqld Ver 3.23.53a-max for
pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB, by Monty and others
This software comes with ABSOLUTELY NO WARRANTY. This
is free software,
and you are welcome to modify and redistribute it
under the GPL license
Starts the MySQL server
Usage: /usr/local/mysql/bin/mysqld [OPTIONS]
--ansi Use ANSI SQL syntax instead of MySQL syntax
-b, --basedir=path Path to installation directory.
All paths are
usually resolved relative to this
--big-tables Allow big result sets by saving all
temporary sets
on file (Solves most 'table full' errors)
--bind-address=IP Ip address to bind to
--bootstrap Used by mysql installation scripts
--character-sets-dir=...
Directory where character sets
are
--chroot=path Chroot mysqld daemon during startup
--core-file Write core on errors
-h, --datadir=path Path to the database root
--default-character-set=charset
Set the default character set
--default-table-type=type
Set the default table type for tables
--delay-key-write-for-all-tables
Don't flush key buffers between writes for any
MyISAM
table
--enable-locking Enable system locking
-T, --exit-info Used for debugging; Use at your own
risk!
--flush Flush tables to disk between SQL commands
-?, --help Display this help and exit
--init-file=file Read SQL commands from this file at
startup
-L, --language=... Client error messages in given
language. May be
given as a full path
--local-infile=[1|0] Enable/disable LOAD DATA LOCAL
INFILE
-l, --log[=file] Log connections and queries to file
--log-bin[=file] Log queries in new binary
format (for replication)
--log-bin-index=file File that holds the names for
last binary log files
--log-update[=file] Log updates to file.# where # is
a unique number
if not given.
--log-isam[=file] Log all MyISAM changes to file
--log-long-format Log some extra information to
update log
--low-priority-updates INSERT/DELETE/UPDATE has
lower priority than selects
--log-slow-queries=[file]
Log slow queries to this log file. Defaults
logging
to hostname-slow.log
--pid-file=path Pid file used by safe_mysqld
--myisam-recover[=option[,option...]] where options
is one of DEAULT,
BACKUP or FORCE.
--memlock Lock mysqld in memory
-n, --new Use very new possible 'unsafe' functions
-o, --old-protocol Use the old (3.20) protocol
-P, --port=... Port number to use for connection
-O, --set-variable var=option
Give a variable an value. --help lists variables
-Sg, --skip-grant-tables
Start without grant tables. This gives all users
FULL ACCESS to all tables!
--safe-mode Skip some optimize stages (for testing)
--safe-show-database Don't show databases for which
the user has no
privileges
--safe-user-create Don't new users cretaion without
privileges to the
mysql.user table
--skip-concurrent-insert
Don't use concurrent insert with MyISAM
--skip-delay-key-write
Ignore the delay_key_write option for all tables
--skip-host-cache Don't cache host names
--skip-locking Don't use system locking. To use
isamchk one has
to shut down the server.
--skip-name-resolve Don't resolve hostnames.
All hostnames are IP's or 'localhost'
--skip-networking Don't allow connection with
TCP/IP.
--skip-new Don't use new, possible wrong routines.
--skip-stack-trace Don't print a stack trace on
failure
--skip-show-database Don't allow 'SHOW DATABASE'
commands
--skip-thread-priority
Don't give threads different priorities.
--socket=... Socket file to use for connection
-t, --tmpdir=path Path for temporary files
--sql-mode=option[,option[,option...]] where option
can be one of:
REAL_AS_FLOAT,
PIPES_AS_CONCAT, ANSI_QUOTES,
IGNORE_SPACE, SERIALIZE,
ONLY_FULL_GROUP_BY.
--transaction-isolation
Default transaction isolation level
--temp-pool Use a pool of temporary files
-u, --user=user_name Run mysqld daemon as user
-V, --version output version information and exit
-W, --warnings Log some not critical warnings
to the log file
--innodb_data_home_dir=dir The common part for
Innodb table spaces
--innodb_data_file_path=dir Path to individual
files and their sizes
--innodb_flush_method=# With which method to flush
data
--innodb_flush_log_at_trx_commit[=#]
Value 0: write and flush once per second
Value 1: write and flush at
each commit
Value 2: write at commit,
flush once per second
--innodb_log_arch_dir=dir Where full logs should
be archived
--innodb_log_archive[=#] Set to 1 if you want to
have logs archived
--innodb_log_group_home_dir=dir Path to innodb log
files.
--skip-innodb Don't use Innodb (will save
memory)
Default options are read from the following files in
the given order:
/etc/my.cnf /usr/local/mysql/var/my.cnf ~/.my.cnf
The following groups are read: mysqld server
The following options may be given as the first
argument:
--print-defaults Print the program argument list and
exit
--no-defaults Don't read default options from any
options file
--defaults-file=# Only read default options from the
given file #
--defaults-extra-file=# Read this file after the
global files are read
To see what values a running MySQL server is using,
type
'mysqladmin variables' instead of 'mysqld --help'.
The default values (after parsing the command line
arguments) are:
basedir:
/usr/local/mysql-max-3.23.53a-pc-linux-gnu-i686/
datadir: /var/lib/mysql/
tmpdir: /tmp/
language:
/usr/local/mysql-max-3.23.53a-pc-linux-gnu-i686/share/mysql/english/
pid file: /var/lib/mysql/localhost.pid
TCP port: 3306
Unix socket: /var/lib/mysql/mysql.sock
system locking is not in use
Possible variables for option --set-variable (-O) are:
back_log current value: 50
binlog_cache_size current value: 32768
connect_timeout current value: 5
delayed_insert_timeout current value: 300
delayed_insert_limit current value: 100
delayed_queue_size current value: 1000
flush_time current value: 0
innodb_mirrored_log_groups current value: 1
innodb_log_files_in_group current value: 2
innodb_log_file_size current value: 5242880
innodb_log_buffer_size current value: 1048576
innodb_buffer_pool_size current value: 8388608
innodb_additional_mem_pool_size current value:
1048576
innodb_file_io_threads current value: 4
innodb_lock_wait_timeout current value: 50
innodb_thread_concurrency current value: 8
innodb_force_recovery current value: 0
interactive_timeout current value: 28800
join_buffer_size current value: 131072
key_buffer_size current value: 8388600
long_query_time current value: 10
lower_case_table_names current value: 0
max_allowed_packet current value: 1048576
max_binlog_cache_size current value: 4294967295
max_binlog_size current value: 1073741824
max_connections current value: 100
max_connect_errors current value: 10
max_delayed_threads current value: 20
max_heap_table_size current value: 16777216
max_join_size current value: 4294967295
max_sort_length current value: 1024
max_tmp_tables current value: 32
max_user_connections current value: 0
max_write_lock_count current value: 4294967295
myisam_max_extra_sort_file_size current value: 256
myisam_max_sort_file_size current value: 2047
myisam_sort_buffer_size current value: 8388608
net_buffer_length current value: 16384
net_retry_count current value: 10
net_read_timeout current value: 30
net_write_timeout current value: 60
open_files_limit current value: 0
query_buffer_size current value: 0
record_buffer current value: 131072
record_rnd_buffer current value: 0
slave_net_timeout current value: 3600
slow_launch_time current value: 2
sort_buffer current value: 2097144
table_cache current value: 64
thread_concurrency current value: 10
thread_cache_size current value: 0
tmp_table_size current value: 33554432
thread_stack current value: 65536
wait_timeout current value: 28800
030504 02:52:05 mysqld ended
030504 5:08:42 /usr/local/mysql/bin/mysqld: Normal
shutdown
030504 5:08:43 InnoDB: Starting shutdown...
030504 5:08:44 InnoDB: Shutdown completed
030504 5:08:44 /usr/local/mysql/bin/mysqld: Shutdown
Complete
030504 05:08:44 mysqld ended
030506 13:00:39 mysqld started
/usr/local/mysql/bin/mysqld: unrecognized option
`--skip_locking'
/usr/local/mysql/bin/mysqld Ver 3.23.53a-max for
pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB, by Monty and others
This software comes with ABSOLUTELY NO WARRANTY. This
is free software,
and you are welcome to modify and redistribute it
under the GPL license
Starts the MySQL server
Usage: /usr/local/mysql/bin/mysqld [OPTIONS]
--ansi Use ANSI SQL syntax instead of MySQL syntax
-b, --basedir=path Path to installation directory.
All paths are
usually resolved relative to this
--big-tables Allow big result sets by saving all
temporary sets
on file (Solves most 'table full' errors)
--bind-address=IP Ip address to bind to
--bootstrap Used by mysql installation scripts
--character-sets-dir=...
Directory where character sets
are
--chroot=path Chroot mysqld daemon during startup
--core-file Write core on errors
-h, --datadir=path Path to the database root
--default-character-set=charset
Set the default character set
--default-table-type=type
Set the default table type for tables
--delay-key-write-for-all-tables
Don't flush key buffers between writes for any
MyISAM
table
--enable-locking Enable system locking
-T, --exit-info Used for debugging; Use at your own
risk!
--flush Flush tables to disk between SQL commands
-?, --help Display this help and exit
--init-file=file Read SQL commands from this file at
startup
-L, --language=... Client error messages in given
language. May be
given as a full path
--local-infile=[1|0] Enable/disable LOAD DATA LOCAL
INFILE
-l, --log[=file] Log connections and queries to file
--log-bin[=file] Log queries in new binary
format (for replication)
--log-bin-index=file File that holds the names for
last binary log files
--log-update[=file] Log updates to file.# where # is
a unique number
if not given.
--log-isam[=file] Log all MyISAM changes to file
--log-long-format Log some extra information to
update log
--low-priority-updates INSERT/DELETE/UPDATE has
lower priority than selects
--log-slow-queries=[file]
Log slow queries to this log file. Defaults
logging
to hostname-slow.log
--pid-file=path Pid file used by safe_mysqld
--myisam-recover[=option[,option...]] where options
is one of DEAULT,
BACKUP or FORCE.
--memlock Lock mysqld in memory
-n, --new Use very new possible 'unsafe' functions
-o, --old-protocol Use the old (3.20) protocol
-P, --port=... Port number to use for connection
-O, --set-variable var=option
Give a variable an value. --help lists variables
-Sg, --skip-grant-tables
Start without grant tables. This gives all users
FULL ACCESS to all tables!
--safe-mode Skip some optimize stages (for testing)
--safe-show-database Don't show databases for which
the user has no
privileges
--safe-user-create Don't new users cretaion without
privileges to the
mysql.user table
--skip-concurrent-insert
Don't use concurrent insert with MyISAM
--skip-delay-key-write
Ignore the delay_key_write option for all tables
--skip-host-cache Don't cache host names
--skip-locking Don't use system locking. To use
isamchk one has
to shut down the server.
--skip-name-resolve Don't resolve hostnames.
All hostnames are IP's or 'localhost'
--skip-networking Don't allow connection with
TCP/IP.
--skip-new Don't use new, possible wrong routines.
--skip-stack-trace Don't print a stack trace on
failure
--skip-show-database Don't allow 'SHOW DATABASE'
commands
--skip-thread-priority
Don't give threads different priorities.
--socket=... Socket file to use for connection
-t, --tmpdir=path Path for temporary files
--sql-mode=option[,option[,option...]] where option
can be one of:
REAL_AS_FLOAT,
PIPES_AS_CONCAT, ANSI_QUOTES,
IGNORE_SPACE, SERIALIZE,
ONLY_FULL_GROUP_BY.
--transaction-isolation
Default transaction isolation level
--temp-pool Use a pool of temporary files
-u, --user=user_name Run mysqld daemon as user
-V, --version output version information and exit
-W, --warnings Log some not critical warnings
to the log file
--innodb_data_home_dir=dir The common part for
Innodb table spaces
--innodb_data_file_path=dir Path to individual
files and their sizes
--innodb_flush_method=# With which method to flush
data
--innodb_flush_log_at_trx_commit[=#]
Value 0: write and flush once per second
Value 1: write and flush at
each commit
Value 2: write at commit,
flush once per second
--innodb_log_arch_dir=dir Where full logs should
be archived
--innodb_log_archive[=#] Set to 1 if you want to
have logs archived
--innodb_log_group_home_dir=dir Path to innodb log
files.
--skip-innodb Don't use Innodb (will save
memory)
Default options are read from the following files in
the given order:
/etc/my.cnf /usr/local/mysql/var/my.cnf ~/.my.cnf
The following groups are read: mysqld server
The following options may be given as the first
argument:
--print-defaults Print the program argument list and
exit
--no-defaults Don't read default options from any
options file
--defaults-file=# Only read default options from the
given file #
--defaults-extra-file=# Read this file after the
global files are read
To see what values a running MySQL server is using,
type
'mysqladmin variables' instead of 'mysqld --help'.
The default values (after parsing the command line
arguments) are:
basedir:
/usr/local/mysql-max-3.23.53a-pc-linux-gnu-i686/
datadir: /var/lib/mysql/
tmpdir: /tmp/
language:
/usr/local/mysql-max-3.23.53a-pc-linux-gnu-i686/share/mysql/english/
pid file: /var/lib/mysql/localhost.pid
TCP port: 3306
Unix socket: /var/lib/mysql/mysql.sock
system locking is not in use
Possible variables for option --set-variable (-O) are:
back_log current value: 50
binlog_cache_size current value: 32768
connect_timeout current value: 5
delayed_insert_timeout current value: 300
delayed_insert_limit current value: 100
delayed_queue_size current value: 1000
flush_time current value: 0
innodb_mirrored_log_groups current value: 1
innodb_log_files_in_group current value: 2
innodb_log_file_size current value: 5242880
innodb_log_buffer_size current value: 1048576
innodb_buffer_pool_size current value: 8388608
innodb_additional_mem_pool_size current value:
1048576
innodb_file_io_threads current value: 4
innodb_lock_wait_timeout current value: 50
innodb_thread_concurrency current value: 8
innodb_force_recovery current value: 0
interactive_timeout current value: 28800
join_buffer_size current value: 131072
key_buffer_size current value: 8388600
long_query_time current value: 10
lower_case_table_names current value: 0
max_allowed_packet current value: 1048576
max_binlog_cache_size current value: 4294967295
max_binlog_size current value: 1073741824
max_connections current value: 100
max_connect_errors current value: 10
max_delayed_threads current value: 20
max_heap_table_size current value: 16777216
max_join_size current value: 4294967295
max_sort_length current value: 1024
max_tmp_tables current value: 32
max_user_connections current value: 0
max_write_lock_count current value: 4294967295
myisam_max_extra_sort_file_size current value: 256
myisam_max_sort_file_size current value: 2047
myisam_sort_buffer_size current value: 8388608
net_buffer_length current value: 16384
net_retry_count current value: 10
net_read_timeout current value: 30
net_write_timeout current value: 60
open_files_limit current value: 0
query_buffer_size current value: 0
record_buffer current value: 131072
record_rnd_buffer current value: 0
slave_net_timeout current value: 3600
slow_launch_time current value: 2
sort_buffer current value: 2097144
table_cache current value: 64
thread_concurrency current value: 10
thread_cache_size current value: 0
tmp_table_size current value: 33554432
thread_stack current value: 65536
wait_timeout current value: 28800
030506 13:00:40 mysqld ended
--- Jeff Newmiller <jdnewmil@dcn.davis.ca.us> wrote:
> On Wed, 23 Apr 2003, Tim Riley wrote:
>
> >
> >
> > Jeff Newmiller wrote:
>
> [...]
>
> > > You don't seem to be familiar with InnoDB
> tables, Tim... they offer some
> > > useful features, but they aren't structured like
> normal MySQL files on
> > > disk. OTOH, my only experience with them has
> been setting them up a couple
> > > of times... I haven't used them for large
> databases, and am just barely
> > > aware of their configuration options.
> >
> > You're right--I dunno about InnoDB. I hope Mysql
> doesn't fall into the feature
> > trap.
>
> They haven't lost their old data storage formats...
> just added some new
> ones.
>
> > The robustness and speed of version 3.22 is due to
> it's simplicity. (I have
> > a client running a database with over 50 tables.
> The largest table has over 40
> > million records, and its datafile size is over 4G,
> and the index file is over 2G.)
>
> You clearly have enabled large file support in your
> kernel and an
> appropriately compiled mysqld. The filesize
> limitation I mentioned is
> from the OS... not InnoDB.
>
> > Mysql's simplicity is due to having 3 files per
> table--definition, data, and index--
> > and letting the operating system do the operating
> system stuff.
>
> I don't see why 3 files is magic... one per table
> could be "simpler" to
> administer, and one file per index could be simpler
> to program (and less
> likely to have bugs).
>
> I do think I see where the KISS principle has merit,
> but there are also
> valid reasons for taking the "data lump" approach
> (OS independence being
> one). I happen to like the "per database" file
> architecture, since you
> can allocate tables to files individually or in
> groups, which makes
> offline archiving easier.
>
> > On the other hand, Oracle was written when
> operating system resources were
> > limited, and therefore Oracle does a lot of
> operating system stuff. Oracle can be very
> > stable if its memory and disk resources are
> monitored
> > and extended as needed. However, this feature-rich
> database engine is bloated,
> > slower, and accident prone.
> >
> > (Just food for thought.)
>
> Yep. Some people need Corvettes, and other people
> need Cadillacs. If
> MySQL wants to have something for everyone I won't
> complain.
>
>
---------------------------------------------------------------------------
> Jeff Newmiller The .....
> ..... Go Live...
> DCN:<jdnewmil@dcn.davis.ca.us> Basics: ##.#.
> ##.#. Live Go...
> Live: OO#..
> Dead: OO#.. Playing
> Research Engineer (Solar/Batteries O.O#.
> #.O#. with
> /Software/Embedded Controllers) .OO#.
> .OO#. rocks...2k
>
---------------------------------------------------------------------------
>
> _______________________________________________
> vox-tech mailing list
> vox-tech@lists.lugod.org
> http://lists.lugod.org/mailman/listinfo/vox-tech
__________________________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo
http://search.yahoo.com