MySQL 5.0 & character sets

[BÁRTHÁZI András]

Hi,

I'm new on this list, so a very short intro about me: I'm Andras Barthazi, 
a Hungarian web developer. I like MySQL very much, I'm using it since 3.x 
versions, so I think I know it very well. But...


So, I started learning, how MySQL 5.0 handles character sets. It is, what 
I think about it:


character_set_client | latin1

This is, what the client thinks about itself, determined from the 
environment. It will be used for autoconverting character data sent from 
the client.


character_set_connection | latin1

This is, what the client thinks about the server, but I'm not sure, how it 
will be determined, and what exactly is it for. As the documentation says, 
it will be used for autoconverting character data sent to the server.


character_set_database   | latin1

It's the general database default character set, used when you create a 
table. No more additional meaning. ?


character_set_results| latin1

It is, what the client thinks about itself, and will be used for 
converting character data coming from the server.


character_set_server | latin1

Pass.

character_set_system | utf8

Pass.


Am I right? Am I miss something? I think the documentation is not clear 
enough in this area.


And another strange thing I found: you can set the table's 
(default) character encoding, but I see no effect. The table data - of 
course - won't be converted (that's another alter table command), the 
results I got won't be changed - so I don't know, what exactly default 
table character set is good for.


Can you point me to the documentation, or can you explain your 
experiments?


Thanks,
   Andras

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

descending index

[Massimo Petrini]

When this limit will be removed ?

from 5.1 manual (13.1.4)
"An index_col_name specification can end with ASC or DESC. These keywords are 
allowed for future extensions for specifying ascending or descending index 
value storage. Currently they are parsed but ignored; index values are always 
stored in ascending order."

Many thanks

---
Massimo Petrini
c/o Omt spa
Via Ferrero 67/a
10090 Cascine Vica (TO)
Tel.+39 011 9505334
Fax +39 011 9575474
E-mail  [EMAIL PROTECTED]

Re: Possible to use a conditional in this UPDATE

[Jasper Bryant-Greene]

On Thu, 2005-10-27 at 21:22 -0700, Scott Haneda wrote:
> on 10/27/05 6:34 PM, Scott Haneda at [EMAIL PROTECTED] wrote:
> 
> > Right now, my update works as follows, and works fine, however, I want to do
> > a condition to say if products.ship_status = 1 then set
> > cart_test.shipping_status to 'now' else set it to 'soon'
> > 
> > UPDATE cart_test, products
> > SET cart_test.shipping_status = products.ship_status
> > WHERE products.id = cart_test.product_id AND cart_test.session_id = '5511'
> > 
> > Is this even possible, if so, can someone help me out a bit.
> > Thanks
> 
> I think I partly got it:
> UPDATE cart, products
> SET cart.ship_status = IF(products.ship_status = 1, 'now', 'soon')
> WHERE products.id = cart.product_id AND
> cart.session_id = "5511"
> 
> However, I need a ELSE in there, I can not always assume other than '1' is
> 'soon', is there some way to add in a ELSE?

Depends how many alternate branches you want. You could just nest
another IF where 'soon' is.

-- 
Jasper Bryant-Greene
General Manager
Album Limited

e: [EMAIL PROTECTED]
w: http://www.album.co.nz/
b: http://jbg.name/
p: 0800 4 ALBUM (0800 425 286) or +64 21 232 3303
a: PO Box 579, Christchurch 8015, New Zealand


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

Re: Possible to use a conditional in this UPDATE

[Scott Haneda]

on 10/27/05 6:34 PM, Scott Haneda at [EMAIL PROTECTED] wrote:

> Right now, my update works as follows, and works fine, however, I want to do
> a condition to say if products.ship_status = 1 then set
> cart_test.shipping_status to 'now' else set it to 'soon'
> 
> UPDATE cart_test, products
> SET cart_test.shipping_status = products.ship_status
> WHERE products.id = cart_test.product_id AND cart_test.session_id = '5511'
> 
> Is this even possible, if so, can someone help me out a bit.
> Thanks

I think I partly got it:
UPDATE cart, products
SET cart.ship_status = IF(products.ship_status = 1, 'now', 'soon')
WHERE products.id = cart.product_id AND
cart.session_id = "5511"

However, I need a ELSE in there, I can not always assume other than '1' is
'soon', is there some way to add in a ELSE?
-- 
-
Scott HanedaTel: 415.898.2602
 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: {Spam?} MySQL and dates puzzle

[ddevaudreuil]

I think there are four cases to consider (hopefully the "picture" will 
come through okay).
 
starttime|--|endtime   The time span in consideration
 *-*  Case 1:  ta has mtg  that 
starts before starttime, mtg ends between starttime and endtime 1
   *--*  Case 2:  mtg starts before 
starttime, mtg ends after endtime 2 
*-*   Case 3:  mtg starts and mtg 
ends between startime and endtime 3
 *---*Case 4:  mtg starts between 
starttime and endtime, mtg ends after endtime 4

However the four can be reduced to two:

a.  A  meeting starts before the starttime and ends after the starttime 
(cases 1 and 2)
or
b.  A meeting starts between the starttime and the endtime (cases 3 and 
4).


So to check if the TA is busy between 17:30 and 18:10

SELECT count(TAID) as total 
FROM tbl_schedule 
where TAID = 1
and ((starts <'2005-10-27 17:30' and ends >'2005-10-27 17:30')
or (starts >=  '2005-10-27 17:30' and starts < '2005-10-27 18:10'))

Regards,
Donna




"C.F. Scheidecker Antunes" <[EMAIL PROTECTED]> 
10/27/2005 05:31 PM

To
mysql@lists.mysql.com
cc

Subject
{Spam?} MySQL and dates puzzle






Hello all,

I have a TA table to record TA UNAVAILABLE times.
This table is quite simple. It has a TAID number, a start date and an 
end date.

tbl_schedule {
TAID  integer,
starts  datetime,
ends datetime }

A valid entry would be a TA whose id is 1 and between 17:00 and 18:00 he 
is busy.
So:
1,'2005-10-27 17:00:00','2005-10-27 18:00:00'

Each ta can have more than one entry per day. He might be a busy TA and 
have a lot
of meetings scheduled. The meetings do not have to be 1 hour length, 
they can be 5 or 10 minutes.
So something like this would also be valid:
1,'2005-10-27 17:05:00','2005-10-27 17:10:00'

Now, I need to check, given a start and end dates, if that would overlap 
with some record already present
in the database.
If I want to know if the TA is busy between 17:30 and 18:10 I could I 
issue something like this:

SELECT count(TAID) as total FROM tbl_schedule where (TAID = 1) AND 
(('2005-10-27 17:30' BETWEEN starts AND ends) OR ('2005-10-27 18:10' 
BETWEEN starts AND ends))

It would return a number not zero as total if the dates are between the 
registered database. However, this does not work properly.
Here's a case when it does not work:
Say I want to check between 14:00 and 20:00. The TA is busy from 17:00 
and 18:00, hence if I try to schedule a meeting that will go
from 14:00 to 20:00 with the statement above it would return 0 as total. 
This is not good because I need to know that given 14:00 to 20:00 that 
would
not overlap with any previous engagement on the database. Since the TA 
is busy from 17:00 to 18:00 I must know that I cannot schedule anything 
like
that.

Can anyone help me on this issue? How can I check given a start and end 
datetime that it does not overlap with what is in the database?

Thanks,

C.F.


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


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.



CONFIDENTIALITY NOTICE:This email is intended solely for the person or 
entity to which it is addressed and may contain confidential and/or 
protected health information.  Any duplication, dissemination, action 
taken in reliance upon, or other use of this information by persons or 
entities other than the intended recipient is prohibited and may violate 
applicable laws.  If this email has been received in error, please notify 
the sender and delete the information from your system.  The views 
expressed in this email are those of the sender and may not necessarily 
represent the views of IntelliCare.

Possible to use a conditional in this UPDATE

[Scott Haneda]

My table products has ship_status INT, where it is either 1 or 0.  I have
been moving those style fields to enum('now', 'soon') because it makes more
sense to me to be able to see the real values.

The problem is this is a live DB, and I am working on a dev site against the
live db, as much as I want to change the ship_status INTO to an enum, it
requires too many changes in application code to do so.

I added a field to cart_test called shipping_status, which I want to make a
enum field.

Right now, my update works as follows, and works fine, however, I want to do
a condition to say if products.ship_status = 1 then set
cart_test.shipping_status to 'now' else set it to 'soon'

UPDATE cart_test, products
SET cart_test.shipping_status = products.ship_status
WHERE products.id = cart_test.product_id AND cart_test.session_id = '5511'

Is this even possible, if so, can someone help me out a bit.
Thanks
-- 
-
Scott HanedaTel: 415.898.2602
 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: MySQL and dates puzzle

[Michael McFadden]

Hi C.F.

I'm new to the list, so please excuse me if I'm
answering out-of-turn from the pro's here.

I think the answer is to also check if the
BusyTime_start is between start and end of the
attempted scheduled.  

ie:
WHERE
(TAID = 1) AND 
(('2005-10-27 17:30' BETWEEN starts AND ends) OR
('2005-10-27 18:10' BETWEEN starts AND ends) OR
(starts BETWEEN '2005-10-27 17:30' AND '2005-10-27
18:10))

I think you only need to check starts (see case 5
below)  because the other case is picked up by the
first two checks. (see case 6 below)

The way I see it, you have 6 cases, but some
simplification can be done, as I have noted:

1) attempted schedule time lies outside of busy times
(return 0 = NOT BUSY)
2) attempted schedule end time lies between busy times
(return !0 = busy)
3) attempted schedule begin time lies between busy
times (return !0 = busy)
4) attempted schedule begin AND end time lie between
busy times (return !0 = busy)
   [this case is a special case of #2 and #3, so it
really disappears!]
5) Busy Time begin time lies between attempted
schedule begin and end times (the problem)
6) Busy Time end time lies between attempted schedule
begin and end times (the problem)

You must check either case 5 or 6 to be sure to catch
the 'attempted schedule wraps busy schedule' case.  If
not, you obviously see the problem.

I think that only case 5 or 6 needs to be checked (not
both) because if one of those is not true, then  case
1, 2, 3 [or 4] (the only one's left!) must be true.

Hope that helps, and wasn't too confusing! 

-Mike McFadden

--- "C.F. Scheidecker Antunes" <[EMAIL PROTECTED]>
wrote:

> Hello all,
> 
> I have a TA table to record TA UNAVAILABLE times.
> This table is quite simple. It has a TAID number, a
> start date and an 
> end date.
> 
> tbl_schedule {
> TAID  integer,
> starts  datetime,
> ends datetime }
> 
> A valid entry would be a TA whose id is 1 and
> between 17:00 and 18:00 he 
> is busy.
> So:
> 1,'2005-10-27 17:00:00','2005-10-27 18:00:00'
> 
> Each ta can have more than one entry per day. He
> might be a busy TA and 
> have a lot
> of meetings scheduled. The meetings do not have to
> be 1 hour length, 
> they can be 5 or 10 minutes.
> So something like this would also be valid:
> 1,'2005-10-27 17:05:00','2005-10-27 17:10:00'
> 
> Now, I need to check, given a start and end dates,
> if that would overlap 
> with some record already present
> in the database.
> If I want to know if the TA is busy between 17:30
> and 18:10 I could I 
> issue something like this:
> 
> SELECT count(TAID) as total FROM tbl_schedule where
> (TAID = 1) AND 
> (('2005-10-27 17:30' BETWEEN starts AND ends) OR
> ('2005-10-27 18:10' 
> BETWEEN starts AND ends))
> 
> It would return a number not zero as total if the
> dates are between the 
> registered database. However, this does not work
> properly.
> Here's a case when it does not work:
> Say I want to check between 14:00 and 20:00. The TA
> is busy from 17:00 
> and 18:00, hence if I try to schedule a meeting that
> will go
> from 14:00 to 20:00 with the statement above it
> would return 0 as total. 
> This is not good because I need to know that given
> 14:00 to 20:00 that would
> not overlap with any previous engagement on the
> database. Since the TA 
> is busy from 17:00 to 18:00 I must know that I
> cannot schedule anything like
> that.
> 
> Can anyone help me on this issue? How can I check
> given a start and end 
> datetime that it does not overlap with what is in
> the database?
> 
> Thanks,
> 
> C.F.




__ 
Start your day with Yahoo! - Make it your home page! 
http://www.yahoo.com/r/hs

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

Re: Mysql privs via PHP? Problems - * Now with error message

[Jasper Bryant-Greene]

On Thu, 2005-10-27 at 13:05 -0500, T.J. Mahaffey wrote:
> Does this have something to do with the OLD_PASSWORD issue in MySQL  
> 5? (I didn't think OSXServer 10.4 included MySQL v.5... )
> 
> My setup:
> Entropy PHP 4.3.11
> MacOS X Server 10.4
> 10.4's default MySQL install

The following command:

mysqld -V

will tell you the version of your MySQL server.

-- 
Jasper Bryant-Greene
General Manager
Album Limited

e: [EMAIL PROTECTED]
w: http://www.album.co.nz/
b: http://jbg.name/
p: 0800 4 ALBUM (0800 425 286) or +64 21 232 3303
a: PO Box 579, Christchurch 8015, New Zealand


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

MySQL and dates puzzle

[C.F. Scheidecker Antunes]

Hello all,

I have a TA table to record TA UNAVAILABLE times.
This table is quite simple. It has a TAID number, a start date and an 
end date.


tbl_schedule {
TAID  integer,
starts  datetime,
ends datetime }

A valid entry would be a TA whose id is 1 and between 17:00 and 18:00 he 
is busy.

So:
1,'2005-10-27 17:00:00','2005-10-27 18:00:00'

Each ta can have more than one entry per day. He might be a busy TA and 
have a lot
of meetings scheduled. The meetings do not have to be 1 hour length, 
they can be 5 or 10 minutes.

So something like this would also be valid:
1,'2005-10-27 17:05:00','2005-10-27 17:10:00'

Now, I need to check, given a start and end dates, if that would overlap 
with some record already present

in the database.
If I want to know if the TA is busy between 17:30 and 18:10 I could I 
issue something like this:


SELECT count(TAID) as total FROM tbl_schedule where (TAID = 1) AND 
(('2005-10-27 17:30' BETWEEN starts AND ends) OR ('2005-10-27 18:10' 
BETWEEN starts AND ends))


It would return a number not zero as total if the dates are between the 
registered database. However, this does not work properly.

Here's a case when it does not work:
Say I want to check between 14:00 and 20:00. The TA is busy from 17:00 
and 18:00, hence if I try to schedule a meeting that will go
from 14:00 to 20:00 with the statement above it would return 0 as total. 
This is not good because I need to know that given 14:00 to 20:00 that would
not overlap with any previous engagement on the database. Since the TA 
is busy from 17:00 to 18:00 I must know that I cannot schedule anything like

that.

Can anyone help me on this issue? How can I check given a start and end 
datetime that it does not overlap with what is in the database?


Thanks,

C.F.


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

Re: does mysql 4.0.25 standard support utf-8?

[Jeff Smelser]

On Thursday 27 October 2005 03:51 pm, [EMAIL PROTECTED] wrote:
> hy! I've been trying to find this piece of information in the manual, but
> seem to have some trouble. so I'm asking it out loud: I'd like to know
> whether mysql 4.0.25 supports utf-8 as an internal encoding. Because a
> server I like has this version and I need to deploy an international
> application, so I really need to store data in utf-8.
> please help if you know. thank you!

No, You need 4.1 and above for that.

Jeff


pgpp1CF3G0AeG.pgp
Description: PGP signature

mysqld crashes and restarts on connect attempt

[Don Doumakes]

I'm installing MySQL 4.1.14 on a new Gentoo box.  When I try to connect 
to the mysqld daemon, it crashes and restarts itself, which just doesn't 
seem right.  I enter these commands:


cd /usr; /usr/bin/mysqld_safe &
mysqladmin -u root password 'foobar'

and get this response:

mysqladmin: connect to server at 'localhost' failed
error: 'Lost connection to MySQL server during query'
Number of processes running now: 0
051027 08:09:52 mysqld restarted

mysqld.err contains this:

InnoDB: !! UNIV_DEBUG switched on !!!
InnoDB: The first specified data file /var/lib/mysql/ibdata1 did not exist:
InnoDB: a new database to be created!
051027 8:09:23 InnoDB: Setting file /var/lib/mysql/ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
051027 8:09:23 InnoDB: Log file /var/lib/mysql/ib_logfile0 did not 
exist: new to be created

InnoDB: Setting log file /var/lib/mysql/ib_logfile0 size to 8 MB
InnoDB: Database physically writes the file full: wait...
051027 8:09:24 InnoDB: Log file /var/lib/mysql/ib_logfile1 did not 
exist: new to be created

InnoDB: Setting log file /var/lib/mysql/ib_logfile1 size to 8 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
051027 8:09:35 InnoDB: Started; log sequence number 0 0
/usr/sbin/mysqld: ready for connections.
Version: '4.1.14-debug-log' socket: '/var/run/mysqld/mysqld.sock' port: 
3306 Gentoo Linux mysql-4.1.14

mysqld got signal 4;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help 
diagnose
the problem, but since we have already crashed, something is definitely 
wrong

and this may fail.

key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=1
max_connections=100
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections 
= 233980 K

bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x8c8ecd8
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xb27db898, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x81455c1
0xb7dcbe55
0x814b1ed
0x8155ebf
0x815688a
0xb7dc613d
0xb7c361ba
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and 
follow instructions on how to resolve the stack trace. Resolved

stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at (nil) is invalid pointer
thd->thread_id=1
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.
InnoDB: !! UNIV_DEBUG switched on !!!
051027 8:09:53 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
051027 8:09:53 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 43634.
InnoDB: Doing recovery: scanned up to log sequence number 0 43634
051027 8:09:53 InnoDB: Flushing modified pages from the buffer pool...
051027 8:09:56 InnoDB: Started; log sequence number 0 43634
/usr/sbin/mysqld: ready for connections.
Version: '4.1.14-debug-log' socket: '/var/run/mysqld/mysqld.sock' port: 
3306 Gentoo Linux mysql-4.1.14


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

Re: Bug? Set Null Value in NOT NULL field...

[LMS]

Jeff Smelser escribió:

On Wednesday 26 October 2005 04:24 pm, LMS wrote:


Hi,
I have this structure:
---
CREATE TABLE tabla (
  id int(10) unsigned NOT NULL auto_increment,
  nombre varchar(100) NOT NULL default '',



because your defaulting it to ''.. so null = '' on insert..

Jeff


Ok thanks for your answer, but I can't set any other property, and I 
need that it gives back an error, because it would have thus to be.


Marcelo

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

does mysql 4.0.25 standard support utf-8?

[mihai.chira]

hy! I've been trying to find this piece of information in the manual, but 
seem to have some trouble. so I'm asking it out loud: I'd like to know 
whether mysql 4.0.25 supports utf-8 as an internal encoding. Because a 
server I like has this version and I need to deploy an international 
application, so I really need to store data in utf-8.
please help if you know. thank you! 



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

Re: Mysql privs via PHP? Problems - * Now with error message

[Hassan Schroeder]

T.J. Mahaffey wrote:

> "Client does not support authentication protocol requested by server; 
> consider upgrading MySQL client"
> 
> Does this have something to do with the OLD_PASSWORD issue in MySQL  5?

The password format changed between MySQL 4.0 and 4.1. I have no
idea what:

> 10.4's default MySQL install

:: is, but:

> Entropy PHP 4.3.11

:: obviously this version of PHP is compiled against old libraries;
time to recompile or find a newer binary :-)

HTH,
-- 
Hassan Schroeder - [EMAIL PROTECTED]
Webtuitive Design ===  (+1) 408-938-0567   === http://webtuitive.com

  dream.  code.



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

Re: comparing two tables

[sheeri kritzer]

Oops, you are correct.  I missed a "not" in the original post.

-Sheeri

On 10/27/05, Brent Baisley <[EMAIL PROTECTED]> wrote:
> You would want to do a left join and only keep the null values.
>
> SELECT tableA.*
> FROM tableA
> LEFT JOIN tableB ON tableA.recordID=tableB.recordID
> WHERE tableB.recordID IS NULL
>
> On Oct 27, 2005, at 6:35 AM, Christopher Molnar wrote:
>
> > If I have two similar tables, with identical columns how would I
> > select rows that are not duplicated between both tables? Any easy
> > way to do this?
> >
> > Thanks,
> > -Chris
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/mysql?
> > [EMAIL PROTECTED]
> >
> >
> >
>
> --
> 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: Bug? Set Null Value in NOT NULL field...

[Jeff Smelser]

On Wednesday 26 October 2005 04:24 pm, LMS wrote:
> Hi,
> I have this structure:
> ---
> CREATE TABLE tabla (
>id int(10) unsigned NOT NULL auto_increment,
>nombre varchar(100) NOT NULL default '',

because your defaulting it to ''.. so null = '' on insert..

Jeff


pgpIHzJjqrxJ0.pgp
Description: PGP signature

Bug? Set Null Value in NOT NULL field...

[LMS]

Hi,
I have this structure:
---
CREATE TABLE tabla (
  id int(10) unsigned NOT NULL auto_increment,
  nombre varchar(100) NOT NULL default '',
  PRIMARY KEY  (id),
  UNIQUE KEY nombre (nombre),
  UNIQUE KEY id (id)
) ENGINE=MyISAM;

SELECT * FROM tabla
+
+ id+   nombre  +
+
+ 1 +   marcos  +
+

Now, I make a update:
-
UPDATE tabla SET nombre = NULL WHERE id = 1;
-
MySQL responds: "Query OK, 0 rows affected (0,01 sec)".
Again I do select:

SELECT * FROM tabla
+
+ id+   nombre  +
+
+ 1 +   +
+

MySQL Server change the NULL value by '', Why?, this is a bug?.

Thanks for any answer...
Marcelo Sosa

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

Re: comparing two tables

[Brent Baisley]

You would want to do a left join and only keep the null values.

SELECT tableA.*
FROM tableA
LEFT JOIN tableB ON tableA.recordID=tableB.recordID
WHERE tableB.recordID IS NULL

On Oct 27, 2005, at 6:35 AM, Christopher Molnar wrote:

If I have two similar tables, with identical columns how would I  
select rows that are not duplicated between both tables? Any easy  
way to do this?


Thanks,
-Chris

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






--
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: will a cluster be faster than a heap-table?

[Brent Baisley]

Wow, it seems like you are going to extremes. To jump from myisam to  
heap is a big step. Did you try using InnoDB? It would handle locking  
issues much better since it doesn't lock the table. Heap tables can  
be pretty dangerous since it's all in memory. If the machine crashes,  
you'll lose the data.


Based on your information, you want to get the best disk I/O you can.  
You won't get that out of a single "IDE" drive, even if it is one of  
the latest SATA based with command queuing. I don't think you'll get  
anything faster than heap tables and tons of RAM. But there is  
certainly finite scalability because of the use of RAM. Clusters may  
be the way to go for scalability, but I would work on getting your  
data disk based for maximum scalability.


I would try InnoDB and maximize you disk setup. I don't know how many  
disks you have in your RAID and if it's hardware or software based.  
More disks will add speed by splitting the load across more disks.  
Just keep in mind the limits of your SCSI card too. You may need to  
add a card to split the load.



On Oct 27, 2005, at 1:57 PM, Jan Kirchhoff wrote:


Hi,

I am currently using a replication setup on two servers with mysql
4.1.13-standard-log (master/slave each a P4 2.4ghz, 3GB RAM,  
Hardware SCSI-RAID).


I have a table that has lots of updates and selects. We converted  
this table
(along with other tables) from a myisam to a heap-table 6 months  
ago which
increased the performance a lot, but we are hitting the limits  
again - on the

master as well as on the slave.

We are only talking about 50-60 queries/second in peaks maybe 90 q/ 
sec (which

means more selects, but not much more inserts), but the inserts are
bulk-inserts (each around 2500-3000 rows) doing INSERT INTO...ON  
DUPLICATE KEY
UPDATE-Queries updating a total of around 50.000 rows/minute in the  
daytime,
We are getting locking-Problems with selects having to wait for 5  
seconds or

sometimes even much longer.
We expect that the amount of insert will increase slowly while the  
selects
will get much more pretty soon. The selects are all optimized and  
respond
within 0.x or 0.0x seconds in a mysql-shell in case they are not  
locked by an
insert. It is weird that those inserts that usually only take 1-2  
seconds
(never saw anything older in the processlist) now sometimes take 10  
seconds or
more while more and more selects are waiting in the Locked-status.  
I saw this
behaviour 3 weeks ago for the first time and maybe 4 or 5 more  
times since then...


I am just wondering if a cluster-setup would help us speed up the  
system.

If I understand this right, it is no problem to mix NDB-tables an
memory/myisam-tables. I'd just have to install a cluster-enabled  
version of
mysqld and set up 2 or more NDB-Nodes, right? I could then alter  
the few
speed-critical tables to the NDB-storage-type and would not have to  
change any

SQL?
I'd just take 2 simple Athlon64-PCs with 1GB-2GB RAM each, Gbit- 
Ethernet and
an IDE-Harddisk, no expensive Server-Hardware? Or would I need at  
least 4

Nodes to have an speed-improvement?

I've been trying to find answers on this on mysql.com but was not  
successful.
There is no info about possibilities of mixing NDB- and myisam- 
tables and all
documentation on mysql-cluster focuses more on HA than on speed  
especially

speed of cluster-tables compared to traditional mysql-memory-tables...

thanks for any help on this!

Jan

in case this is important:
show table status:
*** 25. row ***
   Name: memtable_spr
 Engine: HEAP
Version: 9
 Row_format: Fixed
   Rows: 777330
 Avg_row_length: 294
Data_length: 234729984
Max_data_length: 856336152
   Index_length: 52598232
  Data_free: 294
 Auto_increment: NULL
Create_time: NULL
Update_time: NULL
 Check_time: NULL
  Collation: latin1_swedish_ci
   Checksum: NULL
 Create_options:
Comment:


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






--
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 privs via PHP? Problems - * Now with error message

[T.J. Mahaffey]

*head smack*

Apologies for my newbie-ish first post to the list. (I know better  
than to exclude key details like *gasp* an error message.)

Thanks to Michael's brilliant suggestion, I now have my error:

"Client does not support authentication protocol requested by server;  
consider upgrading MySQL client"


Does this have something to do with the OLD_PASSWORD issue in MySQL  
5? (I didn't think OSXServer 10.4 included MySQL v.5... )


My setup:
Entropy PHP 4.3.11
MacOS X Server 10.4
10.4's default MySQL install

--
T.J. Mahaffey
[EMAIL PROTECTED]



On Oct 27, 2005, at 12:49 PM, Michael Stassen wrote:

The key to fixing these sorts of problems is to write php code that  
tells you what the error is, rather than simply failing.  Please  
pick one of your scripts (or make a test one) and make the connect  
code look like this:


  $link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')
or die('Could not connect: ' . mysql_error());

(replace mysql_host, mysql_user, and mysql_password with the  
appropriate values).  The second line is the key.  When this fails  
to connect, the error message from mysql will be printed.  Reply  
with the connect code (but don't show us the real password, of  
course) and the exact error message (copy/paste). Armed with that  
information, I'm sure we can tell you what's wrong.


Michael



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

will a cluster be faster than a heap-table?

[Jan Kirchhoff]

Hi,

I am currently using a replication setup on two servers with mysql
4.1.13-standard-log (master/slave each a P4 2.4ghz, 3GB RAM, Hardware 
SCSI-RAID).

I have a table that has lots of updates and selects. We converted this table
(along with other tables) from a myisam to a heap-table 6 months ago which
increased the performance a lot, but we are hitting the limits again - on the
master as well as on the slave.

We are only talking about 50-60 queries/second in peaks maybe 90 q/sec (which
means more selects, but not much more inserts), but the inserts are
bulk-inserts (each around 2500-3000 rows) doing INSERT INTO...ON DUPLICATE KEY
UPDATE-Queries updating a total of around 50.000 rows/minute in the daytime,
We are getting locking-Problems with selects having to wait for 5 seconds or
sometimes even much longer.
We expect that the amount of insert will increase slowly while the selects
will get much more pretty soon. The selects are all optimized and respond
within 0.x or 0.0x seconds in a mysql-shell in case they are not locked by an
insert. It is weird that those inserts that usually only take 1-2 seconds
(never saw anything older in the processlist) now sometimes take 10 seconds or
more while more and more selects are waiting in the Locked-status. I saw this
behaviour 3 weeks ago for the first time and maybe 4 or 5 more times since 
then...

I am just wondering if a cluster-setup would help us speed up the system.
If I understand this right, it is no problem to mix NDB-tables an
memory/myisam-tables. I'd just have to install a cluster-enabled version of
mysqld and set up 2 or more NDB-Nodes, right? I could then alter the few
speed-critical tables to the NDB-storage-type and would not have to change any
SQL?
I'd just take 2 simple Athlon64-PCs with 1GB-2GB RAM each, Gbit-Ethernet and
an IDE-Harddisk, no expensive Server-Hardware? Or would I need at least 4
Nodes to have an speed-improvement?

I've been trying to find answers on this on mysql.com but was not successful.
There is no info about possibilities of mixing NDB- and myisam-tables and all
documentation on mysql-cluster focuses more on HA than on speed especially
speed of cluster-tables compared to traditional mysql-memory-tables...

thanks for any help on this!

Jan

in case this is important:
show table status:
*** 25. row ***
   Name: memtable_spr
 Engine: HEAP
Version: 9
 Row_format: Fixed
   Rows: 777330
 Avg_row_length: 294
Data_length: 234729984
Max_data_length: 856336152
   Index_length: 52598232
  Data_free: 294
 Auto_increment: NULL
Create_time: NULL
Update_time: NULL
 Check_time: NULL
  Collation: latin1_swedish_ci
   Checksum: NULL
 Create_options:
Comment:


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

Re: Mysql privs via PHP? Problems

[Michael Stassen]

T.J. Mahaffey wrote:

It seems my MySQL privs aren't working properly.
Symptom is that PHP scripts themselves won't authenticate to MySQL  
*unless* the script uses the root MySQL login/pw.


I've verified that my added MySQL logins are set up and working in  the 
command line and they show up properly in phpmyadmin, but don't  seem to 
work as far as PHP is concerned.


Entropy PHP 4.3.11
MacOS X Server 10.4
10.4's default MySQL install

Can someone assist? I'd appreciate it very much. (Been fighting this  
one all day yesterday...)


--
T.J. Mahaffey
[EMAIL PROTECTED]


The key to fixing these sorts of problems is to write php code that tells you 
what the error is, rather than simply failing.  Please pick one of your scripts 
(or make a test one) and make the connect code look like this:


  $link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')
or die('Could not connect: ' . mysql_error());

(replace mysql_host, mysql_user, and mysql_password with the appropriate 
values).  The second line is the key.  When this fails to connect, the error 
message from mysql will be printed.  Reply with the connect code (but don't show 
us the real password, of course) and the exact error message (copy/paste). 
Armed with that information, I'm sure we can tell you what's wrong.


Michael


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

Mysql privs via PHP? Problems

[T.J. Mahaffey]

It seems my MySQL privs aren't working properly.
Symptom is that PHP scripts themselves won't authenticate to MySQL  
*unless* the script uses the root MySQL login/pw.


I've verified that my added MySQL logins are set up and working in  
the command line and they show up properly in phpmyadmin, but don't  
seem to work as far as PHP is concerned.


Entropy PHP 4.3.11
MacOS X Server 10.4
10.4's default MySQL install

Can someone assist? I'd appreciate it very much. (Been fighting this  
one all day yesterday...)



--
T.J. Mahaffey
[EMAIL PROTECTED]




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

Re: PHP and mysql

[Curtis Maurand]

mysqladmin -u root password 

Curtis

sheeri kritzer wrote:
> Hi Alaister,
>
> Your root password is not actually set.  If you do
>
> mysql -u root -p
> and it fails, it means that the password is not 
>
> if you do
>
> mysql -u root 
>
> the mysql client will parse  as the database you're trying
> to use -- that's just the syntax of the mysql client.  Which means
> that your password is not very secure, because it's the same as your
> database name.  You should get an error like this:
>
> ERROR 1049 (42000): Unknown database ''
>
> What you should do is this:
>
> # mysql -u root
>> set password=password("");
>
> where  is your new password.
>
> hope this helps.
>
> -Sheeri
>
>
> On 10/27/05, Alastair Roy <[EMAIL PROTECTED]> wrote:
>> Thanks for your reply I think this is the issue I don't think root is
>> allowed to login from local host I created another user called web user
>> and
>> changed the script, that works fine, next question is how do I set the
>> permissions for root in mysql to allow root to login, if I use
>> #mysql -u root  I go straight in
>> If I use
>> #mysql -u root -p
>> 
>> I get access denied for [EMAIL PROTECTED]
>>
>> -Original Message-
>>
>> Visit our websites:
>>
>> http://www.dailysnack.com +IBw-bite size news and gossip+IB0
>>
>> http://www.express.co.uk The Worlds Greatest Newspaper
>>
>> http://www.dailystar.co.uk Simply The Best 7 Days A Week
>>
>> http://www.happymagazine.co.uk The One Stop Shopping Magazine
>>
>> http://www.ok.co.uk First For Celebrity News
>>
>> http://www.northernandshell.co.uk The Mark Of Excellence
>>
>> http://www.expresspictures.com  Express Newspapers and OK Magazine
>> online picture archive
>>
>>
>>
>>
>> Also visit:
>>
>> The NMA: Opening Up Newspapers http://www.nmauk.co.uk
>>
>> ###2004###
>> 
>> Any views or opinions are solely those of the author
>> and do not necessarily represent those of Express Newspapers
>> 
>> The information transmitted is intended only for the person
>> or entity to which it is addressed and may contain confidential
>> and/or privileged material.If you are not the intended recipient
>> of this message please do not read ,copy, use or disclose this
>> communication and notify the sender immediately. It should be
>> noted that any review, retransmission, dissemination or other
>> use of, or taking action in reliance upon, this information by
>> persons or entities other than the intended recipient is prohibited.
>> E-mail communications may be monitored.
>> 
>>
>> ##EXN2000##
>>
>>
>> From: sheeri kritzer [mailto:[EMAIL PROTECTED]
>> Sent: 25 October 2005 18:06
>> To: Alastair Roy
>> Cc: mysql@lists.mysql.com
>> Subject: Re: PHP and mysql
>>
>> Yes.
>>
>> Whenever you have a problem, go back to basics.  Can you login as root
>> on the commandline?  What password do you use there?  Once you're
>> there, check root's permissions, but I'd be willing to bet your
>> password is incorrect, or root is only allowed to logon from localhost
>> and not the machine the webserver is on.
>>
>> -Sheeri
>>
>> On 10/25/05, Alastair Roy <[EMAIL PROTECTED]> wrote:
>> > Greetings everyone, I am having a problem with PHP and mysql I have
>> copied
>> a
>> > script exactly off a website to open the database and insert a new
>> user I
>> > try to access the mysql database using the root user, and enter
>> information
>> > into the user table, this is the PHP I am using
>> >
>> >
>> >
>> > > > include 'library/config.php';
>> > include 'library/opendb.php';
>> >
>> > $query = "INSERT INTO user (host, user, password, select_priv,
>> insert_priv,
>> > update_ priv) ".
>> >  "VALUES ('localhost', 'phpcake', PASSWORD('mypass'), 'Y',
>> 'Y',
>> > 'Y')";
>> > mysql_query($query) or die('Error, insert query failed');
>> >
>> > $query = "FLUSH PRIVILEGES";
>> > mysql_query($query) or die('Error, insert query failed');
>> >
>> > include 'library/closedb.php';
>> > ?>
>> >
>> >
>> >
>> > When I try this I get the access for user [EMAIL PROTECTED] host denied, it
>> is
>> > driving me nuts no matter what I try I get the same thing other
>> scripts I
>> > have tried do the same thing, have tried messing around with the
>> config.php
>> > and opendb.php included scripts but nothing works if I run php -f
>> opendb.php
>> > I get no errors which I think means it is working, then again I don't
>> know.
>> >
>> >
>> >
>> > Anyone have any ideas ??
>> >
>> >
>> >
>> > Thanks in advance
>> >
>> >
>> > Visit our websites:
>> >
>> > http://www.dailysnack.com +IBw-bite size news and gossip+IB0
>> >
>> > http://www.express.co.uk The Worlds Greatest Newspaper
>> >
>> > http://www.dailystar.co.uk Simply The Best 7 Days A Week
>> >
>> > http://www.happymagazine.co.uk The One Stop Shopping Magazine
>> >
>> > http://www.ok.co.uk First For Celebrity News
>> >
>> > http://www.northernandshell.co.uk The

Re: PHP and mysql

[sheeri kritzer]

Hi Alaister,

Your root password is not actually set.  If you do

mysql -u root -p
and it fails, it means that the password is not 

if you do

mysql -u root 

the mysql client will parse  as the database you're trying
to use -- that's just the syntax of the mysql client.  Which means
that your password is not very secure, because it's the same as your
database name.  You should get an error like this:

ERROR 1049 (42000): Unknown database ''

What you should do is this:

# mysql -u root
> set password=password("");

where  is your new password.

hope this helps.

-Sheeri


On 10/27/05, Alastair Roy <[EMAIL PROTECTED]> wrote:
> Thanks for your reply I think this is the issue I don't think root is
> allowed to login from local host I created another user called web user and
> changed the script, that works fine, next question is how do I set the
> permissions for root in mysql to allow root to login, if I use
> #mysql -u root  I go straight in
> If I use
> #mysql -u root -p
> 
> I get access denied for [EMAIL PROTECTED]
>
> -Original Message-
>
> Visit our websites:
>
> http://www.dailysnack.com +IBw-bite size news and gossip+IB0
>
> http://www.express.co.uk The Worlds Greatest Newspaper
>
> http://www.dailystar.co.uk Simply The Best 7 Days A Week
>
> http://www.happymagazine.co.uk The One Stop Shopping Magazine
>
> http://www.ok.co.uk First For Celebrity News
>
> http://www.northernandshell.co.uk The Mark Of Excellence
>
> http://www.expresspictures.com  Express Newspapers and OK Magazine online 
> picture archive
>
>
>
>
> Also visit:
>
> The NMA: Opening Up Newspapers http://www.nmauk.co.uk
>
> ###2004###
> 
> Any views or opinions are solely those of the author
> and do not necessarily represent those of Express Newspapers
> 
> The information transmitted is intended only for the person
> or entity to which it is addressed and may contain confidential
> and/or privileged material.If you are not the intended recipient
> of this message please do not read ,copy, use or disclose this
> communication and notify the sender immediately. It should be
> noted that any review, retransmission, dissemination or other
> use of, or taking action in reliance upon, this information by
> persons or entities other than the intended recipient is prohibited.
> E-mail communications may be monitored.
> 
>
> ##EXN2000##
>
>
> From: sheeri kritzer [mailto:[EMAIL PROTECTED]
> Sent: 25 October 2005 18:06
> To: Alastair Roy
> Cc: mysql@lists.mysql.com
> Subject: Re: PHP and mysql
>
> Yes.
>
> Whenever you have a problem, go back to basics.  Can you login as root
> on the commandline?  What password do you use there?  Once you're
> there, check root's permissions, but I'd be willing to bet your
> password is incorrect, or root is only allowed to logon from localhost
> and not the machine the webserver is on.
>
> -Sheeri
>
> On 10/25/05, Alastair Roy <[EMAIL PROTECTED]> wrote:
> > Greetings everyone, I am having a problem with PHP and mysql I have copied
> a
> > script exactly off a website to open the database and insert a new user I
> > try to access the mysql database using the root user, and enter
> information
> > into the user table, this is the PHP I am using
> >
> >
> >
> >  > include 'library/config.php';
> > include 'library/opendb.php';
> >
> > $query = "INSERT INTO user (host, user, password, select_priv,
> insert_priv,
> > update_ priv) ".
> >  "VALUES ('localhost', 'phpcake', PASSWORD('mypass'), 'Y', 'Y',
> > 'Y')";
> > mysql_query($query) or die('Error, insert query failed');
> >
> > $query = "FLUSH PRIVILEGES";
> > mysql_query($query) or die('Error, insert query failed');
> >
> > include 'library/closedb.php';
> > ?>
> >
> >
> >
> > When I try this I get the access for user [EMAIL PROTECTED] host denied, it 
> > is
> > driving me nuts no matter what I try I get the same thing other scripts I
> > have tried do the same thing, have tried messing around with the
> config.php
> > and opendb.php included scripts but nothing works if I run php -f
> opendb.php
> > I get no errors which I think means it is working, then again I don't
> know.
> >
> >
> >
> > Anyone have any ideas ??
> >
> >
> >
> > Thanks in advance
> >
> >
> > Visit our websites:
> >
> > http://www.dailysnack.com +IBw-bite size news and gossip+IB0
> >
> > http://www.express.co.uk The Worlds Greatest Newspaper
> >
> > http://www.dailystar.co.uk Simply The Best 7 Days A Week
> >
> > http://www.happymagazine.co.uk The One Stop Shopping Magazine
> >
> > http://www.ok.co.uk First For Celebrity News
> >
> > http://www.northernandshell.co.uk The Mark Of Excellence
> >
> > http://www.expresspictures.com  Express Newspapers and OK Magazine online
> picture archive
> >
> >
> >
> >
> > Also visit:
> >
> > The NMA: Opening Up Newspapers http://www.nmauk.co.uk
> >
> > ###2004###
> > 

Re: Mysqldump accessing a remote shared folder.

[Michael Stassen]

Tom Brown wrote:


Hi, I am trying to run mysqldump on a bugzilla database that is stored 
on a remote server. I have access to that shared folder
and I have no problem backing up with MySQL Admin. With mysqldump 
using a batch file (Windows) I get the following:
 
mysqldump: Got error: 1045: Access denied for user 
'bugs01'@'XP0406OEM' (using password: YES) when trying to connect


XP0406OEM is my PC. If I change the password argument to 
-p='password-name' the error is a 1044 'Acess denied message
for user 'bugs01'@'%' to database server when selecting the server.' 
If this means anything.
 
I map the shared folder to my S drive and I run the windows batch file 
below. If anyone can help I'd by thankful.  
S:

cd MySQL Server 4.1\bin
mysqldump --add-drop-table=S:\MySQL Server 4.1\backup bugs -u john01 
-p'password'

-h bugzilla.xxx.com --port 3306
pause



you need to grant a connect on that database to your machine eg

grant all on bugs.* to [EMAIL PROTECTED] identified by 'password'

flush privileges


FLUSH PRIVILEGES isn't needed with GRANT 
.


Michael


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

Re: Mysqldump accessing a remote shared folder.

[Tom Brown]

Hi, I am trying to run mysqldump on a bugzilla database that is stored on a 
remote server. I have access to that shared folder
and I have no problem backing up with MySQL Admin. With mysqldump using a batch 
file (Windows) I get the following:
 
mysqldump: Got error: 1045: Access denied for user 'bugs01'@'XP0406OEM' (using password: YES) when trying to connect


XP0406OEM is my PC. If I change the password argument to -p='password-name' the 
error is a 1044 'Acess denied message
for user 'bugs01'@'%' to database server when selecting the server.' If this 
means anything.
 
I map the shared folder to my S drive and I run the windows batch file below. If anyone can help I'd by thankful. 
 
S:

cd MySQL Server 4.1\bin
mysqldump --add-drop-table=S:\MySQL Server 4.1\backup bugs -u john01 
-p'password'
-h bugzilla.xxx.com --port 3306
pause


you need to grant a connect on that database to your machine eg

grant all on bugs.* to [EMAIL PROTECTED] identified by 'password'

flush privileges


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

RE: MSSQL-MySQL Compatibility Question

[J.R. Bullington]

Thanks guys!

J.R. 

-Original Message-
From: JamesDR [mailto:[EMAIL PROTECTED] 
Sent: Thursday, October 27, 2005 9:21 AM
To: mysql@lists.mysql.com
Subject: Re: MSSQL-MySQL Compatibility Question

J.R. Bullington wrote:
> Hi All,
>   Quick question -- I have a client who co-owns a server with me. I am

> a die-hard MySQL guy, they are MSSQL. They have some proprietary 
> Access-frontend/MSSQL-backend financial system that they want to 
> continue to use (i.e. pigheaded and won't convert).
> 
> Can MS-SQL and MySQL run on the same box and not conflict with each 
> other? I don't have the SQL disks in front of me to test and I was 
> wondering if anyone else ran into this situation.
> 
> Box is a Quad Xeon 2.0GHz, 1GB RAM, 80GB Ultra3 SCSI Raid.
> 
> Thanks!
> J.R.
> 
I have MSSQL + MySQL on the same box here, no issues with that... My server
is hardly "Big Iron" or "Small Iron" by any reach:
P3 933 1GB Ram, SCSI160 non-raid ;-D More memory, if you can do it, is
better... (roll on new server...)

Best thing to look at is your current load, if you are pushing the limits,
adding another RDBMS may cripple that box. As far as negative interaction,
I've seen none.

--
Thanks,
James

--
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: MSSQL-MySQL Compatibility Question

[JamesDR]

J.R. Bullington wrote:

Hi All,
Quick question -- I have a client who co-owns a server with me. I am
a die-hard MySQL guy, they are MSSQL. They have some proprietary
Access-frontend/MSSQL-backend financial system that they want to continue to
use (i.e. pigheaded and won't convert). 


Can MS-SQL and MySQL run on the same box and not conflict with each other? I
don't have the SQL disks in front of me to test and I was wondering if
anyone else ran into this situation.

Box is a Quad Xeon 2.0GHz, 1GB RAM, 80GB Ultra3 SCSI Raid.

Thanks!
J.R.

I have MSSQL + MySQL on the same box here, no issues with that... My 
server is hardly "Big Iron" or "Small Iron" by any reach:
P3 933 1GB Ram, SCSI160 non-raid ;-D More memory, if you can do it, is 
better... (roll on new server...)


Best thing to look at is your current load, if you are pushing the 
limits, adding another RDBMS may cripple that box. As far as negative 
interaction, I've seen none.


--
Thanks,
James

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

Re: MSSQL-MySQL Compatibility Question

[Alexey Polyakov]

Yeah, it's perfectly ok to run both on the same machine at the same
time, though with MS SQL you're limited to Microsoft OS.

--
Alexey Polyakov

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

MSSQL-MySQL Compatibility Question

[J.R. Bullington]

Hi All,
Quick question -- I have a client who co-owns a server with me. I am
a die-hard MySQL guy, they are MSSQL. They have some proprietary
Access-frontend/MSSQL-backend financial system that they want to continue to
use (i.e. pigheaded and won't convert). 

Can MS-SQL and MySQL run on the same box and not conflict with each other? I
don't have the SQL disks in front of me to test and I was wondering if
anyone else ran into this situation.

Box is a Quad Xeon 2.0GHz, 1GB RAM, 80GB Ultra3 SCSI Raid.

Thanks!
J.R.

Re: search machine problems

[Jigal van Hemert]

Aleksandra wrote:

Jigal van Hemert wrote:
I assume that you use Full-Text searches (but somehow you have managed 
to change the default minimum word length to three).
As far as I understand what you've written - if I change the minimum 
word length to 4, I won't get any result with the word 'and', 


No, I meant that the default minimum word length for full-text indexes 
is 4. If you can use 'cat and dog' as an argument for non-boolean 
searches and get relevant results, you must somehow have changed that 
setting and rebuilt the indexes. That made me wonder whether you used 
full-text indexes or not...


Regards, Jigal.

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

Re: Circular Replication

[Jigal van Hemert]

Stefan Kuhn wrote:

Am Thursday 27 October 2005 12:56 schrieb Raphaël 'SurcouF' Bordet:


Le vendredi 16 septembre 2005 à 18:14 +0200, Stefan Kuhn a écrit :


I'm using it with four machines (geographically separate) and it works
fine. Stefan


And can writes on each server in simultaneous time ?


I don't understand the question. Replication is pretty fast, but is not 
guaranteed to happen in a certain time (not real-time).


Stefan,

I think Raphaël wants to know if a user can use any server in the circle 
to update or insert records and that the changes will be replicated to 
all other servers.


In another thread he stated that it was for maintaining student 
information on various remote locations (a student can login into the 
system on any of the locations).


What happens if a record is updated on two servers and the changes are 
forwarded to the other servers in the circle? I dont' think that this 
would occur often with student information, but both the student and the 
administration department might update a record "simultaniously" (in 
comparison to the speed of replication with several remote locations).


Regards, Jigal.


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

Mysqldump accessing a remote shared folder.

[John Doneker]

Hi, I am trying to run mysqldump on a bugzilla database that is stored on a 
remote server. I have access to that shared folder
and I have no problem backing up with MySQL Admin. With mysqldump using a batch 
file (Windows) I get the following:
 
mysqldump: Got error: 1045: Access denied for user 'bugs01'@'XP0406OEM' (using 
password: YES) when trying to connect

XP0406OEM is my PC. If I change the password argument to -p='password-name' the 
error is a 1044 'Acess denied message
for user 'bugs01'@'%' to database server when selecting the server.' If this 
means anything.
 
I map the shared folder to my S drive and I run the windows batch file below. 
If anyone can help I'd by thankful. 
 
S:
cd MySQL Server 4.1\bin
mysqldump --add-drop-table=S:\MySQL Server 4.1\backup bugs -u john01 
-p'password'
-h bugzilla.xxx.com --port 3306
pause

 
J. Wayne Doneker
BAE Systems  
York Pa.
717 225 8109 
Email: [EMAIL PROTECTED]

Re: Circular Replication

[Stefan Kuhn]

Am Thursday 27 October 2005 12:56 schrieb Raphaël 'SurcouF' Bordet:
> Le vendredi 16 septembre 2005 à 18:14 +0200, Stefan Kuhn a écrit :
> > I'm using it with four machines (geographically separate) and it works
> > fine. Stefan
>
> And can writes on each server in simultaneous time ?
I don't understand the question. Replication is pretty fast, but is not 
guaranteed to happen in a certain time (not real-time).
Stefan
>
> --
> Raphaël 'SurcouF' Bordet
> http://debianfr.net/ | surcouf at debianfr dot net

-- 
Stefan Kuhn M. A.
Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de)
Zülpicher Str. 47, 50674 Cologne
Tel: +49(0)221-470-7428   Fax: +49 (0) 221-470-7786
My public PGP key is available at http://pgp.mit.edu

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

Re: search machine problems

[Aleksandra]

Jigal van Hemert wrote:


Aleksandra wrote:

I've written a search machine. It's working quite well, but I have 
two problems:


1. When I give the following syntax :   cat and dog , as a result I 
get everything with at least one of the words: 'cat', 'dog' but also 
'and'.
What can I do, so that it's not looking for the word 'and' or some 
other similar words like 'or', etc..


2. I would like the search machine to look for the results that 
consist both: 'cat' and 'dog' and not only one of this words.



I assume that you use Full-Text searches (but somehow you have managed 
to change the default minimum word length to three).
Maybe you can find the functionality you want in Boolean Full-Text 
searches (http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html).


Changing 'cat and dog' to '+cat +dog' is a matter of parsing the input 
in your application. You have to be beware of expressions such as 'cat 
"dog and man" +bird' where your "noise words" are suddenly relevant 
when inside double quotes.


As far as I understand what you've written - if I change the minimum 
word length to 4, I won't get any result with the word 'and', but what's 
more - I won't get any result, because all the words I've used are not 
longer than 3 letters. Another thing ist that there are many short words 
that make sense, so it won't solve my problems.


Any more ideas?

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

Re: Circular Replication

[Raphaël 'SurcouF' Bordet]

Le vendredi 16 septembre 2005 à 18:14 +0200, Stefan Kuhn a écrit :
> I'm using it with four machines (geographically separate) and it works fine.
> Stefan

And can writes on each server in simultaneous time ?

-- 
Raphaël 'SurcouF' Bordet
http://debianfr.net/ | surcouf at debianfr dot net




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

Re: Peer to Peer Replication?

[Jigal van Hemert]

Eldon Ziegler wrote:
We have a geographically dispersed system with a database of student 
status information that needs to be replicated in as near to real time 
as we can get. The MySQL master-slave method of replication doesn't seem 
applicable as students can login to any server and pickup where they 
were before. How can peer to peer replication be done?


Maybe circular replication is something for you?
See a recent (september) thread: http://lists.mysql.com/mysql/189179

Regards, Jigal.

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

Peer to Peer Replication?

[Eldon Ziegler]

We have a geographically dispersed system with a database of student 
status information that needs to be replicated in as near to real 
time as we can get. The MySQL master-slave method of replication 
doesn't seem applicable as students can login to any server and 
pickup where they were before. How can peer to peer replication be done?


Eldon Ziegler


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

Re: Record exists but not found - grrr

[Wenca]

Hi Paul,

I'm sure there are no spaces or other blank characters. I run exactly 
this query (I deleted the row and tried again):


INSERT INTO tab_p (d_id, name) VALUES (20602, 'Machico');
INSERT INTO tab_p (d_id, name) VALUES (20602, 'Funchal');

SELECT * FROM tab_p WHERE d_id = 20602 AND name LIKE 'Machico';
-> no results
SELECT * FROM tab_p WHERE d_id = 20602 AND name LIKE 'Funchal';
-> OK 1 row

SELECT * FROM tab_p WHERE d_id = 20602 AND name LIKE '%Machico';
-> OK 1 row

I really don't understand it. I have this problem with about 3 or 4 
words within nearly 5000 that I currently have in the table.


Wenca

Paul Rhodes wrote:

Hi Wenca,

Is it possible that you may a space at the beginning or end of the
string.

Try this to determine whether this is the case:
SELECT * FROM tab_p WHERE d_id = 20602 AND name LIKE '%Machico%'

If this works, try deleting and re-inserting the row.

I've had a similar situation in the past.

Hope this helps,
Paul


-Original Message-
From: Wenca [mailto:[EMAIL PROTECTED] 
Sent: 27 October 2005 10:56

To: mysql@lists.mysql.com
Subject: Record exists but not found - grrr


Hi all,

I've got a problem that I don't understand and that is driving me mad.

I have a table 'tab_p' with this structure:

nametype
---
p_idmediumint(8) AUTOINCREMENT NOT NULL PRIMARY KEY
d_idsmallint(5)  NOT NULL
namevarchar(50)  NOT NULL

And unique index on (d_id, name).

ENGINE=MyISAM DEFAULT CHARSET=utf8
COLLATE utf8_slovak_ci or utf8_czech_ci

And there is a row with (for example) these data:
p_idd_idname

953 20602   Machico

When I try query:
SELECT * FROM tab_p WHERE d_id = 20602 AND name LIKE 'Machico'
-> no results

So I try to insert the row:
INSERT INTO tab_p (d_id, name) VALUES (20602, 'Machico')
-> Error: #1062 - Duplicate entry '20602-Machico' for key 2

Why!!!???

There are thousands of records in the table an they work fine but then 
there occures some normal word (even with no special characters) and it 
behaves like this.


Can anyone help me?

I'm running MySQL 4.1.11 on Linux.

Thanks
Wenca



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

Re: 'Autoextend' datafile - Mysql-Max-4.1.14 - Linux

[Jigal van Hemert]

Tom Brown wrote:
hmm i'm not entirely sure i fancy adding another data file on the fly - 
Can you give me a brief run down about how you would go about this?
My collegue the sysadmin usually takes care of keeping the databases 
happy. But AFAIK it's a matter of modifying the setting in the .cnf file 
and restarting MySQL. I wouldn't let a process do the editing in the 
.cnf though, but you may trigger alerts automagically.


another option would be for me to create the 100gig as 2 25 gig files 
from the off - Do you know how well these would compress? Do you know 
what they are 'filled' with so when it comes to backup they will 
compress down to virtually nothing?


I wouldn't use the InnoDB datafiles in a backup. They are use server 
wide for storing InnoDB data. You can use a MySQL dump or the InnoDB hot 
backup tool to backup the data easily.


Regards, Jigal.

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

comparing two tables

[Christopher Molnar]

If I have two similar tables, with identical columns how would I  
select rows that are not duplicated between both tables? Any easy way  
to do this?


Thanks,
-Chris

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

Re: Record exists but not found - grrr

[Jigal van Hemert]

Wenca wrote:

SELECT * FROM tab_p WHERE d_id = 20602 AND name LIKE 'Machico'
-> no results


Try it with ...AND `name` LIKE '%Machico%';

I have encountered it once when there were non printable characters in 
front of or after the text itself. Very frustrating!


You can see what extra characters are there when you query
SELECT *, HEX(`name`) FROM...

This will add a column to the output with the hexadecimal representation 
of each character in the name.


Kind regards, Jigal.

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

Re: MySQL 4.1 timezone

[Raphaël 'SurcouF' Bordet]

Le jeudi 27 octobre 2005 à 01:12 +0300, Gleb Paharenko a écrit :
> Hello.
> 
> 
> 
> In mysql-5.0.15 it works. Put, say, default_time_zone='+10:00' in
> 
> [mysqld] section of your file.

This option works too under MySQL 4.1.11:

[mysqld]
default_time_zone=UTC

mysql> SELECT @@global.time_zone, @@session.time_zone;
++-+
| @@global.time_zone | @@session.time_zone |
++-+
| UTC| UTC |
++-+
1 row in set (0.00 sec)


Thanks.

-- 
Raphaël 'SurcouF' Bordet
http://debianfr.net/ | surcouf at debianfr dot net




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

Re: search machine problems

[Jigal van Hemert]

Aleksandra wrote:
I've written a search machine. It's working quite well, but I have two 
problems:


1. When I give the following syntax :   cat and dog , as a result I get 
everything with at least one of the words: 'cat', 'dog' but also 'and'.
What can I do, so that it's not looking for the word 'and' or some other 
similar words like 'or', etc..


2. I would like the search machine to look for the results that consist 
both: 'cat' and 'dog' and not only one of this words.


I assume that you use Full-Text searches (but somehow you have managed 
to change the default minimum word length to three).
Maybe you can find the functionality you want in Boolean Full-Text 
searches (http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html).


Changing 'cat and dog' to '+cat +dog' is a matter of parsing the input 
in your application. You have to be beware of expressions such as 'cat 
"dog and man" +bird' where your "noise words" are suddenly relevant when 
inside double quotes.


Regards, Jigal.

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

search machine problems

[Aleksandra]

Hi!

I've written a search machine. It's working quite well, but I have two 
problems:


1. When I give the following syntax :   cat and dog 
, as a result I get everything with at least one of the words: 'cat', 
'dog' but also 'and'.
What can I do, so that it's not looking for the word 'and' or some other 
similar words like 'or', etc..


2. I would like the search machine to look for the results that consist 
both: 'cat' and 'dog' and not only one of this words.


Can anybody help me with these problems???

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

Record exists but not found - grrr

[Wenca]

Hi all,

I've got a problem that I don't understand and that is driving me mad.

I have a table 'tab_p' with this structure:

nametype
---
p_idmediumint(8) AUTOINCREMENT NOT NULL PRIMARY KEY
d_idsmallint(5)  NOT NULL
namevarchar(50)  NOT NULL

And unique index on (d_id, name).

ENGINE=MyISAM DEFAULT CHARSET=utf8
COLLATE utf8_slovak_ci or utf8_czech_ci

And there is a row with (for example) these data:
p_idd_idname

953 20602   Machico

When I try query:
SELECT * FROM tab_p WHERE d_id = 20602 AND name LIKE 'Machico'
-> no results

So I try to insert the row:
INSERT INTO tab_p (d_id, name) VALUES (20602, 'Machico')
-> Error: #1062 - Duplicate entry '20602-Machico' for key 2

Why!!!???

There are thousands of records in the table an they work fine but then 
there occures some normal word (even with no special characters) and it 
behaves like this.


Can anyone help me?

I'm running MySQL 4.1.11 on Linux.

Thanks
Wenca

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

Slave Problem

[Marvin Wright]

Hi,

I found this morning that the slave replication thread seem to have hung.
Below I have pasted in my show slave status, it seems to be stuck trying to
connect to the master.
To fix this I issued a stop slave and start slave, my slave is about 3 weeks
behind now.

I have a script checking for when the replication fails but that does not
include this type problem. Is this a bug in replication or do I need to
update my script to check for this ?

I'm running version 4.1.12 on Redhat AS3.

Many Thanks

Marvin.

 mysql> show slave status\G
*** 1. row ***
 Slave_IO_State: Reconnecting after a failed master event read
Master_Host: prdmysql01.prd.lastminute.com
Master_User: web
Master_Port: 3306
  Connect_Retry: 60
Master_Log_File: prdmysql01-bin.000118
Read_Master_Log_Pos: 15561995
 Relay_Log_File: prdmysql02-relay-bin.01
  Relay_Log_Pos: 80882143
  Relay_Master_Log_File: prdmysql01-bin.000118
   Slave_IO_Running: Yes
  Slave_SQL_Running: Yes
Replicate_Do_DB: 
Replicate_Ignore_DB: 
 Replicate_Do_Table: 
 Replicate_Ignore_Table: 
Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
 Last_Errno: 0
 Last_Error: 
   Skip_Counter: 0
Exec_Master_Log_Pos: 15561995
Relay_Log_Space: 80882143
Until_Condition: None
 Until_Log_File: 
  Until_Log_Pos: 0
 Master_SSL_Allowed: No
 Master_SSL_CA_File: 
 Master_SSL_CA_Path: 
Master_SSL_Cert: 
  Master_SSL_Cipher: 
 Master_SSL_Key: 
  Seconds_Behind_Master: NULL
1 row in set (0.00 sec)


**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**


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

Trouble running mysql_install_db

[Bruce Martin]

Hello all,

I just installed MySQL 5 on Mac OS 10.3.9. Now when I try to create a 
user using any method it does not work. I read I may have to run the 
mysql_install_db script so I did, but I get these results:


mkdir: ./data/mysql: Permission denied
chmod: ./data/mysql: Permission denied
mkdir: ./data/test: Permission denied
chmod: ./data/test: Permission denied
Installing all prepared tables
051027  5:29:02 [Warning] Can't create test file 
/usr/local/mysql-standard-5.0.15-osx10.3-powerpc/data/24.lower-test
./bin/mysqld: Can't change dir to 
'/usr/local/mysql-standard-5.0.15-osx10.3-powerpc/data/' (Errcode: 13)

051027  5:29:02 [ERROR] Aborting

051027  5:29:02 [Note] ./bin/mysqld: Shutdown complete

./bin/mysql_create_system_tables: line 766:   674 Broken pipe   
  cat  <
use mysql;
set table_type=myisam;
$c_d
$i_d

$c_h
$i_h

$c_u
$i_u

$c_f
$i_f

$c_t
$c_c

$c_ht
$c_hc
$c_hr
$c_hk

$c_tzn
$i_tzn
$c_tz
$i_tz
$c_tzt
$i_tzt
$c_tztt
$i_tztt
$c_tzls
$i_tzls

$c_p
$c_pp

END_OF_DATA

Installation of system tables failed!

Examine the logs in ./data for more information.
You can also try to start the mysqld daemon with:
./bin/mysqld --skip-grant &
You can use the command line tool
./bin/mysql to connect to the mysql
database and look at the grant tables:

shell> ./bin/mysql -u root mysql
mysql> show tables

Try 'mysqld --help' if you have problems with paths. Using --log
gives you a log in ./data that may be helpful.

The latest information about MySQL is available on the web at
http://www.mysql.com
Please consult the MySQL manual section: 'Problems running 
mysql_install_db',

and the manual section that describes problems on your OS.
Another information source is the MySQL email archive.
Please check all of the above before mailing us!
And if you do mail us, you MUST use the ./bin/mysqlbug script!

I then checked the permissions for the /usr/local/mysql/data/ directory 
and it shows:

drwxr-x---  13 mysql  wheel

What am I missing? I installed MySQL using the installer package.

Bruce Martin
The Martin Solution
PO Box 644
Delaware Water Gap, PA
(570) 421-0670
[EMAIL PROTECTED]


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

RE: PHP and mysql

[Logan, David (SST - Adelaide)]

Hi Alastair,

You don't have a password for root set. The syntax for mysql (the
client) would put your #mysql -u root  as setting the database
to .

If you use the -p option, even if you don't supply a password, it will
be rejected if no password is set.

You may like to check out
http://dev.mysql.com/doc/refman/5.0/en/privilege-system.html

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: Alastair Roy [mailto:[EMAIL PROTECTED] 
Sent: Thursday, 27 October 2005 6:23 PM
To: 'sheeri kritzer'
Cc: mysql@lists.mysql.com
Subject: RE: PHP and mysql

Thanks for your reply I think this is the issue I don't think root is
allowed to login from local host I created another user called web user
and
changed the script, that works fine, next question is how do I set the
permissions for root in mysql to allow root to login, if I use 
#mysql -u root  I go straight in
If I use
#mysql -u root -p

I get access denied for [EMAIL PROTECTED]

-Original Message-

Visit our websites: 

http://www.dailysnack.com "bite size news and gossip"

http://www.express.co.uk The Worlds Greatest Newspaper

http://www.dailystar.co.uk Simply The Best 7 Days A Week

http://www.happymagazine.co.uk The One Stop Shopping Magazine

http://www.ok.co.uk First For Celebrity News

http://www.northernandshell.co.uk The Mark Of Excellence

http://www.expresspictures.com  Express Newspapers and OK Magazine
online picture archive




Also visit:

The NMA: Opening Up Newspapers http://www.nmauk.co.uk

###2004###

Any views or opinions are solely those of the author 
and do not necessarily represent those of Express Newspapers

The information transmitted is intended only for the person 
or entity to which it is addressed and may contain confidential 
and/or privileged material.If you are not the intended recipient
of this message please do not read ,copy, use or disclose this 
communication and notify the sender immediately. It should be 
noted that any review, retransmission, dissemination or other 
use of, or taking action in reliance upon, this information by 
persons or entities other than the intended recipient is prohibited.
E-mail communications may be monitored.



##EXN2000##


From: sheeri kritzer [mailto:[EMAIL PROTECTED] 
Sent: 25 October 2005 18:06
To: Alastair Roy
Cc: mysql@lists.mysql.com
Subject: Re: PHP and mysql

Yes.

Whenever you have a problem, go back to basics.  Can you login as root
on the commandline?  What password do you use there?  Once you're
there, check root's permissions, but I'd be willing to bet your
password is incorrect, or root is only allowed to logon from localhost
and not the machine the webserver is on.

-Sheeri

On 10/25/05, Alastair Roy <[EMAIL PROTECTED]> wrote:
> Greetings everyone, I am having a problem with PHP and mysql I have
copied
a
> script exactly off a website to open the database and insert a new
user I
> try to access the mysql database using the root user, and enter
information
> into the user table, this is the PHP I am using
>
>
>
>  include 'library/config.php';
> include 'library/opendb.php';
>
> $query = "INSERT INTO user (host, user, password, select_priv,
insert_priv,
> update_ priv) ".
>  "VALUES ('localhost', 'phpcake', PASSWORD('mypass'), 'Y',
'Y',
> 'Y')";
> mysql_query($query) or die('Error, insert query failed');
>
> $query = "FLUSH PRIVILEGES";
> mysql_query($query) or die('Error, insert query failed');
>
> include 'library/closedb.php';
> ?>
>
>
>
> When I try this I get the access for user [EMAIL PROTECTED] host denied, it
is
> driving me nuts no matter what I try I get the same thing other
scripts I
> have tried do the same thing, have tried messing around with the
config.php
> and opendb.php included scripts but nothing works if I run php -f
opendb.php
> I get no errors which I think means it is working, then again I don't
know.
>
>
>
> Anyone have any ideas ??
>
>
>
> Thanks in advance
>
>
> Visit our websites:
>
> http://www.dailysnack.com "bite size news and gossip"
>
> http://www.express.co.uk The Worlds Greatest Newspaper
>
> http://www.dailystar.co.uk Simply The Best 7 Days A Week
>
> http://www.happymagazine.co.uk The One Stop Shopping Magazine
>
> http://www.ok.co.uk First For Celebrity News
>
> http://www.northernandshell.co.uk The Mark Of Excellence
>
> http://www.expresspictures.com  Express Newspapers and OK Magazine
online
picture archive
>
>
>
>
> Also visit:
>
> The NMA: Opening Up Newspapers http://www.nmauk.co.uk
>
> ###2004###
> 
> Any views or opinions are solely those of the author
> and do not necessarily represent those of Express Newspapers
> 
> The infor

RE: PHP and mysql

[Alastair Roy]

Thanks for your reply I think this is the issue I don't think root is
allowed to login from local host I created another user called web user and
changed the script, that works fine, next question is how do I set the
permissions for root in mysql to allow root to login, if I use 
#mysql -u root  I go straight in
If I use
#mysql -u root -p

I get access denied for [EMAIL PROTECTED]

-Original Message-

Visit our websites: 

http://www.dailysnack.com “bite size news and gossip”

http://www.express.co.uk The Worlds Greatest Newspaper

http://www.dailystar.co.uk Simply The Best 7 Days A Week

http://www.happymagazine.co.uk The One Stop Shopping Magazine

http://www.ok.co.uk First For Celebrity News

http://www.northernandshell.co.uk The Mark Of Excellence

http://www.expresspictures.com  Express Newspapers and OK Magazine online 
picture archive




Also visit:

The NMA: Opening Up Newspapers http://www.nmauk.co.uk

###2004###

Any views or opinions are solely those of the author 
and do not necessarily represent those of Express Newspapers

The information transmitted is intended only for the person 
or entity to which it is addressed and may contain confidential 
and/or privileged material.If you are not the intended recipient
of this message please do not read ,copy, use or disclose this 
communication and notify the sender immediately. It should be 
noted that any review, retransmission, dissemination or other 
use of, or taking action in reliance upon, this information by 
persons or entities other than the intended recipient is prohibited.
E-mail communications may be monitored.


##EXN2000##


From: sheeri kritzer [mailto:[EMAIL PROTECTED] 
Sent: 25 October 2005 18:06
To: Alastair Roy
Cc: mysql@lists.mysql.com
Subject: Re: PHP and mysql

Yes.

Whenever you have a problem, go back to basics.  Can you login as root
on the commandline?  What password do you use there?  Once you're
there, check root's permissions, but I'd be willing to bet your
password is incorrect, or root is only allowed to logon from localhost
and not the machine the webserver is on.

-Sheeri

On 10/25/05, Alastair Roy <[EMAIL PROTECTED]> wrote:
> Greetings everyone, I am having a problem with PHP and mysql I have copied
a
> script exactly off a website to open the database and insert a new user I
> try to access the mysql database using the root user, and enter
information
> into the user table, this is the PHP I am using
>
>
>
>  include 'library/config.php';
> include 'library/opendb.php';
>
> $query = "INSERT INTO user (host, user, password, select_priv,
insert_priv,
> update_ priv) ".
>  "VALUES ('localhost', 'phpcake', PASSWORD('mypass'), 'Y', 'Y',
> 'Y')";
> mysql_query($query) or die('Error, insert query failed');
>
> $query = "FLUSH PRIVILEGES";
> mysql_query($query) or die('Error, insert query failed');
>
> include 'library/closedb.php';
> ?>
>
>
>
> When I try this I get the access for user [EMAIL PROTECTED] host denied, it is
> driving me nuts no matter what I try I get the same thing other scripts I
> have tried do the same thing, have tried messing around with the
config.php
> and opendb.php included scripts but nothing works if I run php -f
opendb.php
> I get no errors which I think means it is working, then again I don't
know.
>
>
>
> Anyone have any ideas ??
>
>
>
> Thanks in advance
>
>
> Visit our websites:
>
> http://www.dailysnack.com “bite size news and gossip”
>
> http://www.express.co.uk The Worlds Greatest Newspaper
>
> http://www.dailystar.co.uk Simply The Best 7 Days A Week
>
> http://www.happymagazine.co.uk The One Stop Shopping Magazine
>
> http://www.ok.co.uk First For Celebrity News
>
> http://www.northernandshell.co.uk The Mark Of Excellence
>
> http://www.expresspictures.com  Express Newspapers and OK Magazine online
picture archive
>
>
>
>
> Also visit:
>
> The NMA: Opening Up Newspapers http://www.nmauk.co.uk
>
> ###2004###
> 
> Any views or opinions are solely those of the author
> and do not necessarily represent those of Express Newspapers
> 
> The information transmitted is intended only for the person
> or entity to which it is addressed and may contain confidential
> and/or privileged material.If you are not the intended recipient
> of this message please do not read ,copy, use or disclose this
> communication and notify the sender immediately. It should be
> noted that any review, retransmission, dissemination or other
> use of, or taking action in reliance upon, this information by
> persons or entities other than the intended recipient is prohibited.
> E-mail communications may be monitored.
>

>
> ##EXN2000##
>
>
>

-- 
MySQL General Mailing List
For list archives: http:

Re: Error 1406

[Gleb Paharenko]

Hello.



> The server was installed with Utf8 so that is not the issue and the

>server from before dumped was utf8 as well.



Complete UTF8 support has appeared only in 4.1 branch.



> The problem with this one seemed to be when the dump would get put

back in,

> it would throw this error on all the french characters in the dump

file.  I



Have you tried to perform a dump with --quote-names option?





James Sherwood wrote:

> I have since uninstalled 5.0 and installed 4.0

> 

> I have found that 4.1 and 5.0 have a VERY hard time with french characters

> and there are MANY problems assosiated with french characters.

> 

> The problem with this one seemed to be when the dump would get put back in,

> it would throw this error on all the french characters in the dump file.  I

> looked in the file at the spot it was throwing the error and there was a

> french character, I deleted it from the dump file and it would throw the

> error at the next one.

> 

> The server was installed with Utf8 so that is not the issue and the server

> from before dumped was utf8 as well.

> 

> James

> 

> 

> - Original Message ---

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




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

Re: how to check repricate

[Gleb Paharenko]

Hello.



I'm a bit sorry, but my English doesn't allow me to clearly see your

problem. Could you repost your message and include the output of the

following statements:



SHOW SLAVE STATUS

SHOW MASTER STATUS

SHOW TABLE STATUS



Execute SHOW TABLE STATUS both on the Slave and Master. Use as much

as possible SQL statements. BTW, your MySQL version is old enough.

I strongly recommend you to upgrade.





かたりなしょうじ wrote:

> Nice to meet you.

> My name is katakura.

> I might want to ask the replication.

> 

> It is that whether the slave side has synchronized though show master

> status is done

> on the side of ..show slave status.. mastering on the slave side and

> Read_Master_Log_

> Pos and Position agree that Rows in the value of show table status is

> not completely

> corresponding.

> Is not it thought that the time lag when the command is input is a

> cause to which

> Rows shifts?

> Is everybody judged that what confirm method is done and

> synchronization is taken?

> 

> 

> Master from MySQL-client-4.0.14-0.i386.rpm in MySQL.

> Slave from src to install mysql-4.0.14.tar.gz

> OS uses SuSE Linux 8.2.

> Because a similar error had not been seen to be reported, it

> contributed.

> 



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




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

Re: MySQL 4.1 timezone

[Gleb Paharenko]

Hello.



In mysql-5.0.15 it works. Put, say, default_time_zone='+10:00' in

[mysqld] section of your file.





Raphaël 'SurcouF' Bordet wrote:

> Hi,

> 

> I want to set up a different timezone that my operating system for MySQL

> upper to 4.1. According to this article[1], timezone system variable was

> changed since 4.1.3. I'm using mysql 4.1.11a from Debian sarge.

> I was tried to set system_time_zone into /etc/mysql/my.cnf

> unsuccessfully. The only way I found to set up this timezone is by add

> --default-time-zone option to boot-up script of mysql.

> 

> Why system variable doesn't work into my.cnf ?

> 

> Best regards,

> 

> --=20

> Rapha=C3=ABl 'SurcouF' Bordet

> http://debianfr.net/ | surcouf at debianfr dot net

> =20

> 

> 

> 



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




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

Re: Sum of Sales

[Daniel Bowett]

Michael Stassen wrote:

Daniel Bowett wrote:


Michael Stassen wrote:


Daniel Bowett wrote:


Peter Brawley wrote:


Dan,

/>...Total sales for ever would be a simple GROUP BY query with a
 >sum on the sales - but I cant see how I am going to get this info.
 >Do I need to use nested queries? /

You don't need nested queries. It's a crosstab or pivot table 
query. The trick is to sum into one column per desired year, scope 
the sums on month-to-date, and group by retailer, eg:


SELECT
  r.name,
  SUM(IF(LEFT(month_2,4)='2004',sales, '')) AS '2004 Sales',
  SUM(IF(LEFT(month_2,4)='2005',sales, '')) AS '2005 Sales'
FROM tbl_retailer AS r
INNER JOIN tbl_sales AS s USING (retailerid)
WHERE SUBSTRING(month_2,6,2)
I am unsure how to write this query, can someone help?

I have two tables.

One has a list of retailers (tbl_retailer):

retailerid
name
postcode

e.g.

1   Sprocket Supplies   CH23 4PY

The other has the sales by month for each retailer:

retailerid
month_2   sales

e.g.

1   2004-01   100
1   2004-02   400
1   2004-03   300
1   2004-04   200
1   2004-05   300


What I need is a way to output a list of each retailer with two 
columns at the end being sales this year to date and the 
equivalent sales for the previous year.


So the columns would be:

namethis_years_sales_to_datelast_years_sales_to_date

Total sales for ever would be a simple GROUP BY query with a sum 
on the sales - but I cant see how I am going to get this info. Do 
I need to use nested queries?


Regards,

Dan.





 



No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.361 / Virus Database: 267.12.5/149 - Release Date: 
10/25/2005




 






Thats workign great, the only problem is the WHERE clause means I 
only show rows where there is sales info in the database. Sometimes 
there will be no sales info in there for a particular retailer - 
would it be possible to show zero for these?





Yes.  Change the INNER JOIN to a LEFT JOIN.

Michael




I tried a LEFT JOIN earlier - it still only shows rows where there is
sales. I think it's because of the WHERE clause.



Sorry, my answer was a bit short.  You are right, the WHERE clause is 
the problem.  Conditions on the right side of a LEFT JOIN need to go in 
the ON clause, not the WHERE clause, or it defeats the purpose of the 
LEFT JOIN.  So, your query would be


  SELECT
r.name,
SUM(IF(LEFT(s.month_2,4)='2004',s.sales, '')) AS '2004 Sales',
SUM(IF(LEFT(s.month_2,4)='2005',s.sales, '')) AS '2005 Sales'
  FROM tbl_retailer AS r
  LEFT JOIN tbl_sales AS s
ON r.retailerid = s.retailerid
   AND SUBSTRING(s.month_2,6,2) < MONTH(NOW())
  GROUP BY r.name;

Michael




Thanks for your help - that's working really well now.


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