DBA: please review my.cnf [for Java Hibernate application] and give suggestions
Hi We will use a Java application which uses Hibernate for DB calls. The vendor didn't made recommendations howto configure MySQL. The application is not yet in production. MySQL is new to me, I previously used Oracle DB. The vendor provided a guide howto configure Oracle. This is our my.conf $ cat /etc/my.cnf [mysqld] datadir=/opt/pprd/mysql socket=/var/lib/mysql/mysql.sock user=mysql max_allowed_packet=10M query_cache_size = 8388608 table_open_cache=256 tmp_table_size=67108864 log_bin = /opt/pprd/log/mysql-bin.log log_bin_index = /opt/pprd/log/mysql-bin.index expire_logs_days= 5 max_binlog_size = 100M binlog_format = row [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid $ rpm -qa mysql mysql-5.1.66-1.el6_3.x86_64 $ free -m total used free sharedbuffers cached Mem: 32241 31610630 0240 27209 -/+ buffers/cache: 4161 28080 Swap: 2047 74 1973 $ cat /etc/redhat-release Red Hat Enterprise Linux Server release 6.1 (Santiago) did we miss an inportant option? What config options do you use? the DDL is provided by vendor and we are not allowed to change it (for example indexes). I personly like if the MySQL behaves like a out-of-tbe-box Oracle 11g db.
Re: DBA: please review my.cnf [for Java Hibernate application] and give suggestions
Hi Lukas, What is your default engine? In MySQL there are a lot of parameters that configure the engine behaviour. Depends on the engine, I suggest you to add some parameters or others. Also it's important to know the size of your data. Your configuration is minimal and by default is not optimal. Regards, Antonio.
Re: DBA: please review my.cnf [for Java Hibernate application] and give suggestions
Is it a standalone DB server or Application is also hosted on top of it. You can give 50-70% of RAM to memory parameters like Innodb_buffer_pool_size ( Innodb ) and key_cache ( Myisam ) for mysql tables. Below link : http://mysql.rjweb.org/doc.php/memory will give you a brief idea. Thanks
Re: DBA: please review my.cnf [for Java Hibernate application] and give suggestions
Hi Antonio all tables use InnoDB. The size is 27 GB (not yet in prod). I guess in prod it will be fast 80GB. thanks On Wed, Feb 12, 2014 at 10:28 AM, Antonio Fernández Pérez antoniofernan...@fabergames.com wrote: Hi Lukas, What is your default engine? In MySQL there are a lot of parameters that configure the engine behaviour. Depends on the engine, I suggest you to add some parameters or others. Also it's important to know the size of your data. Your configuration is minimal and by default is not optimal. Regards, Antonio.
Re: DBA: please review my.cnf [for Java Hibernate application] and give suggestions
Hi it's also a Tomcat application server. Not dedicated MySQL instance. On Wed, Feb 12, 2014 at 11:28 AM, Adarsh Sharma eddy.ada...@gmail.comwrote: Is it a standalone DB server or Application is also hosted on top of it. You can give 50-70% of RAM to memory parameters like Innodb_buffer_pool_size ( Innodb ) and key_cache ( Myisam ) for mysql tables. Below link : http://mysql.rjweb.org/doc.php/memory will give you a brief idea. Thanks
Re: DBA: please review my.cnf [for Java Hibernate application] and give suggestions
Hi Lukas,
In that case, such as Adarsh has said, you can configure until 70% of your
RAM for innodb_buffer_pool_size.
In your case, with 3GB RAM, I suggest you to configure until 2GB for MySQL:
Minimal for MyISAM (Maybe 32MB), and the rest for InnoDB. Your problem will
be loading data. Maybe your application will work slowly loading data
because there are more data than RAM memory.
Executing the following script, you can see your optimal buffer size for
InnoDB with your data.
SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf10240,0,IF(PowerOf10243,0,PowerOf1024)))+0.4),
SUBSTR(' KMG',IF(PowerOf10240,0,
IF(PowerOf10243,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM
information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 3 PowerOf1024) B;
SELECT CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1))
Recommended_InnoDB_Buffer_Pool_Size FROM
(
SELECT RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw
FROM
(
SELECT SUM(data_length+index_length)*1.1*growth RIBPS
FROM information_schema.tables AAA,
(SELECT 1 growth) BBB
WHERE ENGINE='InnoDB'
) AA
) A;
Good luck!
Regards,
Antonio.
Re: DBA: please review my.cnf [for Java Hibernate application] and give suggestions
2014-02-12 12:32 GMT+01:00 Lukas Lehner webleh...@gmail.com: Hi Antonio all tables use InnoDB. The size is 27 GB (not yet in prod). I guess in prod it will be fast 80GB. Depending on how your application is going to use MySQL resources you will need to tweak some things (and not only MySQL). If it is going to be CPU bound, IO bound etc...there are different scenarios Anyways, some general things to take a look at: - Use file per table if possible. This won't give you extra performance, but it will be good if you run into disk spaces issues or for future table migrations. - Make sure you have trx_commit and sync_binlog disabled (make sure you understand what this means and what problems you could have during an un expected crash) - If you're expecting lot of temporary tables (filesorts), make sure tmpdir runs over a fast disk. - Use NUMA memory handling - Make sure you test different disk schedulers (depending if you have RAID and which kind of it) and see how they perform. - You might want to take a look to smp irq affinity and see how it could impact in your system. Manuel.
Please Help. selectcol_arrayref problem
use DBI;
my $dbh = DBI-connect( DBI:mysql:rushload;192.168.0.1, $usrr, $passw, {
RaiseError = 3 } );
my $dbs = $dbh-selectcol_arrayref(show databases);
#my $dsn = dbi:mysql:information_schema:192.168.0.1:3306;
#my $dbh = DBI-connect($dsn, $usrr, $passw);
my $dbs = $dbh-selectcol_arrayref('show databases');
print @$dbs\n;
When I query the server for a list of databases with the code above it
returns the name of just two and there are over 10.
Any ideas?
Thanks
Re: Please Help. selectcol_arrayref problem
I did a GRANT REPLICATION SLAVE ON *.* TO 'user'@'192.168.0.23' IDENTIFIED
BY 'psswd';
on the master. Doesn't *.* mean everything? Why would it just show me to
databases?
2013/4/2 Larry Martell larry.mart...@gmail.com
On Tue, Apr 2, 2013 at 11:30 AM, Richard Reina gatorre...@gmail.com
wrote:
use DBI;
my $dbh = DBI-connect( DBI:mysql:rushload;192.168.0.1, $usrr, $passw,
{
RaiseError = 3 } );
my $dbs = $dbh-selectcol_arrayref(show databases);
#my $dsn = dbi:mysql:information_schema:192.168.0.1:3306;
#my $dbh = DBI-connect($dsn, $usrr, $passw);
my $dbs = $dbh-selectcol_arrayref('show databases');
print @$dbs\n;
When I query the server for a list of databases with the code above it
returns the name of just two and there are over 10.
Any ideas?
Permissions - the user you're logging in as probably only has
permission to see the 2 that are being returned.
Re: Please Help. selectcol_arrayref problem
On Tue, Apr 2, 2013 at 11:30 AM, Richard Reina gatorre...@gmail.com wrote:
use DBI;
my $dbh = DBI-connect( DBI:mysql:rushload;192.168.0.1, $usrr, $passw, {
RaiseError = 3 } );
my $dbs = $dbh-selectcol_arrayref(show databases);
#my $dsn = dbi:mysql:information_schema:192.168.0.1:3306;
#my $dbh = DBI-connect($dsn, $usrr, $passw);
my $dbs = $dbh-selectcol_arrayref('show databases');
print @$dbs\n;
When I query the server for a list of databases with the code above it
returns the name of just two and there are over 10.
Any ideas?
Permissions - the user you're logging in as probably only has
permission to see the 2 that are being returned.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Please Help. selectcol_arrayref problem
Nope. That's just granting replication privileges so it can read updates
on all tables on all databases. It cannot select anything.
Why are you trying to connect with a replication slave user?
On 4/2/13 1:47 PM, Richard Reina gatorre...@gmail.com wrote:
I did a GRANT REPLICATION SLAVE ON *.* TO 'user'@'192.168.0.23'
IDENTIFIED
BY 'psswd';
on the master. Doesn't *.* mean everything? Why would it just show me to
databases?
2013/4/2 Larry Martell larry.mart...@gmail.com
On Tue, Apr 2, 2013 at 11:30 AM, Richard Reina gatorre...@gmail.com
wrote:
use DBI;
my $dbh = DBI-connect( DBI:mysql:rushload;192.168.0.1, $usrr,
$passw,
{
RaiseError = 3 } );
my $dbs = $dbh-selectcol_arrayref(show databases);
#my $dsn = dbi:mysql:information_schema:192.168.0.1:3306;
#my $dbh = DBI-connect($dsn, $usrr, $passw);
my $dbs = $dbh-selectcol_arrayref('show databases');
print @$dbs\n;
When I query the server for a list of databases with the code above it
returns the name of just two and there are over 10.
Any ideas?
Permissions - the user you're logging in as probably only has
permission to see the 2 that are being returned.
Notice: This communication may contain privileged and/or confidential
information. If you are not the intended recipient, please notify the sender by
email, and immediately delete the message and any attachments without copying
or disclosing them. LBI may, for any reason, intercept, access, use, and
disclose any information that is communicated by or through, or which is stored
on, its networks, applications, services, and devices.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
RE: Please check the stored procedure
To adjust a table name within a SQL statement, you need to create a
string with the updated values and use PREPARE/EXECUTE
See below, I am replacing your CONCAT with the complete insert statement
The example below is also assuming the value in the WHERE clause should
be adjusted to the number, instead of hard-coded to '9'
set @str = concat('Insert Into test (Panel_Id) select Panel_Id
from ',Project_Number_val,'_List where Project_Number_val='
,',Project_Number_val,');
prepare stmt from @str;
execute stmt;
deallocate prepare stmt;
-Original Message-
From: Girish Talluru [mailto:girish.dev1...@gmail.com]
Sent: Tuesday, February 12, 2013 1:37 AM
To: mysql@lists.mysql.com
Subject: Please check the stored procedure
DROP PROCEDURE IF EXISTS Cursor_Test;# MySQL returned an empty result
set
(i.e. zero rows).
DELIMITER $$
CREATE PROCEDURE Cursor_Test()
BEGIN
DECLARE Project_Number_val VARCHAR( 255 );
DECLARE Project_List_val VARCHAR(255);
DECLARE no_more_rows BOOLEAN;
DECLARE loop_cntr INT DEFAULT 0;
DECLARE num_rows INT DEFAULT 0;
DECLARE projects_cur CURSOR FOR
SELECT Project_Id
FROM Project_Details;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_rows = TRUE;
OPEN projects_cur;
select FOUND_ROWS() into num_rows;
the_loop: LOOP
FETCH projects_cur
INTO Project_Number_val;
IF no_more_rows THEN
CLOSE projects_cur;
LEAVE the_loop;
END IF;
SET Project_List_val=CONCAT(`Project_Number_val`,'_List') Please
check am I doing CONCAT correct here?
Insert Into test (Panel_Id) select Panel_Id from Project_List_val where
Project_Number_val='9'; ---Is this taking 9_List as table name?
SET loop_cntr = loop_cntr + 1;
END LOOP the_loop;
select num_rows, loop_cntr;
END $$# MySQL returned an empty result set (i.e. zero rows).
DELIMITER
This e-mail, including attachments, may include confidential and/or
proprietary information, and may be used only by the person or entity
to which it is addressed. If the reader of this e-mail is not the intended
recipient or his or her authorized agent, the reader is hereby notified
that any dissemination, distribution or copying of this e-mail is
prohibited. If you have received this e-mail in error, please notify the
sender by replying to this message and delete this e-mail immediately.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
RE: Fwd: Query take too long time - please help!
If the collation for ksd in ..._ci, then it is case-insensitive, and you can
get rid of both calls to LOWER().
-Original Message-
From: Carsten Pedersen [mailto:cars...@bitbybit.dk]
Sent: Tuesday, July 10, 2012 11:22 AM
To: Darek Maciera
Cc: mysql@lists.mysql.com
Subject: Re: Fwd: Query take too long time - please help!
On 10.07.2012 13:16, Darek Maciera wrote:
2012/7/10 Ananda Kumar anan...@gmail.com:
can u show the explain plan for your query
Thanks, for reply!
Sure:
mysql EXPLAIN SELECT * FROM books WHERE
mysql LOWER(ksd)=LOWER('4204661375');
That's definitely not the query you showed the first time around. The
query you're showing here will force a table scan to calculate
LOWER(ksd) for every single row.
Also, how do you know that ksd id unique (as stated in your original
post)? You have no index on it to ensure uniqueness.
You'll have to find some other way to query the table.
Best,
/ Carsten
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Query take too long time - please help!
Hello, I have table: mysql DESCRIBE books; |id |int(255) | NO | PRI | NULL | auto_increment | | idu | int(255) | NO | MUL | NULL | ksd | char(15) | YES | | NULL | idn | int(1)| NO | MUL | NULL | text1 | text | NO | | NULL | ips | int(1)| NO | MUL | NULL | ips2| int(1)| NO | MUL | NULL | date | timestamp | NO | | CURRENT_TIMESTAMP | date2 | date | NO | | NULL | text2| text | NO | | NULL | text3| text | NO | | NULL I have indexes in this table: mysql SHOW INDEX FROM uzytkownicy_strona_kody; | books | 0 | PRIMARY|1 | id | A | 369625 | NULL | NULL | | BTREE | | | books | 1 | idx_idu|1 | idu | A | 184812 | NULL | NULL | | BTREE | | | books | 1 | idx_id |1 | id | A | 369625 | NULL | NULL | | BTREE | | | books | 1 | idx_idn|1 | idn | A | 8 | NULL | NULL | | BTREE | | | books | 1 | idx_ips |1 | ips | A | 8 | NULL | NULL | | BTREE | | | books | 1 | idx_ips2 |1 | ips2 | A | 8 | NULL | NULL | | BTREE | | Every books have unique: 'ksd'. There are about 370.000 records in this table. But this SELECT take too long time: mysql SELECT * FROM books WHERE ksd ='A309CC47B7'; 1 row in set (2.59 sec) Table is in InnoDB engine. I added to my.cnf: innodb_buffer_pool_size = 512MB Any suggestions? Help, please.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Query take too long time - please help!
can u show the explain plan for your query On Tue, Jul 10, 2012 at 2:59 PM, Darek Maciera darekmaci...@gmail.comwrote: Hello, I have table: mysql DESCRIBE books; |id |int(255) | NO | PRI | NULL | auto_increment | | idu | int(255) | NO | MUL | NULL | ksd | char(15) | YES | | NULL | idn | int(1)| NO | MUL | NULL | text1 | text | NO | | NULL | ips | int(1)| NO | MUL | NULL | ips2| int(1)| NO | MUL | NULL | date | timestamp | NO | | CURRENT_TIMESTAMP | date2 | date | NO | | NULL | text2| text | NO | | NULL | text3| text | NO | | NULL I have indexes in this table: mysql SHOW INDEX FROM uzytkownicy_strona_kody; | books | 0 | PRIMARY|1 | id | A | 369625 | NULL | NULL | | BTREE | | | books | 1 | idx_idu|1 | idu | A | 184812 | NULL | NULL | | BTREE | | | books | 1 | idx_id |1 | id | A | 369625 | NULL | NULL | | BTREE | | | books | 1 | idx_idn|1 | idn | A | 8 | NULL | NULL | | BTREE | | | books | 1 | idx_ips |1 | ips | A | 8 | NULL | NULL | | BTREE | | | books | 1 | idx_ips2 |1 | ips2 | A | 8 | NULL | NULL | | BTREE | | Every books have unique: 'ksd'. There are about 370.000 records in this table. But this SELECT take too long time: mysql SELECT * FROM books WHERE ksd ='A309CC47B7'; 1 row in set (2.59 sec) Table is in InnoDB engine. I added to my.cnf: innodb_buffer_pool_size = 512MB Any suggestions? Help, please.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Fwd: Query take too long time - please help!
2012/7/10 Ananda Kumar anan...@gmail.com:
can u show the explain plan for your query
Thanks, for reply!
Sure:
mysql EXPLAIN SELECT * FROM books WHERE LOWER(ksd)=LOWER('4204661375');
++-+-+--+---+--+-+--++-+
| id | select_type | table | type | possible_keys |
key | key_len | ref | rows | Extra |
++-+-+--+---+--+-+--++-+
| 1 | SIMPLE | books | ALL | NULL |
NULL | NULL| NULL | 378241 | Using where |
++-+-+--+---+--+-+--++-+
1 row in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Query take too long time - please help!
you are using a function-LOWER, which will not make use of the unique key
index on ksd.
Mysql does not support function based index, hence your query is doing a
FULL TABLE scan and taking more time.
On Tue, Jul 10, 2012 at 4:46 PM, Darek Maciera darekmaci...@gmail.comwrote:
2012/7/10 Ananda Kumar anan...@gmail.com:
can u show the explain plan for your query
Thanks, for reply!
Sure:
mysql EXPLAIN SELECT * FROM books WHERE LOWER(ksd)=LOWER('4204661375');
++-+-+--+---+--+-+--++-+
| id | select_type | table | type | possible_keys |
key | key_len | ref | rows | Extra |
++-+-+--+---+--+-+--++-+
| 1 | SIMPLE | books | ALL | NULL |
NULL | NULL| NULL | 378241 | Using where |
++-+-+--+---+--+-+--++-+
1 row in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Fwd: Query take too long time - please help!
On 10.07.2012 13:16, Darek Maciera wrote:
2012/7/10 Ananda Kumar anan...@gmail.com:
can u show the explain plan for your query
Thanks, for reply!
Sure:
mysql EXPLAIN SELECT * FROM books WHERE LOWER(ksd)=LOWER('4204661375');
That's definitely not the query you showed the first time around. The
query you're showing here will force a table scan to calculate
LOWER(ksd) for every single row.
Also, how do you know that ksd id unique (as stated in your original
post)? You have no index on it to ensure uniqueness.
You'll have to find some other way to query the table.
Best,
/ Carsten
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Please do not open the link that was sent to you from my email address
Dear Friends, There has been some problems with my mail box and some spam emails have been sent to you from my email. Please do not open the link that was sent to you from my email address!! Sorry for the problem caused. Best regards, Javad Bakhshi, Computer Science M.Sc Department of IT, Uppsala University
RE: Within-group aggregate query help please - customers and latest subscription row
2011/10/24 16:31 -0700, Daevid Vincent WHERE cs.customer_id = 7 GROUP BY customer_id Well, the latter line is now redundant. How will you make the '7' into a parameter? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Within-group aggregate query help please - customers and latest subscription row
I know this is a common problem, and I've been struggling with it for a full day now but I can't get it. I also tried a few sites for examples: http://www.artfulsoftware.com/infotree/queries.php#101 http://forums.devarticles.com/general-sql-development-47/select-max-datetime -problem-10210.html Anyways, pretty standard situation: CREATE TABLE `customers` ( `customer_id` int(10) unsigned NOT NULL auto_increment, `email` varchar(64) NOT NULL default '', `name` varchar(128) NOT NULL default '', `username` varchar(32) NOT NULL, ... ); CREATE TABLE `customers_subscriptions` ( `subscription_id` bigint(12) unsigned NOT NULL default '0', `customer_id` int(10) unsigned NOT NULL default '0', `date` date NOT NULL default '-00-00', ... ); I want to show a table where I list out the ID, email, username, and LAST SUBSCRIPTION. I need this data in TWO ways: The FIRST way, is with a query JOINing the two tables so that I can easily display that HTML table mentioned. That is ALL customers and the latest subscription they have. The SECOND way is when I drill into the customer, I already know the customer_id and so don't need to JOIN with that table, I just want to get the proper row from the customers_subscriptions table itself. SELECT * FROM `customers_subscriptions` WHERE customer_id = 7 ORDER BY `date` DESC; subscription_id processor customer_id date --- - --- -- 134126370 chargem 7 2005-08-04 1035167192 billme 7 2004-02-08 SELECT MAX(`date`) FROM `customers_subscriptions` WHERE customer_id = 7 GROUP BY customer_id; gives me 2005-08-04 obviously, but as you all know, mySQL completely takes a crap on your face when you try what would seem to be the right query: SELECT subscription_id, MAX(`date`) FROM `customers_subscriptions` WHERE customer_id = 7 GROUP BY customer_id; subscription_id MAX(`date`) --- --- 1035167192 2005-08-04 Notice how I have the correct DATE, but the wrong subscription_id. In the example web sites above, they seem to deal more with finding the MAX(subscription_id), which in my case will not work. I need the max DATE and the corresponding row (with matching subscription_id). Thanks, d -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Within-group aggregate query help please - customers and latest subscription row
A kind (and shy) soul replied to me off list and suggested this solution, however, this takes 28 seconds (that's for a single customer_id, so this is not going to scale). Got any other suggestions? :-) SELECT c.customer_id, c.email, c.name, c.username, s.subscription_id, s.`date` FROM customers AS c INNER JOIN customers_subscriptions AS s ON c.customer_id = s.customer_id INNER JOIN (SELECT MAX(`date`) AS LastDate, customer_id FROM customers_subscriptions AS cs GROUP BY customer_id) AS `x` ON s.customer_id = x.customer_id AND s.date = x.LastDate WHERE c.customer_id = 7; There are 781,270 customers (nearly 1 million) and 1,018,092 customer_subscriptions. Our tables have many indexes on pretty much every column and for sure the ones we use here. EXPLAIN says: id select_type table typepossible_keys key key_len refrows Extra -- --- -- -- --- --- -- --- --- 1 PRIMARY c const PRIMARY PRIMARY 4 const 1 1 PRIMARY s ref date,customer_id customer_id 4 const 2 1 PRIMARY derived2 ALL (NULL)(NULL) (NULL) (NULL) 781265 Using where 2 DERIVED cs ALL (NULL)(NULL) (NULL) (NULL) 1018092 Using temporary; Using filesort -Original Message- From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Monday, October 24, 2011 1:46 PM To: mysql@lists.mysql.com Subject: Within-group aggregate query help please - customers and latest subscription row I know this is a common problem, and I've been struggling with it for a full day now but I can't get it. I also tried a few sites for examples: http://www.artfulsoftware.com/infotree/queries.php#101 http://forums.devarticles.com/general-sql-development-47/select-max-datetime -problem-10210.html Anyways, pretty standard situation: CREATE TABLE `customers` ( `customer_id` int(10) unsigned NOT NULL auto_increment, `email` varchar(64) NOT NULL default '', `name` varchar(128) NOT NULL default '', `username` varchar(32) NOT NULL, ... ); CREATE TABLE `customers_subscriptions` ( `subscription_id` bigint(12) unsigned NOT NULL default '0', `customer_id` int(10) unsigned NOT NULL default '0', `date` date NOT NULL default '-00-00', ... ); I want to show a table where I list out the ID, email, username, and LAST SUBSCRIPTION. I need this data in TWO ways: The FIRST way, is with a query JOINing the two tables so that I can easily display that HTML table mentioned. That is ALL customers and the latest subscription they have. The SECOND way is when I drill into the customer, I already know the customer_id and so don't need to JOIN with that table, I just want to get the proper row from the customers_subscriptions table itself. SELECT * FROM `customers_subscriptions` WHERE customer_id = 7 ORDER BY `date` DESC; subscription_id processor customer_id date --- - --- -- 134126370 chargem 7 2005-08-04 1035167192 billme 7 2004-02-08 SELECT MAX(`date`) FROM `customers_subscriptions` WHERE customer_id = 7 GROUP BY customer_id; gives me 2005-08-04 obviously, but as you all know, mySQL completely takes a crap on your face when you try what would seem to be the right query: SELECT subscription_id, MAX(`date`) FROM `customers_subscriptions` WHERE customer_id = 7 GROUP BY customer_id; subscription_id MAX(`date`) --- --- 1035167192 2005-08-04 Notice how I have the correct DATE, but the wrong subscription_id. In the example web sites above, they seem to deal more with finding the MAX(subscription_id), which in my case will not work. I need the max DATE and the corresponding row (with matching subscription_id). Thanks, d -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Within-group aggregate query help please - customers and latest subscription row
Okay, it seems I am learning... slowly... So there needs to be a second WHERE in the sub-select... To get ONE customer's last subscription (0.038s): SELECT c.customer_id, c.email, c.name, c.username, s.subscription_id, s.`date` FROM customers AS c INNER JOIN customers_subscriptions AS s ON c.customer_id = s.customer_id INNER JOIN (SELECT MAX(`date`) AS LastDate, customer_id FROM customers_subscriptions AS cs WHERE cs.customer_id = 7 GROUP BY customer_id ) AS `x` ON s.customer_id = x.customer_id AND s.date = x.LastDate WHERE c.customer_id = 7; To get ALL customers and their last subscription row (1m:28s) SELECT c.customer_id, c.email, c.name, c.username, s.subscription_id, s.`date` FROM customers AS c INNER JOIN customers_subscriptions AS s ON c.customer_id = s.customer_id INNER JOIN (SELECT MAX(`date`) AS LastDate, customer_id FROM customers_subscriptions AS cs GROUP BY customer_id ) AS `x` ON s.customer_id = x.customer_id AND s.date = x.LastDate ORDER BY customer_id LIMIT 10; Thanks to you know who you are for pointing me in the right direction. Hopefully this helps someone else. d. -Original Message- From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Monday, October 24, 2011 4:06 PM To: mysql@lists.mysql.com Subject: RE: Within-group aggregate query help please - customers and latest subscription row A kind (and shy) soul replied to me off list and suggested this solution, however, this takes 28 seconds (that's for a single customer_id, so this is not going to scale). Got any other suggestions? :-) SELECT c.customer_id, c.email, c.name, c.username, s.subscription_id, s.`date` FROM customers AS c INNER JOIN customers_subscriptions AS s ON c.customer_id = s.customer_id INNER JOIN (SELECT MAX(`date`) AS LastDate, customer_id FROM customers_subscriptions AS cs GROUP BY customer_id) AS `x` ON s.customer_id = x.customer_id AND s.date = x.LastDate WHERE c.customer_id = 7; There are 781,270 customers (nearly 1 million) and 1,018,092 customer_subscriptions. Our tables have many indexes on pretty much every column and for sure the ones we use here. EXPLAIN says: id select_type table typepossible_keys key key_len refrows Extra -- --- -- -- --- --- -- --- --- 1 PRIMARY c const PRIMARY PRIMARY 4 const 1 1 PRIMARY s ref date,customer_id customer_id 4 const 2 1 PRIMARY derived2 ALL (NULL)(NULL) (NULL) (NULL) 781265 Using where 2 DERIVED cs ALL (NULL)(NULL) (NULL) (NULL) 1018092 Using temporary; Using filesort -Original Message- From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Monday, October 24, 2011 1:46 PM To: mysql@lists.mysql.com Subject: Within-group aggregate query help please - customers and latest subscription row I know this is a common problem, and I've been struggling with it for a full day now but I can't get it. I also tried a few sites for examples: http://www.artfulsoftware.com/infotree/queries.php#101 http://forums.devarticles.com/general-sql-development-47/select-max-datetime -problem-10210.html Anyways, pretty standard situation: CREATE TABLE `customers` ( `customer_id` int(10) unsigned NOT NULL auto_increment, `email` varchar(64) NOT NULL default '', `name` varchar(128) NOT NULL default '', `username` varchar(32) NOT NULL, ... ); CREATE TABLE `customers_subscriptions` ( `subscription_id` bigint(12) unsigned NOT NULL default '0', `customer_id` int(10) unsigned NOT NULL default '0', `date` date NOT NULL default '-00-00', ... ); I want to show a table where I list out the ID, email, username, and LAST SUBSCRIPTION. I need this data in TWO ways: The FIRST way, is with a query JOINing the two tables so that I can easily display that HTML table mentioned. That is ALL customers and the latest subscription they have. The SECOND way
Re: Slow query - please help
Hi
Here is the response :
'1', 'PRIMARY', 'cities', 'ref', 'IDX_country_code,IDX_language_code',
'IDX_country_code', '7', 'const', '2088', '100.00', 'Using index condition;
Using where; Using filesort'
'4', 'DEPENDENT SUBQUERY', 'districts', 'ALL', 'IDX_language_code', NULL,
NULL, NULL, '1207', '99.75', 'Using where'
'3', 'DEPENDENT SUBQUERY', 'hotels', 'ALL', 'IDX_language_code', NULL, NULL,
NULL, '163102', '100.00', 'Using where'
'2', 'DEPENDENT SUBQUERY', 'hotels', 'ref',
'UNQ_folder_url,IDX_enabled,IDX_language_code', 'IDX_enabled', '1', 'const',
'267', '100.00', 'Using index condition; Using where'
Thanks
Neil
On Wed, Oct 5, 2011 at 3:12 AM, Johnny Withers joh...@pixelated.net wrote:
Can you post the explain extended output of your query?
Sent from my iPad
On Oct 4, 2011, at 2:45 PM, Neil Tompkins neil.tompk...@googlemail.com
wrote:
Can anyone help me ?
Begin forwarded message:
From: Tompkins Neil neil.tompk...@googlemail.com
Date: 30 September 2011 20:23:47 GMT+01:00
To: mark carson mcar...@pixie.co.za
Cc: [MySQL] mysql@lists.mysql.com
Subject: Re: Slow query - please help
I downloaded version mysql-5.6.2-m5-win32.msi and he table definitions
are below, let me know if you need any more information.
CREATE TABLE `districts` (
`district_id` int(11) NOT NULL,
`language_code` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'en',
`city_id` int(11) DEFAULT NULL,
`country_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`latitude` double DEFAULT NULL,
`longitude` double DEFAULT NULL,
`meta_title` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_keywords` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_description` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`link_text` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`folder_url` varchar(55) COLLATE utf8_unicode_ci DEFAULT NULL,
`enabled` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
`last_changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`district_id`,`language_code`),
UNIQUE KEY `UNQ_folder_url` (`folder_url`),
KEY `IDX_country_code` (`country_code`),
KEY `IDX_enabled` (`enabled`),
KEY `IDX_folder_url` (`folder_url`),
KEY `IDX_language_code` (`language_code`),
KEY `IDX_latitude` (`latitude`),
KEY `IDX_longitude` (`longitude`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `cities` (
`city_id` int(11) NOT NULL,
`language_code` char(2) COLLATE utf8_unicode_ci NOT NULL,
`country_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`nr_hotels` int(11) DEFAULT NULL,
`latitude` double DEFAULT NULL,
`longitude` double DEFAULT NULL,
`meta_title` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_keywords` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_description` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`link_text` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`folder_url` varchar(55) COLLATE utf8_unicode_ci DEFAULT NULL,
`enabled` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
`last_changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`city_id`,`language_code`),
UNIQUE KEY `UNQ_folder_url` (`folder_url`),
KEY `IDX_country_code` (`country_code`),
KEY `IDX_enabled` (`enabled`),
KEY `IDX_folder_url` (`folder_url`),
KEY `IDX_language_code` (`language_code`),
KEY `IDX_latitude` (`latitude`),
KEY `IDX_longitude` (`longitude`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `hotels` (
`hotel_id` int(11) NOT NULL,
`language_code` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'en',
`address` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`city` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`city_id` int(11) DEFAULT NULL,
`class_is_estimated` tinyint(4) DEFAULT NULL,
`class` tinyint(4) DEFAULT NULL,
`country_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`currencycode` char(3) COLLATE utf8_unicode_ci DEFAULT NULL,
`district` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`hoteltype_id` int(11) DEFAULT NULL,
`is_closed` tinyint(4) DEFAULT NULL,
`latitude` double DEFAULT NULL,
`longitude` double DEFAULT NULL,
`maxrate` double DEFAULT NULL,
`minrate` double DEFAULT NULL,
`original_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`nr_rooms` int(11) DEFAULT NULL,
`preferred` int(11) DEFAULT NULL,
`ranking` int(11) DEFAULT NULL,
`review_nr` int(11) DEFAULT NULL,
`review_score` double DEFAULT NULL,
`zip` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`checkin_from` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
`checkin_to` varchar(15) COLLATE
Re: Slow query - please help
I just revised my query and now get the following output :
'1', 'PRIMARY', 'cities', 'ref', 'IDX_country_code,IDX_language_code',
'IDX_country_code', '7', 'const', '2088', '100.00', 'Using index condition;
Using where; Using filesort'
'4', 'DEPENDENT SUBQUERY', 'districts', 'ref',
'IDX_country_code,IDX_language_code', 'IDX_country_code', '7', 'const',
'121', '100.00', 'Using index condition; Using where'
'3', 'DEPENDENT SUBQUERY', 'hotels', 'ref',
'IDX_country_code,IDX_language_code', 'IDX_country_code', '7', 'const',
'9982', '100.00', 'Using index condition; Using where'
'2', 'DEPENDENT SUBQUERY', 'hotels', 'ref',
'UNQ_folder_url,IDX_country_code,IDX_enabled,IDX_language_code',
'IDX_enabled', '1', 'const', '267', '100.00', 'Using index condition; Using
where'
After doing this the query speed is acceptable.
Thanks
Neil
On Wed, Oct 5, 2011 at 3:12 AM, Johnny Withers joh...@pixelated.net wrote:
Can you post the explain extended output of your query?
Sent from my iPad
On Oct 4, 2011, at 2:45 PM, Neil Tompkins neil.tompk...@googlemail.com
wrote:
Can anyone help me ?
Begin forwarded message:
From: Tompkins Neil neil.tompk...@googlemail.com
Date: 30 September 2011 20:23:47 GMT+01:00
To: mark carson mcar...@pixie.co.za
Cc: [MySQL] mysql@lists.mysql.com
Subject: Re: Slow query - please help
I downloaded version mysql-5.6.2-m5-win32.msi and he table definitions
are below, let me know if you need any more information.
CREATE TABLE `districts` (
`district_id` int(11) NOT NULL,
`language_code` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'en',
`city_id` int(11) DEFAULT NULL,
`country_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`latitude` double DEFAULT NULL,
`longitude` double DEFAULT NULL,
`meta_title` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_keywords` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_description` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`link_text` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`folder_url` varchar(55) COLLATE utf8_unicode_ci DEFAULT NULL,
`enabled` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
`last_changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`district_id`,`language_code`),
UNIQUE KEY `UNQ_folder_url` (`folder_url`),
KEY `IDX_country_code` (`country_code`),
KEY `IDX_enabled` (`enabled`),
KEY `IDX_folder_url` (`folder_url`),
KEY `IDX_language_code` (`language_code`),
KEY `IDX_latitude` (`latitude`),
KEY `IDX_longitude` (`longitude`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `cities` (
`city_id` int(11) NOT NULL,
`language_code` char(2) COLLATE utf8_unicode_ci NOT NULL,
`country_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`nr_hotels` int(11) DEFAULT NULL,
`latitude` double DEFAULT NULL,
`longitude` double DEFAULT NULL,
`meta_title` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_keywords` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_description` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`link_text` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`folder_url` varchar(55) COLLATE utf8_unicode_ci DEFAULT NULL,
`enabled` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
`last_changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`city_id`,`language_code`),
UNIQUE KEY `UNQ_folder_url` (`folder_url`),
KEY `IDX_country_code` (`country_code`),
KEY `IDX_enabled` (`enabled`),
KEY `IDX_folder_url` (`folder_url`),
KEY `IDX_language_code` (`language_code`),
KEY `IDX_latitude` (`latitude`),
KEY `IDX_longitude` (`longitude`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `hotels` (
`hotel_id` int(11) NOT NULL,
`language_code` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'en',
`address` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`city` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`city_id` int(11) DEFAULT NULL,
`class_is_estimated` tinyint(4) DEFAULT NULL,
`class` tinyint(4) DEFAULT NULL,
`country_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`currencycode` char(3) COLLATE utf8_unicode_ci DEFAULT NULL,
`district` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`hoteltype_id` int(11) DEFAULT NULL,
`is_closed` tinyint(4) DEFAULT NULL,
`latitude` double DEFAULT NULL,
`longitude` double DEFAULT NULL,
`maxrate` double DEFAULT NULL,
`minrate` double DEFAULT NULL,
`original_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`nr_rooms` int(11) DEFAULT NULL,
`preferred` int(11) DEFAULT NULL,
`ranking` int(11) DEFAULT NULL,
`review_nr` int(11) DEFAULT NULL
Fwd: Slow query - please help
Following my mail below, if anyone can help optimise the query further that
would be a great help.
-- Forwarded message --
From: Tompkins Neil neil.tompk...@googlemail.com
Date: Wed, Oct 5, 2011 at 9:48 AM
Subject: Re: Slow query - please help
To: Johnny Withers joh...@pixelated.net
Cc: mysql@lists.mysql.com mysql@lists.mysql.com
I just revised my query and now get the following output :
'1', 'PRIMARY', 'cities', 'ref', 'IDX_country_code,IDX_language_code',
'IDX_country_code', '7', 'const', '2088', '100.00', 'Using index condition;
Using where; Using filesort'
'4', 'DEPENDENT SUBQUERY', 'districts', 'ref',
'IDX_country_code,IDX_language_code', 'IDX_country_code', '7', 'const',
'121', '100.00', 'Using index condition; Using where'
'3', 'DEPENDENT SUBQUERY', 'hotels', 'ref',
'IDX_country_code,IDX_language_code', 'IDX_country_code', '7', 'const',
'9982', '100.00', 'Using index condition; Using where'
'2', 'DEPENDENT SUBQUERY', 'hotels', 'ref',
'UNQ_folder_url,IDX_country_code,IDX_enabled,IDX_language_code',
'IDX_enabled', '1', 'const', '267', '100.00', 'Using index condition; Using
where'
After doing this the query speed is acceptable.
Thanks
Neil
On Wed, Oct 5, 2011 at 3:12 AM, Johnny Withers joh...@pixelated.net wrote:
Can you post the explain extended output of your query?
Sent from my iPad
On Oct 4, 2011, at 2:45 PM, Neil Tompkins neil.tompk...@googlemail.com
wrote:
Can anyone help me ?
Begin forwarded message:
From: Tompkins Neil neil.tompk...@googlemail.com
Date: 30 September 2011 20:23:47 GMT+01:00
To: mark carson mcar...@pixie.co.za
Cc: [MySQL] mysql@lists.mysql.com
Subject: Re: Slow query - please help
I downloaded version mysql-5.6.2-m5-win32.msi and he table definitions
are below, let me know if you need any more information.
CREATE TABLE `districts` (
`district_id` int(11) NOT NULL,
`language_code` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'en',
`city_id` int(11) DEFAULT NULL,
`country_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`latitude` double DEFAULT NULL,
`longitude` double DEFAULT NULL,
`meta_title` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_keywords` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_description` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`link_text` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`folder_url` varchar(55) COLLATE utf8_unicode_ci DEFAULT NULL,
`enabled` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
`last_changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`district_id`,`language_code`),
UNIQUE KEY `UNQ_folder_url` (`folder_url`),
KEY `IDX_country_code` (`country_code`),
KEY `IDX_enabled` (`enabled`),
KEY `IDX_folder_url` (`folder_url`),
KEY `IDX_language_code` (`language_code`),
KEY `IDX_latitude` (`latitude`),
KEY `IDX_longitude` (`longitude`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `cities` (
`city_id` int(11) NOT NULL,
`language_code` char(2) COLLATE utf8_unicode_ci NOT NULL,
`country_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`nr_hotels` int(11) DEFAULT NULL,
`latitude` double DEFAULT NULL,
`longitude` double DEFAULT NULL,
`meta_title` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_keywords` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_description` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`link_text` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`folder_url` varchar(55) COLLATE utf8_unicode_ci DEFAULT NULL,
`enabled` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
`last_changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`city_id`,`language_code`),
UNIQUE KEY `UNQ_folder_url` (`folder_url`),
KEY `IDX_country_code` (`country_code`),
KEY `IDX_enabled` (`enabled`),
KEY `IDX_folder_url` (`folder_url`),
KEY `IDX_language_code` (`language_code`),
KEY `IDX_latitude` (`latitude`),
KEY `IDX_longitude` (`longitude`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `hotels` (
`hotel_id` int(11) NOT NULL,
`language_code` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'en',
`address` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`city` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`city_id` int(11) DEFAULT NULL,
`class_is_estimated` tinyint(4) DEFAULT NULL,
`class` tinyint(4) DEFAULT NULL,
`country_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`currencycode` char(3) COLLATE utf8_unicode_ci DEFAULT NULL,
`district` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`hoteltype_id` int(11) DEFAULT NULL,
`is_closed` tinyint(4) DEFAULT NULL,
`latitude` double DEFAULT NULL,
`longitude` double
Re: Slow query - please help
Try adding an index on cities.name, it may prevent the file sort. What was the
original query time and what is it now?
Sent from my iPad
On Oct 5, 2011, at 4:01 AM, Tompkins Neil neil.tompk...@googlemail.com wrote:
Following my mail below, if anyone can help optimise the query further that
would be a great help.
-- Forwarded message --
From: Tompkins Neil neil.tompk...@googlemail.com
Date: Wed, Oct 5, 2011 at 9:48 AM
Subject: Re: Slow query - please help
To: Johnny Withers joh...@pixelated.net
Cc: mysql@lists.mysql.com mysql@lists.mysql.com
I just revised my query and now get the following output :
'1', 'PRIMARY', 'cities', 'ref', 'IDX_country_code,IDX_language_code',
'IDX_country_code', '7', 'const', '2088', '100.00', 'Using index condition;
Using where; Using filesort'
'4', 'DEPENDENT SUBQUERY', 'districts', 'ref',
'IDX_country_code,IDX_language_code', 'IDX_country_code', '7', 'const',
'121', '100.00', 'Using index condition; Using where'
'3', 'DEPENDENT SUBQUERY', 'hotels', 'ref',
'IDX_country_code,IDX_language_code', 'IDX_country_code', '7', 'const',
'9982', '100.00', 'Using index condition; Using where'
'2', 'DEPENDENT SUBQUERY', 'hotels', 'ref',
'UNQ_folder_url,IDX_country_code,IDX_enabled,IDX_language_code',
'IDX_enabled', '1', 'const', '267', '100.00', 'Using index condition; Using
where'
After doing this the query speed is acceptable.
Thanks
Neil
On Wed, Oct 5, 2011 at 3:12 AM, Johnny Withers joh...@pixelated.net wrote:
Can you post the explain extended output of your query?
Sent from my iPad
On Oct 4, 2011, at 2:45 PM, Neil Tompkins neil.tompk...@googlemail.com
wrote:
Can anyone help me ?
Begin forwarded message:
From: Tompkins Neil neil.tompk...@googlemail.com
Date: 30 September 2011 20:23:47 GMT+01:00
To: mark carson mcar...@pixie.co.za
Cc: [MySQL] mysql@lists.mysql.com
Subject: Re: Slow query - please help
I downloaded version mysql-5.6.2-m5-win32.msi and he table definitions
are below, let me know if you need any more information.
CREATE TABLE `districts` (
`district_id` int(11) NOT NULL,
`language_code` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'en',
`city_id` int(11) DEFAULT NULL,
`country_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`latitude` double DEFAULT NULL,
`longitude` double DEFAULT NULL,
`meta_title` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_keywords` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_description` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`link_text` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`folder_url` varchar(55) COLLATE utf8_unicode_ci DEFAULT NULL,
`enabled` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
`last_changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`district_id`,`language_code`),
UNIQUE KEY `UNQ_folder_url` (`folder_url`),
KEY `IDX_country_code` (`country_code`),
KEY `IDX_enabled` (`enabled`),
KEY `IDX_folder_url` (`folder_url`),
KEY `IDX_language_code` (`language_code`),
KEY `IDX_latitude` (`latitude`),
KEY `IDX_longitude` (`longitude`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `cities` (
`city_id` int(11) NOT NULL,
`language_code` char(2) COLLATE utf8_unicode_ci NOT NULL,
`country_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`nr_hotels` int(11) DEFAULT NULL,
`latitude` double DEFAULT NULL,
`longitude` double DEFAULT NULL,
`meta_title` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_keywords` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_description` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`link_text` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`folder_url` varchar(55) COLLATE utf8_unicode_ci DEFAULT NULL,
`enabled` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
`last_changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`city_id`,`language_code`),
UNIQUE KEY `UNQ_folder_url` (`folder_url`),
KEY `IDX_country_code` (`country_code`),
KEY `IDX_enabled` (`enabled`),
KEY `IDX_folder_url` (`folder_url`),
KEY `IDX_language_code` (`language_code`),
KEY `IDX_latitude` (`latitude`),
KEY `IDX_longitude` (`longitude`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `hotels` (
`hotel_id` int(11) NOT NULL,
`language_code` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'en',
`address` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`city` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`city_id` int(11) DEFAULT NULL,
`class_is_estimated` tinyint(4) DEFAULT NULL,
`class` tinyint(4) DEFAULT NULL,
`country_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`currencycode` char(3) COLLATE utf8_unicode_ci DEFAULT NULL,
`district` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL
Re: Slow query - please help
I already have a FULLTEXT index on cities.name ? Do I still need to add a
normal index ?
On Wed, Oct 5, 2011 at 1:34 PM, Johnny Withers joh...@pixelated.net wrote:
Try adding an index on cities.name, it may prevent the file sort. What was
the original query time and what is it now?
Sent from my iPad
On Oct 5, 2011, at 4:01 AM, Tompkins Neil neil.tompk...@googlemail.com
wrote:
Following my mail below, if anyone can help optimise the query further
that
would be a great help.
-- Forwarded message --
From: Tompkins Neil neil.tompk...@googlemail.com
Date: Wed, Oct 5, 2011 at 9:48 AM
Subject: Re: Slow query - please help
To: Johnny Withers joh...@pixelated.net
Cc: mysql@lists.mysql.com mysql@lists.mysql.com
I just revised my query and now get the following output :
'1', 'PRIMARY', 'cities', 'ref', 'IDX_country_code,IDX_language_code',
'IDX_country_code', '7', 'const', '2088', '100.00', 'Using index
condition;
Using where; Using filesort'
'4', 'DEPENDENT SUBQUERY', 'districts', 'ref',
'IDX_country_code,IDX_language_code', 'IDX_country_code', '7', 'const',
'121', '100.00', 'Using index condition; Using where'
'3', 'DEPENDENT SUBQUERY', 'hotels', 'ref',
'IDX_country_code,IDX_language_code', 'IDX_country_code', '7', 'const',
'9982', '100.00', 'Using index condition; Using where'
'2', 'DEPENDENT SUBQUERY', 'hotels', 'ref',
'UNQ_folder_url,IDX_country_code,IDX_enabled,IDX_language_code',
'IDX_enabled', '1', 'const', '267', '100.00', 'Using index condition;
Using
where'
After doing this the query speed is acceptable.
Thanks
Neil
On Wed, Oct 5, 2011 at 3:12 AM, Johnny Withers joh...@pixelated.net
wrote:
Can you post the explain extended output of your query?
Sent from my iPad
On Oct 4, 2011, at 2:45 PM, Neil Tompkins neil.tompk...@googlemail.com
wrote:
Can anyone help me ?
Begin forwarded message:
From: Tompkins Neil neil.tompk...@googlemail.com
Date: 30 September 2011 20:23:47 GMT+01:00
To: mark carson mcar...@pixie.co.za
Cc: [MySQL] mysql@lists.mysql.com
Subject: Re: Slow query - please help
I downloaded version mysql-5.6.2-m5-win32.msi and he table definitions
are below, let me know if you need any more information.
CREATE TABLE `districts` (
`district_id` int(11) NOT NULL,
`language_code` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'en',
`city_id` int(11) DEFAULT NULL,
`country_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`latitude` double DEFAULT NULL,
`longitude` double DEFAULT NULL,
`meta_title` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_keywords` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_description` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`link_text` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`folder_url` varchar(55) COLLATE utf8_unicode_ci DEFAULT NULL,
`enabled` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
`last_changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`district_id`,`language_code`),
UNIQUE KEY `UNQ_folder_url` (`folder_url`),
KEY `IDX_country_code` (`country_code`),
KEY `IDX_enabled` (`enabled`),
KEY `IDX_folder_url` (`folder_url`),
KEY `IDX_language_code` (`language_code`),
KEY `IDX_latitude` (`latitude`),
KEY `IDX_longitude` (`longitude`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `cities` (
`city_id` int(11) NOT NULL,
`language_code` char(2) COLLATE utf8_unicode_ci NOT NULL,
`country_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`nr_hotels` int(11) DEFAULT NULL,
`latitude` double DEFAULT NULL,
`longitude` double DEFAULT NULL,
`meta_title` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_keywords` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_description` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`link_text` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`folder_url` varchar(55) COLLATE utf8_unicode_ci DEFAULT NULL,
`enabled` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
`last_changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`city_id`,`language_code`),
UNIQUE KEY `UNQ_folder_url` (`folder_url`),
KEY `IDX_country_code` (`country_code`),
KEY `IDX_enabled` (`enabled`),
KEY `IDX_folder_url` (`folder_url`),
KEY `IDX_language_code` (`language_code`),
KEY `IDX_latitude` (`latitude`),
KEY `IDX_longitude` (`longitude`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `hotels` (
`hotel_id` int(11) NOT NULL,
`language_code` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'en',
`address` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`city` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`city_id` int(11) DEFAULT NULL
Fwd: Slow query - please help
Can anyone help me ?
Begin forwarded message:
From: Tompkins Neil neil.tompk...@googlemail.com
Date: 30 September 2011 20:23:47 GMT+01:00
To: mark carson mcar...@pixie.co.za
Cc: [MySQL] mysql@lists.mysql.com
Subject: Re: Slow query - please help
I downloaded version mysql-5.6.2-m5-win32.msi and he table definitions are
below, let me know if you need any more information.
CREATE TABLE `districts` (
`district_id` int(11) NOT NULL,
`language_code` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'en',
`city_id` int(11) DEFAULT NULL,
`country_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`latitude` double DEFAULT NULL,
`longitude` double DEFAULT NULL,
`meta_title` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_keywords` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_description` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`link_text` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`folder_url` varchar(55) COLLATE utf8_unicode_ci DEFAULT NULL,
`enabled` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
`last_changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`district_id`,`language_code`),
UNIQUE KEY `UNQ_folder_url` (`folder_url`),
KEY `IDX_country_code` (`country_code`),
KEY `IDX_enabled` (`enabled`),
KEY `IDX_folder_url` (`folder_url`),
KEY `IDX_language_code` (`language_code`),
KEY `IDX_latitude` (`latitude`),
KEY `IDX_longitude` (`longitude`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `cities` (
`city_id` int(11) NOT NULL,
`language_code` char(2) COLLATE utf8_unicode_ci NOT NULL,
`country_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`nr_hotels` int(11) DEFAULT NULL,
`latitude` double DEFAULT NULL,
`longitude` double DEFAULT NULL,
`meta_title` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_keywords` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_description` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`link_text` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`folder_url` varchar(55) COLLATE utf8_unicode_ci DEFAULT NULL,
`enabled` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
`last_changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`city_id`,`language_code`),
UNIQUE KEY `UNQ_folder_url` (`folder_url`),
KEY `IDX_country_code` (`country_code`),
KEY `IDX_enabled` (`enabled`),
KEY `IDX_folder_url` (`folder_url`),
KEY `IDX_language_code` (`language_code`),
KEY `IDX_latitude` (`latitude`),
KEY `IDX_longitude` (`longitude`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `hotels` (
`hotel_id` int(11) NOT NULL,
`language_code` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'en',
`address` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`city` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`city_id` int(11) DEFAULT NULL,
`class_is_estimated` tinyint(4) DEFAULT NULL,
`class` tinyint(4) DEFAULT NULL,
`country_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`currencycode` char(3) COLLATE utf8_unicode_ci DEFAULT NULL,
`district` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`hoteltype_id` int(11) DEFAULT NULL,
`is_closed` tinyint(4) DEFAULT NULL,
`latitude` double DEFAULT NULL,
`longitude` double DEFAULT NULL,
`maxrate` double DEFAULT NULL,
`minrate` double DEFAULT NULL,
`original_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`nr_rooms` int(11) DEFAULT NULL,
`preferred` int(11) DEFAULT NULL,
`ranking` int(11) DEFAULT NULL,
`review_nr` int(11) DEFAULT NULL,
`review_score` double DEFAULT NULL,
`zip` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`checkin_from` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
`checkin_to` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
`checkout_from` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
`checkout_to` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_title` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_keywords` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_description` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`link_text` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`folder_url` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`short_profile` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`enabled` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
`last_changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`hotel_id`,`language_code`),
UNIQUE KEY `UNQ_folder_url` (`folder_url`),
KEY `IDX_country_code` (`country_code`),
KEY `IDX_enabled` (`enabled
Re: Slow query - please help
Can you post the explain extended output of your query?
Sent from my iPad
On Oct 4, 2011, at 2:45 PM, Neil Tompkins neil.tompk...@googlemail.com wrote:
Can anyone help me ?
Begin forwarded message:
From: Tompkins Neil neil.tompk...@googlemail.com
Date: 30 September 2011 20:23:47 GMT+01:00
To: mark carson mcar...@pixie.co.za
Cc: [MySQL] mysql@lists.mysql.com
Subject: Re: Slow query - please help
I downloaded version mysql-5.6.2-m5-win32.msi and he table definitions are
below, let me know if you need any more information.
CREATE TABLE `districts` (
`district_id` int(11) NOT NULL,
`language_code` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'en',
`city_id` int(11) DEFAULT NULL,
`country_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`latitude` double DEFAULT NULL,
`longitude` double DEFAULT NULL,
`meta_title` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_keywords` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_description` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`link_text` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`folder_url` varchar(55) COLLATE utf8_unicode_ci DEFAULT NULL,
`enabled` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
`last_changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`district_id`,`language_code`),
UNIQUE KEY `UNQ_folder_url` (`folder_url`),
KEY `IDX_country_code` (`country_code`),
KEY `IDX_enabled` (`enabled`),
KEY `IDX_folder_url` (`folder_url`),
KEY `IDX_language_code` (`language_code`),
KEY `IDX_latitude` (`latitude`),
KEY `IDX_longitude` (`longitude`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `cities` (
`city_id` int(11) NOT NULL,
`language_code` char(2) COLLATE utf8_unicode_ci NOT NULL,
`country_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`nr_hotels` int(11) DEFAULT NULL,
`latitude` double DEFAULT NULL,
`longitude` double DEFAULT NULL,
`meta_title` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_keywords` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_description` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`link_text` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`folder_url` varchar(55) COLLATE utf8_unicode_ci DEFAULT NULL,
`enabled` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
`last_changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`city_id`,`language_code`),
UNIQUE KEY `UNQ_folder_url` (`folder_url`),
KEY `IDX_country_code` (`country_code`),
KEY `IDX_enabled` (`enabled`),
KEY `IDX_folder_url` (`folder_url`),
KEY `IDX_language_code` (`language_code`),
KEY `IDX_latitude` (`latitude`),
KEY `IDX_longitude` (`longitude`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `hotels` (
`hotel_id` int(11) NOT NULL,
`language_code` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'en',
`address` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`city` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`city_id` int(11) DEFAULT NULL,
`class_is_estimated` tinyint(4) DEFAULT NULL,
`class` tinyint(4) DEFAULT NULL,
`country_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`currencycode` char(3) COLLATE utf8_unicode_ci DEFAULT NULL,
`district` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`hoteltype_id` int(11) DEFAULT NULL,
`is_closed` tinyint(4) DEFAULT NULL,
`latitude` double DEFAULT NULL,
`longitude` double DEFAULT NULL,
`maxrate` double DEFAULT NULL,
`minrate` double DEFAULT NULL,
`original_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`nr_rooms` int(11) DEFAULT NULL,
`preferred` int(11) DEFAULT NULL,
`ranking` int(11) DEFAULT NULL,
`review_nr` int(11) DEFAULT NULL,
`review_score` double DEFAULT NULL,
`zip` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`checkin_from` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
`checkin_to` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
`checkout_from` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
`checkout_to` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_title` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_keywords` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_description` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`link_text` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`folder_url` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`short_profile` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`enabled` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
`last_changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`hotel_id`,`language_code`),
UNIQUE KEY `UNQ_folder_url` (`folder_url
Slow query - please help
Hi I've the following query : SELECT city_id, name, meta_title, meta_description, meta_keywords, country_code, link_text, folder_url, enabled, last_changed, nr_hotels, (SELECT COUNT(hotels.hotel_id) FROM hotels WHERE hotels.city_id = cities.city_id AND hotels.country_code = 'gb' AND hotels.enabled = 'Y' AND hotels.folder_url IS NOT NULL AND hotels.language_code = 'en') AS hotel_count, (SELECT COUNT(hotels.hotel_id) FROM hotels WHERE hotels.city_id = cities.city_id AND hotels.country_code = 'gb' AND hotels.language_code = 'en') AS available_hotel_count, (SELECT COUNT(districts.city_id) FROM districts WHERE districts.city_id = cities.city_id AND districts.language_code = 'en' AND districts.country_code = 'gb') AS district_count FROM cities WHERE language_code = 'en' AND country_code = 'gb' ORDER BY cities.name ASC , cities.city_id ASC Previously the table format was Innodb with foreign keys and the query was pretty much instant. Now I've changed the table format to MyISAM and obviously removed the foreign keys and the query takes forever to execute using the same data. Can anyone help and tell me where I've gone wrong. Thanks Neil
Re: Slow query - please help
a response Mark On 2011/09/30 17:49, Tompkins Neil wrote: Hi I've the following query : SELECT city_id, name, meta_title, meta_description, meta_keywords, country_code, link_text, folder_url, enabled, last_changed, nr_hotels, (SELECT COUNT(hotels.hotel_id) FROM hotels WHERE hotels.city_id = cities.city_id AND hotels.country_code = 'gb' AND hotels.enabled = 'Y' AND hotels.folder_url IS NOT NULL AND hotels.language_code = 'en') AS hotel_count, (SELECT COUNT(hotels.hotel_id) FROM hotels WHERE hotels.city_id = cities.city_id AND hotels.country_code = 'gb' AND hotels.language_code = 'en') AS available_hotel_count, (SELECT COUNT(districts.city_id) FROM districts WHERE districts.city_id = cities.city_id AND districts.language_code = 'en' AND districts.country_code = 'gb') AS district_count FROM cities WHERE language_code = 'en' AND country_code = 'gb' ORDER BY cities.name ASC , cities.city_id ASC Previously the table format was Innodb with foreign keys and the query was pretty much instant. Now I've changed the table format to MyISAM and obviously removed the foreign keys and the query takes forever to execute using the same data. Can anyone help and tell me where I've gone wrong. Thanks Neil -- Mark Carson Managing Integrated Product Intelligence CC (CK95/35630/23) EMail : mcar...@ipi.co.za/ (secondary:mcar...@pixie.co.za) Physical Address : 34 Spanish Galliard, Mooikloof, Pretoria, South Africa snailmail : P.O. Box 36095 Menlo Park 0102, South Africa Tel. +27 12 996 1193/1815 Fax : +27 86 672 7012 Cell : +27 83 260 8515 This e-mail may contain PRIVILEGED AND/OR CONFIDENTIAL INFORMATION intended only for use of the addressee. If you are not the addressee, or the person responsible for delivering it to the person addressed, you may not copy or deliver this to anyone else. If you received this e-mail by mistake, please do not make use of it, nor disclose it's contents to anyone. Thank you for notifying us immediately by return e-mail or telephone. INFORMATION PROVIDED IN THIS ELECTRONIC MAIL IS PROVIDED AS IS WITHOUT WARRANTY REPRESENTATION OR CONDITION OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO CONDITIONS OR OTHER TERMS OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. THE USER ASSUMES THE ENTIRE RISK AS TO THE ACCURACY AND THE USE OF THIS DOCUMENT.
Re: SPAM: please remove gregario.es from the list
hi The message sid this : Dear, inform you that your email _ * has not been delivered to the recipient * _ because there is no mailbox in the domain A greeting. I have many problems sending mail , any problem or any ideas? 2011/4/2 Reindl Harald h.rei...@thelounge.net Can anybody remove this (i think spanish) autoreply-spammer from the list nbecause the only thing i understand is some price in € and that our spm-firewall is tagging it Original-Nachricht Betreff:[SPAM] Re: Re: Can't connect to MySQL server on '127.0.0.1' (111) Datum: 2 Apr 2011 17:17:20 +0200 Von:no-re...@gregario.es An: h.rei...@thelounge.net Estimad@, le informamos que su mensaje de correo electrónico _*no ha sido entregado al destinatario*_ ya que no existe el buzón de correo en el dominio Un saludo. Consigue gratis tu bono de 30 € para utilizar con Google Adwords http://www.googld.es/ Te regalamos una camiseta personalizada con tu foto http://clk.tradedoubler.com/click?p(22814)a(1548325)g(1076669)url(http://www.vistaprint.es/male-female-tshirts.aspx) -- Mit forever My Blog http://www.redcloverbi.wordpress.com My Faborite Webhttp://ocw.mit.edu/OcwWeb/Electrical-Engineering-and-Computer-Science/index.htm http://www.technologyreview.com/
SPAM: please remove gregario.es from the list
Can anybody remove this (i think spanish) autoreply-spammer from the list nbecause the only thing i understand is some price in € and that our spm-firewall is tagging it Original-Nachricht Betreff:[SPAM] Re: Re: Can't connect to MySQL server on '127.0.0.1' (111) Datum: 2 Apr 2011 17:17:20 +0200 Von:no-re...@gregario.es An: h.rei...@thelounge.net Estimad@, le informamos que su mensaje de correo electrónico _*no ha sido entregado al destinatario*_ ya que no existe el buzón de correo en el dominio Un saludo. Consigue gratis tu bono de 30 € para utilizar con Google Adwords http://www.googld.es/ Te regalamos una camiseta personalizada con tu foto http://clk.tradedoubler.com/click?p(22814)a(1548325)g(1076669)url(http://www.vistaprint.es/male-female-tshirts.aspx) signature.asc Description: OpenPGP digital signature
Database Structure Opinions Please
I am at the planning stage of building a database that will have a fairly large amount of different information. It is a DB for law firms, so it will have all of their contact information including a county. Also an individual lawyer will be listed. Add to that 2 description areas (one for firm, one for lawyer), then there will be individual check boxes for areas of practice, I am thinking about 10. There will also be a need to store articles that will be written by these attorneys. I am thinking one main table with the firm info, a separate table for atty info, third for areas of practice and fourth for articles? I would enjoy hearing opinions on this. -- Gary __ Information from ESET Smart Security, version of virus signature database 5868 (20110212) __ The message was checked by ESET Smart Security. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
On 21.12.2010 11:27 CE(S)T, 杨涛涛 wrote: Maybe you should give a password to MySQL's root. And I think this note will disappear. I would be extremely dumb not to set any password on MySQL's root account at all! Of course (I hope I made that clear before) a password has been set while installing the MySQL package. The point is that this message is plain wrong and should be removed altogether until it works as it claims. -- Yves Goergen LonelyPixel nospam.l...@unclassified.de Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
Maybe you should give a password to MySQL's root. And I think this note will disappear. David Yeung, In China, Beijing. My First Blog:http://yueliangdao0608.cublog.cn My Second Blog:http://yueliangdao0608.blog.51cto.com My Msn: yueliangdao0...@gmail.com 2010/12/17 Yves Goergen nospam.l...@unclassified.de On 14.12.2010 20:36 CE(S)T, Alejandro Bednarik wrote: Are you using ubuntu deb's or mysql bin? Do you get that message when the service start? Look init script to see what it does. I get the message when MySQL is started. I've installed the Ubuntu standard package mysql-server-5.1. It has already asked for a root password during package configuration and I did enter some password there. I just tried to grep my whole filesystem for parts of this message but either grep failed allocating memory (there's plenty left!) or it didn't find the text in a file I could start something with. So I still don't know where the message comes from! -- Yves Goergen LonelyPixel nospam.l...@unclassified.de Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com
Re: PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
On 14.12.2010 20:36 CE(S)T, Alejandro Bednarik wrote: Are you using ubuntu deb's or mysql bin? Do you get that message when the service start? Look init script to see what it does. I get the message when MySQL is started. I've installed the Ubuntu standard package mysql-server-5.1. It has already asked for a root password during package configuration and I did enter some password there. I just tried to grep my whole filesystem for parts of this message but either grep failed allocating memory (there's plenty left!) or it didn't find the text in a file I could start something with. So I still don't know where the message comes from! -- Yves Goergen LonelyPixel nospam.l...@unclassified.de Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
On 13.12.2010 15:26 CE(S)T, who.cat wrote: try /mysql_bin_path/mysql_secure_installation ! Hope it helpfu! I already followed that path and it doesn't quite help. I've done the checks that the install script does and my installation is secure by those means. But I don't want to install things. The server is already up and running in production. I only want to get rid of that whole load of messages posted to syslog. I don't know where they come from to do further analysis. -- Yves Goergen LonelyPixel nospam.l...@unclassified.de Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
Hi, I find the following line in my syslog events: Daemon Error mysqld PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! followed by a whole lot of notes, advice and web links. I do have set a custom password for root, root is not accessible from remote, the anonymous user does not exist and a database named test also does not exist. Yet still I see this message when starting the MySQL server. How can I get rid of it? MySQL 5.1 on Ubuntu 10.04 -- Yves Goergen LonelyPixel nospam.l...@unclassified.de Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Query help please
Hi all, I've the following query : SELECT fixtures_results.seasons_id , home_teams_id AS teams_id , 1 AS home ,0 AS away , (SELECT SUM(goals) FROM players_appearances WHERE fixtures_results.fixtures_results_id = players_appearances.fixtures_results_id AND players_appearances.teams_id = home_teams_id) AS home_goals_aa, IF(home_goals away_goals, 1, 0) AS won_home , IF(home_goals = away_goals, 1, 0) AS drawn_home , IF(home_goals away_goals, 1, 0) AS lost_home , home_goals AS scored_home , away_goals AS conceded_home , 0 AS won_away , 0 AS drawn_away , 0 AS lost_away , 0 AS scored_away , 0 AS conceded_away FROM fixtures_results WHERE fixtures_results.competitions_id = 1 AND fixtures_results.seasons_id = 1 AND fixtures_results.status = 'approved' Basically I have a table called player_appearances which contains a SUM of goals for each fixture for the home and away team. How can I use this SUM called home_goals_aa, in my logic like IF(home_goals away_goals, 1, 0) AS won_home , IF(home_goals = away_goals, 1, 0) AS drawn_home , IF(home_goals away_goals, 1, 0) AS lost_home , Cheers Neil
Fwd: Query help please
I wondered if anyone can help me ? Do you need any further information ? Cheers Neil -- Forwarded message -- From: Tompkins Neil neil.tompk...@googlemail.com Date: Thu, Sep 23, 2010 at 9:49 AM Subject: Query help please To: [MySQL] mysql@lists.mysql.com Hi all, I've the following query : SELECT fixtures_results.seasons_id , home_teams_id AS teams_id , 1 AS home ,0 AS away , (SELECT SUM(goals) FROM players_appearances WHERE fixtures_results.fixtures_results_id = players_appearances.fixtures_results_id AND players_appearances.teams_id = home_teams_id) AS home_goals_aa, IF(home_goals away_goals, 1, 0) AS won_home , IF(home_goals = away_goals, 1, 0) AS drawn_home , IF(home_goals away_goals, 1, 0) AS lost_home , home_goals AS scored_home , away_goals AS conceded_home , 0 AS won_away , 0 AS drawn_away , 0 AS lost_away , 0 AS scored_away , 0 AS conceded_away FROM fixtures_results WHERE fixtures_results.competitions_id = 1 AND fixtures_results.seasons_id = 1 AND fixtures_results.status = 'approved' Basically I have a table called player_appearances which contains a SUM of goals for each fixture for the home and away team. How can I use this SUM called home_goals_aa, in my logic like IF(home_goals away_goals, 1, 0) AS won_home , IF(home_goals = away_goals, 1, 0) AS drawn_home , IF(home_goals away_goals, 1, 0) AS lost_home , Cheers Neil
Re: Indexes larger than RAM (was: Do you know who can answer this question I posted yesterday please?)
Hi! I am no InnoDB and tuning expert, so I had intended to stay away from this question. Ok, I'll give some general remarks: Nunzio Daveri schrieb: [[...]] All, I was running slamdb against one of our QA boxes and noticed that the innodb database is 190Gb in size BUT the worrying issue is that the indexes are 30GB in size!!! When I hit this server hard, it tanks on memory but still performs, slower of course ;-) Having indexes which are larger than RAM is (in itself) not critical. IMO, it becomes bad only when accesses to these indexes are spread so wide that even the index pages become subject to frequent IO. Any suggestions on what I should do? I am thinking of doing one of these: Whether any action is needed, and which, depends on the problem you experience: - If the system as a whole (both CPU and disk) has a significant idle percentage, it isn't yet maxed out, and I don't expect that adding resources would improve performance significantly. - If your CPUs have significant waiting for IO percentage, then data accesses need speedup. This could be done by faster disks, but I would expect more results from adding RAM for larger caches. This holds especially if your disk throughput is close to the possible maximum. (Assuming your bulk work is read/select. If it is insert/update, then *removing* indexes might reduce the workload, as there are fewer indexes to maintain.) - If your CPUs are busy, then I don't expect any increase of caching would help. 1. Remove all queries, run for a few days, look at the slow query logs and then find those queries that really need them and index those specificially for performance. Makes sense (only) if you have indexes which aren't really helpful for accesses, so they just add maintenance load. If you do few inserts/updates, an unused index should be paged out and not do much harm. Comes with the cost of reduced performance during that test time, and the need to rebuild the essential indexes afterwards. Has the benefit of getting rid of unused indexes (which just cause maintenance load). 2. Split the single server into two servers both with 16 gb and 2 quad core cpu's. One master the other a slave. Makes sense if your CPUs are busy, *and* you can distribute the read accesses to the two servers (= most accesses are select). If most load is insert/update, I don't expect a real improvement. Biggest cost in hardware and admin effort, so I would do this only after a decent analysis. OTOH, it gives you some (hardware) fault tolerance, this could be an important argument depending on your requirements. 3. Just add another 16gb (32GB total) and that should take care of the indexing issue. Makes sense if the disks are the bottleneck (CPUs are in waiting for IO), so that larger caches will avoid disk accesses. Assumes your machine supports that amount of RAM (many mainboards have a limit at 16 GB, AIUI). Anyone had this problem before??? Oh this is a single box, 100% mysql only and it talks to 3 front end iPlanet web servers that hit it with a few hundread queries per second. For a specific answer, the distribution of accesses between read and write is needed, as well as information which resource is close to the limit. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com (+49 30) 417 01 487 ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Indexes larger than RAM (was: Do you know who can answer this question I posted yesterday please?)
Thanks again :-) Nunzio From: Joerg Bruehe joerg.bru...@oracle.com To: Nunzio Daveri nunziodav...@yahoo.com; mysQL General List mysql@lists.mysql.com Sent: Fri, July 30, 2010 1:31:54 PM Subject: Re: Indexes larger than RAM (was: Do you know who can answer this question I posted yesterday please?) Hi! I am no InnoDB and tuning expert, so I had intended to stay away from this question. Ok, I'll give some general remarks: Nunzio Daveri schrieb: [[...]] All, I was running slamdb against one of our QA boxes and noticed that the innodb database is 190Gb in size BUT the worrying issue is that the indexes are 30GB in size!!! When I hit this server hard, it tanks on memory but still performs, slower of course ;-) Having indexes which are larger than RAM is (in itself) not critical. IMO, it becomes bad only when accesses to these indexes are spread so wide that even the index pages become subject to frequent IO. Any suggestions on what I should do? I am thinking of doing one of these: Whether any action is needed, and which, depends on the problem you experience: - If the system as a whole (both CPU and disk) has a significant idle percentage, it isn't yet maxed out, and I don't expect that adding resources would improve performance significantly. - If your CPUs have significant waiting for IO percentage, then data accesses need speedup. This could be done by faster disks, but I would expect more results from adding RAM for larger caches. This holds especially if your disk throughput is close to the possible maximum. (Assuming your bulk work is read/select. If it is insert/update, then *removing* indexes might reduce the workload, as there are fewer indexes to maintain.) - If your CPUs are busy, then I don't expect any increase of caching would help. 1. Remove all queries, run for a few days, look at the slow query logs and then find those queries that really need them and index those specificially for performance. Makes sense (only) if you have indexes which aren't really helpful for accesses, so they just add maintenance load. If you do few inserts/updates, an unused index should be paged out and not do much harm. Comes with the cost of reduced performance during that test time, and the need to rebuild the essential indexes afterwards. Has the benefit of getting rid of unused indexes (which just cause maintenance load). 2. Split the single server into two servers both with 16 gb and 2 quad core cpu's. One master the other a slave. Makes sense if your CPUs are busy, *and* you can distribute the read accesses to the two servers (= most accesses are select). If most load is insert/update, I don't expect a real improvement. Biggest cost in hardware and admin effort, so I would do this only after a decent analysis. OTOH, it gives you some (hardware) fault tolerance, this could be an important argument depending on your requirements. 3. Just add another 16gb (32GB total) and that should take care of the indexing issue. Makes sense if the disks are the bottleneck (CPUs are in waiting for IO), so that larger caches will avoid disk accesses. Assumes your machine supports that amount of RAM (many mainboards have a limit at 16 GB, AIUI). Anyone had this problem before??? Oh this is a single box, 100% mysql only and it talks to 3 front end iPlanet web servers that hit it with a few hundread queries per second. For a specific answer, the distribution of accesses between read and write is needed, as well as information which resource is close to the limit. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com (+49 30) 417 01 487 ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603
Re: Indexes larger than RAM (was: Do you know who can answer this question I posted yesterday please?)
Nunzio Daveri, Joerg Bruehe gave you a lot of good tips to try and speed things up. A few hundred queries per second seem to be a relatively small number to cause the server to crawl. I don't have the rest of your thread, but can you publish some of the slow queries (see Slow Query Log) and the table structure? Mike At 01:31 PM 7/30/2010, you wrote: Hi! I am no InnoDB and tuning expert, so I had intended to stay away from this question. Ok, I'll give some general remarks: Nunzio Daveri schrieb: [[...]] All, I was running slamdb against one of our QA boxes and noticed that the innodb database is 190Gb in size BUT the worrying issue is that the indexes are 30GB in size!!! When I hit this server hard, it tanks on memory but still performs, slower of course ;-) Having indexes which are larger than RAM is (in itself) not critical. IMO, it becomes bad only when accesses to these indexes are spread so wide that even the index pages become subject to frequent IO. Any suggestions on what I should do? I am thinking of doing one of these: Whether any action is needed, and which, depends on the problem you experience: - If the system as a whole (both CPU and disk) has a significant idle percentage, it isn't yet maxed out, and I don't expect that adding resources would improve performance significantly. - If your CPUs have significant waiting for IO percentage, then data accesses need speedup. This could be done by faster disks, but I would expect more results from adding RAM for larger caches. This holds especially if your disk throughput is close to the possible maximum. (Assuming your bulk work is read/select. If it is insert/update, then *removing* indexes might reduce the workload, as there are fewer indexes to maintain.) - If your CPUs are busy, then I don't expect any increase of caching would help. 1. Remove all queries, run for a few days, look at the slow query logs and then find those queries that really need them and index those specificially for performance. Makes sense (only) if you have indexes which aren't really helpful for accesses, so they just add maintenance load. If you do few inserts/updates, an unused index should be paged out and not do much harm. Comes with the cost of reduced performance during that test time, and the need to rebuild the essential indexes afterwards. Has the benefit of getting rid of unused indexes (which just cause maintenance load). 2. Split the single server into two servers both with 16 gb and 2 quad core cpu's. One master the other a slave. Makes sense if your CPUs are busy, *and* you can distribute the read accesses to the two servers (= most accesses are select). If most load is insert/update, I don't expect a real improvement. Biggest cost in hardware and admin effort, so I would do this only after a decent analysis. OTOH, it gives you some (hardware) fault tolerance, this could be an important argument depending on your requirements. 3. Just add another 16gb (32GB total) and that should take care of the indexing issue. Makes sense if the disks are the bottleneck (CPUs are in waiting for IO), so that larger caches will avoid disk accesses. Assumes your machine supports that amount of RAM (many mainboards have a limit at 16 GB, AIUI). Anyone had this problem before??? Oh this is a single box, 100% mysql only and it talks to 3 front end iPlanet web servers that hit it with a few hundread queries per second. For a specific answer, the distribution of accesses between read and write is needed, as well as information which resource is close to the limit. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com (+49 30) 417 01 487 ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: test - please ignore
i said: ignore! But did it work? ;-) With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database 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/mysql?unsub=arch...@jab.org
test - please ignore
i said: ignore! -- hartmut -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: DBA questions to ask WAS: How to corrupt a database please???
-Original Message- From: Nurudin Javeri [mailto:nsjav...@idh.com] Sent: Sunday, April 18, 2010 9:25 AM To: mysql@lists.mysql.com Subject: How to corrupt a database please??? Hi all, I am hiring a few new junior DBA's and I want to put them thru a simple db repair training. Does anyone know how I can deliberately corrupt a MyISAM and InnoDB database in different ways please? So what I want to do is corrupt 3 MyISAM 100gb databases, 3 InnoDB 100gb databases - ALL WITH DIFFERENT ERROR MESSAGES and have these newbies fix them in a 2 hour period :-) I have fixed oodles of db's but NEVER thought I would see the say where I would WANT to corrupt a db on purpose, but that day is here and am looking for advise please. Thanks... Nunu 100GB! That's a HUGE database to play with and can take more than hours to repair. I would tone it down. You just want them to fix it and know what the commands/steps are, not spend hours sitting there watching a blinking cursor. We have almost a BILLION rows in our DB and it's only 70GB and we cringe if we have to alter a table as we know it's going to take a LONG time. I would focus on JUNIOR DBA tasks, such as: How do you start/stop the mysql server? sudo /etc/init.d/mysql stop|start|restart Where are the mysql logs found? (Trick Q: On ubuntu they're stupidly in /var/log/messages !?) GRANTing permissions (figure out why the script isn't writing to the DB, or find the security issue with this user, etc.) Execute a mysql command from the bash command line (not the mysql CLI) How do you find a slow query (slow query log) Setup a master/slave and then deliberately write to the slave. This causes replication to fail then. How do you fix it? mysql show slave status\G Slave_IO_Running: Yes Slave_SQL_Running: No Last_Error: Error 'Duplicate key name 'id_operator'' on query. Default database: 'core'. Query: 'ALTER TABLE `user_has_notification` ADD INDEX `id_operator` (`id_operator`)' mysql stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave; show slave status\G What if /var/log/mysql growing too large? mysql PURGE BINARY LOGS; (The command RESET MASTER is not intended while replication slaves are running.) Foreign Key Failures? mysql show innodb status; LATEST FOREIGN KEY ERROR 090604 0:50:37 Cannot drop table `core/city` because it is referenced by `core/state` How do you make a backup? mysqldump -uroot -p --opt --add-drop-database --complete-insert --quote-names --comments --verbose --databases mydatabase | gzip -c ~/mydatabase.sql.gz How would you load that backup back in? gunzip ~/mydatagbase.sql.gz | mysql -uroot -p How do you load a comma separated file with a column header line? load data infile '/home/prod/user-batch.csv' ignore into table invitation_request fields terminated by ',' ignore 1 lines; What if you have lost/forgotten the mysql root password? http://dev.mysql.com/doc/refman/5.1/en/resetting-permissions.html#resetting -permissions-unix You could throw these in for good measure: mysqlcheck --user=root --password=XX --check --auto-repair mydatabase mysqlcheck --user=root --password=XX --analyze --auto-repair mydatabase Ask about mytop and various field lengths: # BIGINT UNSIGNED = 8 Byte = = 18446744073709551615 # INT UNSIGNED = 4 Byte = = 4294967295 # MEDIUMINT UNSIGNED = 3 Byte = FF = 16777215 # SMALLINT UNSIGNED = 2 Byte = = 65535 # TINYINT UNSIGNED = 1 Byte = FF = 255 # BIGINT SIGNED = -9223372036854775808 to 9223372036854775807 # INT SIGNED = -2147483648 to 2147483647 # MEDIUMINT SIGNED = -8388608 to 8388607 # SMALLINT SIGNED = -32768 to 32767 # TINYINT SIGNED = -128 to 127 # TINYTEXT = 255 # TEXT = 65535 # MEDIUMTEXT = 16777215 # LONGTEXT = 4294967295 # TEXT fields are NOT case sensitive, whereas BLOB fields are. # Always try to use UNSIGNED integers whenever possible. Anyways, you get the idea. Don't set the JUNIOR interviewee up for failure from the start. The questions should be something in the realm of what they'll be doing on a daily basis. The rest is stuff that Google will solve if and when that time comes. You want to make sure they have a solid grasp of mysql, not an expert in it. Don't blast them if they don't have this stuff memorized. I sure as hell don't, that's what our Wiki is for. You want people that can find answers, not memorize them. Daevid. http://daevid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How to corrupt a database please???
Hi all, I am hiring a few new junior DBA's and I want to put them thru a simple db repair training. Does anyone know how I can deliberately corrupt a MyISAM and InnoDB database in different ways please? So what I want to do is corrupt 3 MyISAM 100gb databases, 3 InnoDB 100gb databases - ALL WITH DIFFERENT ERROR MESSAGES and have these newbies fix them in a 2 hour period :-) I have fixed oodles of db's but NEVER thought I would see the say where I would WANT to corrupt a db on purpose, but that day is here and am looking for advise please. Thanks... Nunu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to corrupt a database please???
open the file and remove some data and close it for both data file and index files, So the tables will be corrupted when access. On Sun, Apr 18, 2010 at 9:55 PM, Nurudin Javeri nsjav...@idh.com wrote: Hi all, I am hiring a few new junior DBA's and I want to put them thru a simple db repair training. Does anyone know how I can deliberately corrupt a MyISAM and InnoDB database in different ways please? So what I want to do is corrupt 3 MyISAM 100gb databases, 3 InnoDB 100gb databases - ALL WITH DIFFERENT ERROR MESSAGES and have these newbies fix them in a 2 hour period :-) I have fixed oodles of db's but NEVER thought I would see the say where I would WANT to corrupt a db on purpose, but that day is here and am looking for advise please. Thanks... Nunu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com -- Thanks Suresh Kuna MySQL DBA
Re: How to corrupt a database please???
You can remove the innodb logs and/or the innodb data file. You can also remove some of the individual .idb files (if you're using file-per-table option). On Sun, Apr 18, 2010 at 11:25 AM, Nurudin Javeri nsjav...@idh.com wrote: Hi all, I am hiring a few new junior DBA's and I want to put them thru a simple db repair training. Does anyone know how I can deliberately corrupt a MyISAM and InnoDB database in different ways please? So what I want to do is corrupt 3 MyISAM 100gb databases, 3 InnoDB 100gb databases - ALL WITH DIFFERENT ERROR MESSAGES and have these newbies fix them in a 2 hour period :-) I have fixed oodles of db's but NEVER thought I would see the say where I would WANT to corrupt a db on purpose, but that day is here and am looking for advise please. Thanks... Nunu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: How to corrupt a database please???
On Sun, Apr 18, 2010 at 10:39 AM, Suresh Kuna sureshkumar...@gmail.com wrote: open the file and remove some data and close it for both data file and index files, So the tables will be corrupted when access. On Sun, Apr 18, 2010 at 9:55 PM, Nurudin Javeri nsjav...@idh.com wrote: Hi all, I am hiring a few new junior DBA's and I want to put them thru a simple db repair training. Does anyone know how I can deliberately corrupt a MyISAM and InnoDB database in different ways please? So what I want to do is corrupt 3 MyISAM 100gb databases, 3 InnoDB 100gb databases - ALL WITH DIFFERENT ERROR MESSAGES and have these newbies fix them in a 2 hour period :-) I have fixed oodles of db's but NEVER thought I would see the say where I would WANT to corrupt a db on purpose, but that day is here and am looking for advise please. Thanks... Nunu Umm, shouldn't you train your Junion DBA to: 1. fail off of the corrupted servers. 2. restore from backup. 3. or at least get a non-junior dba and then have them shadow? I have a problem with the idea of repairing Innodb. Depending on where the corruption (checksum mismatch) has occurred it can be very difficult to get all the original data out. Don't get me wrong, there are way to do it, but it is a nasty endeavor. For that mater I don't trust repairing MyISAM all that much either. I try my very best to keep MyISAM out of production. In my opinion MyISAM should be treated as something one step higher than the blackhole engine. Put data in and you might be able to pull it out later. (don't get me wrong, packed myisam has its place...) -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to corrupt a database please???
What if the DBA ask for the backup? And those recommendations can be fixed or they have a very high chance of making recovery impossible? On Sun, Apr 18, 2010 at 1:09 PM, Rob Wultsch wult...@gmail.com wrote: On Sun, Apr 18, 2010 at 10:39 AM, Suresh Kuna sureshkumar...@gmail.com wrote: open the file and remove some data and close it for both data file and index files, So the tables will be corrupted when access. On Sun, Apr 18, 2010 at 9:55 PM, Nurudin Javeri nsjav...@idh.com wrote: Hi all, I am hiring a few new junior DBA's and I want to put them thru a simple db repair training. Does anyone know how I can deliberately corrupt a MyISAM and InnoDB database in different ways please? So what I want to do is corrupt 3 MyISAM 100gb databases, 3 InnoDB 100gb databases - ALL WITH DIFFERENT ERROR MESSAGES and have these newbies fix them in a 2 hour period :-) I have fixed oodles of db's but NEVER thought I would see the say where I would WANT to corrupt a db on purpose, but that day is here and am looking for advise please. Thanks... Nunu Umm, shouldn't you train your Junion DBA to: 1. fail off of the corrupted servers. 2. restore from backup. 3. or at least get a non-junior dba and then have them shadow? I have a problem with the idea of repairing Innodb. Depending on where the corruption (checksum mismatch) has occurred it can be very difficult to get all the original data out. Don't get me wrong, there are way to do it, but it is a nasty endeavor. For that mater I don't trust repairing MyISAM all that much either. I try my very best to keep MyISAM out of production. In my opinion MyISAM should be treated as something one step higher than the blackhole engine. Put data in and you might be able to pull it out later. (don't get me wrong, packed myisam has its place...) -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mr.crip...@gmail.com
Re: How to corrupt a database please???
On Sun, Apr 18, 2010 at 11:07 AM, Jim Lyons jlyons4...@gmail.com wrote: You can remove the innodb logs and/or the innodb data file. You can also remove some of the individual .idb files (if you're using file-per-table option). He originally asked about how to provide a training excise about repairing a db. How the hell do you repair from not having data files? For that matter the recovery from lacking log files (and assuming a crashed state) is imho ugly as hell. -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to corrupt a database please???
A couple good tests are. 1. Corrupt a relay log. For this you can stop the sql thread, cat /dev/urandom over the newest relay log, start the sql thread and watch it fail. 2. Change the innodb_log_file_size in my.cnf without going through the proper procedure to remove the old log files. In 5.0 this will cause incorrect information in frm file errors for queries which will take a little bit of work to track down. 3. Can some random data over myisam files and run a check tables so mysql marks them as crashed. On Sun, Apr 18, 2010 at 11:13 AM, Rob Wultsch wult...@gmail.com wrote: On Sun, Apr 18, 2010 at 11:07 AM, Jim Lyons jlyons4...@gmail.com wrote: You can remove the innodb logs and/or the innodb data file. You can also remove some of the individual .idb files (if you're using file-per-table option). He originally asked about how to provide a training excise about repairing a db. How the hell do you repair from not having data files? For that matter the recovery from lacking log files (and assuming a crashed state) is imho ugly as hell. -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=eric.ber...@gmail.com -- Eric Bergen eric.ber...@gmail.com http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to corrupt a database please???
On Sun, Apr 18, 2010 at 11:13 AM, Andrés Tello mr.crip...@gmail.com wrote: What if the DBA ask for the backup? And those recommendations can be fixed or they have a very high chance of making recovery impossible? Who is the dba going to ask for a backup? Himself? The guy that puts backups on tape? One way or another the DBA damn well better know how to get a backup. Failing off of a server gets you on to a slave which should be sync'd with the master. If you restore from backup then you can run a pitr . In my opinion both of these options are usually superior to running repair table on a production server. That is if you like uptime. For the record innodb corruption is quite rare, at least in comparison to MyISAM corruption. If I get a call at 2AM and find a server having died due to innodb corruption I would fail off of the server. No ifs, no ands, not buts. I would assume: 1. Possible, perhaps even probably hardware issues if there is Innodb corruptions. 2. A failover takes a set amount of time. Repairing corruptions will usually take longer, perhaps much much longer. -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to corrupt a database please???
Rob Wultsch wrote: On Sun, Apr 18, 2010 at 11:13 AM, Andrés Tello mr.crip...@gmail.com wrote: What if the DBA ask for the backup? And those recommendations can be fixed or they have a very high chance of making recovery impossible? Who is the dba going to ask for a backup? Himself? The guy that puts backups on tape? One way or another the DBA damn well better know how to get a backup. Failing off of a server gets you on to a slave which should be sync'd with the master. If you restore from backup then you can run a pitr . In my opinion both of these options are usually superior to running repair table on a production server. That is if you like uptime. For the record innodb corruption is quite rare, at least in comparison to MyISAM corruption. If I get a call at 2AM and find a server having died due to innodb corruption I would fail off of the server. No ifs, no ands, not buts. I would assume: 1. Possible, perhaps even probably hardware issues if there is Innodb corruptions. 2. A failover takes a set amount of time. Repairing corruptions will usually take longer, perhaps much much longer. I agree with Rob. InnoDB failures are nearly always caused by OS-level or HW-level failures. The worst-case scenario is to need to rebuild part of your data from whatever information remains in the corrupted file. It is much better to restore from backup or rebuild from a slave than to go through the pain of rebuilding a corrupted tablespace. But, here are some ideas on ways to screw one up: 1) Put it on an NFS drive then read from it using another user's account while the database is trying to write to it. 2) Scan it with an antivirus program while it is online and actively making changes. 3) Use a hex editor and manually zero out a page of data or index 4) Delete the active log file (or both of them) 5) Turn on two MySQL instances to the same files at the same time. 6) Delete the .frm file for a table 7) Take a backup of the tablespace, change a few things, the restore the tablespace but not the logs. While I can't predict what kind of problem you will create for yourself, these are all things that have created problems for others in the past. -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Please help me.
Hi You can make a backup of the original table and recreate the table and import. That will help you ASAP Muthukumar Selvarasu, Project Manager, Webmaster Ltd. -Original Message- From: Gary Smith [mailto:g...@primeexalia.com] Sent: Thursday, March 19, 2009 7:25 AM To: Valentin Ionescu; mysql@lists.mysql.com Cc: wi...@lists.mysql.com; mysql-h...@lists.mysql.com Subject: RE: Please help me. Velentin, http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html Note the section for the droping of foreign keys used the contraint name, not the key name. Try this and see if it solves the first problem (of removing the constraint). Then you should be able to drop the column after that. From: Valentin Ionescu [colibry...@yahoo.com] Sent: Wednesday, March 18, 2009 11:27 AM To: mysql@lists.mysql.com Cc: wi...@lists.mysql.com; mysql-h...@lists.mysql.com Subject: Please help me. Hi! My name is Valentin and I am writing to you for the following problem: I created a database containing the table: CREATE TABLE `documents_ex` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `Journal_ex_ID` int(10) unsigned DEFAULT NULL, `Documents_ID` int(10) unsigned DEFAULT NULL, `Data` datetime DEFAULT NULL, `Nr` varchar(50) DEFAULT NULL, PRIMARY KEY (`ID`), UNIQUE KEY `Journal_ex_ID` (`Journal_ex_ID`,`Documents_ID`,`Data`,`Nr`), KEY `Documents_ID` (`Documents_ID`), CONSTRAINT `documents_ex_ibfk_1` FOREIGN KEY (`Journal_ex_ID`) REFERENCES `journal_ex` (`ID`) ON DELETE CASCADE, CONSTRAINT `documents_ex_ibfk_2` FOREIGN KEY (`Documents_ID`) REFERENCES `documents` (`ID`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 After some time I decided to drop 'Journal_ex_ID' column and all its environment from this non empty table. All I tried to do like: alter table documents_ex drop column Journal_ex_ID or alter table documents_ex drop foreign key Journal_ex_ID or alter table documents_ex drop key Journal_ex_ID I receive the same error 150 and I don't know what to do. Please help me. Best regards. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=g...@primeexalia.com -- MySQL Windows Mailing List For list archives: http://lists.mysql.com/win32 To unsubscribe: http://lists.mysql.com/win32?unsub=muthukumar...@hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Please help me.
Hi! My name is Valentin and I am writing to you for the following problem: I created a database containing the table: CREATE TABLE `documents_ex` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `Journal_ex_ID` int(10) unsigned DEFAULT NULL, `Documents_ID` int(10) unsigned DEFAULT NULL, `Data` datetime DEFAULT NULL, `Nr` varchar(50) DEFAULT NULL, PRIMARY KEY (`ID`), UNIQUE KEY `Journal_ex_ID` (`Journal_ex_ID`,`Documents_ID`,`Data`,`Nr`), KEY `Documents_ID` (`Documents_ID`), CONSTRAINT `documents_ex_ibfk_1` FOREIGN KEY (`Journal_ex_ID`) REFERENCES `journal_ex` (`ID`) ON DELETE CASCADE, CONSTRAINT `documents_ex_ibfk_2` FOREIGN KEY (`Documents_ID`) REFERENCES `documents` (`ID`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 After some time I decided to drop 'Journal_ex_ID' column and all its environment from this non empty table. All I tried to do like: alter table documents_ex drop column Journal_ex_ID or alter table documents_ex drop foreign key Journal_ex_ID or alter table documents_ex drop key Journal_ex_ID I receive the same error 150 and I don't know what to do. Please help me. Best regards. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Please help me.
Velentin, http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html Note the section for the droping of foreign keys used the contraint name, not the key name. Try this and see if it solves the first problem (of removing the constraint). Then you should be able to drop the column after that. From: Valentin Ionescu [colibry...@yahoo.com] Sent: Wednesday, March 18, 2009 11:27 AM To: mysql@lists.mysql.com Cc: wi...@lists.mysql.com; mysql-h...@lists.mysql.com Subject: Please help me. Hi! My name is Valentin and I am writing to you for the following problem: I created a database containing the table: CREATE TABLE `documents_ex` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `Journal_ex_ID` int(10) unsigned DEFAULT NULL, `Documents_ID` int(10) unsigned DEFAULT NULL, `Data` datetime DEFAULT NULL, `Nr` varchar(50) DEFAULT NULL, PRIMARY KEY (`ID`), UNIQUE KEY `Journal_ex_ID` (`Journal_ex_ID`,`Documents_ID`,`Data`,`Nr`), KEY `Documents_ID` (`Documents_ID`), CONSTRAINT `documents_ex_ibfk_1` FOREIGN KEY (`Journal_ex_ID`) REFERENCES `journal_ex` (`ID`) ON DELETE CASCADE, CONSTRAINT `documents_ex_ibfk_2` FOREIGN KEY (`Documents_ID`) REFERENCES `documents` (`ID`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 After some time I decided to drop 'Journal_ex_ID' column and all its environment from this non empty table. All I tried to do like: alter table documents_ex drop column Journal_ex_ID or alter table documents_ex drop foreign key Journal_ex_ID or alter table documents_ex drop key Journal_ex_ID I receive the same error 150 and I don't know what to do. Please help me. Best regards. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=g...@primeexalia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Please the whole message?
I have WindowsXP I stopped the service from the services shortcut in the Administrative tools.then according to to the reference manual to start the server for the first time the command to run is given as C:\C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld --console But it didn't work, the following was the displayed message 'C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld' is not recognized as an i nternal or external command, operable program or batch file. *But when I used mysqld-nt The following is the out put* C:\C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt --console 081004 16:56:01 InnoDB: Started; log sequence number 0 43655 081004 16:56:01 [Warning] Neither --relay-log nor --relay-log-index were used; s o replication may break when this MySQL server acts as a slave and has his hostn ame changed!! Please use '--relay-log=varuna-e2a34b74-relay-bin' to avoid this p roblem. 081004 16:56:01 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: re ady for connections. Version: '5.0.67-community-nt' socket: '' port: 3306 MySQL Community Edition (GPL) 1 In the reference manual there are three server types how can I start the mysqld server? 2 What is the difference between installing MySQL as a server and a service, Is it only that when installed as a service MySQL server starts when Windows starts and when Windows stops it stops? 3 After starting the server in above mentioned way How can I use MySQL server Do I have to open another console window and do what? 4 In Windows is it not possible to start other to two servers mysqld and mysqld-debug Varuna
Re: can some please help me -- REPLICATION
Add additional option to my.cnf [mysqld] relay-log-purge=1 On Mon, Aug 11, 2008 at 2:21 AM, Brown, Charles [EMAIL PROTECTED] wrote: Here is what I'm getting mysql change master to master_host = 'naxbmisq01.bmi.com', master_user = 'repl', master_password = 'repl'; ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log mysql reset slave; Query OK, 0 rows affected (0.00 sec) mysql change master to master_host = 'naxbmisq01.bmi.com', master_user = 'repl', master_password = 'repl'; ERROR 29 (HY000): File './naxbmisq02-relay-bin.000157' not found (Errcode: 2) mysql -Original Message- From: David Giragosian [mailto:[EMAIL PROTECTED] Sent: Sunday, August 10, 2008 12:54 PM To: mysql@lists.mysql.com Subject: Re: can some please help me -- REPLICATION On 8/10/08, Jim Lyons [EMAIL PROTECTED] wrote: you should probably just resync your slave. If it hasn't run for over a month then there's not a lot of point in trying to start it up. Even if you did start the slave (which seems doubtful) you'd have over a month's worth of commands to make up. You can tell mysql to not keep relay logs that have already been used. What's the command or setting for this action? --David. This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
can some please help me -- REPLICATION
The background: We are doing replication. The file-system containing the SLAVE's relay-log got filled to capacity. Later on, I noticed replication has not been working for the last month or more. My Action: I deleted the 2 oldest relay-log then attempted to restart SLAVE. Now, the SLAVE will not start. Replication will not start. The SLAVE continues to request for those logs. My Question: What instructions/command can I issue to the SLAVE so it can ignore relay-logs not found? See below for msg I have been getting from the SLAVE -- for all attempts to restart. 080809 2:09:23 [ERROR] Failed to open the relay log 'FIRST' (relay_log_pos 4) 080809 2:09:23 [ERROR] Could not open log file 080809 2:13:58 [ERROR] Error reading master configuration 080809 2:29:49 [ERROR] Error reading master configuration 080809 7:31:40 [ERROR] Error reading master configuration 080809 7:57:32 [ERROR] Failed to open log (file './naxbmisq02-relay-bin.000157', errno 2) 080809 7:57:32 [ERROR] Failed to open the relay log 'FIRST' (relay_log_pos 4) 080809 7:57:32 [ERROR] Could not open log file 080809 8:01:27 [ERROR] Error reading master configuration 080809 8:11:42 [ERROR] Error reading master configuration This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can some please help me -- REPLICATION
you should probably just resync your slave. If it hasn't run for over a month then there's not a lot of point in trying to start it up. Even if you did start the slave (which seems doubtful) you'd have over a month's worth of commands to make up. You can tell mysql to not keep relay logs that have already been used. Just copy over the masters' files, reset slave and master and start up the slave. On Sun, Aug 10, 2008 at 11:34 AM, Brown, Charles [EMAIL PROTECTED] wrote: The background: We are doing replication. The file-system containing the SLAVE's relay-log got filled to capacity. Later on, I noticed replication has not been working for the last month or more. My Action: I deleted the 2 oldest relay-log then attempted to restart SLAVE. Now, the SLAVE will not start. Replication will not start. The SLAVE continues to request for those logs. My Question: What instructions/command can I issue to the SLAVE so it can ignore relay-logs not found? See below for msg I have been getting from the SLAVE -- for all attempts to restart. 080809 2:09:23 [ERROR] Failed to open the relay log 'FIRST' (relay_log_pos 4) 080809 2:09:23 [ERROR] Could not open log file 080809 2:13:58 [ERROR] Error reading master configuration 080809 2:29:49 [ERROR] Error reading master configuration 080809 7:31:40 [ERROR] Error reading master configuration 080809 7:57:32 [ERROR] Failed to open log (file './naxbmisq02-relay-bin.000157', errno 2) 080809 7:57:32 [ERROR] Failed to open the relay log 'FIRST' (relay_log_pos 4) 080809 7:57:32 [ERROR] Could not open log file 080809 8:01:27 [ERROR] Error reading master configuration 080809 8:11:42 [ERROR] Error reading master configuration This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: can some please help me -- REPLICATION
On 8/10/08, Jim Lyons [EMAIL PROTECTED] wrote: you should probably just resync your slave. If it hasn't run for over a month then there's not a lot of point in trying to start it up. Even if you did start the slave (which seems doubtful) you'd have over a month's worth of commands to make up. You can tell mysql to not keep relay logs that have already been used. What's the command or setting for this action? --David.
RE: can some please help me -- REPLICATION
Re: You can tell mysql to not keep relay logs that have already been used. What command does this -Original Message- From: David Giragosian [mailto:[EMAIL PROTECTED] Sent: Sunday, August 10, 2008 12:54 PM To: mysql@lists.mysql.com Subject: Re: can some please help me -- REPLICATION On 8/10/08, Jim Lyons [EMAIL PROTECTED] wrote: you should probably just resync your slave. If it hasn't run for over a month then there's not a lot of point in trying to start it up. Even if you did start the slave (which seems doubtful) you'd have over a month's worth of commands to make up. You can tell mysql to not keep relay logs that have already been used. What's the command or setting for this action? --David. This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: can some please help me -- REPLICATION
Here is what I'm getting mysql change master to master_host = 'naxbmisq01.bmi.com', master_user = 'repl', master_password = 'repl'; ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log mysql reset slave; Query OK, 0 rows affected (0.00 sec) mysql change master to master_host = 'naxbmisq01.bmi.com', master_user = 'repl', master_password = 'repl'; ERROR 29 (HY000): File './naxbmisq02-relay-bin.000157' not found (Errcode: 2) mysql -Original Message- From: David Giragosian [mailto:[EMAIL PROTECTED] Sent: Sunday, August 10, 2008 12:54 PM To: mysql@lists.mysql.com Subject: Re: can some please help me -- REPLICATION On 8/10/08, Jim Lyons [EMAIL PROTECTED] wrote: you should probably just resync your slave. If it hasn't run for over a month then there's not a lot of point in trying to start it up. Even if you did start the slave (which seems doubtful) you'd have over a month's worth of commands to make up. You can tell mysql to not keep relay logs that have already been used. What's the command or setting for this action? --David. This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Who could please tell me whether my procedure's memory usefulness is normally or not?
mysql show global status like '%stmt%'; +-+-+ | Variable_name | Value | +-+-+ | Com_stmt_close | 4875504 | | Com_stmt_execute| 4875504 | | Com_stmt_fetch | 0 | | Com_stmt_prepare| 4875507 | | Com_stmt_reset | 0 | | Com_stmt_send_long_data | 0 | | Prepared_stmt_count | 0 | +-+-+ 7 rows in set (0.00 sec) -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
[phpMyAdmin] please vote or suggest features
Hi, for all you people out there loving phpMyAdmin ;-) please visit http://hackontest.org and vote for or suggest your favorite feature you would like to see in phpMyAdmin and that can be implemented within 24 hours by a team of three Thank you very much! -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice..
Hi All, I am facing a particular problem which i have explained here. Can you please let me know a solution for this. From my web application, I am trying to connect the MySQL server by using the IP address as the server name, and it says the following error: Host 'abc.def.com' is not allowed to connect to this MySQL server Options that I tried: (from the information availabe on internet) a) I tried modifying the hosts file in WinNT directory to include this host name (assuming that it was not understanding the IP), but it did not work b) I tried adding a record with the IP as host and user as root in user table of mysql database it did not work c) I tried adding a record with the 'abc.def.com' as host and user as root in user table of mysql database it did not work d) I tried enabling Remote Access in MySQL Server instance config wizard but since the root users password is not set, it is not allowing me go forward (i.e. the next button is disabled) e) If I try to reset the password while configuring the MySQL Server Instance, it does not allow me to do so f) I tried adding a record with the '%' as host and user as root in user table of mysql database it did not allow me to add the record Regards Ahmad Please do not print this email unless it is absolutely necessary. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice..
Hi, I did that too, I executed the command - GRANT ALL PRIVILEGES ON *.* to 'root'@'localhost' ; I tried also GRANT ALL PRIVILEGES ON *.* to 'root'@'abc.def.com' ; where abc.def.com is my machine name But both of these did not work, I did not mention this in my earlier mail. Can you please let me know if there is any other way out. Regards Ahmad -Original Message- From: Parikh, Dilip Kumar [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:26 PM To: Ahmadbasha Shaik (WT01 - E-ENABLING); Raghavendra Hosabettu (WT01 - Innovation Group) Subject: FW: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Importance: High Hi all, First try checking out the grant for the particular user ? Show grants for user@'abc.def.com'; If u don't find the results u can give grant as :- Grant select on *.* to user@'abc.def.com' identified by ''; Flush privileges; Thanks Regards, Dilipkumar -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:21 PM To: mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Subject: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Importance: High Hi All, I am facing a particular problem which i have explained here. Can you please let me know a solution for this. From my web application, I am trying to connect the MySQL server by using the IP address as the server name, and it says the following error: Host 'abc.def.com' is not allowed to connect to this MySQL server Options that I tried: (from the information availabe on internet) a) I tried modifying the hosts file in WinNT directory to include this host name (assuming that it was not understanding the IP), but it did not work b) I tried adding a record with the IP as host and user as root in user table of mysql database it did not work c) I tried adding a record with the 'abc.def.com' as host and user as root in user table of mysql database it did not work d) I tried enabling Remote Access in MySQL Server instance config wizard but since the root users password is not set, it is not allowing me go forward (i.e. the next button is disabled) e) If I try to reset the password while configuring the MySQL Server Instance, it does not allow me to do so f) I tried adding a record with the '%' as host and user as root in user table of mysql database it did not allow me to add the record Regards Ahmad Please do not print this email unless it is absolutely necessary. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Please do not print this email unless it is absolutely necessary. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice..
make sure to FLUSH PRIVILAGES; - Original Message From: [EMAIL PROTECTED] [EMAIL PROTECTED] To: [EMAIL PROTECTED]; mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Sent: Monday, 14 July, 2008 10:59:35 AM Subject: RE: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Hi, I did that too, I executed the command - GRANT ALL PRIVILEGES ON *.* to 'root'@'localhost' ; I tried also GRANT ALL PRIVILEGES ON *.* to 'root'@'abc.def.com' ; where abc.def.com is my machine name But both of these did not work, I did not mention this in my earlier mail. Can you please let me know if there is any other way out. Regards Ahmad -Original Message- From: Parikh, Dilip Kumar [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:26 PM To: Ahmadbasha Shaik (WT01 - E-ENABLING); Raghavendra Hosabettu (WT01 - Innovation Group) Subject: FW: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Importance: High Hi all, First try checking out the grant for the particular user ? Show grants for user@'abc.def.com'; If u don't find the results u can give grant as :- Grant select on *.* to user@'abc.def.com' identified by ''; Flush privileges; Thanks Regards, Dilipkumar -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:21 PM To: mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Subject: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Importance: High Hi All, I am facing a particular problem which i have explained here. Can you please let me know a solution for this. From my web application, I am trying to connect the MySQL server by using the IP address as the server name, and it says the following error: Host 'abc.def.com' is not allowed to connect to this MySQL server Options that I tried: (from the information availabe on internet) a) I tried modifying the hosts file in WinNT directory to include this host name (assuming that it was not understanding the IP), but it did not work b) I tried adding a record with the IP as host and user as root in user table of mysql database it did not work c) I tried adding a record with the 'abc.def.com' as host and user as root in user table of mysql database it did not work d) I tried enabling Remote Access in MySQL Server instance config wizard but since the root users password is not set, it is not allowing me go forward (i.e. the next button is disabled) e) If I try to reset the password while configuring the MySQL Server Instance, it does not allow me to do so f) I tried adding a record with the '%' as host and user as root in user table of mysql database it did not allow me to add the record Regards Ahmad Please do not print this email unless it is absolutely necessary. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Please do not print this email unless it is absolutely necessary. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ Not happy with your email address?. Get the one you really want - millions of new email addresses available now at Yahoo! http://uk.docs.yahoo.com/ymail/new.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe
RE: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice..
Hi Parikh, Yes.. It worked with IP i.e when I granted privileges to 'root'@'ip of my machine' and flushed it, it worked fine. But I have a qestion below: GRANT ALL PRIVILEGES ON *.* to 'root'@'IP'; FLUSH PRIVILEGES; However, Would '*.def.com' work in the place of IP?, as I feel that giving privileges for each IP for each machine that is going to access the mysql server would be tedious and not recommended choice. Please let me know. Regards Ahmad -Original Message- From: Parikh, Dilip Kumar [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:34 PM To: Ahmadbasha Shaik (WT01 - E-ENABLING); Raghavendra Hosabettu (WT01 - Innovation Group) Subject: FW: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Ok It means that DNS not configured so better give the IP address instead of hsostname and check using in mysql as Show grants for [EMAIL PROTECTED]; This should show u the grants Thanks Regards, Dilipkumar MphasiS an EDS Company | No 25,Steeple Reach,Cathedral Road | Chennai | India | 91 44 28113801 |Extn 2216 Mobile: 9884430998 | 9962029004 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:30 PM To: Parikh, Dilip Kumar; mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Subject: RE: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Hi, I did that too, I executed the command - GRANT ALL PRIVILEGES ON *.* to 'root'@'localhost' ; I tried also GRANT ALL PRIVILEGES ON *.* to 'root'@'abc.def.com' ; where abc.def.com is my machine name But both of these did not work, I did not mention this in my earlier mail. Can you please let me know if there is any other way out. Regards Ahmad -Original Message- From: Parikh, Dilip Kumar [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:26 PM To: Ahmadbasha Shaik (WT01 - E-ENABLING); Raghavendra Hosabettu (WT01 - Innovation Group) Subject: FW: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Importance: High Hi all, First try checking out the grant for the particular user ? Show grants for user@'abc.def.com'; If u don't find the results u can give grant as :- Grant select on *.* to user@'abc.def.com' identified by ''; Flush privileges; Thanks Regards, Dilipkumar -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:21 PM To: mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Subject: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Importance: High Hi All, I am facing a particular problem which i have explained here. Can you please let me know a solution for this. From my web application, I am trying to connect the MySQL server by using the IP address as the server name, and it says the following error: Host 'abc.def.com' is not allowed to connect to this MySQL server Options that I tried: (from the information availabe on internet) a) I tried modifying the hosts file in WinNT directory to include this host name (assuming that it was not understanding the IP), but it did not work b) I tried adding a record with the IP as host and user as root in user table of mysql database it did not work c) I tried adding a record with the 'abc.def.com' as host and user as root in user table of mysql database it did not work d) I tried enabling Remote Access in MySQL Server instance config wizard but since the root users password is not set, it is not allowing me go forward (i.e. the next button is disabled) e) If I try to reset the password while configuring the MySQL Server Instance, it does not allow me to do so f) I tried adding a record with the '%' as host and user as root in user table of mysql database it did not allow me to add the record Regards Ahmad Please do not print this email unless it is absolutely necessary. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Please do not print this email unless it is absolutely necessary. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary
Re: ***SPAM*** RE: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice..
grant all on *.* to root@'%.def.com'; /* The percent sign is your wildcard character. */ flush privileges; I don't think you need to flush privileges as of 5.0. I still do just to be sure. Curtis [EMAIL PROTECTED] wrote: Hi Parikh, Yes.. It worked with IP i.e when I granted privileges to 'root'@'ip of my machine' and flushed it, it worked fine. But I have a qestion below: GRANT ALL PRIVILEGES ON *.* to 'root'@'IP'; FLUSH PRIVILEGES; However, Would '*.def.com' work in the place of IP?, as I feel that giving privileges for each IP for each machine that is going to access the mysql server would be tedious and not recommended choice. Please let me know. Regards Ahmad -Original Message- From: Parikh, Dilip Kumar [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:34 PM To: Ahmadbasha Shaik (WT01 - E-ENABLING); Raghavendra Hosabettu (WT01 - Innovation Group) Subject: FW: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Ok It means that DNS not configured so better give the IP address instead of hsostname and check using in mysql as Show grants for [EMAIL PROTECTED]; This should show u the grants Thanks Regards, Dilipkumar MphasiS an EDS Company | No 25,Steeple Reach,Cathedral Road | Chennai | India | 91 44 28113801 |Extn 2216 Mobile: 9884430998 | 9962029004 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:30 PM To: Parikh, Dilip Kumar; mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Subject: RE: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Hi, I did that too, I executed the command - GRANT ALL PRIVILEGES ON *.* to 'root'@'localhost' ; I tried also GRANT ALL PRIVILEGES ON *.* to 'root'@'abc.def.com' ; where abc.def.com is my machine name But both of these did not work, I did not mention this in my earlier mail. Can you please let me know if there is any other way out. Regards Ahmad -Original Message- From: Parikh, Dilip Kumar [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:26 PM To: Ahmadbasha Shaik (WT01 - E-ENABLING); Raghavendra Hosabettu (WT01 - Innovation Group) Subject: FW: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Importance: High Hi all, First try checking out the grant for the particular user ? Show grants for user@'abc.def.com'; If u don't find the results u can give grant as :- Grant select on *.* to user@'abc.def.com' identified by ''; Flush privileges; Thanks Regards, Dilipkumar -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:21 PM To: mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Subject: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Importance: High Hi All, I am facing a particular problem which i have explained here. Can you please let me know a solution for this. From my web application, I am trying to connect the MySQL server by using the IP address as the server name, and it says the following error: Host 'abc.def.com' is not allowed to connect to this MySQL server Options that I tried: (from the information availabe on internet) a) I tried modifying the hosts file in WinNT directory to include this host name (assuming that it was not understanding the IP), but it did not work b) I tried adding a record with the IP as host and user as root in user table of mysql database it did not work c) I tried adding a record with the 'abc.def.com' as host and user as root in user table of mysql database it did not work d) I tried enabling Remote Access in MySQL Server instance config wizard but since the root users password is not set, it is not allowing me go forward (i.e. the next button is disabled) e) If I try to reset the password while configuring the MySQL Server Instance, it does not allow me to do so f) I tried adding a record with the '%' as host and user as root in user table of mysql database it did not allow me to add the record Regards Ahmad Please do not print this email unless it is absolutely necessary. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED
help with DB design / query please !
hello, im trying to make a DB for a message system. the best way i have made is this: *TABLE conversations* (informacion de cada conversacion) . i*d_conversation (bigint) count(smallint) updated every time a new message is added to this conversation lastmessagetime (timestamp) **updated every time a new message is added to this conversation** * *INDEX (id_conversation,lastmessage)** * * **TABLE user_conversations* (relacion usuario-conversacion) .. *id_user (int) id_conversation (bigint) **read (bool) ** **INDEX (id_conversation) INDEX (id_user)* *TABLE messages * (mensajes on every conversation) ... id_conversation (bigint) id_writer (int) message(varchar) time (timestamp) INDEX (id_conversation,time) my problem is that i need to query those things and i dont know how to do that. *Conversation list of a given user with `read`, number of messages (`count`) , date of last message (i store this value on `conversation` table) and who did it Order by time of the last message of each conversation* i have tried everything i could but im not getting a good solution: SELECT UC.id_conversation, UC.read, C.count, UNIX_TIMESTAMP( ) - UNIX_TIMESTAMP( lastmessage ) , M.message, M.id_usr FROM user_conversations UC, conversations C, messages M WHERE UC.id_usr = 1 AND C.id_conversation = UC.id_conversation AND M.id_conversation = UC.id_conversation AND M.time = C.lastmessage ORDER BY C.lastmessage DESC LIMIT 0,10 thats giving me an ALL in `conversations` as result id select_typetable type possible_keys key key_len ref rows Extra 1 SIMPLE UCrefid_conversation,id_usr id_usr 4 const 3 Using temporary; Using filesort 1 SIMPLE M reforderbytime orderbytime 8 netlivin3.UC.id_conversation1 1 SIMPLE C ALL id_conversation NULL NULL NULL3 Using where im completely lost here, ill really appreciate any help. thanks.
i quick question please
i am a newbie in this mysql path... what are the diferences between mysql 5.0, 5.1 and 6.0 I work mostly with Ms-SQL 2003. any sugestion? any answer? any help would be nice! thx Roberto Zarate Mendoza lima-peru 511 9238-4883
Re: i quick question please
On Thu, 13 Mar 2008, Roberto Zarate [EMAIL PROTECTED] wrote: i am a newbie in this mysql path... what are the diferences between mysql 5.0, 5.1 and 6.0 I work mostly with Ms-SQL 2003. any sugestion? any answer? any help would be nice! I did a Google search for mysql and the top hit was http://www.mysql.com/ the official MySQL main page. I searched for Documentation and found a link on the page pointing to http://dev.mysql.com/doc/ It links to manuals for each of the versions that you want. I clicked on MySQL 6.0 Reference Manual to go to http://dev.mysql.com/doc/refman/6.0/en/index.html the main page for the version 6.0 manual. A link near the top, 1 General Information, looked good. It points to http://dev.mysql.com/doc/refman/6.0/en/introduction.html It has interesting links like 1.4. Overview of the MySQL Database Management System 1.4.1. What is MySQL? 1.4.2. History of MySQL 1.4.3. The Main Features of MySQL 1.5. MySQL Development Roadmap 1.5.1. What's New in MySQL 6.0 It looks like you should start there. Near the bottom is a link for details, C MySQL Change History, which points to http://dev.mysql.com/doc/refman/6.0/en/news.html It has a LOT of links to changes from each sub version to each succeeding sub version. For example, each of 6.0.0, 6.0.1, ... through 6.0.5 has its own page. Note that there are listings for MySQL 6.0.x and 5.2.x. I saw a suggestion on a page on another site that 5.2 was relabelled 6.0. (The 5.1 manual similarly has changes for 5.1.x, et cetera.) There are also links for a lot of other different projects, like C.5 MySQL Visual Studio Plugin Change History. And, unfortunately, those change logs are extremely detailed, probably more detailed that you want to see. For example, SELECT ... FOR UPDATE is now supported. is a general note, but most are like Creating a table with a 19 digit DECIMAL column would cause incorrect data to be stored. ... which seems like an obscure and relatively minor bug. -- Tim McDaniel, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unicode sorting and binary comparison, please!
On 06.03.2008 15:15 CE(S)T, Paul DuBois wrote: Here's some advice from Alexander Barkov: You might be able to use a particular collation to achieve what you want. For example, latin1_general_ci. You can take a look at its collation chart here: http://www.collation-charts.org/mysql60/mysql604.latin1_general_ci.html As you can see, all accented letters are considered as separate letters. So when you do: SELECT ... WHERE a='a' you only get 'a' and 'A'. But you wan't get any other variants of the letter 'a', That doesn't support Unicode, right? So it could sort a, ä and à but not ā, ă and α. Unicode-capability is a must for my application. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unicode sorting and binary comparison, please!
Yves!
This is a complicated matter alright, but it is a complicated
problem to solve here also. Your statement about characters being the
same isn't really correct. To take an example: Let's assume you were
doing a phonebook, in print, of all people in the world. How would you
sort that? All names in the book should be printed correctly as the name
is usually printed in the respective country of origin. You would
realize that such a phonebook just couldn't be made in just one print.
Certain characters, although they can appear (at least as part of a
name) are treated differently in different countries.
Two examples: The nordic umlaut / ring characters: å ä and ö.
(aring;, auml; and ouml; in HTML lingo). These are sorted differently in
the different countries where they are used. In sweden, the are last in
the alphabet, in germay, they are usually, IIRC, intermixed with the a
and o respectively.
Another, and much better, example is the accented characters. In some
languages, accnts are very important parts of the language, French
probably being the best example here. leaving an accent out would change
things considerably, and with or without an eccent would change the sort
order. In Sweden, accents exist, even in Swedish names, and they change
the pronounciation of the word slightly (although you usually know what
the intention is, even when it is left out). But the accented characters
are treated, collation wise in any type of listing, phonebooks etc, as
the accent just wasn't there. The names Linden and Lindén are pronounced
differently, but sorted together as the accent wasn't there at all.
To you specific problem then, the issue is that as we can have just
about every character in the world available in UNICODE (this isn't
true, really, but for this discussion, let's assume this is the case).
The important thing when you store data is that you allow all these
characters to be stored, i.e. the utf8 charcater set is supported. The
collate specification to the is just the default ordering for the
column. Like the phonebook example above, this is how we would sort the
characters in the phonebook, lets assume we use swedish. Then the nice
thing with MySQL is that you can allow another sort order and/or
comparison method, like being able to resort the phonebook for
non-swedish people.
As for comparisons, the issue is the same. You don't know, assuming
the phonebook problem above, if someone looking for a person in the book
is French, when accented characters should be properly compared, or
swedish, when they are to be ignored. The solution is to say what
language you want, or if you want a binary comparison. If you want to
accknowledge exact matching, and say any character, accented / unlauted
etc, is different from any other character, specifiy a binary comparison:
SELECT * FROM phonebook WHERE BINARY name = 'Handel';
Look into the character set casting / conversion functions in the
MySQL manual: http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html
Alternatively, you could specify the client collation, which would
apply to all operations. Or you could create your own collation. I would
really like more case sensitive collations myself. Case sensitiveness is
also something that is different for different characters in different
languages.
Hope this helps a bit
/Karlsson
Yves Goergen wrote:
Hello,
I've just read through the MySQL documentation about Unicode support,
collations and how it affects sorting and comparison of strings. And I
find it horrible, at least. I feel like I'm back in the MySQL 3.x days
where I used UTF-8 in my application and MySQL treated it binary. The
only problem was incorrect sorting of things. Today we have UTF-8
support in MySQL, which brings correct sorting (for whatever
definition of correct) but has taken correct comparison again.
When I have three strings, e.g. Handel, Händel and Hendel, I'd
like to have them sorted correctly. Using the
utf8_{general,unicode}_ci collation seems the only way. Now when I
want the row with Handel in it, I'll get two rows back. One of them
is not what I wanted. So strictly, the result is incorrect. The only
way to get this right is using the utf8_bin collation. But this again
makes correct sorting impossible.
It's a nightmare. Why can't I get correct sorting *and* correct (i.e.
precise) comparison in one?
If I cannot even rely on the = operator, what good is a text-storing
database? There even isn't a case-sensitive unicode collation other
than utf8_bin. This means that in every database application that uses
unicode, I cannot separate lower from uppercase when retrieving stuff.
MySQL is simply blind for that. Not to mention different characters
that Unicode, MySQL, DIN, ISO or whoever think are the same, but they
aren't. If they were the same, you wouldn't need both of them.
Finally, my application should really be portable. I haven't looked
Re: Unicode sorting and binary comparison, please!
On 03.03.2008 10:27 CE(S)T, Anders Karlsson wrote: [a lot about why sorting unicode is complicated] If you want to accknowledge exact matching, and say any character, accented / unlauted etc, is different from any other character, specifiy a binary comparison: SELECT * FROM phonebook WHERE BINARY name = 'Handel'; Hm, not quite compatible. The solution I found is using this: SELECT * FROM table WHERE column = 'value' COLLATE ...; But still there binary collation has a different name on MySQL and SQLite. PostgreSQL doesn't support the COLLATE clause, although part of the SQL-92 standard. But you din't quite get my actual problem. You said that sorting Unicode things is complicated. I agree. I can live with a trade-off for sorting. But I cannot accept incorrect selection of records. When I want something that I can specify exactly, I only want to get that back, nothing else. The same counts for uniqueness constrains. I've asked a freind who could test the matter with PostgreSQL. He said, it works exactly as expected. Sorting is unicode-like, selection is precise. Why can't MySQL do that, too? Is it so hard to distinguish sorting and selecting? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Unicode sorting and binary comparison, please!
yves when creating a varchar field in table creation, use the binary. that way, selection is exact. always. david -Original Message- From: Yves Goergen [mailto:[EMAIL PROTECTED] Sent: Monday, March 03, 2008 1:44 PM To: Anders Karlsson Cc: MySQL Subject: Re: Unicode sorting and binary comparison, please! On 03.03.2008 10:27 CE(S)T, Anders Karlsson wrote: [a lot about why sorting unicode is complicated] If you want to accknowledge exact matching, and say any character, accented / unlauted etc, is different from any other character, specifiy a binary comparison: SELECT * FROM phonebook WHERE BINARY name = 'Handel'; Hm, not quite compatible. The solution I found is using this: SELECT * FROM table WHERE column = 'value' COLLATE ...; But still there binary collation has a different name on MySQL and SQLite. PostgreSQL doesn't support the COLLATE clause, although part of the SQL-92 standard. But you din't quite get my actual problem. You said that sorting Unicode things is complicated. I agree. I can live with a trade-off for sorting. But I cannot accept incorrect selection of records. When I want something that I can specify exactly, I only want to get that back, nothing else. The same counts for uniqueness constrains. I've asked a freind who could test the matter with PostgreSQL. He said, it works exactly as expected. Sorting is unicode-like, selection is precise. Why can't MySQL do that, too? Is it so hard to distinguish sorting and selecting? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- 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: Unicode sorting and binary comparison, please!
Yves! OK. I agree I don't like this much myself, but we have to live with the multi-lingual aspect of UNICODE. Or rather, we have to agree to be either multi-lingual, and have the cons and pros of that (using UNICODE), or ignore UNICODE and have binary collations etc. And collation also determine equalness. real life example: I have a friend called called Widén, with an accented e. In Sweden, someone called Widen (with a non-accented e, and which is also a perfectly valid name) would sort and compare the same. I.e. in Sweden Widén = Widen. That's just how it works. But the same names, which are binary different but the same using swedish language and swedish collations, would be different when using a french collation. I happen ti live on a street with a ringed and and an umlauted character in the name. When in the US, these two guys have their unlauts removed are are sorted as the umlauts weren't there. Which is OK in US. Which is not OK in sweden. In essence, string comparisons needs to and must use collations when using UNICODE data. You state that Handel is different than Händel. I tend to agree with you, I am swedish by all means. But using a language collation where these characters don't exist just doesn't cut it. UNICODE collation determines not only sorting but also equality (i.e. é = e etc). Right or wrong, well I think that however you turn something will break. Frankly, I think a lot of blame here is on UNICODE to try to do too much, I'm not a big fan of this myself. But whichever way we do it, it will not be perfect. I think MySQL right now follows the UNICODE spec quite well, although there are still things missing. UNICODE is a reasonable compromise, and I see no better means of dealing with this. So even though I admit I'm no big fan of how UNICODE operates, I've still not figurted out a better way of delaing with it. And you are right of course, you may use the COLLATE keyword also, to enforce a certain collation, although if you want BINARY, I think using BINARY might be slightly more effective. What about a feature request to allow WHERE clauses to use a different collations than the one used for ORDER BY. So collation_connection controls the ORDER BY collation, and then I could say SET collation_connection_comparison = 'utf8_bin'. That would do what you want basically, and I think there might possibly be a need for this. /Karlsson Yves Goergen wrote: On 03.03.2008 10:27 CE(S)T, Anders Karlsson wrote: [a lot about why sorting unicode is complicated] If you want to accknowledge exact matching, and say any character, accented / unlauted etc, is different from any other character, specifiy a binary comparison: SELECT * FROM phonebook WHERE BINARY name = 'Handel'; Hm, not quite compatible. The solution I found is using this: SELECT * FROM table WHERE column = 'value' COLLATE ...; But still there binary collation has a different name on MySQL and SQLite. PostgreSQL doesn't support the COLLATE clause, although part of the SQL-92 standard. But you din't quite get my actual problem. You said that sorting Unicode things is complicated. I agree. I can live with a trade-off for sorting. But I cannot accept incorrect selection of records. When I want something that I can specify exactly, I only want to get that back, nothing else. The same counts for uniqueness constrains. I've asked a freind who could test the matter with PostgreSQL. He said, it works exactly as expected. Sorting is unicode-like, selection is precise. Why can't MySQL do that, too? Is it so hard to distinguish sorting and selecting? -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 Skype: drdatabase -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unicode sorting and binary comparison, please!
On 03.03.2008 23:17 CE(S)T, Anders Karlsson wrote: And you are right of course, you may use the COLLATE keyword also, to enforce a certain collation, although if you want BINARY, I think using BINARY might be slightly more effective. I was also considering compatibility with other DBMS. At least SQLite only supports the COLLATE syntax. I'm unable to find out whether the BINARY keyword is part of SQL-92, because it appears too often in it. But since MySQL seems to be the only system (of those I have tested now) that requires such special care anyway, using BINARY only here could also work. What about a feature request to allow WHERE clauses to use a different collations than the one used for ORDER BY. So collation_connection controls the ORDER BY collation, and then I could say SET collation_connection_comparison = 'utf8_bin'. That would do what you want basically, and I think there might possibly be a need for this. That would effectively be what I originally wanted. Use Unicode for sorting things, but do not use Unicode for comparing with the = operator. LIKE may work with Unicode, as its name already implies a level of fuzzyness. I'd expect LIKE to return more than one record on a unique column. But I always expect = to work as in other programming languages, as in maths and anywhere else: absolute equality, not just something similar. So I'd be happy with such an option. Where can I vote for it? :) Is there a chance to see it in a MySQL 5.0 version? Meanwhile, I have chosen to use utf8_bin for all my tables. This breaks sorting for some few cases (but it hasn't really been a problem back in the non-Unicode-MySQL days) but in exchange finds only what I want to find. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unicode sorting and binary comparison, please!
Hello,
I've just read through the MySQL documentation about Unicode support,
collations and how it affects sorting and comparison of strings. And I
find it horrible, at least. I feel like I'm back in the MySQL 3.x days
where I used UTF-8 in my application and MySQL treated it binary. The
only problem was incorrect sorting of things. Today we have UTF-8
support in MySQL, which brings correct sorting (for whatever definition
of correct) but has taken correct comparison again.
When I have three strings, e.g. Handel, Händel and Hendel, I'd
like to have them sorted correctly. Using the utf8_{general,unicode}_ci
collation seems the only way. Now when I want the row with Handel in
it, I'll get two rows back. One of them is not what I wanted. So
strictly, the result is incorrect. The only way to get this right is
using the utf8_bin collation. But this again makes correct sorting
impossible.
It's a nightmare. Why can't I get correct sorting *and* correct (i.e.
precise) comparison in one?
If I cannot even rely on the = operator, what good is a text-storing
database? There even isn't a case-sensitive unicode collation other than
utf8_bin. This means that in every database application that uses
unicode, I cannot separate lower from uppercase when retrieving stuff.
MySQL is simply blind for that. Not to mention different characters that
Unicode, MySQL, DIN, ISO or whoever think are the same, but they aren't.
If they were the same, you wouldn't need both of them.
Finally, my application should really be portable. I haven't looked into
how other DBMS handle it and whether the SQL syntax would be the same,
should there be any method on the language layer to do it right. I only
know that SQLite stores in UTF-8 but otherwise doesn't care about
Unicode, i.e. sorting should be broken, comparison is correct.
PostgreSQL didn't find its own columns again, so I cancelled the test.
--
Yves Goergen LonelyPixel [EMAIL PROTECTED]
Visit my web laboratory at http://beta.unclassified.de
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can anybody please let me know the counterpart functions for SetSRID(),Makebox2D(),Distance_Sphere() of PostGIS in MySQL
Hello
I want to use functions - SetSRID(),Makebox2D(),Distance_Sphere() of
Post GIS in MySQL. Can please let me know the similar functions in
MySQL.
Environment
===
Operating System : Red Hat Linux 3.4
Database :
MySQL Version 5.0.51
PostgreSQL 8.2.6 with PostGIS 1.2.1
Functions which we are trying to use
a) To retreive the distance between two geometries (co-ordinates).
Basically trying to find and use a function in MySQL similar to
distance_sphere in PostGIS
Query Used
===
SELECT X(s.geom),Y(s.geom),todofuken||shigun||kuchoson AS address,
(distance_sphere(s.geom,GeomFromText('POINT(135 35)',4326))) AS distance
FROM todofuken_tbl t,shikuchoson_tbl s.
Error Occurred
==
ERROR 1305 (42000): FUNCTION .DISTANCE _SPHERE does not exist
b) To find and use a function in MySQL similar to MakeBox2D function of
PostGIS
Query Used
===
select MakeBox2D(GeomFromText('POINT(135.0
34.0)',4326),GeomFromText('POINT(135.5 34.5)',4326));
Error Occurred
==
ERROR 1305 (42000): FUNCTION blog_gisdb.MakeBox2D does not exist
c) To find and use a function in MySQL similar to MakeBox2D function of
PostGIS
Query Used
===
SELECT uri,X(geom),Y(geom),ROUND(dist,2) AS distance FROM (
SELECT
uri,geom,distance(geom,GeomFromText('POINT(135.25 34.25)',4326)) AS dist
FROM geom_tbl g,uri_tbl u WHERE g.id=u.id
AND geom SetSRID(MakeBox2D(GeomFromText('POINT(135.0
34.0)',4326),GeomFromText('POINT(135.5 34.5)',4326)),4326)
) AS d ORDER BY dist;
Error Occurred
==
ERROR 1305 (42000): FUNCTION blog_gisdb.SetSRID does not exist
Can somebody please let me know if similar functions exist and if they
exist, please provide me the names of those functions.
Regards
Ahmad
Please do not print this email unless it is absolutely necessary. Spread
environmental awareness.
The information contained in this electronic message and any attachments to
this message are intended for the exclusive use of the addressee(s) and may
contain proprietary, confidential or privileged information. If you are not the
intended recipient, you should not disseminate, distribute or copy this e-mail.
Please notify the sender immediately and destroy all copies of this message and
any attachments.
WARNING: Computer viruses can be transmitted via email. The recipient should
check this email and any attachments for the presence of viruses. The company
accepts no liability for any damage caused by any virus transmitted by this
email.
www.wipro.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ODBC 3.51.22 problem - please help
On Wed, Dec 12, 2007 at 05:11:43PM -0800, Ed Reed wrote: I've found a glaring problem with the latest ODBC connector. Data types have been changed and data is no longer being read correctly. That's not quite correct -- data types are now actually being read correctly. They were wrong before, even if it was what you expected. I'm running MySQL 5.1.16 on Netware. My apps are VB6 and VBA using ADO. The following query produces different data types depending on the version of the ODBC driver. SELECT ConCat(21000,'-','a') In 3.51.19 it is a VarChar and in 3.51.22 it's a VarBinary. Concat is supposed to return a string. In C a byte array may be fine but in VB a string should be a VarChar. Is this a bug or is there a server or OBDC setting that can be changed to make sure that it always returns a VarChar CONCAT() derives its return types from its arguments. The key part from the CONCAT() documentation is: If all arguments are non-binary strings, the result is a non-binary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent binary string form; if you want to avoid that, you can use an explicit type cast, as in this example: SELECT CONCAT(CAST(int_col AS CHAR), char_col); That earlier releases of the driver would return a non-binary string in your case was a bug. That a numeric argument is converted to a binary string is an unfortunate server feature. I hope it will get fixed in a future server release, but I'm not sure when that will be, and it will almost certainly not be in the 5.1 series. Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ODBC 3.51.22 problem - please help
I've found a glaring problem with the latest ODBC connector. Data types have been changed and data is no longer being read correctly. I'm running MySQL 5.1.16 on Netware. My apps are VB6 and VBA using ADO. The following query produces different data types depending on the version of the ODBC driver. SELECT ConCat(21000,'-','a') In 3.51.19 it is a VarChar and in 3.51.22 it's a VarBinary. Concat is supposed to return a string. In C a byte array may be fine but in VB a string should be a VarChar. Is this a bug or is there a server or OBDC setting that can be changed to make sure that it always returns a VarChar Thanks for the help
Query help, please..
I'm looking at a situation I haven't run into before, and I'm a bit puzzled by it. I have this table structure: Table USERS: userid, class Table OBJECT: userid, class, result Now I want to query the database for a certain user's result in a specified class, which is very, very easy. No problems. But, I also want to find out the user's position relative to others depending on the result. So, if the specified user's result is the 9:th best of all of the users, I want to have a reply from the DB query that say he has position number 9. I really can't figure out how to do that... Somehow I have to make MySQL calculate the position based on the value in the result column. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query help, please..
On Dec 11, 2007 8:38 AM, Anders Norrbring [EMAIL PROTECTED] wrote: I'm looking at a situation I haven't run into before, and I'm a bit puzzled by it. I have this table structure: Table USERS: userid, class Table OBJECT: userid, class, result Now I want to query the database for a certain user's result in a specified class, which is very, very easy. No problems. But, I also want to find out the user's position relative to others depending on the result. So, if the specified user's result is the 9:th best of all of the users, I want to have a reply from the DB query that say he has position number 9. I really can't figure out how to do that... Somehow I have to make MySQL calculate the position based on the value in the result column. Take a look at http://arjen-lentz.livejournal.com/55083.html . Very similar ideas in play, though you also have a join. The basic idea is that you do a count on the number of users that have a lower score. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query help, please..
On Dec 11, 2007, at 10:46 AM, Rob Wultsch wrote: On Dec 11, 2007 8:38 AM, Anders Norrbring [EMAIL PROTECTED] wrote: I'm looking at a situation I haven't run into before, and I'm a bit puzzled by it. I have this table structure: Table USERS: userid, class Table OBJECT: userid, class, result Now I want to query the database for a certain user's result in a specified class, which is very, very easy. No problems. But, I also want to find out the user's position relative to others depending on the result. So, if the specified user's result is the 9:th best of all of the users, I want to have a reply from the DB query that say he has position number 9. I really can't figure out how to do that... Somehow I have to make MySQL calculate the position based on the value in the result column. Take a look at http://arjen-lentz.livejournal.com/55083.html . Very similar ideas in play, though you also have a join. The basic idea is that you do a count on the number of users that have a lower score. Is there any reason you wouldn't want to count the people in front of you and add 1 to get your place in line? It seems like depending on where you are, that may be a shorter number to count :) But I don't know anything about how to do stuff off of separate tables yet still trying to grasp that :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query help, please..
Anders, I also want to find out the user's position relative to others depending on the result. For a given pUserID, something like this? SELECT userid,result,rank FROM ( SELECT o1.userid,o1.result,COUNT(o2.result) AS rank FROM object o1 JOIN object o2 ON o1.result o2.result OR (o1.result=o2.result AND o1.userid=o2.userid) GROUP BY o1.userid,o1.result ) WHERE userid = pUserID; PB - Anders Norrbring wrote: I'm looking at a situation I haven't run into before, and I'm a bit puzzled by it. I have this table structure: Table USERS: userid, class Table OBJECT: userid, class, result Now I want to query the database for a certain user's result in a specified class, which is very, very easy. No problems. But, I also want to find out the user's position relative to others depending on the result. So, if the specified user's result is the 9:th best of all of the users, I want to have a reply from the DB query that say he has position number 9. I really can't figure out how to do that... Somehow I have to make MySQL calculate the position based on the value in the result column. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Please help to solve a serios problem
Hi, i need of help for a serios problem. We have installed mysql 5 and we are using InnoDB engine. OS is CentOS 5, x86. Our problem is this. We get this message in the log: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) The problem is that mysql make recovering the database, we are restarting whole server and it crash again. It continue to crash. We tried to recover manually database, but it continue to crash. What we can do to solve this problem? We need to solve this problem urgently. Thanks in advanced! Regards, Ali Nebi! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help to solve a serios problem
i believe you need to have root create the file /var/lib/mysql/mysql.sock and then make it readable and writable by the user that owns mysql. You can make that same file owner by the user that runs mysql. -Original Message- From: Ali Nebi [EMAIL PROTECTED] Sent: Nov 6, 2007 9:24 AM To: mysql@lists.mysql.com Subject: Please help to solve a serios problem Hi, i need of help for a serios problem. We have installed mysql 5 and we are using InnoDB engine. OS is CentOS 5, x86. Our problem is this. We get this message in the log: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) The problem is that mysql make recovering the database, we are restarting whole server and it crash again. It continue to crash. We tried to recover manually database, but it continue to crash. What we can do to solve this problem? We need to solve this problem urgently. Thanks in advanced! Regards, Ali Nebi! -- 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: Please help to solve a serios problem
On Tue, 2007-11-06 at 09:32 -0600, [EMAIL PROTECTED] wrote: i believe you need to have root create the file /var/lib/mysql/mysql.sock and then make it readable and writable by the user that owns mysql. You can make that same file owner by the user that runs mysql. -Original Message- From: Ali Nebi [EMAIL PROTECTED] Sent: Nov 6, 2007 9:24 AM To: mysql@lists.mysql.com Subject: Please help to solve a serios problem Hi, i need of help for a serios problem. We have installed mysql 5 and we are using InnoDB engine. OS is CentOS 5, x86. Our problem is this. We get this message in the log: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) The problem is that mysql make recovering the database, we are restarting whole server and it crash again. It continue to crash. We tried to recover manually database, but it continue to crash. What we can do to solve this problem? We need to solve this problem urgently. Thanks in advanced! Regards, Ali Nebi! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] This socket file is created automatically when i run mysql by this way: /etc/init.d/mysql start. The file has these permissions set. I think this is ok, problem is something different i think, but i don't know where. srwxrwxrwx mysql mysql system_u:object_r:mysqld_var_run_t mysql.sock -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help to solve a serios problem
The mysql.sock location '/var/lib/mysql/mysql.sock' attempted to be used by your connecting client may be wrong. linux$ ls -la /var/lib/mysql/mysql.sock Does the file exist? You say this is the sock file created by mysqld: srwxrwxrwx mysql mysql system_u:object_r:mysqld_var_run_t mysql.sock So your client would need to connect like so: linux$ mysql --socket=system_u:object_r:mysqld_var_run_t mysql.sock --user=user -p What OS are you running MySQL on? -RG Ali Nebi wrote: On Tue, 2007-11-06 at 09:32 -0600, [EMAIL PROTECTED] wrote: i believe you need to have root create the file /var/lib/mysql/mysql.sock and then make it readable and writable by the user that owns mysql. You can make that same file owner by the user that runs mysql. -Original Message- From: Ali Nebi [EMAIL PROTECTED] Sent: Nov 6, 2007 9:24 AM To: mysql@lists.mysql.com Subject: Please help to solve a serios problem Hi, i need of help for a serios problem. We have installed mysql 5 and we are using InnoDB engine. OS is CentOS 5, x86. Our problem is this. We get this message in the log: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) The problem is that mysql make recovering the database, we are restarting whole server and it crash again. It continue to crash. We tried to recover manually database, but it continue to crash. What we can do to solve this problem? We need to solve this problem urgently. Thanks in advanced! Regards, Ali Nebi! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] This socket file is created automatically when i run mysql by this way: /etc/init.d/mysql start. The file has these permissions set. I think this is ok, problem is something different i think, but i don't know where. srwxrwxrwx mysql mysql system_u:object_r:mysqld_var_run_t mysql.sock -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help to solve a serios problem
- Original Message - From: Russell E Glaue [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, November 06, 2007 8:55 PM Subject: Re: Please help to solve a serios problem The mysql.sock location '/var/lib/mysql/mysql.sock' attempted to be used by your connecting client may be wrong. linux$ ls -la /var/lib/mysql/mysql.sock srwxrwxrwx mysql mysql system_u:object_r:mysqld_var_run_t mysql.sock So your client would need to connect like so: linux$ mysql --socket=system_u:object_r:mysqld_var_run_t mysql.sock --user=user -p What OS are you running MySQL on? -RG Thanks for the reply. Does the file exist? Yes, i see that the file exist. You say this is the sock file created by mysqld: when mysqld daemon is stopped the mysql.sock file not exist, when we run /etc/init.d/mysqd (this is mysql.server), it start mysql_safe and create mysql.sock under /var/lib/mysql/. linux$ mysql -- socket=system_u:object_r:mysqld_var_run_t mysql.sock --user=user -p We don't need to use socket option because in /etc/my.cnf we have set where is mysql.sock file. By this way when we start mysql server, mysql client already know where is mysql socket. I suppose this is correct, if not please correct me. We are using CentOS 5, 64 bits version. Also this: system_u:object_r:mysqld_var_run_t. It is not need to be set in sock=, because these are SELinux permissions, this is different. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question related to MySQL 5.1.21 and enabling pbxt engine, please
to `dlsym' sql_plugin.cc:(.text+0x3869): undefined reference to `dlclose' sql_plugin.cc:(.text+0x3916): undefined reference to `dlclose' sql_plugin.cc:(.text+0x3a3d): undefined reference to `dlclose' collect2: ld returned 1 exit status make[3]: *** [mysqld] Error 1 make[3]: Leaving directory `/var/local/repository/src/mysql-5.1.21-beta/sql' make[2]: *** [all-recursive] Error 1 make[2]: Leaving directory `/var/local/repository/src/mysql-5.1.21-beta/sql' make[1]: *** [all] Error 2 make[1]: Leaving directory `/var/local/repository/src/mysql-5.1.21-beta/sql' make: *** [all-recursive] Error 1 Could you please help ? Thanks in advance for your help, Mariella Looking for a deal? Find great prices on flights and hotels with Yahoo! FareChase. http://farechase.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question related to MySQL 5.1.21 and enabling pbxt engine, please
2 make[1]: Leaving directory `/var/local/repository/src/mysql-5.1.21-beta/sql' make: *** [all-recursive] Error 1 --- Vladimir Shebordaev [EMAIL PROTECTED] wrote: Mariella, try to disable building static binaries, please don't use --enable-static configure option, you'd better use --disable-static instead. In the hope it helps. Regards, Vladimir Mariella Petrini пиÑеÑ: Hi All, I would like to be able to use MySQL 5.1.21 with the PBXT engine. I have compiled MySQL 5.1.21 64 bits on Debian 4.0 with linux 2.6 with the options specified in the README that comes with the PBXT source code. I have copied the binary of the pbxt library into the lib/mysql directory When I tried to execuute the sql command install plugin pbxt soname 'llibpbxt.so'; I would get an error saying that I should have compiled with HAVE_DLOPEN ERROR 1289 (HY000): The 'plugin' feature is disabled; you need MySQL built with 'HAVE_DLOPEN' to have it working So I have re-compiled mysql server including -DHAVE_DLOPEN (e.g. ./configure CC=gcc CFLAGS=-DBIG_JOINS=1 -DHAVE_DLOPEN -O2 CXX=g++ CXXFLAGS=-DBIG_JOINS=1 -DHAVE_DLOPEN -felide-constructors -fno-rtti -O2 --prefix=/usr/local/mysql-5.1.21 --localstatedir=/usr/local/mysql-5.1.21/data --libexecdir=/usr/local/mysql-5.1.21/bin --datadir=/usr/local/mysql-5.1.21/data -with-comment=Debian x86_64 --with-server-suffix=Debian x86_64 --enable-shared --enable-static --enable-thread-safe-client --enable-assembler --enable-local-infile --with-big-tables --with-raid --with-mysqld-user=mysql --with-libwrap --with-mysqld-ldflags=-all-static --with-vio --with-bench --with-readline --with-extra-charsets=all --with-innodb --with-isam --with-archive-storage-engine --with-csv-storage-engine --with-federated-storage-engine --with-embedded-privilege-control --with-zlib-dir=bundled --with-ssl=bundled --with-partition --with-extra-charsets=complex --with-plugins=max-no-ndb --with-embedded-server) but unfortunately I get an error during the compilation mysqld.o: In function `set_user(char const*, passwd*)': mysqld.cc:(.text+0x406f): warning: Using 'initgroups' in statically linked applications requires at runtime the shared libraries from the glibc version used for linking mysqld.o: In function `main': mysqld.cc:(.text+0x558d): warning: Using 'getpwnam' in statically linked applications requires at runtime the shared libraries from the glibc version used for linking mysqld.cc:(.text+0x5949): warning: Using 'getpwuid' in statically linked applications requires at runtime the shared libraries from the glibc version used for linking ../mysys/libmysys.a(mf_pack.o): In function `unpack_dirname': mf_pack.c:(.text+0x485): warning: Using 'endpwent' in statically linked applications requires at runtime the shared libraries from the glibc version used for linking hostname.o: In function `ip_to_hostname(in_addr*, unsigned int*)': hostname.cc:(.text+0x391): warning: Using 'gethostbyaddr' in statically linked applications requires at runtime the shared libraries from the glibc version used for linking mysqld.o: In function `mysqld_get_one_option': mysqld.cc:(.text+0x5b19): warning: Using 'gethostbyname' in statically linked applications requires at runtime the shared libraries from the glibc version used for linking ../mysys/libmysys.a(my_gethostbyname.o): In function `my_gethostbyname_r': my_gethostbyname.c:(.text+0x10): warning: Using 'gethostbyname_r' in statically linked applications requires at runtime the shared libraries from the glibc version used for linking mysqld.o: In function `set_ports()': mysqld.cc:(.text+0x292d): warning: Using 'getservbyname' in statically linked applications requires at runtime the shared libraries from the glibc version used for linking sql_udf.o: In function `free_udf(st_udf_func*)': sql_udf.cc:(.text+0x124): undefined reference to `dlclose' sql_udf.o: In function `init_syms(st_udf_func*, char*)': sql_udf.cc:(.text+0x151): undefined reference to `dlsym' sql_udf.cc:(.text+0x18e): undefined reference to `dlsym' sql_udf.cc:(.text+0x1ae): undefined reference to `dlsym' sql_udf.cc:(.text+0x1d1): undefined reference to `dlsym' sql_udf.cc:(.text+0x1ee): undefined reference to `dlsym' sql_udf.o: In function `udf_free()': sql_udf.cc:(.text+0x4a8): undefined reference to `dlclose' sql_udf.o: In function `udf_init()': sql_udf.cc:(.text+0x92a): undefined reference to `dlclose' sql_udf.cc:(.text+0x9e1): undefined reference to `dlopen' sql_udf.o: In function `mysql_drop_function(THD*, st_mysql_lex_string const*)': sql_udf.cc:(.text+0xc46): undefined reference to `dlclose' sql_udf.o: In function `mysql_create_function(THD*, st_udf_func*)': sql_udf.cc:(.text+0xe35): undefined reference to `dlclose
remove me from this list please
Please remove me from this list or tell me how I can do this procedure... Thanks... mail2web.com Enhanced email for the mobile individual based on Microsoft® Exchange - http://link.mail2web.com/Personal/EnhancedEmail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [MySQL] remove me from this list please
[EMAIL PROTECTED] wrote: Please remove me from this list or tell me how I can do this procedure... Thanks... Have you noticed what the footer of each message says? MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Note I edited the above to include your e-mail address (instead of mine). Each subscriber will have his or her e-mail address in that footer. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
