Re: Problem deleteing records
On Fri, Feb 06, 2004 at 02:58:50PM +0400, Vinay wrote: I have a problem with a table that is too big it contains around 35,000,000 lines and each end of month i have to take out about 20,000,000 lines from it so my delete command is : delete from table where column_value** on a column that is indexed. But each time i do that i have mysql that have too many connection problem and i have to kill and restart mysql in the middle of the process. The DELETE probably takes a very long time. In the mean time, clients that use the table, will block until the DELETE is finished. If enough clients keep connecting, you'll run into the connection limit sometime. Do the DELETE in small chunks, so it will block your other clients for only a short time. Pause a little in between each chunk, to allow other clients to do some work. In an interactive application, your users will love you for this :) Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advise on High Availability configuration
On Fri, Jan 30, 2004 at 10:36:34AM -0800, Gowtham Jayaram wrote:
CONFIGURATION:
[...]
- Additionally, I will setup a SCSII controller in
the Primary and Secondary Application machines so that
the actual data store (disk drive) runs on another
physical machine in a disk-array (RAID).
So your servers independently write to the same disk partition through a
SCSI interface? I don't have any experience with this, but I guess you
couldn't even mount the filesystem on the secondary before the primary
goes down. If the primary goes down unexpectedly, your filesystem may be
corrupt unless you use a logging filesystem.
Also, the disks are redundant, but is all hardware in this RAID system
('physical machine') redundant?
OPERATION:
[...]
- If the Primary goes down, the Secondary becomes
Active and starts to perform the database operations.
If the primary goes down, how do you know that the database is in a good
state, even if the filesystem itself is consistent? I don't think MyISAM
gives you any guarantees in that regard. The secondary would need to run
myisamcheck on the tables before taking over.
Regards,
Fred.
--
Fred van Engen XB Networks B.V.
email: [EMAIL PROTECTED]Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: The biggest actual value in a Key-Field
On Sat, Jan 31, 2004 at 08:31:38PM +0100, A.J.Millan wrote: LAST_INSERT_ID() function: This other one has proven before, but in my case it returns many results (in fact, the last one es the desired data). It will return a single result if you just do this: SELECT LAST_INSERT_ID(); Do *NOT* do this: SELECT LAST_INSERT_ID() FROM mytable; That would return the same value for each row of mytable, which is quite useless. Note that LAST_INSERT_ID() is not kept per table, but just once per connection, so 'mytable' is of no use here. The most direct way is to SELECT the MAX() value from the table: OK You are right!!. In my opinion it is not only the most direct, but the ONLY direct . I admit that I had not understood the meaning of this sentence :-( Don't use this instead of LAST_INSERT_ID() because some other process may have inserted another record in the mean time. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Paging!
On Thu, Jan 29, 2004 at 11:19:27AM -0500, Mike Johnson wrote: From: Dan Greene [mailto:[EMAIL PROTECTED] another option is to set your pager option: mysql pager more then re-run your query Wow. You learn something new every day. How about the 'help' command? You'll learn even more ;) Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stumped on a query
On Tue, Jan 20, 2004 at 03:02:45PM -0600, Chris Boget wrote:
The data I'm working with looks like this:
table1.columnA = '1;3;4;6;8;9;12;13;14;15';
table2.columnA = '3';
table2.columnB = 'this';
I need to write a query that will do something along these lines:
SELECT * FROM table1, table2 WHERE
table2.columnB = 'this'
AND
table1.columnA LIKE CONCAT( '%', table2.columnA, '%' );
AND
INSTR(table1.columnA, table2.columnA)
Well, the only problem with going this route is that if table1.columnA 's value
was, instead, '1,4,6,8,9,13,14,15', that row would still match (when it really
shouldn't) because of the '13' within the string. The '3' from table2.columnA
is part of the table1.columnA string.
Try this:
INSTR(CONCAT(';',table1.columnA,';'),CONCAT(';',table2.columnA,';'))
Note that MySQL can't make use of any index here, so it will check all
n x m combinations, with n and m the number of records in table1 and
table2. You really want to do this for small tables only.
Consider an additional table to hold each of your 1, 3, 4, 6, ...
values together with the unique id of a record in table1.
Regards,
Fred.
--
Fred van Engen XB Networks B.V.
email: [EMAIL PROTECTED]Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query syntax help
On Tue, Jan 20, 2004 at 04:10:44PM -0600, Mike Blezien wrote: I've been looking at this SQL query a dozen times or more, but keep getting a syntax error message, Query: SELECT ai.affilid,ai.create_date,CONCAT(ai.fname,' ',ai.lname) AS name,aw.siteid,ai.email,as.username,as.status FROM affiliate_info ai,affiliate_signup as,affiliate_website aw AS is a reserved word. WHERE aw.siteid = 1000 AND ai.affilid = as.affilid AND aw.affilid = ai.affilid what is wrong with this query syntax ?? the syntax error is suppose to be in this area: `affiliate_website aw WHERE aw.siteid = 1000` A bit before that. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Automatic conversion from `char` TO `varchar`
On Sat, Jan 10, 2004 at 05:06:56PM +0500, Hassan Shaikh wrote: It's really strange but when I execute the following statement, all my char(10) columns turn into varchar(10). My other tables are ok and I've tried create dummy table also. Problem seems to be associated with this table only. Your records don't have a fixed size because of a text column. I believe this triggers MySQL to use a variable size for each column, converting char to varchar. Functionally, it shouldn't make a difference I guess? Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication syncronization lag.
Jonathan, On Thu, Jan 08, 2004 at 12:54:06PM +, Jonathan Tullett wrote: I am currently syncronizing two MySQL servers (version 3.23.49) on a very high traffic website. There are, at peak times upwards of 600 updates a second (and many many more selects) During these times the slave database will fall out of sync, sometimes by several thousand seconds (im aware that this calculation is the 'time now - timestamp of last update from the master) What is the status of the slave thread in show processlist? If it is 'Locked' then the slave thread may be starving because of too many (long) selects on the slave. My question is: Is there any way I can ensure that the databases have exactly the same data (ie, are in perfect sync), even if the overhead of this check means that the servers themselves operate slightly slower? If your my.cnf contains low-priority-updates and your slaves are serving selects continuously, then the updates won't come through. This setting is mentioned in the manual. If you remove low-priority-updates, the updates will be processed sooner but you'll get in trouble if you have long running selects. These will delay all following selects on the slave when an update is waiting for the long query to end. Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to use API to write blobs
On Fri, Dec 26, 2003 at 09:35:42PM -0500, Angus March wrote: Angus, On Fri, Dec 26, 2003 at 08:26:38PM -0500, Angus March wrote: I'm trying to use the API to write to a MEDIUMBLOB, but I'm getting a very vague error: something about a problem near '' on line 1. I'm forumating the query string w/ sprintf(query, INSERT INTO support_files (session_id,file_type,file_body) VALUES (%ld,%ld,, sessionID,fileType); Then w/calls to things like memcpy, and unformatted reads from a stream, I append the blob to the end of that, and finalize it w/a ')'. I'm very Well, your blob data may contain a NUL character, which will end your query string. It may contain quotes, a comma, ')' and other nasty stuff. You can't expect the MySQL parser to understand when these characters are part of your blob data and when they are meant to end your query or separate your query parameters. It is all just one single (long) query string that the parser needs to work with. So you will need to escape at least the following in your blob data: NUL because it is a C string terminator ' because it would terminate your blob 'string' [snip] Are you sure this is how it works? If you need to escape stuff, you aren't dealing with binary data, you are dealing with a character string. The first Correct. Queries are character strings, so you'll need to encode your binary data as such. part starts off as a character string (with the INSERT and everything) but the manual explicitly calls blob data in a query binary. Furthermore, it acknowledges the presence of string-unfriendly characters; specifically, the '\0'. Check out: http://www.mysql.com/documentation/mysql/bychapter/manual_Clients.html#mysql_real_query The quote I'm thinking of is: You must use mysql_real_query() rather than mysql_query() for queries that contain binary data, because binary data may contain the `\0' character That's a confusing statement IMHO. It may not be necessary to escape the NUL character, but you still need to create a valid query. There is no way to do that other than putting your binary data into a quoted string in your query. Again, how would the MySQL parser see where your binary data ends? How would the parser distinguish two binary values from one binary value? Binary is a property of the column. It has nothing to do with queries per se. Values in CHAR and VARCHAR columns are sorted and compared in case- insensitive fashion, unless the BINARY attribute was specified when the table was created. The BINARY attribute means that column values are sorted and compared in case-sensitive fashion according to the ASCII order of the machine where the MySQL server is running. BINARY doesn't affect how the column is stored or retrieved. (http://www.mysql.com/documentation/mysql/bychapter/index.html#CHAR) You might want to look at the example at this URL: http://www.mysql.com/documentation/mysql/bychapter/index.html#mysql_real_escape_string Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to use API to write blobs
Angus,
On Sat, Dec 27, 2003 at 11:09:54AM -0500, Angus March wrote:
On Fri, Dec 26, 2003 at 09:35:42PM -0500, Angus March wrote:
http://www.mysql.com/documentation/mysql/bychapter/manual_Clients.html#mysql_real_query
The quote I'm thinking of is:
You must use mysql_real_query() rather than mysql_query() for queries
that
contain binary data, because binary data may contain the `\0' character
That's a confusing statement IMHO. It may not be necessary to escape the
NUL character, but you still need to create a valid query. There is no
way to do that other than putting your binary data into a quoted string
in your query. Again, how would the MySQL parser see where your binary
data ends? How would the parser distinguish two binary values from one
By using the length parameter in the mysql_real_query() header.
The single length parameter would pass the total length of the query
string, not the length of each (or any) of its binary values.
You might want to look at the example at this URL:
http://www.mysql.com/documentation/mysql/bychapter/index.html#mysql_real_escape_string
I didn't notice this function before. That and mysql_real_query() don't
seem to be written with each other in mind. It even encodes the '\0', which
mysql_real_query() is said to be able to deal w/. In fact, why should I need
to use mysql_real_query() over mysql_query() if I use
mysql_real_escape_string()?
Right. I guess you would only need mysql_real_query if you do your own
escaping without escaping NUL characters or if you want to save a few
CPU cycles and already know the length. Look at the implementation of
mysql_query:
int STDCALL
mysql_query(MYSQL *mysql, const char *query)
{
return mysql_real_query(mysql,query, (uint) strlen(query));
}
Well, whatever the reason, I guess this is what API guys are expected to
use. I'll try that and see how it goes.
Regards,
Fred.
--
Fred van Engen XB Networks B.V.
email: [EMAIL PROTECTED]Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to use API to write blobs
Angus, On Fri, Dec 26, 2003 at 08:26:38PM -0500, Angus March wrote: I'm trying to use the API to write to a MEDIUMBLOB, but I'm getting a very vague error: something about a problem near '' on line 1. I'm forumating the query string w/ sprintf(query, INSERT INTO support_files (session_id,file_type,file_body) VALUES (%ld,%ld,, sessionID,fileType); Then w/calls to things like memcpy, and unformatted reads from a stream, I append the blob to the end of that, and finalize it w/a ')'. I'm very Well, your blob data may contain a NUL character, which will end your query string. It may contain quotes, a comma, ')' and other nasty stuff. You can't expect the MySQL parser to understand when these characters are part of your blob data and when they are meant to end your query or separate your query parameters. It is all just one single (long) query string that the parser needs to work with. So you will need to escape at least the following in your blob data: NUL because it is a C string terminator ' because it would terminate your blob 'string' And put the whole thing in single quotes. E.g. if your blob contains the following: NUL NUL ' a b c LF x Your query would look something like this: INSERT INTO support_files (session_id,file_type,file_body) VALUES (123,456,'\0\0\'abc\nx'); Or you can get away with leaving the linefeed as it is: INSERT INTO support_files (session_id,file_type,file_body) VALUES (123,456,'\0\0\'abc x'); Your mail reader may mess things up, but there is a single line-break in the example, just after abc. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication : blocking updates to slave
Eduardo, On Wed, Dec 03, 2003 at 10:25:28AM -0200, Eduardo D Piovesam wrote: We're using replication (1 master + 1 slave), and we want to block updates / deletes, issued by the users, to the slave. Because sometimes they get confused and use the slave as the master... got it? Give their account different GRANTs on the master than on the slave(s) and make sure that GRANTs (the `mysql` database) are not replicated from the master to the slaves. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication : blocking updates to slave
Eduardo, On Wed, Dec 03, 2003 at 10:56:56AM -0200, Eduardo D Piovesam wrote: But we want to replicate the mysql database... we want a exact copy of the master. You would just be missing the mysql.* tables and those are only used for permission related stuff. Permissions are exactly what you want to be different between slaves and master. Is there another way? Not that I know of. I just looked at the startup options but couldn't find anything for this purpose. Regards, Fred. - Original Message - From: Fred van Engen [EMAIL PROTECTED] To: Eduardo D Piovesam [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, December 03, 2003 10:32 AM Subject: Re: Replication : blocking updates to slave Eduardo, On Wed, Dec 03, 2003 at 10:25:28AM -0200, Eduardo D Piovesam wrote: We're using replication (1 master + 1 slave), and we want to block updates / deletes, issued by the users, to the slave. Because sometimes they get confused and use the slave as the master... got it? Give their account different GRANTs on the master than on the slave(s) and make sure that GRANTs (the `mysql` database) are not replicated from the master to the slaves. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange issue with UPDATE.
On Fri, Nov 21, 2003 at 11:26:57PM +0200, [EMAIL PROTECTED] wrote: My table is: CREATE TABLE `some` ( `id` bigint(20) NOT NULL auto_increment, `name` varchar(100) NOT NULL default '', `start_date` timestamp(14) NOT NULL, `end_date` timestamp(14) NOT NULL, [snip] And follow query: (1) UPDATE some SET current_tour='1' WHERE id = some_id This query always update start_date field with NOW() value, i.e. above query is treated from MySQL as: (2) UPDATE some SET start_date=NOW(),current_tour='1' WHERE id = some_id As documented here: http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#DATETIME TIMESTAMP behaviour when not running in MAXDB mode The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is updated automatically. Use DATETIME instead of TIMESTAMP if you don't want this to happen. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Urgent Pl: problem with rename the field name: IN
On Fri, Nov 07, 2003 at 06:58:58PM -0500, karthikeyan wrote: how to rename a field name IN to something else.. I could not use alter table tbl_name change IN NEW mediumtext; I suppose this complains about a syntax error? You can use any reserved word as fieldname (or tablename) by putting backticks around it: alter table tbl_name change `IN` NEW mediumtext; Pl. suggest how to rename particular field name for all the tables in a database Repeat the alter table for each table. :/ Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exponentiation operator
On Wed, Oct 08, 2003 at 11:16:03AM +0200, Bob Brands wrote: What is the correct syntax for using a Exponentiation operator in MySQL? It's not an operator but a function. Look at EXP and POW in the docs: http://www.mysql.com/doc/en/Mathematical_functions.html Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: escaping slashes not present in table?
it's supposed to be (and not pay attention to the mean of what it's reading). so if a varchar(50) column has the data: `qwer'y\19o` then it knows that it should read and return 10 chars/bytes. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication and Auto_Increment
On Thu, Sep 18, 2003 at 01:14:47PM -0400, Christopher Ferry wrote:
We're running bi-directional replication between two servers running
MySQL 3.23.56.
One server being active the other a hot standby.
Wanting to switch to the hot standby I noticed that replication had
ceased on the HS server. The show slave status:
| 192.168.1.10 | mysql_replicator | 3309| 60|
hostname-bin.075 | 1159 | No
| | | 1062 | error
'Duplicate entry '11609031'
for key 1' on query 'insert into CLOCK_INSTALL (URL, MACHINE_ID,
INSTALL_TIME,REMOTE_IP,LASTURL) values
('FRZE1002','81E7A3FBE4A64E6A8CDE92EB00B4D8B0',Now(),'127.0.0.1','')' |
0|
The install_id('11609031') for the table is of auto_increment and that
id is already present in the HS server.
The previous entry '11609030' is the same accross the servers. How
could this happen and why doesn't mysql just increment to '11609032' for
this insert?
It doesn't use auto increment on the slave because the record wouldn't
be identical to the master's record.
The replication has not been touched for weeks and it was working
properly when it was set up. The HS was never in an active situation so
corruption is ruled out on its end.
Are you sure that no insert was done on the HS? You can check this in
its binlog with mysqlbinlog. Look for the server id in the output
comments like this:
#030815 11:59:59 server id 1 Query thread_id=135377exec_time=0
error_code=0
Did the HS ever crash or reboot? Did you ever change its server id? Just
guessing here.
I'd like to solve this without downtime to the primary server.
If there really were two distinct records (one on the active and the
other on the HS) you will need to remove the record on the HS and fix
anything that may depend on this record. Only then can it resume
replication.
If there are too many conflicts, you can:
- shut down MySQL on the HS
- FLUSH TABLES WITH READ LOCK on the active server
- copy all tables on the active server somewhere that takes the least
time (to minimize the 'downtime' of the active server)
- note the last binlog file and size on the active server
- UNLOCK TABLES on the active server
- move the copy to the HS
- update the HS's master.info with the active server's binlog file and
size
- restart MySQL on the HS
Maybe there's an easier way, but this is (from memory) what I've used
before with 3.23.xx.
Fred.
--
Fred van Engen XB Networks B.V.
email: [EMAIL PROTECTED]Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: connection error
Robert, On Sat, Sep 13, 2003 at 08:08:48PM +1200, Robert Morgan wrote: I have created a user grant all privileges on *.* to user@% =identified by ' pword' with grant option; and grant all privileges on *.* to [EMAIL PROTECTED] =identified by ' pword' with grant option; Did you actually use a space in ' pword' ? You could try changing it to a password without a space in it. but when I try and connect through mysql client(DBTools) on remote PC I get the 'access denied for user: [EMAIL PROTECTED]' (using password:yes). This suggest you tried to log on with a username 'user%' instead of 'user'. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IP Address Conversion
Hi, On Wed, Sep 10, 2003 at 09:07:30AM -0400, Mark Riehl wrote: All - We're using MySQL under Linux to store data collected during network tests. One of our columns store the integer representation (in network byte order) of an IP address. Is there any current support in MySQL (or future planned support) to convert from the integer representation of an IP address to a dotted quad (e.g., 192.168.1.1) representation. Currently, we're doing this with a Perl script as part of our post test analysis. Look for INET_NTOA / INET_ATON in the manual. Make sure your fields are unsigned ints. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Possible: Update query within another query's loop?
Mark,
On Thu, Aug 28, 2003 at 09:59:18AM -0400, Mark Richards wrote:
I am still quite new to MySQL and have a basic question. I am using PHP, so
forgive me if this is more a PHP issue.
I want to perform an update to a specific record based on a condition. In
the outer loop, I have Query1, which returns set Result1. Inside this loop,
I run an UPDATE query which returns Result2.
// executed first query.
while ($row = mysql_fetch_assoc($result1))
{
// get the record ID for the row we are on.
$recid = $row[id];
// construct a new query
$q2 =UPDATE `table` SET `review` = 1 where id =
'.$recid.';;
$result2 = mysql_query($q2)
}
Is this a valid use of MySQL? I am concerned that executing the update
query might somehow effect the first one that is used in the outer loop.
You need to make a separate connection to MySQL for the outer query to
prevent the inner query from messing up the outer query's result set.
See the PHP manual for obtaining the connection id's from mysql_connect
and using them with other mysql functions.
Regards,
Fred.
--
Fred van Engen XB Networks B.V.
email: [EMAIL PROTECTED]Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Possible: Update query within another query's loop?
Antony, On Thu, Aug 28, 2003 at 06:29:54PM +0400, Antony Dovgal wrote: On Thu, 28 Aug 2003 16:23:35 +0200 Fred van Engen [EMAIL PROTECTED] wrote: You need to make a separate connection to MySQL for the outer query to prevent the inner query from messing up the outer query's result set. See the PHP manual for obtaining the connection id's from mysql_connect and using them with other mysql functions. ouch! it's a kinda bad advice. Please elaborate. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Possible: Update query within another query's loop?
Antony, On Thu, Aug 28, 2003 at 06:45:27PM +0400, Antony Dovgal wrote: On Thu, 28 Aug 2003 16:37:41 +0200 Fred van Engen [EMAIL PROTECTED] wrote: Please elaborate. I've already answered: On the list I just saw your 'bad advice' message without explanation. On Thu, 28 Aug 2003 17:07:19 +0400 Antony Dovgal [EMAIL PROTECTED] wrote: You don't need execute UPDATE's in the loop in this case. Correct. and you don't need to make another one connection, of course. In the general case you may need to, but not in the usual case in which you use mysql_query. I thought you did, but just double-checked. You only need to if you use mysql_unbuffered_query and don't read all results first. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 3.23.54 + update
On Mon, Aug 25, 2003 at 07:10:02PM -0400, Dan Jones wrote: On Thu, 2003-08-21 at 20:21, Taylor Sittler wrote: Is there any way to update table values based on values in another table? For instance, given: table (column1,column2..) Table 1 (jobid, jobname) Table 2 (person, jobid, jobname) could I update Table 2, setting jobname=Table1.jobname where Table1.jobid=Table.jobid? Is it possible to do this in one SQL command, so that SQL matches the updating set to the stored set? UPDATE Table1, Table2 SET Table2.jobname=Table1.jobname WHERE Table2.jobid=Table1.jobid; Not in 3.23.xx. You would need to do this from a programming language or possibly by creative use of temporary tables (SELECT INTO temp ...; REPLACE INTO `Table 2`(...) SELECT * FROM temp; with appropriate locking). Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: finding out what localhost is
On Sun, Aug 24, 2003 at 07:44:10AM +0100, Pag wrote: localhost is used in mysql to refer to the Unix domain socket. For security, they may not even provide remote TCP access to the mysql server. If SHOW VARIABLES LIKE 'skip_networking' returns ON, then TCP access has been disabled. Otherwise, SHOW VARIABLES LIKE 'port' will tell you what port number it's listening on. Already checked the variables, the skip_network is OFF, and the port is shown. I assume its then possible to remotely connect to the database? BUt how do i know where to connect to? Thats my doubt? By domain name? By ip? Nothing seems to work. The server should be the same as the one running your website. You are probably being blocked by their firewall as a security measure. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL running out of date
On Fri, Aug 22, 2003 at 01:16:48AM -0500, Hans van Harten wrote:
Rajesh Kumar wrote:
Peter Brawley unknowingly asked us:
Interestingly, use of date_add() and date_sub() on 'odd' dates such
as Feb 31 does produce sane results.
Subtract one from 2000 Feb 31, and you'll get 2000-03-01.
This is sane!!??
This is where Unix Timestamps come into action (and perhaps rescue)!
To be sure that we're storing a sane value in our DB, we could use:
SELECT unix_timestamp('2002-02-31');
which provides a correct result.
Some interesting results:
We could then probably eliminate all timestamps stored as 1969-12-31
as we know that they're illegal.
Uh, isn't it an signed integer, thus allowing date before 1970-01-01 too??
Anyway, all this led me towards is:
create database data_test ;
use data_test;
create table test3 (a date);
insert into test3 values (
from_unixtime(unix_timestamp('2002-102-31'),'%Y-%m-%d' ));
insert into test3 values (
from_unixtime(unix_timestamp('1996-02-31'),'%Y-%m-%d' ));
insert into test3 values (
from_unixtime(unix_timestamp('1996-67-31'),'%Y-%m-%d' ));
insert into test3 values ( from_unixtime(-2147483646));
select * from test3;
still resulting in a mash:
1970-01-01
1996-03-01
1970-01-01
1900-01-00
that comes with neither warnings nor errors reported ...
Error reports would have been nice, but why does your application supply
these incorrect dates anyway? Probably because some user typed them. And
what are you going to tell the user when you get a generic MySQL error
for an INSERT with many values? 'Oops, something went wrong'?
Just check the values in your application, like you should do for any
value coming from an external source. Then you can tell the user exactly
what went wrong for each field. It also prevents garbage or badly
formatted data from entering your database.
Regards,
Fred.
--
Fred van Engen XB Networks B.V.
email: [EMAIL PROTECTED]Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fw: Replication
Sam, On Fri, Aug 22, 2003 at 08:37:33PM -0400, sam [EMAIL PROTECTED] wrote: So, you are saying that the fact that replication is not working when we re-connect is not a bug. Replication is all about passing data from one node (a master) to the next (the slave). I do not know what it is you want. Simply when we re-connect we expect it to replicate. what you did (took the slave off line by pulling the RJ45), what you wanted to happen (replication to occur), and what actually happened (nothing - it did not replicate) I am not look for support on how to set it up. This is a bug. I should be able to turn-off a slave and when it comes back up have it replicate with its master - correct? Sorry, I do not know why we are having a problem. I am simply stating the problem - if you need more, You need to tell what you need. See the section in the manual about replication features and problems: If connection to the master is lost, the slave will retry immediately, and then in case of failure every master-connect-retry (default 60) seconds. Because of this, it is safe to shut down the master, and then restart it after a while. The slave will also be able to deal with network connectivity outages. However, the slave will notice the network outage only after receiving no data from the master for slave_net_timeout seconds. So if your outages are short, you may want to decrease slave_net_timeout ; see section 4.5.7.4 SHOW VARIABLES. http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Database_Administration.html#Replication_Features Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL running out of date
On Sat, Aug 23, 2003 at 10:54:32AM +0200, Hans van Harten wrote: Fred van Engen wrote: On Fri, Aug 22, 2003 at 01:16:48AM -0500, Hans van Harten wrote: that comes with neither warnings nor errors reported ... Error reports would have been nice, but why does your application supply these incorrect dates anyway? Probably because some user typed them. And what are you going to tell the user when you get a generic MySQL error for an INSERT with many values? 'Oops, something went wrong'? Planned to dump it into MySQL and check only if MySQL croaked about it. Just check the values in your application, like you should do for any value coming from an external source. Then you can tell the user exactly what went wrong for each field. It also prevents garbage or badly formatted data from entering your database. My checks might not match those of (the next version of) MySQL and at that time the difference in thoughts will pass unnoticed ! I agree that MySQL should complain but I'm not sure it should fail. The problem is that MySQL has always behaved this way and is in many cases documented to do so. Some programs may expect MySQL to do clipping of large values (your example in another post) and will fail if this changes. Valid dates are clearly defined independently of MySQL. Just don't feed MySQL anything else. If the presentation changes (-MM-DD, DD-MM- or MM-DD-) that would need to be documented anyway. Valid ranges for any field are defined in your table definitions and the absolute limits are defined in the MySQL documentation. If MySQL should fail on a simple INSERT with out-of-bounds values, it should also fail when the out-of-bounds value is generated in a complex query involving expressions with fields (or subqueries). How would you know for which records an UPDATE or INSERT failed? Would you want it to fail the entire query and not just problematic records or even fields? That would hardly be possible with MyISAM tables and would need some kind of subtransactions in any transactioned tables. Partly failing is silly IMHO and failing completely is difficult. So I guess it should just warn you that you did something silly. If you use transactions, you can decide to abort or let MySQL decide that. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL running out of date
On Sat, Aug 23, 2003 at 02:07:36PM +0200, Hans van Harten wrote: Fred van Engen wrote: On Sat, Aug 23, 2003 at 10:54:32AM +0200, Hans van Harten wrote: My checks might not match those of (the next version of) MySQL and at that time the difference in thoughts will pass unnoticed ! I agree that MySQL should complain but I'm not sure it should fail. The problem is that MySQL has always behaved this way and is in many cases documented to do so. Some programs may expect MySQL to do clipping of large values (your example in another post) and will fail if this changes. Preserving the good old installed base, I cannot agree more!! Well, the behaviour could change in a major release (5.0 ?) but not in a minor one (3.23.xx or 4.0.xx). If MySQL should fail on a simple INSERT with out-of-bounds values, it should also fail when the out-of-bounds value is generated in a complex query involving expressions with fields (or subqueries). Right ... 2^66 insert test (FUN ) value ( '25' ); select * from test where fun2147483648*2147483648*16; return 0 records. SELECT 2147483648*2147483648*4 returns 0. SELECT 2147483648*2147483648*2 returns -9223372036854775808. SELECT 2147483648*2147483648*2-1 returns 9223372036854775807. The same problem in most programming languages. What do other DBMS do and what do the SQL standards say? I really don't know. How would you know for which records an UPDATE or INSERT failed? Would you want it to fail the entire query and not just problematic records or even fields? Ordinary, UPDATE or INSERT would do one record at a time. INSERT INTO test(id, myint) VALUES (1,2147483647), (2,2147483648); The second is out-of-bounds. Should the first be revoked? INSERT INTO test(id, myint) VALUES (1,2147483646), (2,2147483647); UPDATE test SET myint = myint+1; INSERT INTO test(id, myint) VALUES (1,2147483646), (2,2147483647); INSERT into test2 SELECT id, myint+1 FROM test; The UPDATE and last INSERT are out-of-bounds for the second record. Should the update and insert of the first record be revoked? That's just too much work for current MyISAM tables. BTW INSERT -or REPLACE- do croak about misfits while using FKs and then do not process any field -and none of the other records, if you used an record set- Great. That's InnoDB, which could do the same for each of the earlier examples. People might expect that from transactioned tables. For other table types I guess it would be unrealistic. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: compile mysql meet problem
Hi, On Sat, Aug 16, 2003 at 04:29:01AM +0200, albert wrote: Now i'm trying to install MySQL 4.0.14 through binary source install. I'm using Redhat 7.2 and 8.0 I'm trying like this.. ./configure --prefix=/usr/local/mysql \ --localstatedir=/usr/local/mysql/data \ --with-charset=big5 make so far there is no problems. but when i try like 'make install',i got this error message and Installation is stopped. ... /usr/bin/install -c copy-db /usr/local/mysql/sql-bench/copy-db /usr/bin/install: `copy-db' and `/usr/local/mysql/sql-bench/copy-db' are the same file make[2]: *** [install-benchSCRIPTS] error1 make[2]: exit `/usr/local/mysql/sql-bench' directory make[1]: *** [install-am] error 2 make[1]: exit `/usr/local/mysql/sql-bench' directory make: *** [install-recursive] error 1 [EMAIL PROTECTED] mysql]# It seems like you are (compiling and) installing _from_ /usr/local/mysql and also _to_ /usr/local/mysql. You should compile and install from any directory you choose, but it shouldn't be the same as the directory you specify as destination with configure --prefix. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Matching escaped strings
On Wed, Aug 13, 2003 at 09:23:38AM -0500, Jay Blanchard wrote: [snip] ID| Name 1 | Author\'s As you can see, the name value has been escaped. Now, the question is, how do you match on a value that has escaped charaters? I've tried the following SELECT * FROM table WHERE Name = 'Author\'s' SELECT * FROM table WHERE Name LIKE 'Author\'s' SELECT * FROM table WHERE Name = '%Author\'s%' SELECT * FROM table WHERE Name = 'Author''s' SELECT * FROM table WHERE Name = Author\'s SELECT * FROM table WHERE Name = Author's [/snip] The only one you didn't try SELECT * FROM table WHERE Name LIKE 'Author%' Or any of these, which are more specific: SELECT * FROM table WHERE Name = 'Author\\\'s' SELECT * FROM table WHERE Name = Author\\'s You need to escape the \ because it is the escape character and you need to escape the ' in the first case because it is a string terminator. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A final Windows MySQL PHP plea
Hi, On Tue, Aug 12, 2003 at 05:21:23PM +0100, Gary Broughton wrote: Is there anybody out there who has managed to successfully configure Win2000, IIS5, MySQL 4.0.14 and PHP 4.3.2 (ISAPI) to work with a couple of hundred users at any one time? I have chucked absolutely everything I can think of at this, but the MySQL (it seems) simply eats all the available CPU within a short space of time (regardless of users) and brings the site to a halt. You mention that this ran fine before. What was your previous setup? Did you switch from e.g. MS-SQL to MySQL? From MySQL 3.x to 4.x? You mention ASP vs PHP so your scripts must have changed. Did the queries change? Anyway, I run MySQL 3.23.x on Solaris, so I can't help you on specifics with your setup. But no amount of hardware will help against lacking optimization. So once you've made sure that the CPU time is actually used by MySQL, you might try to put log-slow-queries in your config. It will log slow queries in hostname-slow.log with 'slow queries' being defined as queries that run for more than 10 seconds (default). You can also try 'show processlist' from the MySQL client to see which queries are running and which queries are blocked because of locks by running queries. Once you have found slow queries, run EXPLAIN on them to determine if and how they are optimized. The manual has lots of info on optimization. If MySQL picks the wrong index, try using SELECT USE INDEX (...). Sometimes adding (redundant) fields containing a calculated value will allow you to create a good index (at the cost of making sure that the added field will remain consistent). There are lots of possibilities. Hope this helps. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mutual declarations produce Error 1064
Morten,
In your extremely long mail, I think I managed to find your question and
removed all other stuff.
On Wed, Aug 13, 2003 at 03:18:26PM +0200, Morten Gulbrandsen wrote:
mysql
mysql # from now on the coding causes trouble
mysql
mysql ALTER TABLE EMPLOYEE
- FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN)
- ON DELETE SET NULL
- ON UPDATE CASCADE;
ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresp
onds to your MySQL server version for the right syntax to use near
'FOREIGN KEY
(SUPERSSN) REFERENCES EMPLOYEE(SSN)
ON DELETE SET
mysql
6.5.4 ALTER TABLE Syntax
ALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification ...]
alter_specification:
ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
| ADD [COLUMN] (create_definition, create_definition,...)
| ADD INDEX [index_name] (index_col_name,...)
| ADD PRIMARY KEY (index_col_name,...)
| ADD UNIQUE [index_name] (index_col_name,...)
| ADD FULLTEXT [index_name] (index_col_name,...)
| ADD [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...)
[reference_definition]
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] old_col_name create_definition
[FIRST | AFTER column_name]
| MODIFY [COLUMN] create_definition [FIRST | AFTER column_name]
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP INDEX index_name
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO] new_tbl_name
| ORDER BY col
| table_options
So what it clearly tells you, is to use this (note the 'ADD'):
mysql ALTER TABLE EMPLOYEE
- ADD FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN)
- ON DELETE SET NULL
- ON UPDATE CASCADE;
Regards,
Fred.
--
Fred van Engen XB Networks B.V.
email: [EMAIL PROTECTED]Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using query file from console
Hi, On Fri, Aug 01, 2003 at 04:55:09PM +0200, Lorenzo Rossi wrote: I need to run a query written in a file from inside mysql console, not shell prompt. Anyone can help me? mysql help MySQL commands: Note that all text commands must be first on line and end with ';' help(\h)Display this help. ? (\?)Synonym for `help'. clear (\c)Clear command. connect (\r)Reconnect to the server. Optional arguments are db and host. edit(\e)Edit command with $EDITOR. ego (\G)Send command to mysql server, display result vertically. exit(\q)Exit mysql. Same as quit. go (\g)Send command to mysql server. nopager (\n)Disable pager, print to stdout. notee (\t)Don't write into outfile. pager (\P)Set PAGER [to_pager]. Print the query results via PAGER. print (\p)Print current command. quit(\q)Quit mysql. rehash (\#)Rebuild completion hash. source (\.)Execute a SQL script file. Takes a file name as an argument. status (\s)Get status information from the server. tee (\T)Set outfile [to_outfile]. Append everything into given outfile. use (\u)Use another database. Takes database name as argument. Connection id: 171359 (Can be used with mysqladmin kill) mysql So it looks like you could try the 'source' command. Copying the query through your clipboard is another option. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help in querying two tables
Hi, On Fri, Aug 01, 2003 at 11:55:36PM +0800, Jaime Teng wrote: Now, I have two of these tables (archivetable,currenttable). My problem is how do I perform a single query such that I get results from these two tables: mysql select * from archivetable,currenttable; +++-+-+ | sessionid | username | logon | logoff | +++-+-+ | 03 | dangco77 | 1996-09-25 20:51:59 | 1996-09-25 21:07:00 | | 06 | mccarthy | 1996-09-26 06:15:35 | 1996-09-26 06:20:00 | | 07 | sigmaph| 1996-09-26 06:25:48 | 1996-09-26 06:28:00 | | 09 | sigmaph| 1996-09-26 08:31:53 | 1996-09-26 08:51:00 | | 1000265891 | okame | 2003-08-01 13:38:24 | 2003-08-01 13:43:42 | | 1000265893 | kbs| 2003-08-01 13:38:30 | 2003-08-01 13:38:48 | | 1000265897 | bdo-albaro | 2003-08-01 13:38:54 | 2003-08-01 14:07:06 | +++-+-+ of course that last query isnt correct but thats the result I want. You can use a UNION to do this, but you need MySQL 4.x. It won't work in 3.23.x or before. Look in the manual for details. any suggestion? I read about using JOIN but I have no idea how to\ make it work for my need. Joins are used for combining records from multiple tables, which is not what you seem to want to do. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I Specify explicitly TYPE = InnoDB But My SQL gives me TYPE=MyISAM ?
Hi, On Thu, Jul 31, 2003 at 02:46:06PM +0200, Morten Gulbrandsen wrote: According to the manual : 7.5.5.2 Foreign Key Constraints Starting from version 3.23.43b InnoDB features foreign key constraints. InnoDB is the first MySQL table type which allows you to define foreign key constraints to guard the integrity of your data. TYPE = InnoDB only works if MySQL is compiled with InnoDB support, which is the default in binaries from MySQL. If you compile it yourself you need to configure --with-innodb . Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I Specify explicitly TYPE = InnoDB But My SQL gives me TYPE=MyISAM ?
Morten, On Thu, Jul 31, 2003 at 03:27:51PM +0200, Morten Gulbrandsen wrote: according to what I experience, I do definitely run the default binaries, perhaps the daemon mysqld has to be started with an option in order to support InnoDB ? How can this be investigated ? Well, I never used InnoDB, but have it compiled because I intend to do so sometime. What I see in the error log (hostname.err on Unix et.al.) is this: 030728 05:26:17 mysqld started Cannot initialize InnoDB as 'innodb_data_file_path' is not set. If you do not want to use transactional InnoDB tables, add a line skip-innodb to the [mysqld] section of init parameters in your my.cnf or my.ini. If you want to use InnoDB tables, add to the [mysqld] section, for example, innodb_data_file_path = ibdata1:10M:autoextend But to get good performance you should adjust for your hardware the InnoDB startup options listed in section 2 at http://www.innodb.com/ibman.html /opt/mysql-3.23.56/libexec/mysqld: ready for connections So, look for something similar in your log. If it is there, then make sure that you have innodb configured in my.cnf. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL multiple query in php script. (newbie)
Barry,
On Thu, Jul 24, 2003 at 11:22:28PM -0500, Barry Hayden wrote:
I have to admit I am very much a newbie at MySQL in general (but I'm
working on it).
I have a fairly complex (for me) query that I am trying to complete
using php. I can't get it to work, and I'm sure it's just in the way I
am phrasing the MySQL part of this. The premise of this is a four
question form that acts as a search engine to draw the appropriate
information out of the db. If anyone could help I would really
appreciate it. Here's what I have.
?php
$conn = mysql_connect($server, $user, $pass);
$select = mysql_select_db(realty, $conn);
$sql = SELECT Realtor, HouseAddress, Bathrooms, Bedrooms, YearBuilt,
Garage, Location, Basement, Info FROM properties
WHERE Bathrooms =='.$a.', Bedrooms =='.$b.', Garage
=='.$c.',
Basement =='.$d.';
Use = for equality tests in MySQL, not == like in most programming
languages.
Check for errors with mysql_error().
$result = mysql_query($sql, $conn);
while ($record = mysql_fetch_assoc($result)){
while (list($fieldvalue) = each ($record))
echo $fieldname.;
I'm not sure what the point (.) is doing here.
Check for error messages in the webserver logs.
}
if (mysql_num_rows($result) ==0){
echo Sorry, no matching results.;
}
?
Regards,
Fred.
--
Fred van Engen XB Networks B.V.
email: [EMAIL PROTECTED]Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to limit COUNT(*)
Hi, On Tue, Jul 22, 2003 at 09:40:31AM -0400, Mojtaba Faridzad wrote: for example: SELECT COUNT(*) as numfound FROM mytable WHERE mycondition; in this query, mytable and mycondition are variable and on run time, they are changed. I use this query to jump to the last page of a grid form. sometimes the query may have more than million records and I want to give a warning to the user to specify a condition to limit the number of records. I would like to count the records upto 1 (for example) and if the records are more than this, stop counting and ask user to change the condition. to solve this problem I did something like this: SELECT one_field FROM mytable WHERE mycondition LIMIT 10001; if the number of records of this query is equal to 10001, then I show the warning message. but this query is not as fast of COUNT query. is there any way to limit the first query? My best attempt would be: SELECT 1 FROM mytable WHERE mycondition LIMIT 10001; Then get the result count without getting the actual results :( What you gain here is that MySQL will use just an index file if it can. By querying for 'one_field', it would use the data table if 'one_field' is not part of the index that is used for evaluating 'mycondition'. Are you sure that this query helps? Depending on 'mycondition', it might not be possible to use an index anyway. All records would need to be checked, even if the number of matches were less than 10001. If your query is guaranteed to use an index, you add ORDER BY ... DESC combined with a LIMIT to go to the last page of your form. You won't know the actual count to display then. Regards, Fred. - Original Message - From: gerald_clark [EMAIL PROTECTED] To: Mojtaba Faridzad [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, July 22, 2003 9:15 AM Subject: Re: how to limit COUNT(*) Perhaps you could post some examples of what you have tried. I don't understand what you are asking. Mojtaba Faridzad wrote: Hi, I guess there is no way to limit COUNT(*). Is that right? We cannot use the result of COUNT in WHERE condition or LIMIT doesn't help. In this case so far I have retrieved a field and used LIMIT. Is there a better way to control it? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to limit COUNT(*)
Hi, On Tue, Jul 22, 2003 at 11:30:47AM -0400, Mojtaba Faridzad wrote: thanks Fred! that's better. actully I took a field with one character but it's better to run SELECT '1' FROM as I know, if there is not ORDER BY in the query, mySQL doesn't need to check all records and retreives LIMIT number of them. I checked speed with limit and without limit, limit was faster. Correct, but you might want to check that MySQL uses an index to find those records. If it needs to do a full table scan to find the 10001st matching record at the end of the table, you've gained very little. That was the point I tried to make earlier. You can even use an ORDER BY combined with a LIMIT if you make sure that the optimizer uses the same index for the ORDER BY and the evaluation of your conditions. If it doesn't, it will first select all records, sort them and return the first 10001. Use EXPLAIN to find out what it does with your set of 'mycondition'. You may want to limit your user's ability to influence 'mycondition' to things that optimize well. Also, optimization depends on the size and content of your tables, so you want to try this with real-world data. If the optimizer chooses the wrong index, you can tell it from which indexes to choose by using 'USE (myindex)'. Use this e.g. to force it to use the same index as for an ORDER BY. If it decides to use NO index, I think you have a problem. Regards, Fred. - Original Message - From: Fred van Engen [EMAIL PROTECTED] To: Mojtaba Faridzad [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, July 22, 2003 10:52 AM Subject: Re: how to limit COUNT(*) Hi, On Tue, Jul 22, 2003 at 09:40:31AM -0400, Mojtaba Faridzad wrote: for example: SELECT COUNT(*) as numfound FROM mytable WHERE mycondition; in this query, mytable and mycondition are variable and on run time, they are changed. I use this query to jump to the last page of a grid form. sometimes the query may have more than million records and I want to give a warning to the user to specify a condition to limit the number of records. I would like to count the records upto 1 (for example) and if the records are more than this, stop counting and ask user to change the condition. to solve this problem I did something like this: SELECT one_field FROM mytable WHERE mycondition LIMIT 10001; if the number of records of this query is equal to 10001, then I show the warning message. but this query is not as fast of COUNT query. is there any way to limit the first query? My best attempt would be: SELECT 1 FROM mytable WHERE mycondition LIMIT 10001; Then get the result count without getting the actual results :( What you gain here is that MySQL will use just an index file if it can. By querying for 'one_field', it would use the data table if 'one_field' is not part of the index that is used for evaluating 'mycondition'. Are you sure that this query helps? Depending on 'mycondition', it might not be possible to use an index anyway. All records would need to be checked, even if the number of matches were less than 10001. If your query is guaranteed to use an index, you add ORDER BY ... DESC combined with a LIMIT to go to the last page of your form. You won't know the actual count to display then. Regards, Fred. - Original Message - From: gerald_clark [EMAIL PROTECTED] To: Mojtaba Faridzad [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, July 22, 2003 9:15 AM Subject: Re: how to limit COUNT(*) Perhaps you could post some examples of what you have tried. I don't understand what you are asking. Mojtaba Faridzad wrote: Hi, I guess there is no way to limit COUNT(*). Is that right? We cannot use the result of COUNT in WHERE condition or LIMIT doesn't help. In this case so far I have retrieved a field and used LIMIT. Is there a better way to control it? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424
Re: between A and B with another condition?
Hi, On Tue, Jul 22, 2003 at 02:37:14AM -0500, Lingua2001 wrote: How can I extract values for members whose level is below '3', and their names are between the alphabets 'a' and 'b' ? For example, one of the results should be 'Alex', whose level is '1'. Or 'Bob', whose level is '2'. But not Charlie, or Tom...etc. I tried a query like SELECT uid FROM members WHERE level '1' AND name between 'a' and 'b', I guess your query should work, with 'level 3' of course. SELECT uid FROM members WHERE level 3 AND name between 'a' and 'b', but it does not work Show us your sample data (SELECT uid, level, name FROM members) and the results you get for the query that doesn't work. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple group by taking way too long..
On Sun, Jul 13, 2003 at 08:51:15AM -0700, Henry Hank wrote: I have the following SQL. The source table has 8.1 million rows, and the resulting table will have about 7.9 million rows. I know that's not much of a decrease, but the logic is correct as I've tested it on smaller sets. The problem is that when I run with the full set of 8 million rows, it takes about 2 hours to complete. The source and target tables are all char or tinyint fields (i.e. fixed length records). insert into extract2 select field1,field2,field3,field4, if(right(field1,1)='N',mid(field3,2,1),mid(field3,1,1)) as flag, count(*) as count, val-min(val_cnt) as cnt1, if(max(val)val_cnt,1,0) as cnt2 , if(max(val)=min(val) and max(val)=val_cnt,1,0) as last, if(min(val)=1,1,0) as initial from extract1 group by field1,field2,field3,field4; While this code is running, the temp table that is created to do the summary grows to 730,662,620 bytes, which is 22 million records at 33 bytes per record. Why is mysql creating a temp table of 22 million records, when the SOURCE table is only 8.1 million records? Even if no summary was taking place at all, I wouldn't expect the temp table to be almost three times the size. Is 33 bytes the size of records in extract1? They probably have another size in your temporary table because it has other fields. It shouldn't have more records than the source table, so your record size appears to be more like 92 bytes. If extract2 has smaller records than this, my guess is that MySQL for some reason decides to use ints instead of tinyints in the temp table. It probably allows for NULLs in some fields in your temp table as well. Not much you can do about it, but just to make sure you're aware of this. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: The difference between 1.9G and 2.2G table when create Index
On Sat, Jul 05, 2003 at 01:03:17PM +, James Yang wrote: I have a MYISAM table, which is about 2.2G and 19,000,000 records. When I create indexs for it, I couldn't finish after 4 hours. You mean it was still running? Was there still disk or CPU activity after 4 hours? But when I reduce the records to 16,000,000, about 1.9G, the same index can be finished within 35 minutes. Can anyone explain it? Thanks in advance. That's much shorter than 4 hours! Maybe your disk was full when creating the 2.2G table's index? MySQL waits until disk space becomes available again. You wouldn't see much disk activity while it is waiting. Keep in mind that MySQL makes a copy of your table while it is adding the index. You'll need twice the space of your initial table, plus space for the additional index. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: The difference between 1.9G and 2.2G table when create Index
On Sat, Jul 05, 2003 at 03:16:11PM +0200, Fred van Engen wrote: On Sat, Jul 05, 2003 at 01:03:17PM +, James Yang wrote: I have a MYISAM table, which is about 2.2G and 19,000,000 records. When I create indexs for it, I couldn't finish after 4 hours. You mean it was still running? Was there still disk or CPU activity after 4 hours? But when I reduce the records to 16,000,000, about 1.9G, the same index can be finished within 35 minutes. Can anyone explain it? Thanks in advance. That's much shorter than 4 hours! Maybe your disk was full when creating the 2.2G table's index? MySQL waits until disk space becomes available again. You wouldn't see much disk activity while it is waiting. Keep in mind that MySQL makes a copy of your table while it is adding the index. You'll need twice the space of your initial table, plus space for the additional index. Check this URL for details: http://www.mysql.com/doc/en/Full_disk.html There seems to be an exception to this rule when an index is added, so I may be wrong on this. The adding of an index happens in two phases and the docs are not clear to me about the first (copying) phase however. Your earlier comment indicates it is in the first phase still. I am using PIII 550, RedHat 8.0, with 1G Memory and IDE Hard Disk.The database is stored at ext3 file system. When I use show processlist for the 2.2G indexs, it tell me Copying to tmp table. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1999
On Tue, Jul 01, 2003 at 04:14:17PM +0200, Maunza Kater wrote: Which Version of MySQL was used in 1999? Looking at the MySQL Change History in the docs: - 3.22.7 went gamma in februari 1999 (some people would use it) - 3.22.19 was the first production release in march 1999 - 3.23.0 was the first alpha release in august 1999 (few people would use that) I see no dates for 3.21.x, but it should have been the production release in the beginning of 1999 and was probably supported for a while after 3.22.x was the production release in march. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 2013:Lost connection to MySQL server
Hi, On Tue, Jul 01, 2003 at 02:13:23PM -0500, Miguel Perez wrote: I have the following problem, when I try to connect to my DB servicios I get connected succesfully, but when I try to access certain tables I get the error ERROR 2013: Lost connection to MySQL server during query but only with those certain tables, to be more specific there's only 2 tables that i can't access, I get that error, the other ones works fine. Which version, which OS? Any ideas or suggestions. Maybe mysqld crashes? It restarts automatically, on unices at least. Check the mysql error log to find out if it crashed. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 2013:Lost connection to MySQL server
On Tue, Jul 01, 2003 at 04:09:26PM -0500, Miguel Perez wrote: The OS I'm using is Red Hat 7.3, and the mysql version is 4.0 The wierd thing is that I could access those tables but just after I rebooted my PC I couldn't access the 2 tables. The log files has the following: 030701 14:27:38 InnoDB: Assertion failure in thread 36874 in file dict0load.c line 677 You don't write which exact version you use, but the changelist in the MySQL documentation mentions a fix for an assertion in dict0load.c in version MySQL/InnoDB-4.0.13. mysql select version(); If you use an earlier version than 4.0.13, you could try to upgrade. InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] Well, you did that now :) I don't use InnoDB currently, so maybe someone else will respond. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SUM help needed
On Fri, Jun 27, 2003 at 09:05:48AM -0400, Moulder, Glen wrote: G'day all, Having trouble with SUM, could use a hand. I inherited a table, Funds_Recd that looks like this: ID Job Sub Task Amt 1A01 A1910,001.00 102 B53 A201 79.47 213 X99 L8838,289.02 284 A01 A19126.98 I do not have ownership and *MAY NOT* change the structure. I've another table, Jobs, to which the Funds_Recd recs must be related: ID Job Sub Task 87 A01 A19 929 B53 A201 998 X99 L88 This query works -- SELECT Jobs.Job, Jobs.Sub, Jobs.Task, Funds_Recd.Amt FROM Jobs, Funds_Recd WHERE Jobs.Job=Funds_Recd.Job and Jobs.Sub=Funds_Recd.Sub and Jobs.Task=Funds_Recd.Task ORDER BY Jobs.Job; but produces a list of jobs with individual rows for the various funds amounts. I'm trying to get just a total of the funds amount for a Job, Sub and Task like this -- SELECT Jobs.Job, Jobs.Sub, Jobs.Task, sum(Funds_Recd.Amt) AS Amount FROM Jobs, Funds_Recd WHERE Jobs.Job=Funds_Recd.Job and Jobs.Sub=Funds_Recd.Sub and Jobs.Task=Funds_Recd.Task ORDER BY Jobs.Job; but this produces an error telling me I'm not using Jobs.ID in the aggregate function. Is there a way to generate this query without adding the Jobs.ID column to the Funds_Recd table? I guess you want to use a GROUP BY because the SUM makes no sense without it. You seem to want results for unique combinations of Job, Sub and Task. This means: GROUP BY Jobs.Job, Jobs.Sub, Jobs.Task; Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help me: I'm a beginner
On Thu, Jun 26, 2003 at 08:07:48AM -0400, David Bush wrote: I run the following query: select * from x where x like 'x/%' ; I would like the result of this query to go to a file or pause at the end of the screen so that I can view each page. Any help would be appreciated. mysql pager /usr/bin/less mysql tee somefile.log I guess this is documented somewhere, or try: mysql help Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.13 GRANT syntax
On Wed, Jun 25, 2003 at 10:09:58AM -0400, Adam Lawrence wrote: I am attempting to modify the GRANT table using the syntax specified in the MySQL 4.0.13 documentation, and am getting error messages claiming the syntax is incorrect. (I'm running MySQL on Windows 98, by the way.) I used mysqlc with root access. mysql USE mysql; Database changed mysql GRANT SELECT ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY 'update'; ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '[EMAIL PROTECTED] IDENTIFIED BY 'update'' at line 1 mysql Try [EMAIL PROTECTED] instead. UPDATE is a reserved word in SQL. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL - MS SQL
On Sat, Jun 21, 2003 at 12:42:58PM +0400, Sheni R. Meledath wrote: I have designed a site using PHP MySQL. Recently an urgent requirement has come up from the client side to change the web server to IIS. So I have to migrate the scripts and database to Windows based ASP MS SQL. Unless there is another requirement from your client to use MS SQL, you can continue using MySQL with ASP if you like. This would make the transition to IIS easier if you already have lots of queries and tables. Can any body provide me some details regarding migrating MySQL databases to MS SQL databases. It would be highly appreciated if you could send me the details at the earliest. Sorry, but I have no experience with this. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: wierd sort query, how do you do it? (sort by ip proximity guess)
On Fri, Jun 20, 2003 at 09:09:50AM -0500, Ray wrote: select 8 ^ 5; ERROR 1064: You have an error in your SQL syntax near '^ 5' at line 1 i'm guessing that this means mysql 3.23 doesn't have the xor. It was added in 4.0.2 according to the docs. Try this: mysql select version(); +-+ | version() | +-+ | 3.23.52-log | +-+ 1 row in set (0.00 sec) mysql select (5 | 8) ~(5 8); ++ | (5 | 8) ~(5 8) | ++ | 13 | ++ 1 row in set (0.00 sec) mysql select (5 | 7) ~(5 7); ++ | (5 | 7) ~(5 7) | ++ | 2 | ++ 1 row in set (0.00 sec) mysql Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LAST_INSERT_ID() returns different values on different connections
On Tue, Jun 17, 2003 at 04:02:46PM +0300, Baris Akin wrote: I try to get last inserted autoincrement record ID on table with LAST_INSERT_ID() function (SELECT LAST_INSERT_ID() FROM TABLE). Every connection returns it's own last inserted ID not actual ID. Is this a bug? Also it returns more than one record (20 rows). It's not a bug. LAST_INSERT_ID is meant to return the last inserted ID per connection, NOT per table and NOT per connection per table. The 'FROM TABLE' above is therefore redundant. If LAST_INSERT_ID wasn't kept per connection, it would be useless in multi-user cases. It gives you information on what you just inserted, not what someone else did with another connection. http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#IDX1362 Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Big Mistake Need Help, can I undo an action I did?
Hi, On Fri, Jun 13, 2003 at 02:08:33PM -0700, jack ma wrote: I had updated a field for all the entries in a table by mistake, I need to undo that step. I dont have the database back up to an stisfactory level. Is there a way in MySQL I can step one action back? or undo one command that I just did?? Sorry, you'll need a backup. If your backup is not recent enough and you have a binary log (e.g. for replication), you can use that log to restore the database state. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't send Register ID
Hi,
On Sun, Jun 08, 2003 at 01:53:24PM -0300, Robson Oliveira wrote:
I'm trying to send a e-mail with the record register ID from a PHP code query and
don't show the value.
// Connect to database
$db = mysql_connect (localhost, my_db, passxx);
$db = mysql_connect (localhost, my_db, passxx);
if (!$db) {
print 'mysql_connect error: '.mysql_error();
}
else {
// Select the database
mysql_select_db(my_db, $db);
if (!mysql_select_db(my_db, $db)) {
print 'mysql_select_db error: '.mysql_error();
}
else {
// Select user registration ID from the database based on the last record string
$ssecurity_record from the registration page.
$register_id = mysql_query( SELECT register_id from table_register WHERE
$ssecurity_record = ssecurity_record;
Are you sure it gets this far? Your line above won't compile because of
a missing and a missing ).
$result = mysql_query( SELECT register_id from table_register WHERE
$ssecurity_record = ssecurity_record );
if (!result) {
print 'mysql_query error: '.mysql_error();
}
else {
$row = mysql_fetch_row($result);
if (!row) {
print 'mysql_fetch_row: no row, error: '.mysql_error();
}
else {
$register_id = $row['register_id'];
echo $register_id = At this time the value is blank when I print the value
}
}
}
}
Please, If someone know how to print the record value and Select function in php
send to me.
Read the manual, check return values, etc. If you're running PHP in
apache, then check the error_log and use the error_log() function
instead of print as I use in the code above.
My code above is untested BTW.
Regards,
Fred.
--
Fred van Engen XB Networks B.V.
email: [EMAIL PROTECTED]Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UPDATE query doesn't work at a PHP form
Hi,
On Wed, Apr 02, 2003 at 05:01:48PM -0300, Sibusy wrote:
I'm trying to perform UPDATE with a PHP form , but the UPDATE query doesn't work
anyway, returningCan't perform the update, according to code below. It doesn't
return any error at PHP nor at Mysql, I have tested the variables and they are
beeing sent properly.Could someone help me???
Here goes the code : ?php
$db=mysql_connect($host,$user,$senha) or die(Erro de Conexão);
mysql_select_db($dbnome,$db);
$sql=UPDATE [LOW PRIORITY] grupoadministracao WHERE idgrupo = '$nid' SET nome =
'$nome', resp= '$resp',
cnpj='$cnpj', cpf='$cpf', ie='$ie',
endereco='$endereco',numero='$numero',cpto='$cpto', cep='$cep',
telefone1='$telefone1',telefone2='$telefone2',fax='$fax',celular='$celular',email='$email',login='$login',senha='$senha';
The WHERE should be at the end of the query.
Also use addslashes() to properly escape quotes in your variables.
$result=mysql_query($sql,$db);
if($result) {
echo (Datab$nome/b, registro nºb$nid/b was
successfully updated.br);
} else {
echo Can't perform the update. ;
mysql_error();
Try this:
echo mysql_error()
}
Regards,
Fred.
--
Fred van Engen XB Networks B.V.
email: [EMAIL PROTECTED]Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 'delete' syntax
Hi,
On Mon, Mar 31, 2003 at 03:40:03PM +0100, warm-fusion wrote:
Hi, I'm just wondering what the right syntax for using the delete
statement is? I'm trying to write a simple garbage collection/deletion
query similar to the queries below.
delete from 'item' where 'quantity_of_item_in_stock' = 10;
Why do you use quotes around table and field names? If you want to, then
use backquotes (`) i.s.o. quotes (') or double quotes (). The latter
are used for strings.
(the above version of the query pops up a Java-applet alert asking if I
really want to xyz, then it gives an error in MyPHPAdmin)
delete * from 'item' where 'quantity_of_item_in_stock' = 10;
Yes, this is incorrect syntax.
Regards,
Fred.
--
Fred van Engen XB Networks B.V.
email: [EMAIL PROTECTED]Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_fetch_row wrong on mysql 3.23.52
Hi,
On Sun, Mar 30, 2003 at 05:39:13AM -0800, Prabu Subroto wrote:
Dear my friends
I am trying to read the content per line of a query
result with mysql_fetch_row. But I got this error
message :
Musingin homepage
koneksi sukses. Sukses memilih database. Sukses
melakukan query. Besar hasil query 1.
Warning: mysql_fetch_row(): supplied argument is not a
valid MySQL result resource in
/srv/www/htdocs/i-am-e-system/cgi-bin/tulis.php on
line 20
The query failed, so it didn't return a valid result, whether emtpy or
not.
Akhir baris.
Could any body tell me where my mistake?
Here is my codes under belom:
?PHP
$konek=mysql_connect(localhost, prabu,
password);
if ($konek){
echo koneksi sukses.\n;
mysql_select_db(berita);
echo Sukses memilih database.\n;
$kalimatsql=
SELECT noberita, pengirim, tanggal, namafile
FROM berita
;
I'm not sure what's wrong with the query, but you can call mysql_error
to find out.
$hasil = mysql_query($kalimatsql, $konek);
Now check if the query was successful:
if (!$hasil) {
echo mysql_error();
}
else {
echo Sukses melakukan query.\n;
$besarhasil=sizeof($hasil);
What I guess you want to do here is call mysql_num_rows to get the
number of rows in the result. Use sizeof to get the number of items in
a list, not in a MySQL result resource, which is a single result object.
echo Besar hasil query $besarhasil.\n;
while ($baris = mysql_fetch_row($hasil)){
list($noberita,$pengirim,$tanggal,$namafile)=$baris;
echo
Nomer berita: $noberita\n
Pengirim: $pengirim\n
Tanggal : $tanggal\n
Nama file: $namafile\n
;
}
echo Akhir baris.\n;
} else echo Gagal konek.\n;
mysql_close($konek);
?
Thank you very much in advance.
You're welcome.
Regards,
Fred.
--
Fred van Engen XB Networks B.V.
email: [EMAIL PROTECTED]Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data entered in PHP not appearing in mySQL
Hi,
On Wed, Mar 26, 2003 at 02:51:22PM -0500, Serge Paquin wrote:
Do a print $testField; first. I think what might be happing is that you have
register globals turned off (as you should it's a security problem).
Try:
$sql = INSERT INTO testTable values ('', '${_REQUEST['testField']}');
Make that:
$sql = INSERT INTO testTable values ('', ' . addslashes($_REQUEST['testField']) .
');
Or you would just have coded your first exploitable line of code.
Regards,
Fred.
On Wed, 26 Mar 2003 14:28:21 -0500
BRYANT, LANCE [EMAIL PROTECTED] wrote:
Here is a copy of the php script that I'm using. I can create tables and query
info just not add data to the database.
?php
//open the connection
$conn = mysql_connect(onyx,doghead,dogleg);
// pick the database to use
mysql_select_db(mydb,$conn);
//create the SQL statement
$sql = INSERT INTO testTable values ('', '$testField');
//execute the SQL statement
$result = mysql_query($sql, $conn) or die(mysql_error());
//echo the result identifier
echo $result;
?
-Original Message-
From: Don Read [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 26, 2003 2:11 PM
To: BRYANT, LANCE
Cc: [EMAIL PROTECTED]
Subject: RE: Data entered in PHP not appearing in mySQL
On 26-Mar-2003 BRYANT, LANCE wrote:
When I enter data into my web page and active my PHP script. The page
tells me that the data has been updated to my database, but when I check
the database no data is entered. A row is created but no data is
entered. Can any help?
I can't see yer code in my crystal ball but the Magic eight-ball sez: 'declare
your globals.'
Regards,
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
(53kr33t w0rdz: sql table query)
--
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]
--
Fred van Engen XB Networks B.V.
email: [EMAIL PROTECTED]Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Confused about network traffic on mysql port
Hi, On Thu, Mar 27, 2003 at 02:28:37PM -0500, Gary Huntress wrote: I have noticed on many occasions some extensive traffic on my internal network that I cannot explain. Below you will see two sets of tcpdump traces. I have a mysql server running on my internal host named herzegbol and a windows 98 host named shelbyville This trace is when the MySQL server is running: 14:33:45.886159 eth1 herzegbol.mysql shelbyville.2333: S 700834979:700834979(0) ack 2360059956 win 5792 ms s 1460,sackOK,timestamp 420171046 7876889,nop,wscale 0 (DF) 14:33:46.156126 eth1 herzegbol.mysql shelbyville.2311: S 703613196:703613196(0) ack 1969309172 win 5792 ms s 1460,sackOK,timestamp 420171073 7876916,nop,wscale 0 (DF) 14:33:47.010646 eth1 herzegbol.mysql shelbyville.2345: S 697677373:697677373(0) ack 2546308254 win 5792 ms s 1460,sackOK,timestamp 420171158 7877001,nop,wscale 0 (DF) 14:33:47.246107 eth1 herzegbol.mysql shelbyville.2304: S 705352284:705352284(0) ack 1841862906 win 5792 ms s 1460,sackOK,timestamp 420171182 7877025,nop,wscale 0 (DF) These look like reply packets (SYN-ACK) to a port open request (SYN) sent from shelbyville. In this case a confirmation that the port was opened succesfully. This trace is after I issue mysqladmin shutdown: 14:32:09.886091 eth1 herzegbol.mysql shelbyville.2333: R 0:0(0) ack 2360059956 win 0 (DF) 14:32:15.626067 eth1 herzegbol.mysql shelbyville.2334: R 0:0(0) ack 2356113189 win 0 (DF) 14:32:17.586063 eth1 herzegbol.mysql shelbyville.2308: R 0:0(0) ack 1867829359 win 0 (DF) 14:32:20.696068 eth1 herzegbol.mysql shelbyville.2321: R 0:0(0) ack 2130321013 win 0 (DF) 14:32:25.566094 eth1 herzegbol.mysql shelbyville.2324: R 0:0(0) ack 2251852705 win 0 (DF) 14:32:30.066104 eth1 herzegbol.mysql shelbyville.2325: R 0:0(0) ack 2264947201 win 0 (DF) These look like reply packets (RST) to a port open request (SYN) sent from shelbyville. In this case a notification that nothing is listening on the port. The reason this is confusing to me is that the traffic originates on the mysql server herzegbol via the mysql port and the destination is the windows box on dozens of ports and there is no program or process on the windows machine that is connected to the database server.As far as I can tell there is absolutely no reason for Herzegbol to talk to shelbyville, yet this traffic will pop up almost every day for a period of time and swamp my network. I would like to identify the source and understand the cause. My guess is that you're showing only packets sent by herzegbol and not packets received by herzegbol and that shelbyville is really trying to connect. What is your tcpdump line and what do you see when you connect manually from shelbyville to herzegbol? (i.e. telnet herzegbol 3306) Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Confused about network traffic on mysql port
Hi, On Thu, Mar 27, 2003 at 05:35:22PM -0500, Gary Huntress wrote: I have noticed on many occasions some extensive traffic on my internal network that I cannot explain. Below you will see two sets of tcpdump traces. I have a mysql server running on my internal host named herzegbol and a windows 98 host named shelbyville This trace is when the MySQL server is running: 14:33:45.886159 eth1 herzegbol.mysql shelbyville.2333: S 700834979:700834979(0) ack 2360059956 win 5792 ms [snip] This trace is after I issue mysqladmin shutdown: 14:32:09.886091 eth1 herzegbol.mysql shelbyville.2333: R 0:0(0) ack 2360059956 win 0 (DF) [snip] The reason this is confusing to me is that the traffic originates on the mysql server herzegbol via the mysql port and the destination is the windows box on dozens of ports and there is no program or process on the windows machine that is connected to the database server.As far as I can tell there is absolutely no reason for Herzegbol to talk to shelbyville, yet this traffic will pop up almost every day for a period of time and swamp my network. I would like to identify the source and understand the cause. [snip] :) Unless I discover something else, I'm going to assume this is not a mysql problem. This all happens at the level of the TCP stack, so MySQL couldn't be involved I guess. Could you try running tcpdump -n to make sure the DNS or host tables aren't goofed up? TCP stacks don't just reply to unsent packets. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT HEX(abc) is broken...
On Wed, Mar 26, 2003 at 04:45:01PM -0800, Daevid Vincent wrote: http://www.mysql.com/doc/en/String_functions.html Your MySQL connection id is 14 to server version: 3.23.56-Max mysql SELECT HEX(abc); ++ | HEX(abc) | ++ | 0 | this should return 616263 according to the docs. ++ From http://www.mysql.com/doc/en/News-4.0.1.html : - HEX(string) now returns the characters in string converted to hexadecimal. I guess this is new functionality in 4.0.x and it was never added to 3.23.x. Be careful with the online manual, because it is for the latest version of MySQL. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Autoincrement/last_insert_id race safe?
Hi, On Wed, Mar 26, 2003 at 03:17:42PM +0100, Steve Rapaport wrote: A programmer just asked me about a possible race condition, and I didn't know what to answer: If I insert a line using autoincrement, then ask for last_insert_id() am I guaranteed to get the same ID I just inserted? Yes. The last inserted id is kept per connection. So unless YOU insert another record using the same connection, the last_insert_id isn't changed. Also, last_insert_id in a new connection will give you NULL, regardless of what you did in a previous connection. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why Replication stops ?
Hi, On Wed, Mar 26, 2003 at 11:49:01AM +0530, hemanth wrote: I am using MySQL 3.23.41 database in 2 Systems. Getting replication done in MySQL is really cool. But I often face MySQL replication issue. Replication is getting stopped. kindly let me know what could be the reasons ? Did you check the error log? It will tell you when the master and slave data are inconsistent, e.g. because you have changed tables on the slave instead of the master. And also kindly let me know if there is anyway to automate this replication process. In other cases than above, e.g. network problems, the replication should start again automatically. Again, you would see this in the error log. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Daemon wont start
Hi, On Wed, Mar 26, 2003 at 02:46:15PM +0800, Toto Gamez wrote: i installed mysql-3.23.41-1 on my RH7.2 box but when i run safe_mysqld, mysql daemon started but dies i tried to check the log and says: 030326 14:35:46 mysqld started 030326 14:35:47 /usr/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 030326 14:35:47 mysqld ended how do I correct this You don't seem to have setup the privilege tables. See http://www.mysql.com/doc/en/mysql_install_db.html for details. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: linux problem installation
Hi, On Tue, Mar 25, 2003 at 04:46:52PM +0200, tsvicka wrote: I installed the mysql-standard-4.0.12-pc-linux-i686 binary installation according to the installation instructions, but when I run the .bin/safe_mysqld --user=mysql it gave me: mysqld ended. I looked in the log file and it is written: Fatal error: Cant open privilege tables: Cant find file: './mysql/host.frm I looked at mysql dir with ls -al and the file is missing. do you have any suggestions what the problem is or how can I fix it? You don't seem to have setup the privilege tables. See http://www.mysql.com/doc/en/mysql_install_db.html for details. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UNION operates incorrectly
Hi, On Thu, Mar 20, 2003 at 06:38:04AM -0800, Mike Lemke wrote: The topmost query in a series of queries, each connected by UNION ALL, dictates the allocated space for data in each column for the resulting row set! This is very bad - and makes the UNION useless. This is as documented in http://www.mysql.com/doc/en/UNION.html unless I misunderstand your problem. The columns listed in the select_expression portion of the SELECT should have the same type. The column names used in the first SELECT query will be used as the column names for the results returned. Is there some database option that will make a UNION behave properly in mySQL? Please define 'properly'. What are you trying to do? See msg 135092 for sample script if you want to see a physical example. Which message is 135092? I'm reading a mailing list, not a newsgroup. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: New UNION ALL and problem with ORDER BY ?
Hi, On Wed, Mar 19, 2003 at 03:11:38PM -0800, Mike Lemke wrote: Hello MySQL Guru's- This is regarding UNION support in MySQL 4.0.12. Never used it, but ... The query below is attempting to return rows that can be easily parsed for the purposes of creating XML. The ORDER BY is not being processed properly at the end of the query. Sample scripts are shown below, to setup the DB and test the query. ... Did you try it with braces as explained in the UNION documentation? http://www.mysql.com/doc/en/UNION.html Your query would then be: (SELECT 1 AS Tag, NULL AS Parent, Users.UserIdAS 'User!1!UserID!hide', Users.FirstName AS 'User!1!FirstName', Users.LastName AS 'User!1!LastName', NULLAS 'Cars!2!CarID!hide', NULLAS 'Cars!2!Details', NULLAS 'Trips!3!TripID!hide', NULLAS 'Trips!3!Mileage' FROM Users WHERE Users.UserId='2') UNION ALL (SELECT 2, 1, Users.UserId, NULL, NULL, Cars.CarID, Cars.Details, NULL, NULL FROM Cars INNER JOIN Users ON Cars.UserID = Users.UserID WHERE Users.UserId='2') UNION ALL (SELECT 3, 2, Users.UserId, NULL, NULL, Cars.CarID, NULL, Trips.TripID, Trips.Mileage FROM Trips INNER JOIN Cars ON Trips.CarID = Cars.CarID INNER JOIN Users ON Cars.UserID = Users.UserID WHERE Users.UserId='2') ORDER BY 'Cars!2!CarID!hide', 'User!1!UserID!hide', Tag ; Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: UNION operates incorrectly
On Thu, Mar 20, 2003 at 10:32:07AM -0600, Dan Nelson wrote: In the last episode (Mar 20), Fred van Engen said: See msg 135092 for sample script if you want to see a physical example. Which message is 135092? I'm reading a mailing list, not a newsgroup. The mailing list software at mysql.com does number each message, and even though the archive webpage doesn't let you search for a particular number, you can get individial messages by number if you manually edit the URL. To get the message referenced above, for example, you would use Thanks. http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:135092 I replied to that message :) Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 'Invalid authorization specification' from MySql server
Hi,
On Sun, Mar 09, 2003 at 02:48:31PM -0800, [EMAIL PROTECTED] wrote:
mysql update user set password=PASSWORD('silksmitha') where
Host='bajji.myorg.com';
...OK...
When I connect to the server from bajji.myorg.com, the call
DriverManager.getConnection(
jdbc:mysql://dbserver.myorg.com/MyDb?user=rootpassword=silksmitha
);
yields the exception:
java.sql.SQLException: Invalid authorization specification: Access denied for user:
'[EMAIL PROTECTED]' (Using password: YES)
Use FLUSH PRIVILEGES after updating the mysql tables directly.
Regards,
Fred.
--
Fred van Engen XB Networks B.V.
email: [EMAIL PROTECTED]Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: The Security of MySQL
Hi Dyego, On Wed, Feb 26, 2003 at 04:04:36PM -0300, Dyego Souza do Carmo wrote: MM You should use the filesystem security your operating system provides to MM prevent common users from copying the database files. The directory that MM the database files resides in only needs to allow access by the user MM that the MySQL server is running as. If you do this, only users who know MM the MySQL user's password can 'copy' the databases. I work with notary officers on brazil ... and my product i sell ! , the users (competitors) can be copy my system to sell to other users... this is terrible to my software house... the MySQL is not prepared for this ? exists the method to secure a table with password ? or the secure is only the filesystem permissions ? Securing a table with a password or even using encryption would make things more difficult for your users but by no means impossible. You give them the application that contains the password or encryption key. They just need to find it in your application then, which is more difficult but not impossible. Sue them! (that's what copyright laws are for) Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Possibly bug in InnoDB with mysql 3.23.55
Hi, On Thu, Feb 20, 2003 at 10:12:56AM +0100, Iago Sineiro wrote: I think I detected a bug in InnoDB with MySQL 3.23.55. I have a table named ARTICLE_POID of type InnoDB and transaction_isolation is READ-COMMITTED. AUTOCOMMIT is 1 (default value). ... I think this is a bug because in pass 4) I updated instanceNo to 6. The transaction isolation is READ-COMMITED then in connection A I should obtain values updated in connection B (automatically committed). This is called a consistent read, which is documented in: http://www.mysql.com/doc/en/InnoDB_consistent_read.html Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: question on select group by a 'set' field
Hello Anya,
On Tue, Feb 11, 2003 at 07:51:00PM +0800, Anya wrote:
Dear all,
I have a table Kids which has the following fields:
kid char(20),
favorite_fruits set('apple','pear','orange',)
favorite_fruits has choices of up to 64 items.
Now I want to query the count of each fruit appears in the favorite_fruits of the
kids in the table. Is there anyway to use 'group by' to get the count, such as:
select count(*) from Kids group by favorite_fruits?
But if use above statement, the results will group by 'apple', 'apple, pear',
'apple, pear, orange' instead of by 'apple', 'pear', 'orange'
Or I have to do one by one such as:
select count(*) from Kids where favourite_fruits like '%apple%'
and loop against the items in the set?
I never worked with sets, but you could try something like this:
select
sum(sign(find_in_set('apple', favourite_fruits))) as apples,
sum(sign(find_in_set('pear', favourite_fruits))) as pears
from Kids;
This would give you all results on a single result row.
Regards,
Fred.
--
Fred van Engen XB Networks B.V.
email: [EMAIL PROTECTED]Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: interface to python?
On Thu, Jan 30, 2003 at 09:48:16PM -0800, vishnu mahendra wrote: is there any simple ways to interface python with mysql if yes tell me the details how do we do it in windows and linux I never used Python, but this is very easy to find: http://www.python.org/ http://www.python.org/topics/database/ http://www.python.org/topics/database/modules.html http://sourceforge.net/projects/mysql-python Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Replication bug?
On Mon, Jan 13, 2003 at 09:41:12AM +1000, Jason Brooke wrote: No, I've been ignored on this problem for 18 months now, for some reason. Quite peculiar. The limitations of replicate-do-db are documented in: http://www.mysql.com/doc/en/Replication_Options.html I found out about this limitation the hard way, just like you seem to have done. Since we don't control the scripts that use the database, we couldn't use replicate-do-db. It just takes too much time to fix the problems when someone forgets about this limitation. Regards, Fred. - Original Message - From: Ross Davis - DataAnywhere.net [EMAIL PROTECTED] To: 'Jason Brooke' [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, January 13, 2003 9:16 AM Subject: RE: Replication bug? Did you ever get any confirmation that it will be added to the official bug list? -Original Message- From: Jason Brooke [mailto:[EMAIL PROTECTED]] Sent: Sunday, January 12, 2003 3:14 AM To: Ross Davis - DataAnywhere.net Cc: [EMAIL PROTECTED] Subject: Re: Replication bug? Yes this is the same issue I've reported previously. Unless literally 'select' the database, the query is never written to the binary log. - Original Message - From: Ross Davis - DataAnywhere.net [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, January 12, 2003 12:38 PM Subject: Replication bug? I think I have found a replication bug. We are using Mysql-Max 3.23.53 in a master and multiple slave situation. That is working fine. We are using InnoDB We have found a workaround to the problem but I thought you should know about it. We have 2 databases on the system call them dba and dbb. If I have a connection to dba and and then run the following query the update happens on the master but not on the slaves!!! replace into dbb.tablename set field='somevalue' ... The key to the problem is not the replace into, but the fact that we are connected to one database and working on another. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Replication and the function now()
Remi, n Fri, Jan 03, 2003 at 10:56:21AM -0500, Remi Lanteigne wrote: I would like to know if there is a way of telling mysql to transfer the value of the function now() in it's replication, as it is now (from what I see in the binlog file) mysql passes the now() to the slave instead of the value, this causes a difference in my 2 databases. In my 3.23.52 binlog I see this before each time related update: SET TIMESTAMP=1039489014; Each time with the current timestamp of course. I haven't checked if it is actually used by the slave however. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Replication and the function now()
Remi, Michael,
On Fri, Jan 03, 2003 at 11:39:44AM -0500, Michael T. Babcock wrote:
Fred van Engen wrote:
SET TIMESTAMP=1039489014;
Each time with the current timestamp of course. I haven't checked
if it is actually used by the slave however.
If it is used, I would imagine its for TIMESTAMP columns, not your own
that include blah = now().
Looking at the sources, it seems to be used for the NOW function as well.
Anyway, I would consider it a bug if it wasn't used for the NOW function.
A. The slave sets the timestamp with set_time():
slave.cc:
switch(type_code) {
case QUERY_EVENT:
{
Query_log_event* qev = (Query_log_event*)ev;
int q_len = qev-q_len;
int expected_error,actual_error = 0;
init_sql_alloc(thd-mem_root, 8192,0);
thd-db = rewrite_db((char*)qev-db);
if (db_ok(thd-db, replicate_do_db, replicate_ignore_db))
{
thd-query = (char*)qev-query;
** thd-set_time((time_t)qev-when);
B. The timestamp may be queried with current_thd-query_start():
sql_class.h:
**inline time_t query_start() { query_start_used=1; return start_time; }
inline void set_time(){ if (user_time)
start_time=time_after_lock=user_time; else time_after_lock=time(start_time); }
inline void end_time(){ time(start_time); }
**inline void set_time(time_t t) { time_after_lock=start_time=user_time=t; }
inline void lock_time() { time(time_after_lock); }
C. The NOW() call gets its timestamp from current_thd-query_start():
sql_yacc.yy:
| NOW_SYM optional_braces
**{ $$= new Item_func_now(); }
item_timefunc.cc:
void Item_func_now::fix_length_and_dec()
{
struct tm tm_tmp,*start;
**time_t query_start=current_thd-query_start();
I believe now() is processed as the time
when the command is run, even if run from the update log. You could
presumably replace all occurences of now() with the previous TIMESTAMP
value using some simple PERL of course ...
True.
Regards,
Fred.
--
Fred van Engen XB Networks B.V.
email: [EMAIL PROTECTED]Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: query requiring two results from one table?
Hi, Greg's table definitions seem fine te me. Couldn't he just use: select game.gameid, game.dateTime, home.teamID, home.name, away.teamID, away.name from Games game left join Teams home on home.teamID = game.homeID left join Teams away on away.teamID = game.awayID Unless he wants the result in two rows of course. Regards, Fred. On Mon, Dec 30, 2002 at 11:37:03AM -0600, Cal Evans wrote: Your structure is flawed for this kind of query. Games should be: Games --- gameID === teamID (FK to teams) dateTime (datetime) homeAwayFlag char(1) // either H or A --- This way you could use something like select g.gameid, g.teamID, g.homeAwayFlag, g.dateTime, h.teamid, h.name from Games g left join Teams t on g.teamID = h.teamID order by dateTime, homeAwayFlag As it is (IMHO) your only choice is to use 2 SELECT statements and a UNION (Ugly solution) select g.gameid, g.teamID, g.homeID, null as awayID, g.dateTime, h.teamid, h.name from Games g left join Teams t on g.teamID = h.homeID UNION select g.gameid, g.teamID, null as homeID, g.awayID, g.dateTime, h.teamid, h.name from Games g left join Teams t on g.teamID = h.awayID order by dateTime I've not tested this but it should point you in the right direction. (But again, changing your structure is a much better solution.) HTH, =C= * * Cal Evans * The Virtual CIO * http://www.calevans.com * -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, December 30, 2002 11:11 AM To: [EMAIL PROTECTED] Subject: query requiring two results from one table? Hello, This is my first post to the list, so if I am asking in the wrong place, I apologize. I've got some trouble putting together a query with the following tables Games: Teams: gameid teamid homeid name awayid datetime i want to get all games within a certain timeframe, but also retrieve the team names for both the homeid and awayid (these are both links to the teams.teamid field). Not sure how to get two results from the same table in one query. Can anybody point me in the right direction. thanks gf - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select and Sort?
On Tue, Dec 10, 2002 at 03:32:13PM +0500, Tariq Murtaza wrote: I want to achieve recordset like below (sorting category by Name and including 'Others' at the end), I want to do that by single query ;), any idea??? IDCategory 6 biochemistry 62Commerce 95chemistry 2 Engg 87Ecommerce 7 math 45physics 1 Software Engg 5 Others Try something like this: SELECT id, category from mytable order by concat(if(id=5,'B','A'),category); Only problem is that it won't optimize, so use this on small tables only. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select and Sort?
On Tue, Dec 10, 2002 at 04:12:22PM +0500, Tariq Murtaza wrote: Thanks for help. here is another solution for that... SELECT if (Category = 'others', '999', 0) as priority, ID, Category FROM TableName ORDER BY priority, Category That's right. It has the same problem regarding optimization and it gives you another column in your result set (which you may or may not want). Regards, Fred. Fred van Engen wrote: On Tue, Dec 10, 2002 at 03:32:13PM +0500, Tariq Murtaza wrote: I want to achieve recordset like below (sorting category by Name and including 'Others' at the end), I want to do that by single query ;), any idea??? ID Category 6 biochemistry 62 Commerce 95 chemistry 2 Engg 87 Ecommerce 7 math 45 physics 1 Software Engg 5 Others Try something like this: SELECT id, category from mytable order by concat(if(id=5,'B','A'),category); Only problem is that it won't optimize, so use this on small tables only. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: adding values together
On Thu, Nov 28, 2002 at 12:48:29PM +, Steve Mansfield wrote: Hi, doeas anyone know if it's possible to add 2 or more numeric values together that are in the same cell in Mysql: IE: i have a cloumn called price that for any entry may contain more than one value, lets say £1.00 and £2.00 Is there a way that i can get these values added together ? I know it's easy if they are in separate rows but unfortunately they're not. see the example iditemprice 1 apple 1.00 2 apple 1.00 pear2.00 3 banana 1.00 I need to get the sum for price for id 2. It looks like your 'id' column is not unique, but is used to group several rows (apples and pears) together. In that case, you can use: SELECT id, SUM(price) FROM mytable WHERE id = 2 GROUP BY id; Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Get last auto incremented id - newbie
Hi, On Mon, Nov 11, 2002 at 02:52:02AM -0700, Mark Stringham wrote: I wonder if there is an more efficient way to do the following: I have 2 tables - The user submits a new record into table A and an id (primary key) is created using the auto-increment. At the time that the new record is created in table A I need to insert the newly created id from table A into a column in table B. Is there a way to do this without performing multiple queries to either table? INSERT INTO A VALUES(NULL, ...); UPDATE B SET COLUMNA = LAST_INSERT_ID(); or INSERT INTO A VALUES(NULL, ...); INSERT INTO B VALUES(LAST_INSERT_ID(), ...); You need one query per table. LAST_INSERT_ID is kept per connection, not per table, so you can safely use it like this. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: root priv help
Hi Jesse, On Sat, Jun 22, 2002 at 03:42:57AM -0500, Jesse Angell wrote: Hello, In phpmyadmin I accidentaly gave root no privlegaes.. Now I cannot do anything as phpmyadmin wont work because root can't do anything at all... I have complete ssh access etc to the server, Please help me out what do i do to give root full privs again?!? Please help me get mysql running again Oops! Take a look at http://www.mysql.com/doc/R/e/Resetting_permissions.html on how to restart your MySQL daemon with privilege checking. Then login with: # mysql -u root And type: grant all on *.* to root@localhost; And restart your MySQL daemon again. I never tried this, but I guess it should work. Good luck! Fred. -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: error: 'Access denied for user: 'root@localhost' (Using password: NO)'
Hi David,
On Fri, Jun 21, 2002 at 03:59:33AM -0400, david wrote:
Well i seem to have gotten MySql working but i can't seem to use mysqladmin:
to do anything. When i start MySql with the following:
[root@RR davidwri]# safe_mysqld -u root
Starting mysqld daemon with databases from /var/lib/mysql
it will sit indefinitely as above, is this correct?
Yes. You probably want to add an to the end so it runs in the
background. This is all standard shell functionality. Or even run
it in its own subshell:
[root@RR davidwri]# (safe_mysqld -u root )
If i try to add:
mysql -u root -p mysql
It still just sits,...
That's right. You didn't get your prompt back as you wrote earlier.
[root@RR davidwri]# mysqladmin version
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user: 'root@localhost' (Using password: NO)'
You tell the mysql client to not use a password and root needs a
password. So the MySQL daemon won't allow you to log in like this.
I did Set the password, based on the following syntax example (with my value,
obviously):
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('secret_password');
So if i cntrl-z:
[1]+ Stopped safe_mysqld -u root
then try:
[root@RR davidwri]# mysql -u root
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)
Like before.
[root@RR davidwri]# mysql -u root -p mysql
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 3.23.32
Type 'help;' or '\h' for help. Type '\c' to clear the buffer
mysql
Correct. You told the mysql client to ask you for a password.
I have never set up and administered MySql myself, so i don't know exactly
what to expect but this is definitely not right. thanks for all your help
Nothing unexpected so far.
Regards,
Fred.
--
Fred van Engen XO Communications B.V.
email: [EMAIL PROTECTED] Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Unique Indexes across multiple columns
Hi Chris, On Sat, Jun 08, 2002 at 11:44:43AM +0200, Chris Knipe wrote: Not to long ago, I had a query regarding the best way to store IP addresses in a DB, and make sure that they are unique. It was pointed out to me that I could use four smallint columns instead of a varchar to store these numbers, Why not use unsigned tinyint? This allows values 0..255 and is exactly what you need. A smallint allows -32768..32767 and use two bytes i.s.o. one. See http://www.mysql.com/doc/C/o/Column_types.html for more info on column types. By the way, I just use unsigned int for my IP adresses, and INET_NTOA for text representation. and just implement a UNIQUE index across all four columns to make sure the combination of the four columns, would never be in duplicate. Seems ok. It seems, the UNIQUE index however still insist on having unique values for all of the four columns... MySQL said: Duplicate entry '127-30-127-1' for key 2 Did you check with a SELECT if there really is a duplicate 127.30.127.1 ? Regards, Fred. -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem when inserting a record - auto_increment field gets max integer value instead of next id
Bobby, On Wed, Jun 05, 2002 at 12:30:17PM +0200, Bobby Oswald wrote: I have a database with the following table and data: When I add a new record the auto_increment number becomes '2147483647' not sure why? Checked the SHOW TABLE STATUS too and this shows that the next auto_increment number will be '2147483647' anyone know why? INSERT INTO `ACCategories` VALUES (-1, 'Category not required', 2); INSERT INTO `ACCategories` VALUES (-2, 'Important Message for All', 2); Don't use negative values for auto_increment columns. Regards, Fred. PS: filter, filter, are you there? (sql, query) -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem when inserting a record - auto_increment field gets max integer value instead of next id
Bobby, On Wed, Jun 05, 2002 at 01:49:50PM +0200, Bobby Oswald wrote: Removed all the negative values and the same problem still occurs Did you re-create the table with the queries that were shown in your first mail (except the negative values of course)? MyISAM tables remember their latest auto_increment ID. I believe there is also a command you can use to reset the auto_increment ID, but you would still need to take care to not insert negative values. Regards, Fred. -Original Message- From: Fred van Engen [mailto:[EMAIL PROTECTED]] Sent: 05 June 2002 12:50 To: [EMAIL PROTECTED] Subject: Re: Problem when inserting a record - auto_increment field gets max integer value instead of next id Bobby, On Wed, Jun 05, 2002 at 12:30:17PM +0200, Bobby Oswald wrote: I have a database with the following table and data: When I add a new record the auto_increment number becomes '2147483647' not sure why? Checked the SHOW TABLE STATUS too and this shows that the next auto_increment number will be '2147483647' anyone know why? INSERT INTO `ACCategories` VALUES (-1, 'Category not required', 2); INSERT INTO `ACCategories` VALUES (-2, 'Important Message for All', 2); Don't use negative values for auto_increment columns. Regards, Fred. PS: filter, filter, are you there? (sql, query) -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Reaching the unique id after inserting with auto increment
Hi, On Sun, May 19, 2002 at 05:14:49PM +0200, Gyulay Gabor wrote: This is a basic problem. I need an unique id in a table, so I choosed integer with auto increment. (it is so simple...) After inserting a record, I have to display the created unique id to identify that record later. But how can I select it ? If I select the last record, I should get another one, inserted by a concurrent user. Use SELECT LAST_INSERT_ID() like described in the manual. LAST_INSERT_ID is kept per database connection, so concurrent users don't create any problems. Regards, Fred. The stupid filter needs sql,query -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: A dash in user or host confuses the grant statement
On Mon, Oct 22, 2001 at 04:23:10PM +0200, [EMAIL PROTECTED] wrote: Description: the character '-' in username or hostname is not accepted by the GRANT sql statement. How-To-Repeat: grant all on *.* to [EMAIL PROTECTED]; Fix: don't use dashes, fixing the SQL parser. Or use quotes around the hostname: grant all on *.* to my-user@my-host.com; Fred. -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Max 127 records
Hi,
On Fri, Apr 19, 2002 at 01:38:49PM +0100, Henning Olsen wrote:
Hey - can anyone help?
I have a MySql-db in which I can only have 127 records.
Using phpmyadmin to insert record number 128 (autoincrement) gets this message:
INSERT INTO `kontakt` (`id`, `navn`, `adresse`, `postnummer`, `by`, `telefon`,
`kommentar`) VALUES ('', 'fsd', 'sfdg', 'sg',
+'fdsg', 'sfg', 'sg');
MySQL returnerede:
Duplicate entry '127' for key 1
Your table has an auto_increment column of type 'tiny int'. This allows
values between -128 and 127. You should choose a different column type.
Regards,
Fred.
--
Fred van Engen XO Communications B.V.
email: [EMAIL PROTECTED] Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: The table 'SQLb73d_0' is full
On Tue, Feb 19, 2002 at 09:50:57AM +0200, Mohamed Abd El-Samei Mohamed wrote: mysql insert into table1 select distinct field1 , MIN(field2) from table2 where field2 =2001-12-01 00:00:00 group by field2 ; ERROR 1114: The table 'SQLb73d_0' is full mysql I've seen this when MySQL uses an internal table for temporary data. There is a limit on the size of these tables. You can force MySQL to use a file in its temporary directory to store the results: set SQL_BIG_TABLES = 1; An alternative is to do a SELECT SQL_BIG_RESULT ... instead of SELECT ... . Note that SELECT SQL_BIG_RESULT doesn't work in 3.22.x, though it is accepted by the parser. You don't mention which version you use. 'set SQL_BIG_TABLES' works fine in 3.22.x. Note that if I'm correct, you're using a very old version of MySQL. You might consider an upgrade. Fred. -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Hi, Help!
On Mon, Feb 04, 2002 at 04:37:33PM -0600, Erin Lilly wrote: Hi, this is Erin Lilly, I've used support before with you all for bodyconsultant.com and you were extremely helpful. I'm installing mysql on erinlilly.net and have run into that common /tmp/mysql.sock (111) error Hi, you just mailed your root password to an open mailing list. Take care. You might want to change your password now. Fred. -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT LAST_INSERTID() very sloooow
On Fri, Jan 25, 2002 at 02:22:12PM +0100, Marek Kustka wrote: following query SELECT LAST_INSERT_ID() as auto_num FROM xrenders; fills our whole slow query log (500 kB a day). Just do 'select last_insert_id() as auto_num'. The id isn't kept per table anyway, it is kept per session. Regards, Fred. -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: how to create a table with column name 'key'?
Dominique, On Sun, Jan 20, 2002 at 12:18:45PM -0800, Dominique Plante wrote: The following fails (after selecting a database): mysql create table test (key varchar(255), value varchar(255)); You should be able to do this by using back-quotes around the column name: create table test (`key` varchar(255), value varchar(255)); You might want to reconsider using reserved names like this because you will need to use the back-quotes in all your queries. Fred. -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Error 1062: Duplicate entry '127' for Key 1
It's probably a tinyint field which won't go higher than 127.
Fred.
On Thu, Jan 17, 2002 at 02:40:54PM +0100, Thibaut Allender wrote:
an index cannot be null
you should do this :
insert into equipment (id,description) values ('','test data');
regards
At 14:27 17/01/2002, you wrote:
database,sql,query,table
If you just reply to this message, and include the entire text of it in the
reply, your reply will go through. However, you should
first review the text of the message to make sure it has something to do
with MySQL. Just typing the word MySQL once will be sufficient, for
example.
You have written the following:
insert into equipment (id,description) values (null,'test data');
returns the error message in the subject line. I've tried the obvious stuff
like deleting the record with id=127 in. The id field is auto_increment,
btw. I'm sure it's something obvious...
--
Gary Smith - [EMAIL PROTECTED]
Web: http://www.l33t-d00d.co.uk
Pics: http://photos.l33t-d00d.co.uk
Webcam: http://webcam.l33t-d00d.co.uk
--
Gary Smith - [EMAIL PROTECTED]
Web: http://www.l33t-d00d.co.uk
Pics: http://photos.l33t-d00d.co.uk
Webcam: http://webcam.l33t-d00d.co.uk
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
--
Fred van Engen XO Communications B.V.
email: [EMAIL PROTECTED] Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Why is mysql locking tables on insert
Rafal, On Mon, Sep 24, 2001 at 10:14:59AM +0200, Rafal Jank wrote: Why is mysql locking table during insert operastion? The version is 3.23.22, so it shouldn't behave like this... If you mean it shouldn't behave like this because it does concurrent inserts, then note that they work only on MyISAM tables and only if you have never deleted any rows from them. Fred. -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem with LAST_INSERT_ID();
Hi, On Sat, Apr 28, 2001 at 05:12:42PM +0200, Antonino Chirco wrote: Mysql : 3.23.33 server : NT 4.0 S.P.3 Client : Win98 This is the table structure: CREATE TABLE `ordinit` ( `ID_Ordine` int(11) NOT NULL auto_increment, `Cod_Cliente` int(11) default NULL, .. OMISSIS .. PRIMARY KEY (`ID_Ordine`) ) TYPE=MyISAM the query 'select last_insert_id()' after an 'insert' query return always zero. I have tried also with MySQLGUI Client. What does your INSERT query look like? It should specify NULL for ID_ordine, e.g.: INSERT INTO ordinit(ID_ordine, Cod_Cliente) VALUES (NULL, 10); Any other value for ID_ordine will not assign an auto_increment value. ( excuse me for my English, it's under construction) ;-) Your English is fine. Regards, Fred. -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem with LAST_INSERT_ID();
On Sun, Apr 29, 2001 at 03:38:07PM +0200, [EMAIL PROTECTED] wrote: My insert query look like your 'INSERT INTO ordinit(ID_ordine, Cod_Cliente) VALUES (NULL, 10);'; after the query I look into the table 'ordinit' and always looks fine: the value of ID_ordine is really incremented. But the following 'select LAST_INSERT_ID()' return zero :-(( Weird. I've used them often (on ISAM tables) and it works just fine. Do you use the same connection for the 'LAST_INSERT_ID' query as for the INSERT query? The LAST_INSERT_ID is kept per connection and until another INSERT is done. Regards, Fred. What does your INSERT query look like? It should specify NULL for ID_ordine, e.g.: INSERT INTO ordinit(ID_ordine, Cod_Cliente) VALUES (NULL, 10); Any other value for ID_ordine will not assign an auto_increment value. -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: how force mysql to use certain indexes
Hi, On Tue, Apr 24, 2001 at 10:44:59AM -0400, Vivek Khera wrote: PJM == Patrick J Militzer [EMAIL PROTECTED] writes: PJM I'm using mysql 3.22.27. How can I force mysql to use index's in PJM my select statement? You don't. SQL is a 4GL and you only describe what you want, and it is up to the language interpreter and optimizer to do it efficiently. Maybe it isn't the right thing to do usually, but you *can* do it: SELECT field1, field2 FROM mytable use index ( myindex ) WHERE test = 'test'; I use it to specify an index to use for MERGE tables, because the optimizer is less efficient on MERGE tables than on other tables. The feature is documented in some text in the manual about JOIN syntax. An 'ignore index' can be used as well. Regards, Fred. -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Merge Tables Oversight
Hi, On Fri, Apr 20, 2001 at 10:47:57AM +0100, Basil Hussain wrote: I think I may have come up against a slight niggling omission concerning Merge tables. How does one find out what physical tables are mapped, other than by looking at the contents of the .MRG file? Surely this information should be displayed either when you do SHOW TABLE STATUS or by some other means? I never use SHOW TABLE STATUS, but you can use SHOW CREATE TABLE which will give you the complete CREATE TABLE statement for the MERGE table. This doesn't include the UNION part in MySQL 3.23.33, but I believe this was fixed in MySQL 3.23.36 and later. Regards, Fred. -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
