StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The reason of this not working is not so clear, I suspect a bug either in the documentation or in the error message. The error you are receiving is inappropriate, in my opinion.</p> <p>According to <a href="http://dev.mysql.com/doc/refman/5.5/en/partitioning-limitations.html" rel="nofollow">the manual:</a></p> <blockquote> <p>The following constructs are not permitted in partitioning expressions:</p> <ul> <li>Stored procedures, stored functions, UDFs, or plugins.</li> <li><strong>Declared variables</strong> or user variables.</li> </ul> </blockquote> <p>This explains why your table definition fails.</p> <p>Now, how to get rid of your variable? If you try to remove the declared variable from the partition definition:</p> <pre class="lang-sql prettyprint-override"><code>CREATE TABLE s_time ( completed INT UNSIGNED NOT NULL DEFAULT 0 ) PARTITION BY RANGE ( completed ) ( PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP() ) ); </code></pre> <p>you will get the same error. However, the <a href="http://dev.mysql.com/doc/refman/5.5/en/partitioning-range.html" rel="nofollow">MySQL manual</a> states:</p> <blockquote> <p>It is also possible to use an expression in VALUES LESS THAN clauses. However, MySQL must be able to <strong>evaluate the expression's return value as part of a LESS THAN (<) comparison</strong>.</p> </blockquote> <p>According to this definition, <code>UNIX_TIMESTAMP()</code> should be a valid expression. This sentence from the manual is inaccurate to say the least. I am very curious to see if someone else could provide another understanding.</p> <p>Now, looking at the error message, if you consider the <code>LESS THAN</code> clause as a part of the "partitioning function", then the error message starts making sense:</p> <blockquote> <p>ERROR 1064 (42000) : Constant, <strong>random</strong>, or timezone-dependent expression in (sub)partitioning function <em>or in LESS THAN clause</em> are not allowed</p> </blockquote> <p>By "random", they also mean <em>non-deterministic</em>, which the <code>UNIX_TIMESTAMP()</code> function is by definition.</p> <p>To achieve what you are trying to do, I see no other solution but using an external script to generate the suitable <code>ALTER TABLE</code> command.</p> <p>1) Create your initial table:</p> <pre class="lang-sql prettyprint-override"><code>CREATE TABLE s_time ( req_id INT NOT NULL, ser_id INT NOT NULL, hel_id INT NOT NULL, posted INT UNSIGNED NOT NULL, completed INT UNSIGNED NOT NULL DEFAULT 0 ) PARTITION BY RANGE (completed) ( PARTITION p0 VALUES LESS THAN (0), PARTITION p1 VALUES LESS THAN (1) ); </code></pre> <p>2) <a href="http://dev.mysql.com/doc/refman/5.5/en/partitioning-management-range-list.html" rel="nofollow">Reorganise the partitions</a> every now and then (e.g. with PHP):</p> <pre class="lang-php prettyprint-override"><code><?php $query = "ALTER TABLE s_time REORGANIZE PARTITION p0, p1 INTO ( PARTITION p0 VALUES LESS THAN ($today), PARTITION p1 VALUES LESS THAN ($tomorrow) )"; </code></pre> <p>See the <a href="http://sqlfiddle.com/#!2/11f35" rel="nofollow">proof of concept here</a>.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload