Re: Transactions (not rolling back on error)
I think it might have something to do with the fact that I'm running the NDB engine. I'm not sure... It doesn't seem to have the same problem on Inno (another transaction-safe engine) I think I need to send MySQL some info so they can try to duplicate it. I'm wondering if anyone else running cluster has had any issues with transactions??? Anyone? I'd _really_ like to get this working since my work-around is in my web application until I can get it working... Good to see you on this list too James! :) Cory. James Harvard wrote: Hi Cory - nice to see a fellow Lasso user here! I've not use transactions myself but I think you might be having a problem with autocommit. http://dev.mysql.com/doc/refman/5.0/en/commit.html HTH, James Harvard At 12:44 am -0700 15/12/05, Cory @ SkyVantage wrote: I have a transaction that is very simple, I need to create records in multiple tables that are related. If any one insert statement fails or throws an error I want to rollback the ENTIRE transaction. I thought that this was the default functionality, but apparently that's not the case here. I'm running MySQL-Cluster 5.0.something. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Transactions (not rolling back on error)
I have a transaction that is very simple, I need to create records in multiple tables that are related. If any one insert statement fails or throws an error I want to rollback the ENTIRE transaction. I thought that this was the default functionality, but apparently that's not the case here. I'm running MySQL-Cluster 5.0.something. Here's a simplified example.. START TRANSACTION; INSERT INTO; INSERT INTO; INSERT INTO; COMMIT; I'm getting data inserted into some tables when others (or one) throw an error.What am I doing wrong? Do I need to put in a conditional that checks for an error between each statement? If so, what might that look like? I guess the reason this is so important to me is that Cluster doesn't enforce Foriegn Key Rules, so it's important that my relationships are maintained by the transaction properly or else I'll have orphaned records all over the place... Thanks in advance! Cory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database in Thai
UTF-8 is the encoding would want to use for Thai. Also see: http://dev.mysql.com/doc/refman/4.1/en/charset-asian-sets.html Cory. Peter Lauri wrote: I am in the situation to develop an web site for a Thai school and it will be in Thai and English. How can I setup so that my tables understand the Thai decoding (think it is UTF-8)? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to use Logic in View Statment?
I have a similar question. How do you test if the field in question isn't NULL or '' (empty string)? Is the LENGTH a good test or is there a better way? [EMAIL PROTECTED] wrote: LENGTH does exist (as a function): http://dev.mysql.com/doc/refman/5.0/en/string-functions.html and here is how to use the IF() function: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html I believe this is the equivalent statement you wanted: SELECT fieldOne, fieldTwo, IF(CHAR_LENGTH(fieldThree)0,fieldThree,fieldFour) as Company FROM table; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help to concatenate a result...
What I need to do is take this query and this result: SELECT segfees FROM fsf; +-+ | segfees | +-+ | FS=5.00 | | AY=2.50 | | XF=1.75 | | ZP=3.20 | +-+ I make it just return ONE row like this: +-+ | segfees | +-+ | FS=5.00,AY=2.50,XF=1.75,ZP=3.20 | +-+ Any idea how to write a query to return this type of result? (I plan on using it as a subquery, that's why I only want one result) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help to concatenate a result...
Thanks for the tip.. Here's the query that worked... SELECT GROUP_CONCAT(DISTINCT segfees SEPARATOR ',') from segfees WHERE ID_flights_segments=[insert value here] group by ID_flights_segments; inferno wrote: Hi, You can use select group_concat(segfees) from fsf; The information bellow is from mysql manual, but you have to have mysql 4.1.x = * |GROUP_CONCAT(/|expr|/)| This function returns a string result with the concatenated non-|NULL| values from a group. It returns |NULL| if there are no non-|NULL| values. The full syntax is as follows: GROUP_CONCAT([DISTINCT] /|expr|/ [,/|expr|/ ...] [ORDER BY {/|unsigned_integer|/ | /|col_name|/ | /|expr|/} [ASC | DESC] [,/|col_name|/ ...]] [SEPARATOR /|str_val|/]) mysql *|SELECT student_name,|* - *|GROUP_CONCAT(test_score)|* - *|FROM student|* - *|GROUP BY student_name;|* Or: mysql *|SELECT student_name,|* - *|GROUP_CONCAT(DISTINCT test_score|* - *|ORDER BY test_score DESC SEPARATOR ' ')|* - *|FROM student|* - *|GROUP BY student_name;|* In MySQL, you can get the concatenated values of expression combinations. You can eliminate duplicate values by using |DISTINCT|. If you want to sort values in the result, you should use |ORDER BY| clause. To sort in reverse order, add the |DESC| (descending) keyword to the name of the column you are sorting by in the |ORDER BY| clause. The default is ascending order; this may be specified explicitly using the |ASC| keyword. |SEPARATOR| is followed by the string value that should be inserted between values of result. The default is a comma (‘|,|’). You can remove the separator altogether by specifying |SEPARATOR ''|. You can set a maximum allowed length with the |group_concat_max_len| system variable. The syntax to do this at runtime is as follows, where |val| is an unsigned integer: SET [SESSION | GLOBAL] group_concat_max_len = val; If a maximum length has been set, the result is truncated to this maximum length. = Best regards, Cristi Cory @ SkyVantage wrote: What I need to do is take this query and this result: SELECT segfees FROM fsf; +-+ | segfees | +-+ | FS=5.00 | | AY=2.50 | | XF=1.75 | | ZP=3.20 | +-+ I make it just return ONE row like this: +-+ | segfees | +-+ | FS=5.00,AY=2.50,XF=1.75,ZP=3.20 | +-+ Any idea how to write a query to return this type of result? (I plan on using it as a subquery, that's why I only want one result) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OT: Training Materials
MySQL AB offers great training! http://www.mysql.com/training/ In the long run, getting training right from the true experts is going to be your best bang for your buck. Trust me, I know from experience on this one. Cory. Jeffrey G. Ubalde wrote: Anybody here who have conducted MySQL training before? I was wondering if somebody here have training materials/presentation they could share that we can use for internal training within our group. Any presentation related to SQL or RDBMS for that matter would greatly be appriciated. Im am planning to collate all information I can get hold of specially those coming from DB experts in the open source community. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best Fieldtype to store IP address...
I'm using MySQL-Cluster 5.0, and we're doing some research. What is everyone's opinion as to what the best fieldtype to store an IP address in? varchar(16) ? because 16 is the max chars of an ip address... char(16) ? text(16) Not quite sure how to get the best memory utilization... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best field type for exact matches on alphanumeric fields.
I'm runnung MySQL-Cluster 5.0.15 on Linux. I have to do some re-design of a website database that has quickly outgrown itself. I'm trying to migrate to MySQL Cluster 5.0.15. Mostly I've used VarChar fieldtypes in the past, but I want to make sure I'm using the best fieldtype for alphanumeric indexed fields. Some of the data I'm storing will be fixed in length and some will be variable in length. I've thought of just going to TINYTEXT and TEXT fields. I'm really wanting good memory utilization and speed. Perhaps there's a way to do this and get a reasonable combo of both. Are there any tutorials for setting up MySQL Field Types (for a somewhat-newbie to advanced database design) on the net? Cory @ SkyVantage -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best field type for exact matches on alphanumeric fields.
I'm runnung MySQL-Cluster 5.0.15 on Linux. I have to do some re-design of a website database that has quickly outgrown itself. I'm trying to migrate to MySQL Cluster 5.0.15. Mostly I've used VarChar fieldtypes in the past, but I want to make sure I'm using the best fieldtype for alphanumeric indexed fields. Some of the data I'm storing will be fixed in length and some will be variable in length. I've thought of just going to TINYTEXT and TEXT fields. I'm really wanting good memory utilization and speed. Perhaps there's a way to do this and get a reasonable combo of both. Are there any tutorials for setting up MySQL Field Types (for a somewhat-newbie to advanced database design) on the net? Cory @ SkyVantage -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best field type for exact matches on alphanumeric fields.
We were storing relational data all in one field in XML. We now have a need to search through that data and we're now doing fulltext searches. And as you can imagine, this is getting painfully slow. We're now designing a true normalized database (well, normalized within reason) with table relations rather than storing the XML. We're using cluster because we need a very fast and highly available setup. We're beyond that decision. So if TEXT fieldtypes are stored in a separate table, and Varchar fields are max length. What am I to do? Why are TINYTEXT fields not useful? If I setup the fieldtypes with the limiter 'varchar(6)' it'll still store the maxlength? [EMAIL PROTECTED] wrote: I have to do some re-design of a website database that has quickly outgrown itself. I'm trying to migrate to MySQL Cluster 5.0.15. Outgrown size or performance? Cluster 5.0 is memory based, databases are limited to a few gigs. Throughput can be good if application is _massively_ parallel. But the unique feature of Cluster is redundancy. Mostly I've used VarChar fieldtypes in the past, but I want to make sure I'm using the best fieldtype for alphanumeric indexed fields. In Cluster 5.0 Varchar is stored with maximum length. This changes in 5.0. For indexing, non-binary character sets have high overhead. Some of the data I'm storing will be fixed in length and some will be variable in length. I've thought of just going to TINYTEXT and TEXT fields. TINYTEXT is not useful, just use Varchar. TEXT is a form of BLOB and the bulk of it (in Cluster) is stored in a separate table. This is usually not efficient. I hope this helps to decide if Cluster is right solution for you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]