Re: Restrict inserts to meet certain conditions?
That's what I thought. Thanks! On 30. Sep 2007, at 14:55, Rob Wultsch wrote: CHECK CONSTRAINT I mean... On 9/29/07, Rob Wultsch [EMAIL PROTECTED] wrote: CONSTRAINT does not exist in mysql. You can use a trigger to deal with the issue, which is obviously less than ideal. On 9/29/07, David Zentgraf [EMAIL PROTECTED] wrote: Hi, Is it possible to restrict Inserts and Updates to meet certain conditions? In one of my tables lets say field A, B and C can be NULL, but not all at once. Depending on what type of item I want to store, A needs to be NULL, but B and C need values, for other types A needs a value but B and C can be NULL etc. Is it possible to implement this checking logic on the database level via CONSTRAINTs or something similar? Best Regards, Dav -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- Rob Wultsch (480)223-2566 [EMAIL PROTECTED] (email/google im) wultsch (aim) [EMAIL PROTECTED] (msn) -- Rob Wultsch (480)223-2566 [EMAIL PROTECTED] (email/google im) wultsch (aim) [EMAIL PROTECTED] (msn) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict inserts to meet certain conditions?
I am sorry for sending multiple notes. The reply to is set not being set to mysql discussion list. I click the reply button automatically. This email is for the sake of the list sake... You want a feature like a check constraint: http://www.postgresql.org/docs/8.1/static/ddl-constraints.html which does exist in mysql. A trigger can be used to replicate this feature, in a less than ideal manner... On 9/29/07, David Zentgraf [EMAIL PROTECTED] wrote: Hi, Is it possible to restrict Inserts and Updates to meet certain conditions? In one of my tables lets say field A, B and C can be NULL, but not all at once. Depending on what type of item I want to store, A needs to be NULL, but B and C need values, for other types A needs a value but B and C can be NULL etc. Is it possible to implement this checking logic on the database level via CONSTRAINTs or something similar? Best Regards, Dav -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Rob Wultsch (480)223-2566 [EMAIL PROTECTED] (email/google im) wultsch (aim) [EMAIL PROTECTED] (msn) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multi Lookup Table Joins
I often find that I have more than one column in a tale that is an integer ID used to join to a lookup table. If there is only one Join to do it is to do something like this SELECT t.data, l.group FROM table t JOIN lookuptable l USING (groupID) WHERE whatever however if I need to join more than one that syntax wont work because the second join will be trying to join to the first lookup table no the main table. Is there a way around this or do I need to just do joins using this syntax SELECT x, y, z FROM table t, lookupA la, lookupB lb WHERE t.aID = a.aID AND t.bID = b.bID -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict inserts to meet certain conditions?
I'm seriously intrigued by the idea of trying postgres for this project... Thanks! On 30. Sep 2007, at 15:08, Rob Wultsch wrote: I am sorry for sending multiple notes. The reply to is set not being set to mysql discussion list. I click the reply button automatically. This email is for the sake of the list sake... You want a feature like a check constraint: http://www.postgresql.org/docs/8.1/static/ddl-constraints.html which does exist in mysql. A trigger can be used to replicate this feature, in a less than ideal manner... On 9/29/07, David Zentgraf [EMAIL PROTECTED] wrote: Hi, Is it possible to restrict Inserts and Updates to meet certain conditions? In one of my tables lets say field A, B and C can be NULL, but not all at once. Depending on what type of item I want to store, A needs to be NULL, but B and C need values, for other types A needs a value but B and C can be NULL etc. Is it possible to implement this checking logic on the database level via CONSTRAINTs or something similar? Best Regards, Dav -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- Rob Wultsch (480)223-2566 [EMAIL PROTECTED] (email/google im) wultsch (aim) [EMAIL PROTECTED] (msn) -- 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: MySQL 5.1.21 won't build on OpenBSD unless...
Hi ! Dan Nelson wrote: In the last episode (Sep 27), Jake Conk said: Not sure if this is known or not but I just wanted to let you guys know that MySQL 5.1.21 fails the make. I've never had this problem on other versions of mysql. It looks like GNU make worked all the way through but why won't regular make work anymore? You didn't give much useful information in your message (not even a line number!), but I bet I found the problem. Lines 149 and 150 of Makefile.am have spaces instead of tabs as indentation characters. Even the documentation for GNU make says that A tab character must come at the beginning of every command line to distinguish command lines from other lines in the makefile., so it really should have failed too. Fix those two lines before running ./configure and your build should run okay. If you refer to the Makefile in the root directory of the MySQL sources: This has been spotted and fixed, 5.1.22 sources do not contain it any more. I fully agree to requiring tab characters for indenting actions in Makefiles. Sorry for it ever appearing in published sources ! Joerg -- Joerg Bruehe, Senior Production Engineer 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]
Having trouble storing monetary values...
Hi, all. I'm having trouble storing monetary values. When MySQL 5.0 stores the entered value of 5.23, it storing it as 5, dropping off the values after the decimal. The field is the decimal type with 2 specified as the number of values to store after the decimal. Can anyone provide some clues on how to set up the field or any other changes I need to make to properly store the monetary values? Thanks, Rick
Re: Having trouble storing monetary values...
Rick Faircloth wrote: Hi, all. I'm having trouble storing monetary values. When MySQL 5.0 stores the entered value of 5.23, it storing it as 5, dropping off the values after the decimal. The field is the decimal type with 2 specified as the number of values to store after the decimal. Can anyone provide some clues on how to set up the field or any other changes I need to make to properly store the monetary values? Please send us the exact column definition from SHOW CREATE TABLE. You should be able to use a definition like DECIMAL(10,2) or similar. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi Lookup Table Joins
Chris W wrote: I often find that I have more than one column in a tale that is an integer ID used to join to a lookup table. If there is only one Join to do it is to do something like this SELECT t.data, l.group FROM table t JOIN lookuptable l USING (groupID) WHERE whatever however if I need to join more than one that syntax wont work because the second join will be trying to join to the first lookup table no the main table. Is there a way around this or do I need to just do joins using this syntax SELECT x, y, z FROM table t, lookupA la, lookupB lb WHERE t.aID = a.aID AND t.bID = b.bID You can use ON clauses and explicitly qualify which table the columns are in. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Reusing deleted variable-size record space
Hello I have a database with variable-size fields, some of them may be modified/deleted during the usage and administration, so my doubt is: how can I compact the records to remove those blank spaces and save space, lets say defragment the database file so data is continuous and contiguous? This task should be performed just once or twice a month, so no matter if it could take a while. Is there an internal function to do that? I could copy existing records to a new table, delete all the original ones and then insert them back and drop the second table but don't like it too much. Thanks for any suggestion -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi Lookup Table Joins
Perhaps you want the ON syntax SELECT x, y, z FROM table t, lookupA a, lookupB b WHERE t.aID = a.aID AND t.bID = b.bID is equivalent to: SELECT x, y, z FROM table t INNER JOIN lookupA a ON t.aID = a.aID INNER JOIN lookupB b ON t.bID = b.bID If you were to do: SELECT x, y, z FROM table t INNER JOIN lookupA a USING(aID) INNER JOIN lookupB b USING(bID) that would be equivalent to SELECT x, y, z FROM table t INNER JOIN lookupA a ON t.aID = a.aID INNER JOIN lookupB b ON a.bID = b.bID On 9/29/07, Chris W [EMAIL PROTECTED] wrote: I often find that I have more than one column in a tale that is an integer ID used to join to a lookup table. If there is only one Join to do it is to do something like this SELECT t.data, l.group FROM table t JOIN lookuptable l USING (groupID) WHERE whatever however if I need to join more than one that syntax wont work because the second join will be trying to join to the first lookup table no the main table. Is there a way around this or do I need to just do joins using this syntax SELECT x, y, z FROM table t, lookupA la, lookupB lb WHERE t.aID = a.aID AND t.bID = b.bID -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Rob Wultsch (480)223-2566 [EMAIL PROTECTED] (email/google im) wultsch (aim) [EMAIL PROTECTED] (msn) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reusing deleted variable-size record space
Hello Renito - What you are looking for is MySQL's OPTIMIZE function: http://dev.mysql.com/doc/refman/5.0/en/optimize-table.html It does exactly what you are looking for. You can implement this manually, via shell script on a timer, etc. I have written a multi-threaded perl solution which will check, repair and optimize tables simultaneously to save time: http://dbuettner.dyndns.org/blog/?page_id=88 HTH, Dan On 9/30/07, Renito 73 [EMAIL PROTECTED] wrote: Hello I have a database with variable-size fields, some of them may be modified/deleted during the usage and administration, so my doubt is: how can I compact the records to remove those blank spaces and save space, lets say defragment the database file so data is continuous and contiguous? This task should be performed just once or twice a month, so no matter if it could take a while. Is there an internal function to do that? I could copy existing records to a new table, delete all the original ones and then insert them back and drop the second table but don't like it too much. Thanks for any suggestion -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.1.22-rc has been released
Dear MySQL users, we are proud to present to you the MySQL Server 5.1.22-rc release, the first 5.1 release candidate version of the popular open source database. Bear in mind that this is still a candidate release, and as with any other pre-production release, caution should be taken when installing on production level systems or systems with critical data. For production level systems using 5.0, we would like to direct your attention to the product description of MySQL Enterprise at: http://mysql.com/products/enterprise/ The MySQL 5.1.22-rc release is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up to date at this point in time, so if you can't find this version on some mirror, please try again later or choose another download site. Please also note that some of our mirrors are currently experiencing problems that may result in serving corrupted files. We are working with the mirror maintainers to resolve this. We welcome and appreciate your feedback, bug reports, bug fixes, patches etc.: http://forge.mysql.com/wiki/Contributing The following section lists the changes from version to version in the MySQL source code since the latest released version of MySQL 5.1, the MySQL 5.1.21-beta release. It can also be viewed online at http://dev.mysql.com/doc/refman/5.1/en/news-5-1-22.html Functionality added or changed: * There is a new innodb_autoinc_lock_mode system variable to configure the locking behavior that InnoDB uses for generating auto-increment values. The default behavior now is slightly different from before, which involves a minor incompatibility for multiple-row inserts that specify an explicit value for the auto-increment column in some but not all rows. This can be used to improve scalability and performance, see Section 13.5.6.3, How AUTO_INCREMENT Handling Works in InnoDB.: http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html Bugs fixed: * NDB Cluster: Backups of TIMESTAMP columns made with ndb_restore on a MySQL Cluster using data nodes hosts of one endian could not be used to restore the cluster's data to data node hosts of the other endian. (Bug#30134: http://bugs.mysql.com/30134) * NDB Cluster (Replication): Multi-master replication setups did not handle --log-slave-updates correctly. (Bug#30017: http://bugs.mysql.com/30017) * When sorting rows in an INNODB table using a primary key, where the sort was on the the primary key column and the DESC operator was applied, the rows would be incorrectly sorted if you included a simple WHERE field = value clause in the query. (Bug#31001: http://bugs.mysql.com/31001) * Replication of InnoDB partitioned tables could lose updates withrow-basedormixed replication format. (Bug#28430: http://bugs.mysql.com/28430) * mysql_install_db could fail to find its message file. (Bug#30678: http://bugs.mysql.com/30678) * Non-range queries of the form SELECT ... FROM ... WHERE keypart_1=const, ..., keypart_n=const ORDER BY ... FOR UPDATE sometimes were unnecessarily blocked waiting for a lock if another transaction was using SELECT ... FOR UPDATE on the same table. (Bug#28570: http://bugs.mysql.com/28570) * Under some circumstances, a UDF initialization function could be passed incorrect argument lengths. (Bug#29804: http://bugs.mysql.com/29804) * CONNECTION_ID() always returned 0 for the embedded server (libmysqld). (Bug#30389: http://bugs.mysql.com/30389) * The mysql_list_fields() C API function incorrectly set MYSQL_FIELD::decimalsforsomeviewcolumns. (Bug#29306: http://bugs.mysql.com/29306) * Read lock requests that were blocked by a pending write lock request were not allowed to proceed if the statement requesting the write lock waskilled. (Bug#21281: http://bugs.mysql.com/21281) * Memory corruption occurred for some queries with a top-level OR operation in the WHERE condition if they contained equality predicates and other sargable predicates in disjunctive parts of the condition. (Bug#30396: http://bugs.mysql.com/30396) * The server created temporary tables for filesort operations in the working directory, not in the directory specified by the tmpdir system variable. (Bug#30287: http://bugs.mysql.com/30287) * Using KILL QUERY or KILL CONNECTION to kill a SELECT statement caused a server crash if the query cache was enabled. (Bug#30201: http://bugs.mysql.com/30201) * Operations that used the time zone replicated the time zone only for successful operations, but did not replicate the time zoneforerrorsthatneedto know it.
Crystal Reports XI on W2k3 Server SP2 x64 (fwd)
[take 2] Nobody has any comments ? -- Forwarded message -- Date: Wed, 26 Sep 2007 12:19:21 -0400 (EDT) From: Philip M. Gollucci [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Crystal Reports XI on W2k3 Server SP2 x64 Hi All, I'm trying to get Crystal Reports to connect to a MySQL server. Windows 2003 Server R2 x64 Crystal Reports XI Professional Disabled DEP for crw32.exe mysql-connector-odbc-noinstall-3.51.20-winx64.zip FreeBSD 6.2-RELEASE-p7 x64 mysql-client-5.0.45 mysql-scripts-5.0.45 mysql-server-5.0.45 GRANT SELECT ON db.* TO 'user'@'apps.domain.tld' identified by 'x'; (note, this is not an authentication problem) 1) I created a SystemDSN via the ODBC Panel and it tests okay -- Crystal Reports can't see it -- this is a seperate PR I need to file with them. 2) I created a UserDSN which Crystal Reports can see BUT IM002:[Microsoft][ODBC Driver Manger] Data source name not found and no default driver specified I've also tried the full odbc connect string only to get the same error message. I filed the following PR with Business Objects (owns Crysal Reports) http://support.businessobjects.com/case_management/viewcase.asp?caseid=302861271; The long and the short is they want me to use 3.51.11! The big question, how do I get this version which is so old its no longer distributed. From googling, I have a hunch they are right but I can't fathom why. -- Philip M. Gollucci ([EMAIL PROTECTED]) 323.219.4708 Senior System Admin - Riderway, Inc. http://riderway.com 1024D/EC88A0BF 0DE5 C55C 6BF3 B235 2DAB B89E 1324 9B4F EC88 A0BF Work like you don't need the money, love like you'll never get hurt, and dance like nobody's watching. -- 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]
