Re: RSA encrypted strings - what data type?
On Tue, 2002-03-26 at 14:35, Christopher Thompson wrote: On Tuesday 26 March 2002 3:26 pm, Steven Hajducko wrote: I'm trying to take a string ( In this case, a password ), and encrypt it with RSA encryption via the perl module, Crypt::RSA. Later on, I want to be able to pull the encrypted string back out and decrypt it. Note that if you do this, you must hard-code the key somewhere (unless you are prompting the user for it) which means you are simply obfuscating the data, not actually encrypting it. The user has to specify this. There are 2 ( sets of ) keys. One goes to each department. The scripts require the specification of the key or the location of the key. One of three things happens when I do this however. 1) Everything works fine. 2) I cannot insert the encrypted string. 3) The encrypted string is inserted, but incorrectly and will not decrypt. I tried using a blob or medium blob for the datatype, but neither will work 100% of the time. Has anyone done this before with a Mysql db and if so, how? What sort of datatype do you use for the field that will contain the encrypted string? Did you use a different type of encryption method? ( Keep in mind that I cannot use a one way encryption scheme for this. ) You need a blob. Are you sure you are storing the data as binary? Alternatively, it is probably of fixed size and so you could just store the resulting chunk of data as a series of 8 bit values. That's what I figured. I'm probably trying to store the data incorrectly. I guess this is where I'll start looking. Note that if you do not need to be able to recover the password, just verify whether the password a user typed is correct, you CAN and, in fact, SHOULD use a one-way hash instead of an encryption. As I said, I can't use one way :) The passwords need to be recovered, not verified. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: RSA encrypted strings - what data type?
Well, I screwed around with it. This is what I got ( just in case anybody cares.. ) ( this is in perl, using the Crypt::RSA module.. ) $password = screwyou!; # encrypts the 'password'... $cyphertext = $rsa-encrypt(Message=$password, Key=$pub); # now convert the RSA encrypted key to binary. $bin_str = unpack(B*, pack(u*, $cyphertext)); # now reconvert it back to uuencode.. $recypher = unpack(u*,pack(B*, $bin_str)); # then decrypt it. $plaintext = $rsa-decrypt(Cyphertext=$recypher, Key=$priv); # prints screwyou! print $plaintext\n; Mysql has no trouble keeping the binary version. Thanks for the help guys, Steve On Tue, 2002-03-26 at 14:35, Christopher Thompson wrote: On Tuesday 26 March 2002 3:26 pm, Steven Hajducko wrote: I'm trying to take a string ( In this case, a password ), and encrypt it with RSA encryption via the perl module, Crypt::RSA. Later on, I want to be able to pull the encrypted string back out and decrypt it. Note that if you do this, you must hard-code the key somewhere (unless you are prompting the user for it) which means you are simply obfuscating the data, not actually encrypting it. One of three things happens when I do this however. 1) Everything works fine. 2) I cannot insert the encrypted string. 3) The encrypted string is inserted, but incorrectly and will not decrypt. I tried using a blob or medium blob for the datatype, but neither will work 100% of the time. Has anyone done this before with a Mysql db and if so, how? What sort of datatype do you use for the field that will contain the encrypted string? Did you use a different type of encryption method? ( Keep in mind that I cannot use a one way encryption scheme for this. ) You need a blob. Are you sure you are storing the data as binary? Alternatively, it is probably of fixed size and so you could just store the resulting chunk of data as a series of 8 bit values. Note that if you do not need to be able to recover the password, just verify whether the password a user typed is correct, you CAN and, in fact, SHOULD use a one-way hash instead of an encryption. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL oddity
Below is a table declaration in a database I just created. The oddity is the 2 fields, CL_FIRST_TS and CL_LAST_TS, are automatically marked as Required in the table's Validity Checks listing. This means that I have to add a value to both of them each time I insert a new record. They are involved in a legacy issue by old input devices or I would remove them. Nothing I do will change this behavior. Can anybody please explain to me why they are the only ones in the whole database to do this? The database consists of 90 tables with this one being number 9. I have tried this under MySQL v3.23 and v4.01 and get the same results. DROP TABLE IF EXISTS char_limits; CREATE TABLE CHAR_LIMITS ( CHAR_ID INTEGER NOT NULL, ENG_HIGH DOUBLE PRECISION, NOMINAL DOUBLE PRECISION, ENG_LOW DOUBLE PRECISION, CL_UPPER DOUBLE PRECISION, CL_CENTER DOUBLE PRECISION, CL_LOWER DOUBLE PRECISION, SCL_UPPER DOUBLE PRECISION, SCL_CENTER DOUBLE PRECISION, SCL_LOWER DOUBLE PRECISION, CL_CALC_METHOD VARCHAR(32), CL_FIRST_SG_TS TIMESTAMP NULL, CL_LAST_SG_TS TIMESTAMP NULL, REAS_HIGH DOUBLE PRECISION, REAS_LOW DOUBLE PRECISION, LIMITS_TS TIMESTAMP NOT NULL, TOTAL_LIMIT DOUBLE PRECISION, TOTAL_LIMIT_TYPE VARCHAR(16), CL_CALC_SG_SIZE INTEGER, CAUTION_PERCENT SMALLINT, CAUTION_TYPE VARCHAR(16), RANGE_UPPER DOUBLE PRECISION, RANGE_CENTER DOUBLE PRECISION, RANGE_LOWER DOUBLE PRECISION, SIGMA_UPPER DOUBLE PRECISION, SIGMA_CENTER DOUBLE PRECISION, SIGMA_LOWER DOUBLE PRECISION, ACC_DEFECT_PERCENT DOUBLE PRECISION, ACC_CONFID_PERCENT DOUBLE PRECISION, PRIMARY KEY (CHAR_ID)); CREATE UNIQUE INDEX CHARLIMITS_PK ON CHAR_LIMITS( CHAR_ID); /*/ Steven Gearhart Gearhart, Inc. Phone/Fax: 770.466.2898 Email: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: GRANT
If I understand correctly, you can achieve your goal in either of 2 ways. The first, is to copy sql.exe to one of your path directories (i.e. Windows or Windows\System) or include MySQL\bin in your DOS path statement. This will allow you to run mysql from a DOS prompt. The second method is to download a copy of MyCC from the MySQL.com website. This program allows you access in a Windows environment. Good Luck! -Original Message- From: Loretta [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 21, 2002 10:24 PM To: [EMAIL PROTECTED] Subject: GRANT Hi. I am the only user of MySQL in a Win98 environment. I am developing an ASP front end to access the database to add, update and delete records in the database for a Church. I am having problems with the connection to the database. I am able to connect to the database and print out the fields in a table with no difficulty. However, I am not able to add any records let alone delete or update records through the front end. I believe my difficulty lies in the need to: grant select, insert, delete, create, drop on database.* to person@localhost identified by 'password'; The problem is in order to do this as per the manual I have to get the shell prompt which I so far have not be able to do so that I can issue the following command: mysql --user = root mysql and then issue the grant command. Could someone please tell me how to get to the shell prompt in a windows environment? I recognize shell from my limited exposure to UNIX, but am not familiar with it in windows. I need to know which directory to go to do the required tasks. I have no problems getting into \mysql\bin to create databases and tables. Thank-you for any help. Loretta - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Can not run multiple command scripts
As usual, I am a newbie to MySQL. I have been studying the user manual and Paul DuBois' book, MySQL. I have tried to run the scripts for menagerie and samp_db on both a Windows 2000 machine and a Linux machine, both local and remote with version 3.23.49a. I have used the command line and several GUI interfaces with the same result. I am able to run a single command, however, if I try and run a script with two or more commands, I receive an error number 1064 - syntax error. This usually shows the error at line 1. What am I doing wrong? I am about to give up. /*/ Steven Gearhart Gearhart, Inc. Phone/Fax: 770.466.2898 Email: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysqldumpslow errors
Description: 1. Handling of string simplification confused, crashes perl! 2. Format of slow log had changed a while back, so now the timing information is broken in mysqldumpslow How-To-Repeat: uploaded to mysql secret: n54-slow.log.gz gunzip n54-slow.log.gz mysqldumpslow n54-slow.log Fix: 1. Change: s/'([^\\\']|\\.|\'\')+'/'S'/g; s/([^\\\]|\\.|\\)+/S/g; To: s/''/'S'/g; s//S/g; s/(\\')//g; s/(\\)//g; s/'[^']+'/'S'/g; s/[^]+/S/g; I'm not a perl wizzard, so there is likely a better way. But this actually works. :) 2. Change: s/^# Time: (\d+) Lock_time: (\d+) Rows_sent: (\d+).*\n//; To: s/^# Query_time: (\d+) Lock_time: (\d+) Rows_sent: (\d+).*\n//; Submitter-Id: submitter ID Originator:Steven Roussey Organization: Network54 MySQL support: [none anymore :( ] Synopsis: synopsis of the problem (one line) Severity: serious Priority: [ low | medium | high ] (one line) Category: mysql Class: sw-bug Release: mysql-3.23.47 (Official MySQL binary) Server: /usr/local/bin/mysqladmin Ver 8.23 Distrib 3.23.47, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.47-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 5 days 20 hours 57 min 33 sec Threads: 81 Questions: 693745099 Slow queries: 2238 Opens: 967352 Flush tables: 1 Open tables: 1510 Queries per second avg: 1367.112 Environment: machine, os, target, libraries (multiple lines) System: Linux trinity 2.4.16-0.13smp #1 SMP Fri Dec 14 05:10:37 EST 2001 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/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-98) Compilation info: CC='gcc' CFLAGS='-O3 -mpentium ' CXX='gcc' CXXFLAGS='-O3 -mpentium -felide-constructors' LDFLAGS='-static' LIBC: lrwxrwxrwx1 root root 13 11-16 13:02 /lib/libc.so.6 - libc-2.2.4.so -rwxr-xr-x1 root root 1283580 10-03 11:10 /lib/libc-2.2.4.so -rw-r--r--1 root root 27314604 10-03 10:52 /usr/lib/libc.a -rw-r--r--1 root root 178 10-03 10:52 /usr/lib/libc.so Configure command: ./configure --prefix=/usr/local/mysql '--with-comment=Official MySQL binary' --with-extra-charsets=complex --with-server-suffix= --enable-thread-safe-client --enable-assembler --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --disable-shared Sincerely, Steven Roussey http://Network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
BUGs in mysqldumpslow + fixes
(BTW, mysqldumpslow does not seem to be in the RPMs for v4.) mysqldumpslow (perl itself, I guess) dumps core and acts incorrectly when dealing with simplifying strings. Fix: Change: s/'([^\\\']|\\.|\'\')+'/'S'/g; s/([^\\\]|\\.|\\)+/S/g; To: s/''/'S'/g; s//S/g; s/(\\')//g; s/(\\)//g; s/'[^']+'/'S'/g; s/[^]+/S/g; I'm not a perl wizzard, so there is likely a better way. But this actually works. :) Also, there is a bug with getting the query and lock times. Change: s/^# Time: (\d+) Lock_time: (\d+) Rows_sent: (\d+).*\n//; To: s/^# Query_time: (\d+) Lock_time: (\d+) Rows_sent: (\d+).*\n//; Sincerely, Steven Roussey http://Network54.com/?pp=e sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mytop
deletes, and other) as is possible with the Com_ status variable in the later versions of MySQL (no documentation though..)? I've got a bunch of improvements planned now that I'm regularly using MySQL 4.x. Having all those counters is handy. :-) The counters are in 3.23.47 as well. I don't know how far back they go. One of these days, I need to finish off my graphing stuff and release that, too. Sound great! We store load average data for both our servers (mysql and web) in a mysql database that holds lots of fun stuff for us to watch (like page views, etc) that we graph. We sample every minute. Maybe we'll add qps to the sampling. I just didn't think of that before... Sincerely, Steven Roussey http://Network54.com/?pp=e -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 05, 2002 8:47 pm To: [EMAIL PROTECTED] Subject: Re: mytop On Tue, Mar 05, 2002 at 05:30:31PM -0800, Steven Roussey wrote: Jeremy, Mytop is simply awesome. Thanks! Thanks, glad ya like it. I recently updated to 0.9. I have a request: how about the qps mode show total qps as well as breaking them out (selects, inserts, updates, Too cool. At peak seconds we get 3000 queries per second. :) Nice! Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 26 days, processed 911,593,753 queries (396/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysql on Linux
Starting mysqld daemon with databases from /var/lib/mysql 020305 17:46:10 mysqld ended This is written by safe_mysqld. It seems that mysqld was never started successfully. I've had this issue before. Every time it was a permissions issue. When I execute mysqld -u root -p password I am presented with the default variables. Which are? Also, what is in /var/lib/mysql? Sincerely, Steven Roussey http://Network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: RH 7.2 connections problems w 16 web servers to 2 MySQL servers
We pay for MySQL support but haven't had much help from them. You could pay me, I need work... :) Seriously... We have a very high volume site (3 million page views a day) that's run on 16 Apache / PHP web servers 2 MySQL servers. We are using PHP with persistent connections. Each MySQL serves 8 web servers is supposed to act as a failover machine for the other group of 8 web servers. Nice. We have over 3 million page views a day also (for forums -- a recursive query application if there ever was one -- that is, lots of queries per page), though we use two hand-made dual Athlon machines (one apache/php, the other mysql). Its amazing what you can do with less than $10K. Once upon a time we had 4 web servers in front of 2 mysql servers. I've gone through this before. More below We get a mysterious MySQL lockup once a week at the same day time. No idea. Does it just pause for a bit? Are there any cronjobs? Can you get a mysql process list? Questions : - Is our configuration of 2 sets of 8 Apache/PHP web servers 1 MySQL servers just not a good idea ? Just will need some work... - Would we better off with FreeBSD ? No, that is not the issue. - Is there anyone doing any similar setups with lots of web servers a few MySQLs ? We had 4 per mysql server before, not as big but has the same issues. - Is there any way to get Apache / PHP to use fewer connections ? Yes. First a few questions: - How many Apache servers? 8. - Are you using HTTP Keep-Alives? - Are you using persistent connections? Yes. - What is connecting to mysql (PHP, an Apache module, Perl, JSP)? - What is Apache's http.conf value of MaxClients? - What is Apache's http.conf value of StartServers? - What is Apache's http.conf value of MaxRequestsPerChild? Multiply these three numbers: # of apache servers # of apache's MaxClients # of unique server/user/pass pairs The result is the ceiling on the number of connections needed for mysql. When one goes down, then you will multiply by two, since you have twice as many web servers in that situation. Note this number and work it into MySQL's documentation for memory requirements for the MySQL server. You may be shocked. If you are in an emergency, do this: A. Turn off apache's HTTP Keep-Alive B. Turn off persistent connections C. Up the number of cached threads for mysql That should immediately help. There is a more optimal solution, but the amount of work to properly tune will take at least another email and the answers to the above questions. I can write a chapter Apache/PHP/MySQL optimizations Steven Roussey Network54.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysql on Linux
When I execute mysqld -u root -p password I am presented with the default variables. Which are? How about showing what mysqld --user=root displays? Also, you see linux-bin.* files? That indicates that mysqld was running at some point (maybe nine times). Can you get the directory listings with ls -l ? Sincerely, Steven Roussey http://Network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: RH 7.2 connections problems w 16 web servers to 2 MySQL servers
Also, I forgot these questions: - What is Apache's http.conf value of MinSpareServers? - What is Apache's http.conf value of MaxSpareServers? Steven Roussey Network54.com sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mytop
Jeremy, Mytop is simply awesome. Thanks! I recently updated to 0.9. I have a request: how about the qps mode show total qps as well as breaking them out (selects, inserts, updates, deletes, and other) as is possible with the Com_ status variable in the later versions of MySQL (no documentation though..)? Too cool. At peak seconds we get 3000 queries per second. :) Sincerely, Steven Roussey http://Network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Many processes in SHOW PROCESSLIST;
PMFJI, but has anyone done any testing to see if persistent connections with MySQL and PHP is actually faster in practice? Without testing, I suspect that it is faster but that the gains are very small. If this was Oracle, it'd be a whole different story. The connection overhead in MySQL is minimal. With MySQL's thread_cache, the connection overhead is even lower. PHP's persistent connections can be good or bad. If you have a small site, low traffic, etc., it will help. If you have lots of traffic, then it can make more problems than it solves. PHP's implementation of persistent connections is flawed. A connection is persistent per process, not per server. Depending on you Apache variables, this can cause MySQL to have a _lot_ of useless threads running. And they take up memory. And that can slow things down and push MySQL to crash (setting a limit on MySQL connections solves this crash problem). Maybe with a new MySQL module for PHP in the works, this will get fixed. It needs to use shared memory so multiple apache processes can use the same connection pool. Sincerely, Steven Roussey http://Network54.com/?pp=e sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Any way to hint a particular index for a query?
Any way to hint a particular index for a query? From the manual (http://www.mysql.com/doc/J/O/JOIN.html) As of MySQL Version 3.23.12, you can give hints about which index MySQL should use when retrieving information from a table. This is useful if EXPLAIN shows that MySQL is using the wrong index. By specifying USE INDEX (key_list), you can tell MySQL to use only one of the specified indexes to find rows in the table. The alternative syntax IGNORE INDEX (key_list) can be used to tell MySQL to not use some particular index. USE/IGNORE KEY are synonyms for USE/IGNORE INDEX. Also, have you done an 'analyze table iv_alert'? Sincerely, Steven Roussey http://Network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql on Linux
When I execute mysqld -u root -p password I am presented with the default variables. The manual says to use safe_mysqld to properly start mysqld. Also, --user=root is clearer for that option. And, what are you doing with -p password in starting the daemon? This is not an option, it is an error. http://www.mysql.com/doc/s/a/safe_mysqld.html Sincerely, Steven Roussey http://Network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysqlgui help
Dear sirs I could not connect to mysql database when using mysqlgui. The error message is access denied for the user: [EMAIL PROTECTED] (using password: yes). If I use the mysql -u root -ppassword command, I can connect to the database. Can you kindly tell me something about it?. I am running mysql on linux box and the mysqlgui is on the same box. The gui version is 1.7.5. I look forward to hearing from you. Thanks, steven - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysqldumpslow
I'm trying to find what causes mysqldumpslow to crash for us every time the log is anything but trivial. The following does not crash it, but it does give incorrect results: # User@Host: apache[apache] @ morpheus.e [1.1.1.1] # Query_time: 8 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 replace into forums_posts_new_1 (f,m,p,r,d,dm,a,au,l,i,u,aut,e,t,m,h ) values (7451,1014880635,1014815708,1014815708,'no','no','yes','no',0,3538664738 ,'af34b901b897b5ce59c7bf7df3fb620c','Ex','','Re: HTV','Dont worry BR BR Chow from Japan.',''); Gives Reading mysql slow query log from test.log Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), apache[apache]@morpheus.e # Query_time: N Lock_time: N Rows_sent: N Rows_examined: N replace into forums_posts_new_1 (f,m,p,r,d,dm,a,au,l,i,u,aut,e,t,m,h ) values (N,N,N,N,'S','S','S','S',N,N,'S','S',''S'Re: HTV'S'Dont worry BR BR Chow from Japan.'S'') I think it is the '' at the two locations... Sincerely, Steven Roussey http://Network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Linux optimizations for MySQL
I assume you are talking about bdflush only at 100% (I'm actually trying 90%). Thing is, all our tables are constantly changing. Unless the server crashes just after a flush tables command, the tables are likely to get corrupt anyhow. The data altering flow of queries is over 1000/s at the moment. Right now I want every last ounce of speed while our external RAID array is being rebuilt. 2 disk SCSI RAID 0 is not enough. 4 disk SCSI RAID 0 or 4 disk SCSI 0+1 is much better. Sincerely, Steven Roussey http://Network54.com/?pp=e -Original Message- From: David Turner [mailto:[EMAIL PROTECTED]] If you do that don't you run a greater risk of corruption of the datafiles if the host unexpectedly goes down? Dave On Mon, Feb 25, 2002 at 01:07:06PM -0800, Steven Roussey wrote: I was reading an article on speeding up Oracle on Linux(1) and thought their two optimizations for Linux would work for MySQL as well. Would: chattr -R +A mysql_datadir_path help? Also, what about editing /ect/sysctl.cong file and add an entry to improve filesystem performance, as follows: vm.bdflush = 100 1200 128 512 15 5000 500 1884 2 Would this work as well in 2.4.16+ kernels (tweaking the numbers accordingly for your system)? Sincerely, Steven Roussey http://Network54.com/?pp=e (1) http://www.linuxjournal.com/article.php?sid=5840 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe- [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Linux optimizations for MySQL
I've never heard of this in a recent kernel/e2fsprogs. Nor have I experienced it (thankfully). Do you any further information? Sincerely, Steven Roussey http://Network54.com/?pp=e -Original Message- From: David Turner [mailto:[EMAIL PROTECTED]] Sent: Monday, February 25, 2002 4:01 pm To: [EMAIL PROTECTED] Subject: Re: Linux optimizations for MySQL I thought there was a problem with the datafile corruption if the cache was lost when you chattr a file. That the file would become corrupt. Dave - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MyISAM RAID Tables in the MySQL-MAX Binaries?
Are RAID tables supposed to be enabled in the -MAX binaries? They appear not to be (in 4.0.1), and that surprises me a bit. I thought RAID tables were retired in MySQL 4. It was only a split for the data files anyhow, not the index files, so it was incomplete at best for getting around the lack of large file support in some (now old) OSes. For speed, RAID is best handled by the OS or hardware. Sincerely, Steven Roussey http://Network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Re: recover lost index
Sincerely, Steven Roussey http://Network54.com/?pp=e -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, February 25, 2002 11:21 pm To: [EMAIL PROTECTED] Subject: Re: Re: recover lost index To: [EMAIL PROTECTED] Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: Does anyone know how / if it's possible to recover a lost .MYI file if I still have the .MYD and .frm files? It seems like it should be possible but I haven't found any documentation on it yet. This is essentially what BACKUP TABLE does. Try RESTORE TABLE. First move the files somewhere else and give RESTORE TABLE the path. It should copy them to the datadir and create the MYI file. Sincerely, Steven Roussey http://Network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql and onboard ide raid
As for a filesystem. I wouldn't recommend ext2 or ext3 at all. I would _highly_ recommend a journaling filesystem such as Reiserfs (http://www.namesys.com) or XFS (http://oss.sgi.com). I've used reiserfs for a long time, but XFS is growing on me. It seems to be more stable in latest 2.4 kernels. I don't know the reason for this. I'd refer people to http://www.linuxjournal.com//article.php?sid=5840 Although that article talks about Oracle, we also found ext3 for mysql data disks to be the best choice from our own experience. Reiserfs jams with lots of small files though; useful when compiling (mysql source for example) or for a webserver. Actually, I like it for everything but the mysql data dir. Is it better to take Raid level 1 or 0 ? RAID 0 or RAID 0+1 with a lot of disks makes a huge speed difference under load. SQL server speed (trx/s) can correlate to the rotational speed of the disks once the server is disk bound. RAID 0 lets you increase the effective speed of the virtual drive by summing the speed of the disks (roughly). I have no experience with IDE RAID though. SCSI still outperforms and likely always will. Sincerely, Steven Roussey http://Network54.com/?pp=e sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: load formula
This should probably be on the main MySQL list. I think I'd use the one minute load average from the servers (assuming they were all on the same OS/version), and cache it for a minute (you don't want your pings to add to the load). That is, if you don't need 1minute resolution. The load is a good indicator since it will take into account the complexity of the queries running. With more resolution, nothing is very good, but almost anything will likely do, AFAIK. BTW: Have you looked at: http://www.firstworks.com/sqlrelay.html SQL Relay is a persistent database connection pooling, proxying and load balancing system for Unix and Linux supporting ODBC, Oracle, MySQL, mSQL, PostgreSQL, Sybase, MS SQL Server, IBM DB2, Interbase, Lago and SQLite with C, C++, Perl, Perl-DBD, Python, Python-DB, Zope, PHP, Ruby and Java APIs, command line clients, a GUI configuration tool and extensive documentation. The APIs support advanced database operations such as bind variables, multi-row fetches, client side result set caching and suspended transactions. It is ideal for speeding up database-driven web-based applications, accessing databases from unsupported platforms, migrating between databases, distributing access to replicated databases and throttling database access. Sincerely, Steven Roussey http://Network54.com/?pp=e -Original Message- From: Colin Faber [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 4:13 pm To: [EMAIL PROTECTED] Subject: Re: load formula Well; The reason for the question is; I'm thinking of writing a load balancing system in C which will allow a central connection point; pool a list of mysql servers, Then dump the incoming request off to the server with the least amount of load, I know replication plans to solve this how ever, I haven't seen anything aimed at having a centralized connection point for the end user applications to connect to. Much like a BigIP system with load measurement for mysql. Steven Roussey wrote: Does anyone here have any suggestions for writing up a `load formula' to test on a numerical bases how much load any given mysql server is under? ie, queries per second / threads = load ... as a simple example. This is highly application (and configuration) specific. If both servers are doing identical work, then anything like the above (or better, find the number of queries in the last second) would work. The number has no meaning. Depending on HTTP Keep-Alive and your setup for connection pooling, the threads number can either be constant or the most important number. If we assumed the you are talking about a web server and not doing connection pooling (which is not such a great thing to do in PHP, for example) then the number of threads will be all you need. It will say how many queries are currently being processed on each server. BTW -- why not check the actual 1 minute load average on the servers? Sincerely, Steven Roussey http://Network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] -- Colin Faber (303) 859-1491 fpsn.net, Inc. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Distributed Fulltext?
[comparisons to Google...] While any speed up with a full table fulltext search would be helpful and useful, there are instances where the search is intersected with another column and the problem of search is therefore more complex but also leads to potential optimizations. In our case we rarely do searches that use just the FTS index. More commonly, we do searches based on other columns and a FTS indexed text column. The WHERE clause restraints on the non-text column usually eliminate 99% of the rows. Fulltext indexes can not be combined with numeric columns in a single composite index, so as far as I can tell, MySQL performs the FTS then filters the results to meet the WHERE conditions. I believe the required amount of the index to be paged into main memory is much larger than might otherwise be needed (since the indexes are larger than available memory, and there a zillion other tables accessed simultaneously, this tends to effect all queries). So besides the obvious desire to lower the size of indexes, it would be beneficial if a combined index could lower the number of matching rows and the amount of the index needed to be in memory. I actually find it odd for people to not be using secondary columns in a fulltext search, other than to do something like Google (which does not lend itself to using SQL in the first place. Of course, I have a myopic view stemming from my own requirements and experience. All that said, I'm glad to have the FTS we have. Thanks Sergei for such a wonderful improvement to LIKE '%word%'! Sincerely, Steven Roussey http://Network54.com/?pp=e PS - is FTS search and update O(n^2)? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Request for help in testing new replication code in 4.0.2
That would be great. Thanks! Sincerely, Steven Roussey http://Network54.com/?pp=e -Original Message- From: Brian P. Austin [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 12, 2002 10:45 pm Subject: RE: Request for help in testing new replication code in 4.0.2 I have finished a script that takes the bit keeper sources and makes MySQL-4.0.2 RPMS for Redhat 6.x and apparently 7.X as well. I can put these up on our site for download if you want. They might get others trying the code out. I use the spec file included with the source code. I'll put it up if it's something you might find useful. -Original Message- From: Steven Roussey [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 12, 2002 12:41 PM To: 'Sasha Pachev' Cc: 'Mysql' Subject: Re: Request for help in testing new replication code in 4.0.2 * If you want to do it this very moment, read the instructions at http://www.mysql.com/doc/I/n/Installing_source_tree.html and install a pre-release 4.0.2 MySQL on your test server. You may also wait until 4.0.2 is released, but in that case, replication in 4.0.2 may have a bug you could have caught on your system and it will not be fixed until 4.0.3. Is that possible to provide a binary snapshot of the pre-release? This can be done with some simple shell scripting, I think. I always found the one I build myself is about 30-50% slower then the official binary. I think that's related to the debian patched glibc-2.95 I am using. Hi, I have to agree with this. I don't want to have to factor in RedHat's 7.2 compiler oddities (which one to use?). Some software projects have a machine to do regular builds on a schedule. This would be ideal. You would find more people testing it. You might just do it in special cases like this one. Testing the new code is relatively easy, and does not require risking the stability of your application by running alpha code. Yes and no. Taking down a server and copying gigs of data can take down a service for a long time (moving gigs of data just is never fast, and the machine I'm going to use has lots of power but no RAID). I don't have time to download and install gcc, bitkeeper, autoconf 2.13, automake 1.4, libtool, m4, and mysql today, but I'll try and get to it this week. Taking down the server will have to wait until maintenance windows over the weekend anyhow. Our test plan for MySQL 4 is as follows: 1. Serialized writes (i.e. replication slave) for two weeks (or about 1 billion insert/update/delete type queries) with no crashes. 2. Everything. We will have the app send duplicates of all queries to the mysql 4 server, but will automatically halt when mysql 4 crashes. We will keep trying this until mysql doesn't crash for a week. 3. Check that the two servers hold the same information and return the same results from queries. With the type and volume of queries that we have, mysql alpha versions either fail within minutes/hours/days or they don't fail at all. We certainly are not the biggest user of mysql, but historically, we have been able to push it hard enough to crash. Not the stable version though. Thankfully, that takes a licking and keeps on ticking. :) Eventually (spring/summer), we will move the main server to v4 and keep the slave around as a live backup. I've been wanting such a slave for a long time. Stopping the slave to make a backup would be great. Not to mention faster fulltext and order by DESC. Sincerely, Steven Roussey http://Network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Distributed Fulltext?
Mike Wexler wrote: That may be what you want. But I want a search engine. For my database. I want to be able to do search of the million records of inventory in our database and find the items with certain keywords, that are currently available for sale, in the venue the user is shopping in and within the price range the user specifies. This is a combination of database and fulltext search that I think is exactly in MySQL's marketplace. Personally I use the term 'search engine' to mean Google-like text-only search. I don't have a term for the above, but FTS seems fine. I agree that MySQL users will tend to want an index over several columns including one that is text (FTS). As for practical advice now, I'll tell you what we did: 1. Got better hardware. Athlons, DDR memory, RAID: more is better. :) 2. We split the FTS tables into 10 tables. Since we do not need to search the text without some other constraint (yet -- I'd like FTS to work with merge tables in the few cases where we did), we know which table to use. Your mileage may vary. This helps for several reasons: less index to pull into memory for the search, and FTS does not seem to be O(n) but rather O(n^2) though I can't be sure. 3. Replicated the table to do FTS on to another computer. All it does is FTS. We made our own routine to replicate the table (actually 10 tables) since our text is compressed on the main server (smaller = less disk access = faster) and the indexes are different (obviously). In this case, it can hold a lot of the index in memory -- big difference. Also, a long search does not lock the tables. This is worth in and of itself. Even if you have this copy of the table on the same server. De-normalization can produce huge speed increases depending on what and how you do things. 4. If you do #2 and #3 you'll notice that you can have x (10 for us) number of servers partition the FTS. We don't actually do this, but we could and therefore get 'Distributed Fulltext' -- the title of this thread!!! Number 1 and 3 should work for everyone, I think. Only if your app can partition, can you do #2 and #4. Sincerely, Steven Roussey http://Network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: auto_increment value recycled/reset in BDB table? [3.23.46]
At 11/02/2002 05:21, Paul DuBois wrote: At 4:10 +0700 2/11/02, Steven Haryanto wrote: i believe since 3.23.39 it should not be? BDB and MyISAM tables have different properties with respect to AUTO_INCREMENT behavior. i see, so this is an undocumented feature (i haven't seen this in http://www.mysql.com/doc/B/D/BDB_characteristics.html , for example). any idea how i can get non-recycling auto_increment field with bdb table (aside from having to maintain another dummy myisam table to generate non-recycling sequence for it)? -- sh - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
auto_increment value recycled/reset in BDB table? [3.23.46]
i believe since 3.23.39 it should not be? mysql create table t1_b(i int unsigned primary key auto_increment, j int) type=bdb; mysql create table t1_m(i int unsigned primary key auto_increment, j int) type=myisam; mysql insert into t1_bdb(j)values(0); mysql insert into t1_bdb(j)values(0); mysql insert into t1_bdb(j)values(0); mysql select * from t1_bdb; +---+--+ | i | j| +---+--+ | 1 |0 | | 2 |0 | | 3 |0 | +---+--+ 3 rows in set (0.00 sec) mysql delete from t1_bdb where i=3; mysql insert into t1_bdb(j)values(0); mysql select * from t1_bdb; +---+--+ | i | j| +---+--+ | 1 |0 | | 2 |0 | | 3 |0 | +---+--+ 3 rows in set (0.00 sec) mysql insert into t1_myisam(j)values(0); mysql insert into t1_myisam(j)values(0); mysql insert into t1_myisam(j)values(0); mysql select * from t1_myisam; +---+--+ | i | j| +---+--+ | 1 |0 | | 2 |0 | | 3 |0 | +---+--+ 3 rows in set (0.00 sec) mysql delete from t1_myisam where i=3; mysql insert into t1_myisam(j)values(0); mysql select * from t1_myisam; +---+--+ | i | j| +---+--+ | 1 |0 | | 2 |0 | | 4 |0 | +---+--+ -- sh - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Request for help in testing new replication code in 4.0.2
* If you want to do it this very moment, read the instructions at http://www.mysql.com/doc/I/n/Installing_source_tree.html and install a pre-release 4.0.2 MySQL on your test server. You may also wait until 4.0.2 is released, but in that case, replication in 4.0.2 may have a bug you could have caught on your system and it will not be fixed until 4.0.3. Is that possible to provide a binary snapshot of the pre-release? This can be done with some simple shell scripting, I think. I always found the one I build myself is about 30-50% slower then the official binary. I think that's related to the debian patched glibc-2.95 I am using. Hi, I have to agree with this. I don't want to have to factor in RedHat's 7.2 compiler oddities (which one to use?). Some software projects have a machine to do regular builds on a schedule. This would be ideal. You would find more people testing it. You might just do it in special cases like this one. Testing the new code is relatively easy, and does not require risking the stability of your application by running alpha code. Yes and no. Taking down a server and copying gigs of data can take down a service for a long time (moving gigs of data just is never fast, and the machine I'm going to use has lots of power but no RAID). I don't have time to download and install gcc, bitkeeper, autoconf 2.13, automake 1.4, libtool, m4, and mysql today, but I'll try and get to it this week. Taking down the server will have to wait until maintenance windows over the weekend anyhow. Our test plan for MySQL 4 is as follows: 1. Serialized writes (i.e. replication slave) for two weeks (or about 1 billion insert/update/delete type queries) with no crashes. 2. Everything. We will have the app send duplicates of all queries to the mysql 4 server, but will automatically halt when mysql 4 crashes. We will keep trying this until mysql doesn't crash for a week. 3. Check that the two servers hold the same information and return the same results from queries. With the type and volume of queries that we have, mysql alpha versions either fail within minutes/hours/days or they don't fail at all. We certainly are not the biggest user of mysql, but historically, we have been able to push it hard enough to crash. Not the stable version though. Thankfully, that takes a licking and keeps on ticking. :) Eventually (spring/summer), we will move the main server to v4 and keep the slave around as a live backup. I've been wanting such a slave for a long time. Stopping the slave to make a backup would be great. Not to mention faster fulltext and order by DESC. Sincerely, Steven Roussey http://Network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
auto_increment value recycled/reset in BDB table? [3.23.46]
i believe since 3.23.39 it should not be? mysql create table t1_b(i int unsigned primary key auto_increment, j int) type=bdb; mysql create table t1_m(i int unsigned primary key auto_increment, j int) type=myisam; mysql insert into t1_bdb(j)values(0); mysql insert into t1_bdb(j)values(0); mysql insert into t1_bdb(j)values(0); mysql select * from t1_bdb; +---+--+ | i | j| +---+--+ | 1 |0 | | 2 |0 | | 3 |0 | +---+--+ 3 rows in set (0.00 sec) mysql delete from t1_bdb where i=3; mysql insert into t1_bdb(j)values(0); mysql select * from t1_bdb; +---+--+ | i | j| +---+--+ | 1 |0 | | 2 |0 | | 3 |0 | +---+--+ 3 rows in set (0.00 sec) mysql insert into t1_myisam(j)values(0); mysql insert into t1_myisam(j)values(0); mysql insert into t1_myisam(j)values(0); mysql select * from t1_myisam; +---+--+ | i | j| +---+--+ | 1 |0 | | 2 |0 | | 3 |0 | +---+--+ 3 rows in set (0.00 sec) mysql delete from t1_myisam where i=3; mysql insert into t1_myisam(j)values(0); mysql select * from t1_myisam; +---+--+ | i | j| +---+--+ | 1 |0 | | 2 |0 | | 4 |0 | +---+--+ -- sh - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: auto_increment value recycled/reset in BDB table? [3.23.46]
At 11/02/2002 05:21, Paul DuBois wrote: At 4:10 +0700 2/11/02, Steven Haryanto wrote: i believe since 3.23.39 it should not be? BDB and MyISAM tables have different properties with respect to AUTO_INCREMENT behavior. i see, so this is an undocumented feature (i haven't seen this in http://www.mysql.com/doc/B/D/BDB_characteristics.html , for example). any idea how i can get non-recycling auto_increment field with bdb table (aside from having to maintain another dummy myisam table to generate non-recycling sequence for it)? -- sh - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Some manual suggestions
Some manual suggestions: http://www.mysql.com/doc/T/O/TODO_MySQL_4.0.html _We have now in the final stages one the development of the MySQL Server 4.0._ We are now in the final stages of development of MySQL Server 4.0 http://www.mysql.com/doc/T/O/TODO_MySQL_4.1.html Add record_in_range() method to MERGE tables to be able to choose the right index when there is many to choose from. We should also extend the info interface to get the key distribution for each index, _of_ analyze is run on all sub tables. _of_ should be if Sincerely, Steven Roussey http://Network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
print database schema
Hello I was wondering if anyone knew of a program that would allow you to print out a complete database. I have seen it on a PostGre DB, so I am sure MySQL should have something... cheers! Regards, ___ Steven Wren [EMAIL PROTECTED] http://www.server101.com Webhosting and E-commerce Solutions Phone : AU (07)38766 101 US 877 7762 101 Fax : +61 7 38763 101 The world is not only stranger than we suppose, it is stranger than we can suppose. -J.B.S. Haldane - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: print database schema
Hello more of a visual tool. The program I did see had views of all the tables as if you had done a describe table_name for each table, then had lines linking the relationships between tables (similar to what you can view in Access , even though i hate to admit I have used it :) would just be good for management and seeing where you can improve indexes etc... thanks. Regards, ___ Steven Wren [EMAIL PROTECTED] http://www.server101.com Webhosting and E-commerce Solutions Phone : AU (07)38766 101 US 877 7762 101 Fax : +61 7 38763 101 The world is not only stranger than we suppose, it is stranger than we can suppose. -J.B.S. Haldane On Tue, 29 Jan 2002, Paul DuBois wrote: At 14:42 +1000 1/29/02, Steven Wren wrote: Hello I was wondering if anyone knew of a program that would allow you to print out a complete database. I have seen it on a PostGre DB, so I am sure MySQL should have something... You mean mysqldump --no-data db_name ? cheers! Regards, ___ Steven Wren [EMAIL PROTECTED] http://www.server101.com Webhosting and E-commerce Solutions Phone : AU (07)38766 101 US 877 7762 101 Fax : +61 7 38763 101 The world is not only stranger than we suppose, it is stranger than we can suppose. -J.B.S. Haldane - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Efficiently storing md5
Does anyone have a best practices for efficiently storing md5 hash values in MySQL? Since it is a 32 character string of hex numbers, I originally stored them in a char(32) binary column. But that is wasted space (by a factor of 2). And of course, these things add up, both in data files and indexes. Md5 hash-- 16 bytes. char(32) binary -- 32 bytes. BIGINT -- 8 bytes My thought right now is to convert the md5 hash into two BIGINT numbers and visa-versa. High BIGINT = conv(left(md5,16),16,10) Low BIGINT = conv(right(md5,16),16,10) And reversing: Md5 = concat(lpad(conv(high,10,16),16,'0'),lpad(conv(low,10,16),16,'0')) I suppose there is some point (in terms of the number of rows) where storing more efficiently outweighs the conversion functions, and I assume that I'm past that point. Is there a better way to convert these? Just a note: there is no 128-bit integer type and CONV() only works with up to 64-bit 8-byte numbers anyway. Sincerely, Steven Roussey http://Network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Slow updates with two keys in where.
Is this just something we have to live with or does MySQL 4 handle this better? Something to live with. Someday, I'm sure MySQL will optimize this case. Conceptually its not that hard. You split it into multiple queries that track an index. If it is a select, you also UNION the results back together. However, conceptually easy does not mean trivial. That is a lot of work for the MySQL team to do and not very high on their list. Better for you to change: update forum set approved='N' where id=644122 or thread=644122 into update forum set approved='N' where thread=644122 update forum set approved='N' where id=644122 Of course, as a (sort-of) competitor, I probably shouldn't say that. BTW - out of curiosity, what sort of through-put does Phorum have? We have two machines, one web and one mysql, that handle 100m pv/m, which ought to hold to 150m/m before we need more hardware. Sincerely, Steven Roussey http://Network54.com/?pp=e In honor of Jeremy: MySQL 3.23.47-log: up 5 days, processed 595,464,211 queries (1,350/sec. avg) :) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
compiling and/or running 64-bit MySQL on Solaris 8/sparc?
Is it possible to compile and run MySQL as a 64-bit program on Solaris 8? If so, are there precompiled bits available? The primary reason for doing this is to determine if a 64-bit version would allow the database to be larger than 4-gig. I have been told by another member of our team MySQL has such a limit, and he suggested a 64-bit build might overcome it. Is this correct? I've been attempting to compile MySQL 3.23.47 and 4.0.1 as a 64-bit program using gcc 3.0.3. To build the GCC 3.0.3 with 64 bit capabilities I believe I followed the example found here faithfully: http://www.well.com/~jax/rcfb/solaris_tips/build_gcc_3.0_64bit.html http://www.well.com/~jax/rcfb/solaris_tips/build_gcc_3.0_64bit.html When I attempt to build the MySQL I get an argument from the ./configure script telling me checking return type of sprintf... configure: error: can not run test program while cross compiling. I'm not an expert in the area of gcc and the gnu tools, so I may well have done something wrong somewhere along the line. I'd like to know if I am attempting something that can be accomplished. Has anybody got this working? Here is the complete dump of the configure script: bash-2.03# ./configure --prefix=/opt/ --host=sparcv9-sun-solaris2 loading cache ./config.cache checking host system type... sparcv9-sun-solaris2 checking target system type... sparcv9-sun-solaris2 checking build system type... sparcv9-sun-solaris2 checking for a BSD compatible install... (cached) /usr/local/bin/install -c checking whether build environment is sane... yes checking whether make sets ${MAKE}... (cached) yes checking for working aclocal... found checking for working autoconf... found checking for working automake... found checking for working autoheader... found checking for working makeinfo... missing checking whether to enable maintainer-specific portions of Makefiles... no checking whether build environment is sane... yes checking whether make sets ${MAKE}... (cached) yes checking for gawk... (cached) nawk checking for gcc... (cached) gcc checking whether the C compiler (gcc ) works... yes checking whether the C compiler (gcc ) is a cross-compiler... no checking whether we are using GNU C... (cached) yes checking whether gcc accepts -g... (cached) yes checking for c++... (cached) c++ checking whether the C++ compiler (c++ ) works... yes checking whether the C++ compiler (c++ ) is a cross-compiler... yes checking whether we are using GNU C++... (cached) yes checking whether c++ accepts -g... (cached) yes checking how to run the C preprocessor... (cached) gcc -E checking for ranlib... (cached) ranlib checking for ld used by GCC... (cached) /usr/ccs/bin/ld checking if the linker (/usr/ccs/bin/ld) is GNU ld... (cached) no checking for BSD-compatible nm... (cached) /usr/local/bin/nm -B checking whether ln -s works... (cached) yes loading cache ./config.cache within ltconfig checking for object suffix... o checking for executable suffix... (cached) no checking for gcc option to produce PIC... -fPIC checking if gcc PIC flag -fPIC works... yes checking if gcc supports -c -o file.o... yes checking if gcc supports -c -o file.lo... yes checking if gcc supports -fno-rtti -fno-exceptions ... yes checking if gcc static flag -static works... none checking if the linker (/usr/ccs/bin/ld) is GNU ld... no checking whether the linker (/usr/ccs/bin/ld) supports shared libraries... yes checking command to parse /usr/local/bin/nm -B output... ok checking how to hardcode library paths into programs... immediate checking for /usr/ccs/bin/ld option to reload object files... -r checking dynamic linker characteristics... solaris2 ld.so checking if libtool supports shared libraries... yes checking whether to build shared libraries... yes checking whether to build static libraries... yes checking for objdir... .libs creating libtool loading cache ./config.cache checking for a BSD compatible install... /usr/local/bin/install -c checking for bison... bison -y checking for pdftex... no checking return type of sprintf... configure: error: can not run test program while cross compiling bash-2.03# - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How do I verify it's a 64-bit build
Sorry about the previous, seemingly stupid question asking for clarification on the LDFLAGS. I was not able to hit http://home.earthlink.net/~jaymzh666/mysql.html , so I did not realize that answer was there. I now seem to have a successfully built MySQL 3.23.47 using what I believe is a 64-bit capable gcc-3.0.3. How might I verify that I indeed have a 64-bit MySQL? TIA, Steven - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Re: compiling and/or running 64-bit MySQL on Solaris 8/sparc?
-Original Message- From: Markus Lervik [mailto:[EMAIL PROTECTED]] Sent: Friday, January 18, 2002 8:08 AM To: [EMAIL PROTECTED] Subject:Fwd: Re: compiling and/or running 64-bit MySQL on Solaris 8/sparc? On Friday 18 January 2002 13:57, you wrote: [BIG snip] checking return type of sprintf... configure: error: can not run test program while cross compiling bash-2.03# [/snip] Try adding export LDFLAGS='-R/usr/local/lib' or export LDFLAGS='-R/opt/sfw/lib' before ./configure. Cheers, Markus Markus, Thank you for the reply. I'm not sure of the reason for the LDFLAGS variable. What library files should I be pointing to with it? I put my gcc in /opt/gcc-3.0.3-64. Will this influence the proper choice of value for LDFLAGS? Steven - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Testing MySQL 4.0 ; detaching queries
You could just exec() the page again, with a parameter telling it to use the test server, like so: I like this general idea, though the above method would take a little more work since I would have to pass the environment variables and apache variables (GET and POST, etc) to exec and I don't know how. However, I can try and setup a test web server on the test SQL server and have a auto_postpend file that sets up all these variables (cookies, post vars, etc) and sends them off using fsockopen, etc., as a HTTP request to the other server. If I setup the other server to ignore hangups, then I can send the request and quit. Thanks for the idea! Sincerely, Steven Roussey http://Network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Some amazing query numbers from MySQL 4.0
Jeremy Cole wrote: === Server version: 4.0.0-alpha-log Threads: 408 Questions: 183163443 Slow queries: 4009 Opens: 43522 Flush tables: 1 Open tables: 239 Queries per second avg: 222.828 === Some free configuration advice: * You should increase your ``table_cache'' variable, ``Opens'' is much higher than ``Open_tables'' indicating that MySQL is having to close other tables in order to open some for new clients. Its always good to recheck one's configuration. I took a look at our stats and noticed our opens were much higher than our open tables. But our table cache is set high enough (2500). Server version 3.23.44-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 8 hours 15 min 41 sec Threads: 63 Questions: 33231571 Slow queries: 52 Opens: 48114 Flush tables: 1 Open tables: 599 Queries per second avg: 1117.366 I wonder if opens counts temp tables (explicitly or implicitly created). Anyhow, in the above config, the high count of Threads may lead to an unstable machine particularly on Linux with large per thread caches (there is an equation in the MySQL manual somewhere for this). I'll bet he is using PHP and Apache and has persistent connection on PHP and Keep-Alive on Apache. The MySQL module for PHP is the biggest barrier to scaling in this situation. All database modules are bad in PHP, everyone knows it, very few have the time, resources, or money to fix things. [Case in point: if I had the money, I'd make the time...] There is SQL relay (http://www.firstworks.com/sqlrelay.html) though I have yet to try it. Has anyone? A quick (partial) fix is to use MM inside PHP so persistent connections act as expected (i.e. shared between processes). Even easier (sloppier) fix is to not use Keep-Alive, use persistent connections, but have the timeout inside mysql be a couple of seconds. That gets a better match between active http processes and mysql threads with the least bit of effort. Sincerely, Steven Roussey http://Network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
BINLOG and temp tables
Hi! Is there a way to create a temp table and _not_ have it logged to the binlog? We use temp tables only to increase the speed of certain types of selects (we have discussed the type of select that causes this problem on this list already), and don't want them rerun when we might have to to a restore. Thanks, -steve- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Looking for a Data entry program for mysql...
Try mysqlfront (www.mysqlfront.de) I use it daily in development tasks for a hybrid Win2k - Linux setup. Works great. 10 Sep 01 10:28:21 PM, Francois Meehan [EMAIL PROTECTED] wrote: Hi all, I am looking for a freeware-shareware pgm for data entry, on win32 for a mysql database on Linux(not that it matters I guess), that would allow me to feed data with drop down or list box field. Any suggestions??? Thanks in advance... ### # Francois Meehan, vive l'Open source et Linux # # committed to open source and Linux # # # ### - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Query optimization issue.
mysql explain select * from dominf.domip order by ip desc; 'ORDER BY something DESC' does not use an index. It must be ascending. See the manual. Also, I forgot, if you do this often and it needs to be in DESC order, you can always alter the app to create and use an extra column 'ip_desc unsigned int not null' and set it in an insert to (0x - ip) and have an index on ip_desc. (If the column was signed you would use 0x7FF - thevariable, but ip numbers need to be stored as unsigned). Sincerely, Steven Roussey http://Network54.com/?pp=e PS: I'm trying to set aside a week in September to augment the MySQL code to use an index even with DESC in these situations. MySQL AB has some unfinished code to do this that I'm working from. I'm just familiarizing myself with the context of the code right now. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Query optimization issue.
mysql explain select * from dominf.domip order by ip desc; 'ORDER BY something DESC' does not use an index. It must be ascending. See the manual. Sincerely, Steven Roussey http://Network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: slow querys
The slow queries are those like... SELECT id FROM table WHERE field like '%text%' AND ok=1 AND type=1 WHERE field like '%text%' -- this will not use an index. Read the manual about fulltext. It may or may meet your needs. It works pretty good. If the text is a word you could also build a word index table. Check the list archives. If you are scanning for something like 'SCRIPT type=' in a field, then you will have little luck. So the answer depends on what you are trying to do (as always). Sincerely, Steven Roussey http://Network54.com/?pp=e mysql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql dbi connect
I am having a problem with MySQL. I am seeking some advice on either what is wrong or how to pursue the cause of this bug. I am using Perl DBI to interface with MySQL and when i execute the following statement: $dbh = DBI-connect('DBI:mysql:database=dbmysql2', undef, undef, {PrintError =0}) || die $ DBI::errstr; it causes the error: [Fri Aug 17 09:59:50 2001] productioncontent14.pl: Access denied for user: '@localhost' to database 'dbmysql2' at productioncontent14.pl line 29, STDIN line 1 the dbi connect is being invoked by a perl program which is run from the command line. when i execute the above DBI-connect statement with my MySQL userid and password it works fine. i have MySQL (not unix) root level privileges. for performance and security reasons i prefer to execute the version of the connect which is failing. i am running MySQL as described below: Server version 3.23.36 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock i am running Perl 5.6 and Red Hat 7.1 Linux 2.4.3-12 i checked that i have access to mysql.sock in addition to a my local host with my user name which is fully privileged, i currently show local host with no user name and no password in the mysql user table and with no privileges. i tried, as mysql root, to grant all privileges to localhost, which has no user name, via the sql command: grant all privileges on * to %@localhost; this resulted in a user table entry of no host name, a user name of %@localhost and no privileges. this didn't seem useful so i deleted the new user name, %localhost from all of the mysql security tables.
dbi-connect
I am using Perl DBI to interface with MySQL and when i execute the following statement: $dbh = DBI-connect('DBI:mysql:database=dbmysql2', undef, undef, {PrintError =0}) || die $ DBI::errstr; it causes the error: [Fri Aug 17 09:59:50 2001] productioncontent14.pl: Access denied for user: '@localhost' to database 'dbmysql2' at productioncontent14.pl line 29, STDIN line 1 the dbi connect is being invoked by a perl program which is run from the command line. when i execute the above DBI-connect statement with my MySQL userid and password it works fine. i have MySQL (not unix) root level privileges. for performance and security reasons i prefer to execute the version of the connect which is failing. i am running MySQL as described below: Server version 3.23.36 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock i am running Perl 5.6 and Red Hat 7.1 Linux 2.4.3-12 i checked that i have access to mysql.sock in addition to a my local host with my user name which is fully privileged, i currently show local host with no user name and no password in the mysql user table and with no privileges. i tried, as mysql root, to grant all privileges to localhost, which has no user name, via the sql command: grant all privileges on * to %@localhost; this resulted in a user table entry of no host name, a user name of %@localhost and no privileges. this didn't seem useful so i deleted the new user name, %localhost from all of the mysql security tables. thankyou steve
MySQL DBI connect
I am having a problem with MySQL. I am seeking some advice on either what is wrong or how to pursue the cause of this bug. I am using Perl DBI to interface with MySQL and when i execute the following statement: $dbh = DBI-connect('DBI:mysql:database=dbmysql2', undef, undef, {PrintError =0}) || die $ DBI::errstr; it causes the error: [Fri Aug 17 09:59:50 2001] productioncontent14.pl: Access denied for user: '@localhost' to database 'dbmysql2' at productioncontent14.pl line 29, STDIN line 1 the dbi connect is being invoked by a perl program which is run from the command line. when i execute the above DBI-connect statement with my MySQL userid and password it works fine. i have MySQL (not unix) root level privileges. for performance and security reasons i prefer to execute the version of the connect which is failing. i am running MySQL as described below: Server version 3.23.36 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock i am running Perl 5.6 and Red Hat 7.1 Linux 2.4.3-12 i checked that i have access to mysql.sock in addition to a my local host with my user name which is fully privileged, i currently show local host with no user name and no password in the mysql user table and with no privileges. i tried, as mysql root, to grant all privileges to localhost, which has no user name, via the sql command: grant all privileges on * to %@localhost; this resulted in a user table entry of no host name, a user name of %@localhost and no privileges. this didn't seem useful so i deleted the new user name, %localhost from all of the mysql security tables.
mysql dbi connect
I am having a problem with MySQL. I am seeking some advice on either what is wrong or how to pursue the cause of this bug. I am using Perl DBI to interface with MySQL and when i execute the following statement: $dbh = DBI-connect('DBI:mysql:database=dbmysql2', undef, undef, {PrintError =0}) || die $ DBI::errstr; it causes the error: [Fri Aug 17 09:59:50 2001] productioncontent14.pl: Access denied for user: '@localhost' to database 'dbmysql2' at productioncontent14.pl line 29, line 1 the dbi connect is being invoked by a perl program which is run from the command line. when i execute the above DBI-connect statement with my MySQL userid and password it works fine. i have MySQL (not unix) root level privileges. for performance and security reasons i prefer to execute the version of the connect which is failing. i am running MySQL as described below: Server version 3.23.36 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock i am running Perl 5.6 and Red Hat 7.1 Linux 2.4.3-12 i checked that i have access to mysql.sock in addition to a my local host with my user name which is fully privileged, i currently show local host with no user name and no password in the mysql user table and with no privileges. i tried, as mysql root, to grant all privileges to localhost, which has no user name, via the sql command: grant all privileges on * to %@localhost; this resulted in a user table entry of no host name, a user name of %@localhost and no privileges. this didn't seem useful so i deleted the new user name, %localhost from all of the mysql security tables.
Re: ASP ADO
In my (limited) experience it's nearly as easy as using ASP/ADO wirth Access. MySQL is a bit touchier about what it considers proper SQL. We're developing a hybrid (mutant?) system using MS's IIS web server and MySQL running on a Linux box and it's coming along quite nicely. You really don't need to know much more about ASP/ADO than you need to know to make it work with Access. 07 Aug 01 10:09:06 PM, Steve Doig [EMAIL PROTECTED] wrote: Hi Folks, New user here...How easy or not is it to use mySQL as a substitute when using .asp and ADO? What do I need to know in order to use mySQL with ASP and ADO? Are there 3rd party solns available? Thankyou, Steve. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Nested Queries in MySQl
Hi all, I have not been using MySQL for too long but have been using SQL7.0 for a while now, so I am generally familiar with the structure. My basic problem right now is trying to figure out how to get MySQL to do nested queries such as ... select count(*) from (select count(*) as A, group_col from my_table group by group_col) as B Could someone help me out with getting the nested query to work, (I know this can be done here with a distinct but work with me here.) Thanks in advance. Steve - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Replication without using replication?
In MySQL 3.23.40 there are no more warnings about aborted connections (yay!) and I can see what is actually in the error log. I found a binlog error message even though we do not use replication or the binlog (though this got me thinking that we should use it as it will be replacing the updatelog and has more features). Does anyone know if this is a fluke or something I should be worried about? Thanks! -steve- 010724 14:48:10 mysqld started /usr/local/mysql/bin/mysqld: ready for connections 010724 14:50:40 /usr/local/mysql/bin/mysqld: Binlog closed, cannot RESET MASTER Status information: Current locks: lock: 45cd20dc: read read : 456f8438 (94229:1); lock: 45a8ff64: lock: 452068c4: key_cache status: blocks used: 30857 not flushed: 0 w_requests: 0 writes: 0 r_requests: 7 reads: 5 handler status: read_key:1 read_next: 0 read_rnd 0 read_first: 0 write: 0 delete 0 update: 0 Table status: Opened tables: 2 Open tables:2 Open files: 6 Open streams: 0 Sincerely, Steven Roussey Network54.com http://network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Optimizing MySQL
Recently the index files became corrupted and I ran the myisamchk *.MYI -r -S to repair them. Try an OPTIMIZE TABLE or ANALYZE TABLE. Your queries may be using the incorrect indexes since the statistics have not been updated. You can also do this: myisamchk *.MYI -a Sincerely, Steven Roussey Network54.com http://network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Optimizing MySQL
I have used optimize table on several tables however it has not affected the speed of the queries or the large amount of processor resources that are being used. Have you heard of a way to rebuild a table more efficiently? Go back and do this: myisamchk -o *MYI myisamchk -a *MYI myisamchk -S *MYI myisamchk -R1 *MYI Do them as sepparate commands. It will take a while. Look for messages from myisamchk about the tables. Sincerely, Steven Roussey Network54.com http://network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL 3.23.39 released
Steven Now I have a question: Does this also apply to automatic repair inside Steven mysqld? I'd finally use the feature if its true... If you just run CHECK TABLE, it will repair the open count (it should always have done that). myisamchk --fast --force is equal to: run CHECK TABLE on all tables Run REPAIR TABLE on tables which was not marked as ok. I was refering to --myisam-recover=FORCE As I remember, it would repair tables that had a open count 0 (at least back when I first tried using it). Sincerely, Steven Roussey Network54.com http://network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Help: Replication between 2 tables
Hello, I am trying to set up mysql replication between 4 machines each with version 3.23.38 installed. My problem is that the master database has a table called metadata and the slaves have the same table but called METADATA. Is there a replicate-rewrite-table option, or do I need to trap the selects on metadata and convert them to lowercase in the code to get the replication to work ?... Steven Goodliff. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
: log event entry exceeded max_allowed_packet
Hello, I'm trying to set up replication, but I cant get any further than this message on the slave. Error reading packet from server: log event entry exceeded max_allowed_packet My max allowed packet size is 512M which I've gradually reached while trying to stop the above error message. Is this the real problem or is there another issue causing this ? Cheers Steve Goodliff
Re: MySQL 3.23.39 released
Changes in release 3.23.39 -- * Fixed problem with shutdown when `INSERT DELAYED' was waiting for a `LOCK TABLE'. * Running `myisamchk --fast --force' will no longer repair tables that only had the open count wrong. -- THANK YOU! You don't know what it was for the first item to require a forced shutdown that made myisamchk take a huge amount of time. Although we have not had the need for a `myisamchk --fast --force' in a while, I tell you if you had a big fulltext index, you would avoid it like the plague. Now I have a question: Does this also apply to automatic repair inside mysqld? I'd finally use the feature if its true... Sincerely, Steven Roussey Network54.com http://network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Nesting DATE_FORMAT
Hello I am trying to display a table depending if the registration date is between a certain period. To do this , I want to pull the 'day' field from another table and construct one of the comparison dates with this value. It is probably best explained with a query. This is what I am trying to do: SELECT whatever FROM tbl1, tbl2 WHERE ( tbl1.registration_date = DATE_FORMAT(now(),'%Y-%m-)DATE_FORMAT(tbl2.registration_date,'%d') AND tbl1.registration_date = DATE_FORMAT(now(),'%Y-%m-%d') ) AND tbl2.id=1000 So both tables have their own registration_date, and I am trying to display rows when the registration_date of tbl1 is between the day of tbl2 joined with todays month and year AND todays date. e.g. if the reg_date in tbl2 is 2000-12-15 and todays date is 2001-05-29, it should display any records of tbl1 that have a registration date between 2001-05-15 and 2001-05-29 This is for a payment system, where accounts are paid on the registration date as defined in tbl2 each month, and they can check what accounts they will be getting paid for during the month. I am doing this in a PHP script and can pull the day out first then pass it in a second query. Was trying to find a way to do it in one query. Any tips or hints would be extremely appreciated. Thanks! Have a good one. Regards, Steven Wren - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
2 errrors while executing make
I have installed mysql 3.23.36 for the first time ever I did the test (ok) I ran the bench mark and could not load DBI I installed DBI 1.14 And Data Showtable 3.3 Then tried to install (Msql-Mysql-modules-1.2215) Perl Makefile.pl worked fine I run make and this is what it ends with: make[1]: Entering directory `/usr/lib/perl5/Msql-Mysql-modules-1.2215/mysql' LD_RUN_PATH=/usr/local/mysql/lib/mysql:/lib:/usr/lib:/usr/lib/gcc-lib/i486- linux/2.7.2.3 gcc -o ../blib/arch/auto/DBD/mysql/mysql.so -shared dbdimp.o mysql.o -L/usr/local/mysql/lib/mysql-L/usr/local/mysql/lib/mysql -lmysql client -lm -lz -L/usr/lib/gcc-lib/i486-linux/2.7.2.3 -lgcc /usr/i486-linux/bin/ld: cannot open -lz: No such file or directory make[1]: *** [../blib/arch/auto/DBD/mysql/mysql.so] Error 1 make[1]: Leaving directory `/usr/lib/perl5/Msql-Mysql-modules-1.2215/mysql' make: *** [subdirs] Error 2 I could use any help possible. IÂ’m new to this entire thing Thanks
2 errrors while executing make
I have installed mysql 3.23.36 for the first time ever I did the test (ok) I ran the bench mark and could not load DBI I installed DBI 1.14 And Data Showtable 3.3 Then tried to install (Msql-Mysql-modules-1.2215) Perl Makefile.pl worked fine I run make and this is what it ends with: make[1]: Entering directory `/usr/lib/perl5/Msql-Mysql-modules-1.2215/mysql' LD_RUN_PATH=/usr/local/mysql/lib/mysql:/lib:/usr/lib:/usr/lib/gcc-lib/i486- linux/2.7.2.3 gcc -o ../blib/arch/auto/DBD/mysql/mysql.so -shared dbdimp.o mysql.o -L/usr/local/mysql/lib/mysql-L/usr/local/mysql/lib/mysql -lmysql client -lm -lz -L/usr/lib/gcc-lib/i486-linux/2.7.2.3 -lgcc /usr/i486-linux/bin/ld: cannot open -lz: No such file or directory make[1]: *** [../blib/arch/auto/DBD/mysql/mysql.so] Error 1 make[1]: Leaving directory `/usr/lib/perl5/Msql-Mysql-modules-1.2215/mysql' make: *** [subdirs] Error 2 I could use any help possible. IÂ’m new to this entire thing Thanks
Choosing between the three types of transaction-supporting table
Hi folks, The recent additions of storage handler for transaction-supporting table confuses us. From what I read on the list, Innobase is generally much faster than BerkeleyDB. But I can live with slower speed since my application will not be expecting high rates. It is reliability and tested product which I am more concerned with. Could somebody provide a general overview and comparison of BerkeleyDB, Innobase, and the upcoming Gemini? Regarding speed, space efficiency, recovery, and memory footprint. Better still if it ends with the practical advice on choosing between them (i.e.: you should choose Innobase if ..., but use BerkeleyDB if ...). I believe that is also a manual/FAQ material. Thanks in advance! Steve - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AW: Got an error reading communication packets
hi van thank you for responding. but i have gcc-2.95.2-149 installed (a suse 7.1 version). so i think this isn't the problem... i tried several other things: i changed to other auth_mysql modules for apache: didn't help i used apache 1.3.17: didn't help i tried it first with php 4.0.4pl1: didn't help (by the way: is there any way to get the zend optimizer for php 4.0.5pl1?) so, any new idea? steven -Ursprngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]Im Auftrag von Van Gesendet: Dienstag, 20. Mrz 2001 04:47 An: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Betreff: Re: Got an error reading communication packets Steven Glogger wrote: after upgrading to 3.23.35 (from 3.22.32) i've got several times: 010319 20:45:18 Aborted connection 4 to db: 'unconnected' user: 'admin' host: `localhost' (Got an error reading communication packets) it's running on a productive server, but it's not working correctly. some infos: apache 1.3.19, Linux 2.2.18-SMP #1 SMP Sat Jan 20 07:41:17 GMT 2001 i686 unknown, PHP 4.0.5 RC1 any idea what it is? greetings steven Steven: Check your compiler. I was getting this when compiling with egcs-2.91. Upgraded to 2.95.2 and all is happy, now. Van -- = Linux rocks!!! http://www.dedserius.com = - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: AW: AW: Got an error reading communication packets
hi van ok, here we are: CONFIGURE/INSTALLATION === i installed it with: ./configure --datadir=/usr/local/mysql/data --localstatedir=/www/server/dat abases make make install cd scripts ./mysql_install_db cd ../support-files chmod 755 mysql.server cp mysql.server /etc/rc.d/mysql.server STARTING THE DAEMON /etc/rc.d/mysql.server start root@wpm(/usr/local/mysql) ps ax | grep mysql 12488 ?S 0:00 sh /usr/local/bin/safe_mysqld --user=root --pid-file=/www/server/databases/wpm. pid --datadir=/www/server/databases 12512 ?S 0:00 /usr/local/libexec/mysqld --basedir=/usr/local --datadir=/www/server/databas es --user=root --pid-file=/www/server/databases/wpm.pid --skip-locking 12514 ?S 0:00 /usr/local/libexec/mysqld --basedir=/usr/local --datadir=/www/server/databas es --user=root --pid-file=/www/server/databases/wpm.pid --skip-locking 12515 ?S 0:00 /usr/local/libexec/mysqld --basedir=/usr/local --datadir=/www/server/databas es --user=root --pid-file=/www/server/databases/wpm.pid --skip-locking 23390 pts/3S 0:00 grep mysql = /etc/my.cnf === # Example mysql config file. # You can copy this to one of: # /usr/local/etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is /usr/local/var) or # ~/.my.cnf to set user-specific options. # # One can use all long options that the program supports. # Run the program with --help to get a list of available options # This will be passed to all mysql clients [client] #password = my_password port= 3306 socket = /tmp/mysql.sock # Here is entries for some specific programs # The following values assume you have at least 32M ram # The MySQL server [mysqld] #port= 3306 socket = /tmp/mysql.sock skip-locking set-variable= key_buffer=16M set-variable= max_allowed_packet=1M set-variable= thread_stack=128K set-variable= max_connections=1024 set-variable= wait_timeout=600 # Start logging #log [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash [isamchk] set-variable= key_buffer=16M LOG FILE = 010303 18:30:11 Aborted connection 1727 to db: 'unconnected' user: 'admin' host: `localhost' (Got an error reading communication pa ckets) 010303 18:33:11 Aborted connection 1729 to db: 'unconnected' user: 'admin' host: `localhost' (Got an error reading communication pa ckets) 010303 18:33:17 Aborted connection 1730 to db: 'server_admin' user: 'admin' host: `localhost' (Got an error reading communication p ackets) 010303 18:33:17 Aborted connection 1731 to db: 'server_admin' user: 'admin' host: `localhost' (Got an error reading communication p ackets) [this message is what i got all 3 minutes (ok, my whatsup is checking every 3 minutes if the daemon is working, but he is connecting only with a mysql-client to see, if its working)] i think maybe it's a problem with the libraries. but i tried it on 2 different linux (suse 7.1 and 6.4) systems, both smp systems, and on both i got the same error... sounds strange to me greetings thank you -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Van Sent: Tuesday, March 20, 2001 10:31 AM To: [EMAIL PROTECTED] Subject: Re: AW: AW: Got an error reading communication packets Steven Glogger wrote: hi van thank you for the tip. on a test server i installed a fresh version of mysql 3.23.35. so... i think dont have to myisamchk the files mysql has been compiled with a simple ./configure; make; make install procedure but the result is: not working... this is a strange error. i found many mailinglist entries about this problem, but no one seems to have solved this problem... any futher ideas? steven Steven: Gotta call it a night, but, if you can send some snippets from the logs, I could probably help you. Strip the passwords out. I don't wanna see 'em. In the configure did you set a --localstatedir=/somedir/ directive? If so, you'll see a log entry. Do you have an /etc/my.cnf file? Do you start with mysqld or safe_mysqld? ps ax | grep mysql Flash that output strip passwds echo `I don\'t want \'em` These'll help. I don't do RedHat, but, understand it pretty well, but, the issue we're working with is pretty insidious. I still get 'em on my server in MI. I'm okay w' it since the li'l bastard is still doing his job 155 days after his last reboot, but, I really want to get a bead on this lost communications packets problem. I'm pretty sure it's a library issue. Let me know what you find. As-a-gwin' ta sleep. Peace, Van -- = Linux rocks!!! http://www.dedserius.com
RE: [PHP-DEV] RE: 4.0.5RC1 static build with Apache 1.3.19 problem
Yes, I have tried it both ways. I configure Apache a second time after making php and it still does not work. :( Sincerely, Steven Roussey Network54.com http://network54.com/?pp=e Subject: Re: [PHP-DEV] RE: 4.0.5RC1 static build with Apache 1.3.19 problem Did you configure Apache after you did the make install from PHP? In the PHP directory run make install-sapi and see that PHP copies everything to the right directory. Andi At 07:12 PM 3/16/2001 -0800, Steven Roussey wrote: Subject: 4.0.5RC1 static build with Apache 1.3.19 problem Also happens in Apache 1.3.12 Sincerely, Steven Roussey Network54.com http://network54.com/?pp=e cd apache_1.3.12 CC=gcc CFLAGS="-O6 -march=i686 " CXX=gcc CXXFLAGS="-O6 -march=i686 " \ ./configure \ --activate-module=src/modules/php4/libphp4.a --enable-module=rewrite cd .. cd php-4.0.5RC1 CC=gcc CFLAGS="-O6 -march=i686 " CXX=gcc CXXFLAGS="-O6 -march=i686 " \ ./configure --with-mysql --with-xml --with-dom --with-zlib --with-gd \ --with-openssl --with-sablot --with-apache=../apache_1.3.12 --enable-inline- optimization make make install cd .. cd apache_1.3.12 make === src/modules/php4 make[4]: *** No rule to make target `all'. Stop. make[3]: *** [all] Error 1 make[2]: *** [subdirs] Error 1 make[2]: Leaving directory `/home/network54/webserver/apache_1.3.12/src' make[1]: *** [build-std] Error 2 make[1]: Leaving directory `/home/network54/webserver/apache_1.3.12' make: *** [build] Error 2 -- PHP Development Mailing List http://www.php.net/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Got an error reading communication packets
after upgrading to 3.23.35 (from 3.22.32) i've got several times: 010319 20:45:18 Aborted connection 4 to db: 'unconnected' user: 'admin' host: `localhost' (Got an error reading communication packets) it's running on a productive server, but it's not working correctly. some infos: apache 1.3.19, Linux 2.2.18-SMP #1 SMP Sat Jan 20 07:41:17 GMT 2001 i686 unknown, PHP 4.0.5 RC1 any idea what it is? greetings steven - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
INSERT delayed and concurrent inserts
Hi! Does MySQL's insert delayed handler insert concurrently with selects on MyISAM tables like the non-delayed inserts? Sincerely, Steven Roussey Network54.com http://network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
More on: mysqld got signal 11
Hi! I have received several emails from people that choose not to (or can not) post to the list about mysql segfaulting. At the moment, I have the segfaults stopped by doing all of the below, I don't know (yet) which are to blame or in what combination: 1) Eliminate insert delayed (not really needed anymore, anyways) 2) No fulltext search 3) Increased file descriptor limits (again!) 4) Find and eliminated bad format queries, particularly insert delayeds that cause a error. After a few days of no segfaults, we are going to allow fulltext again, the other changes can (and should) remain permanent. NOTE: From the emails I received I could find two trends that _may_ be the source of segfaults: 1) Not enough allowances for open files (#3 above) 2) Insert delayed AND poorly format queries AND high load REQUEST: Can we make an option in mysqld to log bad queries? Re: 'You have an error in your SQL syntax ...' PS - we do not use autoincrement in insert delayed tables. Sincerely, Steven Roussey Network54.com http://network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: More on: mysqld got signal 11
Damn! It took longer, but crashed again. The stack trace has been the same as the last three crashes: # resolve_stack_dump -s /tmp/mysqld.sym -n mysqld.stack 0x40097532 __evoke_link_warning_llseek + 937652786 0x80b3360 mysql_update__FP3THDP13st_table_listRt4List1Z4ItemT2P4ItemUl15enum_duplicate s13thr_lock_type + 2480 0x80b3595 mysql_update__FP3THDP13st_table_listRt4List1Z4ItemT2P4ItemUl15enum_duplicate s13thr_lock_type + 3045 0x80bf1a4 pack_fields__FiRt4List1Z12create_field + 320 0x80bf4d9 make_empty_rec__Fi7db_typeUiRt4List1Z12create_fieldUiUi + 309 mysql_update() seems to be a problem area (or is getting bad data it is not checking). Is there a way to interpret the above into lines of code? What is the '+ 2480' above in reference to? I'm going nuts. If anyone else has a strace in there error log when mysql 3.23.333 crashes, can you send me the part that looks like: mysqld got signal 11; The manual section 'Debugging a MySQL server' tells you how to use a stack trace and/or the core file to produce a readable backtrace that may help in finding out why mysqld died Attemping backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong stack range sanity check, ok, backtrace follows 0x40097532 0x80b3360 0x80b3595 0x80bf1a4 0x80bf4d9 stack trace successful, now will try to get some variables. Some pointers may be invalid and cause dump abort thd-query at (nil) = Thanks, Sincerely, Steven Roussey Network54.com http://network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysqld got signal 11
Hi, I'm using mysql-3.23.30 Linux binary downloaded directly from the mysql site, and after a while the server tanks and many copies of this are all over the error log: Number of processes running now: 0 010306 09:19:24 mysqld restarted /usr/local/mysql/bin/mysqld: ready for connections mysqld got signal 11; The manual section 'Debugging a MySQL server' tells you how to use a stack trace and/or the core file to produce a readable backtrace that may help in finding out why mysqld died Attemping backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong frame pointer (ebp) is NULL, did you compile with -fomit-frame-pointer? Aborting backtrace I assume the binary is compiled with -fomit-frame-pointer. This problem seems repeatable, but only after several days. This server is used for three things: 1) Logging (using insert delayed), 2) a cache system (normal inserts), and 3) a set of fulltext tables (using replace delayed). The server has worked flawlessly for months over various mysql versions including this one. Only when the replace delayed (and selects) on the fulltext tables occurs, does this appear. Is there a binary of a debug version for Linux? I'd like to catch the server going down with more information. Sincerely, Steven Roussey Network54.com http://network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqld got signal 11
Hi, OK, I now still have the same problem after upgrading to 3.23.33-pc-linux-gnu-i686 binary. After myisamchking all tables, this time it took just about one minute before crashing: mysqld got signal 11; The manual section 'Debugging a MySQL server' tells you how to use a stack trace and/or the core file to produce a readable backtrace that may help in finding out why mysqld died Attemping backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong Bogus stack limit or frame pointer, aborting backtrace Number of processes running now: 0 010306 15:13:38 mysqld restarted I'm off to compiling a debug version. :( Will send a real bug report if it dies again. Sincerely, Steven Roussey Network54.com http://network54.com/?pp=e Sincerely, Steven Roussey Network54.com http://network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqld got signal 11
Hi, Ah, today's problems are from a bug that dates back a year or more. Streaming a large number of insert delayeds into a table that does not exist causes the crash. The table was not created correctly last night. Is there a web version of mysql code like PHP has http://lxr.php.net ? I'd like to browse through and see what I can find, but using vi is just not worth my time. Sincerely, Steven Roussey Network54.com http://network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysqld got signal 11
I have tried the above with 3.23.33 and was not able to reproduce it. I could be wrong (imagine that!). It crashed again today even with the table in place. In order to take one item out of the equation, I stopped the replace delayed process I had running. It still crashed (about 8 hours later). Since it takes this long, I can't do a trace file -- it would be larger than our hard drives. I'm enabling the log on this server today -- hopefully that will help, and will be smaller... Right now the only thing I can think of that is different (since the server never used to crash before): 1) Fulltext searches (selects) are happening on this server. 2) I switched the apache servers to _not_ use persistent connections (in most cases -- I just realized we are currently using a 50/50 mix). So my plan right now is to do this: 1) Turn on the log and see what is there when it crashes. 2) start again but don't have fulltext searches 3) start again but use persistent connection and do have FT searches 4) start again but use persistent connections no FT searches 5) cry if it is still crashing ;| Pertinient information(?): 010307 2:03:36 Aborted connection 580760 to db: 'logging' user: 'apache' host: `tank' (Got timeout reading communication packets) mysqld got signal 11; The manual section 'Debugging a MySQL server' tells you how to use a stack trace and/or the core file to produce a readable backtrace that may help in finding out why mysqld died Attemping backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong stack range sanity check, ok, backtrace follows 0x40097532 0x400fc24e 0x400fbf9a 0x8180797 0x80b2c23 0x80b92e4 0x80bf706 stack trace successful, now will try to get some variables. Some pointers may be invalid and cause dump abort thd-query at (nil) = 010307 2:25:10 Aborted connection 623630 to db: 'logging' user: 'apache' host: `neo' (Got timeout reading communication packets) 010307 2:25:29 Aborted connection 624240 to db: 'logging' user: 'apache' host: `neo' (Got timeout reading communication packets) Sincerely, Steven Roussey Network54.com http://network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: shutdown and insert delayed
Hi, I was trying to use debugging (creating a trace and log file) in order to find the crashing problem we have been experiencing. However, I tend to come up against another problem in the course of things -- mysql not shutting down. This time I have a log file and a trace file. When I shut down, most of the processes quit, but a few remain (exhibit A). At this point the log stops (no additional info being written)(exhibit B), but the trace continues. Then the trace file stops (exhibit C). Everything is on hold. So I have a trace file, and log file that are not be written to, but still have processes running. What is going on? Sincerely, Steven Roussey Network54.com http://network54.com/?pp=e exhibit A: 11519 pts/2S 0:00 /bin/sh ./bin/safe_mysqld --user=root -O back_log=20 -O table_cache=3500 --log --debug=d,info,query 11567 pts/2S 1:48 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/lo cal/mysql/var --user=ro 11569 pts/2S 0:28 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/lo cal/mysql/var --user=ro 11570 pts/2S 0:01 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/lo cal/mysql/var --user=ro 11597 pts/2S 2:49 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/lo cal/mysql/var --user=ro 5391 pts/2S 0:00 /usr/local/mysql/bin/mysqladmin shutdown 5405 pts/2S 0:00 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/lo cal/mysql/var --user=ro 5546 pts/0R 0:00 ps ax Exhibit B: # tail switch.network54.com.err -n20 010307 12:33:41 Aborted connection 87779 to db: 'logging' user: 'apache' host: `tank.f' (Got timeout reading communication packets) 010307 12:33:41 Aborted connection 90741 to db: 'logging' user: 'apache' host: `morpheus.f' (Got timeout reading communication packets) 010307 12:33:41 Aborted connection 90700 to db: 'logging' user: 'apache' host: `mouse.f' (Got timeout reading communication packets) 010307 12:33:41 Aborted connection 90714 to db: 'logging' user: 'apache' host: `neo.f' (Got timeout reading communication packets) 010307 12:33:41 Aborted connection 90722 to db: 'logging' user: 'apache' host: `morpheus.f' (Got timeout reading communication packets) 010307 12:33:41 Aborted connection 90733 to db: 'logging' user: 'apache' host: `morpheus.f' (Got timeout reading communication packets) 010307 12:33:41 Aborted connection 90736 to db: 'logging' user: 'apache' host: `morpheus.f' (Got timeout reading communication packets) 010307 12:33:41 Aborted connection 90729 to db: 'logging' user: 'apache' host: `neo.f' (Got timeout reading communication packets) 010307 12:33:41 Delayed insert thread couldn't get requested lock for table log_day_20010307 010307 12:33:41 Aborted connection 72115 to db: 'logging' user: 'apache' host: `neo.f' (Got an error writing communication packets) 010307 12:33:41 Aborted connection 70096 to db: 'logging' user: 'apache' host: `mouse.f' (Got an error writing communication packets) 010307 12:33:41 Aborted connection 66934 to db: 'logging' user: 'apache' host: `mouse.f' (Got an error writing communication packets) 010307 12:33:41 Aborted connection 56536 to db: 'logging' user: 'apache' host: `mouse.f' (Got an error writing communication packets) 010307 12:33:41 Aborted connection 46514 to db: 'logging' user: 'apache' host: `tank.f' (Got an error writing communication packets) 010307 12:33:41 Aborted connection 40577 to db: 'logging' user: 'apache' host: `tank.f' (Got an error writing communication packets) 010307 12:33:41 Aborted connection 45130 to db: 'logging' user: 'apache' host: `tank.f' (Got an error writing communication packets) 010307 12:33:41 Aborted connection 39806 to db: 'logging' user: 'apache' host: `mouse.f' (Got an error writing communication packets) 010307 12:33:41 Aborted connection 39819 to db: 'logging' user: 'apache' host: `tank.f' (Got an error writing communication packets) 010307 12:33:41 Aborted connection 39476 to db: 'logging' user: 'apache' host: `tank.f' (Got an error writing communication packets) 010307 12:33:41 Aborted connection 30930 to db: 'logging' user: 'apache' host: `neo.f' (Got an error writing communication packets) Exhibit C: # tail switch.trace -n50 _mi_bin_search: exit: flag: 1 keypos: 38 _mi_bin_search: exit: flag: 1 keypos: 78 _mi_bin_search: exit: flag: 1 keypos: 106 _mi_make_key: exit: keynr: 0 _mi_bin_search: exit: flag: 1 keypos: 0 _mi_bin_search: exit: flag: 1 keypos: 34 _mi_bin_search: exit: flag: 1 keypos: 2 _mi_bin_search: exit: flag: 1 keypos: 19 _mi_make_key: exit: keynr: 1 _mi_bin_search: exit: flag: 1 keypos: 19 _mi_bin_search: exit: flag: 1 keypos: 85 _mi_bin_search: exit: flag: 1 keypos: 113 my_malloc: exit: ptr: 42a8 init_io_cache: info: init_io_cache: cachesize = 1048576 _mi_make_key: exit: keynr: 0 _mi_bin_search: exit: flag: 2 keypos: 1 _mi_bin_search: exit: flag: 1 keypos: 10
RE: shutdown and insert delayed
I forget to give the tail of the error log: 010307 12:33:41 Aborted connection 90714 to db: 'logging' user: 'apache' host: `neo.f' (Got timeout reading communication pac kets) 010307 12:33:41 Aborted connection 90722 to db: 'logging' user: 'apache' host: `morpheus.f' (Got timeout reading communicatio n packets) 010307 12:33:41 Aborted connection 90733 to db: 'logging' user: 'apache' host: `morpheus.f' (Got timeout reading communicatio n packets) 010307 12:33:41 Aborted connection 90736 to db: 'logging' user: 'apache' host: `morpheus.f' (Got timeout reading communicatio n packets) 010307 12:33:41 Aborted connection 90729 to db: 'logging' user: 'apache' host: `neo.f' (Got timeout reading communication pac kets) 010307 12:33:41 Delayed insert thread couldn't get requested lock for table log_day_20010307 010307 12:33:41 Aborted connection 72115 to db: 'logging' user: 'apache' host: `neo.f' (Got an error writing communication pa ckets) 010307 12:33:41 Aborted connection 70096 to db: 'logging' user: 'apache' host: `mouse.f' (Got an error writing communication packets) 010307 12:33:41 Aborted connection 66934 to db: 'logging' user: 'apache' host: `mouse.f' (Got an error writing communication packets) 010307 12:33:41 Aborted connection 56536 to db: 'logging' user: 'apache' host: `mouse.f' (Got an error writing communication packets) 010307 12:33:41 Aborted connection 46514 to db: 'logging' user: 'apache' host: `tank.f' (Got an error writing communication p ackets) 010307 12:33:41 Aborted connection 40577 to db: 'logging' user: 'apache' host: `tank.f' (Got an error writing communication p ackets) 010307 12:33:41 Aborted connection 45130 to db: 'logging' user: 'apache' host: `tank.f' (Got an error writing communication p ackets) 010307 12:33:41 Aborted connection 39806 to db: 'logging' user: 'apache' host: `mouse.f' (Got an error writing communication packets) 010307 12:33:41 Aborted connection 39819 to db: 'logging' user: 'apache' host: `tank.f' (Got an error writing communication p ackets) 010307 12:33:41 Aborted connection 39476 to db: 'logging' user: 'apache' host: `tank.f' (Got an error writing communication p ackets) 010307 12:33:41 Aborted connection 30930 to db: 'logging' user: 'apache' host: `neo.f' (Got an error writing communication pa ckets) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysqld got signal 11
An update: o I found other queries that were insert delayed into non-existent tables and others that had wrong column names. Removing these, I thought would fix things. Nope. o I tried writing a log and a trace file. Too slow and unworkable. I tried just log (worked OK -- I got a crash but nothing looks abnormal in the log) and I tried just trace (still too slow). Trace with just info and error runs but didn't give me enough info to help. Lots of 'vio_read returned -1, errno: 11'. o Right now we are going to see how long we can run with no fulltext insert/replace/update/select. No FT at all. o The stack traces have been different but always have 'thd-query at (nil) = ' in the error log. Looks like a null pointer to me. The last stack trace: 010307 15:13:36 mysqld started /usr/local/mysql/libexec/mysqld: ready for connections mysqld got signal 11; The manual section 'Debugging a MySQL server' tells you how to use a stack trace and/or the core file to produce a readable backtrace that may help in finding out why mysqld died Attemping backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong stack range sanity check, ok, backtrace follows 0x40097532 0x80b3360 0x80b3595 0x80bf1a4 0x80bf4d9 stack trace successful, now will try to get some variables. Some pointers may be invalid and cause dump abort thd-query at (nil) = # resolve_stack_dump -s /tmp/mysqld.sym -n mysqld.stack 0x40097532 __evoke_link_warning_llseek + 937652786 0x80b3360 mysql_update__FP3THDP13st_table_listRt4List1Z4ItemT2P4ItemUl15enum_duplicate s13thr_lock_type + 2480 0x80b3595 mysql_update__FP3THDP13st_table_listRt4List1Z4ItemT2P4ItemUl15enum_duplicate s13thr_lock_type + 3045 0x80bf1a4 pack_fields__FiRt4List1Z12create_field + 320 0x80bf4d9 make_empty_rec__Fi7db_typeUiRt4List1Z12create_fieldUiUi + 309 Sincerely, Steven Roussey Network54.com http://network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqld got signal 11
mysqld got signal 11; The manual section 'Debugging a MySQL server' tells you how to use a stack trace and/or the core file to produce a readable backtrace that may help in finding out why mysqld died Attemping backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong stack range sanity check, ok, backtrace follows 0x813f4aa 0x8157e85 0x814f0ee 0x81565c5 0x807c0ac 0x8084f28 stack trace successful, now will try to get some variables. Some pointers may be invalid and cause dump abort thd-query at (nil) = 010306 Two odd things this time. One, the frame pointer is there, so it not being there on other messages really worries me. Two, in the case above is the thd-query equal to nil? BTW: the binary distribution does not contain resolve_stack_dump, which it should. :) If the binary does not omit the frame pointer, then it should be there. Or the binary could omit the frame pointer and it would make sense to not have resolve_stack_dump included. One way or the other. Using resolve_stack_dump: 0x813f4aa pthread_sighandler + 154 0x8157e85 _IO_default_xsputn + 113 0x814f0ee vfprintf + 414 0x81565c5 vsprintf + 69 0x807c0ac net_printf__FP6st_netUie + 108 0x8084f28 handle_one_connection__FPv + 360 Hmm... Greek to me. OK, so I'm starting over. Compliled my own version of mysql-3.23.33 --with-debug as per manual. I'll be back when I have more info Sincerely, Steven Roussey Network54.com http://network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysqld got signal 11
One more thing: In the processlist: | Delayed_insert | 578 | reschedule | log_day_20010307 After another minute (now over 10 minutes to reschedule) I get a _lot_ of these: | Query | 9| waiting for handler insert | insert delayed into logging.log_day_20010307 ... Which fills up all processes and locks up the server and in the error log there is: 010307 18:08:33 Delayed insert thread couldn't get requested lock for table log_day_20010307 Also, I have a BIG trace file for this, but I forgot to let it continue until a crash. Oops. FYI -steve- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ERROR 1030 at line 1: Got error 127 from table handler
010307 9:29:44 read_next: Got error 127 when reading table ./perf/webstats_apollo What causes them and how can I prevent them? Check to be sure you have enough open file descriptors. Set open-files for safe_mysqld or hack a ulimit in there yourself. Sincerely, Steven Roussey Network54.com http://network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: INSERT/REPLACE lock up in 3.23.33
Safest thing for you is to use our latest 3.23 binaries which are statically linked with stable glibc and with some additional patches. For this reason and greater simplicity :) I have been using your binaries for a long time now. Part of the reason we never tested BDB tables... There can be another source of problems with REPLACE. As it has to DELETE a row first, it might take a lot of time on huge table if MySQL is not able for some reason to utilize index in order to locate rows. Now this seems like it might be related. On a variable sized row table, this could be a source of problems. As a note, there is a unique index it should be using. Is there a way to see if it is, or USE INDEX like select? Sincerely, Steven Roussey Network54.com http://network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
PROCESSLIST reschedule
Hi, I searched all over, but I can not find out what the state 'reschedule' means in the PROCESSLIST. Sincerely, Steven Roussey Network54.com http://network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
BUG: delayed_insert_timeout ignored
I'm using 3.23.30, and when I use delayed_insert_timeout set to 2000 (verified with show variables), I still see many threads that have much higher time values. They aren't dieing off like they used to. Sincerely, Steven Roussey Network54.com http://network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Please HELP easy ANSWER
Hi Everyone, I have a big problem, that I bet could be solved pretty simply but I am new to mysql so its killing me and my database. Ok heres the problem, suddenly out of no where when my php script tries to (or manually) do a insert it says: ERROR 1062: Duplicate entry '127' for key 1 Now why all of a sudden is this doing this at id 127? This is what my create table statement looks like if this is of any help to you: CREATE TABLE custinfo ( id tinyint(4) DEFAULT '0' NOT NULL AUTO_INCREMENT, date date, name varchar(50), card varchar(22), exp_date varchar(7), authcode varchar(8), amount double(4,2), tax double(3,2), pnref varchar(16), address text, city tinytext, state varchar( 3), zip varchar(15), country varchar(2), contact_first varchar(20), contact_last varchar(20), contact_phone varchar(20), contact_ema il tinytext, tickets tinyint(3), description varchar(50), PRIMARY KEY (id), UNIQUE id (id)); Now after I got this message these are some of the things I have tried to solve the problem but of course had no success: I tried isamchk -r and -R, i have even tried it with -rkis and then just plain -kis i pretty much have tried them all and it still gives the same error when i try to insert anything into the list ? What do i do to fix it and what did i do to cause it? Please help me I need this database up and running (loosing money every hour its down!) :( please please please help thank you very much for your time and help. -Steven Steven Fruchter Chief Technology Officer SMR Studios, Inc. [EMAIL PROTECTED] http://www.smrstudios.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
client connection timeout
Hi! We are using PHP and want to detect if a MySQL server is available or not, but do so very quickly. Will the MySQL library in PHP use a connection client timeout? Can this be set under [client] in /etc/my.conf? Also, what is the connection is a persistent connection, and the server goes down, but PHP has not yet noticed? Is the configuration different? Thanks! Sincerely, Steven Roussey Network54.com http://network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
INSERT DELAYED and FULLTEXT and locking problems
Hi all, I was curious if anyone has been experiencing problems with INSERT DELAYED lately. At the moment, it seems to limited to INSERT DELAYED and tables with a FULLTEXT index. After a while, a lock on table prevents any normal INSERTS or SELECTS (this behavior may require doing both normal and delayed inserts, but I'm not sure... its either that or its the fulltext index. Insert delayed on a fixed size table with no fulltext index and no normal inserts works without error). The lock persists even when the delayed handler is empty (Not_flushed_delayed_rows is zero). I'm using MySQL 3.23.30 So anyway, I tried doing a flush tables. Bad idea!! Bad! It waits for the tables in question, and does not allow new insert delayed handlers to start up. The process list fills up with 'Creating delayed handler' and sends the server down as it fills all possible connection slots. Shutting down does not succeed. I was curious to hear any related stories that might help me create a test case. Sincerely, Steven Roussey Network54.com http://network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Large Table Problem
Hi. You posted to the wrong list. This list is for developing of MySQL itself. As for your problem, MySQL does not have a good solution at this point. Have you tried using the BACKUP command? It will flush and lock the table and copy only the data and .frm files. A RESTORE will rebuild the index. This, I think, will be faster. Otherwise, do you have an updatelog and a backup? And depending on your application, there are other things you can do. Is there a timestamp or autoincrement field? Are there only inserts going on? You could copy smaller sets of the table until done for example. Sincerely, Steven Roussey Network54.com http://network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Was FULLTEXT bug fixed?
Hi, There was a bug in 3.23.31 (I think) with fultext indexes. Was this fixed in 3.23.32? Sincerely, Steven Roussey Network54.com http://network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: TRUNCATE causes corrupted tables
Hi! No, I can't repeat it on command, sorry. However, I can give a little more info. When inserting, the index file got bigger. It was the data file that was set to 0 bytes, but did not get bigger on inserts. Doing a repair fixed it. I have some tables truncated via scripts, so I turned on mysql's auto repair feature so it can deal with it rather than me. Sincerely, Steven Roussey Network54.com http://network54.com/?pp=e - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: What does this mean ?
This patch will make it into 4.0.5, I believe. 4.0.4pl1 fixes only two bugs (one security issue, and one that make the PDF plugin work again). My patches could have side effects, since it will run code that has never been run before (like actually closing those persistent connections). They are in CVS. If you are using Apache (most of us are with PHP), note that persistent connections are per process, not per machine. Mod_ssl can use an Apache extension to keep shared memory across processes, but PHP does not. Read the docs (or the PHP list) to find out more. Sincerely, Steven Roussey Network54.com http://network54.com/?pp=e That is what I suspected. I suffer the same. The problem is buggy PHP persistent connection code. The "almost good" solution was presented to PHP people by Steven Roussey (http://marc.theaimsgroup.com/?l=php-devm=97562841130973w=2) and (http://marc.theaimsgroup.com/?l=php-devm=97858730928909w=2) and according to Andi Gutmans (http://marc.theaimsgroup.com/?l=php-devm=97858932431876w=2) the patch was committed to PHP CVS. However the patch is absent even from PHP 4.0.4pl1 (its zend_list.c is dated 2000-10-20). You might try to patch your PHP by yourself. Or try the latest snapshot from http://snaps.php.net (though I don't know if the patch is there). Sincerely speaking I don't know why Steven's patches haven't been incorporated into PHP 4.0.4pl1. Maybe Andi (Cc:ed) could explain this? Maciek -Original Message- From: Denis Gasparin [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 16, 2001 8:50 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: What does this mean ? I use php 4.0.3 with persistent connections. MySQL is installed on Linux RedHat 7.0. The problem is that the Mysql exists and I don't know exactly what appends to the php process Denis At 19.21 15/01/01, Maciek Uhlig wrote: Could you describe what exactly software connects to MySQL in your case? Maciek -Original Message- From: Denis Gasparin [mailto:[EMAIL PROTECTED]] Sent: Monday, January 15, 2001 6:00 PM To: [EMAIL PROTECTED] Subject: What does this mean ? From when I installed the mysql 3.23.30gamma I have mysql reporting these warnings: 10115 17:51:22 Aborted connection 2886 to db: 'db_name' user: 'user' host: `www.host' (Got an error reading communication packets) 010115 17:51:36 Aborted connection 3754 to db: 'db_name' user: 'user' host: `www.host' (Got an error reading communication packets) We have 700-800 contemporary connections to mysql and these warnings are given periodically every 5-10 minutes... Why does Mysql generate these logs? Denis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php