texts: Migrating Access 97 to Sql Server - Version Error

texts:
I have Sql Server Migration Assistant for Access v2.7, running on Windows Server 2016. On the same server I have 64bit version of Access 2016 and a 32bit version (I assume) of Access 97.

I am able to migrate Access 2007.accdb files on this server. However, when I click add database and choose an Access 97.mdb file I get the following error:

"Cannot open a database created with a previous version of your application."

Is it possible to migrate (directly) from Access 97 using this tool?

The following link suggested it should be:
https://docs.microsoft.com/en-us/sql/ssma/access/sql-server-migration-assistant-for-access-accesstosql

Microsoft SQL Server Migration Assistant (SSMA) for Access is a tool for migrating databases from Microsoft Access versions 97 through 2010 to Microsoft SQL Server 2005 / Microsoft SQL Server 2008 / Microsoft SQL Server 2012 / Microsoft SQL Server 2014 / Microsoft SQL Server 2016 / Microsoft SQL Server 2017


texts: PostgreSQL table relationship clarification

texts:
I am in the process of setting up table relationships, using a relational diagram provided by our vendor, in PostgreSQL 9.5.3 and am seeking clarification on how to relate a child table that serves two parent tables using the same column. I am using the same PostgreSQL version and database on both Windows 10 Desktop (development) and Amazon RDS (production).

I had no problems using the format

ALTER TABLE schema.childTable
  ADD CONSTRAINT foreignKey_name
  FOREIGN KEY (cColumn)
  REFERENCES schema.parentTable (pColumn);

to establish many-to-one table relationships.

As mentioned above, the problem I have has to do with establishing foreign keys for a child table that serves two parent tables. When I try that using the above formula to set up foreign keys for each of the tables I get an error from PostgreSQL stating the key cannot be generated because values in the child table are missing from the parent table and constitute a foreign key constraint violation. A simple example of the structure is the following excerpt from the relational diagram:

I checked and there are values for both WA and WL in both WXEA and WXNM.

The diagram takes into account the potential for missing values in the parent tables by displaying many (non mandatory) to one (non mandatory) relations between the tables. My understanding is lines in relational diagrams represent foreign keys, but that obviously does not work as there are no inner/outer/full foreign keys.

What I am wondering is the following: Are the lines in the above image actually joins (I thought joins were a more ad hoc query mechanism) or does the diagram assume the DBA will create two intermediate tables from the child, with each intermediate table serving as the child to one of the parent tables?


texts:
I get an error with this query, how can "group by" column by codart and sum only the column Quantita:

Query:

SELECT
    Articolo.CodArt, Articolo.Prezzo,
    DistintaSemplice.Quantita,
    ROUND((DistintaSemplice.Quantita * Articolo.Prezzo), 2) as Totale
FROM
    [Fatture].[dbo].[SottoCapitolo]
INNER JOIN
    Fatture.dbo.Distinta ON Distinta.IdDistinta = SottoCapitolo.Codice
INNER JOIN
    Fatture.dbo.DistintaSemplice ON DistintaSemplice.IdDistinta = Distinta.IdDistinta
INNER JOIN
    Fatture.dbo.Articolo ON Articolo.IdArticolo = DistintaSemplice.IdArticolo
WHERE
    SottoCapitolo.Tipo = 'Distinta'
ORDER BY
    CodArt


texts: Are there any third party tools that can read and/or analyze SQL Profiler trace (.trc) files

texts: I have a .trc file from a trace that a DBA did on one of my databases. I don't have the SQL profiler tool installed on my PC so I can't view the contents and analyze the trace log. How do I read this file without SQL profiler installed on my PC?


texts:
I have a FULLTEXT search on the database, with the WHERE IN condition which checks that search results exists in another table. The query takes more than 1 hour to complete (companies table has 67k rows, and employees table has 82k rows).

Desired result: get search results for "company ABC" in table A only if we have employees data for that company in table B.

The query I have is:

SELECT *,
        MATCH (name, keywords, description) AGAINST ('+searchKeyword*') as relevance,
        (SELECT COUNT(customerID) FROM customers WHERE MATCH (name, keywords, description) AGAINST ('+searchKeyword*' IN BOOLEAN MODE) AND customerID IN(SELECT customerID FROM customersPersonelData)) as totalResults
FROM customers
WHERE MATCH (name, keywords, description) AGAINST ('+searchKeyword*' IN BOOLEAN MODE)
    AND customerID IN(SELECT customerID FROM customersPersonelData)
ORDER BY relevance DESC, name ASC
LIMIT 0,10

For testing I removed "relevance" and "totalResults" subqueries, and this query then takes 72 seconds:

SELECT *
FROM customers
WHERE MATCH (name, keywords, description) AGAINST ('+searchKeyword*' IN BOOLEAN MODE)
    AND customerID IN(SELECT customerID FROM customersPersonelData)
ORDER BY name ASC
LIMIT 0,10

Query explanation:

I tried using EXIST and JOIN, the result is the same.
How can I speed this query up?


texts: Which SQL Server Agent job is processing my tabular model database

texts:
I have a server called SANDBOX with two SQL 2014 instances SANDBOX\00 and SANDBOX\01. It also has a tabular model instance SANDBOX\TABULAR. Agent jobs are disabled on SANDBOX\01. There is no agent (as far as I can see) on SANDBOX\TABULAR.

There is a tabular model database on SANDBOX\TABULAR that is being refreshed every two hours, but for the life of me, I can't locate the agent job that is running this process. I've checked, and as far as I can tell there is no agent job on the source or destination server that would be refreshing the model.

The source data is on a different server. I ran a profiler trace on that database to get that ApplicationName that was pulling the data, but it just returned NULL. It returns the HostName (SANDBOX) of the destination server (but without any instance details).

I ran a profiler trace on the destination database (the tabular model db) and it did show the refresh happening, but in ApplicationName it did not show the name of the agent job that was initiating it. This trace captured the ClientHostName (10.XXX.XXX.XXX:XXXXX). Is this something I can use?

Thanks in advance.


texts: SSMS - How to do case-insensitive searches in Object Explorer

texts:
Preface: I am not referring to queries.

There are numerous places in SSMS that allow for filtering, such as the Object Explorer and Profiler, but these all treat filters as case-sensitive with no visible option otherwise, so if you search for contains 'ASDF' then values like "ASDF_MyEntity" are included but "asdf_MyEntity" are omitted.

For example, we have a lot of SQL Agent jobs on a large server, and I'm trying to use the Object Explorer to filter them by project name, which we always prefix the job names with. However, these appear as both uppercase and lowercase variants.

Another use case is searching for related stored procedure to a module among thousands of entries. If one is named inconsistently (e.g. PascalCase versus camelCase), the filtered search will omit it. This seems like quite an unnecessary hazard of phantoms for debugging.

Furthermore, the sorting in the Object Explorer places uppercase variants before lowercase (so uppercase Z comes before lowercase a) for every type of entity (e.g. table names, stored procedures, job names, etc.), so I either have to scroll around a lot, or check two different filters (or more if it's not all uppercase or all lowercase letters — 2len(name) times to be precise).

I realize I can just query jobs, tables, and other entities manually, but doing this is nonsensical given that SSMS exists, as its name is "SQL Server Management Studio."

Is there something I can do to make SSMS (and SQL Server Profiler) ignore case like every other Windows application? Perhaps I can change a collation setting locally?

Also, why was this decision made by Microsoft when implementing SSMS? I have found it to be only detrimental.

Furthermore, the loading splash screen for SSMS says "built on Visual Studio" which ignores case in Solution Explorer.

P.S. Using SSMS 2014 (version 12.0.5214.6). I cannot make any server-side changes, and my local development environment should match the target server environment for testing purposes.


texts: Determine cause of "MySQL server has gone away"

texts:
I've got a long running process on a headless server. It is a C program that monitors a modem for Caller Id and hangs up on telemarketers. On startup the program opens a connection to a MariaDB 10.3 database on the local machine.

Each morning at 4:00 AM a separate C program fetches the latest data file from the FTC. It includes about 100,000 complaints from the previous day. I add them to the database, so a blacklist table adds 50,000 to 60,000 rows. The current table has about 600,000 rows.

At 9:00 AM when the calls start my queries fail with the message shown below. It is a debugging trace of the C program. It includes Error: MySQL server has gone away.

I checked dmesg and /var/log but there is no indication of the reason or the problem. I can't find it logged anywhere. It also appears the OOM killer did not kill the MariaDB server.

# cat /var/log/mariadb/mariadb.log
#
# dmesg | grep -iE 'maria|mysql|oom'
#

How can I begin to troubleshoot this problem?

I can find lots of posts regarding MySQL server has gone away (apparently this is a chronic problem that has never been fixed). For example, here and here. They are dev-2-dev "try this" answers and lack troubleshooting to determine the root cause.

Debug: ===== new call =====
Debug: ring count: 1
Debug: modem_read
Debug: Read 68:
"DATE = 0308
TIME = 0936
NMBR = 4103174745
NAME = SPAM? PASADENA"
Debug: Ring: 0, Caller Id: 1
Debug: call_read_name
Debug: name: SPAM? PASADENA
Debug: call_read_number
Debug: Number: 4103174745
Debug: database_number_is_whitelist
Error: Failed to prepare statement, Error: MySQL server has gone away
Info: 4103174745 not found in whitelist
Debug: database_number_is_blacklist_lcl
Error: Failed to prepare statement, Error: MySQL server has gone away
Info: 4103174745 not found in local blacklist
Debug: database_number_is_blacklist_ftc
Error: Failed to prepare statement, Error: MySQL server has gone away
Info: 4103174745 not found in FTC blacklist
...


texts: Advantages of a hierarchical schema for private chat

texts:
This post claims that

  All modern chatting interfaces, without exception, implement a
  hierarchical and a not-chronological schema for chat.

It also links another post which gives an example schema for such an interface. Basically, each message contains a reference to the message it replies to (the parent). In order to display a conversation, those references are traversed recursively, starting from messages without a parent.

But I don't see how this is any better than simply sorting the messages by the time they were sent. Since one person may send multiple messages in a row, this doesn't save me from identifying the author (even if it's just a boolean). Can somebody explain the advantages?


texts: High write latency on tempdb datafiles only

texts:
I'm experiencing large write latency on our tempdb database, but on no other database.
(from sys.dm_io_virtual_file_stats) Tempdb log file latency is also listed as being below 5ms.

For comparison:
Write latency on all databases except tempdb are below 5ms, tempdb was about 500ms when using 8 datafiles. I removed 4 datafiles 2 days ago just to check if something changes and it looks like the latency doubled after doing this.

A few notes on our environment:

SQL Server 2008 R2 SP3 VM running on Hyper-V
one large RAID10 array attached to our Hyper-V hosts via fibre channel where everything is stored

I have no explanation for this and I don't know where to look for answers.
I could of course assume that doubling the data files will half the WriteLatency but I don't know if this is the way to go.

Any ideas on where to start?


texts: Mysql output to csv putting column names on the bottom

texts:
I'm trying to export the results of the query listed below to csv and have the column names in the first row. However, when I run the code and open up the csv the header is listed as the last row.  I have no idea why.  What am I missing?

SELECT 'id','state','First_Name', 'Last_Name', 'Phone','County'
UNION ALL
SELECT id, state, First_Name, Last_Name, Phone, County
FROM tx.students
GROUP BY First_Name, Last_Name, Phone,
HAVING COUNT(*)>1
ORDER BY id
INTO OUTFILE '/var/lib/mysql-files/_students_dupes_test3.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';


texts:
To consider a table with millions records and the table schema:

CREATE TABLE `foos` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `foo` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `foo_UNIQUE` (`foo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Ton of this query executes every second

SELECT 1 FROM foos WHERE foo=?

If no record finds, this query will be executed:

INSERT INTO foos(foo) VALUES(?)

The average length of foo less than 20. To optimize performance, a hashing field is considered to add with md5(foo) and drop the unique key.

ALTER TABLE foos DROP INDEX foo_UNIQUE;
ALTER TABLE foos ADD INDEX `foo_IDX` (`hash`);

And the query will be changed as:

SELECT 1 FROM foos WHERE hash=?

If no record finds, this query will be executed:

INSERT INTO foos(foo, hash) VALUES(?, ?)

The Question is:  Will the SELECT query run faster, since average length of foo less than 20 but hashing field length is always 32?


texts:
MySQL database has been problem free for a few months. Today when I checked for upgradeable packages using the Synaptic Package Manager it came up with the various MySQL components (-common,  -client, -server, etc). I therefore decided to upgrade (I forgot to note the installed version but think it was 5.6.21) to 5.6.25-1-ubuntu2.0.Afterwards when I went to query the database I got the message:-

Error occured: Can't connect to MySQL server on '127.0.0.1' (111)

I then found the server was not running. In the log file I fond that the message:-

Can't create file /var/lib/mysql/user.lower-test

I have spent an hour or so searching on line and although the issue has been reported previously I have not seen it in the context of a 'post-update' issue so unsure of my next step.


texts: Does MySQL or MariaDB have a master option to strictly require TLS for ALL connections, no matter what?

texts:
When setting up new MySQL or MariaDB servers, I am much more concerned about security than performance.  The MySQL and MariaDB documentation use the words "allow" and "permit" around all the SSL options, where I would much rather see "require".

I am looking for something like, "mysqld --ssl-mode=REQUIRE" but that apparently doesn't work [anymore?] because the logs say that's a bad option.  I am not looking to do this per-user, I'm looking to do it for the whole system, no matter what.

I should point out that all of my certificates are working fine, and I've verified that all of that works.  It's just that now, I would like to REQUIRE it be used, always, by everyone, period, strictly, no exceptions, no, not even that one, yes really.  Remote?  Encrypt.  Localhost?  Encrypt. Root?  Encrypt.  Somebody else?  Encrypt.  In short: connected to anything at all for any reason?  Encrypt.

Does anybody know of a master switch to require this all the time, period?

Again, I am not looking for per-user options-- that's exactly what I'm hoping to avoid.  This is for an embedded device that may be in service for an extended period of time, and the less branching logic, the better.  "Always" tends to have fewer edge cases.

Thanks!


texts:
What is the most efficient way to lock a specific row for within a mysql transaction.
I am currently using for update using select is there another method that im unaware of?

if not are mysql locks only released when committed or rolled back?

for example i run the following transaction:
begin
select for update
insert..
insert..
update(locked table)
select another table
commit
does the commit release the lock or the update?


texts: MS SQL Management Studio - Script of INSERT statements leaves empty chars for shorter than limit NVARCHAR?

texts:
I have just observed in MS Management Studio that if we want to get the script for table's inserted values the output includes the unused free data space which is in excess for the e.g. NVARCHAR data type. As you can see on the below screenshot, the student names are up to 5 characters which should most equal 10 bytes. However, MS Management Studio has allocated the maximum size to the field as lots of scroll to the right is needed to see the whole insert statement...?

Please advise - is there a way to prevent this?

edit: Please find the advanced scripting options selection from MS Server Management Studio on the below screenshots:

Thanks much in advance!

CREATE TABLE [dbo].[Students](
    [StudentID] [int] NOT NULL,
    [StudentName] [nvarchar](100) NOT NULL,
    [StudentSurname] [nvarchar](100) NOT NULL,
    [Biology] [decimal](3, 2) NOT NULL,
    [Maths] [decimal](3, 2) NOT NULL,
    [Geography] [decimal](3, 2) NOT NULL,
 CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED
(
    [StudentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[Students] ([StudentID], [StudentName], [StudentSurname], [Biology], [Maths], [Geography]) VALUES (1, N'Mike                                                                                ', N'Manson                                                                                ', CAST(2.00 AS Decimal(3, 2)), CAST(2.00 AS Decimal(3, 2)), CAST(1.00 AS Decimal(3, 2)))
GO
INSERT [dbo].[Students] ([StudentID], [StudentName], [StudentSurname], [Biology], [Maths], [Geography]) VALUES (2, N'Timo                                                                                ', N'Torn                                                                                ', CAST(1.00 AS Decimal(3, 2)), CAST(2.00 AS Decimal(3, 2)), CAST(4.00 AS Decimal(3, 2)))
GO
INSERT [dbo].[Students] ([StudentID], [StudentName], [StudentSurname], [Biology], [Maths], [Geography]) VALUES (3, N'Jeffrey                                                                                ', N'Jones                                                                                ', CAST(1.00 AS Decimal(3, 2)), CAST(3.00 AS Decimal(3, 2)), CAST(4.00 AS Decimal(3, 2)))
GO


texts:
I have created an Oracle Database Docker Image with the help of this link

SYSTIMESTAMP shows UTC time and not my local machine's time.

kaushik:~ knayak$ date
Fri Jun  8 17:46:35 IST 2018

select current_timestamp,systimestamp from dual;

CURRENT_TIMESTAMP                           SYSTIMESTAMP
------------------------------------------- ----------------------------------
08-06-18 05:47:51.742216000 PM ASIA/KOLKATA 08-06-18 12:17:51.742211000 PM GMT

How can I change the container's time to match with my time zone?


texts:
In my company we have implemented a distributed cluster using the CitusData extension, the virtual machines we've using are located on a privative virtual server provider. About two or three weeks ago we have noticed that one of the nodes where extremely slow (when doing queries to the master node), and the cores at full capacity. So the first move where to check the processes load and this was what we got issuing (only a few entries shown):

top

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
20398 postgres  20   0  462040   5088    836 S  10.3  0.1  20:00.38 mule
23139 postgres  20   0  462040   5156    864 S  10.3  0.1 303:25.07 mule
 3361 postgres  20   0  462040   5056    784 S  10.0  0.1  52:28.02 mule
13159 postgres  20   0  462040   5076    772 S  10.0  0.1 113:03.39 mule
16580 postgres  20   0  396504   4792    796 S  10.0  0.1  27:28.25 mule
32372 postgres  20   0  265432   5144    920 S   9.6  0.1   0:03.29 mule
19135 postgres  20   0  462040   5120    840 S   9.3  0.1 423:10.94 mule
24734 postgres  20   0  462040   4984    876 S   9.3  0.1  73:49.95 mule
28094 postgres  20   0  330968   5144    904 S   9.3  0.1   6:20.76 mule
 9255 postgres  20   0  462040   4752    696 S   9.0  0.1 130:48.49 mule
.....
.....

ps aux | grep mule

postgres  1365  5.0  0.1 396504  4904 ?        Ssl  13:04   8:12 /tmp/mule
postgres  1896 11.8  0.1 462040  5028 ?        Ssl  Nov24 262:02 /tmp/mule
postgres  2377  5.6  0.1 462040  4988 ?        Ssl  04:03  39:43 /tmp/mule
postgres  3361  8.2  0.1 462040  5124 ?        Ssl  Nov24 141:37 /tmp/mule
postgres  3629  6.6  0.1 462040  4916 ?        Ssl  Nov24  82:51 /tmp/mule
postgres  4692  4.9  0.1 330968  4996 ?        Ssl  14:04   5:00 /tmp/mule
postgres  5577 11.1  0.1 462040  5044 ?        Ssl  Nov24 240:06 /tmp/mule
postgres  5697  5.5  0.1 462040  5092 ?        Ssl  05:03  35:19 /tmp/mule
postgres  7288  6.5  0.1 462040  5180 ?        Ssl  Nov24  77:43 /tmp/mule
postgres  8111  4.9  0.1 265432  4912 ?        Ssl  15:04   2:03 /tmp/mule
postgres  8675  7.9  0.1 462040  4892 ?        Ssl  Nov24 132:40 /tmp/mule
.....
.....

htop screenshot

We restarted that node and all went back to normal, but a few days later, the same ~160 mule processes where initiated by the postgres user. This happends only in one particular node. All of them are replicas (2cores and 8gb ram each) so all of them run postgresql-9.6.5 and CitusData_7.1 over CentOS 7.

We've done some research but with no luck and we can't find any docs or issues regarding these processes, only documentation about MuleSoft, but we never installed anything of such.

We also contacted the CitusData developers and they also have no idea what that process is. We've been using postgresql for quite a while but never seeing this. Is this any kind of malware ?. Feel free to ask for more details.

UPDATE

Fixed information: postgresql server version -> 9.6 to 9.6.5

select version();

                                             version
----------------------------------------------------------------------------------------------------------
PostgreSQL 9.6.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit


texts:
What is the use of self-referencing tables ?

I came across the concept lately and I cannot find any simple example of it (every links on Google explain how to use it with many frameworks).


texts: What does it mean if the spt_monitor - table in SQL Server is empty?

texts:
What does it mean for my environment if I find that the spt_monitor table in SQL Server is empty?

What is this table, how is it used and what populates it?

I am using SQL Server 2005.

My spt_monitor - table is not empty.

I just asked if it were empty....?


texts: Creating an Oracle 11.2-Compatible Database in Oracle 12.2.0.1

texts:
I am using an Oracle Docker image of 12.2.0.1 in my development environment:

https://github.com/oracle/docker-images/tree/master/OracleDatabase/SingleInstance/dockerfiles/12.2.0.1

There does not seem to be an equivalent of this for 11.2.0.4. The database where my code will be deployed into production is 11.2.0.4, so I need to work with compatible=11.2.0.4.

I have tried many times and many ways to set compatible=11.2.0.4 but I cannot get it to work. It seems that, no matter what, due to the built in templates or whatever, I get an error that 12.2 control files are not compatible with 11.2.0.4.

The first method I tried was to set info.ora parameters prior to the first call to dbca and to use dbca responses (in an rsp file) to include compatible=11.2.0.4. This resulted in an error because the dbc template has control files that are incompatible (12.1).

I tried using a dbt template instead, so that control files would not be copied, but the dbca command seems to have created 12.2 control files even though I was requesting 11.2 compatibility.

  Is it possible to create a CDB or PDB with 11.2.0.4 compatibility
  using 12.2.0.1? Currently the question is whether or not it's even
  possible. If it is possible, some high-level guidance as to how to go
  about it would be much appreciated.

Unfortunately I did not save records from yesterday's 10-hour session. For now, knowing whether or not it is even possible to create an older-versioned database will suffice.

Here's what detail I can provide...

The most frequently encountered error was

ORA-00201: control file version 12.2.0.0.0 incompatible with ORACLE version 11.2.0.4.0
ORA-00202: control file: '/opt/oracle/cfgtoollogs/dbca/SIDGNRIC/tempControl.ctl'

This error occurs when my dbca responses included:
responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v11.2.0
initParams=audit_trail=none,audit_sys_operations=false,compatible=11.2.0.4
templateName=General_Purpose.dbc

I also tried changing to templateName=New_Database.dbt with the same result.


texts: I have been using SQL Server BPA for getting good information from Microsoft. I was using its 2005 version in which I used to export the results in CSV format but recently I got two new servers which has got SQL Server 2008 R2 installed on it and I know I can't run BPA 2005 on these, so chose the R2 version of it, but it doesn't have an option to save the report on CSV format, only XML, I have tried using Excel to convert it into CSV but no use, even-though it display the details but can't narrow down the results any idea of converting the results to CSV format?


texts: AWS RDS MariaDB replication to GCP Cloud SQL or Azure SQL DB

texts:
For disaster recovery, I need to replicate an Amazon Multi-AZ RDS instance currently using MariaDB to a different platform like Google Cloud Platform or Azure. If AWS is down for more than 30 minutes, I want to point my apps over to the other platform.

I know replication could more easily be achieved with an EC2 database, but if it's possible to use RDS I would stick with it.

How can RDS replicate to another cloud platform?


texts: Problem creating my first database , annoying error

texts:
I created two tables:

person (id as pk , person and purchase)
user (id as pk , other columns not relevant to question).

After linking the two with an n:m relationship, and proceeding to forward engineer (I'm using MySql Workbench), I get this error:

  Error:  Executing SQL script in server

  ERROR: Error 1064: You have an
  error in your SQL syntax; check the manual that corresponds to your
  MariaDB server version for the right syntax to use near ' INDEX
  fk_table1_has_user_table1_idx (table1_id ASC) VISIBLE, CONSTRAINT' at
  line 8

Note: I'm following a tutorial. As far as I can tell, my code's the exact same as what's in the tutorial, and the tutorial guy didn't get an error.

SQL Code:

-- Table mydb.table1_has_user --
-----------------------------------------------------
 CREATE TABLE IF NOT EXISTS mydb.table1_has_user
      ( table1_id INT NOT NULL,
        user_user id INT NOT NULL,
        PRIMARY KEY (table1_id, user_user id),
        INDEX fk_table1_has_user_user1_idx (user_user id ASC) VISIBLE,
        INDEX fk_table1_has_user_table1_idx (table1_id ASC) VISIBLE,
        CONSTRAINT fk_table1_has_user_table1 FOREIGN KEY (table1_id)
            REFERENCES mydb.table1 (id) ON DELETE NO ACTION ON UPDATE NO ACTION,
        CONSTRAINT fk_table1_has_user_user1 FOREIGN KEY (user_user id)
            REFERENCES mydb.user (user id) ON DELETE NO ACTION ON UPDATE NO ACTION
     ) ENGINE = InnoDB

SQL script execution finished: statements: 7 succeeded, 1 failed

Fetching back view definitions in final form.
Nothing to fetch

Can someone please help me with this? I don't know what I'm doing wrong , the primary key is correct why is this happening?

This is the diagram of the database I'm trying to create:


texts: Best way to transfer a few tables from reserve server to production

texts:
We have two MS SQL Servers (2012 Standard Edition on Windows 2012 Server) which are located in the same subnet. One is production server. Another is reserve server.

Currently there is a transactional replication between two databases on these servers where production server is Publisher and Distributor and reserve server is Subscriber.

On production server every night runs some tasks which inserts to (or updates) about of 5 tables one of them is about 6 Gb.

We would like to move this tasks to reserve server and create on one another database with the same scheme. Then when tasks upload data to another database we need to synchronize one with production database on production server.

One moment : For supporting actual data we must to include another database on reserve in replication as Subscriber from production server.

As one way we consider linked server:

Truncate table on production (in one table there is FKs, so we can't)
Insert data from another database

Edit: How about linked server and merge functionality?

How do you think are there the better way?


texts: Why would you put Identity Increment other than 1 in SQL?

texts:
In SQL Server, you can set the increment of an identity column other than 1.

In what situation would you do that?


texts:
Two days ago, I had connected to my ClearDB test server with MySQL workbench. I had left this workbench open and when I had checked it today, there were two new schemas named "test_justin" and "test_vishnu". I promptly reset my connection and sure enough, they were gone.

I have since reset the username and password for my database (which should have been secure enough in the first place). My questions are:

Has anyone encountered this as a bug in MySQL workbench or ClearDB?
How would an attacker create a schema when even my user account does not have permissions to do so?
What are some safety precautions I should be taking to ensure my database is not compromised again?


texts: So as part of our college project, I'm working on CENSUS Database. I stumbled upon a problem wherein if an individual is below the age of 18, his/her marital status should only allow 'NA' and anyone above 18 should be allowed to choose from any of the following values ('Married', 'Widowed', 'Single', etc). The information on CONSTRAINTS doesn't seem to be helping. Not finding any way to implement this kind of constraint mechanism, please suggest.


texts: SQL Server 2008 R2 - Limit viewing on database list

texts:
In SQL Server 2008 R2 I have user1, he owns 3 db (db1 , db2, db3) and I want he can see only these 3 db when he login by using SSMS.

I did REVOKE VIEW ANY DATABASE FROM PUBLIC, but then he cannot see any db in list.
How can I fix it?


texts: Strange max(date) return in reader vs writer aurora mysql


texts:
I'm trying to restore a dump (-Fd --data-only) from PostgreSQL v9.4.9, but I've come across the following error:

pg_restore: [archiver] could not find entry for ID 12

Exactly the same happens if pg_dump is invoked using -Fc instead of -Fd.

As per this link, this seems to be a known issue, but I'm not sure of the workaround I should apply to move over it.

Could this actually be overcome in this PostgreSQL version? If so, what should I alter to achieve it?


texts:
i have 2 table one store data of relation user other buy user

path table

+-------------+---------------+-------------+
| ancestor_id | descendant_id | lenght       |
+-------------+---------------+-------------+
|           1 |             1 |           0 |
|           1 |             2 |           1 |
|           1 |             3 |           1 |
|           1 |             4 |           2 |
|           1 |             5 |           3 |
|           1 |             6 |           4 |
|           2 |             2 |           0 |
|           2 |             4 |           1 |
|           2 |             5 |           2 |
|           2 |             6 |           3 |
|           3 |             3 |           0 |
|           4 |             4 |           0 |
|           4 |             5 |           1 |
|           4 |             6 |           2 |
|           5 |             5 |           0 |
|           5 |             6 |           1 |
|           6 |             6 |           0 |
+-------------+---------------+-------------+

this is table buy

+--------+--------+
| userid | amount |
+--------+--------+
|      2 |   2000 |
|      4 |   6000 |
|      6 |   7000 |
|      1 |   7000 |

algoritm =
1- select and group buy for each user at last 1000 for descendant
2- join to path table with descendant_id = result step1
3- again select and group buy for each user at last 1000 for ancestor
4- must get  min path_length Except zero According to join in step 2 with ancestor have at last 1000 , aslo if just have one path_length set to null

this mysql code i need a where compare path_length with have ancestor

SELECT a.*
FROM
    ( SELECT userid
       FROM webineh_user_buys
      GROUP BY userid
      HAVING SUM(amount) >= 1000
    ) AS buys_d

JOIN
    webineh_prefix_nodes_paths AS a
    ON a.descendant_id = buys_d.userid

 JOIN
    (
        SELECT userid
        FROM webineh_user_buys
        GROUP BY userid
        HAVING SUM(amount) >= 1000
    ) AS buys_a on (a.ancestor_id = buys_a.userid )

  JOIN
     ( SELECT descendant_id
            , MAX(path_length) path_length
         FROM webineh_prefix_nodes_paths
         where a.ancestor_id = ancestor_id
        GROUP
           BY descendant_id
     ) b
    ON b.descendant_id = a.descendant_id
   AND b.path_length = a.path_length

 GROUP BY a.descendant_id, a.ancestor_id

Intended result

 +--------+--------++--------++------
| descendant | ancestor | path_length
+--------+--------++--------++-------
|     1      |   null   |    null
|     2      |   1      |      1
|     4      |   2      |      1
|     6      |   4      |      2

but have error please see sqlfidle
Unknown column 'a.ancestor_id' in 'where clause'


texts: How to rewrite slow CTE construction to match speed of temp tables

texts:
I think the general advice of this community is to avoid temp tables in favor of CTEs.  However, I sometimes encounter situations in which CTE constructions are very slow, while their temp table equivalents are very fast.
For example, this spins for hours and never seems to yield results.  The query plan is full of nested loops.
CREATE TABLE #TRIANGLES
(
    NODE_A VARCHAR(22),
    NODE_B VARCHAR(22),
    NODE_C VARCHAR(22)
)
;

INSERT INTO #TRIANGLES VALUES
/*  150,000 ROWS  */
;

CREATE NONCLUSTERED INDEX IDX_A ON #TRIANGLES (NODE_A);

CREATE NONCLUSTERED INDEX IDX_B ON #TRIANGLES (NODE_B);

CREATE NONCLUSTERED INDEX IDX_C ON #TRIANGLES (NODE_C);

WITH
TRIANGLES_FILTERED AS
(
    -- **** FILTERING OF THE TRIANGLE TABLE OCCURS IN A CTE ****
    SELECT   *
    FROM     #TRIANGLES AS T
    WHERE    LEN(T.NODE_A) = 2  AND
             LEN(T.NODE_B) = 2  AND
             LEN(T.NODE_C) = 2
),
CONNECTABLE_NODES AS
(
    SELECT   DISTINCT T1.NODE_C AS [NODE]
    FROM     TRIANGLES_FILTERED AS T1

             INNER JOIN
             TRIANGLES_FILTERED AS T2
             ON T1.NODE_B = T2.NODE_A  AND
                T1.NODE_C = T2.NODE_B

             INNER JOIN
             TRIANGLES_FILTERED AS T3
             ON T2.NODE_B = T3.NODE_A  AND
                T2.NODE_C = T3.NODE_B

    WHERE    T1.NODE_A <> T2.NODE_C  AND
             T1.NODE_A <> T3.NODE_C  AND
             T2.NODE_A <> T3.NODE_C
)
SELECT   *
FROM     #TRIANGLES AS T1
WHERE    T1.NODE_A IN (SELECT * FROM CONNECTABLE_NODES)  AND
         T1.NODE_B IN (SELECT * FROM CONNECTABLE_NODES)  AND
         T1.NODE_C IN (SELECT * FROM CONNECTABLE_NODES)
;

Query plan:
https://www.brentozar.com/pastetheplan/?id=rk_5TaiiP

Whereas, the query plan for this uses hash matches and it runs in a flash:
CREATE TABLE #TRIANGLES
(
    NODE_A VARCHAR(22),
    NODE_B VARCHAR(22),
    NODE_C VARCHAR(22)
)
;

INSERT INTO #TRIANGLES VALUES
/*  150,000 ROWS  */
;

CREATE NONCLUSTERED INDEX IDX_A ON #TRIANGLES (NODE_A);

CREATE NONCLUSTERED INDEX IDX_B ON #TRIANGLES (NODE_B);

CREATE NONCLUSTERED INDEX IDX_C ON #TRIANGLES (NODE_C);

-- **** FILTERING OF THE TRIANGLE TABLE SAVED INTO A TEMP TABLE ****
SELECT   *
INTO     #TRIANGLES_FILTERED
FROM     #TRIANGLES AS T
WHERE    LEN(T.NODE_A) = 2  AND
         LEN(T.NODE_B) = 2  AND
         LEN(T.NODE_C) = 2
;

CREATE NONCLUSTERED INDEX IDX_A ON #TRIANGLES_FILTERED (NODE_A);

CREATE NONCLUSTERED INDEX IDX_B ON #TRIANGLES_FILTERED (NODE_B);

CREATE NONCLUSTERED INDEX IDX_C ON #TRIANGLES_FILTERED (NODE_C);

WITH
CONNECTABLE_NODES AS
(
    SELECT   DISTINCT T1.NODE_C AS [NODE]
    FROM     #TRIANGLES_FILTERED AS T1

             INNER JOIN
             #TRIANGLES_FILTERED AS T2
             ON T1.NODE_B = T2.NODE_A  AND
                T1.NODE_C = T2.NODE_B

             INNER JOIN
             #TRIANGLES_FILTERED AS T3
             ON T2.NODE_B = T3.NODE_A  AND
                T2.NODE_C = T3.NODE_B

    WHERE    T1.NODE_A <> T2.NODE_C  AND
             T1.NODE_A <> T3.NODE_C  AND
             T2.NODE_A <> T3.NODE_C
)
SELECT   *
FROM     #TRIANGLES AS T1
WHERE    T1.NODE_A IN (SELECT * FROM CONNECTABLE_NODES)  AND
         T1.NODE_B IN (SELECT * FROM CONNECTABLE_NODES)  AND
         T1.NODE_C IN (SELECT * FROM CONNECTABLE_NODES)
;

Query plan:
https://www.brentozar.com/pastetheplan/?id=B1cZC6isD
How would I rewrite the first one to be as fast as a second one?

BTW, if you're wondering what all the geometry/topology is about, I needed to know how all the triangles connect with each other in the creation of this puzzle:
https://puzzling.stackexchange.com/questions/105275/dragon-summoning-spell


texts:
Please can somebody explain how the following works:

Scenario:

Two nodes in always on availability group configuration (all databases).

If I have a backup job on node 1 to backup Full and Log backups to a network share, then failover to node 2 and continue the log backups, will this not break the log chain? So each time a failover occurs does a full backup job need to be executed?

Thank you


texts: How does one scale out with master-slave replication?

texts:
To my understanding, a master-slave replication might be set up where clients can read from multiple databases. However, they can only write to the master. I believe this helps guarantee ACIDity.

I also understand one-way replication seems generally preferred to two-way for the same reasons.

I don't quite understand how this would work in terms of application development though.

How do you tell an application to write to one database and read from another? Does this mean this actually has to be programmed into the application layer?

This isn't too big a deal. However, let's say it takes 60 seconds for that write to come back down to the local database. How does the client compensate for latency when he reloads the page and it re-queries the local db (missing that write)? This seems so error-prone!
How would this then scale? Understood that reads dominate a majority of a database's work. But what happens when you scale enough that one server can't handle all the writes? What options do you have?

I'm trying to wrap my head around the subject but I'm missing answers to these basic questions.


texts: Implementing an encrypted table in SQL Azure

texts:
I'm moving an App to Azure and need to achieve PCI compliance for a couple columns in a single table. I realize I can encrypt the data before I store it, but I was wondering if there was a feature in SQL Azure that can manage part of this for me. I've done some reading but I'm not sure what is; or is not; supported in SQL Azure.

I've read about patterns using SQL Server (and not SQL Azure) where data encryption & decryption was applied behind a View. In that case, all clients accessed data via a View and consequently were abstracted from the Encryption/Descryption process. With that said, it seems a bit dangerous.

I'm open to any and all suggestions on how to deal with this situation.


texts: Use MySQL to match input against saved queries

texts:
What I'm trying to do:
Create a database that, instead of generating results from a criteria, matches an input against a database of criteria.

Sometimes we can save our search results and have a site mail us whenever a new item comes up that meets our criteria.

Example:

Bob is interested in blue widgets, red widgets, or widgets with size > 5
Alice is interested in blue widgets or green widgets
Tom is interested in green widgets or widgets size between 7 and 10

Results

If we receive a green widget with size 10, everyone will see
it.
If we receive a red widget with size 3, only Bob will see it.
If we receive a purple widget with size 9, Bob and Tom will see it, but
not Alice.

What I've Done

Created a list of queries and run them each against the records.
Problem with this is that I could end up with tens of thousands of
saved queries (search profiles).
Researched RETE algorithm based solutions such as expert systems.
However, I'd rather have this be saved in a database.

Questions:
Is this possible to solve with mySQL other than saving SQL statements and running them over and over? Is there some feature of mySQL that I'm missing - that could show what saved queries match a particular input?


texts:
I have a single SSIS Package on the server running via SQL Agent Job. Every now an then we are getting deadlocks issue. I used Breant Ozar's sp_BlitzLock to find out the deadlocks history and got below result:

Queries:

    Shared Lock S: Victim
<inputbuf>
UPDATE dbo.Courses
SET
CategoryId = s.CategoryId,
Discriminator = s.Discriminator,
Type = s.Type,
ActiveStatus = s.ActiveStatus,
Name = s.Name,
IsFeatured = s.IsFeatured,
IsMandatory = s.IsMandatory,
DateAdded = s.DateAdded,
DateEdited = s.DateEdited,
IsDeleted = s.isDeleted
FROM dbo.Courses c, staging.Courses s
WHERE c.Id = s.Id   </inputbuf>

Exclusive lock X:
<inputbuf>
    UPDATE dbo.Users
    SET
    DepartmentId = s.DepartmentId,
    IsAdmin = s.IsAdmin,
    IsLearner = s.isLearner,
    IsInstructor = s.isInstructor,
    ActiveStatus = s.activeStatus,
    DateAdded = s.DateAdded,
    DateEdited = s.DateEdited,
    IsDeleted = s.isDeleted
    FROM dbo.Users u, staging.Users s
    WHERE u.Id = s.Id
</inputbuf>

What I'm not understanding is although the queries are updating 2 different tables but one is causing the deadlock to the other.

System: SQL Server 2017

I wonder if someone can shed a little light on this.


texts:
I'm currently managing a Mongo DB instance that has several applications using it. It's been accumulating noTimeout cursors, and, while it's easy enough to cull them if they start getting overwhelming, it's a waste of memory and I'd like to track down the culprit process.

Is there some way to view the origin of noTimeout cursors? Any information, whether it's the original query, the collection it's on, or even the data that's left over, would be immensely useful.

According to these docs, it appears that the only information I can get on the cursors is in db.serverStatus().metrics.cursor and includes only counts of the cursors, not any data about the individual cursors.

Is there a way to get additional information?

My apologies if this isn't appropriate for DBA, it didn't seem like the right kind of question for SO and seemed too dev focused for ServerFault. Please let me know if this is an issue.


texts: In MongoDB it is called write concern, that is the number of the servers in a replica set, that the data should be committed before the cluster acknowledges the write request. How is it called in the Postgres?


texts: Can you time how long an Oracle import takes?

texts:
I'm on a Windows machine with Oracle Client 11.1.0, and am going to import a database dump. I'm importing from the command line with the "imp" command.

Is there an option to time how long the import takes?  I've searched around but haven't found anything for that yet.

Thanks in advance!


texts: I am developing a database with multiple tables. One of its tables will have a total of 50.05GB of data inserted as rows. Do the searching and inserting capability of Sql Server 2008 engine in a table decreases with an increase in total rows in a table? Is there per row data size limit?


texts: One or more subscriptions have been marked inactive. Drop and re-create all subscriptions for this node that are failing with this error

texts:
We have a production server error causing replication agent got failed and subscription became inactive. When I tried to restart synchronizing process, it is getting failed.

Should I delete/drop the current subscription and recreate the subscription again? or does Reinitialize subscription work?
As you can see from the picture, replication from A to C is failed. How can I recteate the subscription of A from C?

Thanks
Subin


texts: ERD - Multiple FKs with only one of them being filled for each record

texts:
I'm designing a database schema for a music platform.

Some of the entities as follows:

track
album
band
artist

A "track" can have an "artist". It can also have a "band" which makes the artist FK null.

Same goes with "album", an "album" can have an "artist" but if the "album" is from a "band" then the artist FK will be null. That's because an "album" by a "band" can't also have a single "artist" as well.

The two(or three) FKs will never be filled together and only one of them must be filled for each record.

Is this a good design practice?

Thanks


texts: Data sync across Local and Cloud Database

texts:
There is a Central Master DB(on Cloud) and there are multiple Locations which have their own DB(lets call the latter - Local DB).

The Local DBs are Single Tenant and contain data for that particular Location alone. All data from Local DBs need to be synced with Central Master DB, i.e, Central Master DB behaves like a Multi-tenant DB with data from all locations.

The web-application load balances between the Central Master DB or Local DB based on availability. Preference is always given to Central Master DB when connectivity is there; if the connectivity to Cloud is not present, the data needs to be written to Local DB which then later needs to be synced with the Central Master DB.

What kind of Replication strategy should be followed so that Master syncs only the data relevant for that LocalDB? In someway, this is similar to ‘filtered replication in a master-master replication setup’ - but am not able to find much about this; and also, am not sure if this is the right approach or not. Or is, replication not a possibility, and only a custom sync needs to be done?

Also, not sure which of MySQL 5.7 and Postgres v11 is better for this kind of use case - this question arises as this use-case is primary and we can choose between either of the databases. Only RDBMS and no NOSQLs are considered.


texts:
I have a GIS system with 40 tables, ranging from 1,000 to 60,000 rows per table. The tables are the system of record for assets in a municipality.

The GIS assets in the tables get integrated to a Workorder Management System (WMS) on a weekly basis. The integration is based on web services that serve up the GIS tables to the WMS.

Constraint #1:

The integration to the Workorder Management System is multi-purpose.

There is a single asset table in the WMS that gets updated, via cron tasks, with any edits that have been made to the GIS assets (new assets, changed assets, and decommissioned assets). Only assets that have been edited are updated in the WMS.
The integration is also used to dynamically serve up the assets to a web map in the WMS (all of the GIS assets are used in the map--not just the assets that have been edited). The map in the WMS connects directly to the GIS web services--it does not use the records in the asset table or the cron tasks.

Constraint #2:

The WMS cron tasks are notoriously slow. Given my organization's infrastructure, my vendor says that the WMS cron tasks will only be able to sync 150 records per minute.

Testing is ongoing, but we have been told to only sync the records that actually need to be synced (edits) due to the significant performance concerns. In other words, we can't just integrate or copy all records, all the time.
To give you an idea, this is what the cron task process looks like:
REST GIS web service >> JSON object >> Parse the JSON into individual records >> Generate XML for each record >> Process the XML records with Java classes >> Insert the records into the database.

Constraint #3:

GIS data is notoriously messy.

In constraint #2, I mentioned that the records get processed with Java classes. The Java classes check for errors (parent/child, field rules, etc.) and flag any records that fail.

These records do not get integrated into the WMS.
It is up to the GIS teams to correct the errors in GIS tables, then we'll try again in the next integration instance (next week) to sync the GIS records to the WMS.

Question:

Given the constraints above, I think I need to figure out a way to integrate all the GIS assets to the WMS (constraint 1.2), but also flag the records that need to be synced due to edits (constraint 1.1).

For edited assets that failed to sync--I need to retry them in future syncs until they are successful.
And I need to avoid syncing records unnecessarily--due to the performance concerns.

How can I do this?


texts: What configuration to use for PGbouncer over a WAN link

texts:
We have two datacenters, one in America and one in Europe. For various reasons, the Europe datacenter has the database server, while the workers and web apps are in America.

PGbouncer is in the American datacenter, in transaction pooling mode, helping reduce connection latency to the european datacenter. Unfortunately, I often have transactions in the "<IDLE> in transaction" state, which are holding locks and preventing the rest of the machines from making useful progress.

America
-------

worker      webapp
   |           |
    \         /
     \       /
      +--+--+
         |
     pgbouncer
         |
         :

      public
      Internet

         :
         |
      PostgreSQL

------
Europe

What pgbouncer parameters should be set/reset/changed to prevent those idle in transactions?


texts: Improve performance of sys.dm_db_index_physical_stats

texts:
During a maintenance job, I'm trying to get a list of fragmented indexes. But the query is extremely slow and takes over 30 minutes to execute. I think this is due to a remote scan on sys.dm_db_index_physical_stats.

Is there any way to speed up the following query:

SELECT
    OBJECT_NAME(i.OBJECT_ID) AS TableName,
    i.name AS TableIndexName
FROM
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') phystat
    INNER JOIN sys.indexes i
        ON i.OBJECT_ID = phystat.OBJECT_ID AND i.index_id = phystat.index_id
WHERE
    phystat.avg_fragmentation_in_percent > 20
    AND OBJECT_NAME(i.OBJECT_ID) IS NOT NULL
ORDER BY phystat.avg_fragmentation_in_percent DESC

I'm not a DBA and could be making an obvious mistake in the query above, or maybe there are some indexes or statistics that would help? Maybe it's just the size of the database (around 20Gb with about 140 tables).

The reason I ask is that we only have a very small window for maintenance during the night and this is taking up most of the time.


texts: MySQL architecture for different word clouds

texts:
I want to create a word cloud on my website based on the keywords people search.

I have one free text input field where something can be entered.
I have 3 different user types on my website (anonymous, users, organizations). And I want to keep create a wordcloud on the most searched keywords for all these 3 types separately.

I was wondering what is the best architecture for MySQL to accomodate this problem.

Is it better to save all user types in 1 table and keep a "user_type" field? Or is it better to create a different table for each user type (for performance reasons?)?

As well, longer term-wise, I would like to use the user's searching history to provide better results for him. So to avoid rework later, I think it's good that I already have it linked to a specific user_id already right now.

Can anyone suggest me a mysql architecture for this issue?
I am thinking of going the way as suggested by user @Derek Downey in this post. With adding a user_id in the DateofSearchTerm table.


texts: Should snapshot agent continue to run in transactional replication?

texts: We have transactional replication running for a large number of publications on sql server 2008 R2 2 node active/active cluster.  I have noticed that the snapshot agent job runs hourly and it looks like it does a refresh of the publications (literally, a new snapshot?).  During this time we experience locking issues with the blocker being this job.  Should this be doing a refresh this often if no new articles are being added or changed?


texts:
I am trying to run an alter query on a table, and its giving me a

Lost connection to MySQL server during query
MySQL server has gone away.

The query that I am running is

 alter table table_name modify column_name DOUBLE ( 16, 2) default 8.5;

I am guessing that the table contains a lot of data, and hence the alter is taking a lot of time, and the server connection is lost.

My query is,

Firstly,

How do I find out the amount of data present in the table ? ( in MB/GB i suppose).

Secondly,
 In case the table does contain a huge amount of data, then how do I perform the alter on this table ?


texts:
BACKGROUND :

We have an environment where there is a new DB created almost every minute and it will be Dropped after few days or weeks (maximum 1 month) such that the space used is about 300GB~400GB in /var/lib/mysql/ which is 500GB size.

We are using MariaDB 5.3.5 (FIXED, no chance of upgrade) on Centos6 VM.

Problem :

When the mysql volume is 90% used with 85K DataBases, creation of new Databases is very slow, and DB Dropping is also very slow.

Questions :

Is 85K DataBases too much for MariaDB 5.3.5 ?
What is the Documented safe limit ?
How do I make DB creation not get blocked by DB Deletion ?
What could be the real culprit for slowness when there are too many DataBases ?
What Parameters can I enable on the server to track the slowness ?

Details :
Each DB will have 12 tables. Each table will have 10~100 rows.
Each DB will be around 3MB~5MB.
We are using LVM, with / mounted as ext4.
MariaDB 5.3.5 is running on Centos6.6 VM with Linux Kernel 2.6.32-504.3.3.el6.x86_64.
VM has 4 cpus (3GHz) with 32GB RAM, hosted on VMWare ESX 5.5 running on a Cisco UCS Blade.


texts: View delete statements in the Transaction log

texts: Is it possible to view delete statements that have recently occurred in the Transaction Log?


texts: SQL Query logging in extended events deadlock xml report is too short

texts:
How can I tell the sql server to log the whole query in the xml, and not truncate it?

example:

   <inputbuf>
(@0 nvarchar(20),@1 nvarchar(10),@2 decimal(38,20),@3 int,@4 datetime,@5 nvarchar(10))IF EXISTS(SELECT TOP 1 NULL FROM "MySQLDB".dbo."MyTable$Reservation Entry" WITH(UPDLOCK)  WHERE ("Item No_"=@0 AND "Location Code"=@1 AND "Quantity (Base)"&gt;@2 AND "Reservation Status"=@3 AND "Expected Receipt Date"&lt;=@4 AND "Variant Code"=@5)) SELECT "timestamp","Entry No_","Positive","Item No_","Location Code","Quantity (Base)","Reservation Status","Description","Creation Date","Transferred from Entry No_","Source Type","Source Subtype","Source ID","Source Batch Name","Source Prod_ Order Line","Source Ref_ No_","Item Ledger Entry No_","Expected Receipt Date","Shipment Date","Serial No_","Created By","Changed By","Qty_ per Unit of Measure","Quantity","Binding","Suppressed Action Msg_","Planning Flexibility","Appl_-to Item Entry","Warranty Date","Expiration Date","Qty_ to Handle (Base)","Qty_ to Invoice (Base)","Quantity Invoiced (Base)","New Serial No_","New Lot No_","Disallow Cancellation","Lot No_","Vari   </inputbuf>
  </process>

this is taken from sql server management studio 17.4, management, extended events, sessions, system_health, package0.event_file event detail xml_report value. As you can see the query shown is truncated. Is there a way to tell SQL to log all of the query without truncating it?

If there isn't a way to tell sql not truncate, what is the best way of retrieving the whole query?

Thank you


texts: Order by exact matches (jsonb array), then lexeme similarity

texts:
Say I have a table like this in Postgres 9.5:

CREATE TABLE public.posts
(
    content text,
    tags jsonb,
)

I'd like to design a single query that:

Finds results based on exact tag OR matches (tags ?! array['tag1','tag2']) AND on matched free-form text (to_tsvector(content) @@ plainto_tsquery('some phrase'))
Orders by tags matches first, based on the # of matches - i.e. if row A has [apple, orange] and row B has just [apple], then a search for array['orange', 'apple'] would yield row A higher than row B (but they'd both be returned)
Orders by content second, based on the weight/similarity of the returned result. So a search for the keyword 'french hello' would yield a row containing content of "how do I say 'hello' in French?' higher than 'what's the weather like in the French Riviera?'

How would I go about combining the above in a single query, so that both exact tags matches and/or fuzzy content matches yield results using the weighting above?


texts: Optimistic locking with postgresql blocking due to locks

texts:
I'm trying to use optimistic lock in postgresql but it seems I'm misunderstanding how it should work. I thought that if I used 'serializable' isolation level, each transaction would act as if the other transactions didn't exist and just at compile time any checking would be done and the transaction would eventually abort. However, in the test I'm doing, the transactions are affecting others, in the sense that one transaction might block in a lock. This is the test I'm doing:

First, create the db:

CREATE USER myuser WITH PASSWORD '1234';
CREATE DATABASE tempdb;
GRANT ALL PRIVILEGES ON DATABASE tempdb to myuser;
\connect tempdb;
CREATE TABLE temptable (
    id        integer PRIMARY KEY,
    name       varchar(40) NOT NULL
);
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser;

Then, create two python programs.

The first one will try to insert a row and then will sleep 10 seconds before committing the transaction:

import time
import psycopg2

def main():
    conn = psycopg2.connect(dbname='tempdb', host='localhost', user='myuser', password='1234')
    cur = conn.cursor()
    cur.execute("begin transaction isolation level serializable")
    cur.execute("insert into temptable (id, name) values (1, 'my name');")
    time.sleep(10)
    cur.execute("commit")
    print('test finished')

if __name__ == '__main__':
    main()

The second will try to do almost the same, however it won't sleep for 10 seconds:

import time
import psycopg2

def main():
    conn = psycopg2.connect(dbname='tempdb', host='localhost', user='myuser', password='1234')
    cur = conn.cursor()
    cur.execute("begin transaction isolation level serializable")
    cur.execute("insert into temptable (id, name) values (1, 'my other name');")
    cur.execute("commit")
    print('test finished')

if __name__ == '__main__':
    main()

If I run temp1.py and then, in other window, temp2.py, temp2.py will hang until temp1.py commits. But, what I would expect is that temp2.py would insert the row, commit and then, when temp1.py tries to commit, it would get an error.

Am I doing anything wrong? Is it possible to do what I want in postgresql?

(I'm using postgresql 10.12)


texts: Why and When the pg_stat_activity's most of the columns are null?

texts:
Recently, I have noticed that when I query the pg_stat_activity for few users most of the columns are [null] (pgAdmin 4) in the result.

Only thing I can see is datid, datname, pid, usesysid, usename, application_name, query, rest all are [null].

There is an interesting part here. When I execute

select * from pg_stat_activity where query = '<insufficient privilege>'

is returning same rows as

select * from pg_stat_activity where state is null

and same as

select * from pg_stat_activity where client_addr is not null

What could be the reason for this?

(I am using RDS Aurora Postgresql 9.6.12)


texts: Is it possible to keep one node running in 2 node cluster while keeping  one node down for Maintenance?.I want to do this as we are planning some hardware upgrades.


texts:
Background

At my current organization, we've got a SQL Agent Job running a Powershell script, originally based on Script All Server Level Objects to Recreate SQL Server.

This job writes a bunch of scripts that can be used in the event we need to do disaster recovery.
Our version queries a separate database that lists out the servers that should be checked.
Prior to this job, I hadn't used Powershell though I've had other scripting experience (i.e. Python).

We also have all of our servers setup, using the SQL Server Agent, to email the DBAs when we have failed logins.
One of our development servers has a couple of databases on it that are taken offline for some unknown (to me) business purpose.
When our SQL Agent Job runs, it always generates failed login alerts when it comes to those offline databases on that one development server. I've been asked to see if there's someway to have the script skip checking databases that are offline to prevent these false alerts.
I've traced the source of the alerts back to the function below. I believe this bit, if($database.Status -eq 'Normal') was intended to make sure the database is online but it doesn't work.
Based on some other reading online, I tried replacing if($database.Status -eq 'Normal') with if ($database.IsAccessible), but I still got the failed login alert email when running the script.

#Function to write out Database scripts
function ScriptOutDBObjects($serverObject, $objectType)
{
    foreach ($database in $srv.Databases)
    {
        if($database.Status -eq 'Normal')
        {
            $objectPath = $scriptPath + $objectType +'\'
            if (!(Test-Path -Path $objectPath))
            {
                New-Item -ItemType Directory -Path $objectPath
            }
            $scriptingOptions.FileName = $objectPath + $database.Name.Replace(':','').Replace('\', '_') +'.sql'
            $database.Script($scriptingOptions)
        }
    }
}

The job is using the Powershell type, though I get the same results when running the code from the Powershell ISE on my local computer testing things out.

Question

Will Microsoft.SqlServer.Management.Smo always try to open the database? If so, is there some flag or something I can pass it to make it not do so?


texts: Gaps and islands: client solution vs T-SQL query

texts:
Can a T-SQL solution for gaps and islands run faster than a C# solution running on the client?

To be specific, let us provide some test data:

CREATE TABLE dbo.Numbers
  (
    n INT NOT NULL
          PRIMARY KEY
  ) ;
GO

INSERT  INTO dbo.Numbers
        ( n )
VALUES  ( 1 ) ;
GO
DECLARE @i INT ;
SET @i = 0 ;
WHILE @i < 21
  BEGIN
    INSERT  INTO dbo.Numbers
            ( n
            )
            SELECT  n + POWER(2, @i)
            FROM    dbo.Numbers ;
    SET @i = @i + 1 ;
  END ;
GO

CREATE TABLE dbo.Tasks
  (
    StartedAt SMALLDATETIME NOT NULL ,
    FinishedAt SMALLDATETIME NOT NULL ,
    CONSTRAINT PK_Tasks PRIMARY KEY ( StartedAt, FinishedAt ) ,
    CONSTRAINT UNQ_Tasks UNIQUE ( FinishedAt, StartedAt )
  ) ;
GO

INSERT  INTO dbo.Tasks
        ( StartedAt ,
          FinishedAt
        )
        SELECT  DATEADD(MINUTE, n, '20100101') AS StartedAt ,
                DATEADD(MINUTE, n + 2, '20100101') AS FinishedAt
        FROM    dbo.Numbers
        WHERE   ( n < 500000
                  OR n > 500005
                )
GO

This first set of test data has exactly one gap:

SELECT  StartedAt ,
        FinishedAt
FROM    dbo.Tasks
WHERE   StartedAt BETWEEN DATEADD(MINUTE, 499999, '20100101')
                  AND     DATEADD(MINUTE, 500006, '20100101')

The second set of test data has 2M -1 gaps, a gap between each two adjacent intervals:

TRUNCATE TABLE dbo.Tasks;
GO

INSERT  INTO dbo.Tasks
        ( StartedAt ,
          FinishedAt
        )
        SELECT  DATEADD(MINUTE, 3*n, '20100101') AS StartedAt ,
                DATEADD(MINUTE, 3*n + 2, '20100101') AS FinishedAt
        FROM    dbo.Numbers
        WHERE   ( n < 500000
                  OR n > 500005
                )
GO

Currently I am running 2008 R2, but 2012 solutions are very welcome.
I have posted my C# solution as an answer.


texts:
I have a table that holds status changes per item.  The item can go in and out of a status multiple times.

ItemTable (ItemId int, Status varchar(50), DateOfChange date)

I need to pull the date of each change. However, since the statuses can be repeated for each Item, I can't just do the min(DateOfChange) and find the occurrences.

This is in an OLAP environment, and the subquery that's been used to pull this data is KILLING performance.

Is there a way to pull this stuff via partitioning/rownumber functions?  Or something else that would behave better in OLAP? (This is in SQL 2008.)


texts:
Please suggest me how to solve the error How to solve the error

  Msg 547, Level 16, State 0, Line 1
  The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "MyForeignKeyName". The conflict occurred in database "MyDatabase", table "dbo.MyTable", column 'IMyColumn'.


texts: postgres_fdw slower then copy + scp + copy (~12 times)

texts:
foreign server 9.2
local server 9.5
table is 10GB
data transfer performed on same network interface as foreign server works
no indexes set on data
old way:

copy to - 2:36
scp - 08:17
copy from - 10:11

postgres_fdw:

by the time old way finished it has done 800MB of insert into ..select * from foreign_table

Question: Did I miss something in config (meaning I can improve it), or postgres_fdw is just not meant for bulk load (meaning I can't improve it)? (I use it for small data amount reconcile and it works fine. Idea of insert select from fdw instead of running bash commands looked so sweet)

Update I tried psql to remote server from local server and \copy table - six minutes - faster then over ssh

Update2 The fetch_size option, not available prior to 9.6, can be mocked up with dblink_fetch(CURSOR, fetch_size) - look my answer below for sample.


texts: Dealing with non active transaction in log file and shrinking

texts:
I have posted the first part of my question here & I don't want to flood my question with a loads of information and would force people to alter their answers for my each question.

I think I am lacking the knowledge about how logs works, so I would appreciate if anyone can redirect me to tutorial or book which can tells me deep about it. However, I am confused about

I understand that I get error because my log file was filled when SQL was either trying to run an active transaction or was trying to create checkpoints.

but I have large enough space for log file (i.e. 200 GB); so is there any way I can remove entries from log file for all transaction other than active transaction. Because at max my transaction will not exceed 10 GB at any instance.

It is recommend not to shrink log file
But then how can I ensure that my future transactions (i.e queries) will run smoothly? Does SQL Server shrink/flush/delete data from log by itself?
What is the best practice to deal with log file size?

I already have 200GB allocated for log file and I can ask for more. Isn't that enough? and if I keep increasing my drive size then some other day I will be on the same position where I am today.


texts: I'm already quite comfortable with using  COMPRESS() and DECOMPRESS() in an internal forum software for our company (Currently in SQL Server 2017), but trying to make the database as efficient as possible, is there an advantage to adding _UTF-8 to my current collation as in Latin1_General_100_CI_AS_SC_UTF8 upon future migration to SQL Server 2019?


texts: 1..1 and 1..n cardinalities on the same relationship

texts:
I have two tables (companies and users), for which the following rules apply:

A User has a Role ('employee' and 'boss')
There is one and only one 'boss' in a Company.
A User can be in only one Company.

Before thinking about the Boss case, I created only two tables and added a foreign key column company_id on the users table. So every User has a Company and everything is alright.

But now, there is a new rule: there can be only one boss. How do I write this rule in my schema?

Should I create a boss_id column in companies table? If so, should I leave the company_id NULL for this kind of User? (but it seems weird) Or should I add company_id and boss_id in both tables? (but it's redundant).
Should I keep going with only company_id and keep the rule in my head ("hey, remember, the schema does not tell anything about this, but you can't create two bosses for the same Company").
Should I... Hum, I must be tired I forgot the other patterns?

I think I missed something really simple, but I can't figure it!

This is an over-simplifed example, one could say there can be multiple bosses.


texts: How can I import my .pde file which I already exported from another database by PL/SQL Developer?


texts: Most Efficient Strategy to Insert Many Records

texts:
Thanks for reading this.  I'm trying to work out the best strategy to keep my customer table up to date.

I have a customer table plus 6 daily transaction tables that get new data every day.  If there is a new customer, they might come in the transaction table so I need to find the customers in the transaction table that aren't in the customer table and add them.  This seems right to me but I wanted to get input from others because it seems really inefficient with "select the customer that is not already in the primary table" when the customer table is so big.   The data is loaded every day.

Here is my command:

insert into customer (customer, reptdate)
(select distinct customer, getdate() as reptdate
 from T_TX_CUST
 where not customer in (select customer from customer ))

The thing is, as part of the transaction table processing, I go through the tables row wise in C#.  I could do a select there to see if the customer existed and then add them if they don't.  The programmer in me really wants to do it this way but using SQL would be much simpler but I'm afraid of the cost.   Is using a set of SQL commands in a stored procedure faster than doing a select and then an insert on a record by record basis?

I would really appreciate any thoughts or advice.


texts: Would db corruption be further minimized if I remove querying on a backend table from the user's front end?

texts:
I have an MS Access database that was being used by 30 people. They all use it at different times of the day, but within any 8 hour block of time there might be 10 people using it. This database was not being split between front end and back end. The main process was that a user filled out a form and then VBA code would write this information to a table.

I now want to make a new version of this database where the front end is split from the back end. This is because there were numerous times where the whole database became corrupted and all our data, queries, and forms were lost.

I noticed that on one of the forms I have a control that is querying the table that is being moved to the backend. I'm not sure if having each user's local copy of the front end query the backend table just for this one control is worth it. I want to minimize db corruption, so should I just remove any sort of querying on the backend table for small stuff and only allow users one major write to the backend table? Or does this not matter?


texts: What happens to queries that are executing when SSMS crashes?

texts:
Lets imagine that we use SQL Server Management Studio to execute a very long delete query. For the sake of this example lets say that it's execute time is 10 hours. While it executes a few hours into the process SSMS crashes. What will happen to the query? Will it be rolled back or will it continue executing despite the fact that SSMS is no longer running?

Database restores are terminated when SSMS crashes, but I'm curious to know what happens with alters and delets.


texts: How can I get a value of a column to 2 decimal points?

texts: As you can see from the columns, i wanted the output to be 2 decimal place as shown on EndOfYearForecast. Any way around it?


texts: SQL Server: Batch Requests/sec showed increase in performance, but performance actually decreased

texts:
We are currently on SQL Server 2005, with a plan to move to SQL 2012. To make this move we have to test new database drivers for the application (Java). We performed 2 load tests using the 2 different drivers.

Test 1

Transactions/sec  = 9400
Batch Requests/sec = 2400

Test 2

Transactions/sec = 7200
Batch Requests/Sec = 6000

What I am interested in is why the massive increased in batch request/sec in test2, even though there was an overall drop in trans/sec (note that the front end performance mirrored the drop in trans/sec between the 2 tests). If it wasn't for us monitoring front end performance and trans/sec we would have actually thought we'd seen a 3x improvement in performance, instead of an actual 20% drop in performance.

Any thoughts on what features of the drivers may cause this?

For reference test 1 was performed with I-net Java driver, test 2 was performed with MS JDBC driver.


texts: What sort of exec requests don't have a sql handle? (sys.dm_exec_requests)

texts:
I have crafted a query against SQL Server 2012 that looks like the following:

SELECT r.session_id, r.sql_handle, t.text -- more details excluded for brevity
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t

Now, this helpfully shows me what is running on my server -- and using a query like this I've been able to find things that consuming more resources than they should.

However, it bothers me that (because of the cross apply, I believe) the entries in sys.dm_exec_requests that don't have a sql_handle value, aren't included. I also worry that at times, the number of requests returned (typically about a dozen) is below what I would expect based on load.

I understand (or think I do) that this is the most finite snapshot in time view I could look at, and SQL can only do so many things at once, but I also wonder, what is my query missing to present me a picture of "what is using my server right now"?


texts:
I am a new DBA at a company that has never had a DBA before me. SQL Servers are never upgraded unless it is part of a Windows Update on the server. Service Packs (or CUs) are never installed. We have a mix of versions from 2000 to 2014.

I'm trying to develop a policy for maintaining and upgrading the SQL Servers. How often do you upgrade your SQL Servers? Or more specifically, how up-to-date do you keep your SQL Servers and why?

For example:

SQL Servers will be upgraded when the version is not within Microsoft's Mainstream support.
SQL Servers will stay within 2 (two) versions of the most recent version released from Microsoft.

What about SPs and CUs?


texts: How to access oracle 11g database remotely

texts:
I have oracle database on my computer that i am accessing through a java program using localhost:1521 more precisely using ojdbc7 and host String as "jdbc:oracle:thin:@localhost:1521:orcl". Everything is working fine but now I want to access same database through another computer with different internet connection. I read that I have to change listener.ora file and add ip address where localhost is.

Now I have tried using ip address of my computer which i have set to 192.168.1.5 in network connection and same in listener.ora file and created a virtual server in my router settings and set this same ip address and port there as well. Still when I try to connect to server computer's Internet IP(not 192.168.1.5) it states "TNS:no listener".

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.5)(PORT = 1521))
    )
  )

Firewall is off. I have also tried using 127.0.0.1 and creating virtual server for that in router as well but didn't work. Oracle 11g is installed on both server and client's computer.

I have also tried using 0.0.0.0 ip in listener.ora file so it may listen to all ip's but that didn't work as well.

Can someone point me in the right direction.

Edit: Different internet connection means one is my home internet (ip:39.40.xx.1xx) other is my friends home internet (different ip) we live few miles apart and use same ISP. My computer is acting as server which is using Windows 10. Its firewall is disabled. I am assuming that my friend would use my internet IP address which would be forwarded to my computer (192.168.1.5)


texts: Chat/conversation history, entity relationship diagram

texts:
Totally aware that there are very similar questions, went through each of them, but those are pretty simple/basic solutions from where i took some things and ideas to create below entity relationship diagram, according to my needs. What i'm up to is to create database schema (postgresql) for chat/conversation history, where i need to support couple of tricky things, such as:

Chat have a name and initial creator (customer_id)
Chat message created by customer_id can either be a plain text message or a file, while
those two are sharing some of the columns, but in a sense those are
different in a way that file can have file_name, file_mime_type,
external_path like location where it is saved (e.g file system),
and file can be marked as being relevant or not, by anybody in
conversation. Due to many differences between the two, to me at least, seems
more natural to divide the two and to have "common parent" table, chat_lines. Other approach would be to have single table with many NULL values or so. Highly interested to see your opinion about it.
One of the biggest worries that i have is that chat participant being
able to reply_to existing chat line or start a thread on existing
chat line. I have no better idea than to make chat_lines table having two separate
references to itself. I do not see any better way of how to model threads and replies.
Each message can be seen by anybody in discussion, hence
introducing seens table. Not quite sure is seens correct english
word.
Finally anybody in conversation/chat can react to each chat line,
like thumb up, thumb down, no more than that, so ENUM seems like
a nice approach.

After hours and hours of thinking i came to this:

One of the worries here for me is that chat lines table have become like a God table, everything is concentrated around that table and probably every future table will be related to it. Further, each SELECT query must JOIN literally everything in order to rebuild chat history. Worrying about performances a lot, would expect that chat_lines table will become huge over time, just imagine that one single chat between 5 to 10 participants can have around 1k-5k chat lines, with few file uploads along the way. Looking for proper indexes on those table. Each SELECT query that i can imagine now will be probably using heavily chat name, customer ids (like participants of the chat). So thinking to create some indexes on those.

Of course, whatever participant can search for mentioned things. When it comes to plain text messages, seems like way to go is postgresql tsvector against text column in messages table. Participant will be able to search against file_name when it comes to file uploads, so seems reasonable to have index on file_name in files table.

Please sorry for a little bit "opinionated question", highly interested in your opinion about how much this solution will scale in the future, would highly appreciated any advice what could be changed. Any other idea how replies and threads could be modeled? Where you would add indexes? Whatever i do, i guess database partions based on something from this schema should be created in future when data starts to grow very much.


texts: Registry error while trying to install Oracle 11g on Win 2008 R2

texts:
I am new to Oracle and trying to install Oracle database 11g Enterprise Edition on Windows 2008 Server R2 but getting this error all the time...

It gives me error when updating registry key HKEY_LOCAL_MACHINE/software/oracle/OracleMTSRecoveryServiceProtid_0..

It says The specified home name was not found in the registry

Could anyone please help me out to resolve this..


texts: Is the filestream in SQL Server backed up when using standard backup techniqe

texts: I'm using a SQL-Server 2014 database with filestream enabled. To backup this database I'm using the powershell command Backup-SqlDatabase, which is equivalent (as fas as I know) to T-SQL BACKUP DATABASE. The backup is stored in a "mydatabase.bak" file. My question is very simple: is the filestream backed up as well by this command or do I have to take further steps zu back it up?


texts:
Error:

  Replication-replication Distribution Subsystem: agent (null) scheduled
  for retry.  Query timeout expired.

Based on my research, this error appears when there is a lot of contention on the distribution database.  I do see a distribution query running for over 10 hours.  If I kill this transaction, won't this cause issues with the replication since this may be a transaction that is reading and copying data?  Wouldn't killing it suspend that process and possibly cause corruption.

The publisher and subscribers are all online; these query timeout errors seem to be related to the distribution server and the distribution database contention.  I'm trying to identify what's happening overall, since I have many publications that are getting timeouts and from digging into the servers, it only appears the distribution server is behind.


texts:
I have a table that has 4 e-mail fields. A single contact can have anywhere from 1-4 e-mail addresses on file.

I'm having trouble writing a query that displays all duplicates across columns. For my example, I'll only discuss 2 email fields.

Example:

John Smith, Personal Email fake@fakejohn.com Work fake@fakejohn.com
Jane Smith, Personal Email jane@fake678.com Work NULL
Jane Smith, Personal Email NULL Work jane@fake678.com

In the above example, I can't find a way to say "I have 2 Jane Smiths that are duplicates" because they have the same email, but in different columns while also excluding John Smith, because while his email appears twice, I don't want to self-reference, as this is not a duplicate of itself.

Iterations of my attempt to find a cross-column duplication check have been unsuccessful but include the following:

Select First, Last, Personal
From Contacts C
where C.Personal = C.Work
  and c.ID <> c.ID -- my attempt to prevent self reference

This doesn't work, I receive 0 results, but should have thousands.

Without the c.ID <> c.ID bit, I receive too many results, as I retrieve results for non-duplicate records that simply have the same email address in both fields.

I've also added "Group By" clauses based on First & Last name and filter by greater than 1, but when checking our database, I'm still finding that I have contacts with the same name and no e-mail addresses in common with each other but have the same scenario as John Smith above.

Is there any way to achieve this? I haven't found any queries to get me started.


texts:
I have installed pgAdmin 1.18.1 but whenever I am trying to create a new cluster using Slony-I, it is always saying that it is missing some scripts. I have copied some .sql scripts from my Slony folder in linux to my pgAdmin folder in windows and set the options file for this folder. However, the problem remains.

Does anyone have a solution for this? perhaps anyone can share the scripts please ?
I am using PostgreSQL 9.3 on Ubuntu 12.04 and pgAdmin on windows.


texts: PostgreSQL chooses to seq scan huge table instead of index lookup

texts:
I have a table, account_balance, with primary key (address, timestamp, currency). This table is pretty big, having nearly 1 billion rows and taking ~3T of space. I am doing a simple query on it:
SELECT address, EXISTS(SELECT address FROM account_balance WHERE address = a.address) ex
FROM (VALUES ('ab566dceac0d857dbf05682d1ceb548667cf4580')) AS a(address);

This should be a simple index lookup but instead Postges produces a seq scan. Here's explain result:
Result  (cost=0.37..0.38 rows=1 width=33)
  InitPlan 1 (returns $0)
    ->  Seq Scan on account_balance  (cost=0.00..355882209.06 rows=965801108 width=0)
          Filter: ((address)::text = 'ab566dceac0d857dbf05682d1ceb548667cf4580'::text)

If I set set  enable_seqscan = off then as expected:
Result  (cost=0.99..1.00 rows=1 width=33)
  InitPlan 1 (returns $0)
    ->  Index Only Scan using account_balance_pkey on account_balance  (cost=0.83..153234426.09 rows=965801135 width=0)
          Index Cond: (address = 'ab566dceac0d857dbf05682d1ceb548667cf4580'::text)

So the question is - why it chooses the seq scan? It should never make sense to prefer seq scan to index lookup on such a huge table. What could compel it to choose to go sequentially over a 3T table (which of course takes forever) instead of going for the index (which takes microseconds)? Is some parameter wrong or some system variable I need to check? I tried SET random_page_cost = 1 and for some cases it helps, but for others it still insists on using seq scan. Only setting enable_seqscan to off seems to fix it consistently, but I feel it's a wrong approach.
I am not sure how to interpret the costs numbers - I feel like they may be the key for it, but I can't figure out where they come from and why they are what they are.
This is on PostgreSQL 12.
P.S. Tried to do ANALYZE, didn't change anything.


texts: PostgreSQL filtering on array length inside JSON

texts:
I have a table table with a JSONB field data, which contains a variable-length array, e.g.

{"label": "xyz", "items": [ ... ]}

I created an index on the length of the "items" element:

CREATE INDEX n_items ON table ( JSONB_ARRAY_LENGTH(data->'items') )

but when I filter, I still get a sequential scan when I try to filter on it:

EXPLAIN ANALYZE SELECT COUNT(*) FROM table WHERE JSONB_ARRAY_LENGTH(table.data->'items') = 2;

                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Aggregate  (cost=2565655.67..2565655.68 rows=1 width=8)
   ->  Seq Scan on table (cost=0.00..2535256.19 rows=12159794 width=8)
         Filter: (jsonb_array_length((table.data -> 'items'::text)) = 2)
 Planning time: 0.121 ms
 Execution time: 482891.694 ms

That's about 8 minutes to filter! Did I do something wrong here, or is this the consequence of PostgreSQL not keeping statistics on JSON(B) objects? It should be possible to flatten out this data column, but I'd like to be sure that's what I need to do before I start working on it.

edit: these array lengths do not vary much. There are only 4 distinct values in the data currently, and I don't expect to have many more. Is the index just not very useful in this case, or can I improve filtering some other way?


texts: MySQL performance for aggregate function

texts:
I'm using mysql 5.5

Here is my table:

CREATE TABLE `temperature_information` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `device` int(11) NOT NULL,
   `temperature` int(11) NOT NULL,
   `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (`id`),
   KEY `device` (`device`),
   KEY `date` (`date`),
   KEY `idx` (`device`, `date`)
) ENGINE=InnoDB AUTO_INCREMENT=25602738 DEFAULT CHARSET=latin1

This table has ~50m. records.

Here is my query:

SELECT date as ValueDate, MAX(temperature)
FROM (
     SELECT date, temperature FROM temperature_information WHERE device = 1111
     ORDER BY temperature DESC) c
GROUP BY DATE(ValueDate),HOUR(ValueDate)

This query returns Maximum temperature for each day. It's execution time is ~0.9 s. and subquery's time 0.003 sec.

I have separate indexes on the date and device columns and a multi-column index idx on device,date. The EXPLAIN says it uses device index for query, which is good. The sub query is very fast.

But to get MAX temperature for each day I need to use GROUP BY. I know that functions on indexed column disables the use of indexes, but I don't know a workaround to make it efficient and produce same results.

Here is EXPLAIN:

id  select_type     table           type    possible_keys   key     key_len  ref    rows    Extra
1   SIMPLE  temperature_information ref     device,idx    device      4      const  29330   Using where; Using temporary; Using filesort

My question:

Is it possible to write a query which would be more efficient and would produce same results or should I process rows returned by sub query and find MAX temperature for each day myself (This would be written in c)?

Sub query returns 20-40k rows on average.

PS. I know you can remove sub query, but I left it like this to clarify question

Creating index on those (3) columns improved performance ~20 times. But I have another concern regarding indexes. I provided, not full table. table have another columns like signal, etc. I'm planning to do same queries to find MAX for each day on these columns too. Does adding too many indexes make SELECT slower for other queries. I know that inserts and updates will be slower.


texts: Postgres row_to_json precision for numeric type

texts:
I am using the row_to_json function to convert a table row to a json object.
There are a few columns in the table that are of type numeric which contain float values.

When row_to_json returns the json, values like 9.98 becomes 9.98000000000000043 or 6.30 becomes 6.29999999999999982 in these numeric columns.

Is there a way to impose precision at the json creation function? There are no other data issues in the table which are affecting other modules.
Changing the column type to numeric(12,2) etc. is not possible since the table has over 2 billion rows(the table would be locked for 30+hours) and would affect other production modules.

--update--

I should have made the question a little bit clearer with the use case.

row_to_json is used in a trigger to audit updates in the DB. The json result is stored in another table and this same audit function is used for  multiple tables(100+) where there may be other tables with this same issue.

One solution would be to handle it at the application level when retrieving the jsons from the audit table, but I would like to see if there is a function that can handle it in the DB itself.


texts: MySQL - How to optimize multiple OR conditions in a query on a large table

texts:
I have a table with the following structure:

Configuration (
  id: binary(16) Primary key,
  htId: binary(16),
  raId: binary(16),
  date,
  amount
);

I have already created these indexes:

idx_ht (htId) BTREE, idx_ra (raId) BTREE, idx_date (date) BTREE, idx_ht_ra_date (raId, htId, date) BTREE.

The table has nearly 150M records.

Here is the slow query with multiple htId-raId pairs that I want to optimize.

Select htId, raId, SUM (amount) as totalAmount
From Configuration r
Where ((r.htId = htId_1 AND r.raId = raId_2)
OR (r.htId = htId_3 AND r.raId = raId_4) ... ~1000 pairs more (r.htId = htId_X AND r.raId = raId_X))
AND date Between fromDate
AND toDate
Group by r.htId, r.raId
Having Count(1) = DATE_DIFF(fromDate, toDate)
Order by totalAmount ASC LIMIT 0,100

Is there any way to optimize the query above? Seem that the indexes do not have any affect on my query when the query has multiple ORs like that. It take a lot of time to query..
Any help will be much appreciated.
BR.


texts:
Why does this fail in SQL Developer against an Oracle database and what would the an appropriate way of achieving the same?

DECLARE v_pNbr NUMBER := 100150;
begin
     select * from TARGETTABLE where PROCESSNBR = v_pNbr;
end;

  ORA-06550: line 3, column 2: PLS-00428: an INTO clause is expected in
  this SELECT statement

Coming from SQL Server I have gotten used to creating variables in SSMS query editor/page and then running them across multiple selects. Hence it allows one to understand the operations with a change to one instead of many. The examples I have seen seem to hint a historical terminal usage instead of editor which may preclude such a way of working.... Ergo what is accepted way of working with pl/sql as such.


texts:
I'm struggling to get the below query to perform well. It's doing a sequential scan on the children table even though there's indexes on the join columns. Any ideas?

EXPLAIN ANALYZE SELECT "children".* from "children"
    INNER JOIN "parents" ON "parents"."id" = "children"."parent_id"
    WHERE "parents"."partner_pk" = 'partner'

Hash Join  (cost=55541.97..140831.44 rows=20804 width=168) (actual time=366.809..1851.311 rows=21215 loops=1)
  Hash Cond: (children.parent_id = parents.id)
  Buffers: shared hit=47476
  ->  Seq Scan on children  (cost=0.00..66250.30 rows=2152130 width=168) (actual time=0.007..1012.502 rows=2215906 loops=1)
        Buffers: shared hit=44729
  ->  Hash  (cost=55282.99..55282.99 rows=20718 width=4) (actual time=17.496..17.496 rows=21215 loops=1)
        Buckets: 4096  Batches: 1  Memory Usage: 746kB
        Buffers: shared hit=2747
        ->  Bitmap Heap Scan on parents  (cost=876.99..55282.99 rows=20718 width=4) (actual time=2.339..10.817 rows=21215 loops=1)
              Recheck Cond: ((partner_pk)::text = 'partner'::text)
              Heap Blocks: exact=2614
              Buffers: shared hit=2747
              ->  Bitmap Index Scan on index_parents_on_partner_pk  (cost=0.00..871.81 rows=20718 width=0) (actual time=1.992..1.992 rows=21215 loops=1)
                    Index Cond: ((partner_pk)::text = 'partner'::text)
                    Buffers: shared hit=133
Planning time: 0.280 ms
Execution time: 1855.458 ms

CREATE TABLE "public"."parents" (
    "id" int4 NOT NULL DEFAULT nextval('parents_id_seq'::regclass),
    "email" varchar NOT NULL,
    "partner_id" int4,
    "partner_pk" varchar
)
WITH (OIDS=FALSE);
ALTER TABLE "public"."parents" OWNER TO "bark";
CREATE UNIQUE INDEX "index_parents_on_LOWER_email" ON "public"."parents" USING btree("lower(email::text)" ASC NULLS LAST);
CREATE INDEX "index_parents_on_partner_id" ON "public"."parents" USING btree(partner_id ASC NULLS LAST);
CREATE INDEX "index_parents_on_partner_pk" ON "public"."parents" USING btree(partner_pk ASC NULLS LAST);

CREATE TABLE "public"."children" (
    "id" int4 NOT NULL DEFAULT nextval('children_id_seq'::regclass),
    "first_name" varchar,
    "last_name" varchar,
    "parent_id" int4,
    "email" varchar,
)

CREATE INDEX "index_children_on_parent_id" ON "public"."children" USING btree(parent_id ASC NULLS LAST);


texts: Asynchronous Availability Group Secondary Verify Up To Date with Primary Before Backup

texts:
I was wondering if there was a way to include a verification step before a backup on a secondary replica under asynchronous commit mode in availability groups to verify that the secondary replica is up to date with the primary. We intend to run backups on secondary replicas only to keep load off of our primary. However, we do not want to sacrifice performance by using synchronous commit so we must stick with asynchronous.

Current setup is a FCI as the primary, and 2 additional instances setup as secondary replicas. All configured as asynchronous commit mode. One secondary is read-intent only, another is a readable secondary.

Is there any solution that you know of that could essentially give us the best of both worlds?


texts: Create log file only for my executed queries

texts:
I have been using the following to set up the log file:

log_statement='all',

log_collector='on',

log_destination='on',

log_filename='filename'

After that, for testing I just executed a query then I have seen the log file in tools>serverstatus. Unfortunately it has a lot of anonymous queries including the query which I ran.

How can I filter out the queries which were executed along with my query (anonymous queries)? Is there any possibility to log the affected table information like:

which table got affected?
at what time was the query executed?
what fields got affected?

And how can I get these details using queries?


texts: Is there a way to get the isolation level from sp_whoisactive out of the xml into the plain result?

texts:
While I caught myself re-engineering the functionality of sp_who / sp_whoisactive using the sys.sysprocesses and sys.dm_exec_session views, to get an overview about running sessions and transactions on my server, I thought: "no, instead use something already that is available and tested!"

exec sp_whoisactive @get_additional_info = 1;

returns an additional XML column that offers lots of information, one of this is the Isolation Level.

I would like to have the isolation level in the primary resultset of sp_whoisactive. Did anyone have the same requirement and already solved it? Why is it not included in the primary resultset from scratch, since it is an important information.

sp_whoisactive


texts: Insert a row into the table omitting all values?

texts: Suppose I have a table with a serial as primary key and couple of other columns, that are all nullable. Is there any way to insert a new row in that table by omitting all the columns in the insert clause? Since serial is auto-incremental and can be omitted right? The rest of columns can be null and can therefore be omitted too? So theoretically it should be possible somehow. But is it?