MySQL source command and transactions

I'm using MySQL client:

# mysql --version
mysql  Ver 8.0.32 for Linux on x86_64 (MySQL Community Server - GPL)

Basically I don't understand the difference between running a script SQL in these ways:

Work properly with transactions, I mean rollback everything if there is an error:

scenario 1:

# mysql --show-warnings --verbose -u root -p < script.sql

Execute everything even if we have an error in the middle:

scenario 2:

mysql> source script.sql

My script is basically like this:

Start transaction

update 1 (ok)

update 2 (error)

commit

I'm expecting to get a rollback if I have an error in the middle of the transaction (update 2) but that doesn't happen if I use source script.sql

script.sql

START TRANSACTION;
       
    UPDATE onboarding.components_options
        SET value = 'Business Details​'
        WHERE component_id = (select id from onboarding.components where reference = 'BUSINESS_ENTITY')
            and type = 'title';
           
    UPDATE onboarding.components_options
        SET value2 = 'Is your business incorporated?​'
        WHERE component_id = (select id from onboarding.components where reference = 'BUSINESS_ENTITY')
            and type = 'subtitle';
           
COMMIT;

scenario 1 (work fine)

# mysql --show-warnings --verbose -u root -p < script.sql
Enter password: 
--------------
START TRANSACTION
--------------
--------------
UPDATE onboarding.components_options
        SET value = 'Business Details​'
        WHERE component_id = (select id from onboarding.components where reference = 'BUSINESS_ENTITY')
            and type = 'title'
--------------
--------------
UPDATE onboarding.components_options
        SET value2 = 'Is your business incorporated?​'
        WHERE component_id = (select id from onboarding.components where reference = 'BUSINESS_ENTITY')
            and type = 'subtitle'
--------------
ERROR 1054 (42S22) at line 8: Unknown column 'value2' in 'field list'
bash-4.4# mysql -u root -p

scenario 2 (unexpected result, at least for me)

Pay attention, the commit sentence is executed -> 4

mysql> source script.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
ERROR 1054 (42S22): Unknown column 'value2' in 'field list'
Query OK, 0 rows affected (0.02 sec)

When running mysql in batch mode (mysql --show-warnings --verbose -u root -p < script.sql), by default the script will stop processing when an error occurs. So, when your third statement is executed and produces an error, the script (and session) will end and the transaction is implicitly rolled back. You can change this behaviour by passing the --force option to mysql client on the command line.

When you execute your statements using source the script continues after an error occurs, thus running your COMMIT statement and the changes persist.

This functionality has been questioned in feature requests in the past.