Re: Hijackers?

[Duncan Hill]

On Tuesday 13 December 2005 02:25, Peter Lauri wrote:
> Best group member,
>
>
>
> How can I prevent people from hijacking a query? I read this in an article
> about a few months ago, but now I can not find that article again. This
> question is maybe not so exact, and I do not know how risky it is to not
> protect your system from database hijackers?

I believe what you're referring to are SQL Injection Attacks.

Hypothetical scenario:

You have a web script that runs some SELECT queries against a table.  One day, 
a malicious user happens to be using the site when an SQL error occurs, and 
your table/database name is displayed to them.  They change the content of a 
search box (or any other field in the web script that gets used directly in 
the query) to something like   1;'drop table mytable;'   .  Your script, 
which just so happens to have drop privs, happily executes a double query - 
the first part being what you wanted it to do, the second part being the drop 
table.  There goes all of your data.

The methods to defeat this, to the best of my knowledge, include limiting the 
privileges of the web script user (or any user) to only do what they need to 
do.  So if the script only needs to select data, don't give it any rights 
other than select, and if possible, only select on the tables it needs.  The 
other damage limitation option is to validate all of your input.  I use 
quoting on all fields, including integers, and in some fields I also use the 
HTML conversion functions to convert " to " etc.

-- 
Critical Software.

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

Re: Select Unique?

[John Mistler]

One further question on this topic ... What if I add a third table into 
the mix, so that:


database contains -- table1 (column1, column2), table2 (column1, 
column2, column3), table3 (column3)


What query will return all rows from table2 where the entry for 
table2.column1 does not match any entries in table1.column1 AND 
table2.column3 has a matching entry in table3.column3


The idea is that I want to further restrict the returned rows by 
requiring that the entry in table2.column3 is also found in an entry in 
table3.column3


Is there a nested left join that will work.  Something else?

Thanks,

John

On Dec 12, 2005, at 9:57 AM, Michael Stassen wrote:


Rhino wrote:
- Original Message - From: "John Mistler" 
<[EMAIL PROTECTED]>

To: 
Sent: Monday, December 12, 2005 12:34 AM
Subject: Select Unique?
I have two tables 'table1', 'table2' with a matching column 
'column1'.  How can I return all rows from table2 where the entry 
for table2.column1 does not match any entries in table1.column1?


SELECT * FROM table2 WHERE table2.column1 <> table1.column1

returns all the rows, rather than the unique rows in table2 ... Any 
ideas?



SELECT *
FROM table2
where table2.column1 not in
(select distinct column1
from table1
where column1 not null)


That should be "where column1 IS NOT NULL)".

The 'distinct' in the subquery is not strictly necessary but should 
help performance. The WHERE clause in the subquery is often omitted 
but really shouldn't be.

Rhino


If you're interested in performance, you probably shouldn't use a 
subquery.  If you put EXPLAIN in front, you'll see that mysql labels 
this a "DEPENDENT SUBQUERY", meaning it will rerun the subquery for 
each row in the outer query. The optimizer *should* be smart enough to 
run the inner query once, then compare rows to that list using the 
index, but it isn't.  As a test, I made a 25 row table and a copy 
missing 3 of those rows.  The subquery version took twice as long to 
execute (.12 sec) as the left join version (.06 sec).  The larger the 
tables involved, the larger the difference is likely to be.


Michael

(Test run on my iBook G4, OS X 10.3.9, mysql 4.1.15.)




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

RE: to track the changes in database mySQL

[Logan, David (SST - Adelaide)]

Hi Satya,

You might like to look at triggers here
http://dev.mysql.com/doc/refman/5.0/en/triggers.html You can set a
trigger to go off at any of the events you have noted below. This would
allow you to log the userid and any other relevant information you need.

Regards

David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia

+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax


-Original Message-
From: Satyanarayana_Kesani [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, 13 December 2005 3:13 PM
To: mysql@lists.mysql.com
Subject: to track the changes in database mySQL

Hi all,
I got this mail Id from mySQL lists.I would like to request you for one
help.
Actually I need to track the changes(UPDATE,INSERT,DELETE and others)
done on the database in mySQL by different users. I need to know which
user has done what changes . I am using mySQL 5.0.16-nt.
I would like to know how I can create binary log or some other log for
the same?
Could you please help me on this? 
 
Thanks in advance
 
Thanks&Regards,
   Satya
 


DISCLAIMER:
This email (including any attachments) is intended for the sole use of
the intended recipient/s and may contain material that is CONFIDENTIAL
AND PRIVATE COMPANY INFORMATION. Any review or reliance by others or
copying or distribution or forwarding of any or all of the contents in
this message is STRICTLY PROHIBITED. If you are not the intended
recipient, please contact the sender by email and delete all copies;
your cooperation in this regard is appreciated.

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

to track the changes in database mySQL

[Satyanarayana_Kesani]

Hi all,
I got this mail Id from mySQL lists.I would like to request you for one help.
Actually I need to track the changes(UPDATE,INSERT,DELETE and others) done on 
the database in mySQL by different users. I need to know which user has done 
what changes . I am using mySQL 5.0.16-nt.
I would like to know how I can create binary log or some other log for the same?
Could you please help me on this? 
 
Thanks in advance
 
Thanks&Regards,
   Satya
 


DISCLAIMER:
This email (including any attachments) is intended for the sole use of the 
intended recipient/s and may contain material that is CONFIDENTIAL AND PRIVATE 
COMPANY INFORMATION. Any review or reliance by others or copying or 
distribution or forwarding of any or all of the contents in this message is 
STRICTLY PROHIBITED. If you are not the intended recipient, please contact the 
sender by email and delete all copies; your cooperation in this regard is 
appreciated.

RE: Import Table?

[Logan, David (SST - Adelaide)]

Hi John,
 
Just do a normal join as you would, for the second table reference it in
this fashion, . as xyz. Here is an example that
I've used myself
 
use test;
 
SELECT
   s.name,
   c.Street_addr_1,
   c.Street_addr_2,
   c.Town,
   c.State,
   c.PostCode,
   SUM(s.tot_sales) as t_sales
INTO OUTFILE "./cardfile.csv"
FROM
   sales s
  INNER JOIN lcscreative.contact c
< *
 ON CONCAT(c.Contact_first_name, ' ', c.Contact_last_name) =
s.name
WHERE
   c.Country = "Australia" OR c.Country IS NULL
GROUP BY
   s.name
ORDER BY
   t_sales
DESC
LIMIT 149;

Check the *, this references another database/table
 
Regards
 

David Logan 
Database Administrator 
HP Managed Services 
148 Frome Street, 
Adelaide 5000 
Australia 

+61 8 8408 4273 - Work 
+61 417 268 665 - Mobile 
+61 8 8408 4259 - Fax 

 



From: John Mistler [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, 13 December 2005 1:53 PM
To: mysql@lists.mysql.com; Logan, David (SST - Adelaide)
Subject: Re: Import Table?


Sorry to bother you once more on this David, but I am having trouble
figuring out how to run a query on tables in two different databases. I
normally use 

/usr/local/mysql/bin/mysql -h localhost -u username -ppassword -D
databaseName -N -e SELECT ... 

as the initial string of the query. However, this only references one
database. How can I issue a query over two tables, one in each database?


Thanks, 

John 

On Dec 11, 2005, at 7:02 PM, Logan, David (SST - Adelaide) wrote: 


No problem, good to see a result. 

Regards 

David Logan 
Database Administrator 
HP Managed Services 
148 Frome Street, 
Adelaide 5000 
Australia 

+61 8 8408 4273 - Work 
+61 417 268 665 - Mobile 
+61 8 8408 4259 - Fax 


-Original Message- 
From: John Mistler [mailto:[EMAIL PROTECTED] 
Sent: Monday, 12 December 2005 1:30 PM 
To: Logan, David (SST - Adelaide) 
Subject: Re: Import Table? 

Aha, that's it! I didn't think about the fact that you can run
queries 
on tables in multiple databases in that manner. 

The end result is exactly the same. 

You made my day. Thanks! 

-John 

On Dec 11, 2005, at 6:35 PM, Logan, David (SST - Adelaide)
wrote: 


Hi John, 

I would probably create a temporary database, use this,
dump all the 
tables into that and then use only the table that you
want, followed 

by 

a drop database. It would be a bit difficult to strip
out one table 
AFAIK. I can't see anything in the mysql client options.


eg. mysql -u root -p  <./.sql file 

If you are embedding this into the app, is this perl or
similar? You 
can 
either do a create tempdatabasename or use the test
database that 

seems 

to be created by every mysql installation and just do a
mysql -u root 
-p 
test <./.sql file 

Your comparisons can refer to permanent.tablename and
temp.tablename. 
They will be logically and physically separate. eg. you
can JOIN etc. 
so 
long as you have the permissions. 

Sorry I can't be of more help. 

Regards 

David Logan 
Database Administrator 
HP Managed Services 
148 Frome Street, 
Adelaide 5000 
Australia 

+61 8 8408 4273 - Work 
+61 417 268 665 - Mobile 
+61 8 8408 4259 - Fax 


-Original Message- 
From: John Mistler [mailto:[EMAIL PROTECTED] 
Sent: Monday, 12 December 2005 12:46 PM 
To: Logan, David (SST - Adelaide) 
Subject: Re: Import Table? 

David, 

I should probably just give the big picture of what I
need to 
accomplish. Here it is: 

I am on Mac OS X.3. 
The sql file was dumped from the same database at an
earlier date 

using 

mysqldump. 
The database has 6 tables in it, one of which is the
table I want to 
access -- 'theTable'. 

The end goal is to load 'theTable' from the dumped sql
file into a 
temporary table on the server in order to do some
comparisons between 
'theTable' currently in the server and 'theTable' from
the sql file. 

I am embedding these mysql commands into my MAC OS X
application's 
code, so cutting and pasting data is not relevant to my
situation. 

Thanks for sticking with me on this

Re: Import Table?

[John Mistler]

Sorry to bother you once more on this David, but I am having trouble 
figuring out how to run a query on tables in two different databases.  
I normally use


/usr/local/mysql/bin/mysql -h localhost -u username -ppassword -D 
databaseName -N -e SELECT ...


as the initial string of the query.  However, this only references one 
database.  How can I issue a query over two tables, one in each 
database?


Thanks,

John

On Dec 11, 2005, at 7:02 PM, Logan, David (SST - Adelaide) wrote:


No problem, good to see a result.

Regards

David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia

+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax


-Original Message-
From: John Mistler [mailto:[EMAIL PROTECTED]
Sent: Monday, 12 December 2005 1:30 PM
To: Logan, David (SST - Adelaide)
Subject: Re: Import Table?

Aha, that's it!  I didn't think about the fact that you can run queries
on tables in multiple databases in that manner.

The end result is exactly the same.

You made my day.  Thanks!

-John

On Dec 11, 2005, at 6:35 PM, Logan, David (SST - Adelaide) wrote:


Hi John,

I would probably create a temporary database, use this, dump all the
tables into that and then use only the table that you want, followed

by

a drop database. It would be a bit difficult to strip out one table
AFAIK. I can't see anything in the mysql client options.

eg. mysql -u root -p  <./.sql file

If you are embedding this into the app, is this perl or similar? You
can
either do a create tempdatabasename or use the test database that

seems

to be created by every mysql installation and just do a mysql -u root
-p
test <./.sql file

Your comparisons can refer to permanent.tablename and temp.tablename.
They will be logically and physically separate. eg. you can JOIN etc.
so
long as you have the permissions.

Sorry I can't be of more help.

Regards

David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia

+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax


-Original Message-
From: John Mistler [mailto:[EMAIL PROTECTED]
Sent: Monday, 12 December 2005 12:46 PM
To: Logan, David (SST - Adelaide)
Subject: Re: Import Table?

David,

I should probably just give the big picture of what I need to
accomplish.  Here it is:

I am on Mac OS X.3.
The sql file was dumped from the same database at an earlier date

using

mysqldump.
The database has 6 tables in it, one of which is the table I want to
access -- 'theTable'.

The end goal is to load 'theTable' from the dumped sql file into a
temporary table on the server in order to do some comparisons between
'theTable' currently in the server and 'theTable' from the sql file.

I am embedding these mysql commands into my MAC OS X application's
code, so cutting and pasting data is not relevant to my situation.

Thanks for sticking with me on this!

-John

On Dec 11, 2005, at 5:57 PM, Logan, David (SST - Adelaide) wrote:


Hi John,

Personally, I'd just do a cut and paste job on the .sql file unless

it

is too unmanageable.  Not knowing your platform, and being a unixy

type

person, I would use sed or grep to strip out the lines that I need

and

then plonk them straight into another file. I don't know how you

could

accomplish that on a Windows platform.

I hope I haven't misunderstood, is the .sql file come from another
MySQL
database or is this from a SQL server machine or similar? My
interpretation of a .sql file is something akin to that created by
mysqldump eg. a text file that has a number of SQL statements in it
allowing you to recreate the table by using this as input.

You can also use

Regards

David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia

+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax


-Original Message-
From: John Mistler [mailto:[EMAIL PROTECTED]
Sent: Monday, 12 December 2005 12:14 PM
To: mysql@lists.mysql.com; Logan, David (SST - Adelaide)
Subject: Re: Import Table?

Thanks for the response, David.  How about if I want to import all

the

entries from one specific TABLE within that sql file into a table

with

identical columns on my MySQL server?  Is there a way?

Thanks,

John

On Dec 11, 2005, at 5:29 PM, Logan, David (SST - Adelaide) wrote:


Hi John,

If it is a ".sql" file, with all appropriate SQL statements already

in

place then you only have to do the following

$ mysql -u  -p databasename <.sql file

This will process all appropriate statements in the file.

Regards

David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia

+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax


-Original Message-
From: John Mistler [mailto:[EMAIL PROTECTED]
Sent: Monday, 12 December 2005 11:55 AM
To: mysql@lists.mysql.com
Subject: Import Table?

Is there a command that will load in all of the data from a table
within 

Hijackers?

[Peter Lauri]

Best group member,

 

How can I prevent people from hijacking a query? I read this in an article
about a few months ago, but now I can not find that article again. This
question is maybe not so exact, and I do not know how risky it is to not
protect your system from database hijackers?

 

Can someone give an example of how a database can be hijacked or destroyed?
What kind of queries is more vulnerable then others?

 

Best regards,

Peter Lauri

 

 

 

Re: Where is the perl DBI for version 5 mysql

[Gleb Paharenko]

Hello.



Follow instructions from:

  http://dev.mysql.com/doc/refman/5.0/en/perl-support.html







Logg, Connie A. wrote:

> I have done various searches, and cannot locate the perl dbi for mysql =

> 5.0.16.

> 

> I assume that the old one for mysql 4 will not work.

> 

> Can someone please provide me the url for mysql 5's perl dbd/dbi bundle?

> 

> Thanks, Connie

> Connie Logg, Network Analyst

> Stanford Linear Accelerator Center

> ph: 650-926-2879=20

> "Happiness is found along the way, not at the end of the road, and 'IF' =

> is the middle word in life."

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [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: Select Unique?

[John Mistler]

Michael,

Thanks so much for the query.  As I am surmising from your email, the 
LEFT JOIN is the better way to go for performance.  If you have any 
reason to think I should go with the subquery, let me know!


Thanks again,

John

On Dec 12, 2005, at 9:57 AM, Michael Stassen wrote:


Rhino wrote:
- Original Message - From: "John Mistler" 
<[EMAIL PROTECTED]>

To: 
Sent: Monday, December 12, 2005 12:34 AM
Subject: Select Unique?
I have two tables 'table1', 'table2' with a matching column 
'column1'.  How can I return all rows from table2 where the entry 
for table2.column1 does not match any entries in table1.column1?


SELECT * FROM table2 WHERE table2.column1 <> table1.column1

returns all the rows, rather than the unique rows in table2 ... Any 
ideas?



SELECT *
FROM table2
where table2.column1 not in
(select distinct column1
from table1
where column1 not null)


That should be "where column1 IS NOT NULL)".

The 'distinct' in the subquery is not strictly necessary but should 
help performance. The WHERE clause in the subquery is often omitted 
but really shouldn't be.

Rhino


If you're interested in performance, you probably shouldn't use a 
subquery.  If you put EXPLAIN in front, you'll see that mysql labels 
this a "DEPENDENT SUBQUERY", meaning it will rerun the subquery for 
each row in the outer query. The optimizer *should* be smart enough to 
run the inner query once, then compare rows to that list using the 
index, but it isn't.  As a test, I made a 25 row table and a copy 
missing 3 of those rows.  The subquery version took twice as long to 
execute (.12 sec) as the left join version (.06 sec).  The larger the 
tables involved, the larger the difference is likely to be.


Michael

(Test run on my iBook G4, OS X 10.3.9, mysql 4.1.15.)




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

RE: LOAD DATA INFILE Syntax

[Logan, David (SST - Adelaide)]

Hi Elliot,

It is in the docs, just a little bit further down the page 8-)

LOCAL works only if your server and your client both have been enabled
to allow it. For example, if mysqld was started with --local-infile=0,
then LOCAL  does not work. See Section 5.6.4, "Security Issues with LOAD
DATA LOCAL". 

Regards

David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia

+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax


-Original Message-
From: Elliot Kleiman [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, 13 December 2005 8:12 AM
To: mysql@lists.mysql.com
Subject: LOAD DATA INFILE Syntax

Hi mysql-list,

I just installed,

++
| version()  |
++
| 5.0.16-log |
++

Here is what I am testing out: (simple table and data)

% echo 'a b c d' > testfile

mysql> CREATE TABLE `test` (
-> `fe` VARCHAR( 2 ),
-> `fi` VARCHAR( 2 ),
-> `fo` VARCHAR( 2 ),
-> `fum` VARCHAR( 2 )
-> );
Query OK, 0 rows affected (0.00 sec)

mysql> load data local infile './test' into table test;
ERROR 1148 (42000): The used command is not allowed with this MySQL
version

Q: What am I doing wrong(missing)?

  { I did not see it in the documentation online }
  http://dev.mysql.com/doc/refman/5.0/en/load-data.html

Thanks, Elliot

P.S.
The same command works fine in previous versions of MySQL.

__
WizardsWorks Cluster
http://www.wizardsworks.org/




-- 
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: mysqladmin --skip-grant-tables error

[Alfred Vahau]

Michael Stassen wrote:


Alfred Vahau wrote:

According to the Mysql online manual, to reset a root password one 
procedure is to issue the command:

./mysqladmin --skip-grant-tables
to have full access to the database and update the root password as 
per the instructions in the manual.

My problem is when I issue the command
./mysqladmin --skip-grant-tables, I get the error message
mysqladmin: ERROR: unknown option '--grant-tables'

I also tried ./mysqladmin --skip-grant-tables --user=root and this 
didn't help either
I'm using Mysql version 4.0.17-standard which came bundled with my OS 
(Ubuntu Linux Warty)


Pointers much appreciated.
Thanks,

Alfred Vahau
IT Services
University of Papua New Guinea



You appear to have misread the manual.  --skip-grant-tables is not a 
mysqladmin option.  It's a mysqld option.


Just follow the directions in the manual 
.


Michael



Thanks. Problem fixed.

Alfred,


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

LOAD DATA INFILE Syntax

[Elliot Kleiman]

Hi mysql-list,

I just installed,

++
| version()  |
++
| 5.0.16-log |
++

Here is what I am testing out: (simple table and data)

% echo 'a b c d' > testfile

mysql> CREATE TABLE `test` (
-> `fe` VARCHAR( 2 ),
-> `fi` VARCHAR( 2 ),
-> `fo` VARCHAR( 2 ),
-> `fum` VARCHAR( 2 )
-> );
Query OK, 0 rows affected (0.00 sec)

mysql> load data local infile './test' into table test;
ERROR 1148 (42000): The used command is not allowed with this MySQL
version

Q: What am I doing wrong(missing)?

  { I did not see it in the documentation online }
  http://dev.mysql.com/doc/refman/5.0/en/load-data.html

Thanks, Elliot

P.S.
The same command works fine in previous versions of MySQL.

__
WizardsWorks Cluster
http://www.wizardsworks.org/




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

Re: mysqladmin --skip-grant-tables error

[Michael Stassen]

Alfred Vahau wrote:
According to the Mysql online manual, to reset a root password one 
procedure is to issue the command:

./mysqladmin --skip-grant-tables
to have full access to the database and update the root password as per 
the instructions in the manual.

My problem is when I issue the command
./mysqladmin --skip-grant-tables, I get the error message
mysqladmin: ERROR: unknown option '--grant-tables'

I also tried ./mysqladmin --skip-grant-tables --user=root and this 
didn't help either
I'm using Mysql version 4.0.17-standard which came bundled with my OS 
(Ubuntu Linux Warty)


Pointers much appreciated.
Thanks,

Alfred Vahau
IT Services
University of Papua New Guinea


You appear to have misread the manual.  --skip-grant-tables is not a mysqladmin 
option.  It's a mysqld option.


Just follow the directions in the manual 
.


Michael


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

Re: [OT-ish] Hardware for MySQL server

[Sid Lane]

does it absolutely HAVE to be 1u?

if you can go 2u we've been really happy w/HP DL385s lately.  2u form (which
is still pretty small for a DB server), redundant power supplies (a good
thing for DB server), six drive bays (so you can RAID5 or three mirror
pairs), remote management card and Opteron gives you a 64 bit path w/o
sucking in 32 mode.

I have eleven of them (so far) and don't think anything can touch them on a
bang/$ basis...

mysqladmin --skip-grant-tables error

[Alfred Vahau]

According to the Mysql online manual, to reset a root password one 
procedure is to issue the command:

./mysqladmin --skip-grant-tables
to have full access to the database and update the root password as per 
the instructions in the manual.

My problem is when I issue the command
./mysqladmin --skip-grant-tables, I get the error message
mysqladmin: ERROR: unknown option '--grant-tables'

I also tried ./mysqladmin --skip-grant-tables --user=root and this 
didn't help either
I'm using Mysql version 4.0.17-standard which came bundled with my OS 
(Ubuntu Linux Warty)


Pointers much appreciated.
Thanks,

Alfred Vahau
IT Services
University of Papua New Guinea




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

Re: PHP4 or PHP5?

[Atle Veka]

Just be glad you're not stuck supporting a product that was designed about
6 years ago. "Hey, let's have each account get its own table"

[db1:~] find -L /usr/local/mysql -name \*.frm | wc -l
8116

The programmer that designed that system is now synonymous with bad
design..


Atle
-
Flying Crocodile Inc, Unix Systems Administrator

On Mon, 12 Dec 2005, Peter M. Groen wrote:

> On Sunday 11 December 2005 23:51, Charles Walmsley wrote:
> > Dear All,
> >
> >
> >
> > I do not have much experience with PHP or MySql although I have used SQL
> > quite a lot.  I am going to set up a relatively small MySQL database (circa
> > 40 tables) and we are expecting a hit rate of about 40,000 visitors per
> > annum mostly browsing a relatively low number of pages each.  We plan to
> > launch in March
> >
> >[ 8< ]--
>
> Ehm.. To be blunt... 40 TABLES??? You call that small? What on earth are you
> going to store.
>
> Fester
>
>

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

Where is the perl DBI for version 5 mysql

[Logg, Connie A.]

I have done various searches, and cannot locate the perl dbi for mysql 5.0.16.

I assume that the old one for mysql 4 will not work.

Can someone please provide me the url for mysql 5's perl dbd/dbi bundle?

Thanks, Connie
Connie Logg, Network Analyst
Stanford Linear Accelerator Center
ph: 650-926-2879 
"Happiness is found along the way, not at the end of the road, and 'IF' is the 
middle word in life."

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

Re: technical lingo for search feature

[SGreen]

"leegold" <[EMAIL PROTECTED]> wrote on 12/12/2005 03:18:30 PM:

> Is there a tech term for this? User searches for "reviews" but the
> actual word is "review". So the search engine code searchs on bolth
> words and user gets the hit they want...
> 
> What's this called? Is there a MYSQL implementation of this?
> 
> Thanks
> 

That is a form of "stemming". No, MySQL does not implement stemmed 
searches. It is up to your application to first stem the search word and 
build a query appropriate to it. In this case you want to remove the 
trailing "s" to reveal the stem "review". Next you want to do a wildcard 
search for "review%"  which will return review, reviewing, reviewed, 
reviewer, etc.  If a full wildcard search or a more specific type of 
stemmed search is your need (like looking only for noun or verb forms 
based on grammar) that would be a more complex type of search. 

I am sure if you search the web, you will find lots of different 
algorithms for stemming. 

MySQL supports both full-text and standard indexes. Both of which can be 
used for wildcard searches (as long as the wildcard character is at the 
END of the string).

Did that help?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: NULL, OR, and indexes

[Eamon Daly]

Yep, I forgot to mention that I'm using MySQL 4.1, which
does support ref_or_null, but only for the first column.


Eamon Daly



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

To: "Eamon Daly" <[EMAIL PROTECTED]>
Cc: 
Sent: Monday, December 12, 2005 2:45 PM
Subject: Re: NULL, OR, and indexes



In the last episode (Dec 12), Dan Nelson said:

In the last episode (Dec 12), Eamon Daly said:
> I'm sure the answer is "You're SOL", but I figured I'd ask anyway. I
> have a WHERE condition like:
>
> SELECT * FROM a JOIN b WHERE
> (b1 IS NULL OR b1 = u1) AND
> (b2 IS NULL OR b2 = u2) AND
> (b3 IS NULL OR b3 = u3)
>
> where b is a Very Large table. I have an index on b like (b1, b2,
> b3), but obviously that gets thrown out because of the OR. Is there a
> Better Way to either rewrite the WHERE condition or break out b to
> allow indexes to be used?

What's the explain plain look like, and how many rows does your query
return on average?  The OR shouldn't prevent an index lookup, at least
for the simple one-column index case.  It can use a ref_or_null lookup.
What happens if you force the use of your composite index with a hint?


And to answer my own question:
http://dev.mysql.com/doc/refman/5.0/en/is-null-optimization.html
explains that ref_is_null only works on the first part of a compound
index.

--
Dan Nelson
[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: NULL, OR, and indexes

[Dan Nelson]

In the last episode (Dec 12), Dan Nelson said:
> In the last episode (Dec 12), Eamon Daly said:
> > I'm sure the answer is "You're SOL", but I figured I'd ask anyway. I
> > have a WHERE condition like:
> > 
> > SELECT * FROM a JOIN b WHERE
> > (b1 IS NULL OR b1 = u1) AND
> > (b2 IS NULL OR b2 = u2) AND
> > (b3 IS NULL OR b3 = u3)
> > 
> > where b is a Very Large table. I have an index on b like (b1, b2,
> > b3), but obviously that gets thrown out because of the OR. Is there a
> > Better Way to either rewrite the WHERE condition or break out b to
> > allow indexes to be used?
> 
> What's the explain plain look like, and how many rows does your query
> return on average?  The OR shouldn't prevent an index lookup, at least
> for the simple one-column index case.  It can use a ref_or_null lookup. 
> What happens if you force the use of your composite index with a hint? 

And to answer my own question:
http://dev.mysql.com/doc/refman/5.0/en/is-null-optimization.html
explains that ref_is_null only works on the first part of a compound
index.

-- 
Dan Nelson
[EMAIL PROTECTED]

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

Re: NULL, OR, and indexes

[Dan Nelson]

In the last episode (Dec 12), Eamon Daly said:
> I'm sure the answer is "You're SOL", but I figured I'd ask anyway. I
> have a WHERE condition like:
> 
> SELECT * FROM a JOIN b WHERE
> (b1 IS NULL OR b1 = u1) AND
> (b2 IS NULL OR b2 = u2) AND
> (b3 IS NULL OR b3 = u3)
> 
> where b is a Very Large table. I have an index on b like (b1, b2,
> b3), but obviously that gets thrown out because of the OR. Is there a
> Better Way to either rewrite the WHERE condition or break out b to
> allow indexes to be used?

What's the explain plain look like, and how many rows does your query
return on average?  The OR shouldn't prevent an index lookup, at least
for the simple one-column index case.  It can use a ref_or_null lookup. 
What happens if you force the use of your composite index with a hint? 

If you are using MySQL 5.0, try creating three separate indexes, one
for each column, and see if index merge optimization does any good. 
Yyou may have to force it to use those three indexes using a hint; the
optimizer isn't very smart about merges yet.

-- 
Dan Nelson
[EMAIL PROTECTED]

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

Re: install trouble, perl DBI

[Gleb Paharenko]

Hello.



> rpm -ivh perl-DBI-1.40-5.src.rpm



This rpm installs only the source of perl-DBI, install a binary rpm for

your architecture. You might need to use --force option for rpm command

in case you have a fresher working version of perl-DBI.







Lewis Ashley Foster wrote:

> 

> I'm trying to get mySQL installed on my machine but im having a bit of

> trouble, obviously :)

> 

> When i come to install mySQL server as below:

> 

> rpm -ivh MySQL-server-standard-5.0.16-0.rhel3.i386.rpm

> 

> I get this result:

> 

> warning: MySQL-server-standard-5.0.16-0.rhel3.i386.rpm: V3 DSA

> signature: NOKEY, key ID 5072e1f5

> error: Failed dependencies:

> perl(DBI) is needed by MySQL-server-standard-5.0.16-0.rhel3

> Suggested resolutions:

> perl-DBI-1.32-5.i386.rpm

> 

> But I have already installed perl dbi 1.40 like this with no errors:

> 

> rpm -ivh perl-DBI-1.40-5.src.rpm

> 

> I really cant think how to get round this, unless i downgrade the dbi to

> 1.32 but that seems daft.

> 

> Any ideas would be greatly appreciated.

> 

> Lewis.

> 

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [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: ~mysql query log~

[Gleb Paharenko]

Hello.



Have a look here:

  http://dev.mysql.com/doc/refman/5.0/en/log-file-maintenance.html





abdulazeem wrote:

> Hi,

> 

> Iam running a mysql server version 5.0.15. My mysql query log is

> occupying nearly 21 GB of disk space. how do i truncate the same ?

> 

> Thanks in advance,

> Abdul. 

> 

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [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: backwards compatibility when exporting M

[Gleb Paharenko]

Hello.



Add --hex-blob to mysqldump command line options.





Adam Lipscombe wrote:

> Folks

> 

> 

> I am trialling MySQL 5 before we all upgrade, however others need to =

> import=20

> my dump files into their existing MySQL4.x databases

> 

> MySQL 5 mysqldump seems exports BIT fields as true =3D '=01', false =3D =

> '\0'.

> 

> When  that dump file is imported into MySQL 4 these value are not=20

> interpreted correctly. MySQL4.x does not have a real BIT type , rather =

> it=20

> uses TINYINT(1).

> Both  '=01' and '\0' seem to be interpreted as "false", and the relevant =

> 

> TINYINT(1) field is set to 0.

> 

> So far that has meant that I have to hand-edit my dump file to convert  =

> '=01'=20

> to 1,  '\0' to 0.

> 

> Does anyone know a way around this?

> 

> 

> 

> TIA - Adam Lipscombe

> 

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [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: MySQL hangs each relative poor time

[Gleb Paharenko]

Hello.



What system load do 'top' and 'free' show? Use 'SHOW PROCESSLIST' to

determine what queries are running on the server if you're able to

connect to it. Check that mysqld isn't swapping.







Roberto Rodriguez Garrido wrote:

> MySQL hangs every 30 minutes, it start to make a big process and when I make 
> a 

> show variables I get that values:

> 

> +-+-+

> | Variable_name   | Value   |

> +-+-+

> | back_log| 50  |

> | basedir | /usr/   |

> | binlog_cache_size   | 32768   |

> | bulk_insert_buffer_size | 8388608 |

> | character_set_client| latin1  |

> | character_set_connection| latin1  |

> | character_set_database  | latin1  |

> | character_set_results   | latin1  |

> | character_set_server| latin1  |

> | character_set_system| utf8|

> | character_sets_dir  | /usr/share/mysql/charsets/  |

> | collation_connection| latin1_swedish_ci   |

> | collation_database  | latin1_swedish_ci   |

> | collation_server| latin1_swedish_ci   |

> | concurrent_insert   | ON  |

> | connect_timeout | 10  |

> | datadir | /var/lib/mysql/ |

> | date_format | %Y-%m-%d|

> | datetime_format | %Y-%m-%d %H:%i:%s   |

> | default_week_format | 0   |

> | delay_key_write | ON  |

> | delayed_insert_limit| 100 |

> | delayed_insert_timeout  | 300 |

> | delayed_queue_size  | 1000|

> | expire_logs_days| 0   |

> | flush   | OFF |

> | flush_time  | 0   |

> | ft_boolean_syntax   | + -><()~*:""&|  |

> | ft_max_word_len | 84  |

> | ft_min_word_len | 4   |

> | ft_query_expansion_limit| 20  |

> | ft_stopword_file| (built-in)  |

> | group_concat_max_len| 1024|

> | have_archive| YES |

> | have_bdb| NO  |

> | have_compress   | YES |

> | have_crypt  | YES |

> | have_csv| YES |

> | have_example_engine | NO  |

> | have_geometry   | YES |

> | have_innodb | YES |

> | have_isam   | YES |

> | have_ndbcluster | DISABLED|

> | have_openssl| NO  |

> | have_query_cache| YES |

> | have_raid   | YES |

> | have_rtree_keys | YES |

> | have_symlink| YES |

> | init_connect| |

> | init_file   | |

> | init_slave  | |

> | innodb_additional_mem_pool_size | 1048576 |

> | innodb_autoextend_increment | 8   |

> | innodb_buffer_pool_awe_mem_mb   | 0   |

> | innodb_buffer_pool_size | 8388608 |

> | innodb_data_file_path   | ibdata1:10M:autoextend  |

> | innodb_data_home_dir| |

> | innodb_fast_shutdown| ON  |

> | innodb_file_io_threads  | 4   |

> | innodb_file_per_table   | OFF |

> | innodb_flush_log_at_trx_commit  | 1   |

> | innodb_flush_method | |

> | innodb_force_recovery   | 0   |

> | innodb_lock_wait_timeout| 50  |

> | innodb_

Re: neet setup tips for remote mysql access

[Gleb Paharenko]

Hello.



>ERROR 2003 (HY000): Can't connect to MySQL server on 'myDomain.com' (10061)



See:

  http://dev.mysql.com/doc/refman/5.0/en/can-not-connect-to-server.html





"YL" <[EMAIL PROTECTED]> wrote:

>I have mysql server and php apps on the same machine, they worked very well.

>The machine's OS is win2k pro

>Mysql 5.0.15

>

>Then I access the db from local network area 192.l68 And it worked fine 
>although I

>have to reset my account privileges..

>

>Then I tried to access it remotely by domain name, it stop working even I can 
>ping the

>server with the domain name: 

>

>F:\home\elim>mysql -h myDomain.com -u elim -p

>Enter password: **

>ERROR 2003 (HY000): Can't connect to MySQL server on 'myDomain.com' (10061)

>

>Please help me for the general steps of setting this up. Thanks a lot!



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [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]

NULL, OR, and indexes

[Eamon Daly]

I'm sure the answer is "You're SOL", but I figured I'd ask
anyway. I have a WHERE condition like:

SELECT *
FROM a
JOIN b
WHERE
(b1 IS NULL OR b1 = u1) AND
(b2 IS NULL OR b2 = u2) AND
(b3 IS NULL OR b3 = u3)

where b is a Very Large table. I have an index on b like
(b1, b2, b3), but obviously that gets thrown out because of
the OR. Is there a Better Way to either rewrite the WHERE
condition or break out b to allow indexes to be used?


Eamon Daly


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

technical lingo for search feature

[leegold]

Is there a tech term for this? User searches for "reviews" but the
actual word is "review". So the search engine code searchs on bolth
words and user gets the hit they want...

What's this called? Is there a MYSQL implementation of this?

Thanks

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

Re: neet setup tips for remote mysql access

[Xiaobo Chen]

Could it be the firewall? You might want to open the port 3306 for
external access(or both). In my XP, I did this:

Firewall --> Advance--> Network Connection Settings -->Select Local Area
Connection --> Click Setting, then you might want to 'add...', from there
filling your IP, 3306 for both ports.

Hope this will work for you.

Good luck.

X.Chen

> Dear List,
>
> I have mysql server and php apps on the same machine, they worked very
> well.
> The machine's OS is win2k pro
> Mysql 5.0.15
>
> Then I access the db from local network area 192.l68 And it worked
> fine although I
> have to reset my account privileges..
>
> Then I tried to access it remotely by domain name, it stop working even I
> can ping the
> server with the domain name:
>
> F:\home\elim>mysql -h myDomain.com -u elim -p
> Enter password: **
> ERROR 2003 (HY000): Can't connect to MySQL server on 'myDomain.com'
> (10061)
>
> Please help me for the general steps of setting this up. Thanks a lot!
>
>
>



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

neet setup tips for remote mysql access

[YL]

Dear List,

I have mysql server and php apps on the same machine, they worked very well.
The machine's OS is win2k pro
Mysql 5.0.15

Then I access the db from local network area 192.l68 And it worked fine 
although I
have to reset my account privileges..

Then I tried to access it remotely by domain name, it stop working even I can 
ping the
server with the domain name: 

F:\home\elim>mysql -h myDomain.com -u elim -p
Enter password: **
ERROR 2003 (HY000): Can't connect to MySQL server on 'myDomain.com' (10061)

Please help me for the general steps of setting this up. Thanks a lot!

Re: using a function to define default col value?

[SGreen]

Vince LaMonica <[EMAIL PROTECTED]> wrote on 12/12/2005 01:49:54 PM:

> Hi all,
> 
> I'm curious if this is possible in 4.10: I have a table:
> 
> 
+--+-+--+-+---+--+
> | Field| Type| Null | Key | Default   | Extra |
> 
+--+-+--+-+---+--+
> | tracking_id  | int(12) |  | PRI | NULL  | 
auto_increment 
> | ups_tracking | varchar(64) |  | |   | |
> | order_number | varchar(64) |  | |   | |
> | time_added   | timestamp   | YES  | | CURRENT_TIMESTAMP | |
> | aba_order_number | varchar(96) |  | |   | |
> 
+--+-+--+-+---+--+
> 
> A sample value for the order_number field looks like this:
> 
> ABA-123456
> 
> I would like to make the aba_order_number field reflect just what comes 
> after the "ABA-" part. So, is there a way I can assign a default value 
to 
> aba_order_number to this:
> 
> IF(LEFT(order_number,4) = 'ABA-',REPLACE(order_number, 'ABA-',''),'')
> 
> Currently this table is populated via ODBC. Only the ups_tracking and 
> order_number fields are populated through UPS' WorldShip software [it 
> simply performs an export of those two fields each time a new tracking 
> number is generated].
> 
> I can run this:
> 
> update example_table set aba_order_number=IF(LEFT(order_number,4) = 
> 'ABA-',REPLACE(order_number, 'ABA-', ''),'') ;
> 
> and I get the result I am looking for.
> 
> But running this:
> 
> alter table example_table alter column aba_order_number set
> default IF(LEFT(order_number,4) = 'ABA-',REPLACE(order_number, 'ABA-',
> ''),'');
> 
> doesn't seem to make a difference, as it assigns the string "IF(LEFT..." 

> as the default text, instead of seeing it as a function. The reason I am 

> using an IF() is because some order_number values will not contain a 
> leading "ABA-" string.
> 
> I'm using 4.1.13, FWIW.
> 
> Thanks for any tips y'all might have.
> 
> /vjl/
> 

First - THANK YOU for remembering to post your version!

Quoting from http://dev.mysql.com/doc/refman/4.1/en/create-table.html
>>>
 The DEFAULT clause specifies a default value for a column. With one 
exception, the default value must be a constant; it cannot be a function 
or an expression. This means, for example, that you cannot set the default 
for a date column to be the value of a function such as NOW() or 
CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as 
the default for a TIMESTAMP column as of MySQL 4.1.2. See Section 
11.3.1.2, “TIMESTAMP Properties as of MySQL 4.1”.


No functions are allowed in a DEFAULT declarations, only constants. The 
single, almost-but-not-quite exception to this rule is for timestamp 
fields but that still won't help you do what you wanted to do. I do not 
believe that full functions are available even in 5.0 yet so an upgrade 
won't help either.

Sorry!

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

using a function to define default col value?

[Vince LaMonica]

Hi all,

I'm curious if this is possible in 4.10: I have a table:

+--+-+--+-+---+--+
| Field| Type| Null | Key | Default   | Extra|
+--+-+--+-+---+--+
| tracking_id  | int(12) |  | PRI | NULL  | auto_increment 
| ups_tracking | varchar(64) |  | |   |  |

| order_number | varchar(64) |  | |   |  |
| time_added   | timestamp   | YES  | | CURRENT_TIMESTAMP |  |
| aba_order_number | varchar(96) |  | |   |  |
+--+-+--+-+---+--+

A sample value for the order_number field looks like this:

ABA-123456

I would like to make the aba_order_number field reflect just what comes 
after the "ABA-" part. So, is there a way I can assign a default value to 
aba_order_number to this:


IF(LEFT(order_number,4) = 'ABA-',REPLACE(order_number, 'ABA-',''),'')

Currently this table is populated via ODBC. Only the ups_tracking and 
order_number fields are populated through UPS' WorldShip software [it 
simply performs an export of those two fields each time a new tracking 
number is generated].


I can run this:

update example_table set aba_order_number=IF(LEFT(order_number,4) = 
'ABA-',REPLACE(order_number, 'ABA-', ''),'') ;


and I get the result I am looking for.

But running this:

alter table example_table alter column aba_order_number set
default IF(LEFT(order_number,4) = 'ABA-',REPLACE(order_number, 'ABA-',
''),'');

doesn't seem to make a difference, as it assigns the string "IF(LEFT..." 
as the default text, instead of seeing it as a function. The reason I am 
using an IF() is because some order_number values will not contain a 
leading "ABA-" string.


I'm using 4.1.13, FWIW.

Thanks for any tips y'all might have.

/vjl/

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

Re: install trouble, perl DBI

[Pat Adams]

On Mon, 2005-12-12 at 15:20 +, Lewis Ashley Foster wrote:
> 
> warning: MySQL-server-standard-5.0.16-0.rhel3.i386.rpm: V3 DSA signature: 
> NOKEY, key ID 5072e1f5
> error: Failed dependencies:
>  perl(DBI) is needed by MySQL-server-standard-5.0.16-0.rhel3
>  Suggested resolutions:
>  perl-DBI-1.32-5.i386.rpm
> 
> But I have already installed perl dbi 1.40 like this with no errors:
> 
> rpm -ivh perl-DBI-1.40-5.src.rpm

You're installing the source RPM, which you would then have to build and
install yourself. Try installing the perl-DBI RPM without src in the
filename.
-- 
Pat Adams
Digital Darkness Promotions
Check out the Dallas Music Wiki http://digitaldarkness.com/tiki

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

Re: Merge/Combine two server

[Cal Evans]

Notes in-line

=C=
www.calevans.com

On Mon, 2005-12-12 at 18:33 +0100, Juan Jose Sanchez Mesa wrote:
> Hi!
> 
> We have 2 MySQL servers and now we have purchased one more powerfull server 
> and want to combine the databases from the two older server into the new 
> server.
Yes, you can. I would shut both servers down, mysqldump them, import the
dumps into the new server point the applications to the new server,
bring the new server back on-line.

> 
> Can we just make dumps from older server and make restores into new servers ? 
Yes. Although unless you know for sure that there are no duplicate
databases, i would do it one database at a time instead of the entire
server.

> What happen with databases users 
If you dump and then restore the mysql database, it will restore the
users. However, you probably need to audit the user accounts first to
make sure that there are no duplicates between the servers. then just
create the inserte statements for the accounts you want.



> Any way to do it automaticaly ? How about server-to-server dump/restore with 
> no use of .sql file ?
You probably could but I'd not do this if I were you unless you know for
sure that there are no duplicate databases. 




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

Re: MySQL hangs each relative poor time

[Danny Stolle]

Well Roberto,

it could be anything... analyzing your my.cnf file and variables would 
be looking through spagetti (i like spagetti ... but not in this flavour).


the time your database hangs, are the intervals random 
minutes/seconds/hours? or is there a constant interval like precise on 
30 minutes the mysql database hangs.


perhaps a trial and error method should do the trick? it could be your 
disk quota? perhaps a process which kills the database? perhaps first 
run the database using defaults. keep the my.cnf file as small as you can.


build a new mysql-version; and run this one, see if it reacts the same 
as your database.


can you tell me the mysql>\s values?

i am just giving you some hints, to analyse the problem using trial and 
error. i am sorry i can't give you the full solluton; because too many 
factors can cause the error.


Danny



Roberto Rodriguez Garrido wrote:
MySQL hangs every 30 minutes, it start to make a big process and when I make a 
show variables I get that values:


+-+-+
| Variable_name   | Value   |
+-+-+
| back_log| 50  |
| basedir | /usr/   |
| binlog_cache_size   | 32768   |
| bulk_insert_buffer_size | 8388608 |
| character_set_client| latin1  |
| character_set_connection| latin1  |
| character_set_database  | latin1  |
| character_set_results   | latin1  |
| character_set_server| latin1  |
| character_set_system| utf8|
| character_sets_dir  | /usr/share/mysql/charsets/  |
| collation_connection| latin1_swedish_ci   |
| collation_database  | latin1_swedish_ci   |
| collation_server| latin1_swedish_ci   |
| concurrent_insert   | ON  |
| connect_timeout | 10  |
| datadir | /var/lib/mysql/ |
| date_format | %Y-%m-%d|
| datetime_format | %Y-%m-%d %H:%i:%s   |
| default_week_format | 0   |
| delay_key_write | ON  |
| delayed_insert_limit| 100 |
| delayed_insert_timeout  | 300 |
| delayed_queue_size  | 1000|
| expire_logs_days| 0   |
| flush   | OFF |
| flush_time  | 0   |
| ft_boolean_syntax   | + -><()~*:""&|  |
| ft_max_word_len | 84  |
| ft_min_word_len | 4   |
| ft_query_expansion_limit| 20  |
| ft_stopword_file| (built-in)  |
| group_concat_max_len| 1024|
| have_archive| YES |
| have_bdb| NO  |
| have_compress   | YES |
| have_crypt  | YES |
| have_csv| YES |
| have_example_engine | NO  |
| have_geometry   | YES |
| have_innodb | YES |
| have_isam   | YES |
| have_ndbcluster | DISABLED|
| have_openssl| NO  |
| have_query_cache| YES |
| have_raid   | YES |
| have_rtree_keys | YES |
| have_symlink| YES |
| init_connect| |
| init_file   | |
| init_slave  | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8   |
| innodb_buffer_pool_awe_mem_mb   | 0   |
| innodb_buffer_pool_size | 8388608 |
| innodb_data_file_path   | ibdata1:10M:autoextend  |
| innodb_data_home_dir| |
| innodb_fast_shutdown 

Re: [OT-ish] Hardware for MySQL server

[Gary Richardson]

It doesn't seem like a mission critical app, but I would seriously
consider using redundant RAID (ie, not 0, but 1 or 5). Nothing ruins
your day quite like losing a non-redundant drive, even if you have
good backups.

Also, what sort of workload are you looking at? How responsive does it
need to be? Is your data well indexed? What table types are you using?
What is the total size of the data?

Spindles do play a factor, but for 6GB of data you're not looking at
orders of magnitudes of performance. Smart indexing will make the
biggest difference, then RAM.

It all really depends -- if you queries are run once or twice a day to
generate reports, it is probably okay for them to take 5 or 10
minutes. If you run the questions repeatedly and need under 10 seconds
for a response you'll have different requirements.

You should take a look at Supermicro chassis
(http://www.supermicro.com). They have various 1U systems that take 4
drives. I'm sure there is a UK distributor.

Thanks.

On 12/12/05, James Harvard <[EMAIL PROTECTED]> wrote:
> [Apologies for my first post here being semi-off-topic!]
>
> I normally deploys apps I develop  (MySQL with Lasso web middleware) with an 
> ISP, so I have no experience of choosing hardware configurations or sourcing 
> them.
>
> My current client's application involves a very large amount of data which I 
> have split into a number of tables. These tables (data files) are currently 
> between several hundred MB and 2 GB each for 6 1/2 years data, and will grow. 
> However, tables are not updated in normal use (we can take the app off-line 
> for updates) - so the data is pretty much read-only.
>
> From my reading of the manual it seems that disc seek speed is the limiting 
> factor once tables get so large that the data and indices cannot be cached in 
> RAM. So I believe that the best hardware setup for a dedicated MySQL server 
> would include two fast discs striped (RAID 0) for the databases and a third 
> separate disc for the operating system.
>
> Does this sound right? (Also thoughts on SCSI versus SATA?)
>
> Second question:
>
> The chap who will probably administer the servers seems to prefer buying 
> Dell, but AFAIK Dell don't do any 1U servers that would support 3 drives. Can 
> anyone recommend any server brands available in the UK, or UK based companies 
> that will build servers, supporting 3 discs (2 RAID & 1 for the OS)?
>
> Many thanks,
> James Harvard
>
> --
> 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: Select Unique?

[Michael Stassen]

Rhino wrote:


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

To: 
Sent: Monday, December 12, 2005 12:34 AM
Subject: Select Unique?


I have two tables 'table1', 'table2' with a matching column 
'column1'.  How can I return all rows from table2 where the entry for 
table2.column1 does not match any entries in table1.column1?


SELECT * FROM table2 WHERE table2.column1 <> table1.column1

returns all the rows, rather than the unique rows in table2 ... Any 
ideas?



SELECT *
FROM table2
where table2.column1 not in
(select distinct column1
from table1
where column1 not null)


That should be "where column1 IS NOT NULL)".

The 'distinct' in the subquery is not strictly necessary but should help 
performance. The WHERE clause in the subquery is often omitted but 
really shouldn't be.


Rhino


If you're interested in performance, you probably shouldn't use a subquery.  If 
you put EXPLAIN in front, you'll see that mysql labels this a "DEPENDENT 
SUBQUERY", meaning it will rerun the subquery for each row in the outer query. 
The optimizer *should* be smart enough to run the inner query once, then compare 
rows to that list using the index, but it isn't.  As a test, I made a 25 row 
table and a copy missing 3 of those rows.  The subquery version took twice as 
long to execute (.12 sec) as the left join version (.06 sec).  The larger the 
tables involved, the larger the difference is likely to be.


Michael

(Test run on my iBook G4, OS X 10.3.9, mysql 4.1.15.)

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

Re: Joins on tables with funky data?

[Subscriptions]

Oops, sorry, I was up too late writing that.  I knew if I quickly typed in a 
test query I would mess it up somewhere.  sheesh.  Here's the actual query:


SELECT ag_admin_cart.*, ag_paymethod.paymethod,
  ag_paymethod.miva_module_id,
  Merchant2_0001_customer_dbf.sql_bill_fname,
  Merchant2_0001_customer_dbf.sql_bill_lname,
  Merchant2_0001_customer_dbf.sql_bill_comp,
  Merchant2_0001_customer_dbf.sql_bill_addr,
  Merchant2_0001_customer_dbf.sql_bill_city,
  Merchant2_0001_customer_dbf.sql_bill_state,
  Merchant2_0001_customer_dbf.sql_bill_zip,
  Merchant2_0001_customer_dbf.sql_bill_cntry,
  Merchant2_0001_customer_dbf.sql_bill_email,
  Merchant2_0001_customer_dbf.sql_bill_phone,
 Merchant2_0001_customer_dbf.sql_ship_fname,
  Merchant2_0001_customer_dbf.sql_ship_lname,
  Merchant2_0001_customer_dbf.sql_ship_comp,
  Merchant2_0001_customer_dbf.sql_ship_addr,
  Merchant2_0001_customer_dbf.sql_ship_city,
  Merchant2_0001_customer_dbf.sql_ship_state,
  Merchant2_0001_customer_dbf.sql_ship_zip,
  Merchant2_0001_customer_dbf.sql_ship_cntry,
  Merchant2_0001_customer_dbf.sql_ship_email,
  Merchant2_0001_customer_dbf.sql_ship_phone,
  Merchant2_0001_customer_dbf.sql_id,
  Merchant2_0001_customer_dbf.sql_recno,
  ag_customer_extras.alternate_phone,
  ag_customer_extras.address_line2,
  ag_customer_extras.ship_address_line2,
  ag_customer_extras.dietary_concerns
 FROM ag_admin_cart
 LEFT JOIN Merchant2_0001_customer_dbf
  ON ag_admin_cart.custid = Merchant2_0001_customer_dbf.sql_recno
 LEFT JOIN ag_customer_extras
  ON ag_customer_extras.sql_cust_recno = 
Merchant2_0001_customer_dbf.sql_recno

 LEFT JOIN ag_paymethod
  ON ag_paymethod.paymethod_id = ag_admin_cart.paymethod_id
 WHERE cartid = ".$_SESSION['cartid'];

Jenifer




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

To: 
Cc: "Subscriptions" <[EMAIL PROTECTED]>
Sent: Monday, December 12, 2005 11:25 AM
Subject: Re: Joins on tables with funky data?



Subscriptions aka Jenifer wrote:

query:  SELECT tblCustomer.*, tblNotes.note FROM tblCustomer LEFT
JOIN tblNotes ON tblCustomer.customerid = tblNotes.noteID WHERE
tblCustomer.customerid = 123


You are joining the customerid from the customer table to the noteID from 
your notes table. You should join with tblNotes.customerID. If most 
customers have a note, or at least all customers with a small customerid 
hva notes, this error would manifest in behaviour like you described, as 
only customers without a note would cause a difference in the integer 
value of the keys noteid and customerid. Simpler put: it would seem to 
work until you inserted a customer without a note, and additional 
customers with notes after that.


--
Roger


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

Merge/Combine two server

[Juan Jose Sanchez Mesa]

Hi!

We have 2 MySQL servers and now we have purchased one more powerfull server and 
want to combine the databases from the two older server into the new server.

Can we just make dumps from older server and make restores into new servers ? 
What happen with databases users ? Can be dumped the database users from the 
two older server and restored into the new server and work together ? 
Obviously, no usernames are duplicated in the old servers.

Any way to do it automaticaly ? How about server-to-server dump/restore with no 
use of .sql file ?

Thanks!


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

Re: Joins on tables with funky data?

[Roger Baklund]

Subscriptions aka Jenifer wrote:

query:  SELECT tblCustomer.*, tblNotes.note FROM tblCustomer LEFT
JOIN tblNotes ON tblCustomer.customerid = tblNotes.noteID WHERE
tblCustomer.customerid = 123


You are joining the customerid from the customer table to the noteID 
from your notes table. You should join with tblNotes.customerID. If most 
customers have a note, or at least all customers with a small customerid 
hva notes, this error would manifest in behaviour like you described, as 
only customers without a note would cause a difference in the integer 
value of the keys noteid and customerid. Simpler put: it would seem to 
work until you inserted a customer without a note, and additional 
customers with notes after that.


--
Roger


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

Re: [OT-ish] Hardware for MySQL server

[SGreen]

James Harvard <[EMAIL PROTECTED]> wrote on 12/12/2005 
10:26:42 AM:

> [Apologies for my first post here being semi-off-topic!]
> 
> I normally deploys apps I develop  (MySQL with Lasso web middleware)
> with an ISP, so I have no experience of choosing hardware 
> configurations or sourcing them.
> 
> My current client's application involves a very large amount of data
> which I have split into a number of tables. These tables (data 
> files) are currently between several hundred MB and 2 GB each for 6 
> 1/2 years data, and will grow. However, tables are not updated in 
> normal use (we can take the app off-line for updates) - so the data 
> is pretty much read-only.
> 
> From my reading of the manual it seems that disc seek speed is the 
> limiting factor once tables get so large that the data and indices 
> cannot be cached in RAM. So I believe that the best hardware setup 
> for a dedicated MySQL server would include two fast discs striped 
> (RAID 0) for the databases and a third separate disc for the operating 
system.
> 
> Does this sound right? (Also thoughts on SCSI versus SATA?)
> 
> Second question:
> 
> The chap who will probably administer the servers seems to prefer 
> buying Dell, but AFAIK Dell don't do any 1U servers that would 
> support 3 drives. Can anyone recommend any server brands available 
> in the UK, or UK based companies that will build servers, supporting
> 3 discs (2 RAID & 1 for the OS)?
> 
> Many thanks,
> James Harvard
> 

General recommendations:
RAM, RAM, RAM. Pack in as much as you can afford. Try to pick a stable 
64-bit OS, too. That way you are not limited to just 2,3, or 4 GB of 
addressable RAM. But realities are what they are and that may not be an 
option for you.

The more independent I/O channels you can provide to your disks, the more 
throughput you can support. The more independent spindles you have (or 
sets of spindles because you want them RAIDed) the more concurrency you 
can support. That means you may be better off with lots of cheaper 10-16GB 
disks rather than few 250GB disks if raw disk performance is your 
necessity.

Then, you throw us the curve by saying it all had to fit in a 1U 
factor(groan)

Generally, if you have enough independent spindles to split the OS, paging 
file, and LOGS from the DATA then your performance should be fine. Your 3 
disk setup will probably have the data and the logs on the same RAID set 
and your paging file on the OS partition. It will probably be able to keep 
up but you might consider a set of external SCSI drives in a second 1U 
unit to sit under the first or consider a larger form factor so that you 
can host more drives if that turns out to be a limiting factor

Sorry I cannot make more specific recommendations but if you keep those 
general ideas in mind, your server should zip right along.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

[OT-ish] Hardware for MySQL server

[James Harvard]

[Apologies for my first post here being semi-off-topic!]

I normally deploys apps I develop  (MySQL with Lasso web middleware) with an 
ISP, so I have no experience of choosing hardware configurations or sourcing 
them.

My current client's application involves a very large amount of data which I 
have split into a number of tables. These tables (data files) are currently 
between several hundred MB and 2 GB each for 6 1/2 years data, and will grow. 
However, tables are not updated in normal use (we can take the app off-line for 
updates) - so the data is pretty much read-only.

>From my reading of the manual it seems that disc seek speed is the limiting 
>factor once tables get so large that the data and indices cannot be cached in 
>RAM. So I believe that the best hardware setup for a dedicated MySQL server 
>would include two fast discs striped (RAID 0) for the databases and a third 
>separate disc for the operating system.

Does this sound right? (Also thoughts on SCSI versus SATA?)

Second question:

The chap who will probably administer the servers seems to prefer buying Dell, 
but AFAIK Dell don't do any 1U servers that would support 3 drives. Can anyone 
recommend any server brands available in the UK, or UK based companies that 
will build servers, supporting 3 discs (2 RAID & 1 for the OS)?

Many thanks,
James Harvard

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

Re: install trouble, perl DBI

[Octavian Rasnita]

From: "Lewis Ashley Foster" <[EMAIL PROTECTED]>

>
> I'm trying to get mySQL installed on my machine but im having a bit of
> trouble, obviously :)
>
> When i come to install mySQL server as below:
>
> rpm -ivh MySQL-server-standard-5.0.16-0.rhel3.i386.rpm
>
> I get this result:
>
> warning: MySQL-server-standard-5.0.16-0.rhel3.i386.rpm: V3 DSA signature:
> NOKEY, key ID 5072e1f5
> error: Failed dependencies:
>  perl(DBI) is needed by MySQL-server-standard-5.0.16-0.rhel3
>  Suggested resolutions:
>  perl-DBI-1.32-5.i386.rpm
>
> But I have already installed perl dbi 1.40 like this with no errors:
>
> rpm -ivh perl-DBI-1.40-5.src.rpm
>
> I really cant think how to get round this, unless i downgrade the dbi to
> 1.32 but that seems daft.
>

Hi,

Try this command:

perl -MDBI -e1

If it will tell you that it can't find the  DBI module, this means that DBI
was not properly installed.
If it will give no results, this means that you have the DBI module
installed, so try the following command:

perl -M'DBI 999' -e1

It will give an error telling that you don't have DBI version 999 installed
but only the version ... and here it will tell the version number.

If it is a newer version that the version required by MySQL RPM, then
perhaps there is an error in the format of the version of DBI, or most
probably in the way MySQL checks that version and don't like a newer one.

You have some solutions:

1. Manually edit the file DBI.pm after it is installed on the computer and
write the older version instead. Then install MySQL and then change the
version back in DBI.pm file.
The newer DBI will surely work with MySQL.

2. Install the older version of DBI, install MySQL, then install again the
latest version of DBI module. If rpm doesn't allow you to do this because
you might need to remove the older version of DBI, you can download the
module DBI from search.cpan.org, and compile and install it using:

perl Makefile.pl
make
make test
make install

or use the command:

cpan

(But in this case you will need to answer to some configuration settings)
Then do:

cpan> install DBI

This will download and install the latest version of DBI.

Teddy




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

install trouble, perl DBI

[Lewis Ashley Foster]

I'm trying to get mySQL installed on my machine but im having a bit of 
trouble, obviously :)


When i come to install mySQL server as below:

rpm -ivh MySQL-server-standard-5.0.16-0.rhel3.i386.rpm

I get this result:

warning: MySQL-server-standard-5.0.16-0.rhel3.i386.rpm: V3 DSA signature: 
NOKEY, key ID 5072e1f5

error: Failed dependencies:
perl(DBI) is needed by MySQL-server-standard-5.0.16-0.rhel3
Suggested resolutions:
perl-DBI-1.32-5.i386.rpm

But I have already installed perl dbi 1.40 like this with no errors:

rpm -ivh perl-DBI-1.40-5.src.rpm

I really cant think how to get round this, unless i downgrade the dbi to 
1.32 but that seems daft.


Any ideas would be greatly appreciated.

Lewis.


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

RE: PHP4 or PHP5?

[Mikhail Berman]

 
Just to give an example of what Shawn is saying is very TRUE.

My MS-Access databases department-wide, the largest one is - 39 tables.

On MySQL side enterprise-wide database - 340 tables

Best,

Mikhail Berman


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Monday, December 12, 2005 9:36 AM
To: Charles Walmsley
Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: RE: PHP4 or PHP5?

You are most welcome. 

As a comment to Fester: sometimes even 40 tables are not enough for a
single application. Once you start dealing in enterprise-level data
systems, 40 tables is how many you wish you had. I am sure there are
some applictions using several hundred tables out there and doing just
fine. 
You can't judge the size of a database by the number of tables it has so
this may very well be a small database. 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

[EMAIL PROTECTED] wrote on 12/12/2005 05:30:57 AM:

> The main items to be stored are images and video clips but taking 
> advice from previous emails on this list, we will be holding these 
> outside
MySql so
> the tables are to do with loading these, manipulating them, and 
> keeping details on clients, customers and contacts etc.  None of them 
> will be
large.
> I estimate that if the largest one exceeds 200,000 records we will be 
> millionaires!
> 
> As I am not expecting to do anything radical I have started to write 
> the site in PHP5.
> 
> Thanks to those who replied to my email
> 
> Ch
> 
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]
On
> Behalf Of Peter M. Groen
> Sent: 12 December 2005 00:14
> To: mysql@lists.mysql.com
> Subject: Re: PHP4 or PHP5?
> 
> 
> On Sunday 11 December 2005 23:51, Charles Walmsley wrote:
> > Dear All,
> >
> >
> >
> > I do not have much experience with PHP or MySql although I have used
SQL
> > quite a lot.  I am going to set up a relatively small MySQL database
> (circa
> > 40 tables) and we are expecting a hit rate of about 40,000 visitors
per
> > annum mostly browsing a relatively low number of pages each.  We 
> > plan
to
> > launch in March
> >
> >[ 8< ]--
> 
> Ehm.. To be blunt... 40 TABLES??? You call that small? What on earth 
> are
you
> 
> going to store.
> 
> Fester
> 

> 


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

libmysql connection timer problems

[Michael Katz]

We have an application that uses the libmysql  libraries for email 
archival.   We have recently been getting killed because of 
http://bugs.mysql.com/bug.php?id=4143.  It seems that mysql keels over 
during a big query on a large database and it will not let our client 
write to the db until the queries are completed.  Since there are no 
connection timers in the default client our app was hanging until the 
query was completed.  We applied the patches in the bug and we have a 
patched client with connection timers but I want to ask a few questions 
relative to these types of issues.



1) A lack of connection timers seems like a massive omission.  There is 
a patch for Linux, but as far as I know there is nothing available for 
other OS's.  It is impossible to build reliable database applications 
without connection timers as we have learned.  If we want to contribute 
connection timer patches for other OS's for the client how can we go 
about doing this?


2) Has anyone else been burned by this bug?  If not, beware as there is 
a lurking time bomb if your db can not keep up with load or fails for 
some reason.


3) We are very disappointed that mysql can not seem to run an index job 
or large queries and still accept connections.  We are looking into the 
complete system to find where the bottlenecks are and hopefully it will 
be simple to improve the performance, but the initial indications are 
very disturbing.


Thanks,
MK

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

Re: PHP4 or PHP5?

[Martijn Tonies]

> > I do not have much experience with PHP or MySql although I have used SQL
> > quite a lot.  I am going to set up a relatively small MySQL database
(circa
> > 40 tables) and we are expecting a hit rate of about 40,000 visitors per
> > annum mostly browsing a relatively low number of pages each.  We plan to
> > launch in March
> >
> >[ 8< ]--
>
> Ehm.. To be blunt... 40 TABLES??? You call that small? What on earth are
you
> going to store.

LOL ... yes, that is small (at least in complexity it is).



Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com


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

RE: PHP4 or PHP5?

[SGreen]

You are most welcome. 

As a comment to Fester: sometimes even 40 tables are not enough for a 
single application. Once you start dealing in enterprise-level data 
systems, 40 tables is how many you wish you had. I am sure there are some 
applictions using several hundred tables out there and doing just fine. 
You can't judge the size of a database by the number of tables it has so 
this may very well be a small database. 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

[EMAIL PROTECTED] wrote on 12/12/2005 05:30:57 AM:

> The main items to be stored are images and video clips but taking advice
> from previous emails on this list, we will be holding these outside 
MySql so
> the tables are to do with loading these, manipulating them, and keeping
> details on clients, customers and contacts etc.  None of them will be 
large.
> I estimate that if the largest one exceeds 200,000 records we will be
> millionaires!
> 
> As I am not expecting to do anything radical I have started to write the
> site in PHP5. 
> 
> Thanks to those who replied to my email
> 
> Ch
> 
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] 
On
> Behalf Of Peter M. Groen
> Sent: 12 December 2005 00:14
> To: mysql@lists.mysql.com
> Subject: Re: PHP4 or PHP5?
> 
> 
> On Sunday 11 December 2005 23:51, Charles Walmsley wrote:
> > Dear All,
> >
> >
> >
> > I do not have much experience with PHP or MySql although I have used 
SQL
> > quite a lot.  I am going to set up a relatively small MySQL database
> (circa
> > 40 tables) and we are expecting a hit rate of about 40,000 visitors 
per
> > annum mostly browsing a relatively low number of pages each.  We plan 
to
> > launch in March
> >
> >[ 8< ]--
> 
> Ehm.. To be blunt... 40 TABLES??? You call that small? What on earth are 
you
> 
> going to store.
> 
> Fester
> 

> 

MySQL hangs each relative poor time

[Roberto Rodriguez Garrido]

MySQL hangs every 30 minutes, it start to make a big process and when I make a 
show variables I get that values:

+-+-+
| Variable_name   | Value   |
+-+-+
| back_log| 50  |
| basedir | /usr/   |
| binlog_cache_size   | 32768   |
| bulk_insert_buffer_size | 8388608 |
| character_set_client| latin1  |
| character_set_connection| latin1  |
| character_set_database  | latin1  |
| character_set_results   | latin1  |
| character_set_server| latin1  |
| character_set_system| utf8|
| character_sets_dir  | /usr/share/mysql/charsets/  |
| collation_connection| latin1_swedish_ci   |
| collation_database  | latin1_swedish_ci   |
| collation_server| latin1_swedish_ci   |
| concurrent_insert   | ON  |
| connect_timeout | 10  |
| datadir | /var/lib/mysql/ |
| date_format | %Y-%m-%d|
| datetime_format | %Y-%m-%d %H:%i:%s   |
| default_week_format | 0   |
| delay_key_write | ON  |
| delayed_insert_limit| 100 |
| delayed_insert_timeout  | 300 |
| delayed_queue_size  | 1000|
| expire_logs_days| 0   |
| flush   | OFF |
| flush_time  | 0   |
| ft_boolean_syntax   | + -><()~*:""&|  |
| ft_max_word_len | 84  |
| ft_min_word_len | 4   |
| ft_query_expansion_limit| 20  |
| ft_stopword_file| (built-in)  |
| group_concat_max_len| 1024|
| have_archive| YES |
| have_bdb| NO  |
| have_compress   | YES |
| have_crypt  | YES |
| have_csv| YES |
| have_example_engine | NO  |
| have_geometry   | YES |
| have_innodb | YES |
| have_isam   | YES |
| have_ndbcluster | DISABLED|
| have_openssl| NO  |
| have_query_cache| YES |
| have_raid   | YES |
| have_rtree_keys | YES |
| have_symlink| YES |
| init_connect| |
| init_file   | |
| init_slave  | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8   |
| innodb_buffer_pool_awe_mem_mb   | 0   |
| innodb_buffer_pool_size | 8388608 |
| innodb_data_file_path   | ibdata1:10M:autoextend  |
| innodb_data_home_dir| |
| innodb_fast_shutdown| ON  |
| innodb_file_io_threads  | 4   |
| innodb_file_per_table   | OFF |
| innodb_flush_log_at_trx_commit  | 1   |
| innodb_flush_method | |
| innodb_force_recovery   | 0   |
| innodb_lock_wait_timeout| 50  |
| innodb_locks_unsafe_for_binlog  | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive  | OFF |
| innodb_log_buffer_size  | 1048576 |
| innodb_log_file_size| 5242880 |
| innodb_log_files_in_group   | 2   |
| innodb_log_group_home_dir   | ./  |

Re: Select Unique?

[Rhino]

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

To: 
Sent: Monday, December 12, 2005 12:34 AM
Subject: Select Unique?


I have two tables 'table1', 'table2' with a matching column 'column1'.  How 
can I return all rows from table2 where the entry for table2.column1 does 
not match any entries in table1.column1?


SELECT * FROM table2 WHERE table2.column1 <> table1.column1

returns all the rows, rather than the unique rows in table2 ... Any ideas?


SELECT *
FROM table2
where table2.column1 not in
(select distinct column1
from table1
where column1 not null)

The 'distinct' in the subquery is not strictly necessary but should help 
performance. The WHERE clause in the subquery is often omitted but really 
shouldn't be.


Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.13.13/198 - Release Date: 12/12/2005


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

backwards compatibility when exporting M

[Adam Lipscombe]

Folks


I am trialling MySQL 5 before we all upgrade, however others need to import 
my dump files into their existing MySQL4.x databases

MySQL 5 mysqldump seems exports BIT fields as true = '
', false = '\0'.

When  that dump file is imported into MySQL 4 these value are not 
interpreted correctly. MySQL4.x does not have a real BIT type , rather it 
uses TINYINT(1).
Both  '
' and '\0' seem to be interpreted as "false", and the relevant 
TINYINT(1) field is set to 0.

So far that has meant that I have to hand-edit my dump file to convert  '
' 
to 1,  '\0' to 0.

Does anyone know a way around this?



TIA - Adam Lipscombe


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

Re: Mysql 4.0 always executes case insensitive queries

[Shen139]

You should use:
SELECT username FROM workflow.user WHERE username LIKE BINARY 'NicO'  LIMIT
1;

reference:
http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html

On 12/12/05, Nico Sabbi <[EMAIL PROTECTED] > wrote:
>
> Hi,
> my mysql always executes case insensitive queries:
>
>
> SELECT username FROM workflow.user WHERE username = 'NicO'  LIMIT 1;
> +--+
> | username |
> +--+
> | nico |
> +--+
> 1 row in set (0.01 sec)
>
>
>
> that field is of varchar(255) type.
>
> I don't understand the reason for this behavior.
> What should I check?
>
Thanks,
>
> Nico
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>


--

http://www.openwebspider.org
http://www.eviltime.com

Re: Mysql 4.0 always executes case insensitive queries

[Wolfram Kraus]

Nico Sabbi wrote:

Hi,
my mysql always executes case insensitive queries:


SELECT username FROM workflow.user WHERE username = 'NicO'  LIMIT 1;
+--+
| username |
+--+
| nico |
+--+
1 row in set (0.01 sec)



that field is of varchar(255) type.

I don't understand the reason for this behavior.
What should I check?

Thanks,

Nico


You need the binary function:

http://dev.mysql.com/doc/refman/4.1/en/cast-functions.html

HTH,
Wolfram


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

RE: PHP4 or PHP5?

[Charles Walmsley]

The main items to be stored are images and video clips but taking advice
from previous emails on this list, we will be holding these outside MySql so
the tables are to do with loading these, manipulating them, and keeping
details on clients, customers and contacts etc.  None of them will be large.
I estimate that if the largest one exceeds 200,000 records we will be
millionaires!

As I am not expecting to do anything radical I have started to write the
site in PHP5.  

Thanks to those who replied to my email

Ch

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On
Behalf Of Peter M. Groen
Sent: 12 December 2005 00:14
To: mysql@lists.mysql.com
Subject: Re: PHP4 or PHP5?

 
On Sunday 11 December 2005 23:51, Charles Walmsley wrote:
> Dear All,
>
>
>
> I do not have much experience with PHP or MySql although I have used SQL
> quite a lot.  I am going to set up a relatively small MySQL database
(circa
> 40 tables) and we are expecting a hit rate of about 40,000 visitors per
> annum mostly browsing a relatively low number of pages each.  We plan to
> launch in March
>
>[ 8< ]--

Ehm.. To be blunt... 40 TABLES??? You call that small? What on earth are you

going to store.

Fester

-- 
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 4.0 always executes case insensitive queries

[Nico Sabbi]

Hi,
my mysql always executes case insensitive queries:


SELECT username FROM workflow.user WHERE username = 'NicO'  LIMIT 1;
+--+
| username |
+--+
| nico |
+--+
1 row in set (0.01 sec)



that field is of varchar(255) type.

I don't understand the reason for this behavior.
What should I check?

Thanks,

Nico


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

Re: mysqldump: INSERTS for each individual record.

[Gleb Paharenko]

Hello.



Add --skip-extended-insert to mysqldump options.





Michael Williams wrote:

> When performing  "mysqldump"  is there any way to ensure that each 

> record gets an INSERT of it's own?  I keep getting the following:

> 

> INSERT INTO  'mytable' (1,'test item'), (2,'test item'), (3,'test 

> item'), (4,'test item'), (5,'test item');

> 

> but I'd rather have

> 

> INSERT INTO  'mytable' (1,'test item');

> INSERT INTO  'mytable' (2,'test item');

> INSERT INTO  'mytable' (3,'test item');

> INSERT INTO  'mytable' (4,'test item');

> INSERT INTO  'mytable' (5,'test item');

> 

> because I'm doing my own line diff between files and it's much easier 

> for me to have the items on individual lines.  Any assistance would  be

> appreciated.

> 

> Regards,

> Michael

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [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: Need Help with a query

[Gleb Paharenko]

Hello.



You may use these queries:



select flight_id

,baseline*tan(radians(angle)) as attitude

from flights

where (baseline*tan(radians(angle))) =

( select max(baseline*tan(radians(angle)))

from flights  f2);



+---+-+

| flight_id | attitude|

+---+-+

| 2 | 119.17535925942 |

+---+-+



mysql> select flight_id, baseline*tan(radians(angle)) as attitude from

flights where (baseline*tan(radians(angle)))= (select

min(baseline*tan(radians(angle))) from flights  f2);

+---+-+

| flight_id | attitude|

+---+-+

| 8 | 72.426658110531 |

+---+-+



However, you should be aware about rounding errors and possibly 

different results in 5.0 and older versions. See:

  http://dev.mysql.com/doc/refman/5.0/en/precision-math.html









Mark Phillips <[EMAIL PROTECTED]> wrote:

> I have a table with several columns. The ones of interest are flight_id, 

> angle, and baseline. I want to find the flight_ids for the flights with the 

> maximum and minimum altitudes, where altitude=baseline*tan(radians(angle)).

> 

> For example, 

> Flights

> +++---+

> | flight_id | angle| baseline  |

> +++---+

> |   1 | 37.0 | 100.0 |

> |   2 | 50.0 | 100.0 |

> |   3 | 48.0 | 100.0 |

> |   4 | 40.0 | 100.0 |

> |   5 | 44.0 | 100.0 |

> |   6 | 40.0 | 100.0 |

> |   7 | 45.0 | 100.0 |

> |   8 | 44.0 |  75.0 |

> |   9 | 57.8 |  75.0 |

> +++---+

> 

> The result I am looking for are:

> 

> Maximum altitude:

> +++

> | flight_id | altitude |

> +++

> |   2 | 119.17536 | 

> |   9 | 119.17536| 

> +++

> 

> Minimum altitude:

> +---+---+

> | flight_id | altitudeM |

> +---+---+

> | 8 |  72.42666 |

> +---+---+

> 

> Thanks for any help you can provide!

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [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: SQL on Mac OS X - Socket Errors

[Gleb Paharenko]

Hello.



General recommendations are available here:

 http://dev.mysql.com/doc/refman/5.0/en/can-not-connect-to-server.html





Nathan Whitington <[EMAIL PROTECTED]> wrote:

> Hello there,

> 

> I've searched long and hard, and pestered many people for a solution  

> and for help however I can not get around this problem.

> 

> I have installed MySQL on my computer which is an Apple iBook G4  

> which is running Mac OS X 10.4.2 and I wish to use MySQL so that I  

> can learn something and play with it.

> 

> I have tried to connect and startup the database through the terminal  

> and I've even tried to startup MyPHPAdmin, however I'm getting this  

> message:

> 

> #2002 - The server is not responding (or the local MySQL server's  

> socket is not correctly configured)

> 

> Does anybody know what it is I can do to start to get around this  

> problem?

> 

> Thank you very much in advance,

> 

> Nathan Whitington <><

> [EMAIL PROTECTED]

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [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]