Re: Partitioning throwing #1064 - Constant/Random expression in (sub)partitioning function is not allowed near ') ( PARTITION p1 VALUES IN (1), PARTITION p2 VALUES IN (2) PARTIT' at line 2 error

[Phil Hildebrand]

Currently there is a restriction on partitioning that does not allow user defined functions for a partition key ( http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html )

You would likely have to use a integer that defines your state code in the user table, and then a lookup table (if you need it) user_states or something that has the defined mapping for states to integers (NY=1,CA=2, etc)

Then you could just create your list partition on state_code assuming you modify your table to be something like:

create table user
( xxx varchar....
state_code integer
xxx xxxxx ....
) partition by list (state_code)