re: Character sets
but can I set multiple default character set options to multiple character sets (EUC-JP, EUC-KR, and ISO-8859-1) rather then just one. No, just one in 4.0.x. You have to use 4.1.x for multiple character sets. _ Let the new MSN Premium Internet Software make the most of your high-speed experience. http://join.msn.com/?pgmarket=en-uspage=byoa/premST=1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
5.0.0-alpha: can't create column 'Found'
Description: When creating a table, mysql 5.0.0-alpha (binary package) does not accept column name 'Found'. How-To-Repeat: create table log (Found INT UNSIGNED NOT NULL); Fix: Other names of column work. Submitter-Id: submitter ID Originator:[EMAIL PROTECTED] Organization: MySQL support: none Synopsis: does not accept column name Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-5.0.0-alpha-standard (Official MySQL-standard binary) C compiler:2.95.3 C++ compiler: 2.95.3 Environment: i586-suse-linux-gnu System: Linux orion 2.4.20 #1 Tue Jan 6 17:28:30 CET 2004 i586 unknown unknown GNU/Linux Architecture: i586 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i486-suse-linux/3.3/specs Configured with: ../configure --enable-threads=posix --prefix=/usr --with-local-prefix=/usr/local --infodir=/usr/share/info --mandir=/usr/share/man --libdir=/usr/lib --enable-languages=c,c++,f77,objc,java,ada --disable-checking --enable-libgcj --with-gxx-include-dir=/usr/include/g++ --with-slibdir=/lib --with-system-zlib --enable-shared --enable-__cxa_atexit i486-suse-linux Thread model: posix gcc version 3.3 20030226 (prerelease) (SuSE Linux) Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=pentiumpro' CXX='gcc' CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors' LDFLAGS='' ASFLAGS='' LIBC: -rwxr-xr-x1 root root 1491599 Mar 14 2003 /lib/libc.so.6 -rw-r--r--1 root root 43012516 Mar 14 2003 /usr/lib/libc.a -rw-r--r--1 root root 204 Mar 14 2003 /usr/lib/libc.so lrwxrwxrwx1 root root 19 Jun 17 2003 /usr/lib/libc-client.so - libc-client.so.2002 -rwxr-xr-x1 root root 764016 Apr 11 2003 /usr/lib/libc-client.so.2002 Configure command: ./configure '--prefix=/usr/local/mysql' '--localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/mysql/bin' '--with-comment=Official MySQL-standard binary' '--with-extra-charsets=complex' '--with-server-suffix=-standard' '--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler' '--disable-shared' '--with-client-ldflags=-all-static' '--with-mysqld-ldflags=-all-static' '--with-embedded-server' '--with-innodb' 'CFLAGS=-O2 -mcpu=pentiumpro' 'CXXFLAGS=-O2 -mcpu=pentiumpro -felide-constructors' 'CXX=gcc' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Thread safe client
Hi, Is there any sample code available for MYSQL thread safe client using C API? Thanks in advance, Ram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 5.0.0-alpha: can't create column 'Found'
On Fri, 30 Jan 2004 [EMAIL PROTECTED] wrote: Description: When creating a table, mysql 5.0.0-alpha (binary package) does not accept column name 'Found'. How-To-Repeat: create table log (Found INT UNSIGNED NOT NULL); FOUND is a reserved word. Use within backticks, ie `Found` http://www.mysql.com/doc/en/Reserved_words.html cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Shrinking innodb datafiles?
Lawrence, if you use MySQL-4.1.1, and specify innodb_file_per_table in my.cnf, then InnoDB places each table into its own .ibd file. That is a way to free the disk space to the OS if you drop a table. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Lawrence Smith [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, January 30, 2004 12:59 AM Subject: Re: Shrinking innodb datafiles? --- Jeff Mathis [EMAIL PROTECTED] wrote: someone will no doubt echo what I'm about to say. InnoDB files are created at startup. the files use all the disk you allocate to them in your my.cnf startup file. If you want smaller InnoDB files, specify a smaller file size in your my.cnf file, but I have a feeling thats not what you want to do... I have the default my.cnf , which creates a 10MB file and IIRC default extension of 8MB (this is on a devel machine BTW). I have a couple of DBs with parsely populated tables, and loaded a test database with a lot of data which would account for the vast size of the file. I thought dropping the DB would free up space but this doesn't seem to be the case. I suppose the workaround would be to dump and recreate all databases. Thx = http://www.explanation-guide.info BT Yahoo! Broadband - Free modem offer, sign up online today and save 80 http://btyahoo.yahoo.co.uk -- 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: SELECT and DELETE
Cummings, Shawn (GNAPs) [EMAIL PROTECTED] wrote: I'd like to kill 2 birds with one stone... I'd like to SELECT a set of records (for viewing) and in the same motion DELETE any that show up. Can it be done? You can't do it with one query. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: InnoDB Backups
Mauro, - Original Message - From: Mauro Marcellino [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, January 29, 2004 10:44 PM Subject: InnoDB Backups --=_NextPart_000_00CE_01C3E67E.9D867B90 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable I have made a couple of other postings related to this but I guess my question is: Can I backup InnoDB tables (binary files) using an open file agent? what do you mean by an open file agent? You cannot just copy the ibdata files and ib_logfiles as is, because they would be inconsistent. A commercial tool to make an online (= hot) binary backup is InnoDB Hot Backup. A free way to make an online backup is to use mysqldump. If yes...and I am using Automatic COMMITs my backup will be current? If I am not using Automatic COMMITs then my backup will contain data = up to the last COMMIT (In other words, data since the last COMMIT will not be included in the backup. Is this true? I appreciate any guidance...Thanks Much! Mauro Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Performance problems
I have built a web site and I am testing it locally on my PC. Testing through Internet Explorer is awfully slow and most of the time I am getting error 'ASP 0113' script timed out. The table I am calling records from is quite text heavy (a few hundred to a 1,000+ words per field in some places). I have built a search facility too and in doing so I have added indexes to the table to try to make the search results appear quicker. I have tried selects within the MySQL command window and they are much quicker. For example I have selected all from the table in question and it returned every record (2,000 of them) in 8.9 seconds. Selecting specific records takes fractions of a second. The web pages are taking minutes before timing out. My system is 1ghz processor, 650ram, Windows ME running Personal Web Server and MySQL 4.0.15. Any advice on how I might be able to improve performance with what I have would be appreciated. TIA Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
OT: list management
re: MYSQL and PHP I just noticed that my spam/virus filter is trapping and discarding Cc:'s. If an email address is not in the To: line, you could also be missing some list traffic. Just a comment! thanks dander -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
conflict between SapDB 7.4 service instance and Trend ServerProtect
On a Windows Server (Win2000 or WinXP) that is running a database instance (or many database). If I run the antivirus from Trend Micro (Trend ServerProtect) all my database instance service are stopped. Do you have any idea ? already talk with TrendMicro? Thx, Stéphane -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sql prob
Hi all :) I have 1 relation A. Structure of A: NoPeople Date_entry Date_departure and key is (NoPeople + Date_entry) exemple of records: NoPeople Entry Departure P1 E1 D1 P1 E2 D2 P1 E3 NULL P2 E1 NULL P3 E1 D1 P4 E1 NULL P4 E2 NULL P4 E3 D2 E3E2E1 and D2D1. P1,P2,P3 and P4 are different persons. I would like to query all the people whose date_entry is their MAX date entry AND that have their date departure to NULL. In this exemple, P1 and P2 are ok. P4 isn't ok because max(Date_entry) is E3 and Date_departure - when Date_entry is E3 - isn't NULL. I tried some queries with 'group by' but i can't make it working. Any hint sql masters :) fab -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sql prob
Try this: CREATE TEMPORARY TABLE max_entry SELECT NoPeople, Max(Date_entry) AS Date_entry FROM table; SELECT NoPeople FROM table NATURAL JOIN max_entry WHERE Departure IS NULL; Hi all :) I have 1 relation A. Structure of A: NoPeople Date_entry Date_departure and key is (NoPeople + Date_entry) exemple of records: NoPeople Entry Departure P1 E1 D1 P1 E2 D2 P1 E3 NULL P2 E1 NULL P3 E1 D1 P4 E1 NULL P4 E2 NULL P4 E3 D2 E3E2E1 and D2D1. P1,P2,P3 and P4 are different persons. I would like to query all the people whose date_entry is their MAX date entry AND that have their date departure to NULL. In this exemple, P1 and P2 are ok. P4 isn't ok because max(Date_entry) is E3 and Date_departure - when Date_entry is E3 - isn't NULL. I tried some queries with 'group by' but i can't make it working. Any hint sql masters :) fab -- 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]
Key locks on InnoDB
Hi, got a problem with the key locks, using InnoDB: The mysql.com Manual reads: SELECT ... FROM ... LOCK IN SHARE MODE: sets shared next-key locks on all index records the read encounters. SELECT ... FROM ... FOR UPDATE: sets exclusive next-key locks on all index records the read encounters. The Innodb-Manual reads: SELECT ... FROM ... LOCK IN SHARE MODE sets shared locks on all index records the read encounters. SELECT ... FROM ... FOR UPDATE sets exclusive locks on all index records the read encounters. What locks are used for these statements?? shared locks or shared next-key-locks thanks for your help. -- +++ Mailpower für Multimedia-Begeisterte: http://www.gmx.net/topmail +++ 250 MB Mailbox, 1 GB Online-Festplatte, 100 FreeSMS. Jetzt kostenlos testen! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie call for HELP!
Hello I need to know how to get data base connectivity to a windows SQL2000 server from linux based app. Are there any tools that I need. When I try to connect I get this error DBMS MSS Microsoft SQL Server 6.x is not supported in your current installation I am sure that it is just client connectivity but the question is How in linux. -- Chadley Wilson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [Mysql-discussions] Newbie call for HELP!
did you notice that you are using MSSQL instead of MySQL. You are appearing to miss drivers or something however, perl has some possibilities to connect to MSSQL hosts, checkout www.cpan.org for that. Cheers -- Kind regards, Remko Lodder Elvandar.org/DSINet.org www.mostly-harmless.nl Dutch community for helping newcomers on the hackerscene -Oorspronkelijk bericht- Van: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Chadley Wilson Verzonden: vrijdag 30 januari 2004 13:28 Aan: [EMAIL PROTECTED] Onderwerp: [Mysql-discussions] Newbie call for HELP! Hello I need to know how to get data base connectivity to a windows SQL2000 server from linux based app. Are there any tools that I need. When I try to connect I get this error DBMS MSS Microsoft SQL Server 6.x is not supported in your current installation I am sure that it is just client connectivity but the question is How in linux. -- Chadley Wilson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ___ Mysql-discussions mailing list [EMAIL PROTECTED] http://lists.elvandar.org/mailman/listinfo/mysql-discussions -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
*Forcing* use of TCP/IP by clients for localhost
Greetings, This is a complicated one (at least to explain), but it never hurts to ask, right? While the problem lies mostly in PHP scripts, this also happens with the standalone MySQL client (v4.0.15 I believe). Normally the client (PHP and command-line) tries to use the local socket if the mysql_connect() call asks for localhost. This is normally a good thing. Today wasn't normal :) For reasons too complex for my tired brain to fully express right now, it became desirable for me to use an SSH tunnel to forward connections to port 3306 on localhost to a permanent database server on another rack (same network). If I used this syntax: mysql -u root -p databasename --host=database.server.com --port=3306 It worked just fine; connects over the tunnel and works as if it were localhost (some access restrictions popped up but those are easy to manage). Just asking for localhost, though, it complains that the socket isn't available and croaks. The same goes for PHP scripts. They get the finger from the library and it doesn't try over TCP/IP. Just gives up and dies. There's a few hundred scripts (most of which I don't have control over) that are using localhost and changing them to use 127.0.0.1 or database.server.com isn't feasible without organizing a *lot* of people to do the work. I'm curious if it's possible to instruct the client library (I assume PHP and the MySQL client both use the same underlying shared library) to either fall back to using TCP/IP (turning localhost into 127.0.0.1, for example) when the socket isn't there, or to tell it at the server level that it should be using TCP/IP instead of sockets entirely. It's either that or I get to start munging many many scripts. Any suggestions how to do this? Platform is Linux (x86), client and server both 4.0.15 or newer. Thanks for any suggestions, even if they're just get ready to start editing lots of scripts :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ORDER BY may remove result rows
Description: Instead of ordering rows, ORDER BY may remove rows How-To-Repeat: CREATE TABLE T ( Id INTEGER NOT NULL, Id2 INTEGER NOT NULL, Val FLOAT NOT NULL, Id3 INTEGER NOT NULL, KEY Key2 (Id2, Val), KEY Key3 (Id3) ); INSERT INTO T VALUES(1,3,-12,4); INSERT INTO T VALUES(2,2,-27.5,4); INSERT INTO T VALUES(3,2,18.4,3); mysql SELECT Id3 FROM T WHERE Id2=2 AND Val BETWEEN -30 AND 20; +-+ | Id3 | +-+ | 4 | | 3 | +-+ 2 rows in set (0.00 sec) mysql SELECT Id3 FROM T WHERE Id2=2 AND Val BETWEEN -30 AND 20 ORDER BY Id3; +-+ | Id3 | +-+ | 3 | +-+ 1 row in set (0.01 sec) Note: both indices Key2 and Key3 must be present for the bug to occur. Fix: Submitter-Id: submitter ID Originator:Lukas Knecht Organization: EraGen Biosciences Inc. MySQL support: licence Synopsis: ORDER BY may remove result rows Severity: critical Priority: high Category: mysql Class: sw-bug Release: mysql-4.1.1-alpha-max (Official MySQL-max binary) C compiler:2.95.3 C++ compiler: 2.95.3 Environment: System: Linux barks 2.4.18-4GB #1 Wed Dec 17 18:08:38 UTC 2003 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i486-suse-linux/2.95.3/specs gcc version 2.95.3 20010315 (SuSE) Compilation info: CC='gcc' CFLAGS='-O2 -mpentiumpro -DBIG_TABLES' CXX='gcc' CXXFLAGS='-O2 -mpentiumpro -felide-constructors -DBIG_TABLES' LDFLAGS='' ASFLAGS='' LIBC: -rwxr-xr-x1 root root 1394302 Mar 27 2003 /lib/libc.so.6 -rw-r--r--1 root root 25362104 Mar 27 2003 /usr/lib/libc.a -rw-r--r--1 root root 178 Mar 23 2002 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr/local/mysql' '--localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/mysql/bin' '--with-comment=Official MySQL-max binary' '--with-extra-charsets=complex' '--with-server-suffix=-max' '--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler' '--disable-shared' '--with-berkeley-db' '--with-raid' '--with-readline' '--with-embedded-server' '--with-innodb' 'CFLAGS=-O2 -mpentiumpro -DBIG_TABLES' 'CXXFLAGS=-O2 -mpentiumpro -felide-constructors -DBIG_TABLES' 'CXX=gcc' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Key locks on InnoDB
Hi! It depends on the isolation level whether locking reads set next-key locks (= lock the record AND the gap before it), or only lock the record itself. http://www.innodb.com/ibman.php#Set.transaction: READ UNCOMMITTED This is also called dirty read: non-locking SELECTs are performed so that we do not look at a possible earlier version of a record; thus they are not consistent reads under this isolation level; otherwise this level works like READ COMMITTED. READ COMMITTED Somewhat Oracle-like isolation level. All SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE statements only lock the index records, not the gaps before them, and thus allow free inserting of new records next to locked records. UPDATE and DELETE which use a unique index with a unique search condition, only lock the index record found, not the gap before it. But still in range type UPDATE and DELETE InnoDB must set next-key or gap locks and block insertions by other users to the gaps covered by the range. This is necessary since phantom rows have to be blocked for MySQL replication and recovery to work. Consistent reads behave like in Oracle: each consistent read, even within the same transaction, sets and reads its own fresh snapshot. REPEATABLE READ This is the default isolation level of InnoDB. SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE, and DELETE, which use a unique index with a unique search condition, only lock the index record found, not the gap before it. Otherwise these operations employ next-key locking, locking the index range scanned with next-key or gap locks, and block new insertions by other users. In consistent reads there is an important difference from the previous isolation level: in this level all consistent reads within the same transaction read the same snapshot established by the first read. This convention means that if you issue several plain SELECTs within the same transaction, these SELECTs are consistent also with respect to each other. SERIALIZABLE This level is like the previous one, but all plain SELECTs are implicitly converted to SELECT ... LOCK IN SHARE MODE. http://www.innodb.com/ibman.php#Locks.set.by.statements: SELECT ... FROM ... LOCK IN SHARE MODE sets shared locks on all index records the read encounters. SELECT ... FROM ... FOR UPDATE sets exclusive locks on all index records the read encounters. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL support from http://www.mysql.com/support/index.html List:MySQL General Discussion« Previous MessageNext Message » From:Ingolf HenriciDate:January 30 2004 12:52pm Subject:Key locks on InnoDB Hi, got a problem with the key locks, using InnoDB: The mysql.com Manual reads: SELECT ... FROM ... LOCK IN SHARE MODE: sets shared next-key locks on all index records the read encounters. SELECT ... FROM ... FOR UPDATE: sets exclusive next-key locks on all index records the read encounters. The Innodb-Manual reads: SELECT ... FROM ... LOCK IN SHARE MODE sets shared locks on all index records the read encounters. SELECT ... FROM ... FOR UPDATE sets exclusive locks on all index records the read encounters. What locks are used for these statements?? shared locks or shared next-key-locks thanks for your help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Preparing new lib files
OK lesson learned, why .a vs .lib? Strange and scary notion.
I am still ending up with a segmentation fault once I use them as is. Same
is happening in windows side with code:
#include stdio.h
#include mysql.h
int main()
{
/* declasre structures and variables.*/
MYSQL mysql;
MYSQL_RES *result;
MYSQL_ROW row;
/*initi MYSQL Structure
mysql_inti(mysql);
/* connect */
mysql_real_connect(mysql, localhost, leif, , bmass, 0, NULL, 0);
/* exec query */
mysql_query(mysql, select * from codes);
/* get result set */
result= mysql_store_result(mysql);
/* process results */
while((row= mysql_fetch_row(result)))
{
fprintf(stdout, %d - %d - %s\n, row[0], row[1], row[2]);
}
/* clean up */
mysql_free_result(result);
mysql_close(mysql);
}
and stack trace (from win2000 server)
Exception: STATUS_ACCESS_VIOLATION at eip=1002DFB1
eax=0022FB98 ebx=0001 ecx=10251EF4 edx=2D312D53 esi=0022FB98
edi=
ebp=0022FDC0 esp=0022F8BC program=C:\cygwin\usr\src\sample.exe
cs=001B ds=0023 es=0023 fs=0038 gs= ss=0023
Stack trace:
Frame Function Args
0022FDC0 1002DFB1 (0001, 10250818, 10250330, 61062452)
0022FF40 61005018 (610CFEE0, FFFE, 003C, 610CFE04)
0022FF90 610052ED (, , 8043138F, )
0022FFB0 00401531 (00401089, 037F0009, 0022FFF0, 7C5987E7)
0022FFC0 0040103C (, 77F8A900, 7FFDF000, )
0022FFF0 7C5987E7 (00401000, , 00C8, 0100)
End of stack trace
- Original Message -
From: Dan Nelson [EMAIL PROTECTED]
To: Leif Johnston [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Thursday, January 29, 2004 10:15 PM
Subject: Re: Preparing new lib files
In the last episode (Jan 29), Leif Johnston said:
I have been having trouble under ming, cygwin and Linux all for
different reasons and all because some of my libs don't address
everything. I downloaded the linux distribution and found all the lib
files stored as .a files. How do I turn them into libs and can I
use the same libs on windows cygwin and linux?
.a files are static libraries. Each OS requires its own libraries,
since they may have different types, structure packing rules and
functions.
--
Dan Nelson
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query time out during login over network
I have recently installed suse 9.0 linux which has MySQL 4.0.15 as part of the distribution. After I set up my users I was able to use mysqldump and pipe it into mysql on my windows machine running 4.0.16 to dump a database to the suse box. However, now when I try to login to the linux box from another machine on the network I get a query timed out error immediately after I enter the command line to start the mysql client. mysql -h 192.168.1.2 -u cdw -pxx ERROR 2013: Lost connection to MySQL server during query The error happens with in a small fraction of a second after I type in the command. I can login from the linux machine just fine though. Chris W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: *Forcing* use of TCP/IP by clients for localhost
i've never set up tunnels and such, but are you sure that your ssh tunnel is also listening on localhost (i.e. 127.0.0.1)? i once had a problem like that with my apache, and it turned out that it only listened on 'real' IPs cheers and good luck, M. [EMAIL PROTECTED] wrote: Greetings, This is a complicated one (at least to explain), but it never hurts to ask, right? While the problem lies mostly in PHP scripts, this also happens with the standalone MySQL client (v4.0.15 I believe). Normally the client (PHP and command-line) tries to use the local socket if the mysql_connect() call asks for localhost. This is normally a good thing. Today wasn't normal :) For reasons too complex for my tired brain to fully express right now, it became desirable for me to use an SSH tunnel to forward connections to port 3306 on localhost to a permanent database server on another rack (same network). If I used this syntax: mysql -u root -p databasename --host=database.server.com --port=3306 It worked just fine; connects over the tunnel and works as if it were localhost (some access restrictions popped up but those are easy to manage). Just asking for localhost, though, it complains that the socket isn't available and croaks. The same goes for PHP scripts. They get the finger from the library and it doesn't try over TCP/IP. Just gives up and dies. There's a few hundred scripts (most of which I don't have control over) that are using localhost and changing them to use 127.0.0.1 or database.server.com isn't feasible without organizing a *lot* of people to do the work. I'm curious if it's possible to instruct the client library (I assume PHP and the MySQL client both use the same underlying shared library) to either fall back to using TCP/IP (turning localhost into 127.0.0.1, for example) when the socket isn't there, or to tell it at the server level that it should be using TCP/IP instead of sockets entirely. It's either that or I get to start munging many many scripts. Any suggestions how to do this? Platform is Linux (x86), client and server both 4.0.15 or newer. Thanks for any suggestions, even if they're just get ready to start editing lots of scripts :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL and productivity
Hello , I must create a temp table with 5 or more million records. I need it for creating some reports, so I need to create indexes too. My question is: When total time for work will be smaller? : 1-st case: I create table and create indexes immediately before filling data. 2-nd case: I create table, filling data and then create indexes? -- Best regards, Krasimir_Slaveykov mailto: [EMAIL PROTECTED] [EMAIL PROTECTED] |-| |/ * * *** * ** /| | *** *** *** *** *** *** ** *** //| |/// *** *** * * *** **** *** ///| |// *** *** *** *** *** *** ** ****** | |/ *** *** *** * *** /| |--- www.office1.bg --| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Convert MS Access to MYSQL
There was some discussion in December of converting MS Access databases to Mysql and the DBManager program was recommended. I have DBManager running and select Tools=Data Management=Import/Export Wizards, but the command is greyed out and won't activate. I am using Office 2000 and I have made sure that Preferences=Data=Use DAO 3.6 is checked as instructed. What am I doing wrong? Ken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL and productivity
I'd start with the indexes in place. 5+mil records will take quite some time to index after the fact. P -Krasimir_Slaveykov [EMAIL PROTECTED] wrote: - To: [EMAIL PROTECTED] From: Krasimir_Slaveykov [EMAIL PROTECTED] Date: 01/30/2004 09:14AM Subject: SQL and productivity Hello , I must create a temp table with 5 or more million records. I need it for creating some reports, so I need to create indexes too. My question is: When total time for work will be smaller? : 1-st case: I create table and create indexes immediately before filling data. 2-nd case: I create table, filling data and then create indexes? -- Best regards, Krasimir_Slaveykov mailto: [EMAIL PROTECTED] [EMAIL PROTECTED] |-| |/ * * *** * ** /| | *** *** *** *** *** *** ** *** //| |/// *** *** * * *** *** * *** ///| |// *** *** *** *** *** *** ** *** *** | |/ *** *** *** * *** /| |--- www.office1.bg --| -- 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: SQL and productivity
When total time for work will be smaller? : 1-st case: I create table and create indexes immediately before filling data. 2-nd case: I create table, filling data and then create indexes? Filling the data data first and creating indexes after is faster, so you should definitly go with nr 2. mack / -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL and productivity
From what I've read ( I think in the MySQL docs, might have been here in the list), technically it will take less time to add the indexes after the table creation, than the overhead of index updating per-insert. Either way, it's gonna take a loong time. -Original Message- From: Peter J Milanese [mailto:[EMAIL PROTECTED] Sent: Friday, January 30, 2004 9:15 AM To: Krasimir_Slaveykov Cc: [EMAIL PROTECTED] Subject: Re: SQL and productivity I'd start with the indexes in place. 5+mil records will take quite some time to index after the fact. P -Krasimir_Slaveykov [EMAIL PROTECTED] wrote: - To: [EMAIL PROTECTED] From: Krasimir_Slaveykov [EMAIL PROTECTED] Date: 01/30/2004 09:14AM Subject: SQL and productivity Hello , I must create a temp table with 5 or more million records. I need it for creating some reports, so I need to create indexes too. My question is: When total time for work will be smaller? : 1-st case: I create table and create indexes immediately before filling data. 2-nd case: I create table, filling data and then create indexes? -- Best regards, Krasimir_Slaveykov mailto: [EMAIL PROTECTED] [EMAIL PROTECTED] |-| |/ * * *** * ** /| | *** *** *** *** *** *** ** *** //| |/// *** *** * * *** *** * *** ///| |// *** *** *** *** *** *** ** *** *** | |/ *** *** *** * *** /| |--- www.office1.bg --| -- 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]
Re: *Forcing* use of TCP/IP by clients for localhost
--- [EMAIL PROTECTED] wrote --- i've never set up tunnels and such, but are you sure that your ssh tunnel is also listening on localhost (i.e. 127.0.0.1)? i once had a problem like that with my apache, and it turned out that it only listened on 'real' IPs Yeah, the tunnels work perfectly if I do this: mysql -u root -p -P 3306 -h 127.0.0.1 database_name Blazing fast (same subnet), almost as fast as a local connection. The server load on the client end dropped like a rock too when I stopped the local MySQL copy (the tunnel to the remote MySQL is what I want to use :). It's just that the scripts and client aren't poking 127.0.0.1 via TCP/IP -- they check sockets and die when the socket isn't there. cheers and good luck, Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlclient.lib and missing references
Inside of cygwin I ran gcc sample.c -o sample.exe -I/usr/include/mysql/ -L/lib/mysql -lmysqlclient -lmySQL -lz -lm I confirmed libmysql.dll is in mysql/bin with everyone access full control against these files, also that username and password to db are right, but I am guessing there is another file with bad permissions. mysql\bin is in path. Exception: STATUS_ACCESS_VIOLATION at eip=10006C12 eax= ebx= ecx= edx=0002 esi=0022FCC8 edi=0022FEC8 ebp=0022FCC8 esp=0022F180 program=C:\cygwin\usr\src\sample.exe cs=001B ds=0023 es=0023 fs=003B gs= ss=0023 Stack trace: Frame Function Args 0022FCC8 10006C12 (0022FCD8, 6105E8AB, , ) End of stack trace - Original Message - From: Fabrice Marchal [EMAIL PROTECTED] To: Leif Johnston [EMAIL PROTECTED] Sent: Friday, January 30, 2004 9:04 AM Subject: Re: mysqlclient.lib and missing references Hello Leif, I think you shoud also link the wrapper library : -lmySQL Let me know if it works. I would like to hear from you also if your sample.exe works because I have encountered problems with the newest mysql/cygwin/g++ Fabrice Leif Johnston wrote: I am trying to compile a straightforward sample from the complete MYSQL reference on windows, under cygwin and mysql 4.0.16. I have copied the winfiles into my cygwin space /usr/src and am executing the following with the following results. With mysqlclient, I am getting no useful compile with mySQL.lb I am getting close but am missing the _mysql_free_results. Is there another lib I must get/use/? Thanks [EMAIL PROTECTED] /usr/src $ gcc sample.c -o sample.exe -I/usr/include/mysql -L/lib/mysql -l mysqlserver -lz /cygdrive/c/DOCUME~1/LJOHNS~1/LOCALS~1/Temp/ccUIjujX.o(.text+0xa2):sample.c : undefined reference to `_mysql_real_connect' /cygdrive/c/DOCUME~1/LJOHNS~1/LOCALS~1/Temp/ccUIjujX.o(.text+0xb8):sample.c : undefined reference to `_mysql_query' /cygdrive/c/DOCUME~1/LJOHNS~1/LOCALS~1/Temp/ccUIjujX.o(.text+0xc6):sample.c : undefined reference to `_mysql_store_result' /cygdrive/c/DOCUME~1/LJOHNS~1/LOCALS~1/Temp/ccUIjujX.o(.text+0xda):sample.c : undefined reference to `_mysql_fetch_row' /cygdrive/c/DOCUME~1/LJOHNS~1/LOCALS~1/Temp/ccUIjujX.o(.text+0x13f):sample. c: undefined reference to `_mysql_free_results' /cygdrive/c/DOCUME~1/LJOHNS~1/LOCALS~1/Temp/ccUIjujX.o(.text+0x14d):sample. c: undefined reference to `_mysql_close' collect2: ld returned 1 exit status [EMAIL PROTECTED] /usr/src $ gcc sample.c -o sample.exe -I/usr/include/mysql -L/lib/mysql/lib/opt -l mySQL -lz /cygdrive/c/DOCUME~1/LJOHNS~1/LOCALS~1/Temp/ccG7fUqP.o(.text+0x13f):sample. c: undefined reference to `_mysql_free_results' collect2: ld returned 1 exit status -- Fabrice Marchal http://www.inf.ethz.ch/~marchal [EMAIL PROTECTED] [EMAIL PROTECTED] +41-(0)1-632-56-79 ETH Zurich, CoLab Computational LaboratoryFAX:+41-(0)1-632-17-03 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL and productivity
Heh.. I wasn't going by the docs when I responded... I had to reindex a 50+mil row table... Not a good time :) Then I started preparing the structure a bit before taking in the data. Seemed to work better in my circumstance. P -Dan Greene [EMAIL PROTECTED] wrote: - To: Peter J Milanese [EMAIL PROTECTED], Krasimir_Slaveykov [EMAIL PROTECTED] From: Dan Greene [EMAIL PROTECTED] Date: 01/30/2004 09:31AM cc: [EMAIL PROTECTED] Subject: RE: SQL and productivity From what I've read ( I think in the MySQL docs, might have been here in the list), technically it will take less time to add the indexes after the table creation, than the overhead of index updating per-insert. Either way, it's gonna take a loong time. -Original Message- From: Peter J Milanese [mailto:[EMAIL PROTECTED] Sent: Friday, January 30, 2004 9:15 AM To: Krasimir_Slaveykov Cc: [EMAIL PROTECTED] Subject: Re: SQL and productivity I'd start with the indexes in place. 5+mil records will take quite some time to index after the fact. P -Krasimir_Slaveykov [EMAIL PROTECTED] wrote: - To: [EMAIL PROTECTED] From: Krasimir_Slaveykov [EMAIL PROTECTED] Date: 01/30/2004 09:14AM Subject: SQL and productivity Hello , I must create a temp table with 5 or more million records. I need it for creating some reports, so I need to create indexes too. My question is: When total time for work will be smaller? : 1-st case: I create table and create indexes immediately before filling data. 2-nd case: I create table, filling data and then create indexes? -- Best regards, Krasimir_Slaveykov mailto: [EMAIL PROTECTED] [EMAIL PROTECTED] |-| |/ * * *** * ** /| | *** *** *** *** *** *** ** *** //| |/// *** *** * * *** *** * *** ///| |// *** *** *** *** *** *** ** *** *** | |/ *** *** *** * *** /| |--- www.office1.bg --| -- 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]
Re: key_reads key_read_requests
Hi, I am not sure, but may be you had Key_read_requests overflow. Best regards, Mikhail. - Original Message - From: John David Duncan [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, January 29, 2004 11:40 PM Subject: key_reads key_read_requests Hi, key_reads is usually a small fraction of key_read_requests, but in the case of the server below, key_reads is actually GREATER than key_read_requests. Can anyone explain what would cause that to happen? - JD mysql show status like 'key%'; ++---+ | Variable_name | Value | ++---+ | Key_blocks_used| 997521| | Key_read_requests | 42804277 | | Key_reads | 70150022 | | Key_write_requests | 236384514 | | Key_writes | 130961162 | ++---+ 5 rows in set (0.00 sec) -- 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: sql prob
Thanx a lot Tom :) I guess you forgot 'group by NoPeople' at the end of the first query. right ? fab Tom Cunningham wrote: Try this: CREATE TEMPORARY TABLE max_entry SELECT NoPeople, Max(Date_entry) AS Date_entry FROM table; SELECT NoPeople FROM table NATURAL JOIN max_entry WHERE Departure IS NULL; Hi all :) I have 1 relation A. Structure of A: NoPeople Date_entry Date_departure and key is (NoPeople + Date_entry) exemple of records: NoPeople Entry Departure P1 E1 D1 P1 E2 D2 P1 E3 NULL P2 E1 NULL P3 E1 D1 P4 E1 NULL P4 E2 NULL P4 E3 D2 E3E2E1 and D2D1. P1,P2,P3 and P4 are different persons. I would like to query all the people whose date_entry is their MAX date entry AND that have their date departure to NULL. In this exemple, P1 and P2 are ok. P4 isn't ok because max(Date_entry) is E3 and Date_departure - when Date_entry is E3 - isn't NULL. I tried some queries with 'group by' but i can't make it working. Any hint sql masters :) fab -- 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: SQL and productivity
One more consideration, if your 5mil of records contains duplicates you won't get the opportunity to load the table using REPLACE or INSERT IGNORE without the UNIQUE index already in place. You would have to distinct the rows first or manually remove the duplicates before creating the index after load. If that's the case it would probably cost you more time. Ed -Original Message- From: Peter J Milanese [mailto:[EMAIL PROTECTED] Subject: RE: SQL and productivity Heh.. I wasn't going by the docs when I responded... I had to reindex a 50+mil row table... Not a good time :) Then I started preparing the structure a bit before taking in the data. Seemed to work better in my circumstance. P -Dan Greene [EMAIL PROTECTED] wrote: - From: Dan Greene [EMAIL PROTECTED] Subject: RE: SQL and productivity From what I've read ( I think in the MySQL docs, might have been here in the list), technically it will take less time to add the indexes after the table creation, than the overhead of index updating per-insert. Either way, it's gonna take a loong time. -Original Message- From: Peter J Milanese [mailto:[EMAIL PROTECTED] Subject: Re: SQL and productivity I'd start with the indexes in place. 5+mil records will take quite some time to index after the fact. P -Krasimir_Slaveykov [EMAIL PROTECTED] wrote: - From: Krasimir_Slaveykov [EMAIL PROTECTED] Subject: SQL and productivity Hello , I must create a temp table with 5 or more million records. I need it for creating some reports, so I need to create indexes too. My question is: When total time for work will be smaller? : 1-st case: I create table and create indexes immediately before filling data. 2-nd case: I create table, filling data and then create indexes? -- Best regards, Krasimir_Slaveykov mailto: [EMAIL PROTECTED] [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple Binary Download Difference question
Dan Muey [EMAIL PROTECTED] wrote: What is the difference between 'Mac OS X downloads' and 'Mac OS X Package Installer downloads' listed at: http://www.mysql.com/downloads/mysql-4.0.html I know I want 'standard' and not 'max' or 'debug' but do I want=20 'Mac OS X downloads' or 'Mac OS X Package Installer downloads'? What's the difference? 'Mac OS X downloads' is a binary tarball distribution, 'Mac OS X Package Installer downloads' is a binary package in PKG format. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: Convert MS Access to MYSQL
On Friday, January 30, 2004, at 03:12 AM, [EMAIL PROTECTED] wrote: There was some discussion in December of converting MS Access databases to Mysql and the DBManager program was recommended. I have recently converted access to mysql. I used access to import a CSV file and ensured all data was in the right place. I then built a table in mysql ensuring that all columns were in the same order as the access table (and of the correct format) I then used a plugin for access called MyAccess (shareware available via mysql.com), it enables you to use access as your GUI to mysql. I then simply used access to copy from one table to the other. It was a very text heavy import to deal with, with hundreds if not thousands of illegal characters that would corrupt a mysql db. Myaccess escaped them all for me. It was dead simple and I'm no techie. Job done - in about an hour. Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL and productivity
Hi, actually it depends on table type. Since you said that it will be temp table then I guess it will be MyISAM table. In this case it is better to use 2 way: 2-nd case: I create table, filling data and then create indexes? But if you will create InnoDB temp table that (according to Heikki Tuuri) better to use 1 way: 1-st case: I create table and create indexes immediately before filling data. Look at this email from Heikki Tuuri: Hi! You should always create the indexes BEFORE adding the data to an InnoDB table. In CREATE INDEX ... ON ..., MySQL rebuilds the whole table. Thus, it will be much slower to add the indexes afterwards. Many databases have an optimized index build procedure where adding an index afterwards is faster, but that is not the case for InnoDB. DISABLE KEYS has no effect on InnoDB. It is in the TODO to speed up index creation. Maybe in 2005 it will be faster to add the indexes afterwards :). Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ Best regards, Mikhail. - Original Message - From: Krasimir_Slaveykov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 30, 2004 3:14 PM Subject: SQL and productivity Hello , I must create a temp table with 5 or more million records. I need it for creating some reports, so I need to create indexes too. My question is: When total time for work will be smaller? : 1-st case: I create table and create indexes immediately before filling data. 2-nd case: I create table, filling data and then create indexes? -- Best regards, Krasimir_Slaveykov mailto: [EMAIL PROTECTED] [EMAIL PROTECTED] |-| |/ * * *** * ** /| | *** *** *** *** *** *** ** *** //| |/// *** *** * * *** **** *** ///| |// *** *** *** *** *** *** ** ****** | |/ *** *** *** * *** /| |--- www.office1.bg --| -- 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]
Privileges By Groups
Is there any way to assign Privileges to databases/tables/fields via Groups rather than user by user?
Re: Memory Problems on a G5/OSX/MySql4.0.17
On Jan 28, 2004, at 12:01 PM, Bruce Dembecki wrote this wonderful stuff: So.. My tips for you: 1) Consider a switch to InnoDB, the performance hit was dramatic, and it's about SO much more than transactions (which we still don't do)! Consider it switched! as soon as I find the way to do so :) Are there any changes necessary to his code/queries to use innodb? No changes needed to code/queries, except probably the daily table optimize/repair can go away... As far as how to do it... It seems you can (or are forced to) pretty much have daily down time... All you need is some time to bring the machine down for a quick bounce... First edit the my.cnf file to add the InnoDB settings... You have a setting in your current my.cnf disabling InnoDB, that needs to go away, my InnoDB settings are below, you probably don't need a 40Gig disk space for InnoDB, but you should take 4 x data size at least (InnoDB keeps data times 2 so it can roll back transactions, and then there are index etc...). So Decide how big you want your space to be and make the appropriate changes in my.cnf to make it happen... Whatever you go with you are stuck with as a minimmum, it's hard to go smaller once it is live. After you bounce MySQL and InnoDB is then an option you will still need some memory for MyISAM until you change the tables... So maybe set the sort/read/join buffers to 2M if they aren't already, Key Buffer to say 250M and the InnoDB numbers close to mine. After you have moved the tables just drop the Key Buffer down to 16M or so. Oh yes, and as I will say below, drop that query cache, 64M is plenty for you I expect, the rest is being wasted. Once this is all setup go ahead and bounce the server so the new settings take place. So now you have InnoDB available... All you need now it to change the data... The command is: ALTER TABLE xxx TYPE=InnoDB; Once again, do NOT change the mysql database, it MUST stay as MyISAM, and doesn't affect your performance at any rate. Once this is done you can go ahead and drop the Key Buffer right down to something tiny, 16M or so is what we have, and bounce MySQL again and you are all set. 2) Drop the query cache to something more practical, a gigabyte is fine if your data is static, if it's not it's way too much. We use 128MBytes and typically have about a 30% hit rate on the Query cache and the busiest server is showing 80MBytes unused memory in the query cache and a 41% hit rate, and our databases take about 40G of disk space. Remember having a big query cache doesn't help if it's mostly sitting unused (in fact if ours are still sitting with 80M free in a week I'll drop all of them 64MBytes). we have an average of ~15-20%, with times sustaining 30+% Errmm... The stats you included says that the Query Cache is WAY out of control. There's like a gigabyte of unused space cache there. 3) Give lots of memory to InnoDB, I'll share my settings below. Thank You! 4) Take most of the non InnoDB memory settings and drop them down real low, InnoDB does well on it's own and if you convert all tables you don't need to leave much in the way of resources for MyISAM. ok 5) Turn on and use the slow query log (and if need be change the time needed to qualify as a slow query, the default 10 seconds is a lifetime). You may not code the queries yourself, but you can identify the queries that are causing problems and from there you can advise the client on changes to the database structure (indexes etc) or at least tell him exactly what the problem queries are. The slow log has helped us a lot in the past... with the current slow log settings, only about 0.1% are slow queries. 3K out of 4million in the past 18hours. Currently the time appears to be set at 2 (From show variables: slow_launch_time 2 ). 6) Go get MyTOP from Jeremy Zawodny at http://jeremy.zawodny.com/mysql/mytop/ - personally I like version 1.3 but that may just be what I am used to... You may not be able to control the coding part but you can at least monitor the server and see what it's up to and quickly and easily see problems. Great tool.. only recently started using it. 7) If you decide to stay with MyISAM and not InnoDB then you will want as much memory as you can in the Key Buffer while leaving some space in the sort/read/join buffers.. I'd up the sort/read/join buffers to maybe 10MBytes, or even 20Mbytes, if you need to drop Key buffer to 1500M to give you the space for the others. We got OKish results on MyISAM with the larger sort/read/join buffers - InnoDB made all the difference though. I've only gone as high as 6M on those before. Before giving you our settings I do want to point out one thing... We haven't fine tuned the memory settings since we did the G5 switch. At the time I was bringing the machines up they needed to be up quickly, and when it didn't work correctly with my original settings I had to make adjustments to get it to run at all.
Re: SQL and productivity
At 08:14 AM 1/30/2004, you wrote: Hello , I must create a temp table with 5 or more million records. I need it for creating some reports, so I need to create indexes too. My question is: When total time for work will be smaller? : 1-st case: I create table and create indexes immediately before filling data. 2-nd case: I create table, filling data and then create indexes? And the winner is #1, well maybe. :-) The reason is when you use Alter Table to add an index, it creates a empty temporary table with the new table structure and indexes, MySQL then copies the old data to the temporary table (with the new indexes) so your hard disk needs at least twice the disk space as the original table. If that succeeds, MySQL drops the old table and renames the temporary table to the proper table name. So #2 must do an additional unload and reload of the data. Of course if you use separate Alter Table Add Index statements, it will do this each time. If you add all the indexes in one Alter Table statement the data is only unloaded and reloaded once. 5 million rows is peanuts.g I have tables with up to 100 million rows and I never build indexes later after the data is in there if I don't have to. Otheriwse I'd need another 6gb free for the temporary table. If you are loading a lot of data at one time, I definitely recommend using Load Data. It is much faster than Insert. If you can't use Load Data then #2 could be faster if you use only 1 Alter Table statement to build all the indexes. Mike -- Best regards, Krasimir_Slaveykov mailto: [EMAIL PROTECTED] [EMAIL PROTECTED] |-| |/ * * *** * ** /| | *** *** *** *** *** *** ** *** //| |/// *** *** * * *** **** *** ///| |// *** *** *** *** *** *** ** ****** | |/ *** *** *** * *** /| |--- www.office1.bg --| -- 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 Connection to COBOL
Hi MySQLians, For the last 2 months I have tried to establish connection between COBOL and MySQL. Hope I have succeeded a stage in this regard. I have used Remote server: MySQL Server 4.0.17-max in Suse Linux Client: Windows 2000 Microsoft VC++ 6.0 CA-Realia COBOL Compiler Version 6.0.45 Microsoft (R) Incremental Linker Version 6.00.8168 MySQL 4.0.17-max-debug for Windows In COBOL I can call C functions as external subroutines. I have called the MySQL C API functions from COBOL and try to invoke the connection to MySQL. To do this currently I am in need to call mysql_init, mysql_real_connect, mysql_error and mysql_close functions. I have coded in COBOL and executed, each time when I struck up with error I have mailed to this list, but I could not find any favorable response. Finally now, I have reached the status of connection establishment between COBOL and MySQL with the error message; Can't connect to MySQL server on 'neptune' (10049) According to the manual from the MySQL site, this error happened due to improper Port No or Socket information. But, I have passed the default Port No.3306 and NULL for the socket as parameter to the function mysql_real_connect. Can any one suggest me what else creating this error? I think to establish connection in this way it does not require ODBC settings and DNS in the client machine. It just established the connection via *wsock32* library of the C (here it is VC++). I have further doubts, Is there a need of any SSH in client side? I dont have clear idea about this I hope if I succeed it will be a success to MySQL team too Expecting valuable suggestions Thanks in advance Sincerely yours, Arun. Yahoo! India Mobile: Download the latest polyphonic ringtones. Go to http://in.mobile.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Time out problem
I have developed a website on my PC and I am testing there too. I keep getting script time out errors, can I stop this from happening? I am basically having to build an identical site and test it using access. Double the work!! I can't believe that access is responding tens if not hundreds of times faster and without timing out once! What could I have done wrong? I use a PC with 1ghz processor, 650ram, Windows ME running personal web server (not IIS), Mysql 4.0.15, I am also using MySQL Front. TIA Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL CONNECTIVITY
Hi all, I have been noticing following error message with PHP. Fatal error: Call to undefined function: mysql_connect() Let me know how to fix that.I have installed linux9,apache 2.0, php. thx -seena - Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it!
RE: [Mysql-discussions] SQL CONNECTIVITY
your php is not configured to use mysql? perhaps? and just what is linux9? please install php with mysql support. -- Kind regards, Remko Lodder Elvandar.org/DSINet.org www.mostly-harmless.nl Dutch community for helping newcomers on the hackerscene -Oorspronkelijk bericht- Van: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Seena Blace Verzonden: vrijdag 30 januari 2004 17:12 Aan: [EMAIL PROTECTED] Onderwerp: [Mysql-discussions] SQL CONNECTIVITY Hi all, I have been noticing following error message with PHP. Fatal error: Call to undefined function: mysql_connect() Let me know how to fix that.I have installed linux9,apache 2.0, php. thx -seena - Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need an XRef Table or Something
Here are some tables: Create Table FN ( FNunid varchar(32) not null, ..., PRIMARY KEY (FNunid) ); Create Table Dependent ( DEPunid varchar(32) not null, FNunid varchar(32) not null, ..., PRIMARY KEY (DEPunid), CONSTRAINT `0_69` FOREIGN KEY (FNunid) REFERENCES FN(FNunid) ); Create Table Action( ACTunid varchar(32) not null, FNunid varchar(32) not null, ... PRIMARY KEY (ACTunid), CONSTRAINT `0_67` FOREIGN KEY (FNunid) REFERENCES FN (FNunid) ); The tables were designed to hold information being transferred from a lot of Lotus Notes databases (that's why the UNID primary keys are 32 characters). Obviously, the assumption was that Dependent and Action records are children of FN records. This worked fine for a million plus records -- and then... Some bright people in another office constructed their Lotus Notes databases so that some Dependent records are children of other Dependent records, which, through a chain, ultimately connect to an FN record. Likewise Action records can be children of Dependent or Dependent-to-a-Dependent records. In these cases, the FNunid field actually contains the DEPunid of the next higher Dependent record until you finally get to a record with FNunid = to an FN.FNunid. Do I need to say kaboom? I think the way out of this is to create some sort of lookup table that relates the various levels of dependent records to the root FN record. I can't seem to get my head around the design of such a table, however. By definition (and the structure of Lotus Notes databases), every dependent and action record is, in fact, a child to another dependent or root FN record. Not every FN record, however, has children of either Dependent or Action type. In addition, no FN record is a child of any other record. I thought of something like: Create Table XRef( ThisRecordID varchar(32) not null, ParentRecordID varchar(32) default null ) But I can't figure out how to write a SQL query that will trace an Action or Dependent record, at an unknown level in the hierarchy, back to the root FN. Is there a better way to do this or can you help me with the SQL statement? Thanks Randy [EMAIL PROTECTED] Confidentiality Note: This message and any accompanying attachments contain information from the law firm Fragomen, Del Rey, Bernsen Loewy, P.C. which is confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this e-mail in error, please notify our offices immediately, by telephone at 212-688-8555, or by e-mail at [EMAIL PROTECTED]
RE: [Mysql-discussions] SQL CONNECTIVITY
I installed php. what is linux9? =RED HAT 9 aCTUALLY i WANT TO develop smal webbased application.I was thinking PHP,MYSQL,APACHE AND LINUX WOULD BE good,but any other combination would be great if you guyes suggest. Remko Lodder [EMAIL PROTECTED] wrote: your php is not configured to use mysql? perhaps? and just what is linux9? please install php with mysql support. -- Kind regards, Remko Lodder Elvandar.org/DSINet.org www.mostly-harmless.nl Dutch community for helping newcomers on the hackerscene -Oorspronkelijk bericht- Van: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Seena Blace Verzonden: vrijdag 30 januari 2004 17:12 Aan: [EMAIL PROTECTED] Onderwerp: [Mysql-discussions] SQL CONNECTIVITY Hi all, I have been noticing following error message with PHP. Fatal error: Call to undefined function: mysql_connect() Let me know how to fix that.I have installed linux9,apache 2.0, php. thx -seena - Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it!
Re: InnoDB Backups
By open file tool I mean software that works concurrently with a backup suite such as veritas that would backup any open files (such as MySQL binaries) that would normally be skipped. What do you mean by inconsistent? What does InnoDB Hot backup do differently than an open file agent? So the only two ways to do an online backup of InnoDB tables is InnoDB Hot backup or mysqldump? Thanks, Mauro - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 30, 2004 4:22 AM Subject: Re: InnoDB Backups Mauro, - Original Message - From: Mauro Marcellino [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, January 29, 2004 10:44 PM Subject: InnoDB Backups --=_NextPart_000_00CE_01C3E67E.9D867B90 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable I have made a couple of other postings related to this but I guess my question is: Can I backup InnoDB tables (binary files) using an open file agent? what do you mean by an open file agent? You cannot just copy the ibdata files and ib_logfiles as is, because they would be inconsistent. A commercial tool to make an online (= hot) binary backup is InnoDB Hot Backup. A free way to make an online backup is to use mysqldump. If yes...and I am using Automatic COMMITs my backup will be current? If I am not using Automatic COMMITs then my backup will contain data = up to the last COMMIT (In other words, data since the last COMMIT will not be included in the backup. Is this true? I appreciate any guidance...Thanks Much! Mauro Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.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]
RE: [Mysql-discussions] SQL CONNECTIVITY
That would be good, But installing php does not mean that mysql features are also added. Please checkout your php configuration if it is included, otherwise install the appropiate packages from redhat -- Kind regards, Remko Lodder Elvandar.org/DSINet.org www.mostly-harmless.nl Dutch community for helping newcomers on the hackerscene -Oorspronkelijk bericht- Van: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Seena Blace Verzonden: vrijdag 30 januari 2004 17:44 Aan: [EMAIL PROTECTED] Onderwerp: RE: [Mysql-discussions] SQL CONNECTIVITY I installed php. what is linux9? =RED HAT 9 aCTUALLY i WANT TO develop smal webbased application.I was thinking PHP,MYSQL,APACHE AND LINUX WOULD BE good,but any other combination would be great if you guyes suggest. Remko Lodder [EMAIL PROTECTED] wrote: your php is not configured to use mysql? perhaps? and just what is linux9? please install php with mysql support. -- Kind regards, Remko Lodder Elvandar.org/DSINet.org www.mostly-harmless.nl Dutch community for helping newcomers on the hackerscene -Oorspronkelijk bericht- Van: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Seena Blace Verzonden: vrijdag 30 januari 2004 17:12 Aan: [EMAIL PROTECTED] Onderwerp: [Mysql-discussions] SQL CONNECTIVITY Hi all, I have been noticing following error message with PHP. Fatal error: Call to undefined function: mysql_connect() Let me know how to fix that.I have installed linux9,apache 2.0, php. thx -seena - Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
New user B setup - documentation?
Hallo, I'm totally new to MySQL or any other data bases... I installed MySQL 4.0.15a as supplied with the SlackWare 9.1 Linux distribution. The only documentation supplied are the Reference Manual en the man pages. But the Reference Manual is a little bit too touch for me. I managed to get mysqld started, but now I'm stuck. Is there any other documentation available somewhere that will lead me through the setup in a nice and gentle way? Groetjes, Hans. --- GoldED+/LNX 1.1.5cvs031202 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [Mysql-discussions] SQL CONNECTIVITY
To configure PHP for MYSQL from (http://www.php.net/manual/en/ref.mysql.php): Requirements In order to have these functions available, you must compile PHP with MySQL support. Installation By using the --with-mysql[=DIR] configuration option you enable PHP to access MySQL databases. In PHP 4, the option --with-mysql is enabled by default. To disable this default behavior, you may use the --without-mysql configure option. Also in PHP 4, if you enable MySQL without specifying the path to the MySQL install DIR, PHP will use the bundled MySQL client libraries. In Windows, there is no DLL, it's simply built into PHP 4. Users who run other applications that use MySQL (for example, auth-mysql) should not use the bundled library, but rather specify the path to MySQL's install directory, like so: --with-mysql=/path/to/mysql. This will force PHP to use the client libraries installed by MySQL, thus avoiding any conflicts. Marc. -Message d'origine- De : Seena Blace [mailto:[EMAIL PROTECTED] Envoyé : vendredi 30 janvier 2004 17:44 À : [EMAIL PROTECTED] Objet : RE: [Mysql-discussions] SQL CONNECTIVITY I installed php. what is linux9? =RED HAT 9 aCTUALLY i WANT TO develop smal webbased application.I was thinking PHP,MYSQL,APACHE AND LINUX WOULD BE good,but any other combination would be great if you guyes suggest. Remko Lodder [EMAIL PROTECTED] wrote: your php is not configured to use mysql? perhaps? and just what is linux9? please install php with mysql support. -- Kind regards, Remko Lodder Elvandar.org/DSINet.org www.mostly-harmless.nl Dutch community for helping newcomers on the hackerscene -Oorspronkelijk bericht- Van: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Seena Blace Verzonden: vrijdag 30 januari 2004 17:12 Aan: [EMAIL PROTECTED] Onderwerp: [Mysql-discussions] SQL CONNECTIVITY Hi all, I have been noticing following error message with PHP. Fatal error: Call to undefined function: mysql_connect() Let me know how to fix that.I have installed linux9,apache 2.0, php. thx -seena - Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld hangs with no CPU activity...
Paul Stearns wrote: As reported under the subject Random Database Slowdowns... on the win32 list, our database still hangs on an average of 1-2 times per day. I can find no error messages or logs associated to the problem. It affects both IIS ADO connections as well as local connections from tools such as mysqlcc, mysqladmin and command line tools such as mysql. I see no CPU activity associated with the hangs. I cannot stop and restart the service, but most of the time a reboot will resolve the problem. Sometimes the problem will reoccur within a few minutes of a reboot, other times it takes hours. I can't swear it's the same problem, but we had very similar symptoms some time ago (version was around 4.0.8 or so). This was also on Mac OS X Server 10.2.x. Seemingly random queries would just not finish. They were queries we had run before and could even run at the same time from a different client. But this process would just not finish. It would sit in show processlist forever. If it was the only query running the mysqld cpu load would drop to around zero. There was no i/o activity if it was the only query running. Any temp files associated with the query wouldn't grow. Sometimes it was a small query, sometimes a big one. If you tried to kill the process from within the command line client or mysqladmin it would show up as killed in the process list but never die. Issuing mysqladmin shutdown wouldn't shut down the mysqld server b/c it couldn't kill off the queries either. Even kill -9 mysqld pid would hang the machine. The only solution was rebooting while mysqld was running b/c we couldn't shut it down. We tried moving to InnoDB and got the same situation (and show innodb status\G revealed no work was being done within InnoDB). We tried swapping RAM, swapping hard drives, changing drive formats (HFS+ to UFS), installing Yellow Dog Linux on the machine, etc I was pretty certain it was just a MySQL on the Mac issue, but then I pulled the drives and RAM and put them in an identical Mac. Same drives, same RAM, same data, same OS, same MySQL...the problem disappeared. We sent the machine back to Apple (it was new) and they replaced the mother board. That machine would show random errors even in just desktop use, so though the solution seems onerous we really didn't have much choice. If you can, I'd try running the queries on a different machine. Good luck, Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld hangs with no CPU activity...
Ware Adams said: Paul Stearns wrote: As reported under the subject Random Database Slowdowns... on the win32 list, our database still hangs on an average of 1-2 times per day. I can find no error messages or logs associated to the problem. It affects both IIS ADO connections as well as local connections from tools such as mysqlcc, mysqladmin and command line tools such as mysql. I see no CPU activity associated with the hangs. I cannot stop and restart the service, but most of the time a reboot will resolve the problem. Sometimes the problem will reoccur within a few minutes of a reboot, other times it takes hours. I can't swear it's the same problem, but we had very similar symptoms some time ago (version was around 4.0.8 or so). This was also on Mac OS X Server 10.2.x. Seemingly random queries would just not finish. They were queries we had run before and could even run at the same time from a different client. But this process would just not finish. It would sit in show processlist forever. If it was the only query running the mysqld cpu load would drop to around zero. There was no i/o activity if it was the only query running. Any temp files associated with the query wouldn't grow. Sometimes it was a small query, sometimes a big one. If you tried to kill the process from within the command line client or mysqladmin it would show up as killed in the process list but never die. Issuing mysqladmin shutdown wouldn't shut down the mysqld server b/c it couldn't kill off the queries either. Even kill -9 mysqld pid would hang the machine. The only solution was rebooting while mysqld was running b/c we couldn't shut it down. We tried moving to InnoDB and got the same situation (and show innodb status\G revealed no work was being done within InnoDB). We tried swapping RAM, swapping hard drives, changing drive formats (HFS+ to UFS), installing Yellow Dog Linux on the machine, etc I was pretty certain it was just a MySQL on the Mac issue, but then I pulled the drives and RAM and put them in an identical Mac. Same drives, same RAM, same data, same OS, same MySQL...the problem disappeared. We sent the machine back to Apple (it was new) and they replaced the mother board. That machine would show random errors even in just desktop use, so though the solution seems onerous we really didn't have much choice. If you can, I'd try running the queries on a different machine. Good luck, Ware If moving to new hardware solved the problem, it was the hardware. Had similar problem with Linux kernel compilation a few years ago. Drove us crazy for a while since on that process failed. Turned out bad cache chip on motherboard. Moral: if you can't trust the hardware, all bets are off. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [Mysql-discussions] SQL CONNECTIVITY
Remko, is any parameters value do you want me change in php.ini file? which package do you want to install from redhat pl? Remko Lodder [EMAIL PROTECTED] wrote: That would be good, But installing php does not mean that mysql features are also added. Please checkout your php configuration if it is included, otherwise install the appropiate packages from redhat -- Kind regards, Remko Lodder Elvandar.org/DSINet.org www.mostly-harmless.nl Dutch community for helping newcomers on the hackerscene -Oorspronkelijk bericht- Van: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Seena Blace Verzonden: vrijdag 30 januari 2004 17:44 Aan: [EMAIL PROTECTED] Onderwerp: RE: [Mysql-discussions] SQL CONNECTIVITY I installed php. what is linux9? =RED HAT 9 aCTUALLY i WANT TO develop smal webbased application.I was thinking PHP,MYSQL,APACHE AND LINUX WOULD BE good,but any other combination would be great if you guyes suggest. Remko Lodder wrote: your php is not configured to use mysql? perhaps? and just what is linux9? please install php with mysql support. -- Kind regards, Remko Lodder Elvandar.org/DSINet.org www.mostly-harmless.nl Dutch community for helping newcomers on the hackerscene -Oorspronkelijk bericht- Van: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Seena Blace Verzonden: vrijdag 30 januari 2004 17:12 Aan: [EMAIL PROTECTED] Onderwerp: [Mysql-discussions] SQL CONNECTIVITY Hi all, I have been noticing following error message with PHP. Fatal error: Call to undefined function: mysql_connect() Let me know how to fix that.I have installed linux9,apache 2.0, php. thx -seena - Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it!
RE: [Mysql-discussions] SQL CONNECTIVITY
you need to install this file, findable on the ftp servers from redhat php-mysql-4.2.2-17.i386.rpm ftp pwd 257 /pub/redhat/linux/9/en/os/i386/RedHat/RPMS (ftp.redhat.com/pub/redhat/linux/9/en/os/i386/RedHat/RPMS/) how that further works i dont know since i dont use redhat at all. goodluck -- Kind regards, Remko Lodder Elvandar.org/DSINet.org www.mostly-harmless.nl Dutch community for helping newcomers on the hackerscene -Oorspronkelijk bericht- Van: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Seena Blace Verzonden: vrijdag 30 januari 2004 18:52 Aan: [EMAIL PROTECTED] Onderwerp: RE: [Mysql-discussions] SQL CONNECTIVITY Remko, is any parameters value do you want me change in php.ini file? which package do you want to install from redhat pl? Remko Lodder [EMAIL PROTECTED] wrote: That would be good, But installing php does not mean that mysql features are also added. Please checkout your php configuration if it is included, otherwise install the appropiate packages from redhat -- Kind regards, Remko Lodder Elvandar.org/DSINet.org www.mostly-harmless.nl Dutch community for helping newcomers on the hackerscene -Oorspronkelijk bericht- Van: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Seena Blace Verzonden: vrijdag 30 januari 2004 17:44 Aan: [EMAIL PROTECTED] Onderwerp: RE: [Mysql-discussions] SQL CONNECTIVITY I installed php. what is linux9? =RED HAT 9 aCTUALLY i WANT TO develop smal webbased application.I was thinking PHP,MYSQL,APACHE AND LINUX WOULD BE good,but any other combination would be great if you guyes suggest. Remko Lodder wrote: your php is not configured to use mysql? perhaps? and just what is linux9? please install php with mysql support. -- Kind regards, Remko Lodder Elvandar.org/DSINet.org www.mostly-harmless.nl Dutch community for helping newcomers on the hackerscene -Oorspronkelijk bericht- Van: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Seena Blace Verzonden: vrijdag 30 januari 2004 17:12 Aan: [EMAIL PROTECTED] Onderwerp: [Mysql-discussions] SQL CONNECTIVITY Hi all, I have been noticing following error message with PHP. Fatal error: Call to undefined function: mysql_connect() Let me know how to fix that.I have installed linux9,apache 2.0, php. thx -seena - Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Advise on High Availability configuration
Hello All; I am in the process of examining a High Availability (HA) configuration. The motivation is to not use database replication (at least at this stage) because of the need to work on the complete data set at any given point in time. Here is the configuration choice being considered CONFIGURATION: - I would like to configure two machines, Primary and Secondary. Each machine has our Application and an instance of MySQL database server running on it. - Additionally, I will setup a SCSII controller in the Primary and Secondary Application machines so that the actual data store (disk drive) runs on another physical machine in a disk-array (RAID). - With this setup the MySQL Servers on both the machines will write/read data to/from this disk array. OPERATION: - The idea is to have only one machine actively use the data store at any given time. The other machine will be in the stand-by mode. - To start off, the Primary is Active, performing database operations on the data store. The Secondary is in stand-by mode and does NOT perform any database operations. - If the Primary goes down, the Secondary becomes Active and starts to perform the database operations. CAVEATS I AM AWARE OFF: - Does not work with InnoDB. - Works with MyISAM but need to disable key buffer. This leads to big hit on performance QUESTIONS: - Have any of you seen such a configuration being deployed? - Do you see any big gotcha's in this configuration? - Is it possible for the Primary MySQL process to lock the data store such that the Secondary MySQL process cannot access the data store? - Is it possible for the 2 MySQL processes update the same table simultaneously? Looking forward to all your feedback. Thank you. Gowtham. __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is INSERT...SELECT atomic? [REPOST]
Does anyone know this? In the 4.0.17 and later build, the target table of an INSERT...SELECT can be the same as the SELECT table (insert into table1...select ...from table1). Does the process essentially happen in a natural lock? In other words, could the table change between the data retrieved in the select and the time the insert gets executed? We used to use something like this: Lock tables table1 write; Select @a:=max(column) from table1; Insert into table1 (column) values (@a+1); Unlock tables; Could that now be done as: Insert into table1 (column) select max(column)+1 from table1; -Allon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Efficiently handling large amounts of data in mysql
I routinely see people requesting information on how to efficiently handle *large* tables in mysql. Hopefully, the following will be helpful to those individuals. I have several tables that are 500M to 4B rows in size and require me to drop and re-create indices at regular intervals. Usually, I'm removing about 10% of the existing records and then adding about 15% new records back in. Doing this with indices turned on would take days if not weeks. With the proper sequence of operations, however, I can accomplish this in about 24 hours. For reference, my database server consists of a dual processor machine with a 3Ware 7xxx series controller. For what we are trying to do here, processor speed is almost irrelevant when compared to disk I/O speed. Tests with bonnie++ indicate we get ~130MB/s when using 8 disks configured for RAID 0+1. Note: RAID 5 write performance is 10x slower than RAID 0+1 on these cards For convenience, I will refer to my table as Data throughout this explanation. Deleting Data - It turns out that copying only the desired portion of the data can be much more efficient than running a series of DELETE FROM TABLE type of statements. In addition, it has the same effect as running OPTIMIZE TABLE which should be run anyway in this situation. Removing unwanted records: 1) mysql ALTER TABLE Data RENAME TO OldData; *This can require up to 2x the size of Data.MYD in free disk space 2) mysql CREATE TABLE Data table spec; *Do not add indices at this point, we will add them later 3) mysql INSERT INTO Data SELECT * FROM OldData WHERE criteria for selection; At this point you can choose to drop OldData or keep it around until you are confident that you did everything correctly so far. Loading Data - When possible, we load data using mysqlimport. This is much faster than connecting to the server and executing a series of INSERT statements. Creating Indices - The following process works for adding or removing indices from large tables. It does, however, require direct access to the MYD/MYI files used by mysql which likely means you need root privileges on your database server. This process was given to me by an old and wise mysql administrator/developer. Hopefully, it will someday make it into the FAQ/HOW-TO section of the docs. 1) Flush the tables (force mysql to close all open file handles) by executing: % mysql -e flush tables or % mysqladmin flush-tables If possible, you should also consider shutting down the mysql server at this point 2) Rename the data file (.MYD) by changing to the directory where your database tables are stored and executing the following command: % mv Data.MYD Data.MYD.save 3) Truncate the data and index files so that mysql thinks the table contains no data: (Restart the mysql server if you stopped it in step #1) mysql TRUNCATE TABLE Data; When you execute 'TRUNCATE TABLE' (or equivalent), mysql simply deletes and recreates the MYD and MYI files for the table. Since you've already removed the MYD file as far as mysql is concerned, this is very fast. 4) Now add your index(es) to the empty table: mysql ALTER TABLE ADD INDEX index spec; 5) This is the same as step #1. Either flush the tables or shut down mysql. At this point, you should have 4 files like Data.*: Data.MYD, Data.MYD.save, Data.MYI, and Data.frm 6) Restore the data file you renamed in step #2: % mv Data.MYD.save Data.MYD This will result in a mismatch between the data and index files similar to what you might find if the table crashed. 7) Restore/repair the index file based on the data file: % myisamchk -q -r options Data.MYI Typical options I use: -O key_buffer=128M -O sort_buffer=128M -O read_buffer=2M -O write_buffer=2M The above command will recreate the index without copying the data file. This means that the index creation process does roughly 50% less work -- and it is the expensive part of the process, writing the data to disk, that is skipped. If you have any questions regarding this process, please feel free to contact me. Kyle *** Kyle J. MunnEmail: [EMAIL PROTECTED] EraGen Biosciences Phone: 608.662.9000 x351 http://www.EraGen.com Fax: 608.662.9008 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Segmentation Fault/Core dump
Any thoughts on what might be causing this error? I am runing both on cygwin under windows 2000 with 4.0.16 and using gcc to compile, linking using gcc sample.c -o sample.exe -I/usr/include/mysql -L/lib/mysql/opt/ -lmysqlclient -lmySQL -lz -lm I assume that something in the libraries may not be correct or that I need to check some other parameter but I am at a loss. Thanks Leif
Multiple mysql servers with the same datadir
Hi, all. I notice this is not recommended in the manual. But I wonder if not considering performance, when using dbd as the table type, are there any problems in these two cases? 1) running multiple mysqld processes on the same machine with the same datadir. 2) running multiple mysqld on different machines. but they use the same datadir through NFS. I think there will be problems when using other type of databases. But I heard dbd uses page level lock. So if just talking about consistency, are these two cases ok? Thanks a lot! - Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it!
How to get libmysqlclient_r.so
MySql exporters: I downloaded mysql-4.0.17 binary package for Solaris 8, uncompressed, un-tared and have it running by the command bin/mysqld_safe --user=mysql . Now I'm trying to install another application, RIPE Whois Server, which requires mysql/lib/libmysqlclient_r.so during the installation and failed because that file does not exist. Under mysql/lib, only libmysqlclient_r.a is available. I tried to run configure --enable-thread-safe-client without any luck, so how or from where I can get this libmysqlclient_r.so library file? Thank you in advance for any help. C.C. Liu [EMAIL PROTECTED]
Performance Problem
Hi, I´m having a serious performance problem with my MySQL. The CPU is most of time with a load of 60%-95%. I´m using MySQL-4.0.14 on a FreeBSD-5.1 box. It´s a Celeron-2.0 Ghz - 512 Mb RAM - 40 Gb of Hard Disk. I think the main problem the queries struct wrongly build, but at this time I need to solve the main problem (slower pages) at first. And after that, change the site structure with more calm times. Does any good soul could give some tips to solve my problem? Upgrade my MySQL would solve part of the problem? What version should I use? My application has 71 tables + 261 indexes. It has 3 tables with almost 2.000.000 of records and the orthers have an average of 1.000 records. Any help would be appreciated. Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to get libmysqlclient_r.so
Do a search on RPMFind.net and download libmysqlclient for the version of your mysql. I had the same problem installing MYSQL support in Qmail/Vpopmail Steve Sills Platnum Computers, President http://www.platnum.com [EMAIL PROTECTED] - Original Message - From: Liu, C.C [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 30, 2004 1:20 PM Subject: How to get libmysqlclient_r.so MySql exporters: I downloaded mysql-4.0.17 binary package for Solaris 8, uncompressed, un-tared and have it running by the command bin/mysqld_safe --user=mysql . Now I'm trying to install another application, RIPE Whois Server, which requires mysql/lib/libmysqlclient_r.so during the installation and failed because that file does not exist. Under mysql/lib, only libmysqlclient_r.a is available. I tried to run configure --enable-thread-safe-client without any luck, so how or from where I can get this libmysqlclient_r.so library file? Thank you in advance for any help. C.C. Liu [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to get libmysqlclient_r.so
Steve, Thank you for the information, I checked but found they are all for Linux and none is for Solaris. I will try to download the source file package and compile the whole thing to see if it works. C.C. Liu -Original Message- From: Steve Sills [mailto:[EMAIL PROTECTED] Sent: Friday, January 30, 2004 3:37 PM To: Liu, C.C; [EMAIL PROTECTED] Subject: Re: How to get libmysqlclient_r.so Do a search on RPMFind.net and download libmysqlclient for the version of your mysql. I had the same problem installing MYSQL support in Qmail/Vpopmail Steve Sills Platnum Computers, President http://www.platnum.com [EMAIL PROTECTED] - Original Message - From: Liu, C.C [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 30, 2004 1:20 PM Subject: How to get libmysqlclient_r.so MySql exporters: I downloaded mysql-4.0.17 binary package for Solaris 8, uncompressed, un-tared and have it running by the command bin/mysqld_safe --user=mysql . Now I'm trying to install another application, RIPE Whois Server, which requires mysql/lib/libmysqlclient_r.so during the installation and failed because that file does not exist. Under mysql/lib, only libmysqlclient_r.a is available. I tried to run configure --enable-thread-safe-client without any luck, so how or from where I can get this libmysqlclient_r.so library file? Thank you in advance for any help. C.C. Liu [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: *Forcing* use of TCP/IP by clients for localhost
In this case it might be easier for you to just modify the MySQL source to disable looking for the socket, and treat localhost as 127.0.0.1. --Pete On Fri, Jan 30, 2004 at 06:33:22AM -0800, [EMAIL PROTECTED] wrote: --- [EMAIL PROTECTED] wrote --- i've never set up tunnels and such, but are you sure that your ssh tunnel is also listening on localhost (i.e. 127.0.0.1)? i once had a problem like that with my apache, and it turned out that it only listened on 'real' IPs Yeah, the tunnels work perfectly if I do this: mysql -u root -p -P 3306 -h 127.0.0.1 database_name Blazing fast (same subnet), almost as fast as a local connection. The server load on the client end dropped like a rock too when I stopped the local MySQL copy (the tunnel to the remote MySQL is what I want to use :). It's just that the scripts and client aren't poking 127.0.0.1 via TCP/IP -- they check sockets and die when the socket isn't there. cheers and good luck, Thanks! -- 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: InnoDB Backups
The Hot backup/dump tools use the mysql server to create a live backup while the server is running. On MyIsam tables, I think they are locked during the entire process.. innodb may be different. You can backup the DB files directly, but, the mysql server MUST be shut down to do so.. which is likely not what you want. Since lots of information may be sitting in the buffers when you copy the files, along with file-close checks and such, you would only get partial data backups, which would be far less effective to restore from. When the mysql server is shut down, those buffers would all be sent to disk. Any remaining FS/OS buffers would be honored by the FS call for copy. So, yes it works fine if the files are copied when the server is off. I am about to switch to innodb myself, and I am simply going to have to buy the Innodb hot backup tool to make full backups. however, do not forget that even those are out of date the moment the backup is done ;) Replication is your best friend, next to your Dog of course, -- Adam Goldstein White Wolf Networks http://whitewlf.net On Jan 30, 2004, at 11:50 AM, Mauro Marcellino wrote: By open file tool I mean software that works concurrently with a backup suite such as veritas that would backup any open files (such as MySQL binaries) that would normally be skipped. What do you mean by inconsistent? What does InnoDB Hot backup do differently than an open file agent? So the only two ways to do an online backup of InnoDB tables is InnoDB Hot backup or mysqldump? Thanks, Mauro - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 30, 2004 4:22 AM Subject: Re: InnoDB Backups Mauro, - Original Message - From: Mauro Marcellino [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, January 29, 2004 10:44 PM Subject: InnoDB Backups --=_NextPart_000_00CE_01C3E67E.9D867B90 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable I have made a couple of other postings related to this but I guess my question is: Can I backup InnoDB tables (binary files) using an open file agent? what do you mean by an open file agent? You cannot just copy the ibdata files and ib_logfiles as is, because they would be inconsistent. A commercial tool to make an online (= hot) binary backup is InnoDB Hot Backup. A free way to make an online backup is to use mysqldump. If yes...and I am using Automatic COMMITs my backup will be current? If I am not using Automatic COMMITs then my backup will contain data = up to the last COMMIT (In other words, data since the last COMMIT will not be included in the backup. Is this true? I appreciate any guidance...Thanks Much! Mauro Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Segmentation Fault/Core dump
It will more easier if you post the sample.c. you can try compiling with -ggdb to debug Leif Johnston wrote: Any thoughts on what might be causing this error? I am runing both on cygwin under windows 2000 with 4.0.16 and using gcc to compile, linking using gcc sample.c -o sample.exe -I/usr/include/mysql -L/lib/mysql/opt/ -lmysqlclient -lmySQL -lz -lm I assume that something in the libraries may not be correct or that I need to check some other parameter but I am at a loss. Thanks Leif -- Aftab Jahan Subedar CEO/Software Engineer Subedar Technologies Subedar Baag Bibir Bagicha #1 North Jatrabari Dhaka 1204 Bangladesh http://www.SubedarTechnologies.com http://www.DhakaStockExchangeGame.com/ http://www.CEOBangladesh.com/ http://www.NYSEGame.com tel://+88027519050 EMail://[EMAIL PROTECTED] - Directly to my notebook -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subtracting date fields
try SELECT id,(TO_DAYS(firstdate)- TO_DAYS(postdate)) AS diff FROM calendar well you have to put the bigger date on the lhs. Kenneth Letendre wrote: Hello, I'm trying to get the difference (in days) between dates stored in two date fields. My query: SELECT id,(firstdate- postdate) AS diff FROM calendar This works fine if the two dates are in the same month, but not otherwise. MySQL appears to be treating the two dates as base-10 integers rather than dates. E.g.: 2004-01-07 (20,040,107) - 2003-12-31 (20,031,231) = 8876 How do I get MySQL to treat these date fields as date fields in this case? Thanks, Kenneth -- Aftab Jahan Subedar CEO/Software Engineer Subedar Technologies Subedar Baag Bibir Bagicha #1 North Jatrabari Dhaka 1204 Bangladesh http://www.SubedarTechnologies.com http://www.DhakaStockExchangeGame.com/ http://www.CEOBangladesh.com/ http://www.NYSEGame.com tel://+88027519050 EMail://[EMAIL PROTECTED] - Directly to my notebook -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance Problem
At 02:33 PM 1/30/2004, you wrote: Hi, I´m having a serious performance problem with my MySQL. The CPU is most of time with a load of 60%-95%. I´m using MySQL-4.0.14 on a FreeBSD-5.1 box. It´s a Celeron-2.0 Ghz - 512 Mb RAM - 40 Gb of Hard Disk. I think the main problem the queries struct wrongly build, but at this time I need to solve the main problem (slower pages) at first. And after that, change the site structure with more calm times. Does any good soul could give some tips to solve my problem? Upgrade my MySQL would solve part of the problem? What version should I use? My application has 71 tables + 261 indexes. It has 3 tables with almost 2.000.000 of records and the orthers have an average of 1.000 records. Any help would be appreciated. Thanks, Ronan Ronan, You haven't given us much to go on. Is this application running on a web server? 1) How many rows are your queries returning? (on average) and how long does it take? 2) How many connected users do you have? 3) Are these queries using multi-table joins? 4) Can you provide us with the table structure and query for the slow queries? 5) Can you provide us with a Show Status 6) Are you using MyISAM or InnoDb tables? The more information you can provide, the more accurate the answer will be. The MySQL manual has plenty of articles on optimization: http://www.mysql.com/doc/en/MySQL_Optimisation.html Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL optimisations for search engine
Hi I'm running a small search engine that indexes the web, but have been having trouble with optimising it to handle the load. There are two tables involved in searching - `pages`, which stores the ID, URL, title and crawl date, and `words` which has two rows - `word` and `page` where `word` is a word found on the page and `page` is the ID from the `pages` table. When we crawl an URL we rip all the words from the page and add them to the `words` table with the ID of the page. The query we use for searches is: SELECT COUNT(words.word) AS score, words.page AS id, pages.title, pages.url FROM words,pages WHERE pages.id=words.page AND words.word IN($words) GROUP BY words.page ORDER BY score DESC LIMIT 10 I've put the LIMIT 10 in there because it's been going slow as hell. not only that but it's still going rather slow since we're getting rather high load on the search engine at the moment. If anyone could suggest ways to make it run faster that'd be great, bearing in mind that: a) I can't change MySQL server parameters since the host won't allow it b) I'd rather not start crawling again with a different method - the words table has over 1,700,000 rows. Thanks Jasper Bryant-Greene Cabbage Promotions mailto:[EMAIL PROTECTED] [EMAIL PROTECTED] http://fatalnetwork.com/ http://fatalnetwork.com/ US: +1 (509) 691 3287 NZ: +64 (21) 232 3303
Visual/Wizard style software for creating complex queries/joins?
Greetings! Is anyone familiar with/can recommend any software capable of helping design complex (well for me anyway) queries/updates/inserts with joins? Thanks for any info! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
master InnoDB, slaves MyISAM?
If the master is InnoDB, must slaves also be InnoDB, or could they be MyISAM? (Assuming no foreign keys are set to CASCADE.) -John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
5.0 replication and stored procedure
Does replication in 5.0 support stored procedure? When I create a procedure on the master, it isn't replicated to the slaves. I then manually create the same procedure on the slaves. Then when I call the procedure on the master, all the slaves crashes. Is this not supported or am I doing something wrong? Bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Efficiently handling large amounts of data in mysql
At 01:20 PM 1/30/2004, you wrote: I routinely see people requesting information on how to efficiently handle *large* tables in mysql. Hopefully, the following will be helpful to those individuals. I have several tables that are 500M to 4B rows in size and require me to drop and re-create indices at regular intervals. Usually, I'm removing about 10% of the existing records and then adding about 15% new records back in. Doing this with indices turned on would take days if not weeks. With the proper sequence of operations, however, I can accomplish this in about 24 hours. I'm assuming you're using MyISAM tables and not InnoDb. Have you tried using Alter Table ... Disable Keys to disable the non-unique indexes before you do your deletes and inserts? When possible, we load data using mysqlimport. This is much faster than connecting to the server and executing a series of INSERT statements. You can get similar speeds using Load Data I have to ask, why are you deleting 10% of your rows and adding another 15% back in? Are you removing old data, data that is say 6 months old, and then importing this months data? If so, can't you just store one months data in separate tables and use Merge Table for your queries? At the end of each month, just drop the oldest table and create a new table for the current month's data. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Advise on High Availability configuration
I am wary of something so 'do it yourself'. Have you looked at ReHat's clustering solution? http://www.redhat.com/software/rha/cluster/ http://www.redhat.com/software/rha/cluster/manager/ I don't think it has any issue with InnoDB, key buffers, etc. I believe this solution works best for failover situations. Also if you have machine A doing A-type work and B doing B-type work, then if one goes down then the other will do both A-type and B-type work until the other machine comes back up. I think if both A-type work and B-type work are both MySQL, then you may have to use different ports for connections, use skip-name-resolve (and setting the name of error files, binlog files, etc) in my.cnf to eliminate issues with moving between machines. Also there is Veritas Cluster Server which has a MySQL module. Emic has load balancing as well as failover, offering these items: i) online backup capability ii) dynamic load balancing iii) fault management with fast failovers iv) high availability, and v) performance scalability with each added server node vi) does not require shared SCSI raid array http://www.emicnetworks.com/ http://www.emicnetworks.com/products/mysql.html Lastly, there is MySQL which bought a company to add clustering themselves: http://www.mysql.com/press/release_2003_30.html I have not used any, though I am evaluating all at the moment. -steve- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: *Forcing* use of TCP/IP by clients for localhost
--- [EMAIL PROTECTED] wrote --- In this case it might be easier for you to just modify the MySQL source to disable looking for the socket, and treat localhost as 127.0.0.1. --Pete Pete, I've considered that (and I'm going to try it on a development box). Unfortunately the target of this messy operation is a production system with a few hundred customers, and my employer would lynch me if I casually announced we can no longer use packaged (standard) MySQL binaries. :) I was hoping there was some tweak that could be made in, say, my.cnf, but I imagine that only affects the MySQL client binary and not *anything* build from the library. Thanks for the suggestion though; I will try it on a development box. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL optimisations for search engine
Jasper Bryant-Greene wrote: Hi I'm running a small search engine that indexes the web, but have been having trouble with optimising it to handle the load. There are two tables involved in searching - `pages`, which stores the ID, URL, title and crawl date, and `words` which has two rows - `word` and `page` where `word` is a word found on the page and `page` is the ID from the `pages` table. When we crawl an URL we rip all the words from the page and add them to the `words` table with the ID of the page. The query we use for searches is: SELECT COUNT(words.word) AS score, words.page AS id, pages.title, pages.url FROM words,pages WHERE pages.id=words.page AND words.word IN($words) GROUP BY words.page ORDER BY score DESC LIMIT 10 Not sure which columns are indexed, but the main problem is almost certainly words.word IN($words) - this will yield a set of rows from the 'words' table assuming that the string $words contains the query terms(s), this requires an expensive serial walk of the words table and an expensive string matching operation associated with each row - unless the IN() operator is a lot cleverer than I suspect. A better approach would, perhaps, be to parse the query into an array of words in the application and then construct suitable SQL along the lines of words.word = qword1 OR words.word = qword2 OR words.word = qword3 .. etc with as many or few terms as required. Index the words table on word for a further really big performance boost . I've put the LIMIT 10 in there because it's been going slow as hell. not only that but it's still going rather slow since we're getting rather high load on the search engine at the moment. If anyone could suggest ways to make it run faster that'd be great, bearing in mind that: a) I can't change MySQL server parameters since the host won't allow it b) I'd rather not start crawling again with a different method - the words table has over 1,700,000 rows. Thanks Jasper Bryant-Greene Cabbage Promotions mailto:[EMAIL PROTECTED] [EMAIL PROTECTED] http://fatalnetwork.com/ http://fatalnetwork.com/ US: +1 (509) 691 3287 NZ: +64 (21) 232 3303 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY may remove result rows
In the last episode (Jan 30), [EMAIL PROTECTED] said: Description: Instead of ordering rows, ORDER BY may remove rows ... mysql SELECT Id3 FROM T WHERE Id2=2 AND Val BETWEEN -30 AND 20 ORDER BY Id3; +-+ | Id3 | +-+ | 3 | +-+ 1 row in set (0.01 sec) Note: both indices Key2 and Key3 must be present for the bug to occur. I see this too. Even more fun: mysql SELECT Id3 FROM T WHERE Id2=2 AND Val BETWEEN -30 AND 20 ORDER BY Id3 DESC; Empty set (0.00 sec) -- 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: master InnoDB, slaves MyISAM?
At 17:14 -0500 1/30/04, Millaway, John wrote: If the master is InnoDB, must slaves also be InnoDB, or could they be MyISAM? (Assuming no foreign keys are set to CASCADE.) -John The slaves can be MyISAM. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Memory Problems on a G5/OSX/MySql4.0.17
On Jan 30, 2004, at 10:25 AM, Bruce Dembecki wrote: On Jan 28, 2004, at 12:01 PM, Bruce Dembecki wrote this wonderful stuff: So.. My tips for you: 1) Consider a switch to InnoDB, the performance hit was dramatic, and it's about SO much more than transactions (which we still don't do)! Consider it switched! as soon as I find the way to do so :) Are there any changes necessary to his code/queries to use innodb? No changes needed to code/queries, except probably the daily table optimize/repair can go away... As far as how to do it... SNIP SNIP Unfortunately, while we would love to have similar down times to that, we obviously have overloads on a daily basis, bringing the machine down, or bogging it out, for vast periods of the day... This is only recently (past 2-3 months) before that, it bogged out onlya couple time a day, and the slowness was, at least, functional- though, thats a very relative term, especially when dealing with users who may have shorter attention spans. How are you spanning your queries over multiple DB servers? and are all writes being done on one master server, or have you found a way to do 2 way replication? Currently we have only the main appserver apache2/php/thttpd, and are trying a secondary apache2/php server (dual PIII/850/2G) which doesn't appear to be handling the load well at all... We're looking, obviously, at adding several front end appservers, though we want to ensure the G5 will be able to handle it's job if there are 5-6+ frontends on it. Here's a few stats from today. If you see anything oddball, please let me know. I see the Qcache_free_memory is awfully large, I assume that means it's being tremendously wasted. The stats say that the Query Cache is WAY big, but I predicted that. As for the slowness, I can't address the application side, but MySQL shouldn't be the cause of problems with this sort of hardware and server load. On the rest of the stats my comments are the number of queries aren't that high, this server should be more than enough properly tuned Number of connections seems high, that's an Application thing, I can only assume it's efficient in how it manages it's database connections etc? My stats show 47K connections on an uptime of a week, yours is at 242K connections on less than a day. My server typically has a little less than 400 connections actually present at any time and if I have 10 threads running (including replication etc) I start to get nervous and look for a problem (and pagers go off if we get over 20 threads running for more than 30 seconds). The number of times your connections change database seems high too, there are as many change database commands as selects. Again this is likely an Application thing, but might be an area that could be improved. SNIP While we prepare for the innodb switch, I would like to see if there is some idea as to why we are seeing so many DB changes and connections. Apache is set to allow unlimited persistant connections (prefork mpm... worker is still apparently unable to be used with php) and php.ini is set to allow unlimited persistent connections. I am unsure, yet, what calls are made exactly in the client's code, but, I do know his mysql module uses pconnects. Is there a timeout setting I should be looking for or something else? I will include some more important settings to apache/php/sysctrl below. The G5 has no odd settings, though it is OSX not OSXS (10.3.2). I have felt that the app server is a primary cause of the slowdowns... every page has debug info which report the timing of every sql call on the page, if the debug bit is set in the url when requested, and it doesn't match the page load times all the time. The pages may load slowly (time to data starting to be sent to browser, not time browser takes to completely receive it and images, etc) but the sql info says the queries took no time to complete. The overall generation time of the page is also calculated/reported for every page, and show the same discrepancies at times. In general, however, the sql times added together form 85-95% of the total page generation time, and that time is roughly the correct amount of time it takes for the page to start rendering. But the larger select queries which form the big/heavy portions of the pages (item lists within their categories) can have just plain wild times, with seemingly no reason. The same query done over and over will sometimes jump from 1sec to 5, 30,60 sec. and then back down again. The mysql server may not appear heavily loaded at these times, either- they will appear mostly normal. While we have nice SystemStats on our front end boxes (load/process counts/bandwidth/file sizes, etc) we do not, yet, have anything like it on the G5. The same stats calls do not run ont he G5, or, their output is unrecognized. I am trying to find a good stats grapher for OSX, particularly a non-console app (I use SystemStats2 from
