Re: MySQL 5.0.44 with Innodb Max memory problem :-(

[Ken Menzel]

On 7/21/2010 12:16 PM, Nunzio Daveri wrote:


database is around 150GB with over 5,000 tables. To make things worse, if I
shutdown MySQL, top-c still says all the memory is still used? Is this a bug,
why would it say all the memory is used when I turn off MySQL. The weird thing
is that when I reboot it is fine and purring. When I start to create reports and


Are you sure it's MySQL using up all the memory?  If you order top 
output by size you don't see any other process eating up memory?  This 
is more of a system administration question than MySQL.




Any help, guidance or advise is much appreciated.

Nunzio






--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: What OS is the best platform for MySQL ?

[Ken Menzel]

Dan Nelson wrote:

In the last episode (Jul 06), Blog Tieng Viet said:

I have been using MySQL on FreeBSD for 3 years and encounterd a lot of
problems related to thread management.  And 1 year ago, I found that my
FreeBSD box does not go well with any MySQL revision after 5.1.17-beta,
because the MySQL thread does not end although complied with
LINUX_THREADS.


Try building without LINUX_THREADS; that option shouldn't really be used
with FreeBSD 6 or newer.

Also This article may help if you have not yet abandoned Freebsd.  It 
shows how freebsd can outperform Linux in some cases.


http://people.freebsd.org/~kris/scaling/mysql.html

Ken


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: BSD/OS and 5.1

[Ken Menzel]

Hi Dave,
   There is not enough information here to know if your test is failing 
or your compile is bad.  Did the compile complete without errors?  What 
version of BSD/OS are you using there are many versions and varieties. 
Most of them have a package system with per-built binaries, I would 
really suggest trying those first.


Hope this helps,
Ken

Dave Shariff Yadallee - System Administrator a.k.a. The Root of the 
Problem wrote:

I mean MySQL 5.1

I compile MySQL 5.1.32 and on tests I got

 gmake -k test
cd unittest && gmake test
gmake[1]: Entering directory `/usr/src/contrib/mysql/mysql-5.1.32/unittest'
perl unit.pl run mytap mysys  ../storage/archive ../storage/blackhole 
../storage/csv ../storage/example ../storage/federated ../storage/heap 
../storage/myisam ../storage/myisammrg   ../plugin/daemon_example 
../plugin/fulltext
Running tests: mytap mysys ../storage/archive ../storage/blackhole 
../storage/csv ../storage/example ../storage/federated ../storage/heap 
../storage/myisam ../storage/myisammrg ../plugin/daemon_example 
../plugin/fulltext
mytap/t/basic-t..Useless use of string in void context at -e line 1.
mytap/t/basic-t.. No subtests run
mysys/bitmap-t...Useless use of string in void context at -e line 1.
mysys/bitmap-t... No subtests run
mysys/base64-t...Useless use of string in void context at -e line 1.
mysys/base64-t... No subtests run
mysys/my_atomic-tUseless use of string in void context at -e line 1.
mysys/my_atomic-t No subtests run

Test Summary Report
---
mytap/t/basic-t  (Wstat: 0 Tests: 0 Failed: 0)
  Parse errors: No plan found in TAP output
mysys/bitmap-t   (Wstat: 0 Tests: 0 Failed: 0)
  Parse errors: No plan found in TAP output
mysys/base64-t   (Wstat: 0 Tests: 0 Failed: 0)
  Parse errors: No plan found in TAP output
mysys/my_atomic-t (Wstat: 0 Tests: 0 Failed: 0)
  Parse errors: No plan found in TAP output
Files=4, Tests=0,  1 wallclock secs ( 0.07 usr  0.10 sys +  0.00 cusr  0.10 csys
 =  0.27 CPU)
Result: FAIL
Failed 4/4 test programs. 0/0 subtests failed.
gmake[1]: *** [test] Error 255
gmake[1]: Leaving directory `/usr/src/contrib/mysql/mysql-5.1.32/unittest'
gmake: *** [test-unit] Error 2
cd mysql-test ; \
/usr/bin/perl ./mysql-test-run.pl   --mysqld=--binlog-format=mixed
Logging: ./mysql-test-run.pl  --mysqld=--binlog-format=mixed
090310 10:53:13 [ERROR] Fatal error: Please read "Security" section of the 
manual to find out how to run mysqld as root!

090310 10:53:13 [ERROR] Aborting

mysql-test-run: *** ERROR: Could not find version of MySQL
gmake: *** [test-ns] Error 1
cd mysql-test ; \
/usr/bin/perl ./mysql-test-run.pl   --ps-protocol 
--mysqld=--binlog-format=row
Logging: ./mysql-test-run.pl  --ps-protocol --mysqld=--binlog-format=row
090310 10:53:14 [ERROR] Fatal error: Please read "Security" section of the 
manual to find out how to run mysqld as root!

090310 10:53:14 [ERROR] Aborting

mysql-test-run: *** ERROR: Could not find version of MySQL
gmake: *** [test-pr] Error 1
gmake: Target `test' not remade because of errors. 


Is this safe to deploy or are patches going to be needed?




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Query Optimization

[Ken Menzel]

Try a union instead of an or condition.
http://dev.mysql.com/doc/refman/5.0/en/union.html

Johnny Withers wrote:

I have the following tables:

Customer: id,ssn
Customer_Id: id,customer_id,id_num

The customer table holds customers along with their SSN and the customer_id
table holds identifications for each customer (Driver's License, State
Issued ID, Student ID, etc). The SSN column from the customer table is
VARCHAR(9) and the id_num column from the customer_id table is VARCHAR(32).
Both of these columns have an index on them.

The following query uses the index on customer.ssn and executes in 0ms:

SELECT SQL_NO_CACHE customer.id,customer.ssn,customer_id,id_num
FROM customer USE INDEX(idx_ssn)
LEFT JOIN customer_id ON customer.id=customer_id.customer_id
WHERE ssn='123456789';

Explain output:

*** 1. row ***
   id: 1
  select_type: SIMPLE
table: customer
 type: ref
possible_keys: idx_ssn
  key: idx_ssn
  key_len: 35
  ref: const
 rows: 1
Extra: Using where; Using index
*** 2. row ***
   id: 1
  select_type: SIMPLE
table: customer_id
 type: ref
possible_keys: customer_key
  key: customer_key
  key_len: 5
  ref: aca_ecash.customer.id
 rows: 1
Extra:

Now, this is the query I have trouble with, it does not use the index (or
says it does but doesn't?) and on a busy system (200+ queries per sec) can
take up to 20 seconds or more to execute:

SELECT SQL_NO_CACHE customer.id,customer.ssn,customer_id,id_num
FROM customer USE INDEX(idx_ssn)
LEFT JOIN customer_id ON customer.id=customer_id.customer_id
WHERE ssn='123456789' OR id_num='123456789';

Explain output:

*** 1. row ***
   id: 1
  select_type: SIMPLE
table: customer
 type: index
possible_keys: idx_ssn
  key: idx_ssn
  key_len: 35
  ref: NULL
 rows: 165843
Extra: Using index
*** 2. row ***
   id: 1
  select_type: SIMPLE
table: customer_id
 type: ref
possible_keys: customer_key
  key: customer_key
  key_len: 5
  ref: aca_ecash.customer.id
 rows: 1
Extra: Using where


Is there some way I can make it use the index? I've thought about
redesigning the query to select from the customer_id table first, if a row
is found.. just return the matching customer_id from the customer table..
but I wanted to see if maybe i'm going about this the wrong way before I
"engineer" some way around this.

Thanks in advance,

-
Johnny Withers
601.209.4985
joh...@pixelated.net




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Ancient, unsolved high-CPU problem -- vmstat, top and ps

[Ken Menzel]

from http://dev.mysql.com/doc/refman/5.1/en/connecting.html

On Unix, MySQL programs treat the hostname |localhost| specially, in a 
way that is likely different from what you expect compared to other 
network-based programs. For connections to |localhost|, MySQL programs 
attempt to connect to the local server by using a Unix socket file. This 
occurs even if a |--port| or |-P| option is given to specify a port 
number. To ensure that the client makes a TCP/IP connection to the local 
server, use |--host| or |-h| to specify a hostname value of |127.0.0.1|, 
or the IP address or name of the local server. You can also specify the 
connection protocol explicitly, even for |localhost|, by using the 
|--protocol=TCP| option. For example:


shell> *|mysql --host=127.0.0.1|*
shell> *|mysql --protocol=TCP

|*

*|Just try it. localhost is trated as a special name used for the 
socketfile. If you want to use 127.0.0.1 or have programs that use that 
then you will need to specify 127.0.0.1 when using skip-dns option.  It 
does not hurt to grant both localhost and 127.0.0.1 if you are unsure 
when you are not using DNS.


Ken
|**||*
*||*



Rene Fournier wrote:

So with skip-name-resolve in my.cnf (and MySQL restarted), it should
be okay to have [EMAIL PROTECTED] in the GRANT table since localhost
resolves without DNS lookup? Or do I need to specify [EMAIL PROTECTED]

Sorry, just need to clarify this bit before changing a few things.
Thanks again.

...Rene

On 24-Sep-08, at 3:33 PM, Ken Menzel wrote:

  

  Yes, you can still use a hostname in the connection string, that's
not what mysql uses it for, that hostname gets you from the client
to the server.  If you use GRANT to permit access from certain hosts
IE 'grant all on mydb.* to 'mydbuuser'@'%.mydomain.com'.  Then the
server will not be able to resolve those named permissions from the
connecting  IP back to a domain name matched to a grant to allow
login. Also I don't think you can use subnets in a grant, you can
use a single IP. However  'myuser'@'%' should continue to work just
fine as should localhost using the file socket.  Everything else
should behave normally.  Localhost connections are usually through
the the mysql socket file (/tmp/mysql.sock) not 127.0.0.1 and is
mapped to localhost.



  

Re: Ancient, unsolved high-CPU problem -- vmstat, top and ps

[Ken Menzel]

Hi Rene,
  (Note I have updated my reply address to my current company name).   
Yes, you can still use a hostname in the connection string, that's not 
what mysql uses it for, that hostname gets you from the client to the 
server.  If you use GRANT to permit access from certain hosts IE 'grant 
all on mydb.* to 'mydbuuser'@'%.mydomain.com'.  Then the server will not 
be able to resolve those named permissions from the connecting  IP back 
to a domain name matched to a grant to allow login. Also I don't think 
you can use subnets in a grant, you can use a single IP. However  
'myuser'@'%' should continue to work just fine as should localhost using 
the file socket.  Everything else should behave normally.  Localhost 
connections are usually through the the mysql socket file 
(/tmp/mysql.sock) not 127.0.0.1 and is mapped to localhost.


Don't forget to restart the server after the change in the my.cnf file.

Hopefully Apple will rebranch Darwin from a more current freebsd, there 
have been many improvements in threading, SMP scheduling and 
performance, and I highly recommend version 7 and 8 looks like it will 
be even better.


Ken

Rene Fournier wrote:

Thanks. I've read those links, and they sound like my problem.

On each connection, MySQL calls gethostbyname() to resolve the 
hostname in the connection string into 127.0.0.1 -- e.g.,  
mysql_connect("localhost", "user", "password") -> 127.0.0.1.  Because 
FreeBSD 4.0's (and Mac OS X's) DNS lookups aren't thread-safe, bad 
things can happen while MySQL waits on gethostbyname().  At least, 
that's where the CPU is spending much of its time.


Now, it sounds like using using 127.0.0.1 in place of localhost in the 
connection string is not enough, since MySQL will still call 
gethostbyaddr() as a reverse-lookup.  (Right?)  So this is why, as you 
say, it's necessary to add "skip-name-resolve" to my.cnf.  (Right?)  
It's also then necessary to make the Grant tables not depend on 
hostnames (localhost), but specify 127.0.0.1.


But here's the strange thing: On a test machine, I've added 
"skip-name-resolve" to my.cnf. But I can still use a hostname in the 
connection string, and it works.



On 23-Sep-08, at 5:44 PM, Ken Menzel wrote:


Hi Rene,
 This smells like an old freebsd issue with a non thread safe 
get-host-by-name issue and possibly other thread issues. Since Mac 
OS/X/Darwin is a freebsd 4 branch it is a good bet they are the same. 
Is it possible for you to try adding "skip-name-resolve" to my.cnf. 
Alternatively you could compile with -D SKIP_DNS_CHECK.  Please read 
about these options before trying them to understand any implication 
it my have on your GRANTs if you grant to a domain or server.


Here are some links to more information,
http://jeremy.zawodny.com/blog/archives/000203.html
http://bugs.mysql.com/bug.php?id=414
http://www.mail-archive.com/mysql@lists.mysql.com/msg87497.html

Hope this helps,
Ken

Rene Fournier wrote:
In case a bit more data might help, here's what the server looks 
like right now, while experiencing the strange high-CPU load:

VM_STAT sayeth:
Mach Virtual Memory Statistics: (page size of 4096 bytes)
Pages free:   534327.
Pages active: 331233.
Pages inactive:  1094528.
Pages wired down: 137065.
"Translation faults":  957568490.
Pages copy-on-write:   241306984.
Pages zero filled:1302796176.
Pages reactivated:790261.
Pageins:   95668.
Pageouts:   1212.
Object cache: 217985425 hits of 220226841 lookups (98% hit rate)
Top says:
Processes:  115 total, 3 running, 112 sleeping... 504 
threads  08:12:30
Load Avg:  2.43, 2.44, 2.30 CPU usage:  45.3% user, 48.2% sys, 
6.5% idle

Networks:   676 ipkts/72K  738 opkts /181K
Disks:   10 reads/52K  594 writes/3049K
VM:   0 pageins  0 pageouts
 PID COMMAND  %CPU   TIME   FAULTS PGINS/COWS MSENT/MRCVD  
BSD/MACHCSW
25943 mysqld  92.6% 57:11:01   6473 0/0  154/154
1121358/3403231
20067 php  9.1%  6:53:45   1764 0/238 14/7  
6128/14  584
25957 Terminal 7.0% 12:20:23150 0/0 1013/814
244/2407648

[...]
And PS:
USER   PID %CPU %MEM  VSZRSS  TT  STAT STARTED  TIME
mysql25943  114.1 -29.2  1239384 613296  ??  R10Sep08 
3431:26.73

On 23-Sep-08, at 3:47 PM, Doug Bridgens wrote:
it's all a bit too general, we could be asking continual questions 
until someone asks the right one.


However, I would put some debugging into the 30% scripts to check 
they complete before the next one starts, as if one script takes 
slightly longer (especially if the queries are the same) to 
complete then the rest build u

Re: Ancient, unsolved high-CPU problem -- vmstat, top and ps

[Ken Menzel]

Hi Rene,
  This smells like an old freebsd issue with a non thread safe 
get-host-by-name issue and possibly other thread issues. Since Mac 
OS/X/Darwin is a freebsd 4 branch it is a good bet they are the same. 
Is it possible for you to try adding "skip-name-resolve" to my.cnf. 
Alternatively you could compile with -D SKIP_DNS_CHECK.  Please read 
about these options before trying them to understand any implication it 
my have on your GRANTs if you grant to a domain or server.


Here are some links to more information,
http://jeremy.zawodny.com/blog/archives/000203.html
http://bugs.mysql.com/bug.php?id=414
http://www.mail-archive.com/mysql@lists.mysql.com/msg87497.html

Hope this helps,
Ken

Rene Fournier wrote:
In case a bit more data might help, here's what the server looks like 
right now, while experiencing the strange high-CPU load:



VM_STAT sayeth:

Mach Virtual Memory Statistics: (page size of 4096 bytes)
Pages free:   534327.
Pages active: 331233.
Pages inactive:  1094528.
Pages wired down: 137065.
"Translation faults":  957568490.
Pages copy-on-write:   241306984.
Pages zero filled:1302796176.
Pages reactivated:790261.
Pageins:   95668.
Pageouts:   1212.
Object cache: 217985425 hits of 220226841 lookups (98% hit rate)



Top says:

Processes:  115 total, 3 running, 112 sleeping... 504 threads  
08:12:30
Load Avg:  2.43, 2.44, 2.30 CPU usage:  45.3% user, 48.2% sys, 6.5% 
idle

Networks:   676 ipkts/72K  738 opkts /181K
Disks:   10 reads/52K  594 writes/3049K
VM:   0 pageins  0 pageouts

  PID COMMAND  %CPU   TIME   FAULTS PGINS/COWS MSENT/MRCVD  
BSD/MACHCSW
25943 mysqld  92.6% 57:11:01   6473 0/0  154/154
1121358/3403231
20067 php  9.1%  6:53:45   1764 0/238 14/7  
6128/14  584
25957 Terminal 7.0% 12:20:23150 0/0 1013/814
244/2407648

[...]



And PS:

USER   PID %CPU %MEM  VSZRSS  TT  STAT STARTED  TIME
mysql25943  114.1 -29.2  1239384 613296  ??  R10Sep08 3431:26.73





On 23-Sep-08, at 3:47 PM, Doug Bridgens wrote:

it's all a bit too general, we could be asking continual questions 
until someone asks the right one.


However, I would put some debugging into the 30% scripts to check they 
complete before the next one starts, as if one script takes slightly 
longer (especially if the queries are the same) to complete then the 
rest build up quickly.   Something else could be locking the table 
that your cron queries are trying to access, causing the stacking that 
never recovers.


Once the problem occurs I'd be using 'show processlist' in mysql, and 
vmstat and ps to check the system resources.   Is it definitely mysql, 
or php/apache, a slow disk, etc..


In terms of your stats below, I  have (on a fairly average spec 
server) 500 queries per second and 2000 open tables.   So, unless it's 
a PC or very badly tuned, it should be fine.


cheers,
Doug


On 23 Sep 2008, at 14:16, Rene Fournier wrote:


10% of queries are web-based (Apache/PHP).
30% of queries are from command-line PHP scripts that get executed 
(average 1/second -- they end with mysql_close() btw).
60% of queries are from command-line PHP scripts that run 
continuously (in a loop, with sleep()), acting on incoming socket data.


...Rene

On 23-Sep-08, at 2:20 PM, Jeffrey Santos wrote:


Rene,

How are you querying the database during normal use?  What kind of 
applications are you using?


~Jeffrey Santos

On Mon, Sep 22, 2008 at 2:46 PM, Rene Fournier <[EMAIL PROTECTED]> 
wrote:
Uptime: 1054977  Threads: 10  Questions: 15576766  Slow queries: 
229  Opens: 489  Flush tables: 1  Open tables: 483  Queries per 
second avg: 14.765




I know what the slow queries are--some that take 20-30 seconds to 
compute, and they are normal. The number of open tables seems high, 
no? The database that gets 95% of the load has ~35 tables in total.


As for cron jobs, I have a number of command-line PHP scripts that 
perform regular queries. They've been running for about 10 days now. 
The current high CPU state started a couple days ago.





On 22-Sep-08, at 8:30 PM, Martin Gainty wrote:

curious if you have any cron jobs starting to execute?

what does mysqladmin status show ?

Martin
__
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the 
official business of Sender. This transmission is of a confidential 
nature and Sender does not endorse distribution to any party other 
than intended recipient. Sender does not necessarily endorse content 
contained within this transmission.



> From: [EMAIL PROTECTED]
> To: mysql@lists.mysql.com
> Subject: Ancient, unsolved high-CPU problem
> Date: Mon, 22 Sep 2008 19:41:25 +0200

>
> For the longest time, I've had 

Re: PHP, MySQL questions

[Ken Menzel]

VeeJay wrote:

Hi there

I am going to make 2 Webserver at my work going to handle 50 mil hits per
month... They are using Linux already. But being a FreeBSD fan, I have
proposed MySQL and FreeBSD to my Boss convincing him that MySQL and FreeBSD
is more Fast and Secure solution for his needs... And now I want to show the
results...
*Hardware:
*Dell PowerEdge 2950 III having 2 x CPU 3,0 GHz Intel Xeon L5450 Quad-Core
2x6MB cache WITH 16 GB RAM.

*Tools:*
1. FreeBSD 7 Production Release
2. Apache 2.2.9
3. MySQL 5.1.26
4. PHP 5.2.6

My question is, "*To get the speed, performance and security*":
Should I use Ports or Packages to install all these tools One by One?
*OR
*
Should I use TAR files and compile them manually. For example giving command
line arguments and commands like
./configure --prefix=/www --enable-module=so
make
make install
cd ../php-xxx
./configure --with-mysql --with-apxs=/www/bin/apxs
make
make install
etc


I have googled but still haven't reached to solution...personally I would
prefer comiling them with command line arguments
but then I seek some help from you guys i.e.
How should I write this ./configure..stuff in FreeBSD and what would be
the best options combination, I must choose to get the speed, performane and
security in Apache, MySQL and PHP?
Any suggestion is very welcomed!



This may provide some advice for MySQL of freebsd, looks like you just 
install it from ports.


http://people.freebsd.org/~kris/scaling/mysql.html
http://wiki.freebsd.org/MySQL


Hope this provides some confidence,
Ken

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

Re: enable and disable keys

[Ken Menzel]

Ananda Kumar wrote:

We have 200GB of free space on the file system where our database is
located.

On 6/12/08, Ken Menzel <[EMAIL PROTECTED]> wrote:

Ananda Kumar wrote:


Hi All,
We are doing load data into a table using LOAD DATA INFILE process. Below
is
the method we are following.

1. create empty table with all the indexes.
2. disable keys
3. Load data using LOAD DATA INFILE, close to 99 Million records which
takes
around 3 hrs
4 . Enable keys

Table size is around 19.5 gb

There is one primary key, 2 non unique indexes and one FULLTEXT INDEX.

Enable kyes goes fine for couple of hrs with "REPAIR BY TMP", but then
switches to "REPAIR BY KEYCACHE" and writes a log in the error log
file  "Warning:
Enabling keys got errno 28, retrying"

What could be the problem.

System has 8 cpu and 16GB RAM

I have set myisam_max_sort_file_size=98GB
myisam_sort_buffer_size=750MB.

/tmp folder has 16GB free space.

please let me know, this problem is causing lot of dealy for other process
to run.

regards
anandkl

barclay# perror 28

OS error code  28:  No space left on device

I would say you are running out of space.




What is the value of - show variables like "tmpdir" ?

If you have not set it it will try to use your system /tmp, which does 
not appear to be big enough.

http://dev.mysql.com/doc/refman/5.0/en/temporary-files.html

Hope this helps,
Ken


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

Re: enable and disable keys

[Ken Menzel]

Ananda Kumar wrote:

Hi All,
We are doing load data into a table using LOAD DATA INFILE process. Below is
the method we are following.

1. create empty table with all the indexes.
2. disable keys
3. Load data using LOAD DATA INFILE, close to 99 Million records which takes
around 3 hrs
4 . Enable keys

Table size is around 19.5 gb

There is one primary key, 2 non unique indexes and one FULLTEXT INDEX.

Enable kyes goes fine for couple of hrs with "REPAIR BY TMP", but then
switches to "REPAIR BY KEYCACHE" and writes a log in the error log
file  "Warning:
Enabling keys got errno 28, retrying"

What could be the problem.

System has 8 cpu and 16GB RAM

I have set myisam_max_sort_file_size=98GB
myisam_sort_buffer_size=750MB.

/tmp folder has 16GB free space.

please let me know, this problem is causing lot of dealy for other process
to run.

regards
anandkl


barclay# perror 28
OS error code  28:  No space left on device

I would say you are running out of space.

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

Re: R: rename database in 4.1

[Ken Menzel]

Nanni Claudio wrote:

You can try to dump-to-sql the whole DB,
Create a new DB and import the sql-dump in the new DB.

Aloha!

Claudio


-Messaggio originale-
Da: Thomas Raso [mailto:[EMAIL PROTECTED] 
Inviato: martedì 12 febbraio 2008 17.20

A: mysql@lists.mysql.com
Oggetto: rename database in 4.1

hi list,

how can I rename a database with full innodb tables ?
The version is 4.1

Thnaks all


"Questo messaggio ed ogni suo allegato sono confidenziali e possono essere riservati 
o, comunque, protetti dall'essere diffusi. Se il ricevente non é il destinatario diretto 
del presente messaggio, é pregato di contattare l'originario mittente e di cancellare 
questo messaggio ed ogni suo allegato dal sistema di posta. Se il ricevente non é il 
destinatario diretto del presente messaggio, sono vietati l'uso, la riproduzione e la 
stampa di questo messaggio e di ogni suo allegato, nonché la diffusione del loro 
contenuto a qualsiasi altro soggetto"
*
"This message and any attachment are confidential and may be privileged or otherwise 
protected from disclosure. If you are not the intended recipient, please contact the 
sender and delete this message and any attachment from your system. If you are not the 
intended recipient you must not use, copy or print this message or attachment or disclose 
the contents to any other person."

  
You can also use the rename table command by creating the new database 
name then use rename dbname.table newdbname.table.  You can list 
multiple tables.  You could script it.


Ken

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

Re: mysqld startup failure

[Ken Menzel]

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

To: "jekillen" <[EMAIL PROTECTED]>
Cc: 
Sent: Tuesday, December 11, 2007 9:12 PM
Subject: Re: mysqld startup failure



jekillen wrote:

Hello:
The saga of this machine continues:
FreeBSD v6.2
latest mysql version installed from ports:
mysql51 client, serve, and scripts.
There is no /usr/local/mysql
it did not create this dir.


No idea where it should put the files, a freebsd list might be able 
to point you in the right direction.




Try /usr/local/var/mysql




/usr/local/libexec/mysqld: Can't find file: 
'./mysql/general_log.frm' (errno: 13)
071121 15:04:38 [ERROR] /usr/local/libexec/mysqld: Can't find file: 
'./mysql/host.frm' (errno: 13)
071121 15:04:38 [ERROR] Fatal error: Can't open and lock privilege 
tables: Can't find file: './mysql/host.frm' (errno: 13)

071121 15:04:38  mysqld ended
where is './mysql/ (etc)? the ./ tells me it is relative to 
/usr/local/libexec
I have followed the published instruction steps to configure build 
and install
from source dist and it specifies that data dir should be change to 
owner mysql
but where is the data dir. It is not in /var, unless it is supposed 
to be in /var/db
(where I found the hostname error file). Errno: 13 means access 
denied as I
understand it. So what dir and files do I change privileges for, if 
that is in fact,

the problem?


Yep 13 means permission problems (run 'perror 13' at your prompt).

The data dir should be owned by the mysql user & group.

mysqld --print-defaults

should tell you everything you need.

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





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

Re: FreeBSD 6.1 + Libthr + MySQL 5.0.24a max connection issue or bug?

[Ken Menzel]

Have a look at kern.threads.max_threads_per_proc: 1500
and kern.threads.max_groups_per_proc: 1500

You are probably hitting one of these sysctl's.  Here is a link to 
some other info 
http://unix.derkeiler.com/Mailing-Lists/FreeBSD/performance/2006-01/msg2.html


There is also a wiki on Freebsd and MySQL at 
http://wikitest.freebsd.org/MySQL


Hope this helps,
Ken
- Original Message - 
From: "Abdullah Ibn Hamad Al-Marri" <[EMAIL PROTECTED]>

To: 
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, October 03, 2006 4:13 PM
Subject: FreeBSD 6.1 + Libthr + MySQL 5.0.24a max connection issue or 
bug?



Hello folks,

I hope Greg is reading this list

I use MySQL 5.0.24a from the FreeBSD ports, with libthr threading.

I have a huge app makes alot of connections to MySQL server.

The max I could reach is 1500 threads, even I made the max connection 
in my.cf 5k and I used


kern.threads.max_threads_per_proc: to increase it to 5K.

here is the my top in the box.

45101 mysql 1500 4 0 477M 166M sbwait 0 11:36 0.00% mysqld

when it reachs 1500 I get this msg below

"Could not connect to db"

My server has dual xeon and 2 gig of ram.

Any help would be appreciated.

Thank you,

-Abdullah



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

Re: MySQL 4.0.27-client and MySQL-4.1.21 server

[Ken Menzel]

http://dev.mysql.com/doc/refman/4.1/en/password-hashing.html

enable old-passwords in my.cnf

Hope that helps,
Ken
- Original Message - 
From: "Odhiambo Washington" <[EMAIL PROTECTED]>

To: 
Sent: Tuesday, August 22, 2006 1:38 PM
Subject: MySQL 4.0.27-client and MySQL-4.1.21 server




hi

Why is it that I cannot connect to mysql-4.1 server using 
mysql-4.0.27

client?

ERROR 1251: Client does not support authentication protocol 
requested by

server; consider upgrading MySQL client

Where is the RTFM on this? I just want to read it.



-Wash

http://www.netmeister.org/news/learn2quote.html

DISCLAIMER: See http://www.wananchi.com/bms/terms.php

--
+==+
   |\  _,,,---,,_ | Odhiambo Washington 
<[EMAIL PROTECTED]>

Zzz /,`.-'`'-.  ;-;;,_ | Wananchi Online Ltd.   www.wananchi.com
  |,4-  ) )-,_. ,\ (  `'-'| Tel: +254 20 313985-9  +254 20 313922
 '---''(_/--'  `-'\_) | GSM: +254 722 743223   +254 733 744121
+==+

Any fool can paint a picture, but it takes a wise person to be able 
to

sell it.

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



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

Re: mysqldump large file (>2GBs) : some tests

[Ken Menzel]

Hi,
I just saw your e-mail.

 We use mysqldump on solaris 9 with large files all the time.  Is the 
partition you are writng the file to mounted with the largefiles 
option in /etc/mnttab?


Our mysql dump files are 25 gig.

Ken



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

To: 
Cc: "Duhaime Johanne" <[EMAIL PROTECTED]>
Sent: Monday, July 24, 2006 1:50 PM
Subject: mysqldump large file (>2GBs) : some tests


Hello

This is kind of a follow up of the previous thread "Got errno 27 on
write. file too large" and kind of a survey. The problem was :  on a
solaris 9, 64 bits and mysql 4.1.7, I could not mysqldump in a file 
more

than 2G .  Thank you to the people that help me.

I make a new thread since my question is different: is there anybody
using that version of mysql (more or less) and that version of Solaris
and be able to mysqldump more than 2GBs. If so I would appreciate to
have more details on revision etc. ?

Here is why I am asking this question:

Since it appears it was the revision on my server that might be the
problem, but because the upgrade could not be done in short term and
because Sun Support did not find any problem of that sort on the 
system,

I decide to forget about the mysqldump for now and copy files for
backup.

But then I wondered if, later on, as my datafiles increase in size, I
will have the problem with the 2 gbs limitation for the datafiles
themselves . Which would be much worse. But I  succeded in dumping 
4gbs

in one table. Good news! Then I wonder if my OS allows to do that, how
come it does not work for mysqldump.


Thank you in advance

Johanne Duhaime
[EMAIL PROTECTED]



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

Re: Fixing "the worst InnoDB corruption bug in 3 years" - when

[Ken Menzel]

Hi Bruce

- Original Message - 
From: "Bruce Dembecki" <[EMAIL PROTECTED]>
To: 
Sent: Thursday, December 30, 2004 2:51 AM
Subject: Re: Fixing "the worst InnoDB corruption bug in 3 years" - 
when
As a side note with demonstrated performance increases when using
innodb_file_per_table why aren't more people using it?
Best Regards, Bruce

What demonstrated performance increases are you referring to?  I would 
love to use file_per_table, but as it is new we are very conservative 
with our production DB's and this feature is too new.  But if there 
are "demonstrated performance increases" we would love to start using 
it!

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

Re: FreeBSD and MySQL - mysqld eats CPU alive

[Ken Menzel]

Hi,
   It sounds like you are using the default thread library.  I would
install MySQL from /usr/ports/databases (WITH_LINUX_THREADS, see
options in Makefile) or barring that try Jeremey's excellent build
recipe at http://jeremy.zawodny.com/blog/archives/000458.html  .  We
used to have the same problem and everything runs great now.

Ken

- Original Message - 
From: "adp" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, July 30, 2004 2:58 PM
Subject: FreeBSD and MySQL - mysqld eats CPU alive


> I have several MySQL and FreeBSD installs across a few different
sites, and
> I consistently have problems with mysqld. It will begin to eat up
all of the
> CPU and eventually become unresponsive (or the machine will just
burn). I
> can't seem to manually reproduce this, but given enough time a
FreeBSD box
> with mysqld will go down.
>
> Our servers are generally heavily loaded.
>
> I would say that I'm doing something wrong (although what I could be
doing
> wrong I'm not sure), but I recently began working with another
company that
> has the EXACT SAME PROBLEM. They are even thinking of moving to
PostgreSQL,
> but we are trying to fix mysqld instead for now.
>
> This behavior has been seen on:
>
> FreeBSD 4.4, 4.7, 4.9, 4.10
> MySQL 3.x and 4.x
> Typical load: 50 qps
> With and without replication enabled.
> Some sites are SELECT heavy, some are INSERT heavy.
>
> For one site I think we will be moving from FreeBSD to Linux for the
MySQL
> servers since MySQL seems to run like a champ on Linux. We will
continue to
> use FreeBSD for everything else.
>
> Anyone experienced this problem? Is it mysqld or FreeBSD? I can't
pinpoint
> the exact issue.
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>


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

Re: table_cache size for large value in opened_tables

[Ken Menzel]

Hi Terence,

> Hi all,
>
> My system reports:
>
> Open_tables 512
> Opened_tables 24,429
>
> The docs say that if the latter is high I should increase the table
> cache size. (currently at 512)

Looks like you should do that.

>
> How does one decide what size to increase it to? And is there a
problem
> with one of the applications that's making this figure so high? Or
is
> this normal behaviour?

Whether this is a problem or not really depend on you application and
how busy it is.  Generally you want to avoid reopening tables on a
busy server.

There are two methods for knowing how to increase this number.
1) count how many tables you have in your databases and set this
number larger.
2) Your app will probably not use all tables all the time so
experiment with larger numbers.

Remember to make sure the maximum open files limit of your OS and user
that runs mysql is not exceeded. Run limit to determine what this is
and make sure that you check for the user that runs MySQL.


Hope this helps,
Ken


>
> OS: RH9
> Dual 2.4 Xeon
> 1 GIG RAM
>
> (btw, this kind of question i linked to my previous post for a
> performance tuning guide)
>
> Thanks!
>
> Here's my status
>
> Variable_name,Value,
> Aborted_clients,801,
> Aborted_connects,14,
> Bytes_received,1195564158,
> Bytes_sent,1491507399,
> Com_admin_commands,42960,
> Com_alter_table,317,
> Com_alter_db,0,
> Com_analyze,0,
> Com_backup_table,0,
> Com_begin,0,
> Com_change_db,1654089,
> Com_change_master,0,
> Com_check,0,
> Com_commit,59,
> Com_create_db,9,
> Com_create_function,0,
> Com_create_index,0,
> Com_create_table,465,
> Com_delete,100132,
> Com_delete_multi,21,
> Com_do,0,
> Com_drop_db,2,
> Com_drop_function,0,
> Com_drop_index,0,
> Com_drop_table,549,
> Com_flush,13,
> Com_grant,1,
> Com_ha_close,0,
> Com_ha_open,0,
> Com_ha_read,0,
> Com_help,0,
> Com_insert,6887163,
> Com_insert_select,48,
> Com_kill,0,
> Com_load,14,
> Com_load_master_data,0,
> Com_load_master_table,0,
> Com_lock_tables,15,
> Com_optimize,42,
> Com_purge,0,
> Com_purge_before_date,0,
> Com_rename_table,0,
> Com_repair,42,
> Com_replace,7873,
> Com_replace_select,0,
> Com_reset,0,
> Com_restore_table,0,
> Com_revoke,0,
> Com_rollback,17,
> Com_select,3152624,
> Com_set_option,27303,
> Com_show_binlog_events,0,
> Com_show_binlogs,0,
> Com_show_charsets,0,
> Com_show_column_types,0,
> Com_show_create_table,456,
> Com_show_create_db,0,
> Com_show_databases,89,
> Com_show_errors,0,
> Com_show_fields,1775,
> Com_show_grants,0,
> Com_show_keys,1643,
> Com_show_logs,0,
> Com_show_master_status,0,
> Com_show_new_master,0,
> Com_show_open_tables,0,
> Com_show_privileges,0,
> Com_show_processlist,73,
> Com_show_slave_hosts,0,
> Com_show_slave_status,0,
> Com_show_status,240,
> Com_show_innodb_status,0,
> Com_show_tables,2846,
> Com_show_table_types,0,
> Com_show_variables,96,
> Com_show_warnings,0,
> Com_slave_start,0,
> Com_slave_stop,0,
> Com_truncate,0,
> Com_unlock_tables,15,
> Com_update,5146761,
> Com_update_multi,0,
> Connections,108272,
> Created_tmp_disk_tables,71315,
> Created_tmp_tables,725937,
> Created_tmp_files,0,
> Delayed_insert_threads,0,
> Delayed_writes,0,
> Delayed_errors,0,
> Flush_commands,1,
> Handler_commit,219,
> Handler_delete,941394,
> Handler_read_first,483459,
> Handler_read_key,102941817,
> Handler_read_next,213625710,
> Handler_read_prev,448018710,
> Handler_read_rnd,3049520,
> Handler_read_rnd_next,222363654,
> Handler_rollback,16312,
> Handler_update,37894671,
> Handler_write,27010243,
> Key_blocks_used,353833,
> Key_read_requests,341367134,
> Key_reads,1172755,
> Key_write_requests,47942190,
> Key_writes,38265419,
> Max_used_connections,106,
> Not_flushed_key_blocks,0,
> Not_flushed_delayed_rows,0,
> Open_tables,512,
> Open_files,873,
> Open_streams,0,
> Opened_tables,30492,
> Questions,25298875,
> Qcache_queries_in_cache,23808,
> Qcache_inserts,2368548,
> Qcache_hits,8206562,
> Qcache_lowmem_prunes,29375,
> Qcache_not_cached,784005,
> Qcache_free_memory,40477464,
> Qcache_free_blocks,2311,
> Qcache_total_blocks,50436,
> Rpl_status,NULL,
> Select_full_join,12285,
> Select_full_range_join,16,
> Select_range,70254,
> Select_range_check,1,
> Select_scan,1618573,
> Slave_open_temp_tables,0,
> Slave_running,OFF,
> Slow_launch_threads,0,
> Slow_queries,201,
> Sort_merge_passes,0,
> Sort_range,132898,
> Sort_rows,2993422,
> Sort_scan,576536,
> Table_locks_immediate,17191738,
> Table_locks_waited,70304,
> Threads_cached,4,
> Threads_created,12439,
> Threads_connected,18,
> Threads_running,1,
> Uptime,1186406,
>
>
>
> Variable_name,Value,
> back_log,50,
> basedir,/usr/local/mysql/,
> binlog_cache_size,32768,
> bulk_insert_buffer_size,8388608,
> character_set,latin1,
> character_sets,big5 latin2_czech_ci dec8_swedish_ci cp850_general_ci
> latin1_german1_ci hp8_english_ci koi8r_general_ci latin1_swedish_ci
> latin2_general_ci swe7_swedish_ci ascii_general_ci ujis sjis
> cp1251_bulgarian_ci latin1_danish_ci hebrew tis620 euckr
> latin7_estonian_ci latin2_hungarian_ci ko

Re: MySQL limits.

[Ken Menzel]

Hi,
  Signal 11 can also indicate hardawre problems on BSD.  Also FreeBSD
might get you more answers quickly as there are more of us running
FreeBSD with MYSQL for some reason.  We runn FreBSD w MySQL/Linux
threads on 4.9 and 5.2 and both work just fine.

Ken
- Original Message - 
From: "RV Tec" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, May 18, 2004 9:28 AM
Subject: MySQL limits.


> Folks,
>
> I have a couple of questions that I could not find  the answer
> at the MySQL docs or list archives. Hope you guys can help me.
>
> We have  a database  with approximately  135 tables  (MyISAM).
> Most of them are small,  but we have 5 tables,  with 8.000.000
> records. And  that number  is to  increase at  least 1.000.000
> records per month (until the end of the year, the growing rate
> might surpass 2.000.000 records/month). So, today our database
> size is 6GB.
>
> The server handles about 35-40 concurrent connections. We have
> a lot of table locks, but that does not seem to be a  problem.
> Most of the time it works really well.
>
> >From time to time  (2 weeks uptime or  so), we have to  face a
> Signal 11 crash (which is pretty scary, since we have to run a
> myisamchk  that  takes us  offline  for at  least  1 hour). We
> believe this  signal 11  is related  to the  MySQL server load
> (since we have changed OS's and hardware -- RAM mostly).
>
> Our server  is one  P4 3GHz,  2GB RAM  (400mhz), SCSI Ultra160
> 36GB  disks (database  only) running  on OpenBSD  3.5. We  are
> aware  that  OpenBSD  might  not  be  the  best  OS  for  this
> application... at first, it  was chosen by it's  security. Now
> we  are looking  (if that  helps) to  a OS  with LinuxThreads
> (FreeBSD perharps?).
>
> The fact is that we  are running MySQL on a  dedicated server,
> that  keeps the  load between  0.5 and  1.5. CPU  definitively
> is not  a  problem. The  memory  could  be  a  problem...  our
> key_buffer is set to 384M, according to the recommendations at
> my-huge.cnf. So,  it seems  we have  a lot  of free memory. We
> have  already  tried  to increase  key_buffer (along  with the
> other  settings),  but it does not seem to  hurt or to improve
> our performance (although, the memory use increases).
>
> To track down this signal 11, we have just compiled MySQL with
> debugandreturned   totheoriginal   my-huge.cnf
> recommendations.  Now it seems we are running on a overclocked
> 486 66mhz.
>
> Is there any way to prevent this signal 11 to happen or is  it
> a message that we have exceeded MySQL capability?
>
> Is MySQL able to handle such load with no problems/turbulences
> at  all?   If  so,   what  would   be  the   best  hardware/OS
> configuration?
>
> What is the largest DB known to MySQL community?
>
> If it's needed, I can provide DMESG, MySQL error log,  compile
> options and some database statistics.
>
> Thanks a lot for your help!
>
> Best regards,
> RV Tec
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>


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

Re: can't load library

[Ken Menzel]

Hi Bob,
   I don't use openBSD, but from the FreeBSD ldconfig man page I have:
 -m  Instead of replacing the contents of the hints file with
those
 found in the directories specified, ``merge'' in new
entries.
 Directories recorded in the hints file by previous runs
of
 ldconfig are also rescanned for new shared libraries.

So I would guess you want ldconfig -m,  or reboot your system and I am
sure it will scan for new shared libraries.

Best of luck.
Ken
- Original Message - 
From: "Bob DeBolt" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, May 05, 2004 8:12 PM
Subject: can't load library


> Greets
>
> I have installed mysql 4.0.18 on an OpenBSD 3.4 box using
> the D/L from the mysql.com site.
>
> All compiled / installed well and in fact nearly everything works.
The
> problem I have is when attempting to use mysql I get the following
error.
>
> mysql: can't load library ../libmysql/.libs/libmysqlclient.so.12.0
>
> I have not come across this error before as the configure, make &&
make
> installs
> have worked fine, not that I install mysql on a real regular basis
at this
> point.
>
> The library does exist but I am curious as to why mysql tries
looking
> through the installation directory. ldconfig -r doesn't reveal the
> presence of this library though.
>
> I know this is an extremely simple fix, just need to know which
buttons to
> push.
>
>
> Sincerely
>
> Bob DeBolt
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>


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

Re: Range query on datetime with index - any optimization?

[Ken Menzel]

Should work fine.  Quick test from a large sales test table (1.9
million rows):
mysql> select count(*) from sali;
+--+
| count(*) |
+--+
|  1983026 |
+--+
1 row in set (0.00 sec)

mysql> select salidate,sum(saliQtySold) from sali where salidate >
DATE_SUB(CURRENT_DATE, INTERVAL 60 DAY) group by to_days(salidate);
+-+--+
| salidate| sum(saliQtySold) |
+-+--+
| 2004-03-17 13:44:37 |2 |
| 2004-03-24 14:40:33 |2 |
+-+--+
2 rows in set (0.00 sec)

mysql> explain select salidate,sum(saliQtySold) from sali where
salidate > DATE_SUB(CURRENT_DATE, INTERVAL 60 DAY) group by
to_days(salidate);
+---+---+---+--+-+--+--+--
+
| table | type  | possible_keys | key  | key_len | ref  | rows |
Extra|
+---+---+---+--+-+--+--+--
+
| sali  | range | saliDate  | saliDate |   8 | NULL |   70 |
Using where; Using temporary; Using filesort |
+---+---+---+--+-+--+--+--
+
1 row in set (0.00 sec)

mysql>


- Original Message - 
From: "Jeremy Zawodny" <[EMAIL PROTECTED]>
To: "Pete McNeil" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Wednesday, May 05, 2004 3:00 PM
Subject: Re: Range query on datetime with index - any optimization?


> On Wed, May 05, 2004 at 10:53:13AM -0400, Pete McNeil wrote:
> > Hello folks,
> >
> > I'm usinng MySQL 4.0.17.
> >
> > I have a table something like:
> >
> > RuleID int,
> > GMTBase datetime,
> > Credited bigint,
> > ...
> >
> > I have an index built on GMTBase.
> > I have rougly 8 million rows.
> >
> > GMTBase stores a datetime for the top of the hour on a given date.
> >
> > I want to build a summary of the last 2 days without scanning
every record.
> >
> > It appears that there is no way to get MySQL to use the index on
GMTBase to
> > avoid scanning all 8 million rows. I estimate it should only scan
about
> > 267K rows. Explain mentions the GMTBase index but says it will
examine
> > about a million rows. That seems to roughly match my estimate of
the number
> > of distinct GMTBase values.
> >
> > The query I want to run is:
> >
> > select RuleID, GMTBase, sum(Credited)
> > from RuleHistograms
> > where GMTBase > DATE_SUB(CURRENT_DATE, INTERVAL 2 DAY)
> > group by GMTBase
> >
> > Have I done something wrong or is there simply no way to avoid
scanning all
> > of those records?
>
> *Something* is wrong.  I'm virtually certain I've done this sort of
> thing before without having MySQL perform full table scans.  I can't
> tell what it is off the top of my head, but it should be that bad.
>
> Jeremy
> -- 
> Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
> <[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/
>
> [book] High Performance MySQL -- http://highperformancemysql.com/
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>


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

Re: InnoDB - Foreign Key - Error 150.

[Ken Menzel]

Hi Guy's,
first:  Your are correct it is contraints:
bash-2.05a$ perror 150
Error code 150:  Unknown error: 150
150 = Foreign key constraint is incorrectly formed

Second you could also  try:

set foreign_key_check=0;

at the beginning of the restore file.
set foreign_key_check=1;

at the end!
Best of luck,
Ken
- Original Message - 
From: "Marvin Wright" <[EMAIL PROTECTED]>
To: "Tucker, Gabriel" <[EMAIL PROTECTED]>; "Mysql General
(E-mail)" <[EMAIL PROTECTED]>
Sent: Tuesday, May 04, 2004 10:22 AM
Subject: RE: InnoDB - Foreign Key - Error 150.


> Hi,
>
> I don't think mysqldump takes foreign key constraints into account
when
> dumping them.
> You could specify the tables that you want when you dump so you get
the
> correct order.
>
> e.g.
>
>
mysqldump --allow-keywords --host=$HOST --flush-logs --disable-keys --
opt
>   --port=$1 --socket=/bb/bin/mysql/sockets/mysql.sock.$1
>   --master-data  object_type cur_reject_tk_sum
>   -u mysql -p > /bb/bin/mysql/backups/archive_$1.sql
>
> I dont know your database name do you would have to substitute that.
> this should create the dump in the correct order for your restore.
>
> Marvin.
>
>
> -Original Message-
> From: Tucker, Gabriel [mailto:[EMAIL PROTECTED]
> Sent: 04 May 2004 15:13
> To: Mysql General (E-mail)
> Subject: RE: InnoDB - Foreign Key - Error 150.
>
>
> Marvin
>
> I believe that is the problem with the restore.  When I create the
archive
> file using the mysqldump command and options previously listed, I
get the
> create table in the order listed below and thus, the foreign key
constraint
> is created on table cur_reject_tk_sum before the object_type table
has been
> created.
>
> Now my questions are:
> [1] Is the above scenario my problem?
> [2] Is so, how can I correct it?  Is this a problem with the way I
am using
> mysqldump? [see commands below]  Or, is this a problem with how I am
> restoring the database? [Which, I create a "default" mysql database
on its
> own port and then run from the prompt "mysql --port= --socket= -p <
> archive_file.sql"
>
> Thanks again
> Gabe
>
> -Original Message-
> From: Marvin Wright [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 04, 2004 9:59 AM
> To: Tucker, Gabriel; Mysql General (E-mail)
> Subject: RE: InnoDB - Foreign Key - Error 150.
>
>
> Hi,
>
> Are you creating them in the correct order ?
>
> object_type must exist before you can create cur_reject_tk_sum
otherwise the
> foreign key will give errors.
>
> Marvin
>
>
> -Original Message-
> From: Tucker, Gabriel [mailto:[EMAIL PROTECTED]
> Sent: 04 May 2004 14:46
> To: Mysql General (E-mail)
> Subject: InnoDB - Foreign Key - Error 150.
>
>
> Hello All:
>
> I am having a problem with both V4.0.16 and 4.0.18.  Let me explain:
>
> I have a database with two InnoDB tables in v4.0.16.  I backup up
this
> database every night using the following command:
>
> mysqldump --allow-keywords --host=$HOST --flush-logs --disable-keys
> --opt --port=$1 --socket=/bb/bin
> /mysql/sockets/mysql.sock.$1 --master-data --all-databases -u mysql
> -p > /bb/bin/mysql/backups/archive_$1.sql
>
> I went to restore it the other day and I got this error:
>
> ERROR 1005 at line 20: Can't create table
'./fxprmet/cur_reject_tk_sum.frm'
> (errno: 150)
>
> I thought this might be a version issue, so I copied all the
database files
> to another location, and started it using v4.0.18.  I then dropped
the
> foreign key constraint (no errors) and recreated it (no errors).  I
backed
> up the database.  And when I went to restore it, I got the same
error.
>
> The schema [after I deleted and re-added the constraint]:
>
> CREATE TABLE cur_reject_tk_sum (
>   cur_reject_tk_id int(11) NOT NULL auto_increment,
>   contrib_swift_cd char(4) NOT NULL default '',
>   object_id char(8) binary NOT NULL default '',
>   tick_date date NOT NULL default '-00-00',
>   object_type_cd smallint(6) NOT NULL default '0',
>   num_received int(11) default NULL,
>   num_rejected int(11) default NULL,
>   num_spikes_contrib int(11) default NULL,
>   num_spikes_compos int(11) default NULL,
>   num_spread int(11) default NULL,
>   num_filter int(11) default NULL,
>   num_delayed int(11) default NULL,
>   num_maybe int(11) default NULL,
>   num_diff int(11) default NULL,
>   num_bid_gt_ask int(11) default NULL,
>   num_ask_no_bid int(11) default NULL,
>   num_double_bid_ask int(11) default NULL,
>   time_first_reject time default NULL,
>   time_last_reject time default NULL,
>   PRIMARY KEY  (cur_reject_tk_id),
>   UNIQUE KEY xpkcur_reject_tk_s (cur_reject_tk_id),
>   UNIQUE KEY xak1cur_reject_tk_
> (contrib_swift_cd,object_id,tick_date),
>   KEY xif1cur_reject_tk_ (object_type_cd),
>   KEY xie1cur_reject_tk_ (tick_date),
>   KEY xie2cur_reject_tk_ (object_id),
>   CONSTRAINT `gabe_test` FOREIGN KEY (`object_type_cd`) REFERENCES
> `object_type` (`object_type_cd`)
> ) TYPE=InnoDB;
>
> --
> -- Table structure for table `object_type`
> --
>
> CREATE TABLE object_type (
>   object_type_cd smallint(

Re: [client] var not working

[Ken Menzel]

hi Lou,

I believe this is a recent feature so make sure you on on 4.0.18 if
possible,  I know is was added before 4.0.14.  I am not sure this
works on 3.23.

Ken
- Original Message - 
From: "Lou Olsten" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, April 29, 2004 10:38 AM
Subject: [client] var not working


Per a response from Victoria (thanks, BTW!) I see that I can reload my
InnoDB RI's data by turning off SET FOREIGN_KEY_CHECKS = 0 during the
restore.  I then tried to go to the machine where the dumps were going
to be restored and set this variable in the [client] section of
my.cnf.  But when I try to connect after that, I get an:

"ERROR: unknown variable 'foreign_key_checks=0'.

I then tried using:

set variable=foreign_key_checks=0

...but got the same result.

Is there a list of variables that I *can* use in the [client] section,
or am I just doing something wrong.

Thanks,

Lou Olsten


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

Re: table locking using perl DBI

[Ken Menzel]

Hi Andy,
The best way to accomplish this is at an application level,  setup a
lock table and put your locks in there.  There are many methods for
handling application locks. Choose one that work for you.  With a
database level lock, when the connection terminates the lock is
released.

Persistant connections might be an option, but you can't guarantee
your CGI will get the same connection and be able to release the lock!

Hope this helps,
Ken
- Original Message - 
From: "Andy Ford" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, April 26, 2004 10:01 PM
Subject: table locking using perl DBI


> I have a perl cgi script that needs to lock the tables.
Unfortunately,
> as HTTP is a one shot protocol, once the cgi script completes
execution,
> the tables are unlocked.
>
> I need it to stay locked until another cgi script unlocks them!
>
> Is there any other way of achieving this!?
>
> Thanks
>
> Andy
>
>
> -- 
>
> perl -e 'print qq^;@) [###]^^qq^z\.MY{eLQ9^'
> in:control developer, Telindus, RG27 9HY
> DDI: +44 1256 709211, GSM: +44 7810 636652
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>


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

Re: 10 minutes seems excessive...

[Ken Menzel]

Seems like MySQL is selecting the primary key, or thinks that reading
all rows is faster than using the month_day key.
What happens if you do one day at a time with an = as in:
WHERE month_day = 14

or WHERE month_day = 14 AND month_day=7 (etc)

Does it run faster?  Does the explain show key used=month_day then?

Ken

- Original Message - 
From: "Mailing Lists" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, April 21, 2004 3:04 AM
Subject: 10 minutes seems excessive...


> Hello all...
>
> Source table has approximately 23,000,000 rows.  The explain below
shows
> we'll be working with 2,707,366 of those rows just inserting them
into a
> table.  Why does this take 10 minues to run?  System specifications
> below all that.
>
> * Precompiled binary from mysql.com
> * Completely idle machine
> * Completely idle disk
> * Not I/O bound during query.
> * Mysqld maxing one cpu out reading data, but not writing to any
temp
> tables on disk for quit some time.
> * Started with mysql-huge reference config.
>
>
> 'explain select * from below':
> ==
> table: source_table
>  type: range
> possible_keys: PRIMARY,MONTH_DAY
>   key: PRIMARY
>   key_len: 1
>   ref: NULL
>  rows: 2707366
> Extra: Using where
>
> CREATE TABLE tmp.seperate_disk_partition
> SELECT * FROM source_table
> WHERE month_day IN (14, 7, 31, 24)
>
> Query OK, 2862629 rows affected (9 min 37.36 sec)
> Records: 2862629  Duplicates: 0  Warnings:
>
> * Sun 420, 4-400mhz, 4gb ram.  100gb EMC attached via fiber channel.
>
> Server version4.0.18-standard
> Protocol version  10
> ConnectionLocalhost via UNIX socket
> UNIX socket   /tmp/mysql.sock
> Uptime: 7 days 9 hours 6 min 50 sec
>
> Threads: 1  Questions: 3103  Slow queries: 89  Opens: 352  Flush
tables:
> 1  Open
> >C compiler:gcc (GCC) 3.3
> >C++ compiler:  gcc (GCC) 3.3
> >Environment:
>   
> System: SunOS f10212-06.adc1.level3.com 5.8 Generic_108528-22 sun4u
> sparc SUNW,U
> Architecture: sun4
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>


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

Re: mysql using big two tables in BSD

[Ken Menzel]

Hi Kamlesh,
   You should send an explain of the query,  but if there is no index
on tableB.user_id your join will not work well, since the actual join
would be on tableB.user_id=tableA.user_id

Either change your index on table b to be user_id,user_data2 or add
this index.

Hope this helps,
Ken
Ken
- Original Message - 
From: "kamlesh pandey" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, April 21, 2004 5:10 PM
Subject: mysql using big two tables in BSD


> Hi,
>   I'm new to mysql.
>   I have two big tables ,tableA>4GB and tableB>1GB.
>   both tables around 10 million rows,each.
>
>   tableA has following two cols.
>
>   user_id(varchar(255) PRIMARY KEY
>   user_data1(MEDIUMTEXT)
>   entries looks like
>
>  user1 xyz
>  user2 x1..
>
>  tableB has following cols
>
>  user_data2:varchar(50)
>  user_id(varchar(255)).
>
> tableB is indexed on (user_data2,user_id) and on
> user_id.
> there is no primary key in tableB since,it can have
> entries like
> A   user1
> B   user1
> A   user2
> C   user2.
>
> I NEED to SELECT data from both tables as follwoing
>
> SELECT tableA.userid,tableA.user_data1 from
> tabelA,tableB where tableB.user_data2="myinput" AND
> tableB.user_id=tableA.user_id
>
> It was good while the table size was small,but since
> the table size is big and growing,the query is
> becoming slow.
> I'm using  mysql_use_result().
> to get the result.
>
> Any suggestion either on client query or server tuning
> will
> be helpful.
>
> thanks
>
>
>
> =
> "Don't worry about the world coming to an end today. It's already
tomorrow in Australia."
>
> ¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø¤º°¤º°
> Do You Yahoo !
> ¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø¤º°¤º°
>
>
>
>
> __
> Do you Yahoo!?
> Yahoo! Photos: High-quality 4x6 digital prints for 25¢
> http://photos.yahoo.com/ph/print_splash
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>


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

Re: How to protect MySQL server from intruders ?

[Ken Menzel]

Hi Sam,
   There are many methods and you should alsways employ more than one.
The first is to use the grant system inside of mysql
http://dev.mysql.com/doc/mysql/en/GRANT.html

If you want no network access at all use
--skip-networking
Don't listen for TCP/IP connections at all. All interaction with
mysqld must be made via named pipes (on Windows) or Unix socket files
(on Unix). This option is highly recommended for systems where only
local clients are allowed.
http://dev.mysql.com/doc/mysql/en/Server_options.html


Also you could use a firewall between your system and the outside
world.  If you need to allow outside access use a VPN.  If that is not
an option some systems employ "wrappers" look at /etc/hosts.allow or
hosts_access.  You could also install a software firewall on your
system.  But this is outside the scope of this mailing list.

Hope this helps,
Ken

- Original Message - 
From: "Sam" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, April 12, 2004 3:31 PM
Subject: How to protect MySQL server from intruders ?


> Hello Group 
>
> I would like to know if there is some thing I can do
> during the configuration of MySQL server so that I
> could restrict only one user from accessing the
> database and all others are restricted from the access
> of the databas?
>
> I mean I do not want anybody else to access the
> database from the outside world, except one particular
> application (with one username and password) which I
> would like to give access to , that would be running
> in the same system as the database 
>
> Bottomline restrict the outside world traffic for the
> database !!!
>
> =
>
>Sam
>
> _o  /\  __o
>   -< \_/  \    <  \,
> __(_)/_(_)/\___(_) /_(_)__
>
>
>
>
>
>
>
>
>
>
>
>
>
> __
> Do you Yahoo!?
> Yahoo! Tax Center - File online by April 15th
> http://taxes.yahoo.com/filing.html
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>


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

Re: FBSD 5.2.CURRENT-p4 and mysqld problems

[Ken Menzel]

Are you able to run show process list?  What is the status of your
query? What date was your freeBSD 5-current built on?  Please send
output of uname -a, so that we know exactly which version on which
date because -current changes every day.
Also are you using libmap.conf?  If yes, what are it's contents.  What
does top and ps show when this query is "locked up", can you include
output of this? Why do you feel you MUST use freebsd5 (I am just
curious)?  Have you tried a GDB backtrace (please use debug version of
mysql!)?

I know you already know this, but you shouldn't be using 5-current on
a production server and even the releases such as 5.2.1 can have
issues that you need to be aware of.

Also, one final recommendation,  if you are using current and have not
updated in the last 30 to 60 days I would recommend that you update
again as many thing have been changed and fixed.

Hope this helps,
Ken

- Original Message - 
From: "Ganbold" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Sunday, April 11, 2004 11:35 PM
Subject: FBSD 5.2.CURRENT-p4 and mysqld problems


> Hi,
>
> I found some problematic queries which locks mysql server any
further
> processing. I'm using mysql-4.0.18
> from FreeBSD ports collection. I'm using FreeBSD-5.2-CURRENT.
> Some of the queries like :
>
> select sum(total_amount) pbc from customers.payment where
contract_id>=0
> and contract_id<=4999 and
from_unixtime(time_stamp,'%Y-%m')='2004-03';
>
> Above query is not using any index. It locks mysql server and all
other
> following queries go into queue until I kill locked mysqld thread.
> After killing locked mysqld thread everything goes back to function
normally.
> I made composite index on contract_id,time_stamp,total_amount and
let's see
> what will happen.
>
> In the meantime, can somebody explain me what the problem is?
> I already sent email to freebsd-current and freebsd-ports mailing
list
> about this problem.
> Does somebody have this problem before? Let me know if there is any
solution.
> I know I should use FreeBSD 4.9 stable since it doesn't have any
problem
> with this issue. However I need to solve
> this problem on FreeBSD-5.2-CURRENT.
>
> thanks in advance,
>
> Ganbold
>
>
>
> At 01:36 PM 05.04.2004, you wrote:
> >yes,  we resolved the issue it looks like.  I wanted
> >to hold off on announcing this until i was sure, but
> >it's been running for about 24 hours now without a
> >lockup.
> >
> >options MAXDSIZ="(1024*1024*1024)" # change max from
> >512M to 1G
> >add that to your kernel config.
> >
> >basically whats happening ( and i'm probably wrong
> >with the technicals on this, but bear with me :D ) is
> >that mysqld would aquire a lock (on a table more then
> >likely) and then try to allocate above the FBSD 5.2
> >max default memory size (512M).  when it did this, it
> >crashed and left the lock on the table.  from there it
> >just froze.
> >
> >making that change seems to have fixed it.  it was
> >never evident before because we had a slower webserver
> >that wasn't loading the sql server all that much.  now
> >it is, and we started developing problems. :).
> >
> >as you can tell with this link:
> >http://sql.tribalwar.com/before-ps.txt the mysqld proc
> >wen't above 512M.
> >
> >check your sql.err log, if you're getting a malloc
> >error, then this is more then likely your problem.
> >
> >Thanks to everyone for the help in tracking this down.
> >
> >Let me know if this fixes the problem for you.  Also,
> >are you running -CURRENT in production? or testing?  I
> >had given some thought to upgrading the box to
> >-CURRENT after the commit for the network stack
> >settled.  But I first need to get serial console
> >enabled on it :).
> >
> >Daryl
> >
> >--- Ganbold <[EMAIL PROTECTED]> wrote:
> > > Hi Daryl,
> > >
> > > I have exactly same problem as you. I have FreeBSD
> > > 5.2-CURRENT (did cvsup
> > > on March 23) with mysql-4.0.18 from ports
> > > collection.
> > > It is compiled with linuxthreads. Mysql is working
> > > fine, except it
> > > sometimes freezes, sometimes one or two tables get
> > > corrupted.
> > > Usually freezes once per day. Just freezes and I had
> > > to kill mysql process
> > > and start.
> > >
> > > Did you solve your problem? I also downloaded latest
> > > snapshot from
> > > mysql.com web site and installed, but nothing
> > > changes.
> > > Let me know if you find something.
> > >
> > > TIA,
> > >
> > > Ganbold
> > >
> > >
> > > At 12:48 PM 03.04.2004, you wrote:
> > > >ah! thanks :)
> > > >
> > > >It just happened again and I was able to collect
> > > the
> > > >data again.
> > > >
> > > >Before I did mysqladmin shutdown/killall -11 mysqld
> > > >http://sql.tribalwar.com/before-ps.txt
> > > >
> > > >a few times the 3 giant processes would show as -,
> > > but
> > > >then it got to the point where they weren't
> > > changing
> > > >one bit (staying at Giant)
> > > >
> > > >after I did mysqladmin shutdown/killall -11 mysqld
> > > >http://sql.tribalwar.com/after-ps.txt
> > > >
> 

Re: Problems building readline on Solaris 7

[Ken Menzel]

Hi Erek,
   This seems familiar to me,   are use still using Solaris make?  If
yes,  try using GNU (gmake) it looks like a make problem. Hope this
helps,

Ken
- Original Message - 
From: "Erek Dyskant" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, April 12, 2004 8:44 AM
Subject: Problems building readline on Solaris 7


> Hi-
> I have not been able to build readline on Solaris 7.  No matter
> what I change in the configuration, it still gives me the same
error.
> Any ideas would be greatly appreciated.
> Thank you all in advance for the help.
> Regards,
> Erek
>
>
> System Configuration:
> gcc: 3.3.2
> gnu make: 3.80
> sparc-sun-solaris2.7
>
> ./configure --prefix=/usr/local/mysql
> --localstatedir=/usr/local/mysql/data --libexecdir=/usr/local/mysql/
bin
> --with-extra-charsets=complex  --enable-thread-safe-client --enable-
local-infile
> --disable-shared --with-innodb
>
> Here's the error:
>
> if
gcc -DUNDEF_THREADS_HACK -DHAVE_CONFIG_H -DNO_KILL_INTR -I. -I. -I..
> -I./../include -I./.. -I..-O3 -DDBUG_OFF -O3 -fno-omit-frame-poi
nter
> -DHAVE_CURSES_H -I/opt/home2/erek/src/mysql-4.0.18/include
> -DHAVE_RWLOCK_T -MT tilde.o -MD -MP -MF ".deps/tilde.Tpo" \
>   -c -o tilde.o `test -f 'tilde.c' || echo './'`tilde.c; \
> then mv -f ".deps/tilde.Tpo" ".deps/tilde.Po"; \
> else rm -f ".deps/tilde.Tpo"; exit 1; \
> fi
> rm -f libreadline.a
> false cru libreadline.a readline.o funmap.o keymaps.o vi_mode.o
parens.o
> rltty.o complete.o bind.o isearch.o display.o signals.o util.o
kill.o
> undo.o macro.o input.o callback.o terminal.o xmalloc.o history.o
> histsearch.o histexpand.o histfile.o nls.o search.o shell.o tilde.o
> make[2]: *** [libreadline.a] Error 1
> make[2]: Leaving directory
`/opt/home2/erek/src/mysql-4.0.18/readline'
> make[1]: *** [all-recursive] Error 1
> make[1]: Leaving directory `/opt/home2/erek/src/mysql-4.0.18'
> make: *** [all] Error 2
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>


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

Re: Perl Modelues

[Ken Menzel]

Hi Kirti,
   mysql_config tell the perl build module how to link with the
driver.  The easiest way to fix this is make sure the bin driectory of
MySQL is in your path.  If you can type 'mysql_config'  at the command
line and get an answer like:
Usage: /usr/local/bin/mysql_config [OPTIONS]
Options:  etc

The perl DBD module should be able to find and use this program to
configure itself.

If you get a command not found error add the mysql bin driectory to
the path.

Ken

- Original Message - 
From: "Kirti S. Bajwa" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, April 08, 2004 1:53 PM
Subject: RE: Perl Modelues


>
> First a big thanks to Mark, BAO, Beacker, Matthais, Rich & Ken for
their
> response.
>
> My problem was that I thought that I needed to download "tar" file
to start
> the installation. Rich's response somewhat cleared that. I ran the
following
> commands, and got the following errors:
>
> % perl -MCPAN -e shell
> cpan> install Data::Dumper
> cpan> install DBI
> (no problem upto this point)
> cpan> install DBD::mysql
> Errors returned:
>
> Can't exec "mysql_config": No such file or directory at Makefile.PL
line 174
> readline() on closed filehandle PIPE at Makefile.PL line 176
> ...  (there are 10 such errors)
>
> Then few other lines of output without error. Then I get the
following
> (error) response:
>
> Checking if your kit is complete...
> looks good
> Unsuccessful stat on filename containing newline at
> /usr/lib/perl5/5.8.0/ExtUtils/Liblist/Kid.pm line 97.
> ... (number of such errors)
> Note (probabally harmless): No library found for -lmysqlclient
> Unsuccessful stat on filename containing newline at
> /usr/lib/perl5/5.8.0/ExtUtils/Liblist/Kid.pm line 97.
> ... (again quite a number of such errors)
> Note (probabally harmless): No library found for -lgz
> Writing Makefile for DBD::mysql
> Makefile:89: *** missing separator.  Stop.
>   /usr/bin/make  --NOT OK
> Running make test
>   Can't test without successful make
> Running make install
>   make had returned bad status, install seems impossible
> cpan>
>
>
>
> What are these errors?
> How should I fix them?
> Can I continue installing other software (Apache, qmail, etc.)
>
> Thanks.
>
> Kirti
>
>
>
> -Original Message-
> From: Rich Allen [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, April 07, 2004 6:02 PM
> To: Kirti S. Bajwa
> Cc: '[EMAIL PROTECTED]'
> Subject: Re: Perl Modelues
>
>
> you can install Perl modules with
>
> $ perl -MCPAN -e shell
> cpan> install [module::name]
>
> - hcir
>
> mysql
>
> - hcir
> On Apr 7, 2004, at 10:57 AM, Kirti S. Bajwa wrote:
>
> > Hello List:
> >
> > I have run into stone wall in figuring out installation of Perl
DBI
> > modules
> > with MySQL. When I review the MySQL documentation, 2.7.1
Installing
> > Perl on
> > Unix,
> > it mentions that the "easiest" way to install Perl DBI is to use
CPAN.
> > However when I go to the link provided in the documentation
> > , I can't figure out how to find the Perl
> > module.
> > Can someone show me the light.
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>


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

Re: Perl Modelues

[Ken Menzel]

Just type in the empty search box at the top of the screen:
DBI returns:
http://search.cpan.org/~timb/DBI-1.42/DBI.pm

mysql returns:

http://search.cpan.org/~rudy/DBD-mysql-2.9003/

Hope that helps
Ken
- Original Message - 
From: "Kirti S. Bajwa" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, April 07, 2004 2:57 PM
Subject: Perl Modelues


> Hello List:
>
> I have run into stone wall in figuring out installation of Perl DBI
modules
> with MySQL. When I review the MySQL documentation, 2.7.1 Installing
Perl on
> Unix,
> it mentions that the "easiest" way to install Perl DBI is to use
CPAN.
> However when I go to the link provided in the documentation
> , I can't figure out how to find the Perl
module.
> Can someone show me the light.
> Kirt
> TIB
> Mountain City, TN 37683
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>


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

Re: Could some one help !

[Ken Menzel]

Did you run it from the directory from which you install mysql?  EG:
 Installed mysql at /usr/local/mysql-max-4.0.18-sun-solaris-etc

cd /usr/local/mysql-max-4.0.18-sun-whatever-the-rest-is

./bin/mysql_install_db --user=mysql

a.. If you installed MySQL using a binary distribution, use this
command:
./scripts/mysql_install_db --user=mysql

RTFM Step 2

http://www.mysql.com/doc/en/Unix_post-installation.html

Hope this helps,
Ken

- Original Message - 
From: "Ravi T" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, April 07, 2004 4:50 PM
Subject: Could some one help !


> Hi,
>
> I am trying to install MYSQL on a Sun Solaris box and here are the
details and the error.
>
> Here is the version of MYSQL I am trying to install:
>
> mysql-max-4.0.17-unknown-freebsd5.1-sparc64
>
>
> #uname -a
>
> SunOS kittyhawk 5.8 Generic_108528-23 sun4u sparc SUNW,Ultra-1
>
> # ./mysql_install_db --user=mysql
>
> ERROR =
>
> Didn't find ./bin/mysqld
> You should do a 'make install' before executing this script
>
> I have looked and spent many hours trying to figure this out but
having no luck what so ever.
>
> Could someone tell me what I am doing wrong or how to start
troubleshooting this effectively.
>
> Thanks.
>
> Ravi T.
>
>
>
> -
> Do you Yahoo!?
> Yahoo! Small Business $15K Web Design Giveaway - Enter today


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

Re: aide urgente site d'une association que nous n'arrivons plus a reinstaller

[Ken Menzel]

Bonjour Samana,
I will write in english for the list and then french for Samana.
His problem is that he is not supplying a valid user and password.

Je vais écrire en anglais donc français.

You need to create a new user with permissions to access the database.
I don't recommend that you use the user "nobody".  But I don't know if
that is forced on use by "SPIP".

On doit générer un usager avec accès à le BD.  Je ne conseil pas qu'on
emploie l'usager "nobody" mais je ne sais pais si "SPIP" impose cette
restriction.

Quelquechose comme:
GRANT ALL ON *.* TO 'nobody'@'localhost'

I recommend that you use a user with a password, but you need to
modify the web site connection string with the user name and password.

Je te conseil d'employer un usager avec un mot de passe mais il faut
modifier le logiciel de site de web avec le  usager et mot de passe
correspondants.

GRANT ALL ON nom-de-BD.* TO 'usager'@'localhost' identified by
'mot-de-passe'

vois:
http://www.mysql.com/doc/fr/GRANT.html

espère que ça t'aide,
Amicalement,

Ken Menzel

- Original Message - 
From: "samana" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, March 24, 2004 7:57 PM
Subject: aide urgente site d'une association que nous n'arrivons plus
a reinstaller


> J'ai réinstaller le site web de notre association qui a été réalisé
avec
> SPIP 1.6 sur un serveur dédié . J'ai du changer l'emplacement et le
nom
> de la base de donnée et je n'arrive plus a rentrer dans le site. Si
je
> vai a l'adresse suivante : http://www.ligue-echo.org/ecrire/
> Je reçois le message d'erreur suivant :
> Attention : un problème technique (serveur MySQL) empêche l'accès à
> cette partie du site. Merci de votre compréhension.
> 1045 Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)
>
> J'ai changer le chemin de la base dans le fichier
> ECRIRE/inc_connect.php3 mais rien n'a changer?
> Que dois-je faire?
> Merci pour votre aide et cordiales salutations
> Luc
> e-mail : [EMAIL PROTECTED]
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>


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

Re: problem with sql as a service

[Ken Menzel]

Hi Charles!
   Great,  a little extra info is needed.  Is it windows XP or 2000?
My guess would be not the server version?

What do you mean running as a server?  Did you tpe something like
cd  c:\mysql
mysqld --install   or   mysqld --install-manual  ??

Also what version of  mysql?!  The execution path should be correct
and set when you installed the service!


Thanks Ken

- Original Message - 
From: "Charles Fletcher" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Saturday, March 13, 2004 2:18 PM
Subject: problem with sql as a service


i put mysql on my pc, after unzipping the files in a folder and
running it as a service, i went to my administration tools and to
services to turn it on manuely and te exacution path is wrong, how do
i change this, i tryed reinstalling it but it will not change the path
in services, is there a way to delete the service or change the
path... charles


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

Re: Enabling Logging

[Ken Menzel]

Hi JJ,
   Not really a MySQL question but more a UNIX/Linux question.

  If mysqldump encounters an error it will like all well behaved
programs write an error message to stderr.  You can also from your
script check the error return from the command and perform additional
actions.  I would guess that this will be a repeating operation so I
would suggest you use 'cron'  see 'man cron' at the unix prompt.
Creating an entry in your cron that runs a scripts will mail you
nothing if there is no output from the progam IE it worked or you can
add a success message and you will get a mail every time.  Either way
if there  is an error the text of the error will get mailed to you.

Hope this helps,
ken

- Original Message - 
From: "Jai Jones" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Saturday, March 13, 2004 3:22 AM
Subject: Enabling Logging


> Hi,
>
> I am a little confused on how to get logging working, perhaps some
advanced
> users can help :)
>
> Here's what I'm trying to do, I am backing up my MySQL data using a
shell
> script and mysqldump. I would like to be able to send an email
notification
> to myself to know that the dump worked.
>
> I was thinking of tac'ing or tail'ing the last few entries of a
mysql (or
> mysqldump??) log and pipping it to an email. Does this sound like a
good
> idea?
>
> It appears my server is not logging by default, how do I enable a
log that
> would store this info, or am I looking at this the wrong way.
>
> Thanks :)
> JJ
>
> _
> MSN Messenger: instale grátis e converse com seus amigos.
> http://messenger.msn.com.br
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>


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

Re: link error gcc compiling mysql on solaris 9 (lib not found)

[Ken Menzel]

OK,  sorry to answer my own post,  libgcc was is /usr/local/lib  which
did not get properl added with crle!

crle -u -l /usr/local/lib

Problem gone!  Sorry to waste bandwidth (we are just learning
solaris!).
Ken
- Original Message - 
From: "Ken Menzel" <[EMAIL PROTECTED]>
To: "Sasha Pachev" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Friday, March 12, 2004 10:33 AM
Subject: Re: link error gcc compiling mysql on solaris 9 (lib not
found)


>
> >
> > That's a mess of a problem. If the libraries were included in the
> link,
> > configure must have detected them on your system somehow, but now
> they are not
> > working. Did you by any chance
use --with-mysqld-ldflags=-all-static
> ?
> >
> Yes I did, now I realize this doesn't work on Solaris.  I tried
> linking statically because of the error on the mysql-test script
from
> the mysql client.  So now I am back to a dynamic compile, which
> completes just fine and the mysqld starts OK! But now I get an error
> from the command line mysql:
> hill> pwd
> /export/home/ken/mysql/mysql-4.0.18/client
> hill> ./mysql
> ld.so.1: /export/home/ken/mysql/mysql-4.0.18/client/.libs/lt-mysql:
> fatal: libgcc_s.so.1: open failed: No such file or directory
> Killed
> hill>
>
> But the mysqld daemon seems to star just fine for testing!
>
> Thanks for the hints,
> Ken
>
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>


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

Re: two masters, one slave

[Ken Menzel]

Not at this time,  however you could have two different slaves on the
same physical server using mysql_mutli type of configuration.
http://www.mysql.com/doc/en/mysqld_multi.html

Hope it helps
Ken
- Original Message - 
From: "Ari Davidow" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, March 12, 2004 1:30 PM
Subject: two masters, one slave


> Is it possible to have two masters and one slave? I'm trying to
replicate
> two master databases so that I have a failover.
>
> The alternate, I guess, would be to have two instances of mysql
listing on
> different ports on the slave server platform?
>
> ari
>
> Ari Davidow
> [EMAIL PROTECTED]
> http://www.ivritype.com/
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>


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

Re: mysql-4.0.18 build problem in FreeBSD-5.2-CURRENT

[Ken Menzel]

Hi Ganbold,
   I think you are asking in the wrong place.  I would suggest
[EMAIL PROTECTED] or [EMAIL PROTECTED] or [EMAIL PROTECTED] see .
But my personal thoughts on this is if you are not ready for these
types of issues don't track freebsd-current.  This is bleeding edge
stuff.  FreeBSD-stable or FreeBSD 4.9 for production or even 5.2.  I
know there have been massive threads changes on -current.

see also
http://www.freebsd.org/doc/en_US.ISO8859-1/books/handbook/eresources.html#ERESOURCES-MAIL

Hope that helps,
Ken
- Original Message - 
From: "Ganbold" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, March 11, 2004 10:47 PM
Subject: mysql-4.0.18 build problem in FreeBSD-5.2-CURRENT


> Hi,
>
> Today I wanted to upgrade mysql-4.0.17 to mysql-4.0.18 in
> FreeBSD-5.2-CURRENT and got error below.
> I used following options to compile from ports collection:
>
> make WITH_CHARSET=cp1251 WITH_LINUXTHREADS=yes BUILD_STATIC=yes
install
>
> Error message:
> 
--
---
> /usr/lib/libc.a(res_init.o): In function `__h_error':
> res_init.o(.text+0x1104): multiple definition of `__h_error'
>
/usr/local/lib/liblthread.a(errno.o):/usr/ports/devel/linuxthreads/wor
k/linuxthreads-2.2.3_14/errno.c:29:
> first defined here
> /usr/bin/ld: Warning: size of symbol `__h_error' changed from 88 to
36 in
> /usr/lib/libc.a(res_init.o)
> *** Error code 1
>
> Stop in /usr/ports/databases/mysql40-server/work/mysql-4.0.18/sql.
> *** Error code 1
>
> Stop in /usr/ports/databases/mysql40-server/work/mysql-4.0.18/sql.
> *** Error code 1
>
> Stop in /usr/ports/databases/mysql40-server/work/mysql-4.0.18/sql.
> *** Error code 1
>
> Stop in /usr/ports/databases/mysql40-server/work/mysql-4.0.18.
> *** Error code 1
>
> Stop in /usr/ports/databases/mysql40-server/work/mysql-4.0.18.
> *** Error code 1
>
> Stop in /usr/ports/databases/mysql40-server.
> 
---
>
> What should do in this case? Is there anybody solved this problem
before?
>
> tia,
>
> Ganbold
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>


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

Re: link error gcc compiling mysql on solaris 9 (lib not found)

[Ken Menzel]

>
> That's a mess of a problem. If the libraries were included in the
link,
> configure must have detected them on your system somehow, but now
they are not
> working. Did you by any chance use --with-mysqld-ldflags=-all-static
?
>
Yes I did, now I realize this doesn't work on Solaris.  I tried
linking statically because of the error on the mysql-test script from
the mysql client.  So now I am back to a dynamic compile, which
completes just fine and the mysqld starts OK! But now I get an error
from the command line mysql:
hill> pwd
/export/home/ken/mysql/mysql-4.0.18/client
hill> ./mysql
ld.so.1: /export/home/ken/mysql/mysql-4.0.18/client/.libs/lt-mysql:
fatal: libgcc_s.so.1: open failed: No such file or directory
Killed
hill>

But the mysqld daemon seems to star just fine for testing!

Thanks for the hints,
Ken




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

link error gcc compiling mysql on solaris 9 (lib not found)

[Ken Menzel]

This may be more solaris related,  but I need to compile mysql with
gcc because perl is compiled with gcc and I need DBD::mysql.  It seems
the linker can't find the libs.  I seem to have the same problem
linked dynamicaly expect the build completes and then can't load the
shared library when I start mysql.

I hope I have include all relevant info, hope some one can point me
the right way!

Thanks Ken

../dbug/libdbug.a ../regex/libregex.a
../strings/libmystrings.a -ldl -lz -lposix4 -lcrypt -lgen -lsocket -ln
sl -lm -lpthread -lthread
ld: fatal: library -lrt: not found
ld: fatal: library -ldl: not found
ld: fatal: library -lposix4: not found
ld: fatal: library -lpthread: not found
ld: fatal: library -lthread: not found
ld: fatal: File processing errors. No output written to mysqld
collect2: ld returned 1 exit status
gmake[4]: *** [mysqld] Error 1
gmake[3]: *** [all-recursive] Error 1
gmake[2]: *** [all] Error 2
gmake[1]: *** [all-recursive] Error 1
gmake: *** [all] Error 2
---
additional info
hill> crle

Configuration file [3]: /var/ld/ld.config
  Default Library Path (ELF):   /usr/lib:/usr/local/mysql/lib/mysql
  Trusted Directories (ELF):/usr/lib/secure  (system default)

Command line:
  crle -c /var/ld/ld.config -l /usr/lib:/usr/local/mysql/lib/mysql

hill>CC=gcc  CFLAGS="-O3 " CXX=gcc  \
CXXFLAGS="-O3 -felide-constructors -fno-exceptions -fno-rtti" \
./configure $DEBUG --enable-large-files --with-innodb \
$LOWMEM --enable-assembler --with-mysqld-ldflags=-all-static

hill>gmake -s

hill> gcc -v
Reading specs from
/usr/local/lib/gcc-lib/sparc-sun-solaris2.9/3.3.2/specs
Configured with:
../configure --with-as=/usr/ccs/bin/as --with-ld=/usr/ccs/bin/ld --dis
able-nls
Thread model: posix
gcc version 3.3.2


------------
Ken Menzel  ICQ# 9325188
www.icarz.com  [EMAIL PROTECTED]


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

Re: InnoDB tables using 90% cpu

[Ken Menzel]

Hi Cliff,
   Either way for a production system I recommend using Linuxthreads
with FreeBSD4 (also works on 5 but threads are much improved on 5).
Please use the ports and make WITH_LINUXTHREADS=yes and others that
use (see make pre-fetch in
/usr/ports/databases/mysql-favorite-version)
or
http://jeremy.zawodny.com/blog/archives/000458.html  for Jeremey's
building hints.  I would also examine your innodb configuration and
buffer sizes and isolation level.  These are separate from myisam.

Best of luck,
Ken

- Original Message - 
From: "Cliff" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, March 09, 2004 4:56 PM
Subject: Re: InnoDB tables using 90% cpu


> The query is running dramatically slower than the MyISAM query,
sometimes
> even causing mysql to freeze for a while. I searched this list and
found a
> few people saying that on FreeBSD mysql should be compiled using
linux
> pthreads if you are using InnoDB or else I would get this exact
problem. Has
> this been resolved or is should I recompile? I am using native
freebsd
> threads.
>
>
> - Original Message - 
> From: "Sasha Pachev" <[EMAIL PROTECTED]>
> To: "Cliff" <[EMAIL PROTECTED]>
> Cc: <[EMAIL PROTECTED]>
> Sent: Monday, March 08, 2004 9:18 AM
> Subject: Re: InnoDB tables using 90% cpu
>
>
> > Cliff wrote:
> > > Hi, I have a whole database I wanted to convert to InnoDB from
MyISAM,
> but
> > > do not want to use alter table because of the problems I had
last time.
> I
> > > made a whole dump of the table using mysqldump and changed all
of the
> table
> > > create definitions from MyISAM to InnoDB. Theoretically this
should be
> just
> > > like creating a new innodb table from scratch and inserting new
records.
> > > However, while the MyISAM tables used ~30% of the cpu usage on a
query,
> > > InnoDB runs anywhere from 50-90% depending on the query. The
databases
> > > combined are approximately 200MB. Here is my cnf file:
> > >
> > > [mysqld]
> > > basedir=/mysql
> > > long_query_time=3
> > > log-slow-queries=/tmp/slowmysql.log
> > > innodb_data_home_dir =
> > > innodb_data_file_path = /mysql/data/innodb_data:300M:autoextend
> > > set-variable = innodb_buffer_pool_size=300M
> > > set-variable = innodb_additional_mem_pool_size=20M
> > > set-variable = innodb_log_file_size=150M
> > > set-variable = innodb_log_buffer_size=8M
> > > innodb_flush_log_at_trx_commit=0
> > >
> > > This is mysql 4.0.18 on freebsd 4.8-STABLE. We have 1GB of ram
which
> should
> > > be plenty to run the large queries that we are doing. Thanks in
advance.
> >
> > 50-90% CPU vs only 30% could be actually an improvement ( less
disk I/O,
> and
> > relatively more time to get the data). The question is - does the
query
> actually
> > take less time? If not, it could be because a certain optimization
> available
> > with MyISAM is not available with InnoDB. Isolate the trouble
query, and
> do an
> > EXPLAIN.
> >
> > -- 
> > Sasha Pachev
> > Create online surveys at http://www.surveyz.com/
> >
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>


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

Re: lire des fichiers windev5

[Ken Menzel]

Bonjour Pierre,
   Non mysql ne support pas un format hyperfile.  C'est un format
propriétaire. S'il il est possible d'exporter les donnees en format
ASCII ça pouvait marcher avec un  ou load data commande.
Voici le doc en français:
http://www.mysql.com/doc/fr/mysqlimport.html

En plus s'il est possible ce liste est en anglais.  Je crois que le
list mysql en français est mort.

Amicalement,
Ken


- Original Message - 
From: "sa cefrais" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, March 05, 2004 12:20 PM
Subject: lire des fichiers windev5


> Bonjour,
>
> La possibilité de lire directement les fichiers windev5 au format
hyperfile
> pour exploiter les données ou les migrer sous mysql existe-t-elle?
>
> Merci.
> Pierre Marcotte
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>


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

Re: MySQL Backup advice

[Ken Menzel]

Hi Andre,
   I think you are looking for this:
http://www.mysql.com/doc/en/Binary_log.html

Hope it helps,
Ken
- Original Message - 
From: "Andre MATOS" <[EMAIL PROTECTED]>
To: "Ware Adams" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, March 04, 2004 12:09 PM
Subject: Re: MySQL Backup advice


> Hi Ware Adams,
>
> thanks for your answer. Here's another question:
>
> Is it possible to set MySQL to save all the changes that can happen,
for
> example, if some one insert a new record into table X, MySQL save a
log
> about this task performed and all data that was inserted. I another
person
> update one field from table Y, MySQL save all the information about
this
> change. So, If something happens after the backup, we can recover
the
> database without go back and perform again the insert and the
update?
>
> If "yes", where can I find documentation about this?
>
> Thansk a lot.
>
> Andre
>


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

Re: Can't create a new thread (errno 11)

[Ken Menzel]

Hi Christof,
   You don't mention the OS version you are using (So I will assume
Linux) is there a sysctl for the max number of threads on your OS?
Ken
- Original Message - 
From: "Christof Egner" <[EMAIL PROTECTED]>
To: "James Kelty" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Saturday, February 21, 2004 5:18 AM
Subject: Re: Can't create a new thread (errno 11)


> Hello James,
>
> thanks for your answer, but max_connections is set high enough
(double
> the number of the currently used threads). I think the error would
be
> "Too many connections", wouldn't it?
>
> -Christof
>
> James Kelty wrote:
> > Check the max_connections variable
> >
> >
> > Mysql> show variables liks '%connection%';
> >
> > -James
> >
> > -Original Message-
> > From: Christof Egner [mailto:[EMAIL PROTECTED]
> > Sent: Friday, February 20, 2004 9:03 AM
> > To: [EMAIL PROTECTED]
> > Subject: Can't create a new thread (errno 11)
> >
> > Hello,
> >
> > I'm having problems with a 4.0.18 MySQL Server. Since this
afternoon I
> > get the error "Can't create a new thread (errno 11)" when MySQL
has
> > forked about 200-300 threads.
> >
> > What does it mean? I'm not running out of RAM or disk space.
> >
> > Best Regards,
> > Christof
> >
> >
> >
>
>
> -- 
> ___
> Configo Systems GmbH - Jahnstr. 62 - 64285 Darmstadt - DE
> Tel +49.6151.39656.0 Fax .49 http://www.configo.de
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>


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

Re: my.cnf in MySQL-Server-4.0.17

[Ken Menzel]

Hi Ronan,
   Did you perhaps set MAXDSIZ is your kernel configuration above
"(2047UL*1024*1024)"  ?

Ken
- Original Message - 
From: "Ronan Lucio" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, February 19, 2004 3:06 PM
Subject: my.cnf in MySQL-Server-4.0.17


> Hi All,
>
> I´m trying to do some customization in a MySQL-Server-4.0.17
> to gain a better performance.
>
> We have a Intel P4-2.4 Ghz with 1 Gb RAM and 40 Gb HD
> on a FreeBSD-5.2.1 box dedicated for MySQL.
>
> My trouble is when I create the my.cnf file and start MySQL.
> MySQL don´t stat.
>
> the /usr/local/mysql/aguia.err file shows:
> -
> 040219 16:34:46  mysqld started
> mysqld in malloc(): error: allocation failed
> mysqld got signal 6;
> This could be because you hit a bug. It is also possible that this
binary
> or one of the libraries it was linked against is corrupt, improperly
built,
> or misconfigured. This error can also be caused by malfunctioning
hardware.
> We will try our best to scrape up some info that will hopefully help
> diagnose
> the problem, but since we have already crashed, something is
definitely
> wrong
> and this may fail.
>
> key_buffer_size=134217728
> read_buffer_size=1044480
> max_used_connections=0
> max_connections=100
> threads_connected=0
> It is possible that mysqld could use up to
> key_buffer_size + (read_buffer_size +
sort_buffer_size)*max_connections =
> 335471 K
> bytes of memory
> Hope that's ok; if not, decrease some variables in the equation.
>
> mysqld in free(): error: recursive call
> Fatal signal 6 while backtracing
> 040219 16:34:46  mysqld ended
> -
>
> My my.cnf file is:
> -
> [client]
> port= 3306
> socket  = /tmp/mysql.sock
>
> [mysqld]
> port= 3306
> socket  = /tmp/mysql.sock
> skip-locking
> key_buffer = 128M
> max_allowed_packet = 1M
> table_cache = 512
> sort_buffer_size = 1M
> read_buffer_size = 1M
> myisam_sort_buffer_size = 64M
> thread_cache = 8
> query_cache_size = 32M
> thread_concurrency = 8
>
> log-bin
>
> server-id   = 1
>
> innodb_data_home_dir = /usr/local/mysql/
> innodb_data_file_path = ibdata1:1000M:autoextend
> innodb_log_group_home_dir = /usr/local/mysql/
> innodb_log_arch_dir = /usr/local/mysql/
> innodb_buffer_pool_size = 512M
> innodb_additional_mem_pool_size = 20M
> innodb_log_file_size = 128M
> innodb_log_buffer_size = 8M
> innodb_flush_log_at_trx_commit = 1
> innodb_lock_wait_timeout = 50
>
> [mysqldump]
> quick
> max_allowed_packet = 16M
>
> [mysql]
> no-auto-rehash
>
> [isamchk]
> key_buffer = 256M
> sort_buffer_size = 256M
> read_buffer = 2M
> write_buffer = 2M
>
> [myisamchk]
> key_buffer = 256M
> sort_buffer_size = 256M
> read_buffer = 2M
> write_buffer = 2M
>
> [mysqlhotcopy]
> interactive-timeout
> -
>
> Without the /etc/my.cnf file the MySQL works fine, except in
> some moments that it get slow.
>
> Does anyone knows what could be wrong?
> Any help would be Appreciated.
>
> Thank´s
> Ronan
>
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>


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

Re: Repeated 100% CPU problem in FreeBSD

[Ken Menzel]

Hi Arnaud,
   I did not see an answer to this so; I think the info you need is
here http://jeremy.zawodny.com/blog/archives/000203.html and here
http://jeremy.zawodny.com/blog/archives/000697.html

But I think you want "skip-name-resolve" to be specified in my.cnf.

Ken

I have included the relevant section from Jeremeys blog:
___
1. Non-thread safe DNS Lookups

Certain operations are not thread-safe on FreeBSD. A fine example of
that is gethostbyname(), which MySQL calls to convert host names in to
IP addresses. Usually this happens for each new connection to the
server and whenever MySQL needs to contact another machine--typically
a replication slave connecting to its master.

Based on our testing, the only truly safe way to operate is to use
the --skip-name-resolve flag for starting mysqld AND specifying the IP
address of the master instead of the hostname. That virtually
eliminates the need for MySQL to call gethostbyname().

The symptom of this problem is that the mysqld will consume all the
available CPU time even when there are few (if any) queries running.
You can try and kill -6 the mysqld process and then run it thru gdb to
get a backtrace. You'll likely see something like this:

#0  0x829c94c in _thread_kern_sched_state_unlock () at
./cp/tinfo2.cc:300
#1  0x829c0e0 in _thread_kern_sched () at ./cp/tinfo2.cc:300
#2  0x829c787 in _thread_kern_sched_state () at ./cp/tinfo2.cc:300
#3  0x82c5fdc in kevent () at ./cp/tinfo2.cc:300
#4  0x82c5a4f in res_send () at ./cp/tinfo2.cc:300
#5  0x82a4308 in res_query () at ./cp/tinfo2.cc:300
#6  0x82a4737 in res_querydomain () at ./cp/tinfo2.cc:300
#7  0x82a44bb in res_search () at ./cp/tinfo2.cc:300
#8  0x82a9a00 in _gethostbydnsname () at ./cp/tinfo2.cc:300
#9  0x82a8739 in gethostbyname2 () at ./cp/tinfo2.cc:300
#10 0x82a86d4 in gethostbyname () at ./cp/tinfo2.cc:300
#11 0x8275fc4 in my_gethostbyname_r (
name=0x1b5f79a8 "your_hostanme", result=0x9fa659b8,
buffer=0x9fa651b8 "\032", buflen=2048, h_errnop=0x9fa651b0)
at my_gethostbyname.c:108
#12 0x80d6fbd in mc_mysql_connect ()
#13 0x80d6b37 in mc_mysql_reconnect ()
#14 0x80d4506 in safe_reconnect ()
#15 0x80d3fb8 in handle_slave ()
#16 0x828ffa5 in _thread_start () at ./cp/tinfo2.cc:300
#17 0x0 in ?? ()
If you see that, get rid of DNS lookups.




- Original Message - 
From: "Arnaud Pignard" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, January 16, 2004 7:06 PM
Subject: Re: Repeated 100% CPU problem in FreeBSD


> Hi !
>
> Anyone find a solution for this problem ?
>
> We have 2 mysql server with same problem. Happen like every 1-2
months.
>
> Regards,
>
>
> -- 
> Arnaud Pignard ([EMAIL PROTECTED])
> Frontier Online - Opérateur Internet
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>
>


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

Re: One Slave Many Master

[Ken Menzel]

Hi Leo,  a quick search for "multiple daemons" at www.mysql.com/doc
reveals:
http://www.mysql.com/doc/en/Multiple_servers.html

This should give you all your answers and make it possible to do what
you want.
Hope this helps,
Ken
- Original Message - 
From: "Leo" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, December 29, 2003 2:28 AM
Subject: One Slave Many Master


I know someone already ask this,
and the answer generally 'NO YOU CANT' :b

but, is there any work around so i can make a backup server (slave),
from many other server (master) through replication?

well, it's not just a backup server actually..
it really is the headquarter's db server,
and the branch offices can not directly post their transaction..
because the line is teriblyy sllw :D

so, im thinking of adding extra server on branches
and replicate what ever happens there to the main server

perhaps, if i can run multiple mysql server on different port on the
main server,
with it's own database,
that act as a slave one to one to other server,
and add one more that handle all of the DB ..

Main ServerBranch
-----
DB1, 3306  DB1, 3306, BranchServer1
DB2, 3307  DB2, 3307, BranchServer2
DB3, 3308  DB3, 3308, BranchServer3
DB4, 3309  DB4, 3309, BranchServer4
and so on...

MASTER DB(DB1,DB2...DBn) on port 33xx

is this possible???
or maybe there are other solution.. pleasseee.. i really appreciate it

oh, one more thing, can the replication run both way?
what ever happened on machine A, replicated on machine B and vice
versa..

thanks...

--
Regards
Leonardus Setiabudi
IT Project Coordinator
PT Bina San Prima, www.binasanprima.com



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

Re: How to keep multiple instances of the same information from being displayed

[Ken Menzel]

Perhas you want SELECT DISTINCT ?
http://www.mysql.com/doc/en/SELECT.html

Hope that helps,
Ken

- Original Message - 
From: "Hunter, Jess" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, December 12, 2003 10:47 AM
Subject: How to keep multiple instances of the same information from
being displayed


> Using PHP as the front end I am creating a form with a dropdown box
that
> displays information from one table to be inserted into another
table.
> That's PHP and I have gotten that part down with no issue.
>
> However, in the table a person could be listed multiple times and I
only
> want a user to be listed a single time. for instance in the table I
may have
>
> Jones, Jim
> Jones, Mary
> Jones, Mary
> Jones, Mary
> Jones, Nancy
> Jones, Paul
>
> when I do my SELECT I only want Mary Jones to show up a single time.
>
> Here is the code I am currently using within the Dropdown box:
>
> 
> $Link = mysql_connect($Host, $User, $Password);
> $Query="SELECT * from $TableName ORDER BY userlastname";
> $Result= mysql_db_query ($DBName, $Query, $Link);
>
> while ($Row = mysql_fetch_array ($Result)){
>
>
>
> print ("$Row[userlastname],
$Row[userfirstname]");
> }
> mysql_close ($Link);
> ?>
> 
>
> As you can see I am pulling two fields from one table, then
combining them
> to make a single field in another table. I am doing this to provide
> continuity when I create queries in the future.
>
> Any help with this would be greatly appreciated
>
> Jess
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.550 / Virus Database: 342 - Release Date: 12/9/03
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>


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

Re: MySQL License

[Ken Menzel]

Len,
  You shouldn't post private information to a public mailing list
(never do that!).   Please send these type of questions to
[EMAIL PROTECTED]  But my guess is you should download and install it
if you have not already done so.

Ken
- Original Message - 
From: "Len Buchanan" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, December 09, 2003 2:25 PM
Subject: Re: MySQL License


> Hi,
>
> I recently purchased a licence. I received this email. Do I download
the
> binaries myself or are you sending me a box? If I download it
myself, do I
> need to create an account? If I have one, I've long forgotten it.
>
> Regards,
>
> Len Buchanan
> Datascape Technologies Inc.
>
> At 06:07 PM 12/5/2003 +0100, you wrote:
> >Dear Customer:
> >
> >Included is your MySQL Classic License, ordered from MySQL AB.
> >License number(s): 299376.
> >
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>


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

Re: Inquery from Varian Medical Systems

[Ken Menzel]

Hi Robert,
  I did not see any suggestions to you.  So here is one.  If you want
a guarantee simple brute force dump the entire DB with mysqldump and
recreate of the slave.
For Exmaple
mysqldump --opt mydb | mysql -umyuser -pmypwd -hremotehost myremotedb

You can run this through an ssh tunnel for security or compress and
forward or do it in multiple steps.  But if it completes without
errors all data will be present on the remote system.

Hope this helps,
Ken
- Original Message - 
From: "robertxu" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, November 21, 2003 6:37 PM
Subject: Inquery from Varian Medical Systems


> To Whom It May Concern:
>
> I am Robert and work for Varian Medical Systems. MySQl is the
backend of
> our Intranet, VMSNet.
>
> Our current environment is:
>
> The web server and MySQL reside in one hardware server- US server.
If this
> hardware server fails, we switch the users to our European web
server. But
> the European web server is not bundled with MySQL. So all the MySQL
> applications still are not available until US server is back.  That
is the
> problem.
>
> Someone suggests to set up MySQL master and slave instances to
solves
> this.  But such configuration does not guarantee 100% data
synchronization
> between the master and the slave.
>
> Is there any other alternatives? Please advise. Appreciate your
attention
> and time.
>
>
> Regards,
>
> Robert
>
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>


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

Re: Errors with MySQL

[Ken Menzel]

Hi Rob,
  My guess would be you did not see this in the Fine Manual
http://www.mysql.com/doc/en/Post-installation.html
or this
http://www.mysql.com/doc/en/Starting_server.html

Hope this helps,
Ken
- Original Message - 
From: "Rob Snow" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, November 20, 2003 6:15 AM
Subject: Errors with MySQL


Dear MySQL

Firstly, let me say, I am very new to this.
Secondly, I have been reading Larry Ullman's book PHP and MySQL to get
started.

I have installed PHP fine, and it is working well.
I have followed the instructions for MySQL and it has installed a
folder in my:
usr/local/ folder called mysql-standard-4.0.15-apple-darwin6.4-powerpc
with a shortcut to this folder called mysql.

The book I'm reading states I should do the following to set
permissions:
cd /usr/local/mysql
sudo echo
sudo bin/mysqld_safe &

I do this and I get this result:
Last login: Thu Nov 20 10:44:51 on ttyp1
Welcome to Darwin!
[Rob-Snows-Computer:~] robsnow% cd /usr/local/mysql
[Rob-Snows-Computer:/usr/local/mysql] robsnow% sudo echo
Password:

[Rob-Snows-Computer:/usr/local/mysql] robsnow% sudo bin/mysqld_safe &
[1] 3254
[Rob-Snows-Computer:/usr/local/mysql] robsnow% Starting mysqld daemon
with databases from /usr/local/mysql/data
031120 11:01:23  mysqld ended


[1]Done  sudo bin/mysqld_safe
[Rob-Snows-Computer:/usr/local/mysql] robsnow% bin/mysqladmin -u root
password '•••'
bin/mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (61)'
Check that mysqld is running and that the socket: '/tmp/mysql.sock'
exists!
[Rob-Snows-Computer:/usr/local/mysql] robsnow%

The doesn't have any work arounds to this problem, and I cannot go any
further with the tutorials without mySQL running/working.
I do a test and I get this result in a browser:

Warning :  Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (61) in /Users/robsnow/Sites/mysql_test.php on line
2

Warning :  MySQL Connection Failed: Can't connect to local MySQL
server
through socket '/tmp/mysql.sock' (61)  in
/Users/robsnow/Sites/mysql_test.php on line 2

Larry Ullman's books states it should have the following result, and
he
uses the same installer I did:

Last login: Thu Nov 20 10:44:51 on ttyp1
Welcome to Darwin!
[Rob-Snows-Computer:~] robsnow% cd /usr/local/mysql
[Rob-Snows-Computer:/usr/local/mysql] robsnow% sudo echo
Password:

[Rob-Snows-Computer:/usr/local/mysql] robsnow% sudo bin/mysqld_safe &
[1] 3254

[Rob-Snows-Computer:/usr/local/mysql] robsnow% bin/mysqladmin -u root
password '•••'
[Rob-Snows-Computer:/usr/local/mysql] robsnow%

For some reason my run states that the mysql has ended, should this be
so?
Can you please give me any advise on how to check that mySQL is
running, how to check the version, and any answers to why I'm getting
these errors?
I would be most grateful for your assistance.

Rob Snow

Rob Snow
"Space may be the final frontier
But its made in a Hollywood basement"
RHCP 'Californiacation - Californiacation 1999'

[EMAIL PROTECTED]
http://homepage.mac.com/robsnow


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

Re: InnoDB or OS restriction?

[Ken Menzel]

Hi Alex,
What user is the mysql daemon running as?  Are there any limits on
that user or user class (/etc/login.conf).  Just because you have
raised the kernel limits does not mean they are raised for the user.
Can you login as that user and type "limit" and get something like -
%limit
cputime unlimited
filesizeunlimited
datasize1581056 kbytes
stacksize   524288 kbytes
coredumpsizeunlimited
memoryuse   unlimited
vmemoryuse  unlimited
descriptors 19000
memorylockedunlimited
maxproc 5547
sbsize  unlimited
%

Hope this helps,
Ken
- Original Message - 
From: "Heikki Tuuri" <[EMAIL PROTECTED]>
To: "Varshavchick Alexander" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, October 21, 2003 9:54 AM
Subject: Re: InnoDB or OS restriction?


> Alex,
>
> because 512 MB is not an InnoDB or MySQL restriction, it must be an
OS
> restriction :).
>
> I assume you have not allocated 1.5 GB of MySQL key_buffer.
>
> Best regards,
>
> Heikki
> Innobase Oy
> http://www.innodb.com
> InnoDB - transactions, row level locking, and foreign keys for MySQL
> InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up
> MyISAM tables
>
>
> - Alkuperäinen viesti - 
> Lähettäjä: "Varshavchick Alexander" <[EMAIL PROTECTED]>
> Vastaanottaja: "Heikki Tuuri" <[EMAIL PROTECTED]>
> Kopio: <[EMAIL PROTECTED]>
> Lähetetty: Tuesday, October 21, 2003 2:55 PM
> Aihe: Re: InnoDB or OS restriction?
>
>
> > Hi Heikki,
> >
> > here is a snip from the original posting which you probably have
> > overlooked:
> >
> > > > > The first strange thing is that MAXDSIZ and DFLDSIZ in
kernel config
> are
> > > > > setted to 1536M
> >
> > Regards
> >
> > 
> > Alexander Varshavchick, Metrocom Joint Stock Company
> > Phone: (812)118-3322, 118-3115(fax)
> >
> > On Mon, 20 Oct 2003, Heikki Tuuri wrote:
> >
> > > Date: Mon, 20 Oct 2003 22:05:07 +0300
> > > From: Heikki Tuuri <[EMAIL PROTECTED]>
> > > To: [EMAIL PROTECTED]
> > > Subject: Re: InnoDB or OS restriction?
> > >
> > > Alex,
> > >
> > > in FreeBSD user process memory space is often restricted to 512
MB. You
> have
> > > to reconfigure or recompile the FreeBSD kernel to increase that
limit.
> > >
> > > Best regards,
> > >
> > > Heikki Tuuri
> > > Innobase Oy
> > > http://www.innodb.com
> > > Foreign keys, transactions, and row level locking for MySQL
> > > InnoDB Hot Backup - a hot backup tool for InnoDB: now
> > > also backs up your MyISAM tables
> > >
> > >
> > > - Original Message -
> > > From: "alex" <[EMAIL PROTECTED]>
> > > Newsgroups: mailing.database.myodbc
> > > Sent: Monday, October 20, 2003 1:30 PM
> > > Subject: Re: InnoDB or OS restriction?
> > >
> > >
> > > > Hi again,
> > > >
> > > > as there was not a single answer to my question I can imagine
that no
> one
> > > > encountered the same issue, but anyways, can there be any
hints? First
> of
> > > > all, are there any means of looking at mysql memory allocation
list
> > > > grouped by some major parts - for example,
> > > > innodb main pool - can be retrieved from innodb monitor
> > > > innodb additional pool - can be retrieved from innodb monitor
> > > > myisam main cache - ...?
> > > > myisam sort buffer - ...?
> > > > memory allocated from OS - ...?
> > > >
> > > > The questiion is why mysql is trying to allocate memory via
malloc
> from OS
> > > > while the innodb additional pool is occupied only by 50%? Or
is it
> myisam
> > > > buffer that mysql is trying to extend?
> > > >
> > > > Regards
> > > >
> > > > ---
> > > > Alex
> > > >
> > > > On Tue, 14 Oct 2003, alex wrote:
> > > >
> > > > > Hi people,
> > > > >
> > > > > I have mysql 4.0.7-gamma/linuxthreads running under FreeBSD
4.6.2
> > > (server
> > > > > has 4G phisical memory), and occasionally mysql traps with
the
> message:
> > > > >
> > > > > InnoDB: Fatal error: cannot allocate 1064960 bytes of
> > > > > InnoDB: memory with malloc! Total allocated memory
> > > > > InnoDB: by InnoDB 513951016 bytes. Operating system errno:
12
> > > > > InnoDB: Cannot continue operation!
> > > > >
> > > > > The first strange thing is that MAXDSIZ and DFLDSIZ in
kernel config
> are
> > > > > setted to 1536M, and the second one is that each time it
happens,
> while
> > > > > the number of bytes reported to cannot be allocated is
different,
> total
> > > > > memory allocated by InnoDB is exactly the same - 513951016
bytes.
> Which
> > > > > restrictions are the cause of this - InnoDB's or FreeBSD's?
> > > > >
> > > > > Thanks in advance
> > > > >
> > > > > 
> > > > > Alex
> > > > >
> > > > > --
> > > > > MySQL General Mailing List
> > > > > For list archives: http://lists.mysql.com/mysql
> > > > > To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> > > > >
> > > >
> > > > --
> > > > MySQL General Mailing List
> > > > For list archives: http://lists.mysql.com/mysql
> > > > To unsubscribe:
> > >
http://lists.mysql.com/[EMAIL PROTECTED]
> > > >
> > >
> > >
> > >
> > > --
> > > MySQL General Maili

Re: repair with keycache during ADD INDEX

[Ken Menzel]

Hi Franz,
   Check out
MAX_ROWS = #
and
AVG_ROW_LENGTH = #
options in http://www.mysql.com/doc/en/CREATE_TABLE.html

Set MAX_ROWS to a very very large number
You can set these with ALTER TABLE. See section 6.5.4 ALTER TABLE
Syntax using table options at the end. Use the MAX_ROWS
parameter and set it to some really large value (anything over 2^32
like 1000).  This ALTER statement will cause your indexes to
be rebuilt using long pointers.  You can also create the table this
way using MAX_ROWS option in create statement.

You can verify your changes by running SHOW TABLE STATUS before the
change then again after the change.

Have you done this already?  Ohterwisde your key size will be too
small.

Ken

- Original Message - 
From: "Franz, Fa. PostDirekt MA" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, October 17, 2003 2:34 AM
Subject: repair with keycache during ADD INDEX


> Dear Sirs and Ladies,
>
> I tried to put some keys on a large table (more than 100.000.000 in
13G)
> with the Statement:
>
> ALTER TABLE mytable
> ADD INDEX i1 (COL1(4)),
> ADD INDEX i2 (COL2(4)),
> ADD INDEX i3 (COL3(12)),
> ADD INDEX i4 (COL4(12)),
> ADD INDEX i5 (COL5(5)),
> ADD INDEX i5 (COL6(11));
>
> After about 7 hours, a 'SHOW PROCESSLIST' doesn't say 'copy to
temp-table'
> anymore but 'repair with keycache'.
> Watching the the directory for this database, it seems to happpen at
the
> time when all data from .MYD is copied to
> the temp-table.
> The  'repair with keycache' now lasts about 12 hours and I don' know
when it
> comes to an end.
> Is this a usual thing or did I do something wrong?
> I thought 'repair with keycache' just happens while recovering of a
crashed
> table.
>
> How can I speed up this process ?
> Is innodb on MySQL 4.x faster doing this ?
>
> In order ro increase speed of creating an index, I increased the
> key_buffer-varibale to 1024M,
> which is 60% of the memory on that engine.
> OS is SUSE-LINUX 8.0 (Kernel 2.4.4)
> MySQL is 2.23.52.
> FS is Reiser.
>
> The Table is myisam and looks like this:
>
> Field Type Null Key Default Extra
> COL_A char(2)
> COL1 varchar(4)
> COL2 varchar(4)
> COL6 varchar(11)
> COL_B enum('-1','0','1','2') YES (NULL)
> COL3 varchar(35)
> COL4 varchar(55)
> COL_C varchar(35)
> COL_D varchar(55)
> COL_E varchar(61)
> COL_F varchar(30)
> COL_5 varchar(5)
> COL_G varchar(50)
> COL_H varchar(50)
> COL_I varchar(50)
> COL_J varchar(14)
> COL_K int(11) 0
> COL_O int(11) 0
>
> To make it a bit easier to read, i named all collumns with an index
with a
> number (like COL_1).
> This table will be filled once a week and there will be no further
updates
> or inserts till one wwek later,
> when it will be dropped and rebuild with new data.
> It is the only table in this database and (except mysql's
privilege-tables)
> the only table on this engine at all.
>
> Please Help me
> Klaus
>
>
>
>
>
>
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>


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

Re: deleting rows which table1.row=table2.row

[Ken Menzel]

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

and from that page:
DELETE t1,t2 FROM t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id

or

DELETE FROM t1,t2 USING t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id
In the above case we delete matching rows just from tables t1 and t2.

>From MySQL 4.0, you can specify multiple tables in the DELETE
statement to delete rows from one or more tables depending on a
particular condition in multiple tables. However, you can not use
ORDER BY or LIMIT in a multi-table DELETE.

Hope that helps,

Ken

- Original Message - 
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, October 13, 2003 11:25 AM
Subject: deleting rows which table1.row=table2.row


> how to delete rows which table1.field=table2.field
>
> thanks
>
>
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>


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

Re: connexion avec un pocket pc

[Ken Menzel]

Bonjour Frédéric,
Ce liste est en anglais.  Je crois que tu veux http://www.vbmysql.com .  Mais le 
site est en anglais.  Il y a beaucoup de exemples de VB 6 et VB .net sur ce site.

En plus il existe un liste français à (mais il y a récemment beaucoup de silence) :
Liens utiles :
  Poster un message: [EMAIL PROTECTED]
  S'abonner: [EMAIL PROTECTED]
  Se désabonner :  [EMAIL PROTECTED]
  Ecrire au proprio :   [EMAIL PROTECTED]

URL sur Yahoo :
  http://fr.groups.yahoo.com/group/mysql-france/ 


J'espère que ça t'aide.
Amicalement,
Ken
  - Original Message - 
  From: scr informatiques 
  To: [EMAIL PROTECTED] 
  Sent: Monday, October 13, 2003 11:57 AM
  Subject: connexion avec un pocket pc


bonjour,

nous souhaitons que des utilisateurs munient de pocket pc puissent accéder à 
une base de donnée mysql stocké sur un serveur en nos locaux. Pour cela nous 
souhaitons envoyé une commande à partir de Visual Basic (embedded). Existe-t-il des 
commandes adapter pour cela ?

Merci 


Frédéric NUNGE.


  
   
   
  
IncrediMail - La messagerie électronique a enfin évolué - Cliquer ici

Re: Views in MYSQL

[Ken Menzel]

Hi,
- Original Message - 
From: <[EMAIL PROTECTED]>
To: "Nitin" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
Sent: Monday, October 13, 2003 5:52 AM
Subject: Re: Views in MYSQL



> What about when the script uses persistent connections, these Temp
Tables
> would never go away and each new connection create a new Temp Table.
> Therefore if you are operating a heavy load web site, the chances
are you
> could easily run out of disk space. Temp Tables are not a substitute
for
> Views and were never meant to be.

Temp tables are not views,  however with persistant connections if the
same name for the temporary table is reused (which it should be as
temporary tables are unique to each connection even if they have the
same name) you will never run out of space. Also persistent
connections are not "permanent" connections and they will recycle
according to the child rules for apache.

> The people at MySQL are more concerned
> about speed than they are about standards compilence. Therefore, any
feature
> which protentially has an affect on performance will not get
implemented. A
> perfect example of this is Referential Integrity

Mysql has referential integrity see:
http://www.mysql.com/doc/en/ANSI_diff_Foreign_Keys.html
and www.innodb.com or www.sleepycat.com (BDB table types).

MySQL AB is concerned about implementing features in a manner that
does not adversely affect the users that don't need or want that
feature.  They do not force everyone to take a performance hit so a
new ferature can be implemented quickly.  This works well for most
MySQL users.  If it does not they usually migrate to a more fully
featured DBMS such as DB2, Oracle or MSSQL.

Regards,
Ken

>
> Regards
> Minky
>


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

Re: "No more room in index file"

[Ken Menzel]

Hi Jacco,
Perhaps the manual needs to be enhanced,  but it is only a guide,
perhaps the language should be softened or 'error 136' modified that
it could be either problem. Either way I hope this fixes you problem,
my guess would be you have very large rows of data, which is why you
did not hit the error 135 first.  I hope this takes care of it for
you,  we have created very large tables (150 million rows) and had
good success,  as have others.  If this does not fix it we may have
dig a little deeper.  Let us know.

Best of luck,
Ken

- Original Message - 
From: "Jacco van Schaik" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, October 06, 2003 12:00 PM
Subject: Re: "No more room in index file"


> So then Ken Menzel says...
> > Check out
> > MAX_ROWS = #
> > and
> > AVG_ROW_LENGTH = #
> > options in http://www.mysql.com/doc/en/CREATE_TABLE.html
> >
> > Set MAX_ROWS to a very very large number
> > You can set these with ALTER TABLE. See section 6.5.4 ALTER TABLE
> > Syntax using table options at the end.
> >
> > You can verify your changes by running SHOW TABLE STATUS before
the
> > change then again after the change.
> >
> > Have you done this already?
>
> We tried it once, but it failed due to an unconnected problem (out
of
> diskspace). The thing is, the manual explicitly states (in para.
> 4.4.6.9) that this is a solution for an error 135 (No more room in
> record file) and that an error 136 (No more room in index file) is
> fixed by a table repair. So we decided to try something different
> first.




> No matter, we've freed up some diskspace now, so we'll try again.
Thanks
> for replying. I'm sure I'll be back if it doesn't work ;-)
>
> Groeten, - Jacco
>
> -- 
> +-+
> | IRL:  Jacco van Schaik  | The time is 17:52 on Monday October 6
2003.
> | mail: [EMAIL PROTECTED] | Here at Frontier HQ it's 13 degrees with
a
> | URL:  www.frontier.nl   | fresh breeze from the west.
> +-+
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>


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

Re: "No more room in index file"

[Ken Menzel]

Check out
MAX_ROWS = #
and
AVG_ROW_LENGTH = #
options in http://www.mysql.com/doc/en/CREATE_TABLE.html

Set MAX_ROWS to a very very large number
You can set these with ALTER TABLE. See section 6.5.4 ALTER TABLE
Syntax using table options at the end.

You can verify your changes by running SHOW TABLE STATUS before the
change then again after the change.

Have you done this already?

Ken
- Original Message - 
From: "Jacco van Schaik" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, October 06, 2003 8:44 AM
Subject: "No more room in index file"


> Hi,
>
> We have a database with a table whose data file (.MYD) has grown to
42
> GB and an associated index (.MYI) file that's grown to 17 GB, at
which
> point MySQL gave us an error 136 (No more room in index file).
>
> The MySQL manual says (in paragraph 4.4.6.9 "How to Repair Tables")
that
> this type of error can be fixed with a "repair table", but in our
case
> that doesn't seem to have helped. The maximum size of the index
file,
> as reported by myisamchk -d -v is still 17 GB with a keyfile pointer
> size of 3 bytes.
>
> Since then we've tried to drop the index and re-create it, but that
> hasn't helped either. The new index file is slightly smaller than it
> was before, but it's obviously only a matter of time until we'll run
> into the same limitation again.
>
> Does anyone know what we need to do to force MySQL to start using
4-byte
> keyfile pointers?
>
> Groeten, - Jacco
>
> -- 
> +-+
> | IRL:  Jacco van Schaik  | The time is 14:20 on Monday October 6
2003.
> | mail: [EMAIL PROTECTED] | Here at Frontier HQ it's 15 degrees with
a
> | URL:  www.frontier.nl   | strong breeze from the west.
> +-+
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>


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

Re: FreeBSD 4.8 runaway MySQL 4.0.14

[Ken Menzel]

Hi Nick,
   This is due (so I am told) to a problem in FreeBSD threads. I have
this happen once in a while also.  There have been some threads
changes for 4.9 but I am not sure there is any change in this.

The common suggestion is to compile with LINUXTHREADS option using the
ports tree.  This will solve that and give you better performance on a
multi CPU system. (cd /usr/ports/databases/mysql41-server; more
Makefile )
or see http://jeremy.zawodny.com/blog/archives/000458.html

Another option would be to update to FreeBSD 5.1 and use the new
threading (not so easy).

I personaly hope 4.9 will settle this down some as I don't see the
need to go to LINUX Threads for a single proc machine!

Best of Luck
Ken
- Original Message - 
From: "Nick Gaugler" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, August 28, 2003 3:53 PM
Subject: FW: FreeBSD 4.8 runaway MySQL 4.0.14


> I am running the binary installation of MySQL 4.0.14 on FreeBSD 4.8,
all
> MyISAM tables, and after alittle while mysqld will jump to 100% of
my
> CPU and go crazy.  I've investigated SHOW INNODB STATUS, and as you
can
> see below, there is nothing happening with Innodb.  Infact my Innodb
> files have not even changed since I last restarted.  But for some
reason
> MySQLd uses 100% of my CPU with no queries happening.  I tried to
run
> ktrace and this was all I saw:
>
>
>  30488 mysqld   CALL  gettimeofday(0x2846e288,0)
>  30488 mysqld   RET   gettimeofday 0
>  30488 mysqld   CALL  poll(0x83b6000,0xc,0)
>  30488 mysqld   RET   poll 0
>  30488 mysqld   CALL  poll(0x83b6000,0xd,0x126)
>  30488 mysqld   RET   poll 1
>  30488 mysqld   CALL  gettimeofday(0x2846e288,0)
>  30488 mysqld   RET   gettimeofday 0
>  30488 mysqld   CALL  poll(0x83b6000,0xd,0x126)
>  30488 mysqld   RET   poll 1
>
> With an occasional
>
>  30488 mysqld   CALL  poll(0x83b6000,0xd,0x126)
>  30488 mysqld   PSIG  SIGPROF caught handler=0x2842562c mask=0x0
> code=0x0
>  30488 mysqld   RET   poll 1
>  30488 mysqld   CALL  gettimeofday(0x2846e288,0)
>  30488 mysqld   RET   gettimeofday 0
>  30488 mysqld   CALL  sigreturn(0x83a9db8)
>  30488 mysqld   RET   sigreturn JUSTRETURN
>
>
> Has anyone experienced a runway mysqld on FreeBSD before?  Is there
> anything I can do to fix this or solve this problem?  Below is show
> innodb status\g
>
> =
> 030828 14:36:37 INNODB MONITOR OUTPUT
> =
> Per second averages calculated from the last 16 seconds
> --
> SEMAPHORES
> --
> OS WAIT ARRAY INFO: reservation count 4, signal count 4
> Mutex spin waits 0, rounds 0, OS waits 0
> RW-shared spins 6, OS waits 3; RW-excl spins 1, OS waits 1
> 
> TRANSACTIONS
> 
> Trx id counter 0 1280
> Purge done for trx's n:o < 0 0 undo n:o < 0 0
> Total number of lock structs in row lock hash table 0
> LIST OF TRANSACTIONS FOR EACH SESSION:
> ---TRANSACTION 0 0, not started, OS thread id 299134976
> MySQL thread id 176926, query id 516226 localhost root
> show innodb status
> 
> FILE I/O
> 
> I/O thread 0 state: waiting for i/o request (insert buffer thread)
> I/O thread 1 state: waiting for i/o request (log thread)
> I/O thread 2 state: waiting for i/o request (read thread)
> I/O thread 3 state: waiting for i/o request (write thread)
> Pending normal aio reads: 0, aio writes: 0,
>  ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
> Pending flushes (fsync) log: 0; buffer pool: 0
> 40 OS file reads, 4 OS file writes, 4 OS fsyncs
> 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
> -
> INSERT BUFFER AND ADAPTIVE HASH INDEX
> -
> Ibuf for space 0: size 1, free list len 0, seg size 2,
> 0 inserts, 0 merged recs, 0 merges
> Hash table size 34679, used cells 0, node heap has 0 buffer(s)
> 0.00 hash searches/s, 0.00 non-hash searches/s
> ---
> LOG
> ---
> Log sequence number 0 43892
> Log flushed up to   0 43892
> Last checkpoint at  0 43892
> 0 pending log writes, 0 pending chkp writes
> 9 log i/o's done, 0.00 log i/o's/second
> --
> BUFFER POOL AND MEMORY
> --
> Total memory allocated 16338600; in additional pool allocated 641280
> Buffer pool size   512
> Free buffers   493
> Database pages 19
> Modified db pages  0
> Pending reads 0
> Pending writes: LRU 0, flush list 0, single page 0
> Pages read 19, created 0, written 0
> 0.00 reads/s, 0.00 creates/s, 0.00 writes/s
> No buffer pool page gets since the last printout
> --
> ROW OPERATIONS
> --
> 0 queries inside InnoDB, 0 queries in queue
> Main thread id 158370816, state: waiting for server activity
> Number of rows inserted 0, updated 0, deleted 0, read 0
> 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
> 
> END OF INNODB MONITOR OUTPUT
>
> Nick
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/m

Re: Aborted clients

[Ken Menzel]

Hi Jeff,
   Just a quick guess did you change the max_connections variable up
from the default of 100?  You did not provide a show variables so I
can only guess.

>From the [mysqld] section of /etc/my.cnf
set-variable= max_connections=500

Ken
- Original Message - 
From: "Jeff McKeon" <[EMAIL PROTECTED]>
To: "MySQL LIST" <[EMAIL PROTECTED]>
Sent: Friday, July 25, 2003 7:45 AM
Subject: Aborted clients


> Ver 3.23 on RH Linux.
>
> We came in this morning and were greeted by our DB server rejecting
> connections to the db from our application.  There seems to be a
high
> number of Aborted_clients.  How can we tell what clients/connections
are
> causing this?
>
> mysql> show status;
> +--++
> | Variable_name| Value  |
> +--++
> | Aborted_clients  | 149|
> | Aborted_connects | 122|
> | Bytes_received   | 422801700  |
> | Bytes_sent   | 3604618252 |
> | Connections  | 7375   |
> | Created_tmp_disk_tables  | 885|
> | Created_tmp_tables   | 20331  |
> | Created_tmp_files| 509|
> | Delayed_insert_threads   | 0  |
> | Delayed_writes   | 0  |
> | Delayed_errors   | 0  |
> | Flush_commands   | 2  |
> | Handler_delete   | 5252   |
> | Handler_read_first   | 35028  |
> | Handler_read_key | 95425837   |
> | Handler_read_next| 2978256304 |
> | Handler_read_prev| 0  |
> | Handler_read_rnd | 7727972|
> | Handler_read_rnd_next| 3143081074 |
> | Handler_update   | 45487  |
> | Handler_write| 2010283|
> | Key_blocks_used  | 7793   |
> | Key_read_requests| 585802473  |
> | Key_reads| 18261  |
> | Key_write_requests   | 3740894|
> | Key_writes   | 254091 |
> | Max_used_connections | 100|
> | Not_flushed_key_blocks   | 0  |
> | Not_flushed_delayed_rows | 0  |
> | Open_tables  | 64 |
> | Open_files   | 109|
> | Open_streams | 0  |
> | Opened_tables| 252|
> | Questions| 5171955|
> | Select_full_join | 881|
> | Select_full_range_join   | 0  |
> | Select_range | 22 |
> | Select_range_check   | 0  |
> | Select_scan  | 280668 |
> | Slave_running| ON |
> | Slave_open_temp_tables   | 0  |
> | Slow_launch_threads  | 0  |
> | Slow_queries | 138|
> | Sort_merge_passes| 254|
> | Sort_range   | 474|
> | Sort_rows| 40227394   |
> | Sort_scan| 22550  |
> | Table_locks_immediate| 5695456|
> | Table_locks_waited   | 8278   |
> | Threads_cached   | 0  |
> | Threads_created  | 7373   |
> | Threads_connected| 97 |
> | Threads_running  | 2  |
> | Uptime   | 317854 |
> +--++
>
>
>
> Jeff McKeon
> IT Manager
> Telaurus Communications LLC
> [EMAIL PROTECTED]
> (973) 889-8990 ex 209
>
> ***The information contained in this communication is confidential.
It
> is intended only for the sole use of the recipient named above and
may
> be legally privileged. If the reader of this message is not the
intended
> recipient, you are hereby notified that any dissemination,
distribution
> or copying of this communication, or any of its contents or
attachments,
> is expressly prohibited. If you have received this communication in
> error, please re-send it to the sender and delete the original
message,
> and any copy of it, from your computer system. Thank You.***
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>


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

Re: MySQL 3.23.51 Reference Manual needed

[Ken Menzel]

Hi Venelin,
   If you have the source code go into the ./Docs directory of the
source tree and read the Makefile on how to build the format you want
(PDF, HTML etc)   if that is the machine you built on there may
already be the HTML format document there.

Hope this helps,
Ken
- Original Message - 
From: "Venelin Arnaoudov" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, July 25, 2003 6:41 AM
Subject: MySQL 3.23.51 Reference Manual needed


>
> Hi,
>
> I am looking for the Reference Manual for MySQL 3.23.51 (or the
latest of the prior ones). Can anyone help me?
>
>
> Kindest regards,
> Venelin Arnaoudov
>
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>


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

Re: Aborted clients

[Ken Menzel]

Hi Jeff,

The two telling line for your aborted clients are
> *** 39. row ***
> Variable_name: max_connections
> Value: 100
and
> > > | Max_used_connections | 100|

This means that you hit the limit.
Are saying you don't believe you should have 100 connections? If you
do SHOW PROCESSLIST are they all valid connections?  Do you need more
than 100? Did you perhaps copy an example config file or are you not
using any config file?

Have to run to a meeting now.  Hope this bring some ideas to the
table.
Ken
- Original Message - 
From: "Jeff McKeon" <[EMAIL PROTECTED]>
To: "Ken Menzel" <[EMAIL PROTECTED]>
Sent: Friday, July 25, 2003 9:54 AM
Subject: RE: Aborted clients


> No, no changes to the max_connections.  What we did discover is that
> although this is a master db and not a slave of any, it was trying
to
> replicate to an unknown server (nothing in the master.info file)
with
> user "test".  Nobody here recalls doing anything to set this up...
>
> FYI Variables
>
> mysql> show variables \G;
> *** 1. row ***
> Variable_name: back_log
> Value: 50
> *** 2. row ***
> Variable_name: basedir
> Value: /usr/local/mysql/
> *** 3. row ***
> Variable_name: binlog_cache_size
> Value: 32768
> *** 4. row ***
> Variable_name: character_set
> Value: latin1
> *** 5. row ***
> Variable_name: character_sets
> Value: latin1 dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7
> cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr
greek
> win1250 croat cp1257 latin5
> *** 6. row ***
> Variable_name: concurrent_insert
> Value: ON
> *** 7. row ***
> Variable_name: connect_timeout
> Value: 5
> *** 8. row ***
> Variable_name: datadir
> Value: /home/data/mysql/data/
> *** 9. row ***
> Variable_name: delay_key_write
> Value: ON
> *** 10. row ***
> Variable_name: delayed_insert_limit
> Value: 100
> *** 11. row ***
> Variable_name: delayed_insert_timeout
> Value: 300
> *** 12. row ***
> Variable_name: delayed_queue_size
> Value: 1000
> *** 13. row ***
> Variable_name: flush
> Value: OFF
> *** 14. row ***
> Variable_name: flush_time
> Value: 0
> *** 15. row ***
> Variable_name: have_bdb
> Value: NO
> *** 16. row ***
> Variable_name: have_gemini
> Value: NO
> *** 17. row ***
> Variable_name: have_innodb
> Value: NO
> *** 18. row ***
> Variable_name: have_isam
> Value: YES
> *** 19. row ***
> Variable_name: have_raid
> Value: NO
> *** 20. row ***
> Variable_name: have_ssl
> Value: NO
> *** 21. row ***
> Variable_name: init_file
> Value:
> *** 22. row ***
> Variable_name: interactive_timeout
> Value: 28800
> *** 23. row ***
> Variable_name: join_buffer_size
> Value: 131072
> *** 24. row ***
> Variable_name: key_buffer_size
> Value: 8388600
> *** 25. row ***
> Variable_name: language
> Value: /usr/local/mysql/share/mysql/english/
> *** 26. row ***
> Variable_name: large_files_support
> Value: ON
> *** 27. row ***
> Variable_name: locked_in_memory
> Value: OFF
> *** 28. row ***
> Variable_name: log
> Value: OFF
> *** 29

Searchable docs in french, german, russian etc

[Ken Menzel]

Hi MySQL doc team,
  I have was wondering if a change could be made so that when I browse
the MySQL docs in some selected language there is a search window that
searchs first the language I have currently selected.  The search
always returns the english documentation page even when the page
exists in the language I am browsing!  Browse in french,
http://www.mysql.com/doc/fr/index.html then use the search and it
returns english!  (Still helpful for me,  but not really ideal, and
the URL seems to include the language as part of the path
http://www.mysql.com/doc/search.php?q=binlog&from=%2Fdoc%2Ffr%2Findex.html )
. And even more, non english language words, like etendre, cannot
be searched at all!  It is  very cool to have the manual in other
languages.  I am making suggestions that should help non english
speakers  (you may already have thought of this and not gotten around
to it yet).

Thanks for the very helpful documentation,  you guys do a fantastic
job!
 Ken
-----
Ken Menzel  ICQ# 9325188
www.icarz.com  [EMAIL PROTECTED]


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

Re: selecting PRIMARY KEY when there is no unique value

[Ken Menzel]

Hi Paul,   (good to hear from you!)
   We  use (invoice number+date) number as the primary key for the
header and (invoice number+body item number+date) in the body of the
invoice.  We do not allow the same invoice number to be reused on the
same day. However they can eventually rollover (ours is a multi
location systems with a certain sequence reserved for each location).
Body item number a generated sequence.

I agree with paul TIMESTAMP is not a cool thing to use.  Generating
the sequence (there are many methods of doing this, see the "MySQL
Cookbook" by Paul Dubois for some examples including SQL variables or
an autoincrement key, program variables,  etc) gives a natural and
consistant order to the invoice.

I hope this helps,
Ken

> Don't use 2).  There is no guarantee a TIMESTAMP value will be
unique
> among records with the same invoice number.  That means invoice
number +
> timestamp wil not form a PRIMARY KEY.
>
> I'd use 1), possibly combining the invoice number with the item id
as
> a composite key.
>
>
> -- 
> Paul DuBois, Senior Technical Writer
> Madison, Wisconsin, USA
> MySQL AB, www.mysql.com
>
> Are you MySQL certified?  http://www.mysql.com/certification/
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>


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

Re: Help me!!!

[Ken Menzel]

mailto:[EMAIL PROTECTED]

- Original Message - 
From: "Luis Enrique Bauzá Peña" <[EMAIL PROTECTED]>
To: "Lista Mysql Inglés" <[EMAIL PROTECTED]>
Sent: Thursday, June 19, 2003 11:59 AM
Subject: Help me!!!


Hi, I need some link to a spanish mysql list, would you ...?



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

Re: Simultaneus TCP/IP connections

[Ken Menzel]

Hi Maciej,
1)   MySQL works very fine for me I have 3 servers with about 150
connections per server.  For other success stories see
http://www.mysql.com/press/user_stories/index.html

2) for configuration info see the example configuation files included
with mysql my-huge.cnf and my-large.cnf and the on-line manual for
where to place them.

3) There are many improvements in 4.0 and 4.1,  but handling
connections still works great.  Please use 4.0 as it is the current
production version,  4.1 is still alpha.

4) Sorry can't help with #4 maybe others can offer advice.  We run our
own servers.

Best of luck.
Ken
- Original Message - 
From: "Maciej Zagozda" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, June 16, 2003 12:20 PM
Subject: Simultaneus TCP/IP connections


> I have the following problem: many ISPs reject to offer mySQL
hosting
> and handling remote connections via TCP/IP, they are saying that the
> mySQL may become unstable with about 50 simultaneus TCP/IP
connections.
> Finally I'm thinking about setting up an own mySQL server on W2K
(pro)
> for the project I'm planning, but still I would like to ask you:
>
> 1. Is it true that mySQL can not handle up to 50 simultaneus remote
> connections?
> 2. How to eventually configure mySQL to handle such number of
> connections on Win2K (pro)?
> 3. Is there any significant improvement in handling remote
connections
> in version 4.xxx?
> 4. Can you advice some reliable provider that eventually offer quite
> cheap hosting?
>
> Regards, Maciej
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>


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

Re: mysql multi-thread

[Ken Menzel]

Le reponse de Harald est entirement correct.  En plus il existe un
liste en français:
 Liens utils :
  Poster un message: [EMAIL PROTECTED]
  S'abonner: [EMAIL PROTECTED]
  Se désabonner :  [EMAIL PROTECTED]
  Ecrire au proprio :   [EMAIL PROTECTED]

URL sur Yahoo :
  http://fr.groups.yahoo.com/group/mysql-france/

Et on  serais content de te donner plus de aide!
Amicalement,
Ken
- Original Message - 
From: "Harald Fuchs" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, May 30, 2003 7:54 AM
Subject: Re: mysql multi-thread


> In article <[EMAIL PROTECTED]>,
> "Yannick GUENET" <[EMAIL PROTECTED]> writes:
>
> > Bonjour, je viens d'installer mysql-4.0.3 , or je m'apercois que
pour chaque
> > nouvelle connection, un nouveau processus aparait dans ma liste
ps, est-il
> > possible de configurer mysql pour qu'il utilise des threads plutot
que des
> > fork ?
>
> Bonjour!
> (Sorry, my French is too rusty...)
> MySQL _does_ use threads instead of processes.  It's your
> implementation of "ps" which makes you think otherwise.
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>


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

Re: problème d'install de mysql sur hp-ux

[Ken Menzel]

Bonjour Jérôme,
   En premier cette liste est en anglais pourtant j'ai plusieurs
questions de te demander.  As tu essayé de compiler MySQL sur HPUX ou
utiliser l'édition precompilé sure le page de télécharger?  Je te
conseil de employer la version compiler par le groupe de mysql. En
plus  y-a-t'il des messages dans le fichier d'erreur de MySQL?

Amicalement,  Ken
On peut trouver la liste français à :

Liens utils :
   Poster un message: [EMAIL PROTECTED]
   S'abonner: [EMAIL PROTECTED]
   Se désabonner :  [EMAIL PROTECTED]
   Ecrire au proprio :   [EMAIL PROTECTED]

 URL sur Yahoo :
   http://fr.groups.yahoo.com/group/mysql-france/

- Original Message -

From: "Jerome JAMET" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, March 17, 2003 9:00 AM
Subject: problème d'install de mysql sur hp-ux


>
> Bonjour ,
>
> J'essai d'installer Mysql sur un serveur HP-UX  B.10.20 U 9000/800
> unlimited-user license, et lors du lancement de celui ci , j'ai la
creation
> d'un fichier core "/scripts/mysql_install_db[292]: 22671 Memory
> fault(coredump)" .
>
> Y a t'il une configuration a prendre en compte au niveau de du noyau
ou
> autre ??
>
> Cordialement
>
> Jérôme JAMET
>   Atos Origin Infogérance
>   Tour Manhattan - 21ème étage
>   5/6 , Place de l'Iris - La Défense 2
>   92067 Paris La Défense Cedex
>
> Tél : 01 73 29 32 50
> 06 14 39 55 52
>
>
>
> 
-
> 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: very slow mysql server, help please

[Ken Menzel]

Hi William,
   Sleeping processes are the result of persisitant connections and
should not slow down your server.  As to why you server is "so slow"
you will have to provide specific examples of what is slow.  We reach
speeds of 600 queries per second on slower hardware.  Also please
indicated languages used for examples and versions of FreeBSD, MySQL
and PHP (if you use PHP).

Also there are many tunable parameters
(http://www.mysql.com/doc/en/Server_parameters.html) I recommend Paul
Dubois Book on MySQL
http://www.amazon.com/exec/obidos/tg/detail/-/0735712123/qid=104696018
6/sr=1-4/ref=sr_1_4/103-5030472-7439844?v=glance&s=books

Best of Luck,
Ken
- Original Message -
From: "William Wang" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, March 05, 2003 10:11 PM
Subject: very slow mysql server, help please


>
> Hi All:
> I have three web server (DUAL 1.4 G MHZ, 1G RAM) connect to one
mysql
> database server (DUAL 2.2 G MHZ, 2 G RAM), SSL accelerator and a
load
> balancer. All those servers are FreeBSD. For some reason, mysql
server can
> be easily max out without many users. Also there are many processes
in
> sleep (some of them has been put in sleep for over 3000 seconds.)
>
> My database has nearly 140 tables, but there are less than 1
recorders.
> Database file size is about 3M. One of the table has a BLOB field
(Hold
> some images for my web server.). I am using mysql_pconnect() (Of
cause, I
> tried mysql_connect(), but it's does not make big different.).
>
> Question 1: Does sleeping processes slow down database server?
> Question 2: Any ideas, why is the database so slow?
>
> Any suggestions?
>
> Thanks in advance and for Reading my message.
>
> William
>
>
> 
-
> 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 4.0.11 is released

[Ken Menzel]

Hi,
How come there is not a source download for 4.0.11???  The source
download is still 4.0.10!  Did some one miss the source tar ball?

Thanks,
Ken


- Original Message -
From: "Lenz Grimmer" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Wednesday, February 26, 2003 9:47 AM
Subject: MySQL 4.0.11 is released


> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Hi,
>
> MySQL 4.0.11, a new version of the popular Open Source Database, has
been
> released. It is now available in source and binary form for a number
of
> platforms from our download pages at http://www.mysql.com/downloads/
and
> mirror sites.
>
> Note that not all mirror sites may be up to date at this point of
time -
> if you can't find this version on some mirror, please try again
later or
> choose another download site.
>
> This is a new beta development release, adding new features and
fixing
> recently discovered bugs.
>
>
> Functionality added or changed:
>
>  * Fixed problem with `multi-table-delete' and InnoDB tables.
>
>  * Fixed a problem with `BLOB NOT NULL' columns used with `IS NULL'.
>
>  * `NULL' is now sorted *LAST* if you use `ORDER BY ... DESC' (as it
>was before before MySQL 4.0.2).  This is change was required to
>follow ANSI SQL 99.  (The original change was because we thought
>that ANSI SQL 99 required NULL to be always sorted at the same
>position, but this was wrong).
>
>  * Added `START TRANSACTION' (ANSI SQL99 syntax) as alias for
`BEGIN'.
>This is recommended to use instead of `BEGIN' to start a
>transaction.
>
>  * Added `OLD_PASSWORD()' as a synonym for `PASSWORD()'.
>
>  * Allow keyword `ALL' in group functions.
>
>  * Added support for some new `INNER JOIN' and `JOIN' syntaxes.  For
>  example, `SELECT * FROM t1 INNER JOIN t2' didn't work before.
>
>
> Bugs fixed:
>
>  * Re-added missing pre- and post(un)install scripts to the Linux
RPM
>packages (they were missing after the renaming of the server
>subpackage).
>
>  * Fixed that table locks are not released with multi-table updates
>and deletes with InnoDB storage engine.
>
>  * Fixed bug in updating BLOB columns with long strings.
>
>  * Fixed integer-wraparound when giving big integer (>= 10 digits)
to
>function that requires an unsigned argument, like `CREATE TABLE
>(...)  AUTO_INCREMENT=#'.
>
>  * `MIN(key_column)' could in some cases return `NULL' on a column
>with `NULL' and other values.
>
>  * `MIN(key_column)' and `MAX(key_column)' could in some cases
return
>wrong values when used in `OUTER JOIN'.
>
>  * `MIN(key_column)' and `MAX(key_column)' could return wrong values
>if one of the tables was empty.
>
>  * Fixed rare crash in compressed MyISAM tables with blobs.
>
>  * Fixed bug in using aggregate functions as argument for
`INTERVAL',
>`CASE', `FIELD', `CONCAT_WS', `ELT' and `MAKE_SET' functions.
>
>  * When running with `--lower-case-table-names' (default on windows)
>and you had tables or databases with mixed case on disk, then
>executing `SHOW TABLE STATUS' followed with `DROP DATABASE' or
>`DROP TABLE' could fail with `Errcode 13'.
>
>
> Additional notes:
>
>  * In addition to the binary tarballs, we now do offer native
>Mac OS X PKG packages for Mac OS X 10.2 "Jaguar". Please see
>http://www.mysql.com/doc/en/Mac_OS_X_installation.html for more
info.
>
>  * There are now two different binaries for HP-UX 11 - one for
PA-RISC 2.0
>64bit platforms and one that has been compiled to to be
compatible with
>older PA-RISC 1.1 32bit platforms:
>
>mysql--hp-hpux11.11-hppa2.0w-64bit.tar.gz (64bit PA-RISC
2.0)
>mysql--hp-hpux11.11-hppa2.0w.tar.gz (32bit PA-RISC 1.1)
>
>  * Please note that the "bigint" test in 4.0.11 fails on some 64bit
>Platforms like HP-UX 11 or Tru64. This is because of an error in
this
>particular test of the test suite which has already been fixed in
the
>source tree for future releases.
>
>  * To fix a compilation bug on HP-UX 10.20, we had to add an
additional
>patch to the 4.0.11 sources used to build these binaries.
Therefore,
>the Unix source tarball as well as the HP-UX 10.20 binaries will
be
>labelled 4.0.11a instead of 4.0.11. The other binaries are not
affected
>by this change. This modification has already been added to the
4.0
>source tree and will be part of future releases.
>
> Bye,
> LenZ
>
> - --
> For technical support contracts, visit
https://order.mysql.com/?ref=mlgr
>__  ___ ___   __
>   /  |/  /_ __/ __/ __ \/ /  Mr. Lenz Grimmer <[EMAIL PROTECTED]>
>  / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Production Engineer
> /_/  /_/\_, /___/\___\_\___/ Hamburg, Germany
><___/   www.mysql.com
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.2.0 (GNU/Linux)
> Comment: For info see http://quantumlab.net/pine_privacy_guard/
>
> iD8DBQE+XNN2SVDhKrJykfIRAm93AJ9N0fcXomnsezNoX8V8vJJ1rKU+TgCfVmFG
> Sws0S1rB8orMpU2uQf8wO

Re: 4G file limit

[Ken Menzel]

Hi,
 Even if you have large file support you must tell mysql to use long
pointers when creating the table.  The way to accomplih this is to add
the MAX_ROWS table option when creating the table,  just make it a
really large number like 50.  You can verify this effect by
BEFORE makeing the change, use SHOW TABLE STATUS LIKE 'MYTABLE' .
Then look at the max_data_length column.  Before you change it will be
this 4294967295 (4.0G),  after your change it will be something like
1099511627775.

Yes,  the error message does not make sense.  I have not had time to
build a test case to get to MySQL so they can give us a better error
message.

See this manual page for info on create table.
http://www.mysql.com/doc/C/R/CREATE_TABLE.html

You can just alter the table with this option.

Hope this helps,

- Original Message -
From: "Veysel Harun Sahin" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, February 03, 2003 4:49 AM
Subject: 4G file limit


> Hello,
>
> I am using mysql3.23.49 on openbsd3.2. I have a myisam table with 4G
> file size and now I can no longer insert records into table. My
mysql
> data partition is not full (%24 used). I have read that mysql 3.23
> supports up to 2 terabytes table size. Not so sure but I know
openbsd's
> file size limit is bigger than 4G. Here is my error log.
>
> Failed on my_net_write, writing to stderr instead: Found block with
too
> small length at 4294914928; Skipped
> Failed on my_net_write, writing to stderr instead: Found block that
> points outside data file at 4294915080
>
> Any comments?
>
> Thanks.
>
> mysql, sql
>
>
>
>
> 
-
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try:
http://lists.mysql.com/php/unsubscribe.php
>
>


-
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: Character problems

[Ken Menzel]

Hi Maximo,
  Try this: add this to your HTML document


Hope it helps
Ken
- Original Message -
From: "Maximo Migliari" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, January 30, 2003 11:19 AM
Subject: Character problems


> Platform: windows
> MySQL version: 4.0.9-gamma
> PHP version: 4.3.0
>
> Connected through Command Prompt via MySQL client to local MySQL
server.
>
> Output from a SELECT query:
>
> mysql> select * from category;
>
+++---+--+-+--
--+
> | id | parent | name  | num_elements | description |
type   |
>
+++---+--+-+--
--+
> |  1 |  0 | materias  |0 |   1 |
artigo |
> |  2 |  0 | artigos   |0 |   2 |
artigo |
> |  3 |  1 | Tipos de câncer   |0 |   3 |
artigo |
> |  4 |  1 | Crianças carentes |0 |   4 |
artigo |
> |  5 |  3 | Leucemia  |0 |   5 |
artigo |
> |  6 |  3 | Câncer de pele|0 |   6 |
artigo |
> |  7 |  3 | Notícias  |0 |   7 |
ultima |
>
+++---+--+-+--
--+
> 7 rows in set (0.00 sec)
>
> mysql> exit
> Bye
>
> Note that for the name field,  the characters ç, í, â, are all
appearing
> correctly.
>
> Now when I select this information from PHP and print it as a
webpage, this
> is what I get:
> f  instead of â,
> ¡  instead of í
> ? instead of ç
>
> What's up?  I used to do this before without any problems.  I'm
assuming
> this is some character encoding issue - however, I don't know if its
in PHP
> or in MySQL the problem.
>
> Help!
> Maximo.
>
>
> 
-
> 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 dies after FreeBSD 4.6.2-cvsup-4.7

[Ken Menzel]

Well, just a suggestion,  but if you did not install world (where the
threads are) then you have a mismatch between kernel and world.
OT (but what the heck!)
proper procedure for freebsd

cvsup to desired version
cd /usr/src
mergemaster -p
 make buildworld
make buildkernel KERNCONF=yourkernel
make installkernel KERNCON=yourkernel
reboot
make installworld
mergemaster (your favorite otpions eg: -i )
reboot once more.

Have several server running 3.23 and 4.0 with no problems.

Ken
- Original Message -
From: "Tuc" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Wednesday, January 29, 2003 11:25 AM
Subject: MYSQL dies after FreeBSD 4.6.2-cvsup-4.7


> Hi,
>
> We just cvsup'd a FreeBSD machine from 4.6.2 to 4.7, made
world,
> and installed a new kernel. Now mysql is complaining :
>
>
> Fatal error 'Can't create gc thread' at line ? in file
/usr/src/lib/libc_r/uthre
> ad/uthread_create.c (errno = ?)
> 030129 10:36:35  mysqld restarted
> Fatal error 'Can't create gc thread' at line ? in file
/usr/src/lib/libc_r/uthre
> ad/uthread_create.c (errno = ?)
> 030129 10:36:35  mysqld restarted
> Fatal error 'Can't create gc thread' at line ? in file
/usr/src/lib/libc_r/uthre
> ad/uthread_create.c (errno = ?)
>
>
> Any ideas where to start? Tried to recompile mysql to see if
it just
> needed that, and it didn't change anything. (This is 3.23.54a)
>
> Thanks, Tuc/TTSG Internet Services, Inc.
>
>
> 
-
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try:
http://lists.mysql.com/php/unsubscribe.php
>
>


-
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: FreeBSD 5.0 Thread Work...

[Ken Menzel]

Hi Jeremy,
   My only concern with LinuxThreads is building MySQL 4.0 or 4.1 with
them as there is still not a FreeBSD port for MySQL 4 !!!  I had alot
of trouble making Linux Threads work initialy.  If people want to use
MySQL 4 and want to build from ports (the recommended method) I think
they are stuck at present.  Building from source with native threads
is easy.

Do you have a shell script you would share that builds MySQL 4.0
and/or 4.1 from source with Linux Threads?

Thanks for all the help and great answers you always provide,
Ken
- Original Message -
From: "Jeremy Zawodny" <[EMAIL PROTECTED]>
To: "Grigor, Peter" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, January 21, 2003 4:45 PM
Subject: Re: FreeBSD 5.0 Thread Work...


> On Mon, Jan 20, 2003 at 02:20:10PM -0500, Grigor, Peter wrote:
> > Alrighty then!
> >
> > Now FreeBSD 5.0 has been released, what are the plans for mysql to
evaluate
> > the new (really new :) thread implementation.
>
> What's wrong with LinuxThreads on FreeBSD?  It works great with
> FreeBSD 4.x.
>
> Jeremy
> --
> Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
> <[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/
>
> MySQL 4.0.8: up 2 days, processed 83,092,818 queries (328/sec. avg)
>
> 
-
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try:
http://lists.mysql.com/php/unsubscribe.php
>
>


-
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: FreeBSD 5.0 Thread Work...

[Ken Menzel]

Hi Dan,
   Thanks for that information,  does that mean (for us simple SQL
guys) that even though I am running 5.0RC3 and can compile and install
MySQL just fine (from ports or with my own script) it is using the
threads library from 4-STABLE branch not the new threads?

Also does this have soemthing to do with the word GIANT0 that keeps
appearing in the 'top' process list?


I was hoping for great things from the 5.0, even with kernel profiling
off (invariants?) the /usr/local/sql-bench runs slower on 5.0.  It is
not a show stopper, but it is noticable and measureable.  I am going
to continue to run 5.0 on my test servers with the hopes of great
improvements.  I guess it will be a few more months before trying in
production to be able to see an improvement.  I don't mind being an
early adopter (with the problems that comes with it),  but it will be
good to see things works better.  It does appear not to creash at all!
(for me).
<\observations>

Guess it just a little early, (Darn)!

A freebsd fan
Ken


- Original Message -
From: "Dan Nelson" <[EMAIL PROTECTED]>
To: "Maximo Migliari" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Monday, January 20, 2003 4:54 PM
Subject: Re: FreeBSD 5.0 Thread Work...


> In the last episode (Jan 20), Maximo Migliari said:
> > YES, pretty please, with sugar on top: from now on also make
> > available for us binary distributions of MySQL optimized for
FreeBSD
> > 5.0's new threading implementation.
> >
> > There is a very large FreeBSD user base that must not be ignored
:)
>
> FreeBSD 5.0 has the ability for userland processes to create
multiple
> kernel threads.  It does not yet have a POSIX interface to those
> threads, so don't go asking people for kernel threads support just
yet :)
>
> 5.0 has a lot of new features, but some of them are not complete
> (kernel threads), or not very well tested (sparc64/ia64 ports, MAC,
> ufs2).  The hope is that lots of people will install 5.0 and report
> problems so that 5.2 can be marked -STABLE.
>
> http://www.freebsd.org/releases/5.0R/early-adopter.html
>
> --
> Dan Nelson
> [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
>
>


-
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: FreeBSD: MySQL crash when importing 148 MB dumpfile

[Ken Menzel]

Hi Maximo,
   The problem is that you have a config that requires more memory
than you have available.  Please reduce key buffer, record buffer and
sort buffer.  The clues are:
 030117 17:02:55  Out of memory;  Check if mysqld or some other
process uses
 key_buffer_size + (record_buffer + sort_buffer)*max_connections =
3859309 K

3859309K= almost 4G!

try
key_buffer_size=64M
record_buffer=1M
sort_buffer=1M
max_connections=256


Do you really need 1 connections?

Best of luck,
Ken
- Original Message -
From: "Maximo Migliari" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, January 17, 2003 6:28 PM
Subject: FreeBSD: MySQL crash when importing 148 MB dumpfile


> Problem:  I am migrating servers, from a dual PIII-866 Mhz with 384
MB of
> RAM running Linux to the following server configuration:
>
> Dual Athlon 1.5 Ghz
> 1 GB RAM
> 2 x 18 GIG SCSI drives.
> FreeBSD 4.7-Stable
> MySQL-Max-3.23.54a (ELF) i.e. without linuxthreads - runs on only 1
process.
>
> I am trying to import a database that was saved using mysqldump into
the
> new machine using a method that has NEVER failed with me until now -
that
> is, to do: mysql -uroot -pmypassword tablename < tablename.sql.
>
> Here is how I did it, and the error message:
>
> [root@server1 admin]# mysql -uroot -pxxx _final <_final.sql
> ERROR 2013 at line 1814: Lost connection to MySQL server during
query
> [root@server1 admin]# 030117 17:02:55  mysqld restarted
>
> I have attempted this on both:
> MySQL-Max-3.23.54a and the latest MySQL 4 gamma release for FreeBSD.
>
> When importing other databases, it works fine, but for this larger
file,
> the server is crashing. I have the same version of the software
running on
> another server, a dual Intel 866 Mhz Linux system with only 384 MB
of RAM,
> and this sort of thing ALWAYS works.
> I am using the same my.cnf on both systems, I have pasted my.cnf at
the end
> of this e-mail.
>
> This is what I get in the .err file after the server crashes and
restarts:
> -
> [root@server1 data]# cat server1.err
> 030117 17:01:22  mysqld started
> 030117 17:01:22  Warning: setrlimit returned ok, but didn't change
limits.
> Max open files is 11095
> 030117 17:01:22  Warning: Changed limits: max_connections:
> 1  table_cache: 542
> 030117 17:01:23  InnoDB: Started
> /usr/local/mysql-max-3.23.54a/bin/mysqld: ready for connections
> 030117 17:02:55  Out of memory;  Check if mysqld or some other
process uses
> all available memory. If not you may have to use 'ulimit' to allow
mysqld
> to use more memory or you can add more swap space
> mysqld got signal 11;
> This could be because you hit a bug. It is also possible that this
binary
> or one of the libraries it was linked against is corrupt, improperly
built,
> or misconfigured. This error can also be caused by malfunctioning
hardware.
> We will try our best to scrape up some info that will hopefully help
diagnose
> the problem, but since we have already crashed, something is
definitely wrong
> and this may fail
>
> key_buffer_size=201322496
> record_buffer=1044480
> sort_buffer=1048568
> max_used_connections=0
> max_connections=1
> threads_connected=1
> It is possible that mysqld could use up to
> key_buffer_size + (record_buffer + sort_buffer)*max_connections =
3859309 K
> bytes of memory
> Hope that's ok, if not, decrease some variables in the equation
>
> 030117 17:02:55  mysqld restarted
> 030117 17:02:55  Warning: setrlimit returned ok, but didn't change
limits.
> Max open files is 11095
> 030117 17:02:55  Warning: Changed limits: max_connections:
> 1  table_cache: 542
> 030117 17:02:56  InnoDB: Database was not shut down normally.
> InnoDB: Starting recovery from log files...
> InnoDB: Starting log scan based on checkpoint at
> InnoDB: log sequence number 0 402622121
> InnoDB: Doing recovery: scanned up to log sequence number 0
407864832
> InnoDB: Doing recovery: scanned up to log sequence number 0
413107712
> InnoDB: Doing recovery: scanned up to log sequence number 0
418350592
> InnoDB: Doing recovery: scanned up to log sequence number 0
423593472
> and this may fail
>
> key_buffer_size=201322496
> record_buffer=1044480
> sort_buffer=1048568
> max_used_connections=0
> max_connections=1
> threads_connected=1
> It is possible that mysqld could use up to
> key_buffer_size + (record_buffer + sort_buffer)*max_connections =
3859309 K
> bytes of memory
> Hope that's ok, if not, decrease some variables in the equation
>
> 030117 17:02:55  mysqld restarted
> 030117 17:02:55  Warning: setrlimit returned ok, but didn't change
limits.
> Max open files is 11095
> 030117 17:02:55  Warning: Changed limits: max_connections:
> 1  table_cache: 542
> 030117 17:02:56  InnoDB: Database was not shut down normally.
> 
> .. LOTS OF INNODB RECOVERY MESSAGES...
> ..
> /usr/local/mysql-max-3.23.54a/bin/mysqld: ready for connections
> ---
>
>
> I thought this might have been a kerna

Re: MySQL freezes running under BSDI 4.2

[Ken Menzel]

Hi Dave,
   Couple of things: Did you see these notes?  They apply to BSDi also
http://www.mysql.com/doc/en/FreeBSD.html
 Do you have more that 1.2G of free memory?  What version of Mysql?

Ken
P.S.  my compile script
rm config.cache
CC=gcc  CFLAGS="-O3 -DHAVE_BROKEN_REALPATH"  CXX=gcc  \
CXXFLAGS="-O3 -felide-constructors -fno-exceptions -fno-rtti -DHAVE_BR
OKEN_REALPATH" \
./configure $DEBUG --enable-large-files --with-innodb \
--enable-assembler
gmake -s

- Original Message -
From: "Dave Granic" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, January 16, 2003 8:17 AM
Subject: MySQL freezes running under BSDI 4.2


> Going insane, we have a BSDI 4.2 Version running on an under powered
> machine, we are trying to load up a new server with more horsepower
also
> running BSDI 4.2
>
> We tried several compiled configurations and the binary distribution
and the
> server hangs after a about 1-2 hours of use (binary within
minutes)..
>
> compiles fine
> /set threads to 2 in my.cnf
> correctly set ld.so.conf
>
>
> below is what we find in the .err file
>
> BSDI is  patched up
>
> /usr/local/mysql/libexec/mysqld: ready for connections
> mysqld got signal 11;
> This could be because you hit a bug. It is also possible that this
binary
> or one of the libraries it was linked against is corrupt, improperly
built,
> or misconfigured. This error can also be caused by malfunctioning
hardware.
> We will try our best to scrape up some info that will hopefully help
> diagnose
> the problem, but since we have already crashed, something is
definitely
> wrong
> and this may fail
>
> key_buffer_size=268431360
> record_buffer=1044480
> sort_buffer=1048568
> max_used_connections=1
> max_connections=500
> threads_connected=2
> It is possible that mysqld could use up to
> key_buffer_size + (record_buffer + sort_buffer)*max_connections =
1284136 K
> bytes of memory
> Hope that's ok, if not, decrease some variables in the equation
>
>
>
>
> 
-
> 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: Query mysql on another server

[Ken Menzel]

Well,  not the way you seem to be implying.  You must use your web
application to create a connection to the other database.  You can't
join or union tables,  just create the connection and run the query.
Then your app can do what ever it wants with the data.
Ken
- Original Message -
From: "Paul DuBois" <[EMAIL PROTECTED]>
To: "Chris Boget" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, January 07, 2003 12:11 PM
Subject: Re: Query mysql on another server


> At 10:08 -0600 1/7/03, Chris Boget wrote:
> >We have a *nix network and on that network we have 2
> >web servers.  One for development (server1) and one for
> >production (server2).  And on each of those servers we
> >have MySQL installed.  Is there a way we can query a
> >table on server2 from server1?
>
> No.
>
> >I know that when you are using a database from the mysql
> >command line, you can query a table in another database
> >just by using select databasename.tablename.  But the
> >same method using servername doesn't work.  Is this even
> >possible?
>
> No.
>
> >
> >thnx,
> >Chris
>
> sql, query
>
> 
-
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try:
http://lists.mysql.com/php/unsubscribe.php
>
>


-
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: strange behaviour

[Ken Menzel]

Hi Wash,

Please see ntoes on this page for explanations.
http://jeremy.zawodny.com/blog/archives/000203.html#000203

Next please upgrade to 3.23.54 as this does contain some fixes that
should help including a compile option for broken real path.

Here are my compile flags,  I don't recommend -O3 but it seems to be
OK for me so far.
--

rm config.cache
CC=gcc  CFLAGS="-O3 -DHAVE_BROKEN_REALPATH"  CXX=gcc  \
CXXFLAGS="-O3 -felide-constructors -fno-exceptions -fno-rtti -DHAVE_BR
OKEN_REALPATH" \
./configure
$DEBUG --enable-large-files --with-berkeley-db --with-innodb \
--enable-assembler
--
--

FreeBSD 5 is supposed to improve things but I am having some trouble
with my SCSI controller and FBSD5 seeing the drives.

Best of luck,
Ken
- Original Message -
From: "Odhiambo Washington" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, January 03, 2003 2:10 AM
Subject: strange behaviour


>
> I run mysql on FreeBSD and it's been giving me a lot of problems.
Sometimes
> the load shoots up and some applications cannot access mysql server
anymore.
> During such instances, when I run top, I get something like this:
>
>
> last pid: 57669;  load averages:  1.40,  1.52,  1.52
up 30+00:05:29
> 10:07:24
> 105 processes: 2 running, 101 sleeping, 1 stopped, 1 zombie
> CPU states: 10.1% user,  0.0% nice, 69.3% system,  0.2% interrupt,
20.4% idle
> Mem: 522M Active, 284M Inact, 146M Wired, 48M Cache, 112M Buf, 3916K
Free
> Swap: 1374M Total, 1208K Used, 1373M Free
>
>   PID USERNAME  PRI NICE  SIZERES STATE  C   TIME   WCPU
CPU COMMAND
> 12192 mysql  64   0   279M 13716K CPU0   1  93.1H 92.82%
92.82% mysqld
>
>
>
>
>
>
> I see mysqld taking so much processor time and then I run mytop and
I get:
>
> MySQL on localhost (3.23.41-log)
up 5+18:36:24
> [10:04:04]
>  Queries Total: 3,135,669  Avg/Sec: 6.28  Now/Sec: 9.00  Slow: 0
>  Threads Total: 23Active: 1 Cached: 7
>  Key Efficiency: 99.99%  Bytes in: 285,244,678  Bytes out:
181,425,048
>
>  Id  User  Host  DB   TimeCmd Query or State
>  --      --   --- --
>  399603  root localhosttest  0  Query show full
processlist
>  186085  wolvmail localhost virtualemai  6  Sleep
>  102684 wolrt2use localhost rt2 12  Sleep
>  399436  wolvmail localhost virtualemai 86  Sleep
>  104018 wolrt2use localhost rt2124  Sleep
>  103015 wolrt2use localhost rt2213  Sleep
>  103557 wolrt2use localhost rt2213  Sleep
>  109938 wolrt2use localhost rt2292  Sleep
>  102741 wolrt2use localhost rt2370  Sleep
>  103063 wolrt2use localhost rt2410  Sleep
>  102009 wolrt2use localhost rt2425  Sleep

>  102737 wolrt2use localhost rt2425  Sleep
>  103585 wolrt2use localhost rt2707  Sleep
>  330671 twig275us localhosttwig  55140  Sleep
>  340661 twig275us localhosttwig  55147  Sleep
>  340198 twig275us localhosttwig  55206  Sleep
>  179428 twig275us localhosttwig  55244  Sleep
>  179385 twig275us localhosttwig  55260  Sleep
>  349415 twig275us localhosttwig  55334  Sleep
>  179413 twig275us localhosttwig  55365  Sleep
>  326960 twig275us localhosttwig  55402  Sleep
>  330717 twig275us localhosttwig  55453  Sleep
>  340067 twig275us localhosttwig  0  Sleep
>   1 system us  none 498984 Connec connecting to
master
>
>
>
> My question is: Why connecting to master?
>
> Can someone explain this to me? I am lost for what could be causing
me
> so much grief. I am not an MySQL expert.
>
> Thanks
>
>
>
> -Wash
>
> --
> Odhiambo Washington   <[EMAIL PROTECTED]>  "The box said 'Requires
> Wananchi Online Ltd.  www.wananchi.com  Windows 95, NT, or
better,'
> Tel: +254 2 313985-9  +254 2 313922 so I installed FreeBSD."
> GSM: +254 72 743223   +254 733 744121   This sig is McQ!  :-)
>
>
> Take my word for it, the silliest woman can manage a clever man, but
it
> needs a very clever woman to manage a fool.
> -- Kipling
>
> 
-
> 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-m

Re: MySQL eats 99& CPU time problem help

[Ken Menzel]

Hi Shawn,
  Also under BSDi make sure you are not running out of file
descriptors.  MySQL behaves very badly on BSDi when file descriptors
run out!

Ken
- Original Message -
From: "shawn xu" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, December 11, 2002 11:12 AM
Subject: MySQL eats 99& CPU time problem help


> We have MySQL 3.23.53 running on Intel machine with OS
> BSDI 4.3.
>
> The problem happened more often like this.
>
> MySQL eated up 99% cpu time which was told by running
> "top". When this happened, I guess it still answer
> queries, but it is too slow to answer, which results
> in the customers complaining.
>
> What we have tried, none of them work except wiping
> the disk and reinstalling everything.
>
> I`d appreciate any suggestions to fix this problem.
>
> Shawn
>
>
>
__
> Post your free ad now! http://personals.yahoo.ca
>
> 
-
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try:
http://lists.mysql.com/php/unsubscribe.php
>
>


-
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: table_cache too high?

[Ken Menzel]

Hi Thomas,
   This would be a question for your OS support list,  however an
example from FreeBSD:

bash-2.04$ sysctl -a | grep files
kern.maxfiles: 2
kern.maxfilesperproc: 19000
kern.openfiles: 1569
bash-2.04$ su
tuvok# sysctl kern.maxfiles=20001
kern.maxfiles: 2 -> 20001
tuvok#

 Hope this helps, but it is off topic for this list.

Ken
- Original Message -
From: "Thomas Seifert" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, November 20, 2002 9:25 AM
Subject: Re: table_cache too high?


>
> and how on-the-fly?
>
>
> Thomas
>
> On Wed, 20 Nov 2002 09:18:15 -0500 "Ken Menzel" <[EMAIL PROTECTED]>
wrote:
>
> > Yes,  you are correct and it can be worse because you have not
> > considered that there are some descriptors required by other
processes
> > running.   I suggest you increase your file limit to at least
2048.
> >
> > Good luck
> > Ken
> > - Original Message -
> > From: "Lance Lovette" <[EMAIL PROTECTED]>
> > To: "MySQL" <[EMAIL PROTECTED]>
> > Sent: Wednesday, November 20, 2002 12:12 AM
> > Subject: table_cache too high?
> >
> >
> > > My database has many hundreds of tables. Originally I set my
> > table_cache to
> > > 512. Today I realize this might not be a good idea. By default
> > > open_files_limit is 0. According to my understanding of the
manual
> > this
> > > means each MySQL process will open at most 1124 file handles:
> > >
> > > max_connections + (table_cache * 2)
> > > = 100 + (512 * 2)
> > > = 1124
> > >
> > > ulimit -n says the process file handle limit is 1024. Am I
correct
> > in
> > > assuming this configuration could potentially put the server in
an
> > unstable
> > > situation?
> > >
> > > Thanks!
> > > Lance
> > >
> > >
> >
> 
> > -
> > > 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
>
>


-
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: table_cache too high?

[Ken Menzel]

Yes,  you are correct and it can be worse because you have not
considered that there are some descriptors required by other processes
running.   I suggest you increase your file limit to at least 2048.

Good luck
Ken
- Original Message -
From: "Lance Lovette" <[EMAIL PROTECTED]>
To: "MySQL" <[EMAIL PROTECTED]>
Sent: Wednesday, November 20, 2002 12:12 AM
Subject: table_cache too high?


> My database has many hundreds of tables. Originally I set my
table_cache to
> 512. Today I realize this might not be a good idea. By default
> open_files_limit is 0. According to my understanding of the manual
this
> means each MySQL process will open at most 1124 file handles:
>
> max_connections + (table_cache * 2)
> = 100 + (512 * 2)
> = 1124
>
> ulimit -n says the process file handle limit is 1024. Am I correct
in
> assuming this configuration could potentially put the server in an
unstable
> situation?
>
> Thanks!
> Lance
>
>
> 
-
> 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 source for 4.0.5

[Ken Menzel]

Bingo!   Thanks Dan
Missing were:

ftp://mysql.nixc.net/pub/mysql/Downloads/MySQL-4.0/mysql-4.0.5-beta.ta
r.gz

ftp://mirror.sit.wisc.edu/mirrors/mysql/Downloads/MySQL-4.0/mysql-4.0.
5-beta.tar.gz

http://mysql.mirrors.netnumina.com/Downloads/MySQL-4.0/mysql-4.0.5-bet
a.tar.gz

I only tried 3 before yelling for help.

Thanks again,
Ken
- Original Message -
From: "Dan Nelson" <[EMAIL PROTECTED]>
To: "Ken Menzel" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, November 19, 2002 5:17 PM
Subject: Re: mysql source for 4.0.5


> In the last episode (Nov 19), Ken Menzel said:
> > I have tried 4 mirrors in USA to download mysql source for 4.0.5
> > seems like the file is missing.  Is anyone else having the same
> > problem?
>
>
ftp://mirror.mcs.anl.gov/pub/mysql/Downloads/MySQL-4.0/mysql-4.0.5-bet
a.tar.gz
>
> worked for me this morning.
>
> --
> Dan Nelson
> [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

mysql source for 4.0.5

[Ken Menzel]

I have tried 4 mirrors in USA to download mysql source for 4.0.5
seems like
the file is missing.  Is anyone else having the same problem?

Ken

-
Ken Menzel  ICQ# 9325188
www.icarz.com  [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: How to index a large table?

[Ken Menzel]

Also, use "myisamchk -dv tablename"

Pay attention to datafile pointer (bytes) and Keyfile pointer (bytes)
and the Max keyfile/datafile length.  The keyfile pointer must be at
least 4 bytes for a large table, if it says 3 you have a problem!

This is isn't clear from the CREATE TABLE section of the manual,   but
is in the "1.2.4 How Big Can MySQL Tables Be?".  I don't know if there
is any problem or change like this need for InnoDB, since Innodb is
not mentioned I would suspect it is not needed.

Ken
- Original Message -
From: "Paul DuBois" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; "Ken Menzel" <[EMAIL PROTECTED]>
Cc: "Jocelyn Fournier" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, October 22, 2002 8:00 PM
Subject: Re: How to index a large table?


> At 18:45 -0500 10/22/02, [EMAIL PROTECTED] wrote:
> >Indeed, setting MAX_ROWS lets me create the index.  Thanks for the
tip.
> >
> >How would I find out what the MAX_ROWS setting on a table is?
>
> SHOW CREATE TABLE tbl_name;
>
> or
>
> SHOW TABLE STATUS LIKE 'tbl_name';
>
> >
> >
> >
> >- Original Message -
> >From: Ken Menzel <[EMAIL PROTECTED]>
> >Date: Tuesday, October 22, 2002 4:05 pm
> >Subject: Re: How to index a large table?
> >
> >>  Just curious Chris,  did you set MAX_ROWS option during table
create
> >>  to a really large value?  If not alter the the table with this
and try
> >>  again,  it affects the size of pointers used to create the
index.
> >>  (See CREATE TABLE in the manual)
> >>
> >>  Hope it helps,
> >  > Ken
>
>


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

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

Re: How to index a large table?

[Ken Menzel]

Just curious Chris,  did you set MAX_ROWS option during table create
to a really large value?  If not alter the the table with this and try
again,  it affects the size of pointers used to create the index.
(See CREATE TABLE in the manual)

Hope it helps,
Ken
- Original Message -
From: "Chris Stoughton" <[EMAIL PROTECTED]>
To: "Jocelyn Fournier" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, October 22, 2002 4:11 PM
Subject: Re: How to index a large table?


> I have 760 GB free, not 375.
>
> bash-2.04$ df -h .
> FilesystemSize  Used Avail Use% Mounted on
> /export/data/dp20.a   1.0T  302G  760G  29% /data/dp20.a
> bash-2.04$
>
> The .MYD file is 134 GBytes
> bash-2.04$ ls -lh targetTsObj*
> -rw-rw1 mysqlmysql134G Oct 20 05:37
targetTsObj.MYD
> -rw-rw1 mysqlmysql5.0k Oct 21 11:38
targetTsObj.MYI
> -rw-rw1 mysqlmysql 32k Oct 18 21:05
targetTsObj.frm
>
> so while the "create index" command is running, after the #sql*
files
> are the same size as the targetTsObj.* files, we still have over 600
GB
> of disk space left.
>
>
> Jocelyn Fournier wrote:
>
> >Hi,
> >
> >In fact, I think you run out of disk space.
> >You MYD takes 135 GB.
> >You have 375 GB free
> >When MySQL try to add the index on your database, it copies the MYD
and frm
> >under a #sql* name.
> >So again 135 GB are eaten again.
> >So it remains 240 GB to build the index file, so it's possible you
run out
> >of free space during the MYI generation ? (what does df report ? )
> >
> >Regards,
> >  Jocelyn
> >- Original Message -
> >From: "Chris Stoughton" <[EMAIL PROTECTED]>
> >To: <[EMAIL PROTECTED]>
> >Sent: Tuesday, October 22, 2002 9:49 PM
> >Subject: How to index a large table?
> >
> >
> >
> >
> >>I have tried a few different variables to allow a "create index"
command
> >>to finish successfully.
> >>
> >>1.  I set tmpdir to be a file system with ample space
> >>2.  I increased tmp_table_size
> >>3. I increases myisam_sort_buffer_size to 100M
> >>
> >>Here are the sizes of the database files:
> >>
> >>bash-2.04$ ls -l  targetTsObj*
> >>-rw-rw1 mysqlmysql144173128578 Oct 20 05:37
> >>
> >>
> >targetTsObj.MYD
> >
> >
> >>-rw-rw1 mysqlmysql5120 Oct 21 11:38
targetTsObj.MYI
> >>-rw-rw1 mysqlmysql   32750 Oct 18 21:05
targetTsObj.frm
> >>
> >>
> >>I continue to get this error:
> >>
> >>
> >>Database changed
> >>mysql>  create index targetTsObjobjId on targetTsObj (objId);
> >>ERROR 1034: 136 when fixing table
> >>mysql>
> >>
> >>It takes 75 minutes, for this to happen.  During that time, it
creates a
> >>set of files called #sql*.MYD, .MYI, and .frm, and these grow
until they
> >>are identical in size to the targetTsObj.* files.
> >>
> >>Then, after several minutes of mysqld consuming 99% CPU time, it
ends
> >>with an error.
> >>
> >>1.  Is there a variable I should set in my.cnf?
> >>2. Should I be using innodb tables instead of myisam?
> >>
> >>Thanks.
> >>
> >>
> >>
> >>
>
>=
===
> >==
> >
> >
> >>For the record, here is what mysqladmin variables says:
> >>
> >>
> >>
>
>+-+--
---
>
>-
---
>
>-
---
> >+
> >
> >
> >>| Variable_name   |
> >>Value
> >>|
> >>
> >>
> >>
>
>+-+--
---
>
>-
---
>
>-
---
> >+
> >
> >
> >>| back_log|
> >>50
> >>|
> >>| basedir |
> >>/
> >>|
> >>| bdb_cache_size  |
> >>8388600
> >>|
> >>| bdb_log_buffer_size |
> >>262144
> >>|
> >>| bdb_home|
> >>/export/data/dp20.a/data/mysql/
> >>|
> >>| bdb_max_lock|
> >>1
> >>
> >>
> >
> >
> >
> >>|
> >>| bdb_logdir
> >>|
> >>|
> >>| bdb_shared_data |
> >>OFF
> >>|
> >>| bdb_tmpdir  |
> >>/export/data/dp20.a/tmp/
> >>|
> >>| bdb_version | Sleepycat Software: Berkeley
DB
> >>3.2.9a: (August 14,
> >>2002)
> >>|
> >>| binlog_cache_size   |
> >>32768
> >>|
> >>| character_set   |
> >>latin1
> >>|
> >>| character_sets  | latin1 big5 czech euc_kr
gb2312 gbk
> >>sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7
cp1251
> >>danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek
> >>win1250 croat cp1257 latin5 |
> >>| concurrent_insert   |
> >>ON
> >>|
> >>| connect_timeout |
> >>5
> >>|
> >>| datadir |
> >>/export/data/dp20.a/

Re: AUTO_INCREMENT max value...

[Ken Menzel]

But did you check if the file size is at 4Gig?  This will cause
strange things to happen, mysql does not gice a nice error for this
condition.  If the table was not created with MAX_ROWS option = to
some real large number like 5000, then you could be running
into a problem with the size of the MYD or MYI file.  You can use the
ALTER statement to add this option to the table.
Hope this helps,
Ken
- Original Message -
From: "Bill Hess" <[EMAIL PROTECTED]>
To: "Keith C. Ivey" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, October 08, 2002 4:52 PM
Subject: Re: AUTO_INCREMENT max value...


> Not really getting errors but getting warnings when inserting
records
> and having duplicate values since the primary key is the one that is
> auto incremented...
>
> Keith C. Ivey wrote:
> > On 8 Oct 2002, at 12:23, Bill Hess wrote:
> >
> >
> >>What is the maximum value of an AUTO_INCREMENT field?  I am
currently
> >>running into a maximum value of 296,995,724 on Solaris 8 using
MySQL
> >>3.23.42 - the field has been defined as an INT(11), which should
be able
> >>to handle a value bigger than what I am seeing - The DuBois MySQL
book
> >>states it should handle 2^31 which turns out to be 2,147,483,648
> >
> >
> > What is the error message you're getting?  Could it be that the
> > problem is the actually size of your data file (over 4 GB?) rather
> > than the value of the AUTO_INCREMENT?
> >
> > [Filter fodder: SQL]
> >
>
> --
>
> Bill Hess
> Technology Resource Group
>
>
>
> 
-
> 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 overload problem ?

[Ken Menzel]

Hi Cristiana,
   I am another FreeBSD user and I can tell you that FreeBSD 4.1.1
does have problems with threads.  I would very much recommend that you
update as there are also many security problems with that version if
you are running a server that is remotely available.   There were many
threads problems fixed in 4.3, the current version is 4.6.

Also this list is for the internal programming of how mysql works.
Please use [EMAIL PROTECTED] for questions that do not concern the
internal programming of MySQL.  I copied this response to the main
mailing list.

There is a really good tutorial on updating FreeBSD at
http://www.mostgraveconcern.com/freebsd/ See
http://www.mostgraveconcern.com/freebsd/cvsup.html

Good luck,
Ken
- Original Message -
From: "Cristiana Amza" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, September 27, 2002 11:27 AM
Subject: MySQL overload problem ?


>
> Hi.
>
> We have been using MySQL for a couple of years as a back-end for a
dynamic
> content server. We are using it in a research setting, so we are
generally
> not averse to changing the MySQL code if necessary.
> This message, however is intended only to better understand its
inner workings.
>
> We see the following behaviour that we cannot really explain.
>
> We have a workload where several clients are concurrently
> issuing moderate-to-heavy-weight queries to MySQL.
> In particular, there is a query that takes about 700 msec if run
alone.
> If a low number of clients (e.g. 4-5!) execute this query
concurrently
> throughput degrades (to a value lower than if these queries were run
> sequentially).
>
> The query is a (SELECT) 3-table join, and has an order by and group
by
> clause (and limit 50).
> The query accesses a small amount of data and is CPU bound
> (there is little disk activity with warm caches).
> Other (INSERT/UPDATE) queries may run concurrently on the accessed
tables,
> but the problem appears even if there are none.
> The key point is - with very few concurrent clients, performance
degrades
> quite rapidly, and we need to limit the total load (in terms of
total
> execution time of queries outstanding at MySQL, not number of
clients/queries).
>
> We are not using the MySQL query cache.
> Table cache settings are standard.
>
> This points to a very high context-switching overhead.
> We have read in the documentation that this may be due to the thread
> library overhead because threads may "acquire/release a mutex over a
> short critical region frequently".
> We are using FreeBSD 4.1.1 thus user-level threads, can't imagine
that context
> switching itself between (5!) threads could be that expensive,
unless it is
> for some reason very very frequent.
> Can you please elaborate on exactly the sequence of operations that
MySQL
> does that would produce this behaviour (possibly coupled with the
threading
> library's actions).
>
> We thank you in advance for shedding light on this. The problem has
> plagued us for some time.
>
> -Cristiana
>
> 
-
> 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]>
>
>


-
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: Encrypted MySQL passwords

[Ken Menzel]

Hi Andrew and Richard,
The password and connection info is scrambled up. You can look,  If
you want to check it out for your self look in the source code of
./mysql-3.23.XX/libmysql  search for the following functions.
especially in 'mysql_real_connect' function.   It is not the best
method,  but it is not clear text!

void hash_password(ulong *result, const char *password)
void make_scrambled_password(char *to,const char *password)
char *scramble(char *to,const char *message,const char *password,
   my_bool old_ver)

See ya
Ken

- Original Message -
From: "Andrew Braithwaite" <[EMAIL PROTECTED]>
To: "'Richard Fox'" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, September 26, 2002 1:49 PM
Subject: RE: Encrypted MySQL passwords


> Hi,
>
> My understanding is that it sends it plain text, then mysql encodes
it
> server-side & does the match.  There is client-server ssl planned
soon
> though...
>
> Cheers,
>
> Andrew
>
> -Original Message-
> From: Richard Fox [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, 26 September 2002 16:18
> To: [EMAIL PROTECTED]
> Subject: Encrypted MySQL passwords
>
>
>
> Hi,
>
> I am connecting to  a mysql server on a remote machine, and opened
up port
> 3306 for this purpose. But, I am concerned about sending a clear
text
> password, via the mysql_pconnect() call. My question is, what is the
> procedure for connecting to a remote server with an encrypted
password? Or,
> does mysql_pconnect handle this? THX, RIch
>
> sql
>
>
> 
-
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try:
http://lists.mysql.com/php/unsubscribe.php
>
> 
-
> 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: client library no longer LGPL licensing

[Ken Menzel]

> Does this also apply to non-open-source but free applications?  For
> example, we give away Escapade for free - always have, always will,
> at least for the minimal version of the product.  If we develop a
> non-free version of Escapade that uses mysqlclient, what happens
> then?  We also distribute statically-linked and dynamically-linked
> versions of the product, and I would be interested in knowing
MySQL's
> position on this issue in regards to licensing.

Excellent question and our point also,   we give away our non-open
source application as well and do not charge a per user/copy/customer
charge for the client or restrict copies.  We DO have licensed servers
and and we DO charge for using our servers.  I have been contacted be
e-mail by MySQL licensing and am looking forward to having my
questions answered.  We are still using MySQL 3 so I think we are OK.
But was planning on going to MySQL 4 soon.  I wonder how this affect
the Mascon people (Neat little app also).

Anyway I can understand MySQL's need to grow and would hope they
continue to listen to us MySQL developers as they always have.  (And I
think they will).  But I do feel this is an important change for
anyone working with a windows client for MySQL and want to be able to
earn dinner! And I would hope for open discussions.  We all want MySQL
to succeed as a DB and as a company.

Ken


> - --
> Ed Carp, N7EKG  http://www.pobox.com/~erc
> 214/986-5870
> Licensed Texas Peace Officer
> Computer Crime Investigation Consultant
>
> Director, Software Development
> Escapade Server-Side Scripting Engine Development Team
> Pensacola - Dallas - London - Dresden
> http://www.squishedmosquito.com
>
> "The whole aim of practical politics is to keep the populace
> alarmed-- and thus clamorous to be let to safety-- by menacing it
> with an endless series of hobgoblins, all of them imaginary."
> - -- H. L. Mencken
>
> -BEGIN PGP SIGNATURE-
> Version: PGPfreeware 6.5.8 for non-commercial use

>
> iQA/AwUBPZI6FkbhwAGg7YRjEQI/6gCfcnE/vxjAdVXrUs3LTuNUafxRb9sAn3kT
> A86wjGjw2TYgqhPfPi9V6bna
> =P6pl
> -END PGP SIGNATURE-
>
>


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

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

client library no longer LGPL licensing

[Ken Menzel]

Hi Everyone,
   I don't know if it makes a difference to anyone, but mysql client
libraries are no longer LGPL. see
http://www.mysql.com/doc/en/Copyright.html. (original licenses can be
found in old source tar files documentation). I think this means, if
you have an application that uses mysql as one of your databases don't
include the library it unless your app is GPL also (or MySQL gives
permission or changes back to LGPL).  I guess you could put a link to
the web site so users could download the package.  Is that
inconvienient?

I don't know if this would affect anyone, anyway,  but I found it
interesting that the licensing quietly changed and I wondered if
anyone else cared or if this change cleared up some previous confusion
and is a good thing?

Ken
---------
Ken Menzel  ICQ# 9325188
www.icarz.com  [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: Moving Mysql

[Ken Menzel]

Hello,
   First shutdown mysql.  The move or copy databses to new directory.
Restart server using --datadir=/my/path/for/data  or place info in
my.cnf file.  (See examples provided with MySQL code).

Ken
- Original Message -
From: "Soheil Shaghaghi" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, September 12, 2002 12:41 PM
Subject: Moving Mysql


> Hello everyone,
> I have my MySQL database n the var partition of a FreeBSD server.
> The problem is that the partition is almost full, and now I can't
start the
> SQL server!
>
> I am thinking of moving the MySQL data directory to another
partition, but I
> don't know how I should do this.
> Can anyone please tell me how, and also do I have to change any
settings
> after moving the data directory?
>
> Thanks so much.
>
>
>
> 
-
> 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 Monitoring Tool

[Ken Menzel]

Hi Tom,
   If you are not happy with netsaint you might want to try
http://jfontain.free.fr/moodss/index.html .
I do not use this,  however I understand alot of work has gone into
it.

I am curious as to why you say:

> We are presently using a program known as NetSaint to monitor our
services
> but it sometimes doesn't seem to be 100% reliable at detecting a
MySQL
> error.

Because we use netsaint and have found it very reliable,  (but maybe
it works better on FreeBSD :)

MySQL OK 09-10-2002 10:32:15 49d 11h 10m 18s 1/3 Version
3.23.51-log -- 102 Threads
Uptime 39 days 12 hours 53 min 27 sec

Are you using the MySQL plugin.  What version of netsaint are you
using?  The latest netsaint is now called Nagios.  What errors are you
trying to detect?  It is only meant to monitor if MySQL is up/down.
Errors do not get tracked with netsaint, only up/down time (try Moodss
for charts of errors!).

This along with load monitoring using the NRPE utility and MRTG to
chart query usage gives us some great information.

Hope some of this helps you.

Best of luck,
Ken

- Original Message -
From: "Tom Freeman" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, September 10, 2002 6:03 AM
Subject: MySQL Monitoring Tool


> Hi,
> I hope this question hasn't been asked loads of times before but I
can't see
> any reference to it in the documentation.
> Basically I need a way to monitor MySQL to ensure it hasn't gone
down. We
> are using MySQL as the backend of some important sites and need to
ensure
> that if it does crash for whatever reason, an alert (email and SMS)
is sent
> out to an engineer to resolve the problem.
>
> We are presently using a program known as NetSaint to monitor our
services
> but it sometimes doesn't seem to be 100% reliable at detecting a
MySQL
> error.
>
> Anyway, can anyone tell me a better way to monitor MySQL so that if
it has
> any problems an oncall engineer can respond quickly. I'm sure this
must be a
> common problem so there must be something out there already.
>
> Many thanks,
> Tom
>
>
>
> 
-
> 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: 2**32(4GB) problem on FreeBSD

[Ken Menzel]

Hi JC,
  From http://www.mysql.com/doc/en/Table_size.html  (I searched on
size)

If you need bigger tables than 4G (and your operating system supports
this), you should set the AVG_ROW_LENGTH and MAX_ROWS parameter when
you create your table. See section 6.5.3 CREATE TABLE Syntax. You can
also set these later with ALTER TABLE. See section 6.5.4 ALTER TABLE
Syntax.

FreeBSD supports terabyte files/filesystems, use the MAX_ROWS
parameter and set it to some really large value (anything over 2^32
like 1000).  This ALTER statement will cause your indexes to
be rebuilt using long pointers.  You can also create the table this
way using MAX_ROWS option in create statement.

You can verify your changes by running SHOW TABLE STATUS before the
change then again after the change,  watching Max_data_length.

Hope this helps,
Ken


- Original Message -
From: "JC" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, September 04, 2002 1:45 PM
Subject: 2**32(4GB) problem on FreeBSD


> Hello Everyone,
>
> I have a strange situation in that MySQL 3.23.49 on FreeBSD 4.6
> installed from the ports collection seems to be having an issue with
4GB
> files. When I issue a checktable   I get back the message of
> Datafile is almost full, 4026103700 of 4294967294 used.
> I thought this odd because I just saw a posting from Jeremy in Apr
anonouncing
> that they have a 9GB MYD, but that was Linux.
>
> Is anyone else having this kind of a problem?
> Is there any way to dump the compile time options?
>
> Thanks,
>
> JC
>
> 
-
> 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: Innodb too slow

[Ken Menzel]

Hi Alexander,
  I believe that the the 'count()' function works differently under
INNODB type tables.  With MyISAM count(*) is stored in a 'table
status' area,  but INNODB must scan the tables and count the rows
(very slow).  All other types of queries should perform much better
for you, but 'count(*)' is not impelemented the same way.  Hope this
helps,

Ken
- Original Message -
From: "Varshavchick Alexander" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, September 03, 2002 3:56 AM
Subject: Innodb too slow


> Hi,
>
> why innodb queries work MUCH slower (100 times) than if the table
was of
> myisam type? It's mysql 4.0.3 on FreeBSD 4.5 server. The innodb
monitor
> outputs many messages like the following, why are they there and
what do
> they mean?
>
> Purge done for trx's n:o < 0 782 undo n:o < 0 0
> Total number of lock structs in row lock hash table 0
> LIST OF TRANSACTIONS FOR EACH SESSION:
> ---TRANSACTION 0 3811, ACTIVE 0 sec, OS thread id 10250 fetching
rows
> MySQL thread id 2, query id 3044 localhost handy Sending data
> SELECT count(*) from stat
> Trx read view will not see trx with id >= 0 3812, sees < 0 3807
> ---TRANSACTION 0 3810, ACTIVE 1 sec, OS thread id 12300 fetching
rows
> MySQL thread id 4, query id 3043 localhost handy Sending data
> SELECT count(*) from stat
> Trx read view will not see trx with id >= 0 3811, sees < 0 3806
> ---TRANSACTION 0 3809, ACTIVE 1 sec, OS thread id 13325 fetching
rows
> MySQL thread id 5, query id 3042 localhost handy Sending data
> SELECT count(*) from stat
> Trx read view will not see trx with id >= 0 3811, sees < 0 3806
> ---TRANSACTION 0 3808, ACTIVE 1 sec, OS thread id 14350 fetching
rows
> MySQL thread id 6, query id 3041 localhost handy Sending data
> SELECT count(*) from stat
> Trx read view will not see trx with id >= 0 3809, sees < 0 3804
> ---TRANSACTION 0 3807, ACTIVE 1 sec, OS thread id 11275 fetching
rows
> MySQL thread id 3, query id 3040 localhost handy Sending data
> SELECT count(*) from stat
> Trx read view will not see trx with id >= 0 3808, sees < 0 3803
>
> Any help will be appreciated, thanks!
>
> 
> Alexander Varshavchick, Metrocom Joint Stock Company
> Phone: (812)118-3322, 118-3115(fax)
>
>
> 
-
> 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 3.23 problems on BSDI 4.1

[Ken Menzel]

Hi John,
   Yes, BSDi 4.1 does indeed have serious threading problems.  There was a
patch issued that would stop the crashing, but I don't remeber the number.
I am now installing 4.3.  If you are running a server that will be available
on the internet I highly advise upgrading to 4.2 or 4.3 with latest patches
for openssl issues.  I believe 4.1 also haad a telnet issue.  Also we are
switching over most of our servers to freebsd.  The latest version is quite
good and 5.0 (the next version available in preview) looks even better.

Best of luck,
Ken

"John R Levine" <[EMAIL PROTECTED]> wrote in message
ajgs9m$i7f$[EMAIL PROTECTED]">news:ajgs9m$i7f$[EMAIL PROTECTED]...
> I downloaded and built mysql-3.23.49 on my BSDI 4.1 system.  It configured
> and built fine, but it hangs whenever I do more than the most trivial
> operations.
>
> It is my dim recollection that there have been problems with the thread
> libraries in the past.  Does this ring a bell?
>
> (I can't use the prebuilt 4.2 binaries, because I'm not running 4.2 yet.)
>
> --
> John R. Levine, IECC, POB 727, Trumansburg NY 14886 +1 607 387 6869
> [EMAIL PROTECTED], Village Trustee and Sewer Commissioner,
http://iecc.com/johnl,
> Member, Provisional board, Coalition Against Unsolicited Commercial E-mail
>
> -
> 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: Multi-terabyte mysql databases?

[Ken Menzel]

Hi Andrew,
  We have a catalog database with 147 Million records, but size is
only about 8.5G.  It is a read only application and is extremely fast
under mysql.  Only have about 200 users hitting it, as it is a special
database for a point of sale application.
I think the biggest problem you will have is getting a single
filesystem to be several terabytes!  I don't know of anyone with
anything that large.

See also: http://netgraft.com/~mbac/research/mysqlmyths.html
Testimonials at bottom may be of some interest.

Best of luck,
Ken
- Original Message -
From: "Andrew Bryant" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, July 30, 2002 5:04 AM
Subject: Multi-terabyte mysql databases?


>
>
> How big have people pushed MySQL databases. Has anyone attempted
> terabyte size databases, especially large database that are not just
> read-only resources. Databases with some OLTP activity.
>
> Andy Bryant
> on behalf of Martin Widlake
> Oracle DBAs with a little MySQL experience
>
>
>
>
>
>
> 
-
> 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 FreeBSD LinuxThreads

[Ken Menzel]

Hi Oleg,
  I have similar problems on FreeBSD with one or two slow queries
hogging the CPU. I am running native threads.  I am looking at a
parameter in  the kernel (/usr/src/sys/i386/conf/LINT).  It is the HZ
parameter and has to do with polling frequency.  The default is 100,
I am experimenting with values from 1000 to 5000.  You might give this
a try.  You will need to build a new kernel.  I am not sure this will
have any effect on MySQL.  This is only a suggestion.

Best of Luck,
Ken
- Original Message -
From: "Oleg Borodkin" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Monday, July 15, 2002 3:24 PM
Subject: MySQL FreeBSD LinuxThreads


> Hello,
>
> I have noticed that MySQL server almost hangs up the system while
> processing a big table (perl script using fetchrow).
> I didn't experienced this before - may be it's because of
LinuxThreads?
>
> MySQL launches 38 running processes, but shows only 10 mysql query
> threads (they are all sleeping except 2 or 3).
>
> Thanking you in advance,
> Oleg Borodkin
>
>
> 
-
> 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! MySQL overloading my system

[Ken Menzel]

Hi,
  As a first step I would find out what mysql is doing,  Use either
'show processlist' or using mysqladmin 'mysqladmin proc'.  Or even
better install mytop (cd /usr/ports/databases/mytop; make install; ).
This program sorts the output for you.  There does seem to be a
sometimes bug with mysql that 'may' be related to the thread cache.
If you have increased the thread cahce (which is mostly not needed)
Try reducing the thread cache to 2, but make sure that it is not just
a slow query causing the problem.

Hope this helps,
Ken
- Original Message -
From: "Odhiambo Washington" <[EMAIL PROTECTED]>
To: "mySQL" <[EMAIL PROTECTED]>
Cc: "FBSD-Q" <[EMAIL PROTECTED]>
Sent: Monday, July 01, 2002 1:17 PM
Subject: Help! MySQL overloading my system


>
> Alors!
>
> I run MySQL-3.23.41 on FreeBSD-4.5-STABLE but everytime I get my
system
> slowing down and on checking with `top` I see MySQL as the culprit:
>
> 
> last pid: 29586;  load averages:  1.41,  1.45,  1.48
up 28+10:55:17
> 20:10:59
> 90 processes:  3 running, 83 sleeping, 1 stopped, 3 zombie
> CPU states: 16.7% user,  0.0% nice, 51.4% system,  0.4% interrupt,
31.5% idle
> Mem: 249M Active, 36M Inact, 71M Wired, 16M Cache, 48M Buf, 1960K
Free
> Swap: 1374M Total, 58M Used, 1316M Free, 4% Inuse
>
>   PID USERNAME  PRI NICE  SIZERES STATE  C   TIME   WCPU
CPU COMMAND
> 28687 mysql  63   0 40604K  5940K RUN0  27.8H 88.23%
88.23% mysqld
> 29489 wash   10   0 12944K 12420K nanslp 0   0:04 23.89%
11.43% perl
> 29424 wash   10   0 12904K 12376K nanslp 1   0:04 10.06%
6.54% perl
> 29382 root   30   0  2072K  1200K CPU0   0   0:02  3.50%
2.83% top
> 29511 exim2   0  3692K  2240K select 1   0:01  3.84%
1.51% exim
> 29576 exim2   0  3700K  2240K select 0   0:00  9.74%
0.93% exim
> 41215 www18   0 41056K 24592K lockf  1   0:40  0.29%
0.29% httpd
> 41216 www18   0 39980K 24316K lockf  1   0:23  0.15%
0.15% httpd
>   684 root2   0  1492K   840K select 1 110:49  0.10%
0.10% tpop3d
>
>
> Could someone more knowledgeable point me in the right direction
towards
> isolating what could be causing this load. I have several databases
on this box,
> TWO of which are very busy as the SMTP/POP3 daemons both interact
with it as
> this is the main server.
>
>
> Thanking you in advance.
>
>
>
> -Wash
>
> --
> Odhiambo Washington  <[EMAIL PROTECTED]>"The box said 'Requires
> Wananchi Online Ltd.  www.wananchi.com  Windows 95, NT, or
better,'
> Tel: 254 2 313985-9   Fax: 254 2 313922 so I installed FreeBSD."
> GSM: 254 72 743 223   GSM: 254 733 744 121  This sig is McQ!  :-)
>
>
> Westheimer's Discovery:
> A couple of months in the laboratory can frequently save a
> couple of hours in the library.
>
> 
-
> 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: innodb_buffer_pool_size too large

[Ken Menzel]

Hi Iago,
   This is really a freebsd related question and not MySQL.  I believe
that the archives contain some info.  Anyway you need to build a
custom kernel for freebsd see /usr/src/sys/i386/conf/LINT.
look for this section:
# Certain applications can grow to be larger than the 128M limit
# that FreeBSD initially imposes.  Below are some options to
# allow that limit to grow to 256MB, and can be increased further
# with changing the parameters.  MAXDSIZ is the maximum that the
# limit can be set to, and the DFLDSIZ is the default value for
# the limit.  MAXSSIZ is the maximum that the stack limit can be
# set to.  You might want to set the default lower than the max,
# and explicitly set the maximum with a shell command for processes
# that regularly exceed the limit like INND.
#
options MAXDSIZ="(256*1024*1024)"
options MAXSSIZ="(256*1024*1024)"
options DFLDSIZ="(256*1024*1024)"

Add this or use this to create your own kernel.  Don't go above
2047*1024*1024 or any threaded program will crash on startup.

Hope this helps.
Ken



- Original Message -
From: "Iago Sineiro" <[EMAIL PROTECTED]>
To: "MySql Mail List" <[EMAIL PROTECTED]>
Sent: Thursday, May 30, 2002 3:49 AM
Subject: innodb_buffer_pool_size too large


> Hi all.
>
> I'm testing configuration of InnoDB tables in MySQL database server.
The
> documentation of MySQL says
> that a good configuration for innodb_buffer_pool_size (the buffer)
is 80% of
> physical memory if the computer is a dedicated MySQL server. I have
1GB of
> memory, then I set innodb_buffer_pool_size = 740M. When I restarted
the
> MySQL server it fails.
>
> This is the log of mysql server, indicating that FreeBSD can't
allocate this
> memory, that I need to recompile the operating
> system with that option.
>
> 020529 18:31:52  mysqld started
> InnoDB: Fatal error: cannot allocate 738213888 bytes of
> InnoDB: memory with malloc! Total allocated memory
> InnoDB: by InnoDB 21180100 bytes. Operating system errno: 12
> InnoDB: Cannot continue operation!
> InnoDB: Check if you should increase the swap file or
> InnoDB: ulimits of your operating system.
> InnoDB: On FreeBSD check you have compiled the OS with
> InnoDB: a big enough maximum process size.
> 020529 18:31:52  mysqld ended
>
> Anybody knows how to change the maximum process size?
>
> NOTE: I'm using FreeBSD 4.5 in a 2 Pentium III 1000 Ghz with 1G.
>
> Thanks in advance.
>
> Iago.
>
>
>
> 
-
> 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