mariadb -e "CREATE DATABASE IF NOT EXISTS test;"
mariadb test -e "CREATE TABLE IF NOT EXISTS flights ( id int(11) NOT NULL, name varchar(255) DEFAULT NULL, status varchar(20) NOT NULL DEFAULT 'missing creative', description varchar(255) DEFAULT NULL, notes varchar(255) DEFAULT NULL, time_zone varchar(255) DEFAULT NULL, start_date datetime DEFAULT NULL, end_date datetime DEFAULT NULL, created_at datetime DEFAULT NULL, updated_at datetime DEFAULT NULL, flags int(11) NOT NULL DEFAULT 0, flat_rate_flag tinyint(1) DEFAULT 0, flat_rate decimal(10,2) DEFAULT NULL, flat_rate_local decimal(10,2) DEFAULT NULL, impressions int(11) DEFAULT NULL, bonus_impressions int(11) DEFAULT NULL, cost_impressions decimal(10,2) DEFAULT NULL, cost_impressions_local decimal(10,2) DEFAULT NULL, views int(11) DEFAULT NULL, bonus_views int(11) DEFAULT NULL, cost_views decimal(10,2) DEFAULT NULL, cost_views_local decimal(10,2) DEFAULT NULL, clicks int(11) DEFAULT NULL, bonus_clicks int(11) DEFAULT NULL, cost_clicks decimal(10,2) DEFAULT NULL, cost_clicks_local decimal(10,2) DEFAULT NULL, actions int(11) DEFAULT NULL, bonus_actions int(11) DEFAULT NULL, cost_actions decimal(10,2) DEFAULT NULL, cost_actions_local decimal(10,2) DEFAULT NULL, flight_type varchar(20) DEFAULT NULL, user_info varchar(255) DEFAULT NULL, reference_rate int(11) DEFAULT NULL, deleted_at datetime DEFAULT NULL, campaign_id int(11) DEFAULT NULL, exposure_policy_id int(11) DEFAULT NULL, targets_group_logic varchar(255) DEFAULT NULL, tier_id int(11) DEFAULT NULL, weight int(11) DEFAULT 1, product_id int(11) DEFAULT NULL, lock_version int(11) NOT NULL DEFAULT 0, creative_selection_method varchar(255) NOT NULL DEFAULT 'Weighted', companion_type varchar(255) DEFAULT NULL, daily_rev_cap decimal(10,2) DEFAULT NULL, daily_start_time time DEFAULT NULL, daily_end_time time DEFAULT NULL, line_item_id int(11) DEFAULT NULL, size_id int(11) DEFAULT NULL, creatives_added_at date DEFAULT NULL, on_schedule_priority decimal(10,2) DEFAULT NULL, delivery_patience decimal(3,2) DEFAULT NULL, product_line varchar(255) DEFAULT NULL, external_id varchar(255) DEFAULT NULL, ad_hoc_target_id int(11) DEFAULT NULL, inventory_available bigint(20) DEFAULT NULL, inventory_reserved bigint(20) DEFAULT NULL, inventory_served bigint(20) DEFAULT NULL, team_id int(11) DEFAULT NULL, currency_id int(11) DEFAULT NULL, conversion_rate float DEFAULT NULL, daily_rev_cap_local decimal(10,2) DEFAULT NULL, target_signature varchar(64) DEFAULT NULL, opt_method varchar(25) DEFAULT NULL, opt_sample_size int(11) DEFAULT NULL, opt_done tinyint(1) DEFAULT 0, sticky_duration int(11) DEFAULT NULL, alert_id int(11) DEFAULT NULL, published tinyint(1) DEFAULT 0, published_at datetime DEFAULT NULL, publish_signature bigint(20) DEFAULT 0, adjustment_type varchar(16) DEFAULT NULL, adjustment_amount decimal(10,2) DEFAULT NULL, adjustment_amount_local decimal(10,2) DEFAULT NULL, adjustment_percent decimal(5,2) DEFAULT NULL, adjustment_description varchar(255) DEFAULT NULL, pmp_deal_id varchar(255) DEFAULT NULL, pmp_vendor varchar(20) DEFAULT NULL, network_id int(11) DEFAULT NULL) ENGINE=Columnstore DEFAULT CHARSET=utf8mb3;"
cpimport test flights /tmp/flights.txt -m1 -s '\t'
echo " After cpimport - notice no truncation into the varchar(255) field 'notes'"
mariadb test -e "select id,length(notes) from flights where length(notes) >254;"
mariadb test -e "CREATE TABLE IF NOT EXISTS flights2( id int(11) NOT NULL, name varchar(255) DEFAULT NULL, status varchar(20) NOT NULL DEFAULT 'missing creative', description varchar(255) DEFAULT NULL, notes varchar(255) DEFAULT NULL, time_zone varchar(255) DEFAULT NULL, start_date datetime DEFAULT NULL, end_date datetime DEFAULT NULL, created_at datetime DEFAULT NULL, updated_at datetime DEFAULT NULL, flags int(11) NOT NULL DEFAULT 0, flat_rate_flag tinyint(1) DEFAULT 0, flat_rate decimal(10,2) DEFAULT NULL, flat_rate_local decimal(10,2) DEFAULT NULL, impressions int(11) DEFAULT NULL, bonus_impressions int(11) DEFAULT NULL, cost_impressions decimal(10,2) DEFAULT NULL, cost_impressions_local decimal(10,2) DEFAULT NULL, views int(11) DEFAULT NULL, bonus_views int(11) DEFAULT NULL, cost_views decimal(10,2) DEFAULT NULL, cost_views_local decimal(10,2) DEFAULT NULL, clicks int(11) DEFAULT NULL, bonus_clicks int(11) DEFAULT NULL, cost_clicks decimal(10,2) DEFAULT NULL, cost_clicks_local decimal(10,2) DEFAULT NULL, actions int(11) DEFAULT NULL, bonus_actions int(11) DEFAULT NULL, cost_actions decimal(10,2) DEFAULT NULL, cost_actions_local decimal(10,2) DEFAULT NULL, flight_type varchar(20) DEFAULT NULL, user_info varchar(255) DEFAULT NULL, reference_rate int(11) DEFAULT NULL, deleted_at datetime DEFAULT NULL, campaign_id int(11) DEFAULT NULL, exposure_policy_id int(11) DEFAULT NULL, targets_group_logic varchar(255) DEFAULT NULL, tier_id int(11) DEFAULT NULL, weight int(11) DEFAULT 1, product_id int(11) DEFAULT NULL, lock_version int(11) NOT NULL DEFAULT 0, creative_selection_method varchar(255) NOT NULL DEFAULT 'Weighted', companion_type varchar(255) DEFAULT NULL, daily_rev_cap decimal(10,2) DEFAULT NULL, daily_start_time time DEFAULT NULL, daily_end_time time DEFAULT NULL, line_item_id int(11) DEFAULT NULL, size_id int(11) DEFAULT NULL, creatives_added_at date DEFAULT NULL, on_schedule_priority decimal(10,2) DEFAULT NULL, delivery_patience decimal(3,2) DEFAULT NULL, product_line varchar(255) DEFAULT NULL, external_id varchar(255) DEFAULT NULL, ad_hoc_target_id int(11) DEFAULT NULL, inventory_available bigint(20) DEFAULT NULL, inventory_reserved bigint(20) DEFAULT NULL, inventory_served bigint(20) DEFAULT NULL, team_id int(11) DEFAULT NULL, currency_id int(11) DEFAULT NULL, conversion_rate float DEFAULT NULL, daily_rev_cap_local decimal(10,2) DEFAULT NULL, target_signature varchar(64) DEFAULT NULL, opt_method varchar(25) DEFAULT NULL, opt_sample_size int(11) DEFAULT NULL, opt_done tinyint(1) DEFAULT 0, sticky_duration int(11) DEFAULT NULL, alert_id int(11) DEFAULT NULL, published tinyint(1) DEFAULT 0, published_at datetime DEFAULT NULL, publish_signature bigint(20) DEFAULT 0, adjustment_type varchar(16) DEFAULT NULL, adjustment_amount decimal(10,2) DEFAULT NULL, adjustment_amount_local decimal(10,2) DEFAULT NULL, adjustment_percent decimal(5,2) DEFAULT NULL, adjustment_description varchar(255) DEFAULT NULL, pmp_deal_id varchar(255) DEFAULT NULL, pmp_vendor varchar(20) DEFAULT NULL, network_id int(11) DEFAULT NULL) ENGINE=Columnstore DEFAULT CHARSET=utf8mb3;"
echo " Trying LDIF without IGNORE - but wont work"
mariadb test -e "LOAD DATA INFILE '/tmp/flights.txt' INTO TABLE flights2 FIELDS TERMINATED BY '\t';"
# Notice the error
# ERROR 1406 (22001): Data too long for column 'notes' at row 1
echo " Trying LDIF with IGNORE - will work"
mariadb test -e "LOAD DATA INFILE '/tmp/flights.txt' IGNORE INTO TABLE flights2  FIELDS TERMINATED BY '\t';"
mariadb test -e "select id,length(notes) from flights2 where length(notes) >254;"
# Notice LDIF correctly truncates



