host table in mysql database

[Guy Waugh]

Hi there,

I'm cleaning up MySQL permissions on one of my servers, and I see that in the 
mysql.host table, there are 8 rows. All 8 rows contain specific hosts in the 'Host' 
column (i.e. no wildcards) and specific databases in the 'Db' column. Further, all 
rows in all privilege columns in the host table contain 'Y'.

I see from reading Paul DuBois' MySQL book that MySQL ANDs privileges in the host 
table with privileges in the db table, allowing administrators to revoke particular 
privileges for users connecting from particular hosts.

Can anyone tell me, then, if the host table is currently having any effect on 
permissions? I'm thinking not, as a logical AND of the host table 'Y' and the db table 
'Y' or 'N' will result in the privilege recorded in the db table.

Also, Paul's book says that the host table is only checked if the 'Host' column in the 
relevant client's row in the db table is blank, and all rows in the db table contain 
values in the 'Host' column. So on that fact alone, it looks like I can delete all 
rows in the host table without permissions being affected...?

Just wanted some opinion/advice before I potentially get myself into trouble ;-)

Thanks in advance,
Guy.


-
Before posting, please check:
   http://www.mysql.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: Re[4]: mysql 4.0.8- crash on TCP connection

[Johannes Ullrich]

On Thu, 9 Jan 2003 22:56:04 +0200
"Gelu Gogancea" <[EMAIL PROTECTED]> wrote:

> > All this is very interesting, BUT i have two binary builds (4.0.7 &
> 4.0.8),
> Ha,Hayou  are gentle.
> 
> > (load avg 10-60 Query/sec), and 4.0.8 crash (in some
> > hardware/software) after 2 seconds work :(
> 

Did you see any relationship with 'replication'? I just downloaded
4.0.8. All it did was act as a slave. It crashed after a large
'load table' from the master and now refuses to start. Looks like
it crashes as it read the relay-log-info file or just after it does
so.

at least thats my latest theory after doing more backtrace resolving,
stracing and experimenting.

did submit one or two bug reports about this.


-- 

[EMAIL PROTECTED] Collaborative Intrusion Detection
 join http://www.dshield.org

-
Before posting, please check:
   http://www.mysql.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 Database Design

[Michael T. Babcock]

Colaluca, Brian wrote:


I have come to a brick wall on one facet of my design, however.  I've come
to understand that having a lot of NULLs in your database may be a sign of a
poor design.  And yet I'm having a problem reconciling this with the wildly
un-uniform world of wines from around the world.  For instance, I would like
to have a table called "GrapeVariety," and have the variety_id be a primary
key.  Another table would be "Wine."  And yet, one wine could have one type
of grape or more. 
 


Just an idea ... to get your head spinning (and some sample queries):

Wine
-
ID int unsigned not null auto_increment primary key,
Name ...
Winery ...

Grapes
-
ID int unsigned not null auto_increment primary key,
Name ...
Vineyard? ...

GrapesInWine
-
WineID int unsigned not null,
GrapesID int unsigned not null,
Percentage int unsigned not null

... where Percentage is between 0 and 100.

Then you can, to insert a wine named "Foo" with 50% of each "Grape1" and 
"Grape2":

INSERT INTO Wine (Name) VALUES ("Foo");
SELECT @WinesID := last_insert_id();# I'm using server 
variables here for the sake of demo ...
INSERT INTO Grapes (Name) VALUES ("Grape1");
SELECT @GrapesID := last_insert_id();
INSERT INTO GrapesInWine (WineID, GrapesID, Percent) VALUES (@WinesID, 
@GrapesID, 50);
INSERT INTO Grapes (Name) VALUES ("Grape2");
SELECT @GrapesID := last_insert_id();
INSERT INTO GrapesInWine (WineID, GrapesID, Percent) VALUES (@WineID, 
@GrapesID, 50);

Then, to find out what's in the wine named "Foo":

SELECT * FROM Grapes
   LEFT JOIN GrapesInWine
  ON Grapes.ID = GrapesID
   LEFT JOIN Wine
  ON WinesID = Wine.ID
   WHERE Wine.Name = "Foo";

Or, to find the amounts of "Grape1" in all wines:

SELECT * FROM Wine
   LEFT JOIN GrapesInWine
  ON WineID = Wine.ID
   LEFT JOIN Grapes
  ON Grapes.ID = GrapesID
   WHERE Grapes.Name = "Grape1";

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



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

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

RE: MySQL Database Design

[Jennifer Goodie]

Blend will be a cross reference with a one to many relationship

This is very simplified but an example of your data could be:

Select * from Wine;
++--+
| WineID | WineName |
++--+
| 1  | XYZ  |
++--+

Select * from Grape;
+-+---+
| GrapeID | GrapeName |
+-+---+
| 1   | GrapeA|
| 2   | GrapeB|
+-+---+

Select * from Blend;
+-++-++
| BlendID | WineID | GrapeID | Percentage |
+-++-++
| 1   | 1  | 1   | 80 |
| 2   | 1  | 2   | 20 |
+-++-++


This is obviuously very simplified, just trying to give you a quick response
so you can move ahead in your development without being stuck on this.

There might be some disagreement on the naming conventions I have
illustrated.  Use what you like best.


-Original Message-
From: Colaluca, Brian [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 09, 2003 12:56 PM
To: [EMAIL PROTECTED]
Subject: MySQL Database Design

For instance, let's say that wine XYZ has 80% GrapeA, and 20% GrapeB.  Since
my grape variety would presumably be a foreign key in the Wine table, how
could I specify a certain *percentage* of a foreign key?  I've tried hashing
this out in numerous ways, including the addition of a "Blend" table with
multiple primary keys, but anyway I slice it, there will still be an
abundance of NULLs.  For while the majority of wines may only contain one
grape, there could be wines that have up to 5 or 6 in varying percentages.


-
Before posting, please check:
   http://www.mysql.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: remote connect crash

[Christopher E. Brown]

On Thu, 9 Jan 2003, Dmitry V. Sokolov wrote:

> Good day,
> could you help me to solve this problem?
>
> MySQL server segmentation faults when remote mysql client
> tries to connect on source and binary distributions. Local
> client connect does not cause any problems whatsoever.


The server dies when the connecting hosts IP fails a reverse lookup.
According to a note I received this morning it was fixed last night in
the source tree, and 4.0.9 is being built for release.

-- 
I route, therefore you are.



-
Before posting, please check:
   http://www.mysql.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: Version 4 "safe" to use?

[Jeremy Zawodny]

On Thu, Jan 09, 2003 at 01:51:20PM -0300, Maximo Migliari wrote:
>
> What were the main benefits that you noticed straight away before
> adapting your code to use the new features that MySQL 4 offers?

Replication is faster in 4.0.  That's a big win for us, as we use
replication pretty heavily.

> Does the query cache make a big difference?  Do you have any
> benchmarks, even off your head?

The query cache helps a lot.  I ran some stats a while back.  We have
some queries that are as optimized as they can get.  Accessing this
page:

  http://biz.yahoo.com/n/y/yhoo.html

uses the quey to fetch headlines.

Without the query cache, that query can take anywhere from 50ms to
200ms to return (depends on the specific ticker and a few other
factors).  With the query cache, it returns in 4ms-6ms when the data
is cached.  For frequently accessed tickers, it makes a big
difference.

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

MySQL 3.23.51: up 25 days, processed 866,323,874 queries (388/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

RE: May I raise a question?

[seed . chan]

Thanks, Gerald,

I'm trying to install the MySQL. Apache & Php in Windows 2000 Professional.  A 
book telling me that I should install them in the above sequence.  Actually the 
error message is before the installation of Apache, is it the root cause?  
Should I try again after I start the server?

Best rgds,

Seed Chan
System Services Management
Branch Services
* 2962 7431
*  29173503 / 29173504
* [EMAIL PROTECTED]


-Original Message-
From: gerald.clark [SMTP:[EMAIL PROTECTED]]
Sent: Thursday, January 09, 2003 10:20 PM
To: Chan, Seed
Cc: gerald.clark; mysql
Subject: Re: May I raise a question?

What OS are you running?

Did you start the server?

What is in the error logs?

[EMAIL PROTECTED] wrote:

>Dear Editor,
>
>After installed the MySQL version 3.23.53, I tried to test the MySQL by
>typing the following command, relevant error message appears, please advise
>how to fix.
>
>Command: Display Error Message:
>
>mysqlshowmysqlshow: Can't connect to MySQL server on
>'localhost' (10061)
>
>mysqladmin CREATE test   mysqladmin: connect to server at 'localhost' failed
> error: 'Can't connect to MySQL server on
>'localhost' (10061)'
> Check that mysqld is running on localhost and that
>the port is 3306.
> You can check this by doing 'telnet localhost 3306'
>
>mysql test   ERROR 2003: Can't connect to MySQL server on
>'localhost' (10061)
>
>telnet localhost 3306Connecting To localhost...Could not open a
>connection to host on port 3306 : Connect failed
>
>Best rgds,
>
>Seed Chan
>
>
>
>Seed Chan
>System Services Management
>Branch Services
>* 2962 7431
>*  29173503 / 29173504
>* [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

Transaction and Row Locking Feature

[Robert Tam]

Hello,

I am a new user to MySQL.  I need innoDB's transaction and row locking
feature in MySQL.  Could someone clarify whether the latest 3.23.54a of
MySQL fully supports innoDB transaction and row locking or do I have to use
beta/gamma version of MySQL 4.0.x.  Thanks in advance.

R.T.


-
Before posting, please check:
   http://www.mysql.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: Case Problems...

[Nick Stuart]

Ah, I see. Was unaware of this new feature, and thanks for the
explanation.

-Nick

On Thu, 2003-01-09 at 17:20, Stefan Hinz, iConnect (Berlin) wrote:
> Nick,
> 
> > CREATE DATABASE LookAtMe;
> > the database is created but as:
> > lookatme
> 
> This is not a bug, but a feature. By default, MySQL 4.0.x has
> lower_case_table_names set to 1. If you want to change this behaviour,
> put this in the [mysqld] section of your c:\my.cnf or c:\winnt\my.ini
> file, and restart the MySQL server:
> 
>  set-variable = lower_case_table_names=0
> 
> Regards,
> --
>   Stefan Hinz <[EMAIL PROTECTED]>
>   Geschäftsführer / CEO iConnect GmbH 
>   Heesestr. 6, 12169 Berlin (Germany)
>   Tel: +49 30 7970948-0  Fax: +49 30 7970948-3
> 
> - Original Message -
> From: "Nick Stuart" <[EMAIL PROTECTED]>
> To: "MySQL" <[EMAIL PROTECTED]>
> Sent: Wednesday, January 08, 2003 7:52 PM
> Subject: Case Problems...
> 
> 
> > I seem to have found a bug with 4.0.7. I just want to make sure the
> > issue hasn't been covered before I submit a report.
> > I have 4.0.7 installed on windows 2000 server with all the service
> packs
> > and stuff. When I connect to the database through a Linux client
> > (haven't tried it on the box itself) and issue:
> > CREATE DATABASE LookAtMe;
> > the database is created but as:
> > lookatme
> > with no caps.
> > Anybody run into this before? I know Winblow$ isn't case sensitive and
> > all but it should still create the database/folder as I type it.
> > --
> >
> > -Nick Stuart
> >
> > USM Computer Science Major
> > Visit us at http://csforum.newtsplace.com
> > (run with LAMP)
> >
> > Filter Fodder: mysql, sql, queries, why isn't CREATE or DATABASE in
> the
> > god damn list! =D
> >
> > -
> > Before posting, please check:
> >http://www.mysql.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: converting text to hypertext

[Michael T. Babcock]

Rick Tucker wrote:


This is the code I'm using.  I'm pretty new to PHP, so there may be a simple
solution within PHP of which I'm unaware.  I just thought it could be done
from the MySQL side of things.



I think the point is more that there's no reason to have MySQL do it at 
all since the logic is specific to what you're doing.

$resultID = mysql_query("SELECT * FROM ports", $linkID);

print "Port #";
print "TransportApplication
align=center>RFC/Vendor's URL/MS KB article";

while ($row = mysql_fetch_row($resultID))
{
print "";
foreach ($row as $field)
{
print "$field";
}
print "";
}
print "";
mysql_close ($linkID);
 


If these fields are intended to be URIs, try something like (I didn't 
check my PHP function names; look them up first):

function LinkURI($URI)
{
   $HREF = urlencode($URI);
   $Text = htmlentities($URI);
   return "$Text";
}

print "".LinkURI($field)."";

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



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

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

mysql monitoring

[Michael Weiner]

There are a few solutions out there for you. I recommend you check out
RRDTOOL, 
and associated packages at 
http://people.ee.ethgz.ch/~oetiker/webtools/rrdtool/ and also look for a
took called cactus, it absolutely rules. THere are others, but rrdtool,
has a myriad of contributed packages it can work with to monitor email
servers, bandwidth, and just about anything you can think of.

Enjoy
Michael Weiner

sql,query,queries,smallint




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

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

Re: Possable bug, remote mysqld crash and DoS

[Mark Matthews]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Christopher E. Brown wrote:

Description:
	MySQL 4.0.8, both compiled my me and the official release version
crashes whenever receiving a network connection from a system without a DNS
entry.  Connecting from a system that resolves on reverse (eithor from DNS
or a local hosts file entry) works find.  This system is a Slackware 8.1
install with all currect updates.  I do not know if this is a mysqld
internal thing or some interaction with the system resolver in glibc 2.2.5,
as unfort even a staticly compiled glibc binary uses the system resolver.

	This of course concerns me, there is a large potential for remote
DoS here.


How-To-Repeat:
	Install 4.0.8, run the install db script and fire it up.  Attempt to
connect from a host that will not reverse resolve.  Even a telnet to port
3306 crashed the daemon.  The dump from mysqld is included at the bottom of
the message.


This was fixed in the source tree last night, and will be in the 4.0.9 
release, which is being built as this is being written, and released ASAP.

	-Mark

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

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

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

iD8DBQE+HcfitvXNTca6JD8RAt0RAKCxwM9hsmBRjmk3rQLXciv20QU1MACfUiZQ
AYzgNkfelbpRMjth7dXKwgM=
=xGQF
-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

Starting MySQL

[cam vong]

I'm currently using Red Hat 7.1.  I have loaded the apache, php, and mysql 
rpm.  I seem to be having trouble starting MySQL.  It gives me the following 
error:  Can not connect to localhost MySQL server through socket 
/var/lib/mysql/mysql.sock (111).  Can some help me, please?





_
Protect your PC - get McAfee.com VirusScan Online 
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963


-
Before posting, please check:
  http://www.mysql.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:ÄúºÃ!


±±¾©Èݳ½×¯Ô°ÆÏÌѾÆÓÐÏÞ¹«Ë¾ÀÏ°å¶Îº£ÈÝÏÈÉú£ºÄúÊÇÃÀ¹ú»ªÒá´óÀϰ壬ÄúÊÇÉí¼Û¹ýÒڵĴ󸻺À£¬ÄúÊDz»»áÔÚºõ¶ÔÄúÀ´ËµÇøÇø¼¸ÍòÔªµÄÇ·¿î
µÄ£»È»¶ø£¬ÕâЩǮ¶ÔÓÚÑÛÏÂΪÖβ¡ÒÑÇîÀ§Áʵ¹ÓÖÉí»¼°©Ö¢µÄÑîÏÈÉúËûÃÇÀ´Ëµ£¬¼ÈÊÇËûÃǵÄѪº¹Ç®£¬¸üÊÇËûÃǵľÈÃüÇ®¡£Äú²»Êdz£Ëµ£º×öÉúÒâÒ»¶¨
ÒªÏÈѧ»á×öÈËÂï¡£ÇëÇóÄú·¢·¢´È±¯£¬·¢·¢ÉÆÐÄ£¬¸Ï¿ì°ÑÇ·ÑîÏÈÉúËûÃǵľÈÃüÇ®»¹¸øËûÃÇ°É£¡¸Ðл£¡£¡£¡
 

ÇëÇó±±¾©ÊÐίÊé¼Ç¡¢Êг¤Áõä¿°ïÑîÏÈÉúËûÃÇÌֻػ³À´Èݳ½ÆÏÌѾÆÓÐÏÞ¹«Ë¾±±¾©×ܲ¿ÀÏ°å¶Îº£ÈÝÏÈÉúµÄÇ·¿î£¬ËûÃǵÄѪº¹Ç®£¬ËûÃǵľÈÃüÇ®¡£
·Ç³£¸Ðл£¡£¡£¡

ÇëÇóÕżҿÚÊÐίÊé¼ÇÑîµÂÇì¡¢Êг¤Õű¦Òå°ïÑîÏÈÉúËûÃÇÌÖ»ØÕżҿÚÊл³À´Èݳ½ÆÏÌѾÆÓÐÏÞ¹«Ë¾ÀÏ°å¶Îº£ÈÝÏÈÉúµÄÇ·¿î£¬ËûÃǵÄѪº¹Ç®£¬ËûÃÇ
µÄ¾ÈÃüÇ®¡£·Ç³£¸Ðл£¡£¡£¡

ÇëÇó¸÷ÐÂÎÅýÌå¼°ÍøÂç¸ßÊÖÃÇ°ïæºôÓõ£»ÇëÇóÓÐÄÜÁ¦µÄÍøÃñÃÇÄܽ«´ËÐÅϢת¸æÕżҿÚÊÐίÊé¼ÇÑîµÂÇì¡¢Êг¤Õű¦ÒåºÍ±±¾©ÊÐίÊé¼Ç¡¢Êг¤Áõ
俵ÈÓйز¿ÃÅÁìµ¼»òÖÐÑë¸ü¸ß¼¶Áìµ¼¡£ÖйúÓоä¹Å»°£ºÉ±È˳¥Ãü£¬Ç·Õ®»¹Ç®£¬Ìì¾­µØÒ壡Ȼ¶ø½ñÌ죺Ïñ¶ÎÏÈÉúÕâÑùÇ·Ç®µÄÈËÈ´¶¼³ÉÁË´óÒ¯£¡£¡£¡
ÑîÏÈÉúËûÃÇÒ²ÕæÊDZ»±ÆÎÞÄΣ¬ÎªµÄÊÇÌÖÕ®Öβ¡¾ÈÈË£¬Ï£ÍûÄܹ»µÃµ½ÓйØÁìµ¼¼°ÖÚÍøÃñµÄÉùÔ®¡¢Ö§³ÖºÍ°ïÖú¡£ÖÔÐĸÐл£¡£¡£¡

ÏêÇéÇëµã»÷ÍøÖ·£ºhttp://www.nj-goldfoil.com/rongchen/rongchen1.htm ä¯ÀÀ¡£¸Ðл£¡ 
 
Èݳ½×¯Ô°µØÖ·£ººÓ±±Ê¡ÕżҿÚÊл³À´ÏØСÄÏÐÁ±¤
ׯ԰µç»°£º0313-6853169  ¾ÆÔ°µç»°£º6853443
±±¾©Èݳ½×¯Ô°ÓªÏú×ܲ¿£º±±¾©Êг¯ÑôÇø±±³½¶«Â·8ºÅ»ã±ö´óÏÃA×ù2002ÊÒ
µç»°£º010-84991513  84991514
´«Õ棺010-94991512
¶Îº£ÈÝÏÈÉúÊÖ»ú£º13601149609

»ØÐÅÓÊÏ䣺[EMAIL PROTECTED]

Õâ·âÓʼþÒ»¶¨´òÈÅÄúÁË£¬ÔÚ´Ë£¬ÎÒÃDZíʾ³ÏÐĵĵÀǸ¡£ 

ÖÂ
Àñ!
   Ç뻹¾ÈÃüÇ®£¡
   [EMAIL PROTECTED]
   2003-01-10

-
Before posting, please check:
   http://www.mysql.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[4]: mysql 4.0.8- crash on TCP connection

[Andrew Sitnikov]

Hello ,

GG> Functions gethostby* ,from glibc, work directly with the /etc/hosts file.If
GG> this functions didn't find an entry for the client, will be crashed.
GG> I try to find in the Andrew e-mail if he has installed the glibc 2.2.x but i
GG> don't see nothing about it.

#> uname -a
Linux gap 2.4.20-grsec #1 SMP Fri Dec 6 23:17:05 EET 2002 i686 unknown

#>cat /etc/SuSE-release
SuSE Linux 7.3 (i386)
VERSION = 7.3

#> rpm -qa | grep glibc
glibc-i18ndata-2.2.4-77
glibc-profile-2.2.4-77
glibc-html-2.2.4-77
glibc-info-2.2.4-77
glibc-devel-2.2.4-77
glibc-2.2.4-77


GG> What i see is, he use 2.95.x which is declared by
GG> MySQL like unstable.In this context can be a coincidence what is happened.
I used binary distribution.

GG> Also i don't find difference in MYSQL daemon source code(hostname.cc)
GG> between 4.0.7 and 4.0.8.
I thing that this is "build" problem in binary distribution.


P.S sql,query,queries,smallint :)

Best regards,
 Andrew Sitnikov 
 e-mail : [EMAIL PROTECTED]
 GSM: (+372) 56491109


-
Before posting, please check:
   http://www.mysql.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

Calculate time span in mysql

[Paul Choy]

Hi :

I am trying to calculate the time span of listing a large table database in mysql and 
I run into trouble where I am trying to use curtime() - value stored in startime.But I 
do not know how to get the stored value out from startime.Does anybody knows how I can 
get value out from startime where I can do subtraction. Thanks


mysql> select * from time;
+--+
| startime |
+--+
| 17:06:35 |
| 17:09:39 |
| 00:00:00 |
| 13:27:49 |
| 15:04:52 |
+--+
5 rows in set (0.00 sec)

-
Before posting, please check:
   http://www.mysql.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: count(expr)

[Paul DuBois]

At 10:29 -0600 1/9/03, Meena Vyavahare wrote:

Here is my query-

Select col1, col2, count(*) as total, count(col3 < col4) as violations
from table1
Where condn
group by col1, col2

The result of this query shows same values at both the columns for total
and violations.
The count(expr) does not work


Sure it does.  It's counting non-NULL values.  If no values in col3 or
col4 are NULL, then col3 < col4 evaluates to either 0 or 1 and is
counted, and your total will be the same as COUNT(*).

You probably want SUM(col3 < col4,1,0) instead.



Thanks

Meena
---



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

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

Re: SELECT DISTINCT *

[gerald_clark]

Maybe if we knew what  . . . . . . . . . .  means, we could answer.

[EMAIL PROTECTED] wrote:


Hey all;

I am trying to work on a query that has SELECT DISTINCT * ..

This does not work. While there is no error, the where clause causes repeats. I 
do not really expect this to work, as DISTINCT needs a column to work with. I 
am trying to avoid the workaround of putting in every column from the table in 
the select statement, so does anyone have any idea on how to keep the same row 
from coming up twice while still being able to use SELECT * ??

Mike Hillyer




-
Before posting, please check:
  http://www.mysql.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

post installation error

[Black Eagle]

Hey guys,

I've just installed mysql from source 4.23.54a

But after
./configure --prefix=/usr/local/mysql
make
make install

I makes an error that there's no mysql user .. so I created one !
And I changed the owner of /usr/local/mysql/var/ and recursively to 
mysql:mysql . now it gives me that error but I dont know what to do :

030110 00:38:39 mysqld started
./usr/local/mysql/libexec/mysqld: Can't read dir of '/root/tmp' (Errcode: 13) 
030110 0:38:39 /usr/local/mysql/libexec/mysqld: Can't find file: 
'./mysql/host.frm' (errno: 13)
030110 00:38:39 mysql ended

now what ? he told me he needed to be run as mysql and now he complaints he 
can't access /root/tmp ... hey ..
for the ./mysql/host.frm . I really don't know what 2 do 

Any ideas ? 

thx
black eagle

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

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

Re: MySQL on large server

[Jeremy Zawodny]

On Thu, Jan 09, 2003 at 08:55:36AM +, mysql list wrote:
> Hi,
> 
> I was wondering if anybody has built MySQL 3.23 from source that can
> handle a high number of connections & threads. I've have tried MySQL
> binaries in the past (not RPMs), but these have had stability/load
> problems. When building from source I don't have those issues, but I
> am limited by the inbuilt limits (of glibc,etc...)

How many connections do you need?

If memory serves, the master db behind Slashdot was handling around
700 connections on a 4 processor P3 Xeon with 4GB RAM.  But it's been
a while since I've talked with Brian about it.

> I need to build MySQL 3.23 on a production server running RedHat
> 7.2, patched glibc (2.2.5-42) and a custom kernel (2.4.19-2
> SMP). Hardware contains Dual Xeon 2.4GHz (hyperthreading disabled)
> and 6GB RAM.

With that kind of RAM and horespower you should be able to go well
above 500 connections.

I'd say more on this topic, but I've not really needed to push MySQL
in that direction.  We tend to use more boxes that cost less and
replicate data.

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

MySQL 3.23.51: up 25 days, processed 863,460,485 queries (388/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

Re: Load local data infile problem

[Stefan Hinz, iConnect \(Berlin\)]

Paul,

>> Personally, I regard this security
>> "improvement" rather a bug than a feature.

> The implementation certainly is problematic, but the underlying
> issues that it tries to address is definitely real and not to be
ignored.
> People who say otherwise generally don't understand what those issues
> are.

Alright, now you got me :/

What I mean is there should be a way to turn LOCAL on again. It's
certainly nice if you can turn it off for enhanced security, but why
can't you turn it on again without compiling the server from source? The
manual section says that there is a --local-infile[=1] option for the
mysql CLI to turn it on, but it only mentions --local-infile=0 for the
server to turn it off. To me, this looks like --local-infile=1 was
intended to turn LOCAL on again, though the manual doesn't mention it.

Regards,
--
  Stefan Hinz <[EMAIL PROTECTED]>
  Geschäftsführer / CEO iConnect GmbH 
  Heesestr. 6, 12169 Berlin (Germany)
  Tel: +49 30 7970948-0  Fax: +49 30 7970948-3

- Original Message -
From: "Paul DuBois" <[EMAIL PROTECTED]>
To: "Stefan Hinz, iConnect (Berlin)" <[EMAIL PROTECTED]>; "Charles
Mabbott" <[EMAIL PROTECTED]>; "'Prathmesh J. Mahidharia'"
<[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Thursday, January 09, 2003 4:37 AM
Subject: Re: Load local data infile problem


At 18:39 +0100 1/8/03, Stefan Hinz, iConnect (Berlin) wrote:
>Charles,
>
>>>  I posted the same problem a couple of days ago. LOCAL will not work
>>>  because of a security "improvement" the MySQL folks applied.
>
>>  LOAD DATA INFILE "C:\\mysql\\fred.txt" INTO TABLE data_table;
>>  Hope this helps, but only a workaround...
>
>Without LOCAL, quite alot of things will not work. Imagine an ISP
giving
>every customer write privileges for the mysql/bin directory ... ;-/
>
>Unfortunately, Monty did'nt mention if this is fixed in 4.0.8 or going
>to be fixed in 4.0.9 or 4.1. Personally, I regard this security
>"improvement" rather a bug than a feature.

The implementation certainly is problematic, but the underlying
issues that it tries to address is definitely real and not to be
ignored.
People who say otherwise generally don't understand what those issues
are.

>
>Regards,
>--
>   Stefan Hinz <[EMAIL PROTECTED]>
>   Geschäftsführer / CEO iConnect GmbH 
>   Heesestr. 6, 12169 Berlin (Germany)
>   Tel: +49 30 7970948-0  Fax: +49 30 7970948-3



-
Before posting, please check:
   http://www.mysql.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

[Lists @ Apted Tech.]

automake: strings/Makefile.am: Assembler source seen but `CCAS' not defined
in `configure.in'
automake: strings/Makefile.am: Assembler source seen but `CCASFLAGS' not
defined in `configure.in'
error: Bad exit status from /var/tmp/rpm-tmp.37688 (%build)


RPM build errors:
Bad exit status from /var/tmp/rpm-tmp.37688 (%build)

i downloaded mysql-4.0.8-0.src.rpm off of the mysql web-site and have been
trying to compile it.  does anyone have any suggestions or ideas about how
to resolve the above error.  thanks all.

-chris


-
Before posting, please check:
   http://www.mysql.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

Off-Topic Posts

[Zak Greant]

Dear Readers,

Over the last week there have been a series of messages posted to this
list that were off-topic, inflammatory and often offensive.

Our list manager contacted the offending parties and requested that they
stop their abuse of the mailing list. When this failed to stop the
postings, the administrator unsubscribed the offending user from all
MySQL mailing lists and revoked their posting privileges. It is
unfortunate that these actions were required.

The MySQL mailing lists are a shared resource for the global MySQL
community.  While we are fortunate to have members from many or all of
the wired countries in the world, it means that it is almost inevitable
that there will occasionally be fundamental differences of belief between 
list members.

We ask people to avoid these conflicts and instead focus on the
co-operative and community aspects of the list.

To help maintain the MySQL lists as a friendly, helpful and useful
resource, we must request that people *do not respond to off-topic
posts*.

If you do feel compelled to respond, please do so off of the mailing list.

If you find a post to be offensive, please notify our list
administrators at [EMAIL PROTECTED] - we will deal with the
situation as best we can.


If you wish to discuss this situation further, please mail me *off-list*
at [EMAIL PROTECTED]


Yours Truly,

Zak Greant
MySQL Community Advocate

-
Before posting, please check:
   http://www.mysql.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: Bug in foreign keys

[Stefan Hinz, iConnect \(Berlin\)]

Rafal,

> alter table rezerwacje add foreign key (nip_h,nrpok_p) references
> pokoje(nip_h,nrpok_p);

> ERROR 1005: Can't create table './test/#sql-932_4.frm' (errno: 150)

Most probably, you are using MySQL >= 4.0.1 but < 4.0.5. I had these
kind of problems with ALTER TABLE with 4.0.1 and 4.0.4, but not with
4.0.7 anymore (hey, this rhymes :). I guess it has nothing to do with
the foreign keys, but with the temporary form table MySQL creates when
you do an ALTER TABLE. Anyway, the problem seems to be fixed, so you
should update to >= 4.0.7.

Regards,
--
  Stefan Hinz <[EMAIL PROTECTED]>
  Geschäftsführer / CEO iConnect GmbH 
  Heesestr. 6, 12169 Berlin (Germany)
  Tel: +49 30 7970948-0  Fax: +49 30 7970948-3

- Original Message -
From: "Rafal Jank" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, January 09, 2003 4:11 PM
Subject: Bug in foreign keys


> Hi!
> I have two tables:
> CREATE TABLE `pokoje` (
>   `nrpok_p` char(10) NOT NULL default '',
>   `nip_h` int(10) NOT NULL default '0',
>   `lozka_p` char(2) default NULL,
>   `tv_p` char(1) default NULL,
>   `lazienka_p` char(1) default NULL,
>   `cena_p` int(10) default NULL,
>   `zaliczka_p` int(10) default NULL,
>   PRIMARY KEY  (`nrpok_p`,`nip_h`),
>   KEY `nip_h_index` (`nip_h`,`nrpok_p`),
>   FOREIGN KEY (`nip_h`) REFERENCES `hotele` (`nip_h`) ON DELETE
CASCADE
> ) TYPE=InnoDB;
>
>  CREATE TABLE `rezerwacje` (
>   `id_r` int(10) NOT NULL default '0',
>   `pesel_k` int(11) default NULL,
>   `nip_h` int(10) NOT NULL default '0',
>   `nrpok_p` char(10) NOT NULL default '',
>   `data_r` date default NULL,
>   `od_r` date default NULL,
>   `do_r` date default NULL,
>   `cena_r` int(10) default NULL,
>   `zaliczka_r` int(5) default NULL,
>   `zaplac_r` char(1) default NULL,
>   `wplaczal_r` char(1) default NULL,
>   PRIMARY KEY  (`id_r`),
>   KEY `nip_h_index` (`nip_h`,`nrpok_p`)
> ) TYPE=InnoDB;
>
> Now, when I try:
> alter table rezerwacje add foreign key (nip_h,nrpok_p) references
> pokoje(nip_h,nrpok_p);
> I get:
> ERROR 1005: Can't create table './test/#sql-932_4.frm' (errno: 150)
>
> Why?
>
>
>
> --
> _/_/  _/_/_/  - Rafał Jank [EMAIL PROTECTED] -
>  _/  _/  _/  _/   _/ Wirtualna Polska SA   http://www.wp.pl
>   _/_/_/_/  _/_/_/ul. Traugutta 115c, 80-237 Gdansk, tel/fax.
(58)5215625
>_/  _/  _/ ==*  http://szukaj.wp.pl *==--
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>


-
Before posting, please check:
   http://www.mysql.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: remote connect crash

[Stefan Hinz, iConnect \(Berlin\)]

Dmitry,

> MySQL server segmentation faults when remote mysql client
> tries to connect on source and binary distributions. Local
> client connect does not cause any problems whatsoever.

I had the same sort of problem with MySQL 3.23.5x servers on SuSE Linux
8.0/8.1, and some newsgroup postings reported the same thing with SuSE
Linux. (No one had an explanation or a solution for this.) Which OS do
you have?

Regards,
--
  Stefan Hinz <[EMAIL PROTECTED]>
  Geschäftsführer / CEO iConnect GmbH 
  Heesestr. 6, 12169 Berlin (Germany)
  Tel: +49 30 7970948-0  Fax: +49 30 7970948-3

- Original Message -
From: "Dmitry V. Sokolov" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, January 09, 2003 6:07 PM
Subject: remote connect crash


> Good day,
> could you help me to solve this problem?
>
> MySQL server segmentation faults when remote mysql client
> tries to connect on source and binary distributions. Local
> client connect does not cause any problems whatsoever.
>
> -
> Before posting, please check:
>http://www.mysql.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: Case Problems...

[Stefan Hinz, iConnect \(Berlin\)]

Nick,

> CREATE DATABASE LookAtMe;
> the database is created but as:
> lookatme

This is not a bug, but a feature. By default, MySQL 4.0.x has
lower_case_table_names set to 1. If you want to change this behaviour,
put this in the [mysqld] section of your c:\my.cnf or c:\winnt\my.ini
file, and restart the MySQL server:

 set-variable = lower_case_table_names=0

Regards,
--
  Stefan Hinz <[EMAIL PROTECTED]>
  Geschäftsführer / CEO iConnect GmbH 
  Heesestr. 6, 12169 Berlin (Germany)
  Tel: +49 30 7970948-0  Fax: +49 30 7970948-3

- Original Message -
From: "Nick Stuart" <[EMAIL PROTECTED]>
To: "MySQL" <[EMAIL PROTECTED]>
Sent: Wednesday, January 08, 2003 7:52 PM
Subject: Case Problems...


> I seem to have found a bug with 4.0.7. I just want to make sure the
> issue hasn't been covered before I submit a report.
> I have 4.0.7 installed on windows 2000 server with all the service
packs
> and stuff. When I connect to the database through a Linux client
> (haven't tried it on the box itself) and issue:
> CREATE DATABASE LookAtMe;
> the database is created but as:
> lookatme
> with no caps.
> Anybody run into this before? I know Winblow$ isn't case sensitive and
> all but it should still create the database/folder as I type it.
> --
>
> -Nick Stuart
>
> USM Computer Science Major
> Visit us at http://csforum.newtsplace.com
> (run with LAMP)
>
> Filter Fodder: mysql, sql, queries, why isn't CREATE or DATABASE in
the
> god damn list! =D
>
> -
> Before posting, please check:
>http://www.mysql.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: Re: Load local data infile problem

[Stefan Hinz, iConnect \(Berlin\)]

Jennifer,

> Why would they have to do that? The file does not need to be in that
> directory.  In order to use LOAD DATA INFILE without LOCAL the file
just
> needs to be somewhere on the server that mysqld is running on

Exactly this is the point. Most ISPs (at least all the big ones who
offer MySQL here in Germany) have their MySQL servers running on
separate machines. As a regular customer (this applies to business
customers as well) you will get _no_ account at all on the MySQL host
machines. This is why you _have_ to use LOCAL to bulk import data.

> Obviously this does not negate the fact that LOCAL is sometimes
needed, but
> allowing all users to write to mysql/bin is not needed at all for any
reason
> that I can see.  Maybe I am missing something?

No, this was just an extreme example, thus the smiley ;-)

> From the docs -- http://www.mysql.com/doc/en/LOAD_DATA.html
> "If the LOCAL keyword is specified, the file is read from the client
host.
> If LOCAL is not specified, the file must be located on the server.
(LOCAL is
> available in MySQL Version 3.22.6 or later.)"

This is the way it _should_ be, and the way it _was_ until 4.0.1 or so.
With the recent versions (I tested 4.0.5 and 4.0.7 binary
distributions), LOCAL will not work at all. This is a bug, not a
(security) feature.

Regards,
--
  Stefan Hinz <[EMAIL PROTECTED]>
  Geschäftsführer / CEO iConnect GmbH 
  Heesestr. 6, 12169 Berlin (Germany)
  Tel: +49 30 7970948-0  Fax: +49 30 7970948-3

- Original Message -
From: "Jennifer Goodie" <[EMAIL PROTECTED]>
To: "Stefan Hinz, iConnect (Berlin)" <[EMAIL PROTECTED]>; "Charles
Mabbott" <[EMAIL PROTECTED]>; "'Prathmesh J. Mahidharia'"
<[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Wednesday, January 08, 2003 11:21 PM
Subject: RE: Re: Load local data infile problem


> >Imagine an ISP giving every customer write privileges for the
mysql/bin
> directory ... ;-/
>
> Why would they have to do that? The file does not need to be in that
> directory.  In order to use LOAD DATA INFILE without LOCAL the file
just
> needs to be somewhere on the server that mysqld is running on and be
> readable by the mysqld user.  I load my files in from my home
directory
> because I don't think the mysql base dir and data dir are a great spot
to
> arbitrarily put files (and I don't have permission to them w/o
su-ing).  If
> you are connecting via localhost, have FILE permission on the DB, and
can
> create a readable file somewhere on that server, you would be fine.
>
> We do not allow LOCAL on our servers as we are running replication and
> 3.23.54 won't support it.  I do not have write permission to any
directories
> except my home directory.  I have never run into any problems with
LOAD DATA
> that were not my own fault, usually it is error 13 because I typed the
path
> wrong or didn't chmod the file.
>
> Obviously this does not negate the fact that LOCAL is sometimes
needed, but
> allowing all users to write to mysql/bin is not needed at all for any
reason
> that I can see.  Maybe I am missing something?
>
> From the docs -- http://www.mysql.com/doc/en/LOAD_DATA.html
> "If the LOCAL keyword is specified, the file is read from the client
host.
> If LOCAL is not specified, the file must be located on the server.
(LOCAL is
> available in MySQL Version 3.22.6 or later.)"
>
>
> -Original Message-
> From: Stefan Hinz, iConnect (Berlin) [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, January 08, 2003 9:40 AM
> To: Charles Mabbott; 'Prathmesh J. Mahidharia'; [EMAIL PROTECTED]
> Subject: Re: Load local data infile problem
>
>
> Charles,
>
> >> I posted the same problem a couple of days ago. LOCAL will not work
> >> because of a security "improvement" the MySQL folks applied.
>
> > LOAD DATA INFILE "C:\\mysql\\fred.txt" INTO TABLE data_table;
> > Hope this helps, but only a workaround...
>
> Without LOCAL, quite alot of things will not work. Imagine an ISP
giving
> every customer write privileges for the mysql/bin directory ... ;-/
>
> Unfortunately, Monty did'nt mention if this is fixed in 4.0.8 or going
> to be fixed in 4.0.9 or 4.1. Personally, I regard this security
> "improvement" rather a bug than a feature.
>
>
>
>


-
Before posting, please check:
   http://www.mysql.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: Resetting the auto_increment to start from 1

[Stefan Hinz, iConnect \(Berlin\)]

Paul,

>>It's a rather old book, which deals with MySQL 3.23, and not with
MySQL
>>4.x.

>Actually, he's talking about MySQL Cookbook (p549).  Which is a new
book,
>which is why I said *may* reset the counter rather than *will*
>reset the counter as was true in older versions of MySQL.

Oops. Sorry for calling the brand new MySQL Cookbook an "old book"! :/

>>  TRUNCATE TABLE tbl
>>This will in fact do a DROP/CREATE, thus resetting the AUTO_INCREMENT
>>counter etc.

>Not always!
>Try this script:
>CREATE TABLE t (i INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY)
>TYPE = INNODB;
>INSERT INTO t SET i = NULL;
>INSERT INTO t SET i = NULL;
>INSERT INTO t SET i = NULL;
>SELECT * FROM t;
>TRUNCATE TABLE t;
>INSERT INTO t SET i = NULL;
>SELECT * FROM t;

You're right, and my 4.0.7 behaves the same way. Is TRUNCATE supposed to
behave this way? (The manual doesn't say anything about TRUNCATE and
AUTO_INCREMENT.)

>>Actually, the counter is reset to 0, not 1. The first inserted value
>>then is auto-incremented, and thus becomes 1.

>Sure about that?  Create a new table and try SHOW TABLE STATUS LIKE 't'
>and you'll get:
[snip]
>  Auto_increment: 1

Oh, well ... There's a slight contradiction in the manual, but you're
right again, anyway:

"When you insert a value of NULL (recommended) or 0 into an
AUTO_INCREMENT column, the column is set to value+1, where value is the
largest value for the column currently in the table. AUTO_INCREMENT
sequences begin with 1."

So, if initially value=1, then the first auto_increment value would be
1+1. Anyway, we know what the manual wants to tell us, so sorry for
being precocious.

Regards,
--
  Stefan Hinz <[EMAIL PROTECTED]>
  Geschäftsführer / CEO iConnect GmbH 
  Heesestr. 6, 12169 Berlin (Germany)
  Tel: +49 30 7970948-0  Fax: +49 30 7970948-3

- Original Message -
From: "Paul DuBois" <[EMAIL PROTECTED]>
To: "Stefan Hinz, iConnect (Berlin)" <[EMAIL PROTECTED]>; "Octavian
Rasnita" <[EMAIL PROTECTED]>; "MySQL" <[EMAIL PROTECTED]>
Sent: Thursday, January 09, 2003 1:49 AM
Subject: Re: Resetting the auto_increment to start from 1


At 20:39 +0100 1/8/03, Stefan Hinz, iConnect (Berlin) wrote:
>Octavian,
>
>>  I've read the following in a MySQL book:
>
>It's a rather old book, which deals with MySQL 3.23, and not with MySQL
>4.x.

Actually, he's talking about MySQL Cookbook (p549).  Which is a new
book,
which is why I said *may* reset the counter rather than *will*
reset the counter as was true in older versions of MySQL.


>
>>   DELETE FROM tbl_name WHERE 1 > 0;
>
>In MySQL 3.23, this was a workaround to force the server to delete a
>table row by row. By default, 3.23 would on DELETE FROM tbl just do a
>DROP TABLE + CREATE TABLE, because this was faster in most cases than
>deleting the rows. This behaviour wasn't ANSI SQL compliant, though.
>
>MySQL 4.x does a DELETE FROM tbl with or without WHERE clause ANSI
>compliant. This means, it will always delete the rows, not DROP/CREATE
>the table. To do the latter, use
>
>  TRUNCATE TABLE tbl
>
>This will in fact do a DROP/CREATE, thus resetting the AUTO_INCREMENT
>counter etc.

Not always!

Try this script:

DROP TABLE IF EXISTS t;
CREATE TABLE t (i INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY)
TYPE = INNODB;
INSERT INTO t SET i = NULL;
INSERT INTO t SET i = NULL;
INSERT INTO t SET i = NULL;
SELECT * FROM t;
TRUNCATE TABLE t;
INSERT INTO t SET i = NULL;
SELECT * FROM t;

See if you get the output I do (MySQL 4.0.8):

+---+
| i |
+---+
| 1 |
| 2 |
| 3 |
+---+
+---+
| i |
+---+
| 4 |
+---+

What's the solution?  Do this:

ALTER TABLE t AUTO_INCREMENT = 1;

>
>>  Well, I've tried that sql statement, but the auto_increment point of
>start
>>  was not reset to 1.
>
>Actually, the counter is reset to 0, not 1. The first inserted value
>then is auto-incremented, and thus becomes 1.

Sure about that?  Create a new table and try SHOW TABLE STATUS LIKE 't'
and you'll get:

mysql> show table status like 't'\G
*** 1. row ***
Name: t
Type: InnoDB
  Row_format: Fixed
Rows: 0
  Avg_row_length: 0
 Data_length: 16384
Max_data_length: NULL
Index_length: 0
   Data_free: 0
  Auto_increment: 1
 Create_time: NULL
 Update_time: NULL
  Check_time: NULL
  Create_options:
 Comment: InnoDB free: 14336 kB


>
>Regards,
>--
>   Stefan Hinz <[EMAIL PROTECTED]>
>   Geschäftsführer / CEO iConnect GmbH 
>   Heesestr. 6, 12169 Berlin (Germany)
>   Tel: +49 30 7970948-0  Fax: +49 30 7970948-3
>
>- Original Message -
>From: "Octavian Rasnita" <[EMAIL PROTECTED]>
>To: "MySQL" <[EMAIL PROTECTED]>
>Sent: Wednesday, January 08, 2003 8:33 AM
>Subject: Resetting the auto_increment to start from 1
>
>
>>  Hi all,
>>
>>  I've read the following in a MySQL book:
>>
>>A special case of record deletion occurs when you clear out a
table
>>  entirely using a DELETE with no WHERE clause:
>>   DELETE FROM 

Re: Copying MySql database to others

[Stefan Hinz, iConnect \(Berlin\)]

Frank,

> Since I have limited command line access (ISP hosted database), I will
> probably have to use phpMyAdmin.
> Part of my logic was to copy my MySql database to another MySql
database,
> so that I can use the second database for QA purposes.
> When I perform the Export as above, does that make a copy in another
> database or is that to an  external file? If it is an external file,
would
> I have to Import it to another database that I create?

>> 6. Check "Save as file".

This means phpMyAdmin will write the export file to a file which it will
send to the browser. You can save this file on the machine where the
other (QA) MySQL server is running. On this machine, you can load the
export like this:

Let's say, you exported database "my_database", and you saved the export
file as "c:\mysql\my_database_export.sql" on your QA machine. You can
import it with this command (assuming that database "my_database" exists
on your QA machine, but has no tables in it):

 c:\mysql\bin> mysql -uusername -ppassword my_database <
c:\mysql\my_database_export.sql

Regards,
--
  Stefan Hinz <[EMAIL PROTECTED]>
  Geschäftsführer / CEO iConnect GmbH 
  Heesestr. 6, 12169 Berlin (Germany)
  Tel: +49 30 7970948-0  Fax: +49 30 7970948-3

- Original Message -
From: "Frank Peavy" <[EMAIL PROTECTED]>
To: "Stefan Hinz, iConnect (Berlin)" <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
Sent: Thursday, January 09, 2003 3:37 AM
Subject: Re: Copying MySql database to others


Thanks everyone for your input.

Stefan,
> > >From, within phpMyAdmin?
>
>1. In the left frame, choose the database you want to backup / copy.
>2. Click the EXPORT tab in the right frame.
>3. Choose the tables in the database you want to backup.
>4. Choose "Structure and data".
>5. Check "Enclose table and field names with backquotes" if your table
>or column names might contain special characters (like ä, ö, ü).
>6. Check "Save as file".
>7. Click "Go".

Since I have limited command line access (ISP hosted database), I will
probably have to use phpMyAdmin.

Part of my logic was to copy my MySql database to another MySql
database,
so that I can use the second database for QA purposes.

When I perform the Export as above, does that make a copy in another
database or is that to an  external file? If it is an external file,
would
I have to Import it to another database that I create?



-
Before posting, please check:
   http://www.mysql.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: Bug in foreign keys

[Heikki Tuuri]

Rafal,

I tested also with the `hotele`, and it worked ok.

Please provide your my.cnf and MySQL version number. And test if you can
repeat the error.

Regards,

Heikki


Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.8-gamma-standard-log

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

mysql> drop database test;
Query OK, 0 rows affected (0.08 sec)

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE `hotele` (
->   `nip_h` int(10) NOT NULL default '0',
->   `nazwa_h` varchar(100) NOT NULL default '',
->   `miasto_h` varchar(50) NOT NULL default '',
->   `kodpocz_h` int(5) NOT NULL default '0',
->   `adres_h` varchar(50) default NULL,
->   `email_h` varchar(50) default NULL,
->   `kontobank_h` int(32) default NULL,
->   `wyprzedaz_h` varchar(10) default NULL,
->   `standard_h` varchar(20) default NULL,
->   `opis_h` varchar(200) default NULL,
->   PRIMARY KEY  (`nip_h`)
-> ) TYPE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> CREATE TABLE `pokoje` (
->   `nrpok_p` char(10) NOT NULL default '',
->   `nip_h` int(10) NOT NULL default '0',
->   `lozka_p` char(2) default NULL,
->   `tv_p` char(1) default NULL,
->   `lazienka_p` char(1) default NULL,
->   `cena_p` int(10) default NULL,
->   `zaliczka_p` int(10) default NULL,
->   PRIMARY KEY  (`nrpok_p`,`nip_h`),
->   FOREIGN KEY (`nip_h`) REFERENCES `hotele` (`nip_h`) ON DELETE
CASCADE,
->   KEY `nip_h_index` (`nip_h`,`nrpok_p`),
-> ) TYPE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> CREATE TABLE `rezerwacje` (
->   `id_r` int(10) NOT NULL default '0',
->   `pesel_k` int(11) default NULL,
->   `nip_h` int(10) NOT NULL default '0',
->   `nrpok_p` char(10) NOT NULL default '',
->   `data_r` date default NULL,
->   `od_r` date default NULL,
->   `do_r` date default NULL,
->   `cena_r` int(10) default NULL,
->   `zaliczka_r` int(5) default NULL,
->   `zaplac_r` char(1) default NULL,
->   `wplaczal_r` char(1) default NULL,
->   PRIMARY KEY  (`id_r`),
->   KEY `nip_h_index` (`nip_h`,`nrpok_p`)
-> ) TYPE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> alter table rezerwacje add foreign key (nip_h,nrpok_p) references
-> pokoje(nip_h,nrpok_p);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

- Original Message -
From: "Heikki Tuuri" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, January 09, 2003 11:04 PM
Subject: Re: Bug in foreign keys


> Rafal,
>
> - Original Message -
> From: "Rafal Jank" <[EMAIL PROTECTED]>
> Newsgroups: mailing.database.mysql
> Sent: Thursday, January 09, 2003 6:18 PM
> Subject: Bug in foreign keys
>
>
> > Hi!
> > I have two tables:
> > CREATE TABLE `pokoje` (
> >   `nrpok_p` char(10) NOT NULL default '',
> >   `nip_h` int(10) NOT NULL default '0',
> >   `lozka_p` char(2) default NULL,
> >   `tv_p` char(1) default NULL,
> >   `lazienka_p` char(1) default NULL,
> >   `cena_p` int(10) default NULL,
> >   `zaliczka_p` int(10) default NULL,
> >   PRIMARY KEY  (`nrpok_p`,`nip_h`),
> >   KEY `nip_h_index` (`nip_h`,`nrpok_p`),
> >   FOREIGN KEY (`nip_h`) REFERENCES `hotele` (`nip_h`) ON DELETE CASCADE
> > ) TYPE=InnoDB;
> >
> >  CREATE TABLE `rezerwacje` (
> >   `id_r` int(10) NOT NULL default '0',
> >   `pesel_k` int(11) default NULL,
> >   `nip_h` int(10) NOT NULL default '0',
> >   `nrpok_p` char(10) NOT NULL default '',
> >   `data_r` date default NULL,
> >   `od_r` date default NULL,
> >   `do_r` date default NULL,
> >   `cena_r` int(10) default NULL,
> >   `zaliczka_r` int(5) default NULL,
> >   `zaplac_r` char(1) default NULL,
> >   `wplaczal_r` char(1) default NULL,
> >   PRIMARY KEY  (`id_r`),
> >   KEY `nip_h_index` (`nip_h`,`nrpok_p`)
> > ) TYPE=InnoDB;
> >
> > Now, when I try:
> > alter table rezerwacje add foreign key (nip_h,nrpok_p) references
> > pokoje(nip_h,nrpok_p);
> > I get:
> > ERROR 1005: Can't create table './test/#sql-932_4.frm' (errno: 150)
> >
> > Why?
>
>
> what is your MySQL version? What is the OS? What is the
> default-character-set in my.cnf? What does SHOW CREATE TABLE say for every
> table involved, also `hotel`? Can you write a script which repeats the
> error?
>
> I tested a slightly more complex script in the latest MySQL-4.0 source
tree,
> with the latin1_de charset and it ran without errors:
>
>
> CREATE TABLE `hotele` (
>   `nip_h` int(10) NOT NULL default '0',
>   `abbaguu` int(10),
> PRIMARY KEY (`nip_h`)
> ) TYPE = InnoDB;
>
> CREATE TABLE `pokoje` (
>   `nrpok_p` char(10) NOT NULL default '',
>   `nip_h` int(10) NOT NULL default '0',
>   `lozka_p` char(2) default NULL,
>   `tv_p` char(1) default NULL,
>   `lazienka_p` char(1) default NULL,
>   `cena_p` int(10) default NULL,
>   `zaliczka_p` int(10) default NULL,
>   PRIMARY KEY  (`nrpok_p`,`nip_h`),
>   FO

Selecting max value from sets

[Benjamin Dixon]

Hi all,

I'm trying to figure out a way to select a group of maximums from a set
such that each value pair's greatest value is in the result set. For
example, let's say I have this table Value_Pairs:

Name | Value

Bob1
Joe7
Bob2
Don3
Don4
Bob6

The result I want is like this:

Name | Value

Bob6//Bob's highest value in the table
Joe7//Joe's highest value in the table
Don4//Don't highest value in the table

So I'm looking for distinct maximums.
Is it possible to do this *with a single query* in MySQL? I've tried a
number of things and nothing comes close.

ben


-
Before posting, please check:
   http://www.mysql.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?

[David Lubowa]

cacti?

cheers
David Ziggy Lubowa
Network Engineer
One2net (U)
web: www.one2net.co.ug
Tel: +256 41 345466

-Original Message-
From: Shon Stephens [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 08, 2003 8:55 PM
To: '[EMAIL PROTECTED]'
Subject: mysql monitoring?


is there an snmp mib or something available for snmp to monitor mysql
server? or, does anyone have a script that they would like to share that
they use to monitor and gather statistics about mysql?

thanks,
shon

-
Before posting, please check:
   http://www.mysql.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: count(expr)

[Keith C. Ivey]

On 9 Jan 2003, at 10:29, Meena Vyavahare wrote:

> Select col1, col2, count(*) as total, count(col3 < col4) as violations
> from table1
> Where condn
> group by col1, col2
> 
> The result of this query shows same values at both the columns for total
> and violations.
> The count(expr) does not work

You're misunderstanding how COUNT() works.  Since the expression 
(col3 < col4) is never null, counting it is the same as counting the 
number of rows.  See the documentation:

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

In this situation you might use SUM() instead, since the inequality 
evaluates to either 0 or 1 in numeric context.

-- 
Keith C. Ivey <[EMAIL PROTECTED]>
Tobacco Documents Online
http://tobaccodocuments.org
Phone 202-667-6653

-
Before posting, please check:
   http://www.mysql.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: Bug in foreign keys

[Heikki Tuuri]

Rafal,

- Original Message -
From: "Rafal Jank" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Thursday, January 09, 2003 6:18 PM
Subject: Bug in foreign keys


> Hi!
> I have two tables:
> CREATE TABLE `pokoje` (
>   `nrpok_p` char(10) NOT NULL default '',
>   `nip_h` int(10) NOT NULL default '0',
>   `lozka_p` char(2) default NULL,
>   `tv_p` char(1) default NULL,
>   `lazienka_p` char(1) default NULL,
>   `cena_p` int(10) default NULL,
>   `zaliczka_p` int(10) default NULL,
>   PRIMARY KEY  (`nrpok_p`,`nip_h`),
>   KEY `nip_h_index` (`nip_h`,`nrpok_p`),
>   FOREIGN KEY (`nip_h`) REFERENCES `hotele` (`nip_h`) ON DELETE CASCADE
> ) TYPE=InnoDB;
>
>  CREATE TABLE `rezerwacje` (
>   `id_r` int(10) NOT NULL default '0',
>   `pesel_k` int(11) default NULL,
>   `nip_h` int(10) NOT NULL default '0',
>   `nrpok_p` char(10) NOT NULL default '',
>   `data_r` date default NULL,
>   `od_r` date default NULL,
>   `do_r` date default NULL,
>   `cena_r` int(10) default NULL,
>   `zaliczka_r` int(5) default NULL,
>   `zaplac_r` char(1) default NULL,
>   `wplaczal_r` char(1) default NULL,
>   PRIMARY KEY  (`id_r`),
>   KEY `nip_h_index` (`nip_h`,`nrpok_p`)
> ) TYPE=InnoDB;
>
> Now, when I try:
> alter table rezerwacje add foreign key (nip_h,nrpok_p) references
> pokoje(nip_h,nrpok_p);
> I get:
> ERROR 1005: Can't create table './test/#sql-932_4.frm' (errno: 150)
>
> Why?


what is your MySQL version? What is the OS? What is the
default-character-set in my.cnf? What does SHOW CREATE TABLE say for every
table involved, also `hotel`? Can you write a script which repeats the
error?

I tested a slightly more complex script in the latest MySQL-4.0 source tree,
with the latin1_de charset and it ran without errors:


CREATE TABLE `hotele` (
  `nip_h` int(10) NOT NULL default '0',
  `abbaguu` int(10),
PRIMARY KEY (`nip_h`)
) TYPE = InnoDB;

CREATE TABLE `pokoje` (
  `nrpok_p` char(10) NOT NULL default '',
  `nip_h` int(10) NOT NULL default '0',
  `lozka_p` char(2) default NULL,
  `tv_p` char(1) default NULL,
  `lazienka_p` char(1) default NULL,
  `cena_p` int(10) default NULL,
  `zaliczka_p` int(10) default NULL,
  PRIMARY KEY  (`nrpok_p`,`nip_h`),
  FOREIGN KEY (`nip_h`) REFERENCES `hotele` (`nip_h`) ON DELETE CASCADE,
  KEY `nip_h_index` (`nip_h`,`nrpok_p`),
) TYPE=InnoDB;

CREATE TABLE `rezerwacje` (
  `id_r` int(10) NOT NULL default '0',
  `pesel_k` int(11) default NULL,
  `nip_h` int(10) NOT NULL default '0',
  `nrpok_p` char(10) NOT NULL default '',
  `data_r` date default NULL,
  `od_r` date default NULL,
  `do_r` date default NULL,
  `cena_r` int(10) default NULL,
  `zaliczka_r` int(5) default NULL,
  `zaplac_r` char(1) default NULL,
  `wplaczal_r` char(1) default NULL,
  PRIMARY KEY  (`id_r`),
  KEY `nip_h_index` (`nip_h`,`nrpok_p`)
) TYPE=InnoDB;

CREATE TABLE `kukkuu` (
  `id_r` int(10) NOT NULL default '0',
   PRIMARY KEY  (`id_r`),
   FOREIGN KEY (`id_r`) REFERENCES `rezerwacje` (`id_r`)
) TYPE=InnoDB;

alter table rezerwacje add foreign key (nip_h,nrpok_p) references
pokoje(nip_h,nrpok_p);


mysql> CREATE TABLE `hotele` (
->   `nip_h` int(10) NOT NULL default '0',
->   `abbaguu` int(10),
-> PRIMARY KEY (`nip_h`)
-> ) TYPE = InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> CREATE TABLE `pokoje` (
->   `nrpok_p` char(10) NOT NULL default '',
->   `nip_h` int(10) NOT NULL default '0',
->   `lozka_p` char(2) default NULL,
->   `tv_p` char(1) default NULL,
->   `lazienka_p` char(1) default NULL,
->   `cena_p` int(10) default NULL,
->   `zaliczka_p` int(10) default NULL,
->   PRIMARY KEY  (`nrpok_p`,`nip_h`),
->   FOREIGN KEY (`nip_h`) REFERENCES `hotele` (`nip_h`) ON DELETE
CASCADE,
->   KEY `nip_h_index` (`nip_h`,`nrpok_p`),
-> ) TYPE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> CREATE TABLE `rezerwacje` (
->   `id_r` int(10) NOT NULL default '0',
->   `pesel_k` int(11) default NULL,
->   `nip_h` int(10) NOT NULL default '0',
->   `nrpok_p` char(10) NOT NULL default '',
->   `data_r` date default NULL,
->   `od_r` date default NULL,
->   `do_r` date default NULL,
->   `cena_r` int(10) default NULL,
->   `zaliczka_r` int(5) default NULL,
->   `zaplac_r` char(1) default NULL,
->   `wplaczal_r` char(1) default NULL,
->   PRIMARY KEY  (`id_r`),
->   KEY `nip_h_index` (`nip_h`,`nrpok_p`)
-> ) TYPE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> CREATE TABLE `kukkuu` (
->   `id_r` int(10) NOT NULL default '0',
->PRIMARY KEY  (`id_r`),
->FOREIGN KEY (`id_r`) REFERENCES `rezerwacje` (`id_r`)
-> ) TYPE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> alter table rezerwacje add foreign key (nip_h,nrpok_p) references
-> pokoje(nip_h,nrpok_p);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

> --
> _/_/  _/_/_/  - Rafa³ Jank [EMAIL PROTECTED] -
>  _/  _/  _/  _/

Re: Re[4]: mysql 4.0.8- crash on TCP connection

[Gelu Gogancea]

> All this is very interesting, BUT i have two binary builds (4.0.7 &
4.0.8),
Ha,Hayou  are gentle.

> (load avg 10-60 Query/sec), and 4.0.8 crash (in some
> hardware/software) after 2 seconds work :(

If i understand well it crashed even if no one is connected ?

Regards,

Gelu
_
G.NET SOFTWARE COMPANY

Permanent e-mail address : [EMAIL PROTECTED]
  [EMAIL PROTECTED]
- Original Message -
From: "Andrew Sitnikov" <[EMAIL PROTECTED]>
To: "Gelu Gogancea" <[EMAIL PROTECTED]>
Cc: "Christopher E. Brown" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Thursday, January 09, 2003 10:28 PM
Subject: Re[4]: mysql 4.0.8- crash on TCP connection


> Hello Gelu,
>
> >> No, the glibc gethostby* will walk the tree defined in hosts.conf,
> >> normally files,dns.  A non-find in /etc/hosts followed by a >NXDOMAIN
> GG> If you said soanyhow gethostby* is used by DNS and no reverse.
> GG> The Name Server(named) use this both functions depending  by the order
line
> GG> in /etc/host.conf.And after this read the /etc/hosts.Depend on the
action
> GG> this file is call "host database file" or "static table for host
names".
> All this is very interesting, BUT i have two binary builds (4.0.7 &
4.0.8),
> downloaded from mysql mirror, and 4.0.7 work for me about 1 month with out
problems
> (load avg 10-60 Query/sec), and 4.0.8 crash (in some
> hardware/software) after 2 seconds work :(
>
>
> Best regards,
>  Andrew Sitnikov
>  e-mail : [EMAIL PROTECTED]
>  GSM: (+372) 56491109
>
>


-
Before posting, please check:
   http://www.mysql.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 Database Design

[Colaluca, Brian]

I am in the midst of designing a personal database for keeping track of
wines.  After perusing through several beginner books on MySQL and PHP, I
have decided that my next step would be to embark upon database design.  

My design is almost complete and normalized, although I do expect to be
making many tweakings as my knowledge progresses.

I have come to a brick wall on one facet of my design, however.  I've come
to understand that having a lot of NULLs in your database may be a sign of a
poor design.  And yet I'm having a problem reconciling this with the wildly
un-uniform world of wines from around the world.  For instance, I would like
to have a table called "GrapeVariety," and have the variety_id be a primary
key.  Another table would be "Wine."  And yet, one wine could have one type
of grape or more. 

For instance, let's say that wine XYZ has 80% GrapeA, and 20% GrapeB.  Since
my grape variety would presumably be a foreign key in the Wine table, how
could I specify a certain *percentage* of a foreign key?  I've tried hashing
this out in numerous ways, including the addition of a "Blend" table with
multiple primary keys, but anyway I slice it, there will still be an
abundance of NULLs.  For while the majority of wines may only contain one
grape, there could be wines that have up to 5 or 6 in varying percentages.  

My apologies if this is inappropriately posted to this list (i.e. sorry for
all the "wine"-ing).  Any links or suggestions as to where I may find
answers to database design issues would be greatly appreciated.

bjc-

:==  Brian J. Colaluca - Software Engineer
:==  DRS Technologies - ESG
===  [EMAIL PROTECTED]
===  phone:  (301) 921-8107


-
Before posting, please check:
   http://www.mysql.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: Version 4 "safe" to use?

[William R. Mussatto]

On Thu, 9 Jan 2003, Paul DuBois wrote:

> Date: Thu, 9 Jan 2003 11:29:03 -0600
> From: Paul DuBois <[EMAIL PROTECTED]>
> To: Maximo Migliari <[EMAIL PROTECTED]>, [EMAIL PROTECTED]
> Subject: Re: Version 4 "safe" to use?
> 
> At 13:51 -0300 1/9/03, Maximo Migliari wrote:
> >What were the main benefits that you noticed straight away before 
> >adapting your code to use the new features that MySQL 4 offers?
> >Does the query cache make a big difference?
> 
> Oh, yes.  It does. :-)
> 
> >   Do you have any benchmarks, even off your head?
PC mag a few months back did one and looked at it with and without. You 
might google for that.


Sincerely,

William Mussatto, Senior Systems Engineer
CyberStrategies, Inc
ph. 909-920-9154 ext. 27


-
Before posting, please check:
   http://www.mysql.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: remote connect crash

[Mark Matthews]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Dmitry V. Sokolov wrote:

Good day, 
could you help me to solve this problem?


This is a known issue that is already fixed in the BK source for 4.0.x, 
and will be in 4.0.9, which is currently being built.

	-Mark


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

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

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

iD8DBQE+Hd1jtvXNTca6JD8RAvd/AKCAgVTkX8aXHGdhWlKmRLJ/w96WKgCgm5V4
sP7ZE3DCxqmCREJ3NXGtBu8=
=SeLq
-END PGP SIGNATURE-


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

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

Re[4]: mysql 4.0.8- crash on TCP connection

[Andrew Sitnikov]

Hello Gelu,

>> No, the glibc gethostby* will walk the tree defined in hosts.conf,
>> normally files,dns.  A non-find in /etc/hosts followed by a >NXDOMAIN
GG> If you said soanyhow gethostby* is used by DNS and no reverse.
GG> The Name Server(named) use this both functions depending  by the order line
GG> in /etc/host.conf.And after this read the /etc/hosts.Depend on the action
GG> this file is call "host database file" or "static table for host names".
All this is very interesting, BUT i have two binary builds (4.0.7 & 4.0.8),
downloaded from mysql mirror, and 4.0.7 work for me about 1 month with out problems
(load avg 10-60 Query/sec), and 4.0.8 crash (in some
hardware/software) after 2 seconds work :(


Best regards,
 Andrew Sitnikov 
 e-mail : [EMAIL PROTECTED]
 GSM: (+372) 56491109


-
Before posting, please check:
   http://www.mysql.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: remote connect crash

[Gelu Gogancea]

Hi,
Start mysql daemon with options "--skip-name-resolve"
Regards,

Gelu
_
G.NET SOFTWARE COMPANY

Permanent e-mail address : [EMAIL PROTECTED]
  [EMAIL PROTECTED]
- Original Message -
From: "Dmitry V. Sokolov" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, January 09, 2003 7:07 PM
Subject: remote connect crash


> Good day,
> could you help me to solve this problem?
>
> MySQL server segmentation faults when remote mysql client
> tries to connect on source and binary distributions. Local
> client connect does not cause any problems whatsoever.
>
> -
> Before posting, please check:
>http://www.mysql.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: Re[2]: mysql 4.0.8- crash on TCP connection

[Gelu Gogancea]

sql,query,queries,smallint
> No, the glibc gethostby* will walk the tree defined in hosts.conf,
> normally files,dns.  A non-find in /etc/hosts followed by a >NXDOMAIN
If you said soanyhow gethostby* is used by DNS and no reverse.
The Name Server(named) use this both functions depending  by the order line
in /etc/host.conf.And after this read the /etc/hosts.Depend on the action
this file is call "host database file" or "static table for host names".

> from DNS results in a negative return from the gethostby* call. 

If it find something return a hostent data struct else is a NULL POINTER.

Conclusioni think we talk about two different  thing

Regards,

Gelu
_
G.NET SOFTWARE COMPANY

Permanent e-mail address : [EMAIL PROTECTED]
  [EMAIL PROTECTED]
- Original Message - 
From: "Christopher E. Brown" <[EMAIL PROTECTED]>
To: "Gelu Gogancea" <[EMAIL PROTECTED]>
Cc: "Andrew Sitnikov" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Thursday, January 09, 2003 9:38 PM
Subject: Re: Re[2]: mysql 4.0.8- crash on TCP connection



-
Before posting, please check:
   http://www.mysql.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: Year data type

[Zak Greant]

On Thu, Jan 09, 2003 at 10:48:18AM -0800, [EMAIL PROTECTED] wrote:
> It seems odd to me that the data type "Year", should only allow the limited
> range of 1901-2155.
> i understand that this is so that it will store in 1 byte, but whats the
> point?
> i wanted to use a year field for historical data, such as the year famous
> people were born, such
> as abe lincoln - 1809, but surprise surprise 1809 is unacceptable.
> 
> so i guess i dont see the point of the year datatype.
> 
> i would be interented in any insight as to why the year type is like this.

  Hello Sean,

  I don't speak for Monty here, but I would guess that you have hit the
  nail on the head.

  The years 1901-2155 are useful for a large segment of user data. Being
  able to store year information for account expiry dates, etc. in a
  single byte can be a considerable space savings when looking at a large
  database. This range of values could be represented by a TINYINT -
  however, this would require extra logic to conver the TINYINT to a
  year value.

  If you want to store years outside the range of YEAR, use the SMALLINT
  data type - it allows you to easily represent the years 32768 BC to
  32767 AD.

-- 
Zak Greant <[EMAIL PROTECTED]> | MySQL Advocate |  http://zak.fooassociates.com

MySQL Tip: Connect to a remote MySQL server using the mysql client
  % mysql --host=example.com --user=admin -p

Public Service Announcement:  Open Source Initiative (http://www.opensource.org)

-
Before posting, please check:
   http://www.mysql.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]: mysql monitoring?

[Andrew Sitnikov]

Hello David,

DL> is there an snmp mib or something available for snmp to monitor mysql
DL> server? or, does anyone have a script that they would like to share that
DL> they use to monitor and gather statistics about mysql?

MYSQLSTAT - A set of utilities to monitor, store and display Mysql DBMS usage 
statistics

Types of stats: 

1. Number of queries (queries/sec) 
2. Number of Connections (conn/sec) 
3. Data In/Out (bytes/sec) 
4. Key write requests (requests/sec) 
5. Key read requests (requests/sec) 
6. Key writes (writes/sec) 
7. Key reads (reads/sec) 
8. Types of queries 
9. Temporary and disk tables usage 

http://www.mysqlstat.org/en/
http://www.mysqlstat.org/en/screenshot



Best regards,
 Andrew Sitnikov 
 e-mail : [EMAIL PROTECTED]
 GSM: (+372) 56491109


-
Before posting, please check:
   http://www.mysql.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

IGNORE Line LOAD DATA LOCAL

[Karam Chand]

Hello

I have a fixed width csv file. To import it I use the
following statement -

load data local infile 'F:/Documents and
Settings/Insane/Desktop/test.csv'
into table `insane`.`user_details_copy` fields escaped
by '\\' enclosed by
'' terminated by '' lines terminated by \n' IGNORE 1
LINES ( id, user_name,
user_email, user_ref_from, other_ref, want_notify,
dateofdown )

Now the problem is what even value I give for IGNORE
LINES, MySQL seems to import from the first line
always ?

Whereas if the CSV file is , delimited, the IGNORE
LINES syntax seems to be working fine ?

Is IGNORE LINES not valid for FIXED LENGTH format or I
am missing something ?

Thanks in advance

karam

__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

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

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

Re: Re[2]: mysql 4.0.8- crash on TCP connection

[Christopher E. Brown]

On Thu, 9 Jan 2003, Gelu Gogancea wrote:

>
> Functions gethostby* ,from glibc, work directly with the /etc/hosts file.If
> this functions didn't find an entry for the client, will be crashed.
> I try to find in the Andrew e-mail if he has installed the glibc 2.2.x but i
> don't see nothing about it.What i see is, he use 2.95.x which is declared by
> MySQL like unstable.In this context can be a coincidence what is happened.
> Also i don't find difference in MYSQL daemon source code(hostname.cc)
> between 4.0.7 and 4.0.8.
> Regards,
>
> Gelu


No, the glibc gethostby* will walk the tree defined in hosts.conf,
normally files,dns.  A non-find in /etc/hosts followed by a NXDOMAIN
from DNS results in a negative return from the gethostby* call.  *This
should never cause a crash*, it is not a failure in the resolver code,
it is a negative result.


As to gcc, 2.95.3 is fine and stable, the notes you mention refer to
gcc 2.96, an *unofficial* gcc release, a heavily patched monster
released by RedHat and (for a while) used in alot of places.


-- 
I route, therefore you are.


-
Before posting, please check:
   http://www.mysql.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: Hiding the password

[Octavian Rasnita]

If you have a CGI script with 700 permissions, that script cannot be
accessed by the Apache server unless that server is running Apache SUID,
meaning that your own account runs the web server.
In most cases Apache is run by its own account.

In this case, if you will set the permissions for the file to 755, the file
can be viewed by other accounts from that server.
This is because 755 means read and execute permissions for the group and for
everyone.


Teddy,
Teddy's Center: http://teddy.fcc.ro/
Email: [EMAIL PROTECTED]

- Original Message -
From: "Matthew Baranowski" <[EMAIL PROTECTED]>
To: "Octavian Rasnita" <[EMAIL PROTECTED]>; "Larry Brown"
<[EMAIL PROTECTED]>; "MySQL List" <[EMAIL PROTECTED]>
Sent: Monday, January 06, 2003 8:10 PM
Subject: Re: Hiding the password


Hello Teddy:

Could you please be a bit more demonstrative? If I have a module in at Web
address on a Apache server with permissions 700, (Warren said he has his
scipt to 755, I think) how exactly do you believe a site visitor can access
the text of the script? Or how do you think another system user could access
the text of the script?

I am beginning to think that you operate in a strange, unsecured Web
environment. Please lay out a general set of steps by which someone could
gain access to the text of a Perl script on a Web server with 700 or 755
permissions.

Thanks,

Matt Baranowski


- Original Message -
From: "Octavian Rasnita" <[EMAIL PROTECTED]>
To: "Larry Brown" <[EMAIL PROTECTED]>; "MySQL List"
<[EMAIL PROTECTED]>
Sent: Sunday, January 05, 2003 10:33 PM
Subject: Re: Hiding the password


> No, we are not talking about the staff of the hosting company.
>
> The hosting company runs a single Apache server on a single account on
that
> server for all sites that are sitting on that computer.
> If the user that runs the web server has access to your files, this means
> that everyone has access.
>
>
> Teddy,
> Teddy's Center: http://teddy.fcc.ro/
> Email: [EMAIL PROTECTED]
>
> - Original Message -
> From: "Larry Brown" <[EMAIL PROTECTED]>
> To: "MySQL List" <[EMAIL PROTECTED]>
> Sent: Saturday, January 04, 2003 9:50 PM
> Subject: RE: Hiding the password
>
>
> First, why are we conceding that "everyone can find out your id and
> password"?  Your hosting company has your site separated from other
> customers' sites right?  So we are just talking about the development team
> for your site being privy to this information.
>
> Second, if you are referring to the staff of the hosting company, you
can't
> avoid their ability to access data via your login scripts period.  As far
as
> I know they can view all of your communication with the MySQL database and
> can get that information.  If you want tight security hosting it yourself
is
> a must in my view.
>
> Larry S. Brown
> Dimension Networks, Inc.
> (727) 723-8388
>
> -Original Message-
> From: wcb [mailto:[EMAIL PROTECTED]]
> Sent: Saturday, January 04, 2003 1:51 PM
> To: Mark; MySQL
> Subject: Re: Hiding the password
>
> It isn't at all difficult to grasp.  Please carefully (and exercising a
> certain amount of patience) read my post and the previous post upon which
my
> post was based.  We are acknowledging that EVERYONE can find out your id
and
> password.  The question reformulated is:
>
> "Given that one's MySql environment may not be accessible in terms of
privs
> (which is the case for a lot of people, who are paying for hosting by a
> third party) and given that we CAN'T hide the id/password combination, is
> the standard arrangement that hosts use (which is to ensure that only
> localhost can access the database) adequate to prevent people from doing
> unwanted things in your database?  NOTE that I'm assuming that one has a
> script on localhost, and all users are from another domain, and also
> assuming that the script is properly set up to constrain the activities of
> users, does it even matter that people can determine the id/password
> combination??"
>
> Thanks for patient responses.
>
> Cheers!
>
> -warren
>
>
>
> >
> > > Perhaps gurus can comment on what I'm suggesting here - if the
database
> is
> > > set up so that only "localhost" can access it, then you can use a php
or
> > > PERL script to allow people from elsewhere to cruise in and make
queries
> > > as your script allows.
> >
> > Why is this so difficult to grasp? As I, and many others, have pointed
> out,
> > repeatedly, it does not matter how many layers you wrap around your
> > password-retrieval code, as soon as you make the end-result
> > accessible/readable by your web-CGI, you have done just that: made the
> > user/password accessible by your web-daemon -- hence, made it accessible
> to
> > everyone with access to your web-server.
> >
> > And no, adding some sort of access-control within your CGI is equally
> > useless: as a user being hosted on your web-server I would not bother to
> run
> > your CGI, but simply copy it for ocular inspection. :)
> >
> > > Certainly I'd apprecia

Writing MySQL programs in Ruby

[Paul DuBois]

I've written an article on using Ruby to write programs that
access MySQL databases.  The article is available here:

http://www.kitebird.com/articles/


I'd be interested in feedback, especially corrections if there
are errors.

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

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

Need book, but have basic question ??

[Charles Mabbott]

I have downloaded the ODBC driver and while I am fumbling [still 
reading] about that the reference it makes if for *.MDB similar 
extensions.  I have cruised through the on-line manual and still 
have no idea how to create one of those type extensions with a 
MySql database.  

I am sure it is in there, but darned if I can find it  Or 
am I doing something I shouldn't?

Chuck
=
"Incoming rounds always have the right of way"
  - Rules of War




-
Before posting, please check:
   http://www.mysql.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: Re[2]: mysql 4.0.8- crash on TCP connection

[Gelu Gogancea]

Functions gethostby* ,from glibc, work directly with the /etc/hosts file.If
this functions didn't find an entry for the client, will be crashed.
I try to find in the Andrew e-mail if he has installed the glibc 2.2.x but i
don't see nothing about it.What i see is, he use 2.95.x which is declared by
MySQL like unstable.In this context can be a coincidence what is happened.
Also i don't find difference in MYSQL daemon source code(hostname.cc)
between 4.0.7 and 4.0.8.
Regards,

Gelu
_
G.NET SOFTWARE COMPANY

Permanent e-mail address : [EMAIL PROTECTED]
  [EMAIL PROTECTED]
- Original Message -
From: "Christopher E. Brown" <[EMAIL PROTECTED]>
To: "Gelu Gogancea" <[EMAIL PROTECTED]>
Cc: "Andrew Sitnikov" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Thursday, January 09, 2003 7:48 PM
Subject: Re: Re[2]: mysql 4.0.8- crash on TCP connection


> On Thu, 9 Jan 2003, Gelu Gogancea wrote:
>
> > Hi,
> > This is a glibc problem.In this case you can start mysql daemon with
option
> > "--skip-name-resolve" and in this situation is no need to add the IP
address
> > of every client in hosts file.The disadvantage is that the client can
not
> > connect to the server using host alias.
> > Regards,
> >
> > Gelu
>
>
> Could you clarify "This is a glibc problem"?  A known standard glibc
> 2.2.5 against which every other piece of software functions correctly,
> even when receiving null returns on reverse lookups, but 4.0.8 (both
> precompiled binary and locally build) crashes on a null return.
> Specially when (according to other reports) 4.0.7 functions correctly.
>
>
> --
> I route, therefore you are.
>
>


-
Before posting, please check:
   http://www.mysql.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: WHERE clouse

[Andrea Forghieri]

 
 All the test I've made shows that the first query is faster .

I can't believe my eyes.

Andrea

-
Before posting, please check:
   http://www.mysql.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: Re[2]: mysql 4.0.8- crash on TCP connection

[Christopher E. Brown]

On Thu, 9 Jan 2003, Gelu Gogancea wrote:

> Hi,
> This is a glibc problem.In this case you can start mysql daemon with option
> "--skip-name-resolve" and in this situation is no need to add the IP address
> of every client in hosts file.The disadvantage is that the client can not
> connect to the server using host alias.
> Regards,
>
> Gelu


Could you clarify "This is a glibc problem"?  A known standard glibc
2.2.5 against which every other piece of software functions correctly,
even when receiving null returns on reverse lookups, but 4.0.8 (both
precompiled binary and locally build) crashes on a null return.
Specially when (according to other reports) 4.0.7 functions correctly.


-- 
I route, therefore you are.


-
Before posting, please check:
   http://www.mysql.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: Copying MySql database to others

[Frank Peavy]

Thanks everyone for your input.

Stefan,

> >From, within phpMyAdmin?

1. In the left frame, choose the database you want to backup / copy.
2. Click the EXPORT tab in the right frame.
3. Choose the tables in the database you want to backup.
4. Choose "Structure and data".
5. Check "Enclose table and field names with backquotes" if your table
or column names might contain special characters (like ä, ö, ü).
6. Check "Save as file".
7. Click "Go".


Since I have limited command line access (ISP hosted database), I will 
probably have to use phpMyAdmin.

Part of my logic was to copy my MySql database to another MySql database, 
so that I can use the second database for QA purposes.

When I perform the Export as above, does that make a copy in another 
database or is that to an  external file? If it is an external file, would 
I have to Import it to another database that I create?


-
Before posting, please check:
  http://www.mysql.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.8 crashes and refuses to restart after crash.

[Diana Soares]

I'm not shure, but i think that at least your read_buffer_size and
sort_buffer_size values are too high!

> key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 3415663 K
> bytes of memory

How much memory does that machine have ?
You can modify those values in your mysql configuration file (my.cnf).

On Thu, 2003-01-09 at 05:42, [EMAIL PROTECTED] wrote:
> >Description:
> I installed mysql 4.0.8 from RPMs on a machine running RedHat 7.3 
> (fresh install. I did not install the mysql rpms that come with RH 7.3)
> mysql ran for a while (few hours) and then started to crash in very fast
> cycles (about 1/sec)
> This mysql instance is a slave for a 3.23.53a-Max server. Has at 
> this point almost no other queries but data transfer to setup the slave
> (e.g. load table from master...)
> >How-To-Repeat:
> whenever I start mysql using 'service mysql start', it will crash
> immidiatly.
> 
> error log:
> 
> 030109 00:18:31  mysqld restarted
> 030109  0:18:31  InnoDB: Started
> /usr/sbin/mysqld: ready for connections
> 030109  0:18:31  Slave I/O thread: connected to master 'repl@host:3306',  
>replication started in log 'host-bin.003' at position 84914753
> 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=209715200
> read_buffer_size=209711104
> sort_buffer_size=209715192
> 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 = 3415663 K
> bytes of memory
> Hope that's ok; if not, decrease some variables in the equation.
> 
> thd=0x873a3a8
> Attempting backtrace. You can use the following information to find out
> where mysqld died. If you see no messages after this, something went
> terribly wrong...
> Cannot determine thread, fp=0xbfe5f2b8, backtrace may not be correct.
> Stack range sanity check OK, backtrace follows:
> 0x807328a
> 0x82834b8
> 0x80b36ce
> 0x80b408e
> 0x80ecaa1
> 0x80edb61
> 0x8280c6c
> 0x82b620a
> New value of fp=(nil) failed sanity check, terminating stack trace!
> Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and follow 
>instructions on how to resolve the stack trace. Resolved
> stack trace is much more helpful in diagnosing the problem, so please do 
> resolve it
> Trying to get some variables.
> Some pointers may be invalid and cause the dump to abort...
> thd->query at (nil)  is invalid pointer
> thd->thread_id=2
> 
> Successfully dumped variables, if you ran with --log, take a look at the
> details of what thread 2 did to cause the crash.  In some cases of really
> bad corruption, the values shown above may be invalid.
> 
> The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains
> information that should help you find out what is causing the crash.
> 
> Number of processes running now: 0
> 030109 00:18:33  mysqld restarted
> 
> 
> 
> 
> >Fix:
> sorry. no fix at this point.
> 
> >Submitter-Id:
> >Originator:  root
> >Organization:
>  SANS Inst.
> >MySQL support: [none | licence | email support | extended email support ]
> >Synopsis:mysql 4.0.8 crashes 
> >Severity:critical
> >Priority:  high
> >Category:mysql
> >Class:   sw-bug
> >Release: mysql-4.0.8-gamma (Official MySQL RPM)
> 
> >C compiler:2.95.3
> >C++ compiler:  2.95.3
> >Environment:
>   RedHat 7.3 , dual 1 GHz P-III, 4 GByte RAM
> System: Linux hostname 2.4.18-3smp #1 SMP Thu Apr 18 07:27:31 EDT 2002 i686 unknown
> Architecture: i686
> 
> Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
> GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs
> gcc version 2.96 2731 (Red Hat Linux 7.3 2.96-110)
> Compilation info: CC='gcc'  CFLAGS='-O6 -fno-omit-frame-pointer -mpentium'  
>CXX='gcc'  CXXFLAGS='-O6 -fno-omit-frame-pointer-felide-constructors 
>-fno-exceptions -fno-rtti -mpentium'  LDFLAGS=''  ASFLAGS=''
> LIBC: 
> lrwxrwxrwx1 root root   13 Jan  7 10:56 /lib/libc.so.6 -> 
>libc-2.2.5.so
> -rwxr-xr-x1 root root  1260480 Apr 15  2002 /lib/libc-2.2.5.so
> -rw-r--r--1 root root  2310808 Apr 15  2002 /usr/lib/libc.a
> -rw-r--r--1 root root  178 Apr 15  2002 /usr/lib/libc.so
> Configure command: ./configure '--disable-shared' 
>'--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' 
>'--without-berkeley-db' '--with-innodb' '--without-vio' '--without-openssl' 
>'--enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql' 
>'--with-unix-socket-path=/var/

Re: SELECT DISTINCT *

[Paul DuBois]

At 7:54 -0800 1/9/03, [EMAIL PROTECTED] wrote:

Hey all;

I am trying to work on a query that has SELECT DISTINCT * ..

This does not work. While there is no error, the where clause causes 
repeats. I
do not really expect this to work, as DISTINCT needs a column to work with.

No, it doesn't.   SELECT DISTINCT * should remove duplicates.


 I
am trying to avoid the workaround of putting in every column from the table in
the select statement, so does anyone have any idea on how to keep the same row
from coming up twice while still being able to use SELECT * ??

Mike Hillyer



-
Before posting, please check:
  http://www.mysql.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: Re[2]: mysql 4.0.8- crash on TCP connection

[Gelu Gogancea]

Hi,
This is a glibc problem.In this case you can start mysql daemon with option
"--skip-name-resolve" and in this situation is no need to add the IP address
of every client in hosts file.The disadvantage is that the client can not
connect to the server using host alias.
Regards,

Gelu
_
G.NET SOFTWARE COMPANY

Permanent e-mail address : [EMAIL PROTECTED]
  [EMAIL PROTECTED]
- Original Message -
From: "Andrew Sitnikov" <[EMAIL PROTECTED]>
To: "Christopher E. Brown" <[EMAIL PROTECTED]>
Cc: "Gelu Gogancea" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Thursday, January 09, 2003 7:19 PM
Subject: Re[2]: mysql 4.0.8- crash on TCP connection


> Hello Christopher,
>
> CEB> This sounds like what I just submitted a bug report for.  Connections
> CEB> to 4.0.8 (compiled locally or binary distro) cause a server crash if
> CEB> the IP of the client is not resolvable in DNS.
> Yes ! I really have crash only when client(user) not has DNS records !
> But with 4.0.7 this client(user) working properly.
>
> CEB> One can add an entry to the hosts file for certain IPs to stop this,
> CEB> however this still leaves the fact that ANY IP that can connect to
the
> CEB> server can crash it if there is no reverse entry.
>
>
> CEB>  --
> CEB> I route, therefore you are.
>
>
>
>
> Best regards,
>  Andrew Sitnikov
>  e-mail : [EMAIL PROTECTED]
>  GSM: (+372) 56491109
>
>


-
Before posting, please check:
   http://www.mysql.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: May I raise a question?

[Diana Soares]

Your mysql server does not seem to be running...
Did you execute safe_mysqld ?

Check the "Post installation setup" chapter from the manual.
http://www.mysql.com/doc/en/Post-installation.html


On Thu, 2003-01-09 at 01:16, [EMAIL PROTECTED] wrote:
> Dear Editor,
> 
> After installed the MySQL version 3.23.53, I tried to test the MySQL by
> typing the following command, relevant error message appears, please advise
> how to fix.
> 
> Command: Display Error Message:
> 
> mysqlshowmysqlshow: Can't connect to MySQL server on
> 'localhost' (10061)
> 
> mysqladmin CREATE test   mysqladmin: connect to server at 'localhost' failed
>  error: 'Can't connect to MySQL server on
> 'localhost' (10061)'
>  Check that mysqld is running on localhost and that
> the port is 3306.
>  You can check this by doing 'telnet localhost 3306'
> 
> mysql test   ERROR 2003: Can't connect to MySQL server on
> 'localhost' (10061)
> 
> telnet localhost 3306Connecting To localhost...Could not open a
> connection to host on port 3306 : Connect failed
> 
> Best rgds,
> 
> Seed Chan
> 
> 
> 
> Seed Chan
> System Services Management
> Branch Services
> * 2962 7431
> *  29173503 / 29173504
> * [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
> 
-- 
Diana Soares


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

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

Re: Version 4 "safe" to use?

[Paul DuBois]

At 13:51 -0300 1/9/03, Maximo Migliari wrote:

What were the main benefits that you noticed straight away before 
adapting your code to use the new features that MySQL 4 offers?
Does the query cache make a big difference?

Oh, yes.  It does. :-)


  Do you have any benchmarks, even off your head?

Maximo.

At 09:47 8/1/2003 -0800, you wrote:

On Wed, Jan 08, 2003 at 09:00:51AM -0700, David Rock wrote:


 Are many people using MySQL version 4 yet?  I'm interested in it
 because I want to use it's SSL capabilities but would like to hear
 that people are not having trouble with it first.


I've had very good luck with MySQL 4 in several high-profile
environments.  We began using it in live envrionments back in August
and have contintued to spread MySQL 4 since then.

Jeremy
--
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

MySQL 3.23.51: up 24 days, processed 830,915,772 queries (392/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

Re: Creating MySQL databases and tables via Perl/DBI

[rich allen]

the example will connect to the local mySQL server and create a new 
database named 'dbname"

#!/usr/bin/perl -w
use DBI;
$dbh = DBI->connect( "dbi:mysql:", 'user', 'password', {
	PrintError => 0 } ) or die "Can\"t connect to database: 
$DBI::errstr\n";
$dbh->do("create database dbname");
$dbh->disconnect or warn "Disconnection failed: $DBI::errstr\n";
On Wednesday, January 8, 2003, at 12:04 PM, Ken Hilliard wrote:

I'd like to be able to create a new database using Perl/DBI instead of 
using
the MySQL command line (and other) utilities. It looks like you can 
create a
new database with the driver handle but I can't seem to make it work.


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

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

Re: update auto increment

[Christian Kohlschütter]

Am Donnerstag, 9. Januar 2003 15:28 schrieb Csongor Fagyal:
> Hi,
>
> Sorry if this had been asked on this list previously.
>
> If you had an auto_increment field in a table (let's name it "id"),
> previously (in 3.23.x, 4.0.x) the following (legal) SQL statement
> resulted in a "duplicate key" error:
> UPDATE whatevertable SET id = id +1;
>
> Will this be fixed in 4.1.x? Can it be fixed? Is there a workaround?
>
> Regards,
> - Cs.

The following should already do what you want:
UPDATE whatevertable SET id = id + 1 ORDER BY id DESC;
-- 
Christian Kohlschütter
[EMAIL PROTECTED]

http://www.newsclub.de - Der Meta-Nachrichten-Dienst


-
Before posting, please check:
   http://www.mysql.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

remote connect crash

[Dmitry V. Sokolov]

Good day, 
could you help me to solve this problem?

MySQL server segmentation faults when remote mysql client
tries to connect on source and binary distributions. Local
client connect does not cause any problems whatsoever.

-
Before posting, please check:
   http://www.mysql.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

Demande d'inscription à mysql-france

[Notification de Yahoo! Groupes]

Bonjour,

Vous avez récemment demandé à vous inscrire au groupe 
mysql-france.  Comme vous êtes déjà membre de ce groupe,
votre demande ne sera pas prise en compte.

Pour toute question sur l'envoi de messages à ce groupe, 
veuillez envoyer un e-mail à [EMAIL PROTECTED]


Cordialement,

L'équipe support Yahoo! Groupes 


L'utilisation du service Yahoo! Groupes est soumise à l'acceptation des 
Conditions d'utilisation et de la Charte sur la vie privée, disponibles 
respectivement sur http://fr.docs.yahoo.com/info/utos.html et
http://fr.docs.yahoo.com/info/privacy.html

 




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

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

Re: Bug in foreign keys

[Rafal Jank]

> At 16:11 +0100 1/9/03, Rafal Jank wrote:
> >Hi!
> >I have two tables:
> >CREATE TABLE `pokoje` (
> >   `nrpok_p` char(10) NOT NULL default '',
> >   `nip_h` int(10) NOT NULL default '0',
> >   `lozka_p` char(2) default NULL,
> >   `tv_p` char(1) default NULL,
> >   `lazienka_p` char(1) default NULL,
> >   `cena_p` int(10) default NULL,
> >   `zaliczka_p` int(10) default NULL,
> >   PRIMARY KEY  (`nrpok_p`,`nip_h`),
> >   KEY `nip_h_index` (`nip_h`,`nrpok_p`),
> >   FOREIGN KEY (`nip_h`) REFERENCES `hotele` (`nip_h`) ON DELETE CASCADE
> >) TYPE=InnoDB;
> >
> >  CREATE TABLE `rezerwacje` (
> >   `id_r` int(10) NOT NULL default '0',
> >   `pesel_k` int(11) default NULL,
> >   `nip_h` int(10) NOT NULL default '0',
> >   `nrpok_p` char(10) NOT NULL default '',
> >   `data_r` date default NULL,
> >   `od_r` date default NULL,
> >   `do_r` date default NULL,
> >   `cena_r` int(10) default NULL,
> >   `zaliczka_r` int(5) default NULL,
> >   `zaplac_r` char(1) default NULL,
> >   `wplaczal_r` char(1) default NULL,
> >   PRIMARY KEY  (`id_r`),
> >   KEY `nip_h_index` (`nip_h`,`nrpok_p`)
> >) TYPE=InnoDB;
> >
> >Now, when I try:
> >alter table rezerwacje add foreign key (nip_h,nrpok_p) references
> >pokoje(nip_h,nrpok_p);
> >I get:
> >ERROR 1005: Can't create table './test/#sql-932_4.frm' (errno: 150)
> >
> >Why?
> 
> Circular foreign key relationships?  Removing the FOREIGN KEY
> clause from the definition of pokoje allows the ALTER TABLE to work.

They are not circular - there is another table hotele that I didn't put in
prevoius post:

 CREATE TABLE `hotele` (
  `nip_h` int(10) NOT NULL default '0',
  `nazwa_h` varchar(100) NOT NULL default '',
  `miasto_h` varchar(50) NOT NULL default '',
  `kodpocz_h` int(5) NOT NULL default '0',
  `adres_h` varchar(50) default NULL,
  `email_h` varchar(50) default NULL,
  `kontobank_h` int(32) default NULL,
  `wyprzedaz_h` varchar(10) default NULL,
  `standard_h` varchar(20) default NULL,
  `opis_h` varchar(200) default NULL,
  PRIMARY KEY  (`nip_h`)
) TYPE=InnoDB;


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

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

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

Error 1104 (too many records)

[Andrea Forghieri]

Hi,
I recently upgraded from 4.0.4 to 4.0.7 .
I keep getting the following error message when a executing a query:

ERROR 1104 at line xx : The SELECT would examine too many records and
probably take a long time. Check your WHERE and use SET OPTION
SQL_BIG_SELECTS=1 if the SELECT is ok.

The problem is that this query used to work perfectly with previous versions
of Mysql and that using SET OPTINION SQL_BIG_SELECTS=1 doesn't help :

Does anyone have a clue ?


Best Regards
Andrea Forghieri
Emmegi S.p.A.


-
Before posting, please check:
   http://www.mysql.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

Year data type

[speters]

It seems odd to me that the data type "Year", should only allow the limited
range of 1901-2155.
i understand that this is so that it will store in 1 byte, but whats the
point?
i wanted to use a year field for historical data, such as the year famous
people were born, such
as abe lincoln - 1809, but surprise surprise 1809 is unacceptable.

so i guess i dont see the point of the year datatype.

i would be interented in any insight as to why the year type is like this.

thanks,
sean peters
[EMAIL PROTECTED]



mysql, query



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

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

Re: Bug in foreign keys

[Paul DuBois]

At 16:11 +0100 1/9/03, Rafal Jank wrote:

Hi!
I have two tables:
CREATE TABLE `pokoje` (
  `nrpok_p` char(10) NOT NULL default '',
  `nip_h` int(10) NOT NULL default '0',
  `lozka_p` char(2) default NULL,
  `tv_p` char(1) default NULL,
  `lazienka_p` char(1) default NULL,
  `cena_p` int(10) default NULL,
  `zaliczka_p` int(10) default NULL,
  PRIMARY KEY  (`nrpok_p`,`nip_h`),
  KEY `nip_h_index` (`nip_h`,`nrpok_p`),
  FOREIGN KEY (`nip_h`) REFERENCES `hotele` (`nip_h`) ON DELETE CASCADE
) TYPE=InnoDB;

 CREATE TABLE `rezerwacje` (
  `id_r` int(10) NOT NULL default '0',
  `pesel_k` int(11) default NULL,
  `nip_h` int(10) NOT NULL default '0',
  `nrpok_p` char(10) NOT NULL default '',
  `data_r` date default NULL,
  `od_r` date default NULL,
  `do_r` date default NULL,
  `cena_r` int(10) default NULL,
  `zaliczka_r` int(5) default NULL,
  `zaplac_r` char(1) default NULL,
  `wplaczal_r` char(1) default NULL,
  PRIMARY KEY  (`id_r`),
  KEY `nip_h_index` (`nip_h`,`nrpok_p`)
) TYPE=InnoDB;

Now, when I try:
alter table rezerwacje add foreign key (nip_h,nrpok_p) references
pokoje(nip_h,nrpok_p);
I get:
ERROR 1005: Can't create table './test/#sql-932_4.frm' (errno: 150)

Why?


Circular foreign key relationships?  Removing the FOREIGN KEY
clause from the definition of pokoje allows the ALTER TABLE to work.





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




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

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

Re: mysql 4.0.8- crash on TCP connection

[Christopher E. Brown]

On Thu, 9 Jan 2003, Gelu Gogancea wrote:

> Hi,
> What OS you use ?
>
> Regards,
>
> Gelu
> _
> G.NET SOFTWARE COMPANY
>
> Permanent e-mail address : [EMAIL PROTECTED]
>   [EMAIL PROTECTED]
> - Original Message -
> From: "Andrew Sitnikov" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Wednesday, January 08, 2003 8:14 PM
> Subject: mysql 4.0.8- crash on TCP connection
>
>
> > Hello mysql,
> >
> >   I try use 4.0.8 (max & standard)in our production box,
> >   and it was crash every TCP connection, For 4.0.7 (standard) i has over
> 20 days uptime.


This sounds like what I just submitted a bug report for.  Connections
to 4.0.8 (compiled locally or binary distro) cause a server crash if
the IP of the client is not resolvable in DNS.  One can add an entry
to the hosts file for certain IPs to stop this, however this still
leaves the fact that ANY IP that can connect to the server can crash
it if there is no reverse entry.


 --
I route, therefore you are.


-
Before posting, please check:
   http://www.mysql.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: Resetting the auto_increment to start from 1

[Octavian Rasnita]

Ha ha, thank you for "page 558" but I am blind and I am reading the
electronic version of the book.
However, I think I've reached to page 558 because I've seen how to do it.

Teddy,
Teddy's Center: http://teddy.fcc.ro/
Email: [EMAIL PROTECTED]

- Original Message -
From: "Paul DuBois" <[EMAIL PROTECTED]>
To: "Octavian Rasnita" <[EMAIL PROTECTED]>; "MySQL" <[EMAIL PROTECTED]>
Sent: Wednesday, January 08, 2003 9:26 PM
Subject: Re: Resetting the auto_increment to start from 1


At 9:33 +0200 1/8/03, Octavian Rasnita wrote:
>Hi all,
>
>I've read the following in a MySQL book:
>
>   A special case of record deletion occurs when you clear out a table
>entirely using a DELETE with no WHERE clause:
>  DELETE FROM tbl_name;
>   In this case, the sequence counter may be reset to 1, even for table
types
>for which values normally are not reused (MyISAM and InnoDB). For those
>types, if you wish to delete all the records while maintaining the current
>sequence value, tell MySQL to perform a record-at-a-time delete by
including
>a WHERE clause that specifies some trivially true condition:
>  DELETE FROM tbl_name WHERE 1 > 0;
>
>---
>
>Well, I've tried that sql statement, but the auto_increment point of start
>was not reset to 1.

Note that the text in question says *may* be reset to 1.

To reset it for sure, use the statement at the top of page 558. :-)

>I use MySQL 4.05 under Windows 2000.
>
>Thanks.
>
>Teddy,
>Teddy's Center: http://teddy.fcc.ro/
>Email: [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: Hiding the password

[Octavian Rasnita]

I've read more messages from Apache users list and the Apache experts say
that if the web server is run by your own account this can create more
security problems.
I don't know what problems and I would also like to know but if someone
wants to know, you can subscribe to that list.

... or if someone knows, please tell us.

Teddy,
Teddy's Center: http://teddy.fcc.ro/
Email: [EMAIL PROTECTED]

- Original Message -
From: "Larry Brown" <[EMAIL PROTECTED]>
To: "MySQL List" <[EMAIL PROTECTED]>
Sent: Monday, January 06, 2003 8:23 PM
Subject: RE: Hiding the password


This is your problem.  Change hosting companies.  There should be multiple
accounts.  At least one for each site and nobody from any other site should
be able to log in and view your files.  It's called chroot and is the most
common method of separating customers of a hosting company.  If your
description is correct and there is only one account that all of the
customers use you will have no security what so ever.

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

-Original Message-
From: Octavian Rasnita [mailto:[EMAIL PROTECTED]]
Sent: Monday, January 06, 2003 1:34 AM
To: Larry Brown; MySQL List
Subject: Re: Hiding the password

No, we are not talking about the staff of the hosting company.

The hosting company runs a single Apache server on a single account on that
server for all sites that are sitting on that computer.
If the user that runs the web server has access to your files, this means
that everyone has access.


Teddy,
Teddy's Center: http://teddy.fcc.ro/
Email: [EMAIL PROTECTED]

- Original Message -
From: "Larry Brown" <[EMAIL PROTECTED]>
To: "MySQL List" <[EMAIL PROTECTED]>
Sent: Saturday, January 04, 2003 9:50 PM
Subject: RE: Hiding the password


First, why are we conceding that "everyone can find out your id and
password"?  Your hosting company has your site separated from other
customers' sites right?  So we are just talking about the development team
for your site being privy to this information.

Second, if you are referring to the staff of the hosting company, you can't
avoid their ability to access data via your login scripts period.  As far as
I know they can view all of your communication with the MySQL database and
can get that information.  If you want tight security hosting it yourself is
a must in my view.

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

-Original Message-
From: wcb [mailto:[EMAIL PROTECTED]]
Sent: Saturday, January 04, 2003 1:51 PM
To: Mark; MySQL
Subject: Re: Hiding the password

It isn't at all difficult to grasp.  Please carefully (and exercising a
certain amount of patience) read my post and the previous post upon which my
post was based.  We are acknowledging that EVERYONE can find out your id and
password.  The question reformulated is:

"Given that one's MySql environment may not be accessible in terms of privs
(which is the case for a lot of people, who are paying for hosting by a
third party) and given that we CAN'T hide the id/password combination, is
the standard arrangement that hosts use (which is to ensure that only
localhost can access the database) adequate to prevent people from doing
unwanted things in your database?  NOTE that I'm assuming that one has a
script on localhost, and all users are from another domain, and also
assuming that the script is properly set up to constrain the activities of
users, does it even matter that people can determine the id/password
combination??"

Thanks for patient responses.

Cheers!

-warren



>
> > Perhaps gurus can comment on what I'm suggesting here - if the database
is
> > set up so that only "localhost" can access it, then you can use a php or
> > PERL script to allow people from elsewhere to cruise in and make queries
> > as your script allows.
>
> Why is this so difficult to grasp? As I, and many others, have pointed
out,
> repeatedly, it does not matter how many layers you wrap around your
> password-retrieval code, as soon as you make the end-result
> accessible/readable by your web-CGI, you have done just that: made the
> user/password accessible by your web-daemon -- hence, made it accessible
to
> everyone with access to your web-server.
>
> And no, adding some sort of access-control within your CGI is equally
> useless: as a user being hosted on your web-server I would not bother to
run
> your CGI, but simply copy it for ocular inspection. :)
>
> > Certainly I'd appreciate comments on this by people in the know, because
> > it is an issue that so many people face...
>
> Perhaps those people should do what I do: create special MySQL users
> (@localhost), unprivileged to the max, with only very narrow SELECT
> privileges to the databases they are supposed to read data from, and use
> those users to access the MySQL server in your CGI.
>
> - Mark
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the 

Re: Hiding the password

[Octavian Rasnita]

Yes I know that Apache can be set to run using your own account, but most
hosts don't do that because I heard that this can create other security
problems.

Teddy,
Teddy's Center: http://teddy.fcc.ro/
Email: [EMAIL PROTECTED]

- Original Message -
From: "William R. Mussatto" <[EMAIL PROTECTED]>
To: "Octavian Rasnita" <[EMAIL PROTECTED]>
Cc: "Larry Brown" <[EMAIL PROTECTED]>; "MySQL List"
<[EMAIL PROTECTED]>
Sent: Monday, January 06, 2003 8:07 PM
Subject: Re: Hiding the password


On Mon, 6 Jan 2003, Octavian Rasnita wrote:

> Date: Mon, 6 Jan 2003 08:33:48 +0200
> From: Octavian Rasnita <[EMAIL PROTECTED]>
> To: Larry Brown <[EMAIL PROTECTED]>,
> MySQL List <[EMAIL PROTECTED]>
> Subject: Re: Hiding the password
>
> No, we are not talking about the staff of the hosting company.
>
> The hosting company runs a single Apache server on a single account on
that
> server for all sites that are sitting on that computer.
> If the user that runs the web server has access to your files, this means
> that everyone has access.
>
Its possible to configure a single virtual host to run as a different
user and group.  It still won't protect you from people at the hosting
company, but other hosting clients should be isolated.

>
> Teddy,
> Teddy's Center: http://teddy.fcc.ro/
> Email: [EMAIL PROTECTED]
>
> - Original Message -
> From: "Larry Brown" <[EMAIL PROTECTED]>
> To: "MySQL List" <[EMAIL PROTECTED]>
> Sent: Saturday, January 04, 2003 9:50 PM
> Subject: RE: Hiding the password
>
>
> First, why are we conceding that "everyone can find out your id and
> password"?  Your hosting company has your site separated from other
> customers' sites right?  So we are just talking about the development team
> for your site being privy to this information.
>
> Second, if you are referring to the staff of the hosting company, you
can't
> avoid their ability to access data via your login scripts period.  As far
as
> I know they can view all of your communication with the MySQL database and
> can get that information.  If you want tight security hosting it yourself
is
> a must in my view.
>
> Larry S. Brown
> Dimension Networks, Inc.
> (727) 723-8388
>
> -Original Message-
> From: wcb [mailto:[EMAIL PROTECTED]]
> Sent: Saturday, January 04, 2003 1:51 PM
> To: Mark; MySQL
> Subject: Re: Hiding the password
>
> It isn't at all difficult to grasp.  Please carefully (and exercising a
> certain amount of patience) read my post and the previous post upon which
my
> post was based.  We are acknowledging that EVERYONE can find out your id
and
> password.  The question reformulated is:
>
> "Given that one's MySql environment may not be accessible in terms of
privs
> (which is the case for a lot of people, who are paying for hosting by a
> third party) and given that we CAN'T hide the id/password combination, is
> the standard arrangement that hosts use (which is to ensure that only
> localhost can access the database) adequate to prevent people from doing
> unwanted things in your database?  NOTE that I'm assuming that one has a
> script on localhost, and all users are from another domain, and also
> assuming that the script is properly set up to constrain the activities of
> users, does it even matter that people can determine the id/password
> combination??"
>
> Thanks for patient responses.
>
> Cheers!
>
> -warren
>
>
>
> >
> > > Perhaps gurus can comment on what I'm suggesting here - if the
database
> is
> > > set up so that only "localhost" can access it, then you can use a php
or
> > > PERL script to allow people from elsewhere to cruise in and make
queries
> > > as your script allows.
> >
> > Why is this so difficult to grasp? As I, and many others, have pointed
> out,
> > repeatedly, it does not matter how many layers you wrap around your
> > password-retrieval code, as soon as you make the end-result
> > accessible/readable by your web-CGI, you have done just that: made the
> > user/password accessible by your web-daemon -- hence, made it accessible
> to
> > everyone with access to your web-server.
> >
> > And no, adding some sort of access-control within your CGI is equally
> > useless: as a user being hosted on your web-server I would not bother to
> run
> > your CGI, but simply copy it for ocular inspection. :)
> >
> > > Certainly I'd appreciate comments on this by people in the know,
because
> > > it is an issue that so many people face...
> >
> > Perhaps those people should do what I do: create special MySQL users
> > (@localhost), unprivileged to the max, with only very narrow SELECT
> > privileges to the databases they are supposed to read data from, and use
> > those users to access the MySQL server in your CGI.
> >
> > - Mark
> >
> >
> > -
> > Before posting, please check:
> >http://www.mysql.com/manual.php   (the manual)
> >http://lists.mysql.com/   (the list archive)
> >
> > To request this thread, e-mail <[EMAIL P

count(expr)

[Meena Vyavahare]

Here is my query-

Select col1, col2, count(*) as total, count(col3 < col4) as violations
from table1
Where condn
group by col1, col2

The result of this query shows same values at both the columns for total
and violations.
The count(expr) does not work

Thanks

Meena
---


-
Before posting, please check:
   http://www.mysql.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: Re[4]: mysql 4.0.8- crash on TCP connection

[Gelu Gogancea]

Hi,
Try to start mysql daemon with "--skip-innodb" and see if the problem
persist.I suppose is something wrong with your my.cnf configuration.
Regards,

Gelu
_
G.NET SOFTWARE COMPANY

Permanent e-mail address : [EMAIL PROTECTED]
  [EMAIL PROTECTED]
- Original Message -
From: "Andrew Sitnikov" <[EMAIL PROTECTED]>
To: "Gelu Gogancea" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, January 09, 2003 6:05 PM
Subject: Re[4]: mysql 4.0.8- crash on TCP connection


> Hello Gelu,
>
> GG> Hi Andrew,
> GG> I can not reproduce your case.For me work good.The only difference is
that i
> GG> use source distribution and you use a binary distribution.
> GG> Please take a look in mysql log and tell me what you see there.
> GG> Regards,
>
> 030108 20:12:09  mysqld started
> 030108 20:12:10  InnoDB: Started
> /usr/local/mysql/bin/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=402653184
> read_buffer_size=2093056
> sort_buffer_size=2097144
> max_used_connections=1
> max_connections=200
> threads_connected=1
> It is possible that mysqld could use up to
> key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections =
1211614 K
> bytes of memory
> Hope that's ok; if not, decrease some variables in the equation.
>
> thd=0x8704638
> Attempting backtrace. You can use the following information to find out
> where mysqld died. If you see no messages after this, something went
> terribly wrong...
> Cannot determine thread, fp=0xb397f608, backtrace may not be correct.
> Stack range sanity check OK, backtrace follows:
> 0x806f3bb
> 0x8269928
> 0x807724c
> 0x8077665
> 0x82670dc
> 0x829c67a
> New value of fp=(nil) failed sanity check, terminating stack trace!
> Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and follow
instructions on how to resolve the stack trace. Resolved
> stack trace is much more helpful in diagnosing the problem, so please do
> resolve it
> Trying to get some variables.
> Some pointers may be invalid and cause the dump to abort...
> thd->query at (nil)  is invalid pointer
> thd->thread_id=10
>
> Successfully dumped variables, if you ran with --log, take a look at the
> details of what thread 10 did to cause the crash.  In some cases of really
> bad corruption, the values shown above may be invalid.
>
> The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains
> information that should help you find out what is causing the crash.
>
> Number of processes running now: 0
> 030108 20:12:25  mysqld restarted
> 030108 20:12:26  InnoDB: Started
>
> >> Hello Gelu,
> >>
> >> GG> Hi,
> >> GG> What OS you use ?
> >> System: Linux gap 2.4.20-grsec #1 SMP Fri Dec 6 23:17:05 EET 2002 i686
> GG> unknown
> >> Architecture: i686
> >>
> >> Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake
> GG> /home/local/sitnikov/bin/gcc /home/local/sitnikov/bin/cc
> >> GCC: Reading specs from /usr/lib/gcc-lib/i486-suse-linux/2.95.3/specs
> >> gcc version 2.95.3 20010315 (SuSE)
> >> Compilation info: CC='gcc'  CFLAGS='-O2 -mcpu=pentiumpro'  CXX='gcc'
> GG> CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors'  LD
> >> FLAGS=''  ASFLAGS=''
> >> LIBC:
> >> -rwxr-xr-x1 root root  1384072 Oct  1 19:10 /lib/libc.so.6
> >> -rw-r--r--1 root root 25215016 Oct  1 18:56 /usr/lib/libc.a
> >> -rw-r--r--1 root root  178 Oct  1 18:56
/usr/lib/libc.so
> >>
> Best regards,
>  Andrew Sitnikov
>  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: Backups mechanism

[Fernando Grijalba]

You can do the same thing in Win32 using a vb script.  Just use the Run
command of the shell object to run mysqldump.

This is the way I back up our database to text files that get burnt into a
CD.

HTH

JFernando
** sql **

-Original Message-
From: David T-G [mailto:[EMAIL PROTECTED]]
Sent: January 7, 2003 15:49
To: mysql users
Cc: Jonas Widarsson
Subject: Re: Backups mechanism


-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Jonas --

...and then Jonas Widarsson said...
%
%  Hello world!

Hi!


% Every attempt I have made to find a decent way of backing up a database
% ends up with tons of reading to show hundreds of ways to do database
% backups.

Heh :-)  And you expect less from a mailing list?


%
% I want to know which way to do complete backups is most commonly used by
% professional users. (Windows and Linux)

I can't speak for Windows, but I can speak for Linux -- or at least count
as a voice.  There is, of course,, more than one way to do it :-)


%
% I have pleasant experience from Microsoft SQL Server 2000 where backung
% up is very confident, easy to understand and just a matter of a mouse
click.

"Gosh, that sounds terrible.  Only one way to back up?  Having to use a
mouse?  Who would want that?"

Perhaps that casts some light on the UNIX "philosophy": have small tools
that do few things and do them well, and then interconnect them to make
whatever you need.


%
% Is there any similarily convenient way to do this with mysql, for example:
% * a shell script (windows / Linux) that works as expected without days
% of configuration?
% * scheduled backups, complete or differential?
% * GUI-solution ??? (mysql control center does not even mention the word
% backup)

I don't know about similar, but definitely convenient.  A little shell
script to do your backup (using whatever means you like; more in a
moment) and a cron job (think "task scheduler" if you are not familiar
with cron) to run the script periodically.

So you have to figure out how you want to back up the database.  You
might generate an sql dump of the whole thing with mysqldump and save
the results to a file (on disk or on a tape).  You might connect to the
database, lock the tables, copy the filesystem files themselves, and then
unlock.  You might store the files on mirrored disks and then temporarily
break the mirror and back up from the quiet copy.  You might set up a
slave server to replicate the database and then back up *that* version
instead.

Our needs are simple; I use mysqldump.  I've created a database user
(let's call it backups for this example, because I didn't really) with
SELECT privileges on everything so that it can read every column of every
row of every table of every database in the system.  Then I just run

  OUT=`date +%Y-%m-%d`
  mysqldump --user=backups --opt --all-databases | \
gzip -9 > \
/path/to/backups/dump.$OUT.sql.gz

once a day and there we go.  I won't write a password in a shell script
file, but I *would* like to have a secure way of authenticating the user,
like "only root@localhost can connect as dumper@localhost" through some
certificate or the like, but I haven't gotten around to that yet.


%
% Backup is such an important issue. Why does it seem like it is something
% that the developers don't care about?

Well, there again you're crossing functionality...  The developers care
about the database engine and how to get data in and out quickly; backup
is an entirely separate issue.  Yes, it's important, but it's very much a
different animal -- and so it shouldn't be part of the same package.


HTH & HAND

mysql query,
:-D
- --
David T-G  * There is too much animal courage in
(play) [EMAIL PROTECTED] * society and not sufficient moral courage.
(work) [EMAIL PROTECTED]  -- Mary Baker Eddy, "Science and Health"
http://justpickone.org/davidtg/  Shpx gur Pbzzhavpngvbaf Qrprapl Npg!

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.0.7 (FreeBSD)

iD8DBQE+Gz1FGb7uCXufRwARAibPAKDm6kfYnl1RzI107xS/juKLQBwfQgCgtFOf
MbIB7v9bZgjMRJOjKAtjznU=
=+W9p
-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



-
Before posting, please check:
   http://www.mysql.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 easy delete old records from sql database

[Paul DuBois]

At 13:18 +0200 1/9/03, Putte Koivisto wrote:

Is there any easy way to delete records from database, wich logs user
activity at website? I mean, if there is somekind of limit for rows in
table. And if limit is exceeded, older rows will be automaticly deleted, or
even better, moved to another table.
Is there any query to make this happen?


No.  You might want to schedule a cron job that will do this periodically.
At least then it will be automatic, which seems to be your primary goal.



Sincerely,

Putte Koivisto




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

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

Re: update auto increment

[Paul DuBois]

At 15:28 +0100 1/9/03, Csongor Fagyal wrote:

Hi,

Sorry if this had been asked on this list previously.

If you had an auto_increment field in a table (let's name it "id"), 
previously (in 3.23.x, 4.0.x) the following (legal) SQL statement 
resulted in a "duplicate key" error:
UPDATE whatevertable SET id = id +1;

It's syntactically legal, but the error makes perfect sense.  If you have id
values of 1 and 2 in the table, updating 1 to 2 cannot work, given
that there is already an id value of 2.



Will this be fixed in 4.1.x? Can it be fixed? Is there a workaround?


As of MySQL 4.0.0, you can use ORDER BY with UPDATE, which may allow you
to solve your problem by updating the record beginning with the largest
id value and working backward:

UPDATE tbl_name SET id = id + 1 ORDER BY id DESC;



Regards,
- Cs.



-
Before posting, please check:
  http://www.mysql.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[4]: mysql 4.0.8- crash on TCP connection

[Andrew Sitnikov]

Hello Gelu,

GG> Hi Andrew,
GG> I can not reproduce your case.For me work good.The only difference is that i
GG> use source distribution and you use a binary distribution.
GG> Please take a look in mysql log and tell me what you see there.
GG> Regards,

030108 20:12:09  mysqld started
030108 20:12:10  InnoDB: Started
/usr/local/mysql/bin/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=402653184
read_buffer_size=2093056
sort_buffer_size=2097144
max_used_connections=1
max_connections=200
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1211614 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x8704638
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xb397f608, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x806f3bb
0x8269928
0x807724c
0x8077665
0x82670dc
0x829c67a
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and follow 
instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do 
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at (nil)  is invalid pointer
thd->thread_id=10

Successfully dumped variables, if you ran with --log, take a look at the
details of what thread 10 did to cause the crash.  In some cases of really
bad corruption, the values shown above may be invalid.

The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains
information that should help you find out what is causing the crash.

Number of processes running now: 0
030108 20:12:25  mysqld restarted
030108 20:12:26  InnoDB: Started

>> Hello Gelu,
>>
>> GG> Hi,
>> GG> What OS you use ?
>> System: Linux gap 2.4.20-grsec #1 SMP Fri Dec 6 23:17:05 EET 2002 i686
GG> unknown
>> Architecture: i686
>>
>> Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake
GG> /home/local/sitnikov/bin/gcc /home/local/sitnikov/bin/cc
>> GCC: Reading specs from /usr/lib/gcc-lib/i486-suse-linux/2.95.3/specs
>> gcc version 2.95.3 20010315 (SuSE)
>> Compilation info: CC='gcc'  CFLAGS='-O2 -mcpu=pentiumpro'  CXX='gcc'
GG> CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors'  LD
>> FLAGS=''  ASFLAGS=''
>> LIBC:
>> -rwxr-xr-x1 root root  1384072 Oct  1 19:10 /lib/libc.so.6
>> -rw-r--r--1 root root 25215016 Oct  1 18:56 /usr/lib/libc.a
>> -rw-r--r--1 root root  178 Oct  1 18:56 /usr/lib/libc.so
>>
Best regards,
 Andrew Sitnikov 
 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

SELECT DISTINCT *

[squeebee]

Hey all;

I am trying to work on a query that has SELECT DISTINCT * ..

This does not work. While there is no error, the where clause causes repeats. I 
do not really expect this to work, as DISTINCT needs a column to work with. I 
am trying to avoid the workaround of putting in every column from the table in 
the select statement, so does anyone have any idea on how to keep the same row 
from coming up twice while still being able to use SELECT * ??

Mike Hillyer




-
Before posting, please check:
   http://www.mysql.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: Version 4 "safe" to use?

[Maximo Migliari]

What were the main benefits that you noticed straight away before adapting 
your code to use the new features that MySQL 4 offers?
Does the query cache make a big difference?  Do you have any benchmarks, 
even off your head?

Maximo.

At 09:47 8/1/2003 -0800, you wrote:
On Wed, Jan 08, 2003 at 09:00:51AM -0700, David Rock wrote:
>
> Are many people using MySQL version 4 yet?  I'm interested in it
> because I want to use it's SSL capabilities but would like to hear
> that people are not having trouble with it first.

I've had very good luck with MySQL 4 in several high-profile
environments.  We began using it in live envrionments back in August
and have contintued to spread MySQL 4 since then.

Jeremy
--
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

MySQL 3.23.51: up 24 days, processed 830,915,772 queries (392/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

MySQL4 SSL certificate required?

[David Rock]

Hello,
  Can someone give me a some overview information regarding using SSL with
MySQL version 4? I want to setup a 128bit SSL connection between our website
hosted at an ISP and our MySQL (on FreeBSD) server which is hosted in our
office. Basically I want the same level of security that we currently have
between a web browser and our website. Does this require us to purchase
another certificate from a CA similar to the one we already have installed
at our ISP or can MySQL use the same one? Is a certificate even needed to do
this? Do we need to install a certificate on the MySQL server? Any guidance
is greatly appreciated.

Thanks,
David Rock

-
Before posting, please check:
   http://www.mysql.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: bug submitted (non-unique indicies in heap tables do not work correctly)

[Aaron Krowne]

Cute =)

(MySQL sql query queries smallint =)

Aaron Krowne

On Thu, Jan 09, 2003 at 04:36:12PM +0100, [EMAIL PROTECTED] wrote:
> 
> Your message cannot be posted because it appears to be either spam or
> simply off topic to our filter. To bypass the filter you must include
> one of the following words in your message:
> 
> sql,query,queries,smallint
> 
> If you just reply to this message, and include the entire text of it in the
> reply, your reply will go through. However, you should
> first review the text of the message to make sure it has something to do
> with MySQL. Just typing the word MySQL once will be sufficient, for example.
> 
> You have written the following:
> 
> Hi,
> 
> I just submitted a bug with the parenthesized title, but I don't think
> my "from" address was correct (akrowne instead of [EMAIL PROTECTED]), so I
> am posting this message to connect myself with the bug report.  Did this
> report make it in?  Thanks,
> 
> Aaron Krowne
> 

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

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

Bug in foreign keys

[Rafal Jank]

Hi!
I have two tables:
CREATE TABLE `pokoje` (
  `nrpok_p` char(10) NOT NULL default '',
  `nip_h` int(10) NOT NULL default '0',
  `lozka_p` char(2) default NULL,
  `tv_p` char(1) default NULL,
  `lazienka_p` char(1) default NULL,
  `cena_p` int(10) default NULL,
  `zaliczka_p` int(10) default NULL,
  PRIMARY KEY  (`nrpok_p`,`nip_h`),
  KEY `nip_h_index` (`nip_h`,`nrpok_p`),
  FOREIGN KEY (`nip_h`) REFERENCES `hotele` (`nip_h`) ON DELETE CASCADE
) TYPE=InnoDB;

 CREATE TABLE `rezerwacje` (
  `id_r` int(10) NOT NULL default '0',
  `pesel_k` int(11) default NULL,
  `nip_h` int(10) NOT NULL default '0',
  `nrpok_p` char(10) NOT NULL default '',
  `data_r` date default NULL,
  `od_r` date default NULL,
  `do_r` date default NULL,
  `cena_r` int(10) default NULL,
  `zaliczka_r` int(5) default NULL,
  `zaplac_r` char(1) default NULL,
  `wplaczal_r` char(1) default NULL,
  PRIMARY KEY  (`id_r`),
  KEY `nip_h_index` (`nip_h`,`nrpok_p`)
) TYPE=InnoDB;

Now, when I try:
alter table rezerwacje add foreign key (nip_h,nrpok_p) references
pokoje(nip_h,nrpok_p);
I get:
ERROR 1005: Can't create table './test/#sql-932_4.frm' (errno: 150)

Why?



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

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

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

re: Re: sub select

[Egor Egorov]

On Thursday 09 January 2003 09:32, Sal wrote:

> Thaks 4 ur info. Where can I get MySQL 4.1.0.

You can compile it from development source tree:
http://www.mysql.com/doc/en/Installing_source_tree.html



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




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

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

re: How to easy delete old records from sql database

[Egor Egorov]

On Thursday 09 January 2003 13:18, Putte Koivisto wrote:

> Is there any easy way to delete records from database, wich logs user
> activity at website? I mean, if there is somekind of limit for rows in
> table. And if limit is exceeded, older rows will be automaticly deleted, or
> even better, moved to another table.
> Is there any query to make this happen?

You should control it by yourself. Write script that will  delete/move old 
records from the table. 




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




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

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

re: May I raise a question?

[Victoria Reznichenko]

On Thursday 09 January 2003 03:16, seed dot chan at citicorp dot com wrote:

> After installed the MySQL version 3.23.53, I tried to test the MySQL by
> typing the following command, relevant error message appears, please advise
> how to fix.
>
> Command: Display Error Message:
>
> mysqlshowmysqlshow: Can't connect to MySQL server on
> 'localhost' (10061)
>
> mysqladmin CREATE test   mysqladmin: connect to server at 'localhost'
> failed error: 'Can't connect to MySQL server on
> 'localhost' (10061)'
>  Check that mysqld is running on localhost and that
> the port is 3306.
>  You can check this by doing 'telnet localhost
> 3306'
>
> mysql test   ERROR 2003: Can't connect to MySQL server on
> 'localhost' (10061)

Seems your MySQL server is not running. Just run it ;)



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





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

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

4.0.8 doesn't compile with --without-innodb and --without-query-cache options

[Grisha Mokhin]

Using Linux Slackware and gcc 3.2.1.

Here is the compiler report:

make[4]: Entering directory `/home/local/src/mysql-4.0.8-gamma/sql'
g++ -DMYSQL_SERVER -DDEFAULT_MYSQL_HOME="\"/usr/local/mysql\""
-DDATADIR="\"/usr/local/mysql/var\""
-DSHAREDIR="\"/usr/local/mysql/share/mysql\"" -DHAVE_CONFIG_H -I.
-I. -I.. -I./../include -I./../regex -I. -I../include -I. -O3
-DDBUG_OFF -fno-implicit-templates -fno-exceptions -fno-rtti -c -o
ha_innodb.o `test -f ha_innodb.cc || echo './'`ha_innodb.cc

In file included from ha_innodb.cc:31:
sql_cache.h:380: friend declaration requires class-key, i.e. `friend void'
sql_cache.h:380: invalid type `void' declared `friend'
sql_cache.h:381: friend declaration requires class-key, i.e. `friend void'
sql_cache.h:381: invalid type `void' declared `friend'
sql_cache.h:404: declaration does not declare anything
sql_cache.h:405: declaration does not declare anything
make[4]: *** [ha_innodb.o] Error 1

The problem can be fixed by ifdef'ing the inclusion of the header in
ha_innodb.cc:

+#ifdef HAVE_QUERY_CACHE
 #include "sql_cache.h"
+#endif

After that everything builds just fine, but it seems ha_innodb.cc
needn't be compiled at all when --without-innodb option is
specified.

Please cc me in the answers to this email, as I'm not subscribed to
this list.

Regards,
Grisha



-
Before posting, please check:
   http://www.mysql.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

ANN: EMS MySQL Manager 1.96 released

[Igor Brynskich]

EMS HiTech company is announcing the next version (1.96) of MySQL
Manager -- A Powerful MySQL Administration and Development Tool for
Windows95/98/ME/NT/2000/XP.

You can download the latest version from
http://www.mysqlmanager.com/download.phtml

We're also announcing a special offer! If you purchase MySQL Manager
before the official release of version 2.0 (either in a bundle or
separately) you will get the new version absolutely FREE. The official
release of MySQL Manager v2.0 is planned on February 03, 2003. The cost
of the 2.0 version will be $135. So buy now and save your money!
http://www.mysqlmanager.com/purchase.phtml


What's new in version 1.96?

1. The Visual Query Builder engine was updated to the latest version of
EMS Query Builder component suite
(http://www.ems-hitech.com/querybuilder/). Some improvements and
corrections added. (*)

2. An ability to show only connected databases in the drop-down menus
was added to all object editors and database tools. This option can be
enabled by the corresponding check box on the Tools page of the
Environment Options dialog.

3. Table Editor: total record count in a table now is displayed in the
status bar of the Data View. Navigating to the last portion of data now
works properly and doesn't fetch all the records.

4. Server Properties: the process list fills up with the SHOW FULL
PROCESSLIST command now, which returns full SQL statements currently
executing. This feature is available for MySQL server 3.23.07 or higher.

5. Auto search feature is now available in the SQL Assistant and in the
table list of the Visual Query Builder (*).

6. Setting the "Show Table Subobjects" option now refreshes all the
databases currently active. This option also works properly if set in
the Environment Options dialog.

7. Fixed bug with incorrect database tree refreshing after setting the
"Show hosts in DB Explorer" option in the Environment Options dialog.

8. Fixed bug with the access violation error occurrence after changing
the active database using drop-down menu of an object editor or a
database tool.

9. Fixed bug with "List Error Index" message appearance after data
refreshing in a table, which contains field names, consisting of numbers
only.

10. Fixed bug with the access violation occurrence in the Import Data
Wizard when CSV source file is imported with Text File option enabled.
(*)

11. Fixed bug with disabled Next button in the Extract Metadata Wizard.

12. Some small improvements and minor bugfixes.

(*) - Professional Edition only


What is the EMS MySQL Manager?

EMS MySQL Manager provides you powerful and effective tools for MySQL
Server administration and objects management. Its Graphical User
Interface (GUI) allows you to create/edit all MySQL database objects in
a most easy and simple way, run SQL scripts, manage users and
administrate user privileges, visually build SQL queries, extract or
print metadata, export/import data, view/edit BLOBs and many more
services that will make your work with the MySQL server as easy as it
can be...


We hope you'll enjoy working with our software.
Thank you for your attention.


Best regards,
EMS HiTech development team
http://www.ems-hitech.com


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

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

Re: Select * from names where words(name) = 3

[Abhi Sahay]

USE substring_index function
Example:
mysql> select substring_index(name,' ',3),name from test1;
+-+---+
| substring_index(name,' ',3) | name  |
+-+---+
| a b c   | a b c d e |
| a b c   | a b c |
| a d c   | a d c |
| a d c   | a d c e   |
+-+---+
4 rows in set (0.00 sec)


Enjoy
Abhi


- Original Message -
From: "Clyde England" <[EMAIL PROTECTED]>
To: "Dan Nelson" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, January 09, 2003 1:03 AM
Subject: Re: Select * from names where words(name) = 3


> Thanks Dan,
>
> It never occured to me to use regular expressions. (I'm still new to
Mysql)
>
> You have opened up a whole new world to me :-)
>
> The tables I am working with only have a few thousand records in so
> performance is not a big issue and the reg expression works just fine (the
> response is near instant on my now aging P500 256mb PC)
>
> Thanks again.
> Clyde England
>
> *** REPLY SEPARATOR  ***
>
> On 8/01/2003 at 11:45 PM Dan Nelson wrote:
>
> >In the last episode (Jan 09), Clyde England said:
> >> I have a database of names and would like to do a selection based on
> >> the number or words in a name
> >>
> >> eg the name "Peter Smith" has 2 words
> >> the name "Peter John Smith" has 3 words
> >> the name "Peter John Fred Smith" has 4 words
> >>
> >> IE I would like to select all names where there are 3 words in it for
> >> instance. If there were such a function as words(string) which
> >> returned the number of words in a string then the simple select
> >> syntax would be:
> >>
> >> select * from names where words(name) = 3
> >>
> >> Of course in MySql there is no such function (that I am aware of)  -
> >> so any ideas how I can achieve this result.
> >
> >Easy (although not all that fast) way:
> >
> >select * from names where name regexp "^[^ ]*( [^ ]*){2}$";
> >+--+
> >| name |
> >+--+
> >| Peter John Smith |
> >+--+
> >
> >The '2' in the regex is how many spaces are in the name. 0 = single
> >word, 1 = 2 words, etc.  Exercise to the reader: make it work correctly
> >with runs of spaces, and handle tabs and other whitespace characters.
> >
> >Fast way:
> >
> >Write a UDF the implements your WORDS() function; this will be quite a
> >bit faster than the regex.
> >
> >Fastest way:
> >
> >Write the UDF, add another column to your table called `words`, index
> >it, and update it when you update your `name` field.  Use that column
> >in your queries.
> >
> >--
> > 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

update auto increment

[Csongor Fagyal]

Hi,

Sorry if this had been asked on this list previously.

If you had an auto_increment field in a table (let's name it "id"), 
previously (in 3.23.x, 4.0.x) the following (legal) SQL statement 
resulted in a "duplicate key" error:
UPDATE whatevertable SET id = id +1;

Will this be fixed in 4.1.x? Can it be fixed? Is there a workaround?

Regards,
- Cs.


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

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

Re: Question -Subselect

[gerald_clark]

I answered this yesterday.
Your FROM must precede your WHERE.

Terence Ng wrote:


How do I correct this SQL code:

2 tables there, 
lcopen: id, bank, unit_price_us, order_cbm
lcreceive: id, amount_us, due_date

#this condition :lcreceive.due_date < current_date
#only affect to : SUM(lcreceive.amount_us)
#and NOT :
#SUM(lcopen.unit_price_us*lcopen.order_cbm) * 7.8 AS
open


SELECT
lcopen.bank, 
SUM(lcopen.unit_price_us*lcopen.order_cbm) * 7.8 AS
open,
#
SUM(lcreceive.amount_us) where lcreceive.due_date <
current_date,
#
# the above statement is not correct 
# 
FROM lcopen, lcreceive 
AND lcopen.id=lcreceive.id 
GROUP BY lcopen.bank;






Terence Ng


___
Do You Yahoo!?
Get your free @yahoo.com.hk address at http://mail.english.yahoo.com.hk

-
Before posting, please check:
  http://www.mysql.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: May I raise a question?

[gerald_clark]

What OS are you running?

Did you start the server?

What is in the error logs?

[EMAIL PROTECTED] wrote:


Dear Editor,

After installed the MySQL version 3.23.53, I tried to test the MySQL by
typing the following command, relevant error message appears, please advise
how to fix.

Command: Display Error Message:

mysqlshowmysqlshow: Can't connect to MySQL server on
'localhost' (10061)

mysqladmin CREATE test   mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to MySQL server on
'localhost' (10061)'
Check that mysqld is running on localhost and that
the port is 3306.
You can check this by doing 'telnet localhost 3306'

mysql test   ERROR 2003: Can't connect to MySQL server on
'localhost' (10061)

telnet localhost 3306Connecting To localhost...Could not open a
connection to host on port 3306 : Connect failed

Best rgds,

Seed Chan



Seed Chan
System Services Management
Branch Services
* 2962 7431
*  29173503 / 29173504
* [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

log-bin file rotation questions

[Chambon]

Hello,

I set up a replication architecture with MySql
everything works fine.
Now I concentrate on log-bin file and I have a question

I have understood (I hope... )
 -that I can set a max_binlog_size,
 -that a new file (extension n+1)  will be used while reaching max_binlog_size,
 -that the slave will know the new log-bin filename and position

so doing a 'purge master logs' will remove previous files ok, but

what is the high limit for the extension value ?

This question because a set up a  mysql server,
running for months and months without stopping,
and with a lot of insert/update

In over words how to set a initial extension value WITHOUT stopping master nor slave ?

According to me, the only solution is doing a 'reset master' with a lot of care like :
("lock master"; "sync slave"; "reset master"; "reset slave"; "unlock master")

But doing a "lock master" seem to me dangerous for many reasons

Do I misunderstood  something ?

Regards

--
Bernard CHAMBON
IN2P3 / CNRS (Centre de Calcul de LYON)
email:	mailto:[EMAIL PROTECTED]
Tel : 	04 72 69 42 18


-
Before posting, please check:
  http://www.mysql.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: My new forum

[David T-G]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Larry, et al --

...and then Larry Brown said...
% 
% I appreciate that the guy wants to build traffic on his list, but I have to

Except that it isn't a list :-)  But, yeah, I do as well.


% say that I enjoy having this list as busy as it is.  People are pretty much

Same here!


% always there to chime in.  I'm on a javascript list that I get maybe 4
% e-mails a day.  Asking a question there result in a nice long wait.  Here's
% where it is at and I'd hate to see a portion of this list move.

Don't worry -- at least, not about me :-)  There's no way I'd jump off of
a nice, on-topic, organized mailing list that comes to me where I can
slice and dice it with any tools I wish to go and trudge around in some
web forum -- much less one where one 'can talk about Linux, mysql, and
pretty much wahtever else is computer related'.  I just don't have the
time for all of that other stuff, even if I do run off at the mouth
mysqlf (hey, that typo is good enough to leave in here :-) sometimes.


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


mysql query,
:-D
- -- 
David T-G  * There is too much animal courage in 
(play) [EMAIL PROTECTED] * society and not sufficient moral courage.
(work) [EMAIL PROTECTED]  -- Mary Baker Eddy, "Science and Health"
http://justpickone.org/davidtg/  Shpx gur Pbzzhavpngvbaf Qrprapl Npg!

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.0.7 (FreeBSD)

iD8DBQE+HXHgGb7uCXufRwARAhUDAJ9q+POmr3xlC8X4q6JlT50DKxvnjACfYWfP
NA19H6JUIsFxSSaIZkqgUPI=
=wZ8h
-END PGP SIGNATURE-

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

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

Re: Slow response with MRG tables.

[Sinisa Milivojevic]

On Wed, 08 Jan 2003 09:09:02 -0700
Mariella Di Giacomo <[EMAIL PROTECTED]> wrote:

> Hello,
> 
> 
> 
> I would like to ask three questions related to MySQL and MERGE tables.
> 


Hi!

This list is dedicated to the internal functioning of MySQL server.

For the questions like above, please write to [EMAIL PROTECTED]


--

Regards,

--
For technical support contracts, go to https://order.mysql.com/?ref=msmi
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]>
 / /|_/ / // /\ \/ /_/ / /__   MySQL AB, FullTime Developer
/_/  /_/\_, /___/\___\_\___/   Larnaca, Cyprus
   <___/   www.mysql.com

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

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

Re: mysql 4.0.8- crash on TCP connection

[Gelu Gogancea]

Hi,
What OS you use ?

Regards,

Gelu
_
G.NET SOFTWARE COMPANY

Permanent e-mail address : [EMAIL PROTECTED]
  [EMAIL PROTECTED]
- Original Message -
From: "Andrew Sitnikov" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, January 08, 2003 8:14 PM
Subject: mysql 4.0.8- crash on TCP connection


> Hello mysql,
>
>   I try use 4.0.8 (max & standard)in our production box,
>   and it was crash every TCP connection, For 4.0.7 (standard) i has over
20 days uptime.
>
> Best regards,
>  Andrew Sitnikov
>  e-mail : [EMAIL PROTECTED]
>  GSM: (+372) 56491109
>
>
> -
> Before posting, please check:
>http://www.mysql.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: Autonum broken

[John Morrison]

Normalisation was not the right word.  Each row has only two columns - "id" INT 
and "description" VARCHAR.  So the rows did not, indeed could not, contain data 
relating to more than one entity.

It was the /table/ which contained mixed data.  The description column had data 
for two different categories of object and a third column would have been needed 
to differentiate between the two.  I decided, instead, to have two tables.

---
In article <[EMAIL PROTECTED]>, Michael T. Babcock wrote:

> >In the interests of better normalisation I decided to divide one table's data 
> >between two tables.  So I created another table and copied selected rows into 
> >it.
> >  
> >
> 
> I'd love to know how you believe that copying rows to another table is 
> better for normalization.  If you mean that you moved columns to a new 
> table, then it makes sense; but rows?  Where you getting bad query 
> response time?
> 
> Just curious.
> 
> -- 
> Michael T. Babcock
> C.T.O., FibreSpeed Ltd.
> http://www.fibrespeed.net/~mbabcock
> 
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>




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

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

How to easy delete old records from sql database

[Putte Koivisto]

Is there any easy way to delete records from database, wich logs user
activity at website? I mean, if there is somekind of limit for rows in
table. And if limit is exceeded, older rows will be automaticly deleted, or
even better, moved to another table.
Is there any query to make this happen?

Sincerely,

Putte Koivisto

-
Before posting, please check:
   http://www.mysql.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: [OT] Re: InnoDB vs. MySQL performance Issue

[Gelu Gogancea]

Hi Paul,
MYSQL AB maybe have already done something to diffuse this situation but
without to get involved this very-mega-ultra public MYSQL LIST.
And i think also is a duty of every member of this list to make all to "keep
clean" this list but without any implication of it.
I feel frustration also, because on January 5 2003 two of my
townsman(romanians) was killed without any fault by the palestinian
terrorist.
But we must be rational and must to make difference between our feelings and
MYSQL company with all what it is.
Maybe it's time to show that the "pigs" and "monkey" are able to forgive in
comparison with him,witch is man and witch is not able to respect all the
members of this list(by making propaganda).
If we think that this LISTS belong to MYSQL AB then MYSQL AB is a victim of
this disputation.And if MYSQL AB will be have a PUBLIC attitude this means
MYSQL AB will be agree with this kind of disputation .Personally, i think
THIS NOT A PURPOSE OF MYSQL AB.
Anyhow,i think this list belong to all MYSQL users/software developers and
in this situation MYSQL AB didn't have ANY FAULT.

With all my consideration,

Gelu Gogancea
Software Developer - System Integrator
__
G.NET SOFTWARE COMPANY

Permanent e-mail address : [EMAIL PROTECTED]
  [EMAIL PROTECTED]
- Original Message -
From: "Paul Magid" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; "'Michael Widenius'" <[EMAIL PROTECTED]>
Sent: Thursday, January 09, 2003 7:46 AM
Subject: RE: [OT] Re: InnoDB vs. MySQL performance Issue


> To MySQL AB:
>
>
> Your lists are being used to disseminate extremist muslim hate-speech.
As
> such, this will reflect terribly negatively on your company if you let
this
> go unchallenged much longer.   This individual, if you can call him that,
> has repeatedly said inflammatory stuff about Israel in his signature.
> Clearly, no one in an official capacity at your company saw fit to address
> that.  However, now he has equated the Jews and Christians living in
"[his]
> lands" to "pigs" and "monkeys".   This kind of racism is reprehensible in
> the extreme.  This scum is posting his screed from Egypt; I, on the other
> hand, live in a country which has a much richer tradition of freedom of
> speech, the United States.   And, contrary to this morons assertions, you
DO
> NOT have the right to say anything, at any time, anywhere.   And,
> particularly not in a private forum such as this.  There are no first
> amendment protections from non-governmental actors ie: in this case
MySQLAB.
> Thus, if MySQLAB chooses to do nothing about it your company will be
tacitly
> condoning his behavior.  I recommend that at a bare minimum this guy
should
> be removed from the list, for the sheer disruption that he has caused to
the
> normally extremely helpful discourse which is usual in this list.
> Furthermore, I am a DBA in a Fortune 500 company, and I do have
significant
> influence over the buying decisions of my company with respect to
databases.
> You can be sure that any business I have will not go to you if you do
> nothing.
>
>
> Sincerely,
>
> Paul Magid, DBA
>
>
>
> -Original Message-
> From: Sameh Attia [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, January 08, 2003 1:54 AM
> To: [EMAIL PROTECTED]
> Subject: [OT] Re: InnoDB vs. MySQL performance Issue
>
>
> Sam Przyswa wrote:
>
> >Septenber 11 2001, more than 3000 women, men, child, killed, Tel Aviv
> January 5
> >2003, 23 death and 100 injured by islamic terrorists, that's the islamic
> history
> >(small part).
> >
> >Sam Apache-PHP-MySQL user.
> >
> >--
> >"Albert Einstein, Karl Marx, Jesus Christ,
> >and you Mohamed what have you done for the world ?"
> >
> >
> First of all do not cc me directly. I do get a copy of ur shit by the
list.
>
> If you think that Mohamed did not do something for the world which is
> not true. At least he did not leave us with some pigs and monkeys. Read
> your history if u forgot.
>
> If u were a muslim, I thank God that u were not, u would learn how to
> respect other prophets. We as muslims do repect Jesus, Moses, Ibrahim,
> and other prohpets and we are asked to believe in them because it is a
> fundamental believe in our religion.
>
> If u want to know more about out prophet Mohamed go and read
> http://www.islamonline.com/PAGE12.html
>
> Also you will find below detailed information about what the modern,
> democractec, and civilized gang of Zions does to muslims.
> Israeli Massacres http://www.ummah.net/unity/palestine/index.htm
> Sabra & Shatila http://www.ummah.net/unity/sabra/main.html
> Deir Yassin http://www.deiryassin.org &
> http://www.ariga.com/peacewatch/dy &
> http://www.us-israel.org/jsource/History/deir_yassin.html
> Qana http://web.cyberia.net.lb/qana/
> .and much more but the memory does not help.
>
> Sam go and get a life and may God forgive u

details needed:

[Chaitanya Atreya P. S]

Hello,
I want to know whether the mysql depot (mysql-3.23.54a) works on
HP-UX PA_RISC2.0 or it works only on PA_RISC1.0.
If it doesn't work on PA_RISC2.0, where can I find a mysql depot
that works on PA_RISC2.0?

A quick reply will be greatly appreciated.

Thank You,
Chaitanya Atreya.


-
Before posting, please check:
   http://www.mysql.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: non-unique indicies in HEAP tables handled incorrectly.

[Jocelyn Fournier]

Hi,

 I just want to confirm MySQL 4.0.8 seems to be broken too. However MySQL
4.1 is working fine with this testcase.

Regards,
  Jocelyn
- Original Message -
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, January 09, 2003 6:32 AM
Subject: non-unique indicies in HEAP tables handled incorrectly.


> >Description:
>
> I am using mysql 4.04.
>
> Here is a quote from the mysql manual that started me on this adventure:
>
> "You can have non-unique keys in a HEAP table (this isn't common for
hashed tables)."
>
> Based on this claim, I designed a database which took advantage of
non-unique keys in HEAP tables.  A number of things myseriously failed to
work right, however, and problems could be shown only to surface when the
tables in question were of type HEAP rather than anything else.
>
> The below repro script demonstrates the problem in just about the simplest
cast possible.  There is one key and one "value" column.  Two rows are
inserted, with the same value for the key.  Doing an unqualified row count
gives the correct number, 2, but doing a row count with a traversal by the
key only yields a value of 1!  Clearly what is happening is that the
underlying hash index implementation does not utilize any sort of chaining
to bin duplicate keys, or if it does, the traversal code stops after reading
the first hashed value.
>
> This problem has deeper implications.  I actually first ran into it when
doing JOINs between a table and a heap table with non-unique index: when the
non-unique index was involved in the JOIN condition, less rows were produced
in the output than there should have been.  Actually, this means the
relational algebra is broken...
>
> Anyway, strictly speaking, the above claim from the documentation is not
violated by these phenomenon; you can indeed have non-unique keys in a HEAP
table.  Just don't expect them to work =)  I hope this is in fact a bug, and
the claim was not put forth with this trivial meaning (that would seem
fairly dishonest).
>
> I checked the changelogs for newer versions and didn't see anything
referring to this bug, so that is why I have not tried anything later than
4.04.
>
> >How-To-Repeat:
>
> create temporary table heaptest (id int default 0, name varchar(32),
key(id)) TYPE=Heap;
>
> insert into heaptest values(1, 'foo');
> insert into heaptest values(1, 'bar');
> select count(*) as unqualified_count from heaptest;
> select count(*) as count_by_nonunique_key from heaptest where id=1;
>
> >Fix:
> I have no clue, implementation wise (I wish I had time to go look...).
I'm assuming that the HEAP index implementation is either not chained, or
the index traversal code is not aware of this chaining.  I'd be surprised if
it was the former, because that would mean someone knowingly broke the
relational algebra.
>
> >Submitter-Id: 
> >Originator: Aaron Krowne
> >Organization:
>  Digital Library Research Lab
>  Virginia Tech
>  Blacksburg, VA, USA
> >MySQL support: none
> >Synopsis: non-unique indicies in HEAP tables handled incorrectly.
> >Severity:  critical
> >Priority: high
> >Category: mysql
> >Class: sw-bug
> >Release: mysql-4.0.4-beta (Official MySQL RPM)
>
> >Environment:
>
> System: Linux shaun 2.4.18 #1 Tue Dec 3 05:32:59 EST 2002 i686 unknown
> Architecture: i686
>
> Some paths:  /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
> GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/2.95.4/specs
> gcc version 2.95.4 20010902 (Debian prerelease)
> Compilation info: CC='gcc'  CFLAGS='-O6 -fno-omit-frame-pointer -mpentium'
CXX='gcc'
'-O6 -fno-omit-frame-pointer   -felide-constructors -fno-exceptions 
-fno-rtti -mpentium'  LDFLAGS=''
> LIBC:
> lrwxrwxrwx1 root root   14 Dec  3 05:53 /lib/libc.so.5 ->
libc.so.5.4.46
> -rw-r--r--1 root root   563068 Feb  4  2002
/lib/libc.so.5.4.46
> lrwxrwxrwx1 root root   13 Dec  3 05:53 /lib/libc.so.6 ->
libc-2.3.1.so
> -rwxr-xr-x1 root root  1109068 Nov 19 13:13 /lib/libc-2.3.1.so
> -rw-r--r--1 root root  2344038 Nov 19 13:14 /usr/lib/libc.a
> -rw-r--r--1 root root  178 Nov 19 13:14 /usr/lib/libc.so
> Configure command:
./configure --disable-shared --with-mysqld-ldflags=-all-static --with-client
-ldflags=-all-static --without-berkeley-db --with-innodb --without-vio --wit
hout-openssl --enable-assembler --enable-local-infile --with-mysqld-user=mys
ql --with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/ --with-extra
-charsets=complex --exec-prefix=/usr --libexecdir=/usr/sbin --sysconfdir=/et
c --datadir=/usr/share --localstatedir=/var/lib/mysql --infodir=/usr/share/i
nfo --includedir=/usr/include --mandir=/usr/share/man --with-embedded-server
 --enable-thread-safe-client '--with-comment=Official MySQL RPM' CC=gcc
'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium'
'CXXFLAGS=-O6 -fno-omit-frame-pointer   -felide-constructors -fno-ex
ceptions -fno-rtti -mpentium' CXX=gcc
>
>
> 

MySQL on large server

[mysql list]

Hi,

I was wondering if anybody has built MySQL 3.23 from source that can handle 
a high number of connections & threads. I've have tried MySQL binaries in 
the past (not RPMs), but these have had stability/load problems. When 
building from source I don't have those issues, but I am limited by the 
inbuilt limits (of glibc,etc...)

I read that glibc needs to be modified and file descriptor limits need to be 
increased, but I have been unsuccessful in finding some definitive 
documentation to achieve this.

I need to build MySQL 3.23 on a production server running RedHat 7.2, 
patched glibc  (2.2.5-42) and a custom kernel (2.4.19-2 SMP). Hardware 
contains Dual Xeon 2.4GHz (hyperthreading disabled) and 6GB RAM.

My understanding is that MySQL can handle is 500 connections (recommended 
setting) by default. This is something I would need to increase as well as 
the ability to handle more threads.

I would appreciate any help whatsoever.

Many thanks



_
MSN 8 with e-mail virus protection service: 2 months FREE* 
http://join.msn.com/?page=features/virus


-
Before posting, please check:
  http://www.mysql.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

Possible SOLUTION SENDER?? Complex query, grouping and counting challenge

[nospam1001]

Believe that a respondent sent an email which appears to have been
bounced & deleted before I got a chance to view it.  If you are the
person, please resend your response!  Thank you.  

===

Very complex Grouping and counting challenge

Can anyone offer guidance and suggest SQL which will assist in
resolving this complex and challenging (for me) issue?  I hope that
my attempt to clearly state this problem is successful.

The Environment:

Queries and presentation using PHP Version 4.0.4pl1, have an
MySQL 3.23.33 dataset with the following columns of interest.
Due to the number of records (literally 100s of thousands which must

be calculated) I think that the more that can be done in the query
using
the MySQL engine the better.  Using array_walk() or stepping through
row
by row of thousands of records takes too long!

The Problem:
Running a simple query against the underlying data for EvDate
between
2001-09-01 and 2001-09-07 (7 day period used in AveConc) will return
a
dataset from db rows similar to those in the table A.

In the query they are ordered using EvDate so that the earliest
hour/start
time will be in order. I have shown "ifCalc'd" and the "Conc" to
show what
needs to process. (Conc means concurrent)

Using Table A as an example of the data, indicates that an event,
RID 31911 starting at 01:12:20 on the 1st of Sep occurred for a
Duration of
9 hrs, 53 mins and 25 seconds.  If one were to add the duration
to the EvDate_Time the "ifCalc'd" or end time would be 11:05:25.

The event in RID 31912 starts later then RID 31911, and occurs
during
the window when 31911 is occurring.  Likewise, RID 31913 has a
period of
concurrent time with both 31911 and 31912.  The number of events
which
are occuring concurrently is 3.  Looking at the remaining rows shows
that
RID 31915 on the 4th is concurrent with 31914 as the start time for
31915
is less then or equal and therefore concurrent with the "ifCalc'd"
end
time of 31914.

Neither 31916 or 31917 overlap so the conc for those records is 1,
even
though 2 events occured.

31918, 31919 and 31920 have concurrency for 31918 and 31919 so the
concurrency for that date is 2, even though 3 events occured.

Looking at the remaining rows should look similar and familiar to
those
above.  The AveConc uses the MAXConc on each day divided by number
of
days in the original request.  In this example it was 7 days, 9/1 -
9/6,
but could have been 3 to X.

Table A ---

  RIDEvent  EvDate_Time Duration  ifCalc'd
 31911  EventType12001-09-01 01:12:20   09:53:05  11:05:25 x \
 31912  EventType12001-09-01 10:12:40   05:56:39  16:09:19 x --
Conc= 3
 31913  EventType12001-09-01 11:08:05   05:53:36  17:01:41 x /
 31914  EventType12001-09-04 00:01:42   01:11:09  01:12:51 x -
Conc= 2
 31915  EventType12001-09-04 01:12:51   22:48:12  24:01:03 x /
 31916  EventType12001-09-05 00:01:03   01:11:32  01:12:35 --
Conc= 1
 31917  EventType12001-09-05 01:12:36   22:48:46  24:01:22 /
 31918  EventType12001-09-06 00:01:23   01:11:02  01:12:25 x -
Conc= 2
 31919  EventType12001-09-06 01:12:28   07:27:36  08:40:03 x /
 31920  EventType12001-09-06 09:45:32   00:00:16  09:45:48 - --
  n+1...
 33111  EventType422001-09-01 00:12:20  08:53:05  11:05:25 --
Conc= 1
 33112  EventType422001-09-01 11:12:40  05:56:39  17:09:19 /
 33113  EventType422001-09-03 18:08:05  05:53:36  24:01:41 -
Conc= 1
N+1 to end of data set


Desired output results to be returned and processed as an HTML
table.

 Table B 

  Event EventsMAXConc   AveConcTotalDuration
EventType110 3   1.1478:21:53

 ... other eventtypes rows ..

EventType423 1 0.2920:43:20

=

Guidance or code example gratefully appreciated.  Thoughts or
suggestions equally welcomed.

Thanks

-
Before posting, please check:
   http://www.mysql.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