DB - "Unexpected Update Count received (Actual 2, Expected 1)"

创建于 2021年04月17日 17:25

I upgraded today to version 2021.1 and since the upgrade I can no longer delete duplicate rows in my DB. I select a row for deletion and get the error above. I have looked at posts from 2018 where it was suggested I add a column ID for each row as a auto increment, primary key, but storm would not let me do that as there would then be a "Null Error" . Maybe this will not help, but here is the schema for the table. BTW - I am no DB expert :o)

Thanks!

-- auto-generated definition
create table teacher_attendance
(
    teacherId  int         null,
    timeIn     varchar(10) null,
    timeOut    varchar(10) null,
    time       int         null,
    date       varchar(10) null,
    created_at timestamp   null,
    updated_at timestamp   null
)
    charset = latin1;

2 条评论

Andriy Bazanov

创建于 2021年04月18日 10:09

Hi there,

Yes, you need to have some unique key per table as it will save you from many issues, like the above one for example.

If you cannot make a unique key from the existing fields (which is the case as they all allow NULL) then just insert some "idRec" of INT type (or just "id" ... or whatever the name you will give it) as a first column and make it autoincrement primary unique key.

It should look something like this in PhpStorm Database viewer (MySQL database):

id int unsigned auto_increment primary key

E.g. this is what PhpStorm shows for me for a table where such field is used:

create table if not exists contactus
(
id int unsigned auto_increment primary key,
created_at timestamp null,
updated_at timestamp null,
status tinyint unsigned default 1 not null,
person_name varchar(250) not null,
person_email varchar(200) not null,
app_info varchar(250) null,
message text null,
notes text null
);

The same viewed using Navicat app:

CREATE TABLE `contactus` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `status` tinyint(3) unsigned NOT NULL DEFAULT '1',
  `person_name` varchar(250) NOT NULL,
  `person_email` varchar(200) NOT NULL,
  `app_info` varchar(250) DEFAULT NULL,
  `message` text,
  `notes` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

The same as a Laravel migration file (Laravel v5.8) -- in case if this will give you any hints:

    public function up(): void
    {
        Schema::create('contactus', static function (Blueprint $table) {
            $table->increments('id');
            $table->timestamps();
            $table->unsignedTinyInteger('status')->default(1);
            $table->string('person_name', 250);
            $table->string('person_email', 200);
            $table->string('app_info', 250)->nullable();
            $table->text('message')->nullable();
            $table->text('notes')->nullable();
        });
    }

Vincej

创建于 2021年04月19日 04:29

Hi Andriy, Thank you for getting back to me so quickly!

Ok - so I tried your recommendation through the Database UI twice on the production server, and on both occasions I got the error:

[42000][1075] Incorrect table definition; there can be only one auto column and it must be defined as a key.

This is curious as I checked the table / column definitions four times and there is no "auto column" with in the table, other that the one within your instructions:

make it autoincrement primary unique key

I have a belief that the DB Storm UI is creating the problem. The UI generates some SQL code first adding an "alter table, auto increment" , and then generates a second SQL statement with a second "alter table, auto increment, primary key", and then a third "alter table unique key".

Ok - so instead I went to my local development development server where I have PHPMyAdmin installed. I applied your instructions through that, and it successfully created the new column "Id" with the auto increment. I grabbed the SQL code off of PhpMyAdmin and pasted it to the MySql Terminal within Storm, and bingo it worked for the production server. Furthermore, I managed to delete the duplicate entries however, now since the upgrade to 2021.1, I can only delete two rows at a time, where prior to the upgrade I could delete as many as I needed all at once.

Thanks again, I am amazed you do not get bored supporting the user base. You are a rock star!

请先登录再写评论。