Split up interval into year slices Being a novice Postgres user, I have a table ad in my PostgreSQL 9.5 (x64) database with 87 rows. In addition to other columns, it has two columns 'start' and 'end' having date-time duration range like this:

ID      Start                   End
1       2003-06-07 00:00:00     2004-09-30 23:59:59

I need to split the range into one year windows in separate rows (from start year to the last year of interval) stored in database like this:

ID       Start
1_2003   2003-06-07 00:00:00     2003-12-31 23:59:59
1_2004   2004-01-01 00:00:00     2004-09-30 23:59:59

Using the operator ||'_'|| and Extract() function, I am able to concatenate ID with year. Also, this question addresses how to split up in interval in weeks and this one shows how to do the same for days but none of them addresses how to split up an interval in years specifically.

I avoided this question because I don't want to go for a stored procedure based approach. I am aware that generate_series() returns a series from start and stop parameters but actually I am struggling to break the interval at last day of the year and then re-starting from first day of the year in next row. I would highly appreciate if someone could guide me to this?

A couple of suggestions:

Use legal, lower-case, unquoted identifiers to save yourself a lot of confusion. end (and, to a lesser degree start) are reserved words.

Your id column seems to be numeric type like integer, not text.

Since you don't need a time component in start_day and end_day, the appropriate data type is date, not timestamp.

Why concatenate id and the year for a new "ID"? Add a second column year if you need it for a PK. Or don't add an additional column at all. It can cheaply be extracted from the new start_day on the fly. Generally, don't store data redundantly if you can avoid it.

Typically, timestamp ranges use an inclusive lower bound and an exclusive upper bound. Since timestamps can have fractional digits (up to 6 in Postgres) that is much cleaner. Your input 2003-12-31 23:59:59 would fail for 2003-12-31 23:59:59.123.

So, your table could look like this:
CREATE TABLE ad (
  id        int PRIMARY KEY
, start_day date NOT NULL    -- *inclusive* lower bound
, end_day   date NOT NULL    -- *exclusive* upper bound
CHECK (end_day > start_day)  -- enforce legal input
);

Proper test values:
INSERT INTO ad(id, start_day, end_day)
VALUES
  (1, '2003-06-07', '2004-10-01')   -- span 2 years (your example)
, (2, '2003-06-07', '2003-06-08')   -- 1 day in same year
, (3, '2003-06-07', '2003-10-01')   -- span 1 year
, (4, '2003-06-07', '2006-10-01');  -- span many years

Solution
Use generate_series() in a LATERAL join, based on start and end day, truncated to the year with date_trunc(). This produces one row per year with the new start date. Add a year and you have the new end date. Except for first and last row per id, where you substitute the proper start / end with GREATEST and LEAST respectively. Voilá.
-- CREATE TABLE ad_year AS
SELECT ad.id
     , extract('year' FROM y)::int AS year
     , GREATEST(y                    , ad.start_day) AS start_day
     , LEAST   (y + interval '1 year', ad.end_day)   AS end_day
FROM   ad
     , generate_series(date_trunc('year', start_day::timestamp)  -- cast to ts here!
                     , date_trunc('year', end_day::timestamp)
                     , interval '1 year') y;

Note that date_trunc() returns timestamptz for timestamptz input and timestamp for timestamp input. For date input it defaults to timestamptz. Since you seem to be ignoring time zones, cast the date to timestamp explicitly (start_day::timestamp).
Result:
 id | year |      start_day      |       end_day
----+------+---------------------+---------------------
  1 | 2003 | 2003-06-07 00:00:00 | 2004-01-01 00:00:00
  1 | 2004 | 2004-01-01 00:00:00 | 2004-10-01 00:00:00
  2 | 2003 | 2003-06-07 00:00:00 | 2003-06-08 00:00:00
  3 | 2003 | 2003-06-07 00:00:00 | 2003-10-01 00:00:00
  4 | 2003 | 2003-06-07 00:00:00 | 2004-01-01 00:00:00
  4 | 2004 | 2004-01-01 00:00:00 | 2005-01-01 00:00:00
  4 | 2005 | 2005-01-01 00:00:00 | 2006-01-01 00:00:00
  4 | 2006 | 2006-01-01 00:00:00 | 2006-10-01 00:00:00

If you create a new table from the result, I suggest (id, year) as primary key.
Aside: This is not an operator: ||'_'|| (nor a cute little face). It's  2 concatenation operators || and a string literal '_'.


MySQL case sensitive table names on Linux Moving a MySQL database from Windows to Linux I have the problem that on Linux the names of the tables are case sensitive. This is a problem, because the Java application that I am developing can't find tables.

I have changed my /etc/mysql/my.cnf file adding the row:

lower_case_table_names=1

But that did not change anything.

My server version is:
5.1.61-0ubuntu0.11.10.1 (Ubuntu)

How can I configure MySQL to ignore case in table names?

Just altering the lower_case_table_names setting isn't enough.  It needs to be done before you import your database(s).

The MySQL 5.1 documentation lists a procedure for moving between Windows and Linux/UNIX.  This will ensure that your desired rules for enforcing case sensitivity are followed.  Take a look and verify that you did these steps in the correct order:

  To convert one or more entire databases, dump them before setting
  lower_case_table_names, then drop the databases, and reload them after
  setting lower_case_table_names:

  1 - Use mysqldump to dump each database:

  mysqldump --databases db1 > db1.sql

  mysqldump --databases db2 >
  db2.sql

  ... Do this for each database that must be recreated.

  2 - Use DROP DATABASE to drop each database.

  3 - Stop the server, set lower_case_table_names in the [mysqld] section of your \etc\mysql\my.cnf file, and restart the server.

  4 - Reload the dump file for each database. Because lower_case_table_names
  is set, each database and table name will be converted to lowercase as
  it is recreated:

  mysql < db1.sql

  mysql < db2.sql


Which query is optimized I've written these two queries for same operation, on the basis of time complexity, i want to know which one is efficient.

select Fname, Lname, Address
from (select * from department d, employee e where d.Dnumber = e.Dno) as a
where a.Dname = 'Research';

Edit: on the basis of my first comment i assume that the query in from clause will work as temporary table/view which i name as "a", and contain all the columns of both the tables and i use it.(and i don't know it's a efficient way to do it.)

or

select Fname, Lname, Address from employee
where Dno = (select Dnumber from department where Dname = 'Research');

Or there could be more efficient way to do it.
Thanks.

Edit2:

SELECT Pnumber, Dnum, Lname, Address, Bdate
FROM employee
JOIN department d ON d.Mgr_ssn = employee.Ssn
JOIN project p ON p.Dnum = d.Dnumber
WHERE p.Plocation = 'Stafford';

i've this argument, can you tell me what's the flaw in this.,

let's there be 1000 rows in each PROJECT, DEPARTMENT, EMPLOYEE table,

then in the above query, compiler will choose in what order the join has to be applied (ABC, ACB,BCA, BAC,...) choosing the efficient one. But to select the best of all for the first, then second,... it has to perform all and compare it with each other, plus after that the order of the tuples involved would be same(not sure, maybe if it goes for EMPLOYEE, DEPARTMENT first it only have 100-150 rows, that entirely depends upon tables...right???). I never worked on live project situation, maybe there are advantages of JOINS(which there are, that why it's in advance release) which i'm not able to see. But i'm not convinced.

then in the below query let 250 comes out to be success, which then CARTESIAN PRODUCT with DEPARTMENT table and produce 2,50,000 tuples from which let's say 5,000 be RESULT, which then again CARTESIAN PRODUCT with 1000 tuples to produce 500,000.

SELECT Pnumber, Dnum, Lname, Address, Bdate
FROM (SELECT Pnumber, Dnum, Mgr_ssn
      FROM department d, (SELECT Pnumber, Dnum
                          FROM project where Plocation = 'Stafford') p
      WHERE d.Dnumber = p.Dnum) q, employee e
WHERE q.Mgr_ssn = e.Ssn;

So, my question is JOIN is to write query simply, and let compiler decides what's the efficient order. Whereas in the below one we've done the almost work for compiler.

and, i've one more question, WHERE clause applies only on one relation?, or let me rephrase, first FROM(along with all the JOINS) clause execute then where clause execute coz in that case the query with JOINS will be very expensive.

Thanks.

You could also write

SELECT Fname, Lname, Address
FROM   department
JOIN   employee
       ON employee.dno = department.dnumber
WHERE  department.Dname = 'Research';

which is equivalent to query two and can be a clearer form (see https://stackoverflow.com/questions/1599050/ansi-vs-non-ansi-sql-join-syntax for an answer that describes why the newer syntax is preferred). Most query optimisers will see this equivalence and perform the same operations for such a simple query, though for more complex queries this may not be the case (if there is a difference the JOIN variant is likely to be the better of the two).

Not that this is almost equivalent to the inner part of your first query, join syntax you have used is the older but equivalent one. As you are doing that in the inner query then just adding an extra filter in the outer one, a good query planner will see these queries as identical and run them exactly the same way. A bad query planner will have the engine run the inner query first then apply the extra filtering clause making it much less efficient depending on what indexes are present for it to use.

But this all depends on a few things that you should edit your question and tags to provide:

What database you are using as different query planners may not optimise the same way for a given query (add the database and version to the tags).
Also most database engines provide a way to read what the query planner is likely to do for a given query which case be useful in judging which option is most optimal - I'll not list how to do that in every database I know though, be specific about the database(s) you are working with and we can be more specific & relevant about the answers we give.
The indexes present on the relevant tables (add the index and key definitions to the question text)

Note that if you run the second query and there are two rows in department where name='Research' then you are likely to get an error as the = operator can only work on one value each side. To list people in all departments with that name use IN instead. This may change the answer to which is most efficient.


Unexpected failover of RDS-MySQL server We have a 16GB RDS-MySQL Multi-AZ instance. Of late, it has unexpectedly failed over multiple times causing the dependent application to be offline for over 15 minutes.

AWS-Support blamed it on high Swap Usage. However, the instance has failed when the Swap Usage was as low as 10 MB. We have started optimizing the queries.

Is there anything else that we can do to avoid such mishap?

===================================================================

EDIT :

We have made one observation. The CPU utilization of the instance remains fairly low. So, we presumed that the issue might be with disk access. The write-throughput is several times that of read-throughput. To add to it, Sort_merge_passes has a value of 79425(which is crazy, right ?). On going through the slow query log, it was found that there are multiple queries with ORDER BY clause not using indexes. It will take time before workarounds are found for all these queries. Can you suggest some immediate fix to overcome the    failover issue ? Should we increase the global value of sort_buffer_size ? Current value is 2MB.

===========================================================

EDIT 2 :

Below is a screenshot of the rate of change of Sort_Merge_Passes(Queries per second) as seen on RDS-Performance Insights.

As can be seen, during periods of load,the value varies from 0.5 to 1(we have observed values higher than 1 as well). A query can cause multiple Sort_merge_passes, 8 being the highest that we have observed.

=======================================================================

EDIT 3 :

We also found that the global status Created_tmp_disk_tables is increasing at the rate of approx 2-3/second. Badly structured queries I guess.

Swapping is a bad symptom of over-use of RAM.
An excessive number of simultaneous connections may lead to swapping.
Very complex queries may lead to swapping (due to temp tables).  Let's see such queries if you have any.
Have you changed any things in my.cnf (or the equivalent in RDS)?  If so let's see if you raised some values too much.
As a quick fix (and if RDS lets you), decrase innodb_buffer_pool_size by a gigabyte.
Sort_merge_passes -  divide by Uptime.  1/second would be "high", but not high enough to be concerned with.
For deeper analysis, see http://mysql.rjweb.org/doc.php/mysql_analysis -- the GLOBAL STATUS and VARIABLES may find some clues; pt-query-digest will focus on the 'worst' queries.
sort_buffer_size = 2M is fine.
"ORDER BY clause not using indexes" - show us the queries, plus SHOW CREATE TABLE.


SQL profiler running on prod we have got performance experts analyzing our SQL server by running profiler traces in production. The profiler trace creates 20 GB of trace files everyday since 2 weeks. Is this a correct approach? Please suggest .

You've got a few different questions in here.

Q: Is it really a good practice to run Profiler on a production system?

Generally, you want to avoid doing it, but there can be situations where it's the most efficient way to solve a problem. For example, if you've got a SQL Server 2005 box, and every query comes in with OPTION RECOMPILE so it doesn't stick around in the plan cache, and you need to troubleshoot a particular sequence of queries, then Profiler can be your best bet.

You also mentioned dynamic queries - depending on how the dynamic queries are built, Profiler might indeed be the only way to capture the problem.

Some monitoring programs are even configured to automatically run a trace whenever the server comes under heavy load so that they can help diagnose the problem.

Q: If the server dies under load, how should we troubleshoot it?

You'll probably need to be more specific about what "dies" means. If it stops accepting any query requests, I'd start by checking the error logs, not by running Profiler.

Q: After running traces for 2 weeks, we haven't solved the problem. Are we using a good approach?

If you haven't solved it after two weeks, that would be no.


Adding custom language to SQL Server I would like to use SQL Server @@langid variable to return different data based on current connection language (set in connection string or with SET LANGUAGE). This works perfectly well for built-in languages, but there are just 34 of them and I'd like to make sure this technique is extensible.

As far as I understand, languages are stored in syslanguages table. Is there a way to add custom language to running SQL Server instance?

No, you can't add custom languages to the SQL Server system.

An end user won't interact with the system as such: you'd implement multi-language within your database design/application. The system language is irrelevant.


What exactly is SOLR? It seems like a magical technology that can

Do spatial search
do full text search

The problem is,

It doesn't say where does it store all the data. Does it have it's own proprietary database

If it store it's data in mysql, for example, I know that mysql cannot do spatial search well how can something build on top of it can?

Then someone said they use solr with mongodb. So solr can read mongodb and mysql? Who writes the glue? We?

Ultimately, what is this?

Database?
Index database?
API interface?

What is SOLR?

Solr uses lucene to do the spatial and full text indexing, not mysql. It basically wraps a REST-style service on top of lucene's index library so you can access it as a service. The wikipedia article is pretty good for an overview: http://en.wikipedia.org/wiki/Apache_Solr

An alternative to Solr that also uses lucene is Elastic Search, which I think is superior in at least a few ways--see this answer from the creator of Elastic Search: https://stackoverflow.com/questions/2271600/elasticsearch-sphinx-lucene-solr-xapian-which-fits-for-which-usage/2288211#2288211


ODBC API SQLFetch() Asynchronously I want to populate a grid as data is made available.

If you run a really large sql query in SQL Management Studio the results window starts to populate with rows as they are made ready (I effectively want to mimick this), however at the momoent when I run SQlExecDirect() I have to wait untill all rows have been calculated.

The following code is an example from Microsoft about Asynchronously doing an SQLExecDirect() Asynchronously

SQLHSTMT  hstmt1;
SQLRETURN rc;

// Specify that the statement is to be executed asynchronously.
SQLSetStmtAttr(hstmt1, SQL_ATTR_ASYNC_ENABLE, SQL_ASYNC_ENABLE_ON, 0);

// Execute a SELECT statement asynchronously.
while ((rc=SQLExecDirect(hstmt1,"SELECT * FROM Orders",SQL_NTS))==SQL_STILL_EXECUTING) {
   // While the statement is still executing, do something else.
   // Do not use hstmt1, because it is being used asynchronously.
}

// When the statement has finished executing, retrieve the results.

Does anyone know if it is possible or how it is possible to Asynchronously call SQLFetch() Asynchronously and retrieve each row as it is made available?

I don't think async execution will accomplish what you want. SQL Server returns results to the client as they become available if you've specified a fast-forward ODBC cursor type.

Separately, displaying partial results in a GUI interface while the query is still running may require special programming techniques. That's not really a database problem so StackOverflow would be a better forum if you have additional questions about that.


NonClustered Index not being Used I have a table (created below) that contains 321 rows.

I expect the last query below to use the NonClustered Index and then a Key Lookup. However, instead it uses a Clustered Index scan. Only a single row is returned as expected.

Why does it do the scan rather than use the NonClustered Index? Is it because the table contains only 321 rows?

CREATE TABLE dbo.TestIndexSample
(
    Code char(4) NOT NULL,
    Name nvarchar(200) NOT NULL,
    ModifiedDate datetime NOT NULL CONSTRAINT [DF_TestIndexSample_ModifiedDate] DEFAULT GETDATE(),
    CONSTRAINT [PK_TestIndexSample_Code] PRIMARY KEY CLUSTERED(Code)
);
GO
CREATE NONCLUSTERED INDEX IX_TestIndexSample_Name
    ON dbo.TestIndexSample(Name);
GO

INSERT INTO dbo.TestIndexSample(Code, Name)
select CodeName, FullName
from dbo.SourceTest
GO

SELECT * FROM dbo.TestIndexSample

SELECT * FROM dbo.TestIndexSample where Code = 'X132EY'

SELECT * FROM dbo.TestIndexSample where Name = 'User A'

You can force SQL Server to use the nonclustered index:
SELECT Code, Name, ModifiedDate
FROM   dbo.TestIndexSample WITH(INDEX (IX_TestIndexSample_Name))
WHERE  Name = 'NAME10';

dbfiddle here, plan here
Without the hint the query optimizer considers the plan with the index more costly for this small amount of data, but you can get this plan by increasing the number of rows in your table. I managed to achieve it with 600 rows:
SELECT Code, Name, ModifiedDate
FROM   dbo.TestIndexSample
WHERE  Name = 'NAME10';

dbfiddle here
If you want to get just the Index Seek, your query should return only Name, so that data is pulled only from the index.
SELECT Name
FROM   dbo.TestIndexSample
WHERE  Name = 'NAME10';

dbfiddle here, plan here
But if other columns need to be returned too, you could use a covering index:
CREATE NONCLUSTERED INDEX IX_TestIndexSample_Name
    ON dbo.TestIndexSample(Name) INCLUDE (Code, ModifiedDate);

Obviously the index size will increase, but nothing important for this number of rows.
Now your current query will use an Index Seek:
SELECT Code, Name, ModifiedDate
FROM   dbo.TestIndexSample
WHERE  Name = 'NAME10';

dbfiddle here, plan here


How to change the data directory on Postgresql 9.1 on Ubuntu I am having a problem changing the data directory on postgresql 9.1 on ubuntu 13.04:

I first did a:

sudo pg_dropcluster --stop 9.1 main

and then attempted to create a new one in the desired path:

 sudo pg_createcluster -d /home/fccoelho/Documentos/databases/postgresqldata 9.1 main

but i fails with a permission denied:

Creating new cluster (configuration: /etc/postgresql/9.1/main, data: /home/fccoelho/Documentos/databases/postgresqldata/)...
initdb: could not access directory "/home/fccoelho/Documentos/databases/postgresqldata": Permission denied
Error: initdb failed

How do I make this work?

If you create a cluster in Debian/Ubuntu without specifying the owner (option --user of pg_createcluster) then you have to be sure that the directory you specify is accessibile in r/w to the postgres user. Please note that all parent directories should also be accessibile to the same user with bit x (list directory content).

Otherwise, you may specify a different owner using option --user. This should create a cluster ran by that user instead of postgres.

In order to check if postgres user may access that directory, execute this command:

sudo su - postgres -c 'touch /home/fccoelho/Documentos/databases/postgresqldata/test'

and check for any error.


MS SQL Server 2016 - Always On Node-2 Stuck In Resolving State For testing the Always On structure; we suddenly shut down the machine on which Node-1 was running. So we wanted to see that Node-2 turns primary. However, Node-2 was stuck in resolving mode.

3 machines:
First machine runs Active Directory.
Second machine runs Node-1 (instance-1).
Third machine runs Node-2 (instance-2).

Availability group properties:
Cluster type: windows server failover cluster
Database level health detection is checked.

Availability replicas:

Instance-1 (Node-1) properties:
Role: primary
Availability mode: synchronous commit
Failover mode: manual
Connections in primary role: allow all connections
Readable secondary: yes
Seeding mode: manual
Session timeout: 10 seconds
Endpoint URL: TCP://somehost:port

Instance-2 (Node-2) properties:
Role: secondary
Availability mode: synchronous commit
Failover mode: manual
Connections in primary role: allow all connections
Readable secondary: yes
Seeding mode: automatic
Session timeout: 10 seconds
Endpoint URL: TCP://somehost:port (same port with instance-1)

Failover cluster manager properties:
Roles: 1 role is running and owner ise node-1.
Nodes: 2 nodes are up.
Disks: 1 cluster disk is online, owner node is node-2, capacity is
10GB.
Networks: 1 network is up, cluster use: cluster and client.

How can I fix it?
Best regards.

If I read correctly, your failover mode is set to "manual". In this case, SQL will not trigger a failover by itself.
If you are expecting node 2 to be promoted as primary automatically when node 1 goes down, then you will need to change the failover mode to "automatic" and then, that should do it.


How do I maintain more audit history? I created a SQL Server audit and when I view the audit logs I only see today’s audit. I am saving it to a file and I want to store audit for one year. Is there a way to store database and server audit for one year? Does SQL Server store audits for only a day?

CREATE SERVER AUDIT [Audit-20130618-142022] TO FILE
(
  FILEPATH = N'C:\Documents\Audit\Audit' ,
  MAXSIZE = 0 MB ,
  MAX_ROLLOVER_FILES = 2147483647 ,
  RESERVE_DISK_SPACE = OFF
)
WITH
(
  QUEUE_DELAY = 1000 ,
  ON_FAILURE = CONTINUE
);

There is no way to specify the time-span for which audit-files are kept. They are kept based on size and number of rollover files. The old ones will be deleted then. If rollover = 0 and you restart the audit every day, you would only ever have 1 file for example. If you want an "advanced archive mechnanism" you have to store the files elsewhere, i.e. in a special Datawarehouse. (recommended)


How can I speed up a DateTime comparison query in MySQL? I am performing the following query on a MySQL table with around a million records ( ~350MB ).  It takes roughly 1.5 seconds every time.  If I remove the OR last_updated > last_geocoded comparison, the query time improves by an order of magnitude, to less than 100ms.  If I remove the last_geocoded = 0 line, the query time stays at roughly 1.5 seconds.

SELECT id, company_id, raw_address
FROM locations
WHERE
    raw_address IS NOT NULL
    AND (
        last_geocoded = 0
        OR last_updated > last_geocoded
    )
LIMIT 25

If I reduce the query to just WHERE last_updated > last_geocoded, the query time is less than 100ms.

SELECT id, company_id, raw_address
FROM locations
WHERE last_updated > last_geocoded
LIMIT 25

How can I speed up the original query?  Why is that WHERE statement slowing down my query so much?

Below is the table schema showing my keys:

CREATE TABLE `locations` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(11) unsigned DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `description` text,
  `raw_address` varchar(255) DEFAULT NULL,
  `street` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `city_code` varchar(255) DEFAULT NULL,
  `county` varchar(255) DEFAULT NULL,
  `county_code` varchar(255) DEFAULT NULL,
  `state` varchar(255) DEFAULT NULL,
  `state_code` varchar(255) DEFAULT NULL,
  `country` varchar(255) DEFAULT NULL,
  `country_code` varchar(255) DEFAULT NULL,
  `constituency` varchar(255) DEFAULT NULL,
  `constituency_code` varchar(255) DEFAULT NULL,
  `postal_code` int(5) unsigned zerofill DEFAULT NULL,
  `lat` float(10,6) DEFAULT NULL,
  `lon` float(10,6) DEFAULT NULL,
  `headquarters` tinyint(1) unsigned DEFAULT NULL,
  `research_and_development` tinyint(1) unsigned DEFAULT NULL,
  `manufacturing` tinyint(1) unsigned DEFAULT NULL,
  `distribution` tinyint(1) unsigned DEFAULT NULL,
  `sales` tinyint(1) unsigned DEFAULT NULL,
  `retail` tinyint(1) unsigned DEFAULT NULL,
  `source_id` int(11) unsigned DEFAULT NULL,
  `last_updated` datetime NOT NULL,
  `last_geocoded` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `raw_address` (`raw_address`),
  KEY `last_updated` (`last_updated`),
  KEY `last_geocoded` (`last_geocoded`),
  KEY `city` (`city`),
  KEY `county` (`county`),
  KEY `state` (`state`),
  KEY `country` (`country`),
  KEY `postal_code` (`postal_code`)
) ENGINE=InnoDB AUTO_INCREMENT=997680 DEFAULT CHARSET=latin1;

Below is the original query run with EXPLAIN:

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1,"SIMPLE","locations","range","raw_address,last_geocoded","raw_address",258,NULL,509543,"Using where"

Take the 100ms query and LEFT JOIN it back to the locations table:

SELECT B.id, B.company_id, B.raw_address FROM
(
    SELECT id FROM locations
    WHERE last_updated > last_geocoded LIMIT 25
) A LEFT JOIN locations B USING (id)
WHERE B.raw_address IS NOT NULL;

Give it a Try !!!


Duplicate key value error message on a database for a writing groups scenario I am trying to create a database as portrayed in the following diagram:

This is the SQL-DDL code I have written:

CREATE TABLE Publishers
(
    PublisherName      VARCHAR(50)     NOT NULL,
    PublisherAddress   VARCHAR(50)     NOT NULL,
    PublisherPhone     VARCHAR(50)     NOT NULL,
    PublisherEmail     VARCHAR(50)     NOT NULL,

    CONSTRAINT pk_Publishers PRIMARY KEY (PublisherName)
);

CREATE TABLE WritingGroup
(
    GroupName    VARCHAR(15)     NOT NULL,
    HeadWriter   VARCHAR(15)     NOT NULL,
    YearFormed   CHAR(4),
    Subject      VARCHAR(15)     NOT NULL,

    CONSTRAINT pk_WritingGroup PRIMARY KEY (GroupName)
);

CREATE TABLE Books
(
    GroupName        VARCHAR(15)     NOT NULL,
    BookTitle        VARCHAR(50)     NOT NULL,
    PublisherName    VARCHAR(50)     NOT NULL,
    YearPublished    CHAR(4)         NOT NULL,
    NumberPages      INTEGER         NOT NULL,

    CONSTRAINT pk_Books               PRIMARY KEY (GroupName,BookTitle),
    CONSTRAINT books_pub_fk1          FOREIGN KEY (PublisherName) REFERENCES Publishers (PublisherName),
    CONSTRAINT WritingGroups_Books_fk FOREIGN KEY (GroupName) REFERENCES WritingGroup(GroupName)
);

INSERT INTO publishers VALUES('P01','12345 h st ','+1 215 837 0825','e1@gmail.com');
INSERT INTO publishers VALUES('P02','6789 s st ','+1 215 967 9719','e2@gmail.com');

INSERT INTO books VALUES('GroupName1','title 1 ','P1','1994',50);

SELECT * FROM books

However when running the query I get the following error message:

  [Exception, Error code 30,000, SQLState 23505] The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'PK_PUBLISHERS' defined on 'PUBLISHERS'.
      Line 38, column 1

Can anybody see where the mistake is? This is my first time writing a database and I really dont see where I am going wrong. Thanks in advance.

The problem is in the books insert

INSERT INTO books VALUES('GroupName1','title 1 ','P1','1994',50);

You have a foreign key constraint, books_pub_fk1, referencing publishers, but you don't have a publisher P1, it is P01.

This is for referential integrity.

You will also have the same problem once this is fixed with WritingGroups_Books_fk as you don't have a group record in WritingGroup named GroupName1


Windows could not start the SQL Server (mssqlserver) service on local computer error 17058 I'm working with virtual machine on the windows 8. There is a SQL Server 2008 on the virtual machine. And in the services; when I try to start the SQL Server service getting an error like this:

Can anyone help me?

Thanks.

Edit:

The OS of virtual machine is windows server 2008.

CHeck for another error in the event log that is time-correlated to this one. Basically - this one is from the service manager. The SQL Server service should have written another error (either into th event log or the errorlog) that contains the internal error that happened. THis error is generic from the serivce manager and as such totally irrelevant to debugging. It also is well known - google for "Sql server 17058" finds tons of references.

It seems to be a permission error:

http://www.it-sideways.com/2012/11/mssql-startup-error-code-17058.html

points to

"Could not open error log file ''. Operating system error = 3 (The system cannot find the path specified.)."

which would be an operating system level configuration or insallation issue.


Changing InnoDB data directory prevents MySQL service from starting I have a MySQL instance installed on Windows 7 and I wanted to change the directory of InnoDB tables data.
I modified the "my.ini" adding the following line after "[mysqld]":

innodb_data_home_dir="/ibdata"

After that, I wasn't able to restart the MySQL. Looking at the log, it had the following entries:

130124 12:00:52 [Note] Plugin 'FEDERATED' is disabled.
130124 12:00:52 InnoDB: The InnoDB memory heap is disabled
130124 12:00:52 InnoDB: Mutexes and rw_locks use Windows interlocked functions
130124 12:00:52 InnoDB: Compressed tables use zlib 1.2.3
130124 12:00:52 InnoDB: Initializing buffer pool, size = 106.0M
130124 12:00:52 InnoDB: Completed initialization of buffer pool
130124 12:00:52  InnoDB: Operating system error number 3 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
InnoDB: File name \ibdata\ibdata1
InnoDB: File operation call: 'create'.
InnoDB: Cannot continue operation.

I made sure I had created the directory "ibdata" under the MySQL data directory beforehand.
I've tried moving existing files I think are associated to InnoDB to the new directory (from the clue given by the log file) but the problem persisted.

Why mysql cannot start working with the new directory?

You mention that you are running on Windows. The path to Windows directories are not the same as UNIX ( you cannot just use /ibdata). Instead try setting it like:

innodb_data_home_dir="C:\path\to\ibdata"


Dynamic Filters in SQL Server I have problems with my dynamic filter in SQL Server.

My problem is that the filters aren't working. When I try to execute the stored procedure, I just get all the data without filtering.

I'm new to this.

alter PROCEDURE Member_WS
    @Program int ='',
    @Card nvarchar(30)= '',
    @Account nvarchar(128) ='',
    @Member bigint =0,
    @Email nvarchar(256)='',
    @Phone nvarchar(20)='',
    @ResponseType nvarchar ='',
    @FirstName nvarchar =''
AS
    DECLARE @sql varchar(max),
            @filtro varchar(500)

    set @filtro = ''

    if @Program = '' and @Card = '' and @Account = '' and @Member = '' and @Email = '' and @Phone = '' and @FirstName = ''
    set @filtro = ''
    else
    begin
        set @filtro = ''
        if @Program <> ''
            set @filtro = @filtro + ' and' set @filtro =@filtro + '[Program].ProgramID =  convert(varchar,@Program)'
        if @FirstName <> ''
             Select @filtro = @filtro + ' and [member].FirstName= ' + @FirstName
        if @Card <> ''
             Select @filtro = @filtro + ' and [account].IIN + REPLICATE(0, (8 - len (LS001..[account].AccountNumber))) + ltrim(str(LS001..[account].AccountNumber)) +  ltrim(str(LS001..[account].verificationDigitSufix))= ' +  @Card
        if @Account <> ''
             Select @filtro = @filtro + ' and [Account].AccountID = ' + @Account
        if @Member <> ''
             Select @filtro = @filtro + ' and [member].MemberID= ' +  convert(varchar,@Member)
        if @Email <> ''
             Select @filtro = @filtro + ' and [user].email= ' + char(39) + @email + char(39)
        if @Phone <> ''
             Select @filtro = @filtro + ' and [user].PhoneNumber= ' + @Phone
    end
--------------------------------------------
Exec Member_WS @Program = 50
GO

This is not the full query just the filter part.

I would re-write the whole proc something like this.....

ALTER PROCEDURE Member_WS
    @Program        int             = NULL,
    @Card           nvarchar(30)    = '',
    @Account        nvarchar(128)   ='',
    @Member         bigint          = NULL,
    @Email          nvarchar(256)   ='',
    @Phone          nvarchar(20)    ='',
    @ResponseType   nvarchar(100)   ='',
    @FirstName      nvarchar(100)   =''
AS
BEGIN
  SET NOCOUNT ON;
    DECLARE @sql nvarchar(max);

set @sql = N' SELECT * FROM TableName WHERE 1 = 1 ' --<-- for example your main query
         + CASE WHEN @Program IS NOT NULL
           THEN N' AND [Program].ProgramID =  @Program ' ELSE N' ' END
          + CASE WHEN @FirstName <> ''
           THEN N' AND [member].FirstName = @FirstName ' ELSE N' ' END
          + CASE WHEN  @Card <> ''
            THEN N' and [account].IIN + REPLICATE(0, (8 - len (LS001..[account].AccountNumber)))
                   + ltrim(str(LS001..[account].AccountNumber))
                   +  ltrim(str(LS001..[account].verificationDigitSufix))= @Card ' ELSE N' ' END
          + CASE WHEN @Account <> ''
            THEN N' and [Account].AccountID = @Account '  ELSE N' ' END
          + CASE WHEN @Member IS NOT NULL
            THEN N' and [member].MemberID = @Member' ELSE N' ' END
          + CASE WHEN @Email <> ''
            THEN N' and [user].email=  @email ' ELSE N' ' END
          + CASE WHEN @Phone <> ''
            THEN N' and [user].PhoneNumber = @Phone ' ELSE N' ' END

Exec sp_executesql @sql
                  ,N'@Program int , @Card nvarchar(30), @Account nvarchar(128)
                  ,@Member bigint , @Email nvarchar(256), @Phone nvarchar(20)
                  ,@FirstName nvarchar(100)'
                  ,@Program
                  ,@Card
                  ,@Account
                  ,@Member
                  ,@Email
                  ,@Phone
                  ,@FirstName

END


using a column that references an object_id in your own table Is it safe to use OBJECT_ID() as a reference in your own tables, with the assumption that the value will always refer to the same database object no matter what?
The msdn states

All metadata functions are nondeterministic. This means these
functions do not always return the same results every time they are
called, even with the same set of input values.

so that would indicate it's not safe, but I couldn't find any (other) hard facts that it shouldn't be done, and, to be fair, no examples of people actually doing it, which in itself might be evidence not to do it.
But the value OBJECT_ID() is (or seems to be) used in the system tables and views as keys from one object to another, so there must be some form of integrity and reliability.
For the record: I don't necessarily need it, but it just popped into my mind as an option while designing my system, and got curious.

Do not rely on object_id for your application.
My experience is that once an object is created its object_id will be stable for as long as that object exists. The difficulty is in knowing the meaning of "as long as it exists."
If the object is dropped and re-created it will get a new object_id, even if the exact same statement is used both times, creating an object with the same name.
If the creation script runs against different databases (say DEV and TEST) it will generate different object_ids.
If the database is restored old object_id values may re-appear.
Most risky is that stability of object_ids is not documented and therefore is not guaranteed. A patch, upgrade, security patch or anything else may change IDs for existing objects if MS determines this is something they want to do for any reason whatsoever at all.
MS can use object_id in their internal metadata because they know all the places that can invalidate these values and can write their code accordingly. They do not publish these details for us, as application programmers, to consider.
If you need to reference objects in a data-driven way, store their names. Use the correct datatype (SYSNAME) and don't forget to store the object's schema, too. If a numeric value must be passed around there are some options. An identity column could be added. Values will be different between environments and when objects are removed and re-added. Really this is no better than object_id. Better may be to calculate a hash of the schema and object names. If the hash algorithm used is communicated broadly every consumer should be able to generate it on demand. If you have authority over the objects' names (which seems likely since you're writing them) you could simply choose the corresponding ids and publish them. Don't forget the unique constraint on the name and id columns!


A network-related or instance-specific error occurred I have been using a local database on my laptop for the last view weeks. But today I cannot access it. I get the error:

  A network-related or instance-specific error occurred while
  establishing a connection to SQL Server. The server was not found or
  was not accessible. Verify that the instance name is correct and that
  SQL Server is configured to allow remote connections. (provider: Named
  Pipes Provider, error: 40 - Could not open a connection to SQL Server)

The system cannot find the file specified"

The database connection is: my-pc.C:\DB\DBV8.1.1.MDF.dbo

Under configuration manager I set the server browser to start (right click and start).

The following page/Answer gives more detail: https://stackoverflow.com/questions/6987709/unable-to-connect-to-sql-server-instance-remotely


Offsite Log Shipping We currently use CDC to replicated our Production DB offsite (Microsoft SQL Server 2008 R2), but using CDC and the way we process the files is very unstable. Also note that the two servers cannot communicate with each other due to security. The CDC files gets transferred through various channels to get to the offsite server. The file transfer process has not let us down in more that 3 year now so that is not the issue.

I am busy with a project to change the offsite replication to log shipping. I have setup log shipping on production and the files arrive safely on the offsite server. However I am struggling to setup a job that automatically restores the log files that gets transferred to the offsite server. Normally this would not be an issue to setup if the two server could communicate with each other.

I also need to setup the standard job to send warnings if no files are being received.

Please help me on the right path.

There's three separate parts to this:

Q: Do I use SQL Server's built-in log shipping or roll my own? A: I'd roll your own. What you're doing is beyond what the built-in tools are normally used for.

Q: If I roll my own, where do I start? A: I'd use normal SQL Server full and transaction log backups on the primary. On the secondary, start with MSSQLTips' script to automatically restore backup files from a folder. That example will restore all of the files, though, not just some of them - you'll need to adapt it to only restore log backups in there. In a perfect world, you'd only attempt to restore log files that haven't been restored yet, but if you're in a rush, you could restore all of the log files every time - SQL Server will automatically skip logs that have already been applied.

Q: How do I alert when files aren't being restored? Rather than focus on the files, I'd focus on the data. Do restores with standby when you restore the logs so that you can run queries against them, and after each restore job finishes, query your most transactional table looking for the newest record. If it's older than X minutes, your data may not be coming across. (Granted, this only works for databases with decent change rates, like adding records every few minutes - if you don't have that, then some DBAs add a dbo.LastUpdated table with a datestamp in it, and have a SQL Agent job that updates the only record in there every 5 minutes to set the datestamp to current.)


SSIS keeps force changing excel source string to float There is a column in Excel that is supposed to be text, but SSIS sees numeric text there and automatically makes it double-precision float [DT_R8].

I can change it manually in the Output branch's External and Output columns, but the Error Output simply refuses to let me change the respective column.

Error at Extract Stations [Excel Source [1]]: The data type for "output "Excel Source Error Output" (10)" cannot be modified in the error "output column "Group No" (29)".
Error at Extract Stations [Excel Source [1]]: Failed to set property "DataType" on "output column "Group No" (29)".

I have tried modifying the package xml. I tried the IMEX=1 and typeguessrow=0 but none of that has fixed my problem. Does this have any fix at all? There are also articles suggesting you modify your registry so that excel reads more then the default 8 rows before guessing the data type. I do not want to go this route because even if it were to work, then I would have to modify the registry on the machine I plan to run the package on.

The excel field to be imported into the SQL nvarchar field reads for example

295.3
296.33

but they are being written to the SQL table as

295.30000000000001
296.32999999999998

I put in dataviewers and the fields show

295.3
296.33

all the way thru execution which is correct but I guess when it hits the OLE DB Destination source it somehow converts it to the

295.30000000000001
296.32999999999998

which is wrong.

I also have values such as V321.1 and V213.34 for example which are definitively not integers and need to be stored as varchar.

It appears there is no way to fix this issue. Excel looks at the first few rows of data in the column and will always FORCE the datatype to FLOAT no matter what. I was given a solution of creating a template with a few varchar characters in the first few rows (8+) so EXCEL will pick that up as the data type and then import my data. On thing that helped is since I am using Source Safe I am able to "Check In" a file so that it makes my package file "Read Only" so that Visual Studio can not change my pre defined data types.


Restore database without overriding Auto growth I created a database with auto growth 200MB. After restoring database auto growth changed to 1MB. Because my Backup file database auto growth is 1MB.

Is there a way to restore without overriding the auto growth ?

I just tested this in SQL 2016 (13.0.4001.0). Restore retain the auto-growth setting of the database (for which backup was taken).  Here is the code I tested with.
Create database

CREATE DATABASE [AutoGrowTest]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'AutoGrowTest', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\AutoGrowTest.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 LOG ON
( NAME = N'AutoGrowTest_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\AutoGrowTest_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO

Change auto-growth

USE [master]
GO
ALTER DATABASE [AutoGrowTest] MODIFY FILE ( NAME = N'AutoGrowTest', SIZE = 262144KB , FILEGROWTH = 131072KB )
GO

Backup database

BACKUP DATABASE [AutoGrowTest] TO  DISK = N'C:\AutoGrowthTestFull.bak' WITH NOFORMAT, NOINIT,  NAME = N'AutoGrowTest-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Restore with diff name

USE [master]

RESTORE DATABASE [AutoGrowTest_restore] FROM DISK = N'C:\AutoGrowthTestFull.bak'
WITH FILE = 1, move N'AutoGrowTest' TO
N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\AutoGrowTest_restore.mdf'
, move N'AutoGrowTest_log' TO
N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\AutoGrowTest_restore_log.ldf'
, nounload, stats = 5

GO

It retained auto-growth setting of original database.

Please note auto-growth setting changed in SQL 2016 than what is mentioned in some comments and answers.


SQL Server Occupies more than allocated memory. Possible memory leak Using SQL Server 2012 64 bit (v11.0.6020.0 - 2012 SP3)

Below is the scenario:

On our production we have 32 GB DDR3 RAM installed:

Maximum memory limit has been set in SQL server to 16 GB ie 50% capacity:

When I fire up the task manager and check memory occupied in value it shows 16 GB which is correct:

But when I select memory occupied by Percentage, it shows 80%-85% which is NOT RIGHT:

This will keep on Increasing, until it occupies more than 95% and the

Entire system will slow down
Queries will time out

The only way to correct this is a server restart

My questions are

Is SQL Server leaking memory?
Any quick fix so I don't have to restart?
Permanent resolution?

My questions are
  Is SQL Server leaking memory?

Highly unlikely but you should plan for SQL Server 2012 SP4 ASAP. From my past experience I could say that since SQL Server and other applications like SSAS and SSRS are all running on same machine SQL Server might be facing memory pressure and so might be the case for SSAS/RS. I have seen lot of systems like this and it all boils down to memory pressure.

You have 32 G of RAM and you have given just 16 G to SQL Server make it to 20 G and see if this help. Adding more memory would definitely help if you can go for it.

There can be plethora of reasons why queries are timing out and Troubleshooting SQL Server performance issues can help you to find the root cause of it.

  But when I select memory occupied by Percentage, it shows 80%-85% which is NOT RIGHT:

I would start by saying Task Manager is not a correct place to gauge SQL Server memory consumption, it will not tell you correct value when SQL Server service account has Locked Pages in Memory(LPIM) privilege. This is because normally task manager tracks Process Private bytes memory which is pageable and allocated via VirtualAlloc() function but with LPIM chunk of memory allocation is done by AWE API which is NON pageable so task manager does not tracks it and this can lead to incorrect value. For percentage thing which you are looking is actually Percentage of Process Private bytes not the complete memory and it does not provide any relevant information so just stop looking at it.

It is quite normal for SQL Server to utilize memory allocated to it and to know how much physical memory SQL Server is using please use query

select * from sys.dm_os_process_memory

PS: It is always recommended to move other applications on different machine (if possible) and let SQL Server run solely on it own system this will help SQL Server run faster and better


how to check and list all the files related to mongodb in ubuntu? I am looking for config files, password informations for mongodb I installed the mongodb package in ubuntu using apt following the instructions on http://docs.mongodb.org/manual/tutorial/install-mongodb-on-ubuntu/. I know that binaries are in /usr/bin/, and so on. I wonder how to check and list all the files related to the mongodb package.

As noted in the install page, the MongoDB distribution is comprised of several packages:

mongodb-org: This package is a metapackage that will automatically install the four component packages listed below.
mongodb-org-server: This package contains the mongod daemon and associated configuration and init scripts.
mongodb-org-mongos: This package contains the mongos daemon.
mongodb-org-shell: This package contains the mongo shell.
mongodb-org-tools: This package contains the following MongoDB tools: mongoimport, bsondump, mongodump, mongoexport, mongofiles, mongooplog, mongoperf, mongorestore, mongostat, and mongotop.

Typically you would want to install all of these in your development environment, but for production you may only want to have a subset (i.e. perhaps you don't want the tools or shell installed on every server).

To list the contents of an installed package, use dpkg-query --listfiles (you can also use -L as an alias for --listfiles):

dpkg-query --listfiles mongodb-org-server
/.
/usr
/usr/bin
/usr/bin/mongod
/usr/share
/usr/share/man
/usr/share/man/man1
/usr/share/man/man1/mongod.1.gz
/usr/share/doc
/usr/share/doc/mongodb-org-server
/usr/share/doc/mongodb-org-server/GNU-AGPL-3.0.gz
/usr/share/doc/mongodb-org-server/THIRD-PARTY-NOTICES.gz
/usr/share/doc/mongodb-org-server/copyright
/usr/share/doc/mongodb-org-server/changelog.gz
/usr/share/doc/mongodb-org-server/README
/usr/share/lintian
/usr/share/lintian/overrides
/usr/share/lintian/overrides/mongodb-org-server
/etc
/etc/mongod.conf
/etc/init
/etc/init/mongod.conf
/etc/init.d
/etc/init.d/mongod

You can also specify multiple packages, for example:

 dpkg-query --listfiles mongodb-org-server mongodb-org-shell mongodb-org-tools


Storing Social Security Number as DECIMAL(10,9) for Columnstore I am working on a VERY large database (10B + rows) that performs matching on SSN and BirthDate to try and find duplicate records.  The table is using columnstore compression (SQL SERVER 2016) and it occurred to me that i could save the SSN as a DECIMAL(10,9) to preserve the leading zeros and not take the performance hit from a CHAR/VARCHAR.  I am just wondering if anyone has tried this or if there is a reason why this would not work as expected. I know I could cast to INT and just lose the leading zeros but this seemed like a better solution to me.

ISNULL(TRY_CAST('.' + SSN AS DECIMAL(10,9)),0) AS DecimalSSN

I can always cast it back to a string with RIGHT(TRY_CAST(DecimalSSN AS VARCHAR),9) AS SSN

I wouldn't use a DECIMAL to store SSNs with rowstore or columnstore tables. The INT data type has the following advantages over DECIMAL:

It's generally faster for SQL Server to work with
It allows bitmap filters to be pushed down to the storage engine
If the column doesn't allow NULLs then it allows for a "perfect hashing function" which doesn't require a probe residual in joins.

If you need better performance for your SSN column I would use an INT with a leading 1. That preserves leading zeroes which seems to be desired. You should store all SSNs in the same format and only cast when necessary. For example if you need to display an SSN as a string to an end user then SELECT RIGHT(CAST(1012345678 AS INT), 9) returns "012345678". Otherwise work with the raw value.

I have no idea what your queries look like, but suppose you have 100k SSNs in a table and you need to check if any of those SSNs appear in a different table that has a billion rows. Here's what the query might look like:

SELECT *
FROM dbo.SSNS_TO_CHECK_3 c
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.ALL_SSNS_CCI_INT_LEADING_1 t
    WHERE c.SSN = t.SSN
)
OPTION (MAXDOP 1);

Using an INT column with a leading 1, the above query takes 7 seconds on my machine. With the DECIMAL(10, 9) format that you proposed the query takes 63 seconds my machine. Nearly all of the time is spent on the bitmap operator.

There are some additional considerations with columnstore but they don't matter here. INT is superior in every way that I know of compared to DECIMAL(10, 9).


Is having Estimated number of Rows > Actual Number of Rows an issue to worry about? I've a table [order] where a simple select shows cardinal difference between
Estimated Number of Rows and Actual Number of Rows and in a strange way.

Generally Estimated Number of Rows skews as compared to Actual by in this scenario its totally ironic. Moreover "Number Of Execution" is 1.

Query :

SELECT
ord.paymentmode AS [HowTheyPaid],
ord.id AS [OrderId],
ord.ISBackEndSystemMigrated,
ord.ISDuplicate,
ord.ISMerged
FROM

DedicatedDentalPlans_Stage.dbo.[order] ord
WHERE
ISBackEndSystemMigrated=0
AND ISDuplicate = 1

Table

USE [DedicatedDentalPlans_Stage]
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Order_ISBackEnd]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Order] DROP CONSTRAINT [DF_Order_ISBackEnd]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__Order__ISDuplica__382F5661]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Order] DROP CONSTRAINT [DF__Order__ISDuplica__382F5661]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__Order__ISMerged__39237A9A]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Order] DROP CONSTRAINT [DF__Order__ISMerged__39237A9A]
END

GO

/****** Object:  Table [dbo].[Order]    Script Date: 03/09/2016 02:11:57 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Order]') AND type in (N'U'))
DROP TABLE [dbo].[Order]
GO

/****** Object:  Table [dbo].[Order]    Script Date: 03/09/2016 02:11:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Order](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [SubscriberId] [int] NOT NULL,
    [BillingAddressId] [int] NOT NULL,
    [ShippingAddressId] [int] NULL,
    [OrderStatusId] [int] NOT NULL,
    [ShippingStatusId] [int] NOT NULL,
    [PaymentStatusId] [int] NOT NULL,
    [PaymentMethodSystemName] [nvarchar](max) NULL,
    [OrderDiscount] [decimal](18, 4) NOT NULL,
    [OrderTotal] [decimal](18, 4) NOT NULL,
    [SystemUserId] [int] NULL,
    [UserType] [int] NULL,
    [PaymentMode] [varchar](20) NULL,
    [Deleted] [bit] NOT NULL,
    [AutomaticRenewal] [bit] NULL,
    [CreatedOnUtc] [datetime] NOT NULL,
    [ETLControlId] [int] NULL,
    [ETLGenerationId] [int] NULL,
    [ISBackEndSystemMigrated] [bit] NULL,
    [CheckNumber] [varchar](20) NULL,
    [ISDuplicate] [bit] NULL,
    [ISMerged] [bit] NULL,
 CONSTRAINT [PK__Order__3214EC0722AA2996] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Order] ADD  CONSTRAINT [DF_Order_ISBackEnd]  DEFAULT ((0)) FOR [ISBackEndSystemMigrated]
GO

ALTER TABLE [dbo].[Order] ADD  DEFAULT ((0)) FOR [ISDuplicate]
GO

ALTER TABLE [dbo].[Order] ADD  DEFAULT ((0)) FOR [ISMerged]
GO

Solution Opted so Far with no result:
Updated Statistics related to same index and clustered index with FULL SCAN.

In the particular scenario presented:

No, the difference between estimated and actual number of rows is not important. There are two key pieces of information to support that statement:

The query has qualified for a trivial plan; and
The query has further qualified for simple parameterization

This is clear from the Seek Predicate text, where the literal values for ISBackEndSystemMigrated and ISDuplicate have been replaced with the parameter markers @1 and @2:

This means that the plan selected is the same one SQL Server would choose for all possible literal values. Trivial plan reduces compilation time to a minimum for simple queries; simple parameterization promotes execution plan reuse.

In addition, the apparent cardinality mismatch is due to SQL Server producing an estimate for the average case (over all possible literal values).

In other cases, of course it can matter a great deal that cardinality estimates are inaccurate. The trick is knowing when it is important or not. There is no general safe rule of thumb here, much of it comes down to experience. Query tuning is not all science; there is room for art too :)


Pasting data from Excel table takes very long I use SQL Server Management Studio 2012 to import data from Excel sheet to a table on SQL Server. I use the most straightforward way that I just Ctrl+C a table in Excel and Ctrl+V in table edit window in SSMS. I know that many other ways faster exist to import data. But this is most simple without a need to write any code.

But it takes 300 rows with 5 columns about 5 minutes. Is there any way how to speed it up without using completely different way that would require copying CSV on server or writing a script? Why does it take so long?

You don't need to write code. Just use the Data Import/Export Wizard.

Right-click on the database in SSMS, go to Tasks and Import Data. Select Flat File Source as your data source (because you're using CSV), and then browse for the file. Click next, select the database and the target table. You'll need to check the mappings for the CSV columns to the table columns (making sure the CSV column headings are the same as the table columns helps with this), and you can preview the data to make sure.

If everything is correct, click Next, Next and Finish to import the data. If there's an error, it'll tell you what it is (sometimes slightly cryptic though).

As a note though: I'd advise against trying to copy/paste/edit data in the Table Edit window. Writing SQL statements is a much more effective and time efficient way of doing it (or using the Data Import/Export Wizards).


Backup Strategy for Transaction Log. Tail or Standard Log Backup? We are forced to perform some essential maintenance on one of our production environments. Instructions from the vendor state the recovery model of the database should be set to SIMPLE before the work is undertaken.

I'm OK with that and the business has approved the change, but I'm wrestling with the best way forward in terms of disaster recovery planning.

I want to ensure that the database can be restored up to the point of changing the recovery model, if needs be.

We take a full database backup at midnight every night, and log backups every hour between 4am and 9pm (operational hours).

This work will commence at 9am, just after a scheduled log backup, but of course I'm prepared for slippage into that hour based on various factors. I want to ensure we have a log backup in place to cover, lets say, 9.00am to 9.20am.

EDIT: Users are being locked out of the system at 9.00am or thereabouts, however I can't rely on the last log backup alone, as there may be transactions slipping past 9.00am that need to be restored. It depends when Management push the button...

What's the best course of action?

Should I just take an ad-hoc Transaction Log backup which I can, if needs be, apply after the latest Full Backup and sequence of Log Backups?

Or should I be looking at taking a Tail Log Backup, despite the fact I may never use it?

The real problem is that you need to know when the application is shut down, and users are out of the database. If there is a communication issue with knowing "when Management pushes the button," then perhaps you could monitor sys.dm_exec_sessions and/or sys.dm_exec_requests to determine when user traffic has subsided. Once you see that users are no longer actively in the system, you can take your final log backup, and then switch the database into SIMPLE recovery.

If you take a tail log backup (eg, BACKUP LOG...WITH NORECOVERY), you'll kick users out of the database when the DB flips into a recovering state. However, you'll still need to recover the database so that you can change it to SIMPLE and run your upgrade. If users are still in the application when you recover the database, you'll have the same possibility of lost transactions.

Once you know that all users are out of the database, a simple transaction log backup should suffice to capture all transactions.

Additionally, you can take a differential backup on the database even when the database is in SIMPLE recovery mode. Differential backups don't give the same point-in-time recovery as a log backup, but if you are truly looking for a recovery point of "immediately before I start my maintenance," then using a differential might be your best option. This option has two main benefits:

It achieves the goal of having an easy restore point that includes all changes to the database up to a point just prior to the maintenance.
Recovering to that restore point requires restoring only the full + differential backups. This will likely reduce the time & effort necessary to restore, compared to full + N logs.

Compared to taking one final log backup, the differential will take longer to run, and will require more disk space on your backup share. The exact time & space will vary depending on how much data has changed since the last full backup, so that is something you will need to evaluate on your own.

Additional information on differential backups from Microsoft:
https://technet.microsoft.com/en-us/library/ms191180(v=sql.105).aspx


MySQL lost records in a master slave scheme We have developed a web application that uses two MySQL servers set up with replication (master-slave scheme). The application was to be used for a couple of days only, when ~ 50-60 users would enter numeric data. The database was setup to used InnoDB tables. Before the big day, we made a test when a great portion of the expected users entered data in order to see the cpu utilization of the master server and its performance in general.

The big day came and when the data entry began we experienced an issue of losing data from one table. The user data entry form included data that were saved in two different tables. We discovered an application bug that the data for the first table was outside the transaction for the second. This would explain the behavior of not saving data for the sub-form (the second table). However, after fixing the bug and rolling out the newer version, after ~ 2 hours, the issue happened again. The were several records that had the data for the main form saved (the first table) and the data for the sub-form lost (the second table). The users entered again the missing records. Querying the database returned 0 records of lost rows. And then, the issue stroke again. Without explanation, we had somewhat ~170 lost records from the sub-form (the second table).

Could a possible corrupted ibd file lead to such a situation? After doing some basic research in Google, it came down to a post saying that when using InnoDB plugin (as in our case) and you upgrade to a newer version, inconsistencies could arise and you have to delete your database and re-create it again to be sure that all is ok. When we started developing the application, the MySQL version used was 5.1.56 (Debian 6 stable) and at some point, 5.1.61 was pushed as an update. Could this upgrade lead to a situation when some relatively medium-weight contention cause lost records?

My guess would be that there are three(3) aspects to look over:
ASPECT #1
I would be concerned with InnoDB settings because there are new features in InnoDB (starting with the plugin as of MySQL 5.1.38) that allow for accessing multiple CPUs. If you do not set the parameters, InnoDB will behave like it did before the enhancements. In fact, there are cases where InnoDB in older versions of MySQL will operate faster than newer version:

How do I properly perform a MySQL bake-off?
Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)
Using multiple cores for single MySQL queries on Debian
Is the CPU performance relevant for a database server?

ASPECT #2
I would be very carful when upgrading MySQL 5.1 to another version of MySQL 5.1 because MySQL 5.1 introduced the concept of installing plugins because you now have the table mysql.plugin. If you install a newer version of MySQL, make sure you do not hose mysql.plugin. You have to just reinstall the plugin. This, in itself, is not the cause of your problem. Nevertheless, you need to be aware of this in the event that this is a cause.
ASPECT #3
If you have not already done this, I would raise innodb_log_buffer_size to 32M. The default is 8M. Increasing it will reduce disk I/O. Please do not go beyond this, since large COMMITs can and will form I/O-based bottlenecks.
RECOMMENDATION
I would upgrade completely to MySQL 5.5 because all of the InnoDB Plugin is natively installed in MySQL 5.5. As highlighted in my previous links, you must configure and tune the settings needed for InnoDB to use multiple CPUs. Increase your log buffer
UPDATE 2012-06-03 18:30 EDT
Since you cannot install MySQL 5.5, you still have aspects 2 and 3 you could implement.

Install the InnoDB Plugin
Increase the innodb_log_buffer_size to 32M


Generate random timestamp taking into account shift to daylight saving time I want to generate random big data in MariaDB.
One of this columns is random timestamp which I generate like this:

select (current_timestamp
        - interval (rand() * 60 * 60 * 24 * 365 * 10) second
       ) as record_time;

Since the end of March there is a shift to daylight saving time / summer time, so in one day there is no time from 02:00 to 03:00. And when I inserting this data I get error like:
Incorrect datetime value: '2016-03-27 02:20:24.227322' for column db.tab1.record_time.

How to modify this part of insert script to generate correct random timestamp values while accepting daylight saving time?

Link to MariaDB info about timezones

You can create timestamp in SYSTEM timezone with:

FROM_UNIXTIME(timestamp_in_seconds) -- This is a number, not a datetime

To convert back to a timestamp you can use UNIX_TIMESTAMP function.

This sample demonstrate how to generate random datetime based on starting point. It give you only correct timestamps:

select FROM_UNIXTIME(UNIX_TIMESTAMP('2016-03-27 00:30:00') + rand() * 10000);


How to change query_cache_type to 2 in AWS RDS? I want to change my AWS RDS parameter query_cache_type value to 2 , I have done it in parameter group dashboard as in the given image.

I have also restarted my mySQL server with the changed parameter,
but when I am checking this by query I am getting a different response:-

How can I change this static parameter ?

Have you attached the parameter group to the RDS instance properly? I have just checked on my installation, and things are working as expected. (Note, I do have my cache size set to zero, as for our workload it is not beneficial to enable it)

Parameter Group Settings

MySQL Query Cache

mysql> show global variables like 'query_cache%';
+------------------------------+--------+
| Variable_name                | Value  |
+------------------------------+--------+
| query_cache_limit            | 0      |
| query_cache_min_res_unit     | 512    |
| query_cache_size             | 0      |
| query_cache_type             | DEMAND |
| query_cache_wlock_invalidate | OFF    |
+------------------------------+--------+
5 rows in set (0.01 sec)

Parameter Group Assigned to RDS Instance

Regards,
Dave


Missing archivelogs when restoring duplicate We restore backups to our test systems from production via a point in time RMAN Duplicate with an online full backup and hourly archivelog.  Every blue moon something goes wrong (working to resolve that separately) and I end up not having an archive log.  It spends hours restoring the datafiles, gets to the archivelogs and then bombs.

How can I verify that RMAN has all the correct archivelogs and datafiles to restore before it starts pushing the data around significantly?

Use RESTORE ... PREVIEW. I intentionally deleted some archivelogs, so I do not have a backup of them.

Now I want to check if I can restore the database to a specified time:

run
{
  set until time "to_date('2018-11-12 19:58:00', 'YYYY-MM-DD HH24:MI:SS')";
  restore database preview;
}

Output:

executing command: SET until clause

Starting restore at 12-NOV-18
using channel ORA_DISK_1

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4       Full    1.11G      DISK        00:00:03     11-NOV-18
        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20181111T221533
        Piece Name: /oracle_backup/DOMINO_O77/04thvv85_1_1
  List of Datafiles in backup set 4
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  1       Full 2397081    11-NOV-18              NO    +DATA/DOMINO_O77/DATAFILE/system.257.991918055
  3       Full 2397081    11-NOV-18              NO    +DATA/DOMINO_O77/DATAFILE/sysaux.258.991918107
  4       Full 2397081    11-NOV-18              NO    +DATA/DOMINO_O77/DATAFILE/undotbs1.259.991918131
  7       Full 2397081    11-NOV-18              NO    +DATA/DOMINO_O77/DATAFILE/users.260.991918133

no backup of archived log for thread 1 with sequence 63 and starting SCN of 2784642 found to restore
no backup of archived log for thread 1 with sequence 64 and starting SCN of 2784723 found to restore
no backup of archived log for thread 1 with sequence 68 and starting SCN of 2784830 found to restore

List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
7       151.71M    DISK        00:00:01     12-NOV-18
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20181112T195537
        Piece Name: /oracle_backup/DOMINO_O77/09ti2bdp_1_1

  List of Archived Logs in backup set 7
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    43      2396293    11-NOV-18 2397794    11-NOV-18
  1    44      2397794    11-NOV-18 2398565    11-NOV-18
  1    45      2398565    11-NOV-18 2398568    11-NOV-18
  1    46      2398568    11-NOV-18 2398574    11-NOV-18
  1    47      2398574    11-NOV-18 2398581    11-NOV-18
  1    48      2398581    11-NOV-18 2398588    11-NOV-18
  1    49      2398588    11-NOV-18 2398606    11-NOV-18
  1    50      2398606    11-NOV-18 2398912    11-NOV-18
  1    51      2398912    11-NOV-18 2498959    11-NOV-18
  1    52      2498959    11-NOV-18 2498962    11-NOV-18
  1    53      2498962    11-NOV-18 2499076    11-NOV-18
  1    54      2499076    11-NOV-18 2499560    11-NOV-18
  1    55      2499560    11-NOV-18 2499580    11-NOV-18
  1    56      2499580    11-NOV-18 2601155    11-NOV-18
  1    57      2601155    11-NOV-18 2601158    11-NOV-18
  1    58      2601158    11-NOV-18 2601263    11-NOV-18
  1    59      2601263    11-NOV-18 2601701    11-NOV-18
  1    60      2601701    11-NOV-18 2601705    11-NOV-18
  1    61      2601705    11-NOV-18 2601709    11-NOV-18
  1    62      2601709    11-NOV-18 2784642    12-NOV-18

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
9       13.50K     DISK        00:00:00     12-NOV-18
        BP Key: 9   Status: AVAILABLE  Compressed: NO  Tag: TAG20181112T195610
        Piece Name: /oracle_backup/DOMINO_O77/0bti2beq_1_1

  List of Archived Logs in backup set 9
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    65      2784741    12-NOV-18 2784798    12-NOV-18
  1    66      2784798    12-NOV-18 2784812    12-NOV-18
  1    67      2784812    12-NOV-18 2784830    12-NOV-18
recovery will be done up to SCN 2784849
Media recovery start SCN is 2397081
Recovery must be done beyond SCN 2397081 to clear datafile fuzziness
Finished restore at 12-NOV-18

The complete recovery is not possible, and RMAN reported the missing archivelog backups:

no backup of archived log for thread 1 with sequence 63 and starting SCN of 2784642 found to restore
no backup of archived log for thread 1 with sequence 64 and starting SCN of 2784723 found to restore
no backup of archived log for thread 1 with sequence 68 and starting SCN of 2784830 found to restore


INNODB real-time locks? How can I view real-time locks in innodb when information_schema.innodb_locks is not available?

The quick way to see it is to run SHOW ENGINE INNODB STATUS\G

You will see

exclusive locks
shared locks
corresponding queries
number of read IO thread in use
number of write IO thread in use

Here are some sample questions I answered that uses the output of SHOW ENGINE INNODB STATUS\G

Will these two queries result in a deadlock if executed in sequence?
Trouble deciphering a deadlock in an innodb status log

Give it a Try !!!


How to replicate a mongodb pod with its persistent storage (Minikube - Kubernetes) I made these settings :

     apiVersion: v1
     kind: PersistentVolumeClaim
     metadata:
       labels:
         name: mongo-claim0
       name: mongo-claim0
       namespace: my-app
     spec:
       accessModes:
       - ReadWriteOnce
       resources:
         requests:
           storage: 100Mi
     status: {}

       apiVersion: v1
       kind: ReplicationController
       metadata:
         labels:
           name: mongo
         name: mongo-controller
         namespace: my-app
       spec:
         replicas: 1
         template:
           metadata:
             labels:
               name: mongo
           spec:
             containers:
             - image: mongo
               name: mongo
               ports:
               - name: mongo
                 containerPort: 27017
               volumeMounts:
               - mountPath: /data/db
                 name: mongo-claim0
             restartPolicy: Always
             volumes:
             - name: mongo-claim0
               persistentVolumeClaim:
                 claimName: mongo-claim0

    apiVersion: v1
    kind: Service
    metadata:
      name: mongo
      namespace: my-app
      labels:
        name: mongo
    spec:
      ports:
      - port: 27017
        targetPort: 27017
      selector:
        name: mongo

When I try to scale this pod, Minikube UI show :

  mongo-controller-xr21r -> Waiting: CrashLoopBackOff Back-off
  restarting failed container Error syncing pod

And I got an error on the new pod :

  exception in initAndListen: 98 Unable to lock file:
  /data/db/mongod.lock Resource temporarily unavailable

Could you help me to be able to scale a pod with a persistent storage ?

I believe this is because you have more than one pod trying to use the same using the same data directory which you can not do if the pod is running mongo, one data directory has to be exclusive to one mongo instance.

If the goal is to create a group of pods with the same data then you are looking at creating a replica set.

In order to do this first you will need to create multiple persistent volumes as so:

kind: PersistentVolume
apiVersion: v1
metadata:
  name: mongo-persistent-0
  labels:
    type: local
spec:
  storageClassName: manual
  capacity:
    storage: 100Gi
  accessModes:
    - ReadWriteOnce
  hostPath:
    path: "/var/lib/mongo/0"
---
kind: PersistentVolume
apiVersion: v1
metadata:
  name: mongo-persistent-1
  labels:
    type: local
spec:
  storageClassName: manual
  capacity:
    storage: 100Gi
  accessModes:
    - ReadWriteOnce
  hostPath:
    path: "/var/lib/mongo/1"
---
kind: PersistentVolume
apiVersion: v1
metadata:
  name: mongo-persistent-2
  labels:
    type: local
spec:
  storageClassName: manual
  capacity:
    storage: 100Gi
  accessModes:
    - ReadWriteOnce
  hostPath:
    path: "/var/lib/mongo/2"

Then you will want to create a statefulset binding to the persistent volumes which means that when the pods go down and come back up they attach to the correct persistent volume, the yaml will look something like this:

apiVersion: apps/v1beta1
kind: StatefulSet
metadata:
  name: replica-set-a
spec:
  serviceName: "replica-set-a"
  replicas: 3
  template:
    metadata:
      labels:
        role: replica-set-a
        environment: test
    spec:
      terminationGracePeriodSeconds: 10
      containers:
        - name: replica-set-a
          image: mongo
          resources:
            limits:
              memory: 2Gi
            requests:
              memory: 2Gi
          volumeMounts:
          - name: mongo-persistent
            mountPath: /data/db
          command:
            - mongod
            - "--bind_ip"
            - "0.0.0.0"
            - "--replSet"
            - a
            - "--smallfiles"
            - "--oplogSize"
            - "1024"
          ports:
            - containerPort: 27017
  volumeClaimTemplates:
  - metadata:
      name: mongo-persistent
    spec:
      storageClassName: manual
      accessModes:
        - ReadWriteOnce
      resources:
        requests:
          storage: 100Gi

Your service can remain the same apart from changing it to look at replica-set-a. Once they are all up you will need to issue the rs.initiate(... command in order to tell the pods they are now working as one replica set, details of this can be found in the mongodb documentation: https://docs.mongodb.com/manual/tutorial/deploy-replica-set/

** Note, I have copied the example yaml's from my sharded cluster that has been deployed through kubernetes which means I have removed some parameters so there could potentially be a typo, also you might want to scale down the persistent volumes and memory limits before proceeding.


Sync Login PW across Multiple Instances When attempting to failover many logins fail due to nonmatching passwords on both instances.  Is there a way for me to replicate passwords to other servers for all logins which passwords do not match.

Logins are stored in master database. Since system databases cannot be part of AG (before sql server 2019 - From PASS summit 2018, MS announced that system dbs can be part of AG), they are not replicated by default.

The only way to do is to sync logins using sp_help_revlogin or use Late Robert Davis's script - Transferring Logins or use dbatools - Copy-DbaLogin (You can use -Force option, so that it will sync (drop and create) all the logins from source server to your destination server).


UTF8mb4 MariaDB 10.4 - Max key length is 767 bytes error I am running MariaDB 10.4.
I am trying to setup symmetricDS for database syncing.  Some of the tables it creates have fields that are are VARCHAR(255).  I know UTF8mb4 can only support up to VARCHAR(191)

I know I can setup my database to be UTF8 and this problem goes away.  But it is my understanding that is not ideal solution.  I can't change the tables/fields that symmetricDS is creating.

I have read some other posts about this, since there are many.  But haven't seen anything above MariaDB 10.2 which I believe I read that things have changed with the engines past 10.2.  So I just want to see if it is all still relevant.

Is there any way this can be resolved, while staying with UTF8mb4?
Or does it even matter and just go to UTF8?

I'd keep the utf8mb4 as the default.

You can use innodb_large_prefix=1 to get past the index length limitation.

Its also quite possible that symmetricDS doesn't actually need 255 length character field and its just a length for arbitrary default reasons.


Join queries are not working as expected when trying to compare a count result with a value I'm learning SQL from a book and I'm trying to do some exercices on join queries. The only problem that I'm facing is that all of my join queries are not working while they seem well.

These are the tables:

    students(student_id,student_names,student_age)
    courses_students(course_id,student_id)
    courses(course_id,course_schedule,course_room,teacher_id)
    teachers(teacher_id,teacher_names)

The query is "which courses have more than 5 students enrolled?"

Here is what I've done:

select
  course_name,
  count(select count(*) from courses) as count
from students,courses,courses_students
where students.student_id = courses_students.student_id,
courses.course_id = courses_students.course_id
and count > 5

And the other one is "what are the names of students enrolled in at least 2 courses scheduled for the same hours?"

My query:

select student_name,schedule
from students,courses,courses_students
where students.student_id = courses_students.student_id,
courses.course_id = courses_students.course_id
and count > 2

I would start with:

SELECT course_id
FROM courses_students
GROUP BY course_id
HAVING COUNT(student_id) > 5

The result of this query is a new relation (table, derived table), which you can use as any other table. Let's call this corses_5_students

SELECT c.*
FROM courses as c
JOIN (
    SELECT course_id
    FROM courses_students
    GROUP BY course_id
    HAVING COUNT(student_id) > 5
) AS corses_5_students
    ON corses_5_students.course_id = c.course_id


IMPDP from Oracle 10g TO 11g I'm not able to import the full database from oracle 10g to 11g using impdp FULL=Y. It seems that the file path is differing so what shall I do?
The impdp command is looking for oracle 10g path while importing into 11g db.
is there any way to import full database from 10g to 11g using impdp?

A full export/import contains tablespace definitions with datafile paths as well, so impdp tries to create the datafiles with the original path.

Just create the tablespaces manually before running impdp, that is usually what I do. impdp will notice that the tablespaces already exist, and continue with the remaining objects.

You can extract the tablespace creation scripts as well if you want, using the sqlfile clause:

impdp user/password directory=... dumpfile=... sqlfile=ts.sql include=TABLESPACE

The above creates a ts.sql file containing the CREATE and ALTER statements for your tablespaces, which you can edit or use for creating the tablespaces.


Does an INSTEAD OF trigger gets re-created every time you insert into a view? I have SQL Server 2016. I have two tables with the same structure, one contains historical data, and the other one contains current data.
I have a view that programs use, that returns the content of both tables.

CREATE TABLE A (int Id IDENTITY(1,1) NOT NULL, othercolumns, primary key, ect.)
CREATE TABLE B (int Id NOT NULL, othercolumns, primary key, ect.)
CREATE VIEW MyView AS SELECT * FROM A UNION ALL SELECT * FROM B

CREATE TRIGGER TR_INSTEADOFINSERT_ON_MyView on MyView
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO A (columns)
   SELECT data
   FROM inserted
END

I was wondering if the view gets re-created everytime an insert is done in the view. Because if I check in the sys.dm_exec_sessions and sys.db_exec_requests, I always see the "CREATE TRIGGER TR_INSTEADOFINSERT" in the query text beign executed.

Now I know that it's taking a long time because it's a big table and I have a lot of activity on the server. But I was just wondering why I keep seeing the "CREATE TRIGGER" in the sql_text of the query being executed? Is it re-created or not? What's the most efficient way to do this? Should I modify the programs to reference the table directly when inserting?

The trigger is not being recreated.  sql_text in sys.db_exec_requests is at the batch level, not the query level.  See eg

https://blogs.msdn.microsoft.com/dbrowne/2013/01/18/my-favorite-query-for-investigating-sql-server-performance/

For an example of how to parse out the currently-running query from the larger batch.

That wait is to fetch the page that the row is written to, either on the Clustered Index or some non-clustered index.  So I would look at the index design of the table, and see if it should be better-optimized for inserts.


sqlpackage.exe ignoring BlockOnPossibleDataLoss? I have a dacpac that has set the BlockOnPossibleDataLoss set to false, however the deployment is blocked when I run it with sqlpackage.exe, telling me "The column [a] is being dropped, data loss could occur."

However when I use the exact same deployment profile and publish from Visual Studio 2012 it goes through.

Have you tried executing sqlpackage.exe with /p:BlockOnPossibleDataLoss=false in the command line?

I just used SQLPackage.exe to create a .dacpac file of my test database, then I pubished it with the /p:BlockOnPossibleDataLoss=false option, and it overwrote my database.


Can I use only OrientDB without any RDBMS? I'm struggling with this problem and I really need an advice. My project mainly needs a Social structure, users can be connected with some friendship relations, can create groups and so on. In this scenario a Graph database is a must.

However, each user also has some information that fit relational model. For example, purchases, points, historical data and so on.

At first, I thought that using two types of database could solve all my problems, so I've decided for OrientDB and MySQL. The main problem of this approach is that it's hard to mantain consistency between two databases if we are in a distributed scenario, where billion of users does operation on two databases. Moreover, MySQL as a RDBMS has some limitation for scaling.

So, I've thought: Why not use just OrientDB?

As far as I understand with its multi-model architecture and ACID transactions, I can reach the same RDBMS goal (lot of hooks), and at same time I haven't any problem with consistency. I can improve scaling problem too.

So, is it safe to use just one database, like OrientDB, to create a design like mine?

Any DB model can store any type of data. The trade-off is what you're prepared to do in the application and what you want the DBMS to handle for you, plus performance.

So in some ways I think it would be safer to have a single system that's good enough than several disparate systems that are individually excellent at their own part but that I have to stitch together.

Think also from a business perspective. Where are the risks and opportunities here? Are you able to charge more for this new service because of the clever way you've stitched together MySQL and Orient? Conversely, if the stitching frays is there a risk of service failure and lost revenue? If you can quantify the benefits and costs, even only in tee-shirt sizes, of both approaches I think it will help you reach a conclusion.


Please explain SYSCS_UTIL.SYSCS_CREATE_USER() function Can someone please explain to me how SYSCS_UTIL.SYSCS_CREATE_USER() works? I tried adding a new user like this: SYSCS_UTIL.SYSCS_CREATE_USER('user', 'someP@ss'), and it gave an exception Error code 30000, SQL state 4251K: The first credentials created must be those of the DBO.

I found the function in the Derby Docs, but it showed the Fred example, which I also tried, but it gave me the same exception.

I created the database via NetBeans.

Read the secderby.pdf, page 41, "Configuring NATIVE authentication":

Use the SYSCS_UTIL.SYSCS_CREATE_USER system procedure to add credentials
for the Database Owner. Remember that the Database Owner is the user who
created the database.

I assume you created your DB without username/password or you tried to create a different user then the one when you created the DB.

Working example:
CONNECT 'jdbc:derby://localhost:1527/myDB;create=true;user=admin;password=admin123';
call SYSCS_UTIL.SYSCS_CREATE_USER('admin', 'admin123' );
You see in the above example the DBO (database owner) is 'admin' and for the very first SYSCS_CREATE_USER call, the user must be 'admin' too.

Not working example:
CONNECT 'jdbc:derby://localhost:1527/myBadDB;create=true;';
call SYSCS_UTIL.SYSCS_CREATE_USER('admin', 'admin123' );


Updating column in table a from top 1 in column b with foreign key relationship I've got two tables:

Table A:
PlayerProfileId (PK),
Email,
HighScore,
Name,
UserName,
Date

1   g@s.com 37  Gustav  MegaBoi 2018-09-14 09:44:07.4505232

Table B:
ScoreId (PK),
Points,
Date,
PlayerId (FK)

3   36  2018-09-11 14:18:21.9372840 2

So every Player has a one to many relationship to their scores in table b.
I'm trying to write an update function that can update every row (players) highscore column.

Doing something like this works:

update PlayerProfile
set PlayerProfile.HighScore = a.Points
from
    (select top 1 * from Scores where Scores.PlayerProfileId = 3 order by Points desc) as a
    where PlayerProfile.PlayerProfileId =  3

But I don't want to have to manually change the id number of the the players to update.

How can I change my update to automatically to this for all players?

What I was thinking is doing something some kind of join on table A and B and then only selecting the top 1 of each "group" and then update the highscore of that selected row. But I'm stumped as to how I can do this.

select *
from PlayerProfile
join Scores on PlayerProfile.PlayerProfileId = Scores.PlayerProfileId

If I could (for each playerId) only take the row with top Points and then use update the highscore through that row that would be great.

How can I change my update to automatically to this for all players?

Something like this, using a CTE?

with cte as(
select playerprofileid ,Points,row_number() over(partition by playerId order by points desc) as rownum
from scores)

UPDATE playerP
set HighScore = cte.Points
FROM Playerprofile PlayerP
inner join cte on cte.PlayerProfileId = PlayerP.PlayerProfileId
where rownum = 1;


MySQL auto Increment id has taken but seems like not written to disk MySQL is having a very strange issue and we are unable to figure out. So I need expert advice to figure out the serious problem. I have tried to provide all possible details. But you can ask for more if you require more information.

We have one MySQL table(ticket) in a different database which got records inserted by triggers from some others databases tables(application databases). Before going further let me introduces database design and table structure.

We have more than 600 database tables with similar table schema with InnoDB engine.

Databases

db_20110624

db_20110706

db_20110825

and so on....600+

and logdb

We have after_insert and after update_trigger written to application database table. so as soon any record inserted/updated from the application side we got a track in logdb.tbl1 table. (where we are managing ticket base queue for further processing )

Information about logdb.tbl1

We are having around 3 to 4 lacks records in this table/day
current records in tables 12,273,398 ( 12 millions )
we are purging 1-month-old data from the tables regularly but since a month or 15 days we are not purging it.

Table structure:

'serialid', 'bigint(20)', 'NO', 'PRI', NULL, 'auto_increment'

'requestunkid', 'bigint(20)', 'NO', 'MUL', NULL, ''

'fordate', 'date', 'YES', '', NULL, ''

'databasename', 'varchar(255)', 'YES', '', NULL, ''

Problem

We have one process on another server which selects data from logdb.tbl1  process and inserts those rows to another database server.

We are picking data in limit 100 from tickets.channelupdates and insert to another database server. to track the pending queue we are finding max(serialid) from another database server and select next 100 records from ticket.channelupdates. and sometimes it skipped some records to select.

Example:

| logdb.tbl1 |

serialid

78887794

78887795

78887796

78887797

78887798

78887799

78887800

78887801

78887802

.......

But when we run select, it gives a result with some missing rows.. (eg. it gives 78887794,78887795,78887796,78887800,78887801,78887802) here 78887797 to 78887800 records are missing. but when we run the same query with max number after some time.... it gives an accurate result. which is causing the very serious problem for us. some records got skipped due to this discrepancy.

Note the issue is random. which is happening very often since 15 to 20 days. moreover, we noted there are 1sec to 15 sec gap between the insert(to tbl1 table) and select(from tbl1 table).

Thanks for the help in advance.

Please provide the schemas involved, plus the SQL statements.

A wild guess is that you are using OFFSET (or LIMIT nnn,mmm).  I explain here why OFFSET can skip or duplicate rows.


Problem restoring a database in SQL Server I am trying to follow the instructions on the following post:

https://stackoverflow.com/questions/10299811/creating-new-database-from-a-backup-of-another-database-on-the-same-server

To summarize; I am building the logical file list then restoring the database:

RESTORE FILELISTONLY
FROM DISK = 'C:\KeltieCochrane_v220120220.BAK';

RESTORE DATABASE KeltieCochrane_v2
FROM DISK = 'C:\KeltieCochrane_v220120220.BAK'
WITH
    MOVE 'KeltieCochrane_v2' TO 'C:\Program Files\...\KeltieCochrane_v2.mdf',
    MOVE 'KeltieCochrane_v2' TO 'C:\Program Files\...\KeltieCochrane_v2_1.ldf';

I have no .MDF or .LDF in the target folder and understand that they are contained in the .BAK backup file.

When I restore the database into an empty, new database, it states:

The backup set holds a backup of a database other than the existing 'KCDB'.

When I try to restore using the actual database name in the script (KeltieCochrane_v2) I get There are conflicting file locations specified for the file.

If I try to simply restore the database I get a standard 15105 error and the operating system cannot get the text for this error.

What am I doing wrong?

You're getting that first error because kdcb already exists.  And the backed up database is not that same database.  If you want to overwrite an existing, separate database (that isn't the same.  With the same you don't need to specify replace), you need to include the REPLACE option:

RESTORE DATABASE KeltieCochrane_v2
FROM DISK = 'C:\KeltieCochrane_v220120220.BAK'
WITH
MOVE 'KeltieCochrane_v2' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\KeltieCochrane_v2.mdf',
MOVE 'KeltieCochrane_v2' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\KeltieCochrane_v2_1.ldf',
REPLACE

WARNING!!!  You need to be very careful and make serious consideration by specifying REPLACE.  Here is an exact excerpt from BOL regarding REPLACE:

  Replace Option Impact

  REPLACE should be used rarely and only after careful consideration.
  Restore normally prevents accidentally overwriting a database with a
  different database. If the database specified in a RESTORE statement
  already exists on the current server and the specified database family
  GUID differs from the database family GUID recorded in the backup set,
  the database is not restored. This is an important safeguard.

  The REPLACE option overrides several important safety checks that
  restore normally performs. The overridden checks are as follows:

  Restoring over an existing database with a backup taken of another database.

  With the REPLACE option, restore allows you to overwrite an existing database with whatever database is in the backup set, even if
  the specified database name differs from the database name recorded in
  the backup set. This can result in accidentally overwriting a database
  by a different database.

  Restoring over a database using the full or bulk-logged recovery model where a tail-log backup has not been taken and the STOPAT option
  is not used.

  With the REPLACE option, you can lose committed work, because the log written most recently has not been backed up.

  Overwriting existing files.

  For example, a mistake could allow overwriting files of the wrong type, such as .xls files, or that are being used by another database
  that is not online. Arbitrary data loss is possible if existing files
  are overwritten, although the restored database is complete.


Recreate Indexes on 1 billion record table I have a table with over 1 billion records and it has 6 indexes (including Clustered index (ID)). I need to partition this table on a new Clustered index with date column. I have just enough space (150gb) in the ldf file to grow in case of index rebuild (with tempdb OFF).

Please help me with an efficient way to rebuild the indexes on new Partition function/scheme. Which of the two approaches would be an efficient and less resource consuming way (OR recommend any other approach)?

1.

Drop existing CIX and drop all N-CIX's
Create CIX on PScheme
Create aligned/non-aligned indexes on PScheme

2.

Drop only CIX
Create CIX on PScheme
Create aligned/non-aligned indexes (with DROP_EXISTING) ON

Thanks

Now, the best way to answer this is to set up a smaller test database of a few gigabytes. It will accurately predict the best technique.

Your technique (2) is horrible because step 1 leads to all NCI's being rebuilt to work together with the resulting heap table. Don't do that.

The good news is that DROP_EXISTING works with changing the index definition including columns and partition scheme:

  The existing index is dropped and rebuilt. The index name specified
  must be the same as a currently existing index; however, the index
  definition can be modified. For example, you can specify different
  columns, sort order, partition scheme, or index options.

And this is the reason why this feature exists. It is not equivalent to first dropping, then creating.

Here are the basic techniques (all of yours are horrible due to the intermediate heap table):

Drop all NCIs, rebuild the CI WITH (DROP_EXISTING = ON), create missing NCIs
Drop all non-aligned NCIs, rebuild the CI WITH (DROP_EXISTING = ON), create missing NCIs
Rebuild the CI WITH (DROP_EXISTING = ON)

(1) is very light on the transaction log. It is not maximally efficient. (3) is the simplest - it will cause all indexes to be rebuild as aligned indexes. This is not so good because you will have to rebuild those that you don't want to align.

(2) is probably best in all cases. I can't think of any case where it might be worse-off. It has the advantage of reusing existing indexes for a more efficient rebuild (this is possible because you didn't just drop all NCIs). NCIs which you want to be non-aligned don't benefit from this method and would be rebuild needlessly. So get rid of them before starting the procedure.

All of this actually works online although you said you don't need that.

Again, just try it out on a sample database. Certainly you shouldn't entrust the availability of your database to my advice. I suggest you take the ideas from this post and try them out.