Re: Foreign keys - No action - Errors

[Frank Schröder]

My understanding is that RESTRICT and NO ACTION share the same behavior.

http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html

   *

 |NO ACTION|: In |ANSI SQL-92| standard, |NO ACTION| means /no
 action/ in the sense that an attempt to delete or update a primary
 key value will not be allowed to proceed if there is a related
 foreign key value in the referenced table (Gruber, Mastering SQL,
 2000:181). Starting from 4.0.18 |InnoDB| rejects the delete or
 update operation for the parent table.

   *

 |RESTRICT|: Rejects the delete or update operation for the parent
 table. |NO ACTION| and |RESTRICT| are the same as omitting the |ON
 DELETE| or |ON UPDATE| clause. (Some database systems have
 deferred checks, and |NO ACTION| is a deferred check. In MySQL,
 foreign key constraints are checked immediately, so |NO ACTION|
 and |RESTRICT| are the same.)

This works only on InnoDB tables as - AFAIK - the InnoDB engine the only 
engine is which implements FK constraints.


on 05/24/2005 07:46 AM Sven Åke Johansson said the following:




I have some problem with Foreign Key settings. I use MySQL 4.12, MySQL Query Browser 4.16 and Windows XP. Restrict and Cascade is Ok but when I try to set No action it wont work. Sometimes there is no error message and it seams that the change is saved. But when I check there is no changes. When an error message shows its nr 1005. 




What is the conditions to set No action. Ok In the manual it says only that No 
action is taken in the child table when rows are deleted from the parent or values in the 
referenced columns in the parent table are updated.



I read the articles on MySQL , a lot of books and the manual but I cant get any answer. 




Thanks for any answer wich will solve my problem.



Sven Åke Johansson

[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] 





 



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

Re: Problems with x86_64 mysql-standard-4.1.12

[Kevin Burton]

Pete Harlan wrote:


In addition to failing the tests, I deployed the server on Machine 1
for a while and it failed quickly, with a simple insert hanging up and
kill threadID being unable to kill it.  (The thread's state was
Killed, but it didn't go away and continued to block other threads
from accessing the (MyISAM) table.)

Any help would be appreciated, and please let me know if I can provide
further information.
 


See the Opteron HOWTO:

http://hashmysql.org/index.php?title=Opteron_HOWTO

Also.. are you running NPTL or Linux Threads?   If you have the 
libc6-i686 package installed you have NPTL (not sure if the mysql binary 
needs support for this or not).


I'd also highly recommend installing a glibc  2.3.2 which is what ships 
on debian.  glibc-2.3.5 is in experimental and its what we're running.


Kevin

--


Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. 
See irc.freenode.net #rojo if you want to chat.


Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html

  Kevin A. Burton, Location - San Francisco, CA
 AIM/YIM - sfburtonator,  Web - http://peerfear.org/
GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 



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

Re: Problems with x86_64 mysql-standard-4.1.12

[Kevin Burton]

Pete Harlan wrote:


Hi,
and then it never comes back, presumably from the auto_increment
test.  If I run the auto_increment test alone (i.e., ./mysql-test-run
auto_increment), it fails in this same way.  When it's hung, mysqld
isn't using any CPU.

 

Also.. CPU isn't the only thing you should be watching.  Run iostat -k 1 
and vmstat 1 to see what type of IO you're running at.  Maybe you're IO 
is just being really slow.


Its semi normal for your mysql box to be slowed down by disk...

Kevin

--


Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. 
See irc.freenode.net #rojo if you want to chat.


Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html

  Kevin A. Burton, Location - San Francisco, CA
 AIM/YIM - sfburtonator,  Web - http://peerfear.org/
GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 



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

Re: db/mysql rookie request

[mfatene]

Hi,
You can use this template. The idea is the same. All your line commands for
certain task can be embedded in a single script :

http://webxadmin.free.fr/article/mysql-use-shell-script-to-dump-all-databases-t-54.php


Mathias

Selon Jorgensen, Bill [EMAIL PROTECTED]:

 MySQL guys:



 I am new to databases, MySQL, and anything outside of my world of UNIX
 system administration. A former colleague of mine set up MySQL on our
 backup server and I would like to interface with the database to get
 reports. I have read a little and understand a few things. I have taken
 the time to develop some SQL to get the data I would like to get.
 However, I want to do this with a Korn shell script that emails the
 report to a list of internal customers. Any help you can provide would
 be greatly appreciated.



 Thanks in advance,



 Bill



 

  Bill Jorgensen

  CSG Systems, Inc.

 







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

how can I get performance statistics

[qin lei]

I need know the execution time of a query. And how many times the I/O 
operation are involved for the query. How can I get this kind of 
infomation?

Are there some ways to add these info into the logfile?
Thank you.
I am a  rookie for Mysql and for this mial-list system. If I broke the rule 
of asking a queation, please let me know.


Victo

_
 MSN Messenger:  http://messenger.msn.com/cn  



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

Connection problem

[razat gupta]

Hello Everyone,

I am working on an architecture on which i am using common database
mysql4  for two web servers(Tomcat 5.x).

After moving to 150 number of connections using a java application,
mysql gives an error (exception): Server Connection Failure during
transaction.Attempt reconnect 3 times.

BUT I HAVE MENTIONED MAXIMUM CONNECTION=500 in my.cnf

But it gives an exception on the reach of 150 connections.It should
allow us to create almost 500 connection.

Can any one tell me how i can solve this problem.

Any Suggestions are most welcome.
Thanks in advance.

regards,

-- 
razat gupta [EMAIL PROTECTED]


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

Re: SCRAMBLE(A,B) (was UDF:Request).

[Dan Bolser]

On Mon, 11 Apr 2005 [EMAIL PROTECTED] wrote:

Dan Bolser [EMAIL PROTECTED] wrote on 04/11/2005 11:50:31 AM:

 On Mon, 11 Apr 2005, Dan Bolser wrote:
 
 
 Requirement: 
 
 Given two columns of a table (Column1 and Column2) of length x, return 
two
 columns (Column1_Scram and Column2_Scram) such that the distributions 
of
 values in Column1 and Column2 are preserved in Column1_Scram and
 Column2_Scram, but the pairs of values are randomized.
 
 
 Solution suggested by Shawn Green: 
 
 Create a table with two columns, and populate this table with random 
pairs
 of primary keys picked from the original table. Additionally, allow no
 duplicate primary keys within either column. Select x rows from this
 table, and link both primary keys (the primary key pair) back to the
 original table to get the appropriate number of randomized pairs of
 Column1 and Column2.
 
 He suggests doing the above like this (more or less):
 
 OriginalTable
 PK   A   B
 1   a   c
 2   a   d
 3   b   e
 ...
 
 CREATE TEMPORARY TABLE IntermediateTable (
   PK1  INT NOT NULL,
   ACHAR(1) NOT NULL,
   PK2  INT NOT NULL,
   BCHAR(1) NOT NULL,
   #
   UNIQUE INDEX (PK1,A),
   UNIQUE INDEX (PK2,B)
 );
 
 INSERT IGNORE INTO IntermediateTable
 SELECT 
   x.PK, x.A,
   y.PK, y.B
 FROM 
   OriginalTable x,
   OriginalTable y
 ORDER BY
   RAND();
 
 SELECT 
   x.A,
   y.B
 FROM
   IntermediateTable 
 INNER JOIN 
   OriginalTable x ON (PK1 = x.PK) INNER JOIN
   OriginalTable y ON (PK2 = y.PK)
 LIMIT 
   the_length_of_OriginalTable;
 
 
 The problem with this solution: 
 
 Its too slow on reasonable sized tables! 
 
 
 Their is also a problem with the way RAND() works...
 
 SELECT
   x.PK, x.A,
   y.PK, y.B
 FROM
   OriginalTable x,
   OriginalTable y
 ORDER BY
   RAND()
 LIMIT 
   1;
 
 This takes soo long to pick a random row. Some cleaver 'LIMIT'
 optimization could pick a results set almost instantly, instead of 
taking
 in excess of half an hour with ~50,000 rows.
 
 
 

Let's try this. I will assume, because you used the PK hack, you have 
duplicate values in at least one of your sets. Let's cure the Rand() speed 
issue by adding a column to Original Table to hold a random number and 
eliminate the lookup problem. Since integer math is much faster than 
floating point math, we will set up this field as an integer field and 
fill it appropriately

ALTER TABLE OriginalTable ADD COLUMN RandomKey INT UNSIGNED;

UPDATE OriginalTable SET RandomKey = RAND()*200;

Let's also modify IntermediateTable like this:

DROP TABLE IntermediateTable;

CREATE TABLE FirstColumn
id INT auto_increment
, a char(1)
, PRIMARY KEY (id) 
);

CREATE TABLE SecondColumn
id INT auto_increment
, b char(1)
, PRIMARY KEY (id)
);

And populate the new tables:
INSERT FirstColumn (a)
SELECT a
FROM OriginalTable
ORDER BY PK1;

INSERT SecondColumn (b)
SELECT b
FROM OriginalTable
ORDER BY RandomKey;

Then get your randomized (A,B) pairs this way:

SELECT x.A, y.B
FROM FirstColumn x
INNER JOIN SecondColumn y
on x.id = y.id;

This should be MUCH faster than 30 mins (I would guess on the order of 2 
or 3 at most). FirstColumn gets filled with data in original order, 
SecondColumn gets filled with data in random order (thanks to the random 
value). By creating new tables to cache those values we create two new 
contiguous auto_increment runs (this way you can analyze subsets of your 
original data and not need to worry about mismatching on the final INNER 
JOIN).

On the next pass, Re-run the UPDATE to assign new RAND() values to your 
data. Do not empty or refill FirstColum. Execute a TRUNCATE TABLE 
SecondColumn; then refill it (INSERT SecondColumn...) and repeat the 
final query. 

HTH!!


I finally got round to trying this out - It works really well!

I did the following...

DROP   TABLE IF EXISTS rand_a;
CREATE TABLE   rand_a (
  PKMEDIUMINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  A_id  INT   UNSIGNED NOT NULL,
  #
  INDEX (A_id)
);

DROP   TABLE IF EXISTS rand_b;
CREATE TABLE   rand_b (
  PKMEDIUMINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  B_id  INT   UNSIGNED NOT NULL,
  #
  INDEX (b_id)
);

INSERT INTO rand_a (A_id)
#
SELECT A_id FROM x ORDER BY RAND();

INSERT INTO rand_b (B_id)
#
SELECT B_id FROM x ORDER BY RAND();


I then get the full list of random pairs (with the same marginal
distribution as before) with...

SELECT *
  FROM rand_a 
INNER JOIN rand_b 
 USING (PK);


I chekcked my marginals like this...

SELECT COUNT(*), COUNT(DISTINCT A_id), COUNT(DISTINCT B_id)
  FROM nrints x;

SELECT COUNT(*), COUNT(DISTINCT A_id), COUNT(DISTINCT B_id)
  FROM rand_a 
INNER JOIN rand_b 
 USING (PK);

And they were both identical - which is what I wanted! The whole thing
takes a couple of seconds on the original 'pair' table which has 20,000
rows.

Thank you!


Shawn Green
Database Administrator

unicode and C API

[Patrice Serrand]

Hello,
I have an InnoDB table running on MySQL 4.1.11 

The MySQL server configuration use : default-character-set=utf8

I have a table unicode_tbl with 'default charset set utf8' and a field szWord 
varchar(50) character set utf8 collate utf8_general_ci.

if  I use the C API like this :
mysql_query (mysql, INSERT INTO db_unicode.unicode_tbl VALUES (6, _utf8 
'atüpedâ' COLLATE utf8_general_ci));
I get the string 'at' in my table.

If I use the same query in 'MySQL Query Browser' I can get the whole string 
'atüpedâ' in my table.
To get the same result I have to write : 
mysql_query (mysql, INSERT INTO db_unicode.unicode_tbl VALUES (6, 
'atüpedâ'));

Everything works like if the C API only accepts ANSI strings.

Is it possible to directly insert an unicode string using the C API ?
Is it possible to get in MYSQL_ROW an unicode string after performing a select 
for example using : swprintf(w_out, _T(%s\n), row[1]); ?

If not is MySQL 5.x will allow this ? when ?

Thanks in advance 

Patrice Serrand

Query to select...

[Dan Bolser]

Hello,

I have data like this

PK  GRP_COL
1   A
2   A
3   A
4   B
5   B
6   B
7   C
8   C
9   C


And I want to write a query to select data like this...

PK  FK  GRP_COL
1   1   A
2   1   A
3   1   A
4   4   B
5   4   B
6   4   B
7   7   C
8   7   C
9   7   C


Where FK is a random (or otherwise) member of PK from within the
appropriate group given by GRP_COL. FK recreates the grouping from
GRP_COL, but in terms of PK. I want to do this because GRP_COL is
difficult to handle and I want to re-represent the grouping in terms of PK
(this allows me to link data into the grouping more easily).

Is there a simple way to do this?



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

Re: how can I get performance statistics

[Gleb Paharenko]

Hello.



Usually the execution time is measured on the client side

(you may implement it in your application). I don't know

how to measure the number of I/O operations involved by a

single query. There is only a global statistics reported by

SHOW STATUS. There is a handy tool in MySQL - EXPLAIN statement. 

See:

  http://dev.mysql.com/doc/mysql/en/show-status.html

http://dev.mysql.com/doc/mysql/en/explain.html











qin lei [EMAIL PROTECTED] wrote:

 I need know the execution time of a query. And how many times the I/O 

 operation are involved for the query. How can I get this kind of 

 infomation?

 Are there some ways to add these info into the logfile?

 Thank you.

 I am a  rookie for Mysql and for this mial-list system. If I broke the rule 

 of asking a queation, please let me know.

 

 Victo

 

 _

 $$ MSN Messenger:  http://messenger.msn.com/cn  

 

 



-- 
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: MySQL give up on a query after a certain length of time

[Gleb Paharenko]

Hello.



Check if problem remains with official binaries.





Jacob Friis Larsen [EMAIL PROTECTED] wrote:

 

 I use those from Debian:

 http://packages.debian.org/testing/misc/mysql-server-4.1

 

 Thanks,

 Jacob

 



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

problem-Lost connection to MySQL server during query

[Ying Sun]

Dear there,

I met some problem  when I try to use \. command to create several tables from 
a file in mysql. I got error as follows:
ERROR 2013 (HY000): Lost connection to MySQL server during query
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket 
'/var/lib/mysql/mysql.sock' (111)
.

I read MySQL Reference Manual http://dev.mysql.com/doc/mysql/en/gone-away.html. 
 Actually, my query file is not large, I think, it is within 1M.  Even I try 
mysql mysql --max_allowed_packet=32M and mysql mysqld 
--max_allowed_packet=16M, it doesn't work either. 

And I also notice, if I type shellmysqld, it gives me error report:
 050524 12:49:35 [Warning] Asked for 196608 thread stack, but got 126976
050524 12:49:35 [ERROR] Can't start server: Bind on TCP/IP port: Address 
already in use
050524 12:49:35 [ERROR] Do you already have another mysqld server running on 
port: 3306 ?
050524 12:49:35 [ERROR] Aborting

050524 12:49:35 [Note] ./mysqld: Shutdown complete

I have no idea about that problem. Could someone can help me? Thanks a lot!

Best Regards,

Ying

Problem with query

[Jon Miller]

Quite new to MySQl and queries in general and would like some help in the 
following query:
select prDate, prName, prLEmployee, prDescription, prTotalHours, prLStatus, 
prCompletionDate, prActive from tProject where prDate =2005-05-09

It generates a listing that has years from 2001 to present.  All I'm looking 
for is information start from 2005-05-09 to present.

Thanks
!DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN
HTMLHEAD
META http-equiv=Content-Type content=text/html; charset=us-ascii
META content=MSHTML 6.00.2900.2627 name=GENERATOR/HEAD
BODY style=MARGIN-TOP: 2px; FONT: 10pt Arial; MARGIN-LEFT: 2px
DIVQuite new to MySQl and queries in general and would like some help in the 
following query:/DIV
DIVselect prDate, prName, prLEmployee, prDescription, prTotalHours, 
prLStatus, 
prCompletionDate, prActive from tProject where prDate gt;=2005-05-09/DIV
DIVnbsp;/DIV
DIVIt generates a listing that has years from 2001 to present.nbsp; All I'm 
looking for is information start from 2005-05-09 to present./DIV
DIVnbsp;/DIV
DIVThanksBR/DIV/BODY/HTML

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

Re: 2 Joins in 1 Query

[shaun thornburgh]

From: [EMAIL PROTECTED]
To: shaun thornburgh [EMAIL PROTECTED]
CC: mysql@lists.mysql.com
Subject: Re: 2 Joins in 1 Query
Date: Mon, 23 May 2005 12:20:05 -0400

shaun thornburgh [EMAIL PROTECTED] wrote on 05/23/2005
10:18:29 AM:

 Hi,

 The following query produces the number of bookings per project grouped
by
 week:

 SELECT WEEK(Booking_Start_Date) AS WEEK,
 SUM(IF(B.Project_ID = 23, 1,0)) AS `Project A`,
 SUM(IF(B.Project_ID = 42, 1,0)) AS `Project B`
 FROM Bookings B INNER JOIN Projects P USING (Project_ID)
 WHERE B.Booking_Type = Booking
 AND DATE_FORMAT(B.Booking_Start_Date, %Y-%m-%d) = '2005-01-01'
 AND DATE_FORMAT(B.Booking_Start_Date, %Y-%m-%d) = '2005-12-31'
 GROUP BY WEEK;

 The problem with this is that if I have no bookings for week 42 for
example
 then that week is not shown in the result. To get round this I created a

 table called Weeks that contains all the week numbers for the year.

 However I am not sure how I can join Weeks to Bookings so that all the
weeks
 show.

 Any healp would be greatly appreciated.

 TABLE DEF'S:

 mysql desc Bookings;
 +-+-+--
 +-+-++
 | Field   | Type| Null | Key
|
 Default | Extra  |
 +-+-+--
 +-+-++
 | Booking_ID  | int(11) |  | PRI
|
 NULL| auto_increment |
 | Booking_Type| varchar(15) |  | |
 Unavailability  ||
 | User_ID | int(11) |  | | 0

||
 | Project_ID  | int(11) | YES  | |
 NULL||
 | Rep_ID  | int(11) | YES  | |
 NULL||
 | Practice_ID | int(11) | YES  | |
 NULL||
 | Booking_Creator_ID  | int(11) | YES  | |
 NULL||
 | Booking_Creation_Date   | datetime| YES  | |
 NULL||
 | Booking_Start_Date  | datetime|  | |
 -00-00 00:00:00 ||
 | Booking_End_Date| datetime|  | |
 -00-00 00:00:00 ||
 | Booking_Completion_Date | date| YES  | |
 NULL||
 | Booking_Mileage | int(5)  | YES  | |
 NULL||
 | Booking_Status  | varchar(15) |  | |
 Other   ||
 | Unavailability_ID   | int(2)  | YES  | |
 NULL||
 | Task_ID | int(11) | YES  | |
 NULL||
 | Work_Type_ID| int(2)  | YES  | |
 NULL||
 | Additional_Notes| text| YES  | |
 NULL||
 +-+-+--
 +-+-++
 22 rows in set (0.00 sec)

 mysql desc Projects;
 ++--+--+-+-
 ++
 | Field  | Type | Null | Key | Default |
Extra
 |
 ++--+--+-+-
 ++
 | Project_ID | int(11)  |  | PRI | NULL|
 auto_increment |
 | Project_Name   | varchar(100) |  | | |
 |
 | Client_ID  | int(11)  |  | | 0   |
 |
 ++--+--+-+-
 ++
 8 rows in set (0.00 sec)

 mysql desc Weeks;
 +-+-+--+-+-++
 | Field   | Type| Null | Key | Default | Extra  |
 +-+-+--+-+-++
 | Week_ID | int(11) |  | PRI | NULL| auto_increment |
 | Week_Number | int(11) |  | | 0   ||
 +-+-+--+-+-++
 2 rows in set (0.00 sec)

 mysql


I think you need just think about what you want and what may or may not
exist as data, then you can figure out which JOINs are LEFT and which are
INNER.  You want one row for each week regardless of whether you have a
Project or a Booking. That makes the Weeks table manditory. There may be
weeks that do not have any Bookings. That makes Bookings the right side of
a LEFT JOIN. Because the existence 

RE: Problem with query

[shaun thornburgh]

From: Jon  Miller [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Subject: Problem with query
Date: Tue, 24 May 2005 19:40:32 +0800

Quite new to MySQl and queries in general and would like some help in the 
following query:
select prDate, prName, prLEmployee, prDescription, prTotalHours, prLStatus, 
prCompletionDate, prActive from tProject where prDate =2005-05-09


It generates a listing that has years from 2001 to present.  All I'm 
looking for is information start from 2005-05-09 to present.


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


Try putting quotes around the date

select prDate, prName, prLEmployee, prDescription, prTotalHours, prLStatus, 
prCompletionDate, prActive from tProject where prDate = '2005-05-09'




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

Re: MySQL give up on a query after a certain length of time

[Jacob Friis Larsen]

 Check if problem remains with official binaries.

I will. My schedule won't allow it right now, but I will get to it soon.
Thanks,
Jacob

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

Re: problem-Lost connection to MySQL server during query

[Gleb Paharenko]

Hello.





Have you looked into error log? Server could die during the query execution.





Dear there,



I met some problem  when I try to use \. command to create several

tables from a file in

mysql. I got error as follows:

ERROR 2013 (HY000): Lost connection to MySQL server during query

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

ERROR 2002 (HY000): Can't connect to local MySQL server through socket

'/var/lib/mysql/mysql.sock' (111)

.



I read MySQL Reference Manual

http://dev.mysql.com/doc/mysql/en/gone-away.html.  Actually,

my query file is not large, I think, it is within 1M.  Even I try

mysql mysql

--max_allowed_packet=32M and mysql mysqld --max_allowed_packet=16M,

it doesn't

work either. 



And I also notice, if I type shellmysqld, it gives me error report:

 050524 12:49:35 [Warning] Asked for 196608 thread stack, but got

126976

050524 12:49:35 [ERROR] Can't start server: Bind on TCP/IP port: Address

already in use

050524 12:49:35 [ERROR] Do you already have another mysqld server

running on port: 3306 ?

050524 12:49:35 [ERROR] Aborting



050524 12:49:35 [Note] ./mysqld: Shutdown complete



I have no idea about that problem. Could someone can help me? Thanks a

lot!



Best Regards,



YingYing Sun [EMAIL PROTECTED] wrote:



-- 
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: Query to select...

[mfatene]

Hi,
try for axample,

select PK, ord(GRP_COL),COL from your_table;


Mathias

Selon Dan Bolser [EMAIL PROTECTED]:


 Hello,

 I have data like this

 PKGRP_COL
 1 A
 2 A
 3 A
 4 B
 5 B
 6 B
 7 C
 8 C
 9 C


 And I want to write a query to select data like this...

 PKFK  GRP_COL
 1 1   A
 2 1   A
 3 1   A
 4 4   B
 5 4   B
 6 4   B
 7 7   C
 8 7   C
 9 7   C


 Where FK is a random (or otherwise) member of PK from within the
 appropriate group given by GRP_COL. FK recreates the grouping from
 GRP_COL, but in terms of PK. I want to do this because GRP_COL is
 difficult to handle and I want to re-represent the grouping in terms of PK
 (this allows me to link data into the grouping more easily).

 Is there a simple way to do this?



 --
 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: Query to select...

[SGreen]

Dan Bolser [EMAIL PROTECTED] wrote on 05/24/2005 06:08:32 AM:

 
 Hello,
 
 I have data like this
 
 PK   GRP_COL
 1   A
 2   A
 3   A
 4   B
 5   B
 6   B
 7   C
 8   C
 9   C
 
 
 And I want to write a query to select data like this...
 
 PK   FK   GRP_COL
 1   1   A
 2   1   A
 3   1   A
 4   4   B
 5   4   B
 6   4   B
 7   7   C
 8   7   C
 9   7   C
 
 
 Where FK is a random (or otherwise) member of PK from within the
 appropriate group given by GRP_COL. FK recreates the grouping from
 GRP_COL, but in terms of PK. I want to do this because GRP_COL is
 difficult to handle and I want to re-represent the grouping in terms of 
PK
 (this allows me to link data into the grouping more easily).
 
 Is there a simple way to do this?
 
 

I don't understand your choice of column name for your new column. PK 
generally means PRIMARY KEY and FK generally means FOREIGN KEY. FOREIGN 
KEYs are used to enforce relational data integrity between tables. What it 
looks like you want to do is to tag every row in a group with the lowest 
(minimum) PK value for that group. To me, that is not a FK.

To do what you want will either take a subquery or a separate table. I 
think the separate table will perform faster so I will show you that 
method.

CREATE TEMPORARY TABLE tmpPK (KEY GRP_COL)
SELECT GRP_COL,MIN(PK) as minpk
FROM datatable
GROUP BY GRP_COL;

ALTER TABLE datatable ADD COLUMN FK INT;

UPDATE datatable
INNER JOIN tmpPK
ON tmpPK.GRP_COL = datatable.GRP_COL
SET datatable.FK = tmpPK.minpk;

DROP TEMPORARY TABLE tmpPK;

The slowest part of all of this will be adding the column to your table.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Query to select...

[Michael Stassen]

How on earth will that help?  What does the ASCII (byte) code of GRP_COL 
have to do with what Dan wants?


[EMAIL PROTECTED] wrote:


Hi,
try for axample,

select PK, ord(GRP_COL),COL from your_table;


Mathias

Selon Dan Bolser [EMAIL PROTECTED]:



Hello,

I have data like this

PK  GRP_COL
1   A
2   A
3   A
4   B
5   B
6   B
7   C
8   C
9   C


And I want to write a query to select data like this...

PK  FK  GRP_COL
1   1   A
2   1   A
3   1   A
4   4   B
5   4   B
6   4   B
7   7   C
8   7   C
9   7   C


Where FK is a random (or otherwise) member of PK from within the
appropriate group given by GRP_COL. FK recreates the grouping from
GRP_COL, but in terms of PK. I want to do this because GRP_COL is
difficult to handle and I want to re-represent the grouping in terms of PK
(this allows me to link data into the grouping more easily).

Is there a simple way to do this?



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

Re: 2 Joins in 1 Query

[SGreen]

shaun thornburgh [EMAIL PROTECTED] wrote on 05/24/2005 
07:35:03 AM:

 From: [EMAIL PROTECTED]
 To: shaun thornburgh [EMAIL PROTECTED]
 CC: mysql@lists.mysql.com
 Subject: Re: 2 Joins in 1 Query
 Date: Mon, 23 May 2005 12:20:05 -0400
 
 shaun thornburgh [EMAIL PROTECTED] wrote on 05/23/2005
 10:18:29 AM:
 
   Hi,
  
   The following query produces the number of bookings per project 
grouped
 by
   week:
  
   SELECT WEEK(Booking_Start_Date) AS WEEK,
   SUM(IF(B.Project_ID = 23, 1,0)) AS `Project A`,
   SUM(IF(B.Project_ID = 42, 1,0)) AS `Project B`
   FROM Bookings B INNER JOIN Projects P USING (Project_ID)
   WHERE B.Booking_Type = Booking
   AND DATE_FORMAT(B.Booking_Start_Date, %Y-%m-%d) = '2005-01-01'
   AND DATE_FORMAT(B.Booking_Start_Date, %Y-%m-%d) = '2005-12-31'
   GROUP BY WEEK;
  
   The problem with this is that if I have no bookings for week 42 for
 example
   then that week is not shown in the result. To get round this I 
created a
 
   table called Weeks that contains all the week numbers for the year.
  
   However I am not sure how I can join Weeks to Bookings so that all 
the
 weeks
   show.
  
   Any healp would be greatly appreciated.
  
   TABLE DEF'S:
  
   mysql desc Bookings;
   +-+-+--
   +-+-++
   | Field   | Type| Null | 
Key
 |
   Default | Extra  |
   +-+-+--
   +-+-++
   | Booking_ID  | int(11) |  | 
PRI
 |
   NULL| auto_increment |
   | Booking_Type| varchar(15) |  | 
|
   Unavailability  ||
   | User_ID | int(11) |  | 
| 0
 
  ||
   | Project_ID  | int(11) | YES  | 
|
   NULL||
   | Rep_ID  | int(11) | YES  | 
|
   NULL||
   | Practice_ID | int(11) | YES  | 
|
   NULL||
   | Booking_Creator_ID  | int(11) | YES  | 
|
   NULL||
   | Booking_Creation_Date   | datetime| YES  | 
|
   NULL||
   | Booking_Start_Date  | datetime|  | 
|
   -00-00 00:00:00 ||
   | Booking_End_Date| datetime|  | 
|
   -00-00 00:00:00 ||
   | Booking_Completion_Date | date| YES  | 
|
   NULL||
   | Booking_Mileage | int(5)  | YES  | 
|
   NULL||
   | Booking_Status  | varchar(15) |  | 
|
   Other   ||
   | Unavailability_ID   | int(2)  | YES  | 
|
   NULL||
   | Task_ID | int(11) | YES  | 
|
   NULL||
   | Work_Type_ID| int(2)  | YES  | 
|
   NULL||
   | Additional_Notes| text| YES  | 
|
   NULL||
   +-+-+--
   +-+-++
   22 rows in set (0.00 sec)
  
   mysql desc Projects;
   ++--+--+-+-
   ++
   | Field  | Type | Null | Key | Default |
 Extra
   |
   ++--+--+-+-
   ++
   | Project_ID | int(11)  |  | PRI | NULL|
   auto_increment |
   | Project_Name   | varchar(100) |  | | |
   |
   | Client_ID  | int(11)  |  | | 0   |
   |
   ++--+--+-+-
   ++
   8 rows in set (0.00 sec)
  
   mysql desc Weeks;
   +-+-+--+-+-++
   | Field   | Type| Null | Key | Default | Extra  |
   +-+-+--+-+-++
   | Week_ID | int(11) |  | PRI | NULL| auto_increment |
   | Week_Number | int(11) |  | | 0   ||
   +-+-+--+-+-++
   2 rows in set (0.00 sec)
  
   mysql
  
  
 I think you need just think about what you want and what may or may not
 exist as data, then you 

Re: Query to select...

[Dan Bolser]

On Tue, 24 May 2005 [EMAIL PROTECTED] wrote:

Hi,
try for axample,

select PK, ord(GRP_COL),COL from your_table;


When I said my data was 'like' the below I should have been more specific.
Here is some of my data (with reference to the simplified data (and
question details) below);

INPUT:

PK- --GRP_COL
+--+-+-++
| PDB  | ASS | LIST| COUNTS |
+--+-+-++
| 104l |   1 | 104lA   | 1  |
| 104l |   2 | 104lA   | 1  |
| 104m |   1 | 104m-   | 1  |
| 105m |   1 | 104m-   | 1  |
| 117e |   1 | 117eA   | 2  |
| 11ba |   1 | 11baA   | 2  |
| 11bg |   1 | 11baA   | 2  |
| 12e8 |   1 | 12e8H,12e8L | 2,2|
| 12e8 |   2 | 12e8H,12e8L | 2,2|
...


OUTPUT (wanted):

FK PK-
+-+-+--+-+
| REP_PDB | REP_ASS | PDB  | ASS |
+-+-+--+-+
| 104l|   1 | 104l |   1 |
| 104l|   1 | 104l |   2 |
| 104m|   1 | 104m |   1 |
| 104m|   1 | 105m |   1 |
| 117e|   1 | 117e |   1 |
| 11ba|   1 | 11ba |   1 | 
| 11ba|   1 | 11bg |   1 | 
| 12e8|   1 | 12e8 |   1 | 
| 12e8|   1 | 12e8 |   2 | 
...


Cheers,





Mathias

Selon Dan Bolser [EMAIL PROTECTED]:


 Hello,

 I have data like this

 PK   GRP_COL
 1A
 2A
 3A
 4B
 5B
 6B
 7C
 8C
 9C


 And I want to write a query to select data like this...

 PK   FK  GRP_COL
 11   A
 21   A
 31   A
 44   B
 54   B
 64   B
 77   C
 87   C
 97   C


 Where FK is a random (or otherwise) member of PK from within the
 appropriate group given by GRP_COL. FK recreates the grouping from
 GRP_COL, but in terms of PK. I want to do this because GRP_COL is
 difficult to handle and I want to re-represent the grouping in terms of PK
 (this allows me to link data into the grouping more easily).

 Is there a simple way to do this?



 --
 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: Problems with x86_64 mysql-standard-4.1.12 [SOLVED]

[Pete Harlan]

On Mon, May 23, 2005 at 11:52:50PM -0700, Kevin Burton wrote:
 Pete Harlan wrote:
 
 In addition to failing the tests, I deployed the server on Machine 1
 for a while and it failed quickly, with a simple insert hanging up and
 kill threadID being unable to kill it.  (The thread's state was
 Killed, but it didn't go away and continued to block other threads
 from accessing the (MyISAM) table.)
 
 Any help would be appreciated, and please let me know if I can provide
 further information.
  
 
 See the Opteron HOWTO:
 
 http://hashmysql.org/index.php?title=Opteron_HOWTO
 
 Also.. are you running NPTL or Linux Threads?   If you have the 
 libc6-i686 package installed you have NPTL (not sure if the mysql binary 
 needs support for this or not).
 
 I'd also highly recommend installing a glibc  2.3.2 which is what ships 
 on debian.  glibc-2.3.5 is in experimental and its what we're running.

What a difference a library makes...that was it, thank you!

I had read the Opteron HOWTO, and tried that library with another
problem I was having and it hadn't made a difference, so I reverted to
2.3.2 and forgot to try it here.

[To answer your other questions: NPTL, I don't think libc6-i686 is for
64-bit, and there was no disk i/o either.]

Thanks again!

--Pete


 
 Kevin
 
 -- 
 
 
 Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. 
 See irc.freenode.net #rojo if you want to chat.
 
 Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html
 
   Kevin A. Burton, Location - San Francisco, CA
  AIM/YIM - sfburtonator,  Web - http://peerfear.org/
 GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 
 
 
 -- 
 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]

problem-Lost connection to MySQL server during query

[Ying Sun]

Hi,

I install Mysql 4.1.12-0 by rpm. When I try to create a table, it give me Lost 
connection to MySQL server during query. 
My query is like:  
CREATE TABLE ArrayBatch (
  id int(11) NOT NULL auto_increment,
  arrayType int(11) NOT NULL default '0',
  descr text NOT NULL,
  printStartDate datetime NOT NULL default '-00-00 00:00:00',
  printEndDate datetime NOT NULL default '-00-00 00:00:00',
  owner int(11) NOT NULL default '0',
  name varchar(80) NOT NULL default '',
  addedDate date NOT NULL default '-00-00',
  surfaceType varchar(255) NOT NULL default '',
  protocol int(11) NOT NULL default '0',
  notes text NOT NULL,
  removed tinyint(1) NOT NULL default '0',
  gid int(11) NOT NULL default '0',
  groupAccess tinyint(1) NOT NULL default '0',
  worldAccess tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (id),
  KEY arrayType (arrayType),
  KEY name (name)
) TYPE=MyISAM;

But when I try to create some simple table, it works. Is it bug or I did 
something wrong? 
Hope someone can give me help.

regards,
Ying

Re: Query to select...

[Michael Stassen]

[EMAIL PROTECTED] wrote:


Dan Bolser [EMAIL PROTECTED] wrote on 05/24/2005 06:08:32 AM:


Hello,

I have data like this

PK   GRP_COL
1   A
2   A
3   A
4   B
5   B
6   B
7   C
8   C
9   C

And I want to write a query to select data like this...

PK   FK   GRP_COL
1   1   A
2   1   A
3   1   A
4   4   B
5   4   B
6   4   B
7   7   C
8   7   C
9   7   C

Where FK is a random (or otherwise) member of PK from within the
appropriate group given by GRP_COL. FK recreates the grouping from
GRP_COL, but in terms of PK. I want to do this because GRP_COL is
difficult to handle and I want to re-represent the grouping in terms of 


PK


(this allows me to link data into the grouping more easily).

Is there a simple way to do this?


I don't understand your choice of column name for your new column. PK 
generally means PRIMARY KEY and FK generally means FOREIGN KEY. FOREIGN 
KEYs are used to enforce relational data integrity between tables. What it 
looks like you want to do is to tag every row in a group with the lowest 
(minimum) PK value for that group. To me, that is not a FK.


To do what you want will either take a subquery or a separate table. I 
think the separate table will perform faster so I will show you that 
method.


CREATE TEMPORARY TABLE tmpPK (KEY GRP_COL)
SELECT GRP_COL,MIN(PK) as minpk
FROM datatable
GROUP BY GRP_COL;

ALTER TABLE datatable ADD COLUMN FK INT;

UPDATE datatable
INNER JOIN tmpPK
ON tmpPK.GRP_COL = datatable.GRP_COL
SET datatable.FK = tmpPK.minpk;

DROP TEMPORARY TABLE tmpPK;

The slowest part of all of this will be adding the column to your table.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


I was writing a similar answer, but you beat me to it.

This is the right idea, but I don't think you go far enough.  This 
solution does what Dan asks, but I don't think it's what he really 
needs.  Surely, the problem is that the data isn't normalized.  GRP_COL 
contains repeated strings and is difficult to handle.  I'd suggest a 
permanent, rather than temporary, fix.  Something like:


  CREATE TABLE groups
  (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY)
  SELECT DISTINCT GRP_COL AS group_name
  FROM datatable;

  ALTER TABLE datatable ADD grp_id INT UNSIGNED NOT NULL;

  UPDATE datatable
  JOIN groups ON datatable.GRP_COL = groups.group_name
  SET datatable.grp_id = groups.id;

Check first, then

  ALTER TABLE datatable DROP COLUMN GRP_COL;

Now you join to the new groups table when you need the group name.

Michael




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

Re: Query to select...

[mfatene]

Hi,
I read FK is a random (or otherwise)

But i prefer your help !!


Selon Michael Stassen [EMAIL PROTECTED]:


 How on earth will that help?  What does the ASCII (byte) code of GRP_COL
 have to do with what Dan wants?

 [EMAIL PROTECTED] wrote:

  Hi,
  try for axample,
 
  select PK, ord(GRP_COL),COL from your_table;
 
 
  Mathias
 
  Selon Dan Bolser [EMAIL PROTECTED]:
 
 
 Hello,
 
 I have data like this
 
 PK  GRP_COL
 1   A
 2   A
 3   A
 4   B
 5   B
 6   B
 7   C
 8   C
 9   C
 
 
 And I want to write a query to select data like this...
 
 PK  FK  GRP_COL
 1   1   A
 2   1   A
 3   1   A
 4   4   B
 5   4   B
 6   4   B
 7   7   C
 8   7   C
 9   7   C
 
 
 Where FK is a random (or otherwise) member of PK from within the
 appropriate group given by GRP_COL. FK recreates the grouping from
 GRP_COL, but in terms of PK. I want to do this because GRP_COL is
 difficult to handle and I want to re-represent the grouping in terms of PK
 (this allows me to link data into the grouping more easily).
 
 Is there a simple way to do this?


 --
 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: Query to select...

[SGreen]

Michael Stassen [EMAIL PROTECTED] wrote on 05/24/2005 10:26:14 
AM:

 [EMAIL PROTECTED] wrote:
 
  Dan Bolser [EMAIL PROTECTED] wrote on 05/24/2005 06:08:32 AM:
  
 Hello,
 
 I have data like this
 
 PK   GRP_COL
 1   A
 2   A
 3   A
 4   B
 5   B
 6   B
 7   C
 8   C
 9   C
 
 And I want to write a query to select data like this...
 
 PK   FK   GRP_COL
 1   1   A
 2   1   A
 3   1   A
 4   4   B
 5   4   B
 6   4   B
 7   7   C
 8   7   C
 9   7   C
 
 Where FK is a random (or otherwise) member of PK from within the
 appropriate group given by GRP_COL. FK recreates the grouping from
 GRP_COL, but in terms of PK. I want to do this because GRP_COL is
 difficult to handle and I want to re-represent the grouping in terms 
of 
  
  PK
  
 (this allows me to link data into the grouping more easily).
 
 Is there a simple way to do this?
  
  I don't understand your choice of column name for your new column. PK 
  generally means PRIMARY KEY and FK generally means FOREIGN KEY. 
FOREIGN 
  KEYs are used to enforce relational data integrity between tables. 
What it 
  looks like you want to do is to tag every row in a group with the 
lowest 
  (minimum) PK value for that group. To me, that is not a FK.
  
  To do what you want will either take a subquery or a separate table. I 

  think the separate table will perform faster so I will show you that 
  method.
  
  CREATE TEMPORARY TABLE tmpPK (KEY GRP_COL)
  SELECT GRP_COL,MIN(PK) as minpk
  FROM datatable
  GROUP BY GRP_COL;
  
  ALTER TABLE datatable ADD COLUMN FK INT;
  
  UPDATE datatable
  INNER JOIN tmpPK
  ON tmpPK.GRP_COL = datatable.GRP_COL
  SET datatable.FK = tmpPK.minpk;
  
  DROP TEMPORARY TABLE tmpPK;
  
  The slowest part of all of this will be adding the column to your 
table.
  
  Shawn Green
  Database Administrator
  Unimin Corporation - Spruce Pine
 
 I was writing a similar answer, but you beat me to it.
 
 This is the right idea, but I don't think you go far enough.  This 
 solution does what Dan asks, but I don't think it's what he really 
 needs.  Surely, the problem is that the data isn't normalized.  GRP_COL 
 contains repeated strings and is difficult to handle.  I'd suggest a 
 permanent, rather than temporary, fix.  Something like:
 
CREATE TABLE groups
(id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY)
SELECT DISTINCT GRP_COL AS group_name
FROM datatable;
 
ALTER TABLE datatable ADD grp_id INT UNSIGNED NOT NULL;
 
UPDATE datatable
JOIN groups ON datatable.GRP_COL = groups.group_name
SET datatable.grp_id = groups.id;
 
 Check first, then
 
ALTER TABLE datatable DROP COLUMN GRP_COL;
 
 Now you join to the new groups table when you need the group name.
 
 Michael
 

I agree, especially with the additional information the OP provided about 
his REAL table structure. A separate groups table makes better sense. 

Let this be an object lesson to others looking for assistance: If you want 
timely and useful assistance, provide real and complete information 
whenever possible. SHOW CREATE TABLE gives much better information than 
DESCRIBE or SHOW COLUMNS. If you do chose to obfuscate your real table and 
data layout then be prepared to translate whatever advice you receive. 

Sorry it took so long to get to the bottom of this design issue.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Query to select...

[Dan Bolser]

blip

I agree, especially with the additional information the OP provided about 
his REAL table structure. A separate groups table makes better sense. 

Let this be an object lesson to others looking for assistance: If you want 
timely and useful assistance, provide real and complete information 
whenever possible. SHOW CREATE TABLE gives much better information than 
DESCRIBE or SHOW COLUMNS. If you do chose to obfuscate your real table and 
data layout then be prepared to translate whatever advice you receive. 

Eeep! I often try to simplify my problem to the bare bones before asking a
question, as it is often quite tricky to work out what you really want to
do (tm) and put it in its simplest form - also I often find that doing
this gives me the answer, and I can just delete my email before I ever
send it!


Sorry it took so long to get to the bottom of this design issue.

Now to work out what I really want to do ;) - I will look over the
answers and see if I am any closer...

Thanks all for the help.


Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine






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

Re: problem-Lost connection to MySQL server during query

[Gleb Paharenko]

Hello.



Resolve a stack trace. See:

  http://dev.mysql.com/doc/mysql/en/using-stack-trace.html  





 Hi,

 This is my first time to instll mysql. So I have not so much

 experience. I am apprecia

ted your help!

 Now I check my mysqld.log file, there is something like:



 mysqld got signal 11;

 This could be because you hit a bug. It is also possible that this

 binary

 or one of the libraries it was linked against is corrupt, improperly

 built,

 or misconfigured. This error can also be caused by malfunctioning

 hardware.

 We will try our best to scrape up some info that will hopefully help

 diagnose

 the problem, but since we have already crashed, something is

 definitely wrong

 and this may fail.



 key_buffer_size=8388600

 read_buffer_size=131072

 max_used_connections=1

 max_connections=100

 threads_connected=1

 It is possible that mysqld could use up to

 key_buffer_size + (read_buffer_size +

 sort_buffer_size)*max_connections = 225791

 K

 bytes of memory

 Hope that's ok; if not, decrease some variables in the equation.



 thd=0x8939120

 Attempting backtrace. You can use the following information to find

 out

 where mysqld died. If you see no messages after this, something went

 terribly wrong...

 Cannot determine thread, fp=0xbfe7ea88, backtrace may not be correct.

 Stack range sanity check OK, backtrace follows:

 0x808df77

 0x82e8af8

 0x8561759

 0x8084834

 0x80b4c0e

 0x80a2c14

 0x809d0f2

 0x809cab4

 0x809c167

 0x82e62ac

 0x830fc3a





-- 
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: how can I get performance statistics

[Gleb Paharenko]

qin lei [EMAIL PROTECTED] wrote:



I don't know how to implement this on the server side. Please,

next time answer to the list as well.







Hi,

Thank you for your reply.

In my project, I need to know the prapagation time and execution time

respectively. So I can not measure the execution time on the client

side. Is there any way to do this on the server side?





 _

 $$ MSN Messenger:  http://messenger.msn.com/cn  

 

 



-- 
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: Even if the `published` column is 0, it still shows the title...

[Michael Stassen]

Computer Programmer wrote:
SELECT t2.`col1`, t3.`title`, t3.`col3`, t4.`published` FROM t1 LEFT 
JOIN t2 ON (t2.`catid`=t1.`id`) LEFT JOIN t3 ON (t3.`id`=t2.`sid`) CROSS 
JOIN `main_data` t4 ON (t4.`id`=t2.`sid`) WHERE t2.`catid`='7' AND 
t4.`published`=1 GROUP BY `sid` ORDER BY t3.`title`;


What this does is to show all the titles under catid (Category ID) 
that is published.


0 = not publish
1 = publish

The problem is, even if the `published` column is 0, it will still show 
the title.


I tried to do the reverse, `published`=0, and it will still show 
everything instead of just showing the unpublished titles.


I'm using MySQL 4.1.12 under Windows XP.


It's a good idea to take the time to reformat your query to make it 
easily readable.   That helps us to help you.  (Sometimes, you may even 
see the problem yourself as a result.)  Your query reformatted:


  SELECT t2.col1, t3.title, t3.col3, t4.published
  FROM t1
  LEFT JOIN t2 ON (t2.catid=t1.id)
  LEFT JOIN t3 ON (t3.id=t2.sid)
  CROSS JOIN main_data t4 ON (t4.id=t2.sid)
  WHERE t2.catid=7
AND t4.published=1
  GROUP BY sid
  ORDER BY t3.title;

Shawn has already posted an analysis of some problems here.  Let me add 
to his answer.


First, it is unclear why table t1 is part of this query, as its presence 
adds nothing.  No column from t1 is selected or used.  The only 
possibility is that you intended to get results for rows in t1 which 
don't have corresponding rows in t2, but those are thrown away by 
subsequent joins and the WHERE clause.


Second, you seem to be misusing GROUP BY. You are grouping on sid, but 
you are selecting t2.col1, t3.title, t3.col3, and t4.published.  When 
you use GROUP BY, it usually only makes sense to select grouped columns 
and aggregate functions.  Indeed, many systems wouldn't allow your query 
because of the non-grouped columns in the SELECT clause.  MySQL allows 
selecting non-grouped columns, but you are warned not to do so unless 
the non-grouped columns are guaranteed to have groupwise unique values. 
 Otherwise, you get nonsense results.  I think it's possible there's a 
flaw in the logic of your joins which is obfuscated by the grouping on sid.


Here's a substitute query:

  SELECT t2.col1, t3.title, t3.col3, t4.published
  FROM main_data t4
  JOIN t2 ON t2.sid=t4.id
  LEFT JOIN t3 ON t3.id=t2.sid
  WHERE t2.catid=7
AND t4.published=1
  ORDER BY sid, t3.title;

I left out table t1, rearranged the order of the joins, and dropped the 
grouping on sid.  Instead of grouping on sid, I collect all the rows 
with the same sid by adding sid to the ORDER BY.  If this returns 
expected results, then there's a good chance that the {RIGHT} JOIN 
after a LEFT JOIN bug is part of the problem, as Shawn suggests, but if 
this produces unexpected results, then you (also) have a logic problem.


Of course, all this is based on the query you gave us, which I'm 
guessing isn't your real query, unless you actually have tables named 
t1, t2, t3, and t4, with columns named col1 and col3.  It may be that 
the problems I've found stem from the translation of your query to the 
one you gave us.


Michael

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

DB design question

[Koon Yue Lam]

Hi, here is the case:

one student may have more than one address, and one student may have more 
than one phone number

so the db would be:

student

student_id
name
age

address
---
address_id
student_id
street_name

phone_num
--
student_id
num
extension

the key of 3 tables are student_id

the problems is, when I want to query both student, address and phone num, 
the sql will be

select * from student s, address a, phone_num n
where s.student_id = a.sudent_id
and s.student_id = n.student_id

it won't provide a nice result as data of student are repeated in every row, 
address and phone_num's data are repeated in certain rows
The output is not suitable for reporting and may I ask what is the better 
way of design to handle the above case ?

any help would be apreciated

Regards

RE: DB design question

[Bartis, Robert M (Bob)]

Something like this would make more sense to me and provide greater flexibility;

student

student_id
name
age

address
---
address_id
street_name
city
state
zip

phone_num
--
phone_num_id
num
extension
type (cell, home, etc)
primaryNumber (yes/no)


student_info
---
student_id_FK
phone_num_id_FK
address_id_FK

Spent all of 10 mins on this so its not perfect. Bottom line is I would not 
include the student_id in the address and phone tables. It precludes a student 
having multiple phones or addresses with out duplicate data. The addition of 
the student_info table provide the 1:1 or 1:N mapping you're looking for I 
believe. The only thing you need to ensure is properly set the Cascade on 
update and restrict on delete options to ensure data integrity.

My gut tells me it may be a better implementation to map the student/phone and 
student/address separately and then create the student_info using keys from 
these intermediate tables, but it more complicated and it not clear what the 
constraints on your problem is.

Bob Bartis







-Original Message-
From: Koon Yue Lam [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 24, 2005 1:34 PM
To: mysql@lists.mysql.com
Subject: DB design question


Hi, here is the case:

one student may have more than one address, and one student may have more 
than one phone number

so the db would be:

student

student_id
name
age

address
---
address_id
student_id
street_name

phone_num
--
student_id
num
extension

the key of 3 tables are student_id

the problems is, when I want to query both student, address and phone num, 
the sql will be

select * from student s, address a, phone_num n
where s.student_id = a.sudent_id
and s.student_id = n.student_id

it won't provide a nice result as data of student are repeated in every row, 
address and phone_num's data are repeated in certain rows
The output is not suitable for reporting and may I ask what is the better 
way of design to handle the above case ?

any help would be apreciated

Regards

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

RE: DB design question

[Berman, Mikhail]

Koon Yue Lam,

If you running your MySQL on Windows, you may try to use one of the
reporting tools, like Crystal Report, to create your reports.
Generally these tools allow to hide repetitive data in its reports 

Mikhail Berman

-Original Message-
From: Koon Yue Lam [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, May 24, 2005 1:34 PM
To: mysql@lists.mysql.com
Subject: DB design question

Hi, here is the case:

one student may have more than one address, and one student may have
more than one phone number

so the db would be:

student

student_id
name
age

address
---
address_id
student_id
street_name

phone_num
--
student_id
num
extension

the key of 3 tables are student_id

the problems is, when I want to query both student, address and phone
num, the sql will be

select * from student s, address a, phone_num n where s.student_id =
a.sudent_id and s.student_id = n.student_id

it won't provide a nice result as data of student are repeated in every
row, address and phone_num's data are repeated in certain rows The
output is not suitable for reporting and may I ask what is the better
way of design to handle the above case ?

any help would be apreciated

Regards

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

RE: DB design question

[Mike Johnson]

From: Koon Yue Lam [mailto:[EMAIL PROTECTED] 

 Hi, here is the case:
 
 one student may have more than one address, and one student 
 may have more than one phone number
 
 so the db would be:
 
 student
 
 student_id
 name
 age
 
 address
 ---
 address_id
 student_id
 street_name
 
 phone_num
 --
 student_id
 num
 extension
 
 the key of 3 tables are student_id
 
 the problems is, when I want to query both student, address 
 and phone num, the sql will be
 
 select * from student s, address a, phone_num n
 where s.student_id = a.sudent_id
 and s.student_id = n.student_id
 
 it won't provide a nice result as data of student are 
 repeated in every row, address and phone_num's data are 
 repeated in certain rows
 The output is not suitable for reporting and may I ask what 
 is the better way of design to handle the above case ?

It's good DB design, but you need to not `select *' but the specific
fields you'd like.

An example of might be:

SELECT s.name, s.age, a.street_name, n.num, n.extension 
FROM students s 
JOIN address a ON a.student_id = s.student_id 
JOIN phone_num n ON n.student_id = s.student_id 

HTH!


-- 
Mike Johnson Smarter Living, Inc.
Web Developerwww.smartertravel.com
[EMAIL PROTECTED]   (617) 886-5539


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

RE: DB design question

[Mike Johnson]

From: Mike Johnson [mailto:[EMAIL PROTECTED] 

 From: Koon Yue Lam [mailto:[EMAIL PROTECTED] 
 
  the problems is, when I want to query both student, address 
  and phone num, the sql will be
  
  select * from student s, address a, phone_num n
  where s.student_id = a.sudent_id
  and s.student_id = n.student_id
  
  it won't provide a nice result as data of student are 
  repeated in every row, address and phone_num's data are 
  repeated in certain rows
  The output is not suitable for reporting and may I ask what 
  is the better way of design to handle the above case ?
 
 It's good DB design, but you need to not `select *' but the specific
 fields you'd like.
 
 An example of might be:
 
 SELECT s.name, s.age, a.street_name, n.num, n.extension 
 FROM students s 
 JOIN address a ON a.student_id = s.student_id 
 JOIN phone_num n ON n.student_id = s.student_id 

I just realized I sort of misread your question (or, rather, only read
the first half of it).

I guess my question is whether or not you're using some sort of
front-end scripting language to retrieve results or using the MySQL
client straight. If the former, you can definitely work with the data in
the way you'd like, but as for the latter, the MySQL client itself
wasn't actually meant to be used as any sort of reporting tool. It
certainly isn't made to make data look nice.   :)

Might you be using PHP, Perl, or something else like that?


-- 
Mike Johnson Smarter Living, Inc.
Web Developerwww.smartertravel.com
[EMAIL PROTECTED]   (617) 886-5539


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

Re: DB design question

[Martijn Tonies]

 Something like this would make more sense to me and provide greater
flexibility;

It doesn't to me...

 student
 
 student_id
 name
 age

 address
 ---
 address_id
 street_name
 city
 state
 zip

What addresses are these? Random addresses where a student _might_ live?

 phone_num
 --
 phone_num_id
 num
 extension
 type (cell, home, etc)
 primaryNumber (yes/no)

Again, random phone numbers possibily owned by a student?


 student_info
 ---
 student_id_FK
 phone_num_id_FK
 address_id_FK



If an address isn't any address, why doesn't it relate to a student?

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle  MS SQL
Server
Upscene Productions
http://www.upscene.com


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

GCC 4.0, mysql and Opteron?

[Kevin Burton]

I'm curious what people here think of compiling mysql with gcc 4.0... 
Especially on Opteron.


I've heard that the way to go with Opteron is to use gcc-3.4 but that 
its a little unstable.


Of course it might be too early to find out if gcc 4.0 is better than 3.4...

Kevin

--


Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. 
See irc.freenode.net #rojo if you want to chat.


Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html

  Kevin A. Burton, Location - San Francisco, CA
 AIM/YIM - sfburtonator,  Web - http://peerfear.org/
GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 



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

RE: DB design question

[Gordon]

You probably want to add type to both the address and phone tables. Then you
can be selective in your reporting and still get 1 row per student in your
result set. Just remember if your data has the possibility of not having the
information for a student you want to use LEFT JOIN's vs INNER JOIN's or the
student with no primary phone [in the following statement] will not be
included in the result set.

SELECT student_id, 
   name, 
   age,  
   h.street_name AS home_address, 
   s.street name AS school_address,
   n.num AS primary_phone
FROM   student s
   LEFT JOIN address s 
   USING (student_id) 
   LEFT JOIN address h 
   USING (student_id)
   INNER JOIN phone_num n
   USING (student_id)
WHERE  h.type = 'Home' 
   AND s.type = 'School'
   AND n.type = 'Primary'

-Original Message-
From: Koon Yue Lam [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, May 24, 2005 12:34 PM
To: mysql@lists.mysql.com
Subject: DB design question

Hi, here is the case:

one student may have more than one address, and one student may have more 
than one phone number

so the db would be:

student

student_id
name
age

address
---
address_id
student_id
street_name

phone_num
--
student_id
num
extension

the key of 3 tables are student_id

the problems is, when I want to query both student, address and phone num, 
the sql will be

select * from student s, address a, phone_num n
where s.student_id = a.sudent_id
and s.student_id = n.student_id

it won't provide a nice result as data of student are repeated in every row,

address and phone_num's data are repeated in certain rows
The output is not suitable for reporting and may I ask what is the better 
way of design to handle the above case ?

any help would be apreciated

Regards



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

Re: complicated query | no Sub query

[Anoop kumar V]

Thanks Peter - you gave me some ideas...
here is what I have so far (simplified for simplification..)

select t2.dt_aud_rec, t1.id_secr_rqst from isr2_aud_log t1, isr2_aud_log t2
where t1.id_secr_rqst=t2.id_secr_rqst
and t1.dt_aud_rec  t2.dt_aud_rec
group by t1.id_secr_rqst

but the problem is that it only returns the record related to the second 
largest date for each id_secr_rqst.

any suggestions how to get both the second and the largest date records in 
the same query?

Thanks,
Anoop


On 5/23/05, Peter Normann [EMAIL PROTECTED] wrote:
 
 Anoop kumar V mailto:[EMAIL PROTECTED] wrote:
 
  well - actually it might not be the last 2 days - i just want 2 of
  the latest records for every task regardless of what date it is in
  the table.
 
 Okay, now I think I understand what you need - and if I am correct, this
 looks like one of the more exotic querys to me, but then again, I'm not 
 like
 the SQL king around, but let me give it a shot:
 
 SELECT t1.id_secr_rqst, t2.name_rec_type, t1.dt_aud_rec, MAX(dt_aud_rec)
 Latest, MAX(dt_aud_rec) NoSoLatest
 FROM isr2_aud_log t1, isr2_aud_log t2
 WHERE t1.id_secr_rqst = t2.id_secr_rqst
 AND t1.name_rec_type='Exception Resource'
 AND dt_aud_rec = Latest
 OR dt_aud_rec = NoSoLatest
 HAVING Latest  NoSoLatest
 GROUP BY t1.id_secr_rqst
 ORDER by t1.dt_aud_rec DESC;
 
 I am not 100% sure about the syntax, but you might get the idea.
 
 Peter Normann
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
 
 


-- 
Thanks and best regards,
Anoop

4.0.23 - error 127 from table handler during many parallel inserts

[Joe Kislo]

We have been very successfully running MySQL in a production
environment one way or another for the past 6 years.  We have recently
run into what I believe is a thread race condition while writing then
reading from a MyISAM table.  The server we are experiencing this
problem on is a very stable environment, very rarely has anything
changed on this machine in the past 4 years (other than mysql upgrades,
and security updates). The machine is running Debian Woody (stable), I
have included the libraries from mysqlbug and kernel version at the end
of this email.  The machine is a Dell Poweredge 6450 4 processor XEON
700/2MB, running local hardware raid with an LSI controller.  I include
the hardware configuration because it's possible the thread interaction
problem may lay closer to the hardware level (or compiler) since the box
is a 4 CPU machine with fairly massive L2 caches (even by today's
standards) on each chip that need to be kept synchronized.  There have
been firmware upgrades for this machine in the past to fix 'cpu
synchronization' issues, however I do not know if we are running these
fixes or not.  We are running MySQL 4.0.23 Mysql-binary  (so it should
be statically linked anyway).  I've looked in the changelog for 4.0.24
and 4.0.25 and I don't see any updates that might resolve our issue.

Unfortunately I have no solid test case for this issue and it only
occurs under times of heavy stress.  The problem has manifested itself
twice out of the past two times a customer has been doing 'massive'
batch configuration changes to our system.  They do these changes once a
month, and the past two months this issue has occurred.  The issue
manifests itself in a very particular way, and has been practically
exactly the same both times.  I am hoping somebody can give me a
direction to take this, either to open a MySQL support case,
mysqldumping the table and reinserting the table, looking at the
firmware updates...  

The problem is we get an error 127 from table handler error when doing
a select:

General error,  message from server: Got error 127 from table handler
-- SQLQuery was:select  DISTINCT
Activity.activityID,Activity.processID,Activity.activityName, []
from Activity where ( Activity.processID='147008' AND
Activity.activityName='VIMforQuiz' )

This, ofcourse, crashes out our XML processing and the worker thread
will end up stopping.  What is happening when this happens is there are
atleast 3-4 active threads running on our application server processing
tens of thousands of XML files, and making major changes to the
database.  Each thread is essentially performing the same duty, but just
with different data.  These threads are primarily database bound. 
Replication is active on this server, and there are 3 replication
servers pulling the updates from this server.  None of their tables are
corrupted by this (by virtue of check table).  Looking in the MySQL
binlog for the timeperiod when this occurs (it, ofcourse, does not
include selects), shows about 200-300 inserts/updates going on during
the 1 second period when this issue happens.  There happens to be 3
separate threads inserting records into the Activity table during that
second (they must just happened to have converged).  I am guessing that
the record the above select query is trying to query, has just been
inserted (I found the insert for the record during that same 1 second as
the crash).  

Performing a check table reports everything is kosher:

mysql check table Activity;
+-+---+--+--+
| Table   | Op| Msg_type | Msg_text |
+-+---+--+--+
| abc.Activity| check | status   | OK   |
+-+---+--+--+
1 row in set (5 min 36.80 sec)

Last month I did a repair table aswell, and that reported everything was
fine aswell, but obviously didn't fix anything.

Once this crash occurs, that worker thread will quit... but all other
threads will continue to pound away on this and other tables, and will
work perfectly fine with no errors.  The table is a MyISAM Dynamic
table, as shown from this show table status:

| Activity | MyISAM | Dynamic   
|  4681274 |105 |   505218432 |  4294967295 |   
166466560 |  10512548 |4960114 | 2004-10-14 14:04:25 |
2005-05-24 00:57:34 | 2005-05-24 00:57:10 || |

The free space probably was more like zero when the incident occurred,
all of the commands I have run here were several hours after the
incident.

perror 127 says that the record file is crashed.  What will cause MySQL
to return such an error during a lookup? Clearly the table *itself* is
not marked as crashed, so I suspect what must be happening is the SELECT
statement is being allowed to read from the table while an insert is
still writing... It presumably should be locked out during that
operation, 

Re: DB design question

[SGreen]

Martijn Tonies [EMAIL PROTECTED] wrote on 05/24/2005 02:32:05 PM:

 
 
 
  Something like this would make more sense to me and provide greater
 flexibility;
 
 It doesn't to me...
 
  student
  
  student_id
  name
  age
 
  address
  ---
  address_id
  street_name
  city
  state
  zip
 
 What addresses are these? Random addresses where a student _might_ live?

Not necessarily random but yes, those would be addresses.

 
  phone_num
  --
  phone_num_id
  num
  extension
  type (cell, home, etc)
  primaryNumber (yes/no)
 
 Again, random phone numbers possibily owned by a student?

Yes. Again, not necessarily random. 

 
 
  student_info
  ---
  student_id_FK
  phone_num_id_FK
  address_id_FK
 
 

I think this table works well because most phone numbers are linked with 
an address. If the student has two addresses (a home address and a school 
address) and 4 phone numbers (two home phone numbers, a school phone, and 
a cell phone), there would need to be 4 records added to this table. The 
data would look something like this:

student_id, address_id, phone_num_id

4,2,15
4,2,16
4,13,22
4,41,89


 
 If an address isn't any address, why doesn't it relate to a student?

Odds are, if an address is not related to at least one student, it 
wouldn't exist in the data. However, imagine you have been asked to build 
a student finder database for a university. It should be practical to 
pre-load your database with all of the addresses of the on-campus housing 
(all known student addresses). In that case you could have several dozen 
address records in your database before adding any student records at 
all. The relevance of the address records is not apparent if you just look 
only at the structure. Rather it comes from the choice of the data you 
populate the tables with.

 
 With regards,
 
 Martijn Tonies
 Database Workbench - tool for InterBase, Firebird, MySQL, Oracle  MS 
SQL
 Server
 Upscene Productions
 http://www.upscene.com
 

Koon Yue Lam:  To repeat what others have said, the query will correctly 
return repetitive information for your student fields if there is more 
than one address or phone number or some combination of either per 
student. Data retrieval tools are generally not intended to present 
hierarchical information in a hierarchical manner. That is generally 
accomplished with data analysis tools or data presentation tools or 
user-written code.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: DB design question

[Martijn Tonies]

Shawn,

I agree with you that the tables can have different info with regard to
the requirements.

But for storing only addresses for specific students, this 4 table design
seems weirdish to me... I think it makes more sense to keep a
student_id in the Addresses table...

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle  MS SQL Server
Upscene Productions
http://www.upscene.com

   
Something like this would make more sense to me and provide greater
   flexibility;
   
   It doesn't to me...
   
student

student_id
name
age
   
address
---
address_id
street_name
city
state
zip
   
   What addresses are these? Random addresses where a student _might_ live?

  Not necessarily random but yes, those would be addresses. 

   
phone_num
--
phone_num_id
num
extension
type (cell, home, etc)
primaryNumber (yes/no)
   
   Again, random phone numbers possibily owned by a student?

  Yes. Again, not necessarily random. 

   
   
student_info
---
student_id_FK
phone_num_id_FK
address_id_FK
   
   

  I think this table works well because most phone numbers are linked with an 
address. If the student has two addresses (a home address and a school address) 
and 4 phone numbers (two home phone numbers, a school phone, and a cell phone), 
there would need to be 4 records added to this table. The data would look 
something like this: 

  student_id, address_id, phone_num_id 
   
  4,2,15 
  4,2,16 
  4,13,22 
  4,41,89 


   
   If an address isn't any address, why doesn't it relate to a student?

  Odds are, if an address is not related to at least one student, it wouldn't 
exist in the data. However, imagine you have been asked to build a student 
finder database for a university. It should be practical to pre-load your 
database with all of the addresses of the on-campus housing (all known student 
addresses). In that case you could have several dozen address records in your 
database before adding any student records at all. The relevance of the address 
records is not apparent if you just look only at the structure. Rather it comes 
from the choice of the data you populate the tables with. 

  Koon Yue Lam:  To repeat what others have said, the query will correctly 
return repetitive information for your student fields if there is more than one 
address or phone number or some combination of either per student. Data 
retrieval tools are generally not intended to present hierarchical information 
in a hierarchical manner. That is generally accomplished with data analysis 
tools or data presentation tools or user-written code. 

  Shawn Green
  Database Administrator
  Unimin Corporation - Spruce Pine 

Re: unicode and C API

[Warren Young]

Patrice Serrand wrote:


mysql_query (mysql, INSERT INTO db_unicode.unicode_tbl VALUES (6, _utf8 
'atüpedâ' COLLATE utf8_general_ci));


I'm no Unicode expert, but I've never seen that _utf8 bit before.  What 
is it?  Or more accurately, what do you expect it to do?  I ask because 
that string is probably already in UTF-8 form, if your text editor is 
UTF-8 aware.  The common Unix text editors are, as is the editor in the 
Visual Studio IDE.


I've done very similar things with MySQL++, the C++ wrapper for the C 
API, which I maintain.  http://tangentsoft.net/mysql++/  No _utf8 stuff 
was required.  Check out examples/resetdb.cpp and examples/custom3.cpp 
in the distribution.



Everything works like if the C API only accepts ANSI strings.


Nonsense.  All the C API cares about are null-terminated C strings, 
which UTF-8 data is.


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

Re: Connection problem

[Warren Young]

razat gupta wrote:


But it gives an exception on the reach of 150 connections.It should
allow us to create almost 500 connection.


Use netstat on the server to find out how many connections are actually 
in use.  You may find that your program is not properly closing down 
connnections, for instance, leaving them in various WAIT states 
(TIME_WAIT, FIN_WAIT1...etc.).  If old connections are not being 
completely cleaned up, they can count against that 500 limit.


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

rpm install on RHEL4 x86-64 does not create MySQL grant tables

[Laser, Mary]

Description:
rpm install of MySQL-server-standard-4.1.12-0.rhel4.x86_64.rpm on RHEL4
does
not create the MySQL grant tables.
How-To-Repeat:
install with rpm -ivh MySQL-server-standard-4.1.12-0.rhel4.x86_64.rpm on
x86-64 RHEL4 system.  Post installation attempts to start mysqld but
fails due to missing grant tables.
Fix:
I tried to install the grant tables manually using mysql_install_db
but, this fails also.  no known workaround.

Submitter-Id:  [EMAIL PROTECTED]
Originator:root
Organization:  Hewlett-Packard
MySQL support: licence
Synopsis:  rpm install on RHEL4 x86-64 does not create MySQL grant
tables
Severity:  critical
Priority:  high
Category:  mysql
Class: sw-bug
Release:   mysql-4.1.12-standard (MySQL Community Edition -
Standard (GPL))

C compiler:gcc (GCC) 3.4.3 20041212 (Red Hat 3.4.3-9.EL4)
C++ compiler:  gcc (GCC) 3.4.3 20041212 (Red Hat 3.4.3-9.EL4)
Environment:
System: Linux venus-b3.lra 2.6.9-5.ELsmp #1 SMP Wed Jan 5 19:29:47 EST
2005 x86_64 x86_64 x86_64 GNU/Linux
Architecture: x86_64

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Reading specs from /usr/lib/gcc/x86_64-redhat-linux/3.4.3/specs
Configured with: ../configure --prefix=/usr --mandir=/usr/share/man
--infodir=/usr/share/info --enable-shared --enable-threads=posix
--disable-checking --with-system-zlib --enable-__cxa_atexit
--disable-libunwind-exceptions --enable-languages=c,c++,objc,java,f77
--enable-java-awt=gtk --host=x86_64-redhat-linux
Thread model: posix
gcc version 3.4.3 20041212 (Red Hat 3.4.3-9.EL4)
Compilation info: CC='gcc'  CFLAGS='-O2 -g -pipe -m64'  CXX='gcc'
CXXFLAGS='-O2 -g -pipe -m64'  LDFLAGS=''  ASFLAGS=''
LIBC:
lrwxrwxrwx  1 root root 13 Feb  7 03:58 /lib/libc.so.6 - libc-2.3.4.so
-rwxr-xr-x  1 root root 1439335 Dec 20 03:13 /lib/libc-2.3.4.so
Configure command: ./configure '--disable-shared'
'--with-server-suffix=-standard' '--without-embedded-server'
'--with-innodb' '--with-archive-storage-engine' '--without-bench'
'--without-berkeley-db' '--without-vio' '--without-openssl'
'--enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql'
'--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/'
'--with-extra-charsets=complex' '--exec-prefix=/usr'
'--libexecdir=/usr/sbin' '--libdir=/usr/lib64' '--sysconfdir=/etc'
'--datadir=/usr/share' '--localstatedir=/var/lib/mysql'
'--infodir=/usr/share/info' '--includedir=/usr/include'
'--mandir=/usr/share/man' '--enable-thread-safe-client'
'--with-comment=MySQL Community Edition - Standard (GPL)'
'--with-readline' 'CC=gcc' 'CFLAGS=-O2 -g -pipe -m64'
'CPPFLAGS=-DBIG_TABLES' 'CXXFLAGS=-O2 -g -pipe -m64' 'CXX=gcc'

Mary Laser
Open Source and Linux Organization
Hewlett-Packard Company
[EMAIL PROTECTED]
970.898.0878

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

data length vs index length ??

[Michael Gale]

Hello,

Currently I have a large mysql table (36 million rows) and according to
the mysql-admin the data length is 6.5GB and my index length is 8.8GB. 

I am new to mysql, but I would assume that my index should not consume
more space then my data ?

But in order to provide the data in a timely manner I do not believe I
can remove any of my indexes as there were all added as a result of a
query taking 30-40 minutes.

Michael

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

Re: complicated query | no Sub query

[SGreen]

Anoop kumar V [EMAIL PROTECTED] wrote on 05/24/2005 03:02:11 PM:

 Thanks Peter - you gave me some ideas...
 here is what I have so far (simplified for simplification..)
 
 select t2.dt_aud_rec, t1.id_secr_rqst from isr2_aud_log t1, isr2_aud_log 
t2
 where t1.id_secr_rqst=t2.id_secr_rqst
 and t1.dt_aud_rec  t2.dt_aud_rec
 group by t1.id_secr_rqst
 
 but the problem is that it only returns the record related to the second 

 largest date for each id_secr_rqst.
 
 any suggestions how to get both the second and the largest date records 
in 
 the same query?
 
 Thanks,
 Anoop
 
 
 On 5/23/05, Peter Normann [EMAIL PROTECTED] wrote:
  
  Anoop kumar V mailto:[EMAIL PROTECTED] wrote:
  
   well - actually it might not be the last 2 days - i just want 2 of
   the latest records for every task regardless of what date it is in
   the table.
  
  Okay, now I think I understand what you need - and if I am correct, 
this
  looks like one of the more exotic querys to me, but then again, I'm 
not 
  like
  the SQL king around, but let me give it a shot:
  
  SELECT t1.id_secr_rqst, t2.name_rec_type, t1.dt_aud_rec, 
MAX(dt_aud_rec)
  Latest, MAX(dt_aud_rec) NoSoLatest
  FROM isr2_aud_log t1, isr2_aud_log t2
  WHERE t1.id_secr_rqst = t2.id_secr_rqst
  AND t1.name_rec_type='Exception Resource'
  AND dt_aud_rec = Latest
  OR dt_aud_rec = NoSoLatest
  HAVING Latest  NoSoLatest
  GROUP BY t1.id_secr_rqst
  ORDER by t1.dt_aud_rec DESC;
  
  I am not 100% sure about the syntax, but you might get the idea.
  
  Peter Normann
  
  
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe: 
http://lists.mysql.com/[EMAIL PROTECTED]
  
  
 
 
 -- 
 Thanks and best regards,
 Anoop

I would solve this query by first constructing a table that contains the 
information I need to identify the two most recent records (tasks). SINCE 
YOU HAVE YET TO POST AN ACTUAL TABLE STRUCTURE (shame on you), I will be 
forced to make up nearly every part of my answer. And because you want it 
to be cross-database portable, I won't be able to use the group-wize 
autonumber trick. However this will use a technique twice like the 
group-wize-maximum technique (described here: 
http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html )

CREATE TEMPORARY TABLE tmpMaxDates (
task_id varchar(10) not null,
task_date date not null,
INDEX(task_id, task_date) 
);

INSERT tmpMax (task_id, task_date)
SELECT task_ID, max(task_date)
FROM tasktable
GROUP BY task_ID;

#now collect the max(PK) value for each task_id/task_date pair

CREATE TEMPORARY TABLE tmpRecordsToProcess (
task_id varchar(10) not null,
task_date date not null,
task_pk int not null
INDEX(task_pk) 
);

INSERT tmpRecordsToProcess rtp (task_id, task_date, task_pk)
SELECT tt.task_id, tt.task_date, max(tt.pk)
FROM tasktable tt
INNER JOIN tmpMaxDates md
on tt.task_id = md.task_id
AND tt.task_date = md.task_date
GROUP BY tt.task_id, tt.task_date;

# now to get the second record back

DELETE FROM tmpMaxDates;

INSERT tmpMaxDates (task_id, task_date)
SELECT tt.task_id, max(tt.task_date)
FROM tasktable tt
LEFT JOIN tmpRecordsToProcess rtp
ON rtp.task_pk = tt.PK
WHERE rtp.task_pk is null
GROUP BY tt.task_id;


INSERT tmpRecordsToProcess (task_id, task_date, task_pk)
SELECT tt.task_id, tt.task_date, max(tt.pk)
FROM tasktable tt
INNER JOIN tmpMaxDates md
on tt.task_id = md.task_id
AND tt.task_date = md.task_date
LEFT JOIN tmpRecordsToProcess rtp
ON rtp.task_pk = tt.PK
WHERE rtp.task_PK is null
GROUP BY tt.task_id, tt.task_date;

Now (assuming I am not too hosed-up today) you should be able to process 
against tmpRecordsToProcess (using the task_pk field) to limit your 
queries to just those PK values you have identified as being the two most 
recent for each task (assuming a higher PK value is more recent than a 
lower one for the same task/date pair). This would have been much easier 
to code if we had been able to use the group-wize auto-increment feature 
of MyISAM. You can repeat the last 3 statements as often as you wish in 
order to build a larger most recent list. I am sure that if I made any 
logical errors, fresher minds on the list will catch them as I am nearing 
the end of a rather long day and could have easily mis-typed something.

To summarize: tmpRecordsToProcess should contain a list of the primary key 
values of the two most recent records for each task.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: data length vs index length ??

[mfatene]

Hi,
if you have a lot of indexes on your table, it's normal that they consume  more
than data. That's the general case in a lot of databases.

You've just to monitor index usage and drop the unused ones, before they impact
the insert/update/delete performance.

Mathias

Selon Michael Gale [EMAIL PROTECTED]:

 Hello,

 Currently I have a large mysql table (36 million rows) and according to
 the mysql-admin the data length is 6.5GB and my index length is 8.8GB.

 I am new to mysql, but I would assume that my index should not consume
 more space then my data ?

 But in order to provide the data in a timely manner I do not believe I
 can remove any of my indexes as there were all added as a result of a
 query taking 30-40 minutes.

 Michael

 --
 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: data length vs index length ??

[Dan Nelson]

In the last episode (May 24), Michael Gale said:
 Currently I have a large mysql table (36 million rows) and according
 to the mysql-admin the data length is 6.5GB and my index length is
 8.8GB.
 
 I am new to mysql, but I would assume that my index should not
 consume more space then my data ?
 
 But in order to provide the data in a timely manner I do not believe
 I can remove any of my indexes as there were all added as a result of
 a query taking 30-40 minutes.

If you have multiple-column indexes and they overlap, you can easily
end up with more space used up in indexes than data.  For example: two
indexes, one on (firstname,lastname), and the other on
(lastname,firstname).

Also, if you have a lot of deletes and updates, your indexes will end
up with unused space in the index blocks.  The table space freed up by
a deleted row can be reused by any row, but if you are indexing on last
name, the space freed up in an index block by deleting a row with
Smith in it can only be filled by another Smith.  That's why it's a
good idea to run optimize table occasionally if you make lots of
changes to your tables.  That will rebuild your indexes and remove the
slack space.

-- 
Dan Nelson
[EMAIL PROTECTED]

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

Re: data length vs index length ??

[SGreen]

Michael Gale [EMAIL PROTECTED] wrote on 05/24/2005 04:10:35 PM:

 Hello,
 
 Currently I have a large mysql table (36 million rows) and according to
 the mysql-admin the data length is 6.5GB and my index length is 8.8GB. 
 
 I am new to mysql, but I would assume that my index should not consume
 more space then my data ?
 
 But in order to provide the data in a timely manner I do not believe I
 can remove any of my indexes as there were all added as a result of a
 query taking 30-40 minutes.
 
 Michael
 

This makes excellent sense. An index is physically stored as a list of 
values (just as they exist in the table or as just the first n characters 
of a value (called a prefix)) and a pointer back into the database (where 
the record is physically located within the datafile). Depending on how 
many indexes you have on your tables, how you constructed your indexes, 
and the physical shape of the data being indexed, it is possible that your 
indexes will take up space that is MUCH larger than the table(s) they are 
built from. 

To reduce the disk space consumed by your indexes, try these tips: 

a: Create the fewest number of indexes you need to keep your application 
performing at an acceptable rate. 
b: Use multi-column indexes when practical to do so. 
c: Avoid index duplication. What I mean is that if you have an index on 
(id, name), you shouldn't create another index on (id, date) unless it 
really helps your performance.
d: Tune your queries whenever practical to use existing indexes to avoid 
creating new ones especially for seldom run queries.

Basically we pay a price in disk space for query performance. Faster 
results generally means more space consumed. The down-side is the more 
indexes you have on a table, the slower an INSERT or DELETE will be as 
either command requires updating all of your indexes for each and every 
row added or removed.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

classic outer join problem

[Hank]

I have a table of school class assignments with the following fields -
the first four fields are the primary key:

Year (int) 
Term (enum, Spring, Fall,Winter)
ClassID (int)
SectionID (int)
Attachement (varchar 225)

The attachment field is a pointer to a file in the OS of an uploaded
file, in the format like this:  
/uploads/2003/Fall/330/1/conversions.doc

When an old class item is imported into a new class item for a new
year/term/class/section (new record), the attachment field is copied
over, and the OS file stays where it is.. the attachment field value
is then a pointer the to the OS file for a previous year/term (i.e.
the OS file is not copied to the new location filesystem structure).

I've been trying to construct a self-joining query to list all the
attachments which are NOT referenced by some future
year/term/class/section.   The desired query result is a list of files
I can DELETE - i.e. files not imported or being pointed to by any
other class_item record in a different year/term.

Keep in mind that pointer to files in the same Year/Term (but
different class/section) are NOT to be deleted.

The system currently has MySQL version 4.0.1, so I can't use
subqueries (i.e. NOT IN (...)).

Any suggestions would be greatly appreciated. thanks.
-Hank

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

Re: classic outer join problem

[SGreen]

Hank [EMAIL PROTECTED] wrote on 05/24/2005 05:02:47 PM:

 I have a table of school class assignments with the following fields -
 the first four fields are the primary key:
 
 Year (int) 
 Term (enum, Spring, Fall,Winter)
 ClassID (int)
 SectionID (int)
 Attachement (varchar 225)
 
 The attachment field is a pointer to a file in the OS of an uploaded
 file, in the format like this: 
 /uploads/2003/Fall/330/1/conversions.doc
 
 When an old class item is imported into a new class item for a new
 year/term/class/section (new record), the attachment field is copied
 over, and the OS file stays where it is.. the attachment field value
 is then a pointer the to the OS file for a previous year/term (i.e.
 the OS file is not copied to the new location filesystem structure).
 
 I've been trying to construct a self-joining query to list all the
 attachments which are NOT referenced by some future
 year/term/class/section.   The desired query result is a list of files
 I can DELETE - i.e. files not imported or being pointed to by any
 other class_item record in a different year/term.
 
 Keep in mind that pointer to files in the same Year/Term (but
 different class/section) are NOT to be deleted.
 
 The system currently has MySQL version 4.0.1, so I can't use
 subqueries (i.e. NOT IN (...)).
 
 Any suggestions would be greatly appreciated. thanks.
 -Hank
 
 -- 
It's almost trivial to detect what is NOT in a list, if you have a list to 
compare to. Do you have a table listing all of the files in the catalog 
area on your disk? If you do then we can detect which files are no longer 
in use and those will be the ones you can delete.

Something else you may be able to do is to run a query returning the 
MAX(year) for each attachment

SELECT attachment, max(year)
FROM catalogtable
GROUP BY attachment;

Then eliminate all of the attachments that are too old.

Just some ideas.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Create fulltext index

[Scott Purcell]

Hello,
I created table a while back  like so:
CREATE TABLE CATEGORY (
   cat_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   parent_id INT,
   visible varchar(1) NOT NULL DEFAULT 'Y',
   sort INT,
   name varchar(200)
) TYPE=InnoDB;

I believe I created it as type InnoDB for a foreign key relationship .

Anyway, I want to create a fulltext index on the table, but I cannot because it 
is not supported by InnoDB, but by MyISAM (according to the docs).

As it stands I have 1000+ records in the table, and do not know what to do. Can 
I alter the table to be MyISAM? And what is a MyISAM vs InnoDB? This always 
confuses me.

And if I can alter to MyISAM, can I still have it related to a foreign key for 
delete on cascades?

Thanks
Scott



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

Re: complicated query | no Sub query

[Anoop kumar V]

My profound apologies

here is the table create structure.
the biggest problem i think is that this table does not have any primary 
keys or atleast unique columns: (I think joins require unique columns)

mysql show create table isr2_aud_log\G
*** 1. row ***
Table: isr2_aud_log
Create Table: CREATE TABLE `isr2_aud_log` (
`id_secr_rqst` varchar(64) NOT NULL default '',
`dt_aud_rec` datetime NOT NULL default '-00-00 00:00:00',
`name_rec_type` varchar(30) default NULL,
`cd_rqst_type` varchar(15) default NULL,
`id_user` varchar(10) default NULL,
`name_user_first` varchar(40) default NULL,
`name_user_mid` varchar(40) default NULL,
`name_user_lst` varchar(40) default NULL,
`cd_user_div` varchar(10) default NULL,
`cd_user_cst_cntr` varchar(15) default NULL,
`id_actnee` varchar(10) default NULL,
`name_actnee_first` varchar(40) default NULL,
`name_actnee_mid` varchar(40) default NULL,
`name_actnee_lst` varchar(40) default NULL,
`cd_pltfrm` varchar(10) default NULL,
`cd_rsrc_sub_type` varchar(10) default NULL,
`cd_actn` varchar(10) default NULL,
`cd_rsrc_div` varchar(10) default NULL,
`name_grp` varchar(70) default NULL,
`name_svr` varchar(70) default NULL,
`name_rsrc_1` varchar(70) default NULL,
`name_rsrc_2` varchar(70) default NULL,
`name_rsrc_3` varchar(70) default NULL,
`name_rsrc_4` varchar(70) default NULL,
`name_rsrc_5` varchar(70) default NULL,
`cd_sts_apprl` varchar(30) default NULL,
`cd_prcsg_type` varchar(10) default NULL,
`text_actnee_cmnts` varchar(255) default NULL,
`text_spcl_instn` varchar(255) default NULL,
`dt_lst_updt` datetime default NULL,
`id_user_lst_updt` varchar(8) default NULL
) TYPE=MyISAM

I did read your response/answer to my problem and being a newbie, I found it 
quite complicated for me to follow. Does it really require more than just 1 
or 2 simple select queries to pull out rows ( unique id_secr_rqst - 2 of 
them for each) which have the max(dt_aud_rec) and second max(dt_aud_rec)? I 
mean simple queries with joins. I cannot use sub queries.

I am assured that the table will have no more than a 1000 records and after 
my initial filtering I will have to deal with 100 records maximum. So 
performance is not a problem at all.

If SGreen's response is the only one then its ok - I will try to follow that 
- else I think it has room for simplication a bit.

Thanks,
Anoop

On 5/24/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
 
 
 
 Anoop kumar V [EMAIL PROTECTED] wrote on 05/24/2005 03:02:11 PM:
 
  Thanks Peter - you gave me some ideas...
  here is what I have so far (simplified for simplification..)
  
  select t2.dt_aud_rec, t1.id_secr_rqst from isr2_aud_log t1, isr2_aud_log 
 t2
  where t1.id_secr_rqst=t2.id_secr_rqst
  and t1.dt_aud_rec  t2.dt_aud_rec
  group by t1.id_secr_rqst
  
  but the problem is that it only returns the record related to the second 
 
  largest date for each id_secr_rqst.
  
  any suggestions how to get both the second and the largest date records 
 in 
  the same query?
  
  Thanks,
  Anoop
  
  
  On 5/23/05, Peter Normann [EMAIL PROTECTED] wrote:
   
   Anoop kumar V mailto:[EMAIL PROTECTED] wrote:
   
well - actually it might not be the last 2 days - i just want 2 of
the latest records for every task regardless of what date it is in
the table.
   
   Okay, now I think I understand what you need - and if I am correct, 
 this
   looks like one of the more exotic querys to me, but then again, I'm 
 not 
   like
   the SQL king around, but let me give it a shot:
   
   SELECT t1.id_secr_rqst, t2.name_rec_type, t1.dt_aud_rec, 
 MAX(dt_aud_rec)
   Latest, MAX(dt_aud_rec) NoSoLatest
   FROM isr2_aud_log t1, isr2_aud_log t2
   WHERE t1.id_secr_rqst = t2.id_secr_rqst
   AND t1.name_rec_type='Exception Resource'
   AND dt_aud_rec = Latest
   OR dt_aud_rec = NoSoLatest
   HAVING Latest  NoSoLatest
   GROUP BY t1.id_secr_rqst
   ORDER by t1.dt_aud_rec DESC;
   
   I am not 100% sure about the syntax, but you might get the idea.
   
   Peter Normann
   
   
   --
   MySQL General Mailing List
   For list archives: http://lists.mysql.com/mysql
   To unsubscribe: 
 http://lists.mysql.com/[EMAIL PROTECTED]
   
   
  
  
  -- 
  Thanks and best regards,
  Anoop
  
 I would solve this query by first constructing a table that contains the 
 information I need to identify the two most recent records (tasks). SINCE 
 YOU HAVE YET TO POST AN ACTUAL TABLE STRUCTURE (shame on you), I will be 
 forced to make up nearly every part of my answer. And because you want it to 
 be cross-database portable, I won't be able to use the group-wize autonumber 
 trick. However this will use a technique twice like the group-wize-maximum 
 technique (described here: 
 http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html ) 
 
 CREATE TEMPORARY TABLE tmpMaxDates ( 
 task_id varchar(10) not null, 
 task_date date not null, 
 INDEX(task_id, task_date) 
 ); 
 
 INSERT tmpMax (task_id, task_date) 
 SELECT task_ID, 

Tiger - MySQL --- any news ???

[Kevin Victor]

I would like to know if there is any existing solution for running
MySQL on Tiger. I dont see a binary version for 10.4 yet, will it be
released any time soon??

thanks
Kevin

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

Re: classic outer join problem

[mfatene]

Hi Hank,
I think your problem is to find all the files under /uploads by a command like :

cd /uploads
ls -lR | grep .doc files.txt

then load the files.txt into a temporary table TEMPtable that you create for
this issue (see http://dev.mysql.com/doc/mysql/en/load-data.html), after
truncating it.

When data is loaded, you can then delete by :

delete from Your_table where attachement not in (select attachement from
TEMPtable);
commit;

to rewrite a not in, see
http://dev.mysql.com/doc/mysql/en/rewriting-subqueries.html

AND look at use of tempfalg at the bottom of the page.



Mathias

Selon Hank [EMAIL PROTECTED]:

 I have a table of school class assignments with the following fields -
 the first four fields are the primary key:

 Year (int)
 Term (enum, Spring, Fall,Winter)
 ClassID (int)
 SectionID (int)
 Attachement (varchar 225)

 The attachment field is a pointer to a file in the OS of an uploaded
 file, in the format like this:
 /uploads/2003/Fall/330/1/conversions.doc

 When an old class item is imported into a new class item for a new
 year/term/class/section (new record), the attachment field is copied
 over, and the OS file stays where it is.. the attachment field value
 is then a pointer the to the OS file for a previous year/term (i.e.
 the OS file is not copied to the new location filesystem structure).

 I've been trying to construct a self-joining query to list all the
 attachments which are NOT referenced by some future
 year/term/class/section.   The desired query result is a list of files
 I can DELETE - i.e. files not imported or being pointed to by any
 other class_item record in a different year/term.

 Keep in mind that pointer to files in the same Year/Term (but
 different class/section) are NOT to be deleted.

 The system currently has MySQL version 4.0.1, so I can't use
 subqueries (i.e. NOT IN (...)).

 Any suggestions would be greatly appreciated. thanks.
 -Hank

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

help needed to create index

[Asha]

creating an index is necessary for fulltext search.

and when i say

create fulltext index search_index on sometable(column1,column2)  i
get an error messg like

Error: The used table type doesn't support FULLTEXT indexes



 Why doesn't InnoDb support FullText indexes?

Is there a physical
limitation in the InnoDb table structure as to why it can't
support  FullText indexes?

Can anyone help me to find solution ?

Tia,


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

Re: GCC 4.0, mysql and Opteron?

[Daniel Kasak]

Kevin Burton wrote:

 I'm curious what people here think of compiling mysql with gcc 4.0...
 Especially on Opteron.

 I've heard that the way to go with Opteron is to use gcc-3.4 but that
 its a little unstable.

 Of course it might be too early to find out if gcc 4.0 is better than
 3.4...

 Kevin

I don't know how Opteron support is in gcc-4.0, but for most other
processors, the best option by far is to use a more stable version such
as 3.4 or 3.3.

I'm running the stable branch of Gentoo on our server, and it's using
gcc-3.3.5. On our desktops, I run the unstable branch of Gentoo, and
they're using gcc-3.4.3. Unless there are some *major* issues in
previous versions of gcc, I would choose one of them over 4.0 at the
moment. 4.0 is an initial release, and a major rewrite. Many bugs will
have been introduced during the rewrite. As for performance, I've read a
number of forum posts saying that 4.0 is currently no better ( on
average ... better in some cases, worse in others ) than 3.4.3. This
will improve with further releases - there is apparently a lot of
potential - but for now I would only use 4.0 on development machines. In
fact, if you are interested in getting MySQL compiled with gcc-4.0,
perhaps you should do a Gentoo install using gcc-4.0 and pave the way
for the rest of us :)

-- 
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au

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

LOAD DATA and skip columns in text file...

[Jessica Svensson]

LOAD DATA and skip columns in text file...
What i have found out is that this is not possible in any existing version 
of mysql, correct?


I found a message from Sinisa Milivojevic @ MySQL AB dated 06/29/2000 while 
searching on google.

It said We have entered this on our TODO list one month ago.

So that would be almost exactly 5 years ago... is it really that this 
function has not been implemented during these 5 years? If so, then i guess 
it could be 5 more years before its impelemented and that i should maybe 
look for other solutions.


Thanks!

_
Nyhet! Hotmail direkt i din Mobil! http://mobile.msn.com/


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

Resetting Auto-increment

[christopher . l . hood]

Is there a better way to reset the auto_increment in a table, basically
there are several million rows in the database and the field that is
auto_increment is very large now and I don't want to exceed the limit of
the field description so I want to renumber all the rows starting at 1
again.

 

What I found online was this:

 

For those that are looking to reset the auto_increment, say on a list
that has had a few deletions and you want to renumber everything, you can
do the following.

DROP the field you are auto_incrementing.
ALTER the table to ADD the field again with the same attributes.

You will notice that all existing rows are renumbered and the next
auto_increment number will be equal to the row count plus 1.

 

 

So is there an easier / better way to do this? Also would the way this is
done be different if the table is empty?? I have an empty table that when
I add a record, starts at some number based on how many have been entered
and deleted.

 

Chris Hood 

Investigator Verizon Global Security Operations Center 

Email:  mailto:[EMAIL PROTECTED]
[EMAIL PROTECTED] 

Desk: 972.399.5900

Verizon Proprietary



NOTICE - This message and any attached files may contain information that
is confidential and/or subject of legal privilege intended only for the
use by the intended recipient.  If you are not the intended recipient or
the person responsible for delivering the message to the intended
recipient, be advised that you have received this message in error and
that any dissemination, copying or use of this message or attachment is
strictly forbidden, as is the disclosure of the information therein.  If
you have received this message in error please notify the sender
immediately and delete the message.

 

Re: Query to select...

[Dan Bolser]

On Tue, 24 May 2005 [EMAIL PROTECTED] wrote:

Selon Dan Bolser [EMAIL PROTECTED]:


 Hello,

 I have data like this

 PK   GRP_COL
 1A
 2A
 3A
 4B
 5B
 6B
 7C
 8C
 9C


 And I want to write a query to select data like this...

 PK   FK  GRP_COL
 11   A
 21   A
 31   A
 44   B
 54   B
 64   B
 77   C
 87   C
 97   C


 Where FK is a random (or otherwise) member of PK from within the
 appropriate group given by GRP_COL. FK recreates the grouping from
 GRP_COL, but in terms of PK. I want to do this because GRP_COL is
 difficult to handle and I want to re-represent the grouping in terms of PK
 (this allows me to link data into the grouping more easily).

 Is there a simple way to do this?



Sorry about the column names above (something in my head). Here is my
favorite answer...


SET @i=0, @row='';

SELECT 
  *, -- Data table
  IF(@row=GRP_COL, @i, @i:[EMAIL PROTECTED]) AS FK,
  @row:=GRP_COL  AS DROP_ME_LATER
FROM 
  data 
ORDER BY 
  GRP_COL-- Essential for the logic used
;

http://dev.mysql.com/doc/mysql/en/variables.html (John Belamaric)

Having the FK column taken from the PK column was clearly not necessary
(thanks all again for pointers).

Somehow in the distant memory of my brain this is the answer I was looking
for (and finally found). I like this answer because I hate that half of
SQL which ALTERS tables and I have a neurotic fear of UPDATES accross
JOINS that infected my nightmares as a youth!

Strange I know, but its late and time for me to sleep /(xOx)/

Pleasant dreams!

Dan.





 --
 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: Resetting Auto-increment

[Dan Bolser]

On Tue, 24 May 2005 [EMAIL PROTECTED] wrote:

Is there a better way to reset the auto_increment in a table, basically
there are several million rows in the database and the field that is
auto_increment is very large now and I don't want to exceed the limit of
the field description so I want to renumber all the rows starting at 1
again.

 

What I found online was this:

 

For those that are looking to reset the auto_increment, say on a list
that has had a few deletions and you want to renumber everything, you can
do the following.

DROP the field you are auto_incrementing.
ALTER the table to ADD the field again with the same attributes.

You will notice that all existing rows are renumbered and the next
auto_increment number will be equal to the row count plus 1.

 

 

So is there an easier / better way to do this? Also would the way this is
done be different if the table is empty?? I have an empty table that when
I add a record, starts at some number based on how many have been entered
and deleted.


In the case that the table is empty the information here

http://dev.mysql.com/doc/mysql/en/set-option.html


should let you do what you want.


I don't know the best way to 're-index' existing data. 



 

Chris Hood 

Investigator Verizon Global Security Operations Center 

Email:  mailto:[EMAIL PROTECTED]
[EMAIL PROTECTED] 

Desk: 972.399.5900

Verizon Proprietary



NOTICE - This message and any attached files may contain information that
is confidential and/or subject of legal privilege intended only for the
use by the intended recipient.  If you are not the intended recipient or
the person responsible for delivering the message to the intended
recipient, be advised that you have received this message in error and
that any dissemination, copying or use of this message or attachment is
strictly forbidden, as is the disclosure of the information therein.  If
you have received this message in error please notify the sender
immediately and delete the message.

 




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

Re: Tiger - MySQL --- any news ???

[Rich Allen]

i am running 4.1.9 binary on tiger without any trouble

Your MySQL connection id is 1 to server version: 4.1.9-standard

- hcir
On May 23, 2005, at 1:03 PM, Kevin Victor wrote:


I would like to know if there is any existing solution for running
MySQL on Tiger. I dont see a binary version for 10.4 yet, will it be
released any time soon??



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

Re: Tiger - MySQL --- any news ???

[Jim Winstead]

On Mon, May 23, 2005 at 04:03:52PM -0500, Kevin Victor wrote:
 I would like to know if there is any existing solution for running
 MySQL on Tiger. I dont see a binary version for 10.4 yet, will it be
 released any time soon??

The existing binaries for 10.3 should run fine on Tiger.

Personally, I've downloaded the 4.1.12 standard installer package,
installed it, and run the full test suite and it all passed.

Jim Winstead
MySQL Inc.

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

Sub Query to long...

[Hendro Suryawan]

Hi,
I have 8414 records in table name Barang, I run query like this :

Select BrgId, Kode, Barang From Barang Where Barang in
(Select Barang From Barang  Group By Barang Having Count(*)  1 )

and the answer took 54813 ms. I think is too long. I ran the same query 
against same table in MS SQL Server it took 1 second. Does anyone have 
any idea?


I have mysql on FC 3 X86_64 on Athlon 64, RAM 512 MB.
Table Definition :
CREATE TABLE `Barang` (
 `BrgId` int(4) NOT NULL default '0',
 `Kode` varchar(11) NOT NULL default '',
 `Barang` varchar(70) NOT NULL default '',
 `Satuan` varchar(10) default NULL,
 `Stok` decimal(10,0) default NULL,
 `Nilai` decimal(10,0) default NULL,
 `Lokal` char(1) default NULL,
 `SGrupId` int(10) NOT NULL default '0',
 `StokMin` int(10) default NULL,
 `Catatan` blob,
 `Benda` char(1) NOT NULL default '',
 `Gambar` longblob,
 `LastTrans` date default NULL,
 `Operators` varchar(50) default NULL,
 PRIMARY KEY  (`BrgId`),
 UNIQUE KEY `Kode` (`Kode`),
 KEY `Barang` (`Barang`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


thanks,
Hendro


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

Re: Sub Query to long...

[mfatene]

Hi,
You may have the same table structure in MS, but not the same table definiton :
constraints+indexes+stats !

try :
create index toto on Barang(BrgId, Kode, Barang);
Select BrgId, Kode, Barang From Barang
  Group By Barang
  Having Count(*)  1 ;

Mathias

Selon Hendro Suryawan [EMAIL PROTECTED]:

 Hi,
 I have 8414 records in table name Barang, I run query like this :

 Select BrgId, Kode, Barang From Barang Where Barang in
 (Select Barang From Barang  Group By Barang Having Count(*)  1 )

  and the answer took 54813 ms. I think is too long. I ran the same query
 against same table in MS SQL Server it took 1 second. Does anyone have
 any idea?

 I have mysql on FC 3 X86_64 on Athlon 64, RAM 512 MB.
 Table Definition :
 CREATE TABLE `Barang` (
   `BrgId` int(4) NOT NULL default '0',
   `Kode` varchar(11) NOT NULL default '',
   `Barang` varchar(70) NOT NULL default '',
   `Satuan` varchar(10) default NULL,
   `Stok` decimal(10,0) default NULL,
   `Nilai` decimal(10,0) default NULL,
   `Lokal` char(1) default NULL,
   `SGrupId` int(10) NOT NULL default '0',
   `StokMin` int(10) default NULL,
   `Catatan` blob,
   `Benda` char(1) NOT NULL default '',
   `Gambar` longblob,
   `LastTrans` date default NULL,
   `Operators` varchar(50) default NULL,
   PRIMARY KEY  (`BrgId`),
   UNIQUE KEY `Kode` (`Kode`),
   KEY `Barang` (`Barang`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;


 thanks,
 Hendro


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