texts:
I've read in couple of articles that clustered index is as the same as table, or if you read using clustered index, you have the whole record.
Some say, clustered index stores the whole records in deepest leaf of index.

It is foggy for me, does clustered index has a copy of data?!!
for sure not, but I don't why people say that.

I read couple of articles but still it is not clear to me. Is clustered index is something virtual? Does it use B-Tree structure (like non-clustered index)?

Is there any schematic or model that describes how SQL Server stores/manages clustered index?


texts:
How can I get logon events from audit table for Feb, June, Oct and Dec?

I tried the query below, but it retrieves a lot of duplicate rows although I am using the distinct keyword in the query. Also this query takes long time.

select   distinct os_username ,username, userhost, terminal, timestamp, action_name,
         logoff_time
from     dba_audit_session
where    timestamp between '01-FEB-2017' and '28-FEB-2017'
order by timestamp;


texts: Trouble with installing additional modules on Postgresql 9.1 on ALT-Linux

texts:
I'm trying to create extension under postgresql 9.1 (Linux):

CREATE EXTENSION pg_trgm;

but got error:

ERROR:  could not open extension control file "/usr/share/pgsql/extension/pg_trgm.control": No such file or directory

This file exists and has r/w permissions for all users:

[root@postgresql extension]# pwd
/usr/share/pgsql/extension
[root@postgresql extension]# ls -l | grep pg_trgm
-rwxrwxrwx 1 root root  4977 Apr  6  2013 pg_trgm--1.0.sql
-rwxrwxrwx 1 root root  4230 Apr  6  2013 pg_trgm--unpackaged--1.0.sql
-rwxrwxrwx 1 root root   177 Apr  6  2013 pg_trgm.control

How to solve this problem?


texts: A different number of AFTER INSERT trigger alarms in SQLite and SQL Server

texts:
I have trigger

CREATE TRIGGER [Foo]
AFTER INSERT
ON [Bar]
BEGIN
  UPDATE Log SET count = count + 1;
END

When i execute query

insert into Bar (ColumnName)
values ('SomeValue1'), ('SomeValue2'), ('SomeValue3'), ('SomeValue4')

trigger alarms 4 times in SQLite and 1 time in SQL Server. What's the reason?


texts:
I have a situation where I want to insert records in both a master and details table, but only under certain condition. So when I insert, I do something like this:

set transaction isolation level serializable
BEGIN TRAN

INSERT INTO master (CustomerId, TypeOfTransAction, ...)
SELECT @customerId, 'TEST', ...
WHERE (SELECT SUM(amount) FROM details WHERE customerId = @customerId) < 1000

IF SCOPE_IDENTITY() IS NULL
    RAISERROR('can''t insert now, there''s too many', 16, 1)

INSERT INTO details (CustomerId, ...)
VALUES (@customerId, ...)

INSERT INTO details (CustomerId, ...)
VALUES (@customerId, ...)

...

COMMIT TRAN

Does this guarantee no more rows will be inserted into the master table (from other connections) until it has finished inserting all rows into the details table?


texts: Why is my @@Version unchanged after upgrading?

texts:
I have SQL Server 2008 R2. I installed SQL Server 2014 with the option:

  Upgrade from SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 or SQL Server 2012

and the process ended correctly.

But when running on localhost:

SELECT @@Version

I still have:

  Microsoft SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64)

Sorry if the question is basic but I am very lost at the moment.


texts: Returning Multiple Result-Sets or Single XML Blods

texts:
I've been writing some new Stored Procedures, and find myself returning between 6-8 result-sets in a few cases. I've always worked under the assumption that there were certain inefficiencies with result-sets that should be avoided.

It would appear that my options are to use result-sets or construct an xml representation of the complex objects and return them using SQL-XML channels (this is to avoid truncation of the blogs, by ADO clients). Does anyone have any experience with the performance and/or relative efficiencies of these choices and maybe provide some insights of the bad and good of each?

For additional context...

Each result-set would have no more than 30 columns, with most around 10
Column data is mostly integers (1 to 8 bytes) and some strings between 50 and 2000 characters
During peak load we expect between 5-9 queries per second for the SPROCs in question although there is one Use Case that could be 10’s per second
Denormalizing to a flatter view is not really a reasonable option insofar as we can see


texts: MySQL slow query log: huge query time, no lock time, minuscule number of rows scanned

texts:
I have a slow query log item with the following:

Query_time: 55  Lock_time: 0  Rows_sent: 739  Rows_examined: 739

No one in my group can explain it besides a guess about packet loss. Is there a common cause or set of causes for result like the above?


texts: Transfer of data (lots) from NAS based MariaDB to Postgres at AWS

texts:
I have a bunch of remote autonomous data harvesting systems running, each collects approx 28,800 records over an 8 hour run.  The remote systems are Rasp Pi based, running Python, logging to MariaDB on a Synology NAS.  I need to pump the data up to my Postgres server that is collating all the data. To add to the mix, the remote systems are running off 4G connections that drop in and out on an irregular basis (sometimes 8hrs offline...) so I can't rely on a scheduled job to do the trick.  I have python scripts running that transfer each record on an individual basis as a straight insert (these check and handle loss of connection etc) but that is way way too slow.

The data is massaged on its way to Postgres at the minute, to add in local identifiers etc but they could be incorporated into the local db to make the tables compatible.  My obvious bottleneck is the 4G but there is nothing I can do about that, I figured there must be a much more efficient method than inserting each row, would a parameterised SP give a significant gain ? or a bulk table load from a local csv (with batched records e.g. 1000 at a time)...

Any pointers to routes to explore much appreciated, thank you

Update:
I had googled previously and couldn't find a solution that seemed appropriate but more digging yielded some ideas..
Well on the way to a solution: using Python StringIO to build a 'file' and the postgres copy_from functionality I can bulk load 500 records in under a second. Still need to 'tune' the batching and other bits but seriously faster, the data massaging can still happen in the file build so didn't have to mod the base tables.

Still interested in other routes as I'm sure this one may yet build me a brick wall to bang my head against... again.


texts: pgAdmin 4 v1.6 setting PostgreSQL Binary Path to a remote server to clear-up "The system cannot find the path specified." error

texts: Just wondering how to go about assigning binary path to a remote server. I have pgAdmin on my personal computer, but the server (CentOS) that PostgreSQL resides on is at a different location.


texts: Why does Deadlock occur for this INSERT/UPDATE combination despite seemingly holding an X lock?

texts:
I am regularly seeing deadlocks in a production MySQL 5.5 database but cannot determine exactly what the cause is.

For context, we're inserting new records into a database which are triggered by another system. In some cases, these actions can be triggered twice, which has caused exceptions where the system correctly complains the row already exists.

We have therefore attempted to implement something of the form:

Check if any rows already exist, grabbing a lock so no one else can INSERT and invalidate this query
If any rows exist, return that row and end the transaction.
If no rows exist, INSERT the new row
Release the lock, allowing other transactions to complete step 1, and all being well, jumping out via 2.

The theory being that any duplicate attempts to INSERT will hit step 1 first, and as they'll have to wait for 4 to complete, will not be able to attempt a duplicate.

Here's the deadlock trace:

------------------------
LATEST DETECTED DEADLOCK
------------------------
160324 12:27:51
*** (1) TRANSACTION:
TRANSACTION 52CA7682, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 31 lock struct(s), heap size 6960, 2499 row lock(s)
MySQL thread id 21789278, OS thread handle 0x2ae64b6ca700, query id 652918526 hostname ip db_name Updating

update offer set OfferClickCounter = OfferClickCounter + 1 where quicklinkcode = 'yycajzzw'

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3625 page no 45 n bits 168 index `PRIMARY` of table `db_name`.`offer` trx id 52CA7682 lock_mode X waiting
Record lock, heap no 33 PHYSICAL RECORD: n_fields 25; compact format; info bits 0
 0: len 4; hex 800108d5; asc     ;;
...
 24: len 4; hex 80000081; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 52CA7679, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
16 lock struct(s), heap size 3112, 12 row lock(s), undo log entries 1
MySQL thread id 21789619, OS thread handle 0x2ae64afde700, query id 652918532 hostname ip the_db_name update

INSERT INTO user_offer (CreatedDate, ...) VALUES ('2016-03-24 12:27:51', ....)

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3625 page no 45 n bits 168 index `PRIMARY` of table `db_name`.`offer` trx id 52CA7679 lock_mode X locks rec but not gap
Record lock, heap no 33 PHYSICAL RECORD: n_fields 25; compact format; info bits 0
 0: len 4; hex 800108d5; asc     ;;
...
 24: len 4; hex 80000081; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3625 page no 45 n bits 168 index `PRIMARY` of table `db_name`.`offer` trx id 52CA7679 lock mode S locks rec but not gap waiting
Record lock, heap no 30 PHYSICAL RECORD: n_fields 25; compact format; info bits 0
 0: len 4; hex 800108d2; asc     ;;
...
 24: len 4; hex 80001ee1; asc     ;;

*** WE ROLL BACK TRANSACTION (2)

My understanding from this Deadlock trace is that:

Transaction 2 is attempting to INSERT into the user_offer table - which as mentioned earlier required a SELECT .. FOR UPDATE. Transaction 2 therefore holds an X lock.
Transaction 1 wants an X lock to UPDATE the offer table, and is thus waiting on Transaction 2 (so far, so good)
Transaction 2 also needs an S lock to be granted on the same offer table (I believe due to the fact that the user_offer table and offer table are related by a foreign key on the user_offer table).

i.e.:

t0: T2: SELECT .. FOR UPDATE locks offer with X lock

t1: T1: UPDATE asks for offer X lock and waits

t2: T2: INSERT INTO user_offer asks for S lock for offer

S lock can't be granted at t2 because X lock is already held by t0 (even though it's the same transaction), and another X lock is in the 'queue' before hand. Deadlock.

Have I got that right? How would I resolve this?

Any thoughts welcome and much appreciated.


texts: Will an ALTER TABLE ADD COLUMN on a slave break replication?

texts:
I need to add a last_modified column to a MyISAM table tbl_items. The crux of this problem is that tbl_items houses several gigabytes of data. Also of note, I am using a master-slave deployment with one slave.

Obviously, running the necessary ALTER TABLE command on the master is unacceptable since it locks the table for well over a half-hour. I cannot afford this kind of downtime at ANY time of day or night, most unfortunately.

A few sources have suggested some form of the following:

Disable replication to the slave
Run the ALTER TABLE command on the slave
Switch replication back on and wait for slave to catch up
Promote slave to master

Is this a viable solution? It would seem to me that all new tbl_items inserts would be lost between the beginning of step 2 and step 4; that is, is it possible for tbl_items data to replicate throughout the interval that it's missing the new column?

If that's the case, is there an alternate solution that eliminates the possibility of both downtime and data-loss?


texts: What is the algorithm behind the EXCEPT operator?

texts:
What is the internal algorithm of how the Except operator works under the covers in SQL Server? Does it internally take a hash of each row and compare?

David Lozinksi ran a study, SQL: Fastest way to insert new records where one doesn’t already exist He showed Except statement is the fastest for large number rows; closely tying to our results below.

Assumption: I would think Left join would be fastest, as it only compares 1 column, Except would take the longest, since it has to compare All the columns.
With these results, now our thinking is Except automatically and internally takes a hash of each row? I looked at Except execution plan and it does utilize some hash.

Background:
Our team was comparing two heap tables. Table A Rows not in Table B, were inserted into Table B.

The heap tables (from legacy text filesystem) do not have primary keys/guids/identifiers. Some of the tables had duplicate rows, so we found the Hash of each row, and removed duplicates, and created Primary key identifiers.

1) First we ran an except statement, excluding (hash column)

select * from TableA
Except
Select * from TableB,

2) Then we ran a left join compare between the two tables on the HashRowId

select *
FROM dbo.TableA A
left join dbo.TableB B
    on A.RowHash =  B.RowHash
where B.Hash is null

surprisingly the Except Statement Insert was the fastest.

Results actually map close to testing results from David Lozinksi


texts: SQL Server 2017 Express edition Bulk load

texts:
I am working on the SQL Server 2017. I need to import 20 text file into one table. Every text file has the same data type and column name. I have checked the data and they have in the same order also. I need to import in SQL Table and create a new column, the last column saying that

Row 1 to Row 150 data comes from "textfile-1"

, Row151 to Row300 data comes from "textfile-2"

, Row301 to Row400 data comes from "textfile-3"

We don't have any packages like SSIS.

Can we do it in Advance SQL Query? if so can someone please guide me


texts:
I was creating a DB with mySQL, and this error appeared:

  Can't create table 'projectefinal.apuntats' (errno: 150)

I've got this:

    CREATE DATABASE IF NOT EXISTS projectefinal

CREATE TABLE IF NOT EXISTS usuaris(
    id INT NOT NULL AUTO_INCREMENT,
    nom TEXT NOT NULL,
    cognoms TEXT NOT NULL,
    dni VARCHAR(9) NOT NULL,
    aturat BOOLEAN NOT NULL,
    PRIMARY KEY(id),
    UNIQUE(dni)
);

CREATE TABLE IF NOT EXISTS cursos(
    id INT NOT NULL AUTO_INCREMENT,
    nom TEXT NOT NULL,
    maximspersones INT NOT NULL,
    PRIMARY KEY(id)
);

CREATE TABLE IF NOT EXISTS apuntats(
    id INT NOT NULL AUTO_INCREMENT,
    idcurs INT NOT NULL,
    idusuari INT NOT NULL,
    coneixements TEXT,
    motiu TEXT,
    FOREIGN KEY(idcurs)
        REFERENCES id(curs)
        ON DELETE CASCADE,
    FOREIGN KEY(idusuari)
        REFERENCES id(usuaris)
        ON DELETE CASCADE,
    PRIMARY KEY(id)
);

I've read before that may happends because a table is not created, but it is, I went one by one ("usuaris" first, "curs" for the second, and finally "apuntats") and it gave me that error. Any help on how to fix it?

Thanks again, as always!


texts: In SQL Server 2008, is possible to one database subscribe 2 publications for the same database? (merge replication)

texts:
I'm trying to set up an high availability architecture using merge replication.
I have 3 databases, and I'm trying to keep them all up to date with merge replication.

So far I've manage to create one publisher and 2 subscribers, and all works fine...except when the publisher goes down.

So I thought about creating one more publisher and make the other databases also subscribe to it. I can do the publication normally, but when I create the local subscription, even though the GUI says it went successfully, the new subscription does not appear under "Local Subscriptions" folder.

Is this possible? or is it a limitation of sql server? (if it is possible...what may be wrong in my setup?)

regards


texts: how to resolve "InnoDB: Error (Duplicate key) writing word node to FTS auxiliary index table" in mysql

texts: I am continuous getting above error in mysqld log. Please tell me what is this error about and how to solve it. I am using mysql 5.7 (not mariadb)


texts: SOLVED - How to import a text file with "," value to Mysql


texts:
I have an existing table and wanted to create temporal tables. I used following queries, it worked in local and dev server. But in QA and PROD the same query created some anonymous temporal table not the one I wrote. What could be the issue?
ALTER TABLE dbo.abc
    ADD StartTime DATETIME2
GO
ALTER TABLE dbo.abc
    ADD EndTime DATETIME2
GO
UPDATE dbo.abc SET StartTime = '19000101 00:00:00.0000000', EndTime = '99991231 23:59:59.9999999'
GO
ALTER TABLE dbo.abc
    ALTER COLUMN StartTime DATETIME2 NOT NULL
GO
ALTER TABLE dbo.abc
    ALTER COLUMN EndTime DATETIME2 NOT NULL
GO
ALTER TABLE dbo.abc
    ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)
GO
ALTER TABLE dbo.abc
    SET(SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.abc_history, DATA_CONSISTENCY_CHECK = ON))
GO

So instead of creating system versioning for abc_history, it created MSSql_TemporalHistoryFor_<some-random-number>.
I see abc_history table in DB, it is just not system versioned.
select @@version gives "Microsoft SQL Azure (RTM) - 12.0.2000.8   Feb 20 2021 17:51:58   Copyright (C) 2019 Microsoft Corporation "


texts: Create a maintenance plan for sql database with C#

texts:
We are planning to create c# jobs that perform some database maintenance actions such as backup, shrink files etc...

I have read in many blogs that database shrink is not recommended and thats why I'm posting here in order to have some recommendations to this topic.

We want also to rebuild / reorganize the indexes. This is an easy one and I already wrote the C# script for it.

The backup for the databases and shrink for the transactions log should be also done. The databases have recovery model: Full.

Some blogs recommend this order:

Backup the database
Change recovery model
Shrink the .ldf file
Re-set the original recovery model

some others recommend this not to do shrink at all, are to use recovery model: simple.

My questions are:

How often should we rebuild or reorganize indexes in the sql tables?
Is shrink for log files a good idea?
In which order should we do the database backup / logs backup / logs shrink?

I don't have enough DBA experience, so it will be great if you can give me some hints.

Thanks


texts:
What if table size more than buffer pool size . How data and index are cached in buffer pool size if the table size is more than buffer pool size.

For example:

One of my innodd table size is 231 gb. How data and index are cached in this table


texts: Adding TIME data to a DATE type column in time series table, and what sort of foreign key

texts:
I've got a v. simple table currently containing millions of data points of price on a given date:

CREATE TABLE data_point (
    id INT PRIMARY KEY,
    symbol_id INT,
    x DATE,
    y DOUBLE
)

This is a timeseries database and for this release I'll keep data with a daily (or less frequent) period but if I need to add a time component, would it be better to change the data_point table and convert x to DATETIME?

If this did happen, initially all of the existing data would remain unchanged, it would not be adjusted - only future data (and not all of it) would be saved with time.

Or would it be better to add a second table with a one-to-one relationship to data_point, which held the time data?

Performance is more critical than space.

This leads on to the associated question about this table. Which is better in terms of performance for huge amounts of data, when all the queries on data_point will be joins from symbol? (Assuming INT for a primary key will get filled up within the app's expected lifetime)

CREATE TABLE symbol (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE data_point (
    id BIGINT PRIMARY KEY,
    symbol_id INT,
    x DATE,
    y DOUBLE,
    CONSTRAINT FOREIGN KEY (symbol_id)
        REFERENCES symbol(id)
);

or

CREATE TABLE data_point (
    symbol_id INT,
    x DATE,
    y DOUBLE,
    CONSTRAINT PRIMARY KEY (symbol_id, x),
    CONSTRAINT FOREIGN KEY (symbol_id)
        REFERENCES symbol(id)
);


texts: Oracle XE installation aborts with no error explanation

texts:
I have downloaded Oracle XE 18c for Windows 64 bit, and tried to install it on a Windows 10 machine. I checked the SHA-256 checksum and it's correct.

The installation starts fine, but when it's copying new files it suddenly says "Rolling back installation", then ends and there's no error log to look at (there's no even C:\Program Files\Oracle folder, where Oracle error logs are stored). Any ideas?


texts: Mysql HY000 the table table_name is full when adding new column

texts:
I can't add a new column innodb table which size's 16MB. when I try to add, mysql says HY000 the table table_name is full.

I changed my tmp_table_size and heap_size but mysql still says same error.

I try How to solve "The table ... is full" with "innodb_file_per_table"? this settings but same error.

I create dummy table and Add new column it is ok. but my real table mysql says error.

My variables :
innodb_data_file_path | ibdata1:12M:autoextend


texts: postgresql configuring wal_archive directory

texts:
I had a problem with PostgreSQL server.

I took over PostgreSQL server from my former colleague, and today my server crashed due to filled up disk space with wal_archives.

Usually wal segments were put in root directory /wal_archive/, I checked it - its empty. Some time ago I checked it and it had some segments...

I dug deeper and find out that all wal segments were stored in pg_data/pg_xlog directory.

How could this happen? While postgresql.conf is set up like this (showing only enabled segment of 'write ahead log' rows)

wal_level = hot_standby                 # minimal, archive, hot_standby, or
archive_mode = on               # allows archiving to be done
archive_command = 'test ! -f /wal_archive/%f && cp %p /wal_archive/%f'

Why my wal_archive directory now is empty wwhile config settings showing different information?

The wal_archive directory was owned by root user
pg_hba.conf file does not have any replication user - I assume that there is no replication on the server also. There are no files like recovery.conf anywhere.

  cp: cannot stat 'pg_xlog/000000010000033C00000081': No such file or directory
      2018-12-05 14:37:54 EET [7540-1860] LOG:  archive command failed with exit code 1
      2018-12-05 14:37:54 EET [7540-1861] DETAIL:  The failed archive command was: test ! -f /wal_archive/000000010000033C00000081 && cp pg_xlog/000000010000033C00000081 /wal_archive/000000010000033C00000081
      2018-12-05 14:37:54 EET [7540-1862] WARNING:  archiving transaction log file "000000010000033C00000081" failed too many times, will try again later

Also due to panic, I accidentally deleted big chunk of wal segments in xlog directory, to fire up server. Everything is working ok now, but I want to ensure that there will be no problems in the future.


texts: Monitor Database Memory usage SQL Server

texts:
In our PROD environment, there are two servers on which we have system critical database. The management requirement is to monitor these databases for high memory usage or access or long running queries, etc. My requirement is to create a SQL Job for this purpose and if any spikes are observed for this database an event gets generated. So that we can take corrective actions, before the sites hosted on these Dbs slows down. Please don't recommend using any third party tools for this purpose.

Also, I have come up with one idea, using the following query:

DECLARE @total_buffer INT;

SELECT @total_buffer = cntr_value
   FROM sys.dm_os_performance_counters
   WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
   AND counter_name = 'Total Pages';

;WITH src AS
(
   SELECT
       database_id, db_buffer_pages = COUNT_BIG(*)
       FROM sys.dm_os_buffer_descriptors
       WHERE database_id BETWEEN 5 AND 32766
       GROUP BY database_id
)
SELECT
   [db_name] = CASE [database_id] WHEN 32767
       THEN 'Resource DB'
       ELSE DB_NAME([database_id]) END,
   db_buffer_pages,
   db_buffer_MB = db_buffer_pages / 128,
   db_buffer_percent = CONVERT(DECIMAL(6,3),
       db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC;

The output of the above code is BUFFER USAGE (MB) and % BUFFER for each database, except system Dbs. Planning on creating a job which will keep on monitoring the output and check if the Buffer usage goes beyond a threshold.

Kindly suggest something or give comments about my idea.
Thanks in advance


texts: Constraining data based on related data in a different table. Should this be done on the database level or the application level?

texts:
I am working on a database that contains our customer information as well as information about our vendors. Part of that includes matching our customers with our vendors, the account numbers those vendors provide to us for our customers and what we charge our customers to use those vendor's services.

Background

In the situation I'm working on now, I have a table that contains our customer's account number, the vendor ID and the account number assigned to our customer by that vendor.

I also have a table that contains all of our vendor's information and a related table that list's all of their services and a "base cost" for that service.

The Problem

The problem that I have is associating a customer with a vendor's service.

If I just create a table that has our customer's account number, a vendor's service ID and the cost of that service, then I could potentially run into issues where a service is assigned to a customer by a vendor that doesn't offer that service.

Here's a diagram of what I'm talking about (though crude):

Because there is no "check" between VendorServices and CustomerVendorServices, I could potentially insert a record where the VendorServiceID in the CustomerVendorServices table for a service that a vendor doesn't provide.

This is obviously a problem and I could prevent it from happening through the interface of the application that interacts with this database, but I would feel much more comfortable if there was a way for the database to "check" that the VendorServiceID being inserted into the CustomerVendorServices table matches a service that is offered by the vendor who is associated with the VendorAccountsID/VendorAccountNumber.

I'm sure this is beyond confusing and I'd honestly be surprised if anyone tried to understand it, but I thought I would give it a shot. :)

Any help and/or suggestions would be very much appreciated.


texts: Perform SELECT using WHERE IN clause on JSON Object in MySQL

texts:
I have a json column in MySQL which contains a list of items for eg.
"style": {"style1", "style2", "style3", "style4", "style5"}

I want to search all the rows which contain the styles I specify through an array like IN clause like WHERE style IN(["style1", "style3"])
So I want to get all the rows which will match the string items from array exactly like how the WHERE IN() clause works in MySQL.
Is there any workaround or easy way to get this working?
Please help.. Thanks


texts: Scheduling an event every day at a given time

texts:
I want my event to run everyday at 00:20. I am confused in scheduling of the time. Should I be using the first option or the second one?

Option 1

CREATE EVENT my_event
  ON SCHEDULE
    EVERY 1 DAY
    STARTS '2014-04-30 00:20:00' ON COMPLETION PRESERVE ENABLE
  DO
    # My query

Option 2

CREATE EVENT my_event
  ON SCHEDULE
    AT ('2014-04-30 00:20:00'+ INTERVAL 1 DAY) ON COMPLETION PRESERVE ENABLE
  DO
    # My query


texts:
Install

$ brew install postgresql
Updating Homebrew...
==> Downloading https://homebrew.bintray.com/bottles/postgresql-12.1.mojave.bottle.tar.gz
Already downloaded: /Users/user/Library/Caches/Homebrew/downloads/525a3637cd6f7b7a7e0bfe5adb102483ae01f4be31269aaf4348f6f4291f8a86--postgresql-12.1.mojave.bottle.tar.gz
==> Pouring postgresql-12.1.mojave.bottle.tar.gz
==> /usr/local/Cellar/postgresql/12.1/bin/initdb --locale=C -E UTF-8 /usr/local/var/postgres
==> Caveats
To migrate existing data from a previous major version of PostgreSQL run:
  brew postgresql-upgrade-database

To have launchd start postgresql now and restart at login:
  brew services start postgresql
Or, if you don't want/need a background service you can just run:
  pg_ctl -D /usr/local/var/postgres start
==> Summary
  /usr/local/Cellar/postgresql/12.1: 3,217 files, 37.3MB

Check status

$ brew services list
postgresql     started user /Users/user/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

Connecgt

$ psql
psql: error: could not connect to server: FATAL:  database "user" does not exist

$ psql -U postgres
psql: error: could not connect to server: FATAL:  database "postgres" does not exist

It already inited database, why postgres does not exist?


texts: INSERT data on MySQL to specific position based on its value (similar to ORDER BY)

texts:
I have a MySQL table with 3 columns for simplicity sake:

Datename     |Date    |Name
-------------+--------+-----
30/06/15test |30/06/15|test
30/06/15other|30/06/15|other

Note: Column Datename is unique

I would like all new data to be inserted into the table based on its value.

Example:

If the Date is 29/06/15, it would be inserted at bottom of table.
If the Date is 01/07/15, it would be inserted at the top of the table

Or preferably sorted by Datename column so it will sort by date then name.

Is this easily achievable, or do I need to just create a page that SELECT and ORDER BY?


texts:
How to create unique constraint on a column with duplicate values,without disturbing the existing data.i have already tried the method below,

To create a normal index first and then create a unique constraint.
But in this I won't be able to drop the unused index created first.i don't want that .

Any other clean methods which can be applied in production env?


texts: SQL Server setting up logins for backup site

texts:
I am setting up a second SQL Server for a backup site.
I am using SSIS to transfer user logins from production to site2.
I was wondering whether I should transfer the NT Service,NT AUTHORITY and ###MS****### logins from one server to the other or set those up separately on site 2 ?


texts:
Im a complete beginner with SQL server. I'm trying to train myself using the AdventureWorks2016 sample DB on an SQL server 2016. I'm trying to setup mirroring between VM1/SQLServerInstance1 and VM2/SQLServerInstance2. 2 problems which I'm facing:

The option to backup Transaction logs is missing from properties window of the database (Right click on DB > Properties > Backup).
Mirror option is greyed out with the following message: "Database mirroring is not supported for a database with a MEMORY_OPTIMIZED_DATA filegroup. Use AlwaysOn Availability Groups instead". I was hoping to stay away from AlwaysOn Availability groups by using Mirroring.

I've tried googling and this is my last resort. Help would be greatly appreciated.

UPDATE

Issue 1 (No transaction log backup option), was resolved by Changing Recovery mode to Full from simple (Right Click on DB name > Properties > Options > Recovery Mode).


texts: Is there some way to store EITHER a specific day (date) OR a year in the same column?

texts:
I have a column called "date or year of birth". The reason it's not called "date of birth" is that, in some cases, the exact day is not known -- only the year, or only the year and month.
If I try to store "1998" in a date column, I get an error; PostgreSQL doesn't let me store it.
I would have to turn it into "1998-01-01" for it to be allowed to be stored, but now it's probably lying (unless they happen to be born on 1st of January 1998).
Simply having only the year is not the same as 1st of January 1998. This way, I cannot ever be sure if birth dates on that day are "real" or just another case of "we only know the year".
If I turn this column into a text, it will store both "1998" and "1995-04-06", but this makes it a PITA to deal with whenever I need to compare it time-wise. Furthermore, it "feels wrong" to store such information in a "text" column; I only use those as a last resort when PG simply doesn't have "native knowledge" of the kind of information contained within.
What can I do about this?


texts: Which tool enables using SQL query result sets as temporary tables for further analysis and set operations?

texts:
For examples, lets say I need to do set operations and joins on below 3 result sets.

I don't want to write a composite query or use temporary database tables. Something which would enable faster data analysis using broken-down sets of data like below from a large database. Like as if you could join two result sets in toad or SQL developer with the help of a GUI.

SQL Query 1 result: (T1)

Col1 Col2
A    1
B    2

Query 2 result: (T2)

Col3 Col4
1    #
2    *

Query 3 result: (T3)

Col5
1
3

Required output is

 Select T1.col1,T2.col4
        from T1,T2
        where T1.col2=T2.col3
        and T1.col2 in (Select T3.col5 from T3)

(Not necessary that the tool should generate SQL like above to process the result sets, SQL is only for conveying the logic.)

Required result:

Col1  Col4
1    #


texts: Oracle migration to SQL Server: SQL Server Equivalent to Oracle Global variables

texts:
I am working in a Oracle 11g database migration to SQL Server 2012, I did the migration using SSMA v5.3 and all works fine.
However, I did re-write code in some objects like triggers and stored procedures because the SSMA did a standard migration.

But my questions is about this:
The Oracle Db have some packages that use global variables like this code:

CREATE OR REPLACE package OPS_TRACKING.pkg_trade_data as

-- packaged variable to be used by triggers not to execute during
-- archival and resoring process of the trade data
archive_restore_flag  varchar2(1) := 'N';
procedure p_archive_trade_data( p_trade_id    number );

In my case this global variable is called for some triggers like it:

    CREATE OR REPLACE TRIGGER "OPS_TRACKING"."TG_TRADE_GROUP_AER_DI"
after insert or delete on ops_tracking.trade_group
for each row
declare
  v_id  number;
  v_trade_id number;
begin
   If pkg_trade_data.archive_restore_flag = 'N' Then

      if inserting then
         v_trade_id := :new.trade_id;
      else
         v_trade_id := :old.trade_id;
      end if;

      select id into v_id from ops_tracking.trade_summary
      where trade_id = v_trade_id
      for update nowait;

      update ops_tracking.trade_summary
         set transaction_seq=ops_tracking.seq_trade_summary_transaction.nextval
            ,last_update_timestamp_gmt = sysdate
       where trade_id = v_trade_id;

   End If; -- archive_restore_flag
end tg_trade_group_aer_di;
/

As you can see in this trigger the global variable
(pkg_trade_data.archive_restore_flag = 'N') is used for take a decision and based in their value the oracle trigger execute the action.

I would like to know which is the equivalent or which are my options to migrate this code to SQL 2012.

I appreciate your comments and advice's.


texts: Why does query runtime dramatically increase when made into subquery?

texts:
I'm working on a query that selects counts from 2 servers and joins those together.  Here is what I have:

SELECT
    a.NAME
    , SUM(ISNULL(b.ttl,0)) AS [Total]
FROM TableFunction(@user, DEFAULT) a
-- Selecting NAME from Table1 takes <1s
LEFT JOIN (
    -- Sub-Query 1
    SELECT
        COUNT(*) ttl
        , NAME
    FROM Table2 c
    WHERE active=1
    GROUP BY NAME
    -- Sub-Query 1 (< 1s, 662 rows)
    UNION ALL
    -- Sub-Query 2
    SELECT
        COUNT(*) ttl
        , jt.NAME
    FROM OtherServer.dbase.dbo.Table3 d
    INNER JOIN OtherServer.dbase.dbo.JoiningTable jt ON d.ID=jt.ID
    WHERE ISNULL(d.DELETED,0)=0
    GROUP BY jt.NAME
    -- Sub-Query 2 (7s, 5576 rows)
    -- Full Sub-Query [1 UNION ALL 2] (7s, 6238 rows)
) b ON b.NAME=a.NAME
GROUP BY a.NAME
--Full query (3:44, 91 rows)

The Execution plan suggests that the problem comes from the remote query (Sub-Grid 2).

How can I improve the runtime of this?  Why does a 7 second sub-query take over 3 minutes when joined on a basically instant query?

UPDATE:

After some commenting out random things in hopes of finding something useful, it appears to be specifically the WHERE ISNULL(d.DELETED,0)=0 bit.  Removing that line improves query to 4s.  Why would this help at all?

UPDATE 2:

Playing around more, it seems the fact that the first table is a table-valued function may have been important.  Selecting it's result table into a temp table and using that temp table brings the query down to 8s.  The table-valued function code is:

--Inputs
@user varchar(50)
, @includeDeleted BIT = 0

--Function
RETURNS @tbl table (ID int
  , NAME varchar(max)
  , DESCRIPTION varchar(max))
AS
BEGIN
INSERT INTO @tbl (ID, NAME, DESCRIPTION) (
    select jt.ID, jt.NAME, jt.DESCRIPTION
    from OtherServer.dbase.dbo.JoiningTable jt
    where jt.USER = (select top 1 USER
                            from OtherServer.dbase.dbo.USERS
                            where user_name = @user)
    and 1=CASE WHEN @includeDeleted=1 THEN 1 ELSE
        CASE WHEN ISNULL(jt.Deleted, 0) <> 1 THEN 1 ELSE 0 END
    END
)

INSERT INTO @tbl (ID, NAME, DESCRIPTION) (
    SELECT ID, name, ''
    FROM RandomTable rt
    INNER JOIN RandomJoinTable rjt ON rjt.ID = rt.ID AND rjt.user = @user
    LEFT JOIN @tbl t ON t.ID = rt.ID
    WHERE 1=CASE WHEN @includeDeleted=1 THEN 1 ELSE
        CASE WHEN ISNULL(rt.Deleted,0)=0 THEN 1 ELSE 0 END
    END
    AND t.Name IS NULL
)

RETURN


texts: Should I use MongoDB or a NoSQL solution at all for this?

texts:
I apologize if this belongs in web applications or something.

I'm creating a web application that is sort of a similar concept to github. Basically I plan to allow someone to create a design of something (lets say a recipe for a cake for instance) and they can add new ingredients (or from already existing, which will have attached calories, etc.). But I want to add in that revision control aspect, new versions, branches etc.. Also plan to allow cloning of a design.

That's a basic idea. But would those main functions of a revision-control-esque setup beg for nosql? Am I crazy and jumping into a hip looking db too fast?

Let me know if more details are necessary.


texts: SSIS export table with datetime column from SQL Server to Postgres / Redshift fails with "datetime field overflow"

texts:
I'm trying to execute a simple export task to move a table stored in SQL Server to a table stored in Amazon Redshift.
The table contains a column that is of type DATETIME in SQL Server, and the column in Redshift is of type TIMESTAMP.
I have used the export wizard in SSMS to build an SSIS task that exports this specific table and all columns to Redshift. I have also tried troubleshooting the package in Visual Studio, which has involved some conversion techniques and also changing from ADO to ODBC, etc. The package consists of a simple data flow task with a source DB attached to a destination DB and each field mapped 1-to-1.
I always get the error, ERROR [22008] [Amazon][Support] (40520) Datetime field overflow resulting from invalid datetime.
This seems like such a simple task. Where am I going wrong? How do I make this work?
EDIT: Here is a link to a different problem, but it shows the same process I am trying to use except with PostgreSQL and there is no mention of datetime issues. I suspect this issue is somehow related to the Amazon provided Redshift ODBC driver, but how do I go about addressing that? Transfer data from MS SQL to PostgreSQL with SSIS ODBC destination


texts: MySQL Query - How to get most recent demographics?

texts:
I have a table that has duplicated cardnbr's. I need to pull out demographics that are the most recently used by the customer. I have a date field (lastvisit) that shows their most recently used card. Here's an example query that doesn't work:

SELECT cardnbr, fname, lname, address, city, state, postal
FROM cards
WHERE lastvisit = MAX( lastvisit )
GROUP BY cardnbr

How can I format my query to do this but actually work?

Also, the primary key for this table was created after duplicated data was added to the table.


texts: Execution plan caching of ITVF called with inline vs dynamic SQL

texts:
I would like someone to explain to me how each of the two queries below would generate their execution plan in SQL Server where my function [dbo].[GetAdditionalProjectDatesForCalendar] is an ITVF (inline table-valued function):

Dynamic SQL

sp_executesql N'SELECT * FROM [dbo].[GetAdditionalProjectDatesForCalendar]
(@Parameter1,@Parameter2)', @Parameter1 = 'Value1', @Parameter2 = 'Value2'

Inline SQL

SELECT * FROM [dbo].[GetAdditionalProjectDatesForCalendar]
('Value1','Value2')

Background

The Dynamic SQL is what's generated from a C# application that I'm working on and while it performs just as well as the Inline SQL most of the time it performs poorly for certain values of @Parameter1 and @Parameter2.

I've read up on how this is caused by Parameter Sniffing and I've tried using option(recompile) with the Dynamic SQL but that hasn't improved the performance entirely.

I feel like I need to find a way to force C# to create the Inline SQL so that it will use the exact execution plan for the given parameters instead of creating one on the fly with option(recompile).  I also think I can do this by writing my dynamic sql without using parameterization but I know that would introduce a SQL Injection risk.


texts: Restore Error event id 17066 and 3624 while restoring diff backup

texts:
I am getting the following error while restoring the Diff back up after restoring the Full backup in Norecovery mode.

I am unable use dbcc checkdb as the database in restoring db.Issues happens only in three instance where there is totally 6 instance. In other instance restore is working fine.

Error:

  Msg 5052, Level 16, State 1, Line 4

  ALTER DATABASE is not permitted while a database is in the Restoring
  state.

  Msg 5069, Level 16, State 1, Line 4

  ALTER DATABASE statement failed.

  Location:     filesystem.cpp:526

  Expression:   newTree.IsInArchive ()

  SPID:         98

  Process ID:   1956

  Msg 3013, Level 16, State 1, Line 22

  RESTORE DATABASE is terminating abnormally.

  Msg 3624, Level 20, State 1, Line 22

  A system assertion check has failed. Check the SQL Server error log
  for details.

  Typically, an assertion failure is caused by a software bug or data
  corruption. To check for database corruption, consider running DBCC
  CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini
  dump will be sent to Microsoft. An update might be available from
  Microsoft in the latest Service Pack or in a QFE from Technical
  Support.

Help me in resolving this issue.


texts: How mature is the Firebird database when compared with PostgreSQL and MySQL?

texts:
I have used PostgreSQL with small projects and MySQL is running in production environment. No issues with them. I have been hearing of Firebird which is again a solid RDBMS in the open-source area.

How reliable, ANSI-SQL compliant and stable it is when compared to PostgreSQL? How


texts:
I am new to database management/design but am fascinated by the subject and the possibilities it provides. I have had some experience with the following:

tables
attributes
fields and data types
defining foreign and primary keys
establishing one to many, one to one and many to many relationships using junction tables
developing queries using SQL

I have encountered one data structure that I have not been able to model properly in SQL for instance. Namely, the case where a field contains itself another table. This is frequently encountered in procedural and object oriented programming where data structures (such as derived types in fortran) can contain subtypes which hold only one value at a time but also subtypes which hold arrays... etc. In python for example it is possible that a "field" in a dict can hold a list which itself can hold fields of any given datatype including another list, dict or class. As you can see this allows for considerable complexity which I am not sure how to represent in a classical "database" schema in SQL where a table can have relationships with other tables but where they key: value schema is strictly enforced.

Anyone else know what I'm talking about? Is it possible/necessary to have tables within tables? If not, what SQL functionality renders this obsolete?

THanks


texts: Query to find hierarchical relationship between objects

texts:
I want to execute a SQL query in PostgreSQL 10.6. I have two tables, A and B, that contain a common field, which contains a code. Each code has an inherited code and I want to build a hierarchical classification of these codes.
TableA
id | code
---------
1  | A
2  | B
3  | C
4  | D
5  | E

TableB
id | code | origin | change_date
--------------------------
1  |  A   |   B    | 2020/01/01
2  |  B   |   C    | 2019/01/01
3  |  C   |   D    | 2018/01/01
4  |  D   |   E    | 2017/01/01

I want to get origin values for a defined code in a chronological order. For example with tableA.code = A :
code A origin is B, code B origin is C, code C origin is D, code D origin is E
In other words, for a code A :
id | code | source
------------------------------
1  | A    |  B -> C -> D -> E

I can write a basic query to find value(s) but how can I execute this query each time there is a result and with a dynamic variable for the where clause (= the result of my previous query) ?
SELECT b.origin
FROM tableB b
JOIN tableA a USING (code)
WHERE table a.code LIKE 'A'

SELECT b.origin
FROM tableB b
JOIN tableA a USING (code)
WHERE table a.code LIKE 'B'

...

I have seen examples with functions, DO, DECLARE, IF, LOOP, RETURN QUERY EXECUTE but I don't know what the best way to write this query.


texts: Query goes in Copying to tmp table stauts in MySQL and never returns

texts:
I am getting some strange behaviors for my MySQL query status. Here is my query code:

SELECT a.id as art_id, b.album_artist_id,
s.song_title,s.song_seo,a.artist_seo,a.artist_name,
b.album_title, b.album_picture, s.id,s.picture
FROM tbl_artist_album b, tbl_artists a, tbl_songs_artist_album saa, tbl_songs s
WHERE 1=1
  AND s.id = saa.song_id AND a.id = saa.artist_id
  AND b.id = saa.album_id AND saa.artist_id != 67
  AND ( MATCH (s.song_title) AGAINST ('taylor swift*' IN BOOLEAN MODE))
  AND (CONCAT(s.song_title) LIKE '%taylor swift%')
  AND s.song_status = 1
group by s.id
ORDER BY s.song_title = 'taylor swift' desc
LIMIT 50

When I execute above query in Phpmyadmin panel it works fine and returns 0 results with in few seconds.
But When the query is executed from browser by search form on a page , the query goes directly in the "Copy to tmp table" status and never returns anything.

| 79 | | | | |  632 | Copying to tmp table | select a.id as art_id,b.album_artist_id,s.song_title,s.song_seo,a.artist_seo,a.artist_name,b.album_title, b.album_picture, s.id,s.picture from tbl_artist_album b, tbl_artists a, tbl_songs_artist_album saa, tbl_songs s where 1=1 AND s.id = saa.song_id AND a.id = saa.artist_id AND b.id = saa.album_id AND saa.artist_id != 67 AND s.song_title like 'taylor swift%'  AND s.song_status = 1 group by s.id order by s.song_title = 'taylor swift' desc limit 50 |

I have also attached the screenshot of query execution for reference in Phpmyadmin and it work in few seconds but I am confused why it is showing "copy to tmp table" when executed from browser.


texts: How can I change TIMESTAMP to TIMESTAMP(3)?

texts:
I have a column of TIMESTAMP, NOT NULL, DEFAULT CURRENT_TIMESTAMP.

I want to modify it as TIMESTAMP(3).

I tried

ALTER TABLE <table>
MODIFY COLUMN <column>
TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP;

And I got.

  Error Code: 1067. Invalid default value for '<column>'

What can I do with it?


texts: Is saving disk space still considered an advantage of normalization?

texts: I have read a while back that one of the advantages of normalization is to save disk space (since normalization minimizes redundancy), but is saving disk space still considered an advantage of normalization in current time where disk storage is very cheap?


texts: Regarding Sql server 2014 express edition

texts:
I need to know if adding another data file in a database (let say MYDB) in express edition will have another 10 GB size or 10 GB will be the total size of all the data files we add in a database.  Actually I am using SQL Server 2014 Express edition and we know that DB size is limited to 10 GB and thus I have to archive the database once it reached to 10 Gb which I don't want.

My purpose is to ask this question is that once the database size reached to 10 GB, can adding another data file help me to avoid archiving the database and continue with the same database?


texts:
I have a Pets table (cats, dogs, you name it) with over a million records.  For simplicity sake, it looks like the following:

Pets
Field   Type            Null    Key
id      int(11)         NO      PRI
name    varchar(256)    NO

For the front page on my site, I want to show off 5 random pets to display every month.

I was considering adding a new boolean column to the Pets table for identifying the 5 Pets I'll be showing each month.  But!  I'd have hundreds of thousands of records with false column values, just so that I could have 5 records with true values; which my gut was telling me, doesn't sound very normalized.

So!  I was wondering if it might make sense to refactor the selected records into a new table instead, like below.

PetDisplay
Field   Type            Null    Key
id      int(11)         NO      PRI
pet_id  int(11)         NO      MUL  <<< Foreign key to Pets Table

That way, the new table could just track the 5 selected records, instead of having a column with hundreds of thousands of empty values.  Does that make sense?  Would this actually perform better, or would it be an unnecessary abstraction?  Are there any general database rules around making refactoring decisions like this?

Thanks y'all!


texts: What is the difference between COUNT(*) and COUNT(*) OVER()

texts:
Take the following code example:

SELECT MaritalStatus,
       COUNT(*) AS CountResult
       COUNT(*) OVER() AS CountOverResult
       FROM (schema).(table)
       GROUP BY Marital Status

COUNT(*) Returns all rows ignoring nulls right?

What does COUNT(*) OVER() do?

This question came in a practice exam so I didn't have the data to query.  I have been using Adventure Works and this site http://www.sqlishard.com/Exercise to practice.

If I enter a query like

SELECT ID, COUNT(*) AS 'COUNT(*)' , COUNT(*) OVER() AS 'COUNT(*) OVER()'
FROM Customers
GROUP BY ID

into the practice site I get 3794 rows returned with the Count(*) column full of ones and the Count(*) Over() column full of the total number of rows.  I didn't understand this pattern (sorry) so I came here.


texts: SQL command to clean job queue in pgAdmin 4

texts: I have a Postgres 9.6 installation on a developer pc and a restore seems to have failed. Even after reinstalling (remove Postgres and PgAdmin completely, incl. database and installed new version) the boxes are popping up (even though these tables do not even exist anymore). How to remove these scheduled jobs? Is there an SQL statement to stop these and cleanup the queue? Both jobs are running endless (3000000s+ already).


texts: MongoDB sharding multiple collections with the same shard key

texts:
Lets imagine I have two mongodb sharded collection: likes & comments.

Both collections are sharded, both collections have userId as a sharded key.

Lets imagine that likes collection is huge and comments collection is small. I have 10 shards in total, let's name them shard A to shard G.

Can I assume that if some particular user's likes are in shard A, then his comments will be in shard A as well?

So what I want to achieve is to have any particular user's data in the same shard. Other user's likes may be in shard C, then his comments should be in shard C as well.

Is it possibly to configure MongoDB? ( or it's the way how it's working by default? )


texts: Column does not exist in UNION ALL query

texts:
I am doing some code for my badminton club.

Tables

CREATE TABLE School (
    Name text PRIMARY KEY
);

CREATE TABLE Tournament (
    Number integer PRIMARY KEY
);

CREATE TABLE Player (
    Name text NOT NULL,
    SchoolName text NOT NULL,
    Gender text CHECK (Gender IN ('Masculin', 'Féminin'))
);

ALTER TABLE ONLY Player
    ADD CONSTRAINT Player_pkey PRIMARY KEY (name, SchoolName);

ALTER TABLE ONLY Player
    ADD CONSTRAINT SchoolName_fkey FOREIGN KEY (SchoolName) REFERENCES School(Name);

CREATE TABLE Result (
    -- PlayerId_fkey text
    PlayerName_fkey text,
    SchoolName_fkey text,
    Category text CHECK (Category IN ('Benjamin', 'Cadet', 'Juvénile')),
    TournamentNumber integer,
    Score integer,
    TypeOfPlay text CHECK (TypeOfPlay IN ('Simple', 'Double'))
);

ALTER TABLE ONLY Result
    ADD CONSTRAINT PlayerName_fkey FOREIGN KEY (PlayerName_fkey, SchoolName_fkey) REFERENCES Player(Name, SchoolName);

ALTER TABLE ONLY Result
    ADD CONSTRAINT TournamentNumber_fkey FOREIGN KEY (TournamentNumber) REFERENCES Tournament(Number);

Query

-- Select each player with their single and double total score
SELECT PlayerName_fkey, ScoreSimple, ScoreDouble

FROM (
    -- Select the sum of the best results for each player in single play
    SELECT PlayerName_fkey, score, ScoreRank, SUM(score) as ScoreSimple
    FROM (
        -- Select the best four results in the season for each player in single play
        SELECT PlayerName_fkey, score, tournamentNumber, rank() over (PARTITION BY PlayerName_fkey order by Score desc) AS ScoreRank
        FROM (
            -- Select, for each player, the score for each tournament they played in single play
            SELECT PlayerName_fkey, score, TournamentNumber
                 FROM Result LEFT JOIN Player ON Player.name=Result.PlayerName_fkey
                 WHERE Category='Benjamin' AND Gender='Féminin' AND TypeOfPlay='Simple' AND score > 0 ) tBestIndividualScoresWithDuplicates
            GROUP BY PlayerName_fkey, score, tournamentNumber) tBestIndividualScores
        WHERE ScoreRank <= 4
        GROUP BY PlayerName_fkey, score, ScoreRank

    UNION ALL

    -- Select the sum of the best results for each player in double play
    SELECT PlayerName_fkey, score, ScoreRank, SUM(score) as ScoreDouble
    FROM (
        -- Select the best four results in the season for each player in double play
        SELECT PlayerName_fkey, score, tournamentNumber, rank() over (PARTITION BY PlayerName_fkey order by Score desc) AS ScoreRank
        FROM (
            -- Select, for each player, the score for each tournament they played in double play
            SELECT PlayerName_fkey, score, TournamentNumber
                 FROM Result LEFT JOIN Player ON Player.name=Result.PlayerName_fkey
                 WHERE Category='Benjamin' AND Gender='Féminin' AND TypeOfPlay='Double' AND score > 0 ) tBestIndividualScoresWithDuplicates
            GROUP BY PlayerName_fkey, score, tournamentNumber) tBestIndividualScores
        WHERE ScoreRank <= 4
        GROUP BY PlayerName_fkey, score, ScoreRank) t1

Question

The problem I have is I get the error: "column "scoredouble" does not exist". How do I get the scoredouble column display on my first query? I cannot use a JOIN because some players have scores in one type of play (singles or double) and not the other.

Link here: http://rextester.com/XIG14273


texts: Changed max_allowed_packet and still receiving 'Packet Too Large' error

texts:
I use mysqldump to create a flat file for backup purposes.  I have used this file to recreate the database on an alternate server.  I ran the import process through ssh on the command line and I received multiple Packet too Large errors.

I restarted mysql with a much larger max_allowed_packet (i.e.- 1000M) and still received the error.  I even attempted setting the max_allowed_packet in the import file, still received the error.

Is there a way to ensure the max_allowed_packet is set and/or use mysqldump that will create a file that does not cause this problem?

For reference:

the uncompressed mysqldump file is ~2GB

database type is INNODB


texts: mysqldump --single-transaction, yet update queries are waiting for the backup

texts:
If I use mysqldump --single-transaction, according to the docs it should do flush tables with read lock to get a consistent state and then start a transaction and no writers should be waiting.

However, I have caught the following situation last night:

excerpt from show full processlist:

hundreds of those...

   Command: Query
   Time: 291
   State: Waiting for table flush
   Info: insert into db_external_notification.....

then this:

Command: Query
Time: 1204
State: Sending data
Info: SELECT /*!40001 SQL_NO_CACHE */ * FROM `db_external_notification`

and the rest of the threads are in Sleep

does anyone have any idea what are these inserts waiting for? I don't see any FLUSH tables or DDL or anything mentioned in the manual that can cause the queries to wait.

full mysqldump command

mysqldump --quick --add-drop-table --single-transaction --master-data=2 -uxx -pxx dbname

I guess --quick is redundant here, probably a leftover from earlier times, this script is very old, but shouldn't hurt anyting


texts: When to use hashtable over binary search tree vice verse for Database Index

texts:
Database Index can be constructed using binary search trees (BST) or HashTables ,

From my understanding so far , HashTables are superior to BST in terms of time taken for searching and insertions however BST beats HashTables hand down in terms of space efficiency

Under what situation would it be good to use hashtables instead of BST , vice-verse to construct the Database Index , any there any other points i missed out ???.


texts: Copy SQL Server replication configuration from production to test environment

texts:
Where I'm currently working we have an SQL Server's instance for a billing database and another instance for customer database. Both databases keep some tables synchronized using replication mechanism. I'm trying to set up some automated way to get fresh backups from production into QA/DEV environments, but in order for our platform to be kind of a mirror of production environment we need this replication thing between customer and billing databases to work. I know very few about these kind of things, I'm only a developer by unfortunately no one else is willing to do it.

My question is, Is it possible to copy the replication setup in production to QA without having to go along all the process of setting up replication step by step in QA/DEV environments? Maybe generating some scripts of this setup from production and with some small changes enable it in QA/DEV environments?

Additional notes:

We use an instance for QA/Dev customer databases.
Another instance for QA/DEV billing databases.
Both instances run in the same server.

SQL Server version:

  Microsoft SQL Server 2016 (RTM-GDR) (KB3210111) - 13.0.1728.2 (X64)
      Dec 13 2016 04:40:28
      Copyright (c) Microsoft Corporation
      Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build 9600: ) (Hypervisor)

This is probably a dumb question, but I'd appreciate any help about it.

Thanks.


texts: Is there any disadvantage to using Oracle's Easy Connect syntax?

texts:
Oracle allows a few different syntaxes for specifying where to find your database service. The one I've seen most commonly (in the few short years I've been developing software professionally) looks something like this:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserver)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=myservice)))

I don't think this syntax has any particular name, but it is what Oracle generates in its TNS files if you use graphical tools to modify it. But Oracle also allows the following much more compact syntax, at least with tools like SQL*Plus and application divers:

myserver:1521/myservice

This is known as Easy Connect, and I have read that it was introduced by Oracle 10g. That makes it around 10 years old. I'm not sure if this can be used in a TNS file, and as short and simple as it is, I don't see much need to put it in a TNS file. Which is the good thing about it in my opinion: you can just use it directly with your application instead of needing to maintain a TNS file.

However, from the work I've done and the clients I've worked with, its usage seems to be rather uncommon. The only reason I even know about it is because ESRI started recommending its usage for establishing connections in newer versions of ArcGIS. That leaves me wondering why it's uncommon, so is there some kind of downside to using Easy Connect that I'm missing?

(I'd appreciate if someone could add more tags. I couldn't think of/find any more that made sense.)


texts: Changing autogrow settings with database mirroring

texts:
Recently I changed the Autogrow settings for some of the databases on our SQL Server 2008 R2 server. These are involved in a database mirroring configuration, with the principal on SERVER1 and the mirror on SERVER2.

This week I failed over three databases- now SERVER1 is the mirror and SERVER2 is the principal for these databases. It appears that the autogrow settings did not move over to SERVER2 properly, as the databases now show that they grow by a percentage (two are set to  32768%, the other to 131072%).

This is different than the settings that used to be there (I believe it was the default- 10%), and also different that the 256MB I set on SERVER1.

To make things more confusing, this is only happening on the primary file- the secondary files and log file has retained the settings I set on SERVER1.

My suspicion is that this is a bug- I did patch SQL after changing the autogrow settings. My question is- has anyone seen this scenario before? Is there a method to make sure all of the settings are correct on both servers without failing all the databases over?

UPDATE: Using a 4th database that I'll call DB1, I set the autogrow on SERVER1 to 512MB (after failing the database over, witnessing the same issue, and failing it back). When I failed it over to SERVER2 after that, it shows growth of 65536%. The takeaway is that the is_autogrow_percent value in sys.master_files is not moving to SERVER2, but the growth value is.

UPDATE2: With DB1 on SERVER1, I changed the autogrow to 10% and failed it to SERVER2. The 10% value remained. I then repeated this, setting autogrow back to 256MB this time. Again, the growth value changed but the is_autogrow_percent did not.


texts: How do I get the current unix timestamp from PostgreSQL?

texts:
Unix timestamp is the number of seconds since midnight UTC January 1, 1970.

How do I get the correct unix timestamp from PostgreSQL?

When comparing to currenttimestamp.com and timestamp.1e5b.de I don't get the expected time from PostgreSQL:

This returns the correct timestamp:

SELECT extract(epoch from now());

While this doesn't:

SELECT extract(epoch from now() at time zone 'utc');

I live in time zone UTC +02. What is the correct way to get the current unix timestamp from PostgreSQL?

This returns the correct time and time zone:

SELECT now();
              now
-------------------------------
 2011-05-18 10:34:10.820464+02

Another comparison:

select now(),
extract(epoch from now()),
extract(epoch from now() at time zone 'utc');
              now              |    date_part     |    date_part
-------------------------------+------------------+------------------
 2011-05-18 10:38:16.439332+02 | 1305707896.43933 | 1305700696.43933
(1 row)

Unix timestamp from the web sites:
1305707967


texts: mysql 5.7 general_log table has user_host but the file does not

texts:
I'm trying to resolve an issue where one particular user hammers our mysql 5.7 database every 2 hours, for a few minutes. Not evil, but I'm suspicious it may be inefficient (or it may not be and it's just the way it is).

The mysql.general_log table gives me everything I want, but I prefer the ease of a file. The output to the file specified with general_log_file works fine, but doesn't include the user_host data that the database table has.

I've read 5.4.3 The General Query Log, googled, but can't seem to find any hint of this.

Is there a format I can set that will include user_host into the file system log file? I must of missed something obvious, why wouldn't the output be exactly the same, file or table?

example of what I see in the file:

2018-03-01T15:41:45.450491Z     137300 Query    SHOW COLLATION
2018-03-01T15:41:45.451280Z     137299 Query    SET NAMES latin1
2018-03-01T15:41:45.451425Z     137299 Query    SET character_set_results = NULL
2018-03-01T15:41:45.451554Z     137299 Query    SET autocommit=1

PS

My workaround is

mysql --user etc... -e "select user_host,argument from general_log where user_host like 'billybob%'" > junk

which isn't as nice as (IMO)

grep billybob log_file

edit:

2018-03-01T15:41:45.450317Z     137300 Query    /* mysql-connector-java-5.1.16 ( Revision: ${bzr.revision-id} ) */SELECT
 @@session.auto_increment_increment


texts: Load Multiple images (BLOBs) into the Oracle Database

texts:
I have a folder with several images. My database table is a simple table with 2 fields: name (char) and image (BLOB). How can I import my images located in my C disk (e.g.: C:\Users\me\images) into my table?

Note: By using the SQL Developer I found a way to import 1 image per time, but I am looking for a solution that could import my entire folder; and when I am choosing import data from sql developer, I am getting the following error:

  There are no readers registered to the jpg type.


texts:
I have some columns representing Age in a table, taking values from 0 to 100, but also -1 for 'Missing Value'. I don't want to store them as strings to be able to find average age etc., but SSAS turns 0 into NULL by default, and I also want to replace -1 with NULL, so there is the classic "duplicate attribute key" error.

Is there a proper way / type to handle age values?


texts: Selection of first and last in non grouped item in SQL Server

texts:
Here's my table structure

Id  ToolName  ToolKey   ToolStatus
-----------------------------------
1    XXXX      123      Running
2    XXXX      123      Completed
3    yyyy      134      Running
4    yyyy      134      Completed

I have grouped the above statement by toolname and toolkey.Here's my query

SELECT
    TOOLNAME, ToolKey
FROM
    TOOLS
GROUP BY
    TOOLS.TOOLNAME, TOOLS.ToolKey

I need to have columns ToolStatus1 with value Running and ToolStatus2 with value Completed. If not completed means it should have null.

But in SQL if I add toolstatus in grouping, grouping will not be done.

In simple what I expect is like First() and last() as I will be having only two rows.

Let me know if unclear


texts:
I have this query that is giving me an error

  The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Query:

select
    COUNT(*)
from
    dbo.patient
INNER JOIN
    dbo.study on dbo.patient.pk = dbo.study.patient_fk
              and dbo.study.study_datetime IS NOT NULL
              and dbo.patient.pat_birthdate IS NOT NULL
              and dbo.study.study_datetime <= DATEADD(D, -2192, GETDATE())
              and dbo.patient.pat_birthdate <= DATEADD(D, -7670, GETDATE());

I read where this might help to be converted to 'DATETIME2', but I can't quite figure out how to correctly convert the column 's.study_datetime' as this column more than likely has some bad values as entries.


texts: What is blocking and how does it happen?

texts: I tried to find some information about blocking in SQL Server, but I could not find a concise explanation for what it is and how it happens. Could you please enlighten me?


texts: Got fatal error 1236 Slave has more GTIDs than the master has, using the master's SERVER_UUID

texts:
We are testing replication using GTID's on two servers running MySQL 5.6. Replication was running fine, but now it is not functioning. When I run show slave status\G on the slave, I get the following error message

  Last_IO_Errno: 1236 Last_IO_Error: Got fatal error 1236 from master
  when reading data from binary log: 'Slave has more GTIDs than the
  master has, using the master's SERVER_UUID. This may indicate that the
  end of the binary log was truncated or that the last binary log file
  was lost, e.g., after a power or disk failure when sync_binlog != 1.
  The master may or may not have rolled back transactions that were
  already replica'

I have tried to reset master and stop, reset, and restart slave, with no luck, and cannot find any information about this particular issue online.

Oddly, when checking show slave status\G on master, I get a different error.

  Last_Errno: 1007 Last_Error: Error 'Can't create database 'repltest';
  database exists' on query. Default database: 'repltest'. Query:
  'create database repltest'

But when I check both the slave and the master, the database 'repltest' does not exist. This was just a database we created on master to check that replication was working, and we deleted it from the master afterward.

Edit - Note: this is a staging environment that does not have active database connections from any applications. This was set up merely to test replication with GTIDs


texts: Postgresql installation failed if we provide non-english password(Chinese or French)

texts:
I am using Postgresql postgresql-9.6.2-2-windows-x64.exe installation on a French Windows OS.

I specified Télécharger as the password during postgres installation on the French OS.

(Note: I got the same error when I used Chinese characters.)

Locale used: C

I have tried even french locale but the result was same.

Please help.

Installation Logs:

[12:09:33] Write the base directory to the ini file...
[12:09:33] Write the version number to the ini file...
Initialisation du cluster de bases de données (cela peut prendre un certain temps)...
Executing cscript //NoLogo "C:\Program Files\PostgreSQL\9.6/installer/server/initcluster.vbs" "NT AUTHORITY\NetworkService" "postgres" "****" "C:\Program Files\PostgreSQL\9.6" "C:\Program Files\PostgreSQL\9.6\data" 5432 "C" 0
Script exit code: 1

Script output:
 WScript.Shell Initialized...
Scripting.FileSystemObject initialized...

Called CreateDirectory(C:\Program Files\PostgreSQL\9.6\data)...
Called CreateDirectory(C:\Program Files\PostgreSQL\9.6)...
WScript.Network initialized...
strParentOfDataDirC:\Program Files\PostgreSQL\9.6
logged in userFrench-win7\Admin
Called AclCheck(C:\Program Files\PostgreSQL\9.6\data)
Called IsVistaOrNewer()...
    'winmgmts' object initialized...
    Version:6.1
    MajorVersion:6
Executing icacls to ensure the French-win7\Admin account can read the path C:\Program Files\PostgreSQL\9.6\data
    Executing batch file 'radABEF2.bat'...
    fichier trait'˜: C:\Program Files\PostgreSQL\9.6\data
1 fichiers correctement trait's˜; 'chec du traitement de 0 fichiers

Called IsVistaOrNewer()...
    'winmgmts' object initialized...
    Version:6.1
    MajorVersion:6
Ensuring we can write to the data directory (using icacls) to  French-win7\Admin:
    Executing batch file 'radABEF2.bat'...
    fichier trait'˜: C:\Program Files\PostgreSQL\9.6\data
1 fichiers correctement trait's˜; 'chec du traitement de 0 fichiers

    Executing batch file 'radABEF2.bat'...
    Les fichiers de ce cluster appartiendront … l'utilisateur ® Admin ¯.
Le processus serveur doit ‚galement lui appartenir.

L'instance sera initialis‚e avec la locale ® C ¯.
La configuration de la recherche plein texte a ‚t‚ initialis‚e … ® english ¯.

Les sommes de contr“les des pages de donn‚es sont d‚sactiv‚es.

correction des droits sur le r‚pertoire existant C:/Program Files/PostgreSQL/9.6/data... ok
cr‚ation des sous-r‚pertoires... ok
s‚lection de la valeur par d‚faut de max_connections... 100
s‚lection de la valeur par d‚faut pour shared_buffers... 128MB
s‚lection de l'impl‚mentation de la m‚moire partag‚e dynamique...windows
cr‚ation des fichiers de configuration... ok
lancement du script bootstrap...ok
ex‚cution de l'initialisation aprŠs bootstrap...FATAL:  invalid byte sequence for encoding "UTF8": 0xe9 0x6c 0xe9
le processus fils a quitt‚ avec le code de sortie 1
initdb : suppression du contenu du r‚pertoire des donn‚es ® C:/Program Files/PostgreSQL/9.6/data ¯

Called Die(Failed to initialise the database cluster with initdb)...
Failed to initialise the database cluster with initdb

Script stderr:
 Program ended with an error exit code

Erreur dans l'exécution de cscript //NoLogo "C:\Program Files\PostgreSQL\9.6/installer/server/initcluster.vbs" "NT AUTHORITY\NetworkService" "postgres" "****" "C:\Program Files\PostgreSQL\9.6" "C:\Program Files\PostgreSQL\9.6\data" 5432 "C" 0 : Program ended with an error exit code
Un problème est survenu lors de l'exécution de l'étape de post-installation. L'installation peut avoir échoué
 L'initialisation du cluster de bases de données a échoué.
Création du Désinstalleur
Création du désinstalleur 25%
Création du désinstalleur 50%
Création du désinstalleur 75%
Création du désinstalleur 100%
Installation terminée
Log finished 09/12/2017 at 12:14:05


texts: Getting the value of 'foo' for the closest previous row with higher 'bar'

texts:
From this initial data:

select level as foo, mod(ora_hash(level),4) as bar from dual connect by level<9;
/*
FOO                    BAR
---------------------- ----------------------
1                      3
2                      2
3                      3
4                      3
5                      2
6                      0
7                      3
8                      2
*/

I want to get the value of foo, bar, prev_foo for each row, where prev_foo is the value of foo from the closest 'previous' row (defined by ordering on foo which is unique) such that bar in that row is greater than bar in the current row. In other words, I want this result:

/*
FOO                    BAR                    PREV_FOO
---------------------- ---------------------- ----------------------
1                      3
2                      2                      1
3                      3
4                      3
5                      2                      4
6                      0                      5
7                      3
8                      2                      7
*/

How do I achieve this?


texts: Compatibility level of databases based on sql server version

texts:
We need to check for databases that are not in the correct compatibility level on a particular instance. For example, on a SQL Server 2016 instance, if there are databases that are not in compatibility level of 130, we need to list them. Similarly, on a SQL Server 2017, , if there are databases that are not in compatibility level of 140, we need to list them.

Take a look into the below script

SELECT name as 'Database name',
       compatibility_level AS 'Compatibility level',
       @@VERSION as 'SQL Version'
FROM   sys.databases S


texts: Monitoring SQL Server 2012 slow queries/queries which are causing blocking

texts:
I am using SQL Server 2012 and I want to get hold of the queries which are taking longer to execute along with the queries which are causing blocking on the server. When I look at the server for the running queries to identify any blocking I don't find any query.

I have setup extended events and getting only high level information from that. The tables which are getting timeouts but I need information which tells more about the queries which caused the timeouts.

Any suggestions???


texts: Mail alert from MySQL when databases are edited

texts:
How do I get mail from MySQL when root or someone else edits or updates a particular database? It should trigger sending a mail and log the edits.

I have tried Triggers but its not working.

Server specifications:

Ubuntu 10.04 64 Bit
MySQL Server 5.1.41

script which i have tried

  #!/bin/sh

  tail -f /var/log/mysql/mysql.log | egrep 'INSERT|UPDATE|DELETE|root'
      | mail -s 'backend update' sushanth@example.com

but its not working


texts:
I have made an attempt at the ROW_NUMBER(), and when exporting the results in a csv it was worked perfectly. The issue I’m having now is in the where clause to bring back records where ROWNUM = 1.

I am getting the error message:

  Column or global variable ROWNUM not found.

I have tried using FETCH to bring back one row; this works however the following join does not get considered.

Do you know what the best method is to bring back only records with ROWNUM = 1?

LEFT JOIN
(
    SELECT
        CONO15, ACTR15, FLG115,  MEMB15 AS PAYOWNER,
        PYTP15 AS PAYTYPE, PYRF15 AS PAYREF,
        ROW_NUMBER() OVER (
            PARTITION BY MEMB15, PYTP15, PYRF15
            ORDER BY  PYTP15 ASC) AS ROWNUM
    FROM TKTDTA.PY015L1
    WHERE ROWNUM = 1
)C ON C.CONO15 = A.CONO09 AND C.PAYREF = A.PAYREF


texts: Can I update my Microsoft Enterprise SQL Server?

texts:
I just have a quick question about SQL Server and the various tools that come with it.

I updated my SQL Server Management Studio to the latest version because I wanted to use the STRING_AGG function, but apparently that is separate from the actual SQL Server.

So I am trying to update my actual SQL Server. I downloaded this link, is it the correct one? (SQL Server® 2017 for Microsoft® Windows Latest Cumulative Update)
https://www.microsoft.com/en-us/download/details.aspx?id=56128

I ran it but it seems that it didn't upgrade anything. All it seems to do was create an instillation center. Is that where I have to go (I need admin privileges for every action so it is difficult for me to check)?

Is it even possible to do this upgrade. I work for a company and we use Enterprise Edition. We connect to two different third-party databases. Will updating on my local computer work? Or do I have to speak to the DBA and get it updated on some main server?


texts: Storage provisioning for Microsoft SQL Server

texts:
I need help with my Microsft SQL Server file setup!

I have a TEST Microsoft SQL Server with 3 RAID1 (SSDs):

C: (240 GB)  - O/S installed
L: (460 GB)
M: (900 GB)

I have a PROD Microssoft SQL Server with 4 RAID1 (SSDs):

C: (240 GB) - O/S installed
L: (900 GB)
M: (900 GB)
T: (240 GB)

What is the best way to spread the SQL files for Test and Prod Servers?


texts: Minimizing MySQL downtime during OS update?

texts:
Alright, this one should be simple. We would like to be able to perform scheduled OS updates on production DB nodes w/o excessive downtime. Our setup:

MariaDB Primary "W1" (write+read)
MariaDB Async Replica "R1" (read only)

In off-hours, "slow time", there are ~100 active connections coming from 10-15 app nodes (AWS cloud) generating about 5-10 TPS.

The way we plan on doing this (through automation)

Create a 2nd Replica (R2) based on R1 with the latest OS patches applied to it. Reconfigure the nodes to hit W1+R2 in place of W1+R1 [OK; no service interruption so far]
Discard R1, no use of it anymore.
(Service interruption starts now) Allow existing in-flight transactions to finish gracefully on W1 but block new transactions on it with FLUSH TABLES WITH READ LOCK.
When the FLUSH... command finishes and we (somehow) determined that the async-replica is fully sync'ed, we change the roles of the servers and remap in DNS: W1 is R1 and R2 is W1
Bounce client nodes to pick up the DNS change and reconnect into the new setup.
If everything works, the R1 (old W1) is still outdated wrt OS patches, but that's easy update w/o service interruption, per point #1.

During the points 3-5 the system is briefly offline and clients see errors. The time should be short but appears unavoidable (w/o relying on Galera which we have other issue with BUG MDEV-18641).

If there is another - w/o service interruption - way to accomplish above, I'm very curious.

Thanks a ton!


texts: Column containing the word from another column

texts:
I am trying to select records in a postgresql where the service column contains usernames.

Something like this but not 1 to 1:

select service, cash from services where service not in (select name from users);

and this but for all users:

select service, cash from services join users on service not like '%' || 'username' || '%' and user = id_users;

this show me all records from services:

 select service, cash from services join users on service not like '%' || name || '%' and user = id_users;

example:

    CREATE TABLE users (
    id_users SERIAL PRIMARY KEY,
    name character varying(50) NOT NULL
);

CREATE TABLE services (
    service text,
    cash real,
    usery integer REFERENCES users (id_users)
);

insert into users (name)
values ('user1'),('user2'),('user3'),('user4');
select * from users;

insert into  services (service, cash, usery)
values ('user1','123','1'),
       ('something user2 something','123','1'),
       ('something user3 something','123','3'),
       ('something something','123','1'),
       ('fhalsefh','123','4');
select * from services;

I expect:

service              |  cash  |  usery
---------------------------------------
something something  |   123  |    1
fhalsefh             |   123  |    4


texts: Find missing folders paths in split hierarchies

texts:
I have a table which contains folders' paths. This table contains four columns:

DirID - The folder's ID.
BaseDirID - The ID of the first folder in the hierarchy. So all the folders (paths) from the same hierarchy share the same value in this column.
DirLevel - The depth of the folder.
DisplayPath - The folder's path.

I need to find all the "gaps" between those folders in the hierarchy.

Sample data for example:

DirID BaseDirID DirLevel DisplayPath
1         1        1     'A'
2         1        3     'A\B\C'
3         1        5     'A\B\C\D\E'
4         1        3     'A\B\F'
5         1        5     'A\B\F\G\H'
6         2        1     'U'
7         2        3     'U\V\W'
8         2        5     'U\V\W\X\Y'
9         2        3     'U\V\M'
10        2        5     'U\V\M\L\O'

So we need to find the following data:

BaseDirID   DisplayPath
1           'A\B'
1           'A\B\C\D'
1           'A\B\F\G'
2           'U\V'
2           'U\V\W\X'
2           'U\V\M\L'

Comments:

This table contains more than 250,000 records of folders, so we seek for the most efficient way to do so, otherwise, the script will be stuck for a long time, the time we don't have.
I don't have the list of all folders. What I have are the "root" folders and the "leafs" folders which I need to find the "gaps" between them in the hierarchy.
The table can contain more than one hierarchy and we need to find the "gaps" in all of the hierarchies.
Each of the hierarchies can split, as you can see in the sample data the first hierarchy splits to two folders paths from the 'A\B' folder:
'A\B\C' and 'A\B\F'.
And the second hierarchy splits to two folders paths from the 'U\V' folder: 'U\V\W' and 'U\V\M'.
And we need to find all the "gaps" even in such cases when the hierarchies split.
We can make any changes to the table - add pk, indexes etc...
The SQL Server version is 2012 SP3.
The real folder names could be anything - one character or more.
The results must only contain the "gaps".
All missing intermediate folders are required.

Extended sample data and expected results in this dbfiddle.

This question is a continuation of the Stack Overflow question Find missing hierarchy Folders (Paths) in a table. Our question includes also the 4th comment which appears in bold.

I saw that there is a new type called hierarchyid (starting from SQL Server 2008), which I thought might help us. What do you think?


texts: Can't create table, but table doesn't exist

texts:
I'm using these steps to create a table my_user, that already existed but somehow vanished from my database my_db:

mysql> USE my_db;
mysql> DROP TABLE my_user;
mysql> ERROR 1051 (42S02): Unknown table 'my_user'
mysql> CREATE TABLE my_user (id INT AUTO_INCREMENT NOT NULL, username VARCHAR(255), group_id VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
mysql> ERROR 1005 (HY000): Can't create table 'my_db.my_user' (errno: -1)

Tried # mysqladmin flush-tables and repeated the steps above but it wasn't helpful. Also, restarted the mysql service, but no good.

Any ideas? Google has failed me so far. Thanks.

Extra info:

mysql> SHOW engine innodb STATUS;
------------------------
LATEST FOREIGN KEY ERROR
------------------------
140703 15:15:09 Error in foreign key constraint of table my_db/my_user
there is no index in the table which would contain
the columns as the first columns, or the data types in the
table do not match the ones in the referenced table
or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:
,
  CONSTRAINT "FK_CFBD431E285FAC6D" FOREIGN KEY ("group_id") REFERENCES "my_group" ("id")


texts: Modification of the sql loader control file

texts:
I am working on a script which uses Oracle SQL *Loader to load data into a table. However, anywhere I search about the control file of an sql loader I am seeing only statements like:

"load data into table TABLE_NAME TRUNCATE
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
(
Field1,
Field2,
Field3,
Field4)
'

Can I insert select statement in this control file ?

I suppose want to verify if field3 in the incoming record has a certain value for which a record can be found in an existing table containing a similar field and if yes then it load the data and if no then it would send an output to a log file.

For example, for the incoming record, say p, I want to verify if the count of records for (select * from table_k where field_demo = p.filed1) is greater than zero or not. If it is greater than zero then it would insert the record into the table otherwise show an output message to the log file.


texts: Track my own custom metadata on Postgres tables and columns

texts:
Is there any way to add my own metadata to tables and columns in Postgres, such as key-value pairs of short text?

I am migrating tables, columns, and data from another database system. That other system has properties defined on tables and columns not matched by Postgres. For example, in the other database system each table can be assigned a color for use in graphical display of the database structure such as CUSTOMER_ table being green and INVOICE_ table being red.

So I would like a way to keep track of those property settings within my Postgres database in such a way that my Java app may query for the custom metadata via JDBC.

SET attribute ?

I noticed:

ALTER TABLE … SET ( attribute_option = value [, ... ] )

…and…

ALTER TABLE … ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )

The documentation for ALTER TABLE does not really explain what these attributes do. Can I invent my own attribute names to be used as keys that map to my own metadata values?

Or is there some other approach to having Postgres remember my own custom metadata?

Comment

I suppose one workaround might be embedding key-value pairs as text with the COMMENT property on each table and column. Seems like kind of a hack, and kind of messy as I do often use COMMENT to document business rules as prose. But I suppose it would work. Any better way to remember per-table and per-column attributes?

Extended Properties of Microsoft SQL Server

Apparently MS SQL Server offers an "Extended Properties" feature for such meta-data. Described here on Stack Overflow and here on Microsoft doc.

Perhaps something like that in Postgres?

Additional tables

Of course I could define my own extra tables to store this meta-data. But I was hoping for something tied to the targeted tables and columns to be more obvious to those maintaining this app down road. No point in re-inventing the wheel if Postgres already provides me with a custom meta-data facility of some sort.


texts: Index for VARCHAR column has low efficiency

texts:
I have two tables with relationship one to many.

create table locations
(
  id bigint
     auto_increment
     primary key,
  location_id varchar(128)
     null,
  constraint locations_location_id_uindex
     unique (location_id)
);

create table links
(
  id bigint
     auto_increment
     primary key,
  location_id varchar(128)
     null,
  loc_id bigint
     null,
  constraint links_location_id_fk
     foreign key (loc_id)
     references locations (id)
);

create index links_location_id_index
   on links (location_id);

The locations table has 596_752 records and links table has 1_221_450 ones.  Both tables are very often updated with a new data, there is no a deletion process. The location_id in both tables has size 30 characters.
When a new data appear in links table I have to find all links.location_id that do not exist in locations.location_id. In order to do so I execute the following query:

SELECT links.location_id
FROM links
LEFT JOIN locations l
   ON links.location_id = l.location_id
WHERE l.location_id IS NULL

The log output says:

34 rows retrieved starting from 1 in 1 m 11 s 678 ms (execution: 1 m 11 s 668 ms, fetching: 10 ms)

As you can see, it works too long and I am looking for a way how to improve it, can You help me?

EDIT
The info about MySQL server, it's db.t2.micro AWS instance:

innodb_version  8.0.13
protocol_version    10
slave_type_conversions  ""
tls_version TLSv1,TLSv1.1,TLSv1.2
version 8.0.13
version_comment Source distribution
version_compile_machine x86_64
version_compile_os  Linux
version_compile_zlib    1.2.11


texts: SQL Server 2000 Page Splits per second higher han 12 million?

texts:
I was reading about index fill factors & performance, and out on a whim I tried the query given by the webpage on the company's production SQL Server 2000 server:

SELECT *
FROM MASTER.dbo.sysperfinfo
WHERE counter_name ='Page Splits/sec' AND
OBJECT_NAME LIKE'%Access methods%'

Imagine my shock when I saw the result is 12164778 (yes, that's larger than 12 million).

Is it actually possible to have 12 million page splits per second? Or am I reading the numbers incorrectly? Or is there a mistake in the query?


texts: I am a SQL DBA newbie, and I am trying to get the failover/failback history for one of our mirrored servers. I am able to get the the failover history from event viewer but do not know how to the get the failback history. Your help is greatly appreciated!


texts:
I am trying to figure out the data model for a retailer.

The retailer has several stores across the country and they are modeled using the following hierarchy:

Channel -> Zone -> City -> Store

Each store contains several articles. And each article has attributes like

Activation flag (this indicates the presence of article)
Price
Supplier
Warehouse

Now, the retailer can set these attributes at any level in the hierarchy. Consider the following cases:

Setting price for an article at channel level will apply it to all stores.
The price set at a higher level can be overridden at any other level. For e.g., at a city level for just the stores in a city or for a particular store.
This applies to all attributes listed above.

As of now, they have modeled it using RDBMS by defining global rules at the top of hierarchy and calling out exceptions separately as individual rows. Say, price table, will have price set for an article at Channel level and any changes at any level will be specified separately. Obviously, this is not very efficient when fetching the attributes at the store level.

Sample Data

Assume Channel, Zone, City and Store are collectively called entities. Channel will have ids ranging >= 4000, Zone >= 3000, City >= 2000 and stores range from 1 to 1000.

A subset of the hierarchy relationship data is given below:

Channel   | Zone     | City    | Store |
----------+----------+------------------
4001      | 3001     | 2001    | 13    |
4001      | 3001     | 2001    | 14    |
4001      | 3001     | 2002    | 15    |
4001      | 3002     | 2003    | 16    |
4001      | 3003     | 2006    | 74    |

Price Table

ArticleID | EntityID | Price
----------+----------+----------
12345     | 4001     | 2.5
12345     | 2003     | 2.9
12345     | 74       | 3.0

Here, the price 2.5 for channel 4001 will be set for the article in all stores. The next two rows set the price exceptions in some stores. The second price 2.9 set for city 2003 will be applicable only for the article in store 16 as per the hierarchy relationship mentioned above. And the third row directly sets price 3.0 for the article in store 74.

Hope this gives an idea of current model. So, can you please suggest a better way to store this?


texts: How much data is needed to show MySQL Cluster's performance scaling vs. InnoDB

texts:
I am evaluating MySQL Cluster as a possible replacement for an InnoDB schema.  So far, I have tested it with 10s of MB of data, and found MySQL Cluster slower than InnoDB; however, I have been told MySQL Cluster scales much better.

How much data does it take to show a performance benefit to MySQL Cluster vs. an InnoDB schema?  Or, is there a better way to demonstrate MySQL Cluster's merits?

EDIT

Perhaps an important note:  My cluster is currently a heterogeneous cluster with 4 machines.  On each machine, I have given an equal amount of Data and Index Memory; 4GB, 2GB, 2GB, and 1GB respectively.  The machines are running i7's and are connected over a Gigabit Lan.  NumOfReplicas is set to 2.

EDIT

This application is a low-usage analytics database, which has roughly 3 tables >= 200M rows and 5 tables <= 10K rows.  When we use it, it takes 15 seconds to run our aggregate functions.  My boss asked me to research MySQL Cluster, to see if we could increase performance, since we thought aggregate functions could run pretty well in parallel.


texts: Two physical servers with sql server on same network

texts:
IS there a way I can remotely access sql server DB having two different servers in the same network/public IP?

The first Server is configured to listen to 1433 port but we make the connections using No-Ip provider.

As both servers will be under the same DNS, how can I configure my app/sql server to connect to the DB in the second server?


texts: What are the dangers of not gracefully shutting down mysql server

texts: What are the dangers of not shutting down a windows MySql server gracefully (i.e. hitting the reset switch on the server as apposed to shutting the OS down) ?


texts: Dealing with 130 currencies for products MySQL

texts:
Currently we have a database that stores many products but only in a few currencies. This has meant storing the price with the product entry and it works well enough.

We are about to open things up though and allow 130+ currencies. As we allow a product to be sold in multiple currencies (of the clients choice) this produces a problem with the current setup.

What I am proposing is a table that comprises of the product id and each currency, so a look up table for price. Of course only the relevant currencies will be selected when in operation.

Although this will keep the main product table uncongested it still seems a bit unwieldy especially as many of the currencies will be very limited in use.

In would be good to have a number of tables to break things up a bit but then I don't know how a JOIN would occur, as far as I know you can't get a value from table A and work out which table to JOIN with based on that value(s).

Or maybe 130 columns just isn't something to worry about?


texts:
This question is in reference to my last question: SQL Server 08: Union over while

I'm writing a CTE to do what was described in that question (find available time slots over a given number of days.

I have a CTE written, but I can't quite grasp how to get my date incrementing and limited. As it stands, it only gets data from the 2011-07-30 (@ArrivalDate). Can anybody explain to me how I can get it properly incrementing, and where I'm going wrong?

Update: I've come up with a better query for getting all the available time slots

(SELECT TS.UKTNN, TS.TimeSlotID, TS.TimeSlotGroupID
 FROM FittingCentreTimeSlotGroupTimeSlots TS)
EXCEPT
(Select BFD.UKTNN, BFD.TimeSlotID, BFD.TimeSlotGroupID
 From BookedFittingDates BFD)

And here is the table structure as requested in the comments:

TimeSlotGroup
UKTNN, TimeSlotGroupID, TimeSlotGroupDesc

TimeSlotGroupTimeSlots
UKTNN, TimeSlotID, TimeSlotGroupID, TimeSlotDesc

BookedDates
UKTNN, TimeSlotGroupID, TimeSlotID, Date

Here is what my existing CTE looks like right now

Use[DEV_UKTN_DATA]
GO
DECLARE @UKTNN int;
DECLARE @ArrivalDate date;
DECLARE @NumDays int;
DECLARE @LoopDate date;

SET @UKTNN = 7;
SET @ArrivalDate = '2011-07-30';
SET @LoopDate = @ArrivalDate;
SET @NumDays = 2;

WITH GetTimeSlotsOnDays(TimeSlotGroupID, TimeSlotGroupDesc, TimeSlotID, TimeSlotDescription, AdditionalCost, UKTNN, CurrentDate, Distance)
AS
(
    SELECT TSG.TimeSlotGroupID,
           TSG.TimeSlotGroupDesc,
           TS.TimeSlotID,
           TS.TimeSlotDescription,
           TS.AdditionalCost,
           @UKTNN AS UKTNN,
           @ArrivalDate as CurrentDate,
           0 As Distance
    FROM FittingCentreBranchTimeSlotGroups TSG
    INNER JOIN FittingCentreTimeSlotGroupTimeSlots TS
               ON TSG.TimeSlotGroupID = TS.TimeSlotGroupID AND
                  TSG.UKTNN = TS.UKTNN
    WHERE TSG.UKTNN = @UKTNN AND
          TS.UKTNN = @UKTNN AND
          TS.TimeSlotGroupID = TSG.TimeSlotGroupID AND
          NOT EXISTS(
                SELECT *
                FROM BookedFittingDates BFD
                WHERE BFD.TimeSlotGroupID = TS.TimeSlotGroupID AND
                      BFD.TimeSlotID = TS.TimeSlotID AND
                      BFD.UKTNN = @UKTNN AND
                      BFD.Date = @ArrivalDate)
          --@ArrivalDate <= DATEADD(DAY, @NumDays, @ArrivalDate)

    UNION ALL --Recursion starts here

    SELECT TSG.TimeSlotGroupID,
           TSG.TimeSlotGroupDesc,
           TS.TimeSlotID,
           TS.TimeSlotDescription,
           TS.AdditionalCost,
           @UKTNN UKTNN,
           @ArrivalDate As CurrentDate,
           Distance + 1 As Distance
    FROM FittingCentreBranchTimeSlotGroups TSG
    INNER JOIN FittingCentreTimeSlotGroupTimeSlots TS
               ON TSG.TimeSlotGroupID = TS.TimeSlotGroupID AND
                  TSG.UKTNN = TS.UKTNN
    INNER JOIN GetTimeSlotsOnDays as GTSOD
               ON GTSOD.CurrentDate = DATEADD(DAY, Distance, @ArrivalDate)
)

SELECT *
FROM GetTimeSlotsOnDays GTSOD
WHERE GTSOD.CurrentDate = @ArrivalDate AND GTSOD.Distance = 0
ORDER BY GTSOD.TimeSlotGroupID ASC, GTSOD.TimeSlotID ASC
GO