Cannot find an index in the referenced table where the referenced columns appear as the first columns

I'm creating mysql table following the link https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html

But I keep getting this error

FOREIGN KEY (serial_no) REFERENCES tag_master(orig_serial_no) ON UPDATE CASCADE ON DELETE RESTRICT:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html for correct foreign key definition.

Here are tables:

create table tag_master(
        orig_part_no VARCHAR(70) CHARACTER SET ascii NOT NULL,
        orig_serial_no VARCHAR(56) CHARACTER SET ascii NOT NULL,
        regist_part_no VARCHAR(70) CHARACTER SET ascii,
        regist_serilal_no VARCHAR(56) CHARACTER SET ascii,
        regist_comment VARCHAR(126) CHARACTER SET ascii,
        PRIMARY KEY (orig_part_no, orig_serial_no)
) ENGINE=INNODB;

create table reader_table(
        reader_id  VARCHAR(70) CHARACTER SET ascii NOT NULL,
        name VARCHAR(128) CHARACTER SET ascii,
        PRIMARY KEY (reader_id)
) ENGINE=INNODB;

create table tag_log(
        id BIGINT AUTO_INCREMENT,
        part_no VARCHAR(70) CHARACTER SET ascii NOT NULL,
        serial_no VARCHAR(56) CHARACTER SET ascii NOT NULL,
        access_date DATE,
        latitude FLOAT,
        longitude FLOAT,
        reader_id VARCHAR(70) CHARACTER SET ascii NOT NULL,
        current_part_no VARCHAR(105) CHARACTER SET ascii,
        current_serial_no VARCHAR(70) CHARACTER SET ascii,
        current_comment VARCHAR(128) CHARACTER SET ascii,
        PRIMARY KEY (id),

        INDEX (part_no, serial_no),
        INDEX (reader_id),

        FOREIGN KEY(part_no, serial_no) REFERENCES tag_master(orig_part_no, orig_serial_no) ON UPDATE CASCADE ON DELETE RESTRICT,
        FOREIGN KEY(reader_id) REFERENCES reader_table(reader_id)

) ENGINE=INNODB;

The foreign key for the part_no and reader_id work but somehow I can't create foreign key serial_no to orig_serial_no. I tried ALTER TABLE tag_log ADD FOREIGN KEY (serial_no) REFERENCES tag_master(orig_serial_no) ON UPDATE CASCADE ON DELETE RESTRICT; but it's still doesn't work, it doesn't make any sense.

The desc tag_log table look like this:

+-------------------+--------------+------+-----+---------+----------------+
| Field             | Type         | Null | Key | Default | Extra          |
+-------------------+--------------+------+-----+---------+----------------+
| id                | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| part_no           | varchar(70)  | NO   | MUL | NULL    |                |
| serial_no         | varchar(56)  | NO   |     | NULL    |                |
| access_date       | date         | YES  |     | NULL    |                |
| latitude          | float        | YES  |     | NULL    |                |
| longitude         | float        | YES  |     | NULL    |                |
| reader_id         | varchar(70)  | NO   | MUL | NULL    |                |
| current_part_no   | varchar(105) | YES  |     | NULL    |                |
| current_serial_no | varchar(70)  | YES  |     | NULL    |                |
| current_comment   | varchar(128) | YES  |     | NULL    |                |
+-------------------+--------------+------+-----+---------+----------------+

OS: Ubuntu 18.04

MySQL: mysql Ver 14.14 Distrib 5.7.29, for Linux (x86_64) using EditLine wrapper Server version: 5.7.29-0ubuntu0.18.04.1 (Ubuntu)

Solution

This foreign key...

FOREIGN KEY (serial_no) 
    REFERENCES tag_master(orig_serial_no) 
    ON UPDATE CASCADE ON DELETE RESTRICT

... requires an index on tag_master(orig_serial_no), or at least a compound index where orig_serial_no appears first. This is not the case in your current set up, where the primary key of tag_master is (part_no, serial_no) (the concerned column appears in second position, not first).

One way around this would be to change the order of the columns in the primary key of tag_master:

PRIMARY KEY (orig_serial_no, orig_part_no)

This requires you to also change the order of columns in the compound foreign key in tag_log that references both columns:

FOREIGN KEY(serial_no, part_no) 
    REFERENCES tag_master(orig_serial_no, orig_part_no) 
    ON UPDATE CASCADE ON DELETE RESTRICT

Then you can create the additional foreign key, as demonstrated in this db fiddle.

But, that being said, I just do not see the point for creating this additional foreign key, since you already have another key that covers it (FOREIGN KEY(serial_no, part_no)). Most likely, you just do not need that additional constraint, since the functionalities it offers are aleardy provided by the compound key.