texts:
I logged into the mysql from the command line using the following command and in addition to that I was trying to import the sql file into the database as follows in the same line:

[username@abc-xyz web_app]$ mysql -u root -pmypassword -Dtestdb < TestDB.sql

Where, testdb is the name of the database.

Note: web_app is the folder where I have my TestDB.sql file present which I want to import it into the database.

After running the above command, nothing happens but I see a warning :

Warning: Using a password on the command line interface can be insecure.

Please let me know what am I doing wrong?


texts: Enforce unique constraint on 1 of n values

texts:
Let's say I have a table like this.

create table foo (
    id serial primary key,
    kind smallint not null check (kind >= 0 and kind < 3)
);

With that check constraint, Postgres will only allow kind to be an int between 0 and 2, inclusive. So something like this is valid.

id | kind
---------
1  | 0
2  | 0
3  | 1
4  | 1
5  | 2
6  | 2

However is there a way to say, "at most 1 row can have the value 2"?

time=1
id | kind
---------
1  | 0
2  | 0
3  | 1
4  | 1
5  | 2

time=2
id | kind
---------
1  | 0
2  | 2
3  | 1
4  | 0
5  | 0

time=3
id | kind
---------
1  | 0
2  | 1
3  | 1
4  | 0
5  | 0

Different rows should be allowed to have kind=2 at different points in time, but at most 1 row can have kind=2.

Note, the other values should be unrestricted. You can have as many kind=0s and kind=1s as you like.


texts: new Neo4j install: "No authorization header supplied" even after editing config file

texts:
I just installed Neo4j today to play around on my Ubuntu EC2 instance, and I'm having two problems. First, the ubuntu install did not create a neo4j command so I'm not able to issue commands from the cli. Second, when I try this sample command to add a node to the database, I get the error

curl -H "Accept: application/json; charset=UTF-8" -H "Content-Type: application/json" -X POST http://localhost:7474/db/data/cypher -d '{
  "query" : "CREATE (n:Person { name : {name} }) RETURN n",
  "params" : {
    "name" : "Foo"
  }
}'

I get this error:

{
  "errors" : [ {
    "message" : "No authorization header supplied.",
    "code" : "Neo.ClientError.Security.AuthorizationFailed"
  } ]

I read the docs here, and I modified the config file per these instructions:
http://neo4j.com/docs/stable/rest-api-security.html

I updated the config file so that it should not require authentication:

dbms.security.auth_enabled=false

But still my curl requests to my localhost Neo4j server are blocked. All I can think is maybe neo4j needs a restart, but since I have no neo4j command, I can't use neo4j restart. I logged into the neo4j shell, but in the shell's list of commands there is no restart.

So my questions are:

(1) How can I restart neo4j?

(2) Where is my neo4j command/how can I create one following a standard ubuntu install?

(3) How could I create a username/password if I wanted to keep the access authentication in place?

Thanks.


texts:
Is it possible to "tag" a PostgreSQL database with meta-data?

Here is my intended use case: I have a database that goes through several workflow steps, and it would be nice to record somewhere around a database that a step has been taken. Of course there could be a table inside the database for recording this, but I am not supposed to change the schema for the task at hand.

Could/should e.g. custom options (e.g. ALTER DATABASE db SET a.b = 'c') serve such a purpose?


texts: Oracle 11 rman db restore file corruption

texts:
First to mention that I came to the need of asking help after furious research I reattempts for about 3 weeks. I checked previous threads in Oracle community but nothing works for me. I am trying to restore Oracle 11.1.0.7. multi HDD cluster production on single hdd locally on amazon ec2.

Restore is done from full back-up copy I downloaded from ftp location (I have no access to production,export and etc.) just have access to the files, i.e. I am not able to change anyhow export procedure.

So here are my steps in brief (I guess that problem might be in some of them):

rman target /
set DBID ...
startup nomount
RESTORE CONTROLFILE FROM 'file destination'
shutdown;
startup mount;

Catalog the backup files to rman, for all AL and BK files:

catalog backuppiece 'C:/BKUP/AL_2851492942_20151016_893271615_PGQJSFHV_1_1';

Rename all redo files to match my local directory structure

rman:crosscheck backup; Run{
SET UNTIL SEQUENCE 50511;
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE;
}

sqlplus sys/sys as sysdba

shutdown immediate; startup mount;
alter database noarchivelog;

Drop the no longer used log files.
Drop these in state UNUSED left Active and Inactive.
Drop all temp files(they point to other drive letter):

ALTER DATABASE OPEN;
ALTER TABLESPACE mytempspace
ADD TEMPFILE 'c:\oracle............dbf' SIZE 1G REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

My problem is that on my second query (get) to db through my app I receive:

ORA-01578: ORACLE data block corrupted (file # 64, block # 2559928)
ORA-01110: data file 64: 'C:\ORACLE\ORADATA\mydb\mydbLOBDATA2_1.DBF'
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-06512: at "mydbUSER.POBJECTSTATE", line 78
ORA-06512: at line 1

What I was able to find with validation and it is LOBSEGMENT - Lob_ObjectState and it logically corrupted.

I tried DBMS_REPAIR and with rman advise failure (which suggest to restore my copy from backup - nothing useful).

Since backup are refreshed every week I try with new ones from the next week, again the same problem.

Is there a possibility that the problem starts from export (how can I check this - I do not have access but i may ask db admin to check anything)?


texts:
On a (owned) shared server sometimes mysql writes alott of .mad and .mai.
This happens since i think 2 or 3 months, completely random.
It may happen 3 times a day, then it does not happen for a couple of weeks and then on a monday morning the issue occurs again.

It writes alott of files quickly. The disk is full very fast.
I changed the tmp folder to a different location so it could write more files.
The location it writes now is 16gb and also gets full.

I found this issue on multiple locations and tried some solutions.
I tried to limit tmp files that could be written with tmp_disk_table_size and tmp_table_size but this did not seem to help.

I also tried to enter internal_tmp_disk_storage_engine=MyISAM in my my.cnf but then mysql would not start.
I did this because i googled for a solution to this error in error logs:

Cannot add field images in table database_name.`jospr_tags` because after adding it, the row size is 8769 which is greater than maximum allowed size (8126) for a record on index leaf page.

The problems could maybe stop if all database where changed to innodb, but i cannot do that easily. These are not my databases.

I have alerting on the server, so when the disk is almost full i get a message. When i quickly login, the disk is already full and checking the processlist in mysql does not show a query, problably because it stops because the disk is full.

[ERROR] mysqld: Disk full (/mysqltmp/#sql_3d1b_3.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")

At the moment i am unsure what would help or what i can do to find any cause.
All files that are written are owned by mysql.
When i try to tail a file it only says mysql and when i delete a file the process stops and all files are gone.

The only solution i see at the moment is mounting an additional large disk so it can write longer so we can find the cause. However it could take weeks before this issue occurs again and i really would like to solve this quicker or more efficient.

I really hope some people have more information for me what could cause this and ofcourse any solutions.

BTW: All site using MyIsam seem to be Joomla (3.x) sites.


texts: Moving SQL Server instances from one datacenter to another

texts:
I am trying to find some workaround for moving SQL Server 2005/2008 instances from one data center to another data center without changing database configuration settings at application end, because application team is not sure where server name and database name are hardcoded. I know this is bad practice but the application is developed by a third party and we don't have control of application code.

So the challenge here is to keep the server name, database name, and IP the same.

Note: OS does not support Image Backup so this option is ruled out.

Please let me know your thoughts.


texts: SSIS - The value could not be converted because of a potential loss of data, runs fine in BIDS, not on the server

texts:
The value could not be converted because of a potential loss of data

I'm getting this error when running an SSIS package from a server job. Normally this is some sort of type error, but I can't see it this time. When I run the package from inside the BIDS environment, it executes successfully.

The package is loading a csv file into a database table. Both executions point at the same file and same database and I have re-uploaded the package to  the server to ensure I am comparing like versions.

What would cause this error to happen when executed on the server, but be fine when run through BIDS?


texts: PostgreSQL psql command line tool and SET CONSTRAINTS DEFERRED

texts:
I am using PostgreSQL 9.1.5 and the psql to run a scipt containing a series of INSERT statements in various tables:

psql -U usernamefoo databasenamefoo -f dml_script.sql

The table constraints have been created with the DEFERRABLE option and the script itself (file dml_script.sql above) issues the following before an offending INSERT:

SET CONSTRAINTS ALL DEFERRED;
INSERT INTO KM_TRANSACTION_GROUPS ..

I've checked that the specific constraint on the table in question has been created with the DEFRRABLE option:

ALTER TABLE ONLY KM_TRANSACTION_GROUPS ADD CONSTRAINT TRGR_TRGR_FK FOREIGN KEY (TRGR_PARENT_ID) REFERENCES KM_TRANSACTION_GROUPS(TRGR_ID) DEFERRABLE;

Yet, running the DML script fails:

psql:./schema-dml-test.sql:8: ERROR:  insert or update on table "km_transaction_groups" violates foreign key constraint "trgr_trgr_fk"
DETAIL:  Key (trgr_parent_id)=(2) is not present in table "km_transaction_groups".

My guess is that psql treats each INSERT line in the dml_script.sql as a separate transaction. If that's the reason, is there a way to group INSERT statements in the dml_script.sql file into one longer transaction (or perhaps treat the whole file as one single transaction) so that at its end (when the scope of the SET CONSTRAINTS ALL DEFERRED; terminates), no constraints are violated?

Otherwise some other method (other then changing the order of the INSERT statements which I have reasons I don't want to) to allow me to load the table with a series of INSERT statements so that constraints can be temporarily deactivated ?


texts: MySQL: Select rows, grouped by ID, where none of the rows have a value in a certain column

texts:
Confusing title, I know. Ii couldn't think of a better way to word it.
Basically, I have a table like this:

OwnerID | LotID | CurrentOwner

The OwnerID is the unique key for the table. This value is never repeated.
The LotID IS repeated. This table stores historical owner records for lots.
The CurrentOwner column simply contains a Y or NULL for each row. It is possible for one LotID to have multiple owners.

What I need is basically just for reporting. I need to know which LotIDs DO NOT have a "Y" for CurrentOwner in any of their rows. If I had the following data:

1, 1, Y
2, 1, NULL
3, 1, NULL
4, 2, NULL
5, 2, NULL
6, 3, Y
7, 3, NULL

Then I would need to know that LotID #2 does not have any owners currently.


texts: First query running in PL/SQL Developer results the error

texts:
When I run first query in PL/SQL Developer I got the error:

Dynamic Performance Tables not accessible,

Automatic Statistics disabled for this session

You can disable statistics in the preference menu, or obtain select
privileges on the v$session, v$sesstat and v$statname tables

How can I fix the error?


texts: Postgresql: Disallow duplicate value to be inserted if already in any of multiple columns?

texts:
Suppose I have 3 columns:
Critical, Medium, Low
And I have data UUIDs to be inserted from another tables. And I want to disallow insertion of same UUID in any of the column if it is already present any one of these columns. How can I do that in postgres? How should I setup my table?
example:
--------------------------
|Critical | High  | Low   |
--------------------------
|   A     | null  | null  |
|   null  |  B    | null  |
---------------------------

Now when I try to insert B (again) in any column Critical, High or Low. It should ignore the insert.


texts:
I have a large database with size of 40 GB and a one of the main tables which have row count of 3763071 rows and name of "XXX" and that table has a clustered index which is the primary key

suddenly that clustered index got disabled and the queries stopped

and i have a huge concurrent access on that table
And i have a full text index which got disabled too on the same table
and when i opened the logs found that the sql got some updates just before that problem. I am using dot net framework 4.5 and entity framework as ORM and sql server 2014 .so i'm asking what could cause that problem to avoid it again??
and one another question i have more than 25 index on that table
so is it bad or good to have that number of indexes which have repeated columns?


texts: Updating Database table from another machine

texts:
I am trying to update my database using a batch file. Batch file is located on machine say ABC which should pick a text file from the local directory on ABC and upload that file to DATABASE located on say XYZ machine.

I am getting an error as

If you are using a remote database server, verify it has access to remote_basedir.

ERROR>:   Error is: [Oracle][ODBC][Ora]ORA-29913: error in executing    ODCIEXTTABLEOPEN callout
ERROR>: ORA-29400: data cartridge error
ERROR>: KUP-04040: file testfile.txt in TEST_9F86D0818 not found

I am calling schedules created in NAVICAT to upload .txt files to DATABASE.


texts: What database technologies do big search engines use?

texts:
Does anybody knows how Google or Yahoo perform searches for keywords against very very huge amounts of data?  What sort of database or technologies do they employ for this?

It takes few milliseconds, but they have more than a billion pages indexed.


texts: What are Objective Business Reasons to Prefer SQL Server 2012 over 2008 R2?

texts:
My company is facing the decision whether to purchase SQL Server 2012 Denali or SQL Server 2008 R2 for a new database server. I am looking for objective reasons to choose one over the other.

Our requirements:

Standard edition (for financial reasons and a lack of need for enterprise features)
OLTP workload (this means we don't need the new windowing functions and column store indexes)
Database size of 10-100 GB
No business intelligence features needed. Only the relational engine is required
Synchronous Database Mirroring

Currently, the following reasons are known to me:

SQL Server 2012 Denali

Newest version available

SQL Server 2008 R2

Proven technology

I can't seem to find a lot of technical reasons to prefer one over the other. Basically, it comes down to choosing proven technology that is running successfully vs. the newest and greatest version available.

What are objective reasons to make the decision?


texts: MongoDB Primary Member of Replica Set High Memory Usage

texts:
I currently have a sharded cluster up and running, and I have noticed that the memory usage of the primary member of each replica set is very high, it is 8GB although I started each member with the following:

mongod --auth --bind_ip_all --shardsvr --replSet a --smallfiles --oplogSize 1024 --keyFile /file/somewhere/

I thought (possibly naively) that the oplogSize would limit the amount of memory used.

Any guidance in how to solve this or highlighting the error of my ways is much appreciated.


texts: SQL Server High Availability and Sharepoint - log file grows when no database activity

texts:
Just started as a dba with a company running SQL Server 2012 High Availability and SharePoint.

Short description: Database with no user activity still has growing log file.

Long description:

They have many, many SharePoint dbs where the logfile size dwarfs the datafile size.  They've just been throwing extra disk space at the issue.

They were in Full Recovery (mandatory for HA) but weren't taking Log backups.  Simply by taking a log backup and a DBCC Shrinkfile to a manageable size I've started to bring things under control.

However, on my trial database the logfile is growing rapidly again.  As far as I can ascertain, no user activity in the database has taken place (insert/update/deletes) since I did the log backup.

Running

select [Current LSN], Operation, Context, [Transaction ID]
    , SPID, [Begin Time], [Transaction Name]
from fn_dblog(null,null)

shows hundreds of rows of GhostCleanup Task (looking for deletes) followed by a Begin Transaction and a Commit Transaction:

Can anyone explain what's causing this?  I can obviously set up Maintenance Plans to do Log backups much more frequently to keep the size in check, but I'd like to understand why there are all these transactions occurring in a 'static' database.

I've read about setting trace flag 661 to disable ghost cleanup, but don't want to do a server-wide change at this proof of concept stage.

I'm not sure if this is a HA issue, SharePoint issue, neither or both!  I've read lots of posts about SharePoint causing runaway logs but no real explanation.

It's the primary AOAG server. There are two synchronous secondaries and an asynchronous DR secondary.

dbcc opentran(EXT_Metadata_DB)

reports:

  No active open transactions.

Log file is still growing despite no user activity. There were 5 Autogrowth events overnight.  No transactional replication on this db, though HA obviously needs to write the log to the secondary servers. No blocked processes visible.

select d.name, d.log_reuse_wait_desc
from sys.databases d
where d.database_id = 15

gives:

EXT_Metadata_DB    LOG_BACKUP

This seems to imply that all transactions are in the current VLF which can't be cleared. Puzzling thing is, I haven't done any log backups since I did the Shrinkfile. I'm using a tiny database for this investigation.  Data file is only 14MB and I shrank the log file down to 4MB (13% full).  A day or so later, data file remains at 14MB, but log file has now grown to 69MB and is 92% full.  The particular SQL Server instance I'm looking at has 675GB of log files.

select name, size
from sys.database_files
where type = 1

gives:

EXT_Metadata_DB_log 8872

before a checkpoint.  Ran checkpoint but size remains at 8872.  Server recovery interval is the default 0.

If I run:

SELECT context AS Context, [Transaction Name] as Description, COUNT(context) AS Count
from fn_dblog(null,null)
GROUP BY context, [Transaction Name]
order by count desc

I get 19 rows:

LCX_NULL            NULL                        12681
LCX_NULL            GhostCleanupTask            12326
LCX_BOOT_PAGE       NULL                        12185
LCX_CLUSTERED       NULL                        451
LCX_NULL            UpdateQPStats               81
LCX_FILE_HEADER     NULL                        35
LCX_NULL            SetFileSize                 33
LCX_PFS NULL                                    12
LCX_TEXT_MIX        NULL                        10
LCX_BOOT_PAGE_CKPT  NULL                        10
LCX_DIFF_MAP        NULL                        7
LCX_NULL            DeallocUnlinkedReorgPage    6

No user activity, but 37847 transaction entries in 24 hours. Is this to be expected? I've not modified any data.  It's a static database of metadata.


texts: How to monitor space usage on ASM diskgroups

texts:
Last night the recovery area on one of our Oracle Database Appliances went full. This was reported in one of the database alert logs, and we were able to clear out some space before the next log switch, at which point the production would have come to a halt.

It certainly would have been nice to have a little more warning, like when the disk group was 70% full.

What options do we have for monitoring disk usage inside ASM?


texts: Check for overlaps for several arrays in one query

texts:
I have such table

create table tbl (
    id serial primary key,
    arr text[]
);

With such data

INSERT INTO tbl(arr) VALUES ('{dog, mouse}'), ('{fish, cat, rat}');

If my input is a single array - {dog} - everything is simple:

select id from tbl where arr && '{dog}'

| Id |
|----|
| 1  |

But what I need is the ability to pass several arrays as an input. For example with input {cat}, {dog} I expect output to be:

| Id |
|----|
| 2  |
| 1  |

And even more how can I with input {cat}, {bird}, {dog} receive:

| Id   |
|------|
| 2    |
| null |
| 1    |

Looping through the input array and make a query for each its item is not an option, I want to fire as few requests as possible

Here's the corresponding sqlfiddle - http://sqlfiddle.com/#!15/5740d/2


texts: Automatic seek on an index on a calculated column

texts:
I have a table with a varchar field than I need to be unique, but it is too long for the nonclustered index that the uniqueness constraint requires, so I have added a computed column (either using LEFT or HASHBYTES) and based the uniqueness constraint on that.

CREATE TABLE [dbo].[TableWithLongField] (
    [Customer] [char](10) NOT NULL,
    [PrimaryKey] [varchar](88) NOT NULL,
    [LongField] [varchar](4096) NOT NULL,
    [LongFieldLeft] AS CONVERT(varchar(900), LEFT([LongField], 900)),
    CONSTRAINT [UQ_LongFieldLeft] UNIQUE ([LongFieldLeft]),
    CONSTRAINT [PK_PrimaryKey] PRIMARY KEY NONCLUSTERED ([PrimaryKey]),
    INDEX [IX_Customer] CLUSTERED ([Customer])
);

When I perform a query like

DELETE
FROM dbo.TableWithLongField
WHERE
    PrimaryKey <> @PrimaryKey AND
    LongField = @LongField

I had hoped that SQL Server would be intelligent enough to realise it has an index based on the field available and automatically use it. However, that doesn't seem to be the case, when I look at the execution plan or at the user_seeks on the index. I wondered if it was just calculating that the function would cost more than the whole-table scan it is currently performing, but I've added a few tens of thousands of rows and it hasn't made any difference.

It does use the index if I manually perform the same calculation in the query

DELETE
FROM dbo.TableWithLongField
WHERE
    PrimaryKey <> @PrimaryKey AND
    LongFieldLeft = LEFT(@LongField, 900)
    LongField = @LongField

but having to define the function in two places already seems bad, and will only get worse as I write more queries.

Am I expecting too much from SQL Server and my only option is to do this manually? Or is there something I can do to hint that the index is available for querying on the long field, without adding it explicitly to every query?


texts: PG_bulkload utility postgresql need help

texts:
i am using COPY command to export the data to the out file with changes made to the data before export using option ST_asBinary() using command

COPY (
    SELECT st_asBinary(geom) FROM n_america
) TO '/install/shp_data/n_america_asbin_1_col.txt'
WITH DELIMITER ',';

now i am facing problem with Restoring back to the other table with geometry column , which is now converted to binary while exporting

i have tried with COPY FROM as well as PG_BULKLOAD utility , but i did't got any solution to do this ,

any one please help

Regards
Deepak M


texts: SQL Performance Monitor vs DMV Dynamic Management Views

texts:
We are trying to measure SQL Server Performance metrics. Is it better to record Performance Monitor (PerfMon) or DMV Dynamic Management Views to measure CPU, Memory, Buffer pages, etc?
There are certain metrics, that PerfMon can measure which DMV cannot, and other way around DMV can measure certain things Perfmon can't. I am referring to common shared metrics.

In general, does Microsoft have best practice, to use Perfmon or DMV in SQL Server?
I assume answer is Perfmon with remote computer,  since measuring things with DMV takes lag/pressure on the central server. Perfmon can be utilized from remote server, and reduce load.

Thank you,


texts:
How can I compare value from previous records?

Table:

ID    SAMPLEDATE    LEVEL    PASSED
1     1/1/2016      0        1
2     1/2/2016      1        1
3     1/3/2016      2        0
4     1/4/2016      1        1
5     1/5/2016      2        0

Is the last record (ordered by SAMPLEDATE) the same LEVEL as the last record that failed?  If yes, return TRUE otherwise return FALSE.

So in my example data the last record that failed with LEVEL 2. Then the last record (before the 1/5/2016 record) that failed on 1/3/2016 was the same LEVEL, so TRUE would be returned.  If it was a different LEVEL FALSE would be returned.


texts:
I have 3 nodes PXC 5.6 cluster. Xtrabackup 2.3.10. Centos 7.6

Percona-XtraDB-Cluster-56-5.6.41-28.28.1.el7.x86_64

I am using only single Node-1 for both read and write.

And I use to take backup from another Node-2 which is not used for read or write.

Yesterday, my Node-1 went down and read/write switched to Node-2. Then we continued to take incremental backup from another unused Node-3.

On my backup machine, I decided to extract and prepare backup. My backup preparation log shows everything fine until those backups that were taken from Node-2. All the incremental backups from Node-3 had errors.

Why does it seems like incremental backup should always be from the same node where the full backup was taken? Percona XtraDB Cluster is master-master and so all nodes must be same. Then why did this happen?

I have attached my logs here:

https://www.percona.com/forums/questions-discussions/percona-xtradb-cluster/53285-xtrabackup-corrupts-incremental-backup-when-node-changes


texts: Sql Server Index Data File Drive Location

texts:
As part of my ongoing saga of migrating from one SAN to another, the new SAN vendor said that I need to have my index data file in the same drive as my primary data file. Otherwise, given the tools that come with the SAN, I would need to create a new LUN just for this database and create it as its own volume.

At any rate, is this an acceptable practice ... to put index filegroups (.ndf) on the same drive as the data file?

Thanks


texts: ORACLE file I/O withouth explicit flushing on 10g and 11g

texts:
what happens, if I open a file (foo := utl_file.fopen(... ), write multiple times to a file ( utl_file.put_line (foo,bar) ) and close it ( utl_file.fclose(foo) ) without any flushing?

Is there a difference between the versions 10g and 11g?
Is the output deterministic?

Thank you

Edit 1: I am asking because we got some missing characters in a huge output file at some points where the 7FFF (= 32767) byte is full and the 32768th byte would be written. This error exists only since the update from 10g to 11g. The error in the code might be a missing flush, but we don't get any exceptions and it was working before the update. The issue is not with every 32768th byte.


texts: Why is my PostgreSQL expression index not being used when I ORDER BY in a subquery?

texts:
I have a query that gets about 80k rows from said table with about 3m.  I only need to paginate ten rows at a time, but with ORDER BY (a+b) DESC (a and b are integer type columns.)

Right now my query's taking about 4 seconds to do this.  If I ORDER BY the primary key instead, it avoids seq scans and takes 200ms, which I would be happy with.

So I created an index on (a+b) DESC.

My thinking there is that the ORDER BY and primary key both have indexes (as do all the things in the WHERE clause--and those indexes are being used just fine), so the subquery should be able to accomplish everything it needs to without a seq scan, give up 10 indexed primary keys to the outer query, which, if it needs a seq scan because of the *, only needs to find ten rows.

It didn't make a difference, anyway.

Unfortunately, I can't share full table details, but I can say that my table has 21 columns, with some text, two integer, several timestamps and even one ts_vector. Aside from the expression one there are 4 indexes: the PK (text) is btree, I have another btree text, a btree timestamp, and a gin index on the tsvector.

Further: I can also order by the indexed timestamp and the non-primary-key indexed text, and just like the primary key, this reduces query time to 200ms thanks to index usage.  It's just my expression index that doesn't seem to work this way in the subquery context.

Other options, none of which seem great:

Add a computed column for (a+b) so the index is just on one column.  If it works for ORDER BY on the pk it should work for that column, right?
Cheat by somehow guesstimating a happy limiter on (a+b) due to its distribution.  I'm not sure how I would always know because the query inputs are dynamic, but in this case, if I add a simple AND (a+b) > 1000000, I still get ten rows back but it only takes 200ms--and uses my expression index for the WHERE clause where the seq scan would normally be.  (About the not knowing, I could start doing some kind of binary-search-esque thing, trying high numbers and reducing by half until I get at least 10 rows back and then calling it a day.  If I get it within less than 20 tries, I'm still ahead.  But this is beyond kludgy...)

Any ideas?  How can I get it to use my expression index and avoid a seq scan?

The reason I don't understand the above is that the docs (going back to 9.4 at least) explicitly put it this way:

  Index expressions are relatively expensive to maintain, because the derived expression(s) must be computed for each row upon insertion and whenever it is updated. However, the index expressions are not recomputed during an indexed search, since they are already stored in the index.

So how on earth is other_column any less expensive to ORDER BY than a+b when both are indexed?

Regarding query plans, let's try to get to the heart of it.  I've posted a follow-up question.


texts:
One of my developers is arguing that COALESCE(column, default value) = default value is now sargable.  Is that right?

I ran the following test, and think it implies that COALESCE is non-sargable.

USE tempdb;

SELECT @@VERSION;
-- Microsoft SQL Server 2016 (RTM-CU3-GDR) (KB3194717) - 13.0.2186.6 (X64)   Oct 31 2016 18:27:32   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 14393: ) (Hypervisor)

CREATE TABLE Test
(
    ID int primary key clustered,
    Mod6 int null,
    INDEX IX_Mod6 NONCLUSTERED (Mod6)
);

INSERT INTO Test (ID, Mod6)
SELECT object_id as ID, case when name like '%k%' then null else object_id % 6 end as Mod6
FROM sys.objects;

SELECT Mod6
FROM Test WITH (INDEX = IX_Mod6, FORCESEEK)
where Mod6 is null or Mod6 = 0;
-- Plan shows expected seek

SELECT Mod6
FROM Test WITH (INDEX = IX_Mod6, FORCESEEK)
WHERE COALESCE(Mod6, 0) = 0;
-- Error:
-- Msg 8622, Level 16, State 1, Line 20
-- Query processor could not produce a query plan because of the hints
-- defined in this query. Resubmit the query without specifying any hints
-- and without using SET FORCEPLAN.


texts:
Hi i need to have three databases each one on one sql server that are on connected by network. The main concern is we need to have horizontal partition for a table by the id,

How do we do that in sql server?

I alreary search for tutorials but only find horizontal partition with file group and thats not posible on Distributed Database


texts: Need to encrypt certain columns in a table but also be able to sort and search

texts:
We've got a web app that needs to store sensitive data entered by the user. Currently we're exploring PostgreSQL in AWS. I'm aware of pgcryto and that we can hash contents of certain columns (as not everything needs to be encrypted). However, we need to also be able to search through these columns and perform sorting. These two seem to be limitations once we encrypt the data.

What are my choices at the moment if we must also support sorting and searching? Keeping in mind that the solution must also be performant.


texts:
I found that SQL query for MS SQL server 2008 to display backups.

SELECT      D.name
           ,ISNULL(CONVERT(VARCHAR,backup_start_date),'No backups') AS last_backup_time
           ,D.recovery_model_desc
           ,state_desc,
            CASE WHEN type ='D' THEN 'Full database'
            WHEN type ='I' THEN 'Differential database'
            WHEN type ='L' THEN 'Log'
            WHEN type ='F' THEN 'File or filegroup'
            WHEN type ='G' THEN 'Differential file'
            WHEN type ='P' THEN 'Partial'
            WHEN type ='Q' THEN 'Differential partial'
            ELSE 'Unknown' END AS backup_type
           ,physical_device_name
FROM        sys.databases D
LEFT JOIN  (
    SELECT  database_name,backup_start_date,type,physical_device_name
           ,Row_Number() OVER(PARTITION BY database_name,BS.type
            ORDER BY backup_start_date DESC) AS RowNum
    FROM    msdb..backupset BS
    JOIN    msdb.dbo.backupmediafamily BMF
      ON    BS.media_set_id=BMF.media_set_id
) AS CTE
   ON       D.name = CTE.database_name
  AND       RowNum = 1
ORDER BY    D.name,type;

Is there any other useful information that I can display? Can you propose some additional information that I can query?


texts: AdventureWorks2012: "The FILE SIZE property is incorrect" error while trying to attach sample database "Adventure Works for SQL Server 2012"

texts:
I am trying to attach the sample databases, for example, AdventureWorks2012_Data.mdf from codeplex "Adventure Works for SQL Server 2012" into a MS SQL Server 2012 (default) instances on a a virtual machines under MS Windows Server 2008 and MS Windows 7.

Each time I am getting the error:

  The header for file 'C:\Program Files\Microsoft SQL
  Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Data.mdf' is
  not a valid database file header. The FILE SIZE property is incorrect.
  (.Net SqlClient Data Provider)

  ------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.3128&EvtSrc=MSSQLServer&EvtID=5172&LinkId=20476

What can be done to attach the MS SQL Server 2012 sample databases?

Tried to install these sample databases ак the script(s), also always getting the error, the different one, about which I shall ask in a separate question.


texts: ETL - Moving data from Postgres/Oracle to a TripleStore (RDF)

texts:
I'm looking for pointers on how to handle an ETL process that I have to implement for my organization. I know how to query SQL database but i'm not SQL DB specialist. I work more in the sphere of the semantic web and therefore I am more knowledgeable of TripleStore.

I need to move data from a Postgres and an Oracle database to a triple-store. TripleStore use RDF format, so i will need to perform some transformation in the middle. I need to keep the triple-store in synchronization with the source sql database. That is, I need to continuously update the triplestore from what is happening in the sql stores.

it is one way from the sql store to the triples store

Doing my own research, i have red about change data capture, replication, binary replication and etc...

Please could you help me make sense of all of this and guide on where to look to solve this kind of problems?


texts: Error in alter table reorganize partition for MySQL Cluster

texts:
My cluster contains 48 million rows and cluster contains 4 storage nodes . After that I add online storage node and executed alter table 'customer_tbl' reorganize partition.
Some parameters of config.ini are :

NoOfFragmentLogFiles=100
FragmentLogFileSize=16M

When I checked redo information by command all dump 2398; I saw this on my new storage node:

REDO part: 0 HEAD: file: 99 mbyte: 11 TAIL: file: 0 mbyte: 0 total: 1600 free: 5 (mb)
REDO part: 1 HEAD: file: 0 mbyte: 0 TAIL: file: 0 mbyte: 0 total: 1600 free: 1600 (mb)
REDO part: 2 HEAD: file: 0 mbyte: 0 TAIL: file: 0 mbyte: 0 total: 1600 free: 1600 (mb)
REDO part: 3 HEAD: file: 0 mbyte: 0 TAIL: file: 0 mbyte: 0 total: 1600 free: 1600 (mb)

While my previous existing data nodes have following redo information:

REDO part: 0 HEAD: file: 41 mbyte: 3 TAIL: file: 38 mbyte: 10 total: 1600 free: 1559 (mb)
REDO part: 1 HEAD: file: 0 mbyte: 0 TAIL: file: 0 mbyte: 0 total: 1600 free: 1600 (mb)
REDO part: 2 HEAD: file: 0 mbyte: 0 TAIL: file: 0 mbyte: 0 total: 1600 free: 1600 (mb)
REDO part: 3 HEAD: file: 0 mbyte: 0 TAIL: file: 0 mbyte: 0 total: 1600 free: 1600 (mb)

Please tell me why free storage is so less in new data node's redo log?
And after some time it gives

  ERROR 1297 (HY000): Got temporary error 410 'REDO log files overloaded (decrease TimeBetweenLocalCheckpoints or increase NoOfFragmentLogFiles)' from NDBCLUSTER

Why doesn't the server use the rest of the three free redo log files which have 1600MB free space?
Please explain what is going on while reorganize partition?


texts:
I'm trying to implement SSL certificate on a SQL Server and I already tried two methods. I tried with lets encrypt service and self certificate generated on Powershell.

In the first scenario I can see the cert into SQL Server Configuration Manager but then I got an error while restarting the service. I've got the error 2146885628. First I generated the certificate and followed this tutorial

On the second scenario the cert is not shown on SQL Server Configuration Manager.

My questions are: Am I making something wrong and what is the best Powershell code to generate a certificate compatible with SQL Server 2016 ?


texts: Uninitialized Merge replication subscription

texts:
I have 3 separate SQL Server 2012 databases running on 3 VM's. I have successfully configured one pull subscription to the distributor but when I add a 2nd subscription on another SQL Server the subscription never initializes. The new subscription is configured exactly the same as the working subscription.

Both subscriptions are set to impersonate the service account.
Is there a way to see exactly what the failure is?


texts: Operand type clash: my first User Defined Type with list of Integers, how to test via SSMS

texts:
I found this wonderful code here

    CREATE TYPE [dbo].[StringList] AS TABLE(
    [Item] [NVARCHAR](MAX) NULL
);

    GO
    CREATE PROCEDURE [dbo].[sp_UseStringList]
        @list StringList READONLY
    AS
    BEGIN
        -- Just return the items we passed in
        SELECT l.Item FROM @list l;
    END

    GO

but I dont know how to test it via SSMS please

declare @list   StringList  = '1,2,3,4,5'
exec sp_UseStringList @list

gives the error:

Operand type clash: varchar is incompatible with StringList

How can I test the list '1,2,3,4,5' please


texts:
We have PostgreSQL 9.6 databases and a dailytask running a pg_dump for all databases, though these backups are getting "large" at this point.

My database was 900MB, then I tried clearing it by deleting old history which is not necessary anymore and after that ran VACUUM FULL. The statistics in pgAdmin say that the database is now only 30MB.

When I run the pg_dump command manually through a Command prompt, it creates a file of 22MB. When I run my daily task (windows task scheduler) it still creates a backup file of 1GB.

What am I missing at this point?


texts: Modeling Conversion Events in a Data Warehouse

texts:
How should I model conversion events?

For instance, if a sales opportunity goes from "stage: prospecting" to "stage: validating", should that be treated as a changing dimension, or a conversion event in the fact table?

Max


texts: MySql UPDATE of tiny table is costly (0.2sec) - how to analyse further

texts:
While optimizing some indexes I detected a curious case in the slow_query_log.
These are not the actual tablenames but for simplicity:
# Thread_id: 1293932  Schema: xx  QC_hit: No
# Query_time: 0.205087  Lock_time: 0.000046  Rows_sent: 0  Rows_examined: 1
# Rows_affected: 1  Bytes_sent: 52
UPDATE language SET lastUpdate = current_timestamp() WHERE languageID = '12';

Table 'language' has exactly 12 rows. It has only a few columns. It has only 1 index: the primary key on languageID (INT). This is thus the simplest table you can imagine. Why is this update statement taking 0.2 seconds?
For reference, select statements on rows of 10k-100k's of rows are quite fast:
# Thread_id: 1294423  Schema: xx  QC_hit: No
# Query_time: 0.111802  Lock_time: 0.000266  Rows_sent: 0  Rows_examined: 14279
# Rows_affected: 0  Bytes_sent: 50
INSERT INTO session (sessionID, personID, lastDate)
    SELECT 8, a.personID, current_timestamp() FROM personA a INNER JOIN personP p on p.personID = a.personID LEFT OUTER JOIN view_bigViewB ON view_bigViewB.personID = a.personID WHERE view_bigViewB.someValue = 'x' ;

Above query is simplified a lot, and please do not pay too much atention to it, but let's say this is really a dramatic big data set that is being processed in only 0.1sec with full table scans. It is an INSERT that did not insert any rows, so no write operation was needed.
Of course, a write operation is more costly but 0.2s seems just too slow. How can I analyze what exactly is making up this .2 seconds?
Using MariadDb 10.3.27
SHOW CREATE TABLE

CREATE TABLE `language`
(
   `languageID` int(11) NOT NULL AUTO_INCREMENT,
   `name` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL,
   `lastUpdate` datetime DEFAULT NULL,
    PRIMARY KEY (`languageID`)
)
ENGINE=InnoDB
AUTO_INCREMENT=16
DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Output of innodb-parameters: https://pastebin.com/ReGHc1xp


texts: Change Oracle user home directory at OS level after Oracle Databse/Client Software Installation

texts:
Can we change oracle user's home directory at OS level after Oracle Database/Client Software-Only installation?

No database has been created yet.
We want to change the home directory to /Oracle/profile/oracle from /home/oracle.


texts: Percona Xtrabackup fails to backup InnoDB table with fulltext index

texts:
I'm trying to make a backup of a large InnoDB table with a fulltext index using Percona Xtrabackup (2.4.12).

I have innodb_file_per_table set.

To create the backup, I'm running:

innobackupex --tables-file=tables.txt --no-timestamp /path/to/backup

Then I create the table exports:

innobackupex --tables-file=tables.txt --apply-log --export /path/to/backup

The --export run produces an error message for each of the fulltext index files ("FTS...") in my database directory:

InnoDB: Failed to find tablespace for table `webdb`.`FTS_00000000000005ce_0000000000000630_INDEX_1` in the cache...

Two questions:

Do I have to do the --export run right after I create the backup ? Or can I delay that until I actually need to restore a backup ?

What can I do about the error for the FTS files ? Is that normal ? Will Mysql re-create the fulltext index automatically or is it even included in the main tables .idb file ?

Update:

It would also help if someone could report that backing up tables with fulltext search using Xtravackup did or did not work with Mysql 5.7 for him. Then at least I would know if its a bug in the tool.


texts: transaction replication causing too many deadlocks after adding a indexed view - what to monitor in order to avoid this?

texts:
I have created the following indexed view and its clustered index:

This view was created on a subscriber database and all the tables used in the view are constantly being written to through the replication agent jobs (transaction replication).

USE Bocss2
GO

SP_HELP 'dbo.tblBOrder'
GO
sp_help 'dbo.tblBOrderPayment'
go

CREATE VIEW DBO.vwOrders
WITH SCHEMABINDING
AS

/*-----------------------------------------------------------------------*\
   view for boccs2 reports
   29-sep-2015
\*-----------------------------------------------------------------------*/
SELECT

o.strBxOrderNo,
op.lngPaymentID,
sintMarketId,
sdtmOrdCreated,
o.strCurrencyCode,
sintOrderStatusID,
op.sintPaymentTypeID,
optd.strPaymentTypeDescr

FROM dbo.tblBOrder o

INNER JOIN dbo.tblBOrderPayment op
        ON op.strBxOrderNo = o.strBxOrderNo

INNER JOIN dbo.tblBOrderPaymentTypeDescr optd
        ON op.sintPaymentTypeID = optd.sintPaymentTypeID
        AND optd.sintLanguageID = 1

WHERE 1=1
AND ( NOT o.sintOrderStatusId IN ( 9, 10, 11, 12, 13, 14 ))
AND OP.decPaymentAmount > 0 --Exclude the zero payment record...(sometimes created by the system to track the CC details)

GO

--============================
-- THE ROLLBACK
--============================
--DROP VIEW DBO.vwOrders
GO

--===========================================================
-- the index creation
--http://dba.stackexchange.com/questions/116577/what-would-be-a-good-choice-of-clustered-index-and-other-indexes-for-this-situ
--===========================================================

set deadlock_priority high
CREATE UNIQUE CLUSTERED INDEX PK_VWoRDERS_
ON DBO.vwOrders (sintMarketId,sdtmOrdCreated,strBxOrderNo,lngPaymentID)
WITH(DROP_EXISTING=OFF)
GO
update statistics DBO.vwOrders
go
update statistics dbo.tblBOrderPayment
go
update statistics dbo.tblBOrder
go

We had great results on our queries that were using this indexed view,
however,

I had to drop it because of too many deadlocks caused by the replication.

replication was deadlocking with itself when inserting into
table dbo.tblBOrderPayment  or table dbo.tblBOrder

how could I have avoid this rollback?

whilst I cannot re-create the view and put it in place in a busy production system, I have add below information from the log that shows me 2 replication procedures deadlocking each other.

OrderPayment and Order.

Looking at it today(day 1 after the rollback) , it seems so clear that this indexed view - with an inner join using these 2 big tables - is not a good idea.

Unless I had changed also the way data is written into these tables, alongside the replication.


texts: Partitioning in MySql 5.7 with Primary Key and Unique Key

texts:
I am trying to create a hash partition in MySQL 5.7 AWS RDS, cause the table is growing much rapidly approximately with 200 Million Rows in a month.
The table structure is like this and tried to create the partitions like below
  itemid bigint(20) NOT NULL AUTO_INCREMENT,
  ownerid int(11) NOT NULL,
  product_id int(11) NOT NULL,
  creation_time datetime DEFAULT NULL,
  used_time datetime DEFAULT NULL,
  ......
  ......
  ......
  ......
  PRIMARY KEY (itemid),
  UNIQUE KEY owner_product_uidx (ownerid,product_id),
  KEY prod_indx (product_id)
)
PARTITION BY HASH(itemid,ownerid,product_id)
 PARTITIONS 5;
ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

throws error
Error Code: 1503. A PRIMARY KEY must include all columns in the table's partitioning function.
Tried Partition KEY instead of HASH with
PARTITION BY key(item_id,owner_id,product_id,creation_time;

Still with no luck. Did anyone succeed creating the partition,
Would also be fine i can do it with year(creation_time)-month(creation_time) as partition key.
like (2021-01,2021-02....etc)
Ref: https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations-partitioning-keys-unique-keys.html
Can someone help.


texts:
I have two tables

1st Table : center (2 columns)

+-----------------------------------+
|id | company_name                  |
+-----------------------------------+
|2  | Skytel Outsourcing Pvt. Ltd.  |
+-----------------------------------+

2nd Table : campaign_center (3 columns)

+-------------------------------------+
|campaign_id |  Center_id  | sl_no    |
+-------------------------------------+
|Demo        |    2        | 2        |
+-------------------------------------+
|MyFootPath  |    2        | 3        |
+-------------------------------------+

When I execute this query it's working perfectly: (To find center_id and company name who's campaignid is 'Demo')

select center.id, center.company_name
from center
  inner join campaign_center
  on center.id = campaign_center.center_id
where campaign_center.campaign_id = 'Demo'

but again when i am trying to find those company who's campaign_id is not 'Demo' it doesn't give correct output:

select center.id, center.company_name
from center
  inner join campaign_center
  on center.id = campaign_center.center_id
where campaign_center.campaign_id != 'Demo'

How do I get those ? Please help.


texts: what is difference between database developer and administrator?

texts: i am a doing graduate degree, and i want to go in database field as i find this subject very interesting. For becoming the DBA ,is there a need of becoming developer first for graduate passout and career beginners.


texts: What is the best SQL server backup strategy for large DB with few tables and lots of immutable data?

texts:
We have a large DB (500GB and expanding). 95%+ of the data is stored in 3 tables (one table has 2bn+ rows). The data is largely immutable - i.e. once it's added it's only then read afterwards. We can't archive old data.

We're using SQL Server 2017 which allows for compressed backups but even then it takes a long time to backup and copy across the wire to backup server.

We'd like to speed up this process (and for DR purposes have the backups in the cloud - probably Azure) - Differential backups are small enough, but I think that we'd still need to regularly take full backups (e.g. full each weekend and differential each night is still a problem for us)

My feeling is to use partitioning to split the large tables on some sensible column (clustered on identity ID) then we can back up the older partitions just once and mark them as read-only and not need to back it all again.

This is not an ideal situation as we're going to need to keep adding more partitions in the future. Also the DB cannot be taken down for more than a few minutes so I think I'm going to have to make a shadow version of the data with the partitions then do some switch-over so reduce downtime which is a bit risky and complicated.

If anyone has backup strategies for this sort of DB configuration that they think would work better (or can confirm that this seems like a good idea given the constraints that I have) I'd be glad to hear :)

Additional information:

Current backup schedule:

Full backup (nightly) - compressed backups are about 100 GB (500 GB uncompressed) and take about 40 minutes (with compression)
Log backups (Every 10 minutes) - Almost instant and only about 20 MB each.

Now I know that, for some, 40 minutes is not a very long and time and 100 GB is not a very large file, but I also know that given that 95%+ of the data is immutable and could be safely backed up only once, the backups could take less than a couple of minutes and be a couple of gigabytes (and that's being conservative).

I believe that partitioning is one of the tools used to help manage backups and specifically for this type of scenario, and I am hoping to get someone with real-world experience with this (or SQL Server based alternatives for my scenario) to shed some light on what works for them.


texts: Query plan missing ParameterCompiledValue

texts:
Recently I've started to analyze execution plans. Today I stumbled on an execution plan that has no ParameterCompiledValue (and no ParameterList tag).

It is a plan of a select with one inner join and seven left outer joins.

Is there a reason why some execution plans have a ParameterCompiledValue and others don't?

Edit:

Is it correct to stay the ParameterList is empty due to the fact that sql server could not sniff the parameters? It is not an ad-hoc query and is exectued by a third party c# application.
The query and queryplan can be found on Google drive


texts:
I know that MongoDB has master-slave architecture and Cassandra has peer to peer architecture.

I didn't find any resources about Vertica's architecture.

Is that master slave or peer to peer?
Does Vertica cluster allow multi-read and multi-write?


texts:
I have a table of data that contains 10 point values based on a score for an event (0 - 9 points). If a time is within the range, the point is awarded. There are 20 possible events. Is this a valid use of a multi-value field?

example:

Event 1A times 18.50, 19.11, 19.77, 20.03
Event 2C times 58.50, 60.91, 62.55, 63.99

If the event is 1A and the time is 19.66, this fall into the 2nd and 3rd position - the score is then 2

If the event is 2C and the time is 63.77, this fall into the 3rd and 4th position - the score is then 3

Regards

More Detail:

Key|Event|Type|PointTable (seconds)
1|200E|R|93.86, 94.06, 94.25, 94.45, 94.65, 94.85, 95.05, 95.25, 95.45, 95.65
2|200A|I|98.26, 98.44, 98.63, 98.81, 98.99, 99.18, 99.37, 99.55, 99.74, 99.94
3|200E|I|110.74, 110.94, 111.15, 111.35, 111.56, 111.76, 111.97, 112.18, 112.38, 112.59

Key = unique key, Event is the EventCode, Type is not important, PointTable is the list of seconds....

I have another table that contains RESULTS which includes an EventCode and a TIME. To produce a query, I want a score identified for that TIME. So I use the EventCode to find the corresponding list of times... and where the TIME fits in the array of times produces a 'SCORE'

The time for 200E (1 key) would be in a record for a results of an athletic activity and there needs to be a determination what that time's relative score would be 0=93.86 or less, 1=94.06 or less, 2=94.25 or less... etc. So taking a time from an event 200E with a time of 94.01 would fall into "1=94.06 or less" and therefore the score given to the event with a time of 94.01 would be given "1" ... each event has 10 'scores' and the point value represents the position in the list of scores. Does this help?

-lookup is used to determine the position in the list of values to determine the score...


texts: `select OBJECT_ID(N'X')` takes "forever"

texts:
I have encountered strange state of a SQL Server. In one particular database in this SQL Server select OBJECT_ID(N'X') takes "forever". How to diagnose what is going on?

EDIT: Problem disappeared. However, I am still curious what was going on.


texts: Selecting data from multiple rows and insert it into a column

texts:
My table is like this:

temp1
name   city     phone_no    pincode
-----------------------------------
amit   Delhi     12345       123
Rajiv  Bombay   836536       432

How do I transform the last three columns into a single column, address, like in the following?

name     address
----------------
amit      Delhi
amit      12345
amit        123
rajiv    Bombay
rajiv    836536
rajiv       432


texts: Select the newest data (inner join + group by maybe)

texts:
I think I would need some help.
To simplify my problem, lets assume that I have two tables. First called "topics" with this structure:

idTopic  author  deleted
1        123     false
2        132     false

and second callledd  "texts" which looks like this:

idTopic idText datePosted
1       11     1
1       12     2
2       15     1
2       18     3

and what I need is to select the data for every topic with the data of newest text of this topic. So my output should be like this:

idTopic author deleted idText datePosted
1       123    false   12     2
2       132    false   18     3

so how should my sql query looks? I was trying to do some inner join with second table grouped by idTopic, but it didn't work.

Thanks


texts:
I have the following query:

SELECT 1,
       row_number() over (partition by lower(X), lower(Y) order by X DESC) AS rn
FROM dbo.MyTable
WHERE rn = 1

When I run it, I get a error Invalid column name 'rn'..

I don't understand why I'm getting this as rn is defined in the SELECT portion.

I've tried replacing the WHERE with a HAVING too.


texts: Optimizing a simple query on a large table

texts:
DB: MySQL 5.5.20 (WampServer, default configuration)
OS: Win 7
HDD: Western Digital 3TB Caviar Green, 3.5", IntelliPower, 64MB, Sata3 (WD30EZRX)
Memory: 8GB
MySQL my.ini: http://pastie.org/private/go9kaxlmlvirati2txbaa

Query in question:

  SELECT name.id AS name_id, name.name, cast_info.id,
  cast_info.role_id, cast_info.movie_id
  FROM cast_info
  LEFT JOIN name ON name.id = cast_info.person_id
  WHERE cast_info.movie_id = 1000000
  ORDER BY cast_info.movie_id ASC

It fetches all people who worked on a given movie. Problem is it can take anywhere from 0.1s to nearly 2.0s. That's too long. When the user needs to run it 10k times they might as well uninstall the application. Even I haven't had the patience to wait for it to finish.

edit: The time it takes to run the query is determined by the number of people who worked on it. Roughly 0.1s for each 10 people.

EXPLAIN:

  *************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: cast_info
           type: ref
  possible_keys: idx_mid,mpi
            key: idx_mid
        key_len: 4
            ref: const
           rows: 15
          Extra:
  *************************** 2. row ***************************
             id: 1
    select_type: SIMPLE
          table: name
           type: eq_ref
  possible_keys: PRIMARY,id_name_idx
            key: PRIMARY
        key_len: 4
            ref: imdb.cast_info.person_id
           rows: 1
          Extra:

Tables:

  CREATE TABLE cast_info (
   id int(11) NOT NULL AUTO_INCREMENT,
   person_id int(11) NOT NULL,
   movie_id int(11) NOT NULL,
   person_role_id int(11) DEFAULT NULL,
   note text,
   nr_order int(11) DEFAULT NULL,
   role_id int(11) NOT NULL,
   PRIMARY KEY (id),
   KEY idx_pid (person_id),
   KEY idx_mid (movie_id),
   KEY idx_cid (person_role_id),
   KEY cast_info_role_id_exists (role_id),
   KEY mpi (movie_id,person_id,id)
  ) ENGINE=MyISAM AUTO_INCREMENT=33261692 DEFAULT CHARSET=utf8

  CREATE TABLE name (
   id int(11) NOT NULL AUTO_INCREMENT,
   name varchar(110) NOT NULL,
   imdb_index varchar(12) DEFAULT NULL,
   imdb_id int(11) DEFAULT NULL,
   gender varchar(1) DEFAULT NULL,
   name_pcode_cf varchar(5) DEFAULT NULL,
   name_pcode_nf varchar(5) DEFAULT NULL,
   surname_pcode varchar(5) DEFAULT NULL,
   md5sum varchar(32) DEFAULT NULL,
   PRIMARY KEY (id),
   KEY idx_name (name(6)),
   KEY idx_imdb_id (imdb_id),
   KEY idx_pcodecf (name_pcode_cf),
   KEY idx_pcodenf (name_pcode_nf),
   KEY idx_pcode (surname_pcode),
   KEY idx_md5 (md5sum),
   KEY id_name_idx (id,name)
  ) ENGINE=MyISAM AUTO_INCREMENT=4287972 DEFAULT CHARSET=utf8

Thanks!

edit: MyISAM is used because this is a local database, used by one local application, by one user. Only one query is executed at the same time. Also because IMDbPy takes at least a month to build the database with InnoDB...

edit: Query EXPLAIN after converting to InnoDB:

  *************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: cast_info
           type: ref
  possible_keys: mpi
            key: mpi
        key_len: 3
            ref: const
           rows: 23
          Extra:
  *************************** 2. row ***************************
             id: 1
    select_type: SIMPLE
          table: name
           type: eq_ref
  possible_keys: PRIMARY,id_name_idx
            key: PRIMARY
        key_len: 4
            ref: imdb.cast_info.person_id
           rows: 1
          Extra:


texts: get stuck trying to copy or backup database in microsoft SQL server

texts:
I have a website running on a windows hosting package with my provider.
I bought a new windows hosting package, and now i want to copy my 'older' site into the new pack.

SO I can acces the old db and the new db (which is still totaly empty, no tables, ..) through sql server. So they both connect to an online server, it's not local.

Now I want to copy old db to new.
First option is to use: tasks > copy database
In this wizard i enter my source db data, next, i enter my destination db data, next, now i get the message 'copy database wizard - object reference not set to an instance of an object'.
So here i get stuck..

Alternative option I think is to export old db, and later import into new.
So i go to: tasks > backup
I select my old db, full backup type, destination: disk
--> i'm not sure what 'disk' means. Does it mean create a backup file on my local disk on my laptop from where i do all of this?
--> The standard value that is there looks like an url to a server though, it starts with an ip adress:
\172.20.101.253\sql008\TRN\ID97655_..............tm
--> since i do not know how to access that place, i remove it and i add a new path, something like: D:\MSSQL\BACKUP\backuptest.Bak
--> that .bak file does not exist yet there, the path does exist
--> then i get the warning: 'cannot verify the existence of the backup file location. do you want to use the backup file location anyway?'
--> i choose 'yes', then the backup completes succesfully to 100%
--> there is no bak file to be found on that directory.
so now i'm stuck here to

for the disk location i entered, is it still searching that location on the server instead of localy?
when i google for this issue i find a lot of threads saying it's some rights issue. So I go to my D drive, richtclick , properties, security.
Here I find 4 standard users (verified users, SYSTEM, administrators, users), i make sure all of them have all access.
But maybe this is not enough? do i need to create a new user? how do i know which one?

Thanks for helping me further with this,
have a nice day,

sandy


texts: Appropriate situation to data warehouse and how to best manage the warehouse?

texts:
I have a query that is on the opening page of our application that takes about 8-10 seconds to load.  I've tried all I can to optimize it and I simply cannot make it go any faster (indexed appropriately, rewriting redundant code, ridding it of view-ception hell it lived in).  It's a query on our largest table with a bunch of joins to other tables that have a many to one relationship to the main (many in the smaller relate to one id in the largest) and requires an unavoidable group by largeTable.id that makes it so slow.

Now I do have to make this work faster one way or another.  I think this may be an appropriate situation to have a data warehouse (I hope that's the right term) type table.  I would insert into this warehouse table the results of the slow query and then query from the warehouse table directly.  Then, anytime something in my application that affects a table that affects the data from the slow query/data warehouse table, I would drop the rows in the table and recreate it which would then take slightly longer than the initial query, but only happen a few times a day, whereas the rest of the day I am querying directly from my data warehouse table much faster.

Is this a reasonable solution?  Are there any common pitfalls or things I need to watch out for with this methodology, or a better way to do it?  After a few days of trying to just optimize the query, I need some work around.  I am still relatively new to this and am open to suggestions.


texts: Not able to update innodb_ft_num_word_optimize

texts:
We are using a AWS-RDS(MySQL 5.7) instance with 16GB RAM. We have a table with millions of entries and a FULL-TEXT index which needs to be OPTIMIZED.

Apparently, FULL-TEXT optimization is very expensive and hence MySQL provides the variable innodb_ft_num_word_optimize to control the number of words that is optimized at one time. The default value of this variable is 2000.

For some unknown reason we are not able to update this value beyond 10000. Increasing beyond this results in the RDS instance being stuck in Incompatible-parameter state. In one of the other RDS instance, changing the value did not produce any error but the value of innodb_ft_num_word_optimize maxed out at 10000. MySQL documentation does not mention any max value for this variable.

Can someone explain this behavior ?


texts: Analysis service database security at application level

texts:
I have got a SSAS Cube, we have written an windows application which would issue MDX queries to access the data from the cube and show it in the reports .

This windows application is a licensed application, meaning only those who have got license from us could be able to run the application and then could be able to access the data from the cube.

The problem we are having is what some unlicensed users tries to access our cubes through other cube browser application like Excel.

so we are generally wondering how to protect the cubes and data present in the cube so that only who has got license to our application could be able to access.

Could anyone please advise that how to give this type of security to SSAS database?


texts: How not to have SQL Server to restart on the same node but failover on 1st failure in a cluster

texts:
By default SQL Server tries to restart on the same node in case of a failure, next it does failover.

Personally I am good with the default behavior , but I am being asked a interesting question - is it possible to have SQL Server NOT try a restart on the same node and just failover in case of a failure?

And where is it done from, is it this:

In cluadmin-SQL Server Properties-Policies-

There is a option

  Max restarts in the specified period - Will making it zero do the needful?

Or there is some other way? Thanks.


texts: SQL Server : Transactional Replication Filter By Computed Column

texts:
There is a database that contains records for multiple groups of people. These records do not contain a column indicating which group the record is "owned" by (very frustrating). I need to setup transactional replication to each different group, which would contain only the records that they own.

It would be possible to create a computed column which would contain the logic to derive who the owning group is (note that in some cases, this computed column would have to call a scalar function because the logic may have to join tables to determine the owner).

I am wondering if it is possible to setup a row filter on the computed column for use in transaction replication. Would it matter whether the computed column was persistent or not?

Note that using indexed views is of course an option; however, this would double the space requirements of the database.


texts:
I have a few questions, hoping I can get answers.

what does it mean when the Mongodb said:

  Clients can read documents while write operations are in progress

I can see partially updated document? or the document will be under lock and no one can see it until the lock yielded?
I need to know if two operation can update the same document with WiredTiger or MMAP V1? if so, what about reading at the same time of write or update?
If I use $isolate is this enough to be sure that no one could write on the same document?


texts: What is the most efficient way to exclude records with a particular ID in a MySQL query for random records?

texts:
I have an api endpoint that returns X amount of random posts from a table called "posts". This endpoint is used by a mobile app. It retreives random posts by doing a SELECT COUNT(*) on the posts table and returning the amount of posts in the table. It then enters a for loop in which, at the start of each loop, a random number from 0 to the COUNT(*) is generated. A random post is then obtained using the handy OFFSET. This for loop goes until X amount of random posts obtained.

pseudocode:

FUNCTION getRandPosts :

$numOfPosts = queryExecute("SELECT COUNT(*) from posts");

for (iterations < numOfRandomPostsNeeded) {
    $randomNumber = rand(0, $numOfPosts)

    $randomPost = queryExecute("SELECT * from posts LIMIT 1, OFFSET $randomNumber")

}

Now, for each call to a getRandPosts function I want them to always retrieve a unique post that wasn't retrieved before. For this current getRandPosts call AND FOR PASTS CALLS.

I've thought of several ways of going about doing this:

I could store the ID's of already seen posts in an array IN the app and then send this array to the getRandPosts function. The getRandPosts function then uses a "NOT IN" clause.

FUNCTION getRandPosts(ARRAY $idsOfPostsAlreadySeen) :
...
for ...

$randomPost = queryExecute("
    SELECT * from posts
    WHERE id NOT IN $idsOfPostsAlreadySeen
    LIMIT 1, OFFSET $randomNumber")

$idsOfPostsAlreadySeen.addToArray($randomPost.id)
...

After each random post is retrieved, save that post id to a table called "seenPosts" where there is also a column called idOfApp which is used to distinguish users.

FUNCTION getRandPosts(STRING appInstallID) :
...
for ...

$randomPost = queryExecute("
    SELECT posts.* FROM posts
    JOIN seenPosts.postID FROM seenPosts
    ON posts.id = seenPosts.postID
    WHERE seenPosts.postID = null AND seenPosts.appInstallID = $appInstallID
    LIMIT 1, OFFSET $randomNumber")

queryExecute("INSERT INTO seenPosts SET postID = $randomPost.id, appInstallID = $appInstallID")
...

I'd also like to ask about the idea of, for idea number one, creating a temporary table from the array and using JOIN with that temp table vs using the array.

I'm avoiding using RAND() because it has been said it doesn't work for large tables. I wonder how large is large?

Consider the idea that, at most, 600 posts will have to be excluded for any given user (so an array of 600 ids in the case of the first idea $idsOfPostsAlreadySeen... or 600 records in the seenPosts for a particular person).


texts: Preventing duplication on a Postgres table with 2 columns

texts:
I am currently implementing a simple database schema (in PostgreSQL 11.1 (Debian)), with two int columns (say col1, col2).

My goal is to prevent duplicates appearing in any of the two columns.  If (1,2) already existed, then (1,2) or (2,1) would cause a "duplicate key value violates unique constraint" error.

For a practical example.  I have created a table someTable, with col1, col2, with a unique constraint (col1, col2).  Everything is fine when I insert (1,2) a second time:

CREATE TABLE someTable (
 col1 int NOT NULL,
 col2 int NOT NULL,
 unique (col1, col2));

- CREATE TABLE

INSERT INTO someTable (col1, col2) VALUES (1,2);
- INSERT 0 1

INSERT INTO someTable (col1, col2) VALUES (1,2);
- ERROR:  duplicate key value violates unique constraint "sometable_col1_col2_key"
DETAIL:  Key (col1, col2)=(1, 2) already exists.

INSERT INTO someTable (col1, col2) VALUES (2,1);
- INSERT 0 1

I would like the (col1, col2) unique constraint to work for swapped values (for example 2,1 is treated the same as 1,2).


texts: SQL Server Express averages 2-3% CPU but gets stuck at 100% sometimes

texts:
Skip to the last paragraph for the summary-version
I am dealing with a SQL Server 2008 R2 Express installation. I have a fairly complex stored procedure. Depending upon the inputs, it may return 6500 records.
Each record is a web order. It returns 80 fields. Also, there is a bit of grouping to sum up line-item information and complex filtering on the SPROC itself. Almost always, this query runs in 2 seconds.
Sometimes, when the query runs with the minimal amount of filtering that will result in all 6500 rows returning, the database server enters an error state of some sort.
The CPU spikes to 100% and stays there. Sometimes it exits this state after a few minutes, sometimes it stays there until I reset the SQL Service.
If I reset the SQL Service during this error condition, then run the query again, normally I will be able to run it a hundred times without the error condition repeating. However, as the database ages (and expands) this error condition is becoming more and more common.
Here's what I know

I have analyzed the execution plan and likely have indexes on the appropriate fields in the appropriate tables

These indexes are rebuilt nightly

The error state lasts longer than it used to, on average.

Rebuilding the indexes seems to help when this error condition has just occurred (before resetting the SQL Service). I am including this here because maybe it has something to do with internal stats? More often than not, I just have to reset the sql service now.

As far as I can tell, the execution plan looks relatively healthy. You can see the busiest quarter of the plan here: http://i.imgur.com/bwAFn.png   Note: I am showing this portion due to the 3 underlined items.

I have experimented with changing the joins for those 2 of those 3 items to hash joins, but it actually seems to slow it down further.

On beefier hardware, I have not encountered this error condition. For a variety of reasons, it I need to stay on the limited hardware for the production version.

DB Properties:

My hosting resources are fairly minimal.

~2.5 Ghz single core
1.7 GB RAM
30 GB drive, 6GB Free

With that said, the DB server is "happy" almost all the time, except when this error condition is trigger by this specific SPROC.
I really can't show my code here, though I'd like to.
I wonder if some sort of internal maintenance is being triggered? Is it possible for SQL Server to enter an infinite loop? (I utilize no loops in my code).
Is there a good set of debugging tips for handling this situation:?
Sometimes when a query runs, it triggers a state where SQL Server uses 100% of CPU for 90 seconds to 20+ minutes. 98% of the time when the query runs, there is no problem at all, but this 98% is shrinking slowly over time.
2012 Update

I setup a job that rebuilds all database indexes each night.
Auto Close has been set to False

This largely mitigated the problem. However, I still do have some issues, but they come up about 5% as often, which I still have not solved.


texts: Can column name be "Group" in PostgreSQL or in any databases

texts:
I was designing a project which specifies that in a table what column name should be and one of the column name in the specification is "group".
I tried creating it but it always throw a syntax error near the word = "group". I am really curious since the keyword in SQL is "group by" not group, so what is the reason, I cannot rename or create a column with a name "group".
Syntax and error I am using and getting:
ALTER TABLE test RENAME COLUMN sum TO group;
ERROR:  syntax error at or near "group"
LINE 1: ALTER TABLE test RENAME COLUMN sum TO group;


texts: Why does it appear as though SSIS is not reading a connection string properly?

texts:
I am executing an SSIS package on a db server and receiving an error. I can execute it just fine on my local machine but when I try to execute it via a SQL Job I get the following:

Executed as user: <domain>\<user>.
Microsoft (R) SQL Server Execute Package Utility  Version 11.0.6020.0 for 64-bit
Copyright (C) Microsoft Corporation. All rights reserved.
Argument ""CM.BackUpDB.ConnectionString";"Data "Source=<server>;Initial" "Catalog=<database>;
Provider=SQLNCLI11.1;Integrated" Security=SSPI;"" for option "parameter" is not valid.
The command line parameters are invalid.
The step failed.

It appears to me as though SSIS is splitting the connection string at the spaces instead of the semicolons. Why would it be doing that?

I am running SQL Server 2012. I do not have configuration files, nor am I dynamically generating the connection strings. They get configured in the SQL Job.


texts: When might a tsvector field pay for itself?

texts:
I've been experimenting with tsvector indexes for Full Text Search, and see that it's a common practice to generate an store a vector in a column of type tsvector. We're on Postgres 11.4, but I've already seen this very practice used as an example for PG 12 generated columns. (Simpler than using a trigger for the same purpose.)

My question is, what is the benefit? I tried an expression GIN index on the tsvector of a text field, and a GIN index on the stored tsvector. With around 8M rows locally, I couldn't measure any meaningful speed difference. Given that storing the vector as a column and an index takes more space, I'm curious if there are obvious cases where the extra cost is justified. For example, when you've got a lot more roles.

Note: We're storing the text in the database, so this isn't one of those setups where you index an external page/document/etc without absobing the source text into the database.


texts: Proper use of NULL, and utilizing CHECK constraints for business logic vs stored procedures

texts:
This question regards the proper use of NULL and utilizing CHECK constraints for business logic vs stored procedures.

I have the following tables setup.

I normalized the tables to avoid using NULLs. The problem is that some of these tables depend on each other due to business processes. Some devices must be sanitized, and some are tracked in another system. All devices will eventually be disposed in the Disposal table.

The issue is that I need to perform checks, such as if the boolean field RequiresSantization is true, then the DisposalDate cannot be entered until the Sanitize fields are entered.

Also, if the boolean value IsTrackedInOther is true, then the OfficialOutOfService fields must be entered before the DisposalDate can be entered.

If I merge all of these columns into the Archive.Device table then I will have NULL fields, but I will be able to manage all of the business rules using CHECK constraints.

The alternative is to leave the tables as they are, and manage the business logic in the stored procedure by selecting from the tables to check if records exist and then throw appropriate errors.

Is this a case where NULL can be used appropriately? The boolean fields IsTrackedInOther
and RequiresSanitization basically give meaning to the NULL fields. If IsTrackedInOther is false then the device is not tracked in the other system and SectionID and SpecialDeviceCode are NULL, and I know that they should be NULL becuase it is not tracked in the other system. Likewise, OfficialOutOfServiceDate and OOSLogPath I know will be NULL aswell, and a DisposalDate can be entered at any time.

If IsTrackedInOther is true, then SectionID and SpecialDeviceCode will be required, and if OfficialOutOfServiceDate and OOSLogPath are NULL, then I know they have not been officially removed from that system yet and thus cannot have a DisposalDate until they are entered.

So it's a question between

separate tables/no NULLs/enforce rules in stored procedures

vs

combined table/NULLs/enforce rules in CHECK constraints.

I understand that querying with NULLs in the picture can be complex and have somewhat undefined behavior, so separate tables and stored procedures seem beneficial in that sense. Alternatively, being able to use CHECK constraints and have the rules built into the table seems equally beneficial.

Any thoughts? Thanks for reading. Please ask for clarification where needed.

Update Example table if they were merged and I allowed NULLs.

* = Allow NULL

+-------------------------+
|Archive.Device Table     |
+-------------------------+
|DeviceID                 |
|SerialNumber             |
|DeviceTypeID             |
|IsTrackedInOther         |
|SectionID*               |
|SpecialDeviceCode*       |
|OfficialOutOfServiceDate*|
|OOSLogPath*              |
|OOSRemarks*              |
|RequiresSanitization     |
|SanitizeMethodID*        |
|SanitizeLogPath*         |
|SanitizeDate*            |
|SanitizeRemarks*         |
|Location                 |
|OriginalInventoryDate    |
|ArchiveDate              |
|LastUpdated              |
|ReasonID                 |
|StorageLocation          |
|ArchiveRemarks           |
|CategoryCode             |
+-------------------------+

Example CHECK:

IsTrackedInOther = 0 AND SectionID IS NULL AND SpecialDeviceCode IS NULL AND
OfficialOutOfServiceDate IS NULL AND OOSLogPath IS NULL AND OOSRemarks IS NULL
OR IsTrackedInOther = 1 AND SectionID IS NOT NULL AND SpecialDeviceCode IS NOT NULL

Does this seem a natural way to handle this, or is this a design problem?


texts: SQL Server 2008 R2 differential backups much larger than expected

texts:
We have several SQL Server 2008 R2 databases for which we perform a full backup every Sunday then differential backups Monday to Saturday. We also do transaction log backups every 10 minutes.

The first differential backup on Monday is usually quite small, but Tuesday to Saturday are much larger but similar in size to each other.

I used some scripts I found which predict the differential backup size, e.g. https://dougzuck.com/sql-differential-backup-size-prediction and http://www.sqlskills.com/blogs/paul/new-script-how-much-of-the-database-has-changed-since-the-last-full-backup/, and they predict a very much smaller backup size.

Examples are:

database1, full backup size 5Gb, diff size 3.5Gb, predicted diff size 84Mb
database2, full backup size 40Gb, diff size 1Gb, predicted diff size 17Mb

As you can see, the actual sizes are hugely different and can be half the size of the full backup after only a couple of days.

I know users aren't creating or modifying the actual data to any great extent. As far as I can tell, there are no index rebuilds or other management tasks happening between the full and differential backups.

It's like something is happening on Monday which causes the Tuesday onward differentials to be huge. Backup compression is not used.

I looked at the transaction log backups and the average of the total daily size is around 40Mb. I know it's not as simple as this, but 7 days at 40Mb = 280Mb. Doesn't explain the 3.5Gb differential backup! There are no huge transaction log files. There are no bulk load operations taking place.


texts:
Suppose I have the following long-running query

UPDATE [Table1]
SET [Col1] = 'some value'
WHERE [Col2] -- some clause which selects thousands of rows

and suppose the following query is executed while the above query is running

SELECT *
FROM [Table1]

Does the first query prevent the second query from running until the first query is done? If so, does the first query prevent the second query from running on all of the rows or just the rows involved in the WHERE clause?

EDIT:

Suppose the second query is

SELECT [Col1], [Col2]
FROM [Table1]
WHERE [Col2] -- some clause whose matching elements overlap those from
             -- the clause in the first query and which has additional matching elements


texts: how to integrate ola hallengren solution with log shipping?

texts:
I need to set up log-shipping from my SQLPROD2 to server2

but I am facing the following problems:

1) I already use ola hallegren backup solution, and it provides the 15 min log backups for all databases - can I use this job instead of the job the log shipping wants me to use?

or alternatively should I remove the databases that I add to the log shipping from the Ola's user database backup job?

2) what about the full backups that I take once a day?
would that mess up the log shipping? should I do a full backup with copy_only?


texts:
SUB-TREE WITHIN A TREE in MySQL

In my MYSQL Database COMPANY, I have a Table: Employee with recursive association, an employee can be boss of other employee. A self relationship of kind (SuperVisor (1)- SuperVisee (∞) ).

Query to Create Table:

CREATE TABLE IF NOT EXISTS `Employee` (
  `SSN` varchar(64) NOT NULL,
  `Name` varchar(64) DEFAULT NULL,
  `Designation` varchar(128) NOT NULL,
  `MSSN` varchar(64) NOT NULL,
  PRIMARY KEY (`SSN`),
  CONSTRAINT `FK_Manager_Employee`
              FOREIGN KEY (`MSSN`) REFERENCES Employee(SSN)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I have inserted a set of tuples (Query):

INSERT INTO Employee VALUES
 ("1", "A", "OWNER",  "1"),

 ("2", "B", "BOSS",   "1"), # Employees under OWNER
 ("3", "F", "BOSS",   "1"),

 ("4", "C", "BOSS",   "2"), # Employees under B
 ("5", "H", "BOSS",   "2"),
 ("6", "L", "WORKER", "2"),
 ("7", "I", "BOSS",   "2"),
 # Remaining Leaf nodes
 ("8", "K", "WORKER", "3"), # Employee under F

 ("9", "J", "WORKER", "7"), # Employee under I

 ("10","G", "WORKER", "5"), # Employee under H

 ("11","D", "WORKER", "4"), # Employee under C
 ("12","E", "WORKER", "4")

The inserted rows has following Tree-Hierarchical-Relationship:

         A     <---ROOT-OWNER
        /|\
       / A \
      B     F
    //| \    \
   // |  \    K
  / | |   \
 I  L H    C
/     |   / \
J     G  D   E

I written a query to find relationship:

SELECT  SUPERVISOR.name AS SuperVisor,
        GROUP_CONCAT(SUPERVISEE.name  ORDER BY SUPERVISEE.name ) AS SuperVisee,
        COUNT(*)
FROM Employee AS SUPERVISOR
  INNER JOIN Employee SUPERVISEE ON  SUPERVISOR.SSN = SUPERVISEE.MSSN
GROUP BY SuperVisor;

And output is:

+------------+------------+----------+
| SuperVisor | SuperVisee | COUNT(*) |
+------------+------------+----------+
| A          | A,B,F      |        3 |
| B          | C,H,I,L    |        4 |
| C          | D,E        |        2 |
| F          | K          |        1 |
| H          | G          |        1 |
| I          | J          |        1 |
+------------+------------+----------+
6 rows in set (0.00 sec)

[QUESTION]
Instead of complete Hierarchical Tree, I need a SUB-TREE from a point (selective) e.g.:
If input argument is B then output should be as below...

+------------+------------+----------+
| SuperVisor | SuperVisee | COUNT(*) |
+------------+------------+----------+
| B          | C,H,I,L    |        4 |
| C          | D,E        |        2 |
| H          | G          |        1 |
| I          | J          |        1 |
+------------+------------+----------+

Please help me on this. If not query, a stored-procedure can be helpful.
I tried, but all efforts were useless!


texts: Why doesn't "Total Space Reserved" equal the sum of Data File space reserved and Transaction Log space reserved in the Disk Usage Report?

texts:
When running the disk usage report, it's showing the "Total space reserved" as 275 GB but the "Data Files Space Reserved" is 279 GB and the "Transaction Log Space Reserved" is 1.7 GB. Why isn't the "Total space reserved" closer to the total of "Data Files Space Reserved" + "Transaction Log Space Reserved" ?
Thanks!


texts: Failure to start instance resource on1 node of a 3 node of an Oracle 12c RAC

texts:
After adding a 3rd node to our 2 node 12c RAC, one of the existing node is now unable to start the database resource.

Extract from crsctl stat res -t:

ora.datacdb.db

  1        ONLINE  ONLINE       rac1                  Open,STABLE
  2        ONLINE  OFFLINE                               STABLE
  3        ONLINE  ONLINE       rac3                  Open,STABLE

Any attempt to start the resource (manual or automatic) gives the following:

  C:\Windows\system32>crsctl start res ora.datacdb.db CRS-2672:
  Attempting to start 'ora.datacdb.db' on 'rac2' ORA-12560:
  TNS:protocol adapter error CRS-5017: The resource action
  "ora.datacdb.db start" encountered the following error: ORA-03113:
  end-of-file on communication channel Process ID: 0 Session ID:
  0 Serial number: 0 . For details refer to "(:CLSN00107:)" in
  "C:\app\12.1.0\grid\log\rac2\agent\crsd\oraagent_system\oraagent_system.log". CRS-2674: Start of 'ora.datacdb.db' on 'rac2' failed CRS-2679:
  Attempting to clean 'ora.datacdb.db' on 'rac2' CRS-2681: Clean of
  'ora.datacdb.db' on 'rac2' succeeded CRS-2528: Unable to place an
  instance of 'ora.datacdb.db' as all possible servers are occupied by
  the resource CRS-4000: Command Start failed, or completed with
  errors.

If I try to connect to the instance via a bequeath connection I get either the same TNS error, or a ORA-27101 Shared Realm error.

I already have an Oracle SR open for about a week, but so far it's not yielded anything constructive.


texts:
Help me improve this query. It took 8 minutes to generate 16k rows only.

SELECT TPG.group_id
INTO   #tempgroups
FROM   [sqlcdb-h05].[cosmocallv6].[dbo].tpersonnel AS TP
       INNER JOIN [sqlcdb-h05].[cosmocallv6].[dbo].tpersonnelprofile AS TPP
               ON TP.person_id = TPP.person_id
       INNER JOIN [sqlcdb-h05].[cosmocallv6].[dbo].tprofilegroups TPG
               ON TPP.profile_id = TPG.profile_id
WHERE  ( TP.person_id = 766 )
       AND ( TP.tenant_id = 112 )

SELECT DISTINCT AC.Agent_ID, AC.Group_ID, AC.GroupName
INTO #TempAgent
FROM [offlinedbsvr].[PLDTCustomerCare].[dbo].AgentConfiguration AC INNER JOIN
     #TempGroups ON AC.Group_ID = #TempGroups.Group_ID

DELETE FROM #TempAgent
WHERE NOT([dbo].[GetUpdatedAgentGroup](#TempAgent.Agent_ID) = #TempAgent.Group_ID)

-- End Grouping
BEGIN
    SELECT DISTINCT
        [Date] = CONVERT(VARCHAR(25), arrivaltime, 101),
        [Start Time] = arrivaltime,
        [End Time] = terminatedtime,
        [Duration] = DATEDIFF(ss, arrivaltime, terminatedtime),
        [Agent First Name] = co.agentfirstname,
        [Agent Last Name] = co.agentlastname,
        [Group Name] = groupname,
        [Phone Number Called] = outdialingaddress
    FROM
        [offlinedbsvr].[PLDTCustomerCare].[dbo].StreamSummary ss
            INNER JOIN [offlinedbsvr].[PLDTCustomerCare].[dbo].msgcallout co ON ss.call_id = co.call_id
            INNER JOIN #TempAgent ac ON co.agent_id = ac.agent_id
    WHERE
        arrivaltime BETWEEN '4/1/2014' AND '4/2/2014' AND
        ss.calldirection_id = 1
    ORDER BY
        [Date],
        [Group Name],
        [Agent Last Name],
        [Agent First Name],
        [Start Time],
        [End Time]

For the getUpdatedAgentGroup function, here's the code:

SELECT TOP 1 @AgentGroup = AC.Group_ID
FROM [offlinedbsvr].[PLDTCustomerCare].[dbo].AgentConfiguration AC
WHERE AC.Agent_ID = @AgentID
ORDER BY TIMESTAMP DESC


texts: SqlPackage importing of very large bacpacs to Azure

texts:
I want to import a database from a .bacpac file to a SQL Server in Azure. I read the document.
It says that there is a flag called DatabaseMaximumSize=(INT32).
I wanted to know if there's a limit that sqlpackage can support? For example if I got 8 gb of RAM available, will Sqlpackage be able to load larger .bacpacs than that, meaning it doesn't load it all to the memory?


texts: Query slow when using function in the WHERE clause

texts:
This is fast (49ms):

v_cpf_numerico := ext.uf_converte_numerico(new.nr_cpf);

select cd_cliente into v_cd_cliente
from central.cliente where nr_cpf_cnpj = v_cpf_numerico;

This is slow (15 seconds):

select cd_cliente into v_cd_cliente
from central.cliente where nr_cpf_cnpj = ext.uf_converte_numerico(new.nr_cpf);

Function:

create or replace function ext.uf_converte_numerico(_input varchar(30)) returns bigint
as
$$
begin
    _input := regexp_replace(_input, '[^0-9]+', '', 'g');

    if _input = '' then
        return null;
    end if;

    return cast(_input as bigint);
end
$$ language plpgsql;

I am using PostgreSQL 12.
Why is the second variant slow?


texts: INSERT ON CONFLICT DO NOTHING and RAISE unique_violation

texts:
I am processing messages from a distributed queue that guarantees at least once delivery. My strategy for handling duplicate messages has been to use ON CONFLICT DO NOTHING in my inserts.

I had to convert a few tables in my schema to use table partitioning and a trigger to handle which subtable to direct the insert into. The trigger function checks for duplicate entries and performs a RAISE unique_violation. Unfortunately the ON CONFLICT clause doesn't catch these errors and causes my query to error.

I can change the trigger to ignore these duplicates internally, but I don't want to hide these conflicts from other users of the system.

Is there a way to catch the unique_violation from the partition table via ON CONFLICT?

TL;DR How to I RAISE in a trigger that I can handle with ON CONFLICT?


texts:
I try to select the period of employment for the members in a Company.

Example:
Date of join: 01.01.2001
Today: 27.02.2014
Now, I want this:
13 years, 1 month and 27 days

I have a Statement which works on MSSQL, but I cant translate it to firebird-SQL.

Could anybody help me?

Thx

Erik


texts: mySql Event gets expired on closing of the command prompt

texts:
As I am quite new to server side, I tried to create a event using

  CREATE EVENT enetname ON SCHEDULE AT '2013-06-13 18:00:00'+INTERVAL 30
  SECOND DO update tablname set status= 'Expired' where expirydate <
  CURDATE();

This event is creating updsting the field till the time the mysql cmdprompt is open.

When I am closing the command prompt the event gets dropped/expired, where as my event scheduler is running and waiting fr next activation.

Please tell me how to avoid the dropping of event.


texts: Set maximum cpu usage to a single job in SQL server

texts:
Is it possible to restrict cpu usage for a job in SQL Server?

I found Resource Governor does work for an instance of mssql for a specific users group. However, I only need to set the maximum cpu usage to a single Job not to the instance of mssql as there is only one user that performs all the operations in the database.


texts: Creating a trigger which stores updated value with old value in separate table

texts:
I'm trying to create a trigger which adds an old value to a table with the new value next to it.

For example, if someone were to update their address, I'd like the trigger to add the old address to the table, followed by the new address, then add a timestamp.

The tables:

CREATE TABLE AccountDetails (
    accountID INTEGER AUTO_INCREMENT,
    firstName CHAR(40) NOT NULL,
    lastName CHAR(40) NOT NULL,
    userName VARCHAR(25) UNIQUE,
    userPassword VARCHAR(264),
    emailAddress VARCHAR(30) UNIQUE,
    dateOfBirth DATE NOT NULL,
    phoneNo VARCHAR(20) NOT NULL,
    address1 VARCHAR(50) NOT NULL,
    address2 VARCHAR(50) NOT NULL,
    PRIMARY KEY (accountID)
)  ENGINE INNODB;

CREATE TABLE OldDetails (
    accountID INTEGER NOT NULL,
    oldValue VARCHAR(300),
    newValue VARCHAR(300),
    dateModified DATE,
    PRIMARY KEY (accountID),
    FOREIGN KEY (accountID)
        REFERENCES AccountDetails (accountID)
)  ENGINE INNODB;

The main part I am struggling with is selecting the value that was changed in the update sequence.
Thank you all for your help!


texts: mysqld constant CPU usage when absolutly nothing is connected and no queries

texts:
I'm using mysql on a windows development machine, and all was good for a long time.

But today my PC was running slowly, and I noticed mysqld.exe is using between 25% and 40% CPU.

I've tried rebooting and giving it some time, but the CPU usage is fairly constant even when idleing on the desktop.
I've noticed the hard drive is working quite hard too, probably because of mysqld

I'm certain nothing is using the server as the only thing that would connect to it is the application on my local machine that I am developing, and that is not running.

But, just to be sure I ran the following statements

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysqladmin -u root -p status
Enter password: **********
Uptime: 995  Threads: 1  Questions: 2  Slow queries: 0  Opens: 67  Flush tables: 1
Open tables: 60  Queries per second avg: 0.002

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysqladmin -u root -p processlist
Enter password: **********
+----+------+----------------+----+---------+------+-------+------------------+
| Id | User | Host           | db | Command | Time | State | Info             |
+----+------+----------------+----+---------+------+-------+------------------+
| 2  | root | localhost:1775 |    | Query   | 0    | init  | show processlist |
+----+------+----------------+----+---------+------+-------+------------------+

which also seem to show the sever isn't doing anything.

So, what gives?  What is mysqld doing?

Edit: I did recently kill an update statement that was taking a long time.  Could this have anything to do with it?


texts:
I'm using Percona MySQL 8.0.20 running on Ubuntu 18.04 on an AWS ec2 m5.2xlarge instance. The Xtrabackup version being used is 8.0.14.
While testing the restore process, I'm getting the error as listed in this pastebin link: https://pastebin.pl/view/74b1832d
I'm also listing a snippet of the entire error listing -
...
Open file list len in shard 31 is 0
Open file list len in shard 32 is 9
InnoDB: Assertion failure: dict0dict.cc:1216:table2 == nullptr
InnoDB: thread 140008395342336InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to https://jira.percona.com/projects/PXB.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
15:30:33 UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x55bc783d1800
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7ffe5f6a38b0 thread_stack 0x46000
xtrabackup(my_print_stacktrace(unsigned char const*, unsigned long)+0x3d) [0x55bc736ec8dd]
xtrabackup(handle_fatal_signal+0x2fb) [0x55bc7255c32b]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x128a0) [0x7f563e6918a0]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0xc7) [0x7f563c6eef47]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x141) [0x7f563c6f08b1]
xtrabackup(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0xb2) [0x55bc729ddca2]
xtrabackup(dict_table_add_to_cache(dict_table_t*, unsigned long, mem_block_info_t*)+0x11c) [0x55bc726f28ac]
xtrabackup(dd_table_create_on_dd_obj(dd::Table const*, dd::Partition const*, std::__cxx11::basic_string<char, std::char_traits<char>, Stateless_allocator<char, dd::String_type_alloc, My_free_functor> > const*, bool, unsigned int)+0x1938) [0x55bc72717238]
xtrabackup(+0x144cc65) [0x55bc72718c65]
xtrabackup(dd_table_load_on_dd_obj(dd::cache::Dictionary_client*, unsigned int, dd::Table const&, dict_table_t*&, THD*, std::__cxx11::basic_string<char, std::char_traits<char>, Stateless_allocator<char, dd::String_type_alloc, My_free_functor> > const*, bool)+0x53) [0x55bc72718cd3]
xtrabackup(dict_load_tables_from_space_id(unsigned int, THD*, trx_t*)+0x665) [0x55bc72042155]
xtrabackup(+0xd766c3) [0x55bc720426c3]
xtrabackup(+0xd7bfe0) [0x55bc72047fe0]
xtrabackup(main+0xd37) [0x55bc72006787]
/lib/x86_64-linux-gnu/libc.so.6(__libc_start_main+0xe7) [0x7f563c6d1b97]
xtrabackup(_start+0x2a) [0x55bc720344ca]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): Connection ID (thread ID): 0
Status: NOT_KILLED

Please report a bug at https://jira.percona.com/projects/PXB

Any pointers to this would be of great help.


texts: ER Relationship Total and Partial Participation Example

texts:
I am trying to understand partial and total participation.

Given the statement:

  "Each restaurant must have one or more dishes and each dish must be provided by exact one restaurant."

The below text is my current guess of how this would be written out...

Restaurant ==(1)== Provide ==(n)== Dish

...where Restaurant and Dish are entities and Provide is the relationship.

Is this correct?