Index Field

[Ow Mun Heng]

Hi All,

Just a quick question on whether I need both fields to be indexed.

The table is as below.. I'm wondering if I need to have these 2 fields ->
fa_id & serial_no

fa_id would be a 7 character int like "001", "002"
serial_no would be like "WMACK001", "WMACM121"

most of the time, we'll be referencing the serial_no more as it's what's
being used most often in my nature of work. Initially I thought that I could
index or make serial_no my primary key but as it's not fully numeric, I'm
not sure if this is advisable.

Can anyone help me out? Do I need the fa_id field? If I were to use mysql to
query for data, most likely I'll be using the Serial_no as the reference and
not fa_id. 

CommentsThanks.

CREATE TABLE fa_tracker (
  fa_id int(7) unsigned NOT NULL auto_increment,
  serial_no varchar(12) NOT NULL default '',
  dcm varchar(13) NOT NULL default '',
  family_id smallint(3) unsigned NOT NULL default '0',
  dcm_id smallint(3) unsigned NOT NULL default '0',
  media_id smallint(3) unsigned NOT NULL default '0',
  headers enum('1','2','3','4','5','6') default NULL,
  fail_heads enum('0','1','2','3','4','5') default NULL,
  source_id smallint(3) unsigned NOT NULL default '0',
  drive_cat_id smallint(3) unsigned NOT NULL default '0',
  drive_fa text NOT NULL,
  hm_cat_id smallint(3) unsigned NOT NULL default '0',
  hm_fa text NOT NULL,
  supplier_cat_id smallint(3) unsigned NOT NULL default '0',
  supplier_as_lab_fa text NOT NULL,
  date_submitted datetime NOT NULL default '1970-01-01 00:00:01',
  date_received datetime NOT NULL default '1970-01-01 00:00:01',
  date_sent datetime NOT NULL default '1970-01-01 00:00:01',
  dept_id smallint(3) unsigned NOT NULL default '0',
  user_id int(7) unsigned NOT NULL default '0',
  PRIMARY KEY  (fa_id),
  UNIQUE KEY serial_no (serial_no)
) 



Cheers,
Mun Heng, Ow
H/M Engineering
Western Digital M'sia 
DID : 03-7870 5168

Retrieving image files

[Sreesekhar Palaparthy]

I used mysql_real_escape_string() method to insert image file into 
database. Now how do icheck whether it got stored in proper format 
??

I used

SELECT * INTO OUTFILE F1 FROM IMGTABLE;

command , but i'm not able to see the image back in F1. How do i 
retrieve the image in proper format??
___
Download the hottest & happening ringtones here!
OR SMS: Top tone to 7333
Click here now: 
http://sms.rediff.com/cgi-bin/ringtone/ringhome.pl



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

Re: MySQL multiple query in php script. (newbie)

[Fred van Engen]

Barry,

On Thu, Jul 24, 2003 at 11:22:28PM -0500, Barry Hayden wrote:
> I have to admit I am very much a newbie at MySQL in general (but I'm
> working on it).
> 
> I have a fairly complex (for me) query that I am trying to complete
> using php. I can't get it to work, and I'm sure it's just in the way I
> am phrasing the MySQL part of this. The premise of this is a four
> question form that acts as a search engine to draw the appropriate
> information out of the db. If anyone could help I would really
> appreciate it. Here's what I have.
> 
>  $conn  = mysql_connect($server, $user, $pass);
> $select = mysql_select_db(realty, $conn);
> $sql = "SELECT Realtor, HouseAddress, Bathrooms, Bedrooms, YearBuilt,
> Garage, Location, Basement, Info FROM properties
> WHERE Bathrooms =='".$a."'", "Bedrooms =='".$b."'", "Garage
> =='".$c."'",
> "Basement =='".$d."'";

Use = for equality tests in MySQL, not == like in most programming
languages.

Check for errors with mysql_error().


> $result = mysql_query($sql, $conn);
> while ($record = mysql_fetch_assoc($result)){
> while (list($fieldvalue) = each ($record))
> echo $fieldname.;

I'm not sure what the point (.) is doing here.

Check for error messages in the webserver logs.


> }
> if (mysql_num_rows($result) ==0){
>   echo "Sorry, no matching results.";
> }
> ?>
> 


Regards,

Fred.


-- 
Fred van Engen  XB Networks B.V.
email: [EMAIL PROTECTED]Televisieweg 2
tel: +31 36 5462400 1322 AC  Almere
fax: +31 36 5462424 The Netherlands

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

Re: Help with DELETE and a subquery

[Nils Valentin]

Hi Vikram,

ignore both previous posts. Both don't work as wanted. I just realized that 
and I will come back to you after I created the tables and made it sure.

Sorry for the confusion.

Best regards

Nils Valentin
Tokyo/Japan


2003年 7月 25日 金曜日 14:42、Nils Valentin さんは書きました:
> Hi Vikram,
>
> just read the post once more.  I made a mistake. You want to delete the
> clients with no branches you said, so the command should look like
>
>  mysql> delete from clients where cid = (select clients.cid from clients
>  left join branches using (cid) WHERE ISNULL(clients.cid);
>
> Note that cid itself is ambigous, because in both tables.
>
> Best regards
>
> Nils Valentin
>
> 2003年 7月 25日 金曜日 14:31、Nils Valentin さんは書きました:
> > Hi Vikram,
> >
> > NULL is a special data type and requires special procedures.
> >
> > Try this:
> > > mysql> delete from clients where cid = (select clients.cid from clients
> > > left join branches using (cid) WHERE ISNULL(bid);
> >
> > Please make NO SPACE betwen ISNULL and  (bid) as otherwise wit will give
> > you an syntax error.
> >
> > Hope that ends the problems you had.
> >
> > Best regards
> >
> > Nils Valentin
> > Tokyo/Japan
> >
> > 2003年 7月 25日 金曜日 12:58、Vikram Vaswani さんは書きました:
> > > Hi all,
> > >
> > > I have the following two tables:
> > >
> > > mysql> SELECT * FROM clients;
> > > +-+-+
> > >
> > > | cid | cname   |
> > >
> > > +-+-+
> > >
> > > | 101 | JV Real Estate  |
> > > | 102 | ABC Talent Agency   |
> > > | 103 | DMW Trading |
> > > | 104 | Rabbit Foods Inc|
> > > | 110 | Sharp Eyes Detective Agency |
> > >
> > > +-+-+
> > > 5 rows in set (0.00 sec)
> > >
> > > mysql> SELECT * FROM branches;
> > > +--+-++--+
> > >
> > > | bid  | cid | bdesc  | bloc |
> > >
> > > +--+-++--+
> > >
> > > | 1011 | 101 | Corporate HQ   | CA   |
> > > | 1012 | 101 | Accounting Department  | NY   |
> > > | 1013 | 101 | Customer Grievances Department | KA   |
> > > | 1041 | 104 | Branch Office (East)   | MA   |
> > > | 1042 | 104 | Branch Office (West)   | CA   |
> > > | 1101 | 110 | Head Office| CA   |
> > > | 1031 | 103 | N Region HO| ME   |
> > > | 1032 | 103 | NE Region HO   | CT   |
> > > | 1033 | 103 | NW Region HO   | NY   |
> > >
> > > +--+-++--+
> > > 9 rows in set (0.01 sec)
> > >
> > > I need to delete all clients with no branches. I need to use a subquery
> > > to do this. Given these constraints, I came up with the following:
> > >
> > > mysql> delete from clients where cid = (select clients.cid from clients
> > > left join branches using (cid) WHERE bid is null);
> > >
> > > MySQL says:
> > >
> > > ERROR 1093: You can't specify target table 'clients' for update in FROM
> > > clause
> > >
> > > Does any one know why I am getting this error (MySQL 4.1)? Can you help
> > > me rewrite this operation *using a subquery only*?
> > >
> > > TIA,
> > >
> > > Vikram
> >
> > --
> > ---
> > Valentin Nils
> > Internet Technology
> >
> >  E-Mail: [EMAIL PROTECTED]
> >  URL: http://www.knowd.co.jp
> >  Personal URL: http://www.knowd.co.jp/staff/nils
>
> --
> ---
> Valentin Nils
> Internet Technology
>
>  E-Mail: [EMAIL PROTECTED]
>  URL: http://www.knowd.co.jp
>  Personal URL: http://www.knowd.co.jp/staff/nils

-- 
---
Valentin Nils
Internet Technology

 E-Mail: [EMAIL PROTECTED]
 URL: http://www.knowd.co.jp
 Personal URL: http://www.knowd.co.jp/staff/nils


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

Updating a record with MAX(value)

[Vikram Vaswani]

Hi all,

Is there a way to update the record with maximum value in a table with
another value?

I am trying this:

mysql> update services set sfee = 1 where sfee = max(sfee);
Query OK, 0 rows affected (0.06 sec)
Rows matched: 0  Changed: 0  Warnings: 0

But it doesn't make any changes to the data. Any ideas why?

TIA,

Vikram

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

Re: Help with DELETE and a subquery

[Nils Valentin]

Hi Vikram,

just read the post once more.  I made a mistake. You want to delete the 
clients with no branches you said, so the command should look like

 mysql> delete from clients where cid = (select clients.cid from clients
 left join branches using (cid) WHERE ISNULL(clients.cid);

Note that cid itself is ambigous, because in both tables.

Best regards

Nils Valentin




2003年 7月 25日 金曜日 14:31、Nils Valentin さんは書きました:
> Hi Vikram,
>
> NULL is a special data type and requires special procedures.
>
> Try this:
> > mysql> delete from clients where cid = (select clients.cid from clients
> > left join branches using (cid) WHERE ISNULL(bid);
>
> Please make NO SPACE betwen ISNULL and  (bid) as otherwise wit will give
> you an syntax error.
>
> Hope that ends the problems you had.
>
> Best regards
>
> Nils Valentin
> Tokyo/Japan
>
> 2003年 7月 25日 金曜日 12:58、Vikram Vaswani さんは書きました:
> > Hi all,
> >
> > I have the following two tables:
> >
> > mysql> SELECT * FROM clients;
> > +-+-+
> >
> > | cid | cname   |
> >
> > +-+-+
> >
> > | 101 | JV Real Estate  |
> > | 102 | ABC Talent Agency   |
> > | 103 | DMW Trading |
> > | 104 | Rabbit Foods Inc|
> > | 110 | Sharp Eyes Detective Agency |
> >
> > +-+-+
> > 5 rows in set (0.00 sec)
> >
> > mysql> SELECT * FROM branches;
> > +--+-++--+
> >
> > | bid  | cid | bdesc  | bloc |
> >
> > +--+-++--+
> >
> > | 1011 | 101 | Corporate HQ   | CA   |
> > | 1012 | 101 | Accounting Department  | NY   |
> > | 1013 | 101 | Customer Grievances Department | KA   |
> > | 1041 | 104 | Branch Office (East)   | MA   |
> > | 1042 | 104 | Branch Office (West)   | CA   |
> > | 1101 | 110 | Head Office| CA   |
> > | 1031 | 103 | N Region HO| ME   |
> > | 1032 | 103 | NE Region HO   | CT   |
> > | 1033 | 103 | NW Region HO   | NY   |
> >
> > +--+-++--+
> > 9 rows in set (0.01 sec)
> >
> > I need to delete all clients with no branches. I need to use a subquery
> > to do this. Given these constraints, I came up with the following:
> >
> > mysql> delete from clients where cid = (select clients.cid from clients
> > left join branches using (cid) WHERE bid is null);
> >
> > MySQL says:
> >
> > ERROR 1093: You can't specify target table 'clients' for update in FROM
> > clause
> >
> > Does any one know why I am getting this error (MySQL 4.1)? Can you help
> > me rewrite this operation *using a subquery only*?
> >
> > TIA,
> >
> > Vikram
>
> --
> ---
> Valentin Nils
> Internet Technology
>
>  E-Mail: [EMAIL PROTECTED]
>  URL: http://www.knowd.co.jp
>  Personal URL: http://www.knowd.co.jp/staff/nils

-- 
---
Valentin Nils
Internet Technology

 E-Mail: [EMAIL PROTECTED]
 URL: http://www.knowd.co.jp
 Personal URL: http://www.knowd.co.jp/staff/nils


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

Re: Help with DELETE and a subquery

[Nils Valentin]

Hi Vikram,

NULL is a special data type and requires special procedures.

Try this:

> mysql> delete from clients where cid = (select clients.cid from clients
> left join branches using (cid) WHERE ISNULL(bid);

Please make NO SPACE betwen ISNULL and  (bid) as otherwise wit will give you 
an syntax error.

Hope that ends the problems you had.

Best regards

Nils Valentin
Tokyo/Japan





2003年 7月 25日 金曜日 12:58、Vikram Vaswani さんは書きました:
> Hi all,
>
> I have the following two tables:
>
> mysql> SELECT * FROM clients;
> +-+-+
>
> | cid | cname   |
>
> +-+-+
>
> | 101 | JV Real Estate  |
> | 102 | ABC Talent Agency   |
> | 103 | DMW Trading |
> | 104 | Rabbit Foods Inc|
> | 110 | Sharp Eyes Detective Agency |
>
> +-+-+
> 5 rows in set (0.00 sec)
>
> mysql> SELECT * FROM branches;
> +--+-++--+
>
> | bid  | cid | bdesc  | bloc |
>
> +--+-++--+
>
> | 1011 | 101 | Corporate HQ   | CA   |
> | 1012 | 101 | Accounting Department  | NY   |
> | 1013 | 101 | Customer Grievances Department | KA   |
> | 1041 | 104 | Branch Office (East)   | MA   |
> | 1042 | 104 | Branch Office (West)   | CA   |
> | 1101 | 110 | Head Office| CA   |
> | 1031 | 103 | N Region HO| ME   |
> | 1032 | 103 | NE Region HO   | CT   |
> | 1033 | 103 | NW Region HO   | NY   |
>
> +--+-++--+
> 9 rows in set (0.01 sec)
>
> I need to delete all clients with no branches. I need to use a subquery to
> do this. Given these constraints, I came up with the following:
>
> mysql> delete from clients where cid = (select clients.cid from clients
> left join branches using (cid) WHERE bid is null);
>
> MySQL says:
>
> ERROR 1093: You can't specify target table 'clients' for update in FROM
> clause
>
> Does any one know why I am getting this error (MySQL 4.1)? Can you help me
> rewrite this operation *using a subquery only*?
>
> TIA,
>
> Vikram

-- 
---
Valentin Nils
Internet Technology

 E-Mail: [EMAIL PROTECTED]
 URL: http://www.knowd.co.jp
 Personal URL: http://www.knowd.co.jp/staff/nils


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

Access Denied

[Vidhya CS]

hi ,
I  installed MySQL-server-4.0.14-0.i386.rpm
MySQL-client-4.0.14-0.i386.rpm

when I give mysqladmin -u root
it is asking for password . The doc says that initially the password is
empty and we just need to press enter .
I read the mailing list posting but didnt find any clue
i didnt set any password .
mysql
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
NO)

how do rectify this .
thanks
vidhya.


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

MySQL multiple query in php script. (newbie)

[Barry Hayden]

I have to admit I am very much a newbie at MySQL in general (but I'm
working on it).

I have a fairly complex (for me) query that I am trying to complete
using php. I can't get it to work, and I'm sure it's just in the way I
am phrasing the MySQL part of this. The premise of this is a four
question form that acts as a search engine to draw the appropriate
information out of the db. If anyone could help I would really
appreciate it. Here's what I have.



Barry



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

Re: FW: Relationships in Mysql

[John Hicks]

I would just add a few words to Nils' excellent reply:

In the most general sense, a foreign key is just a primary 
key value that occurs outside of its native table (i.e. in 
a "foreign" table, get it?) in order to point to the record 
in its native table with that value as its primary key.

You can use foreign keys in your database design and 
programming without actually formally declaring them as 
such in the database system. Indeed, as you point out, you 
*have* to use them to establish relationships among your 
tables.

If the database system doesn't recognize the foreign key 
columns as foreign keys, then you must programatically 
ensure that referential integrity is maintained: 
--when you insert a new record, make sure that any foreign 
key points to an existing record (a record with that key 
value as its primary key)
--don't ever change the value of a record's primary key 
(that is, unless you are prepared to change the value of 
all the foreign keys in all the records pointing to that 
record)
--don't ever delete a record that has other records 
pointing to it via foreign keys (unless you delete all 
those records too).

Many people assume that a relational database system *must* 
recognize such foreign keys (and relationships among the 
tables) in order to be truly relational. But the term 
"relational" refers, not to the relationships among the 
tables, but to the basic relation that is defined by each 
table (and Codd's theory that even the most complex data 
structure can be broken down into such simple relations). 

--John

On Thursday 24 July 2003 08:00 pm, Nils Valentin wrote:
> Hi Eli,
>
> The short answer is .. you create relationships between
> tables by creating foreign keys and primary keys.
> However, you might not even need them ;-) - I explain
> below.
>
> Foreign keys and primary keys are used to create
> relations between tables.
>
> Using them will bind you to some rules which you can use
> to your advantage (or not), thats entirely up to you.
>
> If you define a primary key than any foreign key can link
> to the primary key. However , a foreign ke can only
> contain a value which is already defined in a primary
> key.
>
> This can be used to restrict that only valid values are
> insert for foreign keys or f.e no primary key is deleted
> where still existing foreign keys are pointing to the
> primary key record. Currently only the InnoDB table
> format supports Foreign keys.
>
>
> In order to use a join you dont need to create any keys.
> You can free define the columns to be used for the join
> condition.
>
> Please see the JOIN Syntax for more information.
>
> http://www.mysql.com/doc/en/JOIN.html
>
> Best regards
>
> Nils Valentin
> Tokyo/Japan
>
> 2003年 7月 25日 金曜日 03:36、eli さんは書きました:
> > Hi,
> >
> > Can anyone explain me how to create relationships
> > between tables in Mysql?
> >
> > For making some kind of functions like joins, it is
> > indispensable to have relationships defined?
> >
> >
> > Thanks in advanced.
> >
> >
> > eli


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

Help with DELETE and a subquery

[Vikram Vaswani]

Hi all,

I have the following two tables:

mysql> SELECT * FROM clients;
+-+-+
| cid | cname   |
+-+-+
| 101 | JV Real Estate  |
| 102 | ABC Talent Agency   |
| 103 | DMW Trading |
| 104 | Rabbit Foods Inc|
| 110 | Sharp Eyes Detective Agency |
+-+-+
5 rows in set (0.00 sec)

mysql> SELECT * FROM branches;
+--+-++--+
| bid  | cid | bdesc  | bloc |
+--+-++--+
| 1011 | 101 | Corporate HQ   | CA   |
| 1012 | 101 | Accounting Department  | NY   |
| 1013 | 101 | Customer Grievances Department | KA   |
| 1041 | 104 | Branch Office (East)   | MA   |
| 1042 | 104 | Branch Office (West)   | CA   |
| 1101 | 110 | Head Office| CA   |
| 1031 | 103 | N Region HO| ME   |
| 1032 | 103 | NE Region HO   | CT   |
| 1033 | 103 | NW Region HO   | NY   |
+--+-++--+
9 rows in set (0.01 sec)

I need to delete all clients with no branches. I need to use a subquery to
do this. Given these constraints, I came up with the following:

mysql> delete from clients where cid = (select clients.cid from clients
left join branches using (cid) WHERE bid is null);

MySQL says:

ERROR 1093: You can't specify target table 'clients' for update in FROM clause

Does any one know why I am getting this error (MySQL 4.1)? Can you help me
rewrite this operation *using a subquery only*?

TIA,

Vikram

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

Problems with MySQL, Windows 2003, IIS6, ASP?

[Bill J. Vallance]

Hello,
I have been using Snitz forums on windows 2000 IIS5 and MySQL for the
DB.  I have set up a test Microsoft Server 2003 IIS6, but I am having
problems with the setup.  It is able to connect to the database and the
account I have set up has full rights to the db.  The problem is the
setup.asp can not create the tables.  I've posted up asking for help on
Snitz support forums, but no one else has been successful with MySQL &
Server 2003.
http://forum.snitz.com  
So my question is has anyone else had problems with MySQL and Windows
2003?  If so how did you fix it?
Thanks
Bill

mysql setup problem

[jackiu]

§A­Ì¦n! §Ú¨Ï¥Îlinux rehat 7.2 ¦w¸Ëmysql-3.23.43-1.scr.rpm®É­Ô,¹B¦æ¥X²{¦pªþ¥óªº¿ù»~«H®§,
¤£ª¾§A­Ì¯à§_À°À°§Ú,ÁÂÁÂ!

    ­P

§

    jackliu

 






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

Re: Log

[Nils Valentin]

Sounds like you want to give the DEBUG version a try ;-)

Best regards

Nils Valentin
Tokyo/Japan


2003年 7月 25日 金曜日 01:25、Miguel Perez さんは書きました:
> Hi:
>
> I have a question: does anyone know if exists a log in mysql to store only
> incorrect queries or queries that causes a code error in mysql.
>
> Greetings and thnx in advanced.
>
> _
> MSN. Más Útil Cada Día  http://www.msn.es/intmap/

-- 
---
Valentin Nils
Internet Technology

 E-Mail: [EMAIL PROTECTED]
 URL: http://www.knowd.co.jp
 Personal URL: http://www.knowd.co.jp/staff/nils


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

Re: FW: Relationships in Mysql

[Nils Valentin]

Hi Eli,

The short answer is .. you create relationships between tables by creating  
foreign keys and primary keys. However, you might not even need them ;-) - I 
explain below.

Foreign keys and primary keys are used to create relations between tables.

Using them will bind you to some rules which you can use to your advantage (or 
not), thats entirely up to you.  

If you define a primary key than any foreign key can link to the primary key. 
However , a foreign ke can only contain a value which is already defined in a 
primary key. 

This can be used to restrict that only valid values are insert for foreign 
keys or f.e no primary key is deleted where still existing foreign keys are 
pointing to the primary key record. Currently only the InnoDB table format 
supports Foreign keys.


In order to use a join you dont need to create any keys. You can free define 
the columns to be used for the join condition.

Please see the JOIN Syntax for more information.

http://www.mysql.com/doc/en/JOIN.html

Best regards

Nils Valentin
Tokyo/Japan


2003年 7月 25日 金曜日 03:36、eli さんは書きました:
> Hi,
>
> Can anyone explain me how to create relationships between tables in Mysql?
>
> For making some kind of functions like joins, it is indispensable to have
> relationships defined?
>
>
> Thanks in advanced.
>
>
> eli

-- 
---
Valentin Nils
Internet Technology

 E-Mail: [EMAIL PROTECTED]
 URL: http://www.knowd.co.jp
 Personal URL: http://www.knowd.co.jp/staff/nils


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

Configuring Database Server

[Grant Cooper]

I was hoping someone could tell me a way to increase the productivity of a
database server. It has nothing else installed other than the newest
production version of mysql and the default packages from FreeBSD.

Is there a toaster than can bring me up to speed.

Thanks.


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

Re: problem with searching mysql in php

[John Hicks]

Looks like you really want to be doing full-text searching:

http://www.mysql.com/doc/en/Fulltext_Search.html

As you are doing it now, your users will only get a hit if 
they enter the target keywords in the same sequence they 
are stored in.

--John

On Wednesday 23 July 2003 04:24 am, Ercan Yurt wrote:
> Hi All,
>
> Im writing a quick search in php. in this search  user
> can enter  turkish,english and greek key words  and i
> search them in mysql table(MyIsam), in this table data
> are  in unicode my sql string like that
>
> SELECT MAddInfo.idUL,MAddInfo.sBrief FROM MAddInfo LEFT
> JOIN UL ON MAddInfo.idUL=UL.idLanguage WHERE UL.bStatus=1
> AND MAddInfo.sKeyWords LIKE
> '%Σύνδεσμοι%
>'
>
> this query returns zero row
>
> How can i fix this problem
>
> thanks
> sincerely
>
>   Ercan Yurt
>   Yazilim Mühendisi
>   YTM (Yüksek Teknoloji Merkezi)
>   Tel:+90 216 445 53 65
>   Web: http://www.ytm.com.tr

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

optimization

[awards]

Hi,

I read at MysQl.com about Explain SELECT, it says at then end of a paragraph to 
optimize your query, using filesort or temporary, but I went to MySQL search engine no 
luck on finding them, can someone explain me both type.
**What i'm doing, since I'm using mysql for a program*** 
**I execute a query 
**when i get the query , I'm putting everything in a hash dbm
**Then i read the line i want to, I'm doing so because it is easy to overwritte info 
on the dbm which i really want to.

Thank You
Anthony

Re: InnoDB locking: Different behavior on 3.23.55 and 4.0.13

[Mikhail Entaltsev]

Heikki,

> I just tested this. You are probably using the query cache in 4.0. Then
> SELECT can return immediately without acquiring any locks.

Thank you very much. You are absolutly right.

> Of course, it can be discussed if the query cache, too, should respect
LOCK
> TABLES. I am forwarding this to Sanja.

IMHO current behaviour is absolutly correct.

Mikhail.


- Original Message - 
From: "Heikki Tuuri" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, July 24, 2003 11:43 PM
Subject: Re: InnoDB locking: Different behavior on 3.23.55 and 4.0.13


> Mikhail,
>
> I just tested this. You are probably using the query cache in 4.0. Then
> SELECT can return immediately without acquiring any locks.
>
> Of course, it can be discussed if the query cache, too, should respect
LOCK
> TABLES. I am forwarding this to Sanja.
>
> Thank you,
>
> Heikki
>
> - Original Message - 
> From: ""Mikhail Entaltsev"" <[EMAIL PROTECTED]>
> Newsgroups: mailing.database.mysql
> Sent: Thursday, July 24, 2003 3:44 PM
> Subject: InnoDB locking: Different behavior on 3.23.55 and 4.0.13
>
>
> > --=_NextPart_000_0119_01C351F2.03137C50
> > Content-Type: text/plain;
> > charset="koi8-r"
> > Content-Transfer-Encoding: quoted-printable
> >
> > Hi
> >
> > I have 2 MySQL  servers:=20
> > Server1 is 3.23.55-max-log
> > Server2 is 4.0.13-standard-log
> >
> > Let's assume that we have 2 connections (Conn1 and Conn2) and table =
> > test:
> > 
> > CREATE TABLE `test` (
> >   `id` int(3) NOT NULL auto_increment,
> >   `name` char(10) default '',
> >   PRIMARY KEY  (`id`)
> > ) TYPE=3DInnoDB;
> > 
> > and put some data in it:
> > 
> > insert into test (id, name) values (1, 'cat');
> > insert into test (id, name) values (2, 'dog');
> > insert into test (id, name) values (3, 'bird');
> >
> > 
> >
> > I execute queries in the order:
> >
> > Conn1:=20
> > LOCK TABLES test WRITE;
> >
> > then=20
> >
> > Conn2:
> > select * from test;
> >
> > On the Server1 Conn2 is locked and it will be locked even more=20
> > than innodb_lock_wait_timeout (that is not correct, right?).
> > But on the Server2 Conn2 returns results immediately.
> >
> > I guess that Server2 is wrong. What do you think?
> >
> > Thanks,
> > Mikhail.
> > --=_NextPart_000_0119_01C351F2.03137C50--
> >
>
>
>
> -- 
> 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: two masters replicating to a single slave

[Dathan Vance Pattishall]

You will need to run multiple mysqld servers on the slave.
Reason: a slave can have only one master (although it wouldn't be
technically difficult to change this, if a matrix struct was used).

How to run multiple slaves:

I have some code I'm adding to mysqld_multi, but you should use the
documentation on mysql.com; search for mysqld_multi.

-->-Original Message-
-->From: Osmin Castillo [mailto:[EMAIL PROTECTED]
-->Sent: Thursday, July 24, 2003 2:38 PM
-->To: [EMAIL PROTECTED]
-->Subject: two masters replicating to a single slave
-->
-->Hi guys, I need your help with this:
-->
-->I have two big databases in two different servers, each database
-->it's different too and I need to replicate each one to a single
-->mysql slave server.
-->
-->I need this:
-->
-->MasterA --->   SlaveC   <--  MasterB
-->
-->So, I need dbA and dbB replicated on the slave server C
-->
-->
-->Suggestions,ideas?
-->
-->Guille
-->
-->--
-->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: InnoDB locking: Different behavior on 3.23.55 and 4.0.13

[Heikki Tuuri]

Mikhail,

I just tested this. You are probably using the query cache in 4.0. Then
SELECT can return immediately without acquiring any locks.

Of course, it can be discussed if the query cache, too, should respect LOCK
TABLES. I am forwarding this to Sanja.

Thank you,

Heikki

- Original Message - 
From: ""Mikhail Entaltsev"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Thursday, July 24, 2003 3:44 PM
Subject: InnoDB locking: Different behavior on 3.23.55 and 4.0.13


> --=_NextPart_000_0119_01C351F2.03137C50
> Content-Type: text/plain;
> charset="koi8-r"
> Content-Transfer-Encoding: quoted-printable
>
> Hi
>
> I have 2 MySQL  servers:=20
> Server1 is 3.23.55-max-log
> Server2 is 4.0.13-standard-log
>
> Let's assume that we have 2 connections (Conn1 and Conn2) and table =
> test:
> 
> CREATE TABLE `test` (
>   `id` int(3) NOT NULL auto_increment,
>   `name` char(10) default '',
>   PRIMARY KEY  (`id`)
> ) TYPE=3DInnoDB;
> 
> and put some data in it:
> 
> insert into test (id, name) values (1, 'cat');
> insert into test (id, name) values (2, 'dog');
> insert into test (id, name) values (3, 'bird');
>
> 
>
> I execute queries in the order:
>
> Conn1:=20
> LOCK TABLES test WRITE;
>
> then=20
>
> Conn2:
> select * from test;
>
> On the Server1 Conn2 is locked and it will be locked even more=20
> than innodb_lock_wait_timeout (that is not correct, right?).
> But on the Server2 Conn2 returns results immediately.
>
> I guess that Server2 is wrong. What do you think?
>
> Thanks,
> Mikhail.
> --=_NextPart_000_0119_01C351F2.03137C50--
>



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

Re: mysql stops processing

[Heikki Tuuri]

Joe,

- Original Message - 
From: "Joe Shear" <[EMAIL PROTECTED]>
To: "Heikki Tuuri" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, July 24, 2003 11:33 PM
Subject: Re: mysql stops processing


> We do not use LOCK TABLES anywhere.  Are there any other commands that
> could cause a LOCK TABLES to occur?

no.

> It's possible that we have some
> process that is opening up two threads and processing on both.  We
> definitely didn't intend to if it is happening.  What exactly would be
> the case that would cause problems?  What determines when a thread stays
> inside InnoDB?

Normally, a thread is declared to leave InnoDB when the processing of the
SQL statement ends. But with LOCK TABLES it happens only when you call
UNLOCK TABLES.

> We are looking at upgrading to 4.0.14 in the near future, we're in the
> process of testing it right now.  I don't think we're going to increase
> the concurrency just yet, but, after upgrading to 4.0.14 if we see the
> same problem, I'll grab some more diagnostics.

Please bump up innodb_thread_concurrency to 300 now. The low value 8 was
introduced in 2002 in an attempt to fix 'thread thrashing' problems in
glibc. But those glibc problems are long past.

This looks like an InnoDB bug. 4.0.14 will help in finding out what it is.

> thanks for your help

You are welcome,

> joe

Heikki

> On Thu, 2003-07-24 at 12:49, Heikki Tuuri wrote:
> > Joe,
> >
> > are you using LOCK TABLES? Or does your application program open two
> > connections to MySQL and work based on the results from 2 connections?
> >
> > "
> > ---TRANSACTION 0 221806686, ACTIVE 925 sec, OS thread id 163881
> > 2 lock struct(s), heap size 320, undo log entries 1
> > MySQL thread id 31, query id 910 pas01.plaxo.com 10.1.0.1 pas
> > Trx read view will not see trx with id >= 0 221806723, sees < 0
221806596
> > ---TRANSACTION 0 221806623, ACTIVE 925 sec, OS thread id 180269
> > 2 lock struct(s), heap size 320, undo log entries 1
> > MySQL thread id 35, query id 991 pas03.plaxo.com 10.1.0.3 pas
> > Trx read view will not see trx with id >= 0 221806695, sees < 0
221806596
> > ---TRANSACTION 0 221806619, ACTIVE 925 sec, OS thread id 172075
> > 2 lock struct(s), heap size 320, undo log entries 1
> > MySQL thread id 33, query id 650 pas02.plaxo.com 10.1.0.2 pas
> > Trx read view will not see trx with id >= 0 221806695, sees < 0
221806596
> > ---TRANSACTION 0 221806610, ACTIVE 926 sec, OS thread id 135202
> > 2 lock struct(s), heap size 320, undo log entries 1
> > MySQL thread id 24, query id 448 pas03.plaxo.com 10.1.0.3 pas
> > Trx read view will not see trx with id >= 0 221806623, sees < 0
221806592
> >
> > ...
> >  8 queries inside InnoDB, 183 queries in queue; main thread: making
> > checkpoint
> > "
> >
> > Look above. The oldest transactions are just sitting there without doing
> > anything. Why is that? If they used LOCK TABLES they can still be
declared
> > to be "inside InnoDB", though they are actually waiting for further
commands
> > from the client. They are blocking other queries from entering InnoDB.
> >
> > Fix: set
> >
> > innodb_thread_concurrency=300
> >
> > Then during the next 2 weeks monitor with SHOW INNODB STATUS that the
number
> > of queries "inside InnoDB" does not grow. If it grows, then there is a
leak
> > in InnoDB.
> >
> > MySQL-4.0.14 has better diagnostic prints. It would be easier to find
the
> > problem with it.
> >
> > Regards,
> >
> > Heikki
> >
> > - Original Message - 
> > From: "Joe Shear" <[EMAIL PROTECTED]>
> > To: "Heikki Tuuri" <[EMAIL PROTECTED]>
> > Cc: <[EMAIL PROTECTED]>
> > Sent: Thursday, July 24, 2003 9:57 PM
> > Subject: Re: mysql stops processing
> >
> >
> > > Attached to this are two "show innodb status" reports from one of db
> > > servers while it appeared to have stopped processing.  One was taken
> > > after waiting 4 additional minutes to see if anything would change.
The
> > > numbers are fairly low because this happened to occur almost
immediately
> > > after we restarted mysql because of the same thing happening.  Again
> > > this is on mysql/innodb 3.23.56.
> > >
> > >
> > >
> > > On Wed, 2003-07-23 at 13:30, Heikki Tuuri wrote:
> > > > Joe,
> > > >
> > > > - Original Message - 
> > > > From: "Joe Shear" <[EMAIL PROTECTED]>
> > > > To: "Heikki Tuuri" <[EMAIL PROTECTED]>
> > > > Cc: <[EMAIL PROTECTED]>
> > > > Sent: Wednesday, July 23, 2003 11:23 PM
> > > > Subject: Re: mysql stops processing
> > > >
> > > >
> > > > >
> > > > >
> > > > > On Wed, 2003-07-23 at 13:11, Heikki Tuuri wrote:
> > > > > > Joe,
> > > > > >
> > > > > > - Original Message - 
> > > > > > From: "Joe Shear" <[EMAIL PROTECTED]>
> > > > > > To: "Heikki Tuuri" <[EMAIL PROTECTED]>
> > > > > > Cc: <[EMAIL PROTECTED]>
> > > > > > Sent: Wednesday, July 23, 2003 11:03 PM
> > > > > > Subject: Re: mysql stops processing
> > > > ...
> > > > > > 50 % during the hang? Is mysqld taking that much though it is
not
> > > > processing
> > > > > > anything?
> > > > > >
>

RE: SQL Help...

[nick]

Yep, saw that and did just as you stated :)

> Nick,  As you stated, your priority field datatype is "varchar", with
> possible
> values "Hi", "Medium" and "Low", as opposed to being integers. The use of
> max
> function, as suggested by some colleagues without knowing exactly the
> datatype
> would work correctly only on columns of datatype integer. In your case,
> for
> textual columns, lexicographic (dictionary) ordering will be used in
> computing
> function max, and "Medium" would win the competition, instead of "Hi" --
> which
> actually has the lowest ranking in the lexicographic ordering. Perhaps you
> could
> use the " CASE WHEN ..." constructs to map your textual priority into
> numeric
> (integer) values (e.g., L -> 1, M -> 2, H -> 3), and then apply the max
> function
> to the integer values to get correct results.
>
> Best regards,
> 
> Lin
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Thursday, July 24, 2003 4:41 PM
> To: Dathan Vance Pattishall
> Cc: [EMAIL PROTECTED]
> Subject: RE: SQL Help...
>
> I looked at the group by option already and I dont think it will do what I
> need it to do. I say this because it will only group things in the
> priority/task/whatever but that still leaves options for duplicate
> resources. Yes, it would get rid of the dup. resources per priority, but
> not for the entire table.
> I really want to group by the resource, but still have all the info
> available for what ever the highest priority task it is in.
>
> -Nick
>
> Btw, all the fields are varchars with the priorities being Hi, Medium,
> Low, but I could really care less on what particular priority it is atm.
>
>
>> -->-Original Message-
>> -->From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
>> -->Sent: Thursday, July 24, 2003 12:53 PM
>> -->To: [EMAIL PROTECTED]
>> -->Subject: SQL Help...
>> -->
>> -->After some searching around different books/manuals/google I still
>> can't
>> -->seem to figure out how do to this. What I have is a table with 4 cols
>> -->(task, resource, department, priority) and what I want to do is be
>> able
>> -->to
>> -->select distinct resources and list what their highest priority is.
>> -->In other words, if a resource is in a high priority task and a low
>> -->priority task, I only want to show the high priority task.
>>
>> Take a look at GROUP BY HAVING and Count(*) at mysql.com
>> Something like
>> SELECT resource, task, priority GROUP BY priority, task having priority
>>> 
>>
>> I have no idea what your column types are and what data is contained so
>> please excuse this guess.
>>
>>
>>
>> -->
>> -->Thanks for the help!
>> -->-Nick
>> -->
>> -->--
>> -->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 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]

two masters replicating to a single slave

[Osmin Castillo]

Hi guys, I need your help with this:

I have two big databases in two different servers, each database 
it's different too and I need to replicate each one to a single 
mysql slave server.

I need this:

MasterA --->   SlaveC   <--  MasterB

So, I need dbA and dbB replicated on the slave server C


Suggestions,ideas?

Guille

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

RE: SQL Help...

[Lin Yu]

Nick,  As you stated, your priority field datatype is "varchar", with possible
values "Hi", "Medium" and "Low", as opposed to being integers. The use of max
function, as suggested by some colleagues without knowing exactly the datatype
would work correctly only on columns of datatype integer. In your case, for
textual columns, lexicographic (dictionary) ordering will be used in computing
function max, and "Medium" would win the competition, instead of "Hi" -- which
actually has the lowest ranking in the lexicographic ordering. Perhaps you could
use the " CASE WHEN ..." constructs to map your textual priority into numeric
(integer) values (e.g., L -> 1, M -> 2, H -> 3), and then apply the max function
to the integer values to get correct results.

Best regards,

Lin
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 24, 2003 4:41 PM
To: Dathan Vance Pattishall
Cc: [EMAIL PROTECTED]
Subject: RE: SQL Help...

I looked at the group by option already and I dont think it will do what I
need it to do. I say this because it will only group things in the
priority/task/whatever but that still leaves options for duplicate
resources. Yes, it would get rid of the dup. resources per priority, but
not for the entire table.
I really want to group by the resource, but still have all the info
available for what ever the highest priority task it is in.

-Nick

Btw, all the fields are varchars with the priorities being Hi, Medium,
Low, but I could really care less on what particular priority it is atm.


> -->-Original Message-
> -->From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> -->Sent: Thursday, July 24, 2003 12:53 PM
> -->To: [EMAIL PROTECTED]
> -->Subject: SQL Help...
> -->
> -->After some searching around different books/manuals/google I still
> can't
> -->seem to figure out how do to this. What I have is a table with 4 cols
> -->(task, resource, department, priority) and what I want to do is be
> able
> -->to
> -->select distinct resources and list what their highest priority is.
> -->In other words, if a resource is in a high priority task and a low
> -->priority task, I only want to show the high priority task.
>
> Take a look at GROUP BY HAVING and Count(*) at mysql.com
> Something like
> SELECT resource, task, priority GROUP BY priority, task having priority
>> 
>
> I have no idea what your column types are and what data is contained so
> please excuse this guess.
>
>
>
> -->
> -->Thanks for the help!
> -->-Nick
> -->
> -->--
> -->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 General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Function to extract difference in Minutes from DateTime variables

[Dathan Vance Pattishall]

Use UNIX_TIMESTAMP or TIME_TO_SEC

-->-Original Message-
-->From: C.F. Scheidecker Antunes [mailto:[EMAIL PROTECTED]
-->Sent: Thursday, July 24, 2003 1:43 PM
-->Cc: 'MySQL Users'
-->Subject: Function to extract difference in Minutes from DateTime
-->variables
-->
-->Hello all,
-->
-->I need to compare a datetime field in my database with the current
time
-->and return only the records that are 5 minutes old or older. Is there
-->any way to do it? Any function to it?
-->
-->I use to_days() when I am doing the same thing but with days instead
of
-->minutes.
-->
-->Thanks in advance.
-->
-->
-->
-->--
-->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: SQL Help...

[nick]

That works great. =D
Knew it shouldn't be that difficult, thanks a bunch.
And it actually works with the Priorities being in text form to (low, med,
hi).

-Nick

> At 02:52 PM 7/24/2003, you wrote:
>>After some searching around different books/manuals/google I still can't
>>seem to figure out how do to this. What I have is a table with 4 cols
>>(task, resource, department, priority) and what I want to do is be able
>> to
>>select distinct resources and list what their highest priority is.
>>In other words, if a resource is in a high priority task and a low
>>priority task, I only want to show the high priority task.
>>
>>Thanks for the help!
>>-Nick
>
> Nick,
>  You mean something like this:
>
> select resource, Max(Concat(priority, '=', Resource)) ResourcePriority
> group by Resource
>
> This will work with priority 1 through 9.
>
> Mike
>
>


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

RE: SQL Help...

[Cabanillas Dulanto, Ulises]

Try:

select task, resource, department, max(priority) 
from table
group by task, resource, department

Regards,
Ulises

-Mensaje original-
De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Enviado el: Jueves 24 de Julio de 2003 02:53 PM
Para: [EMAIL PROTECTED]
Asunto: SQL Help...


After some searching around different books/manuals/google I still can't
seem to figure out how do to this. What I have is a table with 4 cols
(task, resource, department, priority) and what I want to do is be able to
select distinct resources and list what their highest priority is.
In other words, if a resource is in a high priority task and a low
priority task, I only want to show the high priority task.

Thanks for the help!
-Nick

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

RE: SQL Help...

[nick]

I looked at the group by option already and I dont think it will do what I
need it to do. I say this because it will only group things in the
priority/task/whatever but that still leaves options for duplicate
resources. Yes, it would get rid of the dup. resources per priority, but
not for the entire table.
I really want to group by the resource, but still have all the info
available for what ever the highest priority task it is in.

-Nick

Btw, all the fields are varchars with the priorities being Hi, Medium,
Low, but I could really care less on what particular priority it is atm.


> -->-Original Message-
> -->From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> -->Sent: Thursday, July 24, 2003 12:53 PM
> -->To: [EMAIL PROTECTED]
> -->Subject: SQL Help...
> -->
> -->After some searching around different books/manuals/google I still
> can't
> -->seem to figure out how do to this. What I have is a table with 4 cols
> -->(task, resource, department, priority) and what I want to do is be
> able
> -->to
> -->select distinct resources and list what their highest priority is.
> -->In other words, if a resource is in a high priority task and a low
> -->priority task, I only want to show the high priority task.
>
> Take a look at GROUP BY HAVING and Count(*) at mysql.com
> Something like
> SELECT resource, task, priority GROUP BY priority, task having priority
>> 
>
> I have no idea what your column types are and what data is contained so
> please excuse this guess.
>
>
>
> -->
> -->Thanks for the help!
> -->-Nick
> -->
> -->--
> -->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 General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Function to extract difference in Minutes from DateTime variables

[C.F. Scheidecker Antunes]

Hello all,

I need to compare a datetime field in my database with the current time 
and return only the records that are 5 minutes old or older. Is there 
any way to do it? Any function to it?

I use to_days() when I am doing the same thing but with days instead of 
minutes.

Thanks in advance.



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

Re: mysql stops processing

[Joe Shear]

We do not use LOCK TABLES anywhere.  Are there any other commands that
could cause a LOCK TABLES to occur?  It's possible that we have some
process that is opening up two threads and processing on both.  We
definitely didn't intend to if it is happening.  What exactly would be
the case that would cause problems?  What determines when a thread stays
inside InnoDB?

We are looking at upgrading to 4.0.14 in the near future, we're in the
process of testing it right now.  I don't think we're going to increase
the concurrency just yet, but, after upgrading to 4.0.14 if we see the
same problem, I'll grab some more diagnostics.  

thanks for your help
joe

On Thu, 2003-07-24 at 12:49, Heikki Tuuri wrote:
> Joe,
> 
> are you using LOCK TABLES? Or does your application program open two
> connections to MySQL and work based on the results from 2 connections?
> 
> "
> ---TRANSACTION 0 221806686, ACTIVE 925 sec, OS thread id 163881
> 2 lock struct(s), heap size 320, undo log entries 1
> MySQL thread id 31, query id 910 pas01.plaxo.com 10.1.0.1 pas
> Trx read view will not see trx with id >= 0 221806723, sees < 0 221806596
> ---TRANSACTION 0 221806623, ACTIVE 925 sec, OS thread id 180269
> 2 lock struct(s), heap size 320, undo log entries 1
> MySQL thread id 35, query id 991 pas03.plaxo.com 10.1.0.3 pas
> Trx read view will not see trx with id >= 0 221806695, sees < 0 221806596
> ---TRANSACTION 0 221806619, ACTIVE 925 sec, OS thread id 172075
> 2 lock struct(s), heap size 320, undo log entries 1
> MySQL thread id 33, query id 650 pas02.plaxo.com 10.1.0.2 pas
> Trx read view will not see trx with id >= 0 221806695, sees < 0 221806596
> ---TRANSACTION 0 221806610, ACTIVE 926 sec, OS thread id 135202
> 2 lock struct(s), heap size 320, undo log entries 1
> MySQL thread id 24, query id 448 pas03.plaxo.com 10.1.0.3 pas
> Trx read view will not see trx with id >= 0 221806623, sees < 0 221806592
> 
> ...
>  8 queries inside InnoDB, 183 queries in queue; main thread: making
> checkpoint
> "
> 
> Look above. The oldest transactions are just sitting there without doing
> anything. Why is that? If they used LOCK TABLES they can still be declared
> to be "inside InnoDB", though they are actually waiting for further commands
> from the client. They are blocking other queries from entering InnoDB.
> 
> Fix: set
> 
> innodb_thread_concurrency=300
> 
> Then during the next 2 weeks monitor with SHOW INNODB STATUS that the number
> of queries "inside InnoDB" does not grow. If it grows, then there is a leak
> in InnoDB.
> 
> MySQL-4.0.14 has better diagnostic prints. It would be easier to find the
> problem with it.
> 
> Regards,
> 
> Heikki
> 
> - Original Message - 
> From: "Joe Shear" <[EMAIL PROTECTED]>
> To: "Heikki Tuuri" <[EMAIL PROTECTED]>
> Cc: <[EMAIL PROTECTED]>
> Sent: Thursday, July 24, 2003 9:57 PM
> Subject: Re: mysql stops processing
> 
> 
> > Attached to this are two "show innodb status" reports from one of db
> > servers while it appeared to have stopped processing.  One was taken
> > after waiting 4 additional minutes to see if anything would change.  The
> > numbers are fairly low because this happened to occur almost immediately
> > after we restarted mysql because of the same thing happening.  Again
> > this is on mysql/innodb 3.23.56.
> >
> >
> >
> > On Wed, 2003-07-23 at 13:30, Heikki Tuuri wrote:
> > > Joe,
> > >
> > > - Original Message - 
> > > From: "Joe Shear" <[EMAIL PROTECTED]>
> > > To: "Heikki Tuuri" <[EMAIL PROTECTED]>
> > > Cc: <[EMAIL PROTECTED]>
> > > Sent: Wednesday, July 23, 2003 11:23 PM
> > > Subject: Re: mysql stops processing
> > >
> > >
> > > >
> > > >
> > > > On Wed, 2003-07-23 at 13:11, Heikki Tuuri wrote:
> > > > > Joe,
> > > > >
> > > > > - Original Message - 
> > > > > From: "Joe Shear" <[EMAIL PROTECTED]>
> > > > > To: "Heikki Tuuri" <[EMAIL PROTECTED]>
> > > > > Cc: <[EMAIL PROTECTED]>
> > > > > Sent: Wednesday, July 23, 2003 11:03 PM
> > > > > Subject: Re: mysql stops processing
> > > ...
> > > > > 50 % during the hang? Is mysqld taking that much though it is not
> > > processing
> > > > > anything?
> > > > >
> > > >
> > > > I think its all mysqld -- even when it's not going anything.  Could it
> > > > be thrashing on something?  We don't have our max connections set that
> > > > high (100).  When we were using 3.23.52, we had really bad problems
> with
> > > > a similar problem but the CPU usage would hit 100%.  When we upgraded,
> > > > the problem went away for a month.
> > >
> > > The 'thread thrashing' problem of 2002 caused CPU usage to increase by a
> > > factor of 100, but it did not stop processing completely.
> > >
> > > Anyway, we need the diagnostic outputs to conclude anything further.
> > >
> > > Regards,
> > >
> > > Heikki
> > -- 
> > Joe Shear <[EMAIL PROTECTED]>
> >
-- 
Joe Shear <[EMAIL PROTECTED]>


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

Re: select part of a field into another field

[Jason Joines]

-Original Message-
From: Jason Joines [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 24, 2003 1:53 PM
To: MySQL Users
Subject: select part of a field into another field
 I have a table with an email field that contains values of the form 
[EMAIL PROTECTED]  I need to populate a new field called uid with 
just the uid 
part of the email address.  Is there any way of using select to just 
retrieve the part before the @ and insert it into the uid field?

Thanks,

Jason Joines
Open Source = Open Mind

   



Jonathan Patton wrote:

Jason, 

For example, if you had a table named "test" with a column named "a", which contained one row "[EMAIL PROTECTED]" you would do a query like:

mysql> select substring(a,1,instr(a,'@')-1) from test;
+---+
| substring(a,1,instr(a,'@')-1) |
+---+
| test  |
+---+
1 row in set (0.00 sec)
There are probably other ways to do this as well. 

Jonathan

   Thanks, that was exactly what I needed!  Until all the responses to 
my message it never occured to me to search the manual for string fucntions.

Jason

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

Re: SQL Help...

[mos]

At 02:52 PM 7/24/2003, you wrote:
After some searching around different books/manuals/google I still can't
seem to figure out how do to this. What I have is a table with 4 cols
(task, resource, department, priority) and what I want to do is be able to
select distinct resources and list what their highest priority is.
In other words, if a resource is in a high priority task and a low
priority task, I only want to show the high priority task.
Thanks for the help!
-Nick
Nick,
You mean something like this:
select resource, Max(Concat(priority, '=', Resource)) ResourcePriority 
group by Resource

This will work with priority 1 through 9.

Mike



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

RE: SQL Help...

[Dathan Vance Pattishall]

-->-Original Message-
-->From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
-->Sent: Thursday, July 24, 2003 12:53 PM
-->To: [EMAIL PROTECTED]
-->Subject: SQL Help...
-->
-->After some searching around different books/manuals/google I still
can't
-->seem to figure out how do to this. What I have is a table with 4 cols
-->(task, resource, department, priority) and what I want to do is be
able
-->to
-->select distinct resources and list what their highest priority is.
-->In other words, if a resource is in a high priority task and a low
-->priority task, I only want to show the high priority task.

Take a look at GROUP BY HAVING and Count(*) at mysql.com
Something like
SELECT resource, task, priority GROUP BY priority, task having priority
> 

I have no idea what your column types are and what data is contained so
please excuse this guess.



-->
-->Thanks for the help!
-->-Nick
-->
-->--
-->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]

output from mysqlbug

[amg]

SEND-PR: -*- send-pr -*-
SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as
SEND-PR: will all comments (text enclosed in `<' and `>').
SEND-PR:
From: amg
To: [EMAIL PROTECTED]
Subject: [Command in the Tutorial generates error msg]

>Description:
 LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;
 generates the following error msg:
   ERROR 1148: The used command is not allowed with this MySQL version
>
>How-To-Repeat:

>Fix:


>Submitter-Id:  <[EMAIL PROTECTED]>
>Originator:
>Organization:
 
>MySQL support: [none]
>Synopsis:  
>Severity:  
>Priority:  
>Category:  mysql
>Class: 
>Release:   mysql-3.23.56 (Source distribution)
>Server: /usr/bin/mysqladmin  Ver 8.23 Distrib 3.23.56, for slackware-linux on i386
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version  3.23.56-log
Protocol version10
Connection  Localhost via UNIX socket
UNIX socket /var/run/mysql/mysql.sock
Uptime: 2 hours 58 min 35 sec

Threads: 1  Questions: 38  Slow queries: 0  Opens: 14  Flush tables: 1  Open tables: 2 
Queries per second avg: 0.004
>Environment:

System: Linux UrsaPro 2.4.20 #4 Mon Mar 17 22:08:41 PST 2003 i686 unknown
Architecture: i686

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-slackware-linux/3.2.2/specs
Configured with: ../gcc-3.2.2/configure --prefix=/usr --enable-shared 
--enable-threads=posix --enable-__cxa_atexit --disable-checking --with-gnu-ld 
--verbose --target=i386-slackware-linux --host=i386-slackware-linux
Thread model: posix
gcc version 3.2.2
Compilation info: CC='i386-slackware-linux-gcc'  CFLAGS='-O3 -march=i486 -mcpu=i686'  
CXX='gcc'  CXXFLAGS='-O3 -march=i486 -mcpu=i686 -felide-constructors -fno-exceptions 
-fno-rtti'  LDFLAGS=''
LIBC: 
lrwxrwxrwx1 root root   13 Apr 27 14:44 /lib/libc.so.6 -> libc-2.3.1.so
-rwxr-xr-x1 root root  1435624 Mar  5 00:57 /lib/libc-2.3.1.so
-rw-r--r--1 root root  2425490 Mar  5 00:57 /usr/lib/libc.a
-rw-r--r--1 root root  178 Mar  5 00:50 /usr/lib/libc.so
Configure command: ./configure '--prefix=/usr' '--with-mysqld-user=mysql' 
'--with-unix-socket-path=/var/run/mysql/mysql.sock' '--localstatedir=/var/lib/mysql' 
'--enable-assembler' '--with-raid' '--without-debug' '--without-readline' 
'--enable-thread-safe-client' '--without-bench' '--with-extra-charsets=complex' 
'--program-prefix=' '--program-suffix=' 'i386-slackware-linux' 'CFLAGS=-O3 -march=i486 
-mcpu=i686' 'CXXFLAGS=-O3 -march=i486 -mcpu=i686 -felide-constructors -fno-exceptions 
-fno-rtti' 'CXX=gcc' 'build_alias=i386-slackware-linux' 
'host_alias=i386-slackware-linux' 'target_alias=i386-slackware-linux'


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

SQL Help...

[nick]

After some searching around different books/manuals/google I still can't
seem to figure out how do to this. What I have is a table with 4 cols
(task, resource, department, priority) and what I want to do is be able to
select distinct resources and list what their highest priority is.
In other words, if a resource is in a high priority task and a low
priority task, I only want to show the high priority task.

Thanks for the help!
-Nick

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

Re: mysql stops processing

[Heikki Tuuri]

Joe,

are you using LOCK TABLES? Or does your application program open two
connections to MySQL and work based on the results from 2 connections?

"
---TRANSACTION 0 221806686, ACTIVE 925 sec, OS thread id 163881
2 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 31, query id 910 pas01.plaxo.com 10.1.0.1 pas
Trx read view will not see trx with id >= 0 221806723, sees < 0 221806596
---TRANSACTION 0 221806623, ACTIVE 925 sec, OS thread id 180269
2 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 35, query id 991 pas03.plaxo.com 10.1.0.3 pas
Trx read view will not see trx with id >= 0 221806695, sees < 0 221806596
---TRANSACTION 0 221806619, ACTIVE 925 sec, OS thread id 172075
2 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 33, query id 650 pas02.plaxo.com 10.1.0.2 pas
Trx read view will not see trx with id >= 0 221806695, sees < 0 221806596
---TRANSACTION 0 221806610, ACTIVE 926 sec, OS thread id 135202
2 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 24, query id 448 pas03.plaxo.com 10.1.0.3 pas
Trx read view will not see trx with id >= 0 221806623, sees < 0 221806592

...
 8 queries inside InnoDB, 183 queries in queue; main thread: making
checkpoint
"

Look above. The oldest transactions are just sitting there without doing
anything. Why is that? If they used LOCK TABLES they can still be declared
to be "inside InnoDB", though they are actually waiting for further commands
from the client. They are blocking other queries from entering InnoDB.

Fix: set

innodb_thread_concurrency=300

Then during the next 2 weeks monitor with SHOW INNODB STATUS that the number
of queries "inside InnoDB" does not grow. If it grows, then there is a leak
in InnoDB.

MySQL-4.0.14 has better diagnostic prints. It would be easier to find the
problem with it.

Regards,

Heikki

- Original Message - 
From: "Joe Shear" <[EMAIL PROTECTED]>
To: "Heikki Tuuri" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, July 24, 2003 9:57 PM
Subject: Re: mysql stops processing


> Attached to this are two "show innodb status" reports from one of db
> servers while it appeared to have stopped processing.  One was taken
> after waiting 4 additional minutes to see if anything would change.  The
> numbers are fairly low because this happened to occur almost immediately
> after we restarted mysql because of the same thing happening.  Again
> this is on mysql/innodb 3.23.56.
>
>
>
> On Wed, 2003-07-23 at 13:30, Heikki Tuuri wrote:
> > Joe,
> >
> > - Original Message - 
> > From: "Joe Shear" <[EMAIL PROTECTED]>
> > To: "Heikki Tuuri" <[EMAIL PROTECTED]>
> > Cc: <[EMAIL PROTECTED]>
> > Sent: Wednesday, July 23, 2003 11:23 PM
> > Subject: Re: mysql stops processing
> >
> >
> > >
> > >
> > > On Wed, 2003-07-23 at 13:11, Heikki Tuuri wrote:
> > > > Joe,
> > > >
> > > > - Original Message - 
> > > > From: "Joe Shear" <[EMAIL PROTECTED]>
> > > > To: "Heikki Tuuri" <[EMAIL PROTECTED]>
> > > > Cc: <[EMAIL PROTECTED]>
> > > > Sent: Wednesday, July 23, 2003 11:03 PM
> > > > Subject: Re: mysql stops processing
> > ...
> > > > 50 % during the hang? Is mysqld taking that much though it is not
> > processing
> > > > anything?
> > > >
> > >
> > > I think its all mysqld -- even when it's not going anything.  Could it
> > > be thrashing on something?  We don't have our max connections set that
> > > high (100).  When we were using 3.23.52, we had really bad problems
with
> > > a similar problem but the CPU usage would hit 100%.  When we upgraded,
> > > the problem went away for a month.
> >
> > The 'thread thrashing' problem of 2002 caused CPU usage to increase by a
> > factor of 100, but it did not stop processing completely.
> >
> > Anyway, we need the diagnostic outputs to conclude anything further.
> >
> > Regards,
> >
> > Heikki
> -- 
> Joe Shear <[EMAIL PROTECTED]>
>



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

RE: large mysql/innodb databases

[Joe Shear]

On Wed, 2003-07-23 at 15:27, Andrew Braithwaite wrote:
> >>Power problems are handled by our colo facility, we want to quickly
> restore for most hardware problems (disk/machine failures). 
> 
> Just have multiple inexpensive fully replicated servers with failover built
> into the application layer (that's what we do) - Individual machines can go
> down and the service still stays up.  When those dead boxes recover, they
> can catch up from the replication logs and go back into service..

Our first backup plan is to move over to the slaves if one of the
masters fails right now, but we do need to have something on tape, just
in case of some sort of major disaster.

> >>On a periodic basis, we will take a snapshot using innodb hotbackup of the
> master machine that will go to a third box with a bunch of big raid-5 ide
> drives. We were planning on starting with NFS for the short term since
> innodb hot backup doesn't go over the network and figure something else out
> later.
> 
> That's a good idea - my findings were that NFS was really slow and the best
> solution was to backup from a fully replicated slave (after it had
> temporarily stopped replicating) by piping the raw data files through tar
> and gzip (appropriate for you as you're not concerned abou cpu) to a backup
> big raid-5 ide server.
> 

NFS seems to be working ok for now since it's writing to a big raid-5
ide server.  Heikki mentioned that they are working on adding the
ability to use innodbhotbackup over a socket, so we plan to move to that
when it's available.  We aren't really comfortable with taking snapshots
from a slave since we've had problems with slaves getting corrupted over
time.  Most of the problems have been fixed, but until there have been
no replication bugs fixed for a few months, we aren't comfortable
relying solely on it.  

> >>One issue we have is that we are trying to plan out our setup for storing
> a total of about 25TB of data and we are trying to find the lowest cost
> solution, with decent reliability.
> 
> And I'm trying to find the secret of eternal youth :)
> 

We're considering moving to a solution like EMC's -- do you or anybody
else have any experience with that?  

> Cheers,
> 
> Andrew
> 
> 
> 
> 
> -Original Message-
> From: Joe Shear [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday 23 July 2003 22:51
> To: Andrew Braithwaite
> Cc: [EMAIL PROTECTED]
> Subject: RE: large mysql/innodb databases
> 
> 
> We don't expect recovery to be shorter than the time it takes for the
> hardware to copy the data over.  Restoring from tape should be a solution
> that is only needed in the case of a severe problem.  Power problems are
> handled by our colo facility, we want to quickly restore for most hardware
> problems (disk/machine failures).  
> 
> We don't actually store any archive/aggregate information.  Everything we
> store on the main databases is used on a relatively constant basis.  
> 
> What we are currently thinking about doing right now is having an identical
> master and slave, each with about 500 gigs (later these will be at about 1TB
> each).  On a periodic basis, we will take a snapshot using innodb hotbackup
> of the master machine that will go to a third box with a bunch of big raid-5
> ide drives.  We were planning on starting with NFS for the short term since
> innodb hot backup doesn't go over the network and figure something else out
> later.  This machine would then shutdown the slave, copy over the new
> snapshot, and restart replication at the point from the point that innodb
> hotbackup started running at. 
> We would also take the snapshot from the IDE box, and write it to tape at
> this point.  Any thoughts on this?  What are you doing?
> 
> One issue we have is that we are trying to plan out our setup for storing a
> total of about 25TB of data and we are trying to find the lowest cost
> solution, with decent reliability.  
> 
> On Wed, 2003-07-23 at 14:33, Andrew Braithwaite wrote:
> > Hi,
> > 
> > I'm afraid that with that amount of data and having a few huge 
> > constantly updated tables will result in huge restore times for 
> > disaster recovery (just untaring/copying backups of the magnitude of 
> > terabytes back to the live environment will take hours and hours..)
> > 
> > You're talking "massive enterprise sized solutions" and "we're on a 
> > budget" in the same sentence (which are not compatible with each 
> > other) - I know because we are the same here!
> > 
> > A couple of things I can suggest:
> > 
> > 1. Redesign your applications so that you archive/aggregate 
> > information that will never be used again.
> > 
> > 2. Write a function that will backup the "often changed" stuff on a 
> > daily basis and backup the seldom changed stuff on a weekly basis.  
> > (as you're on a budget use a few inexpensive IDE raid 5 linux boxes - 
> > 6 x 250GB = 1.25 TB for backup)
> > 
> > 3. Put in place a replication system that is so resilient that how 
> > ever many machines go down, t

Re: select part of a field into another field

[Victoria Reznichenko]

Jason Joines <[EMAIL PROTECTED]> wrote:
>  I have a table with an email field that contains values of the form 
> [EMAIL PROTECTED]  I need to populate a new field called uid with just the uid 
> part of the email address.  Is there any way of using select to just 
> retrieve the part before the @ and insert it into the uid field?

Use SUBSTRING_INDEX() function:
http://www.mysql.com/doc/en/String_functions.html


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





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

Re: select part of a field into another field

[Mysql List]

Jason Joines wrote:

 I have a table with an email field that contains values of the form 
[EMAIL PROTECTED]  I need to populate a new field called uid with just the 
uid part of the email address.  Is there any way of using select to 
just retrieve the part before the @ and insert it into the uid field?
select SUBSTRING(email, 1, INSTR(email, '@') - 1) frommytable  limit 
1,10;



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

RE: select part of a field into another field

[Dathan Vance Pattishall]

Its easier to do it code in my opinion but here is the function you will
need.

SUBSTRING_INDEX('[EMAIL PROTECTED]','@',1) --> joines

-->-Original Message-
-->From: Jason Joines [mailto:[EMAIL PROTECTED]
-->Sent: Thursday, July 24, 2003 10:53 AM
-->To: MySQL Users
-->Subject: select part of a field into another field
-->
-->  I have a table with an email field that contains values of the form
-->[EMAIL PROTECTED]  I need to populate a new field called uid with just the
uid
-->part of the email address.  Is there any way of using select to just
-->retrieve the part before the @ and insert it into the uid field?
-->
-->Thanks,
-->
-->Jason Joines
-->Open Source = Open Mind
-->
-->
-->
-->
-->--
-->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 part of a field into another field

[Jonathan Patton]

Jason, 

For example, if you had a table named "test" with a column named "a", which contained 
one row "[EMAIL PROTECTED]" you would do a query like:


mysql> select substring(a,1,instr(a,'@')-1) from test;
+---+
| substring(a,1,instr(a,'@')-1) |
+---+
| test  |
+---+
1 row in set (0.00 sec)


There are probably other ways to do this as well. 

Jonathan

> -Original Message-
> From: Jason Joines [mailto:[EMAIL PROTECTED]
> Sent: Thursday, July 24, 2003 1:53 PM
> To: MySQL Users
> Subject: select part of a field into another field
> 
> 
>   I have a table with an email field that contains values of the form 
> [EMAIL PROTECTED]  I need to populate a new field called uid with 
> just the uid 
> part of the email address.  Is there any way of using select to just 
> retrieve the part before the @ and insert it into the uid field?
> 
> Thanks,
> 
> Jason Joines
> Open Source = Open Mind
> 
> 
> 
> 
> -- 
> 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: Huge Server configuration

[Dathan Vance Pattishall]

-->-Original Message-
-->From: Mysql List [mailto:[EMAIL PROTECTED]
-->Sent: Thursday, July 24, 2003 10:47 AM
-->To: Dathan Vance Pattishall
-->Cc: [EMAIL PROTECTED]
-->Subject: Re: Huge Server configuration
-->
-->Dathan Vance Pattishall wrote:
-->
-->Only for read with lots of filtering. Sorting is not a priority. Only
-->for retreiving data with multiple conditions.
-->some of the fields are indexed. some of them are not. but those will
-->also be used in the where condition.
-->
-->>Reason by example:
-->>Sun T3 with 7 drives. I'm IO bound with a read heavy 2 Gb where most
of
-->>the data is in memory. I'm transferring 15MB a second of read
traffic.

I do a fair amount of writes and reads about 40/60 ratio something mysql
is not very good at. Thus the need for many flushes to the disk and disk
grabs. With this inevitability, this causes my RAID array to transfer a
lot of data so much so I'm hitting the arrays max throughput which is
about 15MB/sec or 140Mbits a second.

With your primarily read box you still might be IO bound especially
during table scans. Try using the query cache. As long as you don't
write to the mysql cached data it should be a win for you.

-->>
-->>
-->Please explain.
-->
-->Thanks Again
-->Chandra




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

select part of a field into another field

[Jason Joines]

 I have a table with an email field that contains values of the form 
[EMAIL PROTECTED]  I need to populate a new field called uid with just the uid 
part of the email address.  Is there any way of using select to just 
retrieve the part before the @ and insert it into the uid field?

Thanks,

Jason Joines
Open Source = Open Mind



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

Re: Huge Server configuration

[Mysql List]

Dathan Vance Pattishall wrote:

RAID-5 is cool, RAID-1+0 (10) is better for writes.
Your defiantly are going to be IO bound. I would go with many smaller
disks >= 20 disk, in multiple RAID-1+0 configurations on different
channels or better yet different RAID controllers.
 

Well I do not have the luxury. Money is already spent. Well there is no 
other way to change any of these things.
This whole system will only act for readonly purpose. There will be not 
much of updates will be going on.
Only for read with lots of filtering. Sorting is not a priority. Only 
for retreiving data with multiple conditions.
some of the fields are indexed. some of them are not. but those will 
also be used in the where condition.

Reason by example:
Sun T3 with 7 drives. I'm IO bound with a read heavy 2 Gb where most of
the data is in memory. I'm transferring 15Mb a second of read traffic.
 

Please explain.

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

RE: Huge Server configuration

[Christopher Knight]

BEGIN my.cnf
[mysqld]
port  = 3306
socket= /tmp/mysql.sock
basedir   = /usr/local/mysql

log   = /var/log/mysql/mysql.log
log-slow-queries  = /var/log/mysql/mysql-slow.log
log-err   = /var/log/mysql/mysql.err
log-bin   = /var/log/mysql/mysql-bin.log
log-long-format

skip-locking

##Change depending on situation
transaction-isolation = READ-COMMITTED

###Tweak Here##
set-variable  = sort_buffer=512M
set-variable  = record_buffer=512M
set-variable  = key_buffer=256M

set-variable  = log-warnings=1
set-variable  = long_query_time=30

###FOR CACHED QUERIES###
set-variable  = query_cache_size=128M

###TUNE BASED ON CONNECTIONS###
set-variable  = max_allowed_packet=32M
set-variable  = max_connections=50
set-variable  = thread_stack=64K
set-variable  = thread_cache=16
set-variable  = thread_concurrency=8


# InnoDB Config


###If you change these... make sure you have a backup !!! (before)
###These are just setup things... not really tweak
  #innodb_data_home_dir  = /usr/local/mysql/data/innodb
  #innodb_data_file_path = innodb1:500M:autoextend
  #innodb_log_group_home_dir   = /var/log/mysql/innodb
  #innodb_log_arch_dir   = /var/log/mysql/innodb/
  #set-variable  = innodb_log_files_in_group=3
  #set-variable  = innodb_log_file_size=10M
  #set-variable  = innodb_log_buffer_size=10M

set-variable  = innodb_lock_wait_timeout=10

###Look At These too!!
set-variable  = innodb_buffer_pool_size=512M
set-variable  = innodb_additional_mem_pool_size=128M
set-variable  = innodb_flush_log_at_trx_commit=0

END my.cnf

>what table types? Innodb.etc...
It is mainly innodb.

>about how many tables?
there are around 200 tables

>do you do alot of sorting?
Lots of sorting and fltering is done

>are the exact same queries repeated alot?
Not likely

>is the machine doing anything else or is mainly a DB server?
Nope. Just DB.

>can I borrow the machine for awhile?
Nope. It is our to be production box

>what version of mysql are you running?
4.0.14


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

FW: Relationships in Mysql

[eli]

Hi,

Can anyone explain me how to create relationships between tables in Mysql?

For making some kind of functions like joins, it is indispensable to have
relationships defined?


Thanks in advanced.


eli



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

RE: Huge Server configuration

[Dathan Vance Pattishall]

RAID-5 is cool, RAID-1+0 (10) is better for writes.
Your defiantly are going to be IO bound. I would go with many smaller
disks >= 20 disk, in multiple RAID-1+0 configurations on different
channels or better yet different RAID controllers.

Reason by example:
Sun T3 with 7 drives. I'm IO bound with a read heavy 2 Gb where most of
the data is in memory. I'm transferring 15Mb a second of read traffic.
 

-->-Original Message-
-->From: Mysql List [mailto:[EMAIL PROTECTED]
-->Sent: Thursday, July 24, 2003 10:16 AM
-->To: [EMAIL PROTECTED]
-->Subject: Re: Huge Server configuration
-->
-->Dathan Vance Pattishall wrote:
-->
-->>NICE
-->>
-->>No matter how big your disks are, the number of spindles and
throughput
-->>is your win.
-->>
-->>
-->I have RAID 5 with 5 hardisks, so usuable number of spindle will only
be
-->4.
-->
-->>my.cnf 3.5x options
-->>
-->>skip-locking
-->>skip-name-resolve
-->>
-->>set-variable = tmp_table_size=4096
-->>log-bin=binlog/something  make sure binlog is a symlink to a
separate
-->>partition / drive
-->>
-->>set-variable = key_buffer=4G
-->>set-variable  = table_cache=2600 # make sure your OS can handle *2
this
-->>many file descriptors
-->>set-variable = sort_buffer=512M # this is not a common mem pool but
a
-->>thread pool
-->>set-variable = record_buffer=512M
-->>set-variable = record_rnd_buffer=512M
-->>set-variable = myisam_sort_buffer_size=512M
-->>set-variable = max_allowed_packet=16M
-->>
-->>
-->>
-->>
-->>-->-Original Message-
-->>-->From: Mysql List [mailto:[EMAIL PROTECTED]
-->>-->Sent: Thursday, July 24, 2003 9:38 AM
-->>-->To: [EMAIL PROTECTED]
-->>-->Subject: Huge Server configuration
-->>-->
-->>-->Hello all,
-->>-->
-->>-->I have a server like 8way Intel Pentium 4 Xeon processor with
12GB
-->>RAM
-->>-->and 1TB harddisk space.
-->>-->All the tables size are over 10GB and they have over 100mm
records.
-->>-->
-->>-->Could some one help me get an appropriate mysql
-->>configuration(my.conf)
-->>-->file for the machine.
-->>-->
-->>-->I  understand ther are lots of factors depends on it to get a
steady
-->>-->working configuration.
-->>-->All I need is some model configuration. I think later on I can
tune
-->>thar
-->>-->up.
-->>-->
-->>-->Thanx in Advance
-->>-->-Chandra
-->>-->
-->>-->
-->>-->
-->>-->
-->>-->--
-->>-->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 General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Huge Server configuration

[Mysql List]

Dathan Vance Pattishall wrote:

NICE

No matter how big your disks are, the number of spindles and throughput
is your win.
 

I have RAID 5 with 5 hardisks, so usuable number of spindle will only be 4.

my.cnf 3.5x options

skip-locking
skip-name-resolve
set-variable = tmp_table_size=4096
log-bin=binlog/something  make sure binlog is a symlink to a separate
partition / drive
set-variable = key_buffer=4G
set-variable  = table_cache=2600 # make sure your OS can handle *2 this
many file descriptors
set-variable = sort_buffer=512M # this is not a common mem pool but a
thread pool
set-variable = record_buffer=512M
set-variable = record_rnd_buffer=512M
set-variable = myisam_sort_buffer_size=512M
set-variable = max_allowed_packet=16M


-->-Original Message-
-->From: Mysql List [mailto:[EMAIL PROTECTED]
-->Sent: Thursday, July 24, 2003 9:38 AM
-->To: [EMAIL PROTECTED]
-->Subject: Huge Server configuration
-->
-->Hello all,
-->
-->I have a server like 8way Intel Pentium 4 Xeon processor with 12GB
RAM
-->and 1TB harddisk space.
-->All the tables size are over 10GB and they have over 100mm records.
-->
-->Could some one help me get an appropriate mysql
configuration(my.conf)
-->file for the machine.
-->
-->I  understand ther are lots of factors depends on it to get a steady
-->working configuration.
-->All I need is some model configuration. I think later on I can tune
thar
-->up.
-->
-->Thanx in Advance
-->-Chandra
-->
-->
-->
-->
-->--
-->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: Huge Server configuration

[Mysql List]

Christopher Knight wrote:

what table types? Innodb.etc...

It is mainly innodb.

about how many tables?

there are around 200 tables

do you do alot of sorting?

Lots of sorting and fltering is done

are the exact same queries repeated alot?

Not likely

is the machine doing anything else or is mainly a DB server?

Nope. Just DB.

can I borrow the machine for awhile?

Nope. It is our to be production box

what version of mysql are you running?
 

4.0.14

Thanks for your reply
-Chandra


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

Re: query help - "people who have bought this item have also bought"

[Roger Baklund]

* Chuck Barnett
> Hi I'm looking to create a query that will look through my previous orders
> and create a table of what items have sold with what products.
>
> My current table that holds the order details is set up like this.
> orderID, productID, quantity.
>
> Any suggestions would be appreceiated.

Maybe something like this:

mysql> use test
mysql> create table orders (orderID int,productID int,quantity int);
Query OK, 0 rows affected (0.42 sec)

mysql> insert into orders values
 (1,1,1),(1,2,1),(2,1,2),(2,3,1),(3,1,1),
 (3,2,2),(4,1,1),(4,2,1),(5,2,1),(6,3,1);
Query OK, 10 rows affected (0.03 sec)
Records: 10  Duplicates: 0  Warnings: 0

select o2.productID, sum(o2.quantity) qty, count(o2.quantity) cnt
  from orders o,orders o2
  where
o2.orderID = o.orderId and
o2.productID != o.productID and
o.productID = 1
  group by o2.productID
  order by qty desc;

+---+--+-+
| productID | qty  | cnt |
+---+--+-+
| 2 |4 |   3 |
| 3 |1 |   1 |
+---+--+-+
2 rows in set (0.00 sec)

Use 'having' to select only those with qty or cnt more than x.

--
Roger


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

Re: Does mysql save to a temp file??

[Mark Matthews]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Michael Masse wrote:

> In the later part of June a new Linux Mysql server was installed.
> While mysqld was still running, the data folder was moved to a different
> volume and a soft link to the new location was put in it's place.   I
> know, bad idea, but everything seemed to work fine, so I didn't think
> anything of it.   Today, the server had to be rebooted for the first
> time since, and now all data since the folder move is gone.   It's as if
> mysqld was just using ram to cache everything, and not writing out
> changes to disk.Is this true, that all data was only written to
> memory and not disk, or is it possible that the data was written out to
> a temporary location somewhere that I might be able to recover some of
> this data from?
>
> Mike
>

Hi!

I'm assuming since you mention symlinks, that you're running on Unix.

Unix allows processes to write to/read from open file descriptors, even
when some other process removes the underlying file. The physical file
isn't actually removed untill _all_ processes using it have finished
with it. I'm assuming when you say 'moved to a different volume', that
you did a 'mv' to a directory that was under a different mount point.
That means it was basically translated into a 'cp' of the original
directory to the new volume, followed by an 'rm', which is where the
problem comes in.

What most likely happened is that the data was written to and read from
disk, using the file pointed to by the _original_ file descriptor (not
the symlinked file). Once you shutdown MySQL, you lost all of that data.
It's not a MySQL problem, it's how the operating system behaves. The
data is gone. I hope you had backups and/or bin logging turned on,
otherwise you won't be able to recover it :(

Although it is not prudent to move files out from underneath running
processes in Unix for this very reason, you could've gotten away with
this if you did a 'FLUSH TABLES WITH READ LOCK' before you moved the
files, followed by an 'UNLOCK TABLES', so that MySQL would close all
file descriptors, and not allow writes until after you moved them.

-Mark

- --
Mr. Mark Matthews
MySQL AB, Software Development Manager, J2EE and Windows Platforms
Office: +1 708 557 2388
www.mysql.com

Are you MySQL Certified?
http://www.mysql.com/certification/
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.1 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQE/IBLEtvXNTca6JD8RArhCAKC73pWAZIA8rVtFBsGKzv7AbL7BvQCdGJfs
IzcHzKB4bI3N5IGp7+penAI=
=xrMY
-END PGP SIGNATURE-


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

RE: Huge Server configuration

[Christopher Knight]

what table types? Innodb.etc...
about how many tables?
do you do alot of sorting?
are the exact same queries repeated alot?
is the machine doing anything else or is mainly a DB server?
can I borrow the machine for awhile?
what version of mysql are you running?

chris


-Original Message-
From: Mysql List [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 24, 2003 11:38 AM
To: [EMAIL PROTECTED]
Subject: Huge Server configuration


Hello all,

I have a server like 8way Intel Pentium 4 Xeon processor with 12GB RAM 
and 1TB harddisk space.
All the tables size are over 10GB and they have over 100mm records.

Could some one help me get an appropriate mysql configuration(my.conf) 
file for the machine.

I  understand ther are lots of factors depends on it to get a steady 
working configuration.
All I need is some model configuration. I think later on I can tune thar up.

Thanx in Advance
-Chandra




-- 
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: Huge Server configuration

[Dathan Vance Pattishall]

NICE

No matter how big your disks are, the number of spindles and throughput
is your win.

my.cnf 3.5x options

skip-locking
skip-name-resolve

set-variable = tmp_table_size=4096
log-bin=binlog/something  make sure binlog is a symlink to a separate
partition / drive

set-variable = key_buffer=4G
set-variable  = table_cache=2600 # make sure your OS can handle *2 this
many file descriptors
set-variable = sort_buffer=512M # this is not a common mem pool but a
thread pool
set-variable = record_buffer=512M
set-variable = record_rnd_buffer=512M
set-variable = myisam_sort_buffer_size=512M
set-variable = max_allowed_packet=16M




-->-Original Message-
-->From: Mysql List [mailto:[EMAIL PROTECTED]
-->Sent: Thursday, July 24, 2003 9:38 AM
-->To: [EMAIL PROTECTED]
-->Subject: Huge Server configuration
-->
-->Hello all,
-->
-->I have a server like 8way Intel Pentium 4 Xeon processor with 12GB
RAM
-->and 1TB harddisk space.
-->All the tables size are over 10GB and they have over 100mm records.
-->
-->Could some one help me get an appropriate mysql
configuration(my.conf)
-->file for the machine.
-->
-->I  understand ther are lots of factors depends on it to get a steady
-->working configuration.
-->All I need is some model configuration. I think later on I can tune
thar
-->up.
-->
-->Thanx in Advance
-->-Chandra
-->
-->
-->
-->
-->--
-->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]

Huge Server configuration

[Mysql List]

Hello all,

I have a server like 8way Intel Pentium 4 Xeon processor with 12GB RAM 
and 1TB harddisk space.
All the tables size are over 10GB and they have over 100mm records.

Could some one help me get an appropriate mysql configuration(my.conf) 
file for the machine.

I  understand ther are lots of factors depends on it to get a steady 
working configuration.
All I need is some model configuration. I think later on I can tune thar up.

Thanx in Advance
-Chandra


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

Re: Log

[Paul DuBois]

At 11:25 -0500 7/24/03, Miguel Perez wrote:
Hi:

I have a question: does anyone know if exists a log in mysql to 
store only incorrect queries or queries that causes a code error in 
mysql.
There is no such log.

--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified?  http://www.mysql.com/certification/

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

Re: Log

[colbey]

I use an extraction layer between mysql and the application/db calls to
handle this.. typically it's only enabled in development to work out any bugs..
Might want to look at something like that..

On Thu, 24 Jul 2003, Miguel Perez wrote:

> Hi:
>
> I have a question: does anyone know if exists a log in mysql to store only
> incorrect queries or queries that causes a code error in mysql.
>
> Greetings and thnx in advanced.
>
> _
> MSN. Más Útil Cada Día  http://www.msn.es/intmap/
>
>
> --
> 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]

Log

[Miguel Perez]

Hi:

I have a question: does anyone know if exists a log in mysql to store only 
incorrect queries or queries that causes a code error in mysql.

Greetings and thnx in advanced.

_
MSN. Más Útil Cada Día  http://www.msn.es/intmap/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: query help - "people who have bought this item have also bought"

[Jay Blanchard]

[snip]
Hi I'm looking to create a query that will look through my previous
orders
and create a table of what items have sold with what products.

My current table that holds the order details is set up like this.
orderID, productID, quantity.

Any suggestions would be appreceiated.
[/snip]

You would have to select productID from the order details table where
the orderID for each productID is present

[example pseudo-code]
SELECT a.orderID, a.productID
FROM order_details a, order_details b
WHERE b.productID = (the currently selected)productID
[/ep-c]

HTH!

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

Re: Repair table

[Paul DuBois]

At 11:14 -0400 7/24/03, Jeff McKeon wrote:
Is there a way to issue a "REPAIR TABLE table_name" command to all
tables at once?  Something like "REPAIR TABLE *"
If you have access to the mysqlcheck command-line program, you can
accomplish that with this command:
mysqlcheck --repair db_name

Thanks,

Jeff McKeon


--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified?  http://www.mysql.com/certification/

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

Re: query help - "people who have bought this item have also bought"

[Paul DuBois]

At 11:02 -0500 7/24/03, Chuck Barnett wrote:
Hi I'm looking to create a query that will look through my previous orders
and create a table of what items have sold with what products.
My current table that holds the order details is set up like this.
orderID, productID, quantity.
I see an order ID and a product ID, but nothing related to "items".
You might want to provide more information.
Any suggestions would be appreceiated.

Thanks,
Chuck


--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified?  http://www.mysql.com/certification/

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

query help - "people who have bought this item have also bought"

[Chuck Barnett]

Hi I'm looking to create a query that will look through my previous orders
and create a table of what items have sold with what products.

My current table that holds the order details is set up like this.
orderID, productID, quantity.

Any suggestions would be appreceiated.

Thanks,
Chuck


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

Win98 - Work-around to sudden lost connection to database

[Al Caponi]

Hi all,

There is a limitation on Win98 when a client app using mm.mysql driver doing
extensive queries to MySQL 3.23.x frequently encounters a connection lost
error.
Error msg is something like:

'java.lang.Exception: Cannot connect to MySQL server on localhost:3306. Is
there a MySQL server running on the machine/port you are trying to connect
to? (java.net.SocketException)'

I've found a very useful piece of info. In brief, you've got to
change/create the registry key for max concurrent TCP connections:
Pls see: http://www.proxyplus.cz/faq/articles/EN/art10002.htm

If the above URL is unavailable, here is the important part:


Solution:
There are following articles in Microsoft Knowledge base:

Windows NT/2000:
http://support.microsoft.com/support/kb/articles/Q196/2/71.ASP
Windows 95: http://support.microsoft.com/support/kb/articles/Q170/7/91.ASP

Some users reported to us there is another solution for Windows 95/98/Me
which solved the problem at all. Windows 95/98/Me allows up to 100 (default
value) of concurrent TCP connections. It looks like when the number of
connection reaches the limit 10055 error is reported. The solution is to
increase the limit of simultaneous connections. You can do it by
changing/creating value of the key:

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\VxD\MSTCP\MaxConnection
s

If you will create the MaxConnections variable key use STRING type for it
(early Windows 95 used DWORD probably). Set the value to 300 for example,
restart the Windows and check whether 10055 problem disappeared.

There is  MS Knowledge Base article which describes the meaning of MSTCP
registry entries:
http://support.microsoft.com/support/kb/articles/Q158/4/74.ASP


My 2 cts worth... I LOVE THIS GUY!
HTH,
Al.



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

Alpha 4.1 - Temporary Tables Question

[Peter Gorelczenko]

I'm running as normal user (not root or database owner).  This user has create 
temporary table priv.

show tables partial:
GRANT CREATE TEMPORARY TABLES ON `foobar0`.* TO 'foo'@'localhost' -> (foobar0 
and foo are masks for the database name and user, respectively)

I created two tmp tables with intermdiate results.  I then try to join the 2 
tmp tables to a third tmp table and get the following error:
ERROR 1142: select command denied to user: '[EMAIL PROTECTED]' for table 'foobar' 
(foo and foobar are masks for the user and table, respectively).

How can I grant a select on a tmp table to the user that created it?  I 
shouldn't have to, should I?  I must be missing something obvious.  Did I 
screw up the GRANT CREATE TEMPORARY TABLES statement?

Thanks for your time.
Pete


-- 
This email may contain CONFIDENTIAL or PRIVILEGED information and is a private 
communication between the intended addressee and PharMetrics, Inc. If you 
have received this email in error, reading, copying, using, or disclosing its 
contents to others is prohibited. Please notify us of the delivery error by 
replying to this message, and then delete it from your system. Thank you. 


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

RE: Re[2]: Can run two versions of MySQL in Windows 2000?

[Degan, George E, JR, MGSVC]

Successfully removed the services.  I rechecked and, no mysqld, just
MySQL and mysqld2.  Thanks!

That should close out this thread!

-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 24, 2003 11:30 AM
To: Degan, George E, JR, MGSVC; Stefan Hinz
Cc: [EMAIL PROTECTED]
Subject: RE: Re[2]: Can run two versions of MySQL in Windows 2000?


At 6:23 -0500 7/24/03, Degan, George E, JR, MGSVC wrote:

>(I think I messed up here.  I decided to start from scratch, I removed
>"MySQL Servers and Clients 4.0.13" from the program list (as it appears
>when I run the "setup.exe" it installs MySQL software in Windows) and
>deleted all the folders (which included the old "c:\mysql\" directory
>with mysqld in the \bin folder) before performing the remove
>instructions below.  So now I can't get to the correct "mysqld" service
>to remove it.  It also appears that I can't remove it in windows as I
>don't see a "remove" or "delete" option in the Services area where you
>directed me.  Will that be "stuck" there, now?  MySQL and mysqld2 still
>appear in the services list.)

You need to remove the services using a MySQL server.  You can use any
of mysqld, mysqld-nt, mysqld-max, or mysql-max-nt to do this.  Suppose
you use mysqld.  This command removes the service having the default
name ("MySQL"):

mysqld --remove

And this one removes a service named "mysqld2":

mysqld --remove mysqld2

>
>>  net stop svc-name-1
>>  net stop svc-name-2
>(by "svs-name-1 and -2" do you refer to mysqld and mysqld2?  If not, to
>what services do you refer?)

MySQL (the default name) and mysqld2 (your second service name)

>  > To remove them:
>
>>  mysqld --remove svc-name-1
>>  mysqld --remove svc-name-2
>(it appears that you do not as "mysqld  --remove mysqld" doesn't seem
to
>make sense.  To what services do you refer?)

See above.

>
>>  Then start from the beginning, using the mysqld --install
instructions
>>  in the manual.
>
>>  (So I don't have to "Uninstall" them in windows since they are now
>>  services?  Or does this have the same effect?)
>
>I'd suggest you to get familiar with the Windows services concept.
>Services are, uh, services that can start and stop software programs
>(like the MySQL server). Rather than starting the software manually,
>you'd set up a service that does that (and can do that automatically,
>e.g. at system startup).
>
>Windows (NT, 2000, XP) usually uses services to start software that
>runs in the background, like the MySQL server.
>
>Unlike software, you don't uninstall a service, you _remove_ it, so it
>won't be there anymore to start the software program it's supposed to
>start. For the MySQL server, that's done with the "mysqld --remove
>" command.
>(How do we remove the mysqld service?)

You don't have a service named "mysqld", do you?


-- 
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

Are you MySQL certified?  http://www.mysql.com/certification/


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

RE: Repair table

[Gary Broughton]

It may or may not help, but if you connect to the database using
MySQLCC, you can highlight all the tables and repair, optimise or check
in one go (in the Windows version at least!).

Gary

-Original Message-
From: Jeff McKeon [mailto:[EMAIL PROTECTED] 
Sent: 24 July 2003 16:15
To: MySQL LIST
Subject: Repair table

Is there a way to issue a "REPAIR TABLE table_name" command to all
tables at once?  Something like "REPAIR TABLE *"

Thanks,

Jeff McKeon

-- 
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: Re[2]: Can run two versions of MySQL in Windows 2000?

[Paul DuBois]

At 6:23 -0500 7/24/03, Degan, George E, JR, MGSVC wrote:

(I think I messed up here.  I decided to start from scratch, I removed
"MySQL Servers and Clients 4.0.13" from the program list (as it appears
when I run the "setup.exe" it installs MySQL software in Windows) and
deleted all the folders (which included the old "c:\mysql\" directory
with mysqld in the \bin folder) before performing the remove
instructions below.  So now I can't get to the correct "mysqld" service
to remove it.  It also appears that I can't remove it in windows as I
don't see a "remove" or "delete" option in the Services area where you
directed me.  Will that be "stuck" there, now?  MySQL and mysqld2 still
appear in the services list.)
You need to remove the services using a MySQL server.  You can use any
of mysqld, mysqld-nt, mysqld-max, or mysql-max-nt to do this.  Suppose
you use mysqld.  This command removes the service having the default
name ("MySQL"):
mysqld --remove

And this one removes a service named "mysqld2":

mysqld --remove mysqld2


 net stop svc-name-1
 net stop svc-name-2
(by "svs-name-1 and -2" do you refer to mysqld and mysqld2?  If not, to
what services do you refer?)
MySQL (the default name) and mysqld2 (your second service name)

 > To remove them:

 mysqld --remove svc-name-1
 mysqld --remove svc-name-2
(it appears that you do not as "mysqld  --remove mysqld" doesn't seem to
make sense.  To what services do you refer?)
See above.


 Then start from the beginning, using the mysqld --install instructions
 in the manual.

 (So I don't have to "Uninstall" them in windows since they are now
 services?  Or does this have the same effect?)
I'd suggest you to get familiar with the Windows services concept.
Services are, uh, services that can start and stop software programs
(like the MySQL server). Rather than starting the software manually,
you'd set up a service that does that (and can do that automatically,
e.g. at system startup).
Windows (NT, 2000, XP) usually uses services to start software that
runs in the background, like the MySQL server.
Unlike software, you don't uninstall a service, you _remove_ it, so it
won't be there anymore to start the software program it's supposed to
start. For the MySQL server, that's done with the "mysqld --remove
" command.
(How do we remove the mysqld service?)
You don't have a service named "mysqld", do you?

--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified?  http://www.mysql.com/certification/

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

Re: Timestamp Format in 4.1 alpha

[Victoria Reznichenko]

mazur <[EMAIL PROTECTED]> wrote:
> Anyone know if there is a way to defeat the new format for the
> timestamp that appears in 4.1 alpha?
> 
> I upgraded a MySQL install on a development box that holds a copy of
> our production data.  I quickly saw that the timestamp format was
> changed from:
> 
> 20030520124559   , to:
> 2003-05-20 12:45:59
> 
> For better or worse (worse at the moment), I have a large app that is
> (unfortunately) dependent on the old format...blows up in many places
> without it actually.  Anyone else in the same boat?  Any thoughts
> about this dilemna, other than to say the app should not have been
> built to be dependent on the old format?  :-)

Use timestamp_column+0 :
http://www.mysql.com/doc/en/Prepare-upgrade-4.0-4.1.html


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





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

Re: Repair table

[Jeremy Zawodny]

On Thu, Jul 24, 2003 at 11:14:51AM -0400, Jeff McKeon wrote:
> Is there a way to issue a "REPAIR TABLE table_name" command to all
> tables at once?  Something like "REPAIR TABLE *"

No.

However, you could use myisamchk with the server off-line.  Then you'd
be able to script it and/or use shell wildcard expansion.
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

MySQL 4.0.13: up 14 days, processed 435,583,452 queries (345/sec. avg)

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

Re: Relay_Log_File

[Egor Egorov]

[EMAIL PROTECTED] wrote:
> 
> I am very much familier with the replication in 3.x version of mysql. Now I
> am trying with the Innodb version of mysql 4.0.13. The show slave status of
> this version is giving some new fieldswhat is actually Relay_Log_File.
> what is the use of this...

You can find this info in the manual:
http://www.mysql.com/doc/en/SHOW_SLAVE_STATUS.html



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




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

Repair table

[Jeff McKeon]

Is there a way to issue a "REPAIR TABLE table_name" command to all
tables at once?  Something like "REPAIR TABLE *"

Thanks,

Jeff McKeon

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

Re: Bookmarks

[Mike . Kent]

I think you're going to have trouble doing this with .asp. I know it can be
done with Perl or PHP, so you might want to look into those programming
languages. MySQL won't do it automatically.



   

  "Shahid M. Syed" 

  <[EMAIL PROTECTED]To:   "Paul DuBois" <[EMAIL 
PROTECTED]>
  rs.com>  cc:   <[EMAIL PROTECTED]>   

   Subject:  Re: Bookmarks 

  07/23/2003 09:04 

  PM   

   

   





> At 8:45 +0500 7/23/03, Shahid M. Syed wrote:
> >Hello
> >
> >Does any version (production or future) of mysql supports Bookmarks?
>
> What is "Bookmark" capability in a database context?

You can use the Bookmark property to set a bookmark (Bookmark: A property
of
a Recordset object or a form that contains a binary string identifying the
current record.) that uniquely identifies a particular record in the form's
underlying table, query, or SQL statement. Read/write Variant.

Example:
In VB6, I can read a bookmark in a variant like
varBookmark = rsName.Bookmark
Where varBookmark is a variant and rsName is a adodb recordset.

Later I can go back to the same record by
rsName.Bookmark = varBookmark

In ASP, I'm trying to come up with a better solution where there is too
much
data in recordset to display at once in a web page. Exactly like when you
search on Yahoo and at bottom its gives links like: Next, Next 10 and also
page links like 1,2,3 14.

If you know any other or better way for doing this, please let me know.

> >
> >Regards
> >
> >Shahid M. Syed
> >Karachi, Pakistan
> >http://members.tripod.com/shahid_syed
>
>
> --
> Paul DuBois, Senior Technical Writer
> Madison, Wisconsin, USA
> MySQL AB, www.mysql.com
>
> Are you MySQL certified?  http://www.mysql.com/certification/
>
>


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

So simple, yet wonderful:-) Re: New Group By, order by question

[Martin Moss]

Thanks to everyone who Helped,

Regards

Marty

- Original Message - 
From: "Joseph Bueno" <[EMAIL PROTECTED]>
To: "Martin Moss" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, July 24, 2003 3:56 PM
Subject: Re: New Group By, order by question


> select  order by abs(field) desc;
>
> Hope this helps,
> Joseph Bueno
>
> Martin Moss wrote:
> > All,
> >
> > I have a question about grouping numbers.
> >
> > Lets say I have 10 records each containing a numeric value:-
> >
> > 1
> > 2
> > 3
> > 5
> > 10
> > -1
> > -2
> > -3
> > -4
> > -5
> >
> > What I wish to do is to select the records from the database but group
them
> > like this :-
> > e.g. by the highest value (ASC or DESC) regarldess of whether the value
is
> > positive or negative?
> >
> > 10
> > 5
> > -5
> > -4
> > 3
> > -3
> > 2
> > -2
> > 1
> > -1
> >
> > Can I do this in one query, or do I need to make two queries
> >
> > Regards
> >
> > Marty
> >
> >
> >
> >
>
>
>


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

Re: Correct way of doing an isnumeric() test? ( Feature Request ? )

[Paul DuBois]

At 12:20 +1000 7/23/03, Daniel Kasak wrote:
Paul DuBois wrote:

At 10:46 +1000 7/23/03, Daniel Kasak wrote:

Hi all.

I need to test whether the first bit of a field is numeric.
For example, the field might contain:
154 boxes


Define this more precisely.  Is an acceptable match one or more
digits followed by a space?  If so, field REGEXP '^[0-9]+ ' should
work.
Yeah that will do it nicely.
It would still be good to have an isnumeric() function aliased to 
something like this, but anyway it solves my problem.
Thanks!
In your original message, you also said that such a function would add
to compatibility with other DB servers.
I'm afraid I don't understand this.  What you appear to want is not a
general function that characterizes values as numeric or non-numeric,
but a special-purpose function that looks specifically for values with
a numeric prefix followed by a space.  "isnumeric()" would be the wrong
name for this, and I don't really see how this aids compatibility.  Do
other DB servers really have such a thing?
It seems to me that REGEXP is still a better solution. It can be adapted
to a broad class of patterns, whereas the proposed isnumeric() handles
a limited special case.  No?
--
Daniel Kasak
IT Developer
* NUS Consulting Group*
Level 18, 168 Walker Street
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: www.nusconsulting.com


--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified?  http://www.mysql.com/certification/

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

Re: New Group By, order by question

[Joseph Bueno]

select  order by abs(field) desc;

Hope this helps,
Joseph Bueno
Martin Moss wrote:
All,

I have a question about grouping numbers.

Lets say I have 10 records each containing a numeric value:-

1
2
3
5
10
-1
-2
-3
-4
-5
What I wish to do is to select the records from the database but group them
like this :-
e.g. by the highest value (ASC or DESC) regarldess of whether the value is
positive or negative?
10
5
-5
-4
3
-3
2
-2
1
-1
Can I do this in one query, or do I need to make two queries

Regards

Marty






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

Timestamp Format in 4.1 alpha

[mazur]

Anyone know if there is a way to defeat the new format for the
timestamp that appears in 4.1 alpha?

I upgraded a MySQL install on a development box that holds a copy of
our production data.  I quickly saw that the timestamp format was
changed from:

20030520124559   , to:
2003-05-20 12:45:59

For better or worse (worse at the moment), I have a large app that is
(unfortunately) dependent on the old format...blows up in many places
without it actually.  Anyone else in the same boat?  Any thoughts
about this dilemna, other than to say the app should not have been
built to be dependent on the old format?  :-)

Rob
-


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

Re: New Group By, order by question

[Nils Valentin]

Hi Martin,


I understand the ABS() function is used for this.

Best regards

Nils Valentin
Tokyo/Japan

2003年 7月 24日 木曜日 23:42、Martin Moss さんは書きました:
> All,
>
> I have a question about grouping numbers.
>
> Lets say I have 10 records each containing a numeric value:-
>
> 1
> 2
> 3
> 5
> 10
> -1
> -2
> -3
> -4
> -5
>
> What I wish to do is to select the records from the database but group them
> like this :-
> e.g. by the highest value (ASC or DESC) regarldess of whether the value is
> positive or negative?
>
> 10
> 5
> -5
> -4
> 3
> -3
> 2
> -2
> 1
> -1
>
> Can I do this in one query, or do I need to make two queries
>
> Regards
>
> Marty

-- 
---
Valentin Nils
Internet Technology

 E-Mail: [EMAIL PROTECTED]
 URL: http://www.knowd.co.jp
 Personal URL: http://www.knowd.co.jp/staff/nils


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

RE: large mysql/innodb databases

[Adam Nelson]

With new HP ultrium tape drives, you can get 200GB/hr transfer rate.  I
kind of hate tapes (just like everybody else), but tapes have really
improved in the past few years.  These things are under $6k and could
back up 1-2 TB overnight without much problem.  With a library
(MSL6060), you can have 4 drives and 60 tapes for 12 TB backup.

I agree with the idea of skipping tape backup altogether, but that's
only if the data is reconstructable in a worst case scenario or if the
value of the lost data times the chance of it going down is not worth
more than the catastrophic failure backup cost. 

> -Original Message-
> From: Andrew Braithwaite [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, July 23, 2003 6:28 PM
> To: 'Joe Shear'
> Cc: [EMAIL PROTECTED]
> Subject: RE: large mysql/innodb databases
> 
> 
> >>Power problems are handled by our colo facility, we want to quickly
> restore for most hardware problems (disk/machine failures). 
> 
> Just have multiple inexpensive fully replicated servers with 
> failover built
> into the application layer (that's what we do) - Individual 
> machines can go
> down and the service still stays up.  When those dead boxes 
> recover, they
> can catch up from the replication logs and go back into service..
> 
> >>On a periodic basis, we will take a snapshot using innodb 
> hotbackup of the
> master machine that will go to a third box with a bunch of 
> big raid-5 ide
> drives. We were planning on starting with NFS for the short term since
> innodb hot backup doesn't go over the network and figure 
> something else out
> later.
> 
> That's a good idea - my findings were that NFS was really 
> slow and the best
> solution was to backup from a fully replicated slave (after it had
> temporarily stopped replicating) by piping the raw data files 
> through tar
> and gzip (appropriate for you as you're not concerned abou 
> cpu) to a backup
> big raid-5 ide server.
> 
> >>One issue we have is that we are trying to plan out our 
> setup for storing
> a total of about 25TB of data and we are trying to find the 
> lowest cost
> solution, with decent reliability.
> 
> And I'm trying to find the secret of eternal youth :)
> 
> Cheers,
> 
> Andrew
> 
> 
> 
> 
> -Original Message-
> From: Joe Shear [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday 23 July 2003 22:51
> To: Andrew Braithwaite
> Cc: [EMAIL PROTECTED]
> Subject: RE: large mysql/innodb databases
> 
> 
> We don't expect recovery to be shorter than the time it takes for the
> hardware to copy the data over.  Restoring from tape should 
> be a solution
> that is only needed in the case of a severe problem.  Power 
> problems are
> handled by our colo facility, we want to quickly restore for 
> most hardware
> problems (disk/machine failures).  
> 
> We don't actually store any archive/aggregate information.  
> Everything we
> store on the main databases is used on a relatively constant basis.  
> 
> What we are currently thinking about doing right now is 
> having an identical
> master and slave, each with about 500 gigs (later these will 
> be at about 1TB
> each).  On a periodic basis, we will take a snapshot using 
> innodb hotbackup
> of the master machine that will go to a third box with a 
> bunch of big raid-5
> ide drives.  We were planning on starting with NFS for the 
> short term since
> innodb hot backup doesn't go over the network and figure 
> something else out
> later.  This machine would then shutdown the slave, copy over the new
> snapshot, and restart replication at the point from the point 
> that innodb
> hotbackup started running at. 
> We would also take the snapshot from the IDE box, and write 
> it to tape at
> this point.  Any thoughts on this?  What are you doing?
> 
> One issue we have is that we are trying to plan out our setup 
> for storing a
> total of about 25TB of data and we are trying to find the lowest cost
> solution, with decent reliability.  
> 
> On Wed, 2003-07-23 at 14:33, Andrew Braithwaite wrote:
> > Hi,
> > 
> > I'm afraid that with that amount of data and having a few huge 
> > constantly updated tables will result in huge restore times for 
> > disaster recovery (just untaring/copying backups of the 
> magnitude of 
> > terabytes back to the live environment will take hours and hours..)
> > 
> > You're talking "massive enterprise sized solutions" and "we're on a 
> > budget" in the same sentence (which are not compatible with each 
> > other) - I know because we are the same here!
> > 
> > A couple of things I can suggest:
> > 
> > 1. Redesign your applications so that you archive/aggregate 
> > information that will never be used again.
> > 
> > 2. Write a function that will backup the "often changed" stuff on a 
> > daily basis and backup the seldom changed stuff on a weekly basis.  
> > (as you're on a budget use a few inexpensive IDE raid 5 
> linux boxes - 
> > 6 x 250GB = 1.25 TB for backup)
> > 
> > 3. Put in place a replication system that is so resilient that how 
>

RE: Myisachk

[Jeff McKeon]

> If I run the script:
> 
> -snip-
> datadir="/var/lib/mysql/telaurus/"
> find $datadir -name "*.MYI" -print | xargs myisamchk -r 
> --silent --fast
> -snip-
> 
> With the -r option on a live running db while connections are 
> being made, could there be any consequences?  Our production 
> db is a bear to bring down because we need to stop all the 
> different systems that us it first and this interups our 
> customer's services.
> 
> Also we'll be upgrading shortly (I hope) to ver 4.x, does the 
> new version have any better way's of accomplishing 
> checks/repairs on db that is up 24/7/365?

H I seem to have answered my own question... 
I ran the script with the -r option and now my db won't replicate...

What have I boogered up now?

--snip--
mysql> show slave status \G;
*** 1. row ***
Master_Host: 10.32.1.12
Master_User: test
Master_Port: 3306
  Connect_retry: 60
   Log_File:
Pos: 4
  Slave_Running: Yes
Replicate_do_db:
Replicate_ignore_db:
 Last_errno: 1030
 Last_error: error 'Got error 127 from table handler' on query
'UPDATE RemoteStation SET PendingIn='0' WHERE ID=980070408'
   Skip_counter: 0
1 row in set (0.00 sec)

ERROR:
No query specified
--snip--

Jeff McKeon


> -Original Message-
> From: Jeff McKeon 
> Sent: Thursday, July 24, 2003 10:02 AM
> To: gerald_clark
> Cc: MySQL LIST
> Subject: RE: Myisachk
> 
> 
> If I run the script:
> 
> -snip-
> datadir="/var/lib/mysql/telaurus/"
> find $datadir -name "*.MYI" -print | xargs myisamchk -r 
> --silent --fast
> -snip-
> 
> With the -r option on a live running db while connections are 
> being made, could there be any consequences?  Our production 
> db is a bear to bring down because we need to stop all the 
> different systems that us it first and this interups our 
> customer's services.
> 
> Also we'll be upgrading shortly (I hope) to ver 4.x, does the 
> new version have any better way's of accomplishing 
> checks/repairs on db that is up 24/7/365?
> 
> Thanks,
> 
> Jeff McKeon
> 
> > -Original Message-
> > From: gerald_clark [mailto:[EMAIL PROTECTED]
> > Sent: Thursday, July 24, 2003 9:32 AM
> > To: Jeff McKeon
> > Cc: MySQL LIST
> > Subject: Re: Myisachk
> > 
> > 
> > Shutdown mysqld before running myisamcheck,
> > or use check/repair table.
> > 
> > 
> > Jeff McKeon wrote:
> > 
> > >Hello,
> > >
> > >When I run a script to issue myismchk against the tables in my
> > >database, I get the following..
> > >
> > >[EMAIL PROTECTED] scripts]# ./check_mysql_tables
> > >myisamchk: MyISAM file /var/lib/mysql/telaurus/Admin.MYI
> > >myisamchk: warning: 1 clients is using or hasn't closed the table
> > >properly MyISAM-table '/var/lib/mysql/telaurus/Admin.MYI' is 
> > usable but
> > >should be fixed
> > >myisamchk: MyISAM file /var/lib/mysql/telaurus/Customer.MYI
> > >myisamchk: warning: 1 clients is using or hasn't closed the table 
> > >properly MyISAM-table '/var/lib/mysql/telaurus/Customer.MYI' is
> > usable but should
> > >be fixed
> > >
> > >This db is ver 3.23 and replicates from another db.
> > >
> > >What causes the tables to be "1 clients is using or hasn't
> > closed the
> > >table properly"
> > >
> > >I do have PHP web sites that pull data from this db, am I
> > perhaps not
> > >closing the tables after I access them from the PHP with select
> > >queries? If that's the case, why are tables I never touch with the 
> > >website getting this error, does replication have something 
> > to do with
> > >it?
> > >
> > >How can I tell what clients are currently using the tables?
> > >
> > >Thanks,
> > >
> > >Jeff McKeon
> > >
> > >  
> > >
> > 
> > 
> > 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?> [EMAIL PROTECTED]
> 
> 

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

RE: Re[4]: Can run two versions of MySQL in Windows 2000?

[Degan, George E, JR, MGSVC]

That did it!  I tried disabling and rebooting and that didn't work, but I went into 
the new folders and simply removed the services as indicated and they are now gone.

Thanks!!

George

-Original Message-
From: miguel solórzano [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 24, 2003 9:45 AM
To: Stefan Hinz; Degan, George E, JR, MGSVC
Cc: Paul DuBois; [EMAIL PROTECTED]; Mark Matthews
Subject: Re[4]: Can run two versions of MySQL in Windows 2000?


At 14:47 24/7/2003 +0200, Stefan Hinz wrote:
Hi,

> > (I think I messed up here.  I decided to start from scratch, I removed
> > "MySQL Servers and Clients 4.0.13" from the program list (as it appears
> > when I run the "setup.exe" it installs MySQL software in Windows) and
> > deleted all the folders (which included the old "c:\mysql\" directory
> > with mysqld in the \bin folder) before performing the remove
> > instructions below.  So now I can't get to the correct "mysqld" service
> > to remove it.  It also appears that I can't remove it in windows as I
> > don't see a "remove" or "delete" option in the Services area where you
> > directed me.  Will that be "stuck" there, now?  MySQL and mysqld2 still
> > appear in the services list.)
>
>I had the same kind of problem before. I don't know why that is so,
>but Windows doesn't provide a remove option in the Services Manager
>window. You can, however, edit the properties of a service and set it
>to "Disabled". After re-booting the system, that service should have
>gone.
>
>If it's still there, you can only remove it by hacking the Windows
>registry (Start > Run > regedit), looking for "mysql..." entries,
>delete them, and reboot. Maybe there's a more proper way of doing
>this, and if someone on this list knows, I'd be interested in how that
>would look like.
>
>(And, yes, I can hear all you Unix guys on this list chuckling.)

Yes the currently installer doesn't remove the service during
the un-install process. This deficiency should be fixed in the
new installer which is developed by Mark ( I am cc this for him
for to me correct if I am wrong).

However in the today behavior I don't recommend to play with the
registry. The situation is:

- Before to un-install remove the service using the server binary
   with mysqld --remove or mysqld --remove service-name.

- If you don't have anymore any server for to perform the remove
   service, you don't need to reboot, just re-install the new
   stuff and do the service remove.

-- 
Regards,

For technical support contracts, visit https://order.mysql.com/
Are you MySQL certified?, http://www.mysql.com/certification/

Miguel Angel Solórzano <[EMAIL PROTECTED]>
São Paulo - Brazil


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

New Group By, order by question

[Martin Moss]

All,

I have a question about grouping numbers.

Lets say I have 10 records each containing a numeric value:-

1
2
3
5
10
-1
-2
-3
-4
-5

What I wish to do is to select the records from the database but group them
like this :-
e.g. by the highest value (ASC or DESC) regarldess of whether the value is
positive or negative?

10
5
-5
-4
3
-3
2
-2
1
-1

Can I do this in one query, or do I need to make two queries

Regards

Marty




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

Re: Re-attaching DB to mysql

[Nils Valentin]

Hope I could be of help.

Best regards

Nils Valentin
Tokyo/Japan

2003年 7月 24日 木曜日 22:51、Henrik Schmiediche さんは書きました:
> Thanks for the help!
>
> - Henrik
>
>
> -Original Message-
> From: Nils Valentin [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, July 23, 2003 10:19 PM
> To: Henrik Schmiediche; 'Andrew Braithwaite'; 'Mysql'
> Subject: Re: Re-attaching DB to mysql
>
> Hi Hendrik,
>
> not sure if you have already received an answer, so please ignore if you
> are
> already back on track ;-)
>
> I did not find which OS you are using , but under Linux/Unix you would
> need to
> change the ownership back to the user "mysql" and the group "mysql"
> before
> you can use the copied data again.
>
> Go into your data folder (/var/lib/mysql for an rpm install) and do:
>
> chown -R mysql foldername
> chgrp -R msql foldername
>
> That should make them accessable again (assuming MySQL uses the owner
> and
> group "mysql".
>
> Best regards
>
> Nils Valentin
> Tokyo/Japan
>
> 2003年 7月 24日 木曜日 04:05、Henrik Schmiediche さんは書きました:
> > Copy of the data files :-(
> >
> > - Henrik
> >
> >
> > -Original Message-
> > From: Andrew Braithwaite [mailto:[EMAIL PROTECTED]
> > Sent: Wednesday, July 23, 2003 2:04 PM
> > To: 'Henrik Schmiediche'; 'Mysql'
> > Subject: RE: Re-attaching DB to mysql
> >
> > Is the backup a mysqldump or a copy of the data files?
> >
> > Andrew
> >
> > -Original Message-
> > From: Henrik Schmiediche [mailto:[EMAIL PROTECTED]
> > Sent: Wednesday 23 July 2003 19:48
> > To: 'Mysql'
> > Subject: Re-attaching DB to mysql
> >
> >
> >
> >
> >  Hello,
> > I have a backup of a mysql v3.0.18 DB. I need to extract one of the
>
> DB's
>
> > from that backup and place it into my current production v4.0.14 mysql
> > DB.
> >
> > Can this be done? How?
> >
> > Sincerely,
> >
> >- Henrik
> >
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/[EMAIL PROTECTED]

-- 
---
Valentin Nils
Internet Technology

 E-Mail: [EMAIL PROTECTED]
 URL: http://www.knowd.co.jp
 Personal URL: http://www.knowd.co.jp/staff/nils


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

Relay_Log_File

[john]

Hi Group,


I am very much familier with the replication in 3.x version of mysql. Now I
am trying with the Innodb version of mysql 4.0.13. The show slave status of
this version is giving some new fieldswhat is actually Relay_Log_File.
what is the use of this...

regds,




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

Re: Rewrite

[Diana Soares]

On Thu, 2003-07-24 at 11:58, mixo wrote:
> How can I write this query so it works on mysql 3.54:
> 
>  select groupmembers.memberid,users.name from groupmembers,users where  
> (not users.name='root')
>  and groupmembers.groupid=(select groups.id from 
> groups where groups.type='Privileged')
>  and groupmembers.memberid=users.id
>  order by name
> 

Try:

SELECT groupmembers.memberid, users.name 
FROM groupmembers, users, groups  
WHERE users.name<>'root' 
  AND groupmembers.groupid=groups.id AND groups.type='Privileged'
  AND groupmembers.memberid=users.id
ORDER BY name


> And, it it possible to remove the case sensetivity of table names.

Read the first item that appears in MySQL manual if you search for "case
sensitivity":
http://www.mysql.com/doc/en/Name_case_sensitivity.html


-- 
Diana Soares


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

R: Replication stopped without reason

[trashMan]

YesThe replication stopped in the next 60 secs.

I've tried to change  connect-retry to 30 secs without result.


:-/

Max


-Messaggio originale-
Da: Dominicus Donny [mailto:[EMAIL PROTECTED] 
Inviato: giovedì 24 luglio 2003 11.36
A: [EMAIL PROTECTED]
Oggetto: Re: Replication stopped without reason


Assume you leave the default connect-retry to 60 secs.
Is the replication still stopped in the next 60 secs
after that?

"Me fail English? That's unpossible"
###___Archon___###

- Original Message -
From: "trashMan" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, July 24, 2003 3:38 PM
Subject: Replication stopped without reason


>
> Hi,
>
> I've a big problem!!!
>
> The replication start and work correctly but after 10 minutes slave 
> don't update anymore. I must do "slave stop - slave start" for 
> restarting replication.
>
> In the log there are not error.
>
> The slave is connected to internet with a isdn-router while the master

> is connected with a cdn.
>
> What can i do for stabilize the replication??
>
> Massimiliano
>
>
> --
> 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 General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Myisachk

[Jeff McKeon]

If I run the script:

-snip-
datadir="/var/lib/mysql/telaurus/"
find $datadir -name "*.MYI" -print | xargs myisamchk -r --silent --fast
-snip-

With the -r option on a live running db while connections are being
made, could there be any consequences?  Our production db is a bear to
bring down because we need to stop all the different systems that us it
first and this interups our customer's services.

Also we'll be upgrading shortly (I hope) to ver 4.x, does the new
version have any better way's of accomplishing checks/repairs on db that
is up 24/7/365?

Thanks,

Jeff McKeon

> -Original Message-
> From: gerald_clark [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, July 24, 2003 9:32 AM
> To: Jeff McKeon
> Cc: MySQL LIST
> Subject: Re: Myisachk
> 
> 
> Shutdown mysqld before running myisamcheck,
> or use check/repair table.
> 
> 
> Jeff McKeon wrote:
> 
> >Hello,
> >
> >When I run a script to issue myismchk against the tables in my 
> >database, I get the following..
> >
> >[EMAIL PROTECTED] scripts]# ./check_mysql_tables
> >myisamchk: MyISAM file /var/lib/mysql/telaurus/Admin.MYI
> >myisamchk: warning: 1 clients is using or hasn't closed the table 
> >properly MyISAM-table '/var/lib/mysql/telaurus/Admin.MYI' is 
> usable but 
> >should be fixed
> >myisamchk: MyISAM file /var/lib/mysql/telaurus/Customer.MYI
> >myisamchk: warning: 1 clients is using or hasn't closed the table
> >properly
> >MyISAM-table '/var/lib/mysql/telaurus/Customer.MYI' is 
> usable but should
> >be fixed
> >
> >This db is ver 3.23 and replicates from another db.
> >
> >What causes the tables to be "1 clients is using or hasn't 
> closed the 
> >table properly"
> >
> >I do have PHP web sites that pull data from this db, am I 
> perhaps not 
> >closing the tables after I access them from the PHP with select 
> >queries? If that's the case, why are tables I never touch with the 
> >website getting this error, does replication have something 
> to do with 
> >it?
> >
> >How can I tell what clients are currently using the tables?
> >
> >Thanks,
> >
> >Jeff McKeon
> >
> >  
> >
> 
> 
> 

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

RE: GROUP BY ORDER BY

[Gary Broughton]

Hi

Many thanks to one and all for your time and assistance with my
question. I used the 'AS cnt' method and it works brilliantly.  Simple
isn't it? :-)

Cheers
Gary

-Original Message-
From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] 
Sent: 24 July 2003 14:01
To: [EMAIL PROTECTED]
Subject: Re: GROUP BY ORDER BY

"Gary Broughton" <[EMAIL PROTECTED]> wrote:
> 
> I wonder if someone could help with what I assume is a simple query
> using GROUP and/or ORDER statements (something I struggle to get to
> grips with).  I am trying to get a list of users who have posted to a
> forum by number of posts descending, but am unable to find the right
> statement to do it.
> 
> 
> 
> At the moment I have: "select count(*), user_id FROM messages WHERE
> forum_id = 294 GROUP BY user_id" . which gets me what I want, but in a
> random user order.
> 
> 
> 
> I have looked through the MySQL documentation, but have been unable to
> hit on the combination of functions needed to get what I need (which
is
> effectively 'ORDER BY count(*) DESC').


SELECT COUNT(*) AS cnt, user_id FROM messages WHERE forum_id = 294 GROUP
BY user_id ORDER BY cnt DESC


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





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


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

RE: Why is this query not working?

[Susan Ator]

Thank you. Changing it to:

$max_esn = mysql_result($max_esn_result,$i);

did the trick.

sa

-Original Message-
From: Jennifer Goodie [mailto:[EMAIL PROTECTED]
Sent: Wednesday, July 23, 2003 6:33 PM
To: Susan Ator; [EMAIL PROTECTED]
Subject: RE: Why is this query not working?


> I am running php 4.2.2 and mysql 3.23.54.

This is a PHP question, not mySQL.

> This is my query:
>
>   $sql = "SELECT MAX(esn) FROM address";  // line 37
>   $max_esn_result = mysql_query($sql) or print mysql_error();  // line
> 38
>   $max_esn = mysql_result($max_esn_result,$i,"esn");  // line 39

What is the value of $i?  Are you sure you want to jump to row $i of your
result set?

>
> This is the error I get:
>
>   esn not found in MySQL result index 5 in
> /var/www/html/address_entry.php on line 39
>

I think since you are selecting MAX(esn) is doesn't get returned as esn, it
gets returned as MAX(esn), I could be wrong.  A simple fix would be to get
rid of the third argument you are passing to mysql_result since your query
is only returning one field you do not need an offset or name.  Or you can
change the query to  "SELECT MAX(esn) as max_esn FROM address"; and then use
max_esn as your offset/fieldname.


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

Grant Privileges

[Charles Cantrell]

I have recently set up mySQL on a Mandrake release of Linux (Version 7 of
Mandrake, I believe), using the binary 4.0.13 standard release.

The set up and start up all were normal, as far as I could tell, with no
warnings or error messages.

In nearly all respects, the database appears to be running as expected. I
have the book "PHP and mySQL Web Development" by Luke Welling and Laura
Thomson, and have been working through the examples there.

The question I have is about the GRANT command, and the apparent results
there. I don't know if my problem is one of understanding what the results
should be, or if I have another problem.

I am attempting to set up an administrative user, that has the same
privileges as root. I have been able to set up a user that appears to have
all privileges, with the exception of GRANT privileges to other users. This
user can create new database tables, insert records, modify them, delete
them and so on.

But, when this user tries to GRANT privileges on any database, even ones it
has created, an 'access denied' error is generated. I have pasted a session
below. (I hope that wrapping does not make this difficult to read.)

mysql> show grants for chc;
+---
---+
| Grants for [EMAIL PROTECTED]
|
+---
---+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX,
ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO 'chc|
| GRANT ALL PRIVILEGES ON `mysql`.`aec2003` TO 'chc'@'%' WITH GRANT OPTION
|
| GRANT ALL PRIVILEGES ON `mysql`.`mysql` TO 'chc'@'%' WITH GRANT OPTION
|
+---
---+
3 rows in set (0.01 sec)

mysql> GRANT ALL
-> ON *.*
-> TO fred IDENTIFIED BY 'classy'
-> WITH GRANT OPTION;
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
mysql> 

Now, if I change this GRANT command as follows:

mysql> GRANT ALL
-> ON aec2003
-> TO fred IDENTIFIED BY 'classy'
-> WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)  

Then, as shown in the results, the query appears to function as desired.

However, when I then attempt to log in as fred, again access is denied:

[EMAIL PROTECTED] chc]$
[EMAIL PROTECTED] chc]$ mysql -u fred -p
Enter password: **
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
[EMAIL PROTECTED] chc]$  

even though when we check the GRANT table, the results are:

mysql> show grants for fred;
+---
-+
| Grants for [EMAIL PROTECTED]
|
+---
-+
| GRANT USAGE ON *.* TO 'fred'@'%' IDENTIFIED BY PASSWORD '677e059523c257eb'
|
| GRANT ALL PRIVILEGES ON `mysql`.`aec2003` TO 'fred'@'%' WITH GRANT OPTION
|
+---
-+
2 rows in set (0.00 sec)  

I am obviously missing some part of the process, but I am not sure what it
is. Can you explain the piece I am missing? I would appreciate it very much.
Thanks.

Charles Cantrell




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

Re[4]: Can run two versions of MySQL in Windows 2000?

[miguel solórzano]

At 14:47 24/7/2003 +0200, Stefan Hinz wrote:
Hi,
> (I think I messed up here.  I decided to start from scratch, I removed
> "MySQL Servers and Clients 4.0.13" from the program list (as it appears
> when I run the "setup.exe" it installs MySQL software in Windows) and
> deleted all the folders (which included the old "c:\mysql\" directory
> with mysqld in the \bin folder) before performing the remove
> instructions below.  So now I can't get to the correct "mysqld" service
> to remove it.  It also appears that I can't remove it in windows as I
> don't see a "remove" or "delete" option in the Services area where you
> directed me.  Will that be "stuck" there, now?  MySQL and mysqld2 still
> appear in the services list.)
I had the same kind of problem before. I don't know why that is so,
but Windows doesn't provide a remove option in the Services Manager
window. You can, however, edit the properties of a service and set it
to "Disabled". After re-booting the system, that service should have
gone.
If it's still there, you can only remove it by hacking the Windows
registry (Start > Run > regedit), looking for "mysql..." entries,
delete them, and reboot. Maybe there's a more proper way of doing
this, and if someone on this list knows, I'd be interested in how that
would look like.
(And, yes, I can hear all you Unix guys on this list chuckling.)
Yes the currently installer doesn't remove the service during
the un-install process. This deficiency should be fixed in the
new installer which is developed by Mark ( I am cc this for him
for to me correct if I am wrong).
However in the today behavior I don't recommend to play with the
registry. The situation is:
- Before to un-install remove the service using the server binary
  with mysqld --remove or mysqld --remove service-name.
- If you don't have anymore any server for to perform the remove
  service, you don't need to reboot, just re-install the new
  stuff and do the service remove.
--
Regards,
For technical support contracts, visit https://order.mysql.com/
Are you MySQL certified?, http://www.mysql.com/certification/
Miguel Angel Solórzano <[EMAIL PROTECTED]>
São Paulo - Brazil
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Myisachk

[gerald_clark]

Shutdown mysqld before running myisamcheck,
or use check/repair table.
Jeff McKeon wrote:

Hello,

When I run a script to issue myismchk against the tables in my database,
I get the following..
[EMAIL PROTECTED] scripts]# ./check_mysql_tables
myisamchk: MyISAM file /var/lib/mysql/telaurus/Admin.MYI
myisamchk: warning: 1 clients is using or hasn't closed the table
properly
MyISAM-table '/var/lib/mysql/telaurus/Admin.MYI' is usable but should be
fixed
myisamchk: MyISAM file /var/lib/mysql/telaurus/Customer.MYI
myisamchk: warning: 1 clients is using or hasn't closed the table
properly
MyISAM-table '/var/lib/mysql/telaurus/Customer.MYI' is usable but should
be fixed
This db is ver 3.23 and replicates from another db.

What causes the tables to be "1 clients is using or hasn't closed the
table properly"
I do have PHP web sites that pull data from this db, am I perhaps not
closing the tables after I access them from the PHP with select queries?
If that's the case, why are tables I never touch with the website
getting this error, does replication have something to do with it?
How can I tell what clients are currently using the tables?

Thanks,

Jeff McKeon

 



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

Re[4]: Can run two versions of MySQL in Windows 2000?

[Stefan Hinz]

Degan,

> On Windows 2000, you can open the Services Manager window as follows:

Start >> Settings > Control Panel > Administration > Services

> In that window you see a list of Windows services, and you should find
> two MySQL-related services running.

> (I think I messed up here.  I decided to start from scratch, I removed
> "MySQL Servers and Clients 4.0.13" from the program list (as it appears
> when I run the "setup.exe" it installs MySQL software in Windows) and
> deleted all the folders (which included the old "c:\mysql\" directory
> with mysqld in the \bin folder) before performing the remove
> instructions below.  So now I can't get to the correct "mysqld" service
> to remove it.  It also appears that I can't remove it in windows as I
> don't see a "remove" or "delete" option in the Services area where you
> directed me.  Will that be "stuck" there, now?  MySQL and mysqld2 still
> appear in the services list.)

I had the same kind of problem before. I don't know why that is so,
but Windows doesn't provide a remove option in the Services Manager
window. You can, however, edit the properties of a service and set it
to "Disabled". After re-booting the system, that service should have
gone.

If it's still there, you can only remove it by hacking the Windows
registry (Start > Run > regedit), looking for "mysql..." entries,
delete them, and reboot. Maybe there's a more proper way of doing
this, and if someone on this list knows, I'd be interested in how that
would look like.

(And, yes, I can hear all you Unix guys on this list chuckling.)

Regards,
--
  Stefan Hinz <[EMAIL PROTECTED]>
  iConnect GmbH 
  Heesestr. 6, 12169 Berlin (Germany)
  Telefon: +49 30 7970948-0  Fax: +49 30 7970948-3

[filter fodder: sql, mysql, query]


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

Re: GROUP BY ORDER BY

[Victoria Reznichenko]

"Gary Broughton" <[EMAIL PROTECTED]> wrote:
> 
> I wonder if someone could help with what I assume is a simple query
> using GROUP and/or ORDER statements (something I struggle to get to
> grips with).  I am trying to get a list of users who have posted to a
> forum by number of posts descending, but am unable to find the right
> statement to do it.
> 
> 
> 
> At the moment I have: "select count(*), user_id FROM messages WHERE
> forum_id = 294 GROUP BY user_id" . which gets me what I want, but in a
> random user order.
> 
> 
> 
> I have looked through the MySQL documentation, but have been unable to
> hit on the combination of functions needed to get what I need (which is
> effectively 'ORDER BY count(*) DESC').


SELECT COUNT(*) AS cnt, user_id FROM messages WHERE forum_id = 294 GROUP BY user_id 
ORDER BY cnt DESC


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





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

Re: GROUP BY ORDER BY

[John Wunderly]

try this: 

order by 1 desc 

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

Myisachk

[Jeff McKeon]

Hello,

When I run a script to issue myismchk against the tables in my database,
I get the following..

[EMAIL PROTECTED] scripts]# ./check_mysql_tables
myisamchk: MyISAM file /var/lib/mysql/telaurus/Admin.MYI
myisamchk: warning: 1 clients is using or hasn't closed the table
properly
MyISAM-table '/var/lib/mysql/telaurus/Admin.MYI' is usable but should be
fixed
myisamchk: MyISAM file /var/lib/mysql/telaurus/Customer.MYI
myisamchk: warning: 1 clients is using or hasn't closed the table
properly
MyISAM-table '/var/lib/mysql/telaurus/Customer.MYI' is usable but should
be fixed

This db is ver 3.23 and replicates from another db.

What causes the tables to be "1 clients is using or hasn't closed the
table properly"

I do have PHP web sites that pull data from this db, am I perhaps not
closing the tables after I access them from the PHP with select queries?
If that's the case, why are tables I never touch with the website
getting this error, does replication have something to do with it?

How can I tell what clients are currently using the tables?

Thanks,

Jeff McKeon

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

InnoDB locking: Different behavior on 3.23.55 and 4.0.13

[Mikhail Entaltsev]

Hi

I have 2 MySQL  servers: 
Server1 is 3.23.55-max-log
Server2 is 4.0.13-standard-log

Let's assume that we have 2 connections (Conn1 and Conn2) and table test:

CREATE TABLE `test` (
  `id` int(3) NOT NULL auto_increment,
  `name` char(10) default '',
  PRIMARY KEY  (`id`)
) TYPE=InnoDB;

and put some data in it:

insert into test (id, name) values (1, 'cat');
insert into test (id, name) values (2, 'dog');
insert into test (id, name) values (3, 'bird');



I execute queries in the order:

Conn1: 
LOCK TABLES test WRITE;

then 

Conn2:
select * from test;

On the Server1 Conn2 is locked and it will be locked even more 
than innodb_lock_wait_timeout (that is not correct, right?).
But on the Server2 Conn2 returns results immediately.

I guess that Server2 is wrong. What do you think?

Thanks,
Mikhail.

RE: impossible to start mysql

[Matthew Smith]

Does the user you wish to run mysqld as have owner read write to 
/var/lib/mysql and its sub directories?

(if you are not sure what I mean, can we see your my.cnf file
and do a 'ls -la /var/lib/mysql')

Matthew

-Original Message-
From: antoine druon [mailto:[EMAIL PROTECTED]
Sent: 24 July 2003 12:45
To: [EMAIL PROTECTED]
Subject: impossible to start mysql


I try to install mysql 3.23 on mandrake 9.0 but I have somme problems

first I have this problem /usr/share/rpm-helper/add-service: line 27: 
[: : integer expression expected a the end of install 
i saw for mandrake 9.1 a patch so i modified my add-service in rpm-
helper
but after that a the end of install i receive this 
message /usr/sbin/mysqld: Shutdown Complete
and impossible t start mysql with safe_mysqld
error message is : shell-init: could not get current directory: 
getcwd: cannot access parent directories: No such file or directory
pwd: could not get current directory: getcwd: cannot access parent 
directories: No such file or directory
Starting mysqld daemon with databases from /var/lib/mysql
030724 13:44:37  mysqld ended

please help me 
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]

impossible to start mysql

[antoine druon]

I try to install mysql 3.23 on mandrake 9.0 but I have somme problems

first I have this problem /usr/share/rpm-helper/add-service: line 27: 
[: : integer expression expected a the end of install 
i saw for mandrake 9.1 a patch so i modified my add-service in rpm-
helper
but after that a the end of install i receive this 
message /usr/sbin/mysqld: Shutdown Complete
and impossible t start mysql with safe_mysqld
error message is : shell-init: could not get current directory: 
getcwd: cannot access parent directories: No such file or directory
pwd: could not get current directory: getcwd: cannot access parent 
directories: No such file or directory
Starting mysqld daemon with databases from /var/lib/mysql
030724 13:44:37  mysqld ended

please help me 
thanks


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

GROUP BY ORDER BY

[Gary Broughton]

Hi

 

I wonder if someone could help with what I assume is a simple query
using GROUP and/or ORDER statements (something I struggle to get to
grips with).  I am trying to get a list of users who have posted to a
forum by number of posts descending, but am unable to find the right
statement to do it.

 

At the moment I have: "select count(*), user_id FROM messages WHERE
forum_id = 294 GROUP BY user_id" . which gets me what I want, but in a
random user order.

 

I have looked through the MySQL documentation, but have been unable to
hit on the combination of functions needed to get what I need (which is
effectively 'ORDER BY count(*) DESC').

Can anybody help?

 

Many thanks

Gary

RE: Re[2]: Can run two versions of MySQL in Windows 2000?

[Degan, George E, JR, MGSVC]

-Original Message-
From: Stefan Hinz [mailto:[EMAIL PROTECTED]
Sent: Wednesday, July 23, 2003 5:15 PM
To: Degan, George E, JR, MGSVC
Cc: Paul DuBois; [EMAIL PROTECTED]
Subject: Re[2]: Can run two versions of MySQL in Windows 2000?


Degan,

jumping in to try and solve some problems that look pretty obvious to
me ...

> #options for default service (mysqld2)
> (mysqld2)

It should be [mysqld2], not (mysqld2).
(thanks -- did this)
> basedir = c:\4.1 Alpha\mysql

As Paul pointed out, the blank in the path name is likely to cause
problems (see below).

> my-opts2.cnf:

> [mysqld2]
> basedir = c:\4.1 Alpha\mysql

Again, don't use a path that contains blanks.

> socket = mypipe2

As opposed to Unix, Windows doesn't know the concept of "sockets" (it
uses "named pipes" instead). Named pipes, however, might cause
problems with MySQL under Windows, so it's recommended not to use
them, but rather use tcp/ip instead (that is why named pipes are
disabled by default). You don't have to specify anything to use tcp/ip
-- it's the default under Windows. (The above line in your
configuration file is syntactically okay, but I'd recommend to delete
it for the mentioned reasons. For MySQL 4.1, that should be
"protocol=mypipe2", anyway.)

(thanks -- removed the two lines in the [mysqld] section:
"enable-named-pipes" and "socket = "mypipe1"
   in the [mysqld2] section I removed "enable-named-pipes" and
amended second line to "protocol = mypipe2"
   Did I understand you correctly?)

> mysqld --defaults-file=C:\my-opts1.cnf
> response:  mysqld:  ERROR: unknown option '--enable-named-pipe'

For the above given reasons, you shouldn't use named pipes. Get rid of
the appropriate lines in your configuration files, and you'll get rid
of that problem.

(amended the Opts#.cnf files similar to the my.cnf file above)

> 030723 15:09:02  Error message file
'C:\mysql\share\english\errmsg.sys'
> had only 237 error messages, but it should contain at least 255 error
> messages.  Check that the above file is the right version for this
> program!  030723 15:09:02 when I invoke it from the folder of the old
> version. and:
> mysqld-max:  Can't change dir to 'C:\4.1 Alpha\mysql\data\'
(Errcode:2)

Well, that error message says it, doesn't it? Avoid having blanks in
your pathnames.

(changed "live" folder to c:\mysql-4.0.13 and the "alpha" folder to
c:\mysql-4.1.0\)

> Okay, that looks good.  When you open the Services Manager, I assume
> it shows lines for services named "MySQL" and "mysqld2" and that they
> both have a status showing them to be running?

> (Sorry, the "Services Manager"? I'm not sure what that is.)

On Windows 2000, you can open the Services Manager window as follows:

Start > Settings > Control Panel > Administration > Services

In that window you see a list of Windows services, and you should find
two MySQL-related services running.

(I think I messed up here.  I decided to start from scratch, I removed
"MySQL Servers and Clients 4.0.13" from the program list (as it appears
when I run the "setup.exe" it installs MySQL software in Windows) and
deleted all the folders (which included the old "c:\mysql\" directory
with mysqld in the \bin folder) before performing the remove
instructions below.  So now I can't get to the correct "mysqld" service
to remove it.  It also appears that I can't remove it in windows as I
don't see a "remove" or "delete" option in the Services area where you
directed me.  Will that be "stuck" there, now?  MySQL and mysqld2 still
appear in the services list.)

>>But when I attempt to invoke MySQL the response is: "ERROR 2003:
Can't
>>connect to MySQL server on 'localhost' (10061)"

> Okay.  What was the command you used here?

> (just the same as before:  "mysql" or "mysql -h localhost -u root"

> What happens if you use this command:

> mysql -h localhost -P 3308

> or this one:

> mysql -h . -S mypipe2

> (both give the following response: ERROR 2003: Can't connect to MySQL
> server on 'localhost' (10061), and I tried several Ports, 3308, 3307,
> 3306, and 3309 in this order)

All that means that the MySQL server you are trying to connect to
isn't running. You can check in the Services Manager window, now that
you know how to find that :)

>>(BTW.  When I came in today I found out that I could no longer connect
>>to 4.1.0 either.  When I left last night, I could connect to 4.1.0,
but
>>could no longer connect to 4.0.13.  According to windows explorer the
>>database files are still there, but for some reason mySQL can no
longer
>>point to them.)

Once again, check in the Services Manager window and make sure the
MySQL servers are actually running. I can only guess, but it looks as
though they are not started at system startup (Start type: automatic).

> net stop svc-name-1
> net stop svc-name-2
(by "svs-name-1 and -2" do you refer to mysqld and mysqld2?  If not, to
what services do you refer?)

> To remove them:

> mysqld --remove svc-name-1
> mysqld --remove svc-name-2
(it appears that