OiO.lk Community platform!

Oio.lk is an excellent forum for developers, providing a wide range of resources, discussions, and support for those in the developer community. Join oio.lk today to connect with like-minded professionals, share insights, and stay updated on the latest trends and technologies in the development field.
  You need to log in or register to access the solved answers to this problem.
  • You have reached the maximum number of guest views allowed
  • Please register below to remove this limitation

Error Code: 1054. Unknown column 'company_name' in 'field list' when it's clearly there

  • Thread starter Thread starter chik0di
  • Start date Start date
C

chik0di

Guest
I am developing an ERD Model in MySQL and I've been going through series of dropping schema and forward engineering again just to test an update in the modeling (I'm not conversant with synchronizing models just yet) but everything has been working just fine and okay.

I eventually used a MySQL/ODBC Connector to link Microsoft Access and make use of it as the Front-end. Everything still worked fine. Until recently and all of a sudden whenever I try to INSERT INTO the company table;

Code:
    INSERT INTO company(company_name, address, city_id, zip_code,
    country_code, kind_of_organization_id, number_of_employees, total_asset, 
    total_liability, registration_body_id, registration_date) 
VALUES
    ('Astro Planet Pictures', '90210 Rodeo Ave', 
    (SELECT city_id FROM city WHERE name = 'Athens'), 
    46543,
    (SELECT code FROM country WHERE country.name = 'Greece'), 
    (SELECT id FROM kind_of_organization WHERE name = 'Non Profit'), 
    45,
    393430.4, 98345.5, 
    (SELECT r.id FROM registration_body r, country c WHERE r.country_code = c.code AND c.name = 'Slovakia'), 
    '2020-01-04');

I get an error:

Code:
Error Code: 1054: Unknown column 'company_name' in 'field list'

A column "company_name" which has always been there from the start and never gave any issues during the INSERT INTO operation.

I cannot seem to identify what may be the cause of this problem or even trace a root, I need help as soon as possible.

I unsynced MS Access and MySQL just to make sure MySQL was acting alone in this instance.

I also changed the column name from 'company_name' to 'name' maybe an invisible character was at play even when I knew it wasn't the case. I don't have a lot of options at this point

For your references, this is the DDL of the COMPANY table

Code:
CREATE TABLE IF NOT EXISTS `movie_production_companies`.`company` (
  `id` VARCHAR(144) NOT NULL,
  `company_name` VARCHAR(144) NOT NULL,
  `address` VARCHAR(115) NOT NULL,
  `zip_code` INT NOT NULL,
  `city_id` INT NOT NULL,
  `country_code` CHAR(3) NOT NULL,
  `kind_of_organization_id` INT NOT NULL,
  `number_of_employees` INT NOT NULL,
  `total_asset` DECIMAL(10,2) NOT NULL,
  `total_liability` DECIMAL(10,2) NOT NULL,
  `registration_body_id` INT NOT NULL,
  `registration_date` DATE NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `country_id_idx` (`country_code` ASC) VISIBLE,
  INDEX `city_id_idx` (`city_id` ASC) VISIBLE,
  INDEX `kind_of_organization_fk_idx` (`kind_of_organization_id` ASC) VISIBLE,
  INDEX `regulatory_body_fk_idx` (`registration_body_id` ASC) VISIBLE,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
  CONSTRAINT `company_country_fk`
    FOREIGN KEY (`country_code`)
    REFERENCES `movie_production_companies`.`country` (`code`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `company_city_fk`
    FOREIGN KEY (`city_id`)
    REFERENCES `movie_production_companies`.`city` (`city_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `company_kind_of_organization_fk`
    FOREIGN KEY (`kind_of_organization_id`)
    REFERENCES `movie_production_companies`.`kind_of_organization` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `company_regulatory_body_fk`
    FOREIGN KEY (`registration_body_id`)
    REFERENCES `movie_production_companies`.`registration_body` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

And I restored the only Trigger associated with the COMPANY table to original format but nothing

Code:
CREATE DEFINER = CURRENT_USER TRIGGER `movie_production_companies`.`company_BEFORE_INSERT_pk_maker` 
BEFORE INSERT ON `company` 
FOR EACH ROW
BEGIN
    DECLARE space_count INT;
    
    SET space_count = LENGTH(company_name) - LENGTH(REPLACE(company_name, ' ', ''));
    
    IF space_count = 0 THEN
        SET NEW.id =  CONCAT(
            NEW.country_code, '-', 
            UPPER(LEFT(NEW.company_name, 3)), '-',
            FLOOR(100000 + RAND() * 900000)
        );
    ELSEIF space_count = 1 THEN
        SET NEW.id = CONCAT(
            NEW.country_code, '-', 
            UPPER(LEFT(NEW.company_name, 1)), 
            UPPER(LEFT(SUBSTRING_INDEX(NEW.company_name, ' ', -1), 2)),
            FLOOR(100000 + RAND() * 900000)
        );
    ELSE
        SET NEW.id = CONCAT(
        NEW.country_code, '-', 
        UPPER(LEFT(NEW.company_name, 1)), 
        UPPER(LEFT(SUBSTRING_INDEX(SUBSTRING_INDEX(NEW.company_name, ' ', 2), ' ', -1), 1)),
        UPPER(LEFT(SUBSTRING_INDEX(SUBSTRING_INDEX(NEW.company_name, ' ', 3), ' ', -1), 1)),
        FLOOR(100000 + RAND() * 900000)
        );
    END IF;
END;
<p>I am developing an ERD Model in MySQL and I've been going through series of dropping schema and forward engineering again just to test an update in the modeling (I'm not conversant with synchronizing models just yet) but everything has been working just fine and okay.</p>
<p>I eventually used a MySQL/ODBC Connector to link Microsoft Access and make use of it as the Front-end. Everything still worked fine. Until recently and all of a sudden whenever I try to INSERT INTO the company table;</p>
<pre><code> INSERT INTO company(company_name, address, city_id, zip_code,
country_code, kind_of_organization_id, number_of_employees, total_asset,
total_liability, registration_body_id, registration_date)
VALUES
('Astro Planet Pictures', '90210 Rodeo Ave',
(SELECT city_id FROM city WHERE name = 'Athens'),
46543,
(SELECT code FROM country WHERE country.name = 'Greece'),
(SELECT id FROM kind_of_organization WHERE name = 'Non Profit'),
45,
393430.4, 98345.5,
(SELECT r.id FROM registration_body r, country c WHERE r.country_code = c.code AND c.name = 'Slovakia'),
'2020-01-04');
</code></pre>
<p>I get an error:</p>
<pre><code>Error Code: 1054: Unknown column 'company_name' in 'field list'
</code></pre>
<p>A column "company_name" which has always been there from the start and never gave any issues during the INSERT INTO operation.</p>
<p>I cannot seem to identify what may be the cause of this problem or even trace a root, I need help as soon as possible.</p>
<p>I unsynced MS Access and MySQL just to make sure MySQL was acting alone in this instance.</p>
<p>I also changed the column name from 'company_name' to 'name' maybe an invisible character was at play even when I knew it wasn't the case. I don't have a lot of options at this point</p>
<p>For your references, this is the DDL of the COMPANY table</p>
<pre><code>CREATE TABLE IF NOT EXISTS `movie_production_companies`.`company` (
`id` VARCHAR(144) NOT NULL,
`company_name` VARCHAR(144) NOT NULL,
`address` VARCHAR(115) NOT NULL,
`zip_code` INT NOT NULL,
`city_id` INT NOT NULL,
`country_code` CHAR(3) NOT NULL,
`kind_of_organization_id` INT NOT NULL,
`number_of_employees` INT NOT NULL,
`total_asset` DECIMAL(10,2) NOT NULL,
`total_liability` DECIMAL(10,2) NOT NULL,
`registration_body_id` INT NOT NULL,
`registration_date` DATE NOT NULL,
PRIMARY KEY (`id`),
INDEX `country_id_idx` (`country_code` ASC) VISIBLE,
INDEX `city_id_idx` (`city_id` ASC) VISIBLE,
INDEX `kind_of_organization_fk_idx` (`kind_of_organization_id` ASC) VISIBLE,
INDEX `regulatory_body_fk_idx` (`registration_body_id` ASC) VISIBLE,
UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
CONSTRAINT `company_country_fk`
FOREIGN KEY (`country_code`)
REFERENCES `movie_production_companies`.`country` (`code`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `company_city_fk`
FOREIGN KEY (`city_id`)
REFERENCES `movie_production_companies`.`city` (`city_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `company_kind_of_organization_fk`
FOREIGN KEY (`kind_of_organization_id`)
REFERENCES `movie_production_companies`.`kind_of_organization` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `company_regulatory_body_fk`
FOREIGN KEY (`registration_body_id`)
REFERENCES `movie_production_companies`.`registration_body` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
</code></pre>
<p>And I restored the only Trigger associated with the COMPANY table to original format but nothing</p>
<pre><code>CREATE DEFINER = CURRENT_USER TRIGGER `movie_production_companies`.`company_BEFORE_INSERT_pk_maker`
BEFORE INSERT ON `company`
FOR EACH ROW
BEGIN
DECLARE space_count INT;

SET space_count = LENGTH(company_name) - LENGTH(REPLACE(company_name, ' ', ''));

IF space_count = 0 THEN
SET NEW.id = CONCAT(
NEW.country_code, '-',
UPPER(LEFT(NEW.company_name, 3)), '-',
FLOOR(100000 + RAND() * 900000)
);
ELSEIF space_count = 1 THEN
SET NEW.id = CONCAT(
NEW.country_code, '-',
UPPER(LEFT(NEW.company_name, 1)),
UPPER(LEFT(SUBSTRING_INDEX(NEW.company_name, ' ', -1), 2)),
FLOOR(100000 + RAND() * 900000)
);
ELSE
SET NEW.id = CONCAT(
NEW.country_code, '-',
UPPER(LEFT(NEW.company_name, 1)),
UPPER(LEFT(SUBSTRING_INDEX(SUBSTRING_INDEX(NEW.company_name, ' ', 2), ' ', -1), 1)),
UPPER(LEFT(SUBSTRING_INDEX(SUBSTRING_INDEX(NEW.company_name, ' ', 3), ' ', -1), 1)),
FLOOR(100000 + RAND() * 900000)
);
END IF;
END;
</code></pre>
I have found how to fix this.
 

Latest posts

Top