MySQL import csv file ERROR 13 (HY000): Can't get stat of /path/file.csv (Errcode: 2) I am an absolute beginner to MySQL (5.5.34) / Linux (Ubuntu 12.04 LTS)`

I have created a simple database with one table. When trying to import data into it via a 'data_test.csv file an error message 13 appears.

Exert from Terminal follows:

mysql> source /home/g/stockrecdb/load_test.sql;
Database changed
ERROR 13 (HY000): Can't get stat of '/home/stockrecdb/data_test.csv' (Errcode: 2)
mysql>

Note: if using LOCAL in LOAD DATA LOCAL INFILE an error appears:
ERROR 1148 (42000): The used command is not allowed with this MySQL version

I hope you are using LOAD DATA INFILE.

Try to use LOAD DATA LOCAL INFILE instead of LOAD DATA INFILE.

Other issue might be this, please visit the following links : MySQL LOAD DATA.

When you login in MySQL do like below,

abdul@xmpp3:~/Desktop/Jiva$ mysql -uroot -p --local-infile
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 257
Server version: 5.5.29-0ubuntu0.12.04.1-log (Ubuntu)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mydb;

mysql> LOAD DATA LOCAL INFILE '/home/abdul/Desktop/STATISTIC_T.csv' INTO TABLE STATISTIC_T  FIELDS TERMINATED BY '|' ENCLOSED BY '"' LINES TERMINATED BY '\n'  IGNORE 1 LINES;

Now load your CSV using LOAD DATA LOCAL INFILE ,we need to use --local-infile before we load CSV in in new MySQL vesrsions,  due to security reasons.


Forcing MS SQL Server to rollback on error I am having some problem to understand how transactions work on SQL Server, even after I thought I had all answers from the documentation.

To put it simply, I have a transaction at the beginning of a loong script (which gravely alters the schema), and if any statement fails it should result in a rollback.
On PostgreSQL this works without no problem.

An example to illustrate, on PostgreSQL:

BEGIN TRANSACTION;
DROP TABLE t1;  -- This results in a rollback, because t1 doesn't exist
CREATE TABLE t1 (c1 int); -- This and following statements are never run
COMMIT TRANSACTION;

Example on SQL Server:

SET IMPLICIT_TRANSACTIONS OFF --
SET XACT_ABORT ON -- Supposedly stop everything on error
GO
BEGIN TRANSACTION
GO
DROP TABLE t1 -- This results in an error
GO
CREATE TABLE t1 (c1 int) -- This is execute anyway?
GO
COMMIT TRANSACTION
GO

Even though the script results in an error, it never aborts to rollback. And the table "[dbo].[t1]" exist in the schema.

Someone suggested wrapping the statements in a TRY/CATCH block, but this does not work due to some schema alterations requiring be split up in batches. (E.g. renaming/adding columns, and later inserting data).

Many answers here does state that SET XACT_ABORT ON should indeed result in the script to be aborted, but it does not.

What am I missing here?

You should check for existing metadata rather than creating transaction logs with queries such as:

If OBJECT_ID('dbo.t1') is null
begin
    CREATE TABLE t1 (c1 int);
    print 'create';
end
else print 'nothing';

or

if not exists(Select 1 From sys.tables where name = 't1' and SCHEMA_NAME(schema_id) = 'dbo')
begin
    CREATE TABLE t1 (c1 int);
    print 'create';
end
else print 'nothing';

or

If OBJECT_ID('dbo.t1') is not null
begin
    DROP TABLE t1
    print 'drop';
end
else print 'nothing';
CREATE TABLE t1 (c1 int);

It is still recommended to use Transaction and Try/Catch.

Unless you are creating objects such as view, you don't need GO everywhere and this will work:

begin try
    begin tran
    DROP TABLE t1
    print 'drop';
    CREATE TABLE t1 (c1 int);
    print 'create';
    if @@trancount > 0 commit
    print 'commit'
end try
begin catch
    print 'rollback'
    Print ERROR_MESSAGE()
    if @@trancount > 0 rollback
end catch
select * from t1;

You will still see this error:

  Msg 208, Level 16, State 1, Line 17
  Invalid object name 't1'.

It is considered as an error in your query because an object does not exist and it will go to the catch block because T1 does not exists. There is no error with the Transaction itself.


Database design for voting module with long-run and high-load capability I'm currently designing a project and I need professional advice from DBA.

My project will feature voting system somewhat similar to the one used in stack exchage websites. I have users and content pieces and users can vote for content pieces they like or don't. Note, that I will have vote up/down option on the feed list, so if I load 30 content pieces I will need to also load the data of user's votes on every piece, since the up/down button should be highlighed if user already has voted for a specific piece. In other words, I expect big load on the votes table. I was thinking of the basic structure like this:

Table users (user_id, ...), table content (content_id, ...), table votes (vote_id, user_id, content_id, datetime, vote). However, I have doubts about this design.

Let's say I have 10k users and 1k content pieces. That's up to 10 million records in table votes. If I start thinking about scaling it up, I can imagine a big problem. Content doesn't go anywhere, old votes as well, so the longer website runs, the more records will be in the table and the slower it will work.

Let's say in some years I will have 100k users and 20k content pieces. That's up to 2 billion records. I understand that not every user will vote on every content piece, but the problem however is clear - there is a limit to that design (by limit I mean that select query will be slow when the amount of rows will reach some point).

So my questions are:

Is there really a limit to that design and if yes, how can it be dealt with?
1.1. If the select query on votes table will be getting slower, what can I do to speed it up?
Is there a better way to design this kind of relations?
How do I cache that data? Or is that even needed with proper indexing?
What kind of indexes would you recommend for the votes table? Am I correct that I need a simple double-field index (user_id,  content_id)?
Most of the load will go on recent content pieces, maybe I should create something like recent_votes table, which will hold duplicate data, but only for the last say 24 hours and most load will go on it, and if user wants some data that is older, he will work with much bigger and slower table with all votes? Does that make any sense?

I really would like to do things right from the start so in a few years I won't end up with a slow website. Thank you for your time.

Is there really a limit to that design and if yes, how can it be dealt with? 1.1. If the select query on votes table will be getting slower, what can I do to speed it up?

I don't think the number of votes is likely to be the problem.  The questions will have to do in part with questions of how well you can index, how your db does caching, etc.  Standard performance tuning applies and that isn't really your design per se.  I will answer more below on what to consider if you run into the wall of being unable to get your design to work fast enough.

  Is there a better way to design this kind of relations?

Not really.

  How do I cache that data? Or is that even needed with proper indexing?

My preference in this case would be to start out without caching, and then to implement a caching layer when you need one.  A caching layer might include something like memcached, or you could build one on a NoSQL solution like Mongo.  At that point you can look at optimizing the areas which are the largest problems.

  What kind of indexes would you recommend for the votes table? Am I correct that I need a simple double-field index (user_id, content_id)?

I know that MySQL and PostgreSQL are different enough to make cross-db somewhat dangerous here but I am thinking you'd want two indexes, one on content_id and one on user_id.  I am thinking this because aggregating by user_id and content_id are likely to be different queries and these are different join conditions.

  Most of the load will go on recent content pieces, maybe I should create something like recent_votes table, which will hold duplicate data, but only for the last say 24 hours and most load will go on it, and if user wants some data that is older, he will work with much bigger and slower table with all votes? Does that make any sense?

Keep in mind that db's frequently do a good job of caching recent content pieces.  I would expect that MySQL can do this too.  If it can't go with PostgreSQL instead.  Don't cache it yourself in the db.

what to do if you hit the wall will depend on your DB choice.  If you are using MySQL, your traditional answer is to look at something like memcached or create a caching layer in a NoSQL db.  If you are using PostgreSQL, you get those choices plus something like Postgres-XC which gives you an ability to do teradata-style scaling out and clustering in OLTP environments.


Storing multiple IDs in column or in new table I'm fairly new to the databases and I'm now creating little project where I have a database and I want to store deck of cards into the table where each card has its own ID (for example 52 cards in a deck).

What would be the best way to go about it, performance wise?

I have two ideas:

The table "deck" will have column with varchar where I will store cards in CSV format with help of backend language
New table will be created with 52 columns for every card and store them there

Thanks for any help.

There is an art and a science to database design.  The science is normalisation.  If you're intent on learning about databases you will need a solid understanding of it.

The art is in deciding just what exactly the "things" are that are going into your database.  Defining them in a why that is comprehensive and precise is a skill to learn.

In your particular case, storing multiple card ids in a single column prima face breaks first normal form.  This is where the "art" bit comes in.  If you only ever consider these cards all together - say, as a "hand" or a "pack" - and never as individual items then it would be legitimate to model them as a single column.  If you ever want to consider each card individually ("How many times has the Queen of hearts been delt?") then you would be better of modelling a seperate table with one row per card.

A second consideration is performance.  It will always be faster to insert or delete one row than 52.  It will almost always be slower to pull a substring out of a long varchar than to pick a single row from a large, well-indexed table. These considerations conflict. On balance, I would always recommend the one-row-per-card approach until it was proven otherwise.

You are best placed to judge the usage and volumes of your application and, hence, resolve the conflicts.


Mysql Slave doesn't find binary log from slave I have to setup a MySQL slave on an other infrasctructure. It will become the Master once that other infrasctructure is ready.

Mysql 5.6.29
Slave A in old infrasctructure
Slave B in new one

I dumped the Slave A database by performing a STOP SLAVE;, FLUSH TABLES WITH READ LOCK then the mysqldump and restarted the slave with  START SLAVE.
I've now a perfect dump of my slave A with the master binlog and its position.

I dumped the dump into the Slave B and setup the correct binlog and position for the replication.
Once started, the Slave B couldn't find the binlog. But they are all in the right /var/lib/mysql folder on the Slave A. When I say they are all there I mean I put them myself in the folder in fact, otherwise they are deleted (I don't know why).

I really don't understand what I'm missing here.

log-slave-updates is ON on the Slave A

Master -> A -> B

binlog on A needs to be turned on.

B must use the binlogs from A, not from the Master.

So...

Turn on binlogging on A.
Stop replication from Master -> A.
Dump A
Load dump onto B
CHANGE MASTER to point B to A.
Start replication from Master -> A.  Things should flow all the way to B.


Designing a scenario where a primary key could be of two types I have a table: all_info, where it holds the users identification. The id of this table can be of two types (just one or another, never both), suppose: int and varchar, but one table can't have two primary keys, and a composite one wouldn't solve my problem.

So, I can't do this:

+--------------------+
| all_info           |
+--------------------+
| PK id1 varchar(50) |
| PK id2 int         |
| ...                |
+--------------------+

Then, I created two others tables: unique_info1 and unique_info2 with the primary keys of the types that I needed, add some informations for the specific types of users, and made relations with the table: all_info, that holds the rest of the users informations (that both types share).

With this scheme, I could relate unique_info1 and unique_info2 with every other table, but I would need to create two columns in every one of them to establish that relationship. To solve this, I had created an artificial primary key in all_info to make all posterior relationships.

Now, it looks like this:

Obs: FK uniq1_id varchar(50) and FK uniq2_id int(10) are unique and nullable.

+--------------------+    +--------------------+    +-------------------------+    +-------------------------+
| unique_info1       |    | unique_info2       |    | all_info                |    | other_table             |
+--------------------+    +--------------------+    +-------------------------+    +-------------------------+
| PK id varchar(50)  |    | PK id int(10)      |    | PK id int(10)           |    | ...                     |
| ...                |    | ...                |    | ...                     |    | ...                     |
| ...                |    | ...                |    | FK uniq1_id varchar(50) |    | ...                     |
| ...                |    | ...                |    | FK uniq2_id int(10)     |    | FK all_id int(10)       |
+--------------------+    +--------------------+    +-------------------------+    +-------------------------+

The thing is: that's the best approach, or should I change the plan?

For examples:

Choose other information to be the id of the users, where all will have the same type, and add all specific info of the users to that hole table?

This would result in a lot of null columns for each user.

Create two completely different tables for the two types of users?

This would result in redundant info.

Also search for super type/subtype here and on the SO.


How to properly clean Merge Replication Setup What is the proper way to completely clean Merge Replication setup ?

I have tried to delete the subscriptions, then delete the publication then "Disable Publishing & Distribution" & delete the distribution database then started setting up the replication again.

unfortunately, after that I discovered that dbo.msmergesubscriptions table still have old records about old subscriptions I used to have before 2 years !!

Is there any official documentation about how to properly & totally clean Merge Replication setup?

Somehow you are ending up with orphaned replication metadata after deleting the subscriptions and/or the publication.  This can happen when using the GUI to drop subscriptions and publication.  Instead, try using T-SQL replication stored procedures to delete the subscriptions and publication.

How to: Delete a Push Subscription (Replication Transact-SQL Programming)

How to: Delete a Pull Subscription (Replication Transact-SQL Programming)

How to: Delete a Publication (Replication Transact-SQL Programming)

After deleting the subscriptions and publication, verify the replication metadata was removed.  If not, use sp_removedbreplication to remove all replication objects from the databases.


PostgreSQL: trigger to autopopulate other fields with sub-strings of other field inserted I am looking to insert 1 to 8 sub-string values from an inserted string value into other fields as integers using a trigger in PostgreSQL. The integer values have a string prefix to identify them within the string value. A specific solution using my details below or a generalized example that helps me see how it can be done would be great. I want to do it this way (getting the integers from the 'calc_form' field string value) for control as mentioned in the "Discarding 3NF With Triggers" section of this link:
http://database-programmer.blogspot.com/2008/01/database-skills-third-normal-form-and.html

The extent of my knowledge on triggers can be seen here (I am new to it):
http://www.w3resource.com/PostgreSQL/postgresql-triggers.php

Here are the real life details, which may not be necessary to some. A solution that does everything I need below is not necessary.

The string values are identified/prefixed with bp or wc before the integer I want to insert with the trigger. Where the bp values go in the bbg_pulls_[#]_id fields and the wc go in 'wh_calc_[#]_id` fields.

Everything happens within this table:

CREATE TABLE wh_calc
(
  id serial NOT NULL,
  calc_form character varying(500) NOT NULL,
  bbg_pulls_1_id integer,
  bbg_pulls_2_id integer,
  bbg_pulls_3_id integer,
  bbg_pulls_4_id integer,
  bbg_pulls_5_id integer,
  bbg_pulls_6_id integer,
  bbg_pulls_7_id integer,
  bbg_pulls_8_id integer,
  wh_calc_1_id integer,
  wh_calc_2_id integer,
  wh_calc_3_id integer,
  wh_calc_4_id integer,
  wh_calc_5_id integer,
  wh_calc_6_id integer,
  wh_calc_7_id integer,
  wh_calc_8_id integer,
  CONSTRAINT wh_calc_pkey PRIMARY KEY (id),
  CONSTRAINT wh_calc_calc_form_key UNIQUE (calc_form)
)

For example, this insert:

insert into wh_calc
(calc_form)
values
('[bp20]/[bp47140]');

...should yield the same result as this insert:

insert into wh_calc
(calc_form,bbg_pulls_1_id,bbg_pulls_2_id)
values
('[bp20]/[bp47140]',20,47140);

Here is another, slightly more complicated example. This insert:

insert into wh_calc
(calc_form,bbg_pulls_1_id,wh_calc_1_id,wh_calc_2_id)
values
('([bp1]/[wc66])*[wc100]');

...should yield the same result as:

insert into wh_calc
(calc_form,bbg_pulls_1_id,wh_calc_1_id,wh_calc_2_id)
values
('([bp1]/[wc66])*[wc100]',1,66,100);

Note, that in both examples I want to insert without the integer values and have the integers show up like the inserts with the integers. The inserts with the integers are only there to illustrate the result I want.

The required trigger is quite fancy. This is the trigger function:

CREATE FUNCTION wh_calc_ins_upd_trg_func()
    RETURNS trigger
    LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
    t text[] ;
BEGIN
    SELECT array_agg(x[1])
    FROM regexp_matches(new.calc_form, '\[bp(\d+)\]', 'gi') AS x
    INTO t ;

    new.bbg_pulls_1_id := t[1]::integer ;
    new.bbg_pulls_2_id := t[2]::integer ;
    new.bbg_pulls_3_id := t[3]::integer ;
    new.bbg_pulls_4_id := t[4]::integer ;
    new.bbg_pulls_5_id := t[5]::integer ;
    new.bbg_pulls_6_id := t[6]::integer ;
    new.bbg_pulls_7_id := t[7]::integer ;
    new.bbg_pulls_8_id := t[8]::integer ;

    SELECT array_agg(x[1])
    FROM regexp_matches(new.calc_form, '\[wc(\d+)\]', 'gi') AS x
    INTO t ;

    new.wh_calc_1_id := t[1]::integer ;
    new.wh_calc_2_id := t[2]::integer ;
    new.wh_calc_3_id := t[3]::integer ;
    new.wh_calc_4_id := t[4]::integer ;
    new.wh_calc_5_id := t[5]::integer ;
    new.wh_calc_6_id := t[6]::integer ;
    new.wh_calc_7_id := t[7]::integer ;
    new.wh_calc_8_id := t[8]::integer ;

    RETURN new ;
END ;
$BODY$ ;

And this is the association of this function to a trigger that should be fired before insert or update of column 'calc_form'. [or...]

CREATE TRIGGER wh_calc_ins_upd_trg
    BEFORE INSERT OR UPDATE OF calc_form
    ON wh_calc
    FOR EACH ROW
    EXECUTE PROCEDURE wh_calc_ins_upd_trg_func();

This trigger plays with a few concepts:

Regular Expressions to capture the integers in [bp___] and [wc___]. The regexp_matches function returns a SET of text[].
The SET of text[] is converted into an array of arrays by means of array_agg.
We convert from text to integer, by means of tx::integer.

You can try then:

INSERT INTO
    wh_calc
    (calc_form)
VALUES
    ('[bp20]/[bp3]')
RETURNING
    * ;

And see that you get what you expect:

| id |    calc_form | bbg_pulls_1_id | bbg_pulls_2_id | bbg_pulls_3_id | bbg_pulls_4_id | bbg_pulls_5_id | bbg_pulls_6_id | bbg_pulls_7_id | bbg_pulls_8_id | wh_calc_1_id | wh_calc_2_id | wh_calc_3_id | wh_calc_4_id | wh_calc_5_id | wh_calc_6_id | wh_calc_7_id | wh_calc_8_id |
|----|--------------|----------------|----------------|----------------|----------------|----------------|----------------|----------------|----------------|--------------|--------------|--------------|--------------|--------------|--------------|--------------|--------------|
|  4 | [bp20]/[bp3] |             20 |              3 |         (null) |         (null) |         (null) |         (null) |         (null) |         (null) |       (null) |       (null) |       (null) |       (null) |       (null) |       (null) |       (null) |       (null) |

You can check it a SQLFiddle


ORA-39001: ORA-39000: ORA-31640: ORA-27041: and Linux-x86_64 Error: 13: Permission denied I used this command to export data:

expdp gsw/password DIRECTORY=gsw DUMPFILE=gsw.dmp logfile=gsw.log SCHEMAS=GSW

Now importing this to other server as:

[root@linux gsw]# impdp gsw/password remap_tablespace=SYSTEM:GSW remap_tablespace=USERS:GSW dumpfile=GSW-31-08-2018.dmp directory=gsw

Import: Release 11.2.0.2.0 - Production on Sat Sep 1 11:17:47 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/u01/app/oracle/admin/XE/dpdump/gsw/GSW-31-08-2018.dmp" for read

ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 9

I googled but not able to fix it. Please advise

Thanks in anticipation

The main issue is Linux-x86_64 Error: 13: Permission denied

cd /u01/app/oracle/admin/XE/dpdump/
 chmod 777 -R gsw

Then login as oracle user and run the same command again


SQL Server asks for 100GB for a dump with 1GB Is it acceptable that SQL Server asks for 100GB free disk space to restore a 1GB dump file?

If no, what should be parameterized?

Yes, it is conceivable. You probably need to shrink the source database files before making the backup. Run sp_helpfile on the source database to see the files and sizes; when you restore from a backup, it will create those files at exactly the same size, though you can move them to different drives/directories.

If the log file or any of the data files are unnecessarily large, you can shrink them, do a new backup, and the restore will use less space. Just beware of performance implications if you get in a continual shrink/grow/shrink process.


Temporary schema per connection? I am trying to migrate my unit tests from H2 to Postgresql.

Currently, H2 gives me an in-memory schema such that each connection maps to a unique schema, creates the tables, runs the test, and drops the schema. The schema creation and destruction is handled automatically by H2.

The unit tests run concurrently.

What is the best way to do this in Postgresql? Specifically,

How do I get a unique schema per connection?

Should the test framework generate unique names or is there a built-in mechanism for doing this?

How do I ensure that the schema is dropped when the connection is dropped?

I don't want to end up with dangling schemas when unit tests get killed.

What approach will yield the highest performance?

I need to create/drop tens of schemas per second.

UPDATE: I found a related answer here but it fails to drop schemas in case the process running the unit tests gets killed.

pg_temp is an alias for the current session's temporary schema.

If you do a SET search_path TO pg_temp before running your tests it should all just work (as long as nothing is referencing a schema explicitly).

If you don't want to change your script at all, then set the search_path on the user that the tests log in as:

> ALTER ROLE testuser SET search_path = pg_temp;

Then everything that user creates will be in pg_temp unless explicitly specified.

Here's an example from psql, showing the actual schema (for this connection) that the alias resolves to:

> SET search_path TO pg_temp;
SET
> create table test();
CREATE TABLE
> \dt test
          List of relations
  Schema   | Name | Type  |  Owner
-----------+------+-------+----------
 pg_temp_4 | test | table | postgres
(1 row)

And, as you'd expect, that schema is different for every concurrent connection, and is gone after the connection is closed.

Note that this also works for functions, though you will have to explicitly reference the pg_temp schema when calling them.


Cannot overwrite file: /var/lib/mysql/ibdata1 when using Percona xtrabackup I am receiving an error when I am trying to restore from a percona backup to my local mysql server.

  Cannot overwrite file: /var/lib/mysql/ibdata1 at /usr/bin/innobackupex
  line 2128.

sudo innobackupex --copy-back --force-non-empty-directories /home/fazlan/Desktop/2015-05-25_09-59-31/

I had also shut down the mysql service prior to executing this command using

sudo service mysql stop

How do I fix this error?

I am using Ubuntu 14.04.

You should be restoring to a completely empty folder - you need to remove all the content (files and folders) from the target folder before doing the restore. Xtrabackup contains the entire system, not just a particular database. As always, test the restore on your test environment before attempting on production! And remember to shut down MySQL before attempting the restore or cleaning the target folder...


Recommendation for synchronizing SQL Server DBs I have two databases, prod and dev. Each DB is about 500GB.  At the end of each work day, the prod database gets several million new rows and several thousands of updates.  After the data is loaded in prod, I would like to refresh dev database from production. Both servers are SQL Server 2012.

Ideally I need something lightweight, easy to setup and maintain. The synch process needs to have a minimal performance impact on the prod server. Open to 3rd party solutions.

What solution would you recommend: transactional replication, change tracking, change data capture, or something else entirely?

Thanks for your help.

PS. Would using Change Tracking + Sync Framework be an suitable alternative?

You can also use ApexSQL Data Diff. It has command line, so you can schedule the synchronization
I've used both RG and ApexSQL. ApexSQL has only 1 version (not basic and Pro, like RG), so the fetures that are available only in RG SQL Data Compare Pro version available in ApexSQL 'basic' version. And ApexSQL is cheaper


Identify data in PostgreSQL database in violation of foreign keys Database PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit - this is on an Amazon AWS instance.

I recently identified some orphan data in one of the databases I look after when it caused a cron job to fail - this orphan data however was in violation of a foreign key constraint. After digging through the logs I found that these little queries had been ran (sadly I don't know by who - I presume their reason was to get around some triggers that discourage mass-deleting):

SET session_replication_role = replica;
delete from bmis.companies where company_id = 38;
SET session_replication_role = DEFAULT;

The data stored in bmis.companies is the absolute top-level data in this system - everything stems from it. I've manually been through the tables and ran deletes that basically boil down to this pseudo code:

delete from
    schema.table
where
    not exists(
        select
            *
        from
            schema.parent_table
        where
            parent_table.id = table.parent_id
    );

However I'm only human and it's entirely possible that I may have missed something - there are many tables in this system. I'm wondering if there's any reasonably easy-to-implement way of identifying data already inside the database that is in violation of the foreign keys, or even just identifying tables that contain this data?

So, after much Googling I came up with the below to locate foreign key violations in my dataset - I had indeed missed a single table.

If you're wondering why I don't use information_schema.table_constraints, information_schema.key_column_usage, and information_schema.constraint_column_usage it's because using those tables completley falls apart for multi-column foreign keys.

Hope this helps someone someday:

do language plpgsql $$
declare
    _schemas varchar[];
    _fkey record;
    _sql text;
    _and text;
    _i integer;
    _test record;
begin
    _schemas := array['my_schema_one', 'my_schema_two']::varchar[];

    create temporary table __foreign_key_violations (
        table_name varchar,
        column_names varchar[],
        foreign_table_name varchar,
        foreign_column_names varchar[]
    ) on commit drop;

    for _fkey in(
        with
            __tables as(
                select
                    quote_ident(tables.table_schema) || '.' || quote_ident(tables.table_name) as table_name
                from
                    information_schema.tables
                where
                    tables.table_type = 'BASE TABLE'
                    and table_schema = any(_schemas)
                order by
                    tables.table_schema asc,
                    tables.table_name asc
            ),
            __raw_fkeys as(
                select
                    __tables.table_name,
                    pg_catalog.pg_get_constraintdef(pg_constraint.oid, true) as fkeydef
                from
                    __tables
                    inner join pg_catalog.pg_constraint on(
                        pg_constraint.conrelid = __tables.table_name::regclass
                        and pg_constraint.contype = 'f'
                    )
            )
        select
            __raw_fkeys.table_name,
            string_to_array(regexp_replace(__raw_fkeys.fkeydef, E'^FOREIGN KEY \\((.*?)\\).*$', E'\\1'), ', ') as column_names,
            regexp_replace(__raw_fkeys.fkeydef, E'^FOREIGN KEY .*? REFERENCES (.*?)\\(.*$', E'\\1') as foreign_table_name,
            string_to_array(regexp_replace(__raw_fkeys.fkeydef, E'^FOREIGN KEY .*? REFERENCES .*?\\((.*?)\\).*$', E'\\1'), ', ') as foreign_column_names
        from
            __raw_fkeys
    ) loop
        _sql := '
        select
            true as violations
        from
            ' || _fkey.table_name || ' as local_table
        where
            not exists(
                select
                    *
                from
                    ' || _fkey.foreign_table_name || ' as foreign_table
                where';

        for _i in 1 .. array_upper(_fkey.column_names, 1) loop
            _and := '';
            if _i > 1 then
                _and := 'and ';
            end if;

            _sql := _sql || '
                    ' || _and || 'local_table.' || quote_ident(_fkey.column_names[_i]) || ' is not null
                    and foreign_table.' || quote_ident(_fkey.foreign_column_names[_i]) || ' = local_table.' || quote_ident(_fkey.column_names[_i]);
        end loop;

        _sql := _sql || '
            )';

        for _i in 1 .. array_upper(_fkey.column_names, 1) loop
            _sql := _sql || '
                and local_table.' || quote_ident(_fkey.column_names[_i]) || ' is not null';
        end loop;

        _sql := _sql || '
        limit 1
        ';

        execute _sql into _test;

        if _test.violations = true then
            insert into __foreign_key_violations (
                table_name,
                column_names,
                foreign_table_name,
                foreign_column_names
            ) values (
                _fkey.table_name,
                _fkey.column_names,
                _fkey.foreign_table_name,
                _fkey.foreign_column_names
            );
        end if;
    end loop; -- _fkey in..
end;
$$;

select * from __foreign_key_violations;


InfluxDB: selecting last() values from different tags I have a bucket with the raw data of several sensors, things like temperature, air pressure and etc. The sensors don't send their data at the same time, which means that a given timestamp might have several data points from various sensors or just from just one.
Each reading is tagged with the ID of the sensor it comes from. I need to get the Last() reading from a given number of IDs. When I try this code:
select last(Temperature) from raw_measure where ID =~ /4372502|4399699|4406512|4407840/
instead of returning the last Temperature observation of each ID, it returns the most recent temperature timestamp of the whole group.
How can I get the last reading of each ID in a single query?

I found the solution. All you need to do is add an ORDER BY with the TAG field. So the end query becomes:
SELECT last(Temperature)
FROM raw_measure
where ID =~ /4372502|4399699|4406512|4407840/
ORDER BY ID


IndexOptimize is successful, but sys.dm_db_index_physical_stats still shows huge fragmentation I'm working on a project that requires a database shrink. Following the shrink, I checked sys.dm_db_index_physical_stats and can see a lot of my indexes are over 99% fragmented (avg_fragmentation_in_percent).

In response to this, I executed IndexOptimize by Ola Hallengren, with the following code:

EXECUTE [dbo].[IndexOptimize]
    @Databases = 'Dynamic.Migrate',
    @FragmentationLow = 'INDEX_REORGANIZE',
    @FragmentationMedium = 'INDEX_REBUILD_OFFLINE',
    @FragmentationHigh = 'INDEX_REBUILD_OFFLINE',
    @FillFactor = '80',
    @UpdateStatistics = 'ALL',
    @StatisticsSample = '100',
    @LogToTable = 'Y'

This completed successfully, but when I check sys.dm_db_index_physical_stats again, the avg_fragmentation_in_percent values have not changed, and state my indexes are still fragmented by over 99%.

Am I missing something here?

This is from Ola Hallengren's SQL Server Index and Statistics Maintenance documentation:

  MinNumberOfPages

  Set a size, in pages; indexes with fewer number of pages are skipped
  for index maintenance. The default is 1000 pages. This is based on
  Microsoft’s recommendation.

  IndexOptimize checks page_count in sys.dm_db_index_physical_stats to
  determine the size of the index.

Therefore, the indexes you mentioned are still fragmented after executing IndexOptimize were most likely skipped for having fewer pages than the minimum of 1000 pages since you didn't change that value in your code.


Oracle SQL script - passing a parameter I have an Oracle SQL script like this:

delete FROM activ_princ where id in
    (select ap.id FROM activ_princ ap
        inner join sect e on e.id=ap.FK_ID_SECTIUNE_e
        inner join chest ch on ch.id=e.FK_ID_CHESTIONAR
    WHERE ch.id=1661);
delete FROM activ_sec where id in
    (select asec.id FROM activ_sec asec
        inner join sect e on e.id=asec.FK_ID_SECTIUNE_e
        inner join chest ch on ch.id=e.FK_ID_CHESTIONAR
    WHERE ch.id=1661);
delete FROM sect where id in
    (select e.id FROM sect e
        inner join chest ch on ch.id=e.FK_ID_CHESTIONAR
    WHERE ch.id=1661);

how can I pass 1661 as a parameter and execute all the script changing just the id I want to delete...

If you're using SQLPlus, you can use a variable & it will prompt you.

delete FROM activ_princ where id in
    (select ap.id FROM activ_princ ap
        inner join sect e on e.id=ap.FK_ID_SECTIUNE_e
        inner join chest ch on ch.id=e.FK_ID_CHESTIONAR
    WHERE ch.id=&1);
delete FROM activ_sec where id in
    (select asec.id FROM activ_sec asec
        inner join sect e on e.id=asec.FK_ID_SECTIUNE_e
        inner join chest ch on ch.id=e.FK_ID_CHESTIONAR
    WHERE ch.id=&1);
delete FROM sect where id in
    (select e.id FROM sect e
        inner join chest ch on ch.id=e.FK_ID_CHESTIONAR
    WHERE ch.id=&1);

See the documentation here.


SSIS FTP Sending Blank Files I'm supporting an existing data interface that is a series of SSIS packages (SQL 2008 R2) for sending and receiving data with a third party.  The communication is being done through FTP, and it uses a SSIS Script Task to call the FtpClientConnection objects.

This has been working fine for years, and then a few days ago it suddenly started randoming zero-length files.  It runs every 15 minutes, and if it's sending 10 files, 2 or 3 or 5 of them will be blank.  The source files that it is trying to send on our side is good, and if we resend it through another FTP client (like FileZilla), they transmit fine.

Again, this just started happening at a specific time and has been happneing since then.  No updates or installations were done on that server before then (the server has been rebooted after that, but the problem started before the reboot and it had not been rebooted for a week prior to that).

    Dim FtpServer As String = Dts.Variables("FtpServer").Value
    Dim FtpUser As String = Dts.Variables("FtpUser").Value
    Dim FtpPass As String = Dts.Variables("FtpPass").Value
    Dim FtpInbound As String = Dts.Variables("FtpInbound").Value
    Dim LocOutbound As String = Dts.Variables("LocOutbound").Value
    Dim FtpFile As String = Dts.Variables("FtpFile").Value
    Dim DistributorID As Integer = Dts.Variables("DistributorID").Value
    Dim UploadFile As String = LocOutbound & FtpFile

    Try
        Dim oCnMgr As ConnectionManager = Dts.Connections("FTP")
        Dim oFtp As FtpClientConnection = New FtpClientConnection(oCnMgr.AcquireConnection(Nothing))

        oFtp.ServerName = FtpServer
        oFtp.ServerUserName = FtpUser
        oFtp.ServerPassword = FtpPass
        oFtp.Retries = 5
        oFtp.UsePassiveMode = True

        If File.Exists(UploadFile) Then
            Dim aUp As New ArrayList
            aUp.Add(UploadFile)

            oFtp.Connect()
            oFtp.SetWorkingDirectory(FtpInbound)
            oFtp.SendFiles(aUp.ToArray(GetType(String)), FtpInbound, True, True)
            oFtp.Close()

            PostFtpFile(" UPDATE FtpUpload SET Uploaded=1 WHERE Filename='" & FtpFile & "' AND DistributorID=" & DistributorID.ToString & " ")
        End If
        ...

The FTP client connection is set up with a 60-second timeout, passive mode, and 5 retries.  It was originally set to a chuck size of 1, but I also tried 10 and 100 chunk sizes with no improvement.  The average file is is between 600 bytes and 2 KB.

Is there any additional way to troubleshoot this?  The task thinks it's successful, but I can't find any way to get detailed diagnostics from the SSIS component.  Is there a log file that gets generated anyhere, or a way to get back from the FTP component how many bytes it actually sent?  Many other FTP component I've worked with outside of SSIS had options to record detailed logs of the transmission.

Any help or guidance would be appreciated.  This system is scheduled to be rewritten in the next few months, but as of now it has brought my clients ecommerce business to a halt.

Thanks

May sound drastic but if you can't find a good way to debug within SSIS then you can always resort to Network Monitor or Wireshark.  That way you can see what's happening during the FTP session.  Another option may be to ask for some extra FTP logging on the receiving end.


Database Mail stops working after browser service stopped After stopping the browser service on a 2-node SQL Server failover cluster system, the Database Mail component stopped working. It fails and creates log entries as follows:

  Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException
  Message: There was an error on the connection.
  Reason: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible.
  Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified), connection parameters:
  Server Name: SQLCLNETWORK1\INSTANCE1,
  Database Name: msdb
  Source: DatabaseMailEngine

The instance behind "SqlClNetwork01" is running on non default Port. The SQL Server engine itself continued without any problems.

So my question is, why does the Mail Service have issues with the browser service stopped? Where is it trying to connect to the SQL Server engine without success?

It has issues because it would use the instance name rather than a port number, and the Browser service is required to do translation from instances to ports. Your other apps must be hardcoded to use the port numbers (or have aliases set in their SQL Client settings, or something similar).

Anyway when a mail is sent this triggers MSSQL\Binn\DatabaseMail.exe to run. I wasn't able to find out how it specifically works out what the default server name is, but you may be able to reconfigure it by creating an MSSQL\Binn\DatabaseMail.exe.config like this on each node, and including a port number:

<configuration>
    <appSettings>
        <add key="DatabaseServerName" value ="LocalServerName" />
        <add key="DatabaseName" value ="msdb" />
    </appSettings>
</configuration>


Get User IDs with no boolean true answer I'm having difficulty wrapping my head around this query. I have a table with a bunch of user features, and I'm trying to get all the users that have no features turned on. I know I need to do something with COUNT and GROUP BY, but SQL is really not my strong suit, and this needs to be done sooner rather than later, otherwise I'd do more research on my own. This is the table structure:

| UserFeatureID | UserID | FeatureID | Active
-----------------------------------------------
| 1             | 1      | 1         | false
| 2             | 1      | 2         | true
| 3             | 2      | 1         | false
| 4             | 2      | 2         | false
| 5             | 3      | 1         | false
| 6             | 3      | 2         | false
| 7             | 4      | 1         | true
| 8             | 4      | 2         | true

So the list of user ID's it'd return is 2 & 3 since they are the only ones that have all features turned off. Any help is appreciated, thank you!

You can just map false/true to 0/1 and sum those per user. Those that have a zero sum have all false.

select userid
from T
group by userid
having sum(case when active then 1 else 0 end) = 0;

A bit shorter is to use count instead of sum. Nulls are not counted, so we only need to map true to a value, and count all values. I use 1 here but you can pick any value that you want:

select userid
from T
group by userid
having count(case when active then 1 end) = 0;


Where to find the OID of a newly created Schema I have created a new Schema in my Postgres db. I want to replace an OID in the pg_class table with the OID of the new Schema. But I don't know where to find the OID of the newly created Schema. Can somebody help me with the right queries?

Do NOT change the system catalogs manually!

If you want to move a table to a different schema, there is no need to know the  oid of the table or the schema:
As documented in the manual you can easily do this through ALTER TABLE
ALTER TABLE old_schema.some_table
    SET SCHEMA new_schema;

without having to check or know any oid.


With the comment from FrustratedWithFormsDesigner, I came to the following solution:

SELECT subq2.*, sum(new_group) OVER (ORDER BY t ASC) AS group_id
FROM (
  SELECT subq.*, CASE WHEN delta > 1500 THEN 1 ELSE 0 END AS new_group
  FROM (
    SELECT t, lag(t) over (ORDER BY t ASC),
      t - lag(t) over (ORDER BY t ASC) AS delta
    FROM time_points
  ) AS subq
) AS subq2

I am using the sum of all new_group values to create different groups.
Thank you very much!


mysql 5.6 gtid replication: is log_slave_updates required? I've read the docs saying log_slave_updates is required.  It makes sense for a general promotion situation.

But is that really still required for something you're intending to be a read only slave that will never get promoted?

Yes, you must set it when using gtid, otherwise mysql won't start, providing you the following error:

[ERROR] --gtid-mode=ON or UPGRADE_STEP_1 or UPGRADE_STEP_2 requires
--log-bin and --log-slave-updates

The slave requires its own executed binary logs in order to auto-position itself in the case of a stop (for example, if it crashes) or a master change (the hardware on the master fails without a possibility of a fix and another slave is promoted as the new slave), even if it is not going to be a master itself never. Although I suppose that can be controlled exclusively in-memory when running, I would bet it needs its own binary logs as a failsafe in case of a crash.

You may think that that is a lot of trouble, but it is a small burden in exchange for not having to deal with binlog files and binlog pos addresses anymore. Also, binary logs on the slave was always a good idea. You can still purge them automatically with expire-logs-days/max-binlog-size if the size is the problem. And with group-commit in 5.6, the performance penalty should be minimal. The other thing why I did not used log_slave_updates before (checking that no writes have been done on the slaves) can now be controlled with gtid_executed values.

In a nutshell, set it up, and do not worry on most cases.


Does Cassandra CQL provide a command to generate an execution plan? In Oracle and other DBs you can generate an execution plan for SQL.

Is this possible for Cassandra?

I want to determine if it is possible to measure the relative complexity of several CQL statements to retrieve query data.

A Google search did not indicate that this is possible.

You can enable tracing of the CQL queries, and see how it's executed. In CQLSH it's done with command TRACING ON;, after you finish set TRACING OFF;.

This blog post provides more detailed description of it.


Oracle vs SQL Server, 4k vs 16k block sizes I was reading over these 3 articles:

At my last company we only used MS SQL Server and MySQL, and if a server was strictly a database server we would setup the HDD to use 16k block sizes.

http://houseofbrick.com/improve-performance-as-part-of-a-sql-server-install/

I work for a different company running Oracle, we have a small issue every once in a while where the database will lock up for about 2 to 3 seconds when a query reads about 700K rows; we know it's a coding issue where a where statement needs to be added.

This article shows some metics for running 8k to 16k on various tables of size and weight:

http://blog.dbi-services.com/do-the-block-size-matter/

Then I read something very interesting:

http://www.dba-oracle.com/s_oracle_block_size.htm

Specifically here:

Block Size Advantages

Smaller blocksize:

-  Good for small rows with lots of random access.

Larger  blocksize:

-  Permits reading several rows into the buffer cache with a single I/O (depending on row size and block size).

Block Size Disadvantages

Smaller  blocksize:

- Not recommended for large rows. There might only be a few rows stored for each block, or worse, row chaining if a single row does not fit into a block.

Larger blocksize:

- Not good for index blocks used in an OLTP environment, because they increase block contention on the index leaf blocks.

Can I split up table reads and specify block size to use?

I thought block size is consistent across the disk and set when the drive is partitioned.

We have a 3-tier SAN, the Oracle server is virtual if that makes a difference.

Oracle logical block size is orthogonal to the storage logical block size (and the file system block size, for that matter). At a minimum your Oracle block size should be a multiple of the disk block size to avoid reading unnecessary data. If your tablespaces have different block sizes, the smallest of them (block sizes) would need to meet this constraint.


What a closure `F` of a family of functional dependencies is? I have some issues to determine what a closure F of a family of functional dependencies is.

I know its definition is

  F⁺= {X → Y\F⊨X → Y}

One example

Let be the following relation

R(Student,Examination, Date) with the following set of functional dependencies:

F={D,St → Ex, Ex → D}

The relation is decomposed in R¹(St,Ex) R²(Ex,D)

Why is F¹= ∅  but F²={Ex → D}? I would have made: F²= ∅ from the definition of F⁺

I know from the community wiki that

  Functional dependencies requires that they must hold for every
  possible instance.

  From an instance, you cannot find the functional dependencies that hold in a certain relation schema

Therefore I don't know how to know what is the closure of a family of functional dependencies.

Another example would have been:

Second example

R(Course,Student,Birthday,Grade)

with the following set of functional dependencies:

F={C,St → G, St → B}

And the following relations:

R¹(C,St,G) and R²(St,B)

F¹{C,St  → G} and R²(St  → B) I would have done, from the definition:

F¹=F²=∅

When you have a relation R with a set of functional dependencies F and a decomposition of R in R1...Rn, you must consider two different concepts:

The closure of the set of functional dependencies F. This closure, called F+, is the set of all the dependencies derived from F, by applying, until possible, a set of rules called “Armstrong’s axioms”. This set can be very lange, (exponential with the number of dependencies of F), so in general it is not calculated. What can be calculated, instead, is a cover of it, which is a small number of dependencies from which F+ can be derived (and so which is equivalent to F+). This computation is useful, for instance, when you normalize a relation.
The projection of the set of dependencies F, over the decomposition of R. By definition, this projection is, for each Ri, the set of all the dependencies of F+ that have all the attributes in Ri (and is represented as πRi(F)). But, since we do not calculate F+, as said above, we cannot calculate such projection. What can be done in practice, in simple cases, is to see if there is some dependency in F that has all its attributes contained in Ri, so we are sure that such dependency holds in  Ri. However, if in this way you don’t find a dependency in one of the decomposed relation, this is not a guarantee that such dependency is lost, since it could hold as consequence of other dependencies.

In your first example, you have two dependencies in F for the relation R(S, Ex, D):

D, St → Ex
Ex → D

So, consider R2 = (Ex, D), we can see immediately that in the dependency Ex → D holds in R2, since all its attributes are contained in R2, while the first dependency cannot hold neither in R1 and R2, since it has three attributes. Actually, in relation R1, you have neither the first, nor the second dependency (only trivial dependencies from F+ are present in R1, like St → St).

In your second example, instead, the decomposed relations are such that in the first there are all the attributes of the first dependency, so it hold trivially in it, while in the second there are all the attributes of second dependency, and again you can be sure that this second dependency holds in the second relation.

Finally, note that, while it is no computationally feasible to calculate πRi(F), there is an efficient polynomial algorithm to see if a certain decomposition preserves the dependencies, in other words, if ∪πRi(F) is a cover of F+. You can see more details in this answer.


Does SQL Server cache data results for DAO? I was surprised to be confronted with the idea that using .NET and DAO one could make use of the SQL Server data cache in a way not at all possible with stored procedures (which benefit from the plan cache). I.e. that the query results would be cached and your .NET application run much faster due to this. Is this non-sense? There seems to be some sort of caching for DAO but that doesn't seem to be really relevant in this case.

I've read this question, but it doesn't really answer the question, because it's about stored procedures only.

I hope I post this in the right place. Any help appreciated.

While I would usually leave an answer this short as a comment, this seems to be worthy of breaking with good practice.

No. Nonsense. Absolutely not.

The answer that @gbn gave to the question you reference is valid, regardless of the method of query. Stored procedure or adhoc, the same applies... Query results are not cached.

  However, the source table and index data and metadata will be cached
  after the 1st use (subject to continued use, load and memory pressure
  though)

  That is, the results of a query will be evaluated every execution but
  the tables(s) (and any indexes etc) used by the query will most likely
  be in memory already.

The "some sort of caching for DAO" is client/API behaviour, irrelevant and unbeknownst to SQL Server.


sql 2005 - trace capturing all events I am trying to capture all statements/SPs and RPCs (either they finish or fail) on a SQL 2005 server using SQL trace.
Are the following events enough?

Exceptions
Stored Procedure
RPC:Completed
RPC:Starting
SP:Recompile
TSQL
SQL:StmtCompleted
SQL:StmtStarting

thanks!

Just use a trace template.  Click File, New Trace, and choose the Replay template.  That'll get you everything that happens.


Can I use SQL Server Authentication for Central Management Servers? I have set up a Central Management Server in SQL Server. When I try to add a new Registered Server to this Central Management Server, I only have the options to use Windows Authentication or Active Directory Integrated Authentication. Is there a way to connect to these Registered Servers using Sql Server Authentication?

This option does exist when connecting to a local server group.

CMS Drawback: Windows Authentication Only, And Only Your Login

Central Management Servers and subordinate registered servers can be registered only by using Windows Authentication.

See:

Create a New Registered Server (SQL Server Management Studio) in the documentation
Central Management Server (CMS) – A Hidden Gem by Dan Taylor.


Repair SQL Server Database Can anyone give me a complete guide to verifying/repairing a SQL Server database?

At the moment our backup is failing on the Solarwinds database saying the database NetPerfMon is corrupted (we are using Backup Exec 12.5 to perform the backups). I have SQL Server 2005 Management Studio Express installed if that helps other than that I don't have much of an idea what to do with SQL other than little bits of dabbling with it.

Please run below command in your SQL server database to check the level of corruption.If database is showing suspect it wont allow you to run this command

  DBCC CHECKDB (DB_NAME) WITH ALL_ERRORMSGS,NO_INFOMSGS

This command can be used to check corruption in SQL Server database. Also I must say you are running very old version of SQL server if you are not on SP4 your SQL server is in un supported state. If above command returns any consisteny and allocation error there is corruption in SQL server database if not then issue is with backup tool you need to contact vendor in that case.

Best method to recover from corruption is to restore from latest valid backup. You can use RESTORE VERIFYONLY to check consistency of backup before restoring although only successful restore can guarantee that backup is in complete consistent state.

Your other task is to look at SQL Server errrorlog and windows event viewer to find any information related to corruption and take appropriate action. Major cause is inconsistent disk subsystem.

Restoring from valid backup would involve first restoring full backup with no recovery then differential backup(if any) with no recovery and then all log backup witn no recovery but the last log backup with recovery.I you just have full database backup you can restore it with recovery.

How to restore database using SSMS


FLUSH LOGS command introduces a long sleep I am using Percona mysql 5.0-92-log on Centos 6.6 final.

I have logged in as a privileged user to mysql. If I issue command

mysql> FLUSH LOGS;

The terminal goes for a long sleep

Below is the output of processlist

mysql> show processlist;

+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
|  9 | root | localhost | NULL | Sleep   |  393 |       | NULL             |
| 10 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+

2 rows in set (0.00 sec)

If I kill the process below is the error I get

mysql> FLUSH LOGS;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Same issue I face while trying to take a mysqldump by issuing command

/usr/bin/mysqldump --user=root --password='****' --host=localhost --verbose --master-data=2 --flush-logs test > $DUMPFILE
-- Connecting to localhost...

And than it hangs.

If I don't use --flush-logs option I get the dump generated properly.

Any pointers as to why it happens?

There was issue with the log-error file to which I was suppose to log mysql errors.
I had this line in my.cnf

log-error=/var/lib/mysqld.log

Due to permission issue mysql was not able to create this log file nor an error was thrown suggesting the same.

I selectively removed and toggled the settings which helped me reach to the problem.


How do I fix SQL Server assembly optimization errors and memory exhaustion? The .NET Runtime Optimization Service (mscorsvw) keeps consuming all my workstation's memory since I installed SQL Server 2012 Service Pack 1.

It consumes about 1.6 GB of memory before exhausting all available memory:

I can kill the process, but it happens again when I restart my computer or wait long enough.

Techdows suggests using the .NET Native Image Generator (ngen) to solve the problem.

I oipened a Command Prompt and ran commands like this:

cd C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319.1
ngen executeQueuedItems

The memory consumption of mscorsvw dropped to about 20-30 MB, and ngen consumed all the remaining memory.

ngen generated only error messages until I killed the process. The first few lines of output looked like this:

Microsoft (R) CLR Native Image Generator - Version 4.0.30319.1
Copyright (c) Microsoft Corporation.  All rights reserved.
Failed to load dependency Microsoft.DataWarehouse of assembly
Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91 because of the following error : The system
cannot find the file specified. (Exception from HRESULT: 0x80070002)
Failed to load dependency Microsoft.VisualStudio.DataTools.Interop of assembly
Microsoft.DataWarehouse, Version=11.0.0.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91 because of the following error : The system
cannot find the file specified. (Exception from HRESULT: 0x80070002)
Failed to load dependency Microsoft.Data.ConnectionUI of assembly
Microsoft.DataWarehouse, Version=11.0.0.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91 because of the following error : The located
assembly's manifest definition does not match the assembly reference.
(Exception from HRESULT: 0x80131040)

All the messsages say either that the system could not find an assembly, or that a manifest definition does not match a reference.

As a workaround, I have disabled the Windows Services clr_optimization_v4.0.30319_32 and clr_optimization_v4.0.30319_64.

This hides the problem rather than solving it, and stops all .NET assemblies from being optimized.

What else can I try to solve this problem?

This problem is described on Connect (see msiexec.exe processes keep running after installation of SQL Server 2012 SP1) and a hotfix is available at KB2793634. There is commentary on work-arounds on Connect and this blog if you are unable to install the hotfix immediately.

I chose to setup 2 scripts.  1st script runs daily at 1am, and set to terminate after 1 hour of execution

c:\windows\Microsoft.NET\Framework\v4.0.30319\ngen.exe executequeueditems
c:\windows\Microsoft.NET\Framework\v2.0.50727\ngen.exe executequeueditems
c:\windows\Microsoft.NET\Framework64\v2.0.50727\ngen.exe executequeueditems
c:\windows\Microsoft.NET\Framework64\v4.0.30319\ngen.exe executequeueditems

2nd script runs daily at 3am (and whenever the computer restarts):

c:\windows\Microsoft.NET\Framework\v4.0.30319\ngen.exe queue pause
c:\windows\Microsoft.NET\Framework64\v4.0.30319\ngen.exe queue pause

This way I still get compile optimization jobs (only run during off-peak hours), but the mscorsvw/msiexec process is tamed.


Convert units of measurement Looking to calculate the most suitable unit of measurement for a list of substances where the substances are given in differing (but compatible) unit volumes.

Unit Conversion Table

The unit conversion table stores various units and how those units relate:

id  unit          coefficient                 parent_id
36  "microlitre"  0.0000000010000000000000000 37
37  "millilitre"  0.0000010000000000000000000 5
 5  "centilitre"  0.0000100000000000000000000 18
18  "decilitre"   0.0001000000000000000000000 34
34  "litre"       0.0010000000000000000000000 19
19  "dekalitre"   0.0100000000000000000000000 29
29  "hectolitre"  0.1000000000000000000000000 33
33  "kilolitre"   1.0000000000000000000000000 35
35  "megalitre"   1000.0000000000000000000000 0

Sorting by the coefficient shows that the parent_id links a child unit to its numeric superior.

This table can be created in PostgreSQL using:

CREATE TABLE unit_conversion (
  id serial NOT NULL, -- Primary key.
  unit text NOT NULL, -- Unit of measurement name.
  coefficient numeric(30,25) NOT NULL DEFAULT 0, -- Conversion value.
  parent_id integer NOT NULL DEFAULT 0, -- Relates units in order of increasing measurement volume.
  CONSTRAINT pk_unit_conversion PRIMARY KEY (id)
)

There should be a foreign key from parent_id to id.

Substance Table

The Substance Table lists specific quantities of substances. For example:

 id  unit          label     quantity
 1   "microlitre"  mercury   5
 2   "millilitre"  water     500
 3   "centilitre"  water     2
 4   "microlitre"  mercury   10
 5   "millilitre"  water     600

The table might resemble:

CREATE TABLE substance (
  id bigserial NOT NULL, -- Uniquely identifies this row.
  unit text NOT NULL, -- Foreign key to unit conversion.
  label text NOT NULL, -- Name of the substance.
  quantity numeric( 10, 4 ) NOT NULL, -- Amount of the substance.
  CONSTRAINT pk_substance PRIMARY KEY (id)
)

Problem

How would you create a query that finds a measurement to represent the sum of the substances using the fewest digits that has a whole number (and optionally real component)?

For example, how would you return:

  quantity  unit        label
        15  microlitre  mercury
       112  centilitre  water

But not:

  quantity  unit        label
        15  microlitre  mercury
      1.12  litre       water

Because 112 has fewer real digits than 1.12 and 112 is smaller than 1120. Yet in certain situations using real digits is shorter -- such as 1.1 litres vs 110 centilitres.

Mostly, I'm having troubles picking the correct unit based on the recursive relation.

Source Code

So far I have (obviously non-working):

-- Normalize the quantities
select
  sum( coefficient * quantity ) AS kilolitres
from
  unit_conversion uc,
  substance s
where
  uc.unit = s.unit
group by
  s.label

Ideas

Does this require using log10 to determine the number of digits?

Constraints

The units are not all in powers of ten. For example: http://unitsofmeasure.org/ucum-essence.xml

This looks ugly:

  with uu(unit, coefficient, u_ord) as (
    select
     unit,
     coefficient,
     case
      when log(u.coefficient) < 0
      then floor (log(u.coefficient))
      else ceil(log(u.coefficient))
     end u_ord
    from
     unit_conversion u
  ),
  norm (label, norm_qty) as (
   select
    s.label,
    sum( uc.coefficient * s.quantity ) AS norm_qty
  from
    unit_conversion uc,
    substance s
  where
    uc.unit = s.unit
  group by
    s.label
  ),
  norm_ord (label, norm_qty, log, ord) as (
   select
    label,
    norm_qty,
    log(t.norm_qty) as log,
    case
     when log(t.norm_qty) < 0
     then floor(log(t.norm_qty))
     else ceil(log(t.norm_qty))
    end ord
   from norm t
  )
  select
   norm_ord.label,
   norm_ord.norm_qty,
   norm_ord.norm_qty / uu.coefficient val,
   uu.unit
  from
   norm_ord,
   uu where uu.u_ord =
     (select max(uu.u_ord)
      from uu
      where mod(norm_ord.norm_qty , uu.coefficient) = 0);

but seems to do the trick:

|   LABEL | NORM_QTY | VAL |       UNIT |
-----------------------------------------
| mercury |   1.5e-8 |  15 | microlitre |
|   water |  0.00112 | 112 | centilitre |

You don't really need the parent-child relationship in the unit_conversion table, because the units in the same family are naturally related to each other by the order of coefficient, as long as you have the family identified.


WITH position unexpected position using Mysql 8.0 Date Generation I'm attempting to use the Date Generation Example to generate my own date range but I can't get the query to run.  The query fails and Workbench shows an error indicating my syntax is invalid.

Am I overlooking/misunderstanding something about how this can be used?  I can't use stored procedures so I hope this isn't limited to being used in that context.

use mydb;

WITH RECURSIVE dates (date) AS
(
  SELECT date + INTERVAL 1 DAY FROM dates
  WHERE DAYOFWEEK(date) = 1 AND
    date >= DATE_SUB(NOW(), INTERVAL 1 YEAR);
)
SELECT * FROM dates;

Recursive CTE implemented in the MySQL 8+ requires the next syntax:

WITH RECURSIVE tbl (a,b,c) AS
(
  SELECT ...  -- initial portion of rows
  UNION ALL
  SELECT ...  -- recursively added rows
)
SELECT *
  FROM tbl
   ...
;

Refer to the documentation/primers for the proper recursive CTE using.


Postgresql 9.6 HA issues (Editing the question with additional information: )

We are in process of testing a Postgres 9.6 active-passive HA cluster using streaming replication on Rhel 7.5. The HA setup is done using pacemaker and  Failover is automated.

We are seeing below issue with the setup :

When a failover is triggered (while data is being added to the primary) by killing primary (killall -9 postgres), standby node takes role of primary and the failed node(old primary) is restarted. However it doesn't start streaming or take stand-by role. It starts in disconnect mode.

We are using Postgres 9.6 because of application specific requirement - other component's dependency on 9.6 version
One thing we checked and noticed that the wal receiver process is not started on the new standby. If we kill postgres processes again the wal receiver process is started and standby node now syncs with new primary.
Another observation is that in the disconnected node, there is some additional data, which is not present on the new stand-alone standby. Which means that the failed primary was ahead in terms of log.

Need urgent inputs in :

Is there any way of simulating abrupt shutdown of postgres?
How can we prevent the situation of data mismatch with "killall -9" on Primary that is prevent old primary having additional data when postgres is killed and failover happens. We are running streaming replication in Sync mode

Thanks !

For questions like that, you'll have to consult the log files.

But the likely explanation is that the primary server was ahead of the standby when it crashed (this can also happen with synchronous replication). Then it cannot act as standby to the newly promoted server.

There are some solutions:

Easiest, but takes long: Reinitialize the server with pg_basebackup.
Faster, but more complicated: get the new primary in backup mode and use rsync.
Easiest, but requires that wal_keep_segments is high enough: pg_rewind the failed server.

You'll have to implement this in the failover scripts.

Why don't you use Patroni which does all that out of the box?


unable to insert utf8mb4 characters in mysql 5.6 recently i was facing problem in inserting emoji and other 4 bytes character in my mysql database then i found that my mysql version was 5.1 and it does not support utf8mb4 so i upgraded to mysql 5.6 which supports utf8mb4 and in my collation i have the option to select utf8mb4.but when i try to insert directly in table row through phpmyadmin i am  getting this error
refered this link also

       http://dba.stackexchange.com/questions/59126/set-value-of-character-set-client-to-utf8mb4

 1 row affected.
      Warning: #1366 Incorrect string value: '\xF0\x9F\x9A\x8A k...' for column 'rajulp' at row 1

i googled and found that this is because the database is not supporting utf8mb4 characters and then i followed few tutorials in internet to update my.cnf file and restarted mysql server as well  as  server.

       https://mathiasbynens.be/notes/mysql-utf8mb4

i tried almost all possible combination but again when i type this in sql it throws maximum as utf8 because of that i am unable to insert eboji and several 4 bytes character.

 Punjab me 1Train k niche 100 Sardar aa gaye..

output of below command in mysql

 SHOW VARIABLES LIKE 'char%'

  character_set_client  utf8
     character_set_connection   utf8
   character_set_database   utf8mb4
    character_set_filesystem    binary
    character_set_results   utf8
   character_set_server utf8mb4
  character_set_system  utf8
     character_sets_dir /usr/share/mysql/charsets/

please guide

I had the same problem with an Ubuntu installation of MySQL 5.6.23.

I had to edit /etc/mysql/my.conf and add these entries to these sections:

[client]
default-character-set=utf8mb4

[mysqld]
character-set-server = utf8mb4

[mysql]
default-character-set=utf8mb4

Then as root execute: service mysql restart

Both my webserver connections and my local (shell) mysql connections worked with utf8mb4.

INSERT INTO some_table_name
(some_index_id,value_to_be_utf8mb4)
VALUES (55, x'F09F9A8A');

select * from some_table_name;
| some_index_id | value_to_be_utf8mb4 |
|            55 |                   |
1 rows in set (0.00 sec)

The following are from a fresh command line instantiation of mysql:

mysql> show variables like "%coll%";
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+

mysql> show variables like "%char%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

As a final note, the "character_set_system" is always "utf8" according to the mysql 5.x specifications, so that is normal.


Database design for a simple association I'm not so good in db/tables design, I'm mainly a frontend web developer, but sometimes I do really small apps. Always I find myself wondering if my thinkings are correct.. So I try to ask here :)

The db is for an app for helping a local association to manage its associates and their annual cards/subscriptions. Basically:

Once associated, the person is an associate forever (no need to renew every year)
Still, the associated needs an annual card to participate to the association's activities. So he/she needs to eventually renew his/her subscription to the activities, yearly. It's not mandatory, though: he's not forced to renew every year. He can be associated without having the current year subscription/card.

So, one associate can have many cards (one per year). One Card can have only one Associate.

I imagined just 2 tables:

Associates
   id
   name
   surname
   etc...

Cards
   associate_id
   year

It should be enough. But, should I maybe instead use a Year table? I'm not really sure this would be any useful in this specific case. Also I would then have a many-many relation between years and associates, so I guess I would need a third table (YearAssociate).

What do you think?

No, adding a year table would add nothing to this data model.

Think about what a Year table would be like. What columns might it have? It really only has the year number itself - 2018, 2019 .. 2031, 2032 etc. Cards would then have a foreign key to this table, which the currently existing column Cards.Year already represents.

If you were doing a logical data model you may choose to put the Year entity type in it. For a physical database implementation & table design there is no need to implement a Year table.

One caveat - if your years are not calendar years, and you care about the calendar dates that each membership year starts and end, then having a separate Year table makes sense. Think about the tax year. It starts in, say, calendar April and runs to the following calendar March. So tax year and calendar year do not cover the same set of days.


How can I choose the best autogrowth option for my new database? I want to create a new database that I can use for testing, restoring it from a full backup from a production database. These backup file sizes are around 45,486KB, and the production database size is 3.7GB. When I try creating the new database, the default initial size (MB)says 45 for the Primary rows data and 3739 for the Log, Autogrowth by 5MB restricted to 50MB for the data and by 10 percent restricted to another large number!

Any advice on what is the ideal number to start with? When do I need to change them? I'm using SQL Server 2008. Thanks.

I believe the log file is seeing that large size because during the backup from production the size there itself was 3.6 GB approx.

As mentioned below by experts, excluding the part of my answer recommending a fix size. Therefore,to analyse more on how to decide the size you may want to keep, check on the autogrow size of data and log file:

select DB_NAME(files.database_id) database_name, files.name logical_name,
CONVERT (numeric (15,2) , (convert(numeric, size) * 8192)/1048576) [file_size (MB)],
[next_auto_growth_size (MB)] = case is_percent_growth
    when 1 then CONVERT(numeric(18,2), (((convert(numeric, size)*growth)/100)*8)/1024)
    when 0 then CONVERT(numeric(18,2), (convert(numeric, growth)*8)/1024)
end,
is_read_only = case is_read_only
    when 1 then 'Yes'
    when 0 then 'No'
end,
is_percent_growth = case is_percent_growth
    when 1 then 'Yes'
    when 0 then 'No'
end,
physical_name
from sys.master_files files
where files.type in (0,1)
and files.growth != 0

Also It is not recommended to keep “In Percent” File Growth option for database files. If “In Percent” option is used for any database file and if the file size is very big, it may cause performance degradation during the file growth phase.


How to make a query on multiple columns without writting all possible permutations? I'm at my first question on stackexchange, as i have a few days since I struggle on this matter:

I want to make a complex query(PLSQL) on a table that has col1,col2,col3,col4,col5 having values like (names: which are split- one part per column)

Andrew Joan Bach Mike
Mark Andrew Livy
Joan Arch Donnie
Joan Andrew Lyx

Number of parts differ from 1 to 5.

I want to search in different combinations like this:

Bach Joan Mike - get #1, #3,#4 order
Andrew Bach - get the following order #1,#2,#4 order

I don't like the idea of using a looooong query in which I will write all possible permutations in order to mach each part of my search string

What I'd like to achieve is this:

first set: match all those n parts (both Bach and Joan and Bach match, in any order)
second set: match n-1 parts (at least N-1 of my search parts match the row, in any order)
third set: match n-2 parts

I use a ORACLE database and I was thinking on making it in a stored procedure: match_my_set(query_str,col1,col2,col3,col4,col5). I would write at least 5 loops (loop into loop) in order to achieve this, but I doubt it is a professional idea . .

Any help is appreciated. Thank you

You don't need loops, but one CASE per searched value:

select ...
from
 (
   select ...
      case when 'Bach' IN (col1,col2,col3,col4,col5) then 1 else 0 end +
      case when 'Joan' IN (col1,col2,col3,col4,col5) then 1 else 0 end +
      case when 'Mike' IN (col1,col2,col3,col4,col5) then 1 else 0 end as matches
   from tab
 ) dt
where matches > 0
order by matches desc


With SQL Server 2008, how can I roll back a transaction done some time before? I am coding up a script to merge some accounts (and other tables) as an out-of-band customer request. All the updates will be done in a single transaction. They are all updates (no inserts or deletes) - effectively changing the "account_id" of some entities to achieve the account merge.

That's all fine, but my boss wants to be able to roll back the affected accounts after other transactions, i.e. DAYS down the road on a very active server.

My ideas are: -

print out all the affected rows (select all the previous data to console) so I could manually move everything back if required (there aren't too many affected tables - about 8)
export the transaction and "somehow" roll that individual transaction back - without affecting any subsequent transactions.
something else?

Many thanks!

There's no way to roll back a specific transaction like that using the native tools.  There are (or at least there used to be) some third party tools which would let you do this, but it depends on a lot of things.

Your best bet will be to log the before and after values for all the rows that you change during out of band update, then if you need to roll back those changes you've got the needed values.  You might want to log the new values as well as the old values so that you can see if the values have been since changed again so that you can then figure out if you want to roll back those changes.


Does SQL manage foreign key constraints in terms of insertion? Say I have a table tbl1 with data A1 and A2, where A1 is the primary key.  Then I have another table tbl2 with data A3 and A1, both together being the primary key, referencing A1 as a foreign key from tbl1.

Am I able to insert a tuple into tbl2 that has an A1 that is not in tbl1?  Does SQL manage this for us as an error?  Or what happens in this situation?

As discussed in the comments violation of a foreign key constraint will produce an error on most legitimate server products.  However, this is only when the foreign key has been defined physically.

A logical foreign key can exist without the enforcer that on most engines is called the  constraint.  I've seen a lot of schemas that show relations on paper but on close inspection revealed that logical relationship wasn't actually being enforced by a foreign key constraint.  This is important to check for data integrity purposes as well as query performance when relationships are properly indexed as well.

When the constraint has been defined then each of the following server products will generate an error if you violate that constraint.

MySQL:

violation of FOREIGN KEY constraint "<name of constraint>" on table "<your table>".

note MySQL currently only supports Foreign Key constraints natively with InnoDB & Falcon engines

SQL Server:

statement conflicted with the FOREIGN KEY constraint "<name of contraint>". Conflict occured in database <your database>

Postgres:

insert or update on table "<your table>" violates foreign key constraint
"<constraint name>"

Oracle:

ORA-02291: integrity constraint (<schema.constraint>) violated - parent key not found

Sybase's message is probably identical to SQL Server, I know DB2 enforces it.  SQLite does as well though the error message doesn't identify the constraint.

Note these engines will also raise an error if you try to remove the parent once children relying on the key exist.

Another tip:

When you look at these errors you also pick up on a good development strategy.  Name your constraints in a way that makes it easy to track back to the tables involved since almost all of them identify the constraint name in the error.


MySQL replication not finished on next site call I have 3 MySQL8.0 instances. Two of them are slaves. Each site call I‘m reading the slave till the first write Request is made. Afterwards Only the Master is used during the request. Now, if I write something into the database and redirect the user to a new page, the slave isn‘t updated yet. Is there any way I can handle such things?

Solutions I already found downgrade to MySQL 5.7 with Galera Cluster/Percona xtraDB/Group Replication.

Any other ideas? Which would you prefer?

While an application-independent solution could be possible, in general, a replication-aware application could be much better, because you can use the high performance of asynchronous replication for most of the time, and only revert back to (slower) synchronous replication when it is needed.

This is not a theoretical method, we at Wikipedia use this model with specific code called "chronology protector", which only gets triggered when an important event happens (e.g. an edit) and you don't want the user to be served outdated results.

In order to do this, after doing your master writes, you should keep your master replication file and position (if not using GTID), or the GTID coordinates (if using GTID) after your write (once the transactions has been commited on the master), and then on the replica connections, wait until the server reaches those positions or higher. There is some extra details to consider, such as when exactly to wait (on write or on read), for how long until you give a timeout (e.g. if replica breaks or lags), but that is highly dependent on your environement.

In terms of implementation, MASTER_POS_WAIT() / WAIT_FOR_EXECUTED_GTID_SET() are the functions commonly used for this.

Some of the clusters you mention give you shortcuts to perform this easier (e.g. Galera's causal reads), but the logic is similar- you don't want to be on fully consistent mode all the time; just forcing it when necessary.


What is the data type of the ‘ctid’ system column in Postgres? The Postgres system columns are documented in Chapter 5. Data Definition > 5.4. System Columns.

That page mentions that oid values “are 32-bit quantities”. And that page says the same about transaction identifiers. So I will assume that means oid, tableoid, xmin, cmin, xmax, and cmax are all 32-bit integers.

But that leaves the ctid system column.

  The physical location of the row version within its table. Note that although the ctid can be used to locate the row version very quickly, a row's ctid will change if it is updated or moved by VACUUM FULL. Therefore ctid is useless as a long-term row identifier. The OID, or even better a user-defined serial number, should be used to identify logical rows.

➡ What is the data type of the ctid column?

Specifically I am interested in version Postgres 10.3, but if it has changed over past versions, that would be good to know.

tid

See the manual page, Chapter 8. Data Types > 8.18. Object Identifier Types. It explains that the data type is Postgres-specific, and known as tid.

  A final identifier type used by the system is tid, or tuple identifier (row identifier). This is the data type of the system column ctid. A tuple ID is a pair (block number, tuple index within block) that identifies the physical location of the row within its table.

You might find this similar Question interesting: How do I decompose ctid into page and row numbers?

By the way, if you are interested in this topic of ctid & tid, you might likely be interested in two new features of Postgres 12: (a) OIDs demoted to normal columns, and (b) the pluggable table storage / Access Methods feature new in Postgres 12 and later. See this, this, this, this, and this.


MongoDB Unrecoverable Failure My machine (OSX) had a power failure and when restarted, I have one db in MongoDB that is completely unrecoverable. I am using the db per directory with dbpath and logpath specified options and was able to recover all except one of the dbs. This is the log from the unrecoverable one. Any ideas would be helpful - I have run --repair and removed the lock file with zero results.

The Error Log message in terminal:

2015-10-07T11:28:22.102+0530 W STORAGE  [initandlisten] database/Users/JineshJohn/TECSOL/zatsa2/data/db local could not be opened due to DBException 0: assertion src/mongo/db/storage/mmap_v1/extent.h:77
2015-10-07T11:28:22.108+0530 I STORAGE  [initandlisten] exception in initAndListen: 0 assertion src/mongo/db/storage/mmap_v1/extent.h:77, terminating
2015-10-07T11:28:22.108+0530 I CONTROL  [initandlisten] now exiting
2015-10-07T11:28:22.108+0530 I NETWORK  [initandlisten] shutdown: going to close listening sockets...
2015-10-07T11:28:22.108+0530 I NETWORK  [initandlisten] removing socket file: /tmp/mongodb-27017.sock
2015-10-07T11:28:22.108+0530 I NETWORK  [initandlisten] shutdown: going to flush diaglog...
2015-10-07T11:28:22.108+0530 I NETWORK  [initandlisten] shutdown: going to close sockets...
2015-10-07T11:28:22.108+0530 I STORAGE  [initandlisten] shutdown: waiting for fs preallocator...
2015-10-07T11:28:22.108+0530 I STORAGE  [initandlisten] shutdown: final commit...
2015-10-07T11:28:22.108+0530 I JOURNAL  [initandlisten] journalCleanup...
2015-10-07T11:28:22.108+0530 I JOURNAL  [initandlisten] removeJournalFiles
2015-10-07T11:28:22.108+0530 I JOURNAL  [initandlisten] Terminating durability thread ...
2015-10-07T11:28:22.213+0530 I JOURNAL  [journal writer] Journal writer thread stopped
2015-10-07T11:28:22.213+0530 I JOURNAL  [durability] Durability thread stopped
2015-10-07T11:28:22.213+0530 I STORAGE  [initandlisten] shutdown: closing all files...
2015-10-07T11:28:22.214+0530 I STORAGE  [initandlisten] closeAllFiles() finished
2015-10-07T11:28:22.214+0530 I STORAGE  [initandlisten] shutdown: removing fs lock...
2015-10-07T11:28:22.214+0530 I CONTROL  [initandlisten] dbexit:  rc: 100

Try to use mongodump and mongorestore. mongodump works more forcefully then repair. Hope it will work and keep updated.


Adding AD Security group to a SQL linked server I have a question about adding permissions for a user group on a linked server in SQL Server. I am not a security specialist, so I appeal to everyone.
Here is the topo:
I have private software that I'll call X installed on a Hyper-V virtual server. Users of this software connect through virtual machines using the Remote desktop.
The application has its own SQL Server database instance. The security for this database is Windows Authenticated. Application X users all belong to a user group in the Windows Server 2019 Active Directory (AD).
On the other hand, I have a Y application (Microsoft Dynamic 365) that is installed locally on each workstation and which has its own SQL instance. The application uses an SQL database which I would also call Y.
As I would like to share information between the 2 SQL instances, I created a linked server on the X instance to the Y instance. I succeeded with some SQL query to insert, update, and delete some information in a database of instance Y from my instance X. To do this, I granted myself DB_owner rights on the database of X.
I have reached the stage of allowing user group X to be able to run from application X using the same queries that interact on SQL server Y.
However, I realize that I need to create each user of X in the SQL instance of application Y from the Security, Connection menu. My requests work when I grant DB_Owner roles. I would have thought that I could have used the same group used in AD for my X application. I tried well but it doesn't work.
I don't know if this is due to the configuration of my linked server.
Here is the configuration I am using:

Could someone point me to a solution to avoid having to recreate every user of my AD from X in my instance Y and just link my group X.

With groups it should be work in the same way as single user.
You must have in place proper spn to grant kerberos delegation between the two  instances.
Maybe you have tried from ssms so you haven't done the two hops for authentication. In that way NTLM is granting you permission but when you are using an application you will need to grant authentication on two hops and so you fall on the anonymous authentication error that is not granting your user proper login.
Please check it.
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/register-a-service-principal-name-for-kerberos-connections?view=sql-server-ver15


Use Common Table Expression (CTE) to reduce locks (max_locks_per_transaction) I am getting

"ERROR: out of shared memory"

presumably because my plpgsql function drops/creates a lot of temporary tables.

Searching for a solution to the issue, I found this answer that says:

  So I guess one answer is that you will need enough locks to cope with
  all these tables being added/dropped throughout the transaction.
  Alternatively, you could try to reuse the temp tables between queries,
  simply truncate them to remove all the temp data

In order to avoid having too many looks, I am thinking about rewriting as many temporary table code using Common Table Expressions (CTEs).

My questions are:

Do the table expressions in CTEs use locks that count towards max_locks_per_transaction?

Will replacing temp tables with CTEs solve the max_locks_per_transaction problem?

Related:

https://stackoverflow.com/questions/16490664/

https://stackoverflow.com/questions/3132533

A CTE is no table and won't need an entry in the lock table.

It is probably a good idea not to create and drop temporary tables all the time. Apart from the problem you are experiencing, it can lead to a bloated pg_attribute catalog table (all those added and deleted columns).

Mind that a CTE is only for the duration of a single table.


How can I insert if key not exist with PostgreSQL? I have a table:

CREATE TABLE mytable (id SERIAL, name VARCHAR(10) PRIMARY KEY)

Now I want to add names to this table, but only if they not exist in the table already, and in both cases return the id. How can I do this with PostgreSQL?

I have seen a few scripts for this, but is there no single SQL-statement to do it?

E.g. I can INSERT and return id with:

INSERT INTO mytable (name) VALUES ('Jonas') RETURNING id

it works the first time and returns id. But it fails if Jonas already exist in the table, but I want to return the id even if the Insert fails. Is this possible to do with PostgreSQL?

Upsert statements used to be planned for 9.1 but have been postponed to 9.2, so until then, your only choice is to test if the value already exists before inserting.

Alternatively, if your intention is merely to have a unique identifier, you could simply use a sequence + nextval.

If you want to create a function to do that, this should get you started:

CREATE OR REPLACE FUNCTION upsert_tableName(arg1 type, arg2 type) RETURNS VOID AS $$
DECLARE
BEGIN
    UPDATE tableName SET col1 = value WHERE colX = arg1 and colY = arg2;
    IF NOT FOUND THEN
    INSERT INTO tableName values (value, arg1, arg2);
    END IF;
END;
$$ LANGUAGE 'plpgsql';


MongoDB increase disk on the VM My problem is with Mongo eating up space on the disk.

Reading a few blogs no the web - I was certain - a repair would save me time as this would free up mongo's reserved space. But attempting a repair throws this error because I am at 90% disk usage :

db.repairDatabase()
{
    "ok" : 0,
    "errmsg" : "Cannot repair database my_d_b having size: 347673198592 (bytes) because free disk space is: 42874580992 (bytes)"
}

Then I tried the next step which is compacting every collection in the database using :

db.getCollectionNames().forEach(function (collectionName) {
  print('Compacting: ' + collectionName);
  db.runCommand({ compact: collectionName });
});

This reduced my disk usage by just 1%.

Now I can easily pause my apps from writing to this mongo database. I don't want to use any replset approach etc. I want to just move it to a bigger disk and move forward. I have already mounted an extra disk which 2.5times the size. I am not finding concrete steps to move the data and change mongo configuration and then start mongo process.

My repairs and compactions may be failing because they need space to operate as per my understading. If I can move this data to new disk and point mongo to the new disk, I will be good.

This is what I want to achieve - would appreciate a little direction.

If you have a standalone MongoDB server and can tolerate downtime, the most direct approach would be to copy the contents of your current dbPath to a directory on the new disk:

Stop mongod
Copy the contents of your dbPath to a directory on the new disk
Update dbPath in your configuration file to point to this new directory
Finally, restart mongod


Return multiple columns (int datatype) inside a CASE expression How can I get multiple columns from a CASE expression?

with cte
as
(
  select
    ROW_NUMBER() over (order by id)'Rownumber',
    id
  from
    dbo.Test
)
select
  case
    when (b.id-a.id) > 1 then a.id
    else ' '
  end
from
  cte a,
  cte b
where
  b.Rownumber = a.Rownumber+1

In the above query, I want to select a.id,b.id only if (b.id-a.id) > 1.
I tried to do it using a nested CASE expression as well as by UNION, but still not able to get the expected result.

If you do not want to return anything (i.e. if you want to omit the row entirely) when the condition is not met, then just do not use CASE, use your condition in the WHERE clause:

WITH
  ...
SELECT
  a.id AS a_id,
  b.id AS b_id
FROM
  ...
WHERE
  ...
  AND (b.id - a.id) > 1
;

If, however, you want to return something (like nulls) in both columns instead and would like to avoid repeating the condition, then you could use OUTER APPLY, but you would probably need to rewrite your comma join to use the explicit join syntax, like this:

WITH
  ...
SELECT
  x.a_id,
  x.b_id
FROM
  cte AS a
  INNER JOIN cte AS b ON b.Rownumber = a.Rownumber + 1
  OUTER APPLY
  (
    SELECT
      a.id AS a_id,
      b.id AS b_id,
    WHERE
      (b.id - a.id) > 1
  ) AS x
;

Alternatively, you could replace the inner join with an outer join in this manner:

WITH
  ...
SELECT
  CASE WHEN b.id IS NOT NULL THEN a.id END AS a_id,
  b.id AS b_id
FROM
  cte AS a
  LEFT JOIN cte AS b ON b.Rownumber = a.Rownumber + 1 AND (b.id - a.id) > 1
;

Note that if you are using SQL Server 2012 or later version, you can avoid the self-join entirely by using the LEAD function:

WITH cte AS
  (
    SELECT
      id = id,
      next_id = LEAD(id) OVER (ORDER BY id ASC
                               ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    FROM
      dbo.Test
  )
SELECT
  id,
  next_id
FROM
  cte
WHERE
  next_id - id > 1
;


What are the differences between these two database structures? Sorry if this question seems broad. These two database structures are based off the same thing made two different ways. I was wondering if there are advantages/disadvantages to the two.

ER diagrams document business rules. They are a visualization of many subject-verb-object sentences. Take the classic shopping cart example. Some rules may be

A customer places one or more orders.
An order consists of one or more items.
Each item is a purchase of exactly one product.

The nouns become entities (boxes in the ERD) and the verbs relationships (ERD lines and arrows or crows feet).

Your two diagrams represent different sets of business rules. For example, on the left there is no link between roster and case. The diagram is stating that, in the particular situation you are modelling, your users consider rosters and cases to be completely independent; neither places any constraint whatsoever on the other. The right-hand diagram, however, has a relationship between them. This states a case is somehow linked to a roster. The relationship name will document what that link is.

Because they are linked the right-hand system will be able to answer a whole class of queries the left-hand one cannot e.g. "which cases were processed during this roster?"

The general steps to develop an ERD are to identify the individual pieces of information required (a case number, a person's name) and the entity types these represent. Document the rules that link them, but only the rules that are meaningful in the context of the problem you're trying to solve. Determine the questions you want the system to answer and ensure it can do so. Reduce the entities to their normal forms. These steps often happen in parallel and cyclically.

In summary, the two diagrams represent two very different sets of business rules and sets of questions that can be answered. Only you can know which better solves your current problem.


Revoke access to selected system tables In Vertica, the SELECT privilege on all system tables in V_CATALOG and V_MONITOR is granted to PUBLIC. Therefore, by default all users can query them.  And not all system tables seem appropriate to be public.
For the most part, for each of the entries, the results appear appropriately limited to a users specific grants.   For instance, select * from v_catalog.columns; returns just columns for which the user has been granted  SELECT for the table and USAGE for the schema, and fortunately SELECT * FROM v_catalog.passwords; returns no rows at all.
But, some seem overly broad, such as SELECT * FROM v_catalog.users returns all users for all schemas.
Is there any downside to REVOKE SELECT ON v_catalog.users for a user intended to have only SELECT access to specific tables in a specific schema?  Or further revoke SELECT on V_CATALOG and V_MONITOR entirely for public users?
Limit access to specific database only, and restrict access to system tables

REVOKE SELECT ON v_catalog.users FROM public;
That should work. If you want more details check this: https://www.techonthenet.com/sql_server/grant_revoke.php