Perfomance Tuning
I have just installed redhat linux 9 which ships with mysql 3.23.56. Mysql has to be setup so that it can use innodb tables, and data inserts (blobs) should be able to handle at least 8M at a time. The machine has two P III 933MHz CPU's, 1.128G RAM (512M*2 + 128M), and a 36 Gig hd with 1 Gig swap and 3 equal size ext3 partitions. What would be the recomended setup for good performance considering that the db will have about 15 users for 9 hours in a day, and about 10 or so users throughout the day who wont be conistenly using the db. My configuration looks like this so far: /etc/mysql.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock max_allowed_packet=16M #InnoDB innodb_data_file_path = ibdata/ibdata1:2000M:autoextend innodb_data_home_dir = /var/lib/mysql [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid /etc/mysql.cnf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: storing large integers properly
you can create/alter the particular column with 'zerofill' attribute set. i.e.: create table books (isbn bigint(16) unsigned zerofill, somemore varchar(100)); -yves -Ursprüngliche Nachricht- Von: Eben Goodman [EMAIL PROTECTED] An: [EMAIL PROTECTED] Gesendet: Dienstag, 5. August 2003 15:49 Betreff: storing large integers properly I am storing book isbn numbers in a table. isbn numbers are 10 digit numbers and many start with 0. The data type of the field I am storing this info in is a bigint(16) unsigned. It appears that isbns that start with 0 are going in as 9 digit numbers, the 0 is being ignored or stripped. I have experienced this before with integer data types ignoring leading 0s. I'm wondering how to address this? Should I change the field to a varchar or char data type? Any advice is appreciated, thanks, Eben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DB Performance - Celeron vs. P4
The fact that you have several millions of rows may indicate that you have an I/O problem, not CPU.. do some benchmarking. and perhaps the solution is going to (if not already) SCSI drives, or some kind of raid configuration (recommend raid 0+1) Or if you want to keep costs low.. perhaps using mysql built in virtual raid feature where the database spans the database over multiple ide drives (ideally on different channels).. I've got several celeron servers with u160 scsi raid, and they smoke! good luck! On Wed, 6 Aug 2003, Jonathan Hilgeman wrote: Currently, I have a Celeron 1.2 Ghz server with 512 RAM, and I'm considering moving to a P4 2 Ghz with the same amount of RAM. I have a few specific tables with several million rows of data, and it takes quite a long time to process that data on my current server. Does anyone have a good idea of the type of performance increase I'd see if I moved to a P4 server? I'm hoping to see a response like, Oh yeah - I moved to a P4 from a Celeron and operations that used to take 10 minutes now take 1 minute or less - all because MySQL has special options to take full advantage of the P4's power. Or something like that. fingers crossed - Jonathan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SubQuery bug again in 4.1
Hi all, I think you misunderstood me. :-) I try to be more detailed, and I will also give an example. So, I have these two tables: CREATE TABLE main ( ID int not null, Value int, primary key (ID)) Type = InnoDB; CREATE TABLE sub ( MainID int not null, KeyDate date not null, SubValue int not null, primary key (MainID, KeyDate), foreign key (MainID) references main (ID)) Type = InnoDB; In the tables I have these lines: INSERT INTO main VALUES (1, null), (2, null), (3, null), (4, null), (5, null); INSERT INTO sub VALUES (1, '2001-01-01', 5), (1, '1999-01-01', 10), (2, '2001-01-01', 3), (2, '2001-01-02', 4), (4, '2001-01-01', 8); Now, I want to update the `Value` fields in the `main` table for ALL records to contain the latest `SubValue` from the `sub` table. So I want this to be in the `main` table: ID Value -- - 1 5 2 4 3 null 4 8 5 null -- - For this I need to select the most recent `SubValue` from the sub table (e.g.: The `SubValue` with the latest `KeyDate` for a specified `MainID`). I can do that this way (if you have other idea, tell me! :-)): select SubValue from sub where MainID = xxx order by KeyDate desc limit 1 (xxx means an ID from the `main` table) This query obviously returns with one record or null so I expect that this won't be a problem if I use this query as a subquery. (By the way, I get the same weird behavior in any subquery expression where I use the limit parameter.) Now, I want to update my `main` table to get the result above. update main set Value = (select SubValue from sub where MainID = main.ID order by KeyDate desc limit 1) This update sequence runs well, but the result in the `main` table will be this: ID Value -- - 1 5 2 4 3 4 4 4 5 4 -- - which is absolutelly not what I expected. Thanks for your help in advance, Dani -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Reaching max between 1456-1458 connections
I'm baffled by reaching a limit of between 1456 and 1458 connections, at which point I can no longer make new connections. I've tried compiling my own MySQL and using the stock MySql RPM's. I've experimented with ulimits, values in my.cnf, and kernel parameters, and all the permutations of the above that seemed relevant. I've tried this on RedHat 8 and 9 systems with a 2.4 kernel and a glibc-2.3. I very much want to get this MySql installation to scale significantly higher, and I can no longer find any hard-coded limits that seem to be affecting the number of connections I can make. The two different boxes on which I have tested are different enough that it doesn't seem possible that the hardware could be the limiting factor and still turn out uniformally to reach the same maximum. They have very different amounts of RAM, and one box has just one significantly slower processor, whereas the other has two much faster processors. But both top out at between 1456 and 1458 connections, both The error I'm seeing is Can't create a new thread (errno 11). Can anyone suggest a way to get to the bottom of this problem and to increase whatever resource is limiting the number of threads I can create? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Corrupt index = mysqld freeze?
Jennifer Goodie wrote: I have a stand alone database server. It is a RAID5 running mySQL 3.23.55 on FreeBSD 4.1 and has 768MB of RAM, I'm not sure on the processor speed, but I think it's a P3 1GHz. It has several tables with 20-40 million rows and a ton of smaller tables with less than 1 million rows. All tables are MyISAM and we have fewer than 10 queries per second. The super large tables are write only for the most part, with most reads taking place in off peak hours (a cron to generate aggregate data). The smaller tables are read/write. We've been experiencing a problem where mysqld stops responding to new connections. Any active connection is fine and can run any query it wants, but all new connections get stuck in the authenticating user phase. CPU and load drop to about zero when this happens, so I don't think it's the notorious threading issue. This freeze happens when more than 10-12 connections drop at the same time, usually when a queue caused by a table lock clears out. One of my coworkers insists that this is due to corrupt indexes, stating that if an index points to a location outside of the record set mysql gets confused and hangs. It has also been stated that multicolumn indexes are a problem, especially if they contain more than 3 columns. This goes against everything I know about mysql. In my experience if there is file corruption an error gets returned promptly. I also believe multicolumn indexes are a valuable feature. I have been told that I need to get rid of all multicolumn indexes in order to make the server stable. Needless to say, I am not very happy with this solution and don't have a lot of faith in it working. Has anyone else experienced anything similar to this, and if so what did you do to fix it? Anyone want to weigh in on the index theory because it doesn't really sound right to me, but I'm not exactly an expert. I'm not sure how related this is, but we have some relatively small tables ( 50,000 rows ) that exhibited the same behaviour when using MyISAM and MS Access front-ends. My solution was to change all affected tables to InnoDB. I hadn't noticed anything about indexes as you described, but then I wasn't looking. When it used to happen to us, there would be one update or insert process that mysqladmin claimed was 'locked' ( I think - this was a while ago ), and then a backlog of other user processes - often in unrelated tables - would start appearing. mysqladmin shutdown didn't work ( but mysqladmin processlist did - go figure ) - I had to kill -9 all mysql processes and restart the server. I always put the problem down to MS Access and it's record-locking 'style'. -- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: www.nusconsulting.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using innodb
On Wed, Jul 30, 2003 at 12:34:54PM +0800, unplug wrote: I use rpm to update the previous version. I can use innodb in redhat 9 with version 2.23.56. But it failed in redhat 7.2. I wonder it is the kernel problem. Why would you suspect the kernel? How did it fail? BTW, I want to ask whether I can do replication in the following case. innodb (master) --replication -- myisam (slave) Yes. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 207,625,568 queries (399/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to 'tell' the select what to omit
I'm writing a function for Perl module I'm writing that will allow you to do $query = SELECT.DBIallexcept($dbh,'fred','wilma').FROM monkey; If that will help. Let em know and I'll notify you when it's done. Thanks Dan As Victoria says, you can't. However I agree that this syntax is useless in many real-world situations. You are not the only one who finds this behaviour annoying. It's about time someone made SQL a 21st century language... Miroslav I. wrote: Hallo suppose you have a table with great number of columns (20 or 30), and you would like to specify every one but two or tree columns in a SELECT statement. Is there a way to specify only those two or tree columns for omission (which would be the 'short' way to do the job) instead of specifying every column that needed (which would be the 'long' way to do the job). Example: The 'TName' table header: id | name | surname | dateOB | idSCHOOL | idCITY | idSTATE | sex | idParent1 | idParent2 | interests | weight | height | age | auditDate | idAudittor | You need every field except the 'dateOB' and 'idSCHOOL' Ordinary select wold be: SELECT id,name,surname,idCITY,idSTATE,sex,idParent1,idParent2,interests,weigh t,height,age,auditDate,idAudittor, FROM TName It is too long expression, is there a way to specify only 'dateOB' and 'idSCHOOL' - the two column that are unwanted in the result set - in order to make the SELECT shorter? The SELECT should return every column but the specified ones. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: rpm build error
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Sat, 26 Jul 2003, Zenon Panoussis wrote: Can I safely comment out everything after %install on that line, or will something else break? The %install section finished successfully that way, but then the next problem popped up: RPM build errors: Installed (but unpackaged) file(s) found: /shared-libs.tar /usr/bin/mysql_fix_extensions /usr/bin/mysql_install /usr/bin/mysql_tableinfo /usr/bin/mysqldumpslow /usr/share/info/dir /usr/share/man/man1/mysql_fix_privilege_tables.1.gz All this is on redhat 9 and rpm-4.2-0.69. Thanks, I've added it to our bugs database now: http://bugs.mysql.com/bug.php?id=998 Bye, LenZ - -- Lenz Grimmer [EMAIL PROTECTED] Senior Production Engineer MySQL GmbH, http://www.mysql.de/ Hamburg, Germany For technical support contracts, visit https://order.mysql.com/?ref=mlgr -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE/L37lSVDhKrJykfIRAnwlAJ9eDG4URtpe1WWc2V2g+i1qgyafYQCeJZG7 s5fBoV3v79c/BfZwois1bIw= =iWmY -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DB Performance - Celeron vs. P4
At 07:00 PM 8/6/2003, you wrote: Currently, I have a Celeron 1.2 Ghz server with 512 RAM, and I'm considering moving to a P4 2 Ghz with the same amount of RAM. I have a few specific tables with several million rows of data, and it takes quite a long time to process that data on my current server. Does anyone have a good idea of the type of performance increase I'd see if I moved to a P4 server? I'm hoping to see a response like, Oh yeah - I moved to a P4 from a Celeron and operations that used to take 10 minutes now take 1 minute or less - all because MySQL has special options to take full advantage of the P4's power. Or something like that. fingers crossed I have a P4 2.4ghz with 1g ram and it is reasonably fast. You'll probably see a speed increase of at least 2x (if your hard disk is not slowing you down). The problem with retrieving millions of rows with a Select statement is MySQL has to put all the retrieved rows into memory before it starts to process it. 512k is definitely not enough. When I select 2 million rows the RAM drops down to 100mb. I'd like to have 2g and will eventually put some more in. When retrieving millions of rows you are better off using a loop with LIMIT offset,1 or LIMIT offset,10 and process only a subset of rows at a time. Or you can use ranges like rcd_id between (1,10) then use rcd_id between (11, 20) etc. You can also look into the HANDLER sql command that will allow you to fetch rows faster than a Select statement. From the MySQL Manual 6.4.2 HANDLER is a somewhat low-level statement. For example, it does not provide consistency. That is, HANDLER ... OPEN does NOT take a snapshot of the table, and does NOT lock the table. This means that after a HANDLER ... OPEN is issued, table data can be modified (by this or any other thread) and these modifications may appear only partially in HANDLER ... NEXT or HANDLER ... PREV scans. The reasons to use this interface instead of normal SQL are: · It's faster than SELECT because: · A designated table handler is allocated for the thread in HANDLER open. · There is less parsing involved. · No optimiser and no query checking overhead. · The used table doesn't have to be locked between two handler requests. · The handler interface doesn't have to provide a consistent look of the data (for example dirty-reads are allow), which allows the table handler to do optimisations that SQL doesn't normally allow. · It makes it much easier to port applications that uses an ISAM like interface to MySQL. · It allows one to traverse a database in a manner that is not easy (in some case impossible) to do with SQL. The handler interface is more natural way to look at data when working with applications that provide an interactive user interfaces to the database. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problem
On Wed, Aug 06, 2003 at 11:39:26AM -0400, walt wrote: On Wednesday 06 August 2003 11:26 am, Andy Smith wrote: Andy, I just noticed that you have a mix of port numbers. Can you try `netstat -an | grep 3306` from the command line and see if the master is indeed listening on that port? $ netstat -an | grep 3306 tcp0 0 0.0.0.0:33060.0.0.0:* LISTEN There is one more thing I can think of to check... Can you send me a copy of the master.info file. I've had to manually change it before after changing the master in the my.cnf file. I found out later that you could do CHANGE MASTER TO I assume you mean from the slave. This is a newly set up slave specifically for this purpose, so all I did was use CHANGE MASTER.. myself. $ cat mysql/master.info angora-bin.001 20102800 127.0.0.1 repl removed 3306 60 Looks fine to me. :( -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't start MySQL on Mac OS X
Todd and Guys, Your advice helped me too. I think either Apple screwed up when set up /tmp directory to be writeable by root only or since MySQL package has bug or shall be installed ONLY as root user. - Nicos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Corrupt index = mysqld freeze?
On Thu, Aug 07, 2003 at 01:41:57PM -0700, Jennifer Goodie wrote: I have a stand alone database server. It is a RAID5 running mySQL 3.23.55 on FreeBSD 4.1 and has 768MB of RAM, I'm not sure on the processor speed, but I think it's a P3 1GHz. It has several tables with 20-40 million rows and a ton of smaller tables with less than 1 million rows. All tables are MyISAM and we have fewer than 10 queries per second. The super large tables are write only for the most part, with most reads taking place in off peak hours (a cron to generate aggregate data). The smaller tables are read/write. We've been experiencing a problem where mysqld stops responding to new connections. Any active connection is fine and can run any query it wants, but all new connections get stuck in the authenticating user phase. CPU and load drop to about zero when this happens, so I don't think it's the notorious threading issue. This freeze happens when more than 10-12 connections drop at the same time, usually when a queue caused by a table lock clears out. Sounds familiar. One of my coworkers insists that this is due to corrupt indexes, stating that if an index points to a location outside of the record set mysql gets confused and hangs. Does he have any evidence whatsoever for that? I'm 99% sure he's wrong--at least in *our* cases. :-) It has also been stated that multicolumn indexes are a problem, especially if they contain more than 3 columns. This goes against everything I know about mysql. In my experience if there is file corruption an error gets returned promptly. Right. MySQL can detect most corruption. I also believe multicolumn indexes are a valuable feature. I have been told that I need to get rid of all multicolumn indexes in order to make the server stable. Needless to say, I am not very happy with this solution and don't have a lot of faith in it working. Has anyone else experienced anything similar to this, and if so what did you do to fix it? Anyone want to weigh in on the index theory because it doesn't really sound right to me, but I'm not exactly an expert. We've seen that happen too on more recent FreeBSD versions with LinuxThreads. So far it's not happening all that often and it seems that the chance of it happening is much greater right after MySQL has been [re]started. I haven't had much luck in tracking it down further. But I have a few more ideas next time I see it. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 212,550,116 queries (399/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: myisamchk Illegal Instruction
Unfortunatly, I'm stuck using mysqld version 3.23.54 :( Original Message Follows From: Victoria Reznichenko [EMAIL PROTECTED] Date: Fri, 08 Aug 2003 18:29:45 +0300 Paul Mahon [EMAIL PROTECTED] wrote: Hello, I recently managed to corrupt a table pretty badly. I read the sections in the documentation about recovering after a crash. None of the methods worked. All give output similar to the following: % bin/myisamchk -t ~/tmp -f -o BROKE/EventsBROKE - recovering (with keycache) MyISAM-table 'BROKE/EventsBROKE.MYI' Data records: 101333504 bin/myisamchk: error: 127 for record at pos 0 MyISAM-table 'BROKE/EventsBROKE' is not fixed because of errors Try fixing it by using the --safe-recover (-o) or the --force (-f) option % bin/myisamchk -t ~/tmp -e BROKE/EventsBROKE Checking MyISAM file: BROKE/EventsBROKE Data records: 101333504 Deleted blocks: 86920613 bin/myisamchk: warning: Table is marked as crashed and last repair failed - check file-size - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 - check records and index references Illegal instruction The mysql server version is fairly old, 3.23.54 but unfortunatly it can't be upgraded. The table that is corrupted is large... in the range of 14GB. I couldn't find a reference to this error in my searches, so I apologise if this has been covered before, but has anyone encountered these problems before? And more importantly, were you able to fix it? You can repair table with REPAIR .. USE_FRM, but it's supported since 4.0.2. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lowering the ft_min_word_len
Hi!
On Aug 05, Justin Hopper wrote:
On Tue, 2003-08-05 at 10:57, Paul DuBois wrote:
At 10:30 -0700 8/5/03, Justin Hopper wrote:
I have a table with a FULLTEXT index on a column of type 'text'.
Searches on this table using MATCH() AGAINST() work fine for most
words. However, I needed to match against a 3 letter word. So I
lowered the ft_min_word_len to 3 in /etc/my.cnf. I then restarted
MySQL. I checked that the variable was set to 3 in the running mysqld.
mysql select title_id from support_doc_articles where match(article)
against ('dns');
Empty set (0.00 sec)
It does not work IN BOOLEAN MODE either:
mysql select title_id from support_doc_articles where match(article)
against ('dns' IN BOOLEAN MODE);
Empty set (0.00 sec)
Actually, I just tried it again, searching for the 3 letter word 'key',
and it brought back results. Is 'dns' in the stopwords list? Is there
any way I can see what words are in there? Can I exclude words from the
stopword list without recompiling MySQL?
I don't believe you can exclude words from the list without recompiling.
You can, actually, there is ft_stopword_file variable.
Hmmm, any ideas why the word 'dns' would not be picked up then?
no ideas. can you create a test case for me to try it out ?
Regards,
Sergei
--
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't connect to local MySQL question
Hi, I've been writing software on the mac since 1987, but am brand new at unix/php/mysql, and that's where I'm headed so I'm reading everything I can get my hands on, but like anything else there's going to be a learning curve, that having been said... I followed the install instructions at http://entropy.ch , and was able to get mySql php installed on my G4 - (OSX 10.2.6) last night. I downloaded Navicat and setup a password for the mysql user. I created a connection called myDatabase_connection to a database called myDatabase. I was able to get everything running OK. Today when I restarted the machine, I tried to launch the php program that I was running last night, and kept getting an error 2002 below. Could someone take a look at the syntax below and see if you could spot what I am doing wrong? Thank you very much... Bill Hernandez Last login: Sat Aug 9 20:00:07 on console Welcome to Darwin! [Home:~] justMe% su Password: [Home:/Users/justMe] justMe# cd /usr/local/mysql [Home:/usr/local/mysql] justMe# chown -R mysql data/ [Home:/usr/local/mysql] justMe# echo [Home:/usr/local/mysql] justMe# ./bin/mysqld_safe [1] 479 [Home:/usr/local/mysql] justMe# Starting mysqld daemon with databases from /usr/local/mysql/data 030809 20:04:58 mysqld ended HERE I HIT RETURN TO GET THE PROMPT AGAIN (Should I have typed the name of the database here, then hit return?) [1]Done ./bin/mysqld_safe [Home:/usr/local/mysql] justMe# ./bin/mysql myDatabase ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) [Home:/usr/local] justMe# cd / [Home:/] justMe# /usr/local/mysql/bin/mysql myDatabase ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) [Home:/] justMe# mysql myDatabase ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) [Home:/] justMe# -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't connect to local MySQL question
Andreas wrote: Bill Hernandez wrote: I followed the install instructions at http://entropy.ch , and was able to get mySql php installed on my G4 - (OSX 10.2.6) last night. I downloaded Navicat and setup a password for the mysql user. I created a connection called myDatabase_connection to a database called myDatabase. I was able to get everything running OK. Did the server start and could you connect ? Today when I restarted the machine, I tried to launch the php program that I was running last night, and kept getting an error 2002 below. 1) Have the mysql daemon running 2) connect with a client [Home:/usr/local/mysql] justMe# ./bin/mysqld_safe [1] 479 [Home:/usr/local/mysql] justMe# Starting mysqld daemon with databases from /usr/local/mysql/data 030809 20:04:58 mysqld ended there is an error.log in the data directory. Look there for mysql's complaints. HERE I HIT RETURN TO GET THE PROMPT AGAIN (Should I have typed the name of the database here, then hit return?) no You are mixing up the server and client process. mysqld is the server. It runs in the background and stores databases in it's data directory. There can be many separate databases. You'll select one of them later when you connect with the client. mysql -- no d (=daemon) is the textmode client that comes with the mysql package. [1]Done ./bin/mysqld_safe [Home:/usr/local/mysql] justMe# ./bin/mysql myDatabase ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) Without running server-process there is no socket to connect to. start by looking at the err-file in the data-dir. You can run mysqld_save without the to see more output. Sometimes the server comes up that way. Then you wont regain access to the shell where you ran mysqld_save. I think it is mysqld_safe! I had a hard time getting this kind of error. One day I figured out, that mc caused the hick-up. mc is a textmode filemanager I really use often. mysqld started and died at once again. If mc produces this effect then perhaps other filemanaging tools do, too. Use pure bash or xterm. Better yet, install mysql as a service to have it started at boot time. -- Peter K. Aganyo Eternal Designs Inc., +1(617)344-8023 (fax voicemail)
Problem with date query
I have a table with a column date that contains dates in SQL format, i.e. 2003-08-10 I need to query the table and return all the rows from the current date thru the next 6 days. (i.e. today 2003-08-10 thru Saturday 2003-08-16). I have tried the following query which returns all of the desired rows except those for the current date. How can I correct this? SELECT date, time, am_pm, tz, height, cond FROM cherry_point_tides WHERE TO_DAYS(date) - TO_DAYS(NOW()) = 6 and date NOW(); Thanks, Jack -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql shuts down overnight
Does it restart or just shut down? Do you have to start it up in the morning? And is there a specific time at which this happens? If you don't have logging on, you should consider turning it on for debugging purposes. After it happens again, check your error log (should be in your data directory - mine is in /usr/local/mysql/var/) - it should be a text file with a filename that ends with .err You might also want to check your /var/log/messages file for anything that looks suspicous or related to this. - Jonathan P.S. This probably isn't related to MyODBC, so for future posts in this thread, I'll only respond to the main MySQL list. - Original Message - From: Chen, Mao [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, August 06, 2003 11:06 AM Subject: mysql shuts down overnight Hi everyone, I got apache 2.0 + MySql 3.23.52 + PHP 4.3.1 on a Redhat 8.0 server. Somehow MySQL automatically shuts down overnight, anyone has a reason for this? Might because of cron? Thanks in advance! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: arbitrary ordering
David, Firstly, to answer your question I don't know of a MySQL function that may allow you to sort indirectly by the contents of a field - there are many many functions and some of them are very specialised, and if you ever move to another db all those great little functions may not be there. However, there is a another, more standard (and IMHO better) way: It's generally not a great idea to store lists of things in a single field. One reason is that you've allocated n chars for the question order, but what if a survey suddenly gets twice as many questions? - You then need to resize your columnns or make them all huge to start with. The other reason is that it creates problems like the one you're having here. To avoid lists in a field, you need to create additional tables to store the lists, but then the problem you have of sorting goes away as if by magic. Here's an example for survey and question (note, I've renamed you original SURVEY_QUESTION table to QUESTION): create table SURVEY ( SID int primary key not null auto_increment, SNAME varchar(20), ) ; create table QUESTION ( QID int primary key not null auto_increment, QBODY varchar(255), ) ; create table SURVEY_QUESTION ( SID int , (you'd probably make these 2 fields the primary key) QID int , QUESTION_ORDER int ) ; Now you can have the same question in many surveys and the QUESTION_ORDER field in SURVEY_QUESTION allows you to sort the questions in any order for that survey. (You just put arbitary numbers in QUESTION_ORDER to make the order work e.g. 10, 20, 30 - by using multiples of 10 you can insert a question without renumbering the order.) I hope that helps, Andy David T-G wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, all -- I have a few tables something like create table SURVEY ( SID int primary key not null auto_increment, SNAME varchar(20), QUESTION_ORDER varchar(20) ) ; create table SURVEY_QUESTION ( QID int primary key not null auto_increment, QBODY varchar(255), OPTION_ORDER varchar(20) ) ; create table SURVEY_OPTION ( OID int primary key not null auto_increment, OBODY varchar(255) ) ; and in the QUESTION_ORDER field I store a list of numbers 12 13 21 14 while in OPTION_ORDER it might be 432 435 435 550 or such. I want to do a select on all three (double left join, if I am starting to grasp this stuff :-) ordered first by the QID as shown in QUESTION_ORDER and then by the OID as shown in OPTION_ORDER. Is there a way to tell my join-and-select statement the sort order based on the contents of another field (but not simply sorting on that field)? TIA HAND :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE/NHTHGb7uCXufRwARAtPvAKDql3YjpBwwEpS5trzncnOzeTjXUACfZo93 Kep54aY/EeVXaCXXlItbKl0= =qi7g -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Tracking a delete
Ok, I know it WAS there because we have two similar tables that should contain sister records. One table has a record the other doesn't so it had to have been deleted. I need to find out WHEN it was deleted. How do I create a log of record deletes? If you keep the update log or the binlog you can look through it. Ok, replication is on so that means I have a bin log, now how do look through it? This is mysql 3.23 on linux. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL field data type for ISBN numbers
Hi, I have a MySQL database for books. The ISBN field is set as varchar(15) and I've put a test ISBN number in of 1--111-11. Can someone tell me why this SQL query isn't working? $ISBN = $_GET['isbn']; $query = SELECT * FROM book_details WHERE ISBN = '$ISBN'; Since the field is a varchar, should wrapping the variable (1--111-11) in single quotes work? If I change the data in the table to 'abcd' and run this query $ISBN = 'abcd'; $query = SELECT * FROM book_details WHERE ISBN = '$ISBN'; It works. PHP Version 4.3.2 mysql-4.0.14b Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with count(*)
I have a table I'm using for logging purposes with a schema like: create table results ( user varchar(255) ); Where user is not a unique field and I want to find out how many unique users there are in the table. I want to do something like: select count(count(*)) from results group by user; But that doesn't work.. Any ideas? Thanks _ STOP MORE SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL field data type for ISBN numbers
Perhaps just something trivial but both numbers are differing: SELECT * FROM book_details WHERE ISBN = '1---1' and and I've put a test ISBN number in of 1--111-11 In any case I have ran a test on my servers with mysql V 4.0.13 and things work accordingly. Thanks, Aaron Holmes [EMAIL PROTECTED] CEO Gurix Web Professionals www.gurix.com - Original Message - From: James Johnson [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, August 10, 2003 8:58 PM Subject: RE: MySQL field data type for ISBN numbers Hi Daniel, Here's what is echoed back. It looks valid to me. SELECT * FROM book_details WHERE ISBN = '1---1' James -Original Message- From: Daniel Kasak [mailto:[EMAIL PROTECTED] Sent: Sunday, August 10, 2003 5:40 PM To: James Johnson; [EMAIL PROTECTED] Subject: Re: MySQL field data type for ISBN numbers James Johnson wrote: Hi, I have a MySQL database for books. The ISBN field is set as varchar(15) and I've put a test ISBN number in of 1--111-11. Can someone tell me why this SQL query isn't working? $ISBN = $_GET['isbn']; $query = SELECT * FROM book_details WHERE ISBN = '$ISBN'; Since the field is a varchar, should wrapping the variable (1--111-11) in single quotes work? If I change the data in the table to 'abcd' and run this query $ISBN = 'abcd'; $query = SELECT * FROM book_details WHERE ISBN = '$ISBN'; It works. PHP Version 4.3.2 mysql-4.0.14b Thanks, James Try 'echo'ing $query to your browser and then copying the query from your browser into the mysql client and see if it works. I suspect there may be something wrong with the data after the $ISBN = $_GET['isbn'] bit, and echoing the query might make it more obvious what the problem is. -- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with data import from text file (part two)
adrian GREEMAN wrote:
I have also read the manual section on this {as I should have
done] to try and understand it
and how to enable reading a local file - the discussion section
seems confusing and confused.
I tried following what others have done and
modified the ini file with set-variable=local-infile=0
and with set-variable=local-infile=1 and with
set-variable=local-infile[=1]
As a command-line option to mysql the first equal sign should not be there
and as of 4.0 set-variable is depreciated and should be left out all
together.
AFAIK set-variable is not to be used within the configuartion file(s)
I obviously need to do something different
For you and me, while both server and client are on the same system, the
LOCAL phrase is redundant, just do without it for now ...
HansH
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please Help
I'd cross post to the mysql-java/jdbc mailing list... Most likely you
need to modify mysql config to allow larger packet sizes.. search the
list archive/website for max_allowed_packet info..
On Fri, 8 Aug 2003, Ma Mei wrote:
Dear administrator,
Now I have a quesion and want to get your help.
When I insert an image file data (data size 64KB) to a BLOB field of MySQL
database by com.mysql.jdbc.driber,there is error. Error message as follows:
aq.executeQuery:Communication link failure:comm.mysql.jdbc.packetTooBigException
The part of my program as follows:
..
FileInputStream fis = new FileInputStream(untitl2.gif);
..
conn =
DriverManager.getConnection(jdbc:mysql://dbgserver.ihep.ac.cn/bsrf?user=bsrfpassword=bsrfuseUnicode=truecharacterEncoding=Gb2312);
String ins =insert into myimg values(?,?);
PreparedStatement stmt = conn.prepareStatement(ins);
System.out.println(Test1*);
stmt.setInt(1,1001);
try
{
int len= fis.available();
System.out.println(len);
stmt.setBinaryStream(2,fis,len);
System.out.println(Test***2*);
int rowsupdated = stmt.executeUpdate(); // When program run
in here , it appears error.
System.out.println(Test3*);
System.out.println(RowsUpdated= +rowsupdated);
}
catch(IOException ex) {
System.out.println(IOException:+ex.getMessage());}
Could you help to relve this quesion as soon as. Thank you very much.
I am looking forward ro hearing from you.
Best Regards,
Ma Mei
---
Ma Mei
Computing Center
Institute of High Energy Physics
P.O.Box 918 Ext.7
Beijing 100039
P.R. China
Phone: (8610) 88235037
FAX: (8610) 88236839
E-Mail: [EMAIL PROTECTED]
-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Checking the mysql status
this is a Perl script, not shell ... #!/usr/bin/perl use strict; use DBI; # change the next four to match your network my $SLAVE_IP= '0.0.0.0'; my $MASTER_IP = '0.0.0.0'; my $USER= 'user'; my $PASSWORD= 'password'; my ( $dbh, $sth, @masterResult, @slaveResult, $i ); $dbh = DBI-connect(dbi:mysql:database=test;host=$MASTER_IP, $USER, $PASSWORD); $sth = $dbh-prepare(show master status); $sth-execute(); @masterResult = $sth-fetchrow_array; $sth-finish; $dbh-disconnect; $dbh = DBI-connect(dbi:mysql:database=test;host=$SLAVE_IP, $USER, $PASSWORD); $sth = $dbh-prepare(show slave status); $sth-execute(); @slaveResult = $sth-fetchrow_array; $sth-finish; $dbh-disconnect; ($masterResult[1] == $slaveResult[16]) ? print 'OK' : print 'Error'; # hcir Hello, mysql, I want to write a shell script to check the slave synchronize with master failed or sucessfully. Any idea to implement this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with count(*)
At 08:15 PM 8/10/2003, you wrote: I have a table I'm using for logging purposes with a schema like: create table results ( user varchar(255) ); Where user is not a unique field and I want to find out how many unique users there are in the table. I want to do something like: select count(count(*)) from results group by user; But that doesn't work.. Any ideas? Thanks Try select user, count(*) Num from results group by user you can also sort the results by: select user, count(*) Num from results group by user order by Num Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
