sql query LOAD DATA INFILE question
sql,query Hi, I am looking at the following situation: I am reading some files arriving every minute and parsing them and creating a set of files ready to be inserted into tables. on the fly. While I am waiting for the next burst of files, I want to insert these into the tables, then erase the files. Normally LOAD DATA INFILE LOCAL works fine here. The problem is that the machine holding these tables is a different one. The question is, is it possible to run LOAD DATA INFILE to do the inserts on the remote machine, or is it better to first ftp the files over, then run LOAD DATA INFILE there? I assume LOAD INFILE is faster than via mysql -u user -ppasswd DBfile.sql where file.sql contains sql INSERT query From the LOAD DATA INFILE documentation it is not clear to me how this can be done, if it can be done. Thanks, - 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
/usr/local/mysql/libexec/mysqld doesn't exist in installation
Trying to install 4.03 from the tarball... Any idea whay I am doing wrong?? Thanks, S.Alexiou Here is what I get ...linux:/usr/local/mysql # scripts/mysql_install_db Installing all prepared tables 020918 21:55:07 ./bin/mysqld: Shutdown Complete To start mysqld at boot time you have to copy support- files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! This is done with: ./bin/mysqladmin -u root password 'new-password' ./bin/mysqladmin -u root -h linux password 'new- password' See the manual for more instructions. NOTE: If you are upgrading from a MySQL = 3.22.10 you should run the ./bin/mysql_fix_privilege_tables. Otherwise you will not be able to use the new GRANT command! You can start the MySQL daemon with: cd . ; ./bin/mysqld_safe You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory: cd sql-bench ; perl run-all-tests Please report any problems with the ./bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at https://order.mysql.com linux:/usr/local/mysql # vi INSTALL-BINARY linux:/usr/local/mysql # bin/mysqld_safe --user=mysql [1] 13054 linux:/usr/local/mysql # The file /usr/local/mysql/libexec/mysqld doesn't exist or is not executable Please do a cd to the mysql installation directory and restart this script from there as follows: ./bin/mysqld_safe. [1]+ Exit 1 bin/mysqld_safe -- user=mysql linux:/usr/local/mysql # ls - l /usr/local/mysql/libexec/* ls: /usr/local/mysql/libexec/*: No such file or directory linux:/usr/local/mysql # ls -l total 4908 drwxr-xr-x 13 root mysql 542 Sep 18 21:55 . drwxr-xr-x 13 root root 387 Sep 11 18:33 .. -rw-r--r--1 root mysql 19106 Aug 24 17:34 COPYING -rw-r--r--1 root mysql 28003 Aug 24 17:34 COPYING.LIB -rw-r--r--1 root mysql 122323 Aug 24 16:43 ChangeLog -rw-r--r--1 root mysql6808 Aug 24 17:34 INSTALL-BINARY -rw-r--r--1 root mysql1937 Aug 24 16:43 README drwxr-xr-x2 root mysql1231 Aug 24 17:39 bin -rwxr-xr-x1 root mysql 773 Aug 24 17:39 configure drwxr-x---2 mysqlmysql 35 Sep 12 17:00 data drwxr-xr-x2 root mysql1331 Aug 24 17:39 include drwxr-xr-x2 root mysql 208 Aug 24 17:39 lib drwxr-xr-x2 root mysql 378 Aug 24 17:39 man -rw-r--r--1 root mysql 2531460 Aug 24 17:32 manual.html -rw-r--r--1 root mysql 2185064 Aug 24 17:32 manual.txt -rw-r--r--1 root mysql 92248 Aug 24 17:32 manual_toc.html drwxr-xr-x6 root mysql 199 Aug 24 17:39 mysql-test drwxr-xr-x2 root mysql 67 Aug 24 17:39 scripts drwxr-xr-x3 root mysql 56 Aug 24 17:39 share drwxr-xr-x7 root mysql 869 Aug 24 17:39 sql-bench drwxr-xr-x2 root mysql 291 Aug 24 17:39 support-files drwxr-xr-x2 root mysql 560 Aug 24 17:39 tests -rw-r--r--1 root root11189 Sep 13 20:27 typescript linux:/usr/local/mysql # vi INSTALL-BINARY linux:/usr/local/mysql # ./bin/mysqld_safe The file /usr/local/mysql/libexec/mysqld doesn't exist or is not executable Please do a cd to the mysql installation directory and restart this script from there as follows: ./bin/mysqld_safe. - 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: /usr/local/mysql/libexec/mysqld doesn't exist in installation
ÐáñÜèåóç Gerald Clark [EMAIL PROTECTED]: chown -R mysql.mysql /usr/local/mysql Thanks, but apparently it still cannot find it: linux:/usr/local/mysql # chown -R mysql.mysql /usr/local/mysql linux:/usr/local/mysql # scripts/mysql_install_db Installing all prepared tables 020919 22:44:19 ./bin/mysqld: Shutdown Complete To start mysqld at boot time you have to copy support- files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! This is done with: ./bin/mysqladmin -u root password 'new-password' ./bin/mysqladmin -u root -h linux password 'new- password' See the manual for more instructions. NOTE: If you are upgrading from a MySQL = 3.22.10 you should run the ./bin/mysql_fix_privilege_tables. Otherwise you will not be able to use the new GRANT command! You can start the MySQL daemon with: cd . ; ./bin/mysqld_safe You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory: cd sql-bench ; perl run-all-tests Please report any problems with the ./bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at https://order.mysql.com linux:/usr/local/mysql # ./bin/mysqld_safe [1] 29610 linux:/usr/local/mysql # The file /usr/local/mysql/libexec/mysqld doesn't exist or is not executable Please do a cd to the mysql installation directory and restart this script from there as follows: ./bin/mysqld_safe. [1]+ Exit 1 ./bin/mysqld_safe linux:/usr/local/mysql # = [EMAIL PROTECTED] wrote: Trying to install 4.03 from the tarball... Any idea whay I am doing wrong?? Thanks, S.Alexiou Here is what I get ...linux:/usr/local/mysql # scripts/mysql_install_db Installing all prepared tables 020918 21:55:07 ./bin/mysqld: Shutdown Complete To start mysqld at boot time you have to copy support- files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! This is done with: ./bin/mysqladmin -u root password 'new-password' ./bin/mysqladmin -u root -h linux password 'new- password' See the manual for more instructions. NOTE: If you are upgrading from a MySQL = 3.22.10 you should run the ./bin/mysql_fix_privilege_tables. Otherwise you will not be able to use the new GRANT command! You can start the MySQL daemon with: cd . ; ./bin/mysqld_safe You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory: cd sql-bench ; perl run-all-tests Please report any problems with the ./bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at https://order.mysql.com linux:/usr/local/mysql # vi INSTALL-BINARY linux:/usr/local/mysql # bin/mysqld_safe -- user=mysql [1] 13054 linux:/usr/local/mysql # The file /usr/local/mysql/libexec/mysqld doesn't exist or is not executable Please do a cd to the mysql installation directory and restart this script from there as follows: ./bin/mysqld_safe. [1]+ Exit 1 bin/mysqld_safe -- user=mysql linux:/usr/local/mysql # ls - l /usr/local/mysql/libexec/* ls: /usr/local/mysql/libexec/*: No such file or directory linux:/usr/local/mysql # ls -l total 4908 drwxr-xr-x 13 root mysql 542 Sep 18 21:55 . drwxr-xr-x 13 root root 387 Sep 11 18:33 .. -rw-r--r--1 root mysql 19106 Aug 24 17:34 COPYING -rw-r--r--1 root mysql 28003 Aug 24 17:34 COPYING.LIB -rw-r--r--1 root mysql 122323 Aug 24 16:43 ChangeLog -rw-r--r--1 root mysql6808 Aug 24 17:34 INSTALL-BINARY -rw-r--r--1 root mysql1937 Aug 24 16:43 README drwxr-xr-x2 root mysql1231 Aug 24 17:39 bin -rwxr-xr-x1 root mysql 773 Aug 24 17:39 configure drwxr-x---2 mysqlmysql 35 Sep 12 17:00 data drwxr-xr-x2 root mysql1331 Aug 24 17:39 include drwxr-xr-x2 root mysql 208 Aug 24 17:39 lib drwxr-xr-x2 root mysql 378 Aug 24 17:39 man -rw-r--r--1 root mysql 2531460 Aug 24 17:32 manual.html -rw-r--r--1 root mysql 2185064 Aug 24 17:32 manual.txt -rw-r--r--1 root mysql 92248 Aug 24 17:32 manual_toc.html drwxr-xr-x6 root mysql 199 Aug 24 17:39 mysql-test drwxr-xr-x2 root mysql 67 Aug 24 17:39 scripts drwxr-xr-x3 root mysql 56 Aug 24 17:39 share drwxr-xr-x7 root mysql 869 Aug 24 17:39 sql-bench drwxr-xr-x2 root mysql 291 Aug 24 17:39 support-files drwxr-xr-x2 root mysql 560 Aug 24 17:39 tests -rw-r--r--1 root root11189 Sep 13 20:27
Re: Need to cleanly uninstall
Looks like there is a problem with 4.0.3 or I am doing something wrong: First I tried to upgrade from rpm, linux:/home/me/TMPOUT # rpm -e MySQL-4.0.3-0.i386.rpm error: package MySQL-4.0.3-0.i386.rpm is not installed linux:/home/me/TMPOUT # rpm -i MySQL-4.0.3-0.i386.rpm package MySQL-4.0.3-0 is already installed linux:/home/me/TMPOUT # rpm -e MySQL-4.0.3-0.i386.rpm error: package MySQL-4.0.3-0.i386.rpm is not installed linux:/home/db/TMPOUT # rpm -q mysql package mysql is not installed linux:/home/db/TMPOUT # rpm -qa | grep MySQL MySQL-shared-4.0.3-0 MySQL-client-4.0.3-0 MySQL-devel-4.0.3-0 MySQL-4.0.3-0 linux:/home/db/TMPOUT # /usr/bin/mysql ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) FIrst, it Looks like rpm for 4.0.3 does not work. ANyone installed from rpm? Second, I went to the tarball: linux:/usr/local # groupadd mysql groupadd: group mysql exists linux:/usr/local # useradd -g mysql mysql useradd: user mysql exists linux:/usr/local # ln -s /usr/local/mysql-4.0.3-beta-pc- linux-gnu-i686 mysql linux:/usr/local # cd mysql linux:/usr/local/mysql # scripts/mysql_install_db Installing all prepared tables Unknown suffix ' ' used for variable 'thread_concurrency' (value '4 # Try number of CPU's*2') ./bin/mysqld: Error while setting value '4 # Try number of CPU's*2' to 'thread_concurrency' Installation of grant tables failed! Examine the logs in ./data for more information. You can also try to start the mysqld daemon with: ./bin/mysqld --skip-grant You can use the command line tool ./bin/mysql to connect to the mysql database and look at the grant tables: shell ./bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in ./data that may be helpful. The latest information about MySQL is available on the web at http://www.mysql.com Please consult the MySQL manual section: 'Problems running mysql_install_db', and the manual section that describes problems on your OS. Another information source is the MySQL email archive. Please check all of the above before mailing us! And if you do mail us, you MUST use the ./bin/mysqlbug script! linux:/usr/local/mysql # I don't get it, since here is /etc/my.cnf Any ideas what I should check?? Thanks, S.Alexiou # Example mysql config file for very large systems. # # This is for large system with memory of 1G-2G where # the system runs mainly # MySQL. # # You can copy this file to # /etc/mf.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is /var/lib/mysql) or # ~/.my.cnf to set user-specific options. # # One can in this file use all long options that the program supports. # If you want to know which options a program support, run the program # with --help option. # The following options will be passed to all MySQL clients [client] #password = your_password port = 3306 socket = /var/lib/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock skip-locking set-variable = key_buffer_size=500M set-variable = key_buffer=100M set-variable = max_allowed_packet=1M set-variable = table_cache=512 set-variable= max_connections=10 set-variable= tmp_table_size=400M set-variable = sort_buffer=2M set-variable = record_buffer=2M set-variable = thread_cache=8 set-variable = thread_concurrency=4 # Try number of CPU's*2 #set-variable = myisam_sort_buffer_size=64M set-variable = myisam_sort_buffer_size=4M # Heiki says add: innodb_data_home_dir= #and then use absolute file paths #innodb_data_file_path=ibdata1:2000M;ibdata2:2000M;ibdat a3:2000M;ibdata4:2000M;ibdata5:2000M;ibdata6:2000M;ibdat a7:2000M;ibdata8:2000M innodb_data_file_path=/var/lib/mysql/ibdata1:2000M;/var/ lib/mysql/ibdata2:2000M;/var/lib/mysql/ibdata3:2000M;/va r/lib/mysql/ibdata4:2000M;/var/lib/mysql/ibdata5:2000M;/ var/lib/mysql/ibdata6:2000M;/var/lib/mysql/ibdata7:2000M ;/var/lib/mysql/ibdata8:2000M;/usr/ibdata/ibdata9:2000M; /usr/ibdata/ibdata10:2000M;/usr/ibdata/ibdata11:2000M;/u sr/ibdata/ibdata12:2000M;/ibdata/ibdata13:2000M;/ibdata/ ibdata14:2000M;/ibdata/ibdata15:2000M;/var/lib/mysql/ibd ata16:2000M;/var/lib/mysql/ibdata17:2000M;/var/lib/mysql /ibdata18:2000M;/var/lib/mysql/ibdata19:2000M;/var/lib/m ysql/ibdata20:2000M;/var/lib/mysql/ibdata21:2000M #;/usr/ibdata/ibdata9:2000M;/usr/ibdata/ibdata10:2000M ## Comment next line if you do not need recovery(the hostname-bin.xxx files) #log-bin ## server-id = 1 #set-variable= open-files-limit=8192 #ulimit is 1024,hard 8192 set-variable = innodb_buffer_pool_size=1100M # so that innodb_buffer_pool size+key_buffer+max_connections* (sort_buffer+record_buffer+2M)=1100+100+10*(2+2+2) =12602000M=RAM set-variable = innodb_additional_mem_pool_size=700M set-variable = innodb_log_file_size=1000M set-variable =
Re: Need to cleanly uninstall
Thanks It's 2.4.18 So, I will try to unistall and install from the tarball, yes? Spiros, linux:/home/db/TMPOUT # rpm -q mysql package mysql is not installed linux:/home/db/TMPOUT # rpm -qa | grep MySQL MySQL-shared-4.0.3-0 MySQL-client-4.0.3-0 MySQL-devel-4.0.3-0 MySQL-4.0.3-0 linux:/home/spiros/TMPOUT # Ok. mypc@linux:~ /usr/bin/mysql ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) mypc@linux:~ /usr/bin/mysqld_safe /usr/bin/mysqld_safe: my_print_defaults: command not found The file /usr/sbin/mysqld doesn't exist or is not executable Please do a cd to the mysql installation directory and restart this script from there as follows: ./bin/mysqld_safe. @linux:~ Does this mean the rpm is no good? What next Possibly. What version of Linux are you using? DSL -- Con te partiro, su navi per mari Che io lo so, no, no non esistono piu Con te io li vivro. (Sartori F, Quarantotto E) - 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
Performance issue: slow inserts
I have a lot of tables, and not all of them are filled
equally.
Inserts to tables that have a lot of entries(see the
count below), take a long time (about .06 secs on the
average in mysql, over 0.09-0.1 in DBI), for example
mysql INSERT INTO T1 VALUES
('3CCF571C1A88118801040302','072','7269','','','',''
,'','',3103,1,24,'2002-09-
01','05:46:52',0.99,13.7560,24.0480,0.0710,190.839,16,0,
0,1,0,0,0,0,0,0,0,0,0,3,4,0,0,0,0,0,0,10,0,'1',1,'0',0,1
,0,'200205010250_ampsc1','AMP',4,'-12015927269-1-95-
3855206',1,0);
Query OK, 1 row affected (0.06 sec)
inserts into tables like T2 which have few elements
take a much shorter time:
mysql INSERT INTO T2 VALUES
('3CCF571C1A88118801040302','072','7269','','','',''
,'','',3103,1,24,'2002-09-
01','05:46:52',0.99,13.7560,24.0480,0.0710,190.839,16,0,
0,1,0,0,0,0,0,0,0,0,0,3,4,0,0,0,0,0,0,10,0,'1',1,'0',0,1
,0,'200205010250_ampsc1','AMP',4,'-12015927269-1-95-
3855206',1,0);
Query OK, 1 row affected (0.00 sec)
mysql SELECT COUNT(*) FROM T2;
+--+
| COUNT(*) |
+--+
| 509 |
+--+
1 row in set (0.00 sec)
mysql SELECT COUNT(*) FROM T1;
+--+
| COUNT(*) |
+--+
| 782910 |
+--+
1 row in set (7.43 sec)
Of course DELETES are similar(actually much worse with
a where), but deletes are not an issue here
mysql DELETE FROM T1 WHERE date ='2002-09-01';
Query OK, 1 row affected (40.19 sec)
mysql DELETE FROM T2 WHERE date ='2002-09-01';
Query OK, 1 row affected (0.24 sec)
I presume the problem is that in the table definitions
I have a couple of unique constraints and
the INSERT time difference is due to mysql
checking all entries in the table for a unique
violation, hence the insert would be slow:
CREATE TABLE T1 (
corrno varchar(43) default NULL,
a varchar(30) default NULL,
b varchar(30) default NULL,
c varchar(30) default NULL,
oc varchar(30) default NULL,
red varchar(30) default NULL,
sanum varchar(30) default NULL,
sbnum varchar(30) default NULL,
tnum varchar(30) default NULL,
o int(5) default NULL,
d int(5) default NULL,
cic int(6) default NULL,
date date default NULL,
start_time time default NULL,
value decimal(30,2) default NULL,
iamtoacm decimal(7,4) default NULL,
iamtoans decimal(7,4) default NULL,
reltorlc decimal(7,4) default NULL,
duration decimal(12,3) default NULL,
rcv int(3) default NULL,
rcvabn int(1) default '0',
loc int(3) default NULL,
reldir int(1) default NULL,
secode int(3) default NULL,
trans int(3) default NULL,
tc int(3) default NULL,
rsp 0 int(3) default NULL,
ccq int(5) default NULL,
ccr int(3) default NULL,
ccc int(3) default NULL,
ccp int(3) default NULL,
ccsan int(3) default NULL,
nata int(2) default NULL,
natb int(2) default NULL,
natc int(2) default NULL,
natocn int(2) default NULL,
natredir int(2) default NULL,
natsccpa int(2) default NULL,
natsccpbint(2) default NULL,
nattrue int(2) default NULL,
acat int(2) default NULL,
bcat int(1) default NULL,
upi char(1) default NULL,
upp int(3) default NULL,
acind char(1) default NULL,
inter int(1) default NULL,
ech int(1) default NULL,
length int(3) default '0',
file varchar(30) default NULL,
link varchar(8) default NULL,
fileind int(11) default NULL,
unicor varchar(100) NOT NULL default '',
stp int(1) default '0',
alarm int(1) default '0',
UNIQUE KEY unicor (unicor),
UNIQUE KEY corrno (corrno)
) TYPE=InnoDB;
The question is what can be done about this?
I guess getting rid of the unique constraints
would help, but then I would be getting some
unwanted duplicates. I should add that currently
I need to do about 100,000
inserts/10 minutes and the system is too slow for this
as it is, while it used to be very fast. So, I am
looking fro what can be done to avoid performance
degradation
as the tables fill.
The server is a dual PIII x1000, 2GB RAM Linux 2.4.18
machine but the inserts are done from a client(a 1000
PIII , 1GB RAM Linux machine). Below is /etc/my.cnf
# Example mysql config file for very large systems.
#
# This is for large system with memory of 1G-2G where
the system runs mainly
# MySQL.
#
# You can copy this file to
# /etc/mf.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options
(in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# One can in this file use all long options that the
program supports.
# If you want to know which options a program support,
run the program
# with --help option.
# The following options will be passed to all MySQL
clients
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
set-variable = key_buffer_size=500M
set-variable = key_buffer=100M
set-variable = max_allowed_packet=1M
avoiding skip locking
I am doing some automated mysql -u user -ppasswd database insertfile.sql in a loop and have some skip locking presumably because there are a number of jobs running to do this (with different files, which however use the same tables). Should I rather do line by line inserts for speed? Thanks, S.Alexiou - 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: Table full with InnoDB
Well, it will NOT let me add more ibdata files: Any ideas?? Thanks, Spiros Here is hostname.err (On a 2GB RAM, 2x1000 CPU, ) 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 3 751045500 020418 0:50:33 InnoDB: Started /usr/sbin/mysqld: ready for connections InnoDB: Assertion failure in thread 40971 in file trx0trx.c line 228 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=402649088 record_buffer=2093056 sort_buffer=2097144 max_used_connections=1 max_connections=10 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer) *max_connections = 434131 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Stack range sanity check OK, backtrace follows: 0x807db7f 0x823d60a 0x8179eac 0x80d50b7 0x80cd82d 0x807798d 0x807d88a 0x8082515 0x824f1b8 0x823d383 0x823ab69 0x807d905 0x8084403 0x823baa7 0x826c1ca New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at (nil) is invalid pointer thd-thread_id=2916 Successfully dumped variables, if you ran with --log, take a look at the details of what thread 2916 did to cause the crash. In some cases of really bad corruption, the values shown above may be invalid. The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0 020418 14:01:56 mysqld restarted InnoDB: Warning: operating system error number 2 in a file operation. InnoDB: Cannot continue operation. # Example mysql config file for very large systems. # # This is for large system with memory of 1G-2G where the system runs mainly # MySQL. # # You can copy this file to # /etc/mf.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is /var/lib/mysql) or # ~/.my.cnf to set user-specific options. # # One can in this file use all long options that the program supports. # If you want to know which options a program support, run the program # with --help option. # The following options will be passed to all MySQL clients [client] #password = your_password port= 3306 socket = /var/lib/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock skip-locking set-variable= key_buffer=384M set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= max_connections=10 set-variable= tmp_table_size=400M set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= thread_cache=8 set-variable= thread_concurrency=4 # Try number of CPU's*2 #set-variable = myisam_sort_buffer_size=64M set-variable= myisam_sort_buffer_size=4M innodb_data_file_path=ibdata1:2000M;ibdata2:2000M;ibdata 3:2000M;ibdata4:2000M;ibdata5:2000M;ibdata6:2000M;ibdata 7:2000M;ibdata8:2000M;/usr/ibdata/ibdata9:2000M;/usr/ibd ata/ibdata10:2000M #Comment works ok with: #innodb_data_file_path=ibdata1:2000M;ibdata2:2000M;ibdat a3:2000M;ibdata4:2000M;ibdata5:2000M;ibdata6:2000M;ibdat a7:2000M;ibdata8:2000M # Also adding ibdata9:2000M to /var/lib/mysql does not work log-bin server-id = 1 #set-variable= open-files-limit=8192 #ulimit is 1024,hard 8192 set-variable = innodb_buffer_pool_size=750M set-variable = innodb_additional_mem_pool_size=400M # Uncomment the following if you are using BDB tables #set-variable = bdb_cache_size=384M #set-variable = bdb_max_lock=10 # Point the following paths to different dedicated disks #tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname [mysqldump] quick set-variable= max_allowed_packet=256M [mysql] no-auto-rehash #safe-updates # Remove the comment character if you are not familiar with SQL
Error 1114 table full with 14000 error??
I am running Mysql 4.0 with InnoDB on a linux 2.4.0
machine
I am doing a mass import of a file with some 40
inserts
and I get a strange unknown error 1114
Interestingly enough , this is not exactly
reproducible, i.e.
the error occurs in slightly different import
positions.
I have been able thus far to successfully import at
least 10 such files
with the same size with no problems.
Any ideas of what is wrong??
Thanks, S.Alexiou
sp@qu5:~/NEW4 perror 1114
Error code 1114: Unknown error 1114
sp@qu5:~/NEW4 su
Password:
root@qu5:/home/sp/NEW4 ulimit -n 8192
root@quy5:/home/sp/NEW4 ulimit -a limit
core file size (blocks) 0
data seg size (kbytes) unlimited
file size (blocks) unlimited
max locked memory (kbytes) unlimited
max memory size (kbytes)unlimited
open files 8192
pipe size (512 bytes) 8
stack size (kbytes) unlimited
cpu time (seconds) unlimited
max user processes 32767
virtual memory (kbytes) unlimited
root@qu5:/home/sp/NEW4 cat /proc/sys/fs/file-nr
140273 8192
root@qu5:/home/sp/NEW4
root@qu5:/home/sp/NEW4 mysql -u sp -p DB1
newbackfrom20011009_ermsc1.sql
Enter password:
ERROR 1114 at line 111235: The table 'DR_OUTGOING_49_1'
is full
root@qu5:/home/sp/NEW4 vi
newbackfrom20011009_ermsc1.sql
root@qu5:/home/sp/NEW4 mysql -u sp -p DB1
newbackfrom20011009_ermsc1.sql
Enter password:
ERROR 1114 at line 119737: The table 'DR_TRANSIT_78_0'
is full
root@quality5:/home/sp/NEW4
How big is that table?
from kmysqladmin I get:
SELECT * FROM CDR_TRANSIT_78_0 ORDER BY anum LIMIT 9
46942 row(s) found
The table newbackfrom20011009_ermsc1.sql looks
like this:
--
set autocommit=0;
INSERT INTO DATES (donedate) VALUES('2001-10-09') ;
INSERT INTO DR_TR_389_0 VALUES(
'','389222963',4129,5857,2,'2001-10-
08','22:59:35',0,0,0,0.205625057220459,0,28,4,'AAA1',1,0
,3,'','','',10,0,'1','1',2,'','','20011009_ermsc1',1
,0,'','','','','-128-144-163-49-2-0-90-58-6-68-3-87-0-
0-',3,'','' );
commit;
---
Similarly, I get the same type of error when doing a
report vi a gui-driven
perl script on a differnt table:
Tk::Error: DBD::mysql::st execute failed: The table
'TMP2' is full at report79.pl line 404.
[\\main::__ANON__]
SELECT * FROM TMP2 ORDER BY date LIMIT 9
16019 row(s) found
---
sp@qu5:~/QUER df -h
FilesystemSize Used Avail Use% Mounted on
/dev/sda6 9.7G 4.8G 4.9G 49% /
/dev/sda7 4.6G 3.0G 1.6G 66% /var
/dev/sda1 23M 4.8M 16M 22% /boot
/dev/sda8 20G 14G 5.6G 72% /home
/dev/fd0 1.4M 821k 603k 58% /floppy
Here i s/etc/my.cnf
# Example mysql config file for very large systems.
#
# This is for large system with memory of 1G-2G where
the system runs mainly
# MySQL.
#
# You can copy this file to
# /etc/mf.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options
(in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# One can in this file use all long options that the
program supports.
# If you want to know which options a program support,
run the program
# with --help option.
# The following options will be passed to all MySQL
clients
[client]
#password = your_password
port= 3306
socket = /var/lib/mysql/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port= 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
set-variable= key_buffer=384M
set-variable= max_allowed_packet=1M
set-variable= table_cache=512
set-variable= sort_buffer=2M
set-variable= record_buffer=2M
set-variable= thread_cache=8
set-variable= thread_concurrency=2 # Try number of
CPU's*2
set-variable= myisam_sort_buffer_size=64M
innodb_data_file_path=ibdata1:2000M;
log-bin
server-id = 1
#set-variable= open-files-limit=8192 #ulimit is
1024,hard 8192
# Uncomment the following if you are using BDB tables
#set-variable = bdb_cache_size=384M
#set-variable = bdb_max_lock=10
# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname
[mysqldump]
quick
set-variable= max_allowed_packet=256M
[mysql]
no-auto-rehash
#safe-updates # Remove the comment character if you
are not familiar with SQL
[isamchk]
set-variable= key_buffer=256M
set-variable= sort_buffer=256M
set-variable= read_buffer=2M
set-variable= write_buffer=2M
[myisamchk]
set-variable= key_buffer=256M
set-variable= sort_buffer=256M
set-variable= read_buffer=2M
set-variable= write_buffer=2M
[mysqlhotcopy]
interactive-timeout
-
InnoDB Clarifications for InnoDB newbie
Hi, I am using 4.0 and switching from MyISAM to InnoDB, so quite newbie on this: I have a couple of questions: 1) First, I read in the docs that the minimal thing to do is to add to /etc/my.cnf innodb_data_file_path=ibdata/ibdata1:2000M (although the ibdata file is some 67M in /var/lib/mysql) which resulted in me not being able to connect with /usr/bin/mysqld_safe . When I commented out the innodb line, I could connect fine. If I need InnoDB, does this mean that I should comment out the myIsam settings(like table-cache etc)? Can one perhaps provide a working /etc/my.cnf for a fairly large (PIIIx1000MHZ, 1GB RAM, 37GB HD SCSI) machine with INNODB? 2) I also assume that in the same database it is NOT possible to have some InnoDB and some MyISAM tables. Is that right? 3) Assuming I manage to get /etc/my.cnf right, I understand that: a) mysqld should not be started automatically Is that right? b) I do not need to recreate the database, just do an ALTER TABLE for all database tables, right? Thanks, Spiros Alexiou P.S I am moving from MyISAM because of bad corruption problems. Is it possible to get table corruption with InnoDB and if so, under what circumstances? - 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
Need to understand mysql mechanisms
I am sending this mail in order to get things straight about table corruption which I am experiencing with 4.0(as well as previous versions). 1)System specs: PIII x1000MHz, 1GB RAM, HD 37GB SCSI, AHA29160N SCSI controller 2) Database MyISam tables, BUT 21000 tables in the database(this is for performance reasons. I need for each such table to run a number of queries and in this way I already have 'VIEWS', except they are permament). I could split that in 4 different databases if it is a problem. 3) I import the data from mysqldumps(that is, the program that reads the data does not directly import them in the database, partly because of the corruption problems, but also partly because I want backups anyway and instead produces files that are importable just like mysqldumps. These files are of the form 'INSERT INTO TABLE1 (.);' conceivably for all 21000 tables. I assume corruption happens at the inverse mysqldump phase. Does this mean that if I break up the dump file into many smaller files, so that say one file consists of all imports TABLES1-100, the second files 101-200 etc corruption will not be a problem?? 4) Corruption is a tossup. The import always works. However, when I run the queries sometimes they all run fine, while other times I get an error 127 on the table it is working on. myisamchk NEVER manages a complete recovery of all records(typically 25% of the records). This makes it hard to tell for sure if this has anythingto do with the size of data imported, since the more files I import the larger the probability of having corruption. I have had corruption on a single import too though. 5)Before writing a bug report(the problem may be that this is erratic and even on my PC corruption is not consistent, e.g. I may import a file and get corruption, or I may import another file without corruption) and hence reproducibility may be problematic., I would appreciiate someone setting me straight on the filedescriptor issues(thanks Sinisa thus far): -It is not clear to me if the problem may be that when mysql is importing data, it may be having more files open than it(or the fs-reiser in this case) can handle. Could this be the reason for (table)corruption? -If so, accordingto the manual, there are 2 ways around: either decrease table_cache in /etc/my.cnf (currently 512) which however may also affect performance as I have a lot of tables or increase ulimit/open-files-limit -The question on this first option(table_cache) is: if now I have 2000 open files and reduce the cache to 256 does it mean I will be getting half the open files? -Regarding the more desirable option of increasing the limit, are the two ways equivalent(that is, does open- file-limit call ulimit?) Is changing either of them enough , or-as I assume does one really have to change ulimit? -I have a ulimit -a limit giving a 1024 open file limit while cat /proc/sys/fs/file-nr gives a hard limit of 8192. However, the man page says the system may not allow you to change ulimit I did a ulimit -n 8192 as root then ulimit -a limit says 8192 but when I open a window as a regular user, and do ulimit -a limit it is still 1024. Rebooting comes back with 1024 Is it at all possible to change it? -Also, if I can/have to change open-files-limit, do I need to do it in /etc/my.cnf /usr/bin/mysqld_safe /both? Last, I have a start_Mysql=yes in rc.config but in contrast to other systems used mysql, mysql does not start when the system boots. Any ideas why? Sorry for the many questions. Thanks in advance S.Alexiou - 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
Table corruption 4.0
I am sending this mail in order to get things straight about table corruption which I am experiencing with 4.0(as well as previous versions). 1)System specs: PIII x1000MHz, 1GB RAM, HD 37GB SCSI, AHA29160N SCSI controller 2) Database MyISam tables, BUT 21000 tables in the database(this is for performance reasons. I need for each such table to run a number of queries and in this way I already have 'VIEWS', except they are permament). I could split that in 4 different databases if it is a problem. 3) I import the data from mysqldumps(that is, the program that reads the data does not directly import them in the database, partly because of the corruption problems, but also partly because I want backups anyway and instead produces files that are importable just like mysqldumps. These files are of the form 'INSERT INTO TABLE1 (.);' conceivably for all 21000 tables. 4) Corruption is a tossup. The import always works. However, when I run the queries sometimes they all run fine, while other times I get an error 127 on the table it is working on. myisamchk NEVER manages a complete recovery of all records(typically 25% of the records). This makes it hard to tell for sure if this has anythingto do with the size of data imported, since the more files I import the larger the probability of having corruption. I have had corruption on a single import too though. 5)Before writing a bug report(the problem may be that this is erratic and even on my PC corruption is not consistent, e.g. I may import a file and get corruption, or I may import another file without corruption) and hence reproducibility may be problematic., I would appreciiate someone setting me straight on the filedescriptor issues(thanks Sinisa thus far): -It is not clear to me if the problem may be that when mysql is importing data, it may be having more files open than it(or the fs-reiser in this case) can handle. Could this be the reason for (table)corruption? -If so, accordingto the manual, there are 2 ways around: either decrease table_cache in /etc/my.cnf (currently 512) which however may also affect performance as I have a lot of tables or increase ulimit/open-files-limit -The question on this first option(table_cache) is: if now I have 2000 open files and reduce the cache to 256 does it mean I will be getting half the open files? -Regarding the more desirable option of increasing the limit, are the two ways equivalent(that is, does open- file-limit call ulimit?) Is changing either of them enough , or-as I assume does one really have to change ulimit? -I have a ulimit -a limit giving a 1024 open file limit while cat /proc/sys/fs/file-nr gives a hard limit of 8192. However, the man page says the system may not allow you to change ulimit I did a ulimit -n 8192 as root then ulimit -a limit says 8192 but when I open a window as a regular user, and do ulimit -a limit it is still 1024. Rebooting comes back with 1024 Is it at all possible to change it? -Also, if I can/have to change open-files-limit, do I need to do it in /etc/my.cnf /usr/bin/mysqld_safe /both? Last, I have a start_Mysql=yes in rc.config but in contrast to other systems used mysql, mysql does not start when the system boots. Any ideas why? Sorry for the many questions. Thanks in advance S.Alexiou - 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
strange table corruption in 4.0
Hi, I am trying version 4 with myIsam tables. (I am almost ready to move to some other hopefully safer type ). The problem is I get table corruption. I have a large database(21000 tables, but the whole /var/lib/mysql/mydatabase directory is less than 800MB) database. I know this is strange, but there is a lot of work to be done per table and it is a waste to have to search every time I filled these tables in using some mysqldumps. Then I have a script to process them. It all goes well, until it gets a corrupted table. Corruption is, error 127 myisamchk -r claims to recover only 1/4 of the records. Even so, when I try to look at them ,the table remains corrupted(127) I go to /var/lib/mysql/mydatabase and erase this table (the .frm, .MYI and .MYD files. ) then I recreate the table (justthe definition) from file 1330.sql No complaints. Then I connect to the database and get the COUNT It says 23, which was the count I believe before the corruption EVEN THOUGH 1330.sql has NO data, just the table definitions. Then I try to SELECT a field and it says error 127. Could this be related to my large # of tables? Are there any settings say in my.cnf to change? . myuser@quality5:~/BACKUPN mysql -u myuser -p CDR 1330.sql Enter password: myuser@quality5:~/BACKUPN mysql -u myuser -pxx CDR Welcome to the MySQL monitor. Commands end with ; or \\g. Your MySQL connection id is 479 to server version: 4.0.0-alpha-log Type 'help;' or '\\h' for help. Type '\\c' to clear the buffer. mysql SELECT COUNT(*) FROM TABLE_1330_1; +--+ | COUNT(*) | +--+ | 23 | +--+ 1 row in set (0.01 sec) mysql SELECT file FROM TABLE_1330_1; ERROR 1030: Got error 127 from table handler mysql Here is my my.cnf (system is: 1 PIII x1000MHz 1 37GB SCSI HD 1GB RAM AHA29160N SCSI controller-does this matter? /var/log/messages has nothing relevant # Example mysql config file for very large systems. # # This is for large system with memory of 1G-2G where the system runs mainly # MySQL. # # You can copy this file to # /etc/mf.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is /var/lib/mysql) or # ~/.my.cnf to set user-specific options. # # One can in this file use all long options that the program supports. # If you want to know which options a program support, run the program # with --help option. # The following options will be passed to all MySQL clients [client] #password = your_password port= 3306 socket = /var/lib/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock skip-locking set-variable= key_buffer=384M set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= thread_cache=8 set-variable= thread_concurrency=2 # Try number of CPU's*2 set-variable= myisam_sort_buffer_size=64M log-bin server-id = 1 # Uncomment the following if you are using BDB tables #set-variable = bdb_cache_size=384M #set-variable = bdb_max_lock=10 # Point the following paths to different dedicated disks #tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname [mysqldump] quick set-variable= max_allowed_packet=256M [mysql] no-auto-rehash #safe-updates # Remove the comment character if you are not familiar with SQL [isamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout Any ideas or any suggestions?? P.S. If I change to InnoDB or something will these error 127 messages disappear? Thanks, S.Alexiou - 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
MYSQL crashes-SOLUTION found!
I am posting this in the hope it will be useful; I seem to have solved the problem. The problem was(on 3.23.38) and Linux 2.2.18 or 2.4.0 This is a huge system, all SCSI, 2GB or RAM, 2PIII x1000MHz processors and 37GB in the volume group corresponding to /var/lib/mysql There is an AHA29160 SCSI controller. A scipt was running continuously getting data into Mysql. Every so often the script peformed a mysqldump The problem was that at some point when I tried to access the database I would get a crash with error code 127. repair table of myisamchk -r could fix that, but not all data was being recovered. Similarly the dumps were corrupted. This was apparently caused by timeouts and the solution was to pass the boot parameter aic7xxx=seltime:0 in lilo. Perhaps this would be a useful addition to the the faqs S.Alexiou -- Here are some excerpts from /var/lib/mysql/'hostname'.err 010826 21:59:19 mysqld started /usr/sbin/mysqld: ready for connections 010827 14:05:37 mysqld started /usr/sbin/mysqld: ready for connections 010827 17:44:12 Aborted connection 171983 to db: 'CDR' user: 'spiros' host: `localhost' (Got an error reading communication packets) 010827 20:55:39 Aborted connection 513765 to db: 'CDR' user: 'spiros' host: `localhost' (Got an error reading communication packets) 010827 20:58:34 Aborted connection 521479 to db: 'CDR' user: 'spiros' host: `localhost' (Got an error reading communication packets) 010827 21:00:34 Aborted connection 524452 to db: 'CDR' user: 'spiros' host: `localhost' (Got an error reading communication packets) 010827 21:01:57 Aborted connection 529556 to db: 'CDR' user: 'spiros' host: `localhost' (Got an error reading communication packets) 010827 21:02:52 Aborted connection 533379 to db: 'CDR' user: 'spiros' host: `localhost' (Got an error reading communication packets) 010827 21:05:40 Aborted connection 540483 to db: 'CDR' user: 'spiros' host: `localhost' (Got an error reading communication packets) 010827 21:10:50 Aborted connection 554558 to db: 'CDR' user: 'spiros' host: `localhost' (Got an error reading communication packets) 010827 21:13:50 Aborted connection 556004 to db: 'CDR' user: 'spiros' host: `localhost' (Got an error reading communication packets) 010827 22:57:44 /usr/sbin/mysqld: Normal shutdown 010827 22:57:45 /usr/sbin/mysqld: Shutdown Complete 010830 18:17:47 mysqld started /usr/sbin/mysqld: ready for connections 010830 18:45:08 Aborted connection 41658 to db: 'CDR' user: 'spiros' host: `localhost' (Got an error reading communication packets) 010830 18:49:16 Aborted connection 50975 to db: 'CDR' user: 'spiros' host: `localhost' (Got an error reading communication packets) 010830 22:03:58 Aborted connection 528901 to db: 'CDR' user: 'spiros' host: `localhost' (Got an error reading communication packets) 010830 22:18:55 Aborted connection 565805 to db: 'CDR' user: 'spiros' host: `localhost' (Got an error reading communication packets) 010830 22:42:52 Warning: Found 11 of 17 rows when repairing './CDR/CDR_INCOMING_1360_1' 010830 22:56:39 Warning: Found 12 of 16 rows when repairing './CDR/CDR_INCOMING_1400_1' 010830 22:58:53 Warning: Found 7 of 12 rows when repairing './CDR/CDR_INCOMING_1412_0' 010830 23:00:26 Warning: Found 13 of 27 rows when repairing './CDR/CDR_INCOMING_1452_1' 010830 23:01:23 Warning: Found 5 of 18 rows when repairing './CDR/CDR_INCOMING_1491_1' 010830 23:02:28 Warning: Found 8 of 12 rows when repairing './CDR/CDR_INCOMING_1505_0' 010830 23:03:13 Warning: Found 1 of 6 rows when repairing './CDR/CDR_INCOMING_1522_1' 010830 23:09:00 Warning: Found 20 of 27 rows when repairing './CDR/CDR_INCOMING_1654_0' 010830 23:17:33 /usr/sbin/mysqld: Normal shutdown 010830 23:17:33 /usr/sbin/mysqld: Shutdown Some messages may also show up in /var/log/messages 29:36 quality4 PAM-unix2[1040]: session started for user root, service su change detected on device fd(2,0) Aug 25 21:17:07 quality4 kernel: scsi : aborting command due to timeout : pid 0, scsi0, channel 0, id 2, lun 0 Write (10) 00 03 92 cd 37 00 00 08 00 Aug 25 21:17:07 quality4 kernel: scsi : aborting command due to timeout : pid 0, scsi0, channel 0, id 2, lun 0 Write (10) 00 03 92 cd 3f 00 00 08 00 Aug 25 21:17:07 quality4 kernel: scsi : aborting command due to timeout : pid 0, scsi0, channel 0, id 2, lun 0 Write (10) 00 03 92 cd 47 00 00 08 00 Aug 25 21:17:07 quality4 kernel: scsi : aborting command due to timeout : pid 0, scsi0, channel 0, id 2, lun 0 Write (10) 00 03 92 cd 4f 00 00 08 00 Aug 25 21:17:07 quality4 kernel: scsi : aborting command due to timeout : pid 0, scsi0, channel 0, id 2, lun 0 Write (10) 00 03 92 cd 57 00 00 08 00 Aug 25 21:17:07 quality4 kernel: scsi : aborting command due to timeout : pid 0, scsi0, channel 0, id 2, lun 0 Write (10) 00 03 92 cd 5f 00 00 08 00 Aug 25 21:17:07 quality4 kernel: scsi
where option in mysqldump
Can anyone give an example of how to use the where option in mysqldump to get a dump of only those records whose values of field fieldx are larger than something? For example for all database tables of db mydatabase which possess a field named fieldx store in the dump only those records whose value of fieldx is larger than '123_xyx_' ? I tried --where= fieldx '123_xyx_ and got no complaints, but it returned all records. Thanks, S.Alexiou - 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
8 hr blocking read limit
Hi, two questions: 1)On the mysql manual, section 4.12.16 Mysql-Win32 compared to Unix MySQL it sas that: Mysql uses a blocking read for each connection. This means that: A connection will not be disconnected automatically after 8 hours, as happens with the Unix Version of Mysql. This is of interest, because I have an application running continuously(on Linux) and filling in a Mysql database. If the connection hangs up, this probably means that I will get corrupted tables among other things. I wonder if this is still true with recent Mysql editions and if there is a way to disable it on Unix. In more detail now: My application wants to transfer data stored in some sort of zipped(not normal zip/gzip) files on a remote server into tables on my machine. To this end the application opens bot a telnet and an ftp connection to the remote server, runs a decompression utility on the server, gets back the results as an ascii file, parses it and stores it in the appropriate database table. The list of files increases every 2 minutes and the files are cyclicly rewritten every 4 months or so. I am starting with some 3 months delay(that is there is already 3 months data in there), but the whle process is faster than the 2 minutes it takes for a new file to appear. I am worried about the limit because this is not just queries being run; there are open database connections and if disconnect occurs when the application is writing data to a table(and probably even if it does not ) the tables will be corrupted and mysqlisamchk is a pain because there are just too many tables in the database. Are my worries correct? Any suggestions? Thanks, S.Alexiou - 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
