texts: Finding cause of high write IOPS on RDS Postgesql

texts:
Our RDS Postgresql 9.6 instance has had an unusual instance of high write IOPS. Write iops went from an idle level of ~10 to over 4000 (our provisioned level) yesterday evening, and stayed at that level for 14 hours.  There was no matching spike of write IOPS on our read replicas, no rise in network traffic, and no read IOPS. I am looking for advice on diagnosing this, and preparing for future incidents.

This morning while IOPS were still high at 10am I looked for long running processes:

SELECT * FROM pg_stat_activity order by query_start asc;

I found two processes idle in transaction from the day before, and terminated them at 10:05. At that point there was no query_start older than 2 minutes, but the write iops remained at 4000. At 10:25 write iops returned to its normal level: I have no evidence that terminating the processes caused that.

Throughout the incident my logs (default AWS configuration) contain nothing but normal checkpoint logging:

2020-03-02 23:55:07 UTC::@:[6884]:LOG:  checkpoint starting: time
2020-03-02 23:55:31 UTC::@:[6884]:LOG:  checkpoint complete: wrote 85 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 1 recycled; write=22.426 s, sync=0.000 s, total=23.549 s; sync files=20, longest=0.000 s, average=0.000 s; distance=16419 kB, estimate=22527 kB

One cause I've considered is autovacuum freeze: cloudwatch shows maximum used transaction id reaching a peak of 199,999,946 then dropping. But:

I've tested with a manual vacuum freeze, and it causes near simultaneous write iops on source and replica.
The writes don't start until an hour after the drop in transaction id.
The data written is more than 2TB on an 80GB database.

This doesn't cause an outage since we're on provisioned iops, but I'd like to understand the cause.  Could this be autovacuum, and what logging options should I change from the RDS defaults to see what's going on?


texts: How can I return the full text of a document indexed in SQL Server Full-Text?

texts:
I'm using SQL Server 2014 with FileTables to store a large number of documents in different formats. The iFilters are working great, and everything is getting indexed with FTS + Semantic Search.  Now I'd like to run some additional processing on the text of those documents, but don't see a reason to have the pipeline redo the work of decoding, extracting, etc. the text from the files.

It seems there should be an obvious solution ... but I've been running in circles without any luck.

So the question is:

How can I query to return the full plaintext of a file in T-SQL?
If that's not possible, can it be done in SSIS or SSAS after the
normal FTS parser has run?
If that's not possible, is there a way to hook into the FTS pipeline
(via a trigger perhaps) so I can split the plain text into another
table?

Alternate solutions are appreciated as well if you've got good examples for me to reference.  The only immediate idea I had was to use a different network share for dropoff, have SSIS pick up the file and extract the text (no idea how to do that), and then to move the file + text to SQL server ... but that seems wonky for a lot of reasons.

[Edited to clarify "why"]

If SQL Server has already pulled out the text in order to chunk it & do the base NLP for the semantic index ... I'd rather use that than reinvent the wheel.  Specific uses I'm looking into are post-processing with other NLP utilities (e.g. NLTK, GenSim, Stanford NLP NER, etc.) so that I can generate extractive document summarizations, store n-gram statistics for my corpus, and include NER for more effective faceted search.

If I have to extract the text out of files before storing them in SQL Server (either using SSIS/.NET so I can keep the iFilters OR by using a different tool altogether) there's limited usefulness in SQL Server's ability to perform that work on FileTables for anything but the most basic tasks.

Consider the number of document formats already supported - and it's a major task to recreate the feature.  Similarly, having to go back to the actual file afterwards and redo that indexing work is inefficient and it'd seem sensible to disable FTS on FileTables, skip using them altogether, or scrap SQL Server for document-based FTS altogether.


texts:
Setup

I have a simple table:

CREATE TABLE [dbo].[StringData](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [DCStringID] [bigint] NOT NULL,
    [TimeStamp] [datetime] NOT NULL,
    [Hour]  AS (dateadd(hour,datediff(hour,(0),[TimeStamp]),(0))) PERSISTED,
    [Date]  AS (CONVERT([date],[TimeStamp],(0))) PERSISTED,
    [DCVoltage] [decimal](18, 2) NULL,
    [DCCurrent] [decimal](18, 2) NULL,
    [DCPower]  AS (([DCVoltage]*[DCCurrent])/(1000)) PERSISTED,
    [IsCompressed] [bit] NULL,
    [TimeStamp15Minutes]  AS (dateadd(minute,(datediff(minute,(0),[TimeStamp])/(15))*(15),(0))),
CONSTRAINT [PK_StringData1] PRIMARY KEY CLUSTERED
(
    [TimeStamp] DESC,
    [DCStringID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

And I have an upsert procedure called [InsertOrUpdateStringData]

ALTER PROCEDURE [dbo].[InsertOrUpdateStringData]
@DCStringID bigint, @TimeStamp datetime, @DCVoltage decimal(18,2), @DCCurrent decimal(18,2)
AS
/****** avoid parameter spoofing ******/
DECLARE @DCStringID_Value AS bigint; SET @DCStringID_Value = @DCStringID;
DECLARE @TimeStamp_Value AS datetime; SET @TimeStamp_Value = @TimeStamp
DECLARE @DCVoltage_Value AS decimal(18,2); SET @DCVoltage_Value = @DCVoltage
DECLARE @DCCurrent_Value AS decimal(18,2); SET @DCCurrent_Value = @DCCurrent
/************/

MERGE [StringData] AS TARGET
USING (VALUES (@DCStringID_Value, @TimeStamp_Value))
    AS SOURCE ([DCStringID], [TimeStamp])
    ON TARGET.[DCStringID] = SOURCE.[DCStringID] AND TARGET.[TimeStamp] = SOURCE.[TimeStamp]
WHEN MATCHED THEN
    UPDATE
    SET [DCVoltage] = @DCVoltage_Value,
    [DCCurrent] = @DCCurrent_Value
WHEN NOT MATCHED THEN
    INSERT ([DCStringID], [TimeStamp], [DCVoltage], [DCCurrent])
    VALUES (@DCStringID_Value, @TimeStamp_Value, @DCVoltage_Value, @DCCurrent_Value);

Using

Many different application threads are using the [InsertOrUpdateStringData] procedure to upsert data to the table synchronous.

The table indexes are use well and one single excecution is quite fast and take around 31ms.

Problem

If another non-select table operation is excecuted (like insert bulk) the are block for a very long time.

Unfortunately [sp_WhoIsActive] is not showing me if the [InsertOrUpdateStringData] procedure calls are also blocking each other.
But it looks like there is a lock chaining because its the only explanation for locking over 10,000ms like in my example.

Question

It seems that my upsert procedure is locking the complete table and slow down other insert operations.

Is the anything I can do to optimize my queries to avoid longtime blocking?

Update 1 - related to Nic answere

The lock happens to all non-select operations, as I wrote bulk insert is only a sample.

Please see here a 10sec lock simple merge call (blocking session 176 is a [InsertOrUpdateStringData] call):

I also tried @get_locks=1 here is the result. Maybe it helps you with more details.

Update 2 - related to Nic answere

Here is one example:

sid 52 is blocked by sid 119 for over 40sec!? But sid 119 seems not to be blocked. I really not understand this.


texts: Copy data from SQL Server 2000 to SQL Server 2014 Express

texts: I need to copy only data (not tables) from a SQL Server 2000 database to a SQL 2014 Express database. I tried to do this using DTS but it loses keys and indices. Backup (2000) and restore (to 2014) does not work. Is there another way?


texts: Encrypt through Java and Decrypt through SQL Server AES 256

texts:
Am trying to insert some value through JAVA Application with AES Algorithm and need to decrypt the data from the SQL Procedure and need to process a data.

Which is the best practice to do so?


texts: dbatools start-dbamigration running very slowly

texts:
I am trying to use the powershell module dbatools to migrate one SQL Server instance to a new instance on a VM. The new VM is on a different physical server than the source. I am not migrating the databases as I already have restored them on the new instance.

I am using the start-dbamigration task, but it seems to be running very slow. I started it last night at 20:00 hours, and as of writing this (the next day at 09:30), it is still running.

SQL Server version is 2014.
I am running dbatools from my personal PC and was wondering if that could have anything to do with the slowness?

The server has a lot of logins, and it seems that much time goes to granting CONNECT access to the different databases (takes around 40 - 50 seconds per login per database).(This task is called Update-SqlPermission in the log)

Anyone have experience with this tool and know if this is normal or not?
Would it be better to install dbatools on one of the servers?


texts: Update an entry in table when time expires

texts:
I need to modify an entry in table, based on time.

i.e. table contains customer-info & subscription-info. I need to modify/delete subscription when subscription expires for each user.

There can be upto 2-3 million entries in the table.

each user will have different expiration date so what is the best means to achieve this?

Is there some sort of trigger mechanism which gets triggered when time expires?

or is it safe to create an event that poles per second or create separate event for each subscriber?

or is it safe to have a third party timer tool that maintains expiration times and trigger an action when timer expires?

or is there a better approach to address this requirement?

btw: database is sql server.

Thanks & Regards,
Prasun


texts: Deny DDL_ADMIN permissions to a role on [dbo] schema

texts:
I have several database schemas like,
[ext],[stag],etc
and of course the built in [dbo] schema,
and also have a role called [MyRole].
My plan is to add [MyRole] to the DDL_ADMIN role, so they can create, alter, delete objects in all schemas,
but I wish to prevent them to do exactly that in the [dbo] schema.
The DDL_ADMIN built in role provides the following permissions for its members:
ALTER ANY ASSEMBLY
ALTER ANY ASYMMETRIC KEY
ALTER ANY CERTIFICATE
ALTER ANY CONTRACT
ALTER ANY DATABASE DDL TRIGGER
ALTER ANY DATABASE EVENT NOTIFICATION
ALTER ANY DATASPACE
ALTER ANY FULLTEXT CATALOG
ALTER ANY MESSAGE TYPE
ALTER ANY REMOTE SERVICE BINDING
ALTER ANY ROUTE
ALTER ANY SCHEMA
ALTER ANY SERVICE
ALTER ANY SYMMETRIC KEY
CHECKPOINT
CREATE AGGREGATE
CREATE DEFAULT
CREATE FUNCTION
CREATE PROCEDURE
CREATE QUEUE
CREATE RULE
CREATE SYNONYM
CREATE TABLE
CREATE TYPE
CREATE VIEW
CREATE XML SCHEMA COLLECTION
REFERENCES

Since DENY takes precedence over GRANT, I can DENY exactly the same permissions above on the dbo schema for [MyRole].
It should be as simple as:
DENY ALTER ANY ASSEMBLY                    ON SCHEMA::dbo TO MyRole
DENY ALTER ANY ASYMMETRIC KEY              ON SCHEMA::dbo TO MyRole
DENY ALTER ANY CERTIFICATE                 ON SCHEMA::dbo TO MyRole
DENY ALTER ANY CONTRACT                    ON SCHEMA::dbo TO MyRole
DENY ALTER ANY DATABASE DDL TRIGGER        ON SCHEMA::dbo TO MyRole
DENY ALTER ANY DATABASE EVENT NOTIFICATION ON SCHEMA::dbo TO MyRole
DENY ALTER ANY DATASPACE                   ON SCHEMA::dbo TO MyRole
DENY ALTER ANY FULLTEXT CATALOG            ON SCHEMA::dbo TO MyRole
DENY ALTER ANY MESSAGE TYPE                ON SCHEMA::dbo TO MyRole
DENY ALTER ANY REMOTE SERVICE BINDING      ON SCHEMA::dbo TO MyRole
DENY ALTER ANY ROUTE                       ON SCHEMA::dbo TO MyRole
DENY ALTER ANY SCHEMA                      ON SCHEMA::dbo TO MyRole
DENY ALTER ANY SERVICE                     ON SCHEMA::dbo TO MyRole
DENY ALTER ANY SYMMETRIC KEY               ON SCHEMA::dbo TO MyRole
DENY CHECKPOINT                            ON SCHEMA::dbo TO MyRole
DENY CREATE AGGREGATE                      ON SCHEMA::dbo TO MyRole
DENY CREATE DEFAULT                        ON SCHEMA::dbo TO MyRole
DENY CREATE FUNCTION                       ON SCHEMA::dbo TO MyRole
DENY CREATE PROCEDURE                      ON SCHEMA::dbo TO MyRole
DENY CREATE QUEUE                          ON SCHEMA::dbo TO MyRole
DENY CREATE RULE                           ON SCHEMA::dbo TO MyRole
DENY CREATE SYNONYM                        ON SCHEMA::dbo TO MyRole
DENY CREATE TABLE                          ON SCHEMA::dbo TO MyRole
DENY CREATE TYPE                           ON SCHEMA::dbo TO MyRole
DENY CREATE VIEW                           ON SCHEMA::dbo TO MyRole
DENY CREATE XML SCHEMA COLLECTION          ON SCHEMA::dbo TO MyRole
DENY REFERENCES                            ON SCHEMA::dbo TO MyRole
GO

SQL Server says "Incorrect syntax near DENY ALTER ...' for all above.
I tried to spell out the correct syntax from BOL, but could only come up with:
DENY ALTER ON SCHEMA::dbo TO MyRole;

nothing else.
What would be the easiest way to DENY all the permissions what DDL_ADMIN membership provides to [MyRole] members, but only on [dbo] SCHEMA?
Thank you!


texts: Where do I find the tables that a role has access to in Vertica?

texts:
I'm trying to use the System Tables to fetch some metadata about the whole Vertica cluster at a table level (including permissions).

Where can I find the information about permissions required to SELECT on a table?

I've poked around in the GRANTS and ROLES system tables, but I can't find any role->table mapping.

I considered using HAS_TABLE_PRIVILEGE() but that does it on user level. I'd rather do it at the role level.


texts: Why can't the schema owner create a new table?

texts: I have a DB user called STANMAN which owns the STANMAN schema.  When I try to create a table as STANMAN, it says STANMAN has insufficient privileges.  How could a schema-owner not already have sufficient privileges?  Naturally, when I try to GRANT ALTER or GRANT CREATE, I get "Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself."  What am I doing wrong?


texts: Load balancing between 2 servers using MySQL Router

texts: I want to setup a simple master-slave MySQL replication. I also want to balance the load between the 2 servers. Can I use MySQL Router 2.1 for that purpose? As I read the official documentation, it seems that MySQL Router now only works with InnoDB cluster. I don't have a need for InnoDB cluster. I just want to load balance 2 servers. Is the Router a good way to go, or should I look elsewhere?


texts:
I have the following table with >1M rows:

CREATE TABLE `wishlist_place` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `wishlist_id` int(11) DEFAULT NULL,
  `place_id` int(11) DEFAULT NULL,
  `city_id` bigint(20) DEFAULT NULL,
  `created_by_id` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `wishlist_place_unique` (`wishlist_id`,`place_id`),
  KEY `IDX_20B83B35FB8E54CD` (`wishlist_id`),
  KEY `IDX_20B83B358BAC62AF` (`city_id`),
  KEY `new_places_on_wishlist` (`wishlist_id`,`place_id`,`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3079689 DEFAULT CHARSET=utf8;

And I'm doing the following query to get the newest places added to that wishlist:

SELECT place_id
FROM wishlist_place as wp
WHERE wishlist_id = 113
ORDER BY wp.id desc
LIMIT 0, 9

I thought the index 'new_places_on_wishlist' would work for this query, but it uses the index 'wishlist_place_unique' instead:

id  select_type table   type    possible_keys                                                       key                     key_len ref     rows    Extra
1   SIMPLE      wp      ref     wishlist_place_unique,IDX_20B83B35FB8E54CD,new_places_on_wishlist   wishlist_place_unique   5       const   28      Using where; Using index; Using filesort

UPDATE

I've noticed that the index that works best is:

KEY `new_places_on_wishlist` (`wishlist_id`,`id`,`place_id`)

but I still have to use the USE INDEX statement:

SELECT place_id
FROM wishlist_place as wp
USE INDEX(new_places_on_wishlist)
WHERE wishlist_id = 113
ORDER BY wp.id desc
LIMIT 0, 9

Why does MySQL takes the UNIQUE index first? The order for the indexes are fields in where, fields in order, fields in select?

--

And another related question, if I do a JOIN with the city table to get the wishlists of that city, is the index IDX_20B83B358BAC62AF the best one or it would be better (city_id, wishlist_id)?


texts: which postgres do i have installed? what directory needs to be in my path to run initdb?

texts:
I need to use postgresql with ruby on rails.

I have these directories

/Library/PostgreSQL

and

/usr/local/Cellar/postgresql/usr:local:Cellar:postgresql:9.2.2

This 2nd directory name is confusing. In finder, when I'm in the directory

/usr/local/Cellar/postgresql,
usr:local:Cellar:postgresql:9.2.2

is actually one directory with forward slashes in the name.  usr/local/Cellar/postgresql.  It's in terminal where I see the :'s

"brew info postgres" returns the following:

postgresql: stable 9.2.2
http://www.postgresql.org/
Depends on: readline, ossp-uuid
/usr/local/Cellar/postgresql/usr:local:Cellar:postgresql:9.2.2 (2819 files, 39M)
https://github.com/mxcl/homebrew/commits/master/Library/Formula/postgresql.rb

So do I have postgres installed properly?  Do I have 2 versions?  One in Library/PostgreSQL and one in /usr/local/Cellar (via HomeBrew)?

Regarding trying to use initdb to create my first postgres database:

I have ohmyzsh installed and I am trying to run the initdb command but getting command not found.  Which path should I put in my path statement so that it can find the command?

/usr/local/var/postgres/server.log did not exist, so I created it.

pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start says server is starting and when I do
    ps auxwww | grep postgres

I get:

postgres   260   0.0  0.1  2522180   8084   ??  SNs  Tue07PM   0:58.79
/System/Library/Frameworks/CoreServices.framework/Frameworks/Metadata.framework/Versions/A/Support/mdworker MDSImporterWorker com.apple.Spotlight.ImporterWorker.504

so I think that means it is running, right?

Well I found initdb script file in both Library and brew locations.  I navigated to the brew directory containing initdb and tried to run the initdb command and got zsh: command not found.

I then navigated to the Library containing initdb, tried to run it, and got the same results.

Isn't the current directory part of the path because I'm IN IT?

Summary of my questions:

Which postgres do i have installed? Is it installed twice and should
I uninstall a version? If so, how?
What directory needs to be in my path to run initdb and do I really
need to run it?
Should the postgres server start when I start my computer?  I heard
it should.  How do I set that up?


texts: SQL Server 2017 Availability group not routing to secondary server

texts:
I've set up a dev AlwaysOn environment on SSMS 2017.
I have my AG set up:

I also ran the following scripts:

ALTER AVAILABILITY GROUP [DEV-SQLAG] MODIFY REPLICA ON N'DEV-DB1' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://Dev-DB1.test.com:5022'));

ALTER AVAILABILITY GROUP [DEV-SQLAG] MODIFY REPLICA ON N'DEV-DB2' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://Dev-DB2.test.com:5022'));

ALTER AVAILABILITY GROUP [DEV-SQLAG] MODIFY REPLICA ON N'DEV-DB1' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ('DEV-DB2','DEV-DB1')));

ALTER AVAILABILITY GROUP [DEV-SQLAG] MODIFY REPLICA ON N'DEV-DB2' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ('DEV-DB1','DEV-DB2')));

now when I connect to the listener (using SSMS2017) with ApplicationIntent=ReadOnly and I run select @@SERVERNAME it still shows me DEV-DB1. I thought it would show me that I'm getting data from the secondary DEV-DB2

What am I missing?


texts: PostgreSQL query takes a long time (as it's not using the index)

texts:
This query takes a long time, as I guess it's doing a full-table scan. It works ok in Oracle, just not in Postgres 12.
select count(*)
FROM EVENT
WHERE PK_EVENT > (select CHECKPOINT1_PKEVENT FROM SYSTEM_CONTROL)

If however, I run this one, it comes back in seconds.
select count(*)
FROM EVENT
WHERE PK_EVENT > (1000755073)

Both the PK_EVENT & CHECKPOINT1_PKEVENT columns are defined as "int8", as they could get very large. EVENT.PK_EVENT is the primary key. I've tried re-arranging the query, but so far nothing works. The count(*) only results in 2537 rows i.e. the number of rows generated today on our test Pg database.
Query plans :
using system_control:-
Finalize Aggregate  (cost=2909961.80..2909961.81 rows=1 width=8)
  InitPlan 1 (returns $0)
    ->  Seq Scan on system_control  (cost=0.00..1.01 rows=1 width=8)
  ->  Gather  (cost=2909960.58..2909960.79 rows=2 width=8)
        Workers Planned: 2
        Params Evaluated: $0
        ->  Partial Aggregate  (cost=2908960.58..2908960.59 rows=1 width=8)
              ->  Parallel Seq Scan on event  (cost=0.00..2866882.04 rows=16831415 width=0)
                    Filter: (pk_event > $0)
JIT:
  Functions: 8
  Options: Inlining true, Optimization true, Expressions true, Deforming true

using hardcoded value:-
Aggregate  (cost=11.42..11.43 rows=1 width=8)
  ->  Index Only Scan using event_pkey on event  (cost=0.57..11.36 rows=25 width=0)
        Index Cond: (pk_event > 1000755073)


texts: Insert into int8range from columns of another table

texts:
I have two tables in PostgreSQL:

Table indexces_grid:

+---------------+------------+
| Column Name   | Type       |
+---------------+------------+
| indexid       | bigint     |
| iprange       | int8range  |
+---------------+------------+

Table citydetails:

+---------------+-----------+
| Column Name   | Type      |
+---------------+-----------+
| ipfrom        | bigint    |
| ipto          | bigint    |
| cityname      | varchar   |
+---------------+-----------+

I want to insert the ipfrom and ipto into the single column iprange of the indexces_grid table.


texts: how to order a field based on two parts of the row value

texts:
I have a field with contents of the form
22/4,
30/8,
30/5,
30/3

I want it ordered in the form
22/4,
30/3,
30/5,
30/8

with the part after / ordered if the part before / is same!

Please help!!


texts:
I'm having trouble working out the details with an IIF containing some lookup statements.

The setup:

Dataset 1: Transactions
Fields: itemid, transactionnotes, rdesc

Dataset 2: EmployeeInfo
Fields: code, fname, surname

I have an expression column with the following:

=iif(Fields!itemid.Value = 100785,
    "Allocated to: "
    & lookup(cint(Fields!transactionnotes.Value), fields!code.value, fields!fname.value, "EmployeeInfo")
    & " "
    & lookup(cint(Fields!transactionnotes.Value), fields!code.value, fields!surname.value, "EmployeeInfo")
  , Fields!rdesc.Value)

itemid = 100785 indicates a line in the DB where it records a transaction allocated to another staff member. In all isntances where the itemid = 100785 the transactionnotes field (datatype string) will contain a numeric value of who it was allocated to. For those lines, this expression is working exactly as intended.

The problem:

For rows that do not contain itemid = 100785, the rdesc value should just be a string description of the transaction. For about half the rows this is working fine. But for about half the rows it shows #error. I've dug into the DB and the transactionnotes.value is NULL for all the rows getting #error. But its also NULL for the rows that are working. I'm including a screenshot so you can see the relevant values for itemid and rdesc for some rows that work and some that get #error. Description Calc is the field with the expression.


texts: Harden a public login to only let it retrieve a list of other logins, and do nothing else

texts:
I'd like my app's login dialog to give users a drop-down-list of login names on the server. So I'm wondering about the most secure way to retrieve this.

The best I can come up with is a login with a hardcoded password that can only access a stored procedure that'll return a list of other logins. What's the best way to harden this? I would love to create a login trigger that'll return what I want and then force the connection to close, or a trigger that will run for all statements, but this doesn't look possible..

Thanks!


texts: Updating a compatibility mode 80 database for SQL Server 2014

texts:
We have a SQL 2008 R2 server with a database on it that is running in compatibility mode 80. We are interested in upgrading 2008 R2 to 2014, but the upgrade adviser doesn't work because we can't force it to look for old syntax that works in compat 80 mode ( *= JOIN syntax for example), it just assumes the DB is a 2008 R2 DB, it ignores the compatibility mode.

What are our options here? We know it uses some deprecated syntax, and I can write some XQuery to check the cache for queries that use it, but is there anything else we can do that might be a little more concrete?


texts:
I'm just wondering because MySQL ENUMs are essentially tied to the table, how do they handle JOINs if two tables have the same ENUM? Are they join based on the ENUM value's or the string-key (input)?

For the follow-up see also, How does MariaDB handle ENUM types used in FOREIGN KEY constraints?


texts:
Can you please help me to understand, why this select examines 828004 rows (whole database) to return 21 rows?

# Time: 161121 14:00:44
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 2.507397  Lock_time: 0.000089 Rows_sent: 21  Rows_examined: 828004
SET timestamp=1479733244;
SELECT DATE(FROM_UNIXTIME(Epoch)) AS NodeDate, DeviceID as NodeID, Description,
AVG(AD0) AS AverageAD0,AVG(AD1) AS AverageAD1
FROM Data
WHERE DATE(FROM_UNIXTIME(Epoch)) >= CURDATE()
AND HOUR(FROM_UNIXTIME(Epoch)) >= HOUR(current_time())
GROUP BY NodeDate, NodeID
ORDER BY AVG(AD0) DESC, AVG(AD0) DESC;

Data table structure

mysql> DESCRIBE Data;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| Id          | int(10)     | NO   | MUL | NULL    | auto_increment |
| Epoch       | int(10)     | NO   |     | NULL    |                |
| Address     | varchar(32) | NO   |     | NULL    |                |
| DeviceID    | varchar(32) | NO   |     | NULL    |                |
| Description | varchar(32) | NO   |     | NULL    |                |
| AD0         | int(10)     | NO   |     | NULL    |                |
| AD1         | int(10)     | NO   |     | NULL    |                |
| AD2         | int(10)     | NO   |     | NULL    |                |
| AnalogZero  | int(8)      | YES  |     | NULL    |                |
| Lat         | varchar(32) | NO   |     | NULL    |                |
| Lon         | varchar(32) | NO   |     | NULL    |                |
| RSSI        | varchar(8)  | NO   |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
12 rows in set (0.00 sec)

Explain

+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra                                        |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------+
|  1 | SIMPLE      | Data  | ALL  | NULL          | NULL | NULL    | NULL | 822772 | Using where; Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------+

Indexes

mysql> SHOW INDEX FROM Data;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Data  |          1 | Id       |            1 | Id          | A         |      839767 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

The select is used to obtain averages from today AND current hour+.

This query is also examining all rows and looks super-simple. I have a 2-Core VPS and that is not handling such simple queries.

SELECT AVG(AD1) AS AverageAD1,AVG(AD2) AS AverageAD2, AVG(AD0) AS AverageAD0
FROM Data
WHERE DeviceID="xxxxxxxx"
AND FROM_UNIXTIME(Epoch) >= NOW() - INTERVAL 60 MINUTE;


texts: Will running pg_stat_reset cause performance issues?

texts:
I'm doing some index analysis on our database to see if there are indexes we can remove. To do this I want to run select pg_stat_reset(); to reset all stat counters back to zero, then just wait a week and see which indexes haven't been used.
Does running pg_stat_reset() also reset row count statistics? Will this affect the query planner?
Is it recommended to rerun Analyze public.#table# on all tables after you reset stats?
Thanks!


texts: Is row based or column based raw input better?

texts:
I have a vendor that is starting to give me some rather large reports on course completions and usage.

They have two formats for the reports:

If a student is enrolled in a course, they will have an row entry with basic student and course information for each course.  Fields would be student name, student id, student email, course name, course status, course completion date.
Each student would have one row.  Each course would be represented by a different column.  So we would have student name, student id, student email, course name A, course status A, course completion date A, course name B, course status B, course completion date B, course name C, course status C, course completion date C, course name D, course status D, course completion date D...

The good point of report #2 is that it is much smaller than the report #1.  However I am having a hard time getting a handle on executing views and jobs based on a variable column table (as courses get added I don't really know).

I need to make some advanced usage reports and wrap up student info with certain courses and completions.  Is there a reason why one of the raw formats will either make my job easier or make the database perform queries easier?


texts:
I have a MySQL database table where I store a user_id and a hash each time someone logs in. The hash is stored in a persistant cookie so it doesn't change as users log in and out of the site.

The table structure is:

CREATE TABLE `user_hashes` (
  `user_id` int(11) NOT NULL,
  `hash` varchar(32) NOT NULL,
  `hash_datetime` datetime NOT NULL,
  UNIQUE KEY `user_id` (`user_id`,`hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The purpose of this is to allow me to track which user accounts are used by the same person, or at least from the same computer.

I need to write a query that accepts a user_id and finds all other user_id's with matching hashes.

So far my query is as follows:

SELECT DISTINCT users.*
FROM user_hashes AS a
LEFT JOIN user_hashes AS b ON a.hash = b.hash
LEFT JOIN users on b.user_id = users.id
WHERE a.user_id = ?
ORDER BY users.id

This works for all hashes matched to the user_id in the where clause but it is possible to also match against hashes related to the matched user_id's?

I could write a loop in PHP that keep running queries against the list of matched users until there are no more unique matches left but is there a nice SQL way to achieve the same result?


texts: Failed to start MySQL Community Server. (start-limit-hit)

texts:
Since i made an update last Friday (2019-01-25), on "Linux Mint 18.3 Sylvia", the mysql-server has broken.

My first solution approach was as following:

sudo apt purge mysql*
sudo apt autoremove
sudo update-grub
sudo apt update
sudo apt dist-upgrade
sudo apt install mysql-server
sudo ufw allow mysql

And I get the following error messages:

user@computer ~ $ systemctl status mysql.service
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: failed (Result: start-limit-hit) since Mon 2019-01-28 10:29:06 CET; 4min 56s ago
  Process: 21680 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=1/FAILURE)

Jan 28 10:29:06 computer systemd[1]: Failed to start MySQL Community Server.
Jan 28 10:29:06 computer systemd[1]: mysql.service: Unit entered failed state.
Jan 28 10:29:06 computer systemd[1]: mysql.service: Failed with result 'exit-code'.
Jan 28 10:29:06 computer systemd[1]: mysql.service: Service hold-off time over, scheduling restart.
Jan 28 10:29:06 computer systemd[1]: Stopped MySQL Community Server.
Jan 28 10:29:06 computer systemd[1]: mysql.service: Start request repeated too quickly.
Jan 28 10:29:06 computer systemd[1]: Failed to start MySQL Community Server.
Jan 28 10:29:06 computer systemd[1]: mysql.service: Unit entered failed state.
Jan 28 10:29:06 computer systemd[1]: mysql.service: Failed with result 'start-limit-hit'.

user@computer ~ $ mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

According to this this side there should exist the following 3 config files after installing mysql-server:

ls /etc/my.cnf             # missing
ls /etc/mysql/my.cnf       # exists
ls /var/lib/mysql/my.cnf   # missing

However only one of them exits in my file-system and because i was curious about them, i looked on my notebook and there also only the second file exists and mysql works fine.

I am currently a bit stuck because the sites i found by doing research were all very specific and none of their solution approaches did either work or was not appropriate, because of similar error messages but in a different context.

Any help is appreciated, thanks!

EDIT:

$ mysql --version
mysql  Ver 14.14 Distrib 5.7.25, for Linux (x86_64) using  EditLine wrapper

$ ls /var/log/mysql/
error.log       error.log.2.gz  error.log.4.gz  error.log.6.gz
error.log.1.gz  error.log.3.gz  error.log.5.gz

$ systemctl show mysql.service
Type=simple
Restart=on-failure
NotifyAccess=none
RestartUSec=100ms
TimeoutStartUSec=10min
TimeoutStopUSec=10min
RuntimeMaxUSec=infinity
WatchdogUSec=0
WatchdogTimestampMonotonic=0
FailureAction=none
PermissionsStartOnly=yes
RootDirectoryStartOnly=no
RemainAfterExit=no
GuessMainPID=yes
MainPID=0
ControlPID=0
FileDescriptorStoreMax=0
NFileDescriptorStore=0
StatusErrno=0
Result=start-limit-hit
ExecMainStartTimestampMonotonic=0
ExecMainExitTimestampMonotonic=0
ExecMainPID=0
ExecMainCode=0
ExecMainStatus=0
ExecStartPre={ path=/usr/share/mysql/mysql-systemd-start ; argv[]=/usr/share/mysql/mysql-systemd-start pre ; ignore_errors=no
ExecStart={ path=/usr/sbin/mysqld ; argv[]=/usr/sbin/mysqld ; ignore_errors=no ; start_time=[n/a] ; stop_time=[n/a] ; pid=0 ;
ExecStartPost={ path=/usr/share/mysql/mysql-systemd-start ; argv[]=/usr/share/mysql/mysql-systemd-start post ; ignore_errors=
Slice=system.slice
MemoryCurrent=18446744073709551615
CPUUsageNSec=18446744073709551615
TasksCurrent=18446744073709551615
Delegate=no
CPUAccounting=no


texts: Including null values in unpivoted column

texts:
Consider the following table:

id  title   genre1  genre2  genre3  genre4  genre5
1   Movie1  Thriller    Crime   NULL    NULL    NULL
2   Movie2  Fantasy NULL    NULL    NULL    NULL
3   Movie3  Political   Philosophical   Historical  NULL    NULL
4   Movie4  Science Fiction NULL    NULL    NULL    NULL

When I unpivot genre1, genre2, ... the NULL values are removed, so that, for example, the rows for Movie1 would be:

id title  genre     original_column
1  Movie1 Thriller  genre1
1  Movie1 Crime     genre2

Is there a way to include NULL values, for example:

id title  genre     original_column
1  Movie1 Thriller  genre1
1  Movie1 Crime     genre2
1  Movie1 NULL      genre3
1  Movie1 NULL      genre4
1  Movie1 NULL      genre5

I can SELECT ISNULL(genre1, '*'), ISNULL(genre2, '*') ..., but suppose I want the values to appear as NULL? Is there a better way than:

WITH
-- SELECT that unpivots table
AS unpivoted
SELECT unpivoted.id,
  unpivoted.title,
  ,CASE unpivoted.genre WHEN '*' THEN NULL ELSE unpivoted.genre END
  ,unpivoted.original_transaction
FROM unpivoted

Also, I notice that the query that returns the source data to be unpivoted MUST have an alias. For example:

SELECT ... FROM
  -- *must* give it an alias or it's a syntax error
  (SELECT ... FROM) AS source
  UNPIVOT(genre for original_column IN (genre1,genre2, genre3,genre4,genre5))

Does anyone know why? I have not found a way to refer to the alias anywhere in the query.


texts: Can't create a table with the same name I deleted before

texts:
I started to use SQL Server recently and I think that its simple to resolve

I did:

1 - Create table table_nome

2 - Drop table table_nome

3 - I'm trying to create a table with the same name, and stay with error in the table_name, written: "There is already an object named 'table_nome' in the database."

How can I fix it and create a table?

if have some errors in the writing, I'm sorry, I'm from brazil and dont know English very well -

I'm using SQL server 2012.


texts: converting varchar to number while passing to IN clause

texts:
I am really stuck here..

Given below is the miniature of what I am trying to achieve.

set serveroutput on;
declare
rajesh varchar2(20);
x number(10);
begin
select wm_concat(stocks_id) into rajesh from users_stocks where users_id=28800;
select count(*) into x from users_stocks where stocks_id in(rajesh);
dbms_output.put_line(x);
end;
/

Here is the error report

Error report:
ORA-01722: invalid number
ORA-06512: at line 6
01722. 00000 -  "invalid number"
*Cause:
*Action:

The data type defined for stocks_id is number, and rajesh here is varchar2, that is why is this error is getting generated. I need some way by which rajesh comes as number to IN clause.
As I stated this is just miniature I can not do something like this

select count(*) into x from users_stocks where stocks_id in(select some column from someTable);

Please suggest. Thanks in advance.


texts: Get last modified date of table in postgresql

texts:
I want know the last modified date of table in postgresql. In SQL Server  can get using

SELECT modify_date FROM sys.objects

How to get same thing in Postgres? I am using Postgres 9.4


texts:
I have to update a bigint[] column named permissao_ver.
Example value: '{1,2,3,4,5,11,44,56,75,11}'.

Pseudo code:

UPDATE callcenter.pausa
SET permissao_ver = '{"(SELECT cod_grupo FROM crm.usuariosgrupos
                        WHERE habilitar = 1)"}'::bigint[]
WHERE habilitado = 1 AND permissao_ver is null

I need to get the codes the above select will return in a format matching the bigint[] column. Like: '{XX, XX, XX, X, XX, X}'.


texts: How to compare timestamps with different time zones?


texts: Selecting only columns where sum(column) > 0

texts:
I have a table like this:

Date     Bob     Joe     Will
1/1/15    1       0        1
1/2/15    0       0        1
1/3/15    0       0        0
1/4/15    1       1        1

Preferred solution

With one statement, I want to return only the columns where SUM(Column) > 0

For example:

SELECT SUM(Bob) as Robert, SUM(Joe) as Joseph, SUM(Will) as William
  FROM table
 WHERE Date BETWEEN '1/1/15' and '1/3/15'

I want my result to look like this:

Robert     William
  1           2

In other words, since SUM(Joe) = 0 for my selected date range, I don't want the column Joseph to appear in the result set. What is the magic that would cause Joseph to not appear?

Alternate Solution

I can work with the data if it comes back slightly differently, so I have considered writing a table function to return a 2-column table like this:

Name        Value
Robert      1
William     2

My thought is that the function code would look similar to this:

--Declare table here
IF SELECT SUM(Bob) > 0
  INSERT INTO temp (Name, Value)
    ('Robert', SELECT SUM(Bob))

Lather, rinse, repeat for each of my columns. I know this isn't ideal since it would need maintenance should there ever be a new column that needs to considered, but there's other maintenance in that situation anyway, so I could handle it.

In Summary

Is there a way to create a query that would give me my preferred solution? If not, are my thoughts on my alternate solution reasonable?


texts:
I have an Oracle 11gr2 DB, and I have created an editioning view called TEST which I'd now like to drop.

I run

DROP VIEW TEST;

as DBA, and get the following error:

ORA-04045: errors during recompilation /revalidation of USER.TEST
ORA-00001: unique constraint violation (SYS.I_EV2) violated

I know the editioning view has errors: that's why I want to drop it.SYS.I_EV2 looks like some kind of system constraint, and I don't want to touch it.

The only reason I want to drop the editioning view is that I want to create another view on the same underlying table, and I get an error message if I try and create a second editioning view on the same table.


texts: Sync postgreSQL with SQL Server database- on data change event

texts:
I have one PostgreSQL database and its data are being changed regularly. I want to sync all its data with my SQL Server database. One thing I can do is use query to check all required tables and columns whether data is same or changed. If changed, then update the same in SQL Server.

Is there any other method is available(like replication)? Or any synchronization software available?


texts:
I have the following problem.

SELECT * FROM tgvbn();
ERROR:  function tgvbn() does not exist
LINE 1: SELECT * FROM tgvbn();
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Since Appendix A of the official documentation only states error_codes and conditional names but not the actual messages, one can only guess that "No function matches..." refers to 42883 / undefined_function.  I can set log_error_verbosity = verbose in postgresql.conf - but that will affect the logs, not the messages returned to the client:

  Controls the amount of detail written in the server log for each
  message that is logged.

Well, I can consult the logs for this piece of information, but is there any means of including it in the error messages?


texts: How can I find age difference using birthdates in MYSQL?

texts:
•Students(stuID: String, stuName: String, gender: String, birthdate: Date, enterYear: Year, gpa: Float)

•Departments(deptName: String, numPhDs: Integer)

•ProfessorWorks(profID: String, profName: String, deptName: String)

•CoursesOffer(cNo: String, cTitle: String, deptName: String)

•Majors(deptName:  String, stuID: String, degreeProgram: String, attendYear: Year, attendSemester: String)

•Sections(cNo: String, semester: String, sectNo: String, profID: String)

•Enrolls(stuID: String, cNo: String, sectNo: String, semester: String, grade: String)

I want to know the age difference between the oldest and youngest student(s) in MYSQL.

SELECT MAX(DATEDIFF(NOW(),'BIRTHDATE') - MIN(DATEDIFF(NOW(),'BIRTHDATE')
FROM STUDENTS;


texts: How to create trigger on database that 'triggers' when mirroring role changes from mirror to principal

texts: I want to perform some sql when my database mirroring state changes


texts: One filegroup, multiple datafiles, how to get list of tables in each file

texts:
In one of my production databases, I have a filegroup with 3 data files sitting on 3 different disks. There are over 50 tables on this filegroup.

How do I identify the location of each of the 50 tables between the 3 data files? I need to know on which data file does the clustered index for each table reside.

Example :

Table A : Datafile 1 : FG1
Table B : Datafile 2 : FG1
Table C : Datafile 1 : FG1
Table D : Datafile 3 : FG1


texts: Database for querying a probabilistic automaton

texts:
I've got a probabilistic automaton of 4000 states, with 20 attributes attached to them, and 60 000 transitions between the states. I would like to, given a defined sequence of states, find all valid transitions between them. For example a user might define a sequence for three states, with only a certain amount of attributes defined, and I would like to return all valid transitions between these states.

Is there a database which could  be suitable for this kind of task?


texts: How to Set up SSRS Web Services to run under another port than 80?

texts: What steps do I have to pace to run Web Service URL and, Report Manager URL, under another port rather than 80? (DBMS is SQL Server 2008R2 and OS is WIn Server 2008R2).


texts: Powershell connecting to Azure SQL DB errors out using Active Directory Password authentication

texts:
The following powershell script connects to an Azure SQL database using Active Directory Password authentication.
Add-Type -Path ('C:\Program Files\PackageManagement\NuGet\Packages\Microsoft.Data.SqlClient.2.1.3\runtimes\win\lib\netcoreapp2.1\Microsoft.Data.SqlClient.dll') -ReferencedAssemblies Microsoft.Data.SqlClient.SNI
 $con = New-Object Microsoft.Data.SqlClient.SqlConnection
 $con.ConnectionString =  "Server=stage.database.windows.net;Authentication=Active Directory Password;Initial Catalog=DBname;UID=userid;PWD=passwd;Trusted_Connection=False;Encrypt=True;Connection Timeout=60;"
 $con.Open()
 $query = "select * from [dbo].[testing_table]"
 Add-Type -AssemblyName System.Data
 $cmd = New-object Microsoft.Data.SqlClient.SqlCommand($query,$con)
 $adp = New-Object Microsoft.Data.SqlClient.SqlDataAdapter $cmd
$data = New-Object System.Data.DataSet
$adp.Fill($data) | Out-Null
 #$ds = New-Object Microsoft.Data.DataSet ($adp).fill($ds) | out-null
 $data.Tables[0]
 $con.Close()

When executed in Powershell 7, the script returns with the error:
Line |
   8 |   $con.Open()
     |   ~~~~~~~~~~~
     | Exception calling "Open" with "0" argument(s): "The type initializer for 'Microsoft.Data.SqlClient.SqlAuthenticationProviderManager' threw an exception."
Line |
  15 |  $adp.Fill($data) | Out-Null
     |  ~~~~~~~~~~~~~~~~~~~~~~~~~~~
     | Exception calling "Fill" with "1" argument(s): "The type initializer for 'Microsoft.Data.SqlClient.SqlAuthenticationProviderManager' threw an exception."

Is there something missing in the script? How can we make it work for multi-factor authentication as well?


texts:
We use transaction replication to replicate our production database to another server(server 1) for reporting purpose. For a standby copy, we also logship the main database to another server (server 2).

Last week I had to reinitialise the standby copy.
However, after restoring the production database on Server 2; under the replication publications node, I see the database has been published, and under that, I see the subscriber server.

The Server 2 is not configured for replication.  Because the secondary database is on Standby\Read Only mode, the system is not allowing me to make any modification.

How can I remove the replication configuration from the secondary server?

Many thanks


texts:
I am getting these errors on my dedicated server because of which database goes into recovery mode.

I have seen the following error in SQL server error log file:

2018-01-20 05:32:19.83 spid7s      LogWriter: Operating system error 1117(The request could not be performed because of an I/O device error.) encountered.
2018-01-20 05:32:19.83 spid7s      Write error during log flush.
2018-01-20 05:32:19.84 spid284     The client was unable to reuse a session with SPID 284, which had been reset for connection pooling. The failure ID is 46. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
2018-01-20 05:32:19.84 spid335     Database newtrack was shutdown due to error 9001 in routine 'XdesRMFull::Commit'. Restart for non-snapshot databases will be attempted after all connections to the database are aborted.
2018-01-20 05:35:03.35 spid22s     Recovery completed for database aa(database ID 7) in 151 second(s) (analysis 18856 ms, redo 115386 ms, undo 2023 ms.) This is an informational message only. No user action is required.

At the same time following errors are generated in event viewer:

Application Event
EventID:17053, MSSQLSERVER
LogWriter: Operating system error 1117
    (The request could not be performed because of an I/O device error.) encountered.
Error 1/20/2018 5:32:19 AM MSSQLSERVER 18056 Server
Error 1/20/2018 5:32:19 AM MSSQLSERVER 9001 Server
Error 1/20/2018 5:32:19 AM MSSQLSERVER 9001 Server
Error 1/20/2018 5:32:19 AM MSSQLSERVER 17053 Server

System Event:
The IO operation at logical block address 4069 for Disk 1
    (PDO name: \Device\00000045) was retried.


texts:
I have a question regarding inner workings of UPDATE statements with regards to SQL Server. I am trying to understand what will happen if the following 2 update statements are received or serviced at the same time:
 |    Session 1 - Statement 1 | Session 2 - Statement 2
 |   -------------------------+-------------------------
 |    update dbo.Table1       |  update dbo.Table1
 |    set Value = 10          |  set Value = 20
 |    where ID = 1            |  where ID = 1
 ▼    and Value = 0;          |  and Value = 0;
(t)

From what I understand, update will first select the row which needs to be updated with a shared lock which means that both update statements can select specific row. It then asks for an exclusive lock for updating the column values. So, it seems that the result would the Value being set to 20.
Am I missing something or is my understanding correct?
The isolation level at the connection is set to READ UNCOMMITTED.


texts: How to delete archive log on just one dest?

texts: I have two archive_log_dest, I need to delete archivelog on dest1 until today. I need to keep on archive_log_dest2 all archive. How can I do that with rman?


texts: Logging procedure, package errors to OS's Syslog

texts:
Good day,

Setup: Oracle 11G on Solaris 10 x86

We'd like to capture the errors/failures of our nightly scheduled procedures/ packages in the OS's syslog. That log will then be sent to a remote NMS managed by the NOC who'll alert the on-call personnel. Can and how can this be done?

Thanks.


texts: How to use Unique key via combinations of table fields?

texts:
Take a look at the following sqlfiddle: http://sqlfiddle.com/#!2/dacb5/1

CREATE TABLE contacts
    (
     id int auto_increment primary key,
     name varchar(20),
     network_id int,
     network_contact_id int
    );

INSERT INTO contacts
(name, network_id, network_contact_id)
VALUES
('John', 4, 10),
('Alex', 4, 11),
('Bob', 4, 12),
('Jeff', 4, 45),
('Bill', 7, 11),
('Walter', 7, 45),
('Jessie', 7, 360) ;

I have a basic table of contacts. The network_id and network_contact_id fields contain id numbers that link to other tables.

I want to be able to run INSERT IGNORE queries to this table, but I want to use the combination of the network_id and network_contact_id as the unique key to match against.

So for example, if I tried to insert a contact that had network_id = 4 and network_contact_id = 12, the INSERT IGNORE query would see that entry already exists, and ignore any error that was thrown.

So basically, network_id is not unique. network_contact_id is not unique. But the combination of the two is unique. How do I set this up? Would I have to have a single other field that is the concatenated values of the two other fields? Or is there a way to setup the keys for this table so it will do what I need?


texts: Permission issue with a view built on top of foreign tables

texts:
I am consolidating data from several postgresql databases, all with the same schema, into a new one. To do that I mapped the tables I am interested in the new DB as foreign tables with postgres_fdw. Each source DB is mapped in its own schema: source1, source2, ..., sourceN. Then to analyze the data as a whole, I built views on top of the foreign tables. The view definition basically is this:

CREATE OR REPLACE consolidated_view AS
SELECT "source1" AS source, * FROM source1.table UNION ALL
SELECT "source2" AS source, * FROM source2.table UNION ALL
...
SELECT "sourceN" AS source, * FROM sourceN.table ;

I did that, and it worked like a charm for many tables. But one is resisting me. I keep getting this error:

ERROR:  permission denied for relation product_alias
CONTEXT:  Remote SQL command: SELECT * FROM public.table

But when I try to do a SELECT * FROM sourceX.table, for each source in 1..N, i get the results I want.

Don't know if I am completely off track here, but what I am not very clear on, and could explain the issue, is the identity under which the remote tables will be accessed throught the view, compared to when I do a SELECT on an individual foreign table. We have two different users involved: one "admin" who owns the view, one "user" as who will execute the query on the view. Both have as USER MAPPING defined. I tried to increase the log verbosity, have all sorts of message pop in the console as the query is executed, adding those settings:

client_min_messages       debug5
log_min_error_statement   debug5
log_min_messages          debug5

But did not find much useful information that way. Any help to the least to improve the way I am trying debug this issue would be much appreciated.


texts:
I want to make a small web app using Postgres as database.

Is there a way to accept database access if and only if the SQL command is coming from my app, blocking any other access to the database?


texts: Wrong results on queries with join for some MariaDB versions

texts:
I found that on some versions of MariaDB server some simple queries return wrong results for certain data.

I have 2 tables:

CREATE TABLE `tab_parent` (
  `id` bigint(12) unsigned NOT NULL AUTO_INCREMENT,
  `f_date` date DEFAULT NULL,
  `f_value` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE TABLE `tab_child` (
  `id` bigint(12) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` bigint(12) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),KEY `parent_id` (`parent_id`),
  CONSTRAINT `fk_2` FOREIGN KEY (`parent_id`) REFERENCES `tab_parent` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

And a view which counts how many children has a parent:

CREATE VIEW `tab_parent_view` AS
  SELECT `tab_parent`.*,
  COUNT(`tab_child`.`id`) AS `child_count`
  FROM (`tab_parent` LEFT JOIN `tab_child` ON(`tab_child`.`parent_id` = `tab_parent`.`id`))
GROUP BY `tab_parent`.`id`;

With a sample data like here: https://dbfiddle.uk/?rdbms=mariadb_10.2&fiddle=0a89cb2a4bec635766313c3c59cd923c the 2 queries do not return the same number of records for versions 10.2.27 and 10.4.8 of MariaDB. But the results are correct for versions 10.1.43, 10.3.15, 10.3.16 and 10.4.12.

SELECT * FROM tab_parent_view WHERE f_date>='2020-02-07' AND f_date<'2020-02-08';
SELECT * FROM tab_parent      WHERE f_date>='2020-02-07' AND f_date<'2020-02-08';

For the view only 2 records are returned, but for the table all the 4 existing ones:

Is this a known bug in MariaDB? I would like to know what causes this and on which versions is it fixed. For MySQL I did not found this problem.


texts: Algorithm for finding the longest prefix

texts:
I have two tables.

First one is a table with prefixes

code name price
343  ek1   10
3435 nt     4
3432 ek2    2

Second is call records with phone numbers

number        time
834353212     10
834321242     20
834312345     30

I need write a script which find longest prefix from prefixes for each record, and write all this data to third table, like this:

 number        code   ....
 834353212     3435
 834321242     3432
 834312345     343

For number 834353212 we must trim '8', and then find the longest code from prefix table, its 3435.
We must always drop first '8' and prefix must be in the beginning.

I solved this task long time ago, with very bad way.
Its was terrible perl script which do a lot of queries for each record.
This script:

Take a number from calls table, do substring from length(number) to
1 => $prefix in the loop
Do the query  : select count(*) from prefixes where code like '$prefix'
If count>0 then take first prefixes and write into table

First problem is query counts - it's call_records * length(number).
Second problem is LIKE expressions. I am afraid those are slow.

I tried to solve the second problem by:

CREATE EXTENSION pg_trgm;
CREATE INDEX prefix_idx ON prefix USING gist (code gist_trgm_ops);

That speeds up each query, but did not solve problem in general.

I have 20k prefixes and 170k numbers now, and my old solution is bad. Looks like I need some new solution without loops.

Only one query for each call record or something like this.


texts: Any ideas on how to parallelize mysql_upgrade?

texts: I am migrating from 5.5 to 5.6 and would like to speed up the mysql_upgrade process. I have thousands of databases per machine and 200 tables (innodb) per db. Any suggestion how to speed up the upgrade process?


texts: How to get the total days per month between two dates?

texts:
Given two dates like 20120302 and 20120605

I need to get a list of Months and the total days in those months that fall between those two dates, like so:

March 28
April 30
May   31
June  03


texts:
I was reading postgresql replications solution but, even I just starting understaning how it works, another doubt has arrisen.
I'll be using postgres internal replication solution but as far as I understand, every event will be replicated just as it is achieved; for example, if I insert some data into the master, automatically it will be replicated to the slave...Am I right?

Well, is that so, I was searching for way of scheduling this replications as a passive backup but the thing is, I need for example, every monday on the night the database gets replicated with all the data it had inserted in it that day to a slave database in Amazon EC2 virtual servers. I was thinking on using cron as a first approach but reading postgres documentation I got aware that practically only configuring the necessary files, postgres do it for you...

So, how could I schedule replication in just desired moments?

PD.-The schedule may vary so it needs to be completely generic, also...I'm using Ubuntu 12.04 and PostgreSQL 9.1 as in the master as in the slave.
Thanks


texts: Can mirror and witness have same port number?

texts:
When configuring database mirroring, the mirror and the witness have generated the same port number. When I hit next, it errors out with:

  End points for instances for the same server must use different port numbers. Your instances have conflicting port numbers....

Can they have the same port number?


texts: Select only rows with minimum values from union table

texts:
SQL Server 2005

Here is a sampling of the data from a union of two tables (named "vProducts_All"):

DBID    UPC             Price1          Price2
1       023614025559    689.0000        649.0000
2       023614025559    632.0000        602.0000
1       011457891333    252.0000        232.0000
2       011457891333    284.0000        254.0000
1       123451234512    199.0000        159.0000
2       123451234512    195.0000        162.0000

I want to return only the rows showing the minimum for Price1 field, IN ADDITION TO all of the other records that didn't have duplication, something like:

DBID    UPC             Price1      Price2
2       023614025559   632.0000     602.0000
1       011457891333    252.0000        232.0000
2       123451234512    195.0000        162.0000
+ All the other records that didn't have duplication in the UPC field

Have tried in vain using joins to derived tables, but nothing works. Any help appreciated


texts: NOT IN statement or what to use in this case?

texts:
I need to delete each row in fabricante_producto_solicitud (fps from now on) table if fps.id has not relation (NOT IN) fabricante_modelo_marca_producto (fmmp from now on).

Since my input will be an array of values (fps.id) as for example:

array(1,2,3,4,5)

How, if it's possible, using NOT IN I can know which fps.id is on fmmp table and which not? In this case fps.id aren't in fmmp can be deleted the existent not. Any advice or any other way to achieve this?

This is the model (just for info purposes)

Doing some tests:

Based on the answer by the user I modified a bit the original query and comes with this:

DELETE
FROM
    negocio.fabricante_producto_solicitud
WHERE
    negocio.fabricante_producto_solicitud.id NOT IN (
        SELECT DISTINCT
            negocio.fabricante_modelo_marca_producto.fabricante_producto_solicitud_id
        FROM
            negocio.fabricante_modelo_marca_producto
    )
AND negocio.fabricante_producto_solicitud.id = :someParameter;

Can I pass an array as :someParameter value or I need to iterate for the values and execute the query several times?


texts: How do I handle a single `reference` to multiple different types of data?

texts:
I host a file sharing site.  The idea is to take a short string (e.g. abcd) and do one of the following:

serve an image
display some text
redirect to another url

Historically, I've hosted this site using non-relational databases, where I'd store it as something like:
{
  short: "abcd",
  type: "url", // or image or text
  // type specific data, e.g. for a url
  url: "https://google.com"
  // or for a file
  store: "/cool_store",
  filesize: 10,
  etc.
}

I've redesigned the platform and decided to use SQLite / Postgres instead.  I'm not that used to schema design and my first design of (not actual code, heavily simplified):
CREATE TABLE Items (
    short varchar(64),
    store varchar(255),
    filesize int
);

Which worked great for the image hosting side of things.  Now I want to expand to the other types and can't decide the best approach.  My two main ideas are:

Replace the singular table with one 'reference' table and separate 'item' tables for each different type, e.g. References { id: 'abcd', type: 'url', ref: 1 } (where the ref represents the primary key of a different table).  Items { id: 1, store: "/cool_store", filesize: 10 }.  Urls { id: 2, url: "https://google.com" }, etc.
Store all the properties required on a single table with a large proportion of the columns being null.  In total we require ~50 columns, with ~30 being null at any point in time.

The database is exceptionally read-heavy, maybe 99.99% reads.


texts: A tail-log backup of the source database will be taken ? Why I am seeing this warning

texts:
I took a backup of a database from 1st server (which runs SQL Server 2014).

When I try to restore the backup to a 2nd server (which runs SQL Server 2012) I get this warning message:

  Warning message - a tail-log backup of the source database will be
  taken. View setting on the Options page.

If I ignore this message and try to restore, it fails.

Any idea ? What should I do ?


texts: Data not showing in English, unless you click on it - In sqldeveloper

texts:
I have installed OracleXE and imported data into it.  From SQLPLUS, I can query the data and it all shows up in English.

However, in SQLDEVELOPER, if I look at the data, it's in a nonsense format, unless I double click on it, where it turns into english?  If I query the data through the SQL Worksheet, it displays in rubbish format, but again click on a data item, and it turns into English again?

Before double-clicking:

After double-clicking:

I am utterly confused.

Any ideas?


texts: Virtual Column - Using it in view (Oracle)

texts:
I have made a virtual column in Oracle, using the following code.

CREATE OR REPLACE function email_address (ID_ varchar2)
return varchar2
deterministic
as

lname varchar2 (256);
snumber varchar2 (256);
 email varchar2 (256);
    BEGIN
    select substr( p.name, instr( p.name, ' ', -1 ) + 1 ) into lname
    from person p where p.id = id_;

    SELECT regexp_replace(p.service_no, '[^0-9]*', '') into snumber
    FROM person p where p.ID = id_;

  email:= snumber||lname||'@met.af';

return email;
end email_address;

The virtual column works fine and it does populate what i want to acheive in the virtual column. But the problem arises when i make a view using virtual column's table; population of view takes hell lot of time (max 5 mins). Here, i would like to mention that the view works perfectly fine if i don't use the function (i.e empty column of email). The code for view is as under

select  distinct
    person.SERVICE_NO as Service_No,
    person.CNIC_NO as CNIC, person.NAME as NAME ,
    card.CPLC_SERIAL_NO as Card_Number,
    child_dc.NAME as Child_DC,
    root_dc.NAME as Root_DC, person.OU as OU,
    person.EMAIL as Email
from

 person_card inner join person
 on person_card.PERSON_ID = person.ID
 inner join card
 on person_card.CARD_ID = card.ID
    left outer join child_dc
 on person.CHILD_DC_ID = child_dc.ID
    left outer join root_dc
 on child_dc.ID = root_dc.ID;

What i presume is that when i make a virtual column, oracle forcibly asks me to keep the datatype length to 4000, therby, making it too large or heavy to populate. What should i do to make the view populate. I need to have a virtual column as email is not being entered by the application. Help required.


texts: In where clause, does MySQL continue checking conditions if first condition before AND falsy

texts:
I have a question about and AND inside a where clause of a query. Within the where clause, I have a subquery but it is after a few other, row value conditions, like validated = 1 AND (subquery). I have a question about the AND...

How does MySQL treat it? Will it continue to the subquery if the first condition in the where clause, before the AND is false?


texts: Subtracting dates give me error in Postgresql

texts:
I use age to get the interval between to date fields. Their type is timestamp.

My code is as below:

select age(timestamp MySecondDateField, timestamp MyFirstDateField)

The data in my field contains:

2014-01-26 09:53:17.036359

How should I subtract two dates?

When I use age as below it works:

select age(timestamp '2001-04-10', timestamp '1957-06-13')


texts: Split mile limits to the thousandth based on ID

texts:
I have a table like this in sql server 2014:

ID | Start_mile|End_mile
1  |5.23       |7.464
2  |2.333      |6.124

What I want to do is to split the miles into thousandths per ID like this:

ID |start_mile|end_mile
1  |5.230     |5.231
1  |5.231     |5.232
1  |5.232     |5.233
....
1  |7.463     |7.464
...

Any ideas as to how to go about doing this? I am trying to stay away from Cursors, unless that's the only way to do this.

I have been able to get this query together , but not sure how to incorporate it with the ID, and mile limits I have so that it runs for the whole table without the declared From and To variables:

DECLARE @from decimal(15, 3) = 0.980;
DECLARE @to decimal(15, 3) = 1.024;

;WITH cte AS
    (SELECT @from AS Value
     UNION ALL
     SELECT CONVERT(decimal(15, 3), Value + 0.001)
     FROM cte
     WHERE Value < @to)
SELECT *
FROM cte
option (maxrecursion 0)


texts: Are there any risks on using 'single-transaction' and 'extended-insert' on mysqldump?

texts:
I'm new in db administration and I've looked for a faster import of a mysqldump.

I had a db with about 1.1 million rows and mostly InnoDB tables and wanted this to import on a managed server. Before I used the standard mysqldump command for dumping the db. At import I had a write speed of about 60.000 rows per hour which was extremly slow.

I searched for a way to speed thingsup and found a post here saying to use --single-transaction and --extended-insert on mysqldump. Now the dump is importing in under 30 seconds.

After a quick overview it seems that the data is correctly imported but I'm a little bit leery about the fast import.

Why is this soo fast after adding these two options?
Are there any disadvantages on using this options?

Thank you for your help.


texts:
According to MySQL 5.6 doc, the supported range for datetime data type is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. But when I tried inserting values that are earlier than '1000-01-01 00:00:00', I received no error and the values were successfully stored in the table.

CREATE TABLE `test` (
  `a` datetime(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

mysql> insert into test values ('999-01-01 01:01:01');
Query OK, 1 row affected (1.18 sec)

mysql> select * from test;
+-------------------------+
| a                       |
+-------------------------+
| 0999-01-01 01:01:01.000 |
+-------------------------+

mysql> insert into test values ('1-01-01 01:01:01');
Query OK, 1 row affected (0.08 sec)

mysql> select * from test;
+-------------------------+
| a                       |
+-------------------------+
| 0999-01-01 01:01:01.000 |
| 0001-01-01 01:01:01.000 |
+-------------------------+

Does this mean that the documentation is wrong? I am using mysql version 5.6.21.


texts:
Every single user has say, 3 of GROUP_A, 10 GROUP_B's per GROUP_A, and 20 GROUP_C's per GROUP_B. And each of the 20 GROUP_C's involve lots of inserts/deletes...

I'm not an expert, but I've done research but it's all theoretical at this point of course, and I don't have hands on experience with the implementation that's for sure. I think my options are something like 'adjacency lists' or 'nested sets'?

Any guidance into the right direction would be very much appreciated!


texts:
Window account is getting the below error when trying to connect into the SQL instance via management studio 2014.

The SELECT permission was denied on the object 'configurations', database 'mssqlsystemresource', schema 'sys' (Error 229)

The SQL instance is 2014, the user doesn't get the error if I make the login sysadmin.

The login has connect permission to the instance and even if i give
it dbowner to the master database it cannot connect.
Default database is master also.
It is not an orphaned user
I have tried the GRANT VIEW DEFINITION - no luck
I have checked and the login doesnt have any DENY permissions

When i go into the properties of the login > Securables > effective tab > it gives the error:

The SELECT permission was denied on the object 'fn_my_permissions', database 'mssqlsystemresource', schema 'sys'.

So that is obviously related also

Any ideas?


texts: MySQL: Using DATE_SUB to subtract business days instead of all calendar days

texts:
I am looking for a robust solution to subtract the number of business days from a date. I am using DATE_SUB however it subtracts all calendar days.

For example, if today is Monday and I subtract 2 days from today, I would like for the result to be Thursday last week and not Saturday.


texts: Finding or confirming absence of corrupted data in MongoDB

texts:
Background

I have a directory full of .gz files that were produced by mongodump on a mongod running MongoDB 3.4.x, which I am writing into a different instance running MongoDB 3.4.2 using mongorestore ... --numInsertionWorkersPerCollection 8 --drop --nsFrom ... --nsTo .... All but one are getting loaded properly. They all take between 1 and 3 hours to load the data, and then another 2-4 hours to rebuild indexes. The problematic file hangs before the step below, and I usually kill the process externally after about a day. I've reproduced the scenario 4 or 5 times.

finished restoring database.collection (N documents)

During this time the mongorestore process is still visible from the shell, but does not have a corresponding entry in db.currentOp(), and the size of the database is not increasing.

These errors were returned right before the log line "restoring indexes for collection database.collection from metadata":

2017-03-13T09:30:04.218-0400    demux namespaceHeader: {database collection true 726142452659775357}
2017-03-13T09:30:04.218-0400    demux checksum for namespace database.collection is correct (726142452659775357), 54599167880 bytes
2017-03-13T09:30:04.218-0400    demux finishing (err:corruption found in archive; I/O error reading length or terminator ( gzip: invalid header ))

I have found a couple reports of mongorestore hanging (e.g.), and some documentation of issues with /s in namespaces in older versions (e.g.), but I haven't found a description of quite my situation.

Question

Now I find myself with some indication that a file I have loaded may have been corrupt, or at least had a corrupt header. Issuing realistic queries to the collection in question and its indices returns results that look reasonable. How can I definitively detect or rule out "corruption" of this type? What indeed does "corrupt" mean in this context? Can I expect to find it in the database, or only within the zip file?

Update: At @mustaccio's suggestion I ran gzip -t on the problematic file and it reported "trailing garbage". Is this sufficient to pin the problem reported by MongoDB on a bad input and stop looking for corruption in the database?


texts: aggregating sums for specific time but would like to have multiple times in the same query

texts:
I am trying to sum the number of orders for two years and have them side-by-side but am not sure how to do this. To summarize one year, I currently have:

select menu_item_id,
       menu_item_header,
       sum(quantity)
from line_items
where order_id in ( select id
                    from orders
                    where menu_id=807
                      and stripe_transaction_state='paid'
                      and created_at between '2019-01-01'
                                         and '2020-01-01' )
group by menu_item_id, menu_item_header order by menu_item_id;

which gives me:

| menu_item_id | menu_item_header    | sum |
|--------------|---------------------|-----|
| 21562        | bacon-wrapped dates |  82 |

but would like

| menu_item_id | menu_item_header    | sum 2018 | sum 2019 |
|--------------|---------------------|---------|----------|
| 21562        | bacon-wrapped dates |  82     | 103      |

One problem is that the menu_item_header can be different? Is there a way that I can take the first one and ignore the rest? I could also join it to the menu_items table too.

How would I do this?


texts:
I have a Windows Server 2008R2 Server, running SQL Server 2008R2 SP2. The server currently only does a full backup once daily. The server's operational hours are between 8am and 5pm. The server's new RTO is 0mins and it's RPO is 0mins during operational hours. What is the best option in achieving this? I have currently thought of an AlwaysOn Availability Cluster. As for backups I have thought of Transactional Log backups every 15mins, Differential backups every 2 hours, and keeping the Full backup every day. Is my plan sufficient?

Also with an AlwaysOn Availability Cluster, do I just need 1 additional server to be a replica or do I really need a Primary Replica and Secondary Replica? (still very confused about AlwaysOn...)

If I implement AlwaysOn, do I really need to backup each server?

If I implement AlwaysOn, do I really need to use a SAN?

Are there any performance impacts doing backups doing business hours?


texts:
Some job descriptions mention required knowledge of SQL standards. What do they mean by that?

I found something on wiki http://en.wikipedia.org/wiki/SQL#Standardization but I don't really know what to think of it.


texts: Installation of Microsoft SQL Server Data Tools Hangs

texts: I'm attempting to install SQL Server Data Tools, but the installation is hanging.  It had an error the first attempt; and I've restarted the machine (as that seems to help in those scenarios).  Now it just sits there on the tools for Visual Studio not installing (I've left it running over night).  Any suggestions on what to do here?


texts:
I am trying to a view with "With" clause in Teradata but it is not allowing to create a view. Please suggest with any options.

Given below is the query which I have tried:

create view derived_table(derived_column)
AS
(
  With temp_table(a,b,c)
  As
  (select a,b,c from table_a where column1=column2)
select (a||'-'||b) as derived_column  from  temp_table
union all
select (a||'-'||b||'-'||C) as derived_column from  temp_table
)


texts: Calculating a percentage discount in SQL

texts:
Basically just started using sql and having a minor problem.
Working out the percentage of a number.

Example: What would I need to type in to work out:

2.75 - 5%

Drink_price= 2.75
After 5%discount = 2.61

So I need to deduct 5% of the price as a discount.

Sql doesn't seem to like the '%' key.


texts: Should I capture all the relationships in a Database design?

texts:
I am a newbie when it comes to database design. I was trying to create a blogging application and I ran into a problem. My database consists of the following tables:

User
HasInt (Records which user has which interest)
Interest (category of blogs: Sports, Literature etc)
Follow (Records which user follows which user)
Blog
Comment
From what I realise, I should not have anything to do with knowing how many comments have been made for any particular Interest. But I can see that there is a many to one relationship between Comment and Interest. Should I still record this information ?
If so, why ?


texts: What's the more performant database design for query speed and write overhead when storing many boolean columns?

texts:
Setting the scene

I have a PostgreSQL 11 database running on a very fast 64core, 10 SSD HW RAID 10 server. In the database I need to create a table with a couple of million rows, let's call it cars. The rows will see very frequent updates and even more frequent read. Very heavy usage. The cars have many different equipment attributes like has_electrical_side_mirrors, has_automatic_gearbox, has_gps_navigation etc. etc. About 150 of them, all boolean. On top of this there is gonna be something like 40 other columns of strings and integers. I want a design specifically for storing those 150 boolean values.

My ideas

I can basically see a couple of designs of which I think two will be superior from a performance and storage overhead point of view:

Add 150 boolean columns
Add a join table with equipment
Add an intarray column with equipment ids (23=has_gps_navigation etc.)
Add a JSONb column with key/value equipment ({'has_gps_navigation' => true} etc.)

Of those above I think the choice stands between option 1. and 3.

Why I don't think option 2. and 4. is a good idea

Option 2. in my experience is gonna give too much performance overhead on queries (I need < 200ms response time on queries on cars that has this or that equipment). I think option 4. is very similar to 3. but 4. will also have a larger disk space overhead than 3. if I store all values like {'has_gps_navigation' => true} etc. because I would have the key names stored in the db.

So down to option 1. and 3. I guess - any strong opinions otherwise?

My concerns about option 1. and 3.

My main concern with option 1. is that I would have to create 150 indexes or somewhere around that, to make queries performant. That sounds like the table, to my knowledge, will see a huge overhead on write performance. The other concern I have with option 1. is that it's gonna be a pain adding new equipment, having to create a column and an index every time.

My main concern with option 2. is that I have little experience with using postgresql intarrays. How performant are they in comparison in this situation and what kind of indexes should I use? As far as I can read, I can cover indexing of all equipment by just having a single index on that equipment_item_ids-column. I can easily create some logic in my application that maps the strings of has_gps_navigation to an integer of 23, my idea is then, if the car has a specific piece of equipment, add the id of it to the array.


texts:
I have a large PostgreSQL database over 500GB in size which is too large. Is there anyway to compress the database down to a more manageable size? I have attempted to do this with SquashFS and the database compressed down to 177GB however PostgreSQL requires that the database have write access and Squashed systems are Read-Only. Do more experienced database users have any suggestions to accomplish this goal?

The database holds GIS data for the planet and will be used locally on a deployed system. Currently it sits on a 1TB SSD, however, I am trying to avoid slapping in an additional hard drive simply to accommodate a large database. The database performs as desired with no issue, I would simply like to compress it down to a more manageable size and avoid placing it on a separate drive.


texts: MongoDB - Adding a new server to an existing replica set with auth enabled

texts:
Short version: Is there a way to add a new SECONDARY server to an existing production MongoDB replica set without downtime, and without temporarily disabling the "auth" and "keyfile" config settings on the existing servers?

Long version: My current production environment consists of three Windows 2012 R2 DataCenter servers running MongoDB version 2.4.9 as a service:

PRIMARY   - ServerA.example.org:27017
SECONDARY - ServerB.example.org:27017
ARBITER   - ServerC.example.org:27017

I have two MongoDB user accounts in the environment:

{ "_id" : ObjectId("XXXXXXXXXX"), "pwd" : "XXXXXXXXXX", "user" : "mongo-admin", "roles" : [  "userAdminAnyDatabase" ] }
{ "_id" : ObjectId("XXXXXXXXXX"), "pwd" : "XXXXXXXXXX", "user" : "mongo-cluster-admin", "roles" : [  "clusterAdmin" ] }

And the config file on the existing servers looks like this:

logpath=C:\mongo\logs\mongo.log
dbpath=C:\mongo\data
replSet=rs
oplogSize=700
port=27017
auth=true
keyFile=C:\mongo\config\keyfile.txt

I want to add ServerD and ServerE as new SECONDARY servers.

I've tried the following from my workstation and from the local console on ServerA, with and without the "auth" and "keyfile" in the config on ServerD, but I get the same result in all cases:

D:\MongoDB\bin>mongo ServerA.example.org:27017
MongoDB shell version: 2.4.7
connecting to: ServerA.example.org:27017/test
> use admin
switched to db admin
> db.auth("mongo-cluster-admin", "XXXXXXXXXX")
1
rs:PRIMARY> rs.add("ServerD.example.org:27017")
Mon May 19 11:41:42.050 count failed: { "ok" : 0, "errmsg" : "unauthorized" } at src/mongo/shell/query.js:180

The only way I've been able to get this working in a test environment is if I disable the "auth" and "keyfile" settings on all five servers, then add the new servers to the replica set using "rs.add()" on the PRIMARY, and finally re-enable "auth" and "keyfile" on all servers. This requires a couple of restarts of the MongoDB service on each box to update the config settings, which I really want to avoid.

Is there a way to add the new SECONDARY servers to the replica set without changing the "auth" and "keyfile" settings on the existing servers first?


texts: Connect to SQL Server with Windows Authentication in a different domain

texts:
I am trying to connect to a remote SQL Server on a VPN in a different domain. When I enter the Server name on the SQL Server and choose Additional Connection Parameters to add some extra stuff needed by my school:

Integrated Security=SSPI; User ID=DOMAIN\username; Password=Password

I get the following error:

  Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.


texts: Can I use xtrabackup to import a database without altering others?

texts:
Context:

Each time a demo branch is built, I want to download a copy of the prod database and setup it onto the demo server. This way under the MySQL I have X database corresponding to X demos.

I achieved it using the mysql_import but it takes 3 ~ 5 minutes when importing the DB. So to speed up I wanna use Xtrabackup.

Problem:

The problem is that during the full backup, xtrabackup copies all the necessary files and also the ib_logfilefiles.

So let's say I want to import the database into database named demo_1874.

I do a full backup of the database, then I run --prepare twice on the same server, and I compress the result.
Download on the other server, uncompress all the backup files
Paste them under /var/lib/mysql which will override the other files (ib_logfile)
Rename the folder original_db to demo_1874

Question

By copying files like ib_logfile, will it break all the other databases I have on this MySQL server (which I don't want this to happen) ?
What are the other options using Xtrabackup to import a database among others without altering the others ?


texts: How can I check if a user have a quota on tablespace

texts:
My objective is to check users quotas on tablespace:

Unfortunately the table dba_ts_quotas gives the details of already used space on tablespaces.

I want to see all users who have quotas on tbs even they don't use any space on the tablespace.

is there solution to check if a user has quota on a tbs?


texts: Select constant text that is the same as a column name

texts:
I'm trying to select const text for an insert. However the text is the same as a column name and gets interpreted as such. How do I work around this?

INSERT INTO Table1 (a, b, c)
SELECT "a", a, b
FROM Table2

If Table2 looks like:

a, b
1, 1
2, 2

I want the inserted columns to look something like:

"a", 1, 1
"a", 2, 2

But with this SQL query, it interprets "a" as column a, which is clearly not what I want.


texts:
I am trying to figure out the best database design for the following;

I have individuals (stored in Agents table) that enter their billing or time worked each day for different projects. Now we have different rates that we pay based on the person and what the project is. And we also have to account for individuals getting pay cuts or raises over time. So....

I have the following tables;

  Agents   - table of each person
  Projects - table of all projects we work
  Rates    - table with AgentID, ProjectID, StartDate, EndDate, Rate
  Billing  - table with Date, AgentID, ProjectID

So my current implementation a billing entry must be made by an agent, and I have to create a join to the rates table based on AgentID, ProjectID, and Date between StartDate and EndDate. This just seems incredibly ugly and all the billing data can be easily changed if a change were made to the rates. I'm assuming most time sheets calculate the total rate and store it in the table so it never changes unless someone specifically goes back to update the data? I'm just wondering if there is a better method for tracking this sort of data. Thanks


texts: Call Powershell command string from Sql Query Publish profile script

texts:
Is there a way to call powershell in SSMS Sql query window? I know Command prompt can be invoked with xp_cmdshell. Does similar method for powershell exist ? (Note, I know about powershell window , but want to pass a script string). want to call powershell script in our Post Deployment script publish profile.

https://docs.microsoft.com/en-us/sql/powershell/run-windows-powershell-from-sql-server-management-studio?view=sql-server-2017


texts: Find IDs in table where at least two entries exist with that ID and an additional field is A for one and B for the other entry

texts:
I have a game where we store user results in a table, similar to this structure:

game_id, user_id, score, ....

Now our service team would like to find all game_ids where user A and user B played together. Meaning I want to SELECT and GROUP BY game_ids WHERE one entry has user_id = A and the other entry has user_id = B.

What would be a performant way to query this in MySQL?


texts: Adding data in a bridge table automatically

texts:
Basically the concept is that a customer can place orders from multiple addresses and an address can have multiple customers, like different members of the same family.

CREATE TABLE Customer (
  cust_id    NUMBER PRIMARY KEY,
  cust_name  VARCHAR2(100),
  cust_phone VARCHAR2(20)
);

CREATE TABLE Address (
  address_id NUMBER PRIMARY KEY,
  address    VARCHAR2(500),
  area       VARCHAR2(100)
);

CREATE TABLE Customer_Address (
  cust_id    NUMBER REFERENCES Customer ( cust_id ),
  Address_id NUMBER REFERENCES Address ( address_id ),
  PRIMARY KEY ( cust_id, address_id )
);

Now I want to automate the process of entry of ids into the Customer_Address table i.e. When a record is added into the customer and address table, the ids are automatically added into the customer_address table.

I have tried this by creating a view where I inserted the ids of customer and address tables and then using the instead of insert trigger to populate the junction table but it did not work.

I created the view and trigger:

CREATE VW_CUSTADD AS
SELECT CUST_ID, ADDRESS_ID FROM CUSTOMER, ADDRESS;

CREATE OR REPLACE TRIGGER TG_CustAdd
INSTEAD OF INSERT ON VW_CustAdd
FOR EACH ROW
ENABLE
BEGIN
INSERT INTO CUSTOMER_ADDRESS VALUES (:NEW.CUST_ID,:NEW.ADDRESS_ID);
END;

The data is getting inserted into the view but not the junction table (Customer_Address). Have I made any mistake?


texts:
My Oracle DB USER table has 3 columns:

USERNAME NAME LAST_LOGIN

However, I know need to store the user's password, i.e. add a fourth column, PASSWORD.

I'd like to encrypt the PASSWORD when inserting it into my DB's table.

How can I encrypt a single column? And, does this approach make sense?


texts: mysql load data local infile is skipping the 1st column of the 1st row

texts:
We have a PHP script that has been running from the command line on a WAMP server for a few years. I am now converting it to run on Linux (AWS EC2 AMI instance). The script basically loads data from a csv into a mysql table. It is almost working but for some reason it skips the data in the 1st column of the 1st row. Here is the sql:

LOAD DATA LOCAL INFILE '/var/scripts/_output/ref_dtl_output.csv'
REPLACE INTO TABLE  MY_TABLE.ref_dtl

FIELDS TERMINATED BY ','
ENCLOSED BY ''

LINES TERMINATED BY '\n'

The files it's loading are anywhere from a few hundred bytes to > 100K bytes. In every case, whether it's getting thousands of rows, or 1 row, the 1st column of the 1st row is always "0" or an empty string. I have checked every csv, and there is data there. This never happened on the Windows server.

I tried running the SQL above from within the PHP script, and I also tried using HeidiSQL to import it and got the same results.

CREATE TABLE `ref_dtl` (
`site_id` INT(11) NOT NULL,
    `business_date` DATE NOT NULL,
    `trans_seq` INT(10) NOT NULL,
    `dtl_seq` INT(10) NOT NULL,
    `parent_trans_seq` INT(10) NOT NULL,
    `parent_dtl_seq` INT(10) NOT NULL,
    `ref` CHAR(20) NOT NULL,
    `ref_type` CHAR(3) NOT NULL,
    PRIMARY KEY (`site_id`, `business_date`, `trans_seq`, `dtl_seq`, `parent_trans_seq`, `parent_dtl_seq`),
    INDEX `site_id` (`site_id`),
    INDEX `business_date` (`business_date`),
    INDEX `trans_seq` (`trans_seq`),
    INDEX `dtl_seq` (`dtl_seq`),
    INDEX `parent_trans_seq` (`parent_trans_seq`),
    INDEX `parent_dtl_seq` (`parent_dtl_seq`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

Any ideas for how to fix?


texts: ACE.OLEDB.12 installed but everything has a 0 on sp_msset_oledb_prop

texts: So I installed ACE.OLEDB.12 to import excel files into sql server. After this, I can acces this procedure to see if everything is ok, but it's not. I have Zeroes on everything. This is a simple question. Should I restart sql server to validade it? I'm asking this because it's a production server and it's really hard to find a free time to restart it. If I'm sure this is going to fix the issue, I can restart it anytime. Thanks.


texts: Database Design when Admin and Employee have similar roles except Admin can see all the other Employees Data

texts:
We are building a system with concept of Admin and Employee. so basically Admin is an employee with all powers and can view all the data created by other Employee.

CREATE TABLE `Vendor` (
  `vendor_Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(40) NOT NULL,
  `email_Id` varchar(40) DEFAULT NULL,
  `landline_Number` varchar(15) DEFAULT NULL,
  `mobile_Number` varchar(15) DEFAULT NULL,
  `address_Line1` varchar(65) NOT NULL,
  `address_Line2` varchar(65) DEFAULT NULL,
  `city` varchar(255) NOT NULL,
  `pincode` int(6) NOT NULL,
  `country` varchar(255) NOT NULL,
  PRIMARY KEY (`vendor_Id`),
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1

CREATE TABLE `Employee` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vendor_Id` int(10) unsigned DEFAULT NULL,
  `name` varchar(40) NOT NULL,
  `username` varchar(40) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `role` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `employee_username_unique` (`username`),
  KEY `employee_vendor_id_foreign` (`vendor_Id`),
  CONSTRAINT `employee_vendor_id_foreign` FOREIGN KEY (`vendor_Id`) REFERENCES `Vendor` (`vendor_Id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1

CREATE TABLE `Action` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `emp_Id` int(10) unsigned DEFAULT NULL,
  `name` varchar(60) NOT NULL,
  `assigned_To` varchar(40) DEFAULT NULL,
  `deadline` datetime(3) NOT NULL,
  `notes` varchar(400) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `action_emp_id_foreign` (`emp_Id`),
  CONSTRAINT `action_emp_id_foreign` FOREIGN KEY (`emp_Id`) REFERENCES `Employee` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1

There are other tables Roles and EmployeeRoles which I thought were not required here.

Approach 1 : Now, when the Admin logs in to see all the Actions created by everyone

we first need to query the Employee table to find all the employees of that Vendor (we will have the Vendor_Id stored in the session when the admin/employee logs in)
Then query the Action table with where in employee_Id array from Step 1

Is this a good approach ?

Approach 2 : or in Action table, I shall store Vendor_Id for each record (mainly all this effort only so that when Admin logs in I can easily retrieve all the records for that Vendor. When Admin logs in from the session I can easily find the Vendor_Id and query the Action table.

I don't know at this moment which would be a better approach. Any suggestions ?
Like Action, there are other 3 tables where similar concept needs to be applied.

Edit 1: There can be a case where we can have  multiple vendors registered under a Single brand (future extension) and the Super-Admin would like to analyze the data across multiple branches.

Edit 2:
Employees can create ** new rows ** by using the features; but admin would be interested to see all the activities that are going on in that feature by different employees. Basically, admin should be able to see how are different employees using the system


texts: Cities and States (a few data): single table or splitted?

texts:
I am designing a DB schema and I need to store cities and states. I expect only a few rows, let's say, 20 cities. Example:

id  | city           | state
1   | Rio de Janeiro | RJ
2   | Niteroi        | RJ
3   | Cabo Frio      | RJ
4   | Nova Friburgo  | RJ
5   | Campos         | RJ
6   | São Paulo      | SP
7   | Santos         | SP
8   | Santo André    | SP

As I said, less than 20 cities and only 2 or 3 states ( CHAR(2) ). Regarding performance, which is the best: A single table like the above example, or 2 tables with relationship between both?

PS: I'm not expecting any SELECT ... WHERE 'state'... I'll only select by city and then display which state it belongs to.

Thanks


texts: Query being executed beyond timeout in a transaction Oracle

texts:
I have a simple query as below when executed separately the result is coming very quickly. But when is part of the application flow the same query is being shown as in execution status for more than 5 minutes and finally getting timeout, causing the transaction rollback. What could be the reason which is causing it to execute for that long when it is perfectly alright individually? Are there are any deadlocks happening? If so, the error message is not showing any such details, it is simply saying that transaction timeout. Our DBA also saying that the query is perfectly alright.

SELECT
  t0_bean.PLCY_PRD_PREM_DRV_ID
FROM
  POLICY_PERIOD_PREMIUM_DRV t0_bean,
  POLICY_PERIOD t1_bean_policyPeriodCMR
WHERE
  (t1_bean_policyPeriodCMR.PLCY_PRD_ID = :1
  AND t0_bean.JUR_TYP_CD = :2
  AND t0_bean.PREM_TYP_CD = :3
  AND t0_bean.RT_ELEM_TYP_CD = :4
  AND t0_bean.VOID_IND = 'n'
  AND t0_bean.PLCY_PRD_ID=t1_bean_policyPeriodCMR.PLCY_PRD_ID)


texts: Oracle 11.2.0.4: Basic info using RMAN for backups

texts:
I'm fairly new to databases. If I plan to use RMAN in archivelog mode to perform full backups each week plus incremental backups (retaining only the last 2 full backups for redundancy), how must I address the archive log files? I plan to use Oracle EM to create/manage RMAN processes, since it seems simplest.

I believe RMAN uses the archive log files to re-create changes to the database during and after the full backup occurs.

QUESTION 1

So, how far back do I need to keep the archive log files?

QUESTION 2

Does the database automatically remove the unneeded log files, or is this something I must address using cron jobs, etc.?

For example, if I do a full RMAN backup today at 1pm, can I delete all archive log files saved before 1pm? Or, will Oracle do that for me since it knows I'm doing a full backup? Or, something else?

QUESTION 3

To complete my understanding of archive logs, do I even need to put the database in archivelog mode to fully restore the database to the point in time of the RMAN backup, if I'm willing to accept loss of data from that time forward?

QUESTION 4

When RMAN finishes, and I want to copy all required backup/restore files to another server, which files need to get copied? The RMAN backup files have .bkp extension, and they need to get copied. The archive log files have .dbf extension, and they should all get copied. Anything else? (control files?)

QUESTION 5

Not sure if anyone can help answer this, but I do a lot of scientific work that uses all available digits in a binary_double number. If I choose compression for backups, will I lose accuracy when uncompressing the data? Or, is the compression algorithm going to restore the exact binary number (assuming the same operating system, etc.)?


texts: Suitability of mysql on a raid 5 server with raid card

texts: I would like to ask is mysql ok to run a on a raid 5 server. The raid is done using  hardware raid and not software. I saw some older links like this Is raid 5 suitable for a mysql installation? which gives negative opinion on settings raid 5? As time pass I guess hardware have improve and I guess it should be better now ? Any opinion ?