Re: Have You Seen My CV?
No I have not seen your CV! But I have seen these answers to stopping spam dead in it's tracks. http://www.tmda.net http://spam-stop.com Regards Keith Roberts PS - apologies to anyone on the list that has been getting messages to confirm your emails to [EMAIL PROTECTED] - that was me checking out TMD's challenge/Response on my laptop. I need to set up a filter to allow all messages from the lists I'm on to be accepted without a challenge/response message being sent out. Kind Regards Keith Roberts In theory, theory and practice are the same; in practice they are not. On Tue, 2 May 2006, Rob Munsch wrote: To: forum mysql mysql@lists.mysql.com From: Rob Munsch [EMAIL PROTECTED] Subject: Re: Have You Seen My CV? Anago Chima wrote: Tired of spam? Yahoo! Mail has the best spam protection around Oh the irony. -- Rob Munsch Solutions For Progress IT -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to move the MySQL data directory?
On 03.05.2006 01:21 (+0100), paul rivers wrote:
Specify the data dir in the local my.cnf and be sure your instance uses it
by starting it with the --defaults-file parameter set to that instance's
local copy.
Okay, since hacking seems to be required anyway, I hacked it the
straight-forward and least-change way. I already had datadir=... changed
in the init script to the correct location. Now I also insert some
variables corrections in bin/mysqld_safe:
# here are the lines where ledir is set totally wrong...
MY_BASEDIR_VERSION=`pwd`
ledir=${MY_BASEDIR_VERSION}/bin
DATADIR=`pwd | sed -r s;/usr/local/;/var/;`/data
defaults=--defaults-file=${DATADIR}/my.cnf
# user=... and so on
This does the job pretty well for MySQL 4.0. Need to do it with every
upgrade, but I think I can automate it.
MySQL 5.0 required a less invasive hack though. I saw that setting
datadir= in the proposed init script is for nothing at the very
beginning since it's overwritten again right below. So moving that line
further down helped. Then the mysqld_safe call in the 'start' section
required an additional parameter --defaults-file=$datadir/my.cnf to make
it read my socket name, IP port etc.
Now both servers are up and running fine again, side by side, with the
*entire* data directory moved somewhere else, saving me from handling
that with every upgrade. Thanks for your help, I thought it could be
done an easy way but it seems nobody has thought about doing that
before. At least I don't have the impression, from reading the scripts.
--
Yves Goergen LonelyPixel [EMAIL PROTECTED]
http://beta.unclassified.de – My web laboratory.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to move the MySQL data directory?
Hi Yves,
You could also have changed the directory in the global /etc/my.cnf file
by setting
datadir=/path/to/mysql/data
This is pretty simple and works a lot easier than hacking the init
scripts.
Regards
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +618 8408 4273
_/ _/ _/_/_/ Mobile: 0417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: Yves Goergen [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 3 May 2006 8:01 PM
To: paul rivers
Cc: mysql@lists.mysql.com
Subject: Re: How to move the MySQL data directory?
On 03.05.2006 01:21 (+0100), paul rivers wrote:
Specify the data dir in the local my.cnf and be sure your instance
uses it
by starting it with the --defaults-file parameter set to that
instance's
local copy.
Okay, since hacking seems to be required anyway, I hacked it the
straight-forward and least-change way. I already had datadir=... changed
in the init script to the correct location. Now I also insert some
variables corrections in bin/mysqld_safe:
# here are the lines where ledir is set totally wrong...
MY_BASEDIR_VERSION=`pwd`
ledir=${MY_BASEDIR_VERSION}/bin
DATADIR=`pwd | sed -r s;/usr/local/;/var/;`/data
defaults=--defaults-file=${DATADIR}/my.cnf
# user=... and so on
This does the job pretty well for MySQL 4.0. Need to do it with every
upgrade, but I think I can automate it.
MySQL 5.0 required a less invasive hack though. I saw that setting
datadir= in the proposed init script is for nothing at the very
beginning since it's overwritten again right below. So moving that line
further down helped. Then the mysqld_safe call in the 'start' section
required an additional parameter --defaults-file=$datadir/my.cnf to make
it read my socket name, IP port etc.
Now both servers are up and running fine again, side by side, with the
*entire* data directory moved somewhere else, saving me from handling
that with every upgrade. Thanks for your help, I thought it could be
done an easy way but it seems nobody has thought about doing that
before. At least I don't have the impression, from reading the scripts.
--
Yves Goergen LonelyPixel [EMAIL PROTECTED]
http://beta.unclassified.de - My web laboratory.
--
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: How to move the MySQL data directory?
Apologies, I didn't read your initial posting properly. Perhaps a glance
at this http://dev.mysql.com/doc/refman/5.0/en/mysqld-multi.html would
provide you the facilities that you require for using multiple servers.
This work well and enables you to manage the multiple global
configuration files required.
Regards
Hi Yves,
You could also have changed the directory in the global /etc/my.cnf file
by setting
datadir=/path/to/mysql/data
This is pretty simple and works a lot easier than hacking the init
scripts.
Regards
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +618 8408 4273
_/ _/ _/_/_/ Mobile: 0417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: Yves Goergen [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 3 May 2006 8:01 PM
To: paul rivers
Cc: mysql@lists.mysql.com
Subject: Re: How to move the MySQL data directory?
On 03.05.2006 01:21 (+0100), paul rivers wrote:
Specify the data dir in the local my.cnf and be sure your instance
uses it
by starting it with the --defaults-file parameter set to that
instance's
local copy.
Okay, since hacking seems to be required anyway, I hacked it the
straight-forward and least-change way. I already had datadir=... changed
in the init script to the correct location. Now I also insert some
variables corrections in bin/mysqld_safe:
# here are the lines where ledir is set totally wrong...
MY_BASEDIR_VERSION=`pwd`
ledir=${MY_BASEDIR_VERSION}/bin
DATADIR=`pwd | sed -r s;/usr/local/;/var/;`/data
defaults=--defaults-file=${DATADIR}/my.cnf
# user=... and so on
This does the job pretty well for MySQL 4.0. Need to do it with every
upgrade, but I think I can automate it.
MySQL 5.0 required a less invasive hack though. I saw that setting
datadir= in the proposed init script is for nothing at the very
beginning since it's overwritten again right below. So moving that line
further down helped. Then the mysqld_safe call in the 'start' section
required an additional parameter --defaults-file=$datadir/my.cnf to make
it read my socket name, IP port etc.
Now both servers are up and running fine again, side by side, with the
*entire* data directory moved somewhere else, saving me from handling
that with every upgrade. Thanks for your help, I thought it could be
done an easy way but it seems nobody has thought about doing that
before. At least I don't have the impression, from reading the scripts.
--
Yves Goergen LonelyPixel [EMAIL PROTECTED]
http://beta.unclassified.de - My web laboratory.
--
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: How to move the MySQL data directory?
On 03.05.2006 12:34 (+0100), Logan, David (SST - Adelaide) wrote: You could also have changed the directory in the global /etc/my.cnf file by setting datadir=/path/to/mysql/data This is pretty simple and works a lot easier than hacking the init scripts. As I said, there are two MySQL servers and there is no such global config file which all scripts seem to assume. There is one for each server and they are located in the datadir to make it easy (following the default setup). -- Yves Goergen LonelyPixel [EMAIL PROTECTED] http://beta.unclassified.de – My web laboratory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to move the MySQL data directory?
On 5/2/06, Yves Goergen [EMAIL PROTECTED] wrote:
But upgrading MySQL like installing it
after MySQL's guide brings a problem: I'd need to move the data
directory to the new programme directory every time.
That is what I do. I find it easier than moving the data directory to
a non-standard location.
I wrapped it all in a shellscript to be run by root.
Assuming mysql is installed in /usr/local like this:
lrwxr-xr-x 1 root wheel 37 May 2 14:34 mysql -
mysql-standard-5.0.20a-osx10.4-powerpc
drwxr-xr-x 19 root mysql 646 May 2 14:34 mysql-standard-5.0.20a-osx10.4-powerpc
And I have a tar.gz 'mysql-standard-5.0.21-osx10.4-powerpc.tar.gz',
and the script below is called 'update-mysql.sh', this:
# update-mysql.sh mysql-standard-5.0.21-osx10.4-powerpc.tar.gz
Updates mysql to version 5.0.21. All I have to do manually is remove
the directory with the old version. I'm sure I could automate that
also but I thought it was good enough like this.
#!/bin/sh
tarfile=$1
usrlocal='/usr/local'
olddir='mysql'
newdir=${tarfile%.tar.gz}
password='secret'
mysqladmin -u root --password=$password shutdown
sleep 5
mv $tarfile $usrlocal
cd $usrlocal
tar xvzf $tarfile
cd $newdir
rm -r data
chown -R root:mysql .
cd ../$olddir
mv data ../$newdir/
cd ..
rm $olddir
ln -s $newdir $olddir
rm $tarfile
mysqld_safe
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to find size of my database
On Wed, 03 May 2006 Rhino wrote : Daniel de Veiga has already answered you on how to determine the size of your database by using the file system and simply looking at the size of the physical files in your database. Another possibility is that you could use the SHOW TABLE STATUS command in MySQL. If you go to your MySQL prompt and select a database, then use the SHOW TABLE STATUS command, like this: set tmp; show table status like '%'; you'll find a column called Data_length which tells you the length of the data file for each table. If you simply add the size of each table in the database together, you should have the size of the whole database. Please note that I'm not sure how accurate my suggestion is; you might find that Daniel's approach gives you a better answer. I'm not sure if the Data_length column considers all the overhead that you might have with a table, such as index sizes, pointer sizes, unusable space, etc. Perhaps someone else can jump in with a better approach; I'm inclined to think it can't be quite as easy as I suggested. I'll jump in for you.. Indeed, you are right that Data_length that does not cover space allocated but unused (i.e space freed up by DELETE or UPDATE statements that is not released back to the filesystem, before an OPTIMIZE TABLE for example). There are other columns within the output however - Data_free and Index_length. Therefore to get the total allocated space to a database: SUM(data_length) + SUM(index_length) Total of actual data: (SUM(data_length) - SUM(data_free)) + SUM(index_length) Allocated but unused: SUM(data_free) Also, even if the Data_length column gives an accurate answer for the size of a table, it is rather tedious to have to execute the SHOW TABLE STATUS command and then manually sum up the various sizes. I don't think you can simply execute an SQL query that does all the work for you, which is very unfortunate. Unfortunately within 4.0 there is no way to do this with a SQL query. You can do this on 5.0 however. For example: SELECT s.schema_name, CONCAT(IFNULL(ROUND((SUM(t.data_length)+SUM(t.index_length)) /1024/1024,2),0.00),Mb) total_size, CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/1024/1024,2),0.00),Mb) data_used, CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00),Mb) data_free, IFNULL(ROUNDSUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free)) /((SUM(t.data_length)+SUM(t.index_length)))*100),2),0) pct_used, COUNT(table_name) total_tables FROM INFORMATION_SCHEMA.SCHEMATA s LEFT JOIN INFORMATION_SCHEMA.TABLES t ON s.schema_name = t.table_schema WHERE s.schema_name = sakila GROUP BY s.schema_name ORDER BY pct_used DESC\G *** 1. row *** schema_name: sakila total_size: 6.62Mb data_used: 6.62Mb data_free: 0.01Mb pct_used: 99.91 total_tables: 22 1 row in set (0.08 sec) It is entirely possible that there is a MySQL command that gives you the actual size of each database directly, although I didn't find it when I searched the manual. Again, perhaps someone with more administrative experience with MySQL can suggest a better approach. If not, perhaps we need to make a feature request of the MySQL people :-) This would appear to be a very useful command to create if it doesn't already exist! I have a little administrative experience ;) I'm going to confuse the matter now, as the above reports freespace correctly for storage engines such as MyISAM, however, it does not report the freespace properly within Data_free column for InnoDB tables - the freespace is reported at the *tablespace* level, within the Comment column of SHOW TABLE STATUS and INFORMATION_SCHEMA.TABLES. There are a couple of other relatively undocumented features within InnoDB that allow you to dump this kind of information - called the InnoDB Monitors: http://dev.mysql.com/doc/refman/5.0/en/innodb-monitor.html If you create the following table for a short period of time, the internal contents of the InnoDB data dictionary will be dumped out to the error log: create table innodb_table_monitor (a int) engine = innodb; Then drop the table after a minute or so (otherwise it will dump continuously). This will dump a wealth of information on all of the tables within InnoDB (for all databases), here's an extract on one of our sakila tables: TABLE: name sakila/rental, id 0 26, columns 11, indexes 5, appr.rows 16305 COLUMNS: rental_id: DATA_INT len 4 prec 0; rental_date: DATA_INT len 8 prec 0; inventory_id: DATA_INT len 3 prec 0; customer_id: DATA_INT len 2 prec 0; return_date: DATA_INT len 8 prec 0; staff_id: DATA_INT len 1 prec 0; last_update: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS prtype 8 len 6 prec 0; DB_TRX_ID: DATA_SYS prtype 8 len 6 prec 0; DB_ROLL_PTR: DATA_SYS prtype 8 len 7 prec 0; INDEX: name PRIMARY, id 0 44, fields 1/9, type 3 root page 207, appr.key vals 16305, leaf pages 53,
UPDATE question
If I have 4 Fields (FIELD1, FIELD2, FIELD3 FIELD4) I can do this easily; UPDATE TABLE_NAME SET FIELD4 = FIELD1; But -- how do I do it so that FIELD4 = FIELD1 FIELD2 ??? I can't seem to find any examples online. Maybe it's just too early in the morning - I'm drawing a blank! ;) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table so slow to read
Hi frieds. I have the next problem. I have a dedicated server with tables and I have a program that read some tables of this server. But when the program consults over 1 table (the query's result are a few records) it is very slow. But if I execute the same program in other server with a database copy (exactly the same tables) the program give me the result fast. The two servers are exactly the same (mysql 5.0.15), the application was developed in Delphi with Zeos componentes What could it be the problem? Over the server there are 75 connections, but we have max_connections=200; Thanks. -- Saludos cordiales. Ing. Gabriel Mahiques Dto. Control de Gestión ELECTROINGENIERA S.A. Telefono: 474 1414 Uspallata 1461. Barrio San Martín. CP: X5008HSH Córdoba. República Argentina. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: UPDATE question
Shawn, Perhaps : UPDATE TABLE_NAME SET FIELD4 = concat(FIELD1,FIELD2); -Original Message- From: Cummings, Shawn (GNAPs) [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 03, 2006 9:33 AM To: Mysql General (E-mail) Subject: UPDATE question If I have 4 Fields (FIELD1, FIELD2, FIELD3 FIELD4) I can do this easily; UPDATE TABLE_NAME SET FIELD4 = FIELD1; But -- how do I do it so that FIELD4 = FIELD1 FIELD2 ??? I can't seem to find any examples online. Maybe it's just too early in the morning - I'm drawing a blank! ;) -- 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: UPDATE question
Cummings, Shawn (GNAPs) schrieb: If I have 4 Fields (FIELD1, FIELD2, FIELD3 FIELD4) I can do this easily; UPDATE TABLE_NAME SET FIELD4 = FIELD1; But -- how do I do it so that FIELD4 = FIELD1 FIELD2 ??? I can't seem to find any examples online. Maybe it's just too early in the morning - I'm drawing a blank! ;) FIELD4 = FIELD1 FIELD 2? Field 4 should be field 1 AND field 2? Can't decide which one field4 should get? :P -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UPDATE question
On 5/3/06, Barry [EMAIL PROTECTED] wrote: Cummings, Shawn (GNAPs) schrieb: If I have 4 Fields (FIELD1, FIELD2, FIELD3 FIELD4) I can do this easily; UPDATE TABLE_NAME SET FIELD4 = FIELD1; But -- how do I do it so that FIELD4 = FIELD1 FIELD2 ??? I can't seem to find any examples online. Maybe it's just too early in the morning - I'm drawing a blank! ;) FIELD4 = FIELD1 FIELD 2? Field 4 should be field 1 AND field 2? Can't decide which one field4 should get? :P Well, every child learns that 1 and 1 is 2 ;-P -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to find size of my database
Thank you VERY much, Mark! Your reply is EXCELLENT and gives us all a lot of very useful information. This is the kind of information that should be in the MySQL manual. Paul DuBois, if you're reading this, please consider adding all of Mark's information to the manual! I think this reply also points to a definite need within the MySQL community, namely monitoring tools. After all, any decent administrator is going to want to know the size of his databases at some point. I don't follow the development of tools for MySQL but if there are no tools to monitor database size, I would imagine there is a definite market for such tools. After all, why should each of us independently re-invent the wheel? This seems like an opportunity for an entrepreneurial type to make some money serving a market. Or for people who have already developed monitoring tools to contribute them freely to the MySQL community. Thanks again, Mark! I know I will revisit your reply when I get around to doing proper monitoring of my MySQL databases when they finally go into production. -- Rhino - Original Message - From: Mark Leith [EMAIL PROTECTED] To: Shivaji S [EMAIL PROTECTED] Cc: Rhino [EMAIL PROTECTED]; [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Wednesday, May 03, 2006 9:20 AM Subject: Re: How to find size of my database On Wed, 03 May 2006 Rhino wrote : Daniel de Veiga has already answered you on how to determine the size of your database by using the file system and simply looking at the size of the physical files in your database. Another possibility is that you could use the SHOW TABLE STATUS command in MySQL. If you go to your MySQL prompt and select a database, then use the SHOW TABLE STATUS command, like this: set tmp; show table status like '%'; you'll find a column called Data_length which tells you the length of the data file for each table. If you simply add the size of each table in the database together, you should have the size of the whole database. Please note that I'm not sure how accurate my suggestion is; you might find that Daniel's approach gives you a better answer. I'm not sure if the Data_length column considers all the overhead that you might have with a table, such as index sizes, pointer sizes, unusable space, etc. Perhaps someone else can jump in with a better approach; I'm inclined to think it can't be quite as easy as I suggested. I'll jump in for you.. Indeed, you are right that Data_length that does not cover space allocated but unused (i.e space freed up by DELETE or UPDATE statements that is not released back to the filesystem, before an OPTIMIZE TABLE for example). There are other columns within the output however - Data_free and Index_length. Therefore to get the total allocated space to a database: SUM(data_length) + SUM(index_length) Total of actual data: (SUM(data_length) - SUM(data_free)) + SUM(index_length) Allocated but unused: SUM(data_free) Also, even if the Data_length column gives an accurate answer for the size of a table, it is rather tedious to have to execute the SHOW TABLE STATUS command and then manually sum up the various sizes. I don't think you can simply execute an SQL query that does all the work for you, which is very unfortunate. Unfortunately within 4.0 there is no way to do this with a SQL query. You can do this on 5.0 however. For example: SELECT s.schema_name, CONCAT(IFNULL(ROUND((SUM(t.data_length)+SUM(t.index_length)) /1024/1024,2),0.00),Mb) total_size, CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/1024/1024,2),0.00),Mb) data_used, CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00),Mb) data_free, IFNULL(ROUNDSUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free)) /((SUM(t.data_length)+SUM(t.index_length)))*100),2),0) pct_used, COUNT(table_name) total_tables FROM INFORMATION_SCHEMA.SCHEMATA s LEFT JOIN INFORMATION_SCHEMA.TABLES t ON s.schema_name = t.table_schema WHERE s.schema_name = sakila GROUP BY s.schema_name ORDER BY pct_used DESC\G *** 1. row *** schema_name: sakila total_size: 6.62Mb data_used: 6.62Mb data_free: 0.01Mb pct_used: 99.91 total_tables: 22 1 row in set (0.08 sec) It is entirely possible that there is a MySQL command that gives you the actual size of each database directly, although I didn't find it when I searched the manual. Again, perhaps someone with more administrative experience with MySQL can suggest a better approach. If not, perhaps we need to make a feature request of the MySQL people :-) This would appear to be a very useful command to create if it doesn't already exist! I have a little administrative experience ;) I'm going to confuse the matter now, as the above reports freespace correctly for storage engines such as MyISAM, however, it does not report the freespace properly within Data_free column for InnoDB tables - the freespace is
Re: UPDATE question
Cummings, Shawn (GNAPs) wrote: If I have 4 Fields (FIELD1, FIELD2, FIELD3 FIELD4) I can do this easily; UPDATE TABLE_NAME SET FIELD4 = FIELD1; But -- how do I do it so that FIELD4 = FIELD1 FIELD2 ??? I can't seem to find any examples online. Maybe it's just too early in the morning - I'm drawing a blank! ;) This depends on the data types, if you want to set FIELD4 to be FIELD1 + FIELD2 with integers: UPDATE table_name SET FIELD4 = (FIELD1 + FIELD2); If they are strings: UPDATE table_name SET FIELD4 = CONCAT(FIELD1,' ',FIELD2); If you are swapping values: UPDATE table_name SET FIELD4 = (@tmp:=FIELD4), FIELD4 = FIELD1, FIELD1 = @tmp; Best regards Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Missing information Search
Hi I have two tables that are structured like so: Table 1: ID int K_Code int Table 2 K_Code int K_Desc char Table 2 has been corrupted and may be missing some records. What I need to be able to do is find any values of K_Code in table 1 that don't appear in table 2. Thanks Robert Gehrig Webmaster at www.gdbarri.com e-mail: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table so slow to read
My name is Gabriel, Saludos Cordiales is the same than Best Regard in spanish. The server explanin is the same. The table structure is the same, the application is the same (redirect the data source only), the quantity of record is the same. All is the same, I copy the database from one server to other Prasad escribió: Hi Saludos cordiales., For both the server the explain plan is same? -Prasad Sify. - Original Message - From: Gabriel Mahiques [EMAIL PROTECTED] To: MySQL Lista mysql@lists.mysql.com Sent: Wednesday, May 03, 2006 7:03 PM Subject: Table so slow to read Hi frieds. I have the next problem. I have a dedicated server with tables and I have a program that read some tables of this server. But when the program consults over 1 table (the query's result are a few records) it is very slow. But if I execute the same program in other server with a database copy (exactly the same tables) the program give me the result fast. The two servers are exactly the same (mysql 5.0.15), the application was developed in Delphi with Zeos componentes What could it be the problem? Over the server there are 75 connections, but we have max_connections=200; Thanks. -- Saludos cordiales. Ing. Gabriel Mahiques Dto. Control de Gestión ELECTROINGENIERA S.A. Telefono: 474 1414 Uspallata 1461. Barrio San Martín. CP: X5008HSH Córdoba. República Argentina. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] Watch India vs. England LIVE, Hot videos and more only on Sify Max! Click Here. www.sifymax.com Get to see what's happening in your favourite City on Bangalore Live! www.bangalorelive.in -- Saludos cordiales. Ing. Gabriel Mahiques Dto. Control de Gestión ELECTROINGENIERA S.A. Telefono: 474 1414 Uspallata 1461. Barrio San Martín. CP: X5008HSH Córdoba. República Argentina. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with this query. How to do a group by on a datetime just the month/day/year
Hello, Not sure if i can do this. I have a table with a datetime column I would like to do group by a day of the month. if i do something like select count(*) from MTracking where mallarea=1001 group by timeofclick every one is listed because time. So is this possible? Thanks Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tuning a Server with 10,000 databases
This problem is indeed not related to OS / Hardware Problems. Take a look at this thread: http://lists.mysql.com/mysql/197542 Read the part about show databases as root vs standard user + observed file system activity. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with this query. How to do a group by on a datetime just the month/day/year
try: group by substring(timeofclick,1,10) -Original Message- From: Randy Paries [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 03, 2006 11:25 AM To: mysql@lists.mysql.com Subject: Help with this query. How to do a group by on a datetime just the month/day/year Hello, Not sure if i can do this. I have a table with a datetime column I would like to do group by a day of the month. if i do something like select count(*) from MTracking where mallarea=1001 group by timeofclick every one is listed because time. So is this possible? Thanks Randy -- 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: Help with this query. How to do a group by on a datetime just the month/day/year
select count(*), substring(timeofclick,1,7) from MTracking where mallarea=1001 group by 2; On 5/3/06, Randy Paries [EMAIL PROTECTED] wrote: Hello, Not sure if i can do this. I have a table with a datetime column I would like to do group by a day of the month. if i do something like select count(*) from MTracking where mallarea=1001 group by timeofclick every one is listed because time. So is this possible? Thanks Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fixing Databases When Replication Is Enabled?
So, just to be clear, when I run: mysqlcheck -r -f database_name Any fixes are recorded to the binlog and replicated to the slave? I want to be sure about this because someone in this forum said the opposite a couple of weeks ago. Thanks! --Eric -Original Message- From: Kishore Jalleda [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 02, 2006 8:23 AM To: Marciano Cc: Robinson, Eric; mysql@lists.mysql.com Subject: Re: Fixing Databases When Replication Is Enabled? My Sincere apologies, I intented to mean mysqlcheck but somehow came out as myisamchk :) Kishore Jalleda On 5/2/06, Marciano [EMAIL PROTECTED] wrote: How myisamchk can write to binlog if the server need to be down? - Mensagem Original De: Kishore Jalleda [EMAIL PROTECTED] Para: Robinson, Eric [EMAIL PROTECTED] Cópia: mysql@lists.mysql.com Assunto: Re: Fixing Databases When Replication Is Enabled? Data: 01/05/06 22:25 Yes On 5/1/06, Robinson, Eric [EMAIL PROTECTED] wrote: I ran mysqlcheck, not myisamchk or REPAIR. Is what you said still true? -Original Message- From: Kishore Jalleda [mailto:[EMAIL PROTECTED] Sent: Monday, May 01, 2006 1:56 PM To: Robinson, Eric Cc: mysql@lists.mysql.com Subject: Re: Fixing Databases When Replication Is Enabled? On 5/1/06, Robinson, Eric [EMAIL PROTECTED] wrote: I ran mysqlcheck against a replication master database and it reported a problem with a table, which it corrected. Is the slave now out of sync? If so, how do I correct the problem without copying the whole database over a slow WAN link? --Eric By Default when you run REPAIR or myisamchk --recover , MySQL writes the changes made to the Binlog , and those will be obviously replicated to the slave, so if everything went fine with your myisamck on the master, then your slave is in sync and you don't have to do anyhthing special on the slave... Kishore Jalleda http://kjalleda.googlepages.com/projects -- 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] Yes On 5/1/06, Robinson, Eric [EMAIL PROTECTED] wrote: I ran mysqlcheck, not myisamchk or REPAIR. Is what you said still true? -Original Message- From: Kishore Jalleda [mailto:[EMAIL PROTECTED] Sent: Monday, May 01, 2006 1:56 PM To: Robinson, Eric Cc: mysql@lists.mysql.com Subject: Re: Fixing Databases When Replication Is Enabled? On 5/1/06, Robinson, Eric [EMAIL PROTECTED] wrote: I ran mysqlcheck against a replication master database and it reported a problem with a table, which it corrected. Is the slave now out of sync? If so, how do I correct the problem without copying the whole database over a slow WAN link? --Eric By Default when you run REPAIR or myisamchk --recover , MySQL writes the changes made to the Binlog , and those will be obviously replicated to the slave, so if everything went fine with your myisamck on the master, then your slave is in sync and you don't have to do anyhthing special on the slave... Kishore Jalleda http://kjalleda.googlepages.com/projects -- 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] __ Webmail Intercol http://www.intercol.com.br -- 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]
Q1. What would run faster?
Dear List, I am looking to see what the List thinks about this question. If we to run the same query that needs tmp table to be open to get an answer. * on a server with * and without an RAID array, the rest of hardware would not change as much as possible. Where this query would run faster? Regards, Mikhail Berman
EXPORTING results to CSV
Is there a way to export the results to a text file (comma-delimited preferred)... ie, SELECT * FROM TABLE test.txt ; (obviously this doesn't work) :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Q1. What would run faster?
Mikhail Berman [EMAIL PROTECTED] writes: Dear List, I am looking to see what the List thinks about this question. If we to run the same query that needs tmp table to be open to get an answer. * on a server with * and without an RAID array, the rest of hardware would not change as much as possible. Where this query would run faster? For disk intense applications, regardless if it's a database or some other application, a proper RAID setup will of course run faster. It also depends on what kind of RAID you are using, and how well the RAID implementation (typically the RAID controller) works. /David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: EXPORTING results to CSV
try : select . into outfile '/tmp/t3.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' from table where . This will create a file in the /tmp directory on the DB server itself this doesn't do the column headings and your output file cannot already exist. -Original Message- From: Cummings, Shawn (GNAPs) [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 03, 2006 2:45 PM To: Mysql General (E-mail) Subject: EXPORTING results to CSV Is there a way to export the results to a text file (comma-delimited preferred)... ie, SELECT * FROM TABLE test.txt ; (obviously this doesn't work) :) -- 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 add multiple index
If you can afford a lock on the table to last a little longer you can go with creating the 2 indexes at once. If not create the one by one... i'm sure some other queries will be honoured between those ALTER statements. From MySQL 4.0 we have: ALTER TABLE ... DISABLE KEYS and ALTER TABLE ... ENABLE KEYS Using the above when you have many inserts the missing index entries are created only once... maybe the same is with adding 2 indexes at a time instead of adding one at a time... So it is possible (although i din't found anything in the manual) that adding 2 indexes at a time will be faster ! [I'll get back if i'll find something in the Certification Study Guide... a pretty nice book !] -- Gabriel PREDA Senior Web Developer
Re: Mysql add multiple index
Me again... in the Certification Study Guide it is writen that is more efficient to add 2 (or many) indexes at a time then adding them individualy... but they don't say why ! I stand by my initial advice: *If you can afford a lock on the table to last a little longer you can go with creating the 2 indexes at once.* *If not create the one by one... i'm sure some other queries will be honoured between those ALTER statements.* -- Gabriel PREDA Senior Web Developer
RE: Q1. What would run faster?
Thank you, David, We are using RAID 5. But, could I bring a point here. A RAID device is usually serves to preserve data, by creating a mirror copy of files on its hard-drives, devices. If this is true, then for a large query that requires a large temp file that would exists on its HD for a long time and in my case it takes over an hour to get the answer back. Would it not the RAID try to make a copy of the temp file, by doing so would it not prolong the return of the answer? Regards, Mikhail Berman -Original Message- From: David Israelsson [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 03, 2006 2:54 PM To: mysql@lists.mysql.com Subject: Re: Q1. What would run faster? Mikhail Berman [EMAIL PROTECTED] writes: Dear List, I am looking to see what the List thinks about this question. If we to run the same query that needs tmp table to be open to get an answer. * on a server with * and without an RAID array, the rest of hardware would not change as much as possible. Where this query would run faster? For disk intense applications, regardless if it's a database or some other application, a proper RAID setup will of course run faster. It also depends on what kind of RAID you are using, and how well the RAID implementation (typically the RAID controller) works. /David -- 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]
Q2. Is there anything could be done to speed up this query
Dear List, I have a table: CREATE TABLE `TICKER_HISTORY_PRICE_DATA_STAGING_NO_KEYS` ( `price_data_ticker` char(8) NOT NULL default '', `price_data_date` date NOT NULL default '-00-00', `price_data_open` float default NULL, `price_data_high` float default NULL, `price_data_low` float default NULL, `price_data_close` float default NULL, `price_data_volume` float default NULL, KEY `prdadadx` (`price_data_date`), KEY `prdatidx` (`price_data_ticker`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | That holds: mysql select count(*) from TICKER_HISTORY_PRICE_DATA_STAGING_NO_KEYS; +--+ | count(*) | +--+ | 19087802 | +--+ 1 row in set (0.00 sec) I am looking to see if there is something I can do to speed up this query: select count(price_data_date), price_data_date from TICKER_HISTORY_PRICE_DATA_STAGING_NO_KEYS group by price_data_ticker, price_data_date having count(price_data_date) 1; My explain returns: mysql explain select count(price_data_date), price_data_date from TICKER_HISTORY_PRICE_DATA_STAGING_NO_KEYS group by price_data_ticker, price_data_date having count(price_data_date) 1; ++-+---+--+- --+--+-+--+--+-- ---+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+- --+--+-+--+--+-- ---+ | 1 | SIMPLE | TICKER_HISTORY_PRICE_DATA_STAGING_NO_KEYS | ALL | NULL | NULL |NULL | NULL | 19087802 | Using temporary; Using filesort | ++-+---+--+- --+--+-+--+--+-- ---+ This table is intentionally designed without the primary keys, so we can catch and display duplicates. Regards, Mikhail Berman
Re: Q2. Is there anything could be done to speed up this query
On Wednesday 03 May 2006 12:16 pm, Mikhail Berman wrote: I have a table: CREATE TABLE `TICKER_HISTORY_PRICE_DATA_STAGING_NO_KEYS` ( `price_data_ticker` char(8) NOT NULL default '', `price_data_date` date NOT NULL default '-00-00', `price_data_open` float default NULL, `price_data_high` float default NULL, `price_data_low` float default NULL, `price_data_close` float default NULL, `price_data_volume` float default NULL, KEY `prdadadx` (`price_data_date`), KEY `prdatidx` (`price_data_ticker`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | snip mysql explain select count(price_data_date), price_data_date from TICKER_HISTORY_PRICE_DATA_STAGING_NO_KEYS group by price_data_ticker, price_data_date having count(price_data_date) 1; | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra ++-+---+--+- --+--+-+--+--+-- ---+ | 1 | SIMPLE | TICKER_HISTORY_PRICE_DATA_STAGING_NO_KEYS | ALL | NULL | NULL |NULL | NULL | 19087802 | Using temporary; Using filesort | ++-+---+--+- --+--+-+--+--+-- ---+ Well, one problem is that nothing is being indexed. I think your best bet is that if you're using that as a high volume query, to look at indexing other fields (possibly price_data_date as it seems to be the main hit for your search). However, this is really all going to depend on how the database is interacted with as well. If this is the only query on this table, or the only major query, then I'd say look at indexing price_data_date per what I'm seeing in your query. This table is intentionally designed without the primary keys, so we can catch and display duplicates. Regards, Mikhail Berman -- Chris White PHP Programmer / DB Monkey Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Q2. Is there anything could be done to speed up this query
Thank you, Chris But the table is indexed on the field you are referring to and the other one the query, which is evident from this: KEY `prdadadx` (`price_data_date`), KEY `prdatidx` (`price_data_ticker`) And this: ll TICKER_HISTORY_PRICE_DATA_STAGING_NO_KEYS.* -rw-rw 1 mysqlmysql610809664 May 1 13:32 TICKER_HISTORY_PRICE_DATA_STAGING_NO_KEYS.MYD -rw-rw 1 mysqlmysql223084544 May 1 13:34 TICKER_HISTORY_PRICE_DATA_STAGING_NO_KEYS.MYI huge index file -rw-rw 1 mysqlmysql8902 May 1 09:00 TICKER_HISTORY_PRICE_DATA_STAGING_NO_KEYS.frm Any other ideas, please? Mikhail Berman -Original Message- From: Chris White [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 03, 2006 3:27 PM To: mysql@lists.mysql.com Subject: Re: Q2. Is there anything could be done to speed up this query On Wednesday 03 May 2006 12:16 pm, Mikhail Berman wrote: I have a table: CREATE TABLE `TICKER_HISTORY_PRICE_DATA_STAGING_NO_KEYS` ( `price_data_ticker` char(8) NOT NULL default '', `price_data_date` date NOT NULL default '-00-00', `price_data_open` float default NULL, `price_data_high` float default NULL, `price_data_low` float default NULL, `price_data_close` float default NULL, `price_data_volume` float default NULL, KEY `prdadadx` (`price_data_date`), KEY `prdatidx` (`price_data_ticker`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | snip mysql explain select count(price_data_date), price_data_date from TICKER_HISTORY_PRICE_DATA_STAGING_NO_KEYS group by price_data_ticker, price_data_date having count(price_data_date) 1; | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra ++-+---+--+- --+--+-+--+--+ --+--+-+--+--+-- ---+ | 1 | SIMPLE | TICKER_HISTORY_PRICE_DATA_STAGING_NO_KEYS | ALL | NULL | NULL |NULL | NULL | 19087802 | Using temporary; Using filesort | ++-+---+--+- --+--+-+--+--+ --+--+-+--+--+-- ---+ Well, one problem is that nothing is being indexed. I think your best bet is that if you're using that as a high volume query, to look at indexing other fields (possibly price_data_date as it seems to be the main hit for your search). However, this is really all going to depend on how the database is interacted with as well. If this is the only query on this table, or the only major query, then I'd say look at indexing price_data_date per what I'm seeing in your query. This table is intentionally designed without the primary keys, so we can catch and display duplicates. Regards, Mikhail Berman -- Chris White PHP Programmer / DB Monkey Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.0.20 installation
Hello All, Sorry for the dumb question, but how do I force MySQL configure script to put files into particular directories of my choice? I mean, when I issue: ./configure --prefix=/mysql libraries are put under /mysql/lib/mysql and headers under /mysql/include/mysql. I want the script to put stuff under /mysql/lib and /mysql/include, respectively. --libdir=/mysql/lib and --includedir=/mysql/include also put /mysql at the end, so this doesn't seem the way out either. Any comments, advice and hint is greatly appreciated. BR Peter. -- View this message in context: http://www.nabble.com/MySQL-5.0.20-installation-t1552384.html#a4217354 Sent from the MySQL - General forum at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: EXPORTING results to CSV
Is there a way to export the results to a text file (comma-delimited preferred)... ie, SELECT * FROM TABLE test.txt ; (obviously this doesn't work) :) With our database developer tool that includes support for MySQL, this is an easy task. Check it out, Database Workbench: www.upscene.com Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Q2. Is there anything could be done to speed up this query
In the last episode (May 03), Mikhail Berman said: Thank you, Chris But the table is indexed on the field you are referring to and the other one the query, which is evident from this: KEY `prdadadx` (`price_data_date`), KEY `prdatidx` (`price_data_ticker`) These are two separate keys, though, and your query is doing a GROUP BY across both fields, so neither of those keys would be useful (mysql would have to do a random record lookup for each row to fetch the other field). Try an index on (price_data_ticker, price_data_date). Since your query only references those fields, mysql should be able to return your results just by scanning the index. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Missing information Search
Robert What I need to be able to do is find any values of K_Code in table 1 that don't appear in table 2. SELECT t1.k_code FROM table1 t1 LEFT JOIN table2 t2 USING (k_code) WHERE t2.k_code IS NULL; PB - Robert Gehrig wrote: Hi I have two tables that are structured like so: Table 1: ID int K_Code int Table 2 K_Code int K_Desc char Table 2 has been corrupted and may be missing some records. What I need to be able to do is find any values of K_Code in table 1 that don't appear in table 2. Thanks Robert Gehrig Webmaster at www.gdbarri.com e-mail: [EMAIL PROTECTED] -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.2/329 - Release Date: 5/2/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
