Mysql – Partitioning not working need primary key

MySQLpartitioning

We are trying some partitioning as below.

ALTER TABLE tblreceipt PARTITION BY RANGE (MONTH(receiptDate)) 
( 
PARTITION p1 VALUES LESS THAN (TO_DAYS('2012-10-20')), 
PARTITION p2 VALUES LESS THAN (TO_DAYS('2012-10-21')), 
PARTITION p3 VALUES LESS THAN (TO_DAYS('2012-10-22')) 
);

but end up with this Error Code: 1503
A PRIMARY KEY must include all columns in the table's partitioning function

I have done the changes now.

ALTER TABLE tblreceipt PARTITION BY RANGE (receiptDate) 
( 
PARTITION p1 VALUES LESS THAN '2012-10-20', 
PARTITION p2 VALUES LESS THAN '2012-10-21', 
PARTITION p3 VALUES LESS THAN '2012-10-22' 
);

Error Code: 1064
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 ''2012-10-20', 
PARTITION p2 VALUES LESS THAN '2012-10-21', 
PARTITION p3 VALUES ' at line 3

Best Answer

The problem is that the function (MONTH()) and the LESS THAN values are incompatible.

Apart from the fact that I don't see the value of using TO_DAYS() (what is the advantage there compared to a simple date?), you should use (TO_DAYS(receiptDate)) instead of (MONTH(receiptDate)).

Or, more simply (if you want a day-by-day partitioning), I'd write just

RANGE COLUMNS (receiptDate) 
... 
LESS THAN ('2012-10-20')

See a very simple working example at SQLFiddle.

Related Solutions

MySQL Join – Troubleshooting Full Outer Join Not Working

So far as I know, you can't do a full outer join in MySQL, so just run it as the UNION of a LEFT JOIN and a RIGHT JOIN as

SELECT * FROM 
cajas LEFT JOIN almacenes ON almacenes.codigo = cajas.almacen
UNION
SELECT * FROM 
cajas RIGHT JOIN almacenes ON almacenes.codigo = cajas.almacen;

I think this would fix your problem.

EDIT: After a quick Googling, I realized that the above may not be perfect for all cases. Instead you could do

SELECT * FROM 
cajas LEFT JOIN almacenes ON almacenes.codigo = cajas.almacen
UNION ALL
SELECT * FROM 
cajas RIGHT JOIN almacenes ON almacenes.codigo = cajas.almacen
WHERE cajas.almacen IS NULL;

See this blog post for reference.

Mysql – on duplicate key update

^{Community Wiki answer originally based on notes by ypercubeᵀᴹ and Rick James suggested in the comments on the question.}

The immediate issue is the parentheses around the UPDATE set list: they should not be there according to the syntax. That is what the error message is about.

Once you have fixed that one, there are other issues with your INSERT statement.

The IGNORE keyword just does not make sense when used together with ON DUPLICATE KEY. You either perform an update when a duplicate key is encountered, or you ignore the duplicate key entry completely, and it looks as though you want the former – therefore, lose the IGNORE keyword.
Setting product_id to '' in the ON DUPLICATE KEY clause is not consistent with setting it to NULL in the VALUES clause: an empty string ('') is not the same as a null. Judging from the column name, you probably want to have NULL specified in both places.
Similarly, seeting id to '1' in the ON DUPLICATE KEY clause is not consistent with setting it to 1 in the VALUES clause: the string ('1') is not the same as the number (1). This will probably not be an issue as the string will be converted to a number but it doesn't hurt to be explicit and very careful with the types of your column and values you pass to them.
While this is not exactly a problem, you don't have to specify the same values twice (in VALUES and in ON DUPLICATE KEY UPDATE clauses). You can specify them once and use them in two places, using the VALUES() function, like this:
```
INSERT INTO shop_item_details 
  (shop_id, product_id, product_name, Product_model, 
   Product_category, Product_details, price) 
VALUES 
  (1, NULL, 'camera', 'sony', '', 'hd', '5000') 
ON DUPLICATE KEY UPDATE
  shop_id          = VALUES(shop_id), 
  product_id       = VALUES(product_id), 
  product_name     = VALUES(product_name), 
  Product_model    = VALUES(Product_model), 
  Product_category = VALUES(Product_category), 
  Product_details  = VALUES(Product_details), 
  price            = VALUES(price) ;
```
Please note that the above is just an example. Since you are updating the same set of columns as those you are inserting into, setting each of them using the column = VALUES(column) pattern is slightly redundant. The columns that define the unique (or primary) key being violated (you have not specified which columns they are in your case - perhaps shop_id and product_id? Or shop_id and product_name and product_model?) can be safely removed from the ON DUPLICATE KEY UPDATE clause.

Best Answer

Related Solutions

MySQL Join – Troubleshooting Full Outer Join Not Working

Mysql – on duplicate key update

Related Question