How query errors affect Postgresql DB performance I have an application stored measurements data in PostgreSQL-12.
Couple of processes write data by queries like:

INSERT INTO "<measurement>" (<columns>) VALUES (<values>);

But for some reason the "<measurement>" table was not created, and I got errors like:

ERROR:  relation "<measurement>" does not exist at character 13

The question is: Does massive errors count affect the DB performance?

Does massive errors count affect the DB performance?

Not really.

It will make the error log [a bit] larger, because each error gets written there, but that should be trivial in size compared to the database itself.  Each of those writes to the log will take [some] time, but not a great deal.

What will get noticed is that the Application trying to execute these statements is failing to do so.
Each of those failures is losing company Data.
That matters.

At some point, the Development team for the Application will get involved and they may well start throwing rocks in your direction because there's a table missing from the database.  Be prepared for the question: "Whose responsibility was it to create that table in the first place?"


The operating system returned error 665(The requested operation could not be completed due to a file system limitation) Have a SQL Server 2014 instance running on Windows 2012 R2 that weekly runs Ola Hallengren's DB integrity scripts and the last two weeks started getting this error:

DESCRIPTION:    The operating system returned error 665(The requested operation could
not be completed due to a file system limitation) to SQL Server during a write at
offset 0x000036ec240000 in file 'E:\DBFiles\XXXXX.mdf_MSSQL_DBCC42'. Additional
messages in the SQL Server error log and system event log may provide more detail. This
is a severe system-level error condition that threatens database integrity and must be
corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This
error can be caused by many factors; for more information, see SQL Server Books Online.

I checked both the event log and the SQL Server error log but not getting a lot of additional info that could help.   I'm rerunning the DBCC check for this db as I did last week and it cleared out the MSSQL_DBCC42 file and didn't find any corruption.

Any other places I could find more info other than event log and SQL Server error log?   Should I ask our storage group to see if something shows up on reports for the SAN or is this purely SQL related due to some contention?

This database is pretty active as its replicating the transactions from our JDE financials and manufacturing environment on an iSeries, and currently is about 300gb.   Each night the database is backed up and restored to a development server.  Would it be better for me to run DBCC check against that copy of the database? This DB is a source for building SSAS multi-dimensional cubes for previous day reporting so I would have the option of a complete rebuild of the data or trying to recover from a backup if it became corrupt etc.

Appreciate any thoughts on what could cause this error or the best option for running DBCC checkdb based on the situation.

DBCC uses snapshots internally. Snapshots are then implemented as sparse files in Windows.

So this is actually a problem with Windows' handling of sparse files, which causes the snapshots used by DBCC to occasionally break on large and very active databases. It's reasonably well documented with a few recommended fixes in http://support.microsoft.com/kb/2002606

However, if you can DBCC on the dev server I'd recommend that instead. (I've personally only seen this problem with databases that were still serving traffic while being DBCC'd/snapshotted. Backup/restore to an idle server, DBCC there => no more 665 errors.)


Unable to apply order by clause on distinct select in Oracle The query is:

select distinct( Name )
from Students
where marks>75
order by Substr(Name,-3,3),ID asc;

This shows an error message:

order by Substr(Name,-3,3),ID asc
*
ERROR at line 4:
ORA-01791: not a SELECTed expression

You can't order by columns that aren't selected when using DISTINCT, or their alias.

To keep your order with a substring and another column, you will have to calculate them first (without DISTINCT), then just display the name while ordering by the other columns behind the curtain.

SELECT
    F.name
FROM
    (
        SELECT
            S.name name,
            Substr(S.name, -3, 3) substrName,
            MAX(S.ID) ID
        FROM
            Students S
        WHERE
            S.marks > 75
        GROUP BY
            S.name
    ) F
ORDER BY
    substrName,
    ID ASC


How to reinsert corrected rows from the conflict table? I have a bidirectional merge replication. I had failure constraints because the primary key was just integer. I change the primary key to the old primary key + a location identifier.The problem is how can I reinsert the old rows of conflict table ( that I can correct manually from MSmerge_conflict_) to the publishers and subscribers.
can you help me please?

sorry for making faults, I'm not english speaker

You can use the Conflict Viewer to submit the winning or losing row.


MySQL 5.1.x: Performance improvement in Enterprise edition Vs community edition My application is running on MySQL 5.1.58 community edition.
I tried to search enterprise edition of MySQL 5.1.X but only MySQL 5.6.X version of enterprise edition is available.
Is it possible to download MySQL 5.1.x enterprise edition - trial version? I want to compare the performance testing with the community edition of MySQL 5.1.X

I don't think you can get a MySQL Enterprise edition for v5.1.X version.

You can see the diffrence in performance between Community and Enterprise version here:

http://www.mysql.com/products/enterprise/scalability.html

You can also download Enterprise product guide whitepaper to know more details.

In summary additional features in MySQL Enterprise version are MySQL Thread Pool, MySQL Enterprise audit and a Technical support for your server related issues.


Where to put glogon.sql in 32-bit ODAC (11.2.0.2.1)? I installed the 32-bit  ODAC (11.2.0.2.1) from here. The  Oracle Universal Installer version contains Oracle SQL*Plus 11.2.0.2.0. But I'm missing the sqlplus/admin/ folder in the installation, where I usually put the glogin.sql file.

Edit:

This install is on a new Windows 7 - 64 bit Notebook, which is my first 64-bit Windows system.

The initial idea is not to install any Oracle server there, but I guess I'll change my mind about this during the first 2 month.

I find sqlplus.exe in the folder D:\app\berndk\product\11.2.0\client_1.

BTW This install has no sqlplus subfolder and the ORACLE_HOME environment varaiable is not set. As I do not need ODBC that install generally works quite well.

Thanks to the link provided by Jack Douglas, I found the solution.

I needed to create a login.sql file in the same folder as sqlplus.exe and I had to set the SQLPATH environment variable to this path.

From the article I got the impression, that setting the variable was not necessary.


Get the unmatched user clockin vs user table The user table contains users' information. The clock_in table contains the clockin of the users every day. I have been trying to check who is absent or not yet clocked in.

For example by 9:00 am, I should be able to check who has not clocked in yet. I have came up with the MySQL query and this what I have come up with so far after about 7 days now searching. The idea is to compare the user table to the users clockin today, if there is an unmatched (IS NULL) then that user has not clocked in today. Here is my updated query so far.

SELECT users.*
FROM users
LEFT OUTER JOIN timecard
  ON users.id = timecard.employment_id
AND timecard.date='".$today."'
WHERE timecard.employment_id IS NULL and user.location = '".$location."'
ORDER BY users.dept DESC

Please, I know Left Outer Join may be the right one according to the results of my search, need your advice

The best solution is something like this:

CREATE TABLE personnel (id INTEGER, name VARCHAR(30));

populate it:

INSERT INTO personnel VALUES (1, 'Joe'), (2, 'Mary'), (3, 'Bill'), (4, 'Fred');

Create and populate the timecard table:

CREATE TABLE timecard (empid INTEGER, clockin_time DATETIME);

INSERT INTO timecard VALUES (1, '2019-08-28 08:00:18');
INSERT INTO timecard VALUES (2, '2019-08-28 08:00:28');
INSERT INTO timecard VALUES (3, '2019-08-28 08:00:28');
INSERT INTO timecard VALUES (1, '2019-08-29 08:00:03');
INSERT INTO timecard VALUES (4, '2019-08-29 08:04:04');

Note that only Joe and Fred have clocked in on the 29th of August - Mary and Joe haven't.

You can find the fiddle here.

So, first run this query to determine who clocked in on the 29th.

SELECT p.id AS the_id -- , p.name, t.empid -- remove -- to see results - shows thought process!
FROM timecard t
LEFT JOIN personnel p
  ON t.empid = p.id
WHERE DATE(t.clockin_time) = '2019-08-29'
ORDER BY p.id, t.clockin_time;

Result:

the_id
     1
     4

So, we see that Joe (id 1) and Fred (id 4) have clocked in.

Now, we need to find out the identities of those who didn't clock in. This is the list of people whose id's are not in the result of the first query. We obtain this by doing the following:

SELECT * FROM personnel pl
WHERE pl.id NOT IN
(
  SELECT p.id AS the_id
  FROM timecard t
  LEFT JOIN personnel p
    ON t.empid = p.id
  WHERE DATE(t.clockin_time) = '2019-08-29'
  ORDER BY p.id, t.clockin_time
);

Result:

id  name
 2  Mary
 3  Bill

Something like this is probably what you require - if not, let me know. This construction allows you to do tallies of how many times person with id x was absent in a given month, day, using BETWEEN and lots more.... fun for all the family! :-)


TSQL LDAP query via Linked Server I have strange problem, I have 3 SQL Server 2012 with Sp1 instances, and on all of them, the versions are identical.

I am trying to query Active Directory services, on 2 of them this is working correctly, but one of them is throwing an error

  Cannot get the column information from OLE DB provider "ADsDSOObject" for linked server "ADSI_TEST".

If I don't use Active Directory Server Name in the query it works fine.

Do you have any idea?

to query the active directory I use the procedure xp_logininfo

  Requires membership in the sysadmin fixed server role or membership in
  the public fixed database role in the master database with EXECUTE
  permission granted.

I have been using the following script and it works fine for me:

SELECT @@SERVERNAME
--myserver

DECLARE @NTLogin nvarchar(128);
SET @NTLogin = 'mycompany\person_or_group_I_want_to_check'

DECLARE @UserList TABLE (
         [Account Name] nvarchar(128),
         [Type] nvarchar(128),
         [Privilege] nvarchar(128),
         [Mapped Login Name] nvarchar(128),
         [Permission Path] nvarchar(128) )

INSERT INTO @UserList EXEC xp_logininfo @NTLogin, 'all' --insert group information

IF EXISTS (SELECT * FROM @UserList WHERE [Type] = 'group') --only if it's a group
   INSERT INTO @UserList EXEC xp_logininfo @NTLogin, 'members' --insert member information

SELECT * FROM @UserList


EXCEPT operator query to differentiate between new and changed rows I am using the EXCEPT Operator to compare data between two tables (as part of a final import I want to compare "today's data" with "yesterday's data" and only finally import new and changed rows.)

It (EXCEPT) seems to be exactly what I need, but does anyone know a way I could extend it so that in the resulting data subset I was able to differentiate between rows that are new and rows that are changed?

You could use the technique from this post.

--Demo setup
set nocount on
drop table if exists #T1
drop table if exists #T2
Create table #T1 (Id int, OtherData varchar(10))
Create table #T2 (Id int, OtherData varchar(10))

insert into #T1(Id,OtherData) values(1,'NewRow')
insert into #T1(Id,OtherData) values(2,'ChgRowB')

insert into #T2(Id,OtherData) values(2,'ChgRowA')

-----------------------------------------------
--The actual query
-----------------------------------------------

-- NEW
SELECT A.*, 'NEW'  AS 'CHANGE_TYPE'
FROM #T1  A
LEFT JOIN #T2  B ON B.ID = A.ID
WHERE B.ID IS NULL
UNION
-- MODIFIED
SELECT B.*, 'MODIFIED'  AS 'CHANGE_TYPE'
FROM (
        SELECT * FROM #T1
        EXCEPT
        SELECT * FROM #T2
    ) S1
INNER JOIN #T2  B ON S1.ID = B.ID;

| Id | OtherData | CHANGE_TYPE |
|----|-----------|-------------|
| 1  | NewRow    | NEW         |
| 2  | ChgRowA   | MODIFIED    |


Oracle linked server invalid table name i have created a linked server from sql server and i am now trying to access the table in oracle using the following trigger to insert data into oracle. i cannot refer the table name in oracle. getting the intellisense as invalid tablename.
CREATE TRIGGER ifs_limss  ON  Culligan_Dev.dbo.C_LIMS_IFST   AFTER INSERT AS
BEGIN

SET NOCOUNT ON;

INSERT INTO IFS.IFSAPP.C_LIMS_IFS
    (id, [name])

SELECT I.id, I.[name]
FROM INSERTED AS I

-- Insert statements for trigger here

END
GO

With a linked server reference you must use the four part name format, for instance SELECT * FROM server.database.schema.object. In your example it will be looking for an object called IFSAPP.C_LIMS_IFS in a local database called IFS. If that is your database name then you need to prepend the server name, if that is the linked server name then you need to insert the database name after it.
In some linking schemes you attach directly to one database not the whole server, in which case you can leave the database to default, but you must give all four parts just with that one blank, perhaps IFS..IFSAPP.C_LIMS_IFS is what you need.
To get better help you need to edit some further detail into your question:

What process did you follow to link the servers? Perhaps include the SQL server statements, with authentication detail removed of course, or a screenshot of you used a GUI.

What are the server, databases, and schema names, in Oracle, and the name you have to the looked server in SQL Server? (I've tried to guess a bit from your current SQL statements but if they are wrong my guess may be). Including the connection details mentioned above may implicitly include this information.

Also, I'm not sure I would expect intellisense to work well over linked servers so perhaps be wary of relying on that.


Query to take partial backup in MYSQL I tried to take full backup in mysql using enterprise backup by using the command

    mysqlbackup -u root -p --backup-dir=g:\full_bck backup

But I could not take backup of a single database , I tried it using --include database_name.

Please help me to sort out this.

The syntax for taking MySQL dump:

-- Dump all databases and all tables (option "-A" is similar to "--all-databases")
mysqldump -u [username] -p[user_password] -A > [dump_filename]
-- Example, please note (NO SPACE between -p and your password)
mysqldump -u root -pmypass -A > full_dumb.sql

-- Dump single database
mysqldump -u [username] -p[user_password] [database_name] > [dump_filename]
-- Example
mysqldump -u root -pmypass dbshopping > dbshopping_dumb.sql

UPDATE:

-- MySQL Enterprise Backup Syntax
mysqlbackup --user[username] --password=[user_password] --backup-dir=[backup_directory] --databases=[database_list] backup

-- Example Command
mysqlbackup --user=root --password=mypass --backup-dir=G:\mysql-enterprise-backup-directory --databases=dbshopping backup

I'm not an expert in 'MySQL Enterprise Backup', yet It worked fine for me...

  You can also verify backup history and/or progress from 'mysql'
  database

SELECT * FROM mysql.backup_progress;
SELECT * FROM mysql.backup_history;

Hope this may help you...


Low PLE and data warehouse Let's imagine : I have several instances of an application, spread into several servers, each instance has a database and data of each database is regularly extracted and stored in a central data warehouse. Sounds like a classic stuff.

This DW size is ~1 To, all the tables are partitioned (monthly) on the creation date of the records.
The DB server has 32 Go of RAM allocated to SQLServer (2014, enterprise).

The thing is that the PLE of this DB server is always low. Low meaning less than 120 seconds.
I know that evaluating PLE "good" value is a complex topic (here or there) and should be correlated with other indicators, but I have always been told that the higher the PLE is, the better it is (for performance, stability, etc...).

The process that extracts data from production instances to the DW runs every 5 minutes.
This process can affect thousands of records, not necessarily the most recent ones (for instance a customer A is created, customer B that was created 6 months ago is updated, etc...). So each time SQLServer will have to access different pages. And therefore PLE will drop.
So, as the process is designed currently, the low PLE seems normal to me.
And actually maybe it's not a so big deal because all other processes using this DW (reporting, aggregation and all) are stable and performant.

So, questions:

is my understanding correct and low PLE normal in this context ?
should I stop worrying and tell the supervision guy not to shout every time Nagios says the PLE is too low?
or should I stop asking dumb questions and ask for more CPU / RAM ?

It can be normal for Page Life Expectancy (PLE) on SQL Servers in a Data Warehouse setup with frequent reloads to jump up and down.

Why?

Well which data will users normally query? It's the data that has been most recently inserted, because that is the data that is interesting. Unless you are performing analysis over long periods of time and conducting forecasts based on last years data.

Page Handling in Memory (Buffer Cache)

Microsoft SQL Server has an internal counter in each page that is stored in memory. This counter starts at four and is deducted by one, each time the SQL Server database engine looks at the page, when no reads occurred.

Under this algorithm, there are “Clock Hands” that sweep the cache at regular intervals. Every time the clock hand steps on a not-in-use entry, it decreases cost by some amount. If the entry is not in use and its cost is zero, the clock hand makes the entry invisible and then attempts to remove it from the cache. In fact, Lifetime of an entry is managed by an embedded reference count in the Clock Entry Info class. After this count goes to 0, an entry is destroyed.

(Reference: An in-depth look at SQL Server Memory–Part 2)

In a Data Warehouse setup each time you load new data and each time the users request this new data it has to be read from disk into memory (buffer cache). This results in all pages that have a zero in the internal counter to be removed from the buffer cache.

Page Life Expectancy

Removing data pages from the buffer cache and moving new data from disk into the buffer cache, results in the Page Life Expectancy value to fall below the recommended threshold of 300, because the new pages initially have a PLE of zero (0). The sum of all new pages in memory (PLE=0) and all old pages in memory (PLE > 300) can fall below 300.

A PLE of 300 is not much of a recommendation nowadays:

A PLE of 300 means your entire buffer pool is being effectively flushed and re-read every five minutes. When the threshold guidance for PLE of 300 was first given by Microsoft, around 2005/2006, that number may have made more sense as the average amount of memory on a server was much lower.
Nowadays, where servers routinely have 64GB, 128GB, and higher amounts of memory, having roughly that much data being read from disk every five minutes would likely be the cause of a crippling performance issue...

Reference: Knee-Jerk PerfMon Counters : Page Life Expectancy)

Your SQL Server is performing as designed, given the current hardware

More Hardware?

Now if your users are experiencing performance issues when retrieving data, then you might benefit from more memory, because SQL Server will then be able to store more data in the Buffer Cache without having to purge all of the data when new data is loaded. Your PLE counter will probably still fall when new data is stored in your DW database, but it might not fall so dramatically.

Baseline

You will have to monitor your system and have a baseline that shows you the normal behaviour. Anything outside the norm is then worth an analysis.

5 Reasons You Must Start Capturing Baseline Data

  Baselines are a good idea. Every DBA that I’ve spoken with seems to understand the value: So why is it that so many companies do not have baseline data? I think there are two factors at play. The first is the startup cost, and by cost I really mean time and effort. If you’re going to roll your own method for capturing baselines, you have to put some thought into it, answering the questions:
     - What data are you going to capture?
     - When are you going to capture data?
     - What methods will you use to get the data?
     - Where are you going to store it?
     - How are you going to report on it?
     - How are you going to manage the data long term?

Back to Basics: Capturing Baselines on Production SQL Servers

Once you know your baseline, you know what values for PLE are good for your DW SQL Server.


How to install Oracle 10g Express and 11g Enterprise on same machine I have to continue maintaining an Oracle 8i database that receives information (about 20 Inserts and Updates a day) from a 9i database.  I am upgrading the 9i database to 11g which does not support database links to 8i.

The plan is to link from Oracle 11g to Oracle 10 XE and then to the 8i and hope that one day I won't have to do this.

I have already found articles noting that installing XE after any other version is not recommended.

Has anyone installed XE and another Oracle version side by side?  What order was used and what problems were encountered?

Edit: @George3 some forum posts indicate that you can use JDBC drivers to connect 11g and 8i.  Please add some detail and post it as answer.

It appears that you don't need to worry too much.

Install XE first.  You don't get to choose the database name (XE) or the port (1521).
Next, install 11g to the default app folder.
Modify the TNSNAMES.ora files for both databases so they know about each other.  Test connections and create database links and you are good to go.


How to implement database schema for tracking the status of a purchase? It's an internet stop, physical products with delivery.

I'm trying to figure out how to implement transactions/payments, fulfilment, shipping, payments, refunds, etc.

Namely, a status of each sale.

Approach #1:

create a column "status" in "sales". It could the following values:

new,
payment_pending,
paid,
fulfilment_started,
fulfilled,
shipping_started,
shipping_in_progress,
delivered_successfully

It may also contain:

refund_claimed,
refund_in_progress,
refunded.

This isn't the complete list of statuses

Approach #2:

create tables for each process:

payment_logs (id, sales_id, status, details)
shipping_logs (id, sales_id, name, status, details)
refund_logs (id, sales_id, status, details)

The approach #2 is more flexible. Yet, there's a downside: how would I know the current global status of a sale?
  Is it being delivered?

Or Is it still being fulfilled?

Or is a payment pending for it still? That is, it's not even proceeded to fulfilment, nor delivery procedures.

To solve that, I'd have to introduce logic to this approach: search in "payment_logs".
Found, successful? ->  proceed to "fulfilment_logs". Found, but not yet "successfully_fulfilled"? Then the global status of a "sale" will be whatever the current status in "fulfilment_logs" is.

Will this, the approach #2, work nicely? Are there any other downsides?

What would you propose?

I had a document approval and publishing module that tracked reports through their status from writing, approval, revisions, and finally publish. The way we tracked it was to have a table with the document id, current status, and a timestamp. Any time the status would change it would update that document's record with the datetime it happened. The only thing to be aware of was that we did not store historical information, so when a report was published we couldn't look back to see when it was originally written and approved by the supervisor.


SQL Server 2005 uses 1GB of RAM Is this normal? To be exact it uses 1,068,000 KB. It occasionally shoots up to 1,400,000 if query takes too long.

My total system memory is 3GB and I am running WinXP. Is there a specific amount of RAM recommended for SQL Servers?

The reason I am asking this question, could our database have problem, if it uses this much RAM? We dont have a DBA here, just a programmer. Also

Does running multiple instances of SQL Servers affect performance?
Does running multiple databases hit peformance? We use only 1 but there are 5 databases running (old ones)

I am using SQL Server 2005 Express. It is not a huge database, just about 40,000 records

SQL Server 2005 Express Edition is limited to 1GB of RAM for the buffer pool. Non-express editions, by default, will not be limited unless configured to set a max. In either case, the usage will typically not decrease unless memory pressure forces it, or the service is restarted.

Multiple instances will definitely impact performance, especially if their memory allocations exceed physical memory, and the I/O contention can slow things down for concurrent queries.
Inactive databases will have no impact, other than the disk space they consume.


Unable to get the latest value added to Mysql table based on posted time SELECT * FROM discussion_comments GROUP BY disc_id ORDER BY posted_date DESC

I have table example like given below:

CREATE TABLE example
(
id int(11),
cname varchar(11),
posted_date date,
posted_time varchar(20)
);

with values like:

INSERT INTO example
VALUES (1,'abc','2015-03-26','04:25 PM');

INSERT INTO example
VALUES (1,'def','2015-03-27','04:30 PM');

INSERT INTO example
VALUES (2,'ghi','2015-03-11','02:25 AM');

INSERT INTO example
VALUES (2,'jkl','2015-03-15','12:25 PM');

and I am trying to get only the latest value added to the table for an id based on posted_date & posted_time fields.

The result I am trying to achieve is:

(1,'def','2015-03-27','04:30 PM')
(2,'jkl','2015-03-15','12:25 PM')

The query I tried is as follows:

SELECT * FROM `example GROUP BY id ORDER BY posted_date DESC

I am not getting the desired result. Where did I go wrong??

Your first HUGE mistake (as @ypercube pointed out) is not using a DATETIME type for a date-time type variable. TIMEs are not VARCHARs and doing this will mess up your queries, make your app non-portable and will confuse the optimiser.

The other HUGE problem is MySQL. It doesn't perform this sort of query properly. Your query is ambiguous - PostgreSQL for example will throw an error if you try and run such a query.

For example

mysql> SELECT id, MAX(posted_date) FROM example GROUP BY id;
+------+------------------+
| id   | MAX(posted_date) |
+------+------------------+
|    1 | 2015-03-27       |
|    2 | 2015-03-15       |
+------+------------------+
2 rows in set (0.00 sec)

mysql>

is unambiguous and gives the correct answer.But, because you have tried to use a SELECT * FROM example, the MySQL engine doesn't know which values to return and (stupidly enough) appears (at least in the several examples of this problem that I have seen) to return the correct unambiguous values, but the other are random - so you could get id = 1 (correct) with cname = 'ghi' (incorrect).

Check out MySQL's SQL modes - there's one (ONLY_FULL_GROUP_BY) that disallows this behaviour. It should be switched on at ALL times.

To get your query to work properly, for starters, use a DATETIME type (makes the subquery below easier) and then do a subquery for cname where your DATETIME value matches that for the correct cname. HTH,

[EDIT]

Using this schema and data, your query becomes relatively easy.

CREATE TABLE example
(
id int(11),
cname varchar(10),
posted_datetime datetime
);

INSERT INTO example
VALUES (1, 'abc', '2015-03-26 04:25');

INSERT INTO example
VALUES (1, 'def', '2015-03-27 16:30');

INSERT INTO example
VALUES (2, 'ghi', '2015-03-11 02:25');

INSERT INTO example
VALUES (2, 'jkl', '2015-03-15 12:25');

And your working query is:

SELECT e.id, e.cname, e.posted_datetime
FROM example e
  JOIN
  (
    SELECT id, MAX(posted_datetime) AS posted_datetime
    FROM example
    GROUP BY id
  ) m
  ON  m.id = e.id
  AND m.posted_datetime = e.posted_datetime ;

or check it out here: SQLfiddle


T-SQL query to find Primary Management Server for SCOM Agents I am trying to replicate the function of a script which uses PowerShell to get info about SCOM agents. I can get everything I am trying to do for the moment except get the Primary Management Server for each agent, so the info that is returned by the PrimaryManagementServerName property when you run the Get-SCOMAgent cmdlet.

I've searched the tables/views etc in the OperationsManager database but have hit a wall, has anyone else tried to do this via T-SQL and been successful? I also tried running a trace on my login while using the SCOM console and selecting the Managed Agents view, but the console isn't passing through my credentials.

In case anyone else is interested, I found a query that seems to do the trick finally. The query was found here

SELECT SourceBME.DisplayName as Agent,TargetBME.DisplayName as PrimaryManagementServer
FROM Relationship  R WITH  (NOLOCK)
JOIN BaseManagedEntity SourceBME ON  R.SourceEntityID  = SourceBME.BaseManagedEntityID
JOIN BaseManagedEntity TargetBME ON  R.TargetEntityID  = TargetBME.BaseManagedEntityID
WHERE  R.RelationshipTypeId  = dbo.fn_ManagedTypeId_MicrosoftSystemCenterHealthServiceCommunication()
AND SourceBME.DisplayName not in (select DisplayName from dbo.ManagedEntityGenericView WITH  (NOLOCK)
where MonitoringClassId  in (select ManagedTypeId from dbo.ManagedType WITH  (NOLOCK)
where TypeName  = 'Microsoft.SystemCenter.GatewayManagementServer') and IsDeleted  ='0')
AND SourceBME.DisplayName not in (select DisplayName from dbo.ManagedEntityGenericView WITH  (NOLOCK)
where MonitoringClassId  in (select ManagedTypeId from dbo.ManagedType WITH  (NOLOCK)
where TypeName  = 'Microsoft.SystemCenter.ManagementServer') and IsDeleted  ='0')
AND  R.IsDeleted  = '0'
ORDER BY [PrimaryManagementServer],[Agent]


Under load, the following job-queue design does not work reliably, why? I have a job queue table which is meant to mark-and-return one job at a time to the caller. The stored procedure works like this:

update job_queue
set status = "WORKING", job_id = <arg_unique_job_id>
where status = "NEW"
limit 1
;

if row_count() > 0 then

    select
        -- get job details for work
    from job_queue
    where job_id = <arg_unique_job_id>
    ;

else

    -- nothing to do
    select 1 from dual where false;

end if;

The client generates a unique number (arg_unique_job_id) and passes it to the stored procedure. If there is any work to be done, that work will be marked as "WORKING" and get assigned the caller's job ID.

On occasion, under heavy DB load, I would find a job marked as WORKING but the client did not appear to get any row back, as if row_count "lied". Above statements are executed in one transaction, against InnoDB table (MySQL 5.6.39). Am I wrong in my assumption how above should work, or the error is likely elsewhere?

In similar situation I use:

DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN END;

REPEAT

    UPDATE job_queue
    SET status = "WORKING", job_id = <arg_unique_job_id>
    WHERE status = "NEW"
    LIMIT 1;

    SELECT *
    FROM job_queue
    WHERE job_id = <arg_unique_job_id>;

UNTIL row_count() < 1 END REPEAT;

It's never gone wrong before.

PS. This construction was used in the case when a record free for reservation exists with guarantee. If not use some local variable (some @loopcount) for to limit the max amount of cycles to avoid infinite loop.


How do I efficiently get "the most recent corresponding row"? I have a query pattern that must be very common, but I don't know how to write an efficient query for it.  I want to look up the rows of a table that correspond to "the most recent date not after" the rows of another table.

I have a table, inventory say, which represents the inventory I hold on a certain day.

date       | good | quantity
------------------------------
2013-08-09 | egg  | 5
2013-08-09 | pear | 7
2013-08-02 | egg  | 1
2013-08-02 | pear | 2

and a table, "price" say, which holds the price of a good on a given day

date       | good | price
--------------------------
2013-08-07 | egg  | 120
2013-08-06 | pear | 200
2013-08-01 | egg  | 110
2013-07-30 | pear | 220

How can I efficiently get the "most recent" price for each row of the inventory table, i.e.

date       | pricing date | good | quantity | price
----------------------------------------------------
2013-08-09 | 2013-08-07   | egg  | 5        | 120
2013-08-09 | 2013-08-06   | pear | 7        | 200
2013-08-02 | 2013-08-01   | egg  | 1        | 110
2013-08-02 | 2013-07-30   | pear | 2        | 220

I know one way of doing this:

select inventory.date, max(price.date) as pricing_date, good
from inventory, price
where inventory.date >= price.date
and inventory.good = price.good
group by inventory.date, good

and then join this query again to inventory. For large tables even doing the first query (without joining again to inventory) is very slow.  However, the same problem is quickly solved if I simply use my programming language to issue one max(price.date) ... where price.date <= date_of_interest ... order by price.date desc limit 1 query for each date_of_interest from the inventory table, so I know there is no computational impediment.  I would, however, prefer to solve the whole problem with a single SQL query, because it would allow me to do further SQL processing on the result of the query.

Is there a standard way to do this efficiently? It feels like it must come up often and that there should be a way to write a fast query for it.

I'm using Postgres, but an SQL-generic answer would be appreciated.

It very much depends on circumstances and exact requirements. Consider my comment.
Simple solution
With DISTINCT ON in Postgres:
SELECT DISTINCT ON (i.good, i.the_date)
       i.the_date, p.the_date AS pricing_date, i.good, p.price
FROM   inventory  i
LEFT   JOIN price p ON i.good = p.good AND i.the_date >= p.the_date
ORDER  BY i.good, i.the_date, p.the_date DESC;

Returned rows are ordered. See:

Select first row in each GROUP BY group?

Or with NOT EXISTS in standard SQL (works with every RDBMS I know):
SELECT i.the_date, p.the_date AS pricing_date, i.good, i.quantity, p.price
FROM   inventory  i
LEFT   JOIN price p ON p.good = i.good AND p.the_date <= i.the_date
WHERE  NOT EXISTS (
   SELECT FROM price p1
   WHERE  p1.good = p.good
   AND    p1.the_date <= i.the_date
   AND    p1.the_date >  p.the_date
   );

Same result, but with arbitrary sort order - unless you add ORDER BY.
Depending on data distribution, exact requirements and indices, either one of these may be faster. See:

How do I (or can I) SELECT DISTINCT on multiple columns?

With only few rows per good, DISTINCT ON is typically faster and you get a sorted result on top of it. But for certain cases other query techniques are (much) faster, yet. See below.
Solutions with subqueries to compute max / min values are typically slower. Variants with CTEs are generally slower, yet. (CTEs improved with Postgres 12.)
Plain views (like proposed by another answer) do not help performance at all in Postgres.
db<>fiddle here
Old sqlfiddle
Proper solution
Strings and collation
First of all, your table layout is a sub-optimal. It may seem trivial, but normalizing your schema can go a long way.
Sorting by character types (text, varchar, ...) is done according to current COLLATION. Typically, your DB would use some local set of rules, like in my case: de_AT.UTF-8. Find out with:
SHOW lc_collate;

This makes sorting and index look-ups slower. The longer your strings (names of goods) the worse. If you do not actually care for collation rules in your output (or the sort order), this can be faster with COLLATE "C":
SELECT DISTINCT ON (i.good COLLATE "C", i.the_date)
       i.the_date, p.the_date AS pricing_date, i.good, p.price
FROM   inventory  i
LEFT   JOIN price p ON i.good = p.good AND i.the_date >= p.the_date
ORDER  BY i.good COLLATE "C", i.the_date, p.the_date DESC;
Note the added collation in two places.
Twice as fast in my test with 20k rows each and very basic names ('good123').
Index
If your query is supposed to use an index, columns with character data have to use a matching collation (good in the example):
CREATE INDEX inventory_good_date_desc_collate_c_idx
ON price(good COLLATE "C", the_date DESC);
Read the last two chapters of the related answer I linked above.
You can even have multiple indexes with different collations on the same columns - if you also need goods sorted according to another (or the default) collation in other queries.
Normalize
Redundant strings (name of good) bloat tables and indexes, which makes everything slower. A proper table layout can avoid most of the problem. Could look like this:
CREATE TABLE good (
  good_id serial PRIMARY KEY
, good    text   NOT NULL
);

CREATE TABLE inventory (
  good_id  int  REFERENCES good (good_id)
, the_date date NOT NULL
, quantity int  NOT NULL
, PRIMARY KEY(good_id, the_date)
);

CREATE TABLE price (
  good_id  int     REFERENCES good (good_id)
, the_date date    NOT NULL
, price    numeric NOT NULL
, PRIMARY KEY(good_id, the_date));

The primary keys automatically provide (almost) all indices we need.
Depending on missing details, a multicolumn index on price with descending order on the second column may improve performance:
CREATE INDEX price_good_date_desc_idx ON price(good, the_date DESC);

Again, the collation must match your query (see above).
Since Postgres 9.2 "covering indices" for index-only scans can help some more - especially if tables hold additional columns, making the table substantially bigger than the index.
These resulting queries are much faster:
DISTINCT ON
SELECT DISTINCT ON (i.the_date)
       i.the_date, p.the_date AS pricing_date, g.good, i.quantity, p.price
FROM   inventory  i
JOIN   good       g USING (good_id)
LEFT   JOIN price p ON p.good_id = i.good_id AND p.the_date <= i.the_date
ORDER  BY i.the_date, p.the_date DESC;

NOT EXISTS
SELECT i.the_date, p.the_date AS pricing_date, g.good, i.quantity, p.price
FROM   inventory  i
JOIN   good       g USING (good_id)
LEFT   JOIN price p ON p.good_id = i.good_id AND p.the_date <= i.the_date
AND    NOT EXISTS (
   SELECT 1 FROM price p1
   WHERE  p1.good_id = p.good_id
   AND    p1.the_date <= i.the_date
   AND    p1.the_date >  p.the_date
   );

db<>fiddle here
OLD sqliddle

Faster solutions
If that still is not fast enough, there may be faster solutions.
Recursive CTE / JOIN LATERAL / correlated subquery
Especially for data distributions with many prices per good:

Optimize GROUP BY query to retrieve latest record per user

Materialized view
If you need to run this often and fast, I suggest you create a materialized view. I think it is safe to assume, that prices and inventories for past dates rarely change. Compute the result once and store a snapshot as materialized view.
Postgres 9.3+ has automated support for materialized views. You can easily implement a basic version in older versions.


How to make stored procedures more readable without harming performance I come from OOP background, and I need to write and maintain huge stored procedures.

Naturally, the sprocs become quite unreadable very quickly, no matter how much I try formatting, giving readable names and aliases, and so on.

Andrew Novic brings up the option of breaking the procedure into subroutines (rule #1 they teach you in school), but points out that this leads to poor performance.

In my case, performance overrules all other considerations, so it looks like I'm stuck with run-on stored procedures. (ORM is out of the question.)

So... is there any way to make my stored procedures more readable,
or is "damage minimilization" (i.e. nice formatting) my only option?

UPDATE - Clarification:

I deliberately left out details, because I reasoned answers would be useful for others as well, and not just my specific case.
However, I'm adding the details to give an idea of what I'm facing.

Here's what makes the SPs unreadable:

Length (hundreds of lines of code each)
Dynamic SQL
Control flow
Nested queries (sometimes two or even three levels)
Combinations of all of the above :(

UPDATE 2:

I'm using Sql Server 2k5/2k8

As you said, start with easy to read consistent formatting.  Here is a general idea of how I like to do it, although it's by no reason the only good way to format.

DECLARE @Test int

IF @Test = 1
BEGIN
    -- Indent within blocks
    PRINT @Test
    SELECT Field1, Field2, -- If the list get's long
            Field3, Field4 -- split into multiple lines
    FROM Table1
    -- Line up JOIN statement and FROM statement
    JOIN Table2
        -- Indent ON condition with one condition per line
        ON Table1.Field1 = Table2.Field2
        AND Table1.Field2 = Table2.Field2
    WHERE Field1 = @Test
      AND Field2 = 'ABC'
END

On top of that you will notice that I've got comments all over the place.  Including in the middle of the SELECT statement and at the end of some lines.  Over commenting can make it more difficult to read also so you need to find a happy balance.  Another commenting trick is to include "block comments" like this:

/************************************************
********* Title for section 1 *******************
************************************************/

These help you divide up your code into obvious sections.  Say "Variable Declarations and Initialization", "Loading Temporary Tables", "Generating Dynamic SQL", etc.

For subqueries I tend to find CTEs and even nested CTEs easier to read although in some cases a mix is best.

SELECT *
FROM (
    SELECT *
    FROM (
            SELECT *
            FROM Table1
        ) Level2
    ) Level1

vs

WITH
    Level2 AS (
        SELECT *
        FROM Table1 ),
    Level1 AS (
        SELECT *
        FROM Level2)
SELECT *
FROM Level1

And a little SSMS trick while you are viewing your SPs.  Pay attention to those little - signs next to the code.

Click on one of them and it will collapse the block below it.  Doing this you can get a similar effect to separating things out as stored procedure calls.

Of course your mileage will vary with any of these and in some cases a monster SP is just a monster.


Reclaim Transaction Log space of SQL Server I have a SQL Server databases for which log files have grown huge. I have log backup scheduled to run only once a day. But now all the disk space is used by these log files.

Can anyone help me figure out what could be possible ways to reclaim that used space again. Here's what I understood and aware off.

I need to take more frequent log backups. As, SQL Server will internally reuse that logspace as and when required.
I have DB's in Full Recovery Model and under AlwaysOn setup. Hence, if I want to shrink them, I'll need to first remove them from AlwaysOn and then put them in Simple Recovery mode and then Shrink the Log File to desired size limit.

Is there any other way apart from the two mentioned above. Can I create a new log files with fresh required size on some other drive and make my db's point to that new location. Is it a doable task? need more clarity on this.

Any inputs on this would be a great help.

Yes, take log backups more frequently. Is there a reason they are so infrequent? It seems like you have a lot of potential for losing data (not just in complete disaster). The log file will continue to grow until a backup is taken, because it will not re-use the space.


MySQL starting 2 processes that can't be used I am running MySQL on CentOS. When I attempt to start it gives an error but in the background 2 processes are started:

Stop:

$ sudo service mysqld stop
[sudo] password for arifu:
Redirecting to /bin/systemctl stop  mysqld.service

$ ps -aux | grep mysql
arifu    20332  0.0  0.0 112620   744 pts/0    S+   13:08   0:00 grep --color=auto mysql

Start:

$ sudo service mysqld start
Redirecting to /bin/systemctl start  mysqld.service
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

Logs:

$ systemctl status mysqld.service
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: deactivating (final-sigterm) (Result: exit-code)
Process: 17465 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=1/FAILURE)
Process: 17443 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
CGroup: /system.slice/mysqld.service
        └─17468 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid...

$ ps -aux | grep mysql
mysql    17798  0.0  0.0  53032  3620 ?        Ss   13:03   0:00 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
mysql    17800 89.6  6.7 39831592 3308524 ?    Rl   13:03   0:02 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
arifu    17826  0.0  0.0 112620   744 pts/0    S+   13:04   0:00 grep --color=auto mysql

None of these can be used:

$ mysql -h localhost -u root -p mysql
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)

Any idea?

EDIT

I managed to trace the problem to wrong configuration - join_buffer & malloc-lib:

join_buffer=256M
malloc-lib=/usr/lib64/libtcmalloc_minimal.so.4

I had copied these when I migrated from MySQL 5.6 but it appears they no longer work for 5.7

The name of the first one is join_buffer_size.  And 256K (not M) has been the default since 5.6.6.  Using 256M is likely to blow out RAM all too easily.  This would either slow down mysqld (due to swapping) or crash it (out of memory).

I don't know about the .so, but I would certainly suspect it.

As for 2 processes -- On some Operating systems, several processes, the number based on some configuration values, probably thread_cache_size and max_connections, plus usage patterns.


mongoexport not exporting all possible data I have a collection with documents similar to the following:

{
 _id: 'p_123456',
 id: 123456,
 kind: 'person'
 data: [...]
}

id field can contain either positive or negative integers.
This collection contains close to 100 million documents and I have a python script which I use in order to process the data from the collection.

I'm trying to export all the data, but into 8 different processes using the $mod operator in the following way:
    mongoexport -u user -p password -d db -c collection --query "{\$and:[{kind:'person'},{id:{\$mod:[8,\$i]}}]}" | python process.py - where $i is a number between 0 - 7.

For some reason I've noticed that when I use this method with 8 processes not all the data is being exported just 65 million out of 87 million for this specific kind.

If I run a single mongoexport process with the query {kind:'person'} only, all 87 million documents are being exported.

Is it possible that running 8 different proccesses with $mod:[8,0] to $mod:[8,7] isn't enough in order to export all the data? what am I missing here?

EDIT 1:
Following AdamC suggestion, I ran the following map reduce:

var map = function() {
  if (this.kind == 'person'){
      var key = this.id % 8
      if (key < 0){
          key = key * (-1)
      }
      emit( key, 1 );
  }
};

var reduce = function( key, values ) {
  return Array.sum(values);
}

The results returned as expected ~87 million records:

> db.map_reduce_modulus.find()
{ "_id" : 0, "value" : 10886482 }
{ "_id" : 1, "value" : 10878131 }
{ "_id" : 2, "value" : 10881552 }
{ "_id" : 3, "value" : 10882586 }
{ "_id" : 4, "value" : 10886060 }
{ "_id" : 5, "value" : 10882565 }
{ "_id" : 6, "value" : 10886171 }
{ "_id" : 7, "value" : 10883563 }

Therefor I don't think this is a type issue

Thanks for advance

I can only speculate without having access to the data, but I suspect that you have non-numeric data in the id field.  Try using the $type operator to verify that some of the fields have not been passed in as strings or similar and that all the fields are homogeneous in terms of type.


MongoDB GridFS Offsite Backups We are using MongoDB GridFS to create a document archive.  I am creating a sharded cluster at our Primary DataCenter.  I need a working backup at our offsite Disaster Recovery DataCenter.  This will be a very large database, ~6TB.  I will be adding 20k new documents daily.  I need to be able to take incremental backups at the end of our batch cycle and apply them to the offsite cluster.  We do not want to use replication because we want the clusters to be entirely independent, but essentially identical.  We will never delete from the collection.  We are running Red Hat.

Does anyone have any suggestions about how to keep the databases in sync?

You can look into tailing the oplogs of the shards yourself, thereby sending the data to a cluster in a remote data center incrementally.  Similarly you can look into tools like mongoriver from Stripe or mongo-connector from MongoDB labs if you don't want to do all the work yourself from scratch (but then are relying on a third party keeping that tool up to date or an unsupported labs project).  You will still need to wrap these utilities in some logic based on your needs and do some manual data verification to validate date between the two sites, and getting point-in-time snapshots of a cluster is non-trivial.

The only "out of the box", supported, way to do this (that I am aware of) is to use the on-premise backup capabilities of Ops Manager which is a paid offering from MongoDB themselves (full disclosure: I used to work for MongoDB).  It's a complicated process to keep a full copy of a sharded cluster in a separate location, so really your choices are to invest your own time and resources, or pay for a product to take care of it for you.


Curious about V$SESSION_LONGOPS output I am curious about something, I was trying to find any long running queries currently running on my Oracle database.

The Query:

SELECT username,sid, serial#,
     TO_CHAR(CURRENT_TIMESTAMP,'HH24:MI:SS') AS curr,
     TO_CHAR(start_time,'HH24:MI:SS') AS logon,
     (sysdate - start_time)*24*60 AS mins
FROM V$SESSION_LONGOPS
WHERE    username is not NULL
AND (SYSDATE - start_time)*24*60 > 1 ;

The output:

USERNAME SID   SERIAL#  CURR         LOGON       MINS
user     1999   23457   15:53:59    13:58:10    4435.81666666667
user     581    7549    15:53:59    15:57:10    4316.81666666667
user    1999    31343   15:53:59    16:58:10    4255.81666666667

Based on the info above is that telling me I have several queries running for days?

I am new to Oracle, mostly MySQL but i know in MySQL this is not a good thing.

If someone could tell me what I am seeing that would be great.

Most likely not queries but system operations, e.g. gather statistics.
Probably nothing to worry about.

V$SESSION_LONGOPS

  V$SESSION_LONGOPS displays the status of various operations that run
  for longer than 6 seconds (in absolute time). These operations
  currently include many backup and recovery functions, statistics
  gathering, and query execution, and more operations are added for
  every Oracle release.

Just to verify, check opname and target_desc.


Change constraint on column based on value of another Is it possible to change a constraint on a column in postgres based on the value of another column?
E.g. (pseudocode):

CREATE TABLE transactions(
    id SERIAL PRIMARY KEY,
    type TXN_TYPE NOT NULL,
    amount BIGINT,
    . . . .,
    refunded boolean DEFAULT FALSE,
    refund_id DEFAULT NULL if (CONSTRAINT link_refund CHECK (refunded=TRUE))=TRUE REFERENCES transactions(id)
);

A foreign key can not be "conditional". The only "exception" to that rule are null values which can't reference another table by definition.

If I understand your question correctly, you are trying to implement a constraint that says "if refunded is true then refund_id must reference an existing transaction".

I don't think you need the refunded column at all. Because the flag refunded can be derived from the value of the refund_id  using the expression: refund_id is not null.

If you do want a column like that, just create a view with that expression.

If you do insist on having the redundant refunded flag, you could setup a check constraint like this:

CREATE TABLE transactions(
    id SERIAL PRIMARY KEY,
    type TXN_TYPE NOT NULL,
    amount BIGINT,
    . . . .,
    refunded boolean DEFAULT FALSE,
    refund_id integer null REFERENCES transactions,
    constraint check_refund
       check ( (refunded and refund_id is not null or
               (not refunded and refund_id is null) )
);


How to configure Standby database in oracle for read/write I have configured a physical standby database in Oracle 11gR2, and the standby database it's working fine. All the changes in the principal database are replicating to the standby database, the standby database is in read only mode. I want to configure the standby database for replicating the changes done in the standby database to the principal database, I mean i don't only need to open in read/write mode but also to apply the changes done in the physical standby to the principal. Any ideas of how to configure it? I have searched in the internet but I only find to open in a read/write test environment.

For this you need a logical standby database, instead of a physical one. This is a little more complex. Not all data types are supported and when data is updated on both sides on the same time, there could be some problems ahead.


How to use a MySQL database both remotely and locally I cannot find the best solution for my problem.

I made a C++ application which runs on a Kiosk under Ubuntu that needs to store and retrieve data from a MySQL database and these same data need to be accessed from a remote web application.

These are the requirements:

The kiosk is connected to Internet, but we cannot assume that internet is always available
The kiosk need to always access to database because the users can access the kiosk services only after the login (user data are stored in the database)
the remote web application needs to insert or modify data stored in the database

At the moment, I'm using a local MySQL database installed in the Kiosk with PhpMyAdmin and the application directly access the local data. Then, I used cron to upload the database once a day, then I import the database on my server in order to be accessed by the remote web application.

This is really a bad solution, so I would like to find another one.
What do you suggest?

I would like to have a database on my server and let the remote web application directly use it and receive updates from the Kiosk.

Remote access can be had if you provide suitable GRANTs for hosts other than "localhost".


How to do a case sensitive search in WHERE clause? I want case sensitive search in SQL query. But by default, MySQL does not consider the case of the strings.

Any idea on how to do a case sensitive search in SQL query?

by default, MySQL does not consider the case of the strings

This is not quite true.  Whenever you create database in MySQL, the database/schema has a character set and a collation.  Each character set has a default collation; see here for more information.

The default collation for character set latin1, which is latin1_swedish_ci, happens to be case-insensitive.

You can choose a case-sensitive collation, for example latin1_general_cs (MySQL grammar):

CREATE SCHEMA IF NOT EXISTS `myschema`
DEFAULT CHARACTER SET latin1
COLLATE latin1_general_cs ;

This has an effect on things like grouping and equality.  For example,

create table casetable (
  id int primary key,
  thing varchar(50)
);

select * from casetable;
+----+-------+
| id | thing |
+----+-------+
|  3 | abc   |
|  4 | ABC   |
|  5 | aBc   |
|  6 | abC   |
+----+-------+

In a case-sensitive database, we get:

select thing, count(*) from casetable group by thing;
+-------+----------+
| thing | count(*) |
+-------+----------+
| ABC   |        1 |
| aBc   |        1 |
| abC   |        1 |
| abc   |        1 |
+-------+----------+

select * from casetable where thing = "abc";
+----+-------+
| id | thing |
+----+-------+
|  3 | abc   |
+----+-------+

While in a case-insensitive database, we get:

select thing, count(*) from casetable group by thing;
+-------+----------+
| thing | count(*) |
+-------+----------+
| abc   |        4 |
+-------+----------+

select * from casetable where thing = "abc";
+----+-------+
| id | thing |
+----+-------+
|  3 | abc   |
|  4 | ABC   |
|  5 | aBc   |
|  6 | abC   |
+----+-------+

Note that you can also change the collation from within a query.  For example, in the case-sensitive database, I can do

select * from casetable where thing collate latin1_swedish_ci = "abc";
+----+-------+
| id | thing |
+----+-------+
|  3 | abc   |
|  4 | ABC   |
|  5 | aBc   |
|  6 | abC   |
+----+-------+


Linking Two database to execute join query I have a remote odbc connexion and an MySQL database in my localhost
i ask if there is a way to execute a join query ( linking the two database ) ?

thanks.

You could make use of FEDERATED Tables. More on them: HERE.

Basically, what you do, is to create a federated table on your local DB, based on the remote table (example in the link). Then you can join them, as they're both local. Everything is pretty straightforward, and explained in the link above.


Reorder partitioned results by specific column I have a query where I want to find the top 50 event_dates for each time zone. While I am getting that, it's not currently formatted in a usable manner. I want the results to look something like:

  ID  |  event_date  |  timezone

  1   | 2017-08-07  | Mountain

  2   | 2017-08-08 |    Mountain

  3   | 2017-08-09 |    Mountain

  4   | 2017-08-07 |    Central

  5   | 2017-08-08  | Central

  6   | 2017-08-09 |    Central

  7   | 2017-08-07 |    Eastern

  8   | 2017-08-08 |    Eastern

  9   | 2017-08-09 |       Eastern

  10  | 2017-08-07 |    Western

  11  | 2017-08-08 |    Western

  12  | 2017-08-09 |    Western

Currently, the results are being mixed between the timezones like this:

  ID  |  event_date  |  timezone

  1   | 2017-08-07 | Mountain

  2   | 2017-08-07 |    Central

  3   | 2017-08-07 |    Eastern

  4   | 2017-08-07 |    Western

  5   | 2017-08-08 | Mountain

  6   | 2017-08-08 |    Central

  7   | 2017-08-08 |    Eastern

  8   | 2017-08-08 |    Western

  9   | 2017-08-09 |       Mountain

  10  | 2017-08-09 |    Central

  11  | 2017-08-09 |    Eastern

  12  | 2017-08-09 |    Western

What would I need to do to update the query to output in that manner?

For reference, what I currently have is something like this:

SELECT TOP 200 ID, event_date, timezone FROM ...
ORDER BY ROW_NUMBER() OVER(PARTITION BY timezone ORDER BY timezone), timezone, event_date ASC

I would get the ROW_NUMBER logic happening first, by enclosing it in a sub-query, and then filtering by the top 50 rows per timezone. This also makes the query immune to new timezones being added to your FROM table.

SELECT *
FROM (
    SELECT ID
        ,event_date
        ,timezone
        ,ROW_NUMBER() OVER (
                PARTITION BY timezone --these are the main groups you want
                ORDER BY event_date DESC --DESC picks the LATEST dates first (ASC would pick the earliest)
            ) AS RowNumber
    FROM...
    ) Results
WHERE RowNumber <= 50 --top X rows per timezone
ORDER BY timezone, RowNumber


SQL Server Backup , Job with embeded SQL I have a SQL Server 2008 R2 instance containing lots of databases that come and go. Each time a database leaves the server or another one pops in, I need to go through a killer routine:

I run this script in SQL Server Management Studio

SET NOCOUNT ON

SELECT 'BACKUP DATABASE [' +
name +
'] TO  DISK = N''C:\Program Files\Microsoft SQL Server\MSSQL10_50.HART\MSSQL\Backup\' + 'BCK\' +name +
'.bak'' WITH NOFORMAT, INIT,  NAME = N''Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'
FROM sys.databases
WHERE state_desc = 'ONLINE'
AND name NOT IN ('master','tempdb','model','msdb','blah','blah2')

SET NOCOUNT OFF

,I get the result to text and I paste it inside the SQL Agent Job Step window.

Is there a chance that I could put this script inside the SQL Server Agent Jobs so every time a database leaves or gets into my server, I won't have to manually change my Job Step definition?

Thank you!

In the sense, that script creates one backup and you are overwriting it each time it is executed....then you can most definitely just throw it into one step of a single job to run on your schedule. Just understand that you only have one full backup for each database, if you have requirement to go back in history to restore you cannot do it.

One tweak you can make to that script to get additional backups for each day is just throw in a date value.

A little bit more robust solution is offered by Ola's SQL Server Backup scripts that handles gracefully when databases are dropped and created. Just setting the parameter @database='USER_DATABASES' will handle this for you, along with the other required parameters of course.


How to ensure only a single row with a certain value Given this table:

CREATE TABLE rollouts
(
    id           UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    device_id    TEXT        NOT NULL,
    is_active    BOOLEAN     NOT NULL DEFAULT FALSE
);

How can I ensure that for a given device_id, there is only ever one active rollout? Multiple inactive ones are fine, so a simple unique index doesn't cut it.

You can use a partial unique index:

create unique index on rollouts (device_id)
where is_active;


SQL Server 2012 hangs at SqlRsConfigAction_install_ConfigNonRC_Cpu64 For some reason SQL Server 2012 keeps hanging at SqlRsConfigAction_install_ConfigNonRC_Cpu64

There isn't any Windows installer running apart from the SQL Server setup.

Update on Resolution
Install SQL2012 without selecting Reporting Services

Solved this problem after uninstalling Microsoft Visual C++ 2017 Redistributable (x86) and (x64) and reinstall successfully sql express 2016.

But the best solution for avoiding this issue is install sql server management studio and sql server express before installing visual studio


Why don't relational databases have read-only indexes on columns? In many programming language implementations, the compiler will turn on certain optimizations if it knows that a variable will not change after its initial assignment. Wouldn't this stretch to persistent data too? If the database knows that a certain column will not be updated after a record is created, couldn't it change the way the data is stored, or cached, to improve performance?

Sure, some database platforms do handle read-only or mostly-read data differently.

For example, in Oracle, you can choose to enable results caching, which lets the database engine save query results and reuse them when it sees the same query come in again. That's really useful for data that rarely changes. However, you wouldn't want to bother enabling that in scenarios where you know the data will change.

Another example: in SQL Server, if you set the entire database as read-only, SQL Server handles locking differently. It doesn't need to worry about transactions when you can't modify the data.

I know what you're thinking: "Why can't the database engine figure it out?" Because data access patterns can be complicated. For example, you might have a data warehouse that is loaded from scratch every night, and then is read-only all day long. You wouldn't want the engine to bother even trying to cache ETL query results overnight during the loads, so you'd want to turn that kind of thing off overnight to maximize load speeds.


Is it possible to set a composite NOT NULL constraint in PostgreSQL For logging purposes, in a table I design, I want to store the IP information of users when they logged in.

When a user logs in, IPv4, IPv6 or both IP addresses should be stored. But I want to set a constraint that both cannot be NULL.

Is it possible to do that in Standard SQL with constraints or should I do that using PL/pgSQL in PostgreSQL or in the business tier?

Let's assume the structure of your table is this one:

CREATE  TABLE log_logins
(
    user_id INTEGER NOT NULL,
    login_time TIMESTAMP NOT NULL DEFAULT now(),
    ip_v4 TEXT /* or any other representation */,
    ip_v6 TEXT /* or any other representation */,
    PRIMARY KEY (user_id, login_time)
) ;

You can just add a CHECK that guarantees that either one of ip_v4 or ip_v6 is not null, but not both (it makes no sense to me to have both a v4 address and a v6 one; you don't normally use both protocols at the same time). That would be done with the following statement:

ALTER TABLE log_logins
  ADD CONSTRAINT one_and_only_one_of_ip_v4_or_ip_v6
  CHECK ((ip_v4 IS NULL) <> (ip_v6 IS NULL));

If it is reasonable to have both a v4 and a v6 address at the same time, the constraint to use would be:

ALTER TABLE log_logins
  ADD CONSTRAINT at_least_one_of_ip_v4_or_ip_v6
  CHECK ((ip_v4 IS NOT NULL) OR (ip_v6 IS NOT NULL));

Alternatively, take a look at the RhodiumToad/ip4r extension. If you use it, you can represent ip4, ip6 or ipaddress (that can contain either an IPv4 or IPv6 address) in a compact and efficient way, and have a collection of operators on both IP addresses and IP address-ranges. I'd actually recommend it.

Although PostgreSQL includes the inet data type, that can be used to store internet addresses (together with netmasks), the ip4r extension provides a few advantages if you only need an IP address (without the netmask). One of the advantage is a more compact representation, that may play a role if the volume of data to be recorded is big. This extension is really helpful if you need to use ip ranges. One use case would be to join the IP column of the log_logins table to another table containing (arbitrary) IP ranges and the countries to which they correspond.


What is the difference between mongod in standalone vs replica set I've been running Mongodb as a replica set on 3 servers (primary, secondary and arbiter) but because I have to downscale I'm converting the primary to a standalone server. Before this change the replica set secondary was just a "backup" in case of primary failure, so no distribution of reads.

I was expecting the CPU load on my primary server to go down or stay the same. However it went up a bit instead (before my "uptime" load rating would be around 0.01, now it's usually around 0.1- 0.2). Not a problem, but stil significant.

So what exactly is the difference in running mongod as a replica set vs a standalone server? And are there drawbacks to running mongod as a single node "fake" replica set?

I'm using the MongoRocks storage engine.

Switching a primary in a replica set to a standalone will not impact CPU unless you were offloading something to the secondaries that will now hit the standalone node like reads or Map Reduce (and you mention you were not). As well as that, mongod is generally not CPU intensive unless you are doing lots of sorts or distincts (or a lot of javascript, Map Reduce etc.). If you are just doing reads/writes then the CPU usage will be minimal.

The load metrics mentioned are both very low, and indicate the server is doing very little - that kind of difference could come from users doing things even. To be completely sure, use the mongod normally and check out what it is doing with CPU in top, I suspect it won't be using CPU heavily.

If there is a difference, then my final suspect would be a slight increase in write load or similar - depending on the options used, the non-MMAP engines can use a little more CPU when writing data (using compression, or cleaning up data on the filesystem). If your write load has not increased then that's probably not the issue.


MySQL8 Binary installation - getting error msg "-bash: bin/mysql: Permission denied" after upgrading to 8.0.22 We have a MySQL 8 instance with Binary Installation. After upgrading to 8.0.22 (from 8.0.19) can't seem to connect using following cmd in CLI
bin/mysql -uusername -p

error msg -
-bash: bin/mysql: Permission denied

Looks like there is a bug reported for this. Hope this helps someone.
https://bugs.mysql.com/bug.php?id=101969


Oracle - How to issue desc command in a procedure within package? It's possible to see only the parameters of a especified procedure into a package?

For example a procedure into dbms_scheduler, something like this:

desc dbms_scheduler.create_job

It's possible?

Currently I get to see parameters viewing all objects into the package like this:

desc dbms_scheduler

DESC / DESCRIBE is a client command (eg SQL*Plus) and not available in PL/SQL.

You can try querying the user_arguments or all_arguments view. It is relatively simple as long as you don't have overloaded procedure / functions and nested data types or weird procedure / variable names.


sp_ExecuteSQL, performance and table variables Is anyone able to explain some behaviour I'm seeing with SQL Profiler? Taking the following two batches:

exec sp_executesql N'
    declare @t table (
        x int
    )

    insert into @t (x) select top 10 number from dbo.gennumbers

    select * from @t
    union
    select * from @t
'

exec sp_executesql N'
    select top 10 number from dbo.gennumbers
    union
    select top 10 number from dbo.gennumbers
'

(this is a simplification of some refactoring I'm doing; in reality the temp table is populated through a CTE that's reused in the final query, so mitigating the amount of duplicated work SQL Server needs to do by caching the data).

In this example, GenNumbers is simply a numbers table with a single column that just contains a sequential run of numbers - nothing special.

In profiler, I'm getting the follow results:

Batch 1, with the temp table:

Event             TextData            Reads
SP:StmtCompleted  INSERT INTO(...)    27
SP:StmtCompleted  SELECT * FROM ...   6
SQL:StmtCompleted exec sp_executesql  170

Batch 2, a direct hit:

Event             TextData              Reads
SP:StmtCompleted  SELECT TOP 10 * ...   6
SQL:StmtCompleted exec sp_executesql    6

What I'm trying to understand is the anomoly of the final read count on the SQL:StmtCompleted. I was expecting that the total reads should equal the sum of all reads that occur within the batch, however that doesn't seem to be the case in batch 1 - in fact, it's significantly higher. Whereas batch 2, that does a single select, final reads == actual reads.

There's nothing obvious in the execution plan to see where this extra time is going, but maybe I'm not capturing the right thing - does anyone have any thoughts on what's actually going on? I'm wondering whether I need to worry about the extra 100 reads that SQL Server is "giving" me, given that this read count is five or six times higher than the actual read count.

I'm assuming it's something to do with SQL needing to maintain information about the temporary table, but it's not clear exactly what. Any light shed would be useful.

There is an implicit CREATE TABLE at the beginning and DROP TABLE @T at the end as the table goes out of scope that aren't associated with either the INSERT or the SELECT statement.

If you replace with a #temp table you should see some additional reads for both the drop and create statements. For me I see 36 for the CREATE and 100 for the DROP so that accounts for 136 of your missing 137 reads.

(Script with #temp table)

EXEC sp_executesql N'
   CREATE TABLE #T
  (
     x INT
  )

INSERT INTO #T
            (x)
SELECT TOP 10 number
FROM   master..spt_values

SELECT *
FROM   #T
UNION
SELECT *
FROM   #T

DROP TABLE #T
'


Best way to compare strings using pre-built ngrams? I'm struggling with an n-gram problem and have been hunting the Web for examples to help me out and have noticed n-grams being discussed on this forum. I'm working on a system where we've got scraped titles and are trying to reduce the number of unrecognised texts. There are roughly 60,000 known good titles and about 6,000,000 unmatched titles. 1:100 is not a very impressive ratio. Some of the bad titles aren't in the list of canonical titles and can't ever be matched, but others are failing to be matched because of punctuation/positional/editing/spelling inconsistencies and errors. Typical dirty data.

To avoid confusion I'll clarify what I mean by n-gram as the term is used for two different things. I'm talking n-grams where you break a string into a series of n-length substrings. These are sometimes called "q-grams", for some reason. I'm not talking about something like Google's corpus of word combination "n-grams". So, with a length of 3 for n, the string "hello" breaks down into:

__h
_he
hel
ell
lo_
o__

(The padding before and after isn't always used but, based on my data set, I think that it will be helpful.)

There is a plug-in for MySQL that parses texts into 2-character blocks, but I'm very likely to need lengths of 3 and possibly other lengths. Testing will show what's most effective.

Despite being a programmer for a long time, I'm poor enough at SQL that I haven't sorted out how to build the n-grams or how to rank similarities based on the n-grams. If it comes to it, I can build the n-grams in another language without any trouble and load them all into MySQL. That leaves me with needing to figure out how to construct the SELECT to compare strings.

Do you happen to have or know of an example that you might be able to refer me to?

For background, the rest of the team has been trying to solve this using some code I provided with a variety of algoritms, phonetic and statistical:

Soundex and metaphone:
Not really appropriate for this data (too many languages) and not great on a good day anyway.
Levenshtein distance, longest common subsequence, and Jaro-Winkler:
Much higher quality, but too expensive to run on-the-fly for a data set of this size.

Thanks in advance for any help or suggestions.

I've done this in SQL Server using a modified version of Dice's Coefficient. Basically, you store a precomputed table of q-grams from your lookup data set. This table should have the primary key of the source record, the resulting q-gram, and the number of times that q-gram appears when breaking up the string.

Then when you want to do a lookup, break your input string into a similar list of q-grams (temporary table, subquery, etc.), and join those to your lookup table on the q-gram, grouping by the primary key from the lookup row (which, in the lookup table, is actually a foreign key rather than a primary key). For each matched q-gram, calculate the number of matches as the minimum cardinality between the input value and the lookup table row, e.g. the string 'aaaa' could have 'aa' * 3, and 'aaaaa' could have 'aa' * 4, thus you would have 3 matches. Double this number to get 6.

Once you've got this doubled number of matches, divide by the total number of q-grams obtained from both the input string, and the rows in the lookup table associated with a given primary key. So 'aaaa' would have 3 q-grams, and 'aaaaa' would have 4. You would calculate the similarity as 6/7. You can then filter this similarity quotient based on your desired threshold.

The nice part about this algorithm is that it's fast, since after splitting your input string, it's just a bunch of index lookups. The downside is that it doesn't put any emphasis on ordering of q-grams, so having two words out of order won't affect the match quality (assuming you're not letting q-grams cross word boundaries).

Following is some sample code from SQL Server to explain the concept, but it makes use of features not found in MySQL. You'd probably need to rewrite some of this using subqueries rather than common-table expressions.

CREATE PROCEDURE FuzzyCustomerLookup
    @name varchar(8000),
    @threshold decimal(10,4) = 0.0,
    @limit_results int = 2147483647
AS
WITH inp_q AS ( --Split input string into q-grams
    SELECT
        q.qgram,
        COUNT(*) AS cardinality
    FROM dbo.Qgrams(@name) q --Function to split string into q-grams
    GROUP BY q.qgram
),
matches AS ( --Count matches of input string against each lookup row
    SELECT
        c.row_id,
        SUM(CASE WHEN c.cardinality < i.cardinality THEN c.cardinality ELSE i.cardinality END) AS matches
    FROM CustomerQgrams c --Precomputed table of q-grams
        INNER JOIN inp_q i
            ON c.qgram = i.qgram
    GROUP BY c.row_id
),
dice AS ( --Calculate match quality
    SELECT
        matches.row_id,
        CAST(matches.matches * 2 AS decimal(10,4)) / (CAST(i.cardinality AS decimal(10,4)) + CAST(c.cardinality AS decimal(10,4))) AS similarity
    FROM matches
        INNER JOIN (
            SELECT row_id, SUM(cardinality) AS cardinality
            FROM CustomerQgrams
            GROUP BY row_id
        ) c
            ON matches.row_id = c.row_id
        CROSS JOIN (
            SELECT SUM(cardinality) AS cardinality FROM inp_q
        ) i
)
SELECT TOP (@limit_results)
    ch.CustomerNo,
    ch.CustomerName,
    dice.similarity
FROM dice
    INNER JOIN Customers ch
        ON dice.row_id = ch.row_id
WHERE dice.similarity >= @threshold
ORDER BY dice.similarity DESC


PgAdmin 3 crashed when font size was increased I am using PgAdmin 3 on Postgresql 9.4 on Windows 7. All things work well until I resized  browser fonts from 12 to 18. I cant create database, rename table and even change the font size to what it was before. Many things stopped working. I have tried to uninstall Postgres and install another version but still the behavior is same, no change. Please what do I do. Thanks

I have solved the issue by deleting PgAdmin3 file in the Windows registry.

Save a copy of the Registry editor, so that in-case there is any error, you can
restore the saved copy.
Double click on the "Registry editor" at C:\windows\regedit.
Click on HKEY_CURRENT_USER\Software.
 delete the folder PgAdmin 111.

Re-start Pgadmin. Then increase font size to 12 - 14 Time and it works fine.


Changing listener IP on default TCP endpoint via DDL statement I'm running SQL server instances that are listening on all available interfaces (0.0.0.0). I need them to listen on one specific IP only. I also need to automate this configuration change, e.g. via a DDL statement.

My strategy is to use ALTER ENDPOINT to change LISTENER_IP on the default TDS endpoint.

I ran SELECT * FROM sys.endpoints and discovered the TSQL Default TCP endpoint.

Then I executed the following SQL command:

ALTER ENDPOINT "TSQL Default TCP"
AS TCP (
    LISTENER_IP = 192.168.4.22
)

This produced the following error.

Msg 7871, Level 15, State 1, Line 1
The clause 'AS TCP' is not valid for this endpoint type.

This doesn't make sense to me. It is definitely a TCP endpoint.

The clause 'AS TCP' is not valid for this endpoint type.

This is because you are trying to alter a deafult endpoint, and the only options you can change for this are STATE and AUTHORIZATION:
Network Protocols and TDS Endpoints

So you can only stop this endpoint and create other endpoints.


Azure SQL Audit Log Analysis Afternoon all

I am attempting to analyse some Server Audit logs for one of my Azure DB databases that reported unusual activity.

I have full access to the logs and have downloaded then as well as Merged them using SSMS 17.1.

I have 2 logs:

Log 1 | 20k Rows

Log 2 | 20k Rows

Log 1 + 2 | 6.5k Rows

Individually they read as you would expect, however, when I add them both to the same Merge Audit Files in SSMS, the resulting session only details 6.5k rows with an extremely limited timeframe.

Has anyone come across this to be able to help me understand what is going on?  If I want to try and identify the Queries run from and given IP address, I would rather not have to do it log by log!

Having now spoken to Microsoft about this, it's a known issue in SSMS 17.1.  At least we now know!

They rightly recommended to use sys.fn_get_audit_file instead.


How to full text search worked with special character like "of"? How to search with fulltext search if keyword has special character like

"of", "-" ...etc

select * from table1
join CONTAINSTABLE (table1,(Title,Description_HTML),
'"of" and "Department"')
tb2 on tb2.[key] = table1.ID

I am unable to view the result?

As Martin Smith commented, "It depends."  The factors that control the processing of Full Text indexes depends on the configuration you use in your CREATE FULLTEXT INDEX command.

Language - Which controls the parsing of words and phrases according to that language's rules.
Stop Word List - Words to be left out of the search, though their position in the string is recognized.
Accent Sensitivity - Determines whether accents are processed or ignored.

There is a tool to help you test your queries: https://msdn.microsoft.com/en-us/library/cc280463.aspx

You can use the sys.dm_fts_parser command to test your full text results.  For example, three similar searches using this function.

select * from sys.dm_fts_parser('"of" and "-" and "Department"',
   1033, NULL, 0) --American English, No stop words, Accent Insensitive;

Returns 2 tokens: of, Department

select * from sys.dm_fts_parser('"of""-""Department"',
   1033, NULL, 0) --American English, No stop words, Accent Insensitive;

Returns 2 tokens: of, Department

select * from sys.dm_fts_parser('"of-Department"',
   1033, NULL, 0) --American English, No stop words, Accent Insensitive

Returns 3 tokens: of-Department, of, Department

In general a "-" is ignored when standing alone. However, depending on the Language choice in your Full Text Index, the "-" serves as a hyphen in text and may be reported as shown in the third example which includes "of-Department" as one of the tokens returned.


Suggest a MySQL book for me I have recently joined a team in my corp office that has mysql as a part of their work profile. Though the mysql work not often in this team, but when they do it comes in big scale. The main mysql technology part that this team works on is High availability. Please do not mix high availability with clustering. Which was the mistake i understaood when i heard this first time. By high availability i mean db replication, master/slave/relay master, etc setup. I won't be doing any query optimization or any of those closely code related STUFF. But more of database administration stuff like looking for replication failures and fixing it and setting up replication, troubleshoot issues maybe like slow query (rarely). Please suggest me a book that concentrates on this part. I have some experince with mysql query language like creating db & tables, looking at tables schema and db priveldges and grants priveldges and backing up and restoring db with mysqldump and writing small select  queries and like for mainly find out where it is going wrong or use it in my report generation script from bash command line. I am mainly a bash scripting and apache httpd and tomcat expert and new to this filed. Although the httpd and tomcat have always maintanined a close relation with mysql and db in general . But where i worked previously had huge db and were maintained by a special dedicated team of experts who work solely on db. And that was in oracle and their size was in tera bytes and had advanced structure like oracle rack and real time replications setup, etc and i wan't expected to touch any parts. I was concerating on the app tier and web tier. So can you guys suggest me a way to quickly learn the important stuff of mysql and know the internals or more subtile part as we move on. A book or a online course or a tutorial site. Ah forgot to mention we are using mysql 5 and 4 in some *nix machines. And these are huge setup with lots of machine in many DC. As i said before my primary filed of expertise is bash scripting and web and app layer stuff. So please guide. I am not familiar with windows though worked on AD (and to some extent on SCCM) related stuff for sometime and i am certified RHCE (current and valid). Thanks. ;)

If you are interested in High Availability and Performance for MySQL, then High Performance MySQL : Optimization, Backups, Replication, and More

is definitely the book for you (Third Edition is out now) !!!

I would still have MySQL 5.0 Certification Study Guide

as well. I used this book as my reference book for 10 months before taking the Certification Exams. In honesty, it has many typos and incorrect facts, all minor in nature.

There is no substitute for Practical Experience. Implement the things leaned from these two books and you will develop the needed confidence to handle,..., no, manhandle MySQL.

If you must use MySQL 4.x, then you will also need MySQL Reference Manual.


How to BCP export three related tables in a transaction consistent manner? Have three related tables that I would like to bcp export to file(s).  This three tables have relationships to each other.  Is there a good way to bcp export these tables in a transactionally consistent manner?

how about creating a database snapshot and then running the BCP against that ?
https://docs.microsoft.com/en-us/sql/relational-databases/databases/create-a-database-snapshot-transact-sql?view=sql-server-ver15


ON CONFLICT ON CONSTRAINT fails saying constraint doesn't exist I'm trying to use new Postgresql 9.5 upsert feature. But for some reason my query saying constraint doesn't exist (when it does).

My query is this

INSERT INTO journals (ext_ids, title) VALUES ('{"nlmid": "000"}', 'blah')
ON CONFLICT ON CONSTRAINT idx_nlmid_journal DO NOTHING;

where idx_nlmid_journal is unique index on jsonb field created like this

CREATE UNIQUE INDEX idx_nlmid_journal ON public.journals ((ext_ids ->> 'nlmid'::text));

I get error

ERROR: constraint "idx_nlmid_journal" for table "journals" does not exist

what am I missing ?

The syntax you use is not valid for a unique index because a unique index does not create a constraint. You need to remove the ON CONSTRAINT and use the index expression instead.

This works:

INSERT INTO journals (ext_ids, title)
VALUES ('{"nlmid": "000"}', 'blah')
ON CONFLICT ((ext_ids ->> 'nlmid'::text))
DO NOTHING;


How to use LIKE operator with multiple conditions at once in PostgreSQL I want to select records using LIKE operator with multiple conditions, I can use OR between the conditions but I am looking for a short way to solve this issue.
I am trying the following query:

select *
from employees
where name like '%.%'
   OR name like '%,%'
   OR name like '%;%'
   OR name like '%/%' -- this code goes very long

I am looking for a way to shorten this query.
Any help is appreciated in advance.

You can combine the LIKE operator with an array:

select *
from employees
where name like any (array['%.%', '%,%', %;%', ...]);

Or a completely different approach, that does not require to repeat the wildcard for each value:

select e.*
from employees e
where exists (select *
              from (values('.', ',', ';')) as t(ch)
              where e.name like concat('%', t.ch, '%'))

As you only check for single characters, you could use Postgres' array functions as well: by turning the name into an array of characters and checking for an overlapping array:

select *
from employees
where string_to_array(name, null) &&  array['.', ',', ';'];

or

select *
from employees
where string_to_array(name, null) &&  string_to_array('.,;', null);


How to speed up SQL Server 2014 Spatial functions I have some normalized tables with about 1,2M records that I collect in a view in a SQL Server 2014 database.
Note that I have simplified the names of the tables and columns.
To illustrate I have created this query on the source tables:
SELECT geom, dateFrom, dateTo
FROM requestsTable
INNER JOIN geomTable ON requestsTable.requestId = geomTable.requestId
WHERE (dateTo >= '2020-09-10 00:00:00' AND dateFrom <= '2020-10-09 00:00:00')

This requests returns 54 rows within 1 second.
The problem is that as soon as I extent this query with spatial functions, the query gets slow.
Example 1: Added spatial filter:
SELECT geom, dateFrom, dateTo
FROM requestsTable
INNER JOIN geomTable ON requestsTable.requestId  = geomTable.requestId
WHERE (dateTo >= '2020-09-10 00:00:00' AND dateFrom <= '2020-10-09 00:00:00')
AND geom.Filter(geometry::STGeomFromText('POLYGON ((-791308.53524129 5350657.6892069, -791308.53524129 6762958.489206901, 2357696.2647587 6762958.489206901, 2357696.2647587 5350657.6892069, -791308.53524129 5350657.6892069))', 25832)) = 1

This requests returns the same 54 rows, but now it takes 23 seconds.
The execution plan shows 67% at the clustered index seek on the PK of the geometry table.

https://www.brentozar.com/pastetheplan/?id=S1l1ivsBv
Example 2: Even just adding the SRID takes a lot of extra time:
SELECT geom, dateFrom, dateTo, geom.STSrid
FROM requestsTable
INNER JOIN geomTable ON requestsTable.requestId  = geomTable.requestId
WHERE (dateTo >= '2020-09-10 00:00:00' AND dateFrom <= '2020-10-09 00:00:00')

This requests returns the same 54 rows, but now in 12 seconds.

https://www.brentozar.com/pastetheplan/?id=BJS8swsSw
The geometry in geomTable does have a spatial index:
CREATE SPATIAL INDEX [spatial_idx]
ON [geomTable] ([geom])
USING GEOMETRY_GRID
WITH (BOUNDING_BOX =(440000, 6040000, 900000, 6410000), GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM),
CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]

The question is: how do I speed up the spatial functions?
UPDATE
I simplified the question a bit to make it more understandable. The real tables have a lot of indices that might mess up the query optimizer. Here is the table and index definition:
https://pastebin.com/8GkSkN2h
And the query:
SELECT the_geom, graveperiode_fra, graveperiode_til FROM graveforespoergsel
INNER JOIN graveforespoergsel_polygon ON graveforespoergsel_polygon.graveforespoergsel_id = graveforespoergsel.graveforespoergsel_id
WHERE
 ("graveperiode_til" >= '2020-01-10 00:00:00' AND "graveperiode_fra" <= '2020-11-05 00:00:00')
 -- Adding the filter below adds a lot of time to the query
 AND "the_geom".Filter(geometry::STGeomFromText('POLYGON ((434527.96559412003 5928957.9586343, 434527.96559412003 6282033.158634299, 1214815.9655941 6282033.158634299, 1214815.9655941 5928957.9586343, 434527.96559412003 5928957.9586343))', 25832)) = 1

it seems you need to add date_to column to index on date_from column (create multicolumn index on dates)
your geometry index is not used in both execution plans (probably because the boundaries in filter are outside of the boundaries in index). But it seems with correct multicolumn index you don't need this spatial index at all

Let's try this:
SELECT graveforespoergsel_id, graveperiode_fra, graveperiode_til
INTO #t
FROM graveforespoergsel
WHERE "graveperiode_til" >= '2019-01-10 00:00:00' AND "graveperiode_fra" <= '2022-10-10 00:00:00'

SELECT "the_geom", graveperiode_fra, graveperiode_til
FROM #t
INNER JOIN graveforespoergsel_polygon ON graveforespoergsel_polygon.graveforespoergsel_id =  #t.graveforespoergsel_id
WHERE "the_geom".Filter(geometry::STGeomFromText('POLYGON ((434527 5928957, 434527 6282033, 1214815 6282033, 1214815 5928957, 434527 5928957))', 25832)) = 1

DROP TABLE #t


What are the causes and solutions for mutating table errors? I understand mutating table errors are caused by a design flaw or problematic query.

An old query was recently put into production which throws a mutating table error. Our DBA solved the problem but we do not know how.

What exactly causes mutating table errors and how would our DBA have fixed the problem?

The most likely cause of a mutating table error is the misuse of triggers. Here is a typical example:

you insert a row in table A
a trigger on table A (for each row) executes a query on table A, for example to compute a summary column
Oracle throws an ORA-04091: table A is mutating, trigger/function may not see it

This is an expected and normal behaviour, Oracle wants to protect you from yourself since Oracle guarantees:

(i) that each statement is atomic (i.e will either fail or succeed completely)
(ii) that each statement sees a consistent view of the data

Most likely when you write this kind of trigger you would expect the query (2) to see the row inserted on (1). This would be in contradiction with both points above since the update is not finished yet (there could be more rows to be inserted).

Oracle could return the result consistent with a point in time just before the beginning of the statement but from most of the examples I have seen that try to implement this logic, people see a multi-row statement as a serie of successive steps and expect the statement [2] to see the changes made by the previous steps. Oracle can not return the expected result and therefore throws the error.

For further reading: "mutating table" on Ask Tom.

If as I suspect the cause of the mutating table error is a trigger, one way to avoid the error is to move the logic out of the trigger into procedures.


What backup/restore strategy can I use for the following scenario We have two databases on a server which are being synced using back up and restore on a scheduled basis. Now, we would like to exclude a few tables getting affected by the back up and restore process.

What is the best way of doing this using backup and restore ?

I tried keeping the tables that should not be affected in a secondary file group and performing a file group back up and restore.

I ran into various issues during the restore process.

The following message was appeared while restoring the PRIMARY file group:

The roll forward start point is now at log sequence
number (LSN) 507000000318400006. Additional roll forward past LSN
507000000318400006 is required to complete the restore sequence.
Gone through some online content and tried restoring the last log backup and got the following     message:

This backup set cannot be applied because it is on a recovery path that is inconsistent with the database.

And then I ran into the following article in sqlservercentral

http://www.sqlservercentral.com/blogs/dotnine-sql-server-and-more/2014/02/20/restoring-filegroup-backups-on-another-server-and-why-it-wont-work/

Question 1: How can I exclude few tables from syncing during a backup and restore process?

Question 2: According to article, I cannot achieve this using backup/restore. I that correct?

Thank you...

You cannot exclude parts of the database from backup and restore in the sense that those parts disappear or just become empty. SQL Server requires that all filegroups are either current or offline. You can bring non-current offline filegroups online by making them current by restoring enough backups.

A database is an inseparable entity when it comes to data and consistency.

If you could empty partitions/tables by not restoring them you could break trusted foreign keys and indexed views.


Perform admin tasks (e.g. create database, add diagram support) in SQL Server when not connected to domain My situation: I have installed SQL Express on my machine, while logged in as a domain user. I work remotely so I need to connect to a VPN to be on the domain, but I often prefer not to work while attached to the domain so my packets don't have to travel across the country. My user account has local admin rights (win 7).

My problem is very similar to this one. I receive the same error message when I try to do "admin-y" things, like creating new databases or adding database diagram support to existing databases. The catch is that the error only happens when I am NOT attached to the VPN (and therefore my machine cannot reach the domain controller).

Is there a way to allow SQL Server to trust my login credentials when I'm not connected to the VPN?

Another solution is to create an SQL Server Log on and give it sysadmin permissions.  This avoids using windows entirely for authentication.  This is what the SA account on SQL Server is, a special SQL Server log in (which i normally disable log on once installed and another admin user is created) with sysadmin privileges.

This would probably be the best bet as it does not require creation of a local user on your machine and having to run SSMS in a different manner every time, just select Sql Server authentication when logging in and use the new credentials.


When do _WA_Sys_ statistics Get Updated? I have some of the auto generated _WA_Sys_ statistics in my database which have not been updated in a while (by comparison to other statistics in the same table)

The rule of thumb appears to be in tables >500 rows that statistics are updated at a 20% + 500 row change of data.

However, I can see using the following query

SELECT  t.name,
        i.name,
        i.rowcnt,
        i.rowmodctr,
        p.last_updated
FROM    sys.sysindexes i
        JOIN sys.tables t
            ON i.id = t.object_id
        JOIN sys.stats s
            ON s.object_id = t.object_id AND i.name = s.name
        CROSS APPLY sys.dm_db_stats_properties(s.object_id,s.stats_id) p
WHERE   rowmodctr > 0
ORDER BY i.rowmodctr DESC

That there is a table with a number of _WA_Sys_ statistics that are way out of date (and have a rowmodctr that is higher than 20% + 500)

If a run a query against the table and add one of the columns associated with the out of date _WA_Sys_ statistics in the WHERE clause and check the updated date of the statistic, I can see it has updated.

If i run the same query with the WHERE clause again, the statistic doesn't update

It seems like the _WA_Sys statistics update when a query is run that will use them and they are out of date?

SQL Server only automatically updates the statistics it uses. It may not be actively using the _WA_Sys statistics for cardinality estimation, though it may load them as part of the decision.

To see which statistics are being used for your query, add this to the end of your query, and look in the Messages tab:

OPTION(QUERYTRACEON 3604, QUERYTRACEON 2363);

If your database is in a compat level prior to 2014, you'll need these:

OPTION(QUERYTRACEON 3604, QUERYTRACEON 9292, QUERYTRACEON 9204);

These won't tell you why it's not using certain statistics, though generally if there are statistics created with a higher sampling percentage on the same column, statistics with lower sampling percentages will be disfavored.

Keep in mind that statistics updates don't occur on modification, only when queries run that use them