Mysql in Win2K
Hi there, I have just upgraded my Win98SE to Win2K and ever since, I could not start Mysqld or Mysqld-nt (with or without the --install option). I am using Mysql 3.23.30-gamma-win version. I am new to Mysql and would appreciate any help from you. Thank you and regards, CT Chang
Antwort: Boolean Datatype
On 06.02.2001 18:09:36 russ wrote:
Im new to the list, apologies if this has been asked before.
Im developing a backend for a personal site (www.russd.com) using mySQL.
The site is hosted on NT4 and has myODBC installed, I have some database
access
working, but I'm looking for a way to implement boolean types.
Using Access/SQL server I can simple do an insert using SQL like the following
(from within ASP):
INSERT INTO tblMyTable (booleanField) VALUES (" myBooleanVariant ");"
Okay, define booleanField to be TinyINT, or maybe ENUM('TRUE', 'FALSE'), but
TinyINT would be a lot easier IMO.
Then for FALSE you'd insert a 0 and for TRUE you'd insert something else.
values within my ASP code to 1/0. This isn't perfect howevere as its more
code,
Why? Dunno about ASP but since it's from M$ it must be superior to PHP *G* and
in PHP you'd simply do this:
$q = "INSERT INTO tblMyTable (booleanField) VALUES ('" . ( "yes" == "no" ) .
"')";
$rs = mysql_query( $q );
Now booleanField would be set to a FALSE value, or:
$q = "INSERT INTO tblMyTable (booleanField) VALUES ('" . ( "answer to life,
universe and everything" == "42" ) . "')";
$rs = mysql_query( $q );
This time, we'd have set your booleanField to TRUE *G* (well, sorta :]).
$q = "SELECT booleanField FROM tblMyTable WHERE Something=42";
$rs = mysql_query( $q );
$row = mysql_fetch_object( $rs );
if( $row-booleanField ){(
// booleanField is anything but 0, ie. it's TRUE
} else {
// booleanField is 0, ie. it is FALSE
}
In how far is this more code??
Could anyone suggest a better way of implementing this, or am I missing
something?
Yeah, you're missing something. In many languages there's no bool data type,
basically, because something like a tinyINT handles this very well.
-
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: Antwort: Boolean Datatype
Many thanks to all for the help on this issue.
I've come to the conclusion the simplest way for me to implement this is to use the
ENUM type with two possible values 'True' and 'False'.
I will convert the VB BOOLEAN type to a string before I enter it.
Thankfully when returning a value from mySQL, ASP detects the string is "True" and it
evaluates succesfully ("true" = True)
Many thanks for everyones help on a relatively simple question, you've been really
good. I've taken enough of your time now...
Russ Davies
-- Original Message --
From: [EMAIL PROTECTED]
Date: Wed, 7 Feb 2001 10:14:14 +0100
On 06.02.2001 18:09:36 russ wrote:
Im new to the list, apologies if this has been asked before.
Im developing a backend for a personal site (www.russd.com) using mySQL.
The site is hosted on NT4 and has myODBC installed, I have some database
access
working, but I'm looking for a way to implement boolean types.
Using Access/SQL server I can simple do an insert using SQL like the following
(from within ASP):
INSERT INTO tblMyTable (booleanField) VALUES (" myBooleanVariant ");"
Okay, define booleanField to be TinyINT, or maybe ENUM('TRUE', 'FALSE'), but
TinyINT would be a lot easier IMO.
Then for FALSE you'd insert a 0 and for TRUE you'd insert something else.
values within my ASP code to 1/0. This isn't perfect howevere as its more
code,
Why? Dunno about ASP but since it's from M$ it must be superior to PHP *G* and
in PHP you'd simply do this:
$q = "INSERT INTO tblMyTable (booleanField) VALUES ('" . ( "yes" == "no" ) .
"')";
$rs = mysql_query( $q );
Now booleanField would be set to a FALSE value, or:
$q = "INSERT INTO tblMyTable (booleanField) VALUES ('" . ( "answer to life,
universe and everything" == "42" ) . "')";
$rs = mysql_query( $q );
This time, we'd have set your booleanField to TRUE *G* (well, sorta :]).
$q = "SELECT booleanField FROM tblMyTable WHERE Something=42";
$rs = mysql_query( $q );
$row = mysql_fetch_object( $rs );
if( $row-booleanField ){(
// booleanField is anything but 0, ie. it's TRUE
} else {
// booleanField is 0, ie. it is FALSE
}
In how far is this more code??
Could anyone suggest a better way of implementing this, or am I missing
something?
Yeah, you're missing something. In many languages there's no bool data type,
basically, because something like a tinyINT handles this very well.
-
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
off topic
Hi all, I have been playing with perl and mysql on debian linux and its now cooking on gas and I love it thanks to all of you for your help Well off topic I know, but whilst on this list last time I met some perl Guru's and once again I need their "Your" help. I want to write a cgi perl web page, that will produce 2 input boxes on a form and a submit button. I then want the 2 varibles passed to a unix command called BING and then the output placed on the screen. I would appreciate any help Cheers James. - 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
question on indexes
Hello, I have two tables: CREATE TABLE categories ( CategoryNumber int(11) NOT NULL, ItemNumber int(11) NOT NULL, KEY CategoryNumber (CategoryNumber), KEY ItemNumber (ItemNumber) ); CREATE TABLE items ( ItemNumber int(11) NOT NULL auto_increment, Description varchar(100) NOT NULL, Price bigint(20) unsigned NOT NULL, KEY Price (Price) ); Each item can have multiple categories. Each table contains more than 1 million records. This kind of query gets executed a lot: SELECT I.* FROM items AS I, categories AS C WHERE C.CategoryNumber = 123 AND C.ItemNumber = I.ItemNumber AND I.Price = 1000 LIMIT 0,51; I would like to improve the performance of this query. My question is: Would a double index on both fields in the categories table increase performance? In other words, would this structure be better: CREATE TABLE categories ( CategoryNumber int(11) NOT NULL, ItemNumber int(11) NOT NULL, KEY CategoryItem (CategoryNumber,ItemNumber) ); Thanks! Tim - 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: Porting 3.22.32 Problems to HPUX 10.20
On Tue, Feb 06, 2001 at 12:47:20PM -0800, John Jung wrote: Hi All, I'm trying to port MySQL 3.22.32 to HPUX 10.20 and am encountering some problems. I'm on an HPUX 10.20 box with GCC 2.95.2, the configure script works fine when I build I get: mysqld.cc: In function `void close_connections()': mysqld.cc:262: implicit declaration of function `int shutdown(...)' mysqld.cc: In function `void server_init()': mysqld.cc:581: implicit declaration of function `int setsockopt(...)' mysqld.cc: In function `void * handle_connections_sockets(void *)': mysqld.cc:1589: implicit declaration of function `int getsockname(...)' Message sent in that regard to the original poster. Posting to the list was censored. Lutz -- Lutz Jaenicke [EMAIL PROTECTED] BTU Cottbus http://www.aet.TU-Cottbus.DE/personen/jaenicke/ Lehrstuhl Allgemeine Elektrotechnik Tel. +49 355 69-4129 Universitaetsplatz 3-4, D-03044 Cottbus Fax. +49 355 69-4153 - 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: MS Access Checkbox equivalent format in MySQL
I use a short (0 unchecked, 1 checked).
Pat...
- Original Message -
From: "John Halladay" [EMAIL PROTECTED]
To: "MySQL List (E-mail)" [EMAIL PROTECTED]
Sent: Tuesday, February 06, 2001 4:57 PM
Subject: MS Access Checkbox equivalent format in MySQL
If I am using MS Access and linking to tables in MySQL through MyODBC,
what
is the equivalent format of a checkbox (Yes/No) in MySQL?
Would it be ENUM('Yes','No') or ENUM('True','False')? All caps TRUE, YES,
etc.?
Thanks ahead of time for your input.
John Halladay
-
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
Hiding data after select
Hi there, suppose I have table table1 with columns col1,col2,col3,col4 I would like to set up a query that omits eg. col1 from the output even though col1 must satisfy some condition: SELECT col2,col3,col4 from table where col1=condition Any suggestions ? Thanks in advance A.Hannemann - 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: Multiple AND on many-many-table.
Bob Hall wrote: I want to do a select like select id from test where id in (select id from test where value in (1,2,3) and id in (select id from test where value in (4,6,7) and id in (select id from test where value in (1,6,25) Even if MySQL supported subqueries, this query would always return the empty set. I'm really unsure about what you're trying to do, but I think the following comes close. SELECT id FROM test WHERE value IN (1, 2, 3, 4, 6, 7, 25); No the query I wrote would return the intersection of three lists of user ids where each list contained any users that had the values listed. Usually, the column name 'id' is used for a unique row identifier. Since you didn't give the table structure, I assumed that was the case here. If not, then your query could return rows. In that case, your temp table approach is probably the best solution. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak - 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
Hidden data after select
Thanks for the help Typing errors occured Sorry for the inconvenience A.Hannemann - 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
another bug in substring_index in conjunction with UDF functions?
Hi!
"ch" == ch [EMAIL PROTECTED] writes:
Description:
ch My udf function and an equal udf function I found in the internet
ch seem to work well as does substring_index. Only when I call them
ch together I get a bug. As my function (quotet below) is so simple
ch that it doubtly has a bug I guess substring_index has one.
How-To-Repeat:
mysql select substring_index("1.2.3.4/24","/",1) ;
ch +-+
ch | substring_index("1.2.3.4/24","/",1) |
ch +-+
ch | 1.2.3.4 |
ch +-+
ch 1 row in set (0.00 sec)
mysql select ewu_aton("1.2.3.4");
ch +-+
ch | ewu_aton("1.2.3.4") |
ch +-+
ch |16909060 |
ch +-+
ch 1 row in set (0.01 sec)
mysql select ewu_aton( substring_index("1.2.3.4/24","/",1) );
ch +-+
ch | ewu_aton( substring_index("1.2.3.4/24","/",1) ) |
ch +-+
ch | 3772388104 |
ch +-+
ch 1 row in set (0.00 sec)
ch Here the correct value should had been 16909060 (0x01020304), too!
ch My Function:
ch /*
ch ** inet_aton()
ch */
ch my_bool ewu_aton_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
ch {
ch if (args-arg_count != 1 || args-arg_type[0] != STRING_RESULT)
ch {
ch strmov(message,"Wrong arguments to ewu_aton; Use the source!");
ch return 1;
ch }
initid- max_length=20;
initid- maybe_null=0;
ch return 0;
ch }
ch long long ewu_aton(UDF_INIT *initid, UDF_ARGS *args,
chchar *is_null, char *error)
ch {
ch struct in_addrin;
ch inet_aton(((char*) args-args[0]), in);
ch *is_null=0;
ch return htonl(in.s_addr);
ch }
Fix:
ch I'd wish I'd have one :-)
The problem is probaly that you are assuming that the string argument
ends with a '\0', which may not be the case!
Try this instead:
long long ewu_aton(UDF_INIT *initid, UDF_ARGS *args,
char *is_null, char *error)
{
struct in_addrin;
char buff[20];
strnmov(buff,(char*) args-args[0], min(args-lengths[0],20));
inet_aton(buff, in);
*is_null=0;
return htonl(in.s_addr);
}
Regards,
Monty
-
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
License
Hello, I've develop the software in Rapid-Q (under Win), It's a bank of data for a medical ambulance. The main data store will be MySQL. It'll run localy so server will run on the same machine as my program. The program is free (binary) , not free is the source code. Only what i'll sell are the datafiles from which my program will fill the MySQL database. So I want to know if I need a licence (for Win or Linux) and if yes need I licence for every copy of the running server ? Thank'sMichal P. S. V. P. U. http://www.pobox.sk/ - 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
Connect problem
I just installed mysql 3.23.32 on my server and it went without a hitch. I ran mysql_install_db, changed the root password, started the server. I used GRANT statements to create another user, then connected as that user to the mysql client--no problem at all, ran queries, etc. But I wrote a PHP test script to test the connection, also on the same machine (so this is a localhost connection), used the same username and password that worked just fine with the client, and I get: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) This usually means that the server isn't started, but it's running. I use dbtools on my wintel to connect to the departmental mysql server, so I set up a connection in it to this one, and it connects just fine, no problem. I looked at the PHP code and saw no problems there, but to test it, I changed the dbhost, user, and password to connect over the network to the departmental server, and it works just fine, so it's not the code. It just won't connect to localhost--though the connection works from the command line and dbtools. Anybody have any idea what's going on here? -- /"\ \ /ASCII RIBBON CAMPAIGN X AGAINST HTML EMAIL / \AND POSTINGS - 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: INDEX() / UNIQUE()
on a related but slightly different example, what would happen if you had a Primary Key consisting of 2 columns a b (so the pkey enforces a unique combination of a b) and then created an index on one of the columns eg b. Would the index on b have any effect, or does a primary key on 2 columns work by creating individual indexes on both columns, meaning that there are now 2 indexes on b and the second one would have no performance effect? A combined index on col1 and col2 is used for col1col2 (obviously) and additionally for col1 alone, but not for col2 alone. - 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
and ...
I just tried connecting to the db from kmysql on the same box--no way, no how. Same can't connect error message, though I can connect remotely. -- /"\ \ /ASCII RIBBON CAMPAIGN X AGAINST HTML EMAIL / \AND POSTINGS - 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: binary bug in mysql 3.23.30-gamma
Binary is an option on the varchar type I pulled the following quote from the mysql website. It just does not seem to be working anymore. [NATIONAL] VARCHAR(M) [BINARY] A variable-length string. NOTE: Trailing spaces are removed when the value is stored (this differs from the ANSI SQL specification). The range of M is 1 to 255 characters. VARCHAR values are sorted and compared in case-insensitive fashion unless the BINARY keyword is given. See section 7.7.1 Silent Column Specification Changes. VARCHAR is a shorthand for CHARACTER VARYING. See section 7.3.4.1 The CHAR and VARCHAR Types. Rolf Hopkins wrote: I think you are a bit confused with binary and varchar. varchar is used to store strings such as "the cow jumped over the moon" and binary means that it is case sensitive. You are trying to insert a hex value of 0x1, right? I think this is an illegal value and those two entries are set to NULL. MySQL behaves differently from standard in that it inserts default values instead, if there is an error, which in your case, would be NULL. What you want instead is an integer column. - Original Message - From: "Tommie Jones" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 07, 2001 3:30 Subject: binary bug in mysql 3.23.30-gamma In the process of converting over to 3.23.30-gamma from 3.22 whenever I set the field to a binary field to a value it stores it in the database as zero. Does anyone have any suggestions. I am having a problem with the binary data type... Here is the log to demonstrate. - mysql create table crud (bin varchar(16) binary); Query OK, 0 rows affected (0.02 sec) mysql insert into crud (bin) values (0x1); Query OK, 1 row affected (0.00 sec) mysql insert into crud (bin) values (0x2); Query OK, 1 row affected (0.00 sec) mysql select * from crud where bin=0; +--+ | bin | +--+ | | | | +--+ 2 rows in set (0.00 sec) mysql select * from crud where bin=0x0; +--+ | bin | +--+ | | | | +--+ 2 rows in set (0.00 sec) mysql select * from crud where bin=0x1; Empty set (0.00 sec) mysql select * from crud where bin=0x2; Empty set (0.00 sec) -- - 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 - 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 number of Joins
Yes, I should have been more clear, I know that you can join a table to itself but I am trying to find out: 1) What is the max number of joins (unique tables/mixture of same table joins) 2) What is the max number of same-table joins So I guess I had two questions :) Bill On Wed, 7 Feb 2001, Rolf Hopkins wrote: Don't know about max number but unlimited, I would have thought. SELECT b1.foo, b2.foo FROM bar as b1, bar as b2 WHERE ... Will get you a table join on itself. - Original Message - From: "goEbusiness.com Mail Lists" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 07, 2001 0:26 Subject: Max number of Joins How many times can I join tables together? What about joining a table to itself? I checked the maillist archive/manual but cannot find a definite answer (searched on "max number of joins", "joins")...I'd though it would be in section 7.20 of the manual but it wasn't. Thanks. Bill - 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
Re: Re[2]: Serious MySQL internal deadlock
Hi, first of all: the deadlock happened again today, this time with no slave running so it isn't a replication issue. It seems we're getting closer as when I did run 'show processlist', the pending query was (excerpt from output): 1666logreader 10.1.1.4syslog Query 114 Sending data select max(swap_used+swap_free) from pcp where host='apollo' and time='2001020614' and time'20 This is the same type of query as the last time the deadlock occured. Actually looking with netstat there was no more connection between the webserver on 10.1.1.4 and the database. Then I tried to kill the thread which resulted in (excerpt from 'show processlist' output): 1666logreader 10.1.1.4syslog Killed 850 Sending data select max(swap_used+swap_free) from pcp where host='apollo' and time='2001020614' and time'20 The slow query log didn't show the query. After killing mysql (with SIGTERM) the slow query log contained: # Time: 010207 14:38:22 # User@Host: logreader[logreader] @ [10.1.1.4] # Time: 891 Lock_time: 0 Rows_sent: 1 use syslog; select max(swap_used+swap_free) from pcp where host='apollo' and time='2001020614' and time'20010207141000'; Running the 'explain' statement on the query shows something very interesting: mysql explain select max(swap_used+swap_free) from pcp where host='apollo' and time='2001020614' and time'20010207141000'; +---+--+---+--+-+---+--++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+---+--++ | pcp | ref | time,host | host | 8 | const | 8318 | where used | +---+--+---+--+-+---+--++ 1 row in set (0.00 sec) mysql explain select max(swap_used+swap_free) from pcp where host='apollo' and time='2001020614' and time'20010207141000'; +---+--+---+--+-+---+--++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+---+--++ | pcp | ref | time,host | host | 8 | const | 5549 | where used | +---+--+---+--+-+---+--++ 1 row in set (0.00 sec) mysql Both commands were run within about a minute. The number of rows, however, is vastly different, especially the amount of rows of the first query is vastly larger than the amount of rows of the second query, but there is no delete on this table, only inserts and queries. I now did count the total rows in the table for host apollo: mysql select count(time) from pcp where host='apollo'; +-+ | count(time) | +-+ |6684 | +-+ 1 row in set (0.08 sec) mysql Then I counted the rows for the time range: mysql select count(time) from pcp where host='apollo' and time='2001020614' and time'20010207141000'; +-+ | count(time) | +-+ |1427 | +-+ 1 row in set (0.09 sec) mysql The latter value is somewhat OK, given the fact that there's one insert/minute for every host (1440 inserts a day) and that there was a database deadlock during the given time span so some inserts were lost while I didn't restart mysql during the search for the reason of the deadlock. As an additional information 'max(swap_used+swap_free)' currently results in the same value (1GByte) for all rows of host apollo,during the given time range all rows do contain swap_used=0 and swap_free=1GByte. The structure of the table in the query in question runs against is as follows: CREATE TABLE pcp ( time timestamp(14), host varchar(8) NOT NULL, cpu_user smallint(5) unsigned DEFAULT '0' NOT NULL, cpu_nice smallint(5) unsigned DEFAULT '0' NOT NULL, cpu_sys smallint(5) unsigned DEFAULT '0' NOT NULL, cpu_idle smallint(5) unsigned DEFAULT '0' NOT NULL, mem_used int(10) unsigned DEFAULT '0' NOT NULL, mem_free int(10) unsigned DEFAULT '0' NOT NULL, mem_shared int(10) unsigned DEFAULT '0' NOT NULL, mem_cached int(10) unsigned DEFAULT '0' NOT NULL, mem_bufmem int(10) unsigned DEFAULT '0' NOT NULL, swap_free int(10) unsigned DEFAULT '0' NOT NULL, swap_used int(10) unsigned DEFAULT '0' NOT NULL, nprocs smallint(5) unsigned DEFAULT '0' NOT NULL, in_lo int(10) unsigned DEFAULT '0' NOT NULL, in_bond0 int(10) unsigned DEFAULT '0' NOT NULL, in_eth0 int(10) unsigned DEFAULT '0' NOT NULL, in_eth1 int(10) unsigned DEFAULT '0' NOT NULL, in_eth2 int(10) unsigned DEFAULT '0' NOT NULL, in_eth3 int(10) unsigned DEFAULT '0' NOT NULL, in_eth4 int(10) unsigned DEFAULT '0' NOT NULL, in_eth5 int(10) unsigned DEFAULT '0' NOT NULL, in_eth6 int(10) unsigned DEFAULT '0' NOT NULL, in_eth7 int(10) unsigned DEFAULT '0' NOT NULL, out_lo int(10) unsigned DEFAULT '0' NOT NULL,
bugreport
Dear friends, as an appendix I have included the bug report generated from the system. After unzipping and taring the new issue (3.23.32) of mysqlbinary for hpux-10.20 the mysql_install_db script crashed with a coredump. The was the first try to install mysql on this machine. Do You have any idea on this with my best regards Dr. Thomas Meyer Uni Greifswald, Institut für Physik Domstr. 10a, 17487 Greifswald Tel. +49-(0)3834-864762 Fax. +49-(0)3834-8680076 mailto:[EMAIL PROTECTED] http://www.physik.uni-greifswald.de/~meyer/ - 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
Bug converting string into integer
In a SELECT I have to convert a string into a number. I used to use something like 'string'+0, where 'string' is a string rapresentation of an integer. Now I noticed that in many cases it returns a rounded floating point!!! For example: mysql select '11'+0; +-+ | '11'+0 | +-+ | 10.99046326 | +-+ 1 row in set (0.00 sec) Is it normal? Or it happens only in my system? (OpenBSD 2.8 i386) What is the best (more efficient) way to convert an integer string to an integer value? Thanks, ___ __ |- [EMAIL PROTECTED] |ederico Giannici http://www.neomedia.it ___ - 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: binary bug in mysql 3.23.30-gamma
Binary is an option on the varchar type I pulled the following quote from the mysql website. It just does not seem to be working anymore. A guess: It seems to me that the problem is not in your colmn definition, but the silent type conversions MySQL performs. In the log you show below, you're trying to insert numerical data into a column accepting strings. When MySQL sees this, it tries to convert the hexadecimal number, fails, and inserts a 0-length string. When you query using SELECT, the 0x0 is semantically equivalent to the empty string. Have you tried explicitly converting the numbers to strings (e.g. CONCAT(0x1) ) before storing them? / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq [NATIONAL] VARCHAR(M) [BINARY] A variable-length string. NOTE: Trailing spaces are removed when the value is stored (this differs from the ANSI SQL specification). The range of M is 1 to 255 characters. VARCHAR values are sorted and compared in case-insensitive fashion unless the BINARY keyword is given. See section 7.7.1 Silent Column Specification Changes. VARCHAR is a shorthand for CHARACTER VARYING. See section 7.3.4.1 The CHAR and VARCHAR Types. Rolf Hopkins wrote: I think you are a bit confused with binary and varchar. varchar is used to store strings such as "the cow jumped over the moon" and binary means that it is case sensitive. You are trying to insert a hex value of 0x1, right? I think this is an illegal value and those two entries are set to NULL. MySQL behaves differently from standard in that it inserts default values instead, if there is an error, which in your case, would be NULL. What you want instead is an integer column. - Original Message - From: "Tommie Jones" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 07, 2001 3:30 Subject: binary bug in mysql 3.23.30-gamma In the process of converting over to 3.23.30-gamma from 3.22 whenever I set the field to a binary field to a value it stores it in the database as zero. Does anyone have any suggestions. I am having a problem with the binary data type... Here is the log to demonstrate. - mysql create table crud (bin varchar(16) binary); Query OK, 0 rows affected (0.02 sec) mysql insert into crud (bin) values (0x1); Query OK, 1 row affected (0.00 sec) mysql insert into crud (bin) values (0x2); Query OK, 1 row affected (0.00 sec) mysql select * from crud where bin=0; +--+ | bin | +--+ | | | | +--+ 2 rows in set (0.00 sec) mysql select * from crud where bin=0x0; +--+ | bin | +--+ | | | | +--+ 2 rows in set (0.00 sec) mysql select * from crud where bin=0x1; Empty set (0.00 sec) mysql select * from crud where bin=0x2; Empty set (0.00 sec) -- - 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 - 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
RE: Connect problem
I just installed mysql 3.23.32 on my server and it went without a hitch. I ran mysql_install_db, changed the root password, started the server. I used GRANT statements to create another user, then connected as that user to the mysql client--no problem at all, ran queries, etc. But I wrote a PHP test script to test the connection, also on the same machine (so this is a localhost connection), used the same username and password that worked just fine with the client, and I get: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) This usually means that the server isn't started, but it's running. I use dbtools on my wintel to ...but in this case, it means that PHP is trying to connect not through TCP/IP as other remote machines, but rather through a UNIX socket located on the machine as you have described above. It probably means that 1) PHP expects to find the socket file as it says 2) your new version of MySQL creates the socket file somewhere else. Either recompile PHP or re-configure MySQL so they both agree on where to find the socket file. Alternatively, try connecting from PHP specifying the host name rather than localhost. This will force PHP to use TCP/IP rather than the socket file (at the cost of some performance loss) / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq connect to the departmental mysql server, so I set up a connection in it to this one, and it connects just fine, no problem. I looked at the PHP code and saw no problems there, but to test it, I changed the dbhost, user, and password to connect over the network to the departmental server, and it works just fine, so it's not the code. It just won't connect to localhost--though the connection works from the command line and dbtools. Anybody have any idea what's going on here? -- /"\ \ / ASCII RIBBON CAMPAIGN X AGAINST HTML EMAIL / \ AND POSTINGS - 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
Antwort: Re: Antwort: Boolean Datatype
On 07.02.2001 10:30:15 russ wrote:
Many thanks to all for the help on this issue.
I've come to the conclusion the simplest way for me to implement this is to
use
the ENUM type with two possible values 'True' and 'False'.
I will convert the VB BOOLEAN type to a string before I enter it.
Thankfully when returning a value from mySQL, ASP detects the string is "True"
and it evaluates succesfully ("true" = True)
As I said - dunno about ASP, but if it is like in PHP, "true" evaluates to TRUE,
because it is a non-empty string. IOW: In PHP everything that is not empty or
not zero ("yes", "true", "this sentence also evaluates to true", "false", "no",
"worng", 42, -1) is TRUE; or the other way around, just blank or zero is FALSE
("", 0, or a unset variable).
Maybe it's the same in ASP? Please try and tell me, I'd be a little bit
interested to know...
-
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
ERROR 1130: Host '' is not allowed to connect to this MySQL server
Good Morning, Recently, on an otherwise functioning system, I began getting the following message ERROR 1130: Host '' is not allowed to connect to this MySQL server I am running both the client and server from Solaris 7 machines. MySQL version is 3.22.32 This started happening about two days ago, and before I was able to connect fine. The command that causes this error runs on a client, trying to connect to a different machine. Here is the command being run:This is a simple shell script. : echo "select netid,login from names where ssn='$1%';" | mysql -h uba.usf.edu -P 3306 -u blackbrd nams hostname returns the correct host, resolveip returns the correct host, nslookup returns the correct host. Somewhere, MySQL is not looking for the client hostname corerctly. I guess my question is where does MySQL look for the hostname, and how can I check what it is looking for? Thanks in advance for looking at my problem. I appreciate any help received. Thanks, Glen - 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: Bug converting string into integer
It's a bug either in your processor or in your distribution libraries. Your SELECT returns correctly on my Linux and FreeBSD servers. -Tilghman -- "There cannot be a crisis today. My schedule is already full." --Henry Kissinger -Original Message- From: Federico Giannici [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 07, 2001 09:09 To: MySQL mailinglist Subject: Bug converting string into integer In a SELECT I have to convert a string into a number. I used to use something like 'string'+0, where 'string' is a string rapresentation of an integer. Now I noticed that in many cases it returns a rounded floating point!!! For example: mysql select '11'+0; +-+ | '11'+0 | +-+ | 10.99046326 | +-+ 1 row in set (0.00 sec) Is it normal? Or it happens only in my system? (OpenBSD 2.8 i386) What is the best (more efficient) way to convert an integer string to an integer value? Thanks, ___ __ |- [EMAIL PROTECTED] |ederico Giannici http://www.neomedia.it ___ - 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
Re: MySQL driver for DELPHI ?
Here you go... http://www.zeos.dn.ua/eng/index.html Paulo Serra wrote: I am a DELPHI developer and I need to know if exists a MySQL driver for DELPHI. Does MySQL have a driver for DELPHI??? If it does, please send me it... My e-mails: [EMAIL PROTECTED] [EMAIL PROTECTED] ... at least Id like to know if it does or it doesnt and where I can find it. __ Acesso fcil, rpido e ilimitado? Suporte 24hs? R$19,90? S no AcessoBOL. http://www.bol.com.br/acessobol/ - 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
Re: Connect problem
clay bond wrote: I just installed mysql 3.23.32 on my server and it went without a hitch. I ran mysql_install_db, changed the root password, started the server. I used GRANT statements to create another user, then connected as that user to the mysql client--no problem at all, ran queries, etc. But I wrote a PHP test script to test the connection, also on the same machine (so this is a localhost connection), used the same username and password that worked just fine with the client, and I get: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) This usually means that the server isn't started, but it's running. I use dbtools on my wintel to connect to the departmental mysql server, so I set up a connection in it to this one, and it connects just fine, no problem. I looked at the PHP code and saw no problems there, but to test it, I changed the dbhost, user, and password to connect over the network to the departmental server, and it works just fine, so it's not the code. It just won't connect to localhost--though the connection works from the command line and dbtools. Anybody have any idea what's going on here? -- PHP may be looking someplace else for the socket. - 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 do I Move Directories
I ran rpm -Uhv MySql*3.23.32-1.i386.rpm against the sever, the client and the shared rpms. It ran without error and created the appropriate files on the 10 gig /var/lib/mysql. I moved the contents of mysql to /dbshare and created a symbolic link in /var/lib called mysql which pointed to /dbshare.I set the ownership of the link, /dbshare, /dbshare/mysql and everything in /dbshare/mysql to "mysql w group root". When I did that, safe_mysqld was successful in starting mysql from root. "Gerald L. Clark" wrote: You should have run install_db --user=mysql Is the mysql directory in /dbshare owned by mysql ? Are the permissions 775 ? Does mysql own the files in /dbshare/mysql ? kentj wrote: dbshare is owned by mysql with group deamon and I have run install_db "Gerald L. Clark" wrote: kentj wrote: Platform Suse 7.0, AMD 450 KIII+, 128MB Ram Linux on 10 Gig system harddrive with 45 Gig additional drive mounted as /dbshare. I have created a symbolic link in /var/lib of mysql- /dbshare. then RPM -Uhv --force MySQL*-3.23.32-1.i386.rpm to install the files from MySQL-3.23.32-1.i386.rpm MySQL-client-3.23.32-1.i386.rpm MySQL-shared-3.23.32-1.i386.rpm The folders mysql and test are placed in /dbshare and mysql.sock is also created in /dbshare along with the following error message. 010202 13:31:48 mysqld started 010202 13:31:48 /usr/sbin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 010202 13:31:48 mysqld ended What am I missing to make this work? Privileges for the mysql user to access these directories. Who owns /dbshare? The privileges that count are the privileges of the real directory, not the symbolic link. - 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
PRIMARY KEY (userid), UNIQUE (userid)
Is there an idea in having a PRIMARY KEY and UNIQUE key for the same table column ? - 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: Mysql in Win2K
Run the winmysqladmin.exe in the bin directory. Then do the right click "install service", right click "start service". I had to do this the first time. Thereafter it does it on install. [EMAIL PROTECTED] National Aeronautics and Space Administration -- S c i e n c e @ N A S A -- http://science.nasa.gov/ INFORM - the fast paced expansion of knowledge http://kids.msfc.nasa.gov/ INSPIRE - the future belongs to our children http://liftoff.msfc.nasa.gov/ INVOLVE - today's dreams, tomorrow's reality. -Original Message- From: CT Chang [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 07, 2001 3:14 AM To: [EMAIL PROTECTED] Subject: Mysql in Win2K Hi there, I have just upgraded my Win98SE to Win2K and ever since, I could not start Mysqld or Mysqld-nt (with or without the --install option). I am using Mysql 3.23.30-gamma-win version. I am new to Mysql and would appreciate any help from you. Thank you and regards, CT Chang - 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: Can anyone do this ?
Web Depressed wrote: Hi Bob, Many thanks for that. I was hoping (through absolute naiveity) not to have to go and calculate the number of days with the specified range. Unless, of course I could have mysql do this for me. Question: why are you using reserved words for your column names? -- http://www.pricegrabber.com | Dog is my co-pilot.
Re: Mysql in Win2K
On Wed, 7 Feb 2001, CT Chang wrote: Date: Wed, 7 Feb 2001 17:14:28 +0800 From: CT Chang [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Mysql in Win2K Hi there, I have just upgraded my Win98SE to Win2K and ever since, I could not = start Mysqld or Mysqld-nt (with or without the --install option). I am = using Mysql 3.23.30-gamma-win version.=20 If that's all that broke consider yourself fortunate. I did the same thing and lost a tape drive and a CDRW drive dispite have both OK's by microsoft's checker. The answering machine software and CDRW burner were also useless. Again after having been passed by MS as compatible. Good Luck. I am new to Mysql and would appreciate any help from you. Thank you and regards, CT Chang Sincerely, William Mussatto, Senior Systems Engineer CyberStrategies, Inc ph. 909-920-9154 ext. 27 - 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: PRIMARY KEY (userid), UNIQUE (userid)
No. A primary key is a unique key, by definition. -- "There cannot be a crisis today. My schedule is already full." --Henry Kissinger -Original Message- From: Jacob Friis Larsen [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 07, 2001 09:58 To: [EMAIL PROTECTED] Subject: PRIMARY KEY (userid), UNIQUE (userid) Is there an idea in having a PRIMARY KEY and UNIQUE key for the same table column ? - 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
I won SWEEPS TAKES
010207 12:47:42 Error reading packet from server: Contratulations! You have hit the magic number and can win sweepstakes if you report the bug (0). I got the magic number, but I prefer a solution instead my prize! I have a lot of "COOL BUGS". I'am a MYSQL FAN. - 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: Mysql in Win2K
At 07:14 07/02/2001, CT Chang wrote: Hi! Notice that the command mysqld-nt --install only install the service on the SCM and for start it you do: net start mysql Also you need to know that in the next boot the service is started automatic. Regards, Miguel Hi there, I have just upgraded my Win98SE to Win2K and ever since, I could not start Mysqld or Mysqld-nt (with or without the --install option). I am using Mysql 3.23.30-gamma-win version. I am new to Mysql and would appreciate any help from you. Thank you and regards, CT Chang __ ___ __ / |/ /_ __/ __/ __ \/ / http://www.mysql.com/ / /|_/ / // /\ \/ /_/ / /__ Miguel Solrzano [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ So Paulo, Brazil ___/ Development Team - 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: Bug converting string into integer
In a SELECT I have to convert a string into a number.
I used to use something like 'string'+0, where 'string' is a string
rapresentation of an integer.
Now I noticed that in many cases it returns a rounded floating point!!!
For example:
mysql select '11'+0;
+-+
| '11'+0 |
+-+
| 10.99046326 |
+-+
1 row in set (0.00 sec)
Is it normal? Or it happens only in my system? (OpenBSD 2.8 i386)
What is the best (more efficient) way to convert an integer string to an
integer value?
ROUND('number') (?)
/ Carsten
--
Carsten H. Pedersen
keeper and maintainer of the bitbybit.dk MySQL FAQ
http://www.bitbybit.dk/mysqlfaq
-
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: About reserved words: mySQL's dirty little secret
I guess my command of the english language isn't too
great.
I never realized that this quote from the manual:
"You're allowed to do it (for example, ABS is an
allowed column name), but whitespace is not allowed
between a function name and the `(' when using
functions whose names are also column names. "
meant:
"You can escape reserved words via the ` character"
Also, it is better explained here:
http://www.mysql.com/doc/L/e/Legal_names.html
but this is either a new edition to the docs or
something I missed earlier.
Still, its a question that's asked all the time on the
list and I've never seen it addressed, until now.
__
Do You Yahoo!?
Yahoo! Auctions - Buy the things you want at great prices.
http://auctions.yahoo.com/
-
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: binary bug in mysql 3.23.30-gamma
Working anymore? You mean that it used to work? Now I'm confused!! The quote from the manual is correct. Using BINARY means that 'A' 'a' not 'A' = 'a' which is the case without the keyword BINARY. What is it you are trying to achieve by inserting 0x1 into a CHAR field? - Original Message - From: "Tommie Jones" [EMAIL PROTECTED] To: "Rolf Hopkins" [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, February 07, 2001 22:18 Subject: Re: binary bug in mysql 3.23.30-gamma Binary is an option on the varchar type I pulled the following quote from the mysql website. It just does not seem to be working anymore. [NATIONAL] VARCHAR(M) [BINARY] A variable-length string. NOTE: Trailing spaces are removed when the value is stored (this differs from the ANSI SQL specification). The range of M is 1 to 255 characters. VARCHAR values are sorted and compared in case-insensitive fashion unless the BINARY keyword is given. See section 7.7.1 Silent Column Specification Changes. VARCHAR is a shorthand for CHARACTER VARYING. See section 7.3.4.1 The CHAR and VARCHAR Types. Rolf Hopkins wrote: I think you are a bit confused with binary and varchar. varchar is used to store strings such as "the cow jumped over the moon" and binary means that it is case sensitive. You are trying to insert a hex value of 0x1, right? I think this is an illegal value and those two entries are set to NULL. MySQL behaves differently from standard in that it inserts default values instead, if there is an error, which in your case, would be NULL. What you want instead is an integer column. - Original Message - From: "Tommie Jones" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 07, 2001 3:30 Subject: binary bug in mysql 3.23.30-gamma In the process of converting over to 3.23.30-gamma from 3.22 whenever I set the field to a binary field to a value it stores it in the database as zero. Does anyone have any suggestions. I am having a problem with the binary data type... Here is the log to demonstrate. - mysql create table crud (bin varchar(16) binary); Query OK, 0 rows affected (0.02 sec) mysql insert into crud (bin) values (0x1); Query OK, 1 row affected (0.00 sec) mysql insert into crud (bin) values (0x2); Query OK, 1 row affected (0.00 sec) mysql select * from crud where bin=0; +--+ | bin | +--+ | | | | +--+ 2 rows in set (0.00 sec) mysql select * from crud where bin=0x0; +--+ | bin | +--+ | | | | +--+ 2 rows in set (0.00 sec) mysql select * from crud where bin=0x1; Empty set (0.00 sec) mysql select * from crud where bin=0x2; Empty set (0.00 sec) -- - 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 - 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: --skip-locking on Redhat 6.1 Linux
Firstly, I'm curious as to why you need --skip-locking in the first place. Now that I know what you are trying to achieve, I can honestly say I'm not sure. I'd have to read the manual for more detail on how flush-logs interact with table locking etc. I presume your daemon, that's accessing the DB, is a cron job. Can you time it so that your flush-logs occur between this daemon process? - Original Message - From: "Hardy Merrill" [EMAIL PROTECTED] To: "Rolf Hopkins" [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, February 07, 2001 23:15 Subject: Re: --skip-locking on Redhat 6.1 Linux Rolf, I'm invoking safe_mysqld with --skip-locking and --log-update=update_log, among other options. If I run mysqladmin flush-logs while database updates are occurring, the update logs sometimes get confused - the scheme I have is basically mysqladmin flush-logs mv name_of_old_update_log backup_dir and using this scheme I've seen a few different types of update log confusion, but here was one: current update log name: update_log.100 when I ran mysqladmin flush-logs while updates were occurring, the result was that the update log that was moved to backup_dir had name "update_log.100", and the new update log that got created in the MySQL data directory had exactly the same name(update_log.100). I have a daemon writing INSERT's and UPDATE's very regularly to the MySQL database, so I don't want to take the MySQL server down if I can help it, but I would like to run flush-logs on a regular basis so I can have checkpoints of database updates to save off - is there a way for me to lock the tables(or the whole database) in the script *before* doing the mysqladmin flush-logs, to prevent update log confusion? Do you know of a way to solve this update log confusion? I did notice that when I inserted a sleep 1(or 2) between the mysqladmin flush-logs and the "mv" that I haven't been able to "make" the update-logs get confused, but I'm not very confident that this is "the" right solution. Please help. Thanks. Hardy Merrill Mission Critical Linux, Inc. http://www.missioncriticallinux.com Rolf Hopkins [[EMAIL PROTECTED]] wrote: can: yes should: That's up to you but personally I wouldn't - Original Message - From: "Hardy Merrill" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 07, 2001 3:31 Subject: --skip-locking on Redhat 6.1 Linux Can/should MySQL be started *without* --skip-locking on Redhat 6.1 Linux? TIA. -- Hardy Merrill Mission Critical Linux, Inc. http://www.missioncriticallinux.com - 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 - 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: PRIMARY KEY (userid), UNIQUE (userid)
Yes, you can but you probably need a real good reason to. As Tilghman pointed out a PK is a unique key. If you have another unique key (a candidate key) then you need revisit your datamodel and make sure you have a good reason. Candidate keys to exist in the real world but they are rare. Cal http://www.calevans.com -Original Message- From: Jacob Friis Larsen [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 07, 2001 9:58 AM To: [EMAIL PROTECTED] Subject: PRIMARY KEY (userid), UNIQUE (userid) Is there an idea in having a PRIMARY KEY and UNIQUE key for the same table column ? - 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
Re: Bug converting string into integer
It's gives me correct result (integer value 11) on my FreeBSD 3.5, FreeBSD
4.2 and Debian Linux 2.2 Machine
- Original Message -
From: "Carsten H. Pedersen" [EMAIL PROTECTED]
To: [EMAIL PROTECTED]; "MySQL mailinglist" [EMAIL PROTECTED]
Sent: Thursday, February 08, 2001 1:15 AM
Subject: RE: Bug converting string into integer
In a SELECT I have to convert a string into a number.
I used to use something like 'string'+0, where 'string' is a string
rapresentation of an integer.
Now I noticed that in many cases it returns a rounded floating point!!!
For example:
mysql select '11'+0;
+-+
| '11'+0 |
+-+
| 10.99046326 |
+-+
1 row in set (0.00 sec)
Is it normal? Or it happens only in my system? (OpenBSD 2.8 i386)
What is the best (more efficient) way to convert an integer string to an
integer value?
ROUND('number') (?)
/ Carsten
--
Carsten H. Pedersen
keeper and maintainer of the bitbybit.dk MySQL FAQ
http://www.bitbybit.dk/mysqlfaq
-
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
RE: problems running mySQL via Telnet
Hi, You don't connect to mysql server using telnet, you use the mysql command line client 'mysql'. Regards Quentin -Original Message- From: @ndYD [mailto:[EMAIL PROTECTED]] Sent: Thursday, 8 February 2001 07:13 To: [EMAIL PROTECTED] Subject: problems running mySQL via Telnet Hello, server version: 3.22.34-shareware-debug OS: Windows 98 I tried to access the mySQL- server via the Win-Telnet Client. If the connection is established, the following mysterious string appears: ( 3.22.34-shareware-debugk7qS,~Z), After that, I entered some characters. Then the connection will be closed by the server and the error string "Bad Handshake" appears. Question: Is this feature (Telnet) not available in the shareware-version. Or is this a basically problem? Thanks in advance for all tips. Regards Andy - 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 The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. - 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: binary bug in mysql 3.23.30-gamma
This works... however it does not fix my problem. I did a little more research... Instead of varchar(M) BINARY I can do a tinyblob. This allows me to do the inserts and selects that I previously mentioned. Also the documentation says that you can consider blob as a varchar binary. So My question is if they are suppose to be the same thing why don't they work the same... Also both work in 3.22 so I guess I am trying to figure out is this a bug in mysql and how do I get it listed as a bug in mysql if there is not now a solution. "Carsten H. Pedersen" wrote: Binary is an option on the varchar type I pulled the following quote from the mysql website. It just does not seem to be working anymore. A guess: It seems to me that the problem is not in your colmn definition, but the silent type conversions MySQL performs. In the log you show below, you're trying to insert numerical data into a column accepting strings. When MySQL sees this, it tries to convert the hexadecimal number, fails, and inserts a 0-length string. When you query using SELECT, the 0x0 is semantically equivalent to the empty string. Have you tried explicitly converting the numbers to strings (e.g. CONCAT(0x1) ) before storing them? / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq [NATIONAL] VARCHAR(M) [BINARY] A variable-length string. NOTE: Trailing spaces are removed when the value is stored (this differs from the ANSI SQL specification). The range of M is 1 to 255 characters. VARCHAR values are sorted and compared in case-insensitive fashion unless the BINARY keyword is given. See section 7.7.1 Silent Column Specification Changes. VARCHAR is a shorthand for CHARACTER VARYING. See section 7.3.4.1 The CHAR and VARCHAR Types. Rolf Hopkins wrote: I think you are a bit confused with binary and varchar. varchar is used to store strings such as "the cow jumped over the moon" and binary means that it is case sensitive. You are trying to insert a hex value of 0x1, right? I think this is an illegal value and those two entries are set to NULL. MySQL behaves differently from standard in that it inserts default values instead, if there is an error, which in your case, would be NULL. What you want instead is an integer column. - Original Message - From: "Tommie Jones" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 07, 2001 3:30 Subject: binary bug in mysql 3.23.30-gamma In the process of converting over to 3.23.30-gamma from 3.22 whenever I set the field to a binary field to a value it stores it in the database as zero. Does anyone have any suggestions. I am having a problem with the binary data type... Here is the log to demonstrate. - mysql create table crud (bin varchar(16) binary); Query OK, 0 rows affected (0.02 sec) mysql insert into crud (bin) values (0x1); Query OK, 1 row affected (0.00 sec) mysql insert into crud (bin) values (0x2); Query OK, 1 row affected (0.00 sec) mysql select * from crud where bin=0; +--+ | bin | +--+ | | | | +--+ 2 rows in set (0.00 sec) mysql select * from crud where bin=0x0; +--+ | bin | +--+ | | | | +--+ 2 rows in set (0.00 sec) mysql select * from crud where bin=0x1; Empty set (0.00 sec) mysql select * from crud where bin=0x2; Empty set (0.00 sec) -- - 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 - 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: --skip-locking on Redhat 6.1 Linux
Rolf Hopkins [[EMAIL PROTECTED]] wrote: Firstly, I'm curious as to why you need --skip-locking in the first place. I'm not sure that I do - I just thought that if I instead used --enable-locking that this problem of update logs getting confused with flush-logs might(?) go away. But I haven't been able to find any definite documentation that advises whether you can/should run MySQL on Linux without the default(on Linux) --skip-locking, and what the issues (pros/cons) are surrounding that. The basic problem is MySQL seems to be running well on Redhat 6.1 Linux *with* the default --skip-locking, except for this update log confusion problem that I see occassionally. And now that I've put the sleep in after the mysqladmin flush-logs, I can't get the confusion to happen any more - maybe I've solved the problem??? Here's my basic backup strategy * mysqldump once a week * mysqladmin flush-logs once an hour(?), and save the inactive (older) logs to a backup directory for that week * I'd like to work in a "myisamchk" once a day(?), but the documentation says not to do this while running --skip-locking unless you bring the server down * Again, I thought that --enable-locking might solve this problem, and allow me to run myisamchk when the server is up - would it? Would you do this differently? Or does this seem like a reasonable strategy? Please reply - it's great to finally(after posting this 3 times, and reading all the related documentation I could find) get a response. Thank you very much! -- Hardy Merrill Mission Critical Linux, Inc. http://www.missioncriticallinux.com Now that I know what you are trying to achieve, I can honestly say I'm not sure. I'd have to read the manual for more detail on how flush-logs interact with table locking etc. I presume your daemon, that's accessing the DB, is a cron job. Can you time it so that your flush-logs occur between this daemon process? - Original Message - From: "Hardy Merrill" [EMAIL PROTECTED] To: "Rolf Hopkins" [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, February 07, 2001 23:15 Subject: Re: --skip-locking on Redhat 6.1 Linux Rolf, I'm invoking safe_mysqld with --skip-locking and --log-update=update_log, among other options. If I run mysqladmin flush-logs while database updates are occurring, the update logs sometimes get confused - the scheme I have is basically mysqladmin flush-logs mv name_of_old_update_log backup_dir and using this scheme I've seen a few different types of update log confusion, but here was one: current update log name: update_log.100 when I ran mysqladmin flush-logs while updates were occurring, the result was that the update log that was moved to backup_dir had name "update_log.100", and the new update log that got created in the MySQL data directory had exactly the same name(update_log.100). I have a daemon writing INSERT's and UPDATE's very regularly to the MySQL database, so I don't want to take the MySQL server down if I can help it, but I would like to run flush-logs on a regular basis so I can have checkpoints of database updates to save off - is there a way for me to lock the tables(or the whole database) in the script *before* doing the mysqladmin flush-logs, to prevent update log confusion? Do you know of a way to solve this update log confusion? I did notice that when I inserted a sleep 1(or 2) between the mysqladmin flush-logs and the "mv" that I haven't been able to "make" the update-logs get confused, but I'm not very confident that this is "the" right solution. Please help. Thanks. Hardy Merrill Mission Critical Linux, Inc. http://www.missioncriticallinux.com Rolf Hopkins [[EMAIL PROTECTED]] wrote: can: yes should: That's up to you but personally I wouldn't - Original Message - From: "Hardy Merrill" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 07, 2001 3:31 Subject: --skip-locking on Redhat 6.1 Linux Can/should MySQL be started *without* --skip-locking on Redhat 6.1 Linux? TIA. -- Hardy Merrill Mission Critical Linux, Inc. http://www.missioncriticallinux.com - 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
RE: problems running mySQL via Telnet
You're trying to use a Telnet client with a MySQL server. Instead, use a MySQL client with a MySQL server and a Telnet client with a Telnet server. Mixing protocols doesn't really work for anybody. -Tilghman -- "There cannot be a crisis today. My schedule is already full." --Henry Kissinger -Original Message- From: @ndYD [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 07, 2001 12:13 To: [EMAIL PROTECTED] Subject: problems running mySQL via Telnet Hello, server version: 3.22.34-shareware-debug OS: Windows 98 I tried to access the mySQL- server via the Win-Telnet Client. If the connection is established, the following mysterious string appears: ( 3.22.34-shareware-debugk7qS,~Z), After that, I entered some characters. Then the connection will be closed by the server and the error string "Bad Handshake" appears. Question: Is this feature (Telnet) not available in the shareware-version. Or is this a basically problem? Thanks in advance for all tips. Regards Andy - 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
Re: binary bug in mysql 3.23.30-gamma
'varchar(16) binary' works in 3.22 and tinyblob works in 3.23. by work I mean I can insert hexidecimal numbers into the field. I am basically treating it as an integer field. I can store 16 byte numbers in the field and in the case of varchar(16) binary on mysql 3.22 I could store a 32 character hexidecimal number in a 16 byte field and use it as a unique identifier. We have several databases on different servers that are eventually merged and to keep records unique we would use a combination ip address, timestamp (and a few other features) to keep the primary keys unique. This is old code that is too large to change in production. Rolf Hopkins wrote: Working anymore? You mean that it used to work? Now I'm confused!! The quote from the manual is correct. Using BINARY means that 'A' 'a' not 'A' = 'a' which is the case without the keyword BINARY. What is it you are trying to achieve by inserting 0x1 into a CHAR field? - 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
slicing strings for comparison?
I have a select I'd like to do...I have 2 kinds of people -- those needing interviews, and those doing interviews. Among other things, each person has their home address/phone in the tables. I want to generate lists of interviewers in close geographical proximity to the interviewees. I can do the city check easily of course, but I'm stumped on telephone exchanges and zipcode matches. The telephone numbers are stored as char[12] (three digits, dash, three digits, dash, four digits). I want to compare the second set of three digits to each other, something like WHERE Interviewee.homephone[4-6] = Interviewer.homephone[4-6] Similarly for zip codes, I want to compare the first three characters of a char[10] (zip plus 4], essentially WHERE Interviewee.zip[0-2] = Interviewer.zip[0-2] Are these types of comparisons possible? If so, what's the syntax? I couldn't find anything in the online documentation, though it's possibly not called "slicing" or "string range"... --Cindy -- [EMAIL PROTECTED] - 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
Position - log-bin
How can I get the log-bin initial position? Like: trevas-bin.001 trevas-bin.002 I want to know in which position trevas-bin.002 began. - 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: Performance issues.
Maybe we're doing something really wrong in the way we do each look up. We have one table with all the defintions on it's own row. Then we have built off of that table another table that is only the distinct words, no definitions. This is because if a word has 10 definitions, it makes it hard to limit the number of results returned from the first table to 5 words, because we don't know how many definitions each word has. We have two coloumns that we check the search on. keyWord and vKeyWord. keyWord is basically the non-display keyword. without spaces and junk. We could remove that from the searching, if it'd help. Would that make much of a difference? So first we do a: "SELECT COUNT(*) AS totalWords FROM keywords WHERE keyword LIKE '$keyword%' OR vkeyword LIKE '$keyword%'" to get the number of entries they can page through. Then we do a: "SELECT vkeyWord FROM keywords WHERE keyword LIKE '$keyword%' OR vkeyword LIKE '$keyword%' LIMIT $startWordCount, 5" ($startWordCount depends on which page they are on) And build a list of the words we received. Then we do a: "SELECT * FROM Random WHERE vKeyWord IN ($word1, $word2, $word3, $word4, $word5) ORDER BY ID" And *poof* we have all the definitions for 5 words, and the maximum number of words that there could be. Are we doing anything obviouslly wrong in this? Is there a way to log all the sql calls? -Original Message- From: Jason Terry [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 07, 2001 11:01 AM To: Jim Beigel; [EMAIL PROTECTED] Subject: Re: Performance issues. Cool, I like this info. (especially key_buffer_size stuff) However, I was running my numbers against what you said would be good. And this is what I came up with... The Key_reads/Key_read_request = 0.002 (much less than you suggest so that is good... I think) However this one worries me a bit... Key_write/Key_write_requests = 0.087 (way lower than 1) Does this mean that I am sending WAY to many un-needed UPDATE requests? - Original Message - From: "Jim Beigel" [EMAIL PROTECTED] To: "Ryan Hadley" [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, February 07, 2001 6:32 AM Subject: RE: Performance issues. Ryan: 1.) ...WHERE word LIKE '%word%' will never be fast. 2.) Since you said you have indexed the field, if you can limit your searches to ...WHERE word LIKE 'word%', then you might want to look at indexing on a subset of the field if it's a big one. 3.) You will get better performance if you perform routine maintenance on your tables. Try running OPTIMIZE TABLE Or, if you can bring down mysqld, try the myisamchk (-a -S) equivalents. 4.) Lastly, in addition to the other comments, I'd take a look at changing some of the parameters in my.cnf. Here's a pertinent snip from the manual: join_buffer_size The size of the buffer that is used for full joins (joins that do not use indexes). The buffer is allocated one time for each full join between two tables. Increase this value to get a faster full join when adding indexes is not possible. (Normally the best way to get fast joins is to add indexes.) key_buffer_size --- Index blocks are buffered and are shared by all threads. key_buffer_size is the size of the buffer used for index blocks. Increase this to get better index handling (for all reads and multiple writes) to as much as you can afford; 64M on a 256M machine that mainly runs MySQL is quite common. If you, however, make this too big (more than 50% of your total memory?) your system may start to page and become REALLY slow. Remember that because MySQL does not cache data read, that you will have to leave some room for the OS filesystem cache. You can check the performance of the key buffer by doing show status and examine the variables Key_read_requests, Key_reads, Key_write_requests, and Key_writes. The Key_reads/Key_read_request ratio should normally be 0.01. The Key_write/Key_write_requests is usually near 1 if you are using mostly updates/deletes but may be much smaller if you tend to do updates that affect many at the same time or if you are using delay_key_write. See section 7.28 SHOW Syntax (Get Information About Tables, Columns,...). To get even more speed when writing many rows at the same time, use LOCK TABLES. See section 7.32 LOCK TABLES/UNLOCK TABLES Syntax. Jim Beigel Director of Software Development Alabanza Corporation [EMAIL PROTECTED] 740-282-2971 x.5205 -Original Message- From: Ryan Hadley [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 06, 2001 6:09 PM To: [EMAIL PROTECTED] Subject: RE: Performance issues. Thanks for the quick response. The response time is slow... and the mysqld processes are what is hogging up the system. We do have indexes on the fields, but from what I understand, when you use a "LIKE" statement, it rarely uses an index. -Ryan -Original Message- From: Kent Hoover
Blocked because of connection errors
When we increase the number of connections our 3.22.25 server 'hangs': mysql show status; No connection. Trying to reconnect... ERROR 1129: Host 'xxx is blocked because of many connection er rors. Unblock with 'mysqladmin flush-hosts' ERROR: Can't connect to the server This happens when we increase MaxClients on the Apache webserver from 200 to 220 resulting in more queries to the db-server. After a few hours the db-server hangs with the above error. DB and apache server run on different machines. Apache talks with mysql through PHP 4.0.41/ZendOptimizer/Cache. The max connections on the db-server is set to 400. Linux. More info (after restarting mysql): mysql show status; +--+--+ | Variable_name| Value| +--+--+ | Aborted_clients | 0| | Aborted_connects | 671 | | Created_tmp_tables | 31067| | Delayed_insert_threads | 0| | Delayed_writes | 0| | Delayed_errors | 0| | Flush_commands | 1| | Handler_delete | 221 | | Handler_read_first | 23 | | Handler_read_key | 1413111 | | Handler_read_next| 26516553 | | Handler_read_rnd | 4318261 | | Handler_update | 85123| | Handler_write| 182257 | | Key_blocks_used | 15641| | Key_read_requests| 6039569 | | Key_reads| 50309| | Key_write_requests | 3213 | | Key_writes | 3005 | | Max_used_connections | 222 | | Not_flushed_key_blocks | 0| | Not_flushed_delayed_rows | 0| | Open_tables | 63 | | Open_files | 6| | Open_streams | 0| | Opened_tables| 6547 | | Questions| 361413 | | Running_threads | 220 | | Slow_queries | 6| | Uptime | 2048 | +--+--+ 30 rows in set (0.00 sec) Anyone have a clue whats going on ? - 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: problems running mySQL via Telnet
i agree .. the mysql server _should_ accept telnet sessions. Maybe in a future release ? it'd really be very handy - Original Message - From: "Quentin Bennett" [EMAIL PROTECTED] To: "'@ndYD'" [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, February 07, 2001 7:56 PM Subject: RE: problems running mySQL via Telnet Hi, You don't connect to mysql server using telnet, you use the mysql command line client 'mysql'. Regards Quentin -Original Message- From: @ndYD [mailto:[EMAIL PROTECTED]] Sent: Thursday, 8 February 2001 07:13 To: [EMAIL PROTECTED] Subject: problems running mySQL via Telnet Hello, server version: 3.22.34-shareware-debug OS: Windows 98 I tried to access the mySQL- server via the Win-Telnet Client. If the connection is established, the following mysterious string appears: ( 3.22.34-shareware-debugk7qS,~Z), After that, I entered some characters. Then the connection will be closed by the server and the error string "Bad Handshake" appears. Question: Is this feature (Telnet) not available in the shareware-version. Or is this a basically problem? Thanks in advance for all tips. Regards Andy - 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 The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. - 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
RE: problems running mySQL via Telnet
"TT" == The Tilghman [EMAIL PROTECTED] writes: TT You're trying to use a Telnet client with a MySQL server. Instead, TT use a MySQL client with a MySQL server and a Telnet client with a TT Telnet server. Mixing protocols doesn't really work for anybody. well, you can use the telnet client to speak to any service, as long as you are capable of typing the poper protocol characters. Telnet clients are handy for debugging SMTP servers, for example. But in general, you want to use the mysql command line client to speak to mysql. That is, unless you're debugging the mysql protocol ;-) - 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
How do tables break?
Hi! Last night, one of tables broke. For some reason, I cannot access the table anymore, trying to do so gives me this error message: ERROR 1016: Can't open file: 'DownloadCounter.MYD'. (errno: 145) DownloadCounter is the table that is broken. How can something like this happen? Is it more probably my fault or is it an error in the DBMS? Thanks, Alexander Skwar -- How to quote: http://learn.to/quote (german) http://quote.6x.to (english) Homepage: http://www.digitalprojects.com | http://www.iso-top.de iso-top.de - Die guenstige Art an Linux Distributionen zu kommen Uptime: 12 hours 45 minutes - 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
Problems with Replication
The problem I'd like to report is when there's an insert with now() in a datetime field. The problem is that, when both servers doesn't have their clocks synchronized, the value of the now() in the Master will be different from that in the Slave. I've tried it with MySQL 3.23.32 in both servers. I believe MySQL should do a simple conversion so that the binary log would record the values of that INSERT and pass those values to the slave. Or maybe MySQL should do its own time syncronization, I'm not sure what's best. Either way, I'm looking for a faster solution. Is there a good tool to sync clocks in two computers (one acting as master)? Thanks, -- Leonardo Dias Catho Online WebDeveloper http://www.catho.com.br/ - 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: slicing strings for comparison?
Check out SUBSTRING in the manual. On the zip codes, you're better off not trying to do any comparisons in SQL -- the way they work is extremely complicated. I would suggest that you purchase distance measurements between two arbitrary zip codes from the Postal Service, if you intend to be accurate. -Tilghman -- "There cannot be a crisis today. My schedule is already full." --Henry Kissinger -Original Message- From: Cindy [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 07, 2001 13:33 To: [EMAIL PROTECTED] Subject: slicing strings for comparison? I have a select I'd like to do...I have 2 kinds of people -- those needing interviews, and those doing interviews. Among other things, each person has their home address/phone in the tables. I want to generate lists of interviewers in close geographical proximity to the interviewees. I can do the city check easily of course, but I'm stumped on telephone exchanges and zipcode matches. The telephone numbers are stored as char[12] (three digits, dash, three digits, dash, four digits). I want to compare the second set of three digits to each other, something like WHERE Interviewee.homephone[4-6] = Interviewer.homephone[4-6] Similarly for zip codes, I want to compare the first three characters of a char[10] (zip plus 4], essentially WHERE Interviewee.zip[0-2] = Interviewer.zip[0-2] Are these types of comparisons possible? If so, what's the syntax? I couldn't find anything in the online documentation, though it's possibly not called "slicing" or "string range"... --Cindy -- [EMAIL PROTECTED] - 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
Re: How many tables in a database?
I think 12 tables is not a large number by any means. If they _logically_ form one database, I'd not bother about the number. Only if performance problems pop up, and are positively recognized as being caused by too many files in the database directory, I'd start looking for a solution. Which need not mean splitting the database up. Why not look for a better-suited filesystem? ReiserFS can handle huge directories without performance loss. Jan Richard Reina wrote: How do you decide when a table should go into another database. For example, I have a database with about 12 tables and growing. I keep them in one database because I am doing joins that periodically involve all of the tables. Is this the right way to do it? Richard - 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
Client library - Exception handling?
Can I implement some sort of exception handling when using the mySQL
client library? I'm running an application and when I issue a query with
an error in it, I catch the error and print output, but the software
throws a general exception. Should this code be in a
try
{
}
catch
{}
routine? If so, what type of exception should I be catching? Sorry if
this is a stupid question, I'm newer to C++.
Chris
-
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: slicing strings for comparison?
Couldn't find it on usps.com, but a quick search of google turned up the following url: http://www.tpsnet.com/html/zipmenu.html -- "There cannot be a crisis today. My schedule is already full." --Henry Kissinger -Original Message- From: Cal Evans [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 07, 2001 15:32 To: The Tilghman; 'Cindy'; [EMAIL PROTECTED] Subject: RE: slicing strings for comparison? Does the post office offer this info? There used to be a service (Melissa) that had Lat. and Long. for the center of each zip code. You could use that to computer "all addresses in a 50 mile radius". but from what I understand, it's gone now. I would love to find something where I could make these kind of comparisons. Gotta URL ? -Original Message- From: The Tilghman [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 07, 2001 2:26 PM Check out SUBSTRING in the manual. On the zip codes, you're better off not trying to do any comparisons in SQL -- the way they work is extremely complicated. I would suggest that you purchase distance measurements between two arbitrary zip codes from the Postal Service, if you intend to be accurate. -Original Message- From: Cindy [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 07, 2001 13:33 I have a select I'd like to do...I have 2 kinds of people -- those needing interviews, and those doing interviews. Among other things, each person has their home address/phone in the tables. I want to generate lists of interviewers in close geographical proximity to the interviewees. I can do the city check easily of course, but I'm stumped on telephone exchanges and zipcode matches. The telephone numbers are stored as char[12] (three digits, dash, three digits, dash, four digits). I want to compare the second set of three digits to each other, something like WHERE Interviewee.homephone[4-6] = Interviewer.homephone[4-6] Similarly for zip codes, I want to compare the first three characters of a char[10] (zip plus 4], essentially WHERE Interviewee.zip[0-2] = Interviewer.zip[0-2] Are these types of comparisons possible? If so, what's the syntax? I couldn't find anything in the online documentation, though it's possibly not called "slicing" or "string range"... - 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
table too big to dump?
I apologize if this has been discussed already, but I quick poke at the archives didn't reveal anything. We have a table that iamchk says is fine. We are trying to get an SQL dump of this table. But, we keep getting out-of-memory errors. I've tried several things, all to no avail, and I'm looking for advice/suggestions. Some real details: The platform: FreeBSD 3.4-RELEASE on a dual-CPU box, with 1G of physical and 2G of swap. We're running MySQL 3.22.32. This machine is idle; there are no other processes, no uses of the database, other than my client. The table: # ls -l audit_trail_271.* -rw-rw 1 root wheel 1486838307 Nov 10 05:26 audit_trail_271.ISD -rw-rw 1 root wheel84807680 Nov 10 05:26 audit_trail_271.ISM -rw-rw 1 root wheel9060 Oct 25 05:06 audit_trail_271.frm # isamchk audit_trail_271.ISM Checking ISAM file: audit_trail_271.ISM Data records: 3869676 Deleted blocks: 0 - check file-size - check delete-chain - check index reference - check data record references index: 1 - check data record references index: 2 - check record links When I try to do a mysqldump on the localhost, I get an out-of-memory error: # mysqldump -uroot -proot customer audit_trail_271 mysqldump: Out of memory (Needed 8164 bytes) mysqldump: Got error: 2008: MySQL client run out of memory When I try to a dump from over the network: # mysqldump -uroot -proot -h db_host1 customer audit_trail_271 # MySQL dump 7.1 # # Host: db_host1Database: customer # # Server version3.22.32-log # # Table structure for table 'audit_trail_271' # CREATE TABLE audit_trail_271 ( audit_trail_id int(10) unsigned DEFAULT '0' NOT NULL auto_increment, account_id int(10) unsigned DEFAULT '0' NOT NULL, user_id int(10) unsigned, bid_id int(10) unsigned, cpc int(10) unsigned, ics2_id int(10) unsigned, action varchar(20) DEFAULT '' NOT NULL, acct_amt int(11) DEFAULT '0' NOT NULL, gift_amt int(11) DEFAULT '0' NOT NULL, free_amt int(11) DEFAULT '0' NOT NULL, bill_amt int(11) DEFAULT '0' NOT NULL, decl_amt int(11) DEFAULT '0' NOT NULL, ref_base varchar(40), ref_more text, apache_log text, mtime timestamp(14), ctime datetime, PRIMARY KEY (audit_trail_id), KEY account_id (account_id) ); # # Dumping data for table 'audit_trail_271' # And there it hangs forever. As in, I let is sit for hours, and no data comes over the pipe. The server admits there's a connection: # echo 'show processlist;' | mysql -u root -proot customer Id UserHostdb Command TimeState Info 162128 rootdb_host2 customerQuery 446 Sending data SELECT * FROM audit_trail_271 162130 rootlocalhost customerQuery 0 NULLshow processlist And, if I simply do a 'select *', and dump all output into a bitbucket: # echo 'select * from audit_trail_271;' | mysql -u root -proot customer /dev/null mysql: Out of memory (Needed 8164 bytes) ERROR 2008 at line 1: MySQL client run out of memory Does anyone have any advice about how I can get a dump of this table? -- Brian 'you Bastard' Reichert[EMAIL PROTECTED] 37 Crystal Ave. #303Daytime number: (603) 434-6842 Derry NH 03038-1713 USA Intel architecture: the left-hand path - 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
mysql not reading all options if started with -p
mysql (the monitor) seems not to read the option character-sets-dir out of any configuration files (at least not /etc/my.cnf on Linux resp. c:my.ini and windows/my.ini on Windows) if it is started with option -p or with --password, i.e. if it shows a password prompt everything works fine if mysql is started without these options or if it is started with --password=xy this happens both on Linux and on Windows with 3.23.32 any ideas? thank you, Michael Kofler http://www.kofler.cc - 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: CHECK variable incorrect?
Two things: 1) Read the manual to see the correct syntax for the CREATE TABLE. 2) Constraints don't actually do anything in MySQL. Again, read the manual. -Tilghman -- "There cannot be a crisis today. My schedule is already full." --Henry Kissinger -Original Message- From: SF [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 07, 2001 15:26 When I try to run the following command: CREATE TABLE Salespersons (empid INTEGER NOT NULL PRIMARY KEY, ename CHAR(15) NOT NULL, rank INTEGER NOT NULL DEFAULT 1 CHECK (rank IN (1,2,3)), salary DECIMAL(7, 2) NOT NULL DEFAULT 1000.00 CHECK (salary =1000.00)); gives me the following error: You have an error in your SQL syntax near 'CHECK (rank IN (1,2,3)), salary...' I've looked through the MySQL manual and cannot find anything that would help explain why I cannot run any of the CHECK constraints - it does not work when I delete that first CHECK constraint, because the second one is there - if I delete both CHECK constraints, the command to create the table works. I've tried it with creating different tables and it still doesn't work - and I can't figure out why. I'm pretty new to SQL. Other than using it in a number of databases I've designed using MS Access, I don't have much experience. I'm working (if any other newbies might have noticed) straight from a book by Wrox called "Instant SQL Programming" to try and cut my teeth on this... - 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: CHECK variable incorrect?
Two things: 1) Read the manual to see the correct syntax for the CREATE TABLE. I did, and that's where I don't understand why this is happening. The furthest I was able to get in the manual and/or any of the archives from MySQL is: (straight out of the manual) CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [select_statement] create_definition: col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [PRIMARY KEY] [reference_definition] orPRIMARY KEY (index_col_name,...) orKEY [index_name] (index_col_name,...) orINDEX [index_name] (index_col_name,...) orUNIQUE [INDEX] [index_name] (index_col_name,...) orFULLTEXT [INDEX] [index_name] (index_col_name,...) or[CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...) [reference_definition] orCHECK (expr) The comment that CHECK (expr) is supposed to be written as such, is something that I either don't understand - and if so, what is wrong with it in the string I am using (line 4: "CHECK (rank IN (1,2,3))")? - or an error I don't understand is causing this - that's why I'm asking. 2) Constraints don't actually do anything in MySQL. Again, read the manual. I understand and I am - over and over. More info: the actual error number is: 1064 -Original Message- From: SF [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 07, 2001 15:26 When I try to run the following command: CREATE TABLE Salespersons (empid INTEGER NOT NULL PRIMARY KEY, ename CHAR(15) NOT NULL, rank INTEGER NOT NULL DEFAULT 1 CHECK (rank IN (1,2,3)), salary DECIMAL(7, 2) NOT NULL DEFAULT 1000.00 CHECK (salary =1000.00)); gives me the following error: You have an error in your SQL syntax near 'CHECK (rank IN (1,2,3)), salary...' I've looked through the MySQL manual and cannot find anything that would help explain why I cannot run any of the CHECK constraints - it does not work when I delete that first CHECK constraint, because the second one is there - if I delete both CHECK constraints, the command to create the table works. I've tried it with creating different tables and it still doesn't work - and I can't figure out why. I'm pretty new to SQL. Other than using it in a number of databases I've designed using MS Access, I don't have much experience. I'm working (if any other newbies might have noticed) straight from a book by Wrox called "Instant SQL Programming" to try and cut my teeth on this... - 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: slicing strings for comparison?
In the last episode (Feb 07), Cal Evans said: Does the post office offer this info? There used to be a service (Melissa) that had Lat. and Long. for the center of each zip code. You could use that to computer "all addresses in a 50 mile radius". but from what I understand, it's gone now. I would love to find something where I could make these kind of comparisons. Gotta URL ? The US Postal service sells a database called TIGER/ZIP+4, which is a combination of USPS and Census bureau data. The official page is at http://www.usps.gov/ncsc/products/tiger.htm , but if you're content with old data, you can download the 1999 version of the file at http://www.census.gov/geo/www/tiger/zip1999.html -- Dan Nelson [EMAIL PROTECTED] - 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
Strange bug with BDB
Hi! "Peter" == Peter Zaitsev [EMAIL PROTECTED] writes: Peter Hello mysql, Peter mysql 3.23.32 + BDB 3.2.3h mysql select * from layers_la00.g03last_hit; Peter +++-+++ Peter | updated| counter_id | stat_id | lasthit_ts | lasthit_id | Peter +++-+++ Peter | 20010206162625 | 110912 | 1 | 20010103141348 | 25 | Peter | 20010206162624 | 110912 | 2 | 20010103141348 | 25 | cut Peter 50 rows in set (0.00 sec) mysql select * from layers_la00.g03last_hit where counter_id=110912; Peter +++-+++ Peter | updated| counter_id | stat_id | lasthit_ts | lasthit_id | Peter +++-+++ Peter | 20010206155808 | 110912 | 35 | 20010103141348 | 25 | Peter | 20010206155808 | 110912 | 36 | 20010103141348 | 25 | Peter | 20010206162624 | 110912 | 39 | 20010103141348 | 25 | Peter | 20010206155808 | 110912 | 42 | 20010103141348 | 25 | Peter | 20010206155808 | 110912 | 43 | 20010103141348 | 25 | Peter | 20010206155808 | 110912 | 45 | 20010103141348 | 25 | Peter | 20010206155808 | 110912 | 46 | 20010103141348 | 25 | Peter | 20010206155808 | 110912 | 47 | 20010103141348 | 25 | Peter | 20010206162624 | 110912 | 49 | 20010103141348 | 25 | Peter | 20010206162625 | 110912 | 101 | 20010103141348 | 25 | Peter | 20010206155808 | 110912 | 102 | 20010103141348 | 25 | Peter | 20010206155808 | 110912 | 103 | 20010103141348 | 25 | Peter | 20010206155808 | 110912 | 104 | 20010103141348 | 25 | Peter | 20010206162625 | 110912 | 105 | 20010103141348 | 25 | Peter | 20010206162625 | 110912 | 106 | 20010103141348 | 25 | Peter | 20010206162625 | 110912 | 107 | 20010103141348 | 25 | Peter | 20010206155808 | 110912 | 108 | 20010103141348 | 25 | Peter | 20010206162625 | 110912 | 109 | 20010103141348 | 25 | Peter | 20010206155808 | 110912 | 110 | 20010103141348 | 25 | Peter | 20010206162625 | 110912 | 111 | 20010103141348 | 25 | Peter | 20010206155808 | 110912 | 112 | 20010103141348 | 25 | Peter | 20010206155808 | 110912 | 113 | 20010103141348 | 25 | Peter | 20010206162625 | 110912 | 114 | 20010103141348 | 25 | Peter | 20010206162625 | 110912 | 115 | 20010103141348 | 25 | Peter | 20010206155808 | 110912 | 116 | 20010103141348 | 25 | Peter +++-+++ Peter 25 rows in set (0.00 sec) Peter As you see "where clause" removes 25 records from result,there they Peter has to be there. Peter I've done mysqladmin "flush logs" and then copied the .frm and .db Peter files into other database directory - and the bug was unable to be Peter repaired. I assume you mean 'unable to be repeated' ? I haven't seen anything like this with BDB tables before :( Peter also I find one of mysql processes hanged - it took 100% of one of the Peter processors. Peter I've connected to the process and made BT several times: Peter 0x8137b17 in _mi_get_binary_pack_key (keyinfo=0x0, nod_flag=0, page_pos=0x0, key=0x0) at mi_search.c:1035 Peter 1035*page_pos= page+length-tmp; Do you think this is related ? The above means that it was stuck in the MyISAM code, which is not depending on the above. I took a look a the backtraces, but they looked 'normal' Did you do a 'mysqladmin proc' when this happened? Peter After I've restarted mysqld the problem removed itself :( It could have been the automatic repair that fixed this table. Do you think this is possible? Regards, Monty - 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: table too big to dump?
On Wed, Feb 07, 2001 at 05:00:12PM -0500, Brian Reichert wrote: When I try to do a mysqldump on the localhost, I get an out-of-memory error: # mysqldump -uroot -proot customer audit_trail_271 mysqldump: Out of memory (Needed 8164 bytes) mysqldump: Got error: 2008: MySQL client run out of memory Sorry about a rely to myself; on a lark, I did some exploring, and the use of the '--opt' flag made the difference. What is that [not] doing, that changed the behavior of eveything? -- Brian 'you Bastard' Reichert[EMAIL PROTECTED] 37 Crystal Ave. #303Daytime number: (603) 434-6842 Derry NH 03038-1713 USA Intel architecture: the left-hand path - 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
Should I switch to SCSI HD for mysql?
Hi all I am running mysql on linux (redhat 6.2) using a regular 18Gb EIDI hard drive. Does anyone know how much (ball park figure) a SCSI hard drive would speed up selects and inserts? Thanks everyone! Michael Thomas abcXyz.com [EMAIL PROTECTED] - 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: table too big to dump?
Brian Reichert wrote: On Wed, Feb 07, 2001 at 05:00:12PM -0500, Brian Reichert wrote: When I try to do a mysqldump on the localhost, I get an out-of-memory error: # mysqldump -uroot -proot customer audit_trail_271 mysqldump: Out of memory (Needed 8164 bytes) mysqldump: Got error: 2008: MySQL client run out of memory Sorry about a rely to myself; on a lark, I did some exploring, and the use of the '--opt' flag made the difference. What is that [not] doing, that changed the behavior of eveything? --opt (and -q) force the data to go directly to standard out. without either of those it goes to RAM first. --opt also does some other stuff you may or may not want, check the manual for details on that. - 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: Should I switch to SCSI HD for mysql?
On Wed, Feb 07, 2001 at 06:24:58PM -0800, mike thomas wrote: Hi all I am running mysql on linux (redhat 6.2) using a regular 18Gb EIDI hard drive. Does anyone know how much (ball park figure) a SCSI hard drive would speed up selects and inserts? That depends. Are your queryes CPU or disk bound now? Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 328-7878Fax: (408) 530-5454 Cell: (408) 439-9951 - 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: Should I switch to SCSI HD for mysql?
Jeremy, My queries are disk bound (I think) from watching the Redhat system monitor when running a query. Mike -Original Message- From: Jeremy D. Zawodny [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 07, 2001 4:07 PM To: mike thomas Cc: Mysql@Lists. Mysql. Com; [EMAIL PROTECTED] Subject: Re: Should I switch to SCSI HD for mysql? On Wed, Feb 07, 2001 at 06:24:58PM -0800, mike thomas wrote: Hi all I am running mysql on linux (redhat 6.2) using a regular 18Gb EIDI hard drive. Does anyone know how much (ball park figure) a SCSI hard drive would speed up selects and inserts? That depends. Are your queryes CPU or disk bound now? Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 328-7878Fax: (408) 530-5454 Cell: (408) 439-9951 - 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
Bug report - configure script problem finding size of char type
Hello,
I tried to send this using the 'scripts/mysqlbug' script but sendmail
barfed
Description: configure problem finding size of char type on Sparc 20
How-To-Repeat: I was doing a fully-standard configure script run
Fix: unsure at this stage
Submitter-Id: Craig Dewick "[EMAIL PROTECTED]"
Originator:Super-User
Organization:
organization of PR author (multiple lines)
MySQL support: [none | licence | email support | extended email support ]
Synopsis: configure process fails when trying to fine size of char type
Severity: critical, since this prevents the config script from working
Priority: high
Category: mysql
Class: [ sw-bug | doc-bug | change-request | support ] (one line)
Release: mysql-3.23.32 (Source distribution)
Environment:
machine, os, target, libraries (multiple lines)
System: SunOS jedi 5.8 Generic sun4m sparc SUNW,SPARCstation-20
Architecture: sun4
Some paths: /usr/bin/perl /usr/ccs/bin/make /usr/local/bin/gcc /usr/ucb/cc
GCC: Reading specs from /usr/local/lib/gcc-lib/sparc-sun-solaris2.8/2.95.2/specs
gcc version 2.95.2 19991024 (release)
Compilation info: CC='gcc' CFLAGS='' CXX='gcc' CXXFLAGS='' LDFLAGS=''
LIBC:
-rw-r--r-- 1 root bin 1731920 Jan 6 2000 /lib/libc.a
lrwxrwxrwx 1 root root 11 Oct 20 00:21 /lib/libc.so - ./libc.so.1
-rwxr-xr-x 1 root bin 1113260 Jan 6 2000 /lib/libc.so.1
-rw-r--r-- 1 root bin 1731920 Jan 6 2000 /usr/lib/libc.a
lrwxrwxrwx 1 root root 11 Oct 20 00:21 /usr/lib/libc.so - ./libc.so.1
-rwxr-xr-x 1 root bin 1113260 Jan 6 2000 /usr/lib/libc.so.1
Configure command: ./configure --with-unix-socket-path=/var/tmp/mysql.sock
--with-low-memory --with-mit-threads=yes --without-perl --enable-thread-safe-client
--without-berkeley-db
I've quoted below the section of config.log which records the actions at the
time the problem occured:
start
configure:6171: checking need of special linking flags
configure:6181: checking for working const
configure:6235: gcc -c -O6 -DDBUG_OFF -DHAVE_CURSES_H
-I/var/azwan/mysql-3.23.32/include -DHAVE_RWLOCK_T conftest.c 15
configure:6256: checking for inline
configure:6270: gcc -c -O6 -DDBUG_OFF -DHAVE_CURSES_H
-I/var/azwan/mysql-3.23.32/include -DHAVE_RWLOCK_T conftest.c 15
configure:6296: checking for off_t
configure:6329: checking for st_rdev in struct stat
configure:6342: gcc -c -O6 -DDBUG_OFF -DHAVE_CURSES_H
-I/var/azwan/mysql-3.23.32/include -DHAVE_RWLOCK_T conftest.c 15
configure:6363: checking whether time.h and sys/time.h may both be included
configure:6377: gcc -c -O6 -DDBUG_OFF -DHAVE_CURSES_H
-I/var/azwan/mysql-3.23.32/include -DHAVE_RWLOCK_T conftest.c 15
configure:6398: checking whether struct tm is in sys/time.h or time.h
configure:6411: gcc -c -O6 -DDBUG_OFF -DHAVE_CURSES_H
-I/var/azwan/mysql-3.23.32/include -DHAVE_RWLOCK_T conftest.c 15
configure:6434: checking size of char
configure:6453: gcc -o conftest -O6 -DDBUG_OFF -DHAVE_CURSES_H
-I/var/azwan/mysql-3.23.32/include -DHAVE_RWLOCK_T conftest.c -ldl -lz
-lcrypt -lgen -lsocket -lnsl -lm -lpthread -lthread 15
configure: failed program was:
#line 6442 "configure"
#include "confdefs.h"
#include stdio.h
main()
{
FILE *f=fopen("conftestval", "w");
if (!f) exit(1);
fprintf(f, "%d\n", sizeof(char));
exit(0);
}
stop
This problem has not occured when building any other software package so I
suspect something in the configure script is not quite right, but given that
SunOS 5.8 has a lot of changes from previous releases, the problem might be
partly due to that fact also.
Regards,
Craig.
-
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: Should I switch to SCSI HD for mysql?
Greg, Well, I'm doing like "%stuff%" selects on a single column table with 20 million records, so any speed gain would be great. Would I be correct in saying that a select of this type will always do a table scan? I think I'll go with SCSI and increase the RAM so I can use a heap table. Thanks Michael Thomas -Original Message- From: greg [mailto:greg]On Behalf Of Greg Cope Sent: Wednesday, February 07, 2001 4:09 PM To: mike thomas Cc: Mysql@Lists. Mysql. Com; [EMAIL PROTECTED] Subject: Re: Should I switch to SCSI HD for mysql? mike thomas wrote: Hi all I am running mysql on linux (redhat 6.2) using a regular 18Gb EIDI hard drive. Does anyone know how much (ball park figure) a SCSI hard drive would speed up selects and inserts? This is a how long is a piece of string question . EIDE disks have nearly the same data transfer rates as SCSI drives. but SCSI drives have nearly twice the seek-time performance - or their average seek time is twice as good. So it depends on how many seeks a query takes to complete that will determine any performance increase. Each query will be atleast two IIRC from the manual - which has a section on Disk performance. If you are serious about performance go SCSI. Greg Thanks everyone! Michael Thomas abcXyz.com [EMAIL PROTECTED] --- -- 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 mysql-unsubscribe-gjjc=rubberplant.freeserve.co.uk@lis ts.mysql.com 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
Re: Should I switch to SCSI HD for mysql?
One thing to keep in mind is IDE tends to eat up processor power where as off board scsi doesn't. Greg Cope wrote: mike thomas wrote: Hi all I am running mysql on linux (redhat 6.2) using a regular 18Gb EIDI hard drive. Does anyone know how much (ball park figure) a SCSI hard drive would speed up selects and inserts? This is a how long is a piece of string question . EIDE disks have nearly the same data transfer rates as SCSI drives. but SCSI drives have nearly twice the seek-time performance - or their average seek time is twice as good. So it depends on how many seeks a query takes to complete that will determine any performance increase. Each query will be atleast two IIRC from the manual - which has a section on Disk performance. If you are serious about performance go SCSI. Greg Thanks everyone! Michael Thomas abcXyz.com [EMAIL PROTECTED] - 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 - 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: CHECK variable incorrect?
In the last episode (Feb 07), SF said: I did, and that's where I don't understand why this is happening. The furthest I was able to get in the manual and/or any of the archives from MySQL is: (straight out of the manual) CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [select_statement] create_definition: col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [PRIMARY KEY] [reference_definition] orPRIMARY KEY (index_col_name,...) orKEY [index_name] (index_col_name,...) orINDEX [index_name] (index_col_name,...) orUNIQUE [INDEX] [index_name] (index_col_name,...) orFULLTEXT [INDEX] [index_name] (index_col_name,...) or[CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...) [reference_definition] orCHECK (expr) The comment that CHECK (expr) is supposed to be written as such, is something that I either don't understand - and if so, what is wrong with it in the string I am using (line 4: "CHECK (rank IN (1,2,3))")? - or an error I don't understand is causing this - that's why I'm asking. Look farther down in the manual: * The `FOREIGN KEY', `CHECK', and `REFERENCES' clauses don't actually do anything. The syntax for them is provided only for compatibility, to make it easier to port code from other SQL servers and to run applications that create tables with references. *Note Missing functions::. -- Dan Nelson [EMAIL PROTECTED] - 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
Inserting into BLOB on an ASP page using VB or Javascript
Title said it all. How can I set up an asp page to allow me to insert a BLOB into MySQL? Are there any file type/size restrictions to a large BLOB that could cause database problems? Any help would be greatly apreciated. -Waldo B. - 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
JOIN trouble
hi there. i've just joined the list. I took a look through the archives and I think I've got the whole concept of JOIN wrong, but here goes. Here's the task: I've got three tables, each with the same number of records (8077, fwiw). they have 149, 149, and 80 columns. There are a few columns that appear in all the tables and have identical content; LOGRECNO is the field that uniquely identifies each record. What I'd like to do is combine the tables, so that I have 1 table that is 368 columns wide. here's what I did, hoping to join the first and second tables: select * from 2000sampledata1 LEFT JOIN 2000sampledata2 on 2000sampledata1.LOGRECNO = 2000sampledata2.LOGRECNO ; This causes the database to churn for about 40 minutes (it's a 300 MHz system with mirrored SCSI disks). In addition, the tables are not, as far as I can detect, altered in any way. After this command is done, the database files are the same size in bytes and appear to have not been modified. I feel like I'm totally on the wrong track ... any suggestions about how to accomplish this seemingly simple task would be most appreciated. -- Paul Krohn - Systems Engineer San Francisco Chronicle 415-777-7884 - [EMAIL PROTECTED] - 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
mysql hanging on mysqldump output
I am trying to copy data from one database to another using output from mysqldump version 8.11 distributed with 3.23.28-gamma. The problem is that part way through importing the data into the target database, mysql just hangs. When I show the process list, there is only one query, and it's stuck in the update state. The table it gets stuck on is the largest in my database, the .MYD is 131M. The size of the .MYD stops at 49M during the import. First, I execute this to create the dump: % mysqldump --host=xxx --user=xxx --password=xxx -C -q --opt xxx | gzip filename.sql.gz Next, I execute this to import the data into the target database: % gunzip --stdout filename.sql.gz | mysql xxx -u root -p This never finishes. I have played with different mysqldump options and I also tried importing the data into a 3.23.32 database and nothing works. What else might I try? Lance - 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
how to get SELECT to return 0 or 1 for WHERE match ?
I want to SELECT on one term have the SELECT return as quickly as possible on the first TRUE instance. Ideally I'd like it to after the first matching case stop looking for other matches ( for speed ) and return 1. If there's no matches at all I want it to return 0. This works OK but returns either the date or an empty set. SELECT thread_last_post_date FROM threads WHERE thread_last_post_date '2001-02-01' LIMIT 1; I know from Perl or PHP I can check the number of returned rows but I'd like something that just returns 0 or 1 so I don't have to special case. I'd like something like below but which work (it returns 2 rows of 1 for some reason ) : SELECT 1 FROM threads WHERE thread_last_post_date '2001-02-01' LIMIT 1; thanks, - Sam. - Do You Yahoo!? Yahoo! Auctions - Buy the things you want at great prices.
RE: JOIN trouble
Hi, You are a bit on the wrong track - a join is a purely temporary thing, and it would be an error for it to modify the database. Perhaps what you are thinking of is a 'View', where the results of a select appear like a table to the user. MySQL doesn't support them (yet). Your command was trying to churn out 8077 * 8077 = 65,237,929 rows each with 298 columns. No wonder it took a long time. Regards Quentin -Original Message- From: Paul Krohn [mailto:[EMAIL PROTECTED]] Sent: Thursday, 8 February 2001 14:06 To: [EMAIL PROTECTED] Subject: JOIN trouble hi there. i've just joined the list. I took a look through the archives and I think I've got the whole concept of JOIN wrong, but here goes. Here's the task: I've got three tables, each with the same number of records (8077, fwiw). they have 149, 149, and 80 columns. There are a few columns that appear in all the tables and have identical content; LOGRECNO is the field that uniquely identifies each record. What I'd like to do is combine the tables, so that I have 1 table that is 368 columns wide. here's what I did, hoping to join the first and second tables: select * from 2000sampledata1 LEFT JOIN 2000sampledata2 on 2000sampledata1.LOGRECNO = 2000sampledata2.LOGRECNO ; This causes the database to churn for about 40 minutes (it's a 300 MHz system with mirrored SCSI disks). In addition, the tables are not, as far as I can detect, altered in any way. After this command is done, the database files are the same size in bytes and appear to have not been modified. I feel like I'm totally on the wrong track ... any suggestions about how to accomplish this seemingly simple task would be most appreciated. -- Paul Krohn - Systems Engineer San Francisco Chronicle 415-777-7884 - [EMAIL PROTECTED] - 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 The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. - 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: help for a begginner!
Greetings Miguel, Step 1: Identify all the information you want to track. Since you already have a form, you probably have done that already. Step 2: Create your database. I recommend that if you are as new to this as you sound that you install webmin www.webmin.com or phpMyAdmin http://www.phpwizard.com/projects/phpMyAdmin/ and use their interface to create your database. Step 3: Write some program to take the data from the form and put it into your database. I recommend that you explore the exciting world of PHP. (if you haven't already) Step 4: Ask a lot of questions. :) There are some really bright people on this list (not me, but there are some) who have probably run across any problem you might come across. Also, join the PHP lists as well cause we don't answer PHP questions here. HTH, Cal http://www.calevans.com -Original Message- From: Miguel Garcia-Oria [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 08, 2001 12:25 AM To: [EMAIL PROTECTED] Subject: help for a begginner! Hello, My name is Miguel and I am a MD from Spain. Since 1999 I am running a website www.acidez.net addressed to patients with heartburn. Now, I would like to create a database, to store the data patients give me. My idea is to do a survey about this disconfort t try to improve the knoledge about it. Right know I am doing it but trough a form in the web page that email the data to a company that send me the results like an email. Well, I would like to develop my own database. So, I undestood I had to upgrade my package in my server into a one that support MySQL, so I did it. Well, so now what?, I have a software installed in my desktop called winmysqladmin, but I do not know what to do with it. If somebody could explain me the first basic steps, like. should I create a SQL database in my desktop, and upload it through and ftp server, or what should I do. Thank you very much, I will appreciat any help. Sincererely, Miguel Garcia-Oria, MD [EMAIL PROTECTED] - 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
Re: table too big to dump?
In the last episode (Feb 07), Brian Reichert said: I apologize if this has been discussed already, but I quick poke at the archives didn't reveal anything. We have a table that iamchk says is fine. We are trying to get an SQL dump of this table. But, we keep getting out-of-memory errors. I've tried several things, all to no avail, and I'm looking for advice/suggestions. # ls -l audit_trail_271.* -rw-rw 1 root wheel 1486838307 Nov 10 05:26 audit_trail_271.ISD -rw-rw 1 root wheel84807680 Nov 10 05:26 audit_trail_271.ISM -rw-rw 1 root wheel9060 Oct 25 05:06 audit_trail_271.frm When I try to do a mysqldump on the localhost, I get an out-of-memory error: # mysqldump -uroot -proot customer audit_trail_271 mysqldump: Out of memory (Needed 8164 bytes) mysqldump: Got error: 2008: MySQL client run out of memory It's probably trying to read the whole table into memory. Try adding "--opt" to the mysqldump commandline. -- Dan Nelson [EMAIL PROTECTED] - 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
table data
what is the simplest command to view data from a table? - 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: table data
SELECT * FROM table_name; This is explained in the manual, unless you want to use a GUI, then someone else on the list may be able to help you. - Original Message - From: "Marc Bragg" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, February 08, 2001 10:26 Subject: table data what is the simplest command to view data from a table? - 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
Up to my neck in Installation guides!!!
Hello, I am a Linux-Mandrake installer of MySQL and have read no less than 4 complete installation guides: -2 of included with the binary each different, -1 online tutorial and -1 from a published book) and none of them work completely I have unzipped, installed and tested. -MySQL server starts when the mysql.server script is run from the console -MySQL starts and stops all at once when the safe_mysqld script is run from the console -mysql client starts correctly from the console I have copied the mysql.server script to /etc/init.d and linked it in rc0.d as S01mysql (as suggested in the commented code) and also tried naming it K90mysql (how I found it named in a rpm install that worked) also I made a linked it in rc3.d as S99 (as suggested in the commented code) and also tried naming it S90 (how I found it named in a rpm install that worked) So, my questions are: 1. How do I get MySQL to start and stop with the boot and shutdown of my system? 2. Why does the safe_mysqld script start and stop in 1 fell swoop? - 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: Up to my neck in Installation guides!!!
look in the scripts directory and use one of the db_install (or install_db) scripts. It is failing because you do not have a mysql database defined... On Wed, Feb 07, 2001 at 10:56:04PM -0500, root wrote: Hello, I am a Linux-Mandrake installer of MySQL and have read no less than 4 complete installation guides: -2 of included with the binary each different, -1 online tutorial and -1 from a published book) and none of them work completely I have unzipped, installed and tested. -MySQL server starts when the mysql.server script is run from the console -MySQL starts and stops all at once when the safe_mysqld script is run from the console -mysql client starts correctly from the console I have copied the mysql.server script to /etc/init.d and linked it in rc0.d as S01mysql (as suggested in the commented code) and also tried naming it K90mysql (how I found it named in a rpm install that worked) also I made a linked it in rc3.d as S99 (as suggested in the commented code) and also tried naming it S90 (how I found it named in a rpm install that worked) So, my questions are: 1. How do I get MySQL to start and stop with the boot and shutdown of my system? 2. Why does the safe_mysqld script start and stop in 1 fell swoop? - 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
Working with different types of Database Servers
Hello, Is it possible to interconnect two different Database Servers like Mysql on Linux and MS-SQL 6.5 on Windows NT. I am mean to say,Can we access both work on the data in above Database Servers simultaneously. Thanks in advance. From, Sangeeta. ([EMAIL PROTECTED])
Re: select trouble
Please search the documents before posting (if you already haven't) http://www.mysql.com/doc/R/e/Regexp.html -- Atle On Thu, 8 Feb 2001, wangzhw wrote: hi: whether mysql is supported by regular expressison? if it is supported,how to express it by select query. thank you! - 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
MySQL and PHP Question
Hi, I have two tables city and country +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | id| int(5) | | PRI | 0 | auto_increment | | city | varchar(35) | | MUL | || | countryid | int(5) | | MUL | 0 || +---+-+--+-+-++ 3 rows in set (0.00 sec) mysql desc country; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | id | int(5) | | PRI | 0 | auto_increment | | country | varchar(35) | | MUL | || +-+-+--+-+-++ 2 rows in set (0.00 sec) I am trying to write a php script that does the following. In an html page I have two dropdowns one country which will get the data from the country table and city which should get the data from the city table based on the one selected from the country drop down. I do not want to use a submit button. I use a javascript and using onChange pass the country.id to the javascript but do not know how to pass it back to the php script. Any pointers Adrian -- === Adrian D'Costa [EMAIL PROTECTED] www.pcsadvt.com === - 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
PHP - working with two MySQL serwers
Hi all, I use replication on MySQL. One of my ideas is to generate HTML pages using PHP. Will I have any troubles using PHP and connecting simultaneously to both servers? Thanks in advance for any comments. Mariusz Brzezinski WBK SA tel. +48 61 856 53 57 mobile +48 501 623 123 - 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
