INTO OUTFILE error

[John Mistler]

Can anyone guess why I am getting this error:

"ERROR 1: Can't create/write to file
'/Users/johnmistler/Desktop/TestFile.txt' (ERRCODE 13)"

when using this statement:

mysql> SELECT * INTO OUTFILE '/Users/johnmistler/Desktop/TestFile.txt' FROM
theTable WHERE column1 != 'thisString';

?  I am logging in as root for the test.

Thanks,

John


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

last_insert_id() value not updated

[paqogomez]

I am trying to build a stored procedure in v. 5.
This is what I have so far.

delimiter |
create procedure get_id(out oid int)
begin
insert into mercury.merchant (name) values(null);
select last_insert_id() into @mid;
insert into mercury.customers( address1 ) values (null);
select last_insert_id() into @cid;
insert into mercury.item_information( description ) values (null);
select last_insert_id() into @iiid;
insert into mercury.fee_information ( delivery_fee ) values (null);
select last_insert_id() into @fiid;
insert into mercury.orders ( customer_id, merchant_id,
item_information_id, fee_information_id ) values ( @cid, @mid, @iiid,
@fiid );
select last_insert_id() into oid;
end
|

My problem is that last_insert_id() is not updated for each insert
statement, only on the connection.  The documentation says something about
using insert ignore, but I couldnt get this to give me any different
results.

Is there any way to do what I want here and still have the procedure be
transaction safe?

TIA
Chad

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

Re: Using IF clause

[Michael Stassen]

ColdFusion Lists wrote:
Hi
 
my table have an field when users can enter any chars
 
If users dont send any content for that field(phone), mysql store "( ) - "
without quotes in that

What im looking for:
 
Display IF phone field is (   )   - NOT ENTER
 
Otherwise display the phone of user.
 
It's possible? Using IF clause Mysql give me an error - maybe
 
IF(`users`.`phone` IS '(   )   - ','NOT ENTER',`users`.`phone`) AS phonefield;
 
What's happened?
 
Thanx for your time
In general, you should post your query and the error message you received. 
That would help us help you.

I expect the problem is your use of "IS" as a comparison operator in place 
of "=".  That won't work.  IS only works as part of IS NULL or IS NOT NULL.

I expect you want something like
  SELECT IF(users.phone = '(   )   - ','NOT ENTER',users.phone) phonefield
  FROM users;
It might be better if you modified your application to store NULL instead of 
"(   )   - " when no phone number is entered, or to require a phone number 
be entered, whichever is appropriate.  In the former case (you store NULLs), 
you would change the above query to:

  SELECT IF(users.phone IS NULL,'NOT ENTER',users.phone) phonefield
  FROM users;
Michael

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

Re: Root users permissions not working

[Michael Stassen]

> Peter,
> Actually,
>
> Mysql -uUSERNAME -pPASSWORD works just fine.
>
> Donny
And `mysql -u root -p somepassword` will not work.  With the space between 
-p and "somepassword", mysql will attempt to interpret "somepassword" as the 
name of a database.

As to the original question, the problem appears to be with root's 
privileges, not root's password.  First doublecheck that mysql believes you 
are [EMAIL PROTECTED] with

  SELECT CURRENT_USER();
Then, you can check your privileges with
  SHOW GRANTS FOR [EMAIL PROTECTED];
If they don't look right, you may be able to fix them with
  GRANT ALL ON *.* TO 'root'@'localhost' WITH GRANT OPTION
so long as you still have rights to the mysql db.  If that doesn't work, 
reply with the results.

Finally, you probably should not log in with
  mysql -u root -psomepassword
as it makes the password visible to others using ps at the right moment. 
Instead, just use

  mysql -u root -p
and let mysql prompt you for the password.
Michael
Donny Simonton wrote:

-Original Message-
From: Sunmaia [mailto:[EMAIL PROTECTED]
hi
this may be the problem or it may just be a typo!
you are showing
#mysql -u root -psomepassword
which should be
#mysql -u root -p somepassword
(note the space after -p)
the first will not pass the password, which would explain the lack of
privileges.
Otherwise start with skip-grant-tables and reset the root password.
If you do is there a possibility it may have been hacked?
Peter
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Hi Guys,
I have been using mysql for a year now without any problems.  Recently,
I just noticed that my root user is not able to create any databases when
I login.  I have tried to change my root password and try but it does not
work:
--
#mysql -u root -psomepassword
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 52 to server version: 3.23.58
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create database slugz;
ERROR 1044: Access denied for user: '[EMAIL PROTECTED]' to database 'slugz'
mysql>
--
I am able to do use databasename and show table commands but cannot make
any changes.  I have been able to before without any problems and I have
not made any configuration changes to mysql.  Is there something that I
might be missing?
Any help would be grately appreciated.
Thanks in advance,
-Simran

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

RE: Feature Request: UPDATE 'error codes' or mysql_affected_rows() to be more "accurate"

[Robert A. Rosenberg]

At 12:38 -0700 on 05/27/2004, Daevid Vincent wrote about Re: Feature 
Request: UPDATE 'error codes' or mysql_affected:

REPLACE INTO won't work, as I need the previous record (hence the update). I
store the first and last time I saw a node, amongst other info. REPLACE
would delete that data.
REPLACE would (might?) also fail if the Primary Key is some other 
table's Foreign Key (although this may be a permissible deletion 
since the record is not actually getting deleted but just the fields 
getting updated).

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

Re: Stop Scrolling

[Robert J Taylor]

[EMAIL PROTECTED] wrote:
Ultimately simple, but I don't find the answer:  how to stop a list 
from scrolling to the bottom when the command SHOW TABLES is entered.

I assume you are referring to the command line of the mysql client?
I use:
mysql>\P more
or
mysql>\P less
depending on my mood.
Other client tidbits can be found using \h or \? at the *> prompt.

Ken

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

Re: Perl arrays into MySQL

[Daniel Kasak]

Nik Belajcic wrote:
This may be a silly question, but I am wondering if there is something
opposite to:
@row = $sth->fetchrow_array
In other words, instead of fetching rows from MySQL and loading them
into an array that can be accessed from Perl, I want to do the opposite
- take a Perl (associative) array and load it into MySQL table.
I know I could dump it into a text file and read it from there, but
assuming I do not want to go through this intermediary step, how could I
do it? 

Any suggestions appreciated.
 

No there is not, but I urge to to implement it :)
You can do something like:
my %record; # Your record in memory you want in the table. It obviously 
needs to be filled

my $sql_1;
my $sql_2;
my $sql_3;
my $tablename = "MyTable"; # TODO: Set this another way?
foreach my $key (keys %record) {
   $sql_1 .= "$key, ";
   $sql_2 .= "?, ";
   $sql_3 .= %record{$key} . ", ";
}
# Chop off the trailing comma and space
$sql_1 = substr($sql_1, 0, length($sql_1)-2);
$sql_2 = substr($sql_2, 0, length($sql_2)-2);
$sql_3 = substr($sql_3, 0, length($sql_3)-2);
my $sth = $dbh->prepare("insert into $tablename ( $sql_1 ) values ( 
$sql_2 )");

$sth->execute($sql_3);
CAUTIONARY NOTE!!
I'm just learning Perl myself. The above code is NOT tested.
But you should be able to get the idea anyway :)
--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au
-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: error

[Terence]

i had the same problem,

for me it was here:
c:\mysql\lib\opt

and I copied it to
c:\mysql\bin\

that got me up and running. maybe it'll work for you.

cheers

- Original Message - 
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, June 01, 2004 10:38 AM
Subject: error


I've installed mysql on my windows 2000 system, with the windows installer.
When it's done and I double click on winmysql admin I get this error -

The dynamic link library LIBMYSQL.dll could not be found in the specified
path
C:\mysql\bin;.;C:\WINNET\system32;C:\WINNT\system

etc


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

Re: error

[Dan Bowkley]

sure that isn't c:\winnt\system32?  see if you can find the libmysql.dll
file on your computer and copy it into c:\winnt\system.  It's not the most
elegant solution...but it'll work.

- Original Message - 
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, May 31, 2004 7:38 PM
Subject: error


I've installed mysql on my windows 2000 system, with the windows installer.
When it's done and I double click on winmysql admin I get this error -

The dynamic link library LIBMYSQL.dll could not be found in the specified
path
C:\mysql\bin;.;C:\WINNET\system32;C:\WINNT\system

etc


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

error

[dtfanatic247]

I've installed mysql on my windows 2000 system, with the windows installer. When it's 
done and I double click on winmysql admin I get this error -

The dynamic link library LIBMYSQL.dll could not be found in the specified path
C:\mysql\bin;.;C:\WINNET\system32;C:\WINNT\system

etc

RE: 4.1 Beta

[Donny Simonton]

Jonathan,
I have no idea, but I can tell you about the stability of the 4.1 tree from
my experience.  If you are using MySQL for anything besides prepared
statements, I would say don't worry if it's beta or not.  It works and works
great!  The only reason I mention anything about prepared statements, is I
was upgrading a few machines from 4.1.1 to 4.1.2 so I decided to print out
the change log and read all of the new additions/bug fixes.  And to be it
seems like 45% of them were with prepared statements in one way or another.

We have been using 4.1 in a production environment since about a month after
4.1.0 was released.  And we have run into a bug or two now and then, but
that usually happens the first day we try something out.  Overall, I won't
install any other version.

Donny

> -Original Message-
> From: Jonathan Soong [mailto:[EMAIL PROTECTED]
> Sent: Monday, May 31, 2004 8:22 PM
> To: [EMAIL PROTECTED]
> Subject: 4.1 Beta
> 
> Hi Guys,
> 
> I'm just wondering if anyone could tell me when 4.1 will be moving to
> Beta?
> 
> I was told by a MySQL guy at Linux Conf AU 2004 (January) that 4.1 would
> be in Beta by March!!
> 
> As such, we have been developing applications on 4.1, but are hesitant
> to roll them into production on Alpha software.
> 
> I've looked all over the site and through the mailing list archives for
> an answer, and have been checking the Mysql site every week.
> 
> Does anyone know when 4.1 will be Beta!!!?? :)
> 
> Cheers
> 
> Jon
> 
> 
> --
> Jonathan Soong
> Information Services
> Institute of Medical and Veterinary Science (IMVS)
> Email:   [EMAIL PROTECTED]
> Web  :   http://www.imvs.sa.gov.au
> Tel  :   +61 8 82223095
> Fax  :   +61 8 82223147
> 
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> 




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

Perl arrays into MySQL

[Nik Belajcic]

This may be a silly question, but I am wondering if there is something
opposite to:

@row = $sth->fetchrow_array

In other words, instead of fetching rows from MySQL and loading them
into an array that can be accessed from Perl, I want to do the opposite
- take a Perl (associative) array and load it into MySQL table.
I know I could dump it into a text file and read it from there, but
assuming I do not want to go through this intermediary step, how could I
do it? 

Any suggestions appreciated.
-- 
Nik Belajcic <[EMAIL PROTECTED]>


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

4.1 Beta

[Jonathan Soong]

Hi Guys,
I'm just wondering if anyone could tell me when 4.1 will be moving to Beta?
I was told by a MySQL guy at Linux Conf AU 2004 (January) that 4.1 would 
be in Beta by March!!

As such, we have been developing applications on 4.1, but are hesitant 
to roll them into production on Alpha software.

I've looked all over the site and through the mailing list archives for 
an answer, and have been checking the Mysql site every week.

Does anyone know when 4.1 will be Beta!!!?? :)
Cheers
Jon
--
Jonathan Soong
Information Services
Institute of Medical and Veterinary Science (IMVS)
Email:   [EMAIL PROTECTED]
Web  :   http://www.imvs.sa.gov.au
Tel  :   +61 8 82223095
Fax  :   +61 8 82223147 

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

RE: Root users permissions not working

[Donny Simonton]

Peter,
Actually, 

Mysql -uUSERNAME -pPASSWORD works just fine.

Donny

> -Original Message-
> From: Sunmaia [mailto:[EMAIL PROTECTED]
> Sent: Monday, May 31, 2004 7:44 PM
> To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Subject: RE: Root users permissions not working
> 
>  hi
> 
> this may be the problem or it may just be a typo!
> 
> you are showing
> 
> #mysql -u root -psomepassword
> 
> which should be
> #mysql -u root -p somepassword
> (note the space after -p)
> 
> the first will not pass the password, which would explain the lack of
> privileges.
> 
> Otherwise start with skip-grant-tables and reset the root password.
> 
> If you do is there a possibility it may have been hacked?
> 
> Peter
> 
> 
> 
> 
> 
> > -Original Message-
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> > Sent: 01 June 2004 01:26
> > To: [EMAIL PROTECTED]
> > Subject: Root users permissions not working
> >
> >
> > Hi Guys,
> >
> > I have been using mysql for a year now without any problems.  Recently,
> I
> > just noticed that my root user is not able to create any databases when
> I
> > login.  I have tried to change my root password and try but it does not
> > work:
> >
> > --
> > #mysql -u root -psomepassword
> > Welcome to the MySQL monitor.  Commands end with ; or \g.
> > Your MySQL connection id is 52 to server version: 3.23.58
> >
> > Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
> >
> > mysql> create database slugz;
> > ERROR 1044: Access denied for user: '[EMAIL PROTECTED]' to database 'slugz'
> > mysql>
> > --
> >
> > I am able to do use databasename and show table commands but cannot make
> > any changes.  I have been able to before without any problems and I have
> > not made any configuration changes to mysql.  Is there something that I
> > might be missing?
> >
> > Any help would be grately appreciated.
> >
> > Thanks in advance,
> > -Simran
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
> > Email has been scanned  for viruses and SPAM by Trader Mailmanager
> > www.trader.uk.com
> >
> >
> >
> >
> 
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> 




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

RE: Root users permissions not working

[Sunmaia]

 hi

this may be the problem or it may just be a typo!

you are showing

#mysql -u root -psomepassword

which should be
#mysql -u root -p somepassword
(note the space after -p)

the first will not pass the password, which would explain the lack of
privileges.

Otherwise start with skip-grant-tables and reset the root password.

If you do is there a possibility it may have been hacked?

Peter





> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: 01 June 2004 01:26
> To: [EMAIL PROTECTED]
> Subject: Root users permissions not working
>
>
> Hi Guys,
>
> I have been using mysql for a year now without any problems.  Recently, I
> just noticed that my root user is not able to create any databases when I
> login.  I have tried to change my root password and try but it does not
> work:
>
> --
> #mysql -u root -psomepassword
> Welcome to the MySQL monitor.  Commands end with ; or \g.
> Your MySQL connection id is 52 to server version: 3.23.58
>
> Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
> mysql> create database slugz;
> ERROR 1044: Access denied for user: '[EMAIL PROTECTED]' to database 'slugz'
> mysql>
> --
>
> I am able to do use databasename and show table commands but cannot make
> any changes.  I have been able to before without any problems and I have
> not made any configuration changes to mysql.  Is there something that I
> might be missing?
>
> Any help would be grately appreciated.
>
> Thanks in advance,
> -Simran
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
> Email has been scanned  for viruses and SPAM by Trader Mailmanager
> www.trader.uk.com
>
>
>
>



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

Root users permissions not working

[linux]

Hi Guys,

I have been using mysql for a year now without any problems.  Recently, I
just noticed that my root user is not able to create any databases when I
login.  I have tried to change my root password and try but it does not
work:

--
#mysql -u root -psomepassword
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 52 to server version: 3.23.58

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database slugz;
ERROR 1044: Access denied for user: '[EMAIL PROTECTED]' to database 'slugz'
mysql>
--

I am able to do use databasename and show table commands but cannot make
any changes.  I have been able to before without any problems and I have
not made any configuration changes to mysql.  Is there something that I
might be missing?

Any help would be grately appreciated.

Thanks in advance,
-Simran

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

optimize table - OK to abort?

[Dennis T Cook]

I've been running OPTIMIZE TABLE on a MyISAM table with an index on two
varchar type columns. The process status shows as "Repair with 
keycache". The table has 73 million rows. OPTIMIZE TABLE has
been running for several days with constant disk activity but low cpu 
usage and no way to estimate whether it's any where close to finishing. 
If I abort the mysql process doing the optimization, will the table or 
the index be damaged? The system is a development system and is really 
sluggish while the optimize is going on.
-- 
Dennis T.Cook


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

Using IF clause

[ColdFusion Lists]

Hi
 
 
my table have an field when users can enter any chars
 
If users dont send any content for that field(phone), mysql store "(   )   - " without 
quotes in that
 
 
What im looking for:
 
Display IF phone field is (   )   - NOT ENTER
 
Otherwise display the phone of user.
 
It's possible? Using IF clause Mysql give me an error - maybe
 
IF(`users`.`phone` IS '(   )   - ','NOT ENTER',`users`.`phone`) AS phonefield;
 
What's happened?
 
Thanx for your time


Diga-me e eu esquecerei
Mostre-me e lembrarei
Ensina-me e aprenderei


-
Yahoo! Mail - Participe da pesquisa global sobre o Yahoo! Mail. Clique aqui!

syntax error

[Bob Lockie]

I want to do something like this:
delete from records where records.id in (select audit_log_records.id 
from audit_log_records, audit_log where 
audit_log.tracker_id=audit_log_records.tracker_id and 
audit_log.operation='D');

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

Re: Select data from two different databases

[Chris]

Oropeza Querejeta, Alejandro wrote:
Hi, two questions:
Is it possible to select data from different Databases on the same
server?
Is it possible to select data from different Databases on Different
servers across the network?
I searched for information on how to do it, but i haven't found any.
Best Regards
Alejandro
 

On the same server just do this:
SELECT dbname.tablename.columnname,db2name.table2name.column2name FROM 
dbname.tablename, db2name.table2name; (I think you get the idea)

As far as I know you can't select data across multiple servers.
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Stop Scrolling

[Chris]

Rhino wrote:
- Original Message - 
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, May 31, 2004 2:20 PM
Subject: Stop Scrolling

 

Ultimately simple, but I don't find the answer:  how to stop a list from
scrolling to the bottom when the command SHOW TABLES is entered.
   

I was wondering that myself so I took a look in the manual and found
something promising: pager. See
http://dev.mysql.com/doc/mysql/en/mysql_Commands.html
Based on that article, it *looks* like the 'pager' command can be set to
'more' or 'less' or 'cat' or whatever you usually use. I can't quite
understand how to use it though. Maybe you can make sense of it though. If
you figure it out, maybe you could post a short explanation for the rest of
us ;-)
Rhino
 

I use it like this:
mysql>pager less -n -i -S
Then, every query output will be run through less (with the attributes 
-n -i -S, you can change these). It only works in the Unix MySQL client.

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

Re: [MySQL] Re: Stop Scrolling

[Ashley M. Kirchner]

Rhino wrote:
Ultimately simple, but I don't find the answer:  how to stop a list from
scrolling to the bottom when the command SHOW TABLES is entered.
I was wondering that myself so I took a look in the manual and found
something promising: pager. See
http://dev.mysql.com/doc/mysql/en/mysql_Commands.html
   > mysql -p [database]
   Enter password:
   mysql> pager more;
   PAGER set to more
   mysql> select * from [table];
   ...data...
   ...data...
   --More--
   You can use 'less', or 'more', or whatever other pager you want to use.
--
W | I haven't lost my mind; it's backed up on tape somewhere.
 +
 Ashley M. Kirchner    .   303.442.6410 x130
 IT Director / SysAdmin / WebSmith . 800.441.3873 x130
 Photo Craft Laboratories, Inc.. 3550 Arapahoe Ave. #6
 http://www.pcraft.com . .  ..   Boulder, CO 80303, U.S.A.

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

Re: Stop Scrolling

[Rhino]

- Original Message - 
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, May 31, 2004 2:20 PM
Subject: Stop Scrolling


> Ultimately simple, but I don't find the answer:  how to stop a list from
> scrolling to the bottom when the command SHOW TABLES is entered.
>
I was wondering that myself so I took a look in the manual and found
something promising: pager. See
http://dev.mysql.com/doc/mysql/en/mysql_Commands.html

Based on that article, it *looks* like the 'pager' command can be set to
'more' or 'less' or 'cat' or whatever you usually use. I can't quite
understand how to use it though. Maybe you can make sense of it though. If
you figure it out, maybe you could post a short explanation for the rest of
us ;-)

Rhino


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

Select data from two different databases

[Oropeza Querejeta, Alejandro]

Hi, two questions:
 Is it possible to select data from different Databases on the same
server?
Is it possible to select data from different Databases on Different
servers across the network?
 
I searched for information on how to do it, but i haven't found any.
 
Best Regards
Alejandro
 

Re: update synopsis error

[Michael Kruckenberg]

What version of MySQL are you using? Subqueries aren't available until 
4.1.x.

saiph wrote:
hi,
mysql> update table1
set c = (select c2 from table2 where c2 = 'value')
where  id = 123;
give me an ERROR 1064.
i.e.
create table t1 ( id int primary key, name varchar(20) );
create table t2 ( id int primary key, name varchar(20) );
insert into t1 values(1, 'not right')
insert into t2 values(1, 'right')
update t1 set name = (select name from t2 where id = 1) where id = 1;
how i can update right?
--
http://mike.kruckenberg.com | [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Questions on index and 'order by'

[Sasha Pachev]

Bernard Chambon wrote:
I try to understand index usage for 'order by'
but that's seemn hard to me.
Here is what I have tried :
1_ About documentation
I have read carefully related documentation (7.2.9  How MySQL Optimizes  
ORDER BY) but
I don't understand it.
 - What is the difference betwween 'key1' and 'key_part1' ?
 - Why is it written ? :
   The following query will use the index to resolve the ORDER BY
 SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2

   The following query cannot use indexes to resolve the ORDER BY
 SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;
2_ I also tried a small example (table Test), with 2 fields and 2  
(multiple) indexes

 create table Test(Name varchar(64),  Adress varchar(128), \
   index K0(Name(16), Adress(16)), \
   index K1(Adress(16), Name(16)) ) type = INNOBASE   ;
   What are the indexes needed for 'orber by' in the  request
  select * from Test  where Name='n1'  order by Adress ;
   If I ask explain, I got 'Using filesort' in Extra column information
mysql> explain select * from Test  where Name='n1'  order by Adress  ;
 +---+--+---+--+-+---+-- 
+-+
| table | type | possible_keys | key  | key_len | ref   | rows |  
Extra   |
 +---+--+---+--+-+---+-- 
+-+
| Test  | ref  | K0| K0   |  17 | const |1 |  
Using where; Using filesort |
 +---+--+---+--+-+---+-- 
+-+
Bernard:
The problem is that you are indexing only a prefix of the address in the 
(name,address). Try key(name(16),address). In general, order by on a key or key 
part cannot use prefix-indexed fields.

--
Sasha Pachev
Create online surveys at http://www.surveyz.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

compile mysql cluster

[Xianan Zhang]

Hi,
I am trying to compile MySQL cluster source code on linux RedHat 9. The software 
required are all installed with the right versions. And I use the file 
BUILD/compile-pentium-max as suggested in Introduction documents. But the compilation 
fails, and I got some output about errors as below.
Could anybody give me some suggestion on how I can fix it? Thanks a lot.

Xianan

---

if --preserve-dup-deps --mode=compile gcc 
-DDEFAULT_CHARSET_HOME="\"/usr/local/mysql\"" -DDATADIR="\"/usr/local/mysql/var\"" 
-DSHAREDIR="\"/usr/local/mysql/share/mysql\"" -DDONT_USE_RAID  -I. -I. -I.. 
-I../include -I/usr/kerberos/include-O3 -DDBUG_OFF -Wimplicit -Wreturn-type 
-Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses 
-Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro -O3 -fno-omit-frame-pointer 
-g   -MT libmysql.lo -MD -MP -MF ".deps/libmysql.Tpo" \
  -c -o libmysql.lo `test -f 'libmysql.c' || echo './'`libmysql.c; \
then mv -f ".deps/libmysql.Tpo" ".deps/libmysql.Plo"; \
else rm -f ".deps/libmysql.Tpo"; exit 1; \
fi
/bin/sh: line 1: --preserve-dup-deps: command not found
gmake[2]: *** [libmysql.lo] Error 1
gmake[2]: Leaving directory `/home/globus/install/MySQL/cluster/mysql-4.1/libmysql_r'
gmake[1]: *** [all-recursive] Error 1
gmake[1]: Leaving directory `/home/globus/install/MySQL/cluster/mysql-4.1'
gmake: *** [all] Error 2

Re: Feature Request: UPDATE 'error codes' or mysql_affected_rows() to be more "accurate"

[Sasha Pachev]

Daevid Vincent wrote:
Well, my "hack" (which is sort of like what you suggest) is to change my
primary key from just an auto_increment 'id' field to a combination of two
other fields (mac/scanner_id) that I know must be unique. Then I rely upon
the fact that mySQL will not allow a duplicate PK. (I did say it was a
hack). A co-worker assures me that a SELECT is cheap, however a version I
tried (without my hack) still allowed duplicates to slip through because I
wasn't locking the tables. I have multiple scanners hitting the same table
and locking seems to me a bad idea.
Also, I guess my TIMESTAMP brainstorm won't work b/c the resolution of that
field is 1 second and these queries happen faster than that. *Neuman!*  :-/
REPLACE INTO won't work, as I need the previous record (hence the update). I
store the first and last time I saw a node, amongst other info. REPLACE
would delete that data.
http://daevid.com
Daevid:
I believe what Steve suggests is the cleanest solution under the assumption you 
are willing to move to 4.1. If not, your college has a very good point - any 
one-row operation on MySQL is very fast - on modern hardware (AMD XP 2200+ or 
faster) you are looking at the order of magnitude of 10,000 per seconds.

--
Sasha Pachev
Create online surveys at http://www.surveyz.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: rework this

[Bob Lockie]

On 05/31/04 15:42 Donny Simonton spoke:
It depends on which version of MySQL you are using.  Since you are using a
subquery, I assume you are using a fairly new version.
Delete audit_log_records from audit_log_records, audit_log where
audit_log_records.tracker_id = audit_log.tracker_id and
audit_log.operation='A'
That should be it.  It's called a multi-table delete, but you are only
deleting from one table.  You can't use left join or inner join with a
multi-table delete or update.  You have to do a comma join.
Donny
Great, thanks.
I have MySQL-4.0.18 and that worked.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: rework this

[Donny Simonton]

It depends on which version of MySQL you are using.  Since you are using a
subquery, I assume you are using a fairly new version.

Delete audit_log_records from audit_log_records, audit_log where
audit_log_records.tracker_id = audit_log.tracker_id and
audit_log.operation='A'

That should be it.  It's called a multi-table delete, but you are only
deleting from one table.  You can't use left join or inner join with a
multi-table delete or update.  You have to do a comma join.

Donny

> -Original Message-
> From: Bob Lockie [mailto:[EMAIL PROTECTED]
> Sent: Monday, May 31, 2004 2:37 PM
> To: MySQL Mailing List
> Subject: rework this
> 
> I need help with this SQL.
> I want to delete all records from the 'audit_log_records' table that
> have 'tracker_id' columns that are the same as those from the audit_log
> table that have 'A' for the operation type.
> 
> delete from audit_log_records where tracker_id=(select tracker_id from
> audit_log where audit_log.operation='A');
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> 




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

rework this

[Bob Lockie]

I need help with this SQL.
I want to delete all records from the 'audit_log_records' table that 
have 'tracker_id' columns that are the same as those from the audit_log 
table that have 'A' for the operation type.

delete from audit_log_records where tracker_id=(select tracker_id from 
audit_log where audit_log.operation='A');

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

Stop Scrolling

[kc68]

Ultimately simple, but I don't find the answer:  how to stop a list from 
scrolling to the bottom when the command SHOW TABLES is entered.

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

Re: MySQL sharing data over NFS ??

[Jeff Smelser]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Monday 31 May 2004 12:08 pm, Kirti S. Bajwa wrote:
> Hello List:
>
> I want to setup two servers (later more servers added) sharing mysql data
> as a NFS Server & Client relationship;

There is a document on the mysql web site that cleary states that unless this 
is a read only database, this is going to give you a lot of problems.. Did 
you not see this article?

Thanks,
Jeff

- -- 
Beer wants to be free.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.4 (GNU/Linux)

iD8DBQFAu3L0ld4MRA3gEwYRAsOTAJwPcv6AIOf3+SM0z029zk27CdaWpwCdHCnA
w5DcJ3bha2O38JpTXVa2Vj0=
=3VUL
-END PGP SIGNATURE-

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

RE: independent database

[Laercio Xisto Braga Cavalcanti]

Hi Marco,

You can create one database for each user, and grant access to this database
only for this user.

Laercio.

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: segunda-feira, 31 de maio de 2004 14:40
To: [EMAIL PROTECTED]
Subject: independent database

Hi all!

I would like that my users can read/write/execute just them database and
that they can not read/write/execute other users's database.

How shoul I set up?

This is because I have to host some web site and I want to give independent
access to features.

Tnks!

marco

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


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

independent database

[marco]

Hi all!

I would like that my users can read/write/execute just them database and
that they can not read/write/execute other users's database.

How shoul I set up?

This is because I have to host some web site and I want to give
independent access to features.

Tnks!

marco

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

MySQL sharing data over NFS ??

[Kirti S. Bajwa]

Hello List:

I want to setup two servers (later more servers added) sharing mysql data as
a NFS Server & Client relationship; 

Server-A:
--
RH9, mysql-4.0.20, NFS Master
This server is a DATA server. All data for the entire site is kept on this
server in "/usr/local/mysql/var" which is symbolically linked to "/rad"
folder. "/rad" folder consists of two IDE disks mirrored under RAID1.

Everything is working fine on this server.


Server-B:
-
RH9, mysql-4.0.20, NFS Client. This server is a MAIL server. I am following
http://www.pipeline.com.au/staff/mbowe/isp/webmail-server.htm to install
various ( qmail, vpopmail, courier-map, qmail-scanner, clam, etc.) mail
server software.

Question:
-
I want to setup Server-B so that all mysql data lies on and shared from the
DATA (Server-A) server. I know how to create a mount point "/rad" on
server-B. I would like to know how the "/rad" mount point is incorporated as
location of mysql data during the mysql installation on Server-B.

For Example: When I install mysql software on Server-B as follows:

 % cd  /usr/local
% gunzip  <  mysql-4.0.20.tar.gz  | tar -xvf   -
% cd  mysql-version
% ./configure -prefix=/usr/local/mysql
% make
% make install
% scripts/mysql_install_db
% ...

At this point the mysql data is in "/usr/local/mysql/var". What do I need to
do so that the Server-B points to Server-A for all mysql data retrieval
and/or updates. I have to do a full installation of MySQL because other
software on Server-B has dependency on mysql software! If somebody has done
such an installation, please direct me to a solution.

I researched GOOGLE extensively & have read the official mysql documentation
& have not found an answer. It has been a chore to explain this problem. If
my explanation is confusing, please let me know and I will answer your
question. It seems to be very simple, but it is beyond me. 

I need help badly.

Thanks in advance.

Kirti

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

Re: "merge tables for big log files ?"

[Jeremy Cole]

Hi Michael,
Problem for this variant: merge table has to be dropped and recreated
  periodically.
  during the short lag interval the log merge table
  does not exist
You don't have to drop and re-create the table.  Please don't edit the MRG 
file directly either.

You can accomplish this easily by using the ALTER TABLE statement:
CREATE TABLE t1 (id INT NOT NULL, c CHAR(10) NOT NULL, PRIMARY KEY(id));
CREATE TABLE t2 (id INT NOT NULL, c CHAR(10) NOT NULL, PRIMARY KEY(id));
CREATE TABLE t_merge (id INT NOT NULL, c CHAR(10) NOT NULL, KEY(id)) 
TYPE=MERGE UNION=(t1, t2);

So now you have a MERGE table containing both tables.  If you want to add a 
third one later:

CREATE TABLE t3 (id INT NOT NULL, c CHAR(10) NOT NULL, PRIMARY KEY(id));
ALTER TABLE t_merge UNION=(t1, t2, t3);
Using ALTER TABLE with a MERGE table is an atomic operation and can be done 
while the server is up and running full-speed without any problems.

Regards,
Jeremy
--
__  ___ ___   __
   /  |/  /_ __/ __/ __ \/ /Mr. Jeremy Cole <[EMAIL PROTECTED]>
  / /|_/ / // /\ \/ /_/ / /__   MySQL AB, Training and Consulting Manager
 /_/  /_/\_, /___/\___\_\___/   Streetsboro, Ohio  USA
<___/   www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: update synopsis error

[Egor Egorov]

saiph <[EMAIL PROTECTED]> wrote:
> 
> mysql> update table1
> set c = (select c2 from table2 where c2 = 'value')
> where  id = 123;
> 
> give me an ERROR 1064.
> 
> 
> i.e.
> 
> create table t1 ( id int primary key, name varchar(20) );
> create table t2 ( id int primary key, name varchar(20) );
> 
> insert into t1 values(1, 'not right')
> insert into t2 values(1, 'right')
> 
> update t1 set name = (select name from t2 where id = 1) where id = 1;
> 
> how i can update right?
> 

Subqueries are supported since 4.1.0.
If you use 4.0.x you can update t1 with the following statement:

UPDATE t1, t2 SET t1.name=t2.name WHERE t1.id=1 AND t2.id=1;

If you use version 3.23.xx you can't do it with one query.



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




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

Re: Trigger and StoreProc Examples

[Victoria Reznichenko]

"Carlos J Souza" <[EMAIL PROTECTED]> wrote:
> Hello ALL,
> 
> I need a examples of stored procedures and triggers in mysql 5.0.
> 
> Please send me all sintaxes of this
> 

Triggers are not supported in v5.0.
Example of stored procedure you can find in the manual at:
http://dev.mysql.com/doc/mysql/en/CREATE_PROCEDURE.html


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





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

Trigger and StoreProc Examples

[Carlos J Souza]

Hello ALL,

I need a examples of stored procedures and triggers in mysql 5.0.

Please send me all sintaxes of this

regards

Carlos J Souza
[EMAIL PROTECTED]
2004-05-31


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

update synopsis error

[saiph]

hi,

mysql> update table1
set c = (select c2 from table2 where c2 = 'value')
where  id = 123;

give me an ERROR 1064.


i.e.

create table t1 ( id int primary key, name varchar(20) );
create table t2 ( id int primary key, name varchar(20) );

insert into t1 values(1, 'not right')
insert into t2 values(1, 'right')

update t1 set name = (select name from t2 where id = 1) where id = 1;

how i can update right?


tnx.

--

>here are more things in heaven and earth,

horatio, than are dreamt of in your philosophy.

---

-- 
>here are more things in heaven and earth,
horatio, than are dreamt of in your philosophy.

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

redhat9 multiple instances walkthrough

[Minuk Choi]

Hey, I have RedHat 9, with the default MySQL RPM pre-installed.

I'd like to set up multiple instances of MySQL.

That is, have multiple data directories(all with separate mysql database and
tables) on different ports.(I have a few projects which "share" user names
but different privileges).

What I need, because I'm new at this is a walkthrough.

I would appreciate any help you can provide, as I tried to read and follow
the online documentation regarding "mysqld_multi" but I couldn't make heads
or tails out of it - I need some working examples.

Thanks in advance,
-Minuk


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

Re: MySQL Benchmark. OT and beyond...

[RV Tec]

Folks,

> FreeBSD 5.x does a lot of things really well, and it can be very
> fast too, but there's no stable release of 5.x yet, and the 5.x
> code is clearly hampered speedwise by the presence of loads of
> debugging code. From the 5.x documentation:
>
> ---
> NOTE TO PEOPLE WHO THINK THAT FreeBSD 5.x IS SLOW:
>  FreeBSD 5.x has many debugging features turned on, in
>  both the kernel and userland.  These features attempt to detect
>  incorrect use of system primitives, and encourage loud failure
>  through extra sanity checking and fail stop semantics.  They
>  also substantially impact system performance.  If you want to
>  do performance measurement, benchmarking, and optimization,
>  you'll want to turn them off.  This includes various WITNESS-
>  related kernel options, INVARIANTS, malloc debugging flags
>  in userland, and various verbose features in the kernel.  Many
>  developers choose to disable these features on build machines
>  to maximize performance.
> ---
>
> If Linux is really twice as fast as FreeBSD, as people have reported
> recently, then go ahead and use it if that's what you want. But I
> hope everyone reporting this is using optimized software, and isn't
> complaining about a FreeBSD that's explicitly slowed down by running
> under gobs of debugging code.
>
> So I would imagine you'd see a huge speedbump from "downgrading" to 4.10.

If it makes any sense, I did turn off everything related to DEBUG at the
kernel. I also removed support of *TRACE. Also did a make world before
compiling MySQL (thus, before the trials).

It was really strange to see that kind of performance, when the expected
was a lot higher. I can't remember if I shared the results with MySQL
4.0.20 running on a FreeBSD 5.2.1 with pthreads, but they weren't that
different from LinuxThreads.

As I write this e-mail, I'm configuring a Linux (Debian) box, later on
I'll share those results.

Best regards,
RV Tec

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

Re: Unexpected subquery behaviour

[Victoria Reznichenko]

Monday, May 31, 2004, 12:28:20 PM, you wrote:

J> I'm sorry, but I'm using Windows. There is no 4.1.2 version available yet

Ok.
Could you create a repeatable test case? You can upload structure of tables and data 
for testing to ftp://ftp.mysql.com/pub/mysql/upload/ and let me know a file name.

J> - Original Message ---

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





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

Re: "merge tables for big log files ?"

[Chris Elsworth]

On Mon, May 31, 2004 at 02:03:09PM +0200, Michael Arndt wrote:
> 
> Problem for this variant: merge table has to be dropped and recreated
>   periodically.
>   during the short lag interval the log merge table
>   does not exist

You don't have to drop and recreate it in MySQL. You can do it under
MySQL's feet without it noticing. The .MRG definition file is simply a
list of tables that make up the merge, with a couple of optional
configuration likes; the only one I use is #INSERT_METHOD.

You can create a new .MRG file very easily in Perl, consisting of the
new range of tables, then move it over the old .MRG, so in one atomic
operation the merge table now has a new subtable; in order to get
MySQL to notice however you have to do a flush tables. You can use
mysqladmin flush-tables to do that.

For insertion, this is where #INSERT_METHOD comes in handy. MySQL can
either insert to the first or last table:

$ tail -3 MessageIDs.MRG
MessageIDs_20040529
MessageIDs_20040530
#INSERT_METHOD=LAST

Now INSERT INTO merge_table will be inserting into
MessageIDs_20040530, which is today. Just after midnight, my Perl runs
and makes a new .MRG, and after the flush MySQL is inserting into the
next table without even realising.
-- 
Chris

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

Re: MySQL Benchmark. OT and beyond...

[Jesse Sheidlower]

On Thu, May 27, 2004 at 02:20:46PM -0400, RV Tec wrote:
> 
> Eric,
> 
> 
> > I am sure all FreeBSD users are sick and tired of saying this. USE
> > 4.10
> > Lots of people blindly follow version numbers but 5.x is a lot different
> > than 4.x in ways I don't yet feel comfortable with using on production
> > machines. Only one of those issues is with benchmark numbers, but that
> > is certainly one of them.
> 
> If you don't feel comfortable with 5_branch on production, that's fine, I
> respect your choice. On the other hand, we have lots of official
> documentation saying that LinuxThreads, threading, memory handling, disk
> access is improved in 5_branch (compared to 4).
> 
> I'm looking for an OS that can handle my database. OpenBSD -- which, I
> just proved by a test machine -- performs better than FreeBSD. That's why
> I can't even imagine how poorly FreeBSD 4.10 (which has just been
> released) would handle my database.

Probably much better. 

FreeBSD 5.x does a lot of things really well, and it can be very
fast too, but there's no stable release of 5.x yet, and the 5.x
code is clearly hampered speedwise by the presence of loads of
debugging code. From the 5.x documentation:

---
NOTE TO PEOPLE WHO THINK THAT FreeBSD 5.x IS SLOW:
 FreeBSD 5.x has many debugging features turned on, in
 both the kernel and userland.  These features attempt to detect
 incorrect use of system primitives, and encourage loud failure
 through extra sanity checking and fail stop semantics.  They
 also substantially impact system performance.  If you want to
 do performance measurement, benchmarking, and optimization,
 you'll want to turn them off.  This includes various WITNESS-
 related kernel options, INVARIANTS, malloc debugging flags
 in userland, and various verbose features in the kernel.  Many
 developers choose to disable these features on build machines
 to maximize performance.
---

If Linux is really twice as fast as FreeBSD, as people have reported
recently, then go ahead and use it if that's what you want. But I 
hope everyone reporting this is using optimized software, and isn't
complaining about a FreeBSD that's explicitly slowed down by running
under gobs of debugging code.

So I would imagine you'd see a huge speedbump from "downgrading" to 4.10.

Jesse Sheidlower

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

"merge tables for big log files ?"

[Michael Arndt]

Hello *,

goal: using merge tables for annual / monthly storage of syslog information 
  using php-syslog-ng and syslog-ng

problem: 

-one "actual table" must be available for inserts
-the merge table needs to be the target for "reports and queries"
-static parts of the merge table should be compressed

question: 

what is the best strategy for grouping / using a merge table ?

a) have one separate table for continous syslog data
   and synchronise this table regularily to e.g. a monthly table log_2004_06 
  
  ("log_2004_06" is Part of a merge table "log_global" to enable global access 
   to all monthly logs with php-syslog-ng)

   syslog-ng -> log
   log --(INSERT INTO) -> log_2004_06
   log_global ( log_2004_12,log2004_11,log_2004_10,...) 

b) let the "insert table" for syslog-ng already be part of the merge table 
   and reordering the merge table on a regularly schema, to be
   able to compress all old,static tables.
   log ( log_2004_12,log2004_11,log_2004_10,...)

Problem for this variant: merge table has to be dropped and recreated
  periodically.
  during the short lag interval the log merge table
  does not exist

anyone has done something like this already ?

TIA
Micha
 

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

Questions on index and 'order by'

[Bernard Chambon]

I try to understand index usage for 'order by'
but that's seemn hard to me.
Here is what I have tried :
1_ About documentation
I have read carefully related documentation (7.2.9  How MySQL Optimizes  
ORDER BY) but
I don't understand it.
 - What is the difference betwween 'key1' and 'key_part1' ?
 - Why is it written ? :
   The following query will use the index to resolve the ORDER BY
 SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2

   The following query cannot use indexes to resolve the ORDER BY
 SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;
2_ I also tried a small example (table Test), with 2 fields and 2  
(multiple) indexes

 create table Test(Name varchar(64),  Adress varchar(128), \
   index K0(Name(16), Adress(16)), \
   index K1(Adress(16), Name(16)) ) type = INNOBASE  
 ;

   What are the indexes needed for 'orber by' in the  request
  select * from Test  where Name='n1'  order by Adress ;
   If I ask explain, I got 'Using filesort' in Extra column information
mysql> explain select * from Test  where Name='n1'  order by Adress  
;
 
+---+--+---+--+-+---+-- 
+-+
| table | type | possible_keys | key  | key_len | ref   | rows |  
Extra   |
 
+---+--+---+--+-+---+-- 
+-+
| Test  | ref  | K0| K0   |  17 | const |1 |  
Using where; Using filesort |
 
+---+--+---+--+-+---+-- 
+-+

Thanks for any help
-
Bernard Chambon
IN2P3 / CNRS
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

MySQL update 4.0.20 w/RPM's

[Mike Blezien]

Hello,
got this error when upgrading our MySQL:
rpm -Uvh MySQL-shared-4.0.20-0.i386.rpm
Preparing...### [100%]
file /usr/lib/libmysqlclient.so.12.0.0 from install of MySQL-shared-4.0.20-0 
conflicts with file from package MySQL-shared-compat-4.0.18-0
file /usr/lib/libmysqlclient_r.so.12.0.0 from install of MySQL-shared-4.0.20-0 
conflicts with file from package MySQL-shared-compat-4.0.18-0

MySQL upgraded to version 4.0.20 with no apparent problems so far, from 4.0.18 
but was wondering if this could be a problem down the road or should we remove 
the old shared or shared-compact RPM's, then do the upgrade to the 4.0.20 RPM's ??

These are the current installed RPM's, O/S RH/Linux 7.3:
MySQL-bench-4.0.18-0
MySQL-embedded-4.0.20-0
MySQL-shared-4.0.18-0
MySQL-server-4.0.20-0
MySQL-devel-4.0.20-0
MySQL-shared-compat-4.0.18-0
MySQL-client-4.0.20-0
--
MikeBlezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://www.thunder-rain.com
Quality Web Hosting
http://www.justlightening.net
MSN Messager: [EMAIL PROTECTED]
Cell:1.985.320.1191 - Office:1.712.737.2548
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Unexpected subquery behaviour

[Victoria Reznichenko]

Jaime <[EMAIL PROTECTED]> wrote:
> 
> I'm using subqueries in MySQL 4.1.1 alpha. I use three subqueries and then want to 
> get those combinations matching a given constraints. It is a very simple task, but I 
> get less results than I expected. In fact, I tried to execute the same query in 
> ORACLE and I get 23 results (in MySQL I only get 9).
> 
> I wanted to know whether subqueries are not fully developed in this alpha version or 
> it is because MySQL behaves differently. Here is the query:
> 
> SELECT i1.id_document , i1.id_item , i2.id_item
> FROM
>   (SELECT DISTINCT j.* FROM item j, itemtriplet it
>WHERE j.id_document = 4 AND j.id_annotator = 1 AND j.word <> 0 
>AND it.id_triplet IN
>(SELECT t.id_triplet FROM triplet t, unit u  
> WHERE u.alphacode_unit = 'N'  AND u.name_level = 'MORPHO-SYNTACTIC'  
> AND t.id_unit = u.id_unit)
>AND it.id_item = j.id_item  
>AND it.is_revised = 0 ) AS i1,
>   
>   (SELECT DISTINCT j.* FROM item j, itemtriplet it 
>WHERE j.id_document = 4 AND j.id_annotator = 1 AND j.word <> 0  
>AND it.id_triplet IN
>(SELECT t.id_triplet FROM triplet t, unit u  
> WHERE u.alphacode_unit = 'N'  AND u.name_level = 'MORPHO-SYNTACTIC'  
> AND t.id_unit = u.id_unit)
>AND it.id_item = j.id_item  
>AND it.is_revised = 0 ) AS i2
> 
> WHERE i1.paragraph = i2.paragraph
> AND i1.sentence = i2.sentence
> AND 3 = i2.token - i1.token
> 

Could you test this query on version 4.1.2? If you get unexpexted result on v4.1.2 
create a test case (structure of your tables and data), please.


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





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

Re: concurrent users & sessions? !!!!!!

[Egor Egorov]

Alaios <[EMAIL PROTECTED]> wrote:
> 
> Do u know how many users the MySQL server v4 can't support?
> I am talking about the users that are currently online.
> I want to know the maximum number for the MyISAM tables and InnoDB
> Do u know any links for continue reading?

Number of connections is limited by max_connections variable (100 by default). It also 
depends on your OS.



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




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

Unexpected subquery behaviour

[Jaime]

Hi all!

I'm using subqueries in MySQL 4.1.1 alpha. I use three subqueries and then want to get 
those combinations matching a given constraints. It is a very simple task, but I get 
less results than I expected. In fact, I tried to execute the same query in ORACLE and 
I get 23 results (in MySQL I only get 9).

I wanted to know whether subqueries are not fully developed in this alpha version or 
it is because MySQL behaves differently. Here is the query:

SELECT i1.id_document , i1.id_item , i2.id_item
FROM
   (SELECT DISTINCT j.* FROM item j, itemtriplet it
WHERE j.id_document = 4 AND j.id_annotator = 1 AND j.word <> 0 
AND it.id_triplet IN
(SELECT t.id_triplet FROM triplet t, unit u  
 WHERE u.alphacode_unit = 'N'  AND u.name_level = 'MORPHO-SYNTACTIC'  
 AND t.id_unit = u.id_unit)
AND it.id_item = j.id_item  
AND it.is_revised = 0 ) AS i1,
   
   (SELECT DISTINCT j.* FROM item j, itemtriplet it 
WHERE j.id_document = 4 AND j.id_annotator = 1 AND j.word <> 0  
AND it.id_triplet IN
(SELECT t.id_triplet FROM triplet t, unit u  
 WHERE u.alphacode_unit = 'N'  AND u.name_level = 'MORPHO-SYNTACTIC'  
 AND t.id_unit = u.id_unit)
AND it.id_item = j.id_item  
AND it.is_revised = 0 ) AS i2

WHERE i1.paragraph = i2.paragraph
AND i1.sentence = i2.sentence
AND 3 = i2.token - i1.token

Thanks a lot!

RE: MySQL statements

[Schalk]

Hi there

 

Can someone tell me what process MySQL follows to process SQL
statements/queries? Is it top down or bottom up?

 

Kind Regards

Schalk Neethling

Web Developer.Designer.Programmer.President

Volume4.Development.Multimedia.Branding

emotionalize.conceptualize.visualize.realize

Tel: +27125468436

Fax: +27125468436

email:[EMAIL PROTECTED]

web: www.volume4.com

 

This message contains information that is considered to be sensitive or
confidential and may not be forwarded or disclosed to any other party
without the permission of the sender. If you received this message in error,
please notify me immediately so that I can correct and delete the original
email. Thank you.