texts: mysqldump reimport isn't working correctly

texts:
I am trying to restore this database from a my sql file, but I'm only getting the following output, and it's not importing anything into my db. Any thoughts?

[root@domU-12-31-39-0B-06-AA data]# mysqldump --host=localhost --password=abc1234 --    user=root --single-transaction blink10 < /data/tt.sql
-- MySQL dump 10.13  Distrib 5.1.52, for redhat-linux-gnu (x86_64)
--
-- Host: localhost    Database: blink10
-- ------------------------------------------------------
-- Server version       5.1.52

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2012-02-13  9:30:47


texts: How do I get more control over backup file retention using Ola Hallengren's DatabaseBackup solution?

texts:
I've noticed some unexpected behavior using the database backup job in Ola's Maintenance Solution. On the test instance I'm working on, I have the user database backup job scheduled to run at 2:00am every morning. I have the @CleanupTime parameter set to 48 and I haven't specified the @CleanupMode parameter.

If I'm understanding this correctly, that should run daily and keep two copies of the the database backup... Since the default is "AFTER_BACKUP", it should delete the oldest file after the new backup completes, only leaving the oldest file if the newest backup fails. So it should start with two days' of files in the folder, create a third file (for the current day) and then delete the oldest file of the three.

What's odd is that sometimes it leaves two days' worth of files and sometimes it leaves three, even though all of the backups are successfully completing. It's not a huge issue but, in the interest of saving space on that drive, I'd prefer to only keep two backups at any given time. Any tips or suggestions would be appreciated.

Edit:
As requested in the comments below, here is the list of parameters in that job step...

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'ALL_DATABASES',
@Directory = NULL,
@BackupType = 'FULL',
@Verify = 'N',
@CleanupTime = 48,
@CheckSum = 'Y',
@LogToTable = 'Y'


texts:
I am trying to return the first name, surname, subject and mark from the following tables:

SQL> desc students;
 Name                     Null?    Type
 ------------------------ -------- -------------------
 STUDENT_NO                        VARCHAR2(10)
 SURNAME                           VARCHAR2(20)
 FORENAME                          VARCHAR2(20)

SQL> desc modules;
 Name                     Null?    Type
 ------------------------ -------- -------------------
 MODULE_CODE                       VARCHAR2(8)
 MODULE_NAME                       VARCHAR2(20)

SQL> desc marks;
 Name                     Null?    Type
 ------------------------ -------- -------------------
 STUDENT_NO                        VARCHAR2(10)
 MODULE_CODE                       VARCHAR2(8)
 MARK                              NUMBER(38)

I have tried the following SQL query within SQL*Plus
SELECT DISTINCT FORENAME AS "First Name", SURNAME, MODULE_NAME AS "Subject", MARK
  FROM STUDENTS, MODULES, MARKS; but this displays duplicate entries.

The following simply does not work.

SELECT st.FORENAME AS "First Name", st.SURNAME, mod.MODULE_NAME AS "Subject"
  FROM STUDENTS st JOIN MODULES mod ON (st.STUDENT_NO = mod.STUDENT_NO);

Am I missing something? Maybe a subquery?
Please help, total database noob.


texts: Configuring PostgreSQL for read performance

texts:
Our system writes a lots of data (kind of Big Data system). Write performance is good enough for our needs but read performance is really too slow.

The primary key (constraint) structure is similar for all our tables:

timestamp(Timestamp) ; index(smallint) ; key(integer).

A table can have millions of rows, even billions of rows, and a read request is usually for a specific period (timestamp / index) and tag. It's common to have a query that returns around 200k lines. Currently, we can read about 15k lines per second but we need to be 10 times faster. Is this possible and if so, how?

Note: PostgreSQL is packaged with our software, so the hardware is different from one client to another.

It is a VM used for testing. The VM's host is Windows Server 2008 R2 x64 with 24.0 GB of RAM.

Server Spec (Virtual Machine VMWare)

Server 2008 R2 x64
2.00 GB of memory
Intel Xeon W3520 @ 2.67GHz (2 cores)

postgresql.conf optimisations

shared_buffers = 512MB (default: 32MB)
effective_cache_size = 1024MB (default: 128MB)
checkpoint_segment = 32 (default: 3)
checkpoint_completion_target = 0.9 (default: 0.5)
default_statistics_target = 1000 (default: 100)
work_mem = 100MB (default: 1MB)
maintainance_work_mem = 256MB (default: 16MB)

Table Definition

CREATE TABLE "AnalogTransition"
(
  "KeyTag" integer NOT NULL,
  "Timestamp" timestamp with time zone NOT NULL,
  "TimestampQuality" smallint,
  "TimestampIndex" smallint NOT NULL,
  "Value" numeric,
  "Quality" boolean,
  "QualityFlags" smallint,
  "UpdateTimestamp" timestamp without time zone, -- (UTC)
  CONSTRAINT "PK_AnalogTransition" PRIMARY KEY ("Timestamp" , "TimestampIndex" , "KeyTag" ),
  CONSTRAINT "FK_AnalogTransition_Tag" FOREIGN KEY ("KeyTag")
      REFERENCES "Tag" ("Key") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE,
  autovacuum_enabled=true
);

Query

The query takes about 30 seconds to execute in pgAdmin3, but we would like to have the same result under 5 seconds if possible.

SELECT
    "AnalogTransition"."KeyTag",
    "AnalogTransition"."Timestamp" AT TIME ZONE 'UTC',
    "AnalogTransition"."TimestampQuality",
    "AnalogTransition"."TimestampIndex",
    "AnalogTransition"."Value",
    "AnalogTransition"."Quality",
    "AnalogTransition"."QualityFlags",
    "AnalogTransition"."UpdateTimestamp"
FROM "AnalogTransition"
WHERE "AnalogTransition"."Timestamp" >= '2013-05-16 00:00:00.000' AND "AnalogTransition"."Timestamp" <= '2013-05-17 00:00:00.00' AND ("AnalogTransition"."KeyTag" = 56 OR "AnalogTransition"."KeyTag" = 57 OR "AnalogTransition"."KeyTag" = 58 OR "AnalogTransition"."KeyTag" = 59 OR "AnalogTransition"."KeyTag" = 60)
ORDER BY "AnalogTransition"."Timestamp" DESC, "AnalogTransition"."TimestampIndex" DESC
LIMIT 500000;

Explain 1

"Limit  (cost=0.00..125668.31 rows=500000 width=33) (actual time=2.193..3241.319 rows=500000 loops=1)"
"  Buffers: shared hit=190147"
"  ->  Index Scan Backward using "PK_AnalogTransition" on "AnalogTransition"  (cost=0.00..389244.53 rows=1548698 width=33) (actual time=2.187..1893.283 rows=500000 loops=1)"
"        Index Cond: (("Timestamp" >= '2013-05-16 01:00:00-04'::timestamp with time zone) AND ("Timestamp" <= '2013-05-16 15:00:00-04'::timestamp with time zone))"
"        Filter: (("KeyTag" = 56) OR ("KeyTag" = 57) OR ("KeyTag" = 58) OR ("KeyTag" = 59) OR ("KeyTag" = 60))"
"        Buffers: shared hit=190147"
"Total runtime: 3863.028 ms"

Explain 2

In my latest test, it took 7 minutes to select my data! See below:

"Limit  (cost=0.00..313554.08 rows=250001 width=35) (actual time=0.040..410721.033 rows=250001 loops=1)"
"  ->  Index Scan using "PK_AnalogTransition" on "AnalogTransition"  (cost=0.00..971400.46 rows=774511 width=35) (actual time=0.037..410088.960 rows=250001 loops=1)"
"        Index Cond: (("Timestamp" >= '2013-05-22 20:00:00-04'::timestamp with time zone) AND ("Timestamp" <= '2013-05-24 20:00:00-04'::timestamp with time zone) AND ("KeyTag" = 16))"
"Total runtime: 411044.175 ms"


texts: How can I detect the maximum value in several rows and columns at the same time in an SQL table?

texts:
I have a table which records values over time, similar to the following:

The next value after the zero value indicates the beginning of the highest value to one case for a given time.
Example: To Time6 the maximum value is 5 because only case4 Start (after zero value) in that moment the others continuing decreasing at that time.

I need to know at a given moment what is the maximum value case for all the times shown and gradually place it as shown in the following table:

I have worked with some of the aggregate and analytic functions, but can't seem to find the correct solution especially because query is to a table of 40 million records.
I hope an SQL expert guru can help me out, or at least point me in the right direction or some efficient algorithm. Is there a way to query this (preferably without a cursor?)

Thanks in advance.


texts: List Top Daily Rentals Per Customer using a CTE

texts:
I've been trying to learn CTEs and having a bit or trouble getting the hang of them.  I wrote a query against the Sakila Sample database that lists information about horror movie rentals for each day.

Here is the overblown (and redundant) SQL that I came up with:

SELECT CONCAT(CU.last_name, ', ', CU.first_name) AS customer,
        A.phone,
        F.title,
        date(R.rental_date) AS rental_date
 FROM sakila.rental R
     LEFT JOIN sakila.inventory I ON R.inventory_id = I.inventory_id
     LEFT JOIN sakila.film F ON I.film_id = F.film_id
     LEFT JOIN sakila.film_category FC on F.film_id = FC.film_id
     LEFT JOIN sakila.category C ON FC.category_id = C.category_id
     LEFT JOIN sakila.customer CU ON R.customer_id = CU.customer_id
     LEFT JOIN sakila.address A ON CU.address_id = A.address_id
 WHERE CU.customer_id in
       (SELECT CU.customer_id
        FROM rental R
        LEFT JOIN sakila.customer CU ON R.customer_id = CU.customer_id
        LEFT JOIN sakila.inventory I ON R.inventory_id = I.inventory_id
        LEFT JOIN sakila.film F ON I.film_id = F.film_id
        LEFT JOIN sakila.film_category FC on F.film_id = FC.film_id
        LEFT JOIN sakila.category C ON FC.category_id = C.category_id
        WHERE C.name = "Horror"
        GROUP BY CU.customer_id
        HAVING COUNT(CU.customer_id) >= 3)
 AND C.name = "Horror"
 ORDER BY customer, title, rental_date DESC;

And here are some of the results in my database client (Navicat for MySQL):

Is there a way to rewrite the query using a CTE?

It seems like just the type of query that a CTE would be perfect for, if only I could figure it out!

Thanks!


texts: How to create multiple mariaDB mysql instance in CentOS 7? I have already installed first mariaDB mysql instance in CentOS 7. I've been searching for documentations on how to configure a Multi-Master Replication in MariaDB but I can't find any. I use to try some configurations for MySQL but they didn't work. Wsrep were both ON in my two nodes but when I connect the second node to the donor, there's an error while restarting mysql. I'm using CentOS 7 OS. Can someone provide a documentation for MariaDB-Galera cluster Multi-Master Replication?


texts:
I have a plain text file with 3 million articles divided with \n.
The file is 58G

When I'm adding the articles to an InnoDB table one per row

INT id
TEXT article_content

the .ibd file of this table (innodb_file_per_table=1) takes up 98G

Can someone explain if such increase in disk space of a table comparing to a text file is normal ? Is there a way to store such content more efficiently ?


texts:
I have an Oracle 11g on RHEL server. My /oradata volume reach 95% disk usage :

[ora11g@bocc7-1-qrec-01 ~]$ df -h
/dev/sdb1             689G  571G   84G  95% /oradata

The Flash Recovery Area (FRA) is the larger folder :

[ora11g@bocc7-1-qrec-01 ~]$ du -skh /oradata/* | sort -h
50G     /oradata/qtest
175G    /oradata/qrec
346G    /oradata/flash_recovery_area

Inside the FRA I can see that the archive logs take approx 230Gb:

[ora11g@bocc7-1-qrec-01 ~]$ du -skh /oradata/flash_recovery_area/qrec/*
232G    /oradata/flash_recovery_area/qrec/archivelog
7.9G    /oradata/flash_recovery_area/qrec/onlinelog

I checked the v$recovery_file_dest table and my SPACE_LIMIT is 20Gb and my SPACE_USED is only 10Mb!

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

qrec> SELECT * FROM V$RECOVERY_FILE_DEST;

NAME
--------------------------------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
/oradata/flash_recovery_area
 2.1475E+10   10984448                 0               1

So, I deduced that it had nothing to do with this table.

Now I'm checking with RMAN :

RMAN> crosscheck archivelog all;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=259 device type=DISK
validation succeeded for archived log
archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1056_965658004.arc RECID=1057 STAMP=969146207
validation succeeded for archived log
archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1057_965658004.arc RECID=1058 STAMP=969149808
validation succeeded for archived log
archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1058_965658004.arc RECID=1059 STAMP=969153409
validation succeeded for archived log
archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1059_965658004.arc RECID=1060 STAMP=969157010
validation succeeded for archived log
archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1060_965658004.arc RECID=1061 STAMP=969160610
validation succeeded for archived log
archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1061_965658004.arc RECID=1062 STAMP=969164210
validation succeeded for archived log
archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1062_965658004.arc RECID=1063 STAMP=969167813
validation succeeded for archived log
archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1063_965658004.arc RECID=1064 STAMP=969171413
validation succeeded for archived log
archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1064_965658004.arc RECID=1065 STAMP=969175014
validation succeeded for archived log
archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1065_965658004.arc RECID=1066 STAMP=969178614
validation succeeded for archived log
archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1066_965658004.arc RECID=1067 STAMP=969182214
validation succeeded for archived log
archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1067_965658004.arc RECID=1068 STAMP=969185814
Crosschecked 12 objects

RMAN> delete noprompt expired archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=259 device type=DISK
specification does not match any archived log in the repository

So the 1st question is: Why does RMAN not see my 2752 ARC files here :

[ora11g@bocc7-1-qrec-01 ~]$ ls -1 /oradata/flash_recovery_area/qrec/archivelog | wc -l
2752

And the 2nd question is: What should I do to free some disk space by removing old archivelog files? (the older ones are more than 1 year old!!)  :

[ora11g@bocc7-1-qrec-01 archivelog]$ find -type f -printf '%T+ %p\n' | sort | head -n 1
2017-02-22+16:54:41.0000000000 ./log_2_1_936636608.arc


texts: Why does the estimated cost of (the same) 1000 seeks on a unique index differ in these plans?

texts:
In the queries below both execution plans are estimated to perform 1,000 seeks on a unique index.

The seeks are driven by an ordered scan on the same source table so seemingly should end up seeking the same values in the same order.

Both nested loops have <NestedLoops Optimized="false" WithOrderedPrefetch="true">

Anyone know why this task is costed at 0.172434 in the first plan but 3.01702 in the second?

(The reason for the question is that the first query was suggested to me as an optimisation due to the apparent much lower plan cost. It actually looks to me as though it does more work but I'm just attempting to explain the discrepancy...)

Setup

CREATE TABLE dbo.Target(KeyCol int PRIMARY KEY, OtherCol char(32) NOT NULL);

CREATE TABLE dbo.Staging(KeyCol int PRIMARY KEY, OtherCol char(32) NOT NULL);

INSERT INTO dbo.Target
SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY @@SPID), LEFT(NEWID(),32)
FROM master..spt_values v1,
     master..spt_values v2;

INSERT INTO dbo.Staging
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY @@SPID), LEFT(NEWID(),32)
FROM master..spt_values v1;

Query 1 "Paste the plan" link

WITH T
     AS (SELECT *
         FROM   Target AS T
         WHERE  T.KeyCol IN (SELECT S.KeyCol
                             FROM   Staging AS S))
MERGE T
USING Staging S
ON ( T.KeyCol = S.KeyCol )
WHEN NOT MATCHED THEN
  INSERT ( KeyCol, OtherCol )
  VALUES(S.KeyCol, S.OtherCol )
WHEN MATCHED AND T.OtherCol > S.OtherCol THEN
  UPDATE SET T.OtherCol = S.OtherCol;

Query 2 "Paste the plan" link

MERGE Target T
USING Staging S
ON ( T.KeyCol = S.KeyCol )
WHEN NOT MATCHED THEN
  INSERT ( KeyCol, OtherCol )
  VALUES( S.KeyCol, S.OtherCol )
WHEN MATCHED AND T.OtherCol > S.OtherCol THEN
  UPDATE SET T.OtherCol = S.OtherCol;

Query 1

Query 2

The above was tested on SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)

@Joe Obbish points out in the comments that a simpler repro would be

SELECT *
FROM staging AS S
  LEFT OUTER JOIN Target AS T
    ON T.KeyCol = S.KeyCol;

vs

SELECT *
FROM staging AS S
  LEFT OUTER JOIN (SELECT * FROM Target) AS T
    ON T.KeyCol = S.KeyCol;

For the 1,000 row staging table both of the above still have the same plan shape with nested loops and the plan without the derived table appearing cheaper, but for a 10,000 row staging table and same target table as above the difference in costs does change the plan shape (with a full scan and merge join seeming relatively more attractive than expensively costed seeks) showing this cost discrepancy can have implications other than just making it harder to compare plans.


texts: Difficulty getting sp_executesql to work

texts:
Alex Aza's comment solution here generates all names of tables with records referencing a specified input record. I would like to adapt Alex's solution into a table-valued function. I'm having trouble seeing how to parameterize his dynamic SQL code correctly. The single-vs-double quotes are tripping me up, for example.
A typical value of @Command from Alex's solution is as follows:
SELECT 'PrimaryTable'
WHERE EXISTS(
    SELECT *
    FROM PrimaryTable
    WHERE ForeignKey_PrimaryTable = 999
    )
UNION ALL
SELECT 'ForeignTable_A'
WHERE EXISTS(
    SELECT *
    FROM ForeignTable_A
    WHERE ForeignKey_PrimaryTable = 999
    )
UNION ALL
SELECT 'ForeignTable_B'
WHERE EXISTS(
    SELECT *
    FROM ForeignTable_B
    WHERE ForeignKey_PrimaryTable = 999
    );

I think I've set up the framework correctly & would appreciate any assistance with assignment of the @Command variable.
Thanks so much for your consideration!
My partial solution is as follows:
-- Declarations needed for sp_executesql
declare @ExecParameterDefinition NVARCHAR(1024)
declare @ExecOutput TABLE(TableName NVARCHAR(1024));
declare @CapturedOutputTable TABLE(TableName NVARCHAR(1024));
SET @ExecParameterDefinition =
    N'@RowId int, ' +
    N'@TableName sysname, ' +
    N'@ExecOutput TABLE(TableName NVARCHAR(1024)) OUTPUT'

    -- Alex Aza's solution
    declare @Command nvarchar(max)

    -- This statement needs to be completely quoted as an executable string:
    SET @Command = isnull(@Command + ' union all ', '') + 'select '''
    + object_name(parent_object_id) +
    ''' where exists(select * from ' + object_name(parent_object_id)
    + ' where ' + col.name+ ' = ' + cast(@RowId as varchar) + ')'
    from sys.foreign_key_columns fkc
    join sys.columns col on
    fkc.parent_object_id = col.object_id and fkc.parent_column_id = col.column_id
    where object_name(referenced_object_id) = @TableName;

-- Display constructed SQL command:
SELECT @Command;

-- Execute dynamic SQL & capture output into a table variable:
EXEC sp_executesql
    @Command,
    @ExecParameterDefinition,
    @RowId = 999,
    @TableName = 'PrimaryTable',
    @CapturedOutputTable = @ExecOutput OUTPUT

-- Display captured results:
select * FROM @CapturedOutputTable

UPDATE : Thanks, Paul White, for pointing out that dynamic-SQL can't be called from within a function. It appears though that I could call sp_executesql from a stored procedure & simply return a bit value indicating whether sp_executesql returned any results or not. Does this make my problem tractable?
UPDATE 2 : I finished parameterizing my code solution, which was incomplete in my original post. Solutions provided here, such as Hannah Vernon's below, can now be quickly tested. I also included Hannah's suggestion to "SELECT @Command".
Running this code with Hannah's suggested solution seems to hit a syntax error in the call to sp_executesql:
(1 row(s) affected)
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'TABLE'.

(0 row(s) affected)

(1 row(s) affected)

I can only imagine that the @ExecOutput table variable declaration must be incorrect within @ExecParameterDefinition..? Could someone please correct me on this as well?


texts: MySQL Master-Master - different bin logs

texts:
We have Master <> Master replication. After some start-stop issues - I found some odds in replication.

First - on both boxes we have an error:

160226 10:08:59 [Note] Slave I/O thread: connected to master 'user@172.**.**.51:3306',replication started in log
'mysql-bin.000210' at position 107
160226 10:08:59 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file (
server_errno=1236)
160226 10:08:59 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log
file name in binary log index file', Error_code: 1236
160226 10:08:59 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000210', position 107

Status on both nodes also says:

      Master_Log_File: mysql-bin.000210
  Read_Master_Log_Pos: 107
       Relay_Log_File: relay.000629
        Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000210
...
  Exec_Master_Log_Pos: 107
      Relay_Log_Space: 107
                Last_IO_Errno: 1236
        Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in
binary log index file'

I found a possible solution to fix a 1236 error - jusr move replication to last bin-log at first position, i.e.:

STOP SLAVE;
CHANGE MASTER TO MASTER_LOG_POS = 0;
CHANGE MASTER TO MASTER_LOG_FILE = 'mysql-bin.0000LASTLOGNUM';
START SLAVE;

But here is what confused me.

Master_1:

# ls -l /var/log/mysql/mysql-bin.*
-rw-rw---- 1 mysql adm  126 Feb 26 09:56 /var/log/mysql/mysql-bin.000213
-rw-rw---- 1 mysql adm  126 Feb 26 10:15 /var/log/mysql/mysql-bin.000214
-rw-rw---- 1 mysql adm 1054 Feb 26 11:13 /var/log/mysql/mysql-bin.000215
-rw-rw---- 1 mysql adm   96 Feb 26 10:15 /var/log/mysql/mysql-bin.index

While Master_2 have:

# ls -l /var/log/mysql/mysql-bin.*
-rw-rw---- 1 mysql adm 126 Feb 26 07:26 /var/log/mysql/mysql-bin.000212
-rw-rw---- 1 mysql adm 107 Feb 26 09:39 /var/log/mysql/mysql-bin.000213
-rw-rw---- 1 mysql adm  64 Feb 26 09:39 /var/log/mysql/mysql-bin.index

What is the correct way then?

Set CHANGE MASTER TO MASTER_LOG_FILE = 'mysql-bin.000215'; on Mater_1?
What then to do on the Master_2?

P.S. In fact - I can just drop && recreate databases on both hosts - there is no sensitive data at this moment and this must "reload" replication process (amirite?). But out of curiosity - what's wrong with those replications?


texts: Postgresql get rows based on minimum value

texts:
Having this table:

warehouse_id
destination_id
days

1
1
2

1
1
3

2
1
3

2
1
4

3
1
5

3
2
5

1
2
2

2
2
3

I'd like to get the warehouse_id and days for each value of warehouse_id, where the row has the minimum value of days, and destination_id matches a specific value, ordered by days.
For example for destination_id = 1

warehouse_id
days

1
2

2
3

3
5


texts: Login in server suddenly lost mapping to their Users in databases

texts:
Mine is Always ON availability environment. I have 3 SQL 2014 servers(Primary and 2 replicas). Since one year all the logins working FINE in the servers. Some how suddenly, I am getting errors from the Application server stating that the PIDS were not able to access the Database. The PIDS are successfully logging in the server but not able to access database. I checked the mappings of the login in the server. I seen no database was checked. I dont know how it lost its mapping to user in the database.

When I checked for the user in the database, I can see that user exist in the database and has all the required privileges assigned to that user too..

NOte: I tried to restart the server that which fix the problem for 2 hours and again we ran back to the same issue. I droped all those users(who lost mapping from Login) and recreated them with all the SIDs synced in all three servers. This fixed my problem for now. but I want to know the root cause of the issue.

Question 1: How my user lost their mappings with login suddenly that which are working absolutely fine since 1 YEAR.

Question 2: How a simple server restart fix the issue for a couple of hours and again why the problem came back.


texts: Varchar index - will hashing value make it faster?

texts:
I have a VARCHAR(1000) column in a table. It will contain strings that will not be guaranteed to be unique. I have a query that searches this column as part of a WHERE IN clause, the list of values in the IN ('...') list is going to be approx 100. The table will likely have millions of rows after the first few months. I understand that indexing this could slow down inserts and could create quite a large index.
QUESTIONS

Would it be faster to store a hash of the value as well and instead index and search on that?
Does that even make sense if the values are not guaranteed to be unique?
If hashing the values gives them a consistent length, would indexing that make queries faster?

I am running mysql 5.1 and using INNODB engine.


texts: ForeignKey setup clarification on updating related fields

texts:
Considering these two tables in PosgtreSQL:
# report
+----+-------------+--------------------+
| id | report_name | report_description |
+----+-------------+--------------------+
| 1  | RPT         | ABCDEFGH           |
| 2  | TST         | LMNOPQRS           |
+----+-------------+--------------------+

# report_years
+----+--------------+-------------+------+
| id | report_id_fk | report_name | year |
+----+--------------+-------------+------+
| 11 | 1            | RPT         | 2019 |
| 12 | 1            | RPT         | 2020 |
| 13 | 2            | TST         | 2019 |
+----+--------------+-------------+------+

Given that report_id_fk is a foreign key in the report_years table, there wouldn't be a way to CASCADE and update the report_name based on report_id_fk?
They would have to be separate foreign keys?
report_name shouldn't even be in the report_years table in the first place since it is redundant and should just be queried over with a JOIN to report?
Is it possible to have a multifield foreign key constraint such that report_name and year would be required for a CASCADE?

For example:
# report
+----+-------------+------+
| id | report_name | year |
+----+-------------+------+
| 1  | RPT         | 2018 |
| 2  | RPT         | 2019 |
| 3  | RPT         | 2020 |
+----+-------------+------+

# report_details
+----+-----------+----------------+---------+---------+
| id | report_id | report_name_fk | year_fk | details |
+----+-----------+----------------+---------+---------+
| 11 | 1         | RPT            | 2018    | ABC     |
| 12 | 2         | RPT            | 2019    | DEF     |
| 13 | 3         | RPT            | 2020    | GHI     |
+----+-----------+----------------+---------+---------+

If I wanted to change the report_name for year = 2020 in report, it would CASCADE to report_details and update the report_name_fk based on the report_name_fk and year_fk?
Or (back to question 1), can I setup a CASCADE that would updated report_name_fk and year_fk in report_details?

I can see how to do this with a manual UPDATE, but seeing if it is possible with CASCADE. Or, like I think, this is redundant, should be avoided and if you need to know report_name and year for a report in report_details, you should just write a query an JOIN it.


texts:
Please can anyone help me in resolving the deadlock of which details and  screenshots are given below?
Occasionally, two stored procedures are resulting in a deadlock. I was able to pin down the statements that caused the conflict. Only one table and one clustered and one non-clustered index is involved. For demonstration purposes, I renamed the table and indexes in a test environment. How can I avoid this deadlock?
Thank you


texts: create dynamic table in store procedure without using any paramaters

texts:
CREATE OR REPLACE procedure create_table_dateWise21()
LANGUAGE plpgsql
as $$
BEGIN
EXECUTE format('
   CREATE TABLE IF NOT EXISTS test21 (
    nodeip text,
    interface text,
    nodeip_interface text
   )');
commit;
end;
$$;

if we 'call create_table_dateWise21()'. It create only test21 table but i want every time new table with the same columns


texts: delete var/lib/mysql folders after dropping database via mysql

texts:
I am aware that you don't want to remove the innodb tables from /var/lib/mysql for a linux distro of mysql (I have Ver 14.14).  So I used the command
drop database mydb1;
drop database mydb2;
-- etc

but see that the folders mydb1 and mydb2 etc. are sill in /var/lib/mysql, and the .MYD, .MYI and .frm files are there in most cases.  Are the database folders safe to manually delete now?  I know the innodb files are very sensitive.


texts: How to build a query that gets its values from a file

texts:
I'm trying to build a query that uses the values in a file

select Epm,Emp_num,Emp_Addr From Dir_Rimco_Table where Emp in (Dir_Emp_Rsed.txt)

Constraints

-I can't create a new table

-I can't modify Dir_Rimco_Table

-Current Privileges

Any idea how I can do that?


texts: Get Data to connect to the AdventureWorksDW

texts: I am trying to connect to a SQL Server 2017 via Visual Studio 2017.What should I be entering in the User name and Password fields?


texts: Authentification failure for PostgreSQL server (9.3)

texts:
I can currently not connect to my Postgresql server (9.3) from pgadmin (Running on Ubuntu 14.04). The server according to pg_lsclusters is on port 5433.

    Ver Cluster Port Status Owner    Data directory               Log file
    9.1 main    5432 online postgres /var/lib/postgresql/9.1/main /var/log/postgresql/postgresql-9.1-main.log
    9.3 main    5433 online postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.3-main.log

I can connect to the 9.1 server with pgadmin, but when I try to access the 9.3 server I get the error that the password can't be authenticated:

    An error has occurred:

    Error connecting to the server: FATAL:  Passwort-Authentifizierung für Benutzer „postgres“ fehlgeschlagen
    FATAL:  Passwort-Authentifizierung für Benutzer „postgres“ fehlgeschlagen

The 9.1 server has the following lines in pg_hba.conf:

    # Database administrative login by Unix domain socket
    local   all             postgres                                peer

    # TYPE  DATABASE        USER            ADDRESS                 METHOD

    # "local" is for Unix domain socket connections only
    local   all             all                                     peer
    # IPv4 local connections:
    host    all             all             127.0.0.1/32            md5
    # IPv6 local connections:
    host    all             all             ::1/128                 md5
    # Allow replication connections from localhost, by a user with the
    # replication privilege.
    #local   replication     postgres                                peer
    #host    replication     postgres        127.0.0.1/32            md5
    #host    replication     postgres        ::1/128                 md5

and the 9.3 server has the following in the pg_hba.conf:

    # Database administrative login by Unix domain socket
    local   all             postgres                                md5

    # TYPE  DATABASE        USER            ADDRESS                 METHOD

    # "local" is for Unix domain socket connections only
    local   all             all                                     md5
    # IPv4 local connections:
    host    all             all             127.0.0.1/32            md5
    # IPv6 local connections:
    host    all             all             ::1/128                 md5
    # Allow replication connections from localhost, by a user with the
    # replication privilege.
    #local   replication     postgres                                peer
    #host    replication     postgres        127.0.0.1/32            md5
    #host    replication     postgres        ::1/128                 md5

The difference is the "peer" or "md5" in the first two active lines. I am going to try switching those to peer next. Am I on the right track to a solution? (Edit: No, this did not help).

Password

I tried changing the default password of the server using:

    $ sudo -u postgres psql
    psql (9.3.4, Server 9.1.13)

    postgres=#  \password

I have no idea why this didn't work. Maybe the command only affects the 9.1 server and not the 9.3 server (Hopefully I can kill the old one now and properly set up the 9.3 server).

Related questions:

-https://stackoverflow.com/questions/18664074/getting-error-peer-authentication-failed-for-user-postgres-when-trying-to-ge

-https://stackoverflow.com/questions/2942485/psql-fatal-ident-authentication-failed-for-user-postgres?rq=1


texts: Can I Improve Performance on Bloated System Tables?

texts:
Background:
I have numerous databases with a large number of VIEW's, and an extremely large number of SYNONYM's.  For example, one db has more than 10k VIEW's and 2+ million SYNONYM's.

General Problem:
Queries involving sys.objects (and system tables in general) tend to be slow.  Queries involving sys.synonyms are glacial.  I am wondering what I can do to improve performance.

Specific Example
This command is run by a third party tool.  It is slow in both the app, and in SSMS:

exec sp_tables_rowset;2 NULL,NULL

My Question:
How can I make this run faster?

What I've Tried:
If I SET STATISTICS IO ON I get this output:

  (2201538 row(s) affected)Table 'sysobjrdb'. Scan count 1, logical
  reads 28, physical reads 0, read-ahead reads 0, lob logical reads 0,
  lob physical reads 0, lob read-ahead reads 0. Table 'sysschobjs'. Scan
  count 1, logical reads 53926, physical reads 0, read-ahead reads 0,
  lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

I have been able to update statistics on the underlying system tables.  This has worked in my SQL 2008 R2 or newer environments:

UPDATE STATISTICS sys.sysobjrdb WITH FULLSCAN
UPDATE STATISTICS sys.sysschobjs WITH FULLSCAN

I have also been able to perform index maintenance.  This works in my SQL 2012 or newer environments.  For instance running sp_help 'sys.sysschobjs' identifies the indexes on the table, and from there I create and run these commands:

ALTER INDEX clst ON sys.sysschobjs REORGANIZE
ALTER INDEX nc1 ON sys.sysschobjs REORGANIZE
ALTER INDEX nc2 ON sys.sysschobjs REORGANIZE
ALTER INDEX nc3 ON sys.sysschobjs REORGANIZE

Updating stats and reorganizing indexes helps, but not by much.


texts: PostgreSQL - Details/counts in pg_dump file

texts:
I'm involved with converting a system from Oracle to PostgreSQL.
We currently use database dumps quite heavily to move data between systems.
For Postgresql, I'm using pdump -Fc and restoring using pg_restore.

The Oracle export/import commands produce a log file showing each table and its row counts (on both export and import).
Is it possible to get something similar from PostgreSQL?, there's a Verbose option but it doesn't seem to go as far as table and row counts.

Thanks in advance, any guidance would be much appreciated.


texts: Sql Server 2008 Database server Minimum Requirements

texts: Does anybody knows what is the base scenario for the sql server 2008 r2 minimum hardware requirements?. By base scenario I mean: Database Size, average # of transactions, number of users connected, etc...


texts:
I'm struggling here on how to do this I couldn't find anywhere or won't be able understand the documentation.

So I'm connected as a DBA user on a DB2 client (TOAD for example) and I want to create a new user to the database from this client, I went to the User tab, right click on it and chose the Add User option.

There is no place to add the user password. Even confirming the create command the user appears on the users list.

So I went to the command line tool and try to create a new using the GRANT command as:

GRANT DBADM WITH DATAACCESS WITH ACCESSCTRL ON DATABASE TO USER TEST;

The user gets created (I see it on the user tab on TOAD) but I'm unable to connect with it because I didn't define the password.

So, how can I define a password for newly created user on DB2 that it is not an OS user?


texts: MySQL Replication - Master Master - Single Master write

texts:
I am looking to improve our fault tolerance for our persistent store. After reading several online docs about how to set-up master <> master replication, I'd still like to ask the experts about the order of set-up, to ensure I don't miss anything important.

Our setup will be a master <> master, however, only one master will be written to at a time. This will be controlled via "floating ip" which can be set to point at the secondary master, if the primary master fails.

With a single write master <> master setup, I don't need to worry about setting the auto_increment_offset correct?
Does MySQL replicate all databases by default, or do I need to explicitly identify them via binlog-do-db?
Do I need to do an export from master1 and import to master2, before I start replication, or can I start master 2 to pick up all transactions over the last several months?
Just to be clear, I need to setup a "replicator" user (with replication perms) on both master1 and master2?

-- Update 8.15.2014 --

This is the guide I followed for my intial setup

To answer question #2, I did the following:

Notice that I have a performance_schema table, this is because I am using Percona MySQL. Per Rolando's edit, the following is not needed if you want to replicate ALL databases, however I am leaving here for posterity to show others how they can specify multiple databases, if they do not want to export all.

binlog_do_db = information_schema
binlog_do_db = mysql
binlog_do_db = performance_schema
binlog_do_db = example_blog
binlog_do_db = example_core
binlog_do_db = example_log

To answer question #3, I ended up creating a snapshot of Master1 by issuing the following command:

mysqldump -h localhost -p -u root --opt --all-databases --single-transaction --master-data  > /tmp/example_snapshot_`date +%Y_%m_%d__%H_%M_%S`.sql

Notice the --master-data param, according to mysql docs, this allows us to create snapshot without have to create a new session and deal with table locking. However it does add an extra line the resulting .sql file:

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000029', MASTER_LOG_POS=107;

I removed this line b/c I wanted to affect this change manually, on each master.

Happy to report that replication appears to be working. Thanks Rolando!


texts:
When I develop complex queries and I make a mistake (e.g. a wrong join that will make the query run for hours or days) Oracle becomes unusable (you can't even start a local SYS session from SQL*Plus). I have to log into the Windows Server box that hosts it and restart the Oracle services.

In this situations I'd prefer to get the query killed if it runs for more than a certain (short) amount of time.

I've read about profiles and LIMIT_USER_QUERY_TIME but it isn't flexible enough: you have to choose a fixed timeout and enforce it on every action of the given user.

Is it possible to establish a variable timeout on specific connections or queries? Something like:

-- Fake directive:
ALTER SESSION SET abort_queries_that_run_for_more_than_x_seconds 60;

SELECT *
FROM FOO
CROSS JOIN BAR;

ALTER SESSION SET abort_queries_that_run_for_more_than_x_seconds NULL;


texts: MySQL bug: NOT IN + subquery using GROUP BY HAVING returns nothing

texts:
I'm querying on this table:

SKU
aaaa
bbbb
bbbb
NULL

Here's the query:

select *
from TEST as N
where  N.SKU NOT IN (select SKU
                 from TEST
                 group by SKU
                 having count(*)>1);

I expect the query returns 'aaaa', however, it returns nothing.

The reason I expect that is because the subquery below only returns 'bbbb':

select SKU
from TEST
group by SKU
having count(*)>1

Therefore, 'aaaa' NOT IN the subquery result.

To show the bug please copy and paste these statements in your MySQL IDE to create schema:

drop table if exists TEST;
create table TEST(
SKU varchar(255)
);
insert into TEST values('aaaa'),('bbbb'),('bbbb'),(NULL);

Even more confusing, try this new table below, re-run the query, and check result:

drop table if exists TEST;
create table TEST(
SKU varchar(255)
);
insert into TEST values('aaaa'),('cccc'),('dddd'),('bbbb'),('bbbb'),(NULL);

Expect 'aaaa', 'cccc', 'dddd' are all returned, however 'aaaa' is missing, yet 'cccc' and 'dddd' are returned.


texts:
I have a CTE that resulting something like this:
i x y
A 4 ...
A 4 ...
A 4 ...
A 4 ...
B 2 ...
B 2 ...
C 3 ...

And already have a query that uses most of them:
WITH cte AS ( ... ) -- above is the example result of the cte
SELECT
    COUNT(DISTINCT (CASE WHEN ... THEN i END)) bla_count1
  , COUNT(DISTINCT (CASE WHEN ... THEN i END)) bla_count2
  , SUM(x)/COUNT(*) bla_average
  -- and much more similar to above
  , -- how to get sum of A, B, C (4+2+3)
FROM cte

How to get the SUM of x DISTINCT by i (4+2+3)?
Modified Fiddle


texts: MySQL restore only Incremental backups with innobackupex

texts:
I am migrating a product to AWS and we have 1.5TB of MySQL database. I want to migrate it to AWS with minimum downtime possible. I am planning to achieve following.

Take one full backup with Percona XtraBackup innobackupex.
Restore it on AWS node.
Then on the day of final migration take an incremental backup and restore it on AWS node.

So my question is, is it possible this way ?

According to my research it is not possible to restore only "incremental" backup. The way Percona described that we need to take full backup then incremental backups and then prepare them and get a one full backup that cane be restored on destination.


texts:
I have this very old and SLOW query that I am trying to optimize, but I am not sure I can do anything to it, but add more indexes on columns involved in WHERE, JOIN and ORDER BY.

Query:

SELECT TOP 400 jobticket.jobnumber, jobticket.typeform, jobticket.filename, jobticket.req_number, jobticket.reqd_del_date, jobticket.point_of_contact, jobticket.status, jobticket.DapsDate, jobticket.elpod, job_info.IDOrderMaskedStatus, job_info.job_status, job_info.SalesID, job_info.location, job_info.TOMetadataID
FROM jobticket WITH (NOLOCK)
INNER JOIN job_info WITH (NOLOCK) ON job_info.jobnumber = jobticket.jobnumber
WHERE
(
    NOT(
        (jobticket.status = 'Complete' OR jobticket.status = 'Completed')
         and (job_info.job_status = 'Actualized' OR job_info.job_status = ''
              OR job_info.job_status = 'Actualized Credit Billed'
              OR job_info.job_status = 'DWAS Actualized' OR job_info.job_status = 'DWAS Actualized Credit Billed'
             )
        )
    or
    ((SELECT COUNT(job_status) AS Expr1 FROM tblConsolidatedBilling AS tblConsolidatedBilling_1 WITH (NOLOCK)
      WHERE  (job_status <> 'Actualized'
      AND  job_status <> 'Actualized Credit Billed')
      AND (master_jobnumber = jobticket.jobnumber)) > 0)
)
and (jobticket.status != 'Waiting Approval' or (jobticket.status = 'Waiting Approval' and jobticket.DPGType is null))
and jobticket.typeform <> 'todpg'
and ((job_info.isHidden <> 1 or job_info.isHidden is null) and job_info.isInConcurrentRelease is null)
and job_info.deleted != '1'
and jobticket.status != 'New Job'
and jobticket.status != 'PRFYCLSFD'

ORDER BY
job_info.expediencyLevel DESC,
jobticket.jobnumber DESC

Execution Plan:

In all honesty I don't know what to do with this query.

Should I add individual nonclustered indexes on all columns involved in WHERE JOIN and ORDER BY?

There are many indexes on these tables, but I am not sure whether they are helpful in this query:


texts: SQL Server 2014 - UPSERT XML Multiple rows

texts:
We have an input like this

    <?xml version="1.0" encoding="UTF-8"?>
    <Orders xmlns:jjsales:api:sales>
        <Order>
            <OrderID>334</OrderID>
            <AmountPaid currencyID="EUR">17.10</AmountPaid>
            <UserID>marc58</UserID>
            <ShippingAddress>
                <Name>Marc Juppé</Name>
                <Address>Rue</Address>
                <City>Paris</City>
                <StateOrProvince></StateOrProvince>
                <Country>FR</Country>
                <Phone>333333333</Phone>
                <PostalCode>22222</PostalCode>
             </ShippingAddress>
             <ShippingCosts>4.50</ShippingCosts>
             <Lines>
                <Line>
                   <Details>
                        <ItemID>3664</ItemID>
                        <Store>47</Store>
                        <Title>MCPU DDA010</Title>
                        <SKU>mmx</SKU>
                    </Details>
                    <LineID>1111</LineID>
                    <Quantity>1</Quantity>
                    <Price currencyID="EUR">6.2</Price>
                </Line>
                <Line>
                   <Details>
                        <ItemID>3665</ItemID>
                        <Store>45</Store>
                        <Title>MCPU DFZ42</Title>
                        <SKU>mmy</SKU>
                    </Details>
                    <LineID>1112</LineID>
                    <Quantity>2</Quantity>
                    <Price currencyID="EUR">3.2</Price>
                </Line>
            </Lines>
        </Order>
    </Orders>

and we are currently importing data into Sales Table with a query like this:

    INSERT INTO Sales (ItemID,Store,Title,SKU,Quantity,Price,OrderID,AmountPaid,UserID,ShippingCosts)

    SELECT I.X.value('(Details/ItemID/text())[1]', 'int') as ItemID,
           I.X.value('(Details/Store/text())[1]', 'int') as Store,
           I.X.value('(Details/Title/text())[1]', 'nvarchar(100)') as Title,
           I.X.value('(Details/SKU/text())[1]', 'nvarchar(100)') as SKU,
           I.X.value('(Quantity/text())[1]', 'int') as Quantity,
           I.X.value('(Price/text())[1]', 'decimal(11,2)') as Quantity,
           O.X.value('(OrderID/text())[1]', 'int') as OrderID,
           O.X.value('(AmountPaid/text())[1]', 'decimal(11,2)') as AmountPaid,
           O.X.value('(UserID/text())[1]', 'nvarchar(100)') as UserID,
           O.X.value('(ShippingCosts/text())[1]', 'decimal(11,2)') as ShippingCosts
    FROM @XML.nodes('/Orders/Order') as O(X)
    CROSS APPLY O.X.nodes('Lines/Line') as I(X)

but now we need to update records that already exists checking Lines.Line.LineID

Since do not want to use MERGE, we thought it was easy to solve adding the usual

    IF NOT EXISTS (SELECT 1 FROM Sales where LineID=@LineID)
        INSERT INTO Sales () SELECT ....
    ELSE
        UPDATE Sales SET ItemID=..., Store=..., ... WHERE LineID=@LineID

but the value of @LineID in inside the XML and has to be calculated while parsing the XML

And discovered have no idea how to get it

Can suggest how to get the value of LineID while parsing the XML?

Thanks


texts: How to add a server list into Registered Servers on Microsoft SQL Server Management Studio (SSMS)

texts:
I have a list of sqlserver on on table on tbl_servers which have 500 server.
example:
Select IP_instance, DBname, username, password from tbl_servers
which will return the all sql server and their sa credential.
--> 10.10.1.30, myDatabase, sa, password
I would like to add all of this into the Registered server, can this done by SQL scripting? or any faster way to add?


texts: PostgreSQL: From vague "DB is slow" to concrete actions

texts:
I know how to debug a particular slow query with explain analyze (PostgreSQL).

If I received vague "DB is slow" feelings, I worked with this answer: https://stackoverflow.com/a/12818168/633961

In my case there are no sequence scans. I guess a combined index is missing.

Is there a tool which can help me to find missing combined indexes?

Other strategies which help to solve the goal are welcome, too.

Update

According to Julien Rouhaud this is fixed in PostgreSQL 10: https://groups.google.com/d/msg/powa-users/UmIEWvgJ2-k/sS5lfRE4CAAJ


texts:
Could you please help me tune this query. It takes over half an hour to run! I want to identify repeat bookings (can only be identified using an annoying varchar field - email)

UPDATE #Repeats --a temp table
SET    Repeats = (SELECT Count(col1)
                  FROM   Booking b1 -- a massive table -- 8 million rows
                  WHERE  email IN --a varchar(100) field
                         (SELECT Email
                          FROM   Booking b2
                          WHERE  DateDiff(day, b2.BookedDate, b1.BookedDate) > 1)
                         AND BookingStatusId != 7
                         AND #Repeats.BookedMonth = MOnth(BookedDate)
                         AND #Repeats.BookedYear = Year(BookedDate)
                         AND #Repeats.Code COLLATE Latin1_General_CI_AS = CONVERT(VARCHAR, b1.Code)
                         AND b1.BookedById = 5)

The table has a columnstore index that the plan is picking up.. Even then it is slow. If I drop the columnstore index, then the plan uses the nonclustered index on email. Thus there are two indexes on booking table. 1. columnstore index 2. nonclustered index on email.

I'm thinking along the lines of joins (instead of 'where email in') or using Window functions. Since I am not very well versed in these areas, I am a bit lost.


texts: Remote MySQL host - is connecting to remote port 3306 vulnerable?

texts:
Hello experienced colleagues,

I have a web application built on a MySQL database. I need to run another copy in a remote location, and refresh the data weekly. The size of the refresh is about 500k records, and is not the entire database, which is >15 million records.

My idea was to get the data running on a local Test Server, then connect to the remote Production Server's port 3306, and copy the data. the 'copy' part is not  problem, we have a python script, it is fast allright.

However, I have been warned that connecting to port 3306 is not safe, the data is not encrypted, etc etc.

I am in a desperate search for a solution. Here is what I have come up with so far:

Connection to MySQL client's port 3306 with SSL -- is it an off-the-shelf solution?
Connection over SSH tunnel -- I'm worrying about the speed of the connection.
Make the remote machine a VPN client of our intranet, and connect to the intranet IP's port 3306  (there are problems with DNS)
Write a sort of a 'back door', a MySQL client that would receive the data over SSL and update the database

As you see, the ideas are sorted in the order of ease-of-implementation, and I would really like one of the first two to work.


texts: Table doesn't exists issue with Oracle stored procedure

texts:
I am new to Oracle and in process of converting SQL Server stored procedures into Oracle stored procedures.
I was able to transform the SQL statements and have tested them individually and they are working fine as expected.

However facing some challenges when trying to execute them in Oracle stored procedures.

For example I have a stored procedure TEST_PROC wherein I am creating a TEST_TABLE_2 using existing table TEST_TABLE_1

BEGIN
EXECUTE IMMEDIATE
'CREATE TABLE TEST_TABLE_2 as
 SELECT
 COL1,
 COL2,
 COL3
 FROM TEST_TABLE_1';
END;

and then inserting data to some other table with the help of table TEST_TABLE_2

INSERT INTO TEST_TABLE_MAIN
(
COL1,
COL2,
COL3
)
WITH CTE_TEST_TABLE_DATE AS (
SELECT
a.COL1,
a.COL2,
a.COL3
FROM TEST_TABLE_2 a LEFT JOIN
 (SELECT GROUP_ID FROM TEST_REF_TABLE) b
 on a.COL1= b.GROUP_ID
)
SELECT * FOM CTE_TEST_TABLE_DATE;

As you can see that TEST_TABLE_2 is getting created on the fly in this procedure
and will be getting dropped in next subsequent procedure.

when I am running this TEST_PROC I am facing below issue which is probably due to that table doesn't exists.

exec TEST_PROC;

  PLS-00905 object SYSTEM.TEST_PROC is invalid


texts: SQL Server RandomNumbers Stored Procedure Error

texts:
I tried to create a stored procedure for generating random numbers but it doesn't work; the random numbers are identical in every record.

CREATE PROCEDURE Update_Driver_Position
@TempName VARCHAR(8000)
AS
DECLARE @maxvalueX INT
DECLARE @minvalueX INT
DECLARE @maxvalueY INT
DECLARE @minvalueY INT

DECLARE @X INT
DECLARE @Y INT

SET @maxvalueX = 3357
SET @minvalueX = 273
SET @maxvalueY = -320
SET @minvalueY = -3096

SET @X = ROUND(RAND(CHECKSUM(NEWID())) * (@maxvalueX - @minvalueX),0) + @minvalueX
SET @Y = ROUND(RAND(CHECKSUM(NEWID())) * (@maxvalueY - @minvalueY),0) + @minvalueY

UPDATE driver_tbl SET pos_x=@x , pos_y=@y

EXEC ('
Select *
Into '+@TempName+'
From  dbo.Driver_Tbl
')

I also couldn't EXEC the stored procedure more than once. Here is that call;

 EXEC dbo.Update_Driver_Position '##Temp'
 SELECT * FROM ##Temp

Any ideas ?


texts: mongodb after restoring replica-set from backup the primary is not master

texts:
after recovering replica-set from a snapshot (for testing)
when I run commands on the primary I got error

not master and slaveOk=false

does primary not equal to master?
I got this output for the command rs.isMaster() on the primary.

shard2-replset:PRIMARY> rs.isMaster()
{
    "hosts" : [
        "mongo-stage-rs-01-a.prod:27018",
        "mongo-stage-rs-01-b.prod:27018"
    ],
    "setName" : "shard2-replset",
    "setVersion" : 17,
    "ismaster" : false,
    "secondary" : true,
    "primary" : "mongo-stage-rs-01-b.prod:27018",
    "me" : "mongo-stage-rs-01-b.prod:27018",
    "electionId" : ObjectId("7fffffff0000000000000028"),
    "maxBsonObjectSize" : 16777216,
    "maxMessageSizeBytes" : 48000000,
    "maxWriteBatchSize" : 1000,
    "localTime" : ISODate("2019-05-15T17:09:13.275Z"),
    "maxWireVersion" : 4,
    "minWireVersion" : 0,
    "ok" : 1
}

is it make sence that the "primary" and "me" is the same, but yet "secondary" : true and "ismaster" : false,

    "ismaster" : false,
    "secondary" : true,
    "primary" : "mongo-stage-rs-01-b.prod:27018",
    "me" : "mongo-stage-rs-01-b.prod:27018",

mongodb version 3.2


texts: Order results from query to have a certain result at the top

texts:
I'm not sure there is even a way to do this, but thought I would ask anyway.

I have the following example output from a query:

Name     Country   Number
--------------------------
Harry    Germany   2
Harry    UK        5
Harry    Portugal  6
Zoe      Spain     10
Zoe      UK        5

For the task in hand, we use the data for UK most often, and would therefore like this at the top of the results set. The data is grouped by name, and then split in to country and number, and it needs to be ordered by Name, and then Country (but with UK at the top of each grouping). The desired output would be:

Name     Country   Number
--------------------------
Harry    UK        5
Harry    Germany   2
Harry    Portugal  6
Zoe      UK        5
Zoe      Spain     10

As you can see, UK is at the top of each grouping.

I would appreciate any advice, even if this cannot be done.

Thanks in advance


texts: Need to filter dimension where two attributes are the same

texts:
I've obfuscated the scenario but the general idea still applies, so if the schema seems retarded, it probably is in this example...

Let's say I have a Student dimension, and in it are two attributes - Major and Minor (subjects).

I want to be able to create a Named Set in SSAS that gives me Students who have somehow been recorded as having the same subject for Major and Minor...

Dimension looks a little like this:

Student
  - Major
  - Minor

The underlying table looks something like this:

StudentID  |  MajorKey  | MinorKey
1             50          51
2             45          101
3             58          56
4             45          45

So, in effect, I want an MDX expression like this:

FILTER (
  [Student].allmembers,
  [Student].[Major] = [Student].[Minor]
)

... but I can't quite figure out the syntax. Any ideas?

UPDATE:

If I use this syntax...

FILTER (
  [Student].allmembers,
  [Student].[Major].Properties("Key") = [Student].[Minor].Properties("Key")
)

Then it slows down the ENTIRE cube for some reason. Every Measure (natural or calculated) takes several minutes as opposed to 1-2 seconds pre-cache.


texts: WSL2 Docker Oracle volume mapping: how to switch from Windows to Linux file system

texts:
I am running Oracle (V19, V18, and V12) instances under WSL2 (Windows 10 version 2004). For persistence, my Oracle data files are mapped to a folder on my Windows host. I can run the containers from Docker for Windows or from within WSL2 (Ubuntu 20.04), all the while mapping the data volume to the same folder on the Windows host.
The reason for this setup is so that we can quickly and easily connect different instances of our database to an Oracle container running either under Linux or under Windows by just switching around the volume mappings.
However, doing this through WSL2 incurs performance penalties and is not recommended (see, e.g. WSL best practices). Windows displays a warning as well:

So I would like to transfer my database volume from the Windows host to the WSL2 file system.
I can create a brand new database in a volume on the Linux host and connect to that from both Docker under Ubuntu and Docker for Windows. But raw copying of the data files for an existing database from Windows to Linux (not too surprisingly) results in a failure of Oracle to open the database (control file appears corrupt):
database_1  | #####################################
database_1  | ########### E R R O R ###############
database_1  | DATABASE SETUP WAS NOT SUCCESSFUL!
database_1  | Please check output for further info!
database_1  | ########### E R R O R ###############
database_1  | #####################################
database_1  | The following output is now a tail of the alert.log:
database_1  | Additional information: 3
database_1  | ORA-221 signalled during: ALTER DATABASE   MOUNT...
database_1  | 2020-11-05T13:50:41.026149+00:00
database_1  | Errors in file /opt/oracle/diag/rdbms/orcl19cdb/ORCL19CDB/trace/ORCL19CDB_mz00_106.trc:
database_1  | ORA-00202: control file: '/opt/oracle/oradata/ORCL19CDB/control01.ctl'
database_1  | 2020-11-05T13:50:41.104385+00:00
database_1  | Errors in file /opt/oracle/diag/rdbms/orcl19cdb/ORCL19CDB/trace/ORCL19CDB_mz00_106.trc:
database_1  | ORA-01103: database name 'ORCL19CD�' in control file is not 'ORCL19CD'
database_1  | ORA-00202: control file: '/opt/oracle/oradata/ORCL19CDB/control01.ctl'
database_1  | Checker run found 1 new persistent data failures

Is there a simple automatic way to transfer the files from the Windows host to the Linux host? Or is there a better way altogether (e.g. creating a new CDB under Linux and copying the PDB from Windows into it)?


texts: How to determine consumption of data for archiving in sql server?

texts:
I was told that it could be possible to analyze the consumption of a given row. I already know I can use sys.dm_db_index_usage_stats to see the reads / writes on a given index (thus the table) but I need to know for the row level.

For example: The invoice table has 5 million rows, spread over 7 years. If I can determine that no rows are getting read for the first 5 years, this data will be archived. Now, if i have 100 tables that contain 7 years data, maybe archiving the first 5 years is good enough for the invoices but not for the other 99 tables.

Any way I can determine the data consumption / usage of rows for specific archiving?


texts: Exclude .LOG file while restoring a database

texts:
I have a database backup file (MyDatabase.bak). When restoring the database through SSMS it gives me "Not enough space on disk" message. I looked for an option to exclude .LOG file from restore but could not find one.

I even tried to remove the "MOVE .LOG" from restore query but got an error message "could not find .LOG file use WITH MOVE option".

Is there an option to exclude the log file while restoring the database?

Thanks


texts: Clone a database from SQL Server 2019 to 2017

texts:
I try to clone a database from an Sql Server on my local machine v15 (2019) to a server machine v14(2017). I did a .bak file and when I tried to import on the server side it says:

System.Data.SqlClient.SqlError: The database was backed up on a server
running version 15.00.4083. That version is incompatible with this
server, which is running version 14.00.2037. Either restore the
database on a server that supports the backup, or use a backup that is
compatible with this server. (Microsoft.SqlServer.SmoExtended)

Is there a "compatibility" backup mode on the 14-15 Sql Server versions?
local (on docker, Linux):

Microsoft SQL Server 2019 (RTM-CU8-GDR) (KB4583459) - 15.0.4083.2
(X64)   Nov  2 2020 18:35:09   Copyright (C) 2019 Microsoft
Corporation  Developer Edition (64-bit) on Linux (Ubuntu 18.04.5 LTS)

distant (on Windows Server)

Microsoft SQL Server 2017 (RTM-GDR) (KB4583456) - 14.0.2037.2 (X64)
Nov  2 2020 19:19:59   Copyright (C) 2017 Microsoft Corporation
Standard Edition (64-bit) on Windows Server 2019 Standard 10.0
(Build 17763: ) (Hypervisor)


texts: ORA-28545 (Net8) error when connecting Oracle 12c on Centos 7 to SQL Server 2014

texts:
I have installed Oracle 12c on Centos 7 and I wanted to connect SQL Server 2014 using freeTDS. So, I installed the following:

FreeTds.
unixODBC.

Next, I configured ODBC on the Linux server so that, when I run isql dsn username password, I can connect to the MSSQL successfully and tnsping also succeed but when I run:

select * from ItemTbl@MSSQL;

I get this error:

   ERROR at line 1:
    ORA-28545: error diagnosed by Net8 when connecting to an agent
    Unable to retrieve text of NETWORK/NCR message 65535
    ORA-02063: preceding 2 lines from MSSQL

freetds.conf:

[SQLSERVERADDRESS]

        host = 192.168.1.102 # MsSQL Server IP Address
        port = 1433 # MsSQL Server Port
        tds version = 8.0 # Tds version for SQL Server 2008

odbc.ini:

[SQLDSN]
Description = SQLDSN CONNECTION
Driver = /usr/local/freetds/lib/libtdsodbc.so
Servername = SQLSERVERADDRESS
Database = Cafe11
[ODBC Data Sources]
SQLDSN=FreeTDS

initSQLDSN.ora:

HS_FDS_CONNECT_INFO = SQLDSN
HS_FDS_TRACE_LEVEL = 0
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P9
set ODBCINI=/etc/odbc.ini

listener.ora:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle.gorannet.net)(PORT = 1521))
    )
  )
SID_LIST_LISTENER =
                (SID_DESC =
                        (SID_NAME=SQLDSN)
                        (ORACLE_HOME=$ORACLE_HOME)
                        (PROGRAM=/u01/app/oracle/product/12.1.0/db_1/bin/dg4odbc)

tnsnames.ora:

    GORANNET =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.140)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = gorannet)
        )
      )
    SQLDSN =
    (DESCRIPTION =
            (ADDRESS =
                    (PROTOCOL=TCP)
                    (HOST=192.168.10.140)
                    (PORT = 1521)
            )
            (CONNECT_DATA =
                    (SID = SQLDSN)
            )
            (HS = OK)
    )    (ENVS= LD_LIBRARY_PATH=/usr/lib64:/usr/local/freetds/lib:$ORACLE_HOME/lib)
)
)

tnsping result:

[oracle@oracle ~]$ tnsping SQLDSN
TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 20-JUN-2015 23:52:01
Copyright (c) 1997, 2013, Oracle.  All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.1.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL=TCP) (HOST=192.168.10.140) (PORT = 1521)) (CONNECT_DATA = (SID = SQLDSN)) (HS = OK))
OK (0 msec)

isql result:

[root@oracle ~]# isql SQLDSN cf11 cf11
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select count(*) from ItemTbl;
+------------+
|            |
+------------+
| 136        |
+------------+
SQLRowCount returns 1
1 rows fetched

database link:

CREATE PUBLIC DATABASE LINK "MSSQL"
   CONNECT TO "cf11" IDENTIFIED BY VALUES ':1'
   USING 'SQLDSN';


texts: Sql servr 2014 instant to sql server 2008 r2 instance linked servers query problem

texts: I'm getting the following error  on some of the linked server,when I try to query a table using a link server. The link server is created on SQL2014 instance to connect to an SQL2008r2 instance.


texts: How to find out all the permissions that belong to a specific server/ database role in SQL Server?

texts:
I can separately query all the server or database level permissions, but I want to know other thing. What server role do the given permissions exactly belong to? For example I want to see all the permissions belonging to securityadmin server role, hence I'll be able to understand what privileges the role has. I want the same for database roles. Can I find this info in SQL Server system tables?

Thanks


texts:
A colleague was having trouble enabling the Service Broker for a particular database.  SQL Server kept on throwing an error stating that the Service Broker was already running. When checking the database, the option for Service Broker was disabled.

It turns out that the database was created using a backup of another database and the logical names had not been changed. The original database did indeed have the Service Broker enabled.

So in summary we have:

DB1 with logical names foo and foo_log with Service Broker enabled
DB2 with logical names foo and foo_log with Service Broker disabled

We were unable to start Service Broker on DB2 as SQL Server said it was already running. The databases were pointing to different .mdf & .ldf files.

Why does the Service Broker use the logical names and not the database names? I always thought that the scope of logical name was restricted to a single database?

EDIT

This is the SQL Statement that was used to enable Service Broker

ALTER DATABASE DB2 SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE

And the error is:

Msg 9772, Level 16, State 1, Line 1
The Service Broker in database "DB2" cannot be enabled because there is already an enabled Service Broker with the same ID.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.


texts: Speed up an aggregate query on an 11 million row table

texts:
I have a query that I want to speed up:

SELECT
  sum(case when FlagDTD = 1 then Success else 0 end)   as SuccessDTD
, sum(case when FlagDTD = 1 then [Error] else 0 end)   as ErrorDTD
, round(sum(case when FlagDTD = 1 then Success else 0 end) * 100.0 / sum(FlagDTD),2)
    as RateDTD
, sum(case when FlagYTD = 1 then Success else 0 end)   as SuccessYTD
, sum(case when FlagYTD = 1 then [Error] else 0 end)   as ErrorYTD
, round(sum(case when FlagYTD = 1 then Success else 0 end) * 100.0 / sum(FlagYTD),2)
    as RateYTD
FROM
(
    SELECT
      CASE WHEN Message = 'OK'  then 1 else 0 end as Success
    , CASE WHEN Message <> 'OK'  then 1 else 0 end as [Error]
    , CASE WHEN DateCreated >
      dateadd(HOUR, datediff(hh,GetUTCDate(), GetDate())*-1,  DATEADD(yy,
        DATEDIFF(yy,0,getdate()), 0)) then 1 else 0 end as FlagYTD
    , CASE WHEN DateCreated >
      dateadd(HOUR, datediff(hh,GetUTCDate(), GetDate())*-1 ,
        convert(varchar(10), getdate(), 101)) then 1 else 0 end as FlagDTD
    FROM
      [Channels4].[dbo].[NotificationResult]
) Cnts

I thought maybe I could create a view or indexed view based on the subquery. However, upon testing I can't create an indexed view because "the view uses an implicit conversion from string to datetime or smalldatetime".

I tried using a traditional view, but that gave no performance enhancement at all. My next thought would be to maybe rewrite the entire query. What are everyone's thoughts?

Plan:

https://www.brentozar.com/pastetheplan/?id=rJqGY7iZW

Table Structure:

CREATE TABLE [dbo].[NotificationResult]
(
    [IdNotificationResult] [bigint] IDENTITY(1,1) NOT NULL,
    [ApplicationGuid] [nvarchar](48) NOT NULL,
    [MessageGuid] [nvarchar](48) NOT NULL,
    [IdNotificationResultTypeStatus] [int] NOT NULL,
    [MessageStatusCode] [int] NULL,
    [Message] [varchar](max) NULL,
    [ExceptionStatusCode] [int] NULL,
    [ExceptionMessage] [varchar](max) NULL,
    [Subject] [varchar](max) NULL,
    [From] [varchar](max) NULL,
    [Timestamp] [datetime] NOT NULL,
    [IdCreatedBy] [bigint] NOT NULL,
    [IdLastUpdatedBy] [bigint] NOT NULL,
    [DateCreated] [datetime] NOT NULL,
    [DateLastUpdated] [datetime] NOT NULL,
  CONSTRAINT [PK_NotificationResult] PRIMARY KEY CLUSTERED
  (
    [IdNotificationResult]
  )
);

CREATE NONCLUSTERED INDEX [IX_NotificationResult_DateMessage]
  ON [dbo].[NotificationResult] ( [DateCreated] ASC ) INCLUDE ( [Message]);

Doing a quick count for YTD, and DTD, I get this number for both: 11739267. The number of rows that are 'OK': 11782564.


texts: Migrate SELECT query from oracle to postgresql

texts:
I want to migrate an oracle query to postgresql :

here is the oracle version :

SELECT
replace(replace(replace(
cf.NO_FACTURE||';'||
cf.MODULE||';'||
replace(replace(replace(cf.REF_FACTURE_SYBEL,'&',' '),'''',''''''),';','')||';'||
nvl(replace(to_char(cf.SOLDE_VERSE),',','.'),0)||';'||
cf.DATE_PAIEMENT_FACTURE||';'||
nvl(replace(to_char(cf.MONTANT_ACOMPTE_VERSE),',','.'),0)
, CHR(10),''),CHR(13),''),'*',' ')
        FROM
            (SELECT distinct td_inf.NO_FACTURE
             FROM tac_dossier td_inf,
                  com_agent ca,
                  com_habilitation ch
             WHERE td_inf.etat_dossier = 'id.15.ClosFacturation'
             AND   MONTHS_BETWEEN(sysdate,td_inf.date_chgt_etat) > $nb_mois_clos
             AND   td_inf.cod_ag_resp = ca.cod_ag(+)
             AND   ch.cod_ag = ca.cod_ag
             AND   ch.cod_ent in (select cod_ent from com_ent_ft where pole = td_inf.cod_ent or cod_ent = td_inf.cod_ent)) td,
             COM_FACTURE cf
        WHERE cf.NO_FACTURE = td.NO_FACTURE;

and here is my postgresql version of the query :

SELECT
replace(replace(replace(
cf.NO_FACTURE||';'||
cf.MODULE||';'||
replace(replace(replace(cf.REF_FACTURE_SYBEL,'&',' '),'''',''''''),';','')||';'||
COALESCE(replace(to_char(cf.SOLDE_VERSE),',','.'),'0')||';'||
cf.DATE_PAIEMENT_FACTURE||';'||
COALESCE(replace(to_char(cf.MONTANT_ACOMPTE_VERSE),',','.'),'0')
, CHR(10),''),CHR(13),''),'*',' ')
        FROM
            (SELECT distinct td_inf.NO_FACTURE
             FROM
                  tac_dossier td_inf
                  LEFT OUTER JOIN com_agent ca ON td_inf.cod_ag_resp = ca.cod_ag
                  INNER JOIN com_ent_ft cef ON (cef.pole = td_inf.cod_ent or cef.cod_ent = td_inf.cod_ent)
                  INNER JOIN com_habilitation ch ON (ch.cod_ag = ca.cod_ag and ch.cod_ent = cef.cod_ent)
             WHERE td_inf.etat_dossier = 'id.15.ClosFacturation'
             AND   EXTRACT(month from age(NOW(),td_inf.date_chgt_etat)) > 2
             ) td,
             COM_FACTURE cf
        WHERE cf.NO_FACTURE = td.NO_FACTURE;

The query is working there is no problem in the syntax But the results of the two queries are different.

Any note on this ?

Thank you.


texts: set postgres instance as readonly with no master

texts:
I have a pg 10 instance that I want to behave as a read only/replica/standby instance.

There is no master, only that read only database and I want the query select pg_is_in_recovery(); to return true to satisfy a monitoring check.

I have tried add a recovery.conf file in the data dir with standby_mode = 'on' as content then SELECT pg_reload_conf(); but I still get false to select pg_is_in_recovery();

office-accounting=# SELECT pg_read_file('recovery.conf');
    pg_read_file
---------------------
 standby_mode = 'on'+

(1 row)

office-accounting=# select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 f
(1 row)


texts: AG listener issues with ApplicationIntent=ReadOnly

texts:
Need help in resolving a specific issue connect AG listener with ApplicationIntent=ReadOnly. We have 3-node cluster with AlwaysOn AG and synchronous replica. All 3-nodes are in same data center (Rackspace Dallas). I have verified that:

We are connecting to Listener Name.
The Read-Only routing list (READ_ONLY_ROUTING_LIST) is defined.
The Routing URL (READ_ONLY_ROUTING_URL) of each instance has a proper FQDN and port combination.
ApplicationIntent is specified in the connection string.
The Sync_State is SYNCHRONIZED or SYNCHORNIZING for the secondary replica.
The secondary replicas are set to allow connections.
The initial catalogue is provided in connection string.

The AG listener with read-only intent connections works fine if I RDP into the servers and connect from any one node to the AG listener via SSMS. We host our servers in Rackspace. However, if I use the outside network (from my office location) to connect to AG listener (using public facing IP), the regular connection works fine but not the readonly intent ones.

Ruled out drivers and SSMS version issues as I checked this on SQL server 2008 R2, 2012 and 2016 with respective SSMS versions. .NET run time on all machine is 4.0.3+ so that is not an issue.

All 3-nodes are connected via a private switch for replication (192.168.5.x subnet), and all 3-nodes are accessible via public facing IP (192.168.99.x subnet through firewall) as well. Can access all of the nodes via respective IPs from our office location (outside Rackspace). The AG listener is configured to respond on (192.168.5.x and (192.168.99.x) for redundancy. Can ping one node to another via HostName, IP and FQDN as well.

The only thing that is missing is a DNS entry for the AG listener, which I am going to create to rule out the issue. However, I am connecting using IP address from our office location to the data center.


texts: How to find when the user last logged in Mysql

texts: I need to find all the users when they last logged in in mysql. My goal is to cleanup users that are not used for months. Please help


texts: Setting up Power BI to schedule publishing from PostgreSQL

texts:
My company suddenly switched to Power BI as a corporate visualization tool but I have a large PostgreSQL database/infrastructure in place.

I was able to download PostgreSQL drivers and connect to my data, but I am not seeing a way to schedule refreshes/publishing. If I go to the Power BI 'cloud' portal I see that the cloud can connect to Azure SQL Database, Azure SQL Data Warehouse, SQL Server Analysis Services, and Spark.

Can I configure the Power BI cloud to refresh data from my local PostgreSQL database? Or, if I create a Linux VM on Azure and install PostgreSQL, will I be able to schedule refreshes there? I just want to add the dashboards which update every 30 minutes so an end user just needs to refresh their browser. Or do I need to pipe data from PostgreSQL to a Microsoft DB?

I would much rather be able to use PostgreSQL if possible, and I would like to have Linux as well as the server OS because I am using Python Airflow to schedule tasks (don't think it works with Windows).


texts:
I'm being unable to perform a backup in SQL Server 2005 installed on Windows 7 local machine. I was initially able to do so but as of now, every time I try I get the following message:

  Backup failed for Server 'EBENEZERAKAGLO\SQLEXPRESS'.
  (Microsoft.SqlServer.Smo)

  System.Data.SqlClient.SqlError: Cannot open backup device 'C:\Program
  Files (x86)\Microsoft SQL Server\MSSQL.3\MSSQL\Backup\Naass.bak'.
  Operating system error 5(Access is denied.). (Microsoft.SqlServer.Smo)


texts: Storage does not failover in Windows cluster

texts:
I created a lab setup for a 2 node cluster using Windows 2012 R2. I first configured the quorum and performed a failover between both the nodes, the quorum would also map to the respective node. But whenever I added the data and the log drives and tried to do a failover, they are not getting mapped to the respective nodes. What am I missing here? These are the steps I followed

1) Create New Virtual SCSI drive from the Server manager
2) Adding both the Nodes as targets with their IP addresses
3) Initialized SCSI target from both the nodes
4) Open Disk management and format the drives to MBR->NTFS-64k allocation
5) In the failover cluster manager -> add the disks to the cluster

Is there something else which I need to do? Please refer to the screenshots below

SCSI creation from Domain controller :

Quorum as part of cluster resources

All disks on SQL1 before failover

Disks on SQL1 after failover to SQL2. Notice that the Data and log drives are still on SQL1. I did not create any Cluster Shared Volumes here


texts: Can I decrypt TDE transaction log files for use with 3rd party software?

texts: Is there a way to decrypt TDE log files on disk for use with 3rd party software? I have the original key and certificates. I am trying to use the transaction files with a 3rd party tool in order to recover some lost data.


texts: How to copy data into same table based on existing data in sql?

texts:
It is possible to copy a set of rows based on Date column and insert in same table with different date?

For example : I have 5 rows with Date column value '201839'
I need to copy those 5 rows in '201840' as well as '201841'

Or can we create a view that have a copy of 201839 rows as 201840 in sql.


texts: SQL job using SSIS package takes a long time to run especially in the evenings

texts: I have a SQL job that runs an SSIS package and it basically pulls records from staging table and dumps it into the master table. It used to take around 8 hrs. to run but in these last 10 days or so the run duration has increased and I would like to know if there is a way I can find out where the job is spending more time and what it is waiting on either running a profiler trace or running a tsql script inside a job to capture anything useful. It runs every day except Sat at 11p est.


texts:
Here are two tables :
table1

id1(pk) objectid z
0        1       100
1        2       98

table2

id2(pk) n_sd prof alt_inf
0        1   5
1        1   15
2        2   8

objectid -> n_sd (1->n).

The aim of the trigger is on insert on table2 to do the maths alt_inf = z-prof for objectid = n_sd.

IF (TG_OP = 'INSERT') THEN
NEW.alti_inf = (select z-NEW.prof from table1 join table2
on table2.n_sd = table1.objectid
where new.gid = table2.gid )
RETURN NEW;
END IF ;

DROP TRIGGER IF EXISTS trg_calcul_altiinf ON table2 ;
CREATE TRIGGER trg_calcul_altiinf AFTER INSERT ON table2
FOR EACH ROW EXECUTE PROCEDURE calcul_altiInf();

I have no result nor error message.


texts: Problem with MySQL multiple join on same table with condition

texts:
Using MySQl 5.6 as my database, I'm trying to join a table twice, on another joined table, but I'm getting duplicate results, rather than the row combined.

SO, I have a checks table, and a check can be assigned to an area or areas, which are stored in the check_assigned_area table. Entries in this table can either be a location or a department. Locations being a parent of departments.

SO, im joining the checks_assigned_area table on to the checks table via the PK (check_id)

AND then im trying to join the areas table twice, once for locations and once for departments, depending on the area_level. 3 being a location and 4 being a department.

But instead of the query returning a single row with the location name and department name. Its returning 2 seperate rows, and it isnt populating the department name.

I've tried all manner of left/inner/right join combinations, distincts, group by's, but I cant get the data correct.

Can I even do it like this? My logic tells me yes, but practically I cant get it to work.

Any ideas if I can get it to output the data i need?

Heres an SQL fiddle of my data and query http://sqlfiddle.com/#!9/63d6ce/4

Checks table:

+----------+---------------+------------+------------+--------+----------+
| check_id | check_type_id |    due     | created_by | status | sign_off |
+----------+---------------+------------+------------+--------+----------+
|    19335 |            43 | 18/09/2017 |          3 |      1 |        1 |
|    19336 |             4 | 19/09/2017 |          3 |      1 |        1 |
|    19358 |            62 | 21/09/2017 |      47336 |      1 |        1 |
+----------+---------------+------------+------------+--------+----------+

Check assigned area table:

+------------------------+---------------+----------+
| check_assigned_area_id | check_area_id | check_id |
+------------------------+---------------+----------+
|                  18801 |             9 |    19335 |
|                  18802 |           816 |    19336 |
|                  18803 |           816 |    19336 |
|                  18804 |          8091 |    19358 |
|                  18835 |          8092 |    19358 |
+------------------------+---------------+----------+

Query:

SELECT `c`.*, `t`.`check_name`,`a`.`check_area_id`,`l`.`area_name` AS 'location_name', `d`.`area_name` AS 'dept_name'
FROM `checks` `c`
LEFT JOIN `check_assigned_areas` `a` ON `a`.`check_id` = `c`.`check_id`
LEFT JOIN `areas` `l` ON `l`.`area_id` = `a`.`check_area_id` AND `l`.`area_level` = '3'
LEFT JOIN `areas` `d` ON `d`.`area_id` = `a`.`check_area_id` AND `d`.`area_level` = '4'
LEFT JOIN `check_types` `t` ON `t`.`check_type_id` = `c`.`check_type_id`
WHERE `c`.`org_id` = '297'
AND `c`.`status` != '0'
AND `c`.`due` <= '2017-09-21'


texts: Create a DELETE statement that deletes Manufacturers with less than 10 sales

texts:
Orders:
Order number (Primary Key)
Date of order
Customer ID (Primary Key for Customers table)
Serial Number (Primary Key for Machines table)

Customers:
ID (Primary Key)
Name
Phone Number
Street (ie. 505 Ramapo Valley Road)
City (ie. Mahwah)
State
Zip

Machines:
Serial Number (Primary Key)
Model Number (Primary Key for Models table)

Model:
ID (Primary key)
Speed
RAM
HD
Price
Manufacturer ID (Primary key for Manufacturer table)

Manufacturer:
ID (Primary Key)
Name
Phone Number
Email Address

Above are tables with different attributes.
I have a lab due soon and one of the tasks is to create an SQLite statement to DELETE Manufacturers with less than ten sales. I have tried to write a sub query, but am not able to formulate a statement that works. The following is what I have so far:
(I know I am off by a lot)
DELETE FROM Manufacturer
HAVING COUNT(*) < 10
WHERE ID IN
(SELECT ManufacturerID FROM Model WHERE Manufacturer
    WHERE Manufacturer.ID = Model.ManufacturerID

SELECT ModelNo FROM Machines
    WHERE Machines.ModelNo = Model.ID

SELECT SerialNo FROM Orders
    ON Orders.SerialNo = Machines.SerialNo


texts: Is innodb_log_file_size not relevant if you are not using transactions in MySQL?

texts:
I am trying to understand the variables:

innodb_log_file_size
innodb_flush_log_at_trx_commit

As I understand the innodb_log_file_size is for undo and redo purposes for when cancelling a transaction and for when server crashes. Does this mean that I can kind of ignore this if I don't plan on writing queries of the form:

START TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 98;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 42;
COMMIT;

I also heard somewhere that

  Every INSERT, UPDATE, DELETE is a transaction if autocommit=1.

What does this mean?

If I do zero transactions, will I gain any performance by setting innodb_flush_log_at_trx_commit=0? Since I won't need to sync after every transaction commit. But again I heard on youtube that if autocommit=1, then every insert, update, and delete "is a transaction".

I am confused.


texts: Progress OpenEdge - how to remove lock from table?

texts:
I have some kind of bug leaving lock on table in application that is connecting to Progress OpenEdge Database. Because of the lock I'm not able to test what app is doing wrong there (since I can't test it more than once because of lock and lock seems to last for very long).

Is there a way to remove all locks from table?


texts: Telnet failed from App server to sql server DB server intermittently

texts:
Recently I noticed one interesting issue in our production server. We have an app server (IIS and asp .net framework) and a DB server (SQL Server 2008) installed.

During peak time when I try to telnet my DB server on 1433 it keeps getting failed but sometimes it gets connected. Both these servers are blade server and are in same chassis. No network firewall is in between all the two servers.

Also in application logs its shows error like

A network-related or instance-specific error occurred while establishing a connection to SQL Server.
The server was not found or was not accessible.
Verify that the instance name is correct and that SQL Server is configured to allow remote ...
Provider, error: 40 - Could not open a connection to SQL Server

I checked number of connections on my DB using the command

SELECT  COUNT(dbid) as TotalConnections
FROM sys.sysprocesses
WHERE
dbid > 0

it returns approximately 140-150 connections while we have multiple databases (~100) on it.

I will appreciate if anybody can help me in troubleshooting this issue.

We are making connection strings as <add name="DBNAME" connectionString="Data Source=10.X.X.X;Initial Catalog=DBNAME;User ID=sa;Password=test" providerName="System.Data.SqlClient" />

Also in web config we have <sessionState timeout="10" />


texts: Why shouldn't I increase SQL Server's timeout globally everywhere?

texts:
I have an application in which some reports time out before getting data. We started increasing timeout througout our code in different places, and as our database is growing bigger and bigger, we see that we're repeating/duplicating more code.

So we decided to increase database timeout for queries globally. But I have this feeling that this is wrong. Yet I'm not able to bring forward any reasons. Is it bad to increase timeout everywhere? Why shouldn't we do this?

We're using SQL Server and Entity Framework.


texts:
Apologies if this has been answered previously but I have been unable to get a specific model to work.

I have a database table that needs to store some very basic info for a reporting system. The information fed to me is Date - Pallets-in - Pallets-out - Pallet_Count - Man_Hours

The report when created needs to show Date - Pallets handled (Pallets-in + Pallets-out) - Man-Hours - Avg PLTS per hour - Overall Pallets in warehouse

EG yesterday report might shows 14-03-2015 PLT-Handled 400 (300 in 100 out) - Man-Hours 36 - Avg Plts p/Hour 11.11 - Overall Plts inW/House 7300 (day before there were 7100 so add 300 in and remove 100 out)

I currently just compose this report manually but I want to try and do it in a database so we have better historical reporting ability. Do i just have a table that stores every part or would it be better to calculate some parts in the report generation. (I am unsure how to mainly include the Total pallets in warehouse as it calculated from a starting figure then add plts in minus plts out.

Hope the above mmakes some sense

Regards

Paul


texts: See SQL Server Reporting Services Reports without logging in?

texts:
I have installed SQL Server 2015 with SSRS and I have some reports I need to run, but in order to access http://reportsserver/Reports I need to start Internet Explorer in administrator mode.

This is clearly suboptimal: any user would need to know the Administrator password.

How do I set up SSRS to not require auth to see reports, or how can I make users not need to run IE in Administrator mode?


texts: How to get SQL Server 2017 shared feature versions to match the instance version for a successful cumulative update?

texts:
I am trying to execute a cumulative update on SQL Server 2017. In the process I noticed that the shared feature Integration Services and SQL Client Connectivity SDK are the ones selected to update and does not include the instance itself.
I also notice that the patch level to update with is 14.0.3223.3 but the summary shows the patch level is 14.0.3335.7 which is the latest update.
Why is this happening when I chose an update CU16 but wants to update to CU21?
I just want to be able to update my instance but seems like the missed matched versions of the shared features to the instance is preventing this update from happening. Please help!


texts: To drop a table, do I have to drop all foreign key constraints or can I just NOCHECK them?

texts:
I know you cannot drop a table that has active foreign key constraints.

So I was wondering is it safe to:

ALTER TABLE myTable NOCHECK CONSTRAINT all
DROP TABLE myTable

Rather than dropping the constraints first, table dropping:

ALTER TABLE myTable DROP Constraint FK_MyTable_AnotherTable
ALTER TABLE myTable DROP Constraint FK_MyTable_YetAnotherTable
DROP TABLE myTable


texts:
Reading some books I found that clustered index not require additional disk space for storing index data, because leaf level of clustered index are data itself.

But, as clustered index is organized as B-tree structure, it require intermediate levels and root level. Where are stored those pages? are they additional index structure? How internal structure on physical level looks like (when speaking about clustered indexes)?


texts:
Is there an optimized way to RPUSH all the values of a set to a list without SPOP-ing them one by one (or SMEMBERS)? (I need to perform bulk RPUSH of huge set each T time to a list) ?

Thank you,


texts: MySQL: DELETE didn't shrink DB size, nor table size

texts:
In mydatabase, there is a table table1 with almost 8M row, after deleting around 7M rows from the table, the size of the DB and the table itself remains the same.
Table size:
+---------------------------------------------+-----------+
| Table                                       | Size (MB) |
+---------------------------------------------+-----------+
| Table1                                      |     96000 |

Delete query:
DELETE FROM table1 WHERE foo < 'boo';
Query OK, 7809000 rows affected (43.25 sec)

Size after deleting the data:
+---------------------------------------------+-----------+
| Table                                       | Size (MB) |
+---------------------------------------------+-----------+
| Table1                                      |     96000 |

Any idea why deleting the data from this table didn't decrease the database size?
Server version: 5.7.19-17-log Percona Server (GPL)


texts: MySql Error #1054 when adding new column to table using prepared statement

texts:
I'm trying to add a new column named lot to a table, but I'm getting error #1054 - Unknown column 'lot' in 'field list'. What could be the problem? Here's the query:

SET @lot = (SELECT lot_no_start FROM `systemconstants`);
SET @sql = CONCAT('ALTER TABLE ipp_sorted ADD `',lot,'` INT PRIMARY KEY AUTO_INCREMENT=',@lot);
PREPARE st FROM @sql;
EXECUTE st;


texts: What could cause 300+ connections from a simple query in MySQL?

texts:
I am retrieving results from a table in MySQL 5.5. When I run even a basic query on this table hundreds of connections are shown in the Server Status screen in MySQL Workbench. Even for a simple query like:

SELECT
  id
FROM
  db.table;

I get 200 - 300+ connections shown in the Server Status screen.

This is the only table I get this result with. If I query other tables in the same db no extra connections are created.

Any suggestions for what the cause might be or how to troubleshoot?


texts:
Is it possible to get EXPLAIN ANALYZE output (e.g. as a notice) in addition to the regular query output?

I am designing a postgresql client and I would like to add a feature that automatically turns all queries into EXPLAIN ANALYZE for the purpose of tracking query execution time, seq scan, etc. However, such feature would require that I am able to obtain the regular query output in addition to the EXPLAIN ANALYZE output.

How to get query output in addition to EXPLAIN ANALYZE output?

Note: I am aware of auto_explain. However, there appears to be no way to access logs from the client for the last executed statement.


texts: Mongodb - Will Bulk.find.remove() lock the collection

texts:
I'm planning to run Bulk.find.remove() on a very big collection. (1.2TB).

Will it block inserting or updating other records (which are not a part of the above find)?


texts: Prevent consecutive duplicate values without a trigger

texts:
Within a group, I'd like to prevent INSERTs of consecutive duplicate values, where "consecutive" is defined by a simple ORDER BY clause.
Imagine a set of experiments which is regularly sampling values from a sensor. We only want to insert a value if it is new for that experiment.
Note that older values are allowed to be duplicates. So this is allowed:
id    experiment    value
 1             A       10
 2             A       20
 3             A       10

but this is not:
id    experiment    value
 1             A       10
 2             A       10

I know how to find the previous value per experiment:
  SELECT
  *,
  lag(sample_value) OVER experiment_and_id
  FROM new_samples
  WINDOW experiment_and_id AS (
    PARTITION BY experiment
    ORDER BY id
  );

From the docs I know that CHECK constraints are not allowed to use other rows in their checking:

PostgreSQL does not support CHECK constraints that reference table data other than the new or updated row being checked. While a CHECK constraint that violates this rule may appear to work in simple tests, it cannot guarantee that the database will not reach a state in which the constraint condition is false (due to subsequent changes of the other row(s) involved). This would cause a database dump and reload to fail. The reload could fail even when the complete database state is consistent with the constraint, due to rows not being loaded in an order that will satisfy the constraint. If possible, use UNIQUE, EXCLUDE, or FOREIGN KEY constraints to express cross-row and cross-table restrictions.
If what you desire is a one-time check against other rows at row insertion, rather than a continuously-maintained consistency guarantee, a custom trigger can be used to implement that. (This approach avoids the dump/reload problem because pg_dump does not reinstall triggers until after reloading data, so that the check will not be enforced during a dump/reload.)

The EXCLUDE constraint looks promising, but is primarily for cases where the test is not equality. And I'm not sure if I can include window functions in there.
So I'm left with a custom trigger but this seems like a bit of a hack for what seems like a fairly common use case.
Can anyone improve on using a trigger?
Ideally, I'd like to be able to just say:
INSERT ....
ON CONFLICT DO NOTHING

and have Postgres deal with the rest!

Minimum working example
BEGIN;
  CREATE TABLE new_samples (
    id INT GENERATED ALWAYS AS IDENTITY,
    experiment VARCHAR,
    sample_value INT
  );

  INSERT INTO new_samples(experiment, sample_value)
  VALUES
  ('A', 1),
  -- This is fine because they are for different groups
  ('B', 1),
  -- This is fine because the value has changed
  ('A', 2),
  -- This is fine because it's different to the previous value in
  -- experiment A.
  ('A', 1),
  -- Two is not allowed here because it's the same as the value
  -- before it, within this experiment.
  ('A', 1);

  SELECT
  *,
  lag(sample_value) OVER experiment_and_id
  FROM new_samples
  WINDOW experiment_and_id AS (
    PARTITION BY experiment
    ORDER BY id
  );
ROLLBACK;