Please Help. selectcol_arrayref problem

[Richard Reina]

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

[Richard Reina]

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

[Larry Martell]

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

[Stillman, Benjamin]

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: Fwd: Query take too long time - please help!

[Rick James]

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!

[Darek Maciera]

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!

[Ananda Kumar]

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!

[Darek Maciera]

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!

[Ananda Kumar]

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!

[Carsten Pedersen]

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

Re: Slow query - please help

[Tompkins Neil]

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

[Tompkins Neil]

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

[Tompkins Neil]

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

[Johnny Withers]

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

[Tompkins Neil]

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

[Neil Tompkins]

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

[Johnny Withers]

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

[Tompkins Neil]

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

[Tompkins Neil]

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`),
  KEY `IDX_language_code` (`language_code`),
  KEY `IDX_latitude` (`latitude`),
  KEY `IDX_longitude` (`longitude`),
  KEY `IDX_name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

On Fri, Sep 30, 2011 at 6:08 PM, mark carson mcar...@pixie.co.za wrote:

 Hi

 You need version of mysql and table/key/index layout in order to get 

RE: Please help me.

[Muthukumar Selvarasu]

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.

[Valentin Ionescu]

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.

[Gary Smith]

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

Re: can some please help me -- REPLICATION

[Moon's Father]

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

[Brown, Charles]

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

[Jim Lyons]

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

[David Giragosian]

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

[Brown, Charles]

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

[Brown, Charles]

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]

Re: ODBC 3.51.22 problem - please help

[Jim Winstead]

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

[Ed Reed]

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

Please help to solve a serios problem

[Ali Nebi]

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

[hwigoda]

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

[Ali Nebi]

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

[Russell E Glaue]

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

[Ali Nebi]

- 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]

Re: Slow query examining 10 Million Rows, please help !!!

[Brent Baisley]

As Dan mentioned, you're searching on the 'tag' field which has no  
index. But since that field is in the table you're joining on, adding  
an index on it might not help. You actually searching on the tag_id  
in the join field, not the 'tag'.
Add an index on 'object_type' in the freetagged_objects table since  
you're searching on object_type=1. You're doing a full table scan on  
that table as indicated my the explain.



On Jun 19, 2007, at 6:20 PM, Kishore Jalleda wrote:


Hi everybody,
  we have this super slow query which is going  
through more

than 10 million rows to retrieve results, here is the query and other
information, I tried a few things to make this faster , but  
failed , so any

help from you guys in making this faster is greatly appreciated 

# Query_time: 10  Lock_time: 0  Rows_sent: 1  Rows_examined: 11863498
SELECT DISTINCT object_id
   FROM freetagged_objects INNER JOIN freetags ON (tag_id =  
id)

   WHERE tag = 'shot'

   AND object_type = 1
   ORDER BY object_id ASC
   LIMIT 0, 10

explain gives the following output

++-+++--- 
+-+-+-- 
+-+--+
| id | select_type | table  | type   | possible_keys |  
key |

key_len | ref  | rows|
Extra|
++-+++--- 
+-+-+-- 
+-+--+
|  1 | SIMPLE  | freetagged_objects | ALL| PRIMARY   |  
NULL
|NULL | NULL | 9079381 | Using  
where;

Using temporary; Using filesort |
|  1 | SIMPLE  | freetags   | eq_ref | PRIMARY   |  
PRIMARY
|   4 | osCommerce.freetagged_objects.tag_id |   1 | Using  
where;

Distinct|
++-+++--- 
+-+-+-- 
+-+--+



mysql show create table freetagged_objects;

| freetagged_objects | CREATE TABLE `freetagged_objects` (
 `tag_id` int(11) unsigned NOT NULL default '0',
 `tagger_id` int(11) unsigned NOT NULL default '0',
 `object_id` int(11) unsigned NOT NULL default '0',
 `tagged_on` datetime NOT NULL default '-00-00 00:00:00',
 `object_type` int(11) NOT NULL default '0',
 PRIMARY KEY  (`tag_id`,`tagger_id`,`object_id`),
 KEY `tagger_id_index` (`tagger_id`),
 KEY `object_id_tagger_id_index` (`object_id`,`tagger_id`),
 KEY `object_id_tag_id_index` (`object_id`,`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |


mysql show create table freetags;

| freetags | CREATE TABLE `freetags` (
 `id` int(11) unsigned NOT NULL auto_increment,
 `tag` varchar(30) NOT NULL default '',
 `raw_tag` varchar(50) NOT NULL default '',
 `suppress` tinyint(1) NOT NULL default '0',
 PRIMARY KEY  (`id`),
 KEY `raw_tag` (`raw_tag`(10))
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |


Freetags table has like a million rows in it 
MySQL version 4.1.11 , server has 16GB RAM ..

Kishore Jalleda
http://kjalleda.googlepages.com/mysqlprojects



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Slow query examining 10 Million Rows, please help !!!

[Kishore Jalleda]

Yes I already did try adding an index on tag, but as you said it didn't work
as its using the primary key from the freetags table for the join , anyway I
will try adding an index on object_type, and see if that helps ...

Thanks
Kishore Jalleda
http://kjalleda.googlepages.com

On 6/20/07, Brent Baisley [EMAIL PROTECTED] wrote:


As Dan mentioned, you're searching on the 'tag' field which has no
index. But since that field is in the table you're joining on, adding
an index on it might not help. You actually searching on the tag_id
in the join field, not the 'tag'.
Add an index on 'object_type' in the freetagged_objects table since
you're searching on object_type=1. You're doing a full table scan on
that table as indicated my the explain.


On Jun 19, 2007, at 6:20 PM, Kishore Jalleda wrote:

 Hi everybody,
   we have this super slow query which is going
 through more
 than 10 million rows to retrieve results, here is the query and other
 information, I tried a few things to make this faster , but
 failed , so any
 help from you guys in making this faster is greatly appreciated 

 # Query_time: 10  Lock_time: 0  Rows_sent: 1  Rows_examined: 11863498
 SELECT DISTINCT object_id
FROM freetagged_objects INNER JOIN freetags ON (tag_id =
 id)
WHERE tag = 'shot'

AND object_type = 1
ORDER BY object_id ASC
LIMIT 0, 10

 explain gives the following output

 ++-+++---
 +-+-+--
 +-+--+
 | id | select_type | table  | type   | possible_keys |
 key |
 key_len | ref  | rows|
 Extra|
 ++-+++---
 +-+-+--
 +-+--+
 |  1 | SIMPLE  | freetagged_objects | ALL| PRIMARY   |
 NULL
 |NULL | NULL | 9079381 | Using
 where;
 Using temporary; Using filesort |
 |  1 | SIMPLE  | freetags   | eq_ref | PRIMARY   |
 PRIMARY
 |   4 | osCommerce.freetagged_objects.tag_id |   1 | Using
 where;
 Distinct|
 ++-+++---
 +-+-+--
 +-+--+


 mysql show create table freetagged_objects;

 | freetagged_objects | CREATE TABLE `freetagged_objects` (
  `tag_id` int(11) unsigned NOT NULL default '0',
  `tagger_id` int(11) unsigned NOT NULL default '0',
  `object_id` int(11) unsigned NOT NULL default '0',
  `tagged_on` datetime NOT NULL default '-00-00 00:00:00',
  `object_type` int(11) NOT NULL default '0',
  PRIMARY KEY  (`tag_id`,`tagger_id`,`object_id`),
  KEY `tagger_id_index` (`tagger_id`),
  KEY `object_id_tagger_id_index` (`object_id`,`tagger_id`),
  KEY `object_id_tag_id_index` (`object_id`,`tag_id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |


 mysql show create table freetags;

 | freetags | CREATE TABLE `freetags` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `tag` varchar(30) NOT NULL default '',
  `raw_tag` varchar(50) NOT NULL default '',
  `suppress` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `raw_tag` (`raw_tag`(10))
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |


 Freetags table has like a million rows in it 
 MySQL version 4.1.11 , server has 16GB RAM ..

 Kishore Jalleda
 http://kjalleda.googlepages.com/mysqlprojects

RE: Slow query examining 10 Million Rows, please help !!!

[Robert DiFalco]

Could also be the DISTINCT processing depending on the number of dups
and the fields in the result set that must be sorted to perform the
distinct operation. Normally if there were a lot of dupes I would
suggest a sub-query but that is not a great option for MySQL. 

-Original Message-
From: Kishore Jalleda [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, June 20, 2007 10:23 AM
To: Brent Baisley
Cc: mysql@lists.mysql.com
Subject: Re: Slow query examining 10 Million Rows, please help !!!

Yes I already did try adding an index on tag, but as you said it didn't
work as its using the primary key from the freetags table for the join ,
anyway I will try adding an index on object_type, and see if that
helps ...

Thanks
Kishore Jalleda
http://kjalleda.googlepages.com

On 6/20/07, Brent Baisley [EMAIL PROTECTED] wrote:

 As Dan mentioned, you're searching on the 'tag' field which has no 
 index. But since that field is in the table you're joining on, adding 
 an index on it might not help. You actually searching on the tag_id in

 the join field, not the 'tag'.
 Add an index on 'object_type' in the freetagged_objects table since 
 you're searching on object_type=1. You're doing a full table scan on 
 that table as indicated my the explain.


 On Jun 19, 2007, at 6:20 PM, Kishore Jalleda wrote:

  Hi everybody,
we have this super slow query which is going 
  through more than 10 million rows to retrieve results, here is the 
  query and other information, I tried a few things to make this 
  faster , but failed , so any help from you guys in making this 
  faster is greatly appreciated 
 
  # Query_time: 10  Lock_time: 0  Rows_sent: 1  Rows_examined: 
  11863498 SELECT DISTINCT object_id
 FROM freetagged_objects INNER JOIN freetags ON (tag_id =
  id)
 WHERE tag = 'shot'
 
 AND object_type = 1
 ORDER BY object_id ASC
 LIMIT 0, 10
 
  explain gives the following output
 
  ++-+++---
  +-+-+--
  +-+--+
  | id | select_type | table  | type   | possible_keys |
  key |
  key_len | ref  | rows|
  Extra|
  ++-+++---
  +-+-+--
  +-+--+
  |  1 | SIMPLE  | freetagged_objects | ALL| PRIMARY   |
  NULL
  |NULL | NULL | 9079381 | Using
  where;
  Using temporary; Using filesort |
  |  1 | SIMPLE  | freetags   | eq_ref | PRIMARY   |
  PRIMARY
  |   4 | osCommerce.freetagged_objects.tag_id |   1 | Using
  where;
  Distinct|
  ++-+++---
  +-+-+--
  +-+--+
 
 
  mysql show create table freetagged_objects;
 
  | freetagged_objects | CREATE TABLE `freetagged_objects` (
   `tag_id` int(11) unsigned NOT NULL default '0',  `tagger_id` 
  int(11) unsigned NOT NULL default '0',  `object_id` int(11) unsigned

  NOT NULL default '0',  `tagged_on` datetime NOT NULL default 
  '-00-00 00:00:00',  `object_type` int(11) NOT NULL default '0',

  PRIMARY KEY  (`tag_id`,`tagger_id`,`object_id`),
   KEY `tagger_id_index` (`tagger_id`),  KEY 
  `object_id_tagger_id_index` (`object_id`,`tagger_id`),  KEY 
  `object_id_tag_id_index` (`object_id`,`tag_id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 
 
  mysql show create table freetags;
 
  | freetags | CREATE TABLE `freetags` (
   `id` int(11) unsigned NOT NULL auto_increment,  `tag` varchar(30) 
  NOT NULL default '',  `raw_tag` varchar(50) NOT NULL default '',  
  `suppress` tinyint(1) NOT NULL default '0',  PRIMARY KEY  (`id`),  
  KEY `raw_tag` (`raw_tag`(10))
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 
 
  Freetags table has like a million rows in it 
  MySQL version 4.1.11 , server has 16GB RAM ..
 
  Kishore Jalleda
  http://kjalleda.googlepages.com/mysqlprojects




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Slow query examining 10 Million Rows, please help !!!

[Kishore Jalleda]

Hi everybody,
  we have this super slow query which is going through more
than 10 million rows to retrieve results, here is the query and other
information, I tried a few things to make this faster , but failed , so any
help from you guys in making this faster is greatly appreciated 

# Query_time: 10  Lock_time: 0  Rows_sent: 1  Rows_examined: 11863498
SELECT DISTINCT object_id
   FROM freetagged_objects INNER JOIN freetags ON (tag_id = id)
   WHERE tag = 'shot'

   AND object_type = 1
   ORDER BY object_id ASC
   LIMIT 0, 10

explain gives the following output

++-+++---+-+-+--+-+--+
| id | select_type | table  | type   | possible_keys | key |
key_len | ref  | rows|
Extra|
++-+++---+-+-+--+-+--+
|  1 | SIMPLE  | freetagged_objects | ALL| PRIMARY   | NULL
|NULL | NULL | 9079381 | Using where;
Using temporary; Using filesort |
|  1 | SIMPLE  | freetags   | eq_ref | PRIMARY   | PRIMARY
|   4 | osCommerce.freetagged_objects.tag_id |   1 | Using where;
Distinct|
++-+++---+-+-+--+-+--+


mysql show create table freetagged_objects;

| freetagged_objects | CREATE TABLE `freetagged_objects` (
 `tag_id` int(11) unsigned NOT NULL default '0',
 `tagger_id` int(11) unsigned NOT NULL default '0',
 `object_id` int(11) unsigned NOT NULL default '0',
 `tagged_on` datetime NOT NULL default '-00-00 00:00:00',
 `object_type` int(11) NOT NULL default '0',
 PRIMARY KEY  (`tag_id`,`tagger_id`,`object_id`),
 KEY `tagger_id_index` (`tagger_id`),
 KEY `object_id_tagger_id_index` (`object_id`,`tagger_id`),
 KEY `object_id_tag_id_index` (`object_id`,`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |


mysql show create table freetags;

| freetags | CREATE TABLE `freetags` (
 `id` int(11) unsigned NOT NULL auto_increment,
 `tag` varchar(30) NOT NULL default '',
 `raw_tag` varchar(50) NOT NULL default '',
 `suppress` tinyint(1) NOT NULL default '0',
 PRIMARY KEY  (`id`),
 KEY `raw_tag` (`raw_tag`(10))
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |


Freetags table has like a million rows in it 
MySQL version 4.1.11 , server has 16GB RAM ..

Kishore Jalleda
http://kjalleda.googlepages.com/mysqlprojects

Re: Slow query examining 10 Million Rows, please help !!!

[Dan Buettner]

I would try adding an index on the freetags.tag column as you are querying
against that column with
 WHERE tag = 'shot'

HTH,
Dan


On 6/19/07, Kishore Jalleda [EMAIL PROTECTED] wrote:


Hi everybody,
   we have this super slow query which is going through
more
than 10 million rows to retrieve results, here is the query and other
information, I tried a few things to make this faster , but failed , so
any
help from you guys in making this faster is greatly appreciated 

# Query_time: 10  Lock_time: 0  Rows_sent: 1  Rows_examined: 11863498
SELECT DISTINCT object_id
FROM freetagged_objects INNER JOIN freetags ON (tag_id = id)
WHERE tag = 'shot'

AND object_type = 1
ORDER BY object_id ASC
LIMIT 0, 10

explain gives the following output


++-+++---+-+-+--+-+--+
| id | select_type | table  | type   | possible_keys | key
|
key_len | ref  | rows|
Extra|

++-+++---+-+-+--+-+--+
|  1 | SIMPLE  | freetagged_objects | ALL| PRIMARY   | NULL
|NULL | NULL | 9079381 | Using where;
Using temporary; Using filesort |
|  1 | SIMPLE  | freetags   | eq_ref | PRIMARY   | PRIMARY
|   4 | osCommerce.freetagged_objects.tag_id |   1 | Using where;
Distinct|

++-+++---+-+-+--+-+--+


mysql show create table freetagged_objects;

| freetagged_objects | CREATE TABLE `freetagged_objects` (
  `tag_id` int(11) unsigned NOT NULL default '0',
  `tagger_id` int(11) unsigned NOT NULL default '0',
  `object_id` int(11) unsigned NOT NULL default '0',
  `tagged_on` datetime NOT NULL default '-00-00 00:00:00',
  `object_type` int(11) NOT NULL default '0',
  PRIMARY KEY  (`tag_id`,`tagger_id`,`object_id`),
  KEY `tagger_id_index` (`tagger_id`),
  KEY `object_id_tagger_id_index` (`object_id`,`tagger_id`),
  KEY `object_id_tag_id_index` (`object_id`,`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |


mysql show create table freetags;

| freetags | CREATE TABLE `freetags` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `tag` varchar(30) NOT NULL default '',
  `raw_tag` varchar(50) NOT NULL default '',
  `suppress` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `raw_tag` (`raw_tag`(10))
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |


Freetags table has like a million rows in it 
MySQL version 4.1.11 , server has 16GB RAM ..

Kishore Jalleda
http://kjalleda.googlepages.com/mysqlprojects

please help reading DB deadlock notice

[Nikita Tovstoles]

Hi,

I'd really appreciate help with reading this db deadlock notice. 
Essentially, I'd like to understand:
-what holds the lock that TX2 is waiting on index `token` of table 
`eviltwin/user_sessions`? I thought it'd be TX1 (hence deadlock ?), yet 
it appears that TX1 holds no locks
-if the answer to the above cannot be found in attached report, is there 
a setting I should turn on the server to get the necessary info next 
time this deadlock occurs?


Our setup: MySQL 5, InnoDB, Repeatable Read

relevant columns in table user_sessions:
-id (PK)
-token (unique, nullable)
-serverSessionId (FK, nullable)

TX1 does:
-select for update on a given 'token'
-set serverSessionId to null

TX2 does:
-select for update on the same 'token'
-delete selected record

TX2 seems to start a little earlier, and succeed on 'select for update'. 
TX1 then tries to do the same and is forced to wait on a lock. This I 
understand.
But why does TX2 need to wait on a lock to deleted already 'selected for 
update' user_session? Who's holding that lock?


thanks,
-nikita

*** 1. row ***
Status: 
=

070402 12:24:38 INNODB MONITOR OUTPUT
=
Per second averages calculated from the last 29 seconds
--
SEMAPHORES
--
OS WAIT ARRAY INFO: reservation count 6313003, signal count 5099409
Mutex spin waits 819355967, rounds 1424470313, OS waits 4424501
RW-shared spins 1000685, OS waits 629116; RW-excl spins 760423, OS waits 175362

LATEST DETECTED DEADLOCK

070402 12:22:41
*** (1) TRANSACTION:
TRANSACTION 0 12012950, ACTIVE 0 sec, process no 7328, OS thread id 1161120096 
starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1216
MySQL thread id 93849, query id 1913636508 lf20.eviltwinstudios.net 
192.168.100.163 eviltwin Sending data
select usersessio0_.id as id37_, usersessio0_.hibernateVersion as 
hibernat2_37_, usersessio0_.serverSessionId as serverSe3_37_, 
usersessio0_.userId as userId37_, usersessio0_.loginTime as loginTime37_, 
usersessio0_.logoutTime as logoutTime37_, usersessio0_.boot as boot37_, 
usersessio0_.token as token37_, usersessio0_.macAddressMD5 as macAddre9_37_, 
usersessio0_.expirationTime as expirat10_37_, usersessio0_.creationDate as 
creatio11_37_ from user_sessions usersessio0_ where 
usersessio0_.token='82ff6193-1216-449a-9e33-5426fb8e10ef' for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 130062 n bits 144 index `PRIMARY` of table 
`eviltwin/user_sessions` trx id 0 12012950 lock_mode X locks rec but not gap 
waiting
Record lock, heap no 9 PHYSICAL RECORD: n_fields 13; compact format; info bits 
32
0: len 30; hex 37653530373061382d393537362d343363352d623138342d656266633763; asc 
7e5070a8-9576-43c5-b184-ebfc7c;...(truncated); 1: len 6; hex 00b74d95; asc 
M ;; 2: len 7; hex 00803c29ee; asc ) ;; 3: len 4; hex 8001; asc
 ;; 4: len 30; hex 39373939383430622d323938372d346630382d613539342d663736303831; 
asc 9799840b-2987-4f08-a594-f76081;...(truncated); 5: len 30; hex 
34343431316237362d386663312d343332652d386332342d653837646433; asc 
44411b76-8fc1-432e-8c24-e87dd3;...(truncated); 6: len 8; hex 80001241013060af; asc 
   A 0` ;; 7: SQL NULL; 8: len 1; hex 00; asc  ;; 9: len 30; hex 
3832363139332d313231362d343439612d39652d35343232; asc 
82ff6193-1216-449a-9e33-5426fb;...(truncated); 10: len 30; hex 
62653637616563323661316363613261646561616135373430646130; asc 
be67aec26a1cca2adeffaaa5740da0;...(truncated); 11: len 8; hex 80001241014ee557; 
ascA N W;; 12: len 8; hex 80001241013060af; ascA 0` ;;

*** (2) TRANSACTION:
TRANSACTION 0 12012949, ACTIVE 0 sec, process no 7328, OS thread id 1182153056 
updating or deleting, thread declared inside InnoDB 499
mysql tables in use 1, locked 1
8 lock struct(s), heap size 1216, undo log entries 3
MySQL thread id 93773, query id 1913636516 lf20.eviltwinstudios.net 
192.168.100.163 eviltwin updating
delete from user_sessions where id='7e5070a8-9576-43c5-b184-ebfc7c288d69' and 
hibernateVersion=1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 130062 n bits 144 index `PRIMARY` of table 
`eviltwin/user_sessions` trx id 0 12012949 lock_mode X locks rec but not gap
Record lock, heap no 9 PHYSICAL RECORD: n_fields 13; compact format; info bits 
32
0: len 30; hex 37653530373061382d393537362d343363352d623138342d656266633763; asc 
7e5070a8-9576-43c5-b184-ebfc7c;...(truncated); 1: len 6; hex 00b74d95; asc 
M ;; 2: len 7; hex 00803c29ee; asc ) ;; 3: len 4; hex 8001; asc
 ;; 4: len 30; hex 39373939383430622d323938372d346630382d613539342d663736303831; 
asc 9799840b-2987-4f08-a594-f76081;...(truncated); 5: len 30; hex 
34343431316237362d386663312d343332652d386332342d653837646433; asc 
44411b76-8fc1-432e-8c24-e87dd3;...(truncated); 6: len 8; hex 80001241013060af; asc 
 

Re: please help reading DB deadlock notice

[Maciej Dobrzanski]

Nikita Tovstoles [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]

Do both of your SELECT statements use the same index (`token`) when setting
locks on the rows? My guess is that the situation is as follows.

TX2: SELECT ... WHERE id = ... FOR UPDATE
TX2 sets an exclusive lock for a row on PRIMARY index.

TX1: SELECT ... WHERE token = ... FOR UPDATE
TX1 tries to acquire X-lock for a row in `token` index, which succedes. This
however requires also a lock on PRIMARY, but the corresponding row on that
index is already locked by TX2. TX1 waits.

TX2: DELETE ... WHERE id = ...
TX2 tries to delete the row. This of course requires the removal of its
presence in the indxexes, including `token`, but because of the earlier
exclusive lock set by TX1 this cannot be accomplished without waiting for
the lock to be freed. TX2 waits.

Hence, the deadlock.


I'm wondering if granting X-locks for the secondary indexes could not be
delayed until such lock on the primary key is acquired...?

Maciek


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Please help*

[Colin Charles]

VeeJay wrote:


How and at What place, can I configure the Makefile under
/usr/ports/databases/mysql50-server/
to have MySQL server working with above mentioned configurations?


Why not just use MySQL that comes from ports?

If you really want to build from source, read:
http://dev.mysql.com/doc/refman/5.0/en/installing-source.html

--
Colin Charles, Community Engineer
MySQL AB, Melbourne, Australia, www.mysql.com
Mobile: +614 12 593 292 / Skype: colincharles
Web: http://www.bytebot.net/blog/

MySQL Forge: http://forge.mysql.com/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

FreeBSD chroot problem...Please help!!!

[VeeJay]

Hello there

I was wondering if someone having great knowledge of FreeBSD chroot system
could give me some help. I trying to run MySQL and Apache under a chroot
envoirnment. But I am getting these errors. Could you please help me to
solve these problems. I have googled a lot but did't find any solution.
Please help!


1. When running MySQL 5.0 in a chroot envoirnment, I get following error:

# chrootuid /chroot/mysql mysql /usr/local/mysql/libexec/mysqld 
[1] 599
# /usr/local/mysql/libexec/mysqld: Can't create/write to file
'/var/tmp/ibPM3e0d' (Errcode: 13)
070107 10:43:49  InnoDB: Error: unable to create temporary file; errno: 13
070107 10:43:49 [Note] /usr/local/mysql/libexec/mysqld: ready for
connections.
Version: '5.0.27'  socket: '/tmp/mysql.sock'  port: 0  FreeBSD port:
mysql-server-5.0.27



2. When running Apache in chroot envoirnment, I get following error:

# chroot /chroot/httpd /usr/local/apache/bin/httpd
fopen: Operation not supported
httpd: could not open document config file /dev/null
I will really appreciate your kind help!

--
Thanks!

BR / vj

Re: FreeBSD chroot problem...Please help!!!

[Riemer Palstra]

On Sun, Jan 07, 2007 at 12:50:34PM +0100, VeeJay wrote:
 I trying to run MySQL and Apache under a chroot envoirnment. But I am
 getting these errors.

[ ... ]

 # /usr/local/mysql/libexec/mysqld: Can't create/write to file
 '/var/tmp/ibPM3e0d' (Errcode: 13)

[ ... ]

 fopen: Operation not supported
 httpd: could not open document config file /dev/null

Are these files/directories/device nodes present in their respective
chroot environment?

-- 
Riemer PalstraAmsterdam, The Netherlands
[EMAIL PROTECTED]http://www.palstra.com/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Please help*

[VeeJay]

I am a novice with Unix and user of MySQL on windows…..



I have a problem, i.e.



I want to install MySQL5.0 at my FreeBSD 6.1 box with following
configurations:



--prefix=/usr/local/mysql

--with-mysqld-user=mysql

--with-unix-socket-path=/tmp/mysql.sock

--with-mysqld-ldflags=-all-static

How and at What place, can I configure the Makefile under
/usr/ports/databases/mysql50-server/
to have MySQL server working with above mentioned configurations?

--
Thanks!

BR / vj

help please, help please

[albert abongwa]

I am not able to install Mysql on my 2003 server at home.
  error 1045!!
  I get an accesd denied for user [EMAIL PROTECTED]
  my firewall is off and I know it is not the port becuase when I configure it 
as 'anonymous it works like a charm. Please help me


-
Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls.  Great rates 
starting at 1¢/min.

Re: help please, help please

[Dave Heath]

I am not able to install Mysql on my 2003 server at home.
  error 1045!!
  I get an accesd denied for user [EMAIL PROTECTED]


I need the full error, but have some questions:

1. Are you sure that you set a root password?
2. Are you using the command line client or some other administrative tool?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Please Help - Stored Procedure Issue

[Jesse]

Aaah.  OK. That's what the @ stands for.  I could not figure out what the 
@ was for, and I posted a message on the list yesterday and did not receive 
a response.  You're right. That solved the problem.


Thanks,
Jesse

- Original Message - 
From: Chris White [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Tuesday, June 27, 2006 5:15 PM
Subject: Re: Please Help - Stored Procedure Issue



On Tuesday 27 June 2006 01:52 pm, Jesse wrote:

@cInvNo
replace all instances of this with just cInvNo.  cInvNo is already 
declared as

an OUT variable, and @cInvNo will be set to that value when you run:

CALL sp_GetNextInv(-1,@cInvNo);
--
Chris White
PHP Programmer/DBBD
Interfuel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Please Help - Stored Procedure Issue

[Jesse]

I have the following stored procedure that I'm using:

DELIMITER $$

DROP PROCEDURE IF EXISTS `bpa`.`sp_GetNextInv` $$
CREATE PROCEDURE `sp_GetNextInv`(
IN nChapterID Int,
OUT cInvNo VarChar(7))
BEGIN
Declare cPrefix VarChar(1);
Declare cNextInv VarChar(7);
Set cInvNo = '';

IF nChapterID  0 THEN
 SELECT TempInvNo INTO cInvNo FROM Chapters WHERE ID=nChapterID;
END IF;

IF (cInvNo = '') or (cInvNo IS NULL) THEN
   SELECT NextInvoiceNo INTO cInvNo FROM Config;
 SET cNextInv = Right('000' + CONVERT(CONVERT(cInvNo, UNSIGNED) + 1, 
CHAR), 7);

   UPDATE Config SET NextInvoiceNo=cNextInv;
   IF nChapterID = -1 THEN
  Set @cInvNo = CONCAT('L',Right(cInvNo,6));
# at this point, it's right.
 END IF;
   IF nChapterID = -2 THEN
  Set @cInvNo = CONCAT('C',Right(cInvNo,6));
 END IF;
   IF nChapterID  0 THEN
  SELECT CT.InvPrefix INTO cPrefix FROM ChapterType CT, Chapters C
   WHERE C.ID=nChapterID AND CT.ChapterType=C.ChapterType;
  Set cInvNo = CONCAT(cPrefix,Right(cInvNo,6));
  UPDATE Chapters SET TempInvNo=cInvNo WHERE ID=nChapterID;
 END IF;
END IF;
END $$

DELIMITER ;

The function should select the next invoice #, and it should have a alpha 
prefix assigned to it.  To debug this, I put SELECT @cInvNo; in the code 
above where it indicates # at this point, it's right.  It properly returns 
'L65973'.  however, in MySQL, I can do the following:


CALL sp_GetNextInv(-1,@cInvNo);
SELECT @cInvNo;

...and it returns 65973.  Does anyone have a clue why it's doing that?

Thanks,
Jesse 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Please Help - Stored Procedure Issue

[Chris White]

On Tuesday 27 June 2006 01:52 pm, Jesse wrote:
 @cInvNo
replace all instances of this with just cInvNo.  cInvNo is already declared as 
an OUT variable, and @cInvNo will be set to that value when you run:

CALL sp_GetNextInv(-1,@cInvNo);
-- 
Chris White
PHP Programmer/DBBD
Interfuel

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

TABLE and VIEW have same IDENTIFIER: PLEASE HELP

[murthy gandikota]

I created a sql file for database sfg as follows:

mysqldump -uuser -ppassword sfg  backup.sql

Then I tried to reload it in another database sfg2

mysql -uuser -ppassword sfg2  backup.sql

I get the error message saying the table already exists. I traced the error to
the lines where the views have the same identifier as the tables. I don't
know if it is possible to have views and tables the same identifier. I didn't
create these views. 

Can someone please help me figure this out?

Thanks
Murthy

 __
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

please help me.

[yuan edit]

my operating system is linux redhat 9.0.
i am installing mysql 5.0.x binary distribution.
Which edition is the most fit in the following editions?
And would you like to tell me the difference among these editions?
Thanks very much!

[image: [ ]] mysql-standard-5.0.22-linux-i686-glibc23.tar.gz
http://mysql.oss.eznetsols.org/Downloads/MySQL-5.0/mysql-standard-5.0.22-linux-i686-glibc23.tar.gz
27-May-2006 02:32   26M  GZIP compressed docume
[image: [TXT]] 
mysql-standard-5.0.22-linux-i686-glibc23.tar.gz.aschttp://mysql.oss.eznetsols.org/Downloads/MySQL-5.0/mysql-standard-5.0.22-linux-i686-glibc23.tar.gz.asc
27-May-2006 08:52  189   GZIP compressed docume
[image: [ ]] 
mysql-standard-5.0.22-linux-i686-glibc23.tar.gz.md5http://mysql.oss.eznetsols.org/Downloads/MySQL-5.0/mysql-standard-5.0.22-linux-i686-glibc23.tar.gz.md5
27-May-2006 02:32   82   GZIP compressed docume
[image: [ ]] 
mysql-standard-5.0.22-linux-i686-icc-glibc23.tar.gzhttp://mysql.oss.eznetsols.org/Downloads/MySQL-5.0/mysql-standard-5.0.22-linux-i686-icc-glibc23.tar.gz
27-May-2006 02:24   24M  GZIP compressed docume
[image: [TXT]] 
mysql-standard-5.0.22-linux-i686-icc-glibc23.tar.gz.aschttp://mysql.oss.eznetsols.org/Downloads/MySQL-5.0/mysql-standard-5.0.22-linux-i686-icc-glibc23.tar.gz.asc
27-May-2006 08:52  189   GZIP compressed docume
[image: [ ]] 
mysql-standard-5.0.22-linux-i686-icc-glibc23.tar.gz.md5http://mysql.oss.eznetsols.org/Downloads/MySQL-5.0/mysql-standard-5.0.22-linux-i686-icc-glibc23.tar.gz.md5
27-May-2006 02:24   86   GZIP compressed docume
[image: [ ]] 
mysql-standard-5.0.22-linux-i686.tar.gzhttp://mysql.oss.eznetsols.org/Downloads/MySQL-5.0/mysql-standard-5.0.22-linux-i686.tar.gz
27-May-2006 07:15   30M  GZIP compressed docume
[image: [TXT]] 
mysql-standard-5.0.22-linux-i686.tar.gz.aschttp://mysql.oss.eznetsols.org/Downloads/MySQL-5.0/mysql-standard-5.0.22-linux-i686.tar.gz.asc
27-May-2006 08:52  189   GZIP compressed docume
[image: [ ]] 
mysql-standard-5.0.22-linux-i686.tar.gz.md5http://mysql.oss.eznetsols.org/Downloads/MySQL-5.0/mysql-standard-5.0.22-linux-i686.tar.gz.md5

Re: please help me.

[Daniel da Veiga]

On 6/2/06, yuan edit [EMAIL PROTECTED] wrote:

 my operating system is linux redhat 9.0.
i am installing mysql 5.0.x binary distribution.
Which edition is the most fit in the following editions?


You notices most of those files are .asc and .md5 used to verify the
integrity of the archive after you download it, right? So, reduced to
3 versions to choose.


And would you like to tell me the difference among these editions?


You can search the MySQL site, and I would advice you to download it
from the site, not from this mirror that I never heard of. If you were
at the site, there are docs explaining the difference between each
version.

mysql-standard-5.0.22-linux-i686-glibc23.tar.gz
Compiled against glibc-2.3

mysql-standard-5.0.22-linux-i686-icc-glibc23.tar.gz
Compiled with ICC?! Dunno, never heard of it and its not at the
official MySQL site.

mysql-standard-5.0.22-linux-i686.tar.gz
Static linked, as far as I know.

I strongly advice you to:

1) Never copy and paste a whole web page in a mail.
2) Strip down what is unrelevant of text
3) Clean html tags
4) Learn about md5 and asc.
5) Be thankful that someone read all this blob and answered
6) Download MySQL from www.mysql.com

--
Daniel da Veiga
Computer Operator - RS - Brazil
-BEGIN GEEK CODE BLOCK-
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
--END GEEK CODE BLOCK--

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: please help optimize this query

[Joseph Alotta]

Thank you for your help.  I will try using chars instead of  
varchars.  That should be fairly
easy to try.  I already do have a table for symbols, though the key  
is varchar not integer.


I was using shares and value as varchar because there was some weird  
rounding happening when I
used decimal, or floats.  It seemed easier to keep them as  
character.  Also the date is part
of the key since for different days there is different values as the  
stock market changes.


I will try char though and see if that makes it faster.

Thank you.



From: DreamWerx [EMAIL PROTECTED]
Date: May 31, 2006 12:25:50 PM CDT
To: Joseph Alotta [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: Re: please help optimize this query


I'd start by looking at your schema.. Quite a lot of use of
varchars...  I'd suggest  using chars (takes more space but processes
faster).. Or even better, normalize the data so that you have a table
for symbols that is linked to this table via an integer based foreign
key.

Also it seems wierd for 'shares' and 'value' to be varchar? I'd think
them to be integer and float types..

Also using date as the PK seems odd..

A lot of times queries perform poorly because of poor database/table  
design.



On 5/31/06, Joseph Alotta [EMAIL PROTECTED] wrote:


Greetings,

I generate this report of all the holdings by symbol, summing up over
accounts.  It is taking much too long.  I was wondering if I can add
an index on something to make it more efficient.  The first query
gets the most recent date, the next query sums it up by symbol.

Thank you for you help,

Joe.






#  mdate = $m.query(select max(date) from positions where symbol = '#
{sym}'
#  and  date = '#{last_date}' ).fetch_row.at(0)
#  this query was taking too long to process.  It has been replace
with the
#  following:

 line = $m.query(select * from positions where symbol = '#
{sym}'
   order by date desc limit 1).fetch_row
 next if line.nil?

 mdate = line.at(4)# fourth position is the date
 next if last_date  mdate

 total = $m.query(select sum(value) from positions where
symbol = '#{sym}'
  and date = '#{mdate}' ).fetch_row.at 
(0).to_f


mysql describe positions;
+-+-+--+-++---+
| Field   | Type| Null | Key | Default| Extra |
+-+-+--+-++---+
| account | varchar(12) |  | PRI ||   |
| symbol  | varchar(12) |  | PRI ||   |
| shares  | varchar(12) | YES  | | NULL   |   |
| value   | varchar(20) | YES  | | NULL   |   |
| date| date|  | PRI | -00-00 |   |
+-+-+--+-++---+
5 rows in set (0.00 sec)





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

please help optimize this query

[Joseph Alotta]

Greetings,

I generate this report of all the holdings by symbol, summing up over  
accounts.  It is taking much too long.  I was wondering if I can add  
an index on something to make it more efficient.  The first query  
gets the most recent date, the next query sums it up by symbol.


Thank you for you help,

Joe.






#  mdate = $m.query(select max(date) from positions where symbol = '# 
{sym}'

#  and  date = '#{last_date}' ).fetch_row.at(0)
#  this query was taking too long to process.  It has been replace  
with the

#  following:

line = $m.query(select * from positions where symbol = '# 
{sym}'

  order by date desc limit 1).fetch_row
next if line.nil?

mdate = line.at(4)# fourth position is the date
next if last_date  mdate

total = $m.query(select sum(value) from positions where  
symbol = '#{sym}'

 and date = '#{mdate}' ).fetch_row.at(0).to_f

mysql describe positions;
+-+-+--+-++---+
| Field   | Type| Null | Key | Default| Extra |
+-+-+--+-++---+
| account | varchar(12) |  | PRI ||   |
| symbol  | varchar(12) |  | PRI ||   |
| shares  | varchar(12) | YES  | | NULL   |   |
| value   | varchar(20) | YES  | | NULL   |   |
| date| date|  | PRI | -00-00 |   |
+-+-+--+-++---+
5 rows in set (0.00 sec)



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: please help optimize this query

[DreamWerx]

I'd start by looking at your schema.. Quite a lot of use of
varchars...  I'd suggest  using chars (takes more space but processes
faster).. Or even better, normalize the data so that you have a table
for symbols that is linked to this table via an integer based foreign
key.

Also it seems wierd for 'shares' and 'value' to be varchar? I'd think
them to be integer and float types..

Also using date as the PK seems odd..

A lot of times queries perform poorly because of poor database/table design.


On 5/31/06, Joseph Alotta [EMAIL PROTECTED] wrote:

Greetings,

I generate this report of all the holdings by symbol, summing up over
accounts.  It is taking much too long.  I was wondering if I can add
an index on something to make it more efficient.  The first query
gets the most recent date, the next query sums it up by symbol.

Thank you for you help,

Joe.






#  mdate = $m.query(select max(date) from positions where symbol = '#
{sym}'
#  and  date = '#{last_date}' ).fetch_row.at(0)
#  this query was taking too long to process.  It has been replace
with the
#  following:

 line = $m.query(select * from positions where symbol = '#
{sym}'
   order by date desc limit 1).fetch_row
 next if line.nil?

 mdate = line.at(4)# fourth position is the date
 next if last_date  mdate

 total = $m.query(select sum(value) from positions where
symbol = '#{sym}'
  and date = '#{mdate}' ).fetch_row.at(0).to_f

mysql describe positions;
+-+-+--+-++---+
| Field   | Type| Null | Key | Default| Extra |
+-+-+--+-++---+
| account | varchar(12) |  | PRI ||   |
| symbol  | varchar(12) |  | PRI ||   |
| shares  | varchar(12) | YES  | | NULL   |   |
| value   | varchar(20) | YES  | | NULL   |   |
| date| date|  | PRI | -00-00 |   |
+-+-+--+-++---+
5 rows in set (0.00 sec)



--
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: totalizing of Rows please help!!

[Nicolas Verhaeghe]

i have three Values in each row of my MySQL database, im needing to sum 
them in the table as they are displayed per Row ??   how do i do this  ?

Projectname Elecremain   Controlremainotherremain   
?php ?
 Project1 2300 1600 
250  ?php  (Sum) ?
 Project2 4300 600   
150 ?php  (Sum) ?




Select
a,b,c,
a+b+c as GrandSum
From
`table`

Try next time to give a name to your table and to use simpler field names,
like a, b, c, etc...

No need to group, here, really. 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

totalizing of Rows please help!!

[Brian E Boothe]

i have three Values in each row of my MySQL database, im needing to sum 
them in the table as they are displayed per Row ??   how do i do this  ?


Projectname Elecremain   Controlremainotherremain   
?php ?
Project1 2300 1600 
250  ?php  (Sum) ?
Project2 4300 600   
150 ?php  (Sum) ?



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: totalizing of Rows please help!!

[Eugene Kosov]

Hi, Brian!

First of all I think next time you should better attach your database 
structure. It'll be much easier to understand your problem if you will...


Maybe you're asking for something like this:
SELECT project_name, elecremain, controlremain, otherremain,  elecremain 
+ controlremain + otherremain AS total FROM table_name;


Brian E Boothe wrote:


i have three Values in each row of my MySQL database, im needing to 
sum them in the table as they are displayed per Row ??   how do i do 
this  ?


Projectname Elecremain   Controlremain
otherremain   ?php ?
Project1 2300 1600 
250  ?php  (Sum) ?
Project2 4300 600   
150 ?php  (Sum) ?






--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: totalizing of Rows please help!!

[Peter Brawley]

Brian,


i have three Values in each row of my MySQL database, im needing to 
sum them in the table as they are displayed per Row ??   how do i do 
this  ?


Projectname Elecremain   Controlremain
otherremain   ?php ?
Project1 2300 1600 
250  ?php  (Sum) ?
Project2 4300 600   
150 ?php  (Sum) ?

In the query, with something like 

SELECT
 elecremain,
 controlremain,
 otherremain,
 elecremain+controlremain+otherremain AS SumRemain
FROM ... etc

PB






--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.5/322 - Release Date: 4/22/2006


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: totalizing of Rows please help!!SQL Dump

[Eugene Kosov]

Please answer to list next time.

Good. Now, what is your Elecremain, Controlremain, otherremain? 
How should they be calculated...


P.S.: Why don't you give a try to some numerical data types? ;) To use 
varchar everythere isn't a right way I think.



Brian E Boothe wrote:

my database structure SQL dump

# phpMyAdmin SQL Dump
# version 2.5.7-pl1
# http://www.phpmyadmin.net
#
# Host: localhost
# Generation Time: Apr 14, 2006 at 12:36 AM
# Server version: 4.0.20
# PHP Version: 4.4.2
#
# Database : `workorder`
#

# 

#
# Table structure for table `orders`
#

CREATE TABLE `orders` (
 `ordernumber` varchar(12) NOT NULL default '',
 `companyname` varchar(12) NOT NULL default '',
 `billingaddress` varchar(12) NOT NULL default '',
 `City` varchar(12) NOT NULL default '',
 `State` varchar(12) default NULL,
 `Zip` varchar(12) NOT NULL default '',
 `PhoneNumber` varchar(12) NOT NULL default '',
 `FaxNumber` varchar(12) NOT NULL default '',
 `WebPage` varchar(12) NOT NULL default '',
 `EmailAddress` varchar(12) NOT NULL default '',
 `Notes` varchar(23) NOT NULL default '',
 `Customer` varchar(12) NOT NULL default '',
 `Startdate` varchar(12) NOT NULL default '',
 `Completedate` varchar(12) NOT NULL default '',
 `Biddate` varchar(12) NOT NULL default '',
 `Bidamount` varchar(12) NOT NULL default '',
 `ElecProjCost` varchar(12) NOT NULL default '',
 `ElecProjBill` varchar(12) NOT NULL default '',
 `ElecRem` varchar(12) NOT NULL default '',
 `CtrlProjCost` varchar(12) NOT NULL default '',
 `CtrlProjBill` varchar(12) NOT NULL default '',
 `CtrlProjrem` varchar(12) NOT NULL default '',
 `OthrProjCost` varchar(12) NOT NULL default '',
 `OthrProjBill` varchar(12) NOT NULL default '',
 `OthrProjrem` varchar(12) NOT NULL default '',
 `BondAm` varchar(7) NOT NULL default '',
 `BondBill` varchar(7) NOT NULL default '',
 `BondRem` varchar(7) NOT NULL default ''
) TYPE=MyISAM;

#
# Dumping data for table `orders`
#

INSERT INTO `orders` VALUES ('4132006', 'Siouxcity SS', '231 Way way', 
'SiouxCity', 'IA', '51101', '712-278-2331', '', 'ssrw.com', 
'[EMAIL PROTECTED]', 'efbebebeftrbbe', 'Siouxcity', '12/2/2006', 
'2/5/2007', '2/4/2007', '123000', '234', '2', '232', '435', '23', '', 
'345', '23', '', '345', '23', '');
INSERT INTO `orders` VALUES ('1232006', 'Sciouscenter', '124323wgwr', 
'Siouxcenter', 'IA', '51101', '712-278-2331', '', '', '', 'this is a 
test project', 'Sioux', '12/2/2006', '2/5/2007', '2/4/2007', '123000', 
'543', '456', '87', '654', '345', '309', '765', '123', '642', '8760', 
'234', '8526');



(---End 
Dump) 




Eugene Kosov wrote:

Hi, Brian!

First of all I think next time you should better attach your database 
structure. It'll be much easier to understand your problem if you 
will...


Maybe you're asking for something like this:
SELECT project_name, elecremain, controlremain, otherremain,  
elecremain + controlremain + otherremain AS total FROM table_name;


Brian E Boothe wrote:


i have three Values in each row of my MySQL database, im needing to 
sum them in the table as they are displayed per Row ??   how do i do 
this  ?


Projectname Elecremain   Controlremain
otherremain   ?php ?
Project1 2300 1600 
250  ?php  (Sum) ?
Project2 4300 600   
150 ?php  (Sum) ?













--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: totalizing of Rows please help!!

[Peter Brawley]

Brian E Boothe wrote:
i have three Values in each row of my MySQL database, im needing to 
sum them in the table as they are displayed per Row ??   how do i do 
this  ?
As Eugene said, VARCHAR values don't sum, since they are not numeric. 
Either cast the column values you wish sums of to a numeric type, or--a 
much better idea--correct your table structure.


PB






--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.5/322 - Release Date: 4/22/2006


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

please help, can not delete database

[Randy Paries]

Hello,
Not sure what is going on
i have mysql  Ver 12.22 Distrib 4.0.17,

I have a database that i can not delete.

i do this
#mysqladmin drop billmax -u admin --password
Enter password:
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'billmax' database [y/N] y
Database billmax dropped

But it does not do anything.

I have tried restarting the DB and restarting the box as well

Please help

Randy

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: please help, can not delete database

[Rhino]

What makes you think the delete of the database failed? It looks like the 
message from the DROP command indicates that the database was dropped 
successfully.


--
Rhino

- Original Message - 
From: Randy Paries [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Saturday, April 22, 2006 9:22 AM
Subject: please help, can not delete database


Hello,
Not sure what is going on
i have mysql  Ver 12.22 Distrib 4.0.17,

I have a database that i can not delete.

i do this
#mysqladmin drop billmax -u admin --password
Enter password:
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'billmax' database [y/N] y
Database billmax dropped

But it does not do anything.

I have tried restarting the DB and restarting the box as well

Please help

Randy

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 21/04/2006




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 21/04/2006


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Fw: please help, can not delete database

[Rhino]

I'm putting this back on the list where it belongs; that enables everyone to 
help and to learn from the discussion, either now or in the future via the 
list archive.


--

Ahh, so you've tried to re-create the database after it appeared to be 
safely dropped! You didn't say that in your note so I wanted to be sure you 
had done that much before writing the note.


Have you looked in the MySQL log to see if it is reporting any problems with 
the DROP? If you are using INNODB, have you tried a SHOW STATUS INNODB to 
see if it reported any problems?


--
Rhino

- Original Message - 
From: Randy Paries [EMAIL PROTECTED]

To: Rhino [EMAIL PROTECTED]
Sent: Saturday, April 22, 2006 11:04 AM
Subject: Re: please help, can not delete database


when i go back and try to create it , it says it already exisit?

Randy

On 4/22/06, Rhino [EMAIL PROTECTED] wrote:

What makes you think the delete of the database failed? It looks like the
message from the DROP command indicates that the database was dropped
successfully.

--
Rhino

- Original Message -
From: Randy Paries [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Saturday, April 22, 2006 9:22 AM
Subject: please help, can not delete database


Hello,
Not sure what is going on
i have mysql  Ver 12.22 Distrib 4.0.17,

I have a database that i can not delete.

i do this
#mysqladmin drop billmax -u admin --password
Enter password:
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'billmax' database [y/N] y
Database billmax dropped

But it does not do anything.

I have tried restarting the DB and restarting the box as well

Please help

Randy

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 21/04/2006




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 21/04/2006





--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 21/04/2006




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.5/321 - Release Date: 21/04/2006


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: please help, can not delete database

[John Hicks]

Randy Paries wrote:

Hello,
Not sure what is going on
i have mysql  Ver 12.22 Distrib 4.0.17,

I have a database that i can not delete.

i do this
#mysqladmin drop billmax -u admin --password
Enter password:
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'billmax' database [y/N] y
Database billmax dropped

But it does not do anything.

I have tried restarting the DB and restarting the box as well

Please help

Randy



Remember that MySQL uses directories to implement databases. When you 
drop a database, it deletes the table files and then tries to delete the 
directory. If the directory remains, even if it is empty, it still looks 
like a databbase to MySQL.


So check the directory that corresponds to this troublesome database for 
 unusual permissions or content (such as some files other than MySQL's 
table files) that may be preventing the deletion of the directory.


(Check /etc/my.cnf for the location of your database directories. RedHat 
usually puts them in /var/lib/mysql.)


Let us know if that was the problem.

--John

PS Remember to reply to the list :)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Summing tables and fields Please help,,,

[Brian E Boothe]

can someone post a snippit on Summing tables together of a feild of the
entire Database ? why does everyone Have such a problem with this 
question? everyone i ask says the same thing,,   let me show what i'm 
needing by Visual representation

 MySQL databaseworkorders
  Table : Orders
  Field(aa) ProjectName --  [enter Projectname]
   Field [a] ElecprojCost  - -Enter in Number
Field [b] ElecprojBilling  --Enter in Number
 Field [c] Elecprojremaining =  [a] - [b]
 Field [d] CtrlprojCost   - -Enter in Number
  Field [e] CtrlprojBilling  - -Enter in 
Number

  Field [f] Ctrlprojremaining =  [d] - [e]
  Field [g] OtherprojCost  - -Enter in Number
Field [h] OtherprojBilling  - -Enter 
in Number

  Field [i] Otherprojremaining =  [g] - [h]
 Field [j] BondedAmount =  -Enter in Number
 Field [k] Bondedprogbilling =  -Enter in Number
  Field [l] Bondedprogremaining =  -= [j] - [ k ]
  Field [m] totalelecworkonhand =  -sum of all remaining = (sum of  C 
all sum records for Values of (C)
 Field [n] totalCtrlworkonhand =  -sum of all remaining = (sum of  F 
all sum records for Values of (F)
 Field [o]totalOtherworkonhand =  -sum of all remaining = (sum of  i 
all sum records for Values of (i)

  Field [p] totalworkonhand =   m + n + o
  Field [Q] totalBondedworkonhand =   sum of all remaining = (sum of  P 
all sum records for Values of (P)


 Any help in all this would be greatly appreaciated,, u can 
also email me via  [EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Summing tables and fields Please help,,,

[Peter Brawley]

Brian,

can someone post
a snippit
on Summing tables together of a feild of the 
entire Database ? 
Do you mean summing
all row
values of a column in a table, grouped by the value of another column?
If so, for example to
retrieve all electprojcost, electprojbilling values and their
differences per
projectname would be ...

SELECT 
 projectname, 
 SUM(electprojcost),
 SUM(elecprojbilling),
 SUM(elexprojcost-electprojbilling)
AS margin
FROM orders
GROUP BY projectname;
Or
do you mean something else?

PB

-

Brian E Boothe wrote:
can
someone post a snippit on Summing tables together of a feild of the
  
entire Database ? why does everyone Have such a problem with this
question? everyone i ask says the same thing,, let me show what i'm
needing by Visual representation
  
 MySQL database  workorders
  
 Table : Orders
  
 Field(aa) ProjectName -- [enter Projectname]
  
 Field [a] ElecprojCost - -Enter in
Number
  
 Field [b] ElecprojBilling --Enter in
Number
  
 Field [c] Elecprojremaining = [a] - [b]
  
 Field [d] CtrlprojCost - -Enter in
Number
  
 Field [e] CtrlprojBilling - -Enter
in Number
  
 Field [f] Ctrlprojremaining = [d] - [e]
  
 Field [g] OtherprojCost - -Enter in
Number
  
 Field [h] OtherprojBilling -
-Enter in Number
  
 Field [i] Otherprojremaining = [g] - [h]
  
Field [j] BondedAmount = -Enter in Number
  
Field [k] Bondedprogbilling = -Enter in Number
  
 Field [l] Bondedprogremaining = -= [j] - [ k ]
  
 Field [m] totalelecworkonhand = -sum of all remaining = (sum of
C all sum records for Values of (C)
  
Field [n] totalCtrlworkonhand = -sum of all remaining = (sum of
F all sum records for Values of (F)
  
Field [o]totalOtherworkonhand = -sum of all remaining = (sum of
i all sum records for Values of (i)
  
 Field [p] totalworkonhand = m + n + o
  
 Field [Q] totalBondedworkonhand = sum of all remaining = (sum of P
all sum records for Values of (P)
  
  
 Any help in all this would be greatly appreaciated,, u can
also email me via [EMAIL PROTECTED]
  
  



No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.3.2/293 - Release Date: 3/26/2006


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Please help: recovering db from crash

[Foo Ji-Haw]

Hi all,

My Windows-based database server crashed (no fault of MySQL. probably OS 
or hardware), and I managed to copy out the data files. I am using 
version 5.0 of the Essentials package.


I tried to install a similar setup on another server, then copy the 
data\ folder over. The MySQL service starts, but I am not able to login, 
even as root.


Is there anyone who can advise me on the recovery steps?

Appreciate your feedback!

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Please help: recovering db from crash

[Mark Leith]

This is actually for Linux/Unix, not Windows.

What error do you get from MySQL when trying to log in? Does the 
mysqld(-nt) process show within Task Manager? What does the new error 
log say?


You may need to reset permissions:

http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

Or you may need to set the appropriate datadir / basdir etc. depending 
on how you set up MySQL:


http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

Best regards

Mark

Adrian Bruce wrote:


not sure, but it may be worth trying the following

run the script:
mysql_install_db --user=root
In the installation dir

this should change ownership and make mysql recognise the data dir.

good luck
Ade

Foo Ji-Haw wrote:


Hi all,

My Windows-based database server crashed (no fault of MySQL. probably 
OS or hardware), and I managed to copy out the data files. I am using 
version 5.0 of the Essentials package.


I tried to install a similar setup on another server, then copy the 
data\ folder over. The MySQL service starts, but I am not able to 
login, even as root.


Is there anyone who can advise me on the recovery steps?

Appreciate your feedback!






--
Mark Leith, Support Engineer
MySQL AB, Worcester, England, www.mysql.com
Are you MySQL certified?  www.mysql.com/certification


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Please help: recovering db from crash

[Adrian Bruce]

not sure, but it may be worth trying the following

run the script:
mysql_install_db --user=root
In the installation dir

this should change ownership and make mysql recognise the data dir.

good luck
Ade

Foo Ji-Haw wrote:


Hi all,

My Windows-based database server crashed (no fault of MySQL. probably 
OS or hardware), and I managed to copy out the data files. I am using 
version 5.0 of the Essentials package.


I tried to install a similar setup on another server, then copy the 
data\ folder over. The MySQL service starts, but I am not able to 
login, even as root.


Is there anyone who can advise me on the recovery steps?

Appreciate your feedback!



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Please help: recovering db from crash

[Foo Ji-Haw]

Thanks for coming to the rescue, Mark and Bruce.

Mark Leith wrote:

This is actually for Linux/Unix, not Windows.

What error do you get from MySQL when trying to log in? Does the 
mysqld(-nt) process show within Task Manager? What does the new error 
log say?


You may need to reset permissions:

http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

Or you may need to set the appropriate datadir / basdir etc. depending 
on how you set up MySQL:


http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

Best regards

Mark

Adrian Bruce wrote:


not sure, but it may be worth trying the following

run the script:
mysql_install_db --user=root
In the installation dir

this should change ownership and make mysql recognise the data dir.

good luck
Ade

Foo Ji-Haw wrote:


Hi all,

My Windows-based database server crashed (no fault of MySQL. 
probably OS or hardware), and I managed to copy out the data files. 
I am using version 5.0 of the Essentials package.


I tried to install a similar setup on another server, then copy the 
data\ folder over. The MySQL service starts, but I am not able to 
login, even as root.


Is there anyone who can advise me on the recovery steps?

Appreciate your feedback!









--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: urgent : PLEASE HELP - problems with back up and restore

[Barak Mery]

Hi,

 

Thanks Again. You are really helping me

 

Well it get a lot of query ok, 0 rows affected 0.00 sec

 

And one like this :

query ok, 2 rows affected 0.01 sec 

Records: 2 Duplicates: 0 Warnings: 0

 

But it look like everything is in the Database now ?

What is this Source Command ?

 

Another thing I noticed is that My Original databes my Sp's Got Delimiter
$$

But on the backup file it replaced with ;;

 

Barak

 

  _  

From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Thursday, November 24, 2005 7:21 AM
To: Barak Mery
Cc: mysql@lists.mysql.com
Subject: RE: urgent : PLEASE HELP - problems with back up and restore

 


I saw both views and stored procedures in the dump file. What error do you
get if you process the script with the source command within the MySQL
CLI? 

mysql -uroot -pmypass 
(login welcome) 
mysql CREATE DATABASE IF NOT EXISTS bcm; 
mysql USE bcm; 
mysql source myback1.sql 

There is another option to drop/create the database. Your dump didn't have
that so you need to do it manually in order to restore it. 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine 

Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 07:18:02 PM:

 Wel I read the manual but didn't find something that I really need to add 
 I suspect it is something with the script itself. 
   
 Now I'm using 
 mysqldump -uroot -pmypass --routines -q -x bcmmyback1.sql 
 
 but after i run 
 mysqldump -uroot -pmypass --routines bcmmyback1.sql 
   
 it now restore the tables but not the views or sp's 
   
 here is the backup file 
   
 thanks 
 Barak 
snipped out backup file 
   
 
 From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
 Sent: Wednesday, November 23, 2005 9:30 PM
 To: Barak Mery
 Cc: mysql@lists.mysql.com
 Subject: RE: urgent : PLEASE HELP - problems with back up and restore 
   
 
 There's a new option added in 5.0.13: 
 
  -R (--routines) 
 
 It's designed designed specifically to dump SPROCs and FUNCTIONs. I 
 thought it would have been turned on by default but I guess it 
 wasn't. Add that to the list of options and check your dump results 
 again. I refer you again to the manual for additional warnings and
cautions. 
 
 http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html 
 
 Shawn Green
 Database Administrator
 Unimin Corporation - Spruce Pine 
 
 
 
 Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 02:23:42 PM:
 
  Well Shawn, 

  First of thanks for the quick result. 

  I tried your suggestion but it didn't help no sp and no restore. 

  It only shrinked the backup file (by deleting the cr). 

  Is there any log file where I can trace that kind of erros ? 

  Barak 



  
  From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
  Sent: Wednesday, November 23, 2005 9:07 PM
  To: Barak Mery
  Cc: mysql@lists.mysql.com
  Subject: Re: urgent : PLEASE HELP - problems with back up and restore 

  
  
  Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 01:51:37 PM:
  
   Hi,
   

   
   I'm really desperate on this on.
   
   After struggling with some really weird bugs I finally finished 
 my project.
   

   
   Buut I can't perform a good backup and restore.
   

   
   I'm using  :
   
   MySql 5.0.16-nt (essentials version)
   
   Windows xp
   
   The DB contains tables, vw's and sp's.
   
   It is a very small one and at the moment I don't have any data inside.
   
   The whole backup file size is 100Kb.
   

   
   1.   I backed up my db using mysql administrator.
   It created a back up file with all tables, views and sp's, but 
 when I tried
   a restore it got errors like.
   Could not handle this statement etc.
   
   
   2.   I tried : mysqldump -uroot -pmypassword dbname  backup.sql
   And then mysqldump -uroot -pmypassword newDbName  backup.sql
   I didn't get any error.
   The shell printed to the screen the backup file completely only 
 without the
   table script part.
   
   It Created a backup file only for the tables (why ? a minute agoI did
the
   backup with the same tool).
   But after restoring, the new db was still empty.
   
   
   3.   I tried to restore with mysql  -uroot -pmypassword newDbName 
   backup.sql
   But I got the same results.
   

   

   
   It should be a very simple and basic issue.
   Why everything is so hard with mysql ?
   
   
   
   Is there any really good and quick forum for mySql ? I posted some new
   thread in mysql.com at the past few days but never got answered.
   

   
   Is it just me or that's the life on the mysql planet ?
   
   
   
   I now try this mailing-list. I hope you can help me.
   

   
   Thanks in advance
   
   Barak
  
  You are in luck as the mailing list is quite active. 
  
  Look at your actual dump file. It is simply a SQL script that will 
  create all of the elements of the database and populate them with 
  data (if you had any). My suspicion is that you have something that 
  isn't quoted that needed

urgent : PLEASE HELP - problems with back up and restore

[Barak Mery]

Hi,

 

I'm really desperate on this on.

After struggling with some really weird bugs I finally finished my project.

 

Buut I can't perform a good backup and restore.

 

I'm using  :

MySql 5.0.16-nt (essentials version)

Windows xp

The DB contains tables, vw's and sp's.

It is a very small one and at the moment I don't have any data inside.

The whole backup file size is 100Kb.

 

1.  I backed up my db using mysql administrator.
It created a back up file with all tables, views and sp's, but when I tried
a restore it got errors like.
Could not handle this statement etc.


2.  I tried : mysqldump -uroot -pmypassword dbname  backup.sql
And then mysqldump -uroot -pmypassword newDbName  backup.sql
I didn't get any error.
The shell printed to the screen the backup file completely only without the
table script part.

It Created a backup file only for the tables (why ? a minute ago I did the
backup with the same tool).
But after restoring, the new db was still empty.


3.  I tried to restore with mysql  -uroot -pmypassword newDbName 
backup.sql
But I got the same results.

 

 

It should be a very simple and basic issue.
Why everything is so hard with mysql ?



Is there any really good and quick forum for mySql ? I posted some new
thread in mysql.com at the past few days but never got answered.

 

Is it just me or that's the life on the mysql planet ?



I now try this mailing-list. I hope you can help me.

 

Thanks in advance

Barak

 

 

Re: urgent : PLEASE HELP - problems with back up and restore

[SGreen]

Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 01:51:37 PM:

 Hi,
 
 
 
 I'm really desperate on this on.
 
 After struggling with some really weird bugs I finally finished my 
project.
 
 
 
 Buut I can't perform a good backup and restore.
 
 
 
 I'm using  :
 
 MySql 5.0.16-nt (essentials version)
 
 Windows xp
 
 The DB contains tables, vw's and sp's.
 
 It is a very small one and at the moment I don't have any data inside.
 
 The whole backup file size is 100Kb.
 
 
 
 1.   I backed up my db using mysql administrator.
 It created a back up file with all tables, views and sp's, but when I 
tried
 a restore it got errors like.
 Could not handle this statement etc.
 
 
 2.   I tried : mysqldump -uroot -pmypassword dbname  backup.sql
 And then mysqldump -uroot -pmypassword newDbName  backup.sql
 I didn't get any error.
 The shell printed to the screen the backup file completely only without 
the
 table script part.
 
 It Created a backup file only for the tables (why ? a minute ago I did 
the
 backup with the same tool).
 But after restoring, the new db was still empty.
 
 
 3.   I tried to restore with mysql  -uroot -pmypassword newDbName 
 backup.sql
 But I got the same results.
 
 
 
 
 
 It should be a very simple and basic issue.
 Why everything is so hard with mysql ?
 
 
 
 Is there any really good and quick forum for mySql ? I posted some new
 thread in mysql.com at the past few days but never got answered.
 
 
 
 Is it just me or that's the life on the mysql planet ?
 
 
 
 I now try this mailing-list. I hope you can help me.
 
 
 
 Thanks in advance
 
 Barak

You are in luck as the mailing list is quite active. 

Look at your actual dump file. It is simply a SQL script that will create 
all of the elements of the database and populate them with data (if you 
had any). My suspicion is that you have something that isn't quoted that 
needed to be.

mysqldump has several options. You can see them with the command: 

mysqldump --help

-or- refer to the manual

http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html

try dumping your database again, this time use the -r= and -Q options. 
That does two things:

a) it avoids adding CR characters at the end of every line
b) it puts backticks around EVERYTHING that needs them (table names, 
column names, etc)


mysqldump -uroot -pmypassword -r backup.sql -Q dbname

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

RE: urgent : PLEASE HELP - problems with back up and restore

[Barak Mery]

Well Shawn, 

 

First of thanks for the quick result.

 

I tried your suggestion but it didn't help no sp and no restore.

 

It only shrinked the backup file (by deleting the cr).

 

Is there any log file where I can trace that kind of erros ?

 

Barak

 

 

 

  _  

From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, November 23, 2005 9:07 PM
To: Barak Mery
Cc: mysql@lists.mysql.com
Subject: Re: urgent : PLEASE HELP - problems with back up and restore

 



Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 01:51:37 PM:

 Hi,
 
  
 
 I'm really desperate on this on.
 
 After struggling with some really weird bugs I finally finished my
project.
 
  
 
 Buut I can't perform a good backup and restore.
 
  
 
 I'm using  :
 
 MySql 5.0.16-nt (essentials version)
 
 Windows xp
 
 The DB contains tables, vw's and sp's.
 
 It is a very small one and at the moment I don't have any data inside.
 
 The whole backup file size is 100Kb.
 
  
 
 1.   I backed up my db using mysql administrator.
 It created a back up file with all tables, views and sp's, but when I
tried
 a restore it got errors like.
 Could not handle this statement etc.
 
 
 2.   I tried : mysqldump -uroot -pmypassword dbname  backup.sql
 And then mysqldump -uroot -pmypassword newDbName  backup.sql
 I didn't get any error.
 The shell printed to the screen the backup file completely only without
the
 table script part.
 
 It Created a backup file only for the tables (why ? a minute ago I did the
 backup with the same tool).
 But after restoring, the new db was still empty.
 
 
 3.   I tried to restore with mysql  -uroot -pmypassword newDbName 
 backup.sql
 But I got the same results.
 
  
 
  
 
 It should be a very simple and basic issue.
 Why everything is so hard with mysql ?
 
 
 
 Is there any really good and quick forum for mySql ? I posted some new
 thread in mysql.com at the past few days but never got answered.
 
  
 
 Is it just me or that's the life on the mysql planet ?
 
 
 
 I now try this mailing-list. I hope you can help me.
 
  
 
 Thanks in advance
 
 Barak

You are in luck as the mailing list is quite active. 

Look at your actual dump file. It is simply a SQL script that will create
all of the elements of the database and populate them with data (if you
had any). My suspicion is that you have something that isn't quoted that
needed to be. 

mysqldump has several options. You can see them with the command: 

mysqldump --help 

-or- refer to the manual 

http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html 

try dumping your database again, this time use the -r= and -Q options. That
does two things: 

a) it avoids adding CR characters at the end of every line 
b) it puts backticks around EVERYTHING that needs them (table names, column
names, etc) 


mysqldump -uroot -pmypassword -r backup.sql -Q dbname 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

RE: urgent : PLEASE HELP - problems with back up and restore

[SGreen]

There's a new option added in 5.0.13:

 -R (--routines) 

It's designed designed specifically to dump SPROCs and FUNCTIONs. I 
thought it would have been turned on by default but I guess it wasn't. Add 
that to the list of options and check your dump results again. I refer you 
again to the manual for additional warnings and cautions.

http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 02:23:42 PM:

 Well Shawn, 
 
 First of thanks for the quick result.
 
 I tried your suggestion but it didn’t help no sp and no restore.
 
 It only shrinked the backup file (by deleting the cr).
 
 Is there any log file where I can trace that kind of erros ?
 
 Barak
 
 
 
 
 From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
 Sent: Wednesday, November 23, 2005 9:07 PM
 To: Barak Mery
 Cc: mysql@lists.mysql.com
 Subject: Re: urgent : PLEASE HELP - problems with back up and restore
 
 
 
 Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 01:51:37 PM:
 
  Hi,
  
  
  
  I'm really desperate on this on.
  
  After struggling with some really weird bugs I finally finished my 
project.
  
  
  
  Buut I can't perform a good backup and restore.
  
  
  
  I'm using  :
  
  MySql 5.0.16-nt (essentials version)
  
  Windows xp
  
  The DB contains tables, vw's and sp's.
  
  It is a very small one and at the moment I don't have any data inside.
  
  The whole backup file size is 100Kb.
  
  
  
  1.   I backed up my db using mysql administrator.
  It created a back up file with all tables, views and sp's, but when I 
tried
  a restore it got errors like.
  Could not handle this statement etc.
  
  
  2.   I tried : mysqldump -uroot -pmypassword dbname  backup.sql
  And then mysqldump -uroot -pmypassword newDbName  backup.sql
  I didn't get any error.
  The shell printed to the screen the backup file completely only 
without the
  table script part.
  
  It Created a backup file only for the tables (why ? a minute ago I did 
the
  backup with the same tool).
  But after restoring, the new db was still empty.
  
  
  3.   I tried to restore with mysql  -uroot -pmypassword newDbName 
  backup.sql
  But I got the same results.
  
  
  
  
  
  It should be a very simple and basic issue.
  Why everything is so hard with mysql ?
  
  
  
  Is there any really good and quick forum for mySql ? I posted some new
  thread in mysql.com at the past few days but never got answered.
  
  
  
  Is it just me or that's the life on the mysql planet ?
  
  
  
  I now try this mailing-list. I hope you can help me.
  
  
  
  Thanks in advance
  
  Barak
 
 You are in luck as the mailing list is quite active. 
 
 Look at your actual dump file. It is simply a SQL script that will 
 create all of the elements of the database and populate them with 
 data (if you had any). My suspicion is that you have something that 
 isn't quoted that needed to be. 
 
 mysqldump has several options. You can see them with the command: 
 
 mysqldump --help 
 
 -or- refer to the manual 
 
 http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html 
 
 try dumping your database again, this time use the -r= and -Q 
 options. That does two things: 
 
 a) it avoids adding CR characters at the end of every line 
 b) it puts backticks around EVERYTHING that needs them (table names,
 column names, etc) 
 
 
 mysqldump -uroot -pmypassword -r backup.sql -Q dbname 
 
 Shawn Green
 Database Administrator
 Unimin Corporation - Spruce Pine

RE: urgent : PLEASE HELP - problems with back up and restore

[Barak Mery]

Thanks again,

 

I will try this and read the manual again.

 

  _  

From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, November 23, 2005 9:30 PM
To: Barak Mery
Cc: mysql@lists.mysql.com
Subject: RE: urgent : PLEASE HELP - problems with back up and restore

 


There's a new option added in 5.0.13: 

 -R (--routines) 

It's designed designed specifically to dump SPROCs and FUNCTIONs. I thought
it would have been turned on by default but I guess it wasn't. Add that to
the list of options and check your dump results again. I refer you again to
the manual for additional warnings and cautions. 

http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine 



Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 02:23:42 PM:

 Well Shawn, 
   
 First of thanks for the quick result. 
   
 I tried your suggestion but it didn't help no sp and no restore. 
   
 It only shrinked the backup file (by deleting the cr). 
   
 Is there any log file where I can trace that kind of erros ? 
   
 Barak 
   
   
   
 
 From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
 Sent: Wednesday, November 23, 2005 9:07 PM
 To: Barak Mery
 Cc: mysql@lists.mysql.com
 Subject: Re: urgent : PLEASE HELP - problems with back up and restore 
   
 
 
 Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 01:51:37 PM:
 
  Hi,
  
   
  
  I'm really desperate on this on.
  
  After struggling with some really weird bugs I finally finished my
project.
  
   
  
  Buut I can't perform a good backup and restore.
  
   
  
  I'm using  :
  
  MySql 5.0.16-nt (essentials version)
  
  Windows xp
  
  The DB contains tables, vw's and sp's.
  
  It is a very small one and at the moment I don't have any data inside.
  
  The whole backup file size is 100Kb.
  
   
  
  1.   I backed up my db using mysql administrator.
  It created a back up file with all tables, views and sp's, but when I
tried
  a restore it got errors like.
  Could not handle this statement etc.
  
  
  2.   I tried : mysqldump -uroot -pmypassword dbname  backup.sql
  And then mysqldump -uroot -pmypassword newDbName  backup.sql
  I didn't get any error.
  The shell printed to the screen the backup file completely only without
the
  table script part.
  
  It Created a backup file only for the tables (why ? a minute ago I did
the
  backup with the same tool).
  But after restoring, the new db was still empty.
  
  
  3.   I tried to restore with mysql  -uroot -pmypassword newDbName 
  backup.sql
  But I got the same results.
  
   
  
   
  
  It should be a very simple and basic issue.
  Why everything is so hard with mysql ?
  
  
  
  Is there any really good and quick forum for mySql ? I posted some new
  thread in mysql.com at the past few days but never got answered.
  
   
  
  Is it just me or that's the life on the mysql planet ?
  
  
  
  I now try this mailing-list. I hope you can help me.
  
   
  
  Thanks in advance
  
  Barak
 
 You are in luck as the mailing list is quite active. 
 
 Look at your actual dump file. It is simply a SQL script that will 
 create all of the elements of the database and populate them with 
 data (if you had any). My suspicion is that you have something that 
 isn't quoted that needed to be. 
 
 mysqldump has several options. You can see them with the command: 
 
 mysqldump --help 
 
 -or- refer to the manual 
 
 http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html 
 
 try dumping your database again, this time use the -r= and -Q 
 options. That does two things: 
 
 a) it avoids adding CR characters at the end of every line 
 b) it puts backticks around EVERYTHING that needs them (table names,
 column names, etc) 
 
 
 mysqldump -uroot -pmypassword -r backup.sql -Q dbname 
 
 Shawn Green
 Database Administrator
 Unimin Corporation - Spruce Pine 

RE: urgent : PLEASE HELP - problems with back up and restore

[Barak Mery]

Thanks Marko :)

-Original Message-
From: Marko Knezevic [mailto:[EMAIL PROTECTED] 
Sent: Thursday, November 24, 2005 12:29 AM
To: MySQL list
Subject: Re: urgent : PLEASE HELP - problems with back up and restore

Dear Barak,

Save yourself year or two of your life and try using MySQL Front not that 
command line tools. Its really nice tool for handling MySQL databases

Regards
Marko 


-- 
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: urgent : PLEASE HELP - problems with back up and restore

[SGreen]

I saw both views and stored procedures in the dump file. What error do you 
get if you process the script with the source command within the MySQL 
CLI?

mysql -uroot -pmypass
(login welcome)
mysql CREATE DATABASE IF NOT EXISTS bcm;
mysql USE bcm;
mysql source myback1.sql

There is another option to drop/create the database. Your dump didn't have 
that so you need to do it manually in order to restore it.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 07:18:02 PM:

 Wel I read the manual but didn’t find something that I really need to 
add
 I suspect it is something with the script itself.
 
 Now I’m using
 mysqldump -uroot -pmypass --routines -q -x bcmmyback1.sql 
 
 but after i run 
 mysqldump -uroot -pmypass --routines bcmmyback1.sql
 
 it now restore the tables but not the views or sp’s
 
 here is the backup file
 
 thanks 
 Barak
snipped out backup file
 
 
 From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
 Sent: Wednesday, November 23, 2005 9:30 PM
 To: Barak Mery
 Cc: mysql@lists.mysql.com
 Subject: RE: urgent : PLEASE HELP - problems with back up and restore
 
 
 There's a new option added in 5.0.13: 
 
  -R (--routines) 
 
 It's designed designed specifically to dump SPROCs and FUNCTIONs. I 
 thought it would have been turned on by default but I guess it 
 wasn't. Add that to the list of options and check your dump results 
 again. I refer you again to the manual for additional warnings and 
cautions. 
 
 http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html 
 
 Shawn Green
 Database Administrator
 Unimin Corporation - Spruce Pine 
 
 
 
 Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 02:23:42 PM:
 
  Well Shawn, 
  
  First of thanks for the quick result. 
  
  I tried your suggestion but it didn’t help no sp and no restore. 
  
  It only shrinked the backup file (by deleting the cr). 
  
  Is there any log file where I can trace that kind of erros ? 
  
  Barak 
  
  
  
  
  From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
  Sent: Wednesday, November 23, 2005 9:07 PM
  To: Barak Mery
  Cc: mysql@lists.mysql.com
  Subject: Re: urgent : PLEASE HELP - problems with back up and restore 
  
  
  
  Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 01:51:37 PM:
  
   Hi,
   
   
   
   I'm really desperate on this on.
   
   After struggling with some really weird bugs I finally finished 
 my project.
   
   
   
   Buut I can't perform a good backup and restore.
   
   
   
   I'm using  :
   
   MySql 5.0.16-nt (essentials version)
   
   Windows xp
   
   The DB contains tables, vw's and sp's.
   
   It is a very small one and at the moment I don't have any data 
inside.
   
   The whole backup file size is 100Kb.
   
   
   
   1.   I backed up my db using mysql administrator.
   It created a back up file with all tables, views and sp's, but 
 when I tried
   a restore it got errors like.
   Could not handle this statement etc.
   
   
   2.   I tried : mysqldump -uroot -pmypassword dbname  backup.sql
   And then mysqldump -uroot -pmypassword newDbName  backup.sql
   I didn't get any error.
   The shell printed to the screen the backup file completely only 
 without the
   table script part.
   
   It Created a backup file only for the tables (why ? a minute agoI 
did the
   backup with the same tool).
   But after restoring, the new db was still empty.
   
   
   3.   I tried to restore with mysql  -uroot -pmypassword newDbName 
   backup.sql
   But I got the same results.
   
   
   
   
   
   It should be a very simple and basic issue.
   Why everything is so hard with mysql ?
   
   
   
   Is there any really good and quick forum for mySql ? I posted some 
new
   thread in mysql.com at the past few days but never got answered.
   
   
   
   Is it just me or that's the life on the mysql planet ?
   
   
   
   I now try this mailing-list. I hope you can help me.
   
   
   
   Thanks in advance
   
   Barak
  
  You are in luck as the mailing list is quite active. 
  
  Look at your actual dump file. It is simply a SQL script that will 
  create all of the elements of the database and populate them with 
  data (if you had any). My suspicion is that you have something that 
  isn't quoted that needed to be. 
  
  mysqldump has several options. You can see them with the command: 
  
  mysqldump --help 
  
  -or- refer to the manual 
  
  http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html 
  
  try dumping your database again, this time use the -r= and -Q 
  options. That does two things: 
  
  a) it avoids adding CR characters at the end of every line 
  b) it puts backticks around EVERYTHING that needs them (table names,
  column names, etc) 
  
  
  mysqldump -uroot -pmypassword -r backup.sql -Q dbname 
  
  Shawn Green
  Database Administrator
  Unimin Corporation - Spruce Pine 
 

Re: upload images / mp3 more Than 1 MB capacity ---- please help

[DreamWerx]

For an PHP example of how to import data getting around
max_packet_size, performance, etc. issues..  Be sure to read this
article.
Also linked from: http://dev.mysql.com/doc/refman/4.1/en/blob.html

Article @ http://php.dreamwerx.net/forums/viewtopic.php?t=6

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: upload images / mp3 more Than 1 MB capacity ---- please help

[Jigal van Hemert]

Dan Buettner wrote:
I tend to disagree - at my place of employment, a newspaper, we have 
hundreds of gigabytes of BLOB data (ad and page layouts  digital 
artwork) stored in SQL databases.  Granted we are using Sybase for that 
and not MySQL but there are a lot of advantages to it - access control, 
change control and tracking, easy insertion and deletion, and access 
from any client right through the database driver so you can repurpose 
content more easily.


There are situations where it might be useful to store large amounts of 
binary data in a database. For most situations the best solution is to 
store metadata about the file in a database and store the file itself on 
a file system.
There have been lots of discussions about it on this list in the past. 
From those discussions one could conclude that in general a file system 
is best for storing (large) files and the metadata about these files can 
live in a database. But there are situations where storing large files 
in a database has more advantages.


Kind regards, Jigal.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

upload images / mp3 more Than 1 MB capacity ---- please help

[Kane Wilson]

hi , 
 
I have crerated a mysql database to store images , mp3 , video files..etc. In 
my first stage i stored images as jpg , gif . 
But when i try to store little but huge gif files it wont store . I used the 
script as follows to upload images, 
 
html
head
titleUpload File To MySQL Database/title
meta http-equiv=Content-Type content=text/html; charset=iso-8859-1
style type=text/css
!--
.box {
font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
border: 1px solid #00;
}
--
/style
/head
body
?
if(isset($_POST['upload']))
{
$fileName = $_FILES['userfile']['name'];
$tmpName  = $_FILES['userfile']['tmp_name'];
$fileType = $_FILES['userfile']['type'];
$fileSize = $_FILES['userfile']['size'];
$fp = fopen($tmpName, 'r');
$content = fread($fp, $fileSize);
$content = addslashes($content);
fclose($fp);
if(!get_magic_quotes_gpc())
{
$fileName = addslashes($fileName);
}

include 'library/config.php';
include 'library/opendb.php';
$query = INSERT INTO upload (name, type, size, content ) .
 VALUES ('$fileName', '$fileType','$fileSize', 
'$content');
mysql_query($query) or die('Error, query failed');
include 'library/closedb.php';
echo brFile $fileName uploadedbr;
}
?
form action= method=post enctype=multipart/form-data name=uploadform
  table width=350 border=0 cellpadding=1 cellspacing=1 class=box
tr
  td width=246input type=hidden name=MAX_FILE_SIZE 
value=200input name=userfile type=file class=box id=userfile
 /td
  td width=80input name=upload type=submit class=box id=upload 
value=  Upload  /td
/tr
  /table
/form
/body
/html
 
56Kb , 75 Kb files are accept to store , but more that 1 MB we cant upload. It 
wont give any error messages . But that particular data wont displayed.
 
so , this is a big problem for me . i couldnt sorted out yet . I used mysqlcc 
and sqlYOG as well to upload images ( contents ).
 
in like this situations how can we work with mp3 and such huge files 
 
please .  help me very very urgent 
 
Thanx in advance,
Kane. 


-
 Yahoo! Music Unlimited - Access over 1 million songs. Try it free.

Re: upload images / mp3 more Than 1 MB capacity ---- please help

[Jigal van Hemert]

Kane Wilson wrote:
But when i try to store little but huge gif files it wont store . 


First of all, use the method described at 
http://www.php.net/manual/en/features.file-upload.php for a safe way to 
handle file uploads. It could be that you run into a server limit which 
will show up if you use that method.


I do think that you exceeded the max_allowed_packet size for MySQL 
queries which has a default value of 1048576 (=1MB). You can increase 
this number (must be done in both client and server!!), but it is 
usually best to store huge files in a file system and not in a database.


Kind regards, Jigal.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: upload images / mp3 more Than 1 MB capacity ---- please help

[Kane Wilson]

thanx for the reply , usually , i uploading jpg and gif files into mysql 
database , there is no any issue. we can display them correctly.
when i try to upload such huge mp3 files and some .jar files into the databse , 
i  used mysqlcc GUI and SQLYOG GUI , from those interfaces also giving 
troubles. contents wont stored.
my requrement is any how keep the contents , in the mysql database, i have high 
performance machine. so then i'm not worry about perforamnce.

please , let me know how could be the way to store such files in a mysql 
databse ??? 
 
please help 
 

Jigal van Hemert [EMAIL PROTECTED] wrote:
Kane Wilson wrote:
 But when i try to store little but huge gif files it wont store . 

First of all, use the method described at 
http://www.php.net/manual/en/features.file-upload.php for a safe way to 
handle file uploads. It could be that you run into a server limit which 
will show up if you use that method.

I do think that you exceeded the max_allowed_packet size for MySQL 
queries which has a default value of 1048576 (=1MB). You can increase 
this number (must be done in both client and server!!), but it is 
usually best to store huge files in a file system and not in a database.

Kind regards, Jigal.


-
 Yahoo! Music Unlimited - Access over 1 million songs. Try it free.

Re: upload images / mp3 more Than 1 MB capacity ---- please help

[SGreen]

AS JIGAL SAID : 
make sure you are not creating an INSERT statement bigger than the 
max_allowed_packet setting for both your destination server and your 
client library (whichever ones you are using). If you attempt to create a 
packet that is too large, the server will _ignore_ it. Because the packet 
(SQL statement)  is too large, it is considered malformed and any 
processing of it will be aborted. 

http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html
http://dev.mysql.com/doc/refman/5.0/en/memory-use.html
http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



Kane Wilson [EMAIL PROTECTED] wrote on 10/13/2005 09:25:24 AM:

 thanx for the reply , usually , i uploading jpg and gif files into 
 mysql database , there is no any issue. we can display them correctly.
 when i try to upload such huge mp3 files and some .jar files into 
 the databse , i  used mysqlcc GUI and SQLYOG GUI , from those 
 interfaces also giving troubles. contents wont stored.
 my requrement is any how keep the contents , in the mysql database, 
 i have high performance machine. so then i'm not worry about 
perforamnce.
 
 please , let me know how could be the way to store such files in a 
 mysql databse ??? 
 
 please help 
 
 
 Jigal van Hemert [EMAIL PROTECTED] wrote:
 Kane Wilson wrote:
  But when i try to store little but huge gif files it wont store . 
 
 First of all, use the method described at 
 http://www.php.net/manual/en/features.file-upload.php for a safe way to 
 handle file uploads. It could be that you run into a server limit which 
 will show up if you use that method.
 
 I do think that you exceeded the max_allowed_packet size for MySQL 
 queries which has a default value of 1048576 (=1MB). You can increase 
 this number (must be done in both client and server!!), but it is 
 usually best to store huge files in a file system and not in a database.
 
 Kind regards, Jigal.
 
 
 -
  Yahoo! Music Unlimited - Access over 1 million songs. Try it free.

Re: upload images / mp3 more Than 1 MB capacity ---- please help

[Dan Buettner]

I tend to disagree - at my place of employment, a newspaper, we have 
hundreds of gigabytes of BLOB data (ad and page layouts  digital 
artwork) stored in SQL databases.  Granted we are using Sybase for 
that and not MySQL but there are a lot of advantages to it - access 
control, change control and tracking, easy insertion and deletion, 
and access from any client right through the database driver so you 
can repurpose content more easily.


I have stored smaller amounts of BLOB data in MySQL for side projects 
in the past, without trouble.


Do understand though that storing BLOB data in the db will impact 
your table optimization and repair times as well as backup/recover 
times.


You might also want to plan ahead and devise a scheme to spread the 
data among several tables, to allow optimization of just one table a 
day (for example) and even to allow you to split storage across 
multiple devices should your database get too large.


Dan


At 3:09 PM +0200 10/13/05, Jigal van Hemert wrote:

Kane Wilson wrote:

But when i try to store little but huge gif files it wont store .


First of all, use the method described at 
http://www.php.net/manual/en/features.file-upload.php for a safe way 
to handle file uploads. It could be that you run into a server limit 
which will show up if you use that method.


I do think that you exceeded the max_allowed_packet size for MySQL 
queries which has a default value of 1048576 (=1MB). You can 
increase this number (must be done in both client and server!!), but 
it is usually best to store huge files in a file system and not in a 
database.


Kind regards, Jigal.

--
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]

please help .........very urgent

[Kane Wilson]

hi , 

I wanted to check the following condition and if it is
success i wanted to display a massage.I tried as
follows. but no luck. nothing displayed.

$dbQuery = results;
$result = mysql_query($dbQuery) or die(Couldn't get
file list);

if (!isset($result))
   {echo NULL;}


//if (mysql_result($result == 0))(

//echo sorry;

?


please , tell me how can i do this ?

Thanx
Kane.




__
Click here to donate to the Hurricane Katrina relief effort.
http://store.yahoo.com/redcross-donate3/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: please help .........very urgent

[Jigal van Hemert]

Kane Wilson wrote:

I wanted to check the following condition and if it is
success i wanted to display a massage.I tried as
follows. but no luck. nothing displayed.

$dbQuery = results;
$result = mysql_query($dbQuery) or die(Couldn't get
file list);

if (!isset($result))
   {echo NULL;}


//if (mysql_result($result == 0))(

//echo sorry;

?
Although this is something for a PHP mailing list of forum, I'll try to 
give you some pointers.


I assume that you first used mysql_connect() to connect to the MySQL db.

mysql_query() returns FALSE if the query failed for some reason. In that 
case you can use mysql_error() to retrieve the error message.

$result = mysql_query($dbQuery);
if (!$result) die('Query failed: ' . mysql_error());

Checking isset() is useless, since $result is always set (either with 
false, true or the result resource of the query).


http://www.php.net/manual/en/ref.mysql.php contains an example which 
shows all the steps you need to set up a connection and run a query.


Regards, Jigal.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: please help .........very urgent

[Gleb Paharenko]

Hello.



 $dbQuery = results;



Have you forgotten to put '$' before the 'results'? Do you

have display_errors = On in your php.ini?





Kane Wilson [EMAIL PROTECTED] wrote:

 

 

 hi , 

 

 I wanted to check the following condition and if it is

 success i wanted to display a massage.I tried as

 follows. but no luck. nothing displayed.

 

 $dbQuery = results;

 $result = mysql_query($dbQuery) or die(Couldn't get

 file list);

 

 if (!isset($result))

   {echo NULL;}

 

 

 //if (mysql_result($result == 0))(

 

 //echo sorry;

 

 ?

 

 

 please , tell me how can i do this ?

 

 Thanx

 Kane.

 

 





 __

 Click here to donate to the Hurricane Katrina relief effort.

 http://store.yahoo.com/redcross-donate3/

 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   ___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: please help .........very urgent

[Edwin Cruz]

Hi!!

$dbQuery = select * from table where 1 ;
$result = mysql_query($dbQuery) or die(Error trying to execute the query:
.mysql_error());
If(mysql_num_rows($result)1){ //also: ==0 works
echo NULL;

-Original Message-
From: Kane Wilson [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, September 07, 2005 1:40 AM
To: mysql
Subject: please help .very urgent



hi , 

I wanted to check the following condition and if it is success i wanted to
display a massage.I tried as follows. but no luck. nothing displayed.

$dbQuery = results;
$result = mysql_query($dbQuery) or die(Couldn't get file list);

if (!isset($result))
   {echo NULL;}


//if (mysql_result($result == 0))(

//echo sorry;

?


please , tell me how can i do this ?

Thanx
Kane.




__
Click here to donate to the Hurricane Katrina relief effort.
http://store.yahoo.com/redcross-donate3/

--
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 me: Boolean fulltext searches, AND instead of OR

[Michael Stassen]

John thegimper wrote:

This is what i need:

Posted by gogman on Monday May 5 2003, @10:42am on the mysql website:

MySQL defaults to an 'OR'. Example: 'dog cat' = 'dog OR cat'. Most fulltext 
search engines default to an 'AND'. These include: AltaVista, Fast Search, 
Google, Hotbot, Lycos, Northern Light and Yahoo. Excite is an exception that 
defaults to an 'OR'. 


It's not clear to me that this is entirely accurate (Google, for example, is 
a relevance search 
http://www.google.com/support/bin/answer.py?answer=427topic=352), but I 
don't think web search engine front ends are particularly relevant in any case.



New Feature: set-variable = ft_boolean_default='AND'
vs 'OR'

('OR' would be the default setting so as to not break older code)

With a 'AND' default 'OR' would have to be explicit. Example: 'dog cat' = 'dog 
AND cat', 'dog OR cat' - requires 'OR' to be set.


Performance tests are indicating a 5-7 times increase in search speed 
with AND vs OR statements.


--

I have done some searches on google and found several people wanting to do the 
same... but no solutions.
 
Is there still no solution for this? Every large searchengine works like

this.


Why do you *need* this?  More to the point, why should mysql do this?

Honestly, I think you are confusing the user interface, the application, and 
the back end.  Your user interface is free to include a search box where the 
user can type 'dog cat' with the expectation that only documents containing 
both (AND) will be returned.  Your application needs to parse the request 
and send the proper query to mysql (the back end).  Mysql is just a useful 
tool.  It stores your data and provides various forms of full-text searching:


Relevance scoring -

  WHERE MATCH (doc, description) AGAINST ('dog cat')

OR searches -

  WHERE MATCH (doc, description) AGAINST ('dog cat' IN BOOLEAN MODE)

AND searches -

  WHERE MATCH (doc, description) AGAINST ('+dog +cat' IN BOOLEAN MODE)

Given these choices, I'm not sure what difference it makes what the default 
is.  You surely don't propose to pass unmodified user input to mysql, as 
that's not a good idea (see SQL injection 
http://www.google.com/search?q=SQL+injection).  If you want AND searches, 
simply have your app add the + signs to the user input as it builds the 
query to send to mysql.


If you are determined to change mysql's default behavior, then Sergei has 
already given a solution earlier in this thread: swap the '+' and ' ' in the 
ft_boolean_syntax variable.  The only objection raised to this was the 
suggestion that if the user prepends a '+' to a word, it becomes optional 
(OR instead of AND), but that's a moot point, as your app will, of course, 
strip the '+' when parsing the user's input.


Michael


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Please help me: Boolean fulltext searches, AND instead of OR

[John thegimper]

Is there still no solution for this? Every large searchengine works like this.

Quoting:
 
 
 Hi that works fine only now if a user puts + in front of a word that
 word 
 becomes optional = OR?
 What i would like is the search to work exactly like before only that it
 
 defaults to AND instead of OR.
 Almost every search engine i have tried, google etc. works like this.
 black horse cat dog only show results with all words present.
 
 This is what i did, set global ft_boolean_syntax = ' +-()~*:|'
 
 I really appreciate your help!!
 
 From: Sergei Golubchik [EMAIL PROTECTED]
 To: Jessica Svensson [EMAIL PROTECTED]
 CC: [EMAIL PROTECTED]
 Subject: Re: Please help me: Boolean fulltext searches, AND instead of
 OR
 Date: Wed, 23 Mar 2005 22:32:49 +0100
 
 Hi!
 
 On Mar 23, Jessica Svensson wrote:
   Is there any way i can get results with AND instead of OR?
   Trying to search for black cat should only return records that 
 contains
   both black and cat.
  
   I'm using the following code to get my result:
  
   SELECT * FROM `searchtbl` WHERE MATCH (text) AGAINST ('black cat' IN
   BOOLEAN MODE);
  
   sure there must be an easy way to change the default word separator
 to 
 AND
   instead of OR?
 
 There is. See ft_boolean_syntax variable - it defines what characters
 is
 used for each operator. In particular it defines '+' for must be
 present and a space ' ' for optionally present words.
 
 You want to put the space first (for must be present words)
 
 Regards,
 Sergei
 
 --
 __  ___ ___   __
/  |/  /_ __/ __/ __ \/ /   Sergei Golubchik [EMAIL PROTECTED]
   / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
 /_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
 ___/  www.mysql.com
 
 _
 Lättare att hitta drömresan med MSN Resor http://www.msn.se/resor/
 
 


 

-
FREE E-MAIL IN 1 MINUTE!
 - [EMAIL PROTECTED] - http://www.pc.nu

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Please help me: Boolean fulltext searches, AND instead of OR

[John thegimper]

This is what i need:

Posted by gogman on Monday May 5 2003, @10:42am on the mysql website:

MySQL defaults to an 'OR'. Example: 'dog cat' = 'dog OR cat'. Most fulltext 
search engines default to an 'AND'. These include: AltaVista, Fast Search, 
Google, Hotbot, Lycos, Northern Light and Yahoo. Excite is an exception that 
defaults to an 'OR'. 


New Feature: set-variable = ft_boolean_default='AND'

vs 'OR'

('OR' would be the default setting so as to not break older code)

With a 'AND' default 'OR' would have to be explicit. Example: 'dog cat' = 'dog 
AND cat', 'dog OR cat' - requires 'OR' to be set.

Performance tests are indicating a 5-7 times increase in search speed 
with AND vs OR statements.

--


I have done some searches on google and found several people wanting to do the 
same... but no solutions.


Quoting John thegimper [EMAIL PROTECTED]:

 Is there still no solution for this? Every large searchengine works like
 this.
 
 Quoting:
  
  
  Hi that works fine only now if a user puts + in front of a word that
  word 
  becomes optional = OR?
  What i would like is the search to work exactly like before only that
 it
  
  defaults to AND instead of OR.
  Almost every search engine i have tried, google etc. works like this.
  black horse cat dog only show results with all words present.
  
  This is what i did, set global ft_boolean_syntax = ' +-()~*:|'
  
  I really appreciate your help!!
  
  From: Sergei Golubchik [EMAIL PROTECTED]
  To: Jessica Svensson [EMAIL PROTECTED]
  CC: [EMAIL PROTECTED]
  Subject: Re: Please help me: Boolean fulltext searches, AND instead
 of
  OR
  Date: Wed, 23 Mar 2005 22:32:49 +0100
  
  Hi!
  
  On Mar 23, Jessica Svensson wrote:
Is there any way i can get results with AND instead of OR?
Trying to search for black cat should only return records that 
  contains
both black and cat.
   
I'm using the following code to get my result:
   
SELECT * FROM `searchtbl` WHERE MATCH (text) AGAINST ('black cat'
 IN
BOOLEAN MODE);
   
sure there must be an easy way to change the default word
 separator
  to 
  AND
instead of OR?
  
  There is. See ft_boolean_syntax variable - it defines what characters
  is
  used for each operator. In particular it defines '+' for must be
  present and a space ' ' for optionally present words.
  
  You want to put the space first (for must be present words)
  
  Regards,
  Sergei
  
  --
  __  ___ ___   __
 /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
  /_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
  ___/  www.mysql.com
  
  _
  Lättare att hitta drömresan med MSN Resor http://www.msn.se/resor/
  
  
 
 
  
 
 -
 FREE E-MAIL IN 1 MINUTE!
  - [EMAIL PROTECTED] - http://www.pc.nu
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 
 



 

-
FREE E-MAIL IN 1 MINUTE!
 - [EMAIL PROTECTED] - http://www.pc.nu

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Total Sum of Monthly Counts for Older MySQL 4.0.22 (Please Help)

[Gleb Paharenko]

Hello.





It is possible to obtain your results without temporary tables, but

with user variables. For a pity you should execute three queries.

With first query you're getting the Sum:



  select @all_sum:= count( employee_id) from table_employee

where employee_hire_date between 2005-01-01 and 2005-4-30;





With the second - number of month (this in most cases could be figured

out without query - with direct computation in next query, or on client

side):



  select @all_num:= count( distinct month(employee_hire_date))

from table_employee 

where employee_hire_date between 2005-01-01 and 2005-4-30;





And with the last query you should get what you want:



  select employee_hire_date, count(employee_id), @all_sum as Sum,

@all_sum/@all_num as Avg 

from table_employee

where employee_hire_date between 2005-01-01 and 2005-4-30

group by month(employee_hire_date);









Henry Chang [EMAIL PROTECTED] wrote:

 

 Hello MySQL users,

 

 Currently, I use MySQL 4.0.22 and I can do a straightforward count of

 employees hired for each month.

 

 select employee_hire_date, count(employee_id)

 from table_employee

 where employee_hire_date between 2005-01-01 and 2005-4-30

 group by month(employee_hire_date)

 

 

 Date   | Count |

 

 2005-01-01 | 123   |

 2005-02-01 | 50|

 2005-03-01 | 76|

 2005-04-01 | 89|

 

 

 However, I would like to do a grand total of the counts and the averages

 that would like the below.  

 

 

 Date   | Count | Sum | Avg  |

 -

 2005-01-01 | 123   | 338 | 84.5 |

 2005-02-01 | 50| 338 | 84.5 |

 2005-03-01 | 76| 338 | 84.5 |

 2005-04-01 | 89| 338 | 84.5 |

 

 

 Since my MySQL version is 4.0.22, I am not able to use subquery and I

 prefer not to use tmp tables.  What would be the right query to solve

 for the grand total sum and average??  Any help would be greatly

 appreciated!!!

 

 Thanks in Advance.

 

 Henry

 

 

 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   ___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Total Sum of Monthly Counts for Older MySQL 4.0.22 (Please Help)

[Michael Stassen]

Without knowing more of your requirements, I find seeing the grand total and 
overall average repeated in every row strange from a usability perspective. 
Do you really need that, or were you just hoping to get that information out 
of your query?


I'd suggest something like:

  SET @sum=0, @m=0;
  SELECT MONTH(employee_hire_date) AS date,
 @m:[EMAIL PROTECTED] as Month,
 count(*) AS Count,
 @sum:[EMAIL PROTECTED](*) AS Sum,
 ROUND((@sum+count(*))/(@m),1) AS Avg
  FROM table_employee
  WHERE employee_hire_date BETWEEN '2005-01-01' AND '2005-4-30'
  GROUP BY MONTH(employee_hire_date);

Date| Month | Count | Sum |  Avg  |
+---+---+-+---+
2005-01 |   1   | 123   | 123 | 123.0 |
2005-02 |   2   |  50   | 173 |  86.5 |
2005-03 |   3   |  76   | 249 |  83.0 |
2005-04 |   4   |  89   | 338 |  84.5 |

The Sum column is a running total, and the Avg column is the average so far. 
 Hence, the grand total and overall average are in the last row.  Would that do?


Michael

Gleb Paharenko wrote:


Hello.

It is possible to obtain your results without temporary tables, but
with user variables. For a pity you should execute three queries.
With first query you're getting the Sum:

  select @all_sum:= count( employee_id) from table_employee
where employee_hire_date between 2005-01-01 and 2005-4-30;


With the second - number of month (this in most cases could be figured
out without query - with direct computation in next query, or on client
side):

  select @all_num:= count( distinct month(employee_hire_date))
	from table_employee 
	where employee_hire_date between 2005-01-01 and 2005-4-30;



And with the last query you should get what you want:

  select employee_hire_date, count(employee_id), @all_sum as Sum,
		@all_sum/@all_num as Avg 
	from table_employee

where employee_hire_date between 2005-01-01 and 2005-4-30
group by month(employee_hire_date);

Henry Chang [EMAIL PROTECTED] wrote:


Hello MySQL users,

Currently, I use MySQL 4.0.22 and I can do a straightforward count of
employees hired for each month.

select employee_hire_date, count(employee_id)
from table_employee
where employee_hire_date between 2005-01-01 and 2005-4-30
group by month(employee_hire_date)


Date   | Count |

2005-01-01 | 123   |
2005-02-01 | 50|
2005-03-01 | 76|
2005-04-01 | 89|


However, I would like to do a grand total of the counts and the averages
that would like the below.  



Date   | Count | Sum | Avg  |
-
2005-01-01 | 123   | 338 | 84.5 |
2005-02-01 | 50| 338 | 84.5 |
2005-03-01 | 76| 338 | 84.5 |
2005-04-01 | 89| 338 | 84.5 |


Since my MySQL version is 4.0.22, I am not able to use subquery and I
prefer not to use tmp tables.  What would be the right query to solve
for the grand total sum and average??  Any help would be greatly
appreciated!!!

Thanks in Advance.

Henry



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Total Sum of Monthly Counts for Older MySQL 4.0.22 (Please Help)

[Henry Chang]

Hello MySQL users,

Currently, I use MySQL 4.0.22 and I can do a straightforward count of
employees hired for each month.

select employee_hire_date, count(employee_id)
from table_employee
where employee_hire_date between 2005-01-01 and 2005-4-30
group by month(employee_hire_date)


Date   | Count |

2005-01-01 | 123   |
2005-02-01 | 50|
2005-03-01 | 76|
2005-04-01 | 89|


However, I would like to do a grand total of the counts and the averages
that would like the below.  


Date   | Count | Sum | Avg  |
-
2005-01-01 | 123   | 338 | 84.5 |
2005-02-01 | 50| 338 | 84.5 |
2005-03-01 | 76| 338 | 84.5 |
2005-04-01 | 89| 338 | 84.5 |


Since my MySQL version is 4.0.22, I am not able to use subquery and I
prefer not to use tmp tables.  What would be the right query to solve
for the grand total sum and average??  Any help would be greatly
appreciated!!!

Thanks in Advance.

Henry



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SLOW SLOW query - please help!

[mfatene]

Hi,
with a table with 33 rows, i have the row in 0s using index on term.

mysql select * from tx where term like 'Britney Spears' ;
+++
| id | term   |
+++
| 327681 | Britney Spears |
+++
1 row in set (0.00 sec)

mysql explain select * from tx where term = 'Britney Spears' ;
++-+---+---+---+--+-+---+--+-+
| id | select_type | table | type  | possible_keys | key  | key_len | ref   |
rows | Extra   |
++-+---+---+---+--+-+---+--+-+
|  1 | SIMPLE  | tx| const | term  | term | 256 | const |   
1 | Using index |
++-+---+---+---+--+-+---+--+-+
1 row in set (0.00 sec)


hope that helps.

Even with lock tables you may have the row fast.

Mathias


Selon Brian Dunning [EMAIL PROTECTED]:

  What is the output of the following:
  EXPLAIN select * from terms where term like 'Britney Spears' limit 1;

 select_type = SIMPLE
 table = terms
 type = range
 possible_keys = term, term_2 (I just created term_2, fulltext)
 key = term (this means it's not using the one I just created?)
 key_len = 255
 ref = NULL
 Extra = Using where

  Also, can you use = instead of like in your query?

 Yes, I made that change and it still works, so I'll leave it.

 --
 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]

Please help with indexes

[Marcin Lewandowski]

Hi,
i have table which would contain data describing holiday houses. It's it:
idbigint(20) unsigned PRIauto_increment
nametinytext
descriptiontext
webpagetinytext
emailtinytext
whole_cantinyint(1) unsigned
whole_onlytinyint(1) unsigned
typeenum('house','apartment','hotel')
person_mintinyint(3) unsigned
person_maxtinyint(3) unsigned
children_mintinyint(3) unsigned
children_maxtinyint(3) unsigned
animal_mintinyint(3) unsigned
animal_maxtinyint(3) unsigned
iconsbigint(20) unsigned
date_adddatetime
date_modifydatetime
I will make a search which should find using random criteria. Sometimes 
it would be find houses (type=house) which can contain 4 to 5 person 
(person_min = 4 and person_max = 5), sometimes it would be find 
hotels which could be reserved whole (type=hotel and whole_can=1) and 
which could contain 100 person (person_min = 100 and person_max = 100)

They could be more types of queries, but mostly which person_min/max and 
 type columns.

How I should create indexes to boost SELECT queries? My tables are innodb.
--
Marcin Lewandowski
[  mailto:[EMAIL PROTECTED]  gg:188068 jid:[EMAIL PROTECTED]  ]
[   http://www.nosoftwarepatents.com/pl/m/intro/index.html   ]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]