texts:
I've designed a mysql database made of several independent/unrelated tables to use in my office. Different tables hold information about clients (names, addresses etc), visits to the office (dates, reason etc) or other information like comments (related to a specific visit).
When a known client enters the office I can retrieve all the relevant data, insert the new visit data and possible comments about the visit.
I need to store visits in a specific order (1st visit 1st, etc). Sometimes I forget to store a visit and I have to enter the data later. The order of the visits gets messed up and I manually (update commands) correct it by changing the visits' autoincrementing primary key number (which represents the order). This way I get the right order of visits but comments (related to the changed primary key) cannot be retrieved.
Is there any way to make sure that changing the primary key would make the necessary changes to the rest of the database, so that everything would work as before the changes? Do relational 1:many or other implementations accomplish that? And how?
Please note that I'm an amateur, self-taugth "database designer".


texts: Storing not very volatile information in database with cache or mongodb

texts:
For a new project I'm working on right now I was doubting on which database/storage system to use for specific information which does not change that often.

We have for instance some company-profiles which will have only incidental writes to it when things like opening-hours change. On the other hand they have a lot of read-throughput; at every page load the information has to be loaded. Storing it at the filesystem as plain documents results in very much IO; Using that as a starting point I was searching for some sort of in-memory-solution.

Normally I store this information in a Postgres database, without any caching in the application-layer. I could off course add a caching layer in redis or memcached to produce lesser IO reads from disk, and handle updates of the cache at the application-layer on update of the information

An other solution is to use MongoDB to store the 'documents'. But I don't know how mongodb handles the read/writes. Eg with every retrieval of the document does it check the filesystem, or does it serve the document from cache/RAM? (I read somewhere mongodb keeps a lot of RAM available for itself)

Can someone shed some light on this?


texts: How to avoid errors in server log when conflict fails within a transaction before retry?

texts:
We have a table such as
create table pages (
  id uuid primary key not null,
  parent_id uuid not null references pages(id),
  shortname text not null,
  shorturl text not null,
  constraint globally_unique_shorturl unique (shorturl)
  constraint locally_unique_shortname unique (parent_id,shortname)
)

where the logical intent is to keep a collection of web pages where the full URL for each page is constructed from shortname fields defined by parent_id references. Root of the system points to itself with parent_id. As such, any given child must have unique shortname per parent (constraint locally_unique_shortname). In addition, we have globally unique shorturl (think bit.ly) that obviously must be globally unique for the whole table. The target is to keep both shortname and shorturl as short as possible and as such, we generate short random identifiers for shorturl. In addition, the shortname is selectable by user to allow system to create semantic URLs for every page.
When I'm trying to add a new page to the system (or modify existing page), we do it in a transaction and we create new SAVEPOINT immediately before trying to add a new page. If we get error
ERROR: duplicate key value violates unique constraint 'XYZ'

we know that we need to rollback to savepoint and then figure out if shorturl or shortname should be modified before retrying. Note that if shorturl fails, we can generate a new one automatically, but if shortname fails, we need to prompt user for a new value, so we really need to know which constraint failed. (In addition, this query fails pretty seldom so I'd prefer not having to try SELECT with the new value to figure out if it's already taken because this mostly succeeds without any extra queries. Also note that such SELECT would still race against other concurrent transactions and I'd still end up with the same issue every now and then.) We're using serializable transactions, in case it makes a difference.
However, this causes PostgreSQL to always store this error in the server log even when the code obviously handles this correctly (rollback is emitted immediately the query fails). These errors spam the log to make other possible real problems harder to notice. Obviously, I can just fall back to filtering the log before trying to monitor it, but that would feel pretty poor solution.
To workaround this, Disable log ERROR: duplicate key value violates unique constraint suggests that I should use syntax INSERT ... ON CONFLICT DO NOTHING. This would be otherwise fine (I could try to do insert and if no rows were affected I can assume that I have a conflict and nothing will be added to server log) but I'm missing info about which constraint failed when there are multiple possible constraints.
Is there any nice way to solve this without having to modify source code of PostgreSQL or load some custom extension in PostgreSQL?


texts: How can I inform myself that an automatic SQL Server process has worked?

texts:
I have a Windows batch file which extracts some data from a remote system and then imports it into an instance of MS SQL Server 2014. The batch file runs SQLCMD and uses SQL files to bulk insert from the text files into a staging table, tidy the fields up and move the data to a production database.

Having finished the development of this, I would like to test the stability of the process by easily knowing when it has run successfully.

I could check the batch file error log, but it is part of a larger process which means it takes a while to find the SQL Server part.
I could send an email to myself when the process has finished, but this does not tell me if it worked properly. By this, I mean if the INSERT INTO statement from the staging table to the production table did not produce any errors.

Does anyone have any suggestions? My gut feeling is that I need to include something in the SQL statements I use to import the data. Thanks!


texts: Altering a parent table in Postgresql 8.4 breaks child table defaults

texts:
The problem: In Postgresql, if table temp_person_two inherits fromtemp_person, default column values on the child table are ignored if the parent table is altered.

How to replicate:

First, create table and a child table. The child table should have one column that has a default value.

CREATE TEMPORARY TABLE temp_person (
    person_id SERIAL,
    name      VARCHAR
);

CREATE TEMPORARY TABLE temp_person_two (
    has_default character varying(4) DEFAULT 'en'::character varying NOT NULL
) INHERITS (temp_person);

Next, create a trigger on the parent table that copies its data to the child table (I know this appears like bad design, but this is a minimal test case to show the problem).

CREATE FUNCTION temp_person_insert() RETURNS trigger
LANGUAGE plpgsql
AS '
BEGIN
INSERT INTO temp_person_two VALUES ( NEW.* );
RETURN NULL;
END;
';

CREATE TRIGGER temp_person_insert_trigger
    BEFORE INSERT ON temp_person
    FOR EACH ROW
    EXECUTE PROCEDURE temp_person_insert();

Then insert data into parent and select data from child. The data should be correct.

INSERT INTO temp_person (name) VALUES ('ovid');
SELECT * FROM temp_person_two;
 person_id | name | has_default
-----------+------+-------------
         1 | ovid | en
(1 row )

Finally, alter parent table by adding a new, unrelated column. Attempt to insert data and watch a "not-null constraint" violation occur:

ALTER TABLE temp_person ADD column foo text;
INSERT INTO temp_person(name) VALUES ('Corinna');
ERROR:  null value in column "has_default" violates not-null constraint
CONTEXT:  SQL statement "INSERT INTO temp_person_two VALUES (  $1 .* )"
PL/pgSQL function "temp_person_insert" line 2 at SQL statement

My version:

testing=# select version();
                                                version
-------------------------------------------------------------------------------------------------------
 PostgreSQL 8.4.17 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit
(1 row)


texts: Change MySQL backup script to use on PostgreSQL

texts:
On a few MySQL databases I have I backup them up ny first;
mysql_config_editor set -h localhost -G dbbkup -P 3306 -u root -p

Then running a wee script;

#!/bin/bash

BACKUPDIR="/home/deploy/backup-db"
BKUPSSH="deploy@backup.com"
BKUPSERVDIR="/home/deploy/backups/databases"

# Remove files older than 30 days
find $BACKUPDIR/ -mtime +31 -exec rm {} \;

# DO NOT BACKUP these databases
IGNOREDB="
information_schema
mysql
test"

#* MySQL binaries *#
MYSQL=$(which mysql)
MYSQLDUMP=$(which mysqldump)
GZIP=$(which gzip)

# assuming that BACKUPDIR exists
if [ ! -d $BACKUPDIR ]; then
  mkdir -p $BACKUPDIR
else
  :
fi

# get all database listing
DBS="$(mysql --login-path=dbbkup -Bse 'show databases')"

# Set date and time for the file
NOW=$(date +"%Y-%m-%d_%H.%M"); # year-month-day_hour.minute format

# start to dump database one by one
for db in $DBS
do
        DUMP="yes";
        if [ "$IGNOREDB" != "" ]; then
                for i in $IGNOREDB # Store all value of $IGNOREDB ON i
                do
                        if [ "$db" == "$i" ]; then # If result of $DBS(db) is equal to $IGNOREDB(i) then
                                DUMP="NO";         # SET value of DUMP to "no"
                                #echo "$i database is being ignored!";
                        fi
                done
        fi

        if [ "$DUMP" == "yes" ]; then # If value of DUMP is "yes" then backup database
                FILE="$BACKUPDIR/$NOW-$db.sql.gz";
                echo "BACKING UP $db";
        $MYSQLDUMP --login-path=dbbkup --add-drop-database --opt --lock-all-tables --set-gtid-purged=OFF $db | $GZIP > $FILE
        fi
done

# change permissions on files
chmod -R 755 $BACKUPDIR

# rsync backup to 'Larry' the backup server and append the log file
rsync -azv $BACKUPDIR -e ssh $BKUPSSH:$BKUPSERVDIR >> /home/deploy/db_rsync.log 2>&1
RESULT="$?"

# check result of rsync
if [ "$RESULT" != "0" ]; then
    echo -e "rsync exit Code:" $RESULT "\nFailed to rsync databases" >> /home/deploy/db_rsync.log 2>&1
else
    echo "succesfully rsynced databases" >> /home/deploy/db_rsync.log 2>&1
fi

As I'm not a DBA to trade, does anyone know how I can achieve the same by slightly tweaking the script to use for PostgreSQL?


texts:
In my sybase 12, when I look at the showplan of a select distinct query, I see GETSORTED in the showplan's output.

I've searched through the entire internet (YES!) and I can not seem to find a link that tells in what order does GETSORTED actually sort the data?

I have reasons to believe that it does the sorting on the size of the data in column for different rows, and for rows of same length, it does alphabetical.

Execution plan:

W (5):     STEP 1
   W (6):         The type of query is INSERT.
   W (7):         The update mode is direct.
   W (8):         Worktable1 created, in allpages locking mode, for DISTINCT.
   W (9):
   W (10):         FROM TABLE
   W (11):             tablename
   W (12):         Nested iteration.
   W (13):         Table Scan.
   W (14):         Forward scan.
   W (15):         Positioning at start of table.
   W (16):         Using I/O Size 2 Kbytes for data pages.
   W (17):         With LRU Buffer Replacement Strategy for data pages.
   W (18):         TO TABLE
   W (19):             Worktable1.
   W (20):
   W (21):     STEP 2
   W (22):         The type of query is SELECT.
   W (23):         This step involves sorting.
   W (24):
   W (25):         FROM TABLE
   W (26):             Worktable1.
   W (27):         Using GETSORTED
   W (28):         Table Scan.
   W (29):         Forward scan.
   W (30):         Positioning at start of table.
   W (31):         Using I/O Size 2 Kbytes for data pages.
   W (32):         With MRU Buffer Replacement Strategy for data pages.

Query:

select distinct col_1 from tab_1

I am in the middle of migration of an application from Sybase 12 to Sybase 15. I see differences in the ordering of the data and that further causes differences downstream. I can not use an ORDER BY because I don't want to touch the existing application. If I can figure out what GETSORTED is doing, I can probably do the same order by on my new dataserver.


texts:
I have a requirement from one of my teams which is database copy (cloning) from production to my test server on daily basis at a particular time.

I know this is a very simple process which every DBA would perform.

Should I use RMAN duplicate for cloning? Should I use Goldengate? Is there any way I can schedule the same without manual intervention?

Server - RHEL
Database - Oracle 12c/11g


texts: Saving all sql server transactions to txt for a transactional replication to another database

texts:
I have sql server 2014 running my main database at location A.
I want to know if it would be possible to export all insert,update and delete queries to txt files for me to then run on a off site database ? I need to set up something like sql servers transactional replication but with the queries being sent to txt files.

Is it possible for sql 2014 to log and export queries to txt files ? is there any softwares i could use ?

Due to cost reasons setting up a vpn to the offsite database for transactional replication is unfeasable.


texts: One of the mongo shards consuming too much disk space.

texts:
We have mongo cluster with 10 shards of each having 3 replicas.
Recently we are facing an issue, exceptionally in a couple of shards, the disk space among all the 3 replicas has reached above 90%.

Can someone help me why this will happen with mongo cluster?

Thanks in advance.


texts: Transaction isolation level - Read committed and select on large tables

texts:
I am trying to troubleshoot an issue in a report and wondering if the below scenario is possible.
I have a tables which is 548GB and there are reports which sometimes select 5 years of data which could be alomost the entire table.  The table has a non unique clustered index.

I read about read committed isolation levels and was testing it to see how it was locking the table during selects and from my understanding it looks like it puts a series of shared page lock.   Acquire shared page lock, read page, release shared page lock, move to next page and so on.

If the above is true, and lets assume the query is doing an clustered index scan, how does it handle an insert at the beginning of the table which it has already read past.  What I mean by beginning of table is that since the table is having a clustered index and since it has to be ordered if an lower value index id record gets inserted then it needs to be inserted into the starting half of the leaf level of the clustered index.


texts: MySql - Remove duplicate record if another column is empty

texts:
I have a table with many duplicate records . My table looks like this:

ID |First_Name     | Last_Name     |City
1  |    Alan       | Smith     |Los Angeles
2  |    Alan       | Smith     |
3  |    Alan       | Smith     |New York
4  |    Alan       | Smith     |
5  |    Alan       | Smith     |Houston

I can find duplicate records with this query:

SELECT First_Name, Last_Name, COUNT(*)
FROM tab_Name
GROUP BY First_Name, Last_Name
HAVING COUNT(*) > 1

I'd like to remove duplicate records only under following 2 conditions:

The First_Name and Last_Name are the same
the city column is empty(null).

What's the correct way to achieve this? Please let me know if more explanation is needed. Thank you!


texts: What's the reason for using double inner join in this SQL statement?

texts:
I am looking at this legacy SQL query. The bit I am not able to get is why it's inner joining same table twice on the same columns. I am talking about Table1 and Table1 joined with alias "Table1Alias",

SELECT DISTINCT othercolumns,
                Table1Alias.columna
FROM   maintable
       INNER JOIN secondarytable
               ON maintable.id1 = secondarytable.a_id1
       INNER JOIN table1
               ON secondarytable.id2 = table1.id3
       INNER JOIN table1 Table1Alias
               ON secondarytable.id2 = Table1Alias.id3
       INNER JOIN thirdtable
               ON table1.id4 = thirdtable.id5
       INNER JOIN fourthtable
               ON thirdtable.id6 = fourthtable.id7
       INNER JOIN fivetable
               ON thirdtable.id8 = fivetable.id9
       INNER JOIN sixthtable
               ON Table1Alias.columna = sixthtable.id10
       LEFT JOIN seventhtable
              ON thirdtable.id11 = seventhtable.id12
WHERE  LEFT(secondarytable.type123, 2) BETWEEN '01' AND '09'
       AND secondarytable.type456 = 'cate'
       AND table1.type = '0'
       AND Table1Alias.columna = 'conn'


texts: In the practice of Kimball's Dimensional data warehouse design model, do lookup tables (not dimensions) contain a sid column?

texts:
Trying to create a simple lookup table for a drop down on a BI web page.
The table contains 2 columns, an key and a description.
What is the best practice with regard to Dimensional Modeling when designing this table?
Would key => number, description => character (column => data type) be sufficient or does the key need to be a SID?

Think of a dropdown in a web page or web form like "Country".


texts: Optimisation select with large IN and millions of records in result

texts:
I work with a table which consists of 1.5B records.
DB: RDS, PostgreSQL 12.4, 16GB RAM, 4vCPU
Schema:
CREATE TABLE public.trip (
     id bigint NOT NULL,
     cell_to character varying NOT NULL,
     cell_from character varying NOT NULL,
     indicator character varying NOT NULL,
     time_id integer,
     weight double precision
);

CREATE INDEX ix_trip_cell_from ON public.trip USING btree (cell_from);
CREATE INDEX ix_trip_cell_to ON public.trip USING btree (cell_to);
CREATE INDEX ix_trip_indicator ON public.trip USING btree (indicator);
CREATE INDEX ix_trip_time_id ON public.trip USING btree (time_id);

I'm trying to pull all trips which happen within some cells (output around 7-12 million records):
EXPLAIN ANALYZE SELECT
    cell_to,
    cell_from,
    time_id,
    weight AS trips
FROM
    trip
WHERE
    cell_to IN (VALUES ... 1k values)
  AND
    cell_from IN (VALUES ... 1k values (the same as above))
  AND
    time_id IN (VALUES ... 3 to 20 values)
  AND
    indicator = 'some string';

The result you can find here https://explain.depesz.com/s/RxH4.
What I've tried:

Replaced with INNER JOINs -> got some improvements
Changed b-tree index to BRIN -> a bit improved timing
VACUUM, REINDEX, work_mem -> zero effect

The query still runs too long.
UPDATE:
Thanks to @NikitaSerbskiy and @Laurenz Albe, forcing postgresql using index and adding a multicolumn index helped a lot.


texts: pre login initialization =29237; handshake=26244

texts:
Connecting to one of my servers (no mirror or availability group running), from a local machine and I get this error message below related to handshake when the firewall is up.

when the firewall is down it connects straight away.

the question I would like to address is:

is it always these number below (29237 and 26244) that sql server uses for connection?

I am aware of the port numbers on single and multiple instances.

This server by the way is not a named instances.

initialization =29237; handshake=26244

or does it change? if so then - based on what?

we have had issues with one domain controller but I have ruled that out.

I haven't even touched authentication schemas and Kerberos


texts: Unable to start the services of SQL server after Downgrading from Enterprise to Standard

texts:
We are performing the downgrade activity from SQL server 2008 R2 Enterprise to Standard.

After the uninstall and re-install the standard version, (once the saved system databases were copied to the new installation location), we are unable to start the SQL service which was running under local system account:

Error from event viewer:

  Error 2(The system cannot find the file specified.) occurred while
  opening file 'C:\Program Files\Microsoft SQL
  Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf' to obtain
  configuration information at startup. An invalid startup option might
  have caused the error. Verify your startup options, and correct or
  remove them if necessary.

Earlier before downgrade these system databases were on D:\, but i believe that may not be the cause because we have not witnesses this earlier during the previous times when we downgraded.

Please suggest what possibly could have gone wrong here:

Thanks


texts: Indexing to improve performance of range queries

texts:
I've got a relation in a Postgres 8.2 database similar to this:

CREATE TABLE foo (
    foo_id varchar(160) NOT NULL,
    bar_id varchar(160) NOT NULL,
    created bigint NOT NULL,
    PRIMARY KEY (foo_id, bar_id)
);

Let's leave aside for the moment the fact that the PK is composite and uses varchars (not my choice, legacy product, etc. etc.).

Currently, our application never issues queries that contain created in a WHERE clause, so it's not indexed. However, we've got a new requirement that requires us to query on a range of created values. The proposed query is along the lines of:

SELECT * FROM foo WHERE foo_id IN (...) AND created > 1234 AND created <= 6789

The foo table is easily the largest in our application, but even then it's quite likely to have fewer than 50,000 rows even in the largest deployment, and there are rarely more than a dozen or so rows with the same foo_id.

My question is, should an index be added on the created column, considering that the foo_id is part of the PK? If so, does it make sense to index only the created column, or to index on (foo_id, created)?

My EXPLAIN on the above statement shows that the PK is being used, and then a FILTER operation is being applied. Using test data, the performance seems fine. My concern is performance if the tables grow to a massive size.

Thanks!


texts: What is a Data Source and where can I find it in SSMS?

texts:
I have just recently started using SQL Server Management Studio. After some hitches, I have managed to create a Database. Inside the DB I have also created some Tables and populate them.

Now I am trying another software that needs to be connected to this DB. When running the software it asks me:

"Data Souce" and "Catalog"

Apparently the "Catalog" is the name of the DB I have created but I don't know what "Data Source" refers.
It must be a rather begineer question but what does "Data Source" refers to?


texts: optimize the query with big and low cardinality


texts: Postgres uuid: Use as primary key, or in addition to SERIAL -for disconnected app-

texts:
For a new SASS app with postgres 9, that will support disconnected clients.

The client not will use the regular IDs, only the UUID. The clients will be on iOS, html5/python and perhaps later on other platforms. THe client database will be sqlite.

Each customer will use their own schema, so hopefully not will exist a too big table, but still because this is for invoicing & financial stuff a lot of data will be generated, and because the cloud hosting cost are tied to CPU/RAM overage charges make sense to have the best performance from the start (plus faster responses times is good for the end-user).

So,I need to use a UUID for support in the client to create new records. Now, I'm debating if use the UUID ONLY for the sync part and regular IDs for JOINS & primary keys.

However, also I feel is duplication, and if I will store the uuid why not drop the int keys at all in the server, but worry if this will give me performance issues later and if which index type I need to use (btree will be good enough?)


texts: Table structure for many custom profiles

texts:
I'm currently developing an bulk-email campaign manager that will allow customers to upload as much information as they would like on their subscribers.

For example, I have a basic profile that is available to everyone:

First name
Last name
Email address (etc.)

This is stored in my SubscriberProfile table, and all is good.

However, I want my users to have the ability to create custom fields for subscriber information.

For example, User A might run an e-commerce site and will have the following custom fields:

Address Line 1 (varchar(100))
Address Line 2 (varchar(100))
City (varchar(100))
Postcode (varchar(7))

User B might run a cinema and have the following custom fields:

Favourite cinema (varchar(50))
Favourite genre (varchar(50))

The data could be indexed if the users wishes to use them for searching their subscribers or to create sub-groups of their main subscriber list.

How would I best structure my tables?

I was thinking initially of having 1 table that holds the custom field definition (FieldID, UserID, FieldName) and 1 table that holds all the custom field values (FieldValueID, FieldID, VarcharValue, IntValue, BitValue, etc.) but this won't allow me to index 1 customer's set of data without indexing everyone else's. It will also mean I'll have null values for all but one column per row. Seems a waste.

Another thought I had is to create a table per custom profile per user. This would allow me to index data, but what if 1000 people register for the site? I'm going to have 1000 tables to hold the custom profile data, is that good practice?

Am I approaching this wrong, is there a better way to do this?

Thanks,
Greg.


texts: Is it possible to keep track of changes to tables structure?

texts:
Is it somehow possible to keep track of changes made to the STRUCTURE (not the data) made to tables?

I mean, i don't want to know when rows are inserted/updated/deleted, what i want is to know when the table definition is been changed (and what those changes were). E.g. to know if/when a column was added, removed, if a column type was changed, if an index was added, etc.

In other words, to keep track of all the "ALTERS" of the tables. (and the state before those alters)

I'd like this to know exactly when some changes were made, and to keep track of discrepancies of data, for example to know when a column was changed to hold VARCHARs instead of INTs (just an idea), or when a new column was added, etc.

So is there a way to do this?

I am using MySQL, version 5.1.66. And, if it is not possible in MySQL, is it in other Databases?

Thanks


texts: Management tool for managing MongoDB sharded cluster

texts: Is there a database management software for MongoDB that makes it easy to create a cluster, add shards, backup shards and all the complicated stuff. There are some many MongoDB clients but they are suited for single database only, and no advanced functionality like creating and managing a MongoDB sharded cluster.


texts: Postgres: Database architecture and materialized views

texts:
I have a database with a huge table, that gathers the ranking history of mobile applications. The table is quite big, around 120 Go.
In order for my DB queries not to be too slow, I implemented several materialized views.
One of these materialized calculates the average rankings of apps over the last 30 days. It is refreshed everyday.

I now want to be able to tell at any point in time what that average was on a particular date. I.e, to have an history of averages.

Would it make sense to add the results of my materialized view everyday to a table ? Or should I partition that big table and do it another way ?

Edit : Main table structure (738,681,765 rows)

+----+--------+---------+------+-------+---------------+------------+-------------+
| id | app_id | ranking | date | price | collection_id | country_id | category_id |
+----+--------+---------+------+-------+---------------+------------+-------------+
|  1 |  1426  |   30    |  t1  |   0   |      12451    |   1658     |   2564      |
|  2 |  1427  |   15    |  t2  |   0   |      23562    |   1485     |   3256      |
|  3 |  1428  |   22    |  t3  |   0   |      14564    |   1320     |   4521      |
|  4 |  1429  |   11    |  t4  |   0   |      12468    |   1578     |   5015      |
|  5 |  1430  |   10    |  t5  |   0   |      18712    |   1100     |   6012      |
+----+--------+---------+------+-------+---------------+------------+-------------+


texts: Oracle Database 12c: TNS:permission Denied (CentOS)

texts:
(This post has a similar title to another post but the underlying cause and solution are totally different.)

Problem:

I can't login to sqlplus as sysdba but the listener seems to start

[John@localhost ~]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 13-AUG-2015 18:19:03

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Starting /home/John/app/John/product/12.1.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /home/John/app/John/product/12.1.0/dbhome_1/network/admin/listener.ora
Log messages written to /home/John/app/John/diag/tnslsnr/localhost/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                13-AUG-2015 18:19:03
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/John/app/John/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /home/John/app/John/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

The listener seems to start but TNS listener rejects the sysdba login.

[John@localhost ~]$  sqlplus -prelim / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 13 18:25:49 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name: ^C
[John@localhost ~]$

Possible causes:

I was able to login a few days before this happened. The only thing I can think of that could have caused the issue is that I changed the all files and folders that were owned by John:John to John:Users and then made Users the primary group of John. I don't know why that would matter though. If it does matter it certainly shouldn't.

How would I be get more detailed information as to why / as sysdba is suddenly rejected by the TNS listener? If it is clearly related to the group change then what specific changes need to be made to leave the group setup how it is while correcting the authentication error?


texts: Amazon Aurora MySQL5.6 Performance Schema column datatype modified and can't be corrected

texts:
I'm having trouble getting performance_schema to work in a few of my Aurora instances.  Brand new instances where I enable performance_schema work just fine, but several instances that I've had for some time seem to be completely broken and I don't know why.  The error logs indicate that the problem is that many columns within performance_schema are of the wrong type.

Excerpt of logs for detail:

2017-11-06 18:28:28 28436 [ERROR] Incorrect definition of table performance_schema.events_waits_current: expected column 'NESTING_EVENT_TYPE' at position 15 to have type enum('STATEMENT','STAGE','WAIT', found type varchar(9).
2017-11-06 18:28:28 28436 [ERROR] Incorrect definition of table performance_schema.events_waits_history: expected column 'NESTING_EVENT_TYPE' at position 15 to have type enum('STATEMENT','STAGE','WAIT', found type varchar(9).
2017-11-06 18:28:28 28436 [ERROR] Incorrect definition of table performance_schema.events_waits_history_long: expected column 'NESTING_EVENT_TYPE' at position 15 to have type enum('STATEMENT','STAGE','WAIT', found type varchar(9).

Not all columns are affected in this way but most appear to be.  Is there any reason why this might have occurred?  Is there a way to fix this?  I assume Amazon support should be able to correct it on the back end but they have been less than helpful thus far.


texts: How to align restore strategy with backup history cleanup?

texts:
I was curious about how that particular problem is tackled.

1) Full backups performed on saturdays.
2) Differentials every other day.
3) Tlog backups every 15 minutes.

When trying to restore a database through SSMS:
Right click on database > Restore...

It seems to hang for a long time then display the error message and display no backups to be restored and displaying the following error message:

Unable to create restore plan due to break in the LSN chain.

Looking into it, I realize that we do have all the backups but I keep 1 month of backups.

I execute sp_delete_backuphistory weekly and delete anything older than 35 days to make sure that I don't remove history for valid backups.

There are situations where the history keeps track of backups which are no longer there and we get the error.

How did you guys handle that particular problem?


texts: Is there a free/web-based game that requires knowledge of SQL to win?

texts: Looking for an alternative way of teaching SQL, and wondering if there is a free/web-based game that requires knowledge of SQL to win. If possible, the game should not only be fun, but also track how much SQL the player knows.


texts:
I had a table with 70+ million records in a SQL Server database, I dropped that table (a one-time thing) to free up space on the disk, but it looks like the size didn't change much. I do see that I could shrink the db to a minimum.

Is that the way to do it?

The last time I did a shrink using SQL Server Management Studio, it took a few hours to complete. Is there a faster way?


texts: Should I Link a Fact to Hierarchical Dimension at ALL Levels or only the most granular?

texts:
Related question about the same project here.

I'm looking at two approaches to model a hierarchical relationship between my fact and dimension tables in a data warehouse for storing IT infrastructure data.

Consider as an example:

FACT_Folder

Contains facts about a file system folder

Dim_Drive

A folder links to a single DRIVE entry

Dim_Array

A drive links to a single array

Dim_Server

An array links to a single server

Dim_Farm

A Server links to a single Farm

For our purposes we don't need to worry about a folder moving to a different drive.

We want to be able to get metrics at all the levels of this hierarchy.

Assuming I will eventually have millions of Folder entities (with daily data), and hundreds or thousands of drives, arrays, etc; which design pattern would you recommend and why?

Normalized

A Folder links to a Drive, and the rest of the hierarchy is between dimensions

Denormalized I

A Folder links to a Drive, but the Drive dimension contains the whole hierarchy for each Drive entry for every row

Denormalized II

A Folder links directly to all levels of the hierarchy

??????


texts: Build a histogram of road speed based on road length by grouping road segments

texts:
A data set look like this:

The table doesn't have sum(segLength yet), but can be computed by summing up sum(segLength) from road_table group by roadID
A roadID (e.i. Lake Street) belongs to a roadType (e.i. St) and consists of segments (e.i. segID is 2005 for a given road block).

I want to plot average speeds of roads grouped by road lengths further grouped by road types like shown in example output below.

How to add columns for each roadType, within each roadType group roads into bins, within each bin compute average value of maxSpeed?

I'm using PL/SQL. I can translate a query from MySQL or PostgreSQL if necessary.


texts: How is the Database Encryption Key for SQL Server TDE linked to the certificate?

texts: The MSDN article on SQL Server TDE states "Create a database encryption key and protect it by the certificate". What does protect mean? The database encryption key is not created from the certificate?


texts: Redshift DROP and TRUNCATE hang with no locks

texts: I followed this procedure to check to see if a table lock is preventing a table from being deleted, but finding none.  However, I am still unable to drop a table in Redshift.  Are there other system tables I can check to see if something is locking a table and preventing the drop?


texts: SQL Server 2014 extract value from table with double join in row and column and subtotal


texts: Oracle Materialized View Overloads Database

texts:
Summary: Materialized View across DB link eating up too many system resources
Question: Is there a way to throttle a materialized view refresh?

We have some fairly large tables that we copy from one oracle DB to several others as a cached backup if the network to the central database is down (or the central database is down). The data copy runs hourly or daily (depending on the data).  It's not mission critical or real-time.

Our first idea was materialized views on the receiving databases to  pull the data from the central database across a database link. If I remember right we couldn't do incremental refreshes because of the database links so the refreshes are full table refreshes.

We discovered when the materialized view refreshes run they eat up lots of system resources sometimes overloading the system and slowing critical stuff. (I'm a programmer not DBA so I'm not entirely sure what resource was constrained, but I think disk access, possibly memory.)

Is there any way to throttle a materialized view refresh so it won't do that?


texts: Avoid unique violation in atomic transaction

texts:
Is possible to create atomic transaction in PostgreSQL?

Consider I have table category with these rows:

id|name
--|---------
1 |'tablets'
2 |'phones'

And column name has unique constraint.

If I try:

BEGIN;
update "category" set name = 'phones' where id = 1;
update "category" set name = 'tablets' where id = 2;
COMMIT;

I'm getting:

ERROR:  duplicate key value violates unique constraint "category_name_key"
DETAIL:  Key (name)=(tablets) already exists.


texts: Error while connecting psql to PostgreSQL 9.6

texts:
I am using Postgresql 9.6 and getting an error while connecting psql.
Error- ./psql
psql.bin: FATAL:  the database system is starting up. What could be the issue?

In the log it says
2021-02-18 13:23:31.311 IST [85845]: [-] db=,user= LOG: started streaming WAL from primary at 3FC/91000000 on timeline 2
2021-02-18 13:23:31.312 IST [85845]: [-] db=,user= FATAL: could not receive data from WAL stream: ERROR: requested WAL segment 00000002000003FC00000091 has already been removed


texts: What is the fastest way to insert data into MySQL memory table?

texts:
I need to insert 10M+ records into a MySQL memory table.

A naive approach was to pipe records using shell, e.g.

$ tail -10 ~/Desktop/test.sql
INSERT INTO `test` (`a`, `b`) VALUES (4, 5);
INSERT INTO `test` (`a`, `b`) VALUES (5, 6);
INSERT INTO `test` (`a`, `b`) VALUES (6, 7);
INSERT INTO `test` (`a`, `b`) VALUES (6, 7);
INSERT INTO `test` (`a`, `b`) VALUES (1, 2);
INSERT INTO `test` (`a`, `b`) VALUES (2, 3);
INSERT INTO `test` (`a`, `b`) VALUES (3, 4);
INSERT INTO `test` (`a`, `b`) VALUES (4, 5);
INSERT INTO `test` (`a`, `b`) VALUES (5, 6);
INSERT INTO `test` (`a`, `b`) VALUES (6, 7);
$ mysql -u root -proot test < ~/Desktop/test.sql

The table itself is simple:

CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8;

In essence, the question is what are some lower level ways of inserting data to MySQL?


texts: Delete duplicates with specific columns as identifier

texts:
I'm required to delete all duplicate entries of a table. The duplicates are identified by two non-key values num (int) and com (varbinary(512))..

My amateur-ish SQLfoo lead me to this non-functioning query (for testing reasons I just selected the duplicates)

SELECT num, com FROM TableA
WHERE num, com IN
    (
        SELECT count(*) AS co FROM TableA WHERE co > 1 GROUP BY num, com
    )

How can I delete duplicates from TableA identified by num and com?

(Working on Microsoft SQL Server Enterprise Edition v9.00.3042.00, i.e. SQL Server 2005 Service Pack 2)

edit

from the suggestions I've tried the following two queries. TableA features ~220 entries, where ~80 are duplicates.
first @dimitars suggestion

select ROW_NUMBER() OVER (PARTITION BY [num], com ORDER BY [num], com) as 'RowNumber', t1.*
    into ##TableA
    from TableA t1

delete t1
--select t1.*
from TableA  t1
join ##TableC t2
    on t2.[num]=t1.[num] and t2.com=t2.com
where t2.RowNumber > 1

resulting in every entry being deleted. And @marcelo miorellis suggestion

select * from TableA;
with cte as (
    select *, row_number() over (partition by [num], com order by [num], com ) as Picker
    from TableA
    )
select cte
where Picker > 1

which gives me

Msg 207, Level 16, State 1, Line 7
Invalid column name 'Picker'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'cte'.


texts: When to use mysqldump with '--no-autocommit'? My benchmark shows slower restore

texts:
I tested quick and dirty --no-autocommit vs. without this option on MacBook (installed with Homebrew latest MariaDB). I restored to an empty MariaDB a 4.6 GB *.sql file. The dump made without --no-autocommit took 5m42.072s to restore and with --no-autocommit it took 1 minute longer.

Other options used for dump are --max_allowed_packet=1G --flush-logs --single-transaction --all-databases

set autocommit=0;
INSERT INTO ...
UNLOCK TABLES;
commit;

When to use --no-autocommit? In what use case this option makes sense?


texts: postgresql query hangs indefinitely over VPN - new firewall

texts:
We have a product where a local linux machine "SCANNER" does some polling on a local network and then stuffs information into a database in our cloud server over an OpenVPN connection, using pg_query (server is running PostgreSQL 9.5.5).

There is a PHP (5.5.9) daemon on SCANNER that checks the database in a 'while' loop for work to be done. This has always worked great, and continues to work great on all of our client networks, except for one, which has recently developed a bizarre problem.

After they upgraded their firewall (a Checkpoint 5200, and as far as we can tell, all the rules are correct to allow traffic from SCANNER to our cloud server over the VPN), one query in one script hangs indefinitely. Here are the symptoms we have noted:

Most of the time, the query works fine and the script continues. Every once in a while tough, the pg_query() call blocks and never returns. It's not that there's an error; the call literally blocks forever (or many hours until we manually restart).

This query has been the same for a long time, and we have never had this problem at any of our other clients, nor at this client until they changed their firewall.

We can tell from watching the pg_stat_activity table on the cloud server that the query does make it to the cloud, and then sits in that table forever. In every case, pg_stat_activity.state='idle' and pg_stat_activity.waiting=false

During this time, we can still ping the cloud server from SCANNER over the VPN, and we can continue to successfully query its remote database from other scripts on SCANNER, and from SCANNER's command line.

This client happens to have two different SCANNER machines, on different subnets but behind the same firewall. This problem can occur at any time on either one, but doesn't necessarily occur at the same time on both (at least not with any statistical significance).

If we restart the daemon, the problem is temporarily resolved. But it usually recurs sometime between 2 seconds and a few hours later.

We are looking for any input that might solve the problem, whether it is related to our application or the firewall itself (which we have been given permission to modify as needed). Feel free to ask any clarifying questions.

Thanks in advance!


texts: pgBouncer - multiple servers with same username but different password

texts:
I have an active pgBouncer setup with one PostgreSQL server.
We are planning to add one more PostgreSQL server to the same pgBouncer. But the problem is that both databases servers have the same username, but with different passwords.
Server1:

user: admin
password: admin123

Server2:

user: admin
password: admin@213

This feature is already requested here, can someone help me with any possible solutions?


texts: MySQL group by multiple columns on multiple tables only one result per group


texts: Why can't I insert this value using timestamp but can with datetime?

texts:
I want to insert this value: 2020-03-29 01:00:00

If I set up a column called date with data type datetime I can insert the value. But if I use the data type timestamp I cannot insert and get an error message:

  Incorrect datetime value: '2020-03-29 01:00:00' for column 'date' at row 1

Furthermore, if I insert 2020-03-29 02:00:00 using data type timestamp it works... seriously, what is going on here?

I am using the GMT timezone. I'm creating a 2020 calendar table where I want to insert every hour in a year. Should I skip that time?


texts: SSMS - Query variables from Table Design Mode > Column Description

texts:
I need to query the description of the Sales.SalesOrderHeader.Status column from the AdventureWorks OLTP, which looks like this:

| SalesOrderID | RevisionNumber | OrderDate  | ShipDate   | Status |
+--------------+----------------+------------+------------+--------+
| 43659        | 8              | 2011-05-31 | 2011-06-12 | 5      |

Instead of the status column being linked as a foreign key to a status table with a description column, the status description appears in the Table Design Mode's column description:

The closest script I've been able to find to query the status description is from Devioblog: Retrieving Table and Column descriptions in SQL Server.

SELECT sys.objects.name AS TableName
  , sys.columns.name AS ColumnName
  , ep.name AS PropertyName
  , ep.value AS Description
FROM sys.objects
  INNER JOIN sys.columns ON sys.objects.object_id = sys.columns.object_id
    CROSS APPLY fn_listextendedproperty(default,
              'SCHEMA', schema_name(schema_id),
              'TABLE', sys.objects.name,
              'COLUMN', sys.columns.name) ep
WHERE sys.objects.name='SalesOrderHeader'
  AND sys.columns.name='Status'
ORDER BY sys.objects.name, sys.columns.column_id

Result:

TableName        | ColumnName | PropertyName   | Description
-----------------+------------+----------------+--------------------------------------------------------------------------------------------------------------
SalesOrderHeader | Status     | MS_Description | Order current status. 1 = In process; 2 = Approved; 3 = Backordered; 4 = Rejected; 5 = Shipped; 6 = Cancelled

The problem with this is that all of the variables are housed as one value in the ep.value AS Description field, but I need to query them separately based on what's in the Sales.SalesOrderHeader.Status column. Pseudo-query:

SELECT ep.value
FROM sys.columns
LEFT OUTER JOIN Sales.SalesOrderHeader ON sys.columns.ep.value=Sales.SalesOrderHeader.Status

Desired Result:

| Status  |
+---------+
| Shipped |


texts: How to Calculate Month over Month User Retention based on already active users (not based on user signup date)?

texts:
I have a Table which tracks User activity (i-e user started a session at what time ?). This table contains the data from Dec 2018 Till now. I need to calculate monthly retention (not based on signup date) based on user Activity( i-e In December 2018, 500 users were active. Then, how many of them were active in Jan,Feb,Mar....Till Now?Same activity should be performed for Jan 2019,Feb 2019... till now users).

I have tried the hard coded way which is to get the users of Dec 2018 in on Table and then Get the Jan 2019 users in other Table and join both tables based on the user_ids, but for that purpose i have to write a lot of joins. Need a dynamic way to check month over month user retention and for all months after Dec 2018(because the data is available from this month onward).

Postrgesql Code #

select A.year_month_id,count(distinct A.user_id) as November_Users,count(distinct B.user_id) as December_Retained_Users
FROM (
        select date_trunc('month', ua.created_at) as monthly,
        ua.user AS user_id
        FROM     user_activity ua
        WHERE    ua.event_type='StartSession'
        and     cast(ua.created_at as date) between cast('20181201' as date) and cast('20181231' as date)
        GROUP BY 1,2
    ) AS A
left Join
    (
        select date_trunc('month', ua.created_at) as monthly,
        ua.user AS user_id
        FROM     user_activity ua
        WHERE    ua.event_type='StartSession'
        and     cast(ua.created_at as date) between cast('20190101' as date) and cast('20190131' as date)
        GROUP BY 1,2
    ) AS B
on A.user_id=B.user_id
group by 1

user_activity Table #

id | user | event_type   | created_at
1  | A1   | StartSession | April 29, 2019, 3:59 AM
2  | A2   | StartSession | December 29, 2018, 1:07 AM
3  | A3   | StartSession | December 9, 2018, 4:59 PM
49 | A31  | StartSession | May 25, 2019, 11:59 AM
100| A46  | StartSession | April 29, 2019, 3:56 AM

Expected Output #

Month |Monthly_Active_Users| Jan_Retained|Feb_Retained|Mar_Retained|.......
Dec   | 500                |  300        |  200       | 330
Jan   | 700                |  N/A        |  450       | 410
Feb   | 1000               |  N/A        |  N/A       | 820
Mar   | 920                |  N/A        |  N/A       | N/A
.
.
.
.
Aug   | 100                | N/A         |    N/A     | N/A


texts: Can I use Backup/Restore to clone a database? Running out of ideas

texts:
I need to clone a database in a single instance of SQL Server 2012.

I have tried the Copy Database tool in SSMS, but it fails with file access error of which I've spent all day reading around to no avail.  I'm running out of time, and I need to get this database cloned, ensuring that the clone is entirely independent of the original.

The source database, DB1 needs to retain much of its data, so Generate Scripts is also no good.

Also my version of SQL does not have the Schema Compare tool.

Can I do a full backup, but then restore to a different database name?  I tried it on a dev machine, but some aspect such as it retaining the original logical name concern me (I might not need to be concerned - please advise!).  Or are there any other ways to do this?


texts:
I have a generic log table, about 5m rows.
There's a "strongly typed" field that stores event type, and a bunch of "losely typed" columns that contain data relevant to the event. That is, meaning of those "losely typed" columns depends on the type of the event.

These columns are defined as:

USER_CHAR1 nvarchar(150) null,
USER_CHAR2 nvarchar(150) null,
USER_CHAR3 nvarchar(150) null,
USER_CHAR4 nvarchar(150) null,
USER_CHAR5 nvarchar(150) null,

USER_INTEGER1 int null,
USER_INTEGER2 int null,
USER_INTEGER3 int null,
USER_INTEGER4 int null,
USER_INTEGER5 int null,

USER_FLAG1 bit null,
USER_FLAG2 bit null,
USER_FLAG3 bit null,
USER_FLAG4 bit null,
USER_FLAG5 bit null,

USER_FLOAT1 float null,
USER_FLOAT2 float null,
USER_FLOAT3 float null,
USER_FLOAT4 float null,
USER_FLOAT5 float null

Columns 1 and 2 in each type are heavily used, but starting from number 3, very few event types would provide this much of info. I therefore desided to mark columns 3-5 in each type as SPARSE.

I did some analysis first, and saw that, indeed, at least 80% of data in each of those columns is null, and in some 100% of data is null. According to the 40% savings threshold table, SPARSE would be a huge win on them.

So I went and applied SPARSE to columns 3-5 in each group.
Now my table takes about 1.8Gb in data space as reported by sp_spaceused, whereas before sparsing it was 1Gb.

I tried dbcc cleantable, but it had no effect.
Then dbcc shrinkdatabase, no effect either.

Puzzled, I removed SPARSE and repeated the dbccs. The size of the table remained at 1.8Gb.

What gives?


texts: SSAS ErrorCode="3239575554" Either the user does not have permissions to alter the 'SERVER' object

texts:
I have just installed SSAS on a server called SASBIO1,
but when I try to run the script below, in order to grant Administrator rights to a specific AD group I get the error message:

<Error ErrorCode="3239575554" Description="Either the 'mycompany\MMiorelli' user does not have permission to alter the 'SASBI01' object, or the object does not exist." Source="Microsoft SQL Server 2014 Analysis Services" HelpFile="" />

This script grants Admin to BIUsersLIVE:

<Batch xmlns='http://schemas.microsoft.com/analysisservices/2003/engine' Transaction='true'>
    <Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
        <Object />
        <ObjectDefinition>
            <Server xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400">
                <ID>SASBI01</ID>
                <Name>SASBI01</Name>
            </Server>
        </ObjectDefinition>
    </Alter>
    <Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
        <Object>
            <RoleID>Administrators</RoleID>
        </Object>
        <ObjectDefinition>
            <Role xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400">
                <ID>Administrators</ID>
                <Name>Administrators</Name>
                <Members>
                    <Member>
                        <Name>mycompany\BIUserslive</Name>
                    </Member>
                </Members>
            </Role>
        </ObjectDefinition>
    </Alter>
</Batch>

The same error happens when I try to do it from SSMS, as you can see on the picture below.

I know I am admin, I am the DBA who installed it.

What is happening?


texts: Can't run query using "localhost\sqlexpress"

texts:
I am trying dynamically build an SQL Statement that includes linked server. That works. When I use "localhost" to reference the current local machine SQL Server, that doesn't work.

For example:

Select * From [LOCALHOST\SQLEXPRESS].[My_Database].[dbo].[My_Table]

However, if I specify my machine name instead, it will work.

Select * From [My_Machine\SQLEXPRESS].[My_Database].[dbo].[My_Table]

Why can't I reference Localhost?


texts: How can I output a legible timestamp from "1583981745.335346" (stored in a 'double' field type) via a select statement?

texts:
I have a table within a WordPress installation that stores timestamps in a format that I do not recognize, nor can I figure out how to translate it. In the MySQL database, it appears as "1583981745.335346". Within WordPress, in the plugin's log, this particular entry reads as follows:

03-11-2020
10:55:47.117 PM

Further examination of the table's structure via phpMyAdmin reveals that this particular column's type is 'double'.

I have done a ton of reading and testing and have even browsed the plugin's code, which can be found below, but I cannot for the life of me figure out how to translate this odd "1583981745.335346" format to a legible timestamp via a select statement.

https://github.com/WPWhiteSecurity/WP-Security-Audit-Log

Searching the files for the column name, 'created_on,' the closest thing I can find is the center of the section beginning on line 2012 of /classes/AlertManager.php:

    // Meta details.
    return array(
        'site_id'    => $site_id,
        'blog_name'  => $blog_name,
        'blog_url'   => $blog_url,
        'alert_id'   => $alert_id,
        'date'       => str_replace(
            '$$$',
            substr( number_format( fmod( (int) $created_on + $this->gmt_offset_sec, 1 ), 3 ), 2 ),
            date( $this->datetime_format, (int) $created_on + $this->gmt_offset_sec )
        ),
        'code'       => $const->name,
        'message'    => $occurrence->GetAlert()->GetMessage( $occurrence->GetMetaArray(), array( $this->plugin->settings, 'meta_formatter' ), $occurrence->_cachedmessage ),
        'user_name'  => $username,
        'user_data'  => $user_id ? $this->get_event_user_data( $username ) : false,
        'role'       => $roles,
        'user_ip'    => $ip,
        'user_agent' => $ua,
    );

Another clue is the following, found on line 62 of /classes/Loggers/Database.php:

$occ->created_on  = is_null( $date ) ? microtime( true ) : $date;

I have not been able to figure out what to do with this information, though, unfortunately. The ultimate goal is to be able to query this field (along with others, including in related tables) using a select statement and output an actual timestamp. Is this possible?


texts: Trigger that updates another table when inserting and deleting from one table

texts:
I need help with modifiying this trigger. I'm new to triggers. When it's executed it is supposed either to delete 1 from nrOfCopies or add 1 to nrOfCopies on a based on the specific isbn(a char(20)), but this does it on all the rows. Becuase its a trigger i cannot use a parameter.

ALTER TRIGGER trgUpdatesNrOfCopies
ON bookcopy
AFTER INSERT, DELETE
AS
BEGIN
   SET NOCOUNT ON

   IF NOT EXISTS(SELECT*FROM INSERTED)
   BEGIN
       PRINT 'DELETE'
       UPDATE book
       SET nrOfCopies=(SELECT nrOfCopies FROM bookcopy WHERE isbn )
   END
   ELSE IF NOT EXISTS(SELECT * FROM DELETED)
   BEGIN
       PRINT 'INSERT'
       UPDATE book
       SET nrOfCopies=nrOfCopies+1
   END
END

The column I need to specify is called isbn. How can I specify it to only make the changes on one specific isbn column?

When this trigger is being fired i get 'Primary key violation' error message. I dont know how to fix this!


texts:
I am designing a query to retrieve the sum of labor hours for a given period, this query works beautifully except when I get to this sonum. All of the values are doubled. I can't for the life of me figure out what is wrong. If I do not join the tables this does not occur. Any help?

SELECT UPPER(`2016`.sonum) as sonum, SUM(`2016`.lbrhrs) as 'lbrhrs', SUM(`2016`.burhrs) as 'burhrs', MONTH(`2016`.date) as 'date', `2016`.dept as dept, closedates.closedate as 'closed date'
FROM `2016`
LEFT JOIN closedates on UPPER(`2016`.sonum)=UPPER(closedates.sonum)
WHERE `2016`.sonum=3470 and month(`2016`.`date`)=1
GROUP BY UPPER(`2016`.sonum), `2016`.dept, month(`2016`.date);


texts: Query returned successfully when obvious error should occur

texts:
I have just installed postgresql and pgadmin on my Windows machine.
The problem is that whatever I enter in query window, it results with message:

  Query returned successfully in XX msec.

Even, when query is obviously incorrect, and should cause an error, no error message is displayed - like on screenshot:

What am I doing wrong?


texts: Migrate from Percona Cluster to MariaDB Cluster

texts:
We are at present running latest version (5.6) of Percona Cluster. Since 5.6 upgrade we are having major stability problems and all minor updates are getting worse.

Is there any possibility of Migrating Percona Cluster to MariaDB Cluster without downtime or with minimum downtime?

Both Clusters run Galera so I am not sure if migrating to MariaDB Cluster will solve problems.


texts:
I have two actors in this transaction.  One actor is a table "update_assets" is a set of records with data that is up-to-date and new. The second actor is a table "application_assets" which is a table used by an application and needs to be updated.  The problem is that the update on this is taking far too long.  The UPDATE is now going on 4 hours.  The transaction is running on a vm with 3 amd cores allocated to it. 8 gb of ram.  The application is not running in the background.

Tables:

update_assets
~3.3 million records
18 columns
1 index on amsid

application_assets
~7 million records
5 columns
1 index on id

Query:

UPDATE application_assets as ams SET number = amu.num, letter = amu.letter
FROM update_assets amu
WHERE ams.id = amu.amsid
AND ams.company_id = '4';

Explain output:

Update on application_assets ams  (cost=219382.14..747965.42 rows=3303562 width=85)
  ->  Hash Join  (cost=219382.14..747965.42 rows=3303562 width=85)
        Hash Cond: ((ams.id)::text = (amu.amsid)::text)
        ->  Seq Scan on application_assets ams  (cost=0.00..244642.25 rows=7883425 width=63)
        ->  Hash  (cost=145825.62..145825.62 rows=3303562 width=55)
              ->  Seq Scan on update_assets amu  (cost=0.00..145825.62 rows=3303562 width=55)

If I removed all of the columns on the update_assets table, except for the pertinent columns for this transaction, will that speed up the transaction?  I noticed that in the Hash Cond portion of the transaction that it does a :: casting operation on each of the columns.  If the columns were already in the text data type format, would the data be updated quicker?  Does postgresql always change data to text data for these types of joins?  What else can I do to expedite this process?


texts:
Where can I find about tutorial of datamining in SQL server 2012 or 2008 R2?

The purpose is to improve my knowledge in datamining.


texts: Help with preventing double bookings with variable lengths

texts:
I'm designing a sample database for a venue where members can create bookings for venues. I'm looking for a method to prevent double bookings (possibly by creating a button on a form that checks availability). I'm aware of the method where times are stored in a separate table (found this by searching on Google), but that doesn't work for me because members should be able to choose what time they'd like to start their booking.

The values that I want to be unique are: the date, the time and the venue room number. For example,

21/12/14, 16:45, Room 4

should not be compatible with

21/12/14, 16:45, Room 4

because all data is the same, but it should be compatible with

21/12/14, 16:45, Room 3

because the room number is different.

Also, I need to find a way where a person can't book the same room while it is being used for someone else. I've thought about using a start and end time for this, but I'm not sure how I could implement it.

If it helps, then I am using Microsoft Access to create this database.

I'd appreciate any help I can get to either problem, and thanks!


texts: Is there any query to fetch the a single row data in chunks?

texts:
The data stored in a row is just like "Phone Number:--- xxxxxxxxxxx Call Type:--- MISSED Call Date:--- 2013-06-13 Call duration in sec :--- 0 Phone Number:--- xxxxxxxxxxx Call Type:--- OUTGOING Call Date:--- 2013-06-14 Call duration in sec :--- 21 Phone Number:--- xxxxxxxxxxx Call Type:--- INCOMING Call Date:--- 2013-06-14 Call duration in sec :--- 79".

Is there any query that may help me to retrieve this row in parts or chunks.


texts: What is a "hashed transaction" in SQL Server Replication terminology?

texts: sys.dm_repl_tranhash contains information about the hashing of transactions, but I have difficulties understanding the terminology. What is a "hashed transaction"?


texts: Oracle SQL NOT IN and NOT EXISTS returning different values. Why?

texts:
I have two tables both containing an ID column which is NUMBER(38).

Table A has ~200k rows which is ALL IDs in existence, while table B has ~15k rows. Table B is a subset of Table A, which means all IDs in B exist in A.

(This is just an example, table names and column names are different and not matching, but I'm trying to make this easier to explain)

I am trying to find all of the records in table A whose ID does not exist in table B:

select ID from tableA where ID not in (select ID from tableB);

Returns 0 rows. This doesn't make sense to me, as I am expecting to get back 85k rows.

When I reverse the statement, I get 15k rows returned which makes sense because B is a subset of A.

Alternatively, I tried using NOT EXISTS:

select ID from TableA A where NOT EXISTS (select ID from tableB B where B.ID = A.ID);

And this result returns 85k records VS 0 from the example above.

I've never seen this behavior before, does anyone know what might be going on and why the NOT IN isn't returning the same results as NOT EXISTS? In this situation, NOT EXISTS is working as expected, but NOT IN is showing 0 records which is wrong.


texts: Oracle 10gR2 to 11gR2 upgrade - performance hit

texts:
We've recently upgraded from Oracle 10gR2 (10.2.0.4 ) to 11gR2 (11.2.0.3) and we are noticing a significant hit in performance although execution plans are the same for the offended queries before and after the upgrade. Allocation of more memory did improve the performance but just slightly. We also tried to set optimizer mode to 10204 at session level but queries produced no better results. The only difference we've noticed is when enabling autotrace the physical reads on 10gR2 were 0 whereas on 11gR2 it was of the magnitude of tens of thousands.

Including stats demonstrating the physical reads discrepancies:

10gR2 Stats
~~~~~~~~~~
Statistics
--------------------------------------------------------
          0  recursive calls
          0  db block gets
      21189  consistent gets
          0  physical reads
          0  redo size
       1014  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

11gR2
~~~~~~
Statistics
------------------------------------------------------
          0  recursive calls
          0  db block gets
      11243  consistent gets
      11232  physical reads
          0  redo size
       1049  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Any ideas/suggestions/right-directions would be greatly appreciated.


texts: Postgresql one master multiple slaves failover

texts:
My problem is same as the one asked in below topic

Streaming Replication Failover - how to point second slave at new master?

Currently I am working on Postgresql 9.5, I have one master and two slave servers, after master crashed and one of the slaves is assigned as new master, I want to change the master of other slave without full base backup.

I tried the steps written in answer, but it does not seem to work, can someone please guide me on solving this problem?

I am writing some details of my configuration, in case it helps.

standby_mode = on
primary_conninfo = 'host=10.10.10.160 port=5432 user=repuser password=*****'
restore_command = 'cp /archivedir/%f %p'
trigger_file = '/tmp/postgresql.trigger.5432'

The 10.10.10.160 is the IP address of new master server.

Also /archivedir folder seems empty in both master and slave servers, is this normal? The below settings are present in the postgresql.conf:

wal_level = hot_standby
archive_mode = on
archive_command = 'test ! -f /archivedir/%f && cp %p /archivedir/%f'
max_wal_senders = 3
hot_standby = on


texts: Should I repeat UNIQUE constraint on similar multi-index?

texts:
The index are on tuples, ordered sets, of columns, but the UNIQUE constraint or qualifiant for an index is on (unordered) sets of columns.

I have MySQL/MariaDB in mind but my questions certainly applies to other DBMS.

If I have a table T with two columns id1, id2 and two index on this two columns:

INDEX idx_id1_id2 (id1, id2)
INDEX idx_id2_id1 (id2, id1)

and I want to declare to the DBMS that the multi-set on all records of the table T of all couples (value(id1), value(id2)), or equivalently all couples (value(id2), value(id1)), is "UNIQUE" (this multi-set is a set),

I can change

INDEX idx_id1_id2 (id1, id2)
INDEX idx_id2_id1 (id2, id1)

for

UNIQUE INDEX idx_id1_id2 (id1, id2)
INDEX idx_id2_id1 (id2, id1)

But I can also declare :

UNIQUE INDEX idx_id1_id2 (id1, id2)
UNIQUE INDEX idx_id2_id1 (id2, id1)

Is there some DBMS that deduce from

UNIQUE INDEX idx_id1_id2 (id1, id2)
INDEX idx_id2_id1 (id2, id1)

that the second index is also unique?

Is there some DBMS that deduce from

UNIQUE INDEX idx_id1_id2 (id1, id2)
UNIQUE INDEX idx_id2_id1 (id2, id1)

that they don't have to do twice the unicity check on inserting/updating value in the table ?

From a declarative point of view, I prefer

UNIQUE INDEX idx_id1_id2 (id1, id2)
UNIQUE INDEX idx_id2_id1 (id2, id1)

but however there may be a performance penalty on insert/update.

If there is a performance penalty for declaring UNIQUE on many indexes, will it be significant ?

Is there any (significant) performance boost for some queries with the second UNIQUE qualifiant added ?


texts: What's the best solution for distribution of one database over two servers?

texts:
I have a MS SQL Server 2014 database with large scale of data an lots of users;
and the RAID level is equal to 10. Now due to the rapid growth of data amount, I'm going to distribute this one database over two servers. What's the best solution for that?


texts: Turning a multiple row resultset into columns with multiple rows

texts:
I currently have a resultset that looks like this:

Title                   Value
Start Time              1998-01-04 01:38:00.000
End Time                1998-01-04 01:43:00.000
Product Name            TESR
Run Time                325
Delay Time              0
Shutdowns               0
Shift                   2
Gross MSF               3.791682
Start Time              1998-08-12 12:43:00.000
End Time                1998-08-13 16:48:00.000
Product Name            FCC
Run Time                320
Delay Time              0
Shutdowns               0
Shift                   1
Gross MSF               3.791682
Start Time              1999-11-04 02:43:00.000
End Time                1999-11-04 03:48:00.000
Product Name            1/2" TESR
Run Time                325
Delay Time              0
Shutdowns               0
Shift                   2
Gross MSF               3.791682

What I would like to end up with is this:

Start Time              1998-01-04 01:38:00.000      1998-08-12 12:43:00.000
End Time                1998-01-04 01:43:00.000      1998-08-13 16:48:00.000
Product Name            TESR                         FCC
Run Time                325                          325
Delay Time              0                            0
Shutdowns               0                            0
Shift                   2                            2
Gross MSF               3.791682                     3.121

Basically looking like a spreadsheet with each group of records being put in a column to the right.

The original result is from a pivot of a flat table joined to a sub table that puts a title to the property fields. It is running on SQL Server 2008 R2. The data is coming from an HMI system and the client wants to see the data in the "spreadsheet" format.


texts: sp_who returns more rows than the value of "max degree of parallelism"

texts:
sp_configure returns the following values on a server.

name                      minimum   maximum config_value    run_value
max degree of parallelism       0     32767            8    8

However, sp_who1 and sp_WhoIsActive show that some spids have more than 8 rows (like 18 or so). Shouldn't it be at most 8 rows in this case?

This query returns a lot of rows from time to time:

SELECT *
FROM sys.sysprocesses sp
where exists
(
    select spid
    from sys.sysprocesses
    where spid =sp.spid
    and sp.waitresource = ''
    group by spid
    having count(*)>9
)


texts:
In the question Is there any (hidden) built-in function on MS-SQL to unquote object names? the author was seeking advice on if there was an internal function to "UNQUOTE" a quoted string. The author McNets had noticed, that some internal functions could handle quoted (e.g. [MyTable]) and unquoted (e.g. MyTable) parameter values passed on (e.g. OBJECT_ID()).

In the answer (1) given by David Browne - Microsoft the quote was made that:

  ...the inverse of QUOTENAME is PARSENAME, which has the additional ability to navigate multi-part names.

I disagreed with this part of the answer with the following comment:

  I do agree that you can modify an argument passed to PARSENAME() to display the parts of a multi-part identifier be it the SERVER, DATABASE, SCHEMA or OBJECT part. But IMHO stating that PARSENAME is the opposite of QUOTENAME is a bit far-fetched.

My question now is:

Is the function PARSENAME() the opposite of QUOTENAME()?


texts: SQL Cluster (Always On) - Database table replication

texts:
I have the following scenario at this moment.
Two old sql servers - they have setup some kind of publisher subscriber replication from one database to another but only some of the tables at a 12 hour time span.
I have built and Always On cluster , where I have created one listener but I have three availability groups, one for each database.
I would like to know how could I implement that sort of replication from one database to another and keep the SQL always On .
Techincal Details - I have WS2K19 Standrad edition with SQL 2017 Standrad edition latest CU , and only two nodes in the SQL cluster and a witness disk to provide qvorum.
SQLNODE1
SQLNODE2
Could maybe someone propose or share a solution ? I want to keep the SQL always on for HA/DR as the two vm's are in different locations .


texts: How can I connect to SQL Server from All IPs?

texts:
I have problem with access to SQL server with TCP/IP.

I would like to SQL instance accept client from all network adapter that machine has.

I go to configuration manager console and enable TCP/IP, set TCP port to 1433 (or any other port) , set “TCP Dynamic Port” to Blank and set “Listen All” to true. After these changes, I restart SQL server service and starting SQL service will fail. For starting SQL service I have to change “Listen All” to false and then service will start successfully.

But after disabling “Listen ALL” you have to set Adapter IP in “IP Addresses” tab and SQL will accept request only from that IP and if you don’t specify any IP, SQL doesn’t accept any TCP/IP request.

Now I need to know why enabling “Listen ALL” will lead to service failure. And how can I resolve this problem?

I ran “netstate –a” and I saw no application has port 1433 on TCP.

When “Listen ALL” was disable and I don’t added any specific IP Yet, I check SQL browser service(UDP 1434) with “portquery.exe” and SQL browser response doesn’t include any TCP port.

After I added 1 specific IP SQL browser service include TCP 1433.

And as I mentioned if I enable “Listen ALL” , SQL service will failure and after restart it can’t start.

After service restart failure I checked windows event viwer and I saw 4 Error as Following :

1.TDSSNIClient initialization failed with error 0xd, status code 0x10. Reason: Unable to retrieve registry settings from TCP/IP protocol's 'IPAll' configuration key. The data is invalid.

2.TDSSNIClient initialization failed with error 0xd, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. The data is invalid.

3.Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

4.SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Some experts tell me may there is a tiny whitespace instead blank in IPALL/TCP Dynamic Ports but When I Checked out that I saw just Blank is there and it is OK.
this screen shot of my TCP/IP properties for more detail :
https://drive.google.com/open?id=0B3zq7J4BJZgMNEdCZEJIeml1RHc

also as you see from above image, All Ips that exist in IP Addresses tab exist and network adapters IPs doesn't changed.I mean I know if SQL listen to non exist Ip it will failed.
Finally , How can  I Listen to all IPs?

Thanks


texts: How will the order of operations in this query affect my results?

texts:
I've written a query, and I'm kinda sure that it does what it want.  I'm thinking, "huh, I wonder if I know how Postgres works?".  I'm going to list a specific assumption about this query below, and if that is wrong, please correct my understanding.

Assumption: The OVER (PARTITION BY) will execute against the data after the final GROUP BY and ORDER BY are executed, thereby limiting the records that are summed to those that are indeed DISTINCT ON (a.id, scl.new_status_id).

Note: This is a Python string that will be formatted into a proper SQL query using a Python library, hence the %s format characters.  If that causes confusion for you, I will update it.  Please assume that I am passing in arguments that make a valid SQL query.

SELECT DISTINCT ON (a.id, scl.new_status_id)
    scl.new_status_id
    ,(scl.change_date AT TIME ZONE %s)::date as d
    ,SUM(COUNT(*)) OVER (PARTITION BY scl.new_status_id
                         order by (scl.change_date AT TIME ZONE %s)::date)

FROM automated_responses_statuschangelogs scl
JOIN application_app a ON scl.applicant_id = a.id
WHERE a.applied_class in %s
AND a.applied_track in %s
AND a.applied_date <= %s
AND a.applied_date > %s
AND scl.new_status_id IN %s
AND scl.new_status_id NOT IN %s

GROUP BY a.id, scl.new_status_id, d
ORDER BY a.id, scl.new_status_id, d desc


texts: Postgres 10. Hide users/roles from other users

texts:
I have a postgresql 10 installed and I want to hide the name of users/roles to other users. I searched through other posts and somebody suggested this:

REVOKE SELECT ON pg_catalog.pg_authid FROM public;
REVOKE SELECT ON pg_catalog.pg_auth_members FROM public;

After doing all of these, user1 can't dig into the info of a user (Error retrieving roles from the database server. ERROR: permission denied for relation pg_auth_members) but still can list them all as you can see on the next image

Of course user1 is not a superuser.

I already tested this with no effect:

REVOKE SELECT ON pg_catalog.pg_roles FROM user1;
REVOKE SELECT ON pg_catalog.pg_user FROM user1;

Tested this too with no effect:

REVOKE SELECT ON pg_catalog.pg_authid FROM user1;
REVOKE SELECT ON pg_catalog.pg_auth_members FROM user1;

If I launch this next statements users can't login and everything is broken:

REVOKE SELECT ON pg_catalog.pg_roles FROM public;
REVOKE SELECT ON pg_catalog.pg_user FROM public;

Is there a way to achieve this? Is very important to me to hide the name of the users to other users.

Background: I'm creating a hacking ctf (capture the flag) virtual machine. This is one of the challenges... the point is avoid a user can see the "right database user name" to connect. That's something he/she must find inside the database finding hints and other cryptographic stuff.

Edit: a "sad" workaround is to create hundreds of dummy users in order to "try to hide" the real one... but it must be an elegant solution for this. Any help?


texts: SQL Server Alert Based On Severity Level Not Working

texts:
I am new to SQL Server alerts, and I am trying to setup an alert That will run a job and send a notification, based on severity level.

In order to test if the alert works correctly I have downloaded a sample corrupted database from MSDN Blogs, and I have restored the database and issued a query against the database which failed with error 824 and level 24.

But although the error is a severe error and was logged to both the SQL log and the Windows Application Log nothing seemed to work, I have even tested with both an alert on the specific database and on all databases. (I have even setup alerts on all other levels from 19 and above just to confirm that nothing is happening.)

However an alert based on error number 824 did work, as well as an alert based on the performance counter's Sql Error counter did worked correctly.

Any suggestions?


texts: high writelog wait on OLTP system and fast SSD storage, log flush is slow

texts:
we have recently upgraded from SQL server 2008 to new servers and upgraded to sql server 2016 (SP1 CU3)

new servers hardware hp proliant 580G8 , 4 socket intel xeon E7 processor, total 60 cores, 640GB ram, 8 intel SSD S3700 Raid 10, local

the environment is a busy OLTP system with around 3K batch request/second mostly insert and to a lesser degree updates. as an OLTP system there is mostly 1 insert/transaction so there are alot of transaction commits. there are some reporting queries for system purposes(queue readers, monitoring, etc)

the problem is log flush performance is slow and we get writelog waits.
i have tested I/O and it can manage over 100K iops/sec in 4k random write test, sequential writes up to 2500MB/sec

databases
compatibility level = 130(sql server 2016)

indirect checkpoint = on,

VLF count = 80-120

processor
cpu usage of 3%-8% , average of 5%, maxed under 10% when log backups runs(compressed backups)
total signal time/total wait time = 6% - 94% resource wait time

memory
PLE is about to tick every second, currently stands for 584000 secs
there is very little page swap observable (pages/sec)

this is the real problem i found

"log flushes/sec" = 1000-2000 /sec

"log flush waits time" = 1000-4000 ms/sec

"log flush write time" = 0-1 ms/sec with spikes of 10-15 ms/sec

"AVG disk sec/write" = 0

"current disk queue" = 0 (with spikes of 2 every few minutes)

"Disk idle time" = 90%-100%

so it seems there's something wrong as sql server is unable to use ultra low latency that SSD array provides...

i tried to disable log writer threads from cpu affinity mask , chris adkin wrote in his blog with no success, also used extended events to monitor log flushes, and saw multiple waits happens for a single log flush.

i don't know if this issue exists on previous servers because on that platform I/O was kinda problem and "log flush write time" was alot higher...

so did you ever see this issue in the field and what is the solution from now on
thanks


texts: Table-based custom type does not verify check constraints

texts:
Is there any way for a table-based custom type to obey the table check constraints, other than creating my own type based on the table?

Assume the following table that serves as a source for the custom type:

CREATE TABLE source
(
  id character varying(20) NOT NULL,
  weight smallint NOT NULL,
  CONSTRAINT source_pkey PRIMARY KEY (id),
  CONSTRAINT source_weight_check CHECK (weight > 0 AND weight < 6)
)

Now, I intend to create the following data table:

CREATE TABLE final
(
  name character varying(20) NOT NULL,
  data source NOT NULL,
  CONSTRAINT final_pkey PRIMARY KEY (name),
)

When inserting records into the final table, the check constraint will not be observed.

Requirements:

I'd like to avoid creating my own type based of source, because when
source changes I'll be forced to remember to also update the custom
type.
I realize that table-based custom types obey domain check constraints. But, on my particular case, the weight field in the source database is ill-suited for a domain. Its check constraints varies from table to table. FYI, it's a value used in calculating weighted averages.


texts:
After upgrading a SQL Server 2008 instance to SQL Server 2012 (in-place upgrade), there appears to be numerous 2008 instance components (namely the 2008 SSMS in start menu, in add/remove programs Microsoft SQL Server 2008 and other items in add/remove programs relating to SQL Server 2008).

How to remove all traces of SQL Server 2008 such that the database is a clean 2012 instance?


texts: pt-kill - how to securely allow access to mysql server when running as service

texts:
I know how to run pt-kill as systemd service:

[Unit]
Description = pt-kill myUser
After=syslog.target mysql.service
Requires=mysql.service
[Service]
Type = simple
PIDFile = /var/run/ptkill.pid
ExecStart = /usr/bin/pt-kill -uxxx -pxxx --daemonize --pid=/var/run/ptkill.pid --log=/var/log/ptkill.log --match-user myUser --match-state "Sending data" --busy-time 640 --kill-query --print
Restart=on-abort
[Install]
WantedBy=multi-user.target

However, this way another system user can see the mysql root password via ps -e -o pid,unit,cmd:

7786 ptkill.service perl /usr/bin/pt-kill -uxxx -pxxx --daemonize --pid=/var/run/ptkill.pid --log=/var/log

I tried adding User=root to the [Service]-section, because root user has protected /root/.my.cnf where passwordless access to mysql is configured. Sadly this doesn't work (I got no permission to access mysql without specifying user... when starting ptkill.service, I think the protected .my.cnf does not get loaded when starting the ptkill.service).
I also tried adding parameters "-umyUser -p`cat /path/to/my/password`" which does not work either.

How to securely run pt-kill as a system service pt-kill and how to securely let it access the mysql server?


texts: How to enforce full integer (no floating point)?

texts:
Why is the following allowed

INSERT INTO c VALUES (123.2,'abcdefghijklmnopqrstuvwxyzabcdef', 'abcdefghijklmnop');

When table contains

CustomerID      NUMBER(3,0) -- That's the 123.2 entry

In other words, total of 3 digits with 0 after floating point?

If number is not a way to go, how would you enforce full integer only (no floating point)


texts:
I have a table that tracks web traffic. The table is constantly written and read from. Since table is pretty big in size I have to make sure the application will not "freeze" in the process of converting the table's storage engine from MyISAM -> InnoDB. Below is what I'm planning to do based on information I could gather from other posts. Please tell me if I'm missing anything.

mysqldump -u -p --lock-tables=false mydb tracking > tracking.sql - from what I understand this should not lock "tracking" table
Rename table name in the sql file to tracking_new
mysql -u -p mydb < tracking.sql
ALTER TABLE mydb.tracking_new ENGINE=InnoDB;
Insert new rows to "tracking_new" table that were written to the original table while performing dump

SELECT @last_id := tracking.id FROM mydb.tracking_new ORDER BY tracking.id DESC LIMIT 1;
INSERT INTO mydb.tracking_new
SELECT * FROM mydb.tracking WHERE tracking.id > @last_id;

Rename tables

RENAME TABLE
    mydb.tracking TO mydb.tracking_old,
    mydb.tracking_new TO mydb.tracking;


texts: Running a SQL Server query with as little impact as possible

texts:
I'm looking to create some basic scripts that are aimed at running on production systems.

I want them to run with as little impact as possible.

They shouldn't affect performance whenever possible, and should be safe to run.

So far I've done this by:

Keeping them small
Keeping them simple
Using WITH (nolock)
Using OPTION (MAXDOP 1)

Is there anything else I could add to limit their impact?

An example of a script I'm looking into is:

SELECT
    cast(SQLProcessUtilization as nvarchar) AS [SQL Server Process CPU Utilization],
    cast(SystemIdle as nvarchar) AS [System Idle Process],
    100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
    DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
FROM
    (SELECT
         record.value('(./Record/@id)[1]', 'int') AS record_id,
         record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle],
         record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization], [timestamp]
     FROM
         (SELECT
              [timestamp], CONVERT(xml, record) AS [record]
          FROM
              sys.dm_os_ring_buffers WITH (nolock)
          WHERE
              (ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
              AND record LIKE '%<SystemHealth>%')
         ) AS x
      ) AS y
ORDER BY
    [Event Time] asc;

Most of these scripts will be used to allow our first line help desk to gather information in a consistent way when requested.


texts: calculating the total number of rows in a table - fails when it is a heap - is there any fix?

texts:
I have a procedure that I use to find out how big is a table.

select * from sys.objects
where name = 'WMS_SLALoad'
-- to get the object_id of my table
-- in this case it is 2041058307

SELECT o.object_id,
       s_name=s.name,
       o.name,
       ddps.row_count
       ,[Statistics_Updated]=STATS_DATE(I.OBJECT_ID,I.INDEX_ID)
FROM sys.indexes AS i
  INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
  INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
  INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
  AND i.index_id = ddps.index_id
WHERE i.index_id < 2
  and o.object_id = 2041058307
go

but when I select from that table, there is always 7 rows less.
my count always tell me the table has 7 rows more that it actually has.

one thing I thought might be the case, is this is actually not a table, it is a heap, because it does not have any indexes
as you can see on the script below.

CREATE TABLE [dbo].[WMS_SLALoad](
    [MessageCycleNumber] [int] NOT NULL,
    [Timestamp] [varchar](50) NOT NULL,
    [Warehouse] [varchar](20) NOT NULL,
    [ProdID] [varchar](8) NOT NULL,
    [QuantityChange] [int] NOT NULL,
    [Sign] [char](1) NOT NULL,
    [Status] [varchar](20) NOT NULL,
    [ReasonCode] [char](2) NULL,
    [InUse] [int] NULL CONSTRAINT [DF_WMS_SLALoad_InUse]  DEFAULT (0)
) ON [PRIMARY]

how can I make sure my script gives me the correct number of rows, whether the object is a table or a heap?


texts: PostgreSQL: Running SELECT DISTINCT ON ~700m rows - how to optimize?

texts:
I have a table that, if we look at just the relevant parts, has two columns: id and raw_data. id is an integer, and raw_data is a text blob. At this point, the table has no constraints or indexes except for an index on id.

My goal is to deduplicate (by id) this data and dump it all to plaintext files (on Amazon S3).

Note that any row with the same id can be assumed to be an exact duplicate (so I only need one, random row's data per id).

The table is on an Amazon EC2 RDS database with 2TB of space, 15GB of RAM. I can expand settings if needed, but want this to run over a reasonable time (i.e. max 24-48 hours, preferably faster).

The queries I'm trying to run (but are too slow) are:

SELECT DISTINCT ON (id) id, data
FROM table
OFFSET <0 through end of table>
LIMIT 250000

The first few offsets run within a reasonable time, but quickly becomes unmanageable (at least minutes to return) when the offset hits 10m+.

Since starting, I've created that id index, removed all other constraints and indexes (there's other columns than I described, but not relevant), set maintenance_work_mem to 4GB (for creating the id index), and most recently tried making the id index a clustered index. But this happened:

cluster id using idx_0;
ERROR:  could not extend file "base/16390/46741.294": wrote only 4096 of
8192 bytes at block 38558630
HINT:  Check free disk space.

Key questions:

1) Is SELECT DISTINCT ON with an OFFSET the right way to do this? Is there a more efficient query for pulling the data?

2) Is there anything else I can do to the DB/table to optimize? Would the clustered index solve my problem? Why is it taking over 1.1TB of extra space to deal with ~800GB of data?

Thanks for any advice!


texts:
Let's say we have a Table in MySQL Database called TAB1 that Contains two Columns col1 : col2 .
the question is :
is there a way to check is any of these Columns Contain a Null or Empty Value?
for the Condition WHERE col_Name is null OR col_Name='' , this Query checks one Column, I want to check all the Columns using a simple Query.


texts:
I have a user which has a default profile in oracle db.
I want to reproduce the following warning
ERROR:
ORA-28002: the password will expire within X days

In dba_profile I set following values.

FAILED_LOGIN_ATTEMPTS            PASSWORD 10
PASSWORD_LIFE_TIME               PASSWORD 1
PASSWORD_REUSE_TIME              PASSWORD UNLIMITED
PASSWORD_REUSE_MAX               PASSWORD 1
PASSWORD_VERIFY_FUNCTION         PASSWORD NULL
PASSWORD_LOCK_TIME               PASSWORD 1
PASSWORD_GRACE_TIME              PASSWORD 1

but however by using above value i am able to generate ORA-28002 error for system user. but for CUSUSER users it is not giving this error.

When i look into dba_users table it shows following values.

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
CUSUSER                        OPEN
SYSTEM                         EXPIRED(GRACE)

I am not able to update account_status for CUSUSER to EXPIRED(GRACE)

Please any one let me know how would i reproduce this error for CUSUSER.


texts: "must be superuser or have the same role to terminate other server processes" with PostgreSQL

texts:
Postgres 9.4.12 locally and 9.4.7 on AWS RDS.

There are two database users: app and untrusted_dev (the latter to mirror AWS RDS: NOSUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION).  app is granted app_user (a group role) permissions.

1) app_user has CRUD permissions on all the normal app tables and functions, but cannot drop tables and so on.

2) untrusted_dev is used for schema migrations.

3) Before/after migration hooks revoke and then re-grant CONNECT privileges for app_user (hence the group role).  The before hook also kicks everyone out:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname='the_app_db' AND pid <> pg_backend_pid();

As I understand it, this separation is good from a security point of view, so that if a user somehow gains control of the app user, their permissions are limited.

And running a migration on AWS RDS (with app users connected) does successfully boot them out.  Yet, running it on a local Postgres 9.4.12 instance (with app users connected) gives the error in the title in response to the pg_terminate_backend() call.

Why?  (I would expect the same behaviour locally and with RDS.  After all, RDS's untrusted_dev's only difference is that it has rds_superuser, but that's not actually a superuser.)

Also, if it's right that I get this error, how should a migration work if you need to boot users, but have no superuser permissions and have a separate login explicitly for the regular users whom you are trying to boot?


texts:
The question is relatively simple. I need to calculate 3 columns where the mid results are huge decimals, and I'm running into a problem early on with SQL Server basically rounding the decimals regardless of any cast / converts.

For example, let's do a simple division as 1234/1233. A calculator will produce 1,00081103000811. But when I do this on SQL Server, we get the following:

-- Result: rounded at 1.000811000... with trailing zeroes up until the 37 precision
SELECT CAST(CAST(1234 AS DEC(38,34))/CAST(1233 AS DEC(38,34)) AS DEC(38,37))

-- Result: rounded at 1.000811
SELECT CONVERT(DECIMAL(38,32), 1234)/CONVERT(DECIMAL(38,32),1233)

-- Correct result at 1,00081103000811
-- But this requires the zeroes to be put in manually when you don't
-- even know the precision of the end result
SELECT 1234.0/1233.00000000000000

Why does this automatic rounding occur? And what's the best way to calculate insanely long decimal values when you can't be sure how big a number (the int or dec part) will be, since the table can contain various different values?

Thanks!


texts: Populating missing data based on previous month-end values

texts:
Given the following data:

create table #histories
(
    username varchar(10),
    account varchar(10),
    assigned date
);

insert into #histories
values
('PHIL','ACCOUNT1','2017-01-04'),
('PETER','ACCOUNT1','2017-01-15'),
('DAVE','ACCOUNT1','2017-03-04'),
('ANDY','ACCOUNT1','2017-05-06'),
('DAVE','ACCOUNT1','2017-05-07'),
('FRED','ACCOUNT1','2017-05-08'),
('JAMES','ACCOUNT1','2017-08-05'),
('DAVE','ACCOUNT2','2017-01-02'),
('PHIL','ACCOUNT2','2017-01-18'),
('JOSH','ACCOUNT2','2017-04-08'),
('JAMES','ACCOUNT2','2017-04-09'),
('DAVE','ACCOUNT2','2017-05-06'),
('PHIL','ACCOUNT2','2017-05-07') ;

... which represents when a given user was assigned to an account.

I am looking to establish who owned a given account on the last day of each month (the assigned date is the date that the account transferred ownership), with any missing month-ends populated (possibly created from a handy dates table that I have available, with useful columns DateKey, Date and LastDayOfMonth, [courtesy of @AaronBertrand])1.

The desired results would be:

PETER, ACCOUNT1, 2017-01-31
PETER, ACCOUNT1, 2017-02-28
DAVE, ACCOUNT1, 2017-03-31
DAVE, ACCOUNT1, 2017-04-30
FRED, ACCOUNT1, 2017-05-31
FRED, ACCOUNT1, 2017-06-30
FRED, ACCOUNT1, 2017-07-31
JAMES, ACCOUNT1, 2017-08-31
PHIL, ACCOUNT2, 2017-01-31
PHIL, ACCOUNT2, 2017-02-28
PHIL, ACCOUNT2, 2017-03-31
JAMES, ACCOUNT2, 2017-04-30
PHIL, ACCOUNT2, 2017-05-31

Doing the initial part of this with a windowing function is trivial, it's adding the "missing" rows that I'm struggling with.