InnoDB cannot find a row - possible index corruption? I have this table:

+----+------------------+------------------+
| Id |      Start       |       End        |
+----+------------------+------------------+
|  1 | 2018-02-04 11:30 | 2018-02-05 12:00 |
|  2 | 2018-02-04 15:40 | 2018-02-05 08:00 |
|  3 | 2018-02-05 10:00 | 2018-02-06 09:00 |
+----+------------------+------------------+

I found this strange situation using MariaDB 10.1.18 InnoDB.

I called this query:

SELECT * FROM mytable WHERE start BETWEEN '2018-02-04 00:00' and '2018-02-04 12:00'

and I didn't find any rows! The first row (id=1) should have been shown.

Calling this query using another colum:

SELECT * FROM mytable WHERE end BETWEEN '2018-02-05 10:00' and '2018-02-05 13:00'

I found the first row, so I was sure the row with id=1 was there.

I solved the problem when I updated the first row:

UPDATE mytable SET Start='2018-02-04 11:31' where id=1;
UPDATE mytable SET Start='2018-02-04 11:30' where id=1;

Now with this apparently useless update I can find the first row with both queries.

Now I don't have this problem anymore and I cannot investigate, but I want to understand why it happened.

Can it be connected with index corruption? I have two different indexes in start and end columns. If yes, maybe, I should have repaired the indexed, but I'm a bit scared about it, I could not have noticed the problem.

Should I do anything at this point, to make sure that there isn't any corruption of the indexes now?

looking for Your data - Your columns is VARCHAR, but not date, this is make query non working with any non readable character in data

by run UPDATES:

UPDATE mytable SET Start='2018-02-04 11:31' where id=1;
UPDATE mytable SET Start='2018-02-04 11:30' where id=1;

You are fix this situation

Proper way for work with dates - have datetime type for date time columns

in this case Your data would look like:

+----+---------------------+---------------------+
| Id |      Start          |       End           |
+----+---------------------+---------------------+
|  1 | 2018-02-04 11:30:00 | 2018-02-05 12:00:00 |
|  2 | 2018-02-04 15:40:00 | 2018-02-05 08:00:00 |
|  3 | 2018-02-05 10:00:00 | 2018-02-06 09:00:00 |


Using of delete obsolete in Oracle 11gR2 RMAN on Linux I'm using crontab for automatic backup (full backup). It works 2 times in a day (8 AM and 23 PM). In my script I use delete obsolete but I have a question. I want to delete old backups without last 2 backup. Let's say:

Day 1: 8 AM

Day 1: 23 PM

Day 2: 8 AM (When my script run here and complete backup operation, after that my delete command should delete just Day 1: 8 AM backup)

How can I do it with delete obsolete command?

It's my script:

#!/bin/bash
. /home/oracle/.bash_profile
RMANBACKUP_MOUNTPOINT1=/u01/backup/rman/
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=ORCL
export PATH=$ORACLE_HOME/bin:$PATH
export NLS_DATE_FORMAT="YYYY-MM-DD:HH24:MI:SS"
rman target /  LOG '/oracle/backups/log/rman.log' << EOF
run{
allocate channel ch1 device type disk MAXPIECESIZE 200M;
allocate channel ch2 device type disk MAXPIECESIZE 200M;
backup spfile;
backup current controlfile;
backup database format '%d_%T_db' plus archivelog format '%d_%T_arch';
delete obsolete redundancy 2;
}
exit;
EOF

Thank you.

Tell RMAN what you want/need to keep and let it do the worrying.

CONFIGURE RETENTION POLICY TO REDUNDANCY 2 ;

That will keep two copies of your database and consider anything before that as obsolete.

I am a little concerned that you say you're only taking "full backups".

Are you backing up the ControlFiles?  The ArchiveLogs?
Without the former, you may have problems restoring a database after changing its structure (removing or even adding DataFiles) and without the latter, you lose any ability to perform a Point-in-Time Recovery.


SQL Server 2008 Partitioning Evaluation I'm trying to do a performance comparison of a table with partitioning and the same table without partitioning.

So we have Subcriber and SubscriberPartitioned tables.

Tables structure is:

SubscriberId | Name | Email | Telephone | UserId (partition column)

Data: I'm using SQL Data Generator:

to fill the tables with 10 million rows each
the UserId range goes from 1-200

Partitioning: I have partitioned the table SubcriberPartioned in 2 according to the UserId, which goes from 1 to 200. So we have approx 5 million rows in each partition.

Performance measure: I'm using SQL Server Profiler to measure the query times.

Why does SQL Server differ so much in query times?

For example the query

select * from subcriber where SubscriberId = 1000

... will the first time take like 40 seconds, and if I re-execute it will take less than a second. If I try with another Id it also will take less than a second. But the first time took a lot of time.

So, is there any optimization or caching being done automatically in the background by SQL Server?

I was expecting the same query to take the same time if repeated over time...
Which do you think would be the best queries to run and how to measure them in order to test the partitioning of the table and to see the optimization gained with the partition strategy instead of the normal table.

EDIT:

After answers on this post I made some cold start tests, and found that the non partitioned table was performing faster than the partitioned table.

Specially this query caught my attention:

select * from [table] where IdUser = 100

Why doesn't the partitioned table improve the performance on it? The above query includes a filter by the partition column, wouldn't that focus the engine to scan only half of the records on the partitioned approach?

EDIT 2:

I have performed the tests again. Assigning the second partition a different file group, and I have reached a performance improvement of 50 % in the mentioned query.

What do you expect your real life volume of data to be?

For 10 million rows, I wouldn't bother with partitioning. The overhead far outweighs the benefits: partitioning isn't a silver bullet to cure performance issues.

To answer,

Point 1: on the first run, data needs loaded into memory ("buffer pool") and will stay cached until evicted based on memory pressure and usage. Personally, I'd test with the cache filled because you'd expect your app to require that data very often, especially if you think partitioning is the solution to some problem

For point 2, what queries do you expect to run in production? The queries should be representative of this production load. However they should test different realistic filter combinations with and without partition key at least.

Edit, some reading, after comments below:

http://www.brentozar.com/archive/2008/06/sql-server-partitioning-not-the-answer-to-everything/
http://www.mssqltips.com/sqlservertip/1914/sql-server-database-partitioning-myths-and-truths/ (point 7 especially)


DB Mirroring in async/High Performance mode: Available in SQL Server 2008 Standard Edition? This should be easy, but the MS docs are not clear.

The docs:
http://msdn.microsoft.com/en-us/library/cc645993%28v=SQL.100%29.aspx

Indicate that in Standard edition, the only supported mirroring is "safety full only"

Taken together with this document:
http://technet.microsoft.com/en-us/library/ms191456(v=sql.105).aspx

The answer would seem to be:

  In SQL Server 2008, async, high performance mirroring is only available in Enterprise Edition

Correct?

In the first document you list, it is clearly stated that Asynchronous Mirroring is only supported in the Enterprise Edition of SQL Server 2008.

Synchronous Mirroring is available in the Standard Edition.


How to filter table 2 before joining to table 1 so that the result still includes all of table 1, even if no match in table 2? The following returns all rows from table 1 where post_type = dlm_download. It groups like values from table 2 by the matching column in table 1 so that each row in table 1 has a maximum of one matching 'result' from table 2. The result is that all records from table 1 are displayed in all cases, simply displaying null for download_date when nothing in table 2 matches (meaning nobody has downloaded the file yet):

SELECT p.post_title AS 'Download title', p.ID AS 'Download ID', MAX(l.download_date) AS 'Last download'
FROM wp_posts p
LEFT JOIN wp_download_log l ON p.ID = l.download_id
WHERE p.post_type = 'dlm_download'
AND p.post_status = 'publish'
GROUP BY p.ID

I want to additionally filter out records from table 2 that match a few conditions...before being matched up with table 1. But the following additions to the query, placed in between WHERE and GROUP BY, ultimately whittle down the result from 70 rows to 59, eliminating the rows from table 1 even if nothing in table 2 matches (previously shown as NULL for the download_date)

AND l.user_agent NOT LIKE '%bot%'
AND l.user_agent NOT LIKE '%crawl%'
AND l.user_agent NOT LIKE '%panscient%'
AND l.user_agent NOT LIKE '%scrapy%'
AND l.user_agent NOT LIKE '%bubing%'
AND l.user_agent NOT LIKE 'Go-http-client/1.1'
AND l.user_agent NOT LIKE '%guzzle%'
AND l.user_agent NOT LIKE '%jetty%'
AND l.user_agent NOT LIKE '%resty%'

This is hard for me to explain or wrap my head around, but I think what is happening is the 11 items that have not yet been downloaded by humans are effectively excluded from the result by the above conditions. In other words, I think the conditions effectively say to not return anything that doesn't match these, and because NULL in table 2 doesn't match, it is eliminating results where there is something in table 1 but not table 2 (and thus NULL for table 2).

How can I return ALL records from table 1, wp_posts, including data from table 2 when a record matches (and NULL If not), but pre-filter table 2 by eliminating things where the user agent excludes bot etc?

If you want to reduce the number of rows you simply make a subselect and join that.
See following query:

you should check the subquery if it returns all the wanted rows.

SELECT p.post_title AS 'Download title', p.ID AS 'Download ID', MAX(l.download_date) AS 'Last download'
FROM wp_posts p
LEFT JOIN (SELECT *
           FROM wp_download_log
           WHERE user_agent NOT LIKE '%bot%'
             AND user_agent NOT LIKE '%crawl%'
             AND user_agent NOT LIKE '%panscient%'
             AND user_agent NOT LIKE '%scrapy%'
             AND user_agent NOT LIKE '%bubing%'
             AND user_agent NOT LIKE 'Go-http-client/1.1'
             AND user_agent NOT LIKE '%guzzle%'
             AND user_agent NOT LIKE '%jetty%'
             AND user_agent NOT LIKE '%resty%') l ON p.ID = l.download_id
WHERE p.post_type = 'dlm_download'
AND p.post_status = 'publish'
GROUP BY p.ID


Restoring development database from production backup triggered production database to restore as well We backed up our production database to restore our development database.

When we restored our development database from the backup it triggered the production database to restore as well.

The production database did not actually restore though...

I verified we applied the restore to the correct database by running

WITH LastRestores AS
(
SELECT
    DatabaseName = [d].[name] ,
    [d].[create_date] ,
    [d].[compatibility_level] ,
    [d].[collation_name] ,
    r.*,
    RowNum = ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY r.[restore_date] DESC)
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.Name
)
SELECT *
FROM [LastRestores]
WHERE [RowNum] = 1

This showed that our dev database has been restored and production has not.

Why did SQL Server Management Studio say that the production database was being restored when it has not been?

It looks like someone else has had a similar experience
https://sqlundercover.com/2017/08/14/ssms-source-database-restoring-after-performing-a-restore-to-a-new-destination/

Found the answer here https://sqlundercover.com/2017/08/14/ssms-source-database-restoring-after-performing-a-restore-to-a-new-destination/

When configuring the restore there is an option to keep the source database in a restore mode while copying the tail log. This is selected by default.


SSRS: Line Chart, Grouping by Day I have a fairly specific set of data I am trying to display.

I have a dataset of helpdesk ticket data.  I would like to display a line chart that shows tickets opened and tickets closed by day, over the last 30 days.  I've been able to display one of these just fine, by adding an expression to my Category group:

=Day(Fields!Created.Value)

The 'Created' line displays just fine, yet the 'Closed' line seems to jump around at random.  It seems that I can't further group by day on the 'Closed' column; this breaks the chart even further.  Here's an example image:

Please let me know if I haven't been clear in any way.

EDIT:
I would like to be clear that I've explored the option of creating a new dataset for this chart.  I've been able to group counts by day, but only for 'Created' or 'Closed', not both. For example:

If there's a way to combine these into one SQL query, that would fix my problem.

*****SOLUTION UPDATE*****

Here's is the query that has given me the data I need.  Thanks, mmarie!

WITH X AS (SELECT      TOP (30) n = ROW_NUMBER() OVER (ORDER BY m1.number) - 1
FROM          [master].dbo.spt_values AS m1 CROSS JOIN
                        [master].dbo.spt_values AS m2), last30days AS
    (SELECT      dt = cast(DATEADD(DAY, (- 1 * n), getdate()) AS date)
      FROM           X)
    SELECT      dt.dt, CASE WHEN tcr.CreatedCount IS NULL THEN 0 ELSE tcr.CreatedCount END AS 'CreatedCount', CASE WHEN tcl.ClosedCount IS NULL
                             THEN 0 ELSE tcl.ClosedCount END AS 'ClosedCount'
     FROM           last30days dt LEFT JOIN
                                 (SELECT      COUNT(a.TicketNbr) AS CreatedCount, CAST(a.date_entered AS date) AS 'DayCreated'
                                   FROM          v_rpt_Service a
                                   WHERE       (closed_by LIKE @tech + '%')
                                   GROUP BY CAST(date_entered AS date)) tcr ON dt.dt = tcr.DayCreated LEFT JOIN
                                 (SELECT      COUNT(a.TicketNbr) AS ClosedCount, CAST(a.date_closed AS date) AS 'DayClosed'
                                   FROM           v_rpt_Service a
                                   WHERE       (closed_by LIKE @tech + '%')
                                   GROUP BY CAST(date_closed AS date)) tcl ON dt.dt = tcl.DayClosed

Since I can't see your table structure and I don't know the size of your data, here's something that will return the right answer but may or may not need to be tweaked for performance. You can write a query that groups both created and closed tickets together by date.  Although you could just write two queries and do a full outer join, I would guess you want SSRS to accurately represent days with no ticket activity in addition to filtering the data for the date range at the source instead of in the presentation layer, so I would suggest creating a date table/CTE/query. If you already have a date table, use it.  If not, here is a way to create what you need for the last 30 days. You could also make it a stored procedure and parameterize it to choose the number of days.

WITH X AS
(
  SELECT TOP (30) n = ROW_NUMBER() OVER (ORDER BY m1.number)-1
  FROM [master].dbo.spt_values AS m1
  CROSS JOIN [master].dbo.spt_values AS m2
)
,last30days as (
SELECT dt = cast(DATEADD(DAY, (-1*n), getdate()) as date)
  FROM X
)
select dt.dt, tcr.ticketscreated, tcl.ticketsclosed
from last30days dt
  left join (select cast(createddate as date) createdate, count(ticketid) ticketscreated
            from dbo.tickets
            group by cast(createddate as date)) tcr on dt.dt = tcr.createdate
  left join (select cast(closeddate as date) closedate, count(ticketid) ticketsclosed
            from dbo.tickets
            where closeddate is not null
            group by cast(closeddate as date) ) tcl on dt.dt = tcl.closedate

Once you have added that query as a dataset, you can build your chart.

Values should be Sum(ticketscreated) and Sum(ticketsclosed)
Category Groups should be dt


CPU Utilization by Long running Query Is there any way to find how much CPU is getting consumed by a query running for long time in Database?

There are several options because DB2 LUW has the older snapshot monitoring and a new monitoring facility integrated into the Workload Manager. Both facilities offer views and table functions to consume the data as well as tools on top.

For an easy way to start I would suggest to use db2top. Connect to a database and use, e.g., "D" to go to the Dynamic SQL screen. It shows Execution Time, CPU Time, Rows Read and Written, and some more information.

As an alternative take a look at the total_cpu_time monitoring element. There are functions and views listed in which that time is reported back.


Why do MySQL MyISAM table indexes go out of date? I have few MyISAM tables, where only the auto_increment primary key is up to date with the number of columns, but not other indexes.

Any idea, why this happens?

This is only a guess but here it goes...
The only way I know that a PRIMARY KEY for a MyISAM table would be OK and all secondary indexes be out-of-date would be under this unique circumstance:
Running this
ALTER TABLE mytable DISABLE KEYS;

and forgetting to undo it with
ALTER TABLE mytable ENABLE KEYS;

When you disable keys, the PRIMARY KEY and all UNIQUE keys are unaffected. When you enable keys, all secondary indexes get rebuilt.
According to MySQL Documentation on ALTER TABLE:

If you use ALTER TABLE on a MyISAM table, all nonunique indexes are created in a separate batch (as for REPAIR TABLE). This should make ALTER TABLE much faster when you have many indexes.
This feature can be activated explicitly for a MyISAM table. ALTER TABLE ... DISABLE KEYS tells MySQL to stop updating nonunique indexes. ALTER TABLE ... ENABLE KEYS then should be used to re-create missing indexes. MySQL does this with a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup. Using ALTER TABLE ... DISABLE KEYS requires the INDEX privilege in addition to the privileges mentioned earlier.
While the nonunique indexes are disabled, they are ignored for statements such as SELECT and EXPLAIN that otherwise would use them.

SUGGESTION
First, make a copy of the table.
Try running
ALTER TABLE mytable ENABLE KEYS;

and see if it does anything. If not, run
REPAIR TABLE mytable;

Give it a Try !!!


Rebuilding system and tables, should I change primary key to int? I am in the process of rebuilding a current system (orders, invoicing, payments, Vendors, etc), which includes normalizing the current tables with more tables and lookup tables to spead up queries,reporting, and implement new system features.

Their current customer table using a CustomerNumber as PK with a format like 'A1005'.  The letter is the first letter of the customer name, and then the number is incremented by 1 for each additional customer with that letter, starting at 1000.  The customer wants to keep the CustomerNumber field as part of the new system, so the addition of an int PK field would be for internal purposes only.

Once the new system is build, I have to convert the old data/tables to the new system tables. If I did add an in PK, then I would have to change all the FKs in the other related tables (orders, billing, shipping info, etc) to link instead to the new int PK field.

This user had only about 1500 customers, but has 20k to 100k records in the other tables (orders, billing, etc). Searches are often made on the CustomerNumber or CustomerName.

So I am wondering, should I add a integer primary key to the customer table for all the reasons that using an int PK is a good idea? Would there be any benefit to changing the primary key for this table in the new system? And would those benefits compensate for the more complex conversion I would need to do for the old data to import into the new system?

Thanks!

No, it is complete overkill for the size of this system and an unnecessary complication for an established database. As for "all the reasons that using an int PK is a good idea", those reasons only apply in either VERY large systems (the performance argument), in systems without CASCADE UPDATE on FKs (the immutable argument), or where a overly-simplistic ORM (or similar style) is in use (the "object identifier" argument).

Keep it simple and work within the existing design.  You're just creating a ton of extra work and risk of errors if you try and retro-fit a new set of keys onto the existing data.


DBO execute returns true but doesn't execute I am having a bit of trouble with some of my code. This is the code:

$STMT3 = $PDO->prepare('
   INSERT INTO `lotteries_entries` (steamID, lottery_id) VALUES (:STEAMID, :ID);
   UPDATE `lotteries_entries` SET `winner` = 1 WHERE `lottery_id` = :ID ORDER BY RAND() LIMIT 1;
   UPDATE `lotteries` SET `status` = 1 WHERE `id` = :ID;
   INSERT INTO `lotteries` (status, type) VALUES (0, :TYPE);'
);

$STMT3->bindParam(':STEAMID', $_SESSION['STEAM_steamid'], \PDO::PARAM_INT);

$STMT3->bindParam(':ID', $result['id'], \PDO::PARAM_INT);

$STMT3->bindParam(':TYPE', $result['type'], \PDO::PARAM_INT);

$STMT3->execute();

Although the $STMT3 returnes true (by using var_dump to check it), it unfortunatly never Inserts or Updates any of the tables it was told to. I've tried to run the script on the MySQL "SQL" tab (Directly through SQL) and it work fine. No errors are thrown through the script, and the SQL code isn't problematic.

Please not i use transactions, and yes i have started an commited the transaction. Both start and commit is reached by the script without any difficulty.

To see the whole code please visit: https://gist.github.com/vasil7112/8913f002940aaeb2f682

Kind regards and wish you have had a wonderful year.

SOLUTION

The problem was fixed by adding this line after the execute.
$STMT3->closeCursor();

Thanks everyone that tried to help :)


Would SQL in a cluster failover when transaction log for database is full? I have a Microsoft SQL Cluster with one instance. If one of the databases is full, would SQL in a cluster failover when transaction log for database is full?

If it depends on the reason the transaction log is full please refer to following::

due to 'log_backup'
due to 'availability replica'
active_transaction
'replication'
'CHECKPOINT"

No, it shouldn't because that is not a server level event that the cluster would be aware of when it happens.

I seem to recall coming across an article that referenced specific events that could cause a failover but can't find it right now.


psql table description in psql if i do \d+, I get something like

 Schema |          Name           |   Type   | Owner  |    Size    |     Description
--------+-------------------------+----------+--------+------------+-------------
 norsar | Routing Result          | table    | morten | 16 kB      |
 norsar | Routing Result_id_seq   | sequence | morten | 8192 bytes |

and so on. The last field, "Description" is always blank. Where does that field read its content? - and thereby, how can I set a description on a table? In some cases that could be quite useful - the same thing for description on columns that are shown if i do \d+ <tablename>.

I have tried \set ECHO_HIDDEN ON, but that didn't make it much clearer.

That shows comments that you define using the command comment on:

The following table definition:

create table foo
(
  id integer primary key,
  name text not null
);

comment on table foo is 'This is the famous foo table';
comment on column foo.id is 'This is the primary key';
comment on column foo.name is 'This is the name of the thing';

Will then show up like this:

postgres> \d+
                                List of relations
 Schema |   Name    | Type  | Owner  |    Size    |         Description
--------+-----------+-------+--------+------------+------------------------------
 public | foo       | table | thomas | 8192 bytes | This is the famous foo table
(1 row)

postgres> \d+ foo
                                             Table "public.foo"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target |          Description
--------+---------+-----------+----------+---------+----------+--------------+-------------------------------
 id     | integer |           | not null |         | plain    |              | This is the primary key
 name   | text    |           | not null |         | extended |              | This is the name of the thing
Indexes:
    "foo_pkey" PRIMARY KEY, btree (id)

postgres>


Difference between worker threads and sessions in SQL Server I'm looking at Azure Premium/P3 database offering under "Overview of the Performance Model" on http://msdn.microsoft.com/en-US/library/azure/dn741336.aspx

What's the difference between worker threads and sessions? Am I getting it right when I think there could be at most 16000 client machines opening a concurrent session to the same database but only 1600 queries can be executed concurrently?

It depends on the session's execution plan. If a session executes a query with a serial plan then yes, you can say 1600 queries, but if it runs a query with a parallel plan, then a session can have multiple worker threads per session.

Tasks, Workers, Threads, Scheduler, Sessions, Connections, Requests – what does it all mean?

  Worker (worker thread) – This is the logical SQL Server representation of a thread (think of it as a wrapper on top of the OS thread). It is a structure within the Scheduler which maintains SQL Server-specific information about what a worker thread does. sys.dm_os_workers. Workers are the humble servants who carry out the task assigned to them by the Master (scheduler).

  Sessions – when the client application connects to SQL Server the two sides establish a "session" on which to exchange information. Strictly speaking a session is not the same as the underlying physical connection, it is a SQL Server logical representation of a connection. But for practical purposes, you can think of this as being a connection (session =~ connection). See sys.dm_exec_sessions


How to resolve "logical replication target relation schema.table is missing some replicated columns" error in postgresql logical replication? I'm getting

"logical replication target relation schema.table is missing some replicated columns"

error in logical replication. I'm using all table replication. Due to some changes I wanted to delete a column from both subscriber and publisher. I dropped column from subscriber end first and then from publisher(it should've been other way around). But deletion from publisher took time and logical replication broke.
I tried adding columns at both end in every way. I also tried refreshing the publication at subscriber end. I also enabled and disabled the subscription. Still the postgres gives same error.
I'm using PostgreSQL 12.3.
How do I start the logical replication in such case without any data loss?
-------------UPDATE---------------
I resolved it by dropping the subscription and recreated it with copy_data = false. Later I copied the data missing on the subscriber side.
This was quite a headache and a long way to do this. Still looking for optimal solutions for this problem. Optimal solutions for this are welcomed.

Simply add the column again at the subscribed database and wait until replication has caught up before dropping the column again.


Does the PCI require to protect the Credit Card Info in the transaction log? The PCI Data Security Standard https://www.pcisecuritystandards.org/documents/pci_dss_v2.pdf
forbid storing Credit Card info in the transaction log

3.2.1 For a sample of system components, examine data sources, including but not limited to the following, and verify that the full contents of any track from the magnetic stripe on the back of card or equivalent data on a chip are not stored under any circumstance:
Incoming transaction data

All logs (for example, transaction, history, debugging, error)
History files
Trace files
Several database schemas
Database contents

Could anybody with read access to the log or log backup file be able to read CC number?
It is not in a clear text. Still, is it possible to decript it?
Is it enough to restrict user access to the log and log backup files?

This means text logs, such as those from Log4net.
Not database redo/undo/transaction files

Why?

PCI says that Credit card numbers (PAN) should be sent and stored as encrypted in the database, which means only the encrypted value will appear in any database log file (whether an error log or the actual redo/undo/transaction log file). See Page 8 and item 3.4 of your document for this.

Edit, after @Shark's observation

The database doesn't do encryption/decryption: only storage, no processing
The sending/receiving application does encryption/decryption: only processing, no storage


SQL Server Mirroring and automatic failover - why don't all DBs failover to the mirror? On SQL Server 2008 R2, I'm mirroring 20 databases on my SharePoint SQL Server with a witness, high-safety.  All mirrored DBs have the default mirroring_connection_timeout = 10.  And, occasionally, a handful of DBs will failover to the mirror (not more than once a month).  Today, it was 3 DBs and the only abnormality I noticed was some highish CPU at the time of failover.

I assume that the 10 second timeout for receiving a PING has been exceeded and failover occurs.  What I don't understand is why, if all DBs have the same timeout they don't all failover when this happens?

The issue is that there is a separate ping for each database, not one for all. So some are successful and some fail. If you need all of the databases to fail over, I outlined a process (using service broker and event notifications) to detect a failover and make sure all mirrored databases fail over in my book Pro SQL Server 2008 Mirroring.

A lot of this information is also located on my blog here Database Mirroring Automation and here: How do I automatically respond to mirrored database failovers?


Does local.oplog.rs hold all data in mongodb replication? I'm trying to do backup full and incremental backup, I wonder does local.oplog.rs collecitons contain all info that I need to do backup and incremental backup?

The MongoDB oplog is a special capped collection including all data used for replication. The oplog alone only includes an idempotent log of data changes. You can use the oplog for incremental backup and point-in-time restoration for a replica set, but note that an oplog backup is only valid to apply against an existing full data backup of the same replica set with a common oplog entry.

If you want to backup the oplog.rs collection independent of the rest of the data files you will have to use mongodump, which can have a significant effect on a production deployment as all of the oplog data needs to be read into memory by mongodump.  The oplog.rs collection also has a special caveat on indexing: it is designed to be scanned in $natural order and does not support adding any indexes.

A typical incremental backup approach for a production system is using filesystem snapshots assuming your deployment (and filesystem) meet the requirements for a consistent backup. Filesystem snapshots capture the changes since the last snapshot, so are incremental after the first snapshot. Ongoing snapshots are less impactful for an active deployment than mongodump.

Another common approach is using agent-based backup (for example, MongoDB Cloud Manager or MongoDB Ops Manager). Agent-based approaches generally also use the oplog for real-time backup. See Cloud Manager Backup Preparations for some background.

The MongoDB manual is the best reference for supported backup methods for your version of MongoDB server. Backing up using mongodump for the oplog alone is not a recommended (or documented) backup method.


MySQL error 1066 - Error Code: 1066. Not unique table/alias: 'TAXES' I have two MySQL statemenents:

SELECT PRODUCTS.REFERENCE,PRODUCTS.NAME,PRODUCTS.PRICEBUY,PRODUCTS.PRICESELL,
       SUM(TICKETLINES.UNITS) AS UNITS,
       SUM(TICKETLINES.PRICE * TICKETLINES.UNITS) AS SUBTOTAL,
       SUM((TICKETLINES.PRICE * TICKETLINES.UNITS) * TAXES.RATE) As TAXES,
       SUM(TICKETLINES.PRICE * TICKETLINES.UNITS)
        + SUM((TICKETLINES.PRICE * TICKETLINES.UNITS) * TAXES.RATE) AS GROSSTOTAL
FROM TICKETLINES
LEFT OUTER JOIN PRODUCTS ON TICKETLINES.PRODUCT = PRODUCTS.ID
LEFT OUTER JOIN TICKETS ON TICKETS.ID = TICKETLINES.TICKET
LEFT OUTER JOIN RECEIPTS ON RECEIPTS.ID = TICKETS.ID, TAXES
WHERE RECEIPTS.ID = TICKETS.ID AND TICKETS.ID = TICKETLINES.TICKET
AND TICKETLINES.PRODUCT = PRODUCTS.ID
AND TICKETLINES.TAXID = TAXES.ID
GROUP BY PRODUCTS.REFERENCE, PRODUCTS.NAME,PRODUCTS.PRICEBUY,PRODUCTS.PRICESELL
ORDER BY GROSSTOTAL DESC
LIMIT 10

and

SELECT PRODUCTS.ID, PRODUCTS.REFERENCE, PRODUCTS.CODE, PRODUCTS.NAME, PRODUCTS.ISCOM,
       PRODUCTS.ISSCALE, PRODUCTS.PRICEBUY, PRODUCTS.PRICESELL, PRODUCTS.TAXCAT,
       PRODUCTS.CATEGORY,PRODUCTS.ATTRIBUTESET_ID, PRODUCTS.IMAGE, PRODUCTS.ATTRIBUTES,
       PRODUCTS.ISKITCHEN, PRODUCTS.ISSERVICE, PRODUCTS.DISPLAY, PRODUCTS.ISVPRICE,
       PRODUCTS.ISVERPATRIB, PRODUCTS.TEXTTIP, PRODUCTS.WARRANTY, PRODUCTS.STOCKUNITS,
       TAXES.NAME, TAXES.RATE, PRODUCTS.STOCKVOLUME
FROM PRODUCTS
INNER JOIN PRODUCTS_CAT ON PRODUCTS.ID=PRODUCTS_CAT.PRODUCT
JOIN TAXCATEGORIES ON PRODUCTS.TAXCAT=TAXCATEGORIES.ID
JOIN TAXES ON TAXCATEGORIES.ID=TAXES.ID
ORDER BY PRODUCTS.NAME

Now, I am trying to combine these two statements into one, here is what I've got:

SELECT PRODUCTS.ID, PRODUCTS.REFERENCE, PRODUCTS.CODE, PRODUCTS.NAME, PRODUCTS.ISCOM,
       PRODUCTS.ISSCALE, PRODUCTS.PRICEBUY, PRODUCTS.PRICESELL, PRODUCTS.TAXCAT,
       PRODUCTS.CATEGORY, PRODUCTS.ATTRIBUTESET_ID, PRODUCTS.IMAGE, PRODUCTS.ATTRIBUTES,
       PRODUCTS.ISKITCHEN, PRODUCTS.ISSERVICE, PRODUCTS.DISPLAY, PRODUCTS.ISVPRICE,
       PRODUCTS.ISVERPATRIB, PRODUCTS.TEXTTIP, PRODUCTS.WARRANTY, PRODUCTS.STOCKUNITS,
       TAXES.NAME, TAXES.RATE, PRODUCTS.STOCKVOLUME,
       SUM(TICKETLINES.UNITS) AS UNITS,
       SUM(TICKETLINES.PRICE*TICKETLINES.UNITS) AS SUBTOTAL,
       SUM((TICKETLINES.PRICE*TICKETLINES.UNITS)*TAXES.RATE) AS TAXESTOTAL,
       SUM(TICKETLINES.PRICE*TICKETLINES.UNITS)
         +SUM((TICKETLINES.PRICE*TICKETLINES.UNITS)*TAXES.RATE) AS GROSSTOTAL
FROM TICKETLINES
INNER JOIN PRODUCTS_CAT ON PRODUCTS.ID=PRODUCTS_CAT.PRODUCT
JOIN TAXCATEGORIES ON PPRODUCTS.TAXCAT=TAXCATEGORIES.ID
JOIN TAXES ON TAXCATEGORIES.ID=TAXES.ID
LEFT OUTER JOIN PRODUCTS ON TICKETLINES.PRODUCT=PRODUCTS.ID
LEFT OUTER JOIN TICKETS ON TICKETS.ID=TICKETLINES.TICKET
LEFT OUTER JOIN RECEIPTS ON RECEIPTS.ID=TICKETS.ID, TAXES
WHERE RECEIPTS.ID=TICKETS.ID AND TICKETS.ID=TICKETLINES.TICKET
AND TICKETLINES.PRODUCT=PRODUCTS.ID
AND TICKETLINES.TAXID=TAXES.ID
ORDER BY PRODUCTS.NAME

Why upper statement does not execute and reports error Error Code: 1066. Not unique table/alias: 'TAXES'
?

Because you have 2 times TAXES in your FROM clause (here between **):

FROM TICKETLINES
INNER JOIN PRODUCTS_CAT ON PRODUCTS.ID=PRODUCTS_CAT.PRODUCT
JOIN TAXCATEGORIES ON PPRODUCTS.TAXCAT=TAXCATEGORIES.ID
JOIN **TAXES** ON TAXCATEGORIES.ID=TAXES.ID
LEFT OUTER JOIN PRODUCTS ON TICKETLINES.PRODUCT=PRODUCTS.ID
LEFT OUTER JOIN TICKETS ON TICKETS.ID=TICKETLINES.TICKET
LEFT OUTER JOIN RECEIPTS ON RECEIPTS.ID=TICKETS.ID, **TAXES**


Unable to add db_datawriter to user defined role I want to grant a user defined role, read and write permissions to the entire database and add users to it. I was unable to do this using GUI. So I tried the solution given at the link - https://www.sqlservercentral.com/Forums/Topic1141208-1526-1.aspx but it gives me the error mentioned below when I try to add db_datawriter to the role I created.

Cannot use the special principal 'db_datawriter'

The command I executed - EXEC sp_addrolemember 'DR_User', 'db_datawriter'

Please help me figure out the right way to achieve this.

JKay, you are a little mistaken about the syntax of this command:

EXEC sys.sp_addrolemember [ @rolename = ] 'role',
    [ @membername = ] 'security_account'

So in your particular case the command is:

EXEC sys.sp_addrolemember @rolename = 'db_datawriter', @membername = 'DR_User';


Workaround for using functions in restricted system I have access to an environment where I can run SELECT statements but cannot call functions. I know what all the code is inside the functions.

The functions are merely lookups (SELECTs and not INSERTs or UPDATEs)

Is there a way to simulate or declare the functions in a local context to get around this problem.?

If the functions are just doing SELECT statements then you can simulate them with an inline view ...

  select t.*
         , notaf.descr
  from  t
        , ( select code, descr from lookup_tbl ) notaf
  where t.col23 = notaf.code

... or a sub-query factoring clause ...

with notaf as ( select code, descr from lookup_tbl )
select t.*
         , notaf.descr
from  t
       , notaf
where t.col23 = notaf.code

--- or even a simple join (using ANSI syntax for variety!) ...

select t.*
         , lookup_tbl.descr
from  t
      join lookup_tbl
           on  t.col23 = lookup_tbl.code

You can use DUAL if the function does something computational (rather than a data lookup) but you may need to pfaff around with nested views to simulate passing parameters.


How does TIMESTAMP WITH LOCAL TIME ZONE behave when exporting data? Consider the following table:

CREATE TABLE MY_DATA (
    MY_DATA_ID NUMBER(38,0) PRIMARY KEY,
    THE_DATA VARCHAR(10) NOT NULL,
    DATA_TIMESTAMP TIMESTAMP (6) WITH LOCAL TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL
);

(Note that TIMESTAMP without further qualification defaults to the data type used above.)

What happens if I export this data using Data Pump and then import it into another database that has a different time zone? Will the stored time be adjusted for the new time zone, or will it behave as though the off set was changed without changing the rest of the data?

For example, if

The original database is US Central time
A row contains 2017-10-25 12:14:38
We import the data into a database using US Eastern time

will the time be adjusted to 2017-10-25 12:14:38 -04:00 (as in replacing the offset without changing the date/time) or 2017-10-25 13:14:38 -04:00 (= 2017-10-25 12:14:38 -05:00)?

My apologies for not testing this myself. I don't have access to another database with a different time zone, but I wish to know to inform my choice of data type.

I made a test and exported table MY_DATA as given in your example.

Then I had a look at the dmp-File using a HEX editor. The file contains

<?xml version="1.0"?>
<ROWSET>
    <ROW>
        <STRMTABLE_T>
            <VERS_MAJOR>1</VERS_MAJOR>
            <VERS_MINOR>1 </VERS_MINOR>
            <VERS_DPAPI>3</VERS_DPAPI>
            <ENDIANNESS>2</ENDIANNESS>
            <CHARSET>WE8ISO8859P1</CHARSET>
            <NCHARSET>AL16UTF16</NCHARSET>
            <DBTIMEZONE>+01:00</DBTIMEZONE>
            <FDO>0000006001240F050B0C030C0C0504050D0609070805050505050F05050505050A050505050504050607080823472347081123081141B0470083001F07D00300000000000000000000000000000000000000000000000000000000000000000000000000</FDO>
            <OBJ_NUM>3286546</OBJ_NUM>
            <OWNER_NAME>PMDSYS</OWNER_NAME>
            <NAME>MY_DATA</NAME>
            <PROPERTY>536870912</PROPERTY>
            <COL_LIST>
                <COL_LIST_ITEM>
                    <OBJ_NUM>3286546</OBJ_NUM>
                    <COL_NUM>1</COL_NUM>
                    <INTCOL_NUM>1</INTCOL_NUM>
                    <SEGCOL_NUM>1</SEGCOL_NUM>
                    <COL_SORTKEY>1</COL_SORTKEY>
                    <BASE_INTCOL_NUM>1</BASE_INTCOL_NUM>
                    <BASE_COL_TYPE>0</BASE_COL_TYPE>
                    <PROPERTY>0</PROPERTY>
                    <NAME>MY_DATA_ID</NAME>
                    <TYPE_NUM>2</TYPE_NUM>
                    <LENGTH>22</LENGTH>
                    <PRECISION_NUM>38</PRECISION_NUM>
                    <SCALE>0</SCALE>
                    <NOT_NULL>1</NOT_NULL>
                    <CHARSETID>0</CHARSETID>
                    <CHARSETFORM>0</CHARSETFORM>
                    <CHARLENGTH>0</CHARLENGTH>
                </COL_LIST_ITEM>
                <COL_LIST_ITEM>
                    <OBJ_NUM>3286546</OBJ_NUM>
                    <COL_NUM>2</COL_NUM>
                    <INTCOL_NUM>2</INTCOL_NUM>
                    <SEGCOL_NUM>2</SEGCOL_NUM>
                    <COL_SORTKEY>2</COL_SORTKEY>
                    <BASE_INTCOL_NUM>2</BASE_INTCOL_NUM>
                    <BASE_COL_TYPE>0</BASE_COL_TYPE>
                    <PROPERTY>0</PROPERTY>
                    <NAME>THE_DATA</NAME>
                    <TYPE_NUM>1</TYPE_NUM>
                    <LENGTH>10</LENGTH>
                    <NOT_NULL>1</NOT_NULL>
                    <CHARSETID>31</CHARSETID>
                    <CHARSETFORM>1</CHARSETFORM>
                    <CHARLENGTH>10</CHARLENGTH>
                </COL_LIST_ITEM>
                <COL_LIST_ITEM>
                    <OBJ_NUM>3286546</OBJ_NUM>
                    <COL_NUM>3</COL_NUM>
                    <INTCOL_NUM>3</INTCOL_NUM>
                    <SEGCOL_NUM>3</SEGCOL_NUM>
                    <COL_SORTKEY>3</COL_SORTKEY>
                    <BASE_INTCOL_NUM>3</BASE_INTCOL_NUM>
                    <BASE_COL_TYPE>0</BASE_COL_TYPE>
                    <PROPERTY>0</PROPERTY>
                    <NAME>DATA_TIMESTAMP</NAME>
                    <TYPE_NUM>231</TYPE_NUM>
                    <LENGTH>11</LENGTH>
                    <SCALE>6</SCALE>
                    <NOT_NULL>1</NOT_NULL>
                    <CHARSETID>0</CHARSETID>
                    <CHARSETFORM>0</CHARSETFORM>
                    <CHARLENGTH>0</CHARLENGTH>
                </COL_LIST_ITEM>
            </COL_LIST>
        </STRMTABLE_T>
    </ROW>
</ROWSET>

As you can see, it contains information <DBTIMEZONE>+01:00</DBTIMEZONE>, so we can assume when you import such file into anther database running on different DBTIMEZONE  the values will be properly converted.


Is there a Windows Performance Counter which contains the "Log Send Rate" for Availability Groups? I wish to be able to get the "Log Send Rate kb/s", as per the SSMS AG dashboard (which is also available via log_send_rate column on the DMV sys.dm_hadr_database_replica_states)

I'd like to get it as a Windows Performance Counter... looking at the available counters for the Database Replica category, the nearest is "Log Bytes Received/sec", which doesn't appear to report the same value.

Does anyone know if there is a performance counter or will I need to rely on the DMV?

The DMV column log_send_rate will tell you Rate at which log records are being sent to the secondary databases, in kilobytes (KB)/second.
Depending on what SP + CU you are running, you may want to look into - KB 3012182 FIX: Log_Send_Rate column cannot reflect the rate accurately in SQL Server 2012 or in SQL Server 2014
Below PERFMON counter will tell you how many bytes were sent

SQL Server:Availability Replica --> Bytes Sent to Replica/sec
Number of bytes sent to the remote availability replica per second. On the primary replica this is the number of bytes sent to the secondary replica. On the secondary replica this is the number of bytes sent to the primary replica.


How to get all months in a query where one month has no matches? I need to obtain every month by year (to fill up a graph). However, I found out that the first month had no data whatsoever and I wanna reflect that. The query is this one:

SELECT tblMes.id, COUNT(*) AS ingreso
FROM tblTicket
LEFT JOIN tblMes ON MONTH(tblTicket.fechaIngreso) = tblMes.id
WHERE year(fechaIngreso) = 2017
GROUP BY tblMes.id
ORDER BY tblMes.id asc

The result of the query is this one:

Month | Qty
--------------
"2"   | "1066"
"3"   | "1395"
"4"   | "761"
"5"   | "1316"
"6"   | "879"
"7"   | "1039"
"8"   | "1099"
"9"   | "577"
"10"  | "1064"
"11"  | "1268"
"12"  | "1188"

The first row should be "1" | "0"

Why is the left join not enough?

I think you just had your tables backwards (the first table is all the rows you want, the outer table is all the rows that might exist). You also need the outer table clauses to be part of the join criteria (ON), rather than filter criteria (WHERE), otherwise your outer join turns into an inner join.

SELECT m.id, COUNT(t.fechaIngreso) AS ingreso
FROM dbo.tblMes AS m
LEFT OUTER JOIN dbo.tblTicket AS t
ON MONTH(t.fechaIngreso) = m.id
AND year(t.fechaIngreso) = 2017
GROUP BY m.id
ORDER BY m.id;

This is assuming tblMes is just a table with 12 rows, with one row for each of the 12 months. You can also do this without that table pretty easily:

;WITH Months(id) AS
(
  SELECT 1 UNION ALL SELECT id+1 FROM Months WHERE id <= 11
)
SELECT m.id, COUNT(t.fechaIngreso) AS ingreso
FROM Months AS m
LEFT OUTER JOIN dbo.tblTicket AS t
ON m.id = MONTH(t.fechaIngreso)
AND YEAR(t.fechaIngreso) = 2017
GROUP BY m.id
ORDER BY m.id;

On modern enough versions of SQL Server, and with an index on fechaIngreso, this may perform better:

ON t.fechaIngreso >= DATEFROMPARTS(2017, m.id, 1)
  AND t.fechaIngreso < DATEADD(MONTH, 1, DATEFROMPARTS(2017, m.id, 1))

The reason is that MONTH() and YEAR() functions will force a scan, because the result of that computation will have to be evaluated for every single row. Avoiding this work is preferable, IMHO, even if you don't have a supporting index on that column now.


MySql InnoDB tables are missing after restarting I had a MySql database with a few InnoDB tables. After restarting the server, and starting MySql Server again, I connected to the MySql Server using MySql Workbench, and couldn't see the database at all (it even doesn't show up empty, it's just not there). It's important to note that the server was brutally shut down (I had no choice), so maybe MySql Server wasn't closed normally, and it caused a problem.

I am new to MySQL area with innoDB. Any help is really appreciated.

Thank you in advance.

Regards, Ron

If you have MySQL 5.6 running before, then the MySQL data is located in C:\Program Files\MySQL\MySQL Server 5.6\data\

If you had recently installed MySQL 5.7, that instance has its own MySQL data in C:\Program Files\MySQL\MySQL Server 5.7\data\.

You need to migrate the data from MySQL 5.6 into MySQL 5.7.

If you are not sure what what version of MySQL was actually running on your Windows Server, here are two things you must investigate:

INVESTIGATION #1

Goto to Windows Explorer
Navigate to C:\Program Files\MySQL

INVESTIGATION #2

For every folder under C:\Program Files\MySQL

Go inside the subfolder MySQL 5.x
Locate the file my.ini

Whichever folder has my.ini was in the installed version of MySQL

If none of them has my.ini, you have to dig a little deeper

INVESTIGATION #3

Click Start -> Control Panel -> Administrative Tools -> Services
Once the Services panel is open, scroll down alphabetically to MySQL.
Right click on MySQL
Choose Properties

This will show you the path to the MySQL binary it called and the my.ini associated with it. If it does not show a my.ini, then the my.ini should appear in the parent directory of the executable.

If this does not work, then you will have to look inside MySQL Workbench for the original connection settings you were connecting with.


PostgreSQL: Find records with single quotes I have identified records in one of our tables with TWO single quotes.
So the record field appears as: ''
What SQL could I utilize to find all the record occurrences with ''
I have tried '%' and also '%''%' with no success.

'%''%'  will look for entries with a one single quote (as a single quote is escaped by doubling it). If you want to search for two single quotes, you need 4 quotes (because of the escaping), e.g.:
where the_column like '%''''%'

alternatively you can use dollar quoting to avoid the escaping
where the_column like $$%''%$$


Store different rows type in single table I'd like to create a DB to store different rows type. It will be kind of event list with time stamp. So I'd like to have an Event table with relations to other tables - each table will be a row type (Transition, Position, Error):

Is this a good approach? Possible drawbacks:

as a result I'll have to take care that every row in Event exactly 1 FK not null as it can relate to exactly 1 event
when adding new event type I'll have to upgrade* Event
when I'll have many event types the result will be that a row in Event table will have mostly null columns and only 1 will be not null

EDIT: Transition, Position, Error, Location tables has unique columns which are not put in the image above, e.g. Transition has type, Position has 'latitude'/'longitude', Error has payload etc.

* By upgrade, I mean when I need to add new type to Event table I'll require rewrite whole Event table - as I'm working on Android it'll require using method SQLiteOpenHelper->onUpgrade.

You have it the other way round.

Put EventId and EventName on Event table and reference EventId in other tables. In other words,

Add EventId column in Transition, Position and Error tables.
Remove time_stamp, transition_id, position_id, and error_id from the Event table.


How can I synchronise Always Encrypted data from On-Premises to Azure SQL Database (near real time)? Let's say I have On Premises SQL Server 2016 SP1 and Azure SQL Database.

There is no way at this point to redirect whole solution directly to Azure SQL Database and I need to have a copy of the database in Azure (few tables in fact). It is required for some real-time analytics purpose. Data in Azure SQL Database can be decrypted (probably even should be).

Now, I know that there are solutions like:

Azure Data Factory (15 min delay)
Azure Sync (5 min delay)
Transactional Replication (near real time)
Self-developed application to read-decrypt-send data (near real time)

It looks like Replication fits our requirements. I tested it and I am able to establish this solution in my environment. Except that Replication doesn't support Always Encrypted feature and this is huge disadvantage.

Self-developed application is not good resolution either because a schema of tables can vary over time (replication resolve this by itself) and there will be probably other problems which pop up in the future. I tried it and it works but only with a hard-coded schema (number and names of columns).

Other solutions, I haven't tested them yet:

Strech Database (depends upon Azure Subscription, currently not available for me)
Third-party Software (which one? HVR?)
SSIS (looks like a good approach, but a schema can vary)

Is it possible to synchronise Always Encrypted data near real time using those tools or in any other way? Do you have any experience in that matter?

In general, any tool that copies data (e.g. using SELECT/INSERT or bulk select/copy) should work. If the target database (in SQL DB) uses a different set of keys than the original database (in SQL Server), the tool needs to have access to both keys (and it would need to re-encrypt the data during the migration). If the target database using the same keys as the source database, it should be possible to avoid re-encrypting the data (please, see https://blogs.msdn.microsoft.com/sqlsecurity/2016/01/07/best-practices-for-moving-data-encrypted-with-always-encrypted/).

SSIS support both options to migrate the data (with and without re-encryption), but I'm not sure if it meets your near-real time requirement. You will basically need to run the SSIS job periodically.

Stretch Database supports moving encrypted data (without re-encrypting), but does not replicate encryption metadata. To retrieve the plaintext values or run queries on the part of the table that "stretches" to SQL DB, an application needs to connect to SQL Server (which has encryption metadata and will delegate the query to SQL DB). Your app can't access plaintext by connecting to SQL DB directly.


unable to start mongod - Fatal assertion 28579 UnsupportedFormat I was frequently getting Segmentation fault (core dumped). Not knowing what to do, I just restarted my ubuntu. Then tried repair which failed because of Segmentation fault. Now when I am trying to start mongod

root@ubuntu-s-4vcpu-8gb-nyc1-01:~/mongo# mongod --dbpath /var/lib/mongodb
2018-11-12T10:13:15.235+0000 I CONTROL  [initandlisten] MongoDB starting : pid=2091 port=27017 dbpath=/var/lib/mongodb 64-bit host=ubuntu-s-4vcpu-8gb-nyc1-01
2018-11-12T10:13:15.235+0000 I CONTROL  [initandlisten] db version v3.2.21
2018-11-12T10:13:15.235+0000 I CONTROL  [initandlisten] git version: 1ab1010737145ba3761318508ff65ba74dfe8155
2018-11-12T10:13:15.235+0000 I CONTROL  [initandlisten] OpenSSL version: OpenSSL 1.0.2g  1 Mar 2016
2018-11-12T10:13:15.235+0000 I CONTROL  [initandlisten] allocator: tcmalloc
2018-11-12T10:13:15.235+0000 I CONTROL  [initandlisten] modules: none
2018-11-12T10:13:15.235+0000 I CONTROL  [initandlisten] build environment:
2018-11-12T10:13:15.235+0000 I CONTROL  [initandlisten]     distmod: ubuntu1604
2018-11-12T10:13:15.235+0000 I CONTROL  [initandlisten]     distarch: x86_64
2018-11-12T10:13:15.235+0000 I CONTROL  [initandlisten]     target_arch: x86_64
2018-11-12T10:13:15.235+0000 I CONTROL  [initandlisten] options: { storage: { dbPath: "/var/lib/mongodb" } }
2018-11-12T10:13:15.255+0000 I -        [initandlisten] Detected data files in /var/lib/mongodb created by the 'wiredTiger' storage engine, so setting the active storage engine to 'wiredTiger'.
2018-11-12T10:13:15.255+0000 W -        [initandlisten] Detected unclean shutdown - /var/lib/mongodb/mongod.lock is not empty.
2018-11-12T10:13:15.255+0000 W STORAGE  [initandlisten] Recovering data from the last clean checkpoint.
2018-11-12T10:13:15.255+0000 I STORAGE  [initandlisten] wiredtiger_open config: create,cache_size=4G,session_max=20000,eviction=(threads_min=4,threads_max=4),config_base=false,statistics=(fast),log=(enabled=true,archive=true,path=journal,compressor=snappy),file_manager=(close_idle_time=100000),checkpoint=(wait=60,log_size=2GB),statistics_log=(wait=0),verbose=(recovery_progress),
2018-11-12T10:13:15.310+0000 I STORAGE  [initandlisten] dropping unused ident: index-0-8381493109628483945
2018-11-12T10:13:15.312+0000 I CONTROL  [initandlisten] ** WARNING: You are running this process as the root user, which is not recommended.
2018-11-12T10:13:15.312+0000 I CONTROL  [initandlisten]
2018-11-12T10:13:15.312+0000 I CONTROL  [initandlisten]
2018-11-12T10:13:15.312+0000 I CONTROL  [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/defrag is 'always'.
2018-11-12T10:13:15.312+0000 I CONTROL  [initandlisten] **        We suggest setting it to 'never'
2018-11-12T10:13:15.312+0000 I CONTROL  [initandlisten]
2018-11-12T10:13:15.312+0000 I -        [initandlisten] Fatal assertion 28579 UnsupportedFormat: Unable to find metadata for table:index-0--3161084524998267994 Index: {name: _id_, ns: local.startup_log} - version too new for this mongod. See http://dochub.mongodb.org/core/3.4-index-downgrade for detailed instructions on how to handle this error.
2018-11-12T10:13:15.313+0000 I -        [initandlisten]

***aborting after fassert() failure

mongod -vvvvvv -dbpath /var/lib/mongodb

and remove corrupted datafile.

rm f index-1234-123456789123456789.wt

and then repair using following command

mongod --repair --dbpath /var/lib/mongodb/ --storageEngine wiredTiger

Note: Have a backup of /var/lib/mongodb


MySQL replication 'connecting to master' Anyone please give detailed documentation on MySQL Replication on Windows system. I replicated master to slave, but it is showing 'connecting to master'. Error number showing as 1045 and 2003 it's varying.

If you have validated the credentials you are using for replication and they work connecting through another client, your 1045 error may lie with the new default authentication plugin in MySQL8. When trying to connect with an insecure connection and an account created with the caching_sha2_password, it will continuously attempt the connection without being successful.

Try connecting using the same replication credentials from the slave to the master through a MySQL client. This will ensure your networking and credentials are both valid. If you cannot do this, check your network/firewall and your replication credentials.


SET DATEFORMAT problems I have some doubts about SET DATEFORMAT command.

1)Does this command is only used for inserting and updating date format and it does not effect the date format of that column? OR does this effect my table after inserting values too?

2) I tried using below command(for date datatype) and got error like This session's YDM date format is not supported when converting from this character string format to date, time, datetime2 or datetimeoffset. Change the session's date format or provide a style to the explicit conversion.
. why the error comes for date datatype(where as for smalldatetime it works as shown in link)?

CREATE TABLE #tempTable (DateFormatSample DATE)
SET DATEFORMAT MDY
INSERT INTO #tempTable
VALUES ('09/28/2007')
SET DATEFORMAT YDM
INSERT INTO #tempTable
VALUES ('2007/28/09')
SET DATEFORMAT YMD
INSERT INTO #tempTable
VALUES ('2007/08/28')
SELECT DateFormatSample
FROM #tempTable
DROP TABLE #tempTable

3)It was mentioned like "The setting of SET DATEFORMAT is set at execute or run time and not at parse time." What does it mean?

1) SET DATEFORMAT has no effect on the actual storage of the table. It is only used to format output and interpret strings. Behind the scenes, all the date formats are stored as integers in a canonical format.

The actual representation of the data type depends on which type is used. For example, SMALLDATETIME is an integer that stores the number of seconds since 1900-01-01. Some of this bubbles to the surface when you cast like this:

SELECT CAST(0 AS SMALLDATETIME)

Result: 1900-01-01 00:00:00

Similarly, for DATETIME:

SELECT CAST(0 AS DATETIME)

Result: 1900-01-01 00:00:00.000

Interestingly, this fails:

SELECT CAST(0 AS DATE)

With exception: Explicit conversion from data type int to date is not allowed.

As should be obvious, something isn't quite fleshed out with the new types.

2) This limitation is documented here: http://msdn.microsoft.com/en-gb/library/ms189491.aspx

  The DATEFORMAT ydm is not supported for date, datetime2 and
  datetimeoffset data types.

Why is this so? Inside the database engine DATE, DATETIME2 and DATETIMEOFFSET take different code paths than DATETIME and SMALLDATETIME (you can validate this with a profiler). Because the new date formats arrived in the SQL Server code base long after DATETIME/SMALLDATETIME - different programmers worked on it and hence, some functionality is not 100% aligned (yet). Another example: In early versions of SQL Server 2008R2, bulk loading a DATE was much slower than bulk loading a SMALLDATETIME, even though one is smaller than the other. This was fixed before release. It's the kind of thing that happens in a big organisation with lots of coders.

3) This means that it is not possible for you to know if the date format is valid until you actually try it. In your case, it means that even if you do an "show query plan" without execution, you will not know that there is an error in the format until you actually execute the query.


Does the Sql Server Agent account need sysadmin permission? I ran Brent Ozar's sp_blitz script, and one of the things it's complaining about is that my SQL Server Agent account has sysadmin permission. I removed the sysadmin permission, but then the agent wouldn't start. (See image below). There's nothing of note in the event log. When I restore the sysadmin permission the agent starts with no difficulty.

My account is a managed service account. None of the other managed service accounts I use (one for running reporting services, and one for running the SQL service) have sysadmin permission.

Is the sysadmin permission actually necessary? If not, what are the minimum permissions this account needs?

Further to Shawn's answer, the Books Online topic titled Select an Account for the SQL Server Agent Service also says:

The account that the SQL Server Agent service runs as must be a member of the following SQL Server roles:

The account must be a member of the sysadmin fixed server role.

To use multiserver job processing, the account must be a member of the msdb database role TargetServersRole on the master server.


MySQL tuning query - 5.6 I'm using Percona Server 5.6.31 on RHEL 6. 64G RAM/16vCPU - VMware. It's Completely InnoDB and the database size is 300G.

I have set Innodb_buffer_pool to 52G, innodb_buffer_pool_instances to default (8) and innodb_flush_method to O_DIRECT. Innodb_read_io_threads and write_io threads are set to 64.

Memory utilization is above 90% most of the time. CPU utilization varies. Sometimes spikes. vm.swappiness is set to default (60).

I have two queries:

What should be my value for innodb_buffer_pool_instances? Is it right or is a change required?
I read many articles suggesting that swappiness needs to be set to 1 or 0 or 10 for MySQL database servers. But I do not observe any swapping on the OS. Do I need to still swappiness to 0 or 1. Is it better to change the value on proactive basis or I should change only when it swaps?

I can provide more info if required.

Update:

Show variables and Show global status output can be seen here Config details

CPU utilization is often above 80%. Does CPU utilization only have to do with query tuning?

swappiness:

= 0 --> crash if you run out of memory -- not nice
small --> best
large --> might swap mysql preemptively, leading to poor performance

pool_instances -- should not be more than pool_size/1G; the specific value makes only a small difference in performance.  (Don't worry about it.)  Some say 16 should be the max number of instances, but I have not heard 'why'.
CPU spikes -- Usually a missing index or a poorly phrased query.  Find it and fix it.  (And/or ask for advice)
The buffer_pool(s) cache data and indexes.  It is (roughly) a least-recently-used cache.  It is also used for delaying index updates due to writes.
52G/64G -- reasonable.  It obviously can't hold the entire 300GB, but that may not be an issue -- especially if most of the action is in isolated portions.  Example:  Only "recent" data is actively accessed.  Counter example:  UUIDs have terrible performance when the data is bigger than the buffer_pool.

For further analysis of settings, post SHOW VARIABLES; and SHOW GLOBAL STATUS;.  (Not here; there is a size limitation.)


Using Postgres param in a function I'm trying to create a few functions to make the SQL queries between my SQLite and Postgres DB's the same.
One thing I would like to do is change a timestamp to a double via a function such as:
create function tz_to_double(a timestamp) returns DOUBLE PRECISION as $$
    SELECT EXTRACT(EPOCH FROM TIMESTAMP a);
$$ language sql;

Postgres however doesn't allow me to create this function. It errors out like so:
ERROR:  syntax error at or near "a"
LINE 2:  SELECT EXTRACT(EPOCH FROM TIMESTAMP a);

Would it be possible to create this function / use this as a param?

You are adding both Timestamp and a in extract function, just remove TIMESTAMP
create function tz_to_double(a timestamp) returns DOUBLE PRECISION as $$
    SELECT EXTRACT(EPOCH FROM  a);
$$ language sql;


Inserts very slow near the end of dataset I'm trying to insert Whois Records ( domain name and relevant info) into MongoDB after parsing them from CSV files. I currently have a collection for each tLD such as .com, .org etc.

During the initial insertion of the records( such as the quick insertions of the lest populated and obscure tLDs and initial .com etc insertion ), I get decent insert speeds of about 10k-15k inserts per second.

However this slows to a crawl, part way through. Since MongoDB uses a B-tree for indexing primaries, shouldnt the performance slowdown due to database size be minimal ?

Extra Information if relevant: MongoDB 3.4, WiredTiger, Java 1.8, primary index is the domain name itself

You have run out of resources!

With 202M records, even one record would be only 64 bytes (you can check with db.collection.stats().avgObjSize), the size is 12GB and then comes index. The optimal situation is when active data, (index and data) is in the memory. This case it is NOT. So, mongod starts using disk heavily and if your disk is not high IOPS (SSD base) and low latency base, your machine starts slowing down.

Can you (for start) increase memory to (at least) 16 - 32GB?


Why do maintenance plans exist as a feature? Do maintenance plans provide any functionality beyond their component parts? It would seem that the only value added is the convenience of grouping subplans into logical bundles, and the simplified GUI for creation. All the actual work is being done by the underlying SSIS tasks which are being scheduled as agent jobs. Is there anything more to it than that?

Underlying SSIS task that do not require SSIS to be installed to run, nor require you to edit via BIDS or SSDT-BI.

They are there more or less for easy deployment of basic database maintenance that does not require much knowledge to build. Is it something that fits any environment? I would say no, it is mostly for the one man/woman shops that just need something up and running quickly and is sufficient.

Now that is not to say in large environments maintenance plans don't have a purpose, just find they may not used as often. You might consider them a stepping stone to SSIS packages.


what is the difference between mysql community server and mysql cluster I am starting to look into different database's for different use cases.  I see there is a mysql community server and mysql cluster.  What is the difference between the two?  Are they both database engines/servers or is one an additional service for the other?

MySQL cluster is a storage engine for MySQL, which distributes and replicates data in an in-memory cluster (NDB data nodes) and can be queried via MySQL servers (SQL nodes).

Where it gets confusing, is that MySQL Cluster also has a native API where you can avoid the MySQL storage engine system and communicate directly to the data nodes with the NDB API, which is kind of NoSQL-like.  I'm not aware of any other storage engines that have this ability, and there are many large customers preferring this method of usage.

MySQL Cluster also has it's own release cycle and versioning system, and is currently at version 7.3.


Cannot run my template in .dbt extension in Oracle 12c DBCA The following is my template file testing.dbt. I kept it under C:\app\ORCLUSER\product\12.1.0\dbhome_1\assistants\dbca\templates. I'm trying to create it from Oracle 12c (Enterprise) DBCA. When I select create database and choose it. It says, the template file is in invalid template file and it is not in the expected mode. Please help me with this.

This is my testing.dbt file

For anyone facing the same problem: the reason of the error "The template data is not in the expected format" may be different interpretation of negative values between the different versions of DBCA.

Deletion of the following tags with negative values in the Testing.dbt attached by OP does the trick:

initSize
blockSize
increment
incrementPercent
minExtendsSize
minExtends
maxExtends
maxSize
uniAllocSize
id


What is the best design for dynamic assignment of locations for variable sized grid boxes to occupy? I want to build a database that will have limited single locations to put boxes. It's divided by lanes and bays.

Imagine a 4x4 grid, with a total of 16 slots, and every time I query the tables, I want to return one location for me to put boxes down.

My approach is to have every slot named and stored in database. And have a flag "occupied" and name for the box to keep track of the box location. The tricky part is there is different sizes of boxes. Some will occupied 3 slots, some will occupy 2 slots and some will occupy 1 slot.

How can I design the database structure so that it's easier for me to query the next available slot based on the size?

Responses to comments

  Is this some resource scheduling application?

It's more like a dynamic space allocation application.

  Are the boxes to be allocated are mono- or bi-dimensional? In other words, do you allocate a space of 2x2 boxes or 4 boxes? In the second case, can you allocate both "horizontally" and "vertically"?

You can only go one way, neither, entire locations is either they go all horizontally or all vertically, they can never be both. Just the size is different, it can occupy 1 slot, 2 slots or 3 slots, but once it is occupied, it can never be used for another box, unless the previous box removed.

Boxes can be placed in the grid horizontally for an entire grid, you can have another grid that can put boxes down vertically. Once a grid is set for this property, it will stay like that.

  Do you have some kind of classification regarding the boxes sizes? Do you have, as stated in comments, only three different sizes of boxes? In case you do not have it, I think that such classification is necessary in order to ensure compatibility between the boxes and the grids (via the “slots”), and it is also necessary to take into account the common dimensions: width, height and depth.

The boxes can allocate 1 slot, or 2 slots or 3 slots, it depends on the size.

  Are the grids fixed to 4x4 exclusively?

Actually 4X4 is a simplified example I gave. The actual size could be 31X8, imagine OOOOOOOO X31 and 8 lanes of those.

  Are you planning to include different types of grids (for instance, 5x3, 10x10, 6x4, etc.) in the future?

The size won't be changed very often. It's pretty set, if there is space increase, I can always add them.

  What is the meaning of the terms bays and lanes in your business context?

Bays and lanes are just terms to distinguish the X, and Y. On a 31 slots per lane, and 8 lanes in each grid. OOOOOOOO X 31 and 8 lanes of these next to each other vertically, bay means each of the "O", and lane is 8 lanes for each row of 31 "O".

This is actually an NP-complete problem

You won't find an optimal way to pack your bins, but you can find a good way

Here's Joe Celko's take on it:

https://www.simple-talk.com/sql/t-sql-programming/bin-packing-problems-the-sql/


all of the databases recovery models changed i logged into a client's server today(which had log shipping for all of its db's), and found that someone changed all the databases recovery models to simple. so now i need to reconfigure the log shipping.
my client says he didn't touch the server in the error log i can see it was changed.
can it be some kind of a bug? the server wasn't restarted im lost...
can the recovery model be changed by itself or by an application?
thanks

Recovery model is changed by a specific ALTER DATABASE statement, and as far as I've seen, there's never been a bug that would change it.

Some common sources of that statement include:

Backup software - some 3rd party backup software will put the database in simple recovery model, especially virtualization backup tools that just do snapshot backups rather than log backups.
Data warehouse load scripts - I've seen ETL processes that would flip the database into simple recovery model to eliminate the need for transaction log backups. (Not saying that's a good idea, just saying that I've seen it.)
Log space reduction scripts - sometimes folks try to shrink their transaction logs, and run into difficulty, so they flip the database into simple and then flip it back.


Estimation of creating index in SQL Server We create new index on our production database, with a few fields, our database size is around 300GB and it works for one hour and do not finished yet.

How can you estimate this operation?
If we will stop index creation; How time it will make rollback by your approximate estimation?
How can we check creation status in management studio?

On SQL Server Std Ed, 2005-2014, there is no way to get est time remaining (and Sporri's answer on this thread is simply incorrect, sorry).

This thread has an actual answer to this question:
SQL Server: How to track progress of CREATE INDEX command?


Problems with queries I have problems with queries in MySQL.

Problem 1:

Query:

SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA="mysql" and TABLE_NAME="servers"

MySQL says:

  #1054 - Unknown column 'mysql' in 'where clause'

Problem 2:

Query:

desc servers; // command no work

Can someone help me?

The double quote " is actually specifying a field or an identifier.  Use single quotes to specify your string filters:

SELECT *
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='mysql'
AND TABLE_NAME='servers'
;

Problem #2 could be you are USEing the wrong database.

For example, if your table servers is in the schema mysql and you are currently using INFORMATION_SCHEMA and you try to DESC SERVERS; you will receive an error.  To get around this you can fully qualify your table name (in this example DESC mysql.servers) or USE <schema_name_here> and then issue your DESC SERVERS; command.

Try explicitly spelling it out: DESCRIBE mysql.servers;  Also, make sure that you are using the correct case for your table names.  Servers, SERVERS, and servers are all different in MySQL.


Backup via mysqldump. Is this safe to do on production or will it crash the server? I want to run this command during the time in which the production server is running the application:

mysqldump -u sbp -p databasename_production > databasename_development_copy.sql

Is this safe to do while production is running or no?

In short, development would be a lot easier if I had a copy of production data. Is this safe to do?

I'm doing this in digitalocean fwiw.

Unless you add --single-transaction and you're using innodb tables this could block writes to the database for the period of the dump. So use --single-transaction (and innodb).

The only risk is if you are using a DO instance that is far too small and causes the mysql server or mysqldump (less likely) to run out of memory.

If you start gaining swap usage during the dump you can kill off the mysqldump process.

These few cases aside, its pretty safe.


Why you want to avoid Dynamic SQL in stored procedure? I have heard one said you do not want to use Dynamic SQL. Can you give some concrete example or real-life example? Personally, I code it a few times in my database. I think it is OK because it's flexibility. My guess is about SQL Injection or Performance. Anything else?

There is nothing wrong with using dynamic SQL if you must. In fact in some circumstances it is the only option that you have. It is more of a recommendation not to use it as yes it can lead to a SQL injection if your input is not sanitized, and yes using dynamic SQL in modules that get called often can be detrimental to it's performance.

I don't think there is a concrete example as per se but I would say this: Try to achieve what you are after using regular queries and statements first - only then once you have exhausted all other avenues do it dynamically. Just remember that executing a dynamic SQL string is done in a separate user session to the module that is calling it - so you may encounter permissions issues where you are not expecting any.

If your worried about performance; test it. If your worried about security; validate your input. There is no right or wrong - only that you use your best judgement based on the information and tools you have available to you at the time.


How do I get the total number of databases that are active? I am learning Oracle. I am trying to figure out (using the command prompt) the total number of databases that are up.

I tried:

select name from v$database

In singletenant (non-CDB, old) architecture: one instance - one database. In multitenant architecture (CDB): one instance - multiple databases.

So first of of you have to know the relationship between instance and database. Then you can identify how many instances do you have on your server (how, it depends on your operating system) and then you have to identify what architecture do you have (non-CDB, CDB).

Than you can make a list of all databases. Take a look at https://stackoverflow.com/questions/36957919/how-to-list-down-the-list-of-pdb-and-cdb-in-oracle-12c

Edit:
As mentioned by jmk, you can meet also Oracle RAC.


Help on writing server-side trace I need help on writing a server-side trace to check logins to try to determine if and when vendor accounts are used. The purpose is to remove sa access. These accounts sure have sa but I need to figure out if I could give them the least permission required to do the job.

The easiest way is to create the needed trace from the SQL Profiler tool and then save it as SQL script. Put this saved script inside an sql job, as a step, and schedule it for once daily run (at midnight). Don't forget to set your trace to save to files somewhere. You will have daily trace saved that you'll be able to select by queries or manually.

You can find some details in the answer of this question: Using SQL Profiler on a database that's in production, especially the article of automating server side tracing.


Trying to run query and getting error: Error 1064 (42000) I am trying to get a list of users from 2 different tables. I am using a sub query and the 2 separate queries run fine.

I am getting  this error:

  ERROR 1064 (42000): You have an error in your SQL syntax; check the
  manual that corresponds to your MySQL server version for the right
  syntax to use near 'IN (SELECT
  DISTINCT(flwusertest_users.ID),flwusertest_users.display_name,flwuser'
  at line 1

Here is the code I am using:

select
  flwusertest_users.ID,
  flwusertest_users.display_name,
  flwusertest_users.user_email
from flwusertest_users
INNER JOIN flwusertest_usermeta ON flwusertest_users.ID=flwusertest_usermeta.user_id
WHERE (flwusertest_usermeta.meta_key='_um_last_login'
         and flwusertest_usermeta.meta_value NOT BETWEEN '1483228800' and '1520952132')
AND NOT IN (
  SELECT DISTINCT(flwusertest_users.ID),
    flwusertest_users.display_name,
    flwusertest_users.user_email
  from flwusertest_users
  INNER JOIN flwusertest_em_bookings ON flwusertest_users.ID=flwusertest_em_bookings.person_id
  WHERE flwusertest_em_bookings.booking_status=1);

Any help is much appreciated.

select
       flwusertest_users.ID,
       flwusertest_users.display_name,
       flwusertest_users.user_email
from
      flwusertest_users
INNER JOIN
      flwusertest_usermeta
      ON flwusertest_users.ID = flwusertest_usermeta.user_id
WHERE (
      flwusertest_usermeta.meta_key = '_um_last_login'
      AND flwusertest_usermeta.meta_value NOT BETWEEN '1483228800' and '1520952132'
      )
AND <some_field> NOT IN (SELECT DISTINCT(flwusertest_users.ID)
                         FROM
                                flwusertest_users
                         INNER JOIN
                                flwusertest_em_bookings
                               ON flwusertest_users.ID=flwusertest_em_bookings.person_id
                        WHERE
                              flwusertest_em_bookings.booking_status=1);

There is more than one error in your sentence.

AND <some_field> NOT IN (SELECT DISTINCT(flwusertest_users.ID),
                                flwusertest_users.display_name,
                                flwusertest_users.user_email

You cannot return more than one field to use it with IN. You should return only

AND <some_field> NOT IN (SELECT DISTINCT(flwusertest_users.ID)
                         FROM
                                flwusertest_users
                         INNER JOIN
                                flwusertest_em_bookings
                               ON ...


For each record, count records dated earlier in PostgreSQL I have a table that contains a column year. I need, for each row, calculate the number of entries where the year is less than that in the row.

So that the output would look like:

  col1 | col2 | ... | year | number of entries prior to this year

I'm using PostgreSQL.

You can use the RANK() function for this:

SELECT
    col1, col2, ..., year,
    RANK() OVER (ORDER BY year) - 1
      AS number_of_entries_prior_to_this_year
FROM
    tableX ;

Tested in SQLFiddle.

There is also a more complicated approach:

SELECT
    col1, col2, year,
    COUNT(*) OVER (ORDER BY year) - COUNT(*) OVER (PARTITION BY year)
      AS number_of_entries_prior_to_this_year
FROM
    tableX ;


File permissions for database backups and certificate backups I have backed up a database and backed up the database master key to the very same folder. Everyone has full access to this folder, however, only the database backup inherits the permissions of the folder whereas the key backup does not.

I need to use admin privileges to view the security of the master key backup file.

Can anyone suggest why this may be?  Both were created by the same account via backup command in T-SQL.

Whenever you run BACKUP CERTIFICATE or BACKUP MASTER KEY, SQL Server will modify the Access Control List (ACL) on each resulting filesystem file it creates1 so that no-one other than the following Windows principals have access to the file:

OWNER_RIGHTS has Full Control.  OWNER_RIGHTS is a well-known security identifier, S-1-3-4 that represents the current owner of the object.  When an Access Control Entry that carries this SID is applied to an object, the system ignores the implicit READ_CONTROL and WRITE_DAC permissions for the object owner.
Members of the local "Administrators" group have Full Control
The Virtual Account or Managed Service Account used to run SQL Server has Full Control.  Note this is not the SQL Server Service Account you configure in SQL Server Configuration Manager.  For a default SQL Server instance, the account is typically NT SERVICE\MSSQLSERVER.  For a named instance, the account is named NT SERVICE\MSSQL$INSTANCE_NAME.
Inheritance is removed from the object's ACL to prevent access by any other principal.

This behavior is by design, and is an attempt to limit access to the resulting certificate and its private key.  Allowing "everyone" to access the certificate and private key files allows everyone to restore a backup of the TDE encrypted database onto the server of their choice, and be able to view all the encrypted data as if it was not encrypted.  What you're proposing to do seems like a very bad idea to me since it essentially circumvents the encryption.

See my blog post for more details, and example code.

1 - assuming the SQL Server Service has "full control" rights to the target folder in the backup statement.


Postgres multiple columns to json I am running postgresql 9.3.4. I have a table with 3 fields:

id   name  addr
---  ----  ----
1    n1    ad1
2    n2    ad2
...

I need to move the data to a new table with fields like:

id   data
---  ----
1    {'name': 'n1', 'addr': 'ad1'}
2    {'name': 'n2', 'addr': 'ad2'}
...

row_to_json is not the solution for me as SELECT t.id, row_to_json(t) as data FROM (select id, name, addr from myt) t adds id to the result as well. Is there a way to choose the fields I need (name & addr) in my data field?

There is a better option with json_build_object() in Postgres 9.4+:

SELECT id, json_build_object('name', name, 'addr', addr) AS data
FROM   myt;

But there is also a simpler and faster way with row_to_json() in Postgres 9.3:

SELECT id, row_to_json((SELECT d FROM (SELECT name, addr) d)) AS data
FROM   myt;

db<>fiddle here
Old SQL Fiddle on Postgres 9.6.

Related answers:

Select columns inside json_agg
Return as array of JSON objects in SQL (Postgres)
Return total number of rows and selected (aggregated) data


Is there any limit to the number of databases you can put on one SQL server? I'm setting up a SaaS system, where we're planning to give each customer their own database. The system is already set up so that we can easily scale out to additional servers if the load becomes too great; we're hoping to have thousands, or even tens of thousands of customers.
Questions

Is there any practical limitation on the number of micro-databases you can/should have on one SQL Server?
Can it affect performance of the server?
Is it better to have 10,000 databases of 100 MB each, or one database of 1 TB?

Additional information
When I say "micro-databases", I don't really mean "micro"; I just mean that we're aiming for thousands of customers, so each individual database would only be a thousandth or less of the total data storage. In reality, each database would be around the 100MB mark, depending on how much usage it gets.
The main reason to use 10,000 databases is for scalability. Fact is, V1 of the system has one database, and we have had some uncomfortable moments when the DB was straining under the load.
It was straining CPU, memory, I/O - all of the above. Even though we fixed those problems, they made us realize that at some point, even with the best indexing in the world, if we're as successful as we hope to be, we simply can't put all our data in one big honkin' database. So for V2 we're sharding, so we can split the load between multiple DB servers.
I've spent the last year developing this sharded solution. It's one license per server, but anyway that's taken care of since we're using VMs on Azure. Reason the question comes up now is because previously we were offering only to large institutions and setting up each one ourselves. Our next order of business is a self-service model where anyone with a browser can sign up and create their own database. Their databases will be much smaller and much more numerous than the large institutions.
We tried Azure SQL Database Elastic Pools. Performance was very disappointing, so we switched back to regular VMs.

I've worked on SQL Servers with 8 to 10 thousand databases on a single instance.  It's not pretty.

Restarting the server can take as long as an hour or more.  Think about the recovery process for 10,000 databases.

You cannot use SQL Server Management Studio to reliably locate a database in the Object Explorer.

Backups are a nightmare, since for backups to be worthwhile you need to have a workable disaster recovery solution in place.  Hopefully your team is great at scripting everything.

You start doing things like naming databases with numbers, like M01022, and T9945.  Trying to make sure you're working in the correct database, e.g. M001022 instead of M01022, can be maddening.

Allocating memory for that many databases can be excruciating; SQL Server ends up doing a lot of I/O, which can be a real drag on performance.  Consider a system that records carbon use details across 4 tables for 10,000 companies.  If you do that in one database, you only need 4 tables; if you do that in 10,000 databases, all of sudden you need 40,000 tables in memory.  The overhead of dealing with that number of tables in memory is substantial.  Any query you design that will be ran against those tables will require at least 10,000 plans in the plan cache if there are 10,000 databases in use.

The list above is just a small sampling of problems you'll need to plan for when operating at that kind of scale.

You'll probably run into things like the SQL Server Service taking a very long time to start up, which can cause Service Controller errors.  You can increase the service startup time yourself, create the following registry entry:

Subkey: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control
Name:   ServicesPipeTimeout
Type:   REG_DWORD
Data:   The number of milliseconds before timeout occurs during service startup

For example, to wait 600 seconds (10 minutes) before the service times out, type 600000.

Since writing my answer I've realized the question is talking about Azure.  Perhaps doing this on SQL Database is not so problematic; perhaps it is more problematic.  Personally, I'd probably design a system using a single database, perhaps sharded vertically across multiple servers, but certainly not one-database-per-customer.


How to find if difference between all row data for a specific columns are same I have a table with column with following data,

Column1
0.50
1.00
1.50
2.00
2.50
....
10000.00

Is there a way to validate the pattern that each data row has a difference of 0.50 ?

I tried googling but even struggling with keywords

I want to achieve is that, I want to make sure no value between 1 and 10000 is missing, e.g. I want to know if 100,00 is missing or 9999.50 is missing. I am not even sure if it's achievable?

validate the pattern that each data row has a difference of 0.50

For example, look at

WITH cte AS ( SELECT Column1 - COALESCE(LEAD(Column1) OVER (ORDER BY Column1), 0) delta
              FROM table )
SELECT delta, COUNT(delta)
FROM cte
GROUP BY delta


SHOW FULL PROCESSLIST for some host does not show `Client port` I am executing Show Full Processlist; and output does not show client port for some hosts in Host column

Documentation says that each and every connection has host:client_port format.

My question is for some of the host column it just shows localhost without client port

What could be the reason behind this?

The reason is that it's not connected via TCP/IP, so there isn't a port.

MySQL allows local clients to connect via a "socket file", which is located on the file system (/var/lib/mysql/mysql.sock, for example`).


python ibm_db driver and decimal values? It appears as if decimal values are treated as strings by ibm_db. Small example:

Python 2.7.14 (default, Mar 14 2018, 13:36:31)
[GCC 7.3.1 20180303 (Red Hat 7.3.1-5)] on linux2

]$ cat aa.py
#!/usr/bin/python

import sys
import ibm_db

conn = ibm_db.connect("dsn=sample","********","********")
s1 = ibm_db.prepare(conn, "values (cast(1 as int),cast(0.1 as decimal(2,1)))")
ibm_db.execute(s1)
d1 = ibm_db.fetch_both(s1)
print d1[0], type(d1[0]), d1[1], type(d1[1])

Output:

1 <type 'long'> 0.1 <type 'str'>

I'm dumping the dict as JSON to a file (for filebeat -> logstash), so I really need numeric types to stay numeric. I guess I can check the prepared statement for the type (the type is correct there) and massage the data before dumping, but that seems terribly wrong.

Any thoughts on how to get numeric data into the JSON object in the simplest possible way?

I found the following 3 year old mention:

https://github.com/ibmdb/python-ibmdb/issues/129

is this limitation still in play?

I'm not sure if it's supposed to work or not, in

https://github.com/ibmdb/python-ibmdb/issues/129

it says:

"This is the current limitation of ibm_db driver due to lack of C API of
python's Decimal object, but you will get decimal object through ibm_db_dbi
wrapper."

I used the following workaround:

import ibm_db
import ibm_db_dbi
[...]
ibm_db_conn = ibm_db.connect("DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s; PWD=%s" % (dbname, host, port, user, passwd),"","")
conn = ibm_db_dbi.Connection(ibm_db_conn)
c1 = conn.cursor()
c1.execute("values (cast(1 as int),cast(0.1 as decimal(2,1)))")
row = c1.fetchone()
if row == None:
    sys.exit(1)

# Manually create a dictionary
rowDict = dict(zip([c[0] for c in c1.description], row))
print rowDict
{'1': 1, '2': Decimal('0.1')}


shrink ibdata1 in Galera Cluster I'm having a Galera cluster (3 nodes, but currently using only one), MariaDB 10.0 Galera.

However, my ibdata1 is growing HUGE (although using inno_file_per_table=1).

Now i want to "shrink" it by using mysqldump (see here) but this is a really time consuming task for my load (around 12-15 hours, tried it with an equal server)
The database size we are talking about is around 150G (and ibdata1 grew to 163G over years due to unclever decisions). Now my main goal is to reduce downtime, say: Only the time it takes to create the dump.

My idea was to use mysqldump as SST method, but: How long is the cluster blocked? Only the time it takes to create the mysqldump or the time it takes to create + recover on the joiner?

Do you have another idea to do this with a small downtime?

I don't think you have many options here. If you want to maintain high availability I'd say shutdown on node and configure it as a replication slave of one of the cluster nodes. Rebuild it from a mysqldump of the cluster node. Once slave catches up, restart it as wsrep node 1 of a new cluster and then promote it as master. Shutdown the other two nodes of the old cluster and empty datadir, and let each join one at a time to the new cluster. Others may have a better suggestion though.


how to stop OracleRemExecServiceV2 from cmd? I have Oracle 12c running on Windows 7.

When I look at the Oracle's specific services (Control Panel\System and Security\Administrative Tools\Services), I have the following 6 services:

So far, everything is normal. I can even start or stop every service by double clicking on it, and then clicking the Start/Stop button.
However, for convenience, I want to create a .bat file with commands to open/close all the services. I run in command prompt commands such as net stop  [service], and they all work fine except for OracleRemExecServiceV2.

As you can see, I get System error 5 - access is denied. How can I solve this problem?

UPDATE:

i ran cmd as admin. Please see the below screenshot. In the Services window, OracleRemExecServiceV2 remains in the 'Starting' state for a long time, without changing to 'Started'.

One more thing: i double clicked the OracleRemExecServiceV2 and, in the General tab, i noticed that the Path was something like C:... (i cannot remember), while the database is installed on D, and all the other five Oracle services have the Path beginning with D:\app\OracleHomeUser\product\12.1.0\dbhome\BIN. So i changed it accordingly, to  point to RemoteExecService.exe in the BIN folder in the D directory. Is it ok what I did? otherwise, what would be the normal value for the path of this service?

Use a command line started with administrator privileges.


SQL server version differences My SQL server version is as below

but When i check in summary.txt at this path below

The product version is different which 13.2.5698 as below

Can anyone advise me why it's different in summary.txt files

It appears that, in the summary.txt log file, that second build number is used to indicate the service pack level.

13.0.5698.0 is SQL Server 2016 SP2 CU12.  Summary.txt replaces that bolded 0 with a 2 to indicate it's SP2.

I double checked this on a few of my instances of SQL Server, and it seems to hold true for SQL Server 2016 and 2014 consistently.  For instance, I have a SQL Server 2014 SP2 GDR instance installed locally, and Summary.txt shows the patch level as 12.2.5223.6, even though the real build number (per @@VERSION) is 12.0.5223.6.

The details of what is in Summary.txt aren't documented per se, so it's probably best to rely on SELECT @@VERSION rather than the contents of that file.