SELECT... EXCEPT FOR
If there's not a way to do this I would suggest it be added to the next version of MySQL, but has anyone ever heard of an EXCEPT FOR clause that can be used in MySQL's SELECT statements? For instance... SELECT * FROM products EXCEPT FOR colors WHERE sizes LIKE '%small%' Thanks... Jed Hunsaker [EMAIL PROTECTED]
Re: DATE
On 12-Jun-2003 Wong Zach-CHZ013 wrote: Hi 1 - I have a column whose datatype is longtext. Its content is 08/06/2003; I created a new column whose datatype is DATE. Its content is null now. How do write a SQL statement that inputs each row from 08/06/2003 in the old column to 2003-08-06 in a new column ? Eg: old column name - my_date new column name - my_new_date I tried select CONCAT(SUBSTRING(MY_DATE FROM 7), '-',SUBSTRING_INDEX(MY_DATE,'/',1), '-', MID(MY_DATE,4,2)) from dap_cell; But how do I put this value into its corresponding row in the new column ? UPDATE dap_cell SET my_new_date= REPLACE(CONCAT(RIGHT(my_date, 4), LEFT(my_date,5)), '/',''); Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What does the ERROR mean?
MySQL 4.0.12 max on AIX 4.3.2 Following are part of .err log file: 030615 1:29:44 Aborted connection 163 to db: 'New' user: 'happy' host: `192.168.5.108' (Got an error reading communication packets) 030615 1:29:54 Aborted connection 167 to db: 'New' user: 'happy' host: `192.168.5.108' (Got an error reading communication packets) 030615 1:29:54 Aborted connection 166 to db: 'New' user: 'happy' host: `192.168.5.108' (Got an error reading communication packets) 030615 1:29:54 Aborted connection 165 to db: 'New' user: 'happy' host: `192.168.5.108' (Got an error reading communication packets) 030615 1:30:28 Aborted connection 170 to db: 'New' user: 'happy' host: `192.168.5.108' (Got an error reading communication packets) 030615 1:30:28 Aborted connection 169 to db: 'New' user: 'happy' host: `192.168.5.108' (Got an error reading communication packets) 030615 1:30:28 Aborted connection 168 to db: 'New' user: 'happy' host: `192.168.5.108' (Got an error reading communication packets)
[BUG] show full processlist on AIX
MySQL 4.0.12 max on AIX 4.3.2 HA. When I use show full processlist on localhost, all Hosts show as localhost. but when I use show full processlist from remote, all Hosts show as remote IP. as follow: mysql show full processlist; +-+-+-++-+--+---+---+ | Id | User| Host| db | Command | Time | State | Info | +-+-+-++-+--+---+---+ | 14 | gege| localhost:3425 | NewProduct | Sleep | 744 | | NULL | | 20 | yuheyang| localhost:3277 | NewProduct | Sleep | 4044 | | NULL | | 21 | yuheyang| localhost:3278 | NewProduct | Sleep | 4044 | | NULL | | 22 | yuheyang| localhost:3279 | NewProduct | Sleep | 4132 | | NULL | | 23 | yuheyang| localhost:3280 | NewProduct | Sleep | 4137 | | NULL | | 24 | yuheyang| localhost:3312 | NewProduct | Sleep | 1946 | | NULL | | 25 | yuheyang| localhost:3313 | NewProduct | Sleep | 1946 | | NULL | | 26 | yuheyang| localhost:3314 | NewProduct | Sleep | 1946 | | NULL | | 27 | yuheyang| localhost:3315 | NewProduct | Sleep | 1946 | | NULL | | 28 | root| localhost | NULL | Query | 0| NULL | show full processlist | | 29 | happynessrabbit | localhost:44555 | NewProduct | Sleep | 291 | | NULL | | 30 | happynessrabbit | localhost:44556 | NewProduct | Sleep | 291 | | NULL | | 31 | happynessrabbit | localhost:44557 | NewProduct | Sleep | 291 | | NULL | | 32 | happynessrabbit | localhost:44558 | NewProduct | Sleep | 291 | | NULL | | 115 | happynessrabbit | localhost:44654 | NewProduct | Sleep | 1809 | | NULL | | 116 | happynessrabbit | localhost:44655 | NewProduct | Sleep | 1801 | | NULL | | 117 | happynessrabbit | localhost:44657 | NewProduct | Sleep | 1792 | | NULL | | 118 | happynessrabbit | localhost:44658 | NewProduct | Sleep | 1781 | | NULL | | 119 | happynessrabbit | localhost:44659 | NewProduct | Sleep | 1776 | | NULL | | 131 | happynessrabbit | localhost:44671 | NewProduct | Sleep | 1715 | | NULL | | 137 | happynessrabbit | localhost:44677 | NewProduct | Sleep | 1711 | | NULL | | 156 | happynessrabbit | localhost:44696 | NewProduct | Sleep | 1603 | | NULL | | 162 | happynessrabbit | localhost:44704 | NewProduct | Sleep | 1561 | | NULL | | 172 | gege| localhost:44721 | NULL | Sleep | 17 | | NULL | +-+-+-++-+--+---+---+ 24 rows in set (0.00 sec) on remote: mysql show full processlist; +-+-+-++-+--+---+---+ | Id | User| Host| db | Command | Time | State | Info | +-+-+-++-+--+---+---+ | 14 | gege| 192.168.5.108:3425 | NewProduct | Sleep | 727 | | NULL | | 20 | yuheyang| 192.168.5.108:3277 | NewProduct | Sleep | 4027 | | NULL | | 21 | yuheyang| 192.168.5.108:3278 | NewProduct | Sleep | 4027 | | NULL | | 22 | yuheyang| 192.168.5.108:3279 | NewProduct | Sleep | 4115 | | NULL | | 23 | yuheyang| 192.168.5.108:3280 | NewProduct | Sleep | 4120 | | NULL | | 24 | yuheyang| 192.168.5.108:3312 | NewProduct | Sleep | 1929 | | NULL | | 25 | yuheyang| 192.168.5.108:3313 | NewProduct | Sleep | 1929 | | NULL | | 26 | yuheyang| 192.168.5.108:3314 | NewProduct | Sleep | 1929 | | NULL | | 27 | yuheyang| 192.168.5.108:3315 | NewProduct | Sleep | 1929 | | NULL | | 28 | root| 192.168.5.108 | NULL | Sleep | 668 | | NULL | | 29 | happynessrabbit | 192.168.5.108:44555 | NewProduct | Sleep | 274 | | NULL | | 30 | happynessrabbit | 192.168.5.108:44556 | NewProduct | Sleep | 274 | | NULL | | 31 | happynessrabbit |
RE: Impossible query??
I don't think that solves the problem. There are multiple test chains with Id's less than 7. ie 7-6-4-3-2 5 1 and your query looking for history on testId=7 SELECT * FROM tests WHERE testID=7 AND connect0; could return testId's 5 and 1 as well if they were part of longer chains What is needed (and does not exist) is a recursive query that can be started on a given testId, follows the connect-testId chain and terminates when it reaches a record with connect=0 A single query can not do this. You will be forced to solve this programatically by executing a query that selects a single record based on the previous record's connect field until you reach a connect=0. (Caution, if ever a connect value points back into the chain you will enter an endless loop!) Or Re-Design your table (and inserting code ) by adding a column called baseTestId that stores the original testId. Every time a new test is added that is an extension of a previous test the previous testId is copied into the new record and the previous baseTestId is also copied to the new record baseTestID. (If a test is the first one it uses it's own id as the baseTestID). Your sample table would look like this... assuming three chains exist 7-6-4-3-2 5 1 +-+--++--+ | testId | connect | baseTestId | result | +-+--++--+ | 1 | 0| 1 | ok | | 2 | 0| 2 | nok | | 3 | 2| 2 | nok | | 4 | 3| 2 | nok | | 5 | 0| 5 | ok | | 6 | 4| 2 | nok | | 7 | 6| 2 | ok | | 8 | 0| 8 | ok | +-+--+|--+ note how all the records that are in a chain have the same baseTestID! Then this single query will do what you want SELECT B.* FROM myTable AS A LEFT OUTER JOIN myTable AS B ON(A.baseTesID=B.baseTestID) WHERE (A.testId=7) It returns ++-++--+ | testId | connect | baseTestId | result | ++-++--+ | 2 | 0 | 2 | nok | | 3 | 2 | 2 | nok | | 4 | 3 | 2 | nok | | 6 | 4 | 2 | nok | | 7 | 6 | 2 | ok | ++-++--+ The idea here is that you need a single piece of data that can relate all the records in a chain of tests together. The where clause selects the test Id in question and the join collects all records whose baseTestID is the same as the record selected by the where clause. This query would return all tests in the chain no matter which id you used. If you queried on testId 4 the same set of data would be returned, basically all tests in the chain. Now if you already have tons of data in place you will need to update the table data so that the baseTestID field reflects these new rules. Mike Scott, Ind. Contractor -Original Message- From: Becoming Digital [mailto:[EMAIL PROTECTED] Sent: Saturday, June 14, 2003 4:13 PM To: [EMAIL PROTECTED] Subject: Re: Impossible query?? Your query seems relatively easy if you don't need the first test (in this case, testID 2) explicitly printed. mysql SELECT * FROM tests WHERE testID=7 AND connect0; +-+--+--+ | testId | connect | result | +-+--+--+ | 7 | 6| ok | | 6 | 4| nok | | 4 | 3| nok | | 3 | 2| nok | +-+--+--+ The history of testID 2 is implied by your table structure, such that you know testID 2 passed because it is the last connect value. The problem I see with this occurs when you have a repeating data set. A query for your next failure (after testID 7) will also return testID 7 and its history, as would be the case with the below data. Repairing this would require adding an additional parameter to the WHERE statement, probably using BETWEEN. +-+--+--+ | testId | connect | result | +-+--+--+ | 1 | 0| ok | | 2 | 0| nok | | 3 | 2| nok | | 4 | 3| nok | | 5 | 0| ok | | 6 | 4| nok | | 7 | 6| ok | | 8 | 0| ok | | 9 | 0| nok | | 10 | 9| nok | | 11
Re: SELECT... EXCEPT FOR
How about SELECT (column1, column2, column3, etc.) FROM products... I believe something along the lines of EXCEPT FOR would be a huge violation of SQL standards. It might be useful for you, but it's likely that most users would simply declare the desired columns or exclude one programatically. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Jed Hunsaker [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, 14 June, 2003 22:03 Subject: SELECT... EXCEPT FOR If there's not a way to do this I would suggest it be added to the next version of MySQL, but has anyone ever heard of an EXCEPT FOR clause that can be used in MySQL's SELECT statements? For instance... SELECT * FROM products EXCEPT FOR colors WHERE sizes LIKE '%small%' Thanks... Jed Hunsaker [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about INSERT vs UPDATE
Consider going over Section 5.2.9 of the manual. http://www.mysql.com/doc/en/Insert_speed.html Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Shane Bryldt [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, 15 June, 2003 01:12 Subject: Question about INSERT vs UPDATE Hello, I am new to the list, so pardon me if I am on the wrong one posting my question. If so, please direct me to the right place. My question is in regards to the efficiency of INSERT statements. I have gone over some of the optimizations, and on a machine running the client and MySQL 4 server, DELETEing a table and repopulating it with ~48k records via INSERT, I have come to the conclusion that, regardless of hardware, the delay will be too significant (on a test machine it ran about 25 seconds). What I am curious to know, is whether there is a significant increase if I switch my method of saving from a complete memory dump, to a partial memory dump. The overhaul involved would require a lot of code restructuring. The situation is this, approximately 10k of those 48k records are actually modified regularily. If I overhaul the code to indicate when a record needs to be updated, or inserted, and only call the appropriate action, is the performance going to be significantly better? Keeping in mind it would have to search the 48k records to UPDATE the 10k modified records, as well as potentially INSERT new records (very few if any). With 48k records, is updating 10k records faster than simply deleting and reinserting every record? Alternatively, is there any way speeding up the INSERT time can be achieved? First, I am using the default format, I believe is MyISAM. Second, I have used table locking to optimize writing before the table is deleted and repopulated, and unlocked after all records are inserted. Third, I am using multiple INSERT lists (of 1000 records inserted at a time). Whether the lists is with 100, or 1000, I get about the same results of 25 seconds. I realize the hardware I am using is not significantly powerful, but I think there is some optimization I could make. Can you preallocate a definite number of rows for a large insert operation? Any assistance would be appreciated, this project is flexible towards new ideas to make the dumping more efficient. The alternative has been considered to use a method of UPDATEing records immediately when changes are made in memory. Transaction overhead could become an issue however, with 10k+ records actively changing at any given time. With an UPDATE method, dumping frequently may actually reduce the time required for the process, since it would update fewer records. Anyone with some insight on this would be much appreciated if they could offer some ways to speed up the process. Thanks, -Shane -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about INSERT vs UPDATE
As my post suggested, I have already addressed the tweaks this this section of the manual addresses, and was hoping there might be some insight on my original question, the process of INSERT vs UPDATE. That chapter was helpful initially, but I have already addressed most of what that chapter has to offer, however it still does not address my question. I have read that chapter thoroughly. I was hoping someone with some experience might offer something more than pointing a finger to some documentation. The documentation does not address UPDATE efficiency nor which operation takes longer on a larger scale. -Shane - Original Message - From: Becoming Digital [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, June 15, 2003 3:21 AM Subject: Re: Question about INSERT vs UPDATE Consider going over Section 5.2.9 of the manual. http://www.mysql.com/doc/en/Insert_speed.html Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Shane Bryldt [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, 15 June, 2003 01:12 Subject: Question about INSERT vs UPDATE Hello, I am new to the list, so pardon me if I am on the wrong one posting my question. If so, please direct me to the right place. My question is in regards to the efficiency of INSERT statements. I have gone over some of the optimizations, and on a machine running the client and MySQL 4 server, DELETEing a table and repopulating it with ~48k records via INSERT, I have come to the conclusion that, regardless of hardware, the delay will be too significant (on a test machine it ran about 25 seconds). What I am curious to know, is whether there is a significant increase if I switch my method of saving from a complete memory dump, to a partial memory dump. The overhaul involved would require a lot of code restructuring. The situation is this, approximately 10k of those 48k records are actually modified regularily. If I overhaul the code to indicate when a record needs to be updated, or inserted, and only call the appropriate action, is the performance going to be significantly better? Keeping in mind it would have to search the 48k records to UPDATE the 10k modified records, as well as potentially INSERT new records (very few if any). With 48k records, is updating 10k records faster than simply deleting and reinserting every record? Alternatively, is there any way speeding up the INSERT time can be achieved? First, I am using the default format, I believe is MyISAM. Second, I have used table locking to optimize writing before the table is deleted and repopulated, and unlocked after all records are inserted. Third, I am using multiple INSERT lists (of 1000 records inserted at a time). Whether the lists is with 100, or 1000, I get about the same results of 25 seconds. I realize the hardware I am using is not significantly powerful, but I think there is some optimization I could make. Can you preallocate a definite number of rows for a large insert operation? Any assistance would be appreciated, this project is flexible towards new ideas to make the dumping more efficient. The alternative has been considered to use a method of UPDATEing records immediately when changes are made in memory. Transaction overhead could become an issue however, with 10k+ records actively changing at any given time. With an UPDATE method, dumping frequently may actually reduce the time required for the process, since it would update fewer records. Anyone with some insight on this would be much appreciated if they could offer some ways to speed up the process. Thanks, -Shane -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Two Table Query
Greetings-
I request your help constructing a query to return a list of active members based on
the following:
CREATE TABLE Members(
member_number int unsigned AUTO_INCREMENT,
name varchar(25),
PRIMARY KEY(member_number))
CREATE TABLE Status(
member_number int unsigned AUTO_INCREMENT,
date DATE,
statusenum('Active', 'Inactive', 'Retired'))
So I might get:
Members:
1 Greg Lindstrom
2 Lee Ramsey
3 Don Tackett
Status:
1 2000/1/1 Active
2 2000/2/1 Active
1 2000/6/1 Inactive
3 2000/8/1 Active
1 2000/9/1 Active
2 2001/1/1 Retired
I would like the status table so I can calculate time served, but I do not know how
to construct a query to return all members with a given status for a given date.
Example: for 2000/7/1 and 'Active'
2 Lee Ramsey Active
and for 2001/2/1 and 'Active'
1 Greg Lindstrom Active
3 Don TackettActive
and 2001/2/1 'Retired'
2 Lee Ramsey Retired
This would be possible to do in an external Python method (I'm working in Zope), but
from what I've learned about SQL in the past 6 months, I'm thinking it could be done
internally (non-corrolated join?).
Thanks for your help,
Greg Lindstrom
Vilonia, Arkansas (USA)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
free table memory shrinks when data deleted
After delete a large amount of data from an innodb table, the free memory available to innodb tables as reported by 'show table status' decreased significantly rather than increasing. I am guessing this is caused by fragmentation? If so, I am guessing the only way to regain the free memory is to dump the data an recreate the table, but the table is several gigs so this would be seriously inconvenient now, and pretty much impossible in the future. So my question is whether the free memory is actually lost or just not reported correctly by 'show table status', and if lost, how can I regain it short of dumping and recreating the table. Thanks Ethan Joffe CTO Nami Media Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Two Table Query
The solution depends on which version of MySQL you are using. If you
are using 4.1, you the easiest solution is to use a sub-select.
Something like this may work:
SELECT m.member_number, m.name, s.status, s.date
FROM members AS m, status AS s ON
WHERE m.member_number = s.member_number
AND m.member_number NOT IN
(SELECT member_number FROM status WHERE status != 'Active' and date =
'2000-07-01')
AND s.date = '2000-07-01';
// This is not tested. I'm not running 4.1.
Otherwise you will need to use two queries like:
CREATE TEMPORARY TABLE inactive
SELECT member_number FROM status WHERE status != 'Active' and date =
'2000-07-01';
SELECT m.member_number, m.name, s.status, s.date
FROM members AS m
JOIN status AS s ON m.member_number = s.member_number
LEFT JOIN inactive AS i ON m.member_number = i.member_number
WHERE i.member_number IS NULL
AND s.date = '2000-07-01';
These statements produce:
mysql [test] select * from inactive;
+---++--+
| member_number | date | status |
+---++--+
| 1 | 2000-06-01 | Inactive |
+---++--+
1 row in set (0.01 sec)
/// second select from above.
+---++++
| member_number | name | status | date |
+---++++
| 2 | Lee Ramsey | Active | 2000-02-01 |
+---++++
1 row in set (0.00 sec)
Of course you would need to change status and date to what ever you are
looking for.
Your create table statement for the Status table has member_number int
unsigned AUTO_INCREMENT, you probably don't want the auto_increment on
the column.
I'm sure that if there is a better way, someone will point it out.
On Sun, 15 Jun 2003 07:44:24 -0500, [EMAIL PROTECTED] wrote:
Greetings-
I request your help constructing a query to return a list of active
members based on the following:
CREATE TABLE Members(
member_number int unsigned AUTO_INCREMENT,
name varchar(25),
PRIMARY KEY(member_number))
CREATE TABLE Status(
member_number int unsigned AUTO_INCREMENT,
date DATE,
statusenum('Active', 'Inactive', 'Retired'))
So I might get:
Members:
1 Greg Lindstrom
2 Lee Ramsey
3 Don Tackett
Status:
1 2000/1/1 Active
2 2000/2/1 Active
1 2000/6/1 Inactive
3 2000/8/1 Active
1 2000/9/1 Active
2 2001/1/1 Retired
I would like the status table so I can calculate time served, but I
do not know how to construct a query to return all members with a
given status for a given date.
Example: for 2000/7/1 and 'Active'
2 Lee Ramsey Active
and for 2001/2/1 and 'Active'
1 Greg Lindstrom Active
3 Don TackettActive
and 2001/2/1 'Retired'
2 Lee Ramsey Retired
This would be possible to do in an external Python method (I'm
working in Zope), but from what I've learned about SQL in the past 6
months, I'm thinking it could be done internally (non-corrolated
join?).
Thanks for your help,
Greg Lindstrom
Vilonia, Arkansas (USA)
---
Listserv only address.
Jeff Shapiro
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Full text search
Hello,
I have a table with just one column and with 1000 rows. It's indexed using
full text.
I've tried MATCH with AGAINST and LIKE and nothing works right!
I've tried:
SELECT * FROM 'test' WHERE MATCH (p) AGAINST ('arvor*');
but if I do
SELECT * FROM 'test' WHERE MATCH (p) AGAINST ('arvore*');
it returns some results. So, it only returns something when I pust the whole
word in the query. It doesn't accept half a word with a *.
With LIKE, things are a little different! the query only works with before
and after the word. Ex.: %word% works but not word%.
Can anybody help me, please??
Thanks,
Nuno Lopes
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql-3.23.56 and RH 7.3 - Table 'mysql.host' doesn't exist
Hi! I have redhat 7.3 with the mysql packages that came with it. Since RHSA-2003:093-14 came out I upgraded mysql with mysql-3.23.56-1.73.i386.rpm mysql-devel-3.23.56-1.73.i386.rpm mysql-server-3.23.56-1.73.i386.rpm When I try to start mysqld I get the following error message in the log: Table 'mysql.host' doesn't exist. After googling around a bit and looking at the mysql and redhat lists I found that this is well-known problem so I didn't panic and indeed found several useful suggestions: 1. The path to the basedir and/or datadir are not set properly but I did set it properly in /etc/my.cnf (the original that came with rh7.3 and the update were incorrect). 2. The privilages and/or ownership of the basedir and/or datadir are not set properly but I also did that, even setting it to 777 and ownership given to user mysql. I (would :)) use user mysql to run mysqld. 3. Mysqld is complaining about innodb but since I don't need it I give the option --skip-innodb to mysqld and this sorts all innodb issues out. After all this the bloody error message is still there and mysqld wouldn't start! Anyone has an idea? A related question: I thought about installing the RPM from mysql.com instead of the one provided by redhat. Since the RPM from redhat doesn't seem to be of the same nature as the one from mysql.com I'm not sure if I can simply do rpm -Fvh name of the package from mysql.com on top of the package from redhat. Do you have any comments on this? I would be very grateful for any help, thanks, Daniel Some (hopefully) useful info: glibc-2.2.5-34 kernel-2.4.20-18 arch: i686 _ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full text search
On Sunday, June 15, 2003, at 01:36 PM, Nuno Lopes wrote:
I have a table with just one column and with 1000 rows. It's indexed
using
full text.
I've tried MATCH with AGAINST and LIKE and nothing works right!
I've tried:
SELECT * FROM 'test' WHERE MATCH (p) AGAINST ('arvor*');
but if I do
SELECT * FROM 'test' WHERE MATCH (p) AGAINST ('arvore*');
it returns some results. So, it only returns something when I pust the
whole
word in the query. It doesn't accept half a word with a *.
MATCH...AGAINST works with full word indexes. Wildcards don't work.
With LIKE, things are a little different! the query only works with
before
and after the word. Ex.: %word% works but not word%.
It will work with 'word%' only if the first four characters of the
field are 'word'. Similarly, '%word' will match only those records with
'word' at the end of the field. With '%word%', it will match if 'word'
appears anywhere in the field.
___/
/
__/
/
/
Ed Leafe
http://leafe.com/
http://opentech.leafe.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: free table memory shrinks when data deleted
Ethan, - Original Message - From: Ethan Joffe [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Sunday, June 15, 2003 6:16 PM Subject: free table memory shrinks when data deleted After delete a large amount of data from an innodb table, the free memory available to innodb tables as reported by 'show table status' decreased significantly rather than increasing. did you wait for purge to clean up the undo log as well as the delete-marked rows? The undo log takes some space. That is why during a big delete the free space decreases until the delete transaction commits. I am guessing this is caused by fragmentation? If the deleted rows are scattered then it is possible that no space is freed from index trees. But the free space should not decrease. Did you follow the table's data and index size in SHOW TABLE STATUS. If so, I am guessing the only way to regain the free memory is to dump the data an recreate the table, but the table is several gigs so this would be seriously inconvenient now, and pretty much impossible in the future. So my question is whether the free memory is actually lost or just not reported correctly by 'show table status', and if lost, how can I regain it short of dumping and recreating the table. Thanks Ethan Joffe CTO Nami Media Inc. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Simple MySQL help needed...
Hi * I'm running MySQl 3.23 and I'm trying to run a very simple command that basically finds the highest number in a column and then for all matching rows sets their card number to be current highest +1. The code looks like this: #cardnumb is actually a string of the form SNx so set @high=x as a number select @high:=convert( right( max( cardnumb ),5) , signed ) FROM Ops; #for all matching crtieria set cardnumb to be update Ops set cardnumb = @high, @[EMAIL PROTECTED] where newphoto =1 and tbprinted =1; Without the , @[EMAIL PROTECTED] part in the script runs fine but sets everyone matching the criteria to @high, rather than increasing it. This is so incredibly simple but it just won't work for me and is not giving me any clue in the logs of on the website. Does anyone see my glaring error? I guess it should really be done by using another table but my current database is only 10k rows and grows very very slowly. Thanks in advance, P h i l l -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full text search
On Sunday, June 15, 2003, at 01:36 PM, Nuno Lopes wrote:
I have a table with just one column and with 1000 rows. It's indexed
using
full text.
I've tried MATCH with AGAINST and LIKE and nothing works right!
I've tried:
SELECT * FROM 'test' WHERE MATCH (p) AGAINST ('arvor*');
but if I do
SELECT * FROM 'test' WHERE MATCH (p) AGAINST ('arvore*');
it returns some results. So, it only returns something when I pust the
whole
word in the query. It doesn't accept half a word with a *.
MATCH...AGAINST works with full word indexes. Wildcards don't work.
With LIKE, things are a little different! the query only works with
before
and after the word. Ex.: %word% works but not word%.
It will work with 'word%' only if the first four characters of the
field are 'word'. Similarly, '%word' will match only those records with
'word' at the end of the field. With '%word%', it will match if 'word'
appears anywhere in the field.
Yes I'm doing LIKE 'A%' and I'm sure I have records starting with an 'A' and
it doesn't return any record!
___/
/
__/
/
/
Ed Leafe
http://leafe.com/
http://opentech.leafe.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Installation problems with W2K
Hi I'm unclear whether the address I'm using here (referenced on page 29 of the manual) is the correct one but I hope one of them may either get me an answer or a reference to where else I might seek help. DETAILS OS: Windows 2000 Professional, 5.0.2195 SP3 build 2195 MySQL version 4.0.13 for Windows, binary (mysql-4.0.13-win.zip) System: self constructed with AMD750MHz Athlon on Biostar M7MKE system board with VIA VT8371 and Award BIOS v6.00PG; 785,904KB RAM; ATI All-in-Wonder Pro video card; part of an Ethernet LAN on a 192.168.1 subnet, connecting to the internet through a Vigor 2000 router using ISDNe. TCP/IP is installed and working. There are two physical HDD, Disc0 of 12.66GB and Disc1 of 12.64GB. These are divided into logical drives C: (Windows, 2.00GB), D: (programs, 3.00GB), E: (Data, 4.00GB), F: (Backup_Store, 3.65GB) [Disc0], G: (Large_Data, 1.95GB) and F: (Recordings, 10.69GB). There is also a DVD drive designated as Z:. All HDDs are formatted as NTFS. PROBLEM The binary download was unzipped to a temporary file and setup run. This appeared to work correctly and the installation was made to D:\Program Files\MySQL with the data directory being set to e:\MySQL\data (and everything from the original ..\data directory moved to there). I then followed the instructions and created a file My.ini containing the installation address and data location addresses and placed this in C:\winnt. I then followed the instructions in the manual at 2.1.2, brought up a DOS box, changed to the ..\bin directory and entered mysqld --standalone. This gave some disc activity, the cursor moved to the next line and nothing else happened. I was unable to move the cursor or to enter anything further but I could close the command line window. Checking showed that the mysqld process had run for one second and could duly be removed. I have: renamed the my.ini file my.cnf and moved it to c:\ reinstalled from the exisitng zip file redownloaded the zipped binaries and reinstalled moved the ..\data directory back to the default (and duly edited the configuration files) getting in each case identical results. If I run mysqld.exe from the Windows desktop then the command window flashes up briefly and nothing further happens. Suggestions, help or pointers elsewhere here would be much appreciated. Secondly, I'm somewhat unclear (and can find nothing particular relevant on the site or in the manual) as to whether MySQL functions merely as an engine or whether there is an associated UI. That is, can it be used with ordinary desktop clients and, if so how, or is it intended purely for use through appropriate programmed interfaces? Thanks in advance Regards Murdoch Mactaggart mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple MySQL help needed...
Phill, I think the solution is simple, remove the part of the code you are having problems with, it's redundant. Instead, according to your statement, use this: UPDATE Ops SET [EMAIL PROTECTED] WHERE newphoto=1 AND tbprinted=1; As for myself, I would actually use the client implementing the statement to prepare the variables in advance. Your select statement should return 0 or more records. Assuming it has 1 or more returned, simply obtain the high value to a client stored variable, add one to it, and parse it as part of the UPDATE statement, instead of having it done on the SQL side. While it may or may not offer a benefit in a minor case like this, preparing your statements ahead of time on the client side for more complicated statements can alleviate some of the processing the MySQL server has to do. If I was using C, for a simple example, it would look as follows: char pSQL[256]; int iHighNum = ObtainHighestFromDB(void); iHighNum++; snprintf(pSQL, 256, UPDATE Ops SET cardnumb=%d WHERE newphoto=1 and tbprinted=1;, iHighNum); This in effect leaves any processing possible to the client side. Hope that helps, -Shane - Original Message - From: Phill Gillespie [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, June 15, 2003 1:53 PM Subject: Simple MySQL help needed... Hi * I'm running MySQl 3.23 and I'm trying to run a very simple command that basically finds the highest number in a column and then for all matching rows sets their card number to be current highest +1. The code looks like this: #cardnumb is actually a string of the form SNx so set @high=x as a number select @high:=convert( right( max( cardnumb ),5) , signed ) FROM Ops; #for all matching crtieria set cardnumb to be update Ops set cardnumb = @high, @[EMAIL PROTECTED] where newphoto =1 and tbprinted =1; Without the , @[EMAIL PROTECTED] part in the script runs fine but sets everyone matching the criteria to @high, rather than increasing it. This is so incredibly simple but it just won't work for me and is not giving me any clue in the logs of on the website. Does anyone see my glaring error? I guess it should really be done by using another table but my current database is only 10k rows and grows very very slowly. Thanks in advance, P h i l l -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about INSERT vs UPDATE
As my post suggested, I have already addressed the tweaks this this section of the manual addresses, and was hoping there might be some insight on my original question, the process of INSERT vs UPDATE. Gotcha. I wasn't sure if you'd checked the manual or just run a huge number of EXPLAINs on your queries. I was hoping someone with some experience might offer something more than pointing a finger to some documentation. Well, I can't offer an exact answer, but perhaps a tip. Have you considered adding a pair of DATE fields to your tables to determine when the records were added and subsequently modified? You could use those fields in conjunction with INSERT, UPDATE and IGNORE to get the desired effect. While this doesn't directly address your question, it may be a better long-term solution to your problem. If you continue going about things with your current technique, you will likely run into the same problem once your table grows, even with the best choice of UPDATE or INSERT. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Shane Bryldt [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, 15 June, 2003 06:03 Subject: Re: Question about INSERT vs UPDATE As my post suggested, I have already addressed the tweaks this this section of the manual addresses, and was hoping there might be some insight on my original question, the process of INSERT vs UPDATE. That chapter was helpful initially, but I have already addressed most of what that chapter has to offer, however it still does not address my question. I have read that chapter thoroughly. I was hoping someone with some experience might offer something more than pointing a finger to some documentation. The documentation does not address UPDATE efficiency nor which operation takes longer on a larger scale. -Shane - Original Message - From: Becoming Digital [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, June 15, 2003 3:21 AM Subject: Re: Question about INSERT vs UPDATE Consider going over Section 5.2.9 of the manual. http://www.mysql.com/doc/en/Insert_speed.html Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Shane Bryldt [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, 15 June, 2003 01:12 Subject: Question about INSERT vs UPDATE Hello, I am new to the list, so pardon me if I am on the wrong one posting my question. If so, please direct me to the right place. My question is in regards to the efficiency of INSERT statements. I have gone over some of the optimizations, and on a machine running the client and MySQL 4 server, DELETEing a table and repopulating it with ~48k records via INSERT, I have come to the conclusion that, regardless of hardware, the delay will be too significant (on a test machine it ran about 25 seconds). What I am curious to know, is whether there is a significant increase if I switch my method of saving from a complete memory dump, to a partial memory dump. The overhaul involved would require a lot of code restructuring. The situation is this, approximately 10k of those 48k records are actually modified regularily. If I overhaul the code to indicate when a record needs to be updated, or inserted, and only call the appropriate action, is the performance going to be significantly better? Keeping in mind it would have to search the 48k records to UPDATE the 10k modified records, as well as potentially INSERT new records (very few if any). With 48k records, is updating 10k records faster than simply deleting and reinserting every record? Alternatively, is there any way speeding up the INSERT time can be achieved? First, I am using the default format, I believe is MyISAM. Second, I have used table locking to optimize writing before the table is deleted and repopulated, and unlocked after all records are inserted. Third, I am using multiple INSERT lists (of 1000 records inserted at a time). Whether the lists is with 100, or 1000, I get about the same results of 25 seconds. I realize the hardware I am using is not significantly powerful, but I think there is some optimization I could make. Can you preallocate a definite number of rows for a large insert operation? Any assistance would be appreciated, this project is flexible towards new ideas to make the dumping more efficient. The alternative has been considered to use a method of UPDATEing records immediately when changes are made in memory. Transaction overhead could become an issue however, with 10k+ records actively changing at any given time. With an UPDATE method, dumping frequently may actually reduce the time required for the process, since it would update fewer records. Anyone with some insight on this would be much appreciated if they could offer some ways to speed up the process. Thanks, -Shane -- MySQL General Mailing List For list
RE: Installation problems with W2K
Murdoch Mactaggart , The WINDOWS Environment needs the --console switch in order to output the return of mysqld to DOS commands. Please ref to: http://lists.mysql.com/list.php?list=win32#b my.cnf will never be looked for by the WINDOWS Binary. Return to using my.ini from the C:\%Windows Directory% WinMySqlAdmin is an administration GUI automatically installed in the mysql\bin subdirectory. The 'Control Center', MySQL Control Center, can be downloaded and installed as the Operational GUI. Several Others are available. RE: I'm somewhat unclear (and can find nothing particular relevant on the site or in the manual) as to whether MySQL functions merely as an engine or whether there is an associated UI. That is, can it be used with ordinary desktop clients and, if so how, or is it intended purely for use through appropriate programmed interfaces? That depends on your installation. Your options are explained at the MySQL Products WWW Site page. David M Friscia friscia.rootsweb.com http://friscia.rootsweb.com [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- From: Murdoch Mactaggart [mailto:[EMAIL PROTECTED] Sent: Sunday, June 15, 2003 8:54 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Installation problems with W2K Hi I'm unclear whether the address I'm using here (referenced on page 29 of the manual) is the correct one but I hope one of them may either get me an answer or a reference to where else I might seek help. DETAILS OS: Windows 2000 Professional, 5.0.2195 SP3 build 2195 MySQL version 4.0.13 for Windows, binary (mysql-4.0.13-win.zip) System: self constructed with AMD750MHz Athlon on Biostar M7MKE system board with VIA VT8371 and Award BIOS v6.00PG; 785,904KB RAM; ATI All-in-Wonder Pro video card; part of an Ethernet LAN on a 192.168.1 subnet, connecting to the internet through a Vigor 2000 router using ISDNe. TCP/IP is installed and working. There are two physical HDD, Disc0 of 12.66GB and Disc1 of 12.64GB. These are divided into logical drives C: (Windows, 2.00GB), D: (programs, 3.00GB), E: (Data, 4.00GB), F: (Backup_Store, 3.65GB) [Disc0], G: (Large_Data, 1.95GB) and F: (Recordings, 10.69GB). There is also a DVD drive designated as Z:. All HDDs are formatted as NTFS. PROBLEM The binary download was unzipped to a temporary file and setup run. This appeared to work correctly and the installation was made to D:\Program Files\MySQL with the data directory being set to e:\MySQL\data (and everything from the original ..\data directory moved to there). I then followed the instructions and created a file My.ini containing the installation address and data location addresses and placed this in C:\winnt. I then followed the instructions in the manual at 2.1.2, brought up a DOS box, changed to the ..\bin directory and entered mysqld --standalone. This gave some disc activity, the cursor moved to the next line and nothing else happened. I was unable to move the cursor or to enter anything further but I could close the command line window. Checking showed that the mysqld process had run for one second and could duly be removed. I have: renamed the my.ini file my.cnf and moved it to c:\ reinstalled from the exisitng zip file redownloaded the zipped binaries and reinstalled moved the ..\data directory back to the default (and duly edited the configuration files) getting in each case identical results. If I run mysqld.exe from the Windows desktop then the command window flashes up briefly and nothing further happens. Suggestions, help or pointers elsewhere here would be much appreciated. Secondly, I'm somewhat unclear (and can find nothing particular relevant on the site or in the manual) as to whether MySQL functions merely as an engine or whether there is an associated UI. That is, can it be used with ordinary desktop clients and, if so how, or is it intended purely for use through appropriate programmed interfaces? Thanks in advance Regards Murdoch Mactaggart mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple MySQL help needed...
Shane Bryldt wrote: I think the solution is simple, remove the part of the code you are having problems with, it's redundant. Instead, according to your statement, use this: UPDATE Ops SET [EMAIL PROTECTED] WHERE newphoto=1 AND tbprinted=1; Thanks for the response Shane. I don't think I explained my statement clearly as your corrected code would set all matching rows to be @high+1. What I am trying to achieve is if there are 20 matching rows to the WHERE clause, then I want cardnumb to be set to @high+1, @high+2, @[EMAIL PROTECTED] respectively for the 20 records (the order they are set is irrelevant). Is it possible to have an auto-incrementing variable in an update statement like this? P h i l l As for myself, I would actually use the client implementing the statement to prepare the variables in advance. Your select statement should return 0 or more records. Assuming it has 1 or more returned, simply obtain the high value to a client stored variable, add one to it, and parse it as part of the UPDATE statement, instead of having it done on the SQL side. While it may or may not offer a benefit in a minor case like this, preparing your statements ahead of time on the client side for more complicated statements can alleviate some of the processing the MySQL server has to do. If I was using C, for a simple example, it would look as follows: char pSQL[256]; int iHighNum = ObtainHighestFromDB(void); iHighNum++; snprintf(pSQL, 256, UPDATE Ops SET cardnumb=%d WHERE newphoto=1 and tbprinted=1;, iHighNum); This in effect leaves any processing possible to the client side. Hope that helps, -Shane - Original Message - From: Phill Gillespie [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, June 15, 2003 1:53 PM Subject: Simple MySQL help needed... Hi * I'm running MySQl 3.23 and I'm trying to run a very simple command that basically finds the highest number in a column and then for all matching rows sets their card number to be current highest +1. The code looks like this: #cardnumb is actually a string of the form SNx so set @high=x as a number select @high:=convert( right( max( cardnumb ),5) , signed ) FROM Ops; #for all matching crtieria set cardnumb to be update Ops set cardnumb = @high, @[EMAIL PROTECTED] where newphoto =1 and tbprinted =1; Without the , @[EMAIL PROTECTED] part in the script runs fine but sets everyone matching the criteria to @high, rather than increasing it. This is so incredibly simple but it just won't work for me and is not giving me any clue in the logs of on the website. Does anyone see my glaring error? I guess it should really be done by using another table but my current database is only 10k rows and grows very very slowly. Thanks in advance, P h i l l -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about INSERT vs UPDATE
I'm not sure I entirely understand your solution. A datestamp would only work if you stamped the record when it's updated, and then when the next update is called, you'd have to have, throughout the program, snippets of code to edit the last modified timestamp, so it knows to update those records... This poses yet still an efficiency issue, because first, the code has to be significantly modified in every location where the record is modified in memory, to update the should dump condition. A solution similar to this that I considered, was 3 flags. Whether inserted, modified or deleted and acting upon each recording according to the current flag... However, this still poses the problem of having to edit every location in the code where the record is modified in memory to indicate it should be dumped. Retaining a last dumped timestamp would not really achieve anything. It would simply indicate when the last dump occured, but not whether it should be dumped again. Unless I have misunderstood you idea. As another possible out-of-the-box idea I had, I was considering the possibility of a delayed import. That is, to dump the entire set of SQL statements normally called in the client, to a dump.sql file, and then in turn, with another thread, use a loaded data infile to quickly import. This would take the load off the client for the duration of MySQL doing it's end of things, allowing it to continue processing without disturbing data that is being dumped (since it's already in the dump.sql) ... Fewer checks would be needed to test whether saving again is safe, while the dump.sql still exists. The thread could remove the file, and in turn indicate it's safe to save again. This keeps a thread-safe environment, and offloads all the processing short of a quick dump file to create. Would this method achieve the results I am theorizing? Hypothetically speaking, it should only take a few seconds to write 48 insert statements with a 1000 value list to a text file. This does indicate however, a delayed import. It is not safe to assume as soon as the services is done it's part, that the database is up to date. Thus, locking the tables and doing it all in one transaction in the separate thread is about as safe and fast as I can think of. Any thoughts? Also, another optimization I made, that seemed to slow things down (could just be coincidence of other processes running), was in changing my varchar fields to static sized char fields. I went from 21 seconds back to 28 seconds. I thought that strange, since it should actually be faster, or at least not so significantly slower. Again, could have been coincidence, so with a second opinion, if static char field is faster than using any text, varchar or blob fields, I'll keep the code as it is with char fields. Thanks, -Shane - Original Message - From: Becoming Digital [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Sunday, June 15, 2003 3:24 PM Subject: Re: Question about INSERT vs UPDATE As my post suggested, I have already addressed the tweaks this this section of the manual addresses, and was hoping there might be some insight on my original question, the process of INSERT vs UPDATE. Gotcha. I wasn't sure if you'd checked the manual or just run a huge number of EXPLAINs on your queries. I was hoping someone with some experience might offer something more than pointing a finger to some documentation. Well, I can't offer an exact answer, but perhaps a tip. Have you considered adding a pair of DATE fields to your tables to determine when the records were added and subsequently modified? You could use those fields in conjunction with INSERT, UPDATE and IGNORE to get the desired effect. While this doesn't directly address your question, it may be a better long-term solution to your problem. If you continue going about things with your current technique, you will likely run into the same problem once your table grows, even with the best choice of UPDATE or INSERT. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Shane Bryldt [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, 15 June, 2003 06:03 Subject: Re: Question about INSERT vs UPDATE As my post suggested, I have already addressed the tweaks this this section of the manual addresses, and was hoping there might be some insight on my original question, the process of INSERT vs UPDATE. That chapter was helpful initially, but I have already addressed most of what that chapter has to offer, however it still does not address my question. I have read that chapter thoroughly. I was hoping someone with some experience might offer something more than pointing a finger to some documentation. The documentation does not address UPDATE efficiency nor which operation takes longer on a larger scale. -Shane - Original Message - From: Becoming Digital [EMAIL PROTECTED] To:
mmmh
hello and thank you for your time. how should i code: select * from stores,subcategory,category where stores.category and subcategory.category = $category-id and stores.city or stores.zip = $cityzip order by name thank you again, tad -- Addison Ellis small independent publishing co. 114 B 29th Avenue North Nashville, TN 37203 (615) 321-1791 [EMAIL PROTECTED] [EMAIL PROTECTED] subsidiaries of small independent publishing co. [EMAIL PROTECTED] [EMAIL PROTECTED] addisonellis.com THIS E-MAIL AND ANY FILES TRANSMITTED WITH IT ARE CONFIDENTIAL AND ARE INTENDED SOLELY FOR THE INDIVIDUAL OR ENTITY TO WHOM THEY ARE ADDRESSED. IF YOU ARE NOT THE INTENDED RECIPIENT, PLEASE DO NOT READ, COPY OR RE-TRANSMIT THIS COMMUNICATION BUT DESTROY IT IMMEDIATELY. ANY UNAUTHORIZED DISSEMINATION, DISTRIBUTION OR COPYING OF THIS COMMUNICATION IS STRICTLY PROHIBITED. A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Remote access to MySQL
I have installed MySQL in Win98SE, with connection ASDL and Fixed IP. In the same machine, works web server (Apache2+PHP) and e-mail e ftp server. Everything works perfectly accessing from my INTRANET (local network). I obtain access to MySQL without problems. In Internet, all servers works perfect, except MySQL. Always I get error of access when use: mysql - h xx.xx.xx.xx - u root - p ERROR 2003: Can't connect you the MySQL server on 'xx.xx.xx.xx' (10060) Informations: * I don't have any firewall * I have all privileges to all the databases and tables, with user 'root'. * I have pleny access to the 'server' machine. * Telnet 'xx.xx.xx.xx:3306 ' also doesn't answer on Internet - In local network, the reply is positive, what confirms server is on * I work with PHP and I have access to Mysql with PHP programming, never directly. * I use 'mysqld.exe', as MySQL server, without any special configuration in my.ini: [WinMySQLAdmin] Server=C:/MYSQL/bin/mysqld.exe [mysqld] basedir=C:/MYSQL datadir=C:/MYSQL/data Summarizing: I can't access to the Mysql from Internet! Some additional suggestions? Regards, Renato Uchoa [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple MySQL help needed...
Ahh, yes, thank you for clarifying. My code would obviously have been no help. Hmm, I have not tested the theory, but I believe you could add to your SELECT statement, a declaration of the return and use it in the next INSERT. That is, SELECT @high:=convert(...) AS a FROM Ops; And then a refers to a record set populated as you needed. I am not really buff on my SQL syntax, because I typically do most processing client side, but I believe you could then set a temporary value to high, and through a loop of the records in a, increment the temp value, before assigning it to cardnum. I understand the logic, unfortunately my SQL knowledge is still a bit rusty. If it cannot be achieved with a temp value and loop on the MySQL side, the C code for it would be quite easy. However, it would involved a multiple UPDATE calls, using a transaction most likely. Lock tables too, for efficiency. Best of luck, -Shane - Original Message - From: Phill Gillespie [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, June 15, 2003 3:54 PM Subject: Re: Simple MySQL help needed... Shane Bryldt wrote: I think the solution is simple, remove the part of the code you are having problems with, it's redundant. Instead, according to your statement, use this: UPDATE Ops SET [EMAIL PROTECTED] WHERE newphoto=1 AND tbprinted=1; Thanks for the response Shane. I don't think I explained my statement clearly as your corrected code would set all matching rows to be @high+1. What I am trying to achieve is if there are 20 matching rows to the WHERE clause, then I want cardnumb to be set to @high+1, @high+2, @[EMAIL PROTECTED] respectively for the 20 records (the order they are set is irrelevant). Is it possible to have an auto-incrementing variable in an update statement like this? P h i l l As for myself, I would actually use the client implementing the statement to prepare the variables in advance. Your select statement should return 0 or more records. Assuming it has 1 or more returned, simply obtain the high value to a client stored variable, add one to it, and parse it as part of the UPDATE statement, instead of having it done on the SQL side. While it may or may not offer a benefit in a minor case like this, preparing your statements ahead of time on the client side for more complicated statements can alleviate some of the processing the MySQL server has to do. If I was using C, for a simple example, it would look as follows: char pSQL[256]; int iHighNum = ObtainHighestFromDB(void); iHighNum++; snprintf(pSQL, 256, UPDATE Ops SET cardnumb=%d WHERE newphoto=1 and tbprinted=1;, iHighNum); This in effect leaves any processing possible to the client side. Hope that helps, -Shane - Original Message - From: Phill Gillespie [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, June 15, 2003 1:53 PM Subject: Simple MySQL help needed... Hi * I'm running MySQl 3.23 and I'm trying to run a very simple command that basically finds the highest number in a column and then for all matching rows sets their card number to be current highest +1. The code looks like this: #cardnumb is actually a string of the form SNx so set @high=x as a number select @high:=convert( right( max( cardnumb ),5) , signed ) FROM Ops; #for all matching crtieria set cardnumb to be update Ops set cardnumb = @high, @[EMAIL PROTECTED] where newphoto =1 and tbprinted =1; Without the , @[EMAIL PROTECTED] part in the script runs fine but sets everyone matching the criteria to @high, rather than increasing it. This is so incredibly simple but it just won't work for me and is not giving me any clue in the logs of on the website. Does anyone see my glaring error? I guess it should really be done by using another table but my current database is only 10k rows and grows very very slowly. Thanks in advance, P h i l l -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Remote access to MySQL
I have all privileges to all the databases and tables, with user 'root'. User 'root' likely doesn't have access from the desired IP. Users are configured by both name and approved hosts. Try this: GRANT ALL ON database TO [EMAIL PROTECTED] IDENTIFIED BY 'password'; Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Renato [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, 15 June, 2003 19:28 Subject: Remote access to MySQL I have installed MySQL in Win98SE, with connection ASDL and Fixed IP. In the same machine, works web server (Apache2+PHP) and e-mail e ftp server. Everything works perfectly accessing from my INTRANET (local network). I obtain access to MySQL without problems. In Internet, all servers works perfect, except MySQL. Always I get error of access when use: mysql - h xx.xx.xx.xx - u root - p ERROR 2003: Can't connect you the MySQL server on 'xx.xx.xx.xx' (10060) Informations: * I don't have any firewall * I have all privileges to all the databases and tables, with user 'root'. * I have pleny access to the 'server' machine. * Telnet 'xx.xx.xx.xx:3306 ' also doesn't answer on Internet - In local network, the reply is positive, what confirms server is on * I work with PHP and I have access to Mysql with PHP programming, never directly. * I use 'mysqld.exe', as MySQL server, without any special configuration in my.ini: [WinMySQLAdmin] Server=C:/MYSQL/bin/mysqld.exe [mysqld] basedir=C:/MYSQL datadir=C:/MYSQL/data Summarizing: I can't access to the Mysql from Internet! Some additional suggestions? Regards, Renato Uchoa [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mmmh
With proper syntax. ;) Your WHERE conditions are improperly arranged. SELECT * FROM stores,subcategory,category WHERE (stores.category = $category-id AND subcategory.category = $category-id) OR stores.zip = $cityzip ORDER BY name; Also, assuming you're coding this in PHP, you need to make sure you handle the variable names properly (dot concatenation). Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Tad Ellis [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, 15 June, 2003 19:27 Subject: mmmh hello and thank you for your time. how should i code: select * from stores,subcategory,category where stores.category and subcategory.category = $category-id and stores.city or stores.zip = $cityzip order by name thank you again, tad -- Addison Ellis small independent publishing co. 114 B 29th Avenue North Nashville, TN 37203 (615) 321-1791 [EMAIL PROTECTED] [EMAIL PROTECTED] subsidiaries of small independent publishing co. [EMAIL PROTECTED] [EMAIL PROTECTED] addisonellis.com THIS E-MAIL AND ANY FILES TRANSMITTED WITH IT ARE CONFIDENTIAL AND ARE INTENDED SOLELY FOR THE INDIVIDUAL OR ENTITY TO WHOM THEY ARE ADDRESSED. IF YOU ARE NOT THE INTENDED RECIPIENT, PLEASE DO NOT READ, COPY OR RE-TRANSMIT THIS COMMUNICATION BUT DESTROY IT IMMEDIATELY. ANY UNAUTHORIZED DISSEMINATION, DISTRIBUTION OR COPYING OF THIS COMMUNICATION IS STRICTLY PROHIBITED. A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HELP!!! permissions not working!
Try specifying someuser at all hosts. GRANT ALL ON * TO [EMAIL PROTECTED] IDENTIFIED BY 'somepass'; Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Timothy Waters [EMAIL PROTECTED] To: MySQL mail [EMAIL PROTECTED] Sent: Saturday, 14 June, 2003 22:54 Subject: HELP!!! permissions not working! I've been messing around making very simple web pages using PHP and connecting to a MySQL database. I've tried using different users to connect and different grants, but so far the ONLY user that can connect through these pages is root, and we all know that isn't cool. I've done grant all on * to someuser identified by 'somepass'; and it STILL won't even log into MySQL using the command line! I'm totally in the dark on this one. Any clues? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
a MOVE command?
Hi... I created the following function to move a record from one table to another. The only difference in the table structures are the auto-incremented id column. The purpose of my function is to allow Admin to get user input from the queue table and after approving it she puts it in a table that will display as current events. The insert works great, but the delete from the queue table isn't working and the only work around is to go back to the queue table and delete the record that was just inserted into the current events table. I don't even know if this is possible. I guess I'm looking for a MOVE command that will delete a record from the queue table replace it with a new record in the current table. I've browsed the manual looking for info on working with two tables, but came up empty. Any words are appreciated. CodyG Victoria, BC
oops, here is the function I was talking about.
Sorry... I clicked send before pasting. Here is the function I was talking about.
function approvesignup($lid){
global $prefix, $dbi, $signqid, $ev_title, $ev_location, $ev_descrip, $ev_datetime,
$ev_organizer, $ev_contact, $ev_children, $ev_number, $ev_cost, $ev_phone;
sql_query(INSERT into $prefix._signup. VALUES (NULL, '$aid', now(), '$ev_title',
'$ev_location', '$ev_descrip', '$ev_datetime', '$ev_organizer', '$ev_contact',
'$ev_children', '$ev_number', '$ev_cost', '$ev_phone'),$dbi);
sql_query(DELETE from .$prefix._signup_queue WHERE signqid='$lid', $dbi);
Header(Location: admin.php?op=listsignupqueue);
}
several key values in one field?
Hi, while trying to handle severl key values, I wonder if it is more efficient to put those values in a row seperated by a certain seperator (eg. comma). The talbe has two fields and looks like; UID ForeinID 1 2,3,4,6 27,9,4,5 33,4 41,5,7,9 52,3,4,5 and the values of ForeignID is primary keys of another table. What would be the best way to handle this kind of case (i.e. M-to-M relationship) in MySQL? Thanks in advance. Lingua
Re: table creation - arrays ?
You are welcome. Sorry I havent been more precise - at this time I didnt find it. Have a look at the UC presentations Moving towards MySQL 5.0 from Matt Wagner and State of the Dolphin from David Axmark and Michael (Monty) Widenius http://www.mysql.com/events/uc2003/highlights.html Read a bit between the lines and you will find it ;-) Best regards Nils Valentin Tokyo/Japan 2003 6 16 01:19: thanks for the info ! wbrgds Hans From: Nils Valentin [EMAIL PROTECTED] To: Hans Nerdell [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: table creation - arrays ? Date: Sat, 14 Jun 2003 11:23:01 +0900 I have read that Arrays are not coming before 5.0. have a look in the Documentation and the UC presentations. Best regards NIls Valentin Tokyo/Japan 2003 6 10 22:25Hans Nerdell : table creation - arrays ? how are rows as ARRAYS specified and created on a table ? wbrgds hans _ Die aktuellsten Computer News gibts auf MSN! http://www.msn.at/computer -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils _ Immer auf dem Laufenden mit den Mobile Telekom News auf MSN! http://www.msn.at/mobil -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Spelling error in MySQL Reference Manual
I didn't know where to send this, so I'm sending it anyway. --- Spelling error in MySQL Reference Manual Where it reads: Portugise error messages. Should read: Portuguese error messages. Where it reads: A Portugese mailing list Should read: A Portuguese mailing list I believe this should be addressed to Paul DeBois. Arthur. --- _ Tired of spam? Get advanced junk mail protection with MSN 8. http://join.msn.com/?page=features/junkmail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql Digest 16 Jun 2003 00:46:21 -0000 Issue 2497
How do I unsub from this list? The unsub link doesn't work?
?php
If( $_POST['beer'] == 24)
{
echo(Life is good!);
exit(1);
?
}
echo(Quick it's all most closing time!!!);
more_help();
?
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Sunday, June 15, 2003 8:46 PM
To: [EMAIL PROTECTED]
Subject: mysql Digest 16 Jun 2003 00:46:21 - Issue 2497
mysql Digest 16 Jun 2003 00:46:21 - Issue 2497
Topics (messages 142726 through 142766):
howto store files in mysql
142726 by: Joe Baptista
142731 by: Becoming Digital
Re: Machine requirements
142727 by: Curtis Maurand
Re: PHP, MySQL and Apache
142728 by: Curtis Maurand
142729 by: Curtis Maurand
Re: soft real-time database
142730 by: Curtis Maurand
HELP!!! permissions not working!
142732 by: Timothy Waters
142765 by: Becoming Digital
Re: PHP, MYSQL and persistant authentication
142733 by: Daevid Vincent
Just a test
142734 by: Scott Haneda
Question about INSERT vs UPDATE
142735 by: Shane Bryldt
142742 by: Becoming Digital
142743 by: Shane Bryldt
142756 by: Becoming Digital
142759 by: Shane Bryldt
SELECT... EXCEPT FOR
142736 by: Jed Hunsaker
142741 by: Becoming Digital
Re: DATE
142737 by: Don Read
What does the ERROR mean?
142738 by: Ares Liu
[BUG] show full processlist on AIX
142739 by: Ares Liu
Re: Impossible query??
142740 by: Michael Scott
Two Table Query
142744 by: yuba.cyberback.com
142745 by: Ryan Fox
142747 by: Jeff Shapiro
free table memory shrinks when data deleted
142746 by: Ethan Joffe
142751 by: Heikki Tuuri
Full text search
142748 by: Nuno Lopes
142750 by: Ed Leafe
142753 by: Nuno Lopes
mysql-3.23.56 and RH 7.3 - Table 'mysql.host' doesn't exist
142749 by: John Smith
Simple MySQL help needed...
142752 by: Phill Gillespie
142755 by: Shane Bryldt
142758 by: Phill Gillespie
142762 by: Shane Bryldt
Installation problems with W2K
142754 by: Murdoch Mactaggart
142757 by: David M Friscia
mmmh
142760 by: Tad Ellis
142764 by: Becoming Digital
Remote access to MySQL
142761 by: Renato
142763 by: Becoming Digital
a MOVE command?
142766 by: CodyG
Administrivia:
To unsubscribe from the digest, e-mail:
[EMAIL PROTECTED]
or click:
http://lists.mysql.com/[EMAIL PROTECTED]
To post to the list, e-mail:
[EMAIL PROTECTED]
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: PHP, MySQL and Apache
Hi, I'm a newbie too. Was faced with this the other day.. I'm using Redhat 8.0 PHP-4.2.2-8.07 and when this problem surfaced, found out that phpinfo() states that it is compiled with the --with-mysql=shared. But I did not install php-MySQL*.rpm Did a rpm -Uvh php-MySQL*.rpm and it installed the mysql.so file into the /usr/lib/php4 directory. After that, you need to stop and restart httpd /sbin/service httpd restart Try again, that should do it. Cheers, Mun Heng, Ow H/M Engineering Western Digital M'sia DID : 03-7870 5168 -Original Message- From: Becoming Digital [mailto:[EMAIL PROTECTED] Sent: Saturday, June 14, 2003 2:58 AM To: [EMAIL PROTECTED] Subject: Re: PHP, MySQL and Apache If PHP was built without MySQL support, you need to recompile it. It should be in there, though, so check the php.ini file. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Subscriptions [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, 13 June, 2003 13:49 Subject: PHP, MySQL and Apache The httpd.conf connects PHP to Apache. What connects PHP to MySQL? I've looked through google and I can't find anything that makes since to me. Can someone point me in a direction as to where I can find this out? The problem I'm having is I have PHP 4.3.2, MySQL 4.0.13 and Apache 2.0.46. I have a page that is trying to connect to MySQL via PHP. The error is: Fatal error: Call to undefined function: mysql_connect() in /var/www/html/ww3.polkmechanical.com/test/mysql_test.php on line 8 The code on line 8 is: $link = mysql_connect( localhost, user, password ) or die(mysql_error()); I can log on through mysql at a telnet prompt with the database, user and password. Ty... . When governments fear the people there is liberty. When the people fear the government there is tyranny. -Thomas Jefferson, third US president, architect and author (1743-1826) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: PHP, MYSQL and persistant authentication
You need to create a cookie session using start_session() and something like that. I'm actually reading up on it on this book PHP MySQL Web development - luke welling Laura thomson (i think) Cheers, Mun Heng, Ow H/M Engineering Western Digital M'sia DID : 03-7870 5168 -Original Message- From: sgannon60 [mailto:[EMAIL PROTECTED] Sent: Saturday, June 14, 2003 8:08 AM To: [EMAIL PROTECTED] Subject: PHP, MYSQL and persistant authentication Hi, Does anyone know of a good tutorial on setting up a login page, and passing the login primary key to other pages so that other tables will be able to refernce the unique login thanks steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Set no root password
Simply I didn't know where the password was located in myphpadmin. The password is not in myphpadmin. It is in the database. Log in as root and look at the mysql database. Also, read sections 4.2 and 4.3 of the MySQL manual. I wanted it secure but I wanted to be able to get to the data with myphpadmin too. But now I know where it is I put the password in. And now I want to protect phpmyadmin so that only folks with a username/password can access it. -- Joel Rees [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
