texts:
I am seeing many errors like the following for my MySQL instance. It appears that it is complaining about an index that does not exist anymore. This is an InnoDB table. What do I need to do to update the innodb_index_stats to reflect the new indexes? I tried ANALYZE table goal_summary but that did not change anything.

  2017-07-14 15:35:21 2b6d11b47700 InnoDB: Ignoring strange row from mysql.innodb_index_stats WHERE database_name = 'network_00576' AND table_name = 'goal_summary' AND index_name = 'goal_summary_id' AND stat_name = 'n_diff_pfx03'; because stat_name is out of range, the index has 2 unique columns

More info:

select * from mysql.innodb_index_stats
 where database_name="network_00576"
   and table_name="goal_summary"
   and index_name="goal_summary_id"
   and stat_name = 'n_diff_pfx03';

Result:

+---------------+--------------+-----------------+---------------------+--------------+------------+-------------+------------------------------+
| database_name | table_name   | index_name      | last_update         | stat_name    | stat_value | sample_size | stat_description             |
+---------------+--------------+-----------------+---------------------+--------------+------------+-------------+------------------------------+
| network_00576 | goal_summary | goal_summary_id | 2017-02-01 22:32:12 | n_diff_pfx03 |        407 |           1 | goal_summary_id,offerid,date |
+---------------+--------------+-----------------+---------------------+--------------+------------+-------------+------------------------------+

And:

show keys from goal_summary where Key_name="goal_summary_id";

Result:

+--------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name        | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| goal_summary |          1 | goal_summary_id |            1 | goal_summary_id | A         |         694 |     NULL | NULL   |      | BTREE      |         |               |
+--------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+


texts: MongoDB 3.6 create local Shard db without replica

texts:
This is what I want to do:

Create a Config Server
Create 2 Shard
Create a Mongos Router

I don't want to replicate my data using replica set, I want to simulate that each shard has only a part of data like it says is shard theory.
I'm following this guide, but I understood that the guide create many replicas for config server and shared server and I don't want that. So this is what I do:
Config file of Config Server:
sharding:
  clusterRole: configsvr
replication:
  replSetName: localhost
net:
  bindIp: localhost
storage:
  dbPath: /home/christian/mongo/confsvr/metadata

Execute Config Server:
mongod --config /mongo/confsvr/mongod.conf --port 27019

Now doc uses rs.initiate() for adding a replica of config server (if I understood right), so I dropped this part.
After that, I created two config file for Shard Server.
sharding:
 clusterRole: shardsvr
replication:
  replSetName: localhost
net:
  bindIp: localhost
storage:
  dbPath: /home/christian/mongo/shard[1|2]

I started two shard:
mongod --config mongo/shard[1|2]/shard.conf  --port 2703[1|2]

Created conf file for Mongos Router:
sharding:
  configDB:localhost/localhost:27019
net:
  bindIp: localhost

So i tried to start mongos:
mongos --config /home/christian/mongo/router/mongos.conf

But terminal print in loop this:

Unable to reach primary for set localhost
Cannot Reach any nodes for set localhost

Last steps were connecting to mongos, and add the Shards to DB
How could I obtain what I want?


texts: The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

texts:
Below is an excerpt of an 800 line stored procedure that is failing with this error now. I say now because this is not new code. This script is scheduled to run every few minutes and has worked for a year.

ALTER PROCEDURE [dbo].[bsi_transfer_reallocate] @transfer_no int
AS
BEGIN
BEGIN TRY

[a few lines of code]

  BEGIN TRANSACTION REALLOCATE;

(over 700 lines of code)

  COMMIT TRANSACTION REALLOCATE;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION REALLOCATE;
    SET @dbg_msg = 'Transfer ' + CAST(@transfer_no AS varchar(8)) + ' aborted due to error condition with error number ' +
       CAST(ERROR_NUMBER() AS varchar(200)) + ' and message ' + ERROR_MESSAGE()
    INSERT INTO BATTERY_debug VALUES ('REALLOCATION Failure', GETDATE(), @dbg_msg)
    select  @dbg_msg
    RETURN (1)
END CATCH
END
return(0)


texts: Deleted sql server windows authentication user

texts:
I had deleted a system user "sysuser" from the server. I had previously used this user to login to SQL Server through "Windows Authentication" mode.

Now, I tried to create a new user with the same username and password but am unable to login to the SQL Server again.

I deleted the user in the server from control panel. Then I log in to SQL Server using windows authentication but cannot log in.  I tried to add back the user with same password using control panel. I tried to log in SQL Server using windows authentication and the re-created user but still cannot log in.


texts: SSIS Script transform package won't compile when deployed to dev [Visual Studio 2017]

texts:
I have visual studio 2017.

I have a script transform that I can run locally just fine, however when I deploy to the  Integration Services Catalog on the dev server and run it, it fails with the following error:

  'package name':Error: Failed to compiled scripts contained in the
  package. Open the package in the SSIS Designer and resolve the
  following compilation errors.

So, when I remote into the dev and rebuild the package, it will work fine on the next execution.

I thought this might be a reference issue but after reinstalling Visual studio 2017 and SSDT the references are an exact match on both machines.

What could be the issue for this?


texts:
I've been doing backups of my database " screen ".

after connecting to PSQL and typing \l+

I'm getting (among other things):

       Name       | Owner | Encoding |   Collate   |    Ctype    | Access privileges |  Size   | Tablespace |                Description
------------------+-------+----------+-------------+-------------+-------------------+---------+------------+--------------------------------------------
           screen | admin | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                   | 36 GB   | pg_default |

The size of my database being around 36GB.

Now i usually make regular backups doing :

pg_dumb screen > screenbackup.bak

And the size of the output was always pretty consistent with the size of my database.

But today i got a backup of only 8gb and that seems really odd to me.

After restoring into a temporary db and performing a few query there indeed seems to be a few missing data.

The size of the restored data was 10GB..

Thankfully I never dropped the original in the first place but there always seems to be a problem. I can't get pg_dump or pg_dumpall to backup the complete DB. The size is inconsistent when in my experience it's often at least the same size of the db or Bigger. not 4 time smaller..

Do you have any idea where to go from there to know where the problem is coming from?

Edit: maybe it's important: I'm on PSQL 9.4.4.1 and trying to do the backup so I can update Postgres in addition to just saving my data.


texts:
bash-4.2$ /usr/pgsql-10/bin/pg_upgrade --old-bindir=/usr/bin/ --new-bindir=/usr/pgsql-10/bin/ --old-datadir=/var/lib/pgsql/data/ --new-datadir=/var/lib/pgsql/10/data/ --check
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok

*failure*
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure.

connection to database failed: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/lib/pgsql/.s.PGSQL.50432"?

could not connect to source postmaster started with the command:
"/usr/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/pgsql/data/" -o "-p 50432 -b  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directory='/var/lib/pgsql'" start
Failure, exiting

tail /var/lib/pgsql/pg_upgrade_server.log gives me:

server stopped
command: "/usr/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/pgsql/data/" -o "-p 50432 -b -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directory='/var/lib/pgsql'" start >> "pg_upgrade_server.log" 2>&1
waiting for server to start....
FATAL: unrecognized configuration parameter "unix_socket_directory"
stopped waiting
pg_ctl: could not start server
Examine the log output.

How can I resolve this?


texts: Disabling variable substitution inside SQLCMD script when using :r syntax

texts:
I am using SQLCMD to deploy a set of scripted Agent jobs. I am running these with a SQLCMD script to calls each script using the :r syntax.

It looks like this:

:ON ERROR EXIT
:r $(scriptpath)\Script1.sql
:r $(scriptpath)\Script2.sql
:r $(scriptpath)\Script3.sql

Is there anyway to disable variable substitution with these scripts (equivalent of the /x switch for SQLCMD)?

I want to run Script1.sql, Script2.sql, etc... without variable substitution.


texts: Is a update-only-once-row table worth sharding?

texts:
I have a big MySQL 5.6 Inno DB table that contains user's request.
A table row updates only once, shortly-after the initial insert!
(The initial insert is to get the unique auto-incrementing request_id in order to do the processing of the request)

CREATE TABLE requests (
id bigint(20) NOT NULL AUTO_INCREMENT,
user_id BIGINT NOT NULL,
date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
request text
data text,
) ENGINE InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

psuedo script code goes like this:

execute("INSERT INTO requests (id) VALUES (default)")
my $id = query("SELECT LAST_INSERT_ID()")
my $data = do_some_fast_processing($id, $query)
execute(qq# UPDATE request SET query="$query", data=$data WHERE id=$id #)

There are roughly 10 million request per day. Meaning 10 million instance of  "insert-and-then-update".
Assume I shard this into 2(odd/even user id), And assume both shards will do half of each day's request, would I gain a significant/worthy write performances? because of  the lack of locking? Also assume my machine IO isn't maxed.


texts: Oracle outer join by (+) to Postgres conversion

texts:
I migrated from the Oracle database to Postgres, but some SQL scripts crashed. I investigated the errors and I found out that Postgres doesn't support outer joins using by (+) convention.
I tried to overwrite this Oracle statement:
SELECT
       r1.fibre AS root_edge_sid,
       sp.sid AS node_sid,
       sp.fid AS node_fid,
       sp.ftid AS node_ftid,
       f.sid AS edge_sid,
       f.fid AS edge_fid,
       f.ftid AS edge_ftid
FROM (SELECT r.FIBRE AS fibre, r.SPLICE_POINT AS splice_point FROM TABLE_A r) r1,
     (SELECT sf.SID AS sid, sf.FID AS fid, sf.FTID AS ftid FROM TABLE_B sf) sp,
     (SELECT r.FIBRE AS fibre, r.SPLICE_POINT AS splice_point FROM TABLE_A r) r2,
     (SELECT f.SID AS sid, f.FID AS fid, f.FTID AS ftid FROM TABLE_C f) f
WHERE r1.splice_point = sp.sid
  AND sp.sid = r2.splice_point(+)
  AND r2.fibre = f.sid(+)
  AND r1.fibre <> f.sid
  AND r1.fibre = 123456;

to Postgres statement:
SELECT r1.fibre AS root_edge_sid,
       sp.sid   AS node_sid,
       sp.fid   AS node_fid,
       sp.ftid  AS node_ftid,
       f.sid    AS edge_sid,
       f.fid    AS edge_fid,
       f.ftid   AS edge_ftid
FROM (SELECT r.FIBRE AS fibre, r.SPLICE_POINT AS splice_point FROM TABLE_A r) r1,
     (SELECT sf.SID AS sid, sf.FID  AS fid, sf.FTID AS ftid FROM TABLE_B sf) sp,
     (SELECT r.FIBRE AS fibre, r.SPLICE_POINT AS splice_point FROM TABLE_A r) r2,
     (SELECT f.SID  AS sid, f.FID  AS fid, f.FTID AS ftid FROM TABLE_C f) f
left outer join r2
on sp.sid = r2.splice_point
left outer join f
on r2.fibre = f.sid
WHERE r1.splice_point = sp.sid
  AND r1.fibre <> f.sid
  AND r1.fibre = 123456;

according to Postgres Wiki but in console I get this error: relation "r2" does not exist
Is possible to make joins between sub-select statements?
Or is in the script any error?


texts: Row Compression in AlwaysOn Availability Groups

texts:
I want to implement row compression on some databases that are in an availability group. Are row and page compression supported in availability group databases?
 Does it affect the replication, failover, configuration, or structure in any way? Does the role and mode of the node make a difference?


texts: Enable Replication/CDC from High Availability Group

texts:
I am a newbie to this Replication concept and I am trying to build some scripts to automate our Build Process where we will be disabling/re-enabling Replication on a regular basis.

Background:
We have an Application Database Server which is on High Availability (Primary & Secondary Nodes). Our Staging Database Server will be communicating with the HA Group either through a Load Balancer or Listener.

Requirement:
We would like to enable Replication at the Table level and not the Database level from HA Group to the Staging Server as we are only interested in few tables. Is this even possible? If so, I am looking for some scripts (from your Toolbox, if you have any) to create Publisher, Distributor, and Subscriber and then disable/re-enable them on a regular basis during our Build process.

Note: This would be more of a "Pull" and I am looking for Scripts that can be executed from the HA Group as part of the Build Process.

Your help with this is very much appreciated.

Thanks!


texts: MongoDB uses only one index on simple query with sorting

texts:
So the problem is that I'm having a huge collection and I'm trying to run a query that filters and sorts on only fields that have indexes on them.

I have these two indexes:

db.getCollection('product').createIndex({"_type": 1, "_collectivity": 1, "own_risk": 1});
db.getCollection('product').createIndex({"price": 1});

And that's the simple query I am running:

db.getCollection('product').find({
    "_type": "healthcare2",
    "_collectivity": null,
    "own_risk": 375
}).sort({price: 1}).explain()

And here's the winningPlan for this query:

{
  "winningPlan": {
    "stage": "FETCH",
    "filter": {
      "$and": [
        {
          "_collectivity": {
            "$eq": null
          }
        },
        {
          "_type": {
            "$eq": "healthcare2"
          }
        },
        {
          "own_risk": {
            "$eq": 375.0000000000000000
          }
        }
      ]
    },
    "inputStage": {
      "stage": "IXSCAN",
      "keyPattern": {
        "price": 1
      },
      "indexName": "price_1",
      "isMultiKey": false,
      "direction": "forward",
      "indexBounds": {
        "price": [
          "[MinKey, MaxKey]"
        ]
      }
    }
  }
}

So it scans through all the collection ignoring all indexes. But if I try to hint:

db.getCollection('product').find({
    "_type": "healthcare2",
    "_collectivity": null,
    "own_risk": 375
}).sort({price: 1}).hint({"_type": 1, "_collectivity": 1, "own_risk": 1})

Mongo will return me an error...

error: {
    "$err" : "Executor error: Overflow sort stage buffered data usage of 33686190 bytes exceeds internal limit of 33554432 bytes",
    "code" : 17144
}

Mongo will not try to use the price index after it sorts out all documents with the first index.

So... What is my problem? Or Mongo's? How do I make MongoDB use more than one index in one query?

My MongoDB is 3.0.6 and running in Ubuntu within Vagrant.


texts: Select data but allow users and groups to supersede everyone data

texts:
I have a database which stores simple text expansions. For example, the user enters a shortcut, and the phrase_text is visible. For the system default shortcuts, the username and group fields are null. If the phrase belongs to a group, then groupname will be populated. If it belongs to a user, the username field will be populated.

I want a query which will find all the shortcuts for a given user, knowing that if the user has any preferences, they will supersede the group preferences, which in turn supersede the system preferences.

Example data:

CREATE TABLE IF NOT EXISTS `phrases2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `shortcut` varchar(20) NOT NULL,
  `username` varchar(10) DEFAULT NULL,
  `groupname` varchar(10) DEFAULT NULL,
  `phrase_text` text,
  PRIMARY KEY (`id`)
)

INSERT INTO `phrases2` (`id`, `shortcut`, `username`, `groupname`, `phrase_text`) VALUES
(1, 'a', NULL,    NULL,     'AAA'),
(3, 'b', NULL,    NULL,     'BBB'),
(4, 'c', NULL,    NULL,     'CCC'),
(5, 'a', 'user1', NULL,     'AAA - for user 1'),
(6, 'a', NULL,    'group1', 'AAA - group1'),
(7, 'a', 'user2', NULL,     'AAA - for user 2'),
(8, 'a', NULL,    'group2', 'AAA - group2');

Suppose I want all the phrases for user1. I'd return rows 3,4,5 becuase row 1 is superseded by row 5.

Or, suppose I want all the phrases for user1 who also happens to be in group2. I'd still get rows 3,4,5 because user preference outranks group preference, which outranks system default preference.

Here is a fiddle: http://sqlfiddle.com/#!9/ee6b4f/21

Here is some code that does what I want, but it seems terribly inefficient:

set @user='user1';
set @group='group1';

select shortcut, phrase_text from phrases2 where username=@user
union select shortcut, phrase_text from phrases2 where groupname=@group
    and shortcut not in (select shortcut from phrases2 where username=@user)
union select shortcut, phrase_text from phrases2 where username is null and groupname is null
    and shortcut not in (select shortcut from phrases2 where username=@user or groupname=@group )


texts: PgAdmin 3 crashed when font size was increased

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


texts: ClassNotFoundException when trying to deconfig Oracle Enterprise Manager

texts:
The Enterprise Manager for my Oracle 11.2 installation doesn't work anymore as described in How can I change the connection descriptor used by Oracle Enterprise Manager? and I tried to re-configure it using the instructions here. However, the deconfig command runs into a ClassNotFoundException:

C:\Users\bcalmac>emca -deconfig dbcontrol db -repos drop

STARTED EMCA at Jan 10, 2014 12:05:04 PM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: test9
Listener port number: 1521
Password for SYS user:
Password for SYS user: Exception in thread "main" java.lang.NoClassDefFoundError: HTTPClient/ProtocolNotSuppException
        at oracle.sysman.emd.targetIntg.agent.AgentTargetInstaller.<clinit>(AgentTargetInstaller.java:235)
        at oracle.sysman.emcp.target.TargetManager.getAgentTargetInstaller(TargetManager.java:1008)
        at oracle.sysman.emcp.target.TargetManager.getLocalHost(TargetManager.java:1033)
        at oracle.sysman.emcp.ParamsManager.getLocalHost(ParamsManager.java:1037)
        at oracle.sysman.emcp.ParamsManager.getHost(ParamsManager.java:3020)
        at oracle.sysman.emcp.DatabaseChecks.validateUserCredentials(DatabaseChecks.java:1066)
        at oracle.sysman.emcp.ParamsManager.validatePassword(ParamsManager.java:3417)
        at oracle.sysman.emcp.EMConfigAssistant.promptForData(EMConfigAssistant.java:701)
        at oracle.sysman.emcp.EMConfigAssistant.promptForParams(EMConfigAssistant.java:2979)
        at oracle.sysman.emcp.EMConfigAssistant.displayWarnsAndPromptParams(EMConfigAssistant.java:3008)
        at oracle.sysman.emcp.EMConfigAssistant.getDisplayAndPromptWarnsParms(EMConfigAssistant.java:3033)
        at oracle.sysman.emcp.EMConfigAssistant.performConfiguration(EMConfigAssistant.java:1397)
        at oracle.sysman.emcp.EMConfigAssistant.statusMain(EMConfigAssistant.java:574)
        at oracle.sysman.emcp.EMConfigAssistant.main(EMConfigAssistant.java:522)

In what jar is this class, so I can copy it over from another Oracle installation?


texts:
This famous paper about finding Access Path in Relational Database Management System by Selinger et al. says the following about criteria tree and predicates (at the end of 3rd section).

  A sargable predicate is one of the form (or which can be put into the form) "column comparison-operator value". SARGS are expressed as a boolean expression of such predicates in disjunctive normal form.

My question would be, what is the significance of DNF here? Why exactly does being in DNF qualify a predicate to be a SARGABLE? Do the authors mean the following?

select ... from A join B on A.Id = B.Id where (A.F1 = 100 and A.F2 = 200) or (B.F1 = 1 and B.F2 = 2)

If so, isn't it a worst form to decide which to access first and do push down? Isn't full Disjunction the best form that it can be?

select ... from A join B on A.Id = B.Id where (A.F1 = 100 and A.F2 = 200) and (B.F1 = 1 and B.F2 = 2)


texts: Is checking for optional characters required in mysql REGEXP?

texts:
I have a few rows, in a users table with a name column:

SELECT name FROM users;
+--------------------+
| name               |
+--------------------+
| Administrator Core |
| Marketing Guy      |
+--------------------+

Now I want to check if the name column contains specified strings. For example, I want to check if 'Mark' or 'ket' is present in any row, and if so return it.

Both of these seem to work:

SELECT * FROM users WHERE LOWER(name) REGEXP 'ket|Mark'\G;

SELECT * FROM users WHERE LOWER(name) REGEXP '(.+)?ket(.+)?|(.+)?Mark(.+)?'\G;

Does the first one implicitly check for optional characters around ket and Mark? That's not how it would work with many programming languages. That is why I ask.


texts: Pgpool create a cluster node with no application traffic

texts: I have a pgpool cluster with 2 standby and 1 primary node. I am trying to create another node which will get replicated but will not handle any request or application traffic. It will solely be use for reporting purpose internally. How can I do that


texts: The fastest way to get the most recent records

texts:
I am looking for the fastest way to return the most recent record for each reference number.

I quite liked the solution from BrentOzar.com, but it does not seem to work when I add a third condition (SequenceId). It appears to work only when I specify the Id and the creation date.

To understand my problem, You will need to create the modified sample table, which is essentially a copy of the table on the above referenced website but with a little twist.

CREATE TABLE [dbo].[TestTable](
      [Id] [int] NOT NULL,
      [EffectiveDate] [date] NOT NULL,
      [SequenceId] [bigint] IDENTITY(1,1) NOT NULL,
      [CustomerId] [varchar](50) NOT NULL,
      [AccountNo] [varchar](50) NOT NULL,
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
(
      [Id] ASC,
      [EffectiveDate] ASC,
      [SequenceId] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO

SET IDENTITY_INSERT [dbo].[TestTable] ON
INSERT [dbo].[TestTable] ([Id], [EffectiveDate], [SequenceId], [CustomerId], [AccountNo])
VALUES (1, CAST(0xDF300B00 AS Date), 1, N'Blue', N'Green')
INSERT [dbo].[TestTable] ([Id], [EffectiveDate], [SequenceId], [CustomerId], [AccountNo])
VALUES (2, CAST(0xDF300B00 AS Date), 2, N'Yellow', N'Blue')
INSERT [dbo].[TestTable] ([Id], [EffectiveDate], [SequenceId], [CustomerId], [AccountNo])
VALUES (1, CAST(0xE0300B00 AS Date), 3, N'Red', N'Yellow')
INSERT [dbo].[TestTable] ([Id], [EffectiveDate], [SequenceId], [CustomerId], [AccountNo])
VALUES (3, CAST(0xE0300B00 AS Date), 4, N'Green', N'Purple')
INSERT [dbo].[TestTable] ([Id], [EffectiveDate], [SequenceId], [CustomerId], [AccountNo])
VALUES (1, CAST(0xE1300B00 AS Date), 5, N'Orange', N'Purple')
INSERT [dbo].[TestTable] ([Id], [EffectiveDate], [SequenceId], [CustomerId], [AccountNo])
VALUES (2, CAST(0xE3300B00 AS Date), 6, N'Blue', N'Orange')
INSERT [dbo].[TestTable] ([Id], [EffectiveDate], [SequenceId], [CustomerId], [AccountNo])
VALUES (3, CAST(0xE6300B00 AS Date), 7, N'Red', N'Blue')
SET IDENTITY_INSERT [dbo].[TestTable] OFF
GO

If I run a similar query as on the website, I get exactly the same result.

SELECT tt.*
FROM dbo.TestTable tt
LEFT OUTER JOIN dbo.TestTable ttNewer
ON tt.id = ttNewer.id AND tt.EffectiveDate < ttNewer.EffectiveDate
WHERE ttNewer.id IS NULL

However, the little twist is that I added a SequenceId column to the table as you may have noticed. The purpose of this column is because the client may want to do a post dated entry for a date in the past. This entry must supercede the other entries made on the same date which is in the past. If I run the query before I add the the post dated entries, I get the same result as previously.

SELECT tt.*
FROM dbo.TestTable tt
LEFT OUTER JOIN dbo.TestTable ttNewer
ON    (
            tt.id = ttNewer.id AND tt.EffectiveDate < ttNewer.EffectiveDate
            AND tt.SequenceId < ttNewer.SequenceId
      )
WHERE ttNewer.Id IS NULL

If I add two post dated entries as shown below, then I start to get interesting results.

INSERT INTO TestTable(Id,EffectiveDate,CustomerId,AccountNo) values
(
      2,'20090103','Blue','Orange'
);

INSERT INTO TestTable(Id,EffectiveDate,CustomerId,AccountNo) values
(
      2,'20090105','Blue','Orange'
);

What you should notice is that the two queries below no longer return the last record whether I use the one which is similar to what is on your website or I use the one which adds another condition (SequenceId)

SELECT tt.*
FROM dbo.TestTable tt
LEFT OUTER JOIN dbo.TestTable ttNewer
ON tt.id = ttNewer.id AND tt.EffectiveDate < ttNewer.EffectiveDate
WHERE ttNewer.id IS NULL

SELECT tt.*
FROM dbo.TestTable tt
LEFT OUTER JOIN dbo.TestTable ttNewer
ON    (
            tt.id = ttNewer.id AND tt.EffectiveDate < ttNewer.EffectiveDate
            AND tt.SequenceId < ttNewer.SequenceId
      )
WHERE ttNewer.Id IS NULL

What I would like the query to do is to return the last record for a reference number (Id) based on the last sequence number for any given day. In other words, the record with the last sequence number on the most recent EffectiveDate.


texts: TSQuery To Search Number and UoM in Text

texts:
I'm beginner with full text search so I'm learning this feature using psql following a tutorial i found here. I modify the data sample to meet my own need:

INSERT INTO documents (document_text) VALUES
('Pack my box with five inches liquor jugs.'),
('Jackdaws love my big sphinx of quartz.'),
('The 5" wizards jump quickly.'),
('How vexingly quick daft zebras jump!'),
('Bright vixens jump; dozy fowl quack.'),
('Sphinx of black quartz, judge my vow.');

And tried to search using several combination of keyword hoping to get document #1 and #3, such as:

SELECT document_id, document_text FROM documents
WHERE document_tokens @@ to_tsquery('5"');

output: document #3 only

SELECT document_id, document_text FROM documents
WHERE document_tokens @@ to_tsquery('5&inch');

output: null

SELECT document_id, document_text FROM documents
WHERE document_tokens @@ to_tsquery('5&in');

output: document #3 only

SELECT document_id, document_text FROM documents
WHERE document_tokens @@ to_tsquery('five&in');

output: document #1 only

SELECT document_id, document_text FROM documents
WHERE document_tokens @@ to_tsquery('inch&five');

output: document #1 only

While naturally we can assume that 5" / 5 in / five inches all are same, why the fts give different result (can't output both document #1 and #3)? If I use the fts in wrong way, please show me the right implementation of it or if I need to use other search engine to solve my problem, which search engine should I use?


texts:
I want to load information about queries execution from log of PostgreSQL to database table. I want such information as query execution time, query start time, query text. Does any simple solution exist?
pgBadger allows to make some analysis, but I want to have access to data about individual query execution for analysis.


texts: mysql innodb cluster 3 nodes, 2 lagging almost 24 hours

texts:
I have a 3 node setup and the 2 read only nodes are way behind (like 24 hours) from the rw node..

I saw here:  https://bugs.mysql.com/bug.php?id=86797 to restart the lagging node and will pick up by itself..
since I was not very convinced I did this in just one node..
restarted ok and came to the cluster in "RECOVERING" mode
such good...
But now I am checking and the Q is super big getting bigger
and when I compare the data in one table to the one in RW is updating.. just very slow like one a sec .. so is not picking up any slack.. it should go faster to pick up with the RW node right?

 CHANNEL_NAME: group_replication_applier
                           VIEW_ID: 15255041(deleted)
                         MEMBER_ID: 47dbb651-XXXXX (deleted)
       COUNT_TRANSACTIONS_IN_QUEUE: 218417
        COUNT_TRANSACTIONS_CHECKED: 0
          COUNT_CONFLICTS_DETECTED: 0
COUNT_TRANSACTIONS_ROWS_VALIDATING: 0
TRANSACTIONS_COMMITTED_ALL_MEMBERS: 1667fe4c-5033-11e8-b942-(Deleted)-3140512,
617916a6-5032-11e8-ad5b-(Deleted):1-14
    LAST_CONFLICT_FREE_TRANSACTION:

note: I added the deleted for privacy.
on my other node is also behind around 24 hours says Q is 0

select * from replication_group_member_stats\G
*************************** 1. row ***************************
                      CHANNEL_NAME: group_replication_applier
                           VIEW_ID: 1525504100(Deleted):13
                         MEMBER_ID: 617916a6-5032-11e8-(deleted)
       COUNT_TRANSACTIONS_IN_QUEUE: 0
        COUNT_TRANSACTIONS_CHECKED: 6846224
          COUNT_CONFLICTS_DETECTED: 0
COUNT_TRANSACTIONS_ROWS_VALIDATING: 5921012
TRANSACTIONS_COMMITTED_ALL_MEMBERS: 1667fe4c-5033-11e8-b942-0cc(deleted):1-3173566,
617916a6-5032-11e8-ad5b-(deleted):1-14
    LAST_CONFLICT_FREE_TRANSACTION: 1667fe4c-5033-11e8-b942-0(deleted)
1 row in set (0.0004 sec)

I am researching but is confusing some is for mysql 8.x like this solution:
http://lefred.be/content/mysql-innodb-cluster-how-to-handle-fc/

and other info I found is for master/slave so is not related..

Also looking into the parallel slave options but not sure if this will make any difference.

NOTE: the hardware is exactly the same on all 3 nodes.

thanks


texts: distinguish single-block vs multi-block reads in Oracle explain plan

texts: If I am looking at a query plan in Oracle 11g, is there any way I can tell which operations will result in single-block reads and which will result in multi-block?


texts: Database design for job board based on neighbouring cities

texts:
I'm trying to implement the relationships for the data represented below.

I have a microjob board, users sees job listings in their city and neighbouring cities

user 1 lives in city1
user 2 lives in city2 and that city is neighbour of city3 and city4
user 3 lives in city3
user 4 lives in city5

user 1 posts a job that needs to be done in city3

After that;

user 2 sees this job at job listings as he lives in city2 and that
city is neighbour of city3 - can offer help
user 3 sees this job at job listings as he lives in city3 - can offer help
user 4 can't see this job as he neither lives in city3 nor is his city a    neighbour of it

How can I accomplish this database schema with relationships?


texts: How to control the excessive use of ram by SQL Server?

texts:
The database server that I am using is running 6 different SQL Server instances. It has 48 GB RAM. And one of them is consuming more than 10 GB of RAM, total consumption is 20 GB for now. The RAM consumption is growing continuously. Couple of days before it was using more than 40 GB of RAM and the server was responding very slow. The application shows the crashing problems when saving data.

So I restarted the SQL Server services.

As soon as the services were restarted, the usage came down to 4 GB, but now it is growing.
And I am worried that it will grow up to 40 GB in 4 or 5 days and make the server slow.

Service restarting is not the good option I guess.

I also found from different sources that we can set the maximum memory usage size for SQL Server. And am not quite sure if this will help or not. I can't test this because the server is using the production database and it would be a risk if the service stops while modifying the setting in SQL Server.

Can any one help for this problem?


texts: SQL Server 2016 install configuration domain account not added to SeManageVolumePrivilege

texts:
I've installed several SQL Server 2016 SP1 servers with the use of a configuration file but I noticed that the service account is not added to 'Perform Volume Maintenace Tasks'. Instead the user 'NT Service\MSSQLServer' is added.

In my ini-file I used following parameters:

SQLSVCACCOUNT="domain\user"
SQLSVCINSTANTFILEINIT="True"

Is this a known bug in the installer or am I doing something wrong?


texts:
I have three tables as mentioned below. I need to implement basic search functionality where the user will search for a keyword which could either match 'title' (from Proposal table) or 'msNumber' or 'description' (from Project table) and should match 'stageNumber' and 'newState' from ProjectLifecycle table.

create table Proposal (
    proposalId LONG not null primary key,
    title VARCHAR(75) null,
);

create table Project (
    proposalId LONG not null primary key,
    msNumber VARCHAR(75) null,
    description VARCHAR(75) null
);

create table ProjectLifecycle (
    lifecycleId LONG not null primary key,
    proposalId LONG,
    stageNumber DOUBLE,
    newState LONG,
);

Below is the query which am using right now

SELECT
    p.proposalId, p.title, pj.msNumber
FROM
  Proposal p
JOIN
  ProjectLifecycle pl ON pl.proposalId = p.proposalId
JOIN
  Project pj ON pj.proposalId = pl.proposalId
WHERE
  (p.title like '%%' or pj.msNumber like '%%')
AND
    pl.newState=0
AND
  (pl.stageNumber= 60.60 OR pl.stageNumber < 60.60 OR pl.stageNumber = 95.99);

The stageNumber and the newState conditions are working fine as expected but the keyword search isn't.

Please help me in finding where I am going wrong. I am using MySQL which does not support FULL OUTER JOIN.


texts:
During Transactional Replication, we have data going from Server A to Server B.

Say we were to reseed an Identity Column on a Table on Server A (for whatever reason), how would this affect the data on Server B?

My intuition would say that the data on Server A will use the newly seeded Identity, and that would be sent over to Server B as-is (and Server B would have content using the new Identity), but I'm not sure if this is correct.


texts: Changing Key (PK, FK) properties in DROP/Add column statements

texts:
I want to change a table by adding a column. So far nothing to see here, BUT I would like to make this column part of a composite key, i.e., I now have a layout

table_name( Field_1  datatype PK, Field_2 datatype,....)

and I want the inserted column, say Field_k to be part of a PK jointly with the existing single-field PK.

I haven't found any source for how to do this or for whether it is possible or not. Any advice, please?


texts: When I run mssql-cli, I get "No module named mssqlcli"

texts:
I just installed mssql-cli with pip,

pip3 install mssql-cli

Then I try to run mssql-cli and I get

$ mssql-cli
/usr/bin/python: No module named mssqlcli


texts: How to unify three separate tables having same data, same columns but tuples in different languages. Only one key(caption) is common to all

texts: This needs to be done in order to complete the unification of many tables present in different databases with different languages. (Part of Dictionary Management, will have caption and messages where multiple columns can make messages)


texts: Statistics disappear after incremental update

texts:
We have a large partitioned SQL Server database utilizing incremental statistics. All of the indexes are partitioned aligned. When we try to rebuild a partition online by partition all of the statistics disappear after the index is rebuilt.

Below is a script to replicate the issue in SQL Server 2014 with the AdventureWorks2014 database.

--Example against AdventureWorks2014 Database

CREATE PARTITION FUNCTION TransactionRangePF1 (DATETIME)
AS RANGE RIGHT FOR VALUES
(
   '20130501', '20130601', '20130701', '20130801',
   '20130901', '20131001', '20131101', '20131201',
   '20140101', '20140201', '20140301'
);
GO

CREATE PARTITION SCHEME TransactionsPS1 AS PARTITION TransactionRangePF1 TO
(
  [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY],
  [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY],
  [PRIMARY], [PRIMARY], [PRIMARY]
);
GO

CREATE TABLE dbo.TransactionHistory
(
  TransactionID        INT      NOT NULL, -- not bothering with IDENTITY here
  ProductID            INT      NOT NULL,
  ReferenceOrderID     INT      NOT NULL,
  ReferenceOrderLineID INT      NOT NULL DEFAULT (0),
  TransactionDate      DATETIME NOT NULL DEFAULT (GETDATE()),
  TransactionType      NCHAR(1) NOT NULL,
  Quantity             INT      NOT NULL,
  ActualCost           MONEY    NOT NULL,
  ModifiedDate         DATETIME NOT NULL DEFAULT (GETDATE()),
  CONSTRAINT CK_TransactionType
    CHECK (UPPER(TransactionType) IN (N'W', N'S', N'P'))
)
ON TransactionsPS1 (TransactionDate);

INSERT INTO dbo.TransactionHistory
SELECT * FROM Production.TransactionHistory
--  SELECT * FROM sys.partitions
--  WHERE object_id = OBJECT_ID('dbo.TransactionHistory');

CREATE NONCLUSTERED INDEX IDX_ProductId ON dbo.TransactionHistory (ProductId)
  WITH (DATA_COMPRESSION = ROW, STATISTICS_INCREMENTAL=ON)
  ON TransactionsPS1 (TransactionDate)

DBCC SHOW_STATISTICS('dbo.TransactionHistory', IDX_ProductId);
PRINT 'Stats are avialable'

ALTER INDEX [IDX_ProductId] ON [dbo].[TransactionHistory] REBUILD
  PARTITION = 9 WITH (ONLINE = ON , DATA_COMPRESSION = ROW)

PRINT 'After online index rebuild by partition stats are now gone'
DBCC SHOW_STATISTICS('dbo.TransactionHistory', IDX_ProductId);

PRINT 'Rebuild the stats with a rebuild for all paritions (this works)'
ALTER INDEX [IDX_ProductId] ON [dbo].[TransactionHistory] REBUILD
  PARTITION = ALL WITH (ONLINE = ON , DATA_COMPRESSION = ROW,
  STATISTICS_INCREMENTAL = ON)

PRINT 'Stats are back'
DBCC SHOW_STATISTICS('dbo.TransactionHistory', IDX_ProductId);

PRINT 'Works correctly for an offline rebuild by partition'
ALTER INDEX [IDX_ProductId] ON [dbo].[TransactionHistory] REBUILD
  PARTITION = 9 WITH (ONLINE = OFF , DATA_COMPRESSION = ROW)

    --stats still there
DBCC SHOW_STATISTICS('dbo.TransactionHistory', IDX_ProductId);

ALTER INDEX [IDX_ProductId] ON [dbo].[TransactionHistory] REBUILD
  PARTITION = 9 WITH (ONLINE = ON , DATA_COMPRESSION = ROW)

DBCC SHOW_STATISTICS('dbo.TransactionHistory', IDX_ProductId);
PRINT' stats are gone!!!!!!'

As shown, we cannot rebuild indexes by partition online without losing all statistics for index. This is a major maintenance issue for us. It almost appears that stats incremental option needs to be a part of the single index rebuild syntax or the online option needs to properly handle it like the offline option does.

Please let me know if I am missing something?

Updates:

As far as our need for incremental stats: We are partitioning on an internal customer id and not a date. So when a new client is brought in (large back-load of data) we can simply update the stats for the partition and quickly avoid any ugly plans being created for this new customer. I think I will file it with Microsoft as a bug and see what they have to say and go with the solution of just re-sampling the stats for that partition.

Connect bug report:

Statistics disappear after online index rebuild with incremental statistics

Update:
Microsoft has confirmed that it is a bug.


texts: SQL Server ReadOnly Routing Connection String

texts: Can we route read-only connection to readable secondary replica without setting connection string (ApplicationIntent = ReadOnly)?


texts:
A couple of thoughts on AlwaysOn AG migrations:

Is there a reason why Microsoft didn't implement similar technology to Hyper-V Live Migration to AlwaysOn AGs so that the buffer pool and cache are transferred to the secondary when failing over. I'm sure it's no easy task to keep everything consistent during migration but it would be a great feature.

If I warmed the cache of a read-only secondary before migration would the plans and data in the buffer pool remain once the DB had come online?

I'm only really thinking of migrations between synchronous nodes on these two points.


texts: Circular dependency between 2 table with one to one and one to many relationship

texts:
I have 2 tables in SQL database, User and Company.

User belongs to one company.

So user table has UserID & CompanyID (foreign key)

Company can have multiple users. One of them will be the company owner.

So company table has CompanyID & UserID (foreign key)

One user can be the owner of only one company. How can I enforce that?

Is it ok to have such a circular reference? Or should I have a third table CompanyOwner with CompanyID and UserID columns?


texts:
What is the most optimal way to store lots (up to 300) of properties about an object? All properties determinate presence of a factor. Like, "has dog", "has cat", "has garden", "has N".

Initially I was thinking about a table with 300 columns:

create table my_objects (
    has_dog enum('0','1') NOT NULL,
    has_cat enum('0','1') NOT NULL,
    has_garden enum('0','1') NOT NULL
);

Don't bother the relevance of the enum type, it's there only for extra self-explanatory reasons.

But having those has_ x 300 columns makes my guts feel nervous.

Any advice is much appreciated.

EDIT

Since many people started asking correct questions, here's the additional info:

The db will mostly exclusively handle SELECT queries. Selects consist of combination of has_{prop} = 0|1 AND/OR has_{prop} = 0|1. Like heavy filtering.
There will be Sphinx/Solr layer on top of db.
The amount of properties is constant. So if there's one row in table my_objects, it will have those 300 columns filled, either with default values, or defined.
my_objcets table will hold no more than 1 mil of objects.


texts: To make a LVM backup of a MySQL database, would I have to reduce the logical volume size?

texts:
I am trying to learn how to do a Ubuntu LVM backup of a MySQL database.

For this test I installed a fresh copy of Ubuntu 12.04.2 LTS on a new box, along with MySQL 5.5.  The box has one hard drive.  During the Ubuntu installation, I chose the partitioning option "Use entire disk and setup LVM".

Here is the space I have:

root@server2:~# df
Filesystem               1K-blocks    Used Available Use% Mounted on
/dev/mapper/server2-root 238088848 1206832 224787760   1% /
udev                        996264       4    996260   1% /dev
tmpfs                       402324     384    401940   1% /run
none                          5120       0      5120   0% /run/lock
none                       1005800       0   1005800   0% /run/shm
/dev/sda1                   233191   26733    194017  13% /boot

Ubuntu gave me a LVM setup of two logical volumes on one volume group:

root@server2:~# pvs
  PV         VG      Fmt  Attr PSize   PFree
  /dev/sda5  server2 lvm2 a-   232.64g 4.00m

root@server2:~# vgs
  VG      #PV #LV #SN Attr   VSize   VFree
  server2   1   2   0 wz--n- 232.64g 4.00m

root@server2:~# lvs
  LV     VG      Attr   LSize   Origin Snap%  Move Log Copy%  Convert
  root   server2 -wi-ao 230.68g
  swap_1 server2 -wi-ao   1.96g

The database data file is at /var/lib/mysql/:

root@server2:~# mysqladmin variables | grep datadir
| datadir  |  /var/lib/mysql/

Which is on the logical volume:

root@server2:~# df /var/lib/mysql
Filesystem               1K-blocks    Used Available Use% Mounted on
/dev/mapper/server2-root 238088848 1206836 224787756   1% /

There is not much free space left:

root@server2:~# vgdisplay server2 | grep Free
  Free  PE / Size       1 / 4.00 MiB

And finally I get to my question, in order to make a LVM backup of this MySQL database, will I first have to reduce the size of the logical volume so that there is more space?


texts: Composite index design where only a subset will be queried

texts:
I have a table with columns A (typedate), B1 ... B24 (all boolean).

Generally, A will always be used in every query and only one of B1 ... B24. A large subset of the B columns (more than half) will generally be queried more than the rest, but all of them will be used fairly frequently.

Should I create indexes on 24 two-tuples of (A, B1) ... (A, B24)?
Or 1 huge composite index encompassing all the columns?

If I'm creating a lot of indexes, at what point am I adding too many indexes to the table and what is the approach to working that out? The table is used in a lot of core functionality and will be queried very frequently.

The B columns will only be filtered by true, and generally only one B column will be used in a single query. So the where clause of the query will generally look like:

... WHERE A = '2018-11-22' AND B12


texts:
Info: I am setting up a new MS Access database for a small team (~10 person) project. There is a table "A" which is never modified but has relations to another table based on the field "Tag Number". This other table "B" contains all the dynamic information which is modified regularly by the team. Once a week, we receive a new Excel spreadsheet that contains the latest engineering export, which contains the entire contents of the table A. Some new records will be added and some records will be gone every week.

My question is this:

How do I replace the entire contents of the A table? I want to ensure that the relation to B table stays intact. I could delete the entire table and copy/paste in from excel (70000 rows), or I can import the excel spreadsheet to a new table and then use a combination of Update/Append/Delete queries. (Update to modify existing records, Append to add the new non existent records and Delete to remove records no longer present in the latest spreadsheet.)

Or some other, better, method.

Help please?


texts: I currently have a database with an attached CMS. The CMS has about 50+ people with various permission levels that can log in etc... I would like to know if there is a way I can look at the transaction logs (taken every 30 minutes) and see where a particular value was updated on a bit switch. I can then cross reference it with the person who was logged in at the time so I am fine on that part. If someone can guide me on how to read the transaction log to find the specific update to table x that would be a huge help.


texts: Postgres - default date format when interpreting strings

texts:
What is the default data format that postgres uses when confronted with a character representation of a date?

These SQL statements all work, and the character string is always correctly interpreted as 11th of December 2013. In these examples there is no scope for mis-interpretation. (mydate is defined as timestamp)

SELECT * FROM mytable WHERE mydate = '11-Dec-2013';
SELECT * FROM mytable WHERE mydate = '11-2013-Dec';
SELECT * FROM mytable WHERE mydate = '2013-11-Dec';

These versions get interpreted as different values

SELECT * FROM mytable WHERE mydate = '11-12-13';
SELECT * FROM mytable WHERE mydate = '11-13-12';
SELECT * FROM mytable WHERE mydate = '13-11-12';

I assume there's a default format setting somewhere which postgres use to decide whether dates are read as "dd-mm-yy", "mm-dd-yy" or "yy-mm-dd" (or any other combination)

What is the default format and how do I change it?

I'm aware that I can explicitly define the format like this....

SELECT * FROM mytable WHERE mydate = TO_DATE('11-12-13','DD-MM-YY');

But implementing that would require a lot of code changes for me, and I'm aware of the SET datestyle command, but that appears to limit me to a set of explicit formats rather than allowing custom formats.


texts: Programmatically find indexes that cannot be rebuilt online

texts:
I am automating rebuild and reorganise indexes using T-SQL. I run into problems with indexes that cannot be rebuilt online. Primarily this happens because ntext/nvarchar columns are included.

Is there a way to programmatically identify them as part of this statement? So kind of addition to the WHERE predicate

SELECT
--TOP 20
OBJECT_NAME(IPS.OBJECT_ID) AS [TableName], avg_fragmentation_in_percent, SI.name         [IndexName],
schema_name(ST.schema_id) AS [SchemaName], 0 AS IsProcessed
INTO #FramentedTableList
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , NULL) IPS
JOIN sys.tables ST WITH (nolock) ON IPS.OBJECT_ID = ST.OBJECT_ID
JOIN sys.indexes SI WITH (nolock) ON IPS.OBJECT_ID = SI.OBJECT_ID AND IPS.index_id =   SI.index_id
WHERE ST.is_ms_shipped = 0 AND SI.name IS NOT NULL
AND avg_fragmentation_in_percent >= CONVERT(DECIMAL,   @FragmentationThresholdForReorganizeTableLowerLimit)
ORDER BY avg_fragmentation_in_percent DESC


texts: SSIS - Excel destination does not allow characters more than 255

texts:
My SSIS package has a ado.net source, which returns columns with width more than 2000 characters,but those columns get truncated when redirected output to excel destination.

Any idea, how to resolve this problem..?

I am using excel 2007, as excel destination.


texts: Getting last modification date of a PostgreSQL database table

texts:
I'm trying to get when my table was modified by checking its file modification date as it is described in this answer. But the result is not always correct. The file modification date updates in several minute after I update my table. Is it correct behaviour? Does PostgreSQL store table modifications in some cache and then flush it to the hard drive?

So, how do I get the correct last modification date of a table (let's assume that auto vacuum modifications are ok too)?

I use PostgreSQL 9.2 under Linux Centos 6.2 x64.


texts: Is there a max allowance for entries in a table in mysql?

texts: Is there a set maximum (or even a recommended maximum) for how many entries that can/should be stored in a mysql table? How much is really too much?


texts:
I have created self signed Certificated by using openssl and have mongodb.pem(mongodb.key + mongodb.cert) file generated from openssl. Also i have my mongodb-ssl.conf file as follows

storage:
   dbPath: C:\Users\rajshanb\MongoDB\data\db\
net:
   port: 27018
   bindIp: 127.0.0.1
   ssl:
       Mode = requireSSL
       PEMKeyFile = C:\Users\rajshanb\mongodb.pem
       PEMKeyPassword = rajshanb
       CAFile = C:\Users\rajshanb\client.pem

But when i am trying to start the server from terminal :

C:\Users\rajshanb\MongoDB\mongodb\bin>mongod -f C:\Users\rajshanb\mongodb-ssl.conf
Unrecognized option: net.ssl
try 'mongod --help' for more information

I'm getting above mentioned error. Please help me to identify whether i am missing something in configuration.

I am having installed MongoDB version as :

C:\Users\rajshanb>mongod --version
db version v3.4.6
git version: c55eb86ef46ee7aede3b1e2a5d184a7df4bfb5b5
OpenSSL version: OpenSSL 1.0.1u-fips  22 Sep 2016
allocator: tcmalloc
modules: enterprise
build environment:
    distmod: windows-64
    distarch: x86_64
    target_arch: x86_64


texts: What happens with WITH if later the query fails

texts:
I am by no means a database expert so I might be asking something dumb.

So I am using PostgreSQL and I want to do the insert to one table, keep the ID (ID is incrementing automatically) and insert a tuple to another table (tuple has that ID in it).

Obviously I want to keep things atomic.

Query:

WITH inserted AS (
  INSERT INTO
    "public"."user" (email, first_name, last_name, username)
  VALUES
    (?, ?, ?, ?)
  RETURNING id
)
INSERT INTO
  "public"."security" (user_id, security)
VALUES((
  SELECT id FROM inserted
), ?);

Question is, can this even work? What will happen if the second insert fails? Will the first remain persisted?

The obvious answer is that I might put this into transaction and commit/rollback but will this be sufficient?


texts: MySQL Daemon failed to start on CentOS 6 after automatic update

texts:
MySQL suddenly stopped working this night, after an automatic update of MySQL.

[root@lincl1161 xxxx]# service mysqld restart
mysqld stopped:                                            [  OK  ]
MySQL Daemon failed to start.
mysqld started:                                            [FAILED]

Here is my mysql.log file:

[root@lincl1161 xxxx]# tail /var/log/mysqld.log
170314  3:41:06 [Note] /usr/libexec/mysqld: Normal shutdown
170314  3:41:06 [Note] Event Scheduler: Killing the scheduler thread, thread id 1
170314  3:41:06 [Note] Event Scheduler: Waiting for the scheduler thread to reply
170314  3:41:06 [Note] Event Scheduler: Stopped
170314  3:41:06 [Note] Event Scheduler: Purging the queue. 2 events
170314  3:41:08 [Warning] /usr/libexec/mysqld: Forcing close of thread 3233579  user: 'admin'
170314  3:41:08 [Warning] /usr/libexec/mysqld: Forcing close of thread 3233578  user: 'admin'
170314  3:41:08 [Warning] /usr/libexec/mysqld: Forcing close of thread 3233575  user: 'admin'
170314  3:41:08 [Warning] /usr/libexec/mysqld: Forcing close of thread 3233574  user: 'admin'
170314  3:41:09  InnoDB: Starting shutdown...
170314  3:41:09  InnoDB: Waiting for 48 pages to be flushed
170314  3:41:10  InnoDB: Shutdown completed; log sequence number 53460683633
170314  3:41:10 [Note] /usr/libexec/mysqld: Shutdown complete
170314 03:41:10 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

And the YUM log (the server uses Plesk):

[root@lincl1161 xxxx]# tail /var/log/yum.log
Jan 15 04:30:07 Updated: httpd-tools-2.2.15-56.el6.centos.3.x86_64
Jan 15 04:30:08 Updated: httpd-2.2.15-56.el6.centos.3.x86_64
Jan 15 04:30:08 Updated: 1:mod_ssl-2.2.15-56.el6.centos.3.x86_64
Jan 31 03:47:55 Updated: policycoreutils-2.0.83-30.1.el6_8.x86_64
Jan 31 03:48:07 Updated: selinux-policy-3.7.19-292.el6_8.2.noarch
Jan 31 03:48:45 Updated: selinux-policy-targeted-3.7.19-292.el6_8.2.noarch
Jan 31 03:48:49 Updated: psa-phpmyadmin-4.6.6-cos6.build1205170125.17.noarch
Jan 31 03:49:52 Updated: psa-selinux-12.5.30-cos6.build1205170125.17.noarch
Mar 14 03:40:51 Updated: mysql-libs-5.5.54-36.x86_64
Mar 14 03:40:54 Updated: mysql-5.5.54-36.x86_64
Mar 14 03:41:06 Updated: mysql-server-5.5.54-36.x86_64

No manual changes in server nor Plesk configurations have been made since months. Any help will be appreciated.


texts: Generate the statistics of a table after truncating it and then repopulate it?

texts:
Is it necessary to generate the statistics of a table after truncating it and then repopulate it?
I have a table with several million rows in Oracle.Every week new data is recharged, truncating the table and then loading the data.

Each time a new data load is made, I must generate the statistics again. Should I also rebuild the indexes?
Some columns are encrypted with TDE.
Thank you


texts: Migrating from phsyical Mysql to virtual instance

texts:
We currently have a physical db server on premise, running mySql version 5.6. We have another instance set up on an Azure virtual machine, as we are in the process of migrating our data center to Azure.

We don't have a dedicated DBA, and we are following the steps in this tutorial from the mySql site:

https://dev.mysql.com/doc/refman/5.7/en/replication-howto-additionalslaves.html

We keep failing on the 2nd step of copying the data directory, is there a workaround for this, or a better way to go about replicating?


texts: Sort one column , based on that sort another column in Oracle

texts:
I have a table :

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

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

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

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

Any help would be appreciated!.
Thanks.


texts:
Looks like this issue is just the wrong version of MySQL, need to go to 5.5

So I have a backup of an offsite source SQL database.  I am not sure how they are preforming the data dump but I am trying to find out.  This database needs to be imported on the new computer.  Every time I try to run the commands below I get the error (at the bottom of the post).  I want to be able to load this data with out an error.  What do I need to do to get the data to load correctly?  So far none of the tables load at all from the dump.

mysql -u username -p database_name < file.sql

I get the error 1064.  I also tried to load this in MySQL with

source \home\user\Desktop\test.sql;

MySQL v12.22 Distrib 4.0.26-nt-max, for Win32 (ia32)
Protocol
version, 10
Client characterset, latin1
Server characterset, latin1
Windows XP SP3
InnoDB is coming up at the type in show create table tablename;

  ERROR 1064: You have an error in your SQL syntax.  Check the manual
  that corresponds to your MySQL version for the right syntax to use
  near 'DEFAULT CHARSET=latin1' at line 1

when trying to add a different table

  ERROR 1074: Too big column length for column 'City' .  Use BLOB instead

ok I kept messing with this, the following code works when I remove, DEFAULT CHATSET=latin1

CREATE TABLE `aaaa` (`MYID` int(11) DEFAULT NULL) ENGINE=MyISAM;


texts: Why does there have to be a tradeoff between optimizing a DB for transactional data and optimizing a DB for analytical data?

texts:
I'm a data scientist who's more on the math and modeling side of things than the engineering side, and I don't know much about the logic, data structures and design principles that underly database design, beyond various platitudes like "Use a relational DB for structured data and a NoSql DB for unstructured data", etc...

I often hear that you have to choose whether a db is going to be used for transactional data that gets frequent updates and requires low latency, or wether it is destined to be more of an analytical database that gets occasional large batch updates and where latency is not a constraint. It is usually implied that there is some tradeoff that has to be decided between going with one or the other?

What I don't understand is why is this tradeoff either/or choice inevitable? I've worked with OLAP cubes (true OLAP platforms, not ROLAP) that could handle near realtime updates and commits to the main data cube from users, as well as batch processing for some of the larger modeling tasks, and could also do realtime slicing and dicing, and aggregation/disaggregation on the fly of various metrics based on predefined aggregation rules and hierarchies.

If such a system is possible (the platforms I worked with have been around since the mid-2000s), why is a backend that can handle both transactional data and analytical data such a difficult to attain objective?


texts: Bad Practice to Store Calculated Data in Database?

texts:
Is it bad practice to store calculated data in each row, or is it better to calculate at the application layer with every read from the database.

Storing in the database avoids the need to calculate multiple times, but if an error is made then data needs to be updated rather than just changing the application level calculations.

I think the latter is better, but is there a general rule of thumb?

I need to, for example, calculate total daily nutritional intake of foods. So various portions of energy of foods. I can either calculate the portion energy based on the corresponding food and store the energy of each portion in the portions table OR I can calculate from the join with the corresponding food every time.

You can imagine if you had to calculate yearly averages, monthly averages, daily averages, etc. for a long period of time it could get quite unwieldy.

What about using materialized views that would get recomputed every time old data, say a week or older, gets updated based on a trigger or something along those lines?


texts: The pg_wal directory on a standby server does not get auto-purged

texts:
I have a classic master-slaver PG (version 10) architecture. Despite the fact the parameter wal_keep_segments is set to 200, the pg_wal directory on the standby server is not purged and keeps filling in. Do you have any ideas ? For information, I don't have this issue on the primary server.

Both primary and standby have the same configuration.

On the master:

select * from pg_stat_replication ;
-[ RECORD 1 ]----+----------------------------------------------
pid              | 5399
usesysid         | 16387
usename          | replication
application_name | walreceiver
client_addr      | XXXXXXXXXXXX
client_hostname  | XXXXXXXXXXXX
client_port      | 56780
backend_start    | 2018-11-05 10:18:50.280663+00
backend_xmin     |
state            | streaming
sent_lsn         | 71/E3000000
write_lsn        | 71/E3000000
flush_lsn        | 71/E3000000
replay_lsn       | 71/E3000000
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
-[ RECORD 2 ]----+----------------------------------------------
pid              | 10175
usesysid         | 16389
usename          | barman_replication
application_name | barman_receive_wal
client_addr      | XXXXXXXXXXXX
client_hostname  | XXXXXXXXXXXX
client_port      | 42572
backend_start    | 2018-11-12 03:09:03.715933+00
backend_xmin     |
state            | streaming
sent_lsn         | 71/E3000000
write_lsn        | 71/E3000000
flush_lsn        | 71/E3000000
replay_lsn       |
write_lag        | 00:00:02.516016
flush_lag        | 00:00:02.516016
replay_lag       | 06:28:11.482478
sync_priority    | 0
sync_state       | async

On the standby:

select * from pg_replication_slots ;
-[ RECORD 1 ]-------+------------
slot_name           | barman
plugin              |
slot_type           | physical
datoid              |
database            |
temporary           | f
active              | f
active_pid          |
xmin                |
catalog_xmin        |
restart_lsn         | 16/9F000000
confirmed_flush_lsn |


texts: Using Postgres to return a selection based on frequency distribution

texts:
I am not even sure if this is a possibility so my apologies if I'm barking up the wrong tree; I am ignorant of Postgres's limitations as I am self-taught.
This is hypothetical, but if I had a table like so:
dice_rolls

roll_1
roll_2
sum

1
1
2

1
2
3

1
3
4

1
4
5

1
5
6

1
6
7

2
1
3

2
2
4

2
3
5

2
4
6

2
5
7

2
6
8

3
1
4

3
2
5

3
3
6

3
4
7

3
5
8

3
6
9

4
1
5

4
2
6

4
3
7

4
4
8

4
5
9

4
6
10

5
1
6

5
2
7

5
3
8

5
4
9

5
5
10

5
6
11

6
1
7

6
2
8

6
3
9

6
4
10

6
5
11

6
6
12

we can see that the value '7' for sum appears most frequently. If we plotted the frequency distribution of the sum, it would look something like this:
               x
            x  x  x
         x  x  x  x  x
      x  x  x  x  x  x  x
   x  x  x  x  x  x  x  x  x
x  x  x  x  x  x  x  x  x  x  x

2  3  4  5  6  7  8  9  10 11 12

Therefore, we can assume that a truly random selection of this dataset will follow this distribution pattern. What I'm interested in is psuedo-random DISTINCT selection of N rows that attempts to normalize the distribution based on the sum column.
E.g.,
SELECT * FROM dice_rolls ORDER BY RANDOM() LIMIT 5;

is what I have now. This query is most likely to contain rows where the sum is 5, 6, 7, 8, 9. How would I go about using a custom ORDER BY function that would be significantly more likely (or even strictly) to, return rows where the sum has an even chance of being 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, or 12?


texts: Improving the performance of procedures showing warnings for implicit conversions

texts:
We have recently migrated from SQL Server 2008 R2 to SQL Server 2012 Standard Edition. We and witnessing poor performance related to quite a number of stored procedures running against a database that is not that big in size (8 GB).

While analyzing the plans we have seen lot of warnings pop ups for eg:

  CONVERT_IMPLICIT(int,[DBNAME].[dbo].[tblname].[col1] as
  [s].[col1],0) CONVERT_IMPLICIT(nvarchar(100),[DBANME].[dbo].[tblname2].[col1]
  as [s].col1],0)

I have a few questions related to above.

How can I find the mismatch for those datatypes in the warnings shown in the plan? Is there any script or do I have to manually check the data types for each column?
When we use index hints, the query performs better. Does that mean that when the above warnings are received, the optimizer won't use available nonclustered indexes? If yes, why so? Also, will using index hints be a benefit in the future or harm?

We have already conveyed these to the developers to change or look at data types for any mismatch. Is there be any better approach to solve this problem?

The server and database collation are same: SQL_Latin1_General_CP1_CI_AS
Statistics were updated using Ola's script a couple of hours back
The actual plan can be found here


texts:
I've recently installed MySQL server and client 5.5.28 through rpm packages on an openSUSE machine, and I was not able to locate my.cnf file. I look into all directories mysql --verbose --help | grep -C3 my.cnf printed which was /etc/my.cnf (no such a file), /etc/mysql/my.cnf(no directory as mysql), usr/etc/my.cnf (don't have etc in my usr) and ~/.my.cnf (doesn't exist). Then I tried find / -name *.cnf which gave me:

/usr/share/doc/MySQL-server-standard-5.0.24a/my-huge.cnf
/usr/share/doc/MySQL-server-standard-5.0.24a/my-innodb-heavy-4G.cnf
/usr/share/doc/MySQL-server-standard-5.0.24a/my-large.cnf
/usr/share/doc/MySQL-server-standard-5.0.24a/my-medium.cnf
/usr/share/doc/MySQL-server-standard-5.0.24a/my-small.cnf
/usr/share/ssl/openssl.cnf
/usr/share/mysql/my-large.cnf
/usr/share/mysql/my-huge.cnf
/usr/share/mysql/my-innodb-heavy-4G.cnf
/usr/share/mysql/my-medium.cnf
/usr/share/mysql/my-small.cnf

I want to edit my.cnf file to practice replication and high availability. I read somewhere that having my.cnf file won't be necessary to use MySQL well not in my case... so I was thinking to rename one these my-*.cnf to my.cnf and put it in the locations that MySQL would look for my.cnf file. But I'm not sure first if this would work, and second which one to pick to be able to practice replication.

I don't want to make it complex for myself, so I was thinking to take the medium one... Am I doing the right thing?


texts:
I'm using MySQL 5.5 + Opensuse 12.3 .
After face an issue with mysqldump (appear be a bug with OpenSuse Mysql distribution) I have looking for others backup solution.

I found the xtrabackup from percona which is very nice solution .
I tested it with successfully to backup and restore .

But, it don't appear to be complete as I expected , they don't backup the users/grants and the configuration file (my.cnf)...
With mysqlpdump I user this shell script:

mygrants()
{
  mysql -B -N $@ -e "SELECT DISTINCT CONCAT(
    'SHOW GRANTS FOR ''', user, '''@''', host, ''';'
    ) AS query FROM mysql.user" | \
  mysql $@ | \
  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'
}

and with xtrabackup I believe I will keep using this "complement" .

Looking at xtrabackup documentation, I found nothing about this subject.

What I need to know:

There is some way to include this (grant and my.cnf) into xtrabackup automatically?
If not, running my script to get the grants and copy of my.cnf is enougth as backup from my database? or I missing something here?


texts: SQL Server 2008 Script Changes from Database Diagram

texts:
I have a database diagram which I have made several changes to using SSMS, however instead of committing the changes I want to generate a SQL script to apply them instead. Is there a simple way to do this using the GUI?

I have done a Google search, but could not find the answer.


texts: How do I get spatial index feature if I mainly use innodb?

texts:
My current strategy is to create another table in myisam that contains those spacial information. Then if I want to find 20 closest business, I'll just use join.

Is this a good idea?


texts: Migrating a Mariadb/wordpress site with only .frm files

texts:
My server crashed. My /home /www and lib/mysql were on a different hard disk so I have all the data. The original server was Debian 9 with Mariadb and an apache webserver. I planned to move to mysql so I have a debian 10 server with mysql. I also have another debian 9 server with Mariadb so that I can use sqldump to move my databases. All my databases moved over fine, except one that was a wordpress database. That directory only has .frm files in it, there are no .MYD or .MYI files. I tried copying them to /var/lib/mysql of the Deb9 server, changed the permissions and ownership. However, mysql dump gives the following error message

mysqldump: Got error: 1932: "Table 'dbname.tablename' doesn't exist in engine" when using LOCK enter code here

I tried copying them as is to the MYSQL server too, the server did not see the tables. In the MariaDB server command line interface it shows the tables. However when I try to view the contents of any table it says

Error 1932 (42S02): Table 'dbname.tablename' doesn't exist in engine

I get the same error when I try repair table.

Any ideas on how I can get this moved?


texts:
I have the following requirements (as part of a larger system).

In the database, I need to maintain a list of parts, suppliers who sell those parts.

Many suppliers can sell the same part and each part can be sold by many suppliers. However, the prices vary.

In my system, I should be able to keep track of the one, and only one, supplier we purchase a specific part from.

The way I figure it, I need two relationships on the entities Supplier and Part. One relationship Sells recording that the particular supplier carries the particular part and vice versa, and another relationship Purchase that records who we purchase an part from.

However, I don't record the attribute price, twice. I think that would be bad practice. Therefore, I ask, can I relate one relationship to another?

I've included a sample diagram below. The dashed line is what I'm not sure about.


texts: Database schema design for dynamic CRM with inter-account interaction

texts:
In a traditional CRM, we have some basic objects such as users, organisations and contacts. The users belong to an organisation's CRM account.

A very basic schema might look like this:

User
----
id
account_id
username/email
password
role
[account permissions type field]

Users belong to accounts:

Account
-------
id
name
[license type field]

Users add affiliated organisations (customers, suppliers, subcontractors):

Organisation
------------
id
name
address
account_id

Users also add contacts within these affiliated organisations:

Contact
-------
id
organisation_id
name
email
phone_number
role
[account_id?]

Suppose you had a customer who also had an account within the same CRM, you have already added this organisation and some contacts to the database. What would be the best practice for the following:

ability to add static organisations and contacts same as traditional CRM
where organisation entries match an existing account with the CRM and the 2 organisations wish to share some B2B type data /communications direct through the CRM, allow them to connect
where an account is connected to another account, the static 'organisation' entry in the database is replaced with a live connection to  the other organisations account.
when the account owner gives permission, "contact" entries are updated with the equivalent users live information for connected accounts.

The User-Account relationship is synonymous with the Contact-Organisation relationship, presumably we would need to build a relationship between Account-Organisation and User-Contact. My question is what would be the ideal design/what are the design considerations for implementing this hybrid static CRM/social environment?


texts: What are the problems with sharing an Access database through online storage service, like Dropbox?

texts:
I have a desktop database I created in Access. I'd like to share the database with a single collaborator, who can have full privileges to the database (table access). However, my collaborator is much less tech-savvy so to make it easier for him, I have set up forms to make table updates, so table access is not a must for him. With that in mind, I am willing to split up the database if this would be a better option.

I had considered sharing the database with him over a Sharepoint website, but have little experience with this and would not know where to begin. I also do not think it would be worth the (relatively low) monthly price to share with a single person. I do not expect our team to grow either, so there is no need to prepare for this. I think the best option is to share the database on Dropbox so that we both have the same version to work with. I've read some forums posts on doing those and all seem to discourage sharing Access databases like this. However, I've had a hard trouble understanding, as I am a novice. There is no need for us to both be able to work on the database at the same time, but I have no idea how I can restrict access to a single user at a time. Can you please explain what the problems are with sharing an Access database in simple terms and what workarounds there is, if any?

Please don't suggest other options as I am trying to keep this project as simple as possible and my experience with SQL is minimal. Plus, I need to keep this simple for my collaborator as well and this is a sort of personal project I don't have a ton of time and resources for.

Thank you for the help!


texts: Can a complex primary key made up of 4 fields have only one of this fields set to AUTO INCREMENT?

texts:
I have a complex primary key, I wonder if what I write in the title is possible:

I have a look up table with 4 fields (they should compose the primary key of the table):

1) an INT column;

2) Another INT column;

3) A YEAR column;

4) another INT column which should be AUTO_INCREMENT.

The 1) and 2) fields are also FOREIGN KEYS of another table, and I need them to be so.

How can I handle this?


texts: Item not automatically selected in intellisense dropdown

texts:
I have intellisense enabled in SSMS. When i start typing, I do see the intellisense dropdown with list of options but the option that matches what i am typing is not automatically selected. I have to use down arrowkey to select the item and then hit enter to auotcomplete. Is there any setting in SSMS to make intellisense behave the way we have it in Visual Studio?

Thanks


texts: Restore database to point in time from full backup only

texts:
I have a database with recovery mode set to FULL. It's a dev database and I have two full backups dated 19th and 27th i.e. latest.

One of the developer did some changes on 26th and I want to rollback those changes and want to restore database at a state of 25th.

restore database MyDb from disk = 'I:\MSSQL\DATA\MyDb_20160127.bak'
with recovery, stopat = '2016-01-25 10:38:15.280',
move 'MyDb' to  'I:\MSSQL\DATA\MyDb.mdf',
move 'MyDb_log' to 'I:\MSSQL\DATA\MyDb.ldf'

  The STOPAT clause specifies a point too early to allow this backup set
  to be restored. Choose a different stop point or use RESTORE DATABASE
  WITH RECOVERY to recover at the current point.

I am using SQL Server 2005


texts: SQL Server 2016 CU2: Severity level 20 errors for DML on restore of AX Dynamics 7 database with Change Tracking

texts:
Problem:
After we restore a database to a new environment, we are unable to perform DML operations (e.g. update the record) in some specific tables which have change tracking enabled.
Details:
We have SQL Server 2016 CU2 installed. We have a backup from SQL Server Azure and restored it on SQL Server 2016 CU2. It is a database from AX Dynamics 7 ERP.
Once we restore it, we are getting severity level 20 errors in SQL Server log when executing DML statements for some specific tables which have change tracking enabled.
If we disable the change tracking, we are able to execute DML statements without any issue. If we drop the table and recreate them with the script from previous object, the issue permanently vanishes. It then starts working with change tracking enabled as well.
Question:
How can we resolve this without disabling change tracking and without dropping and recreating the problematic tables?


texts: How to put result of SQL into bind variable

texts:
I'm trying to put the result of the query into a bind variable like this, but doesn't work:

var teste number;
exec :teste := (select trunc(avg(e.salary),2) from hr.employees e);

How I make it to work? I don't want to use a PL/SQL block.


texts:
On a particular test we're doing with Microsoft Azure, we are seeing a wait_category called: PAGE LATCH (non-I/O) that we're unfamiliar with.

In our test, CaptureSeconds = 1595 and for PAGE LATCH (non-I/O) it reports:

wait_time_ms = 126666216

wait_time_ms_per_sec = 79364

max_waiting_tasks = 13086110

average_wait_time_ms = 9

max_Wait_time_ms = 2526

Can someone tell me what PAGE LATCH (non-I/O) wait category is and whether this is indicating a problem of some sort?

Thanks,

RW


texts: Can data streams from Informatica to SQL Server be multi-threaded?

texts:
Suppose we have a database server, with SQL Server on it.  And we are moving data from another database server, through our Informatica server, and onto that SQL Server database server.  And our SQL Server database server has four processors.  Is it possible to force the connections to be multi-threaded so it sends data quicker?  How can this be done?

Right now, only one of the processors is being used on the SQL Server database server.


texts: Shoudn't DBCC SHIRNKFILE(Data_File, 0) command shrink data file to the last allocated extent?

texts:
I have a datafile with the following parameters:

When I execute DBCC SHRINKFILE (DBAtools_data,1), it shows the following resuls:

I am OK with that. My complaint is about DBCC SHRINKFILE (DBAtools_data,0) command. When I run it nothing changes. I thought that it will shrink the datafile to the last allocated extent, in order words the same result as above. Could somebody clarify it for me?


texts: LDF files vs. TRN files vs. Tempdb.MDF+LDF files.:

texts:
We have a dilemma…
We have all of our MDF and LDF files in the same…\192.168.6.22\d$\Program Files\Microsoft SQL Server\DATA\Production...folder.
We have all of our TRN and BAK files on the…\192.168.6.22\r$...drive.
We have read several articles stating that our Tempdb.MDF+LDF files need to be relocated to a different drive.
We have read several articles stating that our Userdb.LDF files also need to be relocated to a different drive.

http://www.brentozar.com/archive/2009/02/when-should-you-put-data-and-logs-on-the-same-drive/
http://www.tech-recipes.com/rx/19178/sql-server-20082008-r2-move-tempdb-to-improve-performance/

This system is in a virtual environment, so it’s more than likely all of these drives are all on the same array anyway, and if that’s the case would relocation make any difference?
If they are on separate arrays, or if it will make a difference, then should there be four separate locations or five, and do each of these need their own array?.:
a.    One for the MDF files; (1)
b.    One for the LDF files; (2)
c.    One for the BAK and TRN files; (3)
d.    One for the Tempdb.MDF+LDF files; (4) or…
…One for the Tempdb.MDF file, and another for the Tempdb.LDF file; (5)


texts: How to fix error recording sp_whoisactive results to table?

texts:
Have a sql agent job modeled from here that uses sp_whoisactive to capture results to a table. 99% it works fine but every now and then the sql agent job will fail with this error:
Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153)  Violation of PRIMARY KEY constraint 'PK__#ADDF8B9__69B13FDC8C10EA7F'. Cannot insert duplicate key in object 'dbo.@blockers'. The duplicate key value is (623). [SQLSTATE 23000] (Error 2627)  The statement has been terminated. [SQLSTATE 01000] (Error 3621)  Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153)  Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153).  The step failed.
Here is table def
USE [Maint]
GO

/****** Object:  Table [dbo].[WhoIsActive]    ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[WhoIsActive](
    [dd hh:mm:ss.mss] [varchar](8000) NULL,
    [session_id] [smallint] NOT NULL,
    [sql_text] [xml] NULL,
    [sql_command] [xml] NULL,
    [login_name] [nvarchar](128) NOT NULL,
    [wait_info] [nvarchar](4000) NULL,
    [tran_log_writes] [nvarchar](4000) NULL,
    [CPU] [varchar](30) NULL,
    [tempdb_allocations] [varchar](30) NULL,
    [tempdb_current] [varchar](30) NULL,
    [blocking_session_id] [smallint] NULL,
    [reads] [varchar](30) NULL,
    [writes] [varchar](30) NULL,
    [physical_reads] [varchar](30) NULL,
    [query_plan] [xml] NULL,
    [used_memory] [varchar](30) NULL,
    [status] [varchar](30) NOT NULL,
    [tran_start_time] [datetime] NULL,
    [open_tran_count] [varchar](30) NULL,
    [percent_complete] [varchar](30) NULL,
    [host_name] [nvarchar](128) NULL,
    [database_name] [nvarchar](128) NULL,
    [program_name] [nvarchar](128) NULL,
    [start_time] [datetime] NOT NULL,
    [login_time] [datetime] NULL,
    [request_id] [int] NULL,
    [collection_time] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

What is causing this error?
sp_whoisactive version = v11.11.


texts: .NET executes a SQL query and Active Monitor shows multiple rows blocking each other

texts:
I use .NET to execute SQL operations on SQL Server 2014, here's the code used:

using(SqlConnection conn = new SqlConnection(connectionString)){
    //https://stackoverflow.com/questions/1880471/capture-stored-procedure-print-output-in-net
    conn.InfoMessage += new SqlInfoMessageEventHandler(logSqlMessages);
    conn.Open();
    using(SqlCommand stmt = new SqlCommand{
        Connection = conn,
        CommandText = sql,
        CommandTimeout = 30000 // The time in seconds to wait for the command to execute. The default is 30 seconds.
        //,CommandType = CommandType.StoredProcedure
    })
    {
        affectedRecords = stmt.ExecuteNonQuery();
    } // using stmt
} // using conn

When I look at Active Monitor, there are tens of rows referencing  the same operation. They all have the same session_id, some of them have Task State running and most of them are suspended. Some of them have LastWaitTime CXPACKET and most are PAGEIOLATCH_SH.

I also ran a query on SQL Server and same behavior happened on Active Monitor.

Maybe it's a normal behavior of it, but it's odd that a SELECT operation creates multiple rows and blocks itself like that. Any idea of what may be causing it?


texts: How do I migrate Postgres database using an intermediate file

texts:
I have a PostgreSQL v9.5.23 database on an Ubuntu 16.04 server that I want to migrate to a new Postgres 12.4 installation on an Ubuntu 20.4 server.
The network pipe recommended by the Postgres docs won't work for me.  The docs also unhelpfully say "Or you can use an intermediate file if you wish" without giving an example of how to do this.
This is what I've tried.  On the originating server, I ran
$ sudo pg_dump -C {dbname} > ~/dumpfile.sql

to create the dump file.
On the target server, I get this error:
$ sudo -u postgres psql {dbname} < ~/dumpfile.sql
could not change directory to "/home/username": Permission denied
psql: error: could not connect to server: FATAL:  database "{dbname}" does not exist

Of course the target database does not exist; I used the -C option when creating the dumpfile so that it would be created automatically during upload, but evidently this is not automatic.  I read through the man page for psql, but I didn't see a flag that would make it recognize the "create database" part of the dump file.  Web searching didn't turn up anything, and naturally the Postgres documentation is useless.
How do I upload the file and have the "create database" portion recognized?
Edit
The could not change directory to "/home/username": Permission denied line is not the error. This is just something Postgres always does on my installation.
I have tried the upload command from the /tmp/ directory with 777 permissions on the file.  It still does not work:
/tmp$ ls -Alh
-rwxrwxrwx 1 username group 5.1M Oct 21 11:07 dumpfile.sql
/tmp$ sudo -u postgres psql {dbname} < /tmp/dumpfile.sql
psql: error: could not connect to server: FATAL:  database "{dbname}" does not exist


texts: Azure SQL Database vs. error 8152 enhancement

texts: How can I take advantage of the enhancement for error 8152 (https://blogs.msdn.microsoft.com/sql_server_team/string-or-binary-data-would-be-truncated-replacing-the-infamous-error-8152/) in Azure SQL Database? Even when logged in as the Server admin, I can't set the trace flag.


texts: How to fetch file path from a folder through PL/SQL

texts:
We upload files on our library on weekly basis. I wanted to fetch the latest uploaded file path/URL from a folder through PL/SQL, but not sure if it's possible.

If possible, please suggest what should I do.


texts:
I have a MS SQL Server 2012 installation on which I am restoring a SQL 2005 .bak which includes some full-text catalogues

The original .bak has no conflicts with 2012 (as the migrate advisor was used to clear all of these). Upon running a TLog backup I receive the following error message:

Executing the query "BACKUP DATABASE [databaseName] TO  DISK = N'..." failed with the following error: "An error occurred while processing 'BackupMetadata' metadata for database id 5 file id 1. Inconsistent metadata has been encountered. The only possible backup operation is a tail-log backup using the WITH CONTINUE_AFTER_ERROR or NO_TRUNCATE option. BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

After much searching I am at a loss as to why. Any suggestions greatly appreciated.


texts: How To Exclude Queries Returned From mysqldumpslow Command

texts:
I would like to ignore queries in the mysql-slow log that originate from running the command mysqldumpslow by using SQL_NO_CACHE as the trigger word.
My experience using regex are limited at best and I'm unable to find any examples in the docs.
I've tried a few variations of the following:
mysqlslowdump -g '!(SQL_NO_CACHE)' /path/to/my/log
mysqlslowdump -g '^!(SQL_NO_CACHE)$' /path/to/my/log

... but always get back:

Died at /usr/bin/mysqldumpslow line 162

What is wrong in my regex?


texts: MySQL consuming memory, seeking advice on how to configure it

texts:
I have a small VPS that handles my 3 personal WordPress websites.

At a certain time MySQL started consuming more and more memory and actually (for months) the server has been running at 99% RAM usage with MySQL eating more than 2 GB alone.

My VPS is an Ubuntu 14.04.05 with MySQL 5.7.23 (I upgraded to avoid the memory eating bug), 2 amd64 CPU@3.9GHZ and 3 GB of RAM.

The webserver is configured by Virtualmin, with little to no modifications applied by myself (read after).

I tried MySQLTuner, and some other scripts but nothing helps.

I have only these 3 websites and every time the traffic spikes (these are personal websites, so "spikes" are maybe 3 users at the same time) the VPS crashes.

Can you please help me configure it to use lesser RAM or diagnose if there is an issue?
I'm thinking about switching to MariaDB, but without changing the configuration I don't think that anything will change.

All the relevant informations are in this pastebin (posted Aug 7, 2018) for readability:

https://pastebin.com/UasHBcH1

According to MySQL Calculator it should only require 293 MB and I don't have so much visitors to justify this usage.


texts: Using TEXT or even MEDIUMTEXT for all text columns in a MySQL table?

texts:
I'm using a PHP script to insert millions of rows into a MySQL. Each of the rows contain a diversity of columns that are very different across the rows.

Basically I'm using MySQL as a key-value pair store.

While I can make the column to auto-adjust its size according to the length of the string to be inserted on the fly, it may not be a good idea when the table has millions of rows.

For example, with 5 million rows in the table, the next insert has a column with over 5,000 characters while that column is defined as VARCHAR(127) in the table. Re-defining the column in the job would be anything but desirable. Is it? Cause' it bogs things down and it has to change that particular column of all the 5 million rows. Right?

So I'm thinking of creating each column as a TEXT or even MEDIUMTEXT column in the first place. It is after I have done all the inserts that the columns will be re-defined according to the longest value in the column.

Therefore my question is could there be any performance issues with all the columns of the table defined as TEXT / MEDIUMTEXT? Especially for large amounts of inserts?


texts: Is there a method to count total transactions by type (read/write) by day/hour/peak?


texts: How to find MariaDb/Mysql structure differences

texts:
I have two similar databases with many tables, in one of these there was some alter commands maybe changing data types or adding some columns.

Exists a free analyzer/merging tool to check their differences? Please consider that I need only structure difference, not data differences.


texts: Could anyone explain this code to me？Leetcode #180

texts:
select distinct Num as ConsecutiveNums
from (
  select Num,
    case
      when @prev = Num then @count := @count + 1
      when (@prev := Num) is not null then @count := 1
    end as CNT
  from Logs, (select @prev := null,@count := null) as t
) as temp
where temp.CNT >= 3

I found this in
https://leetcode-cn.com/problems/consecutive-numbers/comments/
It is really difficult to understand, especially this sentence
(select @prev := null,@count := null) as t

Why can variable declaration be put there?


texts:
I have been reading about recommended SQL Server storage and RAID 5 or RAID 10 controllers have been recommended.

On my existing Database server I can see 4 Storage controllers under Device Manager.
They are listed as:

LSI Adaper (*2)
VMWare PVSCSI Controller (*2)

Are these an alternative to RAID? Or should I be using RAID for SQL Server?


texts: backups taking longer to run, has anything within sql server changed?

texts:
The following is an email from one of my colleagues about an hour ago:

  Any Idea why the backup time has gone up from 2.5 hrs to 5.5 hours
  since the 16th Sept?

  The duration has remained consistent on ourserver\REP- at around 1
  hour, and they have the exact same data on them. Has something been
  added since the 15th that would cause disk conetention?

  Any other ideas?

how can I find out where the backups are taking more time to run?

I currently can find which backups are taking longer using the following script:

;with Radhe as (
                    SELECT  @@Servername as [Server_Name],
                    B.name as Database_Name,
                    ISNULL(STR(ABS(DATEDIFF(day, GetDate(), MAX(Backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
                    ISNULL(Convert(char(11), MAX(backup_finish_date), 113)+ ' ' + CONVERT(VARCHAR(8),MAX(backup_finish_date),108), 'NEVER') as LastBackupDate
                    ,BackupSize_GB=CAST(COALESCE(MAX(A.BACKUP_SIZE),0)/1024.00/1024.00/1024.00 AS NUMERIC(18,2))
                    ,BackupSize_MB=CAST(COALESCE(MAX(A.BACKUP_SIZE),0)/1024.00/1024.00 AS NUMERIC(18,2))
                    ,media_set_id = MAX(A.media_set_id)
                    ,[AVG Backup Duration]= AVG(CAST(DATEDIFF(s, A.backup_start_date, A.backup_finish_date) AS int))
                    ,[Longest Backup Duration]= MAX(CAST(DATEDIFF(s, A.backup_start_date, A.backup_finish_date) AS int))
                    ,A.type
                    FROM sys.databases B

                    inner JOIN msdb.dbo.backupset A
                                 ON A.database_name = B.name
                                AND (A.type = 'D'  )

                    GROUP BY B.Name, A.type

                )

                 SELECT r.[Server_Name]
                       ,r.Database_Name
                       ,[Backup Type] = r.type
                       ,r.DaysSinceLastBackup
                       ,r.LastBackupDate
                       ,r.BackupSize_GB
                       ,r.BackupSize_MB
                       ,F.physical_device_name
                       ,r.[AVG Backup Duration]
                       ,r.[Longest Backup Duration]

                   FROM Radhe r

                    LEFT OUTER JOIN msdb.dbo.backupmediafamily F
                                 ON R.media_set_id = F.media_set_id

                    ORDER BY [AVG Backup Duration] desc, r.Server_Name, r.Database_Name

is it maybe something else running at the same time?
like backups to take or other types of backup (non-sql server)


texts: SQL server migration to new server from 2008 R2 to SQL 2014

texts:
I need to run the upgrade advisor before migration, so my question is where to Run the upgrade advisor ? In the old server or after restoring the DB in new server ?

As I am new to migration so need help and what all things I should take care for Migration and step by step process would be helpful.


texts: What is the fastest way to clone/copy oracle databases on the same machine?

texts:
I want to clone oracle databases on the same machine and
I need to change user etc. from e.g. PROD to DEV.
For that I use expdp/impdp, but this tends to be slow with increasing database size.
I need to do this on linux and windows machines.

Is using expdp/impdp the fastest way to do this?

Or should I use a completly different way of doing this?


texts: shrink database in always on availability

texts:
hi i was using this query for shrinking the database log file

USE Tfs_CoE;
GO
ALTER DATABASE Tfs_CoE
SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE (Tfs_CoE_Log, 1);
GO
ALTER DATABASE Tfs_CoE
SET RECOVERY FULL;
GO

it was working fine until i have added the database to always on availability group and now i get this error

  Msg 1468, Level 16, State 2, Line 4
  The operation cannot be performed on database "Tfs_CoE" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.
  Msg 5069, Level 16, State 1, Line 4
  ALTER DATABASE statement failed.
  The log for database 'Tfs_CoE' cannot be shrunk until all secondaries have moved past the point where the log was added..

  (1 row(s) affected)
  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Can anyone help me in shrinking the log file of the database which has grown to 650gb which is configured for always on availability group actual size of the database is 39gb only ?

I have checked with taking backup of transaction log even but that dont work for me the log file size remains same 650gb.


texts: Database Wrapper Design issue: No operator matches the given name and argument type(s)

texts:
I'm building a wrapper for DB access using AWS' Lambda functions Java, the issue I'm having is that Lambda parameters are JSON, thus typeless, so all incoming params are de-JSON'd into Strings, and when adding them to a PreparedStatement  using the setObject(param, value) method they all get treated as if you were using setString(param, value), this is fine for Strings and numbers, but when it comes to a parameter that is a timestamp or date, we get a parameter type mismatch like this:

org.postgresql.util.PSQLException: operator does not exist: timestamp with time zone >= character varying
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

I'm aware that passing parameter types as an extra field for the JSON might help, but I'm worried about both the extra overhead and, the need of manually adding the parameter type to the JSON, as it's currently being built "automagically" from the underlying DTO using GSON.

Can anyone figure out a different approach, perhaps on passing the parameters to the PreparedStatement.?


texts: Substring join or additional table, which is faster?

texts:
I have a case that come up regularly at work. I have many tables that use a 4 character string as a foreign key : G191

The G is a sort of category, the 19 is the year, and the 1 is an instance.
Getting all rows of category G is something we do very often. Usually like:

SELECT * FROM [Table]
WHERE Left([ID], 1) = 'G'

There is a way to get this effect without manipulating a string, by joining to a table where this category is defined:

SELECT * FROM [Table]
JOIN [Categories] ON [Table].CategoryID = [Categories].CategoryID
WHERE [Categories].Letter = 'G'

My co worker insists that the first way is more performant, and rolls his eyes at me for doing it the second way.

Which one is better? Does joining by another table really add more work then checking the first character of a string?


texts: How to send MySQL query results to outfile and standard output at the same time?

texts:
I "normally" query MySQL with

SELECT col1, col2 FROM tablename LIMIT 10;

and sometimes instead send the output to a file with

SELECT col1, col2 FROM tablename LIMIT 10 INTO OUTFILE '/path/to/outfile';

Is there a way to do both with one query?  I am trying to store a dump file of the query results while still manipulating the output (in PHP).


texts: Query Regarding Database Instance not proper

texts:
Please sort out one issue regarding SQL Server instances. I have one instance of SQL Server named 'Test\Connect' and other one is 'local' by default.

So the issue is when I connect to local and Test\Connect instances, the databases seen are the same. I am not able to see the databases which I have created in my instance Test\Connect.

But when I connect through IP\Connect (IP address of Test and same instance name) then I can see the databases.

Please help me out and let me know the process how we can sort this out.