I'm doing the equivalent of a materialized view in MySQL 5.0.15 and I've hit a strange error.
Here's the scenario. CREATE OR REPLACE VIEW x_view AS SELECT [select statement, blah, blah, blah]; DROP TABLE IF EXISTS x_mview; CREATE TABLE x_mview SELECT * FROM x_view; That was all working fine until I created a function that I use inside of the view. Now the last statement, the one that would materialize the view, returns a table locking error! ERROR 1100 (HY000): Table 'x_mview' was not locked with LOCK TABLES It's difficult to lock a table that doesn't exist yet... "SELECT * FROM x_view" works fine. The function is a simple bit of logic (it tests a count, moving average and standard deviation to see if the count is more or less than two standard deviations from the mean): CREATE FUNCTION get_signal (cnt MEDIUMINT(8), ma MEDIUMINT(8), stdv MEDIUMINT(8)) RETURNS TINYINT(1) DETERMINISTIC RETURN IF (ma > 9 AND stdv > 0 AND (cnt >= ma + (2 * stdv)) OR cnt <= ma - (2 * stdv), IF (cnt >= ma + (2 * stdv), 1, -1), 0); If this is a bug, I'll be happy to file a report... but I'd really like a solution that will let me use the function. I hit a problem using functions in stored procedures, too, and I'm wondering if these are related. Nick -- Nick Arnett [EMAIL PROTECTED] Messages: 408-904-7198
