Necessity of backing up tail log when restoring transaction log? I have a question about whether or not it is necessary to back the tail log up when performing a restore of a database in Full Recovery mode. I've sprawled for a good while now, and have gotten conflicting reports.

Consider this scenario in SQL Server 2012:

Full database backup scheduled at 4:00 AM every morning.
Transaction log back ups every 15 minutes from 7:00 A.M. to 6:00 P.M.

With this backup plan in place, say we want to perform a deployment against this database after the final transaction log backup has kicked off at 6:00 P.M.. We then run various tests and fill the database with data that does not need to be retained after confirmation of a successful deployment.

The deployment is successful, all tests look to be in line, and we restore back to the 6:00 PM transaction log to rid of all the data just entered. We then run a final deployment against the database. At the time of this restore, is it necessary to back the tail log up if we have no need for all the data entered following the final transaction log?

I am receiving conflicting reports and dated posts/articles.

Could anyone outline the pros/cons of not performing the tail log backup?

Thanks!

At the time of this restore, is it necessary to back the tail log up if we have no need for all the data entered following the final transaction log?

No. If your transaction log backup ran at the top of the hour as expected, and you are certain you don't need any data later than that point, it is not necessary to take a tail of the log backup.


GROUP BY with MAX versus just MAX I am a programmer, dealing with a big table which the following scheme:

UpdateTime, PK, datetime, notnull
Name, PK, char(14), notnull
TheData, float

There is a clustered index on Name, UpdateTime

I was wondering what should be faster:

SELECT MAX(UpdateTime)
FROM [MyTable]

or

SELECT MAX([UpdateTime]) AS value
from
   (
    SELECT [UpdateTime]
    FROM [MyTable]
    group by [UpdateTime]
   ) as t

The inserts to this table are in chunks of 50,000 rows with the same date.
So I thought grouping by might ease the MAX calculation.

Instead of trying to find max of 150,000 rows, grouping by to 3 rows, and then calculation of MAX would be faster ? Is my assumption correct or group by is also costly ?

I created the table big_table according to your schema

create table big_table
(
    updatetime datetime not null,
    name char(14) not null,
    TheData float,
    primary key(Name,updatetime)
)

I then filled the table with 50,000 rows with this code:

DECLARE @ROWNUM as bigint = 1
WHILE(1=1)
BEGIN
    set @rownum  = @ROWNUM + 1
    insert into big_table values(getdate(),'name' + cast(@rownum as CHAR), cast(@rownum as float))
    if @ROWNUM > 50000
        BREAK;
END

Using SSMS, I then tested both queries and realized that in the first query you are looking for the MAX of TheData and in the second, the MAX of updatetime

I thus modified the first query to also get the MAX of updatetime

set statistics time on -- execution time
set statistics io on -- io stats (how many pages read, temp tables)

-- query 1
SELECT MAX([UpdateTime])
FROM big_table

-- query 2
SELECT MAX([UpdateTime]) AS value
from
   (
    SELECT [UpdateTime]
    FROM big_table
    group by [UpdateTime]
   ) as t

set statistics time off
set statistics io off

Using Statistics Time I get back the number of milliseconds required to parse, compile, and execute each statement

Using Statistics IO I get back information about disk activity

STATISTICS TIME and STATISTICS IO provide useful information. Such as were temporary tables used (indicated by worktable). Also how many logical pages read were read which indicates the number of database pages read from cache.

I then activate Execution plan with CTRL+M (activates show actual execution plan) and then execute with F5.

This will provide a comparison of both queries.

Here is the output of the Messages Tab

-- Query 1

Table 'big_table'. Scan count 1, logical reads 543, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 6 ms.

-- Query 2

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'big_table'. Scan count 1, logical reads 543, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 35 ms.

Both queries result in 543 logical reads, but the second query has an elapsed time of 35ms where as the first has only 6ms. You will also notice that the second query results in the use of temporary tables in tempdb, indicated by the word worktable. Even though all the values for worktable are at 0, work was still done in tempdb.

Then there is the output from the actual Execution plan tab next to the Messages tab

According to the execution plan provided by MSSQL, the second query you provided has a total batch cost of 64% whereas the first only costs 36% of the total batch, so the first query requires less work.

Using SSMS, you can test and compare your queries and find out exactly how MSSQL is parsing your queries and what objects: tables, indexes and/or stats if any are being used to satisfy those queries.

One additional side note to keep in mind when testing is cleaning out the cache before testing, if possible.  This helps to ensure that comparisons are accurate and this is important when thinking about disk activity. I start off with DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE to clear out all the cache. Be careful though not to use these commands on a production server actually in use as you will effectively force the server to read everything from disk into memory.

Here is the relevant documentation.

Clear the plan cache with DBCC FREEPROCCACHE
Clear out everything from the buffer pool with DBCC DROPCLEANBUFFERS

Using these commands may not be possible depending on how your environment is used.

Updated 10/28 12:46pm

Made corrections to the execution plan image and statistics output.


Access: search for more than one parameter? I need to perform several queries, looking for more than one parameter.

for example: in my field "lenght" I want to extract values matching both 4.6 and 4.7.
then, next time I need 5.8 and 5.9, and so on. I will be searching for more parameters.

Is it possible to create an input form where i can input something like:

4.6 AND 4.7

PS: would it be better to use a different database software?

If you'll input "4.6, 4.7", it could construct sql-query, using IN predicate that will give you something like:

where length IN(4.6, 4.7)


Is this table in my ERD redundant? (Image) I am developing a research article database for a project. My professor approved this design but I feel like the Author's_University table is redundant and that University_ID could just be added to the Articles_Authored table. Can anyone think of a reason to NOT change it?

Thanks for the input.

The purpose of the Author's_University table seems to be to allow one author on an article to be associated with more than one university. There are a couple of potential reasons why that would be helpful:

An author might claim multiple universities because they are a professor and/or are associated with more than one university when the article was published.
Your application might want to track an author's alma mater(s).

I know this isn't part of your question but I would avoid putting an apostrophe in a table name. Removing the apostrophe would change Author's_University to Authors_University or just Author_University (my preference). That will most likely make the table easier to work with because punctuation isn't your friend when naming.

Also, my personal preference is PascalCase over Snake_Case. That is merely personal preference though, and might clash with the convention in MySQL.


jdbc: does setAutoCommit(true) commits past executions? I would like to execute some statements, and commit the entire batch at the end. I've tried the following approach:

connection.setAutoCommit(false);            // Don't commit for each statement
int[] returnCodes = pstmt.executeBatch();   // Execute all statements
connection.setAutoCommit(true);             // Back to normal state - future statements
                                            // will be committed instantly
connection.commit();                        // Commit our batch

Which fails miserably:

java.sql.SQLException: Can't call commit when autocommit=true
--reference to the line with connection.commit()--

What's the right way to go? does connection.setAutoCommit(true) commits all executed batch statements?

From the Connection.setAutoCommit docs:

  NOTE: If this method is called during a transaction and the auto-commit mode is changed, the transaction is committed. If setAutoCommit is called and the auto-commit mode is not changed, the call is a no-op.

But I don't think it's very readable/obvious in your code. You should probably simply commit before switching back to autocommit mode. Makes the intent clear.


QUERY RECORDS WITHIN 48 hours I am storing some data intro a mysql database like this:

ID     |   DATE      |  INFO
-----------------------------
1      | 1389384821  |  blabla
2      | 1389384821  |  blabla2
3      | 1389384821  |  blabla3

DATE is a timestamp generated before adding it into database using time() function in PHP

I want to get all data from this table that was added in the last 24 hours, or 48 hours, 30 days ...

I tried

SELECT
info
FROM table
WHERE date > (LOCALTIMESTAMP() - 86400)

Instead of LOCALTIMESTAMP(), I also tried CURDATE().

Where is my mistake?

I am not an expert in SQL, so this might sound a silly question.

WHERE date > (UNIX_TIMESTAMP(NOW()) - 86400) ?? Is this a good solution?

Here is 24 hours

SELECT info FROM table
WHERE date > UNIX_TIMESTAMP(NOW() - INTERVAL 1 DAY);

or

SELECT info FROM table
WHERE date > UNIX_TIMESTAMP(NOW() - INTERVAL 24 HOUR);

or your way

SELECT info FROM table
WHERE date > (UNIX_TIMESTAMP(NOW()) - 86400);

Here is 48 hours

SELECT info FROM table
WHERE date > UNIX_TIMESTAMP(NOW() - INTERVAL 2 DAY);

or

SELECT info FROM table
WHERE date > UNIX_TIMESTAMP(NOW() - INTERVAL 48 HOUR);

or your way

SELECT info FROM table
WHERE date > (UNIX_TIMESTAMP(NOW()) - (86400 * 2));

Here is 30 days

SELECT info FROM table
WHERE date > UNIX_TIMESTAMP(NOW() - INTERVAL 30 DAY);

or your way

SELECT info FROM table
WHERE date > (UNIX_TIMESTAMP(NOW()) - (86400 * 30));

Your last expression WHERE date > (UNIX_TIMESTAMP(NOW()) - 86400) is feasible, but requires you to do the number of seconds difference. My additional suggestions lets MySQL do the date computation.


Import Oracle full dump file to fresh Oracle installation I am importing a Oracle dump fill created on a AIX Oracle 9.2 installation with a Full Export option to a Windows Oracle 10.2.0.4 on Windows 2008 R2 x64.

I want to import the file to a clean database. However if I don't create a database first, there's nothing to create. If I create the database, it throws a lot of errors complaining of missing users and tablespaces, even with FULL=Y.

How can I import this dump into a clean Oracle Install?

The export ran under system and the import also. The index file does not contain the statements to import the users and tablespaces, however when I run the full import on the original file, IMP tries to create the TABLESPACES (which have incorrect paths, which I have to change, and i know that) but it does not try to create the users, only complaining they don't exist.

The actual export command user is:

exp <a_User>/<a_Password> FULL=Y FILE=/PATHHERE/oradata/Export/expdat.dmp log=exp`date + %d%m%y.%H%M%S`.log consistent=y

If you are using the old imp method the users and tablespaces must be created first.  This includes grants, database links and any advanced queues you may be using. A sample script could look like this in Windows
imp a_DBA_user/apassword LOG =C:\dump\logs\import.log file =user01.dmp fromuser=user_name touser=user_name

The newer datapump method works wonderfully.  You don't need to create the user but I would create any custom tablespaces you may have on the original.

A sample data pump call requires you to  create a database link to the original database  and a directory. This creates a directory

CREATE OR REPLACE DIRECTORY
DATA_PUMP_DIR AS
'C:\app\Oracle\product\11.2.0\dbhome_1\rdbms\log\';

GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO EXP_FULL_DATABASE;

GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO auser;

GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO IMP_FULL_DATABASE;

In your script to import set these variables
NLS_LANG

ORACLE_SID= your database name

impdp a_user_who_exists/your database name directory=DATA_PUMP_DIR network_link=original database name schemas= user1,user2 LOGFILE=DataPump.log TABLE_EXISTS_ACTION=REPLACE


Joining two tables by the nearest match General problem: I want to do a left join on two huge tables, but there is no matching key - I want to join the left table with the "nearest" row in the right table.  In this example I want to join using a timestamp, but I think one would have a similar (though more complex) problem with geographical coordinates.

To simplify a lot, I have one transaction table with a primary key id, timestamp, currency and amount - and one currency rate table with id, timestamp, currency and rate.  Those tables are independent from each other, and they contain a lot of rows; assume that for some periods there are many transactions between each update of the currency rate, while in other periods there are many updates of the currency rates for each transaction.  I want to join them so that there is one row for each transaction, with the closest matching currency rate.  I have indexes on the timestamps.

So, here is a brief example of what I have and what I need:

transaction:
+---------------------+--------+
| timestamp           | amount |
+---------------------+--------+
| 2017-07-31 00:07:05 | 173.17 |
| 2017-08-02 15:29:11 | 136.57 |
| 2017-08-05 03:45:24 |  81.27 |
| 2017-08-05 03:46:47 |   48.1 |
| 2017-08-05 03:47:38 |  35.21 |
+---------------------+--------+

rate:
+---------------------+-------------------+
| timestamp           | rate              |
+---------------------+-------------------+
| 2017-07-31 00:04:49 |            9.2923 |
| 2017-07-31 01:37:59 | 9.313423370522607 |
| 2017-08-01 08:07:59 |             9.325 |
| 2017-08-01 16:52:23 |            9.3542 |
| 2017-08-01 21:07:09 | 9.357076262185192 |
| 2017-08-02 15:07:33 |  9.34936993421895 |
| 2017-08-02 17:48:45 | 9.357217848393876 |
| 2017-08-04 04:33:51 |  9.38690807898736 |
| 2017-08-04 08:13:01 | 9.383765889641701 |
| 2017-08-06 03:45:03 | 9.118193727124817 |
| 2017-08-06 04:15:01 | 9.353042966450854 |
| 2017-08-06 05:23:29 | 9.353042966450854 |
+---------------------+-------------------+

Want something like this:
+---------------------+--------+--------+
| timestamp           | amount | rate   |
+---------------------+--------+--------+
| 2017-07-31 00:07:05 | 173.17 | 9.2923 |
| 2017-08-02 15:29:11 | 136.57 | 9.3493 |
| 2017-08-05 03:45:24 |  81.27 | 9.3869 |
| 2017-08-05 03:46:47 |   48.1 | 9.3869 |
| 2017-08-05 03:47:38 |  35.21 | 9.3869 |
+---------------------+--------+--------+

(The production data is much more dense than this)

Post-edit: I did try to use a subquery, it failed spectacularly but probably because of a mistake from my side.  Subquery seems to be the best way to solve this, so I've moved it from the question side into an answer.

I want to join the left table with the "nearest" row in the right table.

This is a KNN problem. For it you'll likely want a real database. I highly suggest you migrate to PostgreSQL and use PostGIS, where this can be solved using an index. Your answer has 0 to do with your problem. Your question is,

  by the nearest match

Your answer is only looking at one way directionality, and it's only doing it for one row not a set: it's a correlated sub-query. You simply need to change course, or bounty a developer to include KNN in MySQL. MySQL is using Boost for GIS, so maybe someday someone will jump nearest(Point const &, unsigned) in the code base. I checked for ya, it's not there and there is no professional way to do what you want at any scale.

MySQL's GIS offering is the worst out of all the majors players. No one uses it. To give you an idea of scale,

MySQL has 225 questions on GIS.SE.
PostGIS has 5,771

It's a marketing point to say it supports a spatial index. See also

PostgreSQL vs. MySQL: spatial feature comparison

BTW, PostGIS also supports KNN on timestampg using btree_gist


Two-phase commit for distributed transactions: what if a commit failed? I have 2 servers participating in 2PC scenario:

* Srv1: votes OK
* Srv2: votes OK
* Coordinator runs "commit"
* Srv1: commits
* Srv2: does not accept "commit" command because of network failure

From this moment my distributed DB is not consistence anymore.

How it should be solved?

Server 2 is obliged by its duties under the 2PC to contact the coordinator for advice on the state of the transaction.  Until it gets a Commit or Rollback from the coordinator, it must hold the transaction in the 'Ready to Commit' state, locks and all — even if it is restarted.

This could go on indefinitely, of course, so in practice there are 'Presumed Fail' or 'Presumed Succeed' heuristics that can be applied, but it is not something to undertake lightly.  If you get into a heuristic operation, then you probably do have an inconsistent set of databases — which is just one reason not to go 'heuristic' in a hurry.  Of course, if the Coordinator or the network are out of commission for any extended period of time, you probably have other problems than just database consistency.

You can find out a lot from reading Concurrency Control and Recovery in Database Systems by Philip A. Bernstein, Vassos Hadzilacos, Nathan Goodman (available for download).  That is tough going, though.  You could look in Date (Database Fundamentals, 8th Edn), but it is covered very briefly there, or Recovery Mechanisms in Database Systems by Kumar and Hsu, which has a substantial discussion of 2PC in chapter 13.


Deleting duplicate records when using text datatype I have a large (~678,000 rows) table storing emails, and I need to delete duplicate records that match the fields of the email: to, from, subject, body, as well as the foreign key record_id.

Normally I would use the following statement to remove duplicates:

DELETE
FROM emails
WHERE email_id NOT IN (
SELECT MAX(email_id) FROM emails
GROUP BY record_id, from_add, to_add, subject, body)

However, body is datatype text, so it cannot be compared/grouped. It gives the following error message on the select query:

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

What is the best way to identify duplicates with the text datatype? Do I have to convert the column to varchar first?

You don't have to convert the actual column. Just its usage in the SELECT query.

For a one off clean up task I'd probably just go

;WITH cte
     AS (SELECT ROW_NUMBER() OVER (PARTITION BY record_id,
                                                from_add,
                                                to_add,
                                                subject,
                                                CAST(body AS VARCHAR(MAX))
                                       ORDER BY email_id DESC) RN
         FROM   emails)
DELETE FROM cte
WHERE  RN > 1

Unless dealing with huge amounts of data that might make spending time on a more efficient solution (that doesn't have to sort huge strings) worthwhile.

Or alternatively you could try

;WITH T
     AS (SELECT MAX(email_id) AS max_email_id
         FROM   emails
         GROUP  BY record_id,
                   from_add,
                   to_add,
                   subject,
                   CAST(body AS VARCHAR(max)))
DELETE e1
FROM   emails e1
WHERE  NOT EXISTS (SELECT *
                   FROM   T
                   WHERE  max_email_id = e1.email_id)

This is likely to give you a better plan than NOT IN as SQL Server treats MAX(not_nullable_column) as being potentially nullable and hence you end up with a potentially more expensive plan.


How do I reraise an exception in a PL/pgSQL EXCEPTION block? Consider the following (incomplete) block of PL/pgSQL inside a function:

CREATE OR REPLACE FUNCTION my_calc(myvar1 NUMERIC, myvar2 NUMERIC)
    RETURNS NUMERIC
    RETURNS NULL ON NULL INPUT
    IMMUTABLE
    LANGUAGE plpgsql
    AS $$
    BEGIN
        RETURN some_third_party_function(myvar1, myvar2);
    EXCEPTION WHEN internal_error THEN
        IF SQLERRM LIKE 'KnownErrorPrefix:%' THEN
            RETURN 0;
        ELSE
            -- Reraise the original exception here
            RAISE EXCEPTION '%', SQLERRM;
        END IF;
    END
    $$

When an unanticipated error occurs, this code will throw a new exception with the same message. However, it won't preserve the original type or context.

How can I reraise or rethrow the original exception unmodified?

You can use RAISE without any parameters. This is documented on the Errors and Messages page:

  The last variant of RAISE has no parameters at all. This form can only be used inside a BEGIN block's EXCEPTION clause; it causes the error currently being handled to be re-thrown.

CREATE OR REPLACE FUNCTION my_calc(myvar1 NUMERIC, myvar2 NUMERIC)
    RETURNS NUMERIC
    RETURNS NULL ON NULL INPUT
    IMMUTABLE
    LANGUAGE plpgsql
    AS $$
    BEGIN
        RETURN some_third_party_function(myvar1, myvar2);
    EXCEPTION WHEN internal_error THEN
        IF SQLERRM LIKE 'KnownErrorPrefix:%' THEN
            RETURN 0;
        ELSE
            -- Reraise the original exception here
            RAISE;
        END IF;
    END
    $$


Can't connect using SqlManager for Oracle, but Sql+ I'm using EMS Sql Manager for Oracle to connect to some Oracle databases but I receive the error

ORA-12154: TNS:could not resolve the connect identifier specified

I just re-setup my PC, due to SSD & RAM upgrades, and I converted my old machine in a VM.

This VM connects successfully and the settings seem to be the same, in fact I copied the tnsnames.ora from one client to another (and I use it in Sql Manager).

I can't connect even if I use direct, with connection string, this time it says

ORA-06413: Connection not open

If I use Sql+, it correctly resolve my TNS identifier and connects correctly to all databases. Same for Oracle Enterprise Manager.

This is a piece of the tnsnames.ora I use:

CLADB_CLA2014 =
  (DESCRIPTION =
    (SOURCE_ROUTE = ON)
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = tcp)(HOST = CLA2014)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = CLADB)
      (SERVER = DEDICATED)
    )
  )

After installing 32-bit Oracle client software on a 64-bit Windows
  operating system (OS) you receive one of the following errors:
  ORA-12154 / ORA-06413.

  Cause   64-bit Microsoft OS's install 32-bit applications into   C:\Program Files (x86)\... rather than the typical location of
  C:\Program Files\....

  This causes an existing networking bug to occur where the networking
  layer is unable to parse program locations that contain parenthesis in
  the path to the executable which is attempting to connect to Oracle.

  Solution   Find the location of the application that is generating the error.  Check the path to this location and see if it contains any
  parenthesis.  If so, you must relocate the application to a directory
  without any parenthesis in the path.

I just moved Sql Manager to 64-bit applications install path (I know it's not super "clean", but nevermind) and now it works correctly.

Found answer here: MSDN connecting to oracle on 64bit x64 machine


Why am I getting a syntax error when trying to create a stored procedure in MySQL? I would like to use a transaction to call upon a stored procedure on many rows. If it fails on a single row, I want to roll back everything. Otherwise I want to commit.

I have the following so far but it throws the following error:

  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  'DECLARE val1 INT DEFAULT 0; DECLARE check INT DEFAULT 1;
  DECLARE done BOOL DEFAU' at line 3

 DELIMITER \\
 BEGIN

 DECLARE val1 INT DEFAULT 0;
 DECLARE check INT DEFAULT 1;
 DECLARE done BOOL DEFAULT FALSE;
 DECLARE e INT DEFAULT 1;
 DECLARE cursor1 CURSOR FOR SELECT Table1.col1 FROM Table1 WHERE Table.col2 = (SELECT col1 FROM Table2 WHERE col3 = 'xxx');

 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

 START TRANSACTION;

 OPEN cursor1;

 my_loop:
 LOOP

    FETCH NEXT FROM cursor1 INTO val1;
    IF done THEN
            LEAVE my_loop;
    ELSE
            storedProcedure1(val1,100,e);
            IF SELECT e = 0 THEN
                    ROLLBACK;
                    SET check = 0;
                    LEAVE my_loop;
            END IF;
    END IF;
  END LOOP;

  IF check = 1 THEN
       COMMIT;
  END IF;
  END;

Any ideas on what I may be doing wrong?

If you want to create a stored procedure, you have to use the CREATE PROCEDURE syntax, see the documentation. You can't use procedural language constructs such as DECLARE in normal SQL.

Also, you have to use the special delimiter you've declared after the final END, so the last line will be END\\. (And after that, don't forget to set DELIMITER back to ;, or you'll struggle.)

(There could be other problems as well, these are just two I spotted right away.)


How to list permissions on a schema? In SSMS, if you follow the path [Database] > Security > Schemas and view any schema properties, you have a tab "permissions" that list all the permissions that every user have on that specific schema.

I would like to make a query that gives me the same output than that tab. I tried using sys.database_principals, database_permissions and sys_objects but I can't retrieve the permissions linked to schemas.

Using the accepted answer from List schema permissions, this would probably give you what you want.

--demo setup
CREATE DATABASE listschema
GO

USE listschema
GO

CREATE SCHEMA TestSchema
GO

CREATE USER TestUser WITHOUT LOGIN
GO

GRANT SELECT
    ON SCHEMA::TestSchema
    TO TestUser

DENY INSERT
    ON SCHEMA::TestSchema
    TO TestUser
GO

--The actual query
SELECT state_desc
    ,permission_name
    ,'ON'
    ,class_desc
    ,SCHEMA_NAME(major_id)
    ,'TO'
    ,USER_NAME(grantee_principal_id)
FROM sys.database_permissions AS PERM
JOIN sys.database_principals AS Prin
    ON PERM.major_ID = Prin.principal_id
        AND class_desc = 'SCHEMA'
WHERE major_id = SCHEMA_ID('TestSchema')
    AND grantee_principal_id = user_id('TestUser')
    --AND    permission_name = 'SELECT'
GO

--cleanup
USE tempdb
GO

DROP DATABASE listschema

| state_desc | permission_name | (No column name) | class_desc | (No column name) | (No column name) | (No column name) |
|------------|-----------------|------------------|------------|------------------|------------------|------------------|
| DENY       | INSERT          | ON               | SCHEMA     | TestSchema       | TO               | TestUser         |
| GRANT      | SELECT          | ON               | SCHEMA     | TestSchema       | TO               | TestUser         |


Distinguishing data from different sources I want to store data that comes from multiple data sources, the issue is that the row identifiers have no guarantee to be unique across sources, which wouldn't satisfy the unique key constraint.

I'm not really familiar with databases, and if I had to model this in an imperative programming language I would have probably just stored data from each source apart in different arrays, this (storing in different tables) doesn't look at all like a correct approach for databases, though.

What is the usual approach for storing data that shares the exact same column types, but that we want to be separated, for example because the IDs might conflict? Should I instead put the original IDs in a different column, add another column to specify the "row origin" (ie: what source does it come from) and yet another column for the original ID that it would have had?

Thank you

1) Add a column to the table which holds the source system. Change the primary key of the table to a compound key including source system and source id. Then value ('System A', row 1) is distinct from ('System B', row 1).

There are a few downsides to this. If the source system were ever to revert to previous values (say a DB was restored, or a sequence reset, or a programming error or ... ) our uniqueness would be compromised.

It is generally advantageous to make keys (and hence indexes and foreign keys) narrow for performance reasons. So a one-column key is, other things being equal, preferable to a two-column key. If you're not running your hardware to its limits this is unlikely to be a problem in real life.

Similarly having the system name - a character string - in the key makes it wider. That can be mitigated by introducing a table SourceSystem(SystemName varchar(<whatever>), SystemId smallint) and using SystemId in the actual data table. Now you have to join to the new table should you ever need the human-readable name.

The advantage is simplicity. The loading process need only hard-code a simple string (or a table look-up) and everything else remains the same. It is easy to reference back to the source since all the IDs are intact.

2) Add a surrogate key to your table. You allocate values to this key as you insert rows. This surrogate key becomes the primary key. Say Source A sent rows 1 & 2 and Source B sent rows 1 & 5 the table becomes

SurrogateId  SourceId
-----------  --------
1            1
2            2
3            1
4            5

Advantages are we have complete control over the primary key values in our system. Whatever changes the developers of the source choose to deploy, and however their ID scheme fluctuates, it will not affect us.

Unfortunately we have no direct reference telling us which was the source for each row, if that is important in your world. That can be addressed by adding a new non-key column to hold that information.

3) Put a offset for each source. Source A's rows all get 10,000 (or 10 million, or 100 million or whatever) added, Source B's have 20,000, Source C's have 30,000 and so on. The obvious problem is that each range could be exhausted. If that happens you can move to a new interval for that source.

Option 2 - the surrogate key - is the my preferred solution.


How to add a step in all existing jobs in sql server IF (SELECT ars.role_desc
    FROM sys.dm_hadr_availability_replica_states ars
    INNER JOIN sys.availability_groups ag
    ON ars.group_id = ag.group_id
    AND ars.is_local = 1) <> 'PRIMARY'
BEGIN
   --We're on the secondary node, throw an error
   THROW 50001, 'Unable to execute job on secondary node',1
END

Set this step to continue with the next step on success, and to quit the job reporting success on a failure.

Can you  please explain how to add this step in all existing SQL server jobs?

I have created a stored procedure which will add a new first step to any existing agent job.

It checks if running on the AG primary replica and quits with success otherwise:

use master
go

-- Adds a first step to specified job, which checks whether running on Primary replica

create procedure AddAGPrimaryCheckStepToAgentJob
    @jobname nvarchar(128)
as

set nocount on;

-- Do nothing if No AG groups defined
IF SERVERPROPERTY ('IsHadrEnabled') = 1
begin
    declare @jobid uniqueidentifier = (select sj.job_id from msdb.dbo.sysjobs sj where sj.name = @jobname)

    if not exists(select * from msdb.dbo.sysjobsteps where job_id = @jobid and step_name = 'Check If AG Primary' )
    begin
        -- Add new first step: on success go to next step, on failure quit reporting success
        exec msdb.dbo.sp_add_jobstep
          @job_id = @jobid
        , @step_id = 1
        , @cmdexec_success_code = 0
        , @step_name = 'Check If AG Primary'
        , @on_success_action = 3  -- On success, go to Next Step
        , @on_success_step_id = 2
        , @on_fail_action = 1     -- On failure, Quit with Success
        , @on_fail_step_id = 0
        , @retry_attempts = 0
        , @retry_interval = 0
        , @os_run_priority = 0
        , @subsystem = N'TSQL'
        , @command=N'IF (SELECT ars.role_desc
        FROM sys.dm_hadr_availability_replica_states ars
        JOIN sys.availability_groups ag ON ars.group_id = ag.group_id AND ars.is_local = 1) <> ''Primary''
    BEGIN
       -- Secondary node, throw an error
       raiserror (''Not the AG primary'', 2, 1)
    END'
        , @database_name=N'master'
        , @flags=0
    end
end
GO

Blog post is here.

Note: For SQL Server 2014 onwards you can use the builtin function sys.fn_hadr_is_primary_replica('dbname'):

If sys.fn_hadr_is_primary_replica (@dbname) <> 1
BEGIN
    -- This is not the primary replica, exit without error.
END
-- This is the primary replica, continue to run the job...


Numeric vs Integer for a column - size and performance I have an application which uses a PostgreSQL table. The table is very big (billions of rows) and has a column which is an integer.

The integer can be up to 6 digits, i.e. 0-999,999 , no negatives.

I thought about changing it to be numeric(6,0).

Would this be a good idea? Would numeric(6,0) take fewer bytes? How about the performance (this table is being queried a lot)?

Would this be a good idea?

No.

  would numeric(6,0) take less bytes?

No.

test=> SELECT pg_column_size(INT4 '999999'), pg_column_size(NUMERIC(6,0) '999999');
 pg_column_size | pg_column_size
----------------+----------------
              4 |             10
(1 row)

  how about the performance (this table is being queried a lot)?

Slower. It's stored as binary-coded decimal because it's an arbitrary precision value.


MongoDb Sharding between replica and standalone server So i got a replica set with three servers and a standalone server .
I have enabled sharding between them.

I inserted more data on replica primary server using "rockmongo".

But the chunks are not divided yet and they are still on my replica server

I got more than 50k records so my size in db folder is more than 65M.
the output of sh.status() is

        --- Sharding Status ---
sharding version: {
"_id" : 1,
"version" : 3,
"minCompatibleVersion" : 3,
"currentVersion" : 4,
"clusterId" : ObjectId("534ab99817bb6815bff6bd47")
}
 shards:
{  "_id" : "rsName",  "host" : "rsName/dhruvil-Inspiron-3521:22017,dhruvil-Inspiron-3521:25017,dhruvil-Inspiron-3521:27001" }
{  "_id" : "shard0000",  "host" : "dhruvil-Inspiron-3521:27917" }
databases:
{  "_id" : "admin",  "partitioned" : false,  "primary" : "config" }
{  "_id" : "zips",  "partitioned" : true,  "primary" : "rsName" }
    zips.zip
        shard key: { "state" : 1 }
        chunks:
            rsName  1
        { "state" : { "$minKey" : 1 } } -->> { "state" : { "$maxKey" : 1 } } on : rsName Timestamp(1, 0)

Any thoughts about what i am doing wrong???

Do we insert data on sharded servers in some other way than normal?

Inserts need to be done via the mongos for splits to happen automatically.  In fact, all work should be done via the mongos, not directly to the primary on one of the sets.  That is why you have not seen any splits happen and your chunks are all still in one place.  In terms of details, I've written up some of the details about how splits happen in a previous answer.

Note that you can split manually via the mongos if you want to, you do not have to wait for the automatic splitting to happen.


Are there problems with using SQL Server AlwaysOn Availability Groups with Windows 2008R2? [Cross post from StackOverflow]

Brent Ozar published this webcast (slides, video with free registration) entitled "Real-Life SQL Server 2012: AlwaysOn Lessons Learned", in which he repeatedly states, with varying degrees of force, that

  using Windows 2008R2 with SQL Server 2012 Availability Groups is a bad idea

Among issues he raises are:

Quorum recalculation (a feature of Windows Failover Clustering introduced in Windows 2012)
Numerous bugs requiring Windows Hotfixes (list in AlwaysOn docs, "StackOverflow patch"), though I'm much less interested in problems that are fixed by bringing my deployment up to date.

He doesn't talk about other specifics of why using AGs with Win2008R2 is a bad idea, other than stating that AlwaysOn "pushes Windows clustering harder than any other app, and is uncovering breaking points". It is worth noting though that this is a supported configuration (AlwaysOn system requirements).

What problems have been seen in new deployments using SQL Server 2012 AlwaysOn Availability Groups with Windows Server 2008 R2, and are these eliminated by using Windows 2012? Are there problems seen in Windows 2008R2 that are fixed in Windows 2012 but do not have hotfixes available in 2008R2?

We have multiple customers using SQL Server 2012 with availability groups on Windows Server 2008 R2. The fixes listed in the pre-requisites for implementing availability groups needs to be applied for Windows to ensure that you don't run into known issues.


Stored Procedure Not working as Expected When I create the stored procedure without parameter it works as expected, but when I create it with parameter  it doesn't return any row.

DELIMITER //

CREATE PROCEDURE search()
BEGIN
SELECT * FROM locality WHERE (LOWER( REPLACE(  `locality_area` ,  ' ',  '' ) ) REGEXP ('^.* GS Road.*$')) UNION ALL SELECT * FROM locality WHERE (`locality_area` REGEXP ('^.* GS Road.*$'));

END//

The above procedure returns the rows.

But this doesn't return any value

DELIMITER //

CREATE PROCEDURE search(IN **qry** varchar(100))
BEGIN
SELECT * FROM locality WHERE (LOWER( REPLACE(  `locality_area` ,  ' ',  '' ) ) REGEXP ('^.* ***qry**.*$')) UNION ALL SELECT * FROM locality WHERE (`locality_area` REGEXP ('^.* ***qry**.*$'));

END//

That's because you're looking for the string 'qry' instead of the value held by the parameter qry.

Replace:

REGEXP ('^.* qry.$'))

With

REGEXP CONCAT('^.* ',qry, '.$'))


Best mysql config for my server I have dedicated server, CPU is too high (80%-100%) and memory only use 9gb

RAM 32 gb and CPU Dual Xeon E5-2670(8core and 16 thread)

I have 25000 users that use my android application

My database table only innodb. What is best config?

sorry for bad English

mysql5.7

High CPU almost always means inefficient queries.  The fix may be as simple as a better index (possibly composite), or rephrasing a SELECT.  Find out what queries are most commonly running and let's discuss them.

About the only tunable that might be incorrect is innodb_buffer_pool_size, which needs to be about 70% of available RAM.

You might be able to catch slow queries by using SHOW FULL PROCESSLIST; repeatedly.  But the slowlog is better.  See here.


How can I create a Windows Firewall rule to allow only one IP address to connect MS SQL 2017 Express database in Windows 2016 server? I use MS SQL 2017 Express database in Windows 2016 server, now my MS SQL 2017 express server is being attacked.

I hope to set a Windows Firewall rule to allow only one IP address (such as 45.23.56.12) to connect MS SQL 2017 Express database, how can I do ?

Seems follow-up question of this. However, following are the recommended actions in your case which could be quick release from the cause:

Define static custom port (other than 1433) for the SQL server service

Service restart required here, and the custom port number must be explicitly mentioned in connection string of application. i.e. (DataSource = DatabaseServerIP,PortNumber)

Create new login with sysadmin permissions
Disable the login sa

Also, consider applying security benchmark recommendations particularly the sections Surface Area Reduction and Authentication and Authorization.

Most importantly, do not let the database server expose to public network. Usually, the app server only exposing to public.

  Firewall rule to allow only one IP address (such as 45.23.56.12)

Start Windows firewall (you can use wf.msc from run command), select entry that related to SQL server from the In-bond Rules list (if not existed create new rule)

To Allow specific IP
Right click rule → Properties → Scope → from "Remote IP Address" section select These IP Addresses option and Add desired IP Addresses that only needs to be connect SQL Server

To change port numbers
Right click rule → Properties → Protocols and Ports → Enter the custom port (that SQL service listening on - configured in above steps) at Local port section.


Enhanced ER Model Supertype / Subtype modeling issue with inheritance and recursion Before posting, I have looked for a good solution on internet and I have coined this post for 3 days in row and trying to think through it prior this post. So if I have missed something or got it wrong, please, have understanding.

I need to model Invoicing Database for creating Invoices (in PDF) with SQL Server 2014 and Visual Studio 2013. My company operates and works only with other businesses, that is business entities.

I don't know how to model situation in which:

Seller (Service Provider) issues Invoice to Customer XOR Representative. (XOR = Exclusive OR)

I need to track info about companies and all of them: Seller, Customer or Representative initially have the same attributes. I can come up with identifiers for them like SellerID, CustomerID, RepresentativeID but I don't know if it is valid thing to do or not, since they inherit ID's from Superclass (Company) and up to this point I can't think of any other unique attribute.

There are some business rules like:

Seller (only one at the moment - my company) issues Invoices to either Customer or Representative.
Customer can have only one or none Representative and one Representative can represent many Customers.
Representative is Customer and is Company
I need to track info about issued Invoices and Companies, of course there are other entities like: Bank Accounts, Invoice Items, Services, Agreements (which  can be Contracts and Orders)

Here are the Entities:

Company (supertype)
Seller (Service Provider) (subtype of Company)
Customer (subtype of Company)
Representative (subtype of Customer or subtype of Company - see pictures for more on this...) (maybe this should be recursive relationship)

Questions:

Is supertype / subtype needed here (since subtype Entities have no unique attributes and since it gives me a headaches)?
How to implement inheritance with one entity in recursive relationship - if for instance Customer / Representative is in recursive relationship (relational table example needed)
Can you give me relational solution (EER to relational) with some examples (like table with data) so I can wrap my mind around it.

I have come up with following EER Model:

And now I am missing one of following two modeling options (some other suggestion that will work better and be less newbie friendly) ...

This one is full_disjoint with more inheritance and partial disjoint:

And this one is full overlapping (missing one "p" on a picture description):

I want to call attention to one of your business rules: Representative is Customer and is Company.

This sounds like a superclass/subclass situation to me, and I think you have the EER diagram well in hand, as far as ER modeling goes.  Where things start to get interesting (and a little messy) is when you try to switch over from an ER model (which is abstract and implementation neutral) to a relational model (which is not).

At this point, you'll have to choose between a few well known techniques that make up for the fact that the relational model doesn't incorporate inheritance.  You may wish to include this tag in your original question:  subtypes

Here are three of the techniques in a nutshell:  single table inheritance, class table inheritance, and shared primary key.

Single table inheritance lumps all the subclasses together in one table.  Data that does not pertain to a given instance is left NULL.

Class table inheritance has one table for the superclass and one table for each subclass.  Generalized data goes in the superclass table, and specialized data goes in the appropriate subclass table.

Shared primary key enforces the one to one nature of the relationships between each subclass and the superclass.  It also allows a foreign key elsewhere in the database to reference the superclass without knowing or caring which subclass it belongs to.

For a good treatment of this subject, look up Martin Fowler's treatment on the web.

Edit based on feedback

The question of which ER model is "correct" is usually determined by the database design team by analyzing the subject matter, and coming up with a model that depicts the way it works in "the real world".  This presumes that the database is yet to be designed, but the business itself has already been designed, and the business design is not to be changed.  Your comment makes me think that you are designing (or redesigning) the business itself, not just the database.  If so, then the question is outside the expertise of database design.

To deal more specifically with the questions you asked:

Is supertype / subtype needed here (since subtype Entities have no unique attributes and since it gives me a headaches)

If there are no attributes that are specialized to the subtypes , then no inheritance (from a data perspective) is needed.  So it won't make any difference to the relational design.  Don't give yourself headaches.  Pick a model that works for your business, and let it go.

How to implement inheritance with one entity in recursive relationship - if for instance Customer / Representative is in recursive relationship (relational table example needed)

If Customer and Representative are the same entity, give them a common name.  One entity with two names is confusing.  If they are different entities, rephrase your question.  From my answer to question 1, why is implementing inheritance called for?

Are "recursive relationship" and "reflexive relationship" the same thing?  If not, please clarify your question.

Can you give me relational solution (EER to relational) with some examples (like table with data) so I can wrap my mind around it.

That's what I thought I was answering the first time.  Fowler's treatment of inheritance/relational issues is available on the web.  Here and here.
His examples are about players and cricketers, not customers and representatives, but the issues concerning implementing inheritance (really pseudo inheritance) in a relational system are not case dependent.


Best practices for allowing rollback of individual customers on a shared database Imagine having multiple customers (as an example) on a shared database, and needing a backup system so that a single customer can be rolled back without affecting the other customers. - It's not important whether the customers themselves can order the rollback or only an administrator of sorts, but for a host of reasons is having a database for each customer not an option.
Edit:
There would be a customer table, but the customer's data will be spread across many tables. Maybe because a customer can add stuff only they can access.
\Edit
How can this best be achieved?
I have considered the following options (don't mind the order):
#1
Making a regular database backup and building a mechanism that clears all relevant rows in all relevant tables and then reads the customer's data from the backup-file and insert it into the database.
This mechanism will have to be maintained as the database changes.
#2
Making a shadow-table for each relevant table.
The needed mechanism will essentially be the same as for #1, but it won't be necessary to read from an external backup-file. The mechanism and shadow-tables will have to be maintained as the database changes.
This will work better than #1 if customers need be able to order rollback autonomously.
Any option I'm missing?
Anything I should be mindful about?
Most importantly: Any best practice for doing this sort of thing?
I'm using PostgreSQL in case that includes anything that can help.

Mimic the transaction system of PostgreSQL:

Every affected table has a column visible_from and visible_to, type timestamp with time zone.

When you update or delete a table, set visible_to on the old version of the row to the current timestamp rather than actually updating or deleting it.

When you update or insert a table, add a new row with visible_from set to the current timestamp.

This way you can determine the state of the table at any given point in time. Rolling back is now a matter of deleting some rows and clearing visible_to on others.
Periodically a job removes data with visible_to older than a certain grace period.


Which account should be used for the SQL Server Service logon account where the server has been added to a domain? I have installed SQL Server 2017 Express onto Windows Server 2012 R2. I need to add a self signed certificate to SQL Server Protocols in order to enable wire encryption. This comes with a requirement that the SQL Server logon account has full control permissions over the certificate. The server machine is on a domain. When I try to grant full control over the certificate to the default "NT Service\MSSQL$InstanceName" account, it throws an unrecognised name error. Should this issue be resolved by changing the SQL Server logon account to a domain account with administrator permissions? Are there any potential hazards associated with this practice or is this the recommended solution to this problem?

What I have seen implemented in a few enterprise size companies is to have an Active Directory  managed service account created on a per server or cluster basis which runs the database and agent services.

Depending on group policies in place you may have to also request that AD group policies or local security policies be modified to allow the right to "logon as a service".  There is no need to grant extra privileges to the account.

Ideally you would add this account during the setup of a new install so the permissions on the SQL Server executables are granted automatically.

This benefits security policy as auditors can see that the password policy of the domain is applied to the account and that the account has privileges only on the server/cluster that are appropriate to run the database and nothing else.

I recommend a managed service account to do this because if you are at the stage where you are implementing encryption you might as well do all the right things from a security standpoint now so you don't have to go back and redo them later.  A well named dedicated account will allow you apply a complex password that can be changed as required by company standards without wondering about where else you used that account.


Dynamic contacts database I'm trying to create a database for contacts. I must have many categories for every contact, like Home, Office, Emails, Phones. In every category one can add more than one value. Also the categories can be dynamic. How can this be designed in a database?

Check out relational database design.  It's an art and a science, and something that really can't be explained to the fullest in a post.  But what you should do is create relationships between contacts and all possible data that will be related to it.  This is most prevalent through the use of primary and foreign key relationships.


Schema for 1  0..1(+), many related but only one primary Using tools available in SQL Server 2008 and Entity Framework, what is a solid way to represent a one-to-many relationship, where there is an underlying unique relation that I need to enforce? My specific example is a set of people who have posted zero or more documents, but only one is their primary. It is not necessarily their latest.

Here's what I'm trying...

Can I enforce a relation both from DOCUMENT to PERSON on personId, and from PERSON to DOCUMENT on a composite key, while allowing null on primaryDocumentId? I'm a little worried this could cause me trouble in my ORM.

1. PERSON                       DOCUMENT
   personId unique        <- -> personId
   primaryDocumentId null    -> documentId unique

Here are some other ideas:

2. PERSON                    DOCUMENT
   personId unique        <- personId
   primaryDocumentId null    documentId unique

This does not prevent inconsistency of a person having someone else's document as their primary. It has the benefit that I can enforce a primary document by removing the null if I wish.

3. PERSON             DOCUMENT with trigger
   personId unique <- documentId unique
                      personId
                      isPrimary

This has the problem of the searched/nested action the trigger could require under some circumstances. Also, I've entirely avoided triggers until now and would prefer to keep it that way, especially for core schematic needs.

4. PERSON             DOCUMENT
   personId unique <- documentId unique
                      personId
                      date unique

This is fun, in that there is inherently only one oldest/newest document for each person. However, that doesn't represent my use in this scenario.

5. PERSON             DOCUMENT                          PRIMARYDOCUMENT
   personId unique <- personId           / composite \  personId unique
                      documentId unique  \ relation  /  documentId

This looks like it might be the right way. I assume it is practiced, but I've never seen it before. Have you seen any risks associated with it?

Or any other suggestions?

Try a filtered unique index, not a trigger.

CREATE UNIQUE INDEX ixOnePrimaryEach ON dbo.Document(PersonID)
WHERE IsPrimary = 1;


Howto disable Postgres listening on TCP? Simply to avoid many problems in the first place I do not want my postgres server program to accept/listen to anything from any network (i.e. TCP/IP 4/6) connections.
My setup is a Postgres 9.1 on an Ubuntu 12.04 box and I thought tweeking /etc/postgresql/9.1/main/pg_hba.conf to not include those lines which commented out (see below) would cause postgres to "please not listen on network TCP/IP devices"

local   all             postgres        trust

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
#local   all             all                                     md5
# IPv4 local connections:
#host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
#host    all             all             ::1/128                 md5

Also I know that there is the -i command line to start the postgres server with if we YES want to listen on TCP/IP . I actually seek for the opposite thing a -??? meaning NO please do NOT listen on TCP/IP.
I used a netstat -utap | grep post  and it shows that postgres besides my settings in /etc/postgresql/9.1/main/pg_hba.conf is still listening on  TCP/IP.
QUESTION
What did I do wrong here? How can I shut off this TCP/IP listening attitude of my Postgres server?
Having only unix socket listening I am happy to the max ;)
Thank you
Addition: I also perceive that postgres establishes a UDP connection to this 127.0.0.1:38860, what would this be about?

Maybe it is not the finest Solution / Answer to my question, but at least it will point anybody (facing the said challenge I had in my original question)

To disable listening on TCP/IP network I used this command line option when starting the server application:

postgres [other arguments] -c listen_addresses=''

Addition:
The remaining open udp 127.0.0.1:38860 connection is supposedly linked to the
purpose of the the statistics collector subprocess as suggested on postgresql.org


PostgreSQL encoding conversion error I have a Postgresql database in LATIN9 encoding.

I recently migrated my web applications (PHP) from iso_8859_15 (latin9) to UTF8.

So, now, customers are able to specify UTF-8 characters in web forms like comment form or contact form.

I open PG connections in UTF-8 encoding from PHP (PHP function pg_set_client_encoding('UTF8') or so...)

That works well for standard characters like "éàù"... PG is able to convert them from UTF-8 to LATIN9 (the database encoding). But if someone inserts some exotic character like japanese characters "日本語" PostgreSQL throws an error like this:

ERROR:  character with byte sequence 0xe6 0x97 0xa5 in encoding "UTF8" has no equivalent in encoding "LATIN9"

I know that I should migrate my database to utf8 to solve this problem, but for some reasons, I can not do that for the moment.

In my case, I'd rather PostgreSQL saves my string removing characters it can not convert or for example replacing them with some symbol like "?" rather than throwing an error...

The only way I figured to make things work like this is to open my connections in LATIN9 and use utf8_decode() PHP function that works like this (replacing unknown characters with "?" symbol)

Is it possible to do it in a better way? Maybe a PG parameter (but I don't see such parameter in postgresql.conf)

Or anyone have another idea on how to handle this?

Thanks,

know that I should migrate my database to utf8 to solve this problem, but for some reasons, I can not do that for the moment.

  In my case, I'd rather PostgreSQL saves my string removing characters it can not convert or for example replacing them with some symbol like "?" rather than throwing an error...

PostgreSQL does not support this. It's requested periodically, but nobody who requests it does the work to actually implement it in the system and convince the dev team it's an appropriate option to offer.

You will need to do your text-mangling client-side. In PHP, before you send the text to PostgreSQL, you will need to filter out characters that doesn't match the database encoding. How to do that is entirely PHP-specific (start with iconv support, probably). You have described one way to do this, using utf8_decode, already.

Using utf8_decode is actually incorrect, because the function (per the docs) actually assumes the input is ISO-8859-1, i.e. Latin-1. You're using latin-9, i.e. ISO-8859-15. So it'll mangle some of your input characters, in particular the Euro sign. See changes from ISO-8859-1. Instead, use the iconv function. See the surprisingly useful comments on the utf8_decode function documentation.

If in the process of filtering the text you convert it to LATIN9 inside PHP, remember that you must set your client_encoding to latin9, since that's the encoding of the text you'll be sending to PostgreSQL. That means the results will be in latin-9 too, so you must convert all results from PostgreSQL from latin-9 back to PHP's native utf-8.

If you use utf8_encode to convert your latin-9 output from PostgreSQL for consumption in PHP, you'll have the same problem with latin-1 vs latin-9 as you do on utf8_decode.

For that reason, if possible, try to use a filter that replaces characters not supported in latin-9 without actually converting the string to latin-9. It'll save you a bunch of hassle if you can keep client_encoding set to utf-8 and just mangle your strings instead of converting them.

All this said, I strongly recommend upgrading the database to utf-8 instead. The only reason to keep it in latin-9 would be if you have other client applications that can't cope with chars outside the latin-9 range (i.e. they rely on a latin-9 client_encoding)


SQL over TLS using Let's Encrypt on Raspberry pi I want to be able to connect to a database hosted on a Raspberry Pi (mysql-server) from a C# application. In the real world professionals use a Webservice for this, but I want to use the SQL-connector for C#.

I successfully pulled this off using a certificate from Comodo.
See https://tutorials.pieterjan.pro/?a=Webdesign_Hosting_ExternHosten_HTTPS_SSL_SQL-TLS&lang=nl

I already installed a certificate from Let's Encrypt for pieterjan.pro. But when I configure mysql to use this certificate it doesn't work. While I actually did the same thing.

sudo nano /etc/mysql/my.cnf

contains:

ssl
ssl-cert=/etc/letsencrypt/live/pieterjan.pro/fullchain.pem
ssl-key=/etc/letsencrypt/live/pieterjan.pro/privkey-nopassword.key

The key-file has been reproduced with openSSL, So it wouldn't be password-protected. The paths to the cert and key are correct. Why can't I use SQL over TLS using a LetsEncrypt Certificate?

pi@pieterjan:~ $ mysql -p -u root
Enter password:
mysql> show variables like '%ssl%';
+---------------+------------------------------------------------------------+
| Variable_name | Value                                                      |
+---------------+------------------------------------------------------------+
| have_openssl  | DISABLED                                                   |
| have_ssl      | DISABLED                                                   |
| ssl_ca        |                                                            |
| ssl_capath    |                                                            |
| ssl_cert      | /etc/letsencrypt/live/pieterjan.pro/fullchain.pem          |
| ssl_cipher    |                                                            |
| ssl_key       | /etc/letsencrypt/live/pieterjan.pro/privkey-nopassword.key |
+---------------+------------------------------------------------------------+

EDIT:

So far I've tested some things:

1) The error-log says:

SSL error: Unable to get certificate from '/etc/letsencrypt/live/pieterjan.pro/fullchain.pem'

2) The owner of the specified file is root:root

sudo ls -l /etc/letsencrypt/live/pieterjan.pro/fullchain.pem
-> user=root & group=root

3) The mysql-daemon was started by the mysql-user

ps -aux | grep mysql
-> mysql   /usr/sbin/mysqld

4) Therefore I tried to add the mysql-user to the root group:

sudo usermod -a -G root mysql

5) Double-check -> OK

sudo grep 'root' /etc/group
-> root:x:0:mysql

6) Inspection of certificate-files

sudo ls -la /etc/letsencrypt/live/pieterjan.pro/
-> lrwxrwxrwx 1 root root   37 Jan 18 13:14 cert.pem -> ../../archive/pieterjan.pro/cert1.pem
-> lrwxrwxrwx 1 root root   38 Jan 18 13:14 chain.pem -> ../../archive/pieterjan.pro/chain1.pem
-> lrwxrwxrwx 1 root root   42 Jan 18 13:14 fullchain.pem -> ../../archive/pieterjan.pro/fullchain1.pem

They appear to be just symlinks.

7) Tracked the file down which is mentioned in the error-log

sudo ls -la /etc/letsencrypt/archive/pieterjan.pro/fullchain1.pem
-> -rw-r--r-- 1 root root

The certificate is readable for owner/group/world

Despite the fact that the mysql-user is in the root-group, mysql-server is still unable to read the certificate (after restarting mysql off course).

I'm a bit late replying here but I suspect the problem is that Let's Encrypt supplies certificates in PKCS #8 format, whereas MySQL needs them in PKCS #1 format. You will need to convert the key each time it is renewed. A hook along these lines should do the trick:

openssl rsa -in /etc/letsencrypt/live/example.com/privkey.pem -out /etc/mysql/privkey.pem && systemctl restart mysql


Comparing the contents of 2 .nsm (naming standards) files I have 2 naming standards files from ERwin (.nsm extension), and I want to compare them to see if they contain the same rules. Is there some sort of automated way to check this?

If there isn't a way to compare the two within ERwin itself, is there a way to convert the 2 files into a more 'standard' file type (i.e. an Excel file), and then compare the files that way? I'm coming from a programming perspective (mostly Java), and from what I've seen it wouldn't be that hard to write a program that compares 2 Excel files.

Export both files, see How do I open an ERwin Naming Standards file (.nsm) in Excel?

Use a file compare tool to compare the contents.

If you decide to write a program yourself, follow the advice in this Stack Overflow answer:

Comparing the contents of 2 excel files


Fastest query to filter product by countries I have a large Product table:

ProductID
ProductName

1
Product 1

2
Product 2

...
...

n
Product n

whereas n is around 2,000,000.
To limit product availability to a certain country, I have a second ProductCountryRestriction table:

ProductID
CountryCode
IncludeExclude

1
USA
0

1
UK
0

2
AUS
1

2
NZ
1

If a product is restricted to one or more countries, I will then add product- included countries to this table with IncludeExclude = 0. For example Product 1 is available for USA and UK only.
If a product is valid for all countries except some countries, I will then add product - excluded countries to this table with IncludeExclude = 1. For example Product 2 is available for all countries except AUS and NZ.
All country codes corresponding to a product must either be set as IncludeExclude = 0 or as IncludeExclude = 1. The mixing of 0 and 1 is not allowed. For example, there are records with IncludeExclude = 0 for Product 1, I can't add a record with ProductID = 1 and IncludeExclude = 1 to this table.
An user of the system can select several countries to work with. The user preference is stored in UserCountry table:

UserID
CountryCode

1
USA

1
NZ

So the User 1 can see Product 1 because it is available for USA. He can also see Product 2 because the Product 2 is also available for USA (although it is excluded from NZ).
Here is my query to get all available products for an user:
DECLARE @UserID int = 1;

SELECT P.*
FROM Product P
WHERE
    EXISTS
    (
        SELECT * FROM ProductCountryRestriction PCR
        WHERE
            PCR.ProductId = P.ProductId
            AND PCR.IncludeExclude = 0
            AND PCR.CountryCode IN (SELECT CountryCode FROM UserCountry WHERE UserID = @UserID)
    )
    OR EXISTS
    (
        SELECT * FROM UserCountry UC
        WHERE UserID = @UserID
            AND UC.CountryCode NOT IN (SELECT CountryCode FROM ProductCountryRestriction PCR WHERE PCR.ProductId = P.ProductId AND PCR.IncludeExclude = 1)
    )

This query works as expected but it introduces poor performance. What can I do to improve it? I don't mind to change the DB design.
Thank you for reading my question! I will be grateful for any help you can provide.
Edited 12/31/2020 - Added execution plan as suggested by @J.D.
Please check this link for the plan: https://www.brentozar.com/pastetheplan/?id=BywbFaqaw

Since wrote you won't mind changing the design... Change the design.
Instead of that complicated logic with the flag and implicit inclusions/exclusions, just have a table that maps products to countries. Let's simply call it productcountry. If, and only if, a record for a country exists for a product in productcountry the product is available in that country.
The query then just uses some JOINs and a WHERE.
SELECT DISTINCT
       p.*
       FROM product p
            INNER JOIN productcountry pc
                       ON pc.productid = p.productid
            INNER JOIN usercountry uc
                       ON uc.countrycode = pc.countrycode
       WHERE uc.userid = @userid;

For that you should try indexes on usercountry (userid, countrycode), productcountry (countrycode, productid) and product (id).
It will need a DISTINCT though because a product can be available in more than one country a user uses. (I silently assume here that the products are distinct by themselves, i.e. they have a key.) You can experiment, if you yield a better plan, if you instead use EXISTS and a correlated subquery.
SELECT p.*
       FROM product p
       WHERE EXISTS (SELECT *
                            FROM productcountry pc
                                 INNER JOIN usercountry uc
                                            ON uc.countrycode = pc.countrycode
                            WHERE uc.userid = @userid
                                  AND pc.productid = p.productid);

Here you can try indexes on usercountry (userid, countrycode) and productcountry (productid, countrycode).


VB.Net error with CASE SELECT The following VB.Net code runs fine on SQL Server but gives an error on Access:

sql = "SELECT sr.itemcode, sr.itemname, SUM(CASE WHEN sr.transdate='4/14/2016' THEN sr.temunits] END) AS [Units Sold], " & _
      "SUM(sr.itemunits) AS [Total Units Sold] " & _
      "FROM [tblINV_SalesRecord] AS sr WHERE sr.transdate <= '4/14/2016' GROUP BY sr.itemcode, sr.itemname"

The error is:

IErrorInfo.GetDescription Failed with E_FAIL(0x80004005), -System.Data

MS Access does not support CASE expressions. The most generic equivalent would be the Switch function. Using that function, a SQL CASE expression like this

CASE
  WHEN condition1 THEN value1
  WHEN condition2 THEN value2
  ...
END

can be rewritten like this:

Switch(
  condition1, value1,
  condition2, value2,
  ...
)

A more specific, and probably more appropriate for your situation, equivalent is the IIf function. It is intended for use when you need to return one of two values based on a single condition. In other words, it replaces the following CASE pattern:

CASE WHEN condition THEN true_value ELSE false_value END

and goes like this:

IIf(condition, true_value, false_value)

In your particular situation, the CASE expression does not have an ELSE clause. That means that ELSE NULL is implied, although ELSE 0 may be more appropriate here. So, you can rewrite the query like this:

sql = "SELECT sr.itemcode, sr.itemname, " & _
             "SUM(IIf(sr.transdate='4/14/2016', sr.temunits, 0)) AS [Units Sold], " & _
             "SUM(sr.itemunits) AS [Total Units Sold] " & _
      "FROM [tblINV_SalesRecord] AS sr WHERE sr.transdate <= '4/14/2016' " & _
      "GROUP BY sr.itemcode, sr.itemname"


What would be the fastest approach to bulk update this mysql table (Innodb)? I have a table with 100M rows. I am able update my table like 850 updates / minute. How to speed this up?

I am using following approach:

mysql -uroot < query_file

where query_file contains 1M update queries.

The queries are like:

update table a set col1=1 where col2="something";

Please note, that the value of col2 is different for each query.

What would be the fastest approach to bulk update this mysql table (Innodb)?

Moreover, can I optimize this query like by col2 as a primary key or unique or index or anything else, you can suggest?

UPDATE:

Table has 8 columns.

Right now, the Primary key is a combination of url_hash(16) and domain name(say col 1 and col 6). Though, I am planning to add auto-increment id column as the primary key (col2 in the above example)

Table has 4 BTREE Type index:

Index 1 on say col 1 and col 6   <-- primary key
Index 2 on say col 1, col 3 and col 4
Index 3 on say col 1, col 5
Index 4 on say col 4

The first thing you need to determine about col2 is if it can be a PRIMARY KEY.

Run this query

SELECT COUNT(1),col2 FROM table GROUP BY col2 HAVING COUNT(1) > 1;

If nothing comes back, then col2 can be a UNIQUE KEY. If even one row comes back, then col2 cannot be a UNIQUE KEY. You can create an index on it.

Since this query would take a while without an index, just go ahead and index it

ALTER TABLE table ADD INDEX col2 (col2);

but that might take a very long time.

You may need a temp table to accomplish this:

Step 01) Create a table that will hold the one million col2 values

DROP TABLE IF EXISTS col2values;
CREATE TABLE col2values ENGINE=InnoDB SELECT col2 FROM table WHERE 1=2;
ALTER TABLE col2value ADD PRIMARY KEY (col2);

Step 02) You can load the col2values table with the one million col2 values

Step 03) Index col2

CREATE TABLE table2 LIKE table;
ALTER TABLE table2 ADD INDEX col2 (col2);
INSERT INTO table2 SELECT * FROM table;
DROP TABLE table;
ALTER TABLE table2 RENAME table;

Step 04) Perform update using INNER JOIN

UPDATE col2values A
INNER JOIN table B
USING (col2)
SET B.col1=1;

In the above steps

Step 03 needs to be done only once
Step 03 is done with 5 lines rather than ALTER TABLE table ADD INDEX col2 (col2); because it would require a full rollback operation should anything go wrong.
All other steps are your bulk insert steps going forward

Give it a Try !!!


Setting the column width of a column in a table created by logparser I'm trying to use LogParser for ETL. I am trying to import this file with the following command:

  "c:\Program Files (x86)\Log Parser 2.2\LogParser.exe" "SELECT Field1 AS CountryCode, Field2 AS Zip INTO ZipCodes  FROM 'c:\Users\jdearing\Downloads\us.txt'" -i:TSV -headerrow:OFF -o:SQL -server . -database LogParserTest -createtable -cleartable

It creates a table like so:

CREATE TABLE [dbo].[ZipCodes](
    [CountryCode] [varchar](255) NULL,
    [Zip] [int] NULL
);

If I change the command to

  "c:\Program Files (x86)\Log Parser 2.2\LogParser.exe" "SELECT Field1 AS CountryCode, TO_STRING(Field2) AS Zip INTO ZipCodes  FROM 'c:\Users\jdearing\Downloads\us.txt'" -i:TSV -headerrow:OFF -o:SQL -server . -database LogParserTest -createtable -cleartable

It creates the following table:
    CREATE TABLE [dbo].[ZipCodes](
        [CountryCode] varchar NULL,
        [Zip] varchar NULL
    );

So TO_STRING(Field2) AS Zip INTO ZipCodes creates [Zip] [varchar](255) NULL.

My goal however, is for the resulting table to be:

CREATE TABLE [dbo].[ZipCodes](
    [CountryCode] [char](2) NOT NULL,
    [Zip] [char](5) NOT NULL
);

Both columns remain VARCHAR(255) NULL with a SUBSTR like so:

  SUBSTR(Field1, 0, 2) AS CountryCode, SUBSTR(TO_STRING(Field2), 0, 5) AS Zip

How do I get LogParser to create the table with fixed length NOT NULL char columns?

Its possible to get somewhat close to this. First precreate the table

IF object_id('[LogparserTest].[dbo].[ZipCodes]') IS NOT NULL
BEGIN
    DROP TABLE [dbo].[ZipCodes]
END

CREATE TABLE [dbo].[ZipCodes](
    [CountryCode] [varchar](3) NOT NULL,
    [Zip] [varchar](6) NOT NULL
);

Note that you have to use varchar columns one character wider than your input.

Now run this command, note the lack of -createtable, but you can keep that there if you desire. It does't recreate an existing table:

  "c:\Program Files (x86)\Log Parser 2.2\LogParser.exe" "SELECT SUBSTR(Field1, 0, 1) AS CountryCode, SUBSTR(TO_STRING(Field2), 0, 5) AS Zip INTO ZipCodes  FROM 'c:\Users\jdearing\Downloads\us.txt'" -i:TSV -headerrow:OFF -o:SQL -server . -database LogParserTest -cleartable -transactionRowCount:0  -ignoreMinWarns:OFF

If you create the correct length columns as char or varchar you get the following error for every row:

An error occurred while uploading data to SQL table
  SQL State:     22001
  Native Error:  0
  Error Message: [Microsoft][ODBC SQL Server Driver]String data, right
  truncation

However, note that SELECT MAX(LEN([CountryCode])), MAX(LEN(ZIP)) FROM [LogparserTest].[dbo].[ZipCodes]; Returns 2 and 5. Tracing the bulk insert with extended events shows that things are being paramaterized as follows so I don't know what's going on:

(@Param000004 char(2),@Param000005 char(5))INSERT [LogparserTest].[dbo].ZipCodes VALUES(@Param000004,@Param000005)

So at this point you still need to perform an ALTER TABLE ALTER COLUMN command to alter each column, so it doesn't actually buy you much.


Selecting values that are not associated with one side of a many to many relationship I have a junction table between categories and values that indicate which categories are associated with which each value. I would like to find the values that are not associated with each category.

Category:

ID CategoryName
-- ------------
 1 category1
 2 category2
 3 category3

CategoryValue:

CategoryID ValueID
---------- -------
         1       1
         1       2
         2       1
         3       2

Value:

ID ValueName
-- ---------
 1 value1
 2 value2
 3 value3

The output I would like for the query is the following:

CategoryID ValueID ValueName
---------- ------- ---------
         1       3 value3
         2       2 value2
         2       3 value3
         3       1 value1
         3       3 value3

I'm pretty stuck on how to approach this as a query. The "junction" table seems to prevent the usual LEFT JOIN WHERE null approach. So any advice would be much appreciated.

You need all combinations of Category and Value (a CROSS JOIN) except those that appear in the CategoryValue. This can be done with a LEFT JOIN / IS NULL

SELECT
    c.CategoryID, v.ValueID, v.ValueName
FROM
    Category AS c
  CROSS JOIN
    Value AS v
  LEFT JOIN
    CategoryValue AS cv
      ON  cv.CategoryID = c.CategoryID
      AND cv.ValueID = v.ValueID
WHERE
   cv.CategoryID IS NULL ;

or a NOT EXISTS:

SELECT
    c.CategoryID, v.ValueID, v.ValueName
FROM
    Category AS c
  CROSS JOIN
    Value AS v
WHERE
   NOT EXISTS
     ( SELECT *
       FROM CategoryValue AS cv
       WHERE cv.CategoryID = c.CategoryID
         AND cv.ValueID = v.ValueID
     ) ;

or an EXCEPT solution:

SELECT
    c.CategoryID, v.ValueID, v.ValueName
FROM
    Category AS c
  CROSS JOIN
    Value AS v

EXCEPT

SELECT
    cv.CategoryID, v.ValueID, v.ValueName
FROM
    CategoryValue AS cv
  JOIN
    Value AS v
      ON  cv.ValueID = v.ValueID ;


ODBC connection with RD Gateway here at my company, we use an RD Gateway server.
that means when we want to access a server we have to set up the gateway in the connection...something like rdsgw.mycompany.com

But, how could I do an ODBC connection with my SQL Server if I have to use the gateway?

I'm not an RD Gateway Expert, but I think you don't have to and you can't use the RD Gateway Server for Database Connections through ODBC.
If you want to access an SQL Server from Outside, you will be better with a VPN Server.

The RAS Gateway could fit your needs -> https://docs.microsoft.com/en-us/windows-server/remote/remote-access/ras-gateway/ras-gateway).

Alternativly, you can connect with RDP through your RD Gateway Server to a RDP Server inside your company and start your application with the ODBC connection.

Best Regards,
Björn


ORA-01410: invalid ROWID I get ORA-01410: Invalid ROWID error when executing the following piece of code:

DECLARE
        CURSOR c1 IS
      SELECT e.empno,
             e.ename,
             e.sal,
             d.dname
      FROM   emp e,
             dept d
      WHERE  e.deptno = d.deptno
             AND e.deptno = 10
      FOR UPDATE OF sal NOWAIT;
    c2 c1%ROWTYPE;
BEGIN
    OPEN c1;

    LOOP
        FETCH c1 INTO c2;

        Exit WHEN c1%NOTFOUND;

        IF c2.dname = 'ACCOUNTING' THEN
          UPDATE emp1
          SET    sal = sal + 1000
          WHERE  CURRENT OF c1;
        END IF;
    END LOOP;
END;
/

Please suggest a way to overcome it.

In your code, you are locking the following records in the c1 cursor result set for the column sal:

     EMPNO ENAME             SAL DNAME
---------- ---------- ---------- --------------
      7782 CLARK            9451 ACCOUNTING
      7839 KING            12001 ACCOUNTING
      7934 MILLER           8301 ACCOUNTING

For example, from another session this query

SELECT sal FROM emp FOR UPDATE;

will block until the next commit or rollback statement will be issued (check out v$session).

I guess the problem is that you are updating the sal column values of table emp1 (and not emp one), but these records are not locked. You plan on updating emp1 records that have not been referenced by the previous SELECT FOR UPDATE statement, so their ROWIDs become invalids.
From here: The WHERE CURRENT OF statement allows you to update or delete the record that was last fetched by the cursor.

In fact, if you change the code like this:

...
IF c2.dname = 'ACCOUNTING' THEN
    UPDATE emp1
    SET    sal = sal + 1000;
END IF;
...

or if you replace emp1 with emp:

...
IF c2.dname = 'ACCOUNTING' THEN
    UPDATE emp
    SET    sal = sal + 1000;
    WHERE  CURRENT OF c1;
END IF;
...

it would work.


Dependency preserving decomposition Let be the following relational scheme:
F={AB → C; AC  →  B; AD  →  E; B  → D; BC → A;E  → G}
Is the following decomposition without loss of dependency?
R¹=(A,B),R²=(B,C),R³=(A,B,D,E),R⁴=(E,G)

Looking then at their closures:
[AB]⁺={A,B,C,D,E,G}
[BC]⁺={B,C,A,D,E,G}
The teacher then did something about closures I don't perfectly understand:
F¹⁺=∅
F²⁺=∅
F³⁺={AD → E, B → D, AB → D,AB → E}

I know that F⁺={X → Y\F⊨X → Y}
and that [X]⁺= {A\F⊨X → A}

Then she did:
[AB]⁺⇔
AB → C;
AB → D;
AB → E;
AB → G
[AB]⁺ ∪ F³⁺={A,B,D,E,G}, C isn't in [AB]⁺ ∪ F³⁺
Therefore it isn't a dependency preserving decomposition
Can you help me understand why?

The last two lines are an abbreviated way of solving the problem without recurring to the complete algorithm to check for dependency preservation. In particular the teacher noted that combining the dependencies that you can obtain from R1 and R3 you cannot obtain C, which is essential to get A in the dependency  BC → A. This dependency can never be derived by the projection of F over the decomposed relations.

Here follows the complete explanation.

To check if a decomposition preserves the dependencies, one can use the Ullman algorithm that checks, for each dependency X → Y in F, if Y is contained in the closure of X with respect to the projection of F over the decomposition (formally, calling G the union of the projection of the dependencies over the decomposed relations Ri, that is G = ∪πRi(F), the algorithm checks if X → Y ∈ G+, the closure of G, by checking that Y ⊆ X+G).

So, we must calculate X+G for each dependency of F, to check if Y is contained in it.

Such a closure is computed with the following algorithm.

Initialize X+G with X,
at each step, for each relation Ri, add to X+G the attributes obtained by calculating the closure of the attributes of X+G ∩ Ri with respect to F, and projecting the result again on Ri. In symbol: X+G := X+G ∪ ((X+G ∩ Ri)+F ∩ Ri)
repeat 2 while X+G changes.

Let’s try it for the dependency BC → A.

Initially:

BC+G = BC,

using R1(AB), BC ∩ AB = B, B+F = BD, so intersecting it with AB gives B, which is already in BC+G;

using R2(BC), BC ∩ BC = BC, BC+F = ABCDEG, so intersecting it with BC gives BC which is already in BC+G;

using R3(ABDE), BC ∩ ABDE = B, B+F = BD, so intersecting it with ABDE we obtain BD and we can add D to BC+G, which now becomes BCD;

BC+G is changed, so we must restart checking for the new value BCD. The first two relations do not give any contribute, since D is not present in them. The third one must be considered again:

using R3(ABDE), BCD ∩ ABDE = BD, BD+F = BD, we can not add anything;

using R4(EG), BCD ∩ EG = ∅,  nothing to add again.

So we have terminated, beacause no new attribute can be added to the closure, and since BC+G = BCD does not contain A, we can conclude that at least this dependency is not preserved, and so that the decomposition does not preserve the dependencies.

And since you asked how to decompose a relation without losing dependencies, you can look at the synthesis algorithm to decompose a relation in Third Normal Form (3NF). You can find it on any good book on databases, and it guarantees to preserve both the dependencies as well as the data (lossless decomposition), while reducing redundancies and anomalies.

In this case a decomposition in 3NF is the following:

R1 < (ADE) ,
{ AD → E } >

R2 < (BD) ,
{ B → D } >

R3 < (ABC) ,
{ BC → A,
AC → B,
AB → C } >

R4 < (EG) ,
{ E → G } >


Streaming replication version between different minor versions I want to know few details about streaming replication.

I want to setup streaming replication between version 9.4.4 and 9.4.20 ?

I have the master server in centos6.6 with postgres version 9.4.4 which is a physical machine and we have created a new virtual machine centos7.5 and postgres version is 9.4.20. I want to know if this will cause any problem due to version mismatch.

Also, How can I download specific minor version 9.4.4 of postgres ? I checked some official documentation and by yum I am able to see only 9.4 which is actually 9.4.20 after I installed.

AFAIK it will work and documentation says, it should work.

  In general, log shipping between servers running different major
  PostgreSQL release levels is not possible. It is the policy of the
  PostgreSQL Global Development Group not to make changes to disk
  formats during minor release upgrades, so it is likely that running
  different minor release levels on primary and standby servers will
  work successfully. However, no formal support for that is offered and
  you are advised to keep primary and standby servers at the same
  release level as much as possible. When updating to a new minor
  release, the safest policy is to update the standby servers first — a
  new minor release is more likely to be able to read WAL files from a
  previous minor release than vice versa.

https://www.postgresql.org/docs/9.4/warm-standby.html

For upgrade process

If you installed PostgreSQL via yum, yum update postgresql* should work. If you need rpms here is the PostgreSQL download link;

http://download.postgresql.org/pub/repos/yum/9.4/redhat/


sql server management studio enable encryption I see that i can manage encryption through the option:
right click database->tasks->manage database encryption

I see two options using asymmetric key or certificate as shown below

but i don't see an upload option of the key or certficate .

Can somebody please let me know how to upload a certificate and enable the encryption?

Thank you

SSMS GUI expects you to Create your server certificate before you mange database encryption.

Ref: CREATE CERTIFICATE (Transact-SQL)

Once you create the server certificate you you will be able to chose from the drop down box.

CREATE CERTIFICATE testencryptioncert
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
   WITH SUBJECT = 'Testing Encryption',
   EXPIRY_DATE = '20201031';
GO

Now the above certificate will show up in the drop down list of the GUI.


Recover a broken or damaged database from the transaction file or "journal" A greeting fellow, I would like to know if in MySQL (I understand that all engines must have), you can recover from damage to the database using some kind of file "journal" transactions, if so, what is that file? What is the correct procedure to recover it ?. I know that I can recover it from the * .frm files present in /var/lib/mysql, but I would also like to know this other option. And if someone dominates the subject, I will appreciate your comments.

The MySQL binary log is what you are looking for. Aside from being used for replication, because it stores a range of writes executed on the server, it can be used for point in time recovery.

You will need some kind of regular starting point in case of recovery (such as a full backup), and then you can use the binary logs and mysqlbinlog to roll forward to an arbitrary point in time (e.g. just before an accidental table drop). So they won't substitute backups but can complement them nicely.

Some people was able even to use them to roll back actions under specific circumstances and configuration.


Procedure doesn't have compatible arguments Getting an error while invoking my procedure named LOG_MSG_TEST(). Procedure Schema name is ADMINIST.

My procedure has one INTEGER input parameter and one VARCHAR output parameter.

I am calling this procedure in the CLP using mentioned syntax:

call ADMINIST.LOG_MSG_TEST(1);

It is existing in the SYSCAT.PROCEDURES table, but I am still getting the following error:

  No authorized routine named "ADMINIST.LOG_MSG_TEST" of type
  "PROCEDURE" having compatible arguments was found.. SQLCODE=-440,
  SQLSTATE=42884, DRIVER=4.17.30

You say that your procedure has two parameters: an input INTEGER and an output VARCHAR, however, you call a procedure with only one argument, therefore DB2 is looking for the procedure that has only one INTEGER argument.

You should call it like so:

call ADMINIST.LOG_MSG_TEST(1,?);

where the question mark is a placeholder for the output argument.


How to get the column names of a RECORD? I have a function that declares:

DECLARE r RECORD;

The data on this record varies and at some point I need to know the column names.

How do I get the column names of a RECORD in another record ?

Generally PLpgSQL doesn't allow dynamic work over fields of composite types. This languages is pretty static - more similar to older compilied languages like Pascal, Ada, Modula.

Some modern version of PostgreSQL allows transformation record->json and Postgres has lot of functions for work with JSON values (I use row_to_json and json_each_row` functions):

do $$
declare
  r record;
  key text;
  val text;
begin
  select 1 as a,3.4 as b into r;
  for key, val in
     select * from json_each_text(row_to_json(r))
  loop
    raise notice '% %', key, val;
  end loop;
end $$;

NOTICE:  a 1
NOTICE:  b 3.4
DO

I don't know your case, but if you need this feature, probably you are using plpgsql not well. It is static language - mostly time you should to know, what fields are in record before you are execute query. The programming of stored procedures is pretty far to programming in dynamic languages like JavaScript or Python or Lua.


SQL Server Import Export Wizard Error - Server throw Exception I am trying to move all database tables from one db to another as a nightly job.

The first (export) db is running sql server 2008 - SQL Server Authentication - I connect with SQL Server Native Client 10.0 to that one. I should mention I only have read access to this server.

The second (import) machine where I want to save the job is running 2014 - Windows Authentication - I connect with SQL Server Native Client 11.0 to this one.

Copy all tables. I have tried both saving the SSIS package as SQL Server and File System. It throws an error when I actually try to save it. The error says, "The Server threw an exception." Additional information: The Server threw an exception. Super helpful.

the SQL Server instance is new, so I thought it might be a rights issue. I checked the event log and have these two errors 1000 Application Error and 10016 Microsoft-Windows-DistributedCOM.

For 10016 I tried this run -> dcomcnfg -> Component Services -> DCOM config -> MSDTServer100 -> Right click Properties -> Security -> Launch and Activation Permissions. Also, I am already an admin on this box.

In SQL 2014 I don't have MSDTServer100 So I changed permissions for these DTS Package Host (several of them) and Microsoft SQL Server Integration Services 12. Is there a different config I am missing?

I should add, I ran this wizard locally and the export worked just fine. I couldn't save it because I only have SQL express locally.

I should mention I only have read access to the first server. I ran a Profiler trace. I see two errors User Error Message "Changed database context to 'msdb'" and "Changed language setting to us_english" the event on the trace is Audit Logout. I don't do this often enough to know, but I wouldn't say I'm seeing anything out of the ordinary.

Since you are getting the error when saving and not executing the package,  you are using SQL 2014 you are probably being bitten by the new security features introduced in 2012.

Your user's login most likely doesn't have access to the SSISDB catalog (even though he might have access to SQL Server).
You need to map the login to an SSISDB user and configure access to the SSISDB folders/projects in Integration Services.

Please have a look at this MSDN blog post SSIS Catalog Access Control Tips and SQL 2012 SSIS Catalog Permissions


Possible to make MySQL use more than one core? I've been presented with some dedicated MySQL servers that never use more than a single core. I'm more developer than DBA for MySQL so need some help

Setup

The servers are quite hefty with an OLAP/DataWarehouse (DW) type load:

Primary: 96GB RAM, 8 cores + single RAID 10 array
Test: 32GB RAM with 4 cores
The biggest DB is 540 GB, the total is around 1.1TB and mostly InnoDB tables
Solaris 10 Intel-64
MySQL 5.5.x

Note: The biggest DB is the replicated one from the OLTP DR server and the DW is loaded from this. It isn't a full DW: just last 6 months to 6 weeks so it is smaller than the OLTP DB.

Observations on a test server

3 separate connections
each has a concurrent (and different) ALTER TABLE...DROP KEY...ADD INDEX
the 3 tables have a 2.5, 3.8 and 4.5 million rows
CPU usage goes up to 25% (one core is maxed out) and no higher
the 3 ALTERs take 12-25 minutes (a single on the smallest takes 4.5)

Questions

What setting or patch is required to allow more than one core to be used?
That is, why doesn't MySQL use all cores available? (like other RDBMS)
Is it a consequence of replication?

Other notes

I understand the difference between an RDBMS "thread" and an OS "thread"
I'm not asking about any form of parallelism
Some of the system variables for InnoDB and threads are sub-optimal(looking for a quick win)
Short term, I'm unable to change the disk layout
OS can be tweaked if needed
A single ALTER TABLE on the smallest table takes 4.5 minutes (shocking IMO)

Edit 1

innodb_thread_concurrency is set to 8 on both. Yes, it's wrong but won't make MySQL use multiple cores
innodb_buffer_pool_size is 80GB on primary, 10GB on a test (another instance is shut down). This is OK for now.
innodb_file_per_table = ON

Edit 2

innodb_flush_log_at_trx_commit = 2
innodb_use_sys_malloc = ON
innodb_flush_method should be O_DIRECT (but SHOW VARIABLES doesn't show this)
innodb_doublewrite = OFF
File system = ZFS (And my sysadmin found this: http://blogs.oracle.com/realneel/entry/mysql_innodb_zfs_best_practices)

To test

innodb_flush_method isn't showing as O_DIRECT when it should be
will follow RolandoMySQLDBA's settings

Let me know if I've missed anything important

Cheers

Update

Changed innodb_flush_method + 3 x thread settings in RolandoMySQLDBA's answer
Result: > 1 core used for the tests = positive result

I actually discussed innodb_thread_concurrency with a MySQL Expert at the Percona Live NYC conference back in May 2011.

I learned something surprising: In spite of the documentation, it is best to leave innodb_thread_concurrency at 0 (infinite concurrency). That way, InnoDB decides the best number of innodb_concurrency_tickets to open for a given MySQL instance setup.

Once you set innodb_thread_concurrency to 0, you can set innodb_read_io_threads and innodb_write_io_threads (both since MySQL 5.1.38) to the maximum value of 64. This should engage more cores.


Re-order columns values based on values in a table I would like know, how can we re-order the columns in ascending or descending order, while retrieving the data.

Suppose the table contains the data as below with no primary key/unique key constraints:

Col1    Col2    Col3     Col4
----    ----    ----     ----
B        D       C        A
C        Y       M        T
3        5       2        4
5        2       10       7

I want the output in below format: Each row data should be in ascending order, Whether it's Varchar or Numeric. That I guess could be done by taking ascii value of the value set? Is it achievable, if not why?

ColA    ColB    ColC     ColD
----    ----    ----     ----
A        B       C        D
C        M       T        Y
2        3       4        5
2        5       7        10

Please try to provide a solution, which can be generic for any number of columns.

Every row should be re-arranged in ascending order, while we are retrieving the data. I am trying to do something like order by, but not based on column, but row. Is it achievable. It was an interview question for me by Amazon. I tried pivot and unpivot, but haven't been able to achieve this.

More information:

All column datatypes are VARCHAR2.
No specific order/pattern for the values.
The column names does not need to be same, while retrieving the data. Column names can be aliased or can be taken as per your preference. The data retrieved should be in the ascending order by row.
No primary key/unique key constraints.

I would like to understand, if such a solution is achievable, whether inefficient or not. Looking for answers using Oracle 11g or higher.

Solution

select  ColA,ColB,ColC,ColD

from   (select  t.n,t.val

               ,row_number () over
                (
                    partition by n
                    order by     case when regexp_like(val,'^-?\d+$') then to_number(val) end
                                ,val
                ) as rn

        from   (select      rownum as n
                           ,t.*

                from        t
                ) t unpivot (val for col in (COL1,COL2,COL3,COL4)) t

        ) t pivot (min(val) for rn in ('1' as ColA,'2' as ColB,'3' as ColC,'4' as ColD))

order by n
;

For this demo, the numeric values are assumed to be integers.

Walkthrough

Add a row number to each row:

select      rownum as n
           ,t.*

from        t
;

The row numbers will serve as identifiers to be later used when the unpivoted rows are pivoted back. The output produced is like this:

+---+------+------+------+------+
| N | COL1 | COL2 | COL3 | COL4 |
+---+------+------+------+------+
| 1 | B    | D    | C    | A    |
+---+------+------+------+------+
| 2 | C    | Y    | M    | T    |
+---+------+------+------+------+
| 3 | 3    | 5    | 2    | 4    |
+---+------+------+------+------+
| 4 | 5    | 2    | 10   | 7    |
+---+------+------+------+------+

Unpivot the rows.

unpivot (val for col in (COL1,COL2,COL3,COL4)) t

Rank the columns within each row according to their order as sorted first by their numeric value, if applicable, and then alphabetically as strings.

row_number () over
(
    partition by n
    order by     case when regexp_like(val,'^-?\d+$') then to_number(val) end
                ,val
) as rn

For numeric items, the first sorting criterion will evaluate to corresponding numeric values and thus determine the order of the items. For string items, the first term will be null, and so the order will be determined by the second sorting criterion.

The output that you will get for your example after unpivoting and ranking will be this:

+---+-----+----+
| N | VAL | RN |
+---+-----+----+
| 1 | A   | 1  |
+---+-----+----+
| 1 | B   | 2  |
+---+-----+----+
| 1 | C   | 3  |
+---+-----+----+
| 1 | D   | 4  |
+---+-----+----+
| 2 | C   | 1  |
+---+-----+----+
| 2 | M   | 2  |
+---+-----+----+
| 2 | T   | 3  |
+---+-----+----+
| 2 | Y   | 4  |
+---+-----+----+
| 3 | 2   | 1  |
+---+-----+----+
| 3 | 3   | 2  |
+---+-----+----+
| 3 | 4   | 3  |
+---+-----+----+
| 3 | 5   | 4  |
+---+-----+----+
| 4 | 2   | 1  |
+---+-----+----+
| 4 | 5   | 2  |
+---+-----+----+
| 4 | 7   | 3  |
+---+-----+----+
| 4 | 10  | 4  |
+---+-----+----+

Pivot the rows back using the rankings as new column names (optionally aliased following any pattern you like; in the query above – as ColA, ColB etc.) and the row numbers assigned at the beginning as row identifiers.

The result will be the expected output:

+------+------+------+------+
| COLA | COLB | COLC | COLD |
+------+------+------+------+
| A    | B    | C    | D    |
+------+------+------+------+
| C    | M    | T    | Y    |
+------+------+------+------+
| 2    | 3    | 4    | 5    |
+------+------+------+------+
| 2    | 5    | 7    | 10   |
+------+------+------+------+

A live demo is available at Rextester.