I am working on a spring-boot application. Where I have to run a set of SQL queries ("written for the MySQL DB") to create the tables when the application starts. These queries are working fine while connecting the spring-boot application with MySQL DB.
But I wanted to create these tables in the h2-in memory Db (for integration testing), So I configure the application to connect with h2 DB, But while running the SQL queries I am getting the error
Column "B" not found
in each query.
Please help me, why I am getting column "B" not found in each query.
Here are my error logs
. ____ _ __ _ _
/\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
\\/ ___)| |_)| | | | | || (_| | ) ) ) )
' |____| .__|_| |_|_| |_\__, | / / / /
=========|_|==============|___/=/_/_/_/
:: Spring Boot :: (v1.3.5.RELEASE)
2022-06-28 06:19:45.179 INFO 18824 --- [ main] com.tools.seed.SeedMain : Starting SeedMain v1.0.0.ez on -Latitude-3410 with PID 18824 (/home/Documents/SourceCode/server/code/seed-data/target/seed-data-1.0.0.ez.jar started by abcd in /home/Documents/SourceCode/server/code/seed-data)
2022-06-28 06:19:45.184 DEBUG 18824 --- [ main] com.tools.seed.SeedMain : Running with Spring Boot v1.3.5.RELEASE, Spring v4.2.6.RELEASE
2022-06-28 06:19:45.184 INFO 18824 --- [ main] com.tools.seed.SeedMain : The following profiles are active:
-----> INITIALIZING CONFIGURATION: Tue Jun 28 06:19:53 IST 2022 <-----
-----> Active Profiles: : <-----
2022-06-28 06:19:55.673 DEBUG 18824 --- [ main] c.e.tools.seed.loader.LoaderRegistry : registered [2147483647] com.tools.seed.loader.DeviceFirmwareVersionLoader
2022-06-28 06:19:55.713 INFO 18824 --- [ main] c.e.p.hibernate.config.HibernateConfig : Using Hz as Hibernate Cache
2022-06-28 06:21:09.483 INFO 18824 --- [ main] c.e.p.hibernate.config.HibernateConfig : releaseConnectionAfterTransaction : false
2022-06-28 06:21:12.964 ERROR 18824 --- [ main] org.hibernate.tool.hbm2ddl.SchemaUpdate : HHH000388: Unsuccessful: create table app_version (id bigint not null auto_increment, created_by varchar(20) not null, created_time datetime not null, lock_id int(11) default 0 not null, modified_by varchar(20) not null, modified_time datetime not null, application_id varchar(50) not null, descriptor longtext, device_type varchar(16), file_size bigint, notes varchar(1024), org_code varchar(25) not null, pinned bit(1) default b'1', rollout_id varchar(25), severity int(2) default 1, status varchar(14) not null, version_code bigint not null, version_name varchar(14) not null, primary key (id)) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE latin1_general_cs
2022-06-28 06:21:12.966 ERROR 18824 --- [ main] org.hibernate.tool.hbm2ddl.SchemaUpdate : Column "B" not found; SQL statement:
create table app_version (id bigint not null auto_increment, created_by varchar(20) not null, created_time datetime not null, lock_id int(11) default 0 not null, modified_by varchar(20) not null, modified_time datetime not null, application_id varchar(50) not null, descriptor longtext, device_type varchar(16), file_size bigint, notes varchar(1024), org_code varchar(25) not null, pinned bit(1) default b'1', rollout_id varchar(25), severity int(2) default 1, status varchar(14) not null, version_code bigint not null, version_name varchar(14) not null, primary key (id)) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE latin1_general_cs [42122-212]
2022-06-28 06:21:12.978 ERROR 18824 --- [ main] org.hibernate.tool.hbm2ddl.SchemaUpdate : HHH000388: Unsuccessful: create table bharatqr_provider_config (id bigint not null auto_increment, created_by varchar(20) not null, created_time datetime not null, lock_id int(11) default 0 not null, modified_by varchar(20) not null, modified_time datetime not null, auto_check_status_enabled bit(1) default b'0' not null, auto_check_status_initiation_time integer, auto_check_status_interval_time integer, auto_check_status_type varchar(30), qr_type varchar(15) not null, terminal_type varchar(10) not null, expired_transaction_status varchar(20), generate_batch_number bit(1) default b'0' not null, partial_refund_enabled bit(1) default b'0' not null, processing_hop varchar(15), provider_base_url varchar(255) not null, provider_key varchar(255), provider_name varchar(255) not null, provider_password varchar(255), provider_username varchar(255), status varchar(20), timezone varchar(255), upi_check_status_enabled bit(1) default b'0' not null, primary key (id)) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE latin1_general_cs
2022-06-28 06:21:12.978 ERROR 18824 --- [ main] org.hibernate.tool.hbm2ddl.SchemaUpdate : Column "B" not found; SQL statement:
create table bharatqr_provider_config (id bigint not null auto_increment, created_by varchar(20) not null, created_time datetime not null, lock_id int(11) default 0 not null, modified_by varchar(20) not null, modified_time datetime not null, auto_check_status_enabled bit(1) default b'0' not null, auto_check_status_initiation_time integer, auto_check_status_interval_time integer, auto_check_status_type varchar(30), qr_type varchar(15) not null, terminal_type varchar(10) not null, expired_transaction_status varchar(20), generate_batch_number bit(1) default b'0' not null, partial_refund_enabled bit(1) default b'0' not null, processing_hop varchar(15), provider_base_url varchar(255) not null, provider_key varchar(255), provider_name varchar(255) not null, provider_password varchar(255), provider_username varchar(255), status varchar(20), timezone varchar(255), upi_check_status_enabled bit(1) default b'0' not null, primary key (id)) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE latin1_general_cs [42122-212]
2022-06-28 06:21:12.980 ERROR 18824 --- [ main] org.hibernate.tool.hbm2ddl.SchemaUpdate : HHH000388: Unsuccessful: create table bin_info (bin varchar(6) not null, created_by varchar(20) not null, created_time datetime not null, lock_id int(11) default 0 not null, modified_by varchar(20) not null, modified_time datetime not null, bank varchar(50), bank_code varchar(25), cash_enabled bit(1) default b'1', classification varchar(10), country2_iso varchar(3), country3_iso varchar(4), country_iso varchar(2), emi_enabled bit(1) default b'1', info varchar(100), iso_country varchar(16), loyalty_enabled bit(1) default b'0', mdr_card_category varchar(100), origin varchar(16), payment_card_brand varchar(25), payment_card_type varchar(10), phone varchar(16), verified_bin bit(1) default b'0', www varchar(255), primary key (bin)) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE latin1_general_cs
2022-06-28 06:21:12.980 ERROR 18824 --- [ main] org.hibernate.tool.hbm2ddl.SchemaUpdate : Column "B" not found; SQL statement:
create table bin_info (bin varchar(6) not null, created_by varchar(20) not null, created_time datetime not null, lock_id int(11) default 0 not null, modified_by varchar(20) not null, modified_time datetime not null, bank varchar(50), bank_code varchar(25), cash_enabled bit(1) default b'1', classification varchar(10), country2_iso varchar(3), country3_iso varchar(4), country_iso varchar(2), emi_enabled bit(1) default b'1', info varchar(100), iso_country varchar(16), loyalty_enabled bit(1) default b'0', mdr_card_category varchar(100), origin varchar(16), payment_card_brand varchar(25), payment_card_type varchar(10), phone varchar(16), verified_bin bit(1) default b'0', www varchar(255), primary key (bin)) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE latin1_general_cs [42122-212]
2022-06-28 06:21:12.996 ERROR 18824 --- [ main] org.hibernate.tool.hbm2ddl.SchemaUpdate : HHH000388: Unsuccessful: create table communication_mapping (id bigint not null auto_increment, created_by varchar(20) not null, created_time datetime not null, lock_id int(11) default 0 not null, modified_by varchar(20) not null, modified_time datetime not null, block_repetition bit(1) default b'1', communication_template_id bigint not null, communication_trigger varchar(5) not null, communication_type varchar(12) not null, config_level varchar(4) not null, csv_input json not null, csv_report json, end_date datetime not null, end_date_modified datetime, input_touch_points integer not null, mapping_name varchar(50) not null, recipient_level varchar(5), start_date datetime not null, status varchar(25), target_users_count bigint, total_target_count bigint, no_of_touch_points bigint, no_of_touch_points_modified bigint, primary key (id)) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE latin1_general_cs
2022-06-28 06:21:12.996 ERROR 18824 --- [ main] org.hibernate.tool.hbm2ddl.SchemaUpdate : Column "B" not found; SQL statement:
create table communication_mapping (id bigint not null auto_increment, created_by varchar(20) not null, created_time datetime not null, lock_id int(11) default 0 not null, modified_by varchar(20) not null, modified_time datetime not null, block_repetition bit(1) default b'1', communication_template_id bigint not null, communication_trigger varchar(5) not null, communication_type varchar(12) not null, config_level varchar(4) not null, csv_input json not null, csv_report json, end_date datetime not null, end_date_modified datetime, input_touch_points integer not null, mapping_name varchar(50) not null, recipient_level varchar(5), start_date datetime not null, status varchar(25), target_users_count bigint, total_target_count bigint, no_of_touch_points bigint, no_of_touch_points_modified bigint, primary key (id)) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE latin1_general_cs [42122-212]
My db configuration in the application.properties file
database.driver=org.h2.Driver
database.url=jdbc:h2:mem:testdb12;MODE=MySQL;IGNORECASE=TRUE
database.user=root
database.password=root123
spring.h2.console.enabled=true
database.encEnabled=false
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
Note: all the SQL queries are verified so I can't make any changes to the queries.
Bit literals b'something' are MySQL-specific feature.
In your particular case you need to replace b'1' with TRUE and b'0' with FALSE, both MySQL and H2 accept these standard literals.
It isn't related to you question, but you may want to add ;DATABASE_TO_LOWER=TRUE to your JDBC URL as suggested in documentation of MySQL compatibility mode:
https://h2database.com/html/features.html#compatibility