ENABLE/DISABLE KEYS lag: how to optimize? I've got 32 GB of RAM and a mysql table with varying size of rows: 100m - 500m rows (each row is around 30 bytes). Every hour a PHP script DISABLEs KEYS of the table, LOADs a couple of millions of new rows from a CSV file, and ENABLEs KEYS again. This works well (done within 2 mins) as long as the table has less than 200m rows. Once it grows further, extreme lagging is experienced: the disable-enable keys procedure suddenly lasts longer than an hour.

The table is trimmed frequently back to 100m rows (let's say once every day).

For all I care, I am ready to devote 90% of all resources of my server for this script only (only during the execution of the script, naturally).

QUESTIONS

Why this lagging?
What system variables could I define in my PHP script to try to overcome the lagging?

There are some rare occasions when mysqld processes ALTER TABLE ... ENABLE KEYS; and you get Repair by keycache. The real question to ask is Under what circumstances does mysqld perform Repair by keycache ?

According to MySQL Documentation on General Thread States

  Repair with keycache

  The repair code is using creating keys one by one through the key cache. This is much slower than Repair by sorting.

There are one or more server variables in the session that could potentially affect this behavior

sort_buffer_size

Independent of Storage Engine
The default is 256K
If this is set to 2M and mysqld needs more that 2M to process a sort operation, the session dumps the 2M it has in the sort buffer to a temporary MyISAM table, then continue processing from there.

innodb_sort_buffer_size

Default: 1M, MinValue: 64K, MaxValue: 64M
Documentation says three buffers of this size are allocated for an ALTER TABLE or CREATE TABLE statement that creates an index.

myisam_sort_buffer_size

Default value is 8M.
Definition : The size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE.
If the sort operation needs more than 8M, there is where mysqld will make the session stop doing Repair by sorting and switch to Repair by keycache.

SUGGESTION

Based on the MySQL Documentation on sort_buffer_size

  As of MySQL 5.6.4, the optimizer tries to work out how much space is needed but can allocate more, up to the limit. Before MySQL 5.6.4, the optimizer allocates the entire buffer even if it is not all needed. In either case, setting it larger than required globally will slow down most queries that sort. It is best to increase it as a session setting, and only for the sessions that need a larger size. On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation, so you should consider staying below one of those values. Experiment to find the best value for your workload. See Section B.5.4.4, “Where MySQL Stores Temporary Files”.

Only sort_buffer_size and myisam_sort_buffer_size can be changed for the session.

If you don't have a lot of memory, change myisam_sort_buffer_size so that a larger temp table can be formed. To set it to 1G in your session, do one of the following before executing a large reindexing:

SET myisam_sort_buffer_size = 1024 * 1024 * 1024;

or

SET SESSION myisam_sort_buffer_size = 1024 * 1024 * 1024;

That way, the temp table on disk will be allowed to grow to 1G. Make sure the folder mapped to tmpdir can hold large temp tables.

If you want 1G as the permanent setting for myisam_sort_buffer_size going forward, do this

STEP #1 : Add this to /etc/my.cnf

[mysqld]
myisam_sort_buffer_size=1G

This will allow connections to have this setting after mysqld is restarted.

STEP #2 : Login to mysql as root@localhost and run

SET GLOBAL myisam_sort_buffer_size = 1024 * 1024 * 1024;

This will enabled new connections to have this setting. Current connections would have to be closed and reopened (perhaps restarting apache/tomcat/Hibernate or whatever web server you are using).

MY OTHER POSTS ON Repair by keycache

May 09, 2012 : ENABLE KEYS on a large table is crashing mysql
May 17, 2013 : MySQL Hanging Completely when `ALTER TABLE... ENABLE KEYS`
Aug 12, 2013 : MySQL MyISAM index causes query to match no rows; indexes disabled, rows match


mongodb: Can I create an unshard Replica set for a sharded cluster? I have a 3 node shard cluster mongoDB with replica set, and want to add (single node replica set) a un-sharded Replica set to collect all data in one node.

Can I create an unshard Replica set for a sharded cluster?

mongodb: Can I create an unshard Replica set for a sharded cluster?

As per MongoDB documentation here Starting in MongoDB 3.6, all shards must be replica sets. Before you can upgrade your 3.4 sharded cluster to version 3.6, you must convert any shards that are running as standalone mongod instances to replica set shards.

  Note: To converts a standalone shard to a single-member replica set shard. The procedure assumes that the
  single member runs on the same host and port as before.


MySQL/InnoDB - Clustering Index with UUID In MS SQL Server it is possible to cluster an index on a non-primary, non-unique key. For example, if I wanted to create a clustered index on the insert date to prevent page splits and fragmentation upon insert in the middle of a UUID primary key, SQL Server would perform the background work for this.

It appears in MySQL/InnoDB that the clustered index would be created upon the first primary or unique key added to the table. Other than adding a non-UUID primary key, or not building any unique keys, how would the major fragmentation be avoided? Is there another engine that would work better with this?

If unavoidable, are there any steps that can be taken to mitigate the issue outside of periodic rebuilds of the clustered index?

Face it, wherever you have the UUID as a KEY, it will be in a fragmented BTree.  But, the BTree is kept reasonably clean.  That is, when a BTree block is too full to accept another row, it splits into two blocks, each about half full.  As time goes on, any new inserts into either of those blocks will simply add to the blocks without immediately splitting.  The end result of 'random' inserts is blocks that average about 69% full.  This is only slightly worse than 100% full.

InnoDB uses only BTrees.  The data is 'clustered' with the PRIMARY KEY.  There is always a PRIMARY KEY:

an explicit PK, or
the first UNIQUE key with non-null column(s), or
a fabricated, hidden, 6-byte PK.

If your UUID is the PK, then you are doing splits/fragmentation on the data.  If your UUID is a secondary key, then that BTree suffers from splits/fragmentation.  There is (almost) no escaping it.

I say 'almost' because if you are using Type-1 UUIDs, you can shuffle the bits to make them approximately time oriented.  This makes them much like AUTO_INCREMENT ids.  I discuss that in my blog.

Yes, you could rebuild whatever index contains the UUID. This is via the OPTIMIZE TABLE, which rebuilds the table, blocking access during the process.  And, as I say, you won't gain much from it.  Ordinary blocks splits are not costly; OPTIMIZE is.  I often tell people to 'never' use OPTIMIZE.

Is your entire table small enough to be cached in the buffer_pool?  If so, UUIDs are not too much of a performance problem.  On the other hand, once the table (or the UUID index) gets much bigger than the buffer pool, processing becomes I/O-bound.  This is because of the random nature of UUIDs.  As the table grows, each INSERT and each SELECT (using UUID) becomes more and more likely to require a disk hit.

If you cannot avoid UUIDs (which would be my first recommendation), you can at least shrink them from taking 110 bytes via a casual implementation (VARCHAR(36) w/utf8) or 36 bytes (CHAR(36) w/ascii) and shrink it down to 16 bytes (BINARY(16)).  See my blog for the pair of Stored Functions.  Smaller --> More cacheable --> Less I/O --> Faster.

If you would like to discuss further what you are doing, I would be happy to elaborate further.


Questions about DBCC FREEPROCCACHE I used spAskBrent for troubleshooting a SQL Server 2005 performance issue.

I found the problem was someone had ran DBCC FREEPROCCACHE. It caused low performance (very low PLE) on my database and queries to time out.

How can I find out who ran the query to free the plan cache?

You should be able to locate this within the default trace for SQL Server, if it is still enabled on the instance. One of the events captured is the Security Audit: Audit DBCC Event, can read more on the event here.

If you find the default trace is enabled the following query from MSSQLTips.com article to find when DBCC were executed:

DECLARE @path NVARCHAR(260)

SELECT @path=path FROM sys.traces WHERE is_default = 1

--Security Audit: Audit DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKCATALOG,
--DBCC CHECKALLOC, DBCC CHECKFILEGROUP Events, and more.
SELECT TextData, Duration, StartTime, EndTime, SPID, ApplicationName, LoginName
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass IN (116) AND TextData like 'DBCC%CHECK%'
ORDER BY StartTime DESC


Is there a use case where RDBMS is the best data store for querying only? I am trying to figure out whether there is any legitimate use case for Relational Data Store (like MySQL, PostgreSQL, etc) in DDD, Event sourcing and its projections. Hear me out.

In event sourcing your OLTP and write side is solved for you. For OLAP and the whole read/query side, you are free to create a projection - store data from the source of truth in a way that will fit your query use case the best way possible. So that the read is extremely fast. Therefore I am trying to figure out, if I am only to query the data store, is there a use case, that I would want to store in RDBMS/Relational Data Store? I see that for any use case there is a better Data Store than Relational.

Do you need to get one item by ID? - key-value Data store is the best
Do you need some nested structures to query? - Document Data store is the best
Do you want to query relations between structures? - Graph Data store is the best
Do you want to do some analytical processing? Aggregations, averages and what not? - Column Data store is the best.

Is my thinking correct? Do you see any flaws? Read use cases that RDBMS solves the best?

I find your question a bit confusing, as you mix different concept domains togeter.

RDBMS stands for "Relational database management system". If your data model is relational in nature, you use an RDBMS to store, process and query it. How it optimizes these things physically is an implementation detail (your "Column Data store" is one example).

If your data model is strictly non-relational, you might choose a different DBMS, such as key-value store type, whose implementation is more optimized for the corresponding use cases.


how to query SSISDB to find out the errors in the packages? I have seen this question
SSIS 2012 - How to Query Currently Running Packages in T-SQL?

It gives me the following script:

SELECT
    E.execution_id
,   E.folder_name
,   E.project_name
,   E.package_name
,   E.reference_id
,   E.reference_type
,   E.environment_folder_name
,   E.environment_name
,   E.project_lsn
,   E.executed_as_sid
,   E.executed_as_name
,   E.use32bitruntime
,   E.operation_type
,   E.created_time
,   E.object_type
,   E.object_id
,   E.status
,   E.start_time
,   E.end_time
,   E.caller_sid
,   E.caller_name
,   E.process_id
,   E.stopped_by_sid
,   E.stopped_by_name
,   E.dump_id
,   E.server_name
,   E.machine_name
,   E.total_physical_memory_kb
,   E.available_physical_memory_kb
,   E.total_page_file_kb
,   E.available_page_file_kb
,   E.cpu_count
,   F.folder_id
,   F.name
,   F.description
,   F.created_by_sid
,   F.created_by_name
,   F.created_time
,   P.project_id
,   P.folder_id
,   P.name
,   P.description
,   P.project_format_version
,   P.deployed_by_sid
,   P.deployed_by_name
,   P.last_deployed_time
,   P.created_time
,   P.object_version_lsn
,   P.validation_status
,   P.last_validation_time
,   PKG.package_id
,   PKG.name
,   PKG.package_guid
,   PKG.description
,   PKG.package_format_version
,   PKG.version_major
,   PKG.version_minor
,   PKG.version_build
,   PKG.version_comments
,   PKG.version_guid
,   PKG.project_id
,   PKG.entry_point
,   PKG.validation_status
,   PKG.last_validation_time
FROM
    SSISDB.catalog.executions AS E
INNER JOIN
    ssisdb.catalog.folders AS F ON F.name = E.folder_name
INNER JOIN
    SSISDB.catalog.projects AS P ON P.folder_id = F.folder_id
                                 AND P.name = E.project_name
INNER JOIN
    SSISDB.catalog.packages AS PKG ON PKG.project_id = P.project_id
                                   AND PKG.name = E.package_name;

But it does not answer my quest. I am investigating the reasons why packages fail and I need to get hold of the error messages.

Where can I find it?

I would like to use T-SQL to query for the error message.

I have also this script below, that takes me near, but not quite:

SELECT
    q.*
FROM
    (SELECT em.*
     FROM SSISDB.catalog.event_messages em
     WHERE em.operation_id = (SELECT MAX(execution_id)
                              FROM SSISDB.catalog.executions)
       AND event_name NOT LIKE '%Validate%') q
/* Put in whatever WHERE predicates you might like*/
--WHERE event_name = 'OnError'
WHERE package_name = 'InfoGroup Feed.dtsx'
--WHERE execution_path LIKE '%<some executable>%'
ORDER BY message_time DESC

This is the email I would like to tackle, how did they get to that error message:

  Any information as how to troubleshoot SSIS errors is welcome.

I have a handful of queries I use. The general concepts are that the table with the information is catalog.operation_messages and you are interested in events with a 120 (error) type.

Depending on how robust of a query you want to build out, the following two derived tables might also be of interest.

--- http://technet.microsoft.com/en-us/library/ff877994.aspx
-- This query translates the message_type from SSISDB.catalog.operation_messages
-- into useful text
SELECT
    D.message_type
,   D.message_desc
FROM
(
    VALUES
        (-1,'Unknown')
    ,   (120,'Error')
    ,   (110,'Warning')
    ,   (70,'Information')
    ,   (10,'Pre-validate')
    ,   (20,'Post-validate')
    ,   (30,'Pre-execute')
    ,   (40,'Post-execute')
    ,   (60,'Progress')
    ,   (50,'StatusChange')
    ,   (100,'QueryCancel')
    ,   (130,'TaskFailed')
    ,   (90,'Diagnostic')
    ,   (200,'Custom')
    ,   (140,'DiagnosticEx Whenever an Execute Package task executes a child package, it logs this event. The event message consists of the parameter values passed to child packages.  The value of the message column for DiagnosticEx is XML text.')
    ,   (400,'NonDiagnostic')
    ,   (80,'VariableValueChanged')
) D (message_type, message_desc);

-- Where was the error message generated?
SELECT
    D.message_source_type
,   D.message_source_desc
FROM
(
    VALUES
        (10,'Entry APIs, such as T-SQL and CLR Stored procedures')
    ,   (20,'External process used to run package (ISServerExec.exe)')
    ,   (30,'Package-level objects')
    ,   (40,'Control Flow tasks')
    ,   (50,'Control Flow containers')
    ,   (60,'Data Flow task')
) D (message_source_type, message_source_desc);

I use queries like this to find information about the errors. Maybe I only care about what the errors were (query 1). Other times, I want to know all the activities of all the operations that failed (query 2). Generally, I'm lazy and want to see all the information about the last failing operation (query 3 and note the caveat).

-- http://msdn.microsoft.com/en-us/library/ff877994.aspx
-- Find all error messages
SELECT
    OM.operation_message_id
,   OM.operation_id
,   OM.message_time
,   OM.message_type
,   OM.message_source_type
,   OM.message
,   OM.extended_info_id
FROM
    catalog.operation_messages AS OM
WHERE
    OM.message_type = 120;

-- Generate all the messages associated to failing operations
SELECT
    OM.operation_message_id
,   OM.operation_id
,   OM.message_time
,   OM.message_type
,   OM.message_source_type
,   OM.message
,   OM.extended_info_id
FROM
    catalog.operation_messages AS OM
    INNER JOIN
    (
        -- Find failing operations
        SELECT DISTINCT
            OM.operation_id
        FROM
            catalog.operation_messages AS OM
        WHERE
            OM.message_type = 120
    ) D
    ON D.operation_id = OM.operation_id;

-- Find all messages associated to the last failing run
SELECT
    OM.operation_message_id
,   OM.operation_id
,   OM.message_time
,   OM.message_type
,   OM.message_source_type
,   OM.message
,   OM.extended_info_id
FROM
    catalog.operation_messages AS OM
WHERE
    OM.operation_id =
    (
        -- Find the last failing operation
        -- lazy assumption that biggest operation
        -- id is last. Could be incorrect if a long
        -- running process fails after a quick process
        -- has also failed
        SELECT
            MAX(OM.operation_id)
        FROM
            catalog.operation_messages AS OM
        WHERE
            OM.message_type = 120
    );

Perhaps I'm lazy and don't want to go look this information up in the event of a failure, much as your team appears to have done. I have a SQL Agent job that runs on demand and I have jobs that run SSIS packages set to run that job in the event of failure.

DECLARE
    @profile_name sysname = 'SQLAdmins'
,   @recipients varchar(max) = 'billinkc@kfc.com'
,   @copy_recipients varchar(max) = NULL
,   @blind_copy_recipients varchar(max) = NULL
,   @subject nvarchar(255) = 'failed package test'
,   @body nvarchar(max) = 'Stuff has failed, fix please'
,   @body_format varchar(20) = NULL
,   @importance varchar(6) = 'NORMAL'
,   @sensitivity varchar(12) = 'NORMAL'
,   @file_attachments nvarchar(max) = NULL
,   @query nvarchar(max) = N'
SELECT
    O.object_name AS FailingPackageName
,   O.object_id
,   O.caller_name
,   O.server_name
,   O.operation_id
,   OM.message_time
,   EM.message_desc
,   D.message_source_desc
,   OM.message
FROM
    SSISDB.catalog.operation_messages AS OM
    INNER JOIN
        SSISDB.catalog.operations AS O
        ON O.operation_id = OM.operation_id
    INNER JOIN
    (
        VALUES
            (-1,''Unknown'')
        ,   (120,''Error'')
        ,   (110,''Warning'')
        ,   (70,''Information'')
        ,   (10,''Pre-validate'')
        ,   (20,''Post-validate'')
        ,   (30,''Pre-execute'')
        ,   (40,''Post-execute'')
        ,   (60,''Progress'')
        ,   (50,''StatusChange'')
        ,   (100,''QueryCancel'')
        ,   (130,''TaskFailed'')
        ,   (90,''Diagnostic'')
        ,   (200,''Custom'')
        ,   (140,''DiagnosticEx Whenever an Execute Package task executes a child package, it logs this event. The event message consists of the parameter values passed to child packages.  The value of the message column for DiagnosticEx is XML text.'')
        ,   (400,''NonDiagnostic'')
        ,   (80,''VariableValueChanged'')
    ) EM (message_type, message_desc)
        ON EM.message_type = OM.message_type
    INNER JOIN
    (
        VALUES
            (10,''Entry APIs, such as T-SQL and CLR Stored procedures'')
        ,   (20,''External process used to run package (ISServerExec.exe)'')
        ,   (30,''Package-level objects'')
        ,   (40,''Control Flow tasks'')
        ,   (50,''Control Flow containers'')
        ,   (60,''Data Flow task'')
    ) D (message_source_type, message_source_desc)
        ON D.message_source_type = OM.message_source_type
WHERE
    OM.operation_id =
    (
        SELECT
            MAX(OM.operation_id)
        FROM
            SSISDB.catalog.operation_messages AS OM
        WHERE
            OM.message_type = 120
    )
    AND OM.message_type IN (120, 130);
'
,   @execute_query_database sysname = NULL
,   @attach_query_result_as_file bit = 0
,   @query_attachment_filename nvarchar(260) = NULL
,   @query_result_header bit = 1
,   @query_result_width int = 256
,   @query_result_separator char(1) = char(13)
,   @exclude_query_output bit  = 0
,   @append_query_error bit = 0
,   @query_no_truncate bit = 0
,   @query_result_no_padding bit = 0
,   @mailitem_id int = NULL
,   @from_address varchar(max) = NULL
,   @reply_to varchar(max) = NULL;

-- Send email about the failure
EXECUTE msdb.dbo.sp_send_dbmail
    @profile_name
,   @recipients
,   @copy_recipients
,   @blind_copy_recipients
,   @subject
,   @body
,   @body_format
,   @importance
,   @sensitivity
,   @file_attachments
,   @query
,   @execute_query_database
,   @attach_query_result_as_file
,   @query_attachment_filename
,   @query_result_header
,   @query_result_width
,   @query_result_separator
,   @exclude_query_output
,   @append_query_error
,   @query_no_truncate
,   @query_result_no_padding
,   @mailitem_id OUTPUT
,   @from_address
,   @reply_to;

Adjust as you like


Use system timestamp comparison in SELECT query I have to backup some tables on a daily basis using the condition that the backup will be only for one previous day. I am planning to use copy to dump the records in to a csv file. I need help in getting my query correct, below is the query I am using but does not seem to work:

psql -d my_db -c 'copy (select * from mytab WHERE date_trunc('day',NOW() - interval '1 day') TO STDOUT' -o data1.copy;

The WHERE expression does not make sense:
WHERE date_trunc('day',NOW() - interval '1 day'

It has to evaluate to a boolean value and you need to compare to an actual column of the table like (assuming a timestamp , timestamptz or date column):
WHERE my_timestamp_column >= (NOW()::date - 1)
AND   my_timestamp_column <   NOW()::date

Postgres does not store a creation timestamp for table rows automatically. You need to add a column yourself to keep track (with default value and / or a trigger to make sure). Detailed instructions with code in this related answer:

Tables accessed during last period

"Commit timestamps" in Postgres 9.5 or later
You can activate the track_commit_timestamp setting to start tracking. Then you can use pg_xact_commit_timestamp(xmin) to retrieve the timestamp when each row was inserted or updated (after you started tracking). To get all rows that were inserted or updated within the last day:
SELECT * FROM mytab
WHERE  pg_xact_commit_timestamp(xmin) > now() - interval '1 day'

Certain limitations apply. Commit timestamps are not kept indefinitely. Read the Postgres Wiki for details.
Once activated, commit timestamps are tracked for all transactions in the DB cluster, which causes a performance hit. If you only need it for one or a few tables, rather add timestamps to involved table(s) manually like instructed above.


Color syntax printing in SSMS 2012 It seems the SSMS of Sql server 2012 cannot print the SQL statements with syntax color anymore. Is it a bug? Does it have any workaround?

The color printing extension for Visual Studio 2010 works just fine for adding the color print ability to SSMS 2012. Download the VSIX package, and unzip into it's own directory in your to your SSMS extensions folder (C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Extensions most likely). You'll then want to modify the extension.vsixmanifest file -- in the <SupportedProducts> section add

      <IsolatedShell Version="1.0">ssms</IsolatedShell>

After that, restart SSMS, and you'll have the ability to print in color, even if it doesn't look like it.


sys.objects column [Type] weird value 'ST' I am seeing a strange (not-documented) value for [Type] column in sys.objects. The value is "ST" as shown in the below (note, dbo.Record is a user table)

Does anyone know what this 'ST' value means? (This is in SQL Server 2014 Developer Edition)

Well, I don't know what ST stands for, but based on the naming convention I would have to guess it's some kind of system-generated statistic.

You could check to see if there is, by chance, some kind of text associated with the object if it is a module, e.g.:

SELECT OBJECT_DEFINITION(613577224);

SELECT definition FROM sys.all_sql_modules
  WHERE object_id = 613577224;

You could also try:

EXEC sp_help N'dbo._ST_OEA33...';

Finally, you could try to brute force discover this object in any of the catalog views. This script tries to find any view that contains a row where the object_id column is equal to that value.

CREATE TABLE #v(v sysname);

DECLARE @sql nvarchar(max) = N'', @obj int = 613577224;

SELECT @sql += N'INSERT #v
    SELECT DISTINCT ''sys.' + name + N'''
      FROM sys.' + QUOTENAME(name) + N'
      WHERE [object_id] = @obj;'
  FROM sys.all_views AS v
  WHERE EXISTS
  (
    SELECT 1
      FROM sys.all_columns AS c
      WHERE c.name = N'object_id'
      AND c.[object_id] = v.[object_id]
  );

EXEC sys.sp_executesql @sql, N'@obj int', @obj;

SELECT v FROM #v;

DROP TABLE #v;

If this yields nothing, you could expand it to include all int-based columns across all catalog views, since sometimes object_id values are stored in columns with different names, like referenced_major_id or parent_object_id, and any results here might yield clues as well.

SET NOCOUNT ON;

CREATE TABLE #v(v sysname);

DECLARE @sql nvarchar(max) = N'', @obj int = 613577224;

SELECT @sql += N'INSERT #v
    SELECT DISTINCT ''sys.' + v.name + N'''
      FROM sys.' + QUOTENAME(v.name) + N'
      WHERE ' + QUOTENAME(c.name) + N' = @obj;'
  FROM sys.all_views AS v
  INNER JOIN sys.all_columns AS c
  ON v.[object_id] = c.[object_id]
  WHERE v.[schema_id] = 4
    AND c.[system_type_id] IN (56,127)
    AND v.name NOT IN (N'syscolumns')
    AND v.name NOT LIKE N'dm_fts_%';

EXEC sys.sp_executesql @sql, N'@obj int', @obj;

SELECT v FROM #v;

GO
DROP TABLE #v;


Error while importing a ".sql" file in database I am trying to import a .sql file in to my local database. whenever i type the command to import the file, I get an error ERROR at line 1: Unknown command '\S'.

This is the first line of the sql file update product set aka = 'Trojan.gen,Backdoor.Win32.Agent.bisn' where id = 156701

Since you have an ASCII file, the script could be giving problems because it is came from a Windows machine and you are attempting to load it into mysql on a Linux machine.

You may want to try one of two things:

Using the mysql client in Windows, load the data remotely.
Use sql client in raw mode in one of two ways

mysql -hIP -r -uroot -p DN_name < filename.sql
mysql -hIP -B -uroot -p DN_name < filename.sql


ORM-style server-side programming languages (OO replacement for PL/SQL?) Good evening,

Is there an Object Oriented replacement for PL/SQL, allowing server side procedures to be written [and then called client or server side]?

(for MySQL, PostgreSQL, Oracle  or etc.)

Oracle has Java server-side, but I'd caution that it is not a replacement for PL/SQL. There is no better language than PL/SQL for manipulating data stored in the database - Java may be appropriate for computationally intensive business logic.

For postgres,

  There are currently four procedural languages available in the standard PostgreSQL distribution: PL/pgSQL (Chapter 39), PL/Tcl (Chapter 40), PL/Perl (Chapter 41), and PL/Python (Chapter 42). There are additional procedural languages available that are not included in the core distribution

But I think it is fair to say that PL/pgSQL is the most mature and feature complete (and, you may be sorry to hear, very similar to PL/SQL).

I don't know much about MySQL stored procedures but here is a link to the docs


How to define partition according to Date? I am currently using MySQL 5.6.

In my database, I have a log table called paramlog_ems. It has 2M rows

I want like to partition this log table according to Date.

Here is the table structure:

CREATE TABLE paramlog_ems (
  SiteIndex smallint(5) unsigned NOT NULL,
  RegionIndex smallint(5) unsigned NOT NULL,
  OrganizationIndex smallint(5) unsigned NOT NULL,
  DeviceSlaveId smallint(5) unsigned NOT NULL,
  UserIndex smallint(5) unsigned NOT NULL,
  ParameterID smallint(5) unsigned NOT NULL,
  ParamValue double(20,10) NOT NULL,
  DeviceName varchar(50) NOT NULL,
  LogDate datetime NOT NULL,
  Category tinyint(3) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I want to do this using MySQL Workbench.

Here are the Steps to Partition Your Table by Month

Create a Temp Table, Partitioned and Indexed on LogDate

CREATE TABLE paramlog_ems_new (
  SiteIndex smallint(5) unsigned NOT NULL,
  RegionIndex smallint(5) unsigned NOT NULL,
  OrganizationIndex smallint(5) unsigned NOT NULL,
  DeviceSlaveId smallint(5) unsigned NOT NULL,
  UserIndex smallint(5) unsigned NOT NULL,
  ParameterID smallint(5) unsigned NOT NULL,
  ParamValue double(20,10) NOT NULL,
  DeviceName varchar(50) NOT NULL,
  LogDate datetime NOT NULL,
  Category tinyint(3) NOT NULL,
  KEY LogDate (LogDate)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
PARTITION BY RANGE COLUMNS(`LogDate`)
(PARTITION p00000000000 VALUES LESS THAN ('2015-01-01') ENGINE = MyISAM,
 PARTITION p2015_01_jan VALUES LESS THAN ('2015-02-01') ENGINE = MyISAM,
 PARTITION p2015_02_feb VALUES LESS THAN ('2015-03-01') ENGINE = MyISAM,
 PARTITION p2015_03_mar VALUES LESS THAN ('2015-04-01') ENGINE = MyISAM,
 PARTITION p2015_04_apr VALUES LESS THAN ('2015-05-01') ENGINE = MyISAM,
 PARTITION p2015_05_may VALUES LESS THAN ('2015-06-01') ENGINE = MyISAM,
 PARTITION p2015_06_jun VALUES LESS THAN ('2015-07-01') ENGINE = MyISAM,
 PARTITION p2015_07_jul VALUES LESS THAN ('2015-08-01') ENGINE = MyISAM,
 PARTITION p2015_08_aug VALUES LESS THAN ('2015-09-01') ENGINE = MyISAM,
 PARTITION p2015_09_sep VALUES LESS THAN ('2015-10-01') ENGINE = MyISAM,
 PARTITION p2015_10_oct VALUES LESS THAN ('2015-11-01') ENGINE = MyISAM,
 PARTITION p2015_11_nov VALUES LESS THAN ('2015-12-01') ENGINE = MyISAM,
 PARTITION p2015_12_dec VALUES LESS THAN ('2016-01-01') ENGINE = MyISAM,
 PARTITION p99999999999 VALUES LESS THAN (MAXVALUE) ENGINE = MyISAM);

Load the Temp Table

INSERT INTO paramlog_ems_new SELECT * FROM paramlog_ems;

Swap the Temp Table

ALTER TABLE paramlog_ems RENAME paramlog_ems_old;
ALTER TABLE paramlog_ems_new RENAME paramlog_ems;

GIVE IT A TRY !!!


Postgresql - get relation name from relid or RelOptInfo data structure I want to get the relation name for a given relid or RelOptInfo data structure.

For some experiment I need to fetch the relation name while processing a given query inside postgresql. is there some way of doing this?

You want RelationGetRelationName(Relation).

If you don't have a Relation, use get_rel_name(Oid). This uses the syscache so you must have an open transaction. Beware though, it doesn't schema-qualify the name. If you need that, you should probably do a syscache lookup yourself (see the code for get_rel_name in src/backend/utils/cache/lsyscache.c) so you can also get_namespace_name the schema name. Then use quote_qualified_identifier.

I am constantly surprised that Pg doesn't seem to have a public C-level equivalent of the regclass formatting in regclassout. See src/backend/utils/adt/regproc.c.

There's generate_qualified_relation_name, but it's static for some reason. I think I'll submit a patch proposing to move it, generate_relation_name  and get_relation_name to lsyscache.c and make them public.

In the mean time I suggest shameless ripping off what generate_relation_name or generate_qualified_relation_name does if you're doing something where get_rel_name isn't good enough.

Update: patch submitted. Feel free to shamelessly rip off that function.


Updating data from a report: any potential pitfalls? Question:

In general, are there any pitfalls with updating data from a SSRS report?*

Context:

More specific, I'm talking about this context:

SSRS 2008 (with the option to upgrade, if needed).
MSSQL 2008 (with the option to upgrade, if needed).
Web.forms ReportViewer 2010 in our own ASP.NET application.

I only need to do a minor DB update. The user can tick a checkbox parameter "I'm going to print this" which will do a small update or insert to mark the report has been run at a specific point in time. The report will only show items were added since the previous "print" run, so I filter in my WHERE clause based on that, giving the user only "new" items.

The reason I'm worried is because it doesn't "feel right" to do updates from a tool that's meant primarily for viewing data. On the other hand, with something as small as above it seems like the easiest solution.

Specific concerns:

Here are some specific concerns I have about which I'm seeking insight:

Security: the report's connection will need to be run with a user that's allowed to do inserts, where a more sensible default would be to run it for a select-only user? Are there known sql-injection-like attacks for SSRS?
Usability: I'd like to do the update depending on whether a user ticks a checkbox. I'm not sure how well this would play with refreshes, and if you can detect what the rendering format is while doing the queries (which would allow me to make the checkbox trigger updates only on -e.g.- a PDF export).
Robustness: update statements seem more prone to exceptions (violating constraints, etc). Not sure if there's an elegant way to prevent, log, track, and debug that kind of problem.

Honestly, don't do it. Yes it is possible to do it by declaring a global variable to your report and then passing that to the statement that generates the result set to do some processing before returning data but it is messy and ill-advised at best. What is to say that in the future you won't want to add more variables to the report? At this point it technically becomes a form - which is what ASP.NET was designed to do - not what SSRS/ReportViewer was designed to do.

You say that you are using Report Viewer in a ASP.NET web application - so my recommendation would be to handle the print logic within the application itself if you can and leave the report to do what it does best. We have an application at our company that produces a report in a similar fashion - and we needed to track who was printing the report and when - we did this by disabling the print functionality within the report and then supplied functionality within the application to render the report to a PDF and then print it. This allowed us to be very flexible in what we track/audit and how the report was generated and where it was printed.

Also, keeping the two pieces of logic separate will be better in the long run - for instance if you need to change the report this could be done external to the application and then you just provide the rdl file and replace the old one. Putting code within the report to change data means extra testing and blurs the boundaries of responsibilities.

Additionally, as you mention above - there is the possibility that data could be compromised one way or another (SQL Injection etc..) and having the application validate the input is by far the correct thing to do - not to mention adding all the extra SQL needed to validate, update and then return data would slow your report down. This may not seem like much if you only have a few users but if your user base is large it could cause issues.

It may seem like the quickest solution right now but that is not necessarily the right solution. Keep it simple and separated and you will find you will have a lot less problems in the long run.

I hope this helps.


Clustered Index taking huge amount of space I have been working on indexing a database dealing with historical data and recently encountered an issue with a clustered index that surprised me.

The database is SQL Server 2018. The table itself deals with account information and is essentially a fact table, it is populated with around 200k records a day and has 200 columns. Currently it is sitting at 68mil records, and before the index it took up around 120GB of space.

Because it is a historical table on account information, and we do not have UUID's set up (which would be a massive effort my boss told me not to spend my time on with this database), I created the primary key + clustered index on the Date field and Account Number with a statement equivalent to:

ALTER TABLE AcctHistory
ADD CONSTRAINT PK_AcctHistory PRIMARY KEY CLUSTERED (Date, Account_Number)

The Date field is a Datetime field (This is a DEV environment that I didn't set up but am now building out. Just know I plan on updating it to a date soon, but I am providing the type in case that is relevant.), the Account_Number field is a bigint.

After running this statement, according to the SSMS disk space by table report, the amount of space the table consumed shot up to 480GB, 4x what it took before the index.

I know indexes take up space, but I have never heard of anything like this. Is this normal or is this indicative of a problem I can solve to save space?

Changing a table from a heap to a clustered table doesn't "grow the data". I.e., the clustered index doesn't magically require more space just because it is organized as a linked list (sorted by some column combination) compared to a heap.

However, things to watch out for:

Internal fragmentation (less than 100% full pages). If you had a low value for fillfactor configured for the instance (sp_configure) or specified a low value in the CREATE INDEX/ALTER TABLE command, then that can explain it. So, check your page fullness using sys_dm_db_index_physical_stats().

If you are comparing apples to oranges. Are you looking at only the clustered index or at the table level (i.e., including all the other indexes). The other indexes (non-clustered, NC) have as a "row-pointer" the RID for a heap, which is 8 byte. The row-pointer from an NC index for a clustered table is the clustering key. In your case it is 16 bytes (it would have been 4 extra bytes for duplicates if it wasn't unique). I.e., the NC indexes will now be a bit bigger than before. Also, then converting the table from a heap to a clustered index, all NC indexes were rebuilt, and I think the fillfactor will be picked up from the server setting. Could that explain it? I very much doubt it, but perhaps in some extreme case... Assuming you include the NC index size when you do the comparison. So, make sure that you are looking at the right thing (I prefer my sp_indexinfo, which gives pretty details output, but anything that you know an trust).

My bet is on an sp_configure for fillfactor which is weirdly low...


Changing passphrase for EncryptByPassPhrase I am using EncryptByPassPhrase to encrypting some columns.

If I want to change the passphrase in the future, how can I change it?

You'll need to decrypt the data using the existing password then encrypt the value using the new password.


Count movies with specific score, per actor I have movies and actors tables. Movies have a rating from 0 to 10. Each actor belongs to a group (table groups). Movies and actors are linked through movies_actors table.

For analytics purposes, I need the number of movies with rating between 0 and 1 (the upper bound excluded, named count0), between 1 and 2 (count1), ..., for every actor in a specific group.

Example of expected result:

actor_id | count0 | count1 | count2 | count3 | count4 | count5 | count6 | count7 | count8 | count9 |
---------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|
       1 |      0 |      1 |      0 |      0 |      1 |      0 |      1 |      0 |      0 |      0 |
       3 |      1 |      0 |      0 |      0 |      1 |      0 |      0 |      0 |      0 |      0 |

sqlfiddle here

As you can see in the sqlfiddle, I achieved to get count0, count1, ... for a specific actor whose id is hardcoded into the query.

How do I get this information for every actor in a specific group?

Note: I use Postgres 9.4.1 with Ruby on Rails 5.

You can group by actor_id to get the desired results. You might want to join groups in as well if you want to filter by a column in there. (sqlfiddle)

SELECT
    actor_id,
    COUNT(CASE WHEN score >= 0 and score <= 1 THEN 1 END) count0,
    COUNT(CASE WHEN score > 1 and score  <= 2 THEN 1 END) count1,
    COUNT(CASE WHEN score > 2 and score  <= 3 THEN 1 END) count2,
    COUNT(CASE WHEN score > 3 and score  <= 4 THEN 1 END) count3,
    COUNT(CASE WHEN score > 4 and score  <= 5 THEN 1 END) count4,
    COUNT(CASE WHEN score > 5 and score  <= 6 THEN 1 END) count5,
    COUNT(CASE WHEN score > 6 and score  <= 7 THEN 1 END) count6,
    COUNT(CASE WHEN score > 7 and score  <= 8 THEN 1 END) count7,
    COUNT(CASE WHEN score > 8 and score  <= 9 THEN 1 END) count8,
    COUNT(CASE WHEN score > 9 and score  <= 10 THEN 1 END) count9
FROM movies
 INNER JOIN movies_actors ON movies_actors.movie_id = movies.id
 INNER JOIN actors ON movies_actors.actor_id = actors.id
WHERE actors.group_id = 1
GROUP BY actor_id


psql: FATAL: role "postgres" does not exist Running PostgreSQL server on Void Linux. After installing ran initdb as OS user 'postgres':

[user@host]$ sudo -u postgres -i

$ initdb -D '/var/lib/postgresql/data'

Received output:

  creating directory /var/lib/postgresql/data ... ok creating
  subdirectories ... ok selecting default max_connections ... 100
  selecting default shared_buffers ... 128MB selecting dynamic shared
  memory implementation ... posix creating configuration files ... ok
  running bootstrap script ... ok performing post-bootstrap
  initialization ... locale: Cannot set LC_MESSAGES to default locale:
  No such file or directory ok syncing data to disk ... ok

  WARNING: enabling "trust" authentication for local connections You can
  change this by editing pg_hba.conf or using the option -A, or
  --auth-local and --auth-host, the next time you run initdb.

  Success. You can now start the database server using:
  pg_ctl -D /var/lib/postgresql/data -l logfile start

I've then proceeded to create the service, grant the ownership to 'postgres' and start it:

[user@host]$ ln -s /etc/sv/postgresql /var/service
[user@host]$ sudo chown postgres: /var/service/postgresql
[user@host]$ sudo sv start postgresql
[user@host]$ ls -l /var/service/postgresql
lrwxrwxrwx 1 postgres postgres 18 nov   2 17:05 /var/service/postgresql -> /etc/sv/postgresql

Running:

[user@host]$ sudo -u postgres psql ...

will give me the following error:

psql: FATAL:  role "postgres" does not exist

I've tried granting ownership of /etc/sv/postgresql to 'postgres', but no luck.

These were my sources:

https://wiki.voidlinux.eu/PostgreSQL

PostgreSQL error: Fatal: role “username” does not exist

I do not know what to do next.

EDIT: Stumbled upon psql: FATAL: role “postgres” does not exist. Cannot run psql -l because I get the same error, no matter which user I run psql as.

Not a full answer, but consider the manual about initdb here:

-Uusername
--username=username

  Selects the user name of the database superuser. This defaults to the
  name of the effective user running initdb. It is really not important
  what the superuser's name is, but one might choose to keep the
  customary name postgres, even if the operating system user's name is different.

Meaning ...

When running initdb as OS user postgres, the initial DB superuser role is postgres as well - in standard Postgres. I have heard of systems bending this rule ...
You should be able to force the name postgres with --username=postgres for the initial superuser - unless your package seriously messed with all this.


Cummulative Sum with Reset Value Consider the following table:

ID | GROUP_ID | ORDER_VAL | RESET_VAL | VAL
---+----------+-----------+-----------+-----
1  | 1        | 1         | (null)    | 3
2  | 1        | 2         | (null)    | 2
3  | 1        | 3         | (null)    | 1
4  | 1        | 4         | 4         | 2
5  | 1        | 5         | (null)    | 1
6  | 2        | 1         | (null)    | 4
7  | 2        | 2         | 2         | 3
8  | 2        | 3         | (null)    | 4
9  | 2        | 4         | (null)    | 2
10 | 2        | 5         | (null)    | 2
11 | 2        | 6         | (null)    | 4
12 | 2        | 7         | 14        | 2
13 | 2        | 8         | (null)    | 2

For each row, I need to compute the cumulative sum of VAL for all previous rows (ordered by ORDER_VAL and grouped by GROUP_ID), but each time a non-NULL RESET_VAL is encountered, I need to use that value for the sum. The rows that follow also need to build on top of the RESET_VAL instead of using the actual sum. Note that each group can have multiple reset values.

This is the result I expect for the above table:

ID | GROUP_ID | ORDER_VAL | RESET_VAL | VAL | CUMSUM
---+----------+-----------+-----------+-----+-------
1  | 1        | 1         | (null)    | 3   | 0
2  | 1        | 2         | (null)    | 2   | 3
3  | 1        | 3         | (null)    | 1   | 5
4  | 1        | 4         | 4         | 2   | 4
5  | 1        | 5         | (null)    | 1   | 6
6  | 2        | 1         | (null)    | 4   | 0
7  | 2        | 2         | 2         | 3   | 2
8  | 2        | 3         | (null)    | 4   | 5
9  | 2        | 4         | (null)    | 2   | 9
10 | 2        | 5         | (null)    | 2   | 11
11 | 2        | 6         | (null)    | 4   | 13
12 | 2        | 7         | 14        | 2   | 14
13 | 2        | 8         | (null)    | 2   | 16

If not for the reset values, I could use a window query:

SELECT temp.*,
       COALESCE(SUM(val) OVER (PARTITION BY group_id ORDER BY order_val ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),
                0) AS cumsum
FROM temp;

SQLFiddle of the above

I was originally mistakenly thinking that I could just put RESET_VAL at the beginning of the COALESCE, but this won't work since it won't reset the value for following rows.

I also tried this solution, but it only resets back to zero, not to a value in the column. Adapting it to do so is proving non-trivial because that value must propagate to all following rows.

A recursive query seems like a natural fit, but I haven't been able to figure out how to do that as of yet.

I should probably mention that the table I actually have to deal with is much larger (hundreds of thousands to a couple million rows) than the example above, so please mention if there are any performance pitfalls with any answer.

The following works but there's probably some more clever version. Explanation of the query's logic:

We first find how many "resets" have been done up to and including the current row by counting the not nulls of the reset_val column, so we can separate the rows into subgroups.

We also use another window function LAST_VALUE() with IGNORE NULLS, so we can find the last reset_value.

Note that both these window functions COUNT() and LAST_VALUE() have an ORDER BY, therefore the default window ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Omitted in the query, to make the code more clear.

Assuming that the val is not nullable, the other window function could also be shortened, from:

       COALESCE(SUM(val) OVER
         (PARTITION BY group_id, reset_count
          ORDER BY order_val
          ROWS BETWEEN UNBOUNDED PRECEDING
                   AND 1 PRECEDING), 0)

(avoiding the COALESCE() as well) to:

       SUM(val) OVER
         (PARTITION BY group_id, reset_count
          ORDER BY order_val)
       - val

Finally, in the second cte, we use the above found subgroups (using PARTITION BY group_id, reset_count) to find the cumulative sums.

WITH x AS
  ( SELECT temp.*,
           COUNT(reset_val) OVER
               (PARTITION BY group_id
                ORDER BY order_val)
             AS reset_count,
           COALESCE(LAST_VALUE(reset_val IGNORE NULLS) OVER
               (PARTITION BY group_id
                ORDER BY order_val), 0)
             AS reset_value
    FROM temp
  ) ,
y AS
  ( SELECT x.*,
           COALESCE(SUM(val) OVER
             (PARTITION BY group_id, reset_count
              ORDER BY order_val
              ROWS BETWEEN UNBOUNDED PRECEDING
                       AND 1 PRECEDING), 0)
           + reset_value AS cumsum
    FROM x
  )
SELECT *
FROM y ;

Test at SQLfiddle.

Another variation, based on @Chris' recursive answer. (slightly improved, works with non-consecutive order_val, avoids the final GROUP BY).
Also works in case the first row of a group has a reset_val:

WITH row_nums AS
  ( SELECT id, group_id, order_val, reset_val, val,
           ROW_NUMBER() OVER (PARTITION BY group_id
                              ORDER BY order_val)
             AS rn
    FROM temp
  ) ,
updated_temp (id, group_id, order_val, reset_val, val, rn, cumsum) AS
  ( SELECT id, group_id, order_val, reset_val, val, rn,
           COALESCE(reset_val, 0)
    FROM row_nums
    WHERE rn = 1
  UNION ALL
    SELECT curr.id, curr.group_id, curr.order_val, curr.reset_val, curr.val, curr.rn,
           COALESCE(curr.reset_val, prev.val + prev.cumsum)
    FROM row_nums  curr
      JOIN updated_temp  prev
        ON  curr.rn-1 = prev.rn
        AND curr.group_id = prev.group_id
  )
SELECT id, group_id, order_val, reset_val, val, cumsum
FROM updated_temp
ORDER BY group_id, order_val ;

Test at SQLfiddle-2.

One more variation, using the older (proprietary) CONNECT BY syntax for recursive queries. More compact but I find it more difficult to write and read than the CTE version:

WITH row_nums AS
  ( SELECT id, group_id, order_val, reset_val, val,
           ROW_NUMBER() OVER (PARTITION BY group_id
                              ORDER BY order_val)
             AS rn,
           COALESCE(reset_val, 0) AS cumsum
    FROM temp
  )
SELECT id, group_id, order_val, reset_val, val, rn,
       COALESCE(reset_val, PRIOR val + PRIOR cumsum, 0) AS cumsum
FROM row_nums
START WITH rn = 1 OR reset_val IS NOT NULL
CONNECT BY  rn-1 = PRIOR rn
        AND group_id = PRIOR group_id
        AND reset_val IS NULL
ORDER BY group_id, order_val ;

Tested at SQLfiddle-3.


How can I enter Mongo as a superuser or reset users? I'm was playing around with permissions and locked myself out of Mongo database.  I'm pretty sure I did this by trying to explicitly add access to a database but instead I overwrote only allowing permission to the database. So I'm effectively locked out of my Mongo database and everything I read tells me how to create a super user if I have the add user privilege.  Right now I don't think I have any users that have that privilege.  Is there a way to enter the database as all access?  I own the server and have root access.

If you have locked yourself out then you need to do the following:

Stop your MongoDB instance
Remove the --auth and/or --keyfile options from your MongoDB config to disable authentication
Start the instance without authentication
Edit the users as needed
Restart the instance with authentication enabled


SELECT showing two COUNTs I had this table:

tblA

id | name | value
1    nameA    1
2    nameA    0
3    nameB    0
4    nameC    0
5    nameC    1
6    nameC    1

As you see, Value can be 1 or 0

I need to perform a query that shows this for every nameX: nameX, count_1's, total_rows,

name  | 1's | total
nameA    1      2
nameB    0      1
nameC    2      3

I know that i can use GROUP by and COUNT.
But don't see how to put the "total_rows" in same query.

This will give me name and number of 1's.

SELECT name, value FROM tblA WHERE value = "1" GROUP BY name

How to add the last column i need?

Thanks

For a table called cristian, here is the needed query:

select name,SUM(value) "1s" ,SUM(1) "total"
FROM cristian GROUP BY name;

Here is your sample data:

mysql> use test
Database changed
mysql> drop table cristian;
Query OK, 0 rows affected (0.01 sec)

mysql> create table cristian
    -> (
    ->     id int not null auto_increment primary key,
    ->     name varchar(25),value int not null
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.11 sec)

mysql> insert into cristian (name,value) values
    -> ('nameA',1),('nameA',0),('nameB',0),
    -> ('nameC',0),('nameC',1),('nameC',1);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from cristian;
+----+-------+-------+
| id | name  | value |
+----+-------+-------+
|  1 | nameA |     1 |
|  2 | nameA |     0 |
|  3 | nameB |     0 |
|  4 | nameC |     0 |
|  5 | nameC |     1 |
|  6 | nameC |     1 |
+----+-------+-------+
6 rows in set (0.00 sec)

and here is the query's output

mysql> select name,SUM(value) "1s" ,SUM(1) "total"
    -> FROM cristian GROUP BY name;
+-------+------+-------+
| name  | 1s   | total |
+-------+------+-------+
| nameA |    1 |     2 |
| nameB |    0 |     1 |
| nameC |    2 |     3 |
+-------+------+-------+
3 rows in set (0.14 sec)

mysql>

These would also work

select name,SUM(value) "1s" ,COUNT(1) "total"
FROM cristian GROUP BY name;

select name,SUM(IF(value=1,1,0)) "1s" ,COUNT(1) "total"
FROM cristian GROUP BY name;

Give it a Try !!!


procedure not returning desired result I am trying to generate a report from this procedure
create procedure [dbo].[hstatement]
(
    @fromdate date,
    @todate date,
    @prd varchar(8)
)
as
begin
    create table #tempdata
    (
        LBrCode         int,
        prdCode         varchar(8),
        bal_date        datetime,
        bal_amt         numeric(18,2)
    )

    while @fromdate<@todate
    begin
        insert into #tempdata
        select  LBrCode,
                substring(PrdAcctId,1,8) prdcd,
                convert(varchar(12),@fromdate,106) tdt,
                Balance4
        from
        (
            select LBrCode,
                   PrdAcctId,
                   CblDate dt,
                   Balance4,
                   rank() over (partition by LBrCode,PrdAcctId order by CblDate desc ) rnk
            from cl_balance
            where rtrim(substring(PrdAcctId,1,8)) in (@prd)
                  and floor(cast(substring(PrdAcctId,17,8) as int))=0
                  and CblDate<=@fromdate
        ) x
        where rnk= 1
              and LBrCode=1;

        set @fromdate=dateadd(day,1,@fromdate)
end;

The procedure is compiled successfully.
However, I am expecting a result as each date is between fromdate and todate, but I am getting only the first record.

In this section of your procedure:
where rtrim(substring(PrdAcctId,1,8)) in (@prd)
              and floor(cast(substring(PrdAcctId,17,8) as int))=0
              and CblDate<=@fromdate

you are using the @fromdate as the filter and looking for the values earlier.  Then you increase the @fromdate value.  But the earliest date before Wednesday of the current week is still the same when I get the earliest date before Thursday of the current week - so wouldn't you end up with the same record?
I also assume there is more to the procedure as you only insert into the temp table and never query it?   What is the remainder of the procedure?  Please post the entire code.   Do you get multiple records with the same value as the output?


Is there a set based way to load/read a tree branch with HierarchyId I am playing around with HierarchyId, and I have not figured out a set based way to do the following:

insert all subtree at once
retrieve all subtree at once

This question is related to my previous one, and I suspect the only way to accomplish these two tasks with HierarchyId is one node or one level at a time. If I am using materialized path, both actions are easily accomplished by a single (and trivial) set based command.

What am I missing?

Edit: I also missed a way to move a subtree, but I learned it from  Mikael Eriksson's comment

The function to use is GetReparentedValue but when using only GetReparentedValue the tree may end up in an "inconsistent" state.

Here is some code provided by Microsoft that takes care of that. Moving subtrees.

I guess that related to this is Enforcing a tree. It uses a calculated column for parent id that does a self join to the PK.


Export just rows modified in last month (date defined by user) I'm asking if it's possible in MySQL to export just rows modified in a period of time for example in the past month, if it is possible, how?

This would depend on the table having a timestamp column.
mysqldump has a --where option that you can define when dumping a specific table
EXAMPLE
If you have a table called mydb.mytab defined as follows
CREATE TABLE mytable
(
    id INT NOT NULL AUTO_INCREMENT,
    tm TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

You could specify the date range on that table as follows (for June 2013):
WHERE_CLAUSE="(tm >= '2013-06-01 00:00:00') AND"
WHERE_CLAUSE="${WHERE_CLAUSE} (tm < '2013-07-01 00:00:00')"
mysqldump -uroot -p mydb mytable --where="${WHERE_CLAUSE}"

This would be most helpful since timestamps can be updated automatically.
I wrote a post on Aug 15, 2011 on how to use --where to dump a subset of data not based on a timestamp : Is it possible to mysqldump a subset of a database required to reproduce a query?
If you do not have a timestamp column in the desired tables, you are better of just keeping all binary logs generated during any given month.


SQL Server Core - Installed SQL Server features When using a traditional installation of SQL Server one has the Installed SQL Server features discovery report.

Googled and found no info if some equivalent was available for the core installation. It outputs to HTML so that is another reason why I believe it is not available (though one could still type its text).

So basically, I'd like to know if there is an equivalent on the core version, or what powershell (or command line) could be run to get the same info (version, installed features)?

One way of doing that would be to query the registry location containing all currently installed programs and then filter out all that do not contain SQL SERVER in the name.

$array = @()

$computername="MachineName"

#Define the variable to hold the location of Currently Installed Programs

$UninstallKey="SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Uninstall"

#Create an instance of the Registry Object and open the HKLM base key

$reg=[microsoft.win32.registrykey]::OpenRemoteBaseKey('LocalMachine',$computername)

#Drill down into the Uninstall key using the OpenSubKey Method

$regkey=$reg.OpenSubKey($UninstallKey)

#Retrieve an array of string that contain all the subkey names

$subkeys=$regkey.GetSubKeyNames()

#Open each Subkey and use GetValue Method to return the required values for each

foreach($key in $subkeys){

    $thisKey=$UninstallKey+"\\"+$key

    $thisSubKey=$reg.OpenSubKey($thisKey)

    $obj = New-Object PSObject

    $obj | Add-Member -MemberType NoteProperty -Name "ComputerName" -Value $computername

    $obj | Add-Member -MemberType NoteProperty -Name "DisplayName" -Value $($thisSubKey.GetValue("DisplayName"))

    $obj | Add-Member -MemberType NoteProperty -Name "DisplayVersion" -Value $($thisSubKey.GetValue("DisplayVersion"))

    $obj | Add-Member -MemberType NoteProperty -Name "InstallLocation" -Value $($thisSubKey.GetValue("InstallLocation"))

    $obj | Add-Member -MemberType NoteProperty -Name "Publisher" -Value $($thisSubKey.GetValue("Publisher"))

    $array += $obj

}

$array | Where-Object { $_.DisplayName -like "*SQL SERVER*" } | select ComputerName, DisplayName, DisplayVersion, Publisher | ft -auto

This script was taken from the Hey, Scripting Guy! Blog and I modified to include -like "*SQL SERVER*" and also removed the loop through multiple computers. There may be an easier way. I hope this helps.


Permission Denied for Foreign Server I'm trying to set up a user with limited permissions that would be able to create foreign tables. I have two databases, hr_db and accounting_db. I have created an hr_user user for hr_db and a accounting_user user for accounting_db. I only want the accounting_user user to have select rights on some hr_db tables, such as the users table. To do this, as a superuser I went to the hr_db database and ran:

GRANT CONNECT ON DATABASE hr_db TO accounting_user;
GRANT SELECT ON people TO accounting_user;

I set up a connection to hr_db from accounting_db using a foreign data wrapper:

CREATE SERVER hr_db FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'localhost', dbname 'hr_db', port '5432');

I then added a mapping for the accounting_user user:

CREATE USER MAPPING FOR accounting_user SERVER hr_db
OPTIONS (user 'accounting_user', password 'secretpassword');

The password for accounting_user is the same as the one I use to log in from the command line. This works fine:

psql -U accounting_user -W hr_db
[enter accounting_user password]
SELECT * FROM people LIMIT 10;

I can create a regular table in the accounting_db database as the accounting_user user:

psql -U accounting_user -W accounting_db
[enter accounting_user password]
CREATE TABLE test (person_id integer NOT NULL);
DROP TABLE test;

But if I try to create a foreign table:

CREATE FOREIGN TABLE hr_people (person_id integer NOT NULL)
SERVER hr_db OPTIONS (table_name 'people');
ERROR:  permission denied for foreign server hr_db

As a superuser, I can create the hr_people foreign table and the accounting_user will have access to it. So the foreign data connection to hr_db seems correct. What else do I need to give the accounting_user in order for it to be able to create and drop foreign tables?

To grant permissions for the foreign server:

GRANT USAGE ON FOREIGN SERVER hr_db TO accounting_user;

More details available in the example on official page https://www.postgresql.org/docs/9.6/static/contrib-dblink-connect.html


Get ID of row in aggregate I have the following query to get me the values corresponding with the latest date:

SELECT MAX(RowAddedDate), X, Y
FROM dbo.MyTable
GROUP BY X, Y

This is fine, but I need to get the ID of each row in this query.  If I add the ID though, I get everything as the ID needs to be in the GROUP BY.

How do I solve this?

You can use window (ranking). functions for this:

WITH ct AS
  ( SELECT X, Y, RowAddedDate, ID,
           Rn = DENSE_RANK() OVER (PARTITION BY X, Y
                                   ORDER BY RowAddedDate DESC)
    FROM dbo.MyTable
  )
sELECT X, Y, RowAddedDate, ID
FROM ct
WHERE Rn = 1 ;

If multiple rows have the same date with same X and Y, all of them will be returned due to the DENSE_RANK().

If you want only one, you can replace it with ROW_NUMBER() and adjust the ORDER BY to control which of the tied rows will be returned.

As for efficiency, an index on (X, Y, RowAddedDate) INCLUDE (ID) would help.


Equivalent of SQL Not Exists in MDX (or finding another way to accomplish same result) I am trying to help a contractor implement a cube in Multi-Dimensional SSAS 2017 and do not have very much experience in MDX.

I have a table that looks something like this:

The goal is to only include rows in the query that have the lowest [seq] per each combo of [Part], [CustomerID], and PersonID after filters have been applied, and then sum the value column. So if no filters are applied, only rows 1 and 3 should be returned and the sum of the value column should be 2. But if the user filters data for only the month of June, only rows 2 and 3 should be returned and the sum of the value column should be 1.

We accomplished this in SQL like this:

SELECT SUM(Value)
FROM (SELECT *
 ,ROW_NUMBER() OVER (PARTITION BY CustomerID,PersonID, Part ORDER BY SEQ asc) AS Seq
 FROM Table WHERE DATEKeY BETWEEN @StartDate AND @EndDate
 )A
WHERE Seq=1;

But the contractor is having big problems with performance of the MDX rank function. Not having very much experience with MDX, I am having a hard time knowing what alternatives there are that will perform well and the contractor has not provided any alternatives that match our business need.

Another approach I came up with is this:

SELECT SUM(Value)
FROM [Table]  AS A
WHERE DATEKey BETWEEN @StartDate AND @ENDDate
AND CustomerID=@CustomerID
AND NOT EXISTS(SELECT 1 FROM Table AS B
                WHERE B.Part=A.Part
                  AND B.CustomerID=@CustomerID
                  AND B.PersonID=A.PersonID
                  AND B.SEQ<A.SEQ)

This is actually performing better than the old way in SQL but I have no clue how to implement either this, the previous method, or equivalent logic in MDX that is going to perform well.

I know the MDX will be hard to do without seeing the whole cube but faux code or just some advice on what functions will offer the best performance for this logic would be a big help.

As you say it's hard to try out without your actual cube and it depends a bit on how your dimensions are layed out, but I think you can get there using the BOTTOMCOUNT function if you create  a measure on seq.

  BottomCount(Set_Expression, Count [,Numeric_Expression])

Your set_expression could be a crossjoin between the dimensions you care about and you could create a dynamic set based on that expression.

Something along the lines of

WITH DYNAMIC SET LowestSeq AS
BottomCount({Part.Members * Customer.Members * Person.Members})
   , 1
   , [Measures].[seq])

It would help if you published a small repro of your scenario as an XMLA script on pastebin or something. If your dsv is based on named queries producing the data we can easily create the same cube on our machine.


Permanent solution for Fixing buffer size I have installed MySQL 5.7 on windows server 2016 and set the buffer pool size to 4 GB through SQL query but when the server is restarted buffer pool size getting reduced resulting in the slowness of application hosted on it ... need suggestion so as to permanently fix the buffer pool size.....

To find the slowness of the application, we need to dig deeper on many other aspects, such as table design, query, indexes, data type, etc...
To set the innodb_buffer_pool_size value to 4 GB, edit the configuration file my.ini and add the following statement, then restart the MySQL server:
[mysqld]
innodb_buffer_pool_size = 4G

Or you can set it online (restart not required) by running the following statement:
SET GLOBAL innodb_buffer_pool_size = 1024 * 1024 * 1024 * 4;

There are few things to consider, before changing this variable. My blog post contains the detailed explanation on allocating innodb_buffer_pool_size.


Postgres develops server doesn't listen error spontaniously I have been running Postgres 9.3 continuously for days on a windows 7 machine with no problems. This morning I came in to see that Postgres, and every application using my postgres database had crashed. I had been running an Java program in Eclipse that I have run many thousands of times in the past with no problems, the program makes new tables based on other tables in the database, my log suggests the program ran ok and produced its result as normal.

Now when I try and start the server from Pgadmin I get the server doesn't listen error message. How can I get the server running again, or if that's not possible recover my data? there are many important tables in the database and restoring them from the backup will be a real pain! (especially if this is going to happen again!).

Edit:

The log file says:

2015-12-09 03:22:44 GMT LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.

2015-12-09 03:22:44 GMT LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.

2015-12-09 03:23:08 GMT LOG:  received fast shutdown request
2015-12-09 03:23:08 GMT LOG:  aborting any active transactions
2015-12-09 03:23:08 GMT LOG:  autovacuum launcher shutting down
2015-12-09 03:23:08 GMT LOG:  shutting down
2015-12-09 03:23:09 GMT LOG:  database system is shut down

I tried stopping antivirus and disabling my firewall, neither helped, nor did updating postgres.

Edit 2:

I have found that the postgresql-x64-9.3 service is listed as "stopped" in task manager. I have tried going to the services menu and restarting it. I get the following message:

The postgresql-x64-9.3 - PostgresSQL Server 9.3 service on Local Computer started and then stopped. Some services stop automatically if they are not in use by other services or programs.

This problem was resolved with the following actions:

Postgres 9.3. was uninstalled

Postgres was re-installed with a different data directory

The postgresql-x64-9.3 service was now running.

I stopped postgresql-x64-9.3

Then I cleaned out all the folders in the new data directory (I kept the new config files, in case they were the source of my problem).

I copied across my old data directory files (as per https://stackoverflow.com/questions/3305961/recover-postgresql-databases-from-raw-physical-files)

I restarted postgresql-x64-9.3

The old databases were now working

Thanks everyone for your suggestions


Lock table size issue when deleting on MySQL I'm attempting to delete errant records from a table based on certain dates and running into the following error:

mysql> delete from myTable where dataDate in ('2011-07-12');
ERROR 1206 (HY000): The total number of locks exceeds the lock table size

The table doesn't yet have indexes (this is post-load clean-up), and consists of ~2B records:

mysql> select table_rows from information_schema.tables where table_name = 'myTable';
+------------+
| table_rows |
+------------+
| 2073294315 |
+------------+
1 row in set (0.51 sec)

Suggestions I found via search suggested upping innodb_buffer_pool_size but I've already upped default pool size by 8x in order to get the data to load in reasonable time and current sits at 512MB:

mysql> select @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                1073741824 |
+---------------------------+
1 row in set (0.00 sec)

I'm planning to reload data for 3 dates (~3M records) after deleting the errant records.  Wondering if I should index the table first or if there are other adjustments that will allow this.

Schematically (draft code!):

CREATE PROCEDURE loop_until_done()
BEGIN
DECLARE records_count_before BIGINT;
DECLARE records_count_after BIGINT;
SELECT COUNT(*) FROM myTable INTO records_count_after;
cycle: LOOP
    SET records_count_before = records_count_after;
    DELETE FROM myTable WHERE dataDate IN ('2011-07-12') LIMIT 10000;
    /* SELECT SLEEP(1) INTO records_count_after; */
    SELECT COUNT(*) FROM myTable INTO records_count_after;
    IF records_count_before <= records_count_after THEN
        LEAVE cycle;
    END IF;
END LOOP;
END

It is safe to add SELECT into some service table for some flag value which stops the operation. For example this can be a delay value (SLEEP argument) - its change may change deletion speed and server loading during it, and the value above some predefined value (or maybe zero) is a signal to leave cycle and stop the deletion operation unconditionally.

Simplified code according to danblack's recommendation:

CREATE PROCEDURE loop_until_done()
BEGIN
REPEAT
    /* SELECT SLEEP(1) INTO @dummy; */
    DELETE FROM myTable WHERE dataDate IN ('2011-07-12') LIMIT 10000;
UNTIL NOT ROW_COUNT()
END REPEAT;
END


Test/Verify Log-Shipping Backup We recently suffered a power/connectivity outage at a data center housing two PostgreSQL 8.4 secondary servers, that remotely back up our two primaries via log-shipping replication. Backup power kept them alive, but they received no WAL files for a little over two days. The backlog on the two primaries approached 3500 before we got connectivity back again.

When I look at the logs in ~postgres/data/pg_log on the two secondaries, I can see in the current log that the first WAL segment to be processed when we got back online is the numerically consecutive very next one after the last one that made it into the the log file from the day of the start of the outage. It looks like we didn't miss anything, and the secondaries were caught up in less that a day.

But I'd like to verify this. What would most effectively assuage my paranoia would be to break connectivity again so the primaries would begin to buffer up WAL files, promote the secondary machines to primary in isolation, run enough strictly read-only  queries to satisfy myself of the integrity of our backup data, and then put the secondaries back in continuous recovery mode and finally restore connectivity.

Is that doable? Or would the promotion and processing of queries, even read-only ones, alter the secondary database enough that it could not resume continuous recovery afterwards?

I realize that this would not be an issue with streaming replication, but 8.4 does not have that capability.

After bringing the slave database cluster into standalone read-write mode (either by internal logic inside recovery_command or by restarting it without recovery.conf) it cannot be easily reverted to be a slave.

If you can, use file system level snapshots. This would be the easiest approach as you can revert the cluster to a known state without copying whole data directory.

More or less that would be the logic for testing slave:

stop_postgres()
make_pgdata_filesystem_snapshot()
remove_recovery_conf()
start_postgres()
run_testing()
stop_postgres()
revert_pgdata_fs_snapshot()
start_postgres()

If you don't have filesystem snapshots you will need a local copy of the whole cluster (rsync will be good to maintain this). Only disadvantage is disk space used, and more time needed.

rsync_pgdata_to_local_copy()
stop_postgres()
rsync_pgdata_to_local_copy()
remove_recovery_conf()
start_postgres()
run_testing()
stop_postgres()
rsync_pgdata_from_local_copy()
start_postgres()


Alternative to sequence and timestamp: uniquely ordering records in time Oracle 11gR2 Exadata

I'm required to uniquely identify when records are created in time. Sequence caching means I cannot use a sequence-based ID and batching inserts means that all records inserted in one batch will have the same timestamp value (even using TIMESTAMP(9)).  Akin to Twitter's since_id concept.

The best alternatives I've ideated so far

creating an additional sequence used for each unique timestamp
not batching inserts of the records to force a unique timestamp for each
record
not caching the sequence, although there has been some
discussion that this won't solve the problem under Exadata

Here's my requirement: I have an API that allows users to supply a sequence as a marker and request all records since that time. For example, they request 1000 records with a marker of 7 and they'll get 1000 records from my table with an ID greater than 1007. As an example let's say the numerically greatest ID of the returned 1000 records is 2045 so we return 2045 as the marker Later the clients request 1000 records with a marker of 2045 expecting to get the next batch of 1000 and a new marker.

Pretty straightforward way to allow them to get all of the records in whatever size works for them without missing any. However, due to sequence caching across multiple Exadata nodes, at the time the client requests 1000 records with a marker of 1007, a record with an ID of 2020 may not have been created. Therefore, when they do the next request using the marker of 2045, they will have missed record 2020 forever. Using the ID to get the timestamp of the associated record solves this, but then I must make sure to always insert records into the table individually to guarantee unique timestamps.

Assumptions:

Not a way to get separate timestamps for individual records on a bulk/batch insert
Multiple nodes may cause insertion of records to be non-time-sequential even with NOCACHE on the sequence (e.g., a record with sequence value 180 could be written with a systimestamp greater than that of a record with sequence value 179)

Hopefully I just haven't hit on the correct terms to search for existing answers. I feel that this is a problem that should have been solved by some pattern(s) for years.  I think Twitter has solved it...

I recommend ordering by timestamp. As long as the only records with identical timestamps are inserted in the same transaction, you can use the sequence-based ID as a secondary ordering.


Oracle 11g Express Edition - missing or invalid schema authorization identifier I have a new fresh instance of Oracle Express 11g.
I'm trying to create a new Schema with a Table and a View in the default db (xe).

My script is:

create user XAuthority identified by mysecret;

alter session set current_schema = XAuthority;

create schema authorization XAuthority
create table CUSTOMER (  ID int, CUSTOMER text)
create view CUSTOMER_VIEW as select * from CUSTOMER
grant select on CUSTOMER_VIEW to xareadonly;

but I get an error:

  SQL Error: ORA-02421: missing or invalid schema authorization identifier
  02421. 00000 -  "missing or invalid schema authorization identifier"

  *Cause:    the schema name is missing or is incorrect in an authorization
             clause of a create schema statement.

  *Action:   If the name is present, it must be the same as the current
             schema.

I'm familiar with Postgres and MySql but it seems Oracle is quite different.  Can anyone tell me what's wrong here?

CREATE SCHEMA

  Purpose

  Use the CREATE SCHEMA statement to create multiple tables and views and perform multiple grants in your own schema in a single transaction.

Well you have multiple issues. First of all you need to have CREATE SESSION system privilege granted to the newly created user. Secondly your data types are incompatible in Oracle(int should be converted to number and text to varchar2).

In order to use CREATE SCHEMA statement you need to be connected to database as the user which you have specified on the statement.

The problem is you are not connected to database as xauthority by altering the session.

SQL> conn sys/password
Connected.

SQL> create user XAuthority identified by mysecret;

User created.

SQL> alter session set current_schema = XAuthority;

Session altered.

SQL> create schema authorization XAuthority
create table CUSTOMER(ID number, CUSTOMER_name varchar2(20))
create view CUSTOMER_VIEW as select * from CUSTOMER
grant select on CUSTOMER_VIEW to xareadonly;  2    3    4
create schema authorization XAuthority
*
ERROR at line 1:
ORA-02421: missing or invalid schema authorization identifier

SQL> show user
USER is "SYS"

The user should be xauthority but it is still SYS.

You need create session system privilege to connect to the database.

SQL> grant create session to xauthority;

Grant succeeded.

You need quota on tablespace to create schema objects such as table.

SQL> alter user xauthority quota unlimited on orapdb1_tbs1;

User altered.

SQL> conn xauthority/mysecret
Connected.

SQL> conn xauthority/mysecret
Connected.
SQL> create schema authorization XAuthority
create table CUSTOMER(ID number, CUSTOMER_name varchar2(20))
create view CUSTOMER_VIEW as select * from CUSTOMER;

Schema created.


SQL Server CLE Encryption Implementation We got instruction from HQ to implement encryption on our database. The reason be, should a client gets hacked, the hackers cannot access personal info such as names, phone numbers, addresses, etc.
I have done a fair bit of research and it seems that Cell Level Encryption could be the way to got but I have some questions regarding the implementation (I am a developer, not really DBA material...).
Am I correct in saying the following:

All data types, for the columns we want to encrypt, must be changed to varbinary
We have to change all our SELECT statements to include DecryptByKey for encrypted columns
We have to change all INSERT & UPDATE statements to include EncryptByKey for encrypted columns

Since 99% of all selects, inserts and updates in our system happen in stored procedures, how would I implement the opening and closing of the SYMMETRIC KEY? Surely, any hacker worth something, can look at the SP code to see which key was used, or just execute the SP the get the data?
Also, is there a way to determine if a column contains encrypted data? We have generic insert/update functions and these would need to know when to use EncryptByKey.
Any guidance will be greatly appreciated!

Based on your requirements, I would actually recommend Transparent Data Encryption (TDE) instead of CLE.
TDE encrypts at the database level and therefore you don't have to worry about being compromised by storing the decryption process in a stored procedure. Rather, TDE works on the traditional private and asymmetric key pattern to prevent access to the unencrypted data without the proper key (which you can handle authenticating outside of the database to decouple the security concerns).
It's also better in my opinion because it doesn't force you to change your data types to VARBINARY which almost certainly could affect the performance of your queries. Rather it keeps everything as you designed under the hood (hence Transparent) when you access your database.


How to quote arrays in postgres copy statement to inser into JSON field I have some data that looks like this:
id,bo,c,url
1,"some text here", 22, "[{'id': 'NfKA3', 'u': 'https://somewebsite.com'}]"
2, "more text", 83, "[{'id': 'W3nAl', 'u': 'https://anotherwebsite.com'}]"
3, "even more text", 14, "[{'id': 'CyrMj', 'u': 'https://yetanotherwebsite.com'}]"

I'm trying to insert this data into a table that looks like this:
CREATE TABLE myTable(
id integer,
body varchar,
count, varchar,
url JSON
);

Using this command:
\copy mytable FROM 'myData.csv' WITH DELIMITER ',' csv header;

However, I'm having trouble with the single quotes in the url field of the csv. I'm getting errors like this:
ERROR:  invalid input syntax for type json
DETAIL:  Token "'" is invalid.
CONTEXT:  JSON data, line 1: [{'...

Can someone show me the correct way to structure my \COPY command so as to be able to load the url field in the csv as a JSON?
If necessary I can do some pre-processing of the file at the command line.

JSON uses double quote " to delimite keys and strings:
[{"id": "NfKA3", "u": "https://somewebsite.com"}]

CSV uses double quote to escape double quotes "" means "
1,"some text here", 22, "[{""id"": ""NfKA3"", ""u"": ""https://somewebsite.com""}]"

Look at: https://stackoverflow.com/questions/17808511/properly-escape-a-double-quote-in-csv


Why can't I use newid() in a user-defined table-valued function? I recently found out that SQL Server 2005 doesn't allow the use of newid() within user-defined functions. Why is this?

I have an alternate solution that suits my needs, so I'm not looking for ways to get around this. I'm curious why the designers would make this decision.

SQL Server only allows deterministic functions to be used within user-defined functions. Since the value of NEWID() is not deterministic, it cannot be used. You will find the same thing is true with GETDATE() and any other non-deterministic function.

I'm not qualified to answer why they would make that decision. It annoys me as well but I'm sure the SQL team at MS are not slouches.

EDIT: It turns out that my knowledge about GETDATE() is out-of-date. As the commenter says, you can use GETDATE() within functions from SQL Server 2005 onward. However, you still cannot use NEWID(), which I believe has to do with the same non-determinism constraint.


Can I use LetsEncrypt to secure SQL Server? I am running SQL Server 2014, and use LetsEncrypt to encrypt some websites on the server.  I want to remain PCI compliant, which requires an encrypted connection to the database, if accessing from outside the network (I want to use SSMS).  Would it be possible to leverage the WACS tool to secure a certificate for SQL Server external connections, with automatic updating of the certificate?

To be honest, I'm a little fuzzy on the details of assigning a certificate to SQL Server for external connections in the first place. (I get the gist, I think, but trying to put it together with WACS is another story...)

Yeah, I get it's bad practice to leave external connections open, but it sure does make it convenient to not have to remote into a server to access SQL Server Management Studio, to modify necessary things in the database, on a regular basis.  Not only that, I'd rather not work directly off the server.

Per Igor's suggestion, I went with the VPN method.  What this entailed was installing, and configuring OpenVPN, disabling all external TCP interfaces in the SQL Configuration Manager, and adding one for the standard OpenVPN IP (10.8.0.1), then under protocol, setting listen all to 'none.'

This solved a lot of issues - I also was running FileZilla server to transfer files, and now I no longer need to maintain a certificate, or keep this open to the outside world.  Now I can connect via VPN, and access the FTP server that way (binded the OpenVPN IP to the server).

Moral of the story is:  If you have a private server, configure a VPN, and stop wasting your time trying to secure things like SQL Server to the outside world (unless your web server isn't on your SQL box, of course)


How do I resolve Postgresql error, "PL/Perl function must return reference to hash or array"? The follow code used to work in Postgresql 8.4.11 with perl v5.10.1:

=# select * From testfunction();
      testfunction
------------------------
 http://www.google.com/

However, after doing a dump and load into Postgresql 9.1.3 with perl v5.14.2 I get:

ERROR:  PL/Perl function must return reference to hash or array
CONTEXT:  PL/Perl function "testfunction"

For reference, here is the function:

CREATE OR REPLACE FUNCTION testfunction(OUT text) AS $$
  use URI;
  return URI->new('http://www.google.com/')->canonical;
$$ LANGUAGE plperlu;

Again, the version of perl changed from v5.10.1 to v5.14.2; however, the return from Data::Peek is the same across both versions:

$ perl -MData::Peek -MURI -e'DPeek(
    URI->new(q[http://www.google.com])->canonical
);'

Output on both versions of perl:

\PVMG("http://www.google.com/"\0)

Short answer, coerce what you return to a string, should work fine in 8.4 and 9.1:

return URI->new('http://www.google.com/')->canonical().'';

See http://archives.postgresql.org/pgsql-bugs/2012-05/msg00096.php for my full response.


Shrinking Databases in a maintenance plan Everything I've heard (including from Microsoft) says you should never shrink a database. Yet a software vendor (Symantec) is telling us to do DBCC SHRINKFILE on the SQL 2012 databases and logs related to a product of theirs called Enterprise Vault.

Should I follow the recommendation of Symantec and shrink or the recommendation of Microsoft and everyone else to not shrink?

What you are saying is right. The database should not be shrinked. However there are some exceptions.

Let's say you move a lot of historical data out of your DB and it goes down from 100GB to 10GB. You may want to reduce it back to 10 or 20 or 50GB.

The thing to remember when you decide to go on with a shrink is that data (clustered and non clustered indexes) will get (possibly highly) fragmented. By data in indexes, I mean that rows won't be ordered by the indexed columns and stored continuously on continuous pages anymore. The server will have to jump through pages to read continuous rows (=> more IO).

Therefore, after a shrink you should defragment or rebuild your indexes. Choose for each index if it should be rebuilt or defragmented based on their fragmentation percentages.
You probably already have a maintenance plan taking care of this so you just have to run it again right after the shrink.

If your need their support, you might have to make them happy... The guy asking you to do it probably know nothing about SQL Server and is just following a written procedure.
In that case, you may proceed with the shrink and then update the indexes.

Note that I don't know much about Enterprise Vault.


The use of NOT logic in relation to indexes According to Microsoft's book on database development Exam 70-433: Microsoft SQL Server 2008 Database Development:

  Neither leading wildcard characters not NOT logic allow the query optimizer to use indexes to optimize the search. For optimal performance, you should avoid using the NOT keyword and leading wildcard symbols.

So I took that to be NOT IN, NOT EXISTS etc

Now with regards to this SO question, I thought that the chosen solution by @GBN would violate the statement given above.

Apparently, it does not.

So my question is: Why?

NOT IN (SELECT ...) and NOT EXISTS (SELECT .. WHERE correlation..) are "Anti Semi Joins". That is, recognised set based operations
WHERE NOT (MyColumn = 1) is a filter that requires all rows to be looked at

For more info, see:

Craig Freedman's "Introduction to Joins"
Wikipedia "Relational algebra, Antijoins"

Edit: for completeness

LEFT JOINs often perform worse. See http://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server

This same site notes that in MySQL, NOT EXISTS isn't optimised like other RDBMS and LEFT JOIN is better

In SQL Server, I know from experience that LEFT JOIN doesn't run as well as NOT EXISTS. You also often need DISTINCT to get the same results which another processing step.


Postgres sequence get last usage I have a database that has both sequenced correlated to table unique ids and "standalone" but having values.

I need to know which ones are really used but I'm not the owner of every procedure running on the database.

Is there any way to know when a Postgres sequence was last used?

No, the time when a sequence is used is not recorded by PostgreSQL.

I would figure out the last_value of each sequence, wait for a week and do it again.  Then you can easily see which sequences have advanced and how much.


Oracle 12: Using compound trigger to update data in same table on each insert/update I need some help fixing the code of a compound trigger.
I have a table with two columns that are used for auditing (upd_by and upd_time) and I'm trying to create a trigger that would update these columns on each insert/update to the table.
The table is:
CREATE TABLE customer_info(
    id NUMBER NOT NULL,
    name VARCHAR(50) NOT NULL,
    last_upd_by VARCHAR(25) NOT NULL,
    last_upd_time TIMESTAMP NOT NULL,
    PRIMARY KEY(id)
);

I found some examples on using compound triggers, and this is the code I have so far:
CREATE OR REPLACE TRIGGER customer_info_trg
    FOR UPDATE OR INSERT ON custommer_info
    COMPOUND TRIGGER
    TYPE r_customer_info_type IS RECORD (
        id   custommer_info.id%TYPE
    );

    TYPE t_customer_info_type IS TABLE OF r_customer_info_type
        INDEX BY PLS_INTEGER;

    t_customer_info   t_customer_info_type;

    AFTER EACH ROW IS BEGIN
        t_customer_info (t_customer_info.COUNT + 1).id := :NEW.id;
    END AFTER EACH ROW;

    AFTER EACH STATEMENT IS BEGIN
        FOR indx IN 1 .. t_customer_info.COUNT
        LOOP
            UPDATE customer_info
            SET last_upd_by = USER, last_upd_time = SYSTIMESTAMP
            WHERE id = t_customer_info (indx).id;
        END LOOP;
    END AFTER EACH STATEMENT;
END;

But when I run update on the table I get ORA-04098: trigger 'customer_info_trg'k is invalid and failed re-validation

There is no need for a compound trigger. Use simply
CREATE OR REPLACE TRIGGER customer_info_trg
    BEFORE UPDATE OR INSERT ON custommer_info
    FOR EACH ROW
BEGIN
    :NEW.last_upd_by := USER;
    :NEW.last_upd_time := SYSTIMESTAMP;
END;


Does SQL Server use multiversion concurrency control (MVCC)? Reading an older overview of CouchDB published on the IBM site HERE, I was surprised to find the following:

  Most modern databases have started to move from locking mechanisms to
  MVCC, including Oracle (since V7), MySQL (when used with InnoDB) and
  Microsoft® SQL Server 2005 and later.

I am using SQL Server quite a bit at the moment (V2012) and never considered that there may be any form of MVCC. I have also used with (tablock, holdlock) in stored procedures before.

Does SQL Server really implement MVCC anywhere and how does that reconcile with the idea of with (tablock, holdlock) if it does?

Does SQL Server really implement MVCC anywhere

Yes, since SQL Server 2005.

The SQL Server terminology is "row-versioning isolation levels". See the product documentation tree starting at Locking and Row Versioning. Note in particular that there are two separate "MVCC" implementations, read committed isolation using row versioning (RCSI) and snapshot isolation (SI).

  and how does that reconcile with the idea of with (tablock, holdlock) if it does?

Using that combination of hints serializes access to whole table(s). It is the least concurrent option available, so using these hints should be very rare. Whether a particular use could be replaced with RCSI or SI isolation depends on the specific circumstances. You could ask a follow-up question with a specific example if you want us to address that aspect in detail.

You might also like to read my series of articles on SQL Server isolation levels.


MongoDB failure scenario Design Question- During data insertion, if MongoDB failed, can I try multiple retries? I have read somewhere it will retry only one time.

What will happen if retry fail?
Can I keep the failed record somewhere in server memory?

During data insertion, if MongoDB failed, can I try multiple retries? I have read somewhere it will retry only one time.

The Retryable Writes feature in MongoDB 3.6+ allows compatible drivers to automatically retry certain write operations that may be affected by a transient interruption like a network error or replica set election.

Drivers currently only make one retry attempt if the  retryWrites option is true. This feature is not designed for persistent network errors where multiple retry attempts are likely to be unsuccessful. The Retryable Writes specification addresses this question: Why are write operations only retried once?.

  What will happen if retry fail?

The driver will return a write exception which your application can handle. For example, you could choose to make several retry attempts. For more info on retry strategies, see: How To Write Resilient MongoDB Applications.

  Can I keep the failed record somewhere in server memory?

This is part of the exception handling logic in your application. A write resulting in an exception has not been successfully acknowledged by your MongoDB server deployment, so your application will have to decide how to handle appropriately for your use case. The original data/objects will still be available in your application context (subject to the normal scoping/lifecycle rules of your programming language).


new Neo4j install: "No authorization header supplied" even after editing config file I just installed Neo4j today to play around on my Ubuntu EC2 instance, and I'm having two problems. First, the ubuntu install did not create a neo4j command so I'm not able to issue commands from the cli. Second, when I try this sample command to add a node to the database, I get the error

curl -H "Accept: application/json; charset=UTF-8" -H "Content-Type: application/json" -X POST http://localhost:7474/db/data/cypher -d '{
  "query" : "CREATE (n:Person { name : {name} }) RETURN n",
  "params" : {
    "name" : "Foo"
  }
}'

I get this error:

{
  "errors" : [ {
    "message" : "No authorization header supplied.",
    "code" : "Neo.ClientError.Security.AuthorizationFailed"
  } ]

I read the docs here, and I modified the config file per these instructions:
http://neo4j.com/docs/stable/rest-api-security.html

I updated the config file so that it should not require authentication:

dbms.security.auth_enabled=false

But still my curl requests to my localhost Neo4j server are blocked. All I can think is maybe neo4j needs a restart, but since I have no neo4j command, I can't use neo4j restart. I logged into the neo4j shell, but in the shell's list of commands there is no restart.

So my questions are:

(1) How can I restart neo4j?

(2) Where is my neo4j command/how can I create one following a standard ubuntu install?

(3) How could I create a username/password if I wanted to keep the access authentication in place?

Thanks.

You restart Neo4j by calling /path/to/neo/bin/neo4j restart
or by  /etc/init.d/neo4j restart if you installed it as a service.

The neo4j startup script is in the same path as the shell.


Demographics Dimension Table - Finding the Key! I have a dimension table which captures demographics on Employees.  The schema is similar to below:

    CREATE TABLE [dbo].[DimDemographics]
(
    [DemographicsKey] [int] NOT NULL,
    [AgeBand] [nvarchar](50) NULL,
    [LowerAge] [tinyint] NULL,
    [UpperAge] [tinyint] NULL,
    [SalaryBand] [nvarchar](100) NULL,
    [LowerSalary] [int] NULL,
    [UpperSalary] [int] NULL,
    [Gender] [nvarchar](50) NULL,
    [MaritalStatus] [nvarchar](50) NULL,
)

In the AgeBand, you'll have values such as '25 or Less', '26-30', 31-35'....'Over 65'
The LowerAge and UpperAge are just to allow us to query, i.e '25 or Less' will have a LowerAge of 0 and an UpperAge of 25 and so on.

The SalaryBand, LowerSalary and UpperSalary follow the same principal as AgeBand.

In T-SQL, we've written a query which joins the Employee table to the Demographics table and can extract the DemograhicsKey based on their age, salary, marital status and gender.

The Fact table in question has an Employee Key and a Company Key and we've obtained these with lookups - No problem here.

The problem is how to get a DemographicsKey in SSIS.  We've tried lookups, OLE DB Commands.  The SQL Query is similar to

    SELECT d.DemographicsKey
FROM dbo.DimEmployee e
INNER JOIN dbo.DimDemographics d on
    e.Age >= d.LowerAge and e.Age<=UpperAge
    and e.Gender=d.gender
    and e.Maritalstatus = d.MaritalStatus
    and CAST(e.SalaryReferenceLocal AS FLOAT) >= d.LowerSalary and CAST(e.SalaryReferenceLocal AS FLOAT) <= d.UpperSalary
    and DATEPART(YEAR, DATEADD(YEAR, -e.Age, GETDATE())) >= d.LowerYear AND DATEPART(YEAR, DATEADD(YEAR, -e.Age, GETDATE())) <= d.UpperYear
WHERE e.EmployeeID =?

The ? is because of the OLE DB Command.  I was trying to do it here, but could not get any output from a T-SQL query input.  One consideration was to use a Stored Procedure with OUTPUT, but at this point, I'd prefer to avoid that approach and solve it (if possible) with transformations!

I'm out of ideas now as to how to obtain a key from the demographics based on a list of criteria.

For reference, the table and code is scaled down and not the full version, but has enough information to describe the problem.

Any help would be greatly appreciated!

Thanks!

You mention that you already have a piece of sql that does what you want.

So i would suggest you modify that to become an update statement and implement it as a sql task. Another option is to save the update within a stored proc and call/execute it from ssis.

With a bit of thought you could probably write an update statement that only sets the values for new records or for records that have changed. Or perhaps do it as part of your ETL insert/merge.

However going back to my other comments: i don't think it makes sense to have a single key or dimension for such diverse attributes. I suggest you have a look at some sample schemas to see if it could be done differently.