texts: How to solve UTF8 invalid byte sequence copy errors on a restore, when the source database is encoded in UTF8?

texts:
I was given the task to migrate a PostgreSQL 8.2.x database to another server. To do this I'm using the pgAdmin 1.12.2 (on Ubuntu 11.04 by the way) and using the Backup and Restore using the custom/compress format (.backup) and UTF8 encoding.

The original database is in UTF8, like so:

-- Database: favela

-- DROP DATABASE favela;

CREATE DATABASE favela
  WITH OWNER = favela
       ENCODING = 'UTF8'
       TABLESPACE = favela
       CONNECTION LIMIT = -1;

I'm creating this database exactly like this on the destination server. But when I restore the database from the .backup file using the Restore option it gives me some of these errors:

pg_restore: restoring data for table "arena"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2173; 0 35500 TABLE DATA arena favela
pg_restore: [archiver (db)] COPY failed: ERROR:  invalid byte sequence for encoding "UTF8": 0xe3a709
HINT:  This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".
CONTEXT:  COPY arena, line 62

When I check which record triggered this error in fact some vartext fields have diacritical characters like ç (used in Portuguese, for example, "caça"), and when I manually remove them from the text in the records the error passes to the next record that has them - since when copy has an error it stops inserting data on this table. And I don't want to replace them manually one by one to accomplish this.

But it's kinda of strange because with UTF8 there shouldn't be this kind of problems, right?

I don't know how they got there in the first place. I'm only migrating the database, and I supose that somehow the database was like in LATIN1 and then was improperly changed to UTF8.

Is there any way to check if a table/database has invalid UTF8 sequences? Or any way to enforce/reconvert these characters into UFT8 so I don't run into any problems when I execute the restore?

Thanks, in advance.


texts: Bit map of yes/no answers: Should I store it as binary, blob, varchar, or int?

texts:
I am developing a web app in Laravel where I have to ask users a set of Yes/No questions and save the answers in the database. I thought that I should try and save all answers in binary rather than saving them one-by-one since the number of questions vary from time to time. Say in three questions, the user answered Y-Y-N; that would convert into "110".
Now, on the database side, should I:

save the data as is (varchar)
use binary/blob
convert to int and save sa int

Not sure which option is the best, considering that the system sometimes output 50 questions.
MySQL -V info:
mysql  Ver 14.14 Distrib 5.5.62, for debian-linux-gnu (x86_64) using readline 6.3


texts: I currently have SQL DB and Log files going to the same drive.  I would like to move the Log files to their own drive.   Is there an easy way to achieve this and will it require downtime/reboot of the server?


texts:
Kindly assist me on following upgrade issue.

My Arbiter upgrade (thru yum update -y ) has been failed with following error.

2019-10-19T07:19:25.944-0400 I CONTROL  [initandlisten]
2019-10-19T07:19:25.958-0400 F CONTROL  [initandlisten] ** IMPORTANT: UPGRADE PROBLEM: The data files need to be fully upgraded to version 3.4 before attempting an upgrade to 3.6; see http://dochub.mongodb.org/core/3.6-upgrade-fcv for more details.
2019-10-19T07:19:25.958-0400 I NETWORK  [initandlisten] shutdown: going to close listening so2019-10-19T07:19:25.944-0400 I CONTROL  [initandlisten]
2019-10-19T07:19:25.958-0400 F CONTROL  [initandlisten] ** IMPORTANT: UPGRADE PROBLEM: The data files need to be fully upgraded to version 3.4 before attempting an upgrade to 3.6; see http://dochub.mongodb.org/core/3.6-upgrade-fcv for more details.
2019-10-19T07:19:25.958-0400 I NETWORK  [initandlisten] shutdown: going to close listening so

Primary and secondary database FCV version was showing 3.4 , where as Arbiter FCV was showing 3.2 in place of 3.4

following error was also there in rs.status()

name" : "arbiter1:27017",
   "health" : 0,
   "state" : 8,
   "stateStr" : "(not reachable/healthy)",

version status .
replica01 3.4.23 Secondary
replica02 3.4.23 Primary
replica02  3.4.23 ARBITER

Configuration file of three node.

replica01 :

storage:
  dbPath: /data
  engine: mmapv1
systemLog:
  destination: file
  path: /var/log/mongodb/mongod.log
  logAppend: true
processManagement:
  fork: true
  pidFilePath: /var/run/mongodb/mongod.pid
replication:
  replSetName: productionReplica
 *********************
replica02
storage:
  dbPath: /var/lib/mongo
  engine: mmapv1
  journal:
    enabled: true
systemLog:
  destination: file
  path: /var/log/mongodb/mongod.log
  logAppend: true
processManagement:
  fork: true
  pidFilePath: /var/run/mongodb/mongod.pid
replication:
  replSetName: productionReplica
net:
  port: 27017
 #bindIp: 127.0.0.1
*******************************************************
replica03
storage:
  dbPath: /var/lib/mongo
  engine: mmapv1
systemLog:
  destination: file
  path: /var/log/mongodb/mongod.log
  logAppend: true
processManagement:
  fork: true
  pidFilePath: /var/run/mongodb/mongod.pid
replication:
  replSetName: productionReplica

Please advise how to fix the issue , Seems Arbiter always show FCV one version behind primary database .

Regards
Anurag


texts: How to prevent Extended Events from tracking system fired events?

texts:
I have employed Extended Events to track the DML and DDL operations that are being performed on the tables within a particular database. Apart from this I'm also tracking the procedures that a user(for now I'm the sole user) executes within the database which is being tracked. I'm using the event 'sql_statement_completed' for the same. It captures all the sql statements that are successfully completed, post which I filter the statements to store only Insert, Update, Delete and Drop statements (exec statements are also being stored). Below is the code pertaining to it:

CREATE EVENT SESSION <track> ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.client_hostname,
           sqlserver.sql_text,
           sqlserver.username)
    WHERE ([sqlserver].[database_name]= <database_name>
    AND [sqlserver].[is_system]=(0)
    AND (([statement] like 'INSERT%'
       OR [statement] like 'UPDATE%'
       OR [statement] like 'DELETE%'
       OR [statement] like 'DROP%'
       OR [statement] like 'ALTER%'
       OR [statement] like 'EXEC%'))))
ADD TARGET package0.event_file(
    SET filename=N'C:\ExtendedEvents\track.xel',
    max_file_size=(102400),
    max_rollover_files=(10000),
    metadatafile=N'C:\ExtendedEvents\track.xem')
WITH (MAX_MEMORY=1048576 KB,
      EVENT_RETENTION_MODE=NO_EVENT_LOSS,
      MAX_DISPATCH_LATENCY=5 SECONDS,
      MAX_EVENT_SIZE=0 KB,
      MEMORY_PARTITION_MODE=NONE,
      TRACK_CAUSALITY=OFF,
      STARTUP_STATE=ON)

Now while this has been tracking the DML and DDL statements that are being executed against the tables in the database, it has also been tracking some statements that have not been executed by the user directly(particularly some procedural statements that seem to have been executed by the system itself).

The pic below subsumes the statements(not directly fired the user) that have been tracked:

This is despite the fact that I have explicitly set     [sqlserver].[is_system]=(0) in the where predicate of Extended Events, to disable tracking of system fired statements and only track the user fired statements.

So why is it tracking system fired statements as shown in the pic above?
How to circumvent the same?


texts: Force Client to Validate SSL Certificate?

texts:
When SQL Server uses the Force Encryption with value "yes" then if in the client-side we use encrypt=true option in the connection string, then the client application tries to verify the server certificate.

My question is this how we can force the client (by the server or any way) to verify the certificate regardless of the connection string value?


texts:
I want to create a backup of my database in Postgres.
I'm using pgAdmin.

on my database I have clicked right button of mouse
after that I've selected the "Create a backup of database"

After that I've got a sequence of messages:

C:\Program Files\pgAdmin III\1.12\pg_dump.exe --host localhost --port 5432 --username "postgres" --role "postgres" --no-password  --verbose --file "E:\wfr.backup" "WFR"
pg_dump: server version: 9.1.1; pg_dump version: 9.0.3
pg_dump: aborting because of server version mismatch
pg_dump: *** aborted because of error

Why didn't it create a backup and return the error instead?


texts: "Incorrect Syntax near S" in Stored Procedure

texts:
I'm creating a SQL Server stored procedure and getting error

  Incorrect Syntax near S

I'm not able to find the exact reason of the error.

CREATE PROCEDURE RPT_IVR_CALL_SUMMARY
    (@i_APPLICATION_ID     VARCHAR(50),
     @i_SESSION_ID         VARCHAR(50),
     @i_UNIQUERIGHT_ID     VARCHAR(50),
     @i_CALLINGPROC        VARCHAR(50),
     @i_FROM_DATE          VARCHAR(50),
     @i_T@o_DATE           VARCHAR(50),
     @i_CATEGORY           XML,
     @i_CIRCLE             XML,
     @i_SUMMARIZED_BY      VARCHAR(50),
     @i_REPORT_USER        VARCHAR(50),
     @o_ERRORCODE          INT OUTPUT ,
     @o_ERRORDESCRIPTION   VARCHAR(4000) OUTPUT)
AS
    DECLARE @v_ENABLEAUDIT VARCHAR(15),
            @v_QUERY VARCHAR(15);
    DECLARE @I_SYSDATE      DATETIME;
BEGIN
    SET NOCount ON;

    BEGIN TRY
        SET @I_SYSDATE = GETDATE()

        SELECT
            CODE Code,
            CODE Description,
            SUM(OFFEREDCALLS) CallCount
        FROM
            (SELECT
                 (CASE WHEN @i_SUMMARIZED_BY = 'Circle' THEN CALLCIRCLECODE
                                  ELSE VXMLSERVER END)                CODE,
                 OFFEREDCALLS
             FROM
                 TBL_SUMMRESOLUTION
             WHERE
                 (CAST(CONVERT(VARCHAR(13), SUMMDATE, 103) AS DATETIME) - CAST(CONVERT(VARCHAR(13), @i_FROM_DATE, 103) AS DATETIME)) >= 0
                 AND (CAST(CONVERT(VARCHAR(13), SUMMDATE, 103) AS DATETIME) - CAST(CONVERT(VARCHAR(13), @i_T@o_DATE, 103) AS DATETIME)) <= 0
                 AND (CATEGORY IN (SELECT ROLEGROUPLIST.ID.query('Code').value('.','VARCHAR(50)') AS CATEGORYCODE
                                   FROM  @i_CATEGORY.nodes('/Categories/Category') AS ROLEGROUPLIST(ID))
                 AND (CALLCIRCLECODE IN (SELECT  ROLEGROUPLIST.ID.query('Code').value('.','VARCHAR(50)') AS CIRCLECODE
                                         FROM  @i_CIRCLE.nodes('/Circles/Circle') AS ROLEGROUPLIST(ID))
                ) S
   GROUP BY
       CODE;
 END TRY
 BEGIN CATCH
        SET @o_ERRORCODE = ERROR_NUMBER()
        SET @o_ERRORDESCRIPTION = ERROR_MESSAGE()
          IF UPPER(@v_ENABLEAUDIT) = 'ENABLED'
            EXEC PROC_AUDIT_TRAIL @i_APPLICATION_ID,@i_SESSION_ID, @I_SYSDATE, @o_ERRORDESCRIPTION, @v_QUERY, @i_REPORT_USER , @i_UNIQUERIGHT_ID,'RPT_IVR_CALL_SUMMARY'

          SELECT NULL
END CATCH
END


texts:
I've got a base table with transactions and I need to create a table with running totals. I need them to be per account and and also have a few running totals for each account (depending on the transaction type), and inside that, some running totals per sub-account.

My base table has these fields (more or less) :

AccountID  |  SubAccountID   |  TransactionType  |  TransactionAmount

Considering I've got about 4 types of running totals per Account/TransactionType and 2 more running totals per Account/SubAccount/TransactionType , and I've got about 2M accounts with about 10 sub accounts each, and I'm getting about 10K transactions every minute (at maximum load), how would you do it?

It's also a must that this run asynchronously via an SQL job, creating the aggregations without being part of the transactions themselves.

I'm pretty stuck using a cursor here - which takes way too long. I'd really appreciate any advice / articles that are doing more or less the same.


texts: We use MongoDB 3.6 community version, How can i get the number of hits on single collection as well as bandwith usage?

texts: Is it possible to get the number of hits on single collection as well as bandwith usage in mongodb ?


texts:
I have something like

SELECT var1,var2 FROM table where var3=X AND var4=Y order by var5 desc limit 1;

Running this query takes roughly 5 seconds on my database which is far to slow.
I was wondering if there was any way to retrieve the last row in a way similar to this

SELECT var1,var2 FROM table where var3=X AND var4=Y limit 1;

bcause that is almost instant (.03 seconds) on my database. So, if there was some way to have a query print everything in reverse order that would solve every issue I am having. I have been searching for a long while now and every answer seems to be with using ORDER BY or subqueries which are all >= 5 seconds.

EDIT The tables are not indexed.


texts: Error in setting a value in sql server trigger

texts:
I'm trying to make a trigger that checks which staff member have the least amount of assigned tasks, before adding the task, and assign it to that member.
What I came up with was something like this:

CREATE TRIGGER trgInsteadOfInsert ON helpCase
INSTEAD OF Insert
AS
declare
 @helpCaseId int,
 @problemTypeId int,
 @problemDescribtion varchar(200) ,
 @staffId int,
 @problemSolution varchar(200),
 @operatingSystemTypeId int,
 @applicationTypeId int,
 @helpCaseOpenDate datetime,
 @helpCaseclosedDate datetime;

select @helpCaseId=i.helpCaseId from inserted i;
select @problemTypeId=i.problemTypeId from inserted i;
select @problemDescribtion=i.problemDescribtion from inserted i;

set @staffId = (select Min(ID) frOM (SELECT staffId as ID, COUNT (*) FROM helpCase where helpCaseClosedDate IS NULL GROUP BY staffId)) ;

select @problemSolution=i.problemSolution from inserted i;
select @operatingSystemTypeId=i.operatingSystemTypeId from inserted i;

select @helpCaseOpenDate=i.helpCaseOpenDate from inserted i;
select @helpCaseclosedDate=i.helpCaseclosedDate from inserted i;

BEGIN

Insert into helpCase values(@helpCaseId,@problemTypeId,@problemDescribtion,@staffId,@problemSolution,@operatingSystemTypeId,@helpCaseOpenDate,@helpCaseclosedDate);
END

but I'm getting the following error for the line that I'm setting the value of @staffId in :

Incorrect syntax near ')'. Expecting AS,ID or QOUTED_ID.

Any idea what should I do!?


texts: NoSQL : Getting latest item from DynamoDB table

texts:
I'm implementing chat server currently, with dynamoDB.

Here is keys from ChatData table

Primary partition key : roomNo
Primary sort key : seq

Chat server, can created many instant rooms, approximated to 100~120 daily.
So I choosed primary partition key as room Number(roomNo), and sort by data created time(or sequential value) on seq.

I want to increase seq automatically, however, I know that DynamoDB hasn't support such as auto_increment or identity. So, I'm going to try using Timestamp or AtomicLong from my client application(JAVA 1.8).

But, each has problems.
first, Timestamp, may inserted duplicated value in table.

and AtomicLong, can increased atomic value but when I restart my application, it starts from 1 again.

Maybe if I can get maximum seq value from DynamoDB, what should I use for?
or how to change keys on table?


texts: How can I convert a key in a SQL Server deadlock report to the value?

texts:
I have a deadlock report that tells me that there was a conflict involving
waitresource="KEY: 9:72057632651542528 (543066506c7c)"

and I can see this:
<keylock hobtid="72057632651542528" dbid="9" objectname="MyDatabase.MySchema.MyTable" indexname="MyPrimaryKeyIndex" id="locka8c6f4100" mode="X" associatedObjectId="72057632651542528">

within the <resource-list> element.
I want to be able to find the actual value for the key (id = 12345, for example). What SQL statement would I need to use to obtain that information?


texts: In SQL Server, how do I relate each date in a set to the subsequent date from the same set?

texts:
I have a large table with a DATETIME2 column. I would like to write a query that relates each distinct date in that column to the date from the same column that immediately follows the first date. I suspect that I need to use a self-join, but I'm unsure about what to do next. How do I write such a query?
I don't have any preferences about how the case of the last date is handled. Its NextDate can be set to NULL or the row can be excluded.
Example:
DROP TABLE IF EXISTS #Dates

CREATE TABLE #Dates (
    MyDate DATETIME2 NOT NULL
)

INSERT INTO #Dates (MyDate) VALUES ('20210101')
INSERT INTO #Dates (MyDate) VALUES ('20210102')
INSERT INTO #Dates (MyDate) VALUES ('20210103')
INSERT INTO #Dates (MyDate) VALUES ('20210104')
INSERT INTO #Dates (MyDate) VALUES ('20210105')

SELECT
    MyDate AS CurrentDate
    -- , ??? AS NextDate
FROM #Dates
ORDER BY CurrentDate

How do I get:
+-------------+-------------+
| CurrentDate |  NextDate   |
+-------------+-------------+
|    20210101 |    20210102 |
|    20210102 |    20210103 |
|    20210103 |    20210104 |
|    20210104 |    20210105 |
|    20210105 |    NULL     |
+-------------+-------------+


texts: Manipulating duplicate records in access

texts:
I have a table with data as follows:

Field1  Field2 Field3
10      10     20
10      20
10      30
1       0
20      1      1
20      1
20      1
30      2      2
30      2
30      2

My goal is to find duplicate values of field1, and if said field is a duplicate, to average field2's data into field3. For instance Field1's duplicates are 10, thus Field 3's value should return 20.

How would I go about doing this?

To the best of my knowledge, something similar of Java would be written as follows (bear with me my Java is rusty)

{ int field1 = field1;
  scanner.in();
  int field2 = field2;
  scanner.in();

  int sum = sum;

  int count = count;

  int field3 = field3;

  while (field1.hasnext());
  {
  if(field1 = field1)
    {
        sum = sum+field2;
        count++;
    }
else
    {
        out.print("null");
    }
    }

    field3 = sum/count;

    System.out.println(field3);
    }


texts: php/mysql database redesign and migration: changing databases to tables

texts:
I wrongly designed my application to have one database to each user. each user had 3 similar tables. I now want to have one database and 3 tables only; where i will use the database name in the old databases as a reference in the new system. There was another database called "users" in the old database that stored the database names. I'm done with the schema design of the new database and now left with the migration.

The trick here is that I have 3 db connections. I first connect to the users database and userinfo table, pick up the database_name in a loop, use it to connect each old db and further connect to personal, accounts and games table.

After picking up the tables, i will like to populate/join it with the new Database (DATA_ONE) and the tables whiles i append the old database_name to the new tables. Any help on this or is there a better way to do this? Thanks much

<?php

$dbhost = "localhost";
$dbname = "users";
$dbuser = "root";
$dbpass = "*****";

$conn1 = mysql_connect($dbhost, $dbuser, $dbpass, TRUE) or die("MySQL Error: " .  mysql_error());
$conn2 = mysql_connect($dbhost, $dbuser, $dbpass, TRUE) or die("MySQL Error: " . mysql_error());
$conn3 = mysql_connect($dbhost, $dbuser, $dbpass, TRUE) or die("MySQL Error: " . mysql_error());

mysql_select_db($dbname,$conn1) or die("MySQL Error: " . mysql_error());

$query  = "SELECT database_name FROM userinfo";
$result1 = mysql_query($query,$conn1);
while($row = mysql_fetch_array($result1, MYSQL_ASSOC))
{
$database_name =$row['database_name'];
$conn2 = mysql_connect($dbhost, $dbuser, $dbpass) or die("MySQL Error: " . mysql_error());
  $db = mysql_select_db($database_name ,$conn2) ;
     // now, pick personal, accounts, games tables of user and populate new database and    tables.
  // this is where i need help.
    }

?>


texts: Does running a COUNT() statement using NOCOUNT ON; consume much

texts:
On millions of rows.. Is it worth caching the total amount or turning NOCOUNT OFF; instead of requesting it each time on that much data?

Sql Server 2008 R2

Also, is running a COUNT() statement on the Primary_Key only, increase performance?


texts: Store regular MySQL query results in a timeseries DB

texts:
I'm looking into installing a timeseries database like InfluxDB or Prometheus to handle data for our Grafana monitoring system.

One of the things I like to do is run a couple of MySQL queries every few minutes to gather business metrics (like number of subscribers or application usage metrics).

Documentation for these timeseries databases feature plenty of examples of how to get MySQL performance metrics into their stores but I can't find any that demonstrates how to store data from queries.

Google search results seem to be completely drowned out by the more typical use case of gathering MySQL performance metrics.

Is this at all possible with either InfluxDB or Prometheus and where can I find information on how to set this up?


texts: MySQL command querying all MyISAM database

texts: I would appreciate if anyone could help me of what's the proper command in MySQL to query all database for having "MyISAM" engine. I need to convert all DB & tables from MyISAM to InnoDB.


texts: sp_BlitzFirst - File Stats Interpretation

texts:
I've had sp_BlizFirst running for a few days every 12 minutes to capture the various statistics.

What I've come to realise though is that it doesn't seem to gather a lot of File Related Performance Stats apart from I/O stalls.

Is there enough info to make a decision on whether I/O is a problem or not based off Wait Statistics and I/O Stalls? I can't find in the Blitz tables any mention of Disk avg. read time, Disk avg. write time, Avg. disk queue length that I thought should be considered too?

Can anyone give some feedback on the below? My Parallelism stats definitely need to be properly looked at (CXPACKET), but I'm curious about my disk i/o performance also. OLDEDB Waits I believe are being caused by Linked Servers and Idera SQL Diagnostic Manager monitoring the instance every 1 minute.

From https://www.sqlskills.com/blogs/paul/are-io-latencies-killing-your-performance/ I read the following:
Everyone has their idea of what constitutes good or bad I/O latency, and here’s my take:
Excellent: < 1ms
Very good: < 5ms
Good: 5 – 10ms
Poor: 10 – 20ms
Bad: 20 – 100ms
Shockingly bad: 100 – 500ms
WOW!: > 500ms

If I go by that, I'm in the Bad category.

Here are my Wait Statistics:

Server 1

The Average Read I/O Stall is 23.91

The MAX Read I/O Stall was 1162

The Average Write I/O Stall is 0.71

The MAX Write I/O Stall was 139

and here's the full extract.

Server 2

The Average Read I/O Stall is 26.28

The MAX Read I/O Stall was 428

The Average Write I/O Stall is 2.60

The MAX Write I/O Stall was 667

and here's the full extract.


texts: Transaction Isolation level different threads massive number of Writes

texts:
I have a lot of threads that writing to DB some data in two tables. tbl_raw_data and  tbl_parsed_data where tbl_parsed_data have foreign key to tbl_raw_data.

I also need the writing to be superfast.

While checking the options on improving the writes (asuming reading time is nu so important) a friend of mine told me that I need to check the Transaction Isolation Level that is appropriate for my logic.
after reading some articles regarding this issue what I understand is that this property influence reading.

Is there a Transaction Isolation Level that affect writing? Which isolation level is "best" for lots of Threads running on lots of connections?

Am I looking in the wrong place?

What can I do to improove massive writing?

Im using SQL server, the threads are coming from TomEE server that writes data that came via HTTP requests via JDBC and JPA (ORM).

Thanks!


texts: Analyze, Vacuum equivalent for SQL Server?

texts:
In Postgres, ANALYZE collects statistics about the contents of tables in the database, and store it. These statistics are used to determine the most efficient query plan, in PG. Running analyze command improves db's performance. I want to know if there is something similar in SQL Server.

Does SQL Server have commands equivalent to Postgres' Analyze and Vacuum?

If SQL Server doesn't have the exact equivalents, are there any similar commands?

If those commands do have SQL Server versions, please explain them or post links to useful documentations.


texts: How can I check a version of installed ODBC MariaDB connector?

texts:
Several months ago I built MariaDB Connector/ODBC from Source following official instructions:

git clone https://github.com/MariaDB/mariadb-connector-odbc.git
cd mariadb-connector-odbc
cmake -DCMAKE_BUILD_TYPE=RelWithDebInfo -DCONC_WITH_UNIT_TESTS=Off -DCONC_WITH_MSI=OFF -DCMAKE_INSTALL_PREFIX=/usr/local .
cmake --build . --config RelWithDebInfo

But I don't remember what version of the Connector I built, so now I need to know it. How can I check version of MariaDB Connector/ODBC?


texts: MySQL query takes too long in codeigniter

texts:
I've made this query

SELECT id,name,iupac_name,inchi_key,molecular_weight,molecular_formula,exact_molecular_mass
    FROM compound
    WHERE name LIKE '%quer%'
    OR iupac_name LIKE '%quer%'
    OR inchi_key LIKE '%quer%'
    OR inchi_key IN( SELECT inchi_key FROM compound_synonyms WHERE name LIKE '%quer%')

that took 0.3563 seconds to show result through phpmyadmin in my local machine and took 0.1117 seconds in digital ocean vps.

But the same query took 24+ minutes in another vps.

Does anyone can help me to find out the problem?


texts: View with a concatenated text field with line breaks

texts:
Using SQL Server 2012, I need to create a view with a field that concatenates a log field from another table, roughly:

select
    id,
    description,
    STUFF((
        SELECT
            '------------' + char(13) + char(10) +
            MessageLog.MessageText + char(13) + char(10) +
            '- on ' +
                format(MessageLog.MessageDateTime, 'yyyy-MM-dd HH:mm') +
                ' by ' + MessageLog.MessageAuthor + char(13) + char(10)
        FROM
           MessageLog
        WHERE
            Incident.IncidentNumber = MessageLog.IncidentNumber
      ORDER BY MessageLog.MessageDateTime DESC
      FOR XML PATH('')),
    1,
    2,
    ''
    ) AS allMessages
from
    Incident

The line breaks (char(13)+char(10)) come back as hex encoded &#x0D; instead of, well, line breaks.  The data from the table gets dumped into an HTML input field and I have no chance for any post-processing.

I have tried storing the line break as just char(13); just char(10); \n; <br />; and just hitting enter between single quotes.  Nothing renders correctly. I am guessing that I am abusing STUFF() / XML but cannot figure out where my mistake is.

How can I create my allMessages value with line breaks?


texts: PostgreSQL FDW: is it necessary to specify sever and user for every link to a database on the same server (with identical user privileges)?

texts:
I have a server with roughly 7000 databases that all have a practically identical schema. I've been querying them and collecting information about their contents in a separate database on the server. The way I have done this so far is using dblink queries wrapped in some Python code that iterates through all the databases. This is fine when I'm able to construct a query beforehand and iterate it, but it's not very efficient if I just want to quickly query one of the databases as dblink queries are somewhat complicated to construct.
I haven't used FDW in the past but I figured it would be much simpler to just import the relevant schema(ta) from every database on the server and store those as objects in my main database. What trips me up is that the FDW documentation begins by suggesting the creation of a foreign server object and user mapping. Since every database is on the same server and queried by the same user, I don't want to begin by adding a step that might be unnecessary.
So my question is, is it necessary to specify server and user mapping to create connections to databases on the same server, with a user that has identical permissions across every database?


texts: Change the default version of MSSqlLocalDB for Visual Studio 2015

texts:
I am working on a C# project with SQL Server. For development, we use LocalDB instance of SQL Server. If something is changed in the database, we check-in the .mdf/.ldf files into TFS. The connection is made using MSSqlLocalDB.

The problem

After upgrades on some systems, we are now using VS 2013 and VS 2015, both for development. The issue started because of the fact that VS 2013 defaults to SQL Server 2014 and VS 2015 defaults to SQL Server 2016 for LocalDB instances.

Once the .MDF file has been opened with 2016, it becomes incompatible with 2014. Due to this, people using 2015 can't check-in the database changes.

To solve it, I installed Version 2014 of LocalDB on to machines with VS 2015 but unable to change the default version to which VS connects to.

CAN ANYONE HELP ME??

Thanks in advance...


texts: Precautions when designing database schema if tables are going to have billons of rows

texts:
I am about to start work on telephone dictionary kind of project. Its confirm that there are going to be billions of records in dictionary table, and each entry from this dictionary table may have further inventory table with reference to dictionary table. I have not worked earlier with such huge database.

InnoDB is good for maintaining relational database. There are category and subcategory references, so i am going to use InnoDB. There will come a situation where I need to show total count based on category or subcategory, or even based on state and city. So on... it may any combination.

I am familiar with creating indexes on most searched columns. I have heard about table partitioning also helps to speed up queries.

My Questions is what points should I consider at this early stage when creating such database tables which are going to have billions of rows, so that later on when tables grow larger, I can keep table performance at high level with select queries and DML quires (insert, update).

Guidance will help me a lot.


texts: INSERT violating unique key constraint on primary key

texts:
on a table with primary key 'id' with default value nextval(), inserting a row throws the error

Query 1 ERROR: ERROR:  duplicate key value violates unique constraint "table_pkey"
DETAIL:  Key (id)=(6) already exists.

except this table has 200 rows. why isn't Postgres setting the new row id as 201?


texts: How can I find state name which has the most vehicle accidents in the database?

texts:
I have a table "ACC_DATA" that has the following columns: ACCIDENT_ID, STATE_CODE, ACC/DATE and a second table "US_STATES" which has the following columns: STATE_CODE and STATE_NAME

Each accident that happened is given an ID and the State in which it occurred, in the "ACC_DATA" table. How can I use SQL to find the name of the State which had the most accidents?

This is all that I have so far:

SELECT AD.STATE_CODE, COUNT(*) AS FREQUENCY
FROM ACC_DATA AD, US_STATES US
GROUP BY AD.STATE_CODE
ORDER BY COUNT(*) DESC

But this only gives a table of State codes with number of accidents in each. I am not quite sure how to join the two tables to produce the state name with a single max value.


texts:
SQL Server documentation for partitioning states:

Where possible, minimize requirements for referential integrity across vertical and functional partitions.
If cross-partition joins are necessary, run parallel queries over the
partitions and join the data within the application.

https://docs.microsoft.com/en-us/azure/architecture/best-practices/data-partitioning
I do understand that lack of referential integrity will make writes much faster since there won’t be a need to check other partitions for referential constraints. But how will handling joins in the application be beneficial.The number of requests to the database server and the processing needed will be the same if it was done by an RDBMS join, wouldn't it? (And in addition, for application-side joins, there will be the burden of network round-trips between the application server and the database server.)


texts: Worse query performance even after improving the query by making it to run in parallel mode

texts:
I have a SQL Query which gets generated from application using EFF.
It uses a view and this view has joins to multiple tables in there.
The query runs slow and when i looked at the execution plan,i see that query runs in parallel(wanted it that way as it is a complex query and serial execution is very slow,so purposefully removed serial forcing components).
I also noticed some keylook up and i kind of ignored it earlier because the cost of that operation looked small.
Now the query runs slow and and i created covering indexes to remove key lookup,but even after that the plan uses the old index and is not using covering indexes.Not sure why?

Here is the executing plan from Paste the plan.
https://www.brentozar.com/pastetheplan/?id=Bk1YECnOH

Any findings from the plan,which can help to improve the performance is highly appreciated.

I checked statistics and it is updated today morning ,so it is not the cause.
I am using SQL Server 2016 and have 105GB of RAM allocated to SQL Server out of the 128GB.It is enough i believe.
I have 10GB of tempdb space allocated.

I don't see any other bad signs other than keylook up and i am not an expert in dicing and slicing execution plan.So any help is greatly appreciated.


texts: strategy for enabling a general query log on MySQL 5.0.95 with no restart?

texts:
I would like to configure a general query log for a busy MySQL 5.0.95 production server without effecting the availability.

Obviously I can set the "log" option in the my.cnf file and restart the server, but this is going to kick off a bunch of monitoring emails and cause some tomcat errors which I would like to avoid.

According to the docs, the server "log" option is actually read-only until it becomes the general_log in version 5.1.x;
http://dev.mysql.com/doc/refman/5.0/en/query-log.html

[mysqld]
log
log-output=/var/log/mysqld.log

the /etc/init.d/mysqld script that I have doesn't support a "refresh" and its looks like a reload is going do a fairly brutal stop and start, which is definitely going to show up in monitoring.

on a test server I tried the various mysqladmin refresh and reload options, but couldn't get it to reread the config.

Do I have any other options?

Actually, while researching for this question I found a couple of possibilities for logging the ongoing SQL in real time without restarting the database.

If you are not using SSL for the network connection to port 3306, then its possible to tcpdump the traffic, and then use mk-query-digest or wireshark to re-assemble the TCP stream and decode to the mysql SQL text.

This obviously is only practical when you can collect the data you need in a short time, because you are going to fill up the tcp dump file quickly for anything non-trivial.


texts:
We are having WSFC(Windows Server Failover Cluster) configuration for one of database server and from last few days, backup of one of the database is failing. Sometime backup itself fails whereas on other times restore verify from disk fails.

I am using Ola Hallengren's maintenance script and we have checkdb weekly running for all the database and no issue is reported in that, one time I executed them manually - checkdb as well as backup finished successfully. Did fail-over one time and that day it worked fine however from next day onward its throwing same error. Differential backup just works fine.

Restore verify error is as below:

Command: RESTORE VERIFYONLY FROM DISK = N'Drive_name\Server_name\DB_Name\FULL\Server_Name_DB_Name_FULL_20191021_003022.bak' WITH CHECKSUM
Msg 3203, Level 16, State 1, Server Server_Name, Line 1
Read on "Drive_name\Server_name\DB_Name\FULL\Server_Name_DB_Name_FULL_20191021_003022.bak" failed: 170(The requested resource is in use.)
Msg 3013, Level 16, State 1, Server KHCBSPSQL, Line 1
VERIFY DATABASE is terminating abnormally.
Outcome: Failed
Duration: 00:05:13
Date and time: 2019-10-21 00:46:23

Backup error is as below:

Command: BACKUP DATABASE [DB_Name] TO DISK = N'Drive_name\Server_name\DB_Name\FULL\Server_Name_DB_Name_FULL_20191016_003016.bak' WITH CHECKSUM, COMPRESSION
Msg 3203, Level 16, State 1, Server Server_Name, Line 1
Read on "Drive_Name\MSSQL\DATA\DB_Name.mdf" failed: 170(The requested resource is in use.)
Msg 3202, Level 16, State 1, Server Server_Name, Line 1
Write on "Drive_name\Server_name\DB_Name\FULL\Server_Name_DB_Name_FULL_20191016_003016.bak" failed: 170(The requested resource is in use.)
Msg 3013, Level 16, State 1, Server Server_Name, Line 1
BACKUP DATABASE is terminating abnormally.
Outcome: Failed
Duration: 00:09:19
Date and time: 2019-10-16 00:39:35

I am suspecting that these files are used by some anti-virus running on server or some scanner is holding these files however I am not sure why only this specific database is failing. There are more than 20 databases and their backup runs smoothly, also to note that this is the largest one and approximately 100GB. Any help is much appreciated.

Version is as below:

  Server: Windows Server 2012 Standard

  SQL Server: Microsoft SQL Server  2012 (SP4-GDR) (KB4057116) -
  11.0.7462.6 (X64)     Jan  5 2018 22:11:56    Copyright (c) Microsoft Corporation     Standard Edition (64-bit) on Windows NT 6.2  (Build
  9200: ) (Hypervisor)

UPDATE: Copied this backup to test server and restored the same on a new database and restoration completed successfully without any issue.

Executed the backup manually without checksum and backup completed without any error however when it runs with checksum, throws same error as provided above.

Sysadmin has excluded mdf, ldf, ndf and bak type files from live scanning.


texts: Drupal 7 Uses InnoDB, so do I need other configs?

texts:
If I'm optimizing a database that will be delivering multiple Drupal 7 websites, all using InnoDB, do I need to worry about [myisamchk] & [isamchk] sections, or can I just worry about the stuff within [mysqld]?

I'm assuming if I delete these sections, that the defaults will just kick in if they are needed.

I'm trying to document, outline my.conf best practices here http://groups.drupal.org/node/289613


texts: What extra work is required to restore a SQL Server instance and its databases included in a Windows Server snapshot backup?

texts:
We are migrating our SQL Server databases (various versions) from source Windows servers to new Windows servers. These are to be done by taking snapshot backups of of the servers. Does this server backup method take in a consistent copy of a SQL Server instance and its databases?
Is there any work that will be required to complete restoration of the SQL Server databases or does everything get restored as is on the source server?
I do not have experience in this area, what I know is to backup and restore the database, I would appreciate some guidance on this method of migration please.


texts: Difference between storing a DBUnit file in version control and using Liquibase for Database Version control?

texts:
What is the difference between storing a DBUnit database-loadable xml file in version control, and using Liquibase for database version control?

I'm relatively familiar with DBUnit having used it in Matt Raible's Appfuse project, and I've used it for unit testing in projects based on Appfuse.  It never crossed my mind before, but it would seem to me that it could also be used for Database version control.

Now Liquibase appears to be used for version control of a database, but I fail to see how another tool is needed for this when you can just pull database changes using DBUnit, and get your data with it as well (storing that file in version control to facilitate your db vcs needs.)

There must be a reason that both of these tools exist, but I can't understand what the difference in scope between them is, could someone please explain this to me?


texts: How to know which instance is default in Configuration Manager if default port number is changed?

texts:
In MS SQL Server Configuration Manager I can see all the instances. I recognize the default one (by saying a default instance I mean the one that listens for 1433) by looking at TCP/IP's IP Addresses property which shows IP addresses and used ports from Network Configuration settings. But named instances can use 1433 too. If the port number of the default instance is changed how can I identify which one is the default in Configuration Manager(I know that other options are available without Configuration Manager).

Thanks for your support.


texts: Dynamic SELECT and place result in variable columns

texts:
Edit: I use Microsoft SQL Server Management Studio, I believe it's SQL 2008R2. Sorry, new to this.

I've got a complex question, at least, I think so since I can't find the answer after excessive googling.

Simplified situation:

Table | QuestionAnswers
id
user_id
question_id
answer

Table | Questions
id
question_id
question_name

Now I want to write a query that gives the following result:

user_id | question 1 | question 2 | question 3 | que...etc
1         Something
1                                   Something
2                      Something
3         Something
3                      Something

Or (but that shouldn't be too difficult if I have the above?):

user_id | question 1 | question 2 | question 3 | que...etc
1         Something                 Something
2                      Something
3         Something    Something

Where 'something' is the answer to the question.

Now I have a possible query:

SELECT [user_id]
      ,[answer] as Question1
      ,''       as Question2
      ,etc
FROM [QuestionAnswers]
WHERE [question_id] = 1

UNION ALL

SELECT [user_id]
       ,''       as Question1
       ,[answer] as Question2
       ,etc
FROM [QuestionAnswers]
WHERE [question_id] = 2

UNION ALL

SELECT etc...

Works like a charm. Downside is that this way, I have to make x SELECT statements with x rows, where x is the number of questions I have (50+).

How can this be done dynamically? Is that possible?

I hope I explained myself good enough...

Kind regards,
Tjab


texts: MongoDB - _id as String instead of ObjectId ? Relationship between collections or duplicate data?

texts:
Scenario:
An entity called 'accessPoint' has an important attribute called 'network'. The application will display some parts of this network attribute, so I decided to store it inside accessPoint documents.
I also decided to store all networks documents (with additional attributes) in a separated collection (for performance reason: it will be used in an autocomplete input in the front end)
accessPoint: {
    id: ObjectId,
    name: String,
    uptime: Number,
    network: {
        subnet: String,
        netmask: Number
    }
}

network: {
    _id: ObjectId
    subnet: String,
    netmask: Number,
    size: Number,
    validatedAt: Date,
    attrX: String,
    attrY: String,
    attrZ: String
}

I have 3 major questions:
1 - Is it a good practice to store networks in a separated colletion? (attention to the requirement above)
2 - Is there any compliance/convention issues building a relationship between these two documents (like an SQL DBMS)?
3 - And, I was wondering... can I store _id as String instead of ObjectId?
I've read some advices, but I don't have a conclusive opinion about it.
e.g : ... network: { _id: "192.168.1.0/24" } (uniqueness guaranteed :D)
Thanks!


texts: AWS DMS - How to select different databases for source and target points for a task

texts:
Does aws DMS(Data Migration service) allow to select different databases  for source and target points for a task in a replication instance? My source engine is MySQL.
example: I want to move table A from database D1 to D2. but i am not able to do in DMS.


texts: postgresql data on secondary disk, automatic start fails with "not accessible or does not exist"

texts:
The OS is Ubuntu 20.04. I moved the data directory to a secondary disk. In the "Disks" utility, I clicked "Edit Mount Options", turned off "User Session Defaults", and specified a hard-coded Mount Point like /mnt/database, because by default it was mounted at something like /media/[user name]/disk label. "Mount at system startup" had been checked (unchanged from default).
Now, postgresql's automatic start at the system startup fails with a message like: "[data directory] is not accessible or does not exist". If I manually start it, by executing sudo systemctl start postgresql it starts fine. I suspect that the system tried to start postgresql before the secondary disk was automatically mounted. Is that so? If that is the case, how can I make it start after the secondary disk has been mounted?

PS: I solved it. It was the order. I cannot believe why the tutorials that explained moving data directory to a secondary disk did not mention this.
Open the service file:
sudo nano /lib/systemd/system/postgresql@.service

Add the disk's name at:
After=network.target mnt-[disk name].mount

If the disk name is "database", then it is mnt-database.mount.


texts:
We currently are seeing record locks during insert after we upgraded mysql from 5.6 to 5.7.  I have disabled the deadlock detection and logging everything but still can't determine why the locks are happening.  It looks like a concurrency issue but this wasn't a problem before 5.7.

How to troubleshoot it better? What is the most likely cause?


texts: cannot convert yyyymmdd to yyyy-mm-dd - arithmetic overflow error

texts:
I am trying to do something that is very simple and am stumped why it is not working.

I have a column (sysjobschedules.next_run_date from msdb) that is a varchar formatted like yyyymmdd.  I want to convert that to a datetime.  I use the rather straight-forward command CONVERT(datetime,next_run_date,112) but I keep getting the error Arithmetic overflow error converting expression to data type datetime.

The three distinct values in this column are 20120802, 20120803, and 20120806.  Using that same function above, I was able to successfully convert all three strings.

I feel like I must be missing something obvious...?


texts: Connection Manager in SSIS 2017 Package NOT Saving the Password

texts:
We're using Visual Studio 15.7.2 and using SSIS on SQL 2017 (14.0.3029.16).

We built a connection manager to a 3rd-party, vendor managed SQL Server 2008 R2 (10.50.6529.0).  Our only allowed connections to this server are via SQL Login, so Windows Authentication is a no-go.

We can run the package inside of Visual Studio/SSDT just fine, the package executes and completes successfully as we'd expect.

When we open the package in SSDT we're met with error immediately:

Upon looking at Connection Managers we're met with the following icon:

When we open the connection manager, this is what we see (password blank, should be *********):

The package is configured to run (via job) at 8:45 pm Eastern and it fails every time. We get a plethora of errors that basically state we can't connect to the 3rd-party server.  Investigation shows that the password isn't being saved so we get denied at the front door!

Has anyone ran into this before?  What options do I have to rectify?  I'm somewhat new to SSIS, so I'm learning a lot of hard lessons fast.


texts: Is there a way to synchronize mysql databases that are missing a few days worth of data

texts:
I have a primary-primary MySQL server setup that is connected to a load balancer. At one point, a failure occurred to where the replication wasn't happening and data was being written to one server and not the other. We have since fixed the replication issue. However, both servers are missing data that the other server has due to the load balance switching.

Is there a way to sync the missing data between both devices?


texts: SQL Server Full Text Search Engine is not returning consistent results

texts:
I'm working on an application that makes use of the Full Text Search Engine from SQL Server. In order to convert the user input into readable parameters for SQL, i'm using This very useful FTS query converter. Now, the issue at hand:

We have a record that has the following set of keywords:

  Bockelman v. MCI Worldcom, Inc.

When the user types that on the application, the converter will work out the logic and send the following parameters to CONTAINSTABLE:

   FORMSOF(INFLECTIONAL, Bockelman) AND FORMSOF(INFLECTIONAL, v) AND FORMSOF(INFLECTIONAL, MCI) AND FORMSOF(INFLECTIONAL, Worldcom) AND FORMSOF(INFLECTIONAL, Inc)

However, the query returned no results. I noticed that the converter removes the periods from both "v." and "Inc.", so I performed a direct query on the database adding those missing periods. Still, no results.

If I remove "v." from the query. It returns a number of results. At this point, I thought that the "v." was being treated as a stopword of some sort. However, when I performed another query...

  John v. Smith

It returned a number of results with the "v." included on the query. This feels a bit inconsistent for me. I am probably not using the FTS Engine correctly, but I'm hoping to find some guidance here.

Thanks!

EDIT:

Here's the fully built query:

    SELECT o.OpinionDocumentId
                                       FROM CONTAINSTABLE(OpinionDocuments, Text, 'FORMSOF(INFLECTIONAL, Bockelman) AND FORMSOF(INFLECTIONAL, v)
    AND FORMSOF(INFLECTIONAL, MCI) AND FORMSOF(INFLECTIONAL, Worldcom) AND FORMSOF(INFLECTIONAL, Inc)') AS KEY_TBL
                                       LEFT JOIN dbo.OpinionDocuments AS o ON o.OpinionDocumentId = KEY_TBL.[KEY]
                                       LEFT JOIN dbo.OpinionDocumentPlaintiffs AS p ON p.OpinionDocument_OpinionDocumentId = KEY_TBL.[KEY]
                                       LEFT JOIN dbo.OpinionDocumentDefendants AS d ON d.OpinionDocument_OpinionDocumentId = KEY_TBL.[KEY]


texts: Relation between multiple hierarchical taxonomies linked to a single entity

texts:
I am working on an online test application which supports multiple choice questions.
I have created schema for taxonomy (classification) system to be used with questions.
taxonomy(id, name, slug, description)
taxonomy_terms(id, taxonomy_id, name, slug, parent, weight)

question_taxonomy(id, question_id, taxonomy_term_id)

A question is classified under various categories (taxonomies).
At the top rests Exam taxonomy which has Subject taxonomy as children. Each Subject consists of various Topic taxonomy which may further be divided into Sub Topics by adapting Sub Topic taxonomy.
To create relationships between various taxonomy terms(linked across taxonomies), I have created another table as
taxonomy_term_relations(id, parent_id, child_id)

e.g,
MAT is a taxonomy term for Exam Taxonomy
MAT may have two subjects viz Reasoning & General Knowledge which are taxonomy terms for Subject taxonomy
Reasoning subject will have 10 different topics with taxonomy terms falling under Topic taxonomy, say Reasoning will have Topic A, Topic B ....
Each Question will belong to a Topic which in turn will belong to Subject which again will belong to Exam.
A Subject may belong to multiple Exams or a Topic may belong to multiple Subjects.
Is this the correct approach to create db schema for this system.
UPDATE
@MirNazim suggested another approach to this problem with Exams, Subjects and Topics treated as separate entities rather than taxonomy, which will reduce the complexity of whole thing. So the new structure may become
exams(id, name, description...)
subjects(id, name, description ...)
exam_subjects(id, exam_id, subject_id)
topics(id, name, parent, weight....)
subject_topics(id, subject_id, topic_id)

Any suggestions on this approach will be appreciated.


texts: AWS RDS. CPU Usage 100%. Was it due to low max_connections?

texts:
What do you think is the reason of 100% CPU usage at around 10:45 (which is the same time as 19:45 (local time) in the last 4 graphs)?
Is it because of low max_connections?
The RDS instance is m4.10xlarge that has 160GB memory but the set max_connections were only 2000.
The DB connections went up to around 2300 at one moment.
What do you think was the problem?
The number one guess is that max_connections are the problem, but, I don't understand because although DB connections exceeded the max_connections, why did CPU usage have to go up to 100%? If RDS just cuts off any connection more than 2000 (max_connections), there's no need for CPU usage to go up. So, I want to hear you opinion. What's the cause, and how can I solve this problem so that it will not happen in the future?


texts: Setting up sp_AllNightLog using Azure Blob Storage

texts: Has anyone setup Brent Ozar's sp_AllNightLog using Azure Blob Storage? Trying to start incorporating both but not sure of the steps or how to get started. Any tips or links to similar operations?


texts: Multiple relationships between the same entities

texts: Is there a name for the case when two entities can have multiple relationships between themselves?


texts:
I've had some help to create a query to solve a problem I had and the solution seemed to be to create two CTE's and then join and query them.

If I run the two CTE's as single queries each table contains the expected data without fail but the join produces less results than it should and duplicates and even rows with three or more duplicates.

;WITH CTE1 AS
(
SELECT FContainerHylla.Name, FContainerPlats.HyllId,
FContainerPlats.x, FContainerPlats.y,
    FContainerPlats.enable, FContainerHylla.Type,
ISNULL((SELECT MIN(fcontainer.id) AS id
FROM FContainer
WHERE (PlatsId = FContainerPlats.id)), 0) AS FcId
FROM FContainerPlats
INNER JOIN FContainerHylla ON FContainerPlats.HyllId =

    FContainerHylla.Id
WHERE (FContainerHylla.Type IN (1, 6, 10))
    ),

    CTE2 AS
    (
SELECT FContainer.id AS SubID, produkter.Produktnamn AS Produktnamn
from FContainer
INNER JOIN Stämplingar ON FContainer.id = Stämplingar.ID
INNER JOIN Tempo ON Stämplingar.temponr = Tempo.temponr
INNER JOIN Produkter ON Tempo.produktnr = Produkter.Produktnr
    )

    SELECT CTE1.*, CTE2.Produktnamn
    FROM CTE1
    INNER JOIN CTE2 ON CTE2.SubID = CTE1.FcId
    ORDER BY CTE1.HyllId, CTE1.x, CTE1.y

CTE1 produces a table which is about 2600 rows long and I've verified that it's correct. CTE2 is about 7000 records long and also correct. The column fcontainer.id is a unique key.

As I said when these two tables are joined I get the wrong results. I had expected to get a something similar to the results from CTE1 but with the field produkter.produktnamn added but which I do but instead of the 2500 results from CTE1 I get something around 1600 rows with duplicates.

Create Table For Fcontainer and Stämplingar:

CREATE TABLE [dbo].[FContainer] (
[id]              NUMERIC (18) IDENTITY (1, 1) NOT NULL,
[PlatsId]         NUMERIC (18) CONSTRAINT [DF_FPall_InStoreage] DEFAULT
((0)) NOT NULL,
[Halv]            BIT          NOT NULL,
[FlaggId]         SMALLINT     NOT NULL,
[Kragar]          SMALLINT     NOT NULL,
[TmpFifo]         NUMERIC (18) NULL,
[PackTempoTypeNr] SMALLINT     NULL,
[Invent]          VARCHAR (50) NULL,
CONSTRAINT [PK_FPall] PRIMARY KEY CLUSTERED ([id] ASC) WITH (FILLFACTOR =
90)
);

CREATE TABLE [dbo].[Stämplingar] (
[Stämplingsnr]   NUMERIC (18) IDENTITY (1, 1) NOT NULL,
[temponr]        NUMERIC (18) NULL,
[Tidpunkt]       DATETIME     NULL,
[antal]          NUMERIC (18) NULL,
[anställd]       CHAR (50)    NULL,
[Bokad]          BIT          CONSTRAINT [DF_Stämplingar_Bokad]
DEFAULT ((0)) NOT NULL,
[Skickad]        BIT          CONSTRAINT [DF_Stämplingar_Skickad]
DEFAULT ((0)) NOT NULL,
[Leveransdatum]  DATETIME     NULL,
[Papp]           NUMERIC (18) NULL,
[daglignr]       NUMERIC (18) NULL,
[ID]             NUMERIC (18) NULL,
[Anställningsnr] NUMERIC (18) NULL,
CONSTRAINT [PK_Stämplingar] PRIMARY KEY NONCLUSTERED ([Stämplingsnr] ASC)
WITH (FILLFACTOR = 90)
);

From help with the comments I thing going with left join seems to be a step in the right way. Now I got about 3100 results instead of 1600. If I just can remove the duplicates I can figure out how to get the produktnamn for those that didn't have it reachable via CTE2.

Some of the relationships might be a bit confusing since they are not very well defined where .id on one table might not be the same thing as .id in another. The query in CTE1 is the original query which I inherited and was trying to expand.


texts: What is going on with my user and max date query?

texts:
OK query below.   Hitting about a half million rows and I am trying to grab the last time a user (uid) touched a page on our system denoted by LogDate.   I have used this query quite a few times but applying it to a bigger table seems to cause issues.   Let me review.

Keeping query is I can only grab about 8k rows or my query will just lock up - not getting much in logs.
If I limit to 1000 it takes 2 seconds.   If I change it to 2k it takes 77 seconds.   8k takes about 88 seconds.   After that... nothing.
If I take out the Distinct part it is obviously faster.   However I still can't grab the rows I need so it doesn't matter.    I have tried adjusting Innob memory settings which seems to have zero effect.

SELECT DISTINCT
  `t1`.`uid`,
  `t1`.`LogDate` AS `touchdate`
FROM
  (
      `ref_source_page_log_report` `t1`
      LEFT JOIN `ref_source_page_log_report` `t2` ON (
          (
              (`t1`.`uid` = `t2`.`uid`)
              AND (
                  (
                      `t1`.`LogDate` < `t2`.`LogDate`
                  )
                  OR (
                      (
                          `t1`.`LogDate` = `t2`.`LogDate`
                      )
                      AND (`t1`.`uid` < `t2`.`uid`)
                  )
              )
          )
      )
  )
WHERE
  isnull(`t2`.`uid`)
LIMIT 10000


texts: Breaking a big insert query into smaller ones

texts:
I have the below INSERT query.  It tries to insert over 50,000 rows and it fails because of logging.  Is there a good way of splitting a query like this into batches of about 1000 or so with COMMIT statements so that it is not just one big statement?

INSERT INTO xxx (....) SELECT .... FROM xx


texts:
I work for a small technology MSP. Having just completed my Bachelor's in Information Science, with a focus in data science, my boss has asked me to build a data warehouse (I think he really means a data mart from what I have read). I've been realizing that my education was very heavy on coding, math/stats, and the ML aspect of the data pipeline, but very light on the data preparation aspect, which is frequently claimed to take 80% of the time, so this is a good opportunity for me to learn how all of those tidy data sets I've been analyzing come into existence.

We have 2 main systems, which respectively run SQL Server (~60 GB), and MySQL (~80 GB), and a few other data sources which will come in as CSV. At this point, we simply want to do some simple asset reporting for our clients, and also some simple pricing analysis for some of our monthly fees (e.g. boss has no idea what it actually costs us to support a client's server per month, so our prices are essentially best guesses).

At this point, my main problem is that I don't know what I don't know. I'm currently reading "The Data Warehouse Toolkit" by Kimball, and also taking an EdX.org course in T-SQL from Microsoft. Another co-worker has a small bit of experience in this area, and he says that we should figure out what tables we will need from our 2 main systems, copy them over to another instance of SQL Server, and then use stored procedures to load the data (in the desired format) into a new database, to be used for our purposes for reporting and analysis.

I have two questions:

Does my co-worker's approach seem reasonable for what we are trying to do?
As far as learning what I need to know, are the two sources above sufficient for what I want to do, or am I missing anything important?


texts: pg_switch_wal In Point In Time Recovery PostgreSQL

texts:
I was following this tutorial about point in time recovery in postgres and come up with the following questions. Can anybody help me.

Is it necessary to use pg_basebackup every time I want to back up the database or the postgres backs up the database and stores somewhere considering its configurations?
What does the psql -c "select pg_switch_wal();"do? and is it necessary to run every time I want to back up the database?

I use postgres 11 on ubuntu 18.04.3 if that helps.

EDIT

Let me be more specific in my question. As I've understood by searching on the internet, we should have two files to back up the database: one base which contains the general files such as table attributes and a WAL file which contain the log files showing instances in the database. And I have seen that pg_basebackup backs up whole database cluster not only WAL files, so there should be a function which only backs up WAL files.

Thanks


texts: Create postgres schema in db with multi user in AUTHORIZATION

texts:
Is it possible to create a schema that is owned or accessible by more than one user in the same database?

I was reading postgres create schema documentation and postgres alter schema documentation and there is no syntax to provide more than a single user.

I was also googling for such a solution, either in simple or hard way and found nothing, not even a single point to any workaround.

Could you, please, point me to a solution or possible ways to workaround that, or make me certain, that such a thing is not possible in any way?


texts: Postgresql warning: type reference X converted to Y (SQL State: 00000 - Error Code: 0)

texts:
When I execute:

CREATE VIEW foreign_keys AS
SELECT
    tc.table_name, kcu.column_name, ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name
FROM
    information_schema.table_constraints AS tc, information_schema.key_column_usage AS kcu,
    information_schema.constraint_column_usage AS ccu
WHERE
    tc.constraint_type = 'FOREIGN KEY' AND tc.constraint_name = kcu.constraint_name AND
    ccu.constraint_name = tc.constraint_name;

CREATE FUNCTION GetPermissionColumns() RETURNS TABLE(column_name foreign_keys.column_name%TYPE)
    AS $$
BEGIN
    RETURN QUERY SELECT column_name FROM foreign_keys WHERE table_name = tg_table_name AND
        foreign_table_name = 'permission';
END;
$$ LANGUAGE plpgsql;

under Postgresql 9.3 I get the following warning message:

type reference foreign_keys.column_name%TYPE converted to information_schema.sql_identifier (SQL State: 00000 - Error Code: 0)

What am I doing wrong and how can I correct it?


texts: Oracle RAC takes forever dropping tables

texts:
We are seeing table drops consistently take tens of seconds on our Oracle 12c RAC.
It appears to be spending on its time on reliable message waits.  Which I think means that it asks the other node to do something and waits for it to respond... but there is no further visibility into it.

Here's the portion of trace which appears relevant to me:

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       23      0.00       0.00          0          0          0           0
Execute     23      2.37     772.01        212       3764      15146           0
Fetch        2      0.00       0.01          0          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       48      2.38     772.03        212       3764      15146           1

Misses in library cache during parse: 22

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      25        0.00          0.00
  SQL*Net message from client                    25        0.10          1.05
  row cache lock                               3054        0.00          0.61
  library cache pin                             100        0.00          0.02
  library cache lock                             61        0.00          0.01
  Disk file operations I/O                        5        0.00          0.00
  db file sequential read                       212        0.03          0.82
  enq: IV -  contention                         450        0.00          0.23
  enq: TM - contention                           15        0.00          0.00
  gc current grant 2-way                         92        0.00          0.01
  enq: ZH - compression analysis                149        0.00          0.02
  gc current grant busy                           1        1.39          1.39
  enq: TS - contention                           97        0.00          0.02
  gc cr disk read                                40        0.00          0.00
  enq: RO - fast object reuse                   215        0.01          0.11
  reliable message                              132       28.53        712.21
  enq: CR - block range reuse ckpt               44        0.01          0.19
  DFS lock handle                              1226        0.09          2.34
  log file sync                                  22        0.01          0.07
  latch: messages                                 1        0.00          0.00
  latch: row cache objects                       21        0.00          0.00
  KJC: Wait for msg sends to complete             9        0.00          0.00
  gcs drm freeze in enter server mode           113        0.50         51.71
  library cache: mutex X                          3        0.00          0.00
  latch: ges resource hash list                  46        0.00          0.00
  latch free                                      2        0.00          0.00
  latch: gc element                               1        0.09          0.09
  ADR block file read                            18        0.00          0.00

This is result of dropping 22 tables... over 770 seconds!  This used to be working much faster and then this happened.


texts: PostgreSQL vs. MySQL: spatial feature comparison

texts:
We are the in the process of building out a web application that has a spatial data component. In the beginning our spatial data comparisons will take a given point and return matched overlapping spatial polygons.

That being said, our database has many other components that include all the typical things you would find in your general relational database.

We are at the point in our project where we must choose which database solution to use.

All project members are more familiar with the implementation and administration of MySQL, yet all research suggests that PostgreSQL is the better solution - especially in regards to spatial data using postGIS.

We expect (hope) our application will experience a lot of action with a lot of concurrent users.

Does anyone with experience using MySQL as their RDBMS with a spatial data component have any long term advice / experiences?

Are there any disadvantages of using PostGIS with the exception of familiarity?


texts: Query to List all hierarchical children with parent, grandparent etc. from entire table

texts:
I have a basic sql table with an ID and an optional parentID from the same table. This has multiple levels. I need to get a list of all IDs with each of its parents all the way to the top.

if object_id('tempdb..#table') is not null drop table #table
create table #table (id int identity(1,1), [Name] varchar(16), ParentID int null)
insert into #table
values
('John',null)
,('Doe',1)
,('Mike',1)
,('Neil',3)
,('Sylvia',4)
,('Mary',2)

+----+--------+----------+
| id |  Name  | ParentID |
+----+--------+----------+
|  1 | John   | NULL     |
|  2 | Doe    | 1        |
|  3 | Mike   | 1        |
|  4 | Neil   | 3        |
|  5 | Sylvia | 4        |
|  6 | Mary   | 2        |
+----+--------+----------+

I need to get a list like this:

ID  | Parent | DirectParent
_______________________
2     1       1
3     1       1
4     3       1
4     1       0
5     4       1
5     3       0
5     1       0

It ignores any record that does not have a parent.
For each child there is a record matching it to one of its parents
There is a bit column to know which parent is the one directly above it

How can I query the above table to get this data?

Edit: see what I have been working with

 WITH Recursives AS (
        SELECT   CAST(ID AS VARCHAR(MAX)) + '\' ID,*
        FROM    table
        WHERE   id=@ID
        UNION ALL
        SELECT  r.ID + CAST(t.ID AS VARCHAR(MAX)) + '\', t.*
        FROM    table t
        INNER JOIN Recursives r    ON  t.parentID= r.ID)

    SELECT ID,Name,Parent
    FROM Recursives r

I use this to get a list for a specific parent all the way down.

I can't figure out how to do this for the entire table without
getting an error  The statement terminated. The maximum
recursion 100 has been exhausted before statement completion.
How can I get the same child multiple times. Once with each parent?


texts:
Can CURRENT_TIMESTAMP be used as a PRIMARY KEY?

Is there a possibility that two or more different INSERTs, get the same CURRENT_TIMESTAMP?


texts: What is the best way to purge data from a table with minimum damage to performance?

texts:
When you purge records from a database table, deleting them, you have impact on the logical and physical structure of the target table, as well as indexes and statistics.

What maintenance should be performed on the table after a massive record deletion?


texts: Am I supposed to let PG know in advance before I back up its datadir?

texts:
I run PostgreSQL on Windows. Its data dir is located in "My documents". I often back up that entire dir tree (among others) with ROBOCOPY, which "robustly" copies the dir tree to local and external disks.
Since PG is running at the same time as my ROBOCOPY script, I just had the idea that maybe this is not safe? Should I "let PG know" that I'm about to back up/copy its data dir, and then let it know when I'm done? Or is that unnecessary? Maybe even not a "thing" at all?
It strikes me that it could theoretically be bad if PG is writing to its database files while ROBOCOPY is also copying the same files. But maybe this has been solved fully 30 years ago and I'm just still stuck in early 1990s computing thinking.
I also (separately) use pg_dump regularly. The idea of shutting down PG entirely many times a day is not an attractive one.


texts: Is partial dependency possible In Data Base for Foreign key relationship

texts:
I have 3 tables Student, Teacher & User.

Student:

CREATE TABLE Student( id INT NOT NULL PRIMARY KEY,name VARCHAR(50) NOT NULL);
INSERT INTO [dbo].[Student]([id],[name]) VALUES(4,'Ram'),(5,'Raman');

Teacher:

CREATE TABLE Teacher( id INT NOT NULL PRIMARY KEY,name VARCHAR(50) NOT NULL);
INSERT INTO [dbo].[Student]([id],[name]) VALUES(1,'Raj'),(2,'Rahul');

User:

CREATE TABLE [dbo].[User](
    id INT NOT NULL PRIMARY KEY,
    user_id INT NOT NULL,
    user_type CHAR(1) NOT NULL,
    user_name VARCHAR(10) NOT NULL,
    user_password VARCHAR(255) NOT NULL,

    CONSTRAINT  FOREIGN KEY (user_id) REFERENCES Student (id),
    CONSTRAINT  FOREIGN KEY (user_id) REFERENCES Teacher (id) );

Now I try to INSERT in User table with below query

INSERT INTO [dbo].[User] ([id] ,[user_id] ,[user_type], [user_name], [user_password])
VALUES  (1 ,1,'S','Raj_001','********')

It gives me error for violation of foreign key due to value of user_id is available in  Teacher and not in Student

So my question is: How can I achieve that a value of user_id is  present in one of those tables and data should be inserted in User table.


texts: Return an error where the ID matches, but the owner_id doesn't

texts:
I've seen a common pattern like this used to update a record:

UPDATE posts SET (foo=bar) WHERE id=123 AND owner_id=234

However, what if there is a requirement that a non-existentent ID is differentiated from a owner_id mismatch?

The only easy solution I know of is to fetch the row first, compare owner_id in the client (app server), then proceed with the actual update. Alternatively, one could attempt the update, then if no rows were affected, try selecting the ID to see whether the ID doesn't exist or if the owner_id just didn't match.

Is there a way to communicate an owner_id mismatch so that extra round-trip to the DB can be skipped?


texts: Sharing Databases and Tables between users in MySQL

texts: I want to know how it is possible to share a database tables between multiple users/user groups, I mean, SQL Server has a security feature that we can create user groups and assign users to them, then we can give each user group permissions for tables in a database/schema. So, a user from visitors usergroup can't read sensitive admin tables, or a visitor can read categories but can't insert/update categories table but users from admin user group can.


texts:
I have a situation that is not easy to figure out, and thought I'd ask on this forum if others might have suggestions.

I'm running SQL Server 2008 R2 Standard SP3 on Windows Server 2008R2 Enterprise.

A database needed some maintenance, and after the fact I needed to restore on another server. I have a full db backup done with COPY_ONLY plus a set of 4 tlog backups.

before starting, create tlogbackup1
change from FULL to BULK_LOGGED recovery model
add new filegroup
add file to newfilegroup
set newfilegroup to be default
select into table (on newfilegroup)
drop original table
delete original file
delete original filegroup
change name of new table to match original table
change file name of newfilegroup to match original filegroup
change file name in catalog to match original file name
change file name at OS level to match original file name
set default filegroup to be the original
bring db online
change from BULK_LOGGED to FULL recovery model
After all steps have completed, create tlogbackup2

The restore of all backups must use WITH MOVE, due to drive letter changes on the restore server.

Recovery steps:

RESTORE database SomeDB FROM DISK = 'D:\REPRO\SomeDB.bak'
WITH
MOVE 'SystemData' TO 'D:\SQLDATA\SomeDB.mdf'
,MOVE 'SystemDataPDS' TO 'D:\SqlData\SomeDB.ndf'
,MOVE 'SystemData_log' TO 'D:\SQLLogs\SomeDB.LDF'
,NORECOVERY
,stats = 1

RESTORE LOG SomeDB FROM DISK = 'D:\REPRO\tlogbackup1.trn'
WITH
MOVE 'SystemData' TO 'D:\SQLDATA\SomeDB.mdf'
,MOVE 'SystemDataPDS' TO 'D:\SqlData\SomeDB.ndf'
,MOVE 'SystemData_log' TO 'D:\SQLLogs\SomeDB.LDF'
,NORECOVERY
,stats = 1

RESTORE LOG SomeDB FROM DISK = 'D:\REPRO\tlogbackup2.trn'
WITH
MOVE 'SystemData' TO 'D:\SQLDATA\SomeDB.mdf'
,MOVE 'SystemDataPDS' TO 'D:\SqlData\SomeDB.ndf'
,MOVE 'SystemData_log' TO 'D:\SQLLogs\SomeDB.LDF'
,NORECOVERY
,stats = 1

The final tlog restore gets to 100% and then fails with error 3456:

  Processed 368 pages for database 'SomeDB', file 'SystemData' on file 1.

  Processed 7656520 pages for database 'SomeDB', file 'SystemDataPDS' on file 1.

  Processed 172430 pages for database 'SomeDB', file 'SystemData_log' on file 1.

  Msg 3456, Level 16, State 1, Line 1
  Could not redo log record (210388:123648:232), for transaction ID (0:1016710921), on page (4:8088), database 'SomeDB' (database ID 6). Page: LSN = (0:0:1), type = 11. Log: OpCode = 4, context 11, PrevPageLSN: (210388:122007:1). Restore from a backup of the database, or repair the database. Msg 3013, Level 16, State 1, Line 1 RESTORE LOG is terminating abnormally.

Just to verify that the full db backup was ok, I restored it ran CHECKDB, and there were no errors.

All feedback welcomed.

Thanks in advance,

Ned Otter


texts:
So I've got a table in SQL Server 2014 that stores events.  Events are generated no faster than once per 10 seconds, but can be much slower (minutes or hours between events).  The time between events is not uniform.

I'd like to sample the records by time.
For instance, I'd like to ask for a 10 minute sampling - so it would grab record 1, and the next record should be one that is at least 10 minutes later, and then the next is at least 10 minutes later than row 2, and so on.

I can achieve this with an iterative approach, looping over each record, but I'd like to let the database do the work.  The problem is, I don't know how to do this using T-SQL set/window statements.

Can anyone help me out?

Example data:

EventID | RecordTime
--------+--------------------
   1    | 2017-04-01 12:00:00
   2    | 2017-04-01 12:00:10
   3    | 2017-04-01 12:00:20
   4    | 2017-04-01 12:00:32
   5    | 2017-04-01 12:05:42
   6    | 2017-04-01 12:09:00
   7    | 2017-04-01 12:24:12
   8    | 2017-04-01 12:36:46
   9    | 2017-04-01 12:36:57
  10    | 2017-04-01 15:00:00

Desired result:

EventID | RecordTime
--------+--------------------
   1    | 2017-04-01 12:00:00
   7    | 2017-04-01 12:24:12
   8    | 2017-04-01 12:36:46
  10    | 2017-04-01 15:00:00

EventIDs 2-6 are all within 10 minutes of record 1, so I don't want them.
EventID 7 is 24 minutes 12 seconds later than the last record in my result set (EventID 1), so I do want it.
EventID 8 is 12 minutes 34 seconds later than the last record in my result set (EventID 7), so I do want it.
EventID 9 is 11 seconds later than the last record in my result set (EventID 8), so I do not want it.
EventID 10 is apx. 2.5 hours later than the last record in my result set (EventID 9), so I do want it.


texts: MongoDB MMS replica set fails to restart

texts:
I have a MMS replica set deployed with 3 instances. It was working fine until I shut it down this morning in order to do some changes and restarted it. From logs, it feels that there was an unclean shutdown of replica set. The mongod server on primary is failing to start now.

Here are the full logs

2015-08-20T07:08:41.389+0000 W -        [initandlisten] Detected unclean shutdown - /data/XXXXXXXXXXXX/mongod.lock is not empty.
2015-08-20T07:08:41.406+0000 I JOURNAL  [initandlisten] journal dir=/data/XXXXXXXXXXXXX/journal
2015-08-20T07:08:41.406+0000 I JOURNAL  [initandlisten] recover begin
2015-08-20T07:08:41.406+0000 I JOURNAL  [initandlisten] info no lsn file in journal/ directory
2015-08-20T07:08:41.406+0000 I JOURNAL  [initandlisten] recover lsn: 0
2015-08-20T07:08:41.406+0000 I JOURNAL  [initandlisten] recover /data/XXXXXXXXXXXX/journal/j._0
2015-08-20T07:08:41.407+0000 I JOURNAL  [initandlisten] recover cleaning up
2015-08-20T07:08:41.407+0000 I JOURNAL  [initandlisten] removeJournalFiles
2015-08-20T07:08:41.641+0000 I JOURNAL  [initandlisten] recover done
2015-08-20T07:08:41.641+0000 I JOURNAL  [initandlisten] preallocating a journal file /data/XXXXXXXXXXX/journal/prealloc.0
2015-08-20T07:08:44.074+0000 I -        [initandlisten]   File Preallocator Progress: 744488960/1073741824 69%
2015-08-20T07:08:47.176+0000 I -        [initandlisten]   File Preallocator Progress: 901775360/1073741824 83%
2015-08-20T07:08:50.274+0000 I -        [initandlisten]   File Preallocator Progress: 1027604480/1073741824 95%
2015-08-20T07:09:09.057+0000 I JOURNAL  [durability] Durability thread started
2015-08-20T07:09:09.057+0000 I JOURNAL  [journal writer] Journal writer thread started
2015-08-20T07:09:09.060+0000 I CONTROL  [initandlisten] MongoDB starting : pid=25507 port=27000 dbpath=/data/XXXXXXXXXXXXX 64-bit host=CH$
2015-08-20T07:09:09.060+0000 I CONTROL  [initandlisten] db version v3.0.2
2015-08-20T07:09:09.060+0000 I CONTROL  [initandlisten] git version: 6201872043ecbbc0a4cc169b5482dcf385fc464f
2015-08-20T07:09:09.060+0000 I CONTROL  [initandlisten] OpenSSL version: OpenSSL 1.0.1f 6 Jan 2014
2015-08-20T07:09:09.060+0000 I CONTROL  [initandlisten] build info: Linux ip-10-229-1-2 3.13.0-24-generic #46-Ubuntu SMP Thu Apr 10 19:11:08 U$
2015-08-20T07:09:09.060+0000 I CONTROL  [initandlisten] allocator: tcmalloc
2015-08-20T07:09:09.060+0000 I CONTROL  [initandlisten] options: { config: "/data/XXXXXXXXXXX/automation-mongod.conf", net: { port: 270$
2015-08-20T07:09:09.074+0000 I -        [initandlisten] Invariant failure _name == nsToDatabaseSubstring( ns ) src/mongo/db/catalog/database.c$
2015-08-20T07:09:09.091+0000 I CONTROL  [initandlisten]
 0xf4f859 0xef0031 0xed4b52 0x91e106 0x91e18f 0x920033 0x922cb0 0x808701 0x7d4ba4 0x7f0503489ec5 0x805d17
----- BEGIN BACKTRACE -----
{"backtrace":[{"b":"400000","o":"B4F859"},{"b":"400000","o":"AF0031"},{"b":"400000","o":"AD4B52"},{"b":"400000","o":"51E106"},{"b":"400000","o$
 mongod(_ZN5mongo15printStackTraceERSo+0x29) [0xf4f859]
 mongod(_ZN5mongo10logContextEPKc+0xE1) [0xef0031]
 mongod(_ZN5mongo15invariantFailedEPKcS1_j+0xB2) [0xed4b52]
 mongod(_ZNK5mongo8Database13getCollectionERKNS_10StringDataE+0x36) [0x91e106]
 mongod(_ZN5mongo8Database30_getOrCreateCollectionInstanceEPNS_16OperationContextERKNS_10StringDataE+0x1F) [0x91e18f]
 mongod(_ZN5mongo8DatabaseC1EPNS_16OperationContextERKNS_10StringDataEPNS_20DatabaseCatalogEntryE+0x1E3) [0x920033]
 mongod(_ZN5mongo14DatabaseHolder6openDbEPNS_16OperationContextERKNS_10StringDataEPb+0x150) [0x922cb0]
mongod(_ZN5mongo14DatabaseHolder6openDbEPNS_16OperationContextERKNS_10StringDataEPb+0x150) [0x922cb0]
 mongod(_ZN5mongo13initAndListenEi+0xC01) [0x808701]
 mongod(main+0x134) [0x7d4ba4]
 libc.so.6(__libc_start_main+0xF5) [0x7f0503489ec5]
 mongod(+0x405D17) [0x805d17]
-----  END BACKTRACE  -----
2015-08-20T07:09:09.091+0000 I -        [initandlisten]

***aborting after invariant() failure

Any idea how can I fix it? I have been trying to fix it since past 4 hours but nothing seems to be working.


texts: Does Postgres read blocks sequentially, even if a disk is high-latency?

texts:
I'm using Postgres on AWS EBS, when running a query that uses a bitmap heap scan and a bitmap index scan, the execution time seems proportional to the number of "heap blocks" read, as shown by the EXPLAIN ANALYZE (I've not 100% verified this). Because EBS is networked attached storage, it has a high (~1ms) latency, but a high throughput/bandwidth, meaning Postgres wouldn't be taking full advantage of the disk.
Is it the case that when reading from Disk postgres queries blocks/pages sequentially?


texts: Security and Performance Implications of SQL Tuning Advisor

texts:
I work on a relatively small development team that works with an Oracle (11g) database. It's recently been requested that all the developers be given the Advisor role so that we can utilize SQL Tuning Advisor when developing complex queries. Some have raised concerns that this may have significant performance and/or security implications, but I have not been able to find concrete answers to what these implications are.

If this role were to be given to the various members of my team, what are the major pitfalls we should be watching out for? I've included the tag for Oracle 12c as well, since we'll be upgrading to that in the near future. If there's a significant difference between the two, I'd appreciate it if it was at least pointed out.


texts:
I have a PowerShell script with a query to display yesterday's totals, as well as last year's same day totals, by hour for comparison. Yesterday's totals display by hour fine, but last year's list the entire sum for each hour. Any pointers would be much appreciated. Thank you
 $sql1 = "

    USE STORESQL

            SELECT FORMAT(TRS.F254, 'MM-d-yyyy') AS 'Date',

                   'Time' = CASE
                        WHEN TRS.F01 = 06 THEN '6 AM'
                         WHEN TRS.F01 = 07 THEN '7 AM'
                         WHEN TRS.F01 = 08 THEN '8 AM'
                         WHEN TRS.F01 = 09 THEN '9 AM'
                         WHEN TRS.F01 = 10 THEN '10 AM'
                         WHEN TRS.F01 = 11 THEN '11 AM'
                         WHEN TRS.F01 = 12 THEN '12 PM'
                         WHEN TRS.F01 = 13 THEN '1 PM'
                         WHEN TRS.F01 = 14 THEN '2 PM'
                         WHEN TRS.F01 = 15 THEN '3 PM'
                         WHEN TRS.F01 = 16 THEN '4 PM'
                         WHEN TRS.F01 = 17 THEN '5 PM'
                         WHEN TRS.F01 = 18 THEN '6 PM'
                         WHEN TRS.F01 = 19 THEN '7 PM'
                         WHEN TRS.F01 = 20 THEN '8 PM'
                         WHEN TRS.F01 = 21 THEN '9 PM'
                         WHEN TRS.F01 = 22 THEN '10 PM'
                         WHEN TRS.F01 = 23 THEN '11 PM'
                   END,

                   SUM(TRS.F65) AS 'Total Sales',

                   (SELECT SUM(TRS.F65) FROM RPT_HOU AS TRS
                    JOIN TLZ_TAB TLZ ON TRS.F1034=TLZ.F1034
                    JOIN LNK_TAB LNK ON TRS.F1056=LNK.F1056 and TRS.F1057=LNK.F1057
                    WHERE TRS.F1031= 'D' AND
                    TRS.F254 BETWEEN '$startDateLastYear' and '$startDateLastYear' AND
                    TRS.F1034 BETWEEN '2' and '2' AND
                    TRS.F01 BETWEEN '08' and '22' AND
                    LNK.F1000 NOT IN ('DSM', 'PAL', 'RAL', 'XAL')) AS 'Past Total Sales',

                    SUM(TRS.F64) AS 'Transactions',

                   (SELECT SUM(TRS.F64) FROM RPT_HOU AS TRS
                    JOIN TLZ_TAB TLZ ON TRS.F1034=TLZ.F1034
                    JOIN LNK_TAB LNK ON TRS.F1056=LNK.F1056 and TRS.F1057=LNK.F1057
                    WHERE TRS.F1031= 'D' AND
                    TRS.F254 BETWEEN '$startDateLastYear' and '$startDateLastYear' AND
                    TRS.F1034 BETWEEN '2' and '2' AND
                    TRS.F01 BETWEEN '08' and '22' AND
                    LNK.F1000 NOT IN ('DSM', 'PAL', 'RAL', 'XAL')) AS 'Past Transactions',

                   (round(SUM(TRS.F65)/SUM(TRS.F64), 2)) AS 'Avg Basket Size',

                   (SELECT(round(SUM(TRS.F65)/SUM(TRS.F64), 2)) FROM RPT_HOU AS TRS
                    JOIN TLZ_TAB TLZ ON TRS.F1034=TLZ.F1034
                    JOIN LNK_TAB LNK ON TRS.F1056=LNK.F1056 and TRS.F1057=LNK.F1057
                    WHERE TRS.F1031= 'D' AND
                    TRS.F254 BETWEEN '$startDateLastYear' and '$startDateLastYear' AND
                    TRS.F1034 BETWEEN '2' and '2' AND
                    TRS.F01 BETWEEN '08' and '22' AND
                    LNK.F1000 NOT IN ('DSM', 'PAL', 'RAL', 'XAL')) AS 'Past Avg Basket Size'

            FROM RPT_HOU AS TRS
                    JOIN TLZ_TAB TLZ ON TRS.F1034=TLZ.F1034
                    JOIN LNK_TAB LNK ON TRS.F1056=LNK.F1056 and TRS.F1057=LNK.F1057

            WHERE TRS.F1031= 'D' AND
                    TRS.F254 BETWEEN '$startDate' and '$startDate' AND
                    TRS.F1034 BETWEEN '2' and '2' AND
                    TRS.F01 BETWEEN '08' and '22' AND
                    LNK.F1000 NOT IN ('DSM', 'PAL', 'RAL', 'XAL')

            GROUP BY TRS.F254, TRS.F01
            ORDER BY TRS.F254

    "

Output:


texts: What is the MySQL Mutex called "srv_sys_mutex"?

texts: For the purposes of diagnosing performance issues, what kinds of operations might wait their turn for this mutex, and what configuration options and resources might influence contention on it?


texts: How to have CTE query recurse parent query result one by one?

texts:
I have this query used on Postgres to return place administrative levels.

WITH RECURSIVE hierarchy(name, pinyin, level) AS (
    SELECT p.name, p.pinyin, p.level, p.upper_place_object_id_fk
    from place p
    where p.pinyin = 'Jiulong'
    UNION ALL
    SELECT up.name, up.pinyin, up.level, up.upper_place_object_id_fk
    from hierarchy h
    INNER JOIN place up ON h.upper_place_object_id_fk = up.object_id
    WHERE h.upper_place_object_id_fk IS NOT NULL
)
SELECT h.name, h.pinyin, h.level from hierarchy h;

It works just fine for a single match, e.g.

"Jiulong"   4
"Yingde"    3
"Qingyuan"  2
"Guangdong" 1

But of course, there can be several places with the same name (and a different position in the admin hierarchy)

"Fenggang"  4
"Fenggang"  4
"Huaiji"    3
"Dongguan"  2
"Zhaoqing"  2
"Guangdong" 1
"Guangdong" 1

In this case the result doesn't make sense as I can't reconstruct the proper hierarchy.
And that's because the recursion occurs for every result of the parent query at the same time.

It'd work if the recursion could be done until the exit condition for each result of the parent.

It is possible to control how the recursion is done?

Place Table script

CREATE TABLE public.place
(
    object_id uuid NOT NULL DEFAULT uuid_generate_v4(),
    upper_place_object_id_fk uuid,
    name character varying(50) COLLATE pg_catalog."default" NOT NULL,
    level smallint NOT NULL,
    CONSTRAINT pk_place PRIMARY KEY (object_id),
    CONSTRAINT unique_place UNIQUE (name, upper_place_object_id_fk, level)
,
    CONSTRAINT fk_place_upper_pla_place FOREIGN KEY (upper_place_object_id_fk)
        REFERENCES public.place (object_id) MATCH SIMPLE
        ON UPDATE RESTRICT
        ON DELETE RESTRICT
        NOT VALID
)

and here are the relevant data from the table

object_id                               upper_place_object_id_fk                 name      level
"540ed8e9-c456-4a46-8049-1332f4ab52c1"  NULL                                    "Guangdong" 1   false
"4f7c7071-f917-4ab8-82c9-75aff0ba44d0"  "540ed8e9-c456-4a46-8049-1332f4ab52c1"  "Dongguan"  2   false
"afce8ec3-599f-4cea-9227-29bb750d4b01"  "540ed8e9-c456-4a46-8049-1332f4ab52c1"  "Zhaoqing"  2   false
"714b8e23-c346-4370-8697-25bb09497342"  "afce8ec3-599f-4cea-9227-29bb750d4b01"  "Huaiji "   3   false
"0461963d-e886-4149-bded-ee08216dd374"  "714b8e23-c346-4370-8697-25bb09497342"  "Fenggang"  4   false
"6955f89b-b7d3-4b1e-b21e-fb584d7f165b"  "712c1f45-24ac-4711-a47f-22513afc6fdd"  "Dongguan"  4   true
"d931f691-0ec7-4fc7-9197-b15461e984e0"  "4f7c7071-f917-4ab8-82c9-75aff0ba44d0"  "Fenggang"  4   true


texts: Can't start postgres server, SHMMAX too small

texts:
When I try to start the postgres server with sudo /etc/init.d/postgresql start I get this error:

  Starting PostgreSQL 8.4 database server: mainThe PostgreSQL server
  failed to start. Please check the log output: 2012-10-31 17:52:46 EDT
  FATAL: could not create shared memory segment: Invalid argument
  2012-10-31 17:52:46 EDT DETAIL: Failed system call was
  shmget(key=5432001, size=188080128, 03600). 2012-10-31 17:52:46 EDT
  HINT: This error usually means that PostgreSQL's request for a shared
  memory segment exceeded your kernel's SHMMAX parameter. You can either
  reduce the request size or reconfigure the kernel with larger SHMMAX.
  To reduce the request size (currently 188080128 bytes), reduce
  PostgreSQL's shared_buffers parameter (currently 19200) and/or its
  max_connections parameter (currently 103). If the request size is
  already small, it's possible that it is less than your kernel's SHMMIN
  parameter, in which case raising the request size or reconfiguring
  SHMMIN is called for. The PostgreSQL documentation contains more
  information about shared memory configuration. ... failed!

What should I do here?  Shared_buffers is actually set to 150MB and max_connections is actually set to 100 in the only postgresql.conf file available to it...

I don't know if the request is unusually high, SHMMAX unusually low, or if postgres grabbed some config parameters from the Aether, either seem equally likely...

What am I supposed to make of this, and how do I fix it?


texts: How to troublshoot updating to MSOLEDBSQL driver that is breaking macro-enabled Excel book?

texts:
I have an Excel macro-enabled file (.xlsm) with VBA that builds various SELECT statements and also executes a stored procedure on a SQL Server DB in order to populate a table. It is from a legacy system and uses an old deprecated driver (MSOLEDB.1), and I am trying to update it to read from a new database, while updating the driver as well.

I've updated the new connection string in the VBA code from using Provider=MSOLEDB.1 to the new MSOLEDBSQL driver (which is installed on new SQL Server). I can step through the code and all executes perfectly. I can run the macro from the refresh button on the sheet, all good. But when I save and close the file, and then attempt to re-open it - upon hitting Enable for the macro warning, it hangs excel. When I change it back to the old string, all works fine again- I can close and reopen the file, and re-run the code with no issues.

I can include more detail about both old and new environment, but I wanted to see if anyone even has any suggestions of how to troubleshoot this. The key difference is that old string authenticated via SQL login and the new one via Windows. (I don't want to authenticate via SQL login moving forward.)

Old (sensitive data replaced):
"PROVIDER=SQLOLEDB.1;DATA SOURCE=#.#.#.#\SQLEXPRESS;INITIAL CATALOG=Databasename;NETWORK=DBMSSOCN;USER ID=SQLUser;PASSWORD=password;"

New:
"Provider=MSOLEDBSQL;Server=servername;Database=Databasename;Trusted_Connection=yes"

Running Debug->Compile VBA Project has sorted out buggy things like this before, but not this time. I've tried recompiling every sheet with code (even added comments to sheets with no codes so I could recompile them) but no effect.

I also tried adding the ForceVBALoadFromSource from https://support.microsoft.com/en-us/help/3085435/august-8-2017-update-for-excel-2016-kb3085435 which had no effect.

I believe it has either something to do with the new driver with Windows authentication, but I am really hoping for some hints.


texts:
I'm having two oracle databases. A is used for production, B is some kind of staging instance where people are preparing content that is brought to the production system over night. It is a fully working structural clone of A that will also give you a preview of the upcoming next release. While the overnight synchronization is completely automatized (by scripts), structural changes are first applied to B and are two days later applied to A. This depends on manual operations and is build up on sql and sh scripts.

I'm wondering if there are features that lead to better maintainability and can be utilized for manually triggered synchronization. What would be the a modern approach automatizing structural database changes brought from B to A with Oracle Database 11g?

Please excuse this vague question. Since I'm not experienced in this topic I'm looking more for a starting point on what to read than a full solution to my problem.


texts: WorldWide site - should I save UTC or in fixed time?

texts:
Let's say I have a world wide website ( I mean it has uses from all around the world).

I have 1 server which is in germany ( at some city). it will never be moved from germany - ever.

My question is :

When users saves data  , I need to save the action date :

Should I save ( and I think I do) it as UTC ?

Or since - the server is in one location - it is ok to save all the dates as GetDATE()
so the point of relativity is the same point.

Am I wrong ? If I do , can you please supply a simple scenario where it's gonna fail ?


texts:
Using version 5.5 (on which I'm stuck), I have a table I want to partition, here is the DDL:

CREATE TABLE `emp` (
  `id` int(11) DEFAULT NULL,
  `date1` bigint(20) DEFAULT NULL,
  `gen_year` int(11) GENERATED ALWAYS AS (year(from_unixtime(`date1`))) VIRTUAL,
  `gen_month` int(11) GENERATED ALWAYS AS (month(from_unixtime(`date1`))) VIRTUAL,
  UNIQUE KEY `id` (`id`,`gen_year`,`gen_month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY HASH (12*gen_year+gen_month) PARTITIONS 77 ;

As you can see I want to partition by unique month (taking the year into account). I set 77 partitions because the test data I have put in there uniformly cover 77 months.

When I run the 1st query below, I get pruning just fine, only one partition gets queried.
When I run the 2nd query, I'd expect just a few partitions to be queried, but instead ALL are.

select count(*) from emp where gen_year=1984 and month=8;
select count(*) from emp where gen_year=1984 and month>8;
select count(*) from emp where gen_year=1984 and month BETWEEN 8 AND 12;

EXPLAIN PARTITIONS gives one partition for the first query, but all 77 partitions for the others.

Please advise on what I'm missing.


texts: How to monitor performance in application insights of an on prem SQL Server database?

texts:
Application Insights has great ways to monitor databases. After some online investigation, I saw an explanation of how to monitor actual queries. Monitoring SQL performance is possible too but what I found is an explanation for Azure SQL databases. I use on prem SQL Server....
What I want is doing such monitoring for on prem databases. However, the data needs to be monitored by application insights.
What I am typically interested in is this:

The number of records returned by each select query
The number of queries executed per hour/day
The performance of individual queries.

How can I do this?


texts: dumping database with "compressed data is corrupt" errors

texts:
I'm using pg_dump to dump a large (396 GB) PostgreSQL database:

pg_dump --clean --create mydatabase

After running for a day, a statement fails with ERROR:  compressed data is corrupt and pg_dump exits.

100% data integrity is not my top priority here. It's probably just one broken row that's preventing me from moving or backing up a db that took many weeks to create. I'm fine with losing that row.

Is there any way to create a dump that ignores such data errors? I found nothing in the pg_dump docs.

If not, how do I find and delete all corrupted rows in my table? Rows in other tables are pointing to them and will need to be deleted as well.


texts:
I wonder if it is safe to run the Tablediff tool on a database fairly often?
Let's say every 15 or 30 minutes. Also, the Data in the DB is around 100 GB.


texts: SSIS - This error is thrown by Connections collection when the specific connection element is not found

texts:
I am writing a package to upload result of query to FTP as CSV file.

Package is working fine on local machine, but after transferring .dtsConfig and .dtsx on another environment it throws following error message after executing all tasks in package.

  Description: The connection "{008A2775-60D6-44B1-8790-69958EB74371}"
  is not found. This error is thrown by Connections collection when the
  specific connection element is not found.  End Error  Error:
  2014-11-24 07:32:05.79     Code: 0xC001000E     Source: PackageName
  Description: The connection "{008A2775-60D6-44B1-8790-69958EB74371}"
  is not found. This error is thrown by Connections collection when the
  specific connection element is not found.  End Error  DTExec: The
  package execution returned DTSER_FAILURE (1).  Started:  7:32:05 AM
  Finished: 7:32:07 AM  Elapsed:  1.81 seconds.  The package execution
  failed.  The step failed.

any idea what will be the reason?


texts: In MS SQL how do I find out what tables have not been queried or updated in a long time?

texts:
I'm maintaining a database that is being shared by a few systems. Over the years a few of those systems have been deprecated and lots of the tables are actually orphaned. Is there a query I can run to find out which tables have not been used since a certain date?

Thanks.