RE: SELECT DISTINCT : I've found one trick !
Answer to my query select distinct http://lists.mysql.com/mysql/175839 http://lists.mysql.com/mysql/175839 To select properly only one time one element of a column in a list (here the journal name) I have to add a selection on the end of the name that it is not a space or a return ! Both are found to be necessary even if in my list no space or return are present in the journal names. It appears that SELECT is extended to the name , name\n and name\r ! SELECT DISTINCT journal FROM reference WHERE journal ' ' AND journal NOT LIKE '%\n' AND journal NOT LIKE '%\r ' ORDER BY journal Gives the required list with only one time each journal name; Thanks again for suggestions. Luc BARBIER _ Luc BARBIER DSM/DRECAM/SPCSI Bât 462 CEA Saclay 91191 Gif-sur-Yvette Cedex E-Mail : [mailto:[EMAIL PROTECTED] Tel : 33 (0)1 69 08 51 60 FAX : 33 (0)1 69 08 84 46
Re: Server Won't Start Next record offset is nonsensical
Cliff,
your OS or hardware has probably corrupted the ibdata file.
Next record offset is nonsensical 28769 in record at offset 7022
Before writing an index page to the file, InnoDB checks that offsets are
sensible ( 16 kB).
InnoDB: rec address 407b1b6e, first buffer frame 401c
InnoDB: buffer pool high end 409c, buf fix count 1
041116 17:17:10 InnoDB: Page dump in ascii and hex (16384 bytes):
len 16384; hex
large snip
;InnoDB: End of page dump
74 041116 17:17:10 InnoDB: Page checksum 3244520732,
prior-to-4.0.14-form checksum 1495873249
InnoDB: stored checksum 4145305205, prior-to-4.0.14-form stored checksum 0
Hmm... the 'old checksum' at offsets 16 kB - 8 ... 16 kB - 4 is zero. That
is probably file corruption.
InnoDB: Resetting space id's in the doublewrite buffer
if (mach_read_from_4(doublewrite +
TRX_SYS_DOUBLEWRITE_SPACE_ID_STORED)
!= TRX_SYS_DOUBLEWRITE_SPACE_ID_STORED_N) {
/* We are upgrading from a version 4.1.x to a version
where
multiple tablespaces are supported. We must reset the space
id
field in the pages in the doublewrite buffer because
starting
from this version the space id is stored to
FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID. */
trx_doublewrite_must_reset_space_ids = TRUE;
fprintf(stderr,
InnoDB: Resetting space id's in the doublewrite buffer\n);
} else {
trx_sys_multiple_tablespace_format = TRUE;
}
The printout looks like you tried a downgrade and upgrade of MySQL to
resolve the crash? Is that true? Which 4.1.x version you are running?
Please send the FULL .err log to me [EMAIL PROTECTED] for more
detailed analysis. Do not cut anything off.
Your lsn is only 300 MB. High-end users of InnoDB on a stable Linux computer
can easily reach 300 GB without any corruption.
Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php
Order MySQL technical support from https://order.mysql.com/
- Original Message -
From: Clif Smith [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Wednesday, November 17, 2004 4:53 AM
Subject: Server Won't Start Next record offset is nonsensical
Everything was fine...I haven't installed anything lately, etc. I've
got a Fedora FC1 system running MySQL v4. I noticed my db exports
failing this morning. The db wasn't running and now won't startup. I'm
googling but... Here's what's in the log:
41116 17:17:09 mysqld started
041116 17:17:09 [Warning] Asked for 196608 thread stack, but got 126976
041116 17:17:09 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Resetting space id's in the doublewrite buffer
041116 17:17:10 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 296311265.
041116 17:17:10 InnoDB: Starting an apply batch of log records to the
database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40
41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 InnoDB:
Next record offset is nonsensical 28769 in record at offset 7022
InnoDB: rec address 407b1b6e, first buffer frame 401c
InnoDB: buffer pool high end 409c, buf fix count 1
041116 17:17:10 InnoDB: Page dump in ascii and hex (16384 bytes):
len 16384; hex
large snip
;InnoDB: End of page dump
74 041116 17:17:10 InnoDB: Page checksum 3244520732,
prior-to-4.0.14-form checksum 1495873249
InnoDB: stored checksum 4145305205, prior-to-4.0.14-form stored checksum 0
InnoDB: Page lsn 0 296329762, low 4 bytes of lsn at page end 296329762
InnoDB: Page number (if stored to page already) 6570,
InnoDB: space id (if created with = MySQL-4.1.1 and stored already) 0
InnoDB: Page may be an index page where index id is 0 38
041116 17:17:10InnoDB: Assertion failure in thread 12292 in file
../include/page0page.ic line 494
InnoDB: Failing assertion: 0
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html
InnoDB: about forcing recovery.
75 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
Re: mysqld-nt error 23. Urgent pls
Hi! 4.0.14 on Windows prints a wrong (errno) explanation. You should look at http://dev.mysql.com/doc/mysql/en/Operating_System_error_codes.html 23 (ERROR_CRC) Data error (cyclic redundancy check). Your file (system) is corrupt, and Windows notices it. It is a hardware fault or a Windows bug. Try copy:ing ibdata1 to another file. Does it succeed? Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.com/ - Original Message - From: A Z [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, November 17, 2004 1:48 AM Subject: mysqld-nt error 23. Urgent pls MySQL 4.0.14 Need help please with the folowing: On starting mysqld-nt using mysqld-nt --console we the get the follwing output 041115 10:31:59 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 3136790960 InnoDB: Doing recovery: scanned up to log sequence number 3 3136791157 041115 10:32:00 InnoDB: Starting an apply batch of log records to the database .. InnoDB: Progress in percents: 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 4 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 5 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed 041115 10:32:00 InnoDB: Flushing modified pages from the buffer pool... 041115 10:32:02 InnoDB: Started 041115 10:32:09 InnoDB: Operating system error number 23 in a file operation. InnoDB: See http://www.innodb.com/ibman.html for installation help. InnoDB: Error number 23 means 'Too many open files in system'. InnoDB: See also section 13.2 at http://www.innodb.com/ibman.html InnoDB: about operating system error numbers. InnoDB: File name .\ibdata1 InnoDB: File operation call: 'Windows aio'. InnoDB: Cannot continue operation. regards ___ Win a castle for NYE with your mates and Yahoo! Messenger http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql taking 100% Mem utilisation
Naveen, the size of the mysqld process is only 1.7 GB, and you have plenty of free memory. Swap usage is zero. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Naveen C Joshi [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, November 17, 2004 5:10 AM Subject: Mysql taking 100% Mem utilisation --=_NextPart_000_1503_01C4CBC3.341C7D00 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Hi, I have installed MySQL-4.0.5-0 version on my RH Linux-7.1 box. I am = using the innodb database with tables (.frm, .MYD .MYI ). My system's = memory is around 3 GB ( 3798748K ). But I am facing problem of memory = utilization. The top output is as below ; Mem: 3798748K av, 2378268K used, 1420480K free, 0K shrd,7964K = buff Swap: 2096220K av, 0K used, 2096220K free 509892K = cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND 990 mysql 20 0 1756M 1.7G 2060 R21.0 47.3 135:53 mysqld 883 mysql 20 0 1756M 1.7G 2060 R20.9 47.3 136:15 mysqld 879 mysql 20 0 1756M 1.7G 2060 R20.6 47.3 135:49 mysqld 902 mysql 14 0 1756M 1.7G 2060 R20.3 47.3 136:18 mysqld 22803 root 17 0 1088 1088 840 R13.3 0.0 0:11 top 866 mysql 9 0 1756M 1.7G 2060 S 2.4 47.3 1:33 mysqld 897 mysql 9 0 1756M 1.7G 2060 S 1.7 47.3 1:32 mysqld 898 mysql 9 0 1756M 1.7G 2060 S 1.3 47.3 1:32 mysqld 877 mysql 9 0 1756M 1.7G 2060 S 0.7 47.3 0:35 mysqld 901 mysql 9 0 1756M 1.7G 2060 S 0.4 47.3 0:35 mysqld 900 mysql 9 0 1756M 1.7G 2060 S 0.2 47.3 0:36 mysqld 875 mysql 9 0 1756M 1.7G 2060 S 0.1 47.3 0:34 mysqld 1 root 8 0 544 544 472 S 0.0 0.0 0:04 init My configuration (my.cnf) file of mysql server is as below : [client] port=3D 3306 socket =3D /var/lib/mysql/mysql.sock [mysqld] datadir =3D /xxx/yyy basedir =3D / port=3D 3306 socket =3D /var/lib/mysql/mysql.sock skip-locking set-variable=3D key_buffer=3D768M set-variable=3D max_allowed_packet=3D1M set-variable=3D table_cache=3D512 set-variable=3D sort_buffer=3D9M set-variable=3D record_buffer=3D3M set-variable=3D thread_cache=3D8 set-variable=3D max_connections=3D150 set-variable=3D thread_concurrency=3D8 set-variable=3D myisam_sort_buffer_size=3D64M set-variable=3D thread_stack=3D256k log-bin server-id =3D 1 binlog-do-db=3D sms110 slave-skip-errors =3D all innodb_data_home_dir =3D /xxx/yyy/ innodb_data_file_path =3D ibdata1:2000M;ibdata2:10M:autoextend innodb_log_group_home_dir =3D /xxx/yyy/ innodb_log_arch_dir =3D /xxx/yyy/ set-variable =3D innodb_buffer_pool_size=3D1500M set-variable =3D innodb_additional_mem_pool_size=3D20M set-variable =3D innodb_log_file_size=3D400M set-variable =3D innodb_log_buffer_size=3D10M innodb_flush_log_at_trx_commit=3D1 set-variable =3D innodb_lock_wait_timeout=3D50 set-variable=3D innodb_file_io_threads=3D4 transaction-isolation =3D READ-COMMITTED innodb_thread_concurrency =3D 4 [mysqldump] quick set-variable=3D max_allowed_packet=3D16M [mysql] no-auto-rehash [isamchk] set-variable=3D key_buffer=3D256M set-variable=3D sort_buffer=3D256M set-variable=3D read_buffer=3D2M set-variable=3D write_buffer=3D2M [myisamchk] set-variable=3D key_buffer=3D256M set-variable=3D sort_buffer=3D256M set-variable=3D read_buffer=3D2M set-variable=3D write_buffer=3D2M [mysqlhotcopy] interactive-timeout Please advise me where I have to made changes in config file for the = best performence of mysql server. Regards Naveen =20 --=_NextPart_000_1503_01C4CBC3.341C7D00-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: Mysql-4.1.7 and client library - Client does not support authentication protocol requested by server; consider upgrading MySQL client
Yare sure that this resolves my problem? My problem is the client graphical (webmin or phpmyadmin), working from shell (SSH) is all to place. Thanks Alessio - Original Message - From: Dathan Vance Pattishall [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, November 16, 2004 9:34 PM Subject: RE: Mysql-4.1.7 and client library - Client does not support authentication protocol requested by server; consider upgrading MySQL client You need to reinstall your mysql api library to talk to mysql-4.1.7: 4.1.7 has a different auth implementation that is more secure then the 3.23 mysql protocol which your using. I suggest you link against the C-api that comes with 4.1.7. DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 16, 2004 11:38 AM To: [EMAIL PROTECTED] Subject: Mysql-4.1.7 and client library - Client does not support authentication protocol requested by server; consider upgrading MySQL client They are successful to install mysql-4.1.7 on o.s. solaris 8 thanks to the suggestions of Gleb Paharenko. Now when use webmin (last version 1.170) o phpmyadmin (last version -2.6.0-pl2) I have this problem: DBI connect failed : Client does not support authentication protocol requested by server; consider upgrading MySQL client From shell it works all the solution to the problem exists? Or it is better install the version 4.0.21? Thank's Alessio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql-4.1.7 Start - Ended error - Solaris 8
Alessio, the error means that the user running mysqld does not have the access rights to the MySQL 'datadir' (datadir is typically `/usr/local/mysql/data' for a binary installation). Use Unix commands man chown man chmod to get advice on how to set the owner and the access rights of a directory. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Gleb Paharenko [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, November 16, 2004 6:25 PM Subject: Re: Mysql-4.1.7 Start - Ended error - Solaris 8 Hello, Alessio. Check permissions on directories which should contain innodb data files. User mysql should have write permissions on that directories. You may use --user command line option to specify user you want. Also see: http://dev.mysql.com/doc/mysql/en/File_permissions.html http://dev.mysql.com/doc/mysql/en/Changing_MySQL_user.html http://dev.mysql.com/doc/mysql/en/Starting_server.html Sorry form my english, I am Italian. I'm from Ukraine, and as I know the phonations of our languages are very similar. Your language is one of the most melodious in the West Europe, and mine in the East :) They are successful to install mysql-4.1.7 on o.s. solaris 8 thanks to the suggestions of Gleb Paharenko. Now I have this problem: When start the command mysqld_safe mysql start and ended immediately. In the log file I find this error: 041115 20:43:42 mysqld started 041115 20:43:42 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'create'. InnoDB: Cannot continue operation. 041115 20:43:42 mysqld ended Help me, thanks [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems with simultaneous reads and writes to database
Hi, I work in an internet company that runs a web site with classifieds. We have got more than million page views daily and over a half a million classifieds with 2 weeks long life-cycle on an average. We use PHP and MySQL 4.0.18. We have got problems with simultaneous reads and writes to a database (DB). Now we use MyISAM tables but we are planing to change theme to InnoDB because of an InnoDB's locking on the row level feature. Unfortunately, InnoDB does not support fulltext search but we can overcome it by creating additional MyISAM tables for a fulltext purpose and migrating with rest of them to InnoDB. Furthermore, we would like to add a separate server for read-only (RO) DB to be used only with WWW application. In this manner we would never block main DB server with connections established by clients through WWW application. Obviously we will take care that all data updating queries would be send to main DB. One of the problems is how to synchronise data from the main DB to the RO one. We could use a MySQL replication but we are afraid that we won't gain much because during synchronisation our RO DB could get blocked anyway. Now, to synchronise data without locking RO DB we do something like: CREATE TABLE table_tmp LIKE table; INSERT INTO table_tmp SELECT * FROM table; DROP TABLE table_ro; ALTER TABLE table_tmp RENAME table_ro; table - working table on main DB table_ro - table on RO DB table_tmp - temporary table Real schema is a bit more complicated because the main DB and the RO are on two different servers so instead of 'INSERT INTO SELECT FROM' we fetch data from the main DB and insert it in the RO one in bunches of 100 records (much faster than separate inserts). Additionally we add staff like LOCK TABLES, SQL_BIG_RESULT or SQL_NO_CACHE. Unfortunately, in this manner for a split moment there is no table in RO DB. We have got few questions: - What do you think about migration to InnoDB? - What do you think about RO DB idea? - Do you successfully use MySQL replication in production environment? - How big is a delay in slave update? - What do you think about DROP TABLE/ALTER TABLE synchronisation? - Is there a different method to synchronise data between DBs that we could use? - Could upgrade of MySQL to 4.1.7 improve DB performance? Any kind of comments or suggestions are most welcome. Sincerely, Pawel Marzec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
IF() problem
select IF(BOOK1PL,BOOK1PL,BOOK1EN) from tBooksextra where ksi='id' - (content of BOOK1EN) select BOOK1PL from tBooksextra WHERE BOOK1PL IS NOT NULL AND BOOK1PL!='' AND ksi='id' - (content of BOOK1PL). Why didn't I get the content of BOOK1PL in 1st query? (checked in 4.0.21 and 4.1.7) ./ premax ./ [EMAIL PROTECTED] ./ koniec i bomba, a kto czytal ten traba. w.g. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql NTFS
MySQL 4.0.14 Are there any known issues with MySQL (above version) and NTFS file system with 120 GB of hard-disk and Win 2000 pro. We are facing a problem of mysqld-nt crashing intermittently, throwing error numbers 995, 32. any help is appreciated. regards ___ Win a castle for NYE with your mates and Yahoo! Messenger http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld-nt error 23. Urgent pls
Thanks, We tried copying the file but with no success. Running Norton DD reported errors with file descriptors. regards --- Heikki Tuuri [EMAIL PROTECTED] wrote: Hi! 4.0.14 on Windows prints a wrong (errno) explanation. You should look at http://dev.mysql.com/doc/mysql/en/Operating_System_error_codes.html 23 (ERROR_CRC) Data error (cyclic redundancy check). Your file (system) is corrupt, and Windows notices it. It is a hardware fault or a Windows bug. Try copy:ing ibdata1 to another file. Does it succeed? Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.com/ - Original Message - From: A Z [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, November 17, 2004 1:48 AM Subject: mysqld-nt error 23. Urgent pls MySQL 4.0.14 Need help please with the folowing: On starting mysqld-nt using mysqld-nt --console we the get the follwing output 041115 10:31:59 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 3136790960 InnoDB: Doing recovery: scanned up to log sequence number 3 3136791157 041115 10:32:00 InnoDB: Starting an apply batch of log records to the database .. InnoDB: Progress in percents: 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 4 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 5 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed 041115 10:32:00 InnoDB: Flushing modified pages from the buffer pool... 041115 10:32:02 InnoDB: Started 041115 10:32:09 InnoDB: Operating system error number 23 in a file operation. InnoDB: See http://www.innodb.com/ibman.html for installation help. InnoDB: Error number 23 means 'Too many open files in system'. InnoDB: See also section 13.2 at http://www.innodb.com/ibman.html InnoDB: about operating system error numbers. InnoDB: File name .\ibdata1 InnoDB: File operation call: 'Windows aio'. InnoDB: Cannot continue operation. regards ___ Win a castle for NYE with your mates and Yahoo! Messenger http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ___ Win a castle for NYE with your mates and Yahoo! Messenger http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server Won't Start Next record offset is nonsensical
Hello, Clif. Some times such problems were solved by increasing variables responsible for memory usage. Also if you dig deeper in lists archives you may find a lot of succesfull solutions on similar problems. What exact version of MySQL do you use? In old versions there were several bugs with innodb engine. Clif Smith [EMAIL PROTECTED] wrote: Everything was fine...I haven't installed anything lately, etc. I've got a Fedora FC1 system running MySQL v4. I noticed my db exports failing this morning. The db wasn't running and now won't startup. I'm googling but... Here's what's in the log: 41116 17:17:09 mysqld started 041116 17:17:09 [Warning] Asked for 196608 thread stack, but got 126976 041116 17:17:09 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Resetting space id's in the doublewrite buffer 041116 17:17:10 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 296311265. 041116 17:17:10 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 InnoDB: Next record offset is nonsensical 28769 in record at offset 7022 InnoDB: rec address 407b1b6e, first buffer frame 401c InnoDB: buffer pool high end 409c, buf fix count 1 041116 17:17:10 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex large snip ;InnoDB: End of page dump 74 041116 17:17:10 InnoDB: Page checksum 3244520732, prior-to-4.0.14-form checksum 1495873249 InnoDB: stored checksum 4145305205, prior-to-4.0.14-form stored checksum 0 InnoDB: Page lsn 0 296329762, low 4 bytes of lsn at page end 296329762 InnoDB: Page number (if stored to page already) 6570, InnoDB: space id (if created with = MySQL-4.1.1 and stored already) 0 InnoDB: Page may be an index page where index id is 0 38 041116 17:17:10InnoDB: Assertion failure in thread 12292 in file ../include/page0page.ic line 494 InnoDB: Failing assertion: 0 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html InnoDB: about forcing recovery. 75 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=0 read_buffer_size=131072 max_used_connections=0 max_connections=100 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 217599 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 76 thd=(nil) 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... Cannot determine thread, fp=0xbff3ecb8, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x808d737 0x82e17a8 0x825f3dd 0x825ee95 0x820d264 0x820e2c5 0x81f2751 0x8231a83 0x813ed39 0x82def5c 0x83088da New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/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 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. 77 78 041116 17:17:10 mysqld ended end of log I read the above and came up with the following: 0x808d737 handle_segfault + 423 0x82e17a8 pthread_sighandler + 184 0x825f3dd page_cur_insert_rec_low + 1261 0x825ee95 page_cur_parse_insert_rec + 3749 0x820d264 recv_parse_or_apply_log_rec_body + 68 0x820e2c5 recv_recover_page + 2933 0x81f2751 buf_page_io_complete + 593 0x8231a83 fil_aio_wait + 899 0x813ed39 io_handler_thread + 25 0x82def5c pthread_start_thread + 220 0x83088da thread_start + 4 But I'm just sad sys admin reading greek at this point...
Re: Error 1043 Bad handshake
Hello. Did you use mysql client program from 4.1.7 installation? When you use it from 4.1.0 or older (on another machine) there some differences in authentication handshake. [EMAIL PROTECTED] wrote: I have done some looking in the archive for this but can't seem to find anything recent that seems to apply. I have a brand new install of 4.1.7 on a Redhat 9 box. I compiled it with: CFLAGS=-O3 -mcpu=pentiumpro CXX=gcc CXXFLAGS=-O3 -mpentiumpro -felide-constructors -fno-exceptions -fno-rtti ./configure --prefix /usr/local/mysql --enable-assembler --with-mysqld-ldflags=-all-static --datadir /web/data It starts just fine. This is a new install and I haven't put any data in it so I don't think that I need to do any sort of permission fixing (Isn't that just for migrated data?) I can connect to it via local host just fine but if I try to connect from another machine running 4.1.? I get: bash-2.05$ mysql landfill -u root -p -h 192.168.1.100 Enter password: ERROR 1043: Bad handshake bash-2.05$ Any idea what I am doing wrong? --ja -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table # of rows changing?
Hello. I've found a good answer of Heikki Tuuri: InnoDB does not keep accurate row counts. They are only estimates based on 10 random dives into the clustered index tree. See: http://dev.mysql.com/doc/mysql/en/InnoDB_restrictions.html Jeff Burgoon [EMAIL PROTECTED] wrote: I have table with 83,065 rows. Each time I go to MySQL Administrator and look at the catalogs, the number of Rows reported by the administrator changes. I can keep clicking refresh and the number of rows fluctuates between roughly 81,000 and 86,000. I also see similar behavior when I access the table through PHPMyAdmin in browse mode. The crazy part is, this is a static table. I am 100% positive the table is not being inserted to or deleted from. Here is the create statement for my table. Can anybody offer any insight? CREATE TABLE MyTable (Category varchar(100), Sub_Category varchar(100), Part_Number varchar(40), Description varchar(100), Service_Category varchar(10), Price integer, INDEX(Part_Number(10))) type=InnoDB; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.2 is topping out at 1024 threads!
Hello. Similar problems are often found in lists. Usually they are solved by increasing file limits. You likely need to increase open-files-limit. Help! I can't figure out a way to stop my server from topping out at 1024 threads. This is a very strange behavoir. I have tons of legit use on my database server but I don't think the threads are dying does anyone have any suggestions for this? Thanks, Matt Babineau Web Developer Criticalcode - http://www.criticalcode.com Matt Babineau [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1.7 connections hang with earlier clients
Hi, we are running a MySQL 4.1.7 database on a Windows Server 2003 configured as server machine with transactional databases only and decision support. With this server we are experiencing troubles with old clients (MySQL Control Center 0.94 and ByteFX .NET Provider 0.76) when running a select statement with 2 sub-selects, converts and some joins. With this special statement, the connection thread nearly always hangs and is showing Sending data in the state field of the MySQL Administrator. Killing the thread with the Administrator is not working. Canceling the thread is only possible by killing mysql in the taskmanager and restart the service. The same statement executed over MyODBC 3.51.10 and Query Browser 1.1.1 is always working. I'm not sure if this is also related to crashes of mysql we are experiencing: The mysqld-nt version always crashed when some threads were hanging. Later I switched to mysqld-debug, which seems to be more stable with the hanging threads and only crashed once until now. I've tried to add the debug log, but I'm not able because of a mailinglist size-limit. Is this a known bug or should I submit this at bugs.mysql.com? Regards, Klaus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error 1043 Bad handshake
An answer to my own question. I have solved this problem which seems to be due
to the way mysql 4.1.7 stores it's passwords. I used the mysql function
OLD_PASSWORD to make mysql store it's passwords in the pre 4.1.x method.
Basically the syntax is:
mysql UPDATE mysql.user SET Password = OLD_PASSWORD('newpwd') WHERE Host =
'some_host' AND User = 'some_user';
mysql FLUSH PRIVILEGES;
Now I seem to be able to connect.
--ja
On Wed, 17 Nov 2004, Gleb Paharenko wrote:
Hello.
Did you use mysql client program from 4.1.7 installation? When you use
it from 4.1.0 or older (on another machine) there some differences in
authentication handshake.
[EMAIL PROTECTED] wrote:
I have done some looking in the archive for this but can't seem to find
anything recent that seems to apply.
I have a brand new install of 4.1.7 on a Redhat 9 box. I compiled it with:
CFLAGS=-O3 -mcpu=pentiumpro CXX=gcc CXXFLAGS=-O3 -mpentiumpro
-felide-constructors -fno-exceptions -fno-rtti ./configure --prefix
/usr/local/mysql --enable-assembler --with-mysqld-ldflags=-all-static
--datadir /web/data
It starts just fine. This is a new install and I haven't put any data in
it so I don't think that I need to do any sort of permission fixing (Isn't
that just for migrated data?) I can connect to it via local host just fine
but if I try to connect from another machine running 4.1.? I get:
bash-2.05$ mysql landfill -u root -p -h 192.168.1.100
Enter password:
ERROR 1043: Bad handshake
bash-2.05$
Any idea what I am doing wrong?
--ja
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.2 is topping out at 1024 threads!
In the last episode (Nov 17), Gleb Paharenko said: Help! I can't figure out a way to stop my server from topping out at 1024 threads. This is a very strange behavoir. I have tons of legit use on my database server but I don't think the threads are dying does anyone have any suggestions for this? Similar problems are often found in lists. Usually they are solved by increasing file limits. You likely need to increase open-files-limit. If you're running Linux, you may need to recompile your linuxthreads library also: http://dev.mysql.com/doc/mysql/en/Source_notes-Linux.html -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Limit error
I'm trying to paginate my record results. Running
into a sql syntax error that is boggling my limited
brain cells.
The print out of my statement as it is parsed:
This is before the error occurs, the first 15 records
return fine:
WHERE VendorJobs.Industry IN ('3') AND
VendorJobs.JobTitle LIKE '%%' AND
VendorJobs.LocationCity LIKE '%%' LIMIT 0, 15
I can't get a print, well maybe I can, but this is the
error that is coming back when I hit next:
Check the manual that corresponds to your MySQL server
version for the right syntax to use near 'LIMIT 15,
15' at line 6
I'll hold off on my statement for now if the error
isn't apparent.
Stuart
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re:[SOLVED] Limit error
Note to self, check variables for typos before posting to list! --- Stuart Felenstein [EMAIL PROTECTED] wrote: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error 1043 Bad handshake
Hello. A very comprehensive answer posted by Shawn Green you may read at: http://lists.mysql.com/mysql/173657 Aslo see: http://dev.mysql.com/doc/mysql/en/Programs_Known_to_Work_With_MyODBC.html May be you should really carefully read topics of documentation related to MyODBC. See: http://dev.mysql.com/doc/mysql/en/ODBC_Connector.html Any idea how that is going to effect odbc connections? My real application is to use coldfusion to connect to this database but I seem to be getting error messages there too. Will the change in authentication change the way odbc connections happen too? [EMAIL PROTECTED] wrote: I have done some looking in the archive for this but can't seem to find anything recent that seems to apply. I have a brand new install of 4.1.7 on a Redhat 9 box. I compiled it with: CFLAGS=-O3 -mcpu=pentiumpro CXX=gcc CXXFLAGS=-O3 -mpentiumpro -felide-constructors -fno-exceptions -fno-rtti ./configure --prefix /usr/local/mysql --enable-assembler --with-mysqld-ldflags=-all-static --datadir /web/data It starts just fine. This is a new install and I haven't put any data in it so I don't think that I need to do any sort of permission fixing (Isn't that just for migrated data?) I can connect to it via local host just fine but if I try to connect from another machine running 4.1.? I get: bash-2.05$ mysql landfill -u root -p -h 192.168.1.100 Enter password: ERROR 1043: Bad handshake bash-2.05$ Any idea what I am doing wrong? --ja -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql NTFS
Hello. Not enough info to make a conslusion. Send us your my.ini and a piece of error log file which corresponds to your problem. Do you use named pipes? Can server start if you use tcp instead? May be some antivirus concurently uses your files? Or another copy of MySQL is running. Can you check if problem remains if you upgrade to latest release? A Z [EMAIL PROTECTED] wrote: MySQL 4.0.14 Are there any known issues with MySQL (above version) and NTFS file system with 120 GB of hard-disk and Win 2000 pro. We are facing a problem of mysqld-nt crashing intermittently, throwing error numbers 995, 32. any help is appreciated. regards ___ Win a castle for NYE with your mates and Yahoo! Messenger http://uk.messenger.yahoo.com -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.7 connections hang with earlier clients
Hello. There were several bugs which cause server crashes on some queries. What queries did you use? On which tables? Send us yor my.ini files and output of show create table on tables used in your queries. Can you produce a test case for your problems? You may upload your error log to ftp://ftp.mysql.com/pub/mysql/upload/ Also there is a specific list for windows - [EMAIL PROTECTED] Klaus Pr?ckl [EMAIL PROTECTED] wrote: Hi, we are running a MySQL 4.1.7 database on a Windows Server 2003 = configured as server machine with transactional databases only and = decision support. With this server we are experiencing troubles with old clients (MySQL = Control Center 0.94 and ByteFX .NET Provider 0.76) when running a select = statement with 2 sub-selects, converts and some joins. With this special = statement, the connection thread nearly always hangs and is showing = Sending data in the state field of the MySQL Administrator. Killing = the thread with the Administrator is not working. Canceling the thread = is only possible by killing mysql in the taskmanager and restart the = service. The same statement executed over MyODBC 3.51.10 and Query Browser 1.1.1 = is always working. I'm not sure if this is also related to crashes of mysql we are = experiencing: The mysqld-nt version always crashed when some threads = were hanging. Later I switched to mysqld-debug, which seems to be more = stable with the hanging threads and only crashed once until now. I've tried to add the debug log, but I'm not able because of a = mailinglist size-limit.=20 Is this a known bug or should I submit this at bugs.mysql.com? Regards, Klaus -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.0.2 is topping out at 1024 threads!
This is a very strange problem. As you can see there isn't a lot going on, under a million queries. No problem right? This is a dual cpu 2.8 Ghz server. Ok Great. I am also including my.cnf so you can see my configuration. Here is some more info on the problem I am experiencing: mysql status -- mysql Ver 12.22 Distrib 4.0.20, for pc-linux (i686) Connection id: 25394 Current database: Current user: [EMAIL PROTECTED] SSL:Not in use Current pager: stdout Using outfile: '' Server version: 4.0.20-standard-log Protocol version: 10 Connection: 63.12.130.192 via TCP/IP Client characterset:latin1 Server characterset:latin1 TCP port: 3306 Uptime: 19 hours 40 min 2 sec Threads: 1023 Questions: 781971 Slow queries: 0 Opens: 33 Flush tables: 1 Open tables: 27 Queries per second avg: 11.044 -- mysql == # 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/my.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 key_buffer = 384M max_allowed_packet = 1M table_cache = 512 connect_timeout = 10 sort_buffer_size = 2M read_buffer_size = 2M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 4 max_connections = 1024 max_user_connections = 1024 # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the enable-named-pipe option) will render mysqld useless! # #skip-networking # Replication Master Server (default) # binary logging is required for replication log-bin # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 # Replication Slave (comment out master section to use this) # # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - #the syntax is: # #CHANGE MASTER TO MASTER_HOST=host, MASTER_PORT=port, #MASTER_USER=user, MASTER_PASSWORD=password ; # #where you replace host, user, password by quoted strings and #port by the master's port number (3306 by default). # #Example: # #CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, #MASTER_USER='joe', MASTER_PASSWORD='secret'; # # OR # # 2) Set the variables below. However, in case you choose this method, then #start replication for the first time (even unsuccessfully, for example #if you mistyped the password in master-password and the slave fails to #connect), the slave will create a master.info file, and any later #change in this file to the variables' values below will be ignored and #overridden by the content of the master.info file, unless you shutdown #the slave server, delete master.info and restart the slaver server. #For that reason, you may want to leave the lines below untouched #(commented) and instead use CHANGE MASTER TO (see above) # # required unique id between 2 and 2^32 - 1 # (and different from the master) # defaults to 2 if master-host is set # but will not function as a slave if omitted #server-id = 2 # # The replication master for this slave - required #master-host = hostname # # The username the slave will use for authentication when connecting # to the master - required #master-user = username # # The password the slave will authenticate with when connecting to # the master - required #master-password = password # # The port the master is listening on. # optional - defaults to 3306 #master-port = port # # binary logging - not required for slaves, but recommended #log-bin # Point the following paths to different dedicated disks #tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname # Uncomment the following if you are using BDB tables #bdb_cache_size = 384M
RE: MySQL 4.0.2 is topping out at 1024 threads!
threads or connections ? if the problem is about connection maybe change the parameter in your my.cnf : max_connections = 1024 to a higher value ... Hope this helps... Saludos / Regards, Alvaro. On Wed, 2004-11-17 at 11:18 -0800, Matt Babineau wrote: From: Matt Babineau [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: RE: MySQL 4.0.2 is topping out at 1024 threads! Date: Wed, 17 Nov 2004 11:18:04 -0800 (16:18 CLST) This is a very strange problem. As you can see there isn't a lot going on, under a million queries. No problem right? This is a dual cpu 2.8 Ghz server. Ok Great. I am also including my.cnf so you can see my configuration. Here is some more info on the problem I am experiencing: mysql status -- mysql Ver 12.22 Distrib 4.0.20, for pc-linux (i686) Connection id: 25394 Current database: Current user: [EMAIL PROTECTED] SSL:Not in use Current pager: stdout Using outfile: '' Server version: 4.0.20-standard-log Protocol version: 10 Connection: 63.12.130.192 via TCP/IP Client characterset:latin1 Server characterset:latin1 TCP port: 3306 Uptime: 19 hours 40 min 2 sec Threads: 1023 Questions: 781971 Slow queries: 0 Opens: 33 Flush tables: 1 Open tables: 27 Queries per second avg: 11.044 -- mysql == # 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/my.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 key_buffer = 384M max_allowed_packet = 1M table_cache = 512 connect_timeout = 10 sort_buffer_size = 2M read_buffer_size = 2M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 4 max_connections = 1024 max_user_connections = 1024 # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the enable-named-pipe option) will render mysqld useless! # #skip-networking # Replication Master Server (default) # binary logging is required for replication log-bin # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 # Replication Slave (comment out master section to use this) # # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - #the syntax is: # #CHANGE MASTER TO MASTER_HOST=host, MASTER_PORT=port, #MASTER_USER=user, MASTER_PASSWORD=password ; # #where you replace host, user, password by quoted strings and #port by the master's port number (3306 by default). # #Example: # #CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, #MASTER_USER='joe', MASTER_PASSWORD='secret'; # # OR # # 2) Set the variables below. However, in case you choose this method, then #start replication for the first time (even unsuccessfully, for example #if you mistyped the password in master-password and the slave fails to #connect), the slave will create a master.info file, and any later #change in this file to the variables' values below will be ignored and #overridden by the content of the master.info file, unless you shutdown #the slave server, delete master.info and restart the slaver server. #For that reason, you may want to leave the lines below untouched #(commented) and instead use CHANGE MASTER TO (see above) # # required unique id between 2 and 2^32 - 1 # (and different from the master) # defaults to 2 if master-host is set # but will not function as a slave if omitted #server-id = 2 # # The replication
Re: MySQL 4.0.2 is topping out at 1024 threads!
In the last episode (Nov 17), Matt Babineau said: This is a very strange problem. As you can see there isn't a lot going on, under a million queries. No problem right? This is a dual cpu 2.8 Ghz server. Ok Great. I am also including my.cnf so you can see my configuration. Here is some more info on the problem I am experiencing: mysql status -- mysql Ver 12.22 Distrib 4.0.20, for pc-linux (i686) Threads: 1023 Questions: 781971 Slow queries: 0 Opens: 33 Flush tables: 1 Open tables: 27 Queries per second avg: 11.044 -- So your real problem is not really the one you originally asked about? You now seem more interested in reducing the number of threads instead of raising the limit. Just run show processlist, see what's taking up all your connections, and fix the clients :) -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.0.2 is topping out at 1024 threads!
Ok, this is making a bit more sense now, I took a look at show processlist and this is what I found: | 25687 | inetusr | 62.13.102.133:48206 | protected | Sleep | 454 | | NULL The screen scrolls up with these sleeping connections, any way I can get these guys dumped if they've been sleeping too long? I already have a connection_timeout in the my.cnfis there another option? Matt Babineau Web Developer Criticalcode - http://www.criticalcode.com -Original Message- From: Alvaro Avello [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 17, 2004 11:28 AM To: Matt Babineau Cc: [EMAIL PROTECTED] Subject: RE: MySQL 4.0.2 is topping out at 1024 threads! threads or connections ? if the problem is about connection maybe change the parameter in your my.cnf : max_connections = 1024 to a higher value ... Hope this helps... Saludos / Regards, Alvaro. On Wed, 2004-11-17 at 11:18 -0800, Matt Babineau wrote: From: Matt Babineau [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: RE: MySQL 4.0.2 is topping out at 1024 threads! Date: Wed, 17 Nov 2004 11:18:04 -0800 (16:18 CLST) This is a very strange problem. As you can see there isn't a lot going on, under a million queries. No problem right? This is a dual cpu 2.8 Ghz server. Ok Great. I am also including my.cnf so you can see my configuration. Here is some more info on the problem I am experiencing: mysql status -- mysql Ver 12.22 Distrib 4.0.20, for pc-linux (i686) Connection id: 25394 Current database: Current user: [EMAIL PROTECTED] SSL:Not in use Current pager: stdout Using outfile: '' Server version: 4.0.20-standard-log Protocol version: 10 Connection: 63.12.130.192 via TCP/IP Client characterset:latin1 Server characterset:latin1 TCP port: 3306 Uptime: 19 hours 40 min 2 sec Threads: 1023 Questions: 781971 Slow queries: 0 Opens: 33 Flush tables: 1 Open tables: 27 Queries per second avg: 11.044 -- mysql == # 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/my.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 key_buffer = 384M max_allowed_packet = 1M table_cache = 512 connect_timeout = 10 sort_buffer_size = 2M read_buffer_size = 2M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 4 max_connections = 1024 max_user_connections = 1024 # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the enable-named-pipe option) will render mysqld useless! # #skip-networking # Replication Master Server (default) # binary logging is required for replication log-bin # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 # Replication Slave (comment out master section to use this) # # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - #the syntax is: # #CHANGE MASTER TO MASTER_HOST=host, MASTER_PORT=port, #MASTER_USER=user, MASTER_PASSWORD=password ; # #where you replace host, user, password by quoted strings and #port by the master's port number (3306 by default). # #Example: # #CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, #MASTER_USER='joe', MASTER_PASSWORD='secret'; # # OR # # 2) Set the variables below. However, in case you choose this method, then #start replication for the first time (even unsuccessfully, for example #if you mistyped the password in
question about showing db and/or table size
I know there must be a command line query to show the size of the database or of specific tables. What is it? I've been unable to find anything in the online manual (of course that presupposes I've been looking in the right place). Thanks, Susan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IF() problem
Date: Wed, 17 Nov 2004 12:50:47 +0100 To: [EMAIL PROTECTED] From: Przemyslaw Popielarski [EMAIL PROTECTED] Subject: IF() problem Message-ID: [EMAIL PROTECTED] select IF(BOOK1PL,BOOK1PL,BOOK1EN) from tBooksextra where ksi='id' - (content of BOOK1EN) select BOOK1PL from tBooksextra WHERE BOOK1PL IS NOT NULL AND BOOK1PL!='' AND ksi='id' - (content of BOOK1PL). Why didn't I get the content of BOOK1PL in 1st query? (checked in 4.0.21 and 4.1.7) Observe: mysql SELECT 'something' = 0, '' = 0; +-++ | 'something' = 0 | '' = 0 | +-++ | 1 | 1 | +-++ 1 row in set (0.02 sec) *Any* string value evaluates as 0 (FALSE), not just the empty string. You want If BOOK1PL is not empty, return BOOK1PL, otherwise return BOOK1EN, correct? Then try this instead: SELECT IF(BOOK1PL '', BOOK1PL, BOOK1EN) FROM tBooksextra WHERE ksi = 'id'; -- Jon Stephens, Technical Writer MySQL AB www.mysql.com Office: +61 (07) 3388 2228 Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Static library for MySQL C API
Hello, Is it possible to get static library for MySQL C API() instead of libmysql.dll? Regards, Karam __ Do you Yahoo!? Meet the all-new My Yahoo! - Try it today! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: question about showing db and/or table size
[snip] I know there must be a command line query to show the size of the database or of specific tables. What is it? I've been unable to find anything in the online manual (of course that presupposes I've been looking in the right place). [/snip] SHOW TABLE STATUS [FROM `table`] http://dev.mysql.com/doc/mysql/en/SHOW_TABLE_STATUS.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.0.2 is topping out at 1024 threads!
FIXED! Ok Thanks to Eric on this one, the wait_timeout configuration was what fixed my sleepy connection problems! Thanks ERIC! Matt Babineau Web Developer Criticalcode - http://www.criticalcode.com -Original Message- From: Eric Gunnett [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 17, 2004 11:37 AM To: [EMAIL PROTECTED] Subject: RE: MySQL 4.0.2 is topping out at 1024 threads! wait_timeout Will set the maximum amout of time a thread will be in the Sleep state before MySQL drops it. Eric Gunnett System Administrator Zoovy, Inc. [EMAIL PROTECTED] Matt Babineau [EMAIL PROTECTED] 11/17/04 11:35AM Ok, this is making a bit more sense now, I took a look at show processlist and this is what I found: | 25687 | inetusr | 62.13.102.133:48206 | protected | Sleep | 454 | | NULL The screen scrolls up with these sleeping connections, any way I can get these guys dumped if they've been sleeping too long? I already have a connection_timeout in the my.cnfis there another option? Matt Babineau Web Developer Criticalcode - http://www.criticalcode.com -Original Message- From: Alvaro Avello [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 17, 2004 11:28 AM To: Matt Babineau Cc: [EMAIL PROTECTED] Subject: RE: MySQL 4.0.2 is topping out at 1024 threads! threads or connections ? if the problem is about connection maybe change the parameter in your my.cnf : max_connections = 1024 to a higher value ... Hope this helps... Saludos / Regards, Alvaro. On Wed, 2004-11-17 at 11:18 -0800, Matt Babineau wrote: From: Matt Babineau [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: RE: MySQL 4.0.2 is topping out at 1024 threads! Date: Wed, 17 Nov 2004 11:18:04 -0800 (16:18 CLST) This is a very strange problem. As you can see there isn't a lot going on, under a million queries. No problem right? This is a dual cpu 2.8 Ghz server. Ok Great. I am also including my.cnf so you can see my configuration. Here is some more info on the problem I am experiencing: mysql status -- mysql Ver 12.22 Distrib 4.0.20, for pc-linux (i686) Connection id: 25394 Current database: Current user: [EMAIL PROTECTED] SSL:Not in use Current pager: stdout Using outfile: '' Server version: 4.0.20-standard-log Protocol version: 10 Connection: 63.12.130.192 via TCP/IP Client characterset:latin1 Server characterset:latin1 TCP port: 3306 Uptime: 19 hours 40 min 2 sec Threads: 1023 Questions: 781971 Slow queries: 0 Opens: 33 Flush tables: 1 Open tables: 27 Queries per second avg: 11.044 -- mysql == # 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/my.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 key_buffer = 384M max_allowed_packet = 1M table_cache = 512 connect_timeout = 10 sort_buffer_size = 2M read_buffer_size = 2M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 4 max_connections = 1024 max_user_connections = 1024 # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the enable-named-pipe option) will render mysqld useless! # #skip-networking # Replication Master Server (default) # binary logging is required for replication log-bin # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 # Replication Slave (comment out master section to use this) # # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully
RE: Trying to dump from GUI client
I am trying MySQL Query Browser, but I get this error... SELECT * INTO OUTFILE 'c:\temp\candidate.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '\n' FROM candidate; ERROR 1045: Access denied for user: 'wowdba'@'%' (Using password: YES) ...even though I am logged in via the client. -Original Message- From: Adam [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 16, 2004 7:04 PM To: [EMAIL PROTECTED]; MySQL General Subject: Re: Trying to dump from GUI client Eve, From the command line you can use `mysql`, command line tool that ships with MySQL. You would want to use the SELECT ... INTO OUTFILE 'file.txt' FROM ...; See the MySQL manual for more information on this: - http://dev.mysql.com/doc/mysql/en/SELECT.html You can use another MySQL client. Such as Toad for MySQL or MySQL query browser - see URLs below. Toad for MySQL - http://www.toadsoft.com/toadmysql/toad_mysql.htm MySQL Query Browser: - http://dev.mysql.com/downloads/query-browser/index.html Both of these tools will allow you to export a record set as a comma delimited file. Good luck! Regards, Adam Eve Atley I'm not sure how best to proceed in dumping data from 1 database and getting a copy of the export, in order to transfer it to another server. I usually use phpmyadmin to do an export, which nicely creates a .zip file of everything. I managed to get it connected with Mysql Control Center, but am not sure how to dump from this. So I figure I can: A. use a command line (in which case, what commnands should I use to dump and export to a file), Or B. try to get phpmyadmin to connect (as I'm uncertain how to edit the config file for this), Or C. learn how to dump from MySql CC (how? I saw no way of handling this from MySQL CC), Or D. use another GUI client (which one?). The server in question is mysql.loosefoot.com. Oddly, it was connecting fine until my company decided to move to a new server, and suddenly, it throws an error that Connection to database failed: Unknown MySQL Server Host 'mysql.loosefoot.com' (0). I've changed *nothing* in my connect script, and as mentioned, I can connect to the database via other means. What would you suggest as the least painful solution? Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Regards, Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Trying to dump from GUI client
I'm not sure what the answer here is but check your user's permissions on the database to make sure it has the necessary items GRANTED to it. Matt Babineau Web Developer Criticalcode - http://www.criticalcode.com -Original Message- From: Eve Atley [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 17, 2004 12:14 PM To: 'Adam'; 'MySQL General' Subject: RE: Trying to dump from GUI client I am trying MySQL Query Browser, but I get this error... SELECT * INTO OUTFILE 'c:\temp\candidate.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '\n' FROM candidate; ERROR 1045: Access denied for user: 'wowdba'@'%' (Using password: YES) ...even though I am logged in via the client. -Original Message- From: Adam [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 16, 2004 7:04 PM To: [EMAIL PROTECTED]; MySQL General Subject: Re: Trying to dump from GUI client Eve, From the command line you can use `mysql`, command line tool that ships with MySQL. You would want to use the SELECT ... INTO OUTFILE 'file.txt' FROM ...; See the MySQL manual for more information on this: - http://dev.mysql.com/doc/mysql/en/SELECT.html You can use another MySQL client. Such as Toad for MySQL or MySQL query browser - see URLs below. Toad for MySQL - http://www.toadsoft.com/toadmysql/toad_mysql.htm MySQL Query Browser: - http://dev.mysql.com/downloads/query-browser/index.html Both of these tools will allow you to export a record set as a comma delimited file. Good luck! Regards, Adam Eve Atley I'm not sure how best to proceed in dumping data from 1 database and getting a copy of the export, in order to transfer it to another server. I usually use phpmyadmin to do an export, which nicely creates a .zip file of everything. I managed to get it connected with Mysql Control Center, but am not sure how to dump from this. So I figure I can: A. use a command line (in which case, what commnands should I use to dump and export to a file), Or B. try to get phpmyadmin to connect (as I'm uncertain how to edit the config file for this), Or C. learn how to dump from MySql CC (how? I saw no way of handling this from MySQL CC), Or D. use another GUI client (which one?). The server in question is mysql.loosefoot.com. Oddly, it was connecting fine until my company decided to move to a new server, and suddenly, it throws an error that Connection to database failed: Unknown MySQL Server Host 'mysql.loosefoot.com' (0). I've changed *nothing* in my connect script, and as mentioned, I can connect to the database via other means. What would you suggest as the least painful solution? Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Regards, Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql 4.1 and the LIMIT sql statement
Hi all- Has anyone run into problems with this sql syntax? LIMIT -1 I've used this extensively in my code to get back all records rather then specifing a limit. I've done this programmatically with PHP, so all my queries have a limit even if I don't need one, I just have it specify LIMIT -1, but apparently this functionality doesn't seem to work in 4.1??? Thanks, Matt Babineau Web Developer Criticalcode - http://www.criticalcode.com
Re: Server Won't Start Next record offset is nonsensical
Cliff,
your OS or hardware has probably corrupted the ibdata file.
Ouch!
Next record offset is nonsensical 28769 in record at offset 7022
Before writing an index page to the file, InnoDB checks that offsets
are sensible ( 16 kB).
InnoDB: rec address 407b1b6e, first buffer frame 401c
InnoDB: buffer pool high end 409c, buf fix count 1
041116 17:17:10 InnoDB: Page dump in ascii and hex (16384 bytes):
len 16384; hex
large snip
;InnoDB: End of page dump
74 041116 17:17:10 InnoDB: Page checksum 3244520732,
prior-to-4.0.14-form checksum 1495873249
InnoDB: stored checksum 4145305205, prior-to-4.0.14-form stored
checksum 0
Hmm... the 'old checksum' at offsets 16 kB - 8 ... 16 kB - 4 is zero.
That is probably file corruption.
InnoDB: Resetting space id's in the doublewrite buffer
if (mach_read_from_4(doublewrite +
TRX_SYS_DOUBLEWRITE_SPACE_ID_STORED)
!= TRX_SYS_DOUBLEWRITE_SPACE_ID_STORED_N) {
/* We are upgrading from a version 4.1.x to a version
where
multiple tablespaces are supported. We must reset the
space id
field in the pages in the doublewrite buffer because
starting
from this version the space id is stored to
FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID. */
trx_doublewrite_must_reset_space_ids = TRUE;
fprintf(stderr,
InnoDB: Resetting space id's in the doublewrite buffer\n);
} else {
trx_sys_multiple_tablespace_format = TRUE;
}
The printout looks like you tried a downgrade and upgrade of MySQL to
resolve the crash? Is that true? Which 4.1.x version you are running?
Yes, initially I had thought that there was an issue with the
installation itself, so I upgraded to v4.1.7-0.
Please send the FULL .err log to me [EMAIL PROTECTED] for
more detailed analysis. Do not cut anything off.
Unfortunately the mailing list stripped your domain off. Please send an
email to spam at cjs226.com and I'll send you the err log.
Your lsn is only 300 MB. High-end users of InnoDB on a stable Linux
computer can easily reach 300 GB without any corruption.
Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up
MyISAM tables
http://www.innodb.com/order.php
Order MySQL technical support from https://order.mysql.com/
- Original Message - From: Clif Smith [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Wednesday, November 17, 2004 4:53 AM
Subject: Server Won't Start Next record offset is nonsensical
Everything was fine...I haven't installed anything lately, etc. I've
got a Fedora FC1 system running MySQL v4. I noticed my db exports
failing this morning. The db wasn't running and now won't startup. I'm
googling but... Here's what's in the log:
41116 17:17:09 mysqld started
041116 17:17:09 [Warning] Asked for 196608 thread stack, but got 126976
041116 17:17:09 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Resetting space id's in the doublewrite buffer
041116 17:17:10 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 296311265.
041116 17:17:10 InnoDB: Starting an apply batch of log records to the
database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40
41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 InnoDB:
Next record offset is nonsensical 28769 in record at offset 7022
InnoDB: rec address 407b1b6e, first buffer frame 401c
InnoDB: buffer pool high end 409c, buf fix count 1
041116 17:17:10 InnoDB: Page dump in ascii and hex (16384 bytes):
len 16384; hex
large snip
;InnoDB: End of page dump
74 041116 17:17:10 InnoDB: Page checksum 3244520732,
prior-to-4.0.14-form checksum 1495873249
InnoDB: stored checksum 4145305205, prior-to-4.0.14-form stored
checksum 0
InnoDB: Page lsn 0 296329762, low 4 bytes of lsn at page end 296329762
InnoDB: Page number (if stored to page already) 6570,
InnoDB: space id (if created with = MySQL-4.1.1 and stored already) 0
InnoDB: Page may be an index page where index id is 0 38
041116 17:17:10InnoDB: Assertion failure in thread 12292 in file
../include/page0page.ic line 494
InnoDB: Failing assertion: 0
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html
InnoDB: about forcing recovery.
75 mysqld got signal 11;
This could be because you hit a bug. It is also possible
Re: Mysql 4.1 and the LIMIT sql statement
In the last episode (Nov 17), Matt Babineau said: Has anyone run into problems with this sql syntax? LIMIT -1 I've used this extensively in my code to get back all records rather then specifing a limit. I've done this programmatically with PHP, so all my queries have a limit even if I don't need one, I just have it specify LIMIT -1, but apparently this functionality doesn't seem to work in 4.1??? From http://dev.mysql.com/doc/mysql/en/Upgrading-from-4.0.html : # LIMIT no longer accepts negative arguments. Use some large number (maximum 18446744073709551615) instead of -1. not sure why it was changed, though. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Trying to dump from GUI client
If I remember correctly, with select into outfile the outfile has to be on the server. -Original Message- From: Adam [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 16, 2004 4:04 PM To: [EMAIL PROTECTED]; MySQL General Subject: Re: Trying to dump from GUI client Eve, From the command line you can use `mysql`, command line tool that ships with MySQL. You would want to use the SELECT ... INTO OUTFILE 'file.txt' FROM ...; See the MySQL manual for more information on this: - http://dev.mysql.com/doc/mysql/en/SELECT.html You can use another MySQL client. Such as Toad for MySQL or MySQL query browser - see URLs below. Toad for MySQL - http://www.toadsoft.com/toadmysql/toad_mysql.htm MySQL Query Browser: - http://dev.mysql.com/downloads/query-browser/index.html Both of these tools will allow you to export a record set as a comma delimited file. Good luck! Regards, Adam Eve Atley I'm not sure how best to proceed in dumping data from 1 database and getting a copy of the export, in order to transfer it to another server. I usually use phpmyadmin to do an export, which nicely creates a .zip file of everything. I managed to get it connected with Mysql Control Center, but am not sure how to dump from this. So I figure I can: A. use a command line (in which case, what commnands should I use to dump and export to a file), Or B. try to get phpmyadmin to connect (as I'm uncertain how to edit the config file for this), Or C. learn how to dump from MySql CC (how? I saw no way of handling this from MySQL CC), Or D. use another GUI client (which one?). The server in question is mysql.loosefoot.com. Oddly, it was connecting fine until my company decided to move to a new server, and suddenly, it throws an error that Connection to database failed: Unknown MySQL Server Host 'mysql.loosefoot.com' (0). I've changed *nothing* in my connect script, and as mentioned, I can connect to the database via other means. What would you suggest as the least painful solution? Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Regards, Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql 4.1 and the LIMIT sql statement
I suppose they fixed it. Relying on 'undocumented features' is a bad habbit. Update your code to exclude the LIMIT clause if you do not wish to use it. Its the best thing ou can do. Mark Papadakis On Wed, 17 Nov 2004 12:21:31 -0800, Matt Babineau [EMAIL PROTECTED] wrote: Hi all- Has anyone run into problems with this sql syntax? LIMIT -1 I've used this extensively in my code to get back all records rather then specifing a limit. I've done this programmatically with PHP, so all my queries have a limit even if I don't need one, I just have it specify LIMIT -1, but apparently this functionality doesn't seem to work in 4.1??? Thanks, Matt Babineau Web Developer Criticalcode - http://www.criticalcode.com -- Mark Papadakis Head of RD Phaistos Networks, S.A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Behind Firewall
We are running MySQL 3.23.58-max-nt and are preparing to put the server it's running on behind a firewall. What ports do I have to leave open to the outside world so that outside customers can still access their databases, run queries, manage databases, etc? -- A. Clausen[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question on date calculation +
While I'm figuring this needs to be addressed via my scripting language wanted to ask here. Customers will buy a block of time to use my service. Time meaning number of days. 90, 45, 60, etc. Here are the fields relevant to this question: PostStart [Date] LenChoice [int] DaysLeft [int] When they register , the current date is input automagically into PostStart. LenChoice is chosen by the user and is the length of days they want this block. DaysLeft is where the calculation would be done to hold the difference between the current date, date posted and how many days were paid for. This is where I'm not entirely sure what to do. I'm probably inhaling gasoline or something but how would I get the field to the numbers of DaysLeft ? i.e. DaysLeft[today]= 3, DaysLeft[tomorrow]=2..etc Thank you , Stuart p.s. I'm on 4.0.22 , so no stored procedures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question on date calculation +
You will get your answers by reading carefully the Date Time Functions in the MySQL Reference Manual. Look at the URL: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html Bernard On Wednesday 17 November 2004 16:40, Stuart Felenstein wrote: While I'm figuring this needs to be addressed via my scripting language wanted to ask here. Customers will buy a block of time to use my service. Time meaning number of days. 90, 45, 60, etc. Here are the fields relevant to this question: PostStart [Date] LenChoice [int] DaysLeft [int] When they register , the current date is input automagically into PostStart. LenChoice is chosen by the user and is the length of days they want this block. DaysLeft is where the calculation would be done to hold the difference between the current date, date posted and how many days were paid for. This is where I'm not entirely sure what to do. I'm probably inhaling gasoline or something but how would I get the field to the numbers of DaysLeft ? i.e. DaysLeft[today]= 3, DaysLeft[tomorrow]=2..etc Thank you , Stuart p.s. I'm on 4.0.22 , so no stored procedures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question on date calculation +
--- Bernard Clement [EMAIL PROTECTED] wrote: You will get your answers by reading carefully the Date Time Functions in the MySQL Reference Manual. This will tell me how to automatically update the column in question ? Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Trying to dump from GUI client
Alright, using TOAD, I managed to get my data into CSV. When using PHPMYADMIN to load into another database, it tells me it can't read the file! -Original Message- From: Sergei Skarupo [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 17, 2004 4:00 PM To: Adam; [EMAIL PROTECTED]; MySQL General Subject: RE: Trying to dump from GUI client If I remember correctly, with select into outfile the outfile has to be on the server. -Original Message- From: Adam [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 16, 2004 4:04 PM To: [EMAIL PROTECTED]; MySQL General Subject: Re: Trying to dump from GUI client Eve, From the command line you can use `mysql`, command line tool that ships with MySQL. You would want to use the SELECT ... INTO OUTFILE 'file.txt' FROM ...; See the MySQL manual for more information on this: - http://dev.mysql.com/doc/mysql/en/SELECT.html You can use another MySQL client. Such as Toad for MySQL or MySQL query browser - see URLs below. Toad for MySQL - http://www.toadsoft.com/toadmysql/toad_mysql.htm MySQL Query Browser: - http://dev.mysql.com/downloads/query-browser/index.html Both of these tools will allow you to export a record set as a comma delimited file. Good luck! Regards, Adam Eve Atley I'm not sure how best to proceed in dumping data from 1 database and getting a copy of the export, in order to transfer it to another server. I usually use phpmyadmin to do an export, which nicely creates a .zip file of everything. I managed to get it connected with Mysql Control Center, but am not sure how to dump from this. So I figure I can: A. use a command line (in which case, what commnands should I use to dump and export to a file), Or B. try to get phpmyadmin to connect (as I'm uncertain how to edit the config file for this), Or C. learn how to dump from MySql CC (how? I saw no way of handling this from MySQL CC), Or D. use another GUI client (which one?). The server in question is mysql.loosefoot.com. Oddly, it was connecting fine until my company decided to move to a new server, and suddenly, it throws an error that Connection to database failed: Unknown MySQL Server Host 'mysql.loosefoot.com' (0). I've changed *nothing* in my connect script, and as mentioned, I can connect to the database via other means. What would you suggest as the least painful solution? Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Regards, Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error 1043 Bad handshake [In MySQL Administrator]
I have just installed MySQL Administrator on FreeBSD 5.2.1. I am trying to use the Connect to MySQL Server Instance
dialog to establish a connection to a version: 4.0.22 MySQL server. I can establish a remote connection using:
mysql -h hostname -p -u username databasename
However, using MySQL Administrator, I get the following error:
Could not connect to host 'hostname'.
MySQL Error Nr. 1043
Bad handshake
I have UPDATED the database password as suggested using the OLD_PASSWORD()
function. Still not able to establish a connection.
Any suggestions?
Thanks for your time,
cb
An answer to my own question. I have solved this problem which seems to be
due to the way
mysql 4.1.7 stores it's passwords. I used the mysql function OLD_PASSWORD to
make mysql
store it's passwords in the pre 4.1.x method. Basically the syntax is:
mysql UPDATE mysql.user SET Password = OLD_PASSWORD('newpwd') WHERE Host =
'some_host'
AND User = 'some_user';
mysql FLUSH PRIVILEGES;
Now I seem to be able to connect.
--ja
On Wed, 17 Nov 2004, Gleb Paharenko wrote:
Hello.
Did you use mysql client program from 4.1.7 installation? When you use
it from 4.1.0 or older (on another machine) there some differences in
authentication handshake.
jabbott@/stripped/ wrote:
I have done some looking in the archive for this but can't seem to find anything
recent that seems to apply.
I have a brand new install of 4.1.7 on a Redhat 9 box. I compiled it with:
CFLAGS=-O3 -mcpu=pentiumpro CXX=gcc CXXFLAGS=-O3 -mpentiumpro
-felide-constructors -fno-exceptions -fno-rtti ./configure --prefix /usr/local/mysql
--enable-assembler --with-mysqld-ldflags=-all-static --datadir /web/data
It starts just fine. This is a new install and I haven't put any data in it so
I don't think that I need to do any sort of permission fixing (Isn't that just for
migrated data?) I can connect to it via local host just fine but if I try to connect from
another machine running 4.1.? I get:
bash-2.05$ mysql landfill -u root -p -h 192.168.1.100
Enter password:
ERROR 1043: Bad handshake
bash-2.05$
Any idea what I am doing wrong?
--ja
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question on date calculation +
You can't automatically update it, that would require triggers which are not supported in mysql, you would need some sort of script that runs once a day and manually uses the functions described in the linke Bernard sent you to update the field. However I would recommend a different table structure, 2 fields: startDate, endDate Then nothing needs to be updated. When you check in your script as to whether the user has time left just select where endDate NOW(). You can compute the endDate easily when doing your insert as DATE_ADD(startDate, INTERVAL LenChoise DAYS), so you don't need to modify anything as far as how you present the choice to the user. On Wed, 2004-11-17 at 13:58 -0800, Stuart Felenstein wrote: --- Bernard Clement [EMAIL PROTECTED] wrote: You will get your answers by reading carefully the Date Time Functions in the MySQL Reference Manual. This will tell me how to automatically update the column in question ? Stuart -- John A. McCaskey Software Development Engineer Klir Technologies, Inc. [EMAIL PROTECTED] 206.902.2027 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Behind Firewall
3306 DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: A. Clausen [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 17, 2004 1:21 PM To: MySQL Mailing List Subject: MySQL Behind Firewall We are running MySQL 3.23.58-max-nt and are preparing to put the server it's running on behind a firewall. What ports do I have to leave open to the outside world so that outside customers can still access their databases, run queries, manage databases, etc? -- A. Clausen[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question on date calculation +
No, but it will tell you how to compute the field or, maybe, change your design! You definitely need an update statement, something like: update table name set DaysLeft=wathever your figure out from your reading. Now how to run this automatically? Well I do not know if you are using MS WIndows or Linux. For MS WIndows I cannot really help you! Probably using the at command. Under Linux you can use cron to automatically start mysql with your update statement. You can run it as often as you wish. The command might look like: mysql -uuser -ppassword -e update ... Bernard On Wednesday 17 November 2004 16:58, Stuart Felenstein wrote: --- Bernard Clement [EMAIL PROTECTED] wrote: You will get your answers by reading carefully the Date Time Functions in the MySQL Reference Manual. This will tell me how to automatically update the column in question ? Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
copy data only from one table to another table
How do I copy all data only from one table into another table? Both tables are in the same database. I have phpMyAdmin and it suppossedly does this, but it is not working, and there are no error messages. Thanks, -- Chip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Trying to dump from GUI client
I think for your purpose the best choice in the command line utility mysqldump, which comes with mysql. MySQL CC will also let you save the results as a TSV or CSV (this is set in the preferences somewhere). Use File - Save - Save Results from the menu. -Original Message- From: Eve Atley [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 17, 2004 2:13 PM To: Sergei Skarupo; 'Adam'; 'MySQL General' Subject: RE: Trying to dump from GUI client Alright, using TOAD, I managed to get my data into CSV. When using PHPMYADMIN to load into another database, it tells me it can't read the file! -Original Message- From: Sergei Skarupo [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 17, 2004 4:00 PM To: Adam; [EMAIL PROTECTED]; MySQL General Subject: RE: Trying to dump from GUI client If I remember correctly, with select into outfile the outfile has to be on the server. -Original Message- From: Adam [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 16, 2004 4:04 PM To: [EMAIL PROTECTED]; MySQL General Subject: Re: Trying to dump from GUI client Eve, From the command line you can use `mysql`, command line tool that ships with MySQL. You would want to use the SELECT ... INTO OUTFILE 'file.txt' FROM ...; See the MySQL manual for more information on this: - http://dev.mysql.com/doc/mysql/en/SELECT.html You can use another MySQL client. Such as Toad for MySQL or MySQL query browser - see URLs below. Toad for MySQL - http://www.toadsoft.com/toadmysql/toad_mysql.htm MySQL Query Browser: - http://dev.mysql.com/downloads/query-browser/index.html Both of these tools will allow you to export a record set as a comma delimited file. Good luck! Regards, Adam Eve Atley I'm not sure how best to proceed in dumping data from 1 database and getting a copy of the export, in order to transfer it to another server. I usually use phpmyadmin to do an export, which nicely creates a .zip file of everything. I managed to get it connected with Mysql Control Center, but am not sure how to dump from this. So I figure I can: A. use a command line (in which case, what commnands should I use to dump and export to a file), Or B. try to get phpmyadmin to connect (as I'm uncertain how to edit the config file for this), Or C. learn how to dump from MySql CC (how? I saw no way of handling this from MySQL CC), Or D. use another GUI client (which one?). The server in question is mysql.loosefoot.com. Oddly, it was connecting fine until my company decided to move to a new server, and suddenly, it throws an error that Connection to database failed: Unknown MySQL Server Host 'mysql.loosefoot.com' (0). I've changed *nothing* in my connect script, and as mentioned, I can connect to the database via other means. What would you suggest as the least painful solution? Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Regards, Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: copy data only from one table to another table
Chip Wiegand wrote: How do I copy all data only from one table into another table? Both tables are in the same database. I have phpMyAdmin and it suppossedly does this, but it is not working, and there are no error messages. Thanks, -- Chip create table table_2 select * from table_1 -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Set unique from Control Centre
Hi, Using MYSQL Control Centre 0.9.4 Beta, I would like to set a column to be a unique field. When I choose to edit the table, the Unique radio, along with Index and Fulltext is dulled out (deactivated). No amount of 'fiddling' has enabled me to select this feature. How may I select a column to be unique using the said version please? thanks Paul.
Re: copy data only from one table to another table
- Original Message - From: Daniel Kasak [EMAIL PROTECTED] To: Chip Wiegand [EMAIL PROTECTED]; MySQL List [EMAIL PROTECTED] Sent: Wednesday, November 17, 2004 5:04 PM Subject: Re: copy data only from one table to another table Chip Wiegand wrote: How do I copy all data only from one table into another table? Both tables are in the same database. I have phpMyAdmin and it suppossedly does this, but it is not working, and there are no error messages. Thanks, -- Chip create table table_2 select * from table_1 How would this be done if table_2 already exists? It has an auto_increment field as PK and I want to take all the rows from table_1 and dump them into table_2. The records being copied from table_1 can get new primary keys as there are no foreign key relationships to maintain. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question on date calculation +
Ok, you said you were using a scripting language, what is it, PHP?
If it is the case, I suggest you drop the last column 'DaysLeft' and make a
function in the scripting language of your choice that takes the 'PostStart' and
'LenChoice' as arguments:
in PHP it would write like this: (hope you understand)
function DaysLeft($start, $len){
return time()-($start+$len);
}
This function returns the Unix timestamp that is left for the user's usage. It
is in milliseconds if I'm right (see php.net for time()).
In my opinion, MySQL is not good for time calculations, you should better make
'PostStart' an INTEGER and put a Unix-Timestamp into it, same thing for
'LenChoice' you should put the time length in milliseconds in there as an INTEGER.
And your scripting language makes all the calculations, MySQL is used to store
important variables. Variables that can be calculated will take too much space
for a micro-nothing of work.
I work for security programming and it is common thing to take the initial logon
timestamp and the time elapsed since the last HTTP command. I prefer to deal
with time using my scripting language (PHP) instead of using MySQL functions.
Why, first because I've seen inconsitency in MySQL time calculations, second
because I can easily use the Unix-Timestamp INTEGER and use date() with it to
format the way it should be displayed. Also, it is easy to make a variable
OneDay=(1000*60*60*24), OneHour=(1000*60*60), OneMinute(1000*60), make
calculations (PostStart/OneDay)=DaysLeftAsFloat.
Hope this helps, but it would help to know what scripting language you are
using, as I say scripting languages are good for calculations where MySQL is
good for storing.
Simon
Stuart Felenstein wrote:
While I'm figuring this needs to be addressed via my
scripting language wanted to ask here.
Customers will buy a block of time to use my service.
Time meaning number of days. 90, 45, 60, etc.
Here are the fields relevant to this question:
PostStart [Date]
LenChoice [int]
DaysLeft [int]
When they register , the current date is input
automagically into PostStart. LenChoice is chosen by
the user and is the length of days they want this
block. DaysLeft is where the calculation would be
done to hold the difference between the current date,
date posted and how many days were paid for. This is
where I'm not entirely sure what to do. I'm probably
inhaling gasoline or something but how would I get the
field to the numbers of DaysLeft ?
i.e. DaysLeft[today]= 3, DaysLeft[tomorrow]=2..etc
Thank you ,
Stuart
p.s. I'm on 4.0.22 , so no stored procedures.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
want to post to mysql lists
want to post to mysql lists __ Do you Yahoo!? Meet the all-new My Yahoo! - Try it today! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
using IN()
I've a bunch of IDs fetched(around 60,000) from a DB. I'm using these IDs to fetch data from another DB having a related fields in its tables. I'm using IN clause for it. i.e. for e.g. SELECT * FROM site_users WHERE parentUserId IN (1,2,3,4) Again here parentUserId is Indexed. The query is running fine for now. I wanted to know how MySQL interprets and executes this query and can it have problems in future if number exceeds 60,000?? __ Do you Yahoo!? The all-new My Yahoo! - Get yours free! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
using IN() clause
I've a bunch of IDs fetched(around 60,000) from a DB. I'm using these IDs to fetch data from another DB having a related fields in its tables. I'm using IN clause for it. i.e. for e.g. SELECT * FROM site_users WHERE parentUserId IN (1,2,3,4) Again here parentUserId is Indexed. The query is running fine for now. I wanted to know how MySQL interprets and executes this query and can it have problems in future?? __ Do you Yahoo!? The all-new My Yahoo! - Get yours free! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IF() problem
A clarification. If a string value begins with a digit, MySQL does not
convert it to zero (FALSE), but rather to an integer made up of the
leading digits in the value (until a non-digit is reached). This can be
shown by using the CAST() function.
mysql SELECT '21b' = 0, CAST('21b' AS SIGNED);
+---+---+
| '21b' = 0 | CAST('21b' AS SIGNED) |
+---+---+
| 0 |21 |
+---+---+
1 row in set (0.00 sec)
However, if the string value begins with a non-digit, then it is coerced
or cast to zero:
mysql SELECT 'b21' = 0, CAST('b21' AS SIGNED);
+---+---+
| 'b21' = 0 | CAST('b21' AS SIGNED) |
+---+---+
| 1 | 0 |
+---+---+
1 row in set (0.00 sec)
It is still true that Przemyslaw was assuming that a non-empty string
would always evaluate as TRUE, and this isn't necessarily the case. So
the best strategy is to compare directly with the empty string, as
already discussed.
Thanks to Paul DuBois for reminding me of this. My apologies to anyone
whom I might have misled.
--
Jon Stephens, Technical Writer
MySQL AB www.mysql.com
Office: +61 (07) 3388 2228
Are you MySQL certified? www.mysql.com/certification
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
