temp tables and innodb

[bob]

Interesting issue when playing with the Histogram functionality.

I have a innodb database and the create table statement:



create temporary table histotemp (
label varchar(255),
k int) ;

fails (I kept cutting out more of the histo statement till i narrowed
it down to this line)

the fix for me was:



create temporary table histotemp (
label varchar(255),
k int) engine=myisam;


making the table a myisam table.

using mysql 5.1.34

don't know if anyone else has hit this issue.

[tod Landis]

Hi Bob,
We're looking at this. What error message do you see when
it fails? What Entrance version is this (from the title bar)?
- Tod

[bob]

The database I'm using is called 'cas' and the error message is:

ERROR - Table 'cas.histotemp' doesn't exist

Here is the SQL as formed by the Histogram function:

-- Complete Entrance PLOT syntax is online at:
-- http://dbentrance.com/docs/plotsyntax.html

create temporary table histoTEMP (
label varchar(255),
k int);

Here is the create that makes the txn work:

create temporary table histoTEMP (
label varchar(255),
k int) engine myisam;


my my.cnf has:

default-storage-engine=InnoDB

as the default engine.

[tod Landis]

Probably our bug: The Histogram tool should not mix cases in
the table name "histoTEMP". Try changing it to "histotemp".
If that works for you, we'll have the fix in the next version.
- Tod

[bob]

Nope:

ERROR - Table 'cas.histotemp' doesn't exist

This is on OS X if that helps.

note that this article says that implicit temp tables in innodb don't
work right.

http://forum.percona.com/index.php/m/618/

but it's lacking in more detail..

Note that if I try this under:

solaris:

Database changed
mysql> create temporary table histotemp (
-> label varchar(255),
-> k int);
Query OK, 0 rows affected (0.51 sec)

it works,

under linux:

mysql> create temporary table histotemp (
-> label varchar(255),
-> k int);
Query OK, 0 rows affected (0.06 sec)

ok, and then in OS X:

mysql> create temporary table histotemp (
-> label varchar(255),
-> k int);
ERROR 1146 (42S02): Table 'cas.histotemp' doesn't exist

Weird that it's only my OS X version.

Let me build a new my.cnf and see what's up. I'm currently using
innodb_file_per_table and other strange 'features' of the innodb
engine.

I also get this:

90630 13:21:36 [Warning] Setting lower_case_table_names=2 because file
system for /Users/Bob/Development/mysql/data/ is case insensitive

so let me see why my instance is not working and I'll try again.

thanks for the quick followup.
bobm

[Tod Landis]

Would it help for us to include "ENGINE=MyISAM"
in the CREATE TEMPORARY TABLE generated by the tool?

Doubt that it is OS X-related. We test on
PPC and Intel OS X, and I use Intel OS X heavily.

Related note:.
The Histogram tool is kind of ornery. eg. although it will
let you run the tool with an existing histogram displayed,
it won't build the new histogram unless you have a table
displayed. (This is a bug that will be fixed)
- Tod

Tod Landis
dbEntrance Software
PO Box 2383
Boulder Creek CA 95006
831.338.6967
t...@dbentrance.com
http://dbentrance.com/