Temporary table creation fails
Hi All I am hoping someone can point me in the right direction. We have a mysql 5.0 database which is giving us hassles when trying to create a temporary table. The creation works perfectly on the slave machine as well as all our other db's, however on the one specific main server we are getting the message ERROR 1005 (HY000): Can't create table '/tmp/#sql4a27_68eed1_0.frm' (errno: -1) We have been trying to figure this out for more than a week now with no luck. When looking in the mysql error.log file, the innodb states that there is either another mysqld running (confirmed and not the case) or it is a filesystem permissions problem. We have checked several times and the permissions on the /tmp filesystem is the same as on all servers. Any suggestions would be greatly appreciated as google didnt help much either. Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Temporary table creation fails
try this command and see if you can get more info about the error show innodb status\G On Mon, Sep 10, 2012 at 2:25 PM, Machiel Richards - Gmail machiel.richa...@gmail.com wrote: Hi All I am hoping someone can point me in the right direction. We have a mysql 5.0 database which is giving us hassles when trying to create a temporary table. The creation works perfectly on the slave machine as well as all our other db's, however on the one specific main server we are getting the message ERROR 1005 (HY000): Can't create table '/tmp/#sql4a27_68eed1_0.frm' (errno: -1) We have been trying to figure this out for more than a week now with no luck. When looking in the mysql error.log file, the innodb states that there is either another mysqld running (confirmed and not the case) or it is a filesystem permissions problem. We have checked several times and the permissions on the /tmp filesystem is the same as on all servers. Any suggestions would be greatly appreciated as google didnt help much either. Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Temporary table creation fails
Hi We tried that as well, however the databases are quite busy and either other transactions overwrite the info, or there is nothing logged. We even tried running the create statement and immediately running Show innodb status, but nothing for that statement. Regards On 09/10/2012 11:05 AM, Ananda Kumar wrote: try this command and see if you can get more info about the error show innodb status\G On Mon, Sep 10, 2012 at 2:25 PM, Machiel Richards - Gmail machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com wrote: Hi All I am hoping someone can point me in the right direction. We have a mysql 5.0 database which is giving us hassles when trying to create a temporary table. The creation works perfectly on the slave machine as well as all our other db's, however on the one specific main server we are getting the message ERROR 1005 (HY000): Can't create table '/tmp/#sql4a27_68eed1_0.frm' (errno: -1) We have been trying to figure this out for more than a week now with no luck. When looking in the mysql error.log file, the innodb states that there is either another mysqld running (confirmed and not the case) or it is a filesystem permissions problem. We have checked several times and the permissions on the /tmp filesystem is the same as on all servers. Any suggestions would be greatly appreciated as google didnt help much either. Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
Re: Temporary table creation fails
can you trying setting sort_buffer_size to big value at your session level and create the table On Mon, Sep 10, 2012 at 2:54 PM, Machiel Richards - Gmail machiel.richa...@gmail.com wrote: Hi We tried that as well, however the databases are quite busy and either other transactions overwrite the info, or there is nothing logged. We even tried running the create statement and immediately running Show innodb status, but nothing for that statement. Regards On 09/10/2012 11:05 AM, Ananda Kumar wrote: try this command and see if you can get more info about the error show innodb status\G On Mon, Sep 10, 2012 at 2:25 PM, Machiel Richards - Gmail machiel.richa...@gmail.com wrote: Hi All I am hoping someone can point me in the right direction. We have a mysql 5.0 database which is giving us hassles when trying to create a temporary table. The creation works perfectly on the slave machine as well as all our other db's, however on the one specific main server we are getting the message ERROR 1005 (HY000): Can't create table '/tmp/#sql4a27_68eed1_0.frm' (errno: -1) We have been trying to figure this out for more than a week now with no luck. When looking in the mysql error.log file, the innodb states that there is either another mysqld running (confirmed and not the case) or it is a filesystem permissions problem. We have checked several times and the permissions on the /tmp filesystem is the same as on all servers. Any suggestions would be greatly appreciated as google didnt help much either. Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Temporary table creation fails
Hi, the sort_buffer_size was set to 8Mb as well as 32M for the session (currently 1M) and retried with same result. On 09/10/2012 11:55 AM, Ananda Kumar wrote: can you trying setting sort_buffer_size to big value at your session level and create the table On Mon, Sep 10, 2012 at 2:54 PM, Machiel Richards - Gmail machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com wrote: Hi We tried that as well, however the databases are quite busy and either other transactions overwrite the info, or there is nothing logged. We even tried running the create statement and immediately running Show innodb status, but nothing for that statement. Regards On 09/10/2012 11:05 AM, Ananda Kumar wrote: try this command and see if you can get more info about the error show innodb status\G On Mon, Sep 10, 2012 at 2:25 PM, Machiel Richards - Gmail machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com wrote: Hi All I am hoping someone can point me in the right direction. We have a mysql 5.0 database which is giving us hassles when trying to create a temporary table. The creation works perfectly on the slave machine as well as all our other db's, however on the one specific main server we are getting the message ERROR 1005 (HY000): Can't create table '/tmp/#sql4a27_68eed1_0.frm' (errno: -1) We have been trying to figure this out for more than a week now with no luck. When looking in the mysql error.log file, the innodb states that there is either another mysqld running (confirmed and not the case) or it is a filesystem permissions problem. We have checked several times and the permissions on the /tmp filesystem is the same as on all servers. Any suggestions would be greatly appreciated as google didnt help much either. Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
Re: Temporary table creation fails
start with 500MB and try On Mon, Sep 10, 2012 at 3:31 PM, Machiel Richards - Gmail machiel.richa...@gmail.com wrote: Hi, the sort_buffer_size was set to 8Mb as well as 32M for the session (currently 1M) and retried with same result. On 09/10/2012 11:55 AM, Ananda Kumar wrote: can you trying setting sort_buffer_size to big value at your session level and create the table On Mon, Sep 10, 2012 at 2:54 PM, Machiel Richards - Gmail machiel.richa...@gmail.com wrote: Hi We tried that as well, however the databases are quite busy and either other transactions overwrite the info, or there is nothing logged. We even tried running the create statement and immediately running Show innodb status, but nothing for that statement. Regards On 09/10/2012 11:05 AM, Ananda Kumar wrote: try this command and see if you can get more info about the error show innodb status\G On Mon, Sep 10, 2012 at 2:25 PM, Machiel Richards - Gmail machiel.richa...@gmail.com wrote: Hi All I am hoping someone can point me in the right direction. We have a mysql 5.0 database which is giving us hassles when trying to create a temporary table. The creation works perfectly on the slave machine as well as all our other db's, however on the one specific main server we are getting the message ERROR 1005 (HY000): Can't create table '/tmp/#sql4a27_68eed1_0.frm' (errno: -1) We have been trying to figure this out for more than a week now with no luck. When looking in the mysql error.log file, the innodb states that there is either another mysqld running (confirmed and not the case) or it is a filesystem permissions problem. We have checked several times and the permissions on the /tmp filesystem is the same as on all servers. Any suggestions would be greatly appreciated as google didnt help much either. Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Temporary table creation fails
Hi, still no luck, same error being given immediately after pressing enter. On 09/10/2012 12:02 PM, Ananda Kumar wrote: start with 500MB and try On Mon, Sep 10, 2012 at 3:31 PM, Machiel Richards - Gmail machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com wrote: Hi, the sort_buffer_size was set to 8Mb as well as 32M for the session (currently 1M) and retried with same result. On 09/10/2012 11:55 AM, Ananda Kumar wrote: can you trying setting sort_buffer_size to big value at your session level and create the table On Mon, Sep 10, 2012 at 2:54 PM, Machiel Richards - Gmail machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com wrote: Hi We tried that as well, however the databases are quite busy and either other transactions overwrite the info, or there is nothing logged. We even tried running the create statement and immediately running Show innodb status, but nothing for that statement. Regards On 09/10/2012 11:05 AM, Ananda Kumar wrote: try this command and see if you can get more info about the error show innodb status\G On Mon, Sep 10, 2012 at 2:25 PM, Machiel Richards - Gmail machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com wrote: Hi All I am hoping someone can point me in the right direction. We have a mysql 5.0 database which is giving us hassles when trying to create a temporary table. The creation works perfectly on the slave machine as well as all our other db's, however on the one specific main server we are getting the message ERROR 1005 (HY000): Can't create table '/tmp/#sql4a27_68eed1_0.frm' (errno: -1) We have been trying to figure this out for more than a week now with no luck. When looking in the mysql error.log file, the innodb states that there is either another mysqld running (confirmed and not the case) or it is a filesystem permissions problem. We have checked several times and the permissions on the /tmp filesystem is the same as on all servers. Any suggestions would be greatly appreciated as google didnt help much either. Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
Re: Temporary table creation fails
Hi, If you dont have data on the server, would you please initialize the data directory. Use mysql-install-db and give proper data directory and proper cnf file if you are giving so. Also specify the user as root if you have root access. Thanks On Mon, Sep 10, 2012 at 3:34 PM, Machiel Richards - Gmail machiel.richa...@gmail.com wrote: Hi, still no luck, same error being given immediately after pressing enter. On 09/10/2012 12:02 PM, Ananda Kumar wrote: start with 500MB and try On Mon, Sep 10, 2012 at 3:31 PM, Machiel Richards - Gmail machiel.richa...@gmail.com mailto:machiel.richards@**gmail.commachiel.richa...@gmail.com wrote: Hi, the sort_buffer_size was set to 8Mb as well as 32M for the session (currently 1M) and retried with same result. On 09/10/2012 11:55 AM, Ananda Kumar wrote: can you trying setting sort_buffer_size to big value at your session level and create the table On Mon, Sep 10, 2012 at 2:54 PM, Machiel Richards - Gmail machiel.richa...@gmail.com mailto:machiel.richards@**gmail.commachiel.richa...@gmail.com wrote: Hi We tried that as well, however the databases are quite busy and either other transactions overwrite the info, or there is nothing logged. We even tried running the create statement and immediately running Show innodb status, but nothing for that statement. Regards On 09/10/2012 11:05 AM, Ananda Kumar wrote: try this command and see if you can get more info about the error show innodb status\G On Mon, Sep 10, 2012 at 2:25 PM, Machiel Richards - Gmail machiel.richa...@gmail.com mailto:machiel.richards@**gmail.commachiel.richa...@gmail.com wrote: Hi All I am hoping someone can point me in the right direction. We have a mysql 5.0 database which is giving us hassles when trying to create a temporary table. The creation works perfectly on the slave machine as well as all our other db's, however on the one specific main server we are getting the message ERROR 1005 (HY000): Can't create table '/tmp/#sql4a27_68eed1_0.frm' (errno: -1) We have been trying to figure this out for more than a week now with no luck. When looking in the mysql error.log file, the innodb states that there is either another mysqld running (confirmed and not the case) or it is a filesystem permissions problem. We have checked several times and the permissions on the /tmp filesystem is the same as on all servers. Any suggestions would be greatly appreciated as google didnt help much either. Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
Re: Temporary table creation fails
This is a current production database with about 100Gb + of data and the DB is extremely busy. On 09/10/2012 12:08 PM, Akshay Suryavanshi wrote: Hi, If you dont have data on the server, would you please initialize the data directory. Use mysql-install-db and give proper data directory and proper cnf file if you are giving so. Also specify the user as root if you have root access. Thanks On Mon, Sep 10, 2012 at 3:34 PM, Machiel Richards - Gmail machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com wrote: Hi, still no luck, same error being given immediately after pressing enter. On 09/10/2012 12:02 PM, Ananda Kumar wrote: start with 500MB and try On Mon, Sep 10, 2012 at 3:31 PM, Machiel Richards - Gmail machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com wrote: Hi, the sort_buffer_size was set to 8Mb as well as 32M for the session (currently 1M) and retried with same result. On 09/10/2012 11:55 AM, Ananda Kumar wrote: can you trying setting sort_buffer_size to big value at your session level and create the table On Mon, Sep 10, 2012 at 2:54 PM, Machiel Richards - Gmail machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com wrote: Hi We tried that as well, however the databases are quite busy and either other transactions overwrite the info, or there is nothing logged. We even tried running the create statement and immediately running Show innodb status, but nothing for that statement. Regards On 09/10/2012 11:05 AM, Ananda Kumar wrote: try this command and see if you can get more info about the error show innodb status\G On Mon, Sep 10, 2012 at 2:25 PM, Machiel Richards - Gmail machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com wrote: Hi All I am hoping someone can point me in the right direction. We have a mysql 5.0 database which is giving us hassles when trying to create a temporary table. The creation works perfectly on the slave machine as well as all our other db's, however on the one specific main server we are getting the message ERROR 1005 (HY000): Can't create table '/tmp/#sql4a27_68eed1_0.frm' (errno: -1) We have been trying to figure this out for more than a week now with no luck. When looking in the mysql error.log file, the innodb states that there is either another mysqld running (confirmed and not the case) or it is a filesystem permissions problem. We have checked several times and the permissions on the /tmp filesystem is the same as on all servers. Any suggestions would be greatly appreciated as google didnt help much either. Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
Re: Temporary table creation fails
the message ERROR 1005 (HY000): Can't create table '/tmp/#sql4a27_68eed1_0.frm' (errno: -1) Basics first: 1) Is the /tmp directory write readable for the user mysql runs as? 2) Has the /tmp directory enough free space? -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Temporary table creation fails
Hi, We confirmed that the /tmp directory permissions is set to rwxrwxrwxt and is owned by root , the same as all our other servers. There is also about 60Gb of free space on the filesystem where /tmp resides. Regards On 09/10/2012 01:11 PM, Rik Wasmus wrote: the message ERROR 1005 (HY000): Can't create table '/tmp/#sql4a27_68eed1_0.frm' (errno: -1) Basics first: 1) Is the /tmp directory write readable for the user mysql runs as? 2) Has the /tmp directory enough free space? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Temporary table creation fails
this temp table will hold how many rows, what would be its size. On Mon, Sep 10, 2012 at 5:03 PM, Machiel Richards - Gmail machiel.richa...@gmail.com wrote: Hi, We confirmed that the /tmp directory permissions is set to rwxrwxrwxt and is owned by root , the same as all our other servers. There is also about 60Gb of free space on the filesystem where /tmp resides. Regards On 09/10/2012 01:11 PM, Rik Wasmus wrote: the message ERROR 1005 (HY000): Can't create table '/tmp/#sql4a27_68eed1_0.frm' (errno: -1) Basics first: 1) Is the /tmp directory write readable for the user mysql runs as? 2) Has the /tmp directory enough free space? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Temporary table creation fails
Hi at the moment this does not really matter to us. we have even tried to create a temp table with only one field in order to insert one row for testing, but we are currently not able to create any temporary tables whatsoever as even the simplest form of table still gives the same error. Regards On 09/10/2012 02:33 PM, Ananda Kumar wrote: this temp table will hold how many rows, what would be its size. On Mon, Sep 10, 2012 at 5:03 PM, Machiel Richards - Gmail machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com wrote: Hi, We confirmed that the /tmp directory permissions is set to rwxrwxrwxt and is owned by root , the same as all our other servers. There is also about 60Gb of free space on the filesystem where /tmp resides. Regards On 09/10/2012 01:11 PM, Rik Wasmus wrote: the message ERROR 1005 (HY000): Can't create table '/tmp/#sql4a27_68eed1_0.frm' (errno: -1) Basics first: 1) Is the /tmp directory write readable for the user mysql runs as? 2) Has the /tmp directory enough free space? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
Re: Temporary table creation fails
did u check if there any firewall settings, forbidding you to create files, check if SELinux is disabled On Mon, Sep 10, 2012 at 6:08 PM, Machiel Richards - Gmail machiel.richa...@gmail.com wrote: Hi at the moment this does not really matter to us. we have even tried to create a temp table with only one field in order to insert one row for testing, but we are currently not able to create any temporary tables whatsoever as even the simplest form of table still gives the same error. Regards On 09/10/2012 02:33 PM, Ananda Kumar wrote: this temp table will hold how many rows, what would be its size. On Mon, Sep 10, 2012 at 5:03 PM, Machiel Richards - Gmail machiel.richa...@gmail.com wrote: Hi, We confirmed that the /tmp directory permissions is set to rwxrwxrwxt and is owned by root , the same as all our other servers. There is also about 60Gb of free space on the filesystem where /tmp resides. Regards On 09/10/2012 01:11 PM, Rik Wasmus wrote: the message ERROR 1005 (HY000): Can't create table '/tmp/#sql4a27_68eed1_0.frm' (errno: -1) Basics first: 1) Is the /tmp directory write readable for the user mysql runs as? 2) Has the /tmp directory enough free space? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Temporary table creation fails
Hi, If you can afford try changing the tmpdir for mysql. This is a static variable and will require a mysql restart. thanks On Mon, Sep 10, 2012 at 6:08 PM, Machiel Richards - Gmail machiel.richa...@gmail.com wrote: Hi at the moment this does not really matter to us. we have even tried to create a temp table with only one field in order to insert one row for testing, but we are currently not able to create any temporary tables whatsoever as even the simplest form of table still gives the same error. Regards On 09/10/2012 02:33 PM, Ananda Kumar wrote: this temp table will hold how many rows, what would be its size. On Mon, Sep 10, 2012 at 5:03 PM, Machiel Richards - Gmail machiel.richa...@gmail.com mailto:machiel.richards@**gmail.commachiel.richa...@gmail.com wrote: Hi, We confirmed that the /tmp directory permissions is set to rwxrwxrwxt and is owned by root , the same as all our other servers. There is also about 60Gb of free space on the filesystem where /tmp resides. Regards On 09/10/2012 01:11 PM, Rik Wasmus wrote: the message ERROR 1005 (HY000): Can't create table '/tmp/#sql4a27_68eed1_0.frm' (errno: -1) Basics first: 1) Is the /tmp directory write readable for the user mysql runs as? 2) Has the /tmp directory enough free space? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
Re: Temporary table creation fails
no selinux , checked this as well. We generally dont use selinux and disable it completely from installation. I have also gone through the firewall settings and that is only rules for connections. On 09/10/2012 02:40 PM, Ananda Kumar wrote: did u check if there any firewall settings, forbidding you to create files, check if SELinux is disabled On Mon, Sep 10, 2012 at 6:08 PM, Machiel Richards - Gmail machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com wrote: Hi at the moment this does not really matter to us. we have even tried to create a temp table with only one field in order to insert one row for testing, but we are currently not able to create any temporary tables whatsoever as even the simplest form of table still gives the same error. Regards On 09/10/2012 02:33 PM, Ananda Kumar wrote: this temp table will hold how many rows, what would be its size. On Mon, Sep 10, 2012 at 5:03 PM, Machiel Richards - Gmail machiel.richa...@gmail.com mailto:machiel.richa...@gmail.com wrote: Hi, We confirmed that the /tmp directory permissions is set to rwxrwxrwxt and is owned by root , the same as all our other servers. There is also about 60Gb of free space on the filesystem where /tmp resides. Regards On 09/10/2012 01:11 PM, Rik Wasmus wrote: the message ERROR 1005 (HY000): Can't create table '/tmp/#sql4a27_68eed1_0.frm' (errno: -1) Basics first: 1) Is the /tmp directory write readable for the user mysql runs as? 2) Has the /tmp directory enough free space? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
Re: Temporary table creation fails
please share the command ur using to create the temp table On Mon, Sep 10, 2012 at 6:11 PM, Machiel Richards - Gmail machiel.richa...@gmail.com wrote: no selinux , checked this as well. We generally dont use selinux and disable it completely from installation. I have also gone through the firewall settings and that is only rules for connections. On 09/10/2012 02:40 PM, Ananda Kumar wrote: did u check if there any firewall settings, forbidding you to create files, check if SELinux is disabled On Mon, Sep 10, 2012 at 6:08 PM, Machiel Richards - Gmail machiel.richa...@gmail.com wrote: Hi at the moment this does not really matter to us. we have even tried to create a temp table with only one field in order to insert one row for testing, but we are currently not able to create any temporary tables whatsoever as even the simplest form of table still gives the same error. Regards On 09/10/2012 02:33 PM, Ananda Kumar wrote: this temp table will hold how many rows, what would be its size. On Mon, Sep 10, 2012 at 5:03 PM, Machiel Richards - Gmail machiel.richa...@gmail.com wrote: Hi, We confirmed that the /tmp directory permissions is set to rwxrwxrwxt and is owned by root , the same as all our other servers. There is also about 60Gb of free space on the filesystem where /tmp resides. Regards On 09/10/2012 01:11 PM, Rik Wasmus wrote: the message ERROR 1005 (HY000): Can't create table '/tmp/#sql4a27_68eed1_0.frm' (errno: -1) Basics first: 1) Is the /tmp directory write readable for the user mysql runs as? 2) Has the /tmp directory enough free space? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Temporary table creation fails
Apologies if I missed this in the thread but have you confirmed not only the effectve perms for the directory but that another user can write to this dirrectory? Perhaps outside of mysql for instance; I.e. other processes are successfully writting logs to /tmp? Anything to share from the mysql logs perhaps? Sent from Yahoo! Mail on Android
Re: Temporary table creation fails
Hi, permissions are confirmed as being correct. Other applications and users are currently writing files to this directory yes. The only thing found in the log is innodb error 13 stating that either a second mysqld is running or that there is a filesystem permissions issue. I have now changed the mysql tmp directory and will be restarting tonight to let it take effect. Will keep everyone updated tomorrow. Regards On 09/10/2012 03:32 PM, Garot Conklin wrote: Apologies if I missed this in the thread but have you confirmed not only the effectve perms for the directory but that another user can write to this dirrectory? Perhaps outside of mysql for instance; I.e. other processes are successfully writting logs to /tmp? Anything to share from the mysql logs perhaps? Sent from Yahoo! Mail on Android *From: * Machiel Richards - Gmail machiel.richa...@gmail.com; *To: * Ananda Kumar anan...@gmail.com; *Cc: * mysql@lists.mysql.com; *Subject: * Re: Temporary table creation fails *Sent: * Mon, Sep 10, 2012 12:41:51 PM no selinux , checked this as well. We generally dont use selinux and disable it completely from installation. I have also gone through the firewall settings and that is only rules for connections. On 09/10/2012 02:40 PM, Ananda Kumar wrote: did u check if there any firewall settings, forbidding you to create files, check if SELinux is disabled On Mon, Sep 10, 2012 at 6:08 PM, Machiel Richards - Gmail machiel.richa...@gmail.com javascript:return mailto:machiel.richa...@gmail.com javascript:return wrote: Hi at the moment this does not really matter to us. we have even tried to create a temp table with only one field in order to insert one row for testing, but we are currently not able to create any temporary tables whatsoever as even the simplest form of table still gives the same error. Regards On 09/10/2012 02:33 PM, Ananda Kumar wrote: this temp table will hold how many rows, what would be its size. On Mon, Sep 10, 2012 at 5:03 PM, Machiel Richards - Gmail machiel.richa...@gmail.com javascript:return mailto:machiel.richa...@gmail.com javascript:return wrote: Hi, We confirmed that the /tmp directory permissions is set to rwxrwxrwxt and is owned by root , the same as all our other servers. There is also about 60Gb of free space on the filesystem where /tmp resides. Regards On 09/10/2012 01:11 PM, Rik Wasmus wrote: the message ERROR 1005 (HY000): Can't create table '/tmp/#sql4a27_68eed1_0.frm' (errno: -1) Basics first: 1) Is the /tmp directory write readable for the user mysql runs as? 2) Has the /tmp directory enough free space? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
Re: Temporary table creation fails
2012/9/10 Machiel Richards - Gmail machiel.richa...@gmail.com Hi, permissions are confirmed as being correct. Other applications and users are currently writing files to this directory yes. Have you tried su - mysql and touch /tmp/test? (if your mysql user has shell...) Good luck! Manuel.
Re: Temporary table creation fails
Not to beat the perms to death but /tmp should have the sticky bit set as well... so 1777 not just 0777. Perhaps hard kill any lingering mysql PIDS unless this is production and u expect other DB's to be running... if u have duplicated this DB schema somewhow by mistake and a second or first identical instance is running it is plausable to assume that the other running instance has locked the file it is using preventing it from being written to. Sent from Yahoo! Mail on Android
Re: Temporary table creation fails
On 9/10/2012 9:55 AM, Garot Conklin wrote: Not to beat the perms to death but /tmp should have the sticky bit set as well... so 1777 not just 0777. Perhaps hard kill any lingering mysql PIDS unless this is production and u expect other DB's to be running... if u have duplicated this DB schema somewhow by mistake and a second or first identical instance is running it is plausable to assume that the other running instance has locked the file it is using preventing it from being written to. Excellent point! Yes, you can have multiple MySQL instances running on the same host machine but they cannot share the same --tmpdir location. Name collisions can and do occur between them if they attempt to share a common folder. Verify that you have properly isolated each instance from every other by following these guidelines: http://dev.mysql.com/doc/refman/5.5/en/multiple-servers.html -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Join between columns with unique keys randomly uses the keys, or uses a temporary table (which fails).
Hi all I have this query: SELECT n.ID, n.CatalogNumber, [...more...], d.ID, d.CatalogNumber, [...more...] FROM newdarwincoredata n INNER JOIN darwincoredata d ON n.CatalogNumber = d.CatalogNumber ORDER BY n.CatalogNumber; Both tables have exactly the same structure and indices: mysql SHOW CREATE TABLE darwincoredata; CREATE TABLE `darwincoredata` ( `ID` int(10) NOT NULL auto_increment, `CatalogNumber` varchar(20) NOT NULL, [...more...], PRIMARY KEY (`ID`), UNIQUE KEY `CatalogNumber` (`CatalogNumber`), UNIQUE KEY `GlobalUniqueIdentifier` (`GlobalUniqueIdentifier`), KEY `DateLastModified` (`DateLastModified`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 There are 336983 rows in darwincoredata, and 337304 in newdarwincoredata. The plan for the query varies between using the CatalogNumber index and using a temporary table (which fails, once it fills up the disk). I get either this: mysql EXPLAIN that query ++-+---+--+---+---+-+--++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+---+-+--++-+ | 1 | SIMPLE | d | ALL | CatalogNumber | NULL | NULL| NULL | 336238 | Using temporary; Using filesort | | 1 | SIMPLE | n | ref | CatalogNumber | CatalogNumber | 22 | GBIF_wrapper.d.CatalogNumber | 1 | | ++-+---+--+---+---+-+--++-+ Or this: ++-+---+---+---+---+-+--++---+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+---+---+-+--++---+ | 1 | SIMPLE | d | index | CatalogNumber | CatalogNumber | 22 | NULL | 326508 | | | 1 | SIMPLE | n | ref | CatalogNumber | CatalogNumber | 22 | GBIF_wrapper.d.CatalogNumber | 1 | | ++-+---+---+---+---+-+--++---+ Executing SHOW INDEX IN newdarwincoredata; SHOW INDEX IN darwincoredata; And then doing the EXPLAIN again sometimes changes the plan. I assume this is because the cardinalities are recalculated. I expect the CatalogNumber unique keys to always be used for this query. Even if I do: SELECT [...] FROM newdarwincoredata n FORCE INDEX (CatalogNumber) INNER JOIN darwincoredata d FORCE INDEX (CatalogNumber) ON n.CatalogNumber = d.CatalogNumber ORDER BY n.CatalogNumber The query still sometimes uses a temporary table. EXPLAIN SELECT STRAIGHT_JOIN [...] FROM newdarwincoredata n INNER JOIN darwincoredata d ON n.CatalogNumber = d.CatalogNumber ORDER BY n.CatalogNumber ++-+---+---+---+---+-+--++---+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+---+---+-+--++---+ | 1 | SIMPLE | n | index | CatalogNumber | CatalogNumber | 22 | NULL | 336588 | | | 1 | SIMPLE | d | ref | CatalogNumber | CatalogNumber | 22 | GBIF_wrapper.n.CatalogNumber | 1 | | ++-+---+---+---+---+-+--++---+ This seems to always use the index, but I don't want to rely on this without knowing why -- might it be because n has more rows than d? MySQL version is 5.0.22-log, x86_64, redhat-linux-gnu. Thanks for any insights Matt Blissett
Re: load data into temporary table
Hi, mysql create temporary table t(i int); mysql \! echo 1 /tmp/data.txt mysql load data infile '/tmp/data.txt' into table t; Query OK, 1 row affected (0.00 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: 0 mysql select * from t; +--+ | i| +--+ |1 | +--+ 1 row in set (0.00 sec) Best Regards, -Janek, CMDEV 5.0. StudyLink. Helping People Realise Their Potential. http://studylink.com On Tue, 2009-05-19 at 02:57 +0100, Alex K wrote: Hello, Would anyone know how to load data infile into a temporary table? Thank you, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: load data into temporary table
Thank you but the real problem occurs when you don't know the schema of the table in advance. If data.txt has two columns columns how can I still load it in a temporary table? I'm asking this question because I'd like to add an import csv feature to a web application. I know that you can load data infile into table without specifying the schema of this table but it does not look like you can do load data infile into a temporary table. Thank you, Alex 2009/5/19 Janek Bogucki janek.bogu...@studylink.com: Hi, mysql create temporary table t(i int); mysql \! echo 1 /tmp/data.txt mysql load data infile '/tmp/data.txt' into table t; Query OK, 1 row affected (0.00 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: 0 mysql select * from t; +--+ | i | +--+ | 1 | +--+ 1 row in set (0.00 sec) Best Regards, -Janek, CMDEV 5.0. StudyLink. Helping People Realise Their Potential. http://studylink.com On Tue, 2009-05-19 at 02:57 +0100, Alex K wrote: Hello, Would anyone know how to load data infile into a temporary table? Thank you, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=alex.ksi...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: load data into temporary table
Hi Alex, It is true that use LOAD DATA INFILE you do need to know the schema of the table. I'm not sure how useful it would be to import arbitrary data if you don't have some expectations about what that data is. There are a couple options for you: 1. Make sure your users upload a CSV is a specific format, reject non-conforming input. 2. Let your script transform the user uploaded CSV file into the format the database is expecting. 3. Have your script simply parse the user uploaded CSV and generate insert statements as needed. Just because you want to accept CSV from your app, does not mean you must use LOAD DATA INFILE to get the data into MySQL. Regards, Gavin Towey -Original Message- From: alex.ksi...@gmail.com [mailto:alex.ksi...@gmail.com] On Behalf Of Alex K Sent: Tuesday, May 19, 2009 5:43 AM To: MySQL General List Subject: Re: load data into temporary table Thank you but the real problem occurs when you don't know the schema of the table in advance. If data.txt has two columns columns how can I still load it in a temporary table? I'm asking this question because I'd like to add an import csv feature to a web application. I know that you can load data infile into table without specifying the schema of this table but it does not look like you can do load data infile into a temporary table. Thank you, Alex 2009/5/19 Janek Bogucki janek.bogu...@studylink.com: Hi, mysql create temporary table t(i int); mysql \! echo 1 /tmp/data.txt mysql load data infile '/tmp/data.txt' into table t; Query OK, 1 row affected (0.00 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: 0 mysql select * from t; +--+ | i| +--+ |1 | +--+ 1 row in set (0.00 sec) Best Regards, -Janek, CMDEV 5.0. StudyLink. Helping People Realise Their Potential. http://studylink.com On Tue, 2009-05-19 at 02:57 +0100, Alex K wrote: Hello, Would anyone know how to load data infile into a temporary table? Thank you, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=alex.ksi...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
load data into temporary table
Hello, Would anyone know how to load data infile into a temporary table? Thank you, Alex -- 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 Temporary table right approach
Thanks for your comments. I just gave him a sample value. On Fri, Apr 17, 2009 at 11:10 PM, Andrew Braithwaite andrew.braithwa...@lovefilm.com wrote: If you can not eliminate your temporary tables, you have to adjust the following parameters in my.cnf [mysqld] max_heap_table_size=1G tmp_table_size=1G You're making a lot of assumptions about this guy's setup. You shouldn't just tell him to apply these kinds of settings as you don't what effect they will have on his system. If he only has 512MB available for MySQL and he starts writing lots of 1GB temporary tables what's going to happen to the performance of his server? With advice like that you could grind his server/s to a halt and cause his site To die a miserable swappy death. Andrew -Original Message- From: Moon's Father [mailto:yueliangdao0...@gmail.com] Sent: 17 April 2009 06:36 To: Manoj Singh Cc: php...@lists.php.net; mysql@lists.mysql.com Subject: Re: Is Temporary table right approach If you can not eliminate your temporary tables, you have to adjust the following parameters in my.cnf [mysqld] max_heap_table_size=1G tmp_table_size=1G On Fri, Apr 17, 2009 at 12:57 PM, Manoj Singh manojsingh2...@gmail.comwrote: Hi All, Thanks for your valuable input. I have decided to use temporary table approach. Since I am using it for the first time and this has to be done in the production server. Do I need to consider some facts before using this such as setting some parameters in my.cnf etc or the MYSQL will handle all. Actually I want to know if any one has faces issues practically when implementing temporary tables. Waiting for your suggestion. Thanks, Manoj On Fri, Apr 17, 2009 at 8:21 AM, Moon's Father yueliangdao0...@gmail.comwrote: Use temporary table can be a good idea. But I think you performance would be boost so much if you can do something else to replace the temporary table. On Wed, Apr 15, 2009 at 2:03 PM, Manoj Singh manojsingh2...@gmail.comwrote: Hi All, I have a query which returns the large number of ids which i am using in other queries. I am doing this in PHP. Now the first query can return unlimited number of ids which might create problem in PHP. I want to store this ids in MYSQL through temporary table so that i can access that ids in other queries directly. Do you think the approach is right or there is any other good approach? Please suggest. Regards, Manoj -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn -- David Yeung, MySQL Senior Support Engineer, Sun Gold Partner. My Blog:http://yueliangdao0608.cublog.cn Comanpy: http://www.actionsky.com
Temporary table vs. sub-select
I tend to use temporary tables a lot, because I'm doing one-off manipulations where efficiency is not the primary concern and because it helps me think things through. Nonetheless, I've been wondering about this: SELECT `x` FROM `t1` JOIN (SELECT `x` FROM `t2`) AS `t3` on `t1`.`y` = `t3`.`y`; Table `t3` won't have any indices, even if table `t2` does, is that correct? (Assume that the sub-select is really much more complicated that my example.) Wouldn't it be a lot faster to replace the sub-select with a temporary table that does have a key on `y`? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 http://www.the-infoshop.com www.the-infoshop.com http://www.giiexpress.com www.giiexpress.com www.etudes-marche.com
RE: Is Temporary table right approach
If you can not eliminate your temporary tables, you have to adjust the following parameters in my.cnf [mysqld] max_heap_table_size=1G tmp_table_size=1G You're making a lot of assumptions about this guy's setup. You shouldn't just tell him to apply these kinds of settings as you don't what effect they will have on his system. If he only has 512MB available for MySQL and he starts writing lots of 1GB temporary tables what's going to happen to the performance of his server? With advice like that you could grind his server/s to a halt and cause his site To die a miserable swappy death. Andrew -Original Message- From: Moon's Father [mailto:yueliangdao0...@gmail.com] Sent: 17 April 2009 06:36 To: Manoj Singh Cc: php...@lists.php.net; mysql@lists.mysql.com Subject: Re: Is Temporary table right approach If you can not eliminate your temporary tables, you have to adjust the following parameters in my.cnf [mysqld] max_heap_table_size=1G tmp_table_size=1G On Fri, Apr 17, 2009 at 12:57 PM, Manoj Singh manojsingh2...@gmail.comwrote: Hi All, Thanks for your valuable input. I have decided to use temporary table approach. Since I am using it for the first time and this has to be done in the production server. Do I need to consider some facts before using this such as setting some parameters in my.cnf etc or the MYSQL will handle all. Actually I want to know if any one has faces issues practically when implementing temporary tables. Waiting for your suggestion. Thanks, Manoj On Fri, Apr 17, 2009 at 8:21 AM, Moon's Father yueliangdao0...@gmail.comwrote: Use temporary table can be a good idea. But I think you performance would be boost so much if you can do something else to replace the temporary table. On Wed, Apr 15, 2009 at 2:03 PM, Manoj Singh manojsingh2...@gmail.comwrote: Hi All, I have a query which returns the large number of ids which i am using in other queries. I am doing this in PHP. Now the first query can return unlimited number of ids which might create problem in PHP. I want to store this ids in MYSQL through temporary table so that i can access that ids in other queries directly. Do you think the approach is right or there is any other good approach? Please suggest. Regards, Manoj -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn -- 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 Temporary table right approach
Use temporary table can be a good idea. But I think you performance would be boost so much if you can do something else to replace the temporary table. On Wed, Apr 15, 2009 at 2:03 PM, Manoj Singh manojsingh2...@gmail.comwrote: Hi All, I have a query which returns the large number of ids which i am using in other queries. I am doing this in PHP. Now the first query can return unlimited number of ids which might create problem in PHP. I want to store this ids in MYSQL through temporary table so that i can access that ids in other queries directly. Do you think the approach is right or there is any other good approach? Please suggest. Regards, Manoj -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Is Temporary table right approach
Hi All, Thanks for your valuable input. I have decided to use temporary table approach. Since I am using it for the first time and this has to be done in the production server. Do I need to consider some facts before using this such as setting some parameters in my.cnf etc or the MYSQL will handle all. Actually I want to know if any one has faces issues practically when implementing temporary tables. Waiting for your suggestion. Thanks, Manoj On Fri, Apr 17, 2009 at 8:21 AM, Moon's Father yueliangdao0...@gmail.comwrote: Use temporary table can be a good idea. But I think you performance would be boost so much if you can do something else to replace the temporary table. On Wed, Apr 15, 2009 at 2:03 PM, Manoj Singh manojsingh2...@gmail.comwrote: Hi All, I have a query which returns the large number of ids which i am using in other queries. I am doing this in PHP. Now the first query can return unlimited number of ids which might create problem in PHP. I want to store this ids in MYSQL through temporary table so that i can access that ids in other queries directly. Do you think the approach is right or there is any other good approach? Please suggest. Regards, Manoj -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Is Temporary table right approach
If you can not eliminate your temporary tables, you have to adjust the following parameters in my.cnf [mysqld] max_heap_table_size=1G tmp_table_size=1G On Fri, Apr 17, 2009 at 12:57 PM, Manoj Singh manojsingh2...@gmail.comwrote: Hi All, Thanks for your valuable input. I have decided to use temporary table approach. Since I am using it for the first time and this has to be done in the production server. Do I need to consider some facts before using this such as setting some parameters in my.cnf etc or the MYSQL will handle all. Actually I want to know if any one has faces issues practically when implementing temporary tables. Waiting for your suggestion. Thanks, Manoj On Fri, Apr 17, 2009 at 8:21 AM, Moon's Father yueliangdao0...@gmail.comwrote: Use temporary table can be a good idea. But I think you performance would be boost so much if you can do something else to replace the temporary table. On Wed, Apr 15, 2009 at 2:03 PM, Manoj Singh manojsingh2...@gmail.comwrote: Hi All, I have a query which returns the large number of ids which i am using in other queries. I am doing this in PHP. Now the first query can return unlimited number of ids which might create problem in PHP. I want to store this ids in MYSQL through temporary table so that i can access that ids in other queries directly. Do you think the approach is right or there is any other good approach? Please suggest. Regards, Manoj -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Is Temporary table right approach
Hi All, I have a query which returns the large number of ids which i am using in other queries. I am doing this in PHP. Now the first query can return unlimited number of ids which might create problem in PHP. I want to store this ids in MYSQL through temporary table so that i can access that ids in other queries directly. Do you think the approach is right or there is any other good approach? Please suggest. Regards, Manoj
Re: How to determine if temporary table exists
At 12:00 AM 11/21/2008, you wrote: In the last episode (Nov 20), mos said: At 08:02 PM 11/20/2008, you wrote: Try drop table if exists Tablex; Ahhh, I don't necessarily want to drop the table if it already exists. :) If the table already exists then I'll add new rows to it (and keep the existing rows). If the table doesn't exist, then I'll create it. I suppose could count the rows in Tablex and it would throw an exception if the table did not exist . But I really didn't want to resort to trapping an exception in my program. I thought there should be an easy way using SQL to determine if a temporary table exists or not. Why not CREATE TEMPORARY TABLE IF NOT EXISTS ...? If you really need to know whether the table existed before or not, that command will return a warning if it was there already. http://dev.mysql.com/doc/refman/5.0/en/create-table.html Dan, That will work. :-) Thanks. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to determine if temporary table exists
How can I determine if a temporary table exists? Normally I use something like: create temporary table Tablex like Table1; show tables like Tablex; but the Show Tables never displays any rows for a temporary table even though the temporary Tablex exists. (All in same thread). So is there a better way to determine if a temporary table exists? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to determine if temporary table exists
Try drop table if exists Tablex; On Fri, Nov 21, 2008 at 9:53 AM, mos [EMAIL PROTECTED] wrote: How can I determine if a temporary table exists? Normally I use something like: create temporary table Tablex like Table1; show tables like Tablex; but the Show Tables never displays any rows for a temporary table even though the temporary Tablex exists. (All in same thread). So is there a better way to determine if a temporary table exists? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: How to determine if temporary table exists
At 08:02 PM 11/20/2008, you wrote: Try drop table if exists Tablex; Ahhh, I don't necessarily want to drop the table if it already exists. :) If the table already exists then I'll add new rows to it (and keep the existing rows). If the table doesn't exist, then I'll create it. I suppose could count the rows in Tablex and it would throw an exception if the table did not exist . But I really didn't want to resort to trapping an exception in my program. I thought there should be an easy way using SQL to determine if a temporary table exists or not. Mike On Fri, Nov 21, 2008 at 9:53 AM, mos mailto:[EMAIL PROTECTED][EMAIL PROTECTED] wrote: How can I determine if a temporary table exists? Normally I use something like: create temporary table Tablex like Table1; show tables like Tablex; but the Show Tables never displays any rows for a temporary table even though the temporary Tablex exists. (All in same thread). So is there a better way to determine if a temporary table exists? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysqlhttp://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cnhttp://yueliangdao0608.cublog.cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to determine if temporary table exists
In the last episode (Nov 20), mos said: At 08:02 PM 11/20/2008, you wrote: Try drop table if exists Tablex; Ahhh, I don't necessarily want to drop the table if it already exists. :) If the table already exists then I'll add new rows to it (and keep the existing rows). If the table doesn't exist, then I'll create it. I suppose could count the rows in Tablex and it would throw an exception if the table did not exist . But I really didn't want to resort to trapping an exception in my program. I thought there should be an easy way using SQL to determine if a temporary table exists or not. Why not CREATE TEMPORARY TABLE IF NOT EXISTS ...? If you really need to know whether the table existed before or not, that command will return a warning if it was there already. http://dev.mysql.com/doc/refman/5.0/en/create-table.html -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DESCRIBE temporary table
I am not finding a quick reference to this, but I wanted to DESCIBE a TEMPORARY TABLE so that I can make sure the index was properly applied. Can this not be done? TIA! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DESCRIBE temporary table
Yes and No !!! I say no because temp table are visible neither in the INFORMATION_SCHEMA nor in SHOW TABLES. I say yes because you can do show create table tbl-name\G or describe tbl-name; to a temp table even if you cannot see it. So the answer is YES !!! -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Thursday, July 24, 2008 4:43 PM To: mysql@lists.mysql.com Subject: DESCRIBE temporary table I am not finding a quick reference to this, but I wanted to DESCIBE a TEMPORARY TABLE so that I can make sure the index was properly applied. Can this not be done? TIA! -- 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]
LEFT JOIN without temporary table?
I want to get a list of all products that either exist or do not exist. In other words, if prod.prod_pub_prod_id exists then I want to report its prod_num; if it doesn't, then I want to report the product ID with a blank value for the prod_num. I'm currently using a temporary table to do this, but it feels inelegant to me. Is there any way to do this without a temporary table? Here's what I'm doing now: DROP TEMPORARY TABLE IF EXISTS prod_exists; CREATE TEMPORARY TABLE `giiexpr_db`.`prod_exists` ( `prod_pub_prod_id` VARCHAR( 255 ) NOT NULL , PRIMARY KEY ( `prod_pub_prod_id` ) ) ENGINE = MYISAM; INSERT INTO prod_exists VALUES (MCP-1018), (MCP-1024), ... (MCP-1031) ; SELECT prod_exists.prod_pub_prod_id, IF(prod.prod_num IS NOT NULL,prod.prod_num,) as GII_prod_ID FROM prod_exists LEFT JOIN prod ON prod_exists.prod_pub_prod_id = prod.prod_pub_prod_id WHERE (prod.prod_discont = 0 OR prod.prod_discont IS NULL) ORDER BY prod_exists.prod_pub_prod_id; This works fine, but is there a better way to do it? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Deleting duplicate rows via temporary table either hung or taking way way too long [SOLVED]
-Original Message- From: Chris W [mailto:[EMAIL PROTECTED] Sent: Monday, February 04, 2008 9:05 PM To: Daevid Vincent; MYSQL General List Subject: Re: Deleting duplicate rows via temporary table either hung or taking way way too long Daevid Vincent wrote: DROP TABLE IF EXISTS `dupes`; CREATE TEMPORARY TABLE dupes SELECT LogID FROM buglog GROUP BY BID, TS HAVING count(*) 1 ORDER BY BID; LOCK TABLES buglog WRITE; SELECT * FROM buglog WHERE LogID IN (SELECT LogID FROM dupes) LIMIT 10; #DELETE FROM buglog WHERE LogID IN (SELECT LogID FROM dupes); UNLOCK TABLES; The problem is the SELECT (DELETE) is either taking way too long to return or it's hung. I don't sit there long enough to figure it out. It seems like it shouldn't take as long as I wait. If I run the delete version, my buglog table count never decreases in the time I wait. I am pretty sure I have does this in the past and having an index on the temporary table made it amazingly faster. I assume the LogID field has an index in the other table already, if not you will want to add an index for that field in that table too. The easiest way is to add the index with your create temporary table statement and then do an ... INSERT INTO dupes (SELECT .) WOW! You are right! That's silly. It's a table with a single column. All unique. Anyways, here's the magic incantation that worked for me: DROP TABLE IF EXISTS `dupes`; CREATE TEMPORARY TABLE dupes SELECT LogID FROM buglog GROUP BY BID, TS HAVING count(*) 1 ORDER BY BID; ALTER TABLE `dupes` ADD INDEX `LogID` ( `LogID` ); #//This index is critical. DELETE FROM buglog WHERE LogID IN (SELECT LogID FROM dupes); DROP TABLE IF EXISTS `dupes`; Trying to use the LOCK TABLES didn't work for me for some reason, but I didn't care enough to try and debug why. I just wanted the rows gone. :) Thanks C.W. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deleting duplicate rows via temporary table either hung or taking way way too long [SOLVED]
Daevid Vincent wrote: WOW! You are right! That's silly. It's a table with a single column. All unique. With out the index MySQL doesn't know they are unique. Anyways, here's the magic incantation that worked for me: DROP TABLE IF EXISTS `dupes`; CREATE TEMPORARY TABLE dupes SELECT LogID FROM buglog GROUP BY BID, TS HAVING count(*) 1 ORDER BY BID; ALTER TABLE `dupes` ADD INDEX `LogID` ( `LogID` ); #//This index is critical. DELETE FROM buglog WHERE LogID IN (SELECT LogID FROM dupes); DROP TABLE IF EXISTS `dupes`; I think what happens if the index isn't there on the dupes table, MySQL looks at every row in the buglog table and then does a sequential search in the dupes table for that LogID. So if there there are say 100,000 in bug log and say 1000 in dupes that would be 100,000 x 1,000 = 100 million compares. If it were to do it the other way around, it would be faster. Take every record in the dupes table and then use the index in the buglog table to find the row that matches the LogID. -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Ham Radio Repeater Database. http://hrrdb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Deleting duplicate rows via temporary table either hung or taking way way too long
Having a bit of trouble deleting 8645 duplicate rows... #//mySQL is broken and you can't reference a table you're deleting from in a subselect. #//http://www.thescripts.com/forum/thread490831.html #// you can't even update said table, so this elegant solution fails too... #// update buglog set BID = 0 where LogID #// IN (select LogID from buglog group by BID, TS having count(*) 1 order by BID); #// delete from buglog where BID = 0; So then I tried this hack-method: DROP TABLE IF EXISTS `dupes`; CREATE TEMPORARY TABLE dupes SELECT LogID FROM buglog GROUP BY BID, TS HAVING count(*) 1 ORDER BY BID; LOCK TABLES buglog WRITE; SELECT * FROM buglog WHERE LogID IN (SELECT LogID FROM dupes) LIMIT 10; #DELETE FROM buglog WHERE LogID IN (SELECT LogID FROM dupes); UNLOCK TABLES; The problem is the SELECT (DELETE) is either taking way too long to return or it's hung. I don't sit there long enough to figure it out. It seems like it shouldn't take as long as I wait. If I run the delete version, my buglog table count never decreases in the time I wait. mysql select count(*) from buglog; +--+ | count(*) | +--+ |34867 | +--+ mysql select count(*) from dupes; +--+ | count(*) | +--+ | 8645 | +--+ What am I doing wrong? Is there a better way to delete the duplicate rows? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deleting duplicate rows via temporary table either hung or taking way way too long
Daevid Vincent wrote: DROP TABLE IF EXISTS `dupes`; CREATE TEMPORARY TABLE dupes SELECT LogID FROM buglog GROUP BY BID, TS HAVING count(*) 1 ORDER BY BID; LOCK TABLES buglog WRITE; SELECT * FROM buglog WHERE LogID IN (SELECT LogID FROM dupes) LIMIT 10; #DELETE FROM buglog WHERE LogID IN (SELECT LogID FROM dupes); UNLOCK TABLES; The problem is the SELECT (DELETE) is either taking way too long to return or it's hung. I don't sit there long enough to figure it out. It seems like it shouldn't take as long as I wait. If I run the delete version, my buglog table count never decreases in the time I wait. I am pretty sure I have does this in the past and having an index on the temporary table made it amazingly faster. I assume the LogID field has an index in the other table already, if not you will want to add an index for that field in that table too. The easiest way is to add the index with your create temporary table statement and then do an ... INSERT INTO dupes (SELECT .) -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Ham Radio Repeater Database. http://hrrdb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: remove temporary table from SELECT query
Which is the my.cnf entry I need to increase. I'm only getting around 4k back_log = 75 skip-innodb max_connections = 500 key_buffer = 512M sort_buffer_size = 256M join_buffer_size = 128M read_buffer_size = 128M sort_buffer_size = 128M table_cache = 1800 thread_cache_size = 384 wait_timeout = 7200 connect_timeout = 10 tmp_table_size = 32M max_heap_table_size = 64M max_allowed_packet = 64M max_connect_errors = 1000 read_rnd_buffer_size = 512M bulk_insert_buffer_size = 8M query_cache_limit = 38M query_cache_size = 256M query_cache_type = 1 query_prealloc_size = 65536 query_alloc_block_size = 131072 default-storage-engine = MyISAM On 8/9/07, Andrew Armstrong [EMAIL PROTECTED] wrote: It goes to a temporary table when MySQL does not have enough memory (allocated) to store the temporary results in memory, so it needs to create a temporary table on disk. Try increasing the memory buffer size or eliminating more rows from the query. -Original Message- From: Mike Zupan [mailto:[EMAIL PROTECTED] Sent: Friday, 10 August 2007 4:52 AM To: mysql@lists.mysql.com Subject: remove temporary table from SELECT query I have been pulling my hair out over a temporary table being created in the following query SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS entryid,title FROM friends_test INNER JOIN entries ON friendLink=userid AND userLink=2 order by entryid if I change userLink=2 to friendLink=2 it is fine and its very fast. If i leave it the query is around 2 seconds. ++-+--+--+-+--+- +---+--+ -+ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--+--+-+--+- +---+--+ -+ | 1 | SIMPLE | friends_test | ref | userLink,friendLink | userLink | 3 | const | 458 | Using temporary; Using filesort | | 1 | SIMPLE | entries | ref | userid | userid | 4 | photoblog.friends_test.friendLink | 11 | Using where | ++-+--+--+-+--+- +---+--+ -+ The above is an explain of the bad query Here is the table data for the friends_test and entries table CREATE TABLE `friends_test` ( `friendID` mediumint(8) NOT NULL auto_increment, `userLink` mediumint(8) unsigned NOT NULL, `friendLink` mediumint(8) unsigned NOT NULL, `status` tinyint(1) NOT NULL default '1', PRIMARY KEY (`friendID`), KEY `userLink` (`userLink`), KEY `friendLink` (`friendLink`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=74971 ; CREATE TABLE `entries` ( `entryid` mediumint(10) unsigned NOT NULL auto_increment, `userid` mediumint(8) unsigned default NULL, `title` varchar(255) character set utf8 collate utf8_unicode_ci default NULL, `photos` text, `sizes` mediumtext NOT NULL, `text` text character set utf8 collate utf8_unicode_ci, `category` int(6) unsigned default NULL, `created` int(10) unsigned default NULL, `ts` int(10) unsigned default '0', `modified` int(10) unsigned default NULL, `date` date NOT NULL default '-00-00', `comments` smallint(3) unsigned NOT NULL default '1', `views` mediumint(8) NOT NULL default '0', `dir` varchar(10) NOT NULL default 'photos', `server` varchar(20) NOT NULL default 'i1.photoblog.com', `notes` longtext character set utf8 collate utf8_unicode_ci NOT NULL, `titles` text character set utf8 collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`entryid`), KEY `userid` (`userid`), KEY `date` (`date`), KEY `created` (`created`), KEY `ts` (`ts`), FULLTEXT KEY `title` (`title`,`text`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=204306 ; any help or pointers is a BIG help.
Re: remove temporary table from SELECT query
Hi , ORDER BY, will always use some temporary table for doing sort operation. For that matter, any group function, like min,max,group by,order by will use temporary table before displaying the final results. Regards anandkl On 8/10/07, Mike Zupan [EMAIL PROTECTED] wrote: neither of those have fixed the issue.. I can create a sample database if anyone wants it to be of some help. Mike On 8/10/07, Ananda Kumar [EMAIL PROTECTED] wrote: Also, its not good to set sort_buffer_size=256M, as this much of memory will get allocated to each session, and if u have more than 10 connections at any point of time then it will more than 2GB RAM will get allocated and system will be slow. I feel sort_buffer_size=1MB should be good in my.cnf You need to increase this parameter only at session level and not at my.cnf level. set sort_buffer_size=1; On 8/10/07, Ananda Kumar [EMAIL PROTECTED] wrote: at your mysql prompt set sort_area_size=1; try this regards anandkl On 8/10/07, Mike Zupan [EMAIL PROTECTED] wrote: Which is the my.cnf entry I need to increase. I'm only getting around 4k back_log = 75 skip-innodb max_connections = 500 key_buffer = 512M sort_buffer_size = 256M join_buffer_size = 128M read_buffer_size = 128M sort_buffer_size = 128M table_cache = 1800 thread_cache_size = 384 wait_timeout = 7200 connect_timeout = 10 tmp_table_size = 32M max_heap_table_size = 64M max_allowed_packet = 64M max_connect_errors = 1000 read_rnd_buffer_size = 512M bulk_insert_buffer_size = 8M query_cache_limit = 38M query_cache_size = 256M query_cache_type = 1 query_prealloc_size = 65536 query_alloc_block_size = 131072 default-storage-engine = MyISAM On 8/9/07, Andrew Armstrong [EMAIL PROTECTED] wrote: It goes to a temporary table when MySQL does not have enough memory (allocated) to store the temporary results in memory, so it needs to create a temporary table on disk. Try increasing the memory buffer size or eliminating more rows from the query. -Original Message- From: Mike Zupan [mailto: [EMAIL PROTECTED] ] Sent: Friday, 10 August 2007 4:52 AM To: mysql@lists.mysql.com Subject: remove temporary table from SELECT query I have been pulling my hair out over a temporary table being created in the following query SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS entryid,title FROM friends_test INNER JOIN entries ON friendLink=userid AND userLink=2 order by entryid if I change userLink=2 to friendLink=2 it is fine and its very fast. If i leave it the query is around 2 seconds. ++-+--+--+-+--+- +---+--+ -+ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--+--+-+--+- +---+--+ -+ | 1 | SIMPLE | friends_test | ref | userLink,friendLink | userLink | 3 | const | 458 | Using temporary; Using filesort | | 1 | SIMPLE | entries | ref | userid | userid | 4 | photoblog.friends_test.friendLink | 11 | Using where | ++-+--+--+-+--+- +---+--+ -+ The above is an explain of the bad query Here is the table data for the friends_test and entries table CREATE TABLE `friends_test` ( `friendID` mediumint(8) NOT NULL auto_increment, `userLink` mediumint(8) unsigned NOT NULL, `friendLink` mediumint(8) unsigned NOT NULL, `status` tinyint(1) NOT NULL default '1', PRIMARY KEY (`friendID`), KEY `userLink` (`userLink`), KEY `friendLink` (`friendLink`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=74971 ; CREATE TABLE `entries` ( `entryid` mediumint(10) unsigned NOT NULL auto_increment, `userid` mediumint(8) unsigned default NULL, `title` varchar(255) character set utf8 collate utf8_unicode_ci default NULL, `photos` text, `sizes` mediumtext NOT NULL, `text` text character set utf8 collate utf8_unicode_ci, `category` int(6
Re: remove temporary table from SELECT query
at your mysql prompt set sort_area_size=1; try this regards anandkl On 8/10/07, Mike Zupan [EMAIL PROTECTED] wrote: Which is the my.cnf entry I need to increase. I'm only getting around 4k back_log = 75 skip-innodb max_connections = 500 key_buffer = 512M sort_buffer_size = 256M join_buffer_size = 128M read_buffer_size = 128M sort_buffer_size = 128M table_cache = 1800 thread_cache_size = 384 wait_timeout = 7200 connect_timeout = 10 tmp_table_size = 32M max_heap_table_size = 64M max_allowed_packet = 64M max_connect_errors = 1000 read_rnd_buffer_size = 512M bulk_insert_buffer_size = 8M query_cache_limit = 38M query_cache_size = 256M query_cache_type = 1 query_prealloc_size = 65536 query_alloc_block_size = 131072 default-storage-engine = MyISAM On 8/9/07, Andrew Armstrong [EMAIL PROTECTED] wrote: It goes to a temporary table when MySQL does not have enough memory (allocated) to store the temporary results in memory, so it needs to create a temporary table on disk. Try increasing the memory buffer size or eliminating more rows from the query. -Original Message- From: Mike Zupan [mailto:[EMAIL PROTECTED] Sent: Friday, 10 August 2007 4:52 AM To: mysql@lists.mysql.com Subject: remove temporary table from SELECT query I have been pulling my hair out over a temporary table being created in the following query SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS entryid,title FROM friends_test INNER JOIN entries ON friendLink=userid AND userLink=2 order by entryid if I change userLink=2 to friendLink=2 it is fine and its very fast. If i leave it the query is around 2 seconds. ++-+--+--+-+--+- +---+--+ -+ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--+--+-+--+- +---+--+ -+ | 1 | SIMPLE | friends_test | ref | userLink,friendLink | userLink | 3 | const | 458 | Using temporary; Using filesort | | 1 | SIMPLE | entries | ref | userid | userid | 4 | photoblog.friends_test.friendLink | 11 | Using where | ++-+--+--+-+--+- +---+--+ -+ The above is an explain of the bad query Here is the table data for the friends_test and entries table CREATE TABLE `friends_test` ( `friendID` mediumint(8) NOT NULL auto_increment, `userLink` mediumint(8) unsigned NOT NULL, `friendLink` mediumint(8) unsigned NOT NULL, `status` tinyint(1) NOT NULL default '1', PRIMARY KEY (`friendID`), KEY `userLink` (`userLink`), KEY `friendLink` (`friendLink`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=74971 ; CREATE TABLE `entries` ( `entryid` mediumint(10) unsigned NOT NULL auto_increment, `userid` mediumint(8) unsigned default NULL, `title` varchar(255) character set utf8 collate utf8_unicode_ci default NULL, `photos` text, `sizes` mediumtext NOT NULL, `text` text character set utf8 collate utf8_unicode_ci, `category` int(6) unsigned default NULL, `created` int(10) unsigned default NULL, `ts` int(10) unsigned default '0', `modified` int(10) unsigned default NULL, `date` date NOT NULL default '-00-00', `comments` smallint(3) unsigned NOT NULL default '1', `views` mediumint(8) NOT NULL default '0', `dir` varchar(10) NOT NULL default 'photos', `server` varchar(20) NOT NULL default 'i1.photoblog.com', `notes` longtext character set utf8 collate utf8_unicode_ci NOT NULL, `titles` text character set utf8 collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`entryid`), KEY `userid` (`userid`), KEY `date` (`date`), KEY `created` (`created`), KEY `ts` (`ts`), FULLTEXT KEY `title` (`title`,`text`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=204306 ; any help or pointers is a BIG help.
Re: remove temporary table from SELECT query
Also, its not good to set sort_buffer_size=256M, as this much of memory will get allocated to each session, and if u have more than 10 connections at any point of time then it will more than 2GB RAM will get allocated and system will be slow. I feel sort_buffer_size=1MB should be good in my.cnf You need to increase this parameter only at session level and not at my.cnflevel. set sort_buffer_size=1; On 8/10/07, Ananda Kumar [EMAIL PROTECTED] wrote: at your mysql prompt set sort_area_size=1; try this regards anandkl On 8/10/07, Mike Zupan [EMAIL PROTECTED] wrote: Which is the my.cnf entry I need to increase. I'm only getting around 4k back_log = 75 skip-innodb max_connections = 500 key_buffer = 512M sort_buffer_size = 256M join_buffer_size = 128M read_buffer_size = 128M sort_buffer_size = 128M table_cache = 1800 thread_cache_size = 384 wait_timeout = 7200 connect_timeout = 10 tmp_table_size = 32M max_heap_table_size = 64M max_allowed_packet = 64M max_connect_errors = 1000 read_rnd_buffer_size = 512M bulk_insert_buffer_size = 8M query_cache_limit = 38M query_cache_size = 256M query_cache_type = 1 query_prealloc_size = 65536 query_alloc_block_size = 131072 default-storage-engine = MyISAM On 8/9/07, Andrew Armstrong [EMAIL PROTECTED] wrote: It goes to a temporary table when MySQL does not have enough memory (allocated) to store the temporary results in memory, so it needs to create a temporary table on disk. Try increasing the memory buffer size or eliminating more rows from the query. -Original Message- From: Mike Zupan [mailto:[EMAIL PROTECTED] ] Sent: Friday, 10 August 2007 4:52 AM To: mysql@lists.mysql.com Subject: remove temporary table from SELECT query I have been pulling my hair out over a temporary table being created in the following query SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS entryid,title FROM friends_test INNER JOIN entries ON friendLink=userid AND userLink=2 order by entryid if I change userLink=2 to friendLink=2 it is fine and its very fast. If i leave it the query is around 2 seconds. ++-+--+--+-+--+- +---+--+ -+ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--+--+-+--+- +---+--+ -+ | 1 | SIMPLE | friends_test | ref | userLink,friendLink | userLink | 3 | const | 458 | Using temporary; Using filesort | | 1 | SIMPLE | entries | ref | userid | userid | 4 | photoblog.friends_test.friendLink | 11 | Using where | ++-+--+--+-+--+- +---+--+ -+ The above is an explain of the bad query Here is the table data for the friends_test and entries table CREATE TABLE `friends_test` ( `friendID` mediumint(8) NOT NULL auto_increment, `userLink` mediumint(8) unsigned NOT NULL, `friendLink` mediumint(8) unsigned NOT NULL, `status` tinyint(1) NOT NULL default '1', PRIMARY KEY (`friendID`), KEY `userLink` (`userLink`), KEY `friendLink` (`friendLink`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=74971 ; CREATE TABLE `entries` ( `entryid` mediumint(10) unsigned NOT NULL auto_increment, `userid` mediumint(8) unsigned default NULL, `title` varchar(255) character set utf8 collate utf8_unicode_ci default NULL, `photos` text, `sizes` mediumtext NOT NULL, `text` text character set utf8 collate utf8_unicode_ci, `category` int(6) unsigned default NULL, `created` int(10) unsigned default NULL, `ts` int(10) unsigned default '0', `modified` int(10) unsigned default NULL, `date` date NOT NULL default '-00-00', `comments` smallint(3) unsigned NOT NULL default '1', `views` mediumint(8) NOT NULL default '0', `dir` varchar(10) NOT NULL default 'photos', `server` varchar(20) NOT NULL default 'i1.photoblog.com ', `notes` longtext character set utf8 collate utf8_unicode_ci NOT NULL, `titles` text character set utf8 collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`entryid`), KEY `userid` (`userid`), KEY `date` (`date`), KEY `created` (`created`), KEY `ts` (`ts`), FULLTEXT KEY `title` (`title`,`text`) ) ENGINE=MyISAM
Re: remove temporary table from SELECT query
neither of those have fixed the issue.. I can create a sample database if anyone wants it to be of some help. Mike On 8/10/07, Ananda Kumar [EMAIL PROTECTED] wrote: Also, its not good to set sort_buffer_size=256M, as this much of memory will get allocated to each session, and if u have more than 10 connections at any point of time then it will more than 2GB RAM will get allocated and system will be slow. I feel sort_buffer_size=1MB should be good in my.cnf You need to increase this parameter only at session level and not at my.cnf level. set sort_buffer_size=1; On 8/10/07, Ananda Kumar [EMAIL PROTECTED] wrote: at your mysql prompt set sort_area_size=1; try this regards anandkl On 8/10/07, Mike Zupan [EMAIL PROTECTED] wrote: Which is the my.cnf entry I need to increase. I'm only getting around 4k back_log = 75 skip-innodb max_connections = 500 key_buffer = 512M sort_buffer_size = 256M join_buffer_size = 128M read_buffer_size = 128M sort_buffer_size = 128M table_cache = 1800 thread_cache_size = 384 wait_timeout = 7200 connect_timeout = 10 tmp_table_size = 32M max_heap_table_size = 64M max_allowed_packet = 64M max_connect_errors = 1000 read_rnd_buffer_size = 512M bulk_insert_buffer_size = 8M query_cache_limit = 38M query_cache_size = 256M query_cache_type = 1 query_prealloc_size = 65536 query_alloc_block_size = 131072 default-storage-engine = MyISAM On 8/9/07, Andrew Armstrong [EMAIL PROTECTED] wrote: It goes to a temporary table when MySQL does not have enough memory (allocated) to store the temporary results in memory, so it needs to create a temporary table on disk. Try increasing the memory buffer size or eliminating more rows from the query. -Original Message- From: Mike Zupan [mailto: [EMAIL PROTECTED] ] Sent: Friday, 10 August 2007 4:52 AM To: mysql@lists.mysql.com Subject: remove temporary table from SELECT query I have been pulling my hair out over a temporary table being created in the following query SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS entryid,title FROM friends_test INNER JOIN entries ON friendLink=userid AND userLink=2 order by entryid if I change userLink=2 to friendLink=2 it is fine and its very fast. If i leave it the query is around 2 seconds. ++-+--+--+-+--+- +---+--+ -+ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--+--+-+--+- +---+--+ -+ | 1 | SIMPLE | friends_test | ref | userLink,friendLink | userLink | 3 | const | 458 | Using temporary; Using filesort | | 1 | SIMPLE | entries | ref | userid | userid | 4 | photoblog.friends_test.friendLink | 11 | Using where | ++-+--+--+-+--+- +---+--+ -+ The above is an explain of the bad query Here is the table data for the friends_test and entries table CREATE TABLE `friends_test` ( `friendID` mediumint(8) NOT NULL auto_increment, `userLink` mediumint(8) unsigned NOT NULL, `friendLink` mediumint(8) unsigned NOT NULL, `status` tinyint(1) NOT NULL default '1', PRIMARY KEY (`friendID`), KEY `userLink` (`userLink`), KEY `friendLink` (`friendLink`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=74971 ; CREATE TABLE `entries` ( `entryid` mediumint(10) unsigned NOT NULL auto_increment, `userid` mediumint(8) unsigned default NULL, `title` varchar(255) character set utf8 collate utf8_unicode_ci default NULL, `photos` text, `sizes` mediumtext NOT NULL, `text` text character set utf8 collate utf8_unicode_ci, `category` int(6) unsigned default NULL, `created` int(10) unsigned default NULL, `ts` int(10) unsigned default '0', `modified` int(10) unsigned default NULL, `date` date NOT NULL default '-00-00', `comments` smallint(3) unsigned NOT NULL default '1', `views` mediumint(8) NOT NULL default '0', `dir` varchar(10) NOT NULL default 'photos', `server` varchar(20) NOT NULL default ' i1.photoblog.com ', `notes` longtext
remove temporary table from SELECT query
I have been pulling my hair out over a temporary table being created in the following query SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS entryid,title FROM friends_test INNER JOIN entries ON friendLink=userid AND userLink=2 order by entryid if I change userLink=2 to friendLink=2 it is fine and its very fast. If i leave it the query is around 2 seconds. ++-+--+--+-+--+-+---+--+-+ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--+--+-+--+-+---+--+-+ | 1 | SIMPLE | friends_test | ref | userLink,friendLink | userLink | 3 | const | 458 | Using temporary; Using filesort | | 1 | SIMPLE | entries | ref | userid | userid | 4 | photoblog.friends_test.friendLink | 11 | Using where | ++-+--+--+-+--+-+---+--+-+ The above is an explain of the bad query Here is the table data for the friends_test and entries table CREATE TABLE `friends_test` ( `friendID` mediumint(8) NOT NULL auto_increment, `userLink` mediumint(8) unsigned NOT NULL, `friendLink` mediumint(8) unsigned NOT NULL, `status` tinyint(1) NOT NULL default '1', PRIMARY KEY (`friendID`), KEY `userLink` (`userLink`), KEY `friendLink` (`friendLink`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=74971 ; CREATE TABLE `entries` ( `entryid` mediumint(10) unsigned NOT NULL auto_increment, `userid` mediumint(8) unsigned default NULL, `title` varchar(255) character set utf8 collate utf8_unicode_ci default NULL, `photos` text, `sizes` mediumtext NOT NULL, `text` text character set utf8 collate utf8_unicode_ci, `category` int(6) unsigned default NULL, `created` int(10) unsigned default NULL, `ts` int(10) unsigned default '0', `modified` int(10) unsigned default NULL, `date` date NOT NULL default '-00-00', `comments` smallint(3) unsigned NOT NULL default '1', `views` mediumint(8) NOT NULL default '0', `dir` varchar(10) NOT NULL default 'photos', `server` varchar(20) NOT NULL default 'i1.photoblog.com', `notes` longtext character set utf8 collate utf8_unicode_ci NOT NULL, `titles` text character set utf8 collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`entryid`), KEY `userid` (`userid`), KEY `date` (`date`), KEY `created` (`created`), KEY `ts` (`ts`), FULLTEXT KEY `title` (`title`,`text`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=204306 ; any help or pointers is a BIG help.
RE: remove temporary table from SELECT query
It goes to a temporary table when MySQL does not have enough memory (allocated) to store the temporary results in memory, so it needs to create a temporary table on disk. Try increasing the memory buffer size or eliminating more rows from the query. -Original Message- From: Mike Zupan [mailto:[EMAIL PROTECTED] Sent: Friday, 10 August 2007 4:52 AM To: mysql@lists.mysql.com Subject: remove temporary table from SELECT query I have been pulling my hair out over a temporary table being created in the following query SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS entryid,title FROM friends_test INNER JOIN entries ON friendLink=userid AND userLink=2 order by entryid if I change userLink=2 to friendLink=2 it is fine and its very fast. If i leave it the query is around 2 seconds. ++-+--+--+-+--+- +---+--+ -+ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--+--+-+--+- +---+--+ -+ | 1 | SIMPLE | friends_test | ref | userLink,friendLink | userLink | 3 | const | 458 | Using temporary; Using filesort | | 1 | SIMPLE | entries | ref | userid | userid | 4 | photoblog.friends_test.friendLink | 11 | Using where | ++-+--+--+-+--+- +---+--+ -+ The above is an explain of the bad query Here is the table data for the friends_test and entries table CREATE TABLE `friends_test` ( `friendID` mediumint(8) NOT NULL auto_increment, `userLink` mediumint(8) unsigned NOT NULL, `friendLink` mediumint(8) unsigned NOT NULL, `status` tinyint(1) NOT NULL default '1', PRIMARY KEY (`friendID`), KEY `userLink` (`userLink`), KEY `friendLink` (`friendLink`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=74971 ; CREATE TABLE `entries` ( `entryid` mediumint(10) unsigned NOT NULL auto_increment, `userid` mediumint(8) unsigned default NULL, `title` varchar(255) character set utf8 collate utf8_unicode_ci default NULL, `photos` text, `sizes` mediumtext NOT NULL, `text` text character set utf8 collate utf8_unicode_ci, `category` int(6) unsigned default NULL, `created` int(10) unsigned default NULL, `ts` int(10) unsigned default '0', `modified` int(10) unsigned default NULL, `date` date NOT NULL default '-00-00', `comments` smallint(3) unsigned NOT NULL default '1', `views` mediumint(8) NOT NULL default '0', `dir` varchar(10) NOT NULL default 'photos', `server` varchar(20) NOT NULL default 'i1.photoblog.com', `notes` longtext character set utf8 collate utf8_unicode_ci NOT NULL, `titles` text character set utf8 collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`entryid`), KEY `userid` (`userid`), KEY `date` (`date`), KEY `created` (`created`), KEY `ts` (`ts`), FULLTEXT KEY `title` (`title`,`text`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=204306 ; any help or pointers is a BIG help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Temporary table lifespan - SOLVED
Jerry Schwartz wrote: Possibly you are opening another connection, using the same handle, when you re-enter your script. If that is the case, you'll lose track of your previous connection (the one that has the temporary table). You might also be accumulating a lot of connections, if this recursion happens multiple levels. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Amer Neely [mailto:[EMAIL PROTECTED] Sent: Thursday, February 15, 2007 11:21 PM To: Daniel Kasak; mysql@lists.mysql.com Subject: Re: Temporary table lifespan - SOLVED Amer Neely wrote: Daniel Kasak wrote: Amer Neely wrote: OK, that makes sense. As far as I know, my connection is still live - I don't do a disconnect anywhere. So I'm still not sure why I can't pull the data back out. I do get an error telling me about a problem with my statement near which is where it tries to execute the FROM command. My apologies, I'm not on the computer that I am developing this script on, so can't give you the exact error. I'm naming the table from a purchase order number, and as far as I can tell, the table I try to read from is the same as the one I created - but I get nothing out of it. It's hard to say without more details. You can send the script if you want. Also keep in mind that since you're using tmp tables which are invisible to all other connections, you don't need to much around with creating a unique table name - you can just use a generic one ( eg tmp_purchase_order or something ). I'll try something like that tomorrow. Thanks for the responses. I'll post my results. After failing to get the temporary table method working, I ended up just creating a table, then dropping it when I was done. It seems that a temporary table will get deleted when a script calls itself and jumps to a function inside. That must constitute a disconnection. I checked my code, and one of the first things the script does is connect to the database. So you're right, it starts a new connection. I couldn't figure an easy way around that - kind of like the horse / cart thingy, so went the route I did with just dropping a table after processing. Works fine. -- Amer Neely w: www.softouch.on.ca/ b: www.softouch.on.ca/blog/ v: 705.223.3539 Perl | MySQL programming for all data entry forms. We make web sites work! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Temporary table lifespan - SOLVED
Possibly you are opening another connection, using the same handle, when you re-enter your script. If that is the case, you'll lose track of your previous connection (the one that has the temporary table). You might also be accumulating a lot of connections, if this recursion happens multiple levels. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Amer Neely [mailto:[EMAIL PROTECTED] Sent: Thursday, February 15, 2007 11:21 PM To: Daniel Kasak; mysql@lists.mysql.com Subject: Re: Temporary table lifespan - SOLVED Amer Neely wrote: Daniel Kasak wrote: Amer Neely wrote: OK, that makes sense. As far as I know, my connection is still live - I don't do a disconnect anywhere. So I'm still not sure why I can't pull the data back out. I do get an error telling me about a problem with my statement near which is where it tries to execute the FROM command. My apologies, I'm not on the computer that I am developing this script on, so can't give you the exact error. I'm naming the table from a purchase order number, and as far as I can tell, the table I try to read from is the same as the one I created - but I get nothing out of it. It's hard to say without more details. You can send the script if you want. Also keep in mind that since you're using tmp tables which are invisible to all other connections, you don't need to much around with creating a unique table name - you can just use a generic one ( eg tmp_purchase_order or something ). I'll try something like that tomorrow. Thanks for the responses. I'll post my results. After failing to get the temporary table method working, I ended up just creating a table, then dropping it when I was done. It seems that a temporary table will get deleted when a script calls itself and jumps to a function inside. That must constitute a disconnection. -- Amer Neely w: www.softouch.on.ca/ b: www.softouch.on.ca/blog/ Perl | MySQL programming for all data entry forms. We make web sites work! -- 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]
Temporary table lifespan
I'm writing a perl script in which I need to save some session data. My first attempt is to use a temporary table to store some data. However, I'm a little unclear as to the lifespan of the temporary table. My understanding is they last until the session ends, or a DELETE TABLE is issued. My question is 'what is a session'? For example, in my perl script I create and populate the table in one subroutine, but need to access it from another in the same script. But it doesn't appear to live through the transition from one subroutine to another. Anyone have an idea whether this can even be done, or does calling another subroutine end the 'session', thus killing the table? -- Amer Neely w: www.softouch.on.ca/ b: www.softouch.on.ca/blog/ Perl | MySQL programming for all data entry forms. We make web sites work! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Temporary table lifespan
Amer Neely wrote: I'm writing a perl script in which I need to save some session data. My first attempt is to use a temporary table to store some data. However, I'm a little unclear as to the lifespan of the temporary table. My understanding is they last until the session ends, or a DELETE TABLE is issued. My question is 'what is a session'? For example, in my perl script I create and populate the table in one subroutine, but need to access it from another in the same script. But it doesn't appear to live through the transition from one subroutine to another. Anyone have an idea whether this can even be done, or does calling another subroutine end the 'session', thus killing the table? Session isn't exactly the right word for it. The temporary tables hang around for the lifetime of the database connection. So if you open a database connection, make a tmp table, close the DB connection, and open another one ( inside the same subroutine ), your tmp table will be gone. Also, if you open 2 DB connections, and make a tmp table from one, the other connection can't see it. So just keep your DB connection ( or database handle in Perl speak ) live and you should be able to see the table. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Temporary table lifespan
Amer Neely wrote: Daniel Kasak wrote: Amer Neely wrote: I'm writing a perl script in which I need to save some session data. My first attempt is to use a temporary table to store some data. However, I'm a little unclear as to the lifespan of the temporary table. My understanding is they last until the session ends, or a DELETE TABLE is issued. My question is 'what is a session'? For example, in my perl script I create and populate the table in one subroutine, but need to access it from another in the same script. But it doesn't appear to live through the transition from one subroutine to another. Anyone have an idea whether this can even be done, or does calling another subroutine end the 'session', thus killing the table? Session isn't exactly the right word for it. The temporary tables hang around for the lifetime of the database connection. So if you open a database connection, make a tmp table, close the DB connection, and open another one ( inside the same subroutine ), your tmp table will be gone. Also, if you open 2 DB connections, and make a tmp table from one, the other connection can't see it. So just keep your DB connection ( or database handle in Perl speak ) live and you should be able to see the table. OK, that makes sense. As far as I know, my connection is still live - I don't do a disconnect anywhere. So I'm still not sure why I can't pull the data back out. I do get an error telling me about a problem with my statement near which is where it tries to execute the FROM command. My apologies, I'm not on the computer that I am developing this script on, so can't give you the exact error. I'm naming the table from a purchase order number, and as far as I can tell, the table I try to read from is the same as the one I created - but I get nothing out of it. -- Amer Neely w: www.softouch.on.ca/ b: www.softouch.on.ca/blog/ Perl | MySQL programming for all data entry forms. We make web sites work! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Temporary table lifespan
Amer Neely wrote: OK, that makes sense. As far as I know, my connection is still live - I don't do a disconnect anywhere. So I'm still not sure why I can't pull the data back out. I do get an error telling me about a problem with my statement near which is where it tries to execute the FROM command. My apologies, I'm not on the computer that I am developing this script on, so can't give you the exact error. I'm naming the table from a purchase order number, and as far as I can tell, the table I try to read from is the same as the one I created - but I get nothing out of it. It's hard to say without more details. You can send the script if you want. Also keep in mind that since you're using tmp tables which are invisible to all other connections, you don't need to much around with creating a unique table name - you can just use a generic one ( eg tmp_purchase_order or something ). -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Temporary table lifespan
Daniel Kasak wrote: Amer Neely wrote: OK, that makes sense. As far as I know, my connection is still live - I don't do a disconnect anywhere. So I'm still not sure why I can't pull the data back out. I do get an error telling me about a problem with my statement near which is where it tries to execute the FROM command. My apologies, I'm not on the computer that I am developing this script on, so can't give you the exact error. I'm naming the table from a purchase order number, and as far as I can tell, the table I try to read from is the same as the one I created - but I get nothing out of it. It's hard to say without more details. You can send the script if you want. Also keep in mind that since you're using tmp tables which are invisible to all other connections, you don't need to much around with creating a unique table name - you can just use a generic one ( eg tmp_purchase_order or something ). I'll try something like that tomorrow. Thanks for the responses. I'll post my results. -- Amer Neely w: www.softouch.on.ca/ b: www.softouch.on.ca/blog/ Perl | MySQL programming for all data entry forms. We make web sites work! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Temporary table lifespan - SOLVED
Amer Neely wrote: Daniel Kasak wrote: Amer Neely wrote: OK, that makes sense. As far as I know, my connection is still live - I don't do a disconnect anywhere. So I'm still not sure why I can't pull the data back out. I do get an error telling me about a problem with my statement near which is where it tries to execute the FROM command. My apologies, I'm not on the computer that I am developing this script on, so can't give you the exact error. I'm naming the table from a purchase order number, and as far as I can tell, the table I try to read from is the same as the one I created - but I get nothing out of it. It's hard to say without more details. You can send the script if you want. Also keep in mind that since you're using tmp tables which are invisible to all other connections, you don't need to much around with creating a unique table name - you can just use a generic one ( eg tmp_purchase_order or something ). I'll try something like that tomorrow. Thanks for the responses. I'll post my results. After failing to get the temporary table method working, I ended up just creating a table, then dropping it when I was done. It seems that a temporary table will get deleted when a script calls itself and jumps to a function inside. That must constitute a disconnection. -- Amer Neely w: www.softouch.on.ca/ b: www.softouch.on.ca/blog/ Perl | MySQL programming for all data entry forms. We make web sites work! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to build a single temporary table from 3 tables on the fly
Can someone help point me in the right direction for this. This isnt exactly what I want but once I have the solution to this I can work out the permutations I need. How do I select from 3 tables into a single table (consequtive rows not joined ones) and include a two new columns which is the name of the table from which the data has been extracted, and a fixed piece of text. This select can be executed by more than one person at a time, so I need to extract into a transient temp table so that I can view the dataset. e.g. I have 3 tables containing names and want to extract the rows of a particular surname so I would have lets say select main, thetablename, surname from t1 into myautogentable select personal, thetablename, surname from t2 into myautogentable select group, thetablename, surname from t3 into myautogentable I can then link a database grid in my program to the dataset of the resulting query. Kerry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to build a single temporary table from 3 tables on the fly
Thanks Phil It gives me a solution and some reading. Kerry -Original Message- From: Philip Mather [mailto:[EMAIL PROTECTED] Sent: 11 October 2006 10:02 To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: How to build a single temporary table from 3 tables on the fly Kerry, How do I select from 3 tables into a single table (consequtive rows not joined ones) and include a two new columns which is the name of the table from which the data has been extracted, and a fixed piece of text. I'd do something like... CREATE TABLE Merged_names ( Temp table definition goes here ) ENGINE=MEMORY SELECT * FROM ( ( SELECT main, hardcodedtablename1, `surname` FROM table1 WHERE someCriteria = someOtherCriteria ) UNION ALL ( SELECT main, hardcodedtablename2, `surname` FROM table2 WHERE someCriteria = someOtherCriteria ) UNION ALL ( SELECT main, hardcodedtablename3, `surname` FROM table3 WHERE someCriteria = someOtherCriteria ) ) AS TMP; This select can be executed by more than one person at a time, so I need to extract into a transient temp table so that I can view the dataset. e.g. I have 3 tables containing names and want to extract the rows of a particular surname so I would have lets say select main, thetablename, surname from t1 into myautogentable select personal, thetablename, surname from t2 into myautogentable select group, thetablename, surname from t3 into myautogentable Does that do roughly what you needed? I'd suggest reading... http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html http://dev.mysql.com/doc/refman/5.0/en/create-table.html, search for and start reading at CREATE TABLE new_tbl SELECT http://dev.mysql.com/doc/refman/5.0/en/union.html Regards, Phil
Re: How to build a single temporary table from 3 tables on the fly
Kerry, It gives me a solution and some reading. No probs, here's some actual code that I hacked together on a 4.1-sommat-or-other database, an important thing to note is to be careful of any Unique keys selected from the three individual tables as they may no longer be unique of course once you've union them using the ALL method. CREATE TABLE Merged_names ( `ID` int(11) unsigned NOT NULL auto_increment, `Static_field` varchar(50) NOT NULL default '', `Parent_table` varchar(255) NOT NULL default '', `First_name` varchar(50) NOT NULL default '', `Last_name` varchar(30) NOT NULL default '', PRIMARY KEY (`ID`) ) ENGINE=MEMORY SELECT Static_field, Parent_table, First_name, Last_name FROM ( ( SELECT ID, Static Text AS Static_field, staff1 AS Parent_table, First_name, Last_name FROM staff1 LIMIT 10, 5 ) UNION ALL ( SELECT ID, Static Text AS Static_field, staff2 AS Parent_table, First_name, Last_name FROM staff2 LIMIT 20, 5 ) UNION ALL ( SELECT ID, Static Text AS Static_field, staff3 AS Parent_table, First_name, Last_name FROM staff3 LIMIT 30, 5 ) ) AS TMP; Regards, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Temporary table ERROR 1109 (42S02) where are temporary tables kept?
I ran the following commands: USE snort; CREATE TEMPORARY TABLE sidtemp SELECT cid FROM event WHERE timestamp '2006-05-01'; ... SELECT count(*) from sidtemp; count(*) 7501376 DELETE FROM data WHERE data.cid = sidtemp.cid; ERROR 1109 (42S02): Unkown table 'sidtemp' in where clause SHOW tables; Does not include sidtemp in the list of tables in the snort database nor would I expect it to. Question: What database is the table sidtemp in? r/Raymond
Re: Temporary table ERROR 1109 (42S02) where are temporary tables kept?
Jacob, Raymond A Jr wrote: I ran the following commands: USE snort; CREATE TEMPORARY TABLE sidtemp SELECT cid FROM event WHERE timestamp '2006-05-01'; ... SELECT count(*) from sidtemp; count(*) 7501376 DELETE FROM data WHERE data.cid = sidtemp.cid; ERROR 1109 (42S02): Unkown table 'sidtemp' in where clause SHOW tables; Does not include sidtemp in the list of tables in the snort database nor would I expect it to. Question: What database is the table sidtemp in? r/Raymond You have the wrong syntax. You can't mention a table in the WHERE clause that wasn't in the FROM clause. Try DELETE data FROM data JOIN sidtemp ON data.cid = sidtemp.cid; or DELETE data FROM data, sidtemp WHERE data.cid = sidtemp.cid; See the manual for details http://dev.mysql.com/doc/refman/4.1/en/delete.html. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Temporary table ERROR 1109 (42S02) where are temporary tables kept?
Thank you, I was definitely on the wrong track on this one. I annotated your commands to make sure that I understood what they were doing. Are my comments correct? --- You have the wrong syntax. You can't mention a table in the WHERE clause that wasn't in the FROM clause. Try DELETE data FROM data JOIN sidtemp ON data.cid = sidtemp.cid; /* deletes all records in data with cid equal cid in sidtemp but leaves sidtemp unchanged */ or DELETE data FROM data, sidtemp WHERE data.cid = sidtemp.cid; /* deletes all records in data and sidtemp where cids are equal */ -- /* Will the USING clause work also? */ or /* looking that link below: */ DELETE data FROM data USING data, sidtemp WHERE data.cid = sidtemp.cid or DELETE data FROM data USING data, sidtemp JOIN sidtemp ON data.cid = sidtemp.cid Is the above correct also? Thank you, raymond -- See the manual for details http://dev.mysql.com/doc/refman/4.1/en/delete.html. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Temporary table ERROR 1109 (42S02) where are temporary tables kept?
Jacob, Raymond A Jr wrote: Thank you, I was definitely on the wrong track on this one. I annotated your commands to make sure that I understood what they were doing. Are my comments correct? --- You have the wrong syntax. You can't mention a table in the WHERE clause that wasn't in the FROM clause. Try DELETE data FROM data JOIN sidtemp ON data.cid = sidtemp.cid; /* deletes all records in data with cid equal cid in sidtemp but leaves sidtemp unchanged */ Correct. or DELETE data FROM data, sidtemp WHERE data.cid = sidtemp.cid; /* deletes all records in data and sidtemp where cids are equal */ No, this only deletes from data. These 2 are supposed to be equivalent. Using this version of the syntax deletes rows from the tables named *before* the FROM. Tables used to determine the matching rows come after the FROM. The first version I gave uses an explicit JOIN, the second uses the implicit, comma join. I prefer explicit joins, but I included the implicit join because it seemed to be what you were trying. -- /* Will the USING clause work also? */ It should. or /* looking that link below: */ DELETE data FROM data USING data, sidtemp WHERE data.cid = sidtemp.cid or DELETE data FROM data USING data, sidtemp JOIN sidtemp ON data.cid = sidtemp.cid Is the above correct also? Almost. In the USING form, the tables which should lose rows go after FROM, while the tables used to make the selection go after USING. Hence, the query would be DELETE FROM data USING data JOIN sidtemp ON data.cid = sidtemp.cid; or DELETE FROM data USING data, sidtemp WHERE data.cid = sidtemp.cid; In general, the implicit join syntax (comma between tables with join condition(s) in the WHERE clause) seems easy to use, but frequently leads to trouble. The explicit join syntax (table JOIN table ON condition) is much clearer, which should help avoid mistakes. You should probably read the manual page describing JOIN syntax http://dev.mysql.com/doc/refman/4.1/en/join.html. Thank you, raymond Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Temporary table ERROR 1109 (42S02) where are temporary tables kept?
Thanks again, raymond -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 05, 2006 14:54 To: Jacob, Raymond A Jr Cc: mysql@lists.mysql.com Subject: Re: Temporary table ERROR 1109 (42S02) where are temporary tables kept? Jacob, Raymond A Jr wrote: Thank you, I was definitely on the wrong track on this one. I annotated your commands to make sure that I understood what they were doing. Are my comments correct? --- You have the wrong syntax. You can't mention a table in the WHERE clause that wasn't in the FROM clause. Try DELETE data FROM data JOIN sidtemp ON data.cid = sidtemp.cid; /* deletes all records in data with cid equal cid in sidtemp but leaves sidtemp unchanged */ Correct. or DELETE data FROM data, sidtemp WHERE data.cid = sidtemp.cid; /* deletes all records in data and sidtemp where cids are equal */ No, this only deletes from data. These 2 are supposed to be equivalent. Using this version of the syntax deletes rows from the tables named *before* the FROM. Tables used to determine the matching rows come after the FROM. The first version I gave uses an explicit JOIN, the second uses the implicit, comma join. I prefer explicit joins, but I included the implicit join because it seemed to be what you were trying. -- /* Will the USING clause work also? */ It should. or /* looking that link below: */ DELETE data FROM data USING data, sidtemp WHERE data.cid = sidtemp.cid or DELETE data FROM data USING data, sidtemp JOIN sidtemp ON data.cid = sidtemp.cid Is the above correct also? Almost. In the USING form, the tables which should lose rows go after FROM, while the tables used to make the selection go after USING. Hence, the query would be DELETE FROM data USING data JOIN sidtemp ON data.cid = sidtemp.cid; or DELETE FROM data USING data, sidtemp WHERE data.cid = sidtemp.cid; In general, the implicit join syntax (comma between tables with join condition(s) in the WHERE clause) seems easy to use, but frequently leads to trouble. The explicit join syntax (table JOIN table ON condition) is much clearer, which should help avoid mistakes. You should probably read the manual page describing JOIN syntax http://dev.mysql.com/doc/refman/4.1/en/join.html. Thank you, raymond Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DROP TEMPORARY TABLE waiting for table???
I'm seeing something that I don't think should be happening, but I'm not sure if it's a MySQL bug. To allow some of my stored procedures to operate concurrently, I make a temporary memory copy of some shared tables that are accessed by the procedures. The temporary heap table has the same name as the table I'm copying. The scenario here is a data warehouse with a bunch of data marts and some shared dimensions that are in a separate database. I'm copying the necessary rows of the commonly used shared dimension tables, to overcome the problem of stored procedures locking all the tables they're going to use, which was preventing concurrency. The problem is that despite this, I'm seeing processes that are stuck with status Waiting for table when they are trying to drop the temporary table if it exists (DROP TEMPORARY TABLE IF EXISTS shared_dimensions.page_dim). I always drop and recreate it at the start of a series of analyses, so that they have the most recent copy. I create the temporary heap table in an independent procedure, so it can't be locking the table. There are other procedures using their own temporary heap table copies of the table I'm copying, but they're not using the real table, only the copy. So... my question is, why is there any problem dropping a table that should only be visible to the connection that's trying to drop it? What's even more bizarre is that I get this problem even when the temporary table doesn't exist, on a brand-new connection. I've had this code running for a couple of weeks and just noticed the problem, so I'm not sure if it cropped up right away or not. Haven't had a chance to bounce the server yet. Any insight appreciated. Nick -- Nick Arnett [EMAIL PROTECTED] Messages: 408-904-7198 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
temporary table issue
Hi, all I am trying to use this with error: drop temporary tabel temp_a if exists 'temp_a'; it said syntax error. Could anybody tell me the right syntax? I didn't find the answer after googling a while. Thanks in advance. Xiaobo -- Faculty of Computer Science Dalhousie University Halifax, Nova Scotia Canada -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: temporary table issue
Xiaobo Chen wrote:
Hi, all
I am trying to use this with error:
drop temporary tabel temp_a if exists 'temp_a';
it said syntax error.
Try:
DROP TEMPORARY TABLE IF EXISTS `temp_a`;
('table' instead of 'tabel'; table name only once; backticks around
table name instead of quotes)
http://dev.mysql.com/doc/refman/5.0/en/drop-table.html
Regards, Jigal.
smime.p7s
Description: S/MIME Cryptographic Signature
Re: temporary table issue
DROP TEMPORARY TABLE IF EXISTS `temp_a`; Xiaobo Chen wrote: Hi, all I am trying to use this with error: drop temporary tabel temp_a if exists 'temp_a'; it said syntax error. Could anybody tell me the right syntax? I didn't find the answer after googling a while. Thanks in advance. Xiaobo -- Bill Dodson Parkline, Inc. http://www.parkline.com phone: 304-586-2113 x149 fax: 304-586-3842 email: [EMAIL PROTECTED] Email Disclaimer The information in any email is confidential and may be legally privileged. It is intended solely for the addressee. Access to the email message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, or distribution of the message, or any action or omission taken by you in reliance on it, is prohibited and may be unlawful. If you have received an email message in error, please notify the sender immediately by email, facsimile or telephone and return and/or destroy the original message. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: temporary table issue
Hi, Jigal
Thanks a lot. It works.
Xiaobo Chen wrote:
Hi, all
I am trying to use this with error:
drop temporary tabel temp_a if exists 'temp_a';
it said syntax error.
Try:
DROP TEMPORARY TABLE IF EXISTS `temp_a`;
('table' instead of 'tabel'; table name only once; backticks around
table name instead of quotes)
http://dev.mysql.com/doc/refman/5.0/en/drop-table.html
Regards, Jigal.
--
Faculty of Computer Science
Dalhousie University
Halifax, Nova Scotia
Canada
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: temporary table issue
Thanks for the excellent advise.
In the mean while a friend Googled me an article suggesting that this
can be done using the POINT and SPATIAL INDEX. But I prefer your ideas.
I'll show what I found because I want to see if anybody has an opinion
on problems with either method:
Here I Store coordinates as POINT data types. Adding a SPATIAL INDEX on
the field. I can then select a rectangle against the index to include
all points I want:
SELECT AsText(point_field) FROM ...
WHERE MBRContains(
GeomFromText(
'polygon((x0 y0,x1 y0,x1 y1,x0 y1,x0 y0))'
), point_field);
This does use the SPATIAL INDEX. Therefore if my subset of points is in
a massive table, this *should* be the most efficient way.
But the index does not seem nearly as fast as liner (normal) indexes.
Hence using a less effective liner index may be better...
After this method I still need select a radius within these data points,
more effort.
Another answer I got suggests a third method just to make things worse:
Divide the area into cells. Give each cell an ID. Store data against
that ID. A bit like a hash-table for 2D data. For the area you want,
calculate all the ID's you need and select against them. Then sort the
data afterwards. Select a size of cell to suit most effective queries.
I will have to benchmark...
Thanks for the help,
Ben
Jigal van Hemert wrote:
Xiaobo Chen wrote:
Hi, all
I am trying to use this with error:
drop temporary tabel temp_a if exists 'temp_a';
it said syntax error.
Try:
DROP TEMPORARY TABLE IF EXISTS `temp_a`;
('table' instead of 'tabel'; table name only once; backticks around
table name instead of quotes)
http://dev.mysql.com/doc/refman/5.0/en/drop-table.html
Regards, Jigal.
--
Ben Clewett
+44(0)1923 46
Project Manager
Road Tech Computer Systems Ltd
http://www.roadrunner.uk.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: temporary table issue
Ben Clewett wrote: But the index does not seem nearly as fast as liner (normal) indexes. Hence using a less effective liner index may be better... After this method I still need select a radius within these data points, more effort. This could be an excellent case for using HAVING expr The WHERE can use an index and be very fast selecting the records that might fall within the circle. The HAVING clause will be executed after the record set is built and remove the records outside the circle, but within the square. If the number of records you select is pretty limited (a few hundred or so) the POW() calculations will not ask too much from the server, but the full table scan is avoided... Regards, Jigal. smime.p7s Description: S/MIME Cryptographic Signature
sun.jdbc.rowset.CachedRowSet and Temporary Table.
Because the SHOW FULL COLUMNS FROM command does not apply to temporary table, a
java program using Connector/J
is not able to use the sun.jdbc.rowset.CachedRowSet to retrieve the data from a
temporary table.
Does anyboby have any suggestions?
Thank you.
Here is the version of mysql server and MySQL Connector/J
mysql \s
--
mysql Ver 14.12 Distrib 5.0.15, for Win32 (ia32)
Connection id: 3
Current database: fmms
Current user: [EMAIL PROTECTED]
SSL:Not in use
Using delimiter:;
Server version: 5.0.15-nt-log
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset:latin1
Db characterset:latin1
Client characterset:latin1
Conn. characterset:latin1
TCP port: 3306
Uptime: 29 min 58 sec
Threads: 1 Questions: 97 Slow queries: 0 Opens: 5 Flush tables: 1 Open tabl
es: 12 Queries per second avg: 0.054
--
MySQL Connector/J 3.1.8
This is the stored procedure created in the test database.
It creates a temporary table, populates with one record and it returns the
content of the temporary table.
DROP PROCEDURE IF EXISTS mytempsp
//
CREATE PROCEDURE mytempsp()
BEGIN
CREATE TEMPORARY TABLE mytemptable (i INT, a VARCHAR(16));
INSERT INTO mytemptable(i, a) VALUES (1, First);
SELECT i, a FROM mytemptable;
END
//
Here is the content of log produced by the java program
051028 12:17:06 16 Connect [EMAIL PROTECTED] on test
16 Query SET NAMES latin1
16 Query SET character_set_results = NULL
16 Query SHOW VARIABLES
16 Query SHOW COLLATION
16 Query SET autocommit=1
16 Query SHOW CREATE PROCEDURE `test`.`mytempsp`
16 Query call mytempsp()
16 Query SHOW CHARACTER SET
16 Query SHOW FULL COLUMNS FROM `test`.`mytemptable`
16 Quit
Here is the system output of the java program:
Obtained connection
Executed query [EMAIL PROTECTED]
SQLException: Table 'test.mytemptable' doesn't exist
SQLState: 42S02
VendorError: 1146
Shutting down..
Here is the java code that invokes the stored procedure
/*
* Created on Oct 27, 2005
*
* TODO To change the template for this generated file go to
* Window - Preferences - Java - Code Style - Code Templates
*/
package com.transcore.dao;
import java.sql.Connection;
import java.sql.CallableStatement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import sun.jdbc.rowset.CachedRowSet;
/**
* @author frondonig
*
* TODO To change the template for this generated type comment go to
* Window - Preferences - Java - Code Style - Code Templates
*/
public class TemporaryTable {
private Connection connection = null;
private CallableStatement statement = null;
private ResultSet rs = null;
public TemporaryTable()
{
// Load the driver to allow connection to the db
try {
String driverName = com.mysql.jdbc.Driver;
Class.forName(driverName);
connection =
DriverManager.getConnection(jdbc:mysql://localhost:3306/test?user=rootpassword=mypass);
System.out.println(Obtained connection);
} catch (ClassNotFoundException cnfex) {
System.out.println(Failed to load jdbc driver);
cnfex.printStackTrace();
System.exit(1);
} catch (SQLException sqlex) {
// handle SQL errors
System.out.println(SQLException: +
sqlex.getMessage());
System.out.println(SQLState: + sqlex.getSQLState());
System.out.println(VendorError: +
sqlex.getErrorCode());
} catch (Exception ex) {
// handle any other errors
System.out.println(Exception: + ex.getMessage());
}
}
public void shutDown() {
System.out.println(Shutting down..);
try {
if (connection != null )
connection.close();
} catch (SQLException sqlex) {
// handle SQL errors
System.out.println(Unable to disconnect);
sqlex.printStackTrace();
}
}
public void TempTableViaStoredProcedure() throws Exception {
if ( connection != null ) {
try
CREATE TEMPORARY TABLE error
Nice to meet you.
I am andou.
Slave DB : though is several-time re-synchronization. It is sure to have
entered the state that the process of the slave stops because of the same
error.
Excerpt from show slave status(a part of Fge)
Last_error: error 'unexpected success or fatal error' on query 'CREATE
TEMPORARY TABLE book_continue_target_data SELECT t1.***, t2.id AS ***,
t2.* , t2.*** AS last_day FROM *** AS t1, *** AS t2, service AS t3
WHERE (DATE_FORMAT(t2.***, %Y-%m-%d) BETWEEN '2004-10-21' AND
'2004-10-25') AND t1.id=t2.*** AND t2.service_id=t3.id AND t2.status
IN ('active', 'continue', 'dropping', 'withdrawing') AND t3.name_en
='***''
Is it a problem of the bug of MySQL or the communication because the
stopping part is every time different?
There is no packet loss a similar error occurred though
re-synchronization was tested for the time being by changing
set-variable=innodb_lock_wait_timeout=200 (50 ahead).
Master Server and Slave Server installs it from
MySQL-client-4.0.14-0.i386.rpm in MySQL.
OS uses RedHat AS.
Because a similar error had not been seen to be reported, it
contributed.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
read-only and CREATE TEMPORARY TABLE (was: safe way of replication?)
Speaking of the --read-only option, I don't suppose there's any way to run --read-only but allow CREATE TEMPORARY TABLE, is there? We run several reports against a slave server which require temporary tables for speed, and have had a problem with the occasional accidental write to the slave. The slave server is running MySQL 4.0.20. Eamon Daly - Original Message - From: Keith Ivey [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, March 09, 2005 2:11 PM Subject: Re: safe way of replication? Atle Veka wrote: Even if you replicate the 'mysql' DB, GRANT/REVOKE statements are not replicated, nor are FLUSH statements. So if you are adding new access privileges on the master they will not be active on the slave until you issue FLUSH PRIVILEGES (one the slave). What version are you talking about? GRANT and REVOKE seem to be replicated fine nowadays. I remember some bugs related to their replication, but they were about replicating them when they shouldn't be (when the mysql DB wasn't being replicated), not failing to replicate them when they should be. Also FLUSH PRIVILEGES is replicated as of version 4.1.1, according to the documentation. The original poster might want to look into the read-only option to prevent accidental modification of the slave data: | --read-only | | This option causes the slave to allow no updates except from | slave threads or from users with the SUPER privilege. This can | be useful to ensure that a slave server accepts no updates | from clients. | | This option is available as of MySQL 4.0.14. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
create indexes for temporary table
Hi,
I want to create indexes for temporary created tables in perl dbi.
The following is perl code that I tried, but perl dbi seems not allow
the syntax:
Unable to execute our query PastSales:You have an error in your SQL
syntax; check the manual that corresponds to your MySQL server version
for the right syntax to use near 'alter table tmp_pastsales add
index(salescode,basename,prodcode);' at line 1 Unable to execute our
query Sales:You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'alter table tmp_sales add index(salescode,basename,prodcode);'
at line 1
This is the actual perl code I use:
$tmp_sql = qq{alter table tmp_pastsales add
index(salescode,basename,prodcode);};
$sth_tmp = $dbh-prepare($tmp_sql) or print Unable to prep our
query:.$dbh-errstr.\n;
$sth_tmp-execute or print Unable to execute our query
PastSales:.$dbh-errstr.\n;
Thanks
Sam
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: show temporary table
Sam, You can use 'show tables' with a like clause if you prefix your temporary tables with a string such as 'tmp_'. See the manual for syntax: http://dev.mysql.com/doc/mysql/en/show-tables.html Clint From: sam wun [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: show temporary table Date: Sun, 23 Jan 2005 15:47:22 +0800 Hi, How can I see all temporary tables that created by Create Temporary Table command in mysql 5.01? thanks Sam -- 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: create indexes for temporary table
Sam,
Remove the double quotes and semi-colon out of this statement:
$tmp_sql = qq{alter table tmp_pastsales add
index(salescode,basename,prodcode)};
Clint
From: sam wun [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Subject: create indexes for temporary table
Date: Sun, 23 Jan 2005 18:49:51 +0800
Hi,
I want to create indexes for temporary created tables in perl dbi.
The following is perl code that I tried, but perl dbi seems not allow the
syntax:
Unable to execute our query PastSales:You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the
right syntax to use near 'alter table tmp_pastsales add
index(salescode,basename,prodcode);' at line 1 Unable to execute our query
Sales:You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'alter table tmp_sales add index(salescode,basename,prodcode);' at line 1
This is the actual perl code I use:
$tmp_sql = qq{alter table tmp_pastsales add
index(salescode,basename,prodcode);};
$sth_tmp = $dbh-prepare($tmp_sql) or print Unable to prep our
query:.$dbh-errstr.\n;
$sth_tmp-execute or print Unable to execute our query
PastSales:.$dbh-errstr.\n;
Thanks
Sam
--
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]
show temporary table
Hi, How can I see all temporary tables that created by Create Temporary Table command in mysql 5.01? thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Indicating an INDEX during TEMPORARY table creation
When executing: CREATE TEMPORARY TABLE temp_1 AS SELECT * FROM table_name Is it possible to indicate an index within the syntax of this statement - as opposed to issuing an ALTER TABLE command after the table is created? Thanks in advance for any help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Indicating an INDEX during TEMPORARY table creation
You will need to know the name of the column(s) you will be indexing in order to do this. create temporary table some_table (index idx_name(col1) SELECT * from source_table -Original Message- From: David Perron To: [EMAIL PROTECTED] Sent: 8/11/04 11:36 AM Subject: Indicating an INDEX during TEMPORARY table creation When executing: CREATE TEMPORARY TABLE temp_1 AS SELECT * FROM table_name Is it possible to indicate an index within the syntax of this statement - as opposed to issuing an ALTER TABLE command after the table is created? Thanks in advance for any help. -- 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: Self joins with a temporary table
I found this conversation when searching the archive for information about selfjoins on temporary tables. I was primarily wondering what in one of future releases mean. What branch and what timeframe are we talking about? stable, beta, development? in a year, a month, or? regards, Gustav Munkby Sinisa Milivojevic writes: Benjamin Stapley writes: I don't seem to be able to perform a self-join with a temporary table. Is the any good reason why this should not work? CREATE TEMPORARY table test TYPE=HEAP SELECT . FROM . WHERE ..; SELECT T.term , Q.term FROM test as T, test as Q WHERE .; This generates the error message :- Can't reopen table: 'T' Many thanks Ben Stapley Hi! This is a known issue. We shall try to fix it in one of future releases. Regards, Sinisa -- NEU: WLAN-Router für 0,- EUR* - auch für DSL-Wechsler! GMX DSL = supergünstig kabellos http://www.gmx.net/de/go/dsl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select query that uses a temporary table
Lorderon [EMAIL PROTECTED] wrote: Mabye, is there a way to tell MySQL to limit the temporary table up to 500 rows? so, when a row is matching into the top 500 rows, the last row will be dropped out (in case the table is on limit), and the new matched row will be inserted into the right place in the temporary table... To find out top 500 of 10,000 rows ordered by some criteria you anyway need to sort these 10,000 rows. :) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Create Temporary Table, incorrect rows
Good day to all. I have a table and want to find duplicate info in the table. I know duplicated info exists by running the following... Select count(*) from myTable - 141123 Select distinct(myData) from myTable - 1411000 So I created a temporary table to store distinct records and want to do a join with original table to see what records are duplicates. Create Temporary Table A Select distinct(mydata),rowID from myTable; When I run this it says that the temp table has only 1000 rows. Why? God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Temporary Table update
I added a LIMIT clause to my create table command and now all data is present. Is the 1000 row limit a standard that must always be overridden? God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create Temporary Table, incorrect rows
On Tuesday 06 July 2004 15:30, Paul McNeil might have typed: When I run this it says that the temp table has only 1000 rows. Why? Are you using mysqlcc? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create Temporary Table, incorrect rows
A more direct way to find dupes ... SELECT id, COUNT( id ) AS cnt, FROM myTable GROUP BY id HAVING cnt 1 PB - Original Message - From: Paul McNeil To: MySQL General Sent: Tuesday, July 06, 2004 9:30 AM Subject: Create Temporary Table, incorrect rows Good day to all. I have a table and want to find duplicate info in the table. I know duplicated info exists by running the following... Select count(*) from myTable - 141123 Select distinct(myData) from myTable - 1411000 So I created a temporary table to store distinct records and want to do a join with original table to see what records are duplicates. Create Temporary Table A Select distinct(mydata),rowID from myTable; When I run this it says that the temp table has only 1000 rows. Why? God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: Create Temporary Table, incorrect rows
Thanks. That is a much better way to find dupes. God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 06, 2004 11:11 AM To: Paul McNeil; MySQL General Subject: Re: Create Temporary Table, incorrect rows A more direct way to find dupes ... SELECT id, COUNT( id ) AS cnt, FROM myTable GROUP BY id HAVING cnt 1 PB - Original Message - From: Paul McNeil To: MySQL General Sent: Tuesday, July 06, 2004 9:30 AM Subject: Create Temporary Table, incorrect rows Good day to all. I have a table and want to find duplicate info in the table. I know duplicated info exists by running the following... Select count(*) from myTable - 141123 Select distinct(myData) from myTable - 1411000 So I created a temporary table to store distinct records and want to do a join with original table to see what records are duplicates. Create Temporary Table A Select distinct(mydata),rowID from myTable; When I run this it says that the temp table has only 1000 rows. Why? God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create Temporary Table, incorrect rows
On Tue, Jul 06, 2004 at 10:30:38AM -0400, Paul McNeil wrote: Good day to all. I have a table and want to find duplicate info in the table. I know duplicated info exists by running the following... Select count(*) from myTable - 141123 Select distinct(myData) from myTable - 1411000 I presume there's a typo here: count(*) should have returned a larger number than distinct(myData), not smaller. So I created a temporary table to store distinct records and want to do a join with original table to see what records are duplicates. Create Temporary Table A Select distinct(mydata),rowID from myTable; This is a different query than above. What cout do you get from this query: Select distinct(mydata),rowID from myTable; Yes, it will be huge, but you want that count of selected rows. God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. -- Brian Reichert [EMAIL PROTECTED] 37 Crystal Ave. #303Daytime number: (603) 434-6842 Derry NH 03038-1713 USA BSD admin/developer at large -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Create Temporary Table, incorrect rows
Yes I am using mysqlcc. Is that why the 1000 row limit? God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -Original Message- From: Duncan Hill [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 06, 2004 11:05 AM To: [EMAIL PROTECTED] Subject: Re: Create Temporary Table, incorrect rows On Tuesday 06 July 2004 15:30, Paul McNeil might have typed: When I run this it says that the temp table has only 1000 rows. Why? Are you using mysqlcc? -- 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: Create Temporary Table, incorrect rows
On Tuesday 06 July 2004 17:20, Paul McNeil wrote: Yes I am using mysqlcc. Is that why the 1000 row limit? Yes. It's under the server options tag (don't have it in front of me to give exact details). If you right click the server connection, under I think properties, there's a query limit. Set to 0 and no limit. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select query that uses a temporary table
Hi All, There's something that bothers me.. I have a query that uses a temporary table (has a necessary GROUP BY clause). The query also uses ORDER BY clause (necessary too). And I also use LIMIT clause. If the query finds 10,000 rows, then MySQL will insert 10,000 rows into the temporary table and sort, which makes the query very slow... :( Any suggestions on how can I speed it up? (I would be satisfied with the top 500 rows, no need in all the 10,000) Mabye, is there a way to tell MySQL to limit the temporary table up to 500 rows? so, when a row is matching into the top 500 rows, the last row will be dropped out (in case the table is on limit), and the new matched row will be inserted into the right place in the temporary table... -thanks, Lorderon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DROP TEMPORARY TABLE and implicit commits
Hi, I guess since it is documented, it is a new feature - I agree with the principal of not backporting it. Many thanks for the reply - can't wait for 4.1 to mature :-) Thanks, Mike -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: 15 June 2004 13:00 To: Mysql Subject: Re: DROP TEMPORARY TABLE and implicit commits Michael, - Alkuperäinen viesti - Lähettäjä: Michael McTernan [EMAIL PROTECTED] Vastaanottaja: Mysql [EMAIL PROTECTED] Kopio: [EMAIL PROTECTED] Lähetetty: Monday, June 14, 2004 9:40 PM Aihe: DROP TEMPORARY TABLE and implicit commits Hi there, I'm using MySQL 4.0.18 and finding that DROP TEMPORARY TABLE is performing an implicit commit, as is documented. The problem is that I have a complex query in a Java function, and I need to call it a number of times to get the overall, which I also want to do as a single transaction so as not to leave data inconsistent at any point. The implicit commit is causing me a problem here, the only solution to which I can think is to use 4.1.0 (which is alpha) or to do some horrible table name mangling for the temp table and just accumulate a lot of data for the duration of the transaction. Does anyone know if it is planned to back port the fix in 4.1.0 onto 4.0.21 or later? I am sorry, 4.0 is frozen from new features. The backport will probably not happen. From 4.1.0 changelog: - DROP TEMPORARY TABLE now drops only temporary tables and doesn't end transactions. Thanks, Mike Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL support from http://www.mysql.com/support/index.html -- 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: DROP TEMPORARY TABLE and implicit commits
Michael, - Alkuperäinen viesti - Lähettäjä: Michael McTernan [EMAIL PROTECTED] Vastaanottaja: Mysql [EMAIL PROTECTED] Kopio: [EMAIL PROTECTED] Lähetetty: Monday, June 14, 2004 9:40 PM Aihe: DROP TEMPORARY TABLE and implicit commits Hi there, I'm using MySQL 4.0.18 and finding that DROP TEMPORARY TABLE is performing an implicit commit, as is documented. The problem is that I have a complex query in a Java function, and I need to call it a number of times to get the overall, which I also want to do as a single transaction so as not to leave data inconsistent at any point. The implicit commit is causing me a problem here, the only solution to which I can think is to use 4.1.0 (which is alpha) or to do some horrible table name mangling for the temp table and just accumulate a lot of data for the duration of the transaction. Does anyone know if it is planned to back port the fix in 4.1.0 onto 4.0.21 or later? I am sorry, 4.0 is frozen from new features. The backport will probably not happen. From 4.1.0 changelog: - DROP TEMPORARY TABLE now drops only temporary tables and doesn't end transactions. Thanks, Mike Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL support from http://www.mysql.com/support/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DROP TEMPORARY TABLE and implicit commits
Hi there, I'm using MySQL 4.0.18 and finding that DROP TEMPORARY TABLE is performing an implicit commit, as is documented. The problem is that I have a complex query in a Java function, and I need to call it a number of times to get the overall, which I also want to do as a single transaction so as not to leave data inconsistent at any point. The implicit commit is causing me a problem here, the only solution to which I can think is to use 4.1.0 (which is alpha) or to do some horrible table name mangling for the temp table and just accumulate a lot of data for the duration of the transaction. Does anyone know if it is planned to back port the fix in 4.1.0 onto 4.0.21 or later? From 4.1.0 changelog: - DROP TEMPORARY TABLE now drops only temporary tables and doesn't end transactions. Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [SOLVED?] Access Denied for CREATE TEMPORARY TABLE
On Friday 11 June 2004 06:59 pm, Robert Paulsen wrote: I must be missing something about create temporary table. Here are two sql commands. The first works the second fails: CREATE TABLE mytable (id int(10) NOT NULL auto_increment, data varchar(255), PRIMARY KEY (id) ); CREATE TEMPORARY TABLE mytable2 (id int(10) NOT NULL auto_increment, data varchar(255), PRIMARY KEY (id) ); Prior to issuing the above commands I used the following grant command: GRANT ALL ON MYDB.* TO [EMAIL PROTECTED] identified by 'password' I also tried the following: GRANT CREATE TEMPORARY TABLE ON MYDB.* TO [EMAIL PROTECTED] identified by 'password' but it didn't help. What am I missing? Well, I haven't done an exhaustive analysis, but I *think* there is a mysql bug (I'm at 4.0.18). The GRANT statement is not supposed to require a FLUSH PRIVILEGES and this seems to be true for the CREATE TABLE privilege but not for the CREATE TEMPORARY TABLE privilege. I say this because my problem went away when I used mysqladmin to flush-privileges. -- Robert C. Paulsen, Jr. [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
