Stale table lock in AWS Aurora? We have an AWS Aurora (MySQL engine, r3.large) we are using to test a migration.

We have noticed a couple of queries that simply don't appear to run.  When I run

show engine innodb status;

it reports that there is a single table locked:

2017-06-21 18:20:49 2aea8928a700 Transaction:
TRANSACTION 6093969, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
16 lock struct(s), heap size 376, 6164 row lock(s), undo log entries 1
MySQL thread id 12219, OS thread handle 0x2aea8928a700, query id 8842803 10.127.0.24 root updating
delete from <tablename> where id=<id>
Foreign key constraint fails for table `mydb`.`<otherTable>`:

This is a java application.  I have stopped the tomcat instances, and verified that there are no active connections, but the lock does not clear up.

I looked at a couple of tables in information_schema (INNODB_LOCKS, INNODB_LOCK_WAITS, and INNODB_TRX) but there are 0 records.

Is this an aurora problem?  Any solution other than AWS Support?

I can't tell if this involves Aurora, but here is something worth noting: There are occasions when InnoDB will leave lock lying around. How does this happen ?

About six years ago, I answer this question : Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?. In that post I mentioned the following from the MySQL Documentation:

  When InnoDB performs a complete rollback of a transaction, all locks set by the transaction are released. However, if just a single SQL statement is rolled back as a result of an error, some of the locks set by the statement may be preserved. This happens because InnoDB stores row locks in a format such that it cannot know afterward which lock was set by which statement.

This is still in the MySQL 5.7 Docs on Deadlock Detection and Rollback
 (paragraph 3)

You stated in the question

  I looked at a couple of tables in information_schema (INNODB_LOCKS, INNODB_LOCK_WAITS, and INNODB_TRX) but there are 0 records.

In my answer to the 6-year-old question, I showed the query of this tabled used by a Percona Nagios Check plugin:

SELECT COALESCE(MAX(IF(p.command = 'Sleep', p.time, 0)), 0) AS idle_in_trx
FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS AS w
INNER JOIN INFORMATION_SCHEMA.INNODB_TRX        AS b ON  b.trx_id = w.blocking_trx_id
INNER JOIN INFORMATION_SCHEMA.INNODB_TRX        AS r ON  r.trx_id = w.requesting_trx_id
LEFT JOIN  INFORMATION_SCHEMA.PROCESSLIST       AS p ON  p.id     = b.trx_mysql_thread_id;

I tweaked that query to display the Process ID of the Blocking Transaction, the Process ID of the Requesting Transactions blocked and their respective values from INFORMATION_SCHEMA.PROCESSLIST:

SELECT COALESCE(MAX(IF(pb.command='Sleep',pb.time, 0)), 0) idle_in_trx,
pb.ID BLK_ID,pb.USER BLK_USER,pb.HOST BLK_HOST,pb.DB BLK_DB,
pb.COMMAND BLK_CMD,pb.TIME BLK_TIME,pb.STATE BLK_STATE,pb.INFO BLK_INFO,
pr.ID REQ_ID,pr.USER REQ_USER,pr.HOST REQ_HOST,pr.DB REQ_DB,
pr.COMMAND REQ_CMD,pr.TIME REQ_TIME,pr.STATE REQ_STATE,pr.INFO REQ_INFO,w.*
FROM       INFORMATION_SCHEMA.INNODB_LOCK_WAITS AS w
INNER JOIN INFORMATION_SCHEMA.INNODB_TRX        AS b  ON b.trx_id = w.blocking_trx_id
INNER JOIN INFORMATION_SCHEMA.INNODB_TRX        AS r  ON r.trx_id = w.requesting_trx_id
LEFT JOIN  INFORMATION_SCHEMA.PROCESSLIST       AS pb ON pb.id    = b.trx_mysql_thread_id
LEFT JOIN  INFORMATION_SCHEMA.PROCESSLIST       AS pr ON pr.id    = r.trx_mysql_thread_id
\G

If you run this, you will see one of two things:

The Process ID causing this log jam of locks
Everything coming up NULL (No transactions are blocked). This indicates that the lock is just stuck in LA-LA-LAND (Aurora is at fault). In that event, reboot RDS.


Cross join yields execution plan that reads one of the tables multiple times I have a query where I need to cross join a small table with 5 rows to a table with roughly 31k rows. The execution plan SQL Server came up with does index scans in both tables and joins them with Nested Loops (INNER JOIN). However, when checking the execution plan, I noticed that the scan on the smaller table produced 5 x 31k = 155k rows for the following step. Does that mean SQL Server is scanning the index of the smaller table 31k times?

Here is a small reproduction of the problem I'm having:

CREATE TABLE A (id INT PRIMARY KEY);

CREATE TABLE B (id INT PRIMARY KEY);

INSERT INTO A(id)
    VALUES (1), (2), (3), (4), (5);

INSERT INTO B(id)
    VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9)

SELECT * FROM A CROSS JOIN B

Here is the query live statistics of the SELECT command

My question is whether the index scan on the smaller table is performing multiple I/O operations, one for each row on the bigger table, as opposed to doing it only once and having its result being fed to the following step only once.

My question is whether the index scan on the smaller table is performing multiple I/O operations, one for each row on the bigger table, as opposed to doing it only once and having its result being fed to the following step only once.

Yes, for every row that comes out of A, you need to produce all of the rows for B. Since there are five rows in A, you make five trips to B.

Cross Joins can only be implemented with Nested Loops, and that's normal behavior for it.


Correcting a database that has had indexes and statistics created on it willy-nilly I've inherited a DB and I believe it was the victim of DBAs who encountered random slow operations, ran the tuning adviser for individual queries and blindly created the recommend indexes and statistics for said individual cases with no concern for the impact on the DB overall.
How do I remedy this situation?

I myself am not a DBA but am interested in doing this properly -

Should I drop all indexes and statistics and start over?
Is there a tool out there that can look at the entirety of SQL operations and recommend which indexes and stats to delete, update or create.
Any recommended standard procedures for this situation, I can't imagine I am unique in encountering this scenario

I'm on SQL Server 2012

Consider following the STOP pattern that's used in first aid training. Stop, think, observe and plan. That is, don't rush into action. Start by gathering some information, draft an action plan and only then start doing things. If there's an electricity accident, one doesn't touch the victim before cutting power. Likewise, one shouldn't start dropping indexes without knowing what use those would be.

Is the system working well enough? How can you tell? That is, what's the definition of "well enough?"

Do you have a set of business rules like "Report X must be ready within Y minutes" and "Daily ETL must be finished by 0600 as cube update starts at 0610." Maybe there's something like "Database backup must be finished at 0200, as disk-to-tape backup starts at 0300." Be aware there might be some ad-hoc rules like "CFO needs $stuff NOW" that are usually not documented at all.

If you don't have any business constraints, you need to discuss with your boss, other users and business about such constraints.

If you've got a set of rules, that's great news. Now, see if the system respects those limits at the moment.

After you know the rules for the system, maybe it would be a great idea to do some basic health check. Brent Ozar's sp_Blitz is widely used free tool. There's warnings if backups are not handled properly, lack of recent integrity checks, configuration issues and a lot more.


Is there a timeout option for remote access to PostgreSQL database? I'm working via pgAdmin III on a remote PostgreSQL database. After a not so long period (say 10-15 min.) without doing anything in pgAdmin, the connection automatically expires. I therefore have a error message asking if I want to re-connect. This takes about 10 sec. and the database structure collapses, so I have to re-open the schemas I had open before.

Is there a way to change a timeout parameter somewhere so prevent the connection to expire for a longer period of time?

A PostgreSQL server connection dropping after 10-15 minutes is almost certainly being caused by a state-tracking firewall (possibly using Network Address Translation (NAT)) between the client and the server. Many such firewalls have default timeouts of 15 minutes (900 seconds).

The three server-side parameters, tcp_keepalives_idle, tcp_keepalives_interval, tcp_keepalives_count are designed to help in these situations. See the documentation located here: http://www.postgresql.org/docs/9.2/static/runtime-config-connection.html#GUC-TCP-KEEPALIVES-IDLE

There are also client-side parameters for this: keepalives, keepalives_idle, keepalives_interval, keepalives_count, which you can set on connection. See the documentation located here: http://www.postgresql.org/docs/9.2/static/libpq-connect.html#LIBPQ-KEEPALIVES


DENY EXECUTE on one schema object hides other objects in same schema I have five stored procedures in a schema [XTR], the users are all in one AD group [AD\Users] and have db_datareader and public assigned to them. On one of the stored procedures I don't want to give them the right to execute the SP, so I used

DENY EXECUTE ON OBJECT::XTR.LoadData TO [AD\Users]

when now one of my users connects to the database ALL stored procedures are not visible anymore, the same goes for scalar valued functions (table valued functions they still see).

Did I get a concept totally wrong or why is denying execution on a single object having such effects on all other objects in the same schema ?

Since you didn't specify any other permissions you gave or denied to the user group AD\Users, I'll assume that they are memebers of db_datareader and public and have no other specific permissions.

To perform my test, I used SQL Server 2008, not R2 and started out with a completely new user group, database, schema and stored procedures.

As sysadmin

Created a Windows Group called AD\TEST_USERS in Windows

Created a login from that Windows Group in SQL Server

Created a test database TEST

Added AD\TEST_USERS as a member of public and db_datareader on the TEST database

Created a schema called XTR

Added two stored procedures sp_1 and sp_2 to XTR

As a member of AD\TEST_USERS

Opened up SSMS

Opened up the TEST database and expanded stored procedures.  Nothing was visible

As sysadmin

GRANT EXECUTE ON OBJECT::XTR.sp_1 TO [AD\TEST_USERS]
GRANT EXECUTE ON OBJECT::XTR.sp_2 TO [AD\TEST_USERS]

As a member of AD\TEST_USERS

I refreshed the stored procedures and now see XTR.sp_1 and XTR.sp_2

As sysadmin

DENY EXECUTE ON OBJECT::XTR.sp_1 TO [AD\TEST_USERS]

As a member of AD\TEST_USERS

I refresh stored procedures in SSMS and no longer see XTR.sp_1 but I do see XTR.sp2

Doing the same steps as you on my SQL Server 2008, simply denying EXECUTE on one stored procedure did not make the other stored procedure invisible in the same schema to members of my test group.


Difference between a unique clustered index and a clustered primary key? I'd like to ask two supplementary/follow-on questions, further to this previous/existing question: Is there any tangible difference between a unique clustered index and a clustered primary key?

That question starts with, "I understand that there may be a difference in meaning or intent between the two". I'm a programmer not a DBA, and this fundamental might be unclear to me: what is the difference in meaning or intent between the two?
My summary of the accepted answer, i.e. its most important statement IMO, is that it says, "I don't think there's any difference". If that's so then why did Microsoft implement "clustered indexes"? Why not just say instead, "It's always clustered on the primary key, and you should define as the primary key whatever you want it to be clustered on"?

It seems to me that a primary key (already) is a unique clustered index.

Furthermore, here's a specific problem by way of example.

Let's say I have a table of Users (with a userId as its primary key), and a second table (e.g. Items) which defines items owned by each user. A user can own many items; each item is owned by one user, and has an itemId.

So the itemId could be the primary key of the Items table; and each row in the Items table (which has an itemId) also has a userId to identify its owner.

That's a good way to define a 1-N relationship, isn't it? Assume a foreign key contraint on userId, with Users being the parent table.

At run-time I usually want to retrieve all the items owned by a user, therefore the Items table should be clustered on its userId column.

[Users]
  userId
  + plus other user-specific fields

[Items]
  userId
  itemId
  + plus other item-specific fields

I think there are two ways to define this Items table:

itemId is primary non-clustered key, and (userId,itemId) is unique clustered index

... or:

(userId,itemId) is primary clustered key, and itemId is unique non-clustered index.

Which of the above two is better or more correct, semantically and/or practically, and why?

In case it makes a difference, the itemID is an artificial key: its purpose is to disambiguate/identify the item (and/or identify the item, within the set of items owned by the user).

'Physically' the itemID is probably globally unique (or unique within the table, anyway): because databases make it easy to create a globally-unique artificial key.
'Logically' I wouldn't mind if it were not-globally-unique, but were instead only unique-within-each-user, such that I needed both userId and itemId to uniquely identify an item, i.e.

(userId,itemId) is primary clustered key and itemId is unique non-clustered index.

So I think it isn't altogether wrong to see (userId,itemId) as a composite primary key?

Apparently it's fine and normal to use "two separate attributes" as the primary key of an associative table when it's an N-to-N relationship. Is it wrong (e.g. harmful in some way, for some reason) to use two attributes as the primary key of an object in a 1-to-N relationship? Is it wrong to say that the owner ID is part of the object's identity?

why did Microsoft implement "clustered indexes"? Why not just say instead, "It's always clustered on the primary key, and you should define as the primary key whatever you want it to be clustered on"?

To answer this question: The primary key on the table does not have to be the Clustered Index Key. A Clustered Index is the place where SQL Server stores the data of the entire table. The Index itself has what are called "key values." Key Values (1 to many) define how the index is ordered, along with statistics and other useful things.

In fact, often it's useful to have a Primary Key that is not the Clustered Index Key. For example, some applications create their own keys or GUIDs in order to relate different tables through foreign keys. Using a GUID as a Clustered Index Key is a bad idea though, since GUIDs don't always have an order, unless you create them sequentially. To solve this, you could create a Clustered Index on an identity column, and place the Primary Key on the GUID column.

Your question has many more questions. I think we need to break this down into many questions, since you're asking about a lot of index concepts.


`chown -R` equivalent for PostgreSQL We occasionally have the problem that, when upgrading some software and database, inexperienced (or overworked, not excluding myself) sysadmins (we don’t have separate DBAs except for Oracle) run the DB upgrade scripts as user postgres instead of the correct application user.

Update: emphasis added. Our normal mode of operation, as documented, involves running those scripts as regular user. This question is asked to clean up for those cases when that procedure is accidentally not followed.

How can I change the ownership of all objects in a (one) database, no matter whom they belong to, to a specific user?

The methods described here don’t work for me, because:

REASSIGN OWNED apparently is per-cluster, not per-database (too broad)
REASSIGN OWNED does not work for objects owned by postgres
the manual enumeration from @AlexSoto’s answer requires one to know the types of the objects involved beforehand

I really want basically the equivalent of the Unix chown -R newowner /path command, just for a whole PostgreSQL database (all schemata, tables, indicēs, etc. in it).

Minimum PostgreSQL version involved is 9.1 (Debian wheezy), maximum whatever Debian unstable has at any given time.

My answer doesn't answer your question directly, but addresses your method of operation.

You shouldn't be logging in as postgres, just like you shouldn't be logging in as root on your servers. On your servers you should be logging in as real users and be using sudo for the odd occasion that you need superuser privileges. And preferably sudo su - <other_user> rather than just doing everything directly using sudo <command>.

In PostgreSQL we can implement more fine-grained policies.

Create a role structure something like:

create role admin noinherit;
grant <owner> to admin;
grant admin to <real_person>;

Then login as <real_person>.

To your scripts to deploy into <owner>, add the first line:

set role <owner>;

Then you should never have the problem again that you describe in your question.

And you never need to login as a superuser -- unless you need to create new schemas or users.


Query the last records by date, where condition is true A made this query, which is works perfectly now:

SELECT t.DocID, t.RevID, t.EffDate
FROM tblRev AS t
INNER JOIN (
    SELECT DocID, max(EffDate) AS MAXEffDate
    FROM tblRev GROUP BY DocID
) AS tm ON (t.EffDate = tm.MAxEffDate) AND (t.DocID = tm.DocID);

Basically in my document database, All documents have revisions (1.0, 1.1, 1.2 etc.). I could query the last revisions by date, but there is an tblRev.OK column (true/false) in a table.

I need to insert a WHERE statement to query the last Revision where tblRev.OK=true.

For example(table):

 DocID - RevID - RevDate - OK
   1   -  1.0  -  jan 1  - true
   1   -  1.1  -  feb 2  - true
   1   -  1.2     march3 - false

So I wanted this line: 1   -  1.1  -  feb 2  - true

I can do it if i made query to list al Revs where=true, but I should do it in one query if its possible.

There are various ways to do this kind of queries (greatest-n-per-group) which also differ per DBMS (in syntax and performance) but the standard SQL you have will work in almost all products.

Without changing the structure of your query, you just need to add the condition in both the external and the internal subquery:

SELECT t.DocID, t.RevID, t.EffDate
FROM tblRev AS t
INNER JOIN (
    SELECT DocID, max(EffDate) AS MAXEffDate
    FROM tblRev
    WHERE OK = TRUE
    GROUP BY DocID
) AS tm ON (t.EffDate = tm.MAxEffDate) AND (t.DocID = tm.DocID)
WHERE t.OK = TRUE ;

If there is a UNIQUE constraint on (DocID, RevDate), the external check (WHERE t.OK = TRUE) is not needed.

Minor (improvement or obfuscation?): the WHERE OK = TRUE can be shortened to WHERE = OK, since OK holds boolean values.


Using LOCATE() Function as SUBSTRING() parameter gives ramdom result. Extracting string from text I have several rows with a lot of content and i'm trying to find some code and extract it. It looks like this:

Some code...

&nbsp;

[flv:http://submarino-libre.s3.amazonaws.com/opciones/basico/webready/Video1CursoOpcionesbasicoWebReady.mp4 http://www.submarinobursatil.com/imagenes/portadas/curso-de-opciones.png 600 369]

Duraci&oacute;n: 09:56

Some other code...

Figured out that I need to find [flv: and extract all the line (2 links) ending with ]

So i came out with this query:

SELECT SUBSTRING(`post_content`, LOCATE('[flv:',`post_content`), LOCATE(']',`post_content`)) AS amazonlinks
FROM  posts

First LOCATE function works fine, it finds [flv: but second LOCATE functions doesn't stop the query extraction on ]. It gets a lot more code from the next lines. It only works when i set a number but there is no way i can know the exact lenght number.

Is it possible to use 2 LOCATE functions as parameters of SUBSTRING ? Or what am i'm doing wrong?

UPDATE: Adding DBFiddle:
https://www.db-fiddle.com/f/m62LBUStp1UbRCk2eVNRrY/0

Substrings first argument is the offset, the second argument is the number of characters from the offset. You need to subtract the offset from the second parameter:

SELECT title
     , SUBSTRING(`content`
                -- offset in string
                ,LOCATE('[flv:',`content`)
                -- number of characters from offset
                ,LOCATE(']',`content`) - LOCATE('[flv:',`content`) + 1 )
FROM amazonlinks

I added +1 to include "]" in the result


Expand a range columns into a single column I'm thinking this is an easy query but I don't know how to start. I have a database table called Booklet with columns id, controlNoFrom and controlNoTo. Sample data looked like this:

id       | controlNoFrom | controlNoTo
---------+---------------+-------------
20150105 |             1 |         100
20150106 |           101 |         150
20150107 |           151 |         225
---------+---------------+-------------

What I want is a query that can present the data like this:

id       | controlNo
---------+-----------
20150105 |         1
20150105 |         2
20150105 |         3
...      |       ...
20150105 |        99
20150105 |       100
20150106 |       101
20150106 |       102
...      |       ...
20150106 |       149
20150106 |       150
20150107 |       151
20150107 |       152
...      |       ...
20150107 |       224
20150107 |       225
---------+-----------

It's easy if it is the other way around, but I don't know what kind of SQL to be used in this scenario. What would you call this approach and what would the SQL statement in OracleDB look like?

You could use some kind of row generation technique, for example:

select rownum - 1 as q from dual connect by level <= (select max(controlnoto - controlnofrom) + 1 from booklet)

This generates as many rows as the largest range requires, with data 0, 1, 2, ..., starting with 0, so no correction needed, when we add these values to controlnofrom. Now simply join this to your original table and limit the number of rows by controlnoto - controlnofrom, finally add the row number to controlnofrom:

with booklet as
(
  select '20150105' as id, 1 as controlnofrom, 100 as controlnoto from dual union all
  select '20150106', 101, 150 from dual union all
  select '20150107', 151, 225 from dual
)
select
  b.id as id, b.controlnofrom + r.q as controlno
from
  booklet b
  join (select rownum - 1 as q from dual connect by level <= (select max(controlnoto - controlnofrom) + 1 from booklet)) r on (r.q <= b.controlnoto - b.controlnofrom )
order by
  id, controlno
;


System disk run out of space when running heavy SQL queries on SQL Server 2012 I'm quite new to SQL Server 2012, I would be grateful if someone can help.
I have restored a copy of a huge database to SQL Server 2012 and I tried to run some simple queries against it.

I'm trying to run a SELECT query against a database table of 136898115 lines. This SELECT query only has a simple WHERE clause. Every time I run this query, it fails because the system disk (the partition where Windows is installed - C:\) run out of space (this partition has only 6GB free space), and I don't understand why. I defined my tempdb to be on a different drive, which has more than 14 terabytes of free space. Of course that my database is located on a different drive too.

What makes my system partition run out of space? Is it the page file?

SSMS query results cache to the C: drive by default. Go to Tool \ Options. See attached. Change this to another volume with more storage and you should be fine.


Extract filename without extension from the absolute location I'm trying to get a filename contained in the value of a specific column of my table. My table looks like this:

absolutel_path
\\Path\filename.extension

I need to extract the filename (in the above filename) from the absolute_path (\\Path\filename.extension). Which function should I use to get my filename (substring) out?

Edit:

Even though my first solution answered the question as asked, I saw @DavidBoho 's answer and he made several good points. He suggested that if the filename is my_file.tar.gz then the return value should be my_file.tar and also that my solution would fail in the event that the file had no extension at all. All of the code here is available on this fiddle.

Given the table and data as follows:

CREATE TABLE with_filename
(
  file_id INTEGER,
  file_name VARCHAR (256)
);

populate:

INSERT INTO with_filename
VALUES
(1, '/users/mcm1/ualaoip2/vmm/file1.pdf'),
(2, '/users/mcm1/ualaoip2/vmm/file2.py'),
(3, '/users/mcm1/ualaoip2/vmm/file3.pdf'),
(4, '/users/mcm1/ualaoip2/vmm/file4.c'),
(5, '/users/mcm1/ualaoip2/vmm/file5.java'),
(6, '/users/mcm1/ualaoip2/vmm/file6.class'),
(7, '/users/mcm1/ualaoip2/vmm/file7'),
(8, '/users/mcm1/ualaoip2/vmm/file8.tar.gz'),
(9, '/users/mcm1/my_prog.cpp');

My original solution:

SELECT LEFT(
      RIGHT(file_name, POSITION('/' IN REVERSE(file_name)) - 1),
                       POSITION('.' IN
      RIGHT(file_name, POSITION('/' IN REVERSE(file_name)) - 1)) - 1
) AS my_file
FROM with_filename;

gives the result:

my_file
file1
file2
file3
file4
file5
file6
file       -- << should be file7
file8      -- << should be file8.tar
my_prog

After reading @DavidBoho's post, he used the SPLIT_PART function to resolve the problems with files 7 & 8 - see the fiddle. I decided to look again at my own SQL and I came up with this (perhaps more traditional?):

SELECT
      REPLACE(SUBSTRING(file_name, (LENGTH(file_name) + 2) - POSITION('/' IN REVERSE(file_name))),
      RIGHT(file_name, POSITION('.' IN LEFT(REVERSE(file_name), POSITION('/' IN REVERSE(file_name)) - 1))),
      '') AS the_files
    FROM with_filename

Result:

the_files
file1
file2
file3
file4
file5
file6
file7
file8.tar
my_prog

which is also the correct answer!

While I was grasping for a solution, I became interested in regular expressions as a means of solving this problem. Even though I (we) was/were able to solve this using "traditional" SQL, it became clear to me that regexes are extremely powerful and even though SQL is now Turing complete, it could rapidly  become extremely convoluted for relatively simple string manipulation  problems, so I decided to investigate.

I found two regex solutions - in fairness, I can't claim to have done this myself, the solutions are as a result of a question I asked on StackOverflow. So, the regex solutions are as follows:

The best one is this one -

SELECT
    file_name,
    REGEXP_REPLACE(file_name, '^.*/([^/]*?)(\.[^/.]+)?$', '\1') AS filename
FROM with_filename;

There is a second one, but IMHO (and that of the original author) it's a bit of a hack - it involves two nested REGEXP_REPLACEs

SELECT
    file_name,
    REGEXP_REPLACE(REGEXP_REPLACE(file_name, '^.*/(.*)$', '\1'), '\.[^.]+$', '') AS filename
FROM with_filename

Finally, there may be a solution possible using the UNNEST and the STRING_TO_ARRAY functions together - I came up with this code:

SELECT fn,
    LEFT(fn, POSITION('.' IN fn) - 1) AS lef
FROM with_filename w,
  UNNEST(STRING_TO_ARRAY(w.file_name, '/')) AS fn
GROUP BY fn
HAVING COUNT(fn) = 1
ORDER BY lef;

which gives the result:

fn  lef
file7   file       -- << should be file7
file1.pdf   file1
file2.py    file2
file3.pdf   file3
file4.c     file4
file5.java  file5
file6.class     file6
file8.tar.gz    file8  -- << should be file8.tar
my_prog.cpp     my_prog

I tried lots of different permutations with this, but couldn't get it to work. Would be grateful for any input! :-)

Another interesting function is REGEXP_SPLIT_TO_TABLE.

SELECT
  fn,
  COUNT(fn)
FROM
(
  SELECT REGEXP_SPLIT_TO_TABLE(w.file_name, '/') AS fn
  FROM with_filename w
) AS sq
GROUP BY fn
HAVING COUNT(fn) = 1
ORDER BY fn

Result:

fn  count
file1.pdf   1
file2.py    1
file3.pdf   1
file4.c     1
file5.java  1
file6.class     1
file7   1
file8.tar.gz    1
my_prog.cpp     1

Again, this might be worth pursuing - didn't have time.

Example DML/DDL

CREATE TABLE with_filename
(
  file_id INTEGER,
  file_name VARCHAR (256)
);

INSERT INTO with_filename
VALUES
(1, '/users/mcm1/ualaoip2/vmm/file1.pdf'),
(2, '/users/mcm1/ualaoip2/vmm/file2.py'),
(3, '/users/mcm1/ualaoip2/vmm/file3.pdf'),
(4, '/users/mcm1/ualaoip2/vmm/file4.c'),
(5, '/users/mcm1/ualaoip2/vmm/file5.java'),
(6, '/users/mcm1/ualaoip2/vmm/file6.class'),
(7, '/users/mcm1/ualaoip2/vmm/file7'),
(8, '/users/mcm1/ualaoip2/vmm/file8.tar.gz'),
(9, '/users/mcm1/my_prog.cpp');


How to set statistics level of a table in postgres? I know how to set statistics level for a particular column i.e
alter table table_name alter column column_name set statistics 100;

However, if I wish to set statistics level for a particular table or for a particular schema, is there is one liner command?

There is nothing ready-made for a table or schema, because that is seldom useful.
But you can set it for a whole database with
ALTER DATABASE mydb SET default_statistics_target = 1000;

You can also use psql's \gexec to set the statistics target for all columns of a table:
SELECT format(
          'ALTER TABLE %s ALTER %I SET STATISTICS 1000',
          attrelid::regclass,
          attname
       )
FROM pg_attribute
WHERE attrelid = 'pgbench_tellers'::regclass
  AND attnum > 0 \gexec


Single collection with a key vs multiple collections I am designing the DB for a project, where, I am sure that I would not be querying for data belonging to multiple values of a key x at the same time.  All queries will be for sure for a given value of x.  Is it better to create a single collection having an index for x, or is it better to create multiple collections having x as part of the collection name?

I want to optimize storage space, and well as ensure reasonable read query performance.

Answer is single collection at this case.


Fetch all parents from child id I created a table with name "vegs" and inserted values just like this.

    id  name            parent
--------------------------------------
    1   fruits          0
    2   Apple           1
    3   Orange          1
    4   Grape           1
    5   Green Apple     2
    6   Red Apple       2

I want to recursively fetch all parents of the child  "6".

Recursive queries cannot be done with pure SQL when it comes to MySQL.

I have written posts on how to use Stored Procedures to accomplish this

Oct 24, 2011 : Find highest level of a hierarchical field: with vs without CTEs
Dec 10, 2012 : MySQL: Tree-Hierarchical query
Jul 15, 2013 : Get top most parent by nth child id?

Give them a Try !!!

NOTE : This post, originally May 24, 2014, predates MySQL 8.0, which now has CTEs.


Launching a python script from an insert trigger We have a nice piece of python that sends some emails and interacts with a cloud system. Works fine. But we have to fire it every few minutes to poll the db. We really need, for business purposes, to have the python script fire in real time, so there is no polling delay. (This serves sales people who are on the phone with customers.)

We really do not want a 1 minute polling loop. Or 30 seconds. We want the record to show in the db and for things to happen right away.

The fast way to make this fly is to have it fire when a specific record type is inserted into a table.

Can we fire a python script from a trigger?

Per Aaron's note below, we know that this is a Very Bad Thing™, but this table gets very very little use (0-12 inserts a day). Polling the table fails to meet our business need (we need the .py to run immediately -- it does much more than send an email).

We believe a way to meet our business need is to set up the .net version of python on the SQL Server, and then have T-SQL call the python script the way it calls C# stuff... but we have no idea how to actually do this! (ergo this question).

Docs/details?

I asked a follow-up question on Stack Overflow: How do I create a Python CLR procedure in SQL Server?

The question under the question: You have a piece of python. You want it to fire from a SQL trigger, but you know that is a Very Bad Thing. So what do you do to actually accomplish the same effect without having python code in the middle of a SQL operation?

What is the non-trigger, non-polling approach to solving this need?

(The same effect = "insert/update/delete happens in a table and a python script is triggered within 2 seconds of the db event, without polling the table")

Don't make your user transaction wait for the (hopefully!) successful completion of the Python script. Your entire transaction sits there and waits for this external process to run, try to send mail, etc. I doubt the e-mail really has to go out that instant - especially given you can't control any delays it has as it gets routed to the recipient's inbox anyway. Why not just run the process more frequently, if timing is so important?

Please give this tip a look-through.

If you really, really, really want to do this the wrong way, you can just enable xp_cmdshell and fire away.

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

Now, assuming the user has access to xp_cmdshell and/or the SQL Server service account can see the folder where the python script is stored, you should be able to do this from within your trigger:

EXEC master..xp_cmdshell N'C:\Python27\python.exe C:\source\NotifyAgents.py';

As an aside, you should state in your question that you aware that this is a very bad thingTM, but you are not concerned with that, for whatever reason. I still don't think you're going to get as real time as you expect, even if you do fire this from the trigger. Have you considered database mail instead of python?


Can replication be set up when already mirroring? We currently use SQL Server 2012 Standard Edition and we mirror from one SQL Server instance to another for automatic failover purposes.

I want to replicate the database which is mirrored, to another SQL Server instance, for reporting purposes.  The replication will need to be as close to real time as possible.

Questions

Can you have SQL Server replication working alongside mirroring?
If so, how does it function and does it have constraints / impact on the mirroring?

You can absolutely setup transactional replication on mirrored publishing databases. It is fully supported, and Microsoft has some good content on getting it up and running.

The only caveats are the ones that already exist for transactional replication, and the recommendation to have the distributor database on another server outside of the mirrored pair.


Sum of values for each row SELECT
ratings.programme_id,
programme.programme_name,
ratings.ratings
FROM
programme
INNER JOIN ratings ON ratings.programme_id = programme.programme_id

I have two tables a and b

a has id and name and b has id and ratings. There can be multiple ratings for each id in a. I want to calculate the sum of ratings for each id.

for example entry john has received ratings of 1,3 and 5 and I would like to get the sum which would be 9

To answer this, I did the following.

mysql> CREATE TABLE myid(id INT, myname VARCHAR(10));
Query OK, 0 rows affected (0.35 sec)

mysql> INSERT INTO myid VALUES (1, 'Paul');
Query OK, 1 row affected (0.06 sec)
mysql> INSERT INTO myid VALUES (2, 'Peter');
Query OK, 1 row affected (0.07 sec)

mysql> CREATE TABLE myrating(ratename VARCHAR(10), therating INT);
Query OK, 0 rows affected (0.43 sec)

mysql> INSERT INTO myrating VALUES('Paul', 3);
Query OK, 1 row affected (0.05 sec)
mysql> INSERT INTO myrating VALUES('Paul', 4);
Query OK, 1 row affected (0.04 sec)
mysql> INSERT INTO myrating VALUES('Peter', 6);
Query OK, 1 row affected (0.06 sec)
mysql> INSERT INTO myrating VALUES('Peter', 456);
Query OK, 1 row affected (0.06 sec)

And then performed the query

SELECT myid.myname, SUM(myrating.therating) AS The_Rating
FROM myid inner
JOIN myrating
ON myrating.ratename = myid.myname
GROUP BY myid.myname;

Obtaining the result:

+--------+------------+
| myname | The_Rating |
+--------+------------+
| Paul   |          7 |
| Peter  |        462 |
+--------+------------+
2 rows in set (0.04 sec)

Does this satisfy your requirements?


Troubleshoot corruption in SQL Server Database I have been asked by my interviewer "How do you troubleshoot corrupted data in SQL in SQL Server 2012?"

Well, right off I would verify with the person asking the question that they are referring to corrupted data and not database corruption, there is a difference. I would also determine who was interviewing me and base the answer on that (e.g. non-DBA would not care about the details, where a DBA Manager or something could require me to provide more details).

For somewhere in the middle...

Corrupt Data

With corrupt data entering the database it would be based on how quickly it was caught and likely how wide spread it is (isolated to a table, or effects multiple tables). If a single table was involved it could be as easily as restoring a copy of the database and replace the data in the table. If the database is large this may not be so easily, or if multiple tables are involved, require recreating the data. The focus would be in designing of the application to put in controls that prevent this from happening to begin with because of the pain it can cause, and possibly be unrecoverable.

Database Corruption

Database corruption would mean the database as a whole or underlying structure of the database is damaged. I would start out taking a full backup of that database in its current state before starting anything. I would start out reviewing the previous DBCC CHECKDB output since I would have had that information readily available for systems I manage. Reviewing that output would indicate what level of severity I am dealt.

I would want to determine and ensure the underlying disk the database files reside on are in good condition and may not have been the cause of the issue.

I would then start re-reading post from folks like Paul Randal on corruption recovery to ensure I fully understood the liability of my actions, before doing anything. In a last ditch effort and if the tables that are found having the problem have data that can be recreated easily, I would issue the DBCC CHECKDB WITH ALLOW_DATA_LOSS if it was in a critical timeframe. Outside of that it is time to prepare for the restore process.

I would also go into small rant that alerting and ensuring backups are good, and meet the business needs, would ensure the company is ready to easily handle this type of issue. If my backups are current and good, I would just start the restore process to another database, while I was trying to recover the corruption. This would allow me to have a "backup" plan if the recovery steps above did not work. It is all really based on the criticality of the system.


Changing Data Directory on MySQL 8 Need: To store / accesses dbTable data from a separate drive not the default

I was using MySQL 5.7 so far & as specified in the solution - Link1 I added the my.ini file with the info as stated & it worked fine.

I now uninstalled MySQL 5.7, installed MySQL 8, created a my.ini file & specified the info as stated in this solution Link2. But when I check it still points to the default C:\ drive & hence unable to access data from the existing database stored in E:\ drive

SHOW GLOBAL VARIABLES LIKE 'datadir';

shows

C:\ProgramData\MySQL\MySQL Server 8.0\Data\

The new my.ini file which I have created as below in

C:\Program Files\MySQL\MySQL Server 8.0

[mysqld]
datadir = E:/MySqlData
secure-file-priv="E:/MySqlData/Uploads"

Hope the issue faced is clear, await inputs.

Edit:

I found this input - Link Could anyone let me know if this could be a reason ? Also any known hex editors for Windows 10 ?

Update: Even after removing the hex characters, the server still does not start if I point the parameter datadir in my.ini file to my database folder in another drive

Make sure the directory where you want to write the data files is accessable for MySQL. I put it the security settings on Everyone.

You might need to copy the data also from old data directory to the new one.


How can I list all tables without a primary key? I've seen a ton of queries to list primary and foreign keys but how can I query for tables missing a primary key?

To list all tables without a primary key, you can use this:

select tbl.table_schema,
       tbl.table_name
from information_schema.tables tbl
where table_type = 'BASE TABLE'
  and table_schema not in ('pg_catalog', 'information_schema')
  and not exists (select 1
                  from information_schema.key_column_usage kcu
                  where kcu.table_name = tbl.table_name
                    and kcu.table_schema = tbl.table_schema)

It's not clear to me if you want to find tables that are not referencing any other tables or tables that are not referenced by other tables. But both things can be obtained by querying information_schema.referential_constraints in a similar way as the above query.


PostgreSQL corrupted after running pg_resetxlog We are using PostgreSQL version 9.3 on Ubuntu 14.04. This PostgreSQL server shared among all our application servers (Odoo), so we made it run on the separate environment.

On Saturday we found disk full issue on this DB server. On our further investigation, we found the backup server(barman) is went off. So all the archive log stay on the database server. This occupied the entire disk. Our database backup server may be stopped working a month back.

By googling we found a solution, that by resetting the pg_xlog files to solve this problem. So we clean the log file using pg_restxlog command. As the forum said the disk cleared and we reboot the server. But no database found :-(. We listed using psql - list command. Nothing worked till now. We are not able to restore the backup from barman too. Then we continue our investigation and we found all the data stay safe under the base folder of the main data path of Postgres.

The steps we execute to reset the log follows.

Try to stop the database server

sudo service postgresql stop
Logged in as Postgres user

sudo su - postgres
Run the reset command.

/usr/lib/postgresql/9.3/bin/pg_resetxlog -f /var/lib/postgresql/9.3/main/
Disable the barman configuration in postgres.conf file to stop the backup process for while.
And reboot the server

File content of /var/lib/postgresql/9.3/main/

postgres@server2:~$ du -h 9.3/main/
12K     9.3/main/pg_notify
28M     9.3/main/base/2735749
73M     9.3/main/base/4172290
46M     9.3/main/base/4410494
81M     9.3/main/base/3002089
43M     9.3/main/base/4282962
47M     9.3/main/base/3377227
130M    9.3/main/base/4098067
44M     9.3/main/base/1682791
58M     9.3/main/base/3377231
4.0K    9.3/main/base/pgsql_tmp
6.1M    9.3/main/base/12030
41M     9.3/main/base/4280118
54M     9.3/main/base/3149391
45M     9.3/main/base/4202614
49M     9.3/main/base/3344071
45M     9.3/main/base/2985056
51M     9.3/main/base/2120822
18G     9.3/main/base/3655712
25M     9.3/main/base/2759574
40M     9.3/main/base/4388978
52M     9.3/main/base/2435773
53M     9.3/main/base/4236740
55M     9.3/main/base/3386464
6.2M    9.3/main/base/12035
201M    9.3/main/base/4112218
54M     9.3/main/base/1625789
635M    9.3/main/base/149656
40M     9.3/main/base/4190162
25M     9.3/main/base/4090019
150M    9.3/main/base/4338686
6.2M    9.3/main/base/1
86M     9.3/main/base/2101485
185M    9.3/main/base/3453985
48M     9.3/main/base/4244883
41M     9.3/main/base/4160039
47M     9.3/main/base/3377180
38M     9.3/main/base/4150310
8.9G    9.3/main/base/2926431
47M     9.3/main/base/1693701
28M     9.3/main/base/4153341
25M     9.3/main/base/2744130
74M     9.3/main/base/2023404
29M     9.3/main/base/3231291
28M     9.3/main/base/2749185
43M     9.3/main/base/4371923
47M     9.3/main/base/3410953
47M     9.3/main/base/4313961
50M     9.3/main/base/4399246
49M     9.3/main/base/3402258
84M     9.3/main/base/3379836
64M     9.3/main/base/2777796
30G     9.3/main/base
5.8M    9.3/main/global
88K     9.3/main/pg_multixact/offsets
256K    9.3/main/pg_multixact/members
348K    9.3/main/pg_multixact
4.0K    9.3/main/pg_xlog/archive_status
33M     9.3/main/pg_xlog
100K    9.3/main/pg_stat_tmp
4.0K    9.3/main/pg_serial
4.0M    9.3/main/pg_clog
4.0K    9.3/main/pg_stat
52K     9.3/main/pg_subtrans
4.0K    9.3/main/pg_tblspc
4.0K    9.3/main/pg_twophase
4.0K    9.3/main/pg_snapshots
30G     9.3/main/

I guess it is too late now. If you use pg_resetxlog, you need to be extremely careful and, most importantly, know what you are doing.

Thanks to PostgreSQL's robustness, all you had to do in that case was to free space in the Barman server, for example by deleting the oldest backup in the catalogue. Then, once space was reclaimed, PostgreSQL could have resumed shipping WAL files and automatically recovered.

I know it is too late for you, but I am hoping that my reply will be able to help somebody in the future and prevent them from running pg_resetxlog.


Attempt to fetch logical page (5:65424) in database 2 failed I am getting the following SqlException on calling a stored procedure:

  Attempt to fetch logical page (5:65424) in database 2 failed. It belongs to allocation unit 7349876362857938944 not to 4899918190390149120.

  System.Data.SqlClient.SqlException occurred
  Message="Attempt to fetch logical page (5:65424) in database 2 failed. It belongs to allocation unit 7349876362857938944 not to 4899918190390149120.

  Source=".Net SqlClient Data Provider"
  ErrorCode=-2146232060
  Class=21
  LineNumber=257
  Number=605
  Procedure="ispDisplayCount"
  Server="10.10.1.1"
  State=3

What does this exception mean? Is there any resolution to the above issue?

Although the database referenced in the error above indicates tempdb, similar errors referencing Message 605, may be fixed using the answers below.

  Msg 605, Level 21, State 3, Line 1
  Attempt to fetch logical page (1:8687634) in database 7 failed. It belongs to allocation unit 72057594364821504 not to 72057594052476928.

If the database ID indicated by the error message is 2, the database affected is tempdb.  One method for fixing this type of corruption in tempdb is to simply restart the SQL Server instance.  For database ID's other than 2, follow the recommendations below.

You may need to restore from backup, however try this:

As a member of the sysadmin role, execute DBCC PAGE (2, 5, 65424, 3); and look for the Metadata: IndexId value.

If it is 0 (heap) or 1 (clustered index) you need to restore from backup.
If it is not 0 or 1, you can just rebuild the non-clustered index.

Run the DBCC command, and let us know what you find.  Check https://www.mssqltips.com/sqlservertip/1578/using-dbcc-page-to-examine-sql-server-table-and-index-data/ for details about the DBCC PAGE command


Many-To-Many relationship between Students and heights (Is it necessary?) So I'm trying to design a database for my local school doctor. (This is somewhat analogous to my real problem)
Currently, a student is scheduled for a health check every half year, where the students height is measured.
As it can be seen, one student therefore may have multiple heights, which is signified by a join table, i.e. Student_Height.
To me, this seems like the best way to associate multiple heights to One student. The only downsite is though, that since we may have 1000 students, it is pretty certain that the same height will be measured multiple times - Is this bad practice, and is it overkill to have an extra table for this?
As I see it, this issue could alternatively be solved in two ways:

Pre-populate the height table with heights (i.e. 1, 1.1, 1.2, 1.3, ... 2.9,3.0 ... 180.0 cm).
Put height as a column in the student table, and let it be a Varchar(Max) - Meaning, it could be a comma-separated string of heights. (I don't like this - since it limits/complicates my PowerBi possibilities)

Please let me hear your thoughts on this. Are my thoughts above justified?

Height is not an entity. A student can't have multiple heights, unless your measurements are sometimes incorrect. Unless there's been some time between measurements. Measurements could be an entity, identified by time_taken and student_id, attributes height, weight, and things perhaps too private


Performance Issue after Truncate and Load of a table in Oracle One of our clients has migrated from old to new (larger size/resources) servers. A process (X) is taking 4 hours extra whenever we are doing a truncate and full load of a specific table. If we do not truncate-load this table X doesn't take long to run. In process X we are inserting, updating and reading data from this table into numerous temp tables and then using these temp tables further.

I would like to know:

Best practices whenever a truncate and full load is performed.
What steps can we take from our side on this specific table?
Does the database migration to another server bring such issues?
What debugging should I do? I am gathering stats with cascade after the full load on this table, then running the process X.
Is it recommended to update any Oracle parameters on moving from Oracle 12c from Oracle 11g or 10?

Sorry for being very generic. The exact usage of the table in process X is complex and difficult to mention here. Please advise.

Other details:
Table Structure is as follows: - Around 55 columns -PCT_FREE    10 PCT_USED INI_TRANS   1 MAX_TRANS 255 INITIAL_EXTENT  65536 NEXT_EXTENT   1048576 MIN_EXTENTS 1 MAX_EXTENTS   2147483645 PCT_INCREASE FREELISTS   FREELIST_GROUPS LOGGING YES BACKED_UP   N NUM_ROWS 4064125

Typical weekly full load inserts around 2-3Million records

I am doing gather stats to the table and other related tables as well, with following options: dbms_stats.gather_table_stats(ownname=>'schema_x', tabname => 'Table', estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all columns size auto', cascade=>TRUE, DEGREE=>7)
Table has two Normal Unique index and one Normal Non-Unique index defined

Loading Truncated Table

Assume staging table is X_SOURCE, and target table to load is X_TARGET, and any calculations performed when loading X_TARGET can be done via SQL functions or user-defined functions.  Here we load col_a as is, inserting current date into col_b, col_c is multiplied by 2 times, and col_d as is.

alter table x_target nologging;
alter session enable parallel dml;
-- possibly drop indexes

insert /*+ append parallel(6) */ into X_TARGET
   (select col_a, sysdate, col_c * 2, col_d from X_SOURCE);
-- recreate indexes if they were dropped
alter table x_target logging;

-- then invoke RMAN commands to back up the table

The above has the following features:

Turn off logging on the target table x_target to avoid logging
overhead
The /*+ append */ hint appends data to the end of the table
and bypasses several steps done during normal inserts
Dropping and recreating the indexes can be much quicker than doing index
maintenance during the load.
Don't forget to turn logging back on
Ensure a backup is done after the load

New Server Issues

If the new server has more CPU/memory/etc. and you are seeing longer run time, I would definitely run the DBMS_STATS.GATHER_SYSTEM_STATS('START', ...) procedure, (after creating a table to store the stats being gathered), run your truncate/load process, then run DBMS_STATS.GATHER_SYSTEM_STATS('STOP')  and then query your stats table, and use DBMS_STATS.SET_SYSTEM_STATS to set values that need to be adusted -- see Performance Tuning Guide for details.  If you are on Oracle 12, also look at DBMS_STATS.GATHER_PROCESSING_RATE -- run it too while your process is running.


SQL Server Management Studio 18 won't open (only splash screen pops up) I just installed SSMS 18 GA on a computer with only VS2019 installed, and when I try to open SSMS the splash screen will come up, but then the process exits.

Running ssms with the -log parameter reveals an error message:

  CreateInstance failed for package [Task Scheduler Package]Source:
  'mscorlib' Description: Could not load file or assembly
  'Microsoft.VisualStudio.Shell.Interop.8.0, Version=15.0.0.0,
  Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its
  dependencies. The located assembly's manifest definition does not
  match the assembly reference. (Exception from HRESULT: 0x80131040)
  System.IO.FileLoadException: Could not load file or assembly
  'Microsoft.VisualStudio.Shell.Interop.8.0, Version=15.0.0.0,
  Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its
  dependencies. The located assembly's manifest definition does not
  match the assembly reference. (Exception from HRESULT: 0x80131040)
  File name: 'Microsoft.VisualStudio.Shell.Interop.8.0,
  Version=15.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'
  ---> System.IO.FileLoadException: Could not load file or assembly 'Microsoft.VisualStudio.Shell.Interop.8.0, Version=8.0.0.0,
  Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its
  dependencies. The located assembly's manifest definition does not
  match the assembly reference. (Exception from HRESULT: 0x80131040)
  File name: 'Microsoft.VisualStudio.Shell.Interop.8.0, Version=8.0.0.0,
  Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'

  WRN: Assembly binding logging is turned OFF. To enable assembly bind
  failure logging, set the registry value
  [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1. Note: There
  is some performance penalty associated with assembly bind failure
  logging. To turn this feature off, remove the registry value
  [HKLM\Software\Microsoft\Fusion!EnableLog].

  at System.Reflection.RuntimeAssembly.GetType(RuntimeAssembly
  assembly, String name, Boolean throwOnError, Boolean ignoreCase,
  ObjectHandleOnStack type)    at
  System.Reflection.RuntimeAssembly.GetType(String name, Boolean
  throwOnError, Boolean ignoreCase)    at
  System.Activator.CreateInstance(String assemblyString, String
  typeName, Boolean ignoreCase, BindingFlags bindingAttr, Binder binder,
  Object[] args, CultureInfo culture, Object[] activationAttributes,
  Evidence securityInfo, StackCrawlMark& stackMark)    at
  System.Activator.CreateInstance(String assemblyName, String typeName)
  at System.AppDomain.CreateInstance(String assemblyName, String
  typeName)

  System.IO.FileLoadException: Could not load file or assembly
  'Microsoft.VisualStudio.Shell.Interop.8.0, Version=8.0.0.0,
  Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its
  dependencies. The located assembly's manifest definition does not
  match the assembly reference. (Exception from HRESULT: 0x80131040)
  File name: 'Microsoft.VisualStudio.Shell.Interop.8.0, Version=8.0.0.0,
  Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'

  WRN: Assembly binding logging is turned OFF. To enable assembly bind
  failure logging, set the registry value
  [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1. Note: There
  is some performance penalty associated with assembly bind failure
  logging. To turn this feature off, remove the registry value
  [HKLM\Software\Microsoft\Fusion!EnableLog].

I tried running "Repair" on SSMS, and tried a repair of C:\ProgramData\Package Cache\{AAA9F15B-AF45-4562-9991-93A848D3A902}v15.0.28307.421\redist\vs2017_isoshell_for_ssms.msi, but the problem persists.

I just overlaid the Microsoft.VisualStudio.Shell.Interop.8.0.dll in C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\PublicAssemblies with the copy from ..\PrivateAssemblies\Interop and the IDE opens. (Thanks to Mitch for discovering the offending DLL name.)

This looks like a popular Q & A today, so I'm glad to know this wasn't just me.


Oracle 11g setup closes without errors on pre-requisite check I'm installing Oracle 11g and the setup wizard just closes automatically after the pre-requisite check.

I have administrative access
UAC is disabled
IPv6 is diabled
There is more than enough free space
Windows 7 64bit
Oracle Administrator 64bit
No error in Event Viewer
No error in Installation log in Inventory\logs folder
Tried Run as Administrator also but didn't help

Any ideas?

I assume you are using the 64 bit version of Oracle to install.

I assume you have Windows 7 x64 - Professional, Enterprise, or Ultimate    edition

In the case of unusual errors it would not be impossible for something silly like the language pack in use on Windows to be the cause. Try starting with a new Windows install that has not been customized or has had any other Oracle products installed or removed

Check the    install logs.  With all the versions and folder name changes I am not  100% sure where but you should look in the c:/Windows/oracle or in  the install folder if located on hard disk


How do I get spatial index feature if I mainly use innodb? My current strategy is to create another table in myisam that contains those spacial information. Then if I want to find 20 closest business, I'll just use join.

Is this a good idea?

Using MyISAM is probably your best and only bet to be able to use a spatial index. You might consider storing the data in an InnoDB table and then export data continuously or by batch, according to your needs, to a myisam table. Then use the myisam table for your queries.

I wouldn't trust myisam as storage engine if data is of importance.

Seems like you figured out the solution yourself.


Mariadb replication slave can only access information_schema db I had a master-slave set up working fine until a power outage on the slave. Now I get

error connecting to master 'repl@10.0.0.1:50000' - retry-time: 60
maximum-retries: 86400
message: Access denied for user 'repl'@'localhost' (using password: YES)

The password has not changed and I can log in to the database using
slave-host$ mysql -h10.0.0.1 -P50000 --user=repl --password='secret'

However once logged in I cannot see any databases except information_schema. Trying use <my_db_name> gives an access denied error.
On the master
master-host: mysql> show grants for repl@localhost;
GRANT REPLICATION SLAVE ON *.* TO
  'repl'@'localhost' IDENTIFIED BY PASSWORD '*xxxxx'

I've tried re-granting access, FLUSH PRIVILEGES, START/STOP SLAVE; in various combinations but I'm not getting anywhere.
Nb. the host at 10.0.0.1 has an SSH tunnel setup to the datbase host, so grants for repl@localhost are appropriate because the master sees it as local traffic.
Master and slave both on:  10.1.26-MariaDB-0+deb9u1

Odd.

The fix seemed to be to change the password.

stop slave
change password for repl user at master.
on slave: change master to master_password = 'new pass'
start slave


Alternative to EAV for dynamic fields in a star schema data warehouse I need to support dynamic fields and values in a big datawarehouse for storing API requests log, my user case is that I need to store all API requests query string and able to perform query against them in the future (so it is not just storage, so I can't use blob for them)

e.g. http://example.com/?action=test&foo=abc&bar=def...

I need to store all the field => value mappings, i.e. (action => test), (foo => abc), (bar => def), and since the field is such dynamic, the only solution I have found is to use Entity-Attribute-Value, however, people keep saying it is a very bad design.

So, consider my use case above, what would be a suitable alternative to EAV?

My current schema using KAV

Table requests
(id, timestamp, uri)
e.g. (1, 149382220, '/')
Table params
(request_id, key, value)
e.g. (1, 'action', 'test'), (1, 'foo', 'abc'), (1, 'bar', 'def')

Any suggestions?

Update: We run the warehouse on AWS RedShift

I can think of three solutions - EAV, XML, and Sparse Columns. The latter is vendor-specific and may not be useful to you.

Whichever method you choose, you may wish to consider storing the original request data in a raw format, in a table or flat file. It will make it easy to try new ways of storing the data, allow you to reload data if you discover a mistake with the way you're parsing your requests, and offer opportunities for parsing the API requests using batch processing or "big data" tools if you find that your data warehouse isn't able to efficiently deal with the data.

EAV considerations

EAV/KVS, as you've described it above, is likely to be the most straightforward implementation.

Unfortunately it's also going to be very expensive - to get any sort of efficient queries on commonly used keys you'll need to have indexes on the key column, which could get very fragmented. Querying for particular keys would be extremely expensive.

You may be able to reduce the cost of indexing or index scans by supporting your EAV store with materialised views (many vendors support this) for querying keys or values that you care about.

XML

Most enterprise database systems offer very mature XML handling, including validation, indexing, and sophisticated querying.

Loading the API request into the database as XML would provide one tuple per request, which logically might be a bit more palatable to you than having an unknown number of rows in an EAV table.

Whether this is efficient would depend a lot on your RDBMS vendor and your implementation.

The biggest downside is that this is probably the only way of managing data that's more complicated than string manipulation of the original request!

Sparse Columns / traditional tables

It's possible that you could load your data into a traditional table structure, with one column per key.

SQL Server's Sparse Columns feature is a great alternative to an EAV store. A table with Sparse Columns behaves much the same as a normal table, except that it can have up to 30,000 columns, and NULL values in sparse columns consume no space in the table.

Combining them with Filtered Indexes (another SQL Server specific feature) can provide an extremely efficient alternative to an EAV store if you're frequently querying for a couple of specific columns and/or values.

Using a traditional table with other vendors may be viable - IBM supports over 700 columns per table and Oracle about 1000, and features such as compression or Oracle's treatment of trailing nulls might mean that you can store your API data fairly efficiently.

The obvious downside of this approach is that as you added new keys to your API, you'd need to adjust your schema accordingly.


Display columns as one row I've got a query running on SQL Server 2000 (and using SQL Server Management Studio 10.x)

It results in three columns with multiple rows.

What I am trying to do, and can't get my head round, is to get the multiple rows into one row for each unique item in column 1 (does that make sense).

The query is:

select last_allocated_to as engineer, count(*) as qty, left(post_code, charindex(' ', post_code) - 1) as postcode
from calls with (nolock)
inner join clients on link_to_client=client_Ref
where last_event_status = 'A'
and call_type not in ('VW')
and dbo.dateonly(scheduled_date_Time) = dbo.dateonly(getdate())
and link_to_so in ('OS1','OS2','OS3')
group by last_allocated_to, left(post_code, charindex(' ', post_code) - 1)

The result is (for example):

engineer   qty    postcode
JWH        1      AB25
JWH        1      AB31
JBR        6      EH51
RSC        1      EH51
CPA        1      FK1
RYM        3      FK10
CARPET     1      FK2
RDU        1      FK2
RSC        2      FK2
SWO        5      FK2
BJON       4      FK3
CPA        4      FK3

I want the result to be:

engineer   result
JWH        1 x AB25, 1 x AB31,
JBR        6 x EH51
RSC        1 x EH51
RYM        3 x FK10, 1 x FK2, 2 x FK1
etc etc

Is this at all possible? Or am I on a hiding to nothing?

I suggest that you pipe the results of your query to a temporary table and work from there. You query is already complex enough and to achieve the results you are after you need to perform multiple acceses to the base data.

When your temp table is ready, you can query it like this:

CREATE TABLE #mytable(
   engineer VARCHAR(6) NOT NULL
  ,qty      INT  NOT NULL
  ,postcode VARCHAR(4) NOT NULL
);
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('JWH',1,'AB25');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('JWH',1,'AB31');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('JBR',6,'EH51');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('RSC',1,'EH51');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('CPA',1,'FK1');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('RYM',3,'FK10');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('CARPET',1,'FK2');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('RDU',1,'FK2');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('RSC',2,'FK2');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('SWO',5,'FK2');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('BJON',4,'FK3');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('CPA',4,'FK3');

WITH engineers AS (
    SELECT DISTINCT engineer
    FROM #mytable
),
preaggregation as (
    SELECT engineer, postcode, sum(qty) as qty
    FROM #mytable
    GROUP BY engineer, postcode
)
SELECT *, STUFF((
    SELECT ',' + CAST(qty AS varchar(10)) + ' x postcode'
    FROM preaggregation
    WHERE engineer = engineers.engineer
    FOR XML PATH(''), TYPE
).value('.','varchar(max)'),1,1,SPACE(0))
FROM engineers

Some comments about your code:

NOLOCK does not mean "go faster": it means "no consistency". Get rid of it, you'll thank me later.
Scalar functions are evil, well known performance killers. Avoid them like the plague. In your case, I suppose that the dbo.dateonly function does what's on the tin, so it converts a datetime to a date. You can acheive the same thing with CAST as date (which is still non-SARGable, but doesn't trigger procedural code for each and every row in the input set).

Hope this helps

EDIT: to make the above code compatible with SQL Server 2000, you need another temprary table and (UGH!) a cursor:

IF OBJECT_ID('tempdb..#mytable') IS NOT NULL DROP TABLE #mytable;
IF OBJECT_ID('tempdb..#results') IS NOT NULL DROP TABLE #results;

CREATE TABLE #mytable(
   engineer VARCHAR(6) NOT NULL
  ,qty      INT  NOT NULL
  ,postcode VARCHAR(4) NOT NULL
);
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('JWH',1,'AB25');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('JWH',1,'AB31');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('JBR',6,'EH51');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('RSC',1,'EH51');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('CPA',1,'FK1');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('RYM',3,'FK10');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('CARPET',1,'FK2');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('RDU',1,'FK2');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('RSC',2,'FK2');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('SWO',5,'FK2');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('BJON',4,'FK3');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('CPA',4,'FK3');

CREATE TABLE #results(
   engineer VARCHAR(6) NOT NULL
  ,result varchar(8000) NULL
);

INSERT INTO #results (engineer, result)
SELECT DISTINCT engineer, ''
FROM #mytable;

DECLARE eng CURSOR STATIC LOCAL FORWARD_ONLY READ_ONLY
FOR
SELECT engineer, ',' + CAST(qty AS varchar(10)) + ' x ' + postcode
FROM (
    SELECT engineer, postcode, sum(qty) as qty
    FROM #mytable
    GROUP BY engineer, postcode
) AS preaggregation;

DECLARE @eng varchar(6)
DECLARE @result varchar(8000)

OPEN eng
FETCH NEXT FROM eng INTO @eng, @result

WHILE @@FETCH_STATUS = 0
BEGIN

    UPDATE #results
    SET result = result + @result
    WHERE engineer = @eng;

    FETCH NEXT FROM eng INTO @eng, @result

END

CLOSE eng
DEALLOCATE eng

UPDATE #results
SET result = STUFF(result, 1, 1, SPACE(0))

SELECT *
FROM #results


Why does this trigger slow down updates so much I have a table. Lets call it table tableA. There's another table which keeps data about the history of tableA. Let's call that tableA_records.

On tableA, there's a trigger on update which looks similar to this:

CREATE TRIGGER upd_tableA AFTER UPDATE ON tableA
FOR EACH ROW
if @disable_triggers is null then
BEGIN
 SET @now = unix_timestamp(now());
 UPDATE tableA_records
 SET valid_to = @now
 WHERE valid_to IS NULL AND tableA_id = old.id;
 INSERT INTO tableA_records (
   tableA_id, column2, column3, ...., column27,
   valid_from, valid_to
 ) VALUES (
   new.id, new.column2, new.column3, ...., new.column27,
   @now, NULL
 );
 END;
end if

The issue I'm having is that the trigger slows down updates a lot. Without the trigger, an update on 100 rows of tableA takes 0.2 seconds. Inserting those 100 rows on tableA_records takes 0.25 seconds. The combined actions of the trigger (update tableA_records, insert into tableA_records) and the triggering action (update tableA) should take 0.65 seconds if we sum the times for each. With the trigger, it takes 35 seconds.

Anyone have an idea of what's causing the slowdown?

Your trigger performs two distinct actions.

It "end-dates" the old record in the tableA_records table.
It inserts a fresh copy of the new record into the tableA_records table.

Without seeing how the tableA_Records audit table is constructed, I can't be sure if one step is causing more problems than the other, but some questions you can ask and steps to try:

Is there an index on the tableA_records.tableA_id column? There should be.
What other indexes are on the tableA_records table? Are they necessary? Are they well designed?
Have you tried commenting out the update statement, then testing the trigger, then uncommenting that and commenting the insert statement and retesting? This will tell you if the update or the insert is slower.

I suspect you'll find that more of your delay is caused by the update than the insert, since an update requires scanning through either an index or a full-table scan to find the appropriate row. Make sure that column is properly indexed.

If other columns in tableA_records table are indexed, then look to see if that index is needed. Having too many indexes will slow down inserts.


Compound Primary Key - Index both fields? I have a many-to-many join table between users and roles.

CREATE TABLE AppUserRole
(
    UserId INT NOT NULL,
    RoleId INT NOT NULL,

    CONSTRAINT PK_AppUserRole PRIMARY KEY (UserId, RoleId),

    CONSTRAINT FK_AppUserRole_User FOREIGN KEY (UserId)
        REFERENCES AppUser(UserId),
    CONSTRAINT FK_AppUserRole_Role FOREIGN KEY (RoleId)
        REFERENCES AppRole(RoleId)
);

CREATE INDEX IX_AppUserRole_RoleId ON AppUserRole(RoleId)
CREATE INDEX IX_AppUserRole_UserId ON User(UserId)

Since the compound key is clustered in the order of (UserId, RoleId), is the second index on UserId really necessary?

When you query for all rows for a specific UserId it's going to use the clustered index. Only when you query for all users in a specific RoleId will the index IX_AppUserRole_RoleId be used.

Is the index IX_AppUserRole_UserId necessary?

A query use the index in the column order (UserId first then RoleId).
Without an index on RoleId, it will scan the clustered index.
Unless there is a where clause with a userId, the engine does not know how to get inside the index whitout scanning all the userId.

Because roleId is a FK, best practice suggest to have an index on it. You definetly need it if you (often) query it.

Index on UserId alone is useless. The clustered index already does it since UserId is the entry point. Using it does not require an extra lookup to the PK in order to get data or associated roleId. Just clustered index seek.


Getting All SQL Corresponding to Locks I was recently involved in debugging a tricky SQL Server locking issue.  We eventually figured out what was going on by studying the application code, but it would have been much easier if SQL Server had provided more information.  I'm wondering if there's a way to get that information.  Here's a simple reproduction:
create table Table1 (k int primary key)
create table Table2 (k int primary key)
go

begin transaction
insert into Table1 values (1)  -- acquire a lock
go

insert into Table2 values (2)  -- acquire a second lock
go

I don't show it, but the application was committing the transaction, as it should have.  Before the commit would happen though, it would cause blocking, and our debugging tools were only reporting the text of the last batch/statement executed--the insert into Table2 in this case--along with locks corresponding to a completely different object.  (Table1 in my example.)
Is there a way to get the SQL corresponding to all of the locks held by a session, instead of just the most recent batch?

Is there a way to get the SQL corresponding to all of the locks held by a session, instead of just the most recent batch?

Only in XEvents/Trace by capturing the begin/end statement and acquisition and release of the locks. And that is a very verbose thing to trace, so you would rarely turn on such a trace on a production system.
sys.dm_tran_locks lists all the locks, but there's no DMV that stores the statement that caused the lock to be acquired.


How to most efficiently store possible combinations of categories? I have a table of categories (currently with 14 rows, not likely to change). A need to cache combinations of categories emerged, so I'm creating a table of allowed combinations (only few hundreds of the ~10^10 combinations are likely to be ever searched for and I can constraint it even more). However, I'm not sure how to implement it.

Combination of 14 columns for each row in the category table looks simple, but it doesn't feel right. Any change in the category data would make a change in the combination table structure necessary.

Another option was to make the combination only with ID and combination name and store the important information in the category_combination join table. This feels "right", but making sure that each combination is unique becomes a non-trivial task (unlike simple applying unique constraint over a group of columns in the former case). In PostgreSQL, I would make a deferred trigger, but this is not possible in MySQL. The best option I can think of with MySQL is to make it a function and then calling it from the client app; if it finds a dupe, I'd cancel the transaction. I would aggregate the categories in the combination with GROUP_CONCAT (ordered, of course), either in the query or in an auxiliary view, and then compare the arrays.

Still, I'm not sure if there's no more elegant way, either a completely different approach, or an optimization of the second approach. How to do it best?

EDIT: category combinations will be often used in search, so it needs to be optimized for searching. Adding new a combination will be rare and I hope we will avoid adding new categories, though this should still be possible (even with a lot of work).

How about the SET datatype.  With that, you could represent all combinations of 14 categories in 14 bits (2 bytes).


connecting to an oracle sql database from sql developer on the same computer. When the computer is a domain computer Hi I am trying to do DBA course that requires that I use Oracle database.

I am using my corporate pc to do it.

I set up the loopback adapter to enable localhost communication and setup the hosts file in system 32. When I start lsnrcrl

However when I try to connect I get an error.

<msg time='2016-07-27T08:59:00.083+02:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='ABCDEF121221'
 host_addr='192.168.1.168'>

<txt>27-JUL-2016 08:59:00 * (CONNECT_DATA=(SID=xe)(CID=(PROGRAM=SQLDeveloper)(HOST=__jdbc__)(USER=ABC))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=50619)) * establish * xe * 12505
 </txt>

Extra info

Listener.ora is set to localhost.
hostsfile in system32 is set to 192.168.1.1/domain name.ABCDEF1212221/ ABCDEF1212221

The lstnrctrl status is here.

C:\Windows\system32> lsnrctl status

LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 27-JUL-2016  13:19:16

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 -       Production
Start Date                26-JUL-2016 15:30:00
Uptime                    0 days 21 hr. 49 min. 16 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File      C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora
Listener Log File         C:\oraclexe\app\oracle\diag\tnslsnr\OLDKHQ133304\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)  (HOST=abcdefg1212221.corporate.corporate.net)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

The last number of the log entry is the return code of the operation. On a successful connection, it is 0. In your case, it is 12505.

12505, 00000, "TNS:listener does not currently know of SID given in connect descriptor"
// *Cause:  The listener received a request to establish a connection to a
// database or other service. The connect descriptor received by the listener
// specified a SID for an instance (usually a database instance) that either
// has not yet dynamically registered with the listener or has not been
// statically configured for the listener. This may be a temporary condition
// such as after the listener has started, but before the database instance
// has registered with the listener.
// *Action:
//  - Wait a moment and try to connect a second time.
//  - Check which instances are currently known by the listener by executing:
//    lsnrctl services <listener name>
//  - Check that the SID parameter in the connect descriptor specifies
//    an instance known by the listener.
//  - Check for an event in the listener.log file.

This simply means, you provided an incorrect SID.

xe is the default SID in SQL Developer.

Check the correct SID from the output of lsnrctl status, and use that for connecting.

Or even better, let's just finally forget the SID, and use SERVICE_NAME.

Another possibility is that your database instance was not started, or you do not even have a database created yet.


Can't take a write lock while out of disk space Help me please!

My C:\mongodb-win32-x86_64-2.4.6\data size is 8G. I have 95G free space of drive C. But I get this message:

  com.mongodb.WriteConcernException: { "serverUsed" : "/127.0.0.1:27017"
  , "err" : "Can't take a write lock while out of disk space" , "code" :
  14031 , "n" : 0 , "connectionId" : 10 , "ok" : 1.0}

The permissions are correct to write to data folder, because first few hours application works correctly.

What is going on?

Thanks in advance.

The problem was that I used folder as a mapped drive on Windows.

I've replaced link to the link with absolute path on the real hdd and problem has gone.

It seems that mongo checks free drive space and compares its changes with created file size.

I don't understand the reason for downvotes...


Store parameters of various types I am a (poor, young) scientist working to develop a database to allow a project to scale up. I am experimenting with MySQL and have had very encouraging experiences so far, but I am still novice and am coming at this from perhaps an unusual perspective.

Below is a diagram of the relevant parts of my schema, as I have it modelled right now. The fields are essentially place holders at this point, but in general I would like to leave room for documentation of just about every unique element in the database (except probably not for jobs, but I digress). There are a couple of qualities that I am shooting for:

Valid parameters are algorithm dependent, and we may add new algorithms at any time. I'd like to be able to register a new algorithm, and associate it with particular parameters.
Because new algorithms may require new parameters, I should be able to register new parameters as well.
A particular experiment will use one algorithm. Jobs are unique parametrizations of an experiment/algorithm. Through the relation of an experiment and an algorithm, I can validate the parameters submitted along with a particular job.

My point of confusion, given this schema, is how to store the values of the parameters for a particular job. Right now, as a place holder, I have a values column in the table job_has_parameter, but this is not going to work. Some parameters are integers, others doubles, still others character strings. Some algorithms may even take lists as parameters... but I'm willing to punt on that for now. At any rate, a single value column is not going to work.

Does this schema make sense at all? If it does make sense, generally speaking, what would be a reasonable way to handle storing the parameter values? I welcome criticism; I am under no delusion that I am on the right track here, but it's an effort.

Edit: I think that this schema is inadvertently adopting an entity-attribute-value style. I read that this is a bad thing. https://www.simple-talk.com/sql/t-sql-programming/avoiding-the-eav-of-destruction/ Perhaps someone can help set me on a better path?

How about adding an extra column in you Job_Has_Parameter table?

Add a column called DataType and store what type of value the text contains.

When it comes to using the value, you might have to use the combination of case statement and cast/convert function.

You can take it to next step and add a table called datatype and make a foreign with this new column.


What is the proper way to assign the privileges when a MySQL instance has multi dba? I have a MySQL instance offers services to multi-application, different application has different DBA who responsible to their own application schema.

For example:

Application A has schema DB_A,there are two type of MySQL account for A,one to satisfy the business requirement, and the other one is for administration purpose(performance tuning,DDL,and some other privilege need for administration), apparently ,the former is the subset of the latter.
In addition, there is a super DBA (root), who is responsible to create such accounts I mentions above.

As new to MySQL, I would like to ask that how to execute the proper grant statement to satisfy my requirement?

Mysql offers different ways of creating user .

For application A you can create a user with specific required roles and its better to give IP from respective DBA operates instead of giving % which allows user to enter in mysql from anywhere. With this user you can check performance of your queries and also explain part.
So you can use :
Grant select, insert, update,delete to user@IP on db_a.* Identified by 'password';

For subset user you need to declare which admin rights you want to grant like version and all. Roles will depend on your requirement.

User can be created with same command as above.
Let me know if more clarity is needed


Mysql HY000 the table table_name is full when adding new column I can't add a new column innodb table which size's 16MB. when I try to add, mysql says HY000 the table table_name is full.

I changed my tmp_table_size and heap_size but mysql still says same error.

I try How to solve "The table ... is full" with "innodb_file_per_table"? this settings but same error.

I create dummy table and Add new column it is ok. but my real table mysql says error.

My variables :
innodb_data_file_path | ibdata1:12M:autoextend

There is no maximum in the file_path command you provided that limits the size of the ibdata1 file. Perhaps you don't have enough disk space left. It would be a good idea to add more disk space to the current volume or clean up first as mysql needs that volume.

If you're worried about the ibdata files filling up disk space, you can always add another ibdata file and place that file anywhere that has sufficient disk space. You would have to set permissions so that the mysql user could access the directory where you place the second file.

Example:

innodb_data_file_path = ibdata1:12M:autoextend

becomes

innodb_data_file_path = /ibdata/ibdata1:(NN)M;/disk2/some_directory/ibdata2:128M:autoextend

Where (NN)M is the current size of the ibdata1 file.

The second file will start out with a fixed size of 128M and will autoextend.

Here is the process

Increasing InnoDB table space

Shut down the MySQL server.

If the previous last data file is defined with the keyword autoextend, change its definition to use a fixed size, based on how large it has actually grown. Check the size of the data file, round it down to the closest multiple of 1024 × 1024 bytes (= 1MB), and specify this rounded size explicitly in innodb_data_file_path.

Add a new data file to the end of innodb_data_file_path, optionally making that file auto-extending. Only the last data file in the innodb_data_file_path can be specified as auto-extending.

Set permissions for mysql.

chown mysql:mysql -R /disk2/some_directory/ibdata2

Start the MySQL server again.

The other thing to keep in mind with ibdata files is the fact that they can't be easily shrunk. If you are not using innodb_file_per_table, then ibdata will just grow and grow and even if you delete database objects. If you have deleted databases and are sure that your ibdata file will remain small, you can always recreate that file and the iblogs to give disk space back to the OS. Rolando wrote a post that will help you out with that.


Finding out what permissions are required by the SQL Server Agent Jobs I've been looking at this for a while and I can't work out an easy way to do this.

As it stands every job in the database runs under SA level rights (horrifying thought)

I'm sure some of the jobs need quite high level rights for what they're doing but I'm also certain quite a lot do not. I was thinking to create 3 or 5 domain accounts to better control the jobs (its not necessary but having them all run SA is personally worrying).

Anyone know a way that this can be done without simply ripping out all the permissions and granting them one by one until I stop getting errors?

Ste

The only way you'll be able to do this consistently and correctly is by looking at what each job does, and cross-referencing that with the msdn articles for each command on your given version.

For example, DBCC SHOW_STATISTICS has a section:

  Permissions

  In order to view the statistics object, the user must own the table or
  the user must be a member of the sysadmin fixed server role, the
  db_owner fixed database role, or the db_ddladmin fixed database role.

  > SQL Server 2012 SP1 modifies the permission restrictions and allows
  users with SELECT permission to use this command.

  Note that the following requirements exist for SELECT permissions to
  be sufficient to run the command:   Users must have permissions on all
  columns in the statistics object   Users must have permission on all
  columns in a filter condition (if one exists)   The table cannot have
  a row-level security policy.
  To disable this behavior, use traceflag 9485.

As each command has a different subset of required permissions, and this is at times version dependent, there is no quick and dirty way to determine it that I know of.
(Maybe some tracing could give you the same information faster, but it would also imply monitoring the tasks as they run)


How to properly implement relationship between a user and multiple other users I have a table with the following structure:

Table_1
--- UserId (int)
--- UserName (varchar)

A user can have a relation with other users. Meaning, a UserId from Table_1 can have a relation with other UserIds. So, the question is, how should I structure a second table to implement this?

The second table needs to contain the UserId of a user and other UserIds that the user has relations with. So, should I structure the second table as follows:

Table_2
--- UserId (int - Primary Key)
--- OtherId (varchar)

In the structure above, in OtherId I was planning to store all other UserIds in conjunction, like one column would contain all other UserIds that the principal UserId is related with, but each UserId in the OtherId column would be separated with a comma (,). The way I would retrieve them would be by taking the value from the OtherId column and then, in code-behind, splitting the string by (,) into an array. Then I would use each value from the array to retrieve the UserName from Table_1.

This was the idea I had. I tested it on a database of 300,000 records in Table_1 and one relationship string consisting of almost 3000 UserIds in the second table. This took a significant amount of time (10s) to process all the data, including the splitting of the string.

I am almost certain that my idea is nowhere near to ideal practices. Can someone just give me some ideas on how I should go about this?

It seems what you're describing for Table_2 is a linking table for a self-reference for Table_1. In the case where there is only one possible reference for each UserID, you would store this information on Table_1 itself (UserID and ReferenceUserID with a foreign key back to UserID).

What you have described, however, is a many-to-many relationship, where a UserID can have many related UserIDs and vice-versa. In this scenario, Table_2 should not use a string holding all of the related userIds for your principal. There are several reasons for this, but the two most important are:

Performance will be terrible when querying because you will have to do string manipulation to split the CSV string into rows, then join on those extracted IDs
You will not be able to store information about individual relationships.

To illustrate #2 (using hypothetical use cases for the table), say this database holds information about family relationships. Users can be associated with 0 to many other users in their family tree. If you want to store information about each relationship (not DB relationship, but IRL) then each relationship should be a separate row.

For example, Dave has a UserID of 1. Pete is Dave's brother and has a UserID of 5. In Table_2, you would store Dave's UserID in the UserID column and Pete's in the OtherID column. You can then add fields to record the relationship (Brother) and other information (maiden name for marriages etc).

With the CSV column for OtherID, you could not efficiently store this information for the potentially many relationships a single UserID might have.

Therefore, consider this as the base design for the second table:

CREATE TABLE Table_2
(
  UserID          int FOREIGN KEY REFERENCES Table_1 (UserID),
  ReferenceUserID int FOREIGN KEY REFERENCES Table_1 (UserID),
  PRIMARY KEY (UserID, ReferenceUserID)
);

You can add other columns to this setup to store additional details about each relationship, as discussed above.


How to use SQL CASE result to compare a column and get another case result I'm trying to compare my case result (desirecountry) with an existing column (so.country_name) to get another case result (Final_Country_Map_Name):
     SELECT subs."user email", subs."user join date", subs.location,
                iso.country_name as iso_country, iso.country_id as iso_country_id,
                    users.country, users.event_type,
    -- 1.
    CASE  WHEN subs.location = iso.country_name THEN iso.country_name
             ELSE users.country END AS **desirecountry**,
    -- 2.
    CASE  WHEN **desirecountry** = iso.country_name THEN **desirecountry**
            --ELSE Null END AS **Final_Country_Map_Name**

    FROM subs
    LEFT JOIN
    iso
    ON
    subs.location = iso.country_name
    LEFT JOIN
    users
    ON
    users.user_id = subs."user email"

When I'm using the second CASE conditions the logic is failing, it's not giving the expected output, it's giving me a null value in "final_country_map_name" column instead of "desirecountry" value = United States. Any small help would be helpful, Thank you.
Ex: Column names are:->
location | iso_country  |   country     | desirecountry  | final_country_map_name   &&& Column o/p are: ->
U.s,      null,          United States,   United States,       null,

Your code looks more like postgre, than MySQL
Also for your information, you should not use spaces in column names and leave the desired output to the gui, that save a lot of time
I used the inner SubSELECT to gather the data and put the second CASE in the outer
SELECT
SELECT
    `user email`,
    `user join date`,
    location,
    iso_country,
    iso_country_id,
    users.country,
    users.event_type,
    desirecountry,
    CASE
        WHEN desirecountry = iso_country THEN desirecountry
        ELSE NULL
    END AS Final_Country_Map_Name
FROM
    (SELECT
        subs.`user email`,
            subs.`user join date`,
            subs.location,
            iso.country_name AS iso_country,
            iso.country_id AS iso_country_id,
            users.country,
            users.event_type,
            CASE
                WHEN subs.location = iso.country_name THEN iso.country_name
                ELSE users.country
            END AS desirecountry
    FROM
        subs
    LEFT JOIN iso ON subs.location = iso.country_name
    LEFT JOIN users ON users.user_id = subs.`user email`)


Swapping TDE Cert Slow, any way to improve performance? Fair warning, I am very new to encryption with DBs. Right now we use the following statement to swap certificates when in the process backing/restoring DBs between servers:

ALTER DATABASE ENCRYPTION KEY REGENERATE
    WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE....

This is working perfectly fine but the issue is that the amount of time it takes to change the certificate through this statement. I've noticed that it can take more than an hour to change the certificate for DBs that are >100GB.

Is there a faster way to change TDE Certificates that anyone knows of?

Is there a faster way to change TDE Certificates that anyone knows of?

If you're actually needing to change the certificate that is doing the encryption (it protects the data) then the only faster way is to get a faster disk subsystem, new motherboard/cpus that support AESNI, and upgrade to SQL Server 2016.

If, however, you don't need to rotate the actual encrypting key but one of the protecting keys (Server Cert in this case, not the DEK) it's a very short mostly but not all metadata operation that can be accomplished via ALTER DATABASE ENCRYPTION KEY [Blah] ENCRYPTION BY SERVER CERTIFICATE [Blah2]. That will change the server certificate used to protect the DEK which will be extremely fast as the physical data does not need to be decrypted and encrypted again using the new DEK.


PostgreSQL exits the shell when importing large data I'm having this totally weird scenario: We have an archiving server based on PostgreSQL inside a Scientific Linux virtual server. I want to migrate the data (20+ GB) to a physical Scientific Linux machine, so I used

\copy (select * from table) to '/path/to/file

This ran successfully, and the data is in the output file.

On the physical machine I try to import the data using

\copy table from '/path/to/file'

When I try this command on a terminal emulator, it exits after a few minutes.  Also, tried it on the terminal itself after login but after a few minutes it exits, too.  In both cases there is no data imported.

Could it be that the server is somehow overloaded?  Can we overcome this and import the entire file?

Edit: I increased the RAM to 3 GB, the crash took a bit longer to come.

Edit 2: There is a log file under /var/lib/pgsql/data/pg_log, this log file has these lines as the last few lines of it:

Context: COPY sample, line 77324116
Statement: COPY sample from STDIN
ERROR: Unexpected EOF on client connection
Context: COPY sample, line 77324116
Statement: COPY sample from STDIN
Log: Could not send data to client: Broken pipe
Statement: COPY sample from STDIN
ERROR: Unexpected EOF on client connection

Any help is really appreciated!

Is your physical machine using autologout?

$ echo $TMOUT

When there exists a value above zero, you should unset that environment variable.

$ unset TMOUT

http://www.cyberciti.biz/faq/unset-tmout-under-unix-appleosx-bash-ksh-tcsh-shell/


What is the disadvantage of maintaining both row and column table storage? I just watched Oracle's In Memory database release. I can understand that removing a lot OLAP indexes accelerated the database a bit. But does that really mean maintaining 2 table storages doesn't have extra cost? Like why would insert still be faster when 2 storages need to be maintained simultaneously?

Maintaining two representations of data is obviously more complex than maintaining only one, and added complexity always has a cost. That cost might only be in code complexity and not measurable in terms of raw throughput, but even that needs to be carefully evaluated (more complexity implies higher risk of bugs, and higher cost of maintenance).

That's a fundamental trade-off, like everything else - are the benefits of these new features good enough to warrant the added costs (complexity, CPU, or anything else)? Apparently they are.

Regarding inserts, remember that inserts (and updates) generally have two sides: a read side and a write site. The read side (if only, e.g., checking a primary key for duplicates) can potentially be accelerated by the new in-memory structures. Same for join processing that can happen in complex inserts/updates/merges.

The write side itself can't be faster* (since it does what it used to do without the in-memory option, and then some to maintain consistency of the in-memory store), but if you make the read side an order of magnitude faster, the overall processing time can be reduced. By how much is an open question, and will depend on a whole lot of factors.

Also note that if you end up removing indexes, this will have an impact on insert performance for the affected tables. This would be an "indirect" benefit - not a speedup thanks to the in-memory option per se, but because that option allowed you to reduce the number of indexes.

*Never say never. There may be corner cases.