Driver class 'org.gjt.mm.mysql.Driver' could not be found

Driver class 'org.gjt.mm.mysql.Driver' could not be found, make sure the 'MySQL' driver (jar file) is installed. org.gjt.mm.mysql.Driver

Issue : Creating connection with MySQL with kettle, was using 

• Data Integration Kettle version 9.0
• Mysql Driver version 8.0.25

Error

Driver class 'org.gjt.mm.mysql.Driver' could not be found, make sure the 'MySQL' driver (jar file) is installed. org.gjt.mm.mysql.Driver

Solution

Get version 5 latest driver from url https://downloads.mysql.com/archives/c-j/  (it is 5.1.49)

ALTER TABLE statement conflicted with the FOREIGN KEY constraint

Server Type - MS SQL  Server

Scanerio : Error faced when tried to apply foreign key constraint on table having data in it (non empty table).

Error

Msg 547, Level 16, State 0, Line 20 The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "". The conflict occurred in database "", table "dbo.", column '**'

Solution

Empty table first then apply foreign key

 

Dividing Number Return ZERO Postgres

 If you are also facing same issue, here is solution

EARLIER

select (15/20)
 
Output

0

NOW

use this command to get correct output in postgres

select (15/20::float)::numeric(5,2)

Output 

0.75 

 

you can Also  create function in database

CREATE OR REPLACE FUNCTION dvdrental1(part int, fullv int) RETURNS float AS $$
BEGIN
  RAISE NOTICE 'PROGRESS IN PERCENT : %', (part/fullv::float)::numeric(5,2);
  RETURN part/fullv::float;
END;
$$  LANGUAGE plpgsql VOLATILE
  COST 100;

Postgres DvdRental Sample Database Restore Error

I was facing error while restoring the database dvdrental for postgres. Postgres 9 was installed on my machine and connect as postgres user with database postgres.

First i tried to execute restore.sql script using pgadmin sql executor and throw below error:

ERROR:  syntax error at or near ''\''
LINE 35: \connect

SOLUTION
SQL need to execute on CMD, so open CMD and move to postgres installation directory

cd C:\Program Files\PostgreSQL\9.5\bin
>Enter

psql -h localhost -d postgres -U postgres -p 5432 -a -q -f C:\Users\userPostgres\Downloads\dvdrental\restore.sql

On Execution again faced error

psql:C:/Users/userPostgres/Downloads/dvdrental/restore.sql:920: ERROR:  could not open file "C:\Users\userPostgres\Downloads\dvdrental\3057.dat" for reading: Permission denied




COPY public.actor (actor_id, first_name, last_name, last_update) FROM stdin;
COPY public.actor (actor_id, first_name, last_name, last_update) FROM 'C:\Users\userPostgres\Downloads\dvdrental\3057.dat';
psql:C:/Users/userPostgres/Downloads/dvdrental/restore.sql:920: ERROR:  could not open file "C:\Users\userPostgres\Downloads\dvdrental\3057.dat" for reading: Permission denied
--
-- Data for Name: address; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.address (address_id, address, address2, district, city_id, postal_code, phone, last_update) FROM stdin;
COPY public.address (address_id, address, address2, district, city_id, postal_code, phone, last_update) FROM 'C:\Users\userPostgres\Downloads\dvdrental\3065.dat';
psql:C:/Users/userPostgres/Downloads/dvdrental/restore.sql:928: ERROR:  could not open file "C:\Users\userPostgres\Downloads\dvdrental\3065.dat" for reading: Permission denied
--
-- Data for Name: category; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.category (category_id, name, last_update) FROM stdin;
COPY public.category (category_id, name, last_update) FROM 'C:\Users\userPostgres\Downloads\dvdrental\3059.dat';
psql:C:/Users/userPostgres/Downloads/dvdrental/restore.sql:936: ERROR:  could not open file "C:\Users\userPostgres\Downloads\dvdrental\3059.dat" for reading: Permission denied
--
-- Data for Name: city; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.city (city_id, city, country_id, last_update) FROM stdin;
COPY public.city (city_id, city, country_id, last_update) FROM 'C:\Users\userPostgres\Downloads\dvdrental\3067.dat';
psql:C:/Users/userPostgres/Downloads/dvdrental/restore.sql:944: ERROR:  could not open file "C:\Users\userPostgres\Downloads\dvdrental\3067.dat" for reading: Permission denied
--
-- Data for Name: country; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.country (country_id, country, last_update) FROM stdin;
COPY public.country (country_id, country, last_update) FROM 'C:\Users\userPostgres\Downloads\dvdrental\3069.dat';
psql:C:/Users/userPostgres/Downloads/dvdrental/restore.sql:952: ERROR:  could not open file "C:\Users\userPostgres\Downloads\dvdrental\3069.dat" for reading: Permission denied
--
-- Data for Name: customer; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.customer (customer_id, store_id, first_name, last_name, email, address_id, activebool, create_date, last_update, active) FROM stdin;
COPY public.customer (customer_id, store_id, first_name, last_name, email, address_id, activebool, create_date, last_update, active) FROM 'C:\Users\userPostgres\Downloads\dvdrental\3055.dat';
psql:C:/Users/userPostgres/Downloads/dvdrental/restore.sql:960: ERROR:  could not open file "C:\Users\userPostgres\Downloads\dvdrental\3055.dat" for reading: Permission denied
--
-- Data for Name: film; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.film (film_id, title, description, release_year, language_id, rental_duration, rental_rate, length, replacement_cost, rating, last_update, special_features, fulltext) FROM stdin;
COPY public.film (film_id, title, description, release_year, language_id, rental_duration, rental_rate, length, replacement_cost, rating, last_update, special_features, fulltext) FROM 'C:\Users\userPostgres\Downloads\dvdrental\3061.dat';
psql:C:/Users/userPostgres/Downloads/dvdrental/restore.sql:968: ERROR:  could not open file "C:\Users\userPostgres\Downloads\dvdrental\3061.dat" for reading: Permission denied
--
-- Data for Name: film_actor; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.film_actor (actor_id, film_id, last_update) FROM stdin;
COPY public.film_actor (actor_id, film_id, last_update) FROM 'C:\Users\userPostgres\Downloads\dvdrental\3062.dat';
psql:C:/Users/userPostgres/Downloads/dvdrental/restore.sql:976: ERROR:  could not open file "C:\Users\userPostgres\Downloads\dvdrental\3062.dat" for reading: Permission denied
--
-- Data for Name: film_category; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.film_category (film_id, category_id, last_update) FROM stdin;
COPY public.film_category (film_id, category_id, last_update) FROM 'C:\Users\userPostgres\Downloads\dvdrental\3063.dat';
psql:C:/Users/userPostgres/Downloads/dvdrental/restore.sql:984: ERROR:  could not open file "C:\Users\userPostgres\Downloads\dvdrental\3063.dat" for reading: Permission denied
--
-- Data for Name: inventory; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.inventory (inventory_id, film_id, store_id, last_update) FROM stdin;
COPY public.inventory (inventory_id, film_id, store_id, last_update) FROM 'C:\Users\userPostgres\Downloads\dvdrental\3071.dat';
psql:C:/Users/userPostgres/Downloads/dvdrental/restore.sql:992: ERROR:  could not open file "C:\Users\userPostgres\Downloads\dvdrental\3071.dat" for reading: Permission denied
--
-- Data for Name: language; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.language (language_id, name, last_update) FROM stdin;
COPY public.language (language_id, name, last_update) FROM 'C:\Users\userPostgres\Downloads\dvdrental\3073.dat';
psql:C:/Users/userPostgres/Downloads/dvdrental/restore.sql:1000: ERROR:  could not open file "C:\Users\userPostgres\Downloads\dvdrental\3073.dat" for reading: Permission denied
--
-- Data for Name: payment; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.payment (payment_id, customer_id, staff_id, rental_id, amount, payment_date) FROM stdin;
COPY public.payment (payment_id, customer_id, staff_id, rental_id, amount, payment_date) FROM 'C:\Users\userPostgres\Downloads\dvdrental\3075.dat';
psql:C:/Users/userPostgres/Downloads/dvdrental/restore.sql:1008: ERROR:  could not open file "C:\Users\userPostgres\Downloads\dvdrental\3075.dat" for reading: Permission denied
--
-- Data for Name: rental; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.rental (rental_id, rental_date, inventory_id, customer_id, return_date, staff_id, last_update) FROM stdin;
COPY public.rental (rental_id, rental_date, inventory_id, customer_id, return_date, staff_id, last_update) FROM 'C:\Users\userPostgres\Downloads\dvdrental\3077.dat';
psql:C:/Users/userPostgres/Downloads/dvdrental/restore.sql:1016: ERROR:  could not open file "C:\Users\userPostgres\Downloads\dvdrental\3077.dat" for reading: Permission denied
--
-- Data for Name: staff; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.staff (staff_id, first_name, last_name, address_id, email, store_id, active, username, password, last_update, picture) FROM stdin;
COPY public.staff (staff_id, first_name, last_name, address_id, email, store_id, active, username, password, last_update, picture) FROM 'C:\Users\userPostgres\Downloads\dvdrental\3079.dat';
psql:C:/Users/userPostgres/Downloads/dvdrental/restore.sql:1024: ERROR:  could not open file "C:\Users\userPostgres\Downloads\dvdrental\3079.dat" for reading: Permission denied
--
-- Data for Name: store; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.store (store_id, manager_staff_id, address_id, last_update) FROM stdin;
COPY public.store (store_id, manager_staff_id, address_id, last_update) FROM 'C:\Users\userPostgres\Downloads\dvdrental\3081.dat';
psql:C:/Users/userPostgres/Downloads/dvdrental/restore.sql:1032: ERROR:  could not open file "C:\Users\userPostgres\Downloads\dvdrental\3081.dat" for reading: Permission denied



SOLUTION
1. Check Directory permission (read,write) to window user
2. Change "dvdrental" directory to some another drive, i moved folder to E drive, now path is as below, Also dont forget to change the path within the restore.sql file



psql -h localhost -d postgres -U postgres -p 5432 -a -q -f E\dvdrental\restore.sql

Find Deadlock query and Kill the Process in Postgres

If your query took longer and never ending then possibility tables are under deadlock. You can easily identify the deadlock in postgres and kill the lock.

SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid where state= 'active'

    
Find the records which are under deadlock

select
    pid, state, usename, query, query_start
from
    pg_stat_activity
where
    pid in
    (
        select
            pid
        from
            pg_locks pgl
            join pg_class pgc on pgl.relation = pgc.oid
        where
            pgc.relkind = 'r'
            and pgc.relname = 'your_table'
    );

Observe the query output and identify your queries causing deadlock.

Then Kill the ProcessID's one by one

SELECT pg_cancel_backend(0001);

if Deadlock still there, your PID still visible in pg_locks table then use below query

select pg_terminate_backend(0001)