create alias for columns bound to database?
All, I'd like to be able to create column aliases which are bound to the database itself. I have an assortment of columns without naming standards which i'd like to migrate to a better naming scheme. Is there a feature in MySQL that would allow me to give a database column multiple names? I'm thinking that for SELECT * statements, you would use the default column name, but for insert, delete, update, etc, it would be fine to use the aliased name or default. Doing this would *really* help to allow me to migrate the database to the new naming convention without breaking existing code. I would then be able to refactor at a more leisurely pace. Does the feature exist, or can it be created? -- Dante D. Dante Lorenso da...@lorenso.com 972-333-4139 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Trying to avoid bulk insert table locking
I have a system that imports about 40 million records every 2 days into a single table in MySQL. I was having problems with LOAD DATA CONCURRENT LOCAL INFILE where the table I was importing into would lock until the import was complete. Locks would prevent SELECTs also. I converted the table to MyISAM and removed the AUTO_INCREMENT key and that seemed to help a little bit, but apparently not enough because I still get locks for my larger file imports (maybe I just don't see the locks for the smaller imports). So, I think I want to test a new strategy: 1) import records into a temporary table 2) have a merge stored procedure loop through a cursor and migrate batches of records from the temp table to the permanent table in groups of perhaps 500-10,000 records. 3) make sure any acquired locks are released between each batch merged. Has anyone built logic like this already? Care to share your results and findings? Would this approach work, and is it fairly simple to do? -- Dante -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
Johan De Meersman wrote: First things first: You *are* on InnoDB, which has row-level locking instead of table-level ? Yes, both cli_lock and queue tables are InnoDB. The server is running MySQL 5.1.36. I find it strange that I would have so many of these deadlocks throughout a day when these queries run from 3 processes every 20 seconds. What's the chance that 2 scripts should be executing these queries simultaneously, and even if the probability exists, why is it causing this deadlock error each time? If I break the query into 2 parts ... like SELECT FOR UPDATE followed by the INSERT/UPDATE, would that help fix the errors? What is this error exactly, anyhow? Where is the deadlock ... is it on the select or the insert? -- Dante On Mon, Feb 1, 2010 at 4:08 PM, Michael Dykman mdyk...@gmail.com mailto:mdyk...@gmail.com wrote: The query is probably fine.. that is just the lock doing it's job. Take that advice literally.. when you fail with that class of exception, delay a milli-second or two and retry. For a large PHP site I designed, we had that behaviour built-in: up to three attempts waits 5, then 10 ms between trys. In spite of 1M+ user/day we rarely made it to the third attempt. - michael dykman On Mon, Feb 1, 2010 at 9:33 AM, Dante Lorenso da...@lorenso.com mailto:da...@lorenso.com wrote: All, I am trying to create an atomic operation in MySQL that will manage a queue. I want to lock an item from a table for exclusive access by one of my processing threads. I do this by inserting the unique ID of the record I want to reserve into my cli_lock table. The following query is what I am using to lock a record in my queue: INSERT INTO cli_lock (lock_id, object_id, lock_type, expire_dt) SELECT $LOCK_ID, q.queue_id, 'parse', DATE_ADD(NOW(), INTERVAL 1 HOUR) FROM queue q LEFT JOIN cli_lock l ON l.object_id = q.queue_id AND l.lock_type = 'parse' WHERE l.object_id IS NULL AND q.status = 'parse' ORDER BY q.file_size ASC, q.created ASC, q.queue_id ASC LIMIT 1 However, as I execute this query several times each minute from different applications, I frequently get these messages: DB ERROR: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction Am I writing my query wrong or expecting behavior that MySQL doesn't support? -- Dante -- -- D. Dante Lorenso da...@lorenso.com 972-333-4139 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
Michael Dykman wrote: The query is probably fine.. that is just the lock doing it's job. Take that advice literally.. when you fail with that class of exception, delay a milli-second or two and retry. For a large PHP site I designed, we had that behaviour built-in: up to three attempts waits 5, then 10 ms between trys. In spite of 1M+ user/day we rarely made it to the third attempt. Sounds like the answer is that's just the way MySQL is. I don't usually like those kinds of answers. I've written similar queries in another DB and never got these types of errors. Perhaps there is a better way to create a queue system that avoids this problem entirely? I feel like if MySQL is throwing out this wanring to me, that I should be doing to correct it. I have a queue with several states in it: state1 --- processing1 -- state2 --- processing2 --- state3 I want to find a record that is in state1 and reserve the right to process it. After it is done being processed, the code will set it's state to state2 which allows the next application to pick it up and work on it. I am actually using PHP/MySQL and this problem sounds like a job for a message queue. So, in essence, my solution is like a message queue built using MySQL tables to store and manage the queue. Has this problem already been solved in a way I can just leverage the existing solution? ... er, without the deadlock issue. Are you saying I should just ignore the message about deadlock and let the app run as if the message never occurred (since there's not a problem with seeing that message)? -- Dante - michael dykman On Mon, Feb 1, 2010 at 9:33 AM, Dante Lorenso da...@lorenso.com wrote: All, I am trying to create an atomic operation in MySQL that will manage a queue. I want to lock an item from a table for exclusive access by one of my processing threads. I do this by inserting the unique ID of the record I want to reserve into my cli_lock table. The following query is what I am using to lock a record in my queue: INSERT INTO cli_lock (lock_id, object_id, lock_type, expire_dt) SELECT $LOCK_ID, q.queue_id, 'parse', DATE_ADD(NOW(), INTERVAL 1 HOUR) FROM queue q LEFT JOIN cli_lock l ON l.object_id = q.queue_id AND l.lock_type = 'parse' WHERE l.object_id IS NULL AND q.status = 'parse' ORDER BY q.file_size ASC, q.created ASC, q.queue_id ASC LIMIT 1 However, as I execute this query several times each minute from different applications, I frequently get these messages: DB ERROR: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction Am I writing my query wrong or expecting behavior that MySQL doesn't support? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Is anything ever equal to NULL?
Will anything ever be equal to NULL in a SELECT query? SELECT * FROM sometable WHERE somecolumn = NULL; I have a real-life query like this: SELECT * FROM sometable WHERE somecolumn = NULL OR somecolumn = 'abc'; The 'sometable' contains about 40 million records and in this query, it appears that the where clause is doing a sequential scan of the table to find a condition where 'somecolumn' = NULL. Shouldn't the query parser be smart enough to rewrite the above query like this: SELECT * FROM sometable WHERE FALSE OR somecolumn = 'abc'; And therefor use the index I have on 'somecolumn'? When I manually rewrite the query, I get the performance I expect but when I leave it as it was, it's 100 times slower. What's so special about NULL? -- Dante -- D. Dante Lorenso -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Is anything ever equal to NULL?
Well, if nothing can ever equal null, then why isn't MySQL query parser smart enough to reduce my queries to something more sensible? If I'm saying this: SELECT * FROM sometable WHERE somecolumn = NULL OR somecolumn = 'abc'; Why isn't it able to reduce the query to something more like this: SELECT * FROM sometable WHERE somecolumn = 'abc'; Since it already should know that somecolumn = NULL will always evaluate to FALSE (or is it NULL? ... either way, it's not TRUE)? If I run the first query above, the query takes about 15 seconds to run against 40 million records, but if I run the second query, it takes about .050 seconds. The test for NULL seems to cause the query to skip use of an index because I doubt NULL values are indexed. Am I expecting too much of the parser? -- Dante Martijn Tonies wrote: Hi, Will anything ever be equal to NULL in a SELECT query? No, never. Null also means unknown, if you design your tables well enough, there should be no NULLs -stored- (different from a resultset, where there can be nulls, for example in LEFT JOINs), because it's no use to store what you don't know. The only case when you want to store a null is when you do want to -know- you don't know a value. A column can have two states: null or not null. It either has data (a value, depending on the datatype), or no data (null), which is where column IS NULL (has no data) or column IS NOT NULL (has data) comes into play. Null is not the same as empty. An empty string, for example, is not equal to null (which is unknown), you cannot compare anything to what you don't know, which is why your comparison fails. NULL = NULL fails, so does NULL NULL in the strict sense. SELECT * FROM sometable WHERE somecolumn = NULL; I have a real-life query like this: SELECT * FROM sometable WHERE somecolumn = NULL OR somecolumn = 'abc'; The 'sometable' contains about 40 million records and in this query, it appears that the where clause is doing a sequential scan of the table to find a condition where 'somecolumn' = NULL. Shouldn't the query parser be smart enough to rewrite the above query like this: You mean the optimizer, perhaps it should, perhaps it shouldn't. Yet, your query is not really the smartest, as you should avoid writing column = NULL, as this doesn't make sense. SELECT * FROM sometable WHERE FALSE OR somecolumn = 'abc'; And therefor use the index I have on 'somecolumn'? When I manually rewrite the query, I get the performance I expect but when I leave it as it was, it's 100 times slower. What's so special about NULL? Quite a bit ;-) With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- -- D. Dante Lorenso -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How to not lock anything?
All, I am using MySQL currently, but am starting to think that maybe I don't really need to use an RDBMS. The data I am storing ends up getting indexed with Sphinx because I have full-text indexes for about 40 million records. I have an items table that is heavily updated with 40 million records every 1 or 2 days and I need all those items indexed so they can be searched. The problem that I'm having is that the table is constantly locked because an insert or delete is being performed. I am playing with InnoDB vs MyIsam and have been trying to figure out how to get the best performance. I actually don't care about dirty reads, however, and wouldn't mind if all the 40 mm records could be read/inserted/updated/deleted without any locking at all. Are there known solutions for the kind of storage I am looking for? Anyone have any pointers? Is there a MySQL Storage Engine designed for this kind of usage, or is there a another server that is commonly used along with MySQL for this type of thing? -- Dante -- D. Dante Lorenso da...@larkspark.com 972-333-4139 -- -- D. Dante Lorenso da...@lorenso.com 972-333-4139 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: stored procedure and random table name - temp table, merge, prepared statement
Gavin Towey wrote:
Creating a temporary merge table works fine for me on 5.0.
Your table isn't innodb is it? That will fail with an error like you're
getting.
Strange. Now that I am on my home network and trying this again, it
seems to be working.
mysql SELECT version();
+--+
| version()|
+--+
| 5.1.41-community |
+--+
1 row in set (0.00 sec)
Here is my stored procedure:
8
CREATE PROCEDURE `test_massage_table`(IN in_table_name VARCHAR(64))
NOT DETERMINISTIC
MODIFIES SQL DATA
BEGIN
-- remove temp table
DROP TABLE IF EXISTS test_temp;
-- clone table structure from submitted table
SET @s = CONCAT('CREATE TABLE test_temp LIKE ', in_table_name);
PREPARE stmt FROM @s;
EXECUTE stmt;
-- convert table type to MERGE. Pass through to original table
SET @s = CONCAT('ALTER TABLE test_temp ENGINE=MERGE UNION(',
in_table_name, ')');
PREPARE stmt FROM @s;
EXECUTE stmt;
-- test query 1
UPDATE test_temp SET value = value * value;
-- test query 2
UPDATE test_temp SET modified = NOW();
-- test query 3
DELETE FROM test_temp WHERE value 10;
-- test query 4
SELECT *
FROM test_temp;
END;
8
Then, here is the code I used to test it:
8
-- destroy tables
DROP TABLE IF EXISTS test_table_odds;
DROP TABLE IF EXISTS test_table_evens;
DROP TABLE IF EXISTS test_temp;
--
-- create new tables
CREATE TABLE `test_table_odds` (
`value` int(11) unsigned NOT NULL,
`modified` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE test_table_evens LIKE test_table_odds;
--
-- add sample data
INSERT INTO test_table_odds (value) VALUES (1), (3), (5);
INSERT INTO test_table_evens (value) VALUES (2), (4), (6);
--
-- check table
SELECT * FROM test_table_odds;
SELECT * FROM test_table_evens;
--
-- run new procedure stuff
CALL test_massage_table('test_table_odds');
CALL test_massage_table('test_table_evens');
8
And here is my output:
8
mysql -- destroy tables
mysql DROP TABLE IF EXISTS test_table_odds;
Query OK, 0 rows affected (0.00 sec)
mysql DROP TABLE IF EXISTS test_table_evens;
Query OK, 0 rows affected (0.00 sec)
mysql DROP TABLE IF EXISTS test_temp;
Query OK, 0 rows affected (0.00 sec)
mysql --
mysql -- create new tables
mysql CREATE TABLE `test_table_odds` (
- `value` int(11) unsigned NOT NULL,
- `modified` datetime DEFAULT NULL
- ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE test_table_evens LIKE test_table_odds;
--
-- add sample data
INSERT INTO test_table_odds (value) VALUES (1), (3), (5);
Query OK, 0 rows affected (0.00 sec)
mysql CREATE TABLE test_table_evens LIKE test_table_odds;
Query OK, 0 rows affected (0.00 sec)
mysql --
mysql -- add sample data
mysql INSERT INTO test_table_odds (value) VALUES (1), (3), (5);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql INSERT INTO test_table_evens (value) VALUES (2), (4), (6);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql --
mysql -- check table
mysql SELECT * FROM test_table_odds;
+---+--+
| value | modified |
+---+--+
| 1 | NULL |
| 3 | NULL |
| 5 | NULL |
+---+--+
3 rows in set (0.00 sec)
mysql SELECT * FROM test_table_evens;
+---+--+
| value | modified |
+---+--+
| 2 | NULL |
| 4 | NULL |
| 6 | NULL |
+---+--+
3 rows in set (0.00 sec)
mysql --
mysql -- run new procedure stuff
mysql CALL test_massage_table('test_table_odds');
+---+-+
| value | modified|
+---+-+
| 1 | 2009-12-10 21:18:59 |
| 9 | 2009-12-10 21:18:59 |
+---+-+
2 rows in set (0.01 sec)
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql CALL test_massage_table('test_table_evens');
+---+-+
| value | modified|
+---+-+
| 4 | 2009-12-10 21:19:01 |
+---+-+
1 row in set (0.15 sec)
Query OK, 0 rows affected (0.15 sec)
8
So, thanks for the reply. I hope someone can review what I've done here
and let me know if there is a smarter way to accomplish what I'm trying
to do. Otherwise, I'll have to review what I was working on at the
office and figure out why that wasn't working for me. It might be a
mysql version difference, but I'm guessing it was something to do with
the original table being too complex for the Merge table to work. Maybe
indexes not matching?
Anyhow, let me know what you think of this strategy for sidestepping the
need for prepared statements when working with MyISAM tables passed to
Re: which solution is better for $count and @cols
Fayland Lam wrote:
well, we have a where $where, and I want some @cols depends on $start,
$rows. besides, I want $count too.
so we have two solution here.
A, two SQLs.
1, SELECT COUNT(*) FROM table WHERE $where
2, SELECT col FROM table WHERE $where LIMIT $start, $rows.
B one SQLs with some operation
SELECT col FROM table WHERE $where
while $count is scalar @cols and real cols is splice(@cols, $start, $rows)
which solution is better? or it depends on the $count, big count A is
better and small is B?
Use A always. You might get away with using SQL_CALC_FOUND_ROWS, but
I've always found that I need to know the total row count before I run
the query because if you are asking for a $start which is beyond the
$count, I want to modify $start before running the second query.
It ends up being like this:
A, two SQLs
1, SELECT COUNT(*) FROM table WHERE $where
1.5, if ($count $start) { $start = 1; }
2, SELECT col FROM table WHERE $where LIMIT $start, $rows.
Option B is horrible for large result sets. Only drawback to A is the
tediousness of having 2 queries, but you get over that once you develop
a pattern for writing them that way.
-- Dante
--
D. Dante Lorenso
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to build sub-sequence ... AUTO_INCREMENT on a sub-key starting with a specific offset?
All, I am developing a service in MySQL that models a service I've already built in PostgreSQL. I'm trying to port over some of my ideas from that platform to MySQL. Here's the setup: Let's say I have 2 tables: 'account' and 'widget'. Each of these tables have a primary key but the widget table references the account table with a foreign key on account_id: -- CREATE TABLE `account` ( `account_id` INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT, `label` VARCHAR(64) COLLATE latin1_swedish_ci NOT NULL DEFAULT '', PRIMARY KEY (`account_id`), UNIQUE KEY `account_id` (`account_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'; CREATE TABLE `widget` ( `widget_id` INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT, `label` VARCHAR(64) COLLATE latin1_swedish_ci NOT NULL DEFAULT '', `account_id` INTEGER(11) UNSIGNED NOT NULL, `widget_number` INTEGER(11) UNSIGNED NOT NULL, PRIMARY KEY (`widget_id`), UNIQUE KEY `widget_id` (`widget_id`), UNIQUE KEY `widget_number` (`account_id`, `widget_number`), KEY `account_id` (`account_id`), CONSTRAINT `widget_fk_account_id` FOREIGN KEY (`account_id`) REFERENCES `account` (`account_id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'; -- The problem is that I don't want customers to see the 'widget_id' column on the url like this: http://.../app/widget/edit.php?widget_id=123456 Because if they see this, they can use this information to deduce how many widgets exist in my database. Instead, I want to add another column to the widget table called 'widget_number' that contains an AUTO_INCREMENT column which is based on the 'account_id' in that table. What this means is that every account contains a widget number 1000. And if you add another widget, you get widget number 1001, etc. This way, the url will look like this: http://.../app/widget/edit.php?widget_number=1000 And if I combine widget number 1000 with the account_id, I can uniquely identify an entry in the database. In other words, I have a new primary key candidate of (account_id, widget_number). I would use this combo as the primary key, but I hate doing joins with multiple primary keys, so I'll also keep the widget_id for the purpose of making joins easier. My question is how can I get MySQL to automatically assign the widget_number values? I want the first value for every account ID to be 1000 and increment from there. I almost need a trigger that does something like this: SELECT COALESCE(MAX(widget_number) + 1, 1000) INTO NEW.widget_number FROM widget WHERE account_id = NEW.account_id; I don't think the 'MAX' is optimized, though and maybe there is a better, more robust way to do this which is already built into MySQL that I don't know about. I am using InnoDB, so the solution needs to be transaction safe. Help would be appreciated. -- Dante -- D. Dante Lorenso [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Does MySQL have RETURNING in the language?
There's an awesome feature that was added to PostgreSQL a while back called RETURNING that allows you to make an INSERT, UPDATE, and DELETE statement behave like a SELECT statement. You can do something like this: INSERT INTO mytable (id, value) VALUES (1, 'something') RETURNING any_column_you_want; This would be equivalent to running something like this in MySQL: INSERT INTO mytable (id, value) VALUES (1, 'something'); SELECT any_column_you_want FROM mytable WHERE id = 1; Here is another example with an UPDATE query: UPDATE mytable SET value = 'something' WHERE id = 1 RETURNING id, other_number; The nice thing about this is that every insert or update can return any column you want (even multiple columns) without having to do the INSERT/UPDATE then turn around and perform another SELECT query. I want to use this because when I insert a value into a table, I don't always want to get the primary key returned to me. Sometimes I want another column which may contain a candidate key and I'd like to avoid the round-trip and additional logic incurred with running multiple queries. Does RETURNING exist in any current release of MySQL or is it on the TODO list even? If it's not, how can I go about asking to have it put on there? -- Dante -- D. Dante Lorenso [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does MySQL have RETURNING in the language?
Rob Wultsch wrote: On Wed, Oct 15, 2008 at 12:25 PM, D. Dante Lorenso [EMAIL PROTECTED] wrote: There's an awesome feature that was added to PostgreSQL a while back called RETURNING that allows you to make an INSERT, UPDATE, and DELETE statement behave like a SELECT statement. You can do something like this: INSERT INTO mytable (id, value) VALUES (1, 'something') RETURNING any_column_you_want; This would be equivalent to running something like this in MySQL: INSERT INTO mytable (id, value) VALUES (1, 'something'); SELECT any_column_you_want FROM mytable WHERE id = 1; Here is another example with an UPDATE query: UPDATE mytable SET value = 'something' WHERE id = 1 RETURNING id, other_number; The nice thing about this is that every insert or update can return any column you want (even multiple columns) without having to do the INSERT/UPDATE then turn around and perform another SELECT query. I want to use this because when I insert a value into a table, I don't always want to get the primary key returned to me. Sometimes I want another column which may contain a candidate key and I'd like to avoid the round-trip and additional logic incurred with running multiple queries. Does RETURNING exist in any current release of MySQL or is it on the TODO list even? If it's not, how can I go about asking to have it put on there? -- Dante -- D. Dante Lorenso [EMAIL PROTECTED] You can do your insert through a stored procedure and then at the end do a select of those values. http://dev.mysql.com/doc/refman/5.0/en/faqs-stored-procs.html#qandaitem-22-4-14 22.4.14: Can MySQL 5.0 stored routines return result sets? Stored procedures can, but stored functions cannot. If you perform an ordinary SELECT inside a stored procedure, the result set is returned directly to the client. You need to use the MySQL 4.1 (or above) client-server protocol for this to work. This means that — for instance — in PHP, you need to use the mysqli extension rather than the old mysql extension. This is an interesting strategy in that all your queries would turn into CALL statements. There are several reasons why I would NOT want to turn all my queries into stored procedures, though. The main problem I have is that it is difficult to deploy stored procedures from DEV to PROD environments and have those deployments synchronized with the deployment of the web code. SQL which is kept with the application is easily deployed when the application is deployed and the same goes for version control of the SQL if you are using something like Subversion to maintain change history. So, I suppose you CAN perform an UPDATE and run a SELECT from a stored procedure, but this strategy is not much better than doing both calls from the client and still does not act like the RETURNING feature I was hoping for. -- Dante -- D. Dante Lorenso [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does MySQL have RETURNING in the language?
D. Dante Lorenso wrote: There's an awesome feature that was added to PostgreSQL a while back called RETURNING that allows you to make an INSERT, UPDATE, and DELETE statement behave like a SELECT statement. ... Does RETURNING exist in any current release of MySQL or is it on the TODO list even? If it's not, how can I go about asking to have it put on there? For more information on RETURNING for INSERT statements, read a little of this from the PostgreSQL documentation: http://www.postgresql.org/docs/8.3/interactive/sql-insert.html From what I can tell this is unique to PostgreSQL. I really want this functionality in MySQL. Where do I go to ask for it? -- Dante -- D. Dante Lorenso [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why different engines in one database?
How about ... Use InnoDB for all the transaction important data. Maybe you want to create a powerful search for your stock table, though and maybe that table contains millions of records. You can't use full-text search with InnoDB but you can with MyISAM. You could put some of the not-so-important text in the MyISAM table and enable full-text searches there. Sure, the MyISAM table doesn't do cascading deletes, foreign keys, or transactions, but it's just used for searches and isn't a high-priority table. It can be updated once a night at 3am when the database load is lowest. -- Dante hezjing wrote: Hi When and why we create tables in different storage engines within a same database? Take for example a normal inventory application that provides CRUD operation to - USER table (e.g. create new user) - STOCK table (e.g. when there is new stock arrives) - CUSTOMER table (e.g. create new customer) - SALE table (e.g. when a stock is bough by a customer) I think it is always a best choice to use InnoDB since many applications are transactional. How would one wants to create a USER table in MyISAM engine and SALE table in InnoDB engine? Can you give some example? Thank you! -- -- D. Dante Lorenso [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Current state of CONNECT BY support in MySQL?
Many people seem to ask this question periodically but I wanted the most recent answer... What's the current state of having support for CONNECT BY in MySQL? I'm using MySQL 5.0.45 on CentOS 5.2 and noticed in the documentation that I can't write my own recursive functions: Stored functions cannot be recursive. http://dev.mysql.com/doc/refman/5.1/en/stored-routines-syntax.html ... so I guess I can't build my own recursive tree-traversing stored procedures. Is there support in MySQL 5.x, or MySQL 6.x for doing heirarchical queries? Does MySQL now ... or will it later ... support CONNECT BY syntax? Help would be appreciated. -- Dante -- D. Dante Lorenso [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Any easier way to compare mysql schema of 50 databases?!
Uma Bhat wrote: We are in progress of *optimizing* and designing the existing mysql database enviromnent on *linux*. And need help in comaparing schema of 50 databases from the same mysql instance. If you can afford to spend a few dollars to get the right tool, you want to get DB Comparer for MySQL from the folks at EMS: http://www.sqlmanager.net/en/products/mysql/dbcomparer This tool will compare the schemas of 2 MySQL Databases and allow you to selectively choose which changes to make in order to synch to the master or the target DB. I've been using the PostgreSQL version of this tool for many years and just recently started using their MySQL one. -- Dante -- D. Dante Lorenso [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
