Re: PRINT statement?

[Peter Brawley]

Stephen Cook wrote:
I appreciate it but SELECT isn't quite what I want.  It adds an extra 
4 to 6 lines to the output (drawing the table, headers, row counts, 
etc).  PRINT simply outputs whatever comes after it:
PRINT is not a SQL command. The mysql client (fortunately) does not 
speak Basic.


To minimise output in the mysql client, have a look at the -s --silent 
option.


PB

-



PRINT 'hey you!'

would show:
hey you!



Not a big deal I suppose but it makes for a lot more scrolling around.

I've started just dumping the comments (i.e. '') into a table with a 
timestamp, so I can review it afterwards. Its a close second.



Rhino wrote:

Thanks, Quentin, for the documentation.

Assuming that the Transact-SQL Help file is using various terms in 
the same way as MySQL does, particularly "string expression" and 
"function", I think we will find that the SQL SELECT will do all of 
the things that Stephen has come to expect from the PRINT statement 
in MS SQL Server.


I've just put together an SQL Script that I think demonstrates that 
SELECT can do mostl of the same things as the PRINT statement.


Here is the script, which works perfectly in MySQL 4.0.15:

=
select "=== S C R I P T   B E G I N S ===" as "";

select "CONNECT TO DATABASE" as "Action";
use tmp;

select "DROP/CREATE TABLE" as "Action";
drop table if exists users;
create table if not exists users
(user_id smallint not null,
user_fname char(20) not null,
user_lname char(20) not null,
user_birthdate date not null,
user_education_years int not null,
primary key(user_id));

select "POPULATE TABLE AND DISPLAY CONTENTS" as "Action";
insert into users values
(1, 'Alan', 'Adams', '1970-04-08', 15),
(2, 'Bill', 'Baker', '1964-02-01', 18),
(3, 'Cass', 'Cooke', '1981-12-04', 12),
(4, 'Dina', 'Davis', '1944-06-06', 19),
(5, 'Earl', 'Edger', '1990-08-02', 17);
select * from users;

select "SET AND DISPLAY SCRIPT VARIABLES" as "Action";
set @minimum_education_years = 16;
set @birthdate_of_youngest_legal_worker = date_sub(curdate(), 
interval 16 year);


select "   " as "Variable",
"" as "Value"
UNION
select "minimum_education_years=", @minimum_education_years
UNION
select "birthdate_of_youngest_legal_worker=",
@birthdate_of_youngest_legal_worker;

select "   " as "Variable",
"" as "Value"
UNION
select "minimum_education_years=", @minimum_education_years
UNION
select "birthdate_of_youngest_legal_worker=",
@birthdate_of_youngest_legal_worker;

select "EXECUTE QUERIES THAT USE SCRIPT VARIABLES" as "Action";
select concat("Get users who have more than ", @minimum_education_years,
" years of education") as "Query";
select * from users
where user_education_years >= @minimum_education_years;
select concat("Get users who are old enough to work, i.e. were born 
before ",

@birthdate_of_youngest_legal_worker) as "Query";
select * from users
where user_birthdate <= @legal_to_work;


select "DISPLAY FUNCTION RESULTS" as "Action";
select " " as "Function", " " 
as "Value"

UNION
select "curdate()=", curdate()
UNION
select "now()=", now()
UNION
select "Firstname+Lastname=", concat(user_fname, ' ', user_lname)
from users where user_id = 1;

select "=== S C R I P T   E N D S ===" as "";

=

and this is the output of the script:

=
+---+
|   |
+---+
| === S C R I P T   B E G I N S === |
+---+
1 row in set (0.00 sec)

+-+
| Action  |
+-+
| CONNECT TO DATABASE |
+-+
1 row in set (0.00 sec)

Database changed
+---+
| Action|
+---+
| DROP/CREATE TABLE |
+---+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+-+
| Action  |
+-+
| POPULATE TABLE AND DISPLAY CONTENTS |
+-+
1 row in set (0.00 sec)

Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

+-++++--+ 

| user_id | user_fname | user_lname | user_birthdate | 
user_education_years |
+-++++--+ 

|   1 | Alan   | Adams  | 1970-04-08 
|   15 |
|   2 | Bill   | Baker  | 1964-02-01 
|   18 |
|   3 | Cass   | Cooke  | 1981-12-04 
|   12 |
|   4 | Dina   | Davis  | 1944-06-06 
|   19 |
|   

MYIsam vs. BDB

[sol beach]

In a high activity OLTP environment with "heavy" UPDATE activity, which
table type is better?
I have one table which has many readers & a decent amount of UPDATE
activity.
Will BDB or MYIsam result in better performance & by how much better (please
quantify?

Re: PRINT statement?

[Stephen Cook]

I appreciate it but SELECT isn't quite what I want.  It adds an extra 4 
to 6 lines to the output (drawing the table, headers, row counts, etc). 
 PRINT simply outputs whatever comes after it:


PRINT 'hey you!'

would show:
hey you!



Not a big deal I suppose but it makes for a lot more scrolling around.

I've started just dumping the comments (i.e. '') into a table with a 
timestamp, so I can review it afterwards. Its a close second.



Rhino wrote:

Thanks, Quentin, for the documentation.

Assuming that the Transact-SQL Help file is using various terms in the 
same way as MySQL does, particularly "string expression" and "function", 
I think we will find that the SQL SELECT will do all of the things that 
Stephen has come to expect from the PRINT statement in MS SQL Server.


I've just put together an SQL Script that I think demonstrates that 
SELECT can do mostl of the same things as the PRINT statement.


Here is the script, which works perfectly in MySQL 4.0.15:

=
select "=== S C R I P T   B E G I N S ===" as "";

select "CONNECT TO DATABASE" as "Action";
use tmp;

select "DROP/CREATE TABLE" as "Action";
drop table if exists users;
create table if not exists users
(user_id smallint not null,
user_fname char(20) not null,
user_lname char(20) not null,
user_birthdate date not null,
user_education_years int not null,
primary key(user_id));

select "POPULATE TABLE AND DISPLAY CONTENTS" as "Action";
insert into users values
(1, 'Alan', 'Adams', '1970-04-08', 15),
(2, 'Bill', 'Baker', '1964-02-01', 18),
(3, 'Cass', 'Cooke', '1981-12-04', 12),
(4, 'Dina', 'Davis', '1944-06-06', 19),
(5, 'Earl', 'Edger', '1990-08-02', 17);
select * from users;

select "SET AND DISPLAY SCRIPT VARIABLES" as "Action";
set @minimum_education_years = 16;
set @birthdate_of_youngest_legal_worker = date_sub(curdate(), interval 
16 year);


select "   " as "Variable",
"" as "Value"
UNION
select "minimum_education_years=", @minimum_education_years
UNION
select "birthdate_of_youngest_legal_worker=",
@birthdate_of_youngest_legal_worker;

select "   " as "Variable",
"" as "Value"
UNION
select "minimum_education_years=", @minimum_education_years
UNION
select "birthdate_of_youngest_legal_worker=",
@birthdate_of_youngest_legal_worker;

select "EXECUTE QUERIES THAT USE SCRIPT VARIABLES" as "Action";
select concat("Get users who have more than ", @minimum_education_years,
" years of education") as "Query";
select * from users
where user_education_years >= @minimum_education_years;
select concat("Get users who are old enough to work, i.e. were born 
before ",

@birthdate_of_youngest_legal_worker) as "Query";
select * from users
where user_birthdate <= @legal_to_work;


select "DISPLAY FUNCTION RESULTS" as "Action";
select " " as "Function", " " as 
"Value"

UNION
select "curdate()=", curdate()
UNION
select "now()=", now()
UNION
select "Firstname+Lastname=", concat(user_fname, ' ', user_lname)
from users where user_id = 1;

select "=== S C R I P T   E N D S ===" as "";

=

and this is the output of the script:

=
+---+
|   |
+---+
| === S C R I P T   B E G I N S === |
+---+
1 row in set (0.00 sec)

+-+
| Action  |
+-+
| CONNECT TO DATABASE |
+-+
1 row in set (0.00 sec)

Database changed
+---+
| Action|
+---+
| DROP/CREATE TABLE |
+---+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+-+
| Action  |
+-+
| POPULATE TABLE AND DISPLAY CONTENTS |
+-+
1 row in set (0.00 sec)

Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

+-++++--+ 

| user_id | user_fname | user_lname | user_birthdate | 
user_education_years |
+-++++--+ 

|   1 | Alan   | Adams  | 1970-04-08 |   
15 |
|   2 | Bill   | Baker  | 1964-02-01 |   
18 |
|   3 | Cass   | Cooke  | 1981-12-04 |   
12 |
|   4 | Dina   | Davis  | 1944-06-06 |   
19 |
|   5 | Earl   | Edger  | 1990-08-02 |   
17 |
+-++++--+ 


5 rows in set (0.00 sec)

+

Re: GROUP BY *column* when *column* is NOT in SELECT list?

[Paul DuBois]

At 18:28 -0400 5/11/06, Fan, Wellington wrote:

Hello all,

I have inherited this query:

SELECT
events.eventID AS id,
attribute_master.attributeID AS attrib_id
FROM
events,
attribute_master
WHERE
events.status='8' AND
events.eventReview!='' AND
events.modlast > 1146470445 AND
events.eventID = attribute_master.id AND
attribute_master.tableis = 'events'
GROUP BY
attribute_master.id

I thought that every column listed in the SELECT clause would have 
to be in an aggregate function or mentioned in the GROUP BY...how am 
I wrong?


It's a MySQL extension to GROUP BY:

http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html

--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

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

GROUP BY *column* when *column* is NOT in SELECT list?

[Fan, Wellington]

Hello all,

I have inherited this query:

SELECT
events.eventID AS id,
attribute_master.attributeID AS attrib_id
FROM
events,
attribute_master
WHERE
events.status='8' AND
events.eventReview!='' AND
events.modlast > 1146470445 AND
events.eventID = attribute_master.id AND
attribute_master.tableis = 'events'
GROUP BY
attribute_master.id


I thought that every column listed in the SELECT clause would have to be in an 
aggregate function or mentioned in the GROUP BY...how am I wrong?

--
Wellington

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

Re: One to many join with only one result

[Dan Buettner]

Scott, is it as easy as SELECT DISTINCT?

SELECT DISTINCT u.username
FROM user u, transactions t
WHERE u.userid = t.userid
AND t.status = 'wells'

Dan


Scott Haneda wrote:

Mysql 4.x

I have a one to many case, key is `id`, in this case, I have tables users
and transactions.

A user can have 1 to x transactions, each transaction has a status to it as
well.  I want to be able to get users where there is a transaction status of
'wells', however, there can be more than one of those transactions, all I
care is that it exists and I just want one unique user record back.

Ie: if I have 100 users, and each user has 2 transactions of type 'wells', I
want to select those, but a regular join will send me back 200 records,
where I want only 100

Can someone point me in the direction of the correct join to make this
happen.


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

Re: One to many join with only one result

[Jo�o C�ndido de Souza Neto]

Put your select here to us. With it we can help you better.

"Scott Haneda" <[EMAIL PROTECTED]> escreveu na mensagem 
news:[EMAIL PROTECTED]
> Mysql 4.x
>
> I have a one to many case, key is `id`, in this case, I have tables users
> and transactions.
>
> A user can have 1 to x transactions, each transaction has a status to it 
> as
> well.  I want to be able to get users where there is a transaction status 
> of
> 'wells', however, there can be more than one of those transactions, all I
> care is that it exists and I just want one unique user record back.
>
> Ie: if I have 100 users, and each user has 2 transactions of type 'wells', 
> I
> want to select those, but a regular join will send me back 200 records,
> where I want only 100
>
> Can someone point me in the direction of the correct join to make this
> happen.
> -- 
> -
> Scott HanedaTel: 415.898.2602
>  Novato, CA U.S.A.
>
> 



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

One to many join with only one result

[Scott Haneda]

Mysql 4.x

I have a one to many case, key is `id`, in this case, I have tables users
and transactions.

A user can have 1 to x transactions, each transaction has a status to it as
well.  I want to be able to get users where there is a transaction status of
'wells', however, there can be more than one of those transactions, all I
care is that it exists and I just want one unique user record back.

Ie: if I have 100 users, and each user has 2 transactions of type 'wells', I
want to select those, but a regular join will send me back 200 records,
where I want only 100

Can someone point me in the direction of the correct join to make this
happen.
-- 
-
Scott HanedaTel: 415.898.2602
 Novato, CA U.S.A.



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

RE: ORDER BY making recordset non-updatable

[Eland, Travis M.]

Thanks for the response!  Unfortunately, it is that simple.
 
A basic "Select * from vwMyView" yields an updatable recordset.
 
Adding "Order by Name" to the end does not allow an update.
 
I should mention that the error associated with the lack of update is:
"Insufficient Key Column Information for Updating or Refreshing"
 
I have since futhered my troubleshooting and determined that I actually AM able 
to update the recordset when the order by is applied in some situations.  
Apparently, I can order by any field that is in the view's main table (the 
table that all of the other tables left join off of) and still be able to 
update.  It is when I order by a field that is not from this main table that I 
get the above error and inability to update.
 
I am still at a loss as to how to fix this so that I can order by any field I 
wish.
 
Any input is greatly appreciated.
 
Thanks,
Travis Eland
 
 



From: sheeri kritzer [mailto:[EMAIL PROTECTED]
Sent: Thu 5/4/2006 4:15 PM
To: Eland, Travis M.
Cc: mysql@lists.mysql.com
Subject: Re: ORDER BY making recordset non-updatable



Maybe I'm thick

You have a view, called vwMyView.

You SELECT rows from it, and you're able to update the view?

Yet when you SELECT with an ORDER BY clause, you're not allowed to
update the view?

I just do not understand how a read statement affects DML.  I think
you're going to have to post the query you're using, as it's more
complex than a SELECT.  Perhaps you're using a REPLACE INTO 
SELECT statement?  Or UPDATE  WHERE  IN (SELECT)?

-Sheeri

On 4/19/06, Eland, Travis M. <[EMAIL PROTECTED]> wrote:
> Heya.
>
> I am in the process of modifying a program to access data from a MySQL 
> database instead of a SQL Server database.  I have a view that is referenced 
> as follows (through use of a data environment command):
>
> Select * from vwMyView where id = ?
>
> If I run this command, I get the data that I would expect, and I am able to 
> update the data that I would expect to update (there are a few joins in the 
> view so there are a couple fields that I understand that I cannot update).
>
> My problem is, if I add an ORDER BY statement at the end of this command, the 
> recordset still returns data, but it becomes non-updatable.
>
> I would include my SQL, but unfortunately it is on a classified machine.  I 
> have verified the SQL numerous times and it works fine in every way except 
> when I use ORDER BY.  The SQL structure (though slightly modified for mySQL) 
> also worked fine in SQL Server.
>
> Is this a known issue?  Is there something that I could possibly be missing?
>
> I apologize for the lack of actual code, but I appreciate any insight!
>
> Thanks!
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>



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

MySQL 5.0 strange table creation 'func'

[Amer Neely]

I've just noticed in one of my databases a table named 'func', which I'm 
positive I never created.


It was empty and has 4 columns:
mysql> describe func;
+---+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-+---+
| name  | char(64) | NO   | PRI | NULL|   |
| ret   | tinyint(1)   | NO   | | 0   |   |
| dl| char(128)| NO   | | NULL|   |
| type  | enum('function','aggregate') | NO   | | NULL|   |
+---+--+--+-+-+---+
4 rows in set (0.01 sec)

A search for 'func' in the 5.0 docs reveals there apparently is a system 
table in the mysql database called 'func', but I can't find anything 
about why it would be created in a client database.


It does appear in my mysql database as well, but no others. Anyone got 
an idea where it's coming from? And can I delete it from the client 
database?


I suspect this may have something to do with my recent upgrade to 5.0 
but I don't see the purpose, especially as it only appears in one of my 
client databases.


--
Amer Neely
Home of Spam Catcher
W: www.softouch.on.ca
E: [EMAIL PROTECTED]
Perl | MySQL | CGI programming for all data entry forms.
"We make web sites work!"

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

[SOLVED] Re: #1191 - Can't find FULLTEXT index matching the column list

[afan]

Yup. The problem solved!

Thanks John!

;)

-afan


> [EMAIL PROTECTED] wrote:
>> Ok. Got it.
>> And, since the able is already created and with tons of products inside,
>> how can I change the FULTEXT index?
>> I'm using phpMyAdmin.
>
> Just create a new index.
> http://dev.mysql.com/doc/refman/4.1/en/create-index.html
>
> --J
>
>>> [EMAIL PROTECTED] wrote:
 Hi to all!

 I have "products" table:

 CREATE TABLE `products` (
   `prod_id` int(8) unsigned NOT NULL auto_increment,
   `prod_no` varchar(50) NOT NULL default '',
   `prod_name` varchar(255) NOT NULL default '',
   `prod_description` text,
   `prod_colors` text,
   `prod_includes` text,
   `prod_catalog` varchar(45) default NULL,
   `prod_status` enum('hidden','live','new') NOT NULL default 'new',
   `prod_supplier` varchar(45) default NULL,
   `prod_start_date` date default '-00-00',
   `prod_end_date` date default '-00-00',
   `prod_featured` enum('0','1') default NULL,
   `on_sale` enum('Yes','No') NOT NULL default 'No',
   PRIMARY KEY  (`prod_id`),
   UNIQUE KEY `prod_no` (`prod_no`),
   KEY `products_index1` (`prod_status`),
   KEY `products_index2` (`prod_start_date`,`prod_end_date`),
   KEY `on_sale` (`on_sale`),
   FULLTEXT KEY `prod_name` (`prod_name`),
   FULLTEXT KEY `prod_description` (`prod_description`)
 ) TYPE=MyISAM AUTO_INCREMENT=3367 ;

 When I tried this:
 SELECT * FROM products
 WHERE match (prod_name) against ('+red +shirt');
 I'll get some results.
 But, when I tried this:
 SELECT * FROM products
 WHERE match (prod_name, prod_description) against ('+red +shirt');
 I got this error message:
 #1191 - Can't find FULLTEXT index matching the column list

 What am I doing wrong?
>>> You need a single FULLTEXT index that contains both of the columns
>>> you're searching on.
>>>
>>>  From http://dev.mysql.com/doc/refman/4.1/en/fulltext-search.html :
>>>
>>> For natural-language full-text searches, it is a requirement that the
>>> columns named in the MATCH() function be the same columns included in
>>> some FULLTEXT  index in your table. For the preceding query, note that
>>> the columns named in the MATCH() function (title and body) are the same
>>> as those named in the definition of the article table's FULLTEXT
>>> index.
>>> If you wanted to search the title or body separately, you would need to
>>> create separate FULLTEXT indexes for each column.
>>>
>>> --J
>>>
>>
>>
>
>
> --
> 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]

Explain explanation (was: More LEFT JOIN newbie fun!)

[Chris Sansom]

Following my post about this complex search I'm trying to do...

In the initial post I said I'd tried adding:

   left join table_ga as tga on tga.id = r.id
   left join table_a as ta on ta.ida = tga.ida

or:

   left join (table_ga as tga inner join table_a as ta) on
  (tga.id = r.id and ta.ida = tga.ida)

to my query, and that both got the same results. Having added two 
pairs and tried both versions, I find the second one is fractionally 
(but I mean /really/ fractionally) faster, as reported by phpMyAdmin. 
However, when I do an Explain on both versions, the first shows 
values of 375, 17, 3 and a bunch of 1s in the rows column, whereas 
the second shows 375, 34, 6 and the same lot of 1s. All other details 
are identical.


As I understand it in my naive, newbie way, as a rough rule of thumb 
you can compare the speeds of queries by multiplying together the 
rows values... in which case the search that was fractionally slower 
should have been four times as fast. What's going on here?


--
Cheers... Chris
Highway 57 Web Development -- http://highway57.co.uk/

I'm on a seafood diet - I see food, I eat it.
   -- Dolly Parton

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

Re: More LEFT JOIN newbie fun!

[Chris Sansom]

At 16:09 +0100 11/5/06, I wrote:
Not long ago, some highly knowledgeable people here kindly helped me 
out with a fairly complex query...


...

That's all fine and dandy, but now I need to extend this to a 
further four tables...


What I should have added is that for the moment this has to be 
possible in MySQL 3.23, so I can't do nice things like match ... 
against ...


--
Cheers... Chris
Highway 57 Web Development -- http://highway57.co.uk/

A censor is a man who knows more than he thinks you ought to.
   -- Laurence J. Peter

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

More LEFT JOIN newbie fun!

[Chris Sansom]

Not long ago, some highly knowledgeable people here kindly helped me 
out with a fairly complex query...


Finding names of people (and other info) where one or more fields 
match the search string in up to five tables (abstracting somewhat):




select distinct
   id, firstname, lastname, etc...
from
   master_info as r
   inner join general_info as g
   left join table_1 as t1 on t1.id = r.id
   left join table_2 as t2 on t2.id = r.id
   left join table_3 as t3 on t3.id = r.id
   left join table_4 as t4 on t4.id = r.id
   left join table_5 as t5 on t5.id = r.id

where
   g.id = r.id and
   (t1.blurb like '%searchterm%' or t2.blurb like '%searchterm%' or
  t3.blurb like '%searchterm%' or t4.blurb like '%searchterm%' or
  t5.blurb like '%searchterm%')



That's all fine and dandy, but now I need to extend this to a further 
four tables... except it's really eight tables in four pairs. I'll 
call these table_a and table_ga .. table_d and table_gd.


So far, I can get it to work if I add just one pair, in either of two ways:

   left join table_ga as tga on tga.id = r.id
   left join table_a as ta on ta.ida = tga.ida

or:

   left join (table_ga as tga inner join table_a as ta) on
  (tga.id = r.id and ta.ida = tga.ida)

in each case adding:

   or ta.blurb like '%searchterm%'

to the where clause. As you'll realise this is because the text has 
to match the blurb column in ta, which is in turn identified by its 
own id which has to be matched in tga, which is simply two columns of 
ids (one of people, one of blurbs).


That does, as I say, work, but it does slow things down pretty 
drastically - from less than half a second to about four seconds 
(whichever of the two methods I use). And when I add a second pair 
(table_b and table_gb) it's nearly a minute, so obviously this is 
going to multiply up very nastily if I add the other two pairs.


I've now added full text indices to the blurb columns in table_a and 
table_b and that's speeded things up a lot - about 7.5 seconds now. 
However, in this instance there are matches in both table_a and 
table_b (as well as in some of the 1..5 tables). When I add the 
remaining two pairs in - where I know there are no matches - well, 
it's still running after several minutes, and that's after full text 
indexing those tables too.


Obviously, there's a better way of doing this - any ideas?

(And I'm now cancelling the last query which still hasn't finished!)

--
Cheers... Chris
Highway 57 Web Development -- http://highway57.co.uk/

Without music to decorate it, time is just a bunch of boring
production deadlines or dates by which bills must be paid.
   -- Frank Zappa

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

Re: #1191 - Can't find FULLTEXT index matching the column list

[John Hicks]

[EMAIL PROTECTED] wrote:

Ok. Got it.
And, since the able is already created and with tons of products inside,
how can I change the FULTEXT index?
I'm using phpMyAdmin.


Just create a new index. 
http://dev.mysql.com/doc/refman/4.1/en/create-index.html


--J


[EMAIL PROTECTED] wrote:

Hi to all!

I have "products" table:

CREATE TABLE `products` (
  `prod_id` int(8) unsigned NOT NULL auto_increment,
  `prod_no` varchar(50) NOT NULL default '',
  `prod_name` varchar(255) NOT NULL default '',
  `prod_description` text,
  `prod_colors` text,
  `prod_includes` text,
  `prod_catalog` varchar(45) default NULL,
  `prod_status` enum('hidden','live','new') NOT NULL default 'new',
  `prod_supplier` varchar(45) default NULL,
  `prod_start_date` date default '-00-00',
  `prod_end_date` date default '-00-00',
  `prod_featured` enum('0','1') default NULL,
  `on_sale` enum('Yes','No') NOT NULL default 'No',
  PRIMARY KEY  (`prod_id`),
  UNIQUE KEY `prod_no` (`prod_no`),
  KEY `products_index1` (`prod_status`),
  KEY `products_index2` (`prod_start_date`,`prod_end_date`),
  KEY `on_sale` (`on_sale`),
  FULLTEXT KEY `prod_name` (`prod_name`),
  FULLTEXT KEY `prod_description` (`prod_description`)
) TYPE=MyISAM AUTO_INCREMENT=3367 ;

When I tried this:
SELECT * FROM products
WHERE match (prod_name) against ('+red +shirt');
I'll get some results.
But, when I tried this:
SELECT * FROM products
WHERE match (prod_name, prod_description) against ('+red +shirt');
I got this error message:
#1191 - Can't find FULLTEXT index matching the column list

What am I doing wrong?

You need a single FULLTEXT index that contains both of the columns
you're searching on.

 From http://dev.mysql.com/doc/refman/4.1/en/fulltext-search.html :

For natural-language full-text searches, it is a requirement that the
columns named in the MATCH() function be the same columns included in
some FULLTEXT  index in your table. For the preceding query, note that
the columns named in the MATCH() function (title and body) are the same
as those named in the definition of the article table's FULLTEXT  index.
If you wanted to search the title or body separately, you would need to
create separate FULLTEXT indexes for each column.

--J







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

GROUP BY question

[Fan, Wellington]

<>

Hello all,

I just inherited an application that has 2 tables under consideration, "events" 
and "attribute_master". They are linked on (events.eventID = 
attribute_master.id AND attribute_master.tableis = 'events'). In other words, 
attribute_master.id is kinda like a foreign key to events.eventID, but only 
where attribute_master.tableis = 'events'.

I have ommited some columns from 'events' that I am fairly certain are NOT 
germane to my question:

CREATE TABLE attribute_master (
  tableis varchar(128) NOT NULL default '',
  id int(12) NOT NULL default '0',
  attributeID int(8) NOT NULL default '0',
  PRIMARY KEY  (tableis,id,attributeID)
) TYPE=MyISAM;

CREATE TABLE events (
  eventID mediumint(9) NOT NULL auto_increment,
  eventReview text NOT NULL,
  status tinyint(1) NOT NULL default '0',
  modlast int(8) NOT NULL default '0',
  PRIMARY KEY  (eventID)
) TYPE=MyISAM


 Now, an intersting query:
SELECT
events.eventID AS id,
attribute_master.attributeID AS attrib_id
FROM
events,
attribute_master
WHERE
events.status='8' AND
events.eventReview!='' AND
events.modlast > 1146470445 AND
events.eventID = attribute_master.id AND
attribute_master.tableis = 'events'
GROUP BY
attribute_master.id

So here's my question(s): Given this query, how can on GROUP BY operate on a 
column that doesn't appear in the SELECT list? Secondly, I thought that every 
column listed in the SELECT clause would have to be in an aggregate function or 
mentioned in the GROUP BY...how am I wrong?

--
Wellington

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

RE: #1191 - Can't find FULLTEXT index matching the column list

[afan]

Dude! You just confused me with YOUR QUESTION under  MY SUBJECT LINE
:) :) :)


You have to change a Subject and send it again. Or we will not know to
whom is sent.
:)

-afan


> Hello all,
>
> I just inherited an application that has 2 tables under consideration,
> "events" and "attribute_master". They are linked on (events.eventID =
> attribute_master.id AND attribute_master.tableis = 'events'). In other
> words, attribute_master.id is kinda like a foreign key to events.eventID,
> but only where attribute_master.tableis = 'events'.
>
> I have ommited some columns from 'events' that I am fairly certain are NOT
> germane to my question:
>
> CREATE TABLE attribute_master (
>   tableis varchar(128) NOT NULL default '',
>   id int(12) NOT NULL default '0',
>   attributeID int(8) NOT NULL default '0',
>   PRIMARY KEY  (tableis,id,attributeID)
> ) TYPE=MyISAM;
>
> CREATE TABLE events (
>   eventID mediumint(9) NOT NULL auto_increment,
>   eventReview text NOT NULL,
>   status tinyint(1) NOT NULL default '0',
>   modlast int(8) NOT NULL default '0',
>   PRIMARY KEY  (eventID)
> ) TYPE=MyISAM
>
>
>  Now, an intersting query:
> SELECT
>   events.eventID AS id,
>   attribute_master.attributeID AS attrib_id
> FROM
>   events,
>   attribute_master
> WHERE
>   events.status='8' AND
>   events.eventReview!='' AND
>   events.modlast > 1146470445 AND
>   events.eventID = attribute_master.id AND
>   attribute_master.tableis = 'events'
> GROUP BY
>   attribute_master.id
>
> So here's my question(s): Given this query, how can on GROUP BY operate on
> a column that doesn't appear in the SELECT list? Secondly, I thought that
> every column listed in the SELECT clause would have to be in an aggregate
> function or mentioned in the GROUP BY...how am I wrong?
>
> --
> Wellington
>
> --
> 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: #1191 - Can't find FULLTEXT index matching the column list

[Fan, Wellington]

Damn, I really didn't mean to use that subject line; Sorry all!

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

RE: #1191 - Can't find FULLTEXT index matching the column list

[Fan, Wellington]

Hello all,

I just inherited an application that has 2 tables under consideration, "events" 
and "attribute_master". They are linked on (events.eventID = 
attribute_master.id AND attribute_master.tableis = 'events'). In other words, 
attribute_master.id is kinda like a foreign key to events.eventID, but only 
where attribute_master.tableis = 'events'.

I have ommited some columns from 'events' that I am fairly certain are NOT 
germane to my question:

CREATE TABLE attribute_master (
  tableis varchar(128) NOT NULL default '',
  id int(12) NOT NULL default '0',
  attributeID int(8) NOT NULL default '0',
  PRIMARY KEY  (tableis,id,attributeID)
) TYPE=MyISAM;

CREATE TABLE events (
  eventID mediumint(9) NOT NULL auto_increment,
  eventReview text NOT NULL,
  status tinyint(1) NOT NULL default '0',
  modlast int(8) NOT NULL default '0',
  PRIMARY KEY  (eventID)
) TYPE=MyISAM


 Now, an intersting query:
SELECT
events.eventID AS id,
attribute_master.attributeID AS attrib_id
FROM
events,
attribute_master
WHERE
events.status='8' AND
events.eventReview!='' AND
events.modlast > 1146470445 AND
events.eventID = attribute_master.id AND
attribute_master.tableis = 'events'
GROUP BY
attribute_master.id

So here's my question(s): Given this query, how can on GROUP BY operate on a 
column that doesn't appear in the SELECT list? Secondly, I thought that every 
column listed in the SELECT clause would have to be in an aggregate function or 
mentioned in the GROUP BY...how am I wrong?

--
Wellington

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

Re: #1191 - Can't find FULLTEXT index matching the column list

[afan]

Ok. Got it.
And, since the able is already created and with tons of products inside,
how can I change the FULTEXT index?
I'm using phpMyAdmin.

Thanks



> [EMAIL PROTECTED] wrote:
>> Hi to all!
>>
>> I have "products" table:
>>
>> CREATE TABLE `products` (
>>   `prod_id` int(8) unsigned NOT NULL auto_increment,
>>   `prod_no` varchar(50) NOT NULL default '',
>>   `prod_name` varchar(255) NOT NULL default '',
>>   `prod_description` text,
>>   `prod_colors` text,
>>   `prod_includes` text,
>>   `prod_catalog` varchar(45) default NULL,
>>   `prod_status` enum('hidden','live','new') NOT NULL default 'new',
>>   `prod_supplier` varchar(45) default NULL,
>>   `prod_start_date` date default '-00-00',
>>   `prod_end_date` date default '-00-00',
>>   `prod_featured` enum('0','1') default NULL,
>>   `on_sale` enum('Yes','No') NOT NULL default 'No',
>>   PRIMARY KEY  (`prod_id`),
>>   UNIQUE KEY `prod_no` (`prod_no`),
>>   KEY `products_index1` (`prod_status`),
>>   KEY `products_index2` (`prod_start_date`,`prod_end_date`),
>>   KEY `on_sale` (`on_sale`),
>>   FULLTEXT KEY `prod_name` (`prod_name`),
>>   FULLTEXT KEY `prod_description` (`prod_description`)
>> ) TYPE=MyISAM AUTO_INCREMENT=3367 ;
>>
>> When I tried this:
>> SELECT * FROM products
>> WHERE match (prod_name) against ('+red +shirt');
>> I'll get some results.
>> But, when I tried this:
>> SELECT * FROM products
>> WHERE match (prod_name, prod_description) against ('+red +shirt');
>> I got this error message:
>> #1191 - Can't find FULLTEXT index matching the column list
>>
>> What am I doing wrong?
>
> You need a single FULLTEXT index that contains both of the columns
> you're searching on.
>
>  From http://dev.mysql.com/doc/refman/4.1/en/fulltext-search.html :
>
> For natural-language full-text searches, it is a requirement that the
> columns named in the MATCH() function be the same columns included in
> some FULLTEXT  index in your table. For the preceding query, note that
> the columns named in the MATCH() function (title and body) are the same
> as those named in the definition of the article table's FULLTEXT  index.
> If you wanted to search the title or body separately, you would need to
> create separate FULLTEXT indexes for each column.
>
> --J
>


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

Re: #1191 - Can't find FULLTEXT index matching the column list

[John Hicks]

[EMAIL PROTECTED] wrote:

Hi to all!

I have "products" table:

CREATE TABLE `products` (
  `prod_id` int(8) unsigned NOT NULL auto_increment,
  `prod_no` varchar(50) NOT NULL default '',
  `prod_name` varchar(255) NOT NULL default '',
  `prod_description` text,
  `prod_colors` text,
  `prod_includes` text,
  `prod_catalog` varchar(45) default NULL,
  `prod_status` enum('hidden','live','new') NOT NULL default 'new',
  `prod_supplier` varchar(45) default NULL,
  `prod_start_date` date default '-00-00',
  `prod_end_date` date default '-00-00',
  `prod_featured` enum('0','1') default NULL,
  `on_sale` enum('Yes','No') NOT NULL default 'No',
  PRIMARY KEY  (`prod_id`),
  UNIQUE KEY `prod_no` (`prod_no`),
  KEY `products_index1` (`prod_status`),
  KEY `products_index2` (`prod_start_date`,`prod_end_date`),
  KEY `on_sale` (`on_sale`),
  FULLTEXT KEY `prod_name` (`prod_name`),
  FULLTEXT KEY `prod_description` (`prod_description`)
) TYPE=MyISAM AUTO_INCREMENT=3367 ;

When I tried this:
SELECT * FROM products
WHERE match (prod_name) against ('+red +shirt');
I'll get some results.
But, when I tried this:
SELECT * FROM products
WHERE match (prod_name, prod_description) against ('+red +shirt');
I got this error message:
#1191 - Can't find FULLTEXT index matching the column list

What am I doing wrong?


You need a single FULLTEXT index that contains both of the columns 
you're searching on.


From http://dev.mysql.com/doc/refman/4.1/en/fulltext-search.html :

For natural-language full-text searches, it is a requirement that the 
columns named in the MATCH() function be the same columns included in 
some FULLTEXT  index in your table. For the preceding query, note that 
the columns named in the MATCH() function (title and body) are the same 
as those named in the definition of the article table's FULLTEXT  index. 
If you wanted to search the title or body separately, you would need to 
create separate FULLTEXT indexes for each column.


--J

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

Re: PRINT statement?

[Rhino]

Thanks, Quentin, for the documentation.

Assuming that the Transact-SQL Help file is using various terms in the same 
way as MySQL does, particularly "string expression" and "function", I think 
we will find that the SQL SELECT will do all of the things that Stephen has 
come to expect from the PRINT statement in MS SQL Server.


I've just put together an SQL Script that I think demonstrates that SELECT 
can do mostl of the same things as the PRINT statement.


Here is the script, which works perfectly in MySQL 4.0.15:

=
select "=== S C R I P T   B E G I N S ===" as "";

select "CONNECT TO DATABASE" as "Action";
use tmp;

select "DROP/CREATE TABLE" as "Action";
drop table if exists users;
create table if not exists users
(user_id smallint not null,
user_fname char(20) not null,
user_lname char(20) not null,
user_birthdate date not null,
user_education_years int not null,
primary key(user_id));

select "POPULATE TABLE AND DISPLAY CONTENTS" as "Action";
insert into users values
(1, 'Alan', 'Adams', '1970-04-08', 15),
(2, 'Bill', 'Baker', '1964-02-01', 18),
(3, 'Cass', 'Cooke', '1981-12-04', 12),
(4, 'Dina', 'Davis', '1944-06-06', 19),
(5, 'Earl', 'Edger', '1990-08-02', 17);
select * from users;

select "SET AND DISPLAY SCRIPT VARIABLES" as "Action";
set @minimum_education_years = 16;
set @birthdate_of_youngest_legal_worker = date_sub(curdate(), interval 16 
year);


select "   " as "Variable",
"" as "Value"
UNION
select "minimum_education_years=", @minimum_education_years
UNION
select "birthdate_of_youngest_legal_worker=",
@birthdate_of_youngest_legal_worker;

select "   " as "Variable",
"" as "Value"
UNION
select "minimum_education_years=", @minimum_education_years
UNION
select "birthdate_of_youngest_legal_worker=",
@birthdate_of_youngest_legal_worker;

select "EXECUTE QUERIES THAT USE SCRIPT VARIABLES" as "Action";
select concat("Get users who have more than ", @minimum_education_years,
" years of education") as "Query";
select * from users
where user_education_years >= @minimum_education_years;
select concat("Get users who are old enough to work, i.e. were born before 
",

@birthdate_of_youngest_legal_worker) as "Query";
select * from users
where user_birthdate <= @legal_to_work;


select "DISPLAY FUNCTION RESULTS" as "Action";
select " " as "Function", " " as 
"Value"

UNION
select "curdate()=", curdate()
UNION
select "now()=", now()
UNION
select "Firstname+Lastname=", concat(user_fname, ' ', user_lname)
from users where user_id = 1;

select "=== S C R I P T   E N D S ===" as "";

=

and this is the output of the script:

=
+---+
|   |
+---+
| === S C R I P T   B E G I N S === |
+---+
1 row in set (0.00 sec)

+-+
| Action  |
+-+
| CONNECT TO DATABASE |
+-+
1 row in set (0.00 sec)

Database changed
+---+
| Action|
+---+
| DROP/CREATE TABLE |
+---+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+-+
| Action  |
+-+
| POPULATE TABLE AND DISPLAY CONTENTS |
+-+
1 row in set (0.00 sec)

Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

+-++++--+
| user_id | user_fname | user_lname | user_birthdate | user_education_years 
|

+-++++--+
|   1 | Alan   | Adams  | 1970-04-08 |   15 
|
|   2 | Bill   | Baker  | 1964-02-01 |   18 
|
|   3 | Cass   | Cooke  | 1981-12-04 |   12 
|
|   4 | Dina   | Davis  | 1944-06-06 |   19 
|
|   5 | Earl   | Edger  | 1990-08-02 |   17 
|

+-++++--+
5 rows in set (0.00 sec)

+--+
| Action   |
+--+
| SET AND DISPLAY SCRIPT VARIABLES |
+--+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+-++
| Variable| Value  |
+-++
| 

#1191 - Can't find FULLTEXT index matching the column list

[afan]

Sorry for previous post without Subject line - send by accident.



Hi to all!

I have "products" table:

CREATE TABLE `products` (
  `prod_id` int(8) unsigned NOT NULL auto_increment,
  `prod_no` varchar(50) NOT NULL default '',
  `prod_name` varchar(255) NOT NULL default '',
  `prod_description` text,
  `prod_colors` text,
  `prod_includes` text,
  `prod_catalog` varchar(45) default NULL,
  `prod_status` enum('hidden','live','new') NOT NULL default 'new',
  `prod_supplier` varchar(45) default NULL,
  `prod_start_date` date default '-00-00',
  `prod_end_date` date default '-00-00',
  `prod_featured` enum('0','1') default NULL,
  `on_sale` enum('Yes','No') NOT NULL default 'No',
  PRIMARY KEY  (`prod_id`),
  UNIQUE KEY `prod_no` (`prod_no`),
  KEY `products_index1` (`prod_status`),
  KEY `products_index2` (`prod_start_date`,`prod_end_date`),
  KEY `on_sale` (`on_sale`),
  FULLTEXT KEY `prod_name` (`prod_name`),
  FULLTEXT KEY `prod_description` (`prod_description`)
) TYPE=MyISAM AUTO_INCREMENT=3367 ;

When I tried this:
SELECT * FROM products
WHERE match (prod_name) against ('+red +shirt');
I'll get some results.
But, when I tried this:
SELECT * FROM products
WHERE match (prod_name, prod_description) against ('+red +shirt');
I got this error message:
#1191 - Can't find FULLTEXT index matching the column list

What am I doing wrong?

Thanks for any help.

-afan

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

Re: SELECT Query GROUP BY

[Dan Buettner]

No problem, glad to help.

I noticed your comment in an earlier message about it seeming like a 
workaround - I don't think it seems like a workaround at all.


Having a table with the possible values makes for a normal database 
structure, and an approach that should keep you from having to modify 
your application's SQL queries when someone decides to add contract 
levels 6 through 10 and then later 11 and 12, for example.


I also wouldn't be concerned about performance using such a join - SQL 
database servers are optimized for JOIN operations.  They do them very 
well.  In your case I'd add a UNIQUE index on the contractlevel table, 
more to guard against duplicate values than for performance, though it 
certainly won't hurt performance.


Dan


Jay wrote:

Thank you Dan,

[...]

Otherwise it's just not possible to show
what's not there - in your case, think of this: how would MySQL know to
show 5 when there are no 5's, but not also show the count for every
other integer that's not there?  (6, 7, 8, .. 1048576, 1048577, etc.)

[...]

Sure, easy to understand. I was thinking in a (1,2,3,4,5) list instead
of a table

Thank you very much!

Jay




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

Re: SELECT Query GROUP BY

[Jay]

Thank you Dan,

[...]
> Otherwise it's just not possible to show
> what's not there - in your case, think of this: how would MySQL know to
> show 5 when there are no 5's, but not also show the count for every
> other integer that's not there?  (6, 7, 8, .. 1048576, 1048577, etc.)
[...]

Sure, easy to understand. I was thinking in a (1,2,3,4,5) list instead
of a table

Thank you very much!

Jay


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

Re: Reporting child tables referencing a parnet table

[Peter Brawley]

Ben,
This excellent script from Peter takes over a minute to run, and only 
identified RI (Referential Integrity) to one level of nesting.


If I try to delete a record and get 'a foreign key constraint fails', 
this returns instantly without any delay and to all nested levels of RI.


How does MySQL do this?  Is it possible to use MySQL to find out what 
it thinks is the problem?
InnoDB evidently has an internal map. The first query on 
information_schema takes 30 times longer than subsequent calls. It can 
be faster to parse Create Table statements, and you could do that 
recursively in an sproc.


PB


Regards,

Ben



Peter Brawley wrote:

Ben Clewett wrote:

Unfortunately I get:

mysql> SELECT
->  
c.table_schema,u.table_name,u.column_name,u.referenced_column_name

-> FROM information_schema.table_constraints AS c
-> INNER JOIN information_schema.key_column_usage AS u
-> USING( constraint_schema, constraint_name )
-> WHERE c.constraint_type = 'FOREIGN KEY'
->  AND u.referenced_table_schema='db'
->  AND u.referenced_table_name = 'table'
-> ORDER BY c.table_schema,u.table_name;

Empty set (27.86 sec)

Indeed, you didn't substitute your 'db' and 'table' values.

PB

-


mysql> SELECT version();
+-+
| version()   |
+-+
| 5.1.6-alpha-log |
+-+

But thanks, I think I can work with this and get the information I 
want.


Regards,

Ben.


Peter Brawley wrote:

Ben,:

Dear MySQL,

Can you please tell me if there is a way of listing all child 
tables which have a foreign key reference to a parent?

Find children of db.table:

SELECT
 c.table_schema,u.table_name,u.column_name,u.referenced_column_name
FROM information_schema.table_constraints AS c
INNER JOIN information_schema.key_column_usage AS u
USING( constraint_schema, constraint_name )
WHERE c.constraint_type = 'FOREIGN KEY'
 AND u.referenced_table_schema='db'
 AND u.referenced_table_name = 'table'
ORDER BY c.table_schema,u.table_name;

PB

-



Therefore I can find and delete a child row, then delete the 
parent without getting:


"a foreign key constraint fails"

Regards,

Ben Clewett

















--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.5/335 - Release Date: 5/9/2006


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

Re: Need help in recreating .MYD files

[Pradeep Chandru]

hi,
   Just to add up i have read some where ( i assume in the mailing list 
only) that OS recognizes the file based on few codes added at the 
starting / ending of a file. So there are ways to create a MYD file 
through vi editor as well (this is not from the angle of restoring the 
data in that table). As Daniel said if there is no binlogs/ update / 
query logs there is no way of restoring data to the current date.


--
Regards,
N.PradeepChandru,
MySQL DBA, Cricinfo Pvt Ltd.


Daniel da Veiga wrote:

On 5/9/06, balaraju mandala <[EMAIL PROTECTED]> wrote:

Thank you Daniel for reply.

Just in curiocity i want ask u, how u r taking daily backups, just using
'mysqldump' or using any Tools.



I'm using mysqldump plus bzip2 to compress data for about 2 years now.
Tried many tools, mysqldump scripted in bash (linux) or even in .bat
(windows) was always better than any other tools.

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




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

Re: SELECT Query GROUP BY

[Dan Buettner]

The easiest thing to do would be to create an additional table 
containing all the possible valid values for contract level, then join 
on that table to show counts.  Otherwise it's just not possible to show 
what's not there - in your case, think of this: how would MySQL know to 
show 5 when there are no 5's, but not also show the count for every 
other integer that's not there?  (6, 7, 8, .. 1048576, 1048577, etc.)


CREATE TABLE contractlevel ( level int );
then INSERT 1, 2, 3, etc.

then you need a LEFT JOIN like so:

select cl.level, count(c.level) as count
from contractlevel cl
left join contract c using (level)
group by cl.level;

Hope this helps!

Dan


Jay wrote:

Thank you, Peng Yi-fan

but incase there is no contract with the level 5, it will not be shown.
I would like to see:

level   amount
1   34
2   0
3   18
4   986
5   0

I could add it in the application, but I try to do it within the Query.

btw. the right join I mentioned, doesn't work.

Has someone another idea?

Thank you!

Jay


The schema of your contract should be like this:

Contract (id, level, ...)

where column 'id' is the primary key, isn't it?

If so, you can try this:

SELECT COUNT(id)
FROM contract 
GROUP BY level


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

To: 
Sent: Thursday, May 11, 2006 5:41 PM
Subject: SELECT Query GROUP BY



Hello MySQL Users

I have a contract table. Each contract has a certain level, which can be
in a range from 1-5. This information is stored as a number. There is no
additional table for the levels.

I would like to get a list with the amount of contracts of each level -
including 0 for the levels with no contracts.

Until now I just used :
SELECT COUNT(*), level FROM contract GROUP BY level
but this is just showing level with contracts.

I tried a right join with a table which contains just integer values.
Seems like a workaround, but I'm interested in a easier aolution - I bet
there is one.

Thank you!

Jay

PS: I'm using Version 4.1


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






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

Re: How to repair a table,

[Pradeep Chandru]

Hi,
   I think the syntax is to be verified.
myisamchk * * -u root -p * 
///*.*


mysql will accept only if the passwords is next to "-p" option

**: some frequently used options are mentioned below
-r, --recover
-o, --safe-recover
-e, --extend-check
-q, --quick
-S, --sort-index
-f, --force ( to be used cautiously)

i hope this will solve your issue :-)

--
Regards,
N.PradeepChandru,
MySQL DBA, Cricinfo Pvt Ltd.


Dilipkumar wrote:

Hi,

Yes it can be repaired using

myisamchk -u root -p  datadirectory the table name as tablename.* 
password

this will check the data and also the index file also.

Payne wrote:

hi, I got a table where the myi isn't able to re be read. I tried to 
run myisam but it give an error about the index.


Do I need to drop the table? Can it be repaired?


Payne







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

Re: Sum of counts

[Joerg Bruehe]

Hi Adam, all,


Adam Wolff wrote:

You could you use UNION to make this all execute in a single query.


Sure you can use UNION to get the data or the (separate) counts from the 
four tables, but a plain UNION can not sum over these individual parts.


I have not checked whether you can have a UNION in a subquery, this 
would be your only chance to do the OP's task in a single SQL statement.
But IMO, it is better structure to do four separate SELECTs on the 
individual tables than to force it all into one.


Regards,
Jörg

--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com

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

Re: SELECT Query GROUP BY

[Jay]

Thank you, Peng Yi-fan

but incase there is no contract with the level 5, it will not be shown.
I would like to see:

level   amount
1   34
2   0
3   18
4   986
5   0

I could add it in the application, but I try to do it within the Query.

btw. the right join I mentioned, doesn't work.

Has someone another idea?

Thank you!

Jay

> The schema of your contract should be like this:
> 
> Contract (id, level, ...)
> 
> where column 'id' is the primary key, isn't it?
> 
> If so, you can try this:
> 
> SELECT COUNT(id)
> FROM contract 
> GROUP BY level
> 
> - Original Message - 
> From: "Jay" <[EMAIL PROTECTED]>
> To: 
> Sent: Thursday, May 11, 2006 5:41 PM
> Subject: SELECT Query GROUP BY
> 
> 
>> Hello MySQL Users
>>
>> I have a contract table. Each contract has a certain level, which can be
>> in a range from 1-5. This information is stored as a number. There is no
>> additional table for the levels.
>>
>> I would like to get a list with the amount of contracts of each level -
>> including 0 for the levels with no contracts.
>>
>> Until now I just used :
>> SELECT COUNT(*), level FROM contract GROUP BY level
>> but this is just showing level with contracts.
>>
>> I tried a right join with a table which contains just integer values.
>> Seems like a workaround, but I'm interested in a easier aolution - I bet
>> there is one.
>>
>> Thank you!
>>
>> Jay
>>
>> PS: I'm using Version 4.1
>>
>>
>> -- 
>> 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: SELECT Query GROUP BY

[Peng Yi-fan]

The schema of your contract should be like this:

Contract (id, level, ...)

where column 'id' is the primary key, isn't it?

If so, you can try this:

SELECT COUNT(id)
FROM contract 
GROUP BY level

- Original Message - 
From: "Jay" <[EMAIL PROTECTED]>
To: 
Sent: Thursday, May 11, 2006 5:41 PM
Subject: SELECT Query GROUP BY


> Hello MySQL Users
> 
> I have a contract table. Each contract has a certain level, which can be
> in a range from 1-5. This information is stored as a number. There is no
> additional table for the levels.
> 
> I would like to get a list with the amount of contracts of each level -
> including 0 for the levels with no contracts.
> 
> Until now I just used :
> SELECT COUNT(*), level FROM contract GROUP BY level
> but this is just showing level with contracts.
> 
> I tried a right join with a table which contains just integer values.
> Seems like a workaround, but I'm interested in a easier aolution - I bet
> there is one.
> 
> Thank you!
> 
> Jay
> 
> PS: I'm using Version 4.1
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>

Re: Sum of counts

[Joerg Bruehe]

Hi Rhino, all,


Rhino wrote:

Hi Chris, Joerg, and everyone else following this discussion,

Joerg, you are correct; the best way to sum the tables is individually 
and then add the sums together with program logic of some kind, [[...]]


I'm afraid I jumped in and gave correct but irrelevant information. I 
saw that Chris's query lacked joining conditions so I explained why they 
were needed and how to write them. Unfortunately, this was premature: I 
should have thought about the basic problem more carefully first. [[...]]


My apologies to all for wasting your time with an inappropriate 
solution. I'll try not to do that again!


Well, things like this happen, and none of us is proof against them.

IMO, that is what a mailing list is good for:
- that we get info, tipps, ... from each other,
- that we improve on existing material, reaching better overall results,
- that we can correct each other where we went astray,
- and that we even learn from published errors.

Don't be too angry at yourself, such incidents may also provide help to 
others!


Regards
and thanks for _all_ your replies,

Jörg

--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com

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

SELECT Query GROUP BY

[Jay]

Hello MySQL Users

I have a contract table. Each contract has a certain level, which can be
in a range from 1-5. This information is stored as a number. There is no
additional table for the levels.

I would like to get a list with the amount of contracts of each level -
including 0 for the levels with no contracts.

Until now I just used :
SELECT COUNT(*), level FROM contract GROUP BY level
but this is just showing level with contracts.

I tried a right join with a table which contains just integer values.
Seems like a workaround, but I'm interested in a easier aolution - I bet
there is one.

Thank you!

Jay

PS: I'm using Version 4.1


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

Questions about InnoDB, innodb_buffer_pool_size and friends

[Dan Trainor]

Hello, all -

It's been a while since I've posted here, but I read the list daily. 
Thanks for all the help.


What I'm dealing with here is memory problems using MySQL 5.0.19 under 
FreeBSD.  Although I've enabled allocation of more memory per-process, 
as described by the FreeBSD notes for 5.0.x, I'm still seeing problems.


So I've done some research, and think I've come up with a solution. 
First, I'll provide some details as to my current setup.  Since I'm 
still relatively new to MySQL, and have even less experience with 
InnoDB, I'm hoping to receive some criticism for my configuration.  This 
would be fantastic.


my.cnf:

[mysqld]
# InnoDB settings
innodb_data_home_dir = /usr/local/mysql/data
innodb_data_file_path = 
ibdata1:500M;ibdata2:500M;ibdata3:500M;ibdata4:500M:autoextend:max:1000M

set-variable = innodb_buffer_pool_size=1600M
set-variable = innodb_additional_mem_pool_size=180M
innodb_flush_log_at_trx_commit=1
query_cache_type = 1
query_cache_size = 40M
set-variable=max_connections=900
set-variable=max_connect_errors=1000

We're trying to make our database 'hot', and stick as much of it as we 
can, into memory.  I see a few problems with the current configuration 
(hopefully others can see more problems than I do):


I don't have a innodb_log_file_size in there.  I think that I would 
benefit from using this one, because the default is 5M.  I believe that 
our bottleneck has a lot to do with disk I/O as well, so I think 
bringing this up substantially would help.


If we have four ibdataN files of 500M each, there's no way that we can 
make this database 'hot', especially when taking into consideration that 
this is a 32bit platform.  On top of that, our innodb_buffer_pool_size 
is set to 1600M.


Now, where do I go from here?  A few ideas that have come to mind, is 
going back to 3 data files.  I guess this process would involve dumping 
the data, as described by the documentation, adjusting the data file 
sizes accordingly, and re-importing the data.  I believe that this can 
be accomplished because the Comment field of 'SHOW TABLE STATUS;' says 
"InnoDB free: 580608 kB".  When I read that, I want to say that I can 
assume that I have as much as at least one data file's worth of data 
that is not being used, but is allocated on disk - which I can dump.  I 
believe that I can safely do this, and have the data files grow by using 
innodb_autoextend_increment.  Would this improve our chances of making 
the database completely 'hot'?


Another idea would involve dumping a 32bit platform in favor of a 64bit 
platform, and just throw more memory at it.  But who's employer would be 
fond of that?  ;)


Again, I appreciate your patience as you review this post, and thank you 
for taking the time to read it.  I understand that it has taken a while, 
but I can only hope that your response would benefit not only myself, 
but others that might have questions about the way that InnoDB actually 
works.  Even more.


Thanks!!
-dant

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

Re: Reporting child tables referencing a parnet table

[Ben Clewett]

MySQL,

Another general comment, I'm interested in knowing

This excellent script from Peter takes over a minute to run, and only 
identified RI (Referential Integrity) to one level of nesting.


If I try to delete a record and get 'a foreign key constraint fails', 
this returns instantly without any delay and to all nested levels of RI.


How does MySQL do this?  Is it possible to use MySQL to find out what it 
thinks is the problem?


Regards,

Ben



Peter Brawley wrote:

Ben Clewett wrote:

Unfortunately I get:

mysql> SELECT
->  
c.table_schema,u.table_name,u.column_name,u.referenced_column_name

-> FROM information_schema.table_constraints AS c
-> INNER JOIN information_schema.key_column_usage AS u
-> USING( constraint_schema, constraint_name )
-> WHERE c.constraint_type = 'FOREIGN KEY'
->  AND u.referenced_table_schema='db'
->  AND u.referenced_table_name = 'table'
-> ORDER BY c.table_schema,u.table_name;

Empty set (27.86 sec)

Indeed, you didn't substitute your 'db' and 'table' values.

PB

-


mysql> SELECT version();
+-+
| version()   |
+-+
| 5.1.6-alpha-log |
+-+

But thanks, I think I can work with this and get the information I want.

Regards,

Ben.


Peter Brawley wrote:

Ben,:

Dear MySQL,

Can you please tell me if there is a way of listing all child tables 
which have a foreign key reference to a parent?

Find children of db.table:

SELECT
 c.table_schema,u.table_name,u.column_name,u.referenced_column_name
FROM information_schema.table_constraints AS c
INNER JOIN information_schema.key_column_usage AS u
USING( constraint_schema, constraint_name )
WHERE c.constraint_type = 'FOREIGN KEY'
 AND u.referenced_table_schema='db'
 AND u.referenced_table_name = 'table'
ORDER BY c.table_schema,u.table_name;

PB

-



Therefore I can find and delete a child row, then delete the parent 
without getting:


"a foreign key constraint fails"

Regards,

Ben Clewett














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

Re: Reporting child tables referencing a parnet table

[Ben Clewett]

MySQL,

Another general comment, I'm interested in knowing

This excellent script from Peter takes over a minute to run, and only 
identified RI (Referential Integrity) to one level of nesting.


If I try to delete a record and get 'a foreign key constraint fails', 
this returns instantly without any delay and to all nested levels of RI.


How does MySQL do this?  Is it possible to use MySQL to find out what it 
thinks is the problem?


Regards,

Ben



Peter Brawley wrote:

Ben Clewett wrote:

Unfortunately I get:

mysql> SELECT
->  
c.table_schema,u.table_name,u.column_name,u.referenced_column_name

-> FROM information_schema.table_constraints AS c
-> INNER JOIN information_schema.key_column_usage AS u
-> USING( constraint_schema, constraint_name )
-> WHERE c.constraint_type = 'FOREIGN KEY'
->  AND u.referenced_table_schema='db'
->  AND u.referenced_table_name = 'table'
-> ORDER BY c.table_schema,u.table_name;

Empty set (27.86 sec)

Indeed, you didn't substitute your 'db' and 'table' values.

PB

-


mysql> SELECT version();
+-+
| version()   |
+-+
| 5.1.6-alpha-log |
+-+

But thanks, I think I can work with this and get the information I want.

Regards,

Ben.


Peter Brawley wrote:

Ben,:

Dear MySQL,

Can you please tell me if there is a way of listing all child tables 
which have a foreign key reference to a parent?

Find children of db.table:

SELECT
 c.table_schema,u.table_name,u.column_name,u.referenced_column_name
FROM information_schema.table_constraints AS c
INNER JOIN information_schema.key_column_usage AS u
USING( constraint_schema, constraint_name )
WHERE c.constraint_type = 'FOREIGN KEY'
 AND u.referenced_table_schema='db'
 AND u.referenced_table_name = 'table'
ORDER BY c.table_schema,u.table_name;

PB

-



Therefore I can find and delete a child row, then delete the parent 
without getting:


"a foreign key constraint fails"

Regards,

Ben Clewett














--
Ben Clewett
+44(0)1923 46
Project Manager
Road Tech Computer Systems Ltd
http://www.roadrunner.uk.com

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

Re: Reporting child tables referencing a parnet table

[Ben Clewett]

Peter,

I see...  I was thinking 'db' might have been a system table name or 
something...


This works perfectly, thanks.

Ben


Peter Brawley wrote:

Ben Clewett wrote:

Unfortunately I get:

mysql> SELECT
->  
c.table_schema,u.table_name,u.column_name,u.referenced_column_name

-> FROM information_schema.table_constraints AS c
-> INNER JOIN information_schema.key_column_usage AS u
-> USING( constraint_schema, constraint_name )
-> WHERE c.constraint_type = 'FOREIGN KEY'
->  AND u.referenced_table_schema='db'
->  AND u.referenced_table_name = 'table'
-> ORDER BY c.table_schema,u.table_name;

Empty set (27.86 sec)

Indeed, you didn't substitute your 'db' and 'table' values.

PB

-


mysql> SELECT version();
+-+
| version()   |
+-+
| 5.1.6-alpha-log |
+-+

But thanks, I think I can work with this and get the information I want.

Regards,

Ben.


Peter Brawley wrote:

Ben,:

Dear MySQL,

Can you please tell me if there is a way of listing all child tables 
which have a foreign key reference to a parent?

Find children of db.table:

SELECT
 c.table_schema,u.table_name,u.column_name,u.referenced_column_name
FROM information_schema.table_constraints AS c
INNER JOIN information_schema.key_column_usage AS u
USING( constraint_schema, constraint_name )
WHERE c.constraint_type = 'FOREIGN KEY'
 AND u.referenced_table_schema='db'
 AND u.referenced_table_name = 'table'
ORDER BY c.table_schema,u.table_name;

PB

-



Therefore I can find and delete a child row, then delete the parent 
without getting:


"a foreign key constraint fails"

Regards,

Ben Clewett














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

Looking for MySQL users for market research

[Matthew Zito]

Hi everyone,

My name is Matthew Zito, and I'm the Chief Scientist for a company called 
GridApp Systems based here in New York City.  We're currently looking at 
extending our database automation software to MySQL, and rather than assume 
that MySQL users are facing the same challenges as Oracle and SQL Server users 
(our current major supported database platforms), I thought that perhaps we 
could solicit opinions from the community - give people a chance to be heard on 
what is important to them.

What you'd have to do:  First, email me privately/OFF-LIST and let me know 
you'd like to get involved.  Then answer a few free-form questions over email 
at your leisure - shouldn't take more than 15 minutes.  Possibly respond to 
followup questions (if you're willing).

What you'd get: The chance to have a serious influence on the development of a 
product that you could eventually find very useful.  Also, if I don't get 
overwhelmed with responses, I can probably finagle some GridApp schwag - maybe 
a frisbee or shirt for people who contribute thoughtful responses.  Heck, if 
you're interested, we can probably even finagle giving a few people early peeks 
at whatever we put together.  Plus you'd get the warm fuzzies for being so 
helpful.

What I can promise for sure is that everyone's information will remain 
confidential, no one will ever contact you (except for me with more questions), 
and that this is the only time you'll see me make this request.  Hopefully, 
people who have been thinking about their MySQL headaches will be gracious 
enough to email me and answer a few questions.

Thanks for your time,
Matt

--
Matthew Zito
Chief Scientist
GridApp Systems
P: 646-452-4090
[EMAIL PROTECTED]
http://www.gridapp.com

RE: Intermittent deadlock/InnoDB

[Robert DiFalco]

No problem, just didn't want to jump the gun if it was a known issue or
if it were clear from the posted traces that I was doing something
wrong. 

-Original Message-
From: Stewart Smith [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, May 10, 2006 10:54 PM
To: Robert DiFalco
Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: Re: Intermittent deadlock/InnoDB

On Wed, 2006-05-10 at 16:26 -0700, Robert DiFalco wrote:
> Version 5.0.19.
> 
> We have no autogenerate keys. We perform a single insert and get a 
> lock timeout. The insert is done with a stored procedure with a single
line.
> The lockup happens VERY rarely and we have no idea how to reproduce
it.

Probably best to file a bug report in the bugs system. This is the best
way so we can track problem reports.
--
Stewart Smith, Software Engineer
MySQL AB, www.mysql.com
Office: +14082136540 Ext: 6616
VoIP: [EMAIL PROTECTED]
Mobile: +61 4 3 8844 332

Jumpstart your cluster:
http://www.mysql.com/consulting/packaged/cluster.html


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