texts: How do I provide a failsafe for retrieval of data from excel to SQL

texts:
So say there is a process in place for importing data from excel spreadsheet to SQL using SSIS. And there are two tables, one with new data and one with data from its previous run.

I sometimes have to run queries comparing data in both tables. Like there is a query which detects any difference in the data between the two tables. The excel import happens on the table to store new data, and at the end of the week, data from this table is copied to the previous run table.

My question is, say if something was to go wrong and transactions failed and now, there is stale data in the system because the import failed or maybe it failed at the step where it copies data to the previous run table, resulting in my queries comparing both tables to return false information (like it might say there is no change in data between the new and old table because of its failure to update one of the tables).

How do you ensure that all your information is relevant in that case?

UPDATE: Here is the detailed explanation of what it is I want.
Step 1 : Table A imports data from Excel. TAble A's previous run tables are replaced with Table A's current contents when new data is filled into table A, same process for Table B too every next day.
Step 2: Table B imports data from Table B last run
Step 3: If successful, table A sends an email out (email pulls data from the Step 4: successfully imported table). Same for B, if B's process is done successfully, it also sends an email pulling data from B.
Step 5: If Table A and B imports went well, a combined email that compares data from both Table A and Table B are sent in this case.

The issue is, I do not want Table B or Table A to go through the import and copy process at all in case of the failure of one stream of processes (let's say for example Table A's flow of work) until it has had the chance to catch up to Table B in the example above. So I want it to somehow be such that no matter where it fails, just re-running the package should bring it back to its correct state without messing up data especially for the comparison emails to be sent with data from both tables. It is proving to be horribly difficult laying out the logic for this. I gathered that I will need to track current run AND last run of the whole work flow. But what's happening is say Steps 3 and 4 had succeeded last time and 1 and 2 had failed, it would end up running 3 and 4 every next run. In that case, I do not want 3 and 4 to run in the next run at all until 1 and 2 had a chance to catch up to them. I hope I have described my situation in great detail here now. Thank you.


texts:
I'm trying to execute the DB2MOVE command at a db2 prompt console in a windows 7 machine (I have db2 client installed, with proper catalogs and nodes created and tested.).

The steps I made was:

Open the console (cmd)
Run the db2cmd command (it is on path) and it opens a new console window
Run the db2 tool which puts me into the db2 console window
Connect to a existent database: connect to mydb user x using x
Try to run the db2move command

db2move MYDB COPY -sn MYSCHEMA -tf C:/Users/MYUSER/Desktop/exp.lst -co
  TARGET_DB TESTDB USER user USING pass SCHEMA_MAP ((MYSCHEMA, MYSCHEMA_TEST)) PARALLEL 2

ps.: there is no enter on the command line

The error I'm getting is:

  DB21034E  The command was processed as an SQL statement because it was not a
  valid Command Line Processor command.  During SQL processing it returned:
  SQL0104N  An unexpected token "db2move MYDB COPY -sn" was found following
  "BEGIN-OF-STATEMENT".  Expected tokens may include:  "".
  SQLSTATE=42601
  db2 =>

I'm strictly following the rules on the docs what am I missing?


texts: Pg_upgrade failed - what does "re-initdb" mean?

texts:
My pg_upgrade command failed. During the upgrade it told me, that if things were failing after a curtain point (which they did) Then I should:

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

But what does this mean?

EDIT

$> /usr/lib/postgresql/9.3/bin/pg_upgrade -b /usr/lib/postgresql/9.2/bin -B /usr/lib/postgresql/9.3/bin -d /var/lib/postgresql/9.2/main/ -D /var/lib/postgresql/9.3/main/ -j 6 -O "-c config_file=/etc/postgresql/9.3/main/postgresql.conf" -o "-c config_file=/etc/postgresql/9.2/main/postgresql.conf"
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is a superuser                       ok
Checking for prepared transactions                          ok
Checking for reg* system OID user data types                ok
Checking for contrib/isn with bigint-passing mismatch       ok
Creating dump of global objects                             ok
Creating dump of database schemas
                                                            ok
Checking for presence of required libraries                 ok
Checking database user is a superuser                       ok
Checking for prepared transactions                          ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows on the new cluster                        ok
Deleting files from new pg_clog                             ok
Copying old pg_clog to new server                           ok
Setting next transaction ID for new cluster                 ok
Setting oldest multixact ID on new cluster                  ok
Resetting WAL archives                                      ok
Setting frozenxid counters in new cluster                   ok
Restoring global objects in the new cluster                 ok
Adding support functions to new cluster                     ok
Restoring database schemas in the new cluster
  template1
*failure*

Consult the last few lines of "pg_upgrade_dump_27132.log" for
the probable cause of the failure.
Failure, exiting

child worker exited abnormally: Success
Failure, exiting

*failure*
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure.

Log:

2014-02-26 01:05:58 CET ERROR:  function pg_catalog._final_mode(anyarray) does not exist
2014-02-26 01:05:58 CET STATEMENT:  CREATE AGGREGATE "mode"("anyelement") (
        SFUNC = "array_append",
        STYPE = "anyarray",
        INITCOND = '{}',
        FINALFUNC = "pg_catalog"."_final_mode"
    );


texts: Create limited admin role in SQL Server that cannot alter row level security

texts:
I would like to know if there is a practical way to create an admin role in SQL Server that can essentially do anything except alter row level security policy.

It is simple enough to create row level security policy that will hide data from admins, but much harder of course to prevent the same admins from altering or circumventing the policy.

I have (I think) a working scenario using CONTROL SERVER and some user-level permissions, but there are caveats (see below) and there may also be vulnerabilities that I haven't thought of.  One complicating factor is the fact that "ALTER ANY SECURITY POLICY" is a database level permission, and cannot be denied at the server level (I don't think?).

Here is what I have so far (assume security policy in question pertains to a db called "SecurityTest"):

USE [SecurityTest]
GO

CREATE LOGIN [LimitedAdmin] WITH PASSWORD = '...';
CREATE USER [LimitedAdmin_usr] FROM LOGIN [LimitedAdmin]

/* Server role */
CREATE SERVER ROLE [srvrole_LimitedAdmin]
GRANT CONTROL SERVER TO [srvrole_LimitedAdmin]

DENY IMPERSONATE ANY LOGIN TO [srvrole_LimitedAdmin]; -- Cannot impersonate a sysadmin or other more priviledged principal
DENY ALTER ANY LOGIN TO [srvrole_LimitedAdmin]; -- Cannot create or modify another login or user (i.e. to create a higher-priviledged login for themselves to use)
DENY ALTER ANY SERVER ROLE TO [srvrole_LimitedAdmin]; -- Cannot tamper with this or other server roles

ALTER SERVER ROLE [srvrole_LimitedAdmin] ADD MEMBER [LimitedAdmin]

/* Database restrictions */
DENY ALTER ANY SECURITY POLICY TO [LimitedAdmin_usr]
DENY ALTER ANY ROLE TO [LimitedAdmin_usr]

It appears that this configuration will prevent "LimitedAdmin" from

Disabling the security policy, i.e. ALTER SECURITY POLICY ... WITH
(STATE=OFF)
Impersonating a login or user with elevated permissions
Creating a login, database role, or user (i.e. to obtain elevated
permissions)
Adding self to a role with higher privileges, i.e
ALTER ROLE [db_owner] ADD MEMBER LimitedAdmin
Creating a stored procedure that will execute as a higher-priviledged user, i.e. CREATE PROCEDURE usp_Malicious WITH EXECUTE AS OWNER...

Caveats

Everything listed in this blog post by Andreas Wolter
Admins defined this way would not be able to manage users, logins and roles, or impersonate other logins.  This seems like the biggest "Catch 22".  Perhaps stored procedures could be used to allow pre-defined user management tasks within specific parameters.
LimitedAdmin could collude with another limited admin by granting him/her permission to alter the security policy, i.e. GRANT ALTER ANY SECURITY POLICY TO [AnotherLimitedAdmin].

Any thoughts on improving (or abandoning) this effort would be greatly appreciated.


texts:
I am new so please go easy on me :)

I have the following table

CREATE TABLE `send_sms` (
    `sql_id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `momt` ENUM('MO','MT') NULL DEFAULT NULL,
    `sender` VARCHAR(20) NULL DEFAULT NULL,
    `receiver` VARCHAR(20) NULL DEFAULT NULL,
    `udhdata` BLOB NULL,
    `msgdata` TEXT NULL,
    `time` BIGINT(20) NULL DEFAULT NULL,
    `smsc_id` VARCHAR(255) NULL DEFAULT NULL,
    `service` VARCHAR(255) NULL DEFAULT NULL,
    `account` VARCHAR(255) NULL DEFAULT NULL,
    `id` BIGINT(20) NULL DEFAULT NULL,
    `sms_type` BIGINT(20) NULL DEFAULT NULL,
    `mclass` BIGINT(20) NULL DEFAULT NULL,
    `mwi` BIGINT(20) NULL DEFAULT NULL,
    `coding` BIGINT(20) NULL DEFAULT NULL,
    `compress` BIGINT(20) NULL DEFAULT NULL,
    `validity` BIGINT(20) NULL DEFAULT NULL,
    `deferred` BIGINT(20) NULL DEFAULT NULL,
    `dlr_mask` BIGINT(20) NULL DEFAULT NULL,
    `dlr_url` VARCHAR(255) NULL DEFAULT NULL,
    `pid` BIGINT(20) NULL DEFAULT NULL,
    `alt_dcs` BIGINT(20) NULL DEFAULT NULL,
    `rpi` BIGINT(20) NULL DEFAULT NULL,
    `charset` VARCHAR(255) NULL DEFAULT NULL,
    `boxc_id` VARCHAR(255) NULL DEFAULT NULL,
    `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `binfo` VARCHAR(255) NULL DEFAULT NULL,
    `meta_data` TEXT NULL,
    `priority` BIGINT(20) NULL DEFAULT NULL,
    `foreign_id` VARCHAR(255) NULL DEFAULT NULL,
    PRIMARY KEY (`sql_id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

I have two different applications talking to each other via these tables.

I would like to count the inserted rows (only 3 in below example, but could be 1000s at a time) and separate then into 3 other existing tables (same format)

So for Example:

INSERT INTO send_sms
( momt, sender, receiver, msgdata, sms_type, dlr_mask, dlr_url )
VALUES ( 'MT','1234', '447XXXXXXXX', 'Hello world4', 2, 27, 'test1' ),
( 'MT','Sender', '447XXXXXXXY', 'Hello world4', 2, 27, 'test2' ),
( 'MT','Sender', '447XXXXXXXY', 'Hello world4', 2, 27, 'test3' );

send_sms1 (would have test1)
send_sms2 (would have test2)
send_sms3 (would have test3)

Would need to be able to support if its not multiples of 3 example if its 10,000 rows send_sms1, send_sms2 should get 3,333 and send_sms3 should get 3,334 (doesn't matter which order). As this is a live system the table needs to be accessed at the same time (so during the move the table must be writable for other insert commands

I have tried things like like

INSERT INTO send_sms2(SELECT * FROM send_sms WHERE sql_id >= (SELECT COUNT(*) FROM send_sms_dump)/2);

the above was just a test to split data- this worked however there isn't any delete so the data wasn't moved. Just 50K copied from the table to the other table

Please point me in the right direction so I can do some more research :)

Update thank you Rick James for the solution.

I am now trying to create an update trigger

DELIMITER $$

CREATE TRIGGER my_trigger AFTER INSERT ON send_sms_dump
FOR EACH ROW
BEGIN
    -- Statement one
    INSERT INTO send_sms_dump2
    SELECT * FROM send_sms_dump
             WHERE sql_id % 3 = 0;
    -- Statement two
    INSERT INTO send_sms_dump3
    SELECT * FROM send_sms_dump
             WHERE sql_id % 3 = 1;
    -- More UPDATE statements
    INSERT INTO send_sms_dump4
    SELECT * FROM send_sms_dump
             WHERE sql_id % 3 = 2;
    -- More UPDATE statements
    DELETE FROM send_sms_dump;
END$$

I have the above the error is ... /* SQL Error (1442): Can't update table 'send_sms_dump' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. */ ...

I am guessing its because of the delete command, i didn't want to drop the table as the third party application will be writing again to that table - let me know if i should post a new question :)

thanks guys


texts:
I have SQL Server in which I need to upgrade and restore a .bak file

Server 1:

  Microsoft SQL Server  2000 - 8.00.194 (Intel X86)

And by requirements I need it to match Server 2:

  Microsoft SQL Server  2000 - 8.00.2040 (Intel X86)

I have the SQL2000-KB899761-v8.00.2040-x86x64-ENU.exe, but it says I need a previous update.

This version is too old and I can't figure where to start.

Actually what I really need is restore a .bak file from Server 2 and put it to Server 1.

I can't because it says:

  ... is not a valid Microsoft Tape Format

I can restore it within a 2008 Server. The issue is when I try to restore to the older Server 1.


texts: Select Multiple Values from Same Column; one sql statment

texts:
I am looking to return a list of users from a database. There are hundreds of users in the db. Each user has it's own unique user_id. All of the values that I am trying to return reside in the same column (meta_value).

The database structure is as follows:

id | user_id | meta_key | meta_value

sample data is as follows:

1 | 3434 | first_name | Brandon
2 | 3434 | last_name  | Johnson
3 | 3434 | street_add | 123 main
4 | 3434 | city       | ocean beach
5 | 3434 | state      | Texas

I am trying to return the first name, last name, street_add, city, and state in one sql statement.

I'd like the output to look like:

Brandon, Johnson, 123 Main, Ocean Beach, Texas


texts: PostgreSQL: difference between collations 'C' and 'C.UTF-8'

texts:
In PostgreSQL, what is the difference between collations C and C.UTF-8?

Both show up in rows of pg_collation. Is it perhaps the case that C.UTF-8 is the same as C with encoding UTF-8 regardless or what the actual encoding of a database is?


texts: Can't restore mysqldump database backup in Ubuntu 12.04

texts:
I'm trying to restore a database backup created using mysqldump. If I do:

    $ mysqldump -u ROOT -p DATABASE_NAME < DATABASE.SQL

the database remains empty, even though I get some screen output and the line "Dump completed...", followed by a timestamp. I could not spot any error messages in the output

I made sure I quit MySQL before attempting the restore and I have tried to include the password in the command line, with no success. However, if I try:

    mysql>source /path_to_dump_file/DATABASE.SQL

it works just fine.

Any thoughts?

Many thanks in advance.


texts: How to tell if Postgres was built with the option to include LLVM technology for JIT?

texts:
Postgres 11 gained the ability to perform Just-in-Time Compilation (JIT) based on LLVM technology.

The manual notes that this feature depends on Postgres having been built with the --with-llvm option.

➥ How can one verify their build of Postgres includes LLVM?

Assuming LLVM is installed… In Postgres 11, JIT is disabled by default. In Postgres 12, the feature is enabled. That raises the question:

➥ How can one tell if JIT is currently enabled or disabled?


texts: Is it possible for a client to kill a long-running Postgres query?

texts: A client makes a request to a Postgres database for data. The database is too slow to respond. Is it possible for the client to send a CANCEL request or something to tell Postgres to stop working on the query?


texts: MySQL: How to perform aggregate functions on rows where a column is duplicate, for all values of said column

texts:
I have a table where one of the columns may have duplicate entries, and I want to condense each set of duplicates into a single row, with an aggregate function applied to the other columns. I cannot think of a sensible explanation of this, so here is an example data table and result table

+------------------------+--------------------+
| column_with_duplicates | column_to_aggregate|
+------------------------+--------------------+
| 1                      | 1                  |
| 1                      | 2                  |
| 2                      | 3                  |
| 3                      | 4                  |
| 3                      | 5                  |
| 3                      | 6                  |
+---------------------------------------------+
output

+------------------------+--------------------+
| column_with_duplicates | column_to_aggregate|
+------------------------+--------------------+
| 1                      | 1.5                |
| 2                      | 2                  |
| 3                      | 5                  |
+---------------------------------------------+

All of the rows where column_with_duplicates is 1 had column_to_aggregate averaged together into a result row. The same happened for all values of column_with_duplicates

Since I had so much difficulty describing this, I couldn't effectively research the issue.


texts:
I was playing around on a test server and i actually removed both "frm" and "innodb" files of particular table. Ok - I tried creating that table again but got information that table already exists and that i should discard it's table space. I wasn't able to discard table space because there were no underlying files, i've got error that table doesn't exist. Creating new table (or simply doing copy from other server) won't work too.

In despair i tried removing any mention of that table in "information_schema", it seemed obvious - removing any trace that table ever existed but "information_schema" is read only :(

What should i do to actually fix this issue?


texts: How to succesfully run a batch file in an SQL Agent job?

texts:
I have a SQL Agent Job which generates a specific report in PDF-file and then copies the PDF to a network directory and then deletes the PDF file in the source directory.

The SQL Jobs consists of 2 steps:
1. Generate the report
2. Copy the report to the network location.

For step 2 I made a bat-file which handles the copying and removal of the pdf file.

The bat-file is as follows:

set source_dir=C:\Reports\Energie\Uur
set dest_dir=\\KWS2-WEB-SERVER\Share\Reports\Uur

C:\Windows\System32\Robocopy.exe %source_dir% %dest_dir% /MOV /Z

However, when I run my the Job, it hangs on the second step. The status just stays on "Executing".

This is the line which I stated in the step (location of the bat-file to execute):

cmd.exe /c "C:\Reports\rapport_uur_copy.bat"

My job-settings are as follows:

Step 1

Type: Operating system (CmdExec)
On Success: Go to the next step

On Failure: Quit the job reporting failure

Step 2

Type: Operating system (CmdExec)

On Success: Quit the job reporting success

On Failure: Quit the job reporting failure

Some facts:

I have read/write permissions on the network directory
I run the steps as the Administrator-account (the logged in user, default)
Step 1 succeeds
I run Windows Server 2008 R2 Standard
I have SQL Server 2008 R2
When I run the script from the cmd manually, it works (under Administrator account).


texts: I have an SQLite database and a LibreBase interface, my interface in LibreBase will not work because it says "Database does not support relations" and "Database does not support FULL or LEFT Joins". I have tried both ODBC and JDBC Drivers for SQLite3 in both LibreBase and Open Office and also enabled the FKey's. The interface worked, with some adjustments, in both Postgres and HSQL. Pretty sure SQLite3 supports all these functions; any ideas as to what is going wrong?


texts: which process has created a load spike on my server last week?

texts:
O.S.:Linux
MYSQL server

we got a load spike last week.

how can I find the process that has created the load spike on my server last week.


texts: DB design for single Dependent table column to multiple Primary table

texts:
I have a scenario where records in the dependant table(say History) are created by two entities, one User and other Application. For which my purposed design is
User
-----------------
UserId (PK) BIGINT NOT NULL
Name NVARCHAR(200) NOT NULL
...

Application
-----------------
AppId (PK) BIGINT NOT NULL
Name NVARCHAR(200) NOT NULL
...

History
-----------------
HistoryId (PK) BIGINT NOT NULL
RelatedTableId BIGINT NOT NULL
RelatedTableType VARCHAR(20) NOT NULL
...

With the above design, I can not have FKs (declarative RI) which is one concern and not sure what is the limitation in the future. What would be the best design for this scenario? Looking for a better alternative before its too late.
The closes StackOverflow Q/A  is this(dbfiddle) but I still want to know for this particular case. I am also hearing from other sources that we can use the trigger to enforce RI together with some codetype and codevalue design which I do not understand.
I am using MSSQL 2016 or above.


texts: Performance difference in accessing differrent columns in a Postgres Table


texts: Disable console output when executing a SQL file using MySQL source command

texts:
I begin by launching the MySQL command line tool:

mysql --user=myusername --password=mypassword --silent --force -b

The last -b option is used to disable beep on error.

Then I choose a database:

use Mydatabasename;

Then I execute SQL form a file like this:

source c:\x\y\z\myfile.sql

That's when things go slowly. I've executed part of the file before so the console is filled with duplicate row errors which slow execution badly. I get 5-10 statements executed per second. Without duplicate rows the code executes tens of thousands of statements (30k+) every 5 seconds.

I need to do this since the file is large and I can't execute it in one go.


texts: How can I avoid writing to the Transaction Log file when I drop, create and reload data to a table?

texts:
I need to create a couple of snapshot tables that will export a large amount of data.  While running a few tests, I need to create the table, import data, inspect the results, update the export query and then delete all the data in the table and start over.

The table, when finished, will be moderately large but dropping and reloading the data again and again is making the transaction log file MASSIVE.

What I would like to do is cut off transaction logging for these files until I'm satisfied with my results, or clear the transaction log for the table.  To be clear, I do not want to cut off logging for the entire database.  This is a shared, developer DB and we need to keep the log running.

I need to create a snapshot process that pulls certain data from a massive audit table on our production server.  I want to move the pertinent data to our dev server, and create a process that can run nightly/weekly to get the most recent changes.  However, I need to do an initial population from the full audit table.  I'm new to the company and I do not know anything about their data structures.  Thus, I need to pull the data, do a bit of analysis and then fine-tune my queries.

Can this be done?  I've tried to Google this multiple ways and multiple ways but I can't find anything related to table-specific transaction log settings.


texts: Migrating Oracle smallfile to bigfile tablespace

texts:
I am wanting to migrate all tablespaces in an existing db to use bigfile tablespace rather than the smallfile tablespace. The Oracle documentation hints that this is possible but doesn't explicitly show how to do it.

Any help/pointers would be greatly appreciated.


texts: Mapping a shared physical folder to filestream

texts:
We are working on a project where we have large amounts of images, texts etc which we need to work on in SQL Server. We were thinking of using Filestream and filetable functionality. However, even after trawling through a lot of data on the net, we aren't able to find answers to the below queries:

We are trying to create a shared folder on our system and then map it to the filestream. So we are creating a shared folder (lets say D:\Ftable ) which has access level set to full access for everyone. Now when we go to the SQL Server configuration manager and try to set this folder as shared folder for filstream (the properties tab where we enable the filestream access) it throws us error : The windows share folder name is not a valid windows share. We know that filestream folder has to be a local folder, however even when we just try to put a different local folder path (like D:\test etc) it gives us an error.
We would also like to know if it is possible to create a filestream shared directory on a mapped drive? We have created a shared folder on one system and mapped that folder on a different system (so that it appears as local). However once again, when we try to set a specific folder on the mapped drive as filestream shared folder, it throws us an error.

Would really appreciate any pointers/help on the topic. Our main access is to create a filestream share on a physical location of our choice which is shared so that we can transfer data to it either remotely/ or via a tool.


texts: Any possible way to get a progress estimate for ALTER COLUMN TYPE in Postgres?

texts: I know that there is currently no officially supported way, but I'm nearly two hours in of 100% CPU on a 15,000,000 row table changing an INTEGER to a SMALLINT and I have absolutely no idea how long this will keep going on for. Is there maybe some file that I can watch growing on the filesystem? Does it rewrite the table in place, or create a new file? Is there a way to pause this so that I can run a benchmark on the same machine with fewer rows, so I can get an estimate of rows per second for this operation?


texts: Find Uncompressed/Compressed Sizes of TimescaleDB Database/Table

texts:
For a database/table in TimescaleDB and PostgreSQL 11 running on ZFS with both ZFS compression and native TimescaleDB compression enabled, how can you find the uncompressed and compressed sizes when

using native TimescaleDB compression and ZFS compression
using native TimescaleDB compression but not ZFS compression
not using native TimescaleDB compression and using ZFS compression
not using native TimescaleDB compression and not using ZFS compression

I want to use these values to find out the compress ratio due to TimescaleDB native compression and due to ZFS


texts:
I am planning an upgrade of a legacy MySQL5.6 Database to MYSQL8.0.19 database.

All my testing has been successful but have one issue preventing me from going live.

By default in MYSQL5.6 the following statement would return true

SELECT 'a ' = 'a';

But in MySQL 8, it returns false.

I understand that this has to do with the default collation on the database.

But when I try change it to

ALTER DATABASE **** CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

This collation supports PAD SPACE, however it does not return the functionality back to how it was in 5.6

My issue is that there are a lot of stored procedures that rely on the old functionality and would take a long time to change it.


texts: Ideal way to represent a Grade -> Section -> Student -> Marks database

texts:
Although it's marks for now, I'm going to change the last entity to something else later. So I need every mark of every student in one table. What I'm thinking is for every mark to belong to one student, every student to belong to one section, and every section to one grade.

I'll be analysing the marks in different ways too, i.e, I want to present information on all marks under a particular student, all marks under a particular section, and all marks under a particular grade.

One thing is to store as what I said in the first paragraph. If I want to work on every mark of every student in a grade, I must select, for each section that belongs under the grade, all marks such that the student who owns the mark must be in that section.

However in that case I'm wondering if the queries might take too long. (I'm new to db so I'm not sure). Or should I, for every mark, also have a column for its section and its grade, so that querying under a given grade/section/student is easy?

Thanks


texts: Retrieve data from OLTP MySQL to SQL Server DWH using SSIS

texts:
I'm running a BI solution currently reading data from an OLTP SQL Server 2008 to a SQL Server 2012 data warehouse.

The ETL phase is implemented with SSIS and runs twice a day. This phase relies on a database shapshot of the source database, which is created on the fly as the first step of the ETL procedure.

Now, my client plans to move his OLTP database to a MySQL 5.7 on Amazon RDS solution, while keeping the data warehouse on SQL Server on premise.

I'm no MySQL expert, but did some searches and it doesn't seem to me that MySQL has a feature similar to "database snapshot" as implemented in SQL Server (which is not replcation nor a backup/restore, but something much more lightweight and easier to setup).

Is that true? If that's true, are there best practices / patterns about pulling data from MySQL via SSIS?

My requirement is to leave the online database as alone as possible, reading from a somewhat "offline" version of the database, just like we do now using SQL Server snapshots.

Should I use replication, are there AWS infrastructure features that could help?


texts: Partitioning a table will boost the performance?

texts:
I have a very large table approx 28GB and that is continually increasing.   I am thinking about partitioning my table.

The table is InnoDB and The File Per Table is enabled

I have a field name created which is an integer field and stores the timestamp so I was thinking about creating the table like this:

ALTER TABLE TABLE_NAME PARTITION BY RANGE (created)
(
PARTITION p0 VALUES LESS THAN (1325356200) ENGINE = InnoDB, # Data before 2012
PARTITION p1 VALUES LESS THAN (1333218600) ENGINE = InnoDB, # Data for JAN,FEB,MARCH YEAR 2012
PARTITION p2 VALUES LESS THAN (1341081000) ENGINE = InnoDB, # NEXT THREE MONTH DATA FOR YEAR 2012
PARTITION p3 VALUES LESS THAN (1349029800) ENGINE = InnoDB, # NEXT THREE MONTH DATA FOR YEAR 2012
PARTITION p4 VALUES LESS THAN (1356978600) ENGINE = InnoDB, # NEXT THREE MONTH DATA FOR YEAR 2012
PARTITION p5 VALUES LESS THAN MAXVALUE              # DATA for Next years
) ;

I have partitioned the year 2012 in Quarters(As it is our requirement) and will also do the same for year 2013 but not done here.

  Questions:

  Does partitioning boost INSERTs and SELECTs Performance
  How much time the ALTER take as it is a huge amount of Data(Any way to minimize the Alter time)?
  Do I need to use the field created in the queries so that the optimizer can use the partition?
  Are there any limitation's or risks by partioning this table?
  Is there any way to ALTER the table without downtime?


texts:
I'm using SQL SERVER 2017 GDR version 14.0.2002.14. I want to upgrade to the latest possible version of SQL Server 2017.

My question is can I download CU-17 and directly run the latest cumulative update which is CU-17 version 14.0.3238.1? OR do I need to reinstall SQL Server 2017 RTM version 14.0.1000.169 first?


texts: What is the story about Oracle CREATE TRIGGER ... CALL syntax?

texts:
I was able to use the CREATE TRIGGER ... CALL SOMEPROCEDURE; syntax but I'm a little bewildered about it.  I expected to see some implicit arguments like TABLE%ROWTYPE new but that doesn't seem to be the case.

The documentation doesn't mention this at all.

In a two-year-old post some stackexchangers call the construct "paleolithic."  Is it really that worthless?  Was there anything such a trigger was ever able to do?  Or is there a form with arguments and context and such?


texts: Changing the default foreign key constrain of a schema

texts:
The default foreign key contrain of MariaDB ON UPDATE and ON DELETE is RESTRICT.

However, in my schema, most foreign keys have ON UPDATE / ON DELETE set to CASCADE.

Although I appreciate the expliciting advertising of an occasional automatic deletation behavior, I find it annoying and slightly counter productive when the default behavior has to be explicitly typed for so many columns.

Is there an alternative to changing the default behavior per column, such as changing the default behavior of foreign keys ON UPDATE / ON DELETE constrains for an entire schema or table?


texts:
Recently I started looking at SQL Server ANSI_Padding setting. After reading BOL and some online articles, I find myself with more questions and confusion than I started with.

I understand that I would be better off sticking to ANSI_Padding On, and I understand the different behaviors with NULL and NOT NULL columns when the setting is changed to ON or OFF.

But I cannot grasp this concept. What purpose does padding serve? Doesn't appending zeros to a binary number (or spaces to a character string) change the value?

Binary number 0x0000ee is equal to 0x00ee (at least when it is converted to decimal) But how is 0x00ee00 the same as 0x00ee?

A string 'a ' (1 space) is not the same as 'a' (no space), and yet they both become 'a   ' (3 spaces) in a char(3) column. How is that even acceptable?

Same confusion also exists when values are trimmed.

BOL does seem to mention that ANSI_Padding is used to control how values are stored. So my initial guess was the actual values are not changed. But BOL does not elaborate on this point, nor do any of the articles I have found so far.

Thanks for your help.


texts: What are the best practices for running SQL Server in a Virtual Machine? My on-line transaction activities are very low, but there is a high amount of data processing for the purpose of providing reporting data to multiple web sites.


texts: Where clause over multiple rows, GROUP BY

texts:
I have the following table structure

AccountID   Property            Value
123456      Status              Active
123456      City                Los Angeles
123456      RegistrationDate    2018-05-11 11:30:14.000
543210      Status              Active
543210      City                Las Vegas
543210      RegistrationDate    2018-05-11 11:13:14.000
888888      Status              Inactive
888888      City                Toronto
888888      RegistrationDate    2015-05-12 11:13:14.000

I want to be able to Select all rows where Property == Status and Value == Active And Property == RegistrationDate and Value == 2018-05-11 11:30:14.000

Im figuring I need to do some kind of GROUP BY and then select on that aggregation but I can't get my head around it(my sql is really rusty).

I want the following output for the above "query"

AccountID    Status    RegistrationDate
123456       Active    2018-05-11 11:30:14.000
543210       Active    2018-05-11 11:30:14.000

If all the columns had existed on the same row I would just write something like this:

SELECT AccountID FROM Property
WHERE Status = 'Active'
AND RegistrationDate = '2018-05-11 11:30:14.000'


texts: Slow fulltext search for terms with high occurence

texts:
I have a table that contains data that is extracted from text documents. The data is stored in a column called "CONTENT" for which I have created this index using GIN:

CREATE INDEX "File_contentIndex"
  ON "File"
  USING gin
  (setweight(to_tsvector('english'::regconfig
           , COALESCE("CONTENT", ''::character varying)::text), 'C'::"char"));

I use the following query to perform a full text search on the table:

SELECT "ITEMID",
  ts_rank(setweight(to_tsvector('english', coalesce("CONTENT",'')), 'C') ,
  plainto_tsquery('english', 'searchTerm')) AS "RANK"
FROM "File"
WHERE setweight(to_tsvector('english', coalesce("CONTENT",'')), 'C')
  @@ plainto_tsquery('english', 'searchTerm')
ORDER BY "RANK" DESC
LIMIT 5;

The File table contains 250 000 rows and each "CONTENT" entry consists of one random word and a text string that is the same for all rows.

Now, when I search for a random word (1 hit in the whole table) the query runs very fast (<100 ms). However, when I search for a word that is present in all the rows, the query runs extremely slow (10 minutes or more).

EXPLAIN ANALYZE shows that for the 1-hit search a Bitmap Index Scan followed by a Bitmap Heap Scan is performed. For the slow search a Seq Scan is performed instead, which is what is taking so long.

Granted, it is not realistic to have the same data in all rows. But since I can't control the text documents that's uploaded by the users, nor the searches they perform it is possible that a similar scenario arises (search on terms with very high occurrence in DB). How can I increase the performance of my search query for such a scenario?

Running PostgreSQL 9.3.4

Query plans from EXPLAIN ANALYZE:

Quick search (1 hit in DB)

"Limit  (cost=2802.89..2802.90 rows=5 width=26) (actual time=0.037..0.037 rows=1 loops=1)"
"  ->  Sort  (cost=2802.89..2806.15 rows=1305 width=26) (actual time=0.037..0.037 rows=1 loops=1)"
"        Sort Key: (ts_rank(setweight(to_tsvector('english'::regconfig, (COALESCE("CONTENT", ''::character varying))::text), 'C'::"char"), '''wfecg'''::tsquery))"
"        Sort Method: quicksort  Memory: 25kB"
"        ->  Bitmap Heap Scan on "File"  (cost=38.12..2781.21 rows=1305 width=26) (actual time=0.030..0.031 rows=1 loops=1)"
"              Recheck Cond: (setweight(to_tsvector('english'::regconfig, (COALESCE("CONTENT", ''::character varying))::text), 'C'::"char") @@ '''wfecg'''::tsquery)"
"              ->  Bitmap Index Scan on "File_contentIndex"  (cost=0.00..37.79 rows=1305 width=0) (actual time=0.012..0.012 rows=1 loops=1)"
"                    Index Cond: (setweight(to_tsvector('english'::regconfig, (COALESCE("CONTENT", ''::character varying))::text), 'C'::"char") @@ '''wfecg'''::tsquery)"
"Total runtime: 0.069 ms"

Slow search (250k hits in DB)

"Limit  (cost=14876.82..14876.84 rows=5 width=26) (actual time=519667.404..519667.405 rows=5 loops=1)"
"  ->  Sort  (cost=14876.82..15529.37 rows=261017 width=26) (actual time=519667.402..519667.402 rows=5 loops=1)"
"        Sort Key: (ts_rank(setweight(to_tsvector('english'::regconfig, (COALESCE("CONTENT", ''::character varying))::text), 'C'::"char"), '''cyberspace'''::tsquery))"
"        Sort Method: top-N heapsort  Memory: 25kB"
"        ->  Seq Scan on "File"  (cost=0.00..10541.43 rows=261017 width=26) (actual time=2.097..519465.953 rows=261011 loops=1)"
"              Filter: (setweight(to_tsvector('english'::regconfig, (COALESCE("CONTENT", ''::character varying))::text), 'C'::"char") @@ '''cyberspace'''::tsquery)"
"              Rows Removed by Filter: 6"
"Total runtime: 519667.429 ms"


texts:
I am noticing a strange problem with DB mirroring on SQL Server 2012 (standard edition). The mirroring is set up, and successfully established, and working. There is no witness.
Once principal is rebooted, all the databases are stuck in recovery mode, thus making the DBs inaccessible. Same thing happens with mirror, when the server is restarted, the DBs are stuck in recovery. In order to get out of the recovery, the endpoint needs to be stopped and started again. I've checked the following:

Mirroring is working during normal setup.
Accounts have permissions to establish mirroring.
All DBs have owners that is sa.
SQL Server is running under domain account and the owner is sysadmin on the server. I also gave it explicit grant/authorization to the endpoint.
The error log (startup) says that the endpoint is started.

In order to make the DBs accessible again, the following code is executed:
ALTER ENDPOINT mirroring STATE = STOPPED

ALTER ENDPOINT mirroring STATE = STARTED

After executing the above query, DBs come back to normal state.
Startup error log:

2016-04-03 14:03:24.900   spid18s Database Mirroring Transport is disabled in the endpoint configuration.

2016-04-03 14:03:30.240   spid28s The Database Mirroring endpoint is now listening for connections.

Followed by Database Mirroring is Active with Database 'DatabaseName' as principal copy.
After the above code is executed:

2016-04-03 14:12:42.500   spid67  The Database Mirroring endpoint has stopped listening for connections.

because I am stopping it

2016-04-03 14:12:46.530   spid67  The Database Mirroring endpoint is now listening for connections.

because I am starting it
At this point, Database mirroring is active with database 'DatabaseName' as principal copy shows up again.
Thanks in advance.


texts: PostgreSQL import CSV File causes Syntax Error

texts:
I'm attempting to import a CSV file into a database via the "COPY" command; however, I get the (what seems common) error that I need to be a superuser and that I should use "\copy" instead. However, when using \copy, I get a syntax error:

ERROR:  syntax error at or near "\"
LINE 1: \copy

With the caret pointing to "\". Here's my query:

\copy tablename(column2, column3, column4, column5) from '/home/uploads/data.csv' WITH DELIMITER ',' CSV HEADER'

I tried both "copy" and "\copy". The first giving me a superuser error the latter giving me that syntax error. Any idea on how to fix it? make it work?

I'm executing the command via myPgAdmin's sql input field.

The only other question I have is concerning the importing of the columns via tablename(column2, column3 and so on. Is that the correct syntax for that?


texts: Avoid update/insert conflict in foreign key using SNAPSHOT isolation

texts:
Question is:

Is there any way to avoid conflict in SNAPSHOT isolation when setting foreign key to table updated/inserted in another transaction, having in mind I never use delete?

I mean something like query OPTION or HINT or connection parameter, maybe database setting?

Example:

Field Customers.PriceDefinitionId has relation to PriceDefinitions.Id which is a clustered PK. I can't easy change PK/indexes and structure because it is third party database (in reality many of them on multiple machines) and my changes could be overwritten.

# Transaction 1

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION

UPDATE PriceDefinitions SET Active = 0 WHERE Id = 1;

# Transaction 2

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION

UPDATE Customers SET PriceDefinitionId = 1 WHERE Id = 1

Then, COMMIT transaction 1 gives in second:

  Msg 3960, Level 16, State 2, Line 4
  Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.PriceDefinitions' directly or indirectly in database 'APP' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

Repeated question:

Is there any way to avoid conflict in SNAPSHOT isolation when setting foreign key to table updated/inserted in another transaction, having in mind I never use delete?

I mean something like query OPTION or HINT or connection parameter, maybe database setting?

Please avoid answers mentioning changing structure or indexes/PK, because of third party database (in reality many of them on multiple machines).

Question is similar to: Why am I getting “Snapshot isolation transaction aborted due to update conflict”?, but there is only an explanation to the nature of behaviour in case of DELETE, which I'm not using.
Also i would like to have any solution to the problem, not only why is that.


texts: Difference Between Sign In and Login (Users and Groups)

texts:
I am trying to understand a paragraph from my notes, re Users and Groups in Active Directory. The phrase is:

  Users and Groups Sign in, they do not login.

Now, what I (think I) understand is that logins give access to SQL server, Users are given access to specific databases. But I have not been able to find anything that seems reliable (or barely anything) about sign ins. Could someone please explain to me or give me a ref on what signing in is about and how it is different from logins?
Thanks.


texts: Will deadlocks occur when deleting rows from a table when being added to by another process?

texts:
I have to archive data from a table into another table whilst the original table is in use.  The archival process is to copy the rows into another table and then delete the rows from the original table.  The rows to be archived are selected by a column that has the Identity property.

A factor is that the table from which the rows are being archived will be in use - new rows will be written into the table as the archival process is running.  This is a 24/7 application that cannot be stopped.

The rows that are being deleted are at the low end of the Identity column numbering whilst the new rows will be being added at the end of the table.

My concern is deadlocks.  Is there a chance that deadlocks could occur between the 2 processes?

The archival process will be run from a Stored Procedure?  Should I set the DEADLOCK_PRIORITY to LOW to ensure that if a deadlock does occur that the archival process will be killed and not the running application?

Should I also set TRANSACTION ISOLATION LEVEL?

Thanks


texts: Can syslog be configured for Oracle Database Cloud Service?

texts:
Short Version:
If I create a Cloud Database (on Oracle Database Cloud Service) -

1. Can it log to Syslog?
 2. Can I set where that syslog is located
    (i.e., at which host:port)?

Long Version:
If an Oracle Database runs on a machine, then I can direct its logs (e.g., audit logs) to either a non-default directory, or to a centralised syslog on another machine.

How can I do the same with DBaaS like Oracle Database Cloud Service?


texts:
I have two tables in psql that I am trying to join based on certain attributes - they are very large - 17 M rows and 2.7 M rows respectively

mydb=> SELECT reltuples::bigint AS estimate FROM pg_class where relname='foo';
 estimate
----------
17087196
(1 row)

mydb=> SELECT reltuples::bigint AS estimate FROM pg_class where relname='bar';
 estimate
----------
  2763829
(1 row)

On both, I have created spatial indices with

CREATE INDEX foo_gix ON foo USING GIST (the_geom);

The query I am running is a point in polygon analysis based on data collected at historical time intervals - counting points in polygons when the timestamps on both tables match.  Points are locations of mobile phone connections (bar_history and bar) and polygons are buffers around certain areas (foo_history, foo)

It looks like this:

select s.id, s.place_id, s.time, count(l.location) as total
FROM foo_history as s LEFT JOIN foo as p ON s.place_id = p.id
LEFT JOIN bar_history l ON ST_Contains(ST_Buffer(ST_Transform(ST_SetSRID(ST_Centroid(p.polygon),
4326), 32615), 100), ST_Transform(l.location, 32615)) LEFT JOIN bar ON bar.phone_id = l.id
WHERE bar.network_id = 2
group by s.id LIMIT 5

This query returns results successfully in less than a few seconds.  However, when I add join by timestamps:

select s.id, s.place_id, s.time, count(l.location) as total
FROM foo_history as s LEFT JOIN foo as p ON s.place_id = p.id
LEFT JOIN bar_history l ON ST_Contains(ST_Buffer(ST_Transform(ST_SetSRID(ST_Centroid(p.polygon),
4326), 32615), 100), ST_Transform(l.location, 32615)) LEFT JOIN bar ON bar.phone_id = l.id
WHERE bar.network_id = 2
AND
to_timestamp(floor((extract('epoch' from l.time::timestamp) / 600 )) * 600) =
to_timestamp(floor((extract('epoch' from s.time::timestamp) / 600 )) * 600)
    group by s.id LIMIT 5

the query runs endlessly, even though nearly all of the timestamps will match each other from both tables.  I am not a database expert and would appreciate any suggestions on how to speed up this query or add some kind of index that might speed it up.


texts: what is the best query to find missing index in sql server 2008r2 and how to resolve missing index

texts: identify missing index and solve indexing problem


texts: PFS Corruption after restoring SQL Server 2012 database to 2016

texts:
We migrated few SQL Server 2012 databases over to SQL Server 2016 over the weekend.
Old Server Details
SQL Server 2012 SP4 11.0.7493.4
DB set to compatibility 110
TDE Enabled - Yes
Recovery Model is simple for this DB.
New Server Details:
SQL Server 2016 CU13
Out of 8 databases, 1 database ended up in PFS corruption.
Command: DBCC CHECKDB ([DBName]) WITH NO_INFOMSGS, ALL_ERRORMSGS
Msg 8948, Level 16, State 6, Line 1
Database error: Page (1:35241) is marked with the wrong type in PFS page (1:32352). PFS status 0x40 expected 0x60.
Msg 8948, Level 16, State 6, Line 1
Database error: Page (1:35242) is marked with the wrong type in PFS page (1:32352). PFS status 0x40 expected 0x60.
Msg 8948, Level 16, State 6, Line 1
Database error: Page (1:35243) is marked with the wrong type in PFS page (1:32352). PFS status 0x40 expected 0x60.
Msg 8948, Level 16, State 6, Line 1
Database error: Page (1:35244) is marked with the wrong type in PFS page (1:32352). PFS status 0x40 expected 0x60.
Msg 8948, Level 16, State 6, Line 1

Further
I restored the same backup to another 2012 server and PFS corruption was NOT detected - it was all good.
I restored the same backup to another 2016 server and PFS corruption was detected.
I restored a previous day backup to 2016 and PFS corruption was detected on that copy too.
I disabled encryption on 2016 and still PFS corruption was there.
Can I know what are my options here to restore the DB without PFS corruption on to 2016?
Any help would be much appreciated.
Note: We had done the same exercise on Development server and it was clean. We now have issue with Production Server.
Edit: Adding DBCC PAGE Info
PAGE: (1:35241)

BUFFER:

BUF @0x00000254BA85C240

bpage = 0x000002548E40A000          bhash = 0x0000000000000000          bpageno = (1:35241)
bdbid = 13                          breferences = 3                     bcputicks = 0
bsampleCount = 0                    bUse1 = 63417                       bstat = 0x9
blog = 0x2121215a                   bnext = 0x0000000000000000          bDirtyContext = 0x0000000000000000
bstat2 = 0x0

PAGE HEADER:

Page @0x000002548E40A000

m_pageId = (1:35241)                m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x8200
m_objId (AllocUnitId.idObj) = 125   m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594046119936                                Metadata: PartitionId = 0
Metadata: IndexId = -1              Metadata: ObjectId = 0              m_prevPage = (0:0)
m_nextPage = (0:0)                  pminlen = 184                       m_slotCnt = 15
m_freeCnt = 274                     m_freeData = 7888                   m_reservedCnt = 0
m_lsn = (72128:142272:446)          m_xactReserved = 0                  m_xdesId = (0:0)
m_ghostRecCnt = 0                   m_tornBits = 414910055              DB Frag ID = 1

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED
PFS (1:32352) = 0x40 ALLOCATED   0_PCT_FULL                              DIFF (1:6) = NOT CHANGED
ML (1:7) = NOT MIN_LOGGED

I see ObjectID = 0
As per this link from sqlskills,

The table that the page was part of has been deleted since the page
corruption was logged
The system catalogs are corrupt in some way
The page is corrupt and so incorrect values were used to look up the
metadata


texts: PostgreSQL on Amazon RDS: out of memory error on bytea field > 64MB

texts:
I am attempting to dump a modestly sized (less than 3GB) database from Postgres 9.5.4 running on Amazon RDS. The instance is a db.m4.xlarge, which has 4 virtual cores and 16GB RAM. It's using the default parameter group.

My dump fails like this:

$ pg_dump -h xxxxx.rds.amazonaws.com -U admin -d mydb -Fc -f dump-20170919.psql
Password:
pg_dump: Dumping the contents of table "plugindata" failed: PQgetCopyData() failed.
pg_dump: Error message from server: out of memory
pg_dump: The command was: COPY public.plugindata (plugindataid, pluginkey, filename, lastmoddate, data) TO stdout;

Because attempting to SELECT the problem field also causes an error, I was able to narrow it down to a specific field in one record. The data field (type bytea) in this record has a size of 66,459,495 bytes, which is an order of magnitude larger than any other record in the table.

I have tried increasing both work_mem (to 1GB) and maintenance_work_mem (to 3GB) to no avail. I'm not all that familiar with memory tuning for Postgres, so I assume there's something else I need to tweak?

EDIT: Server logs contain the following:

...:admin@mydb:[14270]:LOG: could not send data to client: Connection reset by peer
...:admin@mydb [14270]:ERROR: canceling statement due to user request
...:admin@mydb:[14270]:LOG: could not send data to client: Broken pipe
...:admin@mydb:[14270]:LOG: SSL error: bad write retry
...:admin@mydb:[14270]:FATAL: connection to client lost

I have verified that ssl_renegotiation_limit is set to 0.


texts:
I'm creating an ad banner tracker for the ad banners on my website by using PHP. The tracker would track unique impressions, total impressions, unique clicks and total clicks of each banner.

The method I would use for detecting unique impressions/clicks is using IP. I'm thinking that the following four tables would be needed:

Banner - For storing information of each banner.
Unique Impression Information - Mainly for storing IPs of visitors who view the banners.
Unique Click Information - For storing detailed information (IP, country, device, browser, operation system, etc) of unique visitors who clicked the banners.
Total Click Information - For storing detailed information (IP, country, device, browser, operation system, etc) of every visitors who clicked the banners.

For storing the counts of impressions and clicks, I would use these four columns of the Banner table: "Unique Impression", "Total Impression", "Unique Click" and "Total Click".

The logic of counting impressions (or clicks) would be like:

If a visitor's IP is not found in the database, the IP would be stored in the database and the visitor would increase the values of both the Unique and Total Impression (or Click) columns of the Banner table by 1.
If a visitor's IP is found in the database, the visitor would only increase the value of the Total Impression (or Click) column of the Banner table again by 1.

I for now have no desire to know detailed information of visitors who only view but don't click the banners. That's why the visitor information stored in the Unique Impression Informaion table is only IP.

Advice on the efficiency of such a database design would be much appreciated.

== Update ==

The CREATE TABLE statements would be:

CREATE TABLE `Banner`
(
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `Unique Impression` int(15) NOT NULL,
    `Total Impression` int(15) NOT NULL,
    `Unique Click` int(15) NOT NULL,
    `Total Click` int(15) NOT NULL,
    /* Columns like `Name`, `Url`, `Src`, etc are omitted at here */
    PRIMARY KEY (ID)
);

/*
CREATE TABLE `Unique Impression IP` */ -- before changing table name
CREATE TABLE `Unique Impression Information`
(
    `ID` int(15) NOT NULL AUTO_INCREMENT,
    `IP` VARCHAR(255) NOT NULL,
    `Banner ID` int(11) NOT NULL,
    PRIMARY KEY (ID)
);

CREATE TABLE `Unique Click Information`
(
    `ID` int(15) NOT NULL AUTO_INCREMENT,
    `IP` VARCHAR(255) NOT NULL,
    `Banner ID` int(11) NOT NULL,
    /* Columns like `Country`, `Device`, `Browser`, etc are omitted at here */
    PRIMARY KEY (ID)
);

CREATE TABLE `Total Click Information`
(
    `ID` int(15) NOT NULL AUTO_INCREMENT,
    `IP` VARCHAR(255) NOT NULL,
    `Banner ID` int(11) NOT NULL,
    /* Columns like `Country`, `Device`, `Browser`, etc are omitted at here */
    PRIMARY KEY (ID)
);

As an example, the MySQL quies that will be used for detecting unique impressions would be:

$check_ip = mysqli_query($conn, "SELECT `IP`, `Banner ID` from `Unique Impression Information` WHERE `IP` = '$visitor_ip' AND `Banner ID` = '$banner_id'");

if (mysqli_num_rows($check_ip) === 0)
{
    mysqli_query($conn, "INSERT INTO `Unique Impression IP` (`IP`, `Banner ID`) VALUES ('$visitor_ip', '$banner_id')");
    mysqli_query($conn, "UPDATE `Banner` SET `Unique Impression` = `Unique Impression` + 1, `Total Impression` = `Total Impression` + 1 WHERE `ID` = '$banner_id'");
}
else
{
    mysqli_query($conn, "UPDATE `Banner` SET `Total Impression` = `Total Impression` + 1 WHERE `ID` = '$banner_id'");
}


texts:
I have created a db, and my problem is that I can insert ANY value in FK.

Sorry for having the values in Spanish.

This is my Database:

CREATE TABLE "Articulo" (
`ID`    INTEGER PRIMARY KEY AUTOINCREMENT,
`nombre`    TEXT NOT NULL,
`precio`    REAL NOT NULL,
`cantidad`  INTEGER NOT NULL,
`nombre_usuario`    TEXT,
FOREIGN KEY(`nombre_usuario`) REFERENCES Usuario)

CREATE TABLE `Categoria` (
    `nombre`    TEXT,
    PRIMARY KEY(nombre)
)

CREATE TABLE "Categoria_Articulo" (
    `ID_articulo`   INTEGER,
    `nombre`    TEXT,
    PRIMARY KEY(ID_articulo,nombre),
    FOREIGN KEY(`ID_articulo`) REFERENCES Articulo,
    FOREIGN KEY(`nombre`) REFERENCES Categoria
)

CREATE TABLE "Pedido" (
        `ID`    INTEGER PRIMARY KEY AUTOINCREMENT,
        `precio_total`  REAL NOT NULL DEFAULT 0,
        `nombre_usuario`    INTEGER NOT NULL,
        `fecha` TEXT NOT NULL,
        FOREIGN KEY(`nombre_usuario`) REFERENCES Usuario
    )

CREATE TABLE "Linea_Pedido" (
    `ID`    INTEGER PRIMARY KEY AUTOINCREMENT,
    `precio_total`  REAL NOT NULL,
    `cantidad`  INTEGER NOT NULL,
    `ID_pedido` INTEGER NOT NULL,
    FOREIGN KEY(`ID_pedido`) REFERENCES Pedido
)

CREATE TABLE "Linea_Pedido_Articulo" (
    `ID_linea_pedido`   INTEGER,
    `ID_articulo`   INTEGER,
    PRIMARY KEY(ID_linea_pedido,ID_articulo),
    FOREIGN KEY(`ID_linea_pedido`) REFERENCES Linea_Pedido,
    FOREIGN KEY(`ID_articulo`) REFERENCES Articulo
)

CREATE TABLE "Linea_Pedido_Articulo" (
    `ID_linea_pedido`   INTEGER,
    `ID_articulo`   INTEGER,
    PRIMARY KEY(ID_linea_pedido,ID_articulo),
    FOREIGN KEY(`ID_linea_pedido`) REFERENCES Linea_Pedido,
    FOREIGN KEY(`ID_articulo`) REFERENCES Articulo
)

CREATE TABLE `Usuario` (
    `nombre_usuario`    TEXT,
    `nombre`    TEXT NOT NULL,
    `apellidos` TEXT NOT NULL,
    `contraseña`    TEXT NOT NULL,
    PRIMARY KEY(nombre_usuario)
)

CREATE TABLE `favorito` (
    `usuario_tiene_favorito`    TEXT,
    `usuario_es_favorito`   TEXT,
    PRIMARY KEY(usuario_tiene_favorito,usuario_es_favorito),
    FOREIGN KEY(`usuario_tiene_favorito`) REFERENCES Usuario,
    FOREIGN KEY(`usuario_es_favorito`) REFERENCES Usuario
)

For example, I am able to insert in Linea_Pedido_Articulo in my all empty database which is an N-N relationship, the values I wanted, when it should show an error.

I have found this similar question, but it is not well answered I think.

Thanks for helping.


texts: Get a parent and the children using the parent name

texts:
Is there a way to get a parent and its children using a single query?
CREATE TABLE `servers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `parent_id` int DEFAULT NULL,
  PRIMARY KEY (`id`)
)

INSERT INTO servers (name) VALUES ('kvm01'), ('kvm02')

INSERT INTO servers (name, parent_id) VALUES ('web01', 1), ('db01', 1), ('web02', 2), ('db02', 2)

Current output:
mysql [lab]> SELECT child.id FROM servers AS parent LEFT JOIN servers AS child ON (child.parent_id = parent.id) WHERE parent.name = 'kvm01';
+------+
| id   |
+------+
|    3 |
|    2 |
+------+
2 rows in set (0.00 sec)

Expected output:
+------+
| id   |
+------+
|    3 |
|    2 |
|    1 |
+------+


texts: Bulk insert top 1000 rows from a big flat file

texts: I have a massive flat file having hundreds of millions rows. I only want to insert the top 1000 of them. How can I do this?


texts: mysql.sock missing: php connection works but cant connect from terminal

texts:
When I try to run mysql from the command line I get the following:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

interestingly the php scripts that connect to mysql in /var/www appear to be connecting just fine and when I stop MySQL these scripts can no longer connect as expected. However when I start mysql I continue to get the missing .sock error from the command line while my php scripts once again are connected to the database they are supposed to.

I have tried restarting mysql in various ways (restarting, starting then stopping, sudo pkill mysql) and still no luck. The only sock file on my system is /run/mysqld/mysqld.sock; but, without being sure that this is the cause of the command line error I dont want to haphazardly make symbolic links in my /tmp directory.

How can I resolve the issue of connecting to mysql via the command line? some suggestions I have read recommend reinstalling MySQL which I don't want to do considering my php connection is working.


texts:
I've been learning how to normalise data into a normalisation table and I'm struggling to get my head around this example question.

I have this 'clinical record' card:

Would anyone able to explain to me as to how to convert this into UNF and then to 3NF?


texts: Does T-SQL MOVE actually move the database, or just copy?

texts:
I'm a bit confused over the T-SQL command MOVE. I have a statement saying:

MOVE
N'Prod_Database_Data'
TO
N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Test_Database.mdf'
GO

The same statement is used on the log: Prod_Database_Log. I think the statement just makes a copy of the database and not actually moves the original database. Am I right or have I missed something fundamentally crucial?


texts: Oracle Materialized View Refresh Group - Montly Interval

texts:
I have a few Materialized Views that I need to update weekly and monthly.

So for the weekly I'm using:

execute DBMS_REFRESH.MAKE(
name=>'LPMVRefresh_Weekly_1',
list=>'mySchema.MV1_mv,mySchema.MV2_mv',
next_date => TO_DATE('2013-09-08:00:30:00','YYYY-MM-DD:HH24:MI:SS'),
interval =>'sysdate+7');

How can I do so they refresh monthly, say every 1st of the month at 1 am? Is there something like Interval => sysdate+monthly?


texts: Questions about Free Space Map of PostgreSQL

texts:
Let's create a temporary table (I choose temporary table because autovacuum don't run for this kind of tables):
CREATE TEMP TABLE test (
  id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  value int
);
INSERT INTO test (value) SELECT 0 FROM generate_series(0, 250);
SELECT ctid, * FROM test;

We will see that the table consists of two pages:
(0,1)   1   0
(0,2)   2   0
...
(0,226) 226 0
(1,1)   227 0
...
(1,25)  251 0

Now if we update one row
UPDATE test SET value = -1 WHERE id = 1;
SELECT ctid, * FROM test WHERE value <> 0;

we will see that the new row version was inserted at the end of the table (in the second page, which has enough free space for this operation) and this is a standard PostgreSQL behavior (the old row verison from (0,1) was marked as dead)
(1,26)  1   -1

Let's check the free space of pages:
CREATE EXTENSION pg_freespacemap;
SELECT * FROM pg_freespace('test');

we get
0   0
1   0

i.e. no free space; documentations says that the Free Space Map (FSM) is updated after vacuum run.
Now if we update another row from the first page:
UPDATE test SET value = -1 WHERE id = 2;
SELECT ctid, * FROM test WHERE value <> 0;

we will see
(0,227) 2   -1
(1,26)  1   -1

The new row version was not added to the 2nd page but was added to the current page, because we made free space when we updated the record with id 1. But this approach does not correspond to my understanding of FSM. Here are my questions:

How PostgreSQL knew at the second UPDATE that there was free space in the first page if pg_freespace('test') returned zero? I thought that this function gives you the FSM which in turn is used in case of UPDATEs in order to decide which page has enough free space to store the new row version.
Generally I was thinking that even if first row was marked as dead it however occupies some space in the 1st page and this space will be freed only after vacuum. Thus I was expecting that the second UPDATE will also add the new row version to the 2nd page.


texts: My database dramatically increased in size. Why?

texts:
I found that my database, specifically the USERS tablespace, dramatically increased in size.

How can I find what DML operations are performing on the objects located in this tablespace?


texts: Consistency of data between two databases on different server

texts:
I have to guarantee consistency of data stored in two databases installed on different servers.
The first one is a MySql database and the second is a Sql Server database.
Data's properties are not the same. There's only one field that allows me to join them.
Basically, I get the object A in the SqlServer database and retrieve the object B in the MySql database using a where clause A.Id = B.A_Id.
Primary keys are auto generated and INT(11) type. The Sql Server database is not yet implemented.
The connection at the MySql database is managed by a WPF application and the connection at the Sql Server database is managed by a WebAPI. I'm using the ORM NHibernate to perform databases handling.
I have to ensure that no errors occurs during an insert.
I have to insert the object A in the Sql Server database to be able to set the A_Id property of my object B before inserting it.
If the insertion in the MySql database fails, I have to rollback the insertion in the Sql Server database.
I would like to find a solution to avoid an insert/delete in the Sql Server database.
What is the best approach to achieve this ?
I try to implement something like this but I'm facing an issue with the primary key of object A:
Connection open for SqlServer database
Begin transaction
Insert A  -> Id is not set : Is it possible to ask NHibernate to book an id here ???
Connection open for MySql database
Begin transaction
Insert B
Commit transaction for B
Commit transaction for A
Close transaction for B
Close transaction for A
Close connection for B
Close connection for A\
Thanks in advance.


texts: text column compares equal to where clause but does not select matching row

texts:
We are having trouble when querying a table in our production database. One text column will compare equal to a string we filter on in the where clause, but postgres will not select the row.
(We are on postgres 11.11)
Our table set up like this:
(PROD)=> \d names;
                           Table "public.names"
        Column        |            Type             | Collation | Nullable | Default
----------------------+-----------------------------+-----------+----------+---------
 name                 | text                        |           | not null |
 processed_name       | text                        |           | not null |
 name_index           | integer                     |           | not null |
 when_created         | timestamp without time zone |           | not null |
Indexes:
    "names_pkey" PRIMARY KEY, btree (name, processed_name)
    "names_name_index_key" UNIQUE CONSTRAINT, btree (name_index)
    "ix_names_name" btree (name)
    "ix_names_processed_name" btree (processed_name)

When we process a list of names we check if they are already in the table in order to prevent double adding and violating the primary key constraint.
However on one name, 'Сергей Иванович МЕНЯЙЛО', the query to see if the name is already present returns an empty set
I would expect to get back the row with the same name. However when we attempt to insert the row in the table we get a primary key conflict
Here are some queries which might explain the problem better
(PROD)=> SELECT name_index,
    name,
    name = 'Сергей Иванович МЕНЯЙЛО' names_compare_equal
FROM names where name_index = 75128;
      name_index      |          name           | names_compare_equal
----------------------+-------------------------+---------------------
                75128 | Сергей Иванович МЕНЯЙЛО | t
(1 row)

However filtering instead on the name column selects no rows.
2021-05-24 20:37:41 UTC
(PROD)=> SELECT name_index,
    name,
    name = 'Сергей Иванович МЕНЯЙЛО'
    names_compare_equal
FROM names
WHERE name = 'Сергей Иванович МЕНЯЙЛО';

       name_index     | name | names_compare_equal
----------------------+------+---------------------
(0 rows)

So then if we try and insert the row we get a primary key conflict:
(PROD)>=> INSERT INTO names (name_index, name, processed_name, when_created)
  VALUES (89266, 'Сергей Иванович МЕНЯЙЛО', lower('Сергей Иванович МЕНЯЙЛО'), now());

ERROR:  duplicate key value violates unique constraint "names_pkey"
DETAIL:  Key (name, processed_name)=(Сергей Иванович МЕНЯЙЛО, сергей иванович меняйло) already exists.

What's more, if I query based on the hash of the rows I get the correct result:
(PROD)=> SELECT name_index,
    name,
    name = 'Сергей Иванович МЕНЯЙЛО' names_compare_equal
FROM names
WHERE md5(name) = md5('Сергей Иванович МЕНЯЙЛО');

      name_index      |          name           | names_compare_equal
----------------------+-------------------------+---------------------
                75128 | Сергей Иванович МЕНЯЙЛО | t
(1 row)

This only happens on our production database - which has the following encoding settings
      Name      |     Owner      | Encoding |   Collate   |    Ctype    |
----------------+----------------+----------+-------------+-------------+
 PROD DB        | PROD DB OWNER  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

This is pretty baffling to me so an ideas about what to check next would be helpful


texts:
I have the following tables in MySQL

`orders` (
  `orderID` int(10) unsigned NOT NULL auto_increment,
  `agentID` int(10) unsigned NOT NULL,
  `order_date` datetime NOT NULL,
  `senderID` int(10) unsigned NOT NULL,
  `receiverID` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`orderID`)
)

 `order_details` (
  `order_details_id` int(10) unsigned NOT NULL auto_increment,
  `orderID` int(10) unsigned NOT NULL,
  `packageID` int(10) unsigned NOT NULL,
   PRIMARY KEY  (`order_details_id`),
   KEY `fk_orderID` (`orderID`)
)

`order_status` (
  `status_id` int(10) unsigned NOT NULL auto_increment,
  `order_details_id` int(10) unsigned NOT NULL,
  `descrID` int(10) unsigned NOT NULL,
  `date_time` datetime NOT NULL,
  PRIMARY KEY  (`status_id`),
  KEY `theDate` (`date_time`),
  KEY `stat` (`descrID`,`order_details_id`,`date_time`)
)

 `order_statuses` (
  `descrID` int(10) unsigned NOT NULL auto_increment,
  `descr` varchar(255) collate utf8_bin NOT NULL,
  `public` smallint(6) NOT NULL COMMENT '1 or 0',
  `leg` tinyint(4) NOT NULL COMMENT 'carrier code',
  PRIMARY KEY  (`statusID`)
)

The third and fourth tables are used to keep track of all the stages the orders go through. The flow is as follows: an order is placed at an agent of the company, the agent gives the order to a carrier, the carrier transfers the order to a hub owned by the company, another carrier picks it up and then hopefully delivers it.
2 questions;

First, any comments on the design?

Second, I need to run a query that will give me the all the orders based on the specific LATEST status per carrier. Right now the query that I am using is taking about 1.5 seconds to complete and I only have one row in the tables.

select odd.order_details_id
from order_details odd, orders od
where odd.orderID = od.orderID and (
    select oas.statusID
    from order_status oas, order_statuses oss
    where oas.descrID = oss.descrID and oss.leg=1 and odd.order_details_id =oas.order_details_id
    order by oas.date_time desc limit 1)=2

Is there a way to optimize this?
Thanks


texts: Restoring with replace going incredibly quickly

texts:
So I have a database (on a SQL Server 2008 R2 SP2 instance) that is 2TB uncompressed, with a .bak file that is 500GB compressed. As I have been toying with the fastest way to restore this database after a series of tests, I've tried a couple of things:

Restoring after dropping the database
Restoring with Replace

When I restore after dropping the database, the fastest I can get a restore going is 4 hours. This is when I put the .bak on a RAID 10 logical drive, with the data files being written to a separate RAID 10 logical drive on the same server. The log files go to yet another RAID 0 on the same server.

However, if I restore with Replace, the restore process only takes 56 minutes (similar setup). Did I find some sort of turbo button? This is so fast that I am worried.


texts:
Which of these is true:

If a table is in 2nd normal-form then it must be in 3rd normal-form.
If a table is in 3rd normal-form then it must be in 2nd normal-form.

I know only one of them is correct, if it's the first it makes sense.  If it's the second -doesn't make any sense to me. Maybe an example?


texts: Why full-text-search returns less rows than LIKE

texts:
I don't get full-text-search working as I want it to, and I don't understand the differences in the resultlists.
Example statements:

SELECT `meldungstext`
FROM `artikel`
WHERE `meldungstext` LIKE '%punkt%'

returns 92 rows. I receive rows which have matches, for example, like "Punkten", "Zwei-Punkte-Vorsprung" and "Treffpunkt" in column meldungstext.

I set a fulltext-index on the column "meldungstext" and tried this:

SELECT `meldungstext`
FROM `artikel`
WHERE MATCH (`meldungstext`)
AGAINST ('*punkt*')

this returns only 8 rows. I receive only rows which have matches to "Punkt" itself or words which I think are taken as "Punkt" as in "i-Punkt".

I then tried boolean mode:

SELECT `meldungstext`
FROM `artikel`
WHERE MATCH (`meldungstext`)
AGAINST ('*punkt*' IN BOOLEAN MODE)

returns 44 rows. I receive rows which have "Zwei-Punkte-Vorsprung" or "Treffpunkt" in column meldungstext, but not those with "Punkten".

Why does this happen and how can I set a "fully" working full-text-search to prevent using LIKE '%%' in the where-clause?


texts:
I am trying to migrate my unit tests from H2 to Postgresql.

Currently, H2 gives me an in-memory schema such that each connection maps to a unique schema, creates the tables, runs the test, and drops the schema. The schema creation and destruction is handled automatically by H2.

The unit tests run concurrently.

What is the best way to do this in Postgresql? Specifically,

How do I get a unique schema per connection?

Should the test framework generate unique names or is there a built-in mechanism for doing this?

How do I ensure that the schema is dropped when the connection is dropped?

I don't want to end up with dangling schemas when unit tests get killed.

What approach will yield the highest performance?

I need to create/drop tens of schemas per second.

UPDATE: I found a related answer here but it fails to drop schemas in case the process running the unit tests gets killed.


texts: Best Practice in storage allocation for compressed and partitioned tables

texts:
I have a fairly large database that basically consists of one table, this table is paritioned by month and all partitions are compressed with page compression. After the initial load of the data there have been update operations (basically one column was set from NULL to an tinyint value). After that I noticed that in the disk usage report showed on the files that the space reserved did grow very large compared to the space used
(81 GB vs. 24 GB). This obviously results in a huge waist of disk space. Compressing the partition and shrinking the file results in an awfully fragmented clustered index for that partition.

What would be best practice to reduce the disk space usage to the absolute minimum ?


texts: SQL Server 2012 Page Life Expectancy resets to 0 after about 50 days

texts:
I've noticed an odd behavior on a 2-server HA cluster and I was hoping someone could confirm my suspicion, or maybe offer some other explanation... Here is my setup:

A 2-server SQL 2012 SP1 installation
SQL AlwaysOn HA has been enabled for a few databases
CPUs are 2.4GHz, 4 cores
RAM is 34 GB (it is an AWS instance, hence the odd number)
Resource utilization is relatively low - each server has 14+ GB memory free, and SQL is not capped on how much memory to use
Disk access time is fine - rarely going over 15ms/Read or Write
Databases are not big - 1 GB, 1.5 GB, 7.5 GB
SQL server process is using 16 GB Private Bytes, 15 GB Working Set

Overall, no resource issues are noted. Now for the odd part. SQL is not restarted (process has been running for almost 6 months) but it seems that every ~50 days, the Page Life Expectancy counter drops to (almost) 0. Up until that point it climbs steadily, no drops. Here is a perf graph:

When I look at the counter data (I don't have the exact number, just an hourly aggregation) it seems the PLE counter value reached about 4,295,000 sec (roughly 50 days) every time (at least every time I have data for).

My crazy theory is that the PLE number is held as milliseconds as an unsigned long int (which has a limit of 4,294,967,295) and at 49.71 days it resets, either by design, or because of a bug. This would explain the behavior of the two servers and the identical pattern they have. Or it could be something totally different and I am just not making any sense. :)

Has anyone seen anything like that, or can explain this behavior?


texts: SQL server taking too much time in opening properties in SSMS

texts: I am using sql server express 2008 r2 SP2 from about 2 years. Now a days when i try to check properties of database using sql server management studio it takes too much time(25 to 30 seconds) to open properties window for any database. But other working such as connection to the server, backup of database are almost same as before.I have not changed any settings or added new large databases recently. So what may be the reason behind this slow behavior? If anyone wants extra information on this problem please let me know.


texts:
I use pgadmin 4 and PostgreSQL

I try to restore a database from a save.

I have put the directory of my file save C:\Users\H2O\Desktop\temp_DB\ as a binary path in file preferences.

When I try to restore my db from my tar file (database -> restore) I have got this error.

  'C:\Users\H2O\Desktop\temp_DB\pg_restore.exe' n'est pas reconnu en tant que commande interne (pg_restore is not recognize as an internal command)

Don't know where it comes from.

I use pg admin 4 and Postgresql

'"C:/Users/Public/Downloads/tempDB/ \pg_restore.exe"' n'est pas reconnu en tant que commande interne


texts: mongo2.6: db.serverStatus().connections nothing happened?

texts:
I wanted to create a user,and got authorize error.
So I modify the /etc/mongod.conf :

#auth=True

And then restart the mongo,and create the user on terminal :

use admin
db.createUser(
  {
    user: "superAdmin",
    pwd: "    ",
    roles: [ { role: "userAdminAnyDatabase", db: "admin" } ]
  }
)

It was success,then I modify /etc/mongod.conf  back to  :

auth=True

And I use remote login with robomongo
And I want to check db.serverStatus().connections
But nothing happened?
How can I fix this?


texts: How to send mail from a restricted SQL Server

texts:
I have created an DB refresh job on a pre-prod SQL server, with latest successful Prod backup to refresh pre-prod DB every monday morning. However, i want to send a status mail post refresh. Due to compliance and security risks in our financial company, we can not send mails from pre-prod server. I was able to send mails successfully from Prod.

Is there any method to send mail with DB refresh status. My thoughts are
1. Refresh script writes the log to a folder in Dev server - should i write a windows batch/powershell script in Prod server to access the folder in dev and read the file to send a mail post refresh with the status?
2. I can not use any linked server from Pre-prod to Prod.

Any other thoughts?


texts: How to get pg_locks related to a specific database in Postgres?

texts:
This question is related to my previous one here
In SQL Server, I could get the lock type from a specific database using the code below:
SELECT request_mode FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID() AND resource_associated_entity_id = OBJECT_ID(N'dbo.cars');

In Postgres 11, how to filter the pg_locks result to a specific database like how SQL Server's resource_database_id = DB_ID() AND resource_associated_entity_id = OBJECT_ID(N'dbo.cars') works?
Thanks


texts: Pg_restore getting stuck for a particular server

texts:
We have 3 postgres VMs hosted on AWS. I have taken a dump of a database from a server. I am able to restore this dump on 2nd server. But whenever I run pg_restore for the 3rd server, it got stuck after some time and I got connection timeout error.backup size is 9 MB.

below is the command I used:

pg_restore -c -h server -U postgres -d dbname "E:\backfile.backup"

I checked using pg_stat_activity, Nobody is using this database except pg_restore.Sometimes it got stuck, sometimes it throws random errors like below:

  pg_restore: [archiver (db)] error returned by PQputCopyData: server closed
      the connection unexpectedly
     This probably means the server terminated abnormally
      before or while processing the request.

How can I debug this issue?


texts: SQL Server Docker image on Mac: Storing a Database

texts:
I am having reasonable success running SQL Server for Linux on Docker for Mac, as per the instructions on https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-docker. The article also notes that:

  Volume mapping for Docker-machine on Mac with the SQL Server on Linux image is not supported at this time.

This, of course, makes it difficult to do any more than really simple testing on the Mac if you have to re-install the database every time you start up Docker.

Has anybody a suggested strategy for maintaining a database beyond the current session?


texts:
I am looking at replication of the master database (PostgreSQL) and this is the configuration I have to setup

master -- hot-standby (hs)
|
|
 --- read only slave (r1)
|
|
 --- read only slave (r2)

My requirement is that the either of master or standby should be available all the time and r1-r2 should be available most of the time.

I have setup streaming replication with recovery on hs and without recovery on r1,r2. All these 3 get the WAL logs from master.

My question is, when the master goes down and we trigger the failover on standby, what should I do to r1 and r2 ? re-parenting them to standby would mean a full base-backup and when eventually master comes up, i may need to re-parent again.

This is my first attempt at replication-setup. So if there is anything obvious I am missing or doing wrong, please let me know. If there are tools/solutions which cater to this sort of a thing, I would be very glad to know and learn.


texts:
Whenever in our system there is a block (everything stopped). I see the below query is the culprit,
  (@P1 numeric(10))SELECT "Col1128","Tbl1029"."A_NAME" "Col1124" FROM "DbName"."Schema"."MyTable1"
"Tbl1029" RIGHT OUTER JOIN (SELECT "Tbl1027"."MyColumn1" "Col1126","Tbl1027"."MyColumn2"
"Col1127","Tbl1027"."MyColumn4" "Col1128","Tbl1027"."MyColumn5" "Col1130","Tbl1027"."MyColumn6"
"Col1129","Tbl1027"."MyColumn5" "Col1131" FROM "DbName"."Schame"."MyTable2" "Tbl1027" WHERE
"Tbl1027"."MyColumn1"=@P1) Qry1132 ON CONVERT(numeric(2,0),"Tbl1029"."MyColumn3",0)="Col1126"

We have 2 separate DB servers with completely different databases. The above is running in DB server 2 but request is coming DB server 1 (client host name). We have 100 of applications connecting to DB servers. Also, we have link server among DB servers.
Now, I know that the query came form DB server 1 with ProgramName Microsoft SQL Server. Now I am confused whether the program Microsoft SQL Server is indicating the link server query or application query using ORM.
I am searching in all applications but not finding anything yet. Form the DBA perspective what else I can do to get the real issue


texts: Dangers in dropping all tables in a schema with CASCADE

texts:
The answer to this question suggests I can use the CASCADE option of DROP SCHEMA to remove all a schema and all its tables, but warns:

  CASCADE - Automatically drop objects (tables, functions, etc.) that are contained in the schema, and in turn all objects that depend on those objects

Being a novice to databases, I'm unsure about what objects may depend on objects within a schema. Is it possible that a table or some other object outside the schema that is dependent on an object inside the schema will be dropped as well? If I just want to eliminate a schema and all its contents without changing anything else, is using CASCADE a good idea?


texts: Is it possible to raiserror within a For Delete Trigger that rolls back the deletes

texts:
There is a claim at the end of this accepted answer that if an error occurs within a "For Delete" trigger, the delete action would be rolled back because it is part of an implicit transaction.

However here is an example showing that the deleted rows remain deleted even though an error is raised:

create table T1 ( i1 int  );
go

create trigger T1_ForDelete on T1
for delete
as
raiserror('Raised 16', 16, 1);
raiserror('Raised 18', 18, 1);
raiserror('Raised #2 16', 16, 255);
raiserror('Raised #2 18', 18, 255);
go

insert into T1 (i1) values (1);

set xact_abort on; -- makes no difference

delete from T1;

Output (order changes):

Msg 50000, Level 16, State 1, Procedure T1_ForDelete, Line 4
Raised 16
Msg 50000, Level 16, State 255, Procedure T1_ForDelete, Line 6
Raised #2 16

(1 row(s) affected)
Msg 50000, Level 18, State 1, Procedure T1_ForDelete, Line 5
Raised 18
Msg 50000, Level 18, State 255, Procedure T1_ForDelete, Line 7
Raised #2 18

Then

select * from T1; -- Returns no records

Is this the expected behavior or is there a way to prevent deletion on an error (e.g. different severity/state)?

I cannot use an "Instead of Delete" because "on delete cascade" is employed.  Create Trigger (MSDN), search "For INSTEAD OF"


texts: What do columns with a "dot" mean when listing Sql Server user permissions using sp_helprotect?

texts:
I have a DB that I did not setup and I used sp_helprotect to list the permissions, the output is as in the image below.

I don't understand the dot's in the Owner, Object and Column columns.  And I can't find a similar example in the few db's I have.

What to they related to or what do they mean?  I assume not applicable or the like, is that correct?

And how can I set permission like this on another DB ... I'm trying to setup another db with a user that has the same permissions.

.


texts: How to take a database dump for selective set of values in few tables?

texts:
create table foo (id, val1, user_id,...)
create table bar (id, foo_id, val2, ...)
create table baz (id, bar_id, val3, ...)

select * from foo where user_id = 1;
select * from bar where id in (select id from foo where user_id = 1)
select * from baz where id in (for all the above foos)

How do I write a dump command which does the above?


texts: Ran "Grant all privileges" but still getting "permission denied for relation"

texts:
I'm using PostGres 9.5.  I'm confused about how to grant all privileges to a user for a particular database.  I thought this would do the trick

postgres=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO cindex;
GRANT
postgres=# \q
localhost:cindex davea$ cindex
psql (9.5.0, server 9.5.1)
Type "help" for help.

cindex=> select * FROM crypto_currencies;
ERROR:  permission denied for relation crypto_currencies
cindex=>

but as you can see, I'm still getting "permission denied for relation" when I try and SELECT something.  What else do I need to do to allow the user to select properly from all the tables in the database?


texts:
I'm trying to create a categorical column, with 3 states:
subA, subB or Neither.

I'm working with SqliteStudio 3.1.0, RHEL7.0.

I loaded the my csv into SQLITE via Python/Pandas. Everything works as expected.

I added constraints in the GUI:

"fullpath" TEXT Unique NOT NULL.

I added the Sub columns in SqiteStudio and double checked in Data table.

alter table data add column Sub text(100)

That works, no errors given. Then I try:

insert into  data(Sub)
select  fullpath, case
        when fullpath like "%subA%" then "subA"
        when fullpath like "%subB%" then "subB"
        else "Neither" end
from data

I get this error which I don't understand.

[18:35:45] Error while executing SQL query on database 'test': 2 values for 1 columns


texts: How can I maintain old information after an update?


texts:
I have a table that stores notes

create tblNote(
  Id int identity(1,1),
  ParentId  int ,
  ParentType varchar(32),
  NoteType varchar(32),
  Note varchar(max),
  CreatedBy varchar(25),
  CreatedDate  datetime,
  .
  .
  .
  <other metadata about the note>
)

I have done a lot of reading recently about how MSSS handles indexes (2005 and forward).

I have a clustered index on ID

[ I have considered changing the clustered index to parentId, parentType since that is reasonably narrow and it is static. ]

The overwhelming percentage of queries against this table are going to be along the lines of

select NOTE, createdDate, createdBy
from tblNote
where parentId = 12 and parentType = 'RFQ'

The question I want to ask today (though any feedback is welcome) is this:

The NC index I could add is:

create index  idx_nc_note_parent(
        parentId ,
        parenttype
    )
    include (createdby, createdDate)

This would be useful in creating little lists of the notes where we might include who and when type info.

I am hesitant to include a varchar(max) field.  It seems like it would really hurt the amount of the index that would be cached  (Is this reasonable or unreasonable)

Assuming I dont include the NOTE field, a RID Lookup will be necessary to actually fetch the note content if it is requested.

While I have read quite a bit about how expensive RID lookups are, it still has to be better to have this index as opposed to doing a table scan, RIGHT?

[apologies for the code block, i have added the 4 spaces, but maybe i did it wrong? ]


texts: UTL_MATCH.JARO_WINKLER_SIMILARITY - how can I set the prefix scale?

texts: I'm using UTL_MATCH's Jaro Winkler similarity function and it seems to be performing well. However, I would like to adjust the prefix scale according to the situation. Is this possible? Is it possible to see what the default prefix scale is? I could not find any documentation on this, but it seems that in order to be a J-W distance, it must use a prefix scale.


texts: PostgreSQL service start failed on CentOS 7

texts:
Installed PostgreSQL using:

sudo yum install postgresql-server postgresql-contrib

PostgreSQL version: 9.2.18

When start service:

sudo service postgresql start

Got error:

Redirecting to /bin/systemctl start  postgresql.service
Job for postgresql.service failed because the control process exited with error code. See "systemctl status postgresql.service" and "journalctl -xe" for details.

The journalctl -xe detail:

-- Unit postgresql.service has failed.
--
-- The result is failed.
 7月 19 04:17:04 node2 systemd[1]: Unit postgresql.service entered failed state.
 7月 19 04:17:04 node2 systemd[1]: postgresql.service failed.
 7月 19 04:17:04 node2 polkitd[19772]: Unregistered Authentication Agent for unix-process:27133:53830492 (system bus name :1.405, object path /org/freedesktop/PolicyKit1/AuthenticationA
 7月 19 04:19:33 node2 polkitd[19772]: Registered Authentication Agent for unix-process:27165:53845472 (system bus name :1.406 [/usr/bin/pkttyagent --notify-fd 5 --fallback], object pat
 7月 19 04:19:33 node2 systemd[1]: Starting PostgreSQL database server...
-- Subject: Unit postgresql.service has begun start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit postgresql.service has begun starting up.
 7月 19 04:19:34 node2 pg_ctl[27187]: pg_ctl: could not start server
 7月 19 04:19:34 node2 pg_ctl[27187]: Examine the log output.
 7月 19 04:19:34 node2 systemd[1]: postgresql.service: control process exited, code=exited status=1
 7月 19 04:19:34 node2 systemd[1]: Failed to start PostgreSQL database server.
-- Subject: Unit postgresql.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit postgresql.service has failed.
--
-- The result is failed.
 7月 19 04:19:34 node2 systemd[1]: Unit postgresql.service entered failed state.
 7月 19 04:19:34 node2 systemd[1]: postgresql.service failed.
 7月 19 04:19:34 node2 polkitd[19772]: Unregistered Authentication Agent for unix-process:27165:53845472 (system bus name :1.406, object path /org/freedesktop/PolicyKit1/AuthenticationA
lines 2850-2910/2910 (END)

Run systemctl status PostgreSQL.service, got this information:

● postgresql.service - PostgreSQL database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Wed 2017-07-19 04:30:26 EDT; 42min ago
  Process: 27285 ExecStart=/usr/bin/pg_ctl start -D ${PGDATA} -s -o -p ${PGPORT} -w -t 300 (code=exited, status=1/FAILURE)
  Process: 27280 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 26640 (code=exited, status=0/SUCCESS)


texts: Is it possible to store Always Encrypted master key an on-premises key vault?

texts:
I want to store the master key in a centralize key vault and have IIS access the key from there. I know I can use Azure Key Vault or local Windows key store, but I was looking for another option to have it outside the server.

Are there any on-premises centralized key vault solutions that can store Always Encrypted master key?


texts: How can I resolve the error "Could not load file or assembly 'Microsoft.DataWarehouse.AS'" in SSIS?

texts:
I launched good old Visual Studio 2017 today to create a new Integration Services project/package. When attempting to add a new target destination (OLE DB connection), upon selecting the drop down menu for the Table/View, I was greeted with an exciting message:

Could not retrieve the table information for the connection manager
'MyFancyConnection.Server'.
Could not load file or assembly
'Microsoft.DataWarehouse.AS' or one of its dependencies. The system
cannot find the file specified.

I tried upgrading my local SQL Server 2017 to the latest CU16, this did not resolve the issue.
How can I resuscitate my Visual Studio installation?


texts: Compare two columns in two databases to insert the content of a third column into a forth one

texts:
I have two tables in  different databases:
b.dbo.table1
ID
Code

w.dbo.table2:
ID
Code2
ID2

I am in need of code that helps me to compare (Code) in table1 with (Code2) in table2. If they are the same then insert (ID) from table1 into (ID2) in table2.


texts: how can an additional natural join with the same table twice *reduce* the row count

texts:
This is real results from my database. I spend some time trying to reproduce this behavior with an SSCCE but failed. How is the last of the below results possible?

SELECT COUNT(*) FROM rr.resource    a
--- 15771

SELECT COUNT(*) FROM rr.resource    a
NATURAL JOIN rr.interface   b
--- 41419

SELECT COUNT(*)
FROM rr.resource    a
NATURAL JOIN rr.interface   b
NATURAL JOIN rr.interface   c
--- 0

The zero (0) row count in the last query is consistent and repeatable every time.
In some examples I created, adding a second natural join with the same table twice yields the same number of rows (as I was expecting). How is then the above result possible?


texts: Implementing HA for SQL Server for a .NET Application that uses MS DTC

texts:
My production environment uses MS DTC and SQL Server 2014. I must have an HA solution such as Mirroring or AG in place. (Not FCI).

I am trying to explain to my team that MS DTC is not supported by SQL Server with Mirroring / AG and what could go wrong. But all the examples I am seeing online are for cross database scenarios, which doesn't apply to us.

I am looking for a worst case scenario & likelihood of its occurrence. Ideally something that I could actually demonstrate to them. ie: creating a transaction that uses MS DTC, triggering a failover, showing what happens.

As a DBA, I would like to be able to explain to the business what are the real world implications of this lack of support for MS DTC from Microsoft, so that they can decide if the risk is worth the cost of:

Upgrading SQL Server to 2016 & AGs and/or
Changing how their application relies on MS DTC (I am told this is impossible).

I don't want to just parrot "Not supported" to them because that's unacceptable.

Any help appreciated.


texts: Mysql: extremely weird index behaviour on "SELECT WHERE IN"?

texts:
I've just been stuck for a bit on a couple of very weird index choices from mysql.
I have a table with half a billion entries and primary key is an auto inc ID

Original query: "select id from table WHERE id in (x,x,x,x,x,x)"

When I use a list of 2000 ids it works perfectly fine:

    EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "902.03"
    },
    "table": {
      "table_name": "table",
      "access_type": "range",
      "possible_keys": [
        "PRIMARY"
      ],
      "key": "PRIMARY",
      "used_key_parts": [
        "id"
      ],
      "key_length": "4",
      "rows_examined_per_scan": 2001,
      "rows_produced_per_join": 2001,
      "filtered": "100.00",
      "using_index": true,
      "cost_info": {
        "read_cost": "701.93",
        "eval_cost": "200.10",
        "prefix_cost": "902.03",
        "data_read_per_join": "31M"
      },
      "used_columns": [
        "id"
      ],

Now the only thing I did was adding another 1000 ids to the SAME query:

EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "48429076.70"
    },
    "table": {
      "table_name": "table",
      "access_type": "index",
      "possible_keys": [
        "PRIMARY"
      ],
      "key": "intern_id_hash",
      "used_key_parts": [
        "intern_id_hash"
      ],
      "key_length": "5",
      "rows_examined_per_scan": 452740737,
      "rows_produced_per_join": 226370368,
      "filtered": "50.00",
      "using_index": true,
      "cost_info": {
        "read_cost": "25792039.85",
        "eval_cost": "22637036.85",
        "prefix_cost": "48429076.70",
        "data_read_per_join": "3T"
      },
      "used_columns": [
        "id"
      ],

When I add "FORCE INDEX (PRIMARY)" it shows query cost of 1300 and works fine.
'intern_id_hash' is an index on an unused column (for historic reasons still present)

Question:
What the heck is happening with the query optimizer that two identical primary id queries with a different number of integers in the IN () clause suddenly malfunctions ?