Access "couldn't use ... file already in use" I have an Access 97 database that is used by some software on multiple (up to 4-6) computers (running Windows 7). Once in a while (maybe 1-2 months) something happens where computers on the network can't access the database.

The program accesses the database through a ADO/JET driver (the program is written in LavVIEW) and reports "Could not use ... file already in use." When this happens attempting to open the database on another computer in Access gives a similar message:

"Couldn't use '.mdb'; file already in use."

When this happens there isn't an associated .ldb file with the .mdb. The last time this happened I restarted the four computers that were running the software and I was immediately able to open the DB with Access on another computer. It seems that caused the problem to go away, but it is possible it was a coincidence.

From what I have read this problem can happen on a network if computers are unable to create/modify the .ldb file. The .mdb file is on a Novell network drive that all the computers have read/write/create permissions on, and everything works fine until the computers stop being able to open the file.

Does anyone have any ideas of what could be causing this? I think one possibility is the computers actually cannot write the .ldb file (the software normally doesn't have a connection open to the database, so most of the time there isn't a .ldb file). I would have to check that by trying to write a file to that directory next time this problem happens. It seems like a strong possibility is something else hanging it up though, especially since restarting the computers seems to fix it but I'm not very familiar with Access, ADO, and JET.

From what I have read this problem can happen on a network if computers are unable to create/modify the .ldb file. The .mdb file is on a Novell network drive that all the computers have read/write/create permissions on, and everything works fine until the computers stop being able to open the file.

What you're doing is very dangerous. Database engines handle MVCC. File I/O is not sufficient. That said, Microsoft Access has a method to get around this called Shared Access Mode. I wouldn't suggest using this but it should get around the error problem.

Make sure that Access is set to open in shared mode on all of the users' computers. This is the default setting, but you should check to be sure — if a user opens the database in exclusive mode, it will interfere with data availability. Complete the following procedure on each computer:

Start Access and under File, click Options. Note, if you're using Access 2007, click the Microsoft Office Button and then click Access Options.
In the Access Options box, click Client Settings. Note, if you're using Access 2007, click Advanced.
In the Advanced section, under Default open mode, select Shared, click OK, and exit Access.

As a side note, it's my bread and butter migrating people away from Microsoft Access, Visual Basic, and Perl. Any decent consultant salivates at the mouth when they read about this for the implications to provide value. Shared Access Mode should not be used without extensive backup solutions. Things will go wrong, and when they do expect downtown. I have a few clients on this and we sync their stuff up to AWS every 15 minutes.
Note, I see you're on Access 97. I'm not sure when they introduced Shared Access mode, but you'll need to upgrade if that option isn't there. I would really not want to work around that, it's not in your best interests if you're advising them, or theirs.


how to check which background process are running in my oracle database? To check all background process in oracle we can see using v$bgprocess view. I want to check which background process are active. Can anybody please help ?

Thanks

17:31:15 SYSTEM@sandbox> ed
Wrote file S:\spool\sandbox\BUFFER_SYSTEM_34.sql

  1  select sid,  process, program
  2    from v$session s join v$bgprocess using (paddr)
  3   where s.status = 'ACTIVE'
  4*    and rownum < 5
17:31:21   5  /

       SID PROCESS                  PROGRAM
---------- ------------------------ ----------------------------------------------------------------
         2 1332                     ORACLE.EXE (PMON)
         3 480                      ORACLE.EXE (PSP0)
         4 976                      ORACLE.EXE (VKTM)
         5 992                      ORACLE.EXE (GEN0)

Elapsed: 00:00:00.05

However, this doesn't make much sense as they're always stated as active, so I guess you should define what you mean saying active.


Add 0 into array_agg if there is no matching data I wrote this view, and it is selecting almost everything I want, but I do not get results for all: project_keywords(which has_many project_report(keyword_id, project_id))

So a project_keyword has a data saved into project_report and the data is stored into position column but there are times when there is no data saved for a project_keyword.

When there is no data saved, I would like to add 0 into array_agg(pr.position).

 SELECT
  k.name,
  pr.project_id,
  array_agg(distinct pr.position) as pr_position,
  date(pr.created_at)
FROM
  project_keywords pk
INNER JOIN keywords k on pk.keyword_id = k.id
INNER JOIN project_reports pr USING (project_id, keyword_id)
WHERE
  date(pr.created_at) = '2014-03-27' AND pr.project_id = 2
GROUP BY k.name, pr.project_id, date(pr.created_at);

Also how can return results where array_agg(distinct pr.position) SIZE(no of elements) is > 1

Edit:

SELECT
  k.name,
  pr.project_id,
  COALESCE(array_agg(DISTINCT pr.position), '{0}'::integer[]),
  date(pr.created_at)
FROM
  project_keywords pk
INNER JOIN keywords k on pk.keyword_id = k.id
INNER JOIN project_reports pr USING (project_id, keyword_id)
WHERE
  date(pr.created_at) = '2014-03-27' AND pr.project_id = 2
GROUP BY k.name, pr.project_id, date(pr.created_at)
HAVING array_length(COALESCE(array_agg(DISTINCT pr.position), '{0}'::integer[]), 1) > 1;

This query is returning all the project_keywords which have project_reports row's, but they are not returning 0 for project_keywords which do not have associated rows in project_reports.

For the first point, you can try this:

COALESCE(array_agg(DISTINCT pr.position), '{0}'::integer[])

The same can be achieved by a CASE, too:

CASE WHEN array_agg(id) IS NULL
     THEN '{0}'::integer[]
     ELSE array_agg(id) END

The second problem can be handled by a HAVING cause:

...
GROUP BY ...
HAVING array_length(COALESCE(array_agg(DISTINCT pr.position), '{0}'::integer[]), 1) > 1;


Mongo Database - Dirty Shutdown We have a major issues on our MongoDB and I've attached the latest logs and files.  In previous forumns I've seen this get fixed by your team.  Can you please help as soon as possible please, we are a medical facility and need this fixed.
We are running on Windows 2016 Server and running fine until a power outage.
2020-09-11T14:34:18.780-0400 I CONTROL  [main] Trying to start Windows service 'MongoDB'
2020-09-11T14:34:18.781-0400 I CONTROL  [initandlisten] MongoDB starting : pid=1816 port=27017 dbpath=C:\Program Files\DELL\WMS\Database\Mongo 64-bit host=VLDATAWMS02
2020-09-11T14:34:18.781-0400 I CONTROL  [initandlisten] targetMinOS: Windows Vista/Windows Server 2008
2020-09-11T14:34:18.781-0400 I CONTROL  [initandlisten] db version v3.4.10
2020-09-11T14:34:18.781-0400 I CONTROL  [initandlisten] git version: 078f28920cb24de0dd479b5ea6c66c644f6326e9
2020-09-11T14:34:18.781-0400 I CONTROL  [initandlisten] allocator: tcmalloc
2020-09-11T14:34:18.782-0400 I CONTROL  [initandlisten] modules: none
2020-09-11T14:34:18.784-0400 I CONTROL  [initandlisten] build environment:
2020-09-11T14:34:18.784-0400 I CONTROL  [initandlisten]     distarch: x86_64
2020-09-11T14:34:18.784-0400 I CONTROL  [initandlisten]     target_arch: x86_64
2020-09-11T14:34:18.785-0400 I CONTROL  [initandlisten] options: { config: "C:\Program Files\DELL\WMS\MongoDB\mongod.cfg", net: { bindIp: "localhost", port: 27017 }, security: { authorization: "enabled" }, service: true, storage: { dbPath: "C:\Program Files\DELL\WMS\Database\Mongo" }, systemLog: { destination: "file", path: "C:\Program Files\DELL\WMS\MongoDb\mongod.log" } }
2020-09-11T14:34:18.785-0400 I -        [initandlisten] Detected data files in C:\Program Files\DELL\WMS\Database\Mongo created by the 'wiredTiger' storage engine, so setting the active storage engine to 'wiredTiger'.
2020-09-11T14:34:18.786-0400 I STORAGE  [initandlisten] wiredtiger_open config: create,cache_size=3583M,session_max=20000,eviction=(threads_min=4,threads_max=4),config_base=false,statistics=(fast),log=(enabled=true,archive=true,path=journal,compressor=snappy),file_manager=(close_idle_time=100000),checkpoint=(wait=60,log_size=2GB),statistics_log=(wait=0),
2020-09-11T14:34:18.797-0400 E STORAGE  [initandlisten] WiredTiger error (0) [1599849258:797507][1816:140721228962256], file:WiredTiger.wt, connection: read checksum error for 4096B block at offset 24576: block header checksum of 1919248233 doesn't match expected checksum of 3720300442
2020-09-11T14:34:18.797-0400 E STORAGE  [initandlisten] WiredTiger error (0) [1599849258:797507][1816:140721228962256], file:WiredTiger.wt, connection: WiredTiger.wt: encountered an illegal file format or internal value
2020-09-11T14:34:18.797-0400 E STORAGE  [initandlisten] WiredTiger error (-31804) [1599849258:797507][1816:140721228962256], file:WiredTiger.wt, connection: the process must exit and restart: WT_PANIC: WiredTiger library panic
2020-09-11T14:34:18.797-0400 I -        [initandlisten] Fatal Assertion 28558 at src\mongo\db\storage\wiredtiger\wiredtiger_util.cpp 361
2020-09-11T14:34:18.797-0400 I -        [initandlisten]
***aborting after fassert() failure
Thank You,

Even you are at the "wrong place"...
Command 'wt salvage' of wiretiger tools probably will solve this problem.


an element with the same SQL SERVER key has already been added Good morning community, I am having problems when I run this query from SQL 2018 and Visual basic 2010 shows me the following error "an element with the same key has already been added"
SELECT DESCRIPCION AS nodeText,'DEPA' + CAST(IDDEPARTAMENTO AS VARCHAR) AS nodeKey,''AS nodeParentKey FROM DEPARTAMENTO
UNION ALL
SELECT DESCRIPCION AS nodeText,'PROV' + CAST(IDPROVINCIA AS VARCHAR) AS nodeKey,
'DEPA' + CAST(IDDEPARTAMENTO AS VARCHAR) AS nodeParentKey FROM PROVINCIA
UNION ALL
SELECT DESCRIPCION AS nodeText,'DIST' + CAST(IDUBIGEO AS VARCHAR) AS nodeKey,
'PROV' + CAST(IDPROVINCIA AS VARCHAR) AS nodeParentKey FROM ubigeo

As I can avoid duplicate elements, I think it is with the DISTINCT clause but I have no idea where this clause can go, please support

use UNION instead of UNION ALL in this way:
SELECT DESCRIPCION AS nodeText,'DEPA' + CAST(IDDEPARTAMENTO AS VARCHAR) AS
nodeKey,''AS nodeParentKey FROM DEPARTAMENTO
UNION ALL
SELECT DESCRIPCION AS nodeText,'PROV' + CAST(IDPROVINCIA AS VARCHAR) AS nodeKey,
'DEPA' + CAST(IDDEPARTAMENTO AS VARCHAR) AS nodeParentKey FROM PROVINCIA
UNION
SELECT DESCRIPCION AS nodeText,'DIST' + CAST(IDUBIGEO AS VARCHAR) AS nodeKey,
'PROV' + CAST(IDPROVINCIA AS VARCHAR) AS nodeParentKey FROM ubigeo

it will remove duplicates


PLSQL - getting real-time updates I want a PL/SQL procedure to give me progress updates as it runs. However, DBMS_OUTPUT seems only to give me the output when the whole procedure is done.

Is there a way to make it give me updates during runtime?

Thanks!

Not to the current session, no.

You can use the dbms_application_info package to instrument the code so that tools running in other sessions can query v$session_longops and see how far along the process is.  This is the same tool that Oracle internally writes to during long-running operations so lots of front-ends like Enterprise Manager and Toad will already read from it.

Alternately, you could use autonomous transactions to write to a status table as your code runs and read from that status table in a different session.  I can't see why you'd want to, but you could theoretically also use the dbms_alert package to send alerts to other sessions.


Get Row Value by Column Name of Name at runtime I currently have a stored procedure that obtains rows from an import table and joins another table which has a row for each column header of the import table.

I am able to query this but in order to obtain the value I need to utilize a huge case statement.

Is there anyway to dynamically get a column value from a row by simply knowing the nvarchar type of the column name?

Here is my current procedure:

...
SELECT

import.ImportID,
f.FieldID,
f.FieldName,

CASE

    WHEN f.FieldName = 'Test'
    THEN import.Test
    WHEN f.FieldName = 'Test2'
    THEN import.Test2
    ELSE
        0

END AS [Value]
--import.[f.FieldName] AS [Value]

FROM [Import_Table] import
LEFT JOIN #Fields f ON (1 = 1)

END

The issue I have is that there are 40+ columns in the import table, case statements aren't too bad, however, would be difficult to maintain/record moving forward. Is there another way to obtain the data dynamically?

Perhaps a temporary pivot table and joining on column names?

TIA

-- edit --

-- this is just used from another table
CREATE TABLE #Fields (FieldID bigint, FieldName VARCHAR(50))

-- this is import table
TABLE Import_Table (ImportID bigint, UserID bigint, Test decimal(5,2), Test2 decimal(5,2), Test3 decimal(7,3), ....

Fields is populated as:

Test
Test2
Test3
Test4
Test5
Test6
...

-- edit 2

//Import Table
1,323,2.3,4.5,3.0,8.803,...

//expected output
1,1,Test1,2.3
1,2,Test2,4.5
1,3,Test3,3.0
1,4,Test4,8.803

You can use the UNPIVOT function to achieve this result set without having to join to the Field table at all.

This query shows how this works (only showing 3 columns for brevity):

SELECT Import_id,
  p.Field,
  p.Value
FROM
(
  SELECT Import_id,
    CAST(Test1 AS VARCHAR) AS Test1,
    CAST(Test2 AS VARCHAR) AS Test2,
    CAST(Test3 AS VARCHAR) AS Test3
  FROM Import_Table
) src
UNPIVOT
(
  Value FOR Field IN (Test1, Test2, Test3)
) p

You can see this in action here in the 3rd query in that db<>fiddle. For more columns, you simply add them to the inner SELECT statement and to the IN clause of the UNPIVOT statement. Alternatively, you can leverage the Fields table and dynamic SQL to build your UNPIVOT statement like the 4th query in the db<>fiddle.

This example requires SQL 2016 or higher as it uses the STRING_AGG() function, earlier versions will work with this method but require a custom string aggregation function.

DECLARE @Sql NVARCHAR(MAX)

SET @Sql = '
SELECT Import_id,
  p.Field,
  p.Value
FROM
(
  SELECT Import_id,
    '

SELECT @Sql = @Sql +
  STRING_AGG('CAST(' + FieldName + ' AS VARCHAR) AS ' + FieldName, ', ')
FROM Fields

SET @Sql = @Sql + '
  FROM Import_Table
) src
UNPIVOT
(
  Value FOR Field IN ('

SELECT @Sql = @Sql +
  STRING_AGG(FieldName, ', ')
FROM Fields
SET @Sql = @Sql + ')
) p'

EXEC sp_executesql @Sql


Index for finding combinations of attributes in JSON array Based on this related question on SO.

This works fine if you only want simple matches. Suppose tracks have both Artist and Title field in the JSON data. So we have something like:

INSERT INTO tracks (id, data)
VALUES (1, '[{"artist": "Simple Plan", "title": "Welcome to My Life"}]');

We create the index like in the original question:

CREATE INDEX tracks_artists_gin_idx ON tracks
USING GIN (json2arr(data, 'artist'));

CREATE INDEX tracks_title_gin_idx ON tracks
USING GIN (json2arr(data, 'title'));

So now we have two fields to match. As you can see, if we perform the original query (with very naive modifications) of:

SELECT *
FROM   tracks
WHERE  '{"ARTIST NAME"}'::text[] <@ (json2arr(data, 'artist'))
AND    '{"TITLE"}'::text[]       <@ (json2arr(data, 'title'))

This will give the wrong answer because the indices of the array of artist and title in the JSON array do not have to match for this query to match something in the JSON. What is the proper way of doing this query so we can get the exact match we need? Does json2arr() need to be changed?

Edit: Why this is wrong

Suppose our table has records like:

INSERT INTO tracks (id, data)  VALUES
  (1, '[{"artist": "Simple Plan",    "title": "Welcome to My Life"}]')
, (2, '[{"artist": "Another Artist", "title": "Welcome to My Life"},
        {"artist": "Simple Plan",    "title": "Perfect"}]');

If you query like:

SELECT *
FROM   tracks
WHERE  '{"Simple Plan"}'::text[]        <@ (json2arr(data, 'artist'))
AND    '{"Welcome to my Life"}'::text[] <@ (json2arr(data, 'title'))

Both records will be matched (both record 1 and 2), even though you really only wanted the first record.

Why?
The expressed requirement that artist and title must match in the same element of the JSON array is not reflected in your query, which finds all rows where at least one element matches the artist and another (possibly a different one) matches the title.
The example data for your first case was inconclusive, since the query cannot fail this way for a single array element. Your second example demonstrates the case well, though.
Solution in Postgres 9.3
There are various ways to fix this. One way would be to translate each json array element to an SQL array of composite type consisting of artist and title and match the whole type as one.
Another way would be to keep indexes like you have now (or even a single composite spanning both expressions: (json2arr(data, 'artist'), json2arr(data, 'title')). Your current query identifies possible matches. Unnest the json array data for all identified candidates and check whether both artist and title match on the same element. May or may not be efficient enough.
Better use jsonb in n Postgres 9.4
I am not going into detail for json / pg 9.3, because the release of jsonb in Postgres 9.4 with advanced indexing capabilities mostly obsoleted the problem. This can be implemented much simpler and more efficient with a native GIN index on a jsonb column. There are various options. To optimize for the presented case:
CREATE TABLE tracks (id serial, data jsonb);
INSERT INTO tracks (id, data)  VALUES
  (1, '[{"artist": "Simple Plan",    "title": "Welcome to My Life"}]')
, (2, '[{"artist": "Another Artist", "title": "Welcome to My Life"},
        {"artist": "Simple Plan",    "title": "Perfect"}]');

Index:
CREATE INDEX tracks_data_gin_idx ON tracks USING gin (data jsonb_path_ops);

Query:
SELECT * FROM tracks
WHERE  data @> '[{"artist": "Simple Plan", "title": "Welcome to My Life"}]';

Sequence of attributes in the JSON value and insignificant whitespace don't matter for jsonb. I added detailed information for jsonb to the referenced answer on SO:

Index for finding an element in a JSON array


How to insert into temp table on Server A results from dynamic SQL open query stored proc call to remote server B via Remote Query showplan operator? I have to call a stored procedure on server B from server A using open query via dynamic SQL, and insert the results into a temp table created on server A. I noticed in the execution plan, it's using a Remote Scan showplan operator. I know when possible the Remote Query showplan operator is more efficient, but I'm not sure if it's possible in this case? (The remote stored procedure returns about 1.5 million rows.)

This is my query on local server A:

CREATE TABLE #FinalResults (Field1 INT, Field2 VARCHAR(100))
DECLARE @DynamicSQL NVARCHAR(MAX) = ''

SELECT @DynamicSQL = @DynamicSQL +
'
SELECT Field1, Field2
FROM OPENQUERY(MyRemoteServerB, ''EXEC sp_MyRemoteStoredProcedure'')
'

INSERT INTO #FinalResults
EXEC sp_ExecuteSQL @DynamicSQL;

This is my query in my remote procedure sp_MyRemoteStoredProcedure on my remote server B:

CREATE TABLE #Results (Field1 INT, Field2 VARCHAR(100))

INSERT INTO #Results
SELECT Field1, Field2
FROM Database1.dbo.Table1
WHERE DateField >= 'Date1'
    AND DateField < 'Date2'

UNION

SELECT Field1, Field2
FROM Database2.dbo.Table1
WHERE DateField >= 'Date1'
    AND DateField < 'Date2'

SELECT Field1, Field2
FROM #Results

Relevant execution plan operators:

I noticed in the execution plan, it's using a Remote Scan showplan operator. I know when possible the Remote Query showplan operator is more efficient...

There's nothing inherently inefficient about the Remote Scan operator. It is used, for example, with BULK INSERT, which is extremely efficient. The performance of the Remote Scan depends on what lies behind it, in this case a linked server, various drivers, networking etc.

A Remote Query operator can be more efficient, for example if a predicate or aggregation operation can be pushed to the remote server, rather than retrieving all rows and doing the work locally.

  ...but I'm not sure if it's possible in this case?

There is no choice except to use Remote Scan to read data from a T-SQL remote stored procedure call.


How to connect to remote MySQL from a specific IP? I have two IPs (192.168.13.1 and 192.168.13.2) in my Server A and  have provided select access to another Server B for secondary IP/virtual IP (192.168.13.2).

mysql> show grants for web_app@192.168.13.2;
+------------------------------------------------------------------------------------------------------------------------------+
| Grants for user@192.168.13.2                                                                                |
+------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO 'user'@'192.168.13.2' IDENTIFIED BY PASSWORD '*pass' |
+------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

When I tried to connect mysql of Server A to Server B ( from Linux ), Primary IP is trying to connect to MySQL and access is getting denied

[root@13-1 ~]# mysql -h 192.168.12.1 -u user -p
Enter password:
ERROR 1130 (HY000): Host '192.168.13.1' is not allowed to connect to this MySQL server

Then I give exactly the same grants for 192.168.13.1 and I was able to login. My application running in 192.168.13.2 trying to fetch from remote server was also not working until I give grants for primary IP. My ifconfig details::

[root@13-1 ~]# ifconfig | grep inet
        inet 192.168.13.1  netmask 255.255.254.0  broadcast 192.168.13.255
        inet 192.168.13.2  netmask 255.255.254.0  broadcast 192.168.13.255
        inet 192.168.13.3  netmask 255.255.254.0  broadcast 192.168.13.255

I have two questions:

How to mysql remote login from a specific IP ( in my case, from 192.168.13.2 ) ?
Is it necessary for an application(with MySQL) to have grants provided for Primary IP also, even though I am trying from App in another IP ? Or Am I missing something?

Let me answer in more formatted form:

MySQL server do not manage client ip on server, and not manage outgoing on client. Server could select IP address for listen only.

Server just compare client IP with permission tables and allow or reject access.
I do not know method how to tell mysql client (including library to use dedicated outgoing ip).

what You can do:

use wildcards, like - user@192.168.12.%
use domain names, like - user@serverB.yourdomain.com

Wildcard make list of IP addresses wider than necessary, but at the same time using DNS resolving:

Slowdown performance under high loading
Could completely crash work with may network related issues

You can combine wildcard + firewall rules for reduce number of allowed IP.


Replicate update statement as Insert in Mysql 5.6 I want to replicate all update statement from Master as a Insert at Slave end.

Is it possible like in SQL Server? If so, how?

I want to do it because, if any record on Master is updating and if same record is not present at Slave end, then instead of Replication tends to fail, it should insert that record at Slave end.

All insert on Master will act as Insert only at Slave end.

How about using INSERT ... ON DUPLICATE KEY UPDATE or REPLACE INTO on master itself?


Why most of Sql Injection need url like this "index.asp?id=123" I am trying to learn "SQL Injection" concepts and also trying to practice some Kali tools to learn, but there is a question that I do not understand and my question is why do most tools need URL's that end with "AAA.BBB?id=000" as a input string for injection.

When you see URL's that have that kind of syntax id=123, there is a good chance the underlying code will use 123 as input to some kind of dynamic SQL statement.  Unless the underlying code is written to prevent SQL injection, you can gain additional information from the database (or cause harm) by simply adding additional information to the url directly in the browser window.

Have a look at SQL Injection Walkthrough

What is SQL Injection?
It is a trick to inject SQL query/command as an input possibly via web pages. Many web pages take parameters from web user, and make SQL query to the database. Take for instance when a user login, web page that user name and password and make SQL query to the database to check if a user has valid name and password. With SQL Injection, it is possible for us to send crafted user name and/or password field that will change the SQL query and thus grant us something else.

What do you need?
Any web browser.

Try to look especially for URL that takes parameters, like:

http://duck/index.asp?id=10

There are numerous examples of SQL injection in the post I referenced above.


What is DBCC66 and DBCC63 files Tried to get some clarity but can't find any thing googling or searching  in forums.

We got a couple of files in the Data folder of one of our MS SQL servers

databasename.ndf_MSSQL_DBCC66
databasename.ndf_MSSQL_DBCC63

Can anyone tell me what these files are?

These are the sparse files checkdb makes while running: https://blogs.msdn.microsoft.com/jorgepc/2010/11/25/what-are-sparse-files-and-why-should-i-care-as-sql-server-dba/


is there some way to do this sql script, in mysql? This script was make in postgres, but I don't find their way to make it in mysql
DO
$do$
BEGIN
  IF EXISTS (SELECT FROM pg_database WHERE datname = 'example_work_db') THEN
    CREATE USER example_work_srv WITH ENCRYPTED PASSWORD 'P4ssw0rd.';
    GRANT CONNECT ON DATABASE example_work_db TO example_work_srv;
    GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO example_work_srv;
  END IF;
  IF EXISTS (SELECT FROM pg_database WHERE datname = 'example_test_db') THEN
    CREATE USER example_test_srv WITH ENCRYPTED PASSWORD 'P4ssw0rd.';
    GRANT CONNECT ON DATABASE example_test_db TO example_test_srv;
    GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO example_test_srv;
  END IF;
  IF EXISTS (SELECT FROM pg_database WHERE datname = 'example_live_db') THEN
    CREATE USER example_live_srv WITH ENCRYPTED PASSWORD 'P4ssw0rd.';
    GRANT CONNECT ON DATABASE example_live_db TO example_live_srv;
    GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO example_live_srv;
  END IF;
END
$do$;

Thank you in advance for the help

You should only change create and grant statements, everything else is ok.
CREATE USER example_work_srv IDENTIFIED BY 'P4ssw0rd.';
GRANT SELECT, INSERT, UPDATE ON public.* TO example_work_srv;


Using hard-coded host name in logon trigger I need to write a trigger which prevents certain users from logging into the production database.
We use Active Data Guard.
If I hardcode the Linux hostname in the trigger to verify the host is production, then in case of "Role Change", the trigger may not work.
Correct?

This is a three-tier system. The end users communicate via an application server, which will use a generic name.
Here is a solution I had been contemplating implementing:

Have a table which will check for combination of user + server + database hostname (name of machine where database is hosted).
Have a DB_ROLE_CHANGE trigger which will get activated if the production now runs from standby
The DB_ROLE_TRIGGER will modify the table so that the database hostname now reflects new production server hostname
The APPSERVER and APPUSER will match OK. But since the hostname is now production - it will disallow login.

If you only want users to connect to the standby then check the db_role value from v$database.  The primary will be PRIMARY and the standby wont (it'll be PHYSICAL STANDBY or similar).

Add a check into your trigger to bump them.

...
SELECT database_role INTO db_role FROM v$database;
IF db_role = 'PRIMARY' THEN (deny those users a connection)
..


Connecting to Oracle Database with ODBC/Python We've recently installed a new piece of software that uses an Oracle Database to store the data. My goal is to connect to that database and pull the data from the back end to do analysis on. I'm having the world's most difficult time connecting to it.

I need to access it two ways:

ODBC - I would like to setup an ODBC connection in Windows 7, so that I can pull the information using Excel. I know how to setup the connection, but I cannot for the life of me manage to setup the Oracle Drivers correctly. All the links from documentation for this piece of software go to dead/changed pages. Apparently the Oracle website has changed since this documentation was written.
Python - From what I can tell, cx_Oracle seems to be the best module, but again I can't seem to set it up correctly. And again, most documentation refers to sections of the Oracle website that has since changed. I'm not sold on using cx_Oracle if someone has a better recommendation.

Thanks for looking over this, and I appreciate your help.

Are you installing the 32 bit drivers or the 64 bit drivers on Windows 7?

I have to install the 32 bit drivers on all of our Windows 7 machines (even those running 64 bit).

After that I have to set up ODBC using:   c:\windows\syswow64\odbcad32.exe  instead of through the control panel.

Your issue may very well be different.


Postgres collate example in select? I would like to comprehend the different behavior of like and =. Sadly, I can not reproduce the following simple example thus that both queries return a different result:

SELECT 'ä' LIKE 'ae' COLLATE "de_DE.utf8";
SELECT 'ä' = 'ae' COLLATE "de_DE.utf8";

both return false.

What am I doing wrong?

PostgreSQL does not support = or LIKE on COLLATE. This is because internally index ordering uses = and so even if the collation returns that they're equal PostgreSQL falls back to binary equal. This is documented,

  Note that while this system allows creating collations that “ignore case” or “ignore accents” or similar (using the ks key), PostgreSQL does not at the moment allow such collations to act in a truly case- or accent-insensitive manner. Any strings that compare equal according to the collation but are not byte-wise equal will be sorted according to their byte values.

PostgreSQL also doesn't support Unicode collation in character classes.


select where index + other field Learning indexing

I have made an index, consisting of usergroupid and birthdate

I understand that queries will then benefit from indexing when searching usergroupid and/or birthdate

But what happens if I throw in other fields in the query?

SELECT * FROM mytable WHERE usergroupid = 54 AND (birthdate BETWEEN '1980-01-01' AND '1983-01-01') AND cityid = 100

Will the extra "AND cityid = 100" mess up the indexing, or will it be seen as something to be found quickly AFTER handling the index?

The extra predicate wont mess up the indexing. Most likely your index will be evaluated first and any rows that are found will be evaluated against the predicate cityid = 100.

On the other hand, assuming your index is defined as (usergroupid, birthdate). If you remove usergroupid = 54 from your query, the index won't do any good and you will have to evaluate all rows in the table against:  birthdate BETWEEN '1980-01-01' AND '1983-01-01') AND cityid = 100


Extract column definitions from an IXF file of unknown origin Suppose someone exported the table PROPERTIES using the command:

 CALL SYSPROC.ADMIN_CMD( 'EXPORT TO /tmp/properties.ixf OF IXF
    MESSAGES ON SERVER select * from properties' );

Since then the column order has changed, some were dropped, some added, but I need to load data from that IXF file into the columns that still exist. I don't know what columns the file contains. Running IMPORT without mapping them explicitly will fail.

I can only think of loading the file into a temporary table, then create a proper mapping:

CALL SYSPROC.ADMIN_CMD( 'IMPORT FROM /tmp/properties.ixf OF IXF
    MODIFIED BY FORCECREATE ROWCOUNT 0 MESSAGES ON SERVER CREATE INTO TEMP12345' );

Problems:

IMPORT command options CREATE and REPLACE_CREATE are deprecated
ROWCOUNT 0 not honored in this mode and the data is still loaded from a potentially big file

The IXF format is documented in the manuals. Conceivably, if other methods don't work as you would like them to, you could write a script to extract column definitions.


InnoDB engine seems lock the table automatically in Mysql5.7 I have a table named Students like:

mysql> select * from students;
+----+-------+---------+
| id | name  | classId |
+----+-------+---------+
| 1  | Kris  |       7 |
| 2  | Tom   |       7 |
| 3  | Mary  |       2 |
| 4  | Leon  |       2 |
| 5  | Mario |       3 |
+----+-------+---------+
5 rows in set (0.00 sec)

I execute the following sql statement, but don't give the commit statement:

set autocommit = 1; start transaction; update students set classId = 1 where id = 1;

After that I will open another session and execute the following sql statement:

set autocommit = 1; start transaction; update students set classId = 1 where id = 2;

Now, the whole shell screen will block and not respond. After a while it will give me an error, similar to this one:

1205 Lock wait timeout exceeded; try restarting transaction

So, I am confused as to why does the InnoDB engine give me a row-level lock on the table? In this case, why does MySQL block my updates to another row?

I apologize for the other answer you were given that just copied and pasted from the manual without being related to your question.

This is a common misconception- Innodb does not do full table locks to ensure isolation between transactions- however, it does apply row and gap locking by default to assure no phantom reads happen. Because your table is unindexed the only (inefficient) way to do gap locking is to setup individual lock in every row and every gap between ( for example if you try to insert a new row with id 0, it should get blocked too.

You have two options: create an index on id (probably a primary key is the right way) so only a single row is actually locked thanks to the new index, or change your default isolation level to read committed (SET SESSION transaction_isolation='READ-COMMITTED';).

This is a common question here on dba.stackexchange so here you have a more detailed explanation I wrote on another similar question: MariaDB - "ERROR 1205 ... Lock wait timeout exceeded; ..." when doing multiple parallel UPDATEs affecting different rows

However, in your case I strongly recommend you to go the route of creating the index.


How to find the count of words (all and unique) in a column row-wise? I have a column in my Postgresql database named Description which contains text. For some analytics purpose, I want to find total number of words (seperated by spaces) - all words and unique words - in that description and set those counts in columns word_count and unique_word_count. For eg.

         id         |  Description
    ----------------+---------------------------------------
         1          |  What a great day
         2          |  This is a product. It is useful

I want to have following output:

         id  | word_count | unique_word_count |  Description
    ---------+------------+-------------------+---------------
         1   |  4         | 4                 | What a great day
         2   |  7         | 6                 | This is a product. It is useful

The unique_word_count for id = 2 is 6 because the word is has been repeated 2 times.

To solve your issue I did the following:

CREATE TABLE wordcount (id SERIAL NOT NULL PRIMARY KEY, description TEXT NOT NULL);

Populate it:

INSERT INTO wordcount (description) VALUES ('What a great day');
INSERT INTO wordcount (description) VALUES ('This is a product. It is useful');

Then performed the following query (fiddle available here):

WITH arranged AS
(
  SELECT id, UNNEST(STRING_TO_ARRAY(
                   REGEXP_REPLACE(description,  '[^\w\s]', '', 'g'), ' '))
                   AS word, description
  FROM wordcount
)
SELECT a.id, COUNT(a.word), COUNT(DISTINCT(a.word)), a.description
FROM arranged a
GROUP BY a.id, a.description;

Result:

id  Word_Count  Unique_Word_Count   Description
 1           4                  4   What a great day
 2           7                  6   This is a product. It is useful

If you're new to all this, I would advise you to go through the fiddle here which shows the development of my thought processes which led to the query above.

I would advise you to put this logic into an onInsert/onUpdate trigger - it's probably fairly computationally intensive. This means the computation is only performed once. Version 12 will allow generated columns (one of the few areas where MySQL is superior to PostgreSQL) which could be a good solution.


Deleting expired SQL server database backup file on data domain using PowerShell I am backing up the database on data domain however I'll have to delete an expired backup using following command. It's an emc databoost agent app for Microsoft sql server.

I am trying to convert the command into powershell so I can run the command to delete expired backups from multiple clients in a loop. That way I can avoid running the command on each individual client.

I created following script and $clientname is hardcoded, but I would like to pass multiple server names as $clientname in loop.

$FileExe = "C:\..\bin\ddbmexptool.exe"
$dbtype = "mssql"
$dduser = "DDBOOST_USER=dduser"
$ddhostpath = "DEVICE_PATH=DDHOSTMSSQL"
$devicehostname = "DEVICE_HOST=ddhost.domain.com"
$clientname = "CLIENT=server1.domain.com"

& $FileExe -n $dbtype -a $dduser -a $ddhostpath -a $ddhostpath -a $devicehostname -a $clientname -b "6 weeks ago" -e "2 weeks ago"

Something like this:

$FileExe = "C:\..\bin\ddbmexptool.exe"
$dbtype = "mssql"
$dduser = "DDBOOST_USER=dduser"
$ddhostpath = "DEVICE_PATH=DDHOSTMSSQL"
$devicehostname = "DEVICE_HOST=ddhost.domain.com"
$servers = "server1","server2","server3"

foreach ($server in $servers)
{
  $clientname = "CLIENT=$server.domain.com"
  & $FileExe -n $dbtype -a $dduser -a $ddhostpath -a $ddhostpath -a $devicehostname -a $clientname -b "6 weeks ago" -e "2 weeks ago"
}


Are auto incrementing identities always given in the order of inserts in SQL Server? If I create a simple table like this:

CREATE TABLE SimpleTable (
     [Id] INT IDENTITY(1,1) NOT NULL,
     [Value] VARCHAR(10) NOT NULL,
     CONSTRAINT [PK_Email] PRIMARY KEY CLUSTERED
     (
         [Id] ASC
     ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

And then I insert into the table 100 times, within the same transaction, like this...

TRUNCATE TABLE SimpleTable -- ensure reset identity seed

BEGIN TRAN

INSERT INTO SimpleTable(Value) VALUES('First')
INSERT INTO SimpleTable(Value) VALUES('Second')
INSERT INTO SimpleTable(Value) VALUES('Third')
INSERT INTO SimpleTable(Value) VALUES('Fourth')
/* ... etc */
INSERT INTO SimpleTable(Value) VALUES('Hundredth')

COMMIT TRAN

Is there any guarantee that the inserted rows will be given identity values in the order in which the insert statements are executed?

More specifically, if I run this query after the inserts...

SELECT * FROM SimpleTable ORDER BY 1

Will I always see this...

Id    Value
----------------------------------------------
1     First
2     Second
3     Third
4     Fourth
... etc
100   Hundredth

Or, does SQL not guarantee this?

If this is the only transaction on that table, then yes.  However, another transaction could also do an insert at the same time, and be somewhere in between 1-100, unless you use SERIALIZABLE isolation.
From IDENTITY (Property) (Transact-SQL) in the product documentation:

The identity property on a column does not guarantee the following:

Uniqueness of the value – Uniqueness must be enforced by using a PRIMARY KEY or UNIQUE constraint or UNIQUE index.

Consecutive values within a transaction – A transaction inserting multiple rows is not guaranteed to get consecutive values for the rows because other concurrent inserts might occur on the table. If values must be consecutive then the transaction should use an exclusive lock on the table or use the SERIALIZABLE isolation level.

Consecutive values after server restart or other failures –SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert. If gaps are not acceptable then the application should use its own mechanism to generate key values. Using a sequence generator with the NOCACHE option can limit the gaps to transactions that are never committed.

Reuse of values – For a given identity property with specific seed/increment, the identity values are not reused by the engine. If a particular insert statement fails or if the insert statement is rolled back then the consumed identity values are lost and will not be generated again. This can result in gaps when the subsequent identity values are generated


Mysql backup strategies? We have a innodb mysql database so we are lost with lots of different mysql backup strategies. Some talk about mysqldump some talk about third part tools. Some of the tables we also plan to run partitions. Any good tool to help for this task?

Today there's hardly any discussion (as I see it, of course), with regard to InnoDB backup. Xtrabackup by Percona is a widespread, feature rich & stable solution which offers:

Hot backups (no locks placed on database)
Incremental backups (done by backing up pages as opposed to statements)
I/O throttling (allowing to limit backup I/O as as to alleviate the load from the machine/disks)
Backup is consistent at time of end of backup (as opposed to time when backup started).

Xtrabackup derives from InnoDB Hot Backup, nowadays called MySQL Enterprise Backup. There's additional wrapping scripts which allow for the extra featureset.

Smaller databases may benefit from the common mysqldump. It really depends on your database size and traffic. mysqldump, event with --single-transaction, places a high load on the DB server, and makes for accumulating locks. It is not as hot as you might think (see this post of mine regarding backup temperatures).

I use LVM snapshots quite a lot. The great advantage is that these are DB ignorant, so no DB-related bugs affect this type of backup. The great disadvantage is an overly increased I/O. Check out mylvmbackup.

If you have ZFS or another file system which supports snapshots, that may be a great solution. If you have a storage device which supports snapshots, that's great too. All snapshots are DB ignorant, though you often want to script this a-la mylvmbackup so as to catch the point in time (DB-wise) for which backup is done.


Converting Python's date format to a MySQL First of all I hope this is the right place to ask.
I have a Python script which parses data from a few links. In the parsers I parse also the date of the last modified article.

This is the output of the date in the code;

2012-08-07T09:00:00 # Year, month, day, hour, minute, seconds

I have to throw this output to a Mysql database with the type DATETIME
When I throw this to the database, I check the field later on, I only have;

0000-00-00 00:00:00

(Although I am using SQLALchemy, it gives me the warning when inserting to the table);

/usr/lib/python2.7/site-packages/sqlalchemy/engine/default.py:331: Warning: Out of range value for column 'cdate' at row 1
  cursor.execute(statement, parameters)
/usr/lib/python2.7/site-packages/sqlalchemy/engine/default.py:331: Warning: Out of range value for column 'mdate' at row 1
  cursor.execute(statement, parameters)

Well, clear enough I am not putting the right content inside, so I wonder if there is any converter in Mysql or you know something in Python that will work this out for me.
I guess formating the whole string will be a bit of a pain and "useless" if there is something like that already.

Cheers.
- Ephexeve

I found a solution for this.

As you can see the time format is;

2012-08-07T09:00:00

I could easily strip the "t" but that wouldn't work and could(?) give me some trouble. I found out that the lib I was using (feedparser) could also return me a Python time object, looking like this:

time.struct_time(tm_year=2012, tm_mon=8, tm_mday=7, tm_hour=19, tm_min=0, tm_sec=22, tm_wday=1, tm_yday=220, tm_isdst=0)

Basicly.. a time.struct_time type.
With an example, you can see what I have done (examples made with iPython)

In [83]: date
Out[83]: time.struct_time(tm_year=2012, tm_mon=8, tm_mday=7, tm_hour=15,tm_min=51, tm_sec=19, tm_wday=1, tm_yday=220, tm_isdst=0)
In [84]: date = datetime.datetime(*date[:-3]).strftime("%Y-%m-%d %H:%M:%S")

In [85]: date
Out[85]: '2012-08-07 15:51:19'

Voila!


Is it okay to rename the MySQL data files (FRM) manually? Recently I realized the case sensitivity issues in MySQL. I wanted to convert my upper case table names to lower case using this solution but I forgot to do Step 1, and completed the remaining steps. Now I couldn't access my tables that are in upper case. Renaming the tables using

mysql>  rename table BOOK to book;
ERROR 1017 (HY000): Can't find file: './las_data_3_dt2014/book.frm' (errno: 2)

returns error.

I'm unable to use mysqldump on my database.

mysqldump: Got error: 1146: Table 'las_data_3_dt2014.adjustment_type' doesn't exist when using LOCK TABLES

I want to know if it's okay to rename the upper case .FRM files to corresponding lower case files, so that I can run the rename commands and access the data again.

change lower_case_table_names back again

Then you should be able to access your tables.

But yes, as long as they as MyISAM tables you CAN rename them. Best to do it while mysql is shutdown. Need to change the MYD and MYI files too.


SQL Server FCI installation with Mount Point permissions issues Please help me get past this installation issue.

I am trying to install a SQL Server 2016 (SP1 slipstreamed) Failover Cluster Instance (FCI) on two Windows Server 2012 R2 VMs.

I have 3 cluster disks.

Data drive: E:\
Log mount point: E:\MSSQL\Log\L1
Tempdb mount point: E:\MSSQL\Data\Tempdb1

I first attempted to install directly to these root directories. After failing, I created subdirectories to install to. The service account and myself are local admins to the VMs and I explicitly granted full control on each of the subdirectories. Finally, I tried to ignore the mount points, for now, and just install to a subdirectory on E:. All attempts failed with the below error.

  Detailed results:   Feature:                       Database Engine
  Services   Status:                        Failed: see logs for details
  Reason for failure:            An error occurred during the setup
  process of the feature.   Next Step:                     Use the
  following information to resolve the error, uninstall this feature,
  and then run the setup process again.   Component name:
  SQL Server Database Engine Services Instance Features   Component
  error code:          0x84CF0004   Error description:             While
  updating permission setting for folder 'E:\MSSQL\Data\TempDB1\System
  Volume Information' the permission setting update failed for file
  'E:\MSSQL\Data\TempDB1\System Volume
  Information\ResumeKeyFilter.Store'. The folder permission setting were
  supposed to be set to
  'D:P(A;OICI;FA;;;BA)(A;OICI;FA;;;SY)(A;OICI;FA;;;CO)(A;OICI;FA;;;S-1-5-80-419818685-2113908795-3893829424-1849583840-1690709397)'.
  Error help link:
  http://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=13.0.4001.0&EvtType=0x88792597%400xBB814387&EvtType=0x88792597%400xBB814387

Below is an anonymized version of the config file being used for install.

;SQL Server 2016 Configuration File
[OPTIONS]

ACTION="InstallFailoverCluster"
SUPPRESSPRIVACYSTATEMENTNOTICE="False"
IACCEPTROPENLICENSETERMS="False"
IAcceptSQLServerLicenseTerms="True"
ENU="True"
QUIET="False"
QUIETSIMPLE="True"
UpdateEnabled="True"
USEMICROSOFTUPDATE="False"
FEATURES=SQLENGINE,REPLICATION,FULLTEXT,DQ,CONN,BC,SDK,SNAC_SDK
UpdateSource="MU"
HELP="False"
INDICATEPROGRESS="True"
X86="False"
INSTANCENAME="InstName"
INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"
INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server"
INSTANCEID="InstName"
INSTANCEDIR="C:\Program Files\Microsoft SQL Server"
FAILOVERCLUSTERDISKS="Cluster Disk 7"
FAILOVERCLUSTERGROUP="SQL Server (InstName)"
FAILOVERCLUSTERIPADDRESSES="IPv4;10.10.10.17;Cluster Network 2;255.255.255.0"
FAILOVERCLUSTERNETWORKNAME="abc-123-IN"
AGTSVCACCOUNT="DOMAIN\sqlsvc-abc-123"
COMMFABRICPORT="0"
COMMFABRICNETWORKLEVEL="0"
COMMFABRICENCRYPTION="0"
MATRIXCMBRICKCOMMPORT="0"
FILESTREAMLEVEL="0"
SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"
SQLSVCACCOUNT="DOMAIN\sqlsvc-abc-123"
SQLSVCINSTANTFILEINIT="True"
SQLSYSADMINACCOUNTS="DOMAIN\AdminGroup"
SECURITYMODE="SQL"
SQLTEMPDBFILECOUNT="8"
SQLTEMPDBFILESIZE="8"
SQLTEMPDBFILEGROWTH="64"
SQLTEMPDBLOGFILESIZE="1024"
SQLTEMPDBLOGFILEGROWTH="128"
INSTALLSQLDATADIR="E:"
SQLBACKUPDIR="E:\MSSQL\Backup"
SQLUSERDBDIR="E:\MSSQL\Data"
SQLUSERDBLOGDIR="E:\MSSQL\Log"
SQLTEMPDBDIR="E:\MSSQL\Data"
SQLTEMPDBLOGDIR="E:\MSSQL\Data"
FTSVCACCOUNT="NT Service\MSSQLFDLauncher$InstName"

You may need to set permissions on the mount point volume instead of at a folder level.  I suspect this is the issue you're running into.

From Guidelines for Setting SQL Permissions on Mount Point Folders (highlighting is Microsoft's):

  Gotchas

  Unfortunately, it is still possible to set/view permissions on the
  mount-point root folder via Windows Explorer, which can lead to
  unexpected results because the permissions of the mount-point root
  folder may seem valid and you can see “proper” inherited permissions,
  however these are not the permissions applied to the mounted volume.

  https://connect.microsoft.com/SQLServer/feedback/details/569895/installation-of-sql-server-2008-r2-on-mount-points-fails

  Guidelines

  It is recommended that you do not place any files directly in the mount-point root folder. This will make permissions management much
  simpler, because the tendency is to always check the folder
  permissions, which in this case is misleading. Instead, create a
  subfolder under the mount-point root folder, and set the proper
  permissions to that subfolder.  Since the subfolder is a regular
  folder, the folder permissions you observe and set are indeed the
  permissions being applied. So using the previous example, you would
  want to create a new folder: D:\FolderForVol3**SubfolderXYZ**. Now,
  set your folder permissions against that new SubfolderXYZ folder
  as you normally would.
  If you absolutely must place items directly in the mount-point root folder (Not the recommend approach), then you will need to set
  volume permissions, not folder permissions.  Recall, that this is
  because the mount-point root folder permissions are not the
  permissions which will actually get set on the mounted volume (because
  the mount-point root folder is not a real folder). You can set volume
  permissions as follows:

  Start->run->diskmgmt.msc (View the properties of a volume  http://technet.microsoft.com/en-us/library/cc740097.aspx )
  Select the volume->Properties->Security tab

  If you are adding a new folder for SQL to use, be aware of the required permissions for SQL access:

  Beginning with SQL Server 2012 permissions are assigned to the per-service SID for each of its services.
  http://msdn.microsoft.com/en-us/library/jj219062.aspx
  Configure Windows Service Accounts and Permissions  http://msdn.microsoft.com/en-us/library/ms143504.aspx

The way I had to solve this was by using the cacls.exe utility.  Detailed instructions for it can be found here.  I suspect your command to grant full permission to a user would be as follows:

cacls E:\MSSQL\Log\L1 /M /E /G YourUserName:F
cacls E:\MSSQL\Data\Tempdb1 /M /E /G YourUserName:F

You'll want to run this for your account and the SQL Server Service Accounts or group(s) as well.

Final note here, it is imperative that you include the /E flag, otherwise it will overwrite permissions on that path.  This behavior carries additional side effects of ulcers and heavy drinking, so make sure you don't forget the edit flag when running this command.

Another option is to delete the drives and repartition them from scratch.  They're still likely messed up from the original attempt at setting up security via Windows Explorer (e.g. check out the Gotcha clause from the first MS article).

In this case if you still want to write to the root folder on the mount path, you'll want to use the cacls utility.  I would also suggest setting up three sub folders on E:\ such as Data, Logs, and TempDB and then install everything to the sub folders.  Don't nest the mount points into a directory you'll install files to.


How do hierarchical databases records link together? If I had a hierarchical database containing 3 tables: course, students, grades

Course would be the root table and may have 10 course records in there.

Each course has its own selection of students, if the record doesn't contain keys like a relational database, how does it know which records for the students table are for which course?

Does it work like this or am I mistaken:

Courses Table:
Name : Length : Teacher
Example : 1 : Sir Example
Example2 : 1 : Sir Test

If I wanted to view the students in the example course, I would select this and it then shows the students table for this course.

It would be like the Windows file system, the records work like folders except display the data and are able to be opened to then show the children tables.

Course 1 > Example > Students

My experience was with IBM IMS. The root held file offset pointers to locate its children, which in turn held pointers to their children, and so forth. The nearest equivalent in the relational world is Row Identifiers (RIDs) which are sometimes used to link the leaves of BTree indexes to the table row.

The analogy with Windows file system is not a bad one, in terms of the nesting and how access to lower items is through higher ones. The idea of a table doesn't really work, however. It may be better to think of it like a JSON document in a NoSQL store that has been shreaded by the DBMS and then internally linked through file pointers.


How to create read-only user for schemas which can be created in the future? SO and Internet contains a lot of answers to question how to create read only user (for example see this article), BUT all of them promote the following way:

Create user
Grant USAGE privilege to all existing schemas
Grant SELECT privilege to all existing tables in all existing schemas
Grant default SELECT privilegie to all tables (which can be created in the future) for all existing schemas

But what to do if some schemas can be created in the future too?

I mean I want to grant read only permissions to these schemas (and tables in these schemans) created in the future without any additional configuration. Is it possible?

You can modify template1 to grant read only permissions to all future schemas.

/connect template1
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO username;

Now all future CREATE DATABASE statements will also have the read only permissions applied to them.

CREATE DATABASE dbname; // is the same as CREATE DATABASE dbname TEMPLATE template1;


Automatic creation of unique Alphanumeric ID? All the accounts in my database have a unique ID, that begins with the letter "G" (always), and has a 3 digit number following (e.g. "G026", "G123","G005","G999", etc.)

When a user is inputting new-account information through a form, I would like the unique ID to be auto-generated. So I would like a unique "G000" formatted number that is NOT already taken up in the list of users. I have tried a few things, but am having a lot of trouble getting it to work.

Does anybody have any ideas of a module I could implement? I'm thinking of making a module that generates these numbers, and calling this module in the "Default Value" field.

THANK YOU!!

This will work. There may be something more elegant but this should get you started.

 Public Function GetUID() As String
      GetUID = "G" & Right("000" + CStr((CInt(Right(DMax("[YourIDField]", "TableContainingField"), 3)) + 1)), 3)
 End Function


MYSQL crashes on XAMPP while working with drupal Good day,
just today morning i was using drupal to add some content, it crashed and mysql shutdown unexpectedly at xampp!!
last thing i was doing was inserting a data into a field with "auto complete entity reference"..but after inserting, it gave me an error in red: "no data exist in entity reference", next thing i did to fix my insertion, it got this error for mysql crashing!
i tried to restarted it many time, but each time it started, it doesn't respond to anything, then shuts down again.
sometimes i was hardly able to logon to phpmyadmin, check the database, (all looed ok to me) but before going deep, it crashed again!
(its been just less than 1 min time, and slow response before the crash!)
i tried writing any sql statment(i thought it has something to do with drupal cache, so i wanted to delete their data), but it hangs and eventually mysql shuts down again.
here's a screen shot of drupals' initial error:

but after trying to refresh and restart mysql at xampp, i get this error:

i looked around google and stack exchange, and found that i need to check the error log file at mysql..after long hours of determining the problem i THINK i found my problem is with a corrupt table:
(i couldnt post all lines of error reporting here cuz they're too long)
so from between the lines i found this:

InnoDB: table  "bitnami_drupal7"."field_revision_field_citizenship"iis corrupted. Please drop the table and recreate.
[Warning] InnoDB: Cannot open table bitnami_drupal7/field_revision_field_citizenship from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.

i was surprised that a whole service is shutdown because a simple table, how come? and why would a table be corrupted by itself? i mean i was just minding my own business with drupal contents and adding setting and thing(not even coding, nothing), how could such a thing happen?
i found some solutions, (the one with increasing the packet size didnt work btw, cuz it happened before and it solved, but now it seems different), here are some solutions which say:
(1).

[mysqld]
innodb_force_recovery = 1

or
(2).

Delete: "ibdata1" file at "C:\xampp\mysql\data"

so, the reason im asking here is that, are those methods safe?  will they not affect my drupal tables?
it would be disastrous for me if anything goes wrong, because 1st solution says "FORCE" which sounds scary and could end up corrupting things based on what i read. (im not entirely sure though)
also, 2nd solution which is deleting that file (im not sure wht that file is or does), does that file contain any database stuff that affect my database files? or is it only for configuring purpose? i mean maybe some people are not having serious stuff in database so they tried it and worked for them.
but for me its very critical if anything bad happens to my data from those solution.
I'd like to know if those solution wont affect my data.
I'm pretty new to this database stuff, so im not sure..thats why im trying to take a cautious step to ask here.
Please guide me from your experiences.
Thanks

ok, i best solution which suited me well.
for being cautious in choosing the above solutions provided in my question above..
(cuz the solutions didn't guarantee 100% that my data would be intact), i made a backup for my drupal database folder just in case.
chose solution (2), which is adding the force_recovery..but no luck in fixing the problem..
so i went to the location of the corrupted file in "C:\xampp\mysql\data\bitnami_drupal7", and moved the corrupted file to another folder (NOTE: i did not delete the corrupted file).
then started mysql service, and viola! my drupal was back to life again.
and i was able to access phpmyadmin with sql queries.
(back then, mysql couldn't execute sql queries because it was in a state where its about to crash).
i went straight to the cache tables of drupal to clear them..
(back then, i wanted to clear drupals' cache since the error started, cuz ever since i knew that mysql crashes because of a table in drupal; but mysql couldn't have enough time to execute sql queries in phpmyadmin, again cuz of the the crash).
hence, after clearing all drupal cache at "bitnami_drupal7" with this code:

TRUNCATE TABLE cache;
TRUNCATE TABLE cache_block;
TRUNCATE TABLE cache_bootstrap;
TRUNCATE TABLE cache_field;
TRUNCATE TABLE cache_filter;
TRUNCATE TABLE cache_form;
TRUNCATE TABLE cache_image;
TRUNCATE TABLE cache_menu;
TRUNCATE TABLE cache_page;
TRUNCATE TABLE cache_path;
TRUNCATE TABLE cache_token;
TRUNCATE TABLE cache_update;

then, i replaced the corrupted file back again to try if it becomes recognized or not, and started mysql..and it worked!!
mysql never crashed again and complain about that file being corrupted anymore, and that saved me from deleting or affecting any data.


Unable to write to ndf after shrink emptyfile I am doing some file balancing practice/experiments on AdventureWorks and pages stopped writing to the ndf file (AdventureWorks2014_Data2.ndf).

Early tests were working as expected.  Proportional fill, spreads new pages across multiple files in file group.

Several tests I was running should have written pages to *Data2.ndf but did not.

Getting very frustrated I tried to shrink the mdf to ndf and if failed.

  Msg 2556, Level 16, State 1, Line 3 There is insufficient space in the filegroup to complete the emptyfile operation.

Both mdf and ndf have 300MB allocated, this does not make sense. mdf has 187mB used, ndf has 0MB used,

There have been multiple t-log backups taken in the time span.

I created *Data3.ndf, and shrunk from mdf, everything (excpet meta data) moved to Data3.ndf, nothing goes to *Data2.ndf.

  Msg 2555, Level 16, State 1, Line 3 Cannot move all contents of file "AdventureWorks2014_Data" to other places to complete the emptyfile operation.

What is wrong with *Data2.mdf?

What I learned,

I Shrunk emptyfile back to .mdf no problem, but when I tried to shrink emptyfile back to Data3.ndf same error.

It seems once you have shrunk emptyfile from an .ndf you have to delete (Remove) it, you can't write to again.

Additionally if you try to recreate the ndf with the same name, you will not be able to, until you run a t-log backup.

Optimal, don't expect to reuse an ndf once you have shrunk emptyfile.  If you need to write back to an ndf use a different name.

If you do need (or want) to write to the same ndf name:

Delete the ndf file (On Properties GUI select file and click remove)
Run a t-log backup
Recreate (Add) the ndf file


SQL Query Returning Unwanted Results I must be setting up the And/OR portion of my query incorrectly.  In the sample dataset below, after filtering out the criteria in my Where clause only FR ni23 should be returned, however my query returns

resonance   userid

Del         er12

NULL        em23

Samit       ra23

            em23

FR          ni23

This is the syntax I attempted, but is not accurate, where does the problem lie, and what should I change to fix?

Declare @T1 Table (resonance varchar(100), userid varchar(10))

Insert Into @T1 (resonance, userid) Values
('Del', 'er12'), (NULL, 'em23'), ('Samit', 'ra23'),
(NULL, 'axel'), ('', 'em23'), ('', 'red'), ('FR', 'ni23')

Select *
FROM @T1
WHERE userid LIKE '%[0-9]%'
AND (resonance <> 'DEL' OR resonance IS NULL OR resonance <> 'Samit')

But still returns the record where resonance = ''

Select *
FROM @T1
WHERE userid LIKE '%[0-9]%'
    AND resonance not in ('DEL','Samit')

You should define it as:

Select *
FROM @T1
WHERE userid LIKE '%[0-9]%'
    AND resonance not in ('DEL','Samit','')


Limit WHERE to MAX() & COUNT() This is my current mySQL query:

SELECT e.*, MAX(m.datetime) AS unread_last, COUNT(m.id) AS unread
  FROM TAB_EVENT e
  LEFT JOIN TAB_MESSAGE m ON e.id=m.event_id
  WHERE ( m.`read` IS NULL OR m.`read` = 0)
  GROUP BY e.id
  ORDER BY m.datetime DESC, e.id ASC
  LIMIT 10;

I get: All TAB_EVENT rows which have no TAB_MESSAGE messages at all or have at least one unread one (read = BOOLEAN DEFAULT 0).

I need: All TAB_EVENT rows no matter of their messages AND the datetime of the newest unread and the count of unread messages for each TAB_EVENT.

There may be thousands of TAB_EVENT rows and many more TAB_MESSAGE rows and I'd like to avoid running one subquery for each single event.

Alternate 1: Using UNION using the query above plus another one with m.read = 1.
Drawback: Two JOINed heavy queries plus another temporary table.

Alternate 2: SELECT e.* unconditional plus another SELECT event_id, MAX(), COUNT() FROM TAB_MESSAGE GROUP BY event_id ORDER BY MAX() and merge everything within the calling Perl script.
Drawback: Loosing the ability of a server-side LIMIT.

UPDATE/RESOLUTION

Here is my final query doing the job:

SELECT e.*, m.unread_last, m.unread
FROM
TAB_EVENT e LEFT JOIN
(
    SELECT event_id,MAX(`datetime`) AS unread_last, COUNT(*) AS unread FROM TAB_MESSAGE
    WHERE `read` = 0
    GROUP BY event_id
    ORDER BY `datetime` DESC
) m
ON e.id=m.event_id
ORDER BY m.datetime DESC, e.id ASC
LIMIT 10;

The read IS NULL condition caught up events not having any messages in the original query, the read column is boolean NOT NULL.

This query now returns all events and adds the newest unread message timestamp plus the number of unread messages. Both message columns are NULL for events not having any messages which is perfectly ok (will be translated to "" for datetime and 0 for the count on the Perl side).

Thanks to RolandoMySQLDBA, my query is based on his answer.

Try joining to a subquery:

SELECT e.*, m.datetime AS unread_last, m.id AS unread
  FROM TAB_EVENT e
  LEFT JOIN (
    SELECT event_id, MAX(datetime) AS `datetime`, COUNT(id) AS id
    FROM TAB_MESSAGE
    WHERE read IS NULL OR read = 0
    GROUP BY event_id
  ) m ON e.id=m.event_id
  ORDER BY m.datetime DESC, e.id ASC
  LIMIT 10;


Solutions for reporting off of an EAV structured database? Existing setup SQL Server 2005 with standard EAV table that users want to slice and dice with a BI tool (Cognos).

Is there any hope that we can transform this data into a format that we can report off of? Google has led me to believe there is no hope. I'd like to believe that some kind of solution is out there.

Well...my solution is as follows:

I used a dynamic pivot table in a stored procedure. The stored procedure called a View that I created which denormalizes the data. I think used MS Query to hook the stored procedure up to Excel 2010. This loads it up into a nicely formatted table that allows the end user to sort and filter to their heart's content.

With 200,000 records, the query takes about 20-30 seconds to run... I pre-loaded the spreadsheet and set it to refresh in the background at set intervals.

They have it in their hands now, but not long enough to have any feedback. With all the tools I've tried and researched...this really is the only one that has met all my requirements...which is slightly frightening to me. Hope this helps anyone who stumbles across this question.


Implicit conversion between decimals with different precisions I know that when SQL Server handles an implicit conversion between types it converts the lowest priority one to the highest one.

But what is the result datatype when I perform an operation between decimals with different precisions?

The type of the result is determined by the rules set out in Precision, scale, and Length (Transact-SQL):

  The following table defines how the precision and scale of the result are calculated when the result of an operation is of type decimal. The result is decimal when either:

  Both expressions are decimal.
  One expression is decimal and the other is a data type with a lower precedence than decimal.

  The operand expressions are denoted as expression e1, with precision p1 and scale s1, and expression e2, with precision p2 and scale s2. The precision and scale for any expression that is not decimal is the precision and scale defined for the data type of the expression. The function max(a,b) means the following: take the greater value of "a" or "b". Similarly, min(a,b) indicates to take the smaller value of "a" or "b".

  * The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, it's reduced to 38, and the corresponding scale is reduced to try to prevent truncating the integral part of a result. In some cases such as multiplication or division, scale factor won't be reduced, to maintain decimal precision, although the overflow error can be raised.

  In addition and subtraction operations, we need max(p1 - s1, p2 - s2) places to store integral part of the decimal number. If there isn't enough space to store them that is, max(p1 - s1, p2 - s2) < min(38, precision) - scale, the scale is reduced to provide enough space for integral part. Resulting scale is MIN(precision, 38) - max(p1 - s1, p2 - s2), so the fractional part might be rounded to fit into the resulting scale.

A convenient quick way to see the resulting type is to use SQL_VARIANT_PROPERTY:

DECLARE @V1 decimal (9, 6) = 123.456789;
DECLARE @V2 decimal (7, 2) = 12345.67;

SELECT
    [BaseType] = SQL_VARIANT_PROPERTY(@V1 + @V2, 'BaseType'),
    [Precision] = SQL_VARIANT_PROPERTY(@V1 + @V2, 'Precision'),
    [Scale] = SQL_VARIANT_PROPERTY(@V1 + @V2, 'Scale');

Output:

╔══════════╦═══════════╦═══════╗
║ BaseType ║ Precision ║ Scale ║
╠══════════╬═══════════╬═══════╣
║ decimal  ║        12 ║     6 ║
╚══════════╩═══════════╩═══════╝

db<>fiddle demo


Putting the database in backup mode before making file system changes We are planning to make a small change in the Linux file system. To be on the safe side, I thought I would take a SAN-level snapshot of all the drives beforehand. I was thinking I should put the Oracle database in backup mode:

Alter system archive log current;
Alter database begin backup;
-- after the SAN snapshot finish--
Alter database end backup;
Alter system archive log current;

Someone said this command is deprecated from 9i.

My question is, if I go ahead and use this, will it be ok? Or is there another way to do this?

Note: I don't want to use RMAN's full backup as this will take 6 hours.

The method you describe is ok. This method was the backup method used before Oracle introduced RMAN and is still valid. It is still used by tools that do snapshot backups (like Netapp's backup tool for Oracle) and I often use this in similar situations.
You also should make a controlfile backup if all the controlfiles are on these disks.


How to promote an existing index to primary key in PostgreSQL I know how to make a primary key within a table, but how do I make an existing index a primary key?  I'm trying to copy an existing table from one database to another. When I show the table, the index at the bottom is in this form:

"my_index" PRIMARY KEY, btree (column1, column2)

I have created the index with:

CREATE INDEX my_index ON my_table (column1, column2)

But I do not know how to make it the primary key ...

UPDATE:  The version of my server is 8.3.3

You'd use ALTER TABLE to add the primary key constraint. In Postgres you can "promote" an index using the "ALTER TABLE .. ADD table_constraint_using_index" form

Note, the index need to be unique of course for a primary key

ALTER TABLE my_table
    ADD CONSTRAINT PK_my_table PRIMARY KEY USING INDEX my_index;


MySQL - An UPDATE from a SELECT The more I learn about about the great things you can do with mysql, the more I keep pushing to learn.

I have two tables:

Tests

TestNumber (int primary key)
InactiveTestSlotBitwise (int)

TestUsers

UserId (int)
TestNumber (int - ties in with the TestNumber in Tests)
UserSlot (int the person's seating position in the test)

I had been doing the following in two stages in php, but now see that as cumbersome. I was collecting all the 'TestUsers' with the UserId = 25 and returning to the php code which then does a separate call the database and then alters the 'InactiveTestSlotBitwise' in the 'Tests' to show they had removed themselves from the Tests they were in.

However, now I think I should be doing something else instead in one call, on the lines:

UPDATE tests AS t SET
    t.InactiveTestSlotBitwise = (t.InactiveTestSlotBitwise | (1 << tu.UserSlot))
FROM
    (SELECT TestNumber, UserSlot FROM testusers
        WHERE UserId=25 AND UserSlot >= 0
    ) AS tu
    WHERE t.TestNumber= tu.TestNumber

Surely this is possible? It does not like it but does really say why. It says:

'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM (SELECT TestNumber, UserSlot ...'

Try the following:

UPDATE tests AS t JOIN testusers AS tu ON t.TestNumber= tu.TestNumber
SET t.InactiveTestSlotBitwise = (t.InactiveTestSlotBitwise | (1 << tu.UserSlot))
WHERE tu.UserId=25 AND tu.UserSlot >= 0;


INNER JOIN between 2 tables vs WHERE on one table performance Suppose I have two tables:

CREATE TABLE `doctors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `clinic_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `clinics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

The next queries returns the same data but for large tables wich is better for performance?

Query with INNER JOIN

SELECT doctors.*
  FROM doctors
 INNER JOIN clinics
    ON clinics.id = doctors.clinic_id
 WHERE clinics.id = 1;
Query only with WHERE

SELECT doctors.*
  FROM doctors
 WHERE doctors.clinic_id = 1;

The second query. But why are you comparing them?

They may return the same data but they are answering two different questions. The first retrieves all records from doctors where a record exists in clinic with id=1. The second retrieves all records from doctors with clinic_id=1 regardless of whether a record exists in clinic.

In the absence of a foreign key constraint between the two tables, the queries are not comparable.


Cannot change logical file name: The logical file name "PROD1" is already in use. Choose a different name I made an error in the naming of a physical data file. So I dropped the file from within SQL Server, and wanted to use the same logical file name again. But now SQL Server 2014 refused. So for the moment I created it as PROD0, and tried a rename afterwards that.

ALTER DATABASE [PROD] MODIFY FILE (NAME=N'PROD0', NEWNAME=N'PROD1')
Msg 1828, Level 16, State 3, Line 3
The logical file name "PROD1" is already in use. Choose a different name.

If I select:

select name,physical_name
from sys.database_files
order by physical_name

this logical filename does not appear:

name        physical_name
PROD        D:\MSSQLSERVER\Data\PROD.mdf
PROD0       D:\MSSQLSERVER\Data\PROD0.ndf
PROD2       D:\MSSQLSERVER\Data\PROD2.ndf
PROD3       D:\MSSQLSERVER\Data\PROD3.ndf
PROD4       D:\MSSQLSERVER\Data\PROD4.ndf
PROD5       D:\MSSQLSERVER\Data\PROD5.ndf
PROD6       D:\MSSQLSERVER\Data\PROD6.ndf
PROD7       D:\MSSQLSERVER\Data\PROD7.ndf
PROD_log    E:\MSSQLSERVER\Data\PROD_log.ldf

So even if I would like to drop it, I can't, because it does not exist.

If it might be important: this is part of an Availability Group (2 nodes only). I made the changes on the primary of course. The changes were correctly propagated to the secondary, so also there the file 'PROD1' is not there.

How can I succeed in renaming PROD0 to PROD1?

Edit after the answer of SQLBoy: Sorry, that did not work; same error. The logical Filename PROD1 seems to be hidden somewhere deep in the datadictionary.

Try this:

What you have to do is, first set it to another name.

ALTER DATABASE [PROD]
MODIFY FILE (NAME=N'PROD0', NEWNAME=N'PROD1Temp')
GO

The file name 'PROD1Temp' has been set.

Then change it back to PROD1 so that you can achieve your target.

ALTER DATABASE [PROD]
MODIFY FILE (NAME=N'PROD1Temp', NEWNAME=N'PROD1')
GO

Some helpful links :

'Logical file name "MyDB" is already in use' Even though the database doesn't exist

https://stackoverflow.com/questions/7534664/sql-server-script-error-database-is-already-in-use


MongoDB Production Amazon - 3 data nodes per replica set, or 2 data nodes plus a shared arbiter I have an Amazon EC2 deployment of mongodb (3.4). Things are going well, but the DB size is growing quickly. I am about to shard a large collection in order to begin horizontal scaling (total space needed is 2TB).

So either (ignoring config servers, etc):

1. Each shard is a replica set with 3 data nodes

total cost = $1,130 / mo

6x m4.large ($85 ea) + 4x 1TB SSDs ($116 ea) + 2x 1TB magnetic (to save some $$) ($78 ea)

2. Each shard is a replica set with 2 data nodes, each also using a shared arbiter

total cost = $809 / mo

4x m4.large ($85 ea) + 4x 1TB SSDs ($116 ea) + 1x arbiter (cheapest machine is $5)

Diff is $321/mo

I get the feeling that option 2, in Amazon's hosted environment, using exclusively SSDs, should be quite durable. As far as I can see, the only problem with Option 2 is that if a primary node dies and I failover to the secondary, for that period there is no backup. But I can't actually evaluate the severity of this scenario.

Could Option 2 be mitigated by  attaching a spinning disk to each data node, that will act as a backup volume?

If anyone could provide some advice from experience it would be super helpful, but any advice is appreciated.

Thanks,

Option 2 has no problems in theory; however the more limited redundancy may be a problem.

About the arbiters:

Yes, you can run multiple arbiters on a single lightweight machine. An arbiter is a very lightweight process, doing little but voting, so you can run multiple arbiters on a single box. Each one must be a separate mongod process.

About the redundancy:

If you have a 2-data-node replica set, and one of the data nodes fails, then the other one can function as primary (with the vote of the arbiter), so you have resilience there, but you no longer have redundancy.

Whether that is a severe problem or not depends on your ops, how quickly you can fix or replace the failed node. That depends on your monitoring capabilities, your ops staff competence and availability, etc; that's a decision you need to evaluate.


How to create relationship metadata between tables without a foreign key check? I use database metadata (primary keys, foreign keys, table names, etc), to generate code and some very complex queries. This metadata resides in theINFORMATION_SCHEMA. I am having problems with following (simplified) use case:

Tables

table A (with ID)
table B (with ID, ColumnA, ColumnC)
table C (with ID)

All ID Columns are unique, ColumnA and ColumnC have duplicate values

Situation

ColumnC has a subset of values from C, in other words, B will never have a value that C doesn't.

ColumnA has a non-perfect superset of values from A, in other words, B has
 exclusive values and almost all values of A, but A also rarely has exclusive values.

Problem

There is a Foreign Key between B.ColumnC referencing C.ID, which works as intended.

Now, because of the nature of Foreign Keys, there is no Foreign Key on B.ColumnA referencing A.ID, because B has values that A don't.

Question

Basically, I am having problem with some "many to many"-like relationships.

I thought about disabling Foreign Key checks and then creating a Foreign Key on B.ColumnA, but I haven't found a way to disable checks on only one specific contraint, because I still want to maintain data integrity on B.ColumnC Foreign Key.

Is there a way to create a relationship between B and A without losing data integrity and preferably without creating any additional tables or columns?

Edit: I am working with Postgres, but I will tag this with "database agnostic" to see if solutions from other vendors are portable to my case.

Adding a new table and proper foreign keys will likely be the more natural way to solve this.

In some DBMS (eg SQL SQL Server), you can create a disabled or disable an existing foreign key constraint with ALTER TABLE .. NOCHECK CONSTRAINT:

ALTER TABLE b NOCHECK CONSTRAINT [a_b_fk] ;

In Postgres, if you really don't want or not allowed to change the schema, you could create a foreign key from A to B and then disable the internal triggers associated with it. There is no way (as far as I know) to mark the foreign key constraint as "disabled" but disabling the internal triggers will have the desired effect.

Create the tables

blue=# create table a (id int primary key) ;
CREATE TABLE
blue=# create table b (id int primary key, aid int not null) ;
CREATE TABLE

Add the foreign key constraint

blue=# alter table b add constraint a_b_fk foreign key (aid) references a(id) ;
ALTER TABLE

Find the trigger names

blue=# SELECT tgname, tgconstraint
FROM   pg_trigger
WHERE  tgrelid = 'public.a'::regclass;
             tgname             | tgconstraint
--------------------------------+--------------
 RI_ConstraintTrigger_a_3657774 |      3657773
 RI_ConstraintTrigger_a_3657775 |      3657773
(2 rows)

blue=# SELECT tgname, tgconstraint
FROM   pg_trigger
WHERE  tgrelid = 'public.b'::regclass;
             tgname             | tgconstraint
--------------------------------+--------------
 RI_ConstraintTrigger_c_3657776 |      3657773
 RI_ConstraintTrigger_c_3657777 |      3657773
(2 rows)

Disable the triggers

blue=# alter table a disable trigger "RI_ConstraintTrigger_a_3657774"  ;
ALTER TABLE
blue=# alter table a disable trigger "RI_ConstraintTrigger_a_3657775"  ;
ALTER TABLE
blue=# alter table b disable trigger "RI_ConstraintTrigger_c_3657776"  ;
ALTER TABLE
blue=# alter table b disable trigger "RI_ConstraintTrigger_c_3657777"  ;
ALTER TABLE
blue=#

we can now insert values as we please, the FOREIGN KEY isn't checked

blue=# insert into a (id) values (1), (2), (3) ;
INSERT 0 3
blue=# insert into b (id, aid) values (1,1), (2,1), (4,4) ;
INSERT 0 3

Show the tables' structure

blue=# \d a
                 Table "public.a"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           | not null |
Indexes:
    "a_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "b" CONSTRAINT "a_b_fk" FOREIGN KEY (aid) REFERENCES a(id)
Disabled internal triggers:
    "RI_ConstraintTrigger_a_3657774" AFTER DELETE ON a FROM b NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del"()
    "RI_ConstraintTrigger_a_3657775" AFTER UPDATE ON a FROM b NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd"()

blue=# \d b
                 Table "public.b"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           | not null |
 aid    | integer |           | not null |
Indexes:
    "b_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "a_b_fk" FOREIGN KEY (aid) REFERENCES a(id)
Disabled internal triggers:
    "RI_ConstraintTrigger_c_3657776" AFTER INSERT ON b FROM a NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins"()
    "RI_ConstraintTrigger_c_3657777" AFTER UPDATE ON b FROM a NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_upd"()

blue=#


How to search by text in SSIS packages? Is there a way to search by text in SQL Server Integration Services packages using T-SQL statement (or other language)?

I want to check if certain table is used in any package as I am going to drop it - if the table is used I need to edit the package.

Could you advice?

Assuming you have the .dtsx files, they are just XML so you could use a simple text search of the files, something like this
https://stackoverflow.com/questions/8153750/how-to-search-a-string-in-multiple-files-and-return-the-names-of-files-in-powers


mysqldump: replace drop table in output with delete? I am using mysqldump to dump some records in a table:

mysqldump mydatabase mytable --where="field=value"

this emits script that includes drop table mytable statement.

Since I use the --where option, when I replay the script, I want to overwrite only these records that satisfy the condition field=value. I want the emitted script to delete just these records:

delete from `mytable` where field=value

is there a way to achieve this?

Specify to not use any creation SQL with --no-create-info

mysqldump mydatabase mytable --no-create-info --where="field=value"

If you want to overwrite the records on reload of the mysqldump, just add --replace

mysqldump mydatabase mytable --no-create-info --replace --where="field=value"

I suggested --replace because REPLACE is a mechanical DELETE and INSERT.

Give it a Try !!!


db2 import and user access When I import data from DB2 using db2move, the dump file comes with the data exported as "USERX.MYTABLE".

The import process seems to be fine, I see lots of:

USERX.MYTABLE n rows imported ...

But USERX is not on my database and when I try to select data using MYUSER, I always get empty results.

Also, if I run db2move and export the data from my local database, it agains export all rows for USERX.

How can I fix the access to MYUSER?

If you don't want to specify the schema explicitly, you can choose one of several options, for example:

Modify the default schema in the session, e.g. by issuing the statement SET CURRENT SCHEMA USERX.
Create an alias for the table USERX.MYTABLE in the schema MYUSER.
Before importing data, edit the file db2move.lst and replace all occurrences of USERX with MYUSER.


SQL Server Log: The database 'master4IDR' is marked RESTORING Why SQL Server log showing following message. I am dead sure, there is no such db on our production server naming "master4IDR"

{The database 'master4IDR' is marked RESTORING and is in a state that does not allow recovery to be run.}

if you are using Symantec BackupExec check out this article

How to disable the creation of the $IDR file when backing up a Microsoft SQL server


Postgres, two tables use the same sequence, how to delete one? I've just finished partitioning an existing table by 1) renaming the original table 2) creating a new partitioned tables with identical structure as the original, and 3) transferring the data from the old un-partitioned table to the new one.

The partitioning has worked just fine, no problems. The only issue is that I now have two tables with and identical field that both use the same sequence

ident integer NOT NULL DEFAULT nextval('mytable_ident_seq'::regclass)

Question - how do I remove the original table?

I can't delete the original table because in doing so Postgres attempts to drop the sequence, which causes an error because 'other objects depend upon it', and the 'other object' is my newly created, partitioned version.

The first thing I tried was to remove the reference to the sequence

 ALTER TABLE mytable_old ALTER COLUMN ident SET DEFAULT 0;

This seems to work, it returns no errors, but when I look at the properties for the field it still says it has a dependency on the sequence, and when I attempt to drop the table I get the same error. The table currently has zero rows of data, and I've dropped all the fields except for this one, but I still can't actually drop the table itself.

This isn't a critical error, the database works ok, the new version of the table is partitioned and working fine, I'd just like to remove the old table for the sake of tidiness.

You were probably using a serial data type.

PostgreSQL documentation says this is equivalent to the following:

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

Note the latest statement.

Based on https://www.postgresql.org/docs/current/sql-altersequence.html you will need to remove the "ownership" dependency:

  Specifying OWNED BY NONE removes any existing association, making the sequence “free-standing”.

Which in your case means:

ALTER SEQUENCE mytable_ident_seq OWNED BY NONE;

Or you may need to reassociate it with the proper table, using:

ALTER SEQUENCE mytable_ident_seq OWNED BY table_name.column_name;

After which you should be able to delete the table without consequences for the sequence.


Restore XAMPP database I have a complete backup of my XAMPP installation.
Unfortunately I forgot to export my database to .sql files. But as mentioned i have all .frm, .idb and ibdata1 ib_logfile* and so on.

However if I simply copy the backed up files into the /opt/lampp/var/mysql directory I get the following error:

(Switching back to the original directory created by the XAMPP installation is completely fine, though.)

  Connection for controluser as defined in your configuration failed.

and

  phpMyAdmin tried to connect to the MySQL server, and the server
  rejected the connection. You should check the host, username and
  password in your  configuration and make sure that they correspond to
  the information given  by the administrator of the MySQL server.

For my /opt/lampp/var/mysql folder I did

sudo chown -R mysql mysql
sudo chgrp -R mysql mysql

What am I missing to restore the database from the XAMPP backup?

I don't know why I didn't try this earlier, but a simple reboot resolved this issue. Note, that the simple restart of xampp and thus of mysql and apache did not work.


How do I force a Writable Materialized View instead of an Updatable one? How do I ensure that an Oracle Materialized View is created as a "Writeable" materialized view and not as an "Updatable" one?  I never want these views to push data back to the source and I want a complete refresh to overwrite anything I change.
The source problem is that one of the remote databases cannot support materialized view logs, but the tables have last modified timestamp columns.  So I'd like to attempt to keep them up to date with Merge statements filtering on that column and then occasionally do normal replication refreshes if any issues turn up.
I tried CREATE MATERIALIZED VIEW Test.SomeTable_MVW TABLESPACE SOME_DATA BUILD DEFERRED REFRESH COMPLETE FOR UPDATE AS (SELECT ... FROM SomeTable@OverThere);
But I got the error:
12013. 00000 -  "updatable materialized view must be simple enough and have a materialized view log on its master table to do fast refresh"
*Cause:    The updatable materialized view query contained a JOIN, SUBQUERY,
           UNION, CONNECT BY, ORDER BY, or GROUP BY clause, or the master
           table did not have a materialized view log.
*Action:   Make the materialized view simpler. If a join is really needed,
           make multiple simple materialized views then put a view on top of
           them. Make sure a materialized view log is created for the master
           table.

There is only one remote table.  I do not understand why this error even contains or the master table did not have a materialized view log since that's how you get a writeable materialized view.  Too bad there isn't a "FOR WRITEABLE" clause for when you know that you don't want to push data back.  And the REFRESH COMPLETE should clue it in that I don't want to push the data back.

Updatable materialized view was an Advanced Replication feature.
A writable materialized view was simply an updatable materialized view that was not a member of a materialized view (replication) group.
Desupport of Advanced Replication

Starting in Oracle Database 12c release 2 (12.2), the Advanced
Replication feature of Oracle Database is desupported.
The Oracle Database Advanced Replication feature is desupported in its
entirety. The desupport of this feature includes all functionality
associated with this feature: multimaster replication, updateable
materialized views, hierarchical materialized views, and deployment
templates. Read-only materialized views are still supported with basic
replication.

You are looking for a solution for a feature that became deprecated 7 years ago (in 12.1) and desupported 4 years ago (12.2).
The FOR UPDATE clause still exists for backward compatibility, but it is not even documented anymore.
I do not know why Oracle implemented it this way, but at this point, I would just let it go.
For the use case you described, a simple table is fine. You can do incremental refreshes with MERGE on key + timestamp, and do a complete refresh with truncate + insert ... select ....


Sql Server Installation can't find files Okay, I wasn't sure whether to ask this here or on Server Fault, but the FAQ for DBA says it includes db configuration, so I'm going with here.

I'm installing Sql Server Express 2012 on a clean Windows Server 2012 box.

The install seems to go fine right up till the very end.

I left the program install directory at C:\Program Files....
I changed the instance root directory to D:\SQLE2012ROOT\.

Install chugs along happily, and when it ends, it says setup completed, but encountered the following error:

Feature:              Database Engine Services
Status:               Failed: see logs for details
Reason for failure:   An error occurred during the setup process of the feature.
Next Step:            Use the following information to resolve the error, uninstall this feature, and then run the setup process again.
Component name:       SQL Server Database Engine Services Instance Features
Component error code: 0x851A001A
Error description:    Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.

(it also gives a URL to go to, but clicking it just takes you to a Microsoft home page that shows consumer products to buy, like Surface and XBox. Thanks MS!)

The SQL service in Service Manager is stopped, and if you try to start it, chokes with message about service-specific error code 945, which googling, says it means Sql Server cannot find the necessary files.

Looking in the Event Logs after trying to start the service shows these two errors (each shows twice, once for MSDBData.mdf and once for model.mdf):

FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\modellog.ldf'. Diagnose and correct the operating system error, and retry the operation.

and

FCB::Open failed: Could not open file e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\MSDBData.mdf for file number 1. OS error: 21 (The device is not ready.).

It would make sense that it cannot find those files, as the E: is an optical drive on the server, with no media mounted in it.

The million dollar question though is WHY it is looking for the files in that location? I've checked, and the setup DID actually create the files in the correct location under my specified instance root (D:\SQLE2012ROOT\MSSQL11.SQLE2012\MSSQL\DATA). But for some unknown reason, Sql Server is LOOKING for them under what looks like a development project build directory off the E: drive.

I looked in the registry and grepped on the C: to see if I could find where it was pointing to this wrong directory and simply re-point it, but I couldn't find anywhere where it pointed to that location.

I've already uninstalled once, and reinstalled from scratch again, and it did the same thing. The install file used was the 64 bit one that includes Management Studio, SQLEXPRWT_x64_ENU.

I've tried a number of things now, and could not get the 2012 to install. So I figured maybe just go to 2016 and see if that works. It installed but again had the same error as before, about Waiting on the Database Recovery handle. After setup concluded, I tried to start the service manually, and got the same errors in the event log about being unable to locate the MSDBDATA and MODEL files, though it has a different wrong path this time. Now it is attempting to look for those files in the directory D:\B\S2\Sources.obj.x86Release\sql\mkmastr\databases\mkmastr.proj\. I checked the startup parameters for the service in Sql Server Configuration Manager...

The file paths listed in the startup parameters ARE CORRECT.
I have no idea why it is looking for the files in this other wonky D:\B\S2... directory.

I think you are trying to start instance from a failed installation.
Un installing and reinstalling SQL Server may clear this error.

Please look into below link which deals with similar issue.

https://connect.microsoft.com/SQLServer/feedback/details/684859/during-install-set-the-default-location-of-databases-problem