mysql cardinality issue

1,024 Posts

Frank Reply #1, 17 years, 7 months ago

hi,

i am trying to recover a mistake. i had on table id set to tinyint. when i had more than 127 entries i ran into problmes. I changed the tinyint to int (10) but the cardinality is on 119 rows however there are 122 rows.
So when I try to insert data through a form, I get a duplicate entry 120 for key 1. When I delete a ros the cardinality goes down a row too. Any other solutions?

best, Frank.

☆ A M B ☆
2,475 Posts

Everett Reply #2, 17 years, 7 months ago

What you probably want to look up in MySQL documentation is "AUTO_INCREMENT" (with the underscore). When you take a table and do a "show create table my_table" on it, you’ll get something like this:

CREATE TABLE `my_table` (
  `id` bigint(20) NOT NULL auto_increment,
  `datestamp` datetime NOT NULL,
  `other` varchar(64) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `datestamp` (`datestamp`)
) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=latin1

Notice the AUTO_INCREMENT=16 bit at the end of that... what that’s saying is that currently, the table has 15 rows, and the next row that gets inserted should use 16 for its primary key (named "id" in this case). Every table you create with a primary key has this type of variable associated with it. That’s just how MySQL keeps track of where to insert rows... it’s keeping its thumb in the page...

I’ve never reset that number manually; usually I just drop the table and re-create it. Creating a new table will reset that number. It might be easier for you to backup the existing table information, drop the table, re-create it, then re-import your data.

Hope that helps.

Hosting stuff: http://fireproofsocks.com/
Coding stuff: http://craftsmancoding.com/
Blog stuff: http://tipsfor.us/

MODX Staff
1,535 Posts

Garry Nutting Reply #3, 17 years, 7 months ago

Try running this query against your table (do a table backup first just to be on the safe side):

ALTER TABLE `your_table` AUTO_INCREMENT 123;

That should push the auto increment up and will avoid the duplicate ID issue.

Garry Nutting
Senior Developer
MODX, LLC

Email: [email protected]
Twitter: @garryn
Web: modx.com

1,024 Posts

Frank Reply #4, 17 years, 7 months ago

Hi,

just recreated table, re-imported data and this is the show create output:

publisher CREATE TABLE `publisher` (
 `idpub` int(10) NOT NULL auto_increment,
 `abbreviation` varchar(30) default NULL,
 `country` varchar(255) default NULL,
 `street` varchar(255) default NULL,
 `postcode` varchar(255) default NULL,
 `city` varchar(255) default NULL,
 `address` varchar(255) default NULL,
 `name` varchar(255) default NULL,
 `website` varchar(255) default NULL,
 `email` varchar(255) default NULL,
 `oddities` varchar(255) default NULL,
 `phone` text,
 PRIMARY KEY  (`idpub`)
) ENGINE=MyISAM AUTO_INCREMENT=124 DEFAULT CHARSET=latin1

There are 123 rows and here ’s what the indexes values are:

   Indexes:   Keyname Type Cardinality Action Field 
                  PRIMARY  PRIMARY     123              idpub

duplicat key error is now row 1, so did next suggestion

ALTER TABLE `publisher` AUTO_INCREMENT 124;

okay I can add data, so now the test (cause this is where things go wrong), delete through a form:

back to the Duplicate entry ’123’ for key 1 error. I attached the deletion form. Hope someone can find a way out.

24,544 Posts

BobRay Reply #5, 17 years, 7 months ago

I’m no MySQL expert, but how about dumping the table to a CSV file, deleting the IDs from the CSV file, emptying the table in PhpMyAdmin, and importing the CSV file. I think you’ll need to have an extra comma at the beginning of each row to get the new IDs generated.

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

24,544 Posts

BobRay Reply #6, 17 years, 7 months ago

I would also try changing this:

 $sql = "DELETE FROM publisher WHERE idpub='$idpub'";

to this:

$sql

= "DELETE FROM publisher WHERE idpub='" . $idpub . "'";

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting