Re: libmysqld.lib

[Bernhard Döbler]

Hey,

libmysqld is for a long time already part of the MySQL 4.x Source-Distribution.

Bernhard

- Original Message - 
From: "Derick Smith" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Wednesday, February 19, 2003 11:35 PM
Subject: libmysqld.lib


> Hi!
> 
> I want to emebed libmysqld.lib into a DLL I am making for Visual Basic. I 
> need to make the DLL in C because it is going to perform other functions 
> (i.e I do not want to use the "Public Declare" in VB to access the DLL).
> 
> I need the libmysqld.lib library.
> 
> I could use LoadLibrary to load it dynamically, but I think this would be 
> more work.
> 
> Does anyone know where I can get it?
> Does anyone know when it will be available on the mysql.com website?
> 
> Does anyone know when the source code for the libmysqld.dll will be 
> distributed (Windows version)? (I thought mysql was open source?)
> 
> Thanks
> Eric
> 
> 
> _
> STOP MORE SPAM with the new MSN 8 and get 2 months FREE*   
> http://join.msn.com/?page=features/junkmail
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: MySQL on Mandrake 9 with EXT3 File system... Max Size?

[Veysel Harun Sahin]

You can see your max table size at the row "Max datafile length:
4294967294". This means that your UsedData table's max size is 4G. If 
you want to change it you have to use alter table command with the 
option max_rows. For example "ALTER TABLE UsedData MAX_ROWS = 100".

[EMAIL PROTECTED] wrote:

Hi, this is my first post here, I hope it's easily answered

I've got Mandrake Linux 9, with the ext3 file system
What is the absolute maximum table size I can have?
And what do I need to change to achive that?

I've spent half the night googling, and couldn't find a real answer
so now I throw my helpless soul onto those who use it more then
I do (c:

Below is information about the table.

MyISAM file: UsedData
Record format:   Packed
Character set:   latin1 (8)
File-version:1
Creation time:   2003-02-17 14:09:19
Recover time:2003-02-20  0:28:44
Status:  checked
Data records:17530  Deleted blocks: 0
Datafile parts:  17530  Deleted data:   0
Datafile pointer (bytes):4  Keyfile pointer (bytes):3
Datafile length:   1267264  Keyfile length:  1024
Max datafile length:4294967294  Max keyfile length:   17179868159
Recordlength:   73

Any help would be greatly appreciated.

Thanks

Shannon

To bypass the anti spam filters: sql


-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail 
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


--

Veysel Harun Sahin
[EMAIL PROTECTED]




-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Need help in Mysql

[saravanan saravanan]

Dear sir
I am using mysql for my project.I am finding
problems of using FOREIGN KEY and STORED procedures in
version 4.0.please help me and send the details as
earlier as possible
by
Saravanan

__
Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips, more
http://taxes.yahoo.com/

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: purging of relay logs?

[Jeremy Zawodny]

On Thu, Feb 20, 2003 at 08:00:32AM +0100, Rafal Jank wrote:
> Is this a way to do it? I've just upgraded mysql to vesion 4.0.10 and found
> out that there are these new files on the slave. How can I rotate them?

They should automatically vanish.  MySQL removes them when they're no
longer needed.
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

MySQL 4.0.8: up 16 days, processed 516,944,815 queries (355/sec. avg)

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

purging of relay logs?

[Rafal Jank]

Is this a way to do it? I've just upgraded mysql to vesion 4.0.10 and found
out that there are these new files on the slave. How can I rotate them?


sql and so on...
-- 
_/_/  _/_/_/  - Rafał Jank [EMAIL PROTECTED] -
 _/  _/  _/  _/   _/ Wirtualna Polska SA   http://www.wp.pl 
  _/_/_/_/  _/_/_/ul. Traugutta 115c, 80-237 Gdansk, tel/fax. (58)5215625
   _/  _/  _/ ==*  http://szukaj.wp.pl *==--

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

SSL connection from client

[Maciej Bobrowski]

Hi,

I have installed the 4.0.10-gamma with support from the openssl v. 0.9.7
on the Debian Woody:

mysql>status
...
SSL:Cipher in use is DHE-RSA-AES256-SHA
...

mysql>show variables like '%openssl%';
+---+---+
| Variable_name | Value |
+---+---+
| have_openssl  | YES   |
+---+---+

O.K. when I want to connect to mysql from console I simply write
mysql --defaults-file=/./openssl/my.cnf -u root -p test
and I can "log on" (in the my.cnf there are definitions of the keys and
certificates for [client] and for [mysqld]):

But I would like to:

 - a client from an IP use SSL connection. Now when I use for example
   simple php program, it needs only the host,user,password and
   database_name. There is not any SSL connection between the user from
   a client and my mysql server with support from SSL.
   I have also a java program, which also doesn't need anything more then
   host,user,password and database_name to connect to data base.
   And I have a perl program, which connect to mysql through DBI,
   and it doesn't need any special things to connect to mysql
   server and make whatever it wants to.
   How can I change it? What the client has to know and/or have?
   Maybe something in the 'user' table in the mysql database and
   an access to files with the keys and certificates?
   In the 'user' table there are ssl_type and ssl_cipher columns.
   Maybe I should change their values for a user?, and then what? ..

 - watch how many SSL connections were established.

O.K. Can anybody help me?

Best regards,

Maciej Bobrowski


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Searching should be easy ?

[John Berman]

Hi

My host used mysql v3.23.28 and I use ASP for data access on my
genealogical database

I have created a fulltext index on 3 fields, surname,groomsurname and
bride surname

And I retrieve an exact name using the following:

SQL = "SELECT * FROM global  WHERE MATCH (SURNAME, GROOMSURNAME,
BRIDESURNAME) AGAINST (' "& globsurname & " ') "
With globsurname coming from my search form - this works fine for an
exact match

Can I use pattern matching against the text collection along the lines
of

SQL = "SELECT * FROM global  WHERE MATCH (SURNAME, GROOMSURNAME,
BRIDESURNAME) Like (' "& globsurname & "% ') "

So it will pick all those rows starting with say A or B, I have spent
hours playing with the syntax so I hope its possible ?


Regards

John Berman







-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Is SQL right for me?

[Marijka Walker]

I'm need to create a database for the first time in 10 years and the wizard
aspect of Access 2002 doesn't work for me - it's too much "help" and slows
me down! I'm used to creating databases and reports almost from scratch with
dBase IV and Paradox, and easily learn software by reading manuals, so is
SQL the answer? Please note that I am strictly pc-based and have never
programmed beyond batch files in DOS.

Since I don't have the software, I didn't join the list, so please mail
answers to [EMAIL PROTECTED] Thanks in advance for your advice!


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: How to determine foreign keys? Meta data?

[Karam Chand]

If you are using Windows, a GUI client like SQLyog
would be of great help. It is free, and has neat
interface to display / manage relationships.

--- Jeff Epstein <[EMAIL PROTECTED]> wrote:
> Hello all.  
> 
> I've only been using MySQL for a few days, but I'm
> experienced with
> Oracle.  So consider this a sort-of-newbie question.
> 
> Is there any way to determine what foreign keys
> exist on a table?  I
> have tried "myisamchk", "describe", "show index",
> "show create table",
> all to no avail.
> 
> Is there complete access to the meta-data?
> 
> (I'm using version 3.23.55, by the way.)
> 
> 
> Thanks!  :'  )
> 
> =
> :'  )
> Jeff Epstein
> [EMAIL PROTECTED]
> http://www.jeffyjeffy.com
> 
> 
> ..
> 
> __
> Do you Yahoo!?
> Yahoo! Shopping - Send Flowers for Valentine's Day
> http://shopping.yahoo.com
> 
>
-
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list
> archive)
> 
> To request this thread, e-mail
> <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
>
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try:
> http://lists.mysql.com/php/unsubscribe.php
> 


__
Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips, more
http://taxes.yahoo.com/

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Is this query possible...

[Jonathan Villa]

Well, I thought I could figure it out but I guess I couldn't.

This is exactly what I want to do...
//Using Access now, but when I use MySQL, I can just get the last insert
id.

$itemID = $_POST['item_id'];
$itemQty = $_POST['item_qty'];
$itemPrice = $_POST['price'];
$orderID = Will be a value taken from an auto_increment field in another
table. 
//I'm using PHP and from I have read and have been told, there is no
odbc function to get the last insert id, so I'm stuck doing this.

INSERT INTO order_history (item_id, item_qty, item_price, order_id)
VALUES ($itemID, $itemQty, $itemPrice, $orderID) {Is this where I would
do a select and if so how?}

So, as you see, I have a combination of values, 3 vars from a form
submission, and one value already stored in the database.

I know that I could run 2 queries, but that is what I am trying to
avoid.

Thanks in advance.
 
---> Jonathan
 
 
 

-Original Message-
From: KH Chiu [mailto:[EMAIL PROTECTED]] 
Sent: Wednesday, February 19, 2003 8:52 PM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: Is this query possible...

Yes. You can use INSERT INTO  select from ..

Regards,

> All, 
> 
> Is this query possible to do:
> 
> I am going to select an id from a table
> 
> SELECT order_id FROM orders WHERE order_start = 1324
> 
> I will then use the order_id from this query and insert it as well as
> some others values I have into another table.
> 
> So in summary, I want to perform a SELECT and INSERT in the same
> database query.
> 
> Can I do it?
> 
> If this matters, I will be doing this in 2 different applications. 1
> database is MySQL, and the other is MS Access.
> 
> -jonathan
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


--
Yours,
KH Chiu
C&A Computer Consultants Ltd.
Tel: 3104 2070 Fax: 3010 0896
Email: [EMAIL PROTECTED]
Website: www.caconsultant.com



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: varchar and java string in sql query ?

[KH Chiu]

Hi,

I am not fimilar with jdbc. But as a general prinple, you should quote your 
string expl. and it is nearly a must for all language to handle SQL query. 
May be you should try str="'id'" instead of str="id". In some language, you 
must use str = "\'id\'". 

Please check jdbc docs.

Regards,

> I and using mysql-jdbc to do some program.
> 
> I can insert record to my db by following code:
> 
> -
> stmt.executeUpdate("insert into apidbusers values('id',
> 'jp','zhu','em1','jian180')");
> ---
> but
> if i use 
> --
> String str="id";
> stmt.executeUpdate("insert into apidbusers values(str, 'jp',
> 'zhu','em1','jtan180')");
> -
> it fails.
> why? how can i use java java string to insert a record?
> 
> Thanks 
> 
> 
> Jianping Zhu
> Department of Computer Science
> Univerity of Georgia 
> Athens, GA 30602
> Tel 706 5423900
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


--
Yours,
KH Chiu
C&A Computer Consultants Ltd.
Tel: 3104 2070 Fax: 3010 0896
Email: [EMAIL PROTECTED]
Website: www.caconsultant.com


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

varchar and java string in sql query ?

[Jianping Zhu]

I and using mysql-jdbc to do some program.

I can insert record to my db by following code:

-
stmt.executeUpdate("insert into apidbusers values('id',
'jp','zhu','em1','jian180')");
---
but
if i use 
--
String str="id";
stmt.executeUpdate("insert into apidbusers values(str, 'jp',
'zhu','em1','jtan180')");
-
it fails.
why? how can i use java java string to insert a record?

Thanks 





Jianping Zhu
Department of Computer Science
Univerity of Georgia 
Athens, GA 30602
Tel 706 5423900




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Is this query possible...

[Jennifer Goodie]

>Yes. You can use INSERT INTO  select from ..
>> If this matters, I will be doing this in 2 different applications. 1
>> database is MySQL, and the other is MS Access.

Not if the two tables are on different database servers. But maybe I'm
reading that wrong and the application is just being developed to run on two
different instances.

-Original Message-
From: KH Chiu [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, February 19, 2003 6:52 PM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: Is this query possible...


Yes. You can use INSERT INTO  select from ..

Regards,

> All,
>
> Is this query possible to do:
>
> I am going to select an id from a table
>
> SELECT order_id FROM orders WHERE order_start = 1324
>
> I will then use the order_id from this query and insert it as well as
> some others values I have into another table.
>
> So in summary, I want to perform a SELECT and INSERT in the same
> database query.
>
> Can I do it?
>
> If this matters, I will be doing this in 2 different applications. 1
> database is MySQL, and the other is MS Access.
>
> -jonathan


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: How to determine foreign keys? Meta data?

[KH Chiu]

Dear Jeff,

There are many ways to lookup MySQL metadata. I was used to familiar with 
them. Unluckily, I am 'spoiled' by phpMyAdmin recently.

I strongly recommend you to have a try with phpMyAdmin.

Regards, 

> Hello all.  
> 
> I've only been using MySQL for a few days, but I'm experienced with
> Oracle.  So consider this a sort-of-newbie question.
> 
> Is there any way to determine what foreign keys exist on a table?  I
> have tried "myisamchk", "describe", "show index", "show create 
> table", all to no avail.
> 
> Is there complete access to the meta-data?
> 
> (I'm using version 3.23.55, by the way.)
> 
> Thanks!  :'  )
> 
> =
> :'  )
> Jeff Epstein
> [EMAIL PROTECTED]
> http://www.jeffyjeffy.com
> 
> ..
> 
> __
> Do you Yahoo!?
> Yahoo! Shopping - Send Flowers for Valentine's Day
> http://shopping.yahoo.com
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


--
Yours,
KH Chiu
C&A Computer Consultants Ltd.
Tel: 3104 2070 Fax: 3010 0896
Email: [EMAIL PROTECTED]
Website: www.caconsultant.com


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Is this query possible...

[KH Chiu]

Yes. You can use INSERT INTO  select from ..

Regards,

> All, 
> 
> Is this query possible to do:
> 
> I am going to select an id from a table
> 
> SELECT order_id FROM orders WHERE order_start = 1324
> 
> I will then use the order_id from this query and insert it as well as
> some others values I have into another table.
> 
> So in summary, I want to perform a SELECT and INSERT in the same
> database query.
> 
> Can I do it?
> 
> If this matters, I will be doing this in 2 different applications. 1
> database is MySQL, and the other is MS Access.
> 
> -jonathan
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


--
Yours,
KH Chiu
C&A Computer Consultants Ltd.
Tel: 3104 2070 Fax: 3010 0896
Email: [EMAIL PROTECTED]
Website: www.caconsultant.com


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

How to determine foreign keys? Meta data?

[Jeff Epstein]

Hello all.  

I've only been using MySQL for a few days, but I'm experienced with
Oracle.  So consider this a sort-of-newbie question.

Is there any way to determine what foreign keys exist on a table?  I
have tried "myisamchk", "describe", "show index", "show create table",
all to no avail.

Is there complete access to the meta-data?

(I'm using version 3.23.55, by the way.)


Thanks!  :'  )

=
:'  )
Jeff Epstein
[EMAIL PROTECTED]
http://www.jeffyjeffy.com


..

__
Do you Yahoo!?
Yahoo! Shopping - Send Flowers for Valentine's Day
http://shopping.yahoo.com

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

MySQL on Mandrake 9 with EXT3 File system... Max Size?

[Shannon Wynter]

Hi, this is my first post here, I hope it's easily answered

I've got Mandrake Linux 9, with the ext3 file system
What is the absolute maximum table size I can have?
And what do I need to change to achive that?

I've spent half the night googling, and couldn't find a real answer
so now I throw my helpless soul onto those who use it more then
I do (c:

Below is information about the table.

MyISAM file: UsedData
Record format:   Packed
Character set:   latin1 (8)
File-version:1
Creation time:   2003-02-17 14:09:19
Recover time:2003-02-20  0:28:44
Status:  checked
Data records:17530  Deleted blocks: 0
Datafile parts:  17530  Deleted data:   0
Datafile pointer (bytes):4  Keyfile pointer (bytes):3
Datafile length:   1267264  Keyfile length:  1024
Max datafile length:4294967294  Max keyfile length:   17179868159
Recordlength:   73

Any help would be greatly appreciated.

Thanks

Shannon

To bypass the anti spam filters: sql


-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Is this query possible...

[Jonathan Villa]

All, 

Is this query possible to do:

I am going to select an id from a table

SELECT order_id FROM orders WHERE order_start = 1324

I will then use the order_id from this query and insert it as well as
some others values I have into another table.

So in summary, I want to perform a SELECT and INSERT in the same
database query.

Can I do it?

If this matters, I will be doing this in 2 different applications. 1
database is MySQL, and the other is MS Access.

-jonathan


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Joining multiple tables results not complete

[KH Chiu]

You should use LEFT JOIN.

Regards,

> I am perplexed trying to retrieve all the rows I need in the query 
> I'm doing.
> 
> I have a table with keeping track of people using ID numbers as the primary
> key and another table with coupon numbers and a reference to the 
> first table.  But not all the second table is going to have 
> correlating matches to the first table.  So, in other words not all 
> people have a coupon number entered into the database.
> 
> I run the query:
> 
> SELECT table1.id, table2.coupon FROM table1, table2 WHERE
> table1.id=table2.person_id;
> 
> I expect back a report of the 213 rows in table1 and their matching 
> coupon # if they have one.  Instead, I get 174 rows and THEIR 
> matching coupons but no results from the others.  I want to write a 
> query that will return the id's from table1 and the table2 coupon 
> regardless of whether the table2.person_id matches table1.id.
> 
> How do I do this?
> 
> Thanks,
> Dean
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


--
Yours,
KH Chiu
C&A Computer Consultants Ltd.
Tel: 3104 2070 Fax: 3010 0896
Email: [EMAIL PROTECTED]
Website: www.caconsultant.com


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Lost connection to MySQL server during query

[KH Chiu]

Assume the ip address of your remote machine is 192.168.0.88

You should perform a

nslookup 192.168.0.88

on your LOCAL machine that hold the mysqld

If it cannot resolve into a symbolic name then you will get into trouble.

Add an entry to /etc/hosts or your DNS will solve the problem.

Regards,

> Hi All
> I just installed a RedHat Linux 8.0 box with
> mysql-server-3.23.52-3
> mysql-3.23.52-3
> mysql-devel-3.23.52-3
> 
> Changed the password for root using mysqladmin
> logged in using a mysql client (locally)
> and
> mysql> GRANT ALL ON *.* TO araheja@'%' IDENTIFIED BY 'amanraheja';
> 
> I DID THE SAME STEPS ON ANOTHER BOX WITH Linux Slackware box with mysql
> 3.23.39
> 
> When I try to login using a remote client in both the servers, 
> Slackware box allows me but RedHat BOX gives me the following error
> 
> "Lost connection to MySQL server during query"
> 
> I also downloaded and installed
> mysql-server-3.23.54a-1
> mysql-3.23.54a-1
> mysql-devel-3.23.54a-1
> 
> Still the same problem.
> Any suggestions?
> 
> Thank you in advance
> Regards
> 
> --
> Aman Raheja
> AGF Inc.
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


--
Yours,
KH Chiu
C&A Computer Consultants Ltd.
Tel: 3104 2070 Fax: 3010 0896
Email: [EMAIL PROTECTED]
Website: www.caconsultant.com


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Problem with replication and corrupting tables

[Steven Roussey]

Hi,

And fixed.

Sorry for the waste of time. Only 4 days before I was set to replace the
disk the database was on, and it is going bad. :( 

-steve-

sql,query


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Creating Tab Delimited Textfile to Load Data Into MySQL Table

[Stephen Tiano]

Stefan and Gerald--

Thanks for the input. Here's how I solved it ... I used a textfile that 
had worked for me the very first time I did such an exercise weeks ago. 
This particular textfile came on a CD with the book I was using. And by 
copying and pasting, so that all the spacing and line breaks were out of 
this particular textfile, but the actual data were new, I finally had a 
textfile that worked.

It's annoying, but I can live with this for now.

Steve Tiano

Steve,


But still only one record, that first one, come in ... at least as
evidenced by the SELECT * FROM [table_name]. What do I try next?
   

Next thing is you send the CREATE TABLE statement of your table, plus
at least two lines of your import text file (not as attachment, the
list manager would skip it).

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]
 

Re: backup script

[Sigurd Urdahl]

Jeff Mao <[EMAIL PROTECTED]> writes:

> Greetings all,
> 
> Not sure if this is the right place to ask this,...I use the following
> script on an OS X server to backup my mysql databases:
> 
>   #!/bin/sh
> DATE=`/bin/date +%Y%m%d"_"%H%M%S`;
> cd /Users/usernmae/Documents;
> /usr/bin/mysqldump -hhost -uusername -ppassword --all-databases >
> full_dump_$DATE.sql;
> exit
> 
> The script runs on from the crontab each morning at 3:30 am.  Simple
> question, I got the script from someone else, so I'm not too savvy
> with scripting.  What can I add to the script so that it automatically
> erases backups that are x weeks or days old?

Probalbly lots of ways:) You could use find to løoace files older than
some date, and erase them. Or if one week is god for you you could
change your script to do something like this:

/usr/bin/mysqldump -hhost -uusername -ppassword --all-databases >
`date +%V`/full_dump.sql;

That will give you 7 directories, one for each weekday, with a dump
each.

-sig

-- 
Sigurd Urdahl   [EMAIL PROTECTED]
Systemkonsulent | Systems consultant www.linpro.no
LIN PRO can improve the health of people who consume the eggs,
meat and milk [..] (http://www.werneragra.com/linpro.html)

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

ADO True or False Return

[Alisson Vale]

Hi,

Is there a mySQL field type that returns True or False after a
SQL Statement executed by MyODBC driver (ADO)?

Thank's a lot

Alisson



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

re: MySQL move

[Jon Miller]

I need to move the MySQL database from it's existing server to a newer server.  Is 
there anything I need to be aware of and can it be copied from it's present location 
to the new server, with changes to config file?

Thanks

Jon L. Miller, MCNE, CNS
Director/Sr Systems Consultant
MMT Networks Pty Ltd
http://www.mmtnetworks.com.au

"I don't know the key to success, but the key to failure
 is trying to please everybody." -Bill Cosby





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Problem with replication and corrupting tables

[Steven Roussey]

Hi,

mysqlbinlog -j   | mysql

works fine. I used -o instead of -j before. So I answered my last
question. When doing this:

mysqlbinlog -j   | more

I see that it had advanced to the query after the one with the problem
in the trace file. In fact, the query succeeded and was there after a
REPAIR TABLE .. USE_FRM.

Now that I got the above to work, I ran it.

And I found a surprising result (to me): It still failed.

So the problem is not with the replication code per se.

So maybe I can make a test case



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

BUG? 3.23.55 not replicating LOAD DATA INFILE

[Jeff Kilbride]

I have one master and two slaves all running 3.23.55-max on RedHat 7.3. I've
had replication up and running smoothly for several days. Today, I decided
to try out LOAD DATA INFILE on the master. After successfully loading on the
master, I checked both slaves and neither one had the new data. There are no
errors in any of the log files and both slaves are still replicating
correctly. Only the LOAD DATA info is missing on the slaves. I have the slow
query log running on the master and the LOAD DATA statement showed up there:

# Time: 030219 17:09:02
# User@Host: root[root] @ localhost []
# Query_time: 34  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
SET timestamp=1045692542;
load data infile '/home/jschaffe/stro0002.txt'
ignore
into table list
(email);

Are there any settings in my.cnf that would affect this? The manual says:

"In 3.23, LOAD DATA INFILE will be handled properly as long as the file
still resides on the master server at the time of update propagation."

When exactly is the "time of update propagation"? I'm assuming this is
immediately, like all other replication tasks. The file still resides on the
master, right now, but the slaves still haven't received the data. Is this a
bug or am I missing something? Here are the server settings from my.cnf on
the master:


# The MySQL server
[mysqld]
port= 3306
socket  = /tmp/mysql.sock
skip-locking
log-slow-queries
skip-name-resolve
set-variable= max_connections=256
set-variable= key_buffer=384M
set-variable= max_allowed_packet=1M
set-variable= table_cache=256
set-variable= sort_buffer=2M
set-variable= record_buffer=2M
set-variable= thread_cache=8
# Try number of CPU's*2 for thread_concurrency
set-variable= thread_concurrency=2
set-variable= myisam_sort_buffer_size=64M

# Replication settings
log-bin
server-id   = 1
-

Any help is appreciated.

Thanks,
--jeff



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Using mysqlbinlog to restore blob data

[Steven Roussey]

Rick,

I am able to restore from logs that had binary data (even though the
output looked real strange and messed up the terminal window). I did
have a problem once when I tried filtering data between mysqlbinlog and
mysql. Be careful if you do that.

What version of mysql are you using?

I have no idea about the -t option for mysqlbinlog, but I'd guess it is
similar to the 'load data/table from master' command inside mysql.
(mysqlbinlog can connect to a remote server rather than using a local
file).

-steve-



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

libmysqld.lib

[Derick Smith]

Hi!

I want to emebed libmysqld.lib into a DLL I am making for Visual Basic. I 
need to make the DLL in C because it is going to perform other functions 
(i.e I do not want to use the "Public Declare" in VB to access the DLL).

I need the libmysqld.lib library.

I could use LoadLibrary to load it dynamically, but I think this would be 
more work.

Does anyone know where I can get it?
Does anyone know when it will be available on the mysql.com website?

Does anyone know when the source code for the libmysqld.dll will be 
distributed (Windows version)? (I thought mysql was open source?)

Thanks
Eric


_
STOP MORE SPAM with the new MSN 8 and get 2 months FREE*   
http://join.msn.com/?page=features/junkmail


-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Problem with replication and corrupting tables

[Steven Roussey]

Quick question: Are the binlog and relaylog files the same format?
Initial tests seem to indicate that they are the same. Can I use

mysqlbinlog -o   | mysql

to get the slave more up to date (without having the slave SQL thread
running)? I tried the above but the  from 'show slave
status' seemed way past the end of the file as it returned no results.
:(

How do I get a proper offset from which to start?

Being able to do this would isolate the issue squarely at the slave SQL
thread if the above had no issues.

Also, I uploaded a small trace file that shows the corruption. It is the
smallest I was able to make last night (about 72MB -- 6MB gzipped). It
is in the secret folder. Hopefully it will help.

-steve-



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Using mysqlbinlog to restore blob data

[Rick Arthur]

Hi,

I want to be able to use the MySQL binary logs and mysqlbinlog to restore 
data if needed.

This works fine, except when the log contains sql that is written when I 
save a binary stream to a BLOB column in a table.  I will get one of those 
messages like 'there is an error in your SQL at ...' when it tries to 
execute the insert.

When I read the log out to a file, it looks to me as if there are 
characters in the values clause of the insert statement (i.e. the binary 
stream for the BLOB column) that need to be escaped, and these are being 
interpreted as part of the sql command syntax, causing the error.

Has anyone dealt with this type of problem before?  Are there some options 
I can set on the binary logs or on mysqlbinlog to get around this problem?

Another question about mysqlbinlog:  what is the -t option used for ? I 
can't find an explanation for it, other than in the help.

TIA


Rick Arthur
Knexa.com Enterprises Inc.
519.747.1139 x 109

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Joining multiple tables results not complete

[Brent Baisley]

Well then you are looking to do a left join.

SELECT table1.id, table2.coupon FROM table1 LEFT JOIN table2 ON
table1.id=table2.person_id;

In your example, the WHERE is not only a join condition, but also a 
filter. It gives you exactly what you asked for, only those records 
whose id's match. A left join tells it to use all of the records in the 
"left" and join the other table into it. You can still add a where 
clause to filter the record if you like.

On Wednesday, February 19, 2003, at 04:28 PM, Dean Householder wrote:

I am perplexed trying to retrieve all the rows I need in the query I'm
doing.

I have a table with keeping track of people using ID numbers as the 
primary
key and another table with coupon numbers and a reference to the first
table.  But not all the second table is going to have correlating 
matches to
the first table.  So, in other words not all people have a coupon number
entered into the database.

I run the query:

SELECT table1.id, table2.coupon FROM table1, table2 WHERE
table1.id=table2.person_id;

I expect back a report of the 213 rows in table1 and their matching 
coupon #
if they have one.  Instead, I get 174 rows and THEIR matching coupons 
but no
results from the others.  I want to write a query that will return the 
id's
from table1 and the table2 coupon regardless of whether the 
table2.person_id
matches table1.id.

How do I do this?

Thanks,
Dean


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail 
[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577


-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Lost connection to MySQL server during query

[Aman Raheja]

Hi All
I just installed a RedHat Linux 8.0 box with
mysql-server-3.23.52-3
mysql-3.23.52-3
mysql-devel-3.23.52-3

Changed the password for root using mysqladmin
logged in using a mysql client (locally)
and
mysql> GRANT ALL ON *.* TO araheja@'%' IDENTIFIED BY 'amanraheja';

I DID THE SAME STEPS ON ANOTHER BOX WITH Linux Slackware box with mysql
3.23.39

When I try to login using a remote client in both the servers, Slackware box
allows me but RedHat BOX gives me the following error

"Lost connection to MySQL server during query"

I also downloaded and installed
mysql-server-3.23.54a-1
mysql-3.23.54a-1
mysql-devel-3.23.54a-1

Still the same problem.
Any suggestions?

Thank you in advance
Regards

--
Aman Raheja
AGF Inc.

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Joining multiple tables results not complete

[Dean Householder]

I am perplexed trying to retrieve all the rows I need in the query I'm
doing.

I have a table with keeping track of people using ID numbers as the primary
key and another table with coupon numbers and a reference to the first
table.  But not all the second table is going to have correlating matches to
the first table.  So, in other words not all people have a coupon number
entered into the database.

I run the query:

SELECT table1.id, table2.coupon FROM table1, table2 WHERE
table1.id=table2.person_id;

I expect back a report of the 213 rows in table1 and their matching coupon #
if they have one.  Instead, I get 174 rows and THEIR matching coupons but no
results from the others.  I want to write a query that will return the id's
from table1 and the table2 coupon regardless of whether the table2.person_id
matches table1.id.

How do I do this?

Thanks,
Dean


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Problems inserting a date...

[Stefan Hinz]

Alejandro,

> I had a problem inserting a date...which format do I need to use?...I'm
> using -mm-dd, but when I throw a query, the records display -00-00.

You can insert a date as a string or as a number. Examples:

20030219 --> today, as a number
'2003-02-19' --> today, as a string

You forgot the quotes around your date.

You have quite some more ways to insert date and time values; check it out:

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

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

[filter fodder: sql, mysql, query]


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: several newbie problems

[Stefan Hinz]

Nikola,

> When I start my mysqld deamon, I get this message: "Can't initialize InnoDB
> as 'innodb_data_file_path' is not set". I've been reading through Reference
> Manual and I realise that I have to configure my.cnf file if I want to use
> InnoDB, and the problem would dissapear, but the problem is I don't need
> InnoDB (in fact dno't know what it is :-) ). Sow, how do I supress this
> message without configuring InnoDB? (MySQL I'm using is "Ver. 3.23.41-max-
> debug for Win95/Win98 on i32")

Don't use the max version. Under Windows 95/98, this server type is called
mysqld-max.exe. Start mysqld.exe instead. In MySQL 3.23, InnoDB is not
enabled in mysqld.exe. As an alternative, you could start
mysqld-max.exe with the option --skip-innodb:

mysqld.exe --skip-innodb

Or you could configure this in your c:\windows\my.ini configuration
file:

[mysqld]
...
skip-innodb

(In this case, you have to restart the server for the changes to take
effect.)

mysql>> use nick
> Reading table information for completion of table and column names
> You can turn off this feature to get a quicker startup with -A
> My question is, what does this message mean, and how to get rid of it?

As it says: Start the command line tool like that:

mysql -A

(or: mysql --disable-auto-rehash)

On a slow machine, this can save you some seconds of your valuable
time :)

> File 'NONEXISTENT/charsets/?.conf' not found (Errcode: 2)
> Character set '#27' is not a compiled character set and is not specified
> in the 'NONEXISTENT/charsets/Index' file

Hmm. Probably your basedir variable is not set, and PHP sort of chokes
on this. When starting the server (or in your my.ini, see above), you
can set this variable (in most cases that's not necessary, but in your
case, this seems to be a source of trouble):

mysqld --basedir=c:\mysql (or whatever your installation directory is)

or

[mysqld]
...
basedir=c:/mysql (note the slash instead of the backslash)

Hope it helps, and have fun with MySQL!

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]


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re[2]: Problem reloading data...

[Stefan Hinz]

Richard,

set-variable = max_allowed_packet=16M

> Yikes!!! I knew that running "default configuration" gave you SOME
> values, but I didn't know they were so small. I've placed the 
> my-medium.cnf as "my.cnf" in /etc on the Sparc 20, and also in C:\ on 
> the W2K machine. Database access is much improved on both.

:))

> Now, if people would get off the production server so I could restart 
> MySQL... 

You don't have to wait for this. You can start mysql (the command line
tool) like this:

mysql --max-allowed-packet=16M

This would set the communication buffer to 16 MB for this session. (I
must admit that this doesn't work for me with MySQL 4.0.10 under
Win2K. It either takes the server setting which overrides the client
setting, or it sets the maximum packet size to 1 MB which is the
default. It might work under a real operating system, though ;-)

You can check whether the new setting was enabled or not by issuing
this SQL statement:

SHOW VARIABLES LIKE 'max_allowed_packet';

This will show the communication buffer size in bytes.

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]


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

replicate-rewrite-db

[Arthur Kerpician]

I have the same db on master and slave and I want to use
replicate-rewrite-db to change the replicating name of this db on the
slave.

Slave my.cnf:
---
[mysqld]
datadir=/var/lib/mysql
socket=/tmp/mysql.sock
master-host=master.company.com
master-user=repl
master-password=
master-port=3306
server-id=2
replicate-rewrite-db=mydb->slave_mydb
skip-innodb

[client]
socket=/tmp/mysql.sock

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


Master my.cnf
---
[mysqld]
log-bin
server-id=1
socket=/tmp/mysql.sock

[client]
socket=/tmp/mysql.sock

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

The slave seems to ignore replicate-rewrite-db option and tries to
create an already existing db (as I said, same db names on master and
slave), so I get an
ERROR 1007: Can't create database 'mydb'. Database exists

I put the replicate-rewrite-db option AFTER creating mydb on master, so
the bin log already contains this action. Can this be the problem?

Thanks,
Arthur


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

mysql query manipulation vs php results manipulation

[Larry Brown]

How do you determine whether or not it is faster/more efficient to run a
complicated query or to run multiple simple queries and join / manipulate
the results with PHP?

sql

Larry S. Brown
Dimension Networks, Inc.
(727) 723-8388




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

resource guide for Web database admins

[Bill Doerrfeld]

Greetings:

If you're responsible for developing or maintaining data-driven Web 
sites powered by MySQL, you may be interested in the recent release 
of the Lasso Resource Guide. This free guide contains a listing of 
various resources that aid building and serving data-driven Web 
sites. And of course, MySQL is highlighted in the guide.

It's available in both PDF and printed formats.

Details available at .

Enjoy!

Bill
--


-
Bill Doerrfeld[EMAIL PROTECTED]
Blue World Communications, Inc.   http://www.blueworld.com/
-
 Build and serve powerful data-driven Web sites
  with Lasso Studio and Lasso Professional.

-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: sql join help?

[KH Chiu]

Multiple table update is only supported from 4.04 or above. If you are using 
3.x, I think you should put the result into another table, delete MSI_List 
and rename the table to MSI_List.

I would also very interested to know whether there exist a more elegant 
solution for 3.x.

Best regards,

> sql
> 
> I have a zip code db named "MSI_Zipcodes that contains
> 
> city
> state
> zipcode (index)
> 
> I also have a db named "MSI_List" that contains
> 
> ID (index)
> email
> city(empty)
> state(empty)
> zip
> 
> My problem is:
> 
> How can i bring the proper info from "MSI_Zipcodes"(city and state)
> and enter it (city and state) into the empty fields of "MSI_List" in 
> accordance with the zip code in "MSI_List"?
> 
> Thanks in advance!
> 
> Todd Clemmer
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


--
Yours,
KH Chiu
C&A Computer Consultants Ltd.
Tel: 3104 2070 Fax: 3010 0896
Email: [EMAIL PROTECTED]
Website: www.caconsultant.com


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: updating many-to-many relationships?

[Paul Chvostek]

I *really* don't want to have a pair of indexes on 60-character varchars
that cover forty million records.  That would increase the size of each
index from 280MB to 4.8GB.  Just for the indexes.  I don't think so.

Also, I don't see how it would address the issue of updates.  The
problem is that for each sku, the list of agents is *changing*.  Some
will be added, some will be removed.  I'm already doing an insert ignore
to "top up" the sku and agent tables, so populating the skuagent table
with new entries consists of:

 INSERT INTO skuagent (sku,agent) SELECT sku.id,agent.id FROM sku,agent
   WHERE sku.sku='%s' AND agent.agent='%s';

for each line in the input text file.  But the more I think about this,
the more it looks as if I'm going to have to process things the slow and
ugly way

Thanks anyway for looking at this.

p

On Wed, Feb 19, 2003 at 08:13:12AM -0600, gerald_clark wrote:
> 
> Looks to my like skuagent should be using the varchar fields instead of 
> the int fields.
> Then you could just insert ignore each line from your test file.
> 
> Paul Chvostek wrote:
> 
> >Hi all.
> >
> >I need some help figuring out how to update a many-to-many relationship
> >from a text file.
> >
> >For the fun of it, let's call the tables 'sku' and 'agent'.  Each sku
> >can have multiple agents (usually < 10, always > 0), and each agent can
> >be associated with an unlimited number of records skus.  Let's say, for
> >example, we're using:
> >
> >CREATE TABLE sku (
> >  id int unsigned NOT NULL auto_increment,
> >  sku varchar(60) NOT NULL default '',
> >  PRIMARY KEY  (id),
> >  UNIQUE KEY sku (sku),
> >);
> >
> >CREATE TABLE agent (
> >  id mediumint unsigned NOT NULL auto_increment,
> >  agent varchar(60) NOT NULL default '',
> >  PRIMARY KEY  (id),
> >  UNIQUE KEY agent (agent),
> >);
> >
> >CREATE TABLE skuagent (
> >  sku int(10) unsigned NOT NULL,
> >  agent mediumint(5) unsigned NOT NULL,
> >  UNIQUE KEY skuagent (sku,agent),
> >  KEY agentsku (agent,sku),
> >);
> >
> >That's fine as far as it goes, but I can't figure out how to repopulate
> >the tables when new data comes in.  On a regular basis (probably once
> >every two days), I'll be getting a new text file with the relationships
> >in a format that looks like:
> >
> > SKU_ONE AGENT_ONE
> > SKU_ONE AGENT_TWO
> > SKU_TWO AGENT_ONE
> > SKU_TWO AGENT_TWO
> > SKU_TWO AGENT_THREE
> > SKU_THREE AGENT_BLUE
> > SKU_THREE AGENT_ORANGE
> >
> >etc.  The text is what gets shoved into the varchar columns.  The input
> >text file does indeed have SKUs grouped as shown, so it's easy to `uniq`.
> >
> >The problem is that with each update, I'll be getting a different set of
> >relationships on perhaps 3% of the SKUs, with some new SKUs and AGENTs,
> >and some removed.  I can add the new records easily enough, and "orphan"
> >records aren't a problem.  What I can't figure out is a good way to do
> >the many-to-many update.
> >
> >I can do it by emptying the skuagent table every time I get a new file,
> >then re-populating it from scratch by running a new INSERT...SELECT for
> >each line in the file.  But the text file has over forty million lines,
> >with 15 million unique SKUs and half a million AGENTs.  I really don't
> >want to take the database down for hours at a time just to refresh.
> >
> >Or alternately, a grottier solution.  Each time I get a new file, I can
> >run a script that will go through each unique SKU in the file, delete
> >the skuagent records for THAT SKU ONLY, and re-add them with current
> >data.  It's *way* more processing time for every host in the loop, but
> >at least I won't have to take the whole thing down.
> >
> >I just can't seem to figure out an elegant solution.  Is there one, or
> >must I do this the ugly way?
> >
> >Any advice would be appreciated.  :)
> >
> >p
> >
> >(And for the list robot: sql,query,queries,smallint)
> >
> >  
> >
> 
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-- 
  Paul Chvostek <[EMAIL PROTECTED]>
  Operations / Abuse / Whatever
  it.canada, hosting and development   http://www.it.ca/


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

'bool' type and configure CXXFLAGS errors

[John J. Allison]

>Description:
>How-To-Repeat:
>Fix:

'bool' type and configure CXXFLAGS errors


Setup:
% uname -a
SunOS hurricane 5.6 Generic_105181-29 sun4u sparc SUNW,Ultra-60
% gcc --version
2.95.3
% setenv CFLAGS -O3
% setenv CXX gcc
% setenv CC gcc
% setenv CXXFLAGS "-O3 -felide-constructors -fno-exceptions -fno-rtti -mcpu=v8 
-Wa,-xarch=v8plusa"
% ./configure --prefix=/storm/mysql --enable-assembler --with-mysqld-user=stormdba 
--with-low-memory


Error:
(gmake == GNU make)
% gmake
...
gcc -DUNDEF_THREADS_HACK -I. -I. -I.. -I./../include -I../include -I./.. -I.. -I.. 
-O3 -DDBUG_OFF -O3 -felide-constructors -fno-exceptions -fno-rtti -mcpu=v8 
-Wa,-xarch=v8plusa=  -DHAVE_RWLOCK_T -c -o mysql.o `test -f mysql.cc || echo 
'./'`mysql.cc
In file included from client_priv.h:19,
 from mysql.cc:32:
../include/my_global.h:759: declaration does not declare anything
gmake: *** [mysql.o] Error 1


Attempted workaround:
vi include/my_config.h
#define HAVE_BOOL 1


New error:
% gmake
...
gcc -DUNDEF_THREADS_HACK -I. -I. -I.. -I./../include -I../include -I./.. -I.. -I.. 
-O3 -DDBUG_OFF -O3 -felide-constructors -fno-exceptions -fno-rtti -mcpu=v8 
-Wa,-xarch=v8plusa=  -DHAVE_RWLOCK_T -c -o mysql.o `test -f mysql.cc || echo 
'./'`mysql.cc
Assembler messages:
Error: invalid architecture -xarch=v8plusa=
gmake: *** [mysql.o] Error 1


Problems:
1. gcc is confused about 'bool' on the Ultra (not on same OS, same patch level on a 
sparc10)
2. configure puts an = on the end of my CXXFLAGS ; does it on a sparc10 system too

Either of the following done before configure results in a "...plusa=" in the Makefile
setenv CXXFLAGS "-O3 -felide-constructors -fno-exceptions -fno-rtti"
setenv CXXFLAGS "-O3 -felide-constructors -fno-exceptions -fno-rtti -mcpu=v8 
-Wa,-xarch=v8plusa"

Note that with the 1st setenv, configure adds its own arch flags and still appends =


Final workaround:
% gmake clean
% setenv CFLAGS -O3
% setenv CXX gcc
% setenv CC gcc
% setenv CXXFLAGS "-O3 -felide-constructors -fno-exceptions -fno-rtti -mcpu=v8 
-Wa,-xarch=v8plusa"
% ./configure --prefix=/storm/mysql --enable-assembler --with-mysqld-user=stormdba 
--with-low-memory
% vi config.status
:g/plusa=/s//plusa/g
% ./config.status
% vi config.h
#define HAVE_BOOL 1
% gmake

passes all tests in mysql-test except ctype_latin1_de
 (another problem that I'm researching, it can't connect to mysql server
  when running mysql-test-run; I have to have my own mysqld running separately,
  then the test fails on the actual data- maybe this is just configuration?)


Actual Fix: ??? do something in configure to get the = out and
to get HAVE_BOOL into config.h


>Submitter-Id:  
>Originator:John J. Allison
>MySQL support: [none | licence | email support | extended email support ]
>Synopsis:  'bool' type and configure CXXFLAGS errors
>Severity:  
>Priority:  
>Category:  mysql
>Class: 
>Release:   mysql-4.0.10-gamma (Source distribution)

>C compiler:2.95.3
>C++ compiler:  2.95.3
>Environment:

System: SunOS hurricane 5.6 Generic_105181-29 sun4u sparc SUNW,Ultra-60
Architecture: sun4

Some paths:  /usr/bin/perl /usr/ccs/bin/make /opt/bin/gmake /opt/bin/gcc 
/opt/SUNWspro/bin/cc
GCC: Reading specs from /usr/local/lib/gcc-lib/sparc-sun-solaris2.6/2.95.3/specs
gcc version 2.95.3 20010315 (release)
Compilation info: CC='gcc'  CFLAGS='-O3'  CXX='gcc'  CXXFLAGS='-O3 
-felide-constructors -fno-exceptions -fno-rtti -mcpu=v8 -Wa,-xarch=v8plusa'  
LDFLAGS=''  ASFLAGS=''
LIBC: 
-rw-r--r--   1 bin  bin  1623504 Sep 25  2001 /lib/libc.a
lrwxrwxrwx   1 root root  11 Sep 26  2001 /lib/libc.so -> ./libc.so.1
-rwxr-xr-x   1 bin  bin  1024888 Sep 25  2001 /lib/libc.so.1
-rw-r--r--   1 bin  bin  1623504 Sep 25  2001 /usr/lib/libc.a
lrwxrwxrwx   1 root root  11 Sep 26  2001 /usr/lib/libc.so -> ./libc.so.1
-rwxr-xr-x   1 bin  bin  1024888 Sep 25  2001 /usr/lib/libc.so.1
Configure command: ./configure '--prefix=/storm/mysql' '--enable-assembler' 
'--with-mysqld-user=stormdba' '--with-low-memory' 'CC=gcc' 'CFLAGS=-O3' 'CXXFLAGS=-O3 
-felide-constructors -fno-exceptions -fno-rtti -mcpu=v8 -Wa,-xarch=v8plusa' 'CXX=gcc'


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Unexpecte unknow column name error

[Barbara Gelhard]

Hello,

I am running MySQL version 3.23.39 on a Mac running OS X 10.2.4

This query works without error:

Drop table if exists temp; 
create temporary table temp select @canread := "false", @canread := if(Institution_Id 
= "8","true","false"), @canread AS canread from persons; 
select * from temp;

This query works without error:

select @canread := "false", @canread := if(FIND_IN_SET("8", Institution_Id) > 
0,"true","false"), @canread AS canread from persons;

This query doesn't:

Drop table if exists temp; 
create temporary table temp select @canread := "false", @canread := 
if(FIND_IN_SET("8", Institution_Id) > 0,"true","false"), @canread AS canread from 
persons; 
select * from temp;

I get the error:

ERROR 1166: Incorrect column name '@canread := if(FIND_IN_SET("8", Institution_Id) > 
0,"true","false")'

Can anyone tell me why?  

Barbara Gelhard


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

several newbie problems

[Nikola Skoric]

Hello,

I'm a newbie with MySQL, so it's possible my qustions are answered
somewhere, but I haven't found those answers yet...

When I start my mysqld deamon, I get this message: "Can't initialize InnoDB
as 'innodb_data_file_path' is not set". I've been reading through Reference
Manual and I realise that I have to configure my.cnf file if I want to use
InnoDB, and the problem would dissapear, but the problem is I don't need
InnoDB (in fact dno't know what it is :-) ). Sow, how do I supress this
message without configuring InnoDB? (MySQL I'm using is "Ver. 3.23.41-max-
debug for Win95/Win98 on i32")

Next two problems have occured on "mysqld  Ver 3.23.49-max for sun-
solaris2.8 on sparc".

First question. I connect to mysql client and do the following:
-
mysql> use nick
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>
-
My question is, what does this message mean, and how to get rid of it?

And, the last problem... I access to MySQL database via PHP standalone
script, that is don't use PHP as Apache module, but I have downloaded PHP
source, compiled it and now I use it for daily maintaining my database (I've
put some lines in my crontab file which run these PHP scripts). And every
time I run any of the scripts, I get following message:
-
File 'NONEXISTENT/charsets/?.conf' not found (Errcode: 2)
Character set '#27' is not a compiled character set and is not specified
in the 'NONEXISTENT/charsets/Index' file
-

What do I do? What's missing?

--
Pozdrav/Regards, Nikola [Nick] Skoric.
"...ket csillag csak oz egen/mint a szemed..."
http://newusers.cjb.net/ - site o Usenetu na hrvatskom!


-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

sql join help?

[Todd]

sql

I have a zip code db named "MSI_Zipcodes that contains

city
state
zipcode (index)

I also have a db named "MSI_List" that contains

ID (index)
email
city(empty)
state(empty)
zip

My problem is:

How can i bring the proper info from "MSI_Zipcodes"(city and state)
and enter it (city and state) into the empty fields of "MSI_List" in 
accordance with the zip code in "MSI_List"?

Thanks in advance!

Todd Clemmer


-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: License question

[Max ItDoesNotMatter]

Thank you KH 

I think that statistics information might
become important part of our system, so it is safer to
buy license or have a look on others RDBMS.

> As far as I know, if you only require MySQL as an
> option or just an added 
> features, your company do not need to purchase a
> license.
> 
> KH
> 
> --


__
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Problems inserting a date...

[Alejandro C. Garrammone]

I had a problem inserting a date...which format do I need to use?...I'm
using -mm-dd, but when I throw a query, the records display -00-00.

Thx in advance


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: How to simulate "FULL JOIN" with MYSQL

[Diana Soares]

Hi,

On Wed, 2003-02-19 at 15:56, Michael Katzmann wrote:
> I have a database with tables representing data from various years
> 1997, 1998, 1999, 2000 etc. In the tables I have data representing
> widget sales, zip codes, types of widgets, for example.
> Not all widget types were sold in every year, so there are entries
> in some tables that don’t occur in others.
> 
> If I use a left join:
> 
> SELECT T2000.sales, T1999.sales, T1998.sales, T1997.sales
> FROM T2000 LEFT JOIN T1999 USING( WidgetType, ZIP )
> LEFT JOIN T1998 USING( WidgetType, ZIP )
> LEFT JOIN T1997 USING( WidgetType, ZIP )
> WHERE T2000.ZIP = 20009
> 
> I might get something like...
> 
> 1250 7800 NULL NULL
> 7689 2434 8788 NULL
> 1234 7878 2323 3434
> 7890 NULL NULL NULL
> 
> What I want is data from all years where sales occurred.
> with a left join I get diminishing returns. (if there is
> a null entry to the left, I never get any output for
> subsequent tables).
> 
> What I want is...
> 
> 1250 7800 NULL 6567 <== last value missing due to left join
> 7689 2434 8788 NULL
> NULL 5679 NULL 3434 <== row missing in 'left join'
> 1234 7878 2323 3434
> 7890 NULL 5664 NULL <== third value missing because of left join

I think the query you are doing doesn't reflect your purpose. 
I'm not shure that what i'm going to say is right, but have a thought
about this.

You're left joining T2000 with T1999, and T1999 with 1998, and T1998
with 1997. When a record exists in T2000 but does not exist in T1999,
the T1999.sales values (for that record) will be null and then the join
between and T1999 with T1998 (for that record) will only return NULL
values and the same for subsequent tables... 
That's why: 

> if there is
> a null entry to the left, I never get any output for
> subsequent tables

Maybe what you want would be something like:

SELECT T2000.sales, T1999.sales, T1998.sales, T1997.sales
FROM T2000 
LEFT JOIN T1999 
 USING( WidgetType, ZIP )
LEFT JOIN T1998 
 ON (T2000.WidgetType=T1998.WidgetType AND T2000.ZIP=T1998.ZIP)
LEFT JOIN T1997 
 ON (T2000.WidgetType=T1997.WidgetType AND T2000.ZIP=T1997.ZIP)
WHERE T2000.ZIP = 20009

This query also is not 100% right because if there isn't a WidgetType in
T2000 with ZIP=20009 that could exist, for example, in T1999, then that
WidgetType would not appear at all.

If this is a problem (there aren't all possible WidgetTypes for
ZIP=20009 in T2000), maybe you should first get all possible WidgetType
with ZIP=20009 to a temporary table and then substitute in the query
above the table T2000 for that temporary table and add T2000 like the
other years...


> This I believe is a "Full Join".
> 
> How can I simulate a full join with many tables with the current release of
> MYSQL ? Can it be done with temorary tables ? Is the solution slow
> (especially since the left join (with proper indexes) is very snappy)?
> 
> I saw a reference to "FULL JOINS" being included in 4.1. Is it, in fact,
> included in the 4.1 alpha release now available?

I'm not shure to answer about this so i'll leave it for the MySQL
"gurus" or others that can help..

> Michael Katzmann
> 
-- 
Diana Soares


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: C API Question

[Paul DuBois]

At 11:13 -0600 2/19/03, Paul DuBois wrote:

At 10:57 -0600 2/19/03, William R. Mattil wrote:

Hello,

I am having some cockpit trouble with the following

MYSQL_RES *mysql_list_fields(MYSQL *mysql, const char *table, const char
*wild);

and it is like lack of understanding on my part. Syntax is:

if (mysql_list_fields( &mysql, "some table name", "some field name"))

mysql_store_result and mysql_fetch_row follow but nothing is ever
returned. If I replace the mysql_list_fields with:

if (mysql_query(&mysql,"Describe some_table_name"))

everything works. Where am I missing the boat here ?


Nothing.  It's just that the documentation for this function is unclear/
incorrect.  I just came to realize this myself a few weeks ago. :-(

The information that mysql_list_fields() returns about the columns is
returned in the result set *metadata*.  So what you should do is call
mysql_fetch_field() to retrieve the metadata for each column of the result
set.

Note that the max_length value will always be zero.



Thanks

Bill
--

William R. Mattil   | Statisticians define a lottery as a tax  
Sr. System Aministrator | on not understanding mathematics
(972) 399-4106  |


To follow up on my own posting:

Here's an example.  It shows how to retrieve various bits of metadata,
including the column metadata. It assumes tbl_name is a string
containing the table name.

MYSQL_ROW   row;
MYSQL_FIELD *field;
unsigned long   *length;
unsigned inti;

MYSQL_RES   *res_set = mysql_list_fields (conn, tbl_name, NULL);

if (res_set == NULL)
fprintf (stderr, "list_fields failed\n");
else
{
printf ("Number of columns: %d\n", mysql_num_fields (res_set));
printf ("Number of rows: %d\n", mysql_num_rows (res_set));
printf ("   %-12s %-12s", "name", "table");
printf (" %-12s %3s %3s %4s %4s %s\n",
"default", "len", "max", "type", "dec", "not null");
for (i = 0; i < mysql_num_fields (res_set); i++)
{
field = mysql_fetch_field (res_set);
printf ("%2u %-12s %-12s",
i,
field->name,
field->table ? field->table : "NULL");
printf (" %-12s %3u %3u %3u %3u %0x %3d\n",
field->def ? field->def : "NULL",
field->length,
field->max_length,
field->type,
field->decimals,
field->flags,
IS_NOT_NULL(field->flags)
);
}
}

mysql_free_result (res_set);

-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

AW: InnoDB

[Rusch (ext) Reiner]

thanks a lot, I managed it now.
was just a little misconfigured my.cnf-file

> -Ursprüngliche Nachricht-
> Von: Victoria Reznichenko [mailto:[EMAIL PROTECTED]]
> Gesendet: Mittwoch, 19. Februar 2003 14:34
> An: [EMAIL PROTECTED]
> Betreff: re: InnoDB
> 
> 
> On Wednesday 19 February 2003 13:21, Rusch (ext) Reiner wrote:
> 
> > I have one question about InnoDB-tables especial the filesize of all
> > tables. In the past I took MyISAM but found out, some 
> things in InnoDB make
> > the system more stable.
> > But I want to get more free space. In MyISAM the space 
> grows automatically,
> > but not in InnoDB by default.
> 
> Take a look at autoextend option:
>   http://www.mysql.com/doc/en/InnoDB_start.html
> 
> > In my /etc/my.cnf the line for the path and file size is 
> not active like in
> > the original file.
> > But it works but with about 250-300MB max. size (don't know 
> exactly).
> > If I uncomment this line to expand my space, mysql (4.0.10) 
> doesn't start.
> > Don't know why.
> 
> Check error logs.
> 
> 
> -- 
> 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
> 
> 
> 
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Archive on remote Machine.

[Chris Faust]

You could just ftp them wherever you want for storage.

I made a simple script where I took the provided "mysqlhotcopy" script,
added it and used it every day as a cron to make a copy of all my databases,
then run through a check of each one then tar them all up and ftp them to a
remote server.

Its been running without a flaw for about 6 months now so personally I think
its a good solution to archiving.

-Chris

>> -Original Message-
>> From: Ahmed S K Anis [mailto:[EMAIL PROTECTED]]
>> Sent: Tuesday, February 18, 2003 5:52 AM
>> To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED];
>> [EMAIL PROTECTED]
>> Subject: Archive on remote Machine.
>>
>>
>> Hi,
>> I use  Select * into outfile  "filepath"  from table  to archive the
>> database.
>> Is there any way to archive this into a different machine. (Not current
>> machine)
>>
>> Yes Mapping to new machine and then giving path is an option. Any other
>> opton.?
>> Anis
>>
>>
>>
>> -
>> Before posting, please check:
>>http://www.mysql.com/manual.php   (the manual)
>>http://lists.mysql.com/   (the list archive)
>>
>> To request this thread, e-mail <[EMAIL PROTECTED]>
>> To unsubscribe, e-mail
>> <[EMAIL PROTECTED]>
>> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>>
>>
>>
>> -
>> Before posting, please check:
>>http://www.mysql.com/manual.php   (the manual)
>>http://lists.mysql.com/   (the list archive)
>>
>> To request this thread, e-mail <[EMAIL PROTECTED]>
>> To unsubscribe, e-mail
>> <[EMAIL PROTECTED]>
>> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Problem reloading data...

[Richard S. Huntrods]

Sefan,

Stefan Hinz wrote:


Richard,

 

H. On the solaris machines (both), there is currently NO "my.cnf"
file anywhere, yet MySQL has been working "just fine". I imagine default 
values are being used for everything. I'll have to select one of the 
.cnf files from "support-files". I'll let you know how it works.
   


 

set-variable = max_allowed_packet=16M
 


If this variable isn't set, the communication buffer (between client
and server) is just 1 MB big. This is a common source of problems,
especially when you are using extended inserts (i.e. INSERT ... VALUES
(...),(...),(...)) like mysqldump would produce with the --opt option.


Yikes!!! I knew that running "default configuration" gave you SOME 
values, but I didn't know they were so small. I've placed the 
my-medium.cnf as "my.cnf" in /etc on the Sparc 20, and also in C:\ on 
the W2K machine. Database access is much improved on both.

Now, if people would get off the production server so I could restart 
MySQL... 

Cheers,

-Richard


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]


 



-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: C API Question

[Paul DuBois]

At 10:57 -0600 2/19/03, William R. Mattil wrote:

Hello,

I am having some cockpit trouble with the following

MYSQL_RES *mysql_list_fields(MYSQL *mysql, const char *table, const char
*wild);

and it is like lack of understanding on my part. Syntax is:

if (mysql_list_fields( &mysql, "some table name", "some field name"))

mysql_store_result and mysql_fetch_row follow but nothing is ever
returned. If I replace the mysql_list_fields with:

if (mysql_query(&mysql,"Describe some_table_name"))

everything works. Where am I missing the boat here ?


Nothing.  It's just that the documentation for this function is unclear/
incorrect.  I just came to realize this myself a few weeks ago. :-(

The information that mysql_list_fields() returns about the columns is
returned in the result set *metadata*.  So what you should do is call
mysql_fetch_field() to retrieve the metadata for each column of the result
set.

Note that the max_length value will always be zero.



Thanks

Bill
--

William R. Mattil   | Statisticians define a lottery as a tax  
Sr. System Aministrator | on not understanding mathematics
(972) 399-4106  |



-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re[2]: Problem reloading data...

[Stefan Hinz]

Richard,

> H. On the solaris machines (both), there is currently NO "my.cnf"
> file anywhere, yet MySQL has been working "just fine". I imagine default 
> values are being used for everything. I'll have to select one of the 
> .cnf files from "support-files". I'll let you know how it works.

>>set-variable = max_allowed_packet=16M

If this variable isn't set, the communication buffer (between client
and server) is just 1 MB big. This is a common source of problems,
especially when you are using extended inserts (i.e. INSERT ... VALUES
(...),(...),(...)) like mysqldump would produce with the --opt option.

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]


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Archive on remote Machine.

[Charles Lewis]

If you have ssh installed on your target machine couldn't you do the
following?

mysqldump  | scp @:.


KH Chiu wrote:


It will then be a networking problem. As far as I know, you may need a 
networking file system like NFS or Samba. You should mount your remote 
machine as a network drive. Then you can use mysqldump to make a backup but 
redirect the output to the network drive.

There may be other methods. In fact, I have a project that has more complex 
remote backup requirement. I am working on it using C right now but not yet 
completed.

Regards,

 

Thanx for the suggestion.
Is there any way i can Archive data to remote machine where MySQL is
not installed.

Anis


   

"KH Chiu" <[EMAIL PROTECTED]> Wednesday, February 19, 2003
 

8:46:46 AM >>>
You may try the following command

mysqldump -u  -p  samp_db | mysql -h 
remote 
host name or address> samp_db -u  -p 

Please also note that samp_db must be exist on your remote machine. Of

course, you should sub. samp_db with your to be achieve database name.

Regards,
   


--
Charles Lewis
Reliable Computer Services
[EMAIL PROTECTED]



-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: License question

[KH Chiu]

As far as I know, if you only require MySQL as an option or just an added 
features, your company do not need to purchase a license.

KH

> > Otherwise, you may ask whether MySQL is an option or
> > not. It means that if 
> > your logs and related information can go to other
> > mean such as a text file 
> > then you are no need to buy license.
> 
> In theory we can find option for any db appliance,
> let say, we can store data in regular files. 
> 
> > 
> > However, if your program can't run without MySQL
> > database, I think license 
> > will be required.
> 
> I can for example, use PostgreSQL, but main key for me
> is to have multiplatform database. 
> 
> __
> Do You Yahoo!?
> Everything you'll ever need on one web page
> from News and Sport to Email and Music Charts
> http://uk.my.yahoo.com
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


--
Yours,
KH Chiu
C&A Computer Consultants Ltd.
Tel: 3104 2070 Fax: 3010 0896
Email: [EMAIL PROTECTED]
Website: www.caconsultant.com


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

C API Question

[William R. Mattil]

Hello,

I am having some cockpit trouble with the following

MYSQL_RES *mysql_list_fields(MYSQL *mysql, const char *table, const char
*wild);

and it is like lack of understanding on my part. Syntax is:

if (mysql_list_fields( &mysql, "some table name", "some field name"))

mysql_store_result and mysql_fetch_row follow but nothing is ever
returned. If I replace the mysql_list_fields with:

if (mysql_query(&mysql,"Describe some_table_name"))

everything works. Where am I missing the boat here ?

Thanks

Bill
-- 

William R. Mattil   | Statisticians define a lottery as a tax   
Sr. System Aministrator | on not understanding mathematics  
(972) 399-4106  |


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Native XADataSource for MySQL

[Frank Gates]

Hello,

Earlier I asked Mark about XA and Connector/J.  He said that it was
dropped from the 3.0 releases because MySQL doesn't natively support XA,
but when it does he will add it back in.   Mark suggested that I ask for
XA on these lists if I wish to expedite it in the pipeline.

I am raising the flag here for XA, but not to exceed work on stored
procedures and views.  I believe that two-phase commit with XA should be
natively supported so that it will compete effectively with other
database providers in those instances where two-phase is valuable.

My personal requirement for it is minimal at this time.  I am developing
an application server development framework (which is MySQL-centric) and
so have need to support XA connection pooling and resources.   I had
already developed an XAResource-wrapper for PooledConnection's when I
discovered that XA was dropped from Connector/J 3.0.  It is a small task
for me to wrap ConnectionPoolDataSource's with an XADataSource
implementation for now.   But I do express my desire for native XA in
MySQL.

(The app server framework is intended to build customizable containers
as well as EJB containers.   The motive for this framework is that there
are some applications that fit the application server model but not the
EJB model or only part of the J2EE model.)

Frank Gates
[EMAIL PROTECTED]


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Problem: mysql can't create new thread.

[Philipp]

Hi there,


this problem was asked in this list before and i thought i solved
it but i was wrong. Heres the description:


I am running mysql 3.23.54 on Debian Linux with 2.4.20 Final
System has 2 GB Ram.
I compiled from source.

Here are relevant passages from my.cnf

port= 3306
socket  = /var/run/mysql/mysql.sock
set-variable= max_connections=1000
skip-locking

set-variable= open_files_limit=8129
set-variable= max_tmp_tables=300
set-variable= key_buffer=512M
set-variable= max_allowed_packet=5M
set-variable= table_cache=3000
set-variable= sort_buffer=2M
set-variable= record_buffer=2M
set-variable= thread_cache=50
set-variable= myisam_sort_buffer_size=96M
set-variable= thread_concurrency=512
set-variable= wait_timeout=60


Ulimits are raised to unlimted for user msql. I am executing this in
safe_mysqld:

ulimit -n $open_files
ulimit -u unlimited
ulimit -a  >> /tmp/mysql.ulmit

mysql.ulimit looks like this:

core file size(blocks, -c) 0
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
max locked memory (kbytes, -l) unlimited
max memory size   (kbytes, -m) unlimited
open files(-n) 8129
pipe size  (512 bytes, -p) 8
stack size(kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes(-u) unlimited
virtual memory(kbytes, -v) unlimited


The System never runs out of Ram. There is lots of it available, but when
Mysql created
750 threads it stops creating more. It then tells me something like in the
Subject: Can't
create thread, perhaps out of memoy, OS-depended bug, blah...


Now finally, a question: Can someone please give me a hint  what to do to
make
mysql create more threads ?


Regards,
Philipp


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: License question

[Max ItDoesNotMatter]

> Otherwise, you may ask whether MySQL is an option or
> not. It means that if 
> your logs and related information can go to other
> mean such as a text file 
> then you are no need to buy license.

In theory we can find option for any db appliance,
let say, we can store data in regular files. 

> 
> However, if your program can't run without MySQL
> database, I think license 
> will be required.

I can for example, use PostgreSQL, but main key for me
is to have multiplatform database. 


__
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: License question

[Max ItDoesNotMatter]

> Otherwise, you may ask whether MySQL is an option or
> not. It means that if 
> your logs and related information can go to other
> mean such as a text file 
> then you are no need to buy license.

In theory we can find option for any db appliance,
let say, we can store data in regular files. 

> 
> However, if your program can't run without MySQL
> database, I think license 
> will be required.

I can for example, use PostgreSQL, but main key for me
is to have multiplatform database. 


__
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

How to simulate "FULL JOIN" with MYSQL

[Michael Katzmann]

I have a database with tables representing data from various years
1997, 1998, 1999, 2000 etc. In the tables I have data representing
widget sales, zip codes, types of widgets, for example.
Not all widget types were sold in every year, so there are entries
in some tables that don’t occur in others.

If I use a left join:

SELECT T2000.sales, T1999.sales, T1998.sales, T1997.sales
FROM T2000 LEFT JOIN T1999 USING( WidgetType, ZIP )
LEFT JOIN T1998 USING( WidgetType, ZIP )
LEFT JOIN T1997 USING( WidgetType, ZIP )
WHERE T2000.ZIP = 20009

I might get something like...

1250 7800 NULL NULL
7689 2434 8788 NULL
1234 7878 2323 3434
7890 NULL NULL NULL

What I want is data from all years where sales occurred.
with a left join I get diminishing returns. (if there is
a null entry to the left, I never get any output for
subsequent tables).

What I want is...

1250 7800 NULL 6567 <== last value missing due to left join
7689 2434 8788 NULL
NULL 5679 NULL 3434 <== row missing in 'left join'
1234 7878 2323 3434
7890 NULL 5664 NULL <== third value missing because of left join

This I believe is a "Full Join".

How can I simulate a full join with many tables with the current release of
MYSQL ? Can it be done with temorary tables ? Is the solution slow
(especially since the left join (with proper indexes) is very snappy)?

I saw a reference to "FULL JOINS" being included in 4.1. Is it, in fact,
included in the 4.1 alpha release now available?

Michael Katzmann



-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Alternative to LOCK TABLES in InnoDB?

[Iago Sineiro]

Hi all.

In my application I use generators generated manually. I have a table named
_POID with contains the last generator used for the table .

With table type MYISAM I use the follow secuence to obtain the next
generator:

LOCK TABLE _POID;

SELECT instanceNo FROM _POID;

UPDATE _POID SET instanceNo = ;

UNLOCK TABLES;

But with table type InnoDB I have to use another connection to do this
because the LOCK TABLE sentence commits the transacction. Is there any form
to do it in MySQL with InnoDB?

Thanks in advance.

Iago Sineiro



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Bug-Load data local infile

[Guddack Thorsten ICM MP SCM GO 21]

Hi List,

I'm using 4.0.10-max-nt of mysql.

I update to this version from 4.0.5. With this version I have no trouble
with load data local infile.

But now if i try to use it from the mysql-Command-line tool i get
an errror 1148, This command is not allowed with this mysql version.

On my server the variable local_infile is set to on.

I change to my.cnf-File on my client to
[mysql]
local-infile=1

or I try to start mysql with the option --local-infile.

Everything gives me the same error.

Any Ideas?

Thorsten Guddack


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Problem reloading data...

[Richard S. Huntrods]

Stefan,

H. On the solaris machines (both), there is currently NO "my.cnf" 
file anywhere, yet MySQL has been working "just fine". I imagine default 
values are being used for everything. I'll have to select one of the 
.cnf files from "support-files". I'll let you know how it works.

-Richard

Stefan Hinz wrote:

Hi Richard, hi Richard :)

 

I still suspect some memory limitation on the Sparc 20 preventing the
"un-dump" from completing. In re-checking my server data list, the 20 
has 1/2 the memory of the E250 (256 Meg on the 20 vs. 512 on the E250 - 
also 512 on the W2K machine).
   


Just to make sure it's not a trivial problem, you should check in
my.cnf (on the target machine, I guess):

set-variable = max_allowed_packet=16M

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]


 



-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: InterBase vs. Mysql

[Vadim Vinokur]

Hi!

What to choose - InterBase/FireBird or MySQL - depends on the tasks you are
going to solve with your SQL server. InterBase/FireBird is intended for
managing a quite large and complicate databases, where a lot of business
rules should be realized and stored in common database instead of being
realized in each client application separately, where referential integrity
plays a significant role, where subselects are often used, and so on. For
these purposes InterBase/FireBird provides wide variety of facilities:
triggers, stored procedures, exceptions, checks and foreign keys, and so on.
But if you need to develop a small database and do it quickly and easily -
than you better do it with MySQL. Of course, MySQL is the best solution for
web development and site management - because speed becomes one of the main
factors here, and because it works very well with PHP.

BTW, FireBird costs the same as MySQL and is distributed under open source
license.
http://www.firebirdsql.org/

And finally I would like to note that we like both of these servers and have
already developed administration and development tools for MySQL,
InterBase/FireBird and even PostgreSQL. :)
http://www.sqlmanager.net

Best regards,
Vadim Vinokur
EMS HiTech
http://www.ems-hitech.com

- Original Message -
From: "Maciej Bobrowski" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, February 18, 2003 4:27 PM
Subject: InterBase vs. Mysql


>
> Hi,
>
> I am a MySQL admin. I like it very much. It is very quick,stable and
> easy in configuration.
> But I have a boss, who has been told about InterBase. He told me I should
> interest in the InterBase, because it is programmed by russian, who made a
> great project and told him mysql is totaly worse in comparison with the
> InterBase. Actually he didn't prooved it, he just heared about it from the
> russians. Did anyone met with the InterBase and can tel me something about
> it and prove me that the Mysql is BETTER? I need arguments for using the
> MySQL for a company (lets tell about 100 people, who use it very
> intensive through the whole day 7 days a week).
>
> Best regards,
>
> Maciej Bobrowski
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Sequential read on a file

[Colin]

Yeah, we are gettign a single row each time.
The file is 
hocatID (auto-increm) primary key
Hocat  (varchar - index)
description (varchar)
...plus many other numeric/varchar fields.

We are looking to display a single record on a page at one time with an
option for the user to step through the file. In our current system, it's a
simple case of read next / read previous on the file. Apologies if this is
bad mailing list form, but here is the crux of the code.
The $hoCategory & $description fields are a cut-down form in html thus..
  
Ho
Category 
  
 
 

 
  
Description 

  



..with the main code being...
 $GLOBALS["maxRecs"] || $num1 < 1 || $num1 == NULL)
$num1 = 0;

$sql = "SELECT * FROM `Ho Category` LIMIT " . $num1 . ",1"; 

$result = mysql_query($sql, $GLOBALS["link"]) or
  die("Failed on Query");

if (!mysql_num_rows($result))
{   //
No more records in the file
if ($Args[0] == "Incr" || $Args[0] == "Last")
$num1--;// undo action
else
$num1++;
}

while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
$row = setVariables($row);
foreach($row as $key => $value)
$$key = $value;// copy value to key-named field/var.
}

mysql_free_result($result);
return $num1;
}
?>

- As I said, we are fumbling a bit here as we look to move towards web-based
systems.

Regards,
Colin K Heaps

-Original Message-
From: Jerry [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, February 19, 2003 3:01 PM
To: Colin; [EMAIL PROTECTED]
Subject: Re: Sequential read on a file


Use and id column, like an auto increment one and use that in the html you
are writing out +1 or -1 to get the next.

i.e.

"select id, X, X, X from table where id=1"

returns 1, value of X, value of X, value of X etc etc etc

then in the html use the id .
id+1
Next

need some error checking for less than 0 and finding out what the max is.

Most lightly going to be something in here :

http://www.devshed.com/Server_Side/PHP


Is it a case of one rows worth of information from the dB per page/request ?

--
Jerry @
MetalCat
dot Net
--

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Help optimizing queries

[Jesse Sheidlower]

I have a database that I've worked hard to set up, and while
many of my queries are working well, I have a few that are
proving to be extremely slow, despite my best efforts to
properly index them. I'd be grateful for any advice on how to
speed these up, or an explanation of how there's no way to
make it any better.

Basically this is a database of quotations, with tables "cg"
having 2.8M rows, "q" having 2.2M, "cit" 76,000, and "sref"
23,000. The larger tables are aspects of the quotations, the
smaller ones contain bibliographic info. Most queries are
getting quotations depending on some bibliographic factors.
All the relevant fields for joins are indexed.

The server is a 1.4Ghz PIII with 1G RAM and 15,000 RPM 
SCSI drives, and I'm running MySQL 4.0.9 on FreeBSD 4.7.
The server is very lightly loaded.

Two queries that are giving me trouble are this, which gives
a count of words added in a particular timespan ("sref.cd" is a
date field, indexed; "cg.cw" is an indexed VARCHAR):

SELECT count(cg.cw) AS count FROM cg,q,cit,sref
WHERE cg.q_id = q.id AND q.cit_id = cit.id AND cit.sref_id = sref.id
AND cg.cw LIKE 'm%' 
AND sref.cd > DATE_SUB(CURDATE(), INTERVAL 6 MONTH);

This can take anywhere from 10-30 seconds to execute, depending
on the letter, and longer with a longer date range. The EXPLAIN
looks like this:

mysql> EXPLAIN SELECT count(cg.cw) AS count FROM cg,q,cit,sref
-> WHERE cg.q_id = q.id AND q.cit_id = cit.id AND cit.sref_id = sref.id
-> AND cg.cw LIKE 'm%'
-> AND sref.cd > DATE_SUB(CURDATE(), INTERVAL 6 MONTH)\G
*** 1. row ***
table: cg
 type: range
possible_keys: q_id,cw
  key: cw
  key_len: 101
  ref: NULL
 rows: 153385
Extra: Using where
*** 2. row ***
table: q
 type: eq_ref
possible_keys: PRIMARY,cit_id
  key: PRIMARY
  key_len: 4
  ref: cg.q_id
 rows: 1
Extra: 
*** 3. row ***
table: cit
 type: eq_ref
possible_keys: PRIMARY,sref_id
  key: PRIMARY
  key_len: 4
  ref: q.cit_id
 rows: 1
Extra: 
*** 4. row ***
table: sref
 type: eq_ref
possible_keys: PRIMARY,cd
  key: PRIMARY
  key_len: 4
  ref: cit.sref_id
 rows: 1
Extra: Using where
4 rows in set (0.02 sec)

It is somewhat faster if I ask for the cg.cw itself, instead of the
count, but it's still in the many-seconds range, which is too slow.

An even worse query is this one, where I'm looking for the most
common words added in a particular timespan:

SELECT cg.cw, count(*) AS count FROM cg,cit,sref,q
WHERE cg.q_id = q.id AND q.cit_id = cit.id AND cit.sref_id = sref.id
AND sref.cd > DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
GROUP BY cw 
ORDER BY count DESC 
LIMIT 20;

This just took 5m 34s to execute, which is totally unbearable. I
understand that if it's trying to sort millions of rows it could
be difficult, but is there any way to speed this up? Here's the EXPLAIN:

mysql> EXPLAIN SELECT cg.cw, count(*) AS count FROM cg,cit,sref,q
-> WHERE cg.q_id = q.id AND q.cit_id = cit.id AND cit.sref_id = sref.id
-> AND sref.cd > DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
-> GROUP BY cw
-> ORDER BY count DESC
-> LIMIT 20\G
*** 1. row ***
table: cg
 type: index
possible_keys: q_id
  key: cw
  key_len: 101
  ref: NULL
 rows: 2839036
Extra: Using temporary; Using filesort
*** 2. row ***
table: q
 type: eq_ref
possible_keys: PRIMARY,cit_id
  key: PRIMARY
  key_len: 4
  ref: cg.q_id
 rows: 1
Extra: 
*** 3. row ***
table: cit
 type: eq_ref
possible_keys: PRIMARY,sref_id
  key: PRIMARY
  key_len: 4
  ref: q.cit_id
 rows: 1
Extra: 
*** 4. row ***
table: sref
 type: eq_ref
possible_keys: PRIMARY,cd
  key: PRIMARY
  key_len: 4
  ref: cit.sref_id
 rows: 1
Extra: Using where
4 rows in set (0.00 sec)

Thanks for any ideas. I'll probably have further problems when
I start trying to use FULLTEXT searches, but these are the
difficulties that sprung up right away.

Jesse Sheidlower

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Sequential read on a file

[KH Chiu]

You are right. "read previous" simply not exist. I think this is due to both 
historical reason and 'client/server model'.

In fact, 'read next' is available by reading next row in a query. However, 
the record cursor is one direction only and cannot be read back.

In my experience, I will store the row id of the previous record and perform 
another sql query if a back operation is required.

Regards, 

> Hi,
> Newbie here so please bear with me.
> I am running  MySQL 3.23.22 with PHP4.01 on WinNT client / Linux 
> host, everything is working okay and I'm just getting to grips with 
> some basic database handling. Although I can use mysql_query to get 
> a list of records using "Select * from `mydata`" - for what we are 
> looknig at, we also need a basic Next/Prev stepping capability on a 
> record per record basis. I realise this can be done just grabbing 
> all records and then moving through the rows, but we are also 
> looknig at picking up each record individually. I have had some 
> success using "count(*)" to get the last record, then stepping 
> through with "limit $x,1" in the select, where $x is decremented / 
> incremented based on which button is pressed. I cannot seem to find 
> any simply "read next" or "read previous" type command though. Is 
> the way I am going about it "right" or is there a more 
> simple/logical method? My main concern is that, with the key being 
> auto-increment, as records are added and removed from the database,
>  as simple +1/-1 may fall over. -Colin
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


--
Yours,
KH Chiu
C&A Computer Consultants Ltd.
Tel: 3104 2070 Fax: 3010 0896
Email: [EMAIL PROTECTED]
Website: www.caconsultant.com


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Hash-Functions

[Andrew]

I thought this was PHP

Andrew

>-Original Message-
>From: Bernhard Döbler [mailto:[EMAIL PROTECTED]]
>Sent: 19 February 2003 14:53
>To: [EMAIL PROTECTED]
>Subject: Hash-Functions
>
>
>Hi,
>
> there's a Password()-Function in MySQL. Can somebody tell my on what
>standard is based (MD5 etc.) and if there's something similar in a
>DBMS that also supports triggers? InterBase does not support many functions...
>
>Thanks
>Bernhard
>
>-
>Before posting, please check:
>   http://www.mysql.com/manual.php   (the manual)
>   http://lists.mysql.com/   (the list archive)
>
>To request this thread, e-mail <[EMAIL PROTECTED]>
>To unsubscribe, e-mail
><[EMAIL PROTECTED]>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
>---
>Incoming mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.455 / Virus Database: 255 - Release Date: 13/02/2003
>


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Hash-Functions

[Michael T. Babcock]

Bernhard Döbler wrote:


there's a Password()-Function in MySQL. Can somebody tell my on what standard is based (MD5 etc.) and if there's something similar in a DBMS that also supports triggers? InterBase does not support many functions...
 


Its deprecated; use MD5() or SHA1() ...

--
Michael T. Babcock
C.T.O., FibreSpeed Ltd.
http://www.fibrespeed.net/~mbabcock



-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Sequential read on a file

[Jerry]

Use and id column, like an auto increment one and use that in the html you
are writing out +1 or -1 to get the next.

i.e.

"select id, X, X, X from table where id=1"

returns 1, value of X, value of X, value of X etc etc etc

then in the html use the id .
id+1
Next

need some error checking for less than 0 and finding out what the max is.

Most lightly going to be something in here :

http://www.devshed.com/Server_Side/PHP


Is it a case of one rows worth of information from the dB per page/request ?

--
Jerry @
MetalCat
dot Net
--

- Original Message -
From: "Colin" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, February 19, 2003 2:51 PM
Subject: Sequential read on a file


> Hi,
> Newbie here so please bear with me.
> I am running  MySQL 3.23.22 with PHP4.01 on WinNT client / Linux host,
> everything is working okay and I'm just getting to grips with some basic
> database handling.
> Although I can use mysql_query to get a list of records using "Select *
from
> `mydata`" - for what we are looknig at, we also need a basic Next/Prev
> stepping capability on a record per record basis.
> I realise this can be done just grabbing all records and then moving
through
> the rows, but we are also looknig at picking up each record individually.
> I have had some success using "count(*)" to get the last record, then
> stepping through with "limit $x,1" in the select, where $x is decremented
/
> incremented based on which button is pressed.
> I cannot seem to find any simply "read next" or "read previous" type
command
> though. Is the way I am going about it "right" or is there a more
> simple/logical method?
> My main concern is that, with the key being auto-increment, as records are
> added and removed from the database, as simple +1/-1 may fall over.
> -Colin
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

MySQL Connector/J and XADataSources

[Frank Gates]

Mark,

I read in the release notes that there is an implementation of XADataSource
(MySQLXADataSource), which does exist in the 2.0.14 release.   I've looked in
the 3.x releases and do not see it.   Am I just not seeing it?  Is this also
planned for the 3.x releases?

Thanks,

Frank




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Hash-Functions

[Bernhard Döbler]

Hi,

 there's a Password()-Function in MySQL. Can somebody tell my on what standard is 
based (MD5 etc.) and if there's something similar in a DBMS that also supports 
triggers? InterBase does not support many functions...

Thanks
Bernhard

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Sequential read on a file

[Colin]

Hi,
Newbie here so please bear with me.
I am running  MySQL 3.23.22 with PHP4.01 on WinNT client / Linux host,
everything is working okay and I'm just getting to grips with some basic
database handling.
Although I can use mysql_query to get a list of records using "Select * from
`mydata`" - for what we are looknig at, we also need a basic Next/Prev
stepping capability on a record per record basis.
I realise this can be done just grabbing all records and then moving through
the rows, but we are also looknig at picking up each record individually.
I have had some success using "count(*)" to get the last record, then
stepping through with "limit $x,1" in the select, where $x is decremented /
incremented based on which button is pressed.
I cannot seem to find any simply "read next" or "read previous" type command
though. Is the way I am going about it "right" or is there a more
simple/logical method?
My main concern is that, with the key being auto-increment, as records are
added and removed from the database, as simple +1/-1 may fall over.
-Colin

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Red Hat 8.0 compile problems with 4.0.10

[Mark Matthews]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Duncan Maitland wrote:

Hello,



I just tried compiling and using MySQL-4.0.11 (from the BitKeeper
repository) on RedHat 8.0, and this problem seems to have gone


away:

Unfortunately the problem is still occurring even after retrieving the
latest source from the repository (I used the compile_pentium_debug
script).



Can you repeat the problem using 4.0.10?



It seems to happen every time, and on any installation of Red Hat 8.0 I
have.

Mark - I note that you have been able to successfully compile and run a
source distribution of MySQL 4.0.10 on Red Hat 8.0. Was your
installation of Red Hat customised in any way? What was the configure
line for MySQL?



I used compile_pentium_max (not debug). I don't know if the debug code 
is causing you any problems, you might try compiling without it.

	-Mark


- -- 
MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/

For technical support contracts, visit https://order.mysql.com/?ref=mmma

__  ___ ___   __
   /  |/  /_ __/ __/ __ \/ /  Mark Matthews <[EMAIL PROTECTED]>
  / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
 /_/  /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
<___/ www.mysql.com
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.1.90 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE+U5brtvXNTca6JD8RAksXAJ9zcI8njEoTdpnikh1tYK8LAsBFqQCgg+iO
NfvN3NcjPiHaE/YHtRwL7Rs=
=/BBz
-END PGP SIGNATURE-


-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: updating many-to-many relationships?

[gerald_clark]

Looks to my like skuagent should be using the varchar fields instead of 
the int fields.
Then you could just insert ignore each line from your test file.

Paul Chvostek wrote:

Hi all.

I need some help figuring out how to update a many-to-many relationship
from a text file.

For the fun of it, let's call the tables 'sku' and 'agent'.  Each sku
can have multiple agents (usually < 10, always > 0), and each agent can
be associated with an unlimited number of records skus.  Let's say, for
example, we're using:

CREATE TABLE sku (
 id int unsigned NOT NULL auto_increment,
 sku varchar(60) NOT NULL default '',
 PRIMARY KEY  (id),
 UNIQUE KEY sku (sku),
);

CREATE TABLE agent (
 id mediumint unsigned NOT NULL auto_increment,
 agent varchar(60) NOT NULL default '',
 PRIMARY KEY  (id),
 UNIQUE KEY agent (agent),
);

CREATE TABLE skuagent (
 sku int(10) unsigned NOT NULL,
 agent mediumint(5) unsigned NOT NULL,
 UNIQUE KEY skuagent (sku,agent),
 KEY agentsku (agent,sku),
);

That's fine as far as it goes, but I can't figure out how to repopulate
the tables when new data comes in.  On a regular basis (probably once
every two days), I'll be getting a new text file with the relationships
in a format that looks like:

	SKU_ONE AGENT_ONE
	SKU_ONE AGENT_TWO
	SKU_TWO AGENT_ONE
	SKU_TWO AGENT_TWO
	SKU_TWO AGENT_THREE
	SKU_THREE AGENT_BLUE
	SKU_THREE AGENT_ORANGE

etc.  The text is what gets shoved into the varchar columns.  The input
text file does indeed have SKUs grouped as shown, so it's easy to `uniq`.

The problem is that with each update, I'll be getting a different set of
relationships on perhaps 3% of the SKUs, with some new SKUs and AGENTs,
and some removed.  I can add the new records easily enough, and "orphan"
records aren't a problem.  What I can't figure out is a good way to do
the many-to-many update.

I can do it by emptying the skuagent table every time I get a new file,
then re-populating it from scratch by running a new INSERT...SELECT for
each line in the file.  But the text file has over forty million lines,
with 15 million unique SKUs and half a million AGENTs.  I really don't
want to take the database down for hours at a time just to refresh.

Or alternately, a grottier solution.  Each time I get a new file, I can
run a script that will go through each unique SKU in the file, delete
the skuagent records for THAT SKU ONLY, and re-add them with current
data.  It's *way* more processing time for every host in the loop, but
at least I won't have to take the whole thing down.

I just can't seem to figure out an elegant solution.  Is there one, or
must I do this the ugly way?

Any advice would be appreciated.  :)

p

(And for the list robot: sql,query,queries,smallint)

 




-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

libz-devel needed for DBD-mysql install on RedHat 8.0

[Sheahan, John (PCLN-NW)]

I am trying to install DBD-mysql-2.1025 on a RH8 machine. When I do a "make"
I get an error saying that it can't find libz.a or libz.so etc

I tried modifying the "perl Makefile.PL" using --libs= and the path to my
libz.so file.

As a fix, it did say that I could fix this by installing libz-devel, but
that is not available anywhere that I can find. Can someone help me with
this?


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

backup script

[Jeff Mao]

Greetings all,

Not sure if this is the right place to ask this,...I use the 
following script on an OS X server to backup my mysql databases:

 #!/bin/sh
DATE=`/bin/date +%Y%m%d"_"%H%M%S`;
cd /Users/usernmae/Documents;
/usr/bin/mysqldump -hhost -uusername -ppassword --all-databases > 
full_dump_$DATE.sql;
exit

The script runs on from the crontab each morning at 3:30 am.  Simple 
question, I got the script from someone else, so I'm not too savvy 
with scripting.  What can I add to the script so that it 
automatically erases backups that are x weeks or days old?  If I 
forget about it, the backup folder soon blossoms as it collects a new 
backup each day, and I'd like to automate the process more so I'm not 
holding on to tons of old backups.

Thanks
Jeff

--

Jeff Mao
[EMAIL PROTECTED]

-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

re: SELECT * FROM different columns

[Egor Egorov]

On Wednesday 19 February 2003 12:51, Reto Baudenbacher wrote:

> newbie-question, sorry...
>
> is it possible to write this (working) query in a shorter form:
>
> SELECT * FROM $table WHERE (col1 LIKE '%$term%') OR (col2 LIKE '%$term%')
> OR (col3 LIKE '%$term%')

The short answer - yes, you can write the query like above. 



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




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

re: InnoDB

[Victoria Reznichenko]

On Wednesday 19 February 2003 13:21, Rusch (ext) Reiner wrote:

> I have one question about InnoDB-tables especial the filesize of all
> tables. In the past I took MyISAM but found out, some things in InnoDB make
> the system more stable.
> But I want to get more free space. In MyISAM the space grows automatically,
> but not in InnoDB by default.

Take a look at autoextend option:
http://www.mysql.com/doc/en/InnoDB_start.html

> In my /etc/my.cnf the line for the path and file size is not active like in
> the original file.
> But it works but with about 250-300MB max. size (don't know exactly).
> If I uncomment this line to expand my space, mysql (4.0.10) doesn't start.
> Don't know why.

Check error logs.


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





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

re: Connecting to remote server

[Egor Egorov]

On Wednesday 19 February 2003 11:24, kk wrote:

>   I just installed mysql4.x into the home/username directory of a remote
> machine. It is running good and I created a new user tester with all
> privileges.
> But how can i connect to the remote server running on a home directory
> using a front end tool like MySQLFront. It is listening to default port
> 3306.

Use hostname of the remote box or IP address.




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




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

re: FOREIGN KEY problem

[Victoria Reznichenko]

On Wednesday 19 February 2003 10:46, [EMAIL PROTECTED] wrote:

> I have problems with foreign key creation.
> I have installed mysql server cersion 3.23.53 on red hat linux 7.3.
> Then I have created innodb datebase to be able to use FOREIGN KEY
> Constarints.
> This is concerning piece of my my.cnf file:
>

[skip]

>
> Then I restarted MySql databas eserver (everything looked ok in logfie)
> But I am not able to create tables with foreign keys. please have a look at
> this error:
>
> mysql> use test;
> Database changed
> mysql> CREATE TABLE SMS_STATUS (
> -> SS_ID VARCHAR(50) NOT NULL,
> -> SS_DESCRIPTION TEXT,
> -> PRIMARY KEY (SS_ID))
> -> TYPE = INNODB;
> Query OK, 0 rows affected (0.01 sec)
>
> mysql> CREATE TABLE SMS_IN (
> -> SI_ID BIGINT(12) NOT NULL AUTO_INCREMENT,
> -> SI_CONTENT VARCHAR(160),
> -> SI_ANUMBER VARCHAR(30) NOT NULL,
> -> SI_BNUMBER VARCHAR(30) NOT NULL,
> -> SI_DATE_SENT DATETIME NOT NULL,
> -> SI_DATE_RECEIVED TIMESTAMP NOT NULL,
> -> SI_FK_SS_ID VARCHAR(50),
> -> PRIMARY KEY (SI_ID))
> -> TYPE = INNODB;
> Query OK, 0 rows affected (0.01 sec)
>
> mysql> ALTER TABLE SMS_IN
> -> ADD CONSTRAINT FOREIGN KEY (SI_FK_SS_ID)
> -> REFERENCES SMS_STATUS(SS_ID)
> -> ON DELETE SET NULL;
> ERROR 1005: Can't create table './test/#sql-1536_5.frm' (errno: 150)
>
>
> The same happens when I insert foreign key into the create statement.
> Does anybody knows what is the problem, and what should I do  to correct
> this problem?

>From the MySQL manual:

Both tables have to be InnoDB type and there must be an index where the 
foreign key and the referenced key are listed as the FIRST columns. InnoDB 
does not auto-create indexes on foreign keys or referenced keys: you have to 
create them explicitly.


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





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

re: MySQL

[Egor Egorov]

On Wednesday 19 February 2003 13:58, Prabu Subroto wrote:

> > I did something very stupid in my development. I
> > created a database with a couple of tables...but I
> > forgot to defining the foreign key and the primary
> > key
> > So if my user updated a record of one table than the
> > corresponding row on related table will not be
> > updated
> > too. That's why I had to write additional function
> > to
> > update the others related tables which are necessary
> > to be updated too.
> >
> > Is it possible to define Foreign Key and Primary Key
> > of my database after the tables have been created ?
> > If yes, how?

Yes, use ALTER TABLE statement:
http://www.mysql.com/doc/en/ALTER_TABLE.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




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Question about auto-increment columns

[Stefan Hinz]

Jedi,

> "The behavior of the auto-increment mechanism is not defined if a user gives
> a negative value to the column or if the value becomes bigger than the
> maximum integer that can be stored in the specified integer type." 

>   Does it mean that MySQL databases will definitely stop working at a random
> date (when an auto-incremental column will overflow) ?

Nope. Say you have a table with a column "id" of type TINYINT which
forms the primary key. The column definition in CREATE TABLE would be:

id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY

Now, if you don't enter data for this column manually, the first entry
will be "1", the second will be "2", etc. The maximum value for
TINYINT UNSIGNED is 255. So this means that when your table has 255
entries, MySQL would tried to set entry #256 to id = 256. This is not
possible, so MySQL will clip this value to 255. This, however, cannot
be entered because it would violate the uniqueness of the primary key.
As a result, you get an error, and the record is not inserted.

So you'll have to choose carefully what column type you need. For a
primary key, always use the UNSIGNED option. Regarding range of
values, you have this choice:

TINYINT: maximum 255
SMALLINT: maximum 65535
MEDIUMINT: maximum 16.7 million
INT: maximum 4.2 billion
BIGINT: no maximum (I can't count up to this ;-)

Details: http://www.mysql.com/doc/en/Numeric_types.html

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]


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: How to create a schema

[Ayyaparaju Ganapathiraju]

Thank you very much. That information is very helpful as I am just starting 
to use/learn MySQL.



Raju Ganapathiraju


From: "Roger Baklund" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
CC: "Ayyaparaju Ganapathiraju" <[EMAIL PROTECTED]>
Subject: Re: How to create a schema
Date: Wed, 19 Feb 2003 13:32:32 +0100

* Ayyaparaju Ganapathiraju
> 1). Is there an equivalent to creating a schema in MySQL. I would like 
to
> create tables in multiple schemas within the same database. Some of the
> tables will have foregign keys across schemas.

No, there is no consept of 'schema' in mysql, other than the implicit
'schema' resulting from the existing table definitions in a database.

> I'm aware that I can create multiple databases and switch between
> databases
> using "use ".

Yes, and you can also use multiple databases in the same sql statement,
using the database.table and database.table.column syntax:

select db1.table1.col1,db2.table2.col2
  from db1.table1,db2.table2
  where db1.table1.id = db2.table2.id

> 2). How can I edit a statement within mysql. Suppose if I make a
> typo in a large select statement, can I go into an editor and fix
> it, then execute it from the buffer.

If you have a new version of mysql, see the standard client \e command.

It is mentioned here, but I have not found any further documentation:

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

The \e command is not available in earlier versions of mysql, but I did not
find any mention of it in the change history, so I don't know when it 
became
available. Somewhere between versions 3.23.30 and 3.23.55.

I normally use a text editor, and then either copy&paste my statements
directly into the mysql client, or I execute it from a command line with:

mysql dbname < script.sql

(or "mysql -h host -u usrname -p dbname < script.sql")

HTH,

--
Roger


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail 
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



_
Add photos to your e-mail with MSN 8. Get 2 months FREE*.  
http://join.msn.com/?page=features/featuredemail


-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: MySQL

[Prabu Subroto]

Thank you my friend..
--- Jerry <[EMAIL PROTECTED]> wrote:
> http://www.mysql.com/doc/en/ALTER_TABLE.html
> 
> - Original Message -
> From: "Prabu Subroto" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Wednesday, February 19, 2003 11:58 AM
> Subject: MySQL
> 
> 
> >
> > --- [EMAIL PROTECTED] wrote:
> > > Your message cannot be posted because it appears
> to
> > > be either spam or
> > > simply off topic to our filter. To bypass the
> filter
> > > you must include
> > > one of the following words in your message:
> > >
> > > sql,query,queries,smallint
> > >
> > > If you just reply to this message, and include
> the
> > > entire text of it in the
> > > reply, your reply will go through. However, you
> > > should
> > > first review the text of the message to make
> sure it
> > > has something to do
> > > with MySQL. Just typing the word MySQL once will
> be
> > > sufficient, for example.
> > >
> > > You have written the following:
> > >
> > > Dear my Pals
> > > I did something very stupid in my development. I
> > > created a database with a couple of tables...but
> I
> > > forgot to defining the foreign key and the
> primary
> > > key
> > > So if my user updated a record of one table than
> the
> > > corresponding row on related table will not be
> > > updated
> > > too. That's why I had to write additional
> function
> > > to
> > > update the others related tables which are
> necessary
> > > to be updated too.
> > >
> > > Is it possible to define Foreign Key and Primary
> Key
> > > of my database after the tables have been
> created ?
> > > If yes, how?
> > >
> > > Thank you very much in advance.
> > >
> > >
> __
> > > Do you Yahoo!?
> > > Yahoo! Shopping - Send Flowers for Valentine's
> Day
> > > http://shopping.yahoo.com
> > >
> >
> > __
> > Do you Yahoo!?
> > Yahoo! Shopping - Send Flowers for Valentine's Day
> > http://shopping.yahoo.com
> >
> >
>
-
> > Before posting, please check:
> >http://www.mysql.com/manual.php   (the manual)
> >http://lists.mysql.com/   (the list
> archive)
> >
> > To request this thread, e-mail
> <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail
>
<[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try:
> http://lists.mysql.com/php/unsubscribe.php
> >
> 
> 
>
-
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list
> archive)
> 
> To request this thread, e-mail
> <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
>
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try:
> http://lists.mysql.com/php/unsubscribe.php
> 

__
Do you Yahoo!?
Yahoo! Shopping - Send Flowers for Valentine's Day
http://shopping.yahoo.com

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: MySQL

[Stefan Hinz]

Prabu,

>> Is it possible to define Foreign Key and Primary Key
>> of my database after the tables have been created ?
>> If yes, how?

Yes. Use the ALTER TABLE command for this. Here's how it works generally:

ALTER TABLE yourtablename
ADD [CONSTRAINT symbol] FOREIGN KEY (...) REFERENCES anothertablename(...)
[on_delete_and_on_update_actions]

Make sure the tables in question are all InnoDB tables.

And please read the appropriate manual section first:
http://www.mysql.com/doc/en/SEC455.html

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]


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: MySQL

[Jerry]

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

- Original Message -
From: "Prabu Subroto" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, February 19, 2003 11:58 AM
Subject: MySQL


>
> --- [EMAIL PROTECTED] wrote:
> > Your message cannot be posted because it appears to
> > be either spam or
> > simply off topic to our filter. To bypass the filter
> > you must include
> > one of the following words in your message:
> >
> > sql,query,queries,smallint
> >
> > If you just reply to this message, and include the
> > entire text of it in the
> > reply, your reply will go through. However, you
> > should
> > first review the text of the message to make sure it
> > has something to do
> > with MySQL. Just typing the word MySQL once will be
> > sufficient, for example.
> >
> > You have written the following:
> >
> > Dear my Pals
> > I did something very stupid in my development. I
> > created a database with a couple of tables...but I
> > forgot to defining the foreign key and the primary
> > key
> > So if my user updated a record of one table than the
> > corresponding row on related table will not be
> > updated
> > too. That's why I had to write additional function
> > to
> > update the others related tables which are necessary
> > to be updated too.
> >
> > Is it possible to define Foreign Key and Primary Key
> > of my database after the tables have been created ?
> > If yes, how?
> >
> > Thank you very much in advance.
> >
> > __
> > Do you Yahoo!?
> > Yahoo! Shopping - Send Flowers for Valentine's Day
> > http://shopping.yahoo.com
> >
>
> __
> Do you Yahoo!?
> Yahoo! Shopping - Send Flowers for Valentine's Day
> http://shopping.yahoo.com
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: How to create a schema

[Roger Baklund]

* Ayyaparaju Ganapathiraju
> 1). Is there an equivalent to creating a schema in MySQL. I would like to
> create tables in multiple schemas within the same database. Some of the
> tables will have foregign keys across schemas.

No, there is no consept of 'schema' in mysql, other than the implicit
'schema' resulting from the existing table definitions in a database.

> I'm aware that I can create multiple databases and switch between
> databases
> using "use ".

Yes, and you can also use multiple databases in the same sql statement,
using the database.table and database.table.column syntax:

select db1.table1.col1,db2.table2.col2
  from db1.table1,db2.table2
  where db1.table1.id = db2.table2.id

> 2). How can I edit a statement within mysql. Suppose if I make a
> typo in a large select statement, can I go into an editor and fix
> it, then execute it from the buffer.

If you have a new version of mysql, see the standard client \e command.

It is mentioned here, but I have not found any further documentation:

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

The \e command is not available in earlier versions of mysql, but I did not
find any mention of it in the change history, so I don't know when it became
available. Somewhere between versions 3.23.30 and 3.23.55.

I normally use a text editor, and then either copy&paste my statements
directly into the mysql client, or I execute it from a command line with:

mysql dbname < script.sql

(or "mysql -h host -u usrname -p dbname < script.sql")

HTH,

--
Roger


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

MySQL

[Prabu Subroto]

MySQL

__
Do you Yahoo!?
Yahoo! Shopping - Send Flowers for Valentine's Day
http://shopping.yahoo.com

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

MySQL

[Prabu Subroto]

--- [EMAIL PROTECTED] wrote:
> Your message cannot be posted because it appears to
> be either spam or
> simply off topic to our filter. To bypass the filter
> you must include
> one of the following words in your message:
> 
> sql,query,queries,smallint
> 
> If you just reply to this message, and include the
> entire text of it in the
> reply, your reply will go through. However, you
> should
> first review the text of the message to make sure it
> has something to do
> with MySQL. Just typing the word MySQL once will be
> sufficient, for example.
> 
> You have written the following:
> 
> Dear my Pals
> I did something very stupid in my development. I
> created a database with a couple of tables...but I
> forgot to defining the foreign key and the primary
> key
> So if my user updated a record of one table than the
> corresponding row on related table will not be
> updated
> too. That's why I had to write additional function
> to
> update the others related tables which are necessary
> to be updated too.
>  
> Is it possible to define Foreign Key and Primary Key
> of my database after the tables have been created ?
> If yes, how?
>  
> Thank you very much in advance. 
> 
> __
> Do you Yahoo!?
> Yahoo! Shopping - Send Flowers for Valentine's Day
> http://shopping.yahoo.com
> 

__
Do you Yahoo!?
Yahoo! Shopping - Send Flowers for Valentine's Day
http://shopping.yahoo.com

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Bug with uppercased database in 3.23.55?

[Juri Shimon]

Hi!
Is it a bug?

C:\mysql>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 29 to server version: 3.23.55-max-nt
 
mysql> create database R;
Query OK, 1 row affected (0.06 sec)

mysql> grant all privileges on `R%`.* to b;
Query OK, 0 rows affected (0.00 sec)
 
 
C:\mysql>mysql -u b R
ERROR 1044: Access denied for user: 'b@localhost' to database 'r'
 
On 3.23.48 it work OK...8(
 
WBR




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

S390 MySql

[Wolfgang . Schrecker]

We try to evaluate MySQL on Linux for IBM S390 but
the binary distribution fails with a cannont execute in my_print_defaults
and the source distribution errror is below:

>How-To-Repeat:
make reports the folling:

../innobase/row/librow.a(row0sel.o): In function
`row_search_for_mysql':
row0sel.o(.text+0x682c): undefined reference to `LC2950'
collect2: ld returned 1 exit status

trying to:g++ -O3 -DDBUG_OFF -fno-implicit-templates -fno-exceptions
-fno-rtti -rdynamic -
o mysqld sql_lex.o sql_handler.o item.o item_sum.o item_buff.o item_func.o
item_
cmpfunc.o item_strfunc.o item_timefunc.o thr_malloc.o item_create.o field.o
key.
o sql_class.o sql_list.o net_serv.o net_pkg.o lock.o my_lock.o sql_string.o
sql_
manager.o sql_map.o mysqld.o password.o hash_filo.o hostname.o convert.o
set_var
.o sql_parse.o sql_yacc.o sql_base.o table.o sql_select.o sql_insert.o
sql_updat
e.o sql_delete.o uniques.o sql_do.o procedure.o item_uniq.o sql_test.o
log.o log
_event.o init.o derror.o sql_acl.o unireg.o des_key_file.o time.o
opt_range.o op
t_sum.o opt_ft.o records.o filesort.o handler.o ha_heap.o ha_myisam.o
ha_myisamm
rg.o ha_berkeley.o ha_innodb.o ha_isam.o ha_isammrg.o sql_db.o sql_table.o
sql_r
ename.o sql_crypt.o sql_load.o mf_iocache.o field_conv.o sql_show.o
sql_udf.o sq
l_analyse.o sql_cache.o slave.o sql_repl.o sql_union.o mini_client.o
mini_client
_errors.o stacktrace.o repl_failsafe.o sql_olap.o  ../innobase/usr/libusr.a
../i
nnobase/odbc/libodbc.a ../innobase/srv/libsrv.a ../innobase/dict/libdict.a
../in
nobase/que/libque.a ../innobase/srv/libsrv.a ../innobase/ibuf/libibuf.a
../innob
nnobase/odbc/libodbc.a ../innobase/srv/libsrv.a ../innobase/dict/libdict.a
../in
nobase/que/libque.a ../innobase/srv/libsrv.a ../innobase/ibuf/libibuf.a
../innob
ase/row/librow.a ../innobase/pars/libpars.a ../innobase/btr/libbtr.a
../innobase
/trx/libtrx.a ../innobase/read/libread.a ../innobase/usr/libusr.a
../innobase/bu
f/libbuf.a ../innobase/ibuf/libibuf.a ../innobase/eval/libeval.a
../innobase/log
/liblog.a ../innobase/fsp/libfsp.a ../innobase/fut/libfut.a
../innobase/fil/libf
il.a ../innobase/lock/liblock.a ../innobase/mtr/libmtr.a
../innobase/page/libpag
e.a ../innobase/rem/librem.a ../innobase/thr/libthr.a
../innobase/com/libcom.a .
./innobase/sync/libsync.a ../innobase/data/libdata.a
../innobase/mach/libmach.a
../innobase/ha/libha.a ../innobase/dyn/libdyn.a ../innobase/
>Fix:


>Submitter-Id:  S390-Linux mysql compiling
>Originator:
>Organization:
>MySQL support: [none]
>Synopsis:  can NOT bind mysqld
>Severity:  critical
>Priority:  medium
>Category:  mysql
>Class: sw-bug
>Release:   mysql-4.0.9-gamma (Source distribution)

>Class: sw-bug
>Release:   mysql-4.0.9-gamma (Source distribution)

>C compiler:2.95.3
>C++ compiler:  2.95.3
>Environment:

System: Linux S390-Linux 2.2.16 #1 SMP Wed Nov 8 10:57:03 GMT 2000 s390
unknown
Architecture: s390

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/s390-suse-linux/2.95.2/specs
gcc version 2.95.2 19991024 (release)
Compilation info: CC='gcc'  CFLAGS='-Wimplicit -Wreturn-type -Wswitch
-Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses
-Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro -O3
-fno-omit-frame-pointer'  CXX='gcc'  CXXFLAGS='-Wimplicit -Wreturn-type
-Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses
-Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wsign-promo -Wreorder
-Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions
-fno-rtti -mcpu=pentiumpro -O3 -fno-omit-frame-pointer'  LDFLAGS=''
ASFLAGS=''
LIBC:
-rwxr-xr-x   1 root root  4165133 Nov  3  2000 /lib/libc.so.6
-rw-r--r--   1 root root 19187426 Nov  3  2000 /usr/lib/libc.a
-rw-r--r--   1 root root  178 Nov  3  2000 /usr/lib/libc.so
-rw-r--r--   1 root root  2077192 Nov  3  2000
/usr/lib/libc-client.a
lrwxrwxrwx   1 root root   20 Jan 31 11:47
/usr/lib/libc-client.so -> ./libc-client.so.4.7
-rwxr-xr-x   1 root root   699400 Nov  3  2000
/usr/lib/libc-client.so.4.7
Configure command: ./configure '--prefix=/usr/local/mysql' '
--enable-assembler' '--with-extra-charsets=complex' '
--enable-thread-safe-client' '--with-innodb' '--with-berkeley-db' '
--with-embedded-server' '--with-openssl' '--with-vio' '
--enable-local-infile' 'CFLAGS=-Wimplicit -Wreturn-type -Wswitch
-Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses
-Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro -O3
-fno-omit-frame-pointer' 'CXXFLAGS=-Wimplicit -Wreturn-type -Wswitch
-Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses
-Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wsign-promo -Wreorder
-Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions
-fno-rtti -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' 'CXX=gcc'


mit freundlichen Grüßen/best

InnoDB

[Rusch (ext) Reiner]

Hi,

I have one question about InnoDB-tables especial the filesize of all tables.
In the past I took MyISAM but found out, some things in InnoDB make the
system more stable.
But I want to get more free space. In MyISAM the space grows automatically,
but not in InnoDB by default.

In my /etc/my.cnf the line for the path and file size is not active like in
the original file.
But it works but with about 250-300MB max. size (don't know exactly).
If I uncomment this line to expand my space, mysql (4.0.10) doesn't start.
Don't know why.

Any hint?

Thank you.

Regards,
Reiner

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

SELECT * FROM different columns

[Reto Baudenbacher]

newbie-question, sorry...

is it possible to write this (working) query in a shorter form:

SELECT * FROM $table WHERE (col1 LIKE '%$term%') OR (col2 LIKE '%$term%') OR
(col3 LIKE '%$term%')

thanks for your help!
Reto Baudenbacher


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Error 1236?

[wertyu]

Hello.

I'm using replication both master and slave are MySQL 4.0.10.

But, When I insert many record( ten thousands of record) at a once,
I got follow error message.

###
030219 18:03:20  Error reading packet from server: binlog truncated in the
middle of event (server_errno=1236)
030219 18:03:20  Got fatal error 1236: 'binlog truncated in the middle of event'
from master when reading data from binary log
030219 18:03:20  Slave I/O thread exiting, read up to log 'test42-bin.003',
position 5329913
###

what does this mean? and what shoud I do?

somebody help me~

Replication status after error:

mysql> show slave status \G
*** 1. row ***
  Master_Host: 203.231.xx.xx
  Master_User: repli
  Master_Port: 3306
Connect_retry: 10
  Master_Log_File: test42-bin.003
  Read_Master_Log_Pos: 5329913
   Relay_Log_File: wertyu-relay-bin.002
Relay_Log_Pos: 5329973
Relay_Master_Log_File: test42-bin.003
 Slave_IO_Running: No
Slave_SQL_Running: Yes
  Replicate_do_db:
  Replicate_ignore_db: mysql
   Last_errno: 0
   Last_error:
 Skip_counter: 0
  Exec_master_log_pos: 5299624
  Relay_log_space: 5329977
1 row in set (0.00 sec)

mysql> show master status \G
*** 1. row ***
File: test42-bin.003
Position: 5393075
Binlog_do_db:
Binlog_ignore_db: intranet,mysql,repli_test,sfactive,test,traffic,wertyu
1 row in set (0.00 sec)


Thank you for advanced answer!

##
Heo, Jungsu Mr.
SimpleX Internet. http://www.simplexi.com

Filter : sql



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail 
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Connecting to remote server

[Jerry]

Using the hostname or IP adders of the machine it is on.
(B
(BJerry
(B
(B- Original Message -
(BFrom: "kk" <[EMAIL PROTECTED]>
(BTo: "list mysql" <[EMAIL PROTECTED]>
(BSent: Wednesday, February 19, 2003 9:24 AM
(BSubject: Connecting to remote server
(B
(B
(B> Hello folks
(B>   I just installed mysql4.x into the home/username directory of a remote
(B> machine. It is running good and I created a new user tester with all
(B> privileges.
(B> But how can i connect to the remote server running on a home directory
(Busing
(B> a front end tool like MySQLFront. It is listening to default port 3306.
(B>
(B> Any  lights?
(B>
(B> regards
(B>
(B>
(B>
(B> -
(B> Before posting, please check:
(B>http://www.mysql.com/manual.php   (the manual)
(B>http://lists.mysql.com/   (the list archive)
(B>
(B> To request this thread, e-mail <[EMAIL PROTECTED]>
(B> To unsubscribe, e-mail
(B<[EMAIL PROTECTED]>
(B> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
(B>
(B
(B
(B-
(BBefore posting, please check:
(B   http://www.mysql.com/manual.php   (the manual)
(B   http://lists.mysql.com/   (the list archive)
(B
(BTo request this thread, e-mail <[EMAIL PROTECTED]>
(BTo unsubscribe, e-mail <[EMAIL PROTECTED]>
(BTrouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

MySQL quitting, restarts only after hard reboot

[Bill Leonard]

This one is still biting us.. any suggestions on where to troubleshoot 
would be appreciated.

Mac OS X Server, 10.2.1 - MySQL 3.23.51

Heavy usage, but no more than 50 open connections at a time

Occasionally, MySQL just quits serving databases. The first indication 
is a forum or something comes up with a "could not connect to database" 
message.

Logging in to MySQL via the command line works, but a show databases 
command shows an empty set.

The error log in /var/mysql doesn't show anything helpful, only the 
last time it was started.

Re-starting mysqld from the command line doesn't work.

Only rebooting the machine seems to work, but clearly, there must be 
something I don't understand.

What could be going on that it exhibits this behavior? Any ideas?

Thanks much...

Bill


-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php