I was going to truncate a table in sqlserver database but it seems to be went in deadlock. Apart from this if issue persist regularly then use delete command instead of truncate command.
List of Deadlock
SELECT
SESSION_ID
FROM SYS.DM_EXEC_REQUESTS
WHERE BLOCKING_SESSION_ID != 0
Kill the deadlock:
kill SESSION_ID
Generate a range of consecutive/Sequence numbers (1 to 100) using a MySQL query, see below. Replace 100 number as per your choice, so it will generate sequence of numbers 1 to number you will going to replace
select t0.rn+t1.rn+t2.rn+t3.rn as Sequence
from
(select 0 rn union select 1 rn union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 rn union select 10 rn union select 20 union select 30 union select 40 union select 50 union select 60 union select 70 union select 80 union select 90) t1,
(select 0 rn union select 100 rn union select 200 union select 300 union select 400 union select 500 union select 600 union select 700 union select 800 union select 900) t2,
(select 0 rn union select 1000 rn union select 2000 union select 3000 union select 4000 union select 5000 union select 6000 union select 7000 union select 8000 union select 9000) t3
where
t3.rn<=100
and t2.rn<=100
and t1.rn<=100
and t0.rn+t1.rn+t2.rn+t3.rn<=100
and t0.rn+t1.rn+t2.rn+t3.rn<>0
order by 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.
Solution
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
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)
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
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;
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
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'
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'
);
SELECT pg_cancel_backend(0001);
select pg_terminate_backend(0001)
