help with query
3 tables: table1 - table2 (one to many) table2 - table3 (one to one) [designed like this] how to achieve this? select table1.*, table2.*, table3.*, sum(table2.field3), sum(table2.field4) from table1, table2, table3 where table1.field1 = table2.field1 and table2.field1 = table3.field1 regards
Re: Change on LEFT JOIN ON syntax in 5.x?
On 2/20/06, Eric Persson [EMAIL PROTECTED] wrote: Hi, I have a query which works fine for me in my 4.1 environment, but when moved to the 5.0.18 environment, it fails with the result below: mysql SELECT r.uid, u.username, u.image_type, count(id) AS antal, s.timestamp FROM recruits_uid r, users u, users u2 LEFT JOIN sessions s ON s.uid=u.uid WHERE r.uid=u.uid AND r.rec_uid=u2.uid AND u2.deleted=0 AND datetime-00-00 00:00:00 GROUP BY r.uid ORDER BY antal DESC LIMIT 100; ERROR 1054 (42S22): Unknown column 'u.uid' in 'on clause' mysql It seems like the table alias u is not recognized for some reason. Does anyone have any hints about this? Yes, try this: LEFT JOIN sessions s ON (s.uid=u.uid) the ON-clause enclosed in parentheses. JP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Certification Questions
Hi Does anybody know where i can get a load of sample questiosn for the MySQL certificate exams, i know there are a few on mysql's site but i was hoping to find quite a few to practice on. Thanks Ade -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqlimport, \r\n and \n
Daniel, What about just running your import file through dos2unix before piping it to mysql? That way, it would leave \n alone (since that is unix format) and replace any occurrances of \r\n with plan old \n. -- George -Original Message- From: Daniel Kasak [mailto:[EMAIL PROTECTED] Sent: Monday, February 20, 2006 11:21 PM To: mysql@lists.mysql.com Subject: mysqlimport, \r\n and \n I've got some import scripts that are giving me trouble. Some MOFOs keep changing the format of the data they give us, and sometimes I loose half the records. When this happens, I change the line terminator from \r\n to \n ... or from \n to \r\n. It's starting to get to me. Is there any way to ease my pain here, short of importing with one format, counting the number of records, truncating the table, importing wit the other format, counting the number of records, and then selecting the format with the most number of records? -- 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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Change on LEFT JOIN ON syntax in 5.x?
Jan Pieter Kunst [EMAIL PROTECTED] wrote on 02/21/2006 04:54:46 AM: On 2/20/06, Eric Persson [EMAIL PROTECTED] wrote: Hi, I have a query which works fine for me in my 4.1 environment, but when moved to the 5.0.18 environment, it fails with the result below: mysql SELECT r.uid, u.username, u.image_type, count(id) AS antal, s.timestamp FROM recruits_uid r, users u, users u2 LEFT JOIN sessions s ON s.uid=u.uid WHERE r.uid=u.uid AND r.rec_uid=u2.uid AND u2.deleted=0 AND datetime-00-00 00:00:00 GROUP BY r.uid ORDER BY antal DESC LIMIT 100; ERROR 1054 (42S22): Unknown column 'u.uid' in 'on clause' mysql It seems like the table alias u is not recognized for some reason. Does anyone have any hints about this? Yes, try this: LEFT JOIN sessions s ON (s.uid=u.uid) the ON-clause enclosed in parentheses. JP JP, That's not what Gabriel meant when he said to use parentheses The original table ref portion of the original query contained several tables that were implicitly cross joined by commas. Eric will need to use parentheses in order to change the order of operations (the join sequence) to include one of his comma-listed tables first or he will need to rewrite his query to use the explicit JOIN ON syntax for all of the table def components of his table ref clause. The demotion of the comma operator as of 5.0.12 makes MySQL more compliant with the applicable SQL standards. Unfortunately there were many users who blindly followed the examples in the documentation and only learned to use commas when they wanted to declare INNER JOINs. Or, they come from Oracle-like systems who use only commas to declare INNER JOINs (and the outer ones, too). Full details on the change and it's impact on query design can be found here: http://dev.mysql.com/doc/refman/5.0/en/join.html I have requested that the documentation be rewritten so that the explicit forms of JOIN declarations are used more often than the implicit form. I do not know the status of making those changes. Perhaps if other could make their recommendations, fewer of our new users will run into this same issue. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: How to turn off all constraints in a table?
Easier than that (though Sheeri's way would work): ALTER TABLE yourtablename DISABLE KEYS; SET FOREIGN_KEY_CHECKS=0; ... do your processing ... SET FOREIGN_KEY_CHECKS=1; ALTER TABLE yourtablename ENABLE KEYS; http://dev.mysql.com/doc/refman/5.0/en/alter-table.html http://dev.mysql.com/doc/refman/5.0/en/set-option.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine sheeri kritzer [EMAIL PROTECTED] wrote on 02/20/2006 12:28:54 PM: Drop the keys and references, change it, and then re-create the keys and references. http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html (a SHOW CREATE TABLE will show you the constraints including foreign keys -- copy those, so you can create them later. Then you can use ALTER TABLE to drop the keys, change your columns, and then you can use ALTER TABLE to add the keys back in, although it sounds like you won't have a primary key in the new table). -Sheeri On 2/17/06, Tomáš Vichta [EMAIL PROTECTED] wrote: Hello, I need to turn off all constraints in a table (especially primary and foreign keys) - because for example I need to exchange primary key values of two rows in a table. And because of primary key constraints I can do it directly. I would to turn off the constraint, rename value of PK1 to PK2 - now I have the same 2 values PK2 and it's the problem, if the primary key constraint is enabled. Thanx very much for answer, TV. -- 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: (mysqldump) Serial output. . .?
Michael, I have been following this thread from the beginning and I just don't see the practical difference between what you propose and the replication methods (SBR and RBR) already in place. How does what you propose differ from the SBR (statement -based replication) that MySQL already supports? Sorry if I am being dense but don't your source and destination schemas need to stay in synch in order for the changes in one table to be able to apply to the other? Isn't that why you are worried about capturing your schemas as ALTER TABLE statements? With SBR, each time a table is altered on the replication master, that ALTER TABLE statement is inserted in the binlog so that the change propagates to the slaves. The DML (data modification language) statements that follow the ALTER TABLE statement in the binlog won't fail because they will be applied to the correct schema on the slave. Again, my sincere apologies for missing the difference in the purpose of what you are trying to do. Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine mwilliams [EMAIL PROTECTED] wrote on 02/20/2006 01:36:18 PM: Sheeri, Thanks very much for the reply. However, that is not what I'm looking for. I don't want the tables that *have been* altered. I want CREATE TABLE statements output in the ALTER TABLE format for re-creation on another system (clean or otherwise). Regards, Michael -- Original Message -- From: sheeri kritzer [EMAIL PROTECTED] Date: Mon, 20 Feb 2006 12:22:55 -0500 mysqldump takes a table or database and dumps it -- current schema, current data. You won't get alter tables. What you want is something that will show all the alter statements. You can run something like this on unix: tail -f binlog* | grep ALTER alter.sql and then the alter.sql text file will always have the alter statements. The binary log captures the alter statements. Or, you could create an 'alteration' table with a text field and timestamp, and have a trigger copy the alter statement to the alteration table. But mysqldump is the wrong solution, because it only dumps now. hope this helps! -Sheeri On 2/16/06, mwilliams [EMAIL PROTECTED] wrote: All, I'm looking to output every piece of data from the database line by line. Is there any methody by which 'mysqldump' can output the following?: use MY_DATABASE; CREATE TABLE IF NOT EXISTS MY_TABLE; ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . The most important of the features above are the ability to CREATE a table only if it doesn't exist (I never want to drop because the same script will be used for syncing) and the ability to have 'mysqldump' be smart and output ALTER IGNORE statements. Any asistance would be greatly appreciated. Regards, Michael -- 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: How to turn off all constraints in a table?
Once CHECK CONSTRAINTS are enabled, I am sure there will be a way to turn them off temporarily. Right now, you can create tables that have them but they are not enforced. To quote http://dev.mysql.com/doc/refman/5.0/en/create-table.html InnoDB tables support checking of foreign key constraints. See Section 14.2, “The InnoDB Storage Engine”. Note that the FOREIGN KEY syntax in InnoDB is more restrictive than the syntax presented for the CREATE TABLE statement at the beginning of this section: The columns of the referenced table must always be explicitly named. InnoDB supports both ON DELETE and ON UPDATE actions on foreign keys. For the precise syntax, see Section 14.2.6.4, “FOREIGN KEY Constraints”. For other storage engines, MySQL Server parses and ignores the FOREIGN KEY and REFERENCES syntax in CREATE TABLE statements. The CHECK clause is parsed but ignored by all storage engines. See Section 1.9.5.5, “Foreign Keys”. So, by disabling the keys (which turns off all UNIQUE and PRIMARY keys) and by disabling all FK checks, you have everything covered. :-) Shawn Green Database Administrator Unimin Corporation - Spruce Pine Tomáš Vichta [EMAIL PROTECTED] wrote on 02/21/2006 11:10:42 AM: Great, that's what I need, thanx very much. And is any similar possibility how to disable all constraints? For example CHECK CONSTRAINT type. [EMAIL PROTECTED] wrote: Easier than that (though Sheeri's way would work): ALTER TABLE yourtablename DISABLE KEYS; SET FOREIGN_KEY_CHECKS=0; ... do your processing ... SET FOREIGN_KEY_CHECKS=1; ALTER TABLE yourtablename ENABLE KEYS; http://dev.mysql.com/doc/refman/5.0/en/alter-table.html http://dev.mysql.com/doc/refman/5.0/en/set-option.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine sheeri kritzer [EMAIL PROTECTED] wrote on 02/20/2006 12:28:54 PM: Drop the keys and references, change it, and then re-create the keys and references. http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html (a SHOW CREATE TABLE will show you the constraints including foreign keys -- copy those, so you can create them later. Then you can use ALTER TABLE to drop the keys, change your columns, and then you can use ALTER TABLE to add the keys back in, although it sounds like you won't have a primary key in the new table). -Sheeri On 2/17/06, TomĂĄĹĄ Vichta [EMAIL PROTECTED] wrote: Hello, I need to turn off all constraints in a table (especially primary and foreign keys) - because for example I need to exchange primary key values of two rows in a table. And because of primary key constraints I can do it directly. I would to turn off the constraint, rename value of PK1 to PK2 - now I have the same 2 values PK2 and it's the problem, if the primary key constraint is enabled. Thanx very much for answer, TV. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with query
how to achieve this? select table1.*, table2.*, table3.*, sum(table2.field3), sum(table2.field4) from table1, table2, table3 where table1.field1 = table2.field1 and table2.field1 = table3.field1 Your question as formulated has no answer. If you query aggregate values like Sum on a table, you will get back meaningful individual row values _only_ for fields which you Group By, so "table2.* makes no sense here. So if the joins are to be on field1, and if you want to Group your Sums also By field1, your query would look like this: SELECT table1.*, SUM(table2.field3), SUM(table2.field4) FROM table1 INNER JOIN table2 USING (field1) INNER JOIN table3 USING (field1) GROUP BY table.field1; PB - xtcsuk wrote: 3 tables: table1 - table2 (one to many) table2 - table3 (one to one) [designed like this] how to achieve this? select table1.*, table2.*, table3.*, sum(table2.field3), sum(table2.field4) from table1, table2, table3 where table1.field1 = table2.field1 and table2.field1 = table3.field1 regards No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.11/264 - Release Date: 2/17/2006 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.11/264 - Release Date: 2/17/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (mysqldump) Serial output. . .?
Shawn, Thanks for the reply, but I think the thread has become much more dramatic than nececessary. Basically, I'm not looking for what has been ALTERed. I simply need the table creation data output in ALTER IGNORE (or whatever is appropriate) format so as to ensure table structure is the same before performing any INSERTS. I don't wish to DROP tables because that would then require reimporting all data. I simply want to write a tool to perform updates between multiple databases that keep them in two-way sync with their respective DBs (and even then, only specific tables) on a main server, both structure-wise and data-wise. The current systems don't use bin logs (*eyes rolling in back of head*) and we don't need replication, but true two-way syncing. Are there any truly quality two-way replication master techniques that you can recommend? I've been working with SJA and I like it pretty well. I'd like to write my own, similar program, but I think it might just work for now. Regards, Michael -- Original Message -- From: [EMAIL PROTECTED] Date: Tue, 21 Feb 2006 11:18:44 -0500 Michael, I have been following this thread from the beginning and I just don't see the practical difference between what you propose and the replication methods (SBR and RBR) already in place. How does what you propose differ from the SBR (statement -based replication) that MySQL already supports? Sorry if I am being dense but don't your source and destination schemas need to stay in synch in order for the changes in one table to be able to apply to the other? Isn't that why you are worried about capturing your schemas as ALTER TABLE statements? With SBR, each time a table is altered on the replication master, that ALTER TABLE statement is inserted in the binlog so that the change propagates to the slaves. The DML (data modification language) statements that follow the ALTER TABLE statement in the binlog won't fail because they will be applied to the correct schema on the slave. Again, my sincere apologies for missing the difference in the purpose of what you are trying to do. Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine mwilliams [EMAIL PROTECTED] wrote on 02/20/2006 01:36:18 PM: Sheeri, Thanks very much for the reply. However, that is not what I'm looking for. I don't want the tables that *have been* altered. I want CREATE TABLE statements output in the ALTER TABLE format for re-creation on another system (clean or otherwise). Regards, Michael -- Original Message -- From: sheeri kritzer [EMAIL PROTECTED] Date: Mon, 20 Feb 2006 12:22:55 -0500 mysqldump takes a table or database and dumps it -- current schema, current data. You won't get alter tables. What you want is something that will show all the alter statements. You can run something like this on unix: tail -f binlog* | grep ALTER alter.sql and then the alter.sql text file will always have the alter statements. The binary log captures the alter statements. Or, you could create an 'alteration' table with a text field and timestamp, and have a trigger copy the alter statement to the alteration table. But mysqldump is the wrong solution, because it only dumps now. hope this helps! -Sheeri On 2/16/06, mwilliams [EMAIL PROTECTED] wrote: All, I'm looking to output every piece of data from the database line by line. Is there any methody by which 'mysqldump' can output the following?: use MY_DATABASE; CREATE TABLE IF NOT EXISTS MY_TABLE; ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] ALTER IGNORE TABLE MY_TABLE ADD MY_COLUMN [properties] INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . INSERT IGNORE . . . . The most important of the features above are the ability to CREATE a table only if it doesn't exist (I never want to drop because the same script will be used for syncing) and the ability to have 'mysqldump' be smart and output ALTER IGNORE statements. Any asistance would be greatly appreciated. Regards, Michael -- 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:
Different result with subquery
Hello, I am having a table with an integer column which I use for probability calculations (the higher a value the more often it is chosen). Now I am having the following query, which should actually incorporate these probabilities, however it seems to prefer values from the middle range SELECT * FROM table WHERE field=(SELECT RAND()*MAX(field) FROM table) ORDER BY field LIMIT 1 If I split it up it still seems to prefer mid-range values over values closer to the higher end, however it doesnt completely ignore the highest value anymore. random_value = SELECT RAND()*MAX(field) FROM table; SELECT * FROM table WHERE field=random_value ORDER BY field LIMIT 1 Does anyone have an explanation for that or a better solution? Thanks __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (mysqldump) Serial output. . .?
One problem with dual-master or multi-master replication is that you have to be able to set and check a lock across all masters before performing a schema change. How would you deal with this scenario using your ALTER TABLE database dumps without such a lock? Server A and B share a table X that has the following definition CREATE TABLE X ( id int auto_increment ,name varchar(20) not null ,status tinyint ) Simultaneously, separate changes are applied to table X on servers A and B with the following statements: SERVER A: ALTER TABLE X CHANGE status status tinyint unsigned; SERVER B: ALTER TABLE X CHANGE status status int; Without some way to serialize those changes you could possibly get stuck in an endless loop. 1) Server A's sync process detects B's change and applies it. B's sync process detects A's changes and applies them to itself. 2) Now both tables (A.X and B.X) are different again. Synchronization attempts to match schemas again. Repeat step 1) until someone wins. Question: What should be the definition of X on both servers at that point? Which change should have precedence? /end scenario NDB (clustering) is the only MySQL database that supports distributed locking and distributed transactions (making sure that at any one time all replicas of the data are kept in sync across the cluster). SBR replication takes care of the circular reference problem by tagging each DML statement with the originating server. If a server detects that it is attempting to process a statement that it already applied to itself, it quits and moves on to the next statement. Clustering can use both SBR and RBR replication (RBR = row-based replication or row-by-row) No, I do not know of any good system for two-way synching (other than NDB) built on top of MySQL. Again, you haven't explained why your synching plan is that much different than setting up circular replication. In circular replication server A is the master to B and B is the master of A. This is a useful design if you can ensure that you can somehow ensure that each server only issues private id values so that your records remain unique throughout your enterprise. Schema changes must occur with great care. I have worked with several different replicating database servers (MySQL, MS SQL server, Lotus Notes) and each have a different way of handling what they call replication conflicts. Those arise from scenarios very similar to what I described above (changes occur to the same record on separate servers between synchronization cycles). How do you plan to handle those? Shawn Green Database Administrator Unimin Corporation - Spruce Pine mwilliams [EMAIL PROTECTED] wrote on 02/21/2006 12:12:42 PM: Shawn, Thanks for the reply, but I think the thread has become much more dramatic than nececessary. Basically, I'm not looking for what has been ALTERed. I simply need the table creation data output in ALTER IGNORE (or whatever is appropriate) format so as to ensure table structure is the same before performing any INSERTS. I don't wish to DROP tables because that would then require reimporting all data. I simply want to write a tool to perform updates between multiple databases that keep them in two-way sync with their respective DBs (and even then, only specific tables) on a main server, both structure-wise and data-wise. The current systems don't use bin logs (*eyes rolling in back of head*) and we don't need replication, but true two-way syncing. Are there any truly quality two-way replication master techniques that you can recommend? I've been working with SJA and I like it pretty well. I'd like to write my own, similar program, but I think it might just work for now. Regards, Michael -- Original Message -- From: [EMAIL PROTECTED] Date: Tue, 21 Feb 2006 11:18:44 -0500 Michael, I have been following this thread from the beginning and I just don't see the practical difference between what you propose and the replication methods (SBR and RBR) already in place. How does what you propose differ from the SBR (statement -based replication) that MySQL already supports? Sorry if I am being dense but don't your source and destination schemas need to stay in synch in order for the changes in one table to be able to apply to the other? Isn't that why you are worried about capturing your schemas as ALTER TABLE statements? With SBR, each time a table is altered on the replication master, that ALTER TABLE statement is inserted in the binlog so that the change propagates to the slaves. The DML (data modification language) statements that follow the ALTER TABLE statement in the binlog won't fail because they will be applied to the correct schema on the slave. Again, my sincere apologies for missing the difference in the purpose of what you are trying to do. Respectfully, Shawn
Re: (mysqldump) Serial output. . .?
Ah, well, in this particular DB, *every single transaction* it's it's own entry. . .basically the DB itself is a binary log. . .kinda. . .sorta. So the current value of a particular item isn't necessarily an issue since, once entered, it will always be the same. A change to that value will in itself be a transaction with its own UUID. As for your question regarding how it's any different, I'm not really sure, besides the need to have the server go down or be locked for a period of time. Regards, Michael -- Original Message -- From: [EMAIL PROTECTED] Date: Tue, 21 Feb 2006 12:51:52 -0500 One problem with dual-master or multi-master replication is that you have to be able to set and check a lock across all masters before performing a schema change. How would you deal with this scenario using your ALTER TABLE database dumps without such a lock? Server A and B share a table X that has the following definition CREATE TABLE X ( id int auto_increment ,name varchar(20) not null ,status tinyint ) Simultaneously, separate changes are applied to table X on servers A and B with the following statements: SERVER A: ALTER TABLE X CHANGE status status tinyint unsigned; SERVER B: ALTER TABLE X CHANGE status status int; Without some way to serialize those changes you could possibly get stuck in an endless loop. 1) Server A's sync process detects B's change and applies it. B's sync process detects A's changes and applies them to itself. 2) Now both tables (A.X and B.X) are different again. Synchronization attempts to match schemas again. Repeat step 1) until someone wins. Question: What should be the definition of X on both servers at that point? Which change should have precedence? /end scenario NDB (clustering) is the only MySQL database that supports distributed locking and distributed transactions (making sure that at any one time all replicas of the data are kept in sync across the cluster). SBR replication takes care of the circular reference problem by tagging each DML statement with the originating server. If a server detects that it is attempting to process a statement that it already applied to itself, it quits and moves on to the next statement. Clustering can use both SBR and RBR replication (RBR = row-based replication or row-by-row) No, I do not know of any good system for two-way synching (other than NDB) built on top of MySQL. Again, you haven't explained why your synching plan is that much different than setting up circular replication. In circular replication server A is the master to B and B is the master of A. This is a useful design if you can ensure that you can somehow ensure that each server only issues private id values so that your records remain unique throughout your enterprise. Schema changes must occur with great care. I have worked with several different replicating database servers (MySQL, MS SQL server, Lotus Notes) and each have a different way of handling what they call replication conflicts. Those arise from scenarios very similar to what I described above (changes occur to the same record on separate servers between synchronization cycles). How do you plan to handle those? Shawn Green Database Administrator Unimin Corporation - Spruce Pine mwilliams [EMAIL PROTECTED] wrote on 02/21/2006 12:12:42 PM: Shawn, Thanks for the reply, but I think the thread has become much more dramatic than nececessary. Basically, I'm not looking for what has been ALTERed. I simply need the table creation data output in ALTER IGNORE (or whatever is appropriate) format so as to ensure table structure is the same before performing any INSERTS. I don't wish to DROP tables because that would then require reimporting all data. I simply want to write a tool to perform updates between multiple databases that keep them in two-way sync with their respective DBs (and even then, only specific tables) on a main server, both structure-wise and data-wise. The current systems don't use bin logs (*eyes rolling in back of head*) and we don't need replication, but true two-way syncing. Are there any truly quality two-way replication master techniques that you can recommend? I've been working with SJA and I like it pretty well. I'd like to write my own, similar program, but I think it might just work for now. Regards, Michael -- Original Message -- From: [EMAIL PROTECTED] Date: Tue, 21 Feb 2006 11:18:44 -0500 Michael, I have been following this thread from the beginning and I just don't see the practical difference between what you propose and the replication methods (SBR and RBR) already in place. How does what you propose differ from the SBR (statement -based replication) that MySQL already supports? Sorry if I am being dense but don't your source and destination schemas need to stay in synch in
mysqldump: invalid option -- Q
Hi, list! I'm facing some porblems with mysqldump. Here is it: [EMAIL PROTECTED] ~] mysqldump --help | grep -- -Q -Q, --quote-names Quote table and column names with ` [EMAIL PROTECTED] ~] mysqldump -Q -u -p -h dbname dump.sql mysqldump: invalid option -- Q Am I doing something wrong? Btw, I have mysqldump V 8.16. [EMAIL PROTECTED] ~] mysqldump --version mysqldump Ver 8.16 Distrib 3.23.46, for unknown-freebsdelf4.6 (i386) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump: invalid option -- Q
At 21:22 +0300 2/21/06, [EMAIL PROTECTED] wrote: Hi, list! I'm facing some porblems with mysqldump. Here is it: [EMAIL PROTECTED] ~] mysqldump --help | grep -- -Q -Q, --quote-names Quote table and column names with ` [EMAIL PROTECTED] ~] mysqldump -Q -u -p -h dbname dump.sql mysqldump: invalid option -- Q Am I doing something wrong? No, looks like a bug, and it seems to be fixed as of 3.23.48. Btw, I have mysqldump V 8.16. [EMAIL PROTECTED] ~] mysqldump --version mysqldump Ver 8.16 Distrib 3.23.46, for unknown-freebsdelf4.6 (i386) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- 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: (mysqldump) Serial output. . .?
Ok, I get that. I have several tables just like that (I use mine as shadow tables for change audits. Every change to the normal table ends up creating new record in the shadow table thus documenting each state of the normal table through time). However, shouldn't schema changes be very rare under such a design as yours? Such infrequent modifications deserve any special attention it would take to convert the output of a SHOW CREATE TABLE xxx into a sequence of ALTER TABLE statements within whichever programming language you are using to control the synchronization. I mean it's not hard to take the output of a SHOW CREATE TABLE xxx statement and transform it into a sequence of ALTER TABLE statements. Alternatively, you could use the output from a SHOW COLUMNS FROM xxx statement as the important parts are already parsed into separate columns (column name, data type, null or not null, etc.). If the application on A is changed to use a new table design (X2) and that schema change is sent to Server B, how does the application on Server B not break ? In particular, if you created X2 by deleting a column from X, that would cause major problems with the application running on B until B is notified of the change from X to X2, wouldn't it? I am not trying to discourage your design or your plan of attack. I am just trying to help by playing devil's advocate. If you plan works well, it may be something many of us in the community may be interested in trying for some of our data issues, if you can share. We understand if you can't. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine mwilliams [EMAIL PROTECTED] wrote on 02/21/2006 01:08:52 PM: Ah, well, in this particular DB, *every single transaction* it's it's own entry. . .basically the DB itself is a binary log. . . kinda. . .sorta. So the current value of a particular item isn't necessarily an issue since, once entered, it will always be the same. A change to that value will in itself be a transaction with its own UUID. As for your question regarding how it's any different, I'm not really sure, besides the need to have the server go down or be locked for a period of time. Regards, Michael snipped
Re: (mysqldump) Serial output. . .?
Yes, schema changes would/should be rare. You may be right, but I would think that since schema information is *alway* checked before any INSERTS then we should be good. There should never be an occasion to break per se. Obviously anything can happen, and appropriate recovery methods (e.g. via reguar dumps) would be in place, but I don't necessarily see that happening often, if at all. All excellent points though! Regards -- Original Message -- From: [EMAIL PROTECTED] Date: Tue, 21 Feb 2006 13:29:22 -0500 Ok, I get that. I have several tables just like that (I use mine as shadow tables for change audits. Every change to the normal table ends up creating new record in the shadow table thus documenting each state of the normal table through time). However, shouldn't schema changes be very rare under such a design as yours? Such infrequent modifications deserve any special attention it would take to convert the output of a SHOW CREATE TABLE xxx into a sequence of ALTER TABLE statements within whichever programming language you are using to control the synchronization. I mean it's not hard to take the output of a SHOW CREATE TABLE xxx statement and transform it into a sequence of ALTER TABLE statements. Alternatively, you could use the output from a SHOW COLUMNS FROM xxx statement as the important parts are already parsed into separate columns (column name, data type, null or not null, etc.). If the application on A is changed to use a new table design (X2) and that schema change is sent to Server B, how does the application on Server B not break ? In particular, if you created X2 by deleting a column from X, that would cause major problems with the application running on B until B is notified of the change from X to X2, wouldn't it? I am not trying to discourage your design or your plan of attack. I am just trying to help by playing devil's advocate. If you plan works well, it may be something many of us in the community may be interested in trying for some of our data issues, if you can share. We understand if you can't. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine mwilliams [EMAIL PROTECTED] wrote on 02/21/2006 01:08:52 PM: Ah, well, in this particular DB, *every single transaction* it's it's own entry. . .basically the DB itself is a binary log. . . kinda. . .sorta. So the current value of a particular item isn't necessarily an issue since, once entered, it will always be the same. A change to that value will in itself be a transaction with its own UUID. As for your question regarding how it's any different, I'm not really sure, besides the need to have the server go down or be locked for a period of time. Regards, Michael snipped -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
confused...
Why, when I create a table as follows: mysql create table requestid ( request_id int not null default 1, constraint requestid_innodb_pk_cons primary key(request_id) ) ENGINE=InnoDB; Query OK, 0 rows affected (0.02 sec) Do I get the following? mysql select request_id from requestid; Empty set (0.01 sec) When I do a show create table I see: mysql show create table requestid; +---+--+ | Table | Create Table | +---+--+ | requestid | CREATE TABLE `requestid` ( `request_id` int(11) NOT NULL default '1', PRIMARY KEY (`request_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +---+--+ 1 row in set (0.00 sec) Shouldn't I be getting back a '1' when I do my select??? Why am I getting an empty set? What am I not understanding? How do I create a table with a starting value of '1' or '0' for an int??? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Different result with subquery
Neroux, RAND() gives a roughly rectangular distribution, not a normal distribution, so samples of fewer than 100 or so values from it are likely to show large but statistically _insignificant_ differences. In larger samples, I don't see any tendency of RAND() to produce more values near its mean (.5) than near its limits (0,1). You can check this yourself by populating a table with RAND() values then running a simple crosstab query eg SELECT SUM(IF(r0.1,1,0)) AS P1, SUM(IF(r=0.1 AND r0.2,1,0)) AS P2, SUM(IF(r=0.2 AND r0.3,1,0)) AS P3, SUM(IF(r=0.3 AND r0.4,1,0)) AS P4, SUM(IF(r=0.4 AND r0.5,1,0)) AS P5, SUM(IF(r=0.5 AND r0.6,1,0)) AS P6, SUM(IF(r=0.6 AND r0.7,1,0)) AS P7, SUM(IF(r=0.7 AND r0.8,1,0)) AS P8, SUM(IF(r=0.8 AND r0.9,1,0)) AS P9, SUM(IF(r=0.9 AND r1.0,1,0)) AS P10 FROM randresults; PB - neroux wrote: Hello, I am having a table with an integer column which I use for probability calculations (the higher a value the more often it is chosen). Now I am having the following query, which should actually incorporate these probabilities, however it seems to prefer values from the middle range SELECT * FROM table WHERE field=(SELECT RAND()*MAX(field) FROM table) ORDER BY field LIMIT 1 If I split it up it still seems to prefer mid-range values over values closer to the higher end, however it doesnt completely ignore the highest value anymore. random_value = SELECT RAND()*MAX(field) FROM table; SELECT * FROM table WHERE field=random_value ORDER BY field LIMIT 1 Does anyone have an explanation for that or a better solution? Thanks __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.11/264 - Release Date: 2/17/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: confused...
On Tue, 21 Feb 2006, Patrick Duda wrote: Why, when I create a table as follows: mysql create table requestid ( request_id int not null default 1, constraint requestid_innodb_pk_cons primary key(request_id) ) ENGINE=InnoDB; Query OK, 0 rows affected (0.02 sec) Defines the properties of an empty table The request id field for an inserted object will default to one if not supplied. But the object must be supplied. Hugh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: confused...
Patrick Duda [EMAIL PROTECTED] wrote on 02/21/2006 02:39:47 PM:
Why, when I create a table as follows:
mysql create table requestid ( request_id int not null default
1, constraint requestid_innodb_pk_cons primary key(request_id) )
ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)
Do I get the following?
mysql select request_id from requestid;
Empty set (0.01 sec)
When I do a show create table I see:
mysql show create table requestid;
+---
+--
+
| Table | Create
Table
|
+---
+--
+
| requestid | CREATE TABLE `requestid` (
`request_id` int(11) NOT NULL default '1',
PRIMARY KEY (`request_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---
+--
+
1 row in set (0.00 sec)
Shouldn't I be getting back a '1' when I do my select??? Why am I
getting
an empty set? What am I not understanding? How do I create a table
with a
starting value of '1' or '0' for an int???
Thanks
You haven't created any rows yet. That's why you get nothing back from
your SELECT query. With a single-column table like this, it will be
impossible to add a row to the table without providing a value for ID
(because it's the only column). You will never see the default value
because you must always supply one.
The term starting value in your original post implies that you intended
some sort of sequence. Did you want the server to automatically increment
the request_id value for you each time you add a record to this table? If
so, you have to do two things:
1) add more columns to this table
2) change the definition of your ID column to be an auto_increment column.
Here is an example of what your `request` table may look like
CREATE TABLE `request` (
id int not null auto_increment,
details varchar(50) not null,
tsModified timestamp,
PRIMARY KEY(id)
);
and you could add reqests to it like this:
INSERT `request`(`details`) VALUES ('details of your first
request'),('details of a second request'), ('details of a third request');
Is it making any better sense?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
JOINs with result of aggregate function fails with error #1054
This must have been asked before but I could not find the answer searching the list archives. I have a simple table: CREATE TABLE `license` ( `id` int(11) NOT NULL auto_increment, `firstname` varchar(100) NOT NULL default '', `lastname` varchar(100) NOT NULL default '', `host_address` varchar(100) NOT NULL default '', `is_visible` tinyint(4) NOT NULL default '1', PRIMARY KEY (`id`) ) TYPE=MyISAM; This first query works (MySQL 4.0.25): SELECT l.id, MAX(l.id) AS maxid, l.host_address FROM `license` l WHERE l.is_visible='1' GROUP BY l.host_address ORDER BY maxid DESC but this second query fails with the error: #1054 - Unknown column 'maxid' in 'on clause' SELECT l.id, MAX(l.id) AS maxid, l.host_address, l2.firstname, l2.lastname FROM `license` l INNER JOIN license l2 ON l2.id=maxid WHERE l.is_visible='1' GROUP BY l.host_address ORDER BY maxid DESC Is there a way to do a join with the result of an aggregate function (in this case MAX(id)) in one query, or do I have to use multiple queries for this? Thanks in advance! Guillaume
Re: confused...
Patrick, Shouldn't I be getting back a '1' when I do my select??? Why am I getting an empty set? First, creating a table doesn't add any rows. Show Create Table ... returns a row of data definition info, not a row of table data. Second, Defining the column as NOT NULL will require numeric input for the column, so DEFAULT 1 will have no effect. What are you trying to accomplish with DEFAULT 1? PB - Patrick Duda wrote: Why, when I create a table as follows: mysql create table requestid ( request_id int not null default 1, constraint requestid_innodb_pk_cons primary key(request_id) ) ENGINE=InnoDB; Query OK, 0 rows affected (0.02 sec) Do I get the following? mysql select request_id from requestid; Empty set (0.01 sec) When I do a show create table I see: mysql show create table requestid; +---+--+ | Table | Create Table | +---+--+ | requestid | CREATE TABLE `requestid` ( `request_id` int(11) NOT NULL default '1', PRIMARY KEY (`request_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +---+--+ 1 row in set (0.00 sec) Shouldn't I be getting back a '1' when I do my select??? Why am I getting an empty set? What am I not understanding? How do I create a table with a starting value of '1' or '0' for an int??? Thanks -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.11/264 - Release Date: 2/17/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOINs with result of aggregate function fails with error #1054
Guillaume Boissiere wrote: This must have been asked before but I could not find the answer searching the list archives. I have a simple table: CREATE TABLE `license` ( `id` int(11) NOT NULL auto_increment, `firstname` varchar(100) NOT NULL default '', `lastname` varchar(100) NOT NULL default '', `host_address` varchar(100) NOT NULL default '', `is_visible` tinyint(4) NOT NULL default '1', PRIMARY KEY (`id`) ) TYPE=MyISAM; This first query works (MySQL 4.0.25): SELECT l.id, MAX(l.id) AS maxid, l.host_address FROM `license` l WHERE l.is_visible='1' GROUP BY l.host_address ORDER BY maxid DESC but this second query fails with the error: #1054 - Unknown column 'maxid' in 'on clause' SELECT l.id, MAX(l.id) AS maxid, l.host_address, l2.firstname, l2.lastname FROM `license` l INNER JOIN license l2 ON l2.id=maxid WHERE l.is_visible='1' GROUP BY l.host_address ORDER BY maxid DESC Is there a way to do a join with the result of an aggregate function (in this case MAX(id)) in one query, or do I have to use multiple queries for this? Thanks in advance! Guillaume You cannot join on an aggregate function. The value of maxid cannot be determined until after the join is completed. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOINs with result of aggregate function fails with error #1054
Guillaume Boissiere [EMAIL PROTECTED] wrote on 02/21/2006 02:23:29 PM: This must have been asked before but I could not find the answer searching the list archives. I have a simple table: CREATE TABLE `license` ( `id` int(11) NOT NULL auto_increment, `firstname` varchar(100) NOT NULL default '', `lastname` varchar(100) NOT NULL default '', `host_address` varchar(100) NOT NULL default '', `is_visible` tinyint(4) NOT NULL default '1', PRIMARY KEY (`id`) ) TYPE=MyISAM; This first query works (MySQL 4.0.25): SELECT l.id, MAX(l.id) AS maxid, l.host_address FROM `license` l WHERE l.is_visible='1' GROUP BY l.host_address ORDER BY maxid DESC but this second query fails with the error: #1054 - Unknown column 'maxid' in 'on clause' SELECT l.id, MAX(l.id) AS maxid, l.host_address, l2.firstname, l2.lastname FROM `license` l INNER JOIN license l2 ON l2.id=maxid WHERE l.is_visible='1' GROUP BY l.host_address ORDER BY maxid DESC Is there a way to do a join with the result of an aggregate function (in this case MAX(id)) in one query, or do I have to use multiple queries for this? Thanks in advance! Guillaume You you will have to use two queries. You might have been able to use a subquery but your version doesn't support them. The reason for the error is that the MAX() function isn't evaluated until you get to the GROUP BY portion of the query. This is AFTER all tables are JOIN-ed and all of the WHERE restrictions have been applied. The column name `maxid` won't exist until the MAX() value has been calculated so ... you have nothing to join to. Thus the error. sorry! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: mysqldump command
I'm assuming you're doing this as root (hence the # sign). The way to debug this, as with any script, is to run it manually on the command line and see what errors you get. So if you run the following, what happens? mysqldump --opt -c -C dp /var/tmp_save/dproject.sql (not sure if you ran that on the command line, or through cron). mysqldump --opt -c -C dp One of these should give you an error. I'd guess that you need to specify a username and password, since you haven't specifically said you'd set them in a .my.cnf. -Sheeri On 2/20/06, Reynier Perez Mira [EMAIL PROTECTED] wrote: I try to create a cron in Linux (Debian Sarge) and the content for this cron is a mysql database backup. I read documentation about mysqldump command and use, but I forgot something because it not works for me. I put this in bash: # mysqldump --opt -c -C dp /var/tmp_save/dproject.sql But when I open .sql file none INSERT statement was added. Is wrong my command or ... Regards, -- ReynierPM 4to. Ing. Informática Linux User: #310201 El programador superhéroe aprende de compartir sus conocimientos. Es el referente de sus compañeros. Todo el mundo va a preguntarle y él, secretamente, lo fomenta porque es así como adquiere su legendaria sabiduría: escuchando ayudando a los demás... -- 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]
Creating a Web Database Search Application
Hello, I commonly create webpages that need to search through a MySQL database and then display the results to the user. An example would be a database that contains the following: first name last name age gender location job interests I usually create a webpage with a combination of drop down menus, checkboxes, and radio buttons that allow the user to select what things they want to search for. An example might be that a user wants to find all results with: last name like smi age between 25 and 35 gender = female interests like golf But I've found it tricky to craft MySQL select statements because due to the way the searches can vary, the queries get complicated pretty quickly. Is there any simple way to do this type of thing? I write my scripts in Perl on Unix (Solaris). Are there webpages that will explain good ways to do this? Any tips are welcomed. Thanks, Douglas Douglas S. Davis Programmer/Analyst Haverford College Administrative Computing 370 Lancaster Ave. Haverford, PA 19041 610-896-4206 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlimport, \r\n and \n
George Law wrote: Daniel, What about just running your import file through dos2unix before piping it to mysql? That way, it would leave \n alone (since that is unix format) and replace any occurrances of \r\n with plan old \n. I could think of a lot of things I could do if this were all running on Linux. Unfortunately the import routine is being triggered from MS Access ... and come to think of it, I'm using 'load data infile' and not 'mysqlimport', but anyway, you get the idea. AFAIK there is no way to trigger anything useful via ODBC. I could write a Perl script, chuck it in my cgi-bin folder, and opening Firefox from Access, passing the script some pointers to the file. But that's dodgy. I suppose while I'm working with Access I'd better get used to dodgy solutions, eh? ;-) -- 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]
Re: mysqlimport, \r\n and \n
A little less dodgy is to write a VBA routine within Access to do the filtering. Alternatively, if you don't want to trust access to do it, you can write a VBScript or JScript routine and run it through the shell (yes, Windoze has shell scripts, too) There are all kinds of things you can do. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Daniel Kasak [EMAIL PROTECTED] wrote on 02/21/2006 04:13:28 PM: George Law wrote: Daniel, What about just running your import file through dos2unix before piping it to mysql? That way, it would leave \n alone (since that is unix format) and replace any occurrances of \r\n with plan old \n. I could think of a lot of things I could do if this were all running on Linux. Unfortunately the import routine is being triggered from MS Access ... and come to think of it, I'm using 'load data infile' and not 'mysqlimport', but anyway, you get the idea. AFAIK there is no way to trigger anything useful via ODBC. I could write a Perl script, chuck it in my cgi-bin folder, and opening Firefox from Access, passing the script some pointers to the file. But that's dodgy. I suppose while I'm working with Access I'd better get used to dodgy solutions, eh? ;-) -- 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]
Re: Creating a Web Database Search Application
- Original Message - From: Douglas S. Davis [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, February 21, 2006 3:58 PM Subject: Creating a Web Database Search Application Hello, I commonly create webpages that need to search through a MySQL database and then display the results to the user. An example would be a database that contains the following: first name last name age gender location job interests I usually create a webpage with a combination of drop down menus, checkboxes, and radio buttons that allow the user to select what things they want to search for. An example might be that a user wants to find all results with: last name like smi age between 25 and 35 gender = female interests like golf But I've found it tricky to craft MySQL select statements because due to the way the searches can vary, the queries get complicated pretty quickly. Is there any simple way to do this type of thing? I write my scripts in Perl on Unix (Solaris). Are there webpages that will explain good ways to do this? Any tips are welcomed. Thanks, Douglas You could have a menu listing the reports available and craft the sql specially for each. The hard part is naming them so it's readily apparent what each report reports. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Creating a Web Database Search Application
- Original Message -
From: Douglas S. Davis [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday, February 21, 2006 3:58 PM
Subject: Creating a Web Database Search Application
Hello,
I commonly create webpages that need to search through a MySQL
database and then display the results to the user. An example would
be a database that contains the following:
first name
last name
age
gender
location
job
interests
I usually create a webpage with a combination of drop down menus,
checkboxes, and radio buttons that allow the user to select what
things they want to search for. An example might be that a user
wants to find all results with:
last name like smi
age between 25 and 35
gender = female
interests like golf
But I've found it tricky to craft MySQL select statements because due
to the way the searches can vary, the queries get complicated pretty
quickly.
Is there any simple way to do this type of thing? I write my scripts
in Perl on Unix (Solaris). Are there webpages that will explain good
ways to do this?
Any tips are welcomed.
Thanks,
Douglas
[Hit send too soon]
With so few fields, sticking with one report isn't
out of the question. Don't know if you're using
this technique:
my $lname = $q-param('lname') || '%';
So all fields not filled/selected by the user are
wildcards by default. A completely empty form
pulls all data in the relevant tables.
--Jon
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Different result with subquery
Hello Peter, thanks for your reply, however my question was less related to RAND() but more about the weird situation that the results are more or less fine with the two queries, however different when they are combined into one query with a subquery. Thanks --- Peter Brawley [EMAIL PROTECTED] wrote: Neroux, RAND() gives a roughly rectangular distribution, not a normal distribution, so samples of fewer than 100 or so values from it are likely to show large but statistically _insignificant_ differences. In larger samples, I don't see any tendency of RAND() to produce more values near its mean (.5) than near its limits (0,1). You can check this yourself by populating a table with RAND() values then running a simple crosstab query eg SELECT SUM(IF(r0.1,1,0)) AS P1, SUM(IF(r=0.1 AND r0.2,1,0)) AS P2, SUM(IF(r=0.2 AND r0.3,1,0)) AS P3, SUM(IF(r=0.3 AND r0.4,1,0)) AS P4, SUM(IF(r=0.4 AND r0.5,1,0)) AS P5, SUM(IF(r=0.5 AND r0.6,1,0)) AS P6, SUM(IF(r=0.6 AND r0.7,1,0)) AS P7, SUM(IF(r=0.7 AND r0.8,1,0)) AS P8, SUM(IF(r=0.8 AND r0.9,1,0)) AS P9, SUM(IF(r=0.9 AND r1.0,1,0)) AS P10 FROM randresults; PB - neroux wrote: Hello, I am having a table with an integer column which I use for probability calculations (the higher a value the more often it is chosen). Now I am having the following query, which should actually incorporate these probabilities, however it seems to prefer values from the middle range SELECT * FROM table WHERE field=(SELECT RAND()*MAX(field) FROM table) ORDER BY field LIMIT 1 If I split it up it still seems to prefer mid-range values over values closer to the higher end, however it doesnt completely ignore the highest value anymore. random_value = SELECT RAND()*MAX(field) FROM table; SELECT * FROM table WHERE field=random_value ORDER BY field LIMIT 1 Does anyone have an explanation for that or a better solution? Thanks __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
parameterized view ?
Is there such a thing in mySQL ? I could not find anything anywhere. thanks, laszlo
dropping a database to reclaim space
Hi. I have a database that is fairly large and I'm doing some testing with 2 different mysql packages, one 32-bit, one 64bit, for comparison's sake. Now I've finished with one of the dbs and I want to reclaim the disk space. I've tried deleting a few (large) tables from the database, thinking this would free up space, however the mysql/var/ibdata1 file doesn't change in size. How can I get rid of a table or even complete database to reclaim disk space? I don't really want to delete the ibdata1 file, because that will also delete the system database right? I've already tried drop database and that doesn't show reduced disk space either. I'm doing this on a solaris 9 box. Any input appreciated. Thanks. Kind regards. -- Luke -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Certification Questions
the study guide? Adrian Bruce wrote: Hi Does anybody know where i can get a load of sample questiosn for the MySQL certificate exams, i know there are a few on mysql's site but i was hoping to find quite a few to practice on. Thanks Ade -- life is a game... so have fun. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Nested Set Model or modified preorder tree traversal mySQL/PHP code wanted
I've been searching the web for the past few hours trying to find a simple drop-in class or functions to implement Nested Set Model or modified preorder tree traversal. I've found several tutorials and related links: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html http://www.sitepoint.com/article/hierarchical-data-database http://www.zend.com/zend/tut/tutorial-ferrara2.php?article=tutorial-ferrara2 id=3453open=1anc=0view=1 http://simon.incutio.com/archive/2003/06/19/storingTrees http://istherelifeafter.com/joecelko.html http://www.codeproject.com/cs/database/Trees_in_SQL_databases.asp I even found some outdated PEAR DB_NestedSet class from 2003 that seemed way to overly complicated. And some other PHP4_Mysql3MPTTA class on PHP Classes that was equally complicated and written for ancient PHP4 and mySQL 3!! Hasn't anyone else done this before (recently)? Did everyone just write their own code every time? Anyone have some easy to use code that allows for: Add, delete, update/rename, select, show tree, bread crumb display, etc. I use PHP 5.0.5 and mySQL 5.0.18, so ideally it would take advantage of all the optimizations and bells whistles of those more modern versions. TIA. D.Vin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Creating a Web Database Search Application
Hi,
With so few fields, sticking with one report isn't
out of the question. Don't know if you're using
this technique:
my $lname = $q-param('lname') || '%';
So all fields not filled/selected by the user are
wildcards by default. A completely empty form
pulls all data in the relevant tables.
Hmm, doing a
... column-name LIKE '%' ...
isn't slower than including nothing?
I mean (warning!, no escaping):
push @where, lname LIKE '$lname'
if $lname;
And:
$where = '';
$where = WHERE .join(' AND ', @where)
if @where;
Bye,
Andras
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: next, prev, records in MySql. Handler Function
Thanks Sheeri:
Yes, you where right. It was because I was not opening the table first.
Actually, I was using this statements from a MySQL GUI (MySQL Query
Browser) so it doesn't work in this program (it doesn't keeps the table
opened).
But when I use your example in the shell mode it works perfect.
Have you ever tried to use this kind of statements using PHP?. It is
because I would like to use it in a PHP script (I was exploring this
function to make a navigator which sends me to the next record each time
I click on a link generated from a MySQL query).
Thanks a lot and very grateful with you,
Alvaro.
sheeri kritzer escribió:
Hi there,
What did your MySQL error say?
It looks like you didn't open the table. my example, which worked:
CREATE TABLE `foo` (
`id` int(11) NOT NULL auto_increment,
`bar` char(3) default NULL,
PRIMARY KEY (`id`),
KEY `idx_bar` (`bar`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
insert into foo (bar) VALUES
('abc'),('def'),('qwe'),('ert'),('wer'),('tyu'),('asd'),('sdf'),('dfg'),('zxc'),('xcc');
and then:
mysql HANDLER foo open;
Query OK, 0 rows affected (0.00 sec)
mysql HANDLER foo READ idx_bar FIRST where bar='wer';
++--+
| id | bar |
++--+
| 5 | wer |
++--+
1 row in set (0.00 sec)
mysql HANDLER foo READ idx_bar NEXT;
++--+
| id | bar |
++--+
| 11 | xcc |
++--+
1 row in set (0.00 sec)
mysql HANDLER foo READ idx_bar PREV;
++--+
| id | bar |
++--+
| 5 | wer |
++--+
1 row in set (0.00 sec)
mysql HANDLER foo READ idx_bar PREV;
++--+
| id | bar |
++--+
| 6 | tyu |
++--+
1 row in set (0.00 sec)
Granted, that's using previous and next in an alphabetical sense.
I found that using the id index didn't work, and I had to create
another non-primary index on key for it to work.
-Sheeri
On 2/8/06, Alvaro Cobo [EMAIL PROTECTED] wrote:
Dear all:
I have been exploring about this issue quite a lot, and find no solution:
Platform: Debian, MySql 4.1.11, PHP 4.3.10-2, Apache.
Is there any way I can retrieve a set of values depending in a where clause:
For example:
from a set of values in one field: 1,2,5,8,9,11,13
I'd like to retrieve a record (8) and also the previous one (5) and the
next one (9) (so the record set would be: 5,8,9)
I have found the Handler function in the Manual, but it and keeps
giving me errors (I have also checked in the manual and it seems to work
with MySql 4.1.x)
/* --Start example
-
HANDLER tbl_sm04_indicador READ PK_indicador_id { FIRST | NEXT | PREV |
LAST }
WHERE PK_indicador_id=8
LIMIT 0, 3
--
--End example (I know, I am completely lost)*/
Does anybody has tried this function before?.
Is it useful for the result I would like to accomplish?
Could anybody could share an example of how to use this function?
Thanks and best regards.
Alvaro Cobo
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
signature.asc
Description: OpenPGP digital signature
Inner join with left join
Got myself a little stumped here, 4.0.18-standard
Three tables in this mess, orders, order_items and products.
orders.prod_id = order_items.prod_id = products.prod_id is how I relate them
all to each other.
order_items has a quantity field.
I need a report that shows me all the products with a sum() for each, but
only if the status of the order is NOT IN ('cancelled', 'pending', 'ghost')
This gets me pretty close, but does not mask out the orders that have the
wrong status, as I do not know how to add in the join on orders
SELECT p.prod_name, count(oi.product_id) as mycount
FROM products as p
LEFT JOIN order_items as oi
on (p.id = oi.product_id)
group by oi.product_id order by mycount;
--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Novato, CA U.S.A.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
