How to validate data before loading it to DB I have a situation where in I have million records in CSV file, what I need to do is to apply some business validation on certain columns of CSV and load data to DB only if particular record is valid.

I tried with per record strategy but that could turn into nightmare if the DB is hit millions of times.

I am using MongoDB to store metaData and Redshift for storing actual data.

Assuming that the RDBMS being used is Microsoft SQL Server (and version 2008 or newer), then Table-Valued Parameters (TVPs) are a great fit for this exact scenario. The idea is that you will read the file line by line in your .NET code but stream the data either all at once, or if that is too much for one transaction, broken up into batches, into SQL Server using a TVP. The TVP is essentially a table variable that is an input parameter to a stored procedure so this will be a set-based operation (as far as SQL Server is concerned), not row-by-row. Technically a stored procedure is not required as a TVP can be sent as a parameter to an ad hoc query, but working with a stored procedure is just a better approach anyway.

There are two main patterns for using a TVP while reading from a file (and each of them relies upon passing in a method that returns IEnumerable<SqlDataRecord> rather than a DataTable):

Execute "import" Stored Procedure, that in turn initiates the file open and reading, all rows are read (and can be validated at this point) and streamed into the Stored Procedure. In this approach, the Stored Procedure is executed only once, and all rows are sent in as a single set. This is the easier approach, but for larger data sets (i.e. millions of rows) it might not perform the best if the operation is to merge the data directly into a live table rather than simply loading into a staging table. The memory required for this approach is the size of 1 record.
Create a variable for int _BatchSize, open the file, and either:

Create a collection to hold the batch of records

Loop for _BatchSize or until no more lines to read from the file

Read a line
Validate
Store valid entry in the collection

Execute the Stored Procedure at the end of each loop, streaming in the collection.
The memory required for this approach is the size of 1 record * _BatchSize.
The benefit is that the transaction in the DB does not depend on any disk I/O latency or business logic latency.

Loop to execute the Stored Procedure until no more lines to read from the file

Execute the Stored Procedure

Loop for _BatchSize or until no more lines to read from the file

Read a line
Validate
Stream record into SQL Server

The memory required for this approach is the size of 1 record.
The downside is that the transaction in the DB depends on disk I/O latency and/or business logic latency, hence could be open longer, hence more potential for blocking.

I have a full example of pattern #1 over on StackOverflow in the following answer: How can I insert 10 million records in the shortest time possible?

For pattern #2.1 (a highly scalable approach), I have a partial example below (it is too late to finish so I will post the C# portion in the morning)...

Required database objects (using a contrived structure):

FIRST, you need a User-Defined Table Type.
Please note the use of UNIQUE, DEFAULT, and CHECK constraints to enforce data integrity before the records even hit SQL Server.
The UNIQUE Constraint is also how you create an index on a Table Variable :)

CREATE TYPE [ImportStructure] AS TABLE
(
    BatchRecordID INT IDENTITY(1, 1) NOT NULL,
    Name NVARCHAR(200) NOT NULL,
    SKU VARCHAR(50) NOT NULL UNIQUE,
    LaunchDate DATETIME NULL,
    Quantity INT NOT NULL DEFAULT (0),
    CHECK ([Quantity] >= 0)
);
GO

SECOND, use the UDTT as an input param to an import stored procedure.
Hence "Tabled-Valued Parameter" (TVP).

CREATE PROCEDURE dbo.ImportData (
   @CustomerID     INT,
   @ImportTable    dbo.ImportStructure READONLY
)
AS
SET NOCOUNT ON;

UPDATE prod
SET    prod.[Name] = imp.[Name],
       prod.[LaunchDate] = imp.[LaunchDate],
       prod.[Quantity] = imp.[Quantity]
FROM   [Inventory].[Products] prod
INNER JOIN @ImportTable imp
        ON imp.[SKU] = prod.[SKU]
WHERE  prod.CustomerID = @CustomerID;

INSERT INTO [Inventory].[Products] ([CustomerID], [SKU], [Name], [LaunchDate], [Quantity])
    SELECT  @CustomerID, [SKU], [Name], [LaunchDate], [Quantity]
    FROM    @ImportTable imp
    WHERE   NOT EXISTS (SELECT prod.[SKU]
                        FROM   [Inventory].[Products] prod
                        WHERE  prod.[SKU] = imp.[SKU]
                       );
GO

App Code:

First we will define the class that will be used to store the batch of records:

using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using Microsoft.SqlServer.Server;

private class ImportBatch
{
  string Name;
  string SKU;
  DateTime LaunchDate;
  int Quantity;
}

Next we define the method used to stream the data from the collection into SQL Server. Please note:

The SqlMetaData entry for BatchRecordID is defined even though it is an IDENTITY field. However, it is defined in such a way as to indicate that the value is server-generated.
The yield return returns the record but then control comes right back to the next line (which just goes back to the top of the loop).

private static IEnumerable<SqlDataRecord> SendImportBatch(List<ImportBatch> RecordsToSend)
{
   SqlMetaData[] _TvpSchema = new SqlMetaData[] {
      new SqlMetaData("BatchRecordID", SqlDbType.Int, true, false,
                      SortOrder.Unspecified, -1),
      new SqlMetaData("Name", SqlDbType.NVarChar, 200),
      new SqlMetaData("SKU", SqlDbType.VarChar, 50),
      new SqlMetaData("LaunchDate", SqlDbType.DateTime),
      new SqlMetaData("Quantity", SqlDbType.Int)
   };

   SqlDataRecord _DataRecord = new SqlDataRecord(_TvpSchema);

   // Stream the collection into SQL Server without first
   // copying it into a DataTable.
   foreach (ImportBatch _RecordToSend in RecordsToSend)
   {
      // we don't set field 0 as that is the IDENTITY field
      _DataRecord.SetString(1, _RecordToSend.Name);
      _DataRecord.SetString(2, _RecordToSend.SKU);
      _DataRecord.SetDateTime(3, _RecordToSend.LaunchDate);
      _DataRecord.SetInt32(4, _RecordToSend.Quantity);

      yield return _DataRecord;
   }
}

Finally, we define the overall import processing operation. It opens the Connection to SQL Server and the File, then it cycles through the file reading and validating the _BatchSize number of records for each cycle. The stored procedure parameters are only defined once as they do not change: the CustomerID value doesn't change, and the TVP parameter value is merely a reference to a method -- SendImportBatch -- that only gets invoked when the stored procedure is executed via ExecuteNonQuery. The input parameter to the method passed in as the TVP value is a reference type so it should always reflect the current value of that variable / object.

public static void ProcessImport(int CustomerID)
{
   int _BatchSize = GetBatchSize();
   string _ImportFilePath = GetImportFileForCustomer(CustomerID);

   List<ImportBatch> _CurrentBatch = new List<ImportBatch>();
   ImportBatch _CurrentRecord;

   SqlConnection _Connection = new SqlConnection("{connection string}");
   SqlCommand _Command = new SqlCommand("ImportData", _Connection);
   _Command.CommandType = CommandType.StoredProcedure;

   // Parameters do not require leading "@" when using CommandType.StoredProcedure
   SqlParameter _ParamCustomerID = new SqlParameter("CustomerID", SqlDbType.Int);
   _ParamCustomerID.Value = CustomerID;
   _Command.Parameters.Add(_ParamCustomerID);

   SqlParameter _ParamImportTbl = new SqlParameter("ImportTable", SqlDbType.Structured);
   // TypeName is not needed when using CommandType.StoredProcedure
   //_ParamImportTbl.TypeName = "dbo.ImportStructure";
   // Parameter value is method that returns streamed data (IEnumerable)
   _ParamImportTbl.Value = SendImportBatch(_CurrentBatch);
   _Command.Parameters.Add(_ParamImportTbl);

   StreamReader _FileReader = null;

   try
   {
      int _RecordCount;
      string[] _InputLine = new string[4];

      _Connection.Open();

      _FileReader = new StreamReader(_ImportFilePath);

       // process the file
       while (!_FileReader.EndOfStream)
       {
          _RecordCount = 1;

          // process a batch
          while (_RecordCount <= _BatchSize
                  && !_FileReader.EndOfStream)
          {
             _CurrentRecord = new ImportBatch();

             _InputLine = _FileReader.ReadLine().Split(new char[]{','});

             _CurrentRecord.Name = _InputLine[0];
             _CurrentRecord.SKU = _InputLine[1];
             _CurrentRecord.LaunchDate = DateTime.Parse(_InputLine[2]);
             _CurrentRecord.Quantity = Int32.Parse(_InputLine[3]);

             // Do validations, transformations, etc
             if (record is not valid)
             {
                _CurrentRecord = null;
                continue; // skip to next line in the file
             }

             _CurrentBatch.Add(_CurrentRecord);
             _RecordCount++; // only increment for valid records
          }

          _Command.ExecuteNonQuery(); // send batch to SQL Server

          _CurrentBatch.Clear();
       }
   }
   finally
   {
      _FileReader.Close();

      _Connection.Close();
   }

   return;
}

UPDATE:

Since it might not be apparently obvious to everyone reading the code, I should point out, especially with regards to comments on the Question suggesting to use a staging table or even staging database, that the method I outlined above is really just a variation of that approach. How so? Well, the table variable that is the TVP is the staging table. And in fact, as opposed to a typical staging table which is a permanent table, being temporary and not shared means that you do not need to worry about:

cleaning up the data after the process is done
separating multiple concurrent import processes

If the READONLY nature of the TVP prohibits some validation and/or transformation that is needed before being merged into the destination table, then the data in the TVP can be easily transferred to a local temporary table at the beginning of the Stored Procedure.


What is the safest way to install the sys schema on a production server? The reason I'm asking is because I need to install the sys schema on my database so that I can utilize performance_schema because I'm hoping that will help me figure out why mysql is crashing every 30 minutes exactly.

I am running MySQL 5.5.45 on Windows Server 2016.  I have four schemas on this server, of which the production schema is 4.1GB in size.  The other schemas are almost as big.

MySQL Workbench gives me an option to install it. Also, I understand that running mysql_upgrade will install it, which as far as I can tell simply executes mysqlcheck.

So it looks like I have three options for installing the sys schema.

Execute mysqlcheck (I think)
Execute mysql_upgrade
Click the install button on MySQL Workbench

What does MySQL Workbench do exactly?  Does it simply execute mysql_upgrade or mysqlcheck?  Or does it do the install a different way?

Will any or all of these options make my other schemas unusable while this install is executing (as in locking tables)?  What other implications are there to using any of these three options?  And most importantly, needing to install this on a production server, what is my best approach?

Option 4: Download it from github and install it manually.

Installation would work like this:

# mysql -uroot -ppassword < sys_56.sql

(I'm aware you're using MySQL 5.5. Please read on...)

When you examine what this does, you see that a separate schema is created and in this schema some procedures, functions, views and tables are created. Nothing too fancy. I can't imagine, that this would have any dramatic influence on the server.
Regarding compatibility with 5.5, I must admit that I'm not familiar with 5.5, but given that it's just some views and so on in a separate schema, I'd say that it either works or it doesn't. I personally wouldn't mind at all. Try it on a VM or docker container or whatever, if you like.


Optimising a Database Shrink (in terms of speed and reducing fragmentation) We have a large database that has had a lot of older data stripped out, leaving  primary datafile (all objects are currently in the primary filegroup) of about 80Gb containing a little less than 20Gb of data. We could do to release a large portion of that unused space back to the underlying filesystem.

Shuffling up to 20Gb of data around is going to be slow as we are using spinning-disk-and-moving-heads type drives, and I'm told that the shrink process can lead to significant structural fragmentation as it puts blocks where they'll fit with little logic regarding putting related blocks near each other.

Am I right in thinking that the following will be both faster and result in a less fragmented result:

Create a new filegroup on a separate array (we currently have three arrays, one containing the system drive and tempdb, one containing data files and one containing transaction logs, I propose the new filegroup goes on the system array)
Migrate all our tables and indexes over to the new filegroup by moving the clustered index over (or creating one on the new filegroup then dropping it, for tables with no clustered index currently) then dropping and recreating other indexes. This is where I expect to see a speed benefit, as the data will be moving between spindles rather than between places on the same disks to there will be less "head thrashing", and if I understand the process it should reduce exiting internal fragmentation too.
Shrink the primary data file
Move the file containing the new filegroup back to the array intended for data files. This would be done either by detaching the database, moving the files, them reattaching it, or by creating another new group and migrating all the data from the current new one over to it (leaving the data in this new filegroup would make this process easier in future - we can't just migrate to a new filegroup in step 2 this way as I'm told you can't do that with the primary group)

There are windows when I can perform the process in a time when it will not be an issue to take the database offline if needed, so a bit of downtime will not be issue.

Am I over complicating this? Is there a better way to shrink the primary filegroup while not introducing the chance of significant extra internal fragmentation? With plenty of RAM in the machine (16Gb currently, with the total data in this and other databases being about 45Gb including archive data that is rarely touched), is the fragmentation likely to matter much anyway? Should I just run a normal shrink operation and twiddle my thumbs while it does its business?!

A note first:

When you rebuild indexes, you need roughly free space of roughly 1.2 times the largest table. So a 15 GB table in that 20 GB used requires 18 GB free space. This sets a minimum database file size.

Personally, for OLTP databases, anything over 50%-60% used is a bit of red flag. So anything less then 35-40GB for for 20GB data would be worrying.

To decide whether you need downtime and filegroups and all the hassle, have you:

rebuilt all indexes (ALTER INDEX ALL .. REBUILD) to see where you are?
run DBCC SHRINKFILE with TRUNCATEONLY to free space only with no data movements?
checked file size after these steps?

One thing to look at would be NTFS fragmentation too after all this. YOu can stop SQL Server and defrag the volumes to get the MDFs and LDFs contiguous again. Of course, when they grow again you'll get fragmentation: which merits having generous file sizes to start with (50-60% used maximum).

Otherwise, your idea is sound to use different spindles to manage IO. Note the file size and NTFS stuff I mentioned above too.


MySQL: What is SQL mode and why we use it If in an application, if we execute sql_mode=mysql4.0 as a first query in my application but using MySQL 5.1 in my application then what will happen?

Will my system use MySQL 4.0 or MySQL 5.1 version features?  What is
SQL mode in a RDBMS?  Why we should use this?
On which basis, we should set its value?


sp_update_schedule says @schedule_id does not exist I wrote a script that reads job schedules from msdb.dbo.sysschedules and runs sp_update_schedule to change the active_start_time for a given subset of jobs. In each case, sp_update_schedule fails with the message "The specified @schedule_id('xxx') does not exist".  (MessageID 14262, Severity 16)
Is the schedule id in sysschedules not what it is looking for? What am I missing here?

The @schedule_id parameter on sp_update_schedule does refer to the schedule_id column on msdb.dbo.sysschedules
sp_update_schedule contains a call to sp_verify_schedule_identifiers to validate the schedule, and it's from this verification step that you are getting that error (message 14262).
EXECUTE @retval = msdb.dbo.sp_verify_schedule_identifiers
          @name_of_name_parameter = '@name',
          @name_of_id_parameter   = '@schedule_id',
          @schedule_name          = @name             OUTPUT,
          @schedule_id            = @schedule_id      OUTPUT,
          @owner_sid              = @cur_owner_sid    OUTPUT,
          @orig_server_id         = NULL

If you are passing both a schedule id and a schedule name, there could be a problem in the string you are passing for the name, as that is used for additional filtering within the verification procedure.
I'd suggest doing some testing with calls directly to sp_verify_schedule_identifiers so that you can simplify your troubleshooting, and make sure you're passing valid parameters to sp_update_schedule:
DECLARE
  @name_of_name_parameter   VARCHAR(60) ,--= '@schedule_name',
  @name_of_id_parameter     VARCHAR(60) = '@schedule_id',
  @schedule_name            sysname     ,--= 'syspolicy_purge_history_schedule',
  @schedule_id              INT         = 8,
  @owner_sid                VARBINARY(85),
  @orig_server_id           INT,
  @job_id_filter            UNIQUEIDENTIFIER    = NULL,
  @RetCode                  INT;

EXEC @RetCode = sp_verify_schedule_identifiers
  @name_of_name_parameter    = @name_of_name_parameter,
  @name_of_id_parameter      = @name_of_id_parameter,
  @schedule_name             = @schedule_name,
  @schedule_id               = @schedule_id,
  @owner_sid                 = @owner_sid,
  @orig_server_id            = @orig_server_id,
  @job_id_filter             = @job_id_filter;

SELECT
   RetCode              = @RetCode,
   NameOfNameParam      = @name_of_name_parameter,
   NameOfIdParam        = @name_of_id_parameter,
   ScheduleName         = @schedule_name,
   ScheduleID           = @schedule_id,
   OwnerSid             = @owner_sid,
   OrigServerID         = @orig_server_id,
   JobIDFilter          = @job_id_filter;

Permissions may also be getting in your way. You would need, at minimum, GRANT EXEC ON sp_update_schedule TO <your login> in order to make the call you are trying to make. To do the troubleshooting step I list above, you would also need to be granted EXEC on sp_verify_schedule_identifiers--though you only need this if you execute it directly. Normally, ownership chaining would allow you to only need the top-level permission & you would implicitly have the permission to do what that procedure does.  You could also be added to the SQLAgentUserRole security role in msdb, which includes other permissions as well--if you're rescheduling jobs, you may also need/want some of those other permissions in order to accomplish your work, assuming that this is an acceptable level of access.


Howto use typeperf to store performance monitor values in a custom schema? I have setup a performance monitoring of my SQL server system using typeperf.exe to store the values directly to SQL Server.

I went through the following article, that explains howto use typperf.exe with correct parameters and a counter configuration file to store the results into a SQL server database, after a connection to this SQL server has been configured in a local System-DSN:

Collecting Performance Data into a SQL Server Table
20 January 2011 by Feodor Georgiev

I use a command as article explains like to this one:

TYPEPERF -f SQL -s SqlServer1 -cf “C:\Counters.txt” -si 15 -o SQL:SQLServerDSN!log1 -sc 4

After that I have the monitored values stored in my SQL server (as configured in the SystemDSN) and can easily query those (without having to bother with the different counter types and calculation of correct average values because of accumulated values)... e.g. like this:

SELECT  MachineName ,
    CounterName ,
    InstanceName ,
    CounterValue ,
    CounterDateTime ,
    DisplayString
FROM    dbo.CounterDetails cdt
    INNER JOIN dbo.CounterData cd ON cdt.CounterID = cd.CounterID
    INNER JOIN DisplayToID d ON d.GUID = cd.GUID

It works fine - except one detail: the tables are created in the dbo schema. I would like to have them in another schema.
Anyone knows if this is possible and how?

Edit:
I tried to use Windows Authentication because it always fails using SQL Auth and I found this article explaining that SQL Auth is kind of broken in DSN: http://forums.hexus.net/windows/178393-windows-server-2008-perfmon-logging-database.html

So changed to Windows Auth, I figured out the Account SQLAgent is running on, setup a new Job that runs the Batch and to be sure ECHOed out the user name in a log. It's the user the SQL Agent is running with.

This user has only the server role PUBLIC and is mapped to msdb. So I mapped it to the destination DB (the DB that I have defined in the SystemDSN where I want the PerfCtr tables in) with default schema I want the tables to be in and granted him permission to ALTER the schema and assigned datawriter role for the db.
After the JobAgent ran the job, the PerfMon tables successfully have been created -> but now they are in dbo schema ?
- How can this user create objects in dbo, that from my perspective it does not have permission to ?
- Why is the default database setting for this user not used by the connection defined in the system DSN?

It seems like the schema used will be the default schema of the database user associated with the login you provide to the DSN.

How you set this up in detail depends on factors like whether SQL Authentication is allowed to this SQL Server instance. You can set the default schema with ALTER USER.

Regarding the edit (almost becoming a new question now), from Database-Level Roles:

  Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.

You could try granting more granular permissions (though having alter on the schema may already be sufficient). It might also be that the implementation hard-codes use of the dbo schema.

Another thing to check: make sure the login is not a member of the sysadmin fixed server role. The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role. All members of the sysadmin fixed server role have a default schema of dbo. Grant CONTROL SERVER to the login instead; this gives exactly the same rights, without the schema side-effect.


Best way to put date range in a table in mySQL I'm designing a system which is basically a booking engine. it uses separate table to store room data and separate table to store it's availability information (available date(s) ). I'm stuck in designing at the availability table.

This is the structure in my rooms table,

room_id
room_type
room_name etc...

This is my current availability table structure

room_id (int)
week_id (int)
date (datetime)
availability (boolean)

is there any better way to achieve this?please help. even the tiniest advice will be greatly appreciated

Note: not a MySQL guy, I use MS SQL. I would try to focus less on if a room is available at the start, and try to focus on marking the dates the room is reserved. If you use a table that stores reservations;

CREATE TABLE [Reservations] (
ReservationID INT NOT NULL,
RoomID INT NOT NULL,
StartDate SmallDate,      --first day of reservation
Duration INT)             --number of days being reserved

Then when you want to see what rooms are available for a day, you can search for what rooms are being used that day (using the startdate and duration columns) and get the inverse. This also frees you up from trying to allocate space for each possible reservation slot, but just add a record into the reservations table when they are reserved.


Compound shard key with _id field I have documents like:

{_id: "someid1", "bar": "somevaluebar1"}
{_id: "someid2", "foo": "somevaluefoo2", "bar": "somevaluebar2"}
{_id: "someid3", "foo": "somevaluefoo3", "zoo": "somevaluezoo3"}
{_id: "someid4", "zoo": "somevaluezoo4"}

If we query documents by "foo" the most and "bar" the second, does it make sense to create a compound shard key like { "foo" : 1, "bar" : 1, "_id" : 1 } because "foo" and "bar" might be missing too?

When I tried to run this command

sh.shardCollection("<your-db>", {{ "foo" : 1, "bar" : 1, "_id" : 1 }:"hashed"})

it gave me a syntax error.

You'll need to rethink your shard key approach.

As at MongoDB 3.2:

All fields in a compound shard key must be present in all documents and will be immutable (i.e. the shard key for an existing document cannot be changed).
A hashed shard key is based on a single field, and does not support range queries.

It generally makes sense to have a shard key that supports your common queries so they can be targeted at a subset of shards with relevant data, but this doesn't appear to be possible in your case as both foo and bar are optional fields.

If your _id field provides good cardinality (i.e. large number of values) but is monotonically increasing (eg. default ObjectIDs) you could consider a hashed shard index on the _id field for good write distribution. The hashed index wouldn't support your common read queries (unless by specific _id values) so you would need a secondary index for your queries on foo and bar (i.e. {foo:1, bar:1}). The recommended secondary index(es) and order will depend on your common queries and sort order.

For further background information I suggest reviewing:

Considerations for Selecting Shard Keys
Use Indexes to Sort Query Results
Optimizing MongoDB Compound Indexes


Which schools are known for graduation level database research? Which schools across the world are best known for research being carried out in database field in recent times?

MIT is the home of Michael Stonebraker, probably the leading RDBMS researcher working in academia today. A lot of the research is done in private labs tho', e.g. CJ Date and Ted Codd did much of their early work at IBM.

I am missing a huge amount here as I am only really interested in RDBMS research; there's probably a lot more going on in OODBMS research.


With B-Tree indexes, more importantly is the cardinality of the data you're indexing. The more unique the data is in the index, generally the better candidate it is for a B-Tree index because it'll result in a much more full tree with many branches and leaves. A very non-unique field, like a boolean based field, will always only have two distinct values, and therefore is very repetitive resulting in two short but wide branches and doesn't filter down the data as much as a more unique field.
Since you're planning to index a BIGINT field with randomized values, it should lead to very unique data and the B-Tree index should perform very well, regardless of the monotonicness of those values. Additionally, if you're only inserting 30 values a month, your table must be tiny so you should have no worries at all regarding using a B-Tree index. B-Tree indexes handle billions of rows just fine.
To answer your actual question, yes monotonic values in a B-Tree index reduce fragmentation initially and therefor are more efficient, but again this is probably a minimal concern based on the fact that your values will be very unique and your overall amount of data being tiny.


RETURN TABLE is converted to RETURN SETOF int in Postgres function In pgAdminIII I create a function that returns multiple rows in a single column named identifier using RETURN TABLE:

CREATE OR REPLACE FUNCTION test(
    IN parm1 date,
    IN parm2 interval)
  RETURNS TABLE(identifier INT)
AS
$BODY$
BEGIN
    RETURN QUERY
    SELECT 1;

END;
$BODY$
  LANGUAGE plpgsql;

After the function is created, pgAdminIII shows the function as having the following definition:

CREATE OR REPLACE FUNCTION test(IN parm1 date, IN parm2 interval)
  RETURNS SETOF integer AS
$BODY$
BEGIN
    RETURN QUERY
    SELECT 1;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

When executing the function it follows the logic of the RETURNS TABLE. But, when editing the function it will pickup the RETURNS SETOF. This provides different functionality than the function I specified (e.g. the name of the returned column no longer exists).

So, how can I force pgAdminIII to keep the returned column name of identifier? I don't particularly care whether I define it as a TABLE or SETOF.

What you see is obviously a display bug in the current pgAdmin 1.20. I could reproduce it.

You must be aware that the code you see is re-engineered from system table entries.

If you look up your function in the system catalogs directly, you'll find that your the return type has been registered properly (at least it works for me in pg 9.4):

SELECT pg_get_functiondef(oid)
FROM   pg_proc
WHERE  proname = 'test';

More useful functions like pg_get_functiondef() in the manual.

You might want to ask for this on the pgAdmin support list:

http://www.postgresql.org/list/pgadmin-support/

Mail to pgadmin-support@postgresql.org

  How can I force the returned column to have a name of identifier?

Your approach with RETURNS TABLE(identifier int) is the right way. RETURNS SETOF does not take a parameter name, unless you combine it with an OUT parameter. Like so:

CREATE OR REPLACE FUNCTION test(parm1 date
                              , parm2 interval
                              , OUT identifier int)
  RETURNS SETOF integer AS ...


How to turn VARBINARY within PeopleSoft into a string? In PeopleSoft there are string messages that are stored in VARBINARY under the hood.
I would like to convert that data into a string so that I can view it in a data query tool.
Is this possible using SQL and perhaps some built-in SQL Server functions?  I would rather not create a special Java program for just this one field.

Ok, I found out how to translate the data.  It is a compressed string stored as VarBinary.  I needed to use the System.IO.Compression.DeflateStream classDeflate
It would be nice to do this all in SQL, but this at least removes the coding roadblock.

... db connection code...
    var query = @"SELECT TOP 10  * FROM PSAPMSGPUBDATA PH WHERE 1=1   AND IBTRANSACTIONID LIKE 'd8d8%'";

    var cmd = new System.Data.SqlClient.SqlCommand(query,conn);

    var dr = cmd.ExecuteReader();

    while (dr.Read())
    {
        dr[5].Dump();
        var stream = dr.GetStream(7);
        var foo = new byte[9000];
        stream.ReadByte();
        stream.ReadByte();
        var output = new System.IO.Compression.DeflateStream(stream, System.IO.Compression.CompressionMode.Decompress);
        var outStream = new MemoryStream();
        var len = (int) stream.Length;
        output.Read(foo,0,9000);
        output.Close();
        var message = System.Text.Encoding.Default.GetString(foo);
        message.Length.Dump();
        message.Dump();
    }


Is it possible to get server roles and database roles in a same query/script? Is it possible to get all database roles and server roles in the same query/script? if so then how can it be achieved?

Initially, I wrote two separate queries, one for server roles and another one for database roles I joined the two queries with 'Union All' but the issue is it doesn't show any database Name which I need.

So I approached the problem from a new angle as the end goal is to get all the databases in the SQL server instance with the roles(e.g db_owner). This will be stored in a temp table from where I can export it to excel. So I wrote a script after doing some reading and online searching. In the following query, I am getting the database roles along with the respective database so that issue is resolved but I want server roles as well and I don't know how to get those using this query. If anyone can help me to modify the script in a way that it provides both the server roles and the database roles along with server and database name.

DECLARE @DatabaseName SYSNAME,
        @sql VARCHAR(1000)
DECLARE @ResultTable TABLE(DatabaseName VARCHAR(100), ServerName VARCHAR(100), Name VARCHAR(100), TypeDesc Varchar(100), DatabaseRole VARCHAR(100) )
DECLARE DatabaseCursor CURSOR
     FOR SELECT Name FROM sys.databases
OPEN databaseCursor
FETCH NEXT FROM databaseCursor INTO @DatabaseName
WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @sql ='SELECT ''' + @DatabaseName + ''' AS [Database],
                      ''' +@@SERVERNAME +  '''AS [Server Name],
                      logins.name Name,
                      logins.type_desc TypeDesc,
                      roles.name RoleName
               FROM '+@databaseName+'.sys.database_role_members rm
               INNER JOIN '+@databaseName +'.sys.database_principals roles ON rm.role_principal_id = roles.principal_id
               INNER JOIN '+@databaseName+'.sys.database_principals logins ON rm.member_principal_id = logins.principal_id'
    INSERT INTO @ResultTable EXEC(@sql)
    FETCH NEXT FROM databaseCursor INTO @databaseName
END
CLOSE databaseCursor
DEALLOCATE databaseCursor
SELECT * FROM @ResultTable;

Add an INSERT...SELECT query to get the server-level role memberships. The example below also uses QUOTENAME to properly handle the concatenated literals and identifiers.

DECLARE @DatabaseName SYSNAME,
        @sql VARCHAR(1000);
DECLARE @ResultTable TABLE(DatabaseName VARCHAR(100), ServerName VARCHAR(100), Name VARCHAR(100), TypeDesc Varchar(100), DatabaseRole VARCHAR(100) );
DECLARE DatabaseCursor CURSOR
     FOR SELECT Name FROM sys.databases;
OPEN databaseCursor;
FETCH NEXT FROM databaseCursor INTO @DatabaseName;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @sql ='SELECT ' + QUOTENAME(@DatabaseName, '''') + ' AS [Database],
                      ' + QUOTENAME(@@SERVERNAME,'''') +  'AS [Server Name],
                      logins.name Name,
                      logins.type_desc TypeDesc,
                      roles.name RoleName
               FROM '+QUOTENAME(@databaseName) +'.sys.database_role_members rm
               INNER JOIN '+QUOTENAME(@databaseName) +'.sys.database_principals roles ON rm.role_principal_id = roles.principal_id
               INNER JOIN '+QUOTENAME(@databaseName) +'.sys.database_principals logins ON rm.member_principal_id = logins.principal_id';
    INSERT INTO @ResultTable EXEC(@sql);
    FETCH NEXT FROM databaseCursor INTO @databaseName;
END;
CLOSE databaseCursor;
DEALLOCATE databaseCursor;

--get server roles
INSERT INTO @ResultTable
SELECT
      NULL
    , @@SERVERNAME
    , logins.name
    , logins.type_desc
    , roles.name AS RoleName
FROM master.sys.server_role_members AS rm
JOIN master.sys.server_principals AS roles ON rm.role_principal_id = roles.principal_id AND type_desc = N'SERVER_ROLE'
JOIN master.sys.server_principals AS logins ON rm.member_principal_id = logins.principal_id;

SELECT * FROM @ResultTable;


Remotely Connect to MySql from MS Access Does anybody know if it is currently possible to remotely connect to a MySql server from MS Access? What does one need to do to go about doing this?

Yes, by using linked tables. Here's the MySQL documentation https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-examples-tools-with-access-linked-tables.html

Add a User DSN from the ODBC Data Source Administrator, testing the link to your MySQL database to ensure you have the server, user name and password correct.

In Access on the External Data tab select ODBC Database. Choose the Link... option. Select Machine Data Source and the name of the DSN you set up. The tables available will appear. Choose the one(s) you want and click OK.


How do I efficiently query for records that share a year? I've got data that looks like
data
uid  id  date        value
1    a   1780-01-01  10
2    a   1780-01-02  11
3    a   1780-01-03  12
4    b   1780-01-01  10
...
999  a   1980-01-01  10

I'd like to know which ids have records between 1880 and 1900.  I currently
generate this big query (which I believe works):
SELECT year1880.id FROM
(SELECT DISTINCT id FROM data WHERE strftime("%Y", date) = "1880") as year1880

  INNER JOIN
  (SELECT DISTINCT id FROM data WHERE strftime("%Y", date) = "1881") as year1881
  ON year1880.id = year1881.id

  INNER JOIN
  (SELECT DISTINCT id FROM data WHERE strftime("%Y", date) = "1882") as year1882
  ON year1880.id = year1882.id

  ...

  INNER JOIN
  (SELECT DISTINCT id FROM data WHERE strftime("%Y", date) = "1900") as year1900
  ON year1880.id = year1900.id
  ;

Is this the most efficient way to make this query?  Could it be done in a closed
form so I don't need to generate a giant query with python?
The database is sqlite.

This should return all the ids that have at least one record for each year in your date range:
SELECT
  id
FROM
  <your table>
WHERE
  CAST(strftime("%Y", date) as decimal) BETWEEN <min year> AND <max year>
GROUP BY
  id
HAVING
  COUNT(DISTINCT strftime("%Y", date)) = <max year> - <min year> + 1
;


Reindexing Clustered Primary Key We need to reindex a Clustered Primary Key on quite a large table. Here is the definition of the table:

Data Size: 22GB
Index Size: 34GB
Total Non-Clustered Indexes: 3
Row Count: 54 Million
Data File Free Space: Minimal
Drive Free Space: 3GB

The indexes are all over 90% fragmented.

I know that reindexing the PK will lock the table, so I was wondering what best practice for this operation is? Since there is insufficient free space in the data file or disk drive to rebuild the index, I wrote a script which would:

Set the DB Recovery mode to Simple (to avoid growing the Log file too large)
Drop the non-clustered indexes
Rebuild the PK
Recreate the non-clustered indexes
Set the DB Recovery mode back to Full

I did a dummy run on a backup of the database on another server, which took 32 minutes in total, which is acceptable for a maintenance window for us.

The benefits I can see of dropping the non-clustered indexes are:

The DB file does not need to autogrow to rebuild the PK as space would have been created when the indexes are dropped
We will not need to shrink the DB file again (and cause additional fragmentation) following the maintenance
The non-clustered indexes will also be defragmented when they are rebuilt.

This script suits our needs for now, but what is considered best practice usually for a table of this size assuming we had infinite available space?

I know that reindexing the PK will lock the table, so I was wondering what best practice for this operation is?

Well having some DB file size/growth and free disk space monitoring would be good so this situation never happens. As you are in this situation I think the plan you have outlined is your only choice until you get more disk space and you really need more disk space.

  This script suits our needs for now, but what is considered best
  practice usually for a table of this size assuming we had infinite
  available space?

Regular index maintenance would be nice. Try looking at Ola Hallengren's maintenance scripts here to solve that.  These scripts provide an almost fully automated methodology for maintenance of your SQL Server databases.


How can I automate sharding in the presence of continuous reads I am using a pymongo in a sharded mongo environment.

I have some fairly write-intensive systems, so I split the data up by time, with one database per hour, so the heavy-lifting of index-updates can happen in-memory, and to give us more flexibility to make performance-related tradeoffs.

I have a number of processes writing to the latest hour's database, and another handful of processes reading from the last n hours of data. To keep things simple, I would like these processes to remain independent. (I do not want to have to synchronize them).

The hourly databases are sharded on creation, so that reads and writes are distributed across the cluster. Unfortunately, the (independent) read processes fall over when they try to read from the latest db whilst the sharding operation is still incomplete.

As I see it, I have a small handful of options available to me:

I can make the read process tolerant to errors, so that when it encounters a failure due to in-progress sharding operations, it can carry on regardless. (This seems the neatest and most pythonic option to me, but runs the risk that the throwing/catching process might lead to large delays).
I can make the read process ignore the most recent database until it is at least 10 minutes old. This will give the sharding operation some time to complete. I do not really like this option because of the involvement of a seemingly arbitrary time interval, making it feel less deterministic than I would like. (Perhaps more acceptable, I could pre-create a few hours worth of databases, and ensure that they are sharded & fully configured well in advance)
I can make the write process create each hourly database by calling the copyDatabase command on a pre-sharded empty database. This approach presumes that the sharding state will be copied with the rest of the database, and that the in-progress copy operation will not be vulnerable to the same (or similar) errors as the in-progress sharding operation was.
I can attempt to synchronise the reads and the writes by maintaining a record of the most-recently-created database, populated when the database is fully ready to be written to. I do not like this approach because it couples the read & write processes together, and introduces a level of complexity that I think I can avoid.

Are there any options that I have missed?

Right now, option 2b seems the most attractive.

Some thoughts on each of the options:

I would say that writing this code in any case is probably a good idea - if something does go wrong on the performance side of things you already have the code written to deal with it in some way, rather than just sending exceptions or timeout messages back to the users.
This sounds like pre-splitting and pre-allocation, which again, is probably a good idea to do anyway - why wait until just before use to create a database?  In fact, this would mirror how mongodb does data file allocation, it always has one empty data file waiting to be filled before it is needed.  The pre-allocation it does is for adding data to a DB though, it can't psychically figure out the need for a new DB, hence that initial allocation may be slowing you down.  Also, make sure you are on ext4/XFS and not using an old kernel to ensure that the allocation is as quick as possible.
I believe that doing pre-allocation is a better option here - I have not heard of anyone using copyDatabase in this manner, so it would require a lot of testing.  Pre-allocating databases in advance though is quite common.
This does sound a bit painful, I think there are better options described above

Overall, if you know you are going to need a new database every hour, create multiple databases in advance of their need.  I know this would mean extra disk usage, but it should be quite predictable (look into the --smallfiles option if your data set is quite small).


Restoring MS SQL TDE database question I'm new to SQL Server TDE and have a question.

I have two VM's (VM1 = Source and VM2 = Target).  On the source machine, I executed all the required steps to enable my Test database as well as backing up the certificate and private key.  No issues here.

On the target machine, i executed the following t-sql commands

CREATE MASTER KEY ENCRYPTION

CREATE CERTIFICATE
FROM FILE
WITH PRIVATE KEY(
FILE
DECRYPTION BY PASSWORD
);

without any issue and successfully restored my Test TDE database.

On the source machine, I had to run this command

CREATE DATABASE ENCRYPTION KEY

but I didn't have to when I did the restore on the target machine.  Here's my question:  Is my Test database on the target machine TDE enabled?

When you say:

  On the source machine, I executed all the required steps to enable my
  Test database as well as backing up the certificate and private key.
  No issues here.

We don't really know the steps you took.

Here is a query you can run to see if the database is encrypted on the target server.  Look at the EncryptionState column.

SELECT
    db_name(database_id) as DatabaseName,
    Case
        when encryption_state=0 then 'No Database Encryption'
        when encryption_state=1 then 'Unencrypted'
        when encryption_state=2 then 'Encryption In Progress'
        when encryption_state=3 then 'Encrypted'
        when encryption_state=4 then 'Key Change In Progress'
        when encryption_state=5 then 'Decryption In Progress'
        when encryption_state=6 then 'Protection Changes In Progress'
    end as EncryptionState,
    percent_complete
FROM sys.dm_database_encryption_keys


Is it possible to use txid_current value for sync I need to expose my data through Rest API for a mobile offline experience.
The idea is basically that a mobile client downloads all data to his device. after a week he wants to download only the changes into his device. each mobile client synced to a different point of time.
My problem is how to create a deterministic cursor for the client which they will send it on their next sync request and I'll be able to figure out the changes since their last sync.
Assumptions:

Clients tolerate data duplication.
My database is insert-update only (never delete).

Possible solutions:

Logical Decoding
It's relatively (to other solutions we have in mind) hard to create a plugin and it doesn't fit our needs because our permission model, each client needs to get only rows he is permitted to see.
txid_current + cmin value.
Save the transaction id on each row to create incremental value, cmin is needed to create a more precise cursor (many rows may have the same txid, but only one row has a specific txid + cmin pair).
Use xmin only, and re-sync (sync from scratch) user's upon wraparound. (according to the epoch counter).
Add SERIAL column/sequence, and bump it up manually or with triggers, it won't make us have to serialize our transaction if we are using Overlapping Sync as describe below.
Serialize all transactions that act on the most top resource in my model (database), which is least preferred because of the performance and complexity it introduces to the application code.

Currently, I really want to use option 2, because it seems like the simplest solution.
How I'm going to handle Consistent Ordering:
The problem:
If the client syncs up to the latest visible (committed) change there may be active transactions that include earlier changes that are not yet visible.

The solution:
Overlapping Sync the clients, Sync up to the latest visible change but keep track of what the latest safe change was. On the next sync, ask for changes since the lastest safe change rather than the latest visible change.

Is option 2 valid? (under the assumption above and the Overlapping Sync solution), if not, what is the next simplest solution? there is another solution which I didn't think about?

I would tag each inserted row with the current transaction ID (txid_current() or, from v13 on, pg_current_xact_id()).
The “latest safe change” is the right before the xmin of the “current snapshot”, which can be found with txid_snapshot_xmin(txid_current_snapshot()) (or pg_snapshot_xmin(pg_current_snapshot()) from v13 on). That xmin is the transaction ID of the oldest still active concurrent transaction, that is, everything older than that is done and visible.
This should make it fairly easy to build a replication system.


SQL Server 2014 View any Live execution plan in Activity Monitor I have a SQL Server 2014 Build(12.0.5205.0) running here.

Would It be possible to view any Live execution plan (from queries being executed by other users) in Activity Monitor ?

My problem is:

When I open the Activity Monitor - > Active Expensive Queries, I can see all expensive queries, but clicking right button the "Show Live execution Plan" is only enabled for queries that my user is running, for the any other user this menu is disabled:

An example:

I heard about Trace flags, enable a global trace flag using DBCC traceon(7412,-1).

So, my questions is:

Enabling This flag above (7412), considering the build version of my Sql Server, will it work? Or only in SQL Server 2016?
If it works, I don't have the sa rights. What kind of permissions do I have to ask for?

You have to enable trace flag 7412

DBCC TRACEON (7412, -1)

According to blog post, you have to enable xEvent: query_post_execution_showplan

Warning, despite that this feature works starting SQL Server 2014 SP2, it can introduce significant overhead - up to 75%.:

Starting SQL Server 2016 SP1, this was changed and the overhead is only 1-2%
(in case of trace flag 7412) or 15% if query_post_execution_showplan xEvent enabled

In 2019 it is enabled by default

Details:

https://blogs.msdn.microsoft.com/sql_server_team/query-progress-anytime-anywhere/

Permissions:

  Requires the database level SHOWPLAN permission to populate the Live
  Query Statistics results page, the server level VIEW SERVER STATE
  permission to see the live statistics, and requires any permissions
  necessary to execute the query.


Selecting distinct records having multiple properties For following table data:

ID     | Property
=================
1      | Sweet
2      | Sweet
2      | Sour
3      | Hot

How can I get the ID's having Property Sweet AND Sour (in this example, 2)

Thanks.

The easiest way would be group by and having

select
ID from table_name
where Property in('Sweet','Sour')
group by ID
having count(*)=2


How does Log shipping knows to keep track I have set up log shipping between 2 SQL Servers (SQL Server 2008 and SQL Server 2012),

At first i took a full backup of the first one and restored it on the other one and than i set up the log shipping,

The databases first backup-restore took a few hours and i wish to know:

how does the log shipping process knows from when to start the logs
it is shipping? does it start from when the last backup-restore process happened or from the moment the log shipping itself is set ?
Can i be sure that no information was lost in these few hours ?

Thanks

You can use log shipping to send transaction logs from one database (the primary database) to another (the secondary database) on a constant basis. Continually backing up the transaction logs from a primary database and then copying and restoring them to a secondary database keeps the secondary database nearly synchronized with the primary database.

Here is the answer of your questions
- If configured correctly then log shipping software start from the point the last backup-restore process happened
- The Answer of your second question is really depends upon how you configure it.

As we are using red gate software and we are taking backup after every 15 minutes and in this way we can lost maximum of 15 minutes of data, as the secondary database nearly synchronized with the primary database.

hopw it will help.

You can read more Monitor Log Shipping


GROUP BY, but use only one row per user I've spent several hours to make a "simple" SELECT with GROUP BY in Postgres without success. The GROUP BY clause is giving me problems.
I have the table cities with columns user_id and city.
user_id can be repeated, so the table can have this information:
"Bill", "New York"
"Bill", "Chicago"
"Adam", "New York"
"Mike", "Los Angeles"
...

If I would like to have the count of cities it could be make this way:
SELECT cities.city, COUNT(*) FROM cities GROUP BY cities.city

But if I want to have this count and take only one city per user (it doesn't matter if "Bill" takes "New York" or "Chicago") how could I group by cities.user_id?

Your query does not exactly do a count of cities, but rather the count of users per listed city. To get that after de-duplicating users:
SELECT city, count(*) AS users
FROM  (
   SELECT DISTINCT ON (user_id) city
   FROM   cities
   ) sub
GROUP  BY city;

This picks one row per user_id arbitrarily like you specified. So we need no ORDER BY in the inner SELECT.
We need nothing but the city from the inner query for the bare count.
Detailed explanation for DISTINCT ON:

Select first row in each GROUP BY group?

Not deterministic for arbitrary pick
The above is typically fastest for few rows per user_id, while implementing stated requirements.
But the result is not deterministic while we pick rows arbitrarily. Can return different numbers for repeated executions as Postgres is free to pick any row for one user. (The total sum over all cities is stable, though, being the count of users.)
The result is normally stable, but any change to the table can trigger a different result. Like autovacuum doing its job in the background, or any unrelated write operation on the table.
To get deterministic results you need to add a deterministic ORDER BY to the inner query, so that DISTINCT ON always picks the same row. Like:
SELECT city, count(*) AS users
FROM  (
   SELECT DISTINCT ON (user_id) city
   FROM   cities
   ORDER  BY user_id, city  -- making the pick determinisitic
   ) sub
GROUP  BY 1;

Which is equivalent to:
SELECT city, count(*) AS users
FROM  (
   SELECT min(city)
   FROM   cities
   GROUP  BY user_id
   ) sub
GROUP  BY 1;


Does MySQL cache queries? I'm interfacing a MySQL database with PHP Data Objects (PDO) and executing an extensive SQL query. Normally, it takes about 1500 ms; I still need to optimize it. When I run the PHP script twice with a short interval in between, the query only takes about 90 ms. The query is in both cases the same. When I run the script, with the same query, after some time again, it takes 1500 ms again.

Why is that? Does the database cache automatically? Is there some time the database saves the cache and then automatically deletes it?

I assume the results can't be cached by PHP, because this happens in two different threads. I wouldn't think PHP would cache the results, because it can't know if the database has changed.

I have a script running every minute to insert new rows to the database. This might also be the reason that it takes 1500 ms again after some time; the cache would've been deleted, because the relevant tables aren't the same anymore.

This is likely an artifact of the MySQL Query Cache.

You execute the SQL query, MySQL caches its result and the next execution if fast. When you run the script to insert the data in tables referenced by your query, the result cache gets invalidated and the query must be executed "for real" the next time.

From the MySQL documentation linked above:

  A query mix consisting almost entirely of a fixed set of SELECT
  statements is much more likely to benefit from enabling the cache than
  a mix in which frequent INSERT statements cause continual invalidation
  of results in the cache.


Strategy to handle an SQL Server DB with too many files (BLOBs) in it? Scenario:
SQL Server 2005 database servicing an ASP.NET application (on a separate web servers).

Database:
DB has about 5GB of "normal" data in it, and about 15GB of "files" (e.g.: a 200k PDF stored as an image (BLOB), that sort of thing). More files are being uploaded by users, and are rapidly consuming more disk space (DB could grow to 50GB in the next few months, mostly files).

Concerns:
Storing so many files in the DB is already causing problems (e.g.: The large total size of the database makes occasional whole-DB backups and deployments difficult.).

And we are worried there will be more problems. (e.g.: performance issues - maybe caused by not being able to keep the whole DB in RAM, perhaps?)

Question:
What technical solution would you suggest to this problem? Store the files in the file system? Split the database in two and have a bigger, slower one for files?

Further Details if Needed:
These files are not super-important, and don't need very fast access times - a couple of seconds would be fine, and there are maybe a dozen selects per hour at most, currently. The other "normal" data in the DB includes info needed many times per second.

I look after a very similar database, currently 3TB and growing 5GB per day.

Filestream (2008+) doesn't solve the backup/restore challenge.
Filestream performs better than LOB storage for files >1MB, so says Paul Randal's testing. It's workload dependent at 256KB-1MB and generally worse at <256KB.
A big plus for Filestream in some environments is that it bypasses the buffer pool and uses the Windows system cache instead.
If you put the files on a filesystem, you lose transactional consistency with the database record. You've also added the overhead of backing up millions of individual files, which can be troublesome.

Weigh up the pro's and con's for Filestream and see if it fits in your case. In our case, we've taken a different route and opted for partitioning the database so we can make use of partial availability/piecemeal restore.

One option that wasn't available to us, which you may have, is to mark older/archive filegroups as read-only. The read-only filegroup(s) can then be backed up infrequently.

If your stuck on 2005 Standard (partitioning is an Enterprise edition feature) and you have the option of read-only for history, you could tackle this the old fashioned way.

Split your table. You could consider the active/history route or date based e.g. table per month.
Put the historic data on a read-only filegroup and back it up only when you archive further data. Ensure your users understand that this cuts backup time only. Restore might take a while when you haven't got the partial availability feature.
Create a partitioned view over the tables.

One final option (which we're considering for our 3TB blobber) is to move the file data to a document database or cloud storage (e.g. AmazonS3, Azure BLOB Storage). This does introduce the transactional consistency problem I mentioned earlier but it takes the load away from those very expensive SQL Servers.


How to connect MS sql database and php using xamp I am trying to connect MSsql database and php using xamp. But i am unable to connect. bellow is my code. Please help me to find this.

<?php
$myServer = "x.x.x.x";
$myUser = "xxx";
$myPass = "xxxx";
$myDB = "xxxx";

//connection to the database
$dbhandle = mssql_connect($myServer, $myUser, $myPass)
  or die("Couldn't connect to SQL Server on $myServer");

//select a database to work with
$selected = mssql_select_db($myDB, $dbhandle)
  or die("Couldn't open database $myDB");

//declare the SQL statement that will query the database
$query = "SELECT id, name, year ";
$query .= "FROM cars ";
$query .= "WHERE name='BMW'";

//execute the SQL query and return records
$result = mssql_query($query);

$numRows = mssql_num_rows($result);
echo "<h1>" . $numRows . " Row" . ($numRows == 1 ? "" : "s") . " Returned     </h1>";

//display the results
while($row = mssql_fetch_array($result))
{
  echo "<li>" . $row["id"] . $row["name"] . $row["year"] . "</li>";
}
//close the connection
mssql_close($dbhandle);

Output error msg is:

Fatal error: Call to undefined function mssql_connect() in C:\xampp\htdocs\roshid\mssqlphp.php on line 8

You don't have the MS SQL Drivers installed. You can check this with phpinfo();

On Linux you need mssql.so or sybase.so With debian its apt-get install php5-sybase

For windows take a look here: http://msdn.microsoft.com/en-US/library/cc793139%28v=SQL.90%29.aspx

Drivers need to be configured for PHP to find the function mssql_...

You could also look at PDO DB classes as they can connect to any DBS, you need the drivers installed.


How to configure so the Oracle wallet password can be authenticated with a web based Oracle application? Does anyone know how to configure it so that the Oracle wallet password can be authenticated with a web based Oracle application?

I’m trying to make secure LDAP connections with an Oracle web based application with secure ports. This requires that the application’s source code be able to authenticate against the Oracle wallet password. I am using ONLY the HTTP portion of Oracle Fusion 11.1 to serve these Oracle web pages.  The wallet has been created and the certificates are in place and working.

When we used the Oracle 10g HTTP server we would just make an entry in the SSL.conf file;
SSLWalletPassword encrypted or unencrypted Wallet password   .
But when I try it with Oracle Fusion 11.1, the HTTP server won’t start. I tried several versions of the above, but none worked. I also tried to solve the problem by modifying the opmn.xml file and changed the script to;

But that didn’t work any either. When we try to authenticate with secure LDAP ports our web applications fail.

Does anyone have any ideas how to enable this with the HTTP portion of Oracle Fusion 11.1? Thanks.

Kirk

  Edited to add more information per
  here
  ~ Richard

I'm quite certain that I created the wallet correctly and that the certificate are correct. I've done it before, though not with Fusion 11.

I'm really starting to suspect there are network denial or port issues.  I do a Utl_Http.Get_Detailed_Sqlerrm and will get an

ORA-24247: network access denied by access control list (ACL)...

message.

But the system guys say that all necessary ports are opened and access is okay. But I can make an entry in the DADs.conf file to "point" to another schema on another server (though 10G) and I have no "can't open wallet" issues.

ORA-24247 is something you would get after you already have a connection and are trying to use a network resource (such as UTL_HTTP).  It sounds like you need to create an ACL (Access Control List).  See http://oraclepoint.com/oralife/2010/10/08/configuring-wallet-manager-to-enable-https-connect-with-oracle-11g-database/.


Syinc databases mySQL to Oracle I'm new in "databaase administration"
the problem I am facing is that I want to have an exact copy of a table, from MySQL database (website database) to oracle database (a local one).

What I've done till now

Searched for two days.

I installed MySQL connector/odbc on the 'oracle machine' and successfully connected to the remote MySQL database

Also I can execute queries from the oracle machine like:

select * from remote_table;

and it shows the expected results,

The problem here is:
Is there a way to sync these two databases (mySQL to oracle), or at least update the local one every midnight..

About the two machines

Both machines are running Windows OS

local one :
Running Windows XP
Oracle 8i

remote one :
Running Windows 8.1
MySql 5.6.17

well i created a small program using java packages :

mysql-connector-java-5.1.18-bin.jar

and aoracle jdbc driver :

classes12.zip (this version for oracle 8i)

with mysql connector i can run a "select" statements an read from mysql databse, and then i build an "insert" statement to instert them in the oracle database.

this was the best solution i found


SQL server standard 2016 fail over cluster Amazon AWS I am in the process of migrating to Amazon AWS and need a SQL server high availability solution. The current licence that I have is SQL standard 2016. At this time Amazon does not support shared volumes for Windows instances. Therefore, I am not able to do a regular SQL cluster fail over solution. This is the one where if the entire server goes down the stand by server picks up the slack and continues writing to the same storage. My only option is high availability always on basic groups. As I am starting to get familiar with this feature I find it very maintenance intensive and can see it becoming a problem when dealing with thousands of databases. In my case I have about 5k databases mostly small in size 600mb or less each. My question is Amazon not a viable hosting environment for a full SQL fail over solution. Is the high availability always on basic groups one per database a viable solution?

I did a presentation on this during the summer @ SQL Saturday - LA and you have a few options.  In some cases, you need clustering if Mirroring isn't an option and AlwaysOn AGs require cross database (but same instance I believe) transactions using MSDTC on some versions of SQL Server.

Thus in order to provide failover clustering support in AWS I used an AWS Storage Gateway to act as a routable SAN.  This was simple to setup and was secured easily via the AWS security groups.  We then used mirroring to mirror to Azure and on Azure I used the new SQL and Windows 2016 shared storage direct solutions which are an option for AWS if you use Windows 2016.

Here are the relevant slides on setting up a storage gateway:

-Select the "Storage Gateway" option from the AWS console.

-Select "Cached Gateway" from the options when you start

-Select "Cache Volume" inside the gateway.

-CHAP has to be fully configured. It is fully supported though.

Please note I had to configure both secret and not secret auth for the configuration to work.  YMMV

S2D Options For Windows 2016:
Here are a few items I had to do with S2D on Windows 2016.  Note the hypervisor in this case is Azure but same concept applies:

-These are the S2D requirements for us, read the docs for your environment.

-This tripped me up, be sure to find and use the 'Friendly Name'.

-You can verify it now

-Verify your steps on Node 2 and after this you will pass validation in WSFC.


How to explain/introspect what locks a DDL operation will need (Postgres) I would like to have some way of inspecting what locks a DDL operation will need to acquire.

Preferably something generic enough that I don't need to personally know/encode knowledge of all of the Postgres locking rules.

Something like EXPLAIN but specific to DDL operations.

I don't want to run the command and then look at any locks that are blocking... I want to know in advance what locks the DDL operation will need to acquire (e.g. ACCESS EXCLUSIVE on tables x, y, z), taking into account existing foreign key constraints etc.

What I do is BEGIN the transaction, run the DDL, then look at the pg_locks table to see what locks are held (not blocked, but held).  I do this on the test server, so there is nothing to block it or be blocked by it. But the knowledge generally transfers to production.  Some DDL can't be run in transactions, those generally require special handling.


Can you update invisible columns I'm trying to update the row_start column on a table with Versioning set. The row_start column is invisible.

UPDATE pgwebp.t_prov a
INNER JOIN pgwebp.t_prov b ON a.prov_id_a = b.prov_id
SET a.row_start = b.system_ts_start;

Error message:

Unknown column 'a.row_start' in 'field list'

This looks like the bug/feature request MDEV-16546.

This pertains to the system versioned tables columns like row_start explicitly as normal invisible columns are currently still updateable.


What is non-page data structure in SQL Server I was monitoring one of the SQL Server in my client evnironment and I can see there is momentary blocking on SQL Server sessions with wait type as LATCH_EX and with wait resource as ACCESS_METHODS_DATASET_PARENT.

So I went to world famous library of wait types (sqlskills.com) and I find below line written by Mr. Paul S. Randal in description:

  "This wait type occurs when a thread is waiting for access to a
  non-page data structure so that it can modify the data structure."

I got confused with what does non-page data structure means.

I am not from CS background. Any help is highly appreciated.

From the same source that you mentioned on LATCH_EX

  A latch is a lightweight synchronization mechanism that provides
  synchronization between threads trying to read or change a data
  structure in SQL Server. There are three types of latches:

  Latches on pages being read from disk (these are covered by the    PAGEIOLATCH_XX wait types – see the PAGEIOLATCH_SH wait type for
  more details)
  Latches on pages already in memory (these are covered by the    PAGELATCH_XX wait types – see the PAGELATCH_EX wait type for more
  details)
  Latches on non-page data structures (i.e. everything else)

You are interested in the last part:

  Latches on non-page data structures (i.e. everything else)

Which are latches that do not correspond to data pages in memory or on disk.

Further below in the same source is more information on these types of latches:

  The LATCH_SH and LATCH_EX wait types occur when a thread requires
  access to a non-page data structure (e.g., page buffers in the buffer
  pool (latch type = BUFFER), or the data structure that represents a
  database’s data and log files (latch type = FGCB_ADD_REMOVE)).

With two more examples, waiting on access to page buffers in the buffer pool or data and log file data structures.

An example of accessing the data and log file data structure is shrinking / growing the data or log file of your database.

Your latch type, ACCESS_METHODS_DATASET_PARENT is related to parallellism and latch on the threads needed to acquire scan ranges. More on that here and here.

I would start with looking at the query itself and asking a new question with as much information if the LATCH_EX waits are troublesome for you.


Questions about the output of "statistics IO" Is the TOTAL number of physical reads a query did = [physical reads] + [read-ahead reads]?
Why sometimes we have [physical reads]=0 while the [read-ahead reads]>0? what does that mean?
How many pages SQL Server reads in one physical read? ... if it is
one to one, then
Why when "select * from aTable", the number of [physical reads]
and/or [read-ahead reads] does not equal the table's number of pages?
although the cache was cleared before executing the query.
why sometimes [logical reads] is greater than [physical reads] +
[read-ahead reads], and even greater than the total number of pages
of the table?

Is the TOTAL number of physical reads a query did = [physical readsc] +
  [read-ahead reads]?

No. These are calculated separately.

physical reads:
Number of pages read from disk-Running thread go to runnable state and wait for the IO to finish.

read-ahead reads:

The Database Engine supports a performance optimization mechanism called read-ahead. Read-ahead anticipates the data and index pages needed to fulfill a query execution plan and brings the pages into the buffer cache before they are actually used by the query. This allows computation and I/O to overlap, taking full advantage of both the CPU and the disk.There are two kinds of read-ahead: one for data pages and one for index pages.

  Why sometimes we have [physical reads]=0 while the [read-ahead
  reads]>0? what does that mean?

As mentioned before read ahead pages are not included in physical read. This means all pages required are already in cache. Either the pages already existed or placed in cache by read ahead mechanism.

  How many pages SQL Server reads in one physical read? ... if it is one
  to one, then

It is not one to one. It will vary.  See reference section below.

  Why when "select * from aTable", the number of [physical reads] and/or
  [read-ahead reads] does not equal the table's number of pages?
  although the cache was cleared before executing the query.

As explained a physical read and read ahead can read more than one page at a time.

The read-ahead mechanism allows the Database Engine to read up to 64 contiguous pages (512KB) from one file. The read is performed as a single scatter-gather read to the appropriate number of (probably non-contiguous) buffers in the buffer cache. If any of the pages in the range are already present in the buffer cache, the corresponding page from the read will be discarded when the read completes. The range of pages may also be "trimmed" from either end if the corresponding pages are already present in the cache.

  why sometimes [logical reads] is greater than [physical reads] +
  [read-ahead reads]

Because your pages are already in buffer so reads in this case means logical read and you do not need (or you need less than) physical read + read ahead read.

  and even greater than the total number of pages of the table?

It is possible to read same page more than once while reading from cache. Think about seek operation on an index. If you have to do 10 seeks, you will always do 10 logical read on the root page and possibly 10 logical reads on the each intermediate level.

Reference:

SET STATISTICS IO (Transact-SQL)
Reading Pages
When does “physical reads” include read-ahead reads in SQL
Server? by Kendra Little
How It Works: Bob Dorr’s SQL Server I/O Presentation


How to partition the table, week-wise? I have a table with hundreds of thousands of records. How to partition the table, week-wise?

A few million rows insertion will be taking place in my table in a day.So i need to partition it weekly. There is a datetime column which I want to partition by weekly

First of all what kind of database are you using? And are you referring to table partitioning?

Anyways for Oracle, please see this link..

https://forums.oracle.com/forums/thread.jspa?threadID=879608

I'm not too sure about how this would be done in PostgreSQL, but I think partitioning is much less relevant for PostgreSQL rather than for Oracle


How to export user with tables and tablespace oracle I'm a newbie to Oracle World.

I have to export, from computer A, tables and their data, to computer B.

So, my question is: is there a way to export user, tablespace, tables and their data with one single command?
Or I have to create before a tablespace and a user in the computer B, and then import the tables?

Sorry, the question might seem stupid, but I'm a newbie, I'm a little confused and afraid to not fully understand the  correct procedure/steps.
Thank you

Usual way of doing that is data pump. As you want to copy user, you'd use the schema mode.

This example is based on 11gXE. If you use another database version, you might need to adjust it.

As data pump requires existence of a directory (Oracle object), you'd have to create it in both source and target database - connect as SYS and run something like this:

SQL> create directory my_dir as 'c:\temp';

Directory created.

SQL> grant read, write on directory my_dir to scott;

Grant succeeded.

SQL>

Physical directory (c:\temp in my example) resides on a database server; as I run 11gXE, my laptop is the database server as well.

When exporting, the export (dmp) file, as well as log file, will be created in that directory. The same goes for import - you'll have to put the dmp file into it.

In both databases, I'm creating a user which will perform export/import and I'll grant it DBA role so that - during import - it also creates user. Otherwise, it'll fail. Also, I'm granting datapump_exp_full_database, datapump_imp_full_database so that it export privileges user already has (otherwise, everything will be exported, but newly imported user will lack in any privilege, even create session).

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS

SQL> create user importer identified by imp
  2  default tablespace users
  3  temporary tablespace temp
  4  profile default
  5  quota unlimited on users;

User created.

SQL> grant dba, datapump_exp_full_database, datapump_imp_full_database to importer;

Grant succeeded.

At the operating system command prompt, run EXPDP to perform export:

expdp importer/imp@xe schemas=scott directory=my_dir dumpfile=scotte.dmp logfile=scott_e.log

It looks like this:

c:\Temp>expdp importer/imp@xe schemas=scott directory=my_dir dumpfile=scotte.dmp logfile=scott_e.log

Export: Release 11.2.0.2.0 - Production on ╚et Ruj 6 21:13:55 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Starting "IMPORTER"."SYS_EXPORT_SCHEMA_01":  importer/********@xe schemas=scott directory=my_dir dum
pfile=scotte.dmp logfile=scott_e.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."BONUS"                             6.210 KB       1 rows
. . exported "SCOTT"."DEPT"                              5.929 KB       4 rows
. . exported "SCOTT"."EMP"                               8.484 KB      12 rows
. . exported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . exported "SCOTT"."EMP_LOG"                               0 KB       0 rows
. . exported "SCOTT"."TABLE01"                               0 KB       0 rows
. . exported "SCOTT"."TABLE02"                               0 KB       0 rows
. . exported "SCOTT"."TABLE03"                               0 KB       0 rows
Master table "IMPORTER"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for IMPORTER.SYS_EXPORT_SCHEMA_01 is:
  C:\TEMP\SCOTTE.DMP
Job "IMPORTER"."SYS_EXPORT_SCHEMA_01" successfully completed at 21:14:18

c:\Temp>

Copy dmp file to target computer and perform import.

I'll just drop user Scott (as I don't have access to another database/server right now):

SQL> show user
USER is "SYS"
SQL> drop user scott cascade;

User dropped.

SQL>

OK, let's import it - operating system command prompt again; command is almost equal to expdp. If tablespace names are different in the source and target databases, you'll need the REMAP_TABLESPACES parameter.

impdp importer/imp@xe schemas=scott directory=my_dir dumpfile=scotte.dmp logfile=scott_i.log

Import looks like this:

c:\Temp>impdp importer/imp@xe schemas=scott directory=my_dir dumpfile=scotte.dmp logfile=scott_i.log

Import: Release 11.2.0.2.0 - Production on ╚et Ruj 6 21:14:50 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Master table "IMPORTER"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "IMPORTER"."SYS_IMPORT_SCHEMA_01":  importer/********@xe schemas=scott directory=my_dir dum
pfile=scotte.dmp logfile=scott_i.log
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
ORA-39082: Object type TYPE:"SCOTT"."IBS_S_DOCUMENT_T_1" created with compilation warnings
ORA-39082: Object type TYPE:"SCOTT"."IBS_S_DOCUMENT_T_1" created with compilation warnings
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."BONUS"                             6.210 KB       1 rows
. . imported "SCOTT"."DEPT"                              5.929 KB       4 rows
. . imported "SCOTT"."EMP"                               8.484 KB      12 rows
. . imported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . imported "SCOTT"."EMP_LOG"                               0 KB       0 rows
. . imported "SCOTT"."TABLE01"                               0 KB       0 rows
. . imported "SCOTT"."TABLE02"                               0 KB       0 rows
. . imported "SCOTT"."TABLE03"                               0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
ORA-39082: Object type ALTER_PROCEDURE:"SCOTT"."A1_P_LOG" created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"SCOTT"."OVERDUETODAY" created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"SCOTT"."PROC" created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"SCOTT"."PROC_SECOND" created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"SCOTT"."P_MATCH" created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"SCOTT"."P_SURVEY" created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"SCOTT"."REPORTS_ITEM_INFO" created with compilation warnings

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
ORA-39082: Object type PACKAGE_BODY:"SCOTT"."MY_JOIN_TEST_SP_PACKAGE_3" created with compilation war
nings
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
ORA-39082: Object type TRIGGER:"SCOTT"."TRG_BU_DEPT" created with compilation warnings
ORA-39082: Object type TRIGGER:"SCOTT"."TRG_BU_DEPT" created with compilation warnings
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "IMPORTER"."SYS_IMPORT_SCHEMA_01" completed with 12 error(s) at 21:14:55

c:\Temp>

Certain objects were imported in invalid state; no wonder, I'm using Scott for testing purposes, creating & dropping objects all the time. In your case, import will probably look prettier.

Let's try to connect to newly imported Scott:

c:\Temp>sqlplus scott/tiger

SQL*Plus: Release 11.2.0.2.0 Production on ╚et Ruj 6 21:30:44 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
DEPT_EXT                       TABLE
EMP                            TABLE
EMP_LOG                        TABLE
SALGRADE                       TABLE
TABLE01                        TABLE
TABLE02                        TABLE
TABLE03                        TABLE

9 rows selected.

SQL> create table test (id number);

Table created.

SQL>

Seems to be OK. Give it a try; you can't spoil anything. If anything goes wrong at the target database, just drop the imported user and try again.

I suggest you read data pump documentation. Seeing an example is nice, but - you'd better understand what's going on.

Good luck!


What is mongodb logging as a COMMAND? Relating to mongodb's documentation, database commands such as count will be logged as component COMMAND. However neither db.count() nor db.runCommand({count:'mycollection'}) produces a COMMAND log entry, even if the default log level for COMMAND is raised from 0 to 1.
Dropping an index, a collection or a database produces a COMMAND log entry even with the default log level 0 though.

Moreover, it seems that almost all mongodb actions can be expressed as command (even a find), as shown in mongodb's documentation, so I wonder how one could properly differentiate between QUERY which logs messages related to queries and COMMANDs.

My goal is to filter out (administrative) commands from the logs for auditing (and I don't want to use mongodb's enterprise version with auditing functionalities for this single purpose).
I'm using mongodb version 4.0.5 Linux 64Bit.

Thanks for shedding light on this!

After setting the verbosity level for command as 1, I am able to see the following entries in the log file

  2019-03-01T16:25:56.967-0800 I COMMAND  [conn67] command test.icons
  appName: "MongoDB Shell" command: count { count: "icons",
  $readPreference: { mode: "secondaryPreferred" }, $db: "test" }
  planSummary: COUNT keysExamined:0 docsExamined:0 numYields:0 reslen:45
  locks:{ Global: { acquireCount: { r: 1 } }, Database: { acquireCount:
  { r: 1 } }, Collection: { acquireCount: { r: 1 } } }
  protocol:op_command 0ms

here the collection name is icons and the database is test

I used the following command to increase the verbosity level.

db.setLogLevel(1, "command")


MySQL Tuning and optimization How to Tune and Optimize MySQL Database, as a reference we are using mysqltuner.pl and we got the below output of slave host which we created recently. our future plan is the perform this same action on master, when performing this action on master slave will become master.

Both on Centos 6.7
Master Version MySQL-server-5.5.41-1.el6.x86_64
Slave Version mysql-community-server-5.6.28-2.el6.x86_64

-------- General Statistics -------------------------------------                                                                      -------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.6.28-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics ------------------------------                                                                      -------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
Warning: Using a password on the command line interface can be in                                                                      secure.
Warning: Using a password on the command line interface can be insecure.
[--] Data in MyISAM tables: 335M (Tables: 62)
[--] Data in InnoDB tables: 107G (Tables: 1670)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 1)
[!!] Total fragmented tables: 551

-------- Security Recommendations  -------------------------------------------
Warning: Using a password on the command line interface can be insecure.
[OK] All database users have passwords assigned
Warning: Using a password on the command line interface can be insecure.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 13h 21m 48s (3K q [0.071 qps], 1K conn, TX: 8M, RX: 270M)
[--] Reads / Writes: 4% / 96%
[--] Total buffers: 109.1G global + 29.2M per thread (600 max threads)
[!!] Maximum possible memory usage: 126.3G (100% of installed RAM)
[OK] Slow queries: 0% (0/3K)
[OK] Highest usage of available connections: 0% (2/600)
[OK] Key buffer size / total MyISAM indexes: 1.0G/153.6M
[OK] Key buffer hit rate: 100.0% (7M cached / 2K reads)
[!!] Query cache efficiency: 0.0% (0 cached / 3K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 5% (7 temp sorts / 125 sorts)
[!!] Joins performed without indexes: 252
[OK] Temporary tables created on disk: 21% (344 on disk / 1K total)
[OK] Thread cache hit rate: 99% (2 created / 1K connections)
[OK] Table cache hit rate: 23% (309 open / 1K opened)
[OK] Open file limit used: 0% (48/20K)
[OK] Table locks acquired immediately: 99% (76K immediate / 76K locks)
[!!] Connections aborted: 20%
[!!] InnoDB data size / buffer pool: 108.0G/107.0G

How do we work on join queries Run OPTIMIZE TABLE closing MySQL connections and footprint for system stability.

-------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Reduce your overall MySQL memory footprint for system stability
        Adjust your join queries to always utilize indexes
        Your applications are not c properly
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        query_cache_limit (> 1M, or use smaller result sets)
        join_buffer_size (> 3.0M, or always use indexes with joins)
        innodb_buffer_pool_size (>= 107G)

[!!] Total fragmented tables: 551  -- bogus, ignore it
[0.071 qps],  -- very lightly loaded
[!!] Maximum possible memory usage: 126.3G (100% of installed RAM)  -- bogus, ignore it
[OK] Highest usage of available connections: 0% (2/600)  -- consider lowering max_connections
[OK] Key buffer size / total MyISAM indexes: 1.0G/153.6M  -- lower key_buffer_size
[!!] Query cache efficiency: 0.0% (0 cached / 3K selects)  -- Turn the QC off
[OK] Sorts requiring temporary tables: 5% (7 temp sorts / 125 sorts)  -- Let's see some slow queries and help you optimize them
[!!] Joins performed without indexes: 252  -- ditto
[OK] Temporary tables created on disk: 21% (344 on disk / 1K total)  -- ditto
[OK] Open file limit used: 0% (48/20K)  -- 20K?
[!!] InnoDB data size / buffer pool: 108.0G/107.0G  -- bogus; it's a cache!

General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance  -- NO!
    MySQL started within last 24 hours - recommendations may be inaccurate  -- true
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***  -- bogus
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_limit (> 1M, or use smaller result sets)  -- no, set to 0
    innodb_buffer_pool_size (>= 107G)  -- No, it is dangerously high now (assuming you have 128GB of RAM)

For more analysis, provide SHOW VARIABLES and SHOW GLOBAL STATUS, plus RAM size.  (Due to limitations here, you may need a postit site.)

The biggest problem seems to be poorly designed/indexed queries.  Those cannot be fixed by tuning.


How can i know the relationship in the database in sql server 2008? I want to know how can I determine relationships between data which is not determined by the primary key in the design.

For example, I have three tables in the database like this :-

table 1: fields
table 2: area
table 3: location

All of the tables have data but the person who created the database did not explain the primary keys and the foreign keys in it.  How can I know the relationship between these tables?

When I have encountered similar problems to this in the past, I have used the query that marc_s provides here: Know relationships between all the tables of database in SQL Server

It should give you a good place to start?

Here it is replicated in case the other thread is removed in the future (credit - marc_s):

SELECT
fk.name 'FK Name',
tp.name 'Parent table',
cp.name, cp.column_id,
tr.name 'Refrenced table',
cr.name, cr.column_id
FROM
sys.foreign_keys fk
INNER JOIN
sys.tables tp ON fk.parent_object_id = tp.object_id
INNER JOIN
sys.tables tr ON fk.referenced_object_id = tr.object_id
INNER JOIN
sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN
sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id =      cp.object_id
INNER JOIN
sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id
ORDER BY
tp.name, cp.column_id


SQL Server 2012 Service Master Key Regenerate I have a new SQL Server 2012 instance and when I run the following command I get an error.

ALTER SERVICE MASTER KEY REGENERATE

Error:

  Msg 15329, Level 16, State 1, Line 1
  The current master key cannot be decrypted. If this is a database master key, you should attempt to open it in the session before performing this operation. The FORCE option can be used to ignore this error and continue the operation but the data encrypted by the old master key will be lost.

When I try with the Force option I get:

  The current master key cannot be decrypted. The error was ignored because the FORCE option was specified.

Error:

  Msg 33096, Level 16, State 1, Line 1
  A generic failure occurred during Service Master Key encryption or decryption.

Any ideas?

In order to regenerate your master key, you need to open it with the old password.  Then, once it's open, you can use the REGENERATE syntax to change it to a new password:

OPEN MASTER KEY DECRYPTION BY PASSWORD = '0ldP@55w0rd';

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'N3wP@55w0rd';

CLOSE MASTER KEY;

Which is why you should always have your encryption passwords safely secured.  Without them, you could be in a place where you can't decrypt your databases.


Mongodb multikey index and $all in queries I have such structure of documents in my products collection:

{
  title: 'Samsung Galaxy S III',
  category_id: ObjectId("50bcc2f0b910a6c1936a4424"),
  properties: [
    {
      title: 'OS',
      value: 'Android'
    },
    {
      title: 'Display',
      value: '4.8"'
    }
  ]
}

Also I've created multikey index: {category_id: 1, 'properties.value': 1, 'properties.title': 1}

What happens when I do query with $all? How it works? Example:

{
  category_id: ObjectId("50bcc2f0b910a6c1936a4424"),
  properties: {
    $all: [
      {
        $elemMatch: {
          title: 'OS',
          value: 'Android'
        }
      },
      {
        $elemMatch: {
          title: 'Display',
          value: '4.8"'
        }
      }
    ]
  }
}

Does it find elements in index by first $elemMatch and then iterate over all found element to ensure second $elemMatch?

For my test collection with 2 androids and 1 ios explain() returns:

{
        "cursor" : "BtreeCursor category_id_1_properties.title_1_properties.value_1",
        "isMultiKey" : true,
        "n" : 1,
        "nscannedObjects" : 2,
        "nscanned" : 2,
        "nscannedObjectsAllPlans" : 2,
        "nscannedAllPlans" : 2,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "indexBounds" : {
            "category_id" : [
                    [
                            ObjectId("50bcc2f0b910a6c1936a4424"),
                            ObjectId("50bcc2f0b910a6c1936a4424")
                    ]
            ],
            "properties.title" : [
                    [
                            "OS",
                            "OS"
                    ]
            ],
            "properties.value" : [
                    [
                            "Android",
                            "Android"
                    ]
            ]
        }
}

First, not sure if you have altered the question or not since posted, but contrary to one of the answers, this is not doing a full collection scan, it is using an index, as evidenced by the "cursor" : "BtreeCursor in the explain output.

As for your question - you are asking about the $all operator, but I think your issue here is that you are using the $elemMatch operator incorrectly.  When you do:

$elemMatch: {
          title: 'OS',
          value: 'Android'
        }

Or similar, you are running $elemMatch against a document, not an array, which is how it is intended.  Hence the explain shows the bounds on the index as specific values of just OS and Android on properties.title and properties.value respectively rather than what you probably wanted, which I think is to only return if all the various properties are present in the array.

Try without $elemMatch:

{
  category_id: ObjectId("50bcc2f0b910a6c1936a4424"),
  properties: {
    $all: [
      {
          title: 'OS',
          value: 'Android'
      },
      {
          title: 'Display',
          value: '4.8"'
      }
    ]
  }
}


How to change SQL Server master key password? I created a master key, then a certificate and encrypted my database.

CREATE MASTER KEY ENCRYPTION
BY PASSWORD='A Very Strong Password';
GO

CREATE CERTIFICATE foo
WITH
SUBJECT='Database Encryption for Multi';
GO

USE mydatabase
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE foo;
GO

ALTER DATABASE mydatabase SET ENCRYPTION ON;
GO

How do I change the MASTER KEY password?

Thanks

According to the Microsoft Docs page, syntax is:
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'password';

From that docs page:

The REGENERATE option re-creates the database master key and all the keys it protects. The keys are first decrypted with the old master key, and then encrypted with the new master key. This resource-intensive operation should be scheduled during a period of low demand, unless the master key has been compromised.

However, ensure you understand the ramifications of changing the key, and make sure you backup the new key.
Check my recent post for more details.
Some notes:

You're actually not modifying the existing master key with ALTER MASTER KEY REGENERATE. You're really decrypting anything that relies on the key, then dropping the old key and creating an entirely new key, then using that new key to re-encrypt everything that was encrypted previously with the old key.

You need to be a member of the sysadmin group before you can change the master key. Also, changing the master key doesn't decrypt and re-encrypt any TDE-encrypted database, it simply encrypts and re-encrypts certificates that have been encrypted with the key.  Those certificates remain valid and are not modified in any way; simply they are protected with a new master key.

Saving passwords in scripts is always a problem.  I've addressed that in the past by treating the certificate-related scripts as protected, the same way I protect TDE keys. In source code control systems, it's necessary to apply access controls to the scripts to ensure only authorized sysadmins can access them.