Mysql hypothetical performance

[Scott Haneda]

I build a few login and password systems in MySql, my general layout is as
follows:

Users table and account table.  The users table has the bare minimum columns
in it, id, username, password, session, updated, added.

Account table has stuff like first name, last name, email address, street
address etc etc.  Sometimes upwards of 30 or so fields.

My logic in this comes from a past history with Filemaker, which never
really supported relations well, relations were each separate databases.
The idea was, the less data in each database, the faster you can select it.
So with only minimal data in the users database in filemaker, I and not
needeing the account data very often, that is how I built it.

In a login pass system, I am selecting from the users table on every page
load, I only need to select the session field generally.  My question is,
does it matter?  it would be easier to put all the data in one table, but
then that one table will have more data in it.  I however, will only be
selecting one item from that now large table.

Summary:
is SELECT session from users where session = 'x' faster or slower depending
on how many columns are in the table?
-- 
-
Scott HanedaTel: 415.898.2602
 Fax: 313.557.5052
<[EMAIL PROTECTED]>  Novato, CA U.S.A.



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

error on restting password - with some addition

[fredrich]

in addition :
when issue : 
"mysqld-nt --skip-grant-tables"
mysql stop automatically, and in process tab on task
manager there is no any process name mysqld*.

thats why i just added a line "skip-grant-tables" on
my.ini.

my next question is can we address this problem by
installing 4.1.9. and/or delete any data in
mysql\data\mysql. and copy paste database from 4.1.5
to 4.1.9.

thanks.




__ 
Do you Yahoo!? 
Yahoo! Mail - Helps protect you from nasty viruses. 
http://promotions.yahoo.com/new_mail

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

error on restting password

[fredrich]

hi all,

i need to reset the root password. i have done 2
things.
but not worked.

i read 
http://dev.mysql.com/doc/mysql/en/resetting-permissions.html

and do ..

a). i changed my.ini, add a line like
"skip-grant-tables"
and started mysql. then issue command ..
mysqladmin -u root flush-privileges password "mypass"
but error :
mysqladmin: unable to change password; error: 'Can't
find any matching row in the user table'

b). i choose alternative way.
"update user set password=PASSWORD('mypass') where
user='root';
flush privileges;"

then restart mysql.

the password is changed. but i cannot login when issue

"mysql -u root -p". and type mypass

SPEC : win 2003 + mysql 4.1.5.

on spec : WINxp sp 2 + mysql 4.1.9 it works fine.

note : i want to reset password on mysql 4.1.5. 
any idea for this ?

thanks.



__ 
Do you Yahoo!? 
Take Yahoo! Mail with you! Get it on your mobile phone. 
http://mobile.yahoo.com/maildemo 

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

Re: MySQL as Email database [sort of OT]

[Steven Weintraut]

Hi! I just finished a project just like this.
We just put in a brand new mail server running PostFix on Mac OS X 
Panther. We added all the extra bells and whistles for spam and virus 
filtering, as well as squirrel mail

There is an single-line option in Postfix's config file to have all 
email going into and out of the server cc'd to a single mailbox

We setup a catch-all account that all email gets cc'd to, and then 
after playing around with a php scripts for a while, I actually wound 
up writing a program to pull the emails out of that account and pour 
them into mysql tables using a programming environment called 
RealBasic, which has a real nice built-in set of POP email and 
attachment handling classes, plus a mysql link as well

Now that the emails are in sql, we've also integrated searching and 
linking through some of our php web interfaces. All in all, so far, 
it's working well.

You could do the same in php, but I was able to write a real nice 
interface for the sql-link in realbasic in a few hours.

On Feb 10, 2005, at 12:35 PM, [EMAIL PROTECTED] wrote:
Brent Baisley <[EMAIL PROTECTED]> wrote on 02/10/2005 12:21:38 PM:
I am looking to store all incoming emails into a MySQL database. I've
looked into dbmail, but can't get it to compile under Mac OSX (I 
posted
a message on that list). I was wondering if anyone could point me in
another direction to use MySQL as an email message store. I don't need
a webmail interface, just a way of getting messages from a mail server
to a MySQL database. Preferably as a direct transfer, but it could be 
a
script that runs periodically.
Currently it seems the best path is using Perl, but I would think this
has been done before, just can't find it on google.

Thanks
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
How to do what you ask depends on how I read your question.  When you 
say
"store incoming emails" does that mean that you are writing an SMTP 
server
that will act as a mail destination or are you retrieving mail from an
SMTP server via POP3 or IMAP or some other mail retrieval protocol?

In either case your code ends up writing the contents of the message 
to a
MySQL database rather than to a local file. What you need to decide is 
how
many pieces you want to break the message into (how much metadata you 
want
to extract from each message). Your database structure will depend on 
your
information needs and how you intend to use the emails once you store 
them
however I can almost guarantee that the body of each message will need 
to
be stored in a TEXT field.

You will probably run out of room faster by storing whole messages in 
your
database than if you were storing them as files on the hard drive.
Capacity control is definitely something you want to consider in your
project design. You may want to store the messages (or parts of them) 
as
files and keep just the metadata in the database, but again that goes 
to
your intended purpose for this project.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

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

Error 1271 (HY000) - Illegal mix of collations

[Eli]

Hi,
I'm running a query using UNION, where all parts of the union are 
queries from the same syntax and from tables with same definition, and 
the select is same too. Each of the union parts is a query with JOINs.

I got this error:
ERROR 1271 (HY000): Illegal mix of collations for operation 'UNION'
I tried to look after it on the net, but couldn't find anything meaningful.
thanks in advance,
-Eli.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

ANN: Gtk2::Ex::DBI-0.7

[Daniel Kasak]

I'm pleased to annouce the 7th release of Gtk2::Ex::DBI ...
http://entropy.homelinux.org/Gtk2-Ex-DBI/
Gtk2::Ex::DBI is an open-source helper object that makes your Gtk2-Perl 
apps data aware. It handles querying, 'painting' records on your 
Glade-generated form, passing updates back to the database server, 
inserting, deleting, and much more. Roll your own MS Access.

This release completes support for MS SQL Server ( and almost certainly 
Sybase ) via DBD::ODBC. Other servers may also work with via DBD::ODBC.

Postgres support is *very* close. I basically need to know how to fetch 
the sequence of an inserted record via $dbh->last_insert_id ... but I 
don't use Postgres and the docs are a little obscure on what's needed. 
Postgres users are encouraged to step up to the plate and complete this 
small missing link :)

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

Re: Any means to get the optimizer out of the way?

[Harrison Fisk]

Hi,
On Feb 10, 2005, at 7:00 PM, Brad Eacker wrote:
Folks,
 I have a 677M row table with index
desc pts_awarded_snap;
+-+-+--+-+-+---+
| Field   | Type| Null | Key | Default | Extra |
+-+-+--+-+-+---+
| member_id   | int(11) |  | MUL | 0   |   |
| PTS_AWARDED | int(11) |  | | 0   |   |
+-+-+--+-+-+---+
2 rows in set (0.00 sec)
select count(*) from pts_awarded_snap;
+---+
| count(*)  |
+---+
| 776723372 |
+---+
1 row in set (0.00 sec)
Which you can see has an index on member_id.
The problem is when I try to create a temporary table from a
portion of this based upon the member_id, the optimizer decides
that a table scan is necessary on this 7GB table.
When I try:
  create temporary table T_awards_snap
select member_id, pts_awarded
from pts_awarded_snap FORCE INDEX (member_id)
where member_id between 71 and 80;
the optimizer decides to do a full table scan to choose the
required rows.
Yet a counting query  of the same space:
  select count(*) from pts_awarded_snap
where member_id between 71 and 80;
+--+
| count(*) |
+--+
|  3957677 |
+--+
1 row in set (5.84 sec)
works fine.
  explain select member_id, pts_awarded
from pts_awarded_snap FORCE INDEX (member_id)
where member_id between 71 and 80;
+--+---+---+---+- 
+--+--
---+-+
| table| type  | possible_keys | key   | key_len | ref  
 | rows
   | Extra   |
+--+---+---+---+- 
+--+--
---+-+
| pts_awarded_snap | range | member_id | member_id |   4 |  
NULL |
3885524 | Using where |
+--+---+---+---+- 
+--+--
---+-+
1 row in set (0.00 sec)

seems to indicate the member_id index can be used.  But the length of  
time to
do this query, and the amount blocks being read as indicated by  
vmstat, tell
me that a full table scan is being performed.
As it says above, it is using the index to resolve the query.  If it  
wasn't using the index it would say NULL under the key column listed  
above.

The difference between the count(*) and the other query is that the  
real query has to use the datafile to retrieve the data when you are  
involving the actual columns.  With the count(*) query it is using an  
Index only read, meaning that it doesn't have to the use the datafile  
at all to resolve it.  If you do an EXPLAIN on the count(*) query, you  
should see a 'Using Index' in the Extra column.  So it is using the  
index and estimating it is going to have to read 3885524 rows from the  
data file.  Assuming the estimate is close, that will be an extra  
3885524 disk seeks and reads to find the data for your query.  That is  
why vmstat is showing the query doing much more disk i/o.

The only way you could improve this is to make a combined index across  
(member_id, pts_awarded) and get rid of the only (member_id) index.   
Then MySQL would be able to again use only the index to resolve the  
query.  Keep in mind this would increase your index size by about a  
third, so it would take more diskspace and you would fit less into  
cache, so it would decrease response times slightly for the count(*)  
query.

Regards,
Harrison
--
Harrison C. Fisk, Trainer and Consultant
MySQL AB, www.mysql.com
Get a jumpstart on MySQL Cluster --  
http://www.mysql.com/consulting/packaged/cluster.html

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

table handler error

[David Mehringer]

[MySQL][ODBC 3.51 Driver][mysqld-4.0.20]Got error 5 from table handler

Can anyone translate this for me and/or point me to a mapping of 
table handler error codes to their meanings?
Thanks



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

Sub query again

[daniel]

I have worked out, to send that field to the where query, i have to select
the outer table again !!
i dont think this is very efficient ?

select * from complaints, (SELECT IF (count(*) > 1,"Yes","No") AS count
FROM complainant ccINNER JOIN complaints c ON cc.complainantID=c.complainantID 
WHERE
c.complainantID IN(SELECT complainantID FROM complaints WHERE
complaintID='.$_GET["complaintID"].')) AS countWHERE 
complaintID=$_GET['complaintID']



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

Any means to get the optimizer out of the way?

[Brad Eacker]

Folks,
 I have a 677M row table with index

desc pts_awarded_snap;
+-+-+--+-+-+---+
| Field   | Type| Null | Key | Default | Extra |
+-+-+--+-+-+---+
| member_id   | int(11) |  | MUL | 0   |   |
| PTS_AWARDED | int(11) |  | | 0   |   |
+-+-+--+-+-+---+
2 rows in set (0.00 sec)

select count(*) from pts_awarded_snap;
+---+
| count(*)  |
+---+
| 776723372 |
+---+
1 row in set (0.00 sec)

Which you can see has an index on member_id.

The problem is when I try to create a temporary table from a
portion of this based upon the member_id, the optimizer decides
that a table scan is necessary on this 7GB table.

When I try:
  create temporary table T_awards_snap
select member_id, pts_awarded
from pts_awarded_snap FORCE INDEX (member_id)
where member_id between 71 and 80;

the optimizer decides to do a full table scan to choose the
required rows.

Yet a counting query  of the same space:

  select count(*) from pts_awarded_snap
where member_id between 71 and 80;
+--+
| count(*) |
+--+
|  3957677 |
+--+
1 row in set (5.84 sec)

works fine.

  explain select member_id, pts_awarded
from pts_awarded_snap FORCE INDEX (member_id)
where member_id between 71 and 80;
+--+---+---+---+-+--+--
---+-+
| table| type  | possible_keys | key   | key_len | ref  | rows 
   | Extra   |
+--+---+---+---+-+--+--
---+-+
| pts_awarded_snap | range | member_id | member_id |   4 | NULL | 
3885524 | Using where |
+--+---+---+---+-+--+--
---+-+
1 row in set (0.00 sec)

seems to indicate the member_id index can be used.  But the length of time to
do this query, and the amount blocks being read as indicated by vmstat, tell
me that a full table scan is being performed.

 I've looked over the optimization elements of the MySQL manual, trying

set max_seeks_for_key=100;

even 'FORCE INDEX (member_id)' has been tried as shown in the query above
to no avail, a table scan is still done.

 Bottom line question out of all this:  Is there a way to convince the
optimizer that it is fine to use a range portion of the index to fulfill
this query?

  Please let me know if there is something I've missed,
  Brad Eacker ([EMAIL PROTECTED])



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

Sub Query question

[Daniel Rossi]

Hi there, I was wondering if its possible to be able to send a field 
from the outer table to be used as the where statement for the sub 
query ?

something like this
select somefield from table 1, (select count(*) from table2 inner join 
table1 using somekey where table1.key=somefield) as alias

where somefield is in the outer table
what i was also hoping to achieve was something like this
select somefield from table 1, (select field2 from table2 inner join 
table1 using somekey where table1.key=somefield) as alias

and alias would contain rows of a one to many resulset  :| that 
would be cool , so then i can list in a datagrid without having to do a 
nested loop !

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

RE: 1 to many query

[Tom Crimmins]

> -Original Message-
> From: Tom Crimmins
> Sent: Thursday, February 10, 2005 17:08
> To: livejavabean
> Cc: mysql@lists.mysql.com
> Subject: RE: 1 to many query 
> 
> 
> > -Original Message-
> > From: livejavabean
> > Sent: Thursday, February 10, 2005 16:47
> > To: mysql@lists.mysql.com
> > Subject: 1 to many query 
> > 
> > Hi there..
> > 
> >  
> > 
> > Hope you can give me some thoughts on this. let say we have 3 tables
> > 
> > 
> > 
> > table 1 (pk=project_id)
> > ===
> > - project_id 
> > - project_name 
> > 
> > table 2 (pk=project_id, project_state_flag)
> > ===
> > - project_id
> > - project_state_flag (fk to state_flag)
> > 
> > table 3 (pk=state_flag)
> > ===
> > - state_flag
> > - state_flag_name 
> > 
> > 
> > thank you.. but do u think it is possible to make the query return: 
> > 
> > - 1 row per project 
> > - each project state row's state become a column
> > e.g. 
> > 
> > project 1, name, state a, state b, state c... 
> > project 2, name, state a, state b, state c.
> > 
> > thanks in advance..
> 
> This looks like a many to many relationship to me. Each project is
> associated with multiple state_flags, and each state_flag can 
> be associated
> with multiple projects.
> 
> If you have mysql 4.1 or greater, you can use try the 
> following. It won't
> get you separate columns for each state_flag_name, but it 
> will give you a
> list of all the state_flag_names associated with each project 
> in a single
> column.
> 
> SELECT t1.project_id, t1.project_name, 
> GROUP_CONCAT(t3.state_flag_name) as
> state_flags FROM t1 INNER JOIN t2 ON (t1.project_id = 
> t2.project_id) INNER
> JOIN t3 ON (t2.project_state_flag = t3.state_flag) GROUP BY 
> t1.project_id

Sorry, also forgot to add that if you want projects returned that don't have
any state_flags associated with them you will need to make that first inner
join a left join.

> 
> > 
> > regards,
> > -ljb
> 
> ---
> Tom Crimmins
> Interface Specialist
> Pottawattamie County, Iowa
> 

---
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa

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

Re: "show table status" extremely slow

[Eric Bergen]

My experience with innodb is that show table status is slow. It's
better to do show table status like 'my_table'

-Eric


On Thu, 10 Feb 2005 16:47:41 -0500, Zhe Wang <[EMAIL PROTECTED]> wrote:
> Hi, there,
> 
> We have a huge database (84 tables of about 360 G of data in MyISAM
> tables). Recently, we converted the entire database to InnoDB (in one
> table space) and set up replication. Then we experienced some slower
> performance.
> 
>  For example, "show table status" on the master took more than 90
> seconds if the database connection was made from local server, and
> terribly 400 seconds if the connection was from a remote server. At the
> moment "show table status" was issued, there were about 5 other queries
> running.
> 
>  However, if the test was done on the slave,  "show table status"
> took approximately 90 seconds for each of the connections from the local
> and remote servers, while only the slave thread was running in the
> meanwhile.
> 
>  Our questions are:
> 1. Is "show table status" generally extremely slow for InnoDB tables?
> 2. Does connection from the local or remote server affect the speed of
> "show table status"?
> 3. Does the fact one server a master another a slave affect the speed of
> "show table status"?
> 
>   Your reply would be greatly appreciated! Thank you in advance.
> 
> Regards,
> Zhe
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 


-- 
Eric Bergen
[EMAIL PROTECTED]
http://www.bleated.com

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

RE: 1 to many query

[Tom Crimmins]

> -Original Message-
> From: livejavabean
> Sent: Thursday, February 10, 2005 16:47
> To: mysql@lists.mysql.com
> Subject: 1 to many query 
> 
> Hi there..
> 
>  
> 
> Hope you can give me some thoughts on this. let say we have 3 tables
> 
> 
> 
> table 1 (pk=project_id)
> ===
> - project_id 
> - project_name 
> 
> table 2 (pk=project_id, project_state_flag)
> ===
> - project_id
> - project_state_flag (fk to state_flag)
> 
> table 3 (pk=state_flag)
> ===
> - state_flag
> - state_flag_name 
> 
> 
> thank you.. but do u think it is possible to make the query return: 
> 
> - 1 row per project 
> - each project state row's state become a column
> e.g. 
> 
> project 1, name, state a, state b, state c... 
> project 2, name, state a, state b, state c.
> 
> thanks in advance..

This looks like a many to many relationship to me. Each project is
associated with multiple state_flags, and each state_flag can be associated
with multiple projects.

If you have mysql 4.1 or greater, you can use try the following. It won't
get you separate columns for each state_flag_name, but it will give you a
list of all the state_flag_names associated with each project in a single
column.

SELECT t1.project_id, t1.project_name, GROUP_CONCAT(t3.state_flag_name) as
state_flags FROM t1 INNER JOIN t2 ON (t1.project_id = t2.project_id) INNER
JOIN t3 ON (t2.project_state_flag = t3.state_flag) GROUP BY t1.project_id

> 
> regards,
> -ljb

---
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa

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

1 to many query

[livejavabean]

Hi there..

 

Hope you can give me some thoughts on this. let say we have 3 tables



table 1 (pk=project_id)
===
- project_id 
- project_name 

table 2 (pk=project_id, project_state_flag)
===
- project_id
- project_state_flag (fk to state_flag)

table 3 (pk=state_flag)
===
- state_flag
- state_flag_name 


thank you.. but do u think it is possible to make the query return: 

- 1 row per project 
- each project state row's state become a column
e.g. 

project 1, name, state a, state b, state c... 
project 2, name, state a, state b, state c.

thanks in advance..

regards,
-ljb

RE: Add an ENUM Column

[Jay Blanchard]

[snip]
Is it possible to add an ENUM column to a table after it has been
created?
[/snip]

Yes... http://dev.mysql.com/doc/mysql/en/alter-table.html

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

"show table status" extremely slow

[Zhe Wang]

Hi, there,
   We have a huge database (84 tables of about 360 G of data in MyISAM 
tables). Recently, we converted the entire database to InnoDB (in one 
table space) and set up replication. Then we experienced some slower 
performance.

For example, "show table status" on the master took more than 90 
seconds if the database connection was made from local server, and 
terribly 400 seconds if the connection was from a remote server. At the 
moment "show table status" was issued, there were about 5 other queries 
running.

However, if the test was done on the slave,  "show table status" 
took approximately 90 seconds for each of the connections from the local 
and remote servers, while only the slave thread was running in the 
meanwhile.

Our questions are:
1. Is "show table status" generally extremely slow for InnoDB tables?
2. Does connection from the local or remote server affect the speed of 
"show table status"?
3. Does the fact one server a master another a slave affect the speed of 
"show table status"?

 Your reply would be greatly appreciated! Thank you in advance.
Regards,
Zhe
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Add an ENUM Column

[shaun thornburgh]

Hi,
Is it possible to add an ENUM column to a table after it has been created?
Thanks

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

RE: php conection problems

[Jeff Mao]

Argh,...this is what happens when you leave your sysadmin job to 
become a bureaucrat!  Lost my edge I guess,...I just looked again at 
what I downloaded earlier today to install,...and I had the version 
for Mac OS X v 10.2 and not 10.3,...

Thanks all for your help!
Jeff
At 8:29 AM +1100 2/11/05, Logan, David (SST - Adelaide) wrote:
As I said, I don't know the process on a mac (unless this is os X and
then I can guess) so can't advise on that. The manual at
http://www.php.net/manual/en/install.macosx.php probably has the info
there.
Another reason would be the different release schedules with php and
mysql. They are dropping the built in mysql for version 5.0 of php. That
is documented at the php site.
The php site has all the installation instructions for a Mac and should
be able to guide you through building your own. The mysql site
http://dev.mysql.com/downloads/mysql/4.1.html (this for version 4.1) has
binaries prebuilt for a Mac OS X
If you still don't get the info you need for the above pages, try
running
./configure --help at the command prompt. This will give you all the
options available to you including directing the compiler/linker etc. to
the right spot for your mysql client libraries.
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: Jeff Mao [mailto:[EMAIL PROTECTED]
Sent: Friday, 11 February 2005 7:52 AM
To: Logan, David (SST - Adelaide)
Cc: mysql@lists.mysql.com
Subject: RE: php conection problems
I'm running Mac OS X,...so if the the disable and rebuild isn't too
hard, can you outline that process?
Jeff
Probably because they don't have the time to get the latest and
greatest
client library every time. I'm not sure how to rebuild it on Windows
but
you can disable the built in on unix and rebuild it linking in the
latest library.
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: Jeff Mao [mailto:[EMAIL PROTECTED]
Sent: Friday, 11 February 2005 7:36 AM
To: mel list_php; mysql@lists.mysql.com
Subject: RE: php conection problems
That did the trick,...thanks
so,...why is my Apache installation using an old client?  After
reading your suggestion, it makes more sense when I look at the
output of phpinfo() which says that mysql is "builtin" and the Client
API version is 3.23.49,...why?!  I guess it was a bad assumption on
my part that a fresh install of mysql would include all new
versions?!  Or is this "builtin" 3.23.49 part of my Apache
installation that shipped with my laptop?
If anyone can shed some light on how to update that part of it, I'd
greatly appreciate it!  I'd hate to have to go through these extra
steps every time!
Jeff
At 8:55 PM + 2/10/05, mel list_php wrote:
I'm not sure if it can be a password problem as you can connect
through command line,but when upgrading to 4.1 I had the classical
"access deny" error with phpmyadmin whereas everything else was ok.
You can still have a look there:
http://dev.mysql.com/doc/mysql/en/old-client.html
but I think in that case you should have an explicit error message.
(Client does not support authentication protocol).
You can still try to update dbUser set password=old_password(***)
for one of your user and give it a try.
good luck!
From: Jeff Mao <[EMAIL PROTECTED]>
To: Jay Blanchard <[EMAIL PROTECTED]>,
mysql@lists.mysql.com
Subject: RE: php conection problems
Date: Thu, 10 Feb 2005 15:36:19 -0500
I've tried using a variety of very wide open permissions just to
get something to connect including:
using the root user/password in the php scripts
 >>>
declaring a user in mysql,...
grant all on mydatabase.* to myuser identified by 'mypassword';
grant all on mydatabase.* to myuser@'%' identified by 'mypassword';
grant all on mydatabase.* to [EMAIL PROTECTED] identified by
'mypassword';
grant all on mydatabase.* to [EMAIL PROTECTED] identified by
'mypassword'; [my current IP]
grant all on mydatabase.* to [EMAIL PROTECTED] identified by
'mypassword';
flushed privileges,..etc,...no go,...
Thanks for any other ideas!
Jeff
At 2:09 PM -0600 2/10/05, Jay Blanchard wrote:
[snip]
In the past, I've always simply installed these two pieces as I
mentioned above and everything was so easy,but it's been awhile
since I last did this,did I miss something?
[/snip]
Permissions?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

--

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

_
Want to block unwanted pop-ups? Download the free MSN Toolbar now!
http://tool

Re: global object-id

[Sergei Golubchik]

Hi!

On Feb 10, Konrad Kieling wrote:
> hi,
> is there a simple way (ie without creating and deleting datasets in an 
> extra table) to use an auto_increment id field in several tables. no 
> table has to have all ids, so i cannot use the field of a master table 
> in childs. it seems one can use a sequence in postgresql in different 
> tables. is it possible in a similar way?

Try UUID() function
 
Regards,
Sergei

-- 
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <[EMAIL PROTECTED]>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
   <___/  www.mysql.com

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

Makefile & my entries.

[Mohsen Pahlevanzadeh]

Dears,I want to add mysql's  INCLUDEs & LIBs path to my Makefile.in .
Of course, i have wizarded a project with KDEvelop.It has created 3 Makefile 
with .cvs .am .in extensions.
Please guide me that i can add mysql_config to them.
Yours,Mohsen

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

RE: php conection problems

[Logan, David (SST - Adelaide)]

As I said, I don't know the process on a mac (unless this is os X and
then I can guess) so can't advise on that. The manual at
http://www.php.net/manual/en/install.macosx.php probably has the info
there.

Another reason would be the different release schedules with php and
mysql. They are dropping the built in mysql for version 5.0 of php. That
is documented at the php site.


The php site has all the installation instructions for a Mac and should
be able to guide you through building your own. The mysql site
http://dev.mysql.com/downloads/mysql/4.1.html (this for version 4.1) has
binaries prebuilt for a Mac OS X
 
If you still don't get the info you need for the above pages, try
running

./configure --help at the command prompt. This will give you all the
options available to you including directing the compiler/linker etc. to
the right spot for your mysql client libraries.

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: Jeff Mao [mailto:[EMAIL PROTECTED] 
Sent: Friday, 11 February 2005 7:52 AM
To: Logan, David (SST - Adelaide)
Cc: mysql@lists.mysql.com
Subject: RE: php conection problems

I'm running Mac OS X,...so if the the disable and rebuild isn't too 
hard, can you outline that process?

Jeff

>Probably because they don't have the time to get the latest and
greatest
>client library every time. I'm not sure how to rebuild it on Windows
but
>you can disable the built in on unix and rebuild it linking in the
>latest library.
>
>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: Jeff Mao [mailto:[EMAIL PROTECTED]
>Sent: Friday, 11 February 2005 7:36 AM
>To: mel list_php; mysql@lists.mysql.com
>Subject: RE: php conection problems
>
>That did the trick,...thanks
>
>so,...why is my Apache installation using an old client?  After
>reading your suggestion, it makes more sense when I look at the
>output of phpinfo() which says that mysql is "builtin" and the Client
>API version is 3.23.49,...why?!  I guess it was a bad assumption on
>my part that a fresh install of mysql would include all new
>versions?!  Or is this "builtin" 3.23.49 part of my Apache
>installation that shipped with my laptop?
>
>If anyone can shed some light on how to update that part of it, I'd
>greatly appreciate it!  I'd hate to have to go through these extra
>steps every time!
>
>Jeff
>
>
>At 8:55 PM + 2/10/05, mel list_php wrote:
>>I'm not sure if it can be a password problem as you can connect
>>through command line,but when upgrading to 4.1 I had the classical
>>"access deny" error with phpmyadmin whereas everything else was ok.
>>
>>You can still have a look there:
>>http://dev.mysql.com/doc/mysql/en/old-client.html
>>
>>but I think in that case you should have an explicit error message.
>>(Client does not support authentication protocol).
>>
>>You can still try to update dbUser set password=old_password(***)
>>for one of your user and give it a try.
>>
>>
>>good luck!
>>
>>>From: Jeff Mao <[EMAIL PROTECTED]>
>>>To: Jay Blanchard <[EMAIL PROTECTED]>,
>>>mysql@lists.mysql.com
>>>Subject: RE: php conection problems
>>>Date: Thu, 10 Feb 2005 15:36:19 -0500
>>>
>>>I've tried using a variety of very wide open permissions just to
>>>get something to connect including:
>>>
>>>using the root user/password in the php scripts
>>>
>>>declaring a user in mysql,...
>>>
>>>grant all on mydatabase.* to myuser identified by 'mypassword';
>>>grant all on mydatabase.* to myuser@'%' identified by 'mypassword';
>>>grant all on mydatabase.* to [EMAIL PROTECTED] identified by
>'mypassword';
>>>grant all on mydatabase.* to [EMAIL PROTECTED] identified by
>>>'mypassword'; [my current IP]
>>>grant all on mydatabase.* to [EMAIL PROTECTED] identified by
>'mypassword';
>>>
>>>flushed privileges,..etc,...no go,...
>>>
>>>Thanks for any other ideas!
>>>Jeff
>>>
>>>At 2:09 PM -0600 2/10/05, Jay Blanchard wrote:
[snip]
In the past, I've always simply installed these two pieces as I
mentioned above and everything was so easy,but it's been awhile
since I last did this,did I miss something?
[/snip]

Permissions?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>>>
>>>
>>>--
>>>
>>>Jeff Mao
>>>[EMAIL PROTECTED]
>>>
>>>--
>>>MySQL General Mailing List
>>>For list archives: http://lists.mysql.com/mysql
>>>To unsubscribe:  
>>>http://lists.mysql.com/[EMAIL PROTECTED]
>>>
>>
>>_
>>Want to block unwanted pop-ups? Download the free MSN Toolbar now!
>>http://toolbar.msn.co.uk/
>
>
>--
>**

RE: php conection problems

[Jeff Mao]

I'm running Mac OS X,...so if the the disable and rebuild isn't too 
hard, can you outline that process?

Jeff
Probably because they don't have the time to get the latest and greatest
client library every time. I'm not sure how to rebuild it on Windows but
you can disable the built in on unix and rebuild it linking in the
latest library.
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: Jeff Mao [mailto:[EMAIL PROTECTED]
Sent: Friday, 11 February 2005 7:36 AM
To: mel list_php; mysql@lists.mysql.com
Subject: RE: php conection problems
That did the trick,...thanks
so,...why is my Apache installation using an old client?  After
reading your suggestion, it makes more sense when I look at the
output of phpinfo() which says that mysql is "builtin" and the Client
API version is 3.23.49,...why?!  I guess it was a bad assumption on
my part that a fresh install of mysql would include all new
versions?!  Or is this "builtin" 3.23.49 part of my Apache
installation that shipped with my laptop?
If anyone can shed some light on how to update that part of it, I'd
greatly appreciate it!  I'd hate to have to go through these extra
steps every time!
Jeff
At 8:55 PM + 2/10/05, mel list_php wrote:
I'm not sure if it can be a password problem as you can connect
through command line,but when upgrading to 4.1 I had the classical
"access deny" error with phpmyadmin whereas everything else was ok.
You can still have a look there:
http://dev.mysql.com/doc/mysql/en/old-client.html
but I think in that case you should have an explicit error message.
(Client does not support authentication protocol).
You can still try to update dbUser set password=old_password(***)
for one of your user and give it a try.
good luck!
From: Jeff Mao <[EMAIL PROTECTED]>
To: Jay Blanchard <[EMAIL PROTECTED]>,
mysql@lists.mysql.com
Subject: RE: php conection problems
Date: Thu, 10 Feb 2005 15:36:19 -0500
I've tried using a variety of very wide open permissions just to
get something to connect including:
using the root user/password in the php scripts
declaring a user in mysql,...
grant all on mydatabase.* to myuser identified by 'mypassword';
grant all on mydatabase.* to myuser@'%' identified by 'mypassword';
grant all on mydatabase.* to [EMAIL PROTECTED] identified by
'mypassword';
grant all on mydatabase.* to [EMAIL PROTECTED] identified by
'mypassword'; [my current IP]
grant all on mydatabase.* to [EMAIL PROTECTED] identified by
'mypassword';
flushed privileges,..etc,...no go,...
Thanks for any other ideas!
Jeff
At 2:09 PM -0600 2/10/05, Jay Blanchard wrote:
[snip]
In the past, I've always simply installed these two pieces as I
mentioned above and everything was so easy,but it's been awhile
since I last did this,did I miss something?
[/snip]
Permissions?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

--

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

_
Want to block unwanted pop-ups? Download the free MSN Toolbar now!
http://toolbar.msn.co.uk/

--

Jeff Mao
[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]

--

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

RE: php conection problems

[Logan, David (SST - Adelaide)]

Probably because they don't have the time to get the latest and greatest
client library every time. I'm not sure how to rebuild it on Windows but
you can disable the built in on unix and rebuild it linking in the
latest library.

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: Jeff Mao [mailto:[EMAIL PROTECTED] 
Sent: Friday, 11 February 2005 7:36 AM
To: mel list_php; mysql@lists.mysql.com
Subject: RE: php conection problems

That did the trick,...thanks

so,...why is my Apache installation using an old client?  After 
reading your suggestion, it makes more sense when I look at the 
output of phpinfo() which says that mysql is "builtin" and the Client 
API version is 3.23.49,...why?!  I guess it was a bad assumption on 
my part that a fresh install of mysql would include all new 
versions?!  Or is this "builtin" 3.23.49 part of my Apache 
installation that shipped with my laptop?

If anyone can shed some light on how to update that part of it, I'd 
greatly appreciate it!  I'd hate to have to go through these extra 
steps every time!

Jeff


At 8:55 PM + 2/10/05, mel list_php wrote:
>I'm not sure if it can be a password problem as you can connect 
>through command line,but when upgrading to 4.1 I had the classical 
>"access deny" error with phpmyadmin whereas everything else was ok.
>
>You can still have a look there:
>http://dev.mysql.com/doc/mysql/en/old-client.html
>
>but I think in that case you should have an explicit error message. 
>(Client does not support authentication protocol).
>
>You can still try to update dbUser set password=old_password(***) 
>for one of your user and give it a try.
>
>
>good luck!
>
>>From: Jeff Mao <[EMAIL PROTECTED]>
>>To: Jay Blanchard <[EMAIL PROTECTED]>, 
>>mysql@lists.mysql.com
>>Subject: RE: php conection problems
>>Date: Thu, 10 Feb 2005 15:36:19 -0500
>>
>>I've tried using a variety of very wide open permissions just to 
>>get something to connect including:
>>
>>using the root user/password in the php scripts
>>
>>declaring a user in mysql,...
>>
>>grant all on mydatabase.* to myuser identified by 'mypassword';
>>grant all on mydatabase.* to myuser@'%' identified by 'mypassword';
>>grant all on mydatabase.* to [EMAIL PROTECTED] identified by
'mypassword';
>>grant all on mydatabase.* to [EMAIL PROTECTED] identified by 
>>'mypassword'; [my current IP]
>>grant all on mydatabase.* to [EMAIL PROTECTED] identified by
'mypassword';
>>
>>flushed privileges,..etc,...no go,...
>>
>>Thanks for any other ideas!
>>Jeff
>>
>>At 2:09 PM -0600 2/10/05, Jay Blanchard wrote:
>>>[snip]
>>>In the past, I've always simply installed these two pieces as I
>>>mentioned above and everything was so easy,but it's been awhile
>>>since I last did this,did I miss something?
>>>[/snip]
>>>
>>>Permissions?
>>>
>>>--
>>>MySQL General Mailing List
>>>For list archives: http://lists.mysql.com/mysql
>>>To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>>
>>
>>--
>>
>>Jeff Mao
>>[EMAIL PROTECTED]
>>
>>--
>>MySQL General Mailing List
>>For list archives: http://lists.mysql.com/mysql
>>To unsubscribe:   
>>http://lists.mysql.com/[EMAIL PROTECTED]
>>
>
>_
>Want to block unwanted pop-ups? Download the free MSN Toolbar now! 
>http://toolbar.msn.co.uk/


-- 

Jeff Mao
[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: php conection problems

[Jeff Mao]

That did the trick,...thanks
so,...why is my Apache installation using an old client?  After 
reading your suggestion, it makes more sense when I look at the 
output of phpinfo() which says that mysql is "builtin" and the Client 
API version is 3.23.49,...why?!  I guess it was a bad assumption on 
my part that a fresh install of mysql would include all new 
versions?!  Or is this "builtin" 3.23.49 part of my Apache 
installation that shipped with my laptop?

If anyone can shed some light on how to update that part of it, I'd 
greatly appreciate it!  I'd hate to have to go through these extra 
steps every time!

Jeff
At 8:55 PM + 2/10/05, mel list_php wrote:
I'm not sure if it can be a password problem as you can connect 
through command line,but when upgrading to 4.1 I had the classical 
"access deny" error with phpmyadmin whereas everything else was ok.

You can still have a look there:
http://dev.mysql.com/doc/mysql/en/old-client.html
but I think in that case you should have an explicit error message. 
(Client does not support authentication protocol).

You can still try to update dbUser set password=old_password(***) 
for one of your user and give it a try.

good luck!
From: Jeff Mao <[EMAIL PROTECTED]>
To: Jay Blanchard <[EMAIL PROTECTED]>, 
mysql@lists.mysql.com
Subject: RE: php conection problems
Date: Thu, 10 Feb 2005 15:36:19 -0500

I've tried using a variety of very wide open permissions just to 
get something to connect including:

using the root user/password in the php scripts
declaring a user in mysql,...
grant all on mydatabase.* to myuser identified by 'mypassword';
grant all on mydatabase.* to myuser@'%' identified by 'mypassword';
grant all on mydatabase.* to [EMAIL PROTECTED] identified by 'mypassword';
grant all on mydatabase.* to [EMAIL PROTECTED] identified by 
'mypassword'; [my current IP]
grant all on mydatabase.* to [EMAIL PROTECTED] identified by 'mypassword';

flushed privileges,..etc,...no go,...
Thanks for any other ideas!
Jeff
At 2:09 PM -0600 2/10/05, Jay Blanchard wrote:
[snip]
In the past, I've always simply installed these two pieces as I
mentioned above and everything was so easy,but it's been awhile
since I last did this,did I miss something?
[/snip]
Permissions?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

--

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

_
Want to block unwanted pop-ups? Download the free MSN Toolbar now! 
http://toolbar.msn.co.uk/

--

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

RE: php conection problems

[mel list_php]

I'm not sure if it can be a password problem as you can connect through 
command line,but when upgrading to 4.1 I had the classical "access deny" 
error with phpmyadmin whereas everything else was ok.

You can still have a look there:
http://dev.mysql.com/doc/mysql/en/old-client.html
but I think in that case you should have an explicit error message. (Client 
does not support authentication protocol).

You can still try to update dbUser set password=old_password(***) for one of 
your user and give it a try.

good luck!
From: Jeff Mao <[EMAIL PROTECTED]>
To: Jay Blanchard <[EMAIL PROTECTED]>, 
mysql@lists.mysql.com
Subject: RE: php conection problems
Date: Thu, 10 Feb 2005 15:36:19 -0500

I've tried using a variety of very wide open permissions just to get 
something to connect including:

using the root user/password in the php scripts
declaring a user in mysql,...
grant all on mydatabase.* to myuser identified by 'mypassword';
grant all on mydatabase.* to myuser@'%' identified by 'mypassword';
grant all on mydatabase.* to [EMAIL PROTECTED] identified by 'mypassword';
grant all on mydatabase.* to [EMAIL PROTECTED] identified by 
'mypassword'; [my current IP]
grant all on mydatabase.* to [EMAIL PROTECTED] identified by 'mypassword';

flushed privileges,..etc,...no go,...
Thanks for any other ideas!
Jeff
At 2:09 PM -0600 2/10/05, Jay Blanchard wrote:
[snip]
In the past, I've always simply installed these two pieces as I
mentioned above and everything was so easy,but it's been awhile
since I last did this,did I miss something?
[/snip]
Permissions?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

--

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

_
Want to block unwanted pop-ups? Download the free MSN Toolbar now!  
http://toolbar.msn.co.uk/

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

RE: php conection problems

[Jeff Mao]

I've tried using a variety of very wide open permissions just to get 
something to connect including:

using the root user/password in the php scripts
declaring a user in mysql,...
grant all on mydatabase.* to myuser identified by 'mypassword';
grant all on mydatabase.* to myuser@'%' identified by 'mypassword';
grant all on mydatabase.* to [EMAIL PROTECTED] identified by 'mypassword';
grant all on mydatabase.* to [EMAIL PROTECTED] identified by 
'mypassword'; [my current IP]
grant all on mydatabase.* to [EMAIL PROTECTED] identified by 'mypassword';

flushed privileges,..etc,...no go,...
Thanks for any other ideas!
Jeff
At 2:09 PM -0600 2/10/05, Jay Blanchard wrote:
[snip]
In the past, I've always simply installed these two pieces as I
mentioned above and everything was so easy,but it's been awhile
since I last did this,did I miss something?
[/snip]
Permissions?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

--

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

RE: php conection problems

[Dean, Michael L USAATC]

phpinfo() should tell you whether it is using the MySQL module or not, that
will help you find out if that part is working. 

Michael

-Original Message-
From: Jeff Mao [mailto:[EMAIL PROTECTED] 
Sent: Thursday, 10 February, 2005 15:02
To: mysql@lists.mysql.com
Subject: php conection problems

Hi All,

I'm not sure this a MySQL problem or a PHP problem,...

I just picked up a new laptop, and like I had always done in the past,
installed MySQL and PHP so I could test and play with code locally.

The laptop is a Mac Powerbook running 10.3.8. The MySQL installation is the
Standard 4.1.19 installation from the dev.mysql.com website. 
The php installation is Marc Liyanage's (entropy.ch) package installer for
4.3.10.

php appears to be OK as pulling a page with  yields the
normal output.

MySQL appears fine as I can login and do things Terminal using the mysql
client.

But setting up an of the numerous simple php/mysql web sites, like phpbb or
moodle give me database connection failures,...

In the past, I've always simply installed these two pieces as I mentioned
above and everything was so easy,but it's been awhile since I last did
this,did I miss something?

Thanks
Jeff


--

Jeff Mao
[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: Daily Incremental Backups on Mysql

[Dean, Michael L USAATC]

The option in my.cnf is log-bin = 'logfilename here'

Michael 

-Original Message-
From: Daniel Kasak [mailto:[EMAIL PROTECTED] 
Sent: Thursday, 10 February, 2005 14:58
To: Jerry Swanson; mysql@lists.mysql.com
Subject: Re: Daily Incremental Backups on Mysql

Jerry Swanson wrote:

>Does binary logs are avaialbe in Mysql 4 or only in Mysql 5?
>Thanks
>
>
>
>On Thu, 10 Feb 2005 10:28:56 +1100, Daniel Kasak 
><[EMAIL PROTECTED]> wrote:
>  
>
>>Jerry Swanson wrote:
>>
>>
>>
>>>Is it possible to do "daily incremental backups" on mysql?
>>>
>>>
>>>Thanks
>>>
>>>  
>>>
MySQL 4.x supports binary logging.
I think you have to use an 'enable logging' ( --enable-logging ... I think )
switch on the command-line, or set an option in your my.cnf.

Dan

--
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: php conection problems

[Jay Blanchard]

[snip]
In the past, I've always simply installed these two pieces as I 
mentioned above and everything was so easy,but it's been awhile 
since I last did this,did I miss something?
[/snip]

Permissions?

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

php conection problems

[Jeff Mao]

Hi All,
I'm not sure this a MySQL problem or a PHP problem,...
I just picked up a new laptop, and like I had always done in the 
past, installed MySQL and PHP so I could test and play with code 
locally.

The laptop is a Mac Powerbook running 10.3.8. The MySQL installation 
is the Standard 4.1.19 installation from the dev.mysql.com website. 
The php installation is Marc Liyanage's (entropy.ch) package 
installer for 4.3.10.

php appears to be OK as pulling a page with  
yields the normal output.

MySQL appears fine as I can login and do things Terminal using the 
mysql client.

But setting up an of the numerous simple php/mysql web sites, like 
phpbb or moodle give me database connection failures,...

In the past, I've always simply installed these two pieces as I 
mentioned above and everything was so easy,but it's been awhile 
since I last did this,did I miss something?

Thanks
Jeff
--

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

Re: Daily Incremental Backups on Mysql

[Daniel Kasak]

Jerry Swanson wrote:
Does binary logs are avaialbe in Mysql 4 or only in Mysql 5?
Thanks 


On Thu, 10 Feb 2005 10:28:56 +1100, Daniel Kasak
<[EMAIL PROTECTED]> wrote:
 

Jerry Swanson wrote:
   

Is it possible to do "daily incremental backups" on mysql?
Thanks
 

MySQL 4.x supports binary logging.
I think you have to use an 'enable logging' ( --enable-logging ... I 
think ) switch on the command-line, or set an option in your my.cnf.

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

Re: MySQL as Email database [sort of OT]

[Brent Baisley]

Sorry, should have been a little clearer on that point. I will be using 
Postfix as the mail server. So I will be retrieving email from the SMTP 
server via POP3 or IMAP. I'm not really looking for help on the 
database structure, I've looked over the dbmail structure. I would want 
to retrieve all the header information, I would store the headers in a 
separate table so I could purge it periodically.
Any suggestions would be appreciated.

On Feb 10, 2005, at 12:35 PM, [EMAIL PROTECTED] wrote:
Brent Baisley <[EMAIL PROTECTED]> wrote on 02/10/2005 12:21:38 PM:
I am looking to store all incoming emails into a MySQL database. I've
looked into dbmail, but can't get it to compile under Mac OSX (I 
posted
a message on that list). I was wondering if anyone could point me in
another direction to use MySQL as an email message store. I don't need
a webmail interface, just a way of getting messages from a mail server
to a MySQL database. Preferably as a direct transfer, but it could be 
a
script that runs periodically.
Currently it seems the best path is using Perl, but I would think this
has been done before, just can't find it on google.

Thanks
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
How to do what you ask depends on how I read your question.  When you 
say
"store incoming emails" does that mean that you are writing an SMTP 
server
that will act as a mail destination or are you retrieving mail from an
SMTP server via POP3 or IMAP or some other mail retrieval protocol?

In either case your code ends up writing the contents of the message 
to a
MySQL database rather than to a local file. What you need to decide is 
how
many pieces you want to break the message into (how much metadata you 
want
to extract from each message). Your database structure will depend on 
your
information needs and how you intend to use the emails once you store 
them
however I can almost guarantee that the body of each message will need 
to
be stored in a TEXT field.

You will probably run out of room faster by storing whole messages in 
your
database than if you were storing them as files on the hard drive.
Capacity control is definitely something you want to consider in your
project design. You may want to store the messages (or parts of them) 
as
files and keep just the metadata in the database, but again that goes 
to
your intended purpose for this project.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL as Email database [sort of OT]

[Rhino]

One of my friends is using "James" for this purpose - it uses MySQL as a
backend - and he is very happy with it.

You can find out more at http://james.apache.org/.

Rhino

- Original Message - 
From: "Brent Baisley" <[EMAIL PROTECTED]>
To: 
Sent: Thursday, February 10, 2005 12:21 PM
Subject: MySQL as Email database [sort of OT]


> I am looking to store all incoming emails into a MySQL database. I've
> looked into dbmail, but can't get it to compile under Mac OSX (I posted
> a message on that list). I was wondering if anyone could point me in
> another direction to use MySQL as an email message store. I don't need
> a webmail interface, just a way of getting messages from a mail server
> to a MySQL database. Preferably as a direct transfer, but it could be a
> script that runs periodically.
> Currently it seems the best path is using Perl, but I would think this
> has been done before, just can't find it on google.
>
> Thanks
> -- 
> Brent Baisley
> Systems Architect
> Landover Associates, Inc.
> Search & Advisory Services for Advanced Technology Environments
> p: 212.759.6400/800.759.0577
>
>
> -- 
> 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: Aes Encryption

[love]

Has any body implemented Aes encryption while storing critical data in 
mysql? I want to know the logic you are implementing to store your 
passwords to encrypt/decrypt data.

Love Kumar
Love Kumar wrote:
Hi,
 Aes Encryption requires a password (key) to access data, now where do
we store this key? with the source code ? or in seperate database ?
because any body who has the access to souce code can view the critical
info or even if you store passwords in another database then it is not a
big deal to get those passwords if source code is accessible.
What i think Aes encryption reduce the risk but does not make data 100%
safe because of source code dependency.
Love Kumar
Perl Develpment Team
eBookers.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]

The information transmitted is intended only for the person or entity 
to whom it is addressed and may contain confidential and / or 
privileged Material. Any review, re-transmission, dissemination or 
other use of or taking of any action in reliance upon, this 
information by persons or entities other than the intended recipient 
is prohibited. If you received this in error, please contact the 
sender and delete the material from your computer. Thank you for your 
understanding & co-operation.

RE: Error 2002

[Dean, Michael L USAATC]

I will try to explain what I have done:

The previous version of MySQL had some different directory
structure.  The .pid and .sock files were placed in /var/run/mysqld/, so
instead of changing this after the upgrade I attempted to change my
/etc/my.cnf file to reflect this.  There was a [client] group in that file
which I pointed the socket to /var/run/mysqld/mysqld.sock.  For some reason
this would only work with root (in retrospect, perphaps the "my.cnf" file is
not readable to regular users, hence the skip -- just checked, that is the
case).  I took out those lines and let it put the .sock and .pid files in
the default locations and all is working fine now.

Michael 

-Original Message-
From: Nestor Florez [mailto:[EMAIL PROTECTED] 
Sent: Thursday, 10 February, 2005 12:14
To: Dean,Michael L USAATC
Subject: Re: Error 2002

I was getting that on my Mac OSX and I had to start mysql

Nestor :-)

-Original Message-
From: "Dean, Michael L USAATC" <[EMAIL PROTECTED]>
Sent: Feb 10, 2005 9:02 AM
To: "'mysql@lists.mysql.com'" 
Subject: Error 2002

I have upgraded my Debian box from MySQL version 3.23 to version 4.19.
Everything works perfectly fine except on the local box.  Therefore I'm sure
it has something to do with sockets.  When I am logged in as root I can
connect to the server fine, when I am logged in as a user I get this error
messsage: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (111)
 
My /tmp directory has these permissions:
drwxrwxrwt5 root root 4096 Feb 10 11:46 tmp/
and mysql.sock has these permissions:
srwxrwxrwx1 mysqlmysql   0 Feb  9 11:36 mysql.sock=

 
I'm not quite sure what is going on, it seems like a permissions issue, but
permissions seem to be ok.
 
Michael



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

Re: MySQL as Email database [sort of OT]

[SGreen]

Brent Baisley <[EMAIL PROTECTED]> wrote on 02/10/2005 12:21:38 PM:

> I am looking to store all incoming emails into a MySQL database. I've 
> looked into dbmail, but can't get it to compile under Mac OSX (I posted 
> a message on that list). I was wondering if anyone could point me in 
> another direction to use MySQL as an email message store. I don't need 
> a webmail interface, just a way of getting messages from a mail server 
> to a MySQL database. Preferably as a direct transfer, but it could be a 
> script that runs periodically.
> Currently it seems the best path is using Perl, but I would think this 
> has been done before, just can't find it on google.
> 
> Thanks
> -- 
> Brent Baisley
> Systems Architect
> Landover Associates, Inc.
> Search & Advisory Services for Advanced Technology Environments
> p: 212.759.6400/800.759.0577
> 
> 
> -- 

How to do what you ask depends on how I read your question.  When you say 
"store incoming emails" does that mean that you are writing an SMTP server 
that will act as a mail destination or are you retrieving mail from an 
SMTP server via POP3 or IMAP or some other mail retrieval protocol? 

In either case your code ends up writing the contents of the message to a 
MySQL database rather than to a local file. What you need to decide is how 
many pieces you want to break the message into (how much metadata you want 
to extract from each message). Your database structure will depend on your 
information needs and how you intend to use the emails once you store them 
however I can almost guarantee that the body of each message will need to 
be stored in a TEXT field.

You will probably run out of room faster by storing whole messages in your 
database than if you were storing them as files on the hard drive. 
Capacity control is definitely something you want to consider in your 
project design. You may want to store the messages (or parts of them) as 
files and keep just the metadata in the database, but again that goes to 
your intended purpose for this project.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

MySQL as Email database [sort of OT]

[Brent Baisley]

I am looking to store all incoming emails into a MySQL database. I've 
looked into dbmail, but can't get it to compile under Mac OSX (I posted 
a message on that list). I was wondering if anyone could point me in 
another direction to use MySQL as an email message store. I don't need 
a webmail interface, just a way of getting messages from a mail server 
to a MySQL database. Preferably as a direct transfer, but it could be a 
script that runs periodically.
Currently it seems the best path is using Perl, but I would think this 
has been done before, just can't find it on google.

Thanks
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Error 2002

[Dean, Michael L USAATC]

I have upgraded my Debian box from MySQL version 3.23 to version 4.19.
Everything works perfectly fine except on the local box.  Therefore I'm sure
it has something to do with sockets.  When I am logged in as root I can
connect to the server fine, when I am logged in as a user I get this error
messsage: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (111)
 
My /tmp directory has these permissions:
drwxrwxrwt5 root root 4096 Feb 10 11:46 tmp/
and mysql.sock has these permissions:
srwxrwxrwx1 mysqlmysql   0 Feb  9 11:36 mysql.sock=

 
I'm not quite sure what is going on, it seems like a permissions issue, but
permissions seem to be ok.
 
Michael

RE: Importing Tables on Top of Tables

[Tom Crimmins]

> -Original Message-
> From: David Blomstrom 
> Sent: Thursday, February 10, 2005 10:06
> To: mysql@lists.mysql.com
> Subject: Importing Tables on Top of Tables
> 
> If I revise a MySQL table and try to publish it
> online, I often get the error message, "Table my_table
> already exists." So I have to delete the online
> version before I can import the revised version.

This error comes from issuing a CREATE TABLE when a table with that name
already exists.

> Is there a way to just publish one table over another,
> as long as both have the same name?

There are two ways to do this. Issue a DROP TABLE IF EXISTS my_table, before
the CREATE TABLE my_table, or you can issue TRUNCATE TABLE my_table and
ditch the CREATE TABLE statement.

> 
> Thanks.
> 

---
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa

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

Re: slow query, how can i imporve it?

[SGreen]

See below

Shailendra Soni <[EMAIL PROTECTED]> wrote on 02/10/2005 01:43:18 
AM:

> Thank ,
> But i can't create multipal index it will not useful for my tabels.
> 
> I tryed to set GLOBAL keycache1.key_buffer_size = 128*1024
> 
> but it gives error that "unknown system varible ' keycache1' ".
> can you tell me that is this useful for my problem? and
> if yes how it is work? and how can i solve this error.
> 
> Thanks again
> reply soon 
> 
> Regards:
> Shailendra
> 

I do not recognize that command either. Where did you find it and how was 
it related to improving query performance?

This situation is analogous to you saying to me "My car is slow, how do I 
make it go faster?". I know nothing about your table structures, your 
indexes, your query, or the issue itself (exactly how slow is it? how fast 
would you like it to be?). If you really need help with a query, please 
respond with all of the following information:

1) The text of the actual query
2) The results of an EXPLAIN on that query
3) The results of SHOW CREATE TABLE x\G for each table used in the 
query.
4) A description of why this query is not meeting your needs and what 
needs you would like it to meet.

Once I have all of that background information, either I or someone else 
on the list will be able to help you with this issue. Do not forget to CC: 
the list with your responses.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

> 
> 
> On Wed, 9 Feb 2005 10:02:49 -0500, [EMAIL PROTECTED] <[EMAIL PROTECTED]
> com> wrote:
> > 
> > 
> > Shailendra Soni <[EMAIL PROTECTED]> wrote on 02/09/2005 
08:28:36
> > AM:
> > 
> > 
> > > Hi,
> > > 
> > > I have a question regarding speed of the query.
> > > In my application i am useing Mysql 4.0.20a-nt.
> > > I have 10 tables and each table contains 400 records
> > > and also 61 columns. I already created indexs on six column which 
are
> > > important for me.
> > > 
> > > i fired the query on tables through servlet(thread).
> > > I fired same query on all tables on same time, but it has take time 
to
> > > getting result . allmost
> > > 7 to 10 minute .
> > > 
> > > so please tell me 
> > > how can i imporve speed of the Mysql or query?
> > > 
> > > so it will take less time !
> > > 
> > > Thanks 
> > > Shailendra
> > > 
> > 
> > Have you tried looking at this for ideas, too? 
> > http://dev.mysql.com/doc/mysql/en/query-speed.html 
> > 
> > Most of us start with an EXPLAIN of the query and work from there (see
> > suggested reading). Check your table structures and, if the frequency 
of
> > this query justifies it, an appropriate multi-column index (not 
multiple
> > single-column indexes). 
> > 
> > Shawn Green
> > Database Administrator
> > Unimin Corporation - Spruce Pine

Importing Tables on Top of Tables

[David Blomstrom]

If I revise a MySQL table and try to publish it
online, I often get the error message, "Table my_table
already exists." So I have to delete the online
version before I can import the revised version.

Is there a way to just publish one table over another,
as long as both have the same name?

Thanks.



__ 
Do you Yahoo!? 
Yahoo! Mail - Helps protect you from nasty viruses. 
http://promotions.yahoo.com/new_mail

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

RE: Need a New Password & Username

[David Blomstrom]

--- Tom Crimmins <[EMAIL PROTECTED]> wrote:

> It's all there in the manual.

I like your explanation a lot better; it works, too!
:)

Thanks.




__ 
Do you Yahoo!? 
All your favorites on one personal page – Try My Yahoo!
http://my.yahoo.com 

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

Re: global object-id

[Martijn Tonies]

> is there a simple way (ie without creating and deleting datasets in an
> extra table) to use an auto_increment id field in several tables. no
> table has to have all ids, so i cannot use the field of a master table
> in childs. it seems one can use a sequence in postgresql in different
> tables. is it possible in a similar way?

"sequences" are separate objects used to generated, guess what,
sequential numbers. The actual usage of the numbers is undefined.

Auto-inc is auto-inc. Plain and simple. It's something related to
a column in a particular table. Each table can have it's own
"instance" of 1 (and only 1) auto-incrementing column.

Short answer: no.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
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]

global object-id

[Konrad Kieling]

hi,
is there a simple way (ie without creating and deleting datasets in an 
extra table) to use an auto_increment id field in several tables. no 
table has to have all ids, so i cannot use the field of a master table 
in childs. it seems one can use a sequence in postgresql in different 
tables. is it possible in a similar way?

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

RE: Need a New Password & Username

[Tom Crimmins]

> -Original Message-
> From: David Blomstrom
> Sent: Thursday, February 10, 2005 09:21
> To: mysql@lists.mysql.com
> Subject: RE: Need a New Password & Username
> 
> Wow, I don't remember dealing with all that code the
> first time around. Can someone show me EXACTLY what I
> would write if my database is named my_database, and I
> want to add the username private_host and the password
> superstar? And if I don't have mysqladmin, can I type
> it into the SQL Query window on phpMyAdmin?

GRANT ALL ON my_database.* TO 'private_host' IDENTIFIED BY 'superstar';

You can add host restrictions to by adding @'hostname' after the username.
It's all there in the manual. Anyway, I haven't used phpMyAdmin, but if you
can just enter queries, which I assume you can, then this will work.

> 
> Thanks.

---
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa

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

RE: Need a New Password & Username

[David Blomstrom]

Wow, I don't remember dealing with all that code the
first time around. Can someone show me EXACTLY what I
would write if my database is named my_database, and I
want to add the username private_host and the password
superstar? And if I don't have mysqladmin, can I type
it into the SQL Query window on phpMyAdmin?

Thanks.




__ 
Do you Yahoo!? 
Meet the all-new My Yahoo! - Try it today! 
http://my.yahoo.com 
 


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

Re: disable multiple statements -- how??

[Mark Matthews]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Dan wrote:
> I've searched the documentation and Google but can't find the answer.
> 
> How do I disable the multiple statements processing in v4.1.x?
> 
> I don't want the user to be able to send more than one statement (like it 
> was in 4.0.x).
> 
> ie:  SELECT * FROM table; DROP TABLE table;
> 
> would generate a SQL error.
> 
> I would imagine there's a server-wide variable that would disable this, as 
> well as possibly a per-connection variable of some sort.  MySQL is running 
> under Win2K and clients connect via the JDBC driver.
> 
> 

Dan,

JDBC doesn't enable multi-statements by default, you have to pass it as
a configuration parameter.

-Mark

- --
Mark Matthews
MySQL AB, Software Development Manager - Client Connectivity
www.mysql.com

MySQL User Conference (Santa Clara CA, 18-21 April 2005)
Early registration until February 28: http://www.mysqluc.com/
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.6 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFCC3mNtvXNTca6JD8RAp9JAJ9SMMX8vZV6Mlifxw7cnir55nb3ywCfeIB1
Po2RXdHt0sFp6aWnhRzgrdE=
=WXa4
-END PGP SIGNATURE-

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

RE: Need a New Password & Username

[Tom Crimmins]

> -Original Message-
> From: David Blomstrom 
> Sent: Thursday, February 10, 2005 08:52
> To: mysql@lists.mysql.com
> Subject: Need a New Password & Username
> 
> I just recreated a database after my computer crashed.
> All my MyISAM tables survived, but my InnoDB tables
> vanished. The other problem is that I need to reassign
> a username and password, and I can't remember how I
> did that originally.

http://dev.mysql.com/doc/mysql/en/grant.html

> 
> I now have the latest version of phpMyAdmin, but I
> can't find any reference to username or password.
> There are instructions for assigning passwords in
> MYSQLADMIN at 
> http://dev.mysql.com/doc/mysql/en/passwords.html
> 
> Would these same instructions work in phpMyAdmin...I'd
> just click "Query" and type the commands in the window
> under "Run SQL query/queries on database XXX_ZZZ:"?
> 
> As I recall, "localhost" is the standard ROOT name and
> is presumably already assigned. I've assigned a
> database name, so I just need to reassign my username
> and password.
> 
> Thanks.

---
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa

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

Re: No mysqld pid file found !!! Please help...

[Chuck Herrick]

Read this:
  http://www.whoopis.com/howtos/php5-mysql4-FC3-rpm.html

Quoting Helena Carlsson <[EMAIL PROTECTED]>:

> Hi all,
>
> On a Fedora Core 3 machine which releases with MySQL
> 3.23 I downloaded all necessary 4.1 RPMs and
> installed, however the MySQL 3.23 existed, but it
> didn't work. Also, all 4.1 RPMs had uninstalled
> automatically MySQL 3.23 and had re-installed itself.
> Then I removed all new installed 4.1 RPMs by using:
>
> rpm -e MySQL-server-...
>
> The only package which didn't removed was
> MySQL-shared-compat. Then I re-installed all 4.1 RPMs
> and still it doesn't work. At the shell when I try to
> restart MySQL daemon as below the error message says:
>
> /etc/init.d/mysql restart
> No mysqld pid file found. Looked for
> /var/lib/mysql/localhost.localdomain.pid.
>
> I am totally confused. Whats the problem ??
>
> Regards,
>
> Helena
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>


--
Chuck Herrick
mailto:[EMAIL PROTECTED]
512 289 0926 (cell)
830 839 4437 (home)

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

RE: Daily Incremental Backups on Mysql

[Tom Crimmins]

> -Original Message-
> From: Jerry Swanson
> Sent: Thursday, February 10, 2005 08:33
> To: Daniel Kasak
> Cc: mysql@lists.mysql.com
> Subject: Re: Daily Incremental Backups on Mysql
> 
> Does binary logs are avaialbe in Mysql 4 or only in Mysql 5?
> Thanks 
> 

Binary logs are available in version 4. There are differences in format
between the versions. See the following for detail.
http://dev.mysql.com/doc/mysql/en/binary-log.html

> 
> 
> On Thu, 10 Feb 2005 10:28:56 +1100, Daniel Kasak
> <[EMAIL PROTECTED]> wrote:
> > Jerry Swanson wrote:
> > 
> > >Is it possible to do "daily incremental backups" on mysql?
> > >
> > >
> > >Thanks
> > >
> > >
> > >
> > Yes.
> > mysqldump will give you a starting point, and the binary transaction
> > logs give you your incremental backups - copy these 
> somewhere each day -
> > onto a backup tape or something. Read the documentation for 
> mysqldump
> > and for processing the binary transaction logs.
> > 
> > --
> > Daniel Kasak
> > IT Developer
> > NUS Consulting Group
> > Level 5, 77 Pacific Highway
> > North Sydney, NSW, Australia 2060
> > T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
> > email: [EMAIL PROTECTED]
> > website: http://www.nusconsulting.com.au
> > 

---
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa

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

No mysqld pid file found !!! Please help...

[Helena Carlsson]

Hi all,

On a Fedora Core 3 machine which releases with MySQL
3.23 I downloaded all necessary 4.1 RPMs and
installed, however the MySQL 3.23 existed, but it
didn't work. Also, all 4.1 RPMs had uninstalled
automatically MySQL 3.23 and had re-installed itself.
Then I removed all new installed 4.1 RPMs by using:

rpm -e MySQL-server-...

The only package which didn't removed was
MySQL-shared-compat. Then I re-installed all 4.1 RPMs
and still it doesn't work. At the shell when I try to
restart MySQL daemon as below the error message says:

/etc/init.d/mysql restart
No mysqld pid file found. Looked for
/var/lib/mysql/localhost.localdomain.pid.

I am totally confused. Whats the problem ??

Regards,

Helena




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

Need a New Password & Username

[David Blomstrom]

I just recreated a database after my computer crashed.
All my MyISAM tables survived, but my InnoDB tables
vanished. The other problem is that I need to reassign
a username and password, and I can't remember how I
did that originally.

I now have the latest version of phpMyAdmin, but I
can't find any reference to username or password.
There are instructions for assigning passwords in
MYSQLADMIN at 
http://dev.mysql.com/doc/mysql/en/passwords.html

Would these same instructions work in phpMyAdmin...I'd
just click "Query" and type the commands in the window
under "Run SQL query/queries on database XXX_ZZZ:"?

As I recall, "localhost" is the standard ROOT name and
is presumably already assigned. I've assigned a
database name, so I just need to reassign my username
and password.

Thanks.



__ 
Do you Yahoo!? 
Take Yahoo! Mail with you! Get it on your mobile phone. 
http://mobile.yahoo.com/maildemo 

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

Re: Daily Incremental Backups on Mysql

[Jerry Swanson]

Does binary logs are avaialbe in Mysql 4 or only in Mysql 5?
Thanks 



On Thu, 10 Feb 2005 10:28:56 +1100, Daniel Kasak
<[EMAIL PROTECTED]> wrote:
> Jerry Swanson wrote:
> 
> >Is it possible to do "daily incremental backups" on mysql?
> >
> >
> >Thanks
> >
> >
> >
> Yes.
> mysqldump will give you a starting point, and the binary transaction
> logs give you your incremental backups - copy these somewhere each day -
> onto a backup tape or something. Read the documentation for mysqldump
> and for processing the binary transaction logs.
> 
> --
> Daniel Kasak
> IT Developer
> NUS Consulting Group
> Level 5, 77 Pacific Highway
> North Sydney, NSW, Australia 2060
> T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
> email: [EMAIL PROTECTED]
> website: http://www.nusconsulting.com.au
> 
> --
> 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: aborting slipped keys

[Tom Crimmins]

> -Original Message-
> From: Scott Haneda
> Sent: Thursday, February 10, 2005 04:40
> To: MySql
> Subject: aborting slipped keys
> 
> In the myslq monitor, in my shell, sometimes I mess up and 
> slip a few keys,
> and mysql is just stuck, waiting on more input, and nothing I 
> tell it, other
> than a control-C gets me out, at which point I have to login 
> again, which is
> a time killer.  How do you get out of a stuck mysql?

By stuck, do you mean that you need to cancel the query? If so \c will do
the trick.

> Sometimes, and I can not figrue out what triggers it, but I 
> like it, I get
> Reading table information for completion of table and column names
> You can turn off this feature to get a quicker startup with -A
> 
> auto completion is very nice, how do I make sure it is on all 
> the time?
> 
> thanks
> -- 
> -
> Scott HanedaTel: 415.898.2602
>  Fax: 313.557.5052
> <[EMAIL PROTECTED]>  Novato, CA U.S.A.

---
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa
office 712.328.4808
mobile 402.677.1592 

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

disable multiple statements -- how??

[Dan]

I've searched the documentation and Google but can't find the answer.
How do I disable the multiple statements processing in v4.1.x?
I don't want the user to be able to send more than one statement (like it 
was in 4.0.x).

ie:  SELECT * FROM table; DROP TABLE table;
would generate a SQL error.
I would imagine there's a server-wide variable that would disable this, as 
well as possibly a per-connection variable of some sort.  MySQL is running 
under Win2K and clients connect via the JDBC driver.

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

Re: Locked myself out of the mysql database!

[f f]

hi,

1. i have same problem.

SPEC :
winxp sp2 + recent security bug patch.
mysql 4.1.9.

the docs :
http://dev.mysql.com/doc/mysql/en/Resetting_permissions.html

just doesnt't worked.


At the DOS command prompt, execute this command: 
C:\> C:\mysql\bin\mysqld-nt --skip-grant-tables


mysql\bin is on my PATH. 
so i use mysqld-nt --skip-grant-tables.

it stops automatically.

i go to mmc and to services panel. and open mysql41
services properties. in commands there is :
mysqld-nt --default-files="path_to_ini_\mysql.ini"
MYSQL41

so i try

mysqld-nt --default-files="path_to_ini_\mysql.ini"
--skip-grant-tables(without any service name).

it runs. however when i execute 
mysqladmin -u root flush-privileges password "newpwd"

mysql complains that it can't find any entry on user
table.

any idea ?

thanks.

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

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

Re: Experience with MySQL 5

[Martijn Tonies]

> Does anyone has experience with MySQL 5.x?
>
> How reliable is it?

On Windows: currently, not at all.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
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]

Re: Filtering non-ascii characters from mysql data, null, tab etc

[zzapper]

On Thu, 10 Feb 2005 10:17:00 +,  wrote:

>Hi,
>I've successfully used the following update-replace statement to replace 
>strings in mysql data
>
>update tbl_county_lookup set countyname=replace(countyname,'&','and') ;
>
>However I've had problems trying to replace a null character 0x00h , 
>
>I'd be interested to know the syntax to filter null characters.
>
>Secondly I'd be interested in a general filter for non-ascii.
>
>cheers
Can't believe I'm the only one who's ever had this problem, I've googled and 
just found a few fellow
searchers!!

(I will probably have to dump the db and use a perl script!)

zzapper (vim, cygwin, wiki & zsh)
--

vim -c ":%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg?"

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]

Re: AW: Slow Replication

[Alec . Cawley]

"Hannes Rohde" <[EMAIL PROTECTED]> wrote on 10/02/2005 11:44:13:

>I don't think we are dealing with an IO bottleneck here because the
> slave server should quite faster with writings to the disc at least 
since we
> are using Raid 0 here. Or is there any way which could explain an IO
> bottleneck even though the slave is not running as many selects as the
> master is? In this case we are talking about one replicated database on 
a
> dedicated slave system.

As I understand the previous posts, the problem is that the replication 
process is single-threaded while the updates on the original master are 
multi-threaded.

On the original server, if Update 1 stalls because it has to fetch data of 
disk, Update 2 can proceed. If Update 2 stalls, Update 3 can proceed - and 
so on. This means firstly that Updates which can take advantage of the 
cache take no effective time - they come in, do their job, and exit while 
peer updates are stalled in Disk wait. This also means that lower-level 
software can optimise disk performance by re-ordering IO operations to 
minimise head movements. In my experience, having up to 4 parallel streams 
of disk operations, and allowing the disk to pick its preferred order of 
execution, usually adds about 50% to disk performance and can double it.

However, when they are replicated to the slave server, the updates are put 
into a strictly First In, First out queue. If Update 1 stalls, Update 2 
cannot be started - and nor can Update 3. When Update 3 does finally 
start, it cannot overlap the others, so that the time it takes, albeit 
small because it does not access disk, is added on to the other times 
rather than included within them. And since you are performing strictly 
one operation at a time (on the Updates side at least) Raid 0 does not 
help you, because there are no overlapping reads to get from alternate 
disks.

Alec Cawley


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

RE: Upgrading from 3.23.57 to 4.1.9 using mysqldump

[Kevin Cowley]

Modify the insert statement so that you name the column you're inserting
to.

INSERT INTO `db` (put the column names in here)
VALUES('localhost','dbxxx','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y'),.
..

Kevin Cowley
R&D
 
Tel: 0118 902 9099 (direct line)
Email: [EMAIL PROTECTED]
Web: http://www.alchemetrics.co.uk

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: 10 February 2005 12:08
> To: mysql@lists.mysql.com
> Subject: Upgrading from 3.23.57 to 4.1.9 using mysqldump
> 
> Hi,
> 
> I just tried to upgrade from mysql 3.23.57 (SPARC/Solaris) to 4.1.9
> (i386/Linux) using mysqldump but when importing the dump on the new
> machine mysql gives the error:
> 
> ERROR 1136 (21S01) at line NNN: Column count doesn't match value count
at
> row 1
> 
> and in line NNN you find
> 
> INSERT INTO `db` VALUES
> ('localhost','dbxxx','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y'),...
> 
> so he is right. The table 'db' has more entries in 4.1.9 than in
3.23.57!
> But what is the correct way to upgrade? (NO! It is not possible for me
to
> upgrade directly on the 3.23.57 machine!)
> 
> MANY thanx in advance,
>   Frank
> 
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]



**
ALCHEMETRICS LIMITED (ALCHEMETRICS)
Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX
Tel:  +44 (0) 118 902 9000Fax:  +44 (0) 118 902 9001
This e-mail is confidential and is intended for the use of the addressee only.
If you are not the intended recipient, you are hereby notified that you must 
not use, copy, disclose, otherwise disseminate or take any action based on 
this e-mail or any information herein.
If you receive this transmission in error, please notify the sender
immediately by reply e-mail or by using the contact details above and then
delete this e-mail.
Please note that e-mail may be susceptible to data corruption, interception 
and unauthorised amendment.  Alchemetrics does not accept any liability for 
any such corruption, interception, amendment or the consequences thereof.
**


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

Upgrading from 3.23.57 to 4.1.9 using mysqldump

[user1dw43a]

Hi,

I just tried to upgrade from mysql 3.23.57 (SPARC/Solaris) to 4.1.9 
(i386/Linux) using mysqldump but when importing the dump on the new machine 
mysql gives the error:

ERROR 1136 (21S01) at line NNN: Column count doesn't match value count at row 1

and in line NNN you find

INSERT INTO `db` VALUES 
('localhost','dbxxx','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y'),...

so he is right. The table 'db' has more entries in 4.1.9 than in 3.23.57! But 
what is the correct way to upgrade? (NO! It is not possible for me to upgrade 
directly on the 3.23.57 machine!)

MANY thanx in advance,
  Frank



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

Full-text search performance issues

[Homam S.A.]

Since MySQL stores RowIDs with the indexed words
instead of the table's primary key IDs, and since it
uses only one index per table in any query, performing
a full-text search on a large table (several million
rows) and joining it with another large table proves
to be extremely slow!

The bottleneck seems to be the disk, as MySQL
generates an insane number of disk reads while trying
to join the two tables without using common indexes
between the two.

Basically I have two tables, A and B. These tables
cannot be merged, so there's no way around using a
join. One of these tables, A, has a TEXT column that
is full-text indexed. Now these tables are normally
joined using the indexes on their primary keys. But if
use a full-text criteria in the WHERE clause against
A, then MySQL will NOT use the primary key index on A
to perform the join. It instead seems to get the
result from the full-text index and uses it to perform
a huge number of random (unsorted) lookups on the
second table to perform the join.

I tried to perform a separate query on each table and
store the results (primary key IDs) in a couple of
temp tables, then join these tables manually. But
these tables can be huge, and doing this for each web
paging request is very inefficient. In addition,
limiting the size of these temp tables would render
the search dysfunctional because it won't be
encompassing any more.

With non-full-text indexes, you can just merge the
indexes into a composite index to work around the
single-index-per-table limitation. But you can't merge
a regular primary key index and a full-text index into
a composite index, and I have no idea why MySQL
developers didn't just use the primary key IDs with
the indexed words, as in MS SQL Server full-text
index, instead of the physical row IDs. May be it's a
MyISAM limitation that an index, no matter what type
it is, has to use physical row IDs as table pointers
instead of primary keys.

Is there any way around this limitation? I'd like to
know if someone has been in a such a situation and how
he or she solved it.

I appreciate your help!




__ 
Do you Yahoo!? 
Meet the all-new My Yahoo! - Try it today! 
http://my.yahoo.com 
 


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

AW: Slow Replication

[Hannes Rohde]

I don't think we are dealing with an IO bottleneck here because the
slave server should quite faster with writings to the disc at least since we
are using Raid 0 here. Or is there any way which could explain an IO
bottleneck even though the slave is not running as many selects as the
master is? In this case we are talking about one replicated database on a
dedicated slave system.

Thanks,

Hannes

-Ursprüngliche Nachricht-
Von: Mechain Marc [mailto:[EMAIL PROTECTED] 
Gesendet: Donnerstag, 10. Februar 2005 12:04
An: Hannes Rohde; Marc Slemko
Cc: mysql@lists.mysql.com
Betreff: RE: Slow Replication

I think he is talking about the "innodb_flush_log_at_trx_commit" parameter.
Try to put it at a value of 0.

innodb_flush_log_at_trx_commit = 0

If you have an IO bottleneck, this may help.

Marc.



-Message d'origine-
De : Hannes Rohde [mailto:[EMAIL PROTECTED]
Envoyé : jeudi 10 février 2005 11:46
À : 'Marc Slemko'
Cc : mysql@lists.mysql.com
Objet : AW: Slow Replication


I don't quite get what you mean with the second paragraph. Do you
mean increasing the thread concurrency to 6 or something like that? I have
already put it on 4 because we do have HT active on the cpu. On the other it
is just a single processor P IV system. On other hand I think it wouldn't
speed up the SQL thread on the slave a whole lot. It would be excellent if
you could run two or more SQL threads on the slave you priories them
somehow.

Anyway I will try your last paragraph's suggestion. 

Thanks,

Hannes


-Ursprüngliche Nachricht-
Von: Marc Slemko [mailto:[EMAIL PROTECTED] 
Gesendet: Donnerstag, 10. Februar 2005 11:24
An: Hannes Rohde
Cc: mysql@lists.mysql.com
Betreff: Re: Slow Replication

On Wed, 9 Feb 2005 22:07:19 +0100, Hannes Rohde <[EMAIL PROTECTED]> wrote:
> Hi all,
> 
> We use MySQL as a database backend on a portal site. We have a two
> database server setup (one master, one slave). The master is a PIV 3,2
GHz.,
> 2 GB Ram and a 80GB Raid-1 system. The slave is a PIV 3.2 GHz., 4 GB Ram
and
> a 80GB Raid-0 system. Both run on MySQL 4.1.9 and only use InnoDB. Even
> though the slave is a bigger system and is quite fast with selects, it
> always falls behind in replication (Seconds behind the server keeps
growing
> at high-load times).
> Is there any way to speed up the replication a little more? I have already
> tried a whole lot of things but have never been successful, yet :-(

That can be problematic since innodb allows much higher concurrency
than myisam, although you can still have this issue with myisam.

What you have to realize is that due to how mysql replication works,
every transaction needs to be serialized.  The slave is only running a
single statement at once.  So if you have multiple CPUs on the server,
or multiple disks that can't be saturated by a single concurrent
operation ... then multiple simultaneous operations can get better
performance on the server than you can get in replication to the
client.

If most of your stuff is innodb, then setting the innodb option to not
sync to disk on every transaction may speed things up a lot ... if you
don't care about your data.  But, then again, I don't think mysql
replication is actually fully transactional yet anyway.



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




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

Re: How to specify autoincrement primary key value

[Martijn Tonies]

> > > One simple question... AFAIK I can specify value for an autoincrement
> > > primary key (int) when inserting a record like this:
> > >
> > > INSERT INTO `tablename` (`id`, `name`) VALUES (1, 'test')
> > >
> > > But it doesn't work for id = 0. Why?
> > >
> > > I would like to use some primary key values for special purpose, e.g.
id
> > 0
> > > means root/default record and so on.
> > > Is that recommended? Are there any alternative ways?
> >
> > Autoincrement starts from 1. That is the way it is, and cannot, I think,
> > be changed.
>
> Yes, it starts from 1, but concrete value CAN be changed to 0 manually.
>
> Maybe it is better to ask: "Why can I set autoincrement primary key to 0
but
> cannot do this using INSERT statement?"
>
> >
> > I would advise against using autoincrement keys for "special" uses. This
> > is muddling two different functions into one. Remeber that primary keys
> > must be unique: if, at some time, you need to have more than one of a
> > "special" value, you will be in trouble.
>
> Sure, but the idea was to use 0 for root category or "root" access. It
seems
> to be reasonable at least.

Then again... this is only an non-business-meaning ID value. If it's 0
or 100 or 1293492342 -> it doesn't matter at all.

> > The function of autoincrement
> > keys is to assign unique record identifiers. Do not mix this with other
> > tasks.

The meaning of auto-inc values is to make people lazy.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
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]

RE: How to specify autoincrement primary key value

[Denis Gerasimov]

> 
> >
> > Hello,
> >
> > One simple question... AFAIK I can specify value for an autoincrement
> > primary key (int) when inserting a record like this:
> >
> > INSERT INTO `tablename` (`id`, `name`) VALUES (1, 'test')
> >
> > But it doesn't work for id = 0. Why?
> >
> > I would like to use some primary key values for special purpose, e.g. id
> 0
> > means root/default record and so on.
> > Is that recommended? Are there any alternative ways?
> 
> Autoincrement starts from 1. That is the way it is, and cannot, I think,
> be changed.

Yes, it starts from 1, but concrete value CAN be changed to 0 manually.

Maybe it is better to ask: "Why can I set autoincrement primary key to 0 but
cannot do this using INSERT statement?"

> 
> I would advise against using autoincrement keys for "special" uses. This
> is muddling two different functions into one. Remeber that primary keys
> must be unique: if, at some time, you need to have more than one of a
> "special" value, you will be in trouble.

Sure, but the idea was to use 0 for root category or "root" access. It seems
to be reasonable at least.

> The function of autoincrement
> keys is to assign unique record identifiers. Do not mix this with other
> tasks.
> 
> Alec


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

Re: Copying Database from One Hard Drive to Another

[love]

Well if you copy my.cnf settings along with ibdata files and log files 
then it is possible to copy innodb exactly on the different machine and 
i have done this before worked fine for me.

Love ..
[EMAIL PROTECTED] wrote:
InnoDB files can not be copied from one machine to another like the
MyISAM files. If you did not dump the data or you do not have an
archived backup you will more than likely have to recreate the table
structures.
David Blomstrom wrote:
>Yikes...I've been replying to myself! I forgot that
>when I reply to messages from the MySQL newsgroup on
>this e-mail account, it doesn't include
>mysql@lists.mysql.com, for some odd reason.
>
>At any rate, I wanted to say that I have just ONE file
>type left - FRM. My MYD and MYI files have both
>vanished.
>
>But I took another look and discovered that SOME
>tables are represented by all three files, and when I
>paste them into my new MySQL > Data folder, they work
>just fine.
>
>I also discovered the following files in my backup
>folder:
>
>ibdata1...215,040 KB
>ib_logfile1...5,120 KB
>ib_logfile0...5,120 KB
>ib_arch_log_0...25 KB
>
>Can these somehow be used to reconstitute lost
>database tables? The situation isn't critical, as I
>can recreate most of these tables from my original
>data, which is on spreadsheets. But recreating all the
>fields and keys would obviously be a pain in the butt;
>I had over 100 tables.
>
>Thanks.
>
>
>
>  
>__
>Do you Yahoo!?
>Yahoo! Mail - Helps protect you from nasty viruses.
>http://promotions.yahoo.com/new_mail
>
> 
>

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

The information transmitted is intended only for the person or entity 
to whom it is addressed and may contain confidential and / or 
privileged Material. Any review, re-transmission, dissemination or 
other use of or taking of any action in reliance upon, this 
information by persons or entities other than the intended recipient 
is prohibited. If you received this in error, please contact the 
sender and delete the material from your computer. Thank you for your 
understanding & co-operation.

Re: How to specify autoincrement primary key value

[Alec . Cawley]

"Denis Gerasimov" <[EMAIL PROTECTED]> wrote on 10/02/2005 10:59:11:

> 
> Hello,
> 
> One simple question... AFAIK I can specify value for an autoincrement
> primary key (int) when inserting a record like this:
> 
> INSERT INTO `tablename` (`id`, `name`) VALUES (1, 'test')
> 
> But it doesn't work for id = 0. Why?
> 
> I would like to use some primary key values for special purpose, e.g. id 
0
> means root/default record and so on.
> Is that recommended? Are there any alternative ways?

Autoincrement starts from 1. That is the way it is, and cannot, I think, 
be changed.

I would advise against using autoincrement keys for "special" uses. This 
is muddling two different functions into one. Remeber that primary keys 
must be unique: if, at some time, you need to have more than one of a 
"special" value, you will be in trouble. The function of autoincrement 
keys is to assign unique record identifiers. Do not mix this with other 
tasks.

Alec


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

RE: Slow Replication

[Mechain Marc]

I think he is talking about the "innodb_flush_log_at_trx_commit" parameter.
Try to put it at a value of 0.

innodb_flush_log_at_trx_commit = 0

If you have an IO bottleneck, this may help.

Marc.



-Message d'origine-
De : Hannes Rohde [mailto:[EMAIL PROTECTED]
Envoyé : jeudi 10 février 2005 11:46
À : 'Marc Slemko'
Cc : mysql@lists.mysql.com
Objet : AW: Slow Replication


I don't quite get what you mean with the second paragraph. Do you
mean increasing the thread concurrency to 6 or something like that? I have
already put it on 4 because we do have HT active on the cpu. On the other it
is just a single processor P IV system. On other hand I think it wouldn't
speed up the SQL thread on the slave a whole lot. It would be excellent if
you could run two or more SQL threads on the slave you priories them
somehow.

Anyway I will try your last paragraph's suggestion. 

Thanks,

Hannes


-Ursprüngliche Nachricht-
Von: Marc Slemko [mailto:[EMAIL PROTECTED] 
Gesendet: Donnerstag, 10. Februar 2005 11:24
An: Hannes Rohde
Cc: mysql@lists.mysql.com
Betreff: Re: Slow Replication

On Wed, 9 Feb 2005 22:07:19 +0100, Hannes Rohde <[EMAIL PROTECTED]> wrote:
> Hi all,
> 
> We use MySQL as a database backend on a portal site. We have a two
> database server setup (one master, one slave). The master is a PIV 3,2
GHz.,
> 2 GB Ram and a 80GB Raid-1 system. The slave is a PIV 3.2 GHz., 4 GB Ram
and
> a 80GB Raid-0 system. Both run on MySQL 4.1.9 and only use InnoDB. Even
> though the slave is a bigger system and is quite fast with selects, it
> always falls behind in replication (Seconds behind the server keeps
growing
> at high-load times).
> Is there any way to speed up the replication a little more? I have already
> tried a whole lot of things but have never been successful, yet :-(

That can be problematic since innodb allows much higher concurrency
than myisam, although you can still have this issue with myisam.

What you have to realize is that due to how mysql replication works,
every transaction needs to be serialized.  The slave is only running a
single statement at once.  So if you have multiple CPUs on the server,
or multiple disks that can't be saturated by a single concurrent
operation ... then multiple simultaneous operations can get better
performance on the server than you can get in replication to the
client.

If most of your stuff is innodb, then setting the innodb option to not
sync to disk on every transaction may speed things up a lot ... if you
don't care about your data.  But, then again, I don't think mysql
replication is actually fully transactional yet anyway.



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


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

Re: How to log some queries and not others

[George Chelidze]

Dear Paul,
Paul DuBois wrote:
At 19:52 +0300 2/9/05, George Chelidze wrote:
Hello,
I have the following problem: I have a table with a blob field and 
turned on query logging. I'd like to log every query except inserts 
into table with a blob field because my log files grow very fast and I 
don't like to see binary data in my log files, while other logs are 
very informative and important. How can I do that?

See:
http://dev.mysql.com/doc/mysql/en/set-option.html
Check the descriptions for SQL_LOG_BIN and SQL_LOG_OFF.
SQL_LOG_OFF did it. Thank you very much for your fast help.
Best Regards,
--
George Chelidze
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

How to specify autoincrement primary key value

[Denis Gerasimov]

Hello,

One simple question... AFAIK I can specify value for an autoincrement
primary key (int) when inserting a record like this:

INSERT INTO `tablename` (`id`, `name`) VALUES (1, 'test')

But it doesn't work for id = 0. Why?

I would like to use some primary key values for special purpose, e.g. id 0
means root/default record and so on.
Is that recommended? Are there any alternative ways?

Best regards, Denis Gerasimov
Outsourcing Services Manager,
VEKOS, Ltd.
www.vekos.ru




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

AW: Slow Replication

[Hannes Rohde]

I don't quite get what you mean with the second paragraph. Do you
mean increasing the thread concurrency to 6 or something like that? I have
already put it on 4 because we do have HT active on the cpu. On the other it
is just a single processor P IV system. On other hand I think it wouldn't
speed up the SQL thread on the slave a whole lot. It would be excellent if
you could run two or more SQL threads on the slave you priories them
somehow.

Anyway I will try your last paragraph's suggestion. 

Thanks,

Hannes


-Ursprüngliche Nachricht-
Von: Marc Slemko [mailto:[EMAIL PROTECTED] 
Gesendet: Donnerstag, 10. Februar 2005 11:24
An: Hannes Rohde
Cc: mysql@lists.mysql.com
Betreff: Re: Slow Replication

On Wed, 9 Feb 2005 22:07:19 +0100, Hannes Rohde <[EMAIL PROTECTED]> wrote:
> Hi all,
> 
> We use MySQL as a database backend on a portal site. We have a two
> database server setup (one master, one slave). The master is a PIV 3,2
GHz.,
> 2 GB Ram and a 80GB Raid-1 system. The slave is a PIV 3.2 GHz., 4 GB Ram
and
> a 80GB Raid-0 system. Both run on MySQL 4.1.9 and only use InnoDB. Even
> though the slave is a bigger system and is quite fast with selects, it
> always falls behind in replication (Seconds behind the server keeps
growing
> at high-load times).
> Is there any way to speed up the replication a little more? I have already
> tried a whole lot of things but have never been successful, yet :-(

That can be problematic since innodb allows much higher concurrency
than myisam, although you can still have this issue with myisam.

What you have to realize is that due to how mysql replication works,
every transaction needs to be serialized.  The slave is only running a
single statement at once.  So if you have multiple CPUs on the server,
or multiple disks that can't be saturated by a single concurrent
operation ... then multiple simultaneous operations can get better
performance on the server than you can get in replication to the
client.

If most of your stuff is innodb, then setting the innodb option to not
sync to disk on every transaction may speed things up a lot ... if you
don't care about your data.  But, then again, I don't think mysql
replication is actually fully transactional yet anyway.



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

aborting slipped keys

[Scott Haneda]

In the myslq monitor, in my shell, sometimes I mess up and slip a few keys,
and mysql is just stuck, waiting on more input, and nothing I tell it, other
than a control-C gets me out, at which point I have to login again, which is
a time killer.  How do you get out of a stuck mysql?

Sometimes, and I can not figrue out what triggers it, but I like it, I get
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

auto completion is very nice, how do I make sure it is on all the time?

thanks
-- 
-
Scott HanedaTel: 415.898.2602
 Fax: 313.557.5052
<[EMAIL PROTECTED]>  Novato, CA U.S.A.



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

Re: Slow Replication

[Marc Slemko]

On Wed, 9 Feb 2005 22:07:19 +0100, Hannes Rohde <[EMAIL PROTECTED]> wrote:
> Hi all,
> 
> We use MySQL as a database backend on a portal site. We have a two
> database server setup (one master, one slave). The master is a PIV 3,2 GHz.,
> 2 GB Ram and a 80GB Raid-1 system. The slave is a PIV 3.2 GHz., 4 GB Ram and
> a 80GB Raid-0 system. Both run on MySQL 4.1.9 and only use InnoDB. Even
> though the slave is a bigger system and is quite fast with selects, it
> always falls behind in replication (Seconds behind the server keeps growing
> at high-load times).
> Is there any way to speed up the replication a little more? I have already
> tried a whole lot of things but have never been successful, yet :-(

Your config settings suggest you are using innodb.

That can be problematic since innodb allows much higher concurrency
than myisam, although you can still have this issue with myisam.

What you have to realize is that due to how mysql replication works,
every transaction needs to be serialized.  The slave is only running a
single statement at once.  So if you have multiple CPUs on the server,
or multiple disks that can't be saturated by a single concurrent
operation ... then multiple simultaneous operations can get better
performance on the server than you can get in replication to the
client.

If most of your stuff is innodb, then setting the innodb option to not
sync to disk on every transaction may speed things up a lot ... if you
don't care about your data.  But, then again, I don't think mysql
replication is actually fully transactional yet anyway.

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

Re: Memory limit?

[Marc Slemko]

On Thu, 10 Feb 2005 10:19:32 +0900, Batara Kesuma
<[EMAIL PROTECTED]> wrote:
> Hi Tobias,
> 
> On Wed, 9 Feb 2005 14:48:16 +0100 (CET)
> Tobias Asplund <[EMAIL PROTECTED]> wrote:
> 
> > > I try to install MySQL 4.1.9 (official RPM from mysql.com). My machine
> > > is running linux 2.6.9, and it has 4GB of RAM. The problem is MySQL
> > > won't start if I set innodb_buffer_pool_size to >= 2GB. Here is my
> > > ulimit.
> >
> > Are you trying this on a 32-bit cpu machine?
> 
> Sorry I forgot to mention. Yes, it is a 32-bit CPU machine.

Yup, most Linux glibc's limit a malloc() to 2 gigs in a misplaced(?)
attempt to avoid errors due to signed/unsigned conversions.  Since
innodb just uses malloc() for things, getting above two gigs doesn't
work.

(the details can be a lot more complicated, ie. needing a kernel with
a 4G/4G split, changing the base address mmap()ed regions start at,
etc.)

I don't think it would be hard at all to change innodb to let you use
a 3-4 gig buffer pool on a 32 bit Linux box, but I've never had the
time to look into it that deeply.

It is unfortunate.

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

Filtering non-ascii characters from mysql data, null, tab etc

[zzapper]

Hi,
I've successfully used the following update-replace statement to replace 
strings in mysql data

update tbl_county_lookup set countyname=replace(countyname,'&','and') ;

However I've had problems trying to replace a null character 0x00h , 

I'd be interested to know the syntax to filter null characters.

Secondly I'd be interested in a general filter for non-ascii.

cheers


zzapper (vim, cygwin, wiki & zsh)
--

vim -c ":%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg?"

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]