Re: column being created as varchar() when char() requested.

[Philip Walden]

Michael Stassen wrote:

Hmmm..., it doesn't seem to be a problem for me in 4.0.17:

  mysql> CREATE TABLE t3
  -> (
  ->id CHAR(5) NOT NULL,
  ->description VARCHAR(48) NOT NULL,
  ->PRIMARY KEY (id)
  -> ) TYPE = InnoDB;
  Query OK, 0 rows affected (0.02 sec)
  mysql> DESC t3;
  +-+-+--+-+-+---+
  | Field   | Type| Null | Key | Default | Extra |
  +-+-+--+-+-+---+
  | id  | varchar(5)  |  | PRI | |   |
  | description | varchar(48) |  | | |   |
  +-+-+--+-+-+---+
  2 rows in set (0.01 sec)
  mysql> CREATE TABLE t4
  -> (
  ->   id INT NOT NULL,
  ->   t3_id CHAR(5) NOT NULL,
  ->   INDEX t3_ind(t3_id)
  -> ) TYPE = InnoDB;
  Query OK, 0 rows affected (0.02 sec)
  mysql> ALTER TABLE t4 ADD CONSTRAINT FOREIGN KEY (t3_id) REFERENCES 
t3(id);
  Query OK, 0 rows affected (0.11 sec)
  Records: 0  Duplicates: 0  Warnings: 0

I do get the error you quote (ERROR 1005: Can't create table...) if I 
leave out the index creation in either table, which is documented in 
the manual 
.  I 
can see that supp.supp_cd is a PRIMARY KEY.  Did you create the 
required index on hpi_supp_agmt.supp_cd before you tried to add the 
foreign key reference?

Michael
The hpi_supp_agmt.supp_cd is part of a compound primary index. BTW, this 
is a legacy database I am trying to port. Here is the create for the 
hpi_supp_agmt:

create table hpi_supp_agmt
(
  div_cd char(4) not null,
  hpi_no char(15) not null,
  supp_cd char(5) not null,
  agmt_no char(8) not null,
  agmt_owner char(4) not null,
  agmt_price decimal(16) not null,
  agmt_uom char(4) not null,
  agmt_lt integer not null,
  agmt_exp_dt date not null,
  updt_user_id smallint not null,
  updt_dt date not null,
  create_dt date not null,
  constraint p1hpisuppagmt primary key (div_cd,hpi_no,supp_cd,agmt_no)
) type = InnoDB;
Thanks
Phil


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

Performance of CHECK TABLE & REPAIR TABLE

[Gowtham Jayaram]

Hello All:

-   Are there any benchmarks that provide information
on time taken to run 'CHECK TABLE' and/or 'REPAIR
TABLE' on tables of different sizes ?

-   What are the factors that effect the time taken to
run 'CHECK TABLE' and/or 'REPAIR TABLE' on a table
other than its size?

-  Are there any pointers to optimize the performance
of these commands ?

Thank you.

Gowtham. 


__
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it!
http://webhosting.yahoo.com/ps/sb/

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

Re: column being created as varchar() when char() requested.

[Michael Stassen]

Hmmm..., it doesn't seem to be a problem for me in 4.0.17:

  mysql> CREATE TABLE t3
  -> (
  ->id CHAR(5) NOT NULL,
  ->description VARCHAR(48) NOT NULL,
  ->PRIMARY KEY (id)
  -> ) TYPE = InnoDB;
  Query OK, 0 rows affected (0.02 sec)
  mysql> DESC t3;
  +-+-+--+-+-+---+
  | Field   | Type| Null | Key | Default | Extra |
  +-+-+--+-+-+---+
  | id  | varchar(5)  |  | PRI | |   |
  | description | varchar(48) |  | | |   |
  +-+-+--+-+-+---+
  2 rows in set (0.01 sec)
  mysql> CREATE TABLE t4
  -> (
  ->   id INT NOT NULL,
  ->   t3_id CHAR(5) NOT NULL,
  ->   INDEX t3_ind(t3_id)
  -> ) TYPE = InnoDB;
  Query OK, 0 rows affected (0.02 sec)
  mysql> ALTER TABLE t4 ADD CONSTRAINT FOREIGN KEY (t3_id) REFERENCES 
t3(id);
  Query OK, 0 rows affected (0.11 sec)
  Records: 0  Duplicates: 0  Warnings: 0

I do get the error you quote (ERROR 1005: Can't create table...) if I 
leave out the index creation in either table, which is documented in the 
manual 
.  I 
can see that supp.supp_cd is a PRIMARY KEY.  Did you create the required 
index on hpi_supp_agmt.supp_cd before you tried to add the foreign key 
reference?

Michael

Philip Walden wrote:

This is not so great when I have another table without any variable 
columns, that is trying to add a foreign key reference to a column that 
has "silently" been changed to varchar(). That is my problem. I can't 
add the foreign key reference as the referenced table column has been 
changed and key types don't match.

In the example below, the table hpi_supp_agmt's column supp_cd is the 
original char(5).

mysql> alter table hpi_supp_agmt add constraint foreign key (supp_cd) 
references supp(supp_cd);

ERROR 1005 (HY000): Can't create table './gem/#sql-72f_4.frm' (errno: 150)

At least one should be able to add the foreign key to a silently changed 
column.

Thanks

Phil

Michael Stassen wrote:

As soon as you have a variable length column, you have variable length 
rows.  In this case, mysql converts your char columns to varchar to 
save space and time.  See the manual for the details 
.

Michael

Philip Walden wrote:

I have MySQL 4.1.1-1. Given the create statement:

create table supp
 (
   supp_cd char(5) not null,
   supp_nm varchar(48) not null,
   supp_abbr char(4) not null,
   supp_stat varchar(32) not null,
   sz_tp_class_cd char(2) not null,
   ownrshp_class_cd char(2) not null,
   city_nm varchar(32) not null,
   geog_area_nm varchar(32) not null,
   cnty_cd char(2) not null,
   dunn_bradstreet_no char(10) not null,
   updt_user_id smallint not null,
   updt_dt date not null,
   create_dt date not null,
   constraint p1supp primary key (supp_cd)
 ) type = InnoDB;
A descibe shows that supp_cd, supp_abbr and dunn_bradstreet_no are being
added as a varchar() instead of a char(). Any ideas as what is wrong?
Looks like a parsing error.
mysql> describe supp;
++-+--+-++---+
| Field  | Type| Null | Key | Default| Extra |
++-+--+-++---+
| supp_cd| varchar(5)  |  | PRI ||   |
| supp_nm| varchar(48) |  | ||   |
| supp_abbr  | varchar(4)  |  | ||   |
| supp_stat  | varchar(32) |  | ||   |
| sz_tp_class_cd | char(2) |  | ||   |
| ownrshp_class_cd   | char(2) |  | ||   |
| city_nm| varchar(32) |  | ||   |
| geog_area_nm   | varchar(32) |  | ||   |
| cnty_cd| char(2) |  | ||   |
| dunn_bradstreet_no | varchar(10) |  | ||   |
| updt_user_id   | smallint(6) |  | | 0  |   |
| updt_dt| date|  | | -00-00 |   |
| create_dt  | date|  | | -00-00 |   |
++-+--+-++---+
13 rows in set (0.01 sec)









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

MySQL viewer

[Alex croes]

I'm currently using MyCC as a tool to "view" and maintain a 
MySQL-database of mine.
I find it quit a good tool, but which tool are you guys using. I have 
heard about MySQLfront
and Navicat. Are this also good tools to maintain the database. What are 
the pro's and the con's of this viewers?

Alex

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

Re: column being created as varchar() when char() requested.

[Philip Walden]

This is not so great when I have another table without any variable 
columns, that is trying to add a foreign key reference to a column that 
has "silently" been changed to varchar(). That is my problem. I can't 
add the foreign key reference as the referenced table column has been 
changed and key types don't match.

In the example below, the table hpi_supp_agmt's column supp_cd is the 
original char(5).

mysql> alter table hpi_supp_agmt add constraint foreign key (supp_cd) references supp(supp_cd);

ERROR 1005 (HY000): Can't create table './gem/#sql-72f_4.frm' (errno: 150)

At least one should be able to add the foreign key to a silently changed 
column.

Thanks

Phil

Michael Stassen wrote:

As soon as you have a variable length column, you have variable length 
rows.  In this case, mysql converts your char columns to varchar to 
save space and time.  See the manual for the details 
.

Michael

Philip Walden wrote:

I have MySQL 4.1.1-1. Given the create statement:

create table supp
 (
   supp_cd char(5) not null,
   supp_nm varchar(48) not null,
   supp_abbr char(4) not null,
   supp_stat varchar(32) not null,
   sz_tp_class_cd char(2) not null,
   ownrshp_class_cd char(2) not null,
   city_nm varchar(32) not null,
   geog_area_nm varchar(32) not null,
   cnty_cd char(2) not null,
   dunn_bradstreet_no char(10) not null,
   updt_user_id smallint not null,
   updt_dt date not null,
   create_dt date not null,
   constraint p1supp primary key (supp_cd)
 ) type = InnoDB;
A descibe shows that supp_cd, supp_abbr and dunn_bradstreet_no are being
added as a varchar() instead of a char(). Any ideas as what is wrong?
Looks like a parsing error.
mysql> describe supp;
++-+--+-++---+
| Field  | Type| Null | Key | Default| Extra |
++-+--+-++---+
| supp_cd| varchar(5)  |  | PRI ||   |
| supp_nm| varchar(48) |  | ||   |
| supp_abbr  | varchar(4)  |  | ||   |
| supp_stat  | varchar(32) |  | ||   |
| sz_tp_class_cd | char(2) |  | ||   |
| ownrshp_class_cd   | char(2) |  | ||   |
| city_nm| varchar(32) |  | ||   |
| geog_area_nm   | varchar(32) |  | ||   |
| cnty_cd| char(2) |  | ||   |
| dunn_bradstreet_no | varchar(10) |  | ||   |
| updt_user_id   | smallint(6) |  | | 0  |   |
| updt_dt| date|  | | -00-00 |   |
| create_dt  | date|  | | -00-00 |   |
++-+--+-++---+
13 rows in set (0.01 sec)







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

Re: query the data of a fulltext index directly from index?

[Sergei Golubchik]

Hi!

On Feb 02, Matt W wrote:
> Sergei,
> 
> Any chance of getting a ft_dump Windows binary in the distribution? :-)

Chances are good :)
It was added to rpms and binary unix distributions 5 min ago,
and it should be added to windows distro too.

Note - the new name is myisam_ftdump.
 
Regards,
Sergei

-- 
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <[EMAIL PROTECTED]>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
   <___/  www.mysql.com

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

Re: column being created as varchar() when char() requested.

[Michael Stassen]

As soon as you have a variable length column, you have variable length 
rows.  In this case, mysql converts your char columns to varchar to save 
space and time.  See the manual for the details 
.

Michael

Philip Walden wrote:
I have MySQL 4.1.1-1. Given the create statement:

create table supp
 (
   supp_cd char(5) not null,
   supp_nm varchar(48) not null,
   supp_abbr char(4) not null,
   supp_stat varchar(32) not null,
   sz_tp_class_cd char(2) not null,
   ownrshp_class_cd char(2) not null,
   city_nm varchar(32) not null,
   geog_area_nm varchar(32) not null,
   cnty_cd char(2) not null,
   dunn_bradstreet_no char(10) not null,
   updt_user_id smallint not null,
   updt_dt date not null,
   create_dt date not null,
   constraint p1supp primary key (supp_cd)
 ) type = InnoDB;
A descibe shows that supp_cd, supp_abbr and dunn_bradstreet_no are being
added as a varchar() instead of a char(). Any ideas as what is wrong?
Looks like a parsing error.
mysql> describe supp;
++-+--+-++---+
| Field  | Type| Null | Key | Default| Extra |
++-+--+-++---+
| supp_cd| varchar(5)  |  | PRI ||   |
| supp_nm| varchar(48) |  | ||   |
| supp_abbr  | varchar(4)  |  | ||   |
| supp_stat  | varchar(32) |  | ||   |
| sz_tp_class_cd | char(2) |  | ||   |
| ownrshp_class_cd   | char(2) |  | ||   |
| city_nm| varchar(32) |  | ||   |
| geog_area_nm   | varchar(32) |  | ||   |
| cnty_cd| char(2) |  | ||   |
| dunn_bradstreet_no | varchar(10) |  | ||   |
| updt_user_id   | smallint(6) |  | | 0  |   |
| updt_dt| date|  | | -00-00 |   |
| create_dt  | date|  | | -00-00 |   |
++-+--+-++---+
13 rows in set (0.01 sec)





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

Trouble with OR

[Yun Li]

Good day everyone,

I've been having trouble using OR in my queries. I hope someone could
help me.

I have three tables t0 (k0, k1, k2), t1 (k0, k1), and t2 (k0, k2),
linked through three columns k0, k1, k2. Specifically the linkages are:

t0.k0 = t1.k0 and t0.k0 = t2.k0 and ( (t0.k1 = t1.k1 and t0.k2 =
t2.k2) OR (t0.k1 = t2.k1 and t0.k2 = t1.k2) )

this results in an extremely slow query. When I tried the following
queries individually, they turned out to be very fast:

t0.k0 = t1.k0 and t0.k0 = t2.k0 and (t0.k1 = t1.k1 and t0.k2 =
t2.k2) 

t0.k0 = t1.k0 and t0.k0 = t2.k0 and (t0.k1 = t2.k1 and t0.k2 =
t1.k2) 

I tried using different indexes on these tables, but none seemed to
work. I am using mysqld 4.0.15a. If you happen to know the reason,
please give me a hand. 

Thank you for your time.

Eric





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

column being created as varchar() when char() requested.

[Philip Walden]

I have MySQL 4.1.1-1. Given the create statement:

create table supp
 (
   supp_cd char(5) not null,
   supp_nm varchar(48) not null,
   supp_abbr char(4) not null,
   supp_stat varchar(32) not null,
   sz_tp_class_cd char(2) not null,
   ownrshp_class_cd char(2) not null,
   city_nm varchar(32) not null,
   geog_area_nm varchar(32) not null,
   cnty_cd char(2) not null,
   dunn_bradstreet_no char(10) not null,
   updt_user_id smallint not null,
   updt_dt date not null,
   create_dt date not null,
   constraint p1supp primary key (supp_cd)
 ) type = InnoDB;
A descibe shows that supp_cd, supp_abbr and dunn_bradstreet_no are being
added as a varchar() instead of a char(). Any ideas as what is wrong?
Looks like a parsing error.
mysql> describe supp;
++-+--+-++---+
| Field  | Type| Null | Key | Default| Extra |
++-+--+-++---+
| supp_cd| varchar(5)  |  | PRI ||   |
| supp_nm| varchar(48) |  | ||   |
| supp_abbr  | varchar(4)  |  | ||   |
| supp_stat  | varchar(32) |  | ||   |
| sz_tp_class_cd | char(2) |  | ||   |
| ownrshp_class_cd   | char(2) |  | ||   |
| city_nm| varchar(32) |  | ||   |
| geog_area_nm   | varchar(32) |  | ||   |
| cnty_cd| char(2) |  | ||   |
| dunn_bradstreet_no | varchar(10) |  | ||   |
| updt_user_id   | smallint(6) |  | | 0  |   |
| updt_dt| date|  | | -00-00 |   |
| create_dt  | date|  | | -00-00 |   |
++-+--+-++---+
13 rows in set (0.01 sec)


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

RE: MySql and Oracle Forms 6i

[Ken Brown]

Yip I kinda figured that and tend to code that way anyhow
But
There is OCA which does work with a number of other database it's just there
isn't a ready built MySql setup.
I'll try converting one of the others to run
Thanks anyhow

Ken

-Original Message-
From: David Griffiths [mailto:[EMAIL PROTECTED] 
Sent: 02 February 2004 23:42
To: Ken Brown; [EMAIL PROTECTED]
Subject: Re: MySql and Oracle Forms 6i

Someone posted an answer:

"Oh wow... I can't think of how you'd get that to work without a ton of
custom. For starters, you'll have to write your own transactional triggers
and intercept every one of Oracle's select, lock, update, insert, delete,
like On-Lock, On-Insert, etc. since Forms will want things like rowids.
You'll also have to write your own on-Login.

Mike"

Oracle and MySQL differ in date handling, functions, triggers/stored
prodedures (or lack of), outer-join syntax, and a hundred other ways that
would make Forms incompatible with MySQL. Not to mention the fact that Forms
probably uses SQL*Net and a TNSNames.ora entry.

David.


- Original Message -
From: "Ken Brown" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, February 02, 2004 3:11 PM
Subject: MySql and Oracle Forms 6i


> Is there any way of using MySql as a back end to Oracle Forms?
>
>
>
>
>
> Ken  Brown
>
>

Mail was checked for spam by the Freeware Edition of No Spam Today!
The Freeware Edition is free for personal and non-commercial use.
You can remove this notice by purchasing a full license! To order
or to find out more please visit: http://www.no-spam-today.com

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

Joined tables still not working

[Luis Lebron]

I am having a problem with a query that joins the following tables. I want
to create a query that would tell me how many Manhours have been alloted for
a particular
project (32) by category and how many hours (from the Tasks table) have
actually been used for this project and category.

The data tables look like this:

Manhours
 mid| ProjectId |ChargeNum  | Catid | Hours | EmployeeId

  1 | 32|11 | 19| 80|200020 
  2 | 32|11 | 19| 24
|23 


Tasks
TaskID  |ChargeNum  |Catid  |EmployeeId |Hours  
1   |11 |19 |200020 |8 
2   |11 |19 |200020 |8 
5   |11 |19 |23 |12 


By looking at the Manhours table I can tell that for ProjectId 32 I have 104
(80+24) hours alloted for Catid 19
By looking at the Tasks table I can tell that 36 hours (8 + 8 + 8 + 12) have
been used on this project (same ChargeNum as Manhours table)
and Catid 19


I would like to creat a query that would give me the following results

Catid   |allotedhours   |usedHours  
19  |104|28 


I have tried joining the tables in a variety of ways but I am still not
getting the correct results. I seem to have some problems with the hours
alloted calculating correctly.


thanks,

Luis

High Availability for an Application using a robust MySQL DataStore

[Gowtham Jayaram]

Hello All:

I have a requirement to design High Availability for
an Application that is using a robust MySQL DataStore.
 I am thinking of the following configuration.  My
previous configuration for HA got exposed for being a
leaky boat on this list (Subject: Advise on High
Availability configuration).

CONFIGURATION:
-   Two machines, Primary and Secondary.  Each machine
has the __capability__ to run an instance of our
Application and an instance of MySQL Server.
-   A Heartbeat Manager runs on both boxes providing
the status of the machines, UP or DOWN.
-   Additionally, I will setup a SCSII controller in
the Primary and Secondary Application machines so that
the actual data store (disk drive) runs on another
physical machine in a disk-array (RAID).  
-   With this setup the MySQL Servers on both the
machines will write/read data to/from this disk array.

INITIAL STATE:
-   The Heartbeat Managers are running on both the
machines.
-   The Application and the MySQL process on the
Primary are Active, performing database operations at
any given time.
-   The Application and the MySQL process on the
Secondary are __not started__.

OPERATION:
-   The Primary machine goes down.  The Heartbeat Mgr
on the Secondary becomes aware of this and performs
the following operations on the Secondary.
* Stop (Kill) the Application running on the
Primary.
* Stop (Kill) the MySQL process on the Primary.
* Run tools programmatically to ensure the data
integrity of the DataStore.
* Start the MySQL process on the Secondary.
* Start the Application on the Secondary.  
-   The Secondary is now ACTIVE and will start
servicing requests.
-   I am guaranteeing that only one MySQL Server will
be accessing the DataStore at any given time. 

CAVEATS:
I have been made aware of the following caveat in the
above configuration.
-  The DataStore is the single point of failure. 
   Does the step of trying to run table repair and
data recovery operations on the DataStore when the
Secondary takes over alleviate this issue?

QUESTIONS:
-   Have any of you seen such a configuration being
deployed?
-   Do you see any big gotcha's in this configuration?
-   Will Stopping (Killing) the Primary MySQL Server
release all the locks it was holding on the DataStore?
-   Is it possible to run the Table Repair and data
recovery tools programmatically (non-manually)?

I look forward to your feedback and comments.  Thank
you.

Gowtham.

__
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it!
http://webhosting.yahoo.com/ps/sb/

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

RE: My SQL for OLTP Systems?

[Félix Beltrán]

 Hi Alexander,

How do you compare MaxDB features against other DB products, for 
example SQLServer?

Regards
FBR

 "Schroeder, Alexander" <[EMAIL PROTECTED]> 
03/02/2004 09:38 a.m.   
To
"'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>
cc

Subject
RE: My SQL for OLTP Systems?

Hello Felix,

Max DB is in use as database for the SAP R/3 ERP since a long time
(under varying product names). 

Alexander Schröder

> -Original Message-
> From: Félix Beltrán [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, February 03, 2004 4:48 PM
> To: [EMAIL PROTECTED]
> Subject: My SQL for OLTP Systems?
> 
> 
> Has anyone used MySQL as the database for an Online Transaction 
> Proccessing Systems? (for example an ERP)
> What about MaxDB?
> 
> 
> FBR
> 
> 

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

My SQL for OLTP Systems?

[Félix Beltrán]

Has anyone used MySQL as the database for an Online Transaction 
Proccessing Systems? (for example an ERP)
What about MaxDB?


FBR


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

Replication

[Pierre Luguern]

Is this configuration possible with MySQL ?

Server A is acting  as a master server for the A database. Server B is
acting as a slave, replicating the A database from server A.
Server B is acting as a master server for the B Database. Server A is
acting  as a slave, replicating the  B database from server B.


How do the configuration file looks like for the two server's ?

Thanks in advance.






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

RE: Problem with joins and query

[Luis Lebron]

What I want to retrieve is how many hours have been assigned to a project
and category (which is in the Manhours table) and how much time has been
actually used (which is is the Tasks table)

So if I look at the data I can see that ChargeNum 11, catid 19 has a
total of 104 hours assigned in the Manhours table (80 for EmployeeId=200020
and 24 for EmployeeId=23)

Then if I look at the Tasks table I see that ChargeNum 111, Catid 19 has
a total of 36 hours (this are the hours that have been actually used)

What I would like as a final result is something like this 

Catid   Hours Used  Hours Alloted
19  | 36|104

-Original Message-
From: Jigal van Hemert [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 03, 2004 9:05 AM
To: Mysql (E-mail)
Subject: Re: Problem with joins and query


- Original Message - 
From: "Luis Lebron" <[EMAIL PROTECTED]>
> Select M.Catid, sum(M.Hours) as allotedhours, sum(T.Hours) as usedHours
> >From Manhours as M, Tasks as T
> Where M.ProjectId=32
> AND T.ChargeNum=M.ChargeNum
> AND T.EmployeeID=M.EmployeeID
> AND T.Catid=M.Catid
> Group by M.Catid
> Order by M.Catid

It might be more understandable if you read it like:

SELECT M.Catid, SUM(M.Hours) AS allotedhours, SUM(T.Hours) AS usedHours
FROM Manhours AS M INNER JOIN Tasks as T
USING (ChargeNum, EmployeeID, Catid)
WHERE M.ProjectId=32
GROUP BY M.Catid

Is it correct that the two tables are joined on three columns?

Maybe it's best to try and refrase how the data in the two tables is
connected and what exactly you want to retrieve.
Try to think in data: "for project 32 I want all the records from table a
plus the records from table b which have the same value for col_name, sum
the values in col_name and sort them by col_name". From this you will be
able to construct your query easily...

Regards, Jigal.



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

Re: max key length 500 in myisam index

[Victoria Reznichenko]

Adam Hardy <[EMAIL PROTECTED]> wrote:
> I've got to create a table that has the following:
> 
> CREATE TABLE access (
>   query VARCHAR(255) NOT NULL,
>   INDEX (query)
> );
> 
> and mysql is telling that the max bytes allowed is 500 for key length. 
> The docs say I can change this by recompiling, which I would like to 
> avoid having to do. Is there any way around this to get a 255 character 
> length field with index?

Your CREATE TABLE statement worked like charm for me. What version of MySQL server do 
you use?

> This is going to store weblog entries, so it's going to be a huge table. 
> Is there a different table type / column type / index type I could use 
> to achieve what I need?
> 


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [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: Problem with joins and query

[Jigal van Hemert]

- Original Message - 
From: "Luis Lebron" <[EMAIL PROTECTED]>
> Select M.Catid, sum(M.Hours) as allotedhours, sum(T.Hours) as usedHours
> >From Manhours as M, Tasks as T
> Where M.ProjectId=32
> AND T.ChargeNum=M.ChargeNum
> AND T.EmployeeID=M.EmployeeID
> AND T.Catid=M.Catid
> Group by M.Catid
> Order by M.Catid

It might be more understandable if you read it like:

SELECT M.Catid, SUM(M.Hours) AS allotedhours, SUM(T.Hours) AS usedHours
FROM Manhours AS M INNER JOIN Tasks as T
USING (ChargeNum, EmployeeID, Catid)
WHERE M.ProjectId=32
GROUP BY M.Catid

Is it correct that the two tables are joined on three columns?

Maybe it's best to try and refrase how the data in the two tables is
connected and what exactly you want to retrieve.
Try to think in data: "for project 32 I want all the records from table a
plus the records from table b which have the same value for col_name, sum
the values in col_name and sort them by col_name". From this you will be
able to construct your query easily...

Regards, Jigal.



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

Re: [Q] INSERT INTO ... SELECT

[Egor Egorov]

Riaan Oberholzer <[EMAIL PROTECTED]> wrote:
> Is there a limit on the number of rows that can be
> inserted in this way? Ie, at what point should I start
> worrying about "out of memory" errors or something
> similar?
> 
> So, how many rows can/should be returned by the SELECT clause?

There is no limitation in MySQL.



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




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

Problem with joins and query

[Luis Lebron]

I am having a problem with a query that joins the following tables. I want
to create a query that would tell me how many Manhours to a particular
project (32) by category and how many hours (from the Tasks table) have
actually been used for this project and category.


Manhours
 mid| ProjectId |ChargeNum  | Catid | Hours | EmployeeId

  1 | 32|11 | 19| 80| 200020 
  2 | 32|11 | 19| 24|
23 


Tasks

TaskID  |ChargeNum  |Catid  |EmployeeId |Hours  
1   |11 |19 |200020 |8 
2   |11 |19 |200020 |8 
5   |11 |19 |23 |12 


The query I am using looks like this:

Select M.Catid, sum(M.Hours) as allotedhours, sum(T.Hours) as usedHours
>From Manhours as M, Tasks as T
Where M.ProjectId=32 
AND T.ChargeNum=M.ChargeNum
AND T.EmployeeID=M.EmployeeID
AND T.Catid=M.Catid
Group by M.Catid
Order by M.Catid

The results I am expecting are:

Catid   |allotedhours   |usedHours  
19  |104|28 

instead I am getting:

Catid   |allotedhours   |usedHours  
19  |184|28 


So I imagine that I am missing something in my join syntax, but I haven't
been able to figure it out.


Luis R. Lebron
Sigmatech, Inc

Re: disabling wait_timeout?

[Mark Matthews]

Neale Banks said:

>
> Can it be done?
>
> I tried to disable this timer with wait_timeout = 0 in my.cnf.
>
> That changed the timeout (as reported by mysqladmin variables) from the
> default 28800 to 1.  Tested, it was definitely a one-second timeout :-(
>
> As a hack-around, I've currently got it set to ten days.
>
> Alternatively, where is the bounds for the value of wait_timeout
> documented?
>
> Lastly, this is for a long-held JDBC connection from an application server
> which could run for days, no, weeks - and gets upset if mysql closes down
> the connection (e.g. overnight/weekends). Any suggestions on a better way
> to handle this?

Neale,

JDBC connections aren't guaranteed to last forever (I even checked with
the JDBC spec lead on this).

You need to make sure your application has the 'smarts' to handle
connection failure, and re-connect and retry the transaction (if it makes
sense), or mark the current connection as bad, pass the exception up the
stack, and re-establish the connection later.

Connections don't go away just because of wait_timeout, someone might
unplug a switch, restart the database server, ifconfig an interface down,
router flakes out...There are many potential failure scenarios for a
network connection, and only your application will know what the _prudent_
action to take (retry immediately, throw error retry later, shutdown, etc0
is.

Luckily, most application servers' connection pools have parameters you
can configure to expire connections that have been idle too long, test
connections while they are idle to see that they are still valid, test
connections before handing them out, or when getting them returned, etc.,
etc.

Regards,

   -Mark

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

Re: [Q] INSERT INTO ... SELECT

[Krasimir_Slaveykov]

Hello Riaan,

RO> Is there a limit on the number of rows that can be
RO> inserted in this way? Ie, at what point should I start
RO> worrying about "out of memory" errors or something
RO> similar?

RO> So, how many rows can/should be returned by the SELECT clause?

I had a similar case.
Difference is that I tried to read result in the array and then to
work from it.
Hoever ... I think that the problem is in your RAM and OS.
In my case I can read in memory succes up to 3 mil records.
When I tried more I received Out Of Memory, too.
I looked at windos swap file and it was more than 2GB
Hoever, in my case I found desicion to read and insert data record by
record. Slowly, but success.








-- 
Best regards,
Krasimir_Slaveykov, 03 Ôåâðóàðè 2004 ã., 15:49:27 
mailto: [EMAIL PROTECTED]  [EMAIL PROTECTED]

|-|
|/     * * ***    *   ** /|
| *** *** ***   ***   *** *** ** ***    //|
|/// *** *** * * *** ****  *** ///|
|// *** *** ***   ***   *** *** ** ****** |
|/     ***   ***   ***    *  *** /|
|--- www.office1.bg --|


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

Re: SQL query help required

[Jigal van Hemert]

- Original Message - 
From: "Riaan Oberholzer" <[EMAIL PROTECTED]>

> 2-0, 2-1, 2-2, 2-3
> 1-0, 1-1, 1-2, 1-3
> 0-0, 0-1, 0-2, 0-3
>

SELECT CONCAT(predictionA, '-', predictionB) AS score,
COUNT(CONCAT(predictionA, '-', predictionB)) AS count
FROM table
WHERE CONCAT(predictionA, '-', predictionB) > 0
GROUP BY score
ORDER BY predictionA DESC, predictionB

Maybe not the fastest solution, but it is a single query !
The only thing you have to add is that you calculate the grandtotal to
display the "n% has selected.." part

Regards, Jigal.



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

Newbie: Rephrase SQL Statement

[Carlos Vazquez]

Hi, all.  I built this SQL Statement in Access97.  I want to rephrase it
to use it in MySQL3.23.   The problem is I don't know the correct
syntax.  Any help will be really appreciated. Thanks.

SELECT 
bnkrestaurants.sRest, 
bnkdetailpos.dTransDate, 
bnkdetailpos.sTerminalNo, 
bnkcardtypes.sCardDescr, 
bnkdetailpos.sCardNo, 
bnkdetailpos.nAmount, 
bnkdetailpos.sTransType
FROM 
(bnkrestaurants 
RIGHT JOIN (bnkterminals 
RIGHT JOIN (bnkdetailpos 
INNER JOIN refundcards 
ON bnkdetailpos.sCardNo = refundcards.scardno) 
ON bnkterminals.sTerminalNo = bnkdetailpos.sTerminalNo) 
ON bnkrestaurants.sMerchNo = bnkterminals.sMerchNo) 
INNER JOIN bnkcardtypes 
ON bnkdetailpos.sCardType =
bnkcardtypes.sCardType

Re: Replication with HEAP tables & slave insert error

[Egor Egorov]

"Vinod Panicker" <[EMAIL PROTECTED]> wrote:
> 
> "Vinod Panicker" <[EMAIL PROTECTED]> wrote:
> 
>> Also, the slave stopped due to an error of a duplicate key!  Why 
>> should it have a problem with inserting data that went through fine on the
> master?
> 
> Could you describe your problem more detailed? What is the table type? Did
> it happen with HEAP table? What is the failed query?
> 
> --- The slave got an error running an insert into a 4 field table with a
> primary key set on a column 'userid'.  The slave SQL thread apparently
> stopped and the slave IO thread continued running, but the table stopped
> getting updated.  The table type again was HEAP.

Check data on slave. Does this value really exist?
Probably your master server was restarted that was the reason why data in your HEAP 
tables could be lost on the master, but not on the slave.



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




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

SQL query help required

[Riaan Oberholzer]

Hi,

Background: I run a prediction league for soccer
competitions. For every game, I want to show how many
predictions were submitted per scoreline, ie.

10% said 1-0
20% said 0-1
30% said 2-3
etc.

My prediction table has a predictionA and predicionB
column with the submitted scores.

Currently I do something like SELECT MAX(predictionA),
MAX(predictionB) and then check for all the possible
scorelines... if I got 2 and 3, then the possibilities
would be:

2-0, 2-1, 2-2, 2-3
1-0, 1-1, 1-2, 1-3
0-0, 0-1, 0-2, 0-3

If the count for any predictions is 0 (ie no-one
prediction that score), it gets omitted rather than
saying "0%".

These I do with individual selects in a code "for
loop". Is there a better way to do this with less
calls to the database?

Thanks!


__
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it!
http://webhosting.yahoo.com/ps/sb/

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

RE: SQL question: Finding duplicates

[Russell Horn]

> 
> row | foo
> 1   | a
> 2   | c
> 3   | b
> 4   | c
> 5   | a
> 6   | d
> 
> the statement would return me rows 1, 2, 4, and 5.
> 

CREATE TEMPORARY TABLE temptable SELECT *
FROM test
GROUP BY foo
HAVING COUNT( * ) >1
ORDER BY foo ASC ;

SELECT *
FROM test, temptable
WHERE test.foo = temptable.foo
ORDER BY test.row ASC;


-- 
Russell.

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

Re: mysql command line syntax

[Egor Egorov]

"KKoTY" <[EMAIL PROTECTED]> wrote:
> hi, you can try something like this
> 
> mysql --force -uuser -ppass -Ddatabase -e"query.sql"

You forgot source command:

mysql ... -e "source query.sql"

> 
> where you can put all your SQL commands into to file named "query.sql"
> 
> - Original Message -
> From: <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Monday, February 02, 2004 8:38 PM
> Subject: mysql command line syntax
> 
>> Why does the following command, entered as a single line at a bash prompt,
> prompt me to enter more?
>>
>> mysql -u user -ppass -D database -e 'select field into outfile
> \'/tmp/field.1\' from mytable where id = 1;'
>> >
>>
>> And is there any way to supress the 300 lines of useless usage that
> accumpanies every failed attempt to execute a command?
>>



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




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

SQL question: Finding duplicates

[Simon Detheridge]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Disclaimer: I'm not an SQL expert so please don't laugh. ;-)

I'm trying to generate a way to find all rows from a table where the data in 
column 'foo' is a duplicate of the data in another row.

I.E.:

row | foo
1   | a
2   | c
3   | b
4   | c
5   | a
6   | d

the statement would return me rows 1, 2, 4, and 5.

I tried this:
SELECT t1.* FROM mytable AS t1, mytable AS t2 WHERE t1.foo = t2.foo && 
t1.rownum != t2.rownum ;

It works on my test table with 10 records, but takes a really long time on my 
table with a large number (13000) of records.

The numerical column is the primary key, and the other column is indexed.

Is there a way to speed this up, or do it more efficiently?

Thanks,
Simon

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQFAH4LxyEdVKI+MVc8RAiYJAJsFdfGHbkk7RRRIXm8V+fIN28OB2QCfWbbE
bWgRa2AkYlDB0mbQP3GHIMc=
=AWOE
-END PGP SIGNATURE-


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

Re: Runtime Error '-214767259'

[Egor Egorov]

Timotius Alfa <[EMAIL PROTECTED]> wrote:
> 
> Hi all, help me pls, I try to connect to Mysql server, but it raise runtime error 
> -214767259,
> 
> "Host C-Xeon is not allowed to connect to this MYsql server"
> 

This error means that user table in the mysql database doesn't have entry that matches 
host 'C-Xeon'. You should give privileges to the user/host combination from wich you 
are trying to connect.



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




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

Re: Create users : with password

[Victoria Reznichenko]

"Mike Mapsnac" <[EMAIL PROTECTED]> wrote:
> I spend some time to figure how to add users with passwords, but have 
> problem with following:
> 
> 
> Adding user with password: PROBLEM
> 
> When I insert users to database:
> insert into user values('localhost','test', 
> password('123456'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
> and specy the password , I cannot login t the database
> 
> [EMAIL PROTECTED] mike]# mysql -h localhost -u test -p
> Enter password:
> ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)

If you edit privilege tables manually (with INSERT, UPDATE, DELETE statements) you 
should run mysqladmin flush-privileges or execute FLUSH PRIVILEGES command to reload 
grant tables.

> 
> Adding user to database with no password : NO PROBLEMS
> 
> But when I create user without password I have no problems:
> insert into user values('localhost','test', 
> '','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
> 
> [EMAIL PROTECTED] mike]# mysql -h localhost -u test1
> Welcome to the MySQL monitor.  Commands end with ; or \g.
> Your MySQL connection id is 15 to server version: 3.23.58
> 
> Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
> 
> mysql>
> 
> 
> Any ideas what I'm doing wrong?

You added user [EMAIL PROTECTED] without password, but connected using test1 user 
name. Seems you have entry for anonymous user without password in the 'user' table, 
that is why you can connect as user test1. 


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [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]

MyISAM Table Corruption

[Hassan Shaikh]

What are the chances of MyISAM tables corruption when the table is
update rarely? (Once in a 60+ days). It's basically a lookup table
used mainly in SELECT statements.

Thanks.

Hassan


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

[Q] INSERT INTO ... SELECT

[Riaan Oberholzer]

Is there a limit on the number of rows that can be
inserted in this way? Ie, at what point should I start
worrying about "out of memory" errors or something
similar?

So, how many rows can/should be returned by the SELECT clause?

__
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it!
http://webhosting.yahoo.com/ps/sb/

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

InnoDB : Snapshot problem

[DUFOUR Geoffrey]

Hello,

I'm getting problems with the procedure below (6.4 How to Set Up
Replication):

It works fine until I shutdown the MySQL server. When I run the shutdown
command, some of the pending (because of the LOCK) update queries are
executed.

Am I missing something ?

"Without the Hot Backup tool, the quickest way to take a snapshot of
InnoDB tables is to shut down the master server and copy the InnoDB
datafiles and logs, and the table definition files (.frm). To record the
current log file name and offset, you should do the following before you
shut down the server: 
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
And then record the log name and the offset from the output of SHOW
MASTER STATUS as was shown earlier. Once you have recorded the log name
and the offset, shut down the server without unlocking the tables to
make sure it goes down with the snapshot corresponding to the current
log file and offset: 
shell> mysqladmin -uroot shutdown"

Regards.

Geoffrey

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

Re: Performance Problem

[Jigal van Hemert]

- Original Message - 
From: "Ronan Lucio" <[EMAIL PROTECTED]>
> Here it is a slowly query, it take about 15 seconds:
>
> SELECT f.dtrelease AS fdtrelease, f.cod AS fcod, f.title AS title,
>vf.price AS vfprice, vf.cod AS vfcod
> FROM film AS f, rent_film AS r, film_format AS ff, film_sale AS vf
> WHERE r.codrent = 1123
> AND ff.codfil = f.cod
> AND r.film_format = ff.cod
> AND vf.codrf = l.cod
> AND ff.media = 2
> GROUP BY f.cod
> ORDER BY fdtrelease desc
> LIMIT 0,4

A few hints:
- try an EXPLAIN of this query (simply put EXPLAIN before the SELECT
command) and see if the JOINs give a problem. Check if the right key is
used, what the join-type is how many records MySQL thinks it will return and
if the Extra-info lists problems like temporary table, filesort, etc.
- contrary to what the manual says, it can be faster to move some WHERE
conditions to the JOIN conditions:
FROM a JOIN b ON a.c2=b.c2 JOIN (...) WHERE a.c1=value (...)
can be slower than
FROM a JOIN b ON a.c2=b.c2 AND a.c1=value JOIN (...) WHERE (...)
It seems that the query optimiser does not always make the right choices.
I've noticed that limiting the size of the recordset that is the result of a
JOIN can prevent MySQL from using temporary tables, etc.
- for columns with low cardinality (ff.media might only contain 4 types of
media or so) MySQL sometimes uses an index, while it might be faster to do a
full table scan. Using IGNORE INDEX (index_name) in the join seems to speed
up things. You can also drop such an index if it is not needed for other
purposes.
- try a FORCE INDEX (index_name) to override the index MySQL decides to use
for a JOIN

> Another one:
> SELECT f.cod AS fcod, f.title AS title
> FROM film AS f, rent_film AS r, film_format AS ff
> WHERE r.codloc = 1123
> AND ff.codfil = f.cod
> AND r.film_format = ff.cod
> AND f.type <> 21
> AND ( ( TO_DAYS( CURDATE() ) - TO_DAYS( f.dtrelease ) <= 180
> AND TO_DAYS(CURDATE()) - TO_DAYS(f.dtrelease) >= 0 )
> OR ( r.release = 1 AND f.dtrelease <= CURDATE() ) )
> GROUP BY f.cod
> ORDER BY f.dtrelease DESC
> LIMIT 0,5

The date calculations will most certainly slow things down. Try to make
different expressions with a column name on one side and a constant
expression on the other side. I've had a performance gain of 100 times by
doing this.
AND ( ( TO_DAYS( CURDATE() ) - TO_DAYS( f.dtrelease ) <= 180
will most certainly be slower than
AND ( f.dtrelease > CURDATE() - INTERVAL 180 DAYS)
MySQL has to calculate the first expression for every record in the table,
while it only has to use an index in the second case.

NOTE: although the manual may not agree with the solutions I presented here
, the hints I provided come from my observations of a MySQL 4.0.17 database
with a lot of slow queries. I've eleminated most of the slow queries using
these (and other) techniques.

Regards, Jigal.



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

Re: Advise on High Availability configuration

[Dr. Frank Ullrich]

Hi,

one question remains:
what you got is a master - "master slave" - multiple slaves setup, right?
Or did you really set up a multi-master replication? If so, how?

By the way, thank you for sharing your setup, it is very helpful for us 
(as we are looking for a high-availability solution for mysql 
(preferably a multi-master cluster)) but the last bits of mist also need 
to be lifted!

So, basically, if I got you right, in your case no app can choose 
randomly whether to update master1 or master2: it's either master1 
__or__ master2 for __all__ slaves?
In case of error on master1 you fail over to master2 which from now on 
is the only master available for updates?

Thank you again for your insights.

Kind regards,
  Frank.
Andrew Braithwaite schrieb:
Hi,


So how does this bit work? If one master falls over and slaves 
move to master two, how do you rebuild master one without downtime? 
Don't the slaves try and use Master 1 as soon as it's back 
online even though its data is out of sync?


If master 1 fails and the slaves move to master 2, then they effectively
swap roles as primary and secondary masters and the directive is issued to
the webservers to connect in the reverse order.  Yes, master 1 will be
offline until it is rebuilt and will join again in the secondary role.  The
master connect wrapper is robust. If a connection fails it will try to
reconnect a few times and run that query again.  All the slaves and masters
are on the same switch so if one fails, generally speaking, they all fail.

What if one of the slaves loses its heartbeat with master one and 
falls over to master two when other slaves can still see master one. 
Don't you then have inconsistencies in your data?


The heartbeat code is pretty robust too. It's similar to the above wrapper
and has the same benefits.
Having said all that, I did mention that this is an inexpensive medium sized
solution to availability and that it is a read-heavy, write-light DB
scenario.  Additionally there is no data of a financial nature (if there
was, we would be using another set-up - commit / rollback for example..). As
such we are tolerant to very small amounts of data loss in the event of a
failover situation.
On this, I refer you to the "how many 9's do you need" dscussion, nicely
explained by Jeremy here:
http://jeremy.zawodny.com/blog/archives/000805.html

Cheers,

Andrew

-Original Message-
From: Russell Horn [mailto:[EMAIL PROTECTED] 
Sent: Monday 02 February 2004 16:21
To: [EMAIL PROTECTED]
Subject: RE: Advise on High Availability configuration

Andrew Braithwaite wrote:


Each slave keeps a heartbeat to the master and in the event of a 
failure, changes it's master to master2.


So how does this bit work? If one master falls over and slaves move to
master two, how do you rebuild master one without downtime? Don't the slaves
try and use Master 1 as soon as it's back online even though its data is out
of sync?
What if one of the slaves loses its heartbeat with master one and falls over
to master two when other slaves can still see master one. Don't you then
have inconsistencies in your data?
Russell.




--
Dr. Frank Ullrich, DBA Netzwerkadministration
Heise Zeitschriften Verlag GmbH & Co KG, Helstorfer Str. 7, D-30625 Hannover
E-Mail: [EMAIL PROTECTED]
Phone: +49 511 5352 587; FAX: +49 511 5352 538
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]