Weird UPDATE Problem
Hello all. I have recently finished my migration from an older server to a newer server running RHEL 6. The MySQL version went from 5.0.77 to 5.1.52. In my application, this query used to work just fine: $paid_query = mysql_query(UPDATE $table_name SET owed = 0 WHERE s_id = $student); Where table_name was mysql_real_escape_string(collection_41_students). With the new MySQL version, the UPDATE query does not work, and echo mysql_error(); results nothing. Is my syntax correct going from version to version? Thanks in advance, David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Southern hemisphere polar alignment // I/C Mr. Rob Kanen
Dear Rob: I live in Brazil, have a SkyWatcher EQ6 pro mount (with a GPS module) and a William Optics 132 FLT refracting telescope. I plan on doing my observations between the towns of Canoinhas and Tres Barras, in the State of Santa Catarina (second southernmost State in Brazil - lats. approx. -26º,10',0 and -26º,6',0, respectively). The mount is a computerized Goto mount and has a 3 star alignment system. Since I´m a beginner in astronomy (a judge by profession) and will be going on vacation in the 2nd half of November when I plan on doing my observations, can you help me align my scope with my equipment/location in mind, kinda doing through the process step by step? I would really appreciate any input since I´m a dummie about these things. Thanx !! Newton Varella, Jr. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MS SQL emulator for MySQL in order to support MS Project Server, Sharepoint...
I apologize for asking this question. I am somewhat confused by Microsoft's Licensing and I personally can not justify to senior management why MS SQL should be procured instead of procuring software more urgent requirements. I had the not so bright idea that maybe a MS SQL emulator or compatibility mode existed for/in MySQL. Does such a beast exist? In particular, I wanted to use MySQL as backend database for MS Project Server and Sharepoint, and other applications that use MS SQL as their backend database. Again, I apologize for asking this question. I could not figure out the correct string to search for such software. thank you
Select rows containing identical values in two columns
I have a table that has a column with the id of the person that created the row. In another column in the same row there is a column with the id of the person that modified that row. Is there a way to write a SELECT statement that will return all the rows where the value in the creation column equals the value in the modification column? I don't want to specify a specific id in either of the columns. TIA Stephen P. Fracek, Jr. [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL ERROR on DELETE
Hi there ! Does anyone knows if there is a specific MySQL ERROR (SQLSATE) for DELETE of a not found key? I have tried MySQL Error 1032 e 1176 (SQLSTATE HY000) but it does not seems to work .. Thanks in advance Nilson
How does one transpose a group of columns into rows?
I have a table: Id |path1 | display| value 1 | ostype | os|windows 1 | ostype | ver |NT4 2 | ostype | os | linux 2 | ostype | ver | RHEL 5.4 That I would like to tranform into Id | os| ver 1 |windows | NT4 2| linux |RHEL 5.4 Thank you, Raymond smime.p7s Description: S/MIME cryptographic signature
no caching of Stored Procedure results?
I am using MySql 5.1.14-beta with Java/ConnectorJ on the client. Section '5.14. The MySQL Query Cache' states 'The query cache is not used for server-side prepared statements' and I have confirmed that the results of a stored procedure that makes use of a passed in parameter are not in fact stored into the query cache. I did not realize this before putting a lot of work into my stored procs. I did so partly because of the performance benefits (I thought) of using stored procedures but I now find that I am missing out on a major performance enhancement -- results caching. The stored procs I am concerned about only contain selects. I know that stored procs are complicated for the cahcing system to analyze but it seems that MySQL could know if a stored proc contains only selects (or made use of my annotation 'DETERMINISTIC READS SQL DATA') that it would then be able to cache the results. Anyone have suggestions to enable me to get caching on the mysql server for the selects inside my stored procs? I could move the selects out to my client code but I would prefer not to -- one of my reasons for using sp's was so that I could limit the permissions of my client user to execute permissions only for designated sprocs and no select permissions. Thanks, Peter Andrews -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Adding and Removing tables from MERGE tables dynamically
Thanks for the information.
I want to make sure that I understand: Do you run ALTER TABLE command
on a live database(table) that is doing inserts;
Or, do you stop accepting Remote connections, flush the tables, run the
ALTER TABLE command,
start accepting connections?
Thank you,
Raymond
-Original Message-
From: Brent Baisley [mailto:[EMAIL PROTECTED]
Sent: Wednesday, September 06, 2006 10:24
To: Jacob, Raymond A Jr; mysql@lists.mysql.com
Subject: Re: Adding and Removing tables from MERGE tables dynamically
I've got a similar setup, total records across about 8 tables hoovers
around 200 million.
To change a merge table just issue an alter table with a new union.
ALTER TABLE mergetable UNION=(table1, table2, table3,...);
- Original Message -
From: Jacob, Raymond A Jr [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday, September 05, 2006 5:29 PM
Subject: Adding and Removing tables from MERGE tables dynamically
Problem: I use two applications one called snort, the client that
inserts data into eleven(11) tables.
The other application BASE joins the tables into one table. When the
tables become 4GB in size,
deleting records for one month becomes unbearably slow(20-30days). The
search(Select ) is slow too but that
is a problem with the BASE application.
I thought that using MERGE tables would allow an administrator to create
a monthly table, using the original table names, composed of daily or
weekly
tables, by appending the date of creation to the table i.e.
data_table1_-MM-DD and join_table_-MM-DD. From the
documentation:
creating the table with INSERT_METHOD = FIRST results in INSERTs being
done to first table in the MERGE UNION statement.
I will assume that the first table is the latest table.
So one of the first tables should look like:
CREATE TABLE original_table
{
...
} TYPE = MERGE UNION = (data_table1_2006-09-12
,data_table1_2006-09-05)
Using cron and depending on the interval chosen daily, weekly,
bi-weekly, monthly, quarterly, or yearly at the start
of a new interval, a new table would be created with current date in
the -MM-DD format.
For example: if the start of new interval begins a week from today on
2006-09-12. At 12:00am on
2006-09-12, a script would create new tables that would look like:
CREATE TABLE data_table1_2006-09-12
{
...
}
One the MERGE TABLES should look like:
CREATE TABLE original_table
{
...
} TYPE = MERGE UNION = (data_table1_2006-09-12
,data_table1_2006-09-05)
On every Tuesday(in this case) from now on, new tables are created
ending with date in the format -MM-DD
and merged into the original table.
So that by 2006-09-30, one of the MERGE tables should look something
like
...
} TYPE = MERGE UNION = (data_table1_2006-09-26, data_table1_2006-09-19,
data_table1_2006-09-12, data_table1_2006-09-05)
On 2006-10-05 at 00:00hrs the newest table data_table1_2006-10-05
should be created and merged into the original_table. The oldest table
in this case data_table1_2006-09-05 should be removed from one of the
MERGE tables in this case original_table. The resulting merge table
should look something like
...
} TYPE = MERGE UNION = (data_table1_2006-10-05,data_table1_2006-09-26,
data_table1_2006-09-19, data_table1_2006-09-12)
Question: How does one add data_table1_2006-09-12 to original_table
dynamically?
Question: How does one remove data_table1_2006-09-05 from the
original_table dynamically?
Question: In other words, can tables be added and removed dynamically
to/from a MERGE TABLE?
Benefit: I hope is to archive individual tables. When I need to review
old data I will use a copy of the BASE application, then
Merge the tables that I am interested in, in order to search smaller
tables without changing the BASE application.
Question: Is this possible. Do these question make sense?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Adding and Removing tables from MERGE tables dynamically
Problem: I use two applications one called snort, the client that
inserts data into eleven(11) tables.
The other application BASE joins the tables into one table. When the
tables become 4GB in size,
deleting records for one month becomes unbearably slow(20-30days). The
search(Select ) is slow too but that
is a problem with the BASE application.
I thought that using MERGE tables would allow an administrator to create
a monthly table, using the original table names, composed of daily or
weekly
tables, by appending the date of creation to the table i.e.
data_table1_-MM-DD and join_table_-MM-DD. From the
documentation:
creating the table with INSERT_METHOD = FIRST results in INSERTs being
done to first table in the MERGE UNION statement.
I will assume that the first table is the latest table.
So one of the first tables should look like:
CREATE TABLE original_table
{
...
} TYPE = MERGE UNION = (data_table1_2006-09-12
,data_table1_2006-09-05)
Using cron and depending on the interval chosen daily, weekly,
bi-weekly, monthly, quarterly, or yearly at the start
of a new interval, a new table would be created with current date in
the -MM-DD format.
For example: if the start of new interval begins a week from today on
2006-09-12. At 12:00am on
2006-09-12, a script would create new tables that would look like:
CREATE TABLE data_table1_2006-09-12
{
...
}
One the MERGE TABLES should look like:
CREATE TABLE original_table
{
...
} TYPE = MERGE UNION = (data_table1_2006-09-12
,data_table1_2006-09-05)
On every Tuesday(in this case) from now on, new tables are created
ending with date in the format -MM-DD
and merged into the original table.
So that by 2006-09-30, one of the MERGE tables should look something
like
...
} TYPE = MERGE UNION = (data_table1_2006-09-26, data_table1_2006-09-19,
data_table1_2006-09-12, data_table1_2006-09-05)
On 2006-10-05 at 00:00hrs the newest table data_table1_2006-10-05
should be created and merged into the original_table. The oldest table
in this case data_table1_2006-09-05 should be removed from one of the
MERGE tables in this case original_table. The resulting merge table
should look something like
...
} TYPE = MERGE UNION = (data_table1_2006-10-05,data_table1_2006-09-26,
data_table1_2006-09-19, data_table1_2006-09-12)
Question: How does one add data_table1_2006-09-12 to original_table
dynamically?
Question: How does one remove data_table1_2006-09-05 from the
original_table dynamically?
Question: In other words, can tables be added and removed dynamically
to/from a MERGE TABLE?
Benefit: I hope is to archive individual tables. When I need to review
old data I will use a copy of the BASE application, then
Merge the tables that I am interested in, in order to search smaller
tables without changing the BASE application.
Question: Is this possible. Do these question make sense?
[5.0] Left Join Problem
I'm trying to upgrade from MySQL 4.1 to MySQL 5.0. A query that works in MySQL 4.1 does not work in MySQL 5.0 and I'm at a loss as to how to proceed. SELECT tab_a.item1, tab_b.item2, tab_c.item3, tab_d.item4, tab_e.item5, tab_f.item6 FROM db.tab_a, db.tab_e, db.tab_f LEFT JOIN db.tab_b ON tab_b.id = tab_a.id LEFT JOIN db.tab_c ON tab_c.id = tab_a.id LEFT JOIN db.tab_d ON tab_d.id = tab_a.id WHERE tab_a.id = 'value' AND tab_a.id2 = tab_e.id2 AND tab_e.id3 = tab_f.id3 LIMIT 1; The above query now generates this error: Unknown column 'tab_a.id in 'on clause'. Placing parentheses around the ON clause, LEFT JOIN db.tab_b ON (tab_b.id = tab_a.id) does not work. However, splitting the original query apart and grouping the Left Joins in one query and the regular joins in another query does NOT generates any errors: SELECT tab_a.item1, tab_b.item2, tab_c.item3, tab_d.item4 FROM db.tab_a LEFT JOIN db.tab_b ON tab_b.id = tab_a.id LEFT JOIN db.tab_c ON tab_c.id = tab_a.id LEFT JOIN db.tab_d ON tab_d.id = tab_a.id WHERE tab_a.id = 'value' LIMIT 1; - this is ok AND SELECT tab_a.item1, tab_e.item5, tab_f.item6 FROM db.tab_a, db.tab_e, db.tab_f WHERE tab_a.id = 'value' AND tab_a.id2 = tab_e.id2 AND tab_e.id3 = tab_f.id3 LIMIT 1; - this is ok Why doesn't the original query work in MySQL 5.0? What do I need to do to make it work? TIA Stephen P. Fracek, Jr. [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [5.0] Left Join Problem
On 2006-07-21 1:01 PM, Gerald L. Clark [EMAIL PROTECTED] wrote: It is a join precedence issue. Use INNER Join instead of a comma. Thanks Gerald. Paul DuBois' polite suggestion to read the manual helped. Upon re-reading the section about the change in precedence with the comma operator and the join, I realized there was a simple fix and that I had misinterpreted the section on the first read. The revised query works. Stephen P. Fracek, Jr. [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I ran of disk space running optimize on table.... Now I can not open data.MYI
Now, I when I do a desc on data table, I get the error ERROR 1016 (HY000): Can't open file: 'data.MYI' (errorno: 144). As I recall I ran: optimize data; The Optimize command did not complete the error as I recall was /var filesystem full. Running the following commands: ls data.* data.MYD data.MYIdata.frm acid# ls -last snort/data.* 2 -rw-rw 1 mysql mysql1024 Jul 12 14:23 data.MYI 4543712 -rw-rw 1 mysql mysql 4650467248 Jul 12 14:09 data.MYD 10 -rw-rw 1 mysql mysql8632 Jun 28 20:54 data.frm Is there anything I can do to get this table back? r/Raymond
RE: I ran of disk space running optimize on table.... Now I can not open data.MYI
Thank you, raymond -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Thursday, July 13, 2006 18:07 To: Jacob, Raymond A Jr Cc: mysql@lists.mysql.com Subject: Re: I ran of disk space running optimize on table Now I can not open data.MYI Hmmm. Doesn't look pretty, Raymond! You've got 4.6GB of data and just 1K of indexes now. Step ZERO - make a backup copy of the files you have NOW, in case any repair operations you try make things worse. On the bright side, your data file (.MYD) should sitll be intact - it's just the index file (.MYI) that's hosed. At a high level, you're going to want to rebuild that index file. How - is a little trickier. See http://dev.mysql.com/doc/refman/5.0/en/repair.html I expect you'll want to look at the section titled Stage 3: Difficult repair because the first part of your index file is in fact destroyed. Good luck! Dan On 7/13/06, Jacob, Raymond A Jr [EMAIL PROTECTED] wrote: Now, I when I do a desc on data table, I get the error ERROR 1016 (HY000): Can't open file: 'data.MYI' (errorno: 144). As I recall I ran: optimize data; The Optimize command did not complete the error as I recall was /var filesystem full. Running the following commands: ls data.* data.MYD data.MYIdata.frm acid# ls -last snort/data.* 2 -rw-rw 1 mysql mysql1024 Jul 12 14:23 data.MYI 4543712 -rw-rw 1 mysql mysql 4650467248 Jul 12 14:09 data.MYD 10 -rw-rw 1 mysql mysql8632 Jun 28 20:54 data.frm Is there anything I can do to get this table back? r/Raymond -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trying to understand why Select running out of memory if table not used
When I try to retrieve all rows in the event table that are in a particular range and then try to determine if the cid is in the data table the join takes forever. So I tried just tried adding the table to see if the where clause was slowing things down. Below is a portion on my session. mysql select cid, timestamp from event where timestamp between '2006-05-01' AND '2006-05-15'; | 7753544 | 2006-05-14 23:59:58 | | 7753545 | 2006-05-15 00:00:00 | | 7753546 | 2006-05-15 00:00:00 | +-+-+ 1336344 rows in set (32.55 sec) mysql select event.cid, event.timestamp from event, data where mysql event.timestamp between '2006-05-01' AND '2006-05-15'; mysql: Out of memory (Needed 1477684 bytes) ERROR 2008 (HY000): MySQL client ran out of memory Obviously, that is a bad idea. I just can not figure Out how to speed the select clause up. I was using the query: Select event.cid, event.timestamp from event, data Where ( event.timestamp between '2006-05-01' AND '2006-05-15' ) and event.cid=data.cid; But the query never completed i.e. I aborted the query after a few hours. Thank you, Raymond -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How does one speed up delete-Again
Could the problem the Locked data table in this case?
mysql show processlist;
+-+---+---+---+-+---
-+--+---
---+
| Id | User | Host | db| Command |
Time | State| Info
|
+-+---+---+---+-+---
-+--+---
---+
| 1 | goat | xxx:62693 | snort | Query |
424668 | Locked | INSERT INTO data (sid,cid,data_payload) VALUES
('2','13305243','45C10FEF40002F11D162CD9E |
| 524 | root | localhost | snort | Query |
424669 | Sending data | DELETE data from data JOIN sidtemp ON data.cid =
sidtemp.cid |
| 537 | root | localhost | snort | Query |
242922 | Locked | DELETE data
FROM data, event
WHERE data.cid = event.cid
AND event.timestamp 2006-05-01 |
| 542 | root | localhost | NULL | Query |
0 | NULL | show processlist
|
+-+---+---+---+-+---
-+--+-
I killed 524 537, started over and CPU went down. The query is still
slow.
Do I need to bring the database down to single user mode(I don't know
the correct
Term so I just am guessing single user mode exists) then do the delete?
Thank you,
Raymond
Re: How does one speed up delete-Again
It appears that every time I start query the event or the data table
gets Locked.
Could this have any affect on why it takes so long to delete records.
Grasping at straws,
Thank you,
Raymond
mysql show processlist;
+-+---+---+---+-
+--+--+--
+
| Id | User | Host | db|
Command | Time | State| Info
|
+-+---+---+---+-
+--+--+--
+
| 1 |goat| xxx:62693 |snort| Query |
1555 | Locked | INSER
T INTO event (sid,cid,signature,timestamp) VALUES ('2', '13305568',
'12', '2006-07-10 11:48:22. |
| 542 | root | localhost |snort| Query
| 1555 | Sending data | DELET
E
data
FROM data, event
WHERE data.cid = event.cid
AND event.timestamp 2006-04-01
|
| 543 |goat| xxx:62680 | snort_archive | Sleep
| 1555 | | NULL
|
| 544 | root | localhost |snort| Query
|0 | NULL | show
processlist
|
+-+---+---+---+-
+--+--processlist
|
| 545 | root | localhost |snort| Query
| 308 | Sending data | DELET
E data FROM data, event
WHERE data.cid = event.cid AND event.timestamp '2006-03-03'
|
+-+---+---+---+-
+--+--+--
+
4 rows in set (0.00 sec)
mysql show processlist;
+-+---+---+---+-
+--+--+--
+
| Id | User | Host | db|
Command | Time | State| Info
|
+-+---+---+---+-
+--+--+--
+
| 1 |goat| xxx:62693 |snort| Query |
333 | Locked | INSER
T INTO event (sid,cid,signature,timestamp) VALUES ('2', '13305782',
'12', '2006-07-10 12:16:30. |
| 543 |goat| xxx:62680 | snort_archive | Sleep
| 333 | | NULL
|
| 544 | root | localhost |snort| Query
|0 | NULL | show
processlist
|
| 545 | root | localhost |snort| Query
| 333 | Sending data | DELET
E data FROM data, event
WHERE data.cid = event.cid AND event.timestamp '2006-03-03'
|
+-+---+---+---+-
+--+--+--
+
4 rows in set (0.00 sec)
mysql show processlist;
+-+---+---+---+-
+--+--+--
+
| Id | User | Host | db|
Command | Time | State| Info
|
+-+---+---+---+-
+--+--+--
+
| 1 |goat| xxx:62693 |snort| Query |
337 | Locked | INSER
T INTO event (sid,cid,signature,timestamp) VALUES ('2', '13305782',
'12', '2006-07-10 12:16:30. |
| 543 |goat| xxx:62680 | snort_archive | Sleep
| 337 | | NULL
|
| 544 | root | localhost |snort| Query
|0 | NULL | show
processlist
|
| 545 | root | localhost |snort| Query
| 337 | Sending data | DELET
E data FROM data, event
WHERE data.cid = event.cid AND event.timestamp '2006-03-03'
|
+-+---+---+---+-
+--+--+--
+
4 rows in set (0.01 sec)
mysql show processlist;
+-+---+---+---+-
+--+--+--
+
| Id | User | Host | db|
Command | Time | State| Info
How does one speed up delete-Again
I started the operation below on Friday at 1300hrs EST DELETE data FROM data, event WHERE data.cid = event.cid AND event.timestamp 2006-05-01 It is now Sunday 22:00hrs EST and the operation is still running. Question: Should it take this long to delete 7.5 million records from a 4.5GB Table? Question: Other than writing a script to export all the cid's to a file and deleting the records one at a time so at least I can delete some records. Is there a way to delete records one at a time or in groups so that if I have to stop the operation the delete will not rolled back? Question:Does anyone on the list have experience deleting what I guess is a large number of records from a large table? i.e. how long does it take? r/Raymond -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How does one speed up delete.
Env: Freebsd 6.0 MySql 4.1.18 Mem: 1GB(?) can not tell without rebooting Disk Avail: 4GB Problem: the table data is 4.5GB. I created a temporary table sidtemp in the database snort by typing: CREATE TEMPORARY TABLE sidtemp SELECT cid FROM event WHERE timestamp '2006-05-01'; Query OK, 7501376 rows affected (36.38 sec) Records: 7501376 Duplicates: 0 Warnings: 0 Next I want to delete all rows from the table data when data.cid = sidtemp.cid So I started the following command on Jul 5 at 16:44 GMT: DELETE data FROM data JOIN sidtemp ON data.cid = sidtemp.cid It is now Jul 7 19:56 GMT. I had forgotten how long it takes to run this delete command as I recall it takes 15-20days on just one database. I have two(2) Databases with the same schema. The databases are live now and Usually without executing this delete mysql uses between 0-10% Of the CPU. The delete is causing the mysql to use between 98-99% of the CPU. Any ideas on what I can do to speed up the Delete? Thank you Raymond
RE: How does one speed up delete.
-Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Friday, July 07, 2006 15:48 To: Jacob, Raymond A Jr Cc: mysql@lists.mysql.com Subject: Re: How does one speed up delete. Raymond, I would expect that adding an index on 'cid' column in your 'sidtemp' table would help quite a bit. Out of curiousity, why use a temp table in this situation? Why not Dan: I had erroneously assumed that the delete would delete rows from data and event. DELETE data FROM data, event WHERE data.cid = event.cid AND event.timestamp 2006-05-01 I just stopped my previous query. I am running the above now. I used a temporary table because I thought I only needed the table to hold the events.cid's temporarily that I wished to delete from the data table. Can you tell when I should use temporary tables. Below I believe command below demonstrates that an index exists on data and event? mysql show index from data; +---++--+--+-+-- -+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+-- -+-+--++--++-+ | data | 0 | PRIMARY |1 | sid | A | 3 | NULL | NULL | | BTREE | | | data | 0 | PRIMARY |2 | cid | A | 9678480 | NULL | NULL | | BTREE | | +---++--+--+-+-- -+-+--++--++-+ 2 rows in set (0.00 sec) mysql show index from event; +---++--+--+-+-- -+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+-- -+-+--++--++-+ | event | 0 | PRIMARY |1 | sid | A |NULL | NULL | NULL | | BTREE | | | event | 0 | PRIMARY |2 | cid | A |14389173 | NULL | NULL | | BTREE | | | event | 1 | sig |1 | signature | A |NULL | NULL | NULL | | BTREE | | | event | 1 | time |1 | timestamp | A |NULL | NULL | NULL | | BTREE | | +---++--+--+-+-- -+-+--++--++-+ 4 rows in set (0.00 sec) Thank you, raymond On 7/7/06, Jacob, Raymond A Jr [EMAIL PROTECTED] wrote: Env: Freebsd 6.0 MySql 4.1.18 Mem: 1GB(?) can not tell without rebooting Disk Avail: 4GB Problem: the table data is 4.5GB. I created a temporary table sidtemp in the database snort by typing: CREATE TEMPORARY TABLE sidtemp SELECT cid FROM event WHERE timestamp '2006-05-01'; Query OK, 7501376 rows affected (36.38 sec) Records: 7501376 Duplicates: 0 Warnings: 0 Next I want to delete all rows from the table data when data.cid = sidtemp.cid So I started the following command on Jul 5 at 16:44 GMT: DELETE data FROM data JOIN sidtemp ON data.cid = sidtemp.cid It is now Jul 7 19:56 GMT. I had forgotten how long it takes to run this delete command as I recall it takes 15-20days on just one database. I have two(2) Databases with the same schema. The databases are live now and Usually without executing this delete mysql uses between 0-10% Of the CPU. The delete is causing the mysql to use between 98-99% of the CPU. Any ideas on what I can do to speed up the Delete? Thank you Raymond -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Temporary table ERROR 1109 (42S02) where are temporary tables kept?
I ran the following commands: USE snort; CREATE TEMPORARY TABLE sidtemp SELECT cid FROM event WHERE timestamp '2006-05-01'; ... SELECT count(*) from sidtemp; count(*) 7501376 DELETE FROM data WHERE data.cid = sidtemp.cid; ERROR 1109 (42S02): Unkown table 'sidtemp' in where clause SHOW tables; Does not include sidtemp in the list of tables in the snort database nor would I expect it to. Question: What database is the table sidtemp in? r/Raymond
RE: Temporary table ERROR 1109 (42S02) where are temporary tables kept?
Thank you, I was definitely on the wrong track on this one. I annotated your commands to make sure that I understood what they were doing. Are my comments correct? --- You have the wrong syntax. You can't mention a table in the WHERE clause that wasn't in the FROM clause. Try DELETE data FROM data JOIN sidtemp ON data.cid = sidtemp.cid; /* deletes all records in data with cid equal cid in sidtemp but leaves sidtemp unchanged */ or DELETE data FROM data, sidtemp WHERE data.cid = sidtemp.cid; /* deletes all records in data and sidtemp where cids are equal */ -- /* Will the USING clause work also? */ or /* looking that link below: */ DELETE data FROM data USING data, sidtemp WHERE data.cid = sidtemp.cid or DELETE data FROM data USING data, sidtemp JOIN sidtemp ON data.cid = sidtemp.cid Is the above correct also? Thank you, raymond -- See the manual for details http://dev.mysql.com/doc/refman/4.1/en/delete.html. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Temporary table ERROR 1109 (42S02) where are temporary tables kept?
Thanks again, raymond -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 05, 2006 14:54 To: Jacob, Raymond A Jr Cc: mysql@lists.mysql.com Subject: Re: Temporary table ERROR 1109 (42S02) where are temporary tables kept? Jacob, Raymond A Jr wrote: Thank you, I was definitely on the wrong track on this one. I annotated your commands to make sure that I understood what they were doing. Are my comments correct? --- You have the wrong syntax. You can't mention a table in the WHERE clause that wasn't in the FROM clause. Try DELETE data FROM data JOIN sidtemp ON data.cid = sidtemp.cid; /* deletes all records in data with cid equal cid in sidtemp but leaves sidtemp unchanged */ Correct. or DELETE data FROM data, sidtemp WHERE data.cid = sidtemp.cid; /* deletes all records in data and sidtemp where cids are equal */ No, this only deletes from data. These 2 are supposed to be equivalent. Using this version of the syntax deletes rows from the tables named *before* the FROM. Tables used to determine the matching rows come after the FROM. The first version I gave uses an explicit JOIN, the second uses the implicit, comma join. I prefer explicit joins, but I included the implicit join because it seemed to be what you were trying. -- /* Will the USING clause work also? */ It should. or /* looking that link below: */ DELETE data FROM data USING data, sidtemp WHERE data.cid = sidtemp.cid or DELETE data FROM data USING data, sidtemp JOIN sidtemp ON data.cid = sidtemp.cid Is the above correct also? Almost. In the USING form, the tables which should lose rows go after FROM, while the tables used to make the selection go after USING. Hence, the query would be DELETE FROM data USING data JOIN sidtemp ON data.cid = sidtemp.cid; or DELETE FROM data USING data, sidtemp WHERE data.cid = sidtemp.cid; In general, the implicit join syntax (comma between tables with join condition(s) in the WHERE clause) seems easy to use, but frequently leads to trouble. The explicit join syntax (table JOIN table ON condition) is much clearer, which should help avoid mistakes. You should probably read the manual page describing JOIN syntax http://dev.mysql.com/doc/refman/4.1/en/join.html. Thank you, raymond Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Solved: Client still reports table full
I forgot I have two databases snortdb and archive_snortdb with same schema. Syslog did not distinguish between the two(2). Both were suffering from the 4GB limit. Once I increased max_row the error stopped on the client. Brent, Thanks again, Raymond -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: Friday, June 30, 2006 8:49 To: Jacob, Raymond A Jr; mysql@lists.mysql.com Subject: Re: Client still reports table full Wow, I'm really sorry about that. Left out a zero. I should stop answering questions before the holiday weekend. I was suggesting a minor change to 500 to see if that would work. Everything I've read about adjusting for table full errors always specifies both. Since only one was changed, MySQL might not have realized things were different(?). Unfortunately, there's not a whole lot of info on this topic, at least that I can find. I learned a while ago to set these parameters when I create the table if I think the table will be huge. I've also switched to using merge tables, which makes it a lot easier to archive parts of the table. You might try running the alter table command again with both variables specified. - Original Message - From: Jacob, Raymond A Jr [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, June 29, 2006 5:21 PM Subject: RE: Client still reports table full If I understand the results from SHOW TABLE STATUS LIKE 'data'; My avg_row_length = 497 Why would descreasing it to 50 have a positive Effect. I would assume I should increase it? Thank you/Raymond -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: Thursday, June 29, 2006 15:53 To: Jacob, Raymond A Jr; mysql@lists.mysql.com Subject: Re: Client still reports table full Oops, left out an important part. You should change the Avg_row_length also. ALTER TABLE AVG_ROW_LENGTH = 50 You need to specify an average row length if you have dynamic length fields in the table (blob, text, etc.). Also, perhaps a silly question which you may have answered earlier, but does you file system allow files larger than 4GB? Sometimes you have to specifically enable that feature in a file system. If that doesn't work, or you're limited to 4GB files, you may need to switch to using a merge table. - Original Message - From: Jacob, Raymond A Jr [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, June 29, 2006 1:37 PM Subject: Client still reports table full Yesterday: I ran the following command: ALTER TABLE data max_rows=1100 Today: The client still reported table is full. I rebooted the client and stopped and started the mysql server. I still get the table is full error on the data table. I ran the command: \ echo SHOW TABLE STATUS LIKE 'data'; | mysql -u user -p snortdb \ below is the output. Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment data MyISAM 9 Dynamic 8721565 497 4335220336 1099511627775 127599616 0 NULL 2006-06-28 20:54:55 2006-06-29 18:02:32 NULL latin1_swedish_ci NULL max_rows=1100 \\ df shows /var the partition with the database has enoungh room: Filesystem1K-blocks Used Avail Capacity Mounted on /dev/amrd0s1f 27792614 18449326 711988072%/var -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Client still reports table full
Yesterday: I ran the following command: ALTER TABLE data max_rows=1100 Today: The client still reported table is full. I rebooted the client and stopped and started the mysql server. I still get the table is full error on the data table. I ran the command: \ echo SHOW TABLE STATUS LIKE 'data'; | mysql -u user -p snortdb \ below is the output. NameEngine Version Row_format RowsAvg_row_length Data_length Max_data_length Index_lengthData_free Auto_increment Create_time Update_time Check_time Collation ChecksumCreate_options Comment dataMyISAM 9 Dynamic 8721565 497 4335220336 1099511627775 127599616 0 NULL2006-06-28 20:54:55 2006-06-29 18:02:32 NULLlatin1_swedish_ci NULL max_rows=1100 \\ df shows /var the partition with the database has enoungh room: Filesystem1K-blocks Used Avail Capacity Mounted on /dev/amrd0s1f 27792614 18449326 711988072%/var -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Client still reports table full
If I understand the results from SHOW TABLE STATUS LIKE 'data'; My avg_row_length = 497 Why would descreasing it to 50 have a positive Effect. I would assume I should increase it? Thank you/Raymond -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: Thursday, June 29, 2006 15:53 To: Jacob, Raymond A Jr; mysql@lists.mysql.com Subject: Re: Client still reports table full Oops, left out an important part. You should change the Avg_row_length also. ALTER TABLE AVG_ROW_LENGTH = 50 You need to specify an average row length if you have dynamic length fields in the table (blob, text, etc.). Also, perhaps a silly question which you may have answered earlier, but does you file system allow files larger than 4GB? Sometimes you have to specifically enable that feature in a file system. If that doesn't work, or you're limited to 4GB files, you may need to switch to using a merge table. - Original Message - From: Jacob, Raymond A Jr [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, June 29, 2006 1:37 PM Subject: Client still reports table full Yesterday: I ran the following command: ALTER TABLE data max_rows=1100 Today: The client still reported table is full. I rebooted the client and stopped and started the mysql server. I still get the table is full error on the data table. I ran the command: \ echo SHOW TABLE STATUS LIKE 'data'; | mysql -u user -p snortdb \ below is the output. Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment data MyISAM 9 Dynamic 8721565 497 4335220336 1099511627775 127599616 0 NULL 2006-06-28 20:54:55 2006-06-29 18:02:32 NULL latin1_swedish_ci NULL max_rows=1100 \\ df shows /var the partition with the database has enoungh room: Filesystem1K-blocks Used Avail Capacity Mounted on /dev/amrd0s1f 27792614 18449326 711988072%/var -- 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: Client still reports table full
I will try REPAIR TABLE data. data.MYD is 4.1G so I am over the 4GB limit and growing. I also tried: CHECK TABLE data QUICK; CHECK TABLE data MEDIUM; CHECK TABLE data EXTENDED; They all showed OK. Thank you again, raymond -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Thursday, June 29, 2006 17:55 To: Jacob, Raymond A Jr Cc: mysql@lists.mysql.com Subject: Re: Client still reports table full I'm not sure that avg_row_length has a bearing on your problem right now ... the output of show table status you posted earlier shows that you have: current data length: 4335220336 maximum data length: 1099511627775 data_free: 0 (oddly) data_free should be something like 1099511627775 - 4335220336 = 1095176407439 I wonder if the table was marked as being in an error status when it filled up earlier and now needs a REPAIR operation (see my earlier post). Alternatively, Brent's question about a filesystem limit seems pertinent. What OS and filesystem are you using? Though I would think your error would change from a MySQL 'table is full' error to some kind of OS-related error... Dan On 6/29/06, Jacob, Raymond A Jr [EMAIL PROTECTED] wrote: If I understand the results from SHOW TABLE STATUS LIKE 'data'; My avg_row_length = 497 Why would descreasing it to 50 have a positive Effect. I would assume I should increase it? Thank you/Raymond -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: Thursday, June 29, 2006 15:53 To: Jacob, Raymond A Jr; mysql@lists.mysql.com Subject: Re: Client still reports table full Oops, left out an important part. You should change the Avg_row_length also. ALTER TABLE AVG_ROW_LENGTH = 50 You need to specify an average row length if you have dynamic length fields in the table (blob, text, etc.). Also, perhaps a silly question which you may have answered earlier, but does you file system allow files larger than 4GB? Sometimes you have to specifically enable that feature in a file system. If that doesn't work, or you're limited to 4GB files, you may need to switch to using a merge table. - Original Message - From: Jacob, Raymond A Jr [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, June 29, 2006 1:37 PM Subject: Client still reports table full Yesterday: I ran the following command: ALTER TABLE data max_rows=1100 Today: The client still reported table is full. I rebooted the client and stopped and started the mysql server. I still get the table is full error on the data table. I ran the command: \ echo SHOW TABLE STATUS LIKE 'data'; | mysql -u user -p snortdb \ below is the output. Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment data MyISAM 9 Dynamic 8721565 497 4335220336 1099511627775 127599616 0 NULL 2006-06-28 20:54:55 2006-06-29 18:02:32 NULL latin1_swedish_ci NULL max_rows=1100 \\ df shows /var the partition with the database has enoungh room: Filesystem1K-blocks Used Avail Capacity Mounted on /dev/amrd0s1f 27792614 18449326 711988072%/var -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sorry for the dumb question how do I fix table is full?
Environment: Freebsd 6.0 Mysql : mysql Ver 14.7 Distrib 4.1.18, for porbld-freebsd6.0 (i386) using 5.0 On the client, I get /var/log/messages, I get the errors: kernel: 9643D22706C and database: mysql_error: The table 'data' is full SQL=INSERT INTO data (sid,cid,data_payload) VALUES I have 7Gig free on the database Server. I assumed tables became full when I ran out of disk space. I guess I was wrong. On the server /var/log/messages is clear except for entries when I ran out of disk space On June 3,2006. Please tell what do I need to do stop the client from generating error messages and sending Data to the server? Thank you, raymond -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Sorry for the dumb question how do I fix table is full?
mysql SHOW TABLE STATUS LIKE 'data'; +--++-++-++- +-+-- +---++-+ -++-- -+--++-+ | Name | Engine | Version | Row_format | Rows| Avg_row_length | Data_length | Max_data_length | Index _length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +--++-++-++- +-+-- +---++-+ -++-- -+--++-+ | data | MyISAM | 9 | Dynamic| 8593198 |499 | 4294967280 | 4294967295 |11 5791872 | 0 | NULL | 2006-03-02 18:47:02 | 2006-06-09 21:08:48 | NULL | latin1_sw edish_ci | NULL || | +--++-++-++- +-+-- +---++-+ -++-- -+--++-+ 1 row in set (0.07 sec) mysql -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 28, 2006 12:43 To: Jacob, Raymond A Jr Cc: mysql@lists.mysql.com Subject: Re: Sorry for the dumb question how do I fix table is full? Raymond, can you post the output of SHOW TABLE STATUS LIKE 'data'; that should show how big your table is and how big it can be... Dan On 6/28/06, Jacob, Raymond A Jr [EMAIL PROTECTED] wrote: Environment: Freebsd 6.0 Mysql : mysql Ver 14.7 Distrib 4.1.18, for porbld-freebsd6.0 (i386) using 5.0 On the client, I get /var/log/messages, I get the errors: kernel: 9643D22706C and database: mysql_error: The table 'data' is full SQL=INSERT INTO data (sid,cid,data_payload) VALUES I have 7Gig free on the database Server. I assumed tables became full when I ran out of disk space. I guess I was wrong. On the server /var/log/messages is clear except for entries when I ran out of disk space On June 3,2006. Please tell what do I need to do stop the client from generating error messages and sending Data to the server? Thank you, raymond -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: (thank you) Sorry for the dumb question how do I fix table is full?
thank you, raymond -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 28, 2006 16:07 To: Jacob, Raymond A Jr; mysql@lists.mysql.com Subject: Re: Sorry for the dumb question how do I fix table is full? I agree it's not very clear. I think Brent's example is perfect: ALTER TABLE data max_rows=1 (or whatever number you believe is appropriate for your table) Dan On 6/28/06, Jacob, Raymond A Jr [EMAIL PROTECTED] wrote: The documentation does not seem very clear, at least me on how to increase the max_rows. Could you give me an example? Thank you raymond -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 28, 2006 15:38 To: Jacob, Raymond A Jr Cc: mysql@lists.mysql.com Subject: Re: Sorry for the dumb question how do I fix table is full? Your table has just about max'd out - you're using 4,294,967,280 bytes out of a maximum of 4,294,967,295 bytes (15 bytes free). You need to tell mysql to expand this table, with an alter table command to increase max rows: http://dev.mysql.com/doc/refman/5.0/en/table-size.html Dan On 6/28/06, Jacob, Raymond A Jr [EMAIL PROTECTED] wrote: mysql SHOW TABLE STATUS LIKE 'data'; +--++-++-++- +--++-++-++- +--++-++-++- +--++-++-++- +--++-++-++- +-+-- +---++-+ +---++-+-- +---++-+-- -++-- -+--++-+ | Name | Engine | Version | Row_format | Rows| Avg_row_length | Data_length | Max_data_length | Index _length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +--++-++-++- +--++-++-++- +--++-++-++- +--++-++-++- +--++-++-++- +-+-- +---++-+ +---++-+-- +---++-+-- -++-- -+--++-+ | data | MyISAM | 9 | Dynamic| 8593198 |499 | 4294967280 | 4294967295 |11 5791872 | 0 | NULL | 2006-03-02 18:47:02 | 2006-06-09 21:08:48 | NULL | latin1_sw edish_ci | NULL || | +--++-++-++- +--++-++-++- +--++-++-++- +--++-++-++- +--++-++-++- +-+-- +---++-+ +---++-+-- +---++-+-- -++-- -+--++-+ 1 row in set (0.07 sec) mysql -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 28, 2006 12:43 To: Jacob, Raymond A Jr Cc: mysql@lists.mysql.com Subject: Re: Sorry for the dumb question how do I fix table is full? Raymond, can you post the output of SHOW TABLE STATUS LIKE 'data'; that should show how big your table is and how big it can be... Dan On 6/28/06, Jacob, Raymond A Jr [EMAIL PROTECTED] wrote: Environment: Freebsd 6.0 Mysql : mysql Ver 14.7 Distrib 4.1.18, for porbld-freebsd6.0 (i386) using 5.0 On the client, I get /var/log/messages, I get the errors: kernel: 9643D22706C and database: mysql_error: The table 'data' is full SQL=INSERT INTO data (sid,cid,data_payload) VALUES I have 7Gig free on the database Server. I assumed tables became full when I ran out of disk space. I guess I was wrong. On the server /var/log/messages is clear except for entries when I ran out of disk space On June 3,2006. Please tell what do I need to do stop the client from generating error messages
Can I delete old host-bin.xxxxx files in order to free up some space?
I have not been monitoring my databases now I am using a 100% disk space. thank you, Raymond
RE: Can I delete old host-bin.xxxxx files in order to free up some space?
-Original Message- From: Gary Richardson [mailto:[EMAIL PROTECTED] Sent: Saturday, June 03, 2006 14:10 To: Jacob, Raymond A Jr Cc: mysql@lists.mysql.com Subject: Re: Can I delete old host-bin.x files in order to free up some space? Yes you can. Be sure not to delete the one the database is currently writing to. Are you replicating your database? [Jacob, Raymond A Jr] No. Are you using them for point in time restores? [Jacob, Raymond A Jr] No. If you're replicating, make sure your replicas have all caught up on the files you're deleting. [Jacob, Raymond A Jr] To remove the excess binary update logs, and start again , should I run the SQL command: RESET MASTER? Or if I don't need to reconstruct or restore a table may I just delete them are comment log-bin out of my.cnf and restart mysql? thank you, Raymond -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Can I delete old host-bin.xxxxx files in order to free up some space?
Thank you Raymond that did the trick. -Original Message- From: Gary Richardson [mailto:[EMAIL PROTECTED] Sent: Saturday, June 03, 2006 14:39 To: Jacob, Raymond A Jr Cc: mysql@lists.mysql.com Subject: Re: Can I delete old host-bin.x files in order to free up some space? I believe the command is PURGE MASTER LOGS TO 'host-bin.xx' where x is between 0-9 or something like that. On 6/3/06, Jacob, Raymond A Jr [EMAIL PROTECTED] wrote: -Original Message- From: Gary Richardson [mailto: [EMAIL PROTECTED] Sent: Saturday, June 03, 2006 14:10 To: Jacob, Raymond A Jr Cc: mysql@lists.mysql.com Subject: Re: Can I delete old host-bin.x files in order to free up some space? Yes you can. Be sure not to delete the one the database is currently writing to. Are you replicating your database? [Jacob, Raymond A Jr] No. Are you using them for point in time restores? [Jacob, Raymond A Jr] No. If you're replicating, make sure your replicas have all caught up on the files you're deleting. [Jacob, Raymond A Jr] To remove the excess binary update logs, and start again , should I run the SQL command: RESET MASTER? Or if I don't need to reconstruct or restore a table may I just delete them are comment log-bin out of my.cnf and restart mysql? thank you, Raymond -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimizing DISTINCT searches
Several of my DISTINCT searches are frequently showing up in the slow query log. These queries use multiple table joins. Using EXPLAIN shows that the queries are using the appropriate keys, as far as I know. Are DISTINCT searches using multiple joins slow? TIA. Stephen P. Fracek, Jr. [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: Optimizing DISTINCT searches
On 2006-05-01 11:55 AM, Robert DiFalco [EMAIL PROTECTED] wrote: Well, normally a DISTINCT has to do a type of sort and is slower than non-DISTINCT queries. Each field of the result set is considered in the DISTINCT logic. Can you modify the query so that it does not require the DISTINCT? Can you post the query? Robert - Query: SELECT DISTINCT Project.Site_ID, Site, Status, Type FROM Project, Site WHERE Site.Site_ID = Project.Site_ID ORDER BY Site; Site is the site name, Status and Type contain additional information about the site, and Site_ID is the unique site id. The Project table contains among other things a list of sites where the projects are being done. The results of this query are supposed to be a non-duplicated list of sites that are associated with at least one project. As the number of projects and sites have increased, this query is now frequently in the slow query log. Stephen P. Fracek, Jr. [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing DISTINCT searches
On 2006-05-01 1:14 PM, Robert DiFalco [EMAIL PROTECTED] wrote: Would you need the DISTINCT if you change the query like so? SELECT Site.Site_ID, Site, Status, Type FROM Site JOIN Project ON Site.Site_ID = Project.Site_ID ORDER BY Site; You may also want to just try your initial query without the distinct to see if that is the issue. Also, do you have an index on the Site column? The issue with this query is that you are pretty much selecting everything from the Project table. Robert - Your query doesn't work - it finds ALL the rows in Project table and hence repeats the sites.. I do have an index on the Site table, it is the Site_ID. The Project.Site_ID is also indexed. Stephen P. Fracek, Jr. [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TIMESTAMP field not automatically updating last_updated field
Hank wrote: Are the other fields in the update statement actually changing the data? I don't know for sure, but if the data on disk is the same as the update statement, mysql won't actually update the record, and therefore might not update the last_updated field also. Just a thought. Yes, I understand that one concept. I have seen it before If you do an update on a record but the actually values that you are passing in the statement are the exact values as were there before, no update to the timestamp field is made because none of the records values actually changed But no, that is not my situation. I've tested it and I am actually changing the values in the table (of course not specifying a new value for the TIMESTAMP field) but still the TIMESTAMP field doesn't auto-update. What disturbes me is that it works fine in one particular table but all the others it works. Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TIMESTAMP field not automatically updating last_updated field
Ferindo Middleton Jr wrote: Hank wrote: Are the other fields in the update statement actually changing the data? I don't know for sure, but if the data on disk is the same as the update statement, mysql won't actually update the record, and therefore might not update the last_updated field also. Just a thought. Yes, I understand that one concept. I have seen it before If you do an update on a record but the actually values that you are passing in the statement are the exact values as were there before, no update to the timestamp field is made because none of the records values actually changed But no, that is not my situation. I've tested it and I am actually changing the values in the table (of course not specifying a new value for the TIMESTAMP field) but still the TIMESTAMP field doesn't auto-update. What disturbes me is that it works fine in one particular table but all the others it works. Ferindo I'm running 5.0.19-nt. I haven't had a chance to test it but should it make any difference if I say: last_updated TIMESTAMP, than if I say all this: last_updatedTIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, I think this may be the difference in why some tables are auto incrementing and others aren't. Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
TIMESTAMP field not automatically updating last_updated field
I think I've seen this complaint posted before but I ignored but now I realize that in some of my db tables' last_updated field the value is automatically updating on UPDATEs to records while in other tables the last_updated fields for some strange reason aren't automatically updating. I'll usually use the following line in my table declarations: last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, In some tables it automatically updates on subsequent updates to the table and in others it will not. The purpose here is to have the last_updated field automatically append to the current timestamp... the application on the front end doesn't specify the time to MySQL but rather expects that it's always going to be UPDATEd to the current time slot. What am I doing wrong what command should I issue to my tables to correct it? Thanks Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TIMESTAMP field not automatically updating last_updated field
jonathan wrote: are you having two timestamp fields in a table (ie a created and a last_updated)? -j On Mar 30, 2006, at 5:17 PM, Ferindo Middleton Jr wrote: I think I've seen this complaint posted before but I ignored but now I realize that in some of my db tables' last_updated field the value is automatically updating on UPDATEs to records while in other tables the last_updated fields for some strange reason aren't automatically updating. I'll usually use the following line in my table declarations: last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, In some tables it automatically updates on subsequent updates to the table and in others it will not. The purpose here is to have the last_updated field automatically append to the current timestamp... the application on the front end doesn't specify the time to MySQL but rather expects that it's always going to be UPDATEd to the current time slot. What am I doing wrong what command should I issue to my tables to correct it? Thanks Ferindo --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] No just the one timestamp field (last_updated) which I expect to be given a timestamp on the initial INSERT and then continue to be automatically updated to the current time on subsequent UPDATEs to any given row... Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql performance problems.
After a 23days of running mysql, I have a 3GB database. When I use an application called base(v.1.2.2) a web based intrusion detection analysis console, the mysqld utilization shoots up to over 90% and stays there until the application times out or is terminated. Question: Have I made some error in configuration? When I don't run the application base, mysqld utilization is between 30-50%. Question: What hardware do I need to speed up queries? Question: How do determine if the query is the problem? Data: I used my-large.cnf as the basis of my.cnf. Hardware and OS info: ... FreeBSD 6.0-RELEASE-p5 #0: ... CPU: Intel Pentium III (997.46-MHz 686-class CPU) Origin = GenuineIntel Id = 0x68a Stepping = 10 Features=0x383fbffFPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE,MCA,CMOV,PAT,PSE36,MMX,FXSR,SSE real memory = 1073676288 (1023 MB) avail memory = 1041784832 (993 MB) Observations: Disk Space used: du -am /var/db/mysql | sort -nr | head -20 5259mysql/ 3055mysql/snort 2184mysql/snort_archive 1546mysql/snort_archive/data.MYD 1546mysql/snort/data.MYD 560 mysql/snort/acid_event.MYI 311 mysql/snort/acid_event.MYD 132 mysql/snort_archive/event.MYI 132 mysql/snort/event.MYI 116 mysql/snort_archive/iphdr.MYI 116 mysql/snort/iphdr.MYI 112 mysql/snort_archive/iphdr.MYD 112 mysql/snort/iphdr.MYD 74 mysql/snort_archive/event.MYD 74 mysql/snort/event.MYD 42 mysql/snort_archive/data.MYI 42 mysql/snort/data.MYI 40 mysql/snort_archive/icmphdr.MYI 40 mysql/snort/icmphdr.MYI 35 mysql/snort_archive/icmphdr.MYD ... snort is 3GB snort_archive is 2GB(snort_archive acid and base tables have not been built that is why snort archive is smaller) When the application searches the database, the mysqld utilization goes up to over 90% until the application times out. top last pid: 44263; load averages: 0.95, 0.89, 0.76 up 25+23:49:4416:07:17 49 processes: 2 running, 47 sleeping Mem: 173M Active, 608M Inact, 186M Wired, 29M Cache, 111M Buf, 1660K Free Swap: 2048M Total, 156K Used, 2048M Free PID USERNAME THR PRI NICE SIZERES STATETIME WCPU COMMAND 31890 mysql 15 200 103M 79032K kserel 768:38 93.46% mysqld 49138 www 1 40 17432K 12848K accept 0:23 0.00% httpd 46759 www 1 200 16584K 12084K lockf0:21 0.00% httpd 46764 www 1 40 16632K 12072K accept 0:21 0.00% httpd 46763 www 1 40 16580K 12012K accept 0:20 0.00% httpd 46760 www 1 40 17452K 12872K accept 0:19 0.00% httpd 46762 www 1 40 16568K 12000K accept 0:19 0.00% httpd 46761 www 1 40 16608K 12088K sbwait 0:17 0.00% httpd 68456 www 1 40 16572K 11980K accept 0:17 0.00% httpd 68457 www 1 40 16724K 11824K accept 0:17 0.00% httpd 68458 www 1 40 16980K 11920K accept 0:17 0.00% httpd Processes that run in the background: I run an update process in the background with hope that if I process the alerts from the snort table on a regular basis.o I won't have process a large number( 44,000) alerts first thing in the morning. The update process inserts records into the acid table that result from the join of certain fields from the snort tables. (Schema at http://www.andrew.cmu.edu/user/rdanyliw/snort/acid_db_er_v102.html ) rabid# cat /var/log/base-update.2006-03-28.log 2006-03-28, 00:05:00, Added 3808 alert(s) to the Alert cache 2006-03-28, 01:05:00, Added 5855 alert(s) to the Alert cache 2006-03-28, 02:05:00, Added 4096 alert(s) to the Alert cache 2006-03-28, 03:05:00, Added 4473 alert(s) to the Alert cache 2006-03-28, 04:05:00, Added 4378 alert(s) to the Alert cache 2006-03-28, 05:05:00, Added 4087 alert(s) to the Alert cache 2006-03-28, 06:05:00, Added 5163 alert(s) to the Alert cache 2006-03-28, 07:05:00, Added 4789 alert(s) to the Alert cache 2006-03-28, 08:05:00, Added 4411 alert(s) to the Alert cache 2006-03-28, 09:05:00, Added 4830 alert(s) to the Alert cache 2006-03-28, 10:05:00, Added 4739 alert(s) to the Alert cache 2006-03-28, 11:05:00, Added 5360 alert(s) to the Alert cache 2006-03-28, 12:05:00, Added 7305 alert(s) to the Alert cache 2006-03-28, 13:05:00, Added 8481 alert(s) to the Alert cache 2006-03-28, 14:05:00, Added 60731 alert(s) to the Alert cache 2006-03-28, 15:05:00, Added 44328 alert(s) to the Alert cache 2006-03-28, 15:50:00, Added 13742 alert(s) to the Alert cache 2006-03-28, 15:55:00, Added 607 alert(s) to the Alert cache 2006-03-28, 16:00:00, Added 938 alert(s) to the Alert cache 2006-03-28, 16:05:00, Added 1348 alert(s) to the Alert cache 2006-03-28, 16:10:02, Added 687 alert(s) to the Alert cache 2006-03-28, 16:15:00, Added 1168 alert(s) to the Alert cache 2006-03-28, 16:20:00, Added 1760 alert(s) to the Alert cache 2006-03-28, 16:25:00, Added 814 alert(s) to the Alert cache 2006-03-28, 16:30:01, Added 617 alert(s) to the Alert cache
getting COUNT() TO return 0 for null matches in a query, how?
I have the following query which counts the records from a table called registration that have an schedule_id that matches a record in another table called schedules. The below query works fine but how can I get it to return a COUNT() of 0 each instance where there is no record in the registration table that matches a schedules.id record? SELECT schedules.id, schedules.start_date, schedules.end_date, COUNT(schedules.id) FROM schedules, registration_and_attendance WHERE registration_and_attendance.schedule_id = schedules.id GROUP BY schedules.id ORDER BY start_date -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
including column name headers in OUTFILE output?
If say something like the following from the mysql command line tool: SELECT * FROM dognames INTO OUTFILE 'C:/outfiles/dognames.tab'; How do I get mysql to include the column names in the file's output? Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL ignores foreign key relationships between tables?
Paul DuBois wrote: At 20:41 -0500 1/30/06, Ferindo Middleton Jr wrote: Paul DuBois wrote: At 18:03 -0500 1/29/06, Ferindo Middleton Jr wrote: Ferindo Middleton Jr wrote: Ferindo Middleton Jr wrote: Paul DuBois wrote: At 17:56 -0500 1/28/06, Ferindo Middleton Jr wrote: I have two tables, registration schedules, that look like this: CREATE TABLE registration ( idSERIAL NOT NULL UNIQUE, firstnameVARCHAR(256) NOT NULL, middlenameTEXT, lastnameVARCHAR(256), suffix TEXT, schedule_id INTEGER REFERENCES schedules(id), ); CREATE TABLE schedules ( id SERIAL NOT NULL UNIQUE, start_date DATE NOT NULL, end_date DATE NOT NULL, ); The registration table above references the the schedules table via the schedule_id. Why does MySQL allow a row created in the schedules table be DELETED if it has a matching schedule_id in the registration table. These two tables share a relationship based on registration.schedule_id schedules.id. I've tried this same syntax in PostgreSQL and it doesn't allow the schedules.id record to be deleted without first removing any records in the registration table which carry a matching schedule_id record. Isn't that the point of a relational database?- TO CHECK RELATIONSHIPS between tables and enforce that those relationships aren't broken? I find it disappointing that MySQL ignores this relationship. Add ENGINE = InnoDB to the end of your table definitions. Foreign keys are supported only for InnoDB tables in MySQL. I am using InnoDB. I use MySQL Administrator and InnoDB is what it says all my tables are already using so it must have chosen that by default or something. Does this mean that I shouldn't have been able to delete records from my schedules table above that had a foreign key in the registration table? Thanks. Ferindo Paul, I discovered that this foreign key constraint wasn't present in these tables anymore due to my own action. You see, I had backed up my database before using MySQL Administrator, not knowing that is was backing up such tables constructs as foreign keys, etc. So the database I'm looking at today isn't the same database I originally created with the same constraints... I'm going to stop using MySQL Administrator... using it seems somewhat misleading and it made me think that the tables sand the constraints I made on them were still present. Thanks. Ferindo I take it back. I imported the data in my database above without using the MySQL Administrator backup utility and first re-CREATEing the db tables in my database But still MySQL still allows for cross-referenced records between my schedules table and the registration table schedule id field to be deleted. Why do you think this is happening. Is this yet another feature that MySQL doesn't really support yet? Is MySQL totally ignoring the REFERENCES part of the schedule_id field from my registration table above. I've determined that I'm using InnoDB so why isn't it working? Ferindo Looking at: http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html I see no examples that are missing FOREIGN KEY. What happens if you change your table definition to change: schedule_id INTEGER REFERENCES schedules(id) To: schedule_id INTEGER, FOREIGN KEY (schedule_id) REFERENCES schedules(id) Also, SERIAL is an alias for a BIGINT type, so you'll probably need to make schedule_id a BIGINT. Thanks Paul, However, I tried changing my table definition in a test db like you suggest above to say: schedule_id BIGINT, FOREIGN KEY (schedule_id) REFERENCES schedules(id) ... but I get this error message still which seems to imply that I'm not forming this foreign key constraint correctly: ERROR 1005 (HY000): Can't create table '.\test\registration.frm' (errno: 150) any ideas what I'm doing wrong? I think so. But first, a tip: When you get an error like that from InnoDB, try SHOW ENGINE INNODB STATUS (or just SHOW INNODB STATUS in older versions of MySQL). Part of the output of this statement will likely contain more detail about the error. In this case, the error is my fault. :-) The referenced column and the referencing column must have the same data type, and I said that SERIAL was an alias for a BIGINT column. But it's really a BIGINT UNSIGNED column, so schedule_id has to be BIGINT UNSIGNED as well. And since SERIAL is also NOT NULL, you might as well make schedule_id NOT NULL, too. These definitions worked for me: CREATE TABLE schedules ( id SERIAL NOT NULL UNIQUE, start_date DATE NOT NULL, end_dateDATE NOT NULL ) ENGINE = InnoDB; CREATE TABLE registration ( idSERIAL
Re: MySQL ignores foreign key relationships between tables?
Paul DuBois wrote: At 18:03 -0500 1/29/06, Ferindo Middleton Jr wrote: Ferindo Middleton Jr wrote: Ferindo Middleton Jr wrote: Paul DuBois wrote: At 17:56 -0500 1/28/06, Ferindo Middleton Jr wrote: I have two tables, registration schedules, that look like this: CREATE TABLE registration ( idSERIAL NOT NULL UNIQUE, firstnameVARCHAR(256) NOT NULL, middlenameTEXT, lastnameVARCHAR(256), suffix TEXT, schedule_id INTEGER REFERENCES schedules(id), ); CREATE TABLE schedules ( id SERIAL NOT NULL UNIQUE, start_date DATE NOT NULL, end_date DATE NOT NULL, ); The registration table above references the the schedules table via the schedule_id. Why does MySQL allow a row created in the schedules table be DELETED if it has a matching schedule_id in the registration table. These two tables share a relationship based on registration.schedule_id schedules.id. I've tried this same syntax in PostgreSQL and it doesn't allow the schedules.id record to be deleted without first removing any records in the registration table which carry a matching schedule_id record. Isn't that the point of a relational database?- TO CHECK RELATIONSHIPS between tables and enforce that those relationships aren't broken? I find it disappointing that MySQL ignores this relationship. Add ENGINE = InnoDB to the end of your table definitions. Foreign keys are supported only for InnoDB tables in MySQL. I am using InnoDB. I use MySQL Administrator and InnoDB is what it says all my tables are already using so it must have chosen that by default or something. Does this mean that I shouldn't have been able to delete records from my schedules table above that had a foreign key in the registration table? Thanks. Ferindo Paul, I discovered that this foreign key constraint wasn't present in these tables anymore due to my own action. You see, I had backed up my database before using MySQL Administrator, not knowing that is was backing up such tables constructs as foreign keys, etc. So the database I'm looking at today isn't the same database I originally created with the same constraints... I'm going to stop using MySQL Administrator... using it seems somewhat misleading and it made me think that the tables sand the constraints I made on them were still present. Thanks. Ferindo I take it back. I imported the data in my database above without using the MySQL Administrator backup utility and first re-CREATEing the db tables in my database But still MySQL still allows for cross-referenced records between my schedules table and the registration table schedule id field to be deleted. Why do you think this is happening. Is this yet another feature that MySQL doesn't really support yet? Is MySQL totally ignoring the REFERENCES part of the schedule_id field from my registration table above. I've determined that I'm using InnoDB so why isn't it working? Ferindo Looking at: http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html I see no examples that are missing FOREIGN KEY. What happens if you change your table definition to change: schedule_id INTEGER REFERENCES schedules(id) To: schedule_id INTEGER, FOREIGN KEY (schedule_id) REFERENCES schedules(id) Also, SERIAL is an alias for a BIGINT type, so you'll probably need to make schedule_id a BIGINT. Thanks Paul, However, I tried changing my table definition in a test db like you suggest above to say: schedule_id BIGINT, FOREIGN KEY (schedule_id) REFERENCES schedules(id) ... but I get this error message still which seems to imply that I'm not forming this foreign key constraint correctly: ERROR 1005 (HY000): Can't create table '.\test\registration.frm' (errno: 150) any ideas what I'm doing wrong? Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL ignores foreign key relationships between tables?
Ferindo Middleton Jr wrote: Ferindo Middleton Jr wrote: Paul DuBois wrote: At 17:56 -0500 1/28/06, Ferindo Middleton Jr wrote: I have two tables, registration schedules, that look like this: CREATE TABLE registration ( idSERIAL NOT NULL UNIQUE, firstnameVARCHAR(256) NOT NULL, middlenameTEXT, lastnameVARCHAR(256), suffix TEXT, schedule_id INTEGER REFERENCES schedules(id), ); CREATE TABLE schedules ( id SERIAL NOT NULL UNIQUE, start_date DATE NOT NULL, end_date DATE NOT NULL, ); The registration table above references the the schedules table via the schedule_id. Why does MySQL allow a row created in the schedules table be DELETED if it has a matching schedule_id in the registration table. These two tables share a relationship based on registration.schedule_id schedules.id. I've tried this same syntax in PostgreSQL and it doesn't allow the schedules.id record to be deleted without first removing any records in the registration table which carry a matching schedule_id record. Isn't that the point of a relational database?- TO CHECK RELATIONSHIPS between tables and enforce that those relationships aren't broken? I find it disappointing that MySQL ignores this relationship. Add ENGINE = InnoDB to the end of your table definitions. Foreign keys are supported only for InnoDB tables in MySQL. I am using InnoDB. I use MySQL Administrator and InnoDB is what it says all my tables are already using so it must have chosen that by default or something. Does this mean that I shouldn't have been able to delete records from my schedules table above that had a foreign key in the registration table? Thanks. Ferindo Paul, I discovered that this foreign key constraint wasn't present in these tables anymore due to my own action. You see, I had backed up my database before using MySQL Administrator, not knowing that is was backing up such tables constructs as foreign keys, etc. So the database I'm looking at today isn't the same database I originally created with the same constraints... I'm going to stop using MySQL Administrator... using it seems somewhat misleading and it made me think that the tables sand the constraints I made on them were still present. Thanks. Ferindo I take it back. I imported the data in my database above without using the MySQL Administrator backup utility and first re-CREATEing the db tables in my database But still MySQL still allows for cross-referenced records between my schedules table and the registration table schedule id field to be deleted. Why do you think this is happening. Is this yet another feature that MySQL doesn't really support yet? Is MySQL totally ignoring the REFERENCES part of the schedule_id field from my registration table above. I've determined that I'm using InnoDB so why isn't it working? Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL ignores foreign key relationships between tables?
I have two tables, registration schedules, that look like this: CREATE TABLE registration ( idSERIAL NOT NULL UNIQUE, firstnameVARCHAR(256) NOT NULL, middlenameTEXT, lastnameVARCHAR(256), suffix TEXT, schedule_id INTEGER REFERENCES schedules(id), ); CREATE TABLE schedules ( id SERIAL NOT NULL UNIQUE, start_date DATE NOT NULL, end_date DATE NOT NULL, ); The registration table above references the the schedules table via the schedule_id. Why does MySQL allow a row created in the schedules table be DELETED if it has a matching schedule_id in the registration table. These two tables share a relationship based on registration.schedule_id schedules.id. I've tried this same syntax in PostgreSQL and it doesn't allow the schedules.id record to be deleted without first removing any records in the registration table which carry a matching schedule_id record. Isn't that the point of a relational database?- TO CHECK RELATIONSHIPS between tables and enforce that those relationships aren't broken? I find it disappointing that MySQL ignores this relationship. Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL ignores foreign key relationships between tables?
Paul DuBois wrote: At 17:56 -0500 1/28/06, Ferindo Middleton Jr wrote: I have two tables, registration schedules, that look like this: CREATE TABLE registration ( idSERIAL NOT NULL UNIQUE, firstnameVARCHAR(256) NOT NULL, middlenameTEXT, lastnameVARCHAR(256), suffix TEXT, schedule_id INTEGER REFERENCES schedules(id), ); CREATE TABLE schedules ( id SERIAL NOT NULL UNIQUE, start_date DATE NOT NULL, end_date DATE NOT NULL, ); The registration table above references the the schedules table via the schedule_id. Why does MySQL allow a row created in the schedules table be DELETED if it has a matching schedule_id in the registration table. These two tables share a relationship based on registration.schedule_id schedules.id. I've tried this same syntax in PostgreSQL and it doesn't allow the schedules.id record to be deleted without first removing any records in the registration table which carry a matching schedule_id record. Isn't that the point of a relational database?- TO CHECK RELATIONSHIPS between tables and enforce that those relationships aren't broken? I find it disappointing that MySQL ignores this relationship. Add ENGINE = InnoDB to the end of your table definitions. Foreign keys are supported only for InnoDB tables in MySQL. I am using InnoDB. I use MySQL Administrator and InnoDB is what it says all my tables are already using so it must have chosen that by default or something. Does this mean that I shouldn't have been able to delete records from my schedules table above that had a foreign key in the registration table? Thanks. Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL ignores foreign key relationships between tables?
Ferindo Middleton Jr wrote: Paul DuBois wrote: At 17:56 -0500 1/28/06, Ferindo Middleton Jr wrote: I have two tables, registration schedules, that look like this: CREATE TABLE registration ( idSERIAL NOT NULL UNIQUE, firstnameVARCHAR(256) NOT NULL, middlenameTEXT, lastnameVARCHAR(256), suffix TEXT, schedule_id INTEGER REFERENCES schedules(id), ); CREATE TABLE schedules ( id SERIAL NOT NULL UNIQUE, start_date DATE NOT NULL, end_date DATE NOT NULL, ); The registration table above references the the schedules table via the schedule_id. Why does MySQL allow a row created in the schedules table be DELETED if it has a matching schedule_id in the registration table. These two tables share a relationship based on registration.schedule_id schedules.id. I've tried this same syntax in PostgreSQL and it doesn't allow the schedules.id record to be deleted without first removing any records in the registration table which carry a matching schedule_id record. Isn't that the point of a relational database?- TO CHECK RELATIONSHIPS between tables and enforce that those relationships aren't broken? I find it disappointing that MySQL ignores this relationship. Add ENGINE = InnoDB to the end of your table definitions. Foreign keys are supported only for InnoDB tables in MySQL. I am using InnoDB. I use MySQL Administrator and InnoDB is what it says all my tables are already using so it must have chosen that by default or something. Does this mean that I shouldn't have been able to delete records from my schedules table above that had a foreign key in the registration table? Thanks. Ferindo Paul, I discovered that this foreign key constraint wasn't present in these tables anymore due to my own action. You see, I had backed up my database before using MySQL Administrator, not knowing that is was backing up such tables constructs as foreign keys, etc. So the database I'm looking at today isn't the same database I originally created with the same constraints... I'm going to stop using MySQL Administrator... using it seems somewhat misleading and it made me think that the tables sand the constraints I made on them were still present. Thanks. Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can I change the password of a user on the server without interupting the user's existing connections?
I have three(3) instances of an application inserting data into a MYSQL (4.X) database. I have to change the password. I would like to know, if I can change the password of the application on the server without disrupting existing connections then modify the password in the startup file for the application. So that when the application is restarted automatically at night, the application will login to the database with the new password with a minimal disruption of service. Thank you, Raymond
problem with using CONSTRAINT declaration
I have the following table where I have a CHECK CONSTRAINT to check for logical data values but for some reason it's not working on INSERTs to the table. MySQL doesn't give any error message when I CREATE TABLE. Any ideas what I'm doing wrong?... or Is this type of declaration not supported... What command can you issue from the command line to check the existence of CONTRAINT declarations such as this? CREATE TABLE schedules ( id SERIAL NOT NULL UNIQUE, start_date DATE NOT NULL, end_date DATE NOT NULL, start_time TIME, end_time TIME, CONSTRAINT end_date_cannot_be_before_start_date CHECK (end_date = start_date), CONSTRAINT end_time_cannot_be_before_start_time CHECK (end_time = start_time), PRIMARY KEY (class_id, start_date, end_date, start_time, end_time) ); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SETting values to TABLE field at TRIGGER runtime
Gleb Paharenko wrote: Hello. It seems that you forgot to OPEN the cursor. The trigger should be similar to this one: CREATE TRIGGER trigger_registration_and_attendance_before_insert BEFORE INSERT ON registration_and_attendance FOR EACH ROW BEGIN DECLARE schedule_class_id INT; DECLARE schedule_class_id_cursor CURSOR FOR SELECT class_id FROM schedules WHERE schedules.id = new.schedule_id; OPEN schedule_class_id_cursor; FETCH schedule_class_id_cursor INTO schedule_class_id; SET new.class_id = schedule_class_id; CLOSE schedule_class_id_cursor ; END; Ferindo Middleton Jr wrote: Is it possible to SET values on fields that involve the TABLE that invoked the TRIGGER with SET actions. I have the following lines in my trigger: delimiter // CREATE TRIGGER trigger_registration_and_attendance_before_insert BEFORE INSERT ON registration_and_attendance FOR EACH ROW BEGIN DECLARE schedule_class_id INT; DECLARE schedule_class_id_cursor CURSOR FOR SELECT class_id FROM schedules WHERE schedules.id = new.schedule_id; FETCH schedule_class_id_cursor INTO schedule_class_id; SET new.class_id = schedule_class_id; END; The server accepts this but new.class_id doesn't get a value when I do an INSERT. Why won't this work? Ferindo Hi, I tried the code above, opening and the cursor before assigning the value called from the declaration into new.class_id but it still doesn't work. The class_id field isn't picking up the value it should from my schedules table. I can't figure out why - frustrating this. Thanks. Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SETting values to TABLE field at TRIGGER runtime - FLAW IN MYSQL TRIGGER IMPLEMENTATION?
Ferindo Middleton Jr wrote: Gleb Paharenko wrote: Hello. It seems that you forgot to OPEN the cursor. The trigger should be similar to this one: CREATE TRIGGER trigger_registration_and_attendance_before_insert BEFORE INSERT ON registration_and_attendance FOR EACH ROW BEGIN DECLARE schedule_class_id INT; DECLARE schedule_class_id_cursor CURSOR FOR SELECT class_id FROM schedules WHERE schedules.id = new.schedule_id; OPEN schedule_class_id_cursor; FETCH schedule_class_id_cursor INTO schedule_class_id; SET new.class_id = schedule_class_id; CLOSE schedule_class_id_cursor ; END; Ferindo Middleton Jr wrote: Is it possible to SET values on fields that involve the TABLE that invoked the TRIGGER with SET actions. I have the following lines in my trigger: delimiter // CREATE TRIGGER trigger_registration_and_attendance_before_insert BEFORE INSERT ON registration_and_attendance FOR EACH ROW BEGIN DECLARE schedule_class_id INT; DECLARE schedule_class_id_cursor CURSOR FOR SELECT class_id FROM schedules WHERE schedules.id = new.schedule_id; FETCH schedule_class_id_cursor INTO schedule_class_id; SET new.class_id = schedule_class_id; END; The server accepts this but new.class_id doesn't get a value when I do an INSERT. Why won't this work? Ferindo Hi, I tried the code above, opening and the cursor before assigning the value called from the declaration into new.class_id but it still doesn't work. The class_id field isn't picking up the value it should from my schedules table. I can't figure out why - frustrating this. Thanks. Ferindo Hello again Gleb, After further testing I have determined that my statement above is incorrect... The trigger above is being evaluated... well SORT OF. The class_id field is part of the primary key of registration_attendance table. With the application I developed to load data into these tables, if I do something like force an arbitrary value for the class_id field within the application, the system works on INSERTs and the trigger appears to be executed -overwriting- whatever value I manually hard coded into the application to be passed to the class_id field. As you can see this trigger is supposed to happen BEFORE INSERT but it appears data from my application is being evaluated into the table before the trigger fires. I guess I could just force an arbitrary value on the field as a workaround but isn't this a flaw. Shouldn't the trigger be executed before the database evaluates data against the table? I've used a trigger similar to this in Postgresql and the Postgresql db wouldn't introduce the data to the table until after the trigger executes which is how it should be. This appears to be a flaw in the MySQL implementation of TRIGGER implementation BEFORE INSERT. It appears that in MySQL, what it may be doing is: 1. Evaluate the data against the table although not committing the INSERT data 2. Execute the BEFORE INSERT TRIGGER 3. Then actually INSERT the data - When I should Perform Step 2 from above, Executing the Trigger before beginning any evaluation of the data into the database table. Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SETting values to TABLE field at TRIGGER runtime
Is it possible to SET values on fields that involve the TABLE that invoked the TRIGGER with SET actions. I have the following lines in my trigger: delimiter // CREATE TRIGGER trigger_registration_and_attendance_before_insert BEFORE INSERT ON registration_and_attendance FOR EACH ROW BEGIN DECLARE schedule_class_id INT; DECLARE schedule_class_id_cursor CURSOR FOR SELECT class_id FROM schedules WHERE schedules.id = new.schedule_id; FETCH schedule_class_id_cursor INTO schedule_class_id; SET new.class_id = schedule_class_id; END; The server accepts this but new.class_id doesn't get a value when I do an INSERT. Why won't this work? Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem with TRIGGER, unresponsive
I have these two tables: 'registration_and attendance' and 'schedules' They both share a common class_id field. I'm trying to write a Trigger which will set the class_id field for 'registration_and attendance' equal to the schedules.class_id matching the registration_and_attendance.schedule_id The below trigger doesn't return any error message when I try to load it into my db but the end result should be a value that registration_and_attendance table picks up for the class_id matching the foreign key, schedule_id, the two tables share. However, nothing happens. there is no value in 'new.class_id' on INSERTs to registration_and_attendance. delimiter // CREATE TRIGGER trigger_registration_and_attendance_before_insert BEFORE INSERT ON registration_and_attendance FOR EACH ROW BEGIN DECLARE schedule_class_id, b INT; DECLARE schedule_class_id_cursor CURSOR FOR SELECT class_id FROM schedules WHERE schedules.id = new.schedule_id; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN schedule_class_id_cursor; REPEAT FETCH schedule_class_id_cursor INTO schedule_class_id; UNTIL b = 1/* I wonder if this loop is even necessary because schedule_class_id_cursor should only return one value anyway */ END REPEAT; CLOSE schedule_class_id_cursor; SET new.class_id = schedule_class_id; END; // -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
parse error creating table
I have been trying to create a table but mysql 5.0.15-nt-max is having a problem parsing the statement. Anyone know what the problem is in the syntax of the following table creation statement: CREATE TABLE registration_and_attendance ( idSERIAL NOT NULL UNIQUE, firstnameVARCHAR(256) NOT NULL, middlenameTEXT, lastnameVARCHAR(256), suffix TEXT, sf182_receivedBOOLEAN NOT NULL DEFAULT TRUE, registrant_email_address TEXT, cc_email_list TEXT, bureau_id INTEGER REFERENCES bureaus(id), office TEXT, class_id INTEGER NOT NULL REFERENCES classes(id), schedule_id INTEGER REFERENCES schedules(id), start_date DATE, end_date DATE, enrolled BOOLEAN, attendedBOOLEAN, completed BOOLEAN, cancelledBOOLEAN DEFAULT FALSE, cancelled_commentsTEXT, comments TEXT, email_confirmation_sent BOOLEAN NOT NULL, employment_status_id INTEGER REFERENCES employment_statuses(id) NOT NULL, last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, last_user_updated TEXT, waitlisted BOOLEAN DEFAULT FALSE, overflow_registrantBOOLEAN DEFAULT FALSE, attach_hotel_listing_directions BOOLEAN, instructor_legacy TEXT, time_legacy TIME WITHOUT TIME ZONE, ssn_legacy TEXT, position_grade_title TEXT, office_phone_legacy TEXT, contractor_legacy BOOLEAN, no_show_legacy BOOLEAN, status_legacy TEXT, funding_id INTEGER REFERENCES funding_types(id), PRIMARY KEY (firstname, lastname, class_id, start_date, end_date) ); I get the following error message with the above statement but I can't figure out what the problem is: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT N ULL, last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, l' at line 23 Thanks, Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: parse error creating table
Thanks Peter. I did originally use this table in a Postgresql db. Thanks for you advice. Your suggestions below allowed me to create this table and I learned a thing t two about proper usage of the TIMESTAMP data type. The intended effect is to get a timestamp field that inserts the current system time on inserts and continues to update the field with the current timestamp on updates without the application or use needing to specify it are you saying that the timestamp attribute alone will do that? Ferindo Peter Brawley wrote: Ferindo One problem is: employment_status_id INTEGER REFERENCES employment_statuses(id) NOT NULL, NOT NULL should be before REFERENCES. Also, in: last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, (i) NOT NULL is superfluous since the default is given by CURRENT_TIMESTAMP. (ii) specifying DEFAULT CURRENT_TIMESTAMP defeats auto-resetting of the timestamp on updates. Is that what you want? To get auto-setting on INSERTs and UPDATEs, just write last_updated TIMESTAMP, Also the manual doesn't mention TIME WITHOUT TIME ZONE. Are you thinking of PostgreSQL? PB - Ferindo Middleton Jr wrote: I have been trying to create a table but mysql 5.0.15-nt-max is having a problem parsing the statement. Anyone know what the problem is in the syntax of the following table creation statement: CREATE TABLE registration_and_attendance ( idSERIAL NOT NULL UNIQUE, firstnameVARCHAR(256) NOT NULL, middlenameTEXT, lastnameVARCHAR(256), suffix TEXT, sf182_receivedBOOLEAN NOT NULL DEFAULT TRUE, registrant_email_address TEXT, cc_email_list TEXT, bureau_id INTEGER REFERENCES bureaus(id), office TEXT, class_id INTEGER NOT NULL REFERENCES classes(id), schedule_id INTEGER REFERENCES schedules(id), start_date DATE, end_date DATE, enrolled BOOLEAN, attendedBOOLEAN, completed BOOLEAN, cancelledBOOLEAN DEFAULT FALSE, cancelled_commentsTEXT, comments TEXT, email_confirmation_sent BOOLEAN NOT NULL, employment_status_id INTEGER REFERENCES employment_statuses(id) NOT NULL, last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, last_user_updated TEXT, waitlisted BOOLEAN DEFAULT FALSE, overflow_registrantBOOLEAN DEFAULT FALSE, attach_hotel_listing_directions BOOLEAN, instructor_legacy TEXT, time_legacy TIME WITHOUT TIME ZONE, ssn_legacy TEXT, position_grade_title TEXT, office_phone_legacy TEXT, contractor_legacy BOOLEAN, no_show_legacy BOOLEAN, status_legacy TEXT, funding_id INTEGER REFERENCES funding_types(id), PRIMARY KEY (firstname, lastname, class_id, start_date, end_date) ); I get the following error message with the above statement but I can't figure out what the problem is: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT N ULL, last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, l' at line 23 Thanks, Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UNIQUE constraint, proper use
I have this SQL statement: CREATE TABLE rooms ( idSERIAL, room_name TEXT UNIQUE, location TEXT, last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, last_user_updatedTEXT, PRIMARY KEY(id) ); When I run this I get the following error: ERROR 1170 (42000): BLOB/TEXT column 'room_name' used in key specification without a key length I'm more used to PostgreSQL and when I run this command there, I don't get this error message. I recognized that by changing room_name to a varchar type, I won't get the error message. Also, the PostgreSQL mailing lists had a special group from SQL-related issue, but I didn't see one of these types of lists in the lists of groups for MySQL community so I'm sorry if I irritate anyone by posting to the wrong group. Thanks. Ferindo Middleton -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQLHotCopy Error
We're having a problem with MySQLHotCopy. It has worked flawlessly in the
past but now we're getting an error message similar to this:
Dumping database... DBD::mysql::db do failed: Can't find file:
'./file.frm' (errno: 24) at /usr/local/mysql/bin/mysqlhotcopy line 468.
Deleting previous 'old' hotcopy directory ('mydirectory')
Existing hotcopy directory renamed to '/mydirectory/db_name_old'
done.
The frm file it can't find varies with each attempt to use MySQLHotCopy.
The files are there. We've flushed, optimized, and repaired all the tables
in the database without any luck. The db appears to be working fine.
MySQLHotCopy works ok with other databases on this server.
MySQL 4.0.23
Server: Mac OSX Server 10.3.8 dual 1.33 Ghz PPC G4 with 2 GB SDRAM
DB has 274 tables with 1.6 million records. DB size is 400 MB.
Any insights would be greatly appreciated.
TIA
Stephen P. Fracek, Jr.
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
syntax errors with ENGINE=MYISAM... and NOT NULL default CURRENT_TIMESTAMP ...
running: mysql Ver 12.22 Distrib 4.0.23, for Win95/Win98(i32) downloaded from www.devside.net/web/servers/free/download. When I run a script with the following commands I get sql syntax errors: Create table 'test' ( 'helper' varchar(22) ) ENGINE=MYISAM DEFAULT CHARSET=latin1; I get a sql error telling me to check the syntax for this version. I modified the line to ENGINE=MYISAM CHARSET=latin1; no sql errors. Next snytax error I get is: Create table 'testime' ( 'teatime' timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, 'val' char(22) NOT Null); I modified the line to Create table 'testime' ( 'teatime' timestamp NOT NULL default 'CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP', 'val' char(22) NOT Null); no sql errors. Question 1: How do I check my syntax against the version and distribution that I am running? Question 2: I thought on update was a reference definition and required a reference to a parent table and I did think CURRENT_STAMP was a reference action? Thank you, Raymond
Does such a JOIN exist that can create a pivot table?
Question: I frequently would like to summarize the results of my query in
heiarchical layout also
known as a Pivot table.
Here is an example of what I would like output. NULL will be printed as a space
when output.
sum of broken| source of | qty |reseller of |qty |customer with |qty
by
bolts |broken bolts|regional |broken bolts|reseller |broken bolts
|customer
-
100 | NULL |NULL |NULL| NULL|NULL |NULL
(100/NULL) | US| 75 |NULL| NULL|NULL |NULL
(100/NULL) | (US/NULL) |(75/NULL) |ACME| 35 |NULL |NULL
(100/NULL) | (US/NULL) |(75/NULL) |(ACME/NULL) |(35/NULL)|Barney Rubble |20
(100/NULL) | US| 75 |NULL| NULL|NULL |NULL
(100/NULL) | (US/NULL) |(75/NULL) |ACME| 35 |NULL |NULL
(100/NULL) | (US/NULL) |(75/NULL) |(ACME/NULL) |(35/NULL)|Betty Rubble |5
(100/NULL) | US| 75 |NULL| NULL|NULL |NULL
(100/NULL) | (US/NULL) |(75/NULL) |ABLE| 25 |NULL |NULL
(100/NULL) | (US/NULL) |(75/NULL) |(ABLE/NULL) |(25/NULL)|Sponge Bobble |20
(100/NULL) | MEXICO| 15 |NULL| NULL|NULL |NULL
(100/NULL) | (MEX/NULL) |(15/NULL) |TIPPY | 12 |NULL |NULL
(100/NULL) | (MEX/NULL) |(15/NULL) |(ACME/NULL) |(12/NULL)|Juan Pepe |7
100 * * * * * *
* US 75 * * * *
* * * ACME 35 * *
* * * * * Barney Ruble 20
Where * represents NULL or a Primary Key.
How does one build a pivot table?
from tables such as:
factory_parts table
::{
part no,
plant,
qty_manufactured
plant name
}
reseller_parts table
::{
part no
plant
qty received
cost
reseller name
reseller id
}
customer_parts table
::{
reseller id
part no
plant
qty sold
qty recvd
customer id
customer name
}
Ooops now the light bulb comes on
I would do:
select factory_parts.plant name,
reseller_parts.reseller_name
customer_parts.customer_name,
customer_parts.qty_recvd
from factory_parts,reseller_parts,customer_parts
where customer_parts.part_no == 'broken_bolt' AND
( customer_parts.part_no == reseller_parts.part.no AND
customer_parts.part_no == factory_parts.part.no )
Now the question becomes how does one construct the aggregate columns
representing the sum of bolts produced by the company,made at the plant,
shipped to the reseller and sold to the customer,
then join those aggregate columns? Any suggestions?
Thank you,
Raymond
Thank you-regarding: Does such a JOIN exist that can create a pivot table?
Does such a JOIN exist that can create a pivot table? Thank you: 182361 by: Dan Bolser 182362 by: Peter Brawley Now I must go into my cave and meditate on these queries: Ommm,Ommm,...Ommm :-) raymond
Merge tables,trigger support, and table rotation
How does one insert records based on some kind of meta data or key in
particular table belonging to a merge table?
I have a network logging program and would like to partition the table so that
analysts can query certain tables belonging
to the merge table instead of the whold table to corelate events.
/*merge table section of email */
Based on my limited research I thought that I would create a merged table such
as: /*from the web site */
mysql CREATE TABLE t1 (
-a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-message CHAR(20));
mysql CREATE TABLE t2 (
-a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-message CHAR(20));
mysql INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
mysql INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');
mysql CREATE TABLE total (
-a INT NOT NULL AUTO_INCREMENT,
-message CHAR(20), INDEX(a))
-TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;
/* trigger section of email */
Question(s): I will assume of that when 5.02 becomes stable and triggers are
available
then
mysql CREATE TRIGGER ins_sum INSERT ON total
IF message='happy ending' Then
INSERT INTO t1 (message) VALUES ('goldie locks'),('mama
bear'),('eat her up')
ELSE INSERT INTO t2 (message) VALUES ('KILLED Mama bear'),('papa
bear and baby bear'),
('in cirus');
Will triggers support insert, update, and delete verbs(actions) on a merged
table?
/* table rotation */
Second question: As tables grow can can a new table be created and
automatically added to the
merged table, at or on a particular date or time or when the size of the table
reaches a
certain limit?
Which leads to the third question: if myisam tables can be created dynamically
based
on size of table or time of day, can the oldest table be compressed with
myisampack
automatically?
Thank you,
Raymond
Is their still any reason why Stored Procedure calls are not supported in MySql?
Firstly, I do appologize for my ignorance in advance. I read the message regarding PRODUCT() and thought to myself:A perl interpreter can be run in a C program. Some one must have written a userdefined function that can execute perl code. Googling I found http://software.tangent.org/download/myperl-0.8.tar.gz at http://tangent.org. Looking at the readme file myperl is mysql userdefined function that executes perl code contained in a table in the select statement. The command line version of mysql also has the ability to start an editor. Theoretically, one could: 1. create a table: Create table user.sparky.functions ( name TEXT NOT NULL , code TEXT NOT NULL ); 2. \e myfunc.mypl 3. LOAD DATA IN FILE myfunc.mypl INTO TABLE user.sparky.functions; 4. /* assume no errors */ Select myfunc(code, colum_data) from user.sparky.functions, data_table where (code = myperl_code ) and ( column_data = what I am looking for ); If and I stress if my assumptions are valid, then stored procedure calls could be written in any interpreted language with an interpreter that can be linked into mysql. Of course from a security stand point this could be dangerous but chrooted brain dead interperter with limited functionality and limits on the amount of disk space and memory that can be used should solve those problems. One interesting consequence of the development of infrastructure to support the development of stored procedure calls is that IDE developers that support mysql would have new market to potentially exploit in a corporate enviroment i.e. run code and get metrics such as how long it to process data i.e. Hi,Lo, Avg. In conclusion, I appologize for my ignorance again however I must still ask: Is their still any reason why Stored Procedure calls are not supported in MySql? Thank you, Raymond -- Date: Mon, 30 Aug 2004 11:29:35 -0400 To: Thomas Schager [EMAIL PROTECTED], [EMAIL PROTECTED] From: Sapenov [EMAIL PROTECTED] Subject: Re: PRODUCT() function - calculating the product of grouped numeric values Message-ID: [EMAIL PROTECTED] Hello Thomas, You probably may consider to write a UDF to do that. Here is a list of available UDF extensions for 4.0.20 - http://mysql-udf.sourceforge.net/ Regards, Khazret Sapenov - http://tangent.org myperl (default) 0.8 2004-01-12 07:58:51 About myperl allows you to execute Perl from inside of MySQL. It can either be stored in a row, or it can be specified in your SQL statement. You also can select other columns which will be passed to Perl. Changes * Added support for the perl interpreter to persist for requests * re-aranged package to meet CPAN spec. * Fix for myperlgroup (it was calling itself too frequently) TAR/GZ http://software.tangent.org/download/myperl-0.8.tar.gz -- Date: Mon, 30 Aug 2004 09:48:55 -0400 To: Per Lonnborg [EMAIL PROTECTED] From: Michael Stassen [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: SELECT, ORDER one DISTINCT kolumn? Message-ID: [EMAIL PROTECTED] Per Lonnborg wrote: Hi, Here´s a question from a newbie: I have a database containing ET-times for drivers and their cars. On the web startpage (http://www.lanechoice.net/~pelle/br/) i have made a Top-10 list containing the 10 fastest times. I'll guess column names based on the web page. I have 2 problems: 1. How to select just ONE (the fastest of course) time per uniqe drivercar? SELECT namm, fabrikat, MIN(tid) AS fastest_tid FROM ettan GROUP BY namm, fabrikat ORDER BY fastest_tid DESC LIMIT 10; 2.IF the driver/car has run some race faster than 7,5 secs, he/she is disqualified and should not be on the Top-10 at all. SELECT namm, fabrikat, MIN(tid) AS fastest_tid FROM ettan GROUP BY namm, fabrikat HAVING fastest_tid = 7.5 ORDER BY fastest_tid LIMIT 10; The query I use right now is: Select * from ettan where tid =7.5 ORDER BY tid LIMIT 10 /Per Stockholm, Sweden Note that I only selected aggregate values and grouped columns. If you need the values of non-grouped columns (ort, datum, 60fot, hast) which correspond to the minimum times, you'll need to do a bit more. The manuaal describes 3 methods http://dev.mysql.com/doc/mysql/en/example-Maximum-column-group-row.html. ___ Skicka gratis SMS! http://www.passagen.se Michael -- -- End of mysql-plain Digest *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is their still any reason why Stored Procedure calls are not supported in MySql?
My Bad. I was all into 4.xx and since most of the www.mysql.com references 4.xx I never thought about 5.0. I appologize for disturbing the list. thank you, raymond -Original Message- From: V. M. Brasseur [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 31, 2004 18:54 To: Jacob, Raymond A Jr Cc: [EMAIL PROTECTED] Subject: Re: Is their still any reason why Stored Procedure calls are not supported in MySql? Stored procedures are in the works for MySQL 5.0: http://dev.mysql.com/doc/mysql/en/TODO_MySQL_5.0.html The manual is your friend. Cheers, --V Jacob, Raymond A Jr wrote: Firstly, I do appologize for my ignorance in advance. I read the message regarding PRODUCT() and thought to myself:A perl interpreter can be run in a C program. Some one must have written a userdefined function that can execute perl code. Googling I found http://software.tangent.org/download/myperl-0.8.tar.gz at http://tangent.org. Looking at the readme file myperl is mysql userdefined function that executes perl code contained in a table in the select statement. The command line version of mysql also has the ability to start an editor. Theoretically, one could: 1. create a table: Create table user.sparky.functions ( name TEXT NOT NULL , code TEXT NOT NULL ); 2. \e myfunc.mypl 3. LOAD DATA IN FILE myfunc.mypl INTO TABLE user.sparky.functions; 4. /* assume no errors */ Select myfunc(code, colum_data) from user.sparky.functions, data_table where (code = myperl_code ) and ( column_data = what I am looking for ); If and I stress if my assumptions are valid, then stored procedure calls could be written in any interpreted language with an interpreter that can be linked into mysql. Of course from a security stand point this could be dangerous but chrooted brain dead interperter with limited functionality and limits on the amount of disk space and memory that can be used should solve those problems. One interesting consequence of the development of infrastructure to support the development of stored procedure calls is that IDE developers that support mysql would have new market to potentially exploit in a corporate enviroment i.e. run code and get metrics such as how long it to process data i.e. Hi,Lo, Avg. In conclusion, I appologize for my ignorance again however I must still ask: Is their still any reason why Stored Procedure calls are not supported in MySql? Thank you, Raymond -- Date: Mon, 30 Aug 2004 11:29:35 -0400 To: Thomas Schager [EMAIL PROTECTED], [EMAIL PROTECTED] From: Sapenov [EMAIL PROTECTED] Subject: Re: PRODUCT() function - calculating the product of grouped numeric values Message-ID: [EMAIL PROTECTED] Hello Thomas, You probably may consider to write a UDF to do that. Here is a list of available UDF extensions for 4.0.20 - http://mysql-udf.sourceforge.net/ Regards, Khazret Sapenov - http://tangent.org myperl (default) 0.8 2004-01-12 07:58:51 About myperl allows you to execute Perl from inside of MySQL. It can either be stored in a row, or it can be specified in your SQL statement. You also can select other columns which will be passed to Perl. Changes * Added support for the perl interpreter to persist for requests * re-aranged package to meet CPAN spec. * Fix for myperlgroup (it was calling itself too frequently) TAR/GZ http://software.tangent.org/download/myperl-0.8.tar.gz -- Date: Mon, 30 Aug 2004 09:48:55 -0400 To: Per Lonnborg [EMAIL PROTECTED] From: Michael Stassen [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: SELECT, ORDER one DISTINCT kolumn? Message-ID: [EMAIL PROTECTED] Per Lonnborg wrote: Hi, Here´s a question from a newbie: I have a database containing ET-times for drivers and their cars. On the web startpage (http://www.lanechoice.net/~pelle/br/) i have made a Top-10 list containing the 10 fastest times. I'll guess column names based on the web page. I have 2 problems: 1. How to select just ONE (the fastest of course) time per uniqe drivercar? SELECT namm, fabrikat, MIN(tid) AS fastest_tid FROM ettan GROUP BY namm, fabrikat ORDER BY fastest_tid DESC LIMIT 10; 2.IF the driver/car has run some race faster than 7,5 secs, he/she is disqualified and should not be on the Top-10 at all. SELECT namm, fabrikat, MIN(tid) AS fastest_tid FROM ettan GROUP BY namm, fabrikat HAVING fastest_tid = 7.5 ORDER BY fastest_tid LIMIT 10; The query I use right now is: Select * from ettan where tid =7.5 ORDER BY tid LIMIT 10 /Per Stockholm, Sweden Note that I only selected aggregate values and grouped columns. If you need the values of non-grouped columns (ort, datum, 60fot, hast) which correspond to the minimum times, you'll need to do a bit more. The manuaal describes 3 methods http://dev.mysql.com/doc/mysql/en/example-Maximum
archive db daily- Rough Draft
-Original Message-
From: Jacob, Raymond A Jr
Sent: Monday, August 02, 2004 21:17
To: [EMAIL PROTECTED]
Subject: archive db daily- Rough Draft
I am new to Databases and would appreciate a second set of eyes looking over my sql
script.
The script takes the existing snort database and copies it to a database named
snort-2004-08-02. Then copies over a day's worth of information to the database
snort-2004-08-02. Once the database has been copied over, mysqlhotcopy is
used to copy the database to another directory. The database is then archived
and compressed to a file with a .tgz extension. After a week the database
is removed. I assume that I can find the age of the snort-2004-08-02 database
from mysql without checking a table in the snort-2004-08-02 database
When I determine that the database is over 7-days old I will drop the database?
thank you,
Raymond
---copySnort.sql---
#copySnort.sql copy snortdb database to snort--mm-dd
CREATE DATABASE snort-2004-08-02;
USE snort-2004-08-02;
# Not sure if this is even necessary Database may use my default information.
GRANT INSERT,SELECT on snort-2004-08-02.* to [EMAIL PROTECTED];
CREATE TABLE acid_event ( sid INT UNSIGNED NOT NULL,
cid INT UNSIGNED NOT NULL,
signature INT UNSIGNED NOT NULL,
sig_nameVARCHAR(255),
sig_class_idINT UNSIGNED,
sig_priorityINT UNSIGNED,
timestamp DATETIME NOT NULL,
ip_src INT UNSIGNED,
ip_dst INT UNSIGNED,
ip_protoINT,
layer4_sportINT UNSIGNED,
layer4_dportINT UNSIGNED,
PRIMARY KEY (sid,cid),
INDEX (signature),
INDEX (sig_name),
INDEX (sig_class_id),
INDEX (sig_priority),
INDEX (timestamp),
INDEX (ip_src),
INDEX (ip_dst),
INDEX (ip_proto),
INDEX (layer4_sport),
INDEX (layer4_dport)
);
INSERT snort-2004-08-02.acid_event.*
SELECT snort.acid_event.*
FROM snort.acid_event, snort.event
WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and
2004-08-01
AND (snort.event.cid = snort.acid_event.cid);
CREATE TABLE acid_ag ( ag_id INT UNSIGNED NOT NULL
AUTO_INCREMENT,
ag_name VARCHAR(40),
ag_desc TEXT,
ag_ctimeDATETIME,
ag_ltimeDATETIME,
PRIMARY KEY (ag_id),
INDEX (ag_id));
INSERT snort-2004-08-02.acid_ag.*
SELECT snort.acid_ag.*, snort.acid_ag_alert.ag_id, snort.acid_ag_alert.ag_cid
FROM snort.acid_ag, snort.event
WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and
'2004-08-01'
AND (snort.event.cid = snort.acid_ag.cid);
CREATE TABLE acid_ag_alert( ag_id INT UNSIGNED NOT NULL,
ag_sid INT UNSIGNED NOT NULL,
ag_cid INT UNSIGNED NOT NULL,
PRIMARY KEY (ag_id, ag_sid, ag_cid),
INDEX (ag_id),
INDEX (ag_sid, ag_cid));
INSERT snort-2004-08-02.acid_ag_alert.*
SELECT snort.acid_ag_alert.*
FROM snort.acid_event, snort.event
WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and
2004-08-01)
AND (snort.event.cid = snort.acid_ag.cid);
CREATE TABLE acid_ip_cache( ipc_ip INT UNSIGNED NOT NULL,
ipc_fqdnVARCHAR(50),
ipc_dns_timestamp DATETIME,
ipc_whois TEXT,
ipc_whois_timestamp DATETIME,
PRIMARY KEY (ipc_ip),
INDEX (ipc_ip) );
INSERT snort-2004-08-02.acid_ip_cache.*
SELECT snort.acid_ip_cache.*
FROM snort.acid_event, snort.event
WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and
2004-08-01
RE: How do you archive db daily?
Shawn: Thank you for your reply. I did not know you could reference a Database in a select clause, I thought you could only reference tables. I do have a followup question. I assume once I have created the dbArchYesterday that mysqld has the database in memory.Is there way to: 1. write the database to disk, 2. unload the database from memory, if it is in memory, in order to file utilities such as tar and gzip to archive and compress the database into to a tgz file? Thank you, raymond PS: Is there a document that describes how mysql loads and unloads tables and databases in memory? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, July 30, 2004 9:03 To: Jacob, Raymond A Jr Cc: [EMAIL PROTECTED] Subject: Re: How do you archive db daily? Why not keep your server running and just use scripted SQL to migrate the records from one DB to the other? CREATE DATABASE dbArchYesterday; USE dbArchYesterday; CREATE TABLE table1 like dbActive.table1; CREATE TABLE table2 like dbActive.table2; CREATE TABLE table3 like dbActive.table3; CREATE TABLE table4 like dbActive.table4; INSERT table1(list of columns to load) SELECT list of columns to read FROM dbActive.table1 WHERE alertdate between 'yesterday 00:00:00' and 'yesterday 23:59:59'; INSERT table2(list of columns to load) SELECT list of columns to read FROM dbActive.table2 WHERE alertdate between 'yesterday 00:00:00' and 'yesterday 23:59:59'; INSERT table3(list of columns to load) SELECT list of columns to read FROM dbActive.table3 WHERE alertdate between 'yesterday 00:00:00' and 'yesterday 23:59:59'; INSERT table4(list of columns to load) SELECT list of columns to read FROM dbActive.table4 WHERE alertdate between 'yesterday 00:00:00' and 'yesterday 23:59:59'; DELETE FROM dbActive.Table1 WHERE alertdate between 'yesterday 00:00:00' and 'yesterday 23:59:59'; DELETE FROM dbActive.Table2 WHERE alertdate between 'yesterday 00:00:00' and 'yesterday 23:59:59'; DELETE FROM dbActive.Table3 WHERE alertdate between 'yesterday 00:00:00' and 'yesterday 23:59:59'; DELETE FROM dbActive.Table4 WHERE alertdate between 'yesterday 00:00:00' and 'yesterday 23:59:59'; That way you never have to stop your server and you can transform any data you need to change during the move. This process also allows you to build additional summary tables during the move, if you want them. By scripting the entire thing and replacing my (hopefully) obvious placeholders with live names and dates, this should perform rather well with little or no intervention on your part. Another advantage to scripting this process is that you can monitor each stage and abort the deletes at the end if things did not go well. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jacob, Raymond A Jr [EMAIL PROTECTED] wrote on 07/29/2004 06:26:26 PM: I am running MySql 3.23.58-1 on a snort database containing IDS alerts. At 12:00am I would like move the previous day's alerts from four tables to a backup database named for the previous day. Has anyone implemented such a backup schedule? And if so can someone send me a script? I had the following ideas on the subject: 1.a. mysql shutdown. b. cp -r database //2004-07-29 c. mysqlstart /* I need a single user mode for the delete to work */ d. echo Delete iphdr; Delete tcphdr;Delete acid_event; Delete event | mysql -p xxx -u e. go to multiuser mode. 2. a. Assuming logging turned on mysqlhotcopy snortdb ( echo Delete iphdr; Delete tcphdr;Delete acid_event; Delete event ; mysqlbinlog snort.log ) | mysql -p xxx -u yyy 3. a. $ mysql -p xxx -u yyy 1. if a week then purge tables: $mysql Delete iphdr; ( repeat for the rest of the tables.) 2. mysql -p xxx -u yyy mysql Select iphdr.* from iphdr, event_id into outfile /.../backup/2004-07-29/iphdr.txt where timestamp.event_id 2004-07-29; mysql Delete iphdr; ( repeat for the rest of the tables.) mysql use backsnort_db Select iphdr.* from iphdr, event_id Load infile /.../backup/2004-07-29/iphdr.txt ( repeat for the rest of the tables.) mysql exit b. tar cvf backsnort_db That is my best shot if anyone has a more elegant solution I would appreciate hearing about it. Thank you, Raymond
How do you archive db daily?
I am running MySql 3.23.58-1 on a snort database containing IDS alerts. At 12:00am I would like move the previous day's alerts from four tables to a backup database named for the previous day. Has anyone implemented such a backup schedule? And if so can someone send me a script? I had the following ideas on the subject: 1.a. mysql shutdown. b. cp -r database //2004-07-29 c. mysqlstart /* I need a single user mode for the delete to work */ d. echo Delete iphdr; Delete tcphdr;Delete acid_event; Delete event | mysql -p xxx -u e. go to multiuser mode. 2. a. Assuming logging turned on mysqlhotcopy snortdb ( echo Delete iphdr; Delete tcphdr;Delete acid_event; Delete event ; mysqlbinlog snort.log ) | mysql -p xxx -u yyy 3. a. $ mysql -p xxx -u yyy 1. if a week then purge tables: $mysql Delete iphdr; ( repeat for the rest of the tables.) 2. mysql -p xxx -u yyy mysql Select iphdr.* from iphdr, event_id into outfile /.../backup/2004-07-29/iphdr.txt where timestamp.event_id 2004-07-29; mysql Delete iphdr; ( repeat for the rest of the tables.) mysql use backsnort_db Select iphdr.* from iphdr, event_id Load infile /.../backup/2004-07-29/iphdr.txt ( repeat for the rest of the tables.) mysql exit b. tar cvf backsnort_db That is my best shot if anyone has a more elegant solution I would appreciate hearing about it. Thank you, Raymond
help query analysis
I am trying to analyze a query that is taking forever. I am new to this so or rather desperate. I would assume that my query would be faster, if the event id (*.cid) Primary key were used to search for rows in the iphdr table with the same event id, but I don't think that is happening. Could some one tell me if my assumption is correct and how can I get the query to use the primary *.cid keys. It appears that the developer created a table that joined signature, iphdr, and event tables together to solve the problem of speed to create the Acid_event table. I would still like to know how can I improve my query? thank you, Raymond -Original Message- From: Raymond Jacob Sent: Monday, July 26, 2004 19:50 To: Jacob, Raymond A Jr Subject: query analysis version: MySQL-Max-3.23.58-1 DESC iphdr; DESC event; DESC signature; DESC acid_event EXPLAIN select count(ip_dst) as ip_dst_count, inet_ntoa(ip_dst) from iphdr, event , signature where ( event.timestamp now() - interval 24 hour ) and (event.cid = iphdr.cid and event.signature = signature.sig_id and signature.sig_sid = 1432 ) group by ip_dst order by ip_dst_count desc limit 100; Iphdr Field|Type|Null|Key|Default|Extra sid|int(10) unsigned||PRI|0| cid|int(10) unsigned||PRI|0| ip_src|int(10) unsigned||MUL|0| ip_dst|int(10) unsigned||MUL|0| ip_ver|tinyint(3) unsigned|YES||NULL| ip_hlen|tinyint(3) unsigned|YES||NULL| ip_tos|tinyint(3) unsigned|YES||NULL| ip_len|smallint(5) unsigned|YES||NULL| ip_id|smallint(5) unsigned|YES||NULL| ip_flags|tinyint(3) unsigned|YES||NULL| ip_off|smallint(5) unsigned|YES||NULL| ip_ttl|tinyint(3) unsigned|YES||NULL| ip_proto|tinyint(3) unsigned|||0| ip_csum|smallint(5) unsigned|YES||NULL| event: Field|Type|Null|Key|Default|Extra sid|int(10) unsigned||PRI|0| cid|int(10) unsigned||PRI|0| signature|int(10) unsigned||MUL|0| timestamp|datetime||MUL|-00-00 00:00:00| Signature: Field|Type|Null|Key|Default|Extra sig_id|int(10) unsigned||PRI|NULL|auto_increment sig_name|varchar(255)||MUL|| sig_class_id|int(10) unsigned||MUL|0| sig_priority|int(10) unsigned|YES||NULL| sig_rev|int(10) unsigned|YES||NULL| sig_sid|int(10) unsigned|YES||NULL| Acid_event: Field TypeNullKey Default Extra sid int(10) unsignedPRI 0 cid int(10) unsignedPRI 0 signature int(10) unsignedMUL 0 sig_namevarchar(255)YES MUL NULL sig_class_idint(10) unsignedYES MUL NULL sig_priorityint(10) unsignedYES MUL NULL timestamp datetimeMUL -00-00 00:00:00 ip_src int(10) unsignedYES MUL NULL ip_dst int(10) unsignedYES MUL NULL ip_protoint(11) YES MUL NULL layer4_sportint(10) unsignedYES MUL NULL layer4_dportint(10) unsignedYES MUL NULL Query Analysis table|type|possible_keys|key|key_len|ref|rows|Extra event|range|sig,time|time|8|NULL|39382|where used; Using temporary; Using filesort iphdr|ALL|NULL|NULL|NULL|NULL|375383|where used signature|eq_ref|PRIMARY|PRIMARY|4|event.signature|1|where used cartesian product= 14,783,333,306 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld process hanging -- can't restart
Hi, I'm having a problem every so often with the following error message: 040718 15:21:59 InnoDB: Started /usr/sbin/mysqld: ready for connections. Version: '4.0.16-standard' socket: '/var/lib/mysql/mysql.sock' port: 3306 Number of processes running now: 1 mysqld process hanging, pid 12141 - killed 040719 17:35:54 mysqld restarted 040719 17:35:54 Can't start server: Bind on TCP/IP port: Address already in use 040719 17:35:54 Do you already have another mysqld server running on port: 3306 ? 040719 17:35:54 Aborting 040719 17:35:54 /usr/sbin/mysqld: Shutdown Complete 040719 17:35:54 mysqld ended 040719 23:09:12 mysqld started As you can see, I didn't notice the server was down until a couple hours later. A lot of people have said I should check to see what process it running on port 3306 but I'm never there when this happens. In any case, it seems pretty clear to me that MySQL isn't all the way shutdown when it tries to start back up again. When I manually have to restart the server it usually takes 10-20 seconds depending on it's current load; the above error message only seems to give a fraction of a second for the server to shutdown. What I can I do to fix this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [OT] Email addresses shown in archive at lists.mysql.com
On Mon, Jul 12, 2004 at 05:28:04PM +0300, Heikki Tuuri wrote the following: Ian, note that the MySQL mailing list is also forwarded to the mailing.database.myodbc Usenet group, as well as to several mailing list archiving websites. Spam robots and viruses have plenty of places from which to harvest your email address if you write to this list. I personally receive some 2000 viruses and 300 spam emails per day to my email address. I'm subscribed to at least 2 dozen mailing lists (I'm active on about a 1/4 of them, the others are informational for programs or interests I have) and I don't get nearly that many viruses and spam, even in a week. -- AIM: pres CTHULHU | ICQ: 18115568 | Yahoo: pagan_prince Jabber: DarkKnightRadick@(jabber.org|amessage.at) | Libertarian @ Large PGP: 0x642F7BDA | http://groups.yahoo.com/group/tennesseans-for-badnarik/ http://mc-luug.homelinux.org/mailman/listinfo/mc-luug pgpwqh3VJzRap.pgp Description: PGP signature
Re: MySQL Website
On Wed, Apr 21, 2004 at 08:08:29AM -0400, Lehman, Jason (Registrar's Office) wrote the following: I should have been clearer. I can't reach the website. I can get to lists.mysql.com with no problem except for the fact that images won't pull form www.mysql.com but I definitely come to a grinding halt when I try to reach www.mysql.com. I can't do a tracert because the university has shut that off here. But I guess it is working for everyone else. www.trace-route.org ;) snip -- Joseph A. Nagy, Jr. http://joseph-a-nagy-jr.homelinux.org Political Activist Extraordinaire Peace, Life, Liberty The only fallacy is the inaction on our part to stave off the worst of horrors, the stripping of personal freedom. -- Joseph A. Nagy, Jr. January 2004 pgp0.pgp Description: PGP signature
Error when dumping DBs.
I am getting the following error when trying to dump my DBs. /usr/bin/mysqldump: Got error: 1103: Incorrect table name '/home/jr/backups/20040420/wcp.sql' when doing LOCK TABLES Version is: 4.0.18-standard These DBs where on another box running the same version. MySQL was shutdown on both the old and new box, the DB directory structure was copied to a new server. MySQL was restarted and everything works fine as far as I can tell except I can't dump the DBs. Any help would be appreciated. Thanks in advance! JR -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP + MySQL Not Playing Nice Any More
On Fri, Apr 16, 2004 at 12:07:39AM -0400, Mark Susol | Ultimate Creative Media wrote the following: On 4/15/04 11:46 PM, Joseph A. Nagy, Jr. [EMAIL PROTECTED] wrote: So I'm using PHP and MySQL to serve up a game and all is going well until today. The first problem came when for some reason the game was sending apache as the username to access the db (which is not what I have in the dbconnect file) and output some errors. I checked the page it was complaining about and all was good so I use phpmyadmin to login and admin my db but now even phpmyadmin won't let me in and I know I haven't touched the config file since I first set it up. Does MySQL do this very often or is it a PHP error and not a MySQL one and if this is a PHP error where do I look to fix it? This seems more like a change was made in your hosting environment, unrelated to php or mysql. I've seen this happen when sites were moved into safe moded environments. That's odd since I run my own server and I know I haven't made any such changes. -- Joseph A. Nagy, Jr. http://joseph-a-nagy-jr.homelinux.org Political Activist Extraordinaire Peace, Life, Liberty The only fallacy is the inaction on our part to stave off the worst of horrors, the stripping of personal freedom. -- Joseph A. Nagy, Jr. January 2004 pgp0.pgp Description: PGP signature
Re: PHP + MySQL Not Playing Nice Any More
The only thing that changed in the environment was the creation of a script that temporarily introduces a variable ($MP3) to be created and then accessed by the script in question but the script doesn't touch mysql at all. What could have changed so drastically as to kill access to the db so completely? -- Joseph A. Nagy, Jr. http://joseph-a-nagy-jr.homelinux.org Political Activist Extraordinaire Peace, Life, Liberty The only fallacy is the inaction on our part to stave off the worst of horrors, the stripping of personal freedom. -- Joseph A. Nagy, Jr. January 2004 pgp0.pgp Description: PGP signature
Re: PHP + MySQL Not Playing Nice Any More
Okay, I got phpmyadmin back but http://logd-test.joseph-a-nagy-jr.homelinux.org still is having problems (click the link to see the problem) and I've gone through and commented out the lines it refers to but then all I get is a blank page. So I copy a fresh, untouched copy of dbwrapper.php to / for the vhost and it still wants to try and use [EMAIL PROTECTED] instead of what is defined in my dbconnect file. I'm out of idea's and am no longer sure this is a mysql problem but I don't know where else to go. ): -- Joseph A. Nagy, Jr. http://joseph-a-nagy-jr.homelinux.org Political Activist Extraordinaire Peace, Life, Liberty The only fallacy is the inaction on our part to stave off the worst of horrors, the stripping of personal freedom. -- Joseph A. Nagy, Jr. January 2004 pgp0.pgp Description: PGP signature
Re: PHP + MySQL Not Playing Nice Any More[Solved]
snip The PHP page that was being reported at being in error wasn't the one in error. Sorry for wasting everyone's bandwidth and time. -- Joseph A. Nagy, Jr. http://joseph-a-nagy-jr.homelinux.org Political Activist Extraordinaire Peace, Life, Liberty The only fallacy is the inaction on our part to stave off the worst of horrors, the stripping of personal freedom. -- Joseph A. Nagy, Jr. January 2004 pgp0.pgp Description: PGP signature
PHP + MySQL Not Playing Nice Any More
So I'm using PHP and MySQL to serve up a game and all is going well until today. The first problem came when for some reason the game was sending apache as the username to access the db (which is not what I have in the dbconnect file) and output some errors. I checked the page it was complaining about and all was good so I use phpmyadmin to login and admin my db but now even phpmyadmin won't let me in and I know I haven't touched the config file since I first set it up. Does MySQL do this very often or is it a PHP error and not a MySQL one and if this is a PHP error where do I look to fix it? -- Joseph A. Nagy, Jr. http://joseph-a-nagy-jr.homelinux.org Political Activist Extraordinaire Peace, Life, Liberty The only fallacy is the inaction on our part to stave off the worst of horrors, the stripping of personal freedom. -- Joseph A. Nagy, Jr. January 2004 pgp0.pgp Description: PGP signature
Re: Set Password [SOLVED]
On Mon, Apr 12, 2004 at 11:19:50AM +0100, Russell Horn wrote the following: The man page says /usr/bin/mysqladmin -u user -p somepassword Not here it doesn't. My man page says: mysqladmin [-#|--debug= logfile] [-f|--force] [-?|--help] [--character-sets-dir=directory] [-C|--compress] [-h|--host=[#]] [-p[pwd]] [--password=[pwd]] [-P|--port= pnum] [-i|--sleep= sec] [-E|--vertical] [-s|--silent] [-S|--socket= #] [-r|--relative] [-t|--timeout= #] [-u|--user= uname][-v|--verbose][-V|--version] [-w|--wait[=retries]] Which means you would user either: /usr/bin/mysqladmin -uuser -psomepassword or /usr/bin/mysqladmin --user=user --pass=somepassword Both of which work from my command line. Neither of the above worked for me with the possible exception of the user field as I had not had any problems with it. That's okay though as I'm now having problems with the mysqlaccess command. -- Joseph A. Nagy, Jr. http://joseph-a-nagy-jr.homelinux.org Political Activist Extraordinaire Peace, Life, Liberty The only fallacy is the inaction on our part to stave off the worst of horrors, the stripping of personal freedom. -- Joseph A. Nagy, Jr. January 2004 pgp0.pgp Description: PGP signature
Set Password
Okay, I've been googling for about half an hour, found several useful links but I'm still having a problem with the password for the initial mysql db that is created when you follow the install instructions. Here is the problem I'm having: joseph-a-nagy-jr root # /usr/bin/mysqladmin -u root -h joseph-a-nagy-jr.homelinux.org password 'somepassword' /usr/bin/mysqladmin: connect to server at 'joseph-a-nagy-jr.homelinux.org' failed error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)' joseph-a-nagy-jr root # /usr/bin/mysqladmin -u [EMAIL PROTECTED] -h joseph-a-nagy-jr.homelinux.org password 'somepassword' /usr/bin/mysqladmin: unable to change password; error: 'Access denied for user: '@localhost' to database 'mysql'' joseph-a-nagy-jr root # when I do mysql -u root -p and it asks for me to enter the pwd, I enter somepassword and it allows me access to the mysql prompt. Do I need to set my router to forward the default mysql port to my box? I'm trying to set up a db driven game (which I posted about earlier) as well as create an ebuild for it and this is an essential part. I'm fixing to sub to the mysql mailing list and forward my problem there too as their archives show they've had similar problems but nothing exactly like mine and nothing (so far as I've looked on my own already) that would come close to being able to solve my problem. -- Joseph A. Nagy, Jr. http://joseph-a-nagy-jr.homelinux.org Political Activist Extraordinaire Peace, Life, Liberty The only fallacy is the inaction on our part to stave off the worst of horrors, the stripping of personal freedom. -- Joseph A. Nagy, Jr. January 2004 pgp0.pgp Description: PGP signature
Re: Set Password [SOLVED]
Problem solved. ): -- Joseph A. Nagy, Jr. http://joseph-a-nagy-jr.homelinux.org Political Activist Extraordinaire Peace, Life, Liberty The only fallacy is the inaction on our part to stave off the worst of horrors, the stripping of personal freedom. -- Joseph A. Nagy, Jr. January 2004 pgp0.pgp Description: PGP signature
Re: Set Password [SOLVED]
On Sun, Apr 11, 2004 at 06:34:28PM -0500, Joseph A. Nagy, Jr. wrote the following: Problem solved. ): snip Well, it wasn't a problem with mysql per sey but a problem with how I was entering the command. The man page says /usr/bin/mysqladmin -u user -p somepassword is correct as is /usr/bin/mysqladmin -u user --password(or pwd)=somepassword but the correct, undocumented way is /usr/bin/mysqladmin -u user -p password 'somepassword' The spaces are important. The mysql man page REALLY needs to be updated to reflect that the other methods DO NOT WORK. The way I had to do it is the only way it works, at least on my install. I'm running Gentoo Linux and the above way, according to what I've seen in the archives, seems to be the only real way to do it. -- Joseph A. Nagy, Jr. http://joseph-a-nagy-jr.homelinux.org Political Activist Extraordinaire Peace, Life, Liberty The only fallacy is the inaction on our part to stave off the worst of horrors, the stripping of personal freedom. -- Joseph A. Nagy, Jr. January 2004 pgp0.pgp Description: PGP signature
RE: ERROR 2002: Can't connect to local MySQL server through socket
You have to run the 'mysqld_safe ' command before you run 'mysql'. That creates the socket for you. J.R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Great QUERY question
This is for those who love a challenge. I am trying to come up with a query that would calculate the Standard Deviation and Variance for 15 fields. Although in theory this is easily done in Access, MySQL does not have the same mathematical calculations that Access/SQL does. Here is the query as it stands in Access: Select avg(Item01) as Item01a, stdev(Item01) as Item01d, avg(Item02) as Item02a, stdev(Item02) as Item02d, avg(Item03) as Item03a, stdev(Item03) as Item03d, avg(Item04) as Item04a, stdev(Item04) as Item04d, avg(Item05) as Item05a, stdev(Item05) as Item05d, avg(Item06) as Item06a, stdev(Item06) as Item06d, avg(Item07) as Item07a, stdev(Item07) as Item07d, avg(Item08) as Item08a, stdev(Item08) as Item08d, avg(Item09) as Item09a, stdev(Item09) as Item09d, avg(Item10) as Item10a, stdev(Item10) as Item10d, avg(Item11) as Item11a, stdev(Item11) as Item11d, avg(Item12) as Item12a, stdev(Item12) as Item12d, avg(Item13) as Item13a, stdev(Item13) as Item13d, avg(Item14) as Item14a, stdev(Item14) as Item14d, avg(Item15) as Item15a, stdev(Item15) as Item15d, avg(overallscore) as overa, stdev(overallscore) as overd from tblFacultyEvalSurgery This is for an online grading system that, except for 5 pages, has been converted over to MySQL and Linux / Apache. 2 of which have this problem. Avg() is easy, but it's the StDev that I can't get. St Dev is made from Variance (or the mean), which again is not a function of MySQL. To view the Variance and StDev formulae, http://davidmlane.com/hyperstat/A16252.html. If you have any ideas, I will be working on this for the next few days. Thanks! J.R.
RE: web hosting quesiong (slightly off topic)
We do not allow SSH access to our virtual hosts but we have dedicated servers starting at $49 per month. JR -- RHCE #808003122507415 MySQL #206067847 Ask Me About Top Notch Web Hosting Programming! -- Computers are like air conditioners: They stop working properly if you open windows. -Original Message- From: Chris W [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 8:10 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: web hosting quesiong (slightly off topic) I was wondering if anyone could recommend a good web hosting company for an Apache - php - MySQL project. I don't need much bandwidth or disk space to start out, but may need more if the site gets big. I would also like to have ssh access to the server, preferably a linux server. Chris W -- 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: Installing
Make all gives me this libmysql.c: In function `mysql_real_connect': libmysql.c:2177: warning: passing arg 5 of `gethostbyname_r' from incompatible pointer type libmysql.c:2177: too few arguments to function `gethostbyname_r' libmysql.c:2177: warning: assignment makes pointer from integer without a cast make[2]: *** [libmysql.lo] Error 1 make[2]: Leaving directory `/mysqltemp/mysql-4.1.0-alpha/libmysql' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/mysqltemp/mysql-4.1.0-alpha' make: *** [all] Error 2 -Original Message- From: Adam Carmichael [mailto:[EMAIL PROTECTED] Sent: Friday, October 03, 2003 11:44 PM To: Michael Cupp, Jr. Subject: Re: Installing I'm attempting to install using INSTALL-BINARIES and get this message while executing mysql_install_db: Did you download the binaries or compile from source? [cuppjr mysql]$ scripts/mysql_install_db scripts/mysql_install_db: ./bin/my_print_defaults: cannot execute binary file WARNING: The host 'raq2.homeunix.org' could not be looked up with resolveip. This probably means that your libc libraries are not 100 % compatible If you downloaded binaries, are they correct for your OS? Example: I run a bunch of OpenBSD and FreeBSD systems, and the Linux binaries won't work on them (not unless I do some emulation). I find it easiest to download the source, uncompress it, and then run the following set of commands: ./configure [any options such as ssl] make all make install /usr/local/bin/mysql_install_db then I log in and set the root password (can be done in mysqladmin I know, but there's normally other things I want to do without having to log in again). Those stages usually work on *BSD and Linux. Posting your OS, version of download, will be very helpful. There's also a section about incompatible versions of libc (see above) with MySQL, consider checking the version of libc that you have against what the MySQL download page says. Adam -- Adam Carmichael [EMAIL PROTECTED] with this binary MySQL version. The MySQL deamon, mysqld, should work normally with the exception that host name resolving will not work. This means that you should use IP addresses instead of hostnames when specifying MySQL privileges ! Installing privilege tables scripts/mysql_install_db: ./bin/mysqld: cannot execute binary file Installation of grant tables failed! Examine the logs in ./data for more information. You can also try to start the mysqld daemon with: /bin/mysqld --skip-grant You can use the command line tool /bin/mysql to connect to the mysql database and look at the grant tables: shell ./bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in ./data that may be helpful. The latest information about MySQL is available on the web at http://www.mysql.com Please consult the MySQL manual section: 'Problems running mysql_install_db', and the manual section that describes problems on your OS. Another information source is the MySQL email archive. Please check all of the above before mailing us! And if you do mail us, you MUST use the ./bin/mysqlbug script! Then if I try to run mysql or mysqld I get this: bash: /usr/local/mysql/bin/mysql: cannot execute binary file -- 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]
Installing
I'm attempting to install using INSTALL-BINARIES and get this message while executing mysql_install_db: [cuppjr mysql]$ scripts/mysql_install_db scripts/mysql_install_db: ./bin/my_print_defaults: cannot execute binary file WARNING: The host 'raq2.homeunix.org' could not be looked up with resolveip. This probably means that your libc libraries are not 100 % compatible with this binary MySQL version. The MySQL deamon, mysqld, should work normally with the exception that host name resolving will not work. This means that you should use IP addresses instead of hostnames when specifying MySQL privileges ! Installing privilege tables scripts/mysql_install_db: ./bin/mysqld: cannot execute binary file Installation of grant tables failed! Examine the logs in ./data for more information. You can also try to start the mysqld daemon with: ./bin/mysqld --skip-grant You can use the command line tool ./bin/mysql to connect to the mysql database and look at the grant tables: shell ./bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in ./data that may be helpful. The latest information about MySQL is available on the web at http://www.mysql.com Please consult the MySQL manual section: 'Problems running mysql_install_db', and the manual section that describes problems on your OS. Another information source is the MySQL email archive. Please check all of the above before mailing us! And if you do mail us, you MUST use the ./bin/mysqlbug script! Then if I try to run mysql or mysqld I get this: bash: /usr/local/mysql/bin/mysql: cannot execute binary file -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Comparing spaces with LIKE
How do I compare blank spaces in a string using LIKE ? I tried the following: SELECT * FROM table WHERE name LIKE '% John %'; But it didn't work. To explain: I don't want the string 'Johnson' to be shown when running the query above. Thanks. Nelson Azambuja Jr. Analista de Sistemas
Re: Reaching max between 1456-1458 connections
Brian Austin says: Have you read the following page in the Manual on their site? http://www.mysql.com/doc/en/Linux.html This gives you some tips to increase thread allocation. Especially interesting is the part about the LinuxThreads hack. That's where I started, and it's why I compiled my own MySQL. However, the constants mentioned on that page are no longer defined as of 2.4 kernels and 2.3 glibcs. It looks like Linux has been somewhat reengineered to remove hard limits on threads since that page was written. And indeed, I don't run into a limit at 1024 threads. So that's why I'm asking here, in case someone has more recent information than the MySQL website. Hope this helps, Thanks, I hope someone has the latest info. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
error creating database
Hi, I was received a error when I was trying to create a new database on freebsd 5.1, mysql 4.0.14. The test database is working well. COMMAND: mysqladmin create desenv or mysql create database desenv; ERROR: mysqladmin: CREATE DATABASE failed; error: 'Access denied for user: '@localhost' to database 'desenv'' The directory /usr/local/mysql and subfiles owner and group are mysql Anybody can help me! Thanks! Paulo Fonseca Jr. [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Reaching max between 1456-1458 connections
I'm baffled by reaching a limit of between 1456 and 1458 connections, at which point I can no longer make new connections. I've tried compiling my own MySQL and using the stock MySql RPM's. I've experimented with ulimits, values in my.cnf, and kernel parameters, and all the permutations of the above that seemed relevant. I've tried this on RedHat 8 and 9 systems with a 2.4 kernel and a glibc-2.3. I very much want to get this MySql installation to scale significantly higher, and I can no longer find any hard-coded limits that seem to be affecting the number of connections I can make. The two different boxes on which I have tested are different enough that it doesn't seem possible that the hardware could be the limiting factor and still turn out uniformally to reach the same maximum. They have very different amounts of RAM, and one box has just one significantly slower processor, whereas the other has two much faster processors. But both top out at between 1456 and 1458 connections, both The error I'm seeing is Can't create a new thread (errno 11). Can anyone suggest a way to get to the bottom of this problem and to increase whatever resource is limiting the number of threads I can create? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Re[2]: Can run two versions of MySQL in Windows 2000?
-Original Message- From: Stefan Hinz [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 23, 2003 5:15 PM To: Degan, George E, JR, MGSVC Cc: Paul DuBois; [EMAIL PROTECTED] Subject: Re[2]: Can run two versions of MySQL in Windows 2000? Degan, jumping in to try and solve some problems that look pretty obvious to me ... #options for default service (mysqld2) (mysqld2) It should be [mysqld2], not (mysqld2). (thanks -- did this) basedir = c:\4.1 Alpha\mysql As Paul pointed out, the blank in the path name is likely to cause problems (see below). my-opts2.cnf: [mysqld2] basedir = c:\4.1 Alpha\mysql Again, don't use a path that contains blanks. socket = mypipe2 As opposed to Unix, Windows doesn't know the concept of sockets (it uses named pipes instead). Named pipes, however, might cause problems with MySQL under Windows, so it's recommended not to use them, but rather use tcp/ip instead (that is why named pipes are disabled by default). You don't have to specify anything to use tcp/ip -- it's the default under Windows. (The above line in your configuration file is syntactically okay, but I'd recommend to delete it for the mentioned reasons. For MySQL 4.1, that should be protocol=mypipe2, anyway.) (thanks -- removed the two lines in the [mysqld] section: enable-named-pipes and socket = mypipe1 in the [mysqld2] section I removed enable-named-pipes and amended second line to protocol = mypipe2 Did I understand you correctly?) mysqld --defaults-file=C:\my-opts1.cnf response: mysqld: ERROR: unknown option '--enable-named-pipe' For the above given reasons, you shouldn't use named pipes. Get rid of the appropriate lines in your configuration files, and you'll get rid of that problem. (amended the Opts#.cnf files similar to the my.cnf file above) 030723 15:09:02 Error message file 'C:\mysql\share\english\errmsg.sys' had only 237 error messages, but it should contain at least 255 error messages. Check that the above file is the right version for this program! 030723 15:09:02 when I invoke it from the folder of the old version. and: mysqld-max: Can't change dir to 'C:\4.1 Alpha\mysql\data\' (Errcode:2) Well, that error message says it, doesn't it? Avoid having blanks in your pathnames. (changed live folder to c:\mysql-4.0.13 and the alpha folder to c:\mysql-4.1.0\) Okay, that looks good. When you open the Services Manager, I assume it shows lines for services named MySQL and mysqld2 and that they both have a status showing them to be running? (Sorry, the Services Manager? I'm not sure what that is.) On Windows 2000, you can open the Services Manager window as follows: Start Settings Control Panel Administration Services In that window you see a list of Windows services, and you should find two MySQL-related services running. (I think I messed up here. I decided to start from scratch, I removed MySQL Servers and Clients 4.0.13 from the program list (as it appears when I run the setup.exe it installs MySQL software in Windows) and deleted all the folders (which included the old c:\mysql\ directory with mysqld in the \bin folder) before performing the remove instructions below. So now I can't get to the correct mysqld service to remove it. It also appears that I can't remove it in windows as I don't see a remove or delete option in the Services area where you directed me. Will that be stuck there, now? MySQL and mysqld2 still appear in the services list.) But when I attempt to invoke MySQL the response is: ERROR 2003: Can't connect to MySQL server on 'localhost' (10061) Okay. What was the command you used here? (just the same as before: mysql or mysql -h localhost -u root What happens if you use this command: mysql -h localhost -P 3308 or this one: mysql -h . -S mypipe2 (both give the following response: ERROR 2003: Can't connect to MySQL server on 'localhost' (10061), and I tried several Ports, 3308, 3307, 3306, and 3309 in this order) All that means that the MySQL server you are trying to connect to isn't running. You can check in the Services Manager window, now that you know how to find that :) (BTW. When I came in today I found out that I could no longer connect to 4.1.0 either. When I left last night, I could connect to 4.1.0, but could no longer connect to 4.0.13. According to windows explorer the database files are still there, but for some reason mySQL can no longer point to them.) Once again, check in the Services Manager window and make sure the MySQL servers are actually running. I can only guess, but it looks as though they are not started at system startup (Start type: automatic). net stop svc-name-1 net stop svc-name-2 (by svs-name-1 and -2 do you refer to mysqld and mysqld2? If not, to what services do you refer?) To remove them: mysqld --remove svc-name-1 mysqld --remove svc-name-2 (it appears that you do not as mysqld --remove mysqld doesn't seem to make sense. To what services do you refer
RE: Re[4]: Can run two versions of MySQL in Windows 2000?
That did it! I tried disabling and rebooting and that didn't work, but I went into the new folders and simply removed the services as indicated and they are now gone. Thanks!! George -Original Message- From: miguel solórzano [mailto:[EMAIL PROTECTED] Sent: Thursday, July 24, 2003 9:45 AM To: Stefan Hinz; Degan, George E, JR, MGSVC Cc: Paul DuBois; [EMAIL PROTECTED]; Mark Matthews Subject: Re[4]: Can run two versions of MySQL in Windows 2000? At 14:47 24/7/2003 +0200, Stefan Hinz wrote: Hi, (I think I messed up here. I decided to start from scratch, I removed MySQL Servers and Clients 4.0.13 from the program list (as it appears when I run the setup.exe it installs MySQL software in Windows) and deleted all the folders (which included the old c:\mysql\ directory with mysqld in the \bin folder) before performing the remove instructions below. So now I can't get to the correct mysqld service to remove it. It also appears that I can't remove it in windows as I don't see a remove or delete option in the Services area where you directed me. Will that be stuck there, now? MySQL and mysqld2 still appear in the services list.) I had the same kind of problem before. I don't know why that is so, but Windows doesn't provide a remove option in the Services Manager window. You can, however, edit the properties of a service and set it to Disabled. After re-booting the system, that service should have gone. If it's still there, you can only remove it by hacking the Windows registry (Start Run regedit), looking for mysql... entries, delete them, and reboot. Maybe there's a more proper way of doing this, and if someone on this list knows, I'd be interested in how that would look like. (And, yes, I can hear all you Unix guys on this list chuckling.) Yes the currently installer doesn't remove the service during the un-install process. This deficiency should be fixed in the new installer which is developed by Mark ( I am cc this for him for to me correct if I am wrong). However in the today behavior I don't recommend to play with the registry. The situation is: - Before to un-install remove the service using the server binary with mysqld --remove or mysqld --remove service-name. - If you don't have anymore any server for to perform the remove service, you don't need to reboot, just re-install the new stuff and do the service remove. -- Regards, For technical support contracts, visit https://order.mysql.com/ Are you MySQL certified?, http://www.mysql.com/certification/ Miguel Angel Solórzano [EMAIL PROTECTED] São Paulo - Brazil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Re[2]: Can run two versions of MySQL in Windows 2000?
Successfully removed the services. I rechecked and, no mysqld, just MySQL and mysqld2. Thanks! That should close out this thread! -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Thursday, July 24, 2003 11:30 AM To: Degan, George E, JR, MGSVC; Stefan Hinz Cc: [EMAIL PROTECTED] Subject: RE: Re[2]: Can run two versions of MySQL in Windows 2000? At 6:23 -0500 7/24/03, Degan, George E, JR, MGSVC wrote: (I think I messed up here. I decided to start from scratch, I removed MySQL Servers and Clients 4.0.13 from the program list (as it appears when I run the setup.exe it installs MySQL software in Windows) and deleted all the folders (which included the old c:\mysql\ directory with mysqld in the \bin folder) before performing the remove instructions below. So now I can't get to the correct mysqld service to remove it. It also appears that I can't remove it in windows as I don't see a remove or delete option in the Services area where you directed me. Will that be stuck there, now? MySQL and mysqld2 still appear in the services list.) You need to remove the services using a MySQL server. You can use any of mysqld, mysqld-nt, mysqld-max, or mysql-max-nt to do this. Suppose you use mysqld. This command removes the service having the default name (MySQL): mysqld --remove And this one removes a service named mysqld2: mysqld --remove mysqld2 net stop svc-name-1 net stop svc-name-2 (by svs-name-1 and -2 do you refer to mysqld and mysqld2? If not, to what services do you refer?) MySQL (the default name) and mysqld2 (your second service name) To remove them: mysqld --remove svc-name-1 mysqld --remove svc-name-2 (it appears that you do not as mysqld --remove mysqld doesn't seem to make sense. To what services do you refer?) See above. Then start from the beginning, using the mysqld --install instructions in the manual. (So I don't have to Uninstall them in windows since they are now services? Or does this have the same effect?) I'd suggest you to get familiar with the Windows services concept. Services are, uh, services that can start and stop software programs (like the MySQL server). Rather than starting the software manually, you'd set up a service that does that (and can do that automatically, e.g. at system startup). Windows (NT, 2000, XP) usually uses services to start software that runs in the background, like the MySQL server. Unlike software, you don't uninstall a service, you _remove_ it, so it won't be there anymore to start the software program it's supposed to start. For the MySQL server, that's done with the mysqld --remove servicename command. (How do we remove the mysqld service?) You don't have a service named mysqld, do you? -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Can run two versions of MySQL in Windows 2000?
I'm sorry, I'm very new at this. I only downloaded the live version last week. Where do I find the setting you are referring to? By attempt to run the servers, perhaps this is a correct answer to your question: I am only using this for learning purposes. I am hoping to attain new skills and thought that SQL was a good place to start (since I believe it to be the base for many database languages (DBIV (or whatever version they're up to today, Oracle, Sybase, Peoplesoft, etc.) and perhaps if I lose my job this will broaden my choices in looking for a new position/career. If this is not a correct answer to your question, please clarify. Finally, thank you, I will change the name of the folder as you suggest. George -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 22, 2003 4:42 PM To: Degan, George E, JR, MGSVC; [EMAIL PROTECTED] Subject: RE: Can run two versions of MySQL in Windows 2000? At 14:15 -0500 7/22/03, Degan, George E, JR, MGSVC wrote: Thanks. I have been fighting with this all afternoon, now it seems I can get to 4.1.0 and not 4.0.13. My databases still seem to be there but I can't point to them either in mysql or in mysqlcc/DBManager. I guess I have moved port 3306 to the 4.1.0 database. How do I name, say, 3307 as the 4.0.13 database? First, you're better off not installing 4.1 (or any version) in a directory with a pathname that contains spaces. I suggest renaminng c:\4.1 Alpha to c:\4.1-Alpha. Second, tell us more about your setup. You're asking us to diagnose the problem with little more information than it doesn't work. What are your settings? How are you attempting to run the servers? Thanks. -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 22, 2003 12:00 PM To: [EMAIL PROTECTED] Subject: Re: Can run two versions of MySQL in Windows 2000? Degan, George E, JR, MGSVC [EMAIL PROTECTED] wrote: I have been running 4.0.13 for several weeks now and though I'd like to test 4.1.0. 4.0.13 is under c:\MySQL. I downloaded 4.1.0 under c:\4.1 Alpha\MySQL. When I try to launch mysql it brings me to the old version, how do I access the new one without killing the old one? (I have also tried putting 4.1.0 on a totally different machine and linking to it also.) Yes, you can find info about it at: http://www.mysql.com/doc/en/Multiple_Windows_servers.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Can run two versions of MySQL in Windows 2000?
SETUP Windows 2000, Version 5.0 (build 2195, Service Pack 2) (In case it matters, I have about 20 GB available on my 40GB hard drive and 192MB of RAM) SETTINGS Prior to attempting to put on the second server, I wasn't using any options, which was logging me in as [EMAIL PROTECTED] (or more likely [EMAIL PROTECTED]), later I invoked mysql as follows: mysql -h localhost -u root to log in as the root. If you see my treads I was having problems with building temporary tables and was advised to set up a root user, turned out one was set up with the installation so I began using it.). When I started attempting to set up the multiple versions, Victoria Reznichenko directed me to the user manual and was attempting to set it up that way. I shut down the original mysql server and installed the mysql in the 4.1 Alpha folder group with the option mysqld-nt --install mysqld2 which was referenced per the manual in a file called my.cnf as follows: # options for mysqld2 service [mysqld2] basedir = C:/mysql-4.0.14 port = 3308 enable-named-pipe socket = mypipe2 It installed with no errors. Then it instructed I startup the servers as follows: NET START mysql NET START mysqld2 when I did that I got the response: The MySQL service started succesfully. and The mysqld2 service started succesfully. respectively. But when I attempt to invoke MySQL the response is: ERROR 2003: Can't connect to MySQL server on 'localhost' (10061) (BTW. When I came in today I found out that I could no longer connect to 4.1.0 either. When I left last night, I could connect to 4.1.0, but could no longer connect to 4.0.13. According to windows explorer the database files are still there, but for some reason mySQL can no longer point to them.) How I'm running the servers: they are services in Windows. Though I had nothing to do with that. The setup/install utility handled that for 4.0.13. Do I have to give them a unique name for 4.1.0 so Windows can distinguish 4.1 from 4.0? Better detail? Thanks for not giving up on me! George -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 23, 2003 12:59 PM To: Degan, George E, JR, MGSVC Cc: [EMAIL PROTECTED] Subject: RE: Can run two versions of MySQL in Windows 2000? At 6:32 -0500 7/23/03, Degan, George E, JR, MGSVC wrote: I'm sorry, I'm very new at this. I only downloaded the live version last week. Where do I find the setting you are referring to? By attempt to run the servers, perhaps this is a correct answer to your question: I am only using this for learning purposes. I am hoping to attain new skills and thought that SQL was a good place to start (since I believe it to be the base for many database languages (DBIV (or whatever version they're up to today, Oracle, Sybase, Peoplesoft, etc.) and perhaps if I lose my job this will broaden my choices in looking for a new position/career. If this is not a correct answer to your question, please clarify. It's not. I'll attempt to rephrase my questions. However, I will also suggest that if, as you say, you're very new at this, attempting to run multiple servers before you are comfortable with running a single server is not likely to be very easy. re: your setup: What version of Windows do you have? re: your settings: What options are you using to start the servers? (for example, are you passing options on the command line or storing them in an option file? If an option file, which one and what do its contents look like?) re: how you're running the servers: What command do you use to invoke the servers? Are you starting them from the command line, or installing them as services and running them that way? It is a good idea to explain exactly what you're doing, and to provide any relevant error messages that occur. (By that I mean the text of the error messages themselves -- not just a general description of the failure.) Thanks. Finally, thank you, I will change the name of the folder as you suggest. George -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 22, 2003 4:42 PM To: Degan, George E, JR, MGSVC; [EMAIL PROTECTED] Subject: RE: Can run two versions of MySQL in Windows 2000? At 14:15 -0500 7/22/03, Degan, George E, JR, MGSVC wrote: Thanks. I have been fighting with this all afternoon, now it seems I can get to 4.1.0 and not 4.0.13. My databases still seem to be there but I can't point to them either in mysql or in mysqlcc/DBManager. I guess I have moved port 3306 to the 4.1.0 database. How do I name, say, 3307 as the 4.0.13 database? First, you're better off not installing 4.1 (or any version) in a directory with a pathname that contains spaces. I suggest renaminng c:\4.1 Alpha to c:\4.1-Alpha. Second, tell us more about your setup. You're asking us to diagnose the problem with little more information than it doesn't work. What are your settings? How are you attempting to run the servers? Thanks. -Original
RE: Can run two versions of MySQL in Windows 2000?
My responses are parenthesied in the text below. -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 23, 2003 2:56 PM To: Degan, George E, JR, MGSVC Cc: [EMAIL PROTECTED] Subject: RE: Can run two versions of MySQL in Windows 2000? At 13:01 -0500 7/23/03, Degan, George E, JR, MGSVC wrote: SETUP Windows 2000, Version 5.0 (build 2195, Service Pack 2) (In case it matters, I have about 20 GB available on my 40GB hard drive and 192MB of RAM) SETTINGS Prior to attempting to put on the second server, I wasn't using any options, which was logging me in as [EMAIL PROTECTED] (or more likely [EMAIL PROTECTED]), ODBC is the default user name that clients use on Windows. (Thanks for the input) later I invoked mysql as follows: mysql -h localhost -u root to log in as the root. If you see my treads I was having problems with building temporary tables and was advised to set up a root user, turned out one was set up with the installation so I began using it.). When I started attempting to set up the multiple versions, Victoria Reznichenko directed me to the user manual and was attempting to set it up that way. I shut down the original mysql server and installed the mysql in the 4.1 Alpha folder group with the option mysqld-nt --install mysqld2 which was referenced per the manual in a file called my.cnf as follows: # options for mysqld2 service [mysqld2] basedir = C:/mysql-4.0.14 port = 3308 enable-named-pipe socket = mypipe2 Okay. You don't have any [mysqld] group for the other server, I take it? But that configuration is odd. It appears to apply to a 4.0.14 installation, yet you state that you're using it for your 4.1 installation. What installation directories are you now using for your two MySQL versions? (Sorry for the confusion, the my.cnf file had two parts to it, I only copied the part about mysqld2, didn't realize the first one was relevant. Here is the entire file (I copied the previous one from the manual, but I did actually run it with the correct directory names): # options for default service (MySql) [mysqld] basedir = C:/mysql port = 3306 enable-named-pipe socket = mypipe1 #options for default service (mysqld2) (mysqld2) basedir = c:\4.1 Alpha\mysql port = 3307 enable-named-pipe socket = mypipe2 It didn't seem to understand the line enable-named-pipe though as when I tried running the following files: my-opts1.cnf: [mysqld] basedir = C:/mysql port = 3306 enable named-pipe socket = mypipe1 my-opts2.cnf: [mysqld2] basedir = c:\4.1 Alpha\mysql port = 3307 enable-named-pipe socket = mypipe2 With these commands: mysqld --defaults-file=C:\my-opts1.cnf response: mysqld: ERROR: unknown option '--enable-named-pipe' mysqld-max --defaults-file=C:\my-opts2.cnf response: yesterday it gave a similar one to the above, though when I do it now it responds: 030723 15:09:02 Error message file 'C:\mysql\share\english\errmsg.sys' had only 237 error messages, but it should contain at least 255 error messages. Check that the above file is the right version for this program! 030723 15:09:02 when I invoke it from the folder of the old version. and: mysqld-max: Can't change dir to 'C:\4.1 Alpha\mysql\data\' (Errcode:2) 030723 15:15:06 Aborting 030723 15:15:06 mysqld-max: Shutdown Complete again per instructions from the manual. I have included the error messages when I attempted them above.) It installed with no errors. Then it instructed I startup the servers as follows: NET START mysql NET START mysqld2 when I did that I got the response: The MySQL service started succesfully. and The mysqld2 service started succesfully. respectively. Okay, that looks good. When you open the Services Manager, I assume it shows lines for services named MySQL and mysqld2 and that they both have a status showing them to be running? (Sorry, the Services Manager? I'm not sure what that is.) But when I attempt to invoke MySQL the response is: ERROR 2003: Can't connect to MySQL server on 'localhost' (10061) Okay. What was the command you used here? (just the same as before: mysql or mysql -h localhost -u root What happens if you use this command: mysql -h localhost -P 3308 or this one: mysql -h . -S mypipe2 (both give the following response: ERROR 2003: Can't connect to MySQL server on 'localhost' (10061), and I tried several Ports, 3308, 3307, 3306, and 3309 in this order) (BTW. When I came in today I found out that I could no longer connect to 4.1.0 either. When I left last night, I could connect to 4.1.0, but could no longer connect to 4.0.13. According to windows explorer the database files are still there, but for some reason mySQL can no longer point to them.) How I'm running the servers: they are services in Windows. Though I had nothing to do with that. The setup/install utility handled that for 4.0.13. Do I have to give them a unique name for 4.1.0 so Windows can distinguish 4.1 from 4.0? Yes. The Services Manager will show what
Can run two versions of MySQL in Windows 2000?
I have been running 4.0.13 for several weeks now and though I'd like to test 4.1.0. 4.0.13 is under c:\MySQL. I downloaded 4.1.0 under c:\4.1 Alpha\MySQL. When I try to launch mysql it brings me to the old version, how do I access the new one without killing the old one? (I have also tried putting 4.1.0 on a totally different machine and linking to it also.) George -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Can run two versions of MySQL in Windows 2000?
Thanks. I have been fighting with this all afternoon, now it seems I can get to 4.1.0 and not 4.0.13. My databases still seem to be there but I can't point to them either in mysql or in mysqlcc/DBManager. I guess I have moved port 3306 to the 4.1.0 database. How do I name, say, 3307 as the 4.0.13 database? -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 22, 2003 12:00 PM To: [EMAIL PROTECTED] Subject: Re: Can run two versions of MySQL in Windows 2000? Degan, George E, JR, MGSVC [EMAIL PROTECTED] wrote: I have been running 4.0.13 for several weeks now and though I'd like to test 4.1.0. 4.0.13 is under c:\MySQL. I downloaded 4.1.0 under c:\4.1 Alpha\MySQL. When I try to launch mysql it brings me to the old version, how do I access the new one without killing the old one? (I have also tried putting 4.1.0 on a totally different machine and linking to it also.) Yes, you can find info about it at: http://www.mysql.com/doc/en/Multiple_Windows_servers.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Another Newbie Question
I am finally able to enter data and am going through the Tutorial in section 3 of the mySQL manual. It suggests that I create a .txt file from which to load date into a table. Where does mySQL look for data to load in the default installation? I thought it would be in the data folder under mysql, but it can't find it. Please advise. Thanks, George -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
More duhh! questions
I am attempting to create a temporary table to do a complex query and I get an error: error 1044: Access denied for user: '@localhost' to database 'shopsample' what can I do to keep this from happening? I am using the production version of mySQL 4.0.13 in windows 2000. Here is the query: create temporary table tmp ( article int(4) unsigned zerofill default '' not null, price double(16,2) default '0.00' not null); the goal is to continue to the following: lock tables shop read; insert into tmp select article, max(price) from shop group by article; select shop.article, dealer, shop.price from shop, tmp where shop.article=tmp.article and shop.price=tmp.price; unlock tables; drop table tmp; Any assistance would be appreciated. George -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
