Execution plan issue requires reset on SQL Server 2005, how to determine cause? We have a  web application that delivers training to thousands of corporate students running on top of SQL Server 2005. Recently, we started seeing that a single specific query in the application went from 1 second to about 30 seconds in terms of execution time. The application started throwing timeouts in that area.

Our first thought was that we may have incorrect indexes, so we reviewed the tables and indexes. However, similar queries elsewhere in the application also run quickly. Reviewing the indexes showed us that they were configured as expected.

We were able to narrow it down to a single query, not a stored procedure. Running this query in SQL Studio also runs quickly. We tried running the application in a different server environment. So a different web server with the same query, parameters and database. The query still ran slow.

The query is a fairly large one related to determining a student's current list of training. It includes joins and left joins on a dozen tables and subqueries. A few of the tables are fairly large (hundreds of thousands of rows) and some of the other tables are small lookup tables. The query uses a grouping clause and a few where conditions. A few of the tables are quite active and the contents change often but the volume of added rows doesn't seem extreme.

These symptoms led us to consider the execution plan. First off, as soon as we reset the execution plan cache with the SQL command 'DBCC FREEPROCCACHE', the problem went away. Unfortunately, the problem started to reoccur within a few days.

The problem has continued to plague us for awhile now. It's usually the same query, but we did appear to see the problem occur in another single query recently. It happens enough to be a nuisance.

We're having a heck of a time trying to fix it since we can't reproduce it in any other environment other than production. I have downloaded the High Availability guide from Red Gate and I read up more on execution plans. I hope to run the profiler on the live server, but I'm a bit concerned about impact. I would like to ask - what is the best way to figure out what is triggering this problem? Has anyone else seen this same issue?

Probably Parameter Sniffing. To troubleshoot this without running Profiler

1) Add a GUID into the text of the query as a comment. e.g.

/*ED7446E4-03B1-4279-AB43-6FFE800AD860*/

2) Wait for the problem to recur so that it runs slow from the application and fine from SSMS.

3) Compare the query plans for the 2 different environments (retrievable from the below)

SELECT *
FROM   sys.dm_exec_cached_plans cp
       CROSS APPLY sys.dm_exec_sql_text(plan_handle) t
       CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
       CROSS APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
WHERE  t.text LIKE '%ED7446E4-03B1-4279-AB43-6FFE800AD860%'
       AND epa.attribute = 'set_options'


Download the replication snapshot file using FTPS I have two databases for two companies. Company A's database contains domain data. The other company is pulling the data using snapshot replication. We have used FTP to communicate:

Created FTP server on IIS in Window Server 2014
Added the certificate to the Server
Created the replication publisher and given the FTP account information
It is working perfectly without the FTP server
IIS set the certificate and the required SSL connection now it is not working
This data is two company data and we want communication done using FTPS

It is not working, we don't want to use VPN. We got a link from MSDN
  and it is saying:

  If you use SSL to secure the connections between computers in a replication topology, specify a value of 1 or 2 for the -EncryptionLevel parameter of each replication agent (a value of 2 is recommended). A value of 1 specifies that encryption is used, but the agent does not verify that the SSL server certificate is signed by a trusted issuer; a value of 2 specifies that the certificate is verified. Agent parameters can be specified in agent profiles and on the command line.

So where can I set this EncryptionLevel=2?

This is the test cases to connect to the server:

We have changed the Server name during the login ftps://Domain.com
Change the port 990 and open the port still not worked

In short, I want to use FTPS for communication.

I can communicate over FTP. I am working on SQL Server 2014.

Usually, SFTP is a specific port that you specify and the protocol sees the port and negotiates the encryption and shares the public key. It looks like you can use the @ftp_port argument in your publication to specify the appropriate sftp port (usually 22).

ftp technet article: https://technet.microsoft.com/en-us/library/ms146956(v=sql.105).aspx


CAP Theorem and MySQL There's a CAP Theorem: R + W > N. Let's say I have W=1 (master), R = 6 (slaves), N = 6, so one slave can write and read. As the theorem says we could be sure in strong consistency (we always will get the newest value).
I read that data storage could check the version and return the newest one. But is it true in fact of using MySQL? Or maybe we need to use any kind of middleman? How does CAP theorem apply to MySQL?

What you are proposing is not really CAP Theorem unless the cluster is totally a share-nothing architecture. Please note what CAP Theorem is from my old post from Jan 01, 2013 : Consistency in ACID and CAP theorem, are they the same?
CAP

Consistency (All Nodes Have Same Data via Eventual Consistency)
Availability
Partition-Tolerance : system continues to operate despite arbitrary message loss or failure of part of the system

Each node would have a complete copy of the full dataset. The given DB Cluster would need to be support eventual consistency i.e. have ACID Compliance support cluster wide.
YOUR ACTUAL QUESTION

But is it true in fact of using MySQL? Or maybe we need to use any kind of middleman? How does CAP theorem apply to MySQL?

The MySQL setups that operate on CAP Theorem are as follows:

InnoDB Cluster
MariaDB Galera Cluster
Percona XtraDB Cluster

Your best middleman ??? If the data you write is critical and needs to be available, read the data from the Master you wrote it. In any of the aforementioned Cluster Setups, any node can be a Master. If data is written on one Master, they should all have it and be ready to be read back provided all nodes in a Cluster run COMMIT on that same given transaction.
If you have slaves attached to these Masters, each slave must wait on its Master COMMIT for data to be be replicated and become visible on a slave.


In SQL Server, is there way to have a field where an auto-number is appended to the end of whatever value the field is set to? Sorry if that's not overly clear.

In SQL Server, can I have a field which I can place a string into, and then SQL Server appends an auto-number onto the end?

So for example, say I have column A.

If I set row 1 in column A to "Foo", the final value will be "Foo1"
Then if I set row 2 in column A to "Bar", the final value is "Bar2"

Is this possible?

Yes use a Calculated Value column. For example:

ALTER TABLE Table1 ADD ColumnB AS ColumnA + CAST(ID AS varchar(8)) PERSISTED

Assuming ID is where you're getting row 1 or row 2 value.


"ORA-00942: table or view does not exist" when deleting from a very real table in the user's schema I have a table that definitely exists in the logged-in user's schema (therefore I own the table) and is not a view/synonym/anything wacky.

I can select from it, but when I try and either insert, update or delete from this table, I get

  ORA-00942: table or view does not exist

I'm completely baffled.

Once I had a similar Problem. To shorten a rather long story, just flush your shared pool.

ALTER SYSTEM FLUSH SHARED_POOL;

Somehow Oracle seems to create an corrupted entry in the statement cache. Problem has been resolved after flush. After that, we were unable to reproduce it.

You need some administrative privileges to flush it.

One final note: ORA-00942 does not exist in the database documentation at all.

According to my notes, we used this Oracle version.

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production


Trying to create a sequence in Oracle that starts with the max value of primary key of a specific table. Why does this not work? Trying to create a sequence in Oracle that starts with the max value from a specific table. Why does this not work ?

CREATE SEQUENCE reservation_sequence
MINVALUE 0
START WITH (SELECT MAX(reservation_id)
 FROM reservation)
INCREMENT BY 1
CACHE 20;

You cannot mix DDL with DML in this way.

Your best bet is an anonymous PL/SQL block:

BEGIN
DECLARE
seqval NUMBER;
  BEGIN
  SELECT MAX(nvl(reservation_id),0)
  INTO seqval
  FROM reservation;

  execute immediate('CREATE SEQUENCE reservation_sequence MINVALUE 0 START WITH '||seqval||' INCREMENT BY 1 CACHE 20');
  END;
END;
/

Plus, are you sure you want max() and not max()+1?


Migrating SQL Server 2000 to SQL Server 2008 I'm migrating SQL Server 2000 to SQL Server 2008. I have many views. How do I check each views that if they have an order by clause?

Thanks.

If you're asking how to get this information from a SQL Server 2008 installation, then use sys.sql_modules to find the text of a procedure/view

select m.definition
    from sys.views v
    join sys.sql_modules m
        on v.object_id = m.object_id
    where definition like '%ORDER[ ]BY%'

or

   select object_definition(object_id)
    from sys.views v
   where object_definition(object_id) like '%ORDER[ ]BY%'

If you're asking how to get the information from the SQL Server 2000 database, why not backup and restore? Or you can connect to your SQL Server 2000 instance using SSMS, right click on the database, choose "Generate Scripts" and select all objects (or views, or procedures, etc.).


What is the reasoning behind the CAP theorem? http://en.wikipedia.org/wiki/CAP_theorem

http://www.cs.berkeley.edu/~brewer/cs262b-2004/PODC-keynote.pdf

I think it is not very straightforward why only two of

Consistency
Availability
Partition tolerance

can hold for any given distributed database system. This conjecture was proved but is there an easier way to see why probably this might hold?

I am not looking for a proof, just a good way to understand why this theorem might make sense. What is the reasoning?

OK, let's imagine that you have a distributed database.  Let's say you have a node in Oregon and one in California.  The CAP theory says that you will run into problems when setting up this type of database.

For example, if you query data from one database, it needs to be the same as the data in the other database.  This insures that whatever value you have in one database is guaranteed to be in the other (Consistency of the CAP theory).
Doing this allows you to update the data in one database and query it from another, getting the same results.

When we update the data in the Oregon node, the data is sent to the California node so that the databases are consistent.  In order to truly maintain consistency, we have to insure that both databases get the update before either are allowed to truly save the data (two-phase commit using distributed transactions).  In other words, if the California database can't save the data for some reason (e.g. hard drive failure), then the database in Oregon will not save the data and will fail the transaction.

The problem with distributed transactions like the one above comes when we want to have high availability.  In this scenario above, the process of trying to get both databases in sync is a very, very slow process.  (Imagine, we have to send the data from Oregon to California, make sure it gets there, make sure that both databases have locks on the data, etc.)  This causes major problems when we want a system that is fast and responsive even during times of high demand.  (This is the Availability of the CAP theorem.)

Usually, what we do in order to insure high availability is we use replication instead of distributed transactions.  So instead of guaranteeing that California can accept the data, we just go ahead and store it in the Oregon node and then send the data to California when we get around to it.  This guarantees that we can always store the data, regardless whether California is ready to store the data or not.

This improves Availability, but at the cost of Consistency.  See, if someone updates the data in Oregon and then someone (at the same time) reads the data in California, they are not getting the new data--the databases are no longer consistent.  In fact, they won't be consistent until Oregon sends the data to California!

So, that's the Availability -vs- Consistency trade-off.

Partition Tolerance is the third aspect of the CAP theory.  Partitioning is, in this context, the idea that a database (or other distributed system) can break into separate sections and still function correctly.

The question becomes, what happens when both databases are running correctly, but the link from Oregon to California is severed?

If we update the database in Oregon, we need to get the data to California one way or another (distributed transaction or replication).  However, if the link between the two is severed, then the system has become partitioned and the databases are no longer linked together.

When this happens, your choices are to stop allowing updates (to maintain Consistency) at the cost of Availability or to allow the updates (to maintain the Availability) at the cost of consistency.

As you can see, partition tolerance creates direct trade-offs between Consistency and Availability.

There's obviously more to it than that, but those are a couple of examples on how these three major aspects of distributed systems work for and against each other.  Julian Browne's explanation of CAP theory is an excellent place to learn more.


ORA-00932: inconsistent datatypes: expected CHAR got NUMBER I am trying to SUBSTR the first 3 character in use this query -> CASE WHEN (RMSTMP_PNG.ota_activity_lotinfo.KEY = 'TestProgram') THEN TO_CHAR(SUBSTR(RMSTMP_PNG.ota_activity_lotinfo.VALUE,1,3)) ELSE 0 END AS Test1
But it return me this ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
00932. 00000 -  "inconsistent datatypes: expected %s got %s". May i know what wrong in my query? I tried to use To_NUMBER or To CHAR, it also return me same error. Appreciate if someone could help on this issue.
Below is my query:
SELECT RMSTMP_PNG.ota_activity.EQP_ID,
SUM(CASE WHEN (RMSTMP_PNG.ota_activity.MESSAGE='Load lot success.' AND RMSTMP_PNG.ota_activity_lotinfo.KEY = 'Quantity') THEN 1 ELSE 0 END) AS LOT_ID,
SUM(CASE WHEN (RMSTMP_PNG.ota_activity.MESSAGE='Load lot success.' AND RMSTMP_PNG.ota_activity_lotinfo.KEY = 'Quantity') THEN TO_NUMBER(RMSTMP_PNG.ota_activity_lotinfo.VALUE) ELSE 0 END) AS QUANTITY,
CASE WHEN (RMSTMP_PNG.ota_activity_lotinfo.KEY = 'TestProgram') THEN TO_CHAR(SUBSTR(RMSTMP_PNG.ota_activity_lotinfo.VALUE,1,3)) ELSE 0 END AS Test1
FROM RMSTMP_PNG.ota_activity
FULL OUTER JOIN RMSTMP_PNG.ota_activity_lotinfo ON RMSTMP_PNG.ota_activity.ID = RMSTMP_PNG.ota_activity_lotinfo.ID
WHERE RMSTMP_PNG.ota_activity.MODIFIED_DATE >= to_date('27-Sep-2020')
AND RMSTMP_PNG.ota_activity.MODIFIED_DATE < to_date('27-Sep-2020') + 1
group by RMSTMP_PNG.ota_activity.EQP_ID,RMSTMP_PNG.ota_activity_lotinfo.VALUE, RMSTMP_PNG.ota_activity_lotinfo.KEY
order by RMSTMP_PNG.ota_activity.EQP_ID

CASE WHEN (RMSTMP_PNG.ota_activity_lotinfo.KEY = 'TestProgram') THEN
TO_CHAR(SUBSTR(RMSTMP_PNG.ota_activity_lotinfo.VALUE,1,3)) ELSE 0 END
AS Test1

Your case statement returns both a character (then to_char(...)) and a number (else 0). Try modifying the else condition to return a string:
CASE WHEN (RMSTMP_PNG.ota_activity_lotinfo.KEY = 'TestProgram') THEN TO_CHAR(SUBSTR(RMSTMP_PNG.ota_activity_lotinfo.VALUE,1,3)) ELSE '0' END AS Test1

or the first condition to return a number:
CASE WHEN (RMSTMP_PNG.ota_activity_lotinfo.KEY = 'TestProgram') THEN TO_NUMBER(SUBSTR(RMSTMP_PNG.ota_activity_lotinfo.VALUE,1,3)) ELSE 0 END AS Test1


How to model common data for different kind of objects in a relational database? In a current project, we are creating an application with some entites like Customer, Project, Company, Ticket, Request and more. The model of these is straight forward. The complicated part where I can't get my head around is the following:

We would like to have some common entities that can be "attached" to various objects. Some examples include Documents, Tags and Notes. Each of those should be manageable for each of the above mentioned domain entities.

In the server-side code (Java), we want to implement the common functionality as components and reuse/embed them on the specific edit pages of our domain objects. We are using an ORM and would also like to reuse those "metadata" classes.

I think this use-case would be easy to model with a document database.
But how would this be done in a relational model?

What you're asking for is a way to make your relational data model fit an arbitrary code constraint, namely: all "attached" items use a single ORM class.  Rather than tell you how you might do that, I'm going to try to explain why this is not a good idea and what you should do instead.

Code reuse is a great principle to apply to code.  Data isn't code, and relational databases shouldn't be designed primarily to facilitate code reuse.  You should design your database for data integrity not for code reuse.  Designing for data integrity means that you should treat distinct types of things in distinct ways, even if they happen to "look similar" at present.  This is what relational databases are designed and built to do.  They aren't designed to optimize for code reuse.

Furthermore, ORM code is generally not reusable, because it's meant to reflect the structure of and to operate on specific tables.  Even if you happened to have multiple tables that all looked alike structurally and even if you defined interfaces which allowed these tables to work through a common set of code, you still have the need for the data to be persisted in different tables.  There's also the matter of ORM code generators, which are just not built to look for code reuse opportunities like what you're trying to achieve.

You could conceivably develop your own ORM (I built one myself in the 1990's).  If you did this you could build it in such a way as to make it meta-data driven at runtime.  This would give you the feel of using a document database in code while storing your data in a relational database.  However, what you're really doing is mashing a square peg into a round hole.  If your application calls for a document database you'd be better off using one instead of a relational database.

The best you could (and probably should) try to achieve is to make a reusable code component of the middle tier/model code (and the front-end/view, if possible) which branches internally at the back-end/data tier to use per table ORM classes.  This lets you achieve the benefits of using relational databases, the benefits of using ORMs for persistence code, and the benefits of components in your business layer and user interface code.


Conditionally OR vs CASE vs IF Lets say, I have a query,

SELECT * FROM MyTable WHERE MyParam = 0 OR MyColumn = MyParam

Here MyParam is parameter and optional. So, it only check MyColumn = MyParam if MyParam is not 0. But our DBA is saying OR will makes it slow and db will suffers. Another option is,

IF MyParam = 0
SELECT * FROM MyTable WHERE MyColumn = MyParam

The problem with this approach is that we have lot of optional parameters. So, our query become very very big. Another option is CASE.

So what you guys suggest. I am talking about in general whether Oracle or SQL Server.

In sql-server:

One option is dynamic sql, another is option (recompile).

using option (recompile):

select * from MyTable where MyParam = 0 or MyColumn = MyParam option (recompile);

dynamic sql example:

declare @sql nvarchar(max);

if nullif(@MyParam,0) is not null
  begin;
  set @sql = 'select * from MyTable where MyColumn=@MyParam;'
  exec sp_exeuctesql @sql, '@MyParam int', @MyParam;
  end;
else
  begin;
  set @sql = 'select * from MyTable;'
  exec sp_exeuctesql @sql;
  end;

Reference:

Parameter Sniffing, Embedding, and the RECOMPILE Options - Paul White
Dynamic Search Conditions - Erland Sommarskog
Catch-all queries - Gail Shaw
An Updated "Kitchen Sink" Example - Aaron Bertand


sql server service broker queue pass parameter to stored procedure Stored Procedure

Create PROCEDURE [dbo].[InitServiceLog]
(
@name varchar(max)=null
)
AS
BEGIN
    SET NOCOUNT ON;
    insert into [AdventureWorks].[dbo].[ServiceBroker_Log](Service) values(@name)
END

Service Broker Queue calling Stored Procedure Name "InitServiceLog"

CREATE QUEUE dbo.SSBSLearningInitiatorQueue
WITH STATUS = ON , RETENTION = OFF ,
ACTIVATION
(
STATUS = ON ,
PROCEDURE_NAME =[dbo].[InitServiceLog],

///// is there any way to pass Stored
///// Procedures Parameters from here
///// Like this "PROCEDURE_NAME =[dbo].[InitServiceLog]('log')

MAX_QUEUE_READERS = 10,
EXECUTE AS OWNER
)
ON [PRIMARY]
GO

In above query Queue how to call stored procedure

No, activation stored procedures are called "in a vacuum" so they don't allow passing parameters (there's "nothing" calling it) nor is there even a user context in which to execute, which is why the EXECUTE AS syntax exists and is required for activation.

If you need to abstract the logic, it can be separated into two procedures. The activation procedure is parameterless, but it can call another with parameters. Something like this:

CREATE PROCEDURE [dbo].[CentralizedLogic]
(
    @p1 varchar(MAX) = NULL
)
AS
BEGIN
    ...
END

CREATE PROCEDURE [dbo].[QueueActivator]
AS
BEGIN
    EXEC [dbo].[CentralizedLogic] @p1 = 'blah';
END

CREATE QUEUE [dbo].[MyQueue]
    WITH
        ...
        ACTIVATION
        (
            ...
            PROCEDURE_NAME = [dbo].[QueueActivator]
            ...
        );


Why can't you do basic operations AFTER an aggregate in a window function? Like (max(x)+5), vs (5+max(x)) The syntax for aggregates and window functions is confusing, this does not work,

SELECT (max(x)+5) OVER ()
FROM generate_series(1,10) AS t(x);

While this works

SELECT 5+max(x) OVER ()
FROM generate_series(1,10) AS t(x);

That's fine if the operator is commutative, but I'm trying to divide an interval by seconds (in my specific case though I worked around it). Is there anyway to simplify this.

SELECT (max(x)/5) OVER ()
FROM generate_series(1,10) AS t(x);

So another encapsulating query is not needed? Is this a PostgreSQL thing, or a SQL thing? Is there a way to disambiguate the query?

If I'm understanding your question correctly, you're trying to divide the result of the window function by 5. The entire window function consists of MAX(x) OVER () therefore your query would look something like this:

SELECT MAX(x) OVER ()/5 FROM generate_series(1,10) AS t(x);

This will result in 10 rows all containing the value 2, since the generate_series function results in 10 rows and each would contain the result of our window function.


SQL Server Extended event filters: can you use an IN predicate with a subquery? I have an extended event that tracks and filters login events from specific users. The filter is something like this:
WHERE (([sqlserver].[nt_user]=N'fredJohnson') OR ([sqlserver].[nt_user]=N'sallySmith')))

What I would really like to do is use an IN, and have it filter off a much larger list of users. I have these usernames stored in a table. I want to go the subquery route because this EE will be pushed to many different databases with many different users. So for example, I would want it to do something like this, where the lname column is a list of a few hundred users from a table:
   WHERE (([sqlserver].[nt_user]=N'Select lname from @Tbl')))

But it doesn't works. Any ideas how to pull this off?
The full EE i'm trying to modify:
CREATE EVENT SESSION [TrackLogins] ON SERVER
ADD EVENT sqlserver.login(
    ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.session_nt_username,sqlserver.username)
    WHERE (([sqlserver].[nt_user]=N'Select lname from @Tbl')))

WITH (STARTUP_STATE=ON)
GO

No.  Extended Event filters can't run queries.  Extended event filters need to be very cheap, so you should favor over-collecting events to over-filtering them.


Unable to shut down MySQL server I have tried using the system preferences and then shutting down MySQL there, but to no avail. I also tried typing killall -9 and -11. Both failed :(.

I also tried /usr/local/mysql/bin/mysqladmin -u root -p shutdown. The last thing I tried is in the following screenshot... it also did not work.

I am using yosemite, if that matters.

Ideally you can shutdown using

/etc/init.d/mysqld stop

I cannot add comments so requesting to post what do you see in error log when you do

mysqladmin shutdown

About kill commands please note that mysqld_safe is wrapper script which will start mysqld upon killing... so you should kill mysqld_safe first followed by mysqld. ( not a suggested way though).

Refer: how to stop mysql on mac


What is the use of Archivelogs? Hi I am new to Oracle Database . Using Oracle 11g R2

I want to know the purpose of archive logs created at archive log destination, how they are helpful in database backup.

Suppose I am having backup of Production database till "15-sept-14" and I want to restore on Development Database data till "20-sept-14" , how to restore data using backup files and archive logs.

Seems like a good reason to visit the Oracle Documentation site. The 2-day dba documents are very good.

If your database is running in ARCHIVELOG mode, it copies all transactions to the archivelog destination. The transactions are always written to the redolog files but when they are full, they are only saved when running in archivelog mode. This enables you to restore your database to any point in time. This restore operation starts with restoring a full backup where you can apply the archives until you reach the point in time where you want to stop the recovery. For example, close before a table was dropped.

Normally production backups are made with the database online. Again, a reason to run your databases in archivelog mode. IF not running archivelog mode, in a disaster scenario you might loose all transactions made since the last backup. If your transactions are really important it could be smart to copy the archivedlog files to a second DC on a very regularly basis. How regularly depends on the cost of loosing transactions. If you can handle loosing one day, you copy daily, most sites copy a few times/hour or even use standby databases that receive the transactions in a near sync way.

In general you start restoring the database and perform a recovery until time. Normally we use rman to do this, something like

    rman
    run
    {
    set until time to_date('20140914 14:22','yyyymmdd hh24:mi');
    restore database;
    recover database;
    }

when ready and not done a full media recovery, open the database with a reset logs option.

alter database open resetlogs;

To prevent nightmares from happening, it might be smarter to get involvement from a dba.


what is the performance impact if we have two asymmetric files on a Filegroup in SQL server database? i have 2 database files under a primary file group, one is set to restricted auto growth (35 GB), the second database file from a same file group but storing in different drive from first one and kept in  unrestricted auto growth. is this bad practice ? is both should be symmetric in properties ?

I wouldn't say it's a bad practice, but definitely  not a best practice.

It looks like you're using the primary filegroup for user objects, which is not a best practice. You'd better separate system and user objects.

It also appears that you're storing all the objects in the same filegroup. It might be a good idea to isolate some critical objects from the other ones, in order to put them in a dedicated disk for performance or recoverability reasons. Having multiple filegroups allows you to perform file and filegroup backup/restores, which can help you if you have challenging backup schedules.

If your files were the same size, SQL Server could use the proportional fill algorithm to stripe writes over the files. The storage engine tries to write to files that have the most free space available: you get best results (both files getting written in turn) when the files are the same size.

Autogrowth is nice to have to avoid running out of space when you have unplanned growth, but ideally you should grow your files in advance and keep them the same size.

Unlimited growth can be problematic if the file grows until you run out of disk space. In that case, no other database file on that disk will be able to grow and you could end up with multiple databases in trouble instead of just one.


What's the difference between mysql-community-server and mysql-community-server-minimal? I just downloaded the first GA release of MySQL 5.7, and noticed that the RPM bundle comes with two versions of the MySQL server package:

mysql-community-server-5.7.9-1.el7.x86_64.rpm
mysql-community-server-minimal-5.7.9-1.el7.x86_64.rpm

What's the difference between the normal and the "minimal" server packages?

I could not find this information on the MySQL website.

It looks like mysql-community-server-5.7.9-1.el7.x86_64.rpm includes the following additional features (list may not included all) that are not available in the mysql-community-server-minimal-5.7.9-1.el7.x86_64.rpm:

innochecksum
myisam_ftdump
myisamlog — Display MyISAM Log File Contents
myisampack — Generate Compressed, Read-Only MyISAM Tables
mysql_plugin — Configure MySQL Server Plugins
mysql_secure_installation — Improve MySQL Installation Security
mysqld_pre_systemd
mysqldumpslow — Summarize Slow Query Log Files
perror — Explain Error Codes
resolve_stack_dump — Resolve Numeric Stack Trace Dump to Symbols
resolveip — Resolve Host name to IP Address or Vice Versa

I think debugging option is also limited in the mysql-community-server-minimal-5.7.9-1.el7.x86_64.rpm version.


Can someone please tell me where I went wrong for an exercise submission? I recently submitted an assignment in a group for our databases class and we lost points on one of the exercises and the correction is unclear. If someone could examine the question, the submission and the grader's comment for me and tell us what we did wrong I would greatly appreciate it.

Question:

  Given a set of cities in some country, we would like to design an ER-diagram which records the distance between every pair of cities. Assume the attributes for each city is its name and its population with the name being unique, i.e., no 2 cities have the same name. Form an ER-diagram to model this application.

Our submission:

In this example, we see that there is a city entity set, and a country entity set. The city set has attributes of Cname, to distinguish between unique cities, and population. The relationship between country and city is “Located In” and is an M-1 relationship, because there are many cities in only one country. Additionally, city has a recursive relationship that measures the distance between a city and another unique city. The relationship between cities is “Distance” and has a M-1 relationship as well, because the distance from a single city can be compared to many other cities in a country.

The comment:
"Use distance as a relation."

Any help is appreciated. Thank you!

Makes little sense to me. Can you not approach your lecturer/examiner and request further feedback?

Personally I wouldn't be storing distances - store the coordinates and do the calculation on the fly. If you have 100 cities, then you have to store 99 distances with each city - not very pretty.

An example with MySqL is here. If MySQL has this, I'd be fairly sure that all the other major vendors support this functionality. And they do! Found this also which you may find this helpful. You didn't mention the name of your RDBMS but you should always do so.

Take the tour, check out the help centre and the "Help us to help you" blog. p.s welcome to the forums!


Dropping Foreign Key In Production Environment I have been asked to drop foreign keys on a number of databases in our production environment. I am relatively new to MySQL... what do I need to be aware of when doing this? E.g. full table locks, rebuilding indexes, etc.

You should disable anything related to such keys before dropping them

Using the variables FOREIGN_KEY_CHECKS and UNIQUE_CHECKS in your session, run

SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;

Doing this in your session makes mysqld bypass referential checks in your session. You are still subject to the usual locks if anyone is accessing the tables whose constraint you are trying to discard. Indexes would be dropped quickly thereafter.

Your best approach would be to have a test server with the same amount of Production data. Run these two commands and perform the drop of the constraints in such a Staging server before doing this in Prod.

If you need to do this in Prod, schedule proper downtime.

Afterwords, do this

STEP 01 : Run the following

SET GLOBAL read_only = 1;
SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;

STEP 02 : Drop all foreign keys

STEP 03 : Run the following

SET GLOBAL read_only = 0;
SET FOREIGN_KEY_CHECKS = 1;
SET UNIQUE_CHECKS = 1;

STEP 04 : End Downtime


How to set up an SQL Server authentication login for a database instead of using Windows Authentication Not sure what I am doing wrong so I have included the parameters I used which I used the existing login and this article for reference:
http://itproguru.com/expert/2014/09/how-to-fix-login-failed-for-user-microsoft-sql-server-error-18456-step-by-step-add-sql-administrator-to-sql-management-studio/
Whatever I do I get the Error: 18456 message.

Ok found the answer after an hour of trying various options.

Server properties - Security (see image)

Shame there is no warning message somewhere when you create SQL Authentication users that they have no access.


MySQL crashed suddenly MySQL seems to crash suddenly but recovered in around 2minutes. I am running on Ubuntu 18.04 (16vcpu, 32GB RAM). I dont know what caused it.
Here is my.ini
[mysql]
default-character-set=utf8mb4

[mysqld]
lower_case_table_names = 1
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /data/mysql
log-error       = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address   = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
max_allowed_packet=1G
max_connect_errors=1000
max_connections=2500
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
innodb_buffer_pool_size=27G
server-id=2
log-bin=env1
expire_logs_days=2
sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
net_read_timeout=300
net_write_timeout=300
log_bin_trust_function_creators=1

Here is my /var/log/mysql/error.log

The crash is around 8:11am as seen on the image above
Im trying to find the root cause for this. Is there any logs in Ubuntu that I can check to find more info on what caused MySQL to crash?
EDIT:
I saw the OOM (Out of Memory) error on the /var/log/syslog. Any idea what caused this?

innodb_buffer_pool_size=27G is too close to the limit especially with a large amount of (max-)connections.
Lower this down and examine utilization. show global status like 'innodb_buffer_pool_pages% should be full after a reasonable amount of warmup (24hrs+)
show global status like 'innodb_buffer_pool_read%. read_requests vs reads should be quite large. (reads% < 1%).
Once you have this balance hopefully there is sufficient ram available to avoid OOM, but still has sufficient performance.


MYSQL: weird bug with nested stored procedure I have two stored procedures. The first one fetch language codes in my language table.

CREATE PROCEDURE `updateCategoryNameAll` (
IN categorieNumber VARCHAR (10),
IN categoryName VARCHAR (128)
)
BEGIN

DECLARE break BOOLEAN DEFAULT FALSE;
DECLARE languageCode CHAR(5);

DECLARE languageCursor CURSOR FOR
 SELECT `language_code` FROM `ps_lang` WHERE `active` = 1;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET break = TRUE;
DECLARE EXIT HANDLER FOR 1644 RESIGNAL;

OPEN languageCursor;

language_loop: LOOP
    FETCH languageCursor INTO languageCode;

    IF (break) THEN
        LEAVE language_loop;
    ELSE
        CALL updateCategoryName(categorieNumber, categoryName, languageCode);
    END IF;

END LOOP language_loop;

CLOSE languageCursor;
END//

The second procedure update the category translation based on the selected language.

Here's my problem. In the main procedure, when I'm fetching the languages everything is fine:

But, when I'm trying to fetch the language I want to work with in my nested stored procedure:

SELECT  IFNULL(
              ( SELECT  `id_category`
                    FROM  `pc_import_category`
                    WHERE  `source_category_number` = categorieNumber
                    LIMIT  1), 0
              ) AS id_category,
        IFNULL(
              ( SELECT  `id_lang`
                    FROM  `ps_lang`
                    WHERE  `language_code` = language_code
                    LIMIT  1), 0
              ) AS id_lang
        INTO idCategory, idLang;

Here's the result when I'm fetching for active language in the parent procedure:

There should be only one language (USA english), but it returns both my languages (with the good id, but the second language code should be 'fr-ca'). When I'm fetching for the other language, here's what I got.

On my second iteration (to update the french translation), here's what I got:

As a result, I find myself always updating only my english language (which is the id 1).

Is there some kind of limitation, in MySQL, with the table lock, I'm not aware of?

Thank you

Beware when using the same name:

WHERE  `language_code` = language_code

Suggest you precede any arguments or locally DECLAREd variables with a _.

You can probably combine both procedures into a single SQL statement.


1236 On Slave After Master Master Failure I have a master (master 1) that replicates to another master (master 2), which then replicates to its slave.

So master 2 had an issue with the binary logs:

  Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

Which I've run into before and was able to resolve by running the same commands I ran from this thread, expire_logs_days directive requires change master? (I'll ask a question later about why this is still occuring, not my current issue though).

If interested here's the relevant bit from that thread for how I got it working:

stop slave;
reset slave;
change master to master_log_file='...' , master_log_pos=...
start slave;

so now master 2 is good and replicating from master 1. This time though the slave of master 2 broke as well.

The error I'm getting is:

  Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from position > file size; the first event 'mysqld-bin.000397' at 244145356, the last event read from './mysqld-bin.000397' at 4, the last byte read from './mysqld-bin.000397' at 4.'

When I run SHOW BINARY LOGS; on the slave I get:

  ERROR 1381 (HY000): You are not using binary logging

What happened to my slave? Why's it not working with the logs anymore? The master still has the log file so I figured running the stop, reset, change, start would resolve the issue (because it would re-request the logs) but it didn't.

I'll bet sync_binlog was turned off.

With it off, the binlog entries just before the crash may not have been flushed to the binlog file, even though they have been sent to the Slave.

Sending replication data from Master to Slave:

Write to the table on the Master.
Buffer up the write to the binlog.
Optionally flush the buffer to the binlog. -- Controlled by sync_binlog
Send the query to the Slave(s).

When sync_binlog=OFF, there is a big chance that the binlog will be shorter than what the slave thinks it should be.

When the Slave-Master connection is reestablished, the Slave picks up where it left off. With sync_binlog=ON, that would be at the exact end of some binlog, and it would decide to move to the next binlog. The manual CHANGE MASTER simulates that.

The CHANGE MASTER to position 0 (or 4) of the next binlog (bump the number by 1).

(I have never used RESET SLAVE; I see not reason for it.)


DNS lookup failed with error: '11001(No such host is known.)' I have a server in the USA that should connect to one of our servers in Europe through service broker.

The server in Europe is an Availability group, whose listener is called sqlst.

when I run the following command in the USA server:

 declare @sql varchar(1008)
 set @sql = 'powershell.exe -command Test-NetConnection sqlst -port 4022'

 EXEC xp_cmdshell @sql

I get a wonderful message saying that all the connectivity is fine as you can see on the picture below:
The Ip addresses have been masked to protect the innocent.

However,

when I go to my USA Server in the correct database, and I run the following query after I have been told that the broker has not worked:

-- Error messages in the queue
SELECT *
FROM sys.transmission_queue

where service_contract_name = 'ORCACOLAContract'
order by enqueue_time desc

I get the following reason as why it has failed:

DNS lookup failed with error: '11001(No such host is known.)'.

The service account in the Europe Server has been added to Kerberos and all linked servers work fine.

Looking at the sql error log I don't see any related error messages.

I am not sure this is related to service broker and alwayson

I have done all these - linked server working fine.

I have no access outside of sql server in the USA machine.
Inside SQL Server I am sysadmin.

As I said above
when I run the following I get a good response:

declare @sql varchar(1008)
 set @sql = 'powershell.exe -command Test-NetConnection sqlst -port 4022'
 EXEC xp_cmdshell @sql

When I replace the server name by its IP Address and run it:

 declare @sql varchar(1008)
 set @sql = 'powershell.exe -command Test-NetConnection 200.1.0.xxx -port 4022'
 EXEC xp_cmdshell @sql

I also get a positive return.

However, when I run nslookup using the IP address of sqlst - 200.1.0.xxx
I get the below error:

declare @sql varchar(1008)
 set @sql = 'powershell.exe -command $ns = (nslookup.exe 200.1.0.xxx )[-4..-3]
$ns'
 EXEC xp_cmdshell @sql

Basically that says, domain controller cannot find the sqlst ip address

I have no way to run ipconfig/flushdns but I have run the following command instead:

 declare @sql varchar(1008)
 set @sql = 'powershell.exe -command Clear-DnsClientCache'
 EXEC xp_cmdshell @sql

this problem was actually caused by a particular way the trusted domains US-Europe was configured, (beyond the scope of this question) however, using the scripts that you can see on the original question, then working with the sysadmins, and then finding server names that could be authenticated on both ends (source and destination) resolved the case - after altering the routers as per this example:

--targer server
USE [cola]
GO
IF  EXISTS (SELECT * FROM sys.routes WHERE name = N'rou_ORCARoute')
DROP ROUTE [rou_ORCARoute]

CREATE ROUTE [rou_ORCARoute]
WITH  SERVICE_NAME  = N'svc_SendStatusChangeService' ,
BROKER_INSTANCE  = N'CD718202-CB34-4DCD-BADC-7800C5F2FF3D' ,  --BROKER ID OF ORCASTG
ADDRESS  = N'TCP://mysourceserver.mycompany.com:4022' --IP address of my source server
GO

--source server
USE [ORCASTG]
GO
IF  EXISTS (SELECT * FROM sys.routes WHERE name = N'rou_CAUKRoute')
DROP ROUTE [rou_CAUKRoute]

CREATE ROUTE [rou_CAUKRoute]
       WITH  SERVICE_NAME  = N'svc_receiveStatusChangeService' ,
       BROKER_INSTANCE  = N'452711F3-60CD-47E6-BFA4-3BD8C9D6AB56' ,  --BROKER_ID OF COLA
       ADDRESS = N'TCP://mytargetserver.mycompany.com:4022'--IP address of target server
GO


What might be the cause of regular periodic slow transactions on an OLTP system? I have a two node Oracle RAC cluster with that runs an oltp application against it.  I have both a production and a duplicate test environment with the same setup. Upon running stress tests on the Test environment (about 160 tps), The average latency is about 70ms.  I noticed that when I examined the maximum transaction time within each minute, that there is a regular spike in the maximum transaction time every 5 minutes of several transactions that take about 1200-1500 ms.  I've traced the individual long transactions down to the second  and them seem to last for anywhere from 3-5 seconds.  There don't seem to be any regular running jobs that would effect the transactions.   In  environment, we see the same thing and in fact once in a while, we see bigger spikes of transactions taking 15-20 seconds (15000-20000 ms) to complete.  While our production environment is in a pilot mode right now, and only pushes about 4 tps, we do still see these spikes where maximum transaction lengths reach 1200-1500 ms, and they also seem to occur on 5 minute intervals.

I have performed ASH analytics on the small few second intervals, and it seems that there is no common wait event that occurs during these spikes.  Sometimes we see "log file sync" events, sometimes we see "gc busy" or other "gc" wait events.  It seems to vary.  I'm just trying to figure out how to diagnose the underlying cause of this periodic slowness.  Any ideas would be appreciated.

High Log File Sync wait events mean that throughput on writing to the online redo logs is not high enough. This blog gives a great explanation of how to diagnose and troubleshoot that event:

http://logicalread.solarwinds.com/oracle-log-file-sync-wait-event-dr01/#.V_TSTvkrKUk

GC Busy and other GC wait events indicate RAC cluster related waits. We'd need to see a lot more detail to troubleshoot this accurately. That said, if you are having Log File Sync waits on some or all nodes, most likely the other nodes will start showing GC wait events. The nodes have to cooperate with each other. When one node has problems, the whole cluster is affected.


How to check the default schema of WINDOWS_GROUP? I am using the following query to check if the default schema is set to dbo.

SELECT DB_NAME() AS [database]
      ,[name]
      ,[type_desc]
      ,[default_schema_name]
FROM [sys].[database_principals]
WHERE [type] IN ('U', 'G')
    --AND [default_schema_name] <> 'dbo';

If not set to dbo and someone create an object without specifying a schema identifier, a new schema is created with his/her windows credentials.

So, I wanted to write a script to check (on certain period of time) if everything is set OK (because new machines and databases are restored/created on daily basis).

The issue with the script above is that I am able to see that my user's defaults schema is not dbo, but when executed with other account, my user is not listed.

I supposed that's because of the WINDOWS_GROUP that we are all member of, so I need to check its settings, but always get NULL value for groups.

How to check the default schema of WINDOWS_GROUP

The default schema of a group can be checked with your script.

When this script shows NULL for a group it means that the default schema was not explicitly set for this group.

but when executed with other account, my user is not listed

This means that this account is not explicitly mapped to this database, but it's a member of one ore more Windows groups that are mapped.

The rule here is:

  If the user has a default schema, that default schema will used. If
  the user does not have a default schema, but the user is a member of a
  group that has a default schema, the default schema of the group will
  be used. If the user does not have a default schema, and is a member
  of more than one group, the default schema for the user will be that
  of the Windows group with the lowest principal_id and an explicitly
  set default schema.

When you see that

  someone create an object without specifying a schema identifier, a new
  schema is created with his/her windows credentials

this means that this user the user is member of one or more Windows groups and none of these group has a default schema.

If you want to prevent this "schema creation" you should not leave users-Windows groups without default schema, just assign dbo schema to dbo:

alter user [someDomain\someGroup] with default_schema = dbo

To check your user's groups mapped to this database execute the following code:

select *
from sys.user_token
where principal_id > 0

You can filter here on type = 'WINDOWS GROUP' but when non filtered, you see all the tokens, so if you don't see windows account as it is, it's because it was not explicitly mapped to this database and it reaches the database through Windows group(s)

UPDATE

This behaviour is called implicit schema creation and is documented here:
CREATE SCHEMA (Transact-SQL)

  Implicit Schema and User Creation

  In some cases a user can use a database without having a database user
  account (a database principal in the database). This can happen in the
  following situations:

  A login has CONTROL SERVER privileges.

  A Windows user does not have an individual database user account (a
  database principal in the database), but accesses a database as a
  member of a Windows group which has a database user account (a
  database principal for the Windows group).

  When a user without a database user account creates an object without
  specifying an existing schema, a database principal and default schema
  will be automatically created in the database for that user. The
  created database principal and schema will have the same name as the
  name that user used when connecting to SQL Server (the SQL Server
  authentication login name or the Windows user name).

  This behavior is necessary to allow users that are based on Windows
  groups to create and own objects. However it can result in the
  unintentional creation of schemas and users. To avoid implicitly
  creating users and schemas, whenever possible explicitly create
  database principals and assign a default schema. Or explicitly state
  an existing schema when creating objects in a database, using two or
  three-part object names.


How to model a set of unique objects? I have an entity type called 'Object', and I need to model an object set as a list of unique objects with variable length. Then, each object could belongs to many object sets.

Current entity-relationship diagram (ERD)

This is my ERD so far, with a many-to-many relationship:

Tables design

At this point, we have the next tables:

Object table:

ObjectID (PrimaryKey)
ObjectAttribute1
ObjectAttribute2
[...]

ObjectSet table:

ObjectSetID (PrimaryKey)
ObjectSetAttribute1
[...]

ObjectBelongsObjectSet:

ObjectSetID (ForeignKey)
ObjectID (ForeignKey)

Using this model, we could have two different sets of objects with exactly the same combination of objects, which could never happen.

My question

How could improve my model to implement this restriction?

The problem can not be solved with the relational model, but, as @jean says in the comments, we can calculate a hash of the objects that make up the set of objects and form a field with restriction of uniqueness.


Oracle Data Pump for extracting DDL as a set of files – one per object I use Oracle Data Pump to extract the DDL for all schema objects – impdp with sqlfile parameter. It generates one huge SQL file. I want to split it up into several files – one file per one schema object. E.g., a package P should be stored in P.pks and P.pkb – the specification and the body, a table T – in T.tbl, etc.

These files are going to be put into some repository for revision control. That is why one huge SQL file containing all the DDL is inconvenient compared to the set of files for each object. I have not find any tool for splitting the Data Pump SQL file. Therefore I will have to write my own program for splitting the Data Pump SQL file and organizing folder structure for the repository.

Is there any Data Pump options/parameters to generate such set of files? Is there any other tool to do so?

Why bother with Data Pump for a task like that? It is designed for something else, and there are simpler solutions for this without Data Pump, like using DBMS_METADATA directly.

$ mkdir /home/oracle/ddl

Then create the directory object:

SQL> create or replace directory ddl_dir as '/home/oracle/ddl';

Then just run something like below. In this example I generated the DDL for only first 5 (order based on object_id) packages, package bodies and tables to keep the output short.

declare
  l_ddl clob;
begin
  for o in (select * from (select owner, object_name, object_type, row_number() over (partition by object_type order by object_id) as rn from dba_objects where owner = 'SYS' and object_type in ('PACKAGE', 'PACKAGE BODY', 'TABLE')) where rn <= 5)
  loop
    l_ddl := dbms_metadata.get_ddl(replace(o.object_type, 'PACKAGE BODY', 'PACKAGE_BODY'), o.object_name, o.owner);
    dbms_xslprocessor.clob2file(l_ddl, 'DDL_DIR', o.object_name || '.' || case when o.object_type = 'PACKAGE' then 'pks' when o.object_type = 'PACKAGE BODY' then 'pkb' when o.object_type = 'TABLE' then 'tbl' end);
  end loop;
end;
/

And the files:

$ ls -l /home/oracle/ddl/
total 268
-rw-r--r--. 1 oracle oinstall    587 Aug 22 15:04 CDBVIEW_INTERNAL.pkb
-rw-r--r--. 1 oracle oinstall    732 Aug 22 15:04 CLU$.tbl
-rw-r--r--. 1 oracle oinstall  11901 Aug 22 15:04 DBMS_REGISTRY.pkb
-rw-r--r--. 1 oracle oinstall  19790 Aug 22 15:04 DBMS_REGISTRY.pks
-rw-r--r--. 1 oracle oinstall   3822 Aug 22 15:04 DBMS_REGISTRY_SERVER.pks
-rw-r--r--. 1 oracle oinstall    818 Aug 22 15:04 DBMS_REGISTRY_SIMPLE.pkb
-rw-r--r--. 1 oracle oinstall   1102 Aug 22 15:04 DBMS_REGISTRY_SIMPLE.pks
-rw-r--r--. 1 oracle oinstall   8659 Aug 22 15:04 DBMS_STANDARD.pks
-rw-r--r--. 1 oracle oinstall    202 Aug 22 15:04 FET$.tbl
-rw-r--r--. 1 oracle oinstall    740 Aug 22 15:04 SEG$.tbl
-rw-r--r--. 1 oracle oinstall  18722 Aug 22 15:04 STANDARD.pkb
-rw-r--r--. 1 oracle oinstall 164861 Aug 22 15:04 STANDARD.pks
-rw-r--r--. 1 oracle oinstall   1239 Aug 22 15:04 TAB$.tbl
-rw-r--r--. 1 oracle oinstall    344 Aug 22 15:04 UET$.tbl
-rw-r--r--. 1 oracle oinstall    870 Aug 22 15:04 XML_SCHEMA_NAME_PRESENT.pkb

The rest is up to you: customize the above for specific types and file extensions.


Storing complex userdata Assume a tool where one can create an useraccount, then store 'fields' for that account. Each field can contain zero or more 'sub-fields' and each of those can contain zero or more strings.

I wonder how a proffesional developer would design a database structure for such a use case.

My approach would be:

Create a table for each user
Add two rows, fields and id
store a big JSON object in the first row, which contains every subfield and its strings

But somehow i think that this won't be the best way to solve this and i'm having a giant brain fart.

I would greatly appreciate any suggestions.

I did a presentation about alternative ways of defining "extensible" relational database schema at the Percona Live MySQL Conference & Expo in April 2013.  Here's a link to my slides:

http://www.slideshare.net/billkarwin/extensible-data-modeling


Best practices for generating a tnsnames.ora entry I need to integrate some scripts that export and import data from Oracle databases. They are shell scripts that rely on the TNS name being set up (e.g., scott/tiger@tns).

The application owners are likely not going to change the scripts to support EZConnect syntax, so my best option right now is to automate the TNS name setup. The file, for reference, sits here:

$ORACLE_HOME/network/admin/tnsnames.ora

Is there already a best practice / existing utility for doing this? Or am I just going to have to write a shell script to pump out the task?

Rather than using tnsnames, I have used openLDAP to do the names resolution.
You can replicate across two nodes if you want. As soon as you change the entry in openLDAP every server has the new information. You can even get the resolution from an ssl encrypted port if you want.
using openLDAP the name resolution


fn_listextendedproperty fails in single_user mode I have an open single-user session on a SQL Server 2016.

All other queries work, but fn_listextendedproperty fails with Database 'EIP_TIM' is already open and can only have one user at a time.

It looks a bit like it tries to create a second connection internally.

The Activity Monitor looks like this:

(the selected connection is my current one)

Note that there have been schema-modifying statements after alter database EIP_TIM set single_user.

If I run just exactly the following block, then it succeeds.

alter database EIP_TIM set multi_user;

alter database EIP_TIM set single_user with rollback immediate;

   select count (*) from tabObject;
   SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE', N'tabAttribImage', N'COLUMN',N'AttribImageID');

But this is all part of a larger update script, and in that situation it fails.

I know this question is at the moment a bit generic. I first wanted to ask if there are any known issues with fn_listextendedproperty in combination with single_user mode.

If you need any other information, please let me know.

Apparently the big bold warnings were not just for show - setting AUTO_UPDATE_STATISTICS_ASYNC OFF fixed the issue.


Deleting duplicate based on latest datestamp entry I have a table and I'm trying to remove all the duplicate and keep the
the rows that has the latest datestamp.

Here is the table:

email address       orgin_date   new_opt_in_date   datestamp
123@ax.tu            1900-1-1     1900-1-1          2016-3-15
123@ax.tu            1900-1-1     1900-1-1          2016-3-15
iron_man@metrix.com  2015-2-2     2016-12-26        2017-1-19
iron_man@metrix.com  2015-2-2     2016-12-26        2018-6-6
sleep@dort.st        2016-3-15    2016-3-151        2019-1-23
sleep@dort.st        2016-3-15   2016-3-151         2018-5-6

I'm trying to keep only the data that has the recent datestamp, delete the
rest and hope that the
output will like this:
email address       orgin_date   new_opt_in_date   datestamp
 123@ax.tu            1900-1-1     1900-1-1          2016-3-15
 iron_man@metrix.com  2015-2-2     2016-12-26        2018-6-6
 sleep@dort.st        2016-3-15    2016-3-151        2019-1-23

DELETE FROM `tablename`
WHERE datestamp
NOT IN (
SELECT * FROM (
  SELECT MAX(datestamp) FROM tablename
    GROUP BY emailaddress
 )
 )
but nothing it didn't work

You can use a CTE to add a row number that starts at 1 from the newest datestamp for each e-mail address, then delete all the rest.

;WITH x AS
(
  SELECT email, origin_date, new_opt_in_date, datestamp,
    rn = ROW_NUMBER() OVER (PARTITION BY email ORDER BY datestamp DESC)
  FROM dbo.tablename
)
DELETE x WHERE rn > 1;


Subquery with variable in where I don't know why but if I do the subquery where in the first LEFT JOIN with the value of the variable @id it does not return any results, it is as if the value of @id was always null.
But why not do the LEFT JOIN directly?
Simple, the table is large and the query is taking horrors to execute, so it would be much faster.
SELECT
    DISTINCT @id := p.id_pen,
    p.id_env_pen,

    0 AS id_star,
    FALSE AS is_star,

    u.id_user AS sender_id,
    u.name_user AS sender_name,

    u2.id_user AS response_id,
    u2.name_user AS response_name,

    p.text_pen AS text_send,
    p2.text_pen AS text_response,

    DATE_FORMAT(p.created_at_pen, '%Y-%m-%d %H:%i:%s') AS send_datetime,
    DATE_FORMAT(p2.created_at_pen, '%Y-%m-%d %H:%i:%s') AS response_datetime,

    p.time_res_pen,
    p.state_pen,
    p.created_at_pen
FROM pendency AS p
    LEFT JOIN (
        SELECT
            p2.id_pen,
            p2.text_pen,
            p2.created_at_pen,
            p2.id_pen_req_pen
        FROM pendency AS p2
        WHERE p2.id_pen_req_pen = @id
        # HERE, I try do `= p.id_pen` but dont works too,
        # id not identify the alias from outside
    ) AS p2 ON p2.id_pen_req_pen = @id

    INNER JOIN users AS u ON u.id_user = p.id_user_pen
    LEFT JOIN users AS u2 ON u2.id_user = p.id_user_req_pen
WHERE (p.id_user_pen = '5' AND p.id_user_req_pen != -10)
OR p.id_user_req_pen = '5'

CREATE TABLES
CREATE TABLE IF NOT EXISTS `users` (
    `id_user` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `id_fs_user` VARCHAR(30) DEFAULT 'Não informado',
    `email_user` VARCHAR(105) DEFAULT 'Não informado',
    `pass_user` VARCHAR(35) DEFAULT 'Não informado',
    `name_user` VARCHAR(120) DEFAULT 'Não informado',
    `cpf_user` VARCHAR(15) DEFAULT 'Não informado',
    `type_user` VARCHAR(15) DEFAULT 'master',
    `work_week_user` INT DEFAULT 5,
    `is_block_user` BOOLEAN DEFAULT FALSE,
    `permission_id_user` INT NOT NULL DEFAULT 0,
    `attendant_user` BOOLEAN DEFAULT FALSE,
    `version_id_user` INT NOT NULL DEFAULT 0,
    `last_login_user` DATETIME,
    `last_move_user` DATETIME,

    `id_user_user` INT DEFAULT 0,
    `created_at_user` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `safe_del_date_user` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `safe_del_state_user` BOOLEAN DEFAULT FALSE,
    `safe_del_user_user` INT DEFAULT 0);

CREATE TABLE IF NOT EXISTS `pendency` (
    `id_pen` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `id_env_pen` INT NOT NULL,
    `text_pen` TEXT,
    `id_user_req_pen` INT DEFAULT 0,
    `id_pen_req_pen` INT DEFAULT 0,
    `time_res_pen` INT NOT NULL,
    `state_pen` BOOLEAN DEFAULT FALSE,
    `id_old_pen` INT NOT NULL,
    `id_user_pen` INT NOT NULL,
    `created_at_pen` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`id_env_pen`) REFERENCES `env`(`id_env`) ON DELETE CASCADE);

The problem with your query performance is that x = 5 or y = 5 queries don't usually use indexes.
We help this out with your code by creating greatest and least values as generated columns with indexes.
alter TABLE `pendency`
add lpen int as (least(id_user_pen,id_user_req_pen)),
add gpen int as (greatest(id_user_pen,id_user_req_pen)),
add index gpen(gpen),
add index lpen(lpen);

Then use a union, first to grap those with 5 as the greater user (talking to 1-4) and then 5 as the least talking to the others.
(
SELECT
    p.id_pen,
    p.id_env_pen,
    0 AS id_star,
    FALSE AS is_star,
    p.text_pen AS text_send,
    p2.text_pen AS text_response,
    DATE_FORMAT(p.created_at_pen, '%Y-%m-%d %H:%i:%s') AS send_datetime,
    DATE_FORMAT(p2.created_at_pen, '%Y-%m-%d %H:%i:%s') AS response_datetime,

    p.time_res_pen,
    p.state_pen,
    p.created_at_pen
FROM pendency AS p
JOIN pendency p2 ON
  p2.id_pen_req_pen = p1.id_pen
  AND p.gpen= 5
)
UNION ALL
(
SELECT
    p.id_pen,
    p.id_env_pen,
    0 AS id_star,
    FALSE AS is_star,
    p.text_pen AS text_send,
    p2.text_pen AS text_response,
    DATE_FORMAT(p.created_at_pen, '%Y-%m-%d %H:%i:%s') AS send_datetime,
    DATE_FORMAT(p2.created_at_pen, '%Y-%m-%d %H:%i:%s') AS response_datetime,

    p.time_res_pen,
    p.state_pen,
    p.created_at_pen
FROM pendency AS p
JOIN pendency p2 ON
  p2.id_pen_req_pen = p1.id_pen
  AND p.lpen= 5
)

Joining to users, handling of (id_user_req_pen!= -10) and ordering has been left as an exercise to the reader you.


Reindexing Partitioned Table Indexes Historic Reporting Server running SQL 2008 R2.

I'm trying to tweak the fill factor. I have a job that records the fragmentation when the jobs runs in the early hours.

For every table that had high fragmentation I have reduced the fill factor from the 100% default for specific indexes which has worked well. But my largest 3 tables are partitioned.

Unfortunately in SQL 2008 R2 you cannot amend the fill factor on a specific partition. The fragmentation on the active partition is 99% on several of these indexes when the job runs.

I don't want to increase all partitions as they are huge, already take 150GB of 250GB db. Does anyone have any suggestions on a workaround or do I have to wait for the next major release hoping this fairly large omission in partitioned table manitenance is corrected.

An option you can try is to combine partitioned views with the table level partitioning.  By having a partitioned view which is current and historic tables unioned together, you will be able to have different table options i.e. fill factor etc.  This also with give better statistics on the active portion of the data as it's no longer skewed with all the historic data.

USE master;
GO
DROP DATABASE PartitionDemo
GO
CREATE DATABASE PartitionDemo
GO
USE PartitionDemo;
GO
CREATE PARTITION FUNCTION everyHundredThousand(int) AS RANGE RIGHT FOR VALUES(100000,200000,300000,400000,500000,600000,700000,800000,900000,1000000)
CREATE PARTITION SCHEME allPrimary AS PARTITION everyHundredThousand ALL TO ([PRIMARY]);
GO
CREATE TABLE    dbo.HistoryTable
(
    ID  int NOT NULL /*Note not an identity*/,
    Filler char(200) NOT NULL,
    CONSTRAINT PK_HistoryTable PRIMARY KEY CLUSTERED(ID) WITH (FILLFACTOR=100)
) ON allPrimary(ID)
GO
CREATE TABLE    dbo.CurrentTable
(
    ID  int NOT NULL IDENTITY(0,1),
    Filler char(200) NOT NULL,
    CONSTRAINT PK_CurrentTable PRIMARY KEY CLUSTERED(ID) WITH (FILLFACTOR=80)
) ON allPrimary(ID)
GO
/* Now generate some data */
INSERT  CurrentTable
        (
            Filler
        )
SELECT      TOP 1000000
            ''
FROM        sys.objects     a
CROSS JOIN  sys.objects     b
CROSS JOIN  sys.objects     c
CROSS JOIN  sys.objects     d
GO
/* Rebuild Indexes to set Fill Factor */
ALTER INDEX ALL ON dbo.CurrentTable REBUILD
GO
/* Create Partitioned View */
CREATE VIEW CurrentPlusHistory
AS
SELECT      ID,
            Filler
FROM        HistoryTable
UNION ALL
SELECT      ID,
            Filler
FROM        CurrentTable
GO
/* Move some data to History Table */
ALTER TABLE CurrentTable SWITCH PARTITION 1 TO HistoryTable PARTITION 1
ALTER TABLE CurrentTable SWITCH PARTITION 2 TO HistoryTable PARTITION 2
ALTER TABLE CurrentTable SWITCH PARTITION 3 TO HistoryTable PARTITION 3
ALTER TABLE CurrentTable SWITCH PARTITION 4 TO HistoryTable PARTITION 4
ALTER TABLE CurrentTable SWITCH PARTITION 5 TO HistoryTable PARTITION 5
ALTER TABLE CurrentTable SWITCH PARTITION 6 TO HistoryTable PARTITION 6
ALTER TABLE CurrentTable SWITCH PARTITION 7 TO HistoryTable PARTITION 7
ALTER TABLE CurrentTable SWITCH PARTITION 8 TO HistoryTable PARTITION 8
GO
/* For the Partitioed View to work we need a check constraint to define the key range */
/* as we've not created one this query will look at both tables */
SET STATISTICS IO ON
SELECT      *
FROM        CurrentPlusHistory
WHERE       ID < 100000
AND         Filler = 'One'
SET STATISTICS IO OFF
GO
SELECT MAX(ID) FROM dbo.HistoryTable

/* Notice the range for the each table is define so SQL Server can use this value to select the target table */
ALTER TABLE dbo.HistoryTable ADD CONSTRAINT HistoryTable_ID_Range CHECK(ID<800000)
ALTER TABLE dbo.CurrentTable ADD CONSTRAINT CurrentTable_ID_Range CHECK(ID>=800000)
GO
/* Notice in the output this time SQL Server doesn't look into the current table */
SET STATISTICS IO ON
SELECT      *
FROM        CurrentPlusHistory
WHERE       ID < 100000
AND         Filler = 'One'
SET STATISTICS IO OFF
GO
/* CHECK FILLFACTORS FOR TABLES\PARTITIONS */
SELECT      OBJECT_SCHEMA_NAME(o.object_id)+'.'+o.name as Table_Name,
            s.*
FROM        sys.objects  o
INNER JOIN  sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'DETAILED')   s
ON          o.object_id                                 = s.object_id
AND         s.index_id                                  = 1
AND         s.index_level                               = 0
AND         s.page_count                                <>0
WHERE       o.name IN ('CurrentTable','HistoryTable')
GO
/* Now rebuild HistoryTable to see that its Partitions take the FillFactor */
ALTER INDEX ALL ON dbo.HistoryTable REBUILD
GO
/* CHECK FILLFACTORS FOR TABLES\PARTITIONS */
SELECT      OBJECT_SCHEMA_NAME(o.object_id)+'.'+o.name as Table_Name,
            s.*
FROM        sys.objects  o
INNER JOIN  sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'DETAILED')   s
ON          o.object_id                                 = s.object_id
AND         s.index_id                                  = 1
AND         s.index_level                               = 0
AND         s.page_count                                <>0
WHERE       o.name IN ('CurrentTable','HistoryTable')
GO
/* Move some more data into the History Table, the CHECK CONSTRAINT range has to be managed as well so this is a coding overhead */
ALTER TABLE HistoryTable DROP CONSTRAINT HistoryTable_ID_Range
ALTER TABLE HistoryTable ADD CONSTRAINT HistoryTable_ID_Range CHECK(ID<900000)
ALTER TABLE CurrentTable SWITCH PARTITION 9 TO HistoryTable PARTITION 9
ALTER TABLE CurrentTable DROP CONSTRAINT CurrentTable_ID_Range
ALTER TABLE CurrentTable ADD CONSTRAINT CurrentTable_ID_Range CHECK(ID>=900000)
GO


Buffer Size Changing? I have a production database that is experiencing wildly fluctuating Page Life Expectancy (PLE) issues.  (It crashes to zero at random times.)
I have been researching the PLE issue and have found something that seems to point to a VMWare issue, but I am not sure I am using the data right.  It seems like I am losing buffer/cache pages.
I am using this query:
SELECT  COUNT(*) AS cached_pages_count,
        CASE database_id
            WHEN 32767 THEN 'ResourceDb'
            ELSE DB_NAME(database_id)
        END AS database_name
FROM    sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id), database_id
ORDER BY cached_pages_count DESC;

(Found here)
I am totaling the results (the count) before and after my PLE crashes.  An example is 1,097,820 before and 131,394 after.  So I seem to "lose" 966,426 pages.
My guess is that the hardware for all the virtual machines is under stress, so it will randomly swap out some memory from the server for a while.  (This is just a guess.)  When that happens all the pages are lost, so the PLE plummets.
So, am I using the sys.dm_os_buffer_descriptors view correctly?  From what I read it always shows used buffer/cached pages.  So if it is empty (or significantly reduced), I either don't have the memory anymore, or it is empty.
(I would love a way to confirm this conclusion.)
Or is there another explanation as to why the count drops so much?
Information below the line was added from the OP's comments

Our System Admins manage the VMs. I am hoping to understand my query before I go to them with this data. The timing of the PLE crashes seems random from the database point of view. (No re-indexing or other high performance stuff happening during the PLE Crashes)
I have done a ton of work to see if it was work load related. And while there is one poorly performing query, it is not enough to use up all the cache. [There is]  no rebuilding or other non-routine user activity on the server when the buffer counts go down. And even if it was, would I not see that being used in my query above? (Meaning if it was a SQL Server action, wouldn't the counts stay the same, just with different stuff?)
I don't have access to the VMWare settings. I was hoping to understand my findings better before involving those that do.  The point of this question was to ensure I was using the view correctly first.
At the end of the comment chain:
I was trying to say that the PLE issue lead me to the loss of Buffer Pages issue.  The query I was using to get PLE would show a low PLE because the pages were being lost. So what was in them was gone. It was a false reading because the amount of memory was reduced.
Here is my @@Version:
Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
    Dec 28 2012 20:23:12
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

Q:  I have a production data base that is experiencing wildly fluctuating Page   Life Expectancy (PLE) issues. (It crashes to zero at random times.)

Let me ask you what is output of Select @@Version. What is SP and CU level to which your SQL Server is patched. The reason I am asking this is because there was bug in SQl Server 2012 which forced PLE to plummet like what you are observing. Ths bug was fixed in SQL Server 2012 SP1 CU4. Or to be on safer said I would recommend you apply SQL Server 2012 SP2 instead of going for CU4
Its sometime normal for PLE to fluctuate on system having high activity. Actually this is by very virtue how PLE code works in SQL Server. But the fact that its plummetting to zero quite frequently make me believe you might be hitting the bug I have mentioned above.
As per Microsoft Bug fix detail

You may experience slow performance in SQL Server 2012. When you check SQL Server Performance Monitor tools, you see the following:
•A rapid decline in the SQLServer:Buffer Manager\Page life expectancy performance counter values. When this issue occurs, the counter is near 0.

PLE on system is measure of how volatile your buffer pool is, its also measure of amount of I/O activity going in your SQL Server. MSDN says that

Page life expectancy - Indicates the number of seconds a page will stay in the buffer pool without references

Believe me this definition is incomplete. It describes it in form of time which is not a complete definition. I have always noticed that it is measure of I/O activity on server. The greater the I/O activity the more volatile would be BPool, thus fluctuating PLE.

Q:  My guess is that the hardware for all the virtual machines is under stress, so it will randomly swap out some memory from the server for a while.

If you believe this is the case and you want SQL Server not be be victim of such issues you must make sure SQl Server service account has Locked Pages in Memory Privielge (LPIM). This will not let OS to force SQL Server page out its memory. If account running SQL Service is local system by default SQL Server will have this privilege in SQL Server 2012.
Note:

This is a workaround. The solution here would be to find out what is causing stress to VM machine. You should fix that. If you feel Wmware Balooning is the issue. You can use RAMMAP tool to track memory which is consumed by Locked Driver. In RAMMAP tool if you see Locked driver taking huge memory its sign of VMware balooning. Take help from the team to configured/disable ballooning for the virtual machine on which SQL Server is running

Befor giving LPIM you must make sure you have set optimum value for max server memory and have left ENOUGH memory for OS to perform efficiently.

If you do not follow above two points and if OS comes under severe memory pressure due to LPIM OS processes would be paged out because it cannot force SQL Server to release memory(its locked/non pageable due to LPIM) and thus leading to tremendous slowness of OS processes.

Q: So, am I using the sys.dm_os_buffer_descriptors view correctly? From what I read it always shows used buffer/cached pages. So if it is empty (or significantly reduced), I either don't have the memory anymore, or it is empty. (I would love a way to confirm this conclusion.)

Buffer descriptors as already mentioned returns information about all the data pages that are currently in the SQL Server buffer pool. IMHO buffer pages are affected by I/O activity on server and thus indirectly related to PLE. If there is request to fetch large amount of pages from disk to memory its quite possible that SQL Server will flush datapages to disk if it finds it needs to create space in buffer pool to bring in the new pages in memory and thus decreasing the amount of data page present in memory for particular database.
So what you are seeing via sys.dm_os_buffer_descriptors is not incorrect but I would not suggest you to use Buffer descriptor DMV to gauge PLE on server. This would not be a correct approach.


Open Transaction that won't go away SPID Ns There is a transaction that has been open on this instance since it was restarted and is preventing us from clearing the ldf.

Here is the transaction information:

Transaction information for database 'MYDATABASE'.

Oldest active transaction:
    SPID (server process ID): 7s
    UID (user ID) : -1
    Name          : user_transaction
    LSN           : (54883:155655:1)
    Start time    : Jun 30 2014  5:45:35:820PM
    SID           : 0x01
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Does anyone know what 7s means, I have not yet seen a SPID with a letter in it.

Thanks,
Craig

Update 14:03

I ran the resolution suggested here and this is the result after restart:

2014-07-18 14:01:12.83 spid7s      Mise à niveau des paramètres d'abonnement et des objets système dans la base de données [MYDATABASE].
2014-07-18 14:01:13.03 spid7s      Nom d'objet 'MSreplication_subscriptions' non valide.
2014-07-18 14:01:13.03 spid7s      Error executing sp_vupgrade_replication.
2014-07-18 14:01:13.03 spid7s      Saving upgrade script status to 'SOFTWARE\Microsoft\MSSQLServer\Replication\Setup'.
2014-07-18 14:01:13.04 spid7s      Saved upgrade script status successfully.

I still have the same problem. Any ideas on how to resolve this?

Update 19/07
Problem solved by adding the  MSReplications_subscriptions table to the database where sp_vupgrade_replication was failing

I guess this is a known issue and Microsoft has released information about it in below links

Cause:

This issue occurs because the "tran_sp_MScreate_peer_tables" transaction was left open by a replication upgrade script during the upgrade. This open transaction prevents usual log truncation.

Transaction log for database is growing with system SPID as open transaction

There is also a documented support article which will help you in understanding the issue

Database transaction log continues to grow after upgrade


Cannot connect to MySQL using PHP 7 The version of MySQL is 8.0.16. This is installed on an EC2 Linux and is a fresh install. I have run the mysql_secure_installation command after installation and was able to change the root password successfully and was also able to add a second user with basic privileges.

To test the connection I made a simple script:

$link = mysqli_connect($dbhost, $dbuser, $dbpass);
    if (mysqli_connect_error() ){
        echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
    }else{
        echo "Connection successful";
    }

I have double checked the values of the variables and they are all correct. When I try to visit the URL it says:

  Debugging errno: 2054

How to resolve this?

I was able to find the answer through this thread And this is because MySQL uses an authentication plugin. You can read all the details on the forum.


Backup standby database using LVM snapshot Objective:  Backup standby database (synchronous streaming replication) using LVM snapshot.

When running pg_start_backup:

postgres=# SELECT pg_start_backup('label');
ERROR:  recovery is in progress
HINT:  WAL control functions cannot be executed during recovery.

Is it possible to copy backup_label and tablespace_map from the primary database's cluster directory into the standby database's cluster directory in order to perform the LVN snapshot backup of the standby cluster directory?

To do that on the standby server you need to configure it like a master server to enable the cascading replication[1].

  To enable it, you need to change the follow parameters:

  wal_level to archive or hot_standby
  If plan to use streamming replication:

  max_wal_senders to 2

  If you plan to use wal archiving:

  archive_mode to on
  archive_command to some place

  Then restart your standby database.

  When you configure that, you can run the pg_start_backup function without a problem.

To do what you need (backup with LVM) you must:

  PLEASE test this steps on a test machine to avoid any issues on your production database server.

run pg_start_backup:

psql -U postgres -c "SELECT pg_start_backup('my lvm backup');"

make the lvm snapshot[2] (and export it to a file[3]), eg:

## Repeat that action for each tablespace
## TODO: Review max grow size for your snapshot
lvcreate --size 10G --snapshot --name pgdata_backup_snapshot /path/to/pgdata/dir
## export the LVM to a image (with the `dd` command):
### TODO: Replace the `vg_name` by correct one
dd if=/dev/vg_name/pgdata_backup_snapshot of=/path/to/pgdata_backup.dd
## now remove the snapshot
lvremove /dev/vg_name/pgdata_backup_snapshot

run pg_stop_backup function:

psql -U postgres -c "SELECT pg_stop_backup();"

Hope it helps.

References:

https://www.postgresql.org/docs/current/static/warm-standby.html#CASCADING-REPLICATION
http://www.tecmint.com/take-snapshot-of-logical-volume-and-restore-in-lvm/
https://www.howtoforge.com/linux_lvm_snapshots_p2
http://www.tldp.org/HOWTO/LVM-HOWTO/snapshots_backup.html


While running Mongodb, getting “enabling http interface” message and stopped I am getting the following error when I run a freshly installed MongoDB:

$ mongod --directoryperdb --dbpath C:/mongodb/data/db --logpath c:/mongodb/log/mongodb.log --logappend --rest
2015-03-17T17:32:55.359+0530 ** WARNING: --rest is specified without --httpinterface
2015-03-17T17:32:55.360+0530 ** enabling http interface

Can you help me to fix it?

I am using windows 7 machine

The REST API (--rest) depends on the HTTP interface (--httpinterface) so the warning here is that the HTTP interface is being enabled for you automatically in order to support REST.

Both the REST API and the HTTP interface are strongly discouraged outside of trusted development environments as they are not secure. Starting from MongoDB 2.6, both of these options are disabled by default.

See also: Network Exposure and Security and the MongoDB Security Checklist.


How to compact mysql database size? My folder mysql/data is huge. I have dropped some databases that have a huge amount of records, removed some logs tables but.. mysql/data size remains unchanged.

What is the best way to compact it? It takes something like 20 Gigabytes on HD and contains about 30 medium or small database. My whole databases folder takes 300 Megabytes.

In addition ibdata1, mysql-bin.000332, mysql-bin.000326, mysql-bin.000206 take about 20 Gigabytes.

PS: I use Windows 7

If all tables in your database uses InnoDB then fire Optimize table table_name ....make sure

"if your table size is 100MB then double of this size must be available in datadir drive.."

If you are using MyISAM you can check with MyISAMcheck utility to optimize myisam tables...

Which MySQL version you are using? If your MySQL DB version is 5.6 or 5.7 then ibtemp will be there in datadir. Normally MySQL not able purge all temporary tables which is created by user stored in ibtemp and it grows day by day. If you are affordable downtime then take restart of MySQL service. It free disk space used by ibtemp temporary Tablespace.

Read about innodb row format you will get idea..


View collation are incomplete joins in couchdb I was trying to create view collations following this tutorial: http://elegantcode.com/2012/02/14/view-collation-in-couchdb/ but I realize that the result was an array with 5 elements with 2 objects types instead of an array with 2 items and those items with another array. Am I supposed to organize this array when I receive the result? Or is there another way of doing relationships on couchdb?

Ok, I found the solution, I have to use the reduce function for that, like:

function(keys, values, rereduce){
var result = null;
var orders = []
for(var i =0; i< values.length; i++){
if(values[i].type=='customer') result = values[i];
    else{
          orders.push(values[i])

    }
}
result.orders = orders;

return result;
}


SQL Server 2016 CTP -> RTM, safe to attach CTP DB files (including SSISDB)? I'm uninstalling an expired CTP build and installing RTM, because the CTP -> RTM upgrade path is not supported. Is there any word on:

Whether it's supported to attach user database files from CTP to RTM.
Whether it's supported to attach SSISDB from CTP to RTM.
I assume it's definitely not supported to attach other system databases from CTP to RTM.

You can safely attach any database from a prior build. As soon as you do this, the internal database structure will be automatically upgraded if there are any changes. This includes system databases, but they will not be considered system databases, using this method.

You can still retrieve SQL Server Agent jobs from the old msdb database if you need them, as well as anything you wanted from master, including things like logins.


Postgres: Explicit lock with transactions I am a beginner in SQL world and have some doubts regarding transaction/locking:

In postgres, do transaction also ensures locking of rows(preferable)/tables until it is completed?
Also, if there are two transactions through different queries, will second one wait for the first one to complete i.e. will it be serial or concurrent?

I think both points infers same but I am not sure so posting both.

In postgres, do transaction also ensures locking of rows(preferable)/tables until it is completed?

No, transactions don't ensure locking. Transactions ensure only that whatever is transacting is done atomically. That is to say, that it either all succeeds, or it all fails. How it does what it does, and what it is doing it to is totally controlled by you.

For instance, PostgreSQL currently supports three isolation levels,

Read committed
Repeatable read
Serializable

These three isolation levels either see and operate can see the data directly, or a snapshot of the data at the time transaction starts.

Only in the last mode serializable is any attempt to be made that the transaction commits in a serialized fashion. Whether or not enough of an attempt is made is another question.

All locking in PostgreSQL can be done explicitly or implicitly (the DDL command know how to lock efficiently to get the job done). Usually, explicit locks are shunned. But in same cases, including under serializable, it's the only way to be sure data has not been added since the transaction started.


Query performance and join hints, plan cost, and duration Having some trouble identifying why a query's duration would decrease when using OPTION (HASH JOIN) or OPTION (MERGE JOIN), although plan cost increases.

Background

I have a reporting database using traditional star schema (dimension/fact tables). SQL is generated by ORM in the BI tool. I have some flexibility with the generated SQL in terms of adding optimizer hints, but that's it (no query refactoring, etc.).

Problem

When the below query is executed with no optimizer hints, average duration is around 90 seconds and estimated subtree cost is around 2.9. When executed with the OPTION (MERGE JOIN) or OPTION (HASH JOIN) hints, average duration is around 3 seconds, but estimated subtree cost is around 4.9.

I have verified that statistics on affected tables are up to date using UPDATE STATISTICS <schema>.<table> WITH FULLSCAN;. Indexes have also been added based on optimizer recommendations.

Here's the query (yes, it's ugly, see ORM comment above):

SELECT  a11.trans_00_key  TRANS_00_KEY,
    a11.region_id  REGION_ID,
    Max(a15.region_cd)  REGION_CD,
    a11.state_id  STATE_ID,
    Max(a13.district_cd)  DISTRICT_CD,
    a12.cntrct_nbr  CNTRCT_NBR,
    a11.proj_nbr  PROJ_NBR,
    Max(a11.proj_nbr)  PROJ_NBR0,
    CONVERT(DATETIME, CONVERT(VARCHAR(10), (a12.sys_date_yr + '-' + a12.sys_date_mon + '-01'), 101))  CustCol_5,
    a12.proj_ctgry_nbr  PROJ_CTGRY_NBR,
    a11.type_of_work  TYPE_OF_WORK,
    a11.funct_rng  FUNCT_RNG,
    Isnull(a11.fis_id, -1)  FIS_ID,
    Max(Isnull(a14.fis_dscr, 'Blank'))  FIS_DSCR,
    CASE WHEN a12.bid_amount > 1 THEN a12.bid_amount ELSE a12.eng_est_amt END  CustCol_7,
    Sum(a11.est_amt)  WJXBFS1,
    (Sum(a11.ltd_amt) - (Sum(a11.ltd_ind_bill_cst) + Sum(a11.ltd_ind_non_bill_cst)))  WJXBFS2,
    Sum(a11.ltd_cost_cntrct)  WJXBFS3,
    ((Sum(a11.ltd_amt) - (Sum(a11.ltd_ind_bill_cst) + Sum(a11.ltd_ind_non_bill_cst))) - Sum(a11.ltd_cost_cntrct))  WJXBFS4,
    (Sum(a11.est_amt) - (Sum(a11.ltd_amt) - (Sum(a11.ltd_ind_bill_cst) + Sum(a11.ltd_ind_non_bill_cst))))  WJXBFS5
FROM    sys_trans_detail_fact   a11
    JOIN    sys_trans_hdr_fact  a12
      ON    (a11.proj_nbr = a12.proj_nbr AND
    a11.trans_00_key = a12.trans_00_key AND
    a11.state_id = a12.state_id)
    JOIN    district_lkp    a13
      ON    (a11.state_id = a13.state_id)
    JOIN    fis_lkp a14
      ON    (Isnull(a11.fis_id, -1) = Isnull(a14.fis_id, -1))
    JOIN    region_lkp  a15
      ON    (a11.region_id = a15.region_id)
WHERE   (((a11.trans_00_key)
 IN (SELECT r12.trans_00_key
    FROM    sys_trans_detail_fact   r12
    WHERE   r12.fund_src_name_id IN (3, 7, 5)))
 AND a11.fund_src_name_id IN (6, 8, 2, 3, 7, 5, 4)
 AND a11.state_id IN (8, 4, 19, 14, 20, 23, 17, 25, 16, 18, 24, 2, 12, 22, 5, 11, 6, 1, 21, 7, 15, 10, 9, 3, 13, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36)
 AND a11.status_id = 1
 AND a11.extract_dttm IN (CONVERT(DATETIME, '2012-09-01 00:00:00', 120))
 AND a11.cost_cat_id IN (10, 4))
GROUP BY    a11.trans_00_key,
    a11.region_id,
    a11.state_id,
    a12.cntrct_nbr,
    a11.proj_nbr,
    CONVERT(DATETIME, CONVERT(VARCHAR(10), (a12.sys_date_yr + '-' + a12.sys_date_mon + '-01'), 101)),
    a12.proj_ctgry_nbr,
    a11.type_of_work,
    a11.funct_rng,
    Isnull(a11.fis_id, -1),
    CASE WHEN a12.bid_amount > 1 THEN a12.bid_amount ELSE a12.eng_est_amt END

Actual execution plan with no hints

I'm confused as to why the plan shows 572 million actual rows for the index seek outlined in red.

Actual execution plan using OPTION (HASH JOIN) hint

I've read that optimizer hints are a last resort after verifying appropriate indexes have been applied and statistics are verified up-to-date. In this case, SQL Server seems to be picking the best plan based on cost, but there is a significant (about 87 second) penalty in terms of query duration. Does this sound like a case where the optimizer hint should be used? If not, what other items should I check to ensure the optimizer picks the best plan for cost and duration?

Another option to optimizer hints if you cannot even touch the query is to use Plan Guides.  However, since your query statement is dynamic with many unparameterized tokens, I doubt it's possible.

Since you know your data, I think you're better off just staying the path of query hints.  Statistics have been known to be lies, lies and damn lies.  It can only provide an overall shape of the data, but for specific conditions (e.g. fund_src_name_id values, state_id values, combination of the two etc) it can just produce the wrong plan.


pg_repack slows down PostgreSQL replication I have a master PostgreSQL 9.5 server and a standby server. For replication I use repmgr (WAL streaming). Typically the delay between master and standby is <5s:

$ psql -t -c "SELECT extract(epoch from now() - pg_last_xact_replay_timestamp());"
  0.044554

Periodically pg_repack is invoked on master in order to optimize indexes and tables. Repacking tables causes massive changes in WAL streaming and significantly slows down replication, so that the difference between master and standby could be more than 1h.

Is there a way how to reduce such delay? Is it possible to synchronize newly incoming data with higher priority than repack changes?

I was about to ask a similar question, but it is related to the replication streaming ("physical"/block related) that streams/replicates the actual data writes to the disk(s). With vacuum full (reindexes), and truncates/restores, and now pg_repack, the tables are rewritten to disk, causing a lot of data writes that need to be streamed to the other side...

Thus, no, I don't believe you'll be able to do the "prioritization" as the moment the rebuilt table are "swapped" into active table, the new updates/writes on the master, will be going to the rebuild table, not the old table, and then the replica needs that table "available"!

I've been getting into the habit of killing the replication, doing the major data changes (perhaps a good practise to have it as a "backup" available before the data changes) and then doing a new full pg_basebackup/replication restarts

Hope this helps to explain the situation you are in and how I've been solving it till now :)

That said do go read: https://www.depesz.com/2013/06/21/bloat-removal-by-tuples-moving/

Depesz explains a mechanism that helped him move data to the beginning of the table "on the fly" with data available all the time using code similar to:

with x as (
delete from test where id in (999997,999998,999999) returning *
)
insert into test
    select * from x;

this is then run in batches, with $vacuum$ statements running together to clean up the space. Doing this in a slow/managed method, you could be able to do the "repack" with not too far behind replicas.

Just be careful of triggers on update/insert/delete !


Are these relations in BCNF? I have a homework question that I am trying to understand but having a tough time with this question. Any explanation or even pointers would be very appreciated (no exact answer expected - I am trying to learn - but any help would be great).

I have two relations with a set of explicitly defined constraints.

D = {R1:CDE, R2:FGH}  Constraints = {R1: CD->E, R2:key(G), R1[DE] subset R2[FG]}

Are the two relations (D={R1 and R2}) in BCNF?

Thank you for any guidance you can provide.

A relation is in BCNF if every determinant of the non-trivial dependencies is a superkey.

If G is the key of R2, we know that G → F and G → H.

Since DE in R1 is a subset of FG in R2, and since G → F, we can easily prove that E → D in R1.

If no other constraint holds on R1, then we can calculate the candidate keys of R1 starting from the two dependencies CD → E and E → D, and see if R1 is in BCNF or not.

Finally note that, if no other constraint is known on R2 a part from the key, it is very easy to decide if it in BCNF or not.


Binlog has bad magic number I keep getting this error whenever I start MySQL.

121028  1:38:55 [Note] Plugin 'FEDERATED' is disabled.
121028  1:38:55 InnoDB: The InnoDB memory heap is disabled
121028  1:38:55 InnoDB: Mutexes and rw_locks use Windows interlocked functions
121028  1:38:56 InnoDB: Compressed tables use zlib 1.2.3
121028  1:38:56 InnoDB: Initializing buffer pool, size = 16.0M
121028  1:38:56 InnoDB: Completed initialization of buffer pool
121028  1:38:56 InnoDB: highest supported file format is Barracuda.
121028  1:38:57  InnoDB: Waiting for the background threads to start
121028  1:38:58 InnoDB: 1.1.8 started; log sequence number 3137114
121028  1:38:58 [ERROR] Binlog has bad magic number;  It's not a binary log file that can be used by this version of MySQL
121028  1:38:58 [ERROR] Can't init tc log
121028  1:38:58 [ERROR] Aborting

121028  1:38:58  InnoDB: Starting shutdown...
121028  1:38:58  InnoDB: Shutdown completed; log sequence number 3137114
121028  1:38:58 [Note] C:\PROGRA~2\EASYPH~1.1\MySql\bin\mysqld.exe: Shutdown complete

I have already tried this.

I have an EasyPHP 12.1 setup on Windows 7x64 PC.

Binary logs have different start positions upon the creation of a new binary log. In fact, a binary log position actual represents the numbers of bytes written in the binary at the end of the most recent SQL statement recorded.

The following is the list of binary log starting positions for each major release of MySQL 5

For MySQL 5.5 : 120
For MySQL 5.5 : 107
For MySQL 5.1 : 106
For MySQL 5.0 : 98

Included in these initial bytes is a static value called the magic number, a base-64 value. I wrote about this back on Dec 26, 2011 : mysql emits BINLOG rows even though binlog_format=STATEMENT

Although the BINLOG magic number is always in the same position, you cannot use binary logs from one version to newer versions under certain circumstances. For example, binary logging events could change and not be understood. Row-based binary logging and MySQL Replication can break on a MySQL 5.6 Slave replicating from an older master when handling DATETIME values. I wrote about this recently

Jun 18, 2015 : mysql master slave replication scenario
Jan 02, 2015 : mysql replication master 5.5 slave 5.1 error on create database

There is also the possibility of a corrupt binary log. I wrote about this on Feb 04, 2011 : MySQL master binlog corruption

Your solution is to

net stop mysql
Erase binary logs (and the binary log index file)

IF you do not know where they are, locate my.ini on your PC
Open my.ini in Notepad
look for the option log-bin or log_bin
look for the option datadir
If log-bin only has a filename, look inside the folder specified by datadir
If log-bin includes a path and a filename, look inside the folder specified by log-bin
Open the desired folder in Windows Expolrer
Remove the binary logs
There should be a file whose file extension is .index. Delete this as well

net start mysql

Please DO NOT ERASE ib_logfile0 or ib_logfile1 when you have binary log issues.


Use column info as template I got this wild idea to fix a date format issue i've been having.

The idea is to use column info to act as a template for the date format since mm/dd/yy can get really confusing for the database since I always use YYYY-mm-dd but my users like to mix things around.

The thing is that I have some trouble to figure out exactly how the SQL would look like.

My idea is something like this:

Tablename: USER
Col1, Col2, Col3, date_template

And the other info:

Tablename: activity
item, quantity, price, date, time

Yes, I know that it could be fixed in the code of the page but since I'm not the creator of the site I won't go into fiddle around with the code until I got some time to make a new page.

I think you will have an easier time to simply cast whatever the user throws at you to a date format that you want, to keep it all uniform. E.g. in PHP like this: date('Y-m-d', strtotime($userdate))

It can also be done in sql with convert(date, '28/09/2016'), however I think you need to specify the format. Link