Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel Strict Mode mysql connection

My answer related to laravel

I have table in mysql. I came across that some columns I didn’t pass while inserting. how mysql accept inserting those records without throw exception?

enter image description here

those are the columns which added zero value to them when I have inserted rows without passing value to them.

This is the table schema

create table if not exists ads_sliders
(
    id bigint unsigned auto_increment
        primary key,
    name varchar(191) not null,
    image varchar(191) not null,
    appearance_time int default 0 null,
    active tinyint(1) default 1 not null,
    `default` tinyint(1) default 0 null,
    first_period_sort int default 99 null,
    second_period_sort int default 99 null,
    third_period_sort int default 99 null,
    internal_link varchar(191) null,
    external_link varchar(191) null,
    deleted tinyint(1) default 0 not null,
    country_id char(36) not null,
    created_at timestamp null,
    updated_at timestamp null,
    start_date date not null,
    end_date date not null,
    ad_appears smallint default 1 null,
    display_period int unsigned not null,
    repetition int unsigned not null,
    display_place tinyint not null,
    on_click_action tinyint not null,
    data json null,
    constraint ads_sliders_country_id_foreign
        foreign key (country_id) references countries (id)
            on delete cascade
)
collate=utf8mb4_unicode_ci;

This is the inserting statement

    insert into
  `ads_sliders` (
    `name`,
    `image`,
    `country_id`,
    `end_date`,
    `updated_at`,
    `created_at`
  )
values
  (
    'sadf',
    'assets/images/ads_sliders/cLZVRNXf0LAP4j3IsdLImZejouAEri0grvEJxB0S.png',
    '55af3f40-23e3-11e9-acdc-efd87e16b684',
    '2024-04-30',
    '2024-04-30 09:16:07',
    '2024-04-30 09:16:07'
  )

I have double checked that those columns not null and does not have defalut value. and I made sure that global sql_mode variable value is defalut which is ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION. The mysql version is 8.0.

I test it in a new database schema. and it’s working correctly.
enter image description here

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I'm sorry it's Laravel issue. I tested it while the request executed. enter image description here

    while request executed somewhere the sql_mode var changed in the session stage.

    I you went to database.php config file inside the mysql connection you will see that there is strict mode.

    If you turn it off. Whenever laravel make any connection to database it will change the sql_mode to be NO_ENGINE_SUBSTITUTION. in this case any missed value from inert statment will ignored.


  2. 0

    In MySQL, if you attempt to insert a row into a table without specifying a value for every column, the database will handle it in one of the following ways:

    1. Default Values: If a column has a default value defined, MySQL will use that value for the column if no value is provided during the insert operation.
    2. Nullable Columns: If a column allows NULL values and no value is provided during the insert operation, MySQL will insert NULL into that column.
    3. Implicit Conversion: MySQL may also perform implicit data type conversion if the value being inserted can be converted to the appropriate data type for the column.
    4. Strict SQL Mode: If the SQL mode is set to strict, MySQL will throw an error if you attempt to insert a row with missing values for columns that don’t allow NULL and don’t have a default value.

    So, if you’re seeing records being inserted without explicitly providing values for all columns, it’s likely due to one of these reasons. You can check the column definitions in your table schema to see if default values are defined or if columns allow NULL values. Additionally, you can check the SQL mode to see if it’s set to strict or not.

    Not allowing nullable value and not declaring a default value is not a very common practice. Defining these depends on the specific requirements of your application and the nature of the data you’re dealing with. But I do recommend declaring a default value when you do not allow null and also do not pass a value when inserting.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search