texts: Is it possible to create an empty or dummy index in postgres?

texts:
I am working on cleaning up a database so that an application upgrade script can run. The state is that the upgrade script expects an index to exist only so that it can drop that index. The index was dropped in production by hand some time ago. Artificially altering the upgrade script or telling the upgrade process it has already run that step isn't an option for non-technical reasons beyond my control.

Instead of taking the time to recreate the index is it possible to create a "dummy" or empty index with the same name so that the upgrade script can run?

This is for postgresql 9.0 or later.


texts: is there a way to configure Azure SQL databases for high-insert scenarios?

texts:
I'm working on a legacy application that uses SQL Server as a backing store. There's a portion of the app that does a ridiculous amount of inserts to a single table. Re-architecting this portion of the app represents a large time investment, probably 2 weeks for one developer. It works as is so we're, well happy isn't the right word, I guess we grudgingly accept it.

However, that portion of the app performs terribly when connected to an Azure database. I'm talking a sudden 4x increase in time that it takes to insert records. Each cycle inserts about 4000 rows into this table via a call to a stored proc (4000 calls; it's awful, I know). On a local SQL Server it will take about 5 minutes but on Azure that spikes up to >15 minutes and our users find that unacceptable.

The table has about 20 columns, mostly bits and ints. It has one primary key (int) and no other indexes. It has 2 foreign keys. Including the full CREATE script here seems excessive, but I can if needed.

What I'm wondering is if there's any way to "tune" an Azure database to handle large numbers of inserts better. Altering the code isn't a great option right now, but it is the right choice down the road.

Azure v12. We use S2 or S3 instances and don't see any high CPU or high DTU usage other than spikes for a few seconds here and there. Databases are all under 1GB.


texts: MySQL Cluster Steps To Recover Data If Catstrophic Failure Occurs

texts:
I want to know if a catstrophic failure occurs (like a power outage), and all nodes in MySQL Cluster are shutdown in cluster then data will be lost even though a backup was taken? Or it possible that data can still be retrieved?

If yes, then please tell me how it can be retrieved.


texts: Moving the IBM Informix IFMXDATA directory to another machine

texts:
I'm a total newbie when it comes to Informix DBs, and I'm stuck with what looks like files that contain an Informix database. I'd like to know if it's possible to add or import these files into an existing installation of Informix.

Probably I should explain myself a bit better so...I have the following Dir\File structure:

C:\IFMXDATA\somename\dbditte_dat.000
C:\IFMXDATA\somename\dbditte_dat.001
C:\IFMXDATA\somename\dbimage_dat.000
C:\IFMXDATA\somename\dbtemp_dat.000
C:\IFMXDATA\somename\llogdbs_dat.000
C:\IFMXDATA\somename\llogdbs_dat.001
C:\IFMXDATA\somename\rootdbs_dat.000

The destination machine is running a much newer Informix version (v12.0), which, I guess, won't make this any easier.
These files are pretty large (500Mb - 2Gb) and, I hope, contain data from an Informix 3.85 installation made quite some time ago. These files is all I have, to restore this DB on another machine.
I already tried by simply replacing them on the destination computer, which didn't work, because the rootsdb_dat file gets overwritten, and I guess it's where the info about tables, and so on, is stored.

After prolonged google-ing I was unable to find information about how I could achieve this, obviously any sane person would export the data properly and then re-import it, which, sadly is not an option right now.

All I need is to access the data, I don't care if the DB name or connection method changes.
Is there any way to import the tables from these files?
Any way to fool the Informix installation into accepting these files?

EDIT
When I try to replace newly generated files on the destination machine with those from the source machine (those that I need), the Informix service crashes and the log states:

17:55:31  Assert Warning: I/O bad request chunk 1, pagenum 87248, pagecnt 1
17:55:31  IBM Informix Dynamic Server Version 12.10.TC3DE
17:55:31   Who: Session(1, informix@WDKGPMOTORS, 0, 00000000)
        Thread(7, main_loop(), 0, 1)
        File: rsbuff.c Line: 5721
17:55:31   Action: Please notify IBM Informix Technical Support.
17:55:31  stack trace for pid 3692 written to C:\PROGRA~1\IBMINF~2\tmp\af.3efbef3
17:55:32   See Also: C:\PROGRA~1\IBMINF~2\tmp\af.3efbef3
17:55:34  I/O bad request chunk 1, pagenum 87248, pagecnt 1
17:55:35  oninit: Fatal error in shared memory initialization

When I try to run oninit -v i get:

Initializing log/checkpoint information...FAILED
oninit: Fatal error in shared memory initialization

Thank you!


texts: SQL query to convert Column to Row using Pivot/Unpivot

texts:
My Select Table. I tried Pivot query to get my expected result but I was struct to generate the expected result.

WITH PersonOptions(ID,[Year], [Text], Value1, Value2) AS
(
              SELECT 'A',2000, 'X',10,2
    UNION ALL SELECT 'A', 2001, 'X',21,1
    UNION ALL SELECT 'A', 2000, 'Y',13,3
    UNION ALL SELECT 'A', 2001, 'Y',2,3
    UNION ALL SELECT 'B', 2000, 'X',0,2
    UNION ALL SELECT 'B', 2001, 'X',5,1
    UNION ALL SELECT 'B', 2000, 'Y',6,4
    UNION ALL SELECT 'B', 2001, 'Y',9,5
    UNION ALL SELECT 'C', 2000, 'X',3,5
    UNION ALL SELECT 'C', 2001, 'X',2,8
    UNION ALL SELECT 'C', 2000, 'Y',9,2
    UNION ALL SELECT 'C', 2001, 'Y',8,3
)
select *  from PersonOptions

ID  Year    Text    Value1  Value2
A   2000    X        10       2
A   2001    X        21       1
A   2000    Y        13       3
A   2001    Y        2        3
B   2000    X        0        2
B   2001    X        5        1
B   2000    Y        6        4
B   2001    Y        9        5
C   2000    X        3        5
C   2001    X        2        8
C   2000    Y        9        2
C   2001    Y        8        3

Expected Output:

               Value1          Value2
ID  Text    2000    2001    2000    2001
A    X       10      2       21      1
A    Y       13      3       2       3
B    X        0      2       5       1
B    Y        6      4       9       5
C    X        3      5       2       8
C    Y        9      2       8       3


texts: How to remove Windows NTFS Compression from Oracle Database datafiles?

texts:
We know Windows NTFS compression on Oracle Database datafiles is a bad idea.

See here and here.

It prevents Asynchronous Random Disk I/O which can increase the risk of corruption and generally slow down the database.

However, we have some Oracle databases (11g & 12c) on Windows Server 2012 R2 servers with Windows file compression on the datafiles. How do we safely deactivated this file compression? Are there any watch-outs?

Thanks.


texts: MongoDB insert data if not exists and update only the array if exists on large amount of documents

texts:
So I have about 1M records to first insert and then subsequently update an array in them whenever I run the script.

A typical record

    {

    "_id" : Object(),

    "id" : "my_unique_id",

    "name" : "my name",

    "books" : [
{
"name" : "The last song",
"author" : "some_author"
}
]

    }

So I have about 1M of these records when the database has no documents, It can simply insert the data, but when the database has the records the database should only update "books" array and insert the new data we have, I mean push into the books.

Currently, I am doing this, looking for documents, if exists I push into it and if not simply inserting it, but as my database went upto 25k records, I am getting this error.

Code for inserting documents

async function insert_document(my_object, collection_name) {

insertion_requests_values++

while (insertion_requests_values > insertion_requests_values_threashold)
{
    await sleep(300)
}

let stats = my_object["statistics"]
stats["time"] = new Date().toISOString().slice(0, 19)
    .replace('T', ' ')+'.'+ new Date().getMilliseconds();

my_object["statistics"] = []
my_object["statistics"].push(stats)

// The code is commented becase it simply insets a document, we need updations

// database_object.collection(collection_name).insertOne(my_object, function(err, res) {
//     if (err) {
//
//         errorLog(err)
//         return
//     }
//     log("documents inserted = " + ++inserted_documents);
// });

var query = { id: my_object.id};
database_object.collection(collection_name).find(query).toArray(function(err, result) {
    insertion_requests_values--

    if (err) {

        errorLog(err)
        return
    }
    if(result.length > 0 ) {
        let newValue = result[0]

        try {

            newValue["statistics"].push(stats)
        }
        catch (e) {
            newValue["statistics"] = []
            newValue["statistics"].push(stats)

        }
        database_object.collection(collection_name).updateOne(query, { $set: newValue },  { upsert: true }, function(err, res) {
            // insertion_requests_values--
            if (err) {

                errorLog(err)
                return
            }
            log("document updated= " + ++updated_documents);
        });

        //The code is commented becuase it deletes and add new document
        //The code in the comments is basically looking for existing documents and then deleting
        // that document and inserting new one, same as updating stuff
        // database_object.collection(collection_name).deleteOne(query, function(err, obj) {
        //     if (err) {
        //
        //         errorLog(err)
        //         return
        //     }

        // database_object.collection(collection_name).insertOne(my_object, function(err, res) {
        //     if (err) {
        //
        //         errorLog(err)
        //         return
        //     }
        //     log("document updated= " + ++updated_documents);
        // });
        // });

    }
    else {

        database_object.collection(collection_name).insertOne(my_object, function(err, res) {
            // insertion_requests_values--

            if (err) {

                errorLog(err)
                return
            }
            log("documents inserted = " + ++inserted_documents);
        });

    }
});

}

Error mongo is giving...

  2019-08-18T16:02:14.823+0000 I COMMAND  [conn16] command
  TubeC.TubeCVideos command: find { find: "TubeCVideos", filter: { id:
  "nHpdrzJA5NY" }, returnKey: false, showRecordId: false, $db: "TubeC" }
  planSummary: COLLSCAN keysExamined:0 docsExamined:28108
  cursorExhausted:1 numYields:221 nreturned:1 reslen:3753 locks:{
  Global: { acquireCount: { r: 222 } }, Database: { acquireCount: { r:
  222 } }, Collection: { acquireCount: { r: 222 } } } storage:{}
  protocol:op_msg 129ms

How can I achieve that with 1M records using NodeJs?


texts: What is the latest version of SQL Server native client that is compatible with Sql Server 2008 r2

texts: Title says it all, what is the most recent version of Sql Server native client that is compatible with Sql Server 2008 R2


texts: How to set or grant SELECTINAUTH for a schema in DB2 11.5

texts:
I'm running DB2 11.5 and want to grant select, insert, update or delete rights for all tables in a specific schema. SELECTINAUTH etc. seems to be perfect for this.
When I'm looking to the SYSCAT.SCHEMAAUTH Catalog View I can see the following columns:

ALTERINAUTH
CREATEINAUTH
DROPINAUTH
SELECTINAUTH
INSERTINAUTH
UPDATEINAUTH
DELETEINAUTH

The first 3 privileges can be granted by e.g. GRANT ALTERINAUTH ON SCHEMA  TO USER . But this don't work for the last 4 columns.
The Knowledge Center tells that this are implicit privileges, but I can't find any hint, how a user or a group can get this privileges implicit or explicit.
Does anybody know when or how e.g. INYSERTINAUT is set to Y?


texts: Is it possible to backup a subset of a database in Microsoft SQL Server?

texts:
Using Microsoft SQL Server, is there a way to backup a subset of a production database? I only want the database structure, and a very small amount of transaction data for a few selected tables.

I want to use this backup file to refresh development database.

For Oracle I see this is achieved using this Powershell script:

userid="/ as sysdba"
compression=all
directory=TEST_DIR
flashback_time=systimestamp
dumpfile=XX.dmp
logfile=xx.log
schemas=XXXXX
query=SCHEMA.TABLE:"where <condition>
exclude=TABLE:"IN (x)"


texts: How to remove duplicates in col1 and get group MAX in col2? / mysql

texts:
Given the following table:

col1 Col2
A    1
B    1
C    1
B    2
B    3
A    0
C    5

how do I write a SELECT query (I just assumed has to be SELECT) that returns the unique items of col1 and the max values of col2, like this:

A   1
B   3
C   5


texts: Is there a DMV through which I can tell that my workload type is 'ad hoc'?


texts: MySQL memory is keep on increasing , any reason?

texts:
On one of our production host MySQL memory is keep on increasing day-by-day.
I tried reboot of MySQL instance but still no avail.

System has 16Gb of RAM.

TOP output

top - 13:05:13 up 35 days,  1:39,  1 user,  load average: 0.00, 0.01, 0.00
Tasks: 101 total,   1 running, 100 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.0%us,  0.0%sy,  0.0%ni, 99.8%id,  0.2%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  16436176k total, 13097948k used,  3338228k free,   233236k buffers
Swap:  4194296k total,    33928k used,  4160368k free,  5540804k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
19548 mysql     15   0 13.9g 6.7g 6800 S  0.0 42.8  35:56.54 mysqld

MySQL configuration

max_allowed_packet                      = 128M
max_connections                         = 500
thread_cache_size                       = 100
open_files_limit                        = 15000
table_open_cache                        = 3000
tmp_table_size                          = 64M
max_heap_table_size                     = 64M
read_buffer_size                        = 512K
read_rnd_buffer_size                    = 512K
sort_buffer_size                        = 1M
key_buffer_size                         = 128M
myisam_sort_buffer_size                 = 128M
bulk_insert_buffer_size                 = 64M


texts:
This may seems like a stupid question, but I cant seem to find a good answer for it. I have a pretty good intuitive understanding of what ROWS_READ in for example  sysibmadm.snapdyn_sql means, and what conclusions can be drawn from this metric. However, I'm not sure exactly what this means, and I can't find any information in the documentation. Looking at:

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.mon.doc/doc/r0001317.html

it says: The number of rows read from the table.

My question is due to observations that sometimes ROWS_READ does not increase eventhough NUM_EXECUTION does. At the same time there are situations where ROWS_READ increases along with NUM_EXECUTIONS eventhough data definitely fits in bufferpool.

Are ROWS_READ physical reads (from disk to bufferpool) or logical reads (in bufferpool) or orthogonal to physical/logical? If the metric is measured in the bufferpool, how can the first phenomena be explained? Invalidated package (something wrong with counters in package cache), a sudden change to index only access or something else?


texts: How to retrieve the query I used to create a view?

texts:
for my company I created a view which is used for exporting.
Maybe there is a problem with the query that is used to create this table.

So here is my question:

Is it possible to get the query I used to create said view table ?


texts: What is non-page data structure in SQL Server

texts:
I was monitoring one of the SQL Server in my client evnironment and I can see there is momentary blocking on SQL Server sessions with wait type as LATCH_EX and with wait resource as ACCESS_METHODS_DATASET_PARENT.

So I went to world famous library of wait types (sqlskills.com) and I find below line written by Mr. Paul S. Randal in description:

  "This wait type occurs when a thread is waiting for access to a
  non-page data structure so that it can modify the data structure."

I got confused with what does non-page data structure means.

I am not from CS background. Any help is highly appreciated.


texts: ORA-24155 - Why am I getting this error when creating a chain rule

texts:
When trying to create a new chain rule, I receive ORA-24155 stating that the the rule I'm trying to create is not in the ruleset which doesn't make sense.

I stop and drop all jobs/chains and then attempt to create one.  Are chain rule names shared with all chains?  Should I use a dynamic name instead of "StartChain".  Only guessing.  Any idea?

-- Stop Job
BEGIN
  sys.dbms_scheduler.stop_job(job_name => l_job_name);
EXCEPTION
  when job_not_running then null; -- dont care, continue
  when job_doesnt_exist then null; -- dont care, continue
END;

-- Drop Job
BEGIN
  sys.dbms_scheduler.drop_job(job_name => l_job_name);
EXCEPTION when job_doesnt_exist then null; -- dont care, continue
END;

-- Drop Chain
BEGIN
  sys.dbms_scheduler.drop_chain(chain_name => l_chain_name);
EXCEPTION when chain_doesnt_exist then null; -- dont care, continue
END;

-- Create chain
sys.dbms_scheduler.create_chain(chain_name          => l_chain_name,
                                rule_set_name       => NULL,
                                comments            => '');

sys.dbms_scheduler.define_chain_rule(chain_name => l_chain_name,
                                     rule_name  => '"StartChain"',
                                     condition  => 'True',
                                     action     => 'START "Step 1 - Build Bio"',
                                     comments   => '');


texts:
My undo tablespace is full after some modifications. Las column is % of space used.

xxx_undo_01.dbf UNDO    2147483648  16777216    2048    2032    16  0,78    99,22

Now i cannot execute any query according to this. How can i free the undo tablespace ?

Thanks in advance.


texts:
I am developing a school management system.

Most data will not be reused after the end of the school year.

I am contemplating using schemas to separate data in various school years, however I am afraid I don't know very much about schemas.

Would it be a good idea to use a new schema for each school year to store data (see below), rather than adding a year field and using a where clause for all queries?

schema_year1.attendance_of_staff
schema_year2.attendance_of_staff


texts: UPSERT with ON CONFLICT using values from source table in the UPDATE part

texts:
Given:

CREATE TABLE A (
PK_A INT8 NOT NULL,
A INT8,
PRIMARY KEY (PK_A)
);

CREATE TABLE B (
PK_B INT8 NOT NULL,
B INT8,
PRIMARY KEY (PK_B)
);

This query:

insert into table_b (pk_b, b)
select pk_a,a from table_a
on conflict (b) do update set b=a;

causes the following error:

ERROR:  column "a" does not exist
LINE 1: ...elect pk_a,a from table_a on conflict (b) do update set b=a;
                                                                 ^
HINT:  There is a column named "a" in table "*SELECT*", but it cannot be referenced from this part of the query.

How to do the update while referring to content of table_a?


texts:
Error message:

You do not have privileges to manipulate with the users!

Appears in PhpMyAdmin using Mariadb 10.3.7 on MacOs High Sierra installation from Homebrew

I'm logged in as root
installed and rebooted with multiple server options: Mysql 5.7, Mysql 8.0
Mariadb 10.2 and 10.3.7.

I've tried everything from granting tables, flushing the permissions.
I need to be able to edit users from the PhpMyAdmin page and its not allowing me even though I have root access (with password set)


texts:
I need to create a character field with some rules, they are,

Field name:

contract_number.

Rules: Contract Number

Contract number must be alphanumeric.
Contract number must be unique.
Contract number must be auto increment.

Example:

CBE27032014001

Here, the contract number represents the following terms,

CBE 27 03 2014 001

CBE - (Coimbatore) City Name.
27032014 - Date Month Year.
001 - Contract reference number (just numeric value).

My database contract table as follows,

create table contract
(
contract_id int not null auto_increment,
contract_number  char(14) not null,
................
unique key(contract_number),
primary key(contract_id)
);

This is completely new to me,

How to implement this task?
Is there any general logic to implement such kind of tasks?
Any suggestions to split the big task into smaller tasks, so that it will easy to understand?

Thanks.


texts: SQL Server SPNs not being created, state: 43

texts:
I'm trying to configure a SQL Server 2014 (12.0.5000.0) instance to use a regular user account in AD and Kerberos auth. During installation the default accounts (local system ones) were used, and the SPNs MSSQLSvc/<FQDN>:[<port> | <instancename>] were both created on the server's computer account.

I reconfigured all the SQL Server services to use the user account. When I manually restart the services via Configuration Manager the SPNs are correctly recreated on the user account and Kerberos auth works (checking via SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid ; from a domain joined client).

I then manually removed the SPNs on the computer account to avoid duplicate SPNs.

The problem is when the server restarts, the SPNs are not recreated (error follows), Kerberos auth does not work on the server, and I end up with NTLM auth instead.

  SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.

  The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/<FQDN>:<instancename> ] for the SQL Server service. Windows return code: 0xffffffff, state: 43. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

  The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/<FQDN>:<port> ] for the SQL Server service. Windows return code: 0xffffffff, state: 43. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

Both the service (user) account, and the server (computer) account have been granted explicit permission to read and write SPNs on themselves and each other.

If I manually restart the services via Configuration Manager once the machine is booted and I'm logged in, the SPNs do get recreated (log message follows) and Kerberos does work.

  SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.

  The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/<FQDN>:<instancename> ] for the SQL Server service.

  The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/<FQDN>:<port> ] for the SQL Server service.

What I can't work out is

what state: 43 means in the SQL Server ERRORLOG. Presumably it's not referring to Idaho.
why the SPNs do get recreated on a restart when using Configuration Manager, but do not get recreated on a server restart
why on a server restart the SPNs (which still exist) don't get used [if I trigger the restart in Configuration Manager they get recreated, but in a server shutdown, they aren't deleted, therefore they still exist on server startup]

I notice there is an SQLWriter service which is still running as Local System but according to SQL Writer Service, this is correct.

The FFL is 2008, and the DFL is 2008 R2.


texts: Finding rows affected by deadlock on page locks

texts:
We've got a db that has been pretty much deadlock free for over 6 months, but a day or two ago we had over 60, all on the same two or three pages of the same table (~12,000,000 rows, ~250 bytes per row.)

From the deadlock graph I can see what pages are common in all the deadlocks, and part of the query that was running (always an INSERT vs a SELECT) but I have no idea what rows or data this involves. Is there any way to find out?


texts: Schedule replication of SQL Server views to MySQL remote database

texts:
I have a Microsoft SQL Server 2005 Database inhouse I have to publish some information on the internet and I can't (for security policy) query this database from the web server.

So I came up with the solution of replicating the information (contained in 3 views) from my SQL Server to a MySQL server on the web server.

I have to schedule this synchronization (one way SQL Server always overwrites MySQL) every night. Ideally SQL Server should push updates into MySQL server (so security ninjas are satisfied).

I read some article about but I have no clue where to begin.

I'm familiar with PHP, Python. Preferably I'd like to realize this with the internal schedule of SQL Server and a stored procedure or a maintenance task

Thank you in advance


texts: SQL Server - Why does importing lots of columns with few rows takes way longer than few columns with lots of rows?

texts:
I am using the import wizard in SQL Server 2014. When I import a text file with 40 columns with 1 million row it is pretty fast. On the other side when I am importing a few rows with 268 columns it is taken AGES ! The import seems to proceed very slowly "blocks" of 128 records. And between each "block" there are minutes going by...
Could someone give me some hints to try understanding what is happening?
Thank you in advance


texts: Select Which Database Mail Profile is used in SQL Server 2008 R2

texts: I am confused about Database mail profiles in SQL Server 2008 R2. When setting up severity notifications as well as "notify operator tasks" in maintenance plans, what determines what mail profile is used?


texts:
I'm adding a new column (bigints, initial values all null), to a large (~5 billion rows) Postgres (9.6) table.

I want to add an index on the row (algorithm: concurrently), and I'm wondering if there's a way I can save it the time of scanning through all five billion null fields to add them to the index, since I'm adding the index when the column is brand new and has no data. Is there any kind of optimization for this, or should I just let it run? (Is it even theoretically possible for there to be an optimization, or does it need to add something to the index even for null fields?)


texts:
Postgres’ docs note:

  While access to the data stored in a materialized view is often much faster than accessing the underlying tables directly or through a view, the data is not always current;

https://www.postgresql.org/docs/9.6/rules-materializedviews.html

Why is it “often much faster?”


texts: What happens when you delete a Clustered index and Non Clustered Index once created

texts:
What happens when we dropped a Cluster index and Non Clustered Index once they are created?

What will happen to the data at the leaf level according to Clustered Index ?


texts: Number of Field in CSV File does not match with number of field in External Table

texts:
Hi is there a way to reject a record with excess csv field.
For example:

CREATE TABLE Table1 (
    Field1    VARCHAR(255),
    Field2    VARCHAR(255)
    )
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  ACCESS PARAMETERS (
    records delimited by newline
    STRING SIZES ARE IN CHARACTERS
      logfile DIR_LOGS:'logile.log'
      badfile DIR_LOGS:'badfile.bad'
      discardfile DIR_LOGS:'dscfile.dsc'
    fields terminated by ',' optionally enclosed by '"'
    missing field values are NULL
    )
  LOCATION (DIR_CSV:'File.csv')
  )

My CSV is like

abc, def
ghi, hijk, lmnop
cat, dog

I want to reject/discard the 2nd record.


texts: Get partition column for a table in Greenplum

texts: How can I find what the partitioning scheme is for a table? Is it in information_schema or in the pg_class related tables?


texts: Oracle Client vs Oracle Instant Client on Linux!

texts:
to have the following setup on a Linux box:

SQLPlus
client library (oci.dll)!!!!
ORACLE_HOME is set for users

is Oracle Instant Client enough or I need to have the Oracle Client installed on the box.


texts: Survey database with different types of users

texts:
I'm designing a web app for managing mentor-mentee sessions. Mentees may be presented with different surveys with different types of questions. I would appreciate feedback/criticism on my design so far.

Surveys may come in different forms like a normal HTML form or a card sorting activity, or a table that the mentees have to fill.

Answers to questions may be in a yes/no, list form, a drawing, or plain text. Similarly, questions may be plain text, tables, fill in the blank etc. I created a content field for both question and answer in which I plan to store question/answer data in xml format, so if it's a table I can retrieve individual columns easily in the app logic. Is this a good idea?

I also created a separate logTimes table to track user logins and whether it was successful or not. A login may be from either a volunteer (mentor or supervisor) or a mentee, so one of idVolunteer or idMentee in this table will always be null. What do you think?


texts: Risks regarding simple recovery model and importance of log file backup

texts:
We have some databases on our server with 'Simple' recovery mode. We are using a third party application for the whole server backup every 30 minutes. We are also taking SQL server database backups twice a day (plan).
My question is

When we take SQL database backup with Simple recovery mode and restore it on a test server, is it correct that we have a blank log file on the test server but the latest database file?
In our current backup scenario, what is the benefit of Full recovery mode as we already have replication every 30 minutes and our RPO is agreed as 1 hour for every client?
I am not a DB admin and probably that is why I have never come across a situation where I had to use SQL server database log file. Whenever anything happened I have always restored the database to the last taken backup which is around 30 minutes old. Can anyone highlight if I need to improve on this point?

I'm just a learner and any guidance / advice is much appreciated.


texts: Mongodb 3.0.1 in ubuntu starts but status, stop or restart show 'mongo stop/waiting'

texts:
I am relatively new to Mongo and have installed 3.0.1 on a Digital Ocean droplet (Ubuntu 14.04 LTS). The database starts without problems but I am unable to 'sudo service mongod [stop/restart/status]' using the default apt-get installation. The process shows up in ps but status shows 'mongod stop/waiting'. Has anyone seen this before?

Just to clarify, the process is running fine but I am unable to use sudo service to check the status, stop, or restart the process. Currently, I am shutting mongo down manually from mongo shell. Here is a startup log

2015-03-30T08:25:31.066Z I CONTROL  ***** SERVER RESTARTED *****
2015-03-30T08:25:31.080Z I STORAGE  [initandlisten] wiredtiger_open config: create,cache_size=1G,session_max=20000,eviction=(threads_max=4),statistics=(fast),log=(enabled=true,archive=true,path=journal,compressor=snappy),checkpoint=(wait=60,log_size=2GB),statistics_log=(wait=0),
2015-03-30T08:25:31.163Z I CONTROL  [initandlisten] MongoDB starting : pid=6266 port=27017 dbpath=/var/lib/mongodb_wtiger 64-bit host=my.hostname.com
2015-03-30T08:25:31.164Z I CONTROL  [initandlisten] db version v3.0.1
2015-03-30T08:25:31.164Z I CONTROL  [initandlisten] git version: 534b5a3f9d10f00cd27737fbcd951032248b5952
2015-03-30T08:25:31.164Z I CONTROL  [initandlisten] OpenSSL version: OpenSSL 1.0.1f 6 Jan 2014
2015-03-30T08:25:31.164Z I CONTROL  [initandlisten] build info: Linux ip-10-167-176-91 3.13.0-24-generic #46-Ubuntu SMP Thu Apr 10 19:11:08 UTC 2014 x86_64 BOOST_LIB_VERSION=1_49
2015-03-30T08:25:31.164Z I CONTROL  [initandlisten] allocator: tcmalloc
2015-03-30T08:25:31.164Z I CONTROL  [initandlisten] options: { config: "/etc/mongod.conf", net: { bindIp: "127.0.0.1", port: 27017, unixDomainSocket: { enabled: true }, wireObjectCheck: false }, processManagement: { fork: true }, security: { authorization: "enabled" }, storage: { dbPath: "/var/lib/mongodb_wtiger", directoryPerDB: true, engine: "wiredTiger", journal: { enabled: true } }, systemLog: { destination: "file", logAppend: true, path: "/var/log/mongodb/mongod.log", timeStampFormat: "iso8601-utc" } }
2015-03-30T08:25:31.238Z I NETWORK  [initandlisten] waiting for connections on port 27017


texts: Is compression and shrinking a database in SQL Server same? If I compress a table, will it decrease query performance and DML?


texts: Rejoining range seek on nullable composite index?

texts:
For the following schema and example data

CREATE TABLE T
  (
     A INT NULL,
     B INT NOT NULL IDENTITY,
     C CHAR(8000) NULL,
     UNIQUE CLUSTERED (A, B)
  )

INSERT INTO T
            (A)
SELECT NULLIF(( ( ROW_NUMBER() OVER (ORDER BY @@SPID) - 1 ) / 1003 ), 0)
FROM   master..spt_values

An application is processing the rows from this table in clustered index order in 1,000 row chunks.

The first 1,000 rows are retrieved from the following query.

SELECT TOP 1000 *
FROM   T
ORDER  BY A, B

The final row of that set is below

+------+------+
|  A   |  B   |
+------+------+
| NULL | 1000 |
+------+------+

Is there any way to write a query that just seeks into that composite index key and then follows it along to retrieve the next chunk of 1000 rows?

/*Pseudo Syntax*/
SELECT TOP 1000 *
FROM   T
WHERE (A, B) is_ordered_after (@A, @B)
ORDER  BY A, B

The lowest number of reads I've managed to get so far is 1020 but the query seems far too convoluted. Is there a simpler way of equal or better efficiency? Perhaps one that manages to do it all in one range seek?

DECLARE @A INT = NULL, @B INT = 1000

;WITH UnProcessed
     AS (SELECT *
         FROM   T
         WHERE  ( EXISTS(SELECT A
                         INTERSECT
                         SELECT @A)
                  AND B > @B )
         UNION ALL
         SELECT *
         FROM   T
         WHERE @A IS NULL AND A IS NOT NULL
         UNION ALL
         SELECT *
         FROM   T
         WHERE A > @A
         )
SELECT TOP 1000 *
FROM   UnProcessed
ORDER  BY A,
          B

FWIW: If the column A is made NOT NULL and a sentinel value of -1 is used instead the equivalent execution plan certainly looks simpler

But the single seek operator in the plan still performs two seeks rather than collapsing it into a single contiguous range and the logical reads are much the same so I'm suspecting that maybe this is pretty much as good as it will get?


texts: mysql database update possible timing issue

texts:
I have a strange situation.
I have a cronjob running queue tasks one after the other. I have a delay of 1s between the tasks.

After a task is run, it sets processed=1, and continues with the next one, that has processed=0.

I have some situations where a task is executed twice. This means that processed=1 was set, but when we read again for processed=0, it finds it.

I use PHP, and ->execute(). Is it possible changes in the database not to be reflected immediately for some reasons?


texts:
I've a table

MESSAGES
id (long) | attach_data (image) | ins_date | flag

this table contains all messages of my application. During night, my job cleans old attach_data with query

update messages set attach_data=0x
where ins_date< DATEADD(DAY, -120, GETDATE())
and datalength(image)> 0

this is very long query (about 2 hours)

Other processes are always querying same table with

select id FROM MESSAGES WHERE Flag = 0 ORDER BY ins_date ASC

But during this 2 night hours, this query is not function (timeout expired).
There are a tricks?
I'm running Sql server 2005


texts: Why a backup created on SQL Server 2016 is significantly larger than one created on SQL Server 2012?

texts:
I've backed up a database on SQL Server 2012 that resulted in a file of 33MB in size. Then, I restored that backup on SQL Server 2016 and created another backup, but this time on SQL Server 2016.

The size of the backup changed from 33MB (SQL Server 2012) to 315MB (SQL Server 2016).

Why such big increase to the size of the backup file on the SQL Server 2016?

Appreciate any help.
Wesley


texts: Upgrading from SQL Server 2012 SP1 to SQL Server 2019

texts:
I'm upgrading an internal database at work, and we are creating a new VM to host it.

I can't see an option to generate a script to export the SQL Server 2012 SP1 database for server version SQL Server 2019. Would it be better for me to:

Follow the upgrade path for SQL Server 2012 to SQL Server 2019 on the current database server, and export the database to the new server when the upgrade is complete.

or

Export the database from SQL Server 2012 as is, and script it for the highest server version available (2017).

If I installed SQL Server 2012 SP4, would that give me an option to Script for Server Version SQL Server 2019?

Thanks for your help in advance.


texts: How to set the timeoffset in a Datetimeoffset column without changing the date part

texts:
Is there any way to set the timeoffset of a datetimeoffset column directly without having to use SWITCHOFFSET which changes the date as well?

In the post below, we can see the SWITCHOFFSET will change the date accordingly along with setting the timeoffset (which is not what we want):
http://blog.sqlauthority.com/2010/07/15/sql-server-datetime-function-switchoffset-example/

For example I have the date value as '2013-02-10' and want to record it in different timezones, without losing the '2013-02-10'. Just set the timeoffset to what needed , e.g: +11, -4, -6, etc.

Is it possible?

Thanks.


texts:
I asked a question earlier about how I could define a many to many relationship between users and FOSes (fields of study). My original question was how could we know whether the user was the instructor or the user was the student.

My original idea was two junction tables, one called user_instructor_fos and one called user_student_fos.
Another solution was to use a bit column inside a single join table called is_instructor. If the user was the instructor this value would be set to one.

I can see how both solutions would work I was just wondering what the pros and cons of each solution are and if there is a best practice.


texts:
I'm going back and forth on whether or not to have three tables, or one, with nullable columns.

In a business sense, I have 'Accounts'.
An account can be either a Bank Account, or a Third Party.

But they're all treated as accounts. I can pay money from one account, to another.

A Bank Account has fields like "Opening Balance", and "BankAccountType: {Savings, Credit, Debit}". As well as a few other specific columns.

A Third Party account has fields like "IsAPerson", for example, plus a load of other columns.

If I had one account table, I'd have a lot of nullable columns, as some fields are not applicable to both types.

I'm leaning towards an Account table, with common fields, a ThirdParty table, which has fields for Third Parties, and a BankAccount table, with it's specific fields.

Account would have an AccountTypeId.
To get all BankAcounts, it's an Inner Join (Account -> BankAccount)
To get all ThirdParties, it's an Inner Join (Account -> ThirdParty)

This feels cleaner. As far as data integrity goes, I COULD have an issue where a Account.AccountTypeId = ThirdParty, but a missing ThirdParty row, but I guess I just need to make sure that doesn't happen.

Which approach is better?
One table with nullable columns?
Or three tables?


texts: What's the meaning of "resources external to the SQL Server computer are needed" in Microsoft's SQL documentation?

texts:
In Microsoft's SQL Server documentation on Windows service account configuration, the decision to use either a VA (virtual account) or a MSA (managed service account) hinges on whether

resources external to the SQL Server computer are needed

What exactly does this phrase mean here? I'm seeking an explanation that makes sense to an 'accidental DBA' with minimal experience of SQL Server, or Windows Server, configuration. What counts as a 'resource' here, and what kinds of 'need' are relevant?
Context: I have a fresh SQL Server 2019 VM on Azure, which was configured with VAs out-of-the-box, and I'm trying to decide whether we need to switch to using MSAs. I have found multiple other questions concerning this same documentation page and/or the same basic decision between VAs or MSAs (or regular AD Accounts) - but none really explain this specific phrase in a way that helps me apply it to my particular scenario. Which is essentially a data warehouse use-case: data will be coming into this SQL instance from external sources, but that'll be managed by a third-party DW automation application running SSIS scripts, not directly by the SQL engine. (This application has its own AD service accounts.)
Whilst I've explained my specific scenario here to try to clarify the question, I'm keen for a generic answer so anyone with any SQL Server use case can evaluate this "resources external to the SQL Server" phrase for their needs. Specific answers for my use case are also welcome.


texts: How to map another server through SQL Server Management Studio

texts:
I am trying to map another server by giving the  command

EXEC xp_cmdshell
    'NET USE H:\\568.256.8.358\backup_147 1234abc /USER:cranew /PERSISTENT:yes'

I got an error with this:

  network path not found

But I am able to map another server manually. Please help me in sorting this out.


texts:
How to find whether the port used by SQL Server is static or dynamic using Get-DbaTcpPort dbatools command?

Below command doesn't show whether it's static/dynamic:

Get-DbaTcpPort -sqlinstance TEST

And below one shows too many rows:

 Get-DbaTcpPort -sqlinstance TEST -ALL


texts: postgresql: PANIC: cannot operate with inconsistent data

texts:
I'm trying to setup a replica using repmgr:

repmgr -D /var/lib/postgresql/9.3/main -p 5432 -U repmgr -R postgres \
   --verbose standby clone psql.master.example.com
repmgr --verbose standby register

I've manage to sync the DB's but the standby replica won't start:

postgres@psql01a:~$ /usr/lib/postgresql/9.3/bin/postgres --single -D /var/lib/postgresql/9.3/main -P -d 1
2016-04-18 14:02:05 UTC [30048]: [1-1] user=,db=,client= LOG:  database system was shut down in recovery at 2016-04-18 14:00:51 UTC
2016-04-18 14:02:05 UTC [30048]: [2-1] user=,db=,client= LOG:  entering standby mode
2016-04-18 14:02:05 UTC [30048]: [3-1] user=,db=,client= DEBUG:  checkpoint record is at 27B5/BA68B550
2016-04-18 14:02:05 UTC [30048]: [4-1] user=,db=,client= DEBUG:  redo record is at 27B5/B3626B20; shutdown FALSE
2016-04-18 14:02:05 UTC [30048]: [5-1] user=,db=,client= DEBUG:  next transaction ID: 0/2281005353; next OID: 230242292
2016-04-18 14:02:05 UTC [30048]: [6-1] user=,db=,client= DEBUG:  next MultiXactId: 879585; next MultiXactOffset: 1823275
2016-04-18 14:02:05 UTC [30048]: [7-1] user=,db=,client= DEBUG:  oldest unfrozen transaction ID: 2094018845, in database 134461654
2016-04-18 14:02:05 UTC [30048]: [8-1] user=,db=,client= DEBUG:  oldest MultiXactId: 1, in database 16546
2016-04-18 14:02:05 UTC [30048]: [9-1] user=,db=,client= DEBUG:  transaction ID wrap limit is 4241502492, limited by database with OID 134461654
2016-04-18 14:02:05 UTC [30048]: [10-1] user=,db=,client= DEBUG:  MultiXactId wrap limit is 2147483648, limited by database with OID 16546
2016-04-18 14:02:05 UTC [30048]: [11-1] user=,db=,client= DEBUG:  resetting unlogged relations: cleanup 1 init 0
2016-04-18 14:02:05 UTC [30048]: [12-1] user=,db=,client= DEBUG:  initializing for hot standby
2016-04-18 14:02:05 UTC [30048]: [13-1] user=,db=,client= LOG:  redo starts at 27B5/B3626B20
2016-04-18 14:02:05 UTC [30048]: [14-1] user=,db=,client= DEBUG:  recovery snapshots are now enabled
2016-04-18 14:02:05 UTC [30048]: [15-1] user=,db=,client= CONTEXT:  xlog redo running xacts: nextXid 2281009749 latestCompletedXid 2281009746 oldestRunningXid 2281009747; 2 xacts: 2281009748 2281009747
2016-04-18 14:02:05 UTC [30048]: [16-1] user=,db=,client= PANIC:  btree_xlog_delete_get_latestRemovedXid: cannot operate with inconsistent data
2016-04-18 14:02:05 UTC [30048]: [17-1] user=,db=,client= CONTEXT:  xlog redo delete: index 1663/16546/215742765; iblk 363218, heap 1663/16546/215740352;
Aborted

Any idea how to start the replica?


texts: I just installed SQL Server Developer Edition and SQL Server Management Studio, how can I configure the server? I want to configure it to have server name localhost-sql-server, with a login/password. I found the configuration manager but not sure where I can modify this.


texts: Choosing between RCSI and Snapshot Isolation

texts:
I have researched into this and I understand the general differences between the two isolation levels. I would like to explain our scenario and need help in deciding the correct isolation level to use.

We were using the default behavior of SQL and started experiencing some locking behavior especially when the volumes of data increased and some of the queries started taking a bit of time to load. There were other factors that made the problem worse which we have addressed already.

In the meantime we enabled SNAPSHOT ISOLATION LEVEL on our database and changed ONLY the select queries to use this ISOLATION LEVEL i.e. when a query is run we just want to get the last committed snapshot of the data. These are mainly queries that are used to display the data in grids.

I have a few questions that I would like some clarification on please:

To achieve what I am trying to achieve is enabling isolation snapshot the best option or are there other options worth considering?
Since I have enables isolation snapshot but I do not run this in the queries where I update / insert / delete records my assumption here is that in these queries it will use the default behavior of SQL (Read Committed). Is my understanding correct?
I have read that snapshot isolation level utilities the tempdb to store versions. Would this be the case even when I am running selects?
What can we do from a performance point of view to optimize our databases that have isolation snapshot enabled?


texts: Recommendations for MS SQL Server monitoring software or service?

texts:
I have a client with MS SQL Server instances (2000, 2005) scattered across 20-30 servers. I've been engaged to get things organized and will be periodically reviewing the state of the database infrastructure for health, testing backups, etc. We also plan to get as many of these databases up to 2008 as possible.

I'm aware of services like New Relic for Linux stack web apps and databases, and I stumbled onto the SQL Monitor product (RedGate) just today via Google. Can anyone provide some direction in terms of services or tools (that you have experience with) that would enable me/my client to monitor and manage their MS SQL instances?


texts: Downloading image data type contain to desktop SQL Server 2008

texts:
I am very new to SQL Server 2008 and question may look very basic to gurus.

Following in the problem universe.

I have a huge (600 GB size) SQL Server 2008 table that stores pdf files in a column of type Image
Need is to download all the images from database table to my desktop (not on to the server).
I can't run C#, ASP.NET etc in the environment and has to rely on SQL Server Management Studio for the job (I see a option called power shell but not sure how to use it)

Table looks like following

Table name : Attachments

Columns :

FileID (PK, int, not null)
Owner (int, null)
file_name (varchar(50), null)
file_type (varchar(50), null)
file_size (int, null)
file_date (datetime, null)
file_BIN (image, null)


texts:
I have over 10,000 geojson rows in a table. How do I go about selecting only distinct coordinates?

The data was imported from a csv file into PostgreSQL 9.5. The columns in the table are:

Trip Duration (seconds)
Start Time and Date
Stop Time and Date
Start Station Name
End Station Name
Station ID
Station Lat/Long

The imported contains I took the latitude and longitude columns and turned it into a geojson object:

update h set thestartgeom =  st_asgeojson(startstationgeom);

The column thestartgeom in the table is

  thestartgeom text;

The database contains 10,000+ lat/lng and other records. Some of the coordinates are duplicated. I am trying to eliminate all duplicates.

startstationgeom is a geometry(POINT,4326).

Here is the geojson column:

                    thestartgeom
---------------------------------------------------------------------

{"type":"Point","coordinates":[40.6882247924805,-73.9793853759766]}
{"type":"Point","coordinates":[40.7329139709473,-74.0071105957031]}
{"type":"Point","coordinates":[40.7429084777832,-73.9770584106445]}
{"type":"Point","coordinates":[40.7160568237305,-73.9919052124023]}
{"type":"Point","coordinates":[40.7497177124023,-74.0029525756836]}
{"type":"Point","coordinates":[40.7349281311035,-73.9920043945312]}
{"type":"Point","coordinates":[40.7281455993652,-73.9902114868164]}
{"type":"Point","coordinates":[40.691650390625,-73.9999771118164]}
{"type":"Point","coordinates":[40.7501983642578,-73.9909286499023]}
{"type":"Point","coordinates":[40.7503814697266,-73.9833908081055]}


texts: PostgreSQL - automatically create a database for users on first login

texts:
Is there a way to configure PostgreSQL to create a database whose name is the same as the username on the user's first login?

Scenario:

In pg_hba.conf there will be a rule something like:

local sameuser all ident sameuser

When a user connects to PostgreSQL via console/psql,

If a database with the users name exists, just connect to database.
If it does not exist, create a database named after the user and connect the user to the newly created database.

There are a lot of users and I do not want to manually create these databases all at once, upfront.


texts: error 1045(28000):anonymous user problem


texts:
I've been tasked to build a somewhat basic Prisoner database and I've run into a situation where I don't know what the appropriate route to take is.

I have a Prisoner table, that looks like the following:

  Primary Key                 attributes
 {prisoner_id, person_ssn} -> offense_id, cell_block_id, etc.

I have a Person table and the Primary key is {person_ssn}

 {person_ssn} -> first_name, last_name, dob, etc.

Now I am a bit confused on whether person_ssn in the Prisoner table needs to be a foreign key or not, and why. Because it's a part of the primary key, does that rule it out of being a foreign key?


texts: Configure HAProxy to load balance Postgresql

texts:
I'm trying to load balance between servers running Postgresql (Master-Slave configuration with Streaming Replication). First I've tried setting up PgPool-II, and it worked fine when I connected some app we're using here, but we use Tableau (some tool for reporting) and Tableau can't connect correctly, so now I'm searching for alternatives to load balance, and I've read in many places that HAProxy is an good alternative to load balance, but can't find a good guide to configure it to function with Postgres.

Following some articles I think I managed to configure something very basic, I managed to start HAProxy running in port 10001, but when I try to connect via psql, I get the error:

psql: The server has closed the connection unexpectedly

(I translated it from Spanish, so may not be the exact message in English).

I get that error immediately, so I'm not sure if it's something in the server running HAProxy or in the server running Postgresql.

At the moment, for testing, I'm running HAProxy in one server and configured to only connect to one server.

My configuration in the HAProxy server is like this:

global
    log                 127.0.0.1 local2
    maxconn             4000

defaults
    mode                tcp
    log                 global
    option              dontlognull
    retries             3
    timeout connect     1m
    timeout client      1m
    timeout server      1m
    maxconn             3000

 listen psql_cluster :10001
    mode tcp
    server psql1 [ip_server_postgres]:5432

Both servers are running Centos 7, I'm running HAProxy 1.5.15 and Postgresql 9.4.

For the record, the communication between the server HAProxy and the master with Postgres works fine with user Postgres...

PS: While writing this, I thought that may be because the new user haproxy can't connect to the master server? Should I be able to ssh from one to another?. If I try "su haproxy" it says "This account is currently not available". Maybe is one of this things?

EDIT
I found in the logs of the HAProxy Server that indeed my connection is getting received by the server:

psql_cluster postgres/psql1 1/-1/0 0 SC 0/0/0/0/3 0/0


texts: Avoid circular reference with configuration tables

texts:
I've got a few tables that define configuration information.  This defines what combination are allowed in the system.

However, I've got a circular reference on ProjectName which can cause potential invalid relationships.  I'm wondering how I can enforce this better in the database.  I don't want to trust the code to enforce it.

Table Schema (Table / Columns)

Project
    ProjectName

Category
    CategoryName

ProjectCategory
    ProjectCategoryID
    ProjectName
    CategoryName

Inventory
    ProjectName
    ProjectCategoryID

Populated Tables (Table/Column/Data)

Project
    ProjectName
        A
        B

Category
    CategoryName
        X
        Y

ProjectCategory
    ProjectCategoryID, ProjectName, ProjectCategory
        1, A, X
        2, A, Y
        3, B, Y

Inventory
    ProjectName, ProjectCategoryID
        A, 1
        A, 3

Inventory record (A, 3) is invalid.  The inventory belongs to project 'A' yet the configuration belongs to project 'B'.  Because of the circular reference, its possible to associate a record incorrectly.


texts: Trying to understand an RCSI example-The Potential Dangers of the Read Committed Snapshot Isolation Level

texts:
There's this article The Potential Dangers of the Read Committed Snapshot Isolation Level which demonstrates RC vs RCSI isolation levels.
I get the RC example but not the RCSI one.In particular how the query has gotten the result -3.
Can somone explain how this query works under RCSI ?


texts: Redo log files are not multiplexed in our database. Redo log blocks are corrupted in group 2, and archiving has stopped. All the redo logs are filled and database activity is halted. Database writer has written everything to disk. Which command would execute to proceed further?


texts: revoke and apply privileges on current active sessions

texts:
I am revoking some privileges to a user like this:

REVOKE INSERT, UPDATE, CREATE, ALTER ON `name`.* FROM 'user'@'%'

I verify via:

SHOW GRANTS FOR 'user'@'%';

And see that privileges really have removed, but the problem I am now trying to solve is that even after flushing the privileges FLUSH PRIVILEGES;, if the user still logged in, the user can do inserts therefore wondering how could I force the new permissions to users with a current session open.

If the user reconnects the permissions take place, I can not kill the connection since I have only access remotely, any alternatives, suggestions?


texts:
I am using postgres 9.5 and have built the following function:

CREATE or REPLACE FUNCTION func_getratio_laglag(_numeratorLAG text, _n1 int, _denominatorLAG text, _n2 int, _table text)
    RETURNS TABLE (date_t timestamp without time zone, customer_code text, index text, ratio real) AS
$BODY$
BEGIN
 RETURN QUERY EXECUTE
        'SELECT
    date_t,
    customer_code,
    index,
        (LAG('||quote_ident(_numeratorLAG)||',' || quote_literal(_n1)||') OVER W / LAG('||quote_ident(_denominatorLAG)||','|| quote_literal(_n2)||') OVER W) '
         || ' FROM ' || quote_ident(_table)
         || ' WINDOW W AS (PARTITION BY customer_code ORDER BY date_t asc);';
END;
$BODY$ LANGUAGE plpgsql;

All the function does is allow me the ability to pick a 2 different columns from a specified table and calculate a ratio between them based on different lag windows. To execute the function above I use the following query:

Select * FROM func_getratio_laglag('order_first',1,'order_last',0,'customers_hist');

The output of this gives me a table with the column labels date_t, customer_code, index and ratio. I have really struggled on how to output ratio as a dynamic column label. That is, I would like to make it contingent on the input parameters e.g. if I ran the select query above then I would like the column labels date_t, customer_code, index and order_first_1_order_last_0.

I am stuck, any advice or hints?


texts: Azure SQL Database - Error Starting Extended Events Session

texts:
I'm using workloadtools to try and capture a production extended events "replay trace" and have hit a problem. The tool is creating the extended events session but is unable to start it for one database (it works fine on another smaller db on the same server). I get this error message:

Operation failed. Operation will cause database event session memory to exceed allowed limit. Event session memory may be released by stopping active sessions or altering session memory options. Check sys.dm_xe_database_sessions for active sessions that can be stopped or altered. If no sessions are active on this database, please check sessions running on other databases under the same logical server. (Microsoft SQL Server, Error: 25747)

The tool uses a template file to define the extended events - see below, I've tried editing the max_memory value both up and down but without success.
max_memory in sys.dm_user_db_resource_governance shows 100 for the db in question, docs say this is a percentage. Looking at that dmv points out to me that the 2 dbs in question have different configurations and are actually in different elastic pools (although on the same server)
These values also seemed pertinent:

max_memory_grant 13
max_db_memory 26787738

Has anyone got a suggestion for what to try next?
CREATE EVENT SESSION [sqlworkload] ON {1}
ADD EVENT sqlserver.attention (
    ACTION(
        package0.event_sequence,
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.database_id,
        sqlserver.database_name,
        sqlserver.{2},
        sqlserver.session_id,
        sqlserver.sql_text
    )
    {0}
),
ADD EVENT sqlserver.rpc_completed (
    SET collect_data_stream = (0),
    collect_output_parameters = (1),
    collect_statement = (1)
    ACTION(
        package0.event_sequence,
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.database_id,
        sqlserver.database_name,
        sqlserver.{2},
        sqlserver.session_id
    )
    {0}
),
ADD EVENT sqlserver.sql_batch_completed (
    SET collect_batch_text = (1)
    ACTION(
        package0.event_sequence,
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.database_id,
        sqlserver.database_name,
        sqlserver.{2},
        sqlserver.session_id
    )
    {0}
),
ADD EVENT sqlserver.user_event(
    ACTION(
        package0.event_sequence,
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.database_id,
        sqlserver.database_name,
        sqlserver.{2},
        sqlserver.session_id
    )
    WHERE [sqlserver].[like_i_sql_unicode_string]([user_info],N'WorkloadTools%')
)
WITH (
    MAX_MEMORY = 4960 KB,
    EVENT_RETENTION_MODE = NO_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    MAX_EVENT_SIZE = 0 KB,
    MEMORY_PARTITION_MODE = PER_CPU,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = OFF
);

ALTER EVENT SESSION [sqlworkload] ON {1} STATE = START;


texts: How to Store text of 15 to 20 KB file data in blob or as text?

texts: How to Store text of 15 to 20 KB file data in blob or as varchar in sql Server?


texts:
I want to backup my database except t1 table's one record (where option='siteurl').

I'm trying mysqldump's --where command.

mysqldump -u root --skip-extended-insert --where "option='siteurl'" konsertvdb t1 > sql.sql

This will work if I backup only the t1 table. But I want to backup all tables, except t1's one record (where option='siteurl') .

> mysqldump -u root --skip-extended-insert --where "option='siteurl'" konsertvdb > sql.sql
mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `wp_commentmeta` WHERE option='siteurl'': Unknown column 'option' in 'where clause' (1054)

*(wp_commentmeta is one of my other tables.)

Can you tell me is it possible?


texts: Create a materialized view on commit with PIVOT function

texts:
I'm trying to create a materialized view with on commit refresh while using a PIVOT on it's select.

I have the following tables:

Events: represents an event that the system process. It's like a representation of a job.

Columns: Id, Type (number)

EventsMessages: messages of the event. 1:N (1 event can have N messages). It has a type column defining its message type: critical, alert, notification, success.

Columns: Id, EventId (FK), Message (text)

EventContainers: containers that have events, so other entities have containers and not a list of event. N:N (1 container han have N event; 1 event is on N containers), the relationship between them is named as EventContainersRelation table.

Columns for the relation table: EventId (FK), ContainerId (FK). Table EventContainers is not needed in this example.

The code is like this:

CREATE MATERIALIZED VIEW test_mv
BUILD IMMEDIATE
REFRESH FORCE
ON COMMIT
AS
SELECT DISTINCT *
FROM
(
    SELECT EC.ContainerId AS Id, EM.Type
    FROM EventMessages EM
    INNER JOIN Events E ON E.Id = EM.EventId
    INNER JOIN EventContainersRelation EC ON EC.EventId = E.Id
    WHERE E.Estado IN (1, 4)
)
PIVOT
(
    COUNT(Type)
    for tipo in (1 AS CriticalMsgs, 2 AS AlertMsgs, 4 AS NotificationMsgs, 8 AS SuccessMsgs)
)

When I run this, I get the following error:

  ORA-12054: não é possível definir o atributo de atualização ON COMMIT
  para a view materializada
  12054. 00000 -  "cannot set the ON COMMIT refresh attribute for the materialized view"
  *Cause:    The materialized view did not satisfy conditions for refresh at
             commit time.
  *Action:   Specify only valid options.

The same code works if I use ON DEMAND instead of ON COMMIT.

I've already checked the restrictions on materialized views on https://docs.oracle.com/cd/B28359_01/server.111/b28313/basicmv.htm and I'm not sure what I'm doing wrong.
Is there anyway to see a more detailed message?
Is there something I'm doing wrong?

Considering I really can't use ON COMMIT here, so while using ON DEMAND, is it a bad thing to make it refreshes everytime it gets inserted, updated or deleted manually in my procedures? (Note that is the almost the same as ON COMMIT, but manually).

Is there another recommended way to achieve this?


texts:
I have a user who has SQL Server State Permissions so he can monitor his databases and processes.

However, there are others that are not the users dbs

When a sql agent job failes on the other db's he gets notified.

Can I stop this and if so, how?

Much appreciated

Thanks Taz


texts: Multi master mysql with automerge data if node disconnected

texts:
I have final project for my course about distributed system, so here the case :

  A company need 3 database server ( example : database a,b,c ) who
  works together. So if someone insert a data to database a , the other
  database ( b and c ) will have that data too. But when network
  disconnected, the database server will work like standalone server.
  But when network connected again, all data that inserted during
  network disconnected, will distributed to other database. Example :
  The database a disconnected to other database, and someone insert a
  data  to database a. And after database a connected to other database,
  other database will have that data again.

I try with galera, but it will fail to insert data when network disconnected.


texts:
In in the PostgreSQL source code there is a function called PQgetvalue that returns a valid text representation of the value that can be used as a constant expression in SQL code.

It essentially creates a literal for any value supported by PostgreSQL.

Is there any such function that is accessible from within the PostgreSQL DBMS itself?

This is an example of what I want:

select PQgetvalue(1); -- 1

select PQgetvalue('cats' || ' dogs'); -- 'cats dogs'

select PQgetvalue('1990-01-01'); -- '1990-01-01'


texts: Installing scott schema for oracle 11g express

texts:
I want to install scott schema, a sample database in oracle.

But in express edition, I can't find out it.

Please help me install it on Oracle 11g express.

I'm a newbie, pls help me


texts: I have a year's worth of inventory data, and I want to extract the first and last day of each month's data. What is the best way to do this? Thanks


texts: Use system timestamp comparison in SELECT query

texts:
I have to backup some tables on a daily basis using the condition that the backup will be only for one previous day. I am planning to use copy to dump the records in to a csv file. I need help in getting my query correct, below is the query I am using but does not seem to work:

psql -d my_db -c 'copy (select * from mytab WHERE date_trunc('day',NOW() - interval '1 day') TO STDOUT' -o data1.copy;


texts: Can this SQL cause unclosed quotation mark?

texts:
I've been tracking a bug in how single quotes/apostrohpes are handled in an application, and I've found a suspect in a database trigger.

That trigger contains code like this: (shortened and anonymized)

insert into logTable ([NewRowData])
select
  (select isnull(convert(nvarchar(max), [Foo]),'NULL') as Foo
   from inserted i where  i.MyID = u.PrsID for xml raw('NewRowData'))
from @updated_row u

In production, I get errors when "Foo" contains a single quote, (For example "O'Hara")

  Incorrect syntax near 'Hara'. Unclosed quotation mark after the
  character string ',NULL)'.

However, I cannot replicate this. A manual insert/update that fires the trigger does not give an error.

Is is possible that the nested functions isnull and convert somehow messes up quotation escapes or am I looking in the wrong direction?

(Cannot find any dynamic SQL genereation anywhere, which naturally was my first suspicion.)

Database is SQL server 2012.

Addendum:
I did a similar test to what Vérace posted below before posting here with the same result i.e no exception. If there IS a bug, it must be dependant on database version, settings or query execution plan.


texts:
I am thinking about making a music database. Something where you can search by
artist, album or song. This seems to be a Many-to-many model, as an album can
have multiple songs, and the same song can be on different albums.
Currently I am just using some JSON files, but this isnt really robust, as it
can only represent hierarchical data.
This would just be a personal project, so I dont need anything too involved. I
have looked at SQLite, but also Document-oriented databases. I like the idea of
being able to persist the data as JSON or CSV files. What is a good option for
my use case?

Response to some questions:

Do you want a free solution or need to use an enterprise level system like
Microsoft SQL Server which costs money.

Free options only

How far do you expect to scale your database?...in other words, in the
foreseeable future roughly how big do you expect it to get (data-wise), how
many users do you expect will be using it both overall and at a given instance
of time, how often will data change inside of it?

Most likely it would be a tool that only has one user at a time. So either I
would use it myself, or someone might download my tool and use it for
themselves.

What does your current technology stack look like?

I would prefer something with of of these if possible: C#, D, Go, Nim or Rust.
Although I have seen many options in C and C++, so I might have to end up with
one of those.

I don't think any modern solution out there persists the underlying data of
the database in JSON or CSV files (though I could be mistaken).

I think Document-oriented database offer this, but I could be
misunderstaning it. Also I had seen Export to CSV for SQLite.


texts:
When I try to run the command :

"C:\Program Files (x86)\MySQL\MySQL Server 5.6\bin\mysqldump.exe" -u root -p******* my_database > backups/my_database_backup_20151221121320.sql

it says

mysqldump: unknown option '--no-beep'

(I'm on windows.)

What I have tried ?

I read and tried many results in google (and here) about moving the "no-beep" away from the [client] section in my.ini . Did not help. still the same error.
I tried to check if the my.ini file that I change, is the file that actually loaded - and it is, I can change other settings and they are applied.
I tried running the command without the full path (Only mysqldump -u..), still the same error.
I searched for all the instances of "beep" in the my.ini file
also, when I type mysql --help it always shows no-beep = TRUE , no matter if I comment or uncomment the "no-beep"

EDIT :

using --no-defaults I have no problem, but I don't want to change the command.

Some days ago my php script worked fine, I don't know what happened.


texts:
Say I have 10 columns in Employee table where i just need to use two colums i.e. salary and name in below query

 select salary from Employee where name = 'Scott';

After going through Video from 6.30 to 8.30(2 mins), My understanding is under RDBMS database(say oracle/Mysql etc) even i have to read two columns DBMS will read complete row from disc in memory then it will filter rows based on name coumn and finally read column salary. Is that correct  ?


texts: Merge replication subscriptions are repeating the same actions endlessly

texts:
We have a merge publication set up on SQL Server 2008R2, with several subscribers. Synchronisation is done through the internet.
Over the last few days, some subscribers are unable to synchronise.
In case it's relevant: This seems to have begun on a day where the disk containing the database's log files had filled up completely, and as a result no subscribers could sync.
I resolved that by shrinking the log file for this database, and of other databases on the same server.

There is no error message; synchronisation can go on for several hours (usually it takes up to 30 minutes; an hour in rare cases) until it times out, runs into a connection issue or has its process killed.

Reinitialising a subscriber (or initialising a new one), either with the existing snapshot or a new one produces the same result.

Other subscribers seem to work as normal. There is no difference between those that work and those that don't that I've been able to pinpoint so far.

The replication monitor on the publisher side simply shows the same few messages repeating endlessly:

Retrieving subscription information.

Enumerating changes in filtered articles using Subscriber's assigned partition ID.
Downloading data changes to the Subscriber
Merge completed with no data changes processed.

*There might be some changes processed, depending on subscriber, but the numbers don't seem to go up.
Messages generated at the subscriber are also repeating:

The upload message to be sent to Publisher 'PUBLISHER' is being generated

The merge process is using Exchange ID '2EE5080F-2F28-4EA6-A0FA-7DA11F97B945' for this web synchronization session.
Uploading data changes to the Publisher
No data needed to be merged.
Request message generated, now making it ready for upload.
Upload request size is 7026 bytes.
Uploaded a total of 1 chunks.
The request message was sent to 'https://WEB_SYNC_SERVER/replisapi.dll'
Downloaded a total of 7 chunks.
The response message was received from 'https://WEB_SYNC_SERVER/replisapi.dll' and is being processed.
The changes contained in the message downloaded from Publisher 'PUBLISHER' will be applied after having collected and displayed the upload statistics.
Downloading data changes to the Subscriber
Web synchronization progress: 97% complete.

I upped the log verbosity, and the sync log on the subscriber also shows repeating parts. This is quite big, so I put it in pastebin: http://pastebin.com/GV2N0Lys
(This shows only one instance of the repeating part)
I've been trying to find similar reports for a day and I'm unable to find anything relevant.
Is there anything I'm missing?


texts:
I need to find the data that is present in Oracle, but missing from SQL Server.

I have tried the below query, but am getting the following error:

  The multi-part identifier "PS.TXN_ID" could not be bound.

SELECT TXN_ID
FROM OPENQUERY(PEX,'SELECT TXN_ID FROM PEX.PEX_SALE_TRAN_CUBE ')
WHERE NOT EXISTS (SELECT TXN_ID
                  FROM  PA_TRANSACTIONS PT
                  WHERE PT.TXN_ID = PS.TXN_ID)

Can someone help out please?


texts: How to insert data into tables which reference each other?(MySQL)

texts:
I'm a student and right now I have a problem that I couldn't solve by myself(after searching on the Internet).
I'm not going to give my «homework» because I want to learn what I'm doing but I will use an example.
I have the following diagram:

And the following:
Employee(ID,DepartamentID,Floor)
foreign key(DepartamentID, Floor) references Departament(DepartamentID,Floor)
Departament(DepartamentID,Floor, Attendant)
foreign key Attendant references Employee(ID)

So with this guidelines, my actual SQL code is:
CREATE TABLE Employee(
    ID varchar(25) not null,
    DepartamentID varchar(25),
    Floor varchar(25),
    CONSTRAINT pk_ID PRIMARY KEY (ID),
) ENGINE=InnoDB;

CREATE TABLE Departament(
    DepartamentID varchar(25) not null,
    Floor varchar(25) not null,
    Attendant varchar(25) not null,
    CONSTRAINT pk_dept PRIMARY KEY (DepartamentID, Floor),
    CONSTRAINT fk_att FOREIGN KEY (Attendant) REFERENCES Employee (ID),
) ENGINE=InnoDB;

ALTER TABLE Employee
ADD CONSTRAINT fk_dept FOREIGN KEY (DepartamentID, Floor) REFERENCES Departament (DepartamentID, Floor);

I think, this way, I accomplish what I have to accomplish as the information given says, but when I try to insert data like:
INSERT INTO Employee (ID, DepartamentID,Floor) VALUES ('123456789-Z', 'IT', 'roof');

or
INSERT INTO Departament (DepartamentID, Floor, Attendant) VALUES ('IT', 'roof', '123456789-Z');

I get the following message:
1452 - Cannot add or update a child row: a foreign key constraint fails
After searching and searching, I realized that is not possible to insert data into a table that expects data from another table and that another table is empty.
I thought that I could do the alter table before the inserts but the teacher wants a create.sql file and a inserts.sql file, separately, so I cannot do it this way.
I need to find a solution to accomplish this and stackexchange is my last hope.
Thanks to anyone that read this, thank you very much.
PD: Sorry if the question is too long, I tried to explain it the best I can(english is not my native language) but if I have to explain something again, sure I will!


texts: How does ORDER BY FIELD() in MySQL work internally

texts:
I understand how ORDER BY clause works and how the FIELD() function works.
What i want to understand is how the both of them work together to sort.
How are the rows retrieved and how is the sort order derived

+----+---------+
| id |  name   |
+----+---------+
|  1 | stan    |
|  2 | kyle    |
|  3 | kenny   |
|  4 | cartman |
+----+---------+

SELECT * FROM mytable WHERE id IN (3,2,1,4) ORDER BY FIELD(id,3,2,1,4)

The query above will result in

+----+---------+
| id |  name   |
+----+---------+
|  3 | kenny   |
|  2 | kyle    |
|  1 | stan    |
|  4 | cartman |
+----+---------+

something similar to saying ORDER BY 3, 2, 1, 4

QUESTIONS

How does this work internally?
How does MySQL get the rows, and calculate the sort order ?
How does MySQL know it has to sort by the id column ?


texts: Postgres dumping specific non-table objects

texts:
I find myself needing to migrate a specific application from one host to another. That application touches a subset of tables within a certain PostgreSQL database; in order to migrate properly, I need to dump all the data from some of these, as well as the schema only from others, while ignoring some other tables in the same database.

The obvious solution is to use pg_dump with the right combination of -t and --exclude-table-data. However, when I do this, certain functions and triggers on the tables listed do not get dumped. These are necessary for the application to work, so I need them in the output as well.

I have not been able to find a way to dump triggers and functions by name. Passing their names to -t does not produce any output. Meanwhile, doing a whole-database dump does indeed dump the triggers and functions; however, this includes the extraneous tables in the same database (used by other applications), which I don't want to bring along.

I could run a whole-database dump with -T options set to exclude all the tables I don't want. However, this would require me to update the dump script every time anyone adds a new table, which would be somewhat inconvenient. I would prefer to only change the script when I update the schemas for my application. I could also run the whole dump and post-process the output to include only the tables I want, but this would require me to basically parse SQL on my own, which I'd rather not.

Is there a way using pg_dump to include specific triggers and functions in the dump, by name? Alternately, can I tell it to include objects that are dependencies of the tables I dump, or similar?


texts:
I am a (poor, young) scientist working to develop a database to allow a project to scale up. I am experimenting with MySQL and have had very encouraging experiences so far, but I am still novice and am coming at this from perhaps an unusual perspective.

Below is a diagram of the relevant parts of my schema, as I have it modelled right now. The fields are essentially place holders at this point, but in general I would like to leave room for documentation of just about every unique element in the database (except probably not for jobs, but I digress). There are a couple of qualities that I am shooting for:

Valid parameters are algorithm dependent, and we may add new algorithms at any time. I'd like to be able to register a new algorithm, and associate it with particular parameters.
Because new algorithms may require new parameters, I should be able to register new parameters as well.
A particular experiment will use one algorithm. Jobs are unique parametrizations of an experiment/algorithm. Through the relation of an experiment and an algorithm, I can validate the parameters submitted along with a particular job.

My point of confusion, given this schema, is how to store the values of the parameters for a particular job. Right now, as a place holder, I have a values column in the table job_has_parameter, but this is not going to work. Some parameters are integers, others doubles, still others character strings. Some algorithms may even take lists as parameters... but I'm willing to punt on that for now. At any rate, a single value column is not going to work.

Does this schema make sense at all? If it does make sense, generally speaking, what would be a reasonable way to handle storing the parameter values? I welcome criticism; I am under no delusion that I am on the right track here, but it's an effort.

Edit: I think that this schema is inadvertently adopting an entity-attribute-value style. I read that this is a bad thing. https://www.simple-talk.com/sql/t-sql-programming/avoiding-the-eav-of-destruction/ Perhaps someone can help set me on a better path?


texts:
I have a function in SQL Server 2019 that copies data from a local table to an Oracle 11.2.0.4 table via Linked Server, using Oracle 19.3 client. Recently, a new column was added to the Oracle table, so I've updated the insert function to include this new column.
Now, whenever I modify the function, it will execute successfully one time and one time only. If I purge the query cache, it also executes once and then starts throwing the error from then on. ALL execution attempts after the first time throw this error:

Msg 7353, Level 16, State 1, Procedure COD.Interface_Upload, Line
29 [Batch Start Line 20] The OLE DB provider "OraOLEDB.Oracle" for
linked server "ORADEV-LINK" supplied inconsistent metadata. An extra
column was supplied during execution that was not found at compile
time.

The only change to this procedure from the previous, working, version is adding the DATE_SUBMITTED field and the "select min([Sample_date])  ..." part to supply that column.
Everything I can find says that if you're getting metadata errors, go to the OPENQUERY syntax, which is what I'm using.
The function is:
ALTER Procedure [COD].[Interface_Upload]
  As
    SET NOCOUNT ON;

    INSERT OPENQUERY([ORADEV-LINK], 'SELECT ACCOUNT_NUMBER, FROM_DATE, TO_DATE,
            COD_TO_BOD_FACTOR, CONSUMPTION, SAMPLE_DATE, COD_CONTENT, SS_CONTENT,
            DATE_SUBMITTED from cbd.WS_CBD_PACS')
        Select [Account_Number]
            ,[From_Date]        ,[To_Date]
            ,[Factor]           ,[Consumption]
            ,[Sample_date]      ,[COD]
            ,[SS]
            , ( select min([Sample_date])
                from dbo.ORA_Interface
                where Account_Number = p.Account_Number )
        from dbo.ORA_Interface p ;

    Return @@rowcount;
GO

The Oracle destination table definition is:
                         Null?    Type
 ----------------------- -------- -------
 ACCOUNT_NUMBER          NOT NULL NUMBER(13)
 FROM_DATE               NOT NULL DATE
 TO_DATE                 NOT NULL DATE
 COD_TO_BOD_FACTOR       NOT NULL NUMBER(6,2)
 CONSUMPTION             NOT NULL NUMBER(15)
 SAMPLE_DATE             NOT NULL DATE
 COD_CONTENT             NOT NULL NUMBER(10)
 SS_CONTENT              NOT NULL NUMBER(10)
 DATE_SUBMITTED                   DATE

The SQL Server source table definition is:
[Account_Number] [varchar](30) NULL,
[From_Date] [datetime] NULL,
[To_Date] [datetime] NULL,
[Factor] [numeric](15, 2) NULL,
[Consumption] [numeric](15, 2) NULL,
[Sample_date] [datetime] NULL,
[COD] [numeric](15, 2) NULL,
[SS] [numeric](15, 2) NULL


texts: How can I get a unique array in PostgreSQL?

texts:
The following

SELECT ARRAY[a,b,c,d]
FROM ( VALUES
  ('foo', 'bar', 'foo', 'baz' )
) AS t(a,b,c,d);

Returns {foo,bar,foo,baz} of type text[]. I would like to get {foo,bar,baz} of type text[] with one of the duplicate bar elements removed? Does PostgreSQL have a unique function that works on a text-array, or an anyarray of anyelement?


texts:
I'm trying to do a pg_restore on some SQL files. Here's the command I used when I dumped from the other database:

pg_dump -f /home/mctools/public_html/cp/upload/manufacturers.sql \
        -Fc -i -v -O -x -t manufacturers \
        -h localhost -p 5432 -U aztools_tools aztools_dbuser

And here is the restore command:

pg_restore -d mctools_dbuser -Fc -c -i -O -x  \
           -h localhost -p 5432 -U mctools_user \
           /home/mctools/public_html/cp/upload/manufacturers.sql

Here is the error I get:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2165; 2606 27769168
                            CONSTRAINT manufacturers_pk aztools_tools
pg_restore: [archiver (db)] could not execute query: ERROR:  cannot drop constraint
                            manufacturers_pk on table manufacturers
                            because other objects depend on it
DETAIL:  constraint products_fk01 on table products depends on index manufacturers_pk
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
Command was: ALTER TABLE ONLY public.manufacturers DROP CONSTRAINT manufacturers_pk;

What do I need to do to fix this?


texts: Cannot connect to SQL Server after setting sa password to blank

texts:
I have installed SQL Server 2008 R2 on a Windows 2008 server. I need to set the sa password to blank for some reasons to make an application works; but it seems like the password changes automatically some hours later.

I log in to SQL Server with windows authentication and set sa password to blank and I am able to log into sql with sa and blank password, but another day when I try to log in with sa and blank password and the error is:

  no process is on the other end of the pipe error 233


texts: SQL Server Reporting Services Upgrade/Migration

texts:
I couldn't find any documentation on this. I'm currently doing a dry run of SSRS 2005 to SSRS 2008 R2 upgrade/migration. I've backed everything up (encryption key and configuration files) and did a native backup then restore on the new server.

As it's restoring, I see "Converting database 'ReportServer' from version 611 to the current version 661.

Does this mean the server is doing all the heavy lifting of the upgrade in the background. After the restore, I am able to bring the service up and going rather quickly. Is this "upgrade" documented anywhere?


texts: Give users permission to read subsets of data in tables in SSRS

texts:
I'd like to create a SSRS report which utilizes data from our Data Warehouse, DW. This DW has summarized, for example, the sales of 100 or so stores into one table FactStoreSale.

In the report I've managed to see which AD user who has started the report, by using the expression =User!UserID as an default value in a parameter.

Each AD user is included in a AD Security Group. This Group typically includes the manager Domain\first.lastname and the employees shared account Domain\Store1000User. The name of the Security Group is typically GRP_1000 (for StoreKey 1000).

Is it possible to allow users in a Security Group to only see data corresponding to that StoreKey, i.e. if some user included in the Security Group GRP_1000 uses the report, the resulting set will be:

Select Sales, Quantity FROM FactStoreSale
WHERE StoreKey = 1000

Note: I do not wish to create a report for each Store, as that would require 100 reports. Some managers can manage more than one store, so they may be included in multiple Security Groups and need the corresponding permissions.

I do not want to split up the underlying table FactStoreSale, even though it should be possible to split this up into 100 or so schemas, and give members of the Security Groups access to these scehmas accordingly.

A solution I have in mind is to create a table using SSIS which gets updated every day with the appearence:

SecurityGroup    StoreKey
GRP_1000         1000
GRP_1001         1001
GRP_1002x        1002

and then use this table to reduce the amount of stores a user can see data from.

I do not know how I can utilize the Security Group in SSRS though, seeing as I have only found UserID in the Built-in Fields in SSRS, but nothing about their respective Security Groups.

I am also not sure if this is the optimal way to go, as it requires additional tables and manually updating the table (because I do not wish to automate this procedure yet to have a higher Control over this).

EDIT

I found a way to get the permission path, i.e. Group belonging to a user using SQL server and the Query:

EXEC xp_logininfo 'Domain\User.Name', 'all'

I've tested in SSRS that I can replace 'Domain\User.Name' with my @User_id variable. I've tested to create a dataset with this query. This seems to give the correct data, but what it lacks is the StoreKey column which I thought could be Cast(RIGHT(Permission_path,4) as int).

I'd like to be able to have a variable called @StoreKey in SSRS where Available Values from Query would be something like:

Select Cast(RIGHT(Permission_path,4) as int)
FROM EXEC xp_logininfo @User_id, 'all'

, but naturally I can't make an EXEC in the FROM clause. I also tried to make a new Dataset in SSRS which would use data from another Dataset within the same Project but that did not work either (and I Googled that it is not possible).

Edit: final solution

What I did to implement this was to create a table of the appearence:

ADGroup     StoreKey
Group_HQ    1010
Group_HQ    1011
Group_HQ    1012
Group_HQ    1000
Group_1010  1010
Group_1011  1011

I then created a variable called StoreKey in SSRS where I used Available values from dataset and where I had a dataset where I selected StoreKey using a JOIN on the current user's AD Group using the Query provided by sepupic and my table I had created. Thus I only got the StoreKeys available to certain AD Groups.


texts: Another question on the photo organisation and storage app design I am working on for my class: the specifications mention that people are able to be recorded in the database even if they don't appear in any photograph and that a location can be recorded even if there aren't any photographs associated with them. I'm not sure I understand what this would mean. Would you say this would mean the person and location tables should be parent tables to the photo table or am I missing something?


texts: Best strategies to have a backup of an RDS MySQL database

texts:
I have an AWS RDS MySQL database. I'm not a DBA, so my knowledge is limited.

I wish to design strategies to have zero data loss in case of a failure, in terms of replication or data backup.

I know they are different terms. (And ignoring that a wrong delete statement can delete data in which case replication may not be useful).

All I wish to do is have a zero data loss when there is a database failure.
AWS maintains snapshots but that may be few hours old. So there is data loss.

Should I consider setting a DB server outside AWS? Or what? Should I
do replication or take regular backup?

Any other strategies that DBAs have in mind?


texts: Need to add leading zeroes to string representing a date

texts:
I have a string that represents a date in the format MM/DD/YY but some dates that I need to insert into a table are missing the leading zero on the MM and/or DD.
For example, '9/6/02'.
I need to add (when necessary) leading zeroes to the MM and DD portion of the string before inserting the value into a varchar2 column.
How to do this?
Currently, the DBA has the column as a varchar2.  No calculations or comparisons are done with the column so it is just for reporting purposes.  I will recommend changing the column to be a date type, however in the meantime I need a solution that will work with the varchar2 column.


texts:
Given the following structure

There's a way to enforce that Table1.id3 only can seted to values in Table4.id3 where Table4.id2 = Table1.id2?  Initially I thought in a CHECK constraint, but subqueries are not allowed.


texts:
I want to alter a table column from CHAR(2) utf8mb4 to CHAR(2) latin1 (so it will use 2 bytes instead of 8, utf8 is not needed because they are country codes so there are only latin characters).

This table receives about 50 new rows per second.

There are about 125 million rows, total table size 5GB, so the alter will take a wile.

How can I alter the table without locking the writes?


texts: SQL Server Replication with Historical Tables

texts:
Is it possible to replicate a SQL Server database and for the replica to have temporal/historical tables?

Details:
I have a production system, running SQL Server 2014. I want to have a replica database and I also want to track historical changes.

It would be ideal to replicate the production Database to a SQL Server 2016 instance and to track changes with temporal/historical tables.

Is this possible?
Is this possible on Azure?

Edit: Part 2

After David Browne's excellent answer and doing some further reading, I have more direction and more questions.
I will be using an Azure SQL Database / Logical Server.
I will be using transactional replication because
1. it is supported on Azure SQL Database and
2. we can have temporal tables disabled on the publisher (Sql Server 2014) and temporal tables enabled on the subscriber (Azure SQL Database / Logical Server) As per David Browne's excellent answer.

Part 2 Questions:
How much additional load is placed on the "publisher" database when it becomes a publisher?

How can I avoid downtime and locks on the publisher when setting this up?

I have multiple "publisher" databases. Can I have multiple subscriber databases on one Azure SQL Database / Logical Server instance?


texts: MySQL query using alias to count certain years from DATETIME

texts:
I have a table which consists of an ID count, a DATETIME field named orderDate showing when a customer bought an item. I am trying to create a query which takes ID and orderDate, to find all dates which were ordered in 2015 using an alias and count them.

For example, my output table should contain IDs and a count of all the orders placed in 2015 for that ID in a single row.

My query, which I have been using to try make it work with very little success is:

SELECT ID,YEAR(orderDate) as lastyear
from t1
HAVING lastyear = 2015

I am aware that I cannot use WHERE, so it's a case of using HAVING, but I cannot for the life of me right now figure out how to use the alias to count.

I am using MySQL 5.1.


texts:
I am implementing a multi tenant web app using postgres. I want to use Row Level Security (RLS ) for data separation. RLS is available from PG version 9.5. However, at the same time, the HA / DR teams plan to use BDR ( Bi directional replication) for the HA solution. It seems that BDR is not yet supported in 9.5.

What can be done in such a scenario ?
Is it possible to get BDR on 9.5 ?
What other replication strategies provide the same benefits as BDR?

Yes, schemas is also an approach, but I am already using that for tables which have less data. I want RLS for tables that will eventually get enormous, so replicating them in different schemas wont be ideal.


texts: Select multiple columns in IF clause conditionally?

texts:
Is it possible to select different fields inside a SELECT conditionally?

Pseudocode:

CREATE VIE myview AS SELECT
  id,
  IF(signal_field == 1)
     firstname as first,
     lastname as last
  ELSE
     concat(firstname + lastname) as firstlast
  FROM mytable;


texts: iSeries Linked-Server SQL 2012 performance issue

texts:
I have a seen a serious performance dive when moving from SQL Server 2008 R2 to SQL Server 2012 on my linked server queries.

I have set the linked server on the 2012 server as a Microsoft OLE DB Provider for ODBC Drivers that links through a DSN which uses the IBM/iSeries driver version 13.64.12.00 (I have also tried the link as provider IBM DB2 for IBMDASQL OLE DB Provider). The 2008 R2 server was setup with a linked server as Microsoft OLE DB Provider for ODBC Drivers that links through a DSN which uses the IBM/iSeries driver version 13.00.05.00.

I have worked with both Microsoft and IBM support for months and they are pointing fingers at each other and the issue is still not resolved. I have done more traces than I care to count. What I do know from all of these:

All other queries through linked servers that are not to the iSeries are faster on the new server than the old.
The iSeries drivers are significantly slower on the new server, within and outside of the SQL environment. The SQL environment does add additional slowness, but even using a separate tool to run the queries it is twice as slow on the SQL 2012 server.
Putting the updated IBM iSeries drivers on did not help (used to have the same version as the SQL2008R2 server but updated in attempt to find speeed).
The OLE DB linked server is slower than the ODBC linked server.

Has anyone else had this issue and what did you do to resolve it?

I have the OLE DB for DB2 provider linked (uses DB2OLEDB) server setup, but this is also much slower than the IBM ODBC connection. I already have the Allow In Process and Dynamic Parameter options set to 1.  I ran the query as an OPENQUERY and as 4 part name linked server query.


texts:
I have set up an AlwaysOn with a primary and secondary replica on different subnets but I have been forced to use Registerallprovidersip set to 1.  This causes the listener to only have 0 IP address in DNS which means I am at the mercy of DNS replication when a failover event occurs as it will take sometime for the listner's IP to update.

Has anyone in a similar situation found ways to improve failover time?


texts: How to create comment on table from query result

texts:
I want to use a query result as comment on the table. I have tried in psql:

\set list select count(*) from department.master_data
COMMENT ON TABLE department.master_data IS :list;

But I got this error:

it=> \set list select count(*) from department.master_data
it=> COMMENT ON TABLE department.master_data IS :list;
ERROR:  syntax error at or near "selectcount"
LINE 1: ...ABLE department.master_data IS selectcoun...


texts: postgresql: how to know when a function was modified

texts:
Scenario:
Someone creates a function named "foo" in a PostgreSQL database.
Next, someone modifies the inner workings of function "foo".

Question:
Does PostgreSQL offer a means to know who modified function "foo" and when "foo" was modified?