Re: find a particular record.
I am doing this to move data from one table to other and getting below error. How can check this particular record at this row number 921888 in dats_tem table. insert into reports1.dats1 select * from dats_tem; ERROR 1292 (22007): Incorrect datetime value: '-00-00 00:00:00' for column 'P_LAST_SOLD_DATE' at row 921888 An extremely quick and dirty solution: mysqldump -uusername -p -t --skip-opt database_name dats_tem | grep INSERT | head -921888 | tail -1 replacing username with your username and database_name with your database name, of course. -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: General Query Log -- No Timestamp
On 6/26/07, Brown, Charles [EMAIL PROTECTED] wrote: The MYSQL general query log does not include timestamp of queries that it logs because queries are logged many many seconds before they are executed. Which version of MySQL are you running? I'm running 5.0.22 on my desktop, but I'm fairly sure that older versions included timestamps in the general query log. I just enabled mine to test this (by adding log = /var/log/mysql.log to /etc/my.cnf) and it looks something like: 070629 8:17:44 6 Connect [EMAIL PROTECTED] on monitoring 6 Query set autocommit=0 6 Query select * from urls where active=y 070629 8:17:45 6 Query INSERT INTO results VALUES (NULL,5,now(),0.5833,35267) 6 Query INSERT INTO results VALUES (NULL,6,now(),0.0137,0) 6 Query INSERT INTO results VALUES (NULL,8,now(),0.7762,28130) 6 Query INSERT INTO results VALUES (NULL,9,now(),0.0348,4217) -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Benchmarking
On 3/14/07, Clyde Lewis [EMAIL PROTECTED] wrote: System Configuration: Sun Microsystems sun4u Sun Fire E2900 System clock frequency: 150 MHZ Memory size: 65536 Megabytes CPU: 12 @ 1200 MHz I'm looking for a tool that will allow us to determine the max number of databases that can run in a single instance of MySQL on a pretty beefy server( Spec above). In total we will have about ~40 MySQL instances running on this server. Each instance of MySQL, there will have between 30-60 individual databases supporting an OLTP application. I know that there are no know internal limits that MySQL have regarding the number of databases that can be created, but I would like get my hands on a tool that can simulate the number of databases and identify where we would potentially run into performance issues. As I mentioned above, your performance issues are going to come not from the number of databases, but from (primarily) how well-designed your database tables and queries are, and (secondly) how you configure the mysql server(s). One important factor to bear in mind is that with 40 separate MySQL instances on the single 64GB server, you will have a maximum 1.6GB of RAM per instance (excluding memory used by the O/S and other applications). This will have to be divided up between the various memory buffers (key_buffer, innodb_buffer_pool, etc.) allocated by each mysql process, so you might want to reconsider if you really need to run 40 separate mysql processes, or whether all the databases can live in the same MySQL instance and thus probably make better use of the available RAM. With regards to stress-testing and benchmarking, two popular tools for benchmarking MySQL servers are: Super Smack: http://vegan.net/tony/supersmack/ Sysbench: http://sysbench.sourceforge.net/ We need to determine whether to have multiple servers to support the ~40 instances or have all ~40 instances on the same machine. Any help of ideas would be greatly appreciated with this decision. I would be inclined to have separate machines, rather than put everything on one huge server. By spreading the data around, you are reducing the risk if the one mega-machine were to become unavailable, and also reducing resource contention (on the disks, CPU, RAM etc.). -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how would mysqld restart affect dynamically set global variables?
We're running mysql 4.1.20. If I understand the manual correctly, I can change max_connections while mysqld is running without restart mysqld to make the change take effect Correct. But what if mysqld restarts later in some other situations, like machine reboot, would my (global) change on max_connections remain? No; you'll need to put it in my.cnf in order for it to persist after mysqld is shut down. -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Repair query overloading server
On 3/8/07, Gary Sewell [EMAIL PROTECTED] wrote: I'm having a problem with a single repair query seemingly taking over the whole server. I'll try and explain…. Large table or 1.2m rows, running a repair on this causes the query queue to grow and grow and finally max out. The problem isn't table locks because no-one else is using the test database I am repairing. Assuming you're running both the test database and the live database on the same physical server, it sounds like the problem could be that the table repair is starving the disks I/O-wise, causing other queries to run very slowly. If at all possible, try and move the test database to its own physical hard drive (or ideally, to its own hard drive on a separate disk controller); that way, any disk-intensive table repairs won't affect disk performance of the live database. Or move the test database to its own server; running test and live systems on the same physical server is asking for trouble! Even the most simple select queries on the live database are taking 500 secs!! I want the repair to run but not to the detriment of other queries. This problem will be a lot worse if the queries aren't well optimised; have you checked them with EXPLAIN? 500 seconds does indeed sound a bit excessive, even for a system under heavy disk I/O load. Once the repair is over it can take about 3 minutes before the queue starts to evaporate also, shouldn't it kick back into gear as soon as the repair is over? In my experience, It can take a while for the kernel to re-balance things. Caching of MyISAM data pages is managed by the operating system's page cache, and not by MySQL; if a large table has to be re-built, this will most likely have an adverse effect on MyISAM performance until things have balanced themselves out. -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb corruption help needed!
On 3/11/07, Jean-Sebastien Pilon [EMAIL PROTECTED] wrote: I am running a mysql database server and we experienced a power failure. The mysql server does not want to restart because innodb is corrupted. Version info: Mysql version 4.1.11-Debian_4sarge7-log Debian sarge Reiserfs filesystem What I have tried: - Ran reiserfsck to fix corrupted file system. It fixed some problems, now says no corruption. - Restarted mysql with no success - Restarted mysql with innodb_force_recovery = 4 option, with no success - Restarted mysql with innodb_force_recovery = 6 option, mysql starts, but cannot access any data in my different schemas/tables See below for mysql startup log What should I do next ? Unfortuantely, it looks like your InnoDB tablespace is beyond recovery. If this data is critical and you don't have a backup, it might be worth getting MySQL Enterprise Support for this server and have them try and recover it -- see https://shop.mysql.com/enterprise/ for details. Assuming you have a backup of your data and you want to get the server back up and running so you can restore from this backup, you need to shut down the server, delete the corrupted InnoDB tablespace and associated .frm files, and start it up again (which will cause the server to re-create the tablespace from scratch): 1. Shut down mysql (/etc/init.d/mysql stop on Debian, iirc) 2. Go into your data directory, and run: a. rm -f ib* b. for f in `find -name \*.ibd`; do b=`echo $f | sed 's/.ibd//g'`; rm -f $b.ibd $b.frm ; done 3. Start the server again (/etc/init.d/mysql start on Debian, iirc) 4. Restore your backup (I'm assuming this is in a .sql file) by running the relevant sections back through the mysql program. By the way, a power outage shouldn't cause the InnoDB tablespace to become corrupted like this, it's ACID-compliant. It's possible that your disk system is doing some caching you don't know about, resulting in MySQL thinking data is on disk (or guaranteed to be put on disk) when it actually isn't. See http://brad.livejournal.com/2116715.html for details on how to test (and fix) this. -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Default root password not blank?
On 3/13/07, Merlin [EMAIL PROTECTED] wrote: I just compiled mysql4.1.22 on a suse 9.3 machine. All went fine, but when I try to change the password I get prompted for one. So I hit return as it is supposed to be blank, right? But it seems to be not: /usr/local/mysql/bin # ./mysqladmin -u root -p password testpw Enter password: ./mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' if I do the command without -p I get: ./mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' Try: ./mysqladmin password testpw -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Gaining statistics from MySQL
On 2/20/07, Clyde Lewis [EMAIL PROTECTED] wrote: I'm looking to find a way to determine the number of transactions that a particular database is processing each min/hour/day/month/year http://dev.mysql.com/doc/refman/4.1/en/show-status.html http://dev.mysql.com/doc/refman/4.1/en/server-status-variables.html Looks like Com_commit is what you're looking for (though you can replace commit with most other types of query, i.e. Com_select, Com_insert...). This is a counter, so you'll need to record the value periodically and work out the differences for a period. -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is the db.opt ?
Would like to know what is the archive db.opt. http://www.google.co.uk/search?q=mysql+db.optstart=0ie=utf-8oe=utf-8client=firefox-arls=org.mozilla:en-US:official -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication error: Unable to get certificate
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /root/.mysql/cacert.pem
Master_SSL_CA_Path: /root/.mysql/
Master_SSL_Cert: /root/.mysql/client-cert.pem
Master_SSL_Cipher:
Master_SSL_Key: /root/.mysql/client-key.pem
Seconds_Behind_Master: 0
[...]
SSL:3017771936:error:0200100D:system library:fopen:Permission
denied:bss_file.c:352:fopen('/root/.mysql/client-cert.pem','r')
MySQL (which runs as the mysql user) isn't going to be able to read
the certificates out of root's home directory, which is only readable
by root.
Put the certificates somewhere where the mysql user can read them -
your data directory would be a sensible place.
-- Alex
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to keep myqsl backup server
I need to create an application where i will be requiring a backup server like when one server fails i can switch automatically to the other server hosted somewhere else , now the data needs to be consistent on both the servers, one sol is i keep automated backups on the primary server and restore it by a script on the backup server You want replication, which will automatically keep your data up-to-date between two servers in near-real-time. See http://dev.mysql.com/doc/refman/5.0/en/replication.html Note: You will need to configure master-master replication if you want to start writing to the other server if the master fails, otherwise your data will be inconsistent. but i need to know how will i switch between servers and if i use domain names instead of ips how will i make immediate switch. You probably shouldn't use DNS entries for an application that requires immediate fail-over; configure your application to connect to an IP address, then change this IP address if you have to fail over to the backup server. -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Band width consume
On 12/4/05, Luiz Rafael Culik Guimaraes [EMAIL PROTECTED] wrote: Dear Friends Is their an Page with data of how much bandwidth mysql consume when using remote servers? mysql show status like 'Bytes_%'; +++ | Variable_name | Value | +++ | Bytes_received | 1718034415 | | Bytes_sent | 2783965287 | +++ 2 rows in set (0.00 sec) This will give you an incrementing value that you can plot on a graph using MRTG (www.mrtg.org) or similar. -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Data sync offline
Hi, Our company is considering migrating some tablesfrom MyISAM to InnoDB, as it has row-level locking and other improvements over MyISAM. However, one of the things we do at the moment is rsync the MySQL data directory to our development server every night over an 2Mbps ADSL connection (as we have 40GB of data, downloading it all every night isn't a viable option). I am aware that you can't copy the InnoDB binaries around to different installations. Does anyone have any suggestions as to how we could continue to do incremental downloads of this data over a slow connection? Regards, -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to limit log space usage?
Nico Sabbi wrote: [...] There are 3 GB of logs that no one needs anymore. Since the master knows that all the slaves are correctly aligned up to a certain MASTER_LOG_POS, can't it automatically remove the logs up to that position? The problem here is that MySQL masters aren't necessarily aware of all their slaves, so it has no way of telling if they're all caught up with the replication. The best way to solve this problem is to write a Perl script to connect to each slave periodically and check the current log, and then purge all the logs previous to the least-caught-up slave's current log. -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem installing without the server
Juan Fernandez wrote: Hi !! I am trying to install mysql without the server on fedora: when I issue the command : [EMAIL PROTECTED] snort-2.2.0]# cd ../mysql-3.23.52 [EMAIL PROTECTED] mysql-3.23.52]# ./configure --without-server --prefix=/usr/local/mysql everything is fine but after I issue the command make I receive the following: [EMAIL PROTECTED] mysql-3.23.52]# make message trounced mysql.o(.text+0x1849): In function `com_source(String*, char*)': : undefined reference to `errno' collect2: ld returned 1 exit status make[2]: *** [mysql] Error 1 make[2]: Leaving directory `/snortinstall/mysql-3.23.52/client' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/snortinstall/mysql-3.23.52' make: *** [all-recursive-am] Error 2 and if i issue the command make install I receive: [EMAIL PROTECTED] mysql-3.23.52]# make install Making install in include Message trounced mysql.o readline.o sql_string.o completion_hash.o ../readline/libreadline.a -lncurses ../libmysql/.libs/libmysqlclient.so -lz -lcrypt -lnsl -lm -lz -lcrypt -lnsl -lm -Wl,--rpath -Wl,/usr/local/mysql/lib/mysql mysql.o(.text+0x1849): In function `com_source(String*, char*)': : undefined reference to `errno' collect2: ld returned 1 exit status make[1]: *** [mysql] Error 1 make[1]: Leaving directory `/snortinstall/mysql-3.23.52/client' make: *** [install-recursive] Error 1 can someone Please help ? I just need the client to use it with Snort IDS !!! thanks !! Just install the Fedora MySQL RPM (i.e. not mysql-server, etc) from the distribution CD. -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem installing without the server
Juan Fernandez wrote: I see 3 packges: Mod_auto_mysql-20030510-3.i386.rpm Mysql-3.23.58-4.i386.rpm Php-mysql. Which one to pick Thanks !! Mysql-3.23.58-4.i386.rpm -- Alex -Original Message- From: Alex Greg [mailto:[EMAIL PROTECTED] Sent: Monday, September 13, 2004 4:06 PM To: Juan Fernandez Cc: '[EMAIL PROTECTED]' Subject: Re: problem installing without the server Juan Fernandez wrote: Hi !! I am trying to install mysql without the server on fedora: when I issue the command : [EMAIL PROTECTED] snort-2.2.0]# cd ../mysql-3.23.52 [EMAIL PROTECTED] mysql-3.23.52]# ./configure --without-server --prefix=/usr/local/mysql everything is fine but after I issue the command make I receive the following: [EMAIL PROTECTED] mysql-3.23.52]# make message trounced mysql.o(.text+0x1849): In function `com_source(String*, char*)': : undefined reference to `errno' collect2: ld returned 1 exit status make[2]: *** [mysql] Error 1 make[2]: Leaving directory `/snortinstall/mysql-3.23.52/client' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/snortinstall/mysql-3.23.52' make: *** [all-recursive-am] Error 2 and if i issue the command make install I receive: [EMAIL PROTECTED] mysql-3.23.52]# make install Making install in include Message trounced mysql.o readline.o sql_string.o completion_hash.o ../readline/libreadline.a -lncurses ../libmysql/.libs/libmysqlclient.so -lz -lcrypt -lnsl -lm -lz -lcrypt -lnsl -lm -Wl,--rpath -Wl,/usr/local/mysql/lib/mysql mysql.o(.text+0x1849): In function `com_source(String*, char*)': : undefined reference to `errno' collect2: ld returned 1 exit status make[1]: *** [mysql] Error 1 make[1]: Leaving directory `/snortinstall/mysql-3.23.52/client' make: *** [install-recursive] Error 1 can someone Please help ? I just need the client to use it with Snort IDS !!! thanks !! Just install the Fedora MySQL RPM (i.e. not mysql-server, etc) from the distribution CD. -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Duplicate entry breaks replication
Hi, We recently moved to new servers, and also upgraded from MySQL 3.23 to 4.0.20. We have one master database server and a second identical machine configured to replicate all databases from the master. Previously this has worked without a hitch, but we seem to be having some problems. The way we did the transition was as follows: 1) On old database server, issued FLUSH TABLES WITH READ LOCK; command 2) Rsynced the data directory onto the new server (db1) 3) Locked the new database server 4) Rsynced the data directory onto the new server (db2) 5) Configured replication and started it up This works, but we keep getting errors like Error 'Duplicate entry '53751556' for key 1' on query. Default database: 'archive'. I know what this means, but how come we are getting them if the two servers started off with an identical snapshot of data. Also, should issuing an insert that fails in this nature break replication? Regards, -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bad too many connections error (os x)
Michael Winston wrote: On Sep 1, 2004, at 9:10 AM, V. M. Brasseur wrote: Michael Winston wrote: Hi- We've been running into a pretty serious problem for the past several versions of mysql 4.0 running on OS X (both client and server). Every once in a while we wake up to find the too many connections error coming up. There really aren't too many connections (we have our max set to 99) - it's the type of message that appears when a wrong password is used too many times (and I'm 100% sure this isn't happening). Now, the problem is that once this message starts appearing we can't even connect with mysqladmin as root. That extra connection that mysql promises doesn't exist. The only way we can shut down mysql is to perform a 'kill -9' (then restart the server and repair all the tables). And we can't reproduce this problem at will. This is driving us nuts. Before I report this as a bug I wanted to know if anyone else has seen something like this or has any suggestions of how to narrow down the problem. Thanks! Michael We've run into this problem ourselves, also using 4.0 but on a 64bit AIX. The problem we found was that some queries were firing off threads which never ended. These threads blocked other threads, which blocked other threads... A logjam resulted with all connections ended up being used by the offending threads. The fix was to *ahem* fix our queries so they'd close their database connections once they were complete. You may wish to do a code inspection and verify that every open connection has a matching close. Hmmm. All of our connections are coming from php-generated web pages. PHP automatically closes the connection at the end of the script. Unless I completely misunderstand how this stuff works. Plus, this problem only happens once every few weeks. If some of our queries are causing this, I would expect the problem to occur more often. I'll look into this, though. Thanks, Michael You'll also find this problem if you have some badly-optimised queries, or writes that take a long time to run on a frequently-accessed table. For example, if you have a table that frequently accessed and run a slow update on it, any thread trying to read from that table will block. If you get more selects happening to that table coming in while it's still locked, your number of connections in use will shoot upwards rapidly until the slow update finishes and the table is unlocked. Have a look in your slow query log (or turn it on if it's not enabled) to look for any queries like this. Regards, -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't connect to MySQL server - Operation now in progress
Hi,
I'm noticing this error in various script of ours that run on the cron:
DBI connect('general:10.1.1.80:3306;mysql_connect_timeout=3','root',...)
failed: Can't connect to MySQL server on '10.1.1.80' (115) at
/usr/local/apache/cgi-bin/Shared/DBStuff.pm line 214
This happens on a number of our servers, in different hosting centres,
connecting to different database servers, running different versions of
MySQL, so I don't think this points to any specific
hardware/software/network issues.
One set of servers are running Red Hat Linux versions 6.2 to RHEL 2.1,
networked over 10/100 ethernet, connecting to MySQL on a remote host
(version 3.22.32) via Perl DBD::mysql.
Another set of servers are running Red Hat Enterprise Linux 2.1,
networked over 10/100 ethernet, connecting to MySQL on a remote host
(version 3.23.55) via Perl DBD::mysql.
The third set of servers are running Fedora Core 1, networked over
gigabit copper ethernet, connecting to MySQL on a remote host (version
4.0.20) via perl DBD::mysql.
Any advice or pointers anyone can give on this would be greatly appreciated.
Regards,
-- Alex
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database recovery
Hi, I have recently had a system crash that required the installation of a new hard drive. I have access to the files on the old hard drive, on which is a database I need to recover. I am running MySql 3.23.37 and all the tables in the database to be recovered are MyISAM. How can I recover the old database onto a new server with only file access? The documentation suggests to me that I need to copy all the *.frm, *.MYD, and *.MYI files. If this is the case, where should I copy them to on the new server? Each table in MySQL is represented by a MYD, MYI, and frm file. These are collected in directories, each of which represents a database. So copy the files, along with the directories they were in (which represent the databases), except for the mysql database, into the MySQL data directory. For example, if you used the RPM install, this directory will be /var/lib/mysql, or wherever you extracted the binary install to if you've used the binary installation. Then restart MySQL, and it will pick up the databases. -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Maximum Database size
Stefan, it depends ... For MyISAM tables max size is limited by the maximum filesize of your OS/Filesystem. Following on from that, the maximum file size you can have on 32-bit Linux is 2GB, unless your distro has LFS (Large File Support), in which case is is much larger (terabytes, not sure exactly how big). I know that Fedora Core 1 has LFS, and RedHat Linux 9 might do as well. For Windows under NTFS, the file size limit is 2TB, on FAT32 it is 4GB. -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie - dependencies
Having just set up a RedHat Enterprise Server V.3 I am trying to add a MySQL database function. I tried and failed using up2date (following instructions from RH), so downloaded the rpms and began to install by myself. My input and the output is shown below: # rpm -i mysql-server-3.23.58-1.i386.rpm error:Failed dependencies: libmysqlclient.so.10 is needed by mysql-server-3.23.58-1 Suggested resolutions: mysql-3.23.58-1.i386.rpm # rpm -i mysql-3.23.58-1.i386.rpm error:Failed dependencies: perl-DBD-MySQL is needed by mysql-3.23.58-1 Suggested resolutions: perl-DBD-MySQL-2.1021-3.i386.rpm # rpm -i perl-DBD-MySQL-2.1021-3.i386.rpm error:Failed dependencies: libmysqlclient.so.10 is needed by perl-DBD-MySQL-2.1021-3 Suggested resolutions: mysql-3.23.58-1.i386.rpm Which takes me back to the second line and begins a loop. Libmysqlclient.so.10 is in /usr/lib/mysql/ problably from the initial setup (before I found out that the mysql-server wasn't on the setup CDs). I went for RHEL on the strength that it has up2date and I would not meet this kind of problem. Anyone got any ideas/get out of jail cards? rpm -ivh perl-DBD-MySQL-2.1021-3.i386.rpm mysql-server-3.23.58-1.i386.rpm mysql-3.23.58-1.i386.rpm -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie - dependencies
Having just set up a RedHat Enterprise Server V.3 I am trying to add a MySQL database function. I tried and failed using up2date (following instructions from RH), so downloaded the rpms and began to install by myself. My input and the output is shown below: # rpm -i mysql-server-3.23.58-1.i386.rpm error:Failed dependencies: libmysqlclient.so.10 is needed by mysql-server-3.23.58-1 Suggested resolutions: mysql-3.23.58-1.i386.rpm # rpm -i mysql-3.23.58-1.i386.rpm error:Failed dependencies: perl-DBD-MySQL is needed by mysql-3.23.58-1 Suggested resolutions: perl-DBD-MySQL-2.1021-3.i386.rpm # rpm -i perl-DBD-MySQL-2.1021-3.i386.rpm error:Failed dependencies: libmysqlclient.so.10 is needed by perl-DBD-MySQL-2.1021-3 Suggested resolutions: mysql-3.23.58-1.i386.rpm Which takes me back to the second line and begins a loop. Libmysqlclient.so.10 is in /usr/lib/mysql/ problably from the initial setup (before I found out that the mysql-server wasn't on the setup CDs). I went for RHEL on the strength that it has up2date and I would not meet this kind of problem. Anyone got any ideas/get out of jail cards? rpm -ivh perl-DBD-MySQL-2.1021-3.i386.rpm mysql-server-3.23.58-1.i386.rpm mysql-3.23.58-1.i386.rpm -- Alex - Cheers for the reply Alex. Tried your method and got: MySQL-server conflicts with mysql-server-3.23.58-1 mysql conflicts with mysql-3.23.58-1 You have a previous version of MySQL installed, from the mysql.com RPM's. To get rid of it, do: rpm -e 'MySQL*' first, then: rpm -ivh perl-DBD-MySQL-2.1021-3.i386.rpm mysql-server-3.23.58-1.i386.rpm mysql-3.23.58-1.i386.rpm I tried 'locate' but could not find either file, so then tried 'rpm -e' of both When you use rpm -e, you give it a package name (e.g. mysql-3.23.58-1.i386 or mysql) not a file name. -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hitting max_connections - safe to raise this?
Alex Greg wrote: Hi, Our main database server is a 2 x PIII 1.2Ghz with 1.5GB RAM and a 73GB SCSI RAID-5 made up of 3 36GB disks. It does between 300 and 1200 queries per second. The read to write ratio is about 4:1. My problem is that we're hitting our max_connections more and more frequently. Is it safe to raise this to (say) 200, or will this cause performance problems? Already the machine is using up a lot of swap; would you recommend that I bump the RAM up to 2GB, or should I bring down the key_buffer_size in order to fit everything into physical RAM? Should I be concerned about the load average of the machine - it goes up to 6 at some points in the day. max_connections is more like a speed governor in a car. The default is set for use by a not so responsible teenager that may want to try to impress his girlfriend with his driving skills. If you are driving an ambulance, it's ok to raise the limit. Set it to 1000 or even 2000 and just watch your system to make sure you have enough resources. Thanks for the advice :) My only concern about raising max_connections over 100 is resources; each MySQL thread takes up 70MB RAM, and this machine is basically out of physical memory. There is around 400MB of stuff permanently lodged in swap (probably causing the high I/O you mentioned); surely if I raise the number of connections, this will cause more stuff to be put into swap, and hence cause performance to deteriorate? This is why I was thinking of raising the amount of physical RAM from 1.5GB to around 2.5GB-3GB - what are your thoughts on this? Are there any other performance tips that anyone can give based on this configuration? If you need more information, please let me know. Spikes in connections are usually a symptom of inefficient queries. Police your slow log (turn on log-slow-queries and log-long-format) and explain every query you find there starting with the ones that examine most rows. Thanks for the advice - I will endeavour to do this at some point this week. I also noticed very high system CPU. This usually means you are doing more I/O that you should. Ideal ratio (at least on Linux) is 70 % user/ 30% system. If you lean a lot more towards user, something is wrong with the libraries or mysqld itself to make it spin wheels, if you lean a lot the other way, you are probably scanning tables a lot. Could this be caused by the machine swapping? Do not sweat over the server buffer size tuning too much - compared to query optimization, there is rarely much room for improvement over the defaults, but there is quite a bit more room for trouble if you get too excited. Tune them after your slow log is either empty or at least every query is accounted for. Will do. Thanks very much for your advice, Sasha. Best Wishes, -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Hitting max_connections - safe to raise this?
Hi, Our main database server is a 2 x PIII 1.2Ghz with 1.5GB RAM and a 73GB SCSI RAID-5 made up of 3 36GB disks. It does between 300 and 1200 queries per second. The read to write ratio is about 4:1. My problem is that we're hitting our max_connections more and more frequently. Is it safe to raise this to (say) 200, or will this cause performance problems? Already the machine is using up a lot of swap; would you recommend that I bump the RAM up to 2GB, or should I bring down the key_buffer_size in order to fit everything into physical RAM? Should I be concerned about the load average of the machine - it goes up to 6 at some points in the day. Are there any other performance tips that anyone can give based on this configuration? If you need more information, please let me know. Here is the output from top (at a fairly busy time of day): 1:39pm up 224 days, 8:09, 8 users, load average: 2.20, 3.37, 3.44 101 processes: 96 sleeping, 5 running, 0 zombie, 0 stopped CPU0 states: 16.0% user, 60.1% system, 0.0% nice, 22.1% idle CPU1 states: 18.0% user, 72.1% system, 0.0% nice, 8.1% idle Mem: 1545040K av, 1531936K used, 13104K free, 0K shrd, 17048K buff Swap: 2061428K av, 421876K used, 1639552K free 1016380K cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND 17727 root 18 0 1064 1060 824 R31.5 0.0 0:01 top 17733 mysql 10 0 426M 385M 356M S 4.3 25.5 0:00 mysqld 17634 mysql 9 0 426M 385M 356M S 2.1 25.5 0:00 mysqld 17720 mysql 9 0 427M 386M 356M S 2.1 25.5 0:00 mysqld 17746 mysql 10 0 426M 385M 356M S 2.1 25.5 0:00 mysqld 15257 mysql 9 0 426M 385M 356M R 1.6 25.5 360:19 mysqld 17725 mysql 9 0 427M 386M 356M S 1.6 25.5 0:00 mysqld 17730 mysql 9 0 426M 385M 356M S 1.6 25.5 0:00 mysqld 17741 mysql 9 0 426M 385M 356M S 1.6 25.5 0:00 mysqld 17750 mysql 9 0 426M 385M 356M S 1.0 25.5 0:00 mysqld 4 root 19 19 00 0 RWN 0.5 0.0 7:07 ksoftirqd_CPU1 15260 mysql 9 0 426M 385M 356M S 0.5 25.5 88:46 mysqld 29177 root 6 0 740 696 560 S 0.5 0.0 0:17 watch 17654 mysql 9 0 427M 386M 356M S 0.5 25.5 0:00 mysqld 17717 mysql 9 0 426M 385M 356M S 0.5 25.5 0:00 mysqld 17718 mysql 9 0 427M 386M 356M S 0.5 25.5 0:00 mysqld 17734 mysql 9 0 426M 385M 356M S 0.5 25.5 0:00 mysqld 17745 mysql 9 0 426M 385M 356M S 0.5 25.5 0:00 mysqld 17749 mysql 9 0 426M 385M 356M S 0.5 25.5 0:00 mysqld 1 root 9 0 488 440 424 S 0.0 0.0 3:24 init 2 root 9 0 00 0 SW0.0 0.0 0:05 keventd 3 root 19 19 00 0 RWN 0.0 0.0 7:20 ksoftirqd_CPU0 5 root 9 0 00 0 SW0.0 0.0 93:38 kswapd 6 root 9 0 00 0 SW0.0 0.0 0:00 bdflush 7 root 9 0 00 0 SW0.0 0.0 14:45 kupdated 8 root 9 0 00 0 SW0.0 0.0 0:00 scsi_eh_0 9 root 9 0 00 0 SW0.0 0.0 0:00 scsi_eh_1 10 root -1 -20 00 0 SW 0.0 0.0 0:00 mdrecoveryd 11 root 9 0 00 0 SW0.0 0.0 161:25 kjournald 509 root 9 0 560 504 464 S 0.0 0.0 2:27 syslogd 514 root 9 0 456 392 392 S 0.0 0.0 0:00 klogd 713 root 9 0 392 336 336 S 0.0 0.0 0:00 mingetty 714 root 9 0 392 336 336 S 0.0 0.0 0:00 mingetty 715 root 9 0 392 336 336 S 0.0 0.0 0:00 mingetty 716 root 9 0 392 336 336 S 0.0 0.0 0:00 mingetty 717 root 9 0 392 336 336 S 0.0 0.0 0:00 mingetty 718 root 9 0 392 336 336 S 0.0 0.0 0:00 mingetty Here is the output of SHOW VARIBLES (minus character_sets): mysql show variables; +-+---+ | Variable_name | Value | +-+---+ | back_log| 200 | | basedir | /usr/local/mysql-max-3.23.55-pc-linux-i686/ | | bdb_cache_size | 8388600 | | bdb_log_buffer_size | 262144 | | bdb_home| /usr/local/mysql/data/ | | bdb_max_lock| 1
Re: Remove a RPM Installation
Please how can I remove a RPM installation. I used MySQL-server-4.1.1-1.i386.rpm to install MySQL, but how can I delete it. rpm -e MySQL-server-4.1.1-1.i386 -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL load balancing
Hi, Currently our web infrastructure has one main MySQL server, to which connections are made by (mostly) mod_perl running under Apache (on 3 different machines), and several other custom-built application servers on other servers (which have persistant connections, and do both reads and writes). This machine answers between 300 and 1200 queries per second, and the load average is generally around 3-5 (falling below 1 off-peak). We have max_connections set to 100, and are starting to hit this limit quite frequently. The machine is a dual PIII 1.2Ghz with 1.5GB RAM and a 73GB SCSI RAID-5 array, and it only runs MySQL. I am intending on implementing a load-balancing solution, whereby we have a pool of around 3 slaves replicating off the main database server. mod_perl scripts can then make a connection to a random slave, and send their read queries there. Hopefully we can then scale this by adding more slaves to the pool. Problem: all the mod_perl pages run a few write queries, so they will require a connection to the main database server. Since around 80% of our queries are reads, would you recommend that each script has two connections: one for read queries, and one for write queries? We can determine which queries should be run on which connection using Perl. I have looked at the master-master replication solution so that any machine in the replication pool can have write queries run on it; unfortunately several of our tables use auto_increment fields, so this isn't an option. Any advice on this would be appreciated. Alex Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Which is MySQL optimised for - BETWEEN or AND?
Hi, I have a select query which is selecting all records in a table (which has around 8,000,000 rows in). time is a field of type time. Should I be using = and = or BETWEEN to find records in a certain range? Which does MySQL optimise for? select time,price from csq where id = 12345 and date = now() and time = '10:00' and time = '11:00'; select time,price from csq where id = 12345 and date = now() and time between '10:00' and '11:00'; Kind Regards, -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where is my typo?
I try to grand some rights by using GRAND You want GRANT, not GRAND. -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Installation Problem - PLEASE HELP
Hi all. I am trying to install mySql on Solaris 2.6. I am getting the following error during 'make': ld: fatal: library -lz: not found ld: fatal: File processing errors. No output written to .libs/libmysqlclient.so.10.0.0 make[2]: *** [libmysqlclient.la] Error 1 make[2]: Leaving directory `/virtual/mysql-3.23.58/libmysql' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/virtual/mysql-3.23.58' make: *** [all] Error 2 (from http://curl.haxx.se/mail/archive-2003-11/0018.html) It means that it didn't find the zlib library at link time. The configure script found it and enabled the use of it, but when it came to linking it somehow doesn't find it. You can fix this by setting up your LD_LIBRARY_PATH (or similar) to include the path of your zlib. -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Enabling Mysql in php
Hi. I haven't mysql enabled in php. How may I do it? I have php 4.0.4, mysql 3.23.36, both installed on linux RH 7.3. Install the php-mysql RPM. -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restore And Dump
Hello, I am running two instances of mysql. They both run on different boxes, not networked. Anyway, I have been developing on one machine, and trying to do a dump and keep the other box updated with Database changes. I have ran this from the dev box. mysqldump --add-drop-table -u dbusername -p dbname dbname.bak.dump // or better yet... mysqldump --opt -u dbusername -p dbname dbname.bak.dump to get the latest dump. Then copy this to CD to reimport on the production box. The only problem, is I don't seem to understand how to reimport, or use this dump. Can anyone help. On the production box: mysql -u dbusername -p dbname dbname.bak.dump PS, I have a lot of blobs and clobs within the Database. Is there anything else I may be missing to keep these two synchronized? These should be OK. -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL RPM Upgrade
Hello, we are currently running MySQL 4.0.15 w/InnoDB tables on a RH/Linux 9.0 and would like to upgrade. the current RPM's installed are: MySQL-client-4.0.15-0 MySQL-embedded-4.0.15-0 MySQL-devel-4.0.15-0 MySQL-shared-compat-4.0.15-0 MySQL-shared-4.0.15-0 MySQL-server-4.0.15-0 MySQL-bench-4.0.15-0 do we need to upgraded ALL these RPM's?? No - you probably don't need bench, devel, shared or embedded. and if so, will we still retain all the current InnoDB data files and databases already setup if we upgrade ?? Yes. -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL RPM Upgrade
Thx' Alex. do the old RPM's need to be removed first before upgrading ?? You can just do: rpm -Uvh MySQL-* in the directory with the new RPM's in - this will overwrite the old packages with the new ones. -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
