Re: ORDER BY RAND() Too Slow! Alternatives?

[Stephen Waits]

"Jeffrey D. Wheelhouse" wrote:
> 
> SELECT @lines:=COUNT(id) FROM table;
> SET @rand=CEILING(RAND()*@lines);
> SELECT * FROM table WHERE (@rand:=@rand-1)+id=id;

Never mind on the "it doesn't work on my system" more like it didn't
work on my brain :)  Works fine.  And now that I ponder it a bit more
and I think I understand what it's doing I see the performance
implications.

Theoretically it could be as fast as Carsten's method couldn't it?  If
it hit a record on the first shot?  Otherwise it's pounding through an
index O(random-nearest_id) where his does it O(1).  And could it
potentially loop infinitely?

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

Re: ORDER BY RAND() Too Slow! Alternatives?

[Stephen Waits]

"Jeffrey D. Wheelhouse" wrote:
> 
> Here's another approach.  I'm curious about the performance implications:
> 
> SELECT @lines:=COUNT(id) FROM table;
> SET @rand=CEILING(RAND()*@lines);
> SELECT * FROM table WHERE (@rand:=@rand-1)+id=id;
> 
> This *should* give each row an equal chance, but it's a rather nasty
> sit-'n-spin loop.  Instead of being linear with the size of the random
> number, it's linear with the size of the table, but the constant multiplier
> is much smaller.  For all but the largest and/or simplest tables, that
> should be a win.

This is broken for me - but, I've learned you can actually set variables
in SQL which just opened a HUGE door for me :)  I'm going to study more
about this ASAP.  ***Thanks for the suggestion.***  I get the following:

mysql> select * from table where (@rand:=@rand-1)+id=id;
ERROR 1064: You have an error in your SQL syntax near 'table where
(@rand:=@rand-1)+id=id' at line 1

> By my measurements, this is a good 3x faster than the LIMIT $rand, 1
> approach on my test table.  But if "id" is indexed, Carsten's no-calc
> approach still blows it away.

Carsten's approach is one of those "duh" things I don't understand why I
hadn't thought of it.  It's fast, and very closely approximates what I
want to do.

> Is it possible to do a "fair" match without incurring at least one full
> pass through the table?

This is "the question" isn't it.  At a minimum, you must know the # of
rows you have to choose from; depending on your indexes this may not
require a full pass through.  But then you must (ideally) randomly
access any single row in that set.

So maybe it cannot be done under MySQL..  What about Oracle, MS SQL, and
Postgres?

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

Date???

[Website4S]

Hi,

I`m selecting information from my MySQL as follows...

Select * FROM Features WHERE Platform='pc' and Public='Y' Order by DateTime  
ASC LIMIT 0,3

I am storing the field DateTime as a date when selected it looks like this 
2001-05-05 but I want to change it so it looks like May 5th 2001, I`ve looked 
about and found the DATE_FORMAT method but I have no idea how to use it on my 
select without it effecting my Order by, anyone have any tips?

Thanks
Ade

-
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: ORDER BY RAND() Too Slow! Alternatives?

[Jeffrey D. Wheelhouse]

Hmm, no reading comprehension points for me.  I managed to read Steve's 
message the first time without realizing that he only wanted one row.

Here's another approach.  I'm curious about the performance implications:

SELECT @lines:=COUNT(id) FROM table;
SET @rand=CEILING(RAND()*@lines);
SELECT * FROM table WHERE (@rand:=@rand-1)+id=id;

This *should* give each row an equal chance, but it's a rather nasty 
sit-'n-spin loop.  Instead of being linear with the size of the random 
number, it's linear with the size of the table, but the constant multiplier 
is much smaller.  For all but the largest and/or simplest tables, that 
should be a win.

By my measurements, this is a good 3x faster than the LIMIT $rand, 1 
approach on my test table.  But if "id" is indexed, Carsten's no-calc 
approach still blows it away.

Is it possible to do a "fair" match without incurring at least one full 
pass through the table?

Jeff

At 10:11 PM 2/10/2001 +0100, Carsten H. Pedersen wrote:
> > Hi there,
> >
> > In the quest to get a random row from a table, "order by rand()" has
> > proven too inefficient and slow.  It's slow because MySQL apparently
> > selects ALL rows into memory, then randomly shuffles ALL of them, then
> > gives you the first one - very inefficient.  There are a few other ways
> > I've thrown around but none are "elegant".
> >
> > One is, if a table has an id # column, like "id int unsigned not null
> > auto_increment", I could do this:
> >
> > select max(id) from table;
> > $random_number = ...
> > select * from table where id=$random_number;
>
>How about
> select * from table
> where id>$random_number
> order by id
> limit 1;
>
>(note that I'm using '>' rather than '='). This should always work,
>and be pretty fast. There is a caveat, tho': this won't work if
>you need "exact randomness", i.e. certain records will have a
>better chance of being selected than others. This gets worse,
>the larger "holes" are in sets of deleted id's.
>
>/ Carsten
>--
>Carsten H. Pedersen
>keeper and maintainer of the bitbybit.dk MySQL FAQ
>http://www.bitbybit.dk/mysqlfaq
>
>
> >
> > This is very fast (assuming the id field is a unique index).  But it has
> > the problem that if records have been deleted I might get a 0-row
> > response.  It also does not work if I want to limit to a particular
> > category, for instance "where category='women'" or something.
> >
> > I could do this too:
> >
> > select count(*) from table;
> > $random_number = ...
> > select * from table limit $random_number,1;
> >
> > This has the benefit of always working but the speed, though faster than
> > the "order by rand()" method, remains unacceptable.  The speed seems
> > linear with regard to the size of $random_number; which is probably
> > obvious to you.
> >
> > So I've experimented with several other things:
> >
> > select * from table where limit rand(),1;
> > select * from table where id=(mod(floor(rand()*4294967296),count(*))+1);
> > .. and it only gets uglier from -- these are all not accepted by MySQL.
> >
> > MySQL does not allow for subqueries which is another way it could possibly
> > be accomplished.  In the end, I'll just use what works, no matter the
> > speed.
> >
> > BUT, I'd love to hear what other people have done to solve this problem!
> >
> > Thanks,
> > 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
> >
>
>
>-
>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

Configure script fails trying to get size of char type in Solaris 8

[Super-User]

>Description:
running ./configure with no options works fine until the test
program to determine the size of the char data type is compiled and
run.  I've compiled and run the sample program manually, and added a
printf() statement to show the result that's placed into the
temporary file using the fprint() line, and the result is '1'. I
don't fully-understand how the configure script works, but something
is not accepting the value placed in the temporary file.

>How-To-Repeat:
All I need to do is re-run the configure script and it always fails
at the same point. The very same thing happens with the configure
script for version 3.23.32 also. My hardware and operating system
types are shown below.

>Fix:
Not sure how to fix it, but I think the problem is in the configure
script itself. gcc builds the test program without any errors, and
it runs fine, producing the correct output result.


>Submitter-Id:  
>Originator:Super-User
>Organization:  Core Matrix Foundation
>MySQL support: [none | licence | email support | extended email support ]
>Synopsis:  seems to be a compatibility issue with Solaris 8 - maybe
>Severity:  critical
>Priority:  high
>Category:  mysql
>Class: sw-bug
>Release:   mysql-3.22.32 (Source distribution)

>Environment:

System: SunOS jedi 5.8 Generic sun4m sparc SUNW,SPARCstation-20
Architecture: sun4

Some paths:  /usr/bin/perl /usr/ccs/bin/make /usr/local/bin/gcc /usr/ucb/cc
GCC: Reading specs from /usr/local/lib/gcc-lib/sparc-sun-solaris2.8/2.95.2/specs
gcc version 2.95.2 19991024 (release)
Compilation info: CC='gcc'  CFLAGS=''  CXX='gcc'  CXXFLAGS=''  LDFLAGS=''
Configure command: ./configure  --with-unix-socket-path=/var/tmp/mysql.sock 
--with-low-memory --with-mit-threads=yes


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

[Thomas DeMartini]

I've been looking for a way to disable logging to mysql_history on a global 
basis.  Does anyone know of a way to do this?  If so, please e-mail me at 
this address (I don't check the list).  If not, can someone tell me how to 
submit a feature request?

My current work-around is to create the file ~/.mysql_history with no 
permissions so that MySQL can't write it if it wanted to.  Fortunately 
MySQL doesn't complain about that and lets me go.

I'm looking for something like a command line option:
   mysql --history=no
and/or the related functionality in the /etc/my.cnf file.

The problem I have is that a lot of my users type passwords into their 
MySQL queries to insert them into the tables and then other users without 
MySQL access come along and look at the first user's .mysql_history file 
(which is created 644 by default) to look at the passwords.  Then they go 
to the web site that the first user setup and pretend to be the other 
people who's passwords were in the history file.  I'd like to be able to 
setup my site so that by default it doesn't write a history file unless the 
user specifies mysql --history=yes or the equivalent.

Thanks,
Thomas.
Please send replies to [EMAIL PROTECTED], I don't check the list.


-
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

first day glitch

[RAJARSHI ADHIKARY]

I am the first day user.
Do I need to type the commands all over again if it is wrong?

RE: ORDER BY RAND() Too Slow! Alternatives?

[Robert Barrington]

";
print $row[col2];

?>

Robert B. Barrington

GetMart Commercial Ecom: Web Administrator
http://weddinginlasvegas.com/ 
http://getmart.com/ 
[EMAIL PROTECTED]
Vegas Vista Productions
3172 North Rainbow Boulevard
Suite 326
Las Vegas, Nevada 89108-4534
Telephone: (702)656-1027
Facsimile: (702)656-1608

-Original Message-
From: Stephen Waits [mailto:[EMAIL PROTECTED]]
Sent: Saturday, February 10, 2001 12:13 PM
To: [EMAIL PROTECTED]
Subject: ORDER BY RAND() Too Slow! Alternatives?


Hi there,

In the quest to get a random row from a table, "order by rand()" has
proven too inefficient and slow.  It's slow because MySQL apparently
selects ALL rows into memory, then randomly shuffles ALL of them, then
gives you the first one - very inefficient.  There are a few other ways
I've thrown around but none are "elegant".

One is, if a table has an id # column, like "id int unsigned not null
auto_increment", I could do this:

select max(id) from table;
$random_number = ...
select * from table where id=$random_number;

This is very fast (assuming the id field is a unique index).  But it has
the problem that if records have been deleted I might get a 0-row
response.  It also does not work if I want to limit to a particular
category, for instance "where category='women'" or something.

I could do this too:

select count(*) from table;
$random_number = ...
select * from table limit $random_number,1;

This has the benefit of always working but the speed, though faster than
the "order by rand()" method, remains unacceptable.  The speed seems
linear with regard to the size of $random_number; which is probably
obvious to you.

So I've experimented with several other things:

select * from table where limit rand(),1;
select * from table where id=(mod(floor(rand()*4294967296),count(*))+1);
.. and it only gets uglier from -- these are all not accepted by MySQL.

MySQL does not allow for subqueries which is another way it could possibly
be accomplished.  In the end, I'll just use what works, no matter the
speed.

BUT, I'd love to hear what other people have done to solve this problem!

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


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

[Jeremy D. Zawodny]

On Sat, Feb 10, 2001 at 11:32:25AM +, guru prasad wrote:
> 
> I have a site with the database of size 90 MB on Mysql-3.22.25.
> It is performing quite well right now,
> 
> Please let us know the maximum limit of the database size that Mysql
> can handle efficiently.

It really depends on the hardware you are using. MySQL can easily
handle multi-gigabyte databases on reasonably modern hardware with
sufficient RAM.

Jeremy
-- 
Jeremy D. Zawodny, <[EMAIL PROTECTED]>
Technical Yahoo - Yahoo Finance
Desk: (408) 328-7878Fax: (408) 530-5454
Cell: (408) 439-9951

-
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: DELETING CACHE

[Jeremy D. Zawodny]

On Sat, Feb 10, 2001 at 03:24:42PM +0533, Jaya Thakar(ASI 2001) wrote:
>
>   Can anybody tell me ,how to remove all the visited pages from the
> cache after logout that is after invalidating the session in java
> servlets and mysql, so that pressing the back button of the browser
> doesn't displays any page visited while a person was logged in.

Someone on a mailing list that deals with Java servlets probably can.

Jeremy
-- 
Jeremy D. Zawodny, <[EMAIL PROTECTED]>
Technical Yahoo - Yahoo Finance
Desk: (408) 328-7878Fax: (408) 530-5454
Cell: (408) 439-9951

-
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: Data modeling tool

[Michael Lépine]

I asked a similar question recently and as a result of my searching, I found
a moderately priced, $99 (I think), data modeling tool from XTG (download
here):

http://www.xtgsystems.com/download.php3

I downloaded it and set up a few tables (only 4 are allowed with eval), then
generated the sequel to create one of the tables. I liked it but didn't go
much further with it as I didn't want to pay for a modeling tool until I had
tried a few more.

Currently, I use Power Designer from Sybase which supports MySQL too but not
as well as the modelling tool from XTG although it includes many more
options, bells and whistles. You can download the eval and use it fully for
45 days.

XTG -   $99 (US)
Power Designer - (approximately)$2000 (US)

Try them for yourself and hopefully you'll like one.

HTH,
Mike

> -Original Message-
> From: Cal Evans [mailto:[EMAIL PROTECTED]]
> Sent: Saturday, February 10, 2001 6:42 PM
> To: [EMAIL PROTECTED]
> Subject: Data modeling tool
>
>
> As much as I love creating models by hand, my databases are growing to the
> point where I need a tool to help me "see" them.  Does anyone
> know of a tool
> like ERWin or ER/Studio that supports MySQL?
>
> Cal
> http://www.calevans.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

thread_cache_size on linux

[Byron Albert]

I am trying to tune mysql on linux for a web based application. Reading
throw the docs I found thread_cache_size I checked what it was set to by
default and found that it was 0. In the docs it says That it doesn't
make much difference if you have a good thread implementation.  So my
major question is does linux have a good thread implementations and
ether way will this make a difference? The only reason That I am kinda
hesitant to set this up really high is that it would keep the threads
living for a long time witch may cause memory leaks.   I would
appreciate any help and also if any one has good links other than the
manual for tuning mysql.


Thanks
Byron


-
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

look forward to contacting Wenhong Liu

[Hong Niu]

Excuse me ,I am looking for  Wenhong Liu, Ph.D. Tulance University 
1995,  BS Naikai University 1990.

Would you please give her contacting information,or forward my information 
to her.

Thanks for your help!

Niu Hong
Email:[EMAIL PROTECTED]
   [EMAIL PROTECTED]

Telephone:(617) 576 1595
MIT ,Sloan School of Management


-
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 Direct-to-disk?

[Benjamin Pflugmann]

Hi.

On Fri, Feb 09, 2001 at 11:49:23AM -0500, [EMAIL PROTECTED] wrote:
> Hello,
> 
> In an earlier post I complained of performance problems with ODBC+MySQL 
> when running a server which makes many 'small' queries and writes.  What is 
> the overhead?  The benchmarks indicate that MySQL via ODBC is perhaps only 
> 2x slower than MySQL alone, yet the performance we are experiencing is 
> _many_ times slower than using Access via DAO.  I'm wondering if the 
> overhead has a lot to do with the fact that MySQL runs over a socket 
> connection to localhost, whereas the Access jet engine was going directly 
> to disk with each query/write?

AFAIK, there are two ODBC handlers for MySQL floating around, one with
debugging enabled (seems to be the default one), and one without. The
one with debugging is a magnitude slower than the other one. Make sure
that you use the faster one.

Bye,

Benjamin.


-
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: Update: Status of MySQL 4.0/Innobase/transactions & row level locks

[Benjamin Pflugmann]

Hi.

On Fri, Feb 09, 2001 at 05:34:38PM +0200, you wrote
[...]
> 5. Add a timeout to lock waiting: Innobase can detect and resolve deadlocks
> within its own lock table, but if a user uses also MySQL LOCK TABLES... or
> BDB locks, a deadlock can occur where Innobase does not know of all the
> locks: this is resolved by adding a timeout for a lock wait, say 100 s.,
> after which the transaction is rolled back.

Maybe I misunderstand this point: But a 100 secs timeout (in worst
case of a deadlock) would be absolutely inacceptable within my
applications. :-/

I mean, okay, if I am going to block a table for 100 secs, that's my
problem. But it sounds as if the handler waits 100 secs before it
"solves" a dead-lock condition by failing.

Is there a better mechanism planned for later? At least one should be
able to change the timeout (I would set it below 5 seconds...).

Bye,

Benjamin.


PS: If there is a better place to discuss the Innobase table handler
for MySQL, let me know.


-
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: Database size estimations

[Benjamin Pflugmann]

Hi.

First, there is somewhere a chapter about disk usage in the manual
(too lazy to look it up).

On Fri, Feb 09, 2001 at 09:51:03AM -0500, [EMAIL PROTECTED] wrote:
> I was wondering if someone could help advise me on calculating
> database size estimations. Assume the following table:
> 
> Field   Type NullDefault
> typeenum('user','group') No  user
> id  smallint(6)  No  0
> action  smallint(6)  No  0
> flagenum('Y','N')No  N
> 
> Keyname   Unique   Field
> PRIMARY   Yes  id
> PRIMARY   Yes  action
> PRIMARY   Yes  type
> 
> Say I want to size this table for 20,000 entries, I would estimate
> that the data would consume (5 * 20,000 + 2 * 20,000 + 2 * 20,000 +
> 1 * 20,000) = 200,000 bytes. Right?

Almost. ENUM fields are not saved as char, but as bit fields, which
are saved as whole bytes (see the ENUM description). Additionally you
have a little overhead to each row (e.g. for NULL value flags, if
appropriate [not in your case] and other). So you have

(head+1+2+2+1) * 2 ~= 130KB

> But how do I estimate the amount of disk space the indexes (keys)
> will consume? The table is MyISAM.

It is quite easy to calculate the maximum: Each field content and
additionally a pointer to the data (depends on your table type and
size, but in your case probably 4 bytes long):

(2+2+1+4)*2 ~= 180KB

In practice, it is normally smaller, because MySQL tries to pack the
content if values are repeating.


Well, the above is only a rough estimation. As always you get the best
results when you just try and build a sample database and fill it with
random data.


Bye,

Benjamin.


-
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: Persistant Connection

[Benjamin Pflugmann]

Hello.

On Fri, Feb 09, 2001 at 09:53:45AM -0500, [EMAIL PROTECTED] wrote:
> Good Morning:
> 
> We have a system of databases (in the plural) which number in the hundreds
> on individual servers.  I want to open a persistant connection on the server
> and switch databases without closing the handle in DBI and reopening it.

The SQL command "USE database" should work.

> Is this possible without getting a rollback warning using DBI?

Hm. What is the exact message? Do you use BDB tables? If yes, maybe an
explicit COMMIT helps? (did not use BDB tables myself, yet).

I nothing else help, you can always work-around the problem by
explicitly stating the database name in your queries (which is not
pretty, but works), like

SELECT * FROM mydatabase.thetable WHERE 

Bye,

Benjamin.


-
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: Trouble creating user

[Rolf Hopkins]

Firstly, have you flushed privileges?

Secondly, I think you want "mysql -p -uuser samp_db".  You have already
created the database, right?

- Original Message -
From: "Larry Marshall" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Sunday, February 11, 2001 6:41
Subject: Trouble creating user


> Using RedHat 7.0, MySQL-3.23.32.-1
> Logged on with mysql -u root -p, issued GRANT ALL ON samp_db.* TO user
> IDENTIFIED BY "secret";
> When logged onto Linux in the user account, typed mysql -p, resulted in:
>
>ERROR 1045: Access denied for user: 'user@localhost'  (Using password:
> YES)
>
> If I use the similar format GRANT command, but TO user@localhost, the
result
> is the same. If I try to issue the mysql command without the password
> parameter, the result is the same.
>
> What am I doing wrong? Larry
>
> -
> 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: Password problems

[Rolf Hopkins]

Just to add to that, suggest you create another user, other than root, and
use that instead.  Look up Grant in chapter 7 of the manual on how to create
users.

- Original Message -
From: "Daniel Von Fange" <[EMAIL PROTECTED]>
To: "Eddie Rhodes" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Sunday, February 11, 2001 9:59
Subject: RE: Password problems


> It looks like MySQL is working fine, phpMyAdmin just needs to know your
new
> password so that it can connect to the database. Just edit the
> "config.inc.php" file in the phpMyAdmin directory.
>
> Find the lines:
>
> $cfgServers[1]['user'] = 'root';// MySQL user (only needed
> with basic auth)
> $cfgServers[1]['password'] = 'george';// MySQL password
> (only needed with basic auth)
>
>
> Also, for security, make sure that no one but you can access phpMyAdmin
from
> the web. Maybe use .htaccess
>
> Hope this helps,
> --Daniel
>
>
>
> -Original Message-
> From: Eddie Rhodes [mailto:[EMAIL PROTECTED]]
> Sent: Saturday, February 10, 2001 8:38 PM
> To: [EMAIL PROTECTED]
> Subject: Password problems
>
>
> Hi all,
>
> The resolution to my problem is probably simple for many of you.
> However, I'm new to MySQL and php and am having difficulties.  However,
> I'm ignorant, not stupid, and can follow directions.
>
> My setup is as follow:  I had My SQL, php4 (with Zend) and phpMyAdmin
> installed on my Red Hat Linux dedicated server last year by the NOC tech
> support dept. I'm now about to use them.
>
> Yesterday, phpMyAdmin and MySQL were working.  I created a couple of
> databases at a friend's house (he knows a bit of programming and also
> has phpMyAdmin.
>
> I returned home and began reading the docs.  In so doing, I discovered
> that I had no root password set, and followed the instructions to create
> one.  I did it via telnet and used the command
>
> shell> mysqladmin -u root password George
>
> Naturally, George is not the real password. It's just less confusing to
> use something other than the word password to represent the password.
>
> Addendum - okay, I found out that I set my password literally to
> password, so I need to change it to "George".
>
> Afterwards, I was unable to access phpMyAdmin. I now get the message:
> Warning: MySQL Connection Failed: Access denied for user:
> 'root@localhost' (Using password: NO) in lib.inc.php on line 255
> Error
>
>
> What a way to spend my birthday!
>
> Would anyone be kind enough to help me out? It would be much
> appreciated.
>
> Step by step literal instructions on setting up a user name and password
> would be appreciated also.  I've been reading the docs and I don't think
> they do it as such.
>
> Eddie
>
> -
> 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: Password problems

[Daniel Von Fange]

It looks like MySQL is working fine, phpMyAdmin just needs to know your new
password so that it can connect to the database. Just edit the
"config.inc.php" file in the phpMyAdmin directory.

Find the lines:

$cfgServers[1]['user'] = 'root';// MySQL user (only needed
with basic auth)
$cfgServers[1]['password'] = 'george';// MySQL password
(only needed with basic auth)


Also, for security, make sure that no one but you can access phpMyAdmin from
the web. Maybe use .htaccess

Hope this helps,
--Daniel



-Original Message-
From: Eddie Rhodes [mailto:[EMAIL PROTECTED]]
Sent: Saturday, February 10, 2001 8:38 PM
To: [EMAIL PROTECTED]
Subject: Password problems


Hi all,

The resolution to my problem is probably simple for many of you.
However, I'm new to MySQL and php and am having difficulties.  However,
I'm ignorant, not stupid, and can follow directions.

My setup is as follow:  I had My SQL, php4 (with Zend) and phpMyAdmin
installed on my Red Hat Linux dedicated server last year by the NOC tech
support dept. I'm now about to use them.

Yesterday, phpMyAdmin and MySQL were working.  I created a couple of
databases at a friend's house (he knows a bit of programming and also
has phpMyAdmin.

I returned home and began reading the docs.  In so doing, I discovered
that I had no root password set, and followed the instructions to create
one.  I did it via telnet and used the command

shell> mysqladmin -u root password George

Naturally, George is not the real password. It's just less confusing to
use something other than the word password to represent the password.

Addendum - okay, I found out that I set my password literally to
password, so I need to change it to "George".

Afterwards, I was unable to access phpMyAdmin. I now get the message:
Warning: MySQL Connection Failed: Access denied for user:
'root@localhost' (Using password: NO) in lib.inc.php on line 255
Error


What a way to spend my birthday!

Would anyone be kind enough to help me out? It would be much
appreciated.

Step by step literal instructions on setting up a user name and password
would be appreciated also.  I've been reading the docs and I don't think
they do it as such.

Eddie

-
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: JDBC->MySql==Sex?????????

[Benjamin Pflugmann]

Hi.

On Thu, Feb 08, 2001 at 01:52:47AM -0800, [EMAIL PROTECTED] wrote:
> Hi Everyone,
> Sorry for the subject but I am in a very very

Btw, this is the perfect way to get ignored.

> desperate state 'coz the whole site is down and if I
> cannot connect to the database through my servlet my
> @ss is grass.
> 
> Everything seems to be working fine except when I
> execute the servlet I get this @$#$@$# error:
> 
> 
> " Server configuration denies access to data source "
> 
> 
> And to connect my servlet code is:
> 
> Class.forName("org.gjt.mm.mysql.Driver").newInstance();
> con =
> 
>DriverManager.getConnection("jdbc:mysql://jumac.com:3306/DBNAME?user=ryan&password=pw007");

Can you connect with the mysql command line client like this (from the
host running the servlet):

shell> mysql -h jumac.com -P 3306 -u ryan -p
Passwort: pw007
(of couse, the password will not be visible while you type it)

If yes, there is a problem with your servlet. If not, there is a
problem with the values you use or with your hosting service.

Btw, in case pw007 is your real password, you should change it at
once.

I was not able to connect to "jumac.com", so this might be your
problem. "www.jumac.com" works for me (even for MySQL).

Bye,

Benjamin.

-
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

Password problems

[Eddie Rhodes]

Hi all,

The resolution to my problem is probably simple for many of you.
However, I'm new to MySQL and php and am having difficulties.  However,
I'm ignorant, not stupid, and can follow directions.

My setup is as follow:  I had My SQL, php4 (with Zend) and phpMyAdmin
installed on my Red Hat Linux dedicated server last year by the NOC tech
support dept. I'm now about to use them.

Yesterday, phpMyAdmin and MySQL were working.  I created a couple of
databases at a friend's house (he knows a bit of programming and also
has phpMyAdmin.

I returned home and began reading the docs.  In so doing, I discovered
that I had no root password set, and followed the instructions to create
one.  I did it via telnet and used the command

shell> mysqladmin -u root password George

Naturally, George is not the real password. It's just less confusing to
use something other than the word password to represent the password.

Addendum - okay, I found out that I set my password literally to
password, so I need to change it to "George".

Afterwards, I was unable to access phpMyAdmin. I now get the message:
Warning: MySQL Connection Failed: Access denied for user:
'root@localhost' (Using password: NO) in lib.inc.php on line 255
Error


What a way to spend my birthday!

Would anyone be kind enough to help me out? It would be much
appreciated.

Step by step literal instructions on setting up a user name and password
would be appreciated also.  I've been reading the docs and I don't think
they do it as such.

Eddie

-
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: Exporting data

[Gerald R. Jensen]

Despite the temptation to suggest RTFM ...

mysqldump database table(s)>textfile.txt

  ~ or ~

mysqldump -uUserID -pPassword database table(s)>textfile.txt

If you type mysqldump|more at the command prompt, the syntax for this
command is fully documented. You will also find the MySQL Manual to be an
invaluable reference.

G. Jensen

- Original Message -
From: "Mike Yuen" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Saturday, February 10, 2001 7:03 PM
Subject: Exporting data


> How do I dump all the contents of a table called "clients" into a .txt
> file (or anyother kind of file for that matter).
>
> Thanks for your help.
> Mike
>
>
>
> -
> 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: Exporting data

[Thalis A. Kalfigopoulos]

Hello,

if you want to have a file with just the bare data contents of the table, you simply 
do:

mysql> SELECT * FROM table_name INTO OUTFILE '/tmp/lala.txt';

otherwise if you want your data represented in SQL, so that you can reconstruct the 
table by importing at a later time (a backup form):

$ mysqldump database_name table_name

and since this gets printed to stdout, you should probably redirect to a file like:

$ mysqldump database_name table_name > table_name.sql.bak

regards,
thalis

On Sat, 10 Feb 2001, Mike Yuen wrote:

> How do I dump all the contents of a table called "clients" into a .txt
> file (or anyother kind of file for that matter).
> 
> Thanks for your help.
> Mike
> 
> 
> 
> -
> 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

Exporting data

[Mike Yuen]

How do I dump all the contents of a table called "clients" into a .txt
file (or anyother kind of file for that matter).

Thanks for your help.
Mike



-
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: Data modeling tool

[Dave Rolsky]

On Sat, 10 Feb 2001, Cal Evans wrote:

> As much as I love creating models by hand, my databases are growing to the
> point where I need a tool to help me "see" them.  Does anyone know of a tool
> like ERWin or ER/Studio that supports MySQL?

I have a project called Alzabo (alzabo.sourceforge.net) that is a
combination data modeller/RBDMS-OO mapper (for Perl).

The data modeller is web based (though I plan to eventually have other
UIs) and requires Perl and HTML::Mason (www.masonhq.com).

Currently it supports Mysql and Postgres, with the Mysql support being a
bit more mature.


-dave

/*==
www.urth.org
We await the New Sun
==*/


-
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: Data modeling tool

[Mark Chalkley]

I haven't tried ERWin, but CASE Studio (www.casestudio.com) is extremely good, offers 
a trial download, and does support MySQL.

Mark Chalkleyi


-
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: Max number of Joins

[Benjamin Pflugmann]

Hi.

On Wed, Feb 07, 2001 at 09:39:15AM -0500, [EMAIL PROTECTED] wrote:
> Yes, I should have been more clear, I know that you can join a table to itself but I 
>am trying to find out:
> 
> 1) What is the max number of joins (unique tables/mixture of same table joins)

As far as I remember, there is no direkt limit, only indirect ones:
e.g. table_cache must be big enough, the optimizer uses an algorithm
which won't be useful above 20-30 tables (your milleage may vary - AND
you can circumvent by using STRAIGHT_JOIN) and so on.

> 2) What is the max number of same-table joins

I assume the same as above.

Bye,

Benjamin.


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

[Murat YESILDAL]

Hi Andy, 

I dont think so to be honest it could be but i removed mysql and
reinstalled it several times
and each time, it works for a few times and then it collapses... any other
idea ???

murat,

At 19:13 10.02.2001 +0100, Dingfelder Andy wrote:
>Hello Murrat,
>
>is it possible, that someone deletes your mysql-database or the host
table?
>Maybe yourself?
>
>The server needs this table for the "authorization rules", and a deletion
>should be avoided!
>
>Hope that helps.
>
>Regards
>Andy
>
>
>
>> -Ursprngliche Nachricht-
>> Von: Murat YESILDAL [mailto:[EMAIL PROTECTED]]
>> Gesendet: Samstag, 10. Februar 2001 18:08
>> An: [EMAIL PROTECTED]
>> Betreff: Hi,
>>
>>
>>  Hi,
>>
>>   I have a problem i was using mysql and it was very well but yesterday
>> it didn't work then i removed it and reinstalled it , it worked 2 times
>> and then again it doesn't work, when i run mysqld it doesn't run in the
>> background of windows,  and in the error file it says
>>
>>  13:05:14  C:\MYSQL\BIN\MYSQLD.EXE: Table 'mysql.host' doesn't exist
>>
>> when i remove it and reinstall it i am able to run it a few times and
>> after a few times, it gives that error, that could be the problem ?
>>
>> thanks,
>>
>> murat,
>>
>> \\\I///
>> ( o o )
>> (  n  )
>> (  -  )
>>   o0o-o0o
>>   | |
>>   |   Mehmet Murat Yesildal |
>>   |BILKENT UNIVERSITY   |
>>   |   Dept : Computer Engineering & Information Science |
>>   |   E-mail   : [EMAIL PROTECTED] |
>>   |   Homepage : http://www.ug.bcc.bilkent.edu.tr/~yesildal |
>>   ---
>>
>>
>>
>>
>> -
>> 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

\\\I///
( o o )
(  n  )
(  -  )
  o0o-o0o
  | |
  |   Mehmet Murat Yesildal |
  |BILKENT UNIVERSITY   |
  |   Dept : Computer Engineering & Information Science |
  |   E-mail   : [EMAIL PROTECTED] |
  |   Homepage : http://www.ug.bcc.bilkent.edu.tr/~yesildal |
  ---



-
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: Max query size

[Benjamin Pflugmann]

Hi.

On Tue, Feb 06, 2001 at 02:00:11PM +0100, [EMAIL PROTECTED] wrote:
> Hello!
> 
> I was wondering if anyone knows how big a qury can be in MySQL 2.23.14???

First 2.23.14 is not a known MySQL release. You probably mean
3.23.14. If so, you are still using an alpha release and should
upgrade to the lastest 3.23.x stable (3.23.33 I guess).

Now to your question: The query size is mainly limited by the packet
size and that is 1MB, if I remember correctly, but can be changed at
compile time (again, IIRC). There is more info in the fine manual
about that.

Bye,

Benjamin.


-
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: why mysql eat my memory over 1G?

[Benjamin Pflugmann]

Hi.

On Tue, Feb 06, 2001 at 10:05:07AM +0800, [EMAIL PROTECTED] wrote:
> why mysql eat my memory over 1G?Please help me!Thank you!!!
> 
> server1:DELL6300,4cpu,4G memory.solaris5.7 for X86 .
> mysql start command:nohup /usr/local/bin/safe_mysqld -O key_buffer=128M -O 
>table_cache=512 -O sort_buffer=128M -O record_buffer=128M -O max_connections=999 -O 
>wait_timeout=5000 &

You can get some info about MySQL's usage by "mysqladmin
extended-status". My comments are a bit vague, because all depends on
the database size and usage.

key_buffer is okay, but may be larger for a machine with 4GB,
depending on your database size. If Key_blocks_used*1024 about 128MB,
you may want to increase it. If it is far lower, decrease
it. Key_read_requests/Key_reads should be a large number (for my
system it is about 500). Btw, this is all documented in the manual,
too.

table_cache: have a look at open_tables. If it is far lower than 512,
you may want to decrease table_cache accordingly.

You know, sort_buffer is used on a per-connection basis? I.e. if you
have 10 connections at a time which need to use a sort buffer,
10*128=1280MB are in use. That probably explains why MySQL uses so
much memory: You have told it to do so. You should probably decrease
this value a lot (depending on the number of concurrent connections
you have to the database... how about 32MB).

record_buffer: also on a per-connection basis. Each connection which
has to do a full scan (i.e. cannot use indexes) will allocate this
memory. Well, idially all your queries use indexes, aren't they? Well,
you problably don't want to have it such large.

The manual suggest the following values for a server with at least
256MB, only running MySQL and a _moderate_ number of clients at the
same time, configured for maximum speed (at expense of memory usage):

key_buffer=64M table_cache=256 sort_buffer=4M record_buffer=1M

Simply multiplying for 4GB gives:

key_buffer=1024M table_cache=4096 sort_buffer=64M record_buffer=16M

Of course, key_buffer and table_cache are a bit high (I wouldn't set
them blindly to these values, but tune the parameters on the usage
shown by extended-status), but you see, that sort_buffer is below
and record_buffer is far below of what you specified.


> use "top" show:
> 
> last pid: 26041;  load averages:  6.89,  9.39, 10.01   13:27:36
> 64 processes:  59 sleeping, 2 running, 3 on cpu
> CPU states:  0.8% idle, 68.1% user, 31.1% kernel,  0.0% iowait,  0.0% swap
> Memory: 4032M real, 557M free, 1787M swap in use, 3602M swap free
> 
>   PID USERNAME THR PRI NICE  SIZE   RES STATE   TIMECPU COMMAND
>  2838 root 147  200 1432M 1317M cpu1   62.1H 53.15% mysqld
>  3057 publish1   52   18M   12M sleep  67:32  2.08% perl
> 23734 publish1  220   17M   14M run 0:32  5.10% httpd
> 16703 publish1  580   17M   11M sleep   0:38  0.00% httpd
> 
> server2:SunUltra-4,1cpu,1G memory,solaris5.7. 
> mysql start command:nohup /usr/local/bin/safe_mysqld -O key_buffer=128M -O 
>table_cache=512 -O sort_buffer=128M -O record_buffer=128M -O max_connections=999 -O 
>wait_timeout=5000  &

Same game here. Additionally, you have only a forth of the memory, but
specify the same parameters... that can't go well. To take the example
from the manual again, multiplied by 4 (256MB * 4 = 1GB):

key_buffer=256M table_cache=1024 sort_buffer=16M record_buffer=4M

So, key_buffer and table_cache are in the correct magnitude (tune them
by hand looking at extended-status), but sort_buffer and record_buffer
are far off. You only need one connection doing a full table scan for
and ordered output and 256MB are in use... that's not good with only
1GB available.

> use "top" show:
> 
> load averages:  0.98,  1.09,  1.17 13:32:41
> 159 processes: 157 sleeping, 1 running, 1 on cpu
> CPU states:  0.0% idle, 57.3% user, 42.7% kernel,  0.0% iowait,  0.0% swap
> Memory: 1024M real, 16M free, 870M swap in use, 894M swap free
> 
>   PID USERNAME THR PRI NICE  SIZE   RES STATE   TIMECPU COMMAND
>   332 root   9  200  779M  400M run80.6H 89.60% mysqld
>   370 root   1  590   18M   13M sleep   0:15  0.00% Xsun
>   392 root   1  590 7136K 1248K sleep   0:09  0.00% dtgreet

Btw, the difference in memory usage comes from the fact, that MySQL
tries to only allocate memory as it needs it, so the second server
just was used in a more memory hungry way.

Bye,

Benjamin.


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

Re: Data modeling tool

[Jon Rosenberg]

I wish, ERWin is wonderful!


- Original Message -
From: "Cal Evans" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Saturday, February 10, 2001 6:41 PM
Subject: Data modeling tool


> As much as I love creating models by hand, my databases are growing to the
> point where I need a tool to help me "see" them.  Does anyone know of a
tool
> like ERWin or ER/Studio that supports MySQL?
>
> Cal
> http://www.calevans.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

Data modeling tool

[Cal Evans]

As much as I love creating models by hand, my databases are growing to the
point where I need a tool to help me "see" them.  Does anyone know of a tool
like ERWin or ER/Studio that supports MySQL?

Cal
http://www.calevans.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 it a bug???

[Benjamin Pflugmann]

Hi.

'0' (i.e. Zero) has a special meaning for AUTO_INCREMENT, namely the
same as NULL, which will choose a automatically a new value for the
column. (Btw, are you sure, that you get the error for the row which
was '0' and not for the one which was '72' - the latter sounds more
probably to me).

Basically, MySQL shoulnd't have allowed you to set the value '0' via
UPDATE at first. This could be considered a bug.

I know no work-around for you. Restoring the '0' value by hand isn't a
solution either, because it could happen that this will be forbidden
by MySQL sooner or later.

Bye,

Benjamin.

On Mon, Feb 05, 2001 at 05:42:27PM -0800, [EMAIL PROTECTED] wrote:
> I have been using MySQL for ecommerce work for quite some time.  One 
> problem that just recently arose was a duplicate key error during db 
> restoration.  The culprit lies in a table which has an 
> auto_incremented, not null defined integer field called "id".  There 
> is an entry which was adjusted, by me, to have an "id" of 0 (instead 
> of 1).  When mysql attempts to restore this table, after a backup, it 
> complains that the entry (which previously had the "id" of 0) now has 
> an "id" of 72, and is invalid as there already is an entry with an id 
> of 72.
> 
> Basically it seems that restore cannot allow for a zero value, if the 
> field is an auto_incremented, not null primary key.  This seems like 
> a serious problem, since many ecommerce table designs use a zero 
> value in such a table to represent a catalog base.
> 
> Is there any solution in mysql?
[...]

-
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: Repeating Groups

[Benjamin Pflugmann]

Hello.

Sorry, I don't understand what you want to archieve. Could you write
your questions in other words again? If possible, with an example.

Bye,

Benjamin.

On Mon, Feb 05, 2001 at 01:56:04PM +0200, [EMAIL PROTECTED] wrote:
> Hello
> 
> Is there any way of repeating groups in MySql.
> 
> i.e. If the same fields occurs more thanr once in a table, is it
> possible to define a repeating group where you would be able to extract
> the data of a field by method of an index.
> 
> Thanks, Paul

-
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: Add, modify and delete entries with PHP or Perl

[Maciek Uhlig]

try  PHPMyEdit: http://phpmyedit.sourceforge.net/

Maciek

> -Original Message-
> From: Nuno Lopes [mailto:[EMAIL PROTECTED]]
> Sent: Saturday, February 10, 2001 11:37 AM
> To: Lista MySQL
> Subject: Add, modify and delete entries with PHP or Perl
> 
> 
> Hi,
> 
> I would like to know how to add, modify and delete entries with 
> PHP or Perl, because I want to create a table in mysql and and 
> don't know how.
> The table look like this:
> 
> -| time spent on-line | leads | mailling list |   
>  e-mail  |
> --
> -
> username1 |  4|   5 | YES   | 
> [EMAIL PROTECTED]  |
> --
> -
> username2 |  6|   8 |  NO
> | [EMAIL PROTECTED] |
> --
> -
> username3 |  9|   2 | YES   | 
> [EMAIL PROTECTED] |
> --
> -
> 
> 
> Thanking in advance,
> Nuno Lopes
> 
> 
> P.S.: When you reply to the list, reply to my e-mail 
> ([EMAIL PROTECTED]), too, please...
> 

-
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

Trouble creating user

[Larry Marshall]

Using RedHat 7.0, MySQL-3.23.32.-1
Logged on with mysql -u root -p, issued GRANT ALL ON samp_db.* TO user
IDENTIFIED BY "secret";
When logged onto Linux in the user account, typed mysql -p, resulted in:

   ERROR 1045: Access denied for user: 'user@localhost'  (Using password:
YES)

If I use the similar format GRANT command, but TO user@localhost, the result
is the same. If I try to issue the mysql command without the password
parameter, the result is the same.

What am I doing wrong? Larry

-
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: compiling without socket support

[Benjamin Pflugmann]

Hi.

'localhost' is a special name for MySQL, which implies to connect via
mysql.sock. You don't need to recompile, just give the host name (like
test.earthlink.net or whatever it is) or IP address of your PC and
MySQL will use TCP sockets.

Bye,

Benjamin.


PS: I don't really know whether there is a method to recompile without
UNIX sockets, but I never heard about it on this list until today.


On Sun, Feb 04, 2001 at 09:08:58PM -0800, [EMAIL PROTECTED] wrote:
> How is it possible to compile mysql sources without the server and without socket 
>support? I am trying to connect a cygwin mysql client to a win32 mysql server on the 
>same PC, but the perl mysql mod test keeps looking for mysql.sock. 
> So I am thinking if I remove the socket client in the sources then it will revert to 
>IP for its conn to the server
> 
> thanks,
> dale

-
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: Connection closing problem!

[Benjamin Pflugmann]

Hi.

It looks like you are using persistent connections with SQL server,
but not with MySQL (time_wait and close_wait are states of a TCP
connection after it was closed by the appliction). I don't know much
about PHP, but there is somewhere a option to influence whether you
want persistent connections or not.

Second, what is MySQL telling (mysqladmin processlist with MySQL-root
privileges). Are there sleeping connections (persistent connections)
or not (no persistent connections).

Bye,

Benjamin.

On Mon, Feb 05, 2001 at 12:26:38PM +1100, [EMAIL PROTECTED] wrote:
> Hi, 
>   Does anyone know of or have a problem that means that the 
> HTML server will not receive the message back from the MySQL 
> server to say close the connection. 
> The two servers are on two seperate machines. 
> The html server is Apache using the PHP scripting language.
> The error messages I am getting, not so much error messages but 
> messages, when I do a netstat on the html machine state that alot 
> of the sql connections are time_wait or close_wait. This should 
> cause no problem I here you say but when the majority of the html 
> server connections are on time_wait or close_wait then the site 
> starts to slow right down. And eventually cause database server 
> busy errors for users. On the SQL server the netstat results appear 
> to be fine with the majority of them being Established. Can any 
> shed some light on this problem or even tell me of a way to fix it. If 
> any questions about the problem need to be asked I will try and 
> answer them. Thanks in advance.
> 
> Drew
> 
> Andrew Toussaint  
> Richardson-Shaw Pty Ltd 
> [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: Different versions causing this?

[Benjamin Pflugmann]

Hi.

On Sun, Feb 04, 2001 at 02:57:06PM -0700, [EMAIL PROTECTED] wrote:
> I recently asked for some help with join order and it's now fixed, kinda :)
> 
> On a web server running mySQL version 3.22.32 on a Linux box (cobalt RAQ) I get the 
>following output for this query:
> 
> EXPLAIN SELECT * FROM (LOG_LOG LEFT JOIN SAT_CMD ON LOG_LOG.LOG_ID=SAT_CMD.LOGID) 
>LEFT JOIN SAT_GEN ON LOG_LOG.LOG_ID=SAT_GEN.LOGID; 
> 
>   table type possible_keys key key_len ref rows Extra 
>   LOG_LOG ALL NULL NULL NULL NULL 41   
>   SAT_CMD ALL LOGID NULL NULL NULL 7   
>   SAT_GEN eq_ref LOGID LOGID 8 LOG_LOG.LOG_ID 1   

That shouldn't be that way even with 3.22.x. The only reason I know
for this kind of behaviour is when the columns are not of exactly the
same type (I mean LOG_ID/LOGID).

> As you can see it's not an optimal query.  Nothing I did could fix it.
> 
> So I dumped the tables and put them on my local machine running mySQL version 
>3.23.28-gamma on Win98.  This is the output I get now for the same query:
> 
> 
>+-++---+---+-++--+---+
> | table   | type   | possible_keys | key   | key_len | ref| rows | Extra 
>|
> 
>+-++---+---+-++--+---+
> | log_log | ALL| NULL  | NULL  |NULL | NULL   |   41 | |
> | sat_cmd | eq_ref | LOGID | LOGID |   8 | log_log.log_id |1 | |
> | sat_gen | eq_ref | LOGID | LOGID |   8 | log_log.log_id |1 | |
> 
>+-++---+---+-++--+---+
> 3 rows in set (0.55 sec)
> 
> All fields remained the same except for case and all indexes are the same.
> 
> Is it the different versions causing this or different platforms?

Very probably a deficiency in 3.22.x has been fixed meanwhile. That's
the reason why one should always test with the most recent version if
a problem is still reproducible (or fixed).

Bye,

Benjamin.


-
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

Repair with key cache error?

[Paul Buder]

Description:

I have this little script (1) that builds a table each night.
For quite a while the output shows a normal load data infile (2).
Then, it switches to a state of 'Repair with keycache' (3).
This just started happening with mysql 3.23.32.  Until I upgraded
a few days ago it was not a problem.  I was using 3.23.8.
The idea is to load the data minus the keys and then add the
keys, since this is much faster.  The data file for load data infile
is 1.3 gigs.  At about the time the message changes, the mysql file
sizes are -
sq1_invhist_rec.MYD 616840056, sq1_invhist_rec.MYI 15904768.
I also made sure the files did not start out corrupt since I
did a mysqldump -d on them, rm'ed and recreated them.  I
never reach the 'pause' line in the script, at least not until I
forcibly (-9) brought down the server.  It didn't seem to want to
shutdown any other way.

This all seems to be sporadic.  It will work fine for a couple of days
taking a couple of hours to build the tables.  Then for the next
couple of days it will bomb out and still be building when I come in
ten hours after it started.  So I shutdown mysql to abort the build.

It sounds somewhat similar to a post I saw from David J. Potter where he
writes

  This happens while inserting a large number rows at high speed being read
  from a text file.  It crashes usually around the 1,000,000th inserted row
  into a table.

Though other details he mentions are different.

Any ideas?


1. The script.
#!/bin/sh
set -x
cd /data2/sq1tmp
PATH=$PATH:/usr/local/bin
echo delete from sq1_invhist_rec | mysql book
mysqladmin flush-tables
myisamchk --keys-used=0 -rq /data/book/sq1_invhist_rec
mysqladmin flush-tables
echo "load data infile '/data2/sq1tmp/$file' into table $file" | mysql book
echo pausing now
read

2. Line from mysqladmin processlist before the repair message


| 43  | root  | localhost   | book | Query   | 1074 |   | load 
|data infile '/data2/sq1tmp/sq1_invhist_rec' into table sq1_invhist_rec |
0|

3. Line from mysqladmin processlist once the repair message appears

| 43  | root | localhost | book | Query   | 1226 | Repair with keycache | load data 
|infile '/data2/sq1tmp/sq1_invhist_rec' into table sq1_invhist_rec |


>How-To-Repeat:

>Fix:


>Submitter-Id:  
>Originator:
>Organization:
 

MySQL support: [email support] (I believe so. For Powell's Books)

Synopsis:   Problem with nightly table build
Severity:   serious
Priority:   medium
Category:   mysql
Class:  sw-bug
>Release:   mysql-3.23.32 (Source distribution)
>Server: /usr/local/bin/mysqladmin  Ver 8.14 Distrib 3.23.32, for pc-linux-gnu on i586
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version  3.23.32
Protocol version10
Connection  Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 57 min 49 sec

Threads: 3  Questions: 2765  Slow queries: 0  Opens: 63  Flush tables: 1  Open tables: 
57 Queries per second avg: 0.797
>Environment:

System: Linux snowball.burnside.powells.com 2.2.5-15 #1 Mon Apr 19 22:21:09 EDT 1999 
i586 unknown
Architecture: i586

Some paths:  /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/egcs-2.91.66/specs
gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release)
Compilation info: CC='gcc'  CFLAGS=''  CXX='c++'  CXXFLAGS=''  LDFLAGS=''
LIBC:
lrwxrwxrwx   1 root root   13 Jun 17  1999 /lib/libc.so.6 -> libc-2.1.1.so
-rwxr-xr-x   1 root root  4016683 Apr 16  1999 /lib/libc-2.1.1.so
-rw-r--r--   1 root root 19533408 Apr 16  1999 /usr/lib/libc.a
-rw-r--r--   1 root root  178 Apr 16  1999 /usr/lib/libc.so
Configure command: ./configure  --prefix=/usr/local/mysql --localstatedir=/data 
--with-raid
Perl: This is perl, version 5.005_03 built for i386-linux


-
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: ORDER BY RAND() Too Slow! Alternatives?

[Carsten H. Pedersen]

> Hi there,
>
> In the quest to get a random row from a table, "order by rand()" has
> proven too inefficient and slow.  It's slow because MySQL apparently
> selects ALL rows into memory, then randomly shuffles ALL of them, then
> gives you the first one - very inefficient.  There are a few other ways
> I've thrown around but none are "elegant".
>
> One is, if a table has an id # column, like "id int unsigned not null
> auto_increment", I could do this:
>
> select max(id) from table;
> $random_number = ...
> select * from table where id=$random_number;

How about
select * from table
where id>$random_number
order by id
limit 1;

(note that I'm using '>' rather than '='). This should always work,
and be pretty fast. There is a caveat, tho': this won't work if
you need "exact randomness", i.e. certain records will have a
better chance of being selected than others. This gets worse,
the larger "holes" are in sets of deleted id's.

/ Carsten
--
Carsten H. Pedersen
keeper and maintainer of the bitbybit.dk MySQL FAQ
http://www.bitbybit.dk/mysqlfaq


>
> This is very fast (assuming the id field is a unique index).  But it has
> the problem that if records have been deleted I might get a 0-row
> response.  It also does not work if I want to limit to a particular
> category, for instance "where category='women'" or something.
>
> I could do this too:
>
> select count(*) from table;
> $random_number = ...
> select * from table limit $random_number,1;
>
> This has the benefit of always working but the speed, though faster than
> the "order by rand()" method, remains unacceptable.  The speed seems
> linear with regard to the size of $random_number; which is probably
> obvious to you.
>
> So I've experimented with several other things:
>
> select * from table where limit rand(),1;
> select * from table where id=(mod(floor(rand()*4294967296),count(*))+1);
> .. and it only gets uglier from -- these are all not accepted by MySQL.
>
> MySQL does not allow for subqueries which is another way it could possibly
> be accomplished.  In the end, I'll just use what works, no matter the
> speed.
>
> BUT, I'd love to hear what other people have done to solve this problem!
>
> Thanks,
> 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
>


-
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: a bug during tar .........

[Boyd Lynn Gerber]

On Sat, 10 Feb 2001, 163 wrote:
> x 
>mysql-3.23.32/sql-bench/Results-linux/big-tables-mysql-Linux_2.0.33_i586-cmp-ms-sql,mysql,sybase,
> 684 bytes, 2 tape blocks
> UX:tar: ERROR: Cannot set time on ././@LongLink: No such file or directory
> UX:tar: ERROR: Directory checksum error
>
>
> what's the wrong and how to fix this bug?

You need to get GNU tar and use it.

Good Luck,

--
Boyd Gerber <[EMAIL PROTECTED]>
ZENEZ   3748 Valley Forge Road, Magna Utah  84044
Office 801-250-0795 FAX 801-250-7975


-
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, modify and delete entries with PHP or Perl

[Nuno Lopes]

Hi,

I would like to know how to add, modify and delete entries with PHP or Perl, because I 
want to create a table in mysql and and don't know how.
The table look like this:

-| time spent on-line | leads | mailling list |e-mail  |
---
username1 |  4|   5 | YES   | [EMAIL PROTECTED]  |
---
username2 |  6|   8 |  NO| [EMAIL PROTECTED] |
---
username3 |  9|   2 | YES   | [EMAIL PROTECTED] |
---


Thanking in advance,
Nuno Lopes


P.S.: When you reply to the list, reply to my e-mail ([EMAIL PROTECTED]), too, 
please...

Re: ORDER BY RAND() Too Slow! Alternatives?

[Jeffrey D. Wheelhouse]

Could you do something like:

CREATE TEMPORARY TABLE temptable (
   pk INTEGER,
   rand INTEGER
);
INSERT INTO temptable SELECT yourpk,Rand() FROM yourtable;
SELECT yourtable.* FROM yourtable,temptable WHERE pk=yourpk ORDER BY rand;
DROP TABLE temptable;

That might be quicker than your current approach.

Jeff

At 12:12 PM 2/10/2001 -0800, Stephen Waits wrote:

>Hi there,
>
>In the quest to get a random row from a table, "order by rand()" has
>proven too inefficient and slow.  It's slow because MySQL apparently
>selects ALL rows into memory, then randomly shuffles ALL of them, then
>gives you the first one - very inefficient.  There are a few other ways
>I've thrown around but none are "elegant".
>
>One is, if a table has an id # column, like "id int unsigned not null
>auto_increment", I could do this:
>
>select max(id) from table;
>$random_number = ...
>select * from table where id=$random_number;
>
>This is very fast (assuming the id field is a unique index).  But it has
>the problem that if records have been deleted I might get a 0-row
>response.  It also does not work if I want to limit to a particular
>category, for instance "where category='women'" or something.
>
>I could do this too:
>
>select count(*) from table;
>$random_number = ...
>select * from table limit $random_number,1;
>
>This has the benefit of always working but the speed, though faster than
>the "order by rand()" method, remains unacceptable.  The speed seems
>linear with regard to the size of $random_number; which is probably
>obvious to you.
>
>So I've experimented with several other things:
>
>select * from table where limit rand(),1;
>select * from table where id=(mod(floor(rand()*4294967296),count(*))+1);
>.. and it only gets uglier from -- these are all not accepted by MySQL.
>
>MySQL does not allow for subqueries which is another way it could possibly
>be accomplished.  In the end, I'll just use what works, no matter the
>speed.
>
>BUT, I'd love to hear what other people have done to solve this problem!
>
>Thanks,
>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


-
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

ORDER BY RAND() Too Slow! Alternatives?

[Stephen Waits]

Hi there,

In the quest to get a random row from a table, "order by rand()" has
proven too inefficient and slow.  It's slow because MySQL apparently
selects ALL rows into memory, then randomly shuffles ALL of them, then
gives you the first one - very inefficient.  There are a few other ways
I've thrown around but none are "elegant".

One is, if a table has an id # column, like "id int unsigned not null
auto_increment", I could do this:

select max(id) from table;
$random_number = ...
select * from table where id=$random_number;

This is very fast (assuming the id field is a unique index).  But it has
the problem that if records have been deleted I might get a 0-row
response.  It also does not work if I want to limit to a particular
category, for instance "where category='women'" or something.

I could do this too:

select count(*) from table;
$random_number = ...
select * from table limit $random_number,1;

This has the benefit of always working but the speed, though faster than
the "order by rand()" method, remains unacceptable.  The speed seems
linear with regard to the size of $random_number; which is probably
obvious to you.

So I've experimented with several other things:

select * from table where limit rand(),1;
select * from table where id=(mod(floor(rand()*4294967296),count(*))+1);
.. and it only gets uglier from -- these are all not accepted by MySQL.

MySQL does not allow for subqueries which is another way it could possibly
be accomplished.  In the end, I'll just use what works, no matter the
speed.

BUT, I'd love to hear what other people have done to solve this problem!

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

Re: MySQL errors, can anyone shed some light?

[Benjamin Pflugmann]

Hi.

A bit late, but I did not see an answer to your question yet.

On Fri, Feb 02, 2001 at 03:23:06PM -0800, [EMAIL PROTECTED] wrote:
> 
> We are getting thse errors. Can anyone point me in the right direction?
> 
> DBI->connect(database=whois;host=www5) failed: Host 'www5' is blocked
> because of many connection errors.  Unblock with 'mysqladmin flush-hosts' at

This means that "www5" has initiated to much bogus connections
(interrupted during intitial handshake, IIRC) and the MySQL server now
blocks connections from this client to protect itself.

This can happen even with a valid client, e.g. if there are problems
with the network connection of similar stuff.

Solution is to connect to the MySQL server (from a different client or
localhost) as MySQL-root and issue "FLUSH HOSTS" or run "mysqladmin
flush-hosts". That will clean all blocks.

Bye,

Benjamin.


-
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

Installation error Linux RedHat 7.0

[Larry Marshall]

On issuing the command:

 rpm -i MySQL-32.23.32.-1.i386.rpm

the following error listed:

  cannot open Packages index using db3 - Permission denied (13)

  --> The rpm database cannot be opened in db3 format.
   If you have just upgraded the rpm package you need to convert
   your database to db3 format by running "rpm --rebuilddb" as root.

  error: cannot open Packages database in /var/lib/rpm

Any suggestions? Thanks - Larry

-
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: ISAM and MYISAM

[Thalis A. Kalfigopoulos]

I'm new too, but a good starting point would be:
http://www.mysql.com/doc/T/a/Table_types.html


regards,
thalis

---+
You're definitely on their list. 
The correct question to ask is what list it is.
---+

On Sat, 10 Feb 2001, Teddy A Jasin wrote:

> Hi,
> I've read some people talking about ISAM and MYISAM table.
> I dont understand what are those tables and can anyone explain to me and the
> difference of each table. and also which one has better performance?


-
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: Newbie Questions and a resolution

[Lad . Gaal]

Well, I finally got that portion working. After I putzed around for awhile, I
said to myself - Self, With all this putzing, I probably need to restart the
mysqld server. So I tried that from linuxconf and for some reason it just didn't
want to restart! So, I went and did a /etc/rc5.d/S85mysqld stop and then a
/etc/rc5.d/S85mysqld start. Low and behold, I could now change the password from
a blank to meg123.

So now my next question is how do I add users? Do I do that from the mysql
database??

Tx for your help




"mysql-return-64843-gaal=ct.marconimed.com"@lists.mysql.com on 02/10/2001
01:06:43 PM

To:   "Aaron Sinclair" <[EMAIL PROTECTED]>
cc:   [EMAIL PROTECTED] (bcc: Lad Gaal/MarconiMedical)

Subject:  Re: Newbie Questions





You're correct in that /var/lib/mysql/mysql/* was not owned by mysql. I did a
chown mysql.mysql and then did a chmod 775 on the directory.
So I still get basically the same thing.
I enter: mysqladmin -u root -p password meg123
Enter password:(I leave it blank and CR)
mysqladmin: unable to change password; error: 'Table 'user' is read only'

But if I enter:
mysqladmin -u root -p password meg123 (cr)
Enter password: meg123 (CR)
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user: 'root@localhost' (Using password: YES)'

I'm open for suggestions TX





"Aaron Sinclair" <[EMAIL PROTECTED]> on 02/09/2001 08:20:16 PM

To:   Lad Gaal/MarconiMedical@Marconi
cc:

Subject:  Re: Newbie Questions



check that /var/lib/mysql/mysql/*  are owned by mysql and have read write
premissions.

I am hanging out at www.mercylink.com/chat/chat.php chat server for a while
if you want to talk about it.

Aaron Sinclair



- Original Message -
From: <[EMAIL PROTECTED]>
To: "Aaron Sinclair" <[EMAIL PROTECTED]>
Sent: Saturday, February 10, 2001 2:05 PM
Subject: Re: Newbie Questions


>
>
> I just tried mysqladmin -u root password meg123
> the return was: mysqladmin: unable to change password; error: 'Table
'user' is
> read only'
>
> I then tried running the mysql_install_db again and then typed
> mysqladmin -u root -p password meg123
> the response was t enter the password which I left blank and hit return
> and got: mysqladmin: unable to change password; error: 'Table 'user' is
read
> only'
>
> I installed MYSQL during the redhat 7 install. Do I need a .cnf file
someplace
> like in /etc. I think I saw a mf.cnf file that I need to copy from
> /usr/share/mysql/my-medium.cnf. I feel like it's something simple, but I
just
> can't grasp it. Things were so much easier with postgresql.
> This is what I get for trying to advance!!!
> Tx...
>
>
>
>
> "Aaron Sinclair" <[EMAIL PROTECTED]> on 02/09/2001 06:22:26 PM
>
> To:   Lad Gaal/MarconiMedical@Marconi
> cc:
>
> Subject:  Re: Newbie Questions
>
>
>
> after running the mysql_install scripts..
>
> mysqladmin -uroot password password
>
> mysql requires you to specify what user you are  ie
>  mysql -u -p
>
> - Original Message -
> From: <[EMAIL PROTECTED]>
> Newsgroups: mailing.database.mysql
> Sent: Saturday, February 10, 2001 11:22 AM
> Subject: Newbie Questions
>
>
> >
> >
> > Just got mysql running (Ithink) on Redhat Linux 7. The version is
> > mysqladmin  Ver 8.8 Distrib 3.23.22-beta, for redhat-linux-gnu on i386
> > TCX Datakonsult AB, by Monty
> >
> > Server version  3.23.22-beta-log
> > Protocol version10
> > Connection  Localhost via UNIX socket
> > UNIX socket /var/lib/mysql/mysql.sock
> >
> > So how do I change/add passwords for the users and how do I add users?
> > I tried 'mysqladmin password newpassword' (where newpassword is my
> password)
> > The response I get is: 'mysqladmin: unable to change password; error:
'You
> are
> > using MySQL as an anonym' '
> > So then I switch to superuser and try again.
> > The response I get is: 'mysqladmin: unable to change password; error:
> 'Table
> > 'user' is read only' '
> > I try : 'mysqladmin -password newpassword'
> > The response is: 'mysqladmin: connect to server at 'localhost' failed
> > error: 'Access denied for user: 'root@localhost' (Using password: YES)'
> >
> > So how the heck do I do this??
> >
> > Thanks
> >
> > 
> > Lad. Gaal
> >
> >
> >
> >
> >
> > -
> > 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

RE: ISAM and MYISAM

[Carsten H. Pedersen]

> Hi,
> I've read some people talking about ISAM and MYISAM table.
> I dont understand what are those tables and can anyone explain to 
> me and the
> difference of each table. and also which one has better performance?

Read The Fine Manual, chapter 8: MySQL table types

/ Carsten
--
Carsten H. Pedersen
keeper and maintainer of the bitbybit.dk MySQL FAQ
http://www.bitbybit.dk/mysqlfaq
> 

-
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: GUI Client Java

[Carsten H. Pedersen]

> I remember trying out a really nice Java GUI for mysql, had a yellow and
> gray color scheme if I remember correctly, and was very nicely done. The
> only thing I can find the the GUI clients page at mysql.com is one so-so
> client that doesn't do what I need. Anyone maintaining a list of all the
> available options including clients outside of the mysql one?

Certainly not all, but quite a few:
http://www.bitbybit.dk/mysqlfaq/faq.html#ch7_23_0

>
> Anyone have a link to the client I am thinking about? I can't for the life
> of me remember its name.

You're probably thinking of Java SQLClient from trustice.com,
http://trustice.com/java/sqlclient/

/ Carsten
--
Carsten H. Pedersen
keeper and maintainer of the bitbybit.dk MySQL FAQ
http://www.bitbybit.dk/mysqlfaq



-
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: HELP!!!

[Mailing List Address]

I did.. I tried everything...

[root@cents /usr/local/mysql]# chmod -R 0777 var
[root@cents /usr/local/mysql]# llm
total 2.1M
drwxr-sr-x  12 mysqlmysql1.0k Feb 10 00:12 .
drwxr-sr-x  26 root root 1.0k Jan 28 00:59 ..
drwxr-sr-x   2 mysqlmysql1.0k Feb  9 23:48 bin
drwxr-sr-x   3 mysqlmysql1.0k Jan 27 23:59 include
drwxr-sr-x   2 mysqlmysql1.0k Feb  9 23:47 info
drwxr-sr-x   3 mysqlmysql1.0k Jan 27 23:59 lib
drwxr-sr-x   2 mysqlmysql1.0k Feb  9 23:48 libexec
drwxr-sr-x   3 mysqlmysql1.0k Jan 27 23:59 man
drwxr-sr-x   6 mysqlmysql1.0k Feb  9 23:48 mysql-test
drwxr-sr-x   3 mysqlmysql1.0k Jan 27 23:59 share
drwxr-sr-x   8 mysqlmysql1.0k Feb  9 23:48 sql-bench
drwxrwxrwx  17 mysqlmysql1.0k Feb 10 00:12 var
 -rw-r--r--   1 root mysql2.1M Feb  9 23:34 var.bak.tar 

[root@cents /usr/local/mysql/bin]# ./mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 72 to server version: 3.23.32 

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

mysql> show databases;
+--+
| Database |
+--+
|  |
|  |
|  |
|  |
|  |
|  |
|  |
|  |
|  |
|  |
|  |
|  |
|  |
|  |
|  |
|  |
|  |
|  |
|  |
|  |
+--+
20 rows in set (0.00 sec) 

:( 

 

Sinisa Milivojevic writes: 

> J.M. Roth writes:
>  > Hello,
>  > 
>  > I updated to 3.23.32 and now this happens:
>  > mysql> show databases;
>  > +--+
>  > | Database |
>  > +--+
>  > |  |
>  > |  |
>  > |  |
>  > |  |
>  > |  |
>  > |  |
>  > |  |
>  > |  |
>  > |  |
>  > |  |
>  > |  |
>  > |  |
>  > |  |
>  > |  |
>  > |  |
>  > |  |
>  > |  |
>  > |  |
>  > |  |
>  > |  |
>  > +--+
>  > 20 rows in set (0.00 sec)
>  > Select queries and stuff seem to work alright...
>  > Downgrade to 3.22.32 had no effect!?
>  > I also installed PHP4.0.4pl1 at the same time.
>  > It seemed this strange behavior occured just after it was all brought up to date.
>  > 
>  > Any insights are very welcome!
>  > 
>  > Regards
>  > 
>  > 
>  > J.M. Roth
>  > 
>  >  
> 
> HI! 
> 
> Check your permissions if on Unix. 
> 
> 
> Regards, 
> 
> Sinisa 
> 
>     __ _   _  ___ ==  MySQL AB
>  /*/\*\/\*\   /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
> /*/ /*/ /*/   \*\_   |*|   |*||*| mailto:[EMAIL PROTECTED]
>/*/ /*/ /*/\*\/*/  \*\|*|   |*||*| Larnaca, Cyprus
>   /*/ /*/  /*/\*\_/*/ \*\_/*/ |*|
>   /*/^^^\*\^^^
>  /*/ \*\Developers Team 
> 
> -
> 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: HELP!!!

[Mailing List Address]

Yes I tried that too... Same stupid stuff happening

clay bond writes: 

>  
> 
> On Fri, 9 Feb 2001, Mailing List Address wrote: 
> 
>> Help!
>> [ ... ]
>> 6 rows in set (0.00 sec)
>> (The output should be mysql and test.)
> 
> Looks to me like you didn't run mysql_install_db 
> 
> --
>  /"\
>  \ /  ASCII RIBBON CAMPAIGN
>   X   AGAINST HTML EMAIL
>  / \  AND POSTINGS 
> 
> 
> -
> 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

AW: Hi,

[Dingfelder Andy]

Hello Murrat,

is it possible, that someone deletes your mysql-database or the host table?
Maybe yourself?

The server needs this table for the "authorization rules", and a deletion
should be avoided!

Hope that helps.

Regards
Andy



> -Ursprüngliche Nachricht-
> Von: Murat YESILDAL [mailto:[EMAIL PROTECTED]]
> Gesendet: Samstag, 10. Februar 2001 18:08
> An: [EMAIL PROTECTED]
> Betreff: Hi,
>
>
>  Hi,
>
>   I have a problem i was using mysql and it was very well but yesterday
> it didn't work then i removed it and reinstalled it , it worked 2 times
> and then again it doesn't work, when i run mysqld it doesn't run in the
> background of windows,  and in the error file it says
>
>  13:05:14  C:\MYSQL\BIN\MYSQLD.EXE: Table 'mysql.host' doesn't exist
>
> when i remove it and reinstall it i am able to run it a few times and
> after a few times, it gives that error, that could be the problem ?
>
> thanks,
>
> murat,
>
> \\\I///
> ( o o )
> (  n  )
> (  -  )
>   o0o-o0o
>   | |
>   |   Mehmet Murat Yesildal |
>   |BILKENT UNIVERSITY   |
>   |   Dept : Computer Engineering & Information Science |
>   |   E-mail   : [EMAIL PROTECTED] |
>   |   Homepage : http://www.ug.bcc.bilkent.edu.tr/~yesildal |
>   ---
>
>
>
>
> -
> 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: Newbie Questions

[Lad . Gaal]

You're correct in that /var/lib/mysql/mysql/* was not owned by mysql. I did a
chown mysql.mysql and then did a chmod 775 on the directory.
So I still get basically the same thing.
I enter: mysqladmin -u root -p password meg123
Enter password:(I leave it blank and CR)
mysqladmin: unable to change password; error: 'Table 'user' is read only'

But if I enter:
mysqladmin -u root -p password meg123 (cr)
Enter password: meg123 (CR)
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user: 'root@localhost' (Using password: YES)'

I'm open for suggestions TX





"Aaron Sinclair" <[EMAIL PROTECTED]> on 02/09/2001 08:20:16 PM

To:   Lad Gaal/MarconiMedical@Marconi
cc:

Subject:  Re: Newbie Questions



check that /var/lib/mysql/mysql/*  are owned by mysql and have read write
premissions.

I am hanging out at www.mercylink.com/chat/chat.php chat server for a while
if you want to talk about it.

Aaron Sinclair



- Original Message -
From: <[EMAIL PROTECTED]>
To: "Aaron Sinclair" <[EMAIL PROTECTED]>
Sent: Saturday, February 10, 2001 2:05 PM
Subject: Re: Newbie Questions


>
>
> I just tried mysqladmin -u root password meg123
> the return was: mysqladmin: unable to change password; error: 'Table
'user' is
> read only'
>
> I then tried running the mysql_install_db again and then typed
> mysqladmin -u root -p password meg123
> the response was t enter the password which I left blank and hit return
> and got: mysqladmin: unable to change password; error: 'Table 'user' is
read
> only'
>
> I installed MYSQL during the redhat 7 install. Do I need a .cnf file
someplace
> like in /etc. I think I saw a mf.cnf file that I need to copy from
> /usr/share/mysql/my-medium.cnf. I feel like it's something simple, but I
just
> can't grasp it. Things were so much easier with postgresql.
> This is what I get for trying to advance!!!
> Tx...
>
>
>
>
> "Aaron Sinclair" <[EMAIL PROTECTED]> on 02/09/2001 06:22:26 PM
>
> To:   Lad Gaal/MarconiMedical@Marconi
> cc:
>
> Subject:  Re: Newbie Questions
>
>
>
> after running the mysql_install scripts..
>
> mysqladmin -uroot password password
>
> mysql requires you to specify what user you are  ie
>  mysql -u -p
>
> - Original Message -
> From: <[EMAIL PROTECTED]>
> Newsgroups: mailing.database.mysql
> Sent: Saturday, February 10, 2001 11:22 AM
> Subject: Newbie Questions
>
>
> >
> >
> > Just got mysql running (Ithink) on Redhat Linux 7. The version is
> > mysqladmin  Ver 8.8 Distrib 3.23.22-beta, for redhat-linux-gnu on i386
> > TCX Datakonsult AB, by Monty
> >
> > Server version  3.23.22-beta-log
> > Protocol version10
> > Connection  Localhost via UNIX socket
> > UNIX socket /var/lib/mysql/mysql.sock
> >
> > So how do I change/add passwords for the users and how do I add users?
> > I tried 'mysqladmin password newpassword' (where newpassword is my
> password)
> > The response I get is: 'mysqladmin: unable to change password; error:
'You
> are
> > using MySQL as an anonym' '
> > So then I switch to superuser and try again.
> > The response I get is: 'mysqladmin: unable to change password; error:
> 'Table
> > 'user' is read only' '
> > I try : 'mysqladmin -password newpassword'
> > The response is: 'mysqladmin: connect to server at 'localhost' failed
> > error: 'Access denied for user: 'root@localhost' (Using password: YES)'
> >
> > So how the heck do I do this??
> >
> > Thanks
> >
> > 
> > Lad. Gaal
> >
> >
> >
> >
> >
> > -
> > 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

Hi,

[Murat YESILDAL]

 Hi, 

  I have a problem i was using mysql and it was very well but yesterday
it didn't work then i removed it and reinstalled it , it worked 2 times
and then again it doesn't work, when i run mysqld it doesn't run in the
background of windows,  and in the error file it says 

 13:05:14  C:\MYSQL\BIN\MYSQLD.EXE: Table 'mysql.host' doesn't exist 

when i remove it and reinstall it i am able to run it a few times and
after a few times, it gives that error, that could be the problem ?

thanks,

murat,

\\\I///
( o o )
(  n  )
(  -  )
  o0o-o0o
  | |
  |   Mehmet Murat Yesildal |
  |BILKENT UNIVERSITY   |
  |   Dept : Computer Engineering & Information Science |
  |   E-mail   : [EMAIL PROTECTED] |
  |   Homepage : http://www.ug.bcc.bilkent.edu.tr/~yesildal |
  ---




-
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: HELP!!!

[clay bond]

On Fri, 9 Feb 2001, Mailing List Address wrote:

> Help!
> [ ... ]
> 6 rows in set (0.00 sec)
> (The output should be mysql and test.)

Looks to me like you didn't run mysql_install_db

--
 /"\
 \ /ASCII RIBBON CAMPAIGN
  X AGAINST HTML EMAIL
 / \AND POSTINGS


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

[Gerald R. Jensen]

We have one (WinNT) installation with a database that has grown to +250mb.
This particular database was ported to MySQL from MSSQL7.

Our experience has been that if you supply adequate resources for the server
(RAM, CPU and Virtual Memory) the size of the database only becomes an issue
during dump/backup operations. The particular server hardware with the
database referenced above is a P-III 733mHz with 512mb RAM and 20gb SCSI
RAID-5.

Ultimately, we will use replication to provide the backup, but so far our
customers have been happy with the present scheme.

G. Jensen

- Original Message -
From: "guru prasad" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Saturday, February 10, 2001 11:32 AM
Subject: Mysql Stability


> Hi,
>
> I have a site with the database of size 90 MB on Mysql-3.22.25.
> It is performing quite well right now,
>
> Please let us know the maximum limit of the database size that Mysql
> can handle efficiently.
>
> Regards,
> Guru
> _
> Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.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: Conect MySql to ODBC

[Roger Retamoza]

Alexander.

Thank you for the interest.

I wait for to understand my unfortunate groins.

My problem is that when I am going to conect to MySql throuhgt ODBC is very
slow in the begining, I want to use another method, if you know another one
that is not ODBC I will thank you. I use Visual Foxpro 6.0 with windows NT.

Waiting for an answer.

Thank you for all.

Roger Retamoza
ICQ 102090754
[EMAIL PROTECTED]
Barranquilla-Colombia



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

[Adam Ward]

Trying to setup mysql on a cobolt raq4 however i keep getting an error 1064
does anyone know what could be causing this.

Regards

Adam Ward


-
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: HELP!!!

[Sinisa Milivojevic]

J.M. Roth writes:
 > Hello,
 > 
 > I updated to 3.23.32 and now this happens:
 > mysql> show databases;
 > +--+
 > | Database |
 > +--+
 > |  |
 > |  |
 > |  |
 > |  |
 > |  |
 > |  |
 > |  |
 > |  |
 > |  |
 > |  |
 > |  |
 > |  |
 > |  |
 > |  |
 > |  |
 > |  |
 > |  |
 > |  |
 > |  |
 > |  |
 > +--+
 > 20 rows in set (0.00 sec)
 > Select queries and stuff seem to work alright...
 > Downgrade to 3.22.32 had no effect!?
 > I also installed PHP4.0.4pl1 at the same time.
 > It seemed this strange behavior occured just after it was all brought up to date.
 > 
 > Any insights are very welcome!
 > 
 > Regards
 > 
 > 
 > J.M. Roth
 > 
 > 

HI!

Check your permissions if on Unix.


Regards,

Sinisa

    __ _   _  ___ ==  MySQL AB
 /*/\*\/\*\   /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/   \*\_   |*|   |*||*| mailto:[EMAIL PROTECTED]
   /*/ /*/ /*/\*\/*/  \*\|*|   |*||*| Larnaca, Cyprus
  /*/ /*/  /*/\*\_/*/ \*\_/*/ |*|
  /*/^^^\*\^^^
 /*/ \*\Developers Team

-
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 dies, fails to restart (address already in use)

[Sinisa Milivojevic]

[EMAIL PROTECTED] writes:
 > >Description:
 > 
 > After running for a variable amount of time, our server will just die.
 > We have run myisamchk several times, with -r and with -o.  We even
 > checked the actual file system for corruption.  This is a real problem
 > when the server doesn't automatically restart. 
 > 
 > This is what the log looks like when it doesn't come back up:
 > 
 > mysqld got signal 11;
 >
 > The manual section 'Debugging a MySQL server' tells you how to use a 
 >
 > stack trace and/or the core file to produce a readable backtrace that may
 >
 > help in finding out why mysqld died  
 >
 > Attemping backtrace. You can use the following information to find out   
 >
 > where mysqld died.  If you see no messages after this, something went
 >
 > terribly wrong   
 >
 > Bogus stack limit or frame pointer, aborting backtrace   
 >
 >  
 >
 > Number of processes running now: 0   
 >
 > 010209 18:37:25  mysqld restarted
 >
 > 010209 18:37:25  Can't start server: Bind on TCP/IP port: Address already
 >
 > in use   
 >
 > 010209 18:37:25  Do you already have another mysqld server running on
 >
 > port: 1433 ? 
 >
 > 010209 18:37:25  Aborting
 >
 >  
 >
 > 010209 18:37:25  mysqld ended   
 > 
 > 
 >  
 > >Severity:   serious 
 > >Priority:   high
 > >Category:   mysql
 > >Class:  
 > >Release:mysql-3.23.32 (Official MySQL RPM)
 > >Server: /usr/bin/mysqladmin  Ver 8.14 Distrib 3.23.32, for pc-linux-gnu on i686
 > Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
 > This software comes with ABSOLUTELY NO WARRANTY. This is free software,
 > and you are welcome to modify and redistribute it under the GPL license
 > 
 > Server version   3.23.32
 > Protocol version 10
 > Connection   Localhost via UNIX socket
 > UNIX socket  /var/lib/mysql/mysql.sock
 > Uptime:  4 min 19 sec
 > 
 > Threads: 101  Questions: 255072  Slow queries: 0  Opens: 139  Flush tables: 1  Open 
 >tables: 133 Queries per second avg: 984.834
 > >Environment:
 >  
 > System: Linux db1.audiogalaxy.com 2.4.2-pre3 #1 SMP Fri Feb 9 18:01:57 CST 2001 i686 
 >unknown
 > Architecture: i686
 > 
 > Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
 > GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs
 > gcc version 2.96 2731 (Red Hat Linux 7.0)
 > Compilation info: CC='egcs'  CFLAGS='-O6 -fomit-frame-pointer -mpentium'  CXX='egcs' 
 > CXXFLAGS='-O6 -fomit-frame-pointer   -felide-constructors 
 >-fno-exceptions -fno-rtti -mpentium'  LDFLAGS=''
 > LIBC: 
 > lrwxrwxrwx1 root root   14 Dec 18 04:16 /lib/libc.so.6 -> 
 >libc-2.1.94.so
 > -rwxr-xr-x1 root root  4796386 Oct  5 16:46 /lib/libc-2.1.94.so
 > -rw-r--r--1 root root 22737326 Oct  5 16:09 /usr/lib/libc.a
 > -rw-r--r--1 root root  178 Oct  5 16:09 /usr/lib/libc.so
 > Configure command: ./configure  --disable-shared --with-mysqld-ldflags=-all-static 
 >--with-client-ldflags=-all-static --enable-assembler --with-mysqld-user=mysql 
 >--with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/ 
 >--with-extra-charsets=complex --exec-prefix=/usr --libexecdir=/usr/sbin 
 >--sysconfdir=/etc --datadir=/usr/share --localstatedir=/var/lib/mysql 
 >--infodir=/usr/info --includedir=/usr/include --mandir=/usr/man --without-berkeley-db 
 >'--with-comment=Official MySQL RPM'


Hi!

Our binaries are built on 2.2 kernel.

Please try to upgrade your glibc to 2.2 and try building mysql
yourself.


Regards,

Sinisa

    __ _   _  ___ ==  MySQL AB
 /*/\*\/\*\   /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/   \*\_   |*|   |*||*|

Re: Newbi: mysqlgui wont connect

[Sinisa Milivojevic]

Kevin H writes:
 > I have mysql working, but I can't get mysqlgui to connect.  I've tried the
 > 
 > 'bin/mysqladmin version' test and that works ok, it's just that mysqlgui doesnt
 > 
 > connect for some reason.  A test doing 'telnet localhost 3306' connects (but later
 > 
 > disconnects, some msg about foreign host...), I just don't get what's wrong.
 > 
 > I have whatever is latest binaries from mysql site.  I'm mainly interested in having
 > 
 > an interface that I can create some databases with and become familiar before I start
 > 
 > trying to write some c++ code.
 > 
 > The install is new so there's no password for 'root' user on mysql.  Mysqlgui asks
 > 
 > for a password, but then just say's it can't connect in the status bar, maybe there's
 > 
 > an error "111" given.
 > 
 > My apologies for such a dumb question.  Any ideas, please?
 > 
 > Kevin
 > 
 > --
 > /*
 > Kevin Hise
 > 


Hi!

All you have to do is follow instruction in README that is shipped
with every mysqlgui distro. For you here is the excerpt from the same
README in this text:

When you start it for the first time, click on ``Options'' button, fill up
all entries correctly and click on ``Save'' button. From then on, you will
logon automatically to the running server on every mysqlgui startup.

Take care to put the right value in ``Ask for password'' button. Also, if
you have problems with location of socket files on *nix, enter a full path
of the socket file in the ``SQL command on the start-up''. This input field
is used on Windoze if you wish to specify that named pipe option.



Regards,

Sinisa

    __ _   _  ___ ==  MySQL AB
 /*/\*\/\*\   /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/   \*\_   |*|   |*||*| mailto:[EMAIL PROTECTED]
   /*/ /*/ /*/\*\/*/  \*\|*|   |*||*| Larnaca, Cyprus
  /*/ /*/  /*/\*\_/*/ \*\_/*/ |*|
  /*/^^^\*\^^^
 /*/ \*\Developers Team

-
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

mysqlbug

[Roopa Rannorey]

Sir,
  Iam Roopa mailing you from linux learning centre.
I have a slight problem regarding mysql-3.23.22-6
I have installed this for the first time using rpm packages.after
installation when i run mysql_install_db ,
but when i try to start mysql daemon using safe_mysqld
am encountering an error saying "mysql daemon ended"
i checked the error log file too .according to which the error is
"can't find file :host.frm".  iam unable to trace what has to be done
 to eliminate this error.
so please guide me in this direction.
eagerly waiting for ur reply,
Thanking you
Roopa.S


-
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 help: mysql_fetch_row(mysql_res)

[Sinisa Milivojevic]

Konstantin Osipov writes:
 > Hi!
 > 
 > Is this code buggy?
 > 
 > ...
 > mysql_res = mysql_store_result(mysql);
 > 
 > if (mysql_errno(mysql) == 0 && mysql_num_rows(mysql_res) > 2 &&
 > mysql_num_fields(mysql_res) > 2)
 > {
 > MYSQL_ROW row1 = mysql_fetch_row(mysql_res); // can i fetch several rows
 > and then work with them?
 > MYSQL_ROW row2 = mysql_fetch_row(mysql_res); //
 > if (row1[0] == row2[0])
 > printf("equal\n");
 > }
 > 
 > 
 > 


HI!

It is !! Konstantin, you send us a lot of buggy code. Nobody should be
flamboyant with it's buggy code. 

What if mysql_store_result() returned NULL ??

Also, you would do much better by fetching rows like this:

while ((row=mysql_fetch_row(mysql_res)))

Also, again if you use properly MySQL++, without shortcuts (!), you
will be much safer from the errors like the above.


Regards,

Sinisa

    __ _   _  ___ ==  MySQL AB
 /*/\*\/\*\   /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/   \*\_   |*|   |*||*| mailto:[EMAIL PROTECTED]
   /*/ /*/ /*/\*\/*/  \*\|*|   |*||*| Larnaca, Cyprus
  /*/ /*/  /*/\*\_/*/ \*\_/*/ |*|
  /*/^^^\*\^^^
 /*/ \*\Developers Team

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

[guru prasad]

Hi,

I have a site with the database of size 90 MB on Mysql-3.22.25.
It is performing quite well right now,

Please let us know the maximum limit of the database size that Mysql
can handle efficiently.

Regards,
Guru
_
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.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: Speed of mysql

[chchen]

oh.
no.i am not talking about of the speed of SELECT.
my problem is the speed of INSERT and UPDATE.
evenmore indexes only slow down the speend of insert,update.
i ruduce the index to only i need.
-

What sort of queries are you doing on this large table? I notice you only have
a couple of the fields indexed

jason


- Original Message -
From: "chchen" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Saturday, February 10, 2001 6:35 PM
Subject: Speed of mysql


hi,all
i have a strange question.
I use FreeBSD4.2-RELEASE + mysql-3.23.32
Dual PIII667+ 768MB RAM

I have a table like this
CREATE TABLE ABC (
   A int(10) unsigned DEFAULT '0' NOT NULL,
   B int(10) unsigned DEFAULT '0' NOT NULL,
   C0 bigint(20) unsigned DEFAULT '0' NOT NULL,
   C1 bigint(20) unsigned DEFAULT '0' NOT NULL,
   C2 bigint(20) unsigned DEFAULT '0' NOT NULL,
   C3 bigint(20) unsigned DEFAULT '0' NOT NULL,
   C4 bigint(20) unsigned DEFAULT '0' NOT NULL,
   C5 bigint(20) unsigned DEFAULT '0' NOT NULL,
   C6 bigint(20) unsigned DEFAULT '0' NOT NULL,
   C7 bigint(20) unsigned DEFAULT '0' NOT NULL,
   Total bigint(20) unsigned DEFAULT '0' NOT NULL,
   A2 smallint(5) DEFAULT '0' NOT NULL,
   B2 smallint(5) DEFAULT '0' NOT NULL,
   PRIMARY KEY (A, B),
   KEY B (B),
   KEY B2 (B2)
);
and i write a C program to insert data into this table.
and my data have about 1M+ rows.

i find when it insert data in mysql, it seems at a high speed.
but after about 200K rows+, it slow down and down.

actually i don;t know if it slow down.
but use CPU usage of my C program and Mysqld
from the begin is 9x% , and then.less and less after about 200K+.

fiannly it usually use only 1x or even less only x%'s cpu usage.

i tried to use memory disk to stored Table, it seems won't happen
what i say before. so i think it is the I/O problem.

soi think maybe is my table too big to search the data.
soi separate my table into 256 tables. to reduce the size of table
but... seems it doesn't useful to solve speed problem.
and when it slow down(CPU useage reduce), i check
all these 256 tables, no one is bigger than 100K rows.

soanyone have good idea to solve this situation?
please don't ask me to put it all into the memory.
i tried before. but table is too big.
i guess about 500MB for the MYD and MYI

Regards
chChen







-
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

I can not alter the new index num.....

[張峰銘]

Hi! 

Thanks for  have a look at me.   I  got  some  problem  followed, thanks..

Because  of   deleting   some  data   from a  table, and I got the  uncountinued  
index number.

Yesterday, I   tried  to  alter  the  table   to delete   a  auto_increment  and 
primary  key  column 
 named  "my_num"  , and I   rebuilt  a  new   auto_increment  primary key  the same  
named
"my_num".  

I type  the  follwed two command:

mysql>   alter  table   bk_user   drop   my_num;
mysql>  alter table  bk_user  add  my_num int auto_increment  primary key;


I  would  just  like to resetthe  auto_increment  and  primary  key   started  
form   No.1  but faild.
It  always  appears  the number   that  start  after  the  ended  and last  number  of 
 the column 
that you deleted  . (for  example :30215 to  start )

I  just  could  not  get  the new fresh   index number   from "  1  " .
Could  any one  tell me  how I can  reolve the problem?  Thanks  a lot !

 Fongming   from  Taiwan.  2001/2/10  

DELETING CACHE

[Jaya Thakar(ASI 2001)]

hiall
  Can anybody tell me ,how to remove all the visited pages from the cache
after logout that is after invalidating the session in java servlets and 
mysql, so that pressing the back button of the browser doesn't displays
any
page visited while a person was logged in. 

-- 

jaya



It's time to stop comparing yourself to other people.  They are no better
or worse than you are.  It will cause you problems at work.  Let it go.  You
are talented and special in your own right.   


---
MSc ASI(2001) 
I.I.T POWAI
MUMBAI

Email:[EMAIL PROTECTED]
  [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: Speed of mysql

[Jason Brooke]

What sort of queries are you doing on this large table? I notice you only have
a couple of the fields indexed

jason


- Original Message -
From: "chchen" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Saturday, February 10, 2001 6:35 PM
Subject: Speed of mysql


hi,all
i have a strange question.
I use FreeBSD4.2-RELEASE + mysql-3.23.32
Dual PIII667+ 768MB RAM

I have a table like this
CREATE TABLE ABC (
   A int(10) unsigned DEFAULT '0' NOT NULL,
   B int(10) unsigned DEFAULT '0' NOT NULL,
   C0 bigint(20) unsigned DEFAULT '0' NOT NULL,
   C1 bigint(20) unsigned DEFAULT '0' NOT NULL,
   C2 bigint(20) unsigned DEFAULT '0' NOT NULL,
   C3 bigint(20) unsigned DEFAULT '0' NOT NULL,
   C4 bigint(20) unsigned DEFAULT '0' NOT NULL,
   C5 bigint(20) unsigned DEFAULT '0' NOT NULL,
   C6 bigint(20) unsigned DEFAULT '0' NOT NULL,
   C7 bigint(20) unsigned DEFAULT '0' NOT NULL,
   Total bigint(20) unsigned DEFAULT '0' NOT NULL,
   A2 smallint(5) DEFAULT '0' NOT NULL,
   B2 smallint(5) DEFAULT '0' NOT NULL,
   PRIMARY KEY (A, B),
   KEY B (B),
   KEY B2 (B2)
);
and i write a C program to insert data into this table.
and my data have about 1M+ rows.

i find when it insert data in mysql, it seems at a high speed.
but after about 200K rows+, it slow down and down.

actually i don;t know if it slow down.
but use CPU usage of my C program and Mysqld
from the begin is 9x% , and then.less and less after about 200K+.

fiannly it usually use only 1x or even less only x%'s cpu usage.

i tried to use memory disk to stored Table, it seems won't happen
what i say before. so i think it is the I/O problem.

soi think maybe is my table too big to search the data.
soi separate my table into 256 tables. to reduce the size of table
but... seems it doesn't useful to solve speed problem.
and when it slow down(CPU useage reduce), i check
all these 256 tables, no one is bigger than 100K rows.

soanyone have good idea to solve this situation?
please don't ask me to put it all into the memory.
i tried before. but table is too big.
i guess about 500MB for the MYD and MYI

Regards
chChen







-
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

Newbi: mysqlgui wont connect

[Kevin H]

I have mysql working, but I can't get mysqlgui to connect.  I've tried the

'bin/mysqladmin version' test and that works ok, it's just that mysqlgui doesnt

connect for some reason.  A test doing 'telnet localhost 3306' connects (but later

disconnects, some msg about foreign host...), I just don't get what's wrong.

I have whatever is latest binaries from mysql site.  I'm mainly interested in having

an interface that I can create some databases with and become familiar before I start

trying to write some c++ code.

The install is new so there's no password for 'root' user on mysql.  Mysqlgui asks

for a password, but then just say's it can't connect in the status bar, maybe there's

an error "111" given.

My apologies for such a dumb question.  Any ideas, please?

Kevin

--
/*
Kevin Hise

email: [EMAIL PROTECTED]
voice: 760/384-3855
WWW: http://www.ridgenet.net/~kevinh/

"There's trouble in the Kingdom
 send a message to the King!" - Kings-X

#198118 with the Linux Counter, http://counter.li.org

America kills 169 babies every hour of every day, over 38 million since 1973.
http://www.roevwade.org/   http://www.abortionfacts.com/
http://www.abortioncancer.com/   http://www.nrlc.org/RU486/Index.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

Speed of mysql

[chchen]

hi,all
i have a strange question.
I use FreeBSD4.2-RELEASE + mysql-3.23.32
Dual PIII667+ 768MB RAM

I have a table like this
CREATE TABLE ABC (
   A int(10) unsigned DEFAULT '0' NOT NULL,
   B int(10) unsigned DEFAULT '0' NOT NULL,
   C0 bigint(20) unsigned DEFAULT '0' NOT NULL,
   C1 bigint(20) unsigned DEFAULT '0' NOT NULL,
   C2 bigint(20) unsigned DEFAULT '0' NOT NULL,
   C3 bigint(20) unsigned DEFAULT '0' NOT NULL,
   C4 bigint(20) unsigned DEFAULT '0' NOT NULL,
   C5 bigint(20) unsigned DEFAULT '0' NOT NULL,
   C6 bigint(20) unsigned DEFAULT '0' NOT NULL,
   C7 bigint(20) unsigned DEFAULT '0' NOT NULL,
   Total bigint(20) unsigned DEFAULT '0' NOT NULL,
   A2 smallint(5) DEFAULT '0' NOT NULL,
   B2 smallint(5) DEFAULT '0' NOT NULL,
   PRIMARY KEY (A, B),
   KEY B (B),
   KEY B2 (B2)
);
and i write a C program to insert data into this table.
and my data have about 1M+ rows.

i find when it insert data in mysql, it seems at a high speed.
but after about 200K rows+, it slow down and down.

actually i don;t know if it slow down.
but use CPU usage of my C program and Mysqld 
from the begin is 9x% , and then.less and less after about 200K+.

fiannly it usually use only 1x or even less only x%'s cpu usage.

i tried to use memory disk to stored Table, it seems won't happen
what i say before. so i think it is the I/O problem.

soi think maybe is my table too big to search the data.
soi separate my table into 256 tables. to reduce the size of table
but... seems it doesn't useful to solve speed problem.
and when it slow down(CPU useage reduce), i check 
all these 256 tables, no one is bigger than 100K rows.

soanyone have good idea to solve this situation?
please don't ask me to put it all into the memory.
i tried before. but table is too big.
i guess about 500MB for the MYD and MYI

Regards
chChen