I think this this may be more of a MySQL problem rather than a Magento problem, but it is the Magento database I'm dealing with. Here's what I have done.
I got MySQL to log queries and found a query similar to the one I'm about to post. This query updates the catalog_product_entity_varchar table. I took out the other varchar attributes being updated and just left the one in question, just to make it more simple to read (there are a lot of attributes).
INSERT INTO `catalog_product_entity_varchar`
(`entity_type_id`,`attribute_id`,`store_id`,`entity_id`,`value`) VALUES
('4', '187', '0', '352203', 'asdf')
ON DUPLICATE KEY UPDATE `value` = VALUES(`value`)
This query, whether I put the entire query original with a whole bunch more attribute updates, or if I just throw in the query exactly as above, does not update the row. The exact query above actually says that two rows were inserted. No values in the database change. This is isolated to this table only in the database. There are no errors given. It simply says that x number of rows were inserted.
I decided to pull out a query from another similar entity table. I chose the text table. That query was as follows:
INSERT INTO `catalog_product_entity_text` (`entity_type_id`,`attribute_id`,`store_id`,`entity_id`,`value`)
VALUES ('4', '83', '0', '352203', 'test'), ('4', '106', '0', '352203', 'test')
ON DUPLICATE KEY UPDATE `value` = VALUES(`value`)
That worked as expected. So, I don't think it's an issue with ON DUPLICATE KEY working database wide -- it's isolated to the catalog_product_entity_varchar table.
I checked all tables, and no tables are locked or in use. I thought maybe I would have to commit the transaction, but I don't think that's the case here. Autocommit is on. I don't believe I can change that based on the table, but I'm not using START TRANSACTION, so I shouldn't have to commit anyway, right? I tested with a COMMIT after the statement, even thought I'm 99% certain that shouldn't do anything, but the results didn't change.
mysql> SHOW VARIABLES LIKE "%autocommit%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
I decided to check my development server (yes, this is happening in production) and the query works perfectly fine. So, this is isolated to the catalog_product_entity_varchar table AND this specific database.
I checked structure between the two databases, they are the same. See below
DEVELOPMENT STRUCTURE DUMP
CREATE TABLE IF NOT EXISTS `catalog_product_entity_varchar` (
`value_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Value ID',
`entity_type_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity Type ID',
`attribute_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Attribute ID',
`store_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store ID',
`entity_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity ID',
`value` varchar(255) DEFAULT NULL COMMENT 'Value',
PRIMARY KEY (`value_id`),
UNIQUE KEY `UNQ_CAT_PRD_ENTT_VCHR_ENTT_ID_ATTR_ID_STORE_ID` (`entity_id`,`attribute_id`,`store_id`),
KEY `IDX_CATALOG_PRODUCT_ENTITY_VARCHAR_ATTRIBUTE_ID` (`attribute_id`),
KEY `IDX_CATALOG_PRODUCT_ENTITY_VARCHAR_STORE_ID` (`store_id`),
KEY `IDX_CATALOG_PRODUCT_ENTITY_VARCHAR_ENTITY_ID` (`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Catalog Product Varchar Attribute Backend Table' AUTO_INCREMENT=211079 ;
--
-- Constraints for dumped tables
--
--
-- Constraints for table `catalog_product_entity_varchar`
--
ALTER TABLE `catalog_product_entity_varchar`
ADD CONSTRAINT `FK_CATALOG_PRODUCT_ENTITY_VARCHAR_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `FK_CAT_PRD_ENTT_VCHR_ATTR_ID_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `FK_CAT_PRD_ENTT_VCHR_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE;
PRODUCTION STRUCTURE DUMP
CREATE TABLE IF NOT EXISTS `catalog_product_entity_varchar` (
`value_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Value ID',
`entity_type_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity Type ID',
`attribute_id` int(10) unsigned NOT NULL COMMENT 'Attribute ID',
`store_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store ID',
`entity_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity ID',
`value` varchar(255) DEFAULT NULL COMMENT 'Value',
PRIMARY KEY (`value_id`),
UNIQUE KEY `UNQ_CAT_PRD_ENTT_VCHR_ENTT_ID_ATTR_ID_STORE_ID` (`entity_id`,`attribute_id`,`store_id`),
KEY `IDX_CATALOG_PRODUCT_ENTITY_VARCHAR_ATTRIBUTE_ID` (`attribute_id`),
KEY `IDX_CATALOG_PRODUCT_ENTITY_VARCHAR_STORE_ID` (`store_id`),
KEY `IDX_CATALOG_PRODUCT_ENTITY_VARCHAR_ENTITY_ID` (`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Catalog Product Varchar Attribute Backend Table' AUTO_INCREMENT=2147483647 ;
--
-- Constraints for dumped tables
--
--
-- Constraints for table `catalog_product_entity_varchar`
--
ALTER TABLE `catalog_product_entity_varchar`
ADD CONSTRAINT `FK_CATALOG_PRODUCT_ENTITY_VARCHAR_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `FK_CAT_PRD_ENTT_VCHR_ATTR_ID_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `FK_CAT_PRD_ENTT_VCHR_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE;
I have no idea why this is happening. I'm so confused.
Also, I did just recently restore this database from a backup, but it was happening prior to that restoration. I am using Percona -- the latest version. Settings for the database were lost, so I set it up from scratch. Here are all the settings I think might help.
mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+----------------------------------------------------- -+
| Variable_name | Value |
+-------------------------+----------------------------------------------------- -+
| innodb_version | 5.6.23-72.1 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.23-72.1 |
| version_comment | Percona Server (GPL), Release 72.1, Revision 0503478 |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+----------------------------------------------------- -+
7 rows in set (0.00 sec)
my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Skip reverse DNS lookup of clients
skip-name-resolve
# optimisations for Magento
# changed open_files_limit to 65535
open_files_limit = 65535
max_allowed_packet = 16M
wait_timeout = 360
# changed kbs from 128M to 32M
key_buffer_size = 32M
# changed query_cache_type = 0 and size to 0
query_cache_type = 1
query_cache_size = 16M
sort_buffer_size = 1M
thread_cache_size = 50
# changed innodb bps to 36G from 20G after RAM upgrade 24GB-48GB
innodb_buffer_pool_size = 39G
# innodb_buffer_pool_instances = 8
innodb_additional_mem_pool_size = 20M
innodb_log_buffer_size = 8M
innodb_file_per_table = 1
innodb_lock_wait_timeout = 1200
ft_min_word_len=2
# Added July 29, 2014
myisam_recover = FORCE,BACKUP
max_connect_errors = 1000000
expire_logs_days = 14
max_connections = 500
table_definition_cache = 4096
table_open_cache = 4096
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 1
general_log_file=/var/log/general_log.log
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
All right, I solve this myself.
The issue was the auto increment on the table. It reached its maximum. It was using an int(11), so a signed integer of 32 bits. Maximum value would be 2147483647.
I changed it to bigint(12) - So, the maximum number would be 999,999,999,999 I believe.
ALTER TABLE `catalog_product_entity_varchar`
CHANGE `value_id` `value_id` BIGINT( 12 )
NOT NULL AUTO_INCREMENT COMMENT 'Value ID';
This should be fine, since no foreign keys use this field. I think if there were foreign keys using it, you may need to change those as well to reflect the same data type.