texts: Join Tables of Varying Criteria in Order of Priority

texts:
I am trying to join two tables based on varying criteria in order of priority.

The situation

I have inherited a clinical database which has inherently bad data quality from years of poor input and shockingly bad application build etc... all out of my control but I am tasked with creating a data warehouse from this system.

I have a table which has diary appointments in them

The structure is

(Appointments)

Appointment_ID (PK),
Appointment_Date,
Appointment_User (FK),
Appointment_Patient_ID (FK),
Appointment_Outcome

Events

When a patient is seen in an appointment there are events that occur..

The events table contains the following information

Event_ID (PK),
Event_Date,
Appointment_ID (FK),
Patient_ID (FK),
User_ID (FK),
Event_Type_ID (FK),
Event_Outcome

The poor design of the application allows:

1) Users create an event without linking it to an appointment.

2) It also allows someone to complete an event on behalf of a clinician (medical secretary) whilst impersonating them - however in the database it records the medical secretary's ID instead of the clinicians ID, but keeps the clinicians ID in the appointment table.

The poor data quality exists where:

1) The user creates the event on the wrong date (so no appointment_ID and date is wrong).

So whilst the event should contain the appointment_ID, user_ID and be on the same date as the appointment it isn't always.

also

A patient can have more than one appointment on a day with and with more than one clinician (as defined by the user_ID).

The ideal would be to get the data quality fixed but the organisation can't do this in time scale (there are people going in and fixing all the errors but this won't be finished for another couple of months) The organisation has agreed to best match on 5 sets of criteria in a certain order of priority and accept the risk that there will be some inaccuracies.

The required outcome

I need to create a table which shows the following information

All data from Appointments (a) on the left
All rows from all columns (where there is a match) on the right from Events (e)

The join should occur in the order of priority:

1) Join e on a.appointment_ID = e.appointment_ID

if there is no match then

2) Join e on a.patient_ID = e.Patient_ID AND a.date = e.date AND a.user_ID = e.user_ID AND e.event ID not found in the results from join 1

if there is no match then

3) Join e on a.patient_ID = e.Patient_ID AND a.date = e.date AND e.event ID not found in the results from join 1 or join 2

if there is no match then

4) Join e on a.patient_ID = e.Patient_ID AND a.date - e.date +/- 5 days AND a.user_ID = e.user_ID AND e.event ID not found in the results from join 1, 2 or 3

5) Join e on a.patient_ID = e.Patient_ID AND a.date - e.date +/- 5 days AND e.event ID not found in the results from join 1, 2, 3 or 4

I tried doing nested not IN statements on the join but as the data is in the GB and records are in the millions of rows per table it's taking half an hour to run the query and this needs to be updated on demand as people are checking the impact of the data quality amendments as they go.

Is there a resource and query run time efficient way of running the join on these 5 sets of criteria in order of priority?


texts:
I have MySQL Percona version 5.7, with 3 undo logs, but i don't know why truncating logs is not working.
I enabled innodb_undo_log_truncate, decreased innodb_purge_rseg_truncate_frequency to 32
show global variables like '%_undo_%'

innodb_max_undo_log_size    1073741824
innodb_undo_directory   /data/mysql/
innodb_undo_log_truncate    ON
innodb_undo_logs    128
innodb_undo_tablespaces 3

show global variables like '%truncate%'

innodb_purge_rseg_truncate_frequency    32
innodb_undo_log_truncate    ON

Executing show engine innodb status doesn't show any long running rollback transaction.
But my undo logs keep growing.
root@server:/data/mysql# du -h -c undo00*
598G    undo001
747G    undo002
346G    undo003

Any suggestions are appreciated.


texts: History of the provisioned write capacity in DynamoDB beyond the last two weeks

texts: Is there any way to see the history of the provisioned write capacity for a DynamoDB table beyond the last two weeks? When I go to the Amazon AWS console, select a DynamoDB table and look at the monitoring tab, the time range does not allow me to go beyond two weeks. Is there any other place to look at that could give me a longer history?


texts:
I have following code, for this, I have two tables, one table contains questions and another table contain answers.
answers table something like following : (ans_question_1 to ans_question_95)

| answersId |ans_question_1| ans_question_2| student_id
|-----------|--------------|---------------|-----------
| 1         | Male         | 23            |11
| 2         | Female       | 21            |12

Questions table (questions 1 to 95)

| question_id | Question|
|-------------|---------|
| 1           | Gender
| 2           | Area code

Want table something like this (Do not want manual code like 'left join')

| student_id  | Question  | Answer
|-------------|-----------|-------
| 11          | Gender    | Male
| 11          | Area code | 23
| 12          | Gender    | Female
| 12          | Area code | 21

i tried following code:

SELECT DISTINCT q.`question_id`,
CONCAT("ans_question_", q.question_id),
    (
        SELECT (CONCAT("ans_question_", q.question_id))
        FROM `answers` a
        WHERE REPLACE(CONCAT("ans_question_", q.question_id), 'ans_question_', '')=q.`question_id`
    ) AS answer
FROM questions q, options_type o
WHERE o.`options_type_id`=q.`questions_type_id`
AND q.questions_status=1

SELECT (CONCAT("ans_question_", q.question_id)) i tried to get column value of the answer table, but its printing column name, not value, if somehow I can extract value by this code then my problem could solve if anyone can help.
Thanks

Answer something Like :

| student_id| Gender| Area code| Other question
|-----------|-------|----------|---------------
| 11        | Male  | 23       |Other answers
| 12        | Female| 21       |Other answers


texts: I can't seem to find any information of which Java version that will be used in the soon to be released DB2 V11.1. Does anyone else know?


texts: MySQL GIS functions strange results from ST_Distance_Sphere


texts:
Given I have a system of Oracle Forms 6i (legacy software), how could I make it work on Windows 7. We have serious problems with apps like PLUS80.exe crashing ("Oracle SQL*Plus has stopped working").

Is it possible at all? We also consider downgrading to XP if problems persist..


texts:
I am getting below errors in sql log of Native Postgres database :
psql.bin:G_co-ac_G_grant_ivspc.sql:1: ERROR:  42601: syntax error at or near "&"
      2 LINE 1: grant select on URM_AC_ACCESS_&25 to finfgen, finfutl;
                                              ^
LOCATION:  scanner_yyerror, scan.l:1128
     17 psql.bin:G_co-ac_G_grant_ivspc.sql:7: ERROR:  42601: syntax error at or near ":"
     18 LINE 1: grant select on URM_AUTHCODE_:sp125 to finfgen, finfutl;
                                             ^

What is the cause of this and how it can be solved ?


texts: What does repair and optimize tables in PHP MyAdmin actually do?

texts:
Sometimes I repair or optimize (normally both) my tables in PHP MyAdmin  Often it fixes a problem I'm having.  My question is what am I actually doing when I undertake these actions?

Is there any danger?  Is any data changed or moved?


texts: SQL Server 2012 Very Slow to Consume Memory

texts:
I have a bit of a conundrum.  I recently upgraded a ~20gb database from Window Server 2008 R2 || SQL Server 2008 R2 to Window Server 2012 || SQL Server 2012. The previous server had 32gb of memory, upon booting the Max Server memory of 28gb would be consumed within one day.  The new server has 48gb of memory however upon booting the database

does not take the minimum memory of 4gb
after a week of running has only consumed 7gb of data

The database is mainly ad-hoc workload that does not utilize stored procs. So the hope in adding memory that was more of query plans would stay cached.

Any idea what could be causing this ?

Edit

dm_os_bufferpool_descriptors:


texts:
I am dealing with a db managed by a 3rd party framework and I can't change it... yet. Isn't that always the case.
Long story short they are never setting nullable fields to null. They are putting in defaults for everything. Think 0 for Int and '1900-01-01 00:00:00.000' for DateTime.
While there are millions of rows in the table, the columns are sparse and should be null most of the time.
I have created filtered indexes that have a [Where SomeField is not null].
While analyzing some query plans I noticed I was getting giant numbers back for [Estimated Number of Rows] and upon investigation I noticed my values weren't null like I thought would be.
This meant all of my indexes are bloated with default values.
I would like to create triggers that set the actual values to null if the value is a default value.
My question is, "Do triggers get fired before indexes are updated? Will I be updating my index twice?"


texts: Query optimization in case of TIMESTAMPDIFF

texts:
I have a table where I need to delete all rows older than 5 hours. Currently I'm doing:

delete from my_table
where TIMESTAMPDIFF(HOUR, from_unixtime(round(created_on/1000)), now()) > 5

The created_on column is a time stamp stored in milliseconds. I think adding an index on created_on column won't help since the query has a computed value (correct me if I'm wrong).

The delete statement works fine for smaller data sets, however for larger data sets (30-40k rows) it often gets stuck. Why does the delete statement get stuck for large data sets and how can I improve performance?


texts:
Sometimes during a SNAFU I have to run kill query xxxxxxx twenty or thirty times. Any sort of kill all command I am missing?

On account of how I don't like typing.


texts:
I have a SQL Authentication User: JOHNDOE

I would like this account to auto-expire or auto-disable after X days or at a certain date/time. Any suggestions or best practices to do this?


texts: Why does Oracle use a different byte length than java for the supplementary unicode character chipmunk?

texts:
I have java code trimming a UTF-8 string to the size of my Oracle (11.2.0.4.0) column which ends up throwing an error because java and Oracle see the string as different byte lengths. I've verified my NLS_CHARACTERSET parameter in Oracle is 'UTF8'.

I wrote a test which illustrates my issue below using the unicode chipmunk emoji ()

public void test() throws UnsupportedEncodingException, SQLException {
    String squirrel = "\uD83D\uDC3F\uFE0F";
    int squirrelByteLength = squirrel.getBytes("UTF-8").length; //this is 7
    Connection connection = dataSource.getConnection();

    connection.prepareStatement("drop table temp").execute();

    connection.prepareStatement("create table temp (foo varchar2(" + String.valueOf(squirrelByteLength) + "))").execute();

    PreparedStatement statement = connection.prepareStatement("insert into temp (foo) values (?)");
    statement.setString(1, squirrel);
    statement.executeUpdate();
}

This fails on the last line of the test with the following message:

  ORA-12899: value too large for column
  "MYSCHEMA"."TEMP"."FOO" (actual: 9, maximum: 7)

The setting of NLS_LENGTH_SEMANTICS is BYTE. Unfortunately, I cannot change this as it is a legacy system. I'm not interested in increasing the column size, just reliably being able to predict the Oracle size of a string.


texts: How to debug trivial MariaDB query with simple execution plan which often runs really slowly?

texts:
The query (with literal ? for dynamic query goodness):

SELECT UNIX_TIMESTAMP(my_timestamp_field) AS my_name
  FROM my_table
 WHERE my_column=?

This query is one of the most common in my slow query log (limit 1 second) - it has taken up to 10 seconds several times just in the last few hours. The lock time on every one of the slow queries except one was 50ms±2ms. I've done the research below, but I'm stumped for how to continue.

Is it possible that another lock somehow is taking up the time without being counted in the lock time? Are there any AWS EBS volume limits I need to be aware of?

Query plan with representative value:

MariaDB [my_schema]> EXPLAIN SELECT UNIX_TIMESTAMP(my_timestamp_field) AS my_name FROM my_table WHERE my_column=10094;
+------+-------------+----------+------+-----------------+---------------+---------+-------+------+-------+
| id   | select_type | table    | type | possible_keys   | key           | key_len | ref   | rows | Extra |
+------+-------------+----------+------+-----------------+---------------+---------+-------+------+-------+
|    1 | SIMPLE      | my_table | ref  | my_column_idx   | my_column_idx | 8       | const |    1 |       |
+------+-------------+----------+------+-----------------+---------------+---------+-------+------+-------+

The table is quite simple, with 21 fixed width columns:

MariaDB [(my_schema)]> SHOW CREATE TABLE my_table\G
*************************** 1. row ***************************
       Table: my_table
Create Table: CREATE TABLE `my_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `my_column` bigint(20) NOT NULL,
  `my_timestamp_field` datetime DEFAULT NULL,
  [other columns]
  PRIMARY KEY (`id`),
  UNIQUE KEY `ind_some_key` (`other_column_1`,`other_column_2`),
  KEY `my_column_idx` (`my_column`)
) ENGINE=InnoDB AUTO_INCREMENT=48033 DEFAULT CHARSET=utf8

The table is 45k rows, and all the columns are fixed length.

Running MariaDB 5.5 on CentOS 7 on a fast AWS EC2 machine.


texts: Is a 'block' and a 'page' the same thing in PostgreSQL?

texts: I've seen many blogs and videos talking about Postgres 'blocks'. Are these the same as 'pages' in other database management system implementations?


texts: How to solve "Servlet error" when using database control in Oracle 12c?

texts:
When I want to use Oracle 12c GUI, I receive an error as written below:

  Servlet error: An exception occurred.

  The current application deployment descriptors do   not allow for including it in this response. Please consult the application log for details.

My browser is Google Chrome, and the firewall and antivirus are also deactivated. I should also mention that I have started all Oracle services in services.msc section.
I have tried to use GUI by entering the address https://pedcom-pc:1158/em , too, but I received the same error.

That would be very helpful of you if you show me a solution to this problem.


texts: PostgreSQL is not working after installing Postgres-XC

texts:
I am using PostgreSQL 9.3 on Ubuntu 17. This set up was working fine, but it stopped working after installing Postgres-XC.

I tried uninstalling Postgres-XC and reverting back the data directory to the
default position. It didn't work and I'm getting

  psql: FATAL:  role "postgres" does not exist.

Any help will be appreciated.


texts: SQL Agent Jobs Automatically Disabled After Modifying Maintenance Plan

texts:
I modified a maintenance plan yesterday, to find out this morning (by using the last modified date from sysjobs) that all jobs related to the maintenance plan are disabled.

I can't figure out why this may be. The SQL Job Owner is sa and I merely changed a maintenance clean-up task with the maintenance plan and saved the job.


texts: 10 TB database backup / restore Question

texts: We have a very big production database which is 10 TB. We are considering migrating it to a cloud service, but I have to understand the risks and estimate the duration of the whole process. I backup the database once per month using Native SQL Server 2014 SP2 backup solution. The duration is almost 720 minutes - 12 h. The compression ratio is 5, meaning the backup size is almost 2 TB. We can't afford differential backups because accumulated, these take as much time and space as the Full, so we do T-log backups (~200 Mb each) instead each 10 minutes ~ 144 files per day. The BIG question is having all these stats, does anyone have any idea how long it would take to restore a 2 TB backup containing 10 TB data? Would it be much longer than the Full backup duration?


texts: How can coupled databases be kept in sync with selective uni-directional syncing and with connections that can be off for days?

texts:
I am trying to create a database topology that looks like this:

                  .------------.        Bad Connection.
Bad Connection.   | Central DB |        May go offline
May go offline    '------------'        for days.
for days.               |  |             .
             \          |  |            /
              ' .--------  ------------'
                |                      |
                |                      |
         .-------------.        .-------------.
         | Remote DB1  |        | Remote DB2  |
         '-------------'        '-------------'

Some of the tables are exclusively pushed from Central down to the Remotes

        .------------.
        | Central DB  |
        |-------------|
        | Master Data |
        | Table Foo   |
        '------------'
              |  |
              V  V
      .--------  ------------.
      |                      |
      V                      V
.---------------.     .---------------.
| Remote DB1    |     | Remote DB2    |
|---------------|     |---------------|
| Unmodified    |     | Unmodified    |
| Slave Copy of |     | Slave Copy of |
| Table Foo     |     | Table Foo     |
'---------------'     '---------------'

Some of the tables are exclusively pushed from Remotes up to Central and the
rows the that each individual Remote mutates are exclusively mutated by the individual Remote node.

        .---------------.
        | Central DB    |
        |---------------|
        | Slave         |
        | Table Bar     |
        |---------------|
        | Row a from DB2|
        | Row b from DB2|
        | Row c from DB1|
        | Row d from DB1|
        | Row e from DB1|
        | Row f from DB2|
        '---------------'
              |  |
              ^  ^
      .--------  ------------.
      |                      |
      ^                      ^
.------------.        .------------.
| Remote DB1 |        | Remote DB2 |
|------------|        |------------|
| Table Bar  |        | Table Bar  |
|------------|        |------------|
| Row c      |        | Row a      |
| Row d      |        | Row b      |
| Row e      |        | Row f      |
'------------'        '------------'

Remote DB1 should not get data generated on Remote DB2.  They only sync
up to Central and Central never pushes down other Remotes' Bar table rows.

Lastly, when connectivity is lost to Remote DB1 or Remote DB2 or Central
or any combination, each database should continue to operate locally and when
connection is restored, the updates to each table or sub-section of table that
they own should be pushed in the appropriate direction.

So, given this topology, and the constraints of having complete internet loss
to one or multiple remote databases on occasion with the addition that each DB
should merrily continue without syncing until connection is restored, is there a
cluster configuration that I can use in MariaDB, MySQL, or PostgreSQL that will
enable such a topology?  I understand that the Remotes are doing something
akin to "horizontal sharding", except that I don't want them to get other
Remotes' data.

The "Master Data" tables that are only mutated by Central clearly looks like a
classic Master-Slave relationship with the Remotes, except the the Remotes
should stay online when the master Central goes away.

With regard to the horizontally sharded  Remotes table Bar, I can imagine
assigning PK ID ranges that those Remotes exclusively mutate.

Is there a way to do this topology with clustering technology or do I need to
manually roll something with incremental database dumps like this technology in
MySQL?


texts: PgAdmin III - How to connect to database when password is empty?

texts:
I have installed PostgreSQL 9.1 on my PC (Win 7). I have a small Java application connecting successfully to it with login=sa and password="". The connection works.

However, it is refused from PgAdmin III itself. I get:

Error connecting to the server: fe_sendauth: no password supplied

How do I connect to my database from PgAdmin III with an empty password?

EDIT

This is just a test, not production code.


texts:
Working in Python/Django 1.8

I have a problem along the following lines:

Assembly Table

|Part
|Parent (FK to other Part)

i.e.:

Car
|--Tire
    |--Bolt A
    |--Bolt B

Journey Table

|Date
|Distance

I want to be able to track the journeys made by every part in my assembly, such as how far a tire has traveled. To do this, I created a table called Part Actions that stores the installed_date and removal_date.

|Part
|Parent
|Installed Date
|Removal Date

Here's the problem: How on do I query the total time for a (e.g.) bolt that could have been installed on one tire, removed and then installed on another car?

Bolt Serial 135 History:

|[date] Installed as part of tire 012 on Car XYZ
|[date] Removed as part of tire 012 on Car XYZ
|[date] Installed as part of tire 025 on Car XYZ
|[date] Removed individually due to damage.

I could log every sub-assembly change in Part Action for every change I will preform, but if I have 500 parts and I remove the engine, I fear I will clog up the database very quickly. Is there any other way around this? Should I decouple the hierarchy (i.e. assembly) from the time tracking element?

I've looked at both Django-MPTT and Django-Treebeard. Both seem to have the hierarchical structure but without the time element.

Please also ask for clarification if I've been unclear before writing extensive answers :)


texts:
I have several SSIS packages jobs running, and some months ago my disk got full because the size of the SSISDB database.

I noticed that the cleanup_server_retention_window was set to 365 days, and I changed it to one day. (It is a development server, and at this point I really dont care about the history).

The (big) problem, obviously, is now that the transaction log grows a lot and fast.

To prevent this, I start performing a full backup every week and a transaction log backup every day, and the size of the database is now controlled.

However, some more experienced guys are telling me that this is not the best aproach to this issue, but I cant see any problem with it..

I would like to know if there is a better solution for this.


texts: Strange issue with MySql 5.6 replication

texts:
I have a strange issue with MySql 5.6 Master-Master replication. I'm not sure if its entirely a replication issue or a client issue. We have two MySql databases on two different RHEL servers. We have setup a common schema for them and enabled schema level replication. The replication works fine when issuing any DML using the command line utilities or NaviCat client on either Master. But it does not seem to replicate the data when using SQL Developer or MySql WorkBench clients.

Here is the server information:

Linux "Hostname" 2.6.32-431.3.1.el6.x86_64 #1 SMP Fri Dec 13 06:58:20 EST 2013 x86_64 x86_64 x86_64 GNU/Linux

Here is the my.cnf file

[client]
#password       = your_password
port=3306
socket=./mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port=3306
socket=./mysql.sock
#skip-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
default-storage-engine=InnoDB
lower_case_table_names=1
server-id = 1

replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1
log-bin = <>mysql-bin.log
log-bin-index = <>/mysql-bin.index
log-slave-updates
replicate-do-db=schema_replicate
binlog-do-db=schema_replicate
replicate-do-table=schema_replicate.ref_tbl
relay-log = <>/relay-bin
relay-log-index = <>/relay-bin.index
relay-log-info-file = <>/relay-bin.info
slave-skip-errors = 1062

# binary logging format - mixed recommended
#binlog_format=mixed
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id       = 1

# The number of seconds the server waits for activity on a noninteractive
# connection before closing it. Range: 1-2147483.  2073600 is 24 days, close
# to maximum
wait_timeout=2073600

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :

innodb_data_file_path = ibdata1:10M:autoextend

innodb_buffer_pool_size = 48M
innodb_additional_mem_pool_size = 2M

innodb_log_file_size = 16M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

Any help is appreciated.


texts: restoring sinnge PostgreSQL DB on RDS- AWS

texts:
AWS doesn’t make it quite simple to restore just one DB on a RDS. I came up with below plans for DR and would like to know your feedback.
•

Our current RDS-T has D1, D2, D3 and D4  postgresql databases
Because of some human  error, we want to restore only DB D1 and D2  as T-1 and keep data in other Databases as it is (T).
We will decide  the what snapshot in AWS we want to restore from.
Create a new RDS-(T-1)  instance with old version of all DBs: D1, D2, D3 and D4.
Our goal here is to restore only D1 and D2 as of T-1.
For D1, since this is a small DB, I will dumb the DB file from RDS-(T-using pg_dump utility and then restore it back to RDS-T using  pg_restore utility. this should be fairly quick
For big DBs like D2 ( 200 GB), I would use a Data Migration service .
I will design a DMS beforehand and just change the endpoint . in this case Source : D2 on RDS-(T-1) and destination : D2 on RDS-(T).
Just run this service to restore data on D2 on RDS-T
We can always restore only one/two/n tables with this service.  Only modification we need to do is change the Jason file that has the mapping defined.
Once the DB on RDS-T is restored, we can just get rid of RDS-(T-1) .

is there a better way to approach this? Do you see any pitfalls with this approach?
Thanks,
Ro


texts: Find Max Value for each month for the last 3 months, properly

texts:
I have a query going that gets data for an ID for the last 3 months.  I need to tweak it so I get the highest value for each of the three months.  I've tried a couple of things with the aggregate function MAX, but I'm not getting anywhere.

I'm trying to get the max value for each of the past months ....

Here's the data from the query, currently sorted by date (asc):

ID      Date               Value
12410   01/03/2017 12:17    0.000178
12410   01/10/2017 11:36    0.000186
12410   01/17/2017 11:27    0.000189
12410   01/24/2017 13:09    0.000182
12410   01/31/2017 10:37    0.000169
12410   02/07/2017 11:03    0.000214
12410   02/14/2017 11:52    0.000176
12410   02/21/2017 10:51    0.000200
12410   02/28/2017 12:29    0.000194
12410   03/07/2017 08:39    0.000206

Here's the query:

select AnalysisID as "ID" , AnalysisDateTime as "Date", AnalysisValue as "Value" from AnalysisValueTbl
where
AnalysisID = 12410 and DatePart(m, AnalysisDateTime) = DatePart(m, DateAdd(m, -3, getdate()))
and DatePart(yyyy, AnalysisDateTime) = DatePart(yyyy, DateAdd(m,-3, getdate()))
or
AnalysisID = 12410 and DatePart(m, AnalysisDateTime) = DatePart(m, DateAdd(m, -2, getdate()))
and DatePart(yyyy, AnalysisDateTime) = DatePart(yyyy, DateAdd(m,-2, getdate()))
or
AnalysisID = 12410 and DatePart(m, AnalysisDateTime) = DatePart(m, DateAdd(m, -1, getdate()))
and DatePart(yyyy, AnalysisDateTime) = DatePart(yyyy, DateAdd(m,-1, getdate()))
order by AnalysisValue desc


texts:
Having an issue with exporting files to a network drive via the BCP host data file.

There is no problem in exporting a file to the C drive of the machine that the SQL Server is installed on, however when exporting to a network drive (Same domain) I receive the following error:

  *SQLState = S1000, NativeError = 0

  Error = [Microsoft][ODBC Driver 11 for SQL Server]

  Unable to open BCP host data-file NULL*

The test code is

    DECLARE @sql VARCHAR(8000);
    DECLARE @Path varchar(max)
    SET @Path = '\\network\location\'
    SELECT @sql = 'bcp "SELECT  FROM [Servername].[Schema].[Table]" queryout '
+ @Path + '-c -t, -T -S'
+ @@SERVERNAME;

    exec master..xp_cmdshell @sql;

I have taken a look at the other examples around this issue, however still no joy.
What other area should be considered in rectifying this issue..


texts: What is the "last" data that get's backed up, when running pg_dump on a live server?

texts:
I'm wondering: If I start doing a pg_dump of a very large database (it would take hours), that is still running actively receiving writes, what is then the last data that goes into the backup? Is it:

The data as-it-was, at the point in time where the pg_dump command was initiated.
The last changes that it encountered at some point where it was dumping that individual record.
Something else.

Bonus question: If I'm trying to dump a database as part of trying to rescue it from corrupt data, will it then make any difference whether I use the directory format or the custom format?


texts: Restoring 40 GB Oracle backup to get db objects into Oracle 11g XE

texts:
I have backup of oracle database size is almost 40 GB.This 40GB database backup contains lots of data that I don't care about. I just wanted to restore DB objects like tables structure, procedures, functions, triggers etc.
limitation: I have only Oracle 11G Express Edition(Free) which has limitation of only 10 GB.

is there any way to restore db objects from database backups?


texts: Creating Superset and Subset table with relationship mapping

texts:
I have requirement in one of the project where different entities
like Teacher,School,Student Store addresses in single table and maintain their relationship.

Example
-School can add teacher and store their address information.

-Teacher store their other address information.

-Teacher can add the student address information.

-Student can add their other address information.

-Teacher can mark their address information to School entity.

Those which are created by the each entity for others are visible to then and owner himself.
Example School enter teacher address information are their address where  other school cannot view it.

The teacher view it but could not edit it. If Teacher create address which are added in the school entities are private copies of teacher.
School can view it but not edit it.

Problem is how to map those tables.

Students :ID

Teachers :ID

Schools:ID

Addresses :ID |StarDate |EndDate

AnotherTable :ID

Other Fields which have relationship between each entities(e.g who added it and from whom)
(private and public relationship).

Comment on Security model and problem

Problem 1

Here i want to store address of School,Teacher and Student

a.First way is to store nullable FK of StudentID,TeacherID and SchoolID

  Problem of having 3 nullable foreign keys in the address table.

b.Second way is to create separate table like below
  SchoolAddress
  ID | StudentID | AddressID

  TeacherAddress
  ID | TeacherID | AddressID

  SchoolAddress
  ID | SchoolID | AddressID

c.Third way i can do is create a Person table.
Person
ID
type(student or teacher)

Addresses
ID
SchoolID
PersonID

i like c methologies  so that in future if i have another entities i can mapped to type field and enhance it.

Problem 3
How to solve the security model issues if above entities  and add each other address like below

School - added/update address- of - teacher (own by school no other entity can view  it except teacher  )
Student - added/update address- of - teacher (own by Student no other entity can edit it)
Teacher - added/update address- of - student
Teacher - added/update address- of - School.
Teacher - share it public and have their own address information and share to other school too.

Which made me to think have option b or separt table of each entities.

Problem 3
How to solve issue related to who own it. I was thinking of created junction table for who own it and entity relatioship.

Junction Table
ID | AddressID | FromPersonID | ToPersonID

But how to maintain Student relation with Teacher.


texts:
I'm trying to decrease the precision of a column from NUMBER(25) to NUMBER(10) but when I do I got error saying column to be modified must be empty to decrease precision

I do not have any data in the column length more than 10. So I want to change this precision forcefully.

How can I update this forcefully? Is it possible?


texts:
My ibdata1 file was taking up a lot of space so I followed the steps explqined on How to shrink/purge ibdata1 file in MySQL

However after deleting the ibdata file and recreating the DB I'm still not able to see any free space.

df -h shows 98% usage, du -sh shows usage of 774Mb

I've looked at the deleted files that are held open by certain processes using the lsof command and while there are a number of entries as below, restarting mysql makes no difference.

mysqld    5216 5219    mysql    6u      REG              253,1         0   25417110 /tmp/ib4eLfA1 (deleted)
mysqld    5216 5219    mysql    7u      REG              253,1         0   25417243 /tmp/ibcoRi27 (deleted)
mysqld    5216 5219    mysql   11u      REG              253,1         0   25417290 /tmp/ibhr6Thb (deleted)
mysqld    5216 5220    mysql    4u      REG              253,1         0   25417100 /tmp/ibs0uz9U (deleted)

Is there any way to force the system to check / rest free space?


texts: Derive daily delta from total daily counts? Tsql

texts:
I have a table with columns:

ID,
tableName,
totalRowCount,
Date

Each day I'm appending the number of rows in the relevant table. What I want to do is create a table which has the number of new rows created on each table each day. Whats the best way to do this?


texts: Postgres 9.6.2 not stopping on Windows. Any idea?

texts:
So, I've got Postgres 9.6.2 installed on Windows 10 64bit, and, when I start Windows, 2 things might happen:

The server starts normally. This is the most common one.
The server starts in an inconsistent way that hangs if you try to connect to it, like with psql. Stopping the service is not possible through the Services app. See screenshot.

When it starts inconsist, I can't stop the service. See:

It stays forever in a  Stopping state.

I'm not an experienced DBA. Any idea on how to solve this or find the reason?


texts: Not Able to access user privileges for root in mysql

texts: I accidentally deleted all privileges for root and users. I would like to know how to restore them. I don't have access to USER privileges for any other user.


texts: How to make related functions and procedures into one unit in mysql

texts:
I want to make related procedures and functions into single unit like packages in sql and access from that like

PackageName. Function() in mysql

Is it possible in MySQL if so explain with simple example.


texts: SQL Server : how can I measure database performance increase after index maintenance?

texts:
We are using a SQL Server 2005 database.

We currently have no maintenance on our databases indexes. Some of our indexes are showing over 85% fragmentation when looking at the table sys.dm_db_index_physical_stats.

We plan to reorganise the indexes on our own server as a test before performing the same operation on a client site. On our own server any negative impact will have minimal or no effect on our business. After this we will set up an automated plan to reorganise the indexes.

But before being able to roll this out to our client sites (2005 and 2008r2) we will have to justify the action and to do this we have to show non-technical people a measure of improvement. How can I justify my existence? How do I measure/demonstrate any improvements?


texts: How to prove "A->B,C then A->B and A->C" from Armstrong Axioms

texts:
I know that this is true but I don't understand how to prove it.

The Problem:

  Prove or disprove that if A->B,C, then  A->B and A->C

This answer is similar but it does not prove the relation using Armstrong's axioms.


texts: If I need to restrict access to certain databases?

texts:
So I've just created a MySQL server. I also created some users only having specific access to certain databases, but do I need to restrict access to default databases to the users I've created?
If so, how? Should I just create a role to try to restrict this access?

I have been using this site as a reference:
https://www.oreilly.com/library/view/mysql-reference-manual/0596002653/ch04s02.html


texts: Mariadb galera cluster master node started and reporting 2 nodes but second node service says activating mariadb

texts:
I managed to start the master node mariadb galera cluster, then I started the second node and I check on the first master node;
$ sudo mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"
Enter password:
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 2     |
+--------------------+-------+

but when I checked the second node it says:
 Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
   Active: activating (start) since Sat 2020-11-28 19:08:24 GMT; 11min ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
  Process: 7659 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUC
  Process: 7660 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SU
  Process: 7662 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/bin/..; /usr/bi
 Main PID: 7829 (mysqld)
   Status: "WSREP state transfer ongoing, current seqno: -1 waited 690.000000 secs"
    Tasks: 12 (limit: 881)
   CGroup: /system.slice/mariadb.service
           ├─ 7829 /usr/sbin/mysqld --wsrep_start_position=00000000-0000-0000-0000-000000000000:-1
           ├─ 7837 sh -c wsrep_sst_rsync --role 'joiner' --address '192.168.5.132' --datadir '/var/lib/mysql/' --pa
           ├─ 7838 /bin/bash -ue /usr//bin/wsrep_sst_rsync --role joiner --address 192.168.5.132 --datadir /var/lib
           ├─ 7890 rsync --daemon --no-detach --port 4444 --config /var/lib/mysql//rsync_sst.conf --address 192.168
           └─10407 sleep 1

Nov 28 19:08:24 raspberrypi systemd[1]: Starting MariaDB 10.3.25 database server...
Nov 28 19:08:27 raspberrypi sh[7662]: WSREP: Recovered position 00000000-0000-0000-0000-000000000000:-1
Nov 28 19:08:28 raspberrypi mysqld[7829]: 2020-11-28 19:08:28 0 [Note] /usr/sbin/mysqld (mysqld 10.3.25-MariaDB-0+d
Nov 28 19:08:28 raspberrypi mysqld[7829]: 2020-11-28 19:08:28 0 [Warning] Could not increase number of max_open_fil
Nov 28 19:08:31 raspberrypi rsyncd[7890]: rsyncd version 3.1.3 starting, listening on port 4444

what does it mean ?
port 4444 is open on both the nodes.


texts: SQL server 2016 (13.0.5026.0) Standard. High CPU ussage even without user queries

texts:
So SQL server CPU ussage is around 20% even when no users are making queries

I query the sysprocesses, and i can see some internal spid's:
select * from master.dbo.sysprocesses order by cpu desc

Any idea of what is this RESOURCE MONITOR with PREEMPTIVE_XE_CALLBACKEXECUTE wait type, and why does it take so much CPU; read somewhere it can be some memory management process, but in our case we have a fixed memory reserved for SQL Server:


texts: Extract all records even with missing "optional" fields in another table

texts:
I have a main product table ICITEM listing product IDs and another table ICITEMO (please don’t ask about the table names) with a number of optional product fields.  Not all optional fields exist for all products, for example:

ICITEM.FMTITEMNO
----------------
Product A
Product B
Product C
Product D

ICITEMO.ITEMNO | ICITEMO.OPTFIELD | ICITEMO.VALUE
---------------+------------------+---------------
 Product A     | COLOR            | Blue
 Pruduct A     | SIZE             | 12
 Product B     | COLOR            | Red
 Pruduct D     | SIZE             | 14

I need to retrieve all product IDs from the main ICITEM tables regardless if they have corresponding optional field(s) in the secondary table ICITEMO, so for example looking at the COLOR field:

ICITEM.FMTITEMNO | ICITEMO.VALUE
---------------- +--------------
Product A        | Blue
Product B        | Red
Product C        | NULL
Product D        | NULL

At the moment I have a rather ugly UNION query which works but I’m sure it can be done in a more professional and elegant way:

SELECT ICITEM.FMTITEMNO, ICITEMO.VALUE
FROM ICITEM, ICITEMO   WHERE ICITEM.ITEMNO = ICITEMO.ITEMNO  AND
ICITEMO.OPTFIELD="COLOR"

UNION

SELECT ICITEM.FMTITEMNO, NULL as GENDER
FROM ICITEM
WHERE ICITEM.FMTITEMNO NOT IN
(SELECT IC.FMTITEMNO
FROM ICITEM IC LEFT JOIN ICITEMO ON IC.ITEMNO = ICITEMO.ITEMNO
WHERE ICITEMO.OPTFIELD="COLOR")


texts: Prevent SQL from apparently executing an expensive sub query

texts:
I have a stored procedure of the form:
CREATE PROCEDURE StoredProcedure
    @SearchString NVARCHAR(MAX),
    @ComparisonType INT
AS
BEGIN

    SELECT  A.Value1,
            B.Value1,
            C.Value1,
            ...
    FROM TableA as A
    INNER JOIN TableB as B on B.Key1 = A.Key1
    INNER JOIN TableC as C on C.Key2 = B.Key2
    WHERE A.DateValue > '2020-10-01'
    And 1 = CASE @SearchString
        WHEN N'' THEN 1
        ELSE
            CASE @ComparisonType
                WHEN 0 THEN -- potentially expensive query 1 referencing TableC
                WHEN 1 THEN -- potentially expensive query 2 referencing TableC
                WHEN 6 THEN -- potentially expensive query 3 referencing TableC
                WHEN 8 THEN -- potentially expensive query 4 referencing TableC
                WHEN 10 -- potentially expensive query 5 referencing TableC
                ELSE 0
            END
        END
END

Even when it gets called with @SearchString set to N'' the query takes a minute and half to execute.  If I remove the "potentially expensive query" parts then the query returns the expected data virtually instantaneously.
From what I've read, the above shouldn't evaluate anything in the ELSE part of the outer CASE statement as @SearchString is N'' so why does it act as though it is?
If this is working as expected then how do I code this to force SQL not to execute the "potentially expensive query" parts?
I'm trying to understand why they appear to affect the main query despite my efforts to ensure that they aren't called at all.


texts: SQL Server Index Maintenance-Conditional Rebuilds

texts:
I have a question about index maintenance. I have a weekly process that does all of my database checks, etc. and a weekly rebuilding of indexes. Those rebuilds have been causing some locking pressure on one of our apps and I am looking at the possibility of scaling the rebuilds back, making them conditional under the right circumstances.

Now, I understand that every case and every application is different, imposing different kinds of tuning if I wanted it 100% optimized. But what I am looking for is a general "good enough" algorithm.

As we are running SQL 2016 Enterprise, it looks like I can do index rebuilds online, which will probably help with the locking pressure, but it would also be nice to be able to look at something in the database and say, hey, "this one can be skipped entirely this week.", either because it's one of my reference tables (no table content changes since last week), or some other slowly changing table...

For various reasons, I gave up on Microsoft's SQL Maintenance Plans a while back and I now use an in house c# program to do all of my SQL maintenance (transaction logs, weekly full backups, weekly checkdbs, weekly index maintenance, etc.) all while sending me an email when it's done letting me know if things went smoothly, or went south somewhere along the way. So, I don't mind adding some queries to my code to see about making index maintenance conditional or not.


texts:
I have the table shown below:

create table Order_Details
(
 ID int not null primary key
,Order_ID int
,Order_Description nvarchar(40)
)

We have inserted 27,500,000 rows in above table, and when I run below query it will takes 6-7 seconds.

select ID,Order_ID, Order_Description
from Order_Details
where Order_Description  like '%Football Size%'

I have applied a non-clustered index on Order_Description including ID and Order_ID.

Any other ways to optimize query and get result in faster way?


texts: Restoring MySQL 8.0 server from cold backup files

texts:
Recently, my database server running MySQL 8.0 crashed due to power outage. I have access to a cold backup (i.e., all physical files) of the server before the crash occurred. I do not have any other backups (e.g., mysqldump) of the server.

Now, I am trying to restore the server from the backup files that I have. The manual for this procedure suggests that I can directly copy all .ibd files as long as I can set up identical table structures (https://dev.mysql.com/doc/refman/8.0/en/innodb-migration.html).

Meanwhile, I am unable to find a way to create tables that are identical to those in the crashed system (I vaguely remember that when I did this years ago, I can restore table schema using .frm files but they do not seem to exist anymore in MySQL 8.0).

Any suggestions would be greatly appreciated. Thank you.


texts: Calculate products count based on category tree table

texts:
It is necessary to calculate how many products were created in each category, of course, that the products created in a child must be considered for the parent.
How can i do that SQL/Postgresql?

Second explanation if first is not clear - I should create a query that will calculate products count based on a categoryId and categoryIds childs too.

Database schema

Table Category
-------------
id
name
parentId
-------------
ROWS
1 rootCategory null
2 child1 1
3 child2 2

Table Product
------------
id
name
categoryId
------------
ROWS
1 productOne 1
2 productTwo 2
3 productOne 2
4 productOne 3

If categoryId = 1 is selected it should display products count from this category ant its children. In this example answer for root category count should be 4, because it has also two subcategories that have also three items in product table.


texts: Incorrect EQ_ROWS from low % sampled statistic (but not on all columns)

texts:
On a particular nullable guid column I am getting EQ_ROWS that are substantially incorrect (Table has 977 million rows). Default sample size is to blame - currently about .1%
Here is a single row out of the stats (the correct count(*) returns 121 for this guid)
RANGE_HI_KEY    RANGE_ROWS  EQ_ROWS     DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
7F0334A0...     2052729     43611.33    289                 7099.937

On another non-nullable guid column (same database) I get almost perfect statistics (correct count(*) is 12)
RANGE_HI_KEY    RANGE_ROWS  EQ_ROWS     DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
D1971F39...     273236.1    11.99996    187206              1.459549

Under what circumstances does the EQ_ROWS and AVG_RANGE_ROWS become multiplied like this?
This system has been upgrade from 2016 to 2019 recently, but a full 100% sample and then default sample statistics restores the same bad effect.


texts: Postgres, how insert "0" value into serial?

texts:
I have a serial id in my application table. However, I would like to insert a single 0 id record to mean global.

Is there a way I can insert this into my table without affecting the counter?


texts: mysql user creation with % to host , can only log on from remote host

texts:
Using MySQL v5.1.52

After creating a mysql user with the following commands:

create user 'user1'@'%' identified by 'pass1';
grant select on db1.* to 'user1';
flush privileges;

Then doing a 'mysql -uuser1 -p' in the command line then entering the password I still get this error:

Enter password:
ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: YES)

However when connecting from a remote system (with the -h) to the above server using the above credentials it works.

Why can I not log on locally with this user?

Here is the user/host outpout:

mysql> select User,Host from mysql.user;
+----------------+-----------------+
| User           | Host            |
+----------------+-----------------+
| user1          | %               |


texts: Managing a SQL Server database with over one terabyte of data

texts:
Recently we have an audit database that went over one terabyte and, since we have storage problems, management is looking for options.

My proposal is at the end of each year we take a backup and truncate all the tables which will keep the database manageable.

It will not be beneficial to have an archive database as it will again consume the same space.

I would like to have an expert opinion about the options that I can propose to the management that is either allocate more space or truncate the whole database every year.


texts: I just heard about mysqlpump - it looks like it was released with MySQL 5.7, however MySQL 5.7 still includes mysqldump. They're both backup programs, but could anyone summarize the main differences? Are there scenarios where one of them might work better? Is mysqldump going to be phased out for mysqlpump?


texts: SQL Server 2005 : How to use pivot instead of a bunch of case statements?

texts:
Please show me how I can modify this SQL select to use pivot instead of all the case statements.

SELECT t.ProjectId,
   COALESCE(MAX(ExecutiveChampion), 'n/a'),
   COALESCE(MAX(BusinessOwner), 'n/a'),
   COALESCE(MAX(BusinessAnalyst), 'n/a'),
   COALESCE(MAX(GeneralContractor), 'n/a'),
   COALESCE(MAX(PrimaryPM),  'n/a'),
   COALESCE(MAX(DevelopmentManager), 'n/a'),
   COALESCE(MAX(DevelopmentLead), 'n/a'),
   COALESCE(MAX(TDM), 'n/a'),
   COALESCE(MAX(PTM), 'n/a')
FROM (
    SELECT pl.ProjectId,
           CASE WHEN StakeholderCID = 95  THEN FullName ELSE NULL END as 'ExecutiveChampion',
           CASE WHEN StakeholderCID = 96  THEN FullName ELSE NULL END as 'BusinessOwner',
           CASE WHEN StakeholderCID = 97  THEN FullName ELSE NULL END as 'BusinessAnalyst',
           CASE WHEN StakeholderCID = 100 THEN FullName ELSE NULL END as 'GeneralContractor',
           CASE WHEN StakeholderCID = 101 THEN FullName ELSE NULL END as 'PrimaryPM',
           CASE WHEN StakeholderCID = 102 THEN FullName ELSE NULL END as 'DevelopmentManager',
           CASE WHEN StakeholderCID = 103 THEN FullName ELSE NULL END as 'DevelopmentLead',
           CASE WHEN StakeholderCID = 104 THEN FullName ELSE NULL END as 'TDM',
           CASE WHEN StakeholderCID = 105 THEN FullName ELSE NULL END as 'PTM'
      FROM @pList pl
     INNER JOIN StatusCode sc
        ON 1 = 1
       AND SCID IN (8, 9)
      LEFT OUTER JOIN ProjectStakeholder ps
        ON pl.ProjectId = ps.ProjectId
       AND sc.CID = ps.StakeholderCID
   ) as t
GROUP BY t.ProjectId


texts: Did anyone tried master-master replication in mongodb. i have used replica set and master-slave replication. But we want to go with 2 data centers and need a master in each data center. i am using latest 3.2 version. Can anyone tried this kind of architecture.


texts:
I've installed a PHP driven application in a client's 9i server (Oracle9i Release 9.2.0.1.0 - 64bit Production). Certain queries are having an awful performance (it can use from 15 minutes to hours just to calculate the execution plan!) and I've tracked the issue to a non-default value of the OPTIMIZER_FEATURES_ENABLE parameter: the default value for 9i is 9.2.0 but the customer changed it to 8.1.7. When I make the same change in my development box, I experience the same performance issues.

If they were running Oracle 10 or greater, I could change it myself for my own sessions but in 9i it's a static parameter that needs to be set for the whole instance. The change was made some time ago in order to support a very important legacy program. The client is currently waiting for an answer from the third-party supplier but I have the feeling that there's little chance of having it changed.

So, what are my options if the param needs to remain untouched? Can its effects be emulated with other changeable settings? Any other idea?


texts: How to (optimally) get random sample of (a.id, b.id) pairs from two tables (a, b)?

texts:
Let's assume I have to very simple tables

CREATE TABLE a(id integer PRIMARY KEY,
       t timestamp default now(),
       sensor_readings real[]);
CREATE TABLE b(id integer PRIMARY KEY,
       t timestamp default now(),
       sensor_readings real[]);

with some data on them

INSERT INTO a(id) SELECT generate_series(    1,   100);
INSERT INTO b(id) SELECT generate_series(10001, 10100);

In reality, table a might have about 100_000 rows, and table b about 50_000. In practive, also, the id sequence might have gaps (in the order of a few %). Thus, the cartesian product a x b has a cardinality of billions.

I want to take a random sample of 1_000 sorted pairs (a.id, b.id).
I can use something like the following query:

SELECT
    *
FROM
(
    SELECT
        *
    FROM
        (
        SELECT
            a.id AS a_id, b.id AS b_id
        FROM
            a CROSS JOIN b
        ORDER BY
            random()
        ) AS s0
    LIMIT
        1000
) AS s1
ORDER BY
    a_id, b_id ;

... but it would become extremely inefficient as soon as the number of rows on a or b grows (because of the growth of the CROSS JOIN).

Is there any way of doing something equivalent to this in an optimal manner? That is, is there a practical way of getting a random sample of rows from the a x b relation without actually having to instantiate it.

NOTE: There's no limitation on the fact that a.id or b.id can be repeated. Although the pair (a.id, b.id) cannot.

If I were trying to program this in an imperative language, I'd most probably would use a loop and be doing something like the following pseudo-code (and then, have it checked by a statistitian, to make sure I really take a sample where all the pairs have the same probability of being chosen):

start with a result set equal to {} (empty set)
while size of result set < 1000
    Pick the id value from a random row from table a -> rand_id_a
    Pick the id value from a random row from table b -> rand_id_b
    If (rand_id_a, rand_id_b) not in result set
        append (rand_id_a, rand_id_b) to result set
    end if
end while
sort result set and return it

Is there a way to achieve an equivalent result without resorting to loops?
If not, is there an efficient way to do it using plpgSQL? (or any other language)


texts: Postgres - should I keep the log file on the fastest drive

texts:
I wonder your recommendations / experience re database log file (pg_log) placement
We are debating internally if it is worth to keep that on fastest (and expensive) SSD volume, or can be kept on magnetic drives without hitting performance.
Have separate volume dedicated only for this - Id be more than happy to reallocate that to slower HDDs and use this space for DB volume
We have verbose logging (writing almost every single query into log) on and producing 100-200M  log file daily
Engine is PG 12.6 working on Linux.


texts: Temporarily moving a SQL Server 2016 database to SQL Server 2017 and then moving back. Is it possible?

texts:
If I take a backup of a database from a SQL Server 2016 instance and then restore it to a 2017 instance to do some work on it.

Can I then turn around and back up that database from the 2017 instance and use that to overwrite the original version on the 2016 instance?


texts:
Can any one help me with this query?

I want to change expiry date for db user to 3 days from current date, but I am not getting the query for the same.

I have installed Oracle Express 11g, it is sqlplus.


texts:
About once a week the query
sp_Blitz @IgnorePrioritiesAbove = 50, @CheckUserDatabaseObjects = 0
 shows "errors" with DB backups that are very old.

It's correct to show this, but is it possible to filter this error in case of availability group DB's which are on a secondary node and are not backed up (I'm using Ola's scripts to backup the DBs)?


texts:
I'm trying to find a reliable, efficient expression to calculate how many decimal digits it takes to write a positive integer.

Mathematically, the number of decimal digits in an integer n is 1 + floor(log(n)), where log is the common logarithm (base 10).

There are several ways to construct an equivalent expression using built-in functions, but some of them give incorrect results. Can someone explain why?

Here is an example.

How to calculate the log?

The simplest way to calculate the common logarithm is to use the LOG10 function.

If you prefer one function for all logarithms you can use the LOG function and specify base 10 with the second parameter.

Prior to 2012, SQL Server's LOG function would calculate only the natural log (base e=2.71828...). You can calculate the log to an arbitrary base of a number by dividing the natural logarithm of the number by the natural logarithm of the base.

The following query calculates all three expressions for some example values:

SELECT
  Number,
  LOG(Number, 10) AS LogAB,
  LOG10(Number) AS LogTen,
  LOG(Number) / LOG(10) AS LogOverLog
FROM (
  VALUES (999), (1000), (1001)
) AS Tally (Number);

Output:

Number      LogAB                  LogTen                 LogOverLog
----------- ---------------------- ---------------------- ----------------------
999         2.99956548822598       2.99956548822598       2.99956548822598
1000        3                      3                      3
1001        3.00043407747932       3.00043407747932       3.00043407747932

I have chosen the values 999, 1000, and 1001 because 1000 is a point where the number of digits steps up. 999 has 3 digits, 1000 has 4.

The value of all three expressions is visibly the same, and looks correct.

Let's move on to the floor step.

How to calculate the floor?

You can take the floor of each log in the previous example using a query like this:

SELECT
  Number,
  FLOOR(LOG(Number, 10)) AS FloorLogAB,
  FLOOR(LOG10(Number)) AS FloorLogTen,
  FLOOR(LOG(Number) / LOG(10)) AS FloorLogOverLog
FROM (
  VALUES (999), (1000), (1001)
) AS Tally (Number);

Output:

Number      FloorLogAB             FloorLogTen            FloorLogOverLog
----------- ---------------------- ---------------------- ----------------------
999         2                      2                      2
1000        2                      3                      2
1001        3                      3                      3

The values of each expression for 999 and 1001 are equal and correct. If we added 1 to each value, we would have a count of 3 digits in 999 and a count of 4 digits in 1001.

The values for 1000 are not the same! If we added 1 to each value, we would have a count of 4 digits in 1000 if we used the LOG10 function, and a count of 3 digits if we used the LOG function in either form.

There is an inconsistency here!

How can FLOOR(3) equal 2?

The implication is clear: using the LOG function would give me an incorrect count for some values, so I should use the LOG10 function.

But the value of each log expression itself is identical and correct. Why does the floor function produce different values from their input?


texts: Schema for ACL/User/Group/Role management

texts:
I'm building an app with some fairly complex permission and ACL rules and want to get my schema right.

These are my goals:

A user signs up on behalf on an organization.
Organization has groups. There can be any number of groups in an organization.
A user can belong to one or more groups.
An "Entity" can have an "ACL" record - this record indicates which users and groups can write to it, and which users and groups can read from it.

My current approach is to use PostgreSQL's arrays. So groups and organizations are simple tables with IDs and a foreign key.

User Columns

group_ids - array of integers
...others

Entity Columns

writable_user_ids - array of integers
writable_group_ids - array of integers
readable_user_ids - array of integers
readable_group_ids - array of integers

But I'll have to do all queries on entities like:

SELECT * FROM entity WHERE [<current id>] IN writable_user_ids OR [<group id list>] IN writable_group_ids;

Is there a better way to do this? I am fairly certain there is since a lot of apps follow this pattern.


texts: Will the transaction log shrink automagically in SQL Server?

texts: When SQL Server database in a SIMPLE mode, you don't have to care about the transaction log bakcups. But in a SIMPLE mode, the transaction log seems to grow as it does in FULL mode. Does is truncate automagically at some time point? Or do I have to truncate/shrink it manually?


texts: ALTER DATABASE MODIFY FILEGROUP [myfilegroup] AUTOGROW_ALL_FILES

texts:
I would like to change all file groups in my database to AutoGrow_all_Files, but only if it is currently AutoGrow_Single_File, like this:

ALTER DATABASE mydatabase MODIFY FILEGROUP [myfilegroup] AUTOGROW_ALL_FILES

How can I check this database property?


texts:
How can I perform "starts with" logic with full text search?

For example there is a Contains logic :

WHERE contains(Note,' "*word*" ')

I guess I need search by first word in the string, but how can I do it?


texts: PostgreSQL replication not working after base backup

texts:
Last week we had an incident with our master PostgreSQL server instance which got totally thrashed so, we had to switch to our slave as our one and only DB instance. After the initial chaos was controlled, we tried to create a new slave replica from scratch, according to instructions left by our DB provider who actually set up the first master-slave replica, but just yesterday I realized that the slave wasn't really replicating at all and, only has the contents of the initial base backup, so we have no redundancy right now in case of a new disaster!

On recovery.conf we have:

standby_mode = on
primary_conninfo = 'host=10.1.1.65 port=5432 user=replicador password=XXXXXXXX'

In /var/lib/postgresql.log (master) there are several messages like this:

2017-03-17 12:21:57 UTC [1969-1] replicador@[unknown] ERROR:  requested WAL segment 0000000100000B280000003A has already been removed

And in the replica's log file, many messages like:

2017-03-22 13:22:32 UTC [2827-1] LOG:  started streaming WAL from primary at B28/3A000000 on timeline 1
2017-03-22 13:22:32 UTC [2827-2] FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 0000000100000B280000003A has already been removed

Seems like the process wasn't fast enough to keep the master's pace. What parameters should I check/raise? Can I actually re-sync, or am I lost and once we'll have to do it all over again?

What could be missing? What additional info should I provide?

Environment:

select version()
PostgreSQL 9.4.9 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit

lsb_release -a
Distributor ID: Debian
Description:    Debian GNU/Linux 8.6 (jessie)
Release:    8.6
Codename:   jessie

uname -a
Linux ip-10-1-0-139 3.16.0-4-amd64 #1 SMP Debian 3.16.36-1+deb8u2 (2016-10-19) x86_64 GNU/Linux


texts:
I have a problem with ssis2008

I built a package that have a connection with SQL Server authentication (username : sa).

When I use BI, it works fine but when I build a job that used this package it doesn't work.

I tried several solution for example change the package protection level or using xml configuration file to save password but none of them worked. Can some body help me?

Errors are:

  Log in failed for user sa

or

  Can not decrypt the password

thanks


texts: Where condition execution sequence in MySQL

texts:
I am using MySQL 5.0.88. I have a query like:

select DISTINCT party.id
FROM party
  LEFT join party_identifier AS pi
  ON pi.party_id = party.id
WHERE (pi.source_note=?
       AND (first_name LIKE ? OR last_name LIKE ? OR pi.identifier LIKE ?))
  AND party.type = ?
  AND party.record_status_id = ?

If the Pi.source_note = ? evaluates to false, will the rest of the conditions will evaluated or not?

What is the execution sequence for the where conditions? (In terms specifically of SQL, not programming languages.)


texts: Find all tables in a DB with column names that contain that substring?

texts:
Say I have these three columns student_id, name, identify in three different tables in the same database.

The query should search for column names that contain that substring. i.e. id should return tablesstudent_id,identify from those three tables^.

This only works if the strings are equal.

I apologise if this is a repost I couldn't find another question similar, and I've hardly ever worked with databases. Thanks


texts: What's a good SQL Server backup schedule?

texts:
There seems to be a plethora of information regarding the process of setting up backup tasks, but not much information about the big-picture view of database backups. At least, it's hard to formulate a search engine query that gives you that information. I know there are three different types of backups:

Full Database Backups
Differential Database Backups
Transaction Log Backups

It seems I'm supposed to use all three of them. So, is this a schedule that makes sense?

1st of each month -- Do a full database backup.
Every day at midnight -- Do a differential database backup.
Every 15 minutes -- Do a transaction log backup.

This way, if my database fails on, say, the 12th, I'd just restore the full database backup from the 1st, do the 12 differential backups from the 1st to the 12th, and then finally restore the most recent transaction log (are the transaction logs differential?).

Finally, is a full database backup self-contained? i.e. Once I make a full database backup on February 1st, can I delete all the files from January? Of course, I'd keep a couple previous months' sets around just in case, but the question is conceptual.


texts:
I have an equation

((P.RealisedConsumption / (NULLIF((PO.ActualQty * P.QuantityPO), 0)
    / NULLIF(1000000, 0))) - 1) * 100 AS FibreScrapFactor

This works for the example order that I am looking at.  However if i take out the where clause (so i have all orders) i get divide by zero error.

If I limit the / 1000000 to 10 which i then have

((P.RealisedConsumption / (NULLIF((PO.ActualQty * P.QuantityPO), 0)
    / NULLIF(10, 0))) - 1) * 100 AS FibreScrapFactor

This works for all orders, but is incorrect I need to divide by a million.  How can I get this to work?   The columns are numeric(32, 16).


texts: How can I store function names to be executed on a subset of rows?

texts:
I have a table, accounts, which contains the vast majority of the data I am concerned with. accountTypes contains different types of accounts (Business, Personal, etc.). I want to perform calculations on each row of accounts based on the accountType. The best method I can think of for doing this would be to create UDFs, then store the names of the UDFs in a table typeFunctions, consisting of rows of accountTypeID and functionName, as in: P, "f_ageOfAccount" and B, "f_daysSinceLastcontact".

Unfortunately, joining this table to the accounts table and looping through it to exec the function name seems very kludgy.

Is there a better way to approach this problem?

Context:  Currently, the legacy version of this application stores ad hoc SQL in typeFunctions and execs the SQL, looping through the accounts with a cursor. Obviously, this has some performance implications and is not optimal when dealing with complex calculations.


texts: Perform UNION ALL regardless of column order in the two joined tables

texts:
SELECT * INTO TABLE1
FROM Table2
UNION ALL
SELECT * FROM Table3;
GO

I am using this query to stack two tables together into one table. These tables should have the columns in the exact same order for this query to execute successfully.

I am wondering if there is a trick we can do so that it works regardless of the column order. Is there a way to instruct SQL Server to automatically line up the columns from both tables by name and perform the UNION?

In SAS, the trick is to write a OUTER UNION CORR query, for example.


texts: Determine if columns in postgres table are inherited

texts:
I want to be able to list out the columns in a child table and see which ones are inherited from the parent table and which ones are defined on the child table.

SELECT column_name, inheritied
FROM column_view
WHERE table_name = 'a_child_table'

------------------------------------
| column_name       |  inherited   |
------------------------------------
| foobar            |      f       |
------------------------------------
| whizbang          |      t       |
------------------------------------

Is this possible?


texts: Should dates in dimensional tables use dimDate?

texts:
Assuming my dimDate has a surrogate key. Should all the date columns in the dimensional tables (not the fact tables) store the surrogate key of date dimension? Or just plain date?

For example, in dimensional table dimCustomer, there may be birthday, join date, graduation date, .... Etc.


texts: For absolute performance, is SUM faster or COUNT?

texts:
This relates to counting the number of records that match a certain condition, e.g. invoice amount > $100.

I tend to prefer

COUNT(CASE WHEN invoice_amount > 100 THEN 1 END)

However, this is just as valid

SUM(CASE WHEN invoice_amount > 100 THEN 1 ELSE 0 END)

I would have thought COUNT is preferable for 2 reasons:

Conveys the intention, which is to COUNT
COUNT probably involves
a simple i += 1 operation somewhere, whereas SUM cannot count on
its expression to be a simple integer value.

Does anyone have specific facts about the difference on specific RDBMS?


texts: After disabling replication, should I disable binary logging?

texts:
I have a master-slave setup currently but am going to remove the replication DB server. The DBs are running on MySQL 5.6. I have a couple of questions about removing the replication:

1) I have read that I have to just run

  stop slave; reset slave all;

on the slave and replication will stop, is that all I need to do?

2) I see on my master DB's my.cnf file:

  log-bin=mysql-bin.log

  log-slave-updates=1

  expire-logs-days = 14

  sync-binlog = 1

Should I remove or comment the above lines? Or should I let them be, I don't know if it's useful to keep logging enabled?


texts: How to get the parameter values of a stored procedure that's in the middle of execution?

texts:
Is there a way to get the values of the parameters that were passed in to a stored procedure while the stored procedure is in the middle of execution? (I'm trying to debug an actively running stored procedure.)

I know I can see the queries of the stored procedure that are currently being executed using the sys.dm_exec_requests and sys.dm_exec_sql_text DMVs but they don't show the values of the parameters being used.


texts: PostgreSQL: Recommended way of user creation/management for development, test and production. I use Ruby on Rails

texts:
I am trying to think of the best (aka recommended or common professional practice) way to create users/roles in PostgreSQL database for development, test and production environments.

I have to say that I use Ruby on Rails if that matters to the answers.

Often I find information that suggests in general the following steps after the installation of PostgreSQL (on Ubuntu 14.04):

create a role for development and a role for test with permissions createdb, login and a password
use those roles (i.e. users) in database configuration files (for connections strings for example)

I wonder whether that is the secure way to go. The users would be able to create/delete databases/tables at will then, isn't that right?

What if I only want to give them read/write access to only the tables that matter for development and test respectively?
And if I go this way any ideas about administration overhead or automizations?

I work alone on the project at the moment, but other developers might join later and I care to have the databases as secure as possible.

I also need to have an idea how to deal with production later. I intend to use Heroku as service provider, so probably some configuration will be given. Anyone experience with that?


texts: Get the pais_id not associated to fabricante_distribuidor_id and producto_solicitud_id, the opposite from SELECT query

texts:
I have this query for get all the pais_id given a fabricante_distribuidor_id and producto_solicitud_id, the query works fine.

SELECT DISTINCT nom.pais_id
FROM   negocio.fabricante_producto_solicitud neg
       INNER JOIN nomencladores.pais_fabricante_producto_solicitud nom
         ON ( neg.id = nom.fabricante_producto_solicitud_id )
WHERE  neg.fabricante_distribuidor_id = 1
       AND neg.producto_solicitud_id = 1

But I want to get the opposite, I mean all the pais_id that does not associated to fabricante_distribuidor_id and producto_solicitud_id, how?

NOTE:

I though this will be more easy but I'm getting some problems getting the data I need. Take a look to this piece of my model:

Well based on this (and was my bad, my sincerely apologies) what I need to get is the pais.id, pais.nombre that has not relation with any fabricante_producto_solicitud.fabricante_distribuidor_id having the same input, meaning fabricante_distribuidor_id and producto_solicitud_id, this is the right question!

I've tried this query on my own:

SELECT
    ps.id, ps.nombre
FROM
    nomencladores.pais_fabricante_producto_solicitud nom
LEFT JOIN negocio.fabricante_producto_solicitud neg ON (
    neg. ID = nom.fabricante_producto_solicitud_id
)
LEFT JOIN nomencladores.pais_fabricante_producto_solicitud pfs ON (
    pfs.fabricante_producto_solicitud_id = nom.fabricante_producto_solicitud_id
)
LEFT JOIN nomencladores.pais ps ON (
  ps."id" = pfs.pais_id
)
WHERE
    neg. ID IS NULL
OR NOT (
    neg.fabricante_distribuidor_id = 1
    AND neg.producto_solicitud_id = 1
)

But it's complete wrong since I get repeated results and also I don't get the expected output. Can I have some advice?

NOTE2: The tables on purple belongs to nomencladores schema and the one in orange belongs to negocio schema

Here is some structure & data files to play with, let me know if any have problems downloading

EDIT: Playing with queries

I'm still playing with queries trying to get the result I'm looking for but this, I made based on the ones leave here, is not working, meaning I'm getting all the rows all the time not matter what parameter changes, why?

SELECT
 nomencladores.pais.id as ID,
 nomencladores.pais.nombre as nombre
FROM
 nomencladores.pais
WHERE ID NOT IN(
 SELECT
     nomencladores.pais.id
 FROM
     negocio.fabricante_producto_solicitud
        INNER JOIN nomencladores.pais_fabricante_producto_solicitud ON (negocio.fabricante_producto_solicitud.id = nomencladores.pais_fabricante_producto_solicitud.fabricante_producto_solicitud_id)
        INNER JOIN nomencladores.pais ON (nomencladores.pais_fabricante_producto_solicitud.pais_id = nomencladores.pais.id)
        INNER JOIN nomencladores.fabricante_distribuidor ON (negocio.fabricante_producto_solicitud.fabricante_distribuidor_id = nomencladores.fabricante_distribuidor.id)
 WHERE
     negocio.fabricante_producto_solicitud.producto_solicitud_id = 3
AND
     negocio.fabricante_producto_solicitud.fabricante_distribuidor_id = 37
GROUP BY
     nomencladores.pais.id
)


texts: How to define alias in an ARRAY_AGG expression?

texts:
I'm trying to return pure JSON from a Postgres 9.2 table.

  SELECT ARRAY_TO_JSON(ARRAY_AGG(ALBUM_ROW))
  FROM (
    SELECT
      album,
      max(release_year) AS release_year,
      max(artwork_path) AS artwork_path,
      MD5(concat(album,release_year,artist)) AS token,
      ARRAY_AGG((media_files.position, media_files.token, media_files.title) ORDER BY media_files.position) as media_files
    FROM media_files
    INNER JOIN playlist_media_files ON playlist_media_files.media_file_id = media_files.id
    WHERE playlist_media_files.playlist_id = 1
    GROUP BY album, release_year, artist
    ORDER BY artist, release_year
  ) as ALBUM_ROW

This query works fairly well, however on the line:

ARRAY_AGG((media_files.position, media_files.token) ORDER ...) as media_files

I would like to alias position and token attributes in the result set.

AS apparently it's not permitted here.

I would have written:

ARRAY_AGG((media_files.position AS xxx, media_files.token AS yyy) ORDER BY media_files.position) as media_files

But it's not working.

Postgres documentations says:
"where aggregate_name is a previously defined aggregate (possibly qualified with a schema name), expression is any value expression that does not itself contain an aggregate expression or a window function call, and order_by_clause is a optional ORDER BY clause as described below."

http://www.postgresql.org/docs/9.3/static/sql-expressions.html#SYNTAX-AGGREGATES

Am I out of luck?


texts: Why most of Sql Injection need url like this "index.asp?id=123"

texts: I am trying to learn "SQL Injection" concepts and also trying to practice some Kali tools to learn, but there is a question that I do not understand and my question is why do most tools need URL's that end with "AAA.BBB?id=000" as a input string for injection.


texts: How to find the active transactions in VLF?

texts:
Is there a way to find the T-SQL statement that is part of the active transaction that is blocking the reuse of the transaction log file VLFs?

I am performing tlog backups on every 15 minutes, but sometimes the following query returns ACTIVE_TRANSACTION or LOG_BACKUP (even such is made):

SELECT [name]
      ,[recovery_model_desc]
      ,[log_reuse_wait_desc]
FROM [sys].[databases]

If I run DBCC Loginfo I can see that the firsts and the lasts rows have status 0.


texts: Sample query/DDL for producing page locks on a clustered index in SQL Server (without hints)

texts:
We currently have some indexes where ALLOW_PAGELOCKS is set to off. This presumably was done in order to reduce deadlocks. However I doubt that it would really had an effect back then.

Now I am trying to understand WHEN SQL Server actually chooses to start locking pages rather than keys in a clustered index. I asked Jonathan Keyhaisas recently and he told me that this could happen if I am touching rows on several subsequent pages. However I didn't manage to get any exclusive page locks by updating rows in a clustered index with a sample query.

Could you help me understand page locks better with a sample query and table? I am running SQL Server 2008 SP4.

Thanks in advance

Martin


texts:
This is the pivot I made:

SELECT *
FROM (
    SELECT *-- codigo,ID,Grupo,convert(int,CodOpcao) as CodOpcao
    FROM (
        SELECT *
        FROM (
            SELECT 39 as codigo
                , (ROW_NUMBER() over
                    (
                    PARTITION by T1.CodNrQuestao
                    ORDER BY T1.CodNrQuestao
                    )
                ) as ID
                , 0 as Grupo
                , T1.Opcao as CodOpcao_1
                , T2.Opcao as CodOpcao_2
                , T3.Opcao as CodOpcao_3
                , T4.Opcao as CodOpcao_4
            From tblQuestaoOpcao T1
                Cross Join tblQuestaoOpcao T2
                Cross Join tblQuestaoOpcao T3
                Cross Join tblQuestaoOpcao T4
            Where T1.codNrQuestao = 811962
                And T2.CodNRQuestao = 811757
                And T3.CodNRQuestao = 812009
                And T4.CodNRQuestao = 812011
        )  as T0
        UNPIVOT (
        [CodOpcao] FOR [DescQuestao] IN (
        [CodOpcao_1]
        , [CodOpcao_2]
        , [CodOpcao_3]
        , [CodOpcao_4]
        )
        ) AS U
    )  as T0
)  as T0
PIVOT ( -- from here I start to get errors
    CONVERT(VARCHAR, AVG(codOpcao))
    FOR descQuestao IN (
        CodOpcao_1
        , CodOpcao_2
        , CodOpcao_3
        , CodOpcao_4
        )
) AS U

I need that this:

Become this:

CODIGO | ID | GRUPO | CODOPÇÃO_1 | CODOPÇÃO_2 | CODOPÇÃO_3 | CODOPÇÃO_4
 39      1     0

SQL is giving me error sintax in everything I try.

This is the fiddle of the table:

http://sqlfiddle.com/#!3/8d3d0


texts: SQLPackage : Import or Export SQL Azure BACPAC without users

texts:
I know how to use SQLpackage.exe. I can export from SQL Azure and create a bacpac. I can also Import to SQL Server via SQLPackage.exe.
I want to export or import without the users in the database. The users are preventing the normal import to fail.

Error importing database :could not import package.
Error SQL72014 Cannot grant, deny or remove permissions to or from special roles

I can't delete the users before I export, or exclude them from the import.
Currently we have to backup the database, restore it on SQLAzure, delete the use on this second database, then create a bacpac from the restored database.
How do I get around this?
can I use IgnoreUserSettingsObjects with my sqlpackage ? I tried using the parameters but it fails


texts:
I have an SQL Query to make an XML Document. Everything works except one thing:

A short insert of my SQL Query at this time is:

select OITM.ItemCode, OITM.ItemName, OITM.Price

Output in XML:

<DocumentElement>

      <ItemCode>12345</ItemCode>
      <ItemName>test</ItemName>
      <Price>20.00</Price>

</DocumentElement>

I want this Structure:

<DocumentElement>

          <ItemCode>12345</ItemCode>
          <ItemName>test</ItemName>
          <Prices>
               <Price>20.00</Price>
          </Prices>

    </DocumentElement>

How can i insert the <Prices> tag?


texts:
I'm running the following with alembic:

def downgrade():
    conn = op.get_bind()
    conn.execute(sa.sql.text('''
DROP TRIGGER update_rated_trigger ON rating;
DROP FUNCTION update_rated;
    '''))

It gives the error:

  sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) syntax error at or near ";"
  LINE 2: DROP TRIGGER "update_rated_trigger";

Not sure what the problem is with postgres?


texts: how to get the date of a file using xp_cmdshell?

texts:
So far I've this code

CREATE TABLE #tmp (strData VARCHAR(1000))
INSERT INTO #tmp EXEC xp_cmdshell 'dir C:\Users\Giba\Desktop\Folder\cmdshell1\*.txt'
SELECT * FROM #tmp --WHERE strData LIKE '09/25/2018'
DROP TABLE #tmp

but I want to select only date of the .txt files and compare them with current date. Can someone help to select only the date?


texts: SQL backup to URL failures on Azure Storage

texts:
I have been using SQL Backup to URL to backup databases in the range of 100-400 GBs on SQL Server 2012 SP3. Intermittent failures have become increasingly common that I am now experiencing a failure more often than not. The error message is

Msg 3202, Level 16, State 1, Line 1
Write on "https://StorageAccount.blob.core.windows.net/container/databasebackup.bak" failed: Backup to URL received an exception from the remote endpoint. Exception Message: ParallelUpload Failed with timeout = 00:03:00

Msg 3271, Level 16, State 1, Line 1
A nonrecoverable I/O error occurred on file "https://StorageAccount.blob.core.windows.net/container/databasebackup.bak:" Error could not be gathered from Remote Endpoint.

Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

This matches closely the issue described in the Microsoft docs regarding Proxy Errors. However, this is an Azure virtual machine and storage account both hosted in an Azure east coast data center. There is no proxy connection and the latency to the storage account should be very low. The initial few minutes of the backup is quite fast (800Mbps) and then the speed abruptly drops to near zero and a timeout occurs 3 minutes after the speed drop. Backups of smaller databases succeed without issue using the same parameters.

Perhaps I need to limit the number of parallel threads that Backup to URL is using. Is this possible? There are no errors logged on the Azure storage. Since this is a 2012 server, I am using storage account key in the SQL credential which limits me to page blobs.  Is there any way to use block blobs on a 2012 server?

Any other places I should be looking to find out what might be causing the connection to be lost?


texts: How to creare a second login for a second database in Azure? How to deny a user access to one of the databases?

texts:
I have an Azure database and I have created another one on the same server. After that, I have created a user and a corresponding login (see the script below) with the intention to use it for the second database only. After running the script I have googled (below), the second user can access both databases. How to deny the second user access to the first database? There are DENY and REVOKE, that deny specific rights, but it is not obvious to me which one should be used.

Bonus question: was it possible to create a user that can access the second database only?

IF NOT EXISTS (SELECT 1 FROM master.sys.databases WHERE ('[' + name + ']' = '[Elmah5]'))
    CREATE DATABASE Elmah5;
GO

IF NOT EXISTS (SELECT 1 FROM sys.sql_logins  WHERE name = 'ElmahAdmin')
    CREATE LOGIN ElmahAdmin WITH PASSWORD = ''
GO

IF NOT EXISTS (SELECT 1 FROM sys.database_principals  WHERE name = 'ElmahAdmin')
    CREATE USER ElmahAdmin FOR LOGIN ElmahAdmin WITH DEFAULT_SCHEMA=[dbo]
GO

HOW IT SHOULD HAVE BEEN DONE

Connect using your primary account.
Switch your current database to MASTER (circled in, just type in MASTER and hit ENTER) and run

IF NOT EXISTS (SELECT 1 FROM master.sys.databases WHERE ('[' + name + ']' = '[SecondDB]'))
    CREATE DATABASE SecondDB;
GO
IF NOT EXISTS (SELECT 1 FROM sys.sql_logins  WHERE name = 'SecondDBAdmin')
    CREATE LOGIN SecondDBAdmin WITH PASSWORD = ''
GO

Still under your first login, switch to SecondDB and run

CREATE USER SecondDBAdmin FOR LOGIN SecondDBAdmin WITH DEFAULT_SCHEMA=[dbo]
GO
EXEC sp_addrolemember @rolename = 'db_owner', @membername = 'SecondDBAdmin'
GO


texts: Shards error msg “can't use localhost … either use localhost or ipadresses”

texts:
I'm trying to set up a a test environment on my pc including sharding. I've added 2 shardservers, a configsvr and a router instance. When i'm trying to add a shard I get following errormsg: "Can't use localhost as a shard since all shards need to communicate. Either use all shards and configdbs in localhost or all in actual IPs. host: localhost:27017 isLocalHost:1""

I searched online and I found a few topics about it but nowhere they explain how exactly and since I'm new to mongoDB it's quiet hard to understand what's going on. I looked here for example. But since I never used anything else than localhost I find it pretty weird.

This is how I set up:

2 shardsvr instances:

    mongod --shardsvr --replSet rs1 --port 27017 --dbpath D:\data\db
    mongod --shardsvr --replSet rs2 --port 27018 --dbpath D:\data2\db

1 configsvr instance:

    mongod --configsvr --replSet rs0 --port 27019 --dbpath D:\datacfg\db

I initialised the 3 replica sets (another question, do they need to be all different replicasets? or not?).

Then I added a mongos instance:

    mongos --configdb rs0/localhost:27019 --port 27020

And then when I'm trying to connect to mongos & addshards it goes wrong:

    mongo --port 27020

Since I'm never specifying a host I assume that he always uses localhost? no? I don't know how to check anyway. If you need more info tell me.

Thanks in advance


texts:
We are having a SQL 2016 Standard Edition Server holding 4TB of data.  And the compressed backup using maintenance plans is taking around 1.5 hrs. Our storage team claims that as PURE FlashArray is used, SQL compression is not required as PURE compresses the backup. PURE documentation here - https://support.purestorage.com/Solutions/Microsoft_Platform_Guide/Microsoft_SQL_Server/001_Microsoft_SQL_Server_Quick_Reference  - talks about uncompressed backups and de-dupe. Brent's article about de-dupe - https://www.brentozar.com/archive/2009/11/why-dedupe-is-a-bad-idea-for-sql-server-backups/.

What is the best practice SQL compression or Uncompressed backups on PURE. How do i measure it, as the stats are more powerful than statements.


texts: Going from separate databases to shared databases and schemas (Multi Tenant Data Architecture)

texts:
I am eagerly trying to design the perfect multitenant data architecture for my database.

I am getting inspiration from an article posted by Microsoft, based on scenario 3.

I have been given all my tables a tenant-column, and afterwards created a matching VIEW

ALTER TABLE {table_name} add TenantID varbinary(85) DEFAULT SUSER_SID()

CREATE VIEW {view_name}
AS
   SELECT *
   FROM {table_name}
   WHERE TenantID = SUSER_SID()

Each of my clients has their own database user account, these users has been given following rights:

db_datareader
db_datawriter
public

And then I changed the following:

DENY SELECT ON {table_name} TO {user_name}
GRANT SELECT ON {view_name} TO {user_name}

Anyway, am I missing something for now? :)


texts: 'utf-8' codec can't decode byte ERROR when querying data

texts:
I have created this foreign SQL Server using tds_fdw foreign data wrapper:

CREATE SERVER servprod_sqlprod_mydb
FOREIGN DATA WRAPPER tds_fdw
OPTIONS (servername 'XX.XX.XX.XX', port '1501',msg_handler 'notice', database 'WHATEVER');

CREATE USER MAPPING FOR postgres
    SERVER servprod_sqlprod_mydb
    OPTIONS (username 'xxx', password 'xxxxxxxxxx');

CREATE FOREIGN TABLE tds_test (
    caclamun character varying,
    caclacal character varying,
    catipvia character varying,
    cadescri character varying,
    caidenti character varying,
    cafechal character varying,
    cafechba character varying)
    SERVER servprod_sqlprod_mydb
    OPTIONS (table_name 'dbo.MYSQL TABLE');

When opening the table I am getting:

'utf-8' codec can't decode byte 0xd1 in position 4: invalid continuation byte

I think it is a postgresql issue and not tds_fdw's, but I am not sure.


texts: Is there a rule of thumb regarding the size and number of a buffer pool instances?

texts:
I know the consensus seems to be approximately 75% of ram should be dedicated to the buffer pool. But, I'm more interested in the optimal number of buffer pool instances. What are some things to consider when determining the setting?

For example, let's say I have a 75GB buffer pool. Should I just assign 75 buffer pool instances at 1GB each? 15 at 5GB each? Or, 10 at 7.5GB each?

Should I start out with a single buffer pool and increase based on whether or not I see locking?

What should the max number of instances be based on - available cores?

Thanks - Your response is greatly appreciated.


texts:
I am looking for an authoritative list of objects for extended events, I want to know what version an object becomes available and some clues about what the value really represents.  I have googled and not finding anything comprehensive.

As an example Microsoft shows version history for
SQL:BatchStarting Event Class going back to 2014, but I have used it on SQL 2012 SP4 (with SSMS 17.4)

Yes I can use Select * from sys.dm_xe_objects to get a list of All the sys.dm_xe_objects and while there is a description it is very general.

If I want to learn something like Difference between sqlserver.nt_username & sqlserver.username there does not seem to be any resources other than comparing results and guessing.

If I write an XEvent, I should have a resource I can use to define what versions of SQL it will run on, I should not need to go trying it on different version to figure it out.

Where can I find an Authoritative Extend Event object list?


texts:
http://www.postgresql.org/docs/9.2/static/transaction-iso.html

  The Repeatable Read mode provides a rigorous guarantee that each transaction sees a completely stable view of the database. However, this view will not necessarily always be consistent with some serial (one at a time) execution of concurrent transactions of the same level. For example, even a read only transaction at this level may see a control record updated to show that a batch has been completed but not see one of the detail records which is logically part of the batch because it read an earlier revision of the control record. Attempts to enforce business rules by transactions running at this isolation level are not likely to work correctly without careful use of explicit locks to block conflicting transactions.

Isn't that a phantom read, which is not possible in repeatable read mode?

The documentation says that a query in a repeatble read transaction sees a snapshot as of the start of the transaction, then how could it be possible for a query to read inconsistent data?


texts: How to stop all nodes on FCI SQL server?

texts:
My Setup: We have two nodes SQL setup with Active/Passive Windows Failover Cluster instance.
My Goal: To stop the running SQL Server service on the active node and prevent failover from happening for a period of time and then bring them back online again.
I'm using Windows Failover Cluster Manager and after doing quite a bit of research I see the following options available to me and how I think each of these options mean.

Drain Role - Done at node level to remove all running roles out of a
node, essentially pausing it on a cluster and is useful when you want
to patch or take specific node in a cluster offline.
Stop Role -
Done at Role level to take the FCI server offline and prevent
failover capability on all nodes belong in this specific Role.
Shut
Down Cluster - Done on the cluster group level, it is similar to
Stop Role except it stops all roles.
Stop Cluster Service - Done at
node level, similar to Drain Role????

Can you verify if what I wrote based on my understanding for these are correct?
So if I want to shut off my active/passive FCI SQL Server service, I plan to do the following, are the steps correct?
Bring down FCI SQL Server services:

Stop Role on the role that runs my SQL FCI to prevent failover
between active/passive node.
Shut off SQL Server services using the Windows
services window on the Active Node.

Bring up FCI SQL Server Services:

Turn on SQL Server service using window services on one of the node I want
to be active.
Start Role on the role that run my SQL Server FCI.

Another option I could think off but unsure its validity, basically i'm stopping the SQL service and at the same time refuse it to have the ability to failover to another node.
Stop the cluster service on my "Passive" node.
Stop the SQL Server service on my "Active" node.


texts:
I'm trying to go through the book "7 Databases in 7 Weeks" and I'm completely stuck at starting a Postgres server.

My current issue is that when I run pg_ctl start I get the following error:

LOG:  database system was shut down at 2013-06-12 17:18:24 PDT
LOG:  invalid magic number 0000 in log file 0, segment 1, offset 0
LOG:  invalid primary checkpoint record
LOG:  invalid secondary checkpoint record
PANIC:  could not locate a valid checkpoint record
LOG:  startup process (PID 1454) was terminated by signal 6: Abort trap
LOG:  aborting startup due to startup process failure

I have no idea how to go about fixing this. I'm running PostgreSQL 9.2.4 on Mountain Lion 10.8.3.


texts: SQL Server 2012 database stuck restoring

texts:
I'm trying to restore my development database DEV with a backup of my production database PROD. Every time I go through this process, the DEV database gets stuck in a Restoring state. Using the SQL "RESTORE DATABASE DEV WITH RECOVERY" does not break it out of the Restoring state. The only way I can get it out of that state is by using the Restore Database wizard. I have generated a script from that wizard and, with the exception of the SINGLE_USER and MULTI_USER lines, the SQL is the same as what I'm using below.

USE [master]

ALTER DATABASE DEV SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE DEV
FROM DISK = N'D:\Backup\PROD-20160629.bak'
WITH FILE = 1,
MOVE N'PROD' TO N'D:\Data\DEV.mdf',
MOVE N'PROD_log' TO N'D:\Logs\DEV.ldf',
NOUNLOAD,  REPLACE,  STATS = 5

ALTER DATABASE DEV SET MULTI_USER

Why does my database get stuck in a Restoring state from this statement?


texts: Backup and Restore of master KEY and Certificate are not working on same server but different DBs. I have SQL Server 2012

texts:
I am following these steps but still not able to DECRYPT the data at destination.

In Source DB I have created Following:

CREATE MASTER KEY ENCRYPTION BY
PASSWORD = 'Analytics@2020';

CREATE CERTIFICATE Certificate_Customer_Data
   WITH SUBJECT = 'Customer Sensitive Data';

CREATE SYMMETRIC KEY EmailID_Key_01
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Certificate_Customer_Data;

Then I backup Master Key and Certificate from Source DB to files:

BACKUP MASTER KEY
  TO FILE = 'C:\Shivendoo\MasterKey.bak'
  ENCRYPTION BY PASSWORD = 'Analytics@2020';

BACKUP CERTIFICATE Certificate_Customer_Data TO FILE = 'C:\Shivendoo\DBCertificate.cer'
    WITH PRIVATE KEY (ENCRYPTION BY PASSWORD = 'Analytics@2020',  FILE = 'C:\Shivendoo\PrivateKey.pvk');

Next I restore Back to Destination DB

RESTORE MASTER KEY
  FROM FILE = 'C:\Shivendoo\MasterKey.bak'
  DECRYPTION BY PASSWORD = 'Analytics@2020'
  ENCRYPTION BY PASSWORD = 'Analytics@2020';

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Analytics@2020';

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

CLOSE MASTER KEY;

CREATE CERTIFICATE Certificate_Customer_Data FROM FILE = 'C:\Shivendoo\DBCertificate.cer'
  WITH PRIVATE KEY(FILE='C:\Shivendoo\PrivateKey.pvk',
                   DECRYPTION BY PASSWORD='Analytics@2020')

CREATE SYMMETRIC KEY EmailID_Key_01
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Certificate_Customer_Data;

Finally I loaded data from Source using this query:

OPEN SYMMETRIC KEY EmailID_Key_01
   DECRYPTION BY CERTIFICATE Certificate_Customer_Data

SELECT ENCRYPTBYKEY(KEY_GUID('EmailID_Key_01'), [EMAIL]) AS [EMAIL]
FROM [dbo].[Source_Table]

Data loaded with encryption to Destination but when I try to Decrypt at destination using below query, It returns as NULL value for column DecryptedEmail

OPEN SYMMETRIC KEY EmailID_Key_01
   DECRYPTION BY CERTIFICATE Certificate_Customer_Data

SELECT CONVERT(NVARCHAR, DECRYPTBYKEY([EMAIL])) AS DecryptedEmail,[EMAIL],* FROM [dbo].[Destination_Table]

Please can you help..?

I am not getting any error but not getting expected outcome at Destination DB when I try to Decrypt Email at Destination which was Encrypted at Source.