texts: Which index shall I create in Postgres to speedup join of two big (several million rows) tables connected one-to-many?

texts:
I have 2 tables, first one contains dated items (something like log records), second one - multiple attributes of those items.

First table:

CREATE TABLE items
(
  date date NOT NULL,
  point_id integer NOT NULL,
  /* several more columns */
);

Second table:

CREATE TABLE attributes
(
  attribute_id integer,
  value double precision NOT NULL,
  point_id integer,
  CONSTRAINT fk_point_id FOREIGN KEY (point_id)
      REFERENCES items (point_id) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE CASCADE
)

So for single record in items there are 1..N records in attributes. For now able items has about 35M of rows, for each row there are 2 rows in attributes table - one with attribute_id = 0 and second with attribute_id = 1

I have separate btree indexes on all listed fields. And what is unclear to me is why following query leads to Seq Scan of attributes table:

EXPLAIN SELECT s.date, a1.value AS value
    FROM items s
    INNER JOIN attributes a1
        ON s.point_id = a1.point_id
    WHERE a1.attribute_id = 0
    AND s.date >= '2016-12-01'::DATE

Here is explain result:

Hash Join  (cost=14457.15..1529906.57 rows=310075 width=12)
  Hash Cond: (a1.point_id = s.point_id)
  ->  Seq Scan on attributes a1  (cost=0.00..1376027.10 rows=36352418 width=12)
        Filter: (attribute_id = 0)
  ->  Hash  (cost=10569.55..10569.55 rows=311008 width=8)
        ->  Index Scan using items_date_idx on items s  (cost=0.56..10569.55 rows=311008 width=8)
              Index Cond: (date >= '2016-12-01'::date)

I see that Postgres uses index to filter items table and get only needed rows - there are about 300K of them. But then why it iterates over whole 70M attributes table to compose hash map? As I have indexes on 'point_id' column in both tables, why not to use them to perform merge join of just 300K?

I tried to enable/disable different planner options, but of course it get worse. I understand that planner does his job very well, there is just something wrong in my schema and I need to help planner somehow. I've tried to solve the issue with adding composite index like 'date,point_id' - at least it helped in Mongo (after filtering by date you have sorted list of point_id and may perform merge join), but Postgres ignored those indexes and used simple ones.

So which index shall I create (or how shall I redesign my schema) to get provided query work faster and prevent seq scan?

Not sure how can it help, but in items table each row has larger point_id than previous and same or larger date. Also, I do not need exact date/time - only date part.

In the query above if I remove date check condition I get the merge join as expected:

Merge Join  (cost=73.17..4229922.45 rows=36352418 width=12)
  Merge Cond: (s.point_id = a1.point_id)
  ->  Index Scan using items_point_id_idx on items s  (cost=0.44..1143957.50 rows=36461804 width=8)
  ->  Index Scan using attributes_point_id_idx on attributes a1  (cost=0.57..2540470.32 rows=36352418 width=12)
        Filter: (attribute_id = 0)

I use postgres 9.5

Result of explain(analyze,verbose): https://explain.depesz.com/s/P1D

In theory there could be custom amount of attributes, it is defined in runtime.

There is an index on attribute_id. I think it is not used because half of table has attribute_id = 0 and another half has attribute_id = 1.


texts: Which java JDK is compatible with oracle instant client 10g

texts:
I'm an intern stuck trying to retrieve data from Oracle 8i (a database that is as as I am)

The problem:

I need to write a java script that pulls data from Oracle 8i into a newer server.  The last client that is compatible with Oracle 8i is Oracle client 10g (10g instant client is still available for free on Oracle's website).

Documentation is scarce about 10g client compatibility. My server currently does not have java installed and my question is what version of java is compatible with 10g client?


texts: Databases missing after exporting server and rebuilding master DB

texts:
We had a server running Microsoft SQL Server 2014 databases on Hyper-V. I exported it (with C: and D: drives) and opened on other machine.
I could not start the MSSQLSERVER service because it said the master db needed to be rebuilt.
I've rebuilt the master db, but now the databases are empty, I cannot see databases except master, model, msdb, and tempdb.
I have all databases (mdf and ldf files) in this folder D:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA. How to restore them and not lose all functionality?


texts: Slave fail first due to duplicate primary key then corruption error

texts:
I have a mysql db running the slave very well for few months. Suddenly today I notice it stopped since 18Nov. I check the status it was showing me the primary key duplication error. SO i stop slave deleted the lines and tried to start the slave and when I check the slave status its showing me this error now. What should be next step ?

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.100.12
                  Master_User: repu1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000039
          Read_Master_Log_Pos: 671168158
               Relay_Log_File: mysqld-relay-bin.000092
                Relay_Log_Pos: 640109291
        Relay_Master_Log_File: mysql-bin.000038
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: dbn
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1594
                   Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 640109146
              Relay_Log_Space: 1744924599
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1594
               Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.


texts:
Hi, so recently when trying to fix another bug within my sql server 2008 R2 db I removed the BUILTIN\Administrators login from sysadmin. Now it will not let me change anything winthin the database. Also I do not have the SA account password to log in as that user to change/ add any permission.

Can anyone help me add the BUILTIN\Administrators login back into sysadmin or find a way to alter the database using my windows account?


texts: Reuse a complex non-deterministic expression in SELECT and WHERE clauses

texts:
I have a query like this:

-- This is inefficient, and may even be wrong if expression is nondeterministic
SELECT TOP 10 team_name,score_points=<big expression>
  FROM teams
 WHERE <big expression> >= 100
 ORDER BY 2 DESC

-- This gives error
SELECT TOP 10 team_name,score_points=<big expression>
  FROM teams
 WHERE score_points >= 100
 ORDER BY 2 DESC

What I want to do is clear: list the teams with the highest score_points. List all teams with at least 100 score_points, but no more than 10 teams.

If I repeat the expression, it will be very ugly and inefficient. Plus, it will give wrong results when my expression is not deterministic.
Using the name score_points in the WHERE clause gives an error.
Is there an efficient way to have the results of my expression both returned as a column in my query, and used as a filter?

Please don't tell me about computed columns, I know about them but I can't use them here.


texts: How to model an “is a” relationship in an ERD using Crow’s foot notation?

texts:
I have to create an entity-relationship diagram (ERD) for an assignment. I am doing this in a particular drawing tool with the Crow’s Foot notation. There is however one thing I cannot figure out:

How would you present an “is a” hierarchy in Crow’s Foot notation?

Currently, I am showing this by means of a one-to-one relationship between the Product superentity type and its subtypes (Books, Papers, etc.).

Is this the correct way to go or is it wrong? (I could not find any website talking about it).


texts: Best practice bring MS SQL database to remote sites

texts:
Currently we use terminalservers to enable remote users to work with a central LOB-application. The terminalserver is on the same LAN as the MS SQL-server.

Now my question is: what is the preferred way to replicate/mirror the database to the remote sites, so users don't need a terminalserver and can work in their local LAN.

These are my concerns:

SQL over internet is often too slow. It would however be my preferred solution because of simplicity (if internet doesn't work this will only temporary effect clients, no worry about shipping log files, etc)
Mirror will be removed in a future version of Microsoft SQL Server. Afaik, it leaves the mirror constantly in restore-mode so the LOB-application can't actually use, correct?
Always On availability groups look very complicated to me but they do support Active Secondary Replicas which sounds like the ability that every remote site can use it's local replica for reading and the central-db for writing. However, I wonder, does this work with all LOB-applications? And does this require a very fast and stable internet-connection? It must not fail with every short internet/VPN hick-up.

More info:

I'm hoping for a general solution that works with 'all' Microsoft SQL-based applications (SQL over internet does when internet is fast and queries are limited).
Data needs to be read/write on remote sites. It's okay if data can't be modified in case of a broken internet/VPN connection. I was hoping that mirroring could do this. That modifications are committed to the principal-db but all reads are directly read from the mirror (transparant to the application / without using 2 different db-servers). A bit like domain-controllers work. This isn't how mirroring works however.
A delay of minutes would be acceptable in most LOB-applications if there is no risk off sync conflicts. I can imagine that if you work with orders, timesheets, etc. it doesn't matter if your data is a bit delayed because you are accessing it from another country.
Assume the vendor doesn't provide a solution. I won't say they don't support it. I asked one and they didn't have plans for such a system. Their solution was to move all infrastructure (servers + desktops) to the 'cloud'. But it's just MS SQL, so I'm hoping for a better solutions. With terminal-server you can have a slow user experience with everything you do. It would be better if you just have a (small) delay in your LOB-application only, not when visiting a website or scrolling a PDF.

Any recommendations? Or is a central terminal-server the only way?

Thanks in advance.


texts: SQL Server Storage for multiple data files

texts:
Recently, our company wants to add 4TB on the drive size for SQL Server. We have two drives now which have different size and data files. In order to make sure our SQL Server has the best performance, where should I add those space:

G: Drive: 19TB (16 Data files)
H: Drive: 3TB (3 Data files)

Should I add the space on H drive and create more data files in it?
Any suggestions?


texts:
I have 2 birds per pair IE "Maleringnumber"and "FenaleRingNumber" in tblPair
If I open a Form for tblBird I want to see all fields in the table AS WELL as "PairedStatus" of the male and the female. The tables in the pic are in a query. How do I link the tblBird and the tblPair


texts: How to create INOUT assignment cast on Amazon RDS PostgreSQL? If not possible, then why?

texts:
I'm migrating existing application to Amazon RDS PostgreSQL.
The application has cross-database support and for compatibility reasons it includes the following SQL:

CREATE CAST (varchar AS numeric) WITH INOUT AS ASSIGNMENT

On plain PostgreSQL, this requires superuser. On Amazon RDS PostgreSQL this fails with

  ERROR: must be owner of type character varying or type numeric

Is there a way to create such a cast in Amazon RDS (perhaps via administrative web interface)?
If this is not possible, then why? Is this deliberate limitation (e.g. is the cast something dangerous) or just a missing feature?

Note: I guess that whether this is assignment or implicit cast is irrelevant, but included this information for completeness.


texts: ORA-1033: ORACLE initialization or shutdown in progress

texts:
I am stuck with a little Oracle problem. My PC was shut down improperly after my graphics card freaked out, which caused Oracle 11g Express to not properly close down. This means that I get the above error when I try to connect. My PC has been on for 3 hours, and I still get the error, so it is safe to assume that there's something wrong.

The usual way of remedying the problem is to use the following sequence of commands from the command line:

sqlplus /nolog
SQL> connect / as sysdba
SQL> shutdown abort
SQL> startup nomount
SQL> alter database mount;
SQL> alter database open;

However, when I give the shutdown command, I get the error

  ORA-12560: TNS:protocol adapter error

So now, I can't mount my db. ANy help would be appreciated.


texts: How to make postgresql SUM to be more accurate on large amount of floating point data?

texts:
I'm trying to SUM 8 million floating point (REAL) values with simple query like this:

SELECT SUM(metric) FROM metrics;

However, it returns very inaccurate result.
It should return 137,586.77, but it returns 137,303 (283.77 difference)

Is there a way to force a query to be more precise?


texts: Simple insert statements are taking so long

texts:
I have a scenario where one main stored procedure calls a nested procedure and that nested procedure contains simple insert statements for multiple tables and almost every insert is taking so long

I know there are lots of possibilities for instance Disk, CPU, Memory, indexes and triggers

How do I find Clustered and NON-Clustered indexes are not the reason of this slowness?

Insert Statement:

INSERT INTO PEDetail with(rowLock) (CID,CustID,PpeID,PeID,SubSys1ID,SubSys2ID,CharacteristicID,cmbID,PETemplateID)
SELECT CID,@PID,@PpeID,PeID,SubSys1ID,SubSys2ID,CharacteristicID,cmbID,PETemplateID
FROM PEDetail with(nolock)
where PpeID = @OldPpeid

Table Structure:

 SET ANSI_NULLS ON
    GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[PEDetail](
    [CID] [int] NOT NULL,
    [CustID] [int] NOT NULL,
    [PpeID] [int] NOT NULL,
    [PeID] [int] NOT NULL,
    [SubSys1ID] [int] NOT NULL,
    [SubSys2ID] [int] NOT NULL,
    [CharacteristicID] [int] NOT NULL,
    [cmbID] [int] NOT NULL,
    [PETemplateID] [int] NOT NULL,
 CONSTRAINT [PK_PEDetail] PRIMARY KEY CLUSTERED
(
    [CID] ASC,
    [CustID] ASC,
    [PpeID] ASC,
    [PeID] ASC,
    [SubSys1ID] ASC,
    [SubSys2ID] ASC,
    [CharacteristicID] ASC,
    [cmbID] ASC,
    [PETemplateID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[PEDetail]  WITH CHECK ADD  CONSTRAINT [FK_PEDetail_PEHeader] FOREIGN KEY([PpeID], [PeID])
REFERENCES [dbo].[PEHeader] ([PpeID], [PeID])
GO

ALTER TABLE [dbo].[PEDetail] CHECK CONSTRAINT [FK_PEDetail_PEHeader]
GO

ALTER TABLE [dbo].[PEDetail] ADD  CONSTRAINT [DF_PEDetail_cmbID]  DEFAULT ((0)) FOR [cmbID]
GO

NON Clustered Indexes:

CREATE NONCLUSTERED INDEX [ix_PEDetail_CID_PeID_6] ON [dbo].[PEDetail]
(
    [CID] ASC,
    [PeID] ASC,
    [SubSys1ID] ASC,
    [SubSys2ID] ASC,
    [CharacteristicID] ASC,
    [PETemplateID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_CID_CharacteristicID_CID_CharacteristicID] ON [dbo].[PEDetail]
(
    [CID] ASC,
    [CharacteristicID] ASC
)
INCLUDE ( [PpeID]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 85) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_PpeID_PeID] ON [dbo].[PEDetail]
(
    [PpeID] ASC,
    [PeID] ASC
)
INCLUDE ( [CID],
[CustID],
[SubSys1ID],
[SubSys2ID],
[CharacteristicID],
[cmbID],
[PETemplateID]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 85) ON [PRIMARY]
GO


texts:
We're running a Redis 2.8.4 instance on a Ubuntu 14.04 server where we're experiencing problems with a key that's being reset to 0.

It's a key we're only running INCR commands on, but every few months it goes to 0. Last time it was at around 29000, so it's not a very big number.

In our codebase, there's only 1 place where the key name is referenced, so I'm a little confused about what's happening.

The only thing i have to go on is an entry in the log (it's a standalone server, no slaves):

[1308] 15 Jul 08:37:20.157 * Background saving terminated with success
[1308] 15 Jul 08:40:52.085 # Connection with slave 127.0.0.1:0 lost.
[1308] 15 Jul 08:40:55.042 # Connection with slave 127.0.0.1:0 lost.
[1308] 15 Jul 08:42:21.034 * 10 changes in 300 seconds. Saving...
[1308] 15 Jul 08:42:21.034 * Background saving started by pid 28136


texts:
It is simple to dump select result into a file:
select *  from table_name into outfile  result.txt;

The result.txt contains records ,but no fields in it,how can dump selected result  with fields name?


texts: Why do I have to omit SQLEXPRESS when connecting from remote machine

texts:
I tried to connect to my remote SERVER\SQLEXPRESS for a couple of minutes, after I enabled TCP/IP with Port 1433 and Named Pipes.

However, it will only connect when I omit the SQLEXPRESS, so I only use SERVER as the instance.

What's the reason for that?


texts: Best way to move different desktop local PostgreSQL DB to single amazon RDS

texts:
I have system which is having java Desktop application with PostgreSQL which is installed on 5 machine each is having individual database and having databases into same database structure.

Now I want to move that all slave data to one master database periodically. Which way will be convenient ?

I found two approach:

Create REST service which will post data each row from each table to master database.
Create slave-master replication.

If there is any other technique then please update me.

Please explain how to apply best approach.

EDITED

Used same schema for all the desktop application and used sequences to generate primary keys.

I want to merge them all into single AMAZON RDS postgreSQL database.


texts: How can I determine which Innodb table is being written?

texts:
I am running Mysql 5.5.

I have about 100 Innodb tables spread over 10 DB's (mostly wordpress sites) and I am recently seeing a lot more disk IO than I have been, without any increase in traffic. The disk IO is from mysqld, and in monitoring mysqld, I am seeing what seem to me to be a lot of Innodb writes and specifically, I am seeing a lot of activity in the Innodb_rows_updated status variable that I can't easily explain.

Is there a way that I can determine which of the ~100 Innodb tables is having those rows updated?


texts: postgresql dump and load -- need to dump "postgres" db also?

texts:
If I pg_dump a db from an 8.4 cluster, and then load it into a different 9.1 cluster, do I need to pg_dump and restore the 'postgres' db as well? (I'm not familiar with what is contained in the 'postgres' db)

I want to upgrade a db from 8.4 to 9.1, so I tried pg_upgradecluster but got a lot of errors, so now I'm going to try doing a dump from 8.4 and load the dump into 9.1.

OS: Ubuntu 11.04 Natty


texts:
We have a database with a single table made up of around 700 million entries.
We update the database by adding new entries on a server then transfer the server to the production server using pg_dump:

pg_dump -c database > /tmp/database_gen

(by the way we use postgres 8.4)
we export the database to the production server by using psql.
The pg_dump generated file has instruction on how to create and fill the table.

The problem is with the index creation. Postgres fills the table then spend days creating the index. That was OK, until postgres couldn't create the index anymore due to the fact that it has no more diskspace because it uses a lot of disk space to for the temporary files for sorting and creating the index. Normaly the database takes around 200GB but during the index creation the used disk space increases to 600GB then after the creation it goes back to 200 GB.

My question is : can we create the index in several steps, like create the index for half the table then add the rest of the table and update the index?

Has anyone had the same issues ?

Thanks


texts: FTS non-latin: text-search query contains only stop words or doesn't contain lexemes, ignored

texts:
I'm trying to use Full Text Search with Chinese characters using a postgres (9.6.1) FTS extension ZHParser.

Following some of the examples here, I wanted to select my title column and match the title to_tsvector to the query string to_tsquery with:

select title from table1 where
to_tsvector('testzhcfg',title) @@ to_tsquery('testzhcfg','饭'); // 饭　 ＝ rice

table1 schema:

id  | title
--------------
1   | 饭
2   | 吃饭
3   | 我喜欢吃饭
4   | 谢谢
5   | 淘气

This gives:

NOTICE:  text-search query contains only stop words or doesn't contain lexemes, ignored
 title
-------
(0 rows)

I expected results:

rows:

title
-----
饭
吃饭
我喜欢吃饭

Since 饭 (rice) is present in each.

This article talks about the issue, and recommends ensuring UTF8 encoded DB. I've ensured the table is UTF8:

mydatabase=# \l
                                             List of databases
      Name      |     Owner      | Encoding |   Collate   |    Ctype    |         Access privileges
----------------+----------------+----------+-------------+-------------+-----------------------------------
 mydatabase     | myname         | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/myname               +
                |                |          |             |             | myname=CTc/myname

What other configurations need to be made to allow FTS to work with non-latin, specifically Chinese, characters?

Even more, if I search through the stop words of the configurations in /usr/local/share/postgresql/tsearch_data, I do not see 饭 listed.


texts: Repmgr: Error to promote Standby Cluster to Master Cluster in failover

texts:
I maked the configuration of the failover system based in Repmgr, but i don't haved success to promote the Standby Cluster to Master Cluster in case of failover.

Following my configurations, this process shoulded happens automatically, but does not works.

Additional Information:

Debian 9.2, PostgresSQL 9.6, Repmgr 3.3
IP Master (host1): 10.0.0.1, IP Standby (host2): 10.0.0.2
Contents included in /etc/postgresql/9.6/main/postgresql.conf, the same for all

wal_keep_segments = 5000
hot_standby = on
archive_mode = on
listen_addresses = '*'
max_wal_senders = 18
wal_level = hot_standby
shared_preload_libraries = 'repmgr_funcs'
archive_command = 'cd .'

Contents of /etc/postgresql/9.6/main/pg_hba.conf

Master

    local   all             postgres                                peer
    local   all             all                                     peer
    host    all             all             127.0.0.1/32            trust
    host    all             all             ::1/128                 md5
    host    repmgr          repmgr          10.0.0.2/32             trust
    host    replication     repmgr          10.0.0.2/32             trust

Standby

    local   all             postgres                                peer
    local   all             all                                     peer
    host    all             all             127.0.0.1/32            trust
    host    all             all             ::1/128                 md5
    host    repmgr          repmgr          10.0.0.1/32             trust
    host    replication     repmgr          10.0.0.1/32             trust

Contents of /etc/repmgr.conf

Master

    cluster=cluster
    node=1
    node_name=host1
    conninfo='host=host1 user=repmgr dbname=repmgr connect_timeout=2'
    failover=automatic
    promote_command='repmgr standby promote -f /etc/repmgr.conf --log-to-file'
    follow_command='repmgr standby follow -f /etc/repmgr.conf --log-to-file'
    logfile='/var/log/postgresql/repmgr.log'
    loglevel=NOTICE
    reconnect_attempts=4
    reconnect_interval=5

Standby

    cluster=cluster
    node=2
    node_name=host2
    conninfo='host=host2 user=repmgr dbname=repmgr connect_timeout=2'
    failover=automatic
    promote_command='repmgr standby promote -f /etc/repmgr.conf --log-to-file'
    follow_command='repmgr standby follow -f /etc/repmgr.conf --log-to-file'
    logfile='/var/log/postgresql/repmgr.log'
    loglevel=NOTICE
    reconnect_attempts=4
    reconnect_interval=5

Contents included in /etc/default/repmgrd, the same for all

REPMGRD_ENABLED=yes
REPMGRD_CONF="/etc/repmgr.conf"

Result of repmgr cluster show command

postgres@host1:~$ repmgr cluster show
Role      | Name   | Upstream | Connection String
----------+--------|----------|--------------------------------------------------------
* master  | host1  |          | host=host1 user=repmgr dbname=repmgr connect_timeout=2
  standby | host2  | host1    | host=host2 user=repmgr dbname=repmgr connect_timeout=2

Error found in log file (/var/log/postgresql/repmgr.log) when executing command "repmgrd"

[ERROR] unable to retrieve record for node 1: ERROR: relation "repmgr_cluster.repl_nodes" does not exist LINE 1: ...onninfo, slot_name, priority, active FROM "repmgr_cl...

Complete Tutorial: https://medium.com/@victor.boissiere/how-to-setup-postgresql-cluster-with-repmgr-febc2f10c243


texts: Best data modelling approach to handle redundant foreign keys in a database about Surveys, Questions and Responses

texts:
I am looking for advice on the best relational modelling approach to store surveys, questions, and responses.

I am looking for which of the two approaches below looks best, or an alternative approach to either.

I have at least these entities:

question
survey
person

And at least these relationships:

Each survey has 1 or more questions.
Each question may be used in 0 or more surveys.
Each person may take 0 or more surveys.

Here is where I run into trouble: how to model the responses to survey questions made by a person.

Here are two approaches I've considered, neither of which seems very good to me.  The diagrams here are greatly simplified to illustrate the issue.

Approach 1:

What I don't like about this approach:

The survey_person_question_response table has two different columns that refer to a survey: survey_question_survey_id and survey_person_survey_id

It would be an error to have different survey_id's referenced in one row for these two columns.  The survey_question must be from the same survey as the person took in survey_person. I can't see a good way to enforce this.

It seems like what I am doing here is making a relationship between two relationships.  That feels wrong to me for some reason.

Approach 2:

Try to avoid two FKs from approach 1 that should refer to the same value...

What I don't like about this approach:

There is no enforcement that the question_id and survey_id FKs are from a valid survey_question pair
There is no enforcement that the survey_id and person_id FKs are from a valid survey_person pair

Any advice on:

Whether one of these approaches is a typical approach
The pros and cons of one of these approaches over the other
A better way to arrange this data entirely

Would be greatly appreciated!


texts:
I have two tables in mysql

Table: objects

1: ID  (Auto Increment)         INT        eg: 1
2: Object (like "Flower)        TEXT       eg: Flower
3: Rank (like 90, calculated)   FLOAT      eg: 90

Table: Rankings

1: ID  (Auto Incement)          INT        eg: 1,2,3,4,5,
2: Object (like "Flower")       TEXT       eg; Flower,Flower,Flower,Flower,Flower
3: Person (like "Peter")        TEXT       eg: Peter,Hans,Markus,Emil,Egon
4: Ranking ( +/- Points)        FLOAT      eg: 100, -10 , -10 , -10 , 20

So, The Rank from Table1, is a sumary of all Rankings in Table2 where Object = Object.Table1. In this case it would be 90 for the Flower.

How can I build a trigger (on insert) for this? Every time someone posts a ranking the Rank.Table1 gets updated via a trigger.

Sorry for this question, but I'm very new to this.


texts: SQL case statement not executing else part

texts:
Problem statement:

Create a function to search a student with roll_no; if exists show "Student Name and Course" else show "Roll No not exist".

CREATE FUNCTION search(roll_num VARCHAR(50))
RETURNS VARCHAR(150)
RETURN
(SELECT CASE
        WHEN `roll_no`= roll_num  THEN CONCAT (`student`.`student_name`,`student`.`course_id`)
        ELSE 'Roll No not exist '
        END
FROM `student`WHERE `student`.`roll_no` = roll_num);

SELECT search(31219005) ; //since this is an existing roll num everything went correct.
SELECT search(21219005) ; //this roll is not an existing one in the DB, but it simply shows NULL , instead of existing my else part.


texts: Is it possible to recover deleted rows from ibdata1? I have only ibdata1 file and no other files or tables. I want to recover all table structures and datas with deleted records. Is it possible ?


texts:
Why would SQL Server silently fail to re-enable a foreign key constraint?

I recently attempted to re-enable a number of constraints using ALTER TABLE. Most enabled successfully; however, minority of the constrains are still reported as disabled by sys.foreign_keys.

Example:

ALTER TABLE [dbo].[Table] WITH CHECK NOCHECK CONSTRAINT FK_ConstraintName;
-- Command(s) completed successfully.

SELECT is_disabled FROM sys.foreign_keys
WHERE name = 'FK_Constraint' AND parent_object_id = OBJECT_ID('[dbo].[Table]');
-- returns "1", indicating that the constraint is still disabled


texts: Point in time restore fails - backup file from before the restore point not available (why does this matter)

texts:
I have tried to do a point-in-time restore of a database through the management studio gui, the point-in-time being yesterday morning (2016-04-12).

This fails with the error

  "...cannot open backup device [name of backup from 2016-02-19]..."

so it cannot access the backup from two months ago - I know there's a very good reason for this, as that backup is no longer present (we don't keep backups that long).

My question is, why is SQL Server trying to access a backup file from two months ago for a restore to a point in time only yesterday? Is there something I'm doing wrong here?

I've checked all my settings in the restore wizard gui (to make sure I hadn't got the date wrong). All the required (Or the only ones I think should be required) backup files are correct and accessible. I am going to just restore the 2016-04-11 nightly backup manually anyway so the urgency for this is off, but it's still interesting/annoying.

I can't see anything in BOL that explains why SQL server is trying to access an old backup for this.

Setup info:

SQL Server 2012 (SP3)
FULL Recovery model
Nightly full backups
T-log backups every 10 minutes

Restore DB screenshot:


texts:
I have 20 users that each keep a copy of a database on their local computer (technology other than SQL Server).
Their copy (we call it a sandbox) is updated each morning and their previous changes are throw away.
We are migrating this data to SQL Server and want to maintain the sandbox feature, but we don't want the overhead of installing SQL Server on each of their computers or making 20 separate databases.
Is there a "copy on write" feature that is easy to turn on that will essentially allow each person to only see their changes to the database while still using a common database?  I don't want to make big changes to the code to achieve this.  If big changes to the code are necessary I will probably go with SQL Server Express and have the database on their local machine.
The users are not technical.
The application that reads the data is an .exe that runs on the users' machine.
We can run what ever version of SQL Server that would support this.
There are enough nuances to the database interaction with the app that I am only going to use SQL Server as trying to switch to e.g. SQLite would find all kinds of problems.
The users need to update the data in sandbox so a read-only solution is not an option.


texts: How to implement a 'default' flag that can only be set on a single row

texts:
For example, with a table similar to this:

create table foo(bar int identity, chk char(1) check (chk in('Y', 'N')));

It doesn't matter if the flag is implemented as a char(1), a bit or whatever. I just want to be able to enforce the constraint that it can only be set on a single row.


texts:
We have a problem with one of our SQL Servers that I have never seen before.

The SQL Server (Enterprise Edition) runs a database with only 70MB data. But after a few hours the server takes 10 GB memory or more. After a few days the server runs out of memory. I already configured the max memory parameter in SSMS, but it didn't help.

Do you have any ideas what to do to find the problem?

Here is the link to the result of dbcc memorystatus: https://drive.google.com/open?id=0B2FJAT6lteKJOEJmUFdqb0ZERFU

Database Version: Microsoft SQL Server 2014 (SP2-CU5) (KB4013098). Enterprise Edition. This is a virtual machine.

I checked another thread and found this query:

SELECT TOP (21) [type] = COALESCE([type],'Total'),
  mb = SUM(pages_kb/1024.0)
FROM sys.dm_os_memory_clerks
GROUP BY GROUPING SETS((type),())
ORDER BY mb DESC;

I ran this on the SQL Server and get the following results:

total = 2452,9
CACHESTORE_SQLCP = 2141,5
MEMORYCLERK_SQLBUFFERPOOL = 160
MEMORYCLERK_SOSNODE = 33
USERSTORE_SCHEMAMGR = 30.94

Is this normal that the cachestore value is that high after it has been running only hours?

Running this query:

select value_in_use
from sys.configurations
where name = 'max server memory (MB)'

Gives the result 8192. This is the value we configured for the instance.

At the moment we have to restart the SQL Server every night so that it is not using too much memory.


texts: Mongodb _id Value and Indexes Are Lost During Mongorestore

texts:
Steps I used:

mongodump
copy dump folder to another computer (for each collection I get .bson and metadata files)
mongorestore myDumpFolder

I noticed the indexes are lost and _id has new values after mongorestore.

Can anyone share their restore experiences that restored both indexes and mongodb_id?


texts: Mysql keeps re-adding disable-log-bin=1 to my.cnf

texts: We have mysql 8 server configured as master. Every time WHM updates mysql, disable-log-bin=1 is added to my.cnf. Not sure where is the issue, but it is already 3rd time it happens. Current problem was when it updated from 8.0.22 to 8.0.23. Any way to ensure my.cnf will not be touched during update?


texts: Sql getting debit, credit and balance issue

texts:
I am using the query below to get Debit/Credit Balance from table statement

SELECT  t.[InvoiceID], t.S_Type as Type,
        t.Date, t.Debit, t.Credit, b.Balance
FROM Statement as t CROSS apply
     (SELECT Balance = SUM(Debit) - SUM(Credit)
      FROM Statement as x
      WHERE (x.date < t.date or
             x.date = t.date
            ) AND
            x.CustID = t.CustID
     ) b
WHERE t.CustID ='48' and date between '2015-01-01' and '2016-01-01'
ORDER BY t.date

output

InvoiceID   Type                Date    Debit   Credit  Balance
51         Service Invoice  2015-08-29  500.00  0.00    500.00
51         Receipt Voucher  2015-09-07  0.00    500.00  0.00
76         Service Invoice  2015-09-28  1000.00 0.00    1500.00
208        Sales Invoice    2015-09-28  500.00  0.00    1500.00
119        Sales Invoice    2015-10-31  500.00  0.00    2000.00
76         Receipt Voucher  2015-11-21  0.00    500.00  500.00
208        Receipt Voucher  2015-11-21  0.00    500.00  500.00
119        Receipt Voucher  2015-11-21  0.00    500.00  500.00
165        Service Invoice  2015-12-01  500.00  0.00    1000.00
165        Receipt Voucher  2015-12-22  0.00    500.00  500.00
224        Service Invoice  2015-12-31  500.00  0.00    1000.00

First, how can I get each Receipt Voucher under its invoice?

Second, when I have the Receipt Voucher with same date as the invoice how can I be sure it shows under the Invoice using order by type to get correct values for the balance?

The expected output - invoices ordered by date and its Receipt Voucher under it.

InvoiceID   Type           Date        Debit   Credit  Balance
51        Service Invoice 2015-08-29  500.00  0.00    500.00
51        Receipt Voucher 2015-09-07  0.00    500.00  0.00
76        Service Invoice 2015-09-28  1000.00 0.00    1000.00
76        Receipt Voucher 2015-11-21  0.00    500.00  500.00
208       Sales Invoice   2015-09-28  500.00  0.00    1000.00
208       Receipt Voucher 2015-11-21  0.00    500.00  500.00
119       Sales Invoice   2015-10-31  500.00  0.00    1000.00
119       Receipt Voucher 2015-11-21  0.00    500.00  500.00
165       Service Invoice 2015-12-01  500.00  0.00    1000.00
165       Receipt Voucher 2015-12-22  0.00    500.00  500.00
224       Service Invoice 2015-12-31  500.00  0.00    1000.00


texts: Mysql - show results from 3 tables excluding NULL or EMPTY values for certain fields

texts:
I have 3 simple tables with polls, users and votes. I'd like to show all polls ordered by clean count of votes (sometimes either poll_id or user_id don't get inserted).

Furthermore I'd like to show a list of users voted, similar to fb (for now my solution is to get that list with new query fetched by ajax call.)

would it be better to create one query to get all results and read them separatelly from array or is it best practice to create separate requests?

My query bellow does get the ordered list but includes the empty or null rows and is very slow for table with >10000 results. any suggestions?

SELECT id,
  title,
  pollid,
  COUNT(pollid) AS clean_count
FROM
(
  SELECT DISTINCT user_id, pollid
  FROM tbl_votes
) AS tmp_tbl
JOIN tbl_polls
  ON tbl_polls.id = tmp_tbl.pollid
WHERE not isnull(tmp_tbl.user_id)
GROUP BY pollid
ORDER BY clean_count DESC

Code to match results of votes with voters:

SELECT DISTINCT fbid, COALESCE(username, 'User not found.')
AS username
FROM tbl_votes JOIN tbl_users
ON tbl_users.fbid = tbl_votes.user_id
WHERE pollid = $band_id AND not isnull(username)


texts:
I install postgresql in my ubuntu Server 18.04 LTS, and I want it support OpenSSL.

I do the lengthy way with PostgreSQL source code installation, where I can select the openssl feature:

 ./configure –with-openssl
  make
  make install
  ldconfigure

Upon installing the Ubuntu server I can select with the help of tasksel PostgreSQL to be installed, or using the apt-get postgresql-10  postgresql-contrib

Does the 2nd way configure PostgreSQL to add the feature of openssl (be SSL ready)?


texts: PostgreSQL - Violate foreign key on purpose

texts:
I have an app 1.0 that uses a database called let's say foo of at least 150 tables filled with data. I have upgraded the app to 3.0 and now I also have a database called foo2 which has almost the same tables and columns as foo. I can't use the app 3.0 with foo because of a bunch of new tables with views and new basic data. I tried migrating the data from foo to foo2 but failed because of the foreign keys constraint (I have like 10 fkeys almost each table). I only need to import about 15 tables of important data from foo to foo2.

Example : I want to import a product with the same columns into the product table of foo2. The product is referencing to a category_id, warehouse_id, account_id partner_id and this partner is referencing to a company_id and this company is referencing to a country_id and so on. I can't add the product because I have no partner_id in foo2 yet. The same goes for adding partners, companies, countries, etc.

I know I could probably start importing data from the root and go on but like I said, I would have 336 tables to go through which is a LOT of work for 15 tables.

So I was wondering if there's a way to ignore temporarily the fkeys constraints until all of my data are imported even if the fkeys point to a non existing id for a short while and then check for integrity problems and adjust values?


texts: How to speed up MySQLl loading from dump file?

texts: I'm using mysqld  Ver 5.7.25-0 in Ubuntu 16.04. I dumped from AWS Relational Database Service (RDS) with mysqldump --single-transaction --quick --max_allowed_packet 1G ... and now when I tried to load the dump with the command: mysql -u <mu_user_name> -p<my_pwd>  <mu_db_name> < /mnt/rds.sql I got stuck for almost 5 days. How long should it take and how can I speed up the processes? The rds.sql dump file size is 254G. btw I redirect the datadir of MySQL with symlink according to this tutorial.


texts: Command runs fine in elevated command prompt, but fails when run in a Windows Task Scheduler task

texts:
Here's my predicament:

The version on the server is SQL Server Express. And no, we cannot use any other version. (Long story, but: vendor will not allow without $ome...)
From 1, you can deduct that there is no SQL Server Agent jobs.
Hence the need to automate SQL Server backups "creatively".
We did so years ago, and it was working perfectly for years (more than 2).
What did we do? We created the command string sqlcmd -U SQLBackupOper -P <password> -S GSUDM -Q "EXEC sp_BackupDatabases".
Yes, sp_BackupDatabases is a stored procedure in the Master database that backs up all DBs.
Yes, if I login to the server and open an elevate (as administrator) command prompt and run the command in number 4, it works perfectly. So, the password is correct. I copied and pasted several times to make sure, and no, the problem is not the password.
Yes, the login SQLBackupOper is mapped to all DBs and has the role membership of db_backupoperator.
We also created a task in the Windows Task Scheduler that is meant to run the command string in number 4, and it worked for years. Now, for some indeterminate time (I am not sure how long) the task is failing. When I look at the SQL Server error logs I am greeted with the error that reads Login failed for user 'SQLBackupOper'. Reason: Password did not match that for the login provided.
Yes, the password in the command string is correct. I tested it from the command prompt via copy and paste. Again, it works from the command prompt. It only fails if run from the task scheduler, the same exact command string.
Yes, I did try varying the user that runs the job. Still failing.
Yes, I do run the task with the highest privileges.

With that said, has anyone faced this problem before? Again: It works from an elevated command prompt, but it complains when using the same command string citing wrong password when run as a task in task scheduler. Thoughts? Suggestions?


texts: Data compression within Internal Tables in SQL server 2008R2

texts:
We are doing downgrade from enterprise to standard for couple of servers which are not using any enterprise features:

For one of our TFS(Team foundation server) database TFS_resx, when we ran

SELECT * FROM sys.dm_db_persisted_sku_features

it returns null value for each column specifying that this database is not using any of enterprise features:

However, when we run the query as shown below to find at object level for any kind of compression used, we found one for internal table:

select so.name,so.type,so.type_desc,
sp.data_compression,sp.data_compression_desc
from sys.partitions sp
inner join sys.objects so
on (so.object_id=sp.object_id)
where sp.data_compression!=0

it says row compression is being used for an internal table

So, i would like to know how come this is not captured as an enterprise feature when i ran SELECT * FROM sys.dm_db_persisted_sku_features. Or may be it is not one.

Just curious in case anyone has witnessed if we need to remove this row compression from internal table of the DB in question or we would be good to downgrade (restore the tfs db onto standard version).

Thanks!


texts: How to perform a left join of list of dates with an existing table of records smartly

texts:
I am using the following query to get a list of dates between two given dates:

SELECT selected_date FROM
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
WHERE selected_date BETWEEN '2016-03-08' AND '2016-03-11';

This gives me the following output:

+---------------+
| selected_date |
+---------------+
| 2016-03-08    |
| 2016-03-09    |
| 2016-03-10    |
| 2016-03-11    |
+---------------+

The following query gives me a list of all employees required to work for a particular date:

SELECT employee.id, employee.firstname
FROM Users employee
LEFT JOIN Table1 table1 ON table1.fId = employee.tId
WHERE table1.day = dayofweek('2016-03-10')

This yields:

+----+-----------+
| id | firstname |
+----+-----------+
|  1 | Mat       |
|  2 | Tom       |
+----+-----------+

The results are empty if no employee is required to work on the given date.

I have a thrid table which lists the performance of each employee for a particular day:

SELECT * FROM performance;

+----+------------+------------+------+
| id | employeeId |    date    | val  |
+----+------------+------------+------+
|  1 |          1 | 2016-03-08 | 100  |
|  2 |          2 | 2016-03-08 | 90   |
|  3 |          1 | 2016-03-09 | 80   |
|  4 |          2 | 2016-03-09 | 100  |
+----+------------+------------+------+

I want to join the above three results to yield:

+----+------------+-----------+------------+------+
| id | employeeId | firstname |    date    | val  |
+----+------------+-----------+------------+------+
|  1 |          1 | Mat       | 2016-03-08 | 100  |
|  2 |          2 | Tom       | 2016-03-08 | 90   |
|  3 |          1 | Mat       | 2016-03-09 | 80   |
|  4 |          2 | Tom       | 2016-03-09 | 100  |
|  5 |          1 | Mat       | 2016-03-10 | null |
|  6 |          2 | Tom       | 2016-03-10 | null |
+----+------------+-----------+------------+------+

Note : On 2016-03-10 Mat and Tom aren't required to work. Hence, their values should be null in the final result. The performance table will not have entries for Mat and Tom for 2016-03-10 in this case.

I am kind of lost on how to achieve this.


texts: Partitioning in SQL Server: Best choice and partition keys

texts:
In my application, I want to partition two tables, "files" and "value_text", because in the next days I have to insert 300 million rows (files) and 1.200 million rows (value_text) in these tables.
Furthermore, this tables will receive between 200.000 - 400.000 inserts every day.
I use SQL Server with Azure. Specifically, Azure SQL Database with pricing/model tier "Standard S4 (200 DTUs)".
Daily, the application has to support searches in this tables filtering by some columns (the tables schemes are at the end of the post):

Files: There are filters by id, cid, eid, name and created columns.
Value_text: There are filters by id_file, id_field and value.

In this searches I need to give priority to the most recent inserts, I mean, when I search for a register which was insert a long time ago I can assume a slow search, but for the most recent inserts, I need a fast search.
One of my reasons for using partitioning is because I need to make many non-stop inserts and I can't block those tables for a long time because the application needs to continue running. So, to solve this problem I had thought to use partition exchange/switch.
I think that "Horizontal partitioning" (sharding) is the best choice for my problem, but I have some questions:

How to choose the partition key for each table?
What partition strategy fits best with my problem? (Lookup, Range or Hash)
Should all the partitions supports the same number of rows? Or should I have
partitions with different size?
How to determinate the size of each partition?

Additional info
files table:
CREATE TABLE [dbo].[files](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [cid] [bigint] NOT NULL DEFAULT ((0)),
    [eid] [bigint] NOT NULL DEFAULT ((0)),
    [year] [bigint] NOT NULL DEFAULT ((0)),
    [name] [nvarchar](255) NOT NULL DEFAULT (N''),
    [extension] [int] NOT NULL DEFAULT ((0)),
    [size] [bigint] NOT NULL DEFAULT ((0)),
    [id_doc] [bigint] NOT NULL DEFAULT ((0)),
    [created] [datetime2](7) NULL DEFAULT (getdate())
CONSTRAINT [PK_files_id] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON),
 CONSTRAINT [files$estructure_unique] UNIQUE NONCLUSTERED
(
    [year] ASC,
    [name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO

ALTER TABLE [dbo].[files]  WITH NOCHECK ADD  CONSTRAINT [FK_files_client] FOREIGN KEY([cid])
REFERENCES [dbo].[clients] ([id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[files] CHECK CONSTRAINT [FK_files_client]
GO

value_text table:
CREATE TABLE [dbo].[value_text](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [id_file] [bigint] NOT NULL DEFAULT ((0)),
    [id_field] [bigint] NOT NULL DEFAULT ((0)),
    [value] [nvarchar](255) NULL DEFAULT (NULL),
    [id_doc] [bigint] NULL DEFAULT (NULL)
 CONSTRAINT [PK_value_text_id] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO

ALTER TABLE [dbo].[value_text]  WITH NOCHECK ADD  CONSTRAINT [FK_valuesT_field] FOREIGN KEY([id_field])
REFERENCES [dbo].[fields] ([id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[value_text] CHECK CONSTRAINT [FK_valuesT_field]
GO


texts: Identify root cause for why database is stuck in RESTORING state

texts:
I know there are questions which solve the issue of a database being stuck in RESTORING state and have used those solutions to manually bring the database back online, but my scenario is somewhat different.

I have an automated restore using Powershell scripts which restores a copy of production to a DEV instance. The scripts are unchanged for about a year and occasionally the restore process finishes but the restored database is stuck in RESTORING state (sometimes the script works fine, sometimes it fails like this).

Each time if I manually re-run the process it works or if I manually restore the database from SSMS's user interface or through T-SQL it completes without a problem.

I've found answers which recommended to run CHECKDB on the restored DB but nothing came out as a cause for this problem.

Since the restore scripts restore a FULL backup of the database and uses a "WITH RECOVERY" option I am trying to find out what might be stopping the restore process, although I'm actually restoring it using "WITH RECOVERY".

Any suggestions are really appreciated since I'm stuck at trying to understand why this is happening from time to time.

I'd very much like to solve the root cause of the issue rather than treat the symptoms, which is to manually restore the DB once again.

Update:

Github Gist as @Brent recommended - here.


texts: 4 CPUs of 20 (NUMA node 0) Pegged at 100%

texts:
The previous Senior DBA left the company and I was made aware of a server which suffers from multiple problems, largely slowness (all the way from SSMS taking a long time to open, long running queries, and failing SSIS jobs (Data Warehouse), to difficulties connecting to linked servers (150+ of them)).

There is most likely way too much happening on this server which contains 5 stacked instances. A new Senior DBA will be joining the team soon but it would be nice to have this all straightened out for him or her by the time they join.

So, the nitty-gritty:

When I was made aware of the issue I found that 94% of the server's memory was allocated to SQL Server. I went ahead and brought that down to 85% by deallocating memory from two instances which were overprovisioned.

I then noticed that MAXDOP on our default instance was set to 4 (possibly 6, I don't recall) along with a CPU Affinity setting. Those CPUs were pegged while there was minimal activity on the others. I went ahead and removed the Affinity setting (since these settings were in place before additional CPUs were added). I set MAXDOP to 20 across all 5 instances.

Currently, I'm still seeing 4 of the CPUs pegged but with an overall avg. usage (across all CPUs) of roughly only 25%.

  I've used SysInternal's ProcExp, the Resource Monitor, and the Windows Performance Toolkit to observe the issue but don't really know how to isolate what process(es), specifically, is(are) the root cause. Any recommendations / guidance on how to truly isolate what's happening here? (i.e. specific counters / traces / other programs.)

UPDATE, per requests:
System Info:
Windows Server 2012 R2 Standard
64 GB memory total
20 CPUs

Configuration:
24 GB memory allocated to this instance
26.5 GB memory allocated to other instances (total of 50.5 GB - 78.9%)
Cost Threshold for Parallelism = 50 (across all instances)
I disabled an unused SSAS process.


texts: Does the order I declare foreign key constraints in matter?

texts:
Does it matter what order I add foreign key constraints to a table in, or what order I write them in a CREATE TABLE statement? MySQL clearly at least remembers the order; if I run the DDL statements below...

CREATE TABLE foo(id INT PRIMARY KEY);
CREATE TABLE bar(id INT PRIMARY KEY);
CREATE TABLE baz (
  foo_id INT,
  bar_id INT,
  FOREIGN KEY (bar_id) REFERENCES bar (id),
  foreign key (foo_id) REFERENCES foo (id)
);
CREATE TABLE qux (
  foo_id INT,
  bar_id INT,
  FOREIGN KEY (foo_id) REFERENCES foo (id),
  FOREIGN KEY (bar_id) REFERENCES bar (id)
);

... and then run SHOW CREATE TABLE on each of baz and qux...

mysql> SHOW CREATE TABLE baz\G
*************************** 1. row ***************************
       Table: baz
Create Table: CREATE TABLE `baz` (
  `foo_id` int(11) DEFAULT NULL,
  `bar_id` int(11) DEFAULT NULL,
  KEY `bar_id` (`bar_id`),
  KEY `foo_id` (`foo_id`),
  CONSTRAINT `baz_ibfk_1` FOREIGN KEY (`bar_id`) REFERENCES `bar` (`id`),
  CONSTRAINT `baz_ibfk_2` FOREIGN KEY (`foo_id`) REFERENCES `foo` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE qux\G
*************************** 1. row ***************************
       Table: qux
Create Table: CREATE TABLE `qux` (
  `foo_id` int(11) DEFAULT NULL,
  `bar_id` int(11) DEFAULT NULL,
  KEY `foo_id` (`foo_id`),
  KEY `bar_id` (`bar_id`),
  CONSTRAINT `qux_ibfk_1` FOREIGN KEY (`foo_id`) REFERENCES `foo` (`id`),
  CONSTRAINT `qux_ibfk_2` FOREIGN KEY (`bar_id`) REFERENCES `bar` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

... then we can see that MySQL remembered that the foreign key pointing to foo comes first in baz, but second in qux.

It's possible to imagine this order making a difference; for instance, if MySQL, upon insert into a table with foreign keys, takes out locks on referenced tables in the order that the foreign keys are declared, then simultaneous inserts into baz and qux in my example above would have the potential to deadlock, and so it would be a best practice to ensure that foreign keys were always declared in the same order on all tables (e.g. by alphabetical order of the referenced table name). Do any such considerations exist in reality, or does the order of foreign key constraints have no effect on anything?


texts: List of all database without a specific index

texts:
I am working on a Multi Tenant System. In which we have one Manager DB and Each client has a Company DB.

Now all of these database have the same Table Schema.

But some of them have a missing Index on a certain Table.

Is there a way to get a list of Databases with that missing index using SQL? We are using SQL-Server 2012

EDIT:

Let's say Company_1 has an index on TableAcalled IX_Missing_Index - but Company_2 and Company_3 DO NOT have the index 'IX_Missing_Index' - So all I need in the result set is Company_2 and Company_3

e.g. like this

    Database Without index IX_Missing_Index
    ----------------------------------------------
    Company_2
    Company_3


texts: Is there a way to display current noise words in SQL 2000 Full Text Indexing?

texts:
I know this is ancient technology, but sometimes upgrading is not always an option.

Does anyone now how to list the actual noise words that are currently in use on a SQL 2000 Full Text Index?

We have a recurring problem on a very old SQL 2000 system.  It is a 3 way Windows 2003 cluster.  Once in a while the cluster fails over to a new node, and when that node comes online it seems to be using a different noise word list than what we want.

It seems that various service packs for SQL 2000 leave noise word files, and when the failover happens the first noiseword file found (in any directory) is the one that gets loaded.


texts: AWS Aurora Mysql different instance types for writer/reader

texts:
I have a db.r5.large for the master/writer.
I want to add some readers.
Do they need to be the same instance type as the master? Or can i add for example 3x db.t3.medium ?


texts: In Postgres, how to preserve newline character in COPY TO command?

texts:
One of the column of my table contains string with '\n'.

postgres=# select * from stringtest ;

 id |    inputstr

----+-----------------

  1 | Name : NOC\r   +

    | \r             +

    | Detail : Detail

(1 row)

But when I retrieve same table with COPY TO, new line character is getting escaped

postgres=# COPY (select * from stringtest) TO STDOUT WITH DELIMITER E'\t' ;

1       Name : NOC\r\n\r\nDetail : Detail

How can I make COPY TO command to replace '\n' with new line ??

Expected output :

postgres=# COPY (select * from stringtest) TO STDOUT WITH DELIMITER E'\t' ;

1       Name : NOC\r
        \r
        Detail : Detail

How to achieve this ??


texts: MariaDB master to master replication auto increment is not sequential

texts:
I have two master to master MariaDB server replication with the below settings:
server A with 192.168.1.100:
------------------------------
bind-address            = 0.0.0.0
server-id       = 1
log-bin         = "mysql-bin"
binlog-do-db        = wordpress
replicate-do-db     = wordpress
relay-log="mysql-relay-log"
auto-increment-increment = 2
auto-increment-offset = 1

and:
Server B with 192.168.1.200:
---------------------------------
bind-address            = 0.0.0.0
server-id               = 2
log-bin                 = "mysql-bin"
binlog-do-db            = wordpress
replicate-do-db         = wordpress
relay-log="mysql-relay-log"
auto-increment-increment = 2
auto-increment-offset = 2

The replication works great but I have problem with auto increments. As you see they are not sequential. For example:
Server A increments: 1, 3, 5, 7, 9 and ...
Server B increments: 2, 4, 6, 8 and ...
The problem occurs when I insert 2 records in server A and 1 record in server B, the total primary key of the table will look like this: ID : 1, 3, 4
As you see number two is missing because I have inserted two records in server A. But I need auto increment to generate sequential numbers since we use them as our invoice numbers.  Gaps can get us in trouble with TAX laws in our country.
I know this is the internal mechanism for MariaDB and MySQL databases in order to avoid duplicate keys. but I just wanted to know if there is an alternative HA solution for sequential index replication.
Thank you


texts: Is there a reason/benefit I am not aware of for this kind setup?

texts:
I now manage a MySQL database that was designed and created by another.

I am confused about a certain setup that the original developer created..

He did not use auto increments for table IDs. Instead, he created a table called
system_sequences which has two columns:

system_sequences.Table_Name
system_sequences.Next_Value

Here is an example of a table and how it currently inserts into the table (try my best):

there is a table called customer with an unique ID column.

If I want to insert into customer, currently I have to do the following:

SELECT system_sequences.Next_Value
FROM system_sequences
WHERE system_sequences.Table_Name = 'customer'

save the next_value into a variable and use it as the next ID for customer

then I have to:

UPDATE system_sequences
SET Next_Value = Next_Value + 1
WHERE system_sequences.Table_Name = 'customer'

I did not design this, I do not like it in any way shape or form  ( I actually hate it ) and I want to convert everything carefully to auto_increment

But is there a reason for this or a benefit that I am not aware of? I have never seen anything like it before. And there is no documentation for this database that explains anything.


texts: Manual sql failover machine with replication

texts:
I hope this is the correct place to ask this question. We recently experienced one of our virtual machines being offline for 3 days due to issues in the data center hosting it. This quickly brought us to realize that we where not well prepared for failures in our system. Since then we have moved data centers and as a precaution I have applied for a separate smaller virtual machine hosted in a different location for a backup machine.

I have drawn a very rough image of what I want to try and achieve.

So to explain what is happening here.

He have a primary sql server which websites and services access. via a dyndns name, for ex.  sql1.dyndns.org

certain tables and columns are replicated off to a group of subscribers (they are read only) and I do live transaction replication. Other services run on the subscriber machines which access the local db on that subscriber.

Now my issue comes in, say my primary sql server goes offline. All of my services depending on it stop working. hence why I want to add the backup machine. Say primary goes down. I simply go and change the IP address associated with sql1.dyndns.org and then all services should be able to continue running. However I then still need my replication to the subscribers from my secondary machine. As data which is inserted into that database needs to be sent off to the subscribers immediately.

So my questions:

Can I do what I have described using the standard sql server methods and technologies which I have mentioned.
Is there maybe a better way to achieve what I am looking for? Am I over-complicating it?
What technology should I use for the purple link (replication / live mirror etc)
After my primary is back online, how would I swap back over to it without loosing the changes in data?


texts: How to arrange rows selected in MYSQL query?

texts:
I'm fetching data from a table "TABLE_A", it's having Primary Key as "PK_ONE" (which is unsigned INT) .

I'm fetching data from "TABLE_A" with where clause on PK_ONE ;
Ex: SELECT PK_ONE from TABLE_A where PK_ONE in (2,88,3999,4,282,33399,1,394);

When I'm using dataset mentioned above in PHP cursor; I want to ensure that data fetched comes in the sequence of values of "PK_ONE" in WHERE clause.

Can you please give me some direction to achieve same? [Currently rows are coming in asending order]


texts:
I am trying to connect PowerBi to a local SQL Server instance.

It has previously worked with a direct Windows Authentication. I tried changing it to a SQL Server Authentication. No luck.

I have specified which login type it was referring to.

The error it reports: "We couldn't authenticate with the credentials provided". I logged in without issues on both authentication methods in SQL Server Management Studio - works perfectly.

What else can I try?


texts:
I'm a bit puzzled by a query that runs slower in production than test (as always..)

Can't quite figure it out since both queries are identical in every way.
Test query runs in 0,5 sec and prod takes 5 sec. Servers are identical regarding version. I tried the query in prod when it was idling at night, no difference. The test database was created a month ago, no big size difference.

The query plan is identical, looked at it in plan explorer and I can't spot the difference.

Adding some facts:

Prodserver and testserver on same storage and esx-host
Not database related, restored testdb to prod, proddb to test, proddb to proddb-copy
Identical SQL settings (apart from database mail and DAC)
Prodserver not under pressure when testing, testserver got 2 less cores and 15Gb less mem
Restored the db on my laptop, got the same good performance that I get on the test server.

Next step (Thanks Joe Obbish) I'll have a look att the code in the function. Gonna test to execute the code outside function and check what plan it produces, (SELECT dbo.cfn_diffworkingminutes('2016-10-01', GETDATE()


texts:
A colleague of mine reported that an hour after starting a restore the operation failed because the server ran out of disk space.  He mentioned that since we are using filestream the size isn't included in the backup size.

Does this sound right?  I thought the first thing sql server did up on restoring was to check the amount of disk necessary [including filestream objects] then reserve the space on disk, start initializing the allocated disk space and finally write out the data.  I didn't think it was possible to run out of disk space mid-restore.

Does any of this sound right?  Does anyone know of a reason the restore would fail halfway through?


texts:
1) I have mongo instances A, B and C, each having a different set of users with different roles. I have used mongo shell to connect with A through an Admin user. Now, to create a replica and add B and C, do I need to pass through auth on B and C. Can you please help me with the commands?

2) I have mongo instances A, B and C, each having a different set of users with different roles. After I create a replica with A, B and C, what happens to the users of individual nodes. Are all the users from each node available in the replica?


texts: Connecting to MySQL VM remotely (Azure Resource Manager)

texts:
I have a VM setup in Azure (running Windows Server 2012 R2)  with MySQL 5.7 installed. I am trying to access this using MySQL Workbench - which when running on the server works perfectly.

However, trying to use Workbench from my own PC is giving me there error "10060":

I have also tried doing this using a PHP file in an Azure web app to connect, again not working.

I have set the following:

Azure Security Group, inbound port 3306 is open

On the server the following inbound port rules have been applied:

User is set to stackexchange@% (i.e. not localhost)

Is there something I have missed? Do I need to allow the database to have remote connections?

Apologises if this is the wrong site. Wasn't sure if it was server related or related to the DB itself.

There is this tutorial by Microsoft but it uses the classic deployment model - not Resource Manager. And questions like this on here don't cover MySQL specifically.


texts: Where is the default location for the SQL Agent Job step output file? How do I update it?

texts:
I recently migrated my system databases to a new location.

I then experienced failures of my SQL Agent jobs with the following error:

2015-10-03 01:53:10 - ! [136] Job  -     reported: Unable to open Step output file.

I can set the location for the step output file by accessing the 'Job Step Properties - Advanced' tab, but it is blank.

Where is the default location for this?

I have found resources which specify it is
The default location depends on the location of your

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\xxxxx.txt

but this is not the case on my server.  Is there an environment variable that holds the path to MSSQL\Log as I changed the directory and this would explain why the failures occurred.  I would like to update the default without having to enter a static location for every job.

I initially thought this could be set by using the sp_set_sqlagent_properties store procedure like so:

USE MASTER
GO
EXEC msdb.dbo.sp_set_sqlagent_properties
@errorlog_file=N'U:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAGENT.OUT'
GO

But this still allows existing jobs to fail.

Is there a way to script the update of the step file location from one drive to another but using the same file paths and names?


texts: Is reply to client about transaction commit, before binary log or after?

texts:
When does MySQL server reply to client that a SQL statement has completed or a transaction has been committed?

Before it writes to binary log or after?


texts: Deleting other files during maintenance plan

texts:
I have an SQL Server 2012 maintenance plan which does the normal stuff (rebuild indexes and update statistics, back up DBs, deletes .bak files older than X days, etc). Everything is working fine with it, except one Maintenance Cleanup Task:

This should be deleting all .7z archives (equivalent to .zip or .rar) which get accidentally left in the nightly backups directory (despite our best efforts, we keep ending up with some people leaving these files on the server...), but we still have old .7z files present in subdirectories (e.g. E:\Backups\foo\bar.7z ) after the maintenance plan runs.

There are no errors in the SQL Server Logs.

I suspect this is because the "Delete files of the following types: Backup files" option is restricting the file types which can be deleted. I've also tried changing that to "Maintenance Plan text reports" with the 7z extension, with no change.

Is it possible to have a maintenance plan delete arbitrary (non-SQL Server) files during a Maintenance Cleanup Task? If so, any idea what I'm doing wrong?


texts:
We utilize a Software that when creating a new user, automatically makes their host a wildcard (%), and we have no method to change this.

Is there anyway to enforce IP Whitelisting via MySQL Globally, to only allow our companies IP to reach our server?


texts: How do I troubleshoot loss of connection with SQL server

texts:
I have an application that intermittently has a break in the connection between it and the SQL Server instance it requires to function. What is a good way to go about troubleshooting this type of problem. Is there some kind of log where SQL server logs something if it drops a connection on purpose.

I understand that just about anything in between the application and the database could be causing the connection to be lost. I don't think its a network connectivity issue between the client and the server because this application is being served via Citrix XenApp and all the other instances running on that same host are not having trouble at, or around that point in time when I saw the problem.

If it matters, this is SQL Server 2005 - Enterprise Edition. My application is a Visual FoxPro based application being served to client machines via Citrix XenApp. This problem is rare and intermittent (1-2 times a day over hundreds of clients).

Also, SQL Server and Citrix are running on a virtualized server infrastructure.

TL;DR: How do I troubleshoot intermittent database connectivity loss?

Update: This is the message in the application error log when the problem happens:

Connectivity error: [Microsoft][ODBC SQL Server Driver]Communication link failure

The only time I've seen that message before is when SQL Server was completely overloaded and when the actual network connection itself was having problems (disconnected). I don't think either of those two things are the case in this instance.


texts: How to replace a CLOB column by a varchar2(4000) column?

texts:
The following code seems to do the job, but I doubt, that it is robust enough.

SET serveroutput on

DECLARE
    v_isclob PLS_INTEGER;
    v_cmd    VARCHAR2(4000);
BEGIN
    SELECT CASE when exists (select * from USER_TAB_COLUMNS where COLUMN_NAME = 'Column_to_modify' AND TABLE_NAME = 'MyTable' AND DATA_TYPE = 'CLOB') then 1 else 0 end into v_isclob FROM DUAL;
    IF v_isclob > 0 THEN
        BEGIN
            EXECUTE IMMEDIATE 'ALTER TABLE MyTable RENAME COLUMN Column_to_modify  TO XXX';

            EXECUTE IMMEDIATE 'ALTER TABLE MyTable ADD Column_to_modify varchar2(4000)';

            EXECUTE IMMEDIATE 'UPDATE MyTable SET Column_to_modify = SUBSTR(XXX, 1, 4000)';

            EXECUTE IMMEDIATE 'ALTER TABLE MyTable DROP COLUMN XXX';
            dbms_output.put_line ('++    Column_to_modify modified to varchar2(4000)');
        EXCEPTION WHEN OTHERS THen
            dbms_output.put_line ('##### Error while modifying Column_to_modify');
        END;
    else
        dbms_output.put_line ('     Column_to_modify is varchar(4000)');
    END IF;
END;
/

How can I improve the code?


texts: Postgres : Truncate if exists in psql function with parameter

texts:
I am trying to get a psql function that would truncate a given table name if it exists. I've been trying multiple functions, but none of them worked so far. Here is the code :

CREATE OR REPLACE FUNCTION truncateIfExists(tableName TEXT)
returns void
as $$
BEGIN
EXECUTE format(
'IF EXISTS (
    SELECT *
    FROM information_schema.tables
    WHERE table_name =' || tableName || '
    )
THEN
TRUNCATE tableName;
END IF;
');
END;
$$language plpgsql

Now, I am able to get it to work in a simple procedure with a harcoded name :

do $$
begin
IF EXISTS (SELECT *
 FROM information_schema.tables
 WHERE table_name = genre_epf)
 THEN
 TRUNCATE genre_epf;
END IF;
end
$$;

But I can't wrap my head on how to mix both queries. What am I doing wrong here ?


texts: Converting huge ASCII file to flat and convert

texts:
I am not a db administrator but I thought to ask this question here.

I have several very large ASCII files (3-5GB each) that I need to extract the data and then sort it.  The instructions say to "extract the zip file into a flat file" and then to "create a program to convert that flat file to whatever application the client chooses to use".

I am not sure if this is something that is simple to do, or if I should hire a database pro to do this.  I am familiar with the basic workings of SQL but maybe enough to get into trouble.

So far I have used something called gVim to look at the files, but they are not delimited in any way except for spaces.

Could someone give me advice on this?
Thanks!


texts: Stored Procedure on a read-only AlwaysOn Availability Group replica

texts: We have a read-only reporting server that is part of the AlwaysOn availability group.   Our developer wants to create some stored procedures. My question is where should the stored procedure be created--will that be on the primary since the secondary is read only ?


texts: Postgresql: How to take incremental backup with Barman

texts:
I am new to Postgresql, I've found a tool named Barman for taking incremental backup of database. But the few tutorials aren't helping that much as they're not for naive users.

Can someone help explaining the steps simply how to backup data with Barman? That would be a great help ! I'm using Postgres 10.4 in Ubuntu 18.04.


texts:
It's very simple package, running fine when executed via visual studio. From the report I get:

Visual Studio 2019 Community
SQL2019CTP3.1

In the connection manager I am using windows authentication. The package has the following structure:

OLE DB source - simple T-SQL statement
OLE DB destination - create table and insert the result of the statement

Can anyone tell what can cause this error? I find some topics telling this is a security issue, but not sure what kind of access I need to grant and to whom.

As I said, when I hit the execute button in the context of Visual studio the package and execute, a table is created in the destination database and records are inserted.


texts: Is it safe to use an output (to_char) date format string of more than 22 characters (ORACLE 11)

texts:
The ORACLE SQL manual (Page 2-58 of the main 11.1 SQL Manual) states:

  The total length of a datetime format model cannot exceed 22 characters.

However, I've been able to use a format string for TO_CHAR with many more characters, almost all of which are text "literals".

Is this safe (ORACLE 11)? (Perhaps the limit does not include non-date literals?)

For example:

SELECT TO_CHAR(sysdate,
       '"SOMESTRING_"YYYYMMDD"_SOME LOTS MORE STRINGS OF A FILENAME PREFIX"')
FROM DUAL;

Result:

SOMESTRING_20200226_SOME LOTS MORE STRINGS OF A FILENAME PREFIX

It also works with dates from tables.

The reason I want to do this is to build a filename template in a database column; otherwise I will need to put the date format in a separate field.

P.S. Please tell me if this should be asked in a different community. I've never asked a question before, so I have no reputation to lose :-). Be nice.


texts: User Rights - Can alter Sp's but not tables

texts: How can i make user permissions so that user can CREATE\ALTER\DELETE Store Procedures, but cannot CREATE\ALTER\DELETE tables in database.


texts: How can I enable logging for a single function in PostgreSQL?

texts:
I want to log the whole function runtime for a single particular function in PostgreSQL? Is this possible?

The value of log_min_duration_statement = 3000 in postgresql.conf and I've tried to do:
ALTER FUNCTION mychema.myfunc(json) SET log_min_duration_statement = 100, however I don't see any logs in /var/log/postgresql/postgresql-9.x-main.log

If the the log_min_duration_statement of the function is less than the log_min_duration_statement in the postgresql.conf does the function runtime get logged?

Can I add an exception somewhere to tell PostgreSQL that I want my function to get logged regardless if the global log_min_duration_statement value is < the log_min_duration_statement of the function?

I want the log_min_duration_statement of the function to take precedence over the global config value to see logs for that one function I'm interested in, not all the others, because enabling logging for all requests adds a significant overhead.


texts:
I need to know to correct order between these following steps, it's hard to find information on the internet on what happens first, i'd be really grateful if someone could give me the answer

A. Oracle searches for a init.ora to determine the configuration
       parameters

B. The proces SMON sometimes executes an instance-recovery

C. memory for the SGA gets allocated and the  background processes that are necessairy get started

D. Oracle searches the database files and redo log files

E. oracle starts an instance

F. oracle searches for the control files with the parameter CONTROL_FILES


texts: Why does MySQL choose this execution plan?

texts:
I have two queries,

select some_other_column
from `table`
order by primary_index_column asc
limit 4000000, 10;

and

select some_other_column
from `table`
order by secondary_index_column asc
limit 4000000, 10;

Both return 10 rows; the first takes 2.74 seconds, and the second takes 7.07 seconds.  some_other_column is not a part of any index.  primary_index_column is the primary key column; secondary_index_column has a b-tree index and a cardinality of 200 (according to MySQL).

Here are the explain results:

mysql> explain select some_other_column from `table` order by primary_index_column limit 4000000, 10;
+----+-------------+---------+-------+---------------+---------+---------+------+---------+-------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows    | Extra |
+----+-------------+---------+-------+---------------+---------+---------+------+---------+-------+
|  1 | SIMPLE      | table   | index | NULL          | PRIMARY | 4       | NULL | 4000010 |       |
+----+-------------+---------+-------+---------------+---------+---------+------+---------+-------+

mysql> explain select some_other_column from `table` order by secondary_index_column limit 4000000, 10;
+----+-------------+---------+------+---------------+------+---------+------+---------+----------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows    | Extra          |
+----+-------------+---------+------+---------------+------+---------+------+---------+----------------+
|  1 | SIMPLE      | table   | ALL  | NULL          | NULL | NULL    | NULL | 4642945 | Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+---------+----------------+

Why does MySQL choose that specific execution plan for the second query?   I don't understand why it can use the index for the first query but not for the second query.


texts:
I have a SQL Server 2014 instance with a ~900GB backup database that goes through a weekly restoration process from a full backup. The server it's on is a Windows Server 2012 R2.

I have recently started seeing the database fail to be restored each weekend.

There are 3 backup files that are copied across to the backup site, produced on the Sat evening around 8, taking only about an hour, copied down to this server just before midnight (usually taking 1-2 hours per file) then restored from at 6am the following morning.

I notice one of the backup files consistently has a date modified time of Sun 6:50, but the other two have it as Sat ~9pm. When attempting to restore to a dev server to verify the backup media it takes an exceptionally long time to read the backup device header for that file.

I have been and still currently am sifting through google results to see if I can work out an answer as well, but no luck so far. Does anyone have any suggestions of what I should be checking or ideas on what the problem might be?

The restore job is done from a procedure called by a PowerShell script. This is the email error I received the last Sunday morning for example:

  Sent: 30 October 2016 12:01
  To: email
  Subject: Server1 - Database Restore Failure

  The following database(s) have not restored correctly on Server1 -
  please investigate.

  Database1

The log file of the copy task just shows the following:

  10/30/2016 00:07:35 Copying databases matching *_database1_FULL_*.bak

The log file for restore task shows:

  10/30/2016 08:29:33 Restoring Database1 From \sharedfileserver\folder\AvailabilityGroup1_Database1_FULL_20161029_201228_1.bak
  10/30/2016 08:29:33 Restoring Database2 From \sharedfileserver\folder\AvailabilityGroup1_Database2_FULL_20161029_201026_1.bak
  10/30/2016 08:29:33 5 percent processed.
  (some time elapses...)
  10/30/2016 08:34:19 100 percent processed.
  10/30/2016 08:34:19 Processed 4946928 pages for database 'Database2', file 'DATABASE2' on file 1.
  10/30/2016 08:34:19 Processed 21 pages for database 'Database2', file 'DATABASE2_log' on file 1.
  10/30/2016 08:34:27 RESTORE DATABASE successfully processed 4946949 pages in 303.526 seconds (127.330 MB/sec).
  10/30/2016 08:34:27
  10/30/2016 08:34:27 3013 - RESTORE DATABASE is terminating abnormally.
  10/30/2016 08:34:27 -1
  10/30/2016 08:34:27 Done.
  10/30/2016 08:34:28 Finished execution

Note: It's Database1 that's the problem child.


texts: Assign result of regexp_split_to_array() to Pl/pgSQL array variable

texts:
How do I create an array and then put a result of a function into it?
I'm doing it like this:

array text[];
select regexp_split_to_array('whatever this is it splits into array with spaces', E'\\s+')
    into array;

But obviously it doesn't work, when I try to

raise notice '%', array[1];

It's just not how it's done.


texts: I just upgraded PostgreSQL with yum on Amazon Linux, now PostgreSQL won't start. What do I do?

texts:
I just ran yum update on my Amazon Linux instance as part of an upgrade to 2012.09 and it upgraded PostgreSQL from 9.1 to 9.2. Now PostgreSQL won't start. What do I do?

(This is a very incomplete question because it's just here to provide somewhere to post the answer).


texts: How to read the MySQL Audit.log file in a SQL table?

texts:
I tried to read the Audit.log file of the MySQL Enterprise using a table or other more friendly format and easy to access and understand, but the xml format with which it is written has an irregular structure:

<AUDIT_RECORD>
   <TIMESTAMP>2019-02-17T12:53:23 UTC</TIMESTAMP>
   <RECORD_ID>1_2019-02-17T12:53:23</RECORD_ID>
   <NAME>Audit</NAME>
   <SERVER_ID>1</SERVER_ID>
   <VERSION>1</VERSION>
   <STARTUP_OPTIONS>./mysqld --basedir=/MYSQL_HOME/mysql</STARTUP_OPTIONS>
   <OS_VERSION>x86_64-linux-glibc2.12</OS_VERSION>
   <MYSQL_VERSION>5.6.38-enterprise-commercial-advanced-log</MYSQL_VERSION>
</AUDIT_RECORD>

<AUDIT_RECORD>
   <TIMESTAMP>2019-02-19T02:22:23 UTC</TIMESTAMP>
   <RECORD_ID>2_2019-02-17T12:53:23</RECORD_ID>
   <NAME>Connect</NAME>
   <CONNECTION_ID>460432</CONNECTION_ID>
   <STATUS>0</STATUS>
   <STATUS_CODE>0</STATUS_CODE>
   <USER>nbryan</USER>
   <OS_LOGIN/>
   <HOST/>
   <IP>172.17.3.150</IP>
   <COMMAND_CLASS>connect</COMMAND_CLASS>
   <PRIV_USER>nbryan</PRIV_USER>
   <PROXY_USER/>
   <DB/>
</AUDIT_RECORD>

<AUDIT_RECORD>
   <TIMESTAMP>2019-02-19T02:22:23 UTC</TIMESTAMP>
   <RECORD_ID>3_2019-02-17T12:53:23</RECORD_ID>
   <NAME>Query</NAME>
   <CONNECTION_ID>460432</CONNECTION_ID>
   <STATUS>0</STATUS>
   <STATUS_CODE>0</STATUS_CODE>
   <USER>flopez[nbryan] @  [172.17.3.150]</USER>
   <OS_LOGIN/>
   <HOST/>
   <IP>172.17.3.150</IP>
   <COMMAND_CLASS>set_option</COMMAND_CLASS>
   <SQLTEXT>/*!40101 set @@session.wait_timeout=28800 */</SQLTEXT>
</AUDIT_RECORD>

As you can see, the same tags are not always generated, so I have not been able to format it to a table from the SQL Server (there is a lot of information on how to read xml formats using sql). I wanted to know if there is another way, maybe from the same MySQL, although it is very difficult to find something of this manager in relation to other solutions.

Any help or idea would be very helpful.

Thank you.


texts: What is difference between Lease Timeout and HealthCheck Timeout in AlwaysON?

texts: this question was asked twice here but still there is no satisfied answer. Could someone explain the main difference between these two timeouts?


texts:
yesterday i had several Problems in a Windows Service with a Function that already works and runs several Months without a Problem.
But yesterday i got alot of Errors in my Service. The Errormessages were a Combination of mostly these Twos.
The connection does not support MultipleActiveResultSets.
Connection Timeout Expired.  The timeout period elapsed during the post-login phase.

I did not have changed anything in the past 2 Months and had never Problems with this Service.
While searching the Problem i stumbled over these SQL-Server Log Messages
Parallel redo is shutdown for database '' with worker pool size [1].

Parallel redo is started for database '' with worker pool size [1].

Starting up database ''

Theses Messages appeared also in the Past, periodically they occured.
But yesterday they happened in a extremly high Frequency. They have started around 5 Hours after the Backup Process and lasts for about 7 Hours.
I'll had nearly 300'000 Events. These 3 Messages together occured around 3-4 Times per Second and suddenly they stopped.
Im not sure anymore if they stopped after i ended the Service, but as this Message have stopped to occur in this High Frequency, my Service also had no Problems anymore. Im not sure if these 2 Instances (Sql-Server and Windows Service) were rocking each other up.
I'll kinda understand what leads to this Messages, but not what why it were spammed like this.
Any Ideas on how to prevent this and / or ways of investigating this further?
All the Best Daniel


texts: Safe to Shrink Database in this Scenario?

texts:
For years I've had it drilled in to me:

  Never ever shrink a database file.

I understand why of course, and I never do, mainly because I never need to.

Now though, I have a database with an .mdf file that is 800GB. It was a system designed to collect data over a certain amount of time, with no retention period.

A retention period of 90 days was recently placed on this data, and as such the developer has cleared about 3 years worth of data from the tables. Data and Indexes now total roughly 70GB, so now I have an .mdf file which is grossly over-sized.

I want to shrink it to reclaim some of that valuable disk space.

I'm planning to perform a shrink prior to rebuilding indexes and updating statistics (i.e the weekly maint. plan)

I'm not breaking any DBA laws here am I? I assume this is an acceptable scenario in which to perform a SHRINK as it is a true one off?

Thanks


texts: Retrieving data if control file is lost in oracle Database

texts:
Suppose if the databases control file is lost permanently (no backup).
Can we retrieve the data from data file in Oracle database?


texts: Delete latest data from mongodb collection

texts: I am using MongoDB 4.0. By mistakenly, I insert 100 BTC blocks transaction data in the wrong collection. Now I want to remove only that data which is inserted recently. I am not getting that how much data is inserted. I just know that data is inserted today. Can somebody give me some suggestion how can I remove this data?


texts: How to avoid null values when generating insert statement

texts:
I´m trying to create an insert statement with my database values, to send to my app.

But I don´t know how to avoid values if they are NULL, and if NULL I want to put an empty string, like ''.

My statement is:

CREATE OR REPLACE VIEW atendimentopublico.vw_insert_atendimento_motivo_encerramento AS

SELECT TRANSLATE ('INSERT INTO AtendimentoMotivoEncerramento (Id, Descricao) VALUES' ||  array((SELECT '(''' || ame.amen_id || ''',''' || ame.amen_dsmotivoencerramento || ''')'
FROM atendimentopublico.atendimento_motivo_encerramento ame
WHERE ame.amen_icuso = 1
ORDER BY ame.amen_id))::text,'"{}','');

ALTER TABLE atendimentopublico.vw_insert_atendimento_motivo_encerramento OWNER TO postgres;
GRANT ALL ON TABLE atendimentopublico.vw_insert_atendimento_motivo_encerramento TO postgres;


texts: Recover InnoDB tables with FRM, ibdata1 and log files, but without ibd files (innodb_file_per_table = OFF)

texts:
I have spent three days investigating how to recover my database.    I have the ibdata1, the FRM, and the ib_logfile0 and ib_logfile1 files (It seems I don't have the ibd files since my configuration is as default: innodb_file_per_table = OFF)

The problem is that most of the recommendations on internet speak about the case when you have the ibd files, but, for my case (with the files I have), the possible solutions are downloading SW that is not available.

Please, I really need help to match the data so I can access the info.  I'll appreciate your help.

When I start the mysqld, there is no error messages at mysqld.log.

When it appears is when I try to access one of the InnoDB tables:

  191008 15:17:13 [ERROR] Cannot find or open table DecopBD/acciones
  from the internal data dictionary of InnoDB though the .frm file for
  the table exists. Maybe you have deleted and recreated InnoDB data
  files but have forgotten to delete the corresponding .frm files of
  InnoDB tables, or you have moved .frm files to another database? or,
  the table contains indexes that this version of the engine doesn't
  support. See
  http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html how
  you can resolve the problem.

Note that the DB has MyISAM and InnoDB tables.   The problem is just in the InnoDB.

Thankyou!!
Enrique


texts:
According to the limitations outlined at http://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-limitations.html, there can only be 48 data nodes.

Does that mean, with number of replicas 2, I can actually have only 24 nodes to store data?

If the data memory is, say 4GB, then, does it mean I can only store 24*4=~100GB?

If so, how can I set up a cluster to store TBs of data?


texts: Query on large database much slower on HDD

texts:
I have the following table:

CREATE TABLE `Geo1` (
  `id` int(8) UNSIGNED NOT NULL,
  `nameRef` int(8) DEFAULT NULL,
  `placeRef` mediumint(7) UNSIGNED DEFAULT NULL,
  `category` enum(...) COLLATE utf8_bin DEFAULT NULL,
  `parentRef` mediumint(7) DEFAULT NULL,
  `incidence` int(9) UNSIGNED NOT NULL,
  `percent` decimal(11,9) UNSIGNED DEFAULT NULL,
  `ratio` int(11) NOT NULL,
  `rank` mediumint(7) UNSIGNED DEFAULT NULL,
  `year` smallint(4) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

ALTER TABLE `Geo1`
  ADD PRIMARY KEY (`id`),
  ADD KEY `placeRef_rank` (`placeRef`,`rank`),
  ADD KEY `nameRef_parentRef` (`nameRef`,`parentRef`),
  ADD KEY `nameRef_placeRef` (`nameRef`,`placeRef`),
  ADD KEY `nameRef_category_year` (`nameRef`,`category`,`year`) USING BTREE;
MODIFY `id` int(8) UNSIGNED NOT NULL AUTO_INCREMENT;

The table is 55GB in total, 36GB of indexes.

On my local server (which uses SSD) it can execute the following query in 0.001 seconds:

SELECT SQL_NO_CACHE placeRef, incidence FROM Geo1
    WHERE nameRef = 1
      AND category = "country";

However on a remote server (which uses HDD) it takes about 0.1-0.6 seconds. This server has 32GB RAM. Here is the mysql.cnf.

The EXPLAIN result on both servers are identical.

Any ideas as to why this is so slow?


texts: How to Insert all PDF files from a folder into a varbinary(max) column in SQL using a Query

texts:
I am trying to create a query script for SQL Server that will convert pdf file into varbinary data and store it into the SQL Table column which is varbinary(max). I also have to make sure it loops so it can do that for the next pdf file in that folder. This folder will only contain pdf files.

DECLARE @pdf VARBINARY(MAX)

SELECT @pdf = BulkColumn
FROM OPENROWSET(BULK N'C:\Users\......\YourFile.pdf', SINGLE_BLOB) AS Document;

SELECT @pdf, DATALENGTH(@pdf)

INSERT INTO dbo.YourTable(PDFContents) VALUES(@Pdf)
GO

How to put .pdf file contents into varbinary(max) colunm


texts: Scheduled Database Backup job fails due to lack of disk space

texts:
Two of my scheduled SQL Server backups failed over the weekend because of a lack of space.

The C: drive has 25.3 GB of free space out of 400 GB. The primary space taker is SQL.

149.9 GB C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP
139.7 GB C:\Program Files\Microsoft SQL Server\MSSQL\DATA

Any suggestions as to how to fix this problem? Please help! I'm a rookie DBA confronted with this issue


texts: Oracle "new or old reference not allowed in table level trigger"

texts:
I have this table

CREATE table Pharmacists (
PhFname varchar2(10),
PhLname varchar2(10),
Salary number(7),
Nationality varchar2(10),
Shift_Start varchar2(2),
Shift_End varchar2(2),
PhID number(7) primary key,
Super_ID number(7) ,
bdate date,
constraint super_fk foreign key (Super_ID) references Pharmacists (PhID) on delete cascade);

I tried to create this trigger

create or replace trigger updShE
after update of Shift_Start on Pharmacists
begin
update Pharmacists
set Shift_End=to_number(:new.Shift_Start, '99') +8
where PhID = :New.PhID ;
end;

but this error appears

  new or old reference not allowed in table level trigger


texts: Override/kill when Code 3/SQLSTATE=57016 Occurs

texts:
I have encountered issues a few times when a process of mine performs a bulk load to a temp table we use for pre-processing purposes. The Bulk load fails for one reason or another (this we are looking into and addressing separately), and then the table that we were bulk loading to gets locked up. Quiesce/Unquiesce and even restarting DB2 are not releasing the lock on this table.

Here is a screenshot of the error in question that we receive every time:

This error code means The table is in the "Load Pending" state. A previous LOAD attempt on this table resulted in failure. No access to the table is allowed until the LOAD operation is restarted or terminated.

Is there any command or way to circumvent this or regain access to this specific table? We have many other processes running when this bulk load runs so restarting DB2 is a very undesirable option and, in our case right now, has not freed up the table.

Apologies if this is not enough information to go off of, please let me know what else I should provide if needed.