Unusual error message - sql server the row object is inconsistent. please rerun the query I'm reviewing the error logs for an instance of SQL Server 2012 and found the following error in the log (error 669):

The row object is inconsistent. please rerun the query

I googled and the only answers seem to be generic listings of sys.messages showing the error number and error description.

Has anyone come across this error before? My thoughts so far is it indicates some kind of data corruption, but the error message doesn't say which database is affected.

As a first step, I'd suggest running DBCC CHECKDB against all databases on that instance.  Here's a quick script that will run against each database (including system databases).  This could take quite a bit of time (depending on the size of your databases), but you need to let it finish running.

DECLARE @Message VARCHAR(max);
DECLARE @databaseList AS CURSOR;
DECLARE @databaseName AS NVARCHAR(500);
DECLARE @tsql AS NVARCHAR(500);

SET @databaseList = CURSOR  LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
        SELECT QUOTENAME([name])
        FROM sys.databases
        WHERE [state] = 0;
OPEN @databaseList;
FETCH NEXT FROM @databaseList into @databaseName;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Message = 'Starting DBCC for ' + @databaseName + ' -> (' + convert(varchar,getdate(),121) + ')'
    RAISERROR(@Message,0,1) WITH NOWAIT
    SET @tsql = N'DBCC CheckDB(' + @databaseName + ') WITH ALL_ERRORMSGS,NO_INFOMSGS;';
    RAISERROR(@tsql,0,1) WITH NOWAIT
    exec SP_EXECUTESQL @tsql;
    SET @Message = 'Completed DBCC for ' + @databaseName + ' -> (' + convert(varchar,getdate(),121) + ')'
    RAISERROR(@Message,0,1) WITH NOWAIT

    FETCH NEXT FROM @databaseList into @databaseName;
END
CLOSE @databaseList;
DEALLOCATE @databaseList;


Upgrade/Migration from SQL Server 2008 R2 to SQL Server 2016 I have a client that is having plans to upgrade/migrate the SQL Server instances from 2008 R2 to 2012. I have provided the details as below. The environments are all Virtual running OS version Windows 2008 r2 enterprise SP1 and uses VMWare.

SQL003 - SQL Server 2008 r2 instance on a WSFC. This SQL Server instance is a publisher with local distributor and subscriber to 2 places SQL007 and SQLDR003. It also has some databases which are both in replication and also part of Mirroring to the DR site (SQLDR003).

SQL007 - SQL Server 2008 R2 instance on a WSFC. This SQL Server instance is a publisher for SQLDR007 and SQL008 and is also a subscriber to SQL003 as noted above as well.

SQL008 - SQL Server 2012 SP1 is a standalone SQL Server instance. I am not too concerned about this instance.

I know that in order for me to move forward with upgrading to SQL Server 2016 the 0S needs to be at least Windows 2012 OS OR later. I am thinking down my head how to achieve this. Is it possible if I can directly upgrade the OS to Windows 2012 or 2016 and then patch SQL Server to 2016 but then how about replication and stuff will this break anything etc or do I need to rebuild a cluster completely and then migrate the objects. It would not be an ideal spot to migrate the databases and all the associated objects based on how the environment is configured. I am in need of some methodology that can save me time and with no changes to the existing hostname etc.

It sounds like your plan is to do an in place upgrade of the OS and the SQL Server. You will experience much less pain and have greater chance for rollback in the event things go awry if you do a side by side upgrade.

Build up the new servers and then migrate to them. This keeps the existing servers working and allows you to migrate and test without affecting the users and data.

Keep in mind too, that there could be many changes that could adversely affect the applications supported by this SQL Server. Having the extra servers will provide the ability to test those applications and find out what mitigation tasks you may need to take.

Once you have the tasks and migration process detailed out, you can utilize log shipping from the main server to the new server to facilitate a low downtime migration.

Also consider using Always On Availability Groups instead of clustering as it may provide you a better HA/DR.

From a host name not changing perspective, utilize CNAME DNS entries. This way you can use new servers, and then when ready cut the DNS entry over to the new server name. To the users, they think nothing has changed.


mysql.proc keeps crashing, cannot do a mysqldump? Due to some problems with InnoDB, I'm going to dump all databases to a new server:

mysqldump -E -R --all-databases | pv -b | mysql -u root -p -h new.server

The dump process stopped with error:

59.9kB assword: 59.9kB
ERROR 145 (HY000) at line 2970: Table './mysql/proc' is marked as crashed and should
    be repaired
 228MB
mysqldump: Got errno 32 on write

I've ran the following command to repair all tables in all databases:

mysqlcheck --auto-repair --all-databases

When I examine the mysql.proc status, I get:

mysql> check table mysql.proc;
+------------+-------+----------+----------+
| Table      | Op    | Msg_type | Msg_text |
+------------+-------+----------+----------+
| mysql.proc | check | status   | OK       |
+------------+-------+----------+----------+
1 row in set (0.02 sec)

However, I still get the same error when re-running the mysqldump command.

I have approximately 2000 store procedures. Can that be the reason for the error?

I just had the same problem, this was fixed by issuing a command, assuming mysql data dir is on /var/lib/mysql/mysql

myisamchk -q -r /var/lib/mysql/mysql/proc

Source : http://dev.mysql.com/doc/refman/5.0/en/myisam-repair.html


What OODBMS are available for PHP? I need an OODBMS for my website, to handle hierarchies and inheritance.
I use PHP and I have a feeling I am asking too much and will have to do with regular RDBMS...

My personal opinion, is that there isn't much sense in using OODBMS over RDBMS.

Due primarily to a lack of a standard language, comparable to that of SQL, OODBMS are often niche, proprietary, commercial systems. You'll be hard-pressed to find a viable option for a language like PHP because of that. You'd probably have better luck finding something if you were using Java or C++. I did find this comparison table, which might be of interest to you; though it looks like it was last updated in 2009; so be mindful.

Perhaps you could consider instead, a RDBMS + ORM solution using Doctrine.


How to apply a newly-created Domain to an existing Column? In Postgres 9.4 Beta 1, I successfully created a domain of type VARCHAR, fuel_domain_. The domain checks that a value is any of 5 possible strings, coal, gas, wind, hydro, other.

CREATE DOMAIN fuel_domain_
  AS character varying
  COLLATE pg_catalog."default"
  NOT NULL
  CONSTRAINT fuel_check_ CHECK (VALUE::text = ANY (ARRAY['coal'::character varying, 'gas'::character varying, 'hydro'::character varying, 'wind'::character varying, 'other'::character varying]::text[]));
ALTER DOMAIN fuel_domain_
  OWNER TO postgres_admin_;

  ➤ How do I apply this new domain to an existing column?

The doc seems to say that a domain should be specified in place of the usual data type when declaring a column.

So I tried and failed to re-define the column’s data type from VARCHAR to the new domain. The base data type of the domain is the same, so the data types should not be in conflict. This SQL for a table unit_ and column fuel_ fails with a syntax error at or near "COLUMN":

ALTER COLUMN unit_.fuel_ SET DATA TYPE fuel_domain_;

WORKAROUNDAs a workaround, I was able to delete the column and successfully recreate it with this SQL:

ALTER TABLE unit_ ADD COLUMN fuel_ fuel_domain_;

Incorrect SQL was the problem. The ALTER COLUMN must be part of an ALTER TABLE.

This SQL does indeed alter the data type of the existing column from VARCHAR to the newly-created domain.

ALTER TABLE unit_
ALTER COLUMN fuel_
SET DATA TYPE fuel_domain_;


postgresql grant user privilages to dynamically created tables We have 2 postgresql users. One user (dbworkeruser) for creating tables dynamically (from C# worker) and other user (injestworkeruser) for inserting data into these tables (from another C# worker as well).
The main issue that we have is that, every time we do drop/create new tables dynamically (on runtime) we get exception that the user injestworkeruser doesn't have enough permission to select or insert data from/into these newly created tables. This is the created user:
CREATE USER injestworkeruser WITH
  LOGIN
  NOSUPERUSER
  INHERIT
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION;

Is there anyway to grant the user permissions to apply CRUD operations to any newely created table?
This one didn't work for me as this applies to the existing tables:
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO injestworkeruser;

Set your default privileges for the schema using ALTER DEFAULT PRIVILEGES


Shoud I uninstall mongodb 2.6 before upgrade to 3.0? I installed mongodb 2.6 in my machine. This week, I followed the instructions in the subsection of Install MongoDB on http://docs.mongodb.org/manual/tutorial/install-mongodb-on-ubuntu/. I installed MongoDB 3.0/. But I found that the date of some files in the mongodb package is in Feb. Why? Should I un-install mongodb 2.6 before upgrade to 3.0? my cluster is running all the time....

No its not necessary to uninstall.
I don't really like yum or apt for installing mongoDB. I suggest you to download the binaries from https://www.mongodb.org/dl/linux/x86_64 , linux/mongodb-linux-x86_64-ubuntu1404-3.1.0.tgz (or the v12) and use it to start mongodb.


As pointed out, this is better solved by Gaps and Islands.

WITH T
 AS (SELECT *,
            DENSE_RANK() OVER (PARTITION BY Target ORDER BY PollTime) -
            DENSE_RANK() OVER (PARTITION BY Target, Value ORDER BY PollTime) AS Grp
     FROM   [schema].[Table]
     )
SELECT Target,
       MIN(PollTime) AS Start,
       MAX(PollTime) AS Finish,
       DATEDIFF(mi, MIN(PollTime), MAX(PollTime)) AS Duration
FROM   T
GROUP  BY Target,
          Grp
HAVING MAX(Value) is null
ORDER  BY Start

Original source for code:  Getting each status change in a table


MySQL Pivot Table Dynamic Rows I have a table like the one below:

+-------+-----------+-----------+-----+--------+-------------+
| grade | firstname | lastname  | age | gender | student_num |
+-------+-----------+-----------+-----+--------+-------------+
|     2 | Stephen   | Harper    |  12 | male   |           1 |
|     1 | Jennifer  | Solomon   |  10 | female |           1 |
|     4 | James     | Barney    |   9 | female |           1 |
|     3 | Collins   | Balmer    |   8 | female |           1 |
|     4 | Kehinde   | Adefemi   |  12 | male   |           2 |
|     2 | Benjamin  | Salem     |  14 | female |           2 |
|     3 | Praise    | Olawale   |   9 | male   |           2 |
|     1 | Janet     | Pelumi    |   7 | male   |           2 |
|     3 | Ire       | Adora     |  11 | female |           3 |
|     2 | Manny     | Grace     |  13 | male   |           3 |
|     2 | Esther    | Benson    |   7 | female |           4 |
|     3 | Stan      | Collimore |   6 | female |           4 |

Please help me with the SQL that will generate the resulting table below from the one above.

grade|s1_fname  |s1_age|s1_gender|s2_fname |s2_age|s2_gender|
  1  | Jennifer | 10   |female   | Janet   | 7    |  male   |
  2  | Stephen  | 12   |male     | Benjamin| 14   |  female |
  3  | Collins  | 8    |female   | Praise  | 9    |  male   |
  4  | Kehinde  | 12   |male     | Myedan  | 8    |  male   |

where s1 means student1. The 1 should be gotten from from student_num column

where s2 means student2. The 2 should be gotten from from student_num column
...

My database server is MySQL.

I Have tried the code below

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
          'SUM(CASE WHEN `grade` = ',
          `grade`,
          ' THEN firstname ELSE 0 END) AS `s',
          `student_num`, '_fname`'
        )
    ) INTO @sql
FROM students;
SET @sql = CONCAT('SELECT grade, ', @sql, ' FROM students GROUP BY grade');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

You could do something along the following lines:

select
    s1.grade,
    s1.firstname as s1_fname,
    s2.firstname as s2_fname,
    s3.firstname as s3_fname,
    s4.firstname as s4_fname
from #grades as s1
-- Self join the table for second student
left outer join grades as s2
    on s1.grade = s2.grade
    and s2.student_num = 2
-- Self join the table for third student
left outer join grades as s3
    on s1.grade = s3.grade
    and s3.student_num = 3
-- Self join the table for forth student
left outer join grades as s4
    on s1.grade = s4.grade
    and s4.student_num = 4
where s1.student_num = 1
order by s1.grade;

This joins the table to itself once for each set of columns (s1, s2, etc.). You could write some dynamic SQL to generate this based on the maximum student_num or just run select max(student_num) and make sure you have enough columns to catch all the values. (If you don't you'll need to add another join like the others).

Edit: This assumes that the grade and student_num columns combine to create a unique key. If you have duplicates (e.g. two students with grade 1, student_num 1), then you'll get duplicate rows in your results.


SQL Server 2019 - Polybase -> Cosmos DB (MongoDB Mode) Error with dates I'm new to polybase. I have linked my SQL Serer 2019 to a third parties Azure cosmos and i am able to query data out of my collection. I am getting an error out when i try to query date fields though. In the documents the dates are defined as:
"created" : {
    "$date" : 1579540834768
},

In my external table i have the column defined as
[created] DATE,

I have tried to create the column as int and nvarchar(128) but the schema detection rejects it each time. (I have tried to create a field created_date but the schema detection also disagrees that this is correct.
When I try a query that returns any of the date fields I get this error:
Msg 105082, Level 16, State 1, Line 8
105082;Generic ODBC error: [Microsoft][Support] (40460) Fractional data truncated while performing conversion. .
OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned message "Unspecified error".
Msg 7421, Level 16, State 2, Line 8
Cannot fetch the rowset from OLE DB provider "MSOLEDBSQL" for linked server "(null)". .

This happens if i try and exclude null values in my query - even when filtering to specific records where the date is populated (validated using the Azure portal interface)
Is there something i should be doing to handle the integer date from the json records; or another type i can use to get my external table to work?

Found a solution. SQL Server recommends the wrong type for mongodb dates in the schema. Using DateTime2 resolved the issue. Found this on a polybase type mapping page in msdn.


How to re-enable read ahead (prefetch) feature in SQL Server after disabling it? In Microsoft SQL Server, I've disabled the read ahead (prefetch) feature using

    DBCC TRACEON(652,-1)

But does anyone know how to enable the read ahead feature?

Before I disabled the read ahead, using

    set statistics io on

I get this result:

    Scan count 1, logical reads 529, physical reads 4, read-ahead reads 1192

Now after disabling read ahead using the above DBCC query, I get this:

    Scan count 1, logical reads 44, physical reads 19, read-ahead reads 0

I'd like to be able to disable/enable the prefetch feature freely so I could compare among different queries. I googled everywhere but didn't find an answer for that... Anyone knows how to re-enable it? Thanks!

All you need do is turn the trace flag off globally:

DBCC TRACEOFF (652, -1);

A service restart is not required.

To control read-ahead for the current session only, simply omit the -1.

DBCC TRACEON (652);
...
...tests
....
DBCC TRACEOFF (652);

This trace flag is only minimally documented, so for anything more than educational purposes on an isolated test system, it makes sense to contact Microsoft Support for advice.

Note: QUERYTRACEON is not effective for trace flag 652.


Using ODBC Connection to Access Firebird Database I have an ODBC connection to a Firebird database that tests successfully.  Is there a way to use this ODBC connection within SQL  Server 2008 R2 to access the tables?  I haven't used Linked Servers before and wasn't sure if that was an option...and if so, what would be the provider needed/desired?

You should be able to create a Linked Server to almost any ODBC data source.

I would suggest trying the Microsoft OLE DB Provider for ODBC Drivers.

While you need to enter something in the Product Name field, I believe you can enter anything, such as "Firebird".

For Data Source, enter the name of the ODBC System DSN you've previously created.

You'll need to confirm the other options for "Security" and "Server Options", and test to see if it works.


Should select queries ever take longer than 1 second? I have select queries in my slow-log file that take 40-120 seconds.

When I consider something like a Google search, how fast that is, it makes me re-evaluate my entire database structure.

Since Google is literally searching billions of URLs in factions of a second, should I be worrying about 40 second queries?

# Query_time: 58.143590  Lock_time: 0.023085 Rows_sent: 3  Rows_examined: 1092421
use scrapers;
SET timestamp=1450286867;
SELECT * FROM `apps` WHERE dev_name = '' LIMIT 10;

Yes, you should be worried*. A simple query like this should be running in milliseconds and not examine a million rows.

My guess is that there are less than 10 rows that match the condition (evidence: Rows_sent: 3) and the lack of any index results in a full table scan (evidence: Rows_examined: 1092421). The explain output for the query would help us investigate if there is any other issue.

Good news is that a simple index on (dev_name) will probably solve the issue, for this query. Bad news is that you have to go through all the slow query, one by one, run explain on them, examine the tables and their indexes and either rewrite the queries or add appropriate indexes.

*: Of course, not because Google searches are fast. Any comparison between the super farms that a giant company uses and a single server is moot.


Best Data compression technique in Oracle We are trying to create a Archival Strategy for Oracle Databases. Archiving old Records to Compressed Oracle tables has come out as a winner.

We have noticed 55% byte saving for Tables with only Text and 25% byte saving for Tables with Attachments (PDF, Word, Excel).

Please suggest

If anyone has faced problems with Compressed Tables.
If we have better options in Oracle to archive data. We need to refer this archived data on daily bases.

Performance is not a big concern.

We are looking for Oracle 11g Solution only.

As longs as you don't do ANY updates on compressed tables you are fine. The old fashioned block level compression works fine when you can add data in batches. The old block level compression only works on loads, not on regular single record inserts.

Assuming that archiving goes in batches block level compression is OK.
PDF's will hardly compress using the available compressors in Oracle but when things like layout and font info is identical for many files, 7zip could be your friend but AFAIK this is not available in the database.

If you want to compress transactional data advanced compression is for you but it has a performance penalty.

When your data accesses are in scans, the compression could even give you a performance benefit because there are less io's to do. This is only true when you have plenty CPU available to do the inflight de-compression.

If you have to do updates on compressed tables, your performance is dead and your volume is back to the uncompressed volume. So be careful. You won't be the first one who is hit by this feature. This is also very true for the archive compression mode in Advanced Compression.


Database Design (Stores + Products) I currently have 2 tables in my DB, stores and products. Each product has it's own ID 1-2000, and each store has its own id 1-200.

My question on DB design, is how do i keep track of which products each store has?

example

store 1 has products 1, 5, 10, 200
store 2 has products 2, 5, 7, 100
store 3 has products 1, 7, 10, 300
etc...

Should I have a column in my stores table that has id's stored as a string separated by a column such as {1;5;10;20} (with the possibility of thousands of entries in the 1 string) ?
Should I have another table that is just [id|storeID|productID] (with the possibility of stores(x)products in the table) ?

The first option you present is a "NoSQL" design pattern & doesn't allow for easy querying in a normal RDBMS.

Your second option is the most suitable. Have a 3rd table that tracks which products are in which store. Possibly called "Stock" - you could add a count to this if you wanted to track how many of each item each store has.


Is it possible to generate workload for hammerora (hammerdb for oracle) from AWR tables? I'm trying to generate workload from our production databases but rather than running a trace file in production, I would prefer using the AWR tables since we have a license for it.

Is it possible?

Thank you.

In short I would say: NO.

If you want to invest lot's of time ... you could generate SQL's and usage frequencies from awr sql history tables but the parameters will be the same for all occurences of an SQL since the bind variables are not recorded for every execution.
I am not sure about hammerora but from swingbench I know you can create your own transactions and for that you could use awr sql history as a guide on how many tx/s to generate. It still remains a synthetic load.

Smarter would be to capture the databases input from a proxy between the application server and the database.

Oracle Replay has a price, for a reason.


Get multiple columns from a function depending on outer group by I have first a selection of trip_id (for a train or a bus e.g.) in a (sub-)Table A

trip_id

350
351
360

and want to use a self-written function which takes the used trip_id and gives back the a TABLE with trip_id, used roads (in form of a road_id) and the sequence seq of stops for this trip (here for example trip no. 350):

SELECT * FROM getroutepertrip(350)
-----
trip_id   road_id    seq

350       a1        1
350       g4        2
350       a2        3
 .        .         .
 .        .         .
 .        .         .

The desired oucome of the whole problem should be like this above but for all the trips from the CTE subselect. Unfortunately, my function only can take a single trip_id. My first and only attempt is of course wrong since a subquery can't contain multiple columns

WITH A AS (SELECT DISTINCT trip_id FROM stop_times)

SELECT trip_id,  (SELECT * FROM getroutepertrip(trip_id))
FROM A
GROUP BY trip_id
ORDER by trip_id

Hopefully somebody can help me here, it feels like I'm mentally in a deadlock...

You want to do a LATERAL join, by putting the the function in the FROM list.  If you just put the function in the from list after table "a", you don't need the LATERAL keyword, as joins to functions are automatically lateral.  If you wrap it in a dummy "select * from", then you do need the keyword:

WITH A AS (SELECT DISTINCT trip_id FROM stop_times)
SELECT route.*
FROM A CROSS JOIN LATERAL (SELECT * FROM getroutepertrip(trip_id)) as route
GROUP BY trip_id
ORDER by trip_id

or

WITH A AS (SELECT DISTINCT trip_id FROM stop_times)
SELECT route.*
FROM A, getroutepertrip(trip_id) as route
GROUP BY trip_id
ORDER by trip_id


Mongo repairDatabase procedure I need to run a repairDatabase in mongo in order to reclaim some os disk space. Can anyone provide feedback on the following procedure: let me know if its correct, if I'm missing anything, if the steps are in order, etc.

The setup is a primary/secondary on separate servers. They are both about out of disk space. My plan is to repair the database on each one separately after the other completes. As I am out of disk space, I will be using an external block storage from my cloud provider to use as a repair path. Mongo is currently running via sudo service mongodb start. To complete this it seems that I'll run the following:

On the primary I step down using rs.stepDown()
I shutdown mongo using rs.shutdownServer() - Is this correct? For the next step it seems that I shouldn't run it if Mongo already has another instance running.
On the primary I run mongod --config /etc/mongodb.conf --repair --repairpath PATH_TO_BLOCK_STORAGE
When complete, do I need to copy the data in the repair path over to the original (/var/lib/mongodb)?
Once back up, I restart Mongo using sudo service mongodb start
I then wait for the oplogs to catch up and for the mongos to be in sync.
Once they primary and secondary are in sync I then go onto mongodb-perf-02 and run rs.stepDown() so that mongodb-perf-01 is the primary again.
Once complete I then repeat this on the secondary.

The solution to this issue was as follows:
Starting with the secondary:

Inside of Mongo I ran the following:

db.shutdownServer()
exit

On the command line:

rm -rf /var/lib/mongodb/*
sudo service mongodb start

It then takes a couple of hours to resync. Once resynced I did the following on the primary:

rs.status() //To make sure that the everything was synced
rs.stepDown(9200)
db.shutdownServer()
exit

On the command line I ran:

rm -rf /var/lib/mongodb/*
sudo service mongodb start

Then in Mongo I ran:

rs.status() //To make sure that everything was synced
rs.freeze(0) //To remove the re.stepDown(9200) lock

On the secondary I ran:

rs.stepDown()

Note: If possible, back up all of your data first.


Query cycles between suspended and runnable without ever completing I have an insert statement, extracting data through multiple inner joins from around 8 tables. It is getting into suspended state then runnable and vice versa forever; never coming back to running state.

Here is the estimated execution plan: https://www.brentozar.com/pastetheplan/?id=Sy6gPl-6L

While running the trace on this particular process, I see, it holds lock on tempdb and also on one user database but not running any statements on them. Simply its state is changing in between suspended & runnable. When I kill and re-run, it is getting executed normally. I see there are no processes or jobs conflicting with this insert statement.

The wait types are "io_completion" and "sos_scheduler_yield" while the query got stuck. I have used sys.syprocesses and sp_who2 active also dm_exec_requests to monitor. I have run trace while the process is running, once the process went into suspended state showing up "io_completion" to "sos_scheduler_yield" and vice versa.

I am not able to figure out why it is happening. Could you please put some light on it and advise any solutions.

Explanation of behavior

Some of the causes of the IO_COMPLETION wait type are:

  Writing intermediate sort buffers to disk (these are called ‘Bobs’)
  Reading and writing sort results from/to disk during a sort spill

There are two sorts that could be spilling, which uses tempdb.

A source of the slowness could also be one the unfortunate "many to many merge join" in the middle of the execution plan (which also uses tempdb, although it doesn't cause IO_COMPLETION waits):

The estimated plan shows ~2 GB of data coming out of that merge join - and that amount of data could be even higher if the estimates are off.

You mentioned the problem is intermittent, which could be because of tempdb contention (if other queries are running at the time).

Suggestions for improvement

Temp table rewrite

The best option I can think of would be to break the query up into smaller chunks.  For instance, you could just select the trans_header rows that meet your where clause into a #temp table.  Then use that temp table in the main query instead of trans_header.

This could improve estimates and let the optimizer produce a better plan - potentially avoiding any spills or other tempdb activity.

That, generally, looks like this.  Sorry if there are any slight typos, it's tough not having intellisense

SELECT
    a.[term_id],
    a.[trans_ref_no],
    a.supplier_no,
    a.cust_no,
    a.trans_id,
    a.carrier,
    a.folio_yr,
    a.folio_mo,
    a.folio_no,
    a.transaction_date
INTO #trans_header_temp
FROM [TOPHAT].[trans_header] AS a with (nolock)
WHERE
    a.folio_yr=year(getdate())-1
    AND a.transaction_date <>' 00000';

INSERT INTO [TOPHAT].[terminal_volume]
           ([term_id]
           ,[terminal_name]
           ,[folio_yr]
           ,[folio_mo]
           ,[folio_no]
     ,[folio_year_month]
           ,[product_id]
           ,[prod_name]
           ,[product_id_name]
           ,[supplier_no]
           ,[supplier_name]
           ,[supplier_no_name]
           ,[customer_no]
           ,[customer_name]
     ,[carrier_no]
     ,[carrier_name]
           ,[bbl]
           ,[gallon]
           ,[customer_no_name]
           ,[trans_code]
           ,[transaction_description]
           ,[transaction_code_description]
           ,[transaction_year]
           ,[transaction_month]
           ,[transaction_date]
           ,[getdate])
select a.[term_id],
 tp.name as terminal_name,
 a.folio_yr,
 a.folio_mo,
 a.folio_no,
 cast(a.folio_yr + '-' + a.folio_mo +'-01' as date) as folio_year_month,
 p.prod_id as product_id,
 p.prod_name as prod_name,
 p.prod_id+' '+p.prod_name  as product_id_name,
 s.supplier_no,
 s.supplier_name,
 s.supplier_no+' '+s.supplier_name as supplier_no_name,
 cu.cust_no as customer_no,
 cu.cust_name as customer_name,
 a.carrier as carrier_no,
 ca.name as carrier_name,
 sum((convert(decimal(12,2),b.net)*(case when b.sign is null then 1 else -1 end))/42) as 'bbl',
 sum(convert(decimal(12,2),b.net)*(case when b.sign is null then 1 else -1 end)) as gallon,
 cu.cust_no+' '+ cu.cust_name as customer_no_name,
 tv.trans_code,
 tv.trans_desc as transaction_description,
 tv.trans_code+' '+tv.trans_desc as transaction_code_description,
 '20'+ left(a.transaction_date, 2) as transaction_year,
 substring(a.transaction_date, 3, 2) as transaction_month,
  case when isdate(a.transaction_date) = 0 then null
   else
   cast(a.transaction_date as date) end as transaction_date,
 cast(getdate() as date)  as [getdate]
   from
#trans_header_temp as a with (nolock)
 inner join [TOPHAT].[terminal_profile] as tp  with (nolock)
  on a.term_id = tp.term_id
 inner join [TOPHAT].[trans_products] as b with (nolock)
  on a.[trans_ref_no] = b.[trans_ref_no]
 inner join [TOPHAT].[product] as p with (nolock)
  on p.term_id = a.term_id
  and p.prod_id = b.prod_id
 inner join TOPHAT.supplier as s with (nolock)
  on s.supplier_no = a.supplier_no
 inner join TOPHAT.customer as cu with (nolock)
  on cu.cust_no = a.cust_no
  and cu.supplier_no = a.supplier_no
 inner join TOPHAT.trans_value as tv with (nolock)
  on tv.trans_code = a.trans_id
 inner join [TOPHAT].[carrier] as ca with (nolock)
  on ca.term_id = a.term_id
  and ca.carr_no = a.carrier

group by
a.[term_id],
tp.name ,
a.folio_yr,
a.folio_mo,
a.folio_no,
p.prod_id,
p.prod_name,
p.prod_id+' '+p.prod_name,
s.supplier_no,
s.supplier_name,
s.supplier_no+' '+s.supplier_name,
cu.cust_no,
cu.cust_name,
cu.cust_no+' '+ cu.cust_name,
a.carrier,
ca.name,
tv.trans_code,
tv.trans_desc,
tv.trans_code+' '+tv.trans_desc,
'20'+ left(a.transaction_date, 2),
substring(a.transaction_date, 3, 2) ,
a.transaction_date,
cast(a.folio_yr + '-' + a.folio_mo +'-01' as date)

Fix implicit conversions

Speaking of estimates, you have several implicit conversion warnings.  It's difficult to advise on how to deal with them, since we don't have table and index definitions, but you should review them to see if they can be avoided.  Especially the ones in the WHERE clause:

Join hint

One low-effort approach would be to try and avoid that specific merge join with a join hint.  This might cause performance to get worse, though, because it will force the order of the joins as written, which limits the optimizer can do quite a bit:

 inner HASH join [TOPHAT].[product] as p with (nolock)
  on p.term_id = a.term_id
  and p.prod_id = b.prod_id

Note that this wouldn't likely help with sort spills.


How to capture runtime parameters with extended event? I use rpc_completed event in order to capture executed sprocs. I am also to view the statement and passed parameters by using the following way:
right-click extended event -> Watch Live Data and finally see statement+parameters
However I want to see the captured data in table format, but I wasn't able to implement it. When I run below query it shows statement without parameters. How can I see parameters in the table form? Need your suggestions.
IF OBJECT_ID('tempdb..#capture_waits_data') IS NOT NULL
DROP TABLE #capture_waits_data
SELECT CAST(target_data as xml) AS targetdata
INTO #capture_waits_data
FROM sys.dm_xe_session_targets xet
JOIN sys.dm_xe_sessions xes
ON xes.address = xet.event_session_address
WHERE xes.name = 'LongRunningQueries'
AND xet.target_name = 'ring_buffer';
--*/
/**********************************************************/
SELECT
CONVERT(datetime2,SWITCHOFFSET(CONVERT(datetimeoffset,xed.event_data.value('(@timestamp)[1]', 'datetime2')),DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS datetime_local,
xed.event_data.value('(@name)[1]', 'varchar(50)') AS event_type,
xed.event_data.value('(data[@name="statement"]/value)[1]', 'varchar(max)') AS statement,
xed.event_data.value('(data[@name="duration"]/value)[1]', 'bigint')/1000 AS duration_ms,
xed.event_data.value('(data[@name="physical_reads"]/value)[1]', 'bigint') AS physical_reads,
xed.event_data.value('(data[@name="logical_reads"]/value)[1]', 'bigint') AS logical_reads
FROM #capture_waits_data
CROSS APPLY targetdata.nodes('//RingBufferTarget/event') AS xed (event_data)
WHERE 1=1 and xed.event_data.value('(action[@name="username"]/value)[1]', 'varchar(255)') <> 'NT SERVICE\SQLSERVERAGENT'

Here is actual used extended event:
CREATE EVENT SESSION [LongRunningQueries] ON SERVER
ADD EVENT sqlserver.rpc_completed(
    ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.session_id,sqlserver.username)
    WHERE ([duration]>(4000000) AND [sqlserver].[database_name]='mydb')),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.session_id,sqlserver.username)
    WHERE ([duration]>(4000000) AND [sqlserver].[database_name]='mydb'))
ADD TARGET package0.ring_buffer(SET max_events_limit=(0),max_memory=(102400))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

If the procedures are being called, you should absolutely see the parameter values in the 'statement' column. I have a blog post that demonstrates exactly this issue. There's literally nothing magical to make this happen. It's just a part of the statement.
Now, to make the statement appear in the grid in the Live Data Viewer, you can either right click in the details pain and select the menu choice to show the statement, or, you can select the column picker from the toolbar. Here's a video showing how that works.


how to encrypt one column of a table without using always encrypted? I have been working on a solution to synchronise logins (using T-SQL) between 2 servers, or between AlwaysOn nodes, inspired by sqlsoldier.

It requires a linked Server.

When run, this procedure outputs the following set of scripts that need to be applied in the current server for synchronising the logins (example):

 Create Login [MY_COMPANY_UK\DW_and_Extracts] From Windows;
 Create Login [MY_COMPANY_UK\AuPairDWETL_TS] From Windows;
 Exec  sp_addsrvrolemember @rolename = 'sysadmin', @loginame = 'NT SERVICE\MSSQLSERVER';
GRANT VIEW ANY DEFINITION  To  [MY_COMPANY_UK\RGDLMonitoring];
GRANT CONNECT SQL  To  [MY_COMPANY_UK\DW_and_Extracts];
GRANT CONNECT SQL  To  [MY_COMPANY_UK\AuPairDWETL_TS];
GRANT CONNECT On ENDPOINT::[SSBEndpoint] To [MY_COMPANY_USA\sqlservice];

I would like to log all these commands to a table before I apply them to my current server, but I understand these are sensitive information, and I would like to be able to encrypt it and be able to decrypt.

What could I achieve this?

I have already seen this:

Is It Possible to Encrypt varbinary(max) Column Using Always Encrypted Feature?

How could I achieve this, with minimal effort, and not affecting anything else on my systems?

(I currently don't use always encrypted)

SQL Server has had column encryption with server-managed keys since SQL 2005.  See Encrypt a Column of Data.

AlwaysEncrypted uses client-side keys.


Oracle wait type enq: TM - contention I’m administrating an Oracle 12c database which shows the wait type: enq: TM – contention in certain time periods (up to 30% of total waits). During this periods the applications does not allow new logins and users are complaining about timeouts and long response time. enq: TM – contention is related to missing indexes on foreign key constrains. How do I find these missing indexes? Should a DBA solve this or should the software company provide the indexes?

Could be many reasons for TM waits but you can try running this script to check for un-indexed foreign keys first.

select cc.owner, cc.table_name, cc.column_name, cc.position
from dba_cons_columns cc
where
 cc.owner not in ('SYS','SYSTEM')
 and position is not null
minus
select i.index_owner, i.table_name, i.column_name, i.column_position
from dba_ind_columns i
where
 i.index_owner not in ('SYS','SYSTEM');


SqlPackage importing of very large bacpacs to Azure I want to import a database from a .bacpac file to a SQL Server in Azure. I read the document.
It says that there is a flag called DatabaseMaximumSize=(INT32).
I wanted to know if there's a limit that sqlpackage can support? For example if I got 8 gb of RAM available, will Sqlpackage be able to load larger .bacpacs than that, meaning it doesn't load it all to the memory?

Is this SQL Server on a VM or Azure SQL Database / Azure SQL Managed Instance ?
Probably one of the last 2 as you could just use a backup to restore to SQL on a VM, If you find the import runs to slow just scale up your machine until the import is completed


Unknown option '--no-beep' I installed a new version of MySQL server (previously 5.0, now 5.5) and I copied the data folder in to the new one. Now I should perform mysql_upgrade. I opened the command prompt (server is located on Windows Server 2012) and used cd to get in the bin folder. I wrote the file name to run the upgrade. The result was mysql_upgrade: unknown option '--no-beep'. What's that? I tried to do some research on Google, but no one seemed to have the same error.

I'm confused. Even the MySQL documentation doesn't seem to tell anything about the error.

The no-beep option causes the mysql command line client not to beep when errors occur.

If that's an option you want, it should be in the [mysql] section of your my.cnf.  This error suggests that it's in the [client] section of your my.cnf, which causes mysql_upgrade to throw the error since the option isn't recognized.

Check your my.cnf and either remove the line or place it in the [mysql] section instead of [client] and you should be good.

  The [client] option group is read by all client programs (but not by
  mysqld). This enables you to specify options that apply to all
  clients. For example, [client] is the perfect group to use to specify
  the password that you use to connect to the server. (But make sure
  that the option file is readable and writable only by yourself, so
  that other people cannot find out your password.) Be sure not to put
  an option in the [client] group unless it is recognized by all client
  programs that you use. Programs that do not understand the option quit
  after displaying an error message if you try to run them.

-- http://dev.mysql.com/doc/refman/5.5/en/option-files.html


Dimensional Model for inheritance in dimensions How are dimensions typically modeled in a dimensional model if one dimension logically inherits from another dimension?

For example, assume I have an Employee dimension and a Driver dimension, where the Driver has all the attributes of an Employee but also has a Truck Number attribute.  How should these dimensions be modelled?

While in a traditional OLTP system you may need to be concerned about normalizing and inheritance, in a typical data warehouse system you will want to denormalize to accelerate query responses.

In this case, I would design an Employee_Dimension table which had all relevant columns about an employee which you might want to query against, along with an additional column which I might call employee_role, which I would fill in with entries like Driver, Manager, etc. and then have another column which had either the truck_number filled for employees who were a driver, or leave the column as NULL when they aren't drivers. Of course, you can add even further columns to include information for other employee roles.

Long story short, if you're building a typical star schema, you can achieve faster query results by using heavy denormalization, at the cost of storage space of course.


is there any reason to explicitly grant the server permission CONNECT SQL? I have been using this script from Kenneth Fisher to get the server permissions.

-- Server Permissions
SELECT Grantee.principal_id AS GranteePrincipalId, Grantee.name AS GranteeName,
   Grantor.name AS GrantorName, Permission.class_desc, Permission.permission_name,
   Permission.state_desc,
   'REVOKE ' +
       CASE WHEN Permission.class_desc = 'ENDPOINT' THEN NULL
       WHEN Permission.[state]  = 'W' THEN 'GRANT OPTION FOR ' ELSE '' END +
       ' ' + Permission.permission_name COLLATE Latin1_General_CI_AS +
       ' FROM ' + QUOTENAME(Grantee.name COLLATE Latin1_General_CI_AS)  + '; ' AS RevokeScript,
   CASE WHEN Permission.class_desc = 'ENDPOINT' THEN NULL
       WHEN Permission.[state]  = 'W' THEN 'GRANT' ELSE Permission.state_desc COLLATE Latin1_General_CI_AS END +
       ' ' + Permission.permission_name COLLATE Latin1_General_CI_AS +
       ' TO ' + QUOTENAME(Grantee.name COLLATE Latin1_General_CI_AS)  + ' ' +
       CASE WHEN Permission.[state]  = 'W' THEN ' WITH GRANT OPTION ' ELSE '' END +
       ' AS '+ QUOTENAME(Grantor.name COLLATE Latin1_General_CI_AS) + ';' AS GrantScript
FROM sys.server_permissions Permission
JOIN sys.server_principals Grantee
   ON Permission.grantee_principal_id = Grantee.principal_id
JOIN sys.server_principals Grantor
   ON Permission.grantor_principal_id = Grantor.principal_id
WHERE 1=1
  --AND Grantee.name LIKE '%TAllard%'

In my live systems I get for this user in particular the following permissions (server):

the VIEW ANY DEFINITION permission I explicitly granted but the other permission CONNECT SQL was not explicitly granted. I believe it is automatically generated when you create a login to the server, and a user for that login on any database that live there.
that implies a connection.

considering this, is there any specific reason why I should grant the CONNECT SQL at servel level?

When you create a login, it is granted CONNECT SQL.

It has nothing to do with user.
When you create a user, it does not automatically create a corresponding login,
and CONNECT SQL is a server level permission that can be granted only to a server level principal, i.e. to login or a server role.

The common use of this permission is the following:

Suppose you have a Windows group as a login, in this case all the members of this group can access the server. One day it happens that one member of the group must have no access to server, but it cannot be removed from Windows group that accesses the server. In this case you can DENY CONNECT SQL to that individual account, so the whole group will still access the server and only that account will not


Error connecting to the server:FATAL:couldnot open file"base\12135\11946":No such file or directory I have a issue from PostgreSQL 9.4 .The issue is given below:

  An Error has occurred: Error connecting to the server:FATAL:could not
  open file"base\12135\11946":No such file or directory.

This issue is in Windows 7.

A file in the base directory, where postgres stores it's data, is missing. This can be due to a corrupted filesystem or some program deleted the file.

At this point the first step you should do is to try and do a pg_dumpall. It will likely fail at the point where it encounters that missing file and then you will know what was impacted. Additionally, if you have other DBs besides the one impacted suggest you try to also back them up individually to make sure those other DBs don't have an issue.

After you identify what table(s) were impacted you then need to do one of these:

Restore the missing data from backups if you have them
If you don't have backups about the best you can do is to try and backup the individual object and see if you can get at least partially some of the data. The rest is likely lost.
After you have either deleted and restored, or just outright deleted, the table then do the backup of the impacted DB again to confirm if any other objects are also missing files.
Audit access to the server. Make sure nobody that should not have access has access.
Ask the people who have access to the server if they were doing any cleanups recently and they were deleting some files. Perhaps someone deleted the wrong file(s) by mistake and did not notice it.


Get the CreatedAt Timestamp when the number of rows is equal to some number I have a simple table in SQL Server.

id
OtherId
createdAt

1
A
2019-11-28 05:03:03.983

2
A
2019-11-28 05:03:06.937

3
B
2019-11-28 05:03:12.983

I want to retrieve a timestamp when the number of rows becomes equal to or greater than a fixed number, let us say 10, where OtherId is A.
So, if the number of rows with A in the OtherId column is more than 10, then return the value from createdAt.

You could try along

SELECT
  id
  , createdAt
FROM (
  SELECT
    id
    , createdAt
    , RANK() OVER (PARTITION BY OtherId ORDER BY createdAt) rnk
  FROM Table1
) AS S
WHERE rnk = 8
;

The inner SELECT groups the records by OtherId, orders and ranks them by createdAt. The outer SELECT picks the record by some number.

The CTE (common table expression) @Akina mentioned, would pull the sub-select to the top:

WITH
S (id, createdAt, rnk) AS (
  SELECT
    id
    , createdAt
    , RANK() OVER (PARTITION BY OtherId ORDER BY createdAt) rnk
  FROM Table1
)
SELECT
  id
  , createdAt
FROM S
WHERE rnk = 8
;

Do not see much difference in this case. But the technique helps a lot for more complex settings as it allows to grow the statements and is usually more readable.

If, however, only a single OtherId is of interest, there is no need for the window function:

SELECT
    id
    , createdAt
FROM Table1
WHERE OtherId='A'
ORDER BY createdAt
OFFSET 7 ROWS
FETCH FIRST 1 ROW ONLY
;

On the other hand: Returning either a specific occurrence or the maximum could be done via a UNION:

WITH
S (otherId, createdAt, rnk, cnt) AS (
  SELECT
    OtherId
    , createdAt
    , RANK() OVER (PARTITION BY OtherId ORDER BY createdAt)
    , COUNT(createdAt) OVER (PARTITION BY OtherId)
  FROM Table1
)
SELECT
  otherId
  , createdAt
FROM S
WHERE rnk = 8
UNION ALL
SELECT
  otherId
  , MAX(createdAt)
FROM S
WHERE cnt < 8
GROUP BY otherId
ORDER BY otherId
;

See it in action: db<>fiddle

Please comment, if and as this requires adjustment / further detail.


Postgresql update multiple rows and skip row on error I have a query which updates single column of multiple records using column value from another table. For example below are the tables--

Table A

+-----------------+---------------------+------+
| id(primary_key) | user_id(unique_key) | f_id |
+-----------------+---------------------+------+
| 1               | 1                   | 11   |
+-----------------+---------------------+------+
| 2               | 3                   | 22   |
+-----------------+---------------------+------+

Table B

+----+------+
| id | f_id |
+----+------+
| 1  | 11   |
+----+------+
| 3  | 22   |
+----+------+

Now I tried to update user_id column for all records in Table A using id column from Table B on basis of f_id column which is common using below query.

UPDATE
    A
SET
    user_id = B.id
FROM
    B
WHERE A.f_id = B.f_id

user_id column in Table A is unique which generates an error on bulk update as:

  ERROR: duplicate key value violates unique constraint "A_user_id_key"
  DETAIL: Key (user_id)=(221207) already exists.

But when I update this particular record using below query

UPDATE
    A
SET
    user_id = 221207
FROM
    B
WHERE A.f_id = B.f_id

it executes successfully without any error. Only bulk updates shows that error.
I don't get what is going wrong.

It seems there is a UNIQUE INDEX on table A(user_id) and you're trying to assign an existing value to more than one row.

Have a look at the next example:

create table a (id int, user_id int, f_id int);
create table b (id int, f_id int);
create unique index A_user_id_key on a(user_id);

insert into a
values (1, 1, 11),
       (2, 3, 22),
       (3, 2, 33),
       (4, 5, 44 );

insert into b
values (1, 11),
       (3, 22),
       (3, 33), --<<<< there is another row with user_id = 3
       (4, 44);

When I try to update using your current query:

update a
set    user_id = b.id
from   b
where  a.f_id = b.f_id;

It returns same error message:

  ERROR:  duplicate key value violates unique constraint "a_user_id_key"
  DETAIL:  Key (user_id)=(3) already exists.

You could solve by avoiding existing values:

update a
set    user_id = b.id
from   b
where  a.f_id = b.f_id
and    not exists(select 1 from a where a.user_id = b.id);

This is the result:

select * from a;

id | user_id | f_id
-: | ------: | ---:
 1 |       1 |   11
 2 |       3 |   22
 3 |       2 |   33
 4 |       4 |   44

db<>fiddle here

But I'll suggest to check wich are the duplicate rows using next query:

select a.*
from   a
join   b
on     a.f_id = b.f_id
and    exists(select 1 from a where user_id = b.id and f_id <> b.f_id);

id | user_id | f_id
-: | ------: | ---:
 3 |       2 |   33

db<>fiddle here


MySQL Join When Record in Joined Table Does Not Exist How I can join two tables when record in joined table doesn't exist. I'm tried something like this:

SELECT
    Confessions.ID, Confessions.WriteTitle, Confessions.WriteArea,
    Confessions.POST_DATE, Votes.Type
FROM Confessions LEFT OUTER JOIN Votes ON Confessions.ID = Votes.PostID
WHERE ForUser = :ForUser AND Status = :Status AND Votes.IP_Check = :IP_Check

but when record from votes with PostID like ID in Confessions doesn't exist MySQL gives back empty result for this record. isthere any other way?

A WHERE-condition on the inner table will change the the result to an Inner Join instead. You need to move this condition to the ON:

SELECT
    Confessions.ID, Confessions.WriteTitle, Confessions.WriteArea,
    Confessions.POST_DATE, Votes.Type
FROM Confessions LEFT OUTER JOIN Votes
  ON Confessions.ID = Votes.PostID
 AND Votes.IP_Check = :IP_Check
WHERE ForUser = :ForUser AND Status = :Status

As a rule of thumb: Normally conditions on the Outer table are in WHERE while condition on the Inner table are in ON.


Why does PostgreSQL require a database connection? A more precise version of my question might be, does the fact that PostgreSQL requires a database connection (as opposed to some sort of generic database cluster connection) follow directly from some basic design decisions that could be understood by a novice database user?

I am one such novice database user; I don't have a good understanding of database internals. The SQL database I'm most familiar with is SQLite (although I wouldn't say I'm very familiar with how SQLite works either) and I also use PostgreSQL occasionally.

Something that I find conceptually distressing about PostgreSQL is that it requires you to connect to a specific database in order to execute commands even if the commands in question are database agnostic. For example, even if I all I want to do is to print a list of the databases in a database cluster (i.e. psql -l) I'm still required to connect to a specific database within that cluster; why?

Another case which I find somewhat more upsetting is that some operations require a connection to one database in order to perform operations on another, such as database restoration with the pg_restore utility. From the pg_restore man page:

-C, --create

    Create the database before restoring into it. If --clean is also
    specified, drop and recreate the target database before connecting to it.

    When this option is used, the database named with -d is used only to
    issue the initial DROP DATABASE and CREATE DATABASE commands. All data
    is restored into the database name that appears in the archive.

Apparently the connection database does not matter if the -C flag is used, but does matter if the -C flag is not used. This just strikes me as a needlessly confusing and roundabout way of doing things.

I don't think there's a fundamental limitation there, and it's something I'd like to see addressed. I agree with you that it's a bit ugly.

That said, the convention to just use the postgres database as a maintenance database is so strong that the real world impact is negligible.

PostgreSQL has shared catalogs, things like pg_authid and pg_database. It's already possible to start a backend or background worker that connects to no particular database by passing InvalidOID as the database oid to InitPostgres(...) or the bgworker function wrappers. Such a backend can query and AFAIK modify shared catalogs, so I don't see why the executor couldn't be used to service frontend requests instead. Transaction state, user identity, databases, etc are all global.

It might be issues with pg_class and similar relations. Despite the fact that pg_database is a global shared catalog relation, it's listed in pg_class, which gets cloned from the template database. There's no global shared variant of pg_class.

So it's possible the relation cache doesn't handle the "no database" case. I haven't investigated closely. I'll be doing more in 9.5, since some fixes to the background worker API will let me avoid doing ugly hacks relating to shared catalog access by starting a shared-relations-only background worker.


Alternative to removing ONLY_FULL_GROUP_BY After upgrading from MySQL 5.5 to MySQL 5.7 I'm getting an error with some of my queries:

  ERROR 1055 (42000):

  Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'grocery.Product_Category.category_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I did my research and found the cause of the problem and how to solve it, basically I just need to remove ONLY_FULL_GROUP_BY from @@sql_mode and  everything will work again.

However I was wondering if that is the right course of action. Is there an alternative for this, maybe a better way to build the query?

This is my case (http://sqlfiddle.com/#!9/6f1bd):

I have two tables (I simplified their structure here but is basically the same): Product and Category and a many to many relationship table to allow products to belong to more than one category:

SELECT * FROM Product;

+------------+---------+
| product_id | name    |
+------------+---------+
|          1 | Tomato  |
|          2 | Orange  |
|          3 | Banana  |
|          4 | Lettuce |
|          5 | Carrot  |
+------------+---------+
5 rows in set (0,00 sec)

SELECT * FROM Category;

+-------------+------------+
| category_id | name       |
+-------------+------------+
|           1 | Fruits     |
|           2 | Vegetables |
+-------------+------------+
2 rows in set (0,00 sec)

I want to get the products from both categories so the simplest query wold be:

SELECT * FROM Product JOIN Product_Category USING(product_id)
  JOIN Category USING(category_id);

+-------------+------------+---------+------------+
| category_id | product_id | name    | name       |
+-------------+------------+---------+------------+
|           1 |          1 | Tomato  | Fruits     |
|           1 |          2 | Orange  | Fruits     |
|           1 |          3 | Banana  | Fruits     |
|           2 |          1 | Tomato  | Vegetables |
|           2 |          4 | Lettuce | Vegetables |
|           2 |          5 | Carrot  | Vegetables |
+-------------+------------+---------+------------+
6 rows in set (0,00 sec

But if a product exists in both categories I just want it once, doing a DISTINCT select won't help since the category_id differs:

SELECT DISTINCT * FROM Product JOIN Product_Category USING(product_id) JOIN Category USING(category_id);

+-------------+------------+---------+------------+
| category_id | product_id | name    | name       |
+-------------+------------+---------+------------+
|           1 |          1 | Tomato  | Fruits     |
|           1 |          2 | Orange  | Fruits     |
|           1 |          3 | Banana  | Fruits     |
|           2 |          1 | Tomato  | Vegetables |
|           2 |          4 | Lettuce | Vegetables |
|           2 |          5 | Carrot  | Vegetables |
+-------------+------------+---------+------------+
6 rows in set (0,00 sec)

So with MySQL 5.5 I used a GROUP BY clause on the product_id field:

SELECT * FROM Product JOIN Product_Category USING(product_id)
  JOIN Category USING(category_id) GROUP BY product_id;

+-------------+------------+---------+------------+
| category_id | product_id | name    | name       |
+-------------+------------+---------+------------+
|           1 |          1 | Tomato  | Fruits     |
|           1 |          2 | Orange  | Fruits     |
|           1 |          3 | Banana  | Fruits     |
|           2 |          4 | Lettuce | Vegetables |
|           2 |          5 | Carrot  | Vegetables |
+-------------+------------+---------+------------+
5 rows in set (0,00 sec)

Which effectively removed the duplicate, I know the result is not deterministic but I don't care if Tomato appears listed in Fruits or Vegetables category, all I care about is getting it only once in the result set.

But this query with MySQL 5.7 causes the error mentioned above.

So, my question is: Is there another (perhaps better) way to get the same result without having to remove ONLY_FULL_GROUP_BY from @@sql_mode?

I would suggest rewriting the query in a way that minimises the number of columns necessary to put into GROUP BY. In your case you can do that by applying the grouping to the Product_Category table only.

According to your example, that table has the following entries:

+------------+-------------+
| product_id | category_id |
+------------+-------------+
|          1 |           1 |
|          2 |           1 |
|          3 |           1 |
|          1 |           2 |
|          4 |           2 |
|          5 |           2 |
+------------+-------------+

Since you want product names to be unique in the output, group this table by product_id, and for category_id select e.g. the minimum value per product:

SELECT
  product_id,
  MIN(category_id) AS category_id
FROM
  Product_Category
GROUP BY
  product_id

That will give you an output like this:

+------------+-------------+
| product_id | category_id |
+------------+-------------+
|          1 |           1 |
|          2 |           1 |
|          3 |           1 |
|          4 |           2 |
|          5 |           2 |
+------------+-------------+

You can see that each product is listed only once. Joining that table to the other two will not produce duplicates. Therefore, just substitute the above query, as a derived table, for the Product_Category in your query (also removing your GROUP BY from it, of course):

SELECT
  *
FROM
  Product
  JOIN (
    SELECT
      product_id,
      MIN(category_id) AS category_id
    FROM
      Product_Category
    GROUP BY
      product_id
  ) AS pc USING(product_id)
  JOIN Category USING(category_id)
;


How to stop a PostgreSQL database with pg_ctl? I am trying to stop the currently running database, because apparently EnterpriseDB on Mac OSX does not support Postgis. And the Postgis site tells me to use Postgres.app. But I cannot stop the currently running database with EnterpriseDB. I tried pg_ctl:

$ pg_ctl stop -D /Library/PostgreSQL/9.6/data
pg_ctl: could not open PID file "/Library/PostgreSQL/9.6/data/postmaster.pid": Permission denied
$ sudo pg_ctl stop -D /Library/PostgreSQL/9.6/data
pg_ctl: cannot be run as root
$ cd /Library/PostgreSQL/9.6
$ sudo chown -R $(whoami) .
$  pg_ctl stop -D /Library/PostgreSQL/9.6/data
pg_ctl: could not send stop signal (PID: 105): Operation not permitted

How am I supposed to do this?

I had to change owner of those files to user postgres and then shut it down via that user:

$ sudo chown -R postgres .
$ sudo su
# su postgres
$ pg_ctl stop -D /Library/PostgreSQL/9.6/data
waiting for server to shut down.... done
server stopped


Creating a numeric user ID from distinct combinations of IP and user agent from a server log I'm trying to create a numeric user ID from the distinct combination of IP addresses (alphanumerically anonymized) and the user agents. The table is a pre-processed server log file and contains among other variables the url, the IP address and the user agent of every html-request (click) the users produce.

First I created a table of distinct combinations of IPs and user agents. Among the 15 million requests, there are 1.5 million distinct combinations. I added an auto-incrementing ID:

CREATE TABLE users (ip_adress varchar(255), user_agent varchar(255));
INSERT INTO users (ip_adress, user_agent)
SELECT ip_adress, user_agent
FROM (SELECT DISTINCT ip_adress, user_agent FROM server_requests) AS internalQuery
order by ip_adress asc;
ALTER TABLE users ADD user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

Now I need to add the user_id from the table users to the table server_requests. What do you think of this query? There is no error, but the query takes far too long (over 24h, now i stopped it):

SELECT
r.request_id,
r.request_datetime,
u.user_id,
r.url_string,
r.ip_adress,
r.user_agent
FROM server_requests r
LEFT JOIN users u
ON r.ip_adress=u.ip_adress
AND r.user_agent=u.user_agent
ORDER BY r.request_id ASC;

Do you have any idea how to improve the query? Or is it normal, that it takes so much time? (Query example tested with MySQL)

I did this query in MySQL for testing reasons. But I plan to use PostgreSQL for the final query. I'm sure there is an equivalent to AUTO_INCREMENT in PostgreSQL. Because the IP addresses are alphanumerically anonymized, I used VARCHAR. The length of (255) is definitely overkill in this case.

CREATE TABLE users (
    user_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
    ip_address VARCHAR(39) CHARACTER SET ascii NOT NULL,
    user_agent VARCHAR(255) NOT NULL,
    PRIMARY KEY(id)
    UNIQUE(ip_address, user_agent)
) ENGINE=InnoDB;

INSERT INTO users (ip_address, user_agent)
    SELECT DISTINCT ip_address, user_agent
    FROM server_requests;

Notes:

39 is the max length for IPv6; don't say 255 (to avoid indexing problems).
The extra index solves the performance problem by having the PK needed to optimize LEFT JOIN users u  ON r.ip_adress=u.ip_adress  AND r.user_agent=u.user_agent


32GB RAM needed to work with 200.000.000 rows in MongoDB? I've been playing with a large dataset, consisting of a few tens of billions of rows of small ascii strings. I want to count the number of occurrences of each string in the dataset, and i suspect the unique number of strings is about 1 billion. I've been upserting them into a collection in a MongoDB 4.0 database on a three 32GB RAM machines in a replica set, with the WiredTiger storage engine. Things go pretty ok, until i hit the magical number of 200.000.000 rows. After that number, the insert speed starts to grind to a halt. I bulk upsert a chunk of 5000 strings at a time, and some operations take 1 second, but once in a while, an operation takes as much as 40 seconds or more. Replication lag also starts to shoot up.

Examining db.stats(), i see that the only index, _id, takes about 8GB of memory; storage size is about 6GB; wired tiger cache size default is half of ram, so about 15GB. All of these add up to almost 29GB. I'm guessing that the oplog also takes a few GB as well.

Am i running out of RAM? I was expecting to be able to load the whole dataset in a shot. I will probably convert the 3 member replica set to 3 shards, as the data is mostly perisable, and i will reduce the wired tiger cache size parameter, but i am curious if i am right about the RAM and the number of documents.

As MongoDB documentation here With WiredTiger, MongoDB utilizes both the WiredTiger internal cache and the filesystem cache.

Starting in 3.4, the WiredTiger internal cache, by default, will use the larger of either:

50% of (RAM - 1 GB), or
256 MB.

  For example, on a system with a total of 4GB of RAM the WiredTiger cache will use 1.5GB of RAM (0.5 * (4 GB - 1 GB) = 1.5 GB).
  Conversely, a system with a total of 1.25 GB of RAM will allocate 256
  MB to the WiredTiger cache because that is more than half of the total
  RAM minus one gigabyte (0.5 * (1.25 GB - 1 GB) = 128 MB < 256 MB).

Via the filesystem cache, MongoDB automatically uses all free memory that is not used by the WiredTiger cache or by other processes.

Note: The  storage.wiredTiger.engineConfig.cacheSizeGB limits the size of the WiredTiger internal cache. The operating system will use the available free memory for filesystem cache, which allows the compressed MongoDB data files to stay in memory. In addition, the operating system will use any free RAM to buffer file system blocks and file system cache.

To accommodate the additional consumers of RAM, you may have to decrease WiredTiger internal cache size.

The default WiredTiger internal cache size value assumes that there is a single mongod instance per machine. If a single machine contains multiple MongoDB instances, then you should decrease the setting to accommodate the other mongod instances.

To more about the serverstatus then run the command such as

db.runCommand( { serverStatus: 1 } )

Monitoring applications can run this command at a regular interval to collect statistics about the instance.

  Note: The value (i.e. 1 above) does not affect the operation of the command.

The following example includes all repl information in the output:

db.runCommand( { serverStatus: 1,  repl: 1 } )

The output fields vary depending on the version of MongoDB, underlying operating system platform, the storage engine, and the kind of node, including mongos, mongod or replica set member.

For the serverStatus output specific to the version of your MongoDB, refer to the appropriate version of the MongoDB Manual.

For your further ref here , here , here and here


Postgresql constraints SARG Are SQL Server style SARGs relevant to PostgreSQL queries in general and more specifically on table constraints?

I have a parent table with many inherited children, partitioned by a date column.
Would it be better to change the constraints from

CHECK (date_part('year', end_of_dt)::integer = 2016)

to

CHECK (end_of_dt between '2016-01-01 0:00:00' and '2016-12-31 23:59:59.999')

We can easily test this.. but first, stop using date_part. Instead, use extract. It's the same thing with slightly different syntax, but it's standardized.

Testing it

Let's create a table with all the second TIMESTAMPS for the year.

CREATE TEMPORARY TABLE foo AS (
  SELECT to_timestamp(x) AS ts
  FROM generate_series(
    extract(EPOCH FROM '2016-01-01'::timestamp)::int,
    extract(EPOCH FROM '2017-01-01'::timestamp)::int
  ) AS t(x)
);

Then we can implement your check in a WHERE condition to get a basic idea of speed. BTW, there are a ton more seconds in a year than I first though. There are 31,622,401 seconds in a year. It takes 1093 MB to load this sample data.

took, 12.6, 10.8, 11.0 seconds.

SELECT count(*) FROM foo
WHERE extract(YEAR FROM ts) = 2016;

took, 6.1, 6.3, 6.2 seconds

SELECT * FROM foo
WHERE ts BETWEEN '2016-01-01 0:00:00' AND '2016-12-31 23:59:59.999';

Conclusion

Yes, the second is roughly 2x as fast in three trial runs. For the purposes of creating a check constraint, you should use the latter.

Addendum

PostgreSQL has the ability to add functional indexes, however an index on date_part() is not possible

CREATE INDEX onyear ON foo (date_part('year',ts));
ERROR:  functions in index expression must be marked IMMUTABLE

I assume this is because the result of datetime functions comes back in the client's timezone. Either way, this makes it even more advantageous to use the timestamp ranges. One easy to get more speed if need be would be with materialized views though..

CREATE MATERIALIZED VIEW matviewfoo AS
SELECT
  extract(YEAR FROM ts)::smallint AS year,
  ts
FROM foo;
CREATE INDEX yearidx ON matviewfoo (year);
VACUUM FULL matviewfoo;

Now you can

SELECT count(*) FROM matviewfoo WHERE year = 2016;

And, you're down to 4.1 seconds


How to know the current log sequence number in 5.0.X I would like to test the optimal value for iblog files on my mysql which is 5.0.77
I found the below procedure in mysqlperformance blog:
    http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/

mysql> pager grep sequence
PAGER set to 'grep sequence'

mysql> show engine innodb status\G select sleep(60); show engine innodb status\G
Log sequence number 84 3836410803
1 row in set (0.06 sec)
1 row in set (1 min 0.00 sec)
Log sequence number 84 3838334638
1 row in set (0.05 sec)

mysql> select (3838334638 - 3836410803) / 1024 / 1024 as MB_per_min;
+------------+
| MB_per_min |
+------------+
| 1.83471203 |
+------------+

But on mysql 5.0.X, I can't see the log sequence number under "show innodb stauts" output.
Shall some one please let me know a way to know current log sequence number in mysql 5.0.x

I actually wrote about this back on August 27, 2012 : Proper tuning for 30GB InnoDB table on server with 48GB RAM. Here is an excerpt from my past answer (under the section entitled Log File Size):

EXCERPT

5MB is the default size for innodb_log_file_size. Percona's mysqperformanceblog.com gave two good articles on computing the right size for your particular MySQL instance:

http://www.mysqlperformanceblog.com/2006/07/03/choosing-proper-innodb_log_file_size/
http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/

Basically, the blog recommends measuring how many bytes are written to the InnoDB Log Files in one hour. This is what I run to figure that out

SET @TimeInterval = 3600;
SELECT variable_value INTO @num1 FROM information_schema.global_status
WHERE variable_name = 'Innodb_os_log_written';
SELECT SLEEP(@TimeInterval);
SELECT variable_value INTO @num2 FROM information_schema.global_status
WHERE variable_name = 'Innodb_os_log_written';
SET @ByteWrittenToLog = @num2 - @num1;
SET @KB_WL_HR = @ByteWrittenToLog / POWER(1024,1) * 3600 / @TimeInterval;
SET @MB_WL_HR = @ByteWrittenToLog / POWER(1024,2) * 3600 / @TimeInterval;
SET @GB_WL_HR = @ByteWrittenToLog / POWER(1024,3) * 3600 / @TimeInterval;
SELECT @KB_WL_HR,@MB_WL_HR,@GB_WL_HR;

END OF EXCERPT

Where do you go from here?

Run these commands every hour for a week. When you  ascertain the highest number for  @MB_WL_HR, take half of it and resize innodb_log_file_size to it : See my post How to safely change MySQL innodb variable 'innodb_log_file_size'?

CONCLUSION

Since you can figure out the proper InnoDB Log File Size in this manner, there is no need to pool the show engine innodb status\G for anything, especially since you said it is not available in MySQL 5.0.

Keep in mind that my answer works for MySQL 5.1/5.5. So, how do you did this for MySQL 5.0?

It's a good thing the status variable Innodb_os_log_written exists in MySQL 5.0.

Looking back, here is your original question:

mysql> pager grep sequence
PAGER set to 'grep sequence'

mysql> show engine innodb status\G select sleep(60); show engine innodb status\G
Log sequence number 84 3836410803
1 row in set (0.06 sec)
1 row in set (1 min 0.00 sec)
Log sequence number 84 3838334638
1 row in set (0.05 sec)

mysql> select (3838334638 - 3836410803) / 1024 / 1024 as MB_per_min;
+------------+
| MB_per_min |
+------------+
| 1.83471203 |
+------------+

With MySQL 5.0 you have to do this:

mysql> show global status like 'Innodb_os_log_written'; select sleep(60); show global status like 'Innodb_os_log_written';

Then subtract and multiply like you did before:

mysql> show global status like 'Innodb_os_log_written'; select sleep(60); show global status like 'Innodb_os_log_written';
+-----------------------+--------------+
| Variable_name         | Value        |
+-----------------------+--------------+
| Innodb_os_log_written | 500034464256 |
+-----------------------+--------------+
1 row in set (0.01 sec)

+-----------+
| sleep(60) |
+-----------+
|         0 |
+-----------+
1 row in set (1 min 0.01 sec)

+-----------------------+--------------+
| Variable_name         | Value        |
+-----------------------+--------------+
| Innodb_os_log_written | 500047017984 |
+-----------------------+--------------+
1 row in set (0.01 sec)

mysql> select (500047017984 - 500034464256) / 1024 / 1024 as MB_per_min;
+-------------+
| MB_per_min  |
+-------------+
| 11.97216797 |
+-------------+
1 row in set (0.00 sec)

mysql>

Give it a Try !!!


Any reasons trigger AFTER INSERT would not fire? Context:

We're using microsoft sync framework in our project.

Sync creates triggers AFTER INSERT, DELETE, UPDATE triggers for tables that are provisioned to sync.
the framework also creates tracking tables, that get updated by these triggers, so it can successfully keep database in sync with each other, when a sync is performed.

The issue:

We have a customer, whom is managing to insert data into one of these tables that contain these triggers, but the information isn't making it into the corresponding tracking table via the trigger.

The Question:

Are there any ways to insert data into a table, that would circumvent the firing of an AFTER INSERT trigger?

Trigger Example:

CREATE TRIGGER [dbo].[Claims_insert_trigger]
ON [dbo].[Claims]
FOR INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE [side]
SET [sync_row_is_tombstone] = 0
    , [local_update_peer_key] = 0
    , [restore_timestamp] = NULL
    , [update_scope_local_id] = NULL
    , [last_change_datetime] = GETDATE()
FROM [Claims_tracking] [side]
    JOIN INSERTED AS [i] ON [side].[ClaimID] = [i].[ClaimID];

INSERT INTO [Claims_tracking]
(
    [i].[ClaimID]
    , [create_scope_local_id]
    , [local_create_peer_key]
    , [local_create_peer_timestamp]
    , [update_scope_local_id]
    , [local_update_peer_key]
    , [sync_row_is_tombstone]
    , [last_change_datetime]
    , [restore_timestamp]
)
SELECT [i].[ClaimID]
    , NULL
    , 0
    , @@DBTS+1
    , NULL
    , 0
    , 0
    , GETDATE()
    , NULL
FROM INSERTED AS [i]
    LEFT JOIN [Claims_tracking] [side] ON [side].[ClaimID] = [i].[ClaimID]
WHERE [side].[ClaimID] IS NULL;
SET NOCOUNT OFF;
END

It is possible to disable a trigger. (The user could have disabled it) Also, it is possible that there are other triggers that return an error and rolls back the transaction, wich means the after trigger is not called.


pre login initialization =29237; handshake=26244 Connecting to one of my servers (no mirror or availability group running), from a local machine and I get this error message below related to handshake when the firewall is up.

when the firewall is down it connects straight away.

the question I would like to address is:

is it always these number below (29237 and 26244) that sql server uses for connection?

I am aware of the port numbers on single and multiple instances.

This server by the way is not a named instances.

initialization =29237; handshake=26244

or does it change? if so then - based on what?

we have had issues with one domain controller but I have ruled that out.

I haven't even touched authentication schemas and Kerberos

By default, SQL uses port 1433 and 1434 (if you are using named instance or the DAC connection).

You can validate which port is used using SQL Server Configuration Manager


What is the purpose of a buffer and a buffer manager? In a database system, what is the purpose of a buffer and a buffer manager?

This concept currently came up in my class and am very interested to learn more.

A buffer is an 8 KB page in memory, the same size as a data or index page you can consider buffer as a frame which holds data and index pages when they are brought from disk to memory.

SQL Server buffer manager manages the task of reading data pages into buffer pool and also writing it to disk. It is a reserved memory store for SQL Server and by default if you do not set value for it it will take as much memory as possible.

What you are looking is documented in SQL Server Buffer Management


Questions regarding removing a secondary node from an availability group I have a 2 node AG in a non-prod environment and we are looking to shut down the secondary node for cost savings, leaving this with just the primary node.  I have some questions based on this process, and my research hasn't answered all of them.  Could somehow fill in the blanks here on what is possible / best to do?

is it possible to keep just a single node AG?  if so, is there a reason to actually do it?  As I understand it, there is no benefit to keeping the AG at all if you remove all the secondary nodes, as it then becomes just a stand alone instance.  If i kept the AG, then the transaction logs of the DB's will not truncate, as its trying to find a place to send the logs to, correct?
The AG Listener - Moot point if we remove the AG altogether, but if i kept the AG as a single node, the listener would not function correct?  My app team will need to make code changes to use something else since listener name is coded to be used, and they would like to avoid that if possible.  i don't think this is possible, but wanted to check.
Secondary node down the road - we may spin up the secondary node on demand down the road, so is there an issue with leaving the cluster resources in place after we remove the AG, or does the removal delete the cluster objects?  I think they stay, but I have not found an answer on this.
SSRS question - we are in a scale-out deployment, do I need to remove the secondary from the scale out?  I would prefer to leave it since we may need to recreate the secondary down the road.  I would need to update connection strings, etc to use the primary node and not the listener.

My assumed workflow is like this based on what I found

Connect to primary replica
Delete the entire AG (this removes AG settings from all DB's, primary and secondary), and throws secondary DB's into restoring state, while leaving primary DB's accessible and completely separate.
log backups will continue on primary like normal and log truncation will happen as expected.

Yes.  Primary benefit is that you can keep the listener online.  Downside is that there is still a dependency on the cluster service to keep the listener online, but that shouldn't be a significant risk since there is only one node as it will always own quorum, assuming you remove all quorum votes other than the remaining node's vote.
The listener is a component of the AG, so if you delete the AG, it will delete the listener.  Options are to 1) just remove the DBs from the AG, 2) you can delete the AG and then create a new cluster role that just has the network name and IP address (which is really all that a "listener" is), or 3) delete the AG and add a DNS record for the host name that clients are currently using.  You could also do as you suggest to modify client connections, but I don't know how painful that would be for you.
If you leave it as a one-node cluster, you can simply join a node to it in the future if needed.  If you remove the cluster completely, you simply have to create a new cluster and join the two (or however many) nodes to it.
If you pointed the SSRS instances to the listener name, then you don't need to do anything if you keep the listener or add a DNS record for the listener name.

With your comments about possibly using an AG again in the future, I personally would not delete the AG and cluster.  I would remove the databases from the AG, remove the secondary node from the cluster, and remove all quorum votes except for the one node in the cluster.  Less work now, less work later.

You haven't mentioned anything about how backups are occurring so there's no way for anyone to know whether your backups will continue as expected.

Also, you'll first have to remove the databases from the AG prior to deleting the AG.


After lowering maxmemory, redis still has high RSS memory I would like Redis to return some memory. I lowered the maxmemory by 10GB but noticed Redis is still "holding onto it" in RSS based on "used_memory_rss". Based on http://redis.io/topics/memory-optimization, it looks like Redis won't return the memory to the OS. How do I force this? Is there any way to do this without restarting Redis?

I had the same problem and this answer was helpful.

  even though Redis isn’t using the memory anymore, the allocator just hasn’t released the memory back to the OS yet. Reds v4 has a MEMORY PURGE command to tell the allocator to release the memory it isn’t using, but unfortunately that isn’t available to you on v3.2.


Different Result When Insert Data Contain Quote on Localhost and Online MySQL Hosting I installed xampp on localhost and insert data contain quote using this php code

$content    = htmlentities($_POST['content']);
$content_esc = mysql_real_escape_string($content);
$save = mysql_query("INSERT INTO tbl_post (title,content,date,publish) values('$title_esc','$content_esc','$date','$publish')");

The result on localhost website is

this is 'example data'

The row on database filled with this

this is &quot;example data&quot;

But using the same code to insert data the result from my hosting provider is different

this is \'example data\'

The row on database filled with this

this is \&quot;example data\&quot;

I test my website on localhost, insert post contain quote and it show on page correctly. Then i surprise when i upload on online hosting, insert post and the post page show '\' before the quote.

At this time i replace the '\' with nothing. How to insert the quote so it can show same result on local mysql database and online mysql database ? What is wrong, my php code or my mysql database ?

Looks PHP issue, I bet you have Magic Quotes turned off on local (good!) whereas your hosting privider has turned them on - http://www.php.net/manual/en/security.magicquotes.what.php.
When they're turned on it's the same like if you'd write:

$content    = htmlentities(addslashes($_POST['content']) );//HERE: addslashes is basically the same like your mysql_real_escape_string() - this means contents of $_POST get escaped automatically
$content_esc = mysql_real_escape_string($content);
$save = mysql_query("INSERT INTO tbl_post (title,content,date,publish) values('$title_esc','$content_esc','$date','$publish')");

I also guess that next time you're better asking such a question on StackOverflow instead;)


How to use  or != operator Here is my query:

select     c._id, c.cat_name
from       category c
inner join deptcat dc
on         c._id != dc.cat_id
inner join department d
on         dc.dept_id != d._id
where      d._id = 1

I have 3 tables department, category and deptcat:

CREATE TABLE department
  (_id, dept_name)
AS
  VALUES
    ( 1, 'CSE' ),
    ( 2, 'E&E' ) ;

CREATE TABLE category
  (_id, cat_name)
AS
  VALUES
    ( 1, 'a' ),
    ( 2, 'b' ),
    ( 3, 'c' ),
    ( 4, 'd' ) ;

CREATE TABLE deptcat
  (_id, dept_id, cat_id)
AS
  VALUES
    ( 1, 1, 1 ),
    ( 2, 1, 2 ) ;

My output should be like this:

_id | cat_name
----|-----------
3   | c
4   | d

I need to fetch the cat_id's which is not present in the deptcat table and also associated with dept_id  1, so can some one help me here?

You can use the exists condition to check for the existence of a record through a subquery. This is often preferable to an anti join (a left join where we make sure the right table is null) as the engine stops as soon as it finds a record.

Sample code:

select
    category._id,
    category.cat_name
from
    category
where
    not exists(
        select
            *
        from
            deptcat
            inner join department on(
                department._id = deptcat.dept_id
            )
        where
            deptcat.cat_id = category._id
            and department._id = 1
    )


Unable to connect SQL Managed instance in the vnet One of our team member wanted to create a Managed instance for his testing and was asked to use existing vnet which was already configured to connect remotely and had other Managed instances for POC.

While building he checked the 'Prepare subnet for Managed instance'. The deployment which should not have taken more than an hour took 6-7 hours and we knew it has done some changes that will cause issues. The deployment was successful and managed instance is up and running.

The real issue :

We can't connect to the MI remotely from the domain anymore. We are investigating ,but would really be helpful if anyone can point out what it would be.

(PS: I am weak in Networksconcepts)

For POC, you can also try the public endpoint which is now available and can be configured through PowerShell. https://techcommunity.microsoft.com/t5/Azure-SQL-Database/Public-endpoint-for-SQL-Database-Managed-Instance-is-available/ba-p/438996


How to get postgres to resume processes after it hangs when the RAID drive hosting the tablespace times out Nearly once per day I'm getting a timeout that shows up in console like:

7:1:36 PM kernel: Areca RAID volume scsi command timeout (target=0 lun=0)

which corresponds exactly to when all postgres processes hang.

I'm reluctant to terminate pids because that tends to bring everything down.  Is there anyway to resurrect these processes?  I can't even open a new postgres window in terminal.

Also, is there any way to make postgres more tolerant of such timeouts?
My System:

Mac Pro (2013) with 12 core X 64GB
OSX 10.10.4
Postgres 9.4.4

the drives I'm using for tablespaces (4 of them) are:

Areca 8050 T2
each with 8 1TB OWC Mercury Electra 6G SSDs in a RAID 0 config.

You very likely have one or more failing disks, or a failing RAID controller. This would be a really good time to migrate to another host.

Also, RAID 0 across eight disks? That's asking for trouble.

In a case like this PostgreSQL will be waiting in a system call into the kernel, like a pwrite(), fsync() or similar. PostgreSQL isn't being tolerant or intolerant of your raid controller issues. It's just patiently waiting until the kernel finishes doing what PostgreSQL asks it to do and returns control to PostgreSQL. This is exactly what should happen. The problem is that the controller isn't responding so the system call never returns. You should be able to confirm this with strace or ps (though I'm not sure of the exact options on OS X).


Import .sql file into multiple databases How to import .sql file into multiple databases at one shot.

I have tried this:

mysql -uroot -p -all-databases < test.sql

But it asks for database name. Whatever there in test.sql file should reflect in all the databases/Schemas.

If you're running on Linux/Unix, you can do it with a bit of shell:

for DB in `mysql -u root -r --silent -p -e 'show databases;' | egrep -v "_schema$|^mysql$|^sys$"`
do
  mysql -uroot -p $DB < test.sql
done

Will obviously be easier if you temporarily set it up to be passwordless.


Looking for another method to count in a single query So, I found this article is very useful Join two tables and return data and count in a single query
and here Single query to return counts over different IDs in a single record for some IDs or parameter to use.

So my question is, if I have more than 10 IDs or parameters for which I need counts,  using COUNT(CASE WHEN ... (as below) becomes awkward and hard to maintain:

SELECT COUNT(CASE WHEN table1.id = 1 THEN table2.id END) as count1,
       COUNT(CASE WHEN table1.id = 2 THEN table2.id END) as count2,
       COUNT(CASE WHEN table1.id = 3 THEN table2.id END) as count3,
       ...,
       COUNT(CASE WHEN table1.id = 10 THEN table2.id END) as count10

Is there a better way to solve this problem?

More details:
I am using MySql database and use Codeigniter to process my data. This my current query:

<?php defined('BASEPATH') OR exit('No direct script access allowed');
class Model_provenance extends CI_Model {
    function __contruct()
    {
        parent :: __contruct();
        $this->load->database();
    }
    function count_koleksi_by_provenance_status() {
        $query = $this->db->select('provenance.provenance_status, cat_provenance.status AS provenance_status,
                                    COUNT(provenance.provenance_status) AS total,
                                    COUNT(CASE WHEN provenance.provenance_status = 1 then cat_provenance.status END) AS count1,
                                    COUNT(CASE WHEN provenance.provenance_status = 2 then cat_provenance.status END) AS count2,
                                    COUNT(CASE WHEN provenance.provenance_status = 3 then cat_provenance.status END) AS count3,
                                    COUNT(CASE WHEN provenance.provenance_status = 4 then cat_provenance.status END) AS count4,
                                    COUNT(CASE WHEN provenance.provenance_status = 5 then cat_provenance.status END) AS count5,
                                    COUNT(CASE WHEN provenance.provenance_status = 6 then cat_provenance.status END) AS count6,
                                    COUNT(CASE WHEN provenance.provenance_status = 7 then cat_provenance.status END) AS count7,
                                    COUNT(CASE WHEN provenance.provenance_status = 8 then cat_provenance.status END) AS count8,
                                    COUNT(CASE WHEN provenance.provenance_status = 9 then cat_provenance.status END) AS count9', FALSE)
                        ->join('provenance', 'provenance.provenance_status = cat_provenance.ID', 'LEFT')
                        ->group_by('cat_provenance.status')
                        ->order_by('cat_provenance.ID', 'ASC')
                        ->from('cat_provenance')
                        ->get();
        if($query->num_rows() > 0) {
            return $query->result();
        } else {
            return NULL;
        }
    }
}

The cat_provenance data table looks like this:

ID | status
------------------
1  | Grant
2  | Legacy
3  | Gift
4  | Purchase
5  | Recompense
6  | Discovery
7  | Search
8  | Exchange
9  | Conversion

The provenance data table has the following columns:

ID
provenanceID
coll_regID
authorID
provenance_status
date_from
date_to
buy_price
provenance_desc
post_by
post_create
edit_by
edit_datetime

I want the final result to look something like this:

Grant (2)
Legacy (5)
Gift (0)
Purchase (0)
Recompense (1)
Discovery (10)
Search (5)
Exchange (3)
Conversion (0)

The key here is I need to see a total for every category, even if that total is 0.

So if I have 20 ID or more in the cat_provenance table, do I have to write this part:

COUNT(CASE WHEN provenance.provenance_status = 1 then cat_provenance.status END) AS count1

or is there another method that I can use so I do not write that part one by one?

Hopefully, this better explains my question.

From the information provided, it sounds like you don't absolutely have to have the data in the format:

  Grant | Legacy | Gift | Purchase | ...
 -------+--------+------+----------+------
    2   |    5   |   0  |     0    | ...

If you can use the data in the format:

  Status  | Count
----------+-------
 Grant    |     2
 Legacy   |     5
 Gift     |     0
 Purchase |     0
 ...      |   ...

Then the simplest solution is to pull the data back as follows:

SELECT cat.status as `Status`, COUNT(p.id) as `Count`
  FROM cat_provenance cat
         LEFT JOIN provenance p ON (cat.ID = p.provenance_status)
 GROUP BY cat.status WITH ROLLUP

Results will look like the above. By including WITH ROLLUP, we include a row with Status NULL and Count the grand total for all categories.

  Status  | Count
----------+-------
 ...      |   ...
 NULL     |    26

Note: code untested


I would suggest creating a dates table and cross-joining that table to your parts table, something like the following example.

Do this in tempdb to not step on anything else.

USE tempdb;

Create a Months table, with the Year, Month, and a starting date for each:

CREATE TABLE dbo.Months
(
    iYear int NOT NULL
    , iMonth int NOT NULL
    , dDate date NOT NULL
    , CONSTRAINT pk_Months
        PRIMARY KEY CLUSTERED
        (iYear, iMonth)
);

Use a CTE to populate rows into the table above.  This example creates years/months in the range of 1900-01-01 to 2173-10-01:

;WITH cte AS
(
    SELECT v.Num
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v(Num)
)
, cteDates AS
(
    SELECT dDate = DATEADD(DAY, 0, ROW_NUMBER() OVER (ORDER BY c1.Num))
    FROM cte c1
        CROSS APPLY cte c2
        CROSS APPLY cte c3
        CROSS APPLY cte c4
        CROSS APPLY cte c5
)
, cteMonths AS
(
    SELECT iYear = DATEPART(YEAR, cteDates.dDate)
        , iMonth = DATEPART(MONTH, cteDates.dDate)
    FROM cteDates
)
INSERT INTO dbo.Months(iYear, iMonth, dDate)
SELECT cteMonths.iYear
    , cteMonths.iMonth
    , DATEFROMPARTS(cteMonths.iYear, cteMonths.iMonth, 1)
FROM cteMonths
GROUP BY cteMonths.iYear
    , cteMonths.iMonth
ORDER BY cteMonths.iYear
    , cteMonths.iMonth;

Create the table with the sample "Parts" data:

CREATE TABLE dbo.PartCosts
(
    PartNum varchar(10) NOT NULL
    , StartDate date NOT NULL
    , EndDate date NOT NULL
    , Cost decimal(10,2) NOT NULL
);

INSERT INTO dbo.PartCosts(PartNum, StartDate, EndDate, Cost)
VALUES ('ABCD1', '2014-01-01', '2014-04-01', 50.99)
    , ('BDE2', '2014-01-01', '2014-03-01', 14.59)
    , ('ABCD1', '2014-04-01', '2014-05-01', 13.99);

Run the cross-join to get the results you're after:

SELECT pc.PartNum
    , pc.StartDate
    , pc.EndDate
    , pc.Cost
    , Period = CONVERT(varchar(7), m.dDate, 111)
FROM dbo.PartCosts pc
    CROSS JOIN dbo.Months m
WHERE pc.StartDate <= m.dDate
    AND pc.EndDate > m.dDate;

The results:

+---------+------------+------------+-------+---------+
| PartNum | StartDate  |  EndDate   | Cost  | Period  |
+---------+------------+------------+-------+---------+
| ABCD1   | 2014-01-01 | 2014-04-01 | 50.99 | 2014/01 |
| ABCD1   | 2014-01-01 | 2014-04-01 | 50.99 | 2014/02 |
| ABCD1   | 2014-01-01 | 2014-04-01 | 50.99 | 2014/03 |
| BDE2    | 2014-01-01 | 2014-03-01 | 14.59 | 2014/01 |
| BDE2    | 2014-01-01 | 2014-03-01 | 14.59 | 2014/02 |
| ABCD1   | 2014-04-01 | 2014-05-01 | 13.99 | 2014/04 |
+---------+------------+------------+-------+---------+


Sort one column , based on that sort another column in Oracle I have a table :

ID     COUNTER     Date
----   -------  -----------
 1        05    23-07-2019
 2        01    23-06-2017
 3        10    02-06-2019
 4        14    01-12-2016
 5        03    23-11-2018
 6        07    23-11-2015

Now I want to Sort first Date column , based on that sort result sort counter .How to write a query for this. If I have a more than one date for a particular year than , sort according to counter for that particular year records.
Like this :

  COUNTER      Date
  -------   -----------
     10       02-06-2019
     05       23-07-2019

     03       23-11-2018
     01       23-06-2017
     14       01-12-2016
     07       23-11-2015

Any help would be appreciated!.
Thanks.

order by to_char(date_column, 'YYYY') desc, counter desc

order by trunc(date_column, 'YYYY') desc, counter desc

drop table t1 purge;
create table t1 (counter varchar2(2), date_column date);
insert into t1 values ('05', date'2019-07-23');
insert into t1 values ('01', date'2017-06-23');
insert into t1 values ('10', date'2019-06-02');
insert into t1 values ('14', date'2016-12-01');
insert into t1 values ('03', date'2018-11-23');
insert into t1 values ('07', date'2015-11-23');
commit;

select * from t1 order by trunc(date_column, 'YYYY') desc, counter desc;

CO DATE_COLUM
-- ----------
10 02-06-2019
05 23-07-2019
03 23-11-2018
01 23-06-2017
14 01-12-2016
07 23-11-2015


mysqldump: Got error: 2002: "Can't connect to MySQL server on 'x' (code)" when trying to connect I am trying to create a back up from a server by using XAMPP (via command prompt).
Unfortunately the error is: mysqldump: Got error: 2002: "Can't connect to MySQL server on 'x' (10060)" when trying to connect. I have looked a bit around on StackOverflow (mostly0, but most questions are regarding "access denied for user at (...)" or they're talking about MYSQL. I have tried finding the files that MYSQL should have (which they're discussing) and looked whether XAMPP had these as well (it did not).
My question is:
What could possibly be causing this error? I know the name, password and host are correct.
How have I tried backing up the database?
I opened Command Prompt with administrator rights.
I navigated to the correct directory where mysqldump is located at.
I then typed in: C:\xampp\mysql\bin>mysqldump -e -uNAME -pPWD -hHOST DATABASENAME > C:\winhostMySqlD.sql
It does create the .sql file, but CMD crashes with the error mentioned above.

mysqldump -e -uNAME -pPWD -hHOST DATABASENAME > C:\winhostMySqlD.sql

I hope you gave this command for representational purpose, if you execute this command as it is, you will get an error. Below mentioned sample command for your reference, and possible reason for the connection error:
To backup database mydbname:
mysqldump -u username -p password -h localhost mydbname > /path/filename.sql

To backup all databases:
mysqldump -u username -p password -h localhost --all-databases > /path/filename.sql

-u username - like admin/root
-p password - like 'Admin@123'
-h hostname - where mysql server is running - like localhost/192.168.x.x (IP address)

Check my post for learn more about backup and restoration.
Some of the reasons for connection errors, listed down for your troubleshooting:

MySQL Server is not running on the server.
MySQL socket file is missing / deleted / different location.
Security Enhanced Linux is protecting mysqld process (Linux).
MySQL server is bound to single address (bind_address).
Firewall blocking MySQL port.
Access restricted to the server hosts.

Check my post for detailed troubleshooting steps for above mentioned reason and other possible causes.