Referring to columns by ordinal
Hi all, I've searched all over the place for a solution to this, and I hope you can help. I'm trying to write a very simple database handling script. The SQL statement I'm trying to do is SELECT * FROM tablename WHERE the_first_column = a_number; All my tables have the primary key in the first column, but they are called different names. What can I use to replace the_first_column. TIA, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can someone please explain this?
During some training on SQL recently, the following occurred. No one can explain it. The training used five tables A, B, C , D and E, each with a single int column. Table A has column a with values 1, 2, 3, 4, 5 Table B has column b with values 1, 2, 3, 4 Table C has column c with values 1, 2, 3, 4, 5, 6 Table D has column d with values 1, 2, 3, 4 Table E has column e with values 1, 2, 3 When running the following SQL... select * from E left join C on e = c right join B on e = b; we got the following results: +--+--+--+ | e| c| b| +--+--+--+ |1 |1 |1 | |2 | NULL |1 | |3 | NULL |1 | |1 | NULL |2 | |2 |2 |2 | |3 | NULL |2 | |1 | NULL |3 | |2 | NULL |3 | |3 |3 |3 | |1 | NULL |4 | |2 | NULL |4 | |3 | NULL |4 | +--+--+--+ 12 rows in set (0.00 sec) Common sense expects the number of rows returned to be four. So where's the Cartesian coming from? - ian -- | Ian Sales Broadband Solutions for | | Database AdministratorHome Business | | PlusNet plc http://www.plus.net | + --- PlusNet - The Smarter Way to Internet + -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB deadlock problem
Hi, I've got a deadlock problem using InnoDB tables in MySQL 4.1.5 for win32. I have two tables, 'jobs' and 'results', where 'results' has a foreign key 'id_job' that references the primary key of 'jobs'. There may be more than one result for any given job. Both tables have a single auto_increment column for their primary key. I have two threads that process a queue of jobs; when a job is completed, I want to delete all existing results for that job (if any) and insert the results for that job, e.g.: Transaction 1: START TRANSACTION; DELETE FROM results WHERE id_job = 25920; INSERT INTO results(result,id_job) VALUES (31.461937,25920); COMMIT; Transaction 2: START TRANSACTION; DELETE FROM results WHERE id_job = 25919; INSERT INTO results(result,id_job) VALUES (25.388607,25919),(22.650234,25919); COMMIT; I'm using the default isolation level. Deadlock sometimes occurs, relevant output of SHOW INNODB STATUS is below: LATEST DETECTED DEADLOCK 041006 10:06:10 *** (1) TRANSACTION: TRANSACTION 0 147130, ACTIVE 0 sec, OS thread id 2688 starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 320 MySQL thread id 7157, query id 30803 localhost 127.0.0.1 root updating DELETE FROM results WHERE id_job = 25920 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 2179 n bits 688 index `id_job` of table `test/results` trx id 0 147130 lock_mode X waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; 1-byte offs TRUE; info bits 0 0: len 9; hex 73757072656d756d00; asc supremum ;; *** (2) TRANSACTION: TRANSACTION 0 147129, ACTIVE 0 sec, OS thread id 3556 inserting, thread declared inside InnoDB 499 mysql tables in use 1, locked 1 6 lock struct(s), heap size 1024, undo log entries 1 MySQL thread id 7156, query id 30799 localhost 127.0.0.1 root update INSERT INTO results(result,id_job) VALUES (25.388607,25919),(22.650234,25919) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 2179 n bits 688 index `id_job` of table `test/results` trx id 0 147129 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; 1-byte offs TRUE; info bits 0 0: len 9; hex 73757072656d756d00; asc supremum ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 2179 n bits 688 index `id_job` of table `test/results` trx id 0 147129 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; 1-byte offs TRUE; info bits 0 0: len 9; hex 73757072656d756d00; asc supremum ;; *** WE ROLL BACK TRANSACTION (1) Thanks in advance for any help. David - ALL-NEW Yahoo! Messenger - all new features - even more fun!
Re: InnoDB deadlock problem
On Wed, 6 Oct 2004, David Edwards wrote: I've got a deadlock problem using InnoDB tables (...) Transaction 1: START TRANSACTION; DELETE FROM results WHERE id_job = 25920; INSERT INTO results(result,id_job) VALUES (31.461937,25920); COMMIT; Transaction 2: START TRANSACTION; DELETE FROM results WHERE id_job = 25919; INSERT INTO results(result,id_job) VALUES (25.388607,25919),(22.650234,25919); COMMIT; I think this manual page might explain what's happening: http://dev.mysql.com/doc/mysql/en/InnoDB_Next-key_locking.html -- Tobias Asplund [EMAIL PROTECTED] Trainer and Consultant, Sweden MySQL AB http://www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB deadlock problem
Hi Tobias, Thanks for your reply. Unfortunately I couldn't see from the manual why I was getting the deadlock - transaction 2 already has a lock on the index it is waiting for. The difference seems to be 'insert intention' - I'm not sure what different types of exclusive lock there are and how they relate to each other. Is there any way I can get both types of lock in one go, in the first statement I execute? Thanks, David Tobias Asplund [EMAIL PROTECTED] wrote: On Wed, 6 Oct 2004, David Edwards wrote: I've got a deadlock problem using InnoDB tables (...) Transaction 1: START TRANSACTION; DELETE FROM results WHERE id_job = 25920; INSERT INTO results(result,id_job) VALUES (31.461937,25920); COMMIT; Transaction 2: START TRANSACTION; DELETE FROM results WHERE id_job = 25919; INSERT INTO results(result,id_job) VALUES (25.388607,25919),(22.650234,25919); COMMIT; I think this manual page might explain what's happening: http://dev.mysql.com/doc/mysql/en/InnoDB_Next-key_locking.html -- Tobias Asplund Trainer and Consultant, Sweden MySQL AB http://www.mysql.com - ALL-NEW Yahoo! Messenger - all new features - even more fun!
Re: Long Running Queries
How do you do to benchmark a query ? I wish we had thought to have had him benchmark a query before and after he added an index. It would be interesting to see the difference in actual time that an index can make on a table with 450,000 records. On Wed, 2004-10-06 at 15:31, Jason Williard wrote: Amit, You are awesome! That fixed it quite nicely. Our system is screaming now :-) Thank You VERY MUCH!, Jason Williard Client Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Referring to columns by ordinal
- Original Message - From: Paul Hanlon [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 07, 2004 4:55 AM Subject: Referring to columns by ordinal Hi all, I've searched all over the place for a solution to this, and I hope you can help. I'm trying to write a very simple database handling script. The SQL statement I'm trying to do is SELECT * FROM tablename WHERE the_first_column = a_number; All my tables have the primary key in the first column, but they are called different names. What can I use to replace the_first_column. Sorry, I don't think there is any way to use ordinals in a WHERE clause. You will have to use the actual name of the first column. According to the manual, http://dev.mysql.com/doc/mysql/en/Problems_with_alias.html, you can use an alias to refer to a column in GROUP BY, ORDER BY, or HAVING but *not* in WHERE. The reason: This is because when the WHERE code is executed, the column value may not yet be determined. Now, this refers to an alias which you have defined in a SELECT clause, like 'Select count(*) as num, not an ordinal. I don't see anything that explicitly says that you can't have an ordinal in a WHERE so there is always the possibility that an ordinal is valid in a WHERE. However, I think you can disprove that possibility very quickly by trying a query like: select * from mytable where 1 = 'Jones'; This assumes that the first column of your table contains surnames. I think you'll see that this doesn't work. There's at least one very practical reason why it would be very confusing if ordinals were allowed in WHERE clauses. Suppose you had a table that contained integers in the some columns and you used an ordinal to represent the column position instead of using its name. Consider this query: select * from mytable where 2 = 7; Is this query trying to find all the rows where the value in the second column is 7 or all the rows where the value in the 7th column is 2? The only way to prevent a misinterpretation here is to insist that the integer to the left of the equal sign is always a column ordinal and the integer to the right of the equal sign is always a literal. The only solution I can see for your problem would be to re-create all of your tables and this time name the first column of each table something like 'key' or 'primary key'. Then, all your queries could say something like: select * from mytable where key = 7; Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delete duplicate entry
Batara Kesuma wrote:
Hi,
I have a table that looks like:
CREATE TABLE `message_inbox` (
`member_id` mediumint(8) unsigned NOT NULL default '0',
`message_id` int(10) unsigned NOT NULL default '0',
`new` enum('y','n','replied') NOT NULL default 'y',
`datetime` datetime default NULL,
KEY `idx_1` (`member_id`,`new`),
KEY `idx_2` (`member_id`,`datetime`)
) TYPE=InnoDB
Now, I want to add a primary key to it.
ALTER TABLE message_inbox ADD PRIMARY KEY (member_id, message_id)
But there are already some duplicated entries.
ERROR 1062: Duplicate entry '10244-80871' for key 1
How can I tell MySQL to delete the duplicated entry and continue to make primary key? Is there any efficient way to do this? Thank you very much.
try ALTER IGNORE TABLE.
Regards,
bk
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Referring to columns by ordinal
The programming solution is work out the column name in your script, ie do describe tablename in your script, look for the column name marked as PRI in the key column, then insert this column name in the select statement. Andy -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: 07 October 2004 14:08 To: Paul Hanlon; [EMAIL PROTECTED] Subject: Re: Referring to columns by ordinal - Original Message - From: Paul Hanlon [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 07, 2004 4:55 AM Subject: Referring to columns by ordinal Hi all, I've searched all over the place for a solution to this, and I hope you can help. I'm trying to write a very simple database handling script. The SQL statement I'm trying to do is SELECT * FROM tablename WHERE the_first_column = a_number; All my tables have the primary key in the first column, but they are called different names. What can I use to replace the_first_column. Sorry, I don't think there is any way to use ordinals in a WHERE clause. You will have to use the actual name of the first column. According to the manual, http://dev.mysql.com/doc/mysql/en/Problems_with_alias.html, you can use an alias to refer to a column in GROUP BY, ORDER BY, or HAVING but *not* in WHERE. The reason: This is because when the WHERE code is executed, the column value may not yet be determined. Now, this refers to an alias which you have defined in a SELECT clause, like 'Select count(*) as num, not an ordinal. I don't see anything that explicitly says that you can't have an ordinal in a WHERE so there is always the possibility that an ordinal is valid in a WHERE. However, I think you can disprove that possibility very quickly by trying a query like: select * from mytable where 1 = 'Jones'; This assumes that the first column of your table contains surnames. I think you'll see that this doesn't work. There's at least one very practical reason why it would be very confusing if ordinals were allowed in WHERE clauses. Suppose you had a table that contained integers in the some columns and you used an ordinal to represent the column position instead of using its name. Consider this query: select * from mytable where 2 = 7; Is this query trying to find all the rows where the value in the second column is 7 or all the rows where the value in the 7th column is 2? The only way to prevent a misinterpretation here is to insist that the integer to the left of the equal sign is always a column ordinal and the integer to the right of the equal sign is always a literal. The only solution I can see for your problem would be to re-create all of your tables and this time name the first column of each table something like 'key' or 'primary key'. Then, all your queries could say something like: select * from mytable where key = 7; Rhino -- 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]
JOIN in the same table
Hi, I have a situation like this: Table People = people_ID people_name people_friend_ID people_friends_ID is the people_ID from another record. Is there a way to make a SELECT that returns people_name and people_friend_name? Perhaps I´d get this with sub-selects but I´m using MySQL-4.0.18. Any help would be appreciated. Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN in the same table
Ronan Lucio wrote: Hi, I have a situation like this: Table People = people_ID people_name people_friend_ID people_friends_ID is the people_ID from another record. Is there a way to make a SELECT that returns people_name and people_friend_name? SELECT p1.people_name,p2.people_name FROM People p1 LEFT JOIN People p2 ON (p2.people_ID = p1.people_friend_ID); -m -- ## Mark T. Dame mailto:[EMAIL PROTECTED] ## VP, Product Development ## MFM Communication Software (http://www.mfm.com/) When I'm with you I don't know whether I should study neurosurgery or go to see the Care Bears Movie. -- Weird Al Yankovic, You Make Me -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [OT] Email heaaders and threading (was Re: update MySQL)
-Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] This tells the recipient's email client that your message is a reply, not a new message, despite your efforts to change the subject and recipients. Many email clients use that header to decide which thread a message belongs to. That's actually the point of the header. I get it now. I wasn't aware of that, since every email client I've ever seen seems to thread strictly by subject. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can someone please explain this?
It's a bug: http://bugs.mysql.com/1677 http://bugs.mysql.com/1591 http://bugs.mysql.com/3765 Depending on which bug report you look at, this result is either because mysql treats this as a nested join, or because mysql does not support nested joins. Frankly, I find the explanations make no sense. Meanwhile, I believe changing your RIGHT JOIN to a LEFT JOIN will yield the results you were expecting: SELECT * FROM B LEFT JOIN E ON e = b LEFT JOIN C ON e = c; +--+--+--+ | b| e| c| +--+--+--+ |1 |1 |1 | |2 |2 |2 | |3 |3 |3 | |4 | NULL | NULL | +--+--+--+ 4 rows in set (0.13 sec) Michael Ian Sales wrote: During some training on SQL recently, the following occurred. No one can explain it. The training used five tables A, B, C , D and E, each with a single int column. Table A has column a with values 1, 2, 3, 4, 5 Table B has column b with values 1, 2, 3, 4 Table C has column c with values 1, 2, 3, 4, 5, 6 Table D has column d with values 1, 2, 3, 4 Table E has column e with values 1, 2, 3 When running the following SQL... select * from E left join C on e = c right join B on e = b; we got the following results: +--+--+--+ | e| c| b| +--+--+--+ |1 |1 |1 | |2 | NULL |1 | |3 | NULL |1 | |1 | NULL |2 | |2 |2 |2 | |3 | NULL |2 | |1 | NULL |3 | |2 | NULL |3 | |3 |3 |3 | |1 | NULL |4 | |2 | NULL |4 | |3 | NULL |4 | +--+--+--+ 12 rows in set (0.00 sec) Common sense expects the number of rows returned to be four. So where's the Cartesian coming from? - ian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN in the same table
This should work. No sub-selects needed here. select a.people_name, b.people_name as people_friend_name from people a, people b where a.people_friend_ID=b.people_ID Remi Mikalsen E-Mail: [EMAIL PROTECTED] URL:http://www.iMikalsen.com On 7 Oct 2004 at 10:34, Ronan Lucio wrote: Hi, I have a situation like this: Table People = people_ID people_name people_friend_ID people_friends_ID is the people_ID from another record. Is there a way to make a SELECT that returns people_name and people_friend_name? Perhaps I´d get this with sub-selects but I´m using MySQL-4.0.18. Any help would be appreciated. Thanks, Ronan -- 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]
**newbie question** renaming a database
using mysql version 4.0.13 - **total newbie here**... something as simple as renaming a database...i've not found anything relating to that on the mysql site or anywhere else. what i have found is that in order to do this, i'd have to shutdown and restart the mysql database in order to do something as simple as this. is there another way? as mysql user 'root' i've tried: mysql rename database old_db to new_db thnx, cheers! -a -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup problem
At 12:27 AM 10/7/2004, you wrote: This is what I need to do, what is the best approach for doing this? I need to take backup of few tables in mysql, then I need to drop that entire DB, recreate it, (and sth else that's not relevant here) and then restore these backed up tables. This backup and restoring should be as fast as possible. How can I do this? Here's a few ideas. I'm sure there are more ways to do it. 1) MyISAM or InnoDb? Which version? How many rows in the tables? 2) Is the backup database accessible on the network? If so why not just create the backup database tables on the fly using something like: use bu_database; lock tables old_database.table1, ... old_database.tablen; flush tables old_database.table1, ..., old_database.tablen; create table1 like old_database.table1; insert into table1 select * from old_database.table1; create tablen like old_database.tablen; insert into tablen select * from old_database.tablen; compare the table row counts from both databases to make sure all rows were transferred. Then if ok, drop the old_database. If the table has a lot of indexes, a faster method would be to not define the indexes until later and just do a use bu_database; create table1 select * from old_database.table1; alter table1 add index ..., add index ..., add index. /*build all indexes with 1 statement*/ Of course you need to know what the old index structure was for the Alter Table command. Keep in mind that an Alter Table will create a copy of the table while it is building the indexes. I'm not sure why it has to do this when building indexes, but it does. (Unless they've changed it in 4.1 and if so someone can jump in and correct me if I'm wrong.) 3) If the backup database is not accessible on the network then you will need to do a MySQLDump (or MySQLHotCopy for ISAM/MyISAM tables) and transport the file over to the new machine and run the script there. You will then have to check the row counts manually before dropping the old database. This is usually the preferred method for large tables (several million rows). Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: **newbie question** renaming a database
Carolina wrote: using mysql version 4.0.13 - **total newbie here**... something as simple as renaming a database...i've not found anything relating to that on the mysql site or anywhere else. what i have found is that in order to do this, i'd have to shutdown and restart the mysql database in order to do something as simple as this. is there another way? as mysql user 'root' i've tried: mysql rename database old_db to new_db thnx, cheers! -a You may think this is simple, but you have to make sure all users are out of the database, and all records are flushed before it could be renamed. It is much safer to shutdown the server and rename the directory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: **newbie question** renaming a database
At 11:16 -0500 10/7/04, gerald_clark wrote: Carolina wrote: using mysql version 4.0.13 - **total newbie here**... something as simple as renaming a database...i've not found anything relating to that on the mysql site or anywhere else. what i have found is that in order to do this, i'd have to shutdown and restart the mysql database in order to do something as simple as this. is there another way? as mysql user 'root' i've tried: mysql rename database old_db to new_db thnx, cheers! -a You may think this is simple, but you have to make sure all users are out of the database, and all records are flushed before it could be renamed. It is much safer to shutdown the server and rename the directory. It's simple conceptually, but complex to actually implment. In addition to the issues gerald mentions, remember that if you have InnoDB tables, they aren't actually stored in the database directory unless you're using individual tablespaces -- and even then, there is an entry for them in the InnoDB data dictionary that is stored in the shared tablespace. And those entries include the database name. If you rename the database directory, those entries become invalid. Also, if you have foreign key relationship, there are similar difficulties. Might be better to create a new database and then RENAME TABLE each table from the original database into the new database. Then drop the original database. The strategy of shutting down the server, renaming the database directory, and restartingg the server does work fine _if_ your database contains only MyISAM (or ISAM) tables, though. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Referring to columns by ordinal
Many thanks for the quick response. All good suggestions. I also noticed that in PHP there is a mysql_fetch_field function which returns a collection of properties about a given field, including it's name and whether it is a primary key, so with a little rejigging of my code, I'll use this. I want the script to give me table information about a database, to be able to click on a table and get all the records in it and then to be able to click on a record and edit or delete it, and I want to do it as generically as possible, so that the only parameters I need to give it are host, user, pass and db name. I got the first two no problem, and all nice and tightly coded, and in order to get the third one, I needed to find out the record id and this is why I was asking if it was possible, it would have saved another loop. Thanks again. Paul. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question about using select...where f in (xxx)
I have a quick SQL question that I hope someone can answer,
I have two tables
Events { e_id int(11), e_name varchar(200) };
Bookings { customer varchar(200), event_list varchar(200) };
Event_list is a string of comma separated values, eg 1,2,4 where the
number corresponds to Events.e_id
Sample data: Events { {1,'one'}, {2,'two'}, {3,'three'} }
Booking { {'john','1,3'} , {'jane','2,3'} }
I would like some query that will return 2 rows
John, 'one, three'
Jane, 'two,three'
Is this possible ?
I inherited the schema and the data, so I would prefer not having to
refactor anything, unless I have to.
Thanks in Advance,
Neal
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can someone please explain this?
Michael Stassen wrote: It's a bug: http://bugs.mysql.com/1677 http://bugs.mysql.com/1591 http://bugs.mysql.com/3765 Depending on which bug report you look at, this result is either because mysql treats this as a nested join, or because mysql does not support nested joins. Frankly, I find the explanations make no sense. - not sure myself why a left join followed by a right join is a nested join, but a right join followed by a left join isn't... If you write the query as: select * from E right join B on e = b left join C on e = c; ... it returns the expected results. - but thanks for the help. - ian -- | Ian Sales Broadband Solutions for | | Database AdministratorHome Business | | PlusNet plc http://www.plus.net | + --- PlusNet - The Smarter Way to Internet + -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about using select...where f in (xxx)
I would strongly recommend refactoring as the string transformation you
mention cannot be accomplished without external scripting assistance.
change your Bookings table to be (customer varchar(200), event_Id int(11))
This way, each booking becomes one row in the table. Five bookings = five
rows. This change also allows you to add additional fields (like price,
date, contact info, etc) on the Bookings table so that you can track that
information about EACH booking. I have no idea how you are tracking any of
that the way you have the data now.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Neal K [EMAIL PROTECTED] wrote on 10/07/2004 12:33:40 PM:
I have a quick SQL question that I hope someone can answer,
I have two tables
Events { e_id int(11), e_name varchar(200) };
Bookings { customer varchar(200), event_list varchar(200) };
Event_list is a string of comma separated values, eg 1,2,4 where
the
number corresponds to Events.e_id
Sample data: Events { {1,'one'}, {2,'two'}, {3,'three'} }
Booking { {'john','1,3'} , {'jane','2,3'} }
I would like some query that will return 2 rows
John, 'one, three'
Jane, 'two,three'
Is this possible ?
I inherited the schema and the data, so I would prefer not having to
refactor anything, unless I have to.
Thanks in Advance,
Neal
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about using select...where f in (xxx)
I second this. The way you are storing multiple data items in a single
column is very bad database design and fails the test for even the first
normal form. You are going to get very poor performance and usability
out of this table design. I know you said you inherited the data so its
probably not your fault, but I believe you will save yourself
considerable headache by refactoring the table rather than working
around the poor design.
John McCaskey
On Thu, 2004-10-07 at 13:03 -0400, [EMAIL PROTECTED] wrote:
I would strongly recommend refactoring as the string transformation you
mention cannot be accomplished without external scripting assistance.
change your Bookings table to be (customer varchar(200), event_Id int(11))
This way, each booking becomes one row in the table. Five bookings = five
rows. This change also allows you to add additional fields (like price,
date, contact info, etc) on the Bookings table so that you can track that
information about EACH booking. I have no idea how you are tracking any of
that the way you have the data now.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Neal K [EMAIL PROTECTED] wrote on 10/07/2004 12:33:40 PM:
I have a quick SQL question that I hope someone can answer,
I have two tables
Events { e_id int(11), e_name varchar(200) };
Bookings { customer varchar(200), event_list varchar(200) };
Event_list is a string of comma separated values, eg 1,2,4 where
the
number corresponds to Events.e_id
Sample data: Events { {1,'one'}, {2,'two'}, {3,'three'} }
Booking { {'john','1,3'} , {'jane','2,3'} }
I would like some query that will return 2 rows
John, 'one, three'
Jane, 'two,three'
Is this possible ?
I inherited the schema and the data, so I would prefer not having to
refactor anything, unless I have to.
Thanks in Advance,
Neal
--
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: Can someone please explain this?
From 5.0.1, I get ... e c b 1 1 1 2 2 2 3 3 3 NULLNULL4 PB - Original Message - From: Ian Sales To: [EMAIL PROTECTED] Sent: Thursday, October 07, 2004 4:21 AM Subject: Can someone please explain this? During some training on SQL recently, the following occurred. No one can explain it. The training used five tables A, B, C , D and E, each with a single int column. Table A has column a with values 1, 2, 3, 4, 5 Table B has column b with values 1, 2, 3, 4 Table C has column c with values 1, 2, 3, 4, 5, 6 Table D has column d with values 1, 2, 3, 4 Table E has column e with values 1, 2, 3 When running the following SQL... select * from E left join C on e = c right join B on e = b; we got the following results: +--+--+--+ | e| c| b| +--+--+--+ |1 |1 |1 | |2 | NULL |1 | |3 | NULL |1 | |1 | NULL |2 | |2 |2 |2 | |3 | NULL |2 | |1 | NULL |3 | |2 | NULL |3 | |3 |3 |3 | |1 | NULL |4 | |2 | NULL |4 | |3 | NULL |4 | +--+--+--+ 12 rows in set (0.00 sec) Common sense expects the number of rows returned to be four. So where's the Cartesian coming from? - ian -- | Ian Sales Broadband Solutions for | | Database AdministratorHome Business | | PlusNet plc http://www.plus.net | + --- PlusNet - The Smarter Way to Internet + -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
optimizing InnoDB tables
The documentation is not clear on this point. Here is a quote: 'For BDB tables, OPTIMIZE TABLE is currently mapped to ANALYZE TABLE. It was also the case for InnoDB tables before MySQL 4.1.3; starting from this version it is mapped to ALTER TABLE.' What is meant by its being mapped to ALTER TABLE? Too, what exactly happens after 4.1.3? Is space, in fact, recovered and defragged? Thanks for your time! Best Regards, Boyd E. Hemphill MySQL Certified Professional [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2278 M: (713) 252-4688 -Original Message- From: Christopher L. Everett [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 06, 2004 6:23 PM To: 'Mysql List' Subject: Re: Repeated corruption with MySQL 4.1.x using FULLTEXT indexes Ed Lazor wrote: -Original Message- From: Christopher L. Everett [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 06, 2004 1:47 AM To: Mysql List Subject: Repeated corruption with MySQL 4.1.x using FULLTEXT indexes I have an application where I create a faily large table (835MB) with a fulltext index. One of our development workstations and our production server will run the script to load the table, but afterwards we have a pervasive corruption, with out of range index index pointer errors. Oddly, my development workstation doesn't have those problems. My box and the ones having the problems have the following differences: - my box runs ReiserFS, the problem boxes run XFS - my box has a nice SCSI HD subsystem, the problem boxes do IDE. All three boxes run Linux 2.6.x kernels, and my workstation and production server share the same mobo. Come to think of it, I saw similar corruption issues under 2.4.x series kernels and MySQL v4.0.x, it just wasn't the show stopper it is now. Also, on all three boxes, altering the table to drop an index and create a new one requires a myisamchk -rq run afterwards when a fulltext index either exists or gets added or dropped, which I'd also call a bug. The problems you're describing are similar to what I've run into when there have been hardware related problems. One system had a problem with ram. Memory tests would test and report ram as ok, but everything started working when I replaced the ram. I think it was just brand incompatibility or something odd, because the ram never gave any problems in another system. I can generate the problem on much smaller data sets, in the mid tens of thousands of records rather than the millions of records. I'll do a memtest86 run on the development boxes overnight, but as I did that just after I installed linux on them and used the linux badram patch to exclude iffy sections of RAM, I don't think thats a problem. One system had hard drive media slowly failing and this wasn't obvious until we ran several full scan chkdsks. 3 hard drives all of different brand, model size, and the problem happening in the same place on both? Not likely. The funniest situation was where enough dust had collected in the CPU fan to cause slight over heating, which resulted in oddball errors. This isn't a problem on my box. I have a 1.5 pound copper heatsink with a 90mm heat sensitive fan and a fan+heatsink for the hard drive, and I saw myisamchk consistently generate the same error in the same place over and over. The sensors report my CPU running in the 45 degree centigrade range on my box pretty consistently. In each of these cases, everything would work fine until the system would start processing larger amounts of data. Small amounts of corruption began to show up that seemed to build on itself. This may or may not relate to what you're dealing with, but maybe it will help =) I'll look, but I don't think that's the problem. I'm going to see how small of a data set will cause this problem and file a bug report. -- Christopher L. Everett Chief Technology Officer www.medbanner.com MedBanner, Inc. www.physemp.com -- 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]
how can this query be optimized?
hello, i was wondering if anyone could help me to optimize a query i use when gathering search results? this is easily the most complicated query i've written (and likely a walk in the park for most of you) and because of this, i'm afraid it's a bit slow. SELECT COUNT(p.id) FROM products AS p LEFT JOIN products_categories AS pc ON pc.prodid = p.id OR pc.prod_sequential_id = p.sequential_id LEFT JOIN products_masids AS pmas ON pmas.prodid = p.id OR pmas.prod_sequential_id = p.sequential_id LEFT JOIN products_media AS pmed ON (pmed.prodid = p.id OR pmed.prod_sequential_id = p.sequential_id) AND pmed.type = 0 WHERE pc.plft = 17 AND pc.prgt = 174 AND p.is_active = 1 GROUP BY p.id the above query is the first query i initially run to get a count of 'total products found'. the purpose being so that i can determine the number of pages to display. i am using 'modified preorder tree traversal' (http://www.sitepoint.com/article/hierarchical-data-database/2) for classifying my products into categories. also, my mysql version is 3.23.54. i've pretty much learned all i know about databases by messing around with them and using the bits and pieces i read on the internet. in other words, i don't know what important information i may or may not be leaving out of this post. please let me know what else is required for an evaluation of my query to be made, if indeed something is needed. oh... what i *could* say is that the hardware i'm using for this box is not too shabby (not awesome either) and for this exact query it returns 586 records in 2.03 seconds. i have 733 products total in my database. thank you, chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Storing SQL in a column? - looking for advice
Greetings, The project I'm working on involves extracting data from one database and storing it in another. With the goal of creating a generic mechanism (perl script) to perform the extraction on a periodic basis, I set up a table mapping destination table and column to the source of the value. In the case of a simple copy, the source table and column name are stored. However, some of the values for the target fields need to be derived from either multiple columns or aggregated from multiple records (like MB/entity/day, with records having timestamps that are scattered throughout the day). I was contemplating storing the queries in a column, which would be read and then executed by the script, but wanted to see if anyone had any recommendations for a better approach. Thanks, Ted -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how can this query be optimized?
You set up your situation very well but for one small item. Please allow me to kindly introduce you to the EXPLAIN command http://dev.mysql.com/doc/mysql/en/EXPLAIN.html Post the results from using that on your query and we will be good to go :-) Shawn Green Database Administrator Unimin Corporation - Spruce Pine Chris W. Parker [EMAIL PROTECTED] wrote on 10/07/2004 03:08:47 PM: hello, i was wondering if anyone could help me to optimize a query i use when gathering search results? this is easily the most complicated query i've written (and likely a walk in the park for most of you) and because of this, i'm afraid it's a bit slow. SELECT COUNT(p.id) FROM products AS p LEFT JOIN products_categories AS pc ON pc.prodid = p.id OR pc.prod_sequential_id = p.sequential_id LEFT JOIN products_masids AS pmas ON pmas.prodid = p.id OR pmas.prod_sequential_id = p.sequential_id LEFT JOIN products_media AS pmed ON (pmed.prodid = p.id OR pmed.prod_sequential_id = p.sequential_id) AND pmed.type = 0 WHERE pc.plft = 17 AND pc.prgt = 174 AND p.is_active = 1 GROUP BY p.id the above query is the first query i initially run to get a count of 'total products found'. the purpose being so that i can determine the number of pages to display. i am using 'modified preorder tree traversal' (http://www.sitepoint.com/article/hierarchical-data-database/2) for classifying my products into categories. also, my mysql version is 3.23.54. i've pretty much learned all i know about databases by messing around with them and using the bits and pieces i read on the internet. in other words, i don't know what important information i may or may not be leaving out of this post. please let me know what else is required for an evaluation of my query to be made, if indeed something is needed. oh... what i *could* say is that the hardware i'm using for this box is not too shabby (not awesome either) and for this exact query it returns 586 records in 2.03 seconds. i have 733 products total in my database. thank you, chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how can this query be optimized?
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] on Thursday, October 07, 2004 12:30 PM said: You set up your situation very well but for one small item. Please allow me to kindly introduce you to the EXPLAIN command http://dev.mysql.com/doc/mysql/en/EXPLAIN.html hey! that looks like it might come in useful. :) i didn't know about this. Post the results from using that on your query and we will be good to go :-) here you are: +---++---+--+-+--+--+--- --+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+--+-+--+--+--- --+ | pmed | system | NULL | NULL |NULL | NULL |0 | const row not found | | p | ALL| NULL | NULL |NULL | NULL | 733 | where used | | pc| ALL| NULL | NULL |NULL | NULL | 753 | where used | | pmas | ALL| NULL | NULL |NULL | NULL | 1410 | | +---++---+--+-+--+--+--- --+ 4 rows in set (0.00 sec) thanks, chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how can this query be optimized?
Can you see where the column possible_keys is NULL for every table? That means that there are NO (none, zilch, nada, zero) indexes that can be used to save your database engine from the trouble of doing a full table scan for EACH AND EVERY MATCH in your query. I don't even see where you defined any PRIMARY KEYS on any of your tables. (May I suggest you hit web and do a little homework on indexes and primary keys?) Try adding these indexes then let me know how your query performs: alter table products add key(id), add key(sequential_id); alter table products_categories add key(prodid, plft, prgt), add key(prod_sequential_id); alter table products_masids add key(prodid), add key(prod_sequential_id); alter table products_media add key(prodid), add key(prod_sequential_id); Shawn Green Database Administrator Unimin Corporation - Spruce Pine Chris W. Parker [EMAIL PROTECTED] wrote on 10/07/2004 04:09:22 PM: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] on Thursday, October 07, 2004 12:30 PM said: You set up your situation very well but for one small item. Please allow me to kindly introduce you to the EXPLAIN command http://dev.mysql.com/doc/mysql/en/EXPLAIN.html hey! that looks like it might come in useful. :) i didn't know about this. Post the results from using that on your query and we will be good to go :-) here you are: +---++---+--+-+--+--+--- --+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+--+-+--+--+--- --+ | pmed | system | NULL | NULL |NULL | NULL |0 | const row not found | | p | ALL| NULL | NULL |NULL | NULL | 733 | where used | | pc| ALL| NULL | NULL |NULL | NULL | 753 | where used | | pmas | ALL| NULL | NULL |NULL | NULL | 1410 | | +---++---+--+-+--+--+--- --+ 4 rows in set (0.00 sec) thanks, chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL v ASP problem
I have searched high and low and I know that you guys can help out ( as you
have helped me before ).
First, I will list table descriptions, then the problem, then I will list
the code, and finally the permissions.
The Descriptions:
2 tables - both MyISAM.
Table 1 has 9 fields, 1 index PID (PK, Index);
Table 2 has 36 fields, 1 index EcnID (PK, index). PID is a FK in
Table 2;
ASP and IIS 6 on a Win2k3 server
MySQL 4.0.20 on a Linux RHEL AS
The Problem:
I am trying to do an rs.update using ASP. In Table 1, code works
perfectly, retrieves and updates without issue. In Table 2, same code,
doesn't work. NOTE: I have to use rs.update and not UPDATE tablename SET
... due to the large amount of data that needs to be pushed. I get the old
Query-based update failed because the row to update could not be found.
So here we go with the code:
BEGIN NECESSARY CODE
*
%
Dim Conn
Conn = DRIVER={MySQL ODBC 3.51 Driver}; SERVER=IPADDRESS;_
'MyODBC driver is 3.51.9
DATABASE=DBNAME; UID=UID;PWD=PWD; OPTION=3
'
dim resdata(36), resflds(36), reschks(36), resnote(36), resfrmt(36), errtext
Set rs = Server.CreateObject(ADODB.Recordset)
'
if request.querystring(EcnID) then
session(EcnID)=request.querystring(EcnID)
** CODE JUMP **
'
if request.form(B1)=Exit Without Changes then
response.redirect(SOMEOTHERPAGE.ASP) 'Handle bail-out
'
if request.form(B1)=COMPLETE REVISION then
' START HERE TO STORE UPDATES
'
vararray=session(resdata)
'retrieve the session data provided by
vararra1=session(resflds)
'database query
vararra2=session(reschks)
vararra3=session(resnote)
'
for x=0 to ubound(vararray)
'Parse the session data into usable arrays
resdata(x)=vararray(x)
resflds(x)=vararra1(x)
reschks(x)=vararra2(x)
resnote(x)=vararra3(x)
next 'x
'
sql = Select * from tblEncounter where EcnID= session(EcnID)
response.write sql
response.flush
rs.Open sql, conn,3,3
'
For Each objItem in request.form
'look at form field
for x=0 to ubound(resflds)
'search all the field name array
if ucase(objitem)= ucase(resflds(x)) then
'update the data
resdata(x)=request.form(objitem)
'resdata array now contains newest data
end if
next 'x
next 'objitem
rs(Compdate)=now()
'
rs.update
response.redirect(SOMEOTHERPAGE.ASP)
end if
'
if request.form(B1)=Update Information then
' START HERE TO DO UPDATE / ERROR CHECK
'
vararray=session(resdata)
'retrieve the session data provided by the
vararra1=session(resflds)
'database query
vararra2=session(reschks)
vararra3=session(resnote)
'
for x=0 to ubound(vararray)
'Parse the session data into usable arrays
resdata(x)=vararray(x)
resflds(x)=vararra1(x)
reschks(x)=vararra2(x)
resnote(x)=vararra3(x)
next 'x
'
CODE JUMP *
Else
' START HERE FOR NEW DATA PULL-UP
'
dim resname
dim rs
dim sql
sql = Select * from tblEncounter where EcnID=session(EcnID)
rs.Open sql, conn
rs.MoveFirst
'
x=0
for each fld in rs.Fields
'Load RS into an session array
resflds(x) = fld.name
'Load field names from database
resdata(x) = fld.value
'Initialize the fields to null
reschks(x) = 1
'set field status to good
resnote(x) =
'set field comment to null
x=x+1
next
'
rs.close
'
session(resdata)=resdata
session(resflds)=resflds
session(reschks)=reschks
session(resnote)=resnote
end if
'
%
*** END NECESSARY CODE **
Now, permissions:
MySQL - FULL CONTROL FOR THIS DATABASE ( ALL PRIVLIGES WITH GRANT OPTION )
IUSR - Read, Read Execute, Write, Modify, List Folder Entries
Please, if any help can be offered I would greatly appreciate it. If you
need anything else from me, please don't hesitate to ask!
TIA
J.R.
smime.p7s
Description: S/MIME cryptographic signature
shoud this query fail?
Consider these three queries, the first fails with an error, the second succeeds and third also succeeds. The only difference the set of records available in the database to match the query. In the case that fails, the picture record exists but the batchflow record does not exist. In fact, NO batch flow records exist. In the case the succeeds, the picture record doesn't exist. In the second case that succeeds, a NON MATCHING batch flow record exists. Isn't (or shouldn't it be) a bug for an update to fail in this way, caused only by the content of the database? F:\tempmysql --version mysql Ver 12.22 Distrib 4.0.20a, for Win95/Win98 (i32) # fails. mysql UPDATE picture LEFT JOIN batchflow - ON batchflow.batch=picture.batch AND batchflow.number=picture.number - SET batchflow.needs_reflow=1,picture.batch='foo' - WHERE uid='124514'; ERROR 1032: Can't find record in 'batchflow' # specify a nonmatching picture record, all is ok mysql UPDATE picture LEFT JOIN batchflow - ON batchflow.batch=picture.batch AND batchflow.number=picture.number - SET batchflow.needs_reflow=1,picture.batch='foo' - WHERE uid='101080'; Query OK, 0 rows affected (0.02 sec) Rows matched: 0 Changed: 0 Warnings: 0 # add a nonmatching batchflow record, all is ok mysql insert into batchflow set number='30950-2005',batch='none'; Query OK, 1 row affected (0.00 sec) mysql UPDATE picture LEFT JOIN batchflow - ON batchflow.batch=picture.batch AND batchflow.number=picture.number - SET batchflow.needs_reflow=1,picture.batch='foo' - WHERE uid='124514'; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 # verify that removing the added batchflow record reinstates the error mysql delete from batchflow; Query OK, 1 row affected (0.00 sec) mysql UPDATE picture LEFT JOIN batchflow - ON batchflow.batch=picture.batch AND batchflow.number=picture.number - SET batchflow.needs_reflow=1,picture.batch='foo' - WHERE uid='124514'; ERROR 1032: Can't find record in 'batchflow' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how can this query be optimized?
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] on Thursday, October 07, 2004 1:34 PM said: Can you see where the column possible_keys is NULL for every table? That means that there are NO (none, zilch, nada, zero) indexes that can be used to save your database engine from the trouble of doing a full table scan for EACH AND EVERY MATCH in your query. I don't even see where you defined any PRIMARY KEYS on any of your tables. that's weird because i've definitely defined PRIMARY KEYS. like i know i had a PK on products.id... but admittedly i don't really know too much about indexes or keys (primary, or foreign). (May I suggest you hit web and do a little homework on indexes and primary keys?) you certainly may. :) might you have any specific links for me to look at? Try adding these indexes then let me know how your query performs: i performed your suggested operations and the query runs at the same speed. it nows reports at 2.22. i did read in the link you gave me in the your first post about the ANALYZE table; query so i'll try doing that right now and then see how if it changes. after doing the ANALYZE TABLE table; query on a few tables the time is now down to 2.07. (i think i made a mistake in my original time of 2.03... maybe it was 2.23. i know for sure it was 2 seconds and *something*.) thanks, chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ResultSet NotUpdatabelProblem
Its my impression that prepared statements are buggy with innodb tables. i've recently filed a bug, at heikki's request, where some buffer on the mysql server periodically flushes itself or otherwise is erased, with the result that the sql executed by a prepared statement is not what you think it is. i'm eagerly waiting fixes for this myself. jeff Mark Matthews wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 [EMAIL PROTECTED] wrote: Hello: I have recently posted the message attached at the bottom of this one to the mailing list. Since then, I have continued to work the sporadic and troublesome errors that are described in that attached message on otherwise perfectly working and proven code. I now have some insights that I would like to share with the group and solicit their thoughts and ideas as to what the root cause(s) may be. [snip] Todd, Would you mind filing a bug report with a testcase at http://bugs.mysql.com/ ? This issue would get the proper attention it needs if you use that 'channel'. Thanks! -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com MySQL Guide to Lower TCO http://www.mysql.com/it-resources/white-papers/tco.php -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBW7BFtvXNTca6JD8RAhP0AKDE4i8+lj5CCFGitdo41mW/U1t3tgCeMTII 7/QoWU8myY2J1FZFQoBRX9E= =8Mac -END PGP SIGNATURE- -- Jeff Mathis, Ph.D. 505-955-1434 Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple Databases or One?
I'm working on several websites that will be driven primarily by two databases - Geography and Animals. The Geography database will feature information about nations, provinces and states, such as capitals, population, etc. The Animals database features lots of taxonomic tables (orders, families, species, etc.), along with information about diet, distribution, etc. I would guess each database could ultimately have as many as two dozen tables or more. Some of my sites will need a third database (or extra tables in one of the existing databases). For example, I'm working on a big Symbols database table. Anyway, I thought I was getting to the point where I'd better split all of my tables into two or more databases to help me keep organized. Then I realized that it could be a lot of trouble figuring out how to connect to and manipulate two or three databases. In the long run, it might be easier to just dump everything into one big database. It occurred to me that as I learn more about MySQL, there may be database-wide operations I'll want to perform on all my Animals tables, but not on my Geography tables. If I do put everything in one table, is there some naming scheme I could use to facilitate this? In other words, if all my Animals tables feature the same prefix or suffix, would it help me perform operations that affect only the Animals tables? Thanks. __ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Resetting the password error
While trying to login to the server I got the following errors: Microsoft Windows XP [Version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp. C:\c:\mysql\bin\mysql -root -p Enter password: * ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) C:\c:\mysql\bin\mysql -root -p Enter password: ** ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) C:\c:\mysql\bin\mysqlshow c:\mysql\bin\mysqlshow: Access denied for user: '[EMAIL PROTECTED]' (Using password : NO) C:\c:\mysql\bin\mysqladmin -u root password my-password-here c:\mysql\bin\mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)' C:\c:\mysql\bin\mysqladmin -u root -h fayec password my-password-here c:\mysql\bin\mysqladmin: connect to server at 'fayec' failed error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)' Then I started the service with: C:\c:\mysql\bin\mysqld-nt --skip-grant-tables I am now trying to reset my root password but I am getting errors when trying to do so. I followed the instructions on: http://dev.mysql.com/doc/mysql/en/Resetting_permissions.html but when I get to the point I have to use: flush-privileges password newpwd I get the following error: 'flush-privileges' is not recognized as an internal or external command, operable program or batch file. I have no clue how to go on from hereanybody?? I am trying to login to be able to create a db but it won't let me through without a password and it won't accept the password I had set before (pretty sure about the old password as it is the same I use for all my local projects). Any tips will be extremely appreciated. Thanks in advance. FayeC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Resetting the password error
Michael FayeC SQL wrote: While trying to login to the server I got the following errors: Microsoft Windows XP [Version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp. C:\c:\mysql\bin\mysql -root -p Enter password: * ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) C:\c:\mysql\bin\mysql -root -p Enter password: ** ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) -root doesn't mean anything, so this is trying to log in as the default user, [EMAIL PROTECTED] What you need is C:\c:\mysql\bin\mysql -u root -p C:\c:\mysql\bin\mysqlshow c:\mysql\bin\mysqlshow: Access denied for user: '[EMAIL PROTECTED]' (Using password : NO) Same here. Should be C:\c:\mysql\bin\mysqlshow -u root -p C:\c:\mysql\bin\mysqladmin -u root password my-password-here c:\mysql\bin\mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)' [EMAIL PROTECTED] already has a password, so you can't change it without authenticating. Try C:\c:\mysql\bin\mysqladmin -u root -p password my-password-here C:\c:\mysql\bin\mysqladmin -u root -h fayec password my-password-here c:\mysql\bin\mysqladmin: connect to server at 'fayec' failed error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)' Here you change your connection method, but you still didn't give the password. You need -p (and [EMAIL PROTECTED] would have to be an authorized user). Then I started the service with: C:\c:\mysql\bin\mysqld-nt --skip-grant-tables I am now trying to reset my root password but I am getting errors when trying to do so. I followed the instructions on: http://dev.mysql.com/doc/mysql/en/Resetting_permissions.html but when I get to the point I have to use: flush-privileges password newpwd I get the following error: 'flush-privileges' is not recognized as an internal or external command, operable program or batch file. I believe you've been bitten by bad formatting in the manual. The command should be C:\mysql\bin\mysqladmin -u root flush-privileges password newpwd I have no clue how to go on from hereanybody?? I am trying to login to be able to create a db but it won't let me through without a password and it won't accept the password I had set before (pretty sure about the old password as it is the same I use for all my local projects). Any tips will be extremely appreciated. Thanks in advance. FayeC Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL v ASP problem
J.R.,
The difference in the way the two statements function is in your code. In the first
example, you set the cursor type (rs.Open sql, conn,3,3) as updatable. In the second
example, you set the cursor to the default type (non-updatable: rs.Open sql, conn).
I haven't used ASP extensively recently, but it seems likely that if you add the
,3,3 to the second rs.Open statement your app will behave properly.
Randy Clamons
Systems Programming
Astro-auction.com
Original Message
From: J.R. Bullington [EMAIL PROTECTED]
To: [EMAIL PROTECTED], [EMAIL PROTECTED]
Date: Thu, Oct-7-2004 1:45 PM
Subject: MySQL v ASP problem
I have searched high and low and I know that you guys can help out ( as
you
have helped me before ).
First, I will list table descriptions, then the problem, then I will
list
the code, and finally the permissions.
The Descriptions:
2 tables - both MyISAM.
Table 1 has 9 fields, 1 index PID (PK, Index);
Table 2 has 36 fields, 1 index EcnID (PK, index). PID is a FK in
Table 2;
ASP and IIS 6 on a Win2k3 server
MySQL 4.0.20 on a Linux RHEL AS
The Problem:
I am trying to do an rs.update using ASP. In Table 1, code works
perfectly, retrieves and updates without issue. In Table 2, same
code,
doesn't work. NOTE: I have to use rs.update and not UPDATE tablename
SET
... due to the large amount of data that needs to be pushed. I get the
old
Query-based update failed because the row to update could not be
found.
So here we go with the code:
BEGIN NECESSARY CODE
*
%
Dim Conn
Conn = DRIVER={MySQL ODBC 3.51 Driver}; SERVER=IPADDRESS;_
'MyODBC driver is 3.51.9
DATABASE=DBNAME; UID=UID;PWD=PWD; OPTION=3
'
dim resdata(36), resflds(36), reschks(36), resnote(36), resfrmt(36),
errtext
Set rs = Server.CreateObject(ADODB.Recordset)
'
if request.querystring(EcnID) then
session(EcnID)=request.querystring(EcnID)
** CODE JUMP **
'
if request.form(B1)=Exit Without Changes then
response.redirect(SOMEOTHERPAGE.ASP)'Handle bail-out
'
if request.form(B1)=COMPLETE REVISION then
' START HERE TO STORE UPDATES
'
vararray=session(resdata)
'retrieve the session data provided by
vararra1=session(resflds)
'database query
vararra2=session(reschks)
vararra3=session(resnote)
'
for x=0 to ubound(vararray)
'Parse the session data into usable arrays
resdata(x)=vararray(x)
resflds(x)=vararra1(x)
reschks(x)=vararra2(x)
resnote(x)=vararra3(x)
next 'x
'
sql = Select * from tblEncounter where EcnID= session(EcnID)
response.write sql
response.flush
rs.Open sql, conn,3,3
'
For Each objItem in request.form
'look at form field
for x=0 to ubound(resflds)
'search all the field name array
if ucase(objitem)= ucase(resflds(x)) then
'update the data
resdata(x)=request.form(objitem)
'resdata array now contains newest data
end if
next 'x
next 'objitem
rs(Compdate)=now()
'
rs.update
response.redirect(SOMEOTHERPAGE.ASP)
end if
'
if request.form(B1)=Update Information then
' START HERE TO DO UPDATE / ERROR CHECK
'
vararray=session(resdata)
'retrieve the session data provided by the
vararra1=session(resflds)
'database query
vararra2=session(reschks)
vararra3=session(resnote)
'
for x=0 to ubound(vararray)
'Parse the session data into usable arrays
resdata(x)=vararray(x)
resflds(x)=vararra1(x)
reschks(x)=vararra2(x)
resnote(x)=vararra3(x)
next 'x
'
CODE JUMP *
Else
' START HERE FOR NEW DATA PULL-UP
'
dim resname
dim rs
dim sql
sql = Select * from tblEncounter where EcnID=session(EcnID)
rs.Open sql, conn
rs.MoveFirst
'
x=0
for each fld in rs.Fields
'Load RS into an session array
resflds(x) = fld.name
'Load field names from database
resdata(x) = fld.value
'Initialize the fields to null
reschks(x) = 1
'set field status to good
resnote(x) =
'set field comment to null
x=x+1
next
'
rs.close
'
session(resdata)=resdata
session(resflds)=resflds
session(reschks)=reschks
session(resnote)=resnote
end if
'
%
*** END NECESSARY CODE **
Now, permissions:
MySQL - FULL CONTROL FOR THIS DATABASE ( ALL PRIVLIGES WITH GRANT
OPTION )
IUSR - Read, Read Execute, Write, Modify, List Folder Entries
Please, if any help can be offered I would greatly appreciate it. If
you
need anything else from me, please don't hesitate to ask!
TIA
J.R.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delete duplicate entry
gerald_clark wrote:
Batara Kesuma wrote:
Hi,
I have a table that looks like:
CREATE TABLE `message_inbox` (
`member_id` mediumint(8) unsigned NOT NULL default '0',
`message_id` int(10) unsigned NOT NULL default '0',
`new` enum('y','n','replied') NOT NULL default 'y',
`datetime` datetime default NULL,
KEY `idx_1` (`member_id`,`new`),
KEY `idx_2` (`member_id`,`datetime`)
) TYPE=InnoDB
Now, I want to add a primary key to it.
ALTER TABLE message_inbox ADD PRIMARY KEY (member_id, message_id)
But there are already some duplicated entries. ERROR 1062: Duplicate
entry '10244-80871' for key 1
How can I tell MySQL to delete the duplicated entry and continue to
make primary key? Is there any efficient way to do this? Thank you
very much.
try ALTER IGNORE TABLE.
That hardly seems like a solution. If the above works, then I'd call
that 'feature' a bug.
You need to remove the duplicates from your table before creating a
primary key, otherwise what are you creating the key for in the first place?
Create a query that finds the duplicates. Choose the ones you want to
delete, and delete them manually.
In your particular case, as you're trying to put a key across 2 columns,
you really do have a problem. I'd *usually* suggest something like:
select sum(1) as number_of_duplicates, my_key_field
from my_table
group by my_key_field
having sum(1)1
However this won't work if your key is going to go across more than one
field. I suppose you could concat() the fields together. It's not
exactly the perfect solution, but it sounds like you don't exactly have
perfect data to start with, and since you're doing this manually, you
can deal with it. Try something like:
select sum(1) as number_of_duplicates, concat(member_id, '___',
message_id) as my_problem
from message_inbox
group by concat(member_id, '___', message_id)
having sum(1)1
Have fun.
Dan
--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Databases in Subdirectories?
Is it possible to create a database in a lower-level subdirectory of MySQL's data directory? We have almost 100,000 sites, and we would like to have a separate database for each site. However, it's very impractical from a filesystem maintenance standpoint to have 100,000 subdirectories of MySQL's data directory. What we would like to do is break up the directories into something like this: for site #12345: [mysql datadir]/01/23/45/[databasename] This would greatly improve the manageability of the table space. Is this possible? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delete duplicate entry
Hi,
I think the query has to be considering the count rather than the sum.
the query can be like this:
select pkfield1[,pkfield2[,pkfield3[,]]], count(1) from your_table
group by pkfield1[,pkfield2[,pkfield3[,]]]
having count(1) 1
Here u can add n-number of feilds which u want make PK.
Reg,
Eldo.
On Fri, 08 Oct 2004 09:37:11 +1000, Daniel Kasak
[EMAIL PROTECTED] wrote:
gerald_clark wrote:
Batara Kesuma wrote:
Hi,
I have a table that looks like:
CREATE TABLE `message_inbox` (
`member_id` mediumint(8) unsigned NOT NULL default '0',
`message_id` int(10) unsigned NOT NULL default '0',
`new` enum('y','n','replied') NOT NULL default 'y',
`datetime` datetime default NULL,
KEY `idx_1` (`member_id`,`new`),
KEY `idx_2` (`member_id`,`datetime`)
) TYPE=InnoDB
Now, I want to add a primary key to it.
ALTER TABLE message_inbox ADD PRIMARY KEY (member_id, message_id)
But there are already some duplicated entries. ERROR 1062: Duplicate
entry '10244-80871' for key 1
How can I tell MySQL to delete the duplicated entry and continue to
make primary key? Is there any efficient way to do this? Thank you
very much.
try ALTER IGNORE TABLE.
That hardly seems like a solution. If the above works, then I'd call
that 'feature' a bug.
You need to remove the duplicates from your table before creating a
primary key, otherwise what are you creating the key for in the first place?
Create a query that finds the duplicates. Choose the ones you want to
delete, and delete them manually.
In your particular case, as you're trying to put a key across 2 columns,
you really do have a problem. I'd *usually* suggest something like:
select sum(1) as number_of_duplicates, my_key_field
from my_table
group by my_key_field
having sum(1)1
However this won't work if your key is going to go across more than one
field. I suppose you could concat() the fields together. It's not
exactly the perfect solution, but it sounds like you don't exactly have
perfect data to start with, and since you're doing this manually, you
can deal with it. Try something like:
select sum(1) as number_of_duplicates, concat(member_id, '___',
message_id) as my_problem
from message_inbox
group by concat(member_id, '___', message_id)
having sum(1)1
Have fun.
Dan
--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Thanks Regards,
Eldo Skaria
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A small, very small sucess story
I know this is off topic, but I have been a lurker for awhile, quietly listening or reading, and getting to learn more and more about this wonderful tool. I am a true mysql newbie, in fact a database newbie. However I decided I wanted to learn something new and differnet then my normal job as a Windows network administrator. So I started messing around with MySQL. This is basically just a thank you note to everyone for letting me learn from thier mistakes and thier questions on the list. I have installed the database on a server and am using it for some very simple tracking of network objects, patches and host/ip. So a big thank you to group, I know a lot of you don't get paid directly for answers to the list... so go buy yourself a beer and give yourself a pat on the bank Thanks for letting me lurk and learn, Jonathan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delete duplicate entry
Eldo Skaria wrote: Hi, I think the query has to be considering the count rather than the sum. the query can be like this: select pkfield1[,pkfield2[,pkfield3[,]]], count(1) from your_table group by pkfield1[,pkfield2[,pkfield3[,]]] having count(1) 1 Here u can add n-number of feilds which u want make PK. Reg, Eldo. count(1) is the same as sum(1) After thinking more about the 'group by' clause, I think I was wrong to start with. You don't have to concat() the fields, and can use the above clause. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
user variables and regexp
User variables do not work with REGEXP under MySQL 4.0.21 4.1.5. Is this a bug or a feature? -- ./ premax ./ [EMAIL PROTECTED] ./ koniec i bomba, a kto czytal ten traba. w.g. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: user variables and regexp
At 3:12 +0200 10/8/04, Przemyslaw Popielarski wrote: User variables do not work with REGEXP under MySQL 4.0.21 4.1.5. Is this a bug or a feature? It's difficult to provide an answer to this because you're providing no information about what do not work means. Can you be more specific? mysql set @x = 'abc'; Query OK, 0 rows affected (0.13 sec) mysql select @x like 'a%'; +--+ | @x like 'a%' | +--+ |1 | +--+ 1 row in set (0.08 sec) mysql select @x like 'b%'; +--+ | @x like 'b%' | +--+ |0 | +--+ 1 row in set (0.01 sec) -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: user variables and regexp
Paul DuBois [EMAIL PROTECTED] wrote: User variables do not work with REGEXP under MySQL 4.0.21 4.1.5. Is this a bug or a feature? It's difficult to provide an answer to this because you're providing no information about what do not work means. Can you be more specific? Sure. I didn't want to write to not mess in case this is a feature. So here goes my test case: SELECT @a:=FIRMLEGALZIPCODE FROM tCustomers WHERE @a REGEXP [0-9] - Empty set (0.03 sec) SELECT @a:=FIRMLEGALZIPCODE FROM tCustomers WHERE FIRMLEGALZIPCODE REGEXP [0-9]; - 2803 rows in set (0.03 sec) -- ./ premax ./ [EMAIL PROTECTED] ./ koniec i bomba, a kto czytal ten traba. w.g. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: user variables and regexp
At 3:47 +0200 10/8/04, Przemyslaw Popielarski wrote: Paul DuBois [EMAIL PROTECTED] wrote: User variables do not work with REGEXP under MySQL 4.0.21 4.1.5. Is this a bug or a feature? It's difficult to provide an answer to this because you're providing no information about what do not work means. Can you be more specific? Sure. I didn't want to write to not mess in case this is a feature. So here goes my test case: SELECT @a:=FIRMLEGALZIPCODE FROM tCustomers WHERE @a REGEXP [0-9] - Empty set (0.03 sec) You're expecting the value to be selected first so that you then can test it with the WHERE clause later. SELECT @a:=FIRMLEGALZIPCODE FROM tCustomers WHERE FIRMLEGALZIPCODE REGEXP [0-9]; - 2803 rows in set (0.03 sec) -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: user variables and regexp
Paul DuBois [EMAIL PROTECTED] wrote: SELECT @a:=FIRMLEGALZIPCODE FROM tCustomers WHERE @a REGEXP [0-9] - Empty set (0.03 sec) You're expecting the value to be selected first so that you then can test it with the WHERE clause later. Of course you're right. Thanks. According to your suggestion this one works okey: SELECT @a FROM tCustomers WHERE @a:=FIRMLEGALZIPCODE REGEXP [0-9] -- ./ premax ./ [EMAIL PROTECTED] ./ koniec i bomba, a kto czytal ten traba. w.g. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Comparing Tables
I know that it is possible to do this, though I don't know how. I have 2 tables containing information about trouble tickets. One of the tables (table2) contains information about every ticket ever received, including tickets that were deleted from the system. For this reason, table2 has ~450,000 records where table1, which holds the good tickets, has only ~4500. I would like to clear the useless data out of table2, but don't want to run 450,000 queries to compare them one row at a time. The two tables share a common id, though one is referred to as tickid while the other is referred to as ticketidchar. Is there an easy way, using just a few queries, to select all the rows in table2 where ticketidchar does not exist in table1? Thank You, Jason
Re: user variables and regexp
At 4:01 +0200 10/8/04, Przemyslaw Popielarski wrote: Paul DuBois [EMAIL PROTECTED] wrote: SELECT @a:=FIRMLEGALZIPCODE FROM tCustomers WHERE @a REGEXP [0-9] - Empty set (0.03 sec) You're expecting the value to be selected first so that you then can test it with the WHERE clause later. Of course you're right. Thanks. According to your suggestion this one works okey: SELECT @a FROM tCustomers WHERE @a:=FIRMLEGALZIPCODE REGEXP [0-9] True, although in this case you don't need a user variable at all: SELECT FIRMLEGALZIPCODE FROM tCustomers WHERE FIRMLEGALZIPCODE REGEXP [0-9] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
data with dynamic schema stored in a column as a property list.
Hi, instead of xml, i stored arbitrary data of the form
(the actual usage of such mechanism is for more fancy stuff,
say, dynamic configuration, otherwise this is really not necessary)
{
name = Fn, Ln; // string value
gender = F; // single word string
interests = (reading,drive fast); // array
children = (
{ lastName = Howe; firstName = Sam; gender = M; dob = 1994-10-07 16:59:26; },
{ lastName = Howe; firstName = Ann; gender = F; dob = 1998-01-26 04:09:12; }
);
creditCards = {
visa = XXX-x;
master = YY-;
};
}
This is called plist and the depth of the hierarchy can go arbitrary deep (unknown
limit). And it can be converted back
and forth from dictionary object by a framework.
My task is to find out ways of querying a column holds such text data? say, find out
whether there is certain key or
whether a key has certain value. I got some solution via regular expression feature of
MySQL.
The column type that I use is text. My question now is how to make the whole thing
perform good. In other words,
for regular expression querying, should I index the column for performance? If so,
what kind of index should I use?
Thanks a lot.
Re: Comparing Tables
On Thu, 7 Oct 2004 19:22:23 -0700 Jason Williard [EMAIL PROTECTED] wrote: I know that it is possible to do this, though I don't know how. I have 2 tables containing information about trouble tickets. One of the tables (table2) contains information about every ticket ever received, including tickets that were deleted from the system. For this reason, table2 has ~450,000 records where table1, which holds the good tickets, has only ~4500. I would like to clear the useless data out of table2, but don't want to run 450,000 queries to compare them one row at a time. The two tables share a common id, though one is referred to as tickid while the other is referred to as ticketidchar. Is there an easy way, using just a few queries, to select all the rows in table2 where ticketidchar does not exist in table1? SELECT table2.id LEFT JOIN table1 ON table1.id = table2.id WHERE table1.id IS NULL; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
data with dynamic schema stored in a column as a property list.
Hi, instead of xml, i stored arbitrary data of the form
(the actual usage of such mechanism is for more fancy stuff,
say, dynamic configuration, otherwise this is really not necessary)
{
name = Fn, Ln; // string value
gender = F; // single word string
interests = (reading,drive fast); // array
children = (
{ lastName = Howe; firstName = Sam; gender = M; dob = 1994-10-07 16:59:26; },
{ lastName = Howe; firstName = Ann; gender = F; dob = 1998-01-26 04:09:12; }
);
creditCards = {
visa = XXX-x;
master = YY-;
};
}
This is called plist and the depth of the hierarchy can go arbitrary deep (unknown
limit). And it can be converted back
and forth from dictionary object by a framework.
My task is to find out ways of querying a column holds such text data? say, find out
whether there is certain key or
whether a key has certain value. I got some solution via regular expression feature of
MySQL.
The column type that I use is text. My question now is how to make the whole thing
perform good. In other words,
for regular expression querying, should I index the column for performance? If so,
what kind of index should I use?
Thanks a lot.
Re: Comparing Tables
Jason Williard wrote: I know that it is possible to do this, though I don't know how. I have 2 tables containing information about trouble tickets. One of the tables (table2) contains information about every ticket ever received, including tickets that were deleted from the system. For this reason, table2 has ~450,000 records where table1, which holds the good tickets, has only ~4500. I would like to clear the useless data out of table2, but don't want to run 450,000 queries to compare them one row at a time. The two tables share a common id, though one is referred to as tickid while the other is referred to as ticketidchar. Is there an easy way, using just a few queries, to select all the rows in table2 where ticketidchar does not exist in table1? Thank You, Jason It sounds like you want a multi-table delete. Assuming tickid is part of the new table, something like: DELETE table2 FROM table2 LEFT JOIN table1 ON ticketidchar = tickid WHERE tickid IS NULL; See the manual for details http://dev.mysql.com/doc/mysql/en/DELETE.html. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
2003 server problem
I am having trouble intalling versions 4.02 and 4.1 onto server 2003. The issues are; 1. my.ini is not being written 2. WinMYSQLAmin is causing the following error; Access violation at address 0040289D in module 'winmysqladmin.exe'. read of address . 3. MySQL can can only be stared once as a service. Restarts fail with the following message; Could not start the MYSQL service on Local Computer. Error 1067: The process terminated unexpectedly 4:OBDC wont connect; Client does not support authentication protocol requested by server; Any advice would be appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Databases in Subdirectories?
How does that help? The database itself should be allowed to organize everything. Ruben On Thu, Oct 07, 2004 at 04:57:39PM -0700, Justin Smith wrote: Is it possible to create a database in a lower-level subdirectory of MySQL's data directory? We have almost 100,000 sites, and we would like to have a separate database for each site. However, it's very impractical from a filesystem maintenance standpoint to have 100,000 subdirectories of MySQL's data directory. What we would like to do is break up the directories into something like this: for site #12345: [mysql datadir]/01/23/45/[databasename] This would greatly improve the manageability of the table space. Is this possible? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- __ Brooklyn Linux Solutions So many immigrant groups have swept through our town that Brooklyn, like Atlantis, reaches mythological proportions in the mind of the world - RI Safir 1998 DRM is THEFT - We are the STAKEHOLDERS - RI Safir 2002 http://fairuse.nylxs.com http://www.mrbrklyn.com - Consulting http://www.inns.net -- Happy Clients http://www.nylxs.com - Leadership Development in Free Software http://www2.mrbrklyn.com/resources - Unpublished Archive or stories and articles from around the net http://www2.mrbrklyn.com/downtown.html - See the New Downtown Brooklyn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparing Tables
see IS NOT On Thu, Oct 07, 2004 at 07:22:23PM -0700, Jason Williard wrote: I know that it is possible to do this, though I don't know how. I have 2 tables containing information about trouble tickets. One of the tables (table2) contains information about every ticket ever received, including tickets that were deleted from the system. For this reason, table2 has ~450,000 records where table1, which holds the good tickets, has only ~4500. I would like to clear the useless data out of table2, but don't want to run 450,000 queries to compare them one row at a time. The two tables share a common id, though one is referred to as tickid while the other is referred to as ticketidchar. Is there an easy way, using just a few queries, to select all the rows in table2 where ticketidchar does not exist in table1? Thank You, Jason -- __ Brooklyn Linux Solutions So many immigrant groups have swept through our town that Brooklyn, like Atlantis, reaches mythological proportions in the mind of the world - RI Safir 1998 DRM is THEFT - We are the STAKEHOLDERS - RI Safir 2002 http://fairuse.nylxs.com http://www.mrbrklyn.com - Consulting http://www.inns.net -- Happy Clients http://www.nylxs.com - Leadership Development in Free Software http://www2.mrbrklyn.com/resources - Unpublished Archive or stories and articles from around the net http://www2.mrbrklyn.com/downtown.html - See the New Downtown Brooklyn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
