Create Oracle DB LINK to a database with no DB_DOMAIN I'm trying to create a database link between this two DBs.

My local DB is 11.2.0.3.

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MYDB.DOMAINNAME

SQL> select name, value from v$parameter where name in ('db_name', 'db_domain', 'global_names');
NAME                 VALUE
-------------------- ----------------------------------------
db_domain
global_names         TRUE
db_name              mydb

The remote DB is 11.2.0.2 configured as follows:

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
myremotedb

SQL> select name, value from v$parameter where name in ('db_name', 'db_domain', 'global_names');

NAME                 VALUE
-------------------- ----------
db_domain
global_names         FALSE
db_name              myremotedb

I want to create a DBLINK in the local database to the remote database.

I have my tnsnames (mydb's machine) configured as follows:

REMOTE-DATABASE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(Host = 192.168.1.2)(Port = 1521))
    )
    (CONNECT_DATA =
      (SID = myremotedb)
    )
  )

Then, when I try to create a database link in my local database, since global_names parameter is set to true, oracle appends the local domain name to the database link name

SQL> create database link myremotedb@myuser connect to myuser identified by mypasswd using 'REMOTE-DATABASE';

Enlace con la base de datos creado.

SQL> select db_link from user_db_links;

DB_LINK
------------------------------
MYREMOTEDB.DOMAINNAME@MYUSER

and if I try to use it, it fails

SQL> select * from dual@MYREMOTEDB@MYUSER;
select * from dual@MYREMOTEDB@MYUSER
                   *
ERROR en línea 1:
ORA-02085: database link MYREMOTEDB.DOMAINNAME@MYUSER connects to MYREMOTEDB

SQL> select * from dual@MYREMOTEDB.DOMAINNAME@MYUSER;
select * from dual@MYREMOTEDB.DOMAINNAME@MYUSER
                   *
ERROR en línea 1:
ORA-02085: database link MYREMOTEDB.DOMAINNAME@MYUSER connects to MYREMOTEDB

How can I create this database link in this scenario?

The remote db doesn't have a db_domain. How can I force oracle to create a DBLINK with no domain name?

Regards!

Can you show us the content of your sqlnet.ora file ?
The domain name should be listed there :

NAMES.DEFAULT_DOMAIN = domain.com.xxx

In case you don't have one , create a new sqlnet.ora file using the netca tool.
- after you create that file edit it and add the entry with you domain name!

Simply at local db set

alter system set global_Names = FALSE;

Then your DB link works.


Directory lookup failed when adding sample database to SQL Server 2012 I am trying to add the AdventureWorks sample database to SQL Server 2012, but I get an error message:

  Msg 5133, Level 16, State 1, Line 1
  Directory lookup for the file "C:\Users\A\Documents\SQL Server Management Studio\Data\AdventureWorks2012_Data.mdf" failed with the operating system error 5(Access is denied.).

The error message seems pretty straightforward: Access is denied. You need to make sure the SQL Server service account has adequate permissions on C:\Users\A\Documents\SQL Server Management Studio\Data\, or better yet, move the MDF (and LDF, if it exists) to the default data/log folders for the instance (where this shouldn't be a problem, and which is a better home for your database files anyway).

You can find the default data/log folders by inspecting:

SELECT physical_name FROM sys.master_files;

Any of the paths presented here should be a valid location to use...

While creating a new database provide database path outside users folder .
So , lot of permissions related issue are solved


Changing master for channel gives syntax error I'm trying to set up a multi-source replication

When trying to set a master for a specific channel using this query

CHANGE MASTER TO MASTER_HOST='192.168.0.203', MASTER_USER='rep', MASTER_PORT=3306, MASTER_PASSWORD='pass', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=107 FOR CHANNEL 'master-203';

I get the following error

  Error Code: 1064. You have an error in your SQL syntax; check the
  manual that corresponds to your MariaDB server version for the right
  syntax to use near 'FOR CHANNEL 'master-203'' at line 1

I can't figure out what's the problem. The query is pretty much a copy-paste from the official documentation

My server is running MariaDB 10.1.21

What could be wrong?

You're looking at the documentation for MySQL 5.7, but you're running MariaDB 10.1, and replication channels happen to be a feature that is implemented differently in MariaDB.

From the MariaDB documentation on multi-source replication:

  You specify which master connection you want to work with by either specifying the connection name in the command or setting default_master_connection to the connection you want to work with.

So in your case you could try:

CHANGE MASTER 'master-203' TO MASTER_HOST='192.168.0.203',
MASTER_USER='rep',
MASTER_PORT=3306,
MASTER_PASSWORD='pass',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=107;

(Disclaimer: I've not tried this in practice.)

CHANGE MASTER 's-102' TO  MASTER_HOST='INFItjgDT-059',
MASTER_USER='rpl1',
MASTER_PASSWORD='rpl1',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=342,
MASTER_PORT=6666;

use this command


How can I minimize the risk of accidentally modifying the wrong database? I just learned the hard way that disconnecting from a server in the Object Explorer doesn't stop you afterwards from executing query windows that were already open on that server.

My situation is like this: I have one instance of SSMS that I use to connect to our dev/staging server and to our production server. I had to delete a bunch of data on dev so I figured I should close my connection to production, but I didn't pay attention to the query window I was using. (Luckily we had a backup of only a few hours old.)

I'm not the first person to destroy production data and I won't be the last I'm sure. So
I'm looking for checklists, best practices, etc. that help you to minimise the risk of executing queries on the wrong database. Have you had this happen to you before, and how have you adapted your workflow to try to avoid this?

One thing I like to do in SSMS is to use Custom Colours when connecting to database.
So you choose a nice bright Red for Live databases, and a gentle blue or green for dev or test systems.
I used to use the inbuilt SSMS, but these days I prefer the SSMS Tools Addon Colour coding.

Or like this for SSMS Tools (A really nice addon, and I find the colour better when it's on top, rather than on the bottom like the built in on)

In agreement with swasheck's comment on the original question, how about executing...

select @@servername + '\' + @@servicename

...before running any DML, or even looking at the status bar to see what instance you're connected to, or even running all DML in a transaction so that you can rollback if you realize you've made a mistake? A lot of great suggestions here, but basically, when it comes to potentially destructive DML, gimmicks will only get you so far. I always check, double-check, and check again. And if I'm dealing with a small amount of data, I might even SELECT INTO a new table prior to the DML, run my DML, do some comparisons to make sure things worked correctly, then drop the "backup" table. Work smarter, not harder.


SUM several CASE results Given:

 CASE
     WHEN A IS NULL THEN 1 ELSE 0
 END AS [A]
, CASE
     WHEN B IS NULL THEN 1 ELSE 0
 END AS [B]
, CASE
     WHEN C IS NULL THEN 1 ELSE 0
 END AS [C]

How can I SUM  A+B+C being the results of 3 different CASE WHEN

You did not specify your RDBMS (SQL Server, Oracle, etc.), but if it supports Common Table Expressions, you could encapsulate your main select logic in a CTE and select/sum from it.  Here is a solution using SQL Server.

Declare @T table (A int, B int, C int)
insert into @T(A,B,C) values(null,2,null)

;WITH _cte
AS (
    SELECT CASE
            WHEN A IS NULL
                THEN 1
            ELSE 0
            END AS [A]
        ,CASE
            WHEN B IS NULL
                THEN 1
            ELSE 0
            END AS [B]
        ,CASE
            WHEN C IS NULL
                THEN 1
            ELSE 0
            END AS [C]
    FROM @T
    )
SELECT A + B + C as Results
FROM _cte

Results
2

As a comment pointed out, you don't need a common table expression.  A derived table will also serve the purpose

Declare @T table (A int, B int, C int)
insert into @T(A,B,C) values(null,2,null)
SELECT A + B + C AS Results
FROM (
    SELECT CASE
            WHEN A IS NULL
                THEN 1
            ELSE 0
            END AS [A]
        ,CASE
            WHEN B IS NULL
                THEN 1
            ELSE 0
            END AS [B]
        ,CASE
            WHEN C IS NULL
                THEN 1
            ELSE 0
            END AS [C]
    FROM @T
    ) a

SELECT (A+B+C) AS [SUM]
FROM(CASE
     WHEN A IS NULL THEN 1 ELSE 0
 END AS [A]
, CASE
     WHEN B IS NULL THEN 1 ELSE 0
 END AS [B]
, CASE
     WHEN C IS NULL THEN 1 ELSE 0
 END AS [C])T;


Financial Statement table I want to ask about database design for financial statement.
I want to combine balance sheet, profit loss, cash flow,and ratio into one big table since each statement is dependent each other.

Here is my table structure:

Id,
RegistrationNo,
Year (date),
ProfitLossNetSales,
ProfitLossMonthlySales,
.....,
BalanceReliabilityFinInformation,
BalanceCashBank,
BalanceTimeDeposit,
.....,
CashFlowInterestExpenses,
......

Is it okay to have table structure like this (having many columns in one table)?

Update:
In application, the user input balance sheet form and profit loss form (like input data in excel).
The Cash Flow and Ratio form is read only and the value are based balance and profit loss.

It is OK to have many columns in one table. Indeed, this is the normal situation. It is not OK, however, to mix different types of information in a single table.

Each table models a single class of "thing" in the real world. The rules of normalisation guide a database designer to separate classes from one another. Denormalisation (storing several things in one table) is a legitimate technique but should be used sparingly, with caution, and proper risk mitigation in place.

In your specific case you will have to consider what is your business domain. Are your users interested solely in the balance sheet bottom-line numbers for several corporations over several years (e.g. investment and trading point of view), or do they need the detail of how those numbers are calculated (e.g. an accountancy / book-keeping point of view)? For the former the table you mentions would be OK in my opinion. For the latter you need to model individual transactions, double-entry ledgers etc., and calculate your bottom-line numbers from these as required.

Hey visit this link you may inspire of designs of database.
Really helpful...

Database design for accounting system

you may found other designs as useful as above.

here are some more examples Click here.


How do you test for race conditions in a database? I try to write database code to make sure that it's not subject to race conditions, to make sure that I've locked the correct rows or tables. But I often wonder: Is my code correct? Is it possible to force any existing race conditions to manifest? I want to be sure that if they do happen in a production environment my application will do the right thing.

I generally know exactly which concurrent query is likely to cause a problem, but I've no idea how to force them to run concurrently to see if the correct behavior happens (e.g. I used the correct type of lock), that the right errors are thrown, etc.

Note: I use PostgreSQL and Perl, so if this can't be answered generically it should probably get retagged as such.

Update: I'd prefer it if the solution was programmatic. That way I can write automated tests to make sure there aren't regressions.

I do it all the time with my T-SQL modules.

Essentially, all you need to do is run your modules from two or more connections in a loop for a couple of minutes. Typically, all potential problems are exposed in a few minutes, assuming you have a SQL Server box with decent CPUs.

I wrote a few examples here and here.

As long as you lock rows you should not get at race condtion as that usually is caused when no locking is in place.

But you could get deadlocked if one question blocks your question for too long.

This is hard to test as time for queries can change when the database grows.

Queries that work fine with 100 000 rows of test data goes off the chart with 10 000 000 rows.

This type of problem can be very difficult to find in advance, but many DBs have some method of identifying slow queries.

By using that regulary you should be able to trap any queries heading into trouble with ample warning.

If you do locking on your own, its another story, but there I cannot help.


Select rows with same id but null and some other value in another column for that id I want to get only rows having a value NULL and some other value than NULL for a particular username column.

If both rows have null for that particular username or both have some values other than null then it should not appear in output.  If there are more than two rows for same username with null and some other value then they should appear.

Below is example sample and output. How it can be done using sql query?

+----------+-------+
| username | col2  |
+----------+-------+
| a        | abc   |
| a        | ef    |
| b        | null  |
| b        | null  |
| c        | der   |
| c        | null  |
+----------+-------+

output

+----------+------+
| username | col2 |
+----------+------+
| c        | der  |
| c        | null |
+----------+------+

You should be able to use conditional aggregation to get the username with both a value in col2 as well as null.

I'd suggest using a HAVING clause with the conditions. The query would be similar to:

select username
from yourtable
group by username
having sum(case when col2 is not null then 1 else 0 end) = 1
  and sum(case when col2 is null then 1 else 0 end) = 1

See SQL Fiddle with Demo. This query groups your data by each username and then uses conditional logic to check if col2 meets both conditions you want - where col2 is not null and col2 is null.

You can then use this in a subquery, etc to get the username and col2 values:

select
  t.username,
  t.col2
from yourtable t
inner join
(
  select username
  from yourtable
  group by username
  having sum(case when col2 is not null then 1 else 0 end) = 1
    and sum(case when col2 is null then 1 else 0 end) = 1
) d
  on t.username = d.username

See SQL Fiddle with Demo.

If you have more than one col2 row with both null and another value, then you just need to alter the HAVING clause slightly:

select
  t.username,
  t.col2
from yourtable t
inner join
(
  select username
  from yourtable
  group by username
  having sum(case when col2 is not null then 1 else 0 end) >= 1
    and sum(case when col2 is null then 1 else 0 end) >= 1
) d
  on t.username = d.username;

See SQL Fiddle with Demo

I tried with this one...

select a.username from
(select username ,col2
   from yourtable
where col2 is null) a,(select username ,col2
                       from yourtable
                        where col2 is not null) b
where a.username=b.username;


Query optimization [Oracle] The Query:

select JOIN_RESULT.batch_id as BATCH_ID,
       JOIN_RESULT.unique_doc_id as DOCUMENT_ID,
       JOIN_RESULT.brand as BRAND,
       JOIN_RESULT.message_date_time as UPLOAD_DATE,
       decode(JOIN_RESULT.recordstatus, '5', 'SUCCESS', 'FAILED') as UPLOAD_STATUS,
       decode(JOIN_RESULT.recordstatus,
              '5',
              null,
              (decode(STATUS_RESULT.vendor_unique_doc_id,
                      JOIN_RESULT.unique_doc_id,
                      STATUS_RESULT.comments,
                      JOIN_RESULT.comments))) as FAILURE_REASON
  from (select d.vendor_unique_doc_id, d.comments
          from doc_duplicate_b d
         inner join doc_recon_record_w r on d.vendor_unique_doc_id =
                                                      r.unique_doc_id) STATUS_RESULT
  full join (select r.batch_id,
                    r.unique_doc_id,
                    r.brand,
                    h.message_date_time,
                    r.recordstatus,
                    r.comments
               from doc_recon_record_w r
              inner join doc_recon_header_w h on r.fileid =
                                                           h.fileid) JOIN_RESULT on STATUS_RESULT.vendor_unique_doc_id =
                                                                                JOIN_RESULT.unique_doc_id
 order by JOIN_RESULT.batch_id

  The Question:

How can I optimize the performance of this query?

Using joins good or is it better to do a Cartesian Product?

Here is a modified query that should be faster and produce the same results:

SELECT r.batch_id, r.unique_doc_id AS DOCUMENT_ID, r.brand,
   (SELECT h.message_date_time FROM doc_recon_header_w h WHERE r.fileid = h.fileid)
      AS UPLOAD_DATE,
   decode(r.recordstatus, '5', 'SUCCESS', 'FAILED') AS UPLOAD_STATUS,
   decode(r.recordstatus, '5',  null,
      (decode(d.vendor_unique_doc_id, r.unique_doc_id, d.comments, r.comments)))
      AS FAILURE_REASON
FROM doc_recon_record_w r
LEFT JOIN doc_duplicate_b d ON d.vendor_unique_doc_id = r.unique_doc_id
ORDER BY r.batch_id;

Reasoning:

Doc_recon_header_w is only joined to get the message_date_time, so that is moved to the select list so it will occur only on the results that will be displayed.  Note that this won't work if there is more than one row per fileid in the table or a header record does not exist for a record, but I wouldn't expect either of these to be the case.
The join on doc_recon_record with doc_duplicate_b is done only to check for the existence of a record in the former, which means the join doesn't need to be a FULL JOIN and can be a LEFT JOIN if the table order is re-arranged.

Here is the  updated SQL query.

select r.batch_id as BATCH_ID,
       max(r.unique_doc_id) as DOCUMENT_ID,
       max(r.brand) as BRAND,
       max(h.message_date_time) as UPLOAD_DATE,
       max(case
         when STATUS_RESULT.vendor_unique_doc_id = r.unique_doc_id then
          STATUS_RESULT.comments
         else
          decode(r.recordstatus, '5', 'SUCCESS', r.comments)
       end) as UPLOAD_STATUS
  from (select d.vendor_unique_doc_id, d.comments
          from doc_duplicate_b d,
               doc_recon_record_w r
         where d.vendor_unique_doc_id = r.unique_doc_id) STATUS_RESULT,
       doc_recon_record_w r,
       doc_recon_header_w h
 where r.fileid = h.fileid
      group by r.batch_id

for your second question its all depend on the indexing of the source table.  You may use query explain to view performance.


T-SQL - AOAG Health state Healthy or Not Healthy in a Single row I am trying to write a small piece of code which should give the status of the AOAG SQL Server 2014 Health or not healthy based on below two conditions .

Mine is 3 Node replica AOAG (including BCP Replica).

Conditions I am looking for :

If the synchronization_health_desc is "Healthy" for all three replicas , I should get the result as below :

If the synchronization_health_desc is "Not Healthy" for any of the three replicas , I should get the result as below :

So far I have tried below :

select dns_name as AG_Listener ,
(select top 1 group_name  from sys.dm_hadr_availability_replica_cluster_nodes  ) as AG_Gorup,
synchronization_health_desc as AG_Status
from sys.dm_hadr_availability_replica_states rs
INNER JOIN  sys.availability_group_listeners gl ON rs.group_id = rs.group_id

The output is below . But I want only one row as result which says overall result as Healthy or Not Healthy based on above two conditions .

You have two states - everything is healthy or something is not healthy. This applies per availability group. So if anything's health is 'NOT_HEALTHY' that's the group's status, otherwise the status is 'HEALTHY'.

There are various ways to find if anything is unhealthy. One is EXISTS. Another is aggregate functions, some of which also work on character types. MAX() & MIN() are the useful ones. In a dictionary sort 'NOT_HEALTHY' > 'HEALTHY' so MAX('HEALTHY', 'NOT_HEALTHY') will return 'NOT_HEALTHY'. There is only one MAX in any GROUP BY so this removes the need for TOP clauses.

The query will look like

select
  case max (synchronization_health_desc)
    when 'NOT_HEALTHY' then 'NOT_HEALTHY'
    else 'HEALTHY'
  end
from <those tables, joined>
group by
  group_name

Once the GROUP BY is introduced other returned columns must also be in aggregate functions or mentioned in the GROUP BY clause.

This is how I made it work , hope this helps someone.

select top 1 dns_name as AG_Listener,
(select top 1 group_name  from sys.dm_hadr_availability_replica_cluster_nodes  ) as AG_Group,
(select distinct case
when exists(select * from sys.dm_hadr_availability_replica_states where synchronization_health_desc = 'NOT_HEALTHY') then 'NOT_HEALTHY'
else 'HEALTHY' end  from sys.dm_hadr_availability_replica_states) as AG_Status

from sys.dm_hadr_availability_replica_states rs
INNER JOIN  sys.availability_group_listeners gl ON rs.group_id = rs.group_id


Optimizing a query with ORDER BY in a derived table The query below takes too long to execute (58 seconds). If I run two more queries like this with different nid values, the second and third query take much more time to execute. How can I optimize it?

SELECT consoleId,
       servertime,
       servertime AS servertimeUNIX,
       nocName,
       eventIdx,
       nocStatus  AS status,
       nid,
       site,
       machine,
       clientversion,
       timeExecuted,
       count(*)   AS eventCount
FROM   (SELECT consoleId,
               servertime,
               nocName,
               eventIdx,
               nocStatus,
               nid,
               site,
               machine,
               clientversion,
               timeExecuted
        FROM   temptest
        ORDER  BY tid DESC) AS x
WHERE  servertime >= 1367902800
       AND servertime <= 1370581199
       AND nid = 1124
GROUP  BY FROM_UNIXTIME(servertime, '%Y-%m-%d'),
          site,
          machine,
          nid;

The table definition is

CREATE TABLE `temptest`
  (
     `tid`                INT(10) NOT NULL AUTO_INCREMENT,
     `eventIdx`           INT(11) NOT NULL DEFAULT '0',
     `servertime`         INT(11) DEFAULT NULL,
     `nocName`            VARCHAR(50) DEFAULT NULL,
     `site`               VARCHAR(50) DEFAULT NULL,
     `machine`            VARCHAR(64) DEFAULT NULL,
     `clientversion`      VARCHAR(20) DEFAULT NULL,
     `nid`                INT(10) DEFAULT NULL,
     `nocStatus`          VARCHAR(250) DEFAULT NULL,
     `consoleId`          INT(11) DEFAULT NULL,
     `timeExecuted`       VARCHAR(100) DEFAULT NULL,
     `machineManufacture` VARCHAR(100) DEFAULT NULL,
     PRIMARY KEY (`tid`),
     KEY `consoleindex` (`consoleId`),
     KEY `nitification` (`servertime`, `nid`)
  )
ENGINE=InnoDB
AUTO_INCREMENT=8229683
DEFAULT CHARSET=latin1

SELECT consoleId,
       servertime,
       servertime AS servertimeUNIX,
       nocName,
       eventIdx,
       nocStatus  AS status,
       nid,
       site,
       machine,
       clientversion,
       timeExecuted,
       count(*)   AS eventCount
FROM   temptest
WHERE  servertime >= 1367902800
       AND servertime <= 1370581199
       AND nid = 1124
GROUP  BY FROM_UNIXTIME(servertime, '%Y-%m-%d'),
          site,
          machine,
          nid;


How to select a row based on the maximum value of a column, when ties are expected SUBSCRIBER  DATE         TIME            VALUE
aaa         23-04-2015   04:04:07        10
aab         23-04-2015   12:04:32        5
bbb         23-04-2015   01:04:05        20
bbb         23-04-2015   12:04:20        20
bbb         23-04-2015   12:04:31        20
ccc         23-04-2015   05:04:00        10

I need a query to get the subscriber details based on the maximum value (and the minimum time if there are ties on max(value)) for a particular subscriber.

select subscriber, date_column, time_column, value from (
  select
    subscriber, date_column, time_column, value,
    rank() over (partition by subscriber order by value desc, time_column) as r
  from mytab
) where r = 1
and subscriber = 'ccc';

The following should work as per your requirement. However, if there is a tie on the time too, it would pick a random row. You may want to choose a combination that would give you a unique row.

select myTable.* from myTable INNER JOIN
    (select top 1 Value, Time
from myTable where subscriber = 'aaa'
order by Value desc, Time ) TT
ON myTable.Value = TT.Value
and myTable.time = TT.time
where subscriber = 'aaa'


Dependency Preservation and Normal Forms Suppose a set of functional dependencies F holds over a relation R . We find that the dependencies violate the conditions of 2NF and decompose the relation into R1 and R2. After the process what can i say about the set R. In other words are all the dependencies preserved if not how to we find the new set of dependencies.
Do we need to specify the dependency set for each of the decomposed relation?

As an example let us consider the set

R = {a, b, c, d, e, f, g , h , i , j}

F = {AB -> C, A -> DE, B -> F, F -> GH, D -> IJ }

Computing the closure we find AB to be a candidate key of R
this leads to the violation of 2NF by the dependency A -> DE We compute A+ = {A , D , E , I , J }

the Decomposition therefore is R1 = {B , C , F , G ,H}  R2 = { A , D , E , I , J}

After this step i am unsure how to check the new relations for violations , which set of dependencies do i take as a reference for checking for violations.

The way we have constructed R2 ensures no violation of 2NF  but what about the Relation R1?

Given R(A B C D E F G H I J ) and the FD's specified above
We would get three relations after normalizing it till 2NF:
Since the FD: A->DE is not satisfying 2NF
Finding A closure we get (ADEIJ).
Since the FD B-> F also does not satisfy 2NF
Finding B Closure we get (BFGH).
Hence we get the relations:
R1(ADEIJ) and R2(BFGH).
A is the Candidate Key for R1; B is the Candidate Key for R2
What remains from R is C.
So we get the third relation R3 (ABC).

This is a dependency preserving and loss less decomposition.

We could get there relation:
Since A- DE is not satisfying 2NF
Finding A Closure (ADEIJ)
Since B-F also does not satisfy 2NF
Finding B Closure  (BFGH)
What remains in R is C
Hence weshould get three relations:
R1 (ADEIJ)
R2 (BFGH)
R3 (ABC)
Now this would be dependency preserving and loss less decomposition.


Cross Database Queries using IBM Data Studio I'm currently using IBM Data Studio 4.1 to run queries against DB2 LUW database(s).
My understanding is that I can do cross database queries by prefixing the database(s) names.
For example, database.schema.table.
However, when I open a SQL Query window, there is a dropdown that requires the user to select the database connection. Since it only allows one database connection, how do we run cross database queries in IBM Data Studio?

For you to execute any SQL statement you have to be connected to some database server; Data Studio does not execute SQL statements. Subsequently, for the three part name (<server>.<schema>.<object>) to work the server you're connected to has to know what the <server> part is.
In the simple case of accessing objects in a database that belongs to the same Db2 for LUW database instance, <server> is the other database name, and no additional setup is required.
However, if the other table is in a database managed by a different instance, or if it belongs to a different DBMS (Db2 for z/OS, Oracle, etc.), you will need to set up a federated data source, whose name you will then use for <server>.

I'm not familiar with DataStudio - their are other tools in the market like https://dbeaver.com/databases/ or https://www.dbvis.com.
Their is a workaround for the last mentioned tool
https://support.dbvis.com/support/solutions/articles/1000196586-can-i-access-tables-across-multiple-databases-within-one-statement
PS
I agree with - mustaccio - the best way is the setup federation -
depending on license issue's
https://www.ibm.com/docs/en/db2/11.1?topic=editions-functionality-in-db2-product-db2-offerings


Explaining 2NF vs 3NF with an example I have a problem with second normal form (2NF) and I have been unable to solve it by using Google. It is making me crazy because I am a teacher and I don't want to teach wrong stuff to my students.

Let's have a table with 5 fields.

Gradings = {StudentName, SubjectCode, SubjectName, #Exam, Grade}

Dependencies are this way:

StudentName, SubjectCode, #Exam -> Grade

SubjectCode -> SubjectName

SubjectName -> SubjectCode

Therefore, candidate key 1 is {StudentName, SubjectCode, #Exam} and candidate key 2 is {StudentName, SubjectName, #Exam}.

Prime attributes are {StudentName, SubjectCode, SubjectName, #Exam} and non-prime attributes is Grade

According to the definition of second normal form, a non-prime attribute cannot depend on a part of a candidate key. The only non-prime attribute (Grade) does not depend on a part of a candidate key so this table seems in 2NF .

The problem is that I think something is amiss (and I could be wrong). I think subjects should have their own table.

Gradings = {StudentName, Subject Code, #Exam, Grade}

Subjects = {Subject Code, SubjectName}

But 2NF does not produce this. 3NF is about dependencies between non-prime attributes so it does not produce this either. But it seems to me that this is the right outcome, because it has no redundancy.

I guess if non-prime attribute was defined as "attribute that is not a candidate key", 2NF would produce the desired result. But I have checked this again and again and non-prime attribute is defined as "attribute that does not BELONG to a candidate key".

What am I doing wrong?

Your relation is in 3NF, (and not only in 2NF), since as you say the only non prime attribute is Grade, which only appears on the right hand side of your FDs.

The relation is not in BCNF, because the left hand side of the two small FDs is not a superkey.

You can, however, losslessly decompose the relation to
(SubjectCode, SubjectName) and either
(StudentName, SubjectCode, #Exam, Grade)
 or
(StudentName, SubjectName, #Exam, Grade)

This decomposition gives you two BCNF relations and preserves all functional dependencies. This isn't always possible (you can always decompose a relation to 3NF, but not necessarily to BCNF).

2NF

If you want an example of 2NF (and not 3NF), your relation needs to contain transitive dependencies.

For instance, say you have a Score column. Intuitively Score->Grade since all exams with the same score should get the same grade (it would be rather unfair otherwise), but note that we cannot say Grade->Score since several scores can have the same grade (11% and 12% would likely be "Fail", for instance).

Now your relation is:

Gradings(StudentName, SubjectCode, SubjectName, #Exam, Score, Grade)

and you have a new form of redundancy since every time you enter a result with the same score as another Gradings record you also have to repeat the corresponding Grade. To get to 3NF you could therefore decompose to

ScoreGrades(Score,Grade)

with Score as the key, and

Scores(StudentName, SubjectCode, SubjectName, #Exam, Score)

I'm preparing to delete this as it is considered incorrect

Subject Name is also a non-prime attribute and it depends on part of the primary key Subject Code (breaks rule - there must not be any partial dependency of any column on primary key).

This is prohibited in 2nd Normal Form and should therefor be placed in it's own table as you have suspected.

I think where you came unstuck is in identifying two sets of candidate keys, when you create the table you must choose one set of candidate keys to make the primary key. The remaining columns become non-prime attributes i.e., if you chose your second candidate key, Subject Code becomes a non-prime attribute dependent on part of the primary key (Subject Name) and should be placed in it's own table.

It is important to teach 1st, 2nd and 3rd normal forms in order as they build on each other. BCNF is also essentially an extension to 3rd normal form so a strong grasp on the lower levels is essential.

Further; an experienced developer will not consider the independent levels of normalisation because many rules become intuitive.

They will also know when to break normalisation rules to solve certain design and optimisation problems. Normalisation should be treated as a guide to good design not a stringent rule, I believe that would also be a good teaching point.


How to export data from multiple tables to csv from remote postgres server (AWS RDS) to local machine? I need to export data in CSV format from many (undertimined amount) tables from a remote postgres (aws RDS PostgreSQL 10.6) server to a local machine.
When the server was in its own machine (AWS EC2) this was possible with a function that used a loop to iterate over the necesary tables then used execute and COPY commands to create the CSV files directly onto the same server from which they could by copied over where needed.
But since the server was migrated to an RDS instance, I can't use that approach anymore because I have no direct access to the filesystem on the postgres server. I can only connect to it remotely via a client.
Every solution I've seen until now requires either filesystem access to the server or works with only one set of data at time with a known table that produces only one csv file on the local machine.
Is there a way to achieve what I need ?

if it was on premises, only thing you need to is to mount it first and then and run this as a sql query :
DO $$
DECLARE ssql varchar := 'COPY ( SELECT  * from schema.table1   ) TO ''/projeler2/Raporlar/SatisRaporlari/test'
|| TO_CHAR(NOW(),'DD_MM_YYYY')
|| '.csv'' WITH CSV DELIMITER '','' HEADER QUOTE ''"''  ;';

BEGIN
    EXECUTE ssql;
    --raise notice 'Value: %', ssql;
END; $$;

but remote server may change the game. give it a try


Why is != not working? how to compare with null values? I am having a hard time understanding why the following query is not working?

select id,sold_dealer_id from myi_corporate where sold_dealer_id != 36;

When I do the following I am getting the result

select id,sold_dealer_id from myi_corporate where sold_dealer_id = 36;

However for != I am not getting the opposite results.

I am returning to psql after a long time after using MongoDB and might have forgotten the basics. Any suggestion is much appreciated

You can use != rather than the SQL standard operator <>, but I recommend using the latter. That has no influence on your problem though.

Your table must contain some NULL values in sold_dealer_id.

Now NULL = 36 is not true, but NULL <> 36 is also not true, so such lines are excluded from both query results.

You can use sold_dealer_id IS DISTINCT FROM 36 to get the opposite of sold_dealer_id = 36. That operator will treat NULL values as if they were normal values.

By using correct postgres-Syntax...

select id,sold_dealer_id from myi_corporate where NOT (sold_dealer_id = 36);

Postgres is a database, not a c-family programming language

https://www.techonthenet.com/postgresql/not.php with some examples


How to do 10 G Database recovery without archive logs but available redo logs, ctl files and .dbf files My client's Oracle 10g database crashed abruptly on Win 2003 NT. The oracle was installed on C drive and redo logs, ctl files and .dbf files were in D:
It was unable to backup C: but successfully backup D:
Later the system was rebuild with updated RAM and high disk size. Oracle is re_install in C: as it was and latest .dbf, CTL and redo logs in D:

Not sure if the below steps would solve the purpose. Please suggest ASAP.

SQL> startup nomount;
SQL> alter system set control_files
Then you mount the database so control file can be read and written
SQL> alter database mount;
SQL> alter database rename file for .dbf files
SQL> alter database rename file for redo logs

If it would be using PFILE then will use Init.ora to update the location.

SQL>startup mount
SQL> ALTER DATABASE OPEN;

Alright, let's assume you have full backup without archivelog. And your backup is output from rman backup such as rman> BACKUP DATABASE;.

So, I'll explain how to restore it.

1) Make sure your DBID is same as your database. (Note : this process is used if you have full backup datafiles,controlfiles,spfile, and copy archivelog if you have.)

SQL> SELECT DBID,OPEN_MODE FROM V$DATABASE; (if you can access your database)
RMAN> SHUTDOWN IMMEDIATE;STARTUP NOMOUNT;
RMAN> SET DBID=your_db_id

ex : G:\rman_backup is your backup location.
If you just to restore your controlfile and datafile, ignore process number 1, continue to process 2.

2) Make sure your autobackup location of CONTROLFILE, SPFILE and DATAFILE also ARCHIVELOG ex : "D:\FLASH_RECOVERY_AREA\your_sid\AUTOBACKUP\2016_06_16\O1_MF_S_914709345_CP5HHNCS_.BKP" or"G:\rman_backup\O1_MF_S_914709345_CP5HHNCS_.BKP".

In this example i assume you have activate your autobackup controlfile, if you don't need to restore your spfile skip this. you can next to restore control file.

RMAN> RESTORE SPFILE TO 'C:\app\Administrator\SPFILESIMKARI.ORA' FROM 'G:\rman_backup\O1_MF_S_914709345_CP5HHNCS_.BKP';
RMAN> SHUTDOWN IMMEDIATE;STARTUP NOMOUNT;
RMAN> RESTORE CONTROLFILE FROM 'G:\rman_backup\O1_MF_S_914709345_CP5HHNCS_.BKP';
RMAN> SHUTDOWN IMMEDIATE;STARTUP MOUNT;
RMAN> LIST BACKUP;
RMAN> CATALOG START WITH 'G:\rman_backup'; -- This is optional, if your control file can show "list backup" you don't need to run this. If not, you must to run this script.
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN; -- If you need to reset your sequence, or error you can "alter database open resetlogs", if not "alter database open noresetlogs";
RMAN> exit

For additional information, you can startup unmount if your SPFILE is configured properly. And `startup mount' if your controlfile is configured properly.
Hope this help you out, please comment if you've trouble.


Customized date range in sql We have got a customized quarter list from client which we need to take reference to get data in SQL. Could you please help.

30-Oct  29-Jan AS Quarter1
30-Jan  30-Apr AS Quarter2
01-May  30-Jul AS Quarter3
31-Jul  29-Oct AS Quarter4

If today falls between any of the quarter we need start date in date format
for example if today is Jan/02/2018 then it shall return start date as 30-10-2017 00:00:00 and end date as 29-01-2018 00:00:00
if today is Dec/30/2017 then it shall return start date as 30-10-2017 00:00:00 and end date as 29-01-2018 00:00:00

Assumption:

some batch work (either as part of a SQL batch, or perhaps a user-defined function) is acceptable (otherwise the batch code could be rolled into a couple additional CTEs)

A batch/CTE solution:

declare @mydate  datetime,

        @mymon   int,
        @myday   int,
        @myyr    int,

        @startyr char(4),
        @endyr   char(4)

select  @mydate = '01/02/2018'

-- break @mydate into numeric components to make it a little easier in the
-- next step to determine our start/end years

select  @mymon = month(@mydate),
        @myday = day(@mydate),
        @myyr  = year(@mydate)

-- determine our start/end years based on current year of @mydate; since we'll
-- be feeding these variables into a convert(datetime,<string>) construct, go
-- ahead and convert to char(4) here in order to minimize coding later

select  @startyr = convert(char(4), case when @mymon = 10 and @myday >= 30 then @myyr
                                         when @mymon in (11,12)            then @myyr
                                                                           else @myyr - 1
                                    end),
        @endyr   = convert(char(4), case when @mymon = 10 and @myday >= 30 then @myyr + 1
                                         when @mymon in (11,12)            then @myyr + 1
                                                                           else @myyr
                                    end);

-- use a CTE to build our list of quarters, using the @starty/@endyr variables
-- to create the datetime ranges that make up the quarters in which
-- @mydate date can fall within

with
quarters as

(select *
 from   (values ('Quarter1', convert(datetime,'10/30/'+@startyr), convert(datetime,'01/29/'+@endyr) ),
                ('Quarter2', convert(datetime,'01/30/'+@endyr),   convert(datetime,'04/30/'+@endyr) ),
                ('Quarter3', convert(datetime,'05/01/'+@endyr),   convert(datetime,'07/30/'+@endyr) ),
                ('Quarter4', convert(datetime,'07/31/'+@endyr),   convert(datetime,'10/29/'+@endyr) ) ) as q(qtr, qstart, qend)
)

-- now it's just a matter of finding which datetime range (aka quarter)
-- @mydate falls between

select q.qtr, q.qstart, q.qend

from   quarters q
where  @mydate between q.qstart and q.qend;

Some sample runs (changing the value of @mydate):

-- @mydate = '10/29/2017'

 qtr      | qstart              | qend
 -------- | ------------------- | -------------------
 Quarter4 | 07/31/2017 00:00:00 | 29/10/2017 00:00:00

-- @mydate = '10/30/2017'

 qtr      | qstart              | qend
 -------- | ------------------- | -------------------
 Quarter1 | 30/10/2017 00:00:00 | 29/01/2018 00:00:00

-- @mydate = '08/17/2016'

 qtr      | qstart              | qend
 -------- | ------------------- | -------------------
 Quarter4 | 31/07/2016 00:00:00 | 29/10/2016 00:00:00

-- @mydate = '01/23/2016'

 qtr      | qstart              | qend
 -------- | ------------------- | -------------------
 Quarter1 | 30/10/2015 00:00:00 | 29/01/2016 00:00:00

Here's a dbfiddle

I'm not sure why this question is poorly received, what you want is something like this.. Here I use PostgreSQL which is a free database with convenient row syntax.

SELECT m,d,
  CASE
    WHEN (m,d) BETWEEN (1,1)   AND (1,29)   THEN 'Quarter1'
    WHEN (m,d) BETWEEN (1,30)  AND (4,30)   THEN 'Quarter2'
    WHEN (m,d) BETWEEN (5,1)   AND (7,30)   THEN 'Quarter3'
    WHEN (m,d) BETWEEN (7,31)  AND (10,29)  THEN 'Quarter4'
    WHEN (m,d) BETWEEN (10,29) AND (12,31)  THEN 'Quarter1'
  END AS q

FROM generate_series(timestamp '1-1-2016', timestamp '1-1-2017', '1 day')
  AS gs(dt)
CROSS JOIN LATERAL ( VALUES
  ( EXTRACT(month FROM gs.dt)::smallint, EXTRACT(day FROM gs.dt)::smallint )
)
  AS t(m,d);

You can put that lengthy case statement in a SQL function to make things a bit nicer.

CREATE FUNCTION my_quarter( m smallint, d smallint )
RETURNS smallint
AS $$
  SELECT CASE
        WHEN (m,d) BETWEEN (1,1)   AND (1,29)   THEN 1::smallint
        WHEN (m,d) BETWEEN (1,30)  AND (4,30)   THEN 2::smallint
        WHEN (m,d) BETWEEN (5,1)   AND (7,30)   THEN 3::smallint
        WHEN (m,d) BETWEEN (7,31)  AND (10,29)  THEN 4::smallint
        WHEN (m,d) BETWEEN (10,29) AND (12,31)  THEN 1::smallint
  END;
$$ LANGUAGE 'sql'
IMMUTABLE;

Now it's simple..

SELECT m,d, 'Quarter' || my_quarter(m,d) AS q
FROM generate_series(timestamp '1-1-2016', timestamp '1-1-2017', '1 day')
  AS gs(dt)
CROSS JOIN LATERAL ( VALUES
  ( EXTRACT(month FROM gs.dt)::smallint, EXTRACT(day FROM gs.dt)::smallint )
)
  AS t(m,d);

All that stuff that may look a bit foreign, all it does is generates date-sequence (test data), and extract from that sequence the month and the day.

Output looks like this,

 m  | d  |    q
----+----+----------
  1 |  6 | Quarter1
  1 | 15 | Quarter1
  1 | 25 | Quarter1
  3 | 29 | Quarter2
  4 | 12 | Quarter2
  4 | 15 | Quarter2
  5 | 22 | Quarter3
  6 | 11 | Quarter3
  6 | 18 | Quarter3
  6 | 28 | Quarter3
  7 |  3 | Quarter3
  9 |  2 | Quarter4
  9 | 22 | Quarter4
 10 | 17 | Quarter4
 10 | 25 | Quarter4
(15 rows)

Microsoft's syntax is usually a little bit longer, and less polished.


How to insert (file) data into a PostgreSQL bytea column? This question is not about bytea v. oid v. blobs v. large objects, etc.

I have a table containing a primary key integer field and a bytea field. I'd like to enter data into the bytea field. This can, presumably, be done by one of the PL/ languages, and I may look into doing this with PL/Python in the future.

As I am still testing and experimenting, I would simply like to insert data from a file (on the server) using "standard" SQL statements. I am aware that only administrators with write permission on the server would be able to insert data in the way I would like to. I'm not concerned about that at this stage as users would not be inserting bytea data at present. I have searched the various StackExchange sites, the PostgreSQL Archives and the Internet generally, but have not been able to find an answer.

Edit: This discussion from 2008 implies that what I want to do is not possible. How are bytea fields used then?

Edit: This similar question from 2005 remains unanswered.

Solved: The details provided here on the psycopg website provided the basis for a solution I've written in Python. It may also be possible to insert binary data into a bytea column using PL/Python. I don't know if this is possible using "pure" SQL.

as superuser:

create or replace function bytea_import(p_path text, p_result out bytea)
                   language plpgsql as $$
declare
  l_oid oid;
begin
  select lo_import(p_path) into l_oid;
  select lo_get(l_oid) INTO p_result;
  perform lo_unlink(l_oid);
end;$$;

lo_get was introduced in 9.4 so for older versions you would need:

create or replace function bytea_import(p_path text, p_result out bytea)
                   language plpgsql as $$
declare
  l_oid oid;
  r record;
begin
  p_result := '';
  select lo_import(p_path) into l_oid;
  for r in ( select data
             from pg_largeobject
             where loid = l_oid
             order by pageno ) loop
    p_result = p_result || r.data;
  end loop;
  perform lo_unlink(l_oid);
end;$$;

then:

insert into my_table(bytea_data) select bytea_import('/my/file.name');

Use the Postgres COPY BINARY function. This is broadly equivalent to Oracle's external tables.


mysqldbcompare SELECT command denied to user I am trying to run mysqldbcompare to see the difference between two tables:

mysqldbcompare --server1=server1:2016@localhost --server2=server2:2016@localhost user:user --difftype=sql --run-all-tests

I get this error. I have already granted all privileges to users.

  ERROR: Query failed. 1142 (42000): SELECT command denied to user 'server1'@'localhost' for table 'proc'

You should enable the dbuser to have READ permissions to the proc table.

  The process attempts to perform a SELECT statement on the 'proc'
  table.  The 'proc' table is a system table which retains the
  procedures of all users on that mysql instance.  On shared hosting,
  allowing userA to see the procedures of userB, which would include
  information about table structure, etc., would be considered breach of
  security.All info found here

Or you can use the root user.

Add this to the connection string

Use Procedure Bodies =false;


SQL Server database recovery By mistake one of my colleague in the office has restored .mdf and .ldf files of another database to production with existing database name. So existing database was overwritten with new backup.

Is there any chance to recover that db back?

xyz database - .mdf & .ldf - restored as abc database (but already existing in production)

SQL Server recovery tools cannot help here, they can only recover lost information from database files (mdf, ldf, ndf, bak, trn). You should search for a way to recover overwritten files in Windows

One of the solutions is to use the File History option in Windows. Your Windows version must support it and it had to be enabled at the time when the files were overwritten and

"File History saves copies of your files so you can get them back if they are lost or damaged"

Restore files or folders using File History


How to optimize indexes on MySQL query with various sorts I have an INNODB table levels:

+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| id                 | int(9)       | NO   | PRI | NULL    |       |
| level_name         | varchar(20)  | NO   |     | NULL    |       |
| user_id            | int(10)      | NO   |     | NULL    |       |
| user_name          | varchar(45)  | NO   |     | NULL    |       |
| rating             | decimal(5,4) | NO   |     | 0.0000  |       |
| votes              | int(5)       | NO   |     | 0       |       |
| plays              | int(5)       | NO   |     | 0       |       |
| date_published     | date         | NO   | MUL | NULL    |       |
| user_comment       | varchar(255) | NO   |     | NULL    |       |
| playable_character | int(2)       | NO   |     | 1       |       |
| is_featured        | tinyint(1)   | NO   | MUL | 0       |       |
+--------------------+--------------+------+-----+---------+-------+

There are ~4 million rows. Because of the front-end functionality, I need to query this table with a variety of filters and sorts. They are on playable_character, rating, plays, and date_published. The date_published can be filtered to show by the last day, week, month, or anytime(last 3 years). There's also paging. So, depending on the user choices, the queries can look, for example, like one of these:

SELECT * FROM levels
WHERE playable_character = 0 AND
    date_published BETWEEN date_sub(now(), INTERVAL 3 YEAR) AND now()
ORDER BY date_published DESC
LIMIT 0, 1000;

SELECT * FROM levels
WHERE playable_character = 4 AND
    date_published BETWEEN date_sub(now(), INTERVAL 1 WEEK) AND now()
ORDER BY rating DESC
LIMIT 4000, 1000;

SELECT * FROM levels
WHERE playable_character = 5 AND
    date_published BETWEEN date_sub(now(), INTERVAL 1 MONTH) AND now()
ORDER BY plays DESC
LIMIT 1000, 1000;

I should add that rating and plays are always queried as DESC. Only date_published may be either DESC or ASC.

I started out with an index idx_date_char(date_published, playable_character) that worked great on the first example query here. Based on some other answers, I changed to two other indexes (date_published, playable_character, plays) and (date_published, playable_character, rating).

The first query still runs very fast, however there's some unusual things happening in EXPLAIN, when player_character = x exceeds a certain number of rows (~700,000): the USING WHERE pops on in EXPLAIN.

So, first question is are there any improvements in the query or indexes possible, and, second, what MySQL settings should get altered to allow for the large result sets.

Any suggestions greatly appreciated. TIA.

WHERE playable_character = 0 AND
    date_published BETWEEN date_sub(now(), INTERVAL 3 YEAR) AND now()

Start with the "=" item, then do the range:

INDEX(playable_character, date_published);

"Pagination", a la ORDER BY rating DESC LIMIT 4000, 1000; is best done by remember where you "left off". That way, you don't have scan over the 4000 records that you don't need.

Everybody here are missing one point - SQLs in question are retrieving 1000 rows. And one cannot retrieve 1000 rows fast unless most of the data is cached. If data is not cached one has to do 1000 random reads sequentially to retrieve the data.

Good disk based storage with fast disks will give you up to ~200 reads per second. Common disks even in RAID I doubt that manage even 100. That means 10+ seconds to get results even with the best indexes.

So in the longer run such data model and queries won't work. Now the queries run fast when you hit cached data.


What are some best practices for using schemas in SQL Server? I understand the capabilities of SQL Server Schemas, but what are the best practices?  Sure they give another layer of security, and provide logical grouping of database objects within the database, but what is typical out there?  I often, in my experience, don't see many custom made schemas utilized.  Is this typical?  Is it the less-frequent scenario where custom schemas should be used?

We use them

to separate permissions per client (eg we have desktop, WebGUI etc schemas)
for logical grouping eg (Data and Staging schemas for our tables).

Useful and practical observations past the white paper mentioned by Marian:

GRANT on the schema: no more permissions per object. So a new proc in the WebGUI schema automatically has the permissions of the schema
Nice groupings in SSMS Object Explorer
OBJECT_SCHEMA_NAME
You are forced to qualify object names (which is best practice)

You may want to go to some of the foundational texts on this to address some of your questions.   Documents on the relational database model from E. F. Codd and C. J. Date address many common questions related to design, performance, security, optimal schema design etc. Ultimately, as the fathers of the modern relational database model, their works stand as the foundation for DB2, ORACLE, SQL Server (Microsoft and Sybase), Ingres, MySQL and every other relational database solution set available.


Transaction Log 16GB on 2MB database Possible Duplicate:
  Why is My SQL Server Transaction Log Growing or Running out of Space?

The transaction log in one of my client's databases is 16GB, when the database itself is only 2MB. I have begun backups of the transaction log and they occur every half an hour.

I have attempted to run DBCC SHRINKFILE (Log,size) but it says all virtual pages are in use.

Does this mean there is an uncommited transaction or something? The file has been this big for a while now. Am I able to somehow truncate the log?

Was the database in FULL recovery but no backups being taken previously? Paul Randall's article Running Out of Transaction Log Space covers the other likely scenarios.

The DMV query below will tell you why the log cannot be truncated. If you're unsure how to interpret the output, add the response to your question.

SELECT
    db.[name] AS [Database Name]
  , db.recovery_model_desc AS [Recovery Model]
  , db.log_reuse_wait_desc AS [Log Reuse Wait Description]
  , ls.cntr_value AS [Log Size (KB)]
  , lu.cntr_value AS [Log Used (KB)]
  , CAST(CAST(lu.cntr_value AS FLOAT)
    / CAST(ls.cntr_value AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Log Used %]
FROM
    sys.databases AS db
INNER JOIN
    sys.dm_os_performance_counters AS lu
ON  db.name = lu.instance_name
INNER JOIN
    sys.dm_os_performance_counters AS ls
ON  db.name = ls.instance_name
WHERE
    lu.counter_name LIKE N'Log File(s) Used Size (KB)%'
AND ls.counter_name LIKE N'Log File(s) Size (KB)%'
AND ls.cntr_value > 0
OPTION (RECOMPILE);

backup your database

change to simple recovery mode,

change back to full recovery mode

magic


MySQL metadata function to get projected column type in query? Is it possible to get the field type from a MySQL query, in the same way you can get it from a table with the SHOW COLUMNS command? Such as from a derived table,

SELECT x -- presumedMetaFn(x) -- returns "int"
FROM (
  SELECT 1 AS x
  UNION SELECT 2
) AS t;

Given the above query, is there a function or something that I can use to get the type of x? PostgreSQL makes this available with the System Information Functions pg_typeof

SELECT x, pg_typeof(x)
FROM ( VALUES (1),(2) ) AS t(x);
 x | pg_typeof
---+-----------
 1 | integer
 2 | integer
(2 rows)

I am not looking for metadata on a table, but from the result of query. With psql on PostgreSQL 11+, this is also possible by running \gdesc after the query.

Within MySQL, you can get this information by creating a temporary table, then using DESCRIBE on that temporary table:

CREATE TEMPORARY TABLE `temp`
SELECT ...
FROM ...
LIMIT 0;

DESCRIBE `temp`;

We cannot just use DESCRIBE on the original table because the column we want to know the type of is a calculated column, not something being directly pulled from a table.  Likewise, we cannot use DESCRIBE directly on the query, because DESCRIBE can only be used on tables.  Creating a temporary table solves both of those problems.

The question, as I read it is about getting details on the columns in a resultset, as opposed to the columns of database tables.

If you are using PHP, there are built-in functions for accessing databases with MYSQLI and PDO which can give you detailed information (data type and so forth) about the columns in a query result (as opposed to the columns of the original tables).

For details about these methods see the PHP documentation:

PDOStatement::getColumnMeta
mysqli_result::fetch_field_direct


How to add a column from another table? I'm starting to using SQL statements in my Oracle database XE (tables and relations made by me) and I'm having a hard time to understand how can I put a column from another table.

The example in this case is:

I have two tables that are interconnected, ORDERS and PRODUCT.

In ORDERS there are the following attributes:

ORDERID (defined as Primary Key), PRODUCT_NAME (a Foreign Key from the PRODUCT table) and QUANTITY.

In PRODUCT there are:

NAME (defined as Primary Key) and PRICE.

I want to add a column that gets the PRICE from the PRODUCT table and insert it to the ORDERS table according to the PRODUCT_NAME, obviously.

How can I do it?
Are there any workarounds?

Please, keep in mind that I'm a newbie and my knowledge is very limited.

The original question was:

  I want to add a column that gets the PRICE from the PRODUCT table and
  insert it to the ORDERS table according to the PRODUCT_NAME,
  obviously. How can I do it? Are there any workarounds? Please, keep in
  mind that I'm newbie and my knowledge is very limited.

and the ultimate reply (after much communication through comments - see above), my response was

  And you can't use triggers to make updating automatic? I'd say they
  are the way to go!

Which worked.

you add column  to  orders table  using

alter table tablename  add  column  datatype

then

insert  into  orders(price) select  price from  product;


Query Processing & The Buffer Pool When SQL Server Server 2008r2 begins to process a query, I know it needs to parse the SQL Text and create the plan, plus a few other steps.

It is doing all of that in memory OUTSIDE of the buffer pool, correct?

The buffer pool does not come into play until the query is actually being executed to retrieve/manipulate data, correct?

Scenario: (server was not built on configured by me)

SQL Server 2008r2 SP1 - yes it needs to be patched, that is on the radar
96 GB of RAM
MAX Memory set to 92 GB
Buffer Pool sitting just below 80 GB used
PLE over 4 hours
Seeing resource_semaphore WAITS

If my understanding that the memory grant to process the query does not come out of the buffer pool, but the remaining available memory, reducing the MAX Memory should help re-mediate the WAIT type.  My thought is set MAX Memory at 72 GB.  Your thoughts?

Thanks!

When SQL Server 2008r2 begins to process a query, I know it needs to parse the SQL Text and create the plan, plus a few other steps. It is doing all of that in memory OUTSIDE of the buffer pool, correct?

No the memory is from inside the buffer pool. Generally memory required for such process would 'not be much' and this could be easily satisfied by buffer pool. Also note SQL Server might not grant all memory requested by the query in one go. If query requests memory which is of considerable amount SQL Server would grant minimum just to make sure query starts running.

  The buffer pool does not come into play until the query is actually being executed to retrieve/manipulate data, correct?

Buffer pool is always there starting from when plan is created to when query executes. Buffer pool in SQL Server 2008 R2 is sole one allocating memory for any request <= 8KB of memory page.

  If my understanding that the memory grant to process the query does not come out of the buffer pool, but the remaining available memory, reducing the MAX Memory should help re-mediate the WAIT type. My thought is set MAX Memory at 72 GB. Your thoughts?

No, reducing max server memory would be of help here. I would suggest you to look at query and the 'statistics optimizer is looking when it is preparing the query plan'. You have enough amount of RAM. Sometimes when you run a query with outdated stats it requests large amount of memory and ofcourse SQL Server is not going to grant all of it. It would provide minimum amount so that query at least starts running.

When SQL server creates a compiled plan, it calculates two memory grant parameters called "required memory" and "additional memory". The required memory is minimum memory SQL Server can give for sorting and hash operation and additional is what can be required by query to store temporary data which comes with huge sorts or spills. If Query start with minimum memory and requests more but SQL Server is not able to provide it all in on go query waits with wait type 'resource semaphore'. This does not means memory is less it means the statistics was outdated which forced SQL Server to create bad plan thus requesting more memory.

I suggest you read Understanding Query Memory Grants

resource_semaphore is used when SQL need to generate plan for huge query. when SQL understand that the amount of memory needed to compile a query is high sql server prefer to use restricted amount of memory for compilation since the other way is to drop part of the buffer pool which obviously sql server does not want to do.
so, high rate of compilation while compilation need a lot of memory cause the resource_semaphore wait type even when the machine is monster (in terms of hardware)

I hope this answers the question.
(I'm new here, not enough reputation to add comment yet)


Azure SQL Db Linked Server - Secured Connection Methods? The developers want a linked server from our on-prem server to our Azure SQL Db (DBaaS) in order to complete a nightly data pull into our data warehouse.

I'd much rather go the route of SSIS but don't have the time to properly develop the package or teach them how to support it.

My main concern at this point is ensuring the data is encrypted in transit.

Once the linked server is in place along with a corresponding Agent job, my options appear to be:

sqlcmd (with the -N switch, "...used by the client to request an encrypted connection."
Invoke-SQLcmd Azure PowerShell cmdlet (with the -EncryptConnection switch)
OR... Azure SQL Data Sync (Hub to Member), which as of this writing is still in preview but looks promising!

Are there any gotchas or special considerations for these methods that I should be aware of?

The main difference between this answer and the previous one offered is the (what I find to be incredibly important) acknowledgement of MitM attacks rather than a blanket statement which overlooks caveats.
For posterity, from Securing your SQL Database in the documentation:

Important
All connections to Azure SQL Database require encryption (SSL/TLS) at all times while data is "in transit" to and from the database. In your application's connection string, you must specify parameters to encrypt the connection and not to trust the server certificate (this is done for you if you copy your connection string out of the Azure portal), otherwise the connection does not verify the identity of the server and is susceptible to "man-in-the-middle" attacks. For the ADO.NET driver, for instance, these connection string parameters  are Encrypt=True and TrustServerCertificate=False. For information about TLS and connectivity, see TLS considerations.

For other ways to encrypt your data, consider:

Cell-level encryption to encrypt specific columns or even cells of data with different encryption keys.

If you need a Hardware Security Module or central management of your encryption key hierarchy, consider using Azure Key Vault with SQL Server in an Azure VM.

Data is encrypted in transit to Azure by default. It is not possible to send non-encrypted in-transit data to Azure.

Even when you try to connect to Azure SQL Database with tools like SSMS and you did not specify encryption, encryption is still enforced.

When a client first attempts a connection to SQL Server / SQL Azure, it sends an initial connection request. Consider this a "pre-pre-connection" request. At this point the client does not know if SSL/Encryption is required and waits an answer from SQL Server/SQL Azure to determine if SSL is indeed required throughout the session (not just the login sequence, the entire connection session). A bit is set on the response indicating so. Then the client library disconnects and reconnects armed with this information.

By the way I suggest you not to use a linked server for ETL. You will find OLEDB waits so high that it will be no viable option.


Error: 'PRIMARY' filegroup is full We have increased disk space on the SQL Server, and there is definitely enough space on the server, yet below error is raised:

  com.softmar.fileserver.WebFileException: com.microsoft.sqlserver.jdbc.SQLServerException: Could not allocate space for object 'dbo.WebFileImage'.'pkWebFileImage' in database 'sslFileServer' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

The properties of the database shows that there is only 1.1 MB available, yet other databases on the same SQL server shows much more space available on the properties.  So why is only this database not accommodating the disk size increase?  Looking at the file settings it is set to unrestricted growth.

This is rather critical so will appreciate your swift response.

add a file to your database and set it's filegroup to primary.


How to know what are the necessary log files for a backup in DB2? Is there a way to know what are the necessary log files for specific backup?

Un example of backup and restore process:

db2 BACKUP DATABASE TST1 ONLINE TO '/db2/backup'
db2 RESTORE DATABASE TST1 FROM '/db2/backup' TAKEN AT 20150826131633 TO '/db2/data' INTO TST2

db2 rollforward db TST2 to end of logs
db2 rollforward db TST2 complete

Now, in order to be able to rollfoward to end of logs I need to copy the logs from the backuped DB to the target archive dir. What I do is coping the last 2 days and it always passed. Some day it will not.

So how can I know during backup time(right after it) what files exactly in addition I need to backup..?

By default an online backup places the logs necessary to rollforward to the point of the end of the backup inside of the backup file. You can extract the logs from the backup file and then ROLLFORWARD to the end of them. Use the LOGTARGET clause of the RESTORE command to extract the logs to a location (I have tended to restore them to a location other than my logs directory.) Then use the OVERFLOW LOG PATH clause of the ROLLFORWARD command to point the ROLLFORWARD command to the logs you just extracted.

Example:

db2 RESTORE DATABASE TST1 FROM '/db2/backup' TAKEN AT 20150826131633 TO '/db2/data' INTO TST2 LOGTARGET '/tmp/extractedlogs'

db2 ROLLFORWARD db TST2 TO END OF LOGS AND COMPLETE OVERFLOW LOG PATH ('/tmp/extractedlogs')

In history file you find first log needed. Rollforward query status should indicate this.


How should I save apache logs into a mysql table? This is a php script to view apache logs.

I want to save apache logs into a mysql database.

Then add some rules for tagging urls using mysql REGEXP search, like: SET tag='some tag' WHERE url REGEXP 'some pattern';

a) Should I use one table for storing all the urls every time they are accessed even if they repeat and then do the REGEXP search and apply the tag to all of them?

b) Or it would be better to save one table with unique urls, and a second table with the id of the url and the time accessed? Then the tagging will be applied this table that has less rows if the url repeats itself.

If option 'b' is better, what kind of index should I use for unique urls? varchar(4000) primary key? I was thinking about creating a md5 hash of the string of the url and use that as primary key because it will be shorter.

I ask this question because I want to know what would be best performance when:

Tagging many urls with regexp search
Importing thousands of urls into one table and make sure they are unique

Thanks!

Because this is really logging data that you are capturing, I would first store it in its raw form, then ETL/normalize it as needed.

For searching, you can index the front part of the URLs first few characters. Let's say the options are http:// and https:// - then limit the index size to three characters passed the protocol part.

CREATE TABLE log (
datetime_created DATETIME,
url VARCHAR(1024),
domainname VARCHAR(255),
someotherdata VARCHAR(255),
...
INDEX `idx_domain` (domainname(3)))
ENGINE=InnoDB ROW_FORMAT=COMPRESSED;

InnoDB will allow you to search the table w/o locking it.
Compression will help with disk space.
INDEX idx_domain (domainname(3)) will index the first 3 characters of the domain name column and speed up searches. However, the strategy for REGEXP will be to try to match on the front of the domain name.

Scale will eventually be an issue if the site becomes popular, so buyer beware.

grep (or egrep) the logs.  Don't use MySQL.


SQLPlus configuration problem or firewall issue I need to connect to a remote Oracle DB, so I requested to open the firewall on the port 1521 to the DB and the replica.
[root@ansible-test ~]$ telnet <oracle_db_active> 1521
Trying <oracle_db_active>...
Connected to <oracle_db_active>.
Escape character is '^]'.

^CConnection closed by foreign host.

[root@ansible-test ~]$ telnet <oracle_db_replica> 1521
Trying <oracle_db_replica>...
Connected to <oracle_db_replica>.
Escape character is '^]'.

^CConnection closed by foreign host.

I'm able to telnet to the DB on port 1521.
But when I try to connect using SQLPlus I get an error as the host doesn't exist.
[root@ansible-test ~]$ echo exit |sqlplus "<user>/<pass> @(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = <oracle_db_active>)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = <oracle_db_replica>)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = <service>)(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 12)(DELAY = 5))))"

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Sep 7 17:37:23 2020
Version 19.8.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

ERROR:
ORA-12545: Connect failed because target host or object does not exist

Enter user-name: Enter password:
ERROR:
ORA-12162: TNS:net service name is incorrectly specified

I have another server in the same network as the DB and I'm able to connect with the same string.
I don't have an sql.ora or tnsname.ora configure on any of the servers.
The only difference between the two servers is that one is RedHat and the other centos.
Do I need to open any other port to the DB o it is a configuration issue?
Thanks
(sorry for all the redacting)

I end up finding the answer :)
On my test server on the same network as the DB I made a tcpdump and I notice that my connection started on the primary IP and the DB reply through the secondary IP. There was a request on the DNS server in the middle.
I made the same test on my ansible-test server and found out that there was problem on then DNS request and fail the connection. Basically, it was not able to understand the secondary IP name.
So this end up being a weird networking problem.
Thanks All.

I once had a similar scenario like the one you are having. I deployed Oracle Connection Manager on RedHat 6. Everything configured through ansible. Everything was working on my Vagrant Oracle Linux during development, but once deployed to RedHat I had these ORA- messages. Everything was correct, but I never found the reason why I couldn't connect to the database. I replaced the node with Oracle Linux and it worked. I possibly could have replaced the node with another RedHat node. Like I said, same configuration rolled out with ansible. Sometimes you cannot waste more time on a problem. Recreate node and run your ansible playbook.


What perfmon counters should I use to get the fastest restore possible on an idle system? I have a development VM running Windows 2012-R2 and Sql Server 2014. I have one disk from a SAN holding the OS and SQL. My backup file is stored locally on the disk.

I want to restore database as quickly as possible as part of a "reset my dev environment" process. The system is mostly idle when I do this. The system runs SQL Server and Tomcat 7. I usually have an RDP session which has one or more of the following open Perfmon, SSMS, Visual Studio 2013, Visual Studio 2010. Process Monitor. I'm willing to tune this restore to the point where this RDP session becomes completely unresponsive during the restore process. No one but me is using this server.

Using Nic Cain's post I was able to get my restore process from 6xx seconds to ~100-120 seconds with the following parameters:

RESTORE DATABASE [XXXX]
    FROM DISK = N'$(BackupFile)'
        WITH
            REPLACE,
            MOVE N'RMS_SIMULATION' TO N'$(MdbPath)\XXXX.mdf',
            MOVE N'RMS_SIMULATION_log' TO N'$(LDBPATH)\XXXX.LDF',
            STATS = 5,
            MAXTRANSFERSIZE = 4194304,
            BUFFERCOUNT = 72

By adding MAXTRANSFERSIZE and BUFFERCOUNT I was able to peg the I/O Data bytes/sec and I/O Data operations/sec counters to the top of the perfmon chart. Before that, bytes/sec would spike to the top, but spend more time at the bottom of the scale, and operations/sec was spikey in the middle of the scale.

Now I know I can tweak with the numbers and run the restores 100 times at different numbers and measure the restore time. While, there is a certain scientific vigor in that approach, I'd like something more data-centric.

I know I don't want SQL Server page faulting during the restore, because hitting virtual memory is a big slow down. However, what other perfmon counters should I be looking at?

If you are running Developer or Enterprise edition, consider restoring from a database snapshot rather than a full backup (https://msdn.microsoft.com/en-s/library/ms189281.aspx).  This can also be less resource-intensive than a full restore.

The time needed to revert from a snapshot is largely proportional to the amount of changes made since the snapshot was taken so it can be significantly faster than a normal restore in many cases.  Like a regular full restore, the log is initialized so keeping the log size small will reduce the restore time.

Instant file initialization doesn't apply to transaction log files.  So, you may also help yourself by precreating and/or adjusting the destination database to at least the sizes from the database you are restoring from.

Thus, SQL Server won't have to zero out any files during the restore.


WAMP server performance tuning for MySQL We have a Drupal site with nearly 2000 pages running on WAMP server. The pages also contain images, and flash videos embedded into them. So, they are taking some time to load under normal settings.

Nearly 200-300 users are expected to use the system concurrently (the number is expected to grow later).

The dev server (on our company intranet) has 16 GB RAM, and runs Windows7 - 64 bit OS. I can see that in wamp/bin/mysql/mysql5.5.16/ has files

my.ini
my-huge.ini
my-innodb-heavy-4G.ini

I had a look into both of them, and I am not sure what is the purpose of each one of them and which one is used by my the WAMP server currently. Also, I needed to know the difference in using my-huge.ini and my-innodb-heavy-4G.ini. I have also configured Apache SOLR for indexing the search (I don't know if this is relevant).

P.S : Drupal stores pages (along with the link to images and videos) into the database and images and other media are stored in folder under the site root.

The differences have to do with the expected capacity mysqld is requested to handle

my_huge.ini

does not have max_connections set
has innodb settings commented out
values preset

sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M

my-innodb-heavy-4G.ini

has max_connections set at 100
has innodb settings enabled
values preset

max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 64M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M

Default values are used if not specified. Whichever one you want to use, you must copy it over my.ini and restart mysqld to take effect.

If using my-huge.ini, you must shutdown mysqld, copy my-innodb-heavy-4G.ini to my.ini then startup mysqld.
If using my-innodb-heavy-4G.ini, you must shutdown mysqld, delete the innodb log files (ib_logfile0,ib_logfile1), copy my-innodb-heavy-4G.ini to my.ini then startup mysqld.

Since you said the number of DB Connections is expected to grow, you must raise max_connections to higher values but you must balance the usage of RAM. The best way to balance it would to run some diagnostic program like mysqltuner.pl and get recommendations adjusting settings per connection and per instance.

Since you are running MySQL in a WAMP environment, I would not surpass 25% of RAM for total memory because you must give Windows, Apache, and PHP (or Perl or Python) their due in terms of RAM. (I am glad you are using Apache and not IIS, otherwise this would be a WIMP environment !!!)

My WAMP MySQL was performing tasks roughly 10 times longer than my server / local MySQL databse.
I was unable to find a way to improve the performance by changing the WAMP MySQL settings.
What did solve the problem for me was to switch off the WAMP MySQL database and turn on my local MySQL server and use that instead.


Export SQL Agent jobs with steps and scripts I recently saw some kind of export of SQL jobs configured under SQL Server Agent that I would like to achieve as well.
Unfortunately I don't have any trace to author of that report to ask him\her about report creation, hence looking for help here as I had no luck googling it nor searching in here.

Report was in excel (so I guess originally was some csv or maybe directly xls) and contained every single job from SQL instance with one step per row and most importantly containing also script content of each job step and some further details.

As for an example as SQL has default job syspolicy_purge_history only, export will contain 3 rows for each of it's step.

I can't seem to find it as any build in export, so I guess some query maybe or I have missed this somewhere

Anyone aware of any such report?  Any help is welcome

Would something like this work?

USE [msdb]
GO
SELECT
    j.Name
    , j.[Description]
    , js.Step_ID
    , js.Step_Name
    , js.Database_Name
    , js.Command
    --, j.*, js.*
FROM dbo.sysjobs j
INNER JOIN dbo.sysjobsteps js
    ON js.job_id = j.job_id
--WHERE j.enabled = 1
ORDER BY j.name, js.Step_ID

USE [msdb]
GO
SELECT
    j.Name as Job_name
    ,case j.enabled
        when 1 then 'yes'
        when 0 then 'no'
    end as [isJobenabled]
    , j.category_id  as category_Of_Job
    , j.[Description] as Job_Description
    , js.Step_ID as Job_Step_Id
    , js.Step_Name as Job_Step_name
    , js.subsystem as Step_Type
    , js.Command as Excutable_Command
    , js.Database_Name
    , js.server
    , js.output_file_name
    , case sySch.enabled
        when 1 then 'yes'
        when 0 then 'no'
    end as [isJobschedule_enabled]
    , CASE
        WHEN [freq_type] = 64 THEN 'Start automatically when SQL Server Agent starts'
        WHEN [freq_type] = 128 THEN 'Start whenever the CPUs become idle'
        WHEN [freq_type] IN (4,8,16,32) THEN 'Recurring'
        WHEN [freq_type] = 1 THEN 'One Time'
      END [ScheduleType]
    , CASE [freq_type]
        WHEN 1 THEN 'One Time'
        WHEN 4 THEN 'Daily'
        WHEN 8 THEN 'Weekly'
        WHEN 16 THEN 'Monthly'
        WHEN 32 THEN 'Monthly - Relative to Frequency Interval'
        WHEN 64 THEN 'Start automatically when SQL Server Agent starts'
        WHEN 128 THEN 'Start whenever the CPUs become idle'
      END [Occurrence]
    , CASE [freq_type]
        WHEN 4 THEN 'Occurs every ' + CAST([freq_interval] AS VARCHAR(3)) + ' day(s)'
        WHEN 8 THEN 'Occurs every ' + CAST([freq_recurrence_factor] AS VARCHAR(3))
                    + ' week(s) on '
                    + CASE WHEN [freq_interval] & 1 = 1 THEN 'Sunday' ELSE '' END
                    + CASE WHEN [freq_interval] & 2 = 2 THEN ', Monday' ELSE '' END
                    + CASE WHEN [freq_interval] & 4 = 4 THEN ', Tuesday' ELSE '' END
                    + CASE WHEN [freq_interval] & 8 = 8 THEN ', Wednesday' ELSE '' END
                    + CASE WHEN [freq_interval] & 16 = 16 THEN ', Thursday' ELSE '' END
                    + CASE WHEN [freq_interval] & 32 = 32 THEN ', Friday' ELSE '' END
                    + CASE WHEN [freq_interval] & 64 = 64 THEN ', Saturday' ELSE '' END
        WHEN 16 THEN 'Occurs on Day ' + CAST([freq_interval] AS VARCHAR(3))
                     + ' of every '
                     + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)'
        WHEN 32 THEN 'Occurs on '
                     + CASE [freq_relative_interval]
                        WHEN 1 THEN 'First'
                        WHEN 2 THEN 'Second'
                        WHEN 4 THEN 'Third'
                        WHEN 8 THEN 'Fourth'
                        WHEN 16 THEN 'Last'
                       END
                     + ' '
                     + CASE [freq_interval]
                        WHEN 1 THEN 'Sunday'
                        WHEN 2 THEN 'Monday'
                        WHEN 3 THEN 'Tuesday'
                        WHEN 4 THEN 'Wednesday'
                        WHEN 5 THEN 'Thursday'
                        WHEN 6 THEN 'Friday'
                        WHEN 7 THEN 'Saturday'
                        WHEN 8 THEN 'Day'
                        WHEN 9 THEN 'Weekday'
                        WHEN 10 THEN 'Weekend day'
                       END
                     + ' of every ' + CAST([freq_recurrence_factor] AS VARCHAR(3))
                     + ' month(s)'
      END AS [Recurrence]
    , CASE [freq_subday_type]
        WHEN 1 THEN 'Occurs once at '
                    + STUFF(
                 STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
        WHEN 2 THEN 'Occurs every '
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Second(s) between '
                    + STUFF(
                   STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
                    + ' & '
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
        WHEN 4 THEN 'Occurs every '
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Minute(s) between '
                    + STUFF(
                   STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
                    + ' & '
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
        WHEN 8 THEN 'Occurs every '
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Hour(s) between '
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
                    + ' & '
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
      END [Frequency]
    --, j.*, js.*
FROM dbo.sysjobs j
INNER JOIN dbo.sysjobsteps js
    ON js.job_id = j.job_id
--WHERE j.enabled = 1
left join sysjobschedules syJsch
   on syJsch.job_id=js.job_id
left join sysschedules sySch
   on sySch.schedule_id=syJsch.schedule_id
ORDER BY j.name, js.Step_ID


MySQL Replication Error I am getting an Error on Slave as:

  could not execute Write_rows event on table mydatabasename.atable; Duplicate entry '174465' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000004, end_log_pos 60121977

As I am directly getting updates from master so why this happens.

How can we handle this in an effective manner so that there is no data loss on the slave.I do not want to set up the whole replication again for maintaining the data integrity.

Thanks..!

Whenever there is a 1062 error, the usual table with the problem is the actual table being updated in the query. The query should appear in the output of SHOW SLAVE STATUS\G

For example, in your error it says Duplicate entry '174465' for key 'PRIMARY'. This indicates that you should look up the value 174465 in the table you are either doing an INSERT or UPDATE. If the row does exist, can you have to decide if the query halted execution will change the row's contents. If the query will simply reproduce the exact same contents, and you believe that will be the case, you can perform one of two options:

OPTION 1

Skip the error, wait 5 seconds, and view the Slave Status. Here the 5 steps for Skipping an Error

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;
SELECT SLEEP(5);
SHOW SLAVE STATUS\G

When you view the Slave Status, here is what to expect

If Seconds_Behind_Master is NULL

Replication is Broken : Look for Tell-Tale Signs
If Error Number is 1062 again, Repeat the 5 steps for Skipping an Error

If Seconds_Behind_Master is a Number

Replication is running
When Seconds_Behind_Master > 0, Replication is Catching Up.
When Seconds_Behind_Master = 0, Replication is Fully Caught Up.

OPTION 2

Remove the row to allow replication to continue

Delete the row from the table on the Slave and do the following 4 Steps for Skipping an Error:

STOP SLAVE;
START SLAVE;
SELECT SLEEP(5);
SHOW SLAVE STATUS\G

At the risk of sounding redundant...

When you view the Slave Status, here is what to expect

If Seconds_Behind_Master is NULL

Replication is Broken : Look for Tell-Tale Signs
If Error Number is 1062 again, delete the row Repeat the 4 steps for Skipping an Error

If Seconds_Behind_Master is a Number

Replication is running
When Seconds_Behind_Master > 0, Replication is Catching Up.
When Seconds_Behind_Master = 0, Replication is Fully Caught Up.

What if there are just too many duplicate key issues? Here are some of my earlier posts concerning how to use MAATKIT's mk-table-checksum, mk-table-sync, pt-table-checksum, pt-table-sync:

In a MySQL Master/Slave replication, what would happen if I write to the Slave?
Fixing tables out of sync as reported by pt-table-checksum, pt-table-sync is not working
MySQL replication problem: slave has become out of sync

The following script will keep skipping the global counter till replication error gets fixed. Replication error happens generally when the time of dump from master differs from the position of master taken differs.

Try this script:

#!/bin/bash
logfile=~/slave-watcher.log
while true; do
    status=$(mysql --execute="show slave status\G"|grep "Seconds_Behind_Master:"|awk '{print $2}')
    if [ $status == "NULL" ]; then
        mysql --execute="show slave status\G" | grep "Last_SQL_Error:" | tee -a $logfile
        mysql --execute="set global sql_slave_skip_counter=1; start slave;"
    fi
    sleep 1
done

Note: To add password, modify script like this:

  mysql -p your_password --execute


So if I understand correctly, you want the latest status and be able to filter on it. I don't know what version of SQL Server you're using but APPLY has been out since 2005 so try this out:

SELECT  DISTINCT TagNo,
        TruckNo,
        CA.VerifiedDateTime,
        CA.Status
FROM yourTable A
CROSS APPLY
(
    SELECT TOP 1 VerifiedDateTime,Status
    FROM yourTable
    WHERE TagNo = A.TagNo
    ORDER BY VerifiedDateTime DESC
) CA
WHERE CA.Status = 'A';

Using inner query first find all TruckNo where exist only one record against their status for them and then filter them using the required status value.

Try this:

SELECT TagNo, TruckNo, Status, VerifiedDateTime
FROM TABLENAME
WHERE TruckNo IN (
    SELECT TruckNo FROM TABLENAME
    GROUP BY TruckNo
    HAVING COUNT(DISTINCT Status) = 1
)
AND Status = 'A'


Contiunous rise of mysqld memory consumption Mysqld memory consumption rises forever and never seems to be freed. It starts out about 6GB but gradually rises to around 10GB in a few weeks, and of the 10GB only 4GB and 50MB is being used by the buffer pool and the dictionary respectively when checking innodb status.

This is with MySQL 5.6.16 on a server with 12GB memory. A few of the tables are partioned and there are roughly 8000 idb files. Also one table is created each day.

I have tried to FLUSH TABLES with no success. The tables are closed but the memory does not get freed at all. In fact, more memory gets consumed.

Why is the memory being consumed? And are there any known issues with memory not being freed when using partioned tables?

my.cnf

query_cache_size = 512M
query_cache_limit = 16M
max_allowed_packet = 16M
table_open_cache = 1024
sort_buffer_size = 2M
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 2MB
myisam_sort_buffer_size = 1M
max_connections = 1024
thread_cache = 1024
tmp_table_size = 16M
max_heap_table_size = 16M
wait_timeout = 20
join_buffer_size = 256KB
thread_cache_size = 50
table_definition_cache = 400
key_buffer_size = 256M

We tracked it down to glibc. With glibc-2.12-1.149.el6_6.4.x86_64, memory is not fully freed when closing tables.

It seems that under high concurrent connections, accessing partitioned tables increases Open_tables which increases memory usage, however memory is not fully freed when the table is closed using glibc.

Newer glibcs faced the same problem as well.
Switching to jemalloc-3.6.0-1.el6.x86_64 has resolved our problem.

Test environment
CentOS release 6.6 (Final)
kernel 2.6.32-504.el6.x86_64
mysql-5.6.16

physical ram 2G
bufferpool 256M
other default

memory usage with gblic-2.12-1.166.el6_7.7.x86_64
1st round
mysqlslap concurrent connection: 70, accessed partions: 800 -> 1.3G
"flush table" -> 1.0G

2nd round
mysqlslap concurrent connection: 70, accessed partions: 800 -> 1.6G
"flush table" -> 1.2G

3rd round
mysqlslap concurrent connection: 70, accessed partions: 800 -> 1.6G
"flush table" -> 1.4G

memory usage with jemalloc-3.6.0-1.el6.x86_6
1st round
mysqlslap concurrent connection: 70, accessed partions: 800 -> 1.3G
"flush table" -> 582M

2nd round
mysqlslap concurrent connection: 70, accessed partions: 800 -> 1.3G
"flush table" -> 583M

3rd round
mysqlslap concurrent connection: 70, accessed partions: 800 -> 1.2G
"flush table" -> 584M

8000 idb files ... Also one table is created each day.

Not a good design.  Please provide details so we can discuss viable alternatives.

How many of those 8000 are you using on a daily basis?

  query_cache_size = 512M

Over about 50M, the QC causes spikes of poor performance.

How much RAM do you have?

Are you using InnoDB?  Or MyISAM?  Or Both?

What is the STATUS value of Max_used_connections?

For only InnoDB on 12GB machine, key_buffer_size=20M and innodb_buffer_pool_size=8G.

You mentioned "partitioning"; how many partitions in any table?  About 50 is a practical upper limit; much more than that and performance suffers noticeably.


Errors in the OLAP storage engine: A duplicate attribute key has been found when processing When I try to process my cube and specifically the Employee_DIM, I get the following error :

  Errors in the OLAP storage engine: A duplicate attribute key has been
  found when processing: Table: 'dbo_Employee_DIM', Column: 'Firstname',
  Value: 'Aurélie'. The attribute is 'Firstname'.

I think that duplicate values for the column Firstname are considered as keys. After checking with the Employee_DIM :

 SELECT [Firstname], COUNT([Firstname]) AS dup_count
     FROM [Database].[dbo].[Employee]
        GROUP BY [Firstname]
             HAVING (COUNT([Firstname]) > 1)
                ORDER BY [Firstname]

Is there any alternative to leave only the business key in Employee_DIM and get Lastname and Firstname without adding them in the dimension for a future use?

You can find the solution here : Errors in the OLAP storage engine: A duplicate attribute key has been found when processing


Transactional Replication snapshot locking Publisher db from beginning I have recently upgraded my remote distributor to 2008r2. I am getting complete system locks when attempting to generate a new snapshot in the day when we have added an article to the publication (we could do this in the past with very little issues).

I have checked sp_Helppublication and we are using synchronization method 3 (concurrent), so no locks should be held until the very end.

I have been trying to following this link as I am only adding an additional table with 26 rows but don't want to reinitialize and have the whole snapshot delivered.

http://deepakrangarajan.blogspot.co.uk/2009/01/sql-2005-transaction-replication-adding.html

I attempted to run this code

EXEC sp_changepublication
@publication = 'statics',
@property = N'immediate_sync',
@value = 'false'
GO

but this is also locking up the system completely. I guess I will have to run in the middle of the night but thought that you should be able to run this as just a config change to the publication.

Also when I try the last step in the link to add it to the subscription

EXEC sp_addsubscription
@publication = 'statics',
@article = 'dbo.SupplierCorporatePayAccountLink_tbl',
@subscriber = 'PEARLJAM',
@destination_db = 'MLT-Replicated',
@reserved='Internal'

I get the following error message

  Msg 14058, Level 16, State 1, Procedure sp_MSrepl_addsubscription_article, Line 331
  Cannot create the subscription because the subscription already exists in the subscription database. Only one subscription to the same publication is allowed in each subscription database. Drop the subscription and add it again if necessary. If the problem persists, replication metadata might be incorrect; see Books Online for troubleshooting information.

After the snapshot, how can I just have the new article added to the subscriber?

To get this to work I scheduled switching off the 'immediate sync' to very low traffic period

EXEC sp_changepublication
@publication = 'statics',
@property = 'Immediate_sync',
@value = 'false'
GO

I then ran a snapshot which only took a fraction of the time at 6 mins.

I then had to drop the current subscription for that article only

    EXEC sp_dropsubscription
    @publication = 'statics',
    @article = 'dbo.SupplierCorporatePayAccountLink_tbl',
    @subscriber = 'SURVIVOR'

and rerun the snapshot. Then i could add the subscription back into the publication

    EXEC sp_addsubscription
    @publication = 'statics',
    @article = 'dbo.SupplierCorporatePayAccountLink_tbl',
    @subscriber = 'SURVIVOR',
    @destination_db = 'MLT-Replicated',
    @reserved='Internal'

but it would not allow this without the fairly obscure setting

@reserved ='Internal'

or I got an error message saying I needed to subscribe to all articles.... replication then automatically sync'd the new table. I'm a happy man as this has been causing me some long nights!

You need to remove the existing publication. Use the below code to guide you, how you can remove existing publications. I hope it works.

-- This batch is executed at the Publisher to remove
-- a pull or push subscription to a transactional publication.
DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
SET @publication = N'AdvWorksProductTran';
SET @subscriber = $(SubServer);

USE [AdventureWorks2012]
EXEC sp_dropsubscription
 @publication = @publication,
 @article = N'all',
 @subscriber = @subscriber;