SELECT 9 BETWEEN 1 AND 0

[Steven Ducat]

I am trying to create a select query to find the post town of a users
post code. I am using Royal Mails (UK) post town gazetteer. UK Postcode
(eg. RH6 9XJ). The first column contains the first half of the post code
(eg. RH6) and the next column holds the range of the second half (eg.
2AA-6PP). 

I have some code as follows: 


This will return 1 row for example if the first column was "RH6" and the
second column was "8ZZ-9ZZ". 

But in the post town list the range goes from 1 - 0 (eg. 1AA-0ZZ) so
what I find is if I use the postcode "RH6 9XJ" and a first column of RH6
and a second column of 7AA-0BW it will not return the row as it does not
count from 1 - 0. 

The question is how can I use some sort of statement using mysql and php
to select all rows between 1 and 0 NOT 0 and 9 like the above statement
performs. 


What I really need is some sort of function where I can set the range
that the between option sorts from.
 
I have been trying to solve this for some weeks now. 

Thank You.
 
Steve.
 

MySQL DLL for windows

[Prem Soman]

hi !

  i have a problem compiling a simple mysql client
program in windows. 

what are the compilation options needed and also how
the Linking is made .

The manual says to include my_global.h in the header
but i dont find that under include folder. also i
searched in the net , can anyone provide me the link
from where it could be downloaded ,.. i am using mysql
3.23.40

advance thanx!


Want to chat instantly with your online friends?  Get the FREE Yahoo!
Messenger http://mail.messenger.yahoo.co.uk

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

Re: C API

[Paul DuBois]

At 20:14 -0400 10/20/03, Priyanka Gupta wrote:
Is there a way to have a common C API for MySQL and Oracle. I am 
writing some software that I would like to work with both MYSQL or 
Oracle as the backend server?
The C API for MySQL is specific only to MySQL.  You'd have to write your
own abstraction that allows you to use the same API for MySQL or Oracle.
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified?  http://www.mysql.com/certification/

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

type data

[DWI CAHYO]

Hallo everybody,

can everybody help me, as i would like to save
extention (*.bmp.jpg) on mysql, which i use type data
?

Thank for this help

__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

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

C API

[Priyanka Gupta]

Is there a way to have a common C API for MySQL and Oracle. I am writing 
some software that I would like to work with both MYSQL or Oracle as the 
backend server?

priyanka

_
Enjoy MSN 8 patented spam control and more with MSN 8 Dial-up Internet 
Service.  Try it FREE for one month!   http://join.msn.com/?page=dept/dialup

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

Re: Reliability of _rowid over multiple database accesses

[Roger Baklund]

* Guy Smith
> I'd like to use the _rowid capability of MySQL to improve the
> efficiency of a project, but I have concerns about the
> reliability of the number it returns.  I understand that _rowid
> "may change over time depending on many factors," but I'd like to
> know whether this is true if the row is locked.  In particular,
> can I perform a select on a row which returns a _rowid, modify
> the row data, and later do an UPDATE using the _rowid as index,
> without worrying that the _rowid has changed?  Any help will be
> much appreciated.

The _rowid in MySQL is an alias for any field of a table with an unique
integer index, for instance a primary key. It is usefull when you don't
remember the name of the key, but it will not "improve efficiency" in any
other way, AFAIK.

The value of a key will not change "over time", unless you explicitly change
it.

There should be nothing to worry about. :)

--
Roger


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

Re: Multiple Image inserts into a MySQL BLOB column

[colbey]

Checkout http://php.dreamwerx.net/forums/viewtopic.php?t=6
For a PHP example you could easily convert to PERL or just install PHP
standalone binary on the box.



On Fri, 3 Oct 2003, Zafar wrote:

> Hello
>
> Having trouble inserting images into a BLOB column. No problems doing
> this 'one at a time' via
> a third party MySQL GUI Manager, but I need to create an indexed table
> containing some
> 1K + images. Using the recommeded method from the MySQL manaual ie.
>
> UPDATE tbl_name
> SET blob_column=LOAD_FILE("/tmp/picture")
> WHERE id=1;
>
> only sets the blob field to NULL (0 bytes) ! - even where an image is
> stored perfectly well.
> There has to a SAFE METHOD of doing these image inserts from within a
> 'while' 'for' or some other loop statement in a shell script or perl
> routine. Appreciate enlighenment on this topic from
> someone who has been down this road and figured it out.
>
> Rgds, ZCH
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>

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

Re: Multiple Image inserts into a MySQL BLOB column

[Zafar]

Hello

Having trouble inserting images into a BLOB column. No problems doing 
this 'one at a time' via
a third party MySQL GUI Manager, but I need to create an indexed table 
containing some
1K + images. Using the recommeded method from the MySQL manaual ie.

UPDATE tbl_name
SET blob_column=LOAD_FILE("/tmp/picture")
WHERE id=1;

only sets the blob field to NULL (0 bytes) ! - even where an image is 
stored perfectly well.
There has to a SAFE METHOD of doing these image inserts from within a 
'while' 'for' or some other loop statement in a shell script or perl 
routine. Appreciate enlighenment on this topic from
someone who has been down this road and figured it out.

Rgds, ZCH



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

RE: I need the opposite of BINARY

[Jeff Shapiro]

You can change your query to something like:

 ... WHERE CONCAT(CAST(anumber as CHAR), aname) LIKE 
'12SomeString'

Using the CAST function seems to trick CONCAT into thinking that it 
only received CHARs.

Not that elegant but it might be a little more efficient that using 
LOWER() or UPPER().

On Mon, 20 Oct 2003 18:05:58 +0200 (CEST), Thomas Spahni spoke thusly 
about RE: I need the opposite of BINARY:
> Simon
> 
> you missed this from the manual:
> 
> 
> If you want to convert a number to a string explicitly, pass it as the
> argument to `CONCAT()'.
> 
> If a string function is given a binary string as an argument, the
> resulting string is also a binary string.  A number converted to a
> string is treated as a binary string.  This only affects comparisons.
> 
> Normally, if any expression in a string comparison is case-sensitive,
> the comparison is performed in case-sensitive fashion.
> 
> 
> It is well documented. My question was about how I could change this,
> because I want to perform a comparison in a NON-case-sensitive fashion.
> 
> Thomas Spahni
> 
> 
> On Mon, 20 Oct 2003, Simon Green wrote:
> 
>>  CONCAT turns every this in to a string then puts them together?
>>  LIKE is not case sensitive with string?
>>  When is this turned in to BINARY?
>> 
>>  What have I missed please
>>  Simon
>> 
>>  -Original Message-
>>  From: Thomas Spahni [mailto:[EMAIL PROTECTED]
>>  Sent: 20 October 2003 15:38
>>  To: [EMAIL PROTECTED]
>>  Subject: I need the opposite of BINARY
>> 
>> 
>>  Hi,
>> 
>>  I do the following:
>> 
>>   ... WHERE CONCAT(anumber, aname) LIKE '12SomeString'
>> 
>>  As explained in the manual this is treated as a BINARY comparison i.e.
>>  case of the letters matter. I need a case independent comparison here. Is
>>  there a way to get the usual behaviour of LIKE in this case? (besides
>>  translating all characters to LOWER which is IMHO no elegant solution).
>> 
>>  TIA
>>  Thomas Spahni

---
Listserv only address.
Jeff Shapiro

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

Reliability of _rowid over multiple database accesses

[Guy Smith]

I'd like to use the _rowid capability of MySQL to improve the efficiency of a project, 
but I have concerns about the reliability of the number it returns.  I understand that 
_rowid "may change over time depending on many factors," but I'd like to know whether 
this is true if the row is locked.  In particular, can I perform a select on a row 
which returns a _rowid, modify the row data, and later do an UPDATE using the _rowid 
as index, without worrying that the _rowid has changed?  Any help will be much 
appreciated.

Guy L. Smith
Lead Developer
[EMAIL PROTECTED]

Re: Data modelling software

[Cal Evans]

The absolute best modeling software I've seen for MySQL (and runner up 
for the best modeling software I've ever used) is DBDesigner. 
(http://www.fabforce.net/)  It's open source, cross platform and very good.

There are a few quirks in it (mainly when editing the fields of a table, 
sometimes the editor gets confused) but other than that it's rock solid.

It's one of three programs that I almost always have open. (See, there 
it is in my toolbar now!)

HTH,
=C=
* Cal Evans
* http://www.eicc.com
* We take care of your IT,
* So you can take care of your business.
*
* I think inside the sphere.
Hardik Doshi wrote:
Actually i installed erwin but it doesn't support mysql database directly. How do you handle it with mysql?
 
Thanks for your reply.

"Fortuno, Adam" <[EMAIL PROTECTED]> wrote:
I like Erwin.
-Original Message-
From: Hardik Doshi [mailto:[EMAIL PROTECTED]
Sent: Monday, October 20, 2003 12:42 PM
To: [EMAIL PROTECTED]
Subject: Data modelling software
Hi Group,

Can anyone tell me which data modelling software is good for the mysql
database?
Thanks 

Hardik



-
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
-
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search


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

RE: Data modelling software

[Hardik Doshi]

Actually i installed erwin but it doesn't support mysql database directly. How do you 
handle it with mysql?
 
Thanks for your reply.

"Fortuno, Adam" <[EMAIL PROTECTED]> wrote:
I like Erwin.

-Original Message-
From: Hardik Doshi [mailto:[EMAIL PROTECTED]
Sent: Monday, October 20, 2003 12:42 PM
To: [EMAIL PROTECTED]
Subject: Data modelling software


Hi Group,

Can anyone tell me which data modelling software is good for the mysql
database?

Thanks 

Hardik



-
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search

-
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search

ODBC Encryption?

[Paul F]

Greetings. Can anyone tell me what type of encryption is used when sending
mysql username/password through ODBC. I see that the data and username are
plaintext.

Thanks!


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

Problem with User defined function

[yhou4]

Hi, everyone

I am new to MYSQL. I got a question in using UDF. Each time I have modified my
UDF function, the MySQL server restart itself automatically. I don't know if
it should be like this or I must do something wrong.

It happened like below:

1) I midify my UDF function and add it to the /usr/lib/.
2) I use "DROP FUNCTION func_name" to remove it first.
   At this moment, I got error message that can't connect with the server,
server goes away...
3) Then I found out that immediately the server restart itself
  and I can use DROP FUNCTION and CREATE FUNCTION successfully now.

Is it supposed to be like this?

Thanks  for any help

Yijue



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

Re: WANTED: Betatesters for a MySQL Developer Tool

[Heikki Tuuri]

Martijn,

are you now aware that MySQL supports transactions and foreign key
constraints? In January you still claimed the contrary in an InterBase
newsgroup.

Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which now
also backs up your MyISAM tables

- Original Message - 
From: ""Martijn Tonies"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Monday, October 20, 2003 9:05 PM
Subject: WANTED: Betatesters for a MySQL Developer Tool


> Dear reader,
>
> We're looking for beta-testers for a product called
> Database Workbench. It's a cross-database developer
> tool, currently supporting the database engines
> Borland InterBase, Firebird and Microsoft SQL Server.
>
> As we're expanding the product, we're adding MySQL
> support.
>
> For this version, we're looking for a group of about
> 5 beta-testers. Beta-testers will be subscribed to
> a Yahoogroups mailinglist - if this is unacceptable
> to you, please don't apply.
>
> There's no payment involved for selected betatesters,
> but you will receive a free single-user license for
> Database Workbench.
>
> We would like to have people who:
>
> - would use this product on a daily basis, like they're
>   using MySQL CC now.
> - work with larger databases, both volume and database
>   object count
> - are able to report bugs in a reasonable detailed manner
> - are able to discuss new features or enhancements
> - are able to regularly download updates
>
> For more information on the current functionality of
> Database Workbench, see:
> http://www.upscene.com - company page
> http://www.upscene.com/documentation/dbw/ - documentation
>
> If you think you would be the right person for this,
> please send an email to [EMAIL PROTECTED] with:
> - your name, company and function
> - the kind of database application you are building
> - an estimate of the object count and database size
> - a small piece of text in which you're telling
>   something about yourself and why you would make
>   a good beta-tester
>
> With regards,
>
> Martijn Tonies
> Upscene Productions
> http://www.upscene.com
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>



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

Re: InnoDB or OS restriction?

[Heikki Tuuri]

Alex,

in FreeBSD user process memory space is often restricted to 512 MB. You have
to reconfigure or recompile the FreeBSD kernel to increase that limit.

Best regards,

Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB: now
also backs up your MyISAM tables


- Original Message - 
From: "alex" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Monday, October 20, 2003 1:30 PM
Subject: Re: InnoDB or OS restriction?


> Hi again,
>
> as there was not a single answer to my question I can imagine that no one
> encountered the same issue, but anyways, can there be any hints? First of
> all, are there any means of looking at mysql memory allocation list
> grouped by some major parts - for example,
> innodb main pool - can be retrieved from innodb monitor
> innodb additional pool - can be retrieved from innodb monitor
> myisam main cache - ...?
> myisam sort buffer - ...?
> memory allocated from OS - ...?
>
> The questiion is why mysql is trying to allocate memory via malloc from OS
> while the innodb additional pool is occupied only by 50%? Or is it myisam
> buffer that mysql is trying to extend?
>
> Regards
>
> ---
> Alex
>
> On Tue, 14 Oct 2003, alex wrote:
>
> > Hi people,
> >
> > I have mysql 4.0.7-gamma/linuxthreads running under FreeBSD 4.6.2
(server
> > has 4G phisical memory), and occasionally mysql traps with the message:
> >
> > InnoDB: Fatal error: cannot allocate 1064960 bytes of
> > InnoDB: memory with malloc! Total allocated memory
> > InnoDB: by InnoDB 513951016 bytes. Operating system errno: 12
> > InnoDB: Cannot continue operation!
> >
> > The first strange thing is that MAXDSIZ and DFLDSIZ in kernel config are
> > setted to 1536M, and the second one is that each time it happens, while
> > the number of bytes reported to cannot be allocated is different, total
> > memory allocated by InnoDB is exactly the same - 513951016 bytes. Which
> > restrictions are the cause of this - InnoDB's or FreeBSD's?
> >
> > Thanks in advance
> >
> > 
> > Alex
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> >
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>



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

Re: Pronunciation of ISAM table name

[Jim Mathews]

Thanks to you both for your assistance! 

--- [EMAIL PROTECTED] wrote:
> Hi
> 
> - Original Message -
> From: "Matt W" <[EMAIL PROTECTED]>
> To: "Jim Mathews" <[EMAIL PROTECTED]>;
> <[EMAIL PROTECTED]>
> Sent: Saturday, October 18, 2003 10:18 PM
> Subject: Re: Pronunciation of ISAM table name
> 
> 
> > Hi Jim,
> >
> > As far as I know: eye-sam & my-eye-sam. At least
> that's how I pronounce
> > them. :-)
> 
> Just to add a little extra. ISAM means Indexed
> Sequential Access Method
> >
> >
> > Matt
> >
> >
> > - Original Message -
> > From: "Jim Mathews"
> > Sent: Saturday, October 18, 2003 3:44 PM
> > Subject: Pronunciation of ISAM table name
> >
> >
> > > I have a very basic newbie question that,
> despite
> > > multiple searches, I have been unable to find an
> > > answer to. How is ISAM pronounced? Is it:
> > >
> > > 1. eye-sam (As in "I don't like green eggs and
> ham,
> > > Sam I am.")
> > >
> > > 2. is-am
> > >
> > > 3. I-S-A-M (with the individual letters spoken
> out
> > > like most acronyms)
> > >
> > > I know this seems basic, but I want to learn the
> > > correct way, especially seeing the lively
> discussion
> > > that occurs over pronouncing MySQL as "My Ess
> Que Ell"
> > > versus "my sequel" (which is at least discussed
> in the
> > > MySQL manual -
> 
> The above is what the docs say but C. J Dates's
> Book, pre-dates MySQL by for
> than 20 year refers to SQL as SEQUEL as in
> Structured English Query Language
> 
> > > http://www.mysql.com/doc/en/What-is.html#IDX24)
> > > Besides, I too laugh at people that call a SCSI
> drive
> > > an es-cee-es-eye drive ;)
> > >
> > > Thanks in advance.
> > > Jim
> >
> >
> Regards
> Minky
> 
> 


__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

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

RE: Newbie - Primary Keys

[Carlos Vazquez]

That's just what I wanted to do.
1 primary key made up with multiple fields.  In my case I will be a Date
Field for Transaction Date and a Integer field for StoreNumber.

Thanks a lot for your advice, guys.

:oD


-Original Message-
From: Mark V [mailto:[EMAIL PROTECTED] 
Sent: Monday, October 20, 2003 2:34 PM
To: [EMAIL PROTECTED]
Subject: Re: Newbie - Primary Keys

Good points Patrick. Thanks for the follow-up :-)

--- Patrick Shoaf <[EMAIL PROTECTED]> wrote:

-
Mark is correct, you can define only 1 Primary Key,
but the primary keycan be made up of multiple fields. 
Example:

CREATE TABLE test (item_code char(5) NOT
NULL,item_color char(5) NOTNULL, data varchar(255),
PRIMARY KEY (ITEM_CODE,ITEM_COLOR));

This would allow multiple entries for an item_code and
multiple entriesfor item_color, but only 1 entry for a
combination. example

item_code | item_color | data
---
hammer| red   | my favorite
hammer| blue   |nice color 
nail  |red| ugly item
nail  | green | nice combination with red
hammer
name  |blue   | good with red hammer

With the option below you can only have 1 hammer, and
only 1 nail in theid1 field.  
You need to decide do you need multiple indexes, or do
you need 1 indexwith multiple data fields.

At 02:07 PM 10/20/2003, Mark V wrote:
Hi Carlos,

You can't have 2 primary keys. A table can only have
one primary key. You can, however, also define a
Unique key, and if you set it up such that it is not
null, it will act much like a prmary key:

CREATE TABLE test 
(  id1 INT UNSIGNED NOT NULL, 
   PRIMARY KEY (id1), 
   id2 INT UNSIGNED NOT NULL, 
   UNIQUE KEY (id2), 
   data VARCHAR(25)
);

Hope that helps,
Mark

--- Carlos Vazquez <[EMAIL PROTECTED]> wrote:
> Hi all!
>  
> Just wanted to know how do I create a table with two
> primary keys.
>  
> Thanks a lot!
>  
> 


__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

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


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

Re: Newbie - Primary Keys

[Mark V]

Good points Patrick. Thanks for the follow-up :-)

--- Patrick Shoaf <[EMAIL PROTECTED]> wrote:

-
Mark is correct, you can define only 1 Primary Key,
but the primary keycan be made up of multiple fields. 
Example:

CREATE TABLE test (item_code char(5) NOT
NULL,item_color char(5) NOTNULL, data varchar(255),
PRIMARY KEY (ITEM_CODE,ITEM_COLOR));

This would allow multiple entries for an item_code and
multiple entriesfor item_color, but only 1 entry for a
combination. example

item_code | item_color | data
---
hammer| red   | my favorite
hammer| blue   |nice color 
nail  |red| ugly item
nail  | green | nice combination with red
hammer
name  |blue   | good with red hammer

With the option below you can only have 1 hammer, and
only 1 nail in theid1 field.  
You need to decide do you need multiple indexes, or do
you need 1 indexwith multiple data fields.

At 02:07 PM 10/20/2003, Mark V wrote:
Hi Carlos,

You can't have 2 primary keys. A table can only have
one primary key. You can, however, also define a
Unique key, and if you set it up such that it is not
null, it will act much like a prmary key:

CREATE TABLE test 
(  id1 INT UNSIGNED NOT NULL, 
   PRIMARY KEY (id1), 
   id2 INT UNSIGNED NOT NULL, 
   UNIQUE KEY (id2), 
   data VARCHAR(25)
);

Hope that helps,
Mark

--- Carlos Vazquez <[EMAIL PROTECTED]> wrote:
> Hi all!
>  
> Just wanted to know how do I create a table with two
> primary keys.
>  
> Thanks a lot!
>  
> 


__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

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

Re: Newbie - Primary Keys

[Mark V]

BTW Carlos, 

I'm not sure why you would want or need two primary
keys in a table, but if find you truly do (as opposed
to a PK and some other unique index/key) - there is
probably something wrong with your database design.
You may need to rethink your design. Do some research
and reading on the topics of "Database Design" and
"Database Normalization" - that may help. 

I know when I first started in Databases (especially
in designing them), I had some major misconceptions as
to what a PK was and how it should be used. The
O'Reilly's book "Managing and Using MySQL, 2nd
Edition" has a good chapter on the topics. So does
"Mastering MySQL 4". But keep in mind these are only
*basic* tutorials and high-level coverage of the
subject. Also cheek out Paul DuBois' book "MySQL, 2nd
Edition". While, if my memory serves me correctly, he
does not have a specific chapter on DB design or
normalization, he does teach good design concepts as
you progress through the book and its examples; and
for anything else MySQL related, it is, IMHO, the best
book out there.

Good luck,
Mark


--- Mark V <[EMAIL PROTECTED]> wrote:
> Hi Carlos,
> 
> You can't have 2 primary keys. A table can only have
> one primary key. You can, however, also define a
> Unique key, and if you set it up such that it is not
> null, it will act much like a prmary key:
> 
> CREATE TABLE test 
> (  id1 INT UNSIGNED NOT NULL, 
>PRIMARY KEY (id1), 
>id2 INT UNSIGNED NOT NULL, 
>UNIQUE KEY (id2), 
>data VARCHAR(25)
> );
> 
> Hope that helps,
> Mark
> 
> --- Carlos Vazquez <[EMAIL PROTECTED]> wrote:
> > Hi all!
> >  
> > Just wanted to know how do I create a table with
> two
> > primary keys.
> >  
> > Thanks a lot!
> >  
> > 


__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

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

Re: Newbie - Primary Keys

[Mark V]

Hi Carlos,

You can't have 2 primary keys. A table can only have
one primary key. You can, however, also define a
Unique key, and if you set it up such that it is not
null, it will act much like a prmary key:

CREATE TABLE test 
(  id1 INT UNSIGNED NOT NULL, 
   PRIMARY KEY (id1), 
   id2 INT UNSIGNED NOT NULL, 
   UNIQUE KEY (id2), 
   data VARCHAR(25)
);

Hope that helps,
Mark

--- Carlos Vazquez <[EMAIL PROTECTED]> wrote:
> Hi all!
>  
> Just wanted to know how do I create a table with two
> primary keys.
>  
> Thanks a lot!
>  
> 


__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

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

WANTED: Betatesters for a MySQL Developer Tool

[Martijn Tonies]

Dear reader,

We're looking for beta-testers for a product called
Database Workbench. It's a cross-database developer 
tool, currently supporting the database engines
Borland InterBase, Firebird and Microsoft SQL Server.

As we're expanding the product, we're adding MySQL
support.

For this version, we're looking for a group of about
5 beta-testers. Beta-testers will be subscribed to
a Yahoogroups mailinglist - if this is unacceptable
to you, please don't apply.

There's no payment involved for selected betatesters, 
but you will receive a free single-user license for 
Database Workbench.

We would like to have people who:

- would use this product on a daily basis, like they're
  using MySQL CC now.
- work with larger databases, both volume and database
  object count
- are able to report bugs in a reasonable detailed manner
- are able to discuss new features or enhancements
- are able to regularly download updates

For more information on the current functionality of
Database Workbench, see:
http://www.upscene.com - company page
http://www.upscene.com/documentation/dbw/ - documentation

If you think you would be the right person for this, 
please send an email to [EMAIL PROTECTED] with:
- your name, company and function
- the kind of database application you are building
- an estimate of the object count and database size
- a small piece of text in which you're telling 
  something about yourself and why you would make
  a good beta-tester

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com


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

Newbie - Primary Keys

[Carlos Vazquez]

Hi all!
 
Just wanted to know how do I create a table with two primary keys.
 
Thanks a lot!
 

[ANN] Tool for “Zero Install” MySQL Replication

[Karam Chand]

Webyog has released SQLyog 3.61 - which includes
SQLyog Job Agent ( SJA ) that enables “Zero Install"
MySQL Data Synchronization.

SJA  is a high-performance, multithreaded, multi
platform application to automate and schedule
Synchronization of Data between two MySQL hosts. On
Windows, it is included with SQLyog 3.61 -  the latest
version of the award winning MySQL Client. The Linux
version of SJA is FREE and can be downloaded from
http://www.webyog.com. 

SJA does not require any installation at hosts running
the MySQL server. You can use any host to run SJA. For
example – you can use SJA to keep your production
databases ( probably hosted with an ISP ) in complete
sync with your test database located in your PC or
LAN. 

SJA uses an efficient algorithm to generate checksums
to find out the changes. Therefore, only those rows
that have been inserted, updated or deleted since the
last sync are transferred between the hosts. 

Additionally, you can configure SJA to detect changes
only for specific rows and columns. For example – you
can exclude blob columns or include only those rows
that fulfill a WHERE clause. This makes it an ideal
tool to sync data even if there is limited bandwidth.

SJA is a command line tool that accepts a Job
Definition file encoded in XML as one of the
parameters. You can either create the Job Definition
file manually or use one of the wizards included with
SQLyog. If you use SQLyog to create your job files,
you don’t need to have any knowledge about XML or the
Job Definition schema.

On Windows platforms, SQLyog uses the Task Scheduler
services to schedule your jobs.  On other platforms
you can use the respective OS scheduling services, e.g
– on Linux, you can use cron to schedule jobs.

Apart from regular administration tools, SQLyog also
contains the following features:-

- ODBC Import - Migrate ORACLE, MS SQL, Sybase, DB2,
Access, etc to MySQL with a powerful wizard based
interface!
- Schema Synchronization Tool - Automatically generate
scripts to bring the table structures of two databases
in sync.
- Relationship Manager - Intuitive interface to create
and manage relationships.


__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

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

Data modelling software

[Hardik Doshi]

Hi Group,
 
Can anyone tell me the best software for data modelling? I want to draw the ER diagram 
for the new system as well as existing system.
 
Thanks
 
Hardik


-
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search

replication redirector

[Benjamin KRIEF]

hi guys,

i've just finished implementing replication on 2 production servers.

i'm using round robin dns to balance the load between the slave & the master.

as you would presume , the master handle all the updates, and 1/2 of the
selects , the slave handle the other 1/2 of the selects.

the selection of the server is made in the php code using regexp matching
"^SELECT" , if one request start by "SELECT" , it is redirected to
select.mydomain.com.

in my dns zone file , i've got two entries for select.mydomain.com , one
for each server.

the problem is that it's quite impossible to fine tune the load between
the servers , and my slave is a bit too busy , so the replication thread
is becoming very slow , and sometimes , i can see that the slave is more
than 2 hours behind the master in terms of replication , and this really
is a pb for me.

i looked everywhere for a "high-priority-updates" option, but i can't find
it.tell me if i'm wrong but i deduced that i can't ask the slave to wait
until it reached the master before answering selects.

so i've started using more regexps to redirect the queries , but it's
quite awful to my eyes. i would really like to input the ratio of "select"
queries i'd like to send on the slave .

what about , instead of round robin dns , writing a small perl script ,
including a socket listening on the port 3306 , this small script would
have to redirect the sql requests on the slave or on the master , but this
time , i could fine tune it , and input for instance 0,3  for the slave ,
so that 3 requests out of 10 would go to the slave.

maybe stg similar already exists ?


thanks for your attention,


*
Benjamin KRIEF * Directeur Technique *  IGUANE Studio
Tel:01.56.55.54.20 *  5-7-9 passage des Cloys
Fax:01.56.55.54.24*   75018 PARIS
Gsm:06.12.56.50.41 *  mailto:[EMAIL PROTECTED]
*

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

Data modelling software

[Hardik Doshi]

Hi Group,

Can anyone tell me which data modelling software is good for the mysql database?

Thanks 

Hardik



-
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search

Re: SELECT Question

[Jean-Pierre Schwickerath]

Hello Mumba, Hello Barry, 

> > How do I select out and filter only rows that match
> > both 16 and 62 in the KEYW_ID col?  IE.  The query
> > would return only 119 and 108?
> 
> I'm sure this could be done more effeciently other ways, possibly with
> a sub select if available, but something like this would probably
> work:
> 
>   SELECT temp1.*
>   FROM table_name AS temp1
>   LEFT JOIN table_name AS temp2
>   ON temp1.tbl_idx=temp2.tbl_idx
>   WHERE (temp1.keyw_id=16 AND temp2.keyw_id=62)
>   OR (temp1.keyw_id=62 AND temp2.keyw_id=16);
> 

I'm not sure which one is more efficient but I'd do it this way:

SELECT * FROM table 
WHERE KEYW_ID = 16 OR KEYW_ID = 62
GROUP BY TBL_IDX
HAVING COUNT(TBL_IDX) = 2;


Jean-Pierre
-- 
Powered by Linux From Scratch - http://schwicky.net/
PGP Key ID: 0xEE6F49B4 - AIM/Jabber: Schwicky - ICQ: 4690141

Nothing is impossible... Everything is relative!

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

Re: count max days

[Colin Kettenacker]

Whenever you use the Max() function on a column while accessing other
columns at the same time you need a GROUP BY statement so that the MAX()
function knows how to "group" it's results.

I suggest you look into the manual for this. In fact it has a tutorial
showing you options on how to do exactly what you want to do:



In particular:





ck

-- 
Cheap Domain Registration | Web Hosting | Email Packages | + more
Fantastic prices -- Even better service.
http://www.hosttohost.net


[EMAIL PROTECTED] [EMAIL PROTECTED] on 10/20/03 6:52 AM wrote:

> I sent this out on last friday but I never actually saw it post to the list so
> maybe I messed up the address.  If this is showing up a second time, sorry.
> 
> I have a database where I want to count the number of days where a field gets
> to a certain value.  I am using two seperate queries to count the number of
> days that get to that value.  Right now I am using:
> 
> SELECT  count(DISTINCT aqiValues.readingDate) as greenCount
> FROMaqiRegions, aqiSites, aqiValues
> WHERE   aqiRegions.aqiRegionID = aqiSites.aqiRegionID  AND
> aqiValues.site_name = aqiSites.siteNumber AND
> aqiRegions.showOnSite = 1 AND
> aqiSites.online = 1 AND
> aqiRegions.aqiRegion = 'Twin Cities' AND
> aqiValues.readingDate >= '2003/01/01' AND
> aqiValues.aqiNumber between 0 and 51
> 
> Then I am also using:
> SELECT  count(DISTINCT aqiValues.readingDate) as yellowCount
> FROMaqiRegions, aqiSites, aqiValues
> WHERE   aqiRegions.aqiRegionID = aqiSites.aqiRegionID  AND
> aqiValues.site_name = aqiSites.siteNumber AND
> aqiRegions.showOnSite = 1 AND
> aqiSites.online = 1 AND
> aqiRegions.aqiRegion = ? AND
> aqiValues.readingDate >= '2003/01/01' AND
> aqiValues.aqiNumber between 51 and 100
> 
> My problem is I only want to count the day high value.  So if it is 25 in the
> morning and 52 in the afternoon I only want to count the 52 (the yellowcount
> query) to return results.  I have tried to work in the MAX(aqiNumber) into the
> query but I get a GROUP error.  I must be putting it in the wrong place.
> Maybe I need to somehow make this only one query?
> 
> --ja
> 
> 



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

RE: Starting the mySQL Server

[Mark V]

On Windows machines you do not need to init the mysql
db as it is pre-initialized. But, if you installed
MySQL a directory other than C:\mysql there is
additional work that has to be done. You have to
create either a C:\my.cnf OR
\my.ini file with the following
settings (example shows an install in D:\pgms\mysql):

[mysqld]
basedir=D:/pgms/mysql
datadir=D:/pgms/mysql/data

And yes, you need to use forward slashes and not
backslashes.

I'm not sure if the exclusion of this config info will
give you the error you are getting, but I suspect it
may.

Lastly, I've read on other forums that MySQL doesn't
like install locations with spaces in the path (such
as the famous "C:\Program Files") - I don't know if
that is true or not, but thought it may be worth
mentioning. 

Hope that helps,
Mark


--- Simon Green <[EMAIL PROTECTED]> wrote:
> On UNIX you need to run a creat datbase using
> scripts/mysql_install_db
> 
> Simon
> 
> -Original Message-
> From: Larry Wasserman
> [mailto:[EMAIL PROTECTED]
> Sent: 20 October 2003 16:30
> To: [EMAIL PROTECTED]
> Subject: Starting the mySQL Server
> 
> 
> I cannot start the MySQl Server.  I am running
> Windows XP.  I checked the
> error file and get the following message. 
> 
> 031020 10:11:56  InnoDB: Started
> 031020 10:11:56  Fatal error: Can't open privilege
> tables: Table
> 'mysql.host' doesn't exist
> 031020 10:11:56  Aborting
> 
>  What do I need to do for my mysql.hosts file? 
> Where should it be located?
> 
>
_
> 
> Larry Wasserman Two
> Prudential Plaza 
> Enterprise Marketing Automation  180 North
> Stetson, Suite 5200
> SAS Institute Inc. 
> Chicago, IL  60601
> 
> <>  Phone:
> 312-819-6800 x8826
> Pager Phone: 888-446-6597 Fax: 312-240-0342
> 
> SAS ... The Power to Know 


__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

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

Cannot get Digest

[Paul F]

I seem to be unable to get this list in digest format :(

I previously signed up for normal version. I have tried unsubscribing and
re-subscribing with 
Digest - MIME instead and I keep getting the normal list (individual e-mails
for every post!)

Thanks for any help!


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

RE: OS X - can't set root password

[Fortuno, Adam]

Robert,

I also think the read me is a little vague. I see what they're getting at
but I don't understand why it was phrased that way.

When you think about it, the error message makes sense. When you do any
command through mysqladmin your authenticating to MySQL then executing the
command. You were attempting to connect to MySQL without a password when you
had just set one. MySQL responded by telling you your login attempt was
unsuccessful. After all, you can set your root user's password as many times
as you like.

Its been a while but I believe MySQL identifies the full hostname,
lazarus.local. versus the short name lazarus (see command line example
below). So yes, I wouldn't be surprised to see a host name like that
returned.

[machine] prompt% hostname
lazarus.local.
[machine] prompt% hostname -s
lazarus

If you have additional questions, let me know. I don't use MySQL too much
these days, but I'll be glad to tell you what I know.

Regards,
Adam

-Original Message-
From: Robert Lund [mailto:[EMAIL PROTECTED]
Sent: Monday, October 20, 2003 12:00 PM
To: [EMAIL PROTECTED]
Subject: Re: OS X - can't set root password


Fortuno, Adam wrote:

>If I follow you're email correctly, it looks like you attempting to assign
a
>password to the root user twice. The first statement you provide:
>  
>
>>bin/mysqladmin -u root password **
>>
>>
>Sets the password to whatever value you've set in-place of **. Then in
>the statement below you attempt to set the password again.
>  
>
>>/usr/local/mysql/bin/mysqladmin -u root -h `hostname` password **
>>
>>
>
>Take a good look at that second line. You're assigning a new password to
the
>root account, but when you attempt to reset the password for root as root
>you don't use root's new (as set in line 1) password. Make sense?
>
What you say makes sense - although I would expect the mysql response to 
be something indicating that I'm trying to set the root password twice, 
instead of telling me:

error: 'Host '192.168.1.100' is not allowed to connect to this MySQL server'

I only entered those two instructions because in the README supplied 
with the Installation PKG, the following was included:

If you installed MySQL for the first time, *please remember to set a
password for the MySQL `root' user!*

This is done with the following two commands:

 /usr/local/mysql/bin/mysqladmin -u root password 
 /usr/local/mysql/bin/mysqladmin -u root -h `hostname` password 


Am I missing something here? Do they actually mean "either of the 
following two commands"?

Robert

PS: Does 'lazarus.local.' look like a normal response from 'hostname' or 
should it be just the plain name 'lazarus'?


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

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

RE: I need the opposite of BINARY

[Thomas Spahni]

Simon

you missed this from the manual:


If you want to convert a number to a string explicitly, pass it as the
argument to `CONCAT()'.

If a string function is given a binary string as an argument, the
resulting string is also a binary string.  A number converted to a
string is treated as a binary string.  This only affects comparisons.

Normally, if any expression in a string comparison is case-sensitive,
the comparison is performed in case-sensitive fashion.


It is well documented. My question was about how I could change this,
because I want to perform a comparison in a NON-case-sensitive fashion.

Thomas Spahni


On Mon, 20 Oct 2003, Simon Green wrote:

> CONCAT turns every this in to a string then puts them together?
> LIKE is not case sensitive with string?
> When is this turned in to BINARY?
>
> What have I missed please
> Simon
>
> -Original Message-
> From: Thomas Spahni [mailto:[EMAIL PROTECTED]
> Sent: 20 October 2003 15:38
> To: [EMAIL PROTECTED]
> Subject: I need the opposite of BINARY
>
>
> Hi,
>
> I do the following:
>
>  ... WHERE CONCAT(anumber, aname) LIKE '12SomeString'
>
> As explained in the manual this is treated as a BINARY comparison i.e.
> case of the letters matter. I need a case independent comparison here. Is
> there a way to get the usual behaviour of LIKE in this case? (besides
> translating all characters to LOWER which is IMHO no elegant solution).
>
> TIA
> Thomas Spahni
>
>
>


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

Re: AW: count max days (fwd)

[jabbott]

The trouble with this is it returns 1.  It seems to pick the max value for the year 
instead of by day.

--ja

On Mon, 20 Oct 2003, Franz, Fa. PostDirekt MA wrote:

> Hi,
> 
> maybe I didn't understand the question wrong.
> 
> >I have tried to work in the MAX(aqiNumber) into the query but I get a GROUP
> error.
> 
> That is what I try to prevent here:
> 
> SELECT  aqiValues.readingDate, MAX(aqiValues.aqiNumber) 
> FROMaqiRegions, aqiSites, aqiValues
> WHERE   aqiRegions.aqiRegionID = aqiSites.aqiRegionID  AND
>aqiValues.site_name = aqiSites.siteNumber 
>  aqiRegions.showOnSite = 1
> GROUP BY aqiValues.readinDate;
> 
> I know this i'nt the whole query, but it might be a hint.
> I hope this helps
>   Klaus
> 
> 
> 

-- 



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

Re: OS X - can't set root password

[Robert Lund]

Fortuno, Adam wrote:

If I follow you're email correctly, it looks like you attempting to assign a
password to the root user twice. The first statement you provide:
 

bin/mysqladmin -u root password **
   

Sets the password to whatever value you've set in-place of **. Then in
the statement below you attempt to set the password again.
 

/usr/local/mysql/bin/mysqladmin -u root -h `hostname` password **
   

Take a good look at that second line. You're assigning a new password to the
root account, but when you attempt to reset the password for root as root
you don't use root's new (as set in line 1) password. Make sense?
What you say makes sense - although I would expect the mysql response to 
be something indicating that I'm trying to set the root password twice, 
instead of telling me:

error: 'Host '192.168.1.100' is not allowed to connect to this MySQL server'

I only entered those two instructions because in the README supplied 
with the Installation PKG, the following was included:

If you installed MySQL for the first time, *please remember to set a
password for the MySQL `root' user!*
This is done with the following two commands:

/usr/local/mysql/bin/mysqladmin -u root password 
/usr/local/mysql/bin/mysqladmin -u root -h `hostname` password 


Am I missing something here? Do they actually mean "either of the 
following two commands"?

Robert

PS: Does 'lazarus.local.' look like a normal response from 'hostname' or 
should it be just the plain name 'lazarus'?

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

Re: Starting the mySQL Server

[Director General: NEFACOMP]

That is one of the tables in MySQL database (for privileges and other server
stuffs).

It is located in
MySQL DATA --->mysql --->
MySQL data is the directory containing all data.
On windows systems that is normally c:\mysql\data
So the table file should be in c:\mysql\data\mysql


Thanks
Emery
- Original Message -
From: "Larry Wasserman" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, October 20, 2003 17:29
Subject: Starting the mySQL Server


I cannot start the MySQl Server.  I am running Windows XP.  I checked the
error file and get the following message.

031020 10:11:56  InnoDB: Started
031020 10:11:56  Fatal error: Can't open privilege tables: Table
'mysql.host' doesn't exist
031020 10:11:56  Aborting

 What do I need to do for my mysql.hosts file?  Where should it be located?

_
Larry Wasserman Two Prudential Plaza
Enterprise Marketing Automation180 North Stetson, Suite 5200
SAS Institute Inc.  Chicago, IL  60601
<>  Phone: 312-819-6800 x8826
Pager Phone: 888-446-6597 Fax: 312-240-0342

SAS ... The Power to Know



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






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

RE: Starting the mySQL Server

[Simon Green]

On UNIX you need to run a creat datbase using scripts/mysql_install_db

Simon

-Original Message-
From: Larry Wasserman [mailto:[EMAIL PROTECTED]
Sent: 20 October 2003 16:30
To: [EMAIL PROTECTED]
Subject: Starting the mySQL Server


I cannot start the MySQl Server.  I am running Windows XP.  I checked the
error file and get the following message. 

031020 10:11:56  InnoDB: Started
031020 10:11:56  Fatal error: Can't open privilege tables: Table
'mysql.host' doesn't exist
031020 10:11:56  Aborting

 What do I need to do for my mysql.hosts file?  Where should it be located?

_ 
Larry Wasserman Two Prudential Plaza 
Enterprise Marketing Automation180 North Stetson, Suite 5200
SAS Institute Inc.  Chicago, IL  60601

<>  Phone: 312-819-6800 x8826
Pager Phone: 888-446-6597   Fax: 312-240-0342

SAS ... The Power to Know 



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

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

Re: Getting remote connected machine as value in a select.

[Mark V]

FYI,
If you only want the hostname, and not the user,
combine the USER() with a SUBSTRING_INDEX():

SUBSTRING_INDEX(USER(),'@',-1)  --> returns host only
SUBSTRING_INDEX(USER(),'@',1)   --> returns username
only

USER(), SESSION_USER(), and SYSTEM_USER() are all
synonymous

CURRENT_USER() however is different in that it returns
the username and hostname that the current session was
authenticated as.

I too am unaware of how to prevent the truncation of
string. If anyone knows, I myself would be very
interested.

Thanks,
-Mark

--- Chris Faust <[EMAIL PROTECTED]> wrote:
> That is good to know Emery..
> 
> I think I'll be ok in that respects, I did the
> permissions so the granted
> host is by IP (ex 192.168.0.%), so I'm getting IP
> back and I'm using the
> same username for all and its not truncated - so I
> think I'm good to go!!
> 
> I just my first test on my dev server and everything
> worked exactly as I
> hoped!
> 
> Thanks
> -Chris
> 
> - Original Message - 
> From: "Director General: NEFACOMP" <[EMAIL PROTECTED]>
> To: "Chris Faust" <[EMAIL PROTECTED]>;
> <[EMAIL PROTECTED]>
> Sent: Monday, October 20, 2003 9:48 AM
> Subject: Re: Getting remote connected machine as
> value in a select.
> 
> 
> > You are welcome.
> > But remember that sometimes it truncates the name.
> > For example I sometimes connect from my laptop
> using nzeyimana as username
> > and it just returns [EMAIL PROTECTED] instead of
> returning
> > [EMAIL PROTECTED]
> >  (it just removes the last P).
> >
> > I don't know why. Maybe someone on the list may
> help on this issue.
> > Also as I told you, I don't know the difference
> between USER() and
> > SESSION_USER()
> >
> >
> > Thanks
> > Emery
> > - Original Message -
> > From: "Chris Faust" <[EMAIL PROTECTED]>
> > To: "Director General: NEFACOMP"
> <[EMAIL PROTECTED]>;
> <[EMAIL PROTECTED]>
> > Sent: Monday, October 20, 2003 15:27
> > Subject: Re: Getting remote connected machine as
> value in a select.
> >
> >
> > > Thanks Emery,
> > >
> > > That is exactly what I needed, it gives me back
> the IP of the machine
> I'm
> > > connecting from.
> > > This is going to allow me to remove so much
> machine specific code that
> we
> > > could put up a new server within mins if needed.
> > >
> > > Thanks Again
> > > -Chris
> > >
> > > - Original Message -
> > > From: "Director General: NEFACOMP"
> <[EMAIL PROTECTED]>
> > > To: "Chris Faust" <[EMAIL PROTECTED]>;
> <[EMAIL PROTECTED]>
> > > Sent: Monday, October 20, 2003 4:38 AM
> > > Subject: Re: Getting remote connected machine as
> value in a select.
> > >
> > >
> > > > I don't know I well understand your problem.
> > > > There is a function SESSION_USER() in MySQL
> that returns the currently
> > > > connected user. The user string also returns
> the host from which he
> > > > connected in the form [EMAIL PROTECTED]
> > > >
> > > > So, by issuing the query:
> > > > SELECT SESSION_USER(), Table.* from Table
> > > > you will get the needed data.
> > > >
> > > > But the problem is that you will need to get
> the host name from that
> > > > username. Also it truncates the string when it
> is long
> > > > There is also USER() that returns the same
> [EMAIL PROTECTED] . I really don't
> > know
> > > > the real difference between the two functions.
> > > >
> > > >
> > > > Hope this helps
> > > >
> > > >
> > > > Thanks
> > > > Emery
> > > > - Original Message -
> > > > From: "Chris Faust" <[EMAIL PROTECTED]>
> > > > To: <[EMAIL PROTECTED]>
> > > > Sent: Sunday, October 19, 2003 23:55
> > > > Subject: Getting remote connected machine as
> value in a select.
> > > >
> > > >
> > > > Hello,
> > > >
> > > > I'm curious as to if something is possible - I
> don't know if its
> > possible
> > > to
> > > > explain it right.
> > > >
> > > > I have a table which contains a bunch machine
> specific information,
> file
> > > > paths, names etc.
> > > >
> > > > What I would like to try and accomplish is
> when I query this table for
> > the
> > > > information I need, I also receive back a
> value (that I can define)
> that
> > > is
> > > > determined by the identity of the machine
> making the query - either by
> > IP
> > > > address or whatever.
> > > >
> > > > For an example say I have 3 machines total.
> > > > 1 mySQL DB Server
> > > > 2 web servers
> > > >
> > > > When I connect to mySQL from either of the 2
> web servers and say
> > something
> > > > like "Select * from table", one of the values
> returned would be
> > something
> > > > like "web1" as that is what I defined to be
> the value for when machine
> > > > 192.168.0.4 connected to it.
> > > > It doesn't have to be IP, I'm just using that
> for a example.
> > > >
> > > > Any ideas on how to accomplish something like
> this?
> > > > mySql 4.0
> > > > RH 9
> > > >
> > > > Thanks
> > > > -Chris
> > > >
> > > >
> > >
> > >
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:   
> http://lists

RE: I need the opposite of BINARY

[Simon Green]

CONCAT turns every this in to a string then puts them together?
LIKE is not case sensitive with string?
When is this turned in to BINARY?

What have I missed please
Simon

-Original Message-
From: Thomas Spahni [mailto:[EMAIL PROTECTED]
Sent: 20 October 2003 15:38
To: [EMAIL PROTECTED]
Subject: I need the opposite of BINARY


Hi,

I do the following:

 ... WHERE CONCAT(anumber, aname) LIKE '12SomeString'

As explained in the manual this is treated as a BINARY comparison i.e.
case of the letters matter. I need a case independent comparison here. Is
there a way to get the usual behaviour of LIKE in this case? (besides
translating all characters to LOWER which is IMHO no elegant solution).

TIA
Thomas Spahni


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

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

Starting the mySQL Server

[Larry Wasserman]

I cannot start the MySQl Server.  I am running Windows XP.  I checked the error file 
and get the following message. 

031020 10:11:56  InnoDB: Started
031020 10:11:56  Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't 
exist
031020 10:11:56  Aborting

 What do I need to do for my mysql.hosts file?  Where should it be located?

_ 
Larry Wasserman Two Prudential Plaza 
Enterprise Marketing Automation180 North Stetson, Suite 5200
SAS Institute Inc.  Chicago, IL  60601   
<>  Phone: 312-819-6800 x8826
Pager Phone: 888-446-6597   Fax: 312-240-0342

SAS ... The Power to Know 



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

Re: repair with keycache during ADD INDEX

[Ken Menzel]

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

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

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

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

Ken

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


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


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

I need the opposite of BINARY

[Thomas Spahni]

Hi,

I do the following:

 ... WHERE CONCAT(anumber, aname) LIKE '12SomeString'

As explained in the manual this is treated as a BINARY comparison i.e.
case of the letters matter. I need a case independent comparison here. Is
there a way to get the usual behaviour of LIKE in this case? (besides
translating all characters to LOWER which is IMHO no elegant solution).

TIA
Thomas Spahni


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

Re: Getting remote connected machine as value in a select.

[Chris Faust]

That is good to know Emery..

I think I'll be ok in that respects, I did the permissions so the granted
host is by IP (ex 192.168.0.%), so I'm getting IP back and I'm using the
same username for all and its not truncated - so I think I'm good to go!!

I just my first test on my dev server and everything worked exactly as I
hoped!

Thanks
-Chris

- Original Message - 
From: "Director General: NEFACOMP" <[EMAIL PROTECTED]>
To: "Chris Faust" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Monday, October 20, 2003 9:48 AM
Subject: Re: Getting remote connected machine as value in a select.


> You are welcome.
> But remember that sometimes it truncates the name.
> For example I sometimes connect from my laptop using nzeyimana as username
> and it just returns [EMAIL PROTECTED] instead of returning
> [EMAIL PROTECTED]
>  (it just removes the last P).
>
> I don't know why. Maybe someone on the list may help on this issue.
> Also as I told you, I don't know the difference between USER() and
> SESSION_USER()
>
>
> Thanks
> Emery
> - Original Message -
> From: "Chris Faust" <[EMAIL PROTECTED]>
> To: "Director General: NEFACOMP" <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
> Sent: Monday, October 20, 2003 15:27
> Subject: Re: Getting remote connected machine as value in a select.
>
>
> > Thanks Emery,
> >
> > That is exactly what I needed, it gives me back the IP of the machine
I'm
> > connecting from.
> > This is going to allow me to remove so much machine specific code that
we
> > could put up a new server within mins if needed.
> >
> > Thanks Again
> > -Chris
> >
> > - Original Message -
> > From: "Director General: NEFACOMP" <[EMAIL PROTECTED]>
> > To: "Chris Faust" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> > Sent: Monday, October 20, 2003 4:38 AM
> > Subject: Re: Getting remote connected machine as value in a select.
> >
> >
> > > I don't know I well understand your problem.
> > > There is a function SESSION_USER() in MySQL that returns the currently
> > > connected user. The user string also returns the host from which he
> > > connected in the form [EMAIL PROTECTED]
> > >
> > > So, by issuing the query:
> > > SELECT SESSION_USER(), Table.* from Table
> > > you will get the needed data.
> > >
> > > But the problem is that you will need to get the host name from that
> > > username. Also it truncates the string when it is long
> > > There is also USER() that returns the same [EMAIL PROTECTED] . I really don't
> know
> > > the real difference between the two functions.
> > >
> > >
> > > Hope this helps
> > >
> > >
> > > Thanks
> > > Emery
> > > - Original Message -
> > > From: "Chris Faust" <[EMAIL PROTECTED]>
> > > To: <[EMAIL PROTECTED]>
> > > Sent: Sunday, October 19, 2003 23:55
> > > Subject: Getting remote connected machine as value in a select.
> > >
> > >
> > > Hello,
> > >
> > > I'm curious as to if something is possible - I don't know if its
> possible
> > to
> > > explain it right.
> > >
> > > I have a table which contains a bunch machine specific information,
file
> > > paths, names etc.
> > >
> > > What I would like to try and accomplish is when I query this table for
> the
> > > information I need, I also receive back a value (that I can define)
that
> > is
> > > determined by the identity of the machine making the query - either by
> IP
> > > address or whatever.
> > >
> > > For an example say I have 3 machines total.
> > > 1 mySQL DB Server
> > > 2 web servers
> > >
> > > When I connect to mySQL from either of the 2 web servers and say
> something
> > > like "Select * from table", one of the values returned would be
> something
> > > like "web1" as that is what I defined to be the value for when machine
> > > 192.168.0.4 connected to it.
> > > It doesn't have to be IP, I'm just using that for a example.
> > >
> > > Any ideas on how to accomplish something like this?
> > > mySql 4.0
> > > RH 9
> > >
> > > Thanks
> > > -Chris
> > >
> > >
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
> >
>
>
>
>



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

RE: I can't start mysqld

[Christensen, Dave]

In the data directory for MySQL, you should find a file titled
.err.  What is the contents of that file?

-Original Message-
From: Hamid Nouri [mailto:[EMAIL PROTECTED] 
Sent: Saturday, October 18, 2003 5:07 PM
To: [EMAIL PROTECTED]
Subject: I can't start mysqld


Hi there

I have a problem with starting mysqld. I tried to
install mysql-4.1 on my debian, but when i want to
start mysql daemon it failes. I generated bug report.
my.cnf and mysql also attached. 

Thanks in advance

-
SEND-PR: -*- send-pr -*-
SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as
SEND-PR: will all comments (text enclosed in `<' and
`>').
SEND-PR:
From: root
To: [EMAIL PROTECTED]
Subject:

>Description:

>How-To-Repeat:

>Fix:


>Submitter-Id:  
>Originator:root
>Organization:
 
>MySQL support: [none | licence | email support |
extended email support ]
>Synopsis:  
>Severity:  <[ non-critical | serious | critical ] (one
line)>
>Priority:  <[ low | medium | high ] (one line)>
>Category:  mysql
>Class: <[ sw-bug | doc-bug | change-request |
support ] (one line)>
>Release:   mysql-4.1.0-alpha-standard (Official
MySQL-standard binary)

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

System: Linux pinguin 2.4.18-bf2.4 #1 Son Apr 14
09:53:28 CEST 2002 i686 unknown
Architecture: i686

Some paths:  /usr/bin/perl /usr/bin/make /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 20011002 (Debian prerelease)
Compilation info: CC='gcc'  CFLAGS='-O2
-mcpu=pentiumpro'  CXX='gcc'  CXXFLAGS='-O2
-mcpu=pentiumpro -felide-constructors'  LDFLAGS='' 
ASFLAGS=''
LIBC: 
lrwxrwxrwx1 root root   13 Oct  3
13:31 /lib/libc.so.6 -> libc-2.2.5.so
-rwxr-xr-x1 root root  1153784 Apr 28 
2002 /lib/libc-2.2.5.so
-rw-r--r--1 root root  2390922 Apr 28 
2002 /usr/lib/libc.a
-rw-r--r--1 root root  178 Apr 28 
2002 /usr/lib/libc.so
-rw-r--r--1 root root   716080 Jan 13 
2002 /usr/lib/libc-client.so.2001
Configure command: ./configure
'--prefix=/usr/local/mysql' '--with-comment=Official MySQL-standard binary'
'--with-extra-charsets=complex' '--with-server-suffix=-standard'
'--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler'
'--disable-shared' '--with-client-ldflags=-all-static'
'--with-mysqld-ldflags=-all-static' '--with-innodb'
'CFLAGS=-O2 -mcpu=pentiumpro' 'CXXFLAGS=-O2
-mcpu=pentiumpro -felide-constructors' 'CXX=gcc'


my.cnf---


# You can copy this to one of:
# /etc/mysql/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options
(in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
# 
# One can use all long options that the program
supports.
# Run the program with --help to get a list of
available options

# This will be passed to all mysql clients
[client]
#password   = my_password
port= 3306
socket  = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M
ram

[safe_mysqld]
err-log = /var/log/mysql/mysql.err

[mysqld]
user= mysql
pid-file= /var/run/mysqld/mysqld.pid
socket  = /var/run/mysqld/mysqld.sock
port= 3306
#
# You can also put it into /var/log/mysql/mysql.log
but I leave it in /var/log
# for backward compatibility. Both location gets
rotated by the cronjob.
#log= /var/log/mysql/mysql.log
log = /var/log/mysql.log
basedir = /usr
datadir = /var/lib/mysql
tmpdir  = /tmp
language= /usr/share/mysql/english
skip-locking
#
# The skip-networkin option will no longer be set via
debconf menu.
# You have to manually change it if you want
networking i.e. the server
# listening on port 3306. The default is "disable" -
for security reasons.
skip-networking
set-variable= key_buffer=16M
set-variable= max_allowed_packet=1M
set-variable= thread_stack=128K
#
# Here you can see queries with especially long
duration
#log-slow-queries   = /var/log/mysql/mysql-slow.log
#
# The following can be used as easy to replay backup
logs or for replication
#server-id  = 1
#log-bin= /var/log/mysql/mysql-bin.log
#binlog-do-db   = include_database_name
#binlog-ignore-db   = include_database_name
#
# Read the manual if you want to enable InnoDB!
skip-innodb

[mysqldump]
quick
set-variable= max_allowed_packet=1M

[mysql]
#no-auto-rehash # faster start of mysql but no tab
completition

[isamchk]
set-variable= key_buffer=16M


--mysql-

#!/bin/sh -e
#
# MySQL daemon start/stop script.
#
# Debian version. Based on the original by TcX.
#

test $DEBIAN_SCRIPT_DEBUG && set -v -x 

te

Re: Quotes and loading

[Egor Egorov]

Kim Kohen <[EMAIL PROTECTED]> wrote:
> 
> I have a bit of a problem with some characters I'm loading from a Filemaker
> Pro database. The single quotes are stored in MySQL as ASCII character 155
> (an 'O' with a tilde over it). I have tried everything I can think of to
> replace this with the PHP I use to query the db but I have failed miserably.
> I have similar problems with all single quotes and double quotes.
> 
> I could probably process the text file before loading it into MySQL
> (replacing the quotes with their HTML special characters) but I'm wondering
> if there is anything more fundamental I should be doing.
> 

You should escape single or double quotes:
http://www.mysql.com/doc/en/String_syntax.html

You can use MySQL function QUOTE() or PHP function mysql_escape_string ().



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




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

Re: Database Design and Bianry Operations

[Mojtaba Faridzad]

by 4 bytes number (as 1 field) , you can keep 32 bits (criteria). this shows
how much you can save the space. but if the database is not huge, it's
better to select simpler solution (one field for one criteria). this is an
example in MySQL manual to show how to work with bits:

The following shows an idea of how you can use the bit group functions to
calculate the number of days per month a user has visited a web page.

CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL, day INT(2)
UNSIGNED ZEROFILL);

INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),
(2000,2,23),(2000,2,23);

SELECT year,month,BIT_COUNT(BIT_OR(1<
To: <[EMAIL PROTECTED]>
Sent: Tuesday, October 14, 2003 9:17 AM
Subject: Database Design and Bianry Operations


> Hello,
>
> I'm after some advice on database design:
>
> I've got an object - for an example a hotel - and I want to keep
information
> about this hotel, criteria that it either has or hasn't (TV, swimming pool
> etc).
>
> I want to search on criteria and return the most appropriate match.
>
> Bearing in mind I've currently got over 200 criteria and want to expand
> this, how should approach the design of my criteria table.
>
> Should I have a table with Hotel Id and then a char(1) (Y/N) field for
each
> criteria and then a seperate look-up table for criteria name.
>
> I get the feeling there is a more efficient method using binary operations
> (only one field populated with zeros and ones) but I can't find anything
to
> help in the manuals.
>
> Any thoughts?
>
> Lee Denny
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>


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

count max days (fwd)

[jabbott]

I sent this out on last friday but I never actually saw it post to the list so maybe I 
messed up the address.  If this is showing up a second time, sorry.

I have a database where I want to count the number of days where a field gets to a 
certain value.  I am using two seperate queries to count the number of days that get 
to that value.  Right now I am using:

SELECT  count(DISTINCT aqiValues.readingDate) as greenCount
FROMaqiRegions, aqiSites, aqiValues
WHERE   aqiRegions.aqiRegionID = aqiSites.aqiRegionID  AND
aqiValues.site_name = aqiSites.siteNumber AND
aqiRegions.showOnSite = 1 AND
aqiSites.online = 1 AND
aqiRegions.aqiRegion = 'Twin Cities' AND
aqiValues.readingDate >= '2003/01/01' AND  
aqiValues.aqiNumber between 0 and 51

Then I am also using:
SELECT  count(DISTINCT aqiValues.readingDate) as yellowCount
FROMaqiRegions, aqiSites, aqiValues
WHERE   aqiRegions.aqiRegionID = aqiSites.aqiRegionID  AND
aqiValues.site_name = aqiSites.siteNumber AND
aqiRegions.showOnSite = 1 AND
aqiSites.online = 1 AND
aqiRegions.aqiRegion = ? AND
aqiValues.readingDate >= '2003/01/01' AND  
aqiValues.aqiNumber between 51 and 100

My problem is I only want to count the day high value.  So if it is 25 in the morning 
and 52 in the afternoon I only want to count the 52 (the yellowcount query) to return 
results.  I have tried to work in the MAX(aqiNumber) into the query but I get a GROUP 
error.  I must be putting it in the wrong place.  Maybe I need to somehow make this 
only one query?

--ja

 
-- 



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

Re: Getting remote connected machine as value in a select.

[Director General: NEFACOMP]

You are welcome.
But remember that sometimes it truncates the name.
For example I sometimes connect from my laptop using nzeyimana as username
and it just returns [EMAIL PROTECTED] instead of returning
[EMAIL PROTECTED]
 (it just removes the last P).

I don't know why. Maybe someone on the list may help on this issue.
Also as I told you, I don't know the difference between USER() and
SESSION_USER()


Thanks
Emery
- Original Message -
From: "Chris Faust" <[EMAIL PROTECTED]>
To: "Director General: NEFACOMP" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Monday, October 20, 2003 15:27
Subject: Re: Getting remote connected machine as value in a select.


> Thanks Emery,
>
> That is exactly what I needed, it gives me back the IP of the machine I'm
> connecting from.
> This is going to allow me to remove so much machine specific code that we
> could put up a new server within mins if needed.
>
> Thanks Again
> -Chris
>
> - Original Message -
> From: "Director General: NEFACOMP" <[EMAIL PROTECTED]>
> To: "Chris Faust" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> Sent: Monday, October 20, 2003 4:38 AM
> Subject: Re: Getting remote connected machine as value in a select.
>
>
> > I don't know I well understand your problem.
> > There is a function SESSION_USER() in MySQL that returns the currently
> > connected user. The user string also returns the host from which he
> > connected in the form [EMAIL PROTECTED]
> >
> > So, by issuing the query:
> > SELECT SESSION_USER(), Table.* from Table
> > you will get the needed data.
> >
> > But the problem is that you will need to get the host name from that
> > username. Also it truncates the string when it is long
> > There is also USER() that returns the same [EMAIL PROTECTED] . I really don't
know
> > the real difference between the two functions.
> >
> >
> > Hope this helps
> >
> >
> > Thanks
> > Emery
> > - Original Message -
> > From: "Chris Faust" <[EMAIL PROTECTED]>
> > To: <[EMAIL PROTECTED]>
> > Sent: Sunday, October 19, 2003 23:55
> > Subject: Getting remote connected machine as value in a select.
> >
> >
> > Hello,
> >
> > I'm curious as to if something is possible - I don't know if its
possible
> to
> > explain it right.
> >
> > I have a table which contains a bunch machine specific information, file
> > paths, names etc.
> >
> > What I would like to try and accomplish is when I query this table for
the
> > information I need, I also receive back a value (that I can define) that
> is
> > determined by the identity of the machine making the query - either by
IP
> > address or whatever.
> >
> > For an example say I have 3 machines total.
> > 1 mySQL DB Server
> > 2 web servers
> >
> > When I connect to mySQL from either of the 2 web servers and say
something
> > like "Select * from table", one of the values returned would be
something
> > like "web1" as that is what I defined to be the value for when machine
> > 192.168.0.4 connected to it.
> > It doesn't have to be IP, I'm just using that for a example.
> >
> > Any ideas on how to accomplish something like this?
> > mySql 4.0
> > RH 9
> >
> > Thanks
> > -Chris
> >
> >
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
>





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

Re: SQL query needed

[Roger Baklund]

* Reto Baumann
> I'm working on a book database with some special requirements.
> Each book is associated with some keywords and put into a
> category. Category 0 is special, as this is "Unsorted", i.e. not
> associated with a category (which most books are at the moment).
>
> For thei query, let's simplify the structure and define it as follows:
> CREATE TABLE books (
>   bookID mediumint(8) unsigned NOT NULL auto_increment,
>   title varchar(200) NOT NULL default '',
>   category mediumint(9) default '0',
>   score mediumint(9) NOT NULL default '0',
>   PRIMARY KEY  (bookID)
> ) TYPE=MyISAM AUTO_INCREMENT=1;
>
>
> With each book, there is also a score field which represents the
> "rating" of a book, therefore the higher the better.
>
> Now I would like to construct a query which does the following:
> * Return all books that match a requested keyword
> * All matching books from category 0
> * Only one book for each category >0 if there is a match
> with the keyword (and if so, the one with the highest score)
> * Order the books by score

Kind of tricky, but maybe something like this could be used:

SELECT DISTINCT books.*
  FROM books
  LEFT JOIN books b2 ON
b2.title LIKE "%$keyword%" AND
b2.category = books.category AND
b2.score > books.score
  WHERE
books.title LIKE "%$keyword%" AND
(b2.bookID IS NULL OR books.category = 0)
  ORDER BY books.score;

The left join is used to check if there are any rows with a higher score for
the same category. If there is, this row is _not_ included, unless
category=0.

You will get multiple books from the same category if two or more books
share the same highest score within that category.

--
Roger


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

Re: MySQL & VEE

[Mojtaba Faridzad]

as I know in HP-VEE program you can export the data in a text file. in MySQL
you can read a text file by LOAD DATA INFILE command. this helps you to keep
the data in MySQL database without activeX programming.

- Original Message - 
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, October 20, 2003 9:06 AM
Subject: MySQL & VEE


> Hi everybody,
>
> we are trying to transfer Agilent Vee measuring data to MySQL. To my
> knowledge programming an active-x component would be a useful way, but
> probably somebody has an alternative approach, experience or a program for
> the subject.
> Recent tries were just partially successful, the internet research did not
> bring up useful information.
>
> Kind regards
>
> Michael Daheim
> Entwicklungsingenieur LF 1
> Krohne Messtechnik GmbH & Co. KG
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>


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

Re: Getting remote connected machine as value in a select.

[Chris Faust]

Thanks Emery,

That is exactly what I needed, it gives me back the IP of the machine I'm
connecting from.
This is going to allow me to remove so much machine specific code that we
could put up a new server within mins if needed.

Thanks Again
-Chris

- Original Message - 
From: "Director General: NEFACOMP" <[EMAIL PROTECTED]>
To: "Chris Faust" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Monday, October 20, 2003 4:38 AM
Subject: Re: Getting remote connected machine as value in a select.


> I don't know I well understand your problem.
> There is a function SESSION_USER() in MySQL that returns the currently
> connected user. The user string also returns the host from which he
> connected in the form [EMAIL PROTECTED]
>
> So, by issuing the query:
> SELECT SESSION_USER(), Table.* from Table
> you will get the needed data.
>
> But the problem is that you will need to get the host name from that
> username. Also it truncates the string when it is long
> There is also USER() that returns the same [EMAIL PROTECTED] . I really don't know
> the real difference between the two functions.
>
>
> Hope this helps
>
>
> Thanks
> Emery
> - Original Message -
> From: "Chris Faust" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Sunday, October 19, 2003 23:55
> Subject: Getting remote connected machine as value in a select.
>
>
> Hello,
>
> I'm curious as to if something is possible - I don't know if its possible
to
> explain it right.
>
> I have a table which contains a bunch machine specific information, file
> paths, names etc.
>
> What I would like to try and accomplish is when I query this table for the
> information I need, I also receive back a value (that I can define) that
is
> determined by the identity of the machine making the query - either by IP
> address or whatever.
>
> For an example say I have 3 machines total.
> 1 mySQL DB Server
> 2 web servers
>
> When I connect to mySQL from either of the 2 web servers and say something
> like "Select * from table", one of the values returned would be something
> like "web1" as that is what I defined to be the value for when machine
> 192.168.0.4 connected to it.
> It doesn't have to be IP, I'm just using that for a example.
>
> Any ideas on how to accomplish something like this?
> mySql 4.0
> RH 9
>
> Thanks
> -Chris
>
>



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

Re: MYSQL Connection String error

[gerald_clark]

You need to grant privileges to 'root'@'127.0.0.1' because it is not the 
same thing
as 'root'@'localhost'.  

Timotius Alfa wrote:

Pls help me, I want to know if this connection string can be used for multi user, coz when I change "Server=localhost" with "server=127.0.0.1" it can not work. thanks 

Connect_Str = "Driver={MySQL ODBC 3.51 Driver};" & _
 "SERVER=127.0.0.1;" & _
 "Database=sekolah;" & _
 "UID=root;PWD=tim; OPTION= " & 1 + 2 + 8 + 32 + 2048 + 16384


-
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
 



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

MySQL & VEE

[MDaheim]

Hi everybody,

we are trying to transfer Agilent Vee measuring data to MySQL. To my
knowledge programming an active-x component would be a useful way, but
probably somebody has an alternative approach, experience or a program for
the subject.
Recent tries were just partially successful, the internet research did not
bring up useful information.

Kind regards

Michael Daheim
Entwicklungsingenieur LF 1
Krohne Messtechnik GmbH & Co. KG

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

Re: Lock wait timeout exceeded problem

[Egor Egorov]

Hess Yvan <[EMAIL PROTECTED]> wrote:
> I am doing a lot of inserts, updates and reads with big among of data into
> longtext and longblob fields using JDBC driver and mysql 4.0.15 for Windows
> with innodb tables. 
> After 1 hours of intensive working the database sent me a message "Lock wait
> timeout exceeded; Try restarting transaction" SQL code: 1205".  
> I doing know how I can solve this problem ? How I have do configure mysql or
> is it a bug ?
> 
> Environment: 
> 
> 6 Java Virtual machine using each their own connections pool.
> O/R mapping is done with Hibernate version 2.0.
> 

You can increase value of innodb_lock_wait_timeout variable:
http://www.mysql.com/doc/en/InnoDB_start.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




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

SQL query needed

[Reto Baumann]

Hi all

I'm working on a book database with some special requirements. Each book is associated 
with some keywords and put into a category. Category 0 is special, as this is 
"Unsorted", i.e. not associated with a category (which most books are at the moment).

For thei query, let's simplify the structure and define it as follows:
CREATE TABLE books (
  bookID mediumint(8) unsigned NOT NULL auto_increment,
  title varchar(200) NOT NULL default '',
  category mediumint(9) default '0',
  score mediumint(9) NOT NULL default '0',
  PRIMARY KEY  (bookID)
) TYPE=MyISAM AUTO_INCREMENT=1;


With each book, there is also a score field which represents the "rating" of a book, 
therefore the higher the better.

Now I would like to construct a query which does the following:
* Return all books that match a requested keyword
* All matching books from category 0
* Only one book for each category >0 if there is a match with the keyword (and 
if so, the one with the highest score)
* Order the books by score

Any idea and help?

Thanks a lot
cu
reto

Re: select from two tables then create a new table

[Roger Baklund]

* zhu xiaofeng 
> Sent: Thursday, October 09, 2003 4:45 AM

An old question, but no replies...

> There is a question:
> Two tables:
>table_out:  ( fields ) product_name,out_count , out_date
>table_in:  ( fields ) product_name,in_count , in_date   
> I want to create a table that show the remain of product.

Try something like this:

# Make a temporary table with one line for each product
CREATE TEMPORARY TABLE tmp_in 
  SELECT product_name,sum(in_count) AS in_count
  FROM table_in
  GROUP BY product_name;

# Do the same for the other table
CREATE TEMPORARY TABLE tmp_out 
  SELECT product_name,sum(out_count) AS out_count
  FROM table_out
  GROUP BY product_name;

# Create the new table "table_new" by joining the temporary tables:
CREATE TABLE table_new
  SELECT tmp_in.product_name,SUM(in_count) - SUM(out_count) AS new_count
  FROM tmp_in
  LEFT JOIN tmp_out USING(product_name)
  GROUP BY tmp_in.product_name


-- 
Roger

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

RE: OS X - can't set root password

[Fortuno, Adam]

Robert,

If I follow you're email correctly, it looks like you attempting to assign a
password to the root user twice. The first statement you provide:

> bin/mysqladmin -u root password **

Sets the password to whatever value you've set in-place of **. Then in
the statement below you attempt to set the password again.

> /usr/local/mysql/bin/mysqladmin -u root -h `hostname` password **

Take a good look at that second line. You're assigning a new password to the
root account, but when you attempt to reset the password for root as root
you don't use root's new (as set in line 1) password. Make sense?

Normally, once you've set the root password you don't need to set it again.
If you garble the password and cannot access the root account restart MySQL
and bypass the GRANT tables (see below for direction).

If you need more help, let me know. I'm running 4.0.15 on a 12" PB with OS X
10.2.8 (get that system patch brother!).

# See what MySQL processes are running.
ps -aux | grep -i mysql

# Enable the sudo construct (you will need to enter your OS X password)
sudo -v

# Start MySQL and without using the grant tables.
sudo mysqld_safe --user=mysql --skip-grant-tables &

# Assign a new root password (ONLY do this ONCE)
mysqladmin -uroot password 'password'

# Access MySQL as root. You will be prompted to enter root's password.
mysql -uroot -p

Regards,
Adam

-Original Message-
From: Robert Lund [mailto:[EMAIL PROTECTED]
Sent: Saturday, October 18, 2003 8:32 PM
To: [EMAIL PROTECTED]
Subject: OS X - can't set root password


I just installed 4.0.15 on an iBook running Mac OS X 10.2.6. I started 
the daemon using bin/mysqld_safe, and then tried to initialize the root 
password as instructed. The first,

bin/mysqladmin -u root password **

ran apparently successfully. When I tried to execute the second:

/usr/local/mysql/bin/mysqladmin -u root -h `hostname` password **

the response was:

/usr/local/mysql/bin/mysqladmin: connect to server at 'lazarus.local.' 
failed
error: 'Host '192.168.1.100' is not allowed to connect to this MySQL server'

That is the IP address assigned my Mac by my DSL router; and its name is 
'lazarus'.
Where can I find out the source of this permissions gap and how the 
channel might be opened up?

Thanks

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

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

Re:Re-indexing problem on large table

[gunnar . lunde]

Hi,

We have given up trying to understand why the problem (please see below)
occurs, and have moved on to see what will be the best workaround. We have
three different suggestions. Alternatives 1 and 2 below are under the
assumption that setting the max_rows parameter to 10.000.000.000 caused the
problem


Regenerate the table without the max_rows set.

As described below we have a table 20G of data (76 million records) and a
set of indexes where only one is unique.  We create a new table without the
max_rows set (use the default) and only the unique index and fill it with
all the data.  The next step will be to add the other indexes with one alter
table statement

Regenerate the table with max_rows set to 300 million.

This is a limit that is a little less then twice what we expect to be max.
Other than that this is a copy of number 1 above.

We dump the table with the -e parameter set and pipe it in to a new table
with all indexes in place



I would be very happy if you let us know any advantages/disadvantages you
might see with the three alternatives.

Best regards

Gunnar Lunde

-
Hello!

I hope someone can help us with a problem we have:

We had a table that was 35G big and the index file was about 16G (ca 120
million records).  We ran into a problem where the database produced an "no
more room in index file" error.  

To fix this we regenerated the table and set max_rows to 10.000.000.000.
(MySQL changed max_rows to 4.294.967.295, which I guess is an upper limit?).
We stripped the table for all indecies except one unique index and inserted
the data. We used the opportunity to remove old, unneeded data leaving us
with a table of 20G and index file of 4G.

Now, our next step was to add the other indecies. We added all indecies with
one alter table statement. Mysql started with "copy to tmp table" and after
a few days it changed to "repair by keycache", which is odd because we have
set the mysql variables pretty high (se below), so we had anticipated
"repair by sorting"

When finished, the index file should be about 10G, but when the tmp MYI
reach 4.5 G it slows down and when it reach 5.2G it stops growing. There is
no error message. The CPU usage is minimal and the IO is also minimal
compared to what we have seen the disk system perform earlier. (It's a 1+0
raid with 10 disks, IIRC.There is also plenty of disk space)
Could it be that setting the max_rows to this high number and then adding
the indecies would make the index tree balancing go astray? Any pointer to a
solution would be very welcome!

All table are MyISAM. (I know that it might not bee the smartest type to
use)
Mysql version : 3.23.41
OS: RedHat 7.1(Linux version 2.4.9-31Enterprise) (Red Hat Linux 7.1 2.96-98)
DISK The diskset is a 1+0 raid with about 10 15k rpm disks  (plenty of
space)


---+

-+ 
| Variable_name | Value | 
+-+-


+ 
| back_log | 50 
| basedir | /usr/ 
| bdb_cache_size | 8388600 
| bdb_log_buffer_size | 262144 
| bdb_home | /data/mysql/ 
| bdb_max_lock | 1 
| bdb_logdir | 
| bdb_shared_data | OFF 
| bdb_tmpdir | /data/mysql/ 
| bdb_version | Sleepycat Software: Berkeley DB 3.2.9a: (August 11, 2001) 
| binlog_cache_size | 32768 
| character_set | latin1 
| character_sets | latin1 big5 cp1251 cp1257 croat czech danish dec8 dos
estonia euc_kr gb2312 gbk german1 greek hebrew hp8 hungarian koi8_ru
koi8_ukr latin2 latin5 swe7 usa7 win1250 win1251 win1251ukr ujis sjis tis620
| 
| concurrent_insert | ON 
| connect_timeout | 5 
| datadir | /data/mysql/ 
| delay_key_write | ON 
| delayed_insert_limit | 100 
| delayed_insert_timeout | 300 
| delayed_queue_size | 1000 
| flush | OFF 
| flush_time | 0 
| have_bdb | YES 
| have_gemini | NO 
| have_innodb | NO 
| have_isam | YES 
| have_raid | NO 
| have_ssl | NO 
| init_file | 
| interactive_timeout | 28800 
| join_buffer_size | 131072 
| key_buffer_size | 402649088 
| language | /usr/share/mysql/english/ 
| large_files_support | ON 
| locked_in_memory | OFF 
| log | OFF 
| log_update | OFF 
| log_bin | ON 
| log_slave_updates | OFF 
| log_long_queries | OFF 
| long_query_time | 10 
| low_priority_updates | OFF 
| lower_case_table_names | 0 
| max_allowed_packet | 1047552 
| max_binlog_cache_size | 4294967295 
| max_binlog_size | 1073741824 
| max_connections | 100 
| max_connect_errors | 10 
| max_delayed_threads | 20 
| max_heap_table_size | 16777216 
| max_join_size | 4294967295 
| max_sort_length | 1024 
| max_user_connections | 0 
| max_tmp_tables | 32 
| max_write_lock_count | 4294967295 
| myis

Re: Troubles with InnoDB perfomance.

[Mikhail Entaltsev]

Forgot to mention...

OS is not swapping... The load on MySQL server is the same...

Usually my system has ~100 selects/sec and 0.2 inserts/sec.
And I found that during inserts MySQL uses 100% of CPU, 
before it used ~5-10% CPU maximum...

I understand that it's difficult to say what is going wrong 
without checking. 
But may be you can give me some advices 
or you can point me something that can help me 
to understand the reason of my troubles.
I appreciate any help.

Mikhail.


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

RE: SELECT Question

[Barry Byrne]

> -Original Message-
> From: Mumba Chucks [mailto:[EMAIL PROTECTED]

>
> I've been given a table to work with, and I'm not
> meant to change it:
>
> -
> | TABLE_NAME  | TBL_IDX | KEYW_ID |
> -
> | PROPERTIES  | 108 | 16  |
> -
> | PROPERTIES| 119 | 16  |
> -
> | PROPERTIES| 108 | 62  |
> -
> | PROPERTIES| 119 | 16  |
> -
> | PROPERTIES| 135 | 16  |
> -
> | PROPERTIES| 135 | 17  |
> -
>
> How do I select out and filter only rows that match
> both 16 and 62 in the KEYW_ID col?  IE.  The query
> would return only 119 and 108?

I'm sure this could be done more effeciently other ways, possibly with a sub
select if available, but something like this would probably work:

SELECT temp1.*
FROM table_name AS temp1
LEFT JOIN table_name AS temp2
ON temp1.tbl_idx=temp2.tbl_idx
WHERE (temp1.keyw_id=16 AND temp2.keyw_id=62)
OR (temp1.keyw_id=62 AND temp2.keyw_id=16);

 - Barry


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

Re-indexing problem on large table

[gunnar . lunde]

Hello!

I hope someone can help us with a problem we have:

We had a table that was 35G big and the index file was about 16G (ca 120
million records).  We ran into a problem where the database produced an "no
more room in index file" error.  

To fix this we regenerated the table and set max_rows to 10.000.000.000.
(MySQL changed max_rows to 4.294.967.295, which I guess is an upper limit?).
We stripped the table for all indecies except one unique index and inserted
the data. We used the opportunity to remove old, unneeded data leaving us
with a table of 20G and index file of 4G.

Now, our next step was to add the other indecies. We added all indecies with
one alter table statement. Mysql started with "copy to tmp table" and after
a few days it changed to "repair by keycache", which is odd because we have
set the mysql variables pretty high (se below), so we had anticipated
"repair by sorting"

When finished, the index file should be about 10G, but when the tmp MYI
reach 4.5 G it slows down and when it reach 5.2G it stops growing. There is
no error message. The CPU usage is minimal and the IO is also minimal
compared to what we have seen the disk system perform earlier. (It's a 1+0
raid with 10 disks, IIRC.There is also plenty of disk space)
Could it be that setting the max_rows to this high number and then adding
the indecies would make the index tree balancing go astray? Any pointer to a
solution would be very welcome!

All table are MyISAM. (I know that it might not bee the smartest type to
use)
Mysql version : 3.23.41
OS: RedHat 7.1(Linux version 2.4.9-31Enterprise) (Red Hat Linux 7.1 2.96-98)
DISK The diskset is a 1+0 raid with about 10 15k rpm disks  (plenty of
space)


---+

-+ 
| Variable_name | Value | 
+-+-


+ 
| back_log | 50 
| basedir | /usr/ 
| bdb_cache_size | 8388600 
| bdb_log_buffer_size | 262144 
| bdb_home | /data/mysql/ 
| bdb_max_lock | 1 
| bdb_logdir | 
| bdb_shared_data | OFF 
| bdb_tmpdir | /data/mysql/ 
| bdb_version | Sleepycat Software: Berkeley DB 3.2.9a: (August 11, 2001) 
| binlog_cache_size | 32768 
| character_set | latin1 
| character_sets | latin1 big5 cp1251 cp1257 croat czech danish dec8 dos
estonia euc_kr gb2312 gbk german1 greek hebrew hp8 hungarian koi8_ru
koi8_ukr latin2 latin5 swe7 usa7 win1250 win1251 win1251ukr ujis sjis tis620
| 
| concurrent_insert | ON 
| connect_timeout | 5 
| datadir | /data/mysql/ 
| delay_key_write | ON 
| delayed_insert_limit | 100 
| delayed_insert_timeout | 300 
| delayed_queue_size | 1000 
| flush | OFF 
| flush_time | 0 
| have_bdb | YES 
| have_gemini | NO 
| have_innodb | NO 
| have_isam | YES 
| have_raid | NO 
| have_ssl | NO 
| init_file | 
| interactive_timeout | 28800 
| join_buffer_size | 131072 
| key_buffer_size | 402649088 
| language | /usr/share/mysql/english/ 
| large_files_support | ON 
| locked_in_memory | OFF 
| log | OFF 
| log_update | OFF 
| log_bin | ON 
| log_slave_updates | OFF 
| log_long_queries | OFF 
| long_query_time | 10 
| low_priority_updates | OFF 
| lower_case_table_names | 0 
| max_allowed_packet | 1047552 
| max_binlog_cache_size | 4294967295 
| max_binlog_size | 1073741824 
| max_connections | 100 
| max_connect_errors | 10 
| max_delayed_threads | 20 
| max_heap_table_size | 16777216 
| max_join_size | 4294967295 
| max_sort_length | 1024 
| max_user_connections | 0 
| max_tmp_tables | 32 
| max_write_lock_count | 4294967295 
| myisam_recover_options | 0 
| myisam_max_extra_sort_file_size | 2000 
| myisam_max_sort_file_size | 2097152000 
| myisam_sort_buffer_size | 1572864000 
| net_buffer_length | 16384 
| net_read_timeout | 30 
| net_retry_count | 10 
| net_write_timeout | 60 
| open_files_limit | 0 
| pid_file | /data/mysql/dlp-news02.pid 
| port | 3306 
| protocol_version | 10 
| record_buffer | 2093056 
| record_rnd_buffer | 2093056 
| query_buffer_size | 0 
| safe_show_database | OFF 
| server_id | 2 
| slave_net_timeout | 3600 
| skip_locking | ON 
| skip_networking | OFF 
| skip_show_database | OFF 
| slow_launch_time | 2 
| socket | /var/lib/mysql/mysql.sock 
| sort_buffer | 2097144 
| sql_mode | 0 
| table_cache | 512 
| table_type | MYISAM 
| thread_cache_size | 8 
| thread_stack | 65536 
| transaction_isolation | READ-COMMITTED 
| timezone | CEST 
| tmp_table_size | 33554432 
| tmpdir | /data/mysql/ 
| version | 3.23.41-log 
| wait_timeout | 28800 




Mvh / Best Regards

Gunnar Lunde

sql

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

SELECT Question

[Mumba Chucks]

Hi,

I've been given a table to work with, and I'm not
meant to change it:

- 
| TABLE_NAME| TBL_IDX | KEYW_ID |
-
| PROPERTIES| 108 | 16  |
-
| PROPERTIES| 119 | 16  |
-
| PROPERTIES| 108 | 62  |
-
| PROPERTIES| 119 | 16  |
-
| PROPERTIES| 135 | 16  |
-
| PROPERTIES| 135 | 17  |
-

How do I select out and filter only rows that match
both 16 and 62 in the KEYW_ID col?  IE.  The query
would return only 119 and 108?

Any help much appreciated

Mumba.


Want to chat instantly with your online friends?  Get the FREE Yahoo!
Messenger http://mail.messenger.yahoo.co.uk

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

Re: InnoDB or OS restriction? - sorry for duplicated posts

[alex]

I'm very sorry for the duplicated posts, my mail softtware behaved wrong
:(

---
Alex


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

Re: InnoDB or OS restriction?

[alex]

Hi again,

as there was not a single answer to my question I can imagine that no one
encountered the same issue, but anyways, can there be any hints? First of
all, are there any means of looking at mysql memory allocation list
grouped by some major parts - for example,
innodb main pool - can be retrieved from innodb monitor
innodb additional pool - can be retrieved from innodb monitor
myisam main cache - ...?
myisam sort buffer - ...?
memory allocated from OS - ...?

The questiion is why mysql is trying to allocate memory via malloc from OS
while the innodb additional pool is occupied only by 50%? Or is it myisam
buffer that mysql is trying to extend?

Regards

---
Alex

On Tue, 14 Oct 2003, alex wrote:

> Hi people,
>
> I have mysql 4.0.7-gamma/linuxthreads running under FreeBSD 4.6.2 (server
> has 4G phisical memory), and occasionally mysql traps with the message:
>
> InnoDB: Fatal error: cannot allocate 1064960 bytes of
> InnoDB: memory with malloc! Total allocated memory
> InnoDB: by InnoDB 513951016 bytes. Operating system errno: 12
> InnoDB: Cannot continue operation!
>
> The first strange thing is that MAXDSIZ and DFLDSIZ in kernel config are
> setted to 1536M, and the second one is that each time it happens, while
> the number of bytes reported to cannot be allocated is different, total
> memory allocated by InnoDB is exactly the same - 513951016 bytes. Which
> restrictions are the cause of this - InnoDB's or FreeBSD's?
>
> Thanks in advance
>
> 
> Alex
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>

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

Re: InnoDB or OS restriction?

[alex]

Hi again,

as there was not a single answer to my question I can imagine that no one
encountered the same issue, but anyways, can there be any hints? First of
all, are there any means of looking at mysql memory allocation list
grouped by some major parts - for example,
innodb main pool - can be retrieved from innodb monitor
innodb additional pool - can be retrieved from innodb monitor
myisam main cache - ...?
myisam sort buffer - ...?
memory allocated from OS - ...?

The questiion is why mysql is trying to allocate memory via malloc from OS
while the innodb additional pool is occupied only by 50%? Or is it myisam
buffer that mysql is trying to extend?

Regards

---
Alex

On Tue, 14 Oct 2003, alex wrote:

> Hi people,
>
> I have mysql 4.0.7-gamma/linuxthreads running under FreeBSD 4.6.2 (server
> has 4G phisical memory), and occasionally mysql traps with the message:
>
> InnoDB: Fatal error: cannot allocate 1064960 bytes of
> InnoDB: memory with malloc! Total allocated memory
> InnoDB: by InnoDB 513951016 bytes. Operating system errno: 12
> InnoDB: Cannot continue operation!
>
> The first strange thing is that MAXDSIZ and DFLDSIZ in kernel config are
> setted to 1536M, and the second one is that each time it happens, while
> the number of bytes reported to cannot be allocated is different, total
> memory allocated by InnoDB is exactly the same - 513951016 bytes. Which
> restrictions are the cause of this - InnoDB's or FreeBSD's?
>
> Thanks in advance
>
> 
> Alex
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>

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

Best Setup.

[Patrick]

Hi,

Im currently using mysql 4.0.15
what could i do in the my.cnf to make it work better faster :)

i use innodb
and its a linux box with kernel 2.4.23_pre
and its got xfs on it and its got a gig of ram as well as 2x 2.6ghz
hyperthreading xeons

P


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

Re: Error 1045

[Director General: NEFACOMP]

Add the -p option to tell mySQL to ask for a password.

For example
c:\mysql\bin\mysql -u root -p

Or if you want to give the password without a prompt
c:\mysql\bin\mysql -u root -p YourPassWord

Or if you are connecting on another Server
c:\mysql\bin\mysql -u root -h RemoteServerAddress -p


Hope this helps


Thanks
Emery
- Original Message -
From: "Stephan Wölfel" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, October 14, 2003 18:05
Subject: Re: Error 1045


> But MySQL even doesn't ask me for a password but returns immediately this
> error.
>
> - Original Message -
> From: "gerald_clark" <[EMAIL PROTECTED]>
> To: "Stephan Wölfel" <[EMAIL PROTECTED]>
> Cc: <[EMAIL PROTECTED]>
> Sent: Monday, October 13, 2003 10:14 PM
> Subject: Re: Error 1045
>
>
> > You are not entering the right password.
> > If you did NOT set a password for root, then leave the -p option off.
> >
> > Stephan Wölfel wrote:
> >
> > >When I start MySQL with "mysql -u root -p" I get again the error 1045
> > >("Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)".
> > >What am I doing wrong ?
> > >
> > >- Original Message -
> > >From: "gerald_clark" <[EMAIL PROTECTED]>
> > >To: "Stephan Wölfel" <[EMAIL PROTECTED]>
> > >Cc: <[EMAIL PROTECTED]>
> > >Sent: Monday, October 13, 2003 5:54 PM
> > >Subject: Re: Error 1045
> > >
> > >
> > >
> > >
> > >>Stephan Wölfel wrote:
> > >>
> > >>
> > >>
> > >>>Thanks for the advise.
> > >>>However I have some additional questions:
> > >>>ad 2) How can I check the grant privileges ?
> > >>>ad 3) How can I "run this as the mysql root user" ?
> > >>>
> > >>>
> > >>>
> > >>mysql -u root -p
> > >>enter the root password you set when you installed mysql
> > >>use mysql
> > >>enter the grant command.
> > >>
> > >>
> > >>
> > >>>- Original Message -
> > >>>From: "gerald_clark" <[EMAIL PROTECTED]>
> > >>>To: "Stephan Wölfel" <[EMAIL PROTECTED]>
> > >>>Cc: <[EMAIL PROTECTED]>
> > >>>Sent: Monday, October 13, 2003 4:47 PM
> > >>>Subject: Re: Error 1045
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>
> > >>--
> > >>MySQL General Mailing List
> > >>For list archives: http://lists.mysql.com/mysql
> > >>To unsubscribe:
> > >>
> > >>
> > >http://lists.mysql.com/[EMAIL PROTECTED]
> > >
> > >
> > >>
> > >>
> > >___
> > >M=FCde auf kleines Postfach? http://my-mail.ch/?redirect=3D9903
> > >Weine aus der Toskana! http://my-mail.ch/?redirect=3D1179
> > >
> > >
> > >
> > >
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
> _
> Schlappe =DCbertragungsgrenze? http://my-mail.ch/?redirect=3D9904
> Weine aus der Toskana! http://my-mail.ch/?redirect=3D1179
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
>



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

Re: InnoDB or OS restriction?

[alex]

Hi again,

as there was not a single answer to my question I can imagine that no one
encountered the same issue, but anyways, can there be any hints? First of
all, are there any means of looking at mysql memory allocation list
grouped by some major parts - for example,
innodb main pool - can be retrieved from innodb monitor
innodb additional pool - can be retrieved from innodb monitor
myisam main cache - ...?
myisam sort buffer - ...?
memory allocated from OS - ...?

The questiion is why mysql is trying to allocate memory via malloc from OS
while the innodb additional pool is occupied only by 50%? Or is it myisam
buffer that mysql is trying to extend?

Regards

---
Alex

On Tue, 14 Oct 2003, alex wrote:

> Hi people,
>
> I have mysql 4.0.7-gamma/linuxthreads running under FreeBSD 4.6.2 (server
> has 4G phisical memory), and occasionally mysql traps with the message:
>
> InnoDB: Fatal error: cannot allocate 1064960 bytes of
> InnoDB: memory with malloc! Total allocated memory
> InnoDB: by InnoDB 513951016 bytes. Operating system errno: 12
> InnoDB: Cannot continue operation!
>
> The first strange thing is that MAXDSIZ and DFLDSIZ in kernel config are
> setted to 1536M, and the second one is that each time it happens, while
> the number of bytes reported to cannot be allocated is different, total
> memory allocated by InnoDB is exactly the same - 513951016 bytes. Which
> restrictions are the cause of this - InnoDB's or FreeBSD's?
>
> Thanks in advance
>
> 
> Alex
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>

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

Re: InnoDB or OS restriction?

[alex]

Hi again,

as there was not a single answer to my question I can imagine that no one
encountered the same issue, but anyways, can there be any hints? First of
all, are there any means of looking at mysql memory allocation list
grouped by some major parts - for example,
innodb main pool - can be retrieved from innodb monitor
innodb additional pool - can be retrieved from innodb monitor
myisam main cache - ...?
myisam sort buffer - ...?
memory allocated from OS - ...?

The questiion is why mysql is trying to allocate memory via malloc from OS
while the innodb additional pool is occupied only by 50%? Or is it myisam
buffer that mysql is trying to extend?

Regards

---
Alex

On Tue, 14 Oct 2003, alex wrote:

> Hi people,
>
> I have mysql 4.0.7-gamma/linuxthreads running under FreeBSD 4.6.2 (server
> has 4G phisical memory), and occasionally mysql traps with the message:
>
> InnoDB: Fatal error: cannot allocate 1064960 bytes of
> InnoDB: memory with malloc! Total allocated memory
> InnoDB: by InnoDB 513951016 bytes. Operating system errno: 12
> InnoDB: Cannot continue operation!
>
> The first strange thing is that MAXDSIZ and DFLDSIZ in kernel config are
> setted to 1536M, and the second one is that each time it happens, while
> the number of bytes reported to cannot be allocated is different, total
> memory allocated by InnoDB is exactly the same - 513951016 bytes. Which
> restrictions are the cause of this - InnoDB's or FreeBSD's?
>
> Thanks in advance
>
> 
> Alex
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>

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

Re: InnoDB or OS restriction?

[alex]

Hi again,

as there was not a single answer to my question I can imagine that no one
encountered the same issue, but anyways, can there be any hints? First of
all, are there any means of looking at mysql memory allocation list
grouped by some major parts - for example,
innodb main pool - can be retrieved from innodb monitor
innodb additional pool - can be retrieved from innodb monitor
myisam main cache - ...?
myisam sort buffer - ...?
memory allocated from OS - ...?

The questiion is why mysql is trying to allocate memory via malloc from OS
while the innodb additional pool is occupied only by 50%? Or is it myisam
buffer that mysql is trying to extend?

Regards

---
Alex

On Tue, 14 Oct 2003, alex wrote:

> Hi people,
>
> I have mysql 4.0.7-gamma/linuxthreads running under FreeBSD 4.6.2 (server
> has 4G phisical memory), and occasionally mysql traps with the message:
>
> InnoDB: Fatal error: cannot allocate 1064960 bytes of
> InnoDB: memory with malloc! Total allocated memory
> InnoDB: by InnoDB 513951016 bytes. Operating system errno: 12
> InnoDB: Cannot continue operation!
>
> The first strange thing is that MAXDSIZ and DFLDSIZ in kernel config are
> setted to 1536M, and the second one is that each time it happens, while
> the number of bytes reported to cannot be allocated is different, total
> memory allocated by InnoDB is exactly the same - 513951016 bytes. Which
> restrictions are the cause of this - InnoDB's or FreeBSD's?
>
> Thanks in advance
>
> 
> Alex
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>

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

Re: InnoDB or OS restriction?

[alex]

Hi again,

as there was not a single answer to my question I can imagine that no one
encountered the same issue, but anyways, can there be any hints? First of
all, are there any means of looking at mysql memory allocation list
grouped by some major parts - for example,
innodb main pool - can be retrieved from innodb monitor
innodb additional pool - can be retrieved from innodb monitor
myisam main cache - ...?
myisam sort buffer - ...?
memory allocated from OS - ...?

The questiion is why mysql is trying to allocate memory via malloc from OS
while the innodb additional pool is occupied only by 50%? Or is it myisam
buffer that mysql is trying to extend?

Regards

---
Alex

On Tue, 14 Oct 2003, alex wrote:

> Hi people,
>
> I have mysql 4.0.7-gamma/linuxthreads running under FreeBSD 4.6.2 (server
> has 4G phisical memory), and occasionally mysql traps with the message:
>
> InnoDB: Fatal error: cannot allocate 1064960 bytes of
> InnoDB: memory with malloc! Total allocated memory
> InnoDB: by InnoDB 513951016 bytes. Operating system errno: 12
> InnoDB: Cannot continue operation!
>
> The first strange thing is that MAXDSIZ and DFLDSIZ in kernel config are
> setted to 1536M, and the second one is that each time it happens, while
> the number of bytes reported to cannot be allocated is different, total
> memory allocated by InnoDB is exactly the same - 513951016 bytes. Which
> restrictions are the cause of this - InnoDB's or FreeBSD's?
>
> Thanks in advance
>
> 
> Alex
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>

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

Re: Error 1044

[Egor Egorov]

[EMAIL PROTECTED] wrote:
> I recently downloaded MySQL v3.23 on my Windows 2000 laptop.  When I try to
> create a database at the mysql> prompt, I get the message "Error 1044:
> Access denied for user '@localhost' to database xx."  I originally
> assigned a username and password when I first ran the WinMySQL admin 1.4
> tool.  I tried changing this as well, but still no luck.  Please help.

This error means that you connect as anonymous user. Use -u option of mysql to connect 
as a root user and create new users:
shell> mysql -uroot

Here you can find info how to change password, add new user, set up privileges:
http://www.mysql.com/doc/en/Default_privileges.html
http://www.mysql.com/doc/en/Passwords.html
http://www.mysql.com/doc/en/Adding_users.html
http://www.mysql.com/doc/en/GRANT.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




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

Re: Why is there no REGEXP()?

[zzapper]

On Sun, 19 Oct 2003 22:08:30 -0400, Kelley Lingerfelt
<[EMAIL PROTECTED]> wrote:


>
>WHERE lookup RLIKE "this|that|other|^starts like this|ends like this$|it
>contains.*something.*like.*this"
>

I am not yet expert enough in  mySQL to even specify my question
accurately (so I will have another go)

In the following where_condtion  I can use functions such as
left(),right()
where left(t1.txtDevPostCode,5) in
(#QuotedValueList(qryRadius.postcode)#)

but unfortunately UK postcodes are variable length so i need a REGEXP

SO I really want something like

where REGEXP(t1.txtDevPostCode,"^[a-z]{1,2}[0-9]0,2}") in
(#QuotedValueList(qryRadius.postcode)#) ##NOT POSSIBLE##

You can see what complicates the above is using the in
QuotedValueList.

But what's REALLY unusual about this query is that I want to
manipulate the contents of a TABLE COLUMN (txtdevPostCODE) before
doing the comparison, I seem to need some kind of inner query.

can U help?

zzapper
--

vim -c ":%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?"

http://www.vim.org/tips/tip.php?tip_id=305  Best of Vim Tips


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

Troubles with InnoDB perfomance.

[Mikhail Entaltsev]

show statusHi all,

I am running MySQL version 4.0.14-standard-log on Suse Linux 2.4.20 i686.
All tables in my db are InnoDB tables.

Today morning I've got troubles with perfomance...

Generally all queries are slower in 4-5 times than usually.
Also I see that number of deadlocks is in 10-15 times more than usually.

I didn't change any code that is working with MySQL,
didn't upgrade any software on the server, didn't change any configuration
parameters.

Last week everything worked perfectly...
On weekend I've executed 3 commands:
1. OPTIMIZE TABLES 
2. ANALYZE TABLE for every table
3. CHECK TABLE for every table

No errors.

I've checked /var/log/messages and MySQL log file as well. Everything looks
good.
I have no idea what's happend :(

Any suggestions, comments, question are really appreciated.

Thanks in advace,
Mikhail.

Below are outputs of 'show variables' and 'show status' commands.

show variables (121 Records)
Variable_nameValue
back_log 50
basedir /usr/local/mysql-standard-4.0.14-pc-linux-i686/
binlog_cache_size 32768
bulk_insert_buffer_size 1048576
character_set latin1
character_sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620
ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew
win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257
latin5
concurrent_insert ON
connect_timeout 5
convert_character_set
datadir /usr/local/mysql/data/
default_week_format 0
delay_key_write OFF
delayed_insert_limit 100
delayed_insert_timeout 300
delayed_queue_size 1000
flush OFF
flush_time 0
ft_boolean_syntax + -><()~*:""&|
ft_min_word_len 4
ft_max_word_len 254
ft_max_word_len_for_sort 20
ft_stopword_file (built-in)
have_bdb NO
have_crypt YES
have_innodb YES
have_isam YES
have_raid NO
have_symlink YES
have_openssl NO
have_query_cache YES
init_file
innodb_additional_mem_pool_size 20971520
innodb_buffer_pool_size 335544320
innodb_data_file_path
ibdata1:2000M;ibdata2:2000M;ibdata3:2000M;ibdata4:2000M;ibdata5:2000M;ibdata
6:10M:autoextend
innodb_data_home_dir /usr/local/mysql/data/
innodb_file_io_threads 4
innodb_force_recovery 0
innodb_thread_concurrency 8
innodb_flush_log_at_trx_commit 1
innodb_fast_shutdown ON
innodb_flush_method O_DSYNC
innodb_lock_wait_timeout 50
innodb_log_arch_dir /usr/local/mysql/data/
innodb_log_archive OFF
innodb_log_buffer_size 8388608
innodb_log_file_size 83886080
innodb_log_files_in_group 2
innodb_log_group_home_dir /usr/local/mysql/data/
innodb_mirrored_log_groups 1
innodb_max_dirty_pages_pct 90
interactive_timeout 28800
join_buffer_size 520192
key_buffer_size 2097152
language /usr/local/mysql-standard-4.0.14-pc-linux-i686/share/mysql/english/
large_files_support ON
local_infile ON
locked_in_memory OFF
log OFF
log_update OFF
log_bin ON
log_slave_updates OFF
log_slow_queries ON
log_warnings ON
long_query_time 10
low_priority_updates OFF
lower_case_table_names OFF
max_allowed_packet 1047552
max_binlog_cache_size 4294967295
max_binlog_size 1073741824
max_connections 100
max_connect_errors 10
max_delayed_threads 20
max_heap_table_size 16777216
max_join_size 4294967295
max_relay_log_size 0
max_seeks_for_key 4294967295
max_sort_length 1024
max_user_connections 0
max_tmp_tables 32
max_write_lock_count 4294967295
myisam_max_extra_sort_file_size 268435456
myisam_max_sort_file_size 2147483647
myisam_repair_threads 1
myisam_recover_options OFF
myisam_sort_buffer_size 67108864
net_buffer_length 16384
net_read_timeout 30
net_retry_count 10
net_write_timeout 60
new OFF
open_files_limit 0
pid_file /usr/local/mysql/data/blackcat.pid
log_error
port 3306
protocol_version 10
read_buffer_size 1044480
read_only OFF
read_rnd_buffer_size 1044480
rpl_recovery_rank 0
query_cache_limit 1048576
query_cache_size 16777216
query_cache_type ON
server_id 1
slave_net_timeout 3600
skip_external_locking ON
skip_networking OFF
skip_show_database OFF
slow_launch_time 2
socket /tmp/mysql.sock
sort_buffer_size 4194296
sql_mode 0
table_cache 256
table_type MYISAM
thread_cache_size 8
thread_stack 126976
tx_isolation REPEATABLE-READ
timezone GMT
tmp_table_size 41943040
tmpdir /tmp/
version 4.0.14-standard-log
wait_timeout 28800



show status (132 Records)
Variable_nameValue
Aborted_clients 1094
Aborted_connects 9
Bytes_received 3370224049
Bytes_sent 1615721075
Com_admin_commands 1536
Com_alter_table 0
Com_analyze 1
Com_backup_table 0
Com_begin 3360480
Com_change_db 625
Com_change_master 0
Com_check 1
Com_commit 3360480
Com_create_db 0
Com_create_function 0
Com_create_index 0
Com_create_table 362871
Com_delete 8
Com_delete_multi 0
Com_drop_db 0
Com_drop_function 0
Com_drop_index 0
Com_drop_table 245
Com_flush 0
Com_grant 0
Com_ha_close 0
Com_ha_open 0
Com_ha_read 0
Com_insert 407
Com_insert_select 362737
Com_kill 2
Com_load 0
Com_load_master_data 0
Com_load_master_table 0
Com_lock_tables 8
Com_optimize 0
Com_purge 0
Com_rename_table 0
Com_repair 0
Com_replace 0
Com_replace_select 0
Com_reset 2
Com_restore_table 0
Com_revoke 0
Com_rollback 0
Com_savepoint 0
Com_select 649791
C

Re: ERROR 2013 & 2369 SegmentationFault

[Victoria Reznichenko]

Alexander K?ppe <[EMAIL PROTECTED]> wrote:
> Hi,
> I have trouble to connect from a remote host to the mysql-server.
> On localhost works all right.
> But when I want to establish a connection with
> 
> mysql --host=server --user=com75778 --database=com75778 --pass
> Enter password:
> ERROR 2013: Lost connection to MySQL server during query
> 
> 
> from another host, as the server, this error Message appears on client's 
> console.
> 
> On every (failed) try to connect, an error message also appears on server's 
> console:
> 
> /usr/bin/safe_mysqld: line 273:  2369 Segmentation fault  $NOHUP_NICENESS 
> $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR 
> $USER_OPTION --pid-file=$pid_file --skip-locking >>$err_log 2>&1
> 
> Number of processes running now: 1
> mysqld process hanging, pid 2372 - killed
> 031018 22:11:51  mysqld restarted
> 
> 
> Do someone know this problem and can help me.

What OS do you use? What is the version of MySQL? Did you install MySQL from binary 
distribution?


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





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

Re: Getting remote connected machine as value in a select.

[Director General: NEFACOMP]

I don't know I well understand your problem.
There is a function SESSION_USER() in MySQL that returns the currently
connected user. The user string also returns the host from which he
connected in the form [EMAIL PROTECTED]

So, by issuing the query:
SELECT SESSION_USER(), Table.* from Table
you will get the needed data.

But the problem is that you will need to get the host name from that
username. Also it truncates the string when it is long
There is also USER() that returns the same [EMAIL PROTECTED] . I really don't know
the real difference between the two functions.


Hope this helps


Thanks
Emery
- Original Message -
From: "Chris Faust" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Sunday, October 19, 2003 23:55
Subject: Getting remote connected machine as value in a select.


Hello,

I'm curious as to if something is possible - I don't know if its possible to
explain it right.

I have a table which contains a bunch machine specific information, file
paths, names etc.

What I would like to try and accomplish is when I query this table for the
information I need, I also receive back a value (that I can define) that is
determined by the identity of the machine making the query - either by IP
address or whatever.

For an example say I have 3 machines total.
1 mySQL DB Server
2 web servers

When I connect to mySQL from either of the 2 web servers and say something
like "Select * from table", one of the values returned would be something
like "web1" as that is what I defined to be the value for when machine
192.168.0.4 connected to it.
It doesn't have to be IP, I'm just using that for a example.

Any ideas on how to accomplish something like this?
mySql 4.0
RH 9

Thanks
-Chris



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

Re: Quotes and loading

[Santino]

At 15:58 +1000 20-10-2003, Kim Kohen wrote:
Hello all

I have a bit of a problem with some characters I'm loading from a Filemaker
Pro database. The single quotes are stored in MySQL as ASCII character 155
(an 'O' with a tilde over it). I have tried everything I can think of to
replace this with the PHP I use to query the db but I have failed miserably.
I have similar problems with all single quotes and double quotes.
I could probably process the text file before loading it into MySQL
(replacing the quotes with their HTML special characters) but I'm wondering
if there is anything more fundamental I should be doing.
Cheers and any assistance appreciated

kim

I have had a similar problem importing text data from Mac To Linux.
MySql uses ISO character encoding and the Mac (OS 9) uses MacRoman.
I wrote a small filter that converts MacRoman to ISO.
If You use ascii chars < 128 all works fine but ascii char > 128 are 
mapped in different position so You have to convert them. (eg 
á  é).
Attention: There are few chars ( < 10) of MacRoman set that don't 
have an equivalent in ISO (eg Apple Symbol 0xf0 quotes 0xd2-0xd5).

Santino

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

Re: select in inverse order

[Director General: NEFACOMP]

The best method will be to adda TIMESTAMP.
But if you are sure that no record gets DELETEd you may use the following
procedure.

CREATE a temporary Table to store the records you want to use. That temp
table needs to have a key or ID.
SELECT from that temp table and order using that ID.

But I will recommend using a TIMESTAMP to avoid all these "acrobaties".


Thanks
Emery
- Original Message -
From: "dorilys" <[EMAIL PROTECTED]>
To: "'Roger Baklund'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Sunday, October 19, 2003 03:01
Subject: RE : select in inverse order


> Hi,
>
> > If your table has a primary key or unique integer key you can order
> > descending using the keyword "_ROWID" like this:
> >
> > SELECT * FROM table WHERE NUMERO='248' ORDER BY _ROWID DESC;
> >
>
> I just tried your solution but unfortunately I got an error
>
> My table have no key
>
> In fact my problem is it.
> The users insert records,
> I list the records at the end of the page,
>
> Like this
>
> Formular for input new recors
>
> First record
> Second record
> Third record
>
>  but It's better if I can succeed to invert the order
>
> Formular for input new recors
>
> Third record
> Second record
> First record
>
> With this solution, my users avoid to scroll the page for checking the
> last input.
>
> I prefer modify the syntax of the SELECT because it's the same PHP page
> (by include) who control all my tables (+ ou -  100 tables).
> Inserting a autoincrementing primary key in all tables represent a long
> work and a waste of place, but if I can't find a solution by syntax,
> I have to do that :-(
>
> Thanks a lot, Roger for your answer and for your help.
> Dorilys
>
>
>



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