Re: MySQL Hangs
Velu Shk wrote: Related: http://lists.mysql.com/mysql/44164 No solutions mentioned here. Slack 10.1 is booting and once the newly installed MySQL Standard 4.1.14 starts up with: "Starting mysqld daemon with databases from /usr/local/mysql/data" The system hangs and is dead to the world. I will need some time to prepare a disk for recovery to get past this inconvenience. Perhaps a hotkey workaround I am unaware of will allow init to skip this standstill? - velusip Velu, If you boot the system into run level 1, you should be able to remove the symlink from the appropriate rc.x directory. Once you've done that, the machine will boot and maybe you can find something in the mysql error log. If you don't see anything there, adding strace to the mysql startup script might give some more info which would be helpful in solving your problem. walt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem compiling MyODBC - looking for sql.h file
Ryan Stille wrote: I posted this on the ODBC list, but there is not much traffic there and no one replied. Hopefully someone here has dealt with this. I am setting up ColdFusion to access a MySQL 4.1.x database. This required new MyODBC drivers to be installed on the system for ColdFusion. After a lot of trial and error, I got it figured out. I install mysql on the system (using --without-server), then install MyODBC. Then copy the libmyodbc3.so file to a directory under ColdFusion, and change ColdFusion's odbc.ini file to reference this new driver. I am now trying to do it on a second system, but am getting errors during the ./configure of MyODBC (3.5.11). This is my configure statement: ./configure --with-mysql-path=/usr/local/mysql \ --without-samples \ --disable-test \ --enable-thread-safe And this is the resulting output: ... ODBC DRIVER MANAGER CONFIGURATION - LIBRARIES AND HEADERS checking for isql... No checking for unixODBC version... Unknown checking sql.h usability... No checking sql.h presence... No checking for sql.h... no configure: error: Unable to find the unixODBC headers in '/usr/local//include' It's complaining about unixODBC headers, maybe specifically the sql.h file in the second to last line? But I don't have unixODBC installed on the FIRST system, the one that is working just fine. (ColdFusion has it's one built in ODBC manager, unixODBC is not necessary.) However there is a /usr/local/include/sql.h file on that system. I don't know how it got there. By looking inside the file it appears to be related to MyODBC 3.5.11. Just for kicks I copied this file to the second system, but got the same error when trying to run ./configure. Any ideas? -Ryan Ryan, It's part of unixODBC-devel walt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Temp table full (I think) -- how do I fix this?
Walt Weaver wrote: Hi, I have a job runnning that's modifying a column on a 15-million-row table and is throwing out the following error: Output: Replication Error 1114, slave: replicatenj07, error: Error 'The table '#sql-5303_3c' is full' on query. Default database 'customer__upgrade'. Query: ALTER TABLE inc_performance MODIFY COLUMN. This indicates to me that the temp table is full. Problem is, I'm not sure what config parm or whatever needs to be changed to allow the temp table to grow sufficiently. Can someone push me in the right direction on what to change, or if I'm barking up the wrong tree, show me which tree to bark at? Thanks, --Walt Weaver Bozeman, Montana Walt, Run mysql> show variables; There are several config options for temp stuff such as size & directory. I don't know the syntax to change these off the top of my head but it should point you to the correct "tree". :-) walt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't switch databases 5.0.15-0 RedHat ES4
Is this something new with mysql 5, a bug, or something I'm missing? I'm
not able to switch databases.
[EMAIL PROTECTED] 5.0.15-0-es4]# rpm -ivh
MySQL-client-standard-5.0.15-0.rhel4.i386.rpm
MySQL-devel-standard-5.0.15-0.rhel4.i386.rpm
MySQL-server-standard-5.0.15-0.rhel4.i386.rpm
[EMAIL PROTECTED] 5.0.15-0-es4]# mysql
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('mysql');
Query OK, 0 rows affected (0.20 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.16 sec)
mysql> exit
[EMAIL PROTECTED] 5.0.15-0-es4]# mysql -u root -p
mysql> CREATE DATABASE FOO;
Query OK, 1 row affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON FOO.* TO 'bar'@'localhost' IDENTIFIED BY
'bar' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> use FOO;
ERROR 1049 (42000): Unknown database 'FOO;'
mysql> show databases;
++
| Database |
++
| information_schema |
| FOO|
| mysql |
| test |
++
4 rows in set (0.06 sec)
mysql> exit
[EMAIL PROTECTED] 5.0.15-0-es4]# ls /var/lib/mysql/FOO/
db.opt
[EMAIL PROTECTED] 5.0.15-0-es4]# mysql -u bar -p
mysql> show tables;
ERROR 1046 (3D000): No database selected
mysql>
mysql> use FOO;
ERROR 1044 (42000): Access denied for user 'bar'@'localhost' to database
'FOO;'
mysql> exit;
[EMAIL PROTECTED] 5.0.15-0-es4]# mysql -u bar FOO -p
mysql> show tables;
Empty set (0.00 sec)
mysql>quit;
[EMAIL PROTECTED] 5.0.15-0-es4]# mysql -u root -p
mysql> create database BLA;
Query OK, 1 row affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON BLA.* TO 'bar'@'localhost' IDENTIFIED BY
'bar' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> quit
[EMAIL PROTECTED] 5.0.15-0-es4]# mysql -u bar -p
mysql> use FOO;
ERROR 1044 (42000): Access denied for user 'bar'@'localhost' to database
'FOO;'
mysql> use BLA;
ERROR 1044 (42000): Access denied for user 'bar'@'localhost' to database
'BLA;'
mysql> quit
[EMAIL PROTECTED] 5.0.15-0-es4]# mysql -u bar FOO -p
mysql> show tables;
Empty set (0.00 sec)
mysql> use BLA;
ERROR 1044 (42000): Access denied for user 'bar'@'localhost' to database
'BLA;'
mysql> quit
[EMAIL PROTECTED] 5.0.15-0-es4]# mysql -u bar BLA -p
mysql> show tables;
Empty set (0.00 sec)
mysql>
Thanks !
walt
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql performance problems.
1075 alert(s) to the Alert cache
2006-03-28, 16:40:00, Added 826 alert(s) to the Alert cache
2006-03-28, 16:45:00, Added 1885 alert(s) to the Alert cache
2006-03-28, 16:50:00, Added 1030 alert(s) to the Alert cache
2006-03-28, 16:55:00, Added 914 alert(s) to the Alert cache
2006-03-28, 17:00:00, Added 753 alert(s) to the Alert cache
2006-03-28, 17:05:00, Added 531 alert(s) to the Alert cache
rabid#
rabid# crontab -l
...
*/5 * * * * /usr/local/etc/base-update.sh
rabid# cat /usr/local/etc/base-update.sh
#!/bin/sh
Current_Date=`date '+%Y-%m-%d' `
Current_Time=`date '+%H:%M:%S'`
echo "$Current_Date, $Current_Time, `/usr/local/bin/php
/usr/local/www/base/update.php | \
sed 's/^.*Added/Added/;s###'`" >>
/var/log/base-update.${Current_Date}.log
rabid#
rabid# pwd
/usr/local/www/base
rabid# cat update.php
include("base_conf.php");
include_once("$BASE_path/includes/base_auth.inc.php");
include_once("$BASE_path/includes/base_db.inc.php");
include_once("$BASE_path/includes/base_output_html.inc.php");
include_once("$BASE_path/base_common.php");
include_once("$BASE_path/base_db_common.php");
include_once("$BASE_path/includes/base_cache.inc.php");
include_once("$BASE_path/includes/base_state_criteria.inc.php");
include_once("$BASE_path/includes/base_log_error.inc.php");
include_once("$BASE_path/includes/base_log_timing.inc.php");
$db = NewBASEDBConnection($DBlib_path, $DBtype);
$db_connect_method = 1;
$db->baseDBConnect($db_connect_method, $alert_dbname, $alert_host, $alert_port, $alert_user, $alert_passw
ord);
UpdateAlertCache($db);
?>
Jacob,
Have you turned on the slow query log ?
http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html
Once you have logged slow queries, than you can run explain on them and
possible tune your indexes better.
walt
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql disaster recovery
Andrew, Andrew Hall wrote: > > John, > > Thank you for your reply. I have read the replication chapter and this > seems to be easier, but I do have a few questions. > > 1. I am using InnoDB tables, at least there are innodb argument to > mysqld in the start script, so should I use mysqldump instead of tar-ing > the data dir to create the baseline for the slave? I believe thats what > the docs are stating, but I wanted to be certain. You'll only be using innodb tables if you specified that table type when you created each table or if you have " default-table-type=innodb" in a my.cnf file somewhere. You would also have to create the innodb tablespace and log files before you could create the tables. You can copy the innodb files to a slave, but the my.cnf files on both machines should match. > 2. I want to have a failover scenario here, so what would the impact be > on the mysql db if I go master->slave->master? > I wouldn't recommend this. We had a instance a couple of weeks ago where the master db was having disk issues and when we had to repair some tables. When we did, we lost information in the master but luckily the slaves had replicated the data so we were able to restore that info back. > 3. What happens if the master fails, dies, and goes offline. Must I > change all my apps to connect to the IP of the slave for connections to > work? I am unclear if replication will provide failover capabilities. > For all of our "services", we run "floating ip addresses". That way if something like mysql or dns crashes, we can automatically drop the ip on the main machine and bring it up on the backup without being concerned about the server's actual ip address. Searching google for pirinna and heartbeat should help you with the fail-over part. > 4. What is necessary to bring the master back up after a failure? > Should I? or should I leave the slave (new master) up, and make the old > master the new slave? I'd leave the "new master" up until you can stop both databases and sync the data. > 5. I am running 3.23.54 and I know I should upgrade, but its not going > to happen today, so are there any show stopping bugs with this version? We've been running .53 for over a year and half with no problems. We use iptables on the db servers to get around the security updates > Thank you for your time, > > Andrew > > On Tue, 2003-12-02 at 17:10, John Griffin wrote: > > Hi Andrew, > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Hope this helps! walt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Anger Managment and MySql
TriKster Abacus wrote: > > I am trying to get some kind of friggin Quake 3 stat generator to work > on my Quake 3 servers that I have running. > Currently I am running a program called Q3Log, which is java based, but > it seems to have stopped working for some reason. (no log to show > errors). Anyhow, now my only option is to run a MySql / php backend stat > generator. > TriKster, The "mysql.sock" you refer to is what is known in the *nix world as a named pipe. It is how applications can communicate with each other without using network sockets. When you get the "Can't connect to local MySQL server through socket '/var/run/mysql/mysql.sock'" That usually means that mysql is not running. Did you try `ps -eaf |grep -i mysql` to see if mysql was running? If it was running, then you know that the mysql client is not looking in the correct place. If you do a "man msql" you'll see that you can add a "-S" flag to specify the socket to use. The fact that mysql doesn't appear to start can be caused by many things that aren't really related to mysql. You said you did a "chmod 755" on the mysql directory. Did you do that as root. If so, how do expect the "mysql user" to write anything to that directory? The first thing you need to do is get mysql running. Try to start mysql and if you get a "mysqld ended" then oviously it's not running and how are you going to connect to a database that's not running? Try su - mysql and then `chmod 755 /var/lib/mysql`. Then try to start mysql again as the "OS" root user. walt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: tuning key_buffer_size
Thomas wrote: > > Hello all, > > My mySQL 4.1.0 server is currently set to use a key_buffer_size of 32M. My > server has 1GB of RAM, and is now using only InnoDB tables. > > The manual says that 'Key_reads/Key_read_request ratio should normally be < > 0.01'; mine is ~ 0.12; the manual also says 'Key_write/Key_write_requests is > usually near 1'; mine is very, very small (< 0.0001). > > This server is also running apache/php. Any suggestions on how I should > proceed in tuning key_buffer_size? > > Thanks, > > Joshua Thomas > Network Operations Engineer > PowerOne Media, Inc. > tel: 518-687-6143 > [EMAIL PROTECTED] > > --- > In theory there is no difference between theory and practice. In practice > there is. > - Yogi Berra > --- Joshua, The key buffer is not used for innodb tables. innodb_buffer_pool_size is where you want to do your tweaking. walt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database take too much hard drive space
Xavier Fernández i Marín wrote: > > Hi, > > I've been inserting a csv file of about 150Mb into a mysql database. The > problem is that there is no enough free space on the hard disk, and the > process have been collapsed. > > Now I've been trying to access to the database to drop the table and change > the directory where mysql stores the information, but I can't access it. > > I suppose that the procedure is to stop mysql server and then drop the table, > but I don't know how to drop a table when mysqld does not run. And, appart > from that, is this the best procedure? > > I'm running mysql 3.23 in a Linux Mandrake 9.0 > > Thanks, > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] As long as the table is a standard mysql table (MyISAM), you can just delete the data files for that table from the database directory. I'd make a backup copy of the datafiles before you delete them just in case something doesn't go correctly. Also make sure mysql isn't running. There may be a better way, but this should work. To remove a table called customer from a database called office for example, you'd simply do. rm /var/lib/mysql/office/customer.* -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Socket Error
Birju,
What messages do you get in the mysql log file? The message you're
getting from perl just says that the database is not available. Before
you try to restart mysql, have you checked to see if there are still
mysql processes running (ps -eax |grep -i mysql). Have you run myisamchk
on the tables since mysql has crashed.
walt
Birju Shah wrote:
>
> Hello,
>
> Thankyou for your quick response.
> When I start getting this error, the mysql crashes, any application which I
> try to start which uses mysql backend, says mysql.sock error could not
> connect .
> -
> DBI connect('database=search;host=localhost','web',...) failed: Can't
> connect to local MySQL server through socket '/tmp/mysql.sock' (61) at (eval
> 2) line 1
> --
> When i try to restart the mysql server nothing happens and the only option
> that remains is to reboot the entire system. Everything works fine then for
> a few days and again the same error.
>
> Birju Shah
>
> -
>
> - Original Message -
> From: "Daniel Kasak" <[EMAIL PROTECTED]>
> To: "Birju Shah" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> Sent: Monday, January 12, 2004 2:13 PM
> Subject: Re: Mysql Socket Error
>
> > Birju Shah wrote:
> >
> > >Hello Friends,
> > >
> > >We are using free bsd, mysql 4.0.15. We are using this system since more
> than a year now. We have a autoresponder script which has mysql database.
> Since a month or so we are noticing that mysql gives the mysql.sock error
> and the only option which remains is to reboot the entire system.
> > >
> > >This error didnt happen before, the same scripts are running, we havent
> changed anything in the scripts. The mysql gives the socket error sometimes
> in 3 days sometimes in 7-8 days.
> > >
> > >What can be causing this error and what is the solution.
> > >
> > >Sorry to bother you.
> > >
> > >Birju Shah
> > >
> > >
> > Another mysql.sock error!
> > Firstly, when you start getting the error, what's happening to MySQL? Do
> > you access it in any other way, or only though the script which works
> > via the socket? Can you connect to MySQL with the command-line client,
> > or with MySQLCC?
> > Maybe MySQL is crashing and this is the reason which you can't connect
> > through the socket. What do the mysql logs say? Maybe examine / post them.
> >
> > --
> > Daniel Kasak
> > IT Developer
> > NUS Consulting Group
> > Level 5, 77 Pacific Highway
> > North Sydney, NSW, Australia 2060
> > T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
> > email: [EMAIL PROTECTED]
> > website: http://www.nusconsulting.com.au
> >
> >
> >
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Socket Error
Birju,
Here's a little Linux/Unix info for you.
The "/tmp/mysql.sock" in question is used for ipc (Inter Process
Communication). This allows other applications to communicate with the mysql
server without using a "TCP/IP" connection. This eliminates the need to pass
everything through the networking code.
In the post below, the person is having problems when trying to connect
through the network layer. It would be like changing your database connect
string to
DBI connect('database=search;host=192.168.1.5','web',...).
As far as the 1045 error, it does not look like you typed everything
correctly. What is the "-number" supposed to be?
walt
On Wednesday 14 January 2004 05:03 am, you wrote:
> Hello Walt,
>
> I did and it says the following
>
> > mysql -u websquash -number
>
> ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
> NO)
>
> One more thing, I just saw that there is someone who has posted a query
> where the mysql crashes on freebsd. I am pasting it below
> if that can be of help to you taking my situation in consideration.
>
> --Some one posted this
> Hi!On Jan 12, Holm Tiffe wrote:> >Description:> mysqld 4.0.17 crash on
> FreeBSD 5.1-current-alpha> >How-To-Repeat:> Any acces over IP (not domain
> socket) crashes mysqld:> #/usr/local/bin/mysqladmin: connect to server at
> 'install' failed> error: 'Lost connection to MySQL server during query'> >
> syslog:> install mysqld[78066]: warning: can't get client address: Bad file
> descriptorI suspect it is a an issue of KSE library (either a bug or
> someincompatibility with MySQL).Could you run mysqld with libc_r and see if
> it helps ?Unfortunately we don't have FreeBSD-5/Alphato try this ourselves.
> Regards,Sergei--__ ___ ___ __ / |/ /_ __/ __/ __ \/ /
> Sergei Golubchik <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB,
> Senior Software Developer/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
> <___/ www.mysql.com-- MySQL General Mailing ListFor list archives:
> http://lists.mysql.com/mysqlTo unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>- ---
>
>
>
>
> - Original Message -
> From: "walt" <[EMAIL PROTECTED]>
> To: "Birju Shah" <[EMAIL PROTECTED]>
> Sent: Tuesday, January 13, 2004 12:17 PM
> Subject: Re: Mysql Socket Error
>
> On Wednesday 14 January 2004 04:41 am, you wrote:
> > Hello,
> >
> > When I run the command it gave me this
> >
> > Command history
> >
> > > ls /tmp/mysql.sock
> >
> > /tmp/mysql.sock
> >
> >
> >
> >
> > Now, how do I execute the client through the command prompt ?
> >
> > Let me know
> >
> > Thankyou
> > birju shah
>
> try something like
> `mysql -u your_user_name -p`
> when it asks for your password, type it in.
>
> walt
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Starting up MySQL :(
Annie Law wrote: > > Hi, > > I would appreciate any help on the following. I have searched the mailing list for > information But have not come up with a solution for my situation which has been > frustrating. I think I need some things need to be clarified. > > The OS that is running is RedHat V.9.0 linux. MySQL was installed when RedHat V.9.0 > was installed. Here are the packages that that I have installed > Annie, There should be a startup/stop script for mysql in /etc/init.d/ . Try running "/etc/init.d/mysql stop" After that, check for mysql with "ps -eaf |grep -i mysq" . If you find a process running, try to kill it with "kill -15 pid". You may have to use "kill -9 pid" if the processes wont go away with -15. You should be able to start mysql using "/etc/init.d/mysql start". Once the server is running, su to the mysql user and you should be able to log in to the database using "mysql". walt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what can i change in my.cnf/mysqld to get better perfomance
Pete, I was just glancing at this and it looks like you have about 13.5GB of memory allocated on a 8GB machine (4096+4096+512+4096+1024). Are you swapping badly?? walt "Lancashire, Pete" wrote: > > I'm doing some tests on a small Sun server, a 280R to demo > MySQL vs. Oracle. For the demo I using a subset of a test table. > > The destination server will be a Sun 880 with 8 1.2 GHz CPU's, > 16 GB RAM, Sun T3+ RAID Array. > > Any suggestions on settings or other changes would be helpful. > > Also what would be the best variables/settings to compile a binary > for a server of this type ? Gcc 3.3.2, Solaris 9. > > number of rows is only 10,735,291 > > MYD is 6.7 GBytes, and MYI is 1.4 GBytes. > > This table is a subset of the real table of about 120 Gb. > > the test/lab server is a small sun box with 2 each 900 MHz CPUs, and 8 > GB of RAM. > > I'm trying to avoid using disk until I get a better performance disk > subsystem. > > The version is the website binary, solaris 64 bit. > > the setting i currently have > > [mysqld] > set-variable = myisam_sort_buffer_size=4096M > set-variable = sort_buffer_size=4096M > set-variable = read_buffer_size=512M > set-variable = key_buffer_size=4096M > set-variable = table_cache=1024 > > tmp_table_size = 1024M > max_heap_table_size = 1024M > > a search on text fields such as > > select substring_index(request_uri,'/',-1) jsp, count(*) from apache_jan > where request_is_jsp = 1 group by jsp; > > takes 8 1/2 minutes. > > A rebuild of the indexes takes over 8 hours. > > the table format > > CREATE TABLE `apache_jan` ( > `dt` datetime NOT NULL default '-00-00 00:00:00', > `hostname` varchar(125) default NULL, > `ip` varchar(15) default NULL, > `ipn` int(10) unsigned default NULL, > `user` varchar(125) default NULL, > `timetaken` float default NULL, > `auth` varchar(255) default NULL, > `request` text, > `request_type` varchar(255) default NULL, > `request_uri` text, > `request_file` varchar(255) default NULL, > `request_is_jsp` tinyint(1) default NULL, > `request_is_servlet` tinyint(1) default NULL, > `request_query` text, > `request_version` varchar(255) default NULL, > `status` smallint(3) unsigned default NULL, > `status_orig` smallint(3) unsigned default NULL, > `bytes` int(11) default NULL, > `bytes_i` int(11) default NULL, > `bytes_o` int(11) default NULL, > `header` text, > `header_uri` text, > `header_file` varchar(255) default NULL, > `header_is_jsp` tinyint(1) default NULL, > `useragent` varchar(255) default NULL, > `biz_obj` varchar(255) default NULL, > KEY `dt` (`dt`), > KEY `hostname` (`hostname`), > KEY `ip` (`ip`), > KEY `status` (`status`), > KEY `request_is_jsp` (`request_is_jsp`), > KEY `request_is_servlet` (`request_is_servlet`), > KEY `request_file` (`request_file`), > KEY `header_file` (`header_file`), > KEY `header_is_jsp` (`header_is_jsp`), > KEY `ipn` (`ipn`) > ) TYPE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=2 > AVG_ROW_LENGTH=600 INDEX DIRECTORY='/DISK1/MYSQL/pdm/' > > -- > 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: OR in query doesn't use keys?
Bill, Someone sent this too the list the other day. >> MySQL's optimizer has a slight problem. OR queries cause it to get very >> confused. >> >> Try the following to get the best performance: >> >> Rewrite SELECT FROM table WHERE (condition1) OR (condition2); >> >> As: >> >> (SELECT FROM table WHERE condition1) UNION (SELECT FROM table WHERE >> condition2); walt Bill Marrs wrote: > > I've noticed that If I use an OR in my query, mysql seems to choose not to > use my indexes. Though, it would seem to help (as, if I do the query in > two steps, I can get faster results than as one query). > > Is there some way I can convince mysql to use my keys with an OR, or > perhaps another way to do queries to avoid OR, but still get an OR-like result? > > Here's a simplified example (my actual case is more complicated and slower): > > mysql> SELECT count(*) FROM Trades WHERE User1 = 79909; > +--+ > | count(*) | > +--+ > | 22 | > +--+ > 1 row in set (0.00 sec) > > mysql> SELECT count(*) FROM Trades WHERE User2 = 79909; > +--+ > | count(*) | > +--+ > | 33 | > +--+ > 1 row in set (0.01 sec) > > mysql> SELECT count(*) FROM Trades WHERE (User1 = 79909 OR User2 = 79909); > +--+ > | count(*) | > +--+ > | 55 | > +--+ > 1 row in set (0.35 sec) > > Note - the OR is slower, describe (below) even says that it doesn't use > either key in this case. > > mysql> describe SELECT count(*) FROM Trades WHERE (User1 = 79909 OR User2 = > 79909); > ++--+---+--+-+--++-+ > | table | type | possible_keys | key | key_len | ref | rows | > Extra | > ++--+---+--+-+--++-+ > | Trades | ALL | User1Key,User2Key | NULL |NULL | NULL | 100775 | > Using where | > ++--+---+--+-+--++-+ > 1 row in set (0.00 sec) > > Here's the table: > > CREATE TABLE Trades ( >UID int(10) unsigned NOT NULL auto_increment, >User1 int(10) unsigned NOT NULL default '0', >User2 int(10) unsigned NOT NULL default '0', >PRIMARY KEY (UID), >KEY User1Key (User1), >KEY User2Key (User2) > ) TYPE=MyISAM PACK_KEYS=1; > > Anyone know a trick to do OR queries faster? > > Thanks in advance, > > -bill > > -- > 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: Installation Confusion in Linux
Ross, rpm -qlp package_name.rpm will list all the files in an rpm for you. You may need to install the client rpm as well. walt Ross O wrote: > > I was able to successfully install MySQL server 4 > under windows, but am a little confused about doing it > through linux. > > My main confusion is where the install goes? Im using > the rpm, and from my personal directory where i > downloaded the rpm to, i issue a rpm -i > MySQL-server-...rpm command and it goes through its > deal. The very first thing I notice is it says is to > execute /usr/bin/mysqladmin -u root... to set the > passwords. Well there are about a dozen mysql scripts > in that directory but no mysqladmin, so immediately Im > thinking i have a problem or did something wrong. Next > it says the default directory for the install is > /usr/local/mysql unless i specified otherwise. I did > not and that directory does not exist. > > so what happened? i downloaded the rpm off the mysql > site and all i did was run it. didnt touch anything > else. it installs the mysql service fine, and it puts > all its db files in the /var/lib directory like it > says it will, but it sure seems like im missing some > other critical files? > > __ > Do you Yahoo!? > Yahoo! Finance: Get your refund fast by filing online. > http://taxes.yahoo.com/filing.html > > -- > 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]
Newbie question
I need a little advice on where to get started. I want to create a db and simple form that will populate the db. Which language is best? What should I read to help me along? tia Walt
Re: Newbie question
I recently graduated from college, BS in computer science, I am familiar with a variety of languages including C++, Java, VB, jscript, perl, cgi and sql. I used VB in the past for a front end on MS access. I have not yet landed a job so I thought learning mySql and creating a some sort of form that could up date the db woul dbe nice. The big question is where to begin, which language to use for a simple form and how to hook the form to the db. tia Walt - Original Message - From: "Rhino" <[EMAIL PROTECTED]> To: "Walt" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, February 20, 2004 12:10 AM Subject: Re: Newbie question > > - Original Message - > From: "Walt" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Thursday, February 19, 2004 8:00 AM > Subject: Newbie question > > > > I need a little advice on where to get started. I want to create a db and > simple form that will populate the db. Which language is best? What should > I read to help me along? > > That's pretty hard to answer since you haven't said anything about your > skills, your environment, etc. > > Java is a really neat language but there's a pretty substantial learning > curve to it. If you already know one or more programming languages, you > should say so; it's quite possible that the language you already know can be > used to do the work you require. > > If you are doing this work strictly on your own and for yourself, you can > choose pretty much any language like Java, Perl, Php, C, C++, etc. On the > other hand, if you are part of an IT shop, you should probably use the shop > language, whatever it is. If you are doing this work for a customer and will > hand maintenance of the program over to them, you should choose a language > that your customer can support. > > Etc. etc. > > There are many possible options but the best one depends on your situation. > > Rhino > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >
Re: Newbie question
Those were my exact thoughts being a former cne and msce. I know how to write the code whatever form I want, web based or standalone, I can also create the necessary db in mySql but how do they get hooked up? Walt - Original Message - From: "Donny Simonton" <[EMAIL PROTECTED]> To: "'Walt'" <[EMAIL PROTECTED]>; "'Rhino'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Sunday, February 22, 2004 4:15 PM Subject: RE: Newbie question > As a manager of over 20 programmers, I would say from a web point of via PHP > would be your best bet. Everybody who graduates today can program in Java, > and if they don't have any other skills I don't even interview them. I > consider Java programmers to be the MCSE of a few years ago. > > Donny > > > -Original Message- > > From: Walt [mailto:[EMAIL PROTECTED] > > Sent: Sunday, February 22, 2004 1:46 PM > > To: Rhino; [EMAIL PROTECTED] > > Subject: Re: Newbie question > > > > I recently graduated from college, BS in computer science, I am familiar > > with a variety of languages including C++, Java, VB, jscript, perl, cgi > > and sql. I used VB in the past for a front end on MS access. I have not > > yet landed a job so I thought learning mySql and creating a some sort of > > form that could up date the db woul dbe nice. The big question is where to > > begin, which language to use for a simple form and how to hook the form to > > the db. > > > > tia Walt > > > > > > - Original Message - > > From: "Rhino" <[EMAIL PROTECTED]> > > To: "Walt" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > > Sent: Friday, February 20, 2004 12:10 AM > > Subject: Re: Newbie question > > > > > > > > > > - Original Message - > > > From: "Walt" <[EMAIL PROTECTED]> > > > To: <[EMAIL PROTECTED]> > > > Sent: Thursday, February 19, 2004 8:00 AM > > > Subject: Newbie question > > > > > > > > > > I need a little advice on where to get started. I want to create a db > > and > > > simple form that will populate the db. Which language is best? What > > should > > > I read to help me along? > > > > > > That's pretty hard to answer since you haven't said anything about your > > > skills, your environment, etc. > > > > > > Java is a really neat language but there's a pretty substantial learning > > > curve to it. If you already know one or more programming languages, you > > > should say so; it's quite possible that the language you already know > > can be > > > used to do the work you require. > > > > > > If you are doing this work strictly on your own and for yourself, you > > can > > > choose pretty much any language like Java, Perl, Php, C, C++, etc. On > > the > > > other hand, if you are part of an IT shop, you should probably use the > > shop > > > language, whatever it is. If you are doing this work for a customer and > > will > > > hand maintenance of the program over to them, you should choose a > > language > > > that your customer can support. > > > > > > Etc. etc. > > > > > > There are many possible options but the best one depends on your > > situation. > > > > > > Rhino > > > > > > > > > -- > > > MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql > > > To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > >
Re: upgrading, tuning and performance
Andy Stubbs wrote: > > Hi, long time listener, first time caller (I think), > > I've got a database which I'm looking to increase performance, either by > buying bigger kit or by somehow optimising current configuration. > > I'm running MySQL-Max-3.23.56-1 from the mysql.com RPMs on a Dell > Poweredge 2500 with dual 1400MHz PIII processors and 4GB RAM with RedHat > 7.3 kernel 2.4.18-10smp. > > The database itself is actually quite small; about 3.3GB on disk. Disk > configuration is RAID-5, 3 disks, chunksize 8KB, default mounting options. > > Currently, when moderately busy, it's not unusual to see 400 queries/sec, > so I imagine when running top whack at the moment we're probably hitting > up to 500 selects/second. Keeping our developers focussed on how their > design decisions affect performance is a continual process of course... > > Anyway, the load average on this server is hitting 2 occasionally, and > it's time either to tune the configuration, move it onto some other kit, > or buy in some kit specifically. Apparently I might be looking at the > traffic on the database quadrupling in the next few months, so I'm keen on > getting this sorted ASAP. > > Having Read The Fine Manual, and being more of a coder/sysadmin than a DBA > I have some dumb questions. Which are: > > 1. Does anybody else have any experience with this situation? Good, Bad, >Ugly? > > 2. I can move this DB to a dual PE2600 with dual Xeon 1.8GHz processors. >and I can get up to 6GB RAM in there. Is it worth doing this? i.e., how >much extra capacity does this buy me? Does enabling HyperThreading on >the Xeons help or hinder database servers? > > 3. Would upgrading to 4.0.13 help at all? What kind of performance does it >have compared with 3.23.56? This is a medium term goal anyway, and >we'd like to take rather more time over it. > > 4. I read that on a 32 bit architecture (like these Pentium class CPUs) >the database tables are not memory-mapped (i.e., it's not possible to >store the entire database in memory anyway). Is this the case with the >64 bit Solaris too? Is there a planned implementation schedule for this >functionality? > > 5. What's the performance of MySQL like on Solaris 8/9 compared to Linux >2.4.18? Is a big multiprocessor Sun box (like a Sun Fire 880 or 1280) a >good choice for a database server running MySQL? Or are there more >suitable platforms? Is, in fact, something like a SunFire 880 overkill? > > 6. Would it be complete lunacy, in the absence of memory-mapped tables, to >specify a RAM-disk on which to store the database? Should improve seek >times, eh? And lots of redundant UPS stuff. > > 7. Any other suggestions welcome. I'm a bit nervous about turning off >atime on mounting the disks - is it really not used by the database >anywhere? what kind of performance boost does it give? > > Regards, > > Andy > > -- > Andy Stubbs, B.A., Ph.D. > Network Manager, Active Hotels Ltd. > +44 1223 578106 > Andy, A load avg of 2 sounds like the machine is under a high I/O load. Have you considered using 15k rpm drives? Is your raid setup hardware or software? walt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication bin log rollover....
Jeff Kilbride wrote: > > I remember seeing this same question a few months ago, so you might try the > archives. I'm pretty sure the answer was that mysql continues incrementing > to 4 digits. If you have a dev box, you could create a .999 binlog and put > it in your bin-index file -- then start mysql, issue a flush logs command > and see what happens. > > --jeff > > - Original Message - > From: "Andrew Braithwaite" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Monday, July 07, 2003 3:33 AM > Subject: Replication bin log rollover > > > Hi, > > > > I have a MySQL master server doing huge amounts of inserts and updates. > I'm > > rapidly reaching the point where my binlogs will get to: > > > > myserver-bin.999 > > > > Does anyone know if MySQL treats the rollover gracefully? Will it > rollover > > to myserver-bin.001 or will it move to a 4 figure extension? > > > > I couldn't find anything in the docs.. > > > > Thanks for any help. > > > > Cheers, > > > > Andrew > > > > sql, query > > > > -- > > 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] Andrew, They will roll over. We're at -bin.1268 walt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can't set variable (sort_buffer)
Anthony, Try adding this to the my.cnf file set-variable =sort_buffer=30M walt arobins wrote: > > I would like to change the sort_buffer variable but > that variable doesn't seem to be configurable. > > i.e. > > I get the following when doing mysql --help. > Don't understand why only these variables are configurable. > Using db version 3.23.51. > > Possible variables for option --set-variable (-O) are: > connect_timeout current value: 0 > max_allowed_packetcurrent value: 16777216 > net_buffer_length current value: 16384 > select_limit current value: 1000 > max_join_size current value: 100 > > -- anthony > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb file won't shrink
Heikki Tuuri wrote: > > On September 15th, 2003 you will be able to put every InnoDB table into its > own file. That should alleviate this kind of problem. > > > Best regards, > > Heikki Tuuri > Innobase Oy > http://www.innodb.com > Transactions, foreign keys, and a hot backup tool for MySQL > Order MySQL technical support from https://order.mysql.com/ That is great news! Will a single table be able to span several datafiles? Thanks! walt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup problem - disaster waiting to happen
H M Kunzmann wrote:
>
> I use mysqldump to dump my databases to file.
> I then write these files to tape.
>
> I was doing a test restore to a test server this weekend and found that
> for my largest database, I cannot restore from this file.
>
> I use mysql < backup.script
>
> It runs for a long time and creates most of the tables, but eventually
> comes up with a syntax error and stops processing the file.
>
> I have two questions:
> How do I get around this ? The error message is:
>
> ERROR 1064 at line 78631: You have an error in your SQL syntax. Check the manual
> that c
> om:vml\"\r\nxmlns:o=\"u
>
> This data is xml data stored in one of the fields. If mysqldump created
> the syntax surely it should process back in correctly ? There's no way I
> can edit 2GB of incorrect entries in order to correct them.
>
> Secondly, how can I make the restore more fault tolerant ? If one call
> fails to continue with the next one ?
>
> Thank
> Ciao
> Herbert
> --
> Herbert Michael Kunzmann
> Binary Chaos Magician
Herbert,
It might be better if you do a per table export instead of whole
database export. If you still have files that are too large to easily
edit, use a utility like split to break them up. Below is the script we
use to backup all of our tables except for 100_PATS and 400_PATS as
those tables are dropped and reloaded everynight anyway.
Hope this helps!
walt
#!/bin/bash
cd /var/lib/mysql/NEA/
FILES=`ls *.frm`
for file in $FILES; do
LEN=${#file}
STRIP=$((LEN -4))
table=`expr substr $file 1 $STRIP`
if [ $table != "100_PATS" ] && [ $table != "400_PATS" ]; then
/usr/bin/mysqldump -qt -u nea NEA $table -r /opt/db_dump/$table
fi
done
exit 0
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table error 127
Jonathan Patton wrote: > > I have mysql setup on two computers with identical databases. When I run a group of > queries on the one computer I get back an error 127 which I checked on an it appears > to be a table corruption error. On the other computer, the queries run fine. Since > I had all the data for the table in question in a data file, I just dropped the > table and recreated it. The error still appeared. Any suggestions on what to try > next? The only other thing I can think of is to compare the mysql versions to see if > I have an older version on the other computer. Jonathan, Have you tried running myisamchk on the database in question before running queries on it? walt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable to ADD databases
"Jeffery C. Baldwin" wrote: > > Hello All - > > I'm just started the process of learning MySQL and databases in general. > I just bought the book 'MySQL' written by Paul DuBois, who I gather is > really active in this community. > > Anyhow.. Here's the problem that I'm having. > > mysql> GRANT ALL ON sampdb.* TO 'sampadm'@'localhost' IDENTIFIED BY > 'secret'; > Query OK, 0 rows affected (0.00 sec) > > mysql> show databases; > +--+ > | Database | > +--+ > | helpdesk | > | mysql| > | test | > +--+ > 3 rows in set (0.01 sec) > > Anyone notice the problem? Even though I get no error.. the sampdb is > not being greated. This is weird as I've been able to use this exact > command in the past with no problems. I even tried logging in as root > before starting the mysql client, still made no difference. > > Thanks in advance.. > > Jeff Jeff, You probably need to create the database first. "create database sampdb" then do the "grant all" walt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table error 127
On Monday 04 August 2003 02:10 pm, Richard Gabriel wrote: > I have been getting this type of error ever since I upgraded from MySQL 3 > to 4. I actually have set up a cron to check/repair tables hourly because > of this. The following diagnoses have been suggested, but I would bet it's > an obscure MySQL bug: > > 1. Kernel 2.4.18 IO problem > 2. Another shared library linked to MySQL (I use RPMs) > 3. RAID issue causing corruption > > Unfortunately I haven't been able to pinpoint which query causes the issue > so I can't report a bug. It anyone else has experienced this or has > information on it, I would really appreciate it. Thanks. > > Richard Gabriel > Director of Technology, > CoreSense Inc. > (518) 306-3043 x3951 Richard, You may already know this, but here is a snipit out of the manual "If you run mysqld with --skip-locking (which is the default on some systems, like Linux), you can't reliably use myisamchk to check a table when mysqld is using the same table. If you can be sure that no one is accessing the tables through mysqld while you run myisamchk, you only have to do mysqladmin flush-tables before you start checking the tables. If you can't guarantee the above, then you must take down mysqld while you check the tables. If you run myisamchk while mysqld is updating the tables, you may get a warning that a table is corrupt even if it isn't." -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 "If it's not broketweak it" CONFIDENTIALITY NOTICE The information contained in this email may contain legally privileged and confidential information intended only for the use of the individual noted above. If you are not the intended recipient or employee or agent of the entity listed above, you are hereby notified that any reading, disclosure, distribution, or copying of this email communication in any way, or the taking of any action in relation to this communication, is strictly prohibited. If you have received this email in error, please immediately notify the sender and contact our Privacy Officer at (800) 782-5150 ext: 1601. If you were not the intended recipient, please delete it from your files. Thank you for your compliance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Check table script
Jean Hagen wrote: > > Hey, > > having trouble running mysqlcheck from a script. Running 4.0.13 on > Linux. I can run the following command: > > /usr/local/mysql/bin/mysqlcheck -u root -p -A -a -m -v --auto-repair > > /var/log/mysql/check_tables.log > > from the command line, plug in the password at the prompt, and everything > works. When I put this exact command into an executable file, and add the > '=[pswd]' argument, I get an access denied error message: > > /usr/local/mysql/bin/mysqlcheck: Got error: 1045: Access denied for user: > '[EMAIL PROTECTED]' (Using password: YES) when trying to connect > > p.s. I've used this exact method on mysqldump, and it works. > > Any suggestions? Anyone have a mysqlcheck script that they run via cron? > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Jean, Have you tried adding [mysqladmin] user=root password=blabla to the .my.cnf file for the user running the script? walt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problem
On Wednesday 06 August 2003 11:13 am, Andy Smith wrote: > On Wed, Aug 06, 2003 at 03:59:53PM +0100, Andy Smith wrote: > > On Wed, Aug 06, 2003 at 10:52:54AM -0400, walt wrote: > > > Andy, > > > I don't see > > > "log-slave-updates" > > > in your master setup. I see log-bin, but I "think" that only applies to > > > updates done directly to the database (not replication updates). I'm > > > not 100% sure about that, but it may be worth looking into. > > > > OK, so why would that prevent my _slave_ from even doing "slave > > start"? > > OK, so I added log-slave-updates to the master and it did not fix > my problem. Thanks for your help though! Any other suggestions? 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? -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 "If it's not broketweak it" CONFIDENTIALITY NOTICE The information contained in this email may contain legally privileged and confidential information intended only for the use of the individual noted above. If you are not the intended recipient or employee or agent of the entity listed above, you are hereby notified that any reading, disclosure, distribution, or copying of this email communication in any way, or the taking of any action in relation to this communication, is strictly prohibited. If you have received this email in error, please immediately notify the sender and contact our Privacy Officer at (800) 782-5150 ext: 1601. If you were not the intended recipient, please delete it from your files. Thank you for your compliance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problem
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" -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 "If it's not broketweak it" CONFIDENTIALITY NOTICE The information contained in this email may contain legally privileged and confidential information intended only for the use of the individual noted above. If you are not the intended recipient or employee or agent of the entity listed above, you are hereby notified that any reading, disclosure, distribution, or copying of this email communication in any way, or the taking of any action in relation to this communication, is strictly prohibited. If you have received this email in error, please immediately notify the sender and contact our Privacy Officer at (800) 782-5150 ext: 1601. If you were not the intended recipient, please delete it from your files. Thank you for your compliance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table error 127
On Tuesday 05 August 2003 08:58 am, Jonathan Patton wrote: > Walt, > > Thanks for the suggestion. I ran myisamchk on the table and it said it was > corrupted. So I ran myismachk on the table with the -r and it said the > table was fixed. I then ran the update queries I was running before and > received the same 127 error. The update queries were: > > update discussion_categories discussion_categories1, discussion_categories > set discussion_categories1.parent_1 = discussion_categories.category_id > where discussion_categories1.`parent_1_text` = discussion_categories.name; > > update discussion_categories discussion_categories1, discussion_categories > set discussion_categories1.parent_2 = discussion_categories.category_id > where discussion_categories1.`parent_2_text` = discussion_categories.name; > > update discussion_categories discussion_categories1, discussion_categories > set discussion_categories1.parent_3 = discussion_categories.category_id > where discussion_categories1.`parent_3_text` = discussion_categories.name; > > update discussion_categories discussion_categories1, discussion_categories > set discussion_categories1.parent_4 = discussion_categories.category_id > where discussion_categories1.`parent_4_text` = discussion_categories.name; > > > So I shut the mysql server down, ran mysqlchk again and all the tables were > okay. I had a backup of the database, so I just dropped the whole thing and > imported from the backup. (The backup comes from another mysql server). The > errors still persisted. The table in question only has 167 rows in it. I > had a text file as well with the data in it, so I deleted all the data from > the file and loaded the data with the load data infile command. I did get > 1300 some warnings. Could the data being loaded in cause a table > corruption? I'm going to go through the 169 rows being loaded to see if I > can find out the problem or at least eliminate that problem from this > problem. Also, could it be the backup is corrupted? > > Jonathan > Jonathan, Have you tried running each query separately and then checking the table after each one? -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 "If it's not broketweak it" CONFIDENTIALITY NOTICE The information contained in this email may contain legally privileged and confidential information intended only for the use of the individual noted above. If you are not the intended recipient or employee or agent of the entity listed above, you are hereby notified that any reading, disclosure, distribution, or copying of this email communication in any way, or the taking of any action in relation to this communication, is strictly prohibited. If you have received this email in error, please immediately notify the sender and contact our Privacy Officer at (800) 782-5150 ext: 1601. If you were not the intended recipient, please delete it from your files. Thank you for your compliance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problem
On Wednesday 06 August 2003 11:50 am, Andy Smith wrote: > > 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. :( Looks fine to me as well Are you still getting the 1200 error when you try SLAVE START ? -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 "If it's not broketweak it" CONFIDENTIALITY NOTICE The information contained in this email may contain legally privileged and confidential information intended only for the use of the individual noted above. If you are not the intended recipient or employee or agent of the entity listed above, you are hereby notified that any reading, disclosure, distribution, or copying of this email communication in any way, or the taking of any action in relation to this communication, is strictly prohibited. If you have received this email in error, please immediately notify the sender and contact our Privacy Officer at (800) 782-5150 ext: 1601. If you were not the intended recipient, please delete it from your files. Thank you for your compliance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table error 127
On Tuesday 05 August 2003 09:06 am, Richard Gabriel wrote: > My database experiences a similar effect, but I can't pinpoint the specific > queries because it gets thousands per second. I have not noticed the > problem on a machine that is only used occasionally. Is there a way to get > queries out of the binlog for a specific date/time range? That might help > me pinpoint the problem and ultimately get this resolved. Thanks. Richard, You could run mysqlbinlog bin_log_file > /tmp/some_file.txt and then use something like perl or php to parse the text file looking for lines that begin with SET TIMESTAMP= . I'm not sure what format the time is in, but here is a snip from one of my logs. You can see the "human" time so all you have to do is figure out what time format the "1057631707" is. # at 618337 #030707 22:35:07 server id 100 Query thread_id=8185 exec_time=0 error_code=0 SET TIMESTAMP=1057631707; INSERT INTO SFG_TOTAL_PATS VALUES (bla,bla,bla, etc) -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 "If it's not broketweak it" CONFIDENTIALITY NOTICE The information contained in this email may contain legally privileged and confidential information intended only for the use of the individual noted above. If you are not the intended recipient or employee or agent of the entity listed above, you are hereby notified that any reading, disclosure, distribution, or copying of this email communication in any way, or the taking of any action in relation to this communication, is strictly prohibited. If you have received this email in error, please immediately notify the sender and contact our Privacy Officer at (800) 782-5150 ext: 1601. If you were not the intended recipient, please delete it from your files. Thank you for your compliance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: alter date
Fabio Bernardo wrote: > > Hi there, > Do you know a sql command which I can write to obtain the last date that I > updated a table Fabio, mysql> SHOW TABLE STATUS LIKE 'table_name' walt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: notification when replication stops.....
Tom Roos wrote: > > hi > > every now and then my replication stops. i now about this when i logon 2 the slave > and check the status. > > what is the best procedure i would follow 2 automate a notification message when > replication stops? > > i ryn mysql 4.0.13 on rh linux > > tks > > _ ___ _ __ >/______/ / ___/ / \ // > / / / // / / ^ v / >/ / / /__/ / / / \/ / / > /_ / /__ / /_ / /_ / > > Cell: 083 440 2213 > Ph: 021 980 4969 Tom, I'd use a perl script. You could either write it where it runs in a continuous loop checking every X seconds or use the cron daemon to launch the script. The advantage to using the cron daemon is that daemon will email you if something goes wrong in your script. Just my $.02 walt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
recover single table from binlog
Does anyone know if there is a tool or a good way to recover a single table from the binlogs ? I tried for n in dev-bin.*; do mysqlbinlog $n |grep table_name >> /tmp/file.sql; done but it just gives me the first line of the insert/update statement. I guess you could dump all the bin files out to a text file and then use a perl script to look for "insert into table_name" or "update table_name" and keep reading lines until you hit a ";". walt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
specify data type in select statement
We've run into a problem where binary char column types crash our c++ application when migrating from RedHat 7.3 to RedHat ES. Is there a way to specify the data type to be returned in the select statement. In the below example, selecting office_id will crash the app if it's left as a binary column. I'm looking for something like select char from office; create table office ( office_id char(8) binary not null, office_name varchar(50) ); Thanks! -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia "If it's not broketweak it" -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with variable tuning in my.cnf
I'd bump up these two settings. innodb_buffer_pool_size=32M innodb_additional_mem_pool_size=16M I'm not sure what the max is on innodb_buffer_pool_size, but that is where innodb caches data. The more that is in cache means less disk reads which equals speed. walt K Old wrote: > > Hello everyone, > > I am using a product that came packaged with Mysql 4.0.8-gamma and I > believe our configuration settings in my.cnf are too low and was wanting > a second opinion, and I'm aware of the my-huge.cnf file that comes > bundled with the mysql source and our variables are set way below the > my-huge.cnf defaults. We are using InnoDB tables and our tablespace > size is currently 2.3GB. We are running a 2Ghz IBM e-server with 3.5GB > RAM. Here's what are current settings are for mysqld in my.cnf: > > [mysqld] > port= 1205 > socket = /tmp/mysql.1205.sock > datadir = /home/edi/si/mysql/data > basedir = /home/edi/si/mysql > skip-locking > set-variable= key_buffer=16M > set-variable= max_allowed_packet=100M > set-variable= table_cache=64 > set-variable= sort_buffer=512K > set-variable= net_buffer_length=8K > set-variable= myisam_sort_buffer_size=8M > log-bin > server-id = 1 > transaction-isolation=READ-COMMITTED > #SI server > innodb_data_file_path = ibdata1:2256M;ibdata2:50M:autoextend > innodb_data_home_dir = /home/edi/si/mysql/var/ > innodb_log_group_home_dir = /home/edi/si/mysql/var/ > innodb_log_arch_dir = /home/edi/si/mysql/var/ > set-variable = innodb_mirrored_log_groups=1 > set-variable = innodb_log_files_in_group=3 > set-variable = innodb_log_file_size=5M > set-variable = innodb_log_buffer_size=8M > innodb_flush_log_at_trx_commit=1 > innodb_log_archive=0 > set-variable = innodb_buffer_pool_size=32M > set-variable = innodb_additional_mem_pool_size=16M > set-variable = innodb_file_io_threads=4 > set-variable = innodb_lock_wait_timeout=600 > set-variable = wait_timeout=114748364 > > Thanks, > Kevin > -- > K Old <[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]
many innodb datafiles on the same disk
Does anyone know if it is better to have 1 large innodb datafile on a disk or if it is better to have a few smaller datafiles? Here is what I have. Both drives are 15K scsi running at full 160MB/S speed. /var/lib/mysql/ - contains all myisam tables as well as a 1GB innodb datafile. /mysql2 - contains 3 innodb datafiles. Each 3GB in size. Would I be better off with a 9GB datafile on the /mysql2 disk or the 3 smaller files? The reason I ask is this machine is 1 of 2 slaves. We're planning on replacing our primary database with this one. I've run a query on this machine which is taking about a minute to run. If I run the same query on the other slave, it only takes about 13 seconds. The "only" difference in the mysql setup is that the other slave has a 6.2GB datafile on the /mysql2 partition instead of the 3 3GB datafiles. The other machine is also about 1/2 the speed of this one and runs on 5200rpm IDE drives. Below are the specs for the two machines. I'd try creating just a single datafile on the /mysql2 drive, but I don't want to shut down both slaves if I don't have to. Slow running slave - Athlon XP2200, 768MB RAM, 2 15K scsi drives running at full 160MB/s speed. set-variable = innodb_buffer_pool_size=300M set-variable = innodb_additional_mem_pool_size=50M Fast running slave - Pentium III 600MHz, 500MB RAM, 2 5200 RPM drives. set-variable = innodb_buffer_pool_size=200M set-variable = innodb_additional_mem_pool_size=20M Thanks! -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia "If it's not broketweak it" -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Performance Question
Rainer Sip wrote: > > I'm running a community site (Xoops) on Mysql 4.0.14. > > I found that the speed of my site is slow during peak hours, when there are 450 > concurrent uers hanging on the site. Mytop showed that the queries per second maxed > at 500. I believe this could be higher, provided that I have it running on a > dedicated machine. I also noticed the load average is very high (12+ during peak > hours) > > In the mid run I'm planning to mirgrate to innodb for higher concurrency (I'm > currently using myisam). However, I'm seeking suggestions in fine tuning the > parameters. > > The machine is a Dell PowerEdge 4400 with 2 Xeon 1G processors and 2GB of memory. > There are 4 disks running raid 0+1. Attached the my.cnf for your easy review. Thanks > a lot in advance. > Rainer, The "12+" load avg. is HIGH. What is % idle when the load average is high? walt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: C API
Priyanka Gupta wrote: > > Is there a way to have a common C API for MySQL and Oracle. I am writing > some software that I would like to work with both MYSQL or Oracle as the > backend server? > > priyanka > > _ > Enjoy MSN 8 patented spam control and more with MSN 8 Dial-up Internet > Service. Try it FREE for one month! http://join.msn.com/?page=dept/dialup > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Priyanka, This might work for you - http://otl.sourceforge.net/home.htm walt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
VB and mySql
I used to program with VB using MS access as the db. VB was very quick to develope nice looking forms. Which language would be the best to use with mysql to create similiar forms. These forms would be for scrolling through the db, adding, updating, deleteing, printing... Walt
Re: VB and mySql
I am new to mysql and started a tutorial for it and php. Mysql is running ok on my redhat server. I thought VB might be to unstable. VB is easy to use but I think its not web based. Also all the job adds I see want mysql and php. thanks for responding Walt - Original Message - From: "Freddie Sorensen" <[EMAIL PROTECTED]> To: "'Walt'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Sunday, March 07, 2004 12:20 PM Subject: AW: VB and mySql Walt You can also use VB with MySQL without problems. There are several connectors available at the MySQL site Freddie -Ursprüngliche Nachricht- Von: Walt [mailto:[EMAIL PROTECTED] Gesendet: Sonntag, 7. März 2004 18:01 An: [EMAIL PROTECTED] Betreff: VB and mySql I used to program with VB using MS access as the db. VB was very quick to develope nice looking forms. Which language would be the best to use with mysql to create similiar forms. These forms would be for scrolling through the db, adding, updating, deleteing, printing... Walt
innodb/mysql slow returning anything other than primary key
I've run into an interesting problem. I have a large innodb table (2274962 rows, 46 columns, 2 datafiles - 4.5GB total). When I run a query that uses a clustered_index in the where clause and the data I'm selecting is not the primary key of the table, it takes up to 2 1/2 minutes to return zero results (the result is correct). If I select the primary key, it takes 2 1/2 seconds to return zero results. For example: create table xray ( trans_id int not null, customer_id char(25) not null, customer_last_name char(25), UNIQUE INDEX trans_idx (trans_id), INDEX cus_id (customer_id ) ); type=INNODB; SELECT customer_last_name FROM XRAY WHERE customer_id = '12345'; This takes ~ 2 1/2 minutes to return 0 results. SELECT trans_id FROM XRAY WHERE customer_id = '12345'; This takes ~ 2 1/2 SECONDS. From what I understand, clustered indexes contain the primary key as well as the indexed data and return that key as a "pointer" to the row where the data being selected is located. This explains why selecting the key is so quick, BUT if no values are found in the index that match the where clause, why is it taking so long? I've run explain and it's telling me the database is using the correct index. I've even added USE INDEX to sql query with no improvement. The machine is an AMD Athlon XP 2000 with 1GB of ram. Mysql version is 3.23.53 OS = Linux 2.4.18-SGI_XFS_1.1enterprise Thanks for any ideas or suggestions! -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 "If it's not broketweak it" - 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: Using Perl DBI quote() method with NULL values?
Jeff Snoxell wrote:
> Hello,
>
> I've got a mysql table with a lot of fields and I'm using a map statement
> to pass each of my values for a new record through the quote system so-as
> to have everything nicely wrapped up. Problem is that I can't find any way
> of writing a null value to my DB when the value has first passed through
> quote().
>
> I've tried:
>
> my $err = $MyDatabase->do('INSERT INTO MyTable VALUES(' .
>join(",",map($MyDatabase->quote($_),
>0, # REF
>0, # PARENT REF
>$FormData{'Title'} . " " . $FormData{'Name'} . "",
>$FormData{'Email'} . "",
>"\N", # <-- I REALLY WANT THIS TO BE A NULL VALUE!
>etc.,
>etc.,
>etc.
> )) .
> ')');
>
> Which works fine except the "\N" isn't entered into my database as a null
> value. I've also tried '\N' and '' and "".
>
> Any ideas how I can solve this easily?
>
> Thanks,
>
> Jeff
>
> -
> 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
Jeff,
I'm not sure if this will help or not, but we ended up adding our own version of
quote to
perl cgis. Someone told me when I ask a similar question to check the value
before calling
quote, but it seemed like more of a headache... You may have to use perl's ord &
chr to look for
\n
walt
sub nea_quote
{
my ($input) = @_;
if (length($input) == 0)
{
$return_string = "NULL";
return $return_string;
}
else
{
$return_string = $dbh->quote($input);
return $return_string;
}
}
-
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: converting text to hypertext
Rick Tucker wrote: > I just imported a .csv file and one of the columns of data was websites > addresses. Those addresses aren't being recognized as links when I output > an html table from my queries. I'm scratching me head on how to make the > conversion. I figured there would by a hypertext datatype of some sort, but > I can't find any information regarding this issue. If someone could point me > in the right direction, I would appreciate it. > > Thanks, > > rick > > - > 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 Rick, Are the addresses appearing in the correct format (http://www.somewhere.com) when you pull them from the db? If so, you'll still have to build the part in the html so it is infact a hyperlink. walt - 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 replication problem
Vaso Koutsonikola wrote: > Hi, > I am replicating a table between 2 servers. > The table on the master executes many transactions that should be > replicated to the table on the slave.. > The table on the slave is only readable... > I have noticed that the changes on the master are not replicated at once > but it takes about 2 hours to > get the slave updated.. > When this happens I see on the slaves error log file the error > Error reading packet from server: Lost connection to MySQL server during > query (read_errno 134,server_errno=2013) > and then the slave seems to reconnect to the master. > Has anyone any idea why is this happening? > Is it possible that when I read data from the table on the slave, the > slave cannot be updated because it gets locked or something > like that? > > Thanks.. > > - > 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 Vaso, What version of mysql and what OS? walt - 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/innob optimizer problem with != and selecting other than primary key
I ran into a problem a few weeks ago with a query that would take up to 2 minutes to return 0 rows found when selecting anything other than the primary key (0 rows is correct). When I selected just the primary_key, the query would take less than 3 seconds. The 2 columns involved in the where part of the query are in a 2 column clustered index which aparently was being "ignored" when selecting anything other than the primary key and a != in where part of the query. The actual table is 48 columns * 2,457,684 rows. See below for better explanation. NOTES - order_status can have 3 possible values (N, T, Y) create table customer_orders ( order_idint NOT NULL AUTO_INCREMENT, customer_id char(8) NOT NULL, customer_session_id varchar(30) NOT NULL, order_statuschar(1) DEFAULT 'N' NOT NULL, cus_last_name varchar(20) NOT NULL, cus_first_name varchar(20) NOT NULL, cus_address1 varchar(50) NOT NULL, cus_address2 varchar(50), cus_zip varchar(10), UNIQUE INDEX ORD_ID_IDX (order_id), INDEX CUS_ID_SESSION_IDX (customer_id, customer_session_id), INDEX CUS_ID_ORD_STAT_IDX (customer_id, order_status), INDEX CUS_LAST_IDX(cus_last_name), INDEX CUS_FIRST_IDX(cus_first_name) ) TYPE=INNODB; SELECTING ONLY THE PRIMARY KEY - "select order_id from customer_orders where customer_id = 'timma" and order_status != 'Y' and order_status != 'T'"; query time < 3 seconds SELECTING MORE THAN THE PRIMARY KEY - "select order_id, cus_last_name, cus_first_name from customer_orders where customer_id = 'timma" and order_status != 'Y' and order_status != 'T'"; query time =~ 2 minutes SELECTING MORE THAN THE PRIMARY KEY BUT USING = INSTEAD OF != "select order_id, cus_last_name, cus_first_name from customer_orders where customer_id = 'timma" and order_status = 'N'"; query time < 3 seconds Hope this helps! mysql, query -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 "If it's not broketweak it" - 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: Managing replication logs
Danny Haworth wrote:
> I too would be interested in a good way of clearing the binary log files
> (preferably non disruptive to the server ;-)
> The only thing thats saving me so far is that the systems capacity is 320Gb.
>
> danny
>
> Anirudha Kukreti wrote:
>
> >hi all
> >i have established a two way replication setup
> >my problem is that my hard disk gets occupied by the log files;
> >i tried purging the files but after some time again my hard disk gets filled
> >with the log files
> >
> >
> >mysql, queries
> >
> >
>
> -
> 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
Here is a script I wrote which archives the logs in 2 places. We run it every 5
minutes from the cron dameon
It's designed to check a slave database located 40 miles away before moving the
logs. Since you are not keeping
the logs around, you may want to change the mv command to rm.
Hope this helps!
walt
#!/bin/bash
#achive_logs.sh
#
# Purpose - Archvive mysql log if size >= 5MB
# Log files are in master_server_name-bin.xxx format.
# Main script calls check_slave which in turn calls copy_log.
# server names
SLAVE=slave.nea-fast.com
MASTER=master
# length we'll need to strip off of log file name
LENGTH=${#MASTER}
STRIP=$((LENGTH + 5)) # we add 5 for "-bin."
# archive directories
BASE="/var/lib/mysql/"
ARCH1="/var/lib/mysql/mysql_arch1/"
ARCH2="/opt/mysql_arch2/"
# email address
[EMAIL PROTECTED]
[EMAIL PROTECTED]
# page() - Takes 2 arguments. function and dir
page()
{
echo "$1 couldn't copy to $2" | /bin/mail -s "Help!!" $PAGE
}
# copy_log - takes 2 arguments. The log file and a 0 or 1.
# If 2nd arg is 0, the slave db is in sync and it's cool
# to delete the log after coping. If 2nd arg is 1, the slave
# has not caught up and we dont want to delete the log from the
# mysql dir.
# If there is an error coping to either dir, dont
# delete the old log file! We'll try to copy again next time.
copy_log()
{
PROBLEM=$2
cp -f $1 $ARCH1
if [ $? -ne 0 ]; then
echo "copy-log couldn't copy to arch1"
page copy-log arch1
PROBLEM=1
fi
cp -f $1 $ARCH2
if [ $? -ne 0 ]; then
echo "couldn't copy to arch2"
page copy-log arch2
PROBLEM=1
fi
if [ $PROBLEM -eq 0 ]; then
echo "removing file"
rm -f $1
fi
}
# check_slave - takes one argument which is the log name. We
# need this function because we may end up with several log files
# in the mysql dir because the slave was not caught up when we last
# ran.
check_slave()
{
M_LOG=$1
M_LOG_SEQ=${M_LOG:$STRIP} #strip off "$MASTER-bin. from file name"
echo "getting info from slave `date`"
DATA=`ssh $SLAVE cat /var/lib/mysql/master.info`
echo "got data from slave `date`"
SLAVE_LOG=`echo $DATA | awk '{print $1}'` # Master log is first line
#strip off "$MASTER-BIN." from file name to get sequence number
S_LOG_SEQ=${SLAVE_LOG:$STRIP}
if [ $M_LOG_SEQ -lt $S_LOG_SEQ ]; then
# Slave has switched to new log so were ok. Send file name
# and 0 to copy_log. 0 tells copy_log to delete the file when finished
copy_log $1 0
else
# Slave hasn't switched to new log. Send file name and 1 to copy_log.
# 1 tells copy_log to NOT delete the file when finished.
copy_log $1 1
fi
}
# main script###
# The first thing we need to do is make sure we're not already running
if [ -f /var/lib/mysql/archiving ]; then
echo "We appear to still be running!!!"
exit 0
else
touch /var/lib/mysql/archiving
chmod 400 /var/lib/mysql/archiving
fi
# Make sure we can talk to other site
ping -c 5 $SLAVE > /dev/null # dev/null so cron dameon doesn't spam
if [ $? -ne 0 ]; then
echo "couldn't ping $SLAVE"
rm /var/lib/mysql/archiving
exit 0
fi
cd $BASE
# Get current log from index file
CUR_LOG=`tail -1 $MASTER-bin.index`
# Log name is stored as "./mysql_master.xxx" so we'll strip "./"
FILE=${CUR_LOG:2}
# Check size of file
SIZE=`du -ks $FILE | awk '{print $1}'`
if [ "$SIZE" -gt "2048" ]; then
# Get all log files
LOGS=`ls $MASTER-bin.* | grep -v $MASTER-bin.index`
/usr/b
Re: Innodb and tablespace
Vincent Ferretti wrote:
> I need help!
>
> I'm making some testing with innodb tables and there're some issues I
> don't understand.
>
> I created a small tablespace of 10M:
>
>innodb_data_file_path = ibdata1:10M;
>
> Then I created this simple database:
>
>CREATE DATABASE trace_db;
>use trace_db;
>
>CREATE TABLE chromatogram (
> id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
> trace MEDIUMBLOB NOT NULL,
> PRIMARY KEY (id),
>) TYPE=INNODB;
>
> Then I wrote a little Perl script to fill this table:
>
>my $fileName = "A03_017.ab1.bz2";
>for (my $i = 1; $i <= $max; $i++) {
> my $query = qq {insert into chromatogram set trace =
> LOAD_FILE("$fileName")};
> $dbh->do($query) || die;
>}
>
> Here is the problem: Although the size of the file is 63Kb, I can do
> only a maximum of 84 insertions (which represent a total of 5.3M).
> After that, I get the error message that the table is full.
>
> Command "show table status" gives me a data_length of 5.9M which is
> only 59% of the total tablespace allocated.
>
> I want to have a database containing hundreds thousand of those files.
> I can't obviously afford to loose 41% of my tablespace.
>
> Can someone explain to me those numbers and what should I do to
> optimize my disk space.
>
> Thank you very much
>
> Vincent
>
> -
> 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
Vincent,
Is there some particular reason you want to store the files inside of the
database?
There are many advantages to storing the files using the file system and storing
the
path in the database. One of the best arguments I've seen for doing it this way
is the
fact that the application getting data from the db can get the path and then
spawn a "child"
process to fetch the file while retreiving another path from the database. If you
pull the files
directly from the database, the application will have to wait until the database
returns each file
before it can begin fetching another.
Just a suggestion.
walt
-
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: Converting DBs
"Tamayo, Nelson" wrote:
> Is there a way to convert an Access database into MySQL easily?
>
> I am running into problems trying to convert it through VB code using a
> recordset. I'm rather new at this stuff. I'm also tried generating an insert
> statement through VB code and then executing it, I get the error "Unknown
> column 'False' in 'field list'"
>
> this is the SQL string I am using to insert the record.
>
> insert into csapps.qainfo
>
> (DCN,NoRetention,AttachmentCode,NPPN,CPTList,ReceivedDate,Answered,ClientID,
> PPO,Retry,Tax,EOP,TXDeficientIsAnswered,TXDeficient,CACPAnswered,CACPAuthori
> zed,MGMAnswered,MGMContracted,Priority,ImageStatus,BatchCode)
>
> values
>
> ('123673852',False,'E : JUST an ID CARD',False,
> 'Dental','8/19/2001',True,'TN','??',0,0,'TX',True,False,False,False,False,Fa
> lse,1,'NotRequested','N1TN236012')
>
> Any ideas?
>
> Nelson Tamayo
> Claims Automation
> (800) 237-7767 x3520
> [EMAIL PROTECTED]
>
> The information contained in this transmission is legally privileged and
> confidential information intended only for the use of the individual or
> entity named above. If the reader of this message is not the intended
> recipient, you are hereby notified that any dissemination, distribution, or
> copy of this transmission is strictly prohibited. If you have received this
> transmission in error, please contact the sender immediately.
>
> -
> 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
Nelson,
It looks like you need to put ticks around your true and false
values like below
('123673852','False','E : JUST an ID CARD','False',
'Dental','8/19/2001','True','TN','??',0,0,'TX','True','False','False','False','False','Fa
lse',1,'NotRequested','N1TN236012')
walt
-
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: Datetime vs Unixtime
Paul DuBois wrote: > At 10:34 -0500 3/13/03, Keith C. Ivey wrote: > >I am curious why a DATE takes 3 bytes and a TIME takes 3 bytes, but a > >DATETIME takes 8 bytes, even though TIME covers a much greater range > >than the time part of a DATETIME, but that's just one of the > >mysteries of MySQL that's probably not worth losing sleep over. > > I believe that to get that information you must sign a > non-disclosure agreement. > > > > >[Filter fodder: SQL] > > - > 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 You can't download the source? - 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: Replication error
trashMan wrote: > Please.seven days to try it!! Help me!! :-( > > -- > > Hello, > I've tried to setup a replication but ...i've several problem! I've > follow the manual istruction > http://www.mysql.com/doc/en/Replication_HOWTO.html > But the slave don't start the replica. > > SHOW SLAVE STATUS on SLAVE return SLAVE:running > > SHOW PROCESSLIST on SLAVE return reconnecting after a failed read > > Any suggestion about this?? Please, help me! I don't know what can i do! > > The master and the slave are not in the same network: the master is a > server located in a webfarm and the slave is my pc. If i try to connect > me to mysql MASTER from my pc via mysql --host= > --user=userforreplica --password=pwdforreplica i enter but i can not do > nothing. > > Thanks > > Massimiliano and samuela > > - > 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.phpI I'm not sure I understand your email. Are you able to connect to the master? If so, the only Privileges you should have are replication unless other Privileges have been granted to userforreplica. walt - 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 Client on Linux won't connect to MySQL Server on XP
Paul Larue wrote: > Hi all, > I got MySQL 3.23.55-nt running on XP and MySQL Client 3.23.36 running on > Linux Red Hat 7. I'm trying to have the client connect to the server but I > get the same error all the time: > > [EMAIL PROTECTED] /root]# mysql -h 10.0.0.41 -u guest > ERROR 1130: Host '10.0.0.220' is not allowed to connect to this MySQL server > > Could it be that the server and the client are not running the same version > and OS? Actually this situation is only a simulation of the future setup > which will be a win2k box holding the MySQL Server and the Linux box still > holding the client. Apache will run on the linux box as well as PHP so that > I can connect to mysql. > > I'd be glad to hear from people having a similar setup and who would be > willing to give me tips on problems I could encounter as well as some hints. > > Thanks in advance > > Paul > > - > 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 Paul, Why are you running Client 3.23.36? As far as the error goes, it looks like you need to run something like this on the master GRANT SOME_PRIVILEGE ON database_name to [EMAIL PROTECTED] or GRANT ALL PRIVILEGES ON database_name to [EMAIL PROTECTED] walt - 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: running an mysql client on a linux cluster?
Murad Nayal wrote: > Hello, > > I wonder if anyone has encountered this problem before and has any > ideas. I need to run programs on a linux cluster that make client > connections to an mysql database on a different server. this linux > cluster is set up where only the main node has an internet connection. > so the problem is how make it possible for programs running on the > internal nodes to make connections to the mysql server. This may not be > a strictly an mysql question. but I am in a little bit of a bind and > would greatly appreciate any help if anyone has experience dealing with > a similar situation. > > many thanks > > Murad > > - > 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 Murad, You could write a program that runs on the master node which makes the request on behalf of the slave/secondary nodes and then returns the result back. We use something similar to this that we call a broker. The broker is the only thing that "talks" to the database. All clients make requests to the broker , the broker gets/updates the info, and returns the result to the client. It's a very simple concept and has worked well. We wrote it to get around Oracle licensing (only one user connected to the db, but we could handle requests from multiple web servers). Hope this helps! walt - 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: FreeBSD + MySQL bottleneck
Matthias Trevarthan wrote:
>
> Howdy list,
>
> I run MySQL 3.23.54 with FreeBSD 4.6-RELEASE.
>
> We recently had a BBS get hammered by a lot of
> concentrated traffic.
>
> I currently run a 'mysql-optimize.sh' script from
> cron on Wednesday and Sunday that executes:
>
> ${bindir}/myisamchk -i -r --check --sort-index --analyze ${datadir}/*/*.MYI
> ${bindir}/isamchk -i -r --analyze --sort-index ${datadir}/*/*.ISM
>
> This works great to keep my databases lean and mean
> for normal server load and traffic, but this last
> hit was just too much. The server was bottlenecked
> somewhere.
>
> Problem was, I couldn't figure out where!
>
> I'm running SCSI 160 disks in a Raid config, with a
> dual 1GHZ PIII and 1G of SDRAM. I'd think that setup
> would be able to handle some pretty killer loads...
>
> Anyway, I ran 'top', and MySQL was turning about 97%
> processor utilitzation on one processor.
>
> It said I still had 128M of free ram left (and my
> MySQL tables are all under 10M). And I was only using
> 3% swap, which is normal because I run phpa_accelerator.
>
> Also, 'mysql> show status;' showed that I only had
> about 25 threads open at a time. And it also said I
> had 82 tables open.
>
> My T1s were NOT maxed out. They weren't even half full,
> and besides: I accessed the BBS from our 100Mb switch,
> and it was still dog slow (20-30 seconds for a page load),
> which means it was purely a bottleneck in my machine.
>
> So, my questions are these:
> ---
>
> Can any experienced MySQL-FreeBSD admins out there
> give me some pointers for identifying bottlenecks?
>
> Specifically, I don't know how to determine if my
> disks were being maxed out. Could someone give me
> some pointers?
>
> And also, from 'show status', is the number of 'threads'
> directly related to the specific number of MySQL socket
> connections? (I have MySQL setup to allow up to 200 and I
> wasn't even getting over 30 from 'netstat | grep -i mysql')
>
> Any help would be appreciated! Thanks!
>
> Matthias
>
> -
> 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
Matthias,
I'm not sure on BSD (never used it) but with Linux if your processor
load is high and load
avg is low, you are not suffering from an I/O bottleneck. If your load
avg
was high and cpu was low, then you have lots of I/O. It sounds like you
have
a query or two that needs to be optimized. I'd run "show full
processlist"
next time it bogs down and test all running queries on another system
and see if
you can tweak them or move the logic out of the database into the actual
code
that's making the query.
Hope this helps!
walt
-
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: "select distinct" doesn't coalesce NULL rows
Smurf wrote:
>
> >Description:
> "select distinct FOO from BAR" reports multiple NULL rows
> >How-To-Repeat:
> Unknown. It's a large table (1 entries or so). The problem
> didn't show with a simple test table.
>
> The table:
>
> -- MySQL dump 10.0
> --
> -- Host: localhostDatabase: pop
> -
> -- Server version 4.1.0-alpha-debug-log
>
> --
> -- Table structure for table 'person'
> --
>
> DROP TABLE IF EXISTS person;
> CREATE TABLE person (
> descr int(11) default NULL,
> id int(11) NOT NULL default '0',
> kunde int(11) NOT NULL default '0',
> nameid int(11) default NULL,
> abtid int(11) default NULL,
> mailid int(11) default NULL,
> foneid int(11) default NULL,
> faxid int(11) default NULL,
> pagerid int(11) default NULL,
> isdnid int(11) default NULL,
> dest varchar(4) default NULL,
> adrid int(11) default NULL,
> ausweis int(11) default NULL,
> zusatz int(11) default NULL,
> suche int(11) default NULL,
> username int(11) default NULL,
> passwort int(11) default NULL,
> uid int(11) NOT NULL default '0',
> pwsubdir int(11) default NULL,
> pwuse bigint(20) NOT NULL default '0',
> udomain int(11) default NULL,
> uip int(11) default NULL,
> proto smallint(6) default NULL,
> maxconn tinyint(4) default NULL,
> ulocip int(11) default NULL,
> uremip int(11) default NULL,
> prefcall smallint(6) default NULL,
> tarif int(11) default NULL,
> satz tinyint(4) default NULL,
> mperson int(11) default NULL,
> gebtag smallint(6) default NULL,
> gebjahr smallint(6) default NULL,
> funktion int(11) default NULL,
> ustid varchar(10) default NULL,
> timestamp timestamp NOT NULL,
> PRIMARY KEY (id),
> KEY suche (suche),
> KEY username (username),
> KEY uid (uid),
> KEY mailid (mailid),
> KEY mperson (mperson),
> KEY timestamp (timestamp)
> ) TYPE=InnoDB CHARSET=latin1;
>
> The statement: ("explain select distinct mperson from person"):
> id select_type table typepossible_keys key key_len ref rows
>Extra
> 1 SIMPLE person index NULLmperson 5 NULL8125Using index
>
> >Fix:
> None known.
>
> >Submitter-Id:
> >Originator:Matthias Urlichs
> >Organization:
> noris network AG, Nuernberg, Germany
> >MySQL support: licence
> >Synopsis: "select distinct" reports multiple NULL rows
> >Severity: serious
> >Priority: high
> >Category: mysql
> >Class: sw-bug
> >Release: mysql-4.1.0-alpha (Up-to-date Bitkeeper distribution)
>
> >C compiler:gcc.real (GCC) 3.2.3 20030228 (Debian prerelease)
> >C++ compiler: g++.real (GCC) 3.2.3 20030228 (Debian prerelease)
> >Environment:
>
> System: Linux play.smurf.noris.de 2.4.19-586tsc #1 Sun Oct 6 18:00:21 EST 2002 i686
> unknown unknown GNU/Linux
> Architecture: i686
>
> Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
> GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/3.2.3/specs
> Configured with: ../src/configure -v
> --enable-languages=c,c++,java,f77,proto,pascal,objc,ada --prefix=/usr
> --mandir=/usr/share/man --infodir=/usr/share/info
> --with-gxx-include-dir=/usr/include/c++/3.2 --enable-shared --with-system-zlib
> --enable-nls --without-included-gettext --enable-__cxa_atexit --enable-clocale=gnu
> --enable-java-gc=boehm --enable-objc-gc i386-linux
> Thread model: posix
> gcc version 3.2.3 20030308 (Debian prerelease)
> Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' ASFLAGS=''
> LIBC:
> lrwxrwxrwx1 root root 13 2003-02-28 17:59 /lib/libc.so.6 ->
> libc-2.3.1.so
> -rwxr-xr-x1 root root 1104072 2003-02-25 14:46 /lib/libc-2.3.1.so
> -rw-r--r--1 root root 2337976 2003-02-25 14:47 /usr/lib/libc.a
> -rw-r--r--1 root root 178 2003-02-25 14:47 /usr/lib/libc.so
> Configure command: ./configure '--prefix=/usr' '--exec-prefix=/usr'
> '--libexecdir=/usr/sbin' '--datadir=/usr/share' '--sysconfdir=/etc/mysql'
> '--localstatedir=/var/lib/mysql' '--includedir=/usr/include'
> '--infodir=/usr/share/info' '--mandir=/usr/share/man' '--enable-shared'
> '--enable-static' '--enable-thread-safe-client' '--enable-assembler'
> '--enable-local-infile' '--with-raid'
> '--with-unix-socket-path=/var/run/mysqld/mysqld.sock' '--with-mysqld-user=mysql'
> '--with-libwrap' '--with-client-ldflags=-lstdc++' '--with-embedded-server'
> '--with-vio' '--with-openssl' '--without-docs' '--without-bench'
> '--without-readline' '--with-extra-charsets=all' '--with-debug' '--with-innodb'
>
> -
> 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 un
Re: Process Limit on Linux ?
Philipp wrote: > > Hi there, > > i wrote several times to the list asking for help with a problem > regarding process limits on linux, but never got an answer. > today i found this story: > > http://www.mysql.com/press/user_stories/handy.de.html > > here are the relevant sentences: > > "We had some process limit problems on our Linux Systems, > but thanks to your support we where able to patch the linux boxes > and move the limit to a size that meets our needs (we've got an average of > about > 1600 concurrent threads per server)." > > These people use 2.2 Kernels so i dont know if the mentioned kernel and > glibc > patching is also relevant for me, as i am using 2.4 kernels only. > > Here is my problem in detail: > > i am using mysql-3.23.55 binary packages on linux 2.4.20 and i raised ulimit > values and configuration in my.conf to allow more then 1500 threads. but > when > there are around 750 threads a new client connecting is told something like > that (dont have the errno at the moment, i think its 11): > > "cant create new thread, perhaps you are out of memory or there is a > os-depended bug". > > The machine only runs apache and mysql and is a Xeon 2x2 2.4 Gz with 2 GB of > RAM. > cat /proc/meminfo sais that more then 1 Gig is used for caching, so memory > should be no > problem . > > Please, if you have any ideas, let me know. If it is a kernel issue, tell me > to go to linux mailing lists > or if its some kind of "secret issue" only the support will be able to > answer let me know that. > > Thanks in advance, > Philipp > > - > 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 Philipp, Did you check /proc/sys/kernel/threads-max? I know with oracle 8i, you are supposed to increase /proc/sys/kernel/shmmax as well as some other values. You might check into that and see if changing those values will help. Does your syslog say anything when these problems occur? walt walt - 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 processor utilization
Sherif Diaa Mohamad wrote: > > I installed mysql on Redhat Linux, I have a problem that it uses 85% of the > processor making the idle time of the processor = 0 while I just hosted 1 > site with php > > Is that normal or I have something wrong ? > > Best Regards > Sherif Mohamad > > - > 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 Sherif, What version of RH and what version of mysql? Does the mysql error log show anything? Does the syslog show anything strange? walt - 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: Process Limit on Linux ?
On Wednesday 19 March 2003 12:38 pm, Dan Nelson wrote: > In the last episode (Mar 19), Philipp said: > > i just talked about openmosix because i read about shared memory > > segments. my only desire is to make mysql able to spawn 1000-1500 > > connections and *not* to tell my client "cant create new thread, > > perhaps out of memory" while 1.5 GB of RAM is only used for caching. > > Is that really possible that mysql is not able to handle this amount > > of connections ? > > With appropriate tuning you should be able to handle that amount. Check > the list archives; there have been many posts showing how to adjust > Linux's limits to allow for lots of threads. I just saw in the latest mysql manual (downloaded per page html tarball) in the linux installation notes a discussion on linux threads. The html file is manual_Installing.html#Linux -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 "If it's not broketweak it" - 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: images with mysql
Lai Liu-yuan wrote: > > Well, this may be off topic. > > In my case, I store tens of thousands of images, gradually growing. All of them are > quite small, most around 30*30 gray scale. Would it still be faster to store them on > disk? > In most cases yes. We have over 3.5 million images stored and mananaging them from the database side would be almost impossible. There are many benifits to using the file system instead of the database. 1. You can archive the images on cd/dvd/tape as new ones are received/added. 2. Try converting or moving databases with 200GB of blob files. 3. The larger the database, the longer it takes to retreive information. 4. Data from a database is retreived one row at a time so you have to wait for each image. Storing the path in the database allows you to fetch a row, spawn a child process to fetch the image, and continue to fetch rows from the database while the child processes handle getting the images. 5. Mysql will not cache the images. The OS however will cache disk reads. 6. The database has a finite set of resources. You can add many file servers, each specific to what "type" of image you're pulling. We store stuff by date so we know to pull 1998 images from fileserver a and 1999 images from fileserver b, etc... The OS on each fileserver can then cache what is requested most often. Good luck! walt - 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: Question on choosing a MySQL API
Vikram Vaswani wrote: > > Hi, > > The MySQL API is available in a number of languages - PHP, Perl, C, Java, > etc. I was wondering if anyone here had any thoughts on the decision > criteria to be kept in mind when choosing which language to use when > programming with the MySQL API? > > A brief list of these would be very helpful to me. Or if you could point me > to a link, that would be great too! > > TIA, > > Vikram Speed. The fastest is C followed by C++. Behind that would be java and behind java would be Perl or PHP. The downside to C and C++ is development time and debugging code. Java is great because it's fast and you don't have to deal with pointers. walt - 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
16+ hours to drop an index?
Does anyone have an idea why it would take over 16 hours to drop an index on an innodb table. The table has/had about 1.7 million records. I used the sql query "alter table table_name drop index index_name". I issued the command before I left work yesterday and it was still running this morning. TOP reported 0% idle. -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 "If it's not broketweak it" - 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: Real-time data warehousing
Brad Teale wrote: > We are warehousing real-time data. The data is received at up to T1 speeds, > and is broken up and stored into the database in approximately 25 different > tables. Currently MySQL is doing terrific, we are using MyISAM tables and > are storing 24 hours worth of data but we don't have any users and we need > to store 72 hours worth of data. > > Our concern is that when we start letting our users (up to 200 simultaneous) > hit the database, we won't be able to keep up with ingesting and serving > data with the MyISAM locking scheme. > > We have tested Oracle and PostgreSQL which fell behind on the ingest. The > current production system uses regular ISAM files, but we need to make a > certification which requires a relational database. Also, the current > production system doesn't have the feature list the new system has. > > Is there a better database solution or do you think MySQL can handle it? > If MySQL can handle it, would we be better off using InnoDB or MyISAM > tables? > > Thanks, > Brad > > - > 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 Brad, We're in process of evaluating mysql vs our current Oracle 8 system. Importing data is much faster in mysql than oracle according the numbers we're getting. However, from our benchmarking, Oracle seems to be faster on the queries (no writes to db during query time). The table were running our queries against has 46 coulmns and 14 indexes (some columns indexed twice in multi-column indexes). All queries are based on indexed columns. We've also run into some issues trying to delete indexes, 14+ hours before we killed the db and reloaded data, but I may be something stupid. One note on Oracle, $30,000+ for a single processor licence. From our testing, it looks like the bottleneck is disk I/O not processing power. With Oracle, you have better control over which disks your data resides on which lets you balance disk I/O better. However, for $30k, you can buy 10 15,000 rpm drives, stripe them, and then buy another server for replication of data and still have $25K left over. - 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: Real-time data warehousing
On Friday 17 May 2002 12:58 pm, Brad Teale wrote: > I forgot to mention, we have Oracle in-house, and the machine the MySQL > database will reside on is a 2 proc Sun box with 1.5G of RAM. The Oracle > databases reside on a 16 proc Sun box with 10G of RAM. How are your apps written? We use OTL libaries from http://members.fortunecity.com/skuchin/home.htm which are compiled into our C/C++ code. Moving our apps from oracle to mysql only requires changing 3 or 4 lines per call to the db in the code. Its not odbc "compliant", but still allows our apps to be "farily" portable and fast. We debated rewriting our apps to be ODBC compiant, but figured that was one more layer for bugs and we'd have to switch db platforms 4 times for it to be cost effective. Have you tried Oracle on Linux? We did some testing before Oracle told us the cost of migrating our licence from Oracle8/Solaris to Oracle8i/Linux. We benchmarked our current db server, Sun Ultra single processor 768MB ram, against a 600Mhz 500MB ram Intel/Linux box. The Linux./8i/Intel box smoked our current db server. - 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
innodb rollbacks
Does anyone know where rollbacks are stored for innodb tables? I assume memory since I keep getting a table full error when trying to drop an index. sql,query Thanks for your help! -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 "If it's not broketweak it" - 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 message....somewhat lost
try using "which" instead of "whereis" "Simon K. Chan" wrote: > Gelu, > > Thanks for taking the time to respond. I did this: > > whereis mysqld > > and get this: > > mysqld: > > which is really weird, because mysqld is located in /usr/local/mysql/bin > > I would appreciate any advice you can offer me! thanks! :-) > > --- Gelu Gogancea <[EMAIL PROTECTED]> wrote: > > ...sorry ... idon't read carefully your message... > > check with : > > whereis mysqld > > ... if is in /usr/libexec then check if is executable. > > If is in other directory "whereis" tell you and you can move "mysqld" from > > "whereis" to /usr/libexec. > > > > > > - Original Message - > > From: "Simon K. Chan" <[EMAIL PROTECTED]> > > To: "Mysql Help" <[EMAIL PROTECTED]> > > Sent: Monday, May 20, 2002 8:14 AM > > Subject: error messagesomewhat lost > > > > > > > > > > Hi All, > > > > > > The other day, I tried connecting to mysql > > > via the good old > > > > > > >mysql -u me -p > > > > > > but I got a message saying that my mysql.sock is missing > > > from the tmp folder. After checking the mailing list > > > archive, I decided (well, guessed is the more appropriate word) > > > that I could try to re-start the daemon: > > > > > > bin/safe_mysqld --log & > > > > > > But i get this error message: > > > The file /usr/local/mysql/libexec/mysqld doesn't exist or > > > is not executable > > > Please do a cd to the mysql installation directory and restart > > > this script from there as follows: > > > ./bin/safe_mysqld. > > > > > > Now I'm really confused!! I tried entering a) ./bin/safe_mysqld and b) > > ./bin/safe_mysqld. > > > (thinking that the period is part of the command...) but that didn't > > work. > > > a) just gets me the same error message and b) isn't even a command! :-( > > > > > > Comments? > > > > > > Many thanks! > > > > > > simon > > > > > > __ > > > Do You Yahoo!? > > > LAUNCH - Your Yahoo! Music Experience > > > http://launch.yahoo.com > > > > > > > > > > > > = > > > # > > > > > > Warmest Regards, > > > Simon K. Chan - [EMAIL PROTECTED] > > > > > > "Great spirits have always encountered violent opposition from mediocre > > minds." - Albert Einstein > > > > > > __ > > > Do You Yahoo!? > > > LAUNCH - Your Yahoo! Music Experience > > > http://launch.yahoo.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 > > > 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 > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > = > # > > Warmest Regards, > Simon K. Chan - [EMAIL PROTECTED] > > "Great spirits have always encountered violent opposition from mediocre minds." - >Albert Einstein > > __ > Do You Yahoo!? > LAUNCH - Your Yahoo! Music Experience > http://launch.yahoo.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 > 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 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Need some direction please
Webmaster wrote: > Here's my dilema. Our website runs a quarterly prize giveaway. Currently > we have an ASP form that gathers the inputed info into a database and > then sends us an e-mail confirmation of the entry. Our rules > specifically state that only one entry is allowed per quarter. As I am > not a programmer (just a dabbler in programming) I have no idea on how > to do the following and need some help. > > What we need is a script that will check our current database against an > e-mail, last name, and first name entry. If that entry is in our > database a message pops up telling them that they have already > registered with us. If there entry is not located in our database then > the new info passes along unhindered. > > We are on a Cobalt RaQ4r with ChiliAsp 3.62 installed and are using > MySQL. > > Thanks in advance for the help. > > Sincerely, > > Stephen M. LaBar, Jr. > (770) 381-3826 > Computer Specialist > > Please Visit Our Site At: > http://www.csi-atl.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 add primary_key (e-mail, last name, first name) when checking the result from the db, parse the error message looking for the error string mysql generates when a primary_key is violated - 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: innodb rollbacks
On Tuesday 21 May 2002 12:24 am, Jeremy Zawodny wrote: > On Mon, May 20, 2002 at 12:25:26PM -0400, walt wrote: > > Does anyone know where rollbacks are stored for innodb tables? I > > assume memory since I keep getting a table full error when trying to > > drop an index. sql,query > > I'm not exactly sure what you're asking, but the rollback information > needs to be stored on disk. Otherwise it cannot recover form a crash. > > Anyway, I suspect that your problem is that your tablespace is full. > Can you try added another file and see if that makes the problem go > away? > > Jeremy Thanks for your reply Jeremy! I'm an Oracle person, so I tend to think of things being in multiple files/tablespaces. Do you know if there is a way to view the rollback stats? I've got 6GB of datafiles and only 1 table loaded with 46 rows, 14 indexes, and approx 1.6million records. I'm trying now to load all the data again in a seperate table to see what it takes to fill up the datafiles. Thanks again! -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 "If it's not broketweak it" - 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: Ibdata1 file thats just too big...
"Williamson, David" wrote: > Hi there, > > My Ibdatafile is set too big (the disk is full to the point that nothing is > executing properly) I am wondering is it safe to delete this file and then > in the my.cnf file reset the size to something smaller... - how does the > Ibdata file work in conjunction with mysql... > Will I lose data if I do this? > > Many thanks, > > David > > - > 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 windows or 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 <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
specifying date format for search
Does anyone know if version 4.0 will support formatted date queries.
Example:
select create_date from what_ever where create_date >
date_format('03/01/2002','%m/%d/%Y')
sql, query
--
Walter Anthony
System Administrator
National Electronic Attachment
Atlanta, Georgia
1-800-782-5150 ext. 1608
"If it's not broketweak it"
-
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
perl dbh->quote and numeric columns
I'm in the process of re-writing 100+ perl CGIs written for oracle to mysql.
Does anyone know how to get the dbh->quote(some_value) function in perl to
return the string "NULL" if some_value is empty. Currently its returning
'' (2 ticks) and when you update a numeric column, it sets the value to 0
"zero".
For example:
update kids set age = '' where bla bla...
sets the value of age to 0 which is not NULL
That works fine for oracle, but not mysql. I would perfer not to litter the
code with a bunch of
if (length($some_variable) == 0)
{ $quoted_some_variable = "NULL"; }
else
{ $quoted_some_variable = $dbh->quote($some_variable); }
sql, query
Thanks!
--
Walter Anthony
System Administrator
National Electronic Attachment
Atlanta, Georgia
"If it's not broketweak it"
-
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: perl dbh->quote and numeric columns
On Thursday 27 June 2002 02:30 pm, Mike(mickalo)Blezien wrote: > Walt, > > Use Perl's 'undef' for NULL values. > > $quoted_some_variable = undef; > > now the $quoted_some_variable will be a null value. > > >>On Thu, 27 Jun 2002 12:50:55 -0400, walt <[EMAIL PROTECTED]> wrote: > >> > >>I'm in the process of re-writing 100+ perl CGIs written for oracle to > >> mysql. > >> > >>Does anyone know how to get the dbh->quote(some_value) function in perl > >> to return the string "NULL" if some_value is empty. Currently its > >> returning '' (2 ticks) and when you update a numeric column, it sets the > >> value to 0 "zero". Thanks for you reply Mike! This almost gets me where I want, but I'd still have to run a check to see if the value for the variable is null and that is what I'm trying to get away from. There are about 10 editable text fields in one the CGIs and many of those may be blank (user deleted the value) in which case I'd need to update that column in the db to NULL. Any other ideas would be appreciated! Thanks! -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 "If it's not broketweak it" - 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 works but error log full of junk
I've set up replication and the slave is updating any changes made to the master, but these errors keep showing up in the error log. Any ideas?? 020716 12:57:26 Slave: Failed reading log event, reconnecting to retry, log 'walt-bin.002' position 265 020716 12:57:26 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'walt-bin.002' at position 265 020716 12:57:56 Error reading packet from server: (server_errno=1159) Thanks for any help! sql, query -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 "If it's not broketweak it" - 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
slow count queries
I have an INNODB table which has 4 records in it. customer_number is the primary key. If I run select count(cutomer_number) from customer; It takes about 15 seconds to return the number of rows. I ran explain on the query and it's using the unique key index on customer_number. If I run SHOW TABLE STATUS LIKE 'cutomer'; it only takes about 1.52 seconds for innodb to tell me number of rows. Is there a faster way to get the number of rows without using "show table status" ? I ran the same query on an oracle db with 1762058 rows and it took 8 seconds. Thanks! -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 "If it's not broketweak it" - 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 count queries
On Tuesday 16 July 2002 05:05 pm, Jeremy Zawodny wrote: > On Tue, Jul 16, 2002 at 03:52:35PM -0400, walt wrote: > > I have an INNODB table which has 4 records in it. customer_number is > > the primary key. > > > > If I run > > select count(cutomer_number) from customer; > > > > It takes about 15 seconds to return the number of rows. > > I ran explain on the query and it's using the unique key index on > > customer_number. > > That's documented. InnoDB must read the whole table. > > > If I run > > SHOW TABLE STATUS LIKE 'cutomer'; > > it only takes about 1.52 seconds for innodb to tell me number of rows. > > That's an estimate of the number of rows. > > Jeremy Thanks Jeremy! After I sent this email, I changed the primary key to a unique index and it took under 2 seconds to return the number of rows. Do you know of any advantage a primary key would have over a unique index? The unique index still enforces the uniqueness of the column. Kinda funny that the error message from violating the unique index is "Duplicate entry '104191' for key 1" :-) Thanks! -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 "If it's not broketweak it" - 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
removing duplicate records
Does anyone know a good way find and remove duplicate records from a table? I can create an identical table structure and use a script to pull records from the existing table and insert them into the new table if they are not duplicates, but I'd rather not do it that way. Here is an example of an sql script I use for Oracle databases delete from employee a whererowid < ( select max(rowid) fromemployee b where b.COL1 = a.COL1 and b.COL2 = a.COL2 and b.COL# = a.COL#); sql, query Thanks in advance! -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 "If it's not broketweak it" - 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: removing duplicate records
Thanks David! The entire row, not just one or two columns, is a duplicate which makes life fun.. :-) I can key or unique index only a few columns once the data is cleaned up to prevent this problem. If I create an identical table and include either a key or unique index (innodb seems to like the index better) on all the columns and do a insert into new_table using select * from old_table will mysql quit inserting once a duplicate is hit, or will it keep going and skip over the duplicates? walt On Tuesday 30 July 2002 03:57 pm, David Kramer wrote: > You could always use an insert statement into a second table, when > performing the insert use a GROUP BY clause to consalidate your records > > something along these lines but this isnt 100% accurate, I would need the > table DDL and business rules behind the DEDUP > > Insert into tableB > ( > column names, ... > > > ) > (select > column a, > column b, > max(column c), --or you could use MIN > from > table A > > group by > column a, > column b); > > > > > > **JUST make sure your Identifing column, i.e. the column you use to tell > which is a duplicate record or not is included in the group by. Also what > are the business rules behind the DEDUP(Deduplication)? Are the other > values contained in the other columns necessary? If you tell me more about > what your trying to do and provide some Table DDL I can help you write this > query. Just let me know! > > Thanks, > > DK > > group by statement > > -Original Message- > From: walt [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, July 30, 2002 12:43 PM > To: [EMAIL PROTECTED] > Subject: removing duplicate records > > > Does anyone know a good way find and remove duplicate records from a table? > I can create an identical table structure and use a script to pull records > from the existing table and insert them into the new table if they are not > duplicates, but I'd rather not do it that way. Here is an example of an sql > script I use for Oracle databases > > delete from employee a > whererowid < ( > select max(rowid) > fromemployee b > where b.COL1 = a.COL1 > and b.COL2 = a.COL2 > and b.COL# = a.COL#); > > sql, query > > Thanks in advance! - 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: Running an SQL file from the command line
Scott Pippin wrote:
> I need to run a file called sms.sql from the command line. Is this
> possible and what is the syntax?
> Thanks in advance.
>
> -
> 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
/usr/bin/mysql -u user_name database < sms.sql
I added this to my .bashrc and saved my password in ~/.my.cnf
alias sql='/usr/bin/mysql -u xxx xxx '
then added
sq ()
{
sql < $1
}
which allows me to use
sq sms.sql
walt
-
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
data value changing for float column
I've run into a weird problem. If I try to insert this value, 78978978, into a FLOAT(9,2) column, the value changes. See below. System info: MySQL-shared-3.23.51-1 MySQL-client-3.23.51-1 MySQL-devel-3.23.51-1 MySQL-Max-3.23.51-1 MySQL-3.23.51-1 Red Hat Linux release 7.3 (Valhalla) #Wrong mysql> CREATE TABLE TIMMA ( AMOUNT FLOAT(10,2)); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO TIMMA VALUES(78978978); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM TIMMA; +-+ | AMOUNT | +-+ | 78978976.00 | +-+ 1 row in set (0.00 sec) #Try again with different value mysql> DROP TABLE TIMMA; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE TIMMA ( AMOUNT FLOAT(10,2)); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO TIMMA VALUES(98978978); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM TIMMA; +-+ | AMOUNT | +-+ | 98978976.00 | +-+ 1 row in set (0.00 sec) sql, query -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 "If it's not broketweak it" - 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 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
warnings in 4.x
Does anyone know if version 4.x has/will have warnings if data being inserted is too large for a column? sql,query -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 "If it's not broketweak it" - 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: replication problem
Froilan Mendoza wrote: > Hello, > > I have two machines that I am trying to use replication on. Here are their > configurations: > master (my.cnf) > log-bin > serveri-d = 1 > > slave: > server-id = 2 > master-host=master.domain.com > master-user=replicate > master-password=replicatedb > log-bin > > I followed the steps in the manual but found this error on my slave logs: > 020820 13:24:52 Slave I/O thread: connected to master > '[EMAIL PROTECTED]:3306', replication started in log 'FIRST' at > position 4 > 020820 13:24:52 Error updating slave list: > 020820 13:24:52 Slave I/O thread exiting, read up to log 'FIRST', position 4 > > What does this mean? > > Whenever I make changes to the master, the Position changes: > mysql> show master status; > ++--+--+--+ > | File | Position | Binlog_do_db | Binlog_ignore_db | > ++--+--+--+ > | master-bin.002 | 208 | | | > ++--+--+--+ > > but doesn't reflect on the slave: > mysql> show master status; > ++--+--+--+ > | File | Position | Binlog_do_db | Binlog_ignore_db | > ++--+--+--+ > | slave-bin.002 | 79 | | | > ++--+--+--+ > > Should the "file"s be the same for both master and slave? > > Here are other things that might help: > (slave) > mysql> show processlist; > | 2 | system user | none | NULL | Connect | 1945 | Slave: waiting > for binlog update | NULL | > > mysql> show slave status; > | master.domain.com | replicate | 3306| > 60| | 4 | slave-relay-bin.002 > | 4 | | No | > Yes | | | > 0 || 0| 0 | > 4 | > > Any tips/advice? > > TIA! > > Froilan > > - > 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 on the slave you should run show slave status also on the slave check /path/to/mysql/master.info this file gives you good info and I had to change it manually when I changed master server names in the my.cnf file also, add set-variable = slave_net_timeout=3600 set-variable = net_read_timeout=3600 in the my.cnf on the slave to keep it from fillng the log with junk - 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: NULL values in a JOIN
Quinten Steenhuis wrote:
> Sql, query
>
> > Hi,
> >
> > I have a field that I need to do a join on. Unfortunately, the column is
> > allowed to be null (and it should be). My plan is to return the literal
> > string 'None' if the column is null, and otherwise to return the joined
> > value. How can I do this, when the inner join that I want will exclude the
> > rows where the column in question is null?
> >
> > eg:
> >
> > SELECT
> > ... as 'Motion Ref.', title as
> > Title,
> > IF(IFNULL(o.name,1<0),CONCAT('None'),o.name) as Organization,
> > ... as Date FROM m,o
> > WHERE (o.ID = m.organization)
> >
> > Quinten
>
> -
> 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
Quinten,
]Look at "outer joins". It might help.
http://www.mysql.com/doc/en/JOIN.html
walt
-
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 Data Infile
Serge Paquin wrote: > This is the only option? That's not a very automated process and I must import this >data once a day. I also will not be able to get them to reformate their data since >I'm only one of many many people downloading everyday. Is their no way for load data >to do this automaticaly? > > Serge. > > On Tue, 20 Aug 2002 21:31:45 +0100 > "DL Neil" <[EMAIL PROTECTED]> wrote: > > > Hello Serge, > > > > > I am having troubles with Load Data Infile and cannot figure out the > > problem. I have a date field but the format is 'Jan 1 1986' rather than > > mysql's default. So when I import I only get '-00-00' rather than the > > date. How can I tell MySQL what the format is? > > > > > > I have read through the manual and couldn't find anything. Spent about an > > hour now looking and now hoping someone here has the answer. > > > > > > Alter the table by replacing the date field with a temporary replacement > > formatted as text (for example). > > Load the data. > > Alter the table to add the date field back into the table. > > Use SQL functions to format the date according to ISO. > > Alter the table to remove the temporary field. > > > > or > > > > Go back to the original source and have the date formatted correctly (ISO > > standard). > > > > Regards, > > =dn > > > > - > 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 you load the data from a perl script? - 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: replication problem
On Tuesday 20 August 2002 09:02 pm, Froilan Mendoza wrote: > Walt, > > At 01:09 PM 8/20/2002 -0400, walt wrote: > >on the slave you should run > >show slave status > > on slave: > mysql> show slave status; > +-+-+-+---+ >-+-+--+---+- >--+--+---+-+ >-+++--+- >+-+ > > | Master_Host | Master_User | Master_Port | Connect_retry | > > Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | > Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | > Slave_SQL_Running | Replicate_do_db | Replicate_ignore_db | Last_errno | > Last_error | Skip_counter | Exec_master_log_pos | Relay_log_space | > +-+-+-+---+ >-+-+--+---+- >--+--+---+-+ >-+++--+- >+-+ > > | master.idsk.com | replicate | 3306| > > 60| | 4 | slave-relay-bin.001 > > | 4 | | No | > > Yes | | | > 0 || 0| 0 | > 4 | > +-+-+-+---+ >-+-+--+---+- >--+--+---+-+ >-+++--+- >+-+ 1 row in set (0.00 sec) > > What in particular should I be looking at here? I can see > Slave_SQL_Running: Yes. > > When i insert info into one of the master's table, > > slave's show master status is: > ++--+--+--+ > > | File | Position | Binlog_do_db | Binlog_ignore_db | > > ++--+--+--+ > > | slave-bin.001 | 79 | | | > > ++--+--+--+ > > master's show master status is: > ++--+--+--+ > > | File | Position | Binlog_do_db | Binlog_ignore_db | > > ++--+--+--+ > > | master-bin.002 | 162 | | | > > ++--+--+--+ > > > I re installed everything and still got the same error: > 020820 18:05:20 Slave I/O thread: connected to master > '[EMAIL PROTECTED]:3306', replication started in log 'FIRST' at > position 4 > 020820 18:05:20 Error updating slave list: > 020820 18:05:20 Slave I/O thread exiting, read up to log 'FIRST', position > 4 > > >also on the slave check > >/path/to/mysql/master.info > >this file gives you good info and I had to change it manually when I > > changed master server names in the my.cnf file > > my slave's master.info looks like: > 4 > master.idsk.com > replicate > replicatedb > 3306 > 60 > 138206092 > > Anything wrong with it? > > >also, add > >set-variable = slave_net_timeout=3600 > >set-variable = net_read_timeout=3600 > > I have added this one, thanks. Hmmm... When you set up the master database, did you copy all the *.frm, *.MYD, *.MYI to the slave database? I also noticed in your email the my.cnf file on the master had "serveri-d" instead of "server-id" Try removing bin-log from the my.cnf on the slave. If I remember correctly, there is a way to set up the slave to keep "archives" of the updates. The master.info file on the slave should look something like this Ignor stuff in quotes, I added that. walt-bin.950 "log file on the master" 73 "position of the log file on the master" walt.nea-fast.com "master server name" repl "replication user name" passwd "replication password" 3306 "port" 60 6 60 Let me know if any of this helps! -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 "If it's not broketweak it" - 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 Table size
Scott Pippin wrote: > Which Operating Systems limit MySQL to a 2GB table size? > > - > 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 hello, Linux 2.2.x at one time, but that may have changed. If you want to use Linux, use the 2.4.x kernel. I would recommend RedHat 7.2 or 7.3 (Oracle has been certified on 7.2 which helps give that warm fuzzy feeling). RedHat's kernels are also built from Alan Cox's kernel tree which contains misc. performance patches. walt - 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: Inserting a text or Word file into mysql
Thomas Spahni wrote: > On Mon, 26 Aug 2002, Donald J Miller wrote: > > > Hello everyone, > > > > I am trying to create a mysql table to hold either the entire contents > > of text or WordPerfect files. I've read about BLOB or TEXT type columns > > so I've tried creating a table such as: > > > > CREATE TABLE filetest(fileid int(5) not null, file BLOB null); > > so far, so good ... it will not store more than 65535 bytes in file > > > I 've created the table and have used LOAD DATA in order to populate the > > database however I get numerous errors. > > WORD documents are binary files. Using LOAD DATA you have to escape them > properly. Have a look at the LOAD_FILE(file_name) function instead. > > > Can someone shed some light as to how to do this correctly? What > > would be the proper query to retrieve this file out of mysql? > > SELECT file FROM filetest WHERE fileid = 12345; > > Regards, > Thomas > > - > 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 Donald, I'd recommend just storing the filename (and possibly path) and using an application to fetch the file from the OS filesystem. There are several reasons. 1. Your database files become too large to effectivly manage (try backing up a 5 GB table every night). 2. Storing files on the OS filesystem allows you to backup only files that have changed. 3. If you store the files in MySQL and you later decide to switch DB software, you'll pull your hair out! 4. You can archive the files that don't change on CD. walt - 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 4.0 stable release
Dean Ellis wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On Tuesday 27 August 2002 11:39 am, Lenz Grimmer wrote: > > As soon as it actually *is* stable :) > > > > On a more serious note, we are currently working on preparing the first > > release of 4.0 (4.0.3) that will be declared "Beta" instead of "Alpha". > > The annoying thing about it, for me, is that MySQL 4.0 has been perfectly > stable and perfectly usable for us until 4.0.3, which so far has yet to > produce a usable client library. I keep pulling from CVS and recompiling, > hoping that one day soon I'll be able to move beyond 4.0.2. ;) > > Clients in 4.0.3 simply lock up when they try to access a database (whether a > 4.0.2 or 4.0.3 or 3.23 database), for me. Linux 2.4.19, gcc 3.1.1 and 3.2. > > But it looks like several things were changed for 4.0.3, so I'll wait it out, > I suppose. > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.0.7 (GNU/Linux) > > iD8DBQE9a8MwRU3T/K5ORe8RArMOAKCnjNf49O7snEPNvhNJhgrLPTPPsQCeIqKN > dDUPU2iwJ3bFFZPfOh6cF40= > =i+W8 > -END PGP SIGNATURE- > > - > 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 Dean, There has been some issues with gcc 3.x if I remember correctly. I know RedHat released another beta because of problems with gcc 3.x. Have you tried it on a box with gcc 2.9x ? walt - 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: urgent ...
"Sekhar.Thota" wrote: > Hi, > > I have did setup for MySQL - Innodb tables. But I am not getting support for > row level locks. Whenever I am issuing a update the innodb tables are > locking my tables. Please help me whether I need to set any variables in > sqld file to get the support for row level locks ??? > > Regards, > Sekhar > > - > 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 SHOW TABLE STATUS LIKE 'TABLE_NAME' should tell you if the table is innodb or not. If not you can. 1. ALTER TABLE tbl_name TYPE = INNODB; 2. recreate the table with type=INNODB 3. add default-table-type=innodb to the my.cnf file, restart mysql, and recreate the table hope this helps walt - 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-install on linux
Egor Egorov wrote: > Muzi, > Thursday, September 05, 2002, 1:15:18 PM, you wrote: > > >> I was running my mysql 3.23. on redhat 6.2 > >> I uninstall it using the rpm -e command > >> then I went into var/lib/mysql deleted the dbase and all mysql directoring. > >> > >> I then re-install mysql > >> > >> mysql version,client,devel,shared > >> > >> when I run: rpm -qa | grep MySql > >> > >> result : I only see client,devel and shared ...I cannot see version > >> thus I cannot run mysql > > How did you installed MySQL server? What exact 3.23 version do you > use? Was there any error during installation? > > -- > For technical support contracts, goto https://order.mysql.com/?ref=ensita > This email is sponsored by Ensita.net http://www.ensita.net/ >__ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Egor Egorov > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] > /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ><___/ www.mysql.com > > - > 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 try rpm -qa | grep -i mysql you may have to rebuild the rpm database as well hope this helps! walt - 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] HELP: Can MySQL run external programs... Sendmail for example
"Keith Burke [Experience IT]" wrote: > Greetings, > > I'm new to this list but not to MySQL. I've been using MySQL on > Linux with PHP for the past year or two. I've always managed to sort out > any problems by looking at the documentation on the website but my > current problem has me perplexed. > > I need to be able to send Emails to people from MySQL command > line. > > I'm importing a text file of users via a cronjob. MySQL process > the data and segragates it into different tables. My problem is that I > want MySQL to Email the plain text passwords to the users before it > MD5's them. I cannot find any references that MySQL can run external > programs [sendmail]. > > Any help or workarounds [no matter how crazy ;-)] would be appriciated, > although I'd rather not do the Emailing and MD5'ing at the data source. > > Oh, if this isn't the correct mailing list to post this, just let me > know. > > Regards > > ** > * K e i t h B u r k e* > ** > * Experience IT Ltd * Phone : +353 1 460 3880 * > * G6 * GSM : +353 86 600 4004 * > * Riverview Business Park * Email : [EMAIL PROTECTED] * > * New Nangor Road * WWW : http://www.experience.ie * > * Dublin 12 > * Ireland * R a n g e R * > ** > * Lieutenant, Your men are already dead* > ** > > - > 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 Keith, When you say "Emailing and MD5'ing at the data source.". Are you saying you dont want to use PHP to insert and email the passwords? walt - 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: mysterious disconnects during processing
Derrell Durrett wrote: > I'm running server version: 3.22.32, on SunOS 5.6 Generic_105181-17. > The code is perl 5.6.1, using DBI and DBD::mysql as the interface. We > have code that prepares a group of statement handles for use in doing > various queries and inserts in a loop that is executed anywhere from > 1-3 times per use of this program. Another program drives this one, > calling it about 150 times. > > Last week (after I filled the local hard disk on the server), we began > to suffer disconnects from the database in these queries and inserts. > There is no obvious pattern to the disconnections, and they appear at > arbitrary times during the connections, but at approximately the same > time for each set of data (some of the shorter datasets are inserted > completely). > > The admin for this db is certain the problem is in the code. However > I can't understand this since that code is unchanged. > > What should I look for, what should he look for, or what additional > information do I need to supply in order to pursue resolving this issue? > > Derrell > > -- > Derrell Durrett Xilinx, Inc. Productivity Engineering >Longmont, Colorado > 720.652.3843 > > - > 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 Derrell, There are a few things I'd check. 1. Is anything else touching this/these table/tables while the application is running. 2. In your perl code, are you running ->finish on each query? 3. Is/are the table/tables you're touching innodb or myisam (could be log issue); 4. What error message does perl give you from the db? 5. What is the load avg on the db and application server when query/update fails? 6. If the query or update fails, what happens if the perl app sleeps for a few seconds and trys again? hope this helps walt - 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: Moving Mysql
Soheil Shaghaghi wrote: > Hello everyone, > I have my MySQL database n the var partition of a FreeBSD server. > The problem is that the partition is almost full, and now I can't start the > SQL server! > > I am thinking of moving the MySQL data directory to another partition, but I > don't know how I should do this. > Can anyone please tell me how, and also do I have to change any settings > after moving the data directory? > > Thanks so much. > > - > 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 Soheil, If you have a partition that you can dedicate to mysql, just rename the current mysql directory, mount the dedicated partion as /var/lib/mysql/ and copy the files from the mysql directory you renamed . Of course, be sure to stop mysql and check permissions of the new location. walt - 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: Problems with a Quote in Select
Gerald Clark wrote:
> in perl you would do
> my $qselcat=$dbh->quote($selcat);
> $whichpart = "AND key1=$qselcat";
>
> René Fournier wrote:
>
> > Hello everyone,
> >
> > I'm trying to Select on a field that contains a doublequote, and can't
> > figure out how to pass it throught the Select statement (couldn't find
> > anything in the docs either...)
> >
> > -=-=-=-=-
> >
> > This doesn't work...
> >
> > $whichpart = "AND key1='$selcat'";
> > $result = mysql_query("SELECT * FROM parts WHERE status='online'
> > $whichpart",$db);
> >
> > ...when $selcat contains a double quote. For example, if I echo
> > $selcat, I get:
> >
> > AND key1='36\" Core Shafts'
> >
> > ==
> > I can see that mysql is choking on that double quote--do I need to
> > convert it to something else? Thanks.
> >
> > ...Rene
> >
> > ---
> > René Fournier,
> > [EMAIL PROTECTED]
> >
> > Toll-free +1.888.886.2754
> > Tel +1.403.291.3601
> > Fax +1.403.250.5228
> > www.smartslitters.com
> >
> > SmartSlitters International
> > #33, 1339 - 40th Ave NE
> > Calgary AB T2E 8N6
> > Canada
> >
> >
> > -
> > 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
FYI -
Be careful using dbh->quote with inserts and updates. If you feed dbh->quote a
variable that has
no value, it will return '' (tick tick). If the column is a date the value will
be set to -00-00 and
if the column is numeric it will be set to 0 instead of NULL . You can either
check the length of the variable to see if you
can quote it, or use a sub_routine like below and call it instead of dbh->quote.
walt
#
# # nea_quote
# replacement for direct dbh->quote which doesn't work well with
# mysql which returns '' instead of NULL.
sub nea_quote
{
my ($input) = @_;
if (length($input) == 0)
{
$return_string = "NULL";
return $return_string;
}
else
{
$return_string = $dbh->quote($input);
return $return_string;
}
}
-
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
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Cron and mysqlimport on OS X 10.2
"Warren, Keith" wrote: > I've created a mysql database solutions that works perfectly in Mac OS X 10.1.4. >However, I've purchased a new G4 running 10.1.2 (Jaguar) and I'm having one problem. > > I need to update the database every 5 minutes with sports scores that are exported >from another system. I'm using the mysqlimport command, and it works perfectly inside >a shell script when run from the command line. > > When I try to execute that same shell script with cron, it will not import the file. >The exact script works perfectly in Mac OS 10.1.4. > > Also, if I run the mysqlimport command as part of the crontab file (not calling the >shell script) it works, but that doesn't help me because I need to do other things to >the file before imports the file, and I'd rather not have multiple crons running. > > I suppose it could be a permissions problem somewhere, but I've tried running it as >root as well as a regular user with admin rights, and it still doesn't work. > > Any ideas? > > Keith Warren > Systems Editor > The Clarion-Ledger > 601-961-7058 > > - > 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 Keith, It might be a path issue. What error message is the cron generating? walt - 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 - Which DB gives best sql performance?
Brian Safford wrote: > Which DB type will provide the best performance? We're currently using > MyISAM, but I'm curious if switching to InnoDB will provide any performance >improvements. > > Regards, > Brian Safford > EDS Messaging > > Note: The following was added to get this posting through the spam > filters: 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 Hello Brian, I would only use innodb if you "must" have transaction support or row level locking. It seems to me to be just as fast, or maybe a little slower due to log file writes, as the myisam tables. There is one important thing I do not like about Innodb tables. .If you add or drop and index, it rebuilds the entire table (possibly tablespace, I'm not sure). During testing, I've found that its faster to drop a table, recreate it with the new index, and reimport the data. From what I understand, it's because the indexes are kept along with the data unlike myisam where the indexes are seperate files. Hope this helps! walt - 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: OT: Reply-Adress in this list
Franz Alt wrote: > I use some mail-lists for mysql, php and security with Outlook Express. > > When I "reply", some lists set their list-adress automatically, some the > adress of the person originally posting. The second version may not so good > IMHO, because I think so many replies are not going to the lists. > mysql++ and mysql do not automatically reply to the list > mysql-de does. > > I think the a big Problem with the first solution are some autoresponders > which may "spamming" mainling lists. > > I'm not a freak in mailservers, but maybe others know if there's a better > configuration. > > Maybe I can configure my Outlook Express better? > > -- > [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 what happens if you click "reply to all" - 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: raid vs splitting the database
> 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 Brent, You mentioned placing the busy tables on seperate disks. I didn't think in mysql that you could specify where the datafiles foreach table"live". I know you could symlink (linux/unix) the files, but I remember seeing something about issues recovering a database with symlinked files. I'd really like to do this with mysql since I/O is the biggest bottleneck we have. Thanks! walt - 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
recover innodb using slave datafiles
I have a few questions regarding recovering an innodb tablespace/datafile. Here's the situation. I lose a drive on my master database that contained an innodb datafile. I then shut the database down and umount that drive. What would I need to copy from the slave database in order to bring the master database backup? I know I'd need to copy all innodb datafiles from the slave to the master but what else would I have to do? Will the database complain about the new datafiles if things were not shutdown correctly? 99% of the transactions on the innodb tables are done by software which only inserts/updates one row at a time and they are immediate commits, so I'm not worried about losing transactions. I know I could run hot backups on the master for innodb tablespace, but I'd need to store them across the network so I'm guessing it would be quicker to just copy the slave datafiles and start the master database than it would be to copy the backups and then run the logs against them. mysql query Thanks! -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 "If it's not broketweak it" - 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: InnoDb data file path
Scott Pippin wrote: > I am trying to set up two data files in case the first one fills up. I > tried to use the following in my.cnf but it says there is an error. If > I take out the reference to the second data file everything works > > AIX 4.3.3 > MySQL 4.0.4 > > >innodb_data_file_path=libdata1:100M:autoextend:max:2000M;libdata2:100M:autoextend:max:2000M > > Any help would be greatly appreciated. > > (query, 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 Scott, >From the innodb manual. It looks like you can only autoextend the last datafile >listed. Starting from versions 3.23.50 and 4.0.2 InnoDB allows the last data file on the innodb_data_file_path line to be specified as auto-extending. The syntax for innodb_data_file_path is then the following: pathtodatafile:sizespecification;pathtodatafile:sizespec;... ...;pathtodatafile:sizespec[:autoextend[:max:sizespecification]] If you specify the last data file with the autoextend option, InnoDB will extend the last data file if it runs out of free space in the tablespace. The increment is 8 MB at a time. An example: innodb_data_home_dir = innodb_data_file_path = /ibdata/ibdata1:100M:autoextend instructs InnoDB to create just a single data file whose initial size is 100 MB and which is extended in 8 MB blocks when space runs out. If the disk becomes full you may want to add another data file to another disk, for example. Then you have to look the size of ibdata1, round the size downward to the closest multiple of 1024 * 1024 bytes (= 1 MB), and specify the rounded size of ibdata1 explicitly in innodb_data_file_path. After that you can add another data file: - 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
innodb not using correct index
Is there a way to find out what index an sql query is using? I know you can use explain, but those are just "possible" indexes that the query might use. I'm trying to find out exacly what execution path it is taking. Something similar to Oracle's "set autotrace on;" MySql version - MySQL-Max-3.23.52-1 OS version - RedHat 2.4.18-SGI_XFS_1.1enterprise Here is the problem I'm having. I've got a simple query which is taking about 1 1/2 minutes to run. select several_fields from xray where create_date > '2002-10-03' and cust_id = 'TEST1'; I have the following indexes. X_CREATE_DATE(create_date, cust_id). X_CUST_ID(cust_id, download_file). If I run select several_fields from xray where create_date > '2002-10-03'; It takes less the 10 seconds. If I run select several_fields from xray where cust_id = 'TEST1'; It also takes less than 10 seconds. If I run the query on my Oracle test server (sparc 20, dual 60mhz, 160mb ram, old ass raid with 5400rpm scsi drives), it takes about 10 seconds to return the 374 rows. The mysql server is a PIII 600mhz, 500mb ram, with 2 10K rpm lvd scsi drives attached to an Ultra 160 scsi card and is much faster when doing single where clauses than the oracle test server. To satisfy my curiosty, I wrote a perl script that selected all the cust_id where create_date > '2002-10-03' and then checked the cust_id against "TEST1' and it only took about 15 seconds. I origionally only had create_date in the X_CREATE_DATE index, but I figured adding cust_id along with it would make it faster but it didn't. I'm open to any suggestions or comments. This table 46 columns * 2.2million rows and has 13 indexes (full table scans bring the db to it's knees). It's still a test database so I can try "most" anything to speed this up. Thanks! sql, query -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 "If it's not broketweak it" - 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
