Member Avatar for wish02

how do i fix this? the error says that the column count does not match value count.
thanks in advance!:)
--
-- Definition of table `assets`
--

DROP TABLE IF EXISTS `assets`;
CREATE TABLE `assets` (
`_id` int(10) NOT NULL auto_increment,
`_controlNum` varchar(255) NOT NULL,
`_category` varchar(255) NOT NULL,
`_subcategory` varchar(255) NOT NULL,
`_brand` varchar(255) NOT NULL,
`_product` varchar(255) default NULL,
`_memory` varchar(255) default NULL,
`_processor` varchar(255) default NULL,
`_motherboard` varchar(255) default NULL,
`_graphicsCard` varchar(255) default NULL,
`_hardDrive` varchar(255) default NULL,
`_productNum` varchar(255) default NULL,
`_serialNum` varchar(255) default NULL,
`_warrantyPeriod` varchar(255) default NULL,
`_warrantyEnd` date default NULL,
`_datePurchased` date NOT NULL,
`_supplier_id` int(10) default NULL,
`_cost` float default NULL,
`_status` varchar(255) default NULL,
`_assignedDate` date default NULL,
`_expectedLifeCycle` date default NULL,
`_retirementNoticeDate` date default NULL,
`_retireDate` date default NULL,
`_workstation_id` int(10) default NULL,
`_user_accounts_id` int(10) default NULL,
PRIMARY KEY (`_id`),
KEY `_supplier_id` (`_supplier_id`),
KEY `_workstation_id` (`_workstation_id`),
KEY `_user_accounts_id` (`_user_accounts_id`),
CONSTRAINT `assets_ibfk_1` FOREIGN KEY (`_supplier_id`) REFERENCES `supplier` (`_id`),
CONSTRAINT `assets_ibfk_2` FOREIGN KEY (`_workstation_id`) REFERENCES `workstation` (`_id`),
CONSTRAINT `assets_ibfk_3` FOREIGN KEY (`_user_accounts_id`) REFERENCES `user_accounts` (`_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `assets`
--

/*!40000 ALTER TABLE `assets` DISABLE KEYS */;
INSERT INTO `assets` (`_id`,`_controlNum`,`_category`,`_subcategory`,`_brand`,`_product`,`_memory`,`_processor`,`_motherboard`,`_graphicsCard`,`_hardDrive`,`_productNum`,`_serialNum`,`_warrantyPeriod`,`_datePurchased`,`_supplier_id`,`_cost`,`_status`,`_assignedDate`,`_expectedLifeCycle`,`_retirementNoticeDate`,`_retireDate`,`_workstation_id`,`_user_accounts_id`) VALUES
(1,'MTR00001','Hardware','Monitor','NEC','52V-bk 15\" LCD',NULL,NULL,NULL,NULL,NULL,'105a2V-bk21','03RK001BV','1 Year','2011-06-19','2010-06-19',1,3459,'In Use','0000-00-00','2010-07-01',NULL,NULL,1,3),
(2,'CPU00001','Hardware','CPU','Stilla','CB+ 100','mem1','proc1','moth1','gcar1','hddr1','105a2V-bk21','03RK001BV','1 Year','2011-06-19','2010-06-19',1,3459,'In Use','0000-00-00','2010-07-01',NULL,NULL,1,3),
(3,'KYB00001','Hardware','Keyboard','Logitech','K110',NULL,NULL,NULL,NULL,NULL,'105a2V-bk21','03RK001BV','1 Year','2011-06-19','2010-06-19',1,3459,'In Use','0000-00-00','2010-07-01',NULL,NULL,1,3),
(4,'MSE00001','Hardware','Mouse','Logitech','M110',NULL,NULL,NULL,NULL,NULL,'105a2V-bk21','03RK001BV','1 Year','2011-06-19','2010-06-19',1,3459,'In Use','0000-00-00','2010-07-01',NULL,NULL,1,3),
(5,'APP00001','Software','Microsoft Excel 2007','Microsoft','Excel 2007',NULL,NULL,NULL,NULL,NULL,'105a2V-bk21','03RK001BV','1 Year','2011-06-19','2010-06-19',1,3459,'In Use','0000-00-00','2010-07-01',NULL,NULL,1,3),
(6,'MTR00002','Hardware','Monitor','NEC','52V-bk 15\" LCD',NULL,NULL,NULL,NULL,NULL,'105a2V-bk21','03RK001BV','1 Year','2011-06-19','2010-06-19',1,3459,'In Use','0000-00-00','2010-07-01',NULL,NULL,2,2),
(7,'CPU00002','Hardware','CPU','Stilla','CB+ 200','mem2','proc2','moth2','gcar2','hddr1','105a2V-bk21','03RK001BV','1 Year','2011-06-19','2010-06-19',1,3459,'In Use','0000-00-00','2010-07-01',NULL,NULL,2,2),
(8,'KYB00002','Hardware','Keyboard','Logitech','K111',NULL,NULL,NULL,NULL,NULL,'105a2V-bk21','03RK001BV','1 Year','2011-06-19','2010-06-19',1,3459,'In Use','0000-00-00','2010-07-01',NULL,NULL,2,2),
(9,'MSE00002','Hardware','Mouse','Logitech','M111',NULL,NULL,NULL,NULL,NULL,'105a2V-bk21','03RK001BV','1 Year','2011-06-19','2010-06-19',1,3459,'In Use','0000-00-00','2010-07-01',NULL,NULL,2,2),
(10,'APP00002','Software','Microsoft Powerpoint 2007','Microsoft','Powerpoint 2007',NULL,NULL,NULL,NULL,NULL,'105a2V-bk21','03RK001BV','1 Year','2011-06-19','2010-06-19',1,3459,'In Use','0000-00-00','2010-07-01',NULL,NULL,2,2);
/*!40000 ALTER TABLE `assets` ENABLE KEYS */;

  • 2 Contributors
  • 3 Replies
  • 183 Views
  • 17 Hours Discussion Span
  • Latest Post Latest Post by wish02

Dani AI

Generated

Brief diagnosis and practical checks for "Column count doesn't match value count":

The error simply means the number of columns you listed in the INSERT does not equal the number of values in each VALUES row. In this dump the table definition had one more column than the INSERT column list, so each VALUES tuple supplied an extra value and the server rejected the statement. The OP later confirmed the import was fixed.

Quick ways to verify (safe read-only commands):

  • Run DESCRIBE assets; or SHOW CREATE TABLE assets; and count the table columns.
  • Compare that count to the column names inside the INSERT's parentheses.
  • For multi-row INSERTs make sure each parenthesised row has the same number of values and that strings are correctly quoted (an unescaped quote or stray comma in a string will break the value count).

How to fix and best practices:

  • Either add the missing column(s) to the INSERT column list or remove the extra value(s). Alternatively supply NULL or DEFAULT for the missing column.
  • Prefer always specifying the column list in INSERT (and omit the auto_increment id when you want the server to assign it).
  • Validate multi-row dumps in a text editor or with a small script that counts columns vs values before importing.
  • As noted, declare auto_increment keys as UNSIGNED to avoid subtle mismatches on older servers; that advice is good practice but applies to a different class of problems (foreign-key/engine mismatches).

If the error persists after these checks, inspect for mismatched quotes, stray commas, or SQL mode issues (strict mode and zero dates can surface other errors). In rare cases an InnoDB metadata mismatch will show different errors and requires server-side investigation rather than fixing the INSERT itself.

Recommended Answers

All 3 Replies

Member Avatar for tesuji

Hi,

there can be a discrepancy between the innodb data dictionary entry for table assets and additional information still holding separately in assets.frm file (really a mess, mysql should eventually learn not to store information about a table schema in various places! Such data must be hold in data dictionary (system catalog, system tables) soley).

You may look at the server's error log. If there is an error message saying that table assets already exists, try to DROP TABLE assets. If all else fails, instead of trying to fix this odd problem it is often easier to give the concerning table just another name.

btw, it is always a good idea to declare columuns having auto_increment option unsigned int, e.g. _id unsigned not null auto_increment. On older Mysql versions using int together with auto_increment also mixing int and unsigned int when defining primary and foreign keys sometimes produced similar odd errors.

--tesu

Member Avatar for tesuji

Sorry, my answer should be for your older post MySQL errorno:150.


Which column 'count' you actually mean? Where is it hidden in?


Possibly you should consider what i already stated for errorno 150:

btw, it is always a good idea to declare columuns having auto_increment option unsigned int, e.g. _id unsigned not null auto_increment. On older Mysql versions using int together with auto_increment also mixing int and unsigned int when defining primary and foreign keys sometimes produced similar odd errors.

-- tesu

Member Avatar for wish02

i got it already :) thanks anyway :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.