Error 1025 (errno -1 unknown error -1) on Alter Table Command

MySQL & MariaDB Other MySQL® Questions
1

Hi, I’ve been trying to alter a Table named import_permit, adding about 12 new columns for it, using the alter statements:


ALTER TABLE eips2live.import_permit ADD `closed_by_id` bigint(20) DEFAULT NULL;
ALTER TABLE eips2live.import_permit ADD `date_closed` datetime DEFAULT NULL;
ALTER TABLE eips2live.import_permit ADD `date_disapproved` datetime DEFAULT NULL;
ALTER TABLE eips2live.import_permit ADD `date_for_cancellation` datetime DEFAULT NULL;
ALTER TABLE eips2live.import_permit ADD `disapproved_by_id` bigint(20) DEFAULT NULL;
ALTER TABLE eips2live.import_permit ADD `for_cancellation_by_id` bigint(20) DEFAULT NULL;
ALTER TABLE eips2live.import_permit ADD `for_cancellation_by_etrade` varchar(255) DEFAULT NULL;
ALTER TABLE eips2live.import_permit ADD `reason_for_disapproval` varchar(255) DEFAULT NULL;
ALTER TABLE eips2live.import_permit ADD `remarks_for_cancellation` varchar(255) DEFAULT NULL;
ALTER TABLE eips2live.import_permit ADD `remarks_for_close` varchar(255) DEFAULT NULL;
ALTER TABLE eips2live.import_permit ADD `remarks_for_closed` varchar(255) DEFAULT NULL;
ALTER TABLE eips2live.import_permit ADD `date_opened` datetime DEFAULT NULL;

but always ended with Error

Code: 1025. Error on rename of './eips2live/#sql-5e74_42' to './eips2live/import_permit' (errno: -1 - Unknown error -1)

checking my mysql-error.log, I can see this log about the same time the error appears on workbench:

InnoDB: Error: 'eips2live/import_permit' is already in tablespace memory cache

I have been trying to find this errno-1 everywhere but the closest one I got was err 150 which seems to be related to constraints, but I still have to read more of it. Another one was a MySQL Bug, which uses the same errno: -1 and Unknown error -1, but I do not know what to make of it, like how to avoid/resolve this current issue.

Hoping someone can give me a heads up on what to do/where to look for next. thanks.

Here is my my.cnf for reference.

[mysql]

# CLIENT #
#port = 3306

[mysqld]

# GENERAL #
server-id = 2002
user = mysql
default-storage-engine = InnoDB
socket = /srv/data/mysql/mysql.sock
pid-file = /srv/data/mysql/mysql.pid
bind-address = 0.0.0.0

# MyISAM #
key-buffer-size = 32M
myisam-recover = FORCE,BACKUP

# SAFETY #
max-allowed-packet = 16M
max-connect-errors = 1000000
#sql-mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE
sysdate-is-now = 1
innodb = FORCE

# DATA STORAGE #
datadir = /srv/data/mysql/

# BINARY LOGGING #
#log-bin = /srv/data/mysql/mysql-bin
binlog-format = row
log-bin = slavedb-bin
expire-logs-days = 14
sync-binlog = 1

# CACHES AND LIMITS #
#tmp-table-size = 32M
#max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 500
thread-cache-size = 50
open-files-limit = 65535
#table-definition-cache = 4096
#table-open-cache = 4096

# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
#innodb-log-file-size = 512M
innodb-log-file-size = 5M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 26G

# LOGGING #
log-error = /srv/data/mysql/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /srv/data/mysql/mysql-slow.log
2

Hi paacharon3;

I think you are hitting the error because you are trying to alter the datatype of a foreign key to something different than the matching parent/child table. Take a look at the below article for a longer explanation:

[url]http://www.xaprb.com/blog/2006/08/22/mysqls-error-1025-explained/[/url]

If it turns out that this is your issue, then you’ll have to drop the foreign key constraints, modify the datatypes of the foreign key in both the parent/child table, and then re-create the foreign key constraints.

Also in case you didn’t already know, you can do all of your alters to the same table in one statement (once the issue is fixed).

-Scott

3

Hi Scott,

Would it be possible to fix the issue if we recreate the table with the updated data types for constraints? like doing the manual alter table of creating the new table with the new fields and dump the data, or would it be more efficient/faster to just drop the constraints/FK and let Grails (since our backend is grails)make them again? I’ll have to look into our foreign keys again to check the datatypes.

And oh, I tried to separate the alter statements because I thought if we added only select fields with no foreign key/constraint it wouldn’t break the alter table. Many thanks for the advice!

4

Hi paacharon3;

Fixing the existing tables seems easier to me, but however you think is best for your environment is the way I would go. =)

-Scott

5

Hi Scott,

After checking the datatypes of the parent/child table, I found out that they are same. Also, running the same alter statements on our test mysql server did not result. Running the show create table command, i found the constraint keys for the table in question:

#Production
PRIMARY KEY (`id`),
UNIQUE KEY `number_idx` (`number`) USING BTREE,
KEY `FKA91C99F55455F4BA` (`logistics_id`),
KEY `FKA91C99F52B0515CE` (`applied_by_id`),
KEY `FKA91C99F527C1A33A` (`broker_id`),
KEY `FKA91C99F5C24CC3CC` (`tagged_as_received_by_id`),
KEY `FKA91C99F5F98A365A` (`zone_id`),
KEY `FKA91C99F58F841FA2` (`cancelled_by_id`),
KEY `FKA91C99F5FE3CE878` (`loa_id`),
KEY `FKA91C99F517A9CDA8` (`tagged_as_used_by_id`),
KEY `FKA91C99F5E8C68A3A` (`port_id`),
KEY `FKA91C99F5906E984A` (`origin_id`),
KEY `FKA91C99F5FF81483B` (`tagged_as_delivered_by_id`),
KEY `FKA91C99F59163A7F9` (`created_by_id`),
KEY `FKA91C99F547DB3888` (`approved_by_id`),
KEY `FKA91C99F5B2521F8C` (`updated_by_id`),
KEY `FKA91C99F5D3A0B9DA` (`locator_id`),
KEY `idx_import_permit_no` (`number`) USING BTREE,
KEY `import_permit_locator_idx` (`locator_id`) USING BTREE,
KEY `import_permit_logistics_idx` (`logistics_id`) USING BTREE,
KEY `locator_id_idx` (`locator_id`) USING BTREE,
KEY `logistics_id_idx` (`logistics_id`) USING BTREE,
KEY `origin_id_idx` (`origin_id`) USING BTREE,
KEY `port_id_idx` (`port_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

while for our test database which did not result in an error had this:


PRIMARY KEY (`id`),
UNIQUE KEY `number_idx` (`number`) USING BTREE,
KEY `FKA91C99F55455F4BA` (`logistics_id`),
KEY `FKA91C99F52B0515CE` (`applied_by_id`),
KEY `FKA91C99F527C1A33A` (`broker_id`),
KEY `FKA91C99F5C24CC3CC` (`tagged_as_received_by_id`),
KEY `FKA91C99F5F98A365A` (`zone_id`),
KEY `FKA91C99F58F841FA2` (`cancelled_by_id`),
KEY `FKA91C99F5FE3CE878` (`loa_id`),
KEY `FKA91C99F517A9CDA8` (`tagged_as_used_by_id`),
KEY `FKA91C99F5E8C68A3A` (`port_id`),
KEY `FKA91C99F5906E984A` (`origin_id`),
KEY `FKA91C99F5FF81483B` (`tagged_as_delivered_by_id`),
KEY `FKA91C99F59163A7F9` (`created_by_id`),
KEY `FKA91C99F547DB3888` (`approved_by_id`),
KEY `FKA91C99F5B2521F8C` (`updated_by_id`),
KEY `FKA91C99F5D3A0B9DA` (`locator_id`),
KEY `idx_import_permit_no` (`number`) USING BTREE,
KEY `import_permit_locator_idx` (`locator_id`) USING BTREE,
KEY `import_permit_logistics_idx` (`logistics_id`) USING BTREE,
KEY `locator_id_idx` (`locator_id`) USING BTREE,
KEY `logistics_id_idx` (`logistics_id`) USING BTREE,
KEY `origin_id_idx` (`origin_id`) USING BTREE,
KEY `port_id_idx` (`port_id`) USING BTREE,
KEY `FKA91C99F53A44FD0A` (`for_cancellation_by_id`),
KEY `FKA91C99F5AC9AAC96` (`disapproved_by_id`),
KEY `FKA91C99F5BB50177D` (`closed_by_id`),
CONSTRAINT `FKA91C99F53A44FD0A` FOREIGN KEY (`for_cancellation_by_id`) REFERENCES `account` (`id`),
CONSTRAINT `FKA91C99F5AC9AAC96` FOREIGN KEY (`disapproved_by_id`) REFERENCES `account` (`id`),
CONSTRAINT `FKA91C99F5BB50177D` FOREIGN KEY (`closed_by_id`) REFERENCES `account` (`id`)

Can enlighten me if I need to remove all these keys before issuing the alter statements? Also, checking the datatypes of the reference field (field id from account table)

the structure is:

`id` bigint(20) NOT NULL DEFAULT '0',

while for the import_permit table which contains the supposed to be foreign keys, it has

`for_cancellation_by_id` bigint(20) DEFAULT NULL,

`disapproved_by_id` bigint(20) DEFAULT NULL, 

`closed_by_id` bigint(20) DEFAULT NULL,

they are both of type bigint(20), so am I safe from the foreign key issue? or do I need to check for other things, or check in another way? Many Thanks!

6

Hi paacharon3;

It’s a bit hard to follow your train of thought as to what you are actually doing and the differences between the two environments, so I would just start from the bottom and see what’s what.

First thing I would do is verify which of your alters it is failing on. Does it fail on them all, or does it only fail on one (or more)? That might help narrow it down.

If it fails on all of them, then I would start by doing a simple alter of the table like “alter table eips2live.import_permit engine=innodb;” and see if it will rebuild the table as-is, and then try to run your alters again if that succeeds. If that fails, then I would try dumping the table with mysqldump and then re-importing it. You could modify the dump file and change the table name to have it import as a different table for testing, and then try to do your alters on that. That should help narrow down if it is a problem with the table itself or with what you are trying to do with the table.

-Scott

7

Hi Scott,

Sorry if I didn’t explain it that well, It seems I started somewhere in the middle. I was trying to add 12 new columns for the table “import_permit”. On our test database server, all the alter statements went through. no errors. But when I tried it on our staging database server, All the alter statements failed.

yesterday on the Staging Server, I tried creating a table similar to the import_permit table, and tried adding the new columns for it, and surprisingly it didn’t fail, so I was starting to think the problem may have been the table itself.

I will try testing the simple alter statement and dumping the table/reimporting it to see if it succeeds. Thanks again!

8

Tips to fix-
Solution one: Remove any protective tape from the print cartridges
Solution two: Print from a different software program
Solution three: Adjust, reload, and change the paper
Solution four: Check the printer environment
Solution five: Check the printer connections
Solution six: Clean inside the printer

Regards,
Rachel Gomez