Coordinator (0.6.x): SQLException: Can't create/write to file '/tmp/#sql_2cc7_0.MYI'

[Wayne Adams]

Hi everyone:

  I'm just learning the Indexing Service on 0.6.x.  I've noticed I see this every time I start the coordinator and wonder if that could be the reason a test-file ingest succeeds without any metadata showing up in MySQL.  Here's a chunk of log:

2014-01-08 23:16:14,757 INFO [main] com.metamx.common.lifecycle.Lifecycle$AnnotationBasedHandler - Invoking start method[public void io.druid.server.coordinator.DruidCoordinator.start()] on object[io.druid.server.coordinator.DruidCoordinator@7d9dd722].
2014-01-08 23:16:14,762 ERROR [DatabaseRuleManager-Exec--0] io.druid.db.DatabaseRuleManager - Exception while polling for rules
org.skife.jdbi.v2.exceptions.CallbackFailedException: org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: java.sql.SQLException: Can't create/write to file '/tmp/#sql_2cc7_0.MYI' (Errcode: 13) [statement:"SELECT r.dataSource, r.payload FROM druid_rules r INNER JOIN(SELECT dataSource, max(version) as version FROM druid_rules GROUP BY dataSource) ds ON r.datasource = ds.datasource and r.version = ds.version", located:"SELECT r.dataSource, r.payload FROM druid_rules r INNER JOIN(SELECT dataSource, max(version) as version FROM druid_rules GROUP BY dataSource) ds ON r.datasource = ds.datasource and r.version = ds.version", rewritten:"SELECT r.dataSource, r.payload FROM druid_rules r INNER JOIN(SELECT dataSource, max(version) as version FROM druid_rules GROUP BY dataSource) ds ON r.datasource = ds.datasource and r.version = ds.version", arguments:{ positional:{}, named:{}, finder:[]}]
        at org.skife.jdbi.v2.DBI.withHandle(DBI.java:262)
        at io.druid.db.DatabaseRuleManager.poll(DatabaseRuleManager.java:198)
        at io.druid.db.DatabaseRuleManager$2.run(DatabaseRuleManager.java:170)
        at com.metamx.common.concurrent.ScheduledExecutors$1.call(ScheduledExecutors.java:62)
        at com.metamx.common.concurrent.ScheduledExecutors$1.call(ScheduledExecutors.java:58)
        at com.metamx.common.concurrent.ScheduledExecutors$2.run(ScheduledExecutors.java:99)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
        at java.util.concurrent.FutureTask.run(FutureTask.java:262)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:178)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:292)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
        at java.lang.Thread.run(Thread.java:744)
Caused by: org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: java.sql.SQLException: Can't create/write to file '/tmp/#sql_2cc7_0.MYI' (Errcode: 13) [statement:"SELECT r.dataSource, r.payload FROM druid_rules r INNER JOIN(SELECT dataSource, max(version) as version FROM druid_rules GROUP BY dataSource) ds ON r.datasource = ds.datasource and r.version = ds.version", located:"SELECT r.dataSource, r.payload FROM druid_rules r INNER JOIN(SELECT dataSource, max(version) as version FROM druid_rules GROUP BY dataSource) ds ON r.datasource = ds.datasource and r.version = ds.version", rewritten:"SELECT r.dataSource, r.payload FROM druid_rules r INNER JOIN(SELECT dataSource, max(version) as version FROM druid_rules GROUP BY dataSource) ds ON r.datasource = ds.datasource and r.version = ds.version", arguments:{ positional:{}, named:{}, finder:[]}]
        at org.skife.jdbi.v2.SQLStatement.internalExecute(SQLStatement.java:1306)
        at org.skife.jdbi.v2.Query.fold(Query.java:172)
        at io.druid.db.DatabaseRuleManager$3.withHandle(DatabaseRuleManager.java:205)
        at io.druid.db.DatabaseRuleManager$3.withHandle(DatabaseRuleManager.java:201)

Obviously, no one's going to be writing to a file named '/tmp/#sql...'.  This is from a fresh deployment, new install of everything, nothing else going on except mysqld started and Zookeeper running.  Any ideas?  Is the above enough info to point to an obvious misconfiguration, etc.?  Thanks much!

Regards, Wayne

[Eric Tschetter]

I think that's actually an error bubbling up from mysql. Make sure
that your mysql is running with a user that can write to /tmp (perhaps
try doing an insert or something directly against mysql without even
including Druid). If that works, make sure Druid can write to /tmp.
I'm not sure what's trying to write there, but something obviously is.

--Eric

> --
> You received this message because you are subscribed to the Google Groups
> "Druid Development" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to druid-developm...@googlegroups.com.
> To post to this group, send email to druid-de...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/druid-development/328c3ff4-762a-4aef-9423-f63a5a359246%40googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.

[Fangjin Yang]

This might also help: http://dev.mysql.com/doc/refman/5.0/en/cannot-create.html

To view this discussion on the web visit https://groups.google.com/d/msgid/druid-development/CAB8U%2Bh2Ye0ryCRi1n87drLNfZe71rTX-zqQsTnkivSkWwb-L7w%40mail.gmail.com.

[Wayne Adams]

Thanks, Eric and Fangjin:

  In my haste to mount /tmp on a larger partition, I had failed to set the permissions on it properly.  So I changed them, but that turns out not to solve the problem.  I start MySQL as a service under root.  Whether /tmp is owned by ec2-user or root, with 777 either way, doesn't seem to make a difference.  I reconfig'ed MySQL to write to a different tmp directory, played around with permissions on that directory, and still get the same error.

  This seems to be a common problem, but it did not start until I mounted /tmp on its own partition.  I suppose I'll have to back that out and hope I can return to where I was.  For an example of how widespread (and unresolved :( ) this issue is, see posts like

http://stackoverflow.com/questions/13867387/mysqldump-cant-create-write-to-file-error-from-amazon-rds

I'm going to see if unmounting /tmp helps next.

-- Wayne

[Wayne Adams]

Update:  unmounting /tmp did not help, but following up that action with a node reboot did.  Perhaps if I had rebooted after the original mount of /tmp I would not have had this problem to begin with.  Now that I know how to set the tmp directory for Middle Manager nodes, I don't need the large /tmp mount anyway.  So my load is now running with no mysql errors in the coordinator log.