texts: Best pratice to store dates group by months-year key par value

texts:
I am making one service which needs to hold data grouped and calculated by year-month combination.  I know how to calculate data and put it on new table. But I am confused what kind of data type I should use for storing month - year value.  Here is what I have considered.

Two Integer columns one for year other for month (clear to understand and strong to manipulate (anybody who uses this table can easily use ranges and orders) but I think that would take more space than another solution.
Use a Date field, and always store one date for entire month. (Hard to explain to users how it works and how to create WHERE clauses)
One varchar(7) column and put there stings like 2012-02,2013-01. For me this is easy to understand but hard to manipulate.

Whick I should pick? or is there other solution.  Can someone suggest according to performance which way is going to be good solution, Because most of queries is going to using data range in where solution and my new table will have around 2-5 million records.


texts: How to update a single record in a table based of foriegn key ID in another table?

texts: I am still very new to sql. I am having troubles updating one table. I am sending information from a web application into Microsoft SQL 2008 database. I have a table called EmployeePermissions that has employee ID and Permission ID. The Permission ID links this table to the Permissions table where different permissions are located and given a true or false value. If I am sending SQL the employee ID and the true or false values of the specific permissions, how do I update the Permissions table by sending the information through the EmployeePermissions table? Hopefully the diagram below can help explain a little better. Thanks!


texts:
General question on dbms_stats and auto sample size?

what is the impact of having IOTs with overflows and you switch to auto_sample_size and enable approximate NDV .

How does it impact?


texts: Output File Default Location using MultiServer Agent Jobs

texts:
I've used Ola's scripts for quite a while at several companies, and just started a new job at a company who has already implemented MultiServer Agent Jobs to execute backups, DBCC CheckDB and IndexOptimization. For whatever reason, the person who implemented the solution in mulitserver jobs removed the output file "string" (i.e. the path to the Log folder on the instance). When I copied that value from Ola's scripts after I deployed them on my local machine, it caused 2 of the full backup jobs to fail because it "couldn't find" the path. The machine that the failures occurred on has 2 instances, and so far as I know, the path to "Errorlog" is default and based on the installation selection of that default. Both instances are SQL Server 2008 R2 Standard, while all the rest of our instances are 2014 Enterprise (which really should make no difference...but who knows?).
I have removed the output file on the backup jobs so they will run successfully, but I'd really like to figure out the best way to implement this on all of our instances.
At the very least, I suppose I could remove the 2 instances that are barfing with the path and recreate them on the local instances...but that really kills the benefit of using MultiServer SQL Agent jobs.

Anyone have any ideas?


texts: Mysql temp table insert from json can't handle null values

texts:
The MySql-8 JSON_TABLE function allows you to handle JSON data as if it was a table.

I'm trying to use it to populate a temporary table using the generated table but I believe I'm hitting a bug.

When a null value is inserted into an INT NULL column, the following error is thrown : Invalid JSON value for CAST to INTEGER from column quantity at row 1

Here is the offending code

DROP TABLE IF EXISTS json_temp_table;
CREATE TEMPORARY TABLE json_temp_table (
  item_id int NOT NULL PRIMARY KEY,
  model_number varchar(100),
  quantity int NULL
)
ENGINE = INNODB
SELECT
  json_tb.item_id,
  json_tb.model_number,
  json_tb.quantity
FROM JSON_TABLE
(
'[{"item_id":1,"model_number":"MFJA53","quantity":4},{"item_id":2,"model_number":"HSRHJN5","quantity":null},{"item_id":3,"model_number":"FAFAF1","quantity":345}]'
, "$[*]"
COLUMNS
(
item_id int PATH "$.item_id",
model_number varchar(100) PATH "$.model_number",
quantity int PATH "$.quantity"
)
) json_tb;
SELECT
  *
FROM json_temp_table

Is there a way to make json-generated tables insert nulls into temp tables ?

The weird part is the select statement works fine without the insert. Also, temp tables by themselves can definitely handle null int values. I don't understand why those two don't mix.


texts: How to verify (CHECKSUM or MD5) dataset you received over a network?

texts: I will remotely query mysql database and I want to check that the received dataset matches the table in the host computer. How do you do that? I am fairly new to sql database. I know of Checksum and md5, but how do you use them to verify the received dataset is not corrupted on the local machine.


texts: PostgreSQL Streaming Replication Tools - Windows

texts:
Is there any clusterware available for PostgreSQL clusters running on Windows OS? I'm specifically interested in automatic failover and STONITH functionality. I've done a fairly extensive Google search and had no luck so far.

Thanks.


texts: Failover clustering SQL server 2016 standard

texts:
I have some trouble finding my way in and distinguishing standard vs enterprise features in SQL Server 2016. What it boils down to: Does failover clustering support multiple databases in SQL Server 2016 Standard Edition?

Or put differently (perhaps?) is failover clustering always on the entire instance and all databases it contains?


texts: What indexes do I need to define for my bridging tables?

texts:
I have three tables in my database:

users:

user_id (PK)

roles:

role_id (PK)

users_roles:

user_id (PK, FK)
role_id (PK, FK)

I've done plenty of research surrounding indexing columns but I can't find a definitive answer to my question which is what columns should be indexed in this bridging scenario when I have a compound primary key. I need to be able to conduct WHERE queries using both user_id and role_id in the bridging table. I've read mixed answers and don't understand how many indexes need to be defined.

My current indexes for this bridging table include:

PRIMARY (user_id, role_id)
INDEX (user_id)
INDEX (role_id)

I assume ^ is incorrect.


texts: I want to enable dead lock trace flag -t1222 on a production SQL Server 2014 instance. Many databases on this instance are configured with database mirroring. What (if any) impact to mirrored databases will occur after applying -t1222 and restarting the server?


texts: High availability SQL Server without Active Directory

texts:
I'm fairly new to SQL Server. I'm looking to achieve a highly available SQL Server setup where if the primary database fails, the backup database takes over. What would you guys recommend?

I'm familiar with MySQL replication, however in the case of SQL Server it doesn't seem straight forward to switch the publisher role to a subscriber.

Ideally not a high availability cluster because I'd like to avoid Active Directory. I've had a brief search on google, however I'm asking on here to get the opinion of experienced pros.

Thanks in advance.


texts:
When I try to create a procedure, I get an error message:

  SQL80001: Incorrect Syntax:" Create Procedure" must be the only statement in the batch

This is my procedure:

CREATE PROCEDURE [dbo].[USP_UserRoles_Select]
(
    @Rolename VARCHAR(30) = ''
)
AS
BEGIN
    Select ID,Name
    FROM
        AspNetRoles
    WHERE
        Name like  @Rolename +'%'
END

What does the error mean and how to fix it?


texts: Apply function in UNIQUE constraint in MySQL 5.7

texts:
I would like to add a multi column unique constraint to my MySQL 5.7 database table like this:
ALTER TABLE report_summary
ADD UNIQUE (
    tenant_id,
    app_id,
    date_format(FROM_UNIXTIME(statistic_time/1000), '%Y-%m-%d')
);

...but it gives me the following error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM_UNIXTIME(statistic_time/1000), '%Y-%m-%d'))' at line 1
I want to add a constraint that one day only have one line for one app per tenant, so what should I do to achieve this?


texts: Unable to create a new listener in oracle 11g

texts: In net manager i get an error message "A valid logfile is required" when i click on listener and choose '+' button. and then it keeps on giving me that error when i click on any other tab or button in net manager.


texts: How to use variable and drop in test script?

texts:
I have to modify certain Oracle Packages. Basically I will be extracting functionality and moving it to C.

They provided me with some "Test" scripts that I shoud be able to run to test that my changes don't break the underlying logic of the package. But they are not working.

Also I noticed they have hardcoded some values in their original scripts and I am trying to use variables instead. I have never worked with Oracle so I am confused.

In Toad I have a Script along these lines:

declare
myTestId VARCHAR(32);

begin

select  '23423DSSF34SD234' into myTestId from dual;

--Here my drop for my_test_table1 and my_test_table2
drop table my_test_table1;
drop table my_test_table2;

--Here create table my_test_table1 and my_test_table2

--Here I have a bunch of inserts

--Here are some selects statements

end;

However when I run the script it fails when it reaches the "Drop" statements it doesn't like them. How can I perform drop statements in my script?

I have been reading around and it seems that in Oracle it is not a good idea to create temporary tables like this? What would be the best approach?


texts: Looping through JSON and insert data to Postgres DB

texts:
Below is how i have my data at the moment. How would i achieve in postgres with Postgresql to get all those "class_name" and "id" values and store them on their own rows in the database. I can store the data as JSON or JSONB, that doesnt matter but im having trouble looping through all the cases and getting all the values.

I have tried json_each and combination of jsonb_query_path and json_array_elements but only have managed to get only the first case in each suite.

Would appreciate any help here and i would prefer to do this with postgresql instead of python

 {
  "duration" : 6109.1104,
  "failCount" : 0,
  "passCount" : 4389,
  "skipCount" : 0,
  "suites" : [
    {
    "cases" : [
      {
        "class_name": "testname1",
        "id": 231
      },
      {
        "class_name": "testname2",
         "id": 233
      }
    ],
    "duration" : 0.012,
    "id" : null,
    "name" : "EventTest",
    "stderr" : null,
    "stdout" : null,
    "timestamp" : null
     }
   ]
 }


texts: What is the difference between "OPTION (OPTIMIZE FOR UNKNOWN)" and "OPTION (USE HINT('DISABLE_PARAMETER_SNIFFING'))"

texts: Can you tell me what is difference between OPTION (USE HINT('DISABLE_PARAMETER_SNIFFING')) and OPTION (OPTIMIZE FOR UNKNOWN)?


texts: Database Security - Prevent column writing?

texts:
I am using a Postgres db. I am concerned if someone gets in my DB, they could change the price of my products to $0 and download my products.

Am I able to prevent writing / editing of that column except by a specific user?

I'm in a rails app, if it matters.


texts:
I'm wondering if there are any auditing processes I can enable to see what is causing these errors, like sql statements being run, or any other metadata that I could use to track down the source of these errors?

I am aware of DBA_AUDIT_TRAIL (which uses SYS.AUD$), but it isn't giving me what I need.  Fine Grained Audit Trail may be what I need?  Not sure....

Here are my auditing settings for your review, anything I can change here to give me more to work with?

SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------------
audit_file_dest                      string      /01/daa01/oracle/db/admin/DAA/adump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      DB


texts: Select all rows from a table where a value is NOT found?

texts:
I have 3 tables:

ve_survey_questions

Qid | Question | CategoryId

ve_survey_answers

AnswerID | QuestionId | Answer

ve_survey_useranswers

id | UserId | QuestionId | AnswerId | Datetime

This is a multiple choice test. So ve_s_Q is all possible questions, ve_s_A is the possible multiple-choice answers, and ve_s_useranswers links the answer chosen by that particular user.

I'm trying to display every question that the user has NOT yet answered with a two step query:

    //check user answers
     $surveyanswerQ = "SELECT * FROM ve_survey_useranswers ORDER BY QuestionId";
     $surveyanswerResult = mysqli_query($db, $surveyanswerQ);
     $surveyanswer=  mysqli_fetch_assoc($surveyanswerResult);

     //select all unanswered questions
     $surveyQ = "SELECT * FROM ve_survey_questions q
                          JOIN ve_survey_answers a
                          ON q.Qid=a.QuestionId
                          WHERE q.Qid <> {$surveyanswer['QuestionId']}
                          ORDER BY a.QuestionId LIMIT 4 ";
     $surveyResult = mysqli_query($db, $surveyQ);

This does not work as supposed to... How can I write a better query?


texts:
I am using db2inst1 to connect to a database in DB2 which I have installed on my machine. Therefore, db2inst1 user does not require username/password authentication (borrows them from the OS). I would like to change that, and force every time a connection is initiated a username/password to be requested.

More specifically, this is how the authentication configuration looks like:

  db2 get dbm cfg|grep -i auth

 GSS Plugin for Local Authorization    (LOCAL_GSSPLUGIN) =
 Server Connection Authentication          (SRVCON_AUTH) = NOT_SPECIFIED
 Database manager authentication        (AUTHENTICATION) = CLIENT
 Alternate authentication           (ALTERNATE_AUTH_ENC) = NOT_SPECIFIED
 Cataloging allowed without authority   (CATALOG_NOAUTH) = NO
 Trusted client authentication          (TRUST_CLNTAUTH) = SERVER
 Bypass federated authentication            (FED_NOAUTH) = NO

  db2 connect to dbName

   Database Connection Information

   Database server        = DB2/LINUXX8664 10.1.0
   SQL authorization ID   = DB2INST1
   Local database alias   = DBNAME

  db2 connect to dbName user db2inst1 using password

   SQL1639N  The database server was unable to perform authentication because
   security-related database manager files on the server do not have the required
   operating system permissions.  SQLSTATE=08001

I have played with some authentication combinations for "AUTHENTICATION" and "TRUST_CLNTAUTH" without much luck.


texts: Recover an Innodb mysql database from an EBS on Ec2

texts:
My instance crashed on AWs, and i had to create a new one and mount my Vol that contain my web application and the Mysql Database.

After i stoped the Mysql Server i mounted the vol and also mounted mysql folders from the colume to /var/lib/mysq, /var/log/mysal and /etc/mysql  using this command lines:

echo "/mnt/vol/etc/mysql /etc/mysql     none bind" | sudo tee -a /etc/fstab
sudo mount /etc/mysql

echo "/mnt/vol/var/lib/mysql /var/lib/mysql none bind" | sudo tee -a /etc/fstab
sudo mount /var/lib/mysql

echo "/mnt/vol/var/log/mysql /var/log/mysql none bind" | sudo tee -a /etc/fstab
sudo mount /var/log/mysql

[Tutorial ref : aws.amazon.com/articles/1663 ]

Then i tried to run the Mysql again, with :

# service mysql start

but with no joy.

And when i run mysqld i got this errors messages :

# mysqld
140123 19:34:56 [Warning] Can't create test file /mnt/vol/var/lib/mysql/ip-15-120-63-11.lower-test
140123 19:34:56 [Warning] Can't create test file /mnt/vol/var/lib/mysql/ip-15-120-63-11.lower-test
140123 19:34:56 [Note] Plugin 'FEDERATED' is disabled.
mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
140123 19:34:56 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
140123 19:34:56  InnoDB: Initializing buffer pool, size = 8.0M
140123 19:34:56  InnoDB: Completed initialization of buffer pool
140123 19:34:56  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.

I can see Permission issue for InnoDB! but what i am curious about is that i get this erro when i try to run my mysql command

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'

Solution :

Step 1

Now Mysql have an apparmor profile enabled by default that will prevent mysql to access non standard area of the file system.

To fix this, i had to add /mnt/vol/var/lib/mysql (or whatever non standard path you are using) to the list of authorized paths in the apparmor mysql profile you will find in /etc/apparmor.d/.

In my case i found it under :

# sudo vi /etc/apparmor.d/usr.sbin.mysqld

NB: You can also put mysql profile in complain mode using "sudo aa-complain" as described in the server guide should show you this in the logs.

Step 2

Run Mysqld to be sure that everything is okey:

# mysqld

Step 3

Run mysql and check the it status :

# sudo service mysql start
# sudo service mysql status

You have to find your Mysql up and running now.
You canalso check mysql log file for more details under /var/log/mysql/error.log


texts:
I've got a simple blog database in postgres-8.4 which has two tables, articles and comments. I have a query (generated by Django) that wants to get the latest article of type 'NEWS' and also find the number of comments for that article. It does that with the following query:

SELECT "articles"."id", "articles"."datestamp", "articles"."title", "articles"."shorttitle", "articles"."description", "articles"."markdown", "articles"."body", "articles"."idxfti", "articles"."published", "articles"."type", COUNT("comments"."id") AS "comment__count"
FROM "articles"
LEFT OUTER JOIN "comments" ON ("articles"."id" = "comments"."article_id")
WHERE ("articles"."type"='NEWS')
GROUP BY "articles"."id", "articles"."datestamp", "articles"."title", "articles"."shorttitle", "articles"."description", "articles"."markdown", "articles"."body", "articles"."idxfti", "articles"."published", "articles"."type"
ORDER BY "articles"."datestamp" DESC
LIMIT 1;

None of these tables is particularly large, and yet that query takes 46ms. The execution plan is:

Limit  (cost=119.54..119.58 rows=1 width=1150) (actual time=46.479..46.481 rows=1 loops=1)
   ->  GroupAggregate  (cost=119.54..138.88 rows=455 width=1150) (actual time=46.475..46.475 rows=1 loops=1)
     ->  Sort  (cost=119.54..120.68 rows=455 width=1150) (actual time=46.426..46.428 rows=2 loops=1)
           Sort Key: articles.datestamp, articles.id, articles.title, articles.shorttitle, articles.description, articles.markdown, articles.body, articles.idxfti, articles.published, articles.type
           Sort Method:  quicksort  Memory: 876kB
           ->  Hash Left Join  (cost=11.34..99.45 rows=455 width=1150) (actual time=0.513..2.527 rows=566 loops=1)
                 Hash Cond: (articles.id = comments.article_id)
                 ->  Seq Scan on articles  (cost=0.00..78.84 rows=455 width=1146) (actual time=0.017..0.881 rows=455 loops=1)
                       Filter: ((type)::text = 'NEWS'::text)
                 ->  Hash  (cost=8.93..8.93 rows=193 width=8) (actual time=0.486..0.486 rows=193 loops=1)
                       ->  Seq Scan on comments  (cost=0.00..8.93 rows=193 width=8) (actual time=0.004..0.252 rows=193 loops=1)
 Total runtime: 46.574 ms

The articles table has the following index defined (amongst others):

idx_articles_datestamp" btree (datestamp DESC) CLUSTER

Before I clustered it, the query execution was more in line with the estimates, around 119ms.

To my untrained eye, it looks like the sort is what's taking the most amount of time here. It also seems to be trying to sort on all the GROUP BY fields, the issue being that it's trying to sort on three relatively large fields, body, markdown and idx_fti.

My question is this: Is this an unreasonable amount of time for this query to take, or is there something obvious I've missed that I can use to speed this query up? All the other queries requested by this blog site take around 1-5ms to execute, so this one stands out as taking a long time. I appreciate there's an OUTER JOIN and a SORT, which don't really help. However, I'm not an expert, so if anyone has any suggestions, that'd be hugely useful.


texts: MySQL: Expanding table columns and insert/update values

texts:
I need to combine two tables into one.

Table s_ogr

id | ad | numara | yurt_id |

Current Table s_kontrol

id | ogr_id | yurt_id

What is needed s_kontrol_k

id | ogr_id | ogr_numara |ogr_ad | yurt_id  `(ogr_id represent the id from s_ogr)`

( ad = name , yurt_id = department_id, ogr_id is student_number(reason of problem, should be student_id)  )

My Sittuation; s_kontrol is full of data. I need it to be like s_kontrol_k , but new two fields should be taken from s_ogr table , by the match operation of s_ogr and s_kontrol (s_ogr.id=s_kontrol.ogr_id and s_ogr.yurt_id =s_kontrol.yurt_id). Previous mistake was not to add the ogr_id as student_id into s_kontrol

How to create my new s_kontrol_k with filled values of new columns. I am able to copy the same values from s_kontrol but could'n achieve to fill new colums


texts:
I have a table like so:

effective_from  archived      cal
2020-01-19      2020-01-20    15.3
2020-01-13      2020-01-19    42.2
2020-01-17      2020-01-18    13.6
2020-01-16      2020-01-17    11.2
2020-01-15      2020-01-16    7.2
2020-01-14      2020-01-15    7.2
2020-01-13      2020-01-15    8.6
2020-01-13      2020-01-14    4.2
2020-01-12      2020-01-13    3.7

I'd like to write a query that returns a table that has the most recent effective_from row, as determined by the archived row. The most recent effective_from row is the one with the most recent archived date. There are three rows with an effective_from date of Jan-13, but the one I'm interested in is the one where archived = 2020-01-19, cal = 42.2

I can do it in a brute force way, where I order the table by archived and then do a select and union on each row in turn based on the effective_from column:

SELECT effective_from, archived, cal FROM stack_question ORDER BY archived ASC;

(SELECT * FROM stack_question WHERE effective_from <= '12-Jan-2020' ORDER BY archived DESC LIMIT 1)
UNION
(SELECT * FROM stack_question WHERE effective_from <= '13-Jan-2020' ORDER BY archived DESC LIMIT 1)
UNION
(SELECT * FROM stack_question WHERE effective_from <= '14-Jan-2020' ORDER BY archived DESC LIMIT 1)
UNION
(SELECT * FROM stack_question WHERE effective_from <= '13-Jan-2020' ORDER BY archived DESC LIMIT 1)
UNION
(SELECT * FROM stack_question WHERE effective_from <= '15-Jan-2020' ORDER BY archived DESC LIMIT 1)
UNION
(SELECT * FROM stack_question WHERE effective_from <= '16-Jan-2020' ORDER BY archived DESC LIMIT 1)
UNION
(SELECT * FROM stack_question WHERE effective_from <= '17-Jan-2020' ORDER BY archived DESC LIMIT 1)
UNION
(SELECT * FROM stack_question WHERE effective_from <= '13-Jan-2020' ORDER BY archived DESC LIMIT 1)
UNION
(SELECT * FROM stack_question WHERE effective_from <= '19-Jan-2020' ORDER BY archived DESC LIMIT 1) ORDER BY effective_from ASC;

This gives me the expected result:

2020-01-12  2020-01-13 3.7
2020-01-13  2020-01-19 42.2
2020-01-19  2020-01-20 15.3

But of course that isn't actually a viable way of doing it. I could always write a function in pg/PLSQL or whatever, but I feel there's an efficient way to do this with just a SELECT. CTEs/recursion seem like the most promising approach, but I can't quite wrap my head around how it would look for my example based on what I've read.

Does anyone have any rough pointers on the best approach here?

I've put my test case on dbfiddle.


texts: changing mysql configuration . Getting errors like Table is marked as crashed and should be repaired

texts:
On Our server I have done following configuration for Database: [server has 2 core, 2 GB ram and windows 2008 operating system]

  query_cache_size=         32M
  [Old] query_cache_size=         50M
  [New]

  thread_cache_size=        8
  [Old] thread_cache_size=        100
  [New]

  key_buffer_size=             384M
  [Old] key_buffer_size=             600M
  [New]

  innodb_flush_log_at_trx_commit=1
  [Old] innodb_flush_log_at_trx_commit=0
  [New]

  innodb_buffer_pool_size=16M
  [Old] innodb_buffer_pool_size=250M
  [New]

  innodb_thread_concurrency= 0
  [New] innodb_read_io_threads=       64
  [New] innodb_write_io_threads=     64
  [New]

though application was working fine but now i am having a problem regarding mysql table getting crashed.

CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 1194 Table 'mytable' is marked as crashed and should be repaired. The SQL statement executed wa....

My sql version is 5.5.17


texts: SQL Server - register Service Principle Name without service restart?

texts:
I have a live SQL Server 2012 environment that had no SPN registered - this has previously not been an issue as Kerberos authentication wasn't required.  But now it is required.

I have configured everything correctly, but after these changes I want to ensure that SQL Server can register the SPN's successfully- preferably without a service restart as this is a live environment.

Is there a way of doing this?

P.S. I know I can register the SPN's manually, but I want to make certain that they can be registered automatically.


texts: oracle format specifiers: to_number vs to_char

texts:
SQL> select TO_NUMBER(123.56,'999.9') from dual;
select TO_NUMBER(123.56,'999.9') from dual
                 *
ERROR at line 1:
ORA-01722: invalid number

SQL> select TO_CHAR(123.56,'999.9') from dual;

TO_CHA
------
 123.6

SQL>

I am having a hard time in understanding the nuances of Oracle SQL. For example, have a look at the two queries above.

Why does first query fail but second one succeed?


texts: Incorrect statistics are used on index seek

texts:
I have a table-valued function and ordinary table and need to perform INNER JOIN on them. Because, the engine thinks that the function is returning always one row, I try to store the results in temporary table and then to perform the join operation - in both cases, the Estimate number of rows count that are read from the table/index is wrong.

When the temporary table is used, the correct Estimate number of rows count value is used for the rows returned by the function. All involved indexes are rebuilt.

Why I am worried about this? This join is part of more complex query which execution is very slow. I believe this is due to the fact that the statistics that are used by the engine are wrong and not the optimal execution plan is used. Even when the query use parallelism again nested loop is used to join the tables.

If I use a HASH or MERGE hints on join the correct statistics are used and better execution plan is built but I prefer to find a way to provide the correct statistics instead of forcing the engine to do something.

Could anyone tell why always one row is expected? Maybe because table-valued function is used, the engine thinks one row is going to be read from the table/index?


texts:
I have simple EAV like database which has 3 tables: products, attributes, attribute_values.

I am using below code to find all products that have attribute 'length' and then I am using substring function to get rid of 'inch' and after that I am using between to get all products that have 'length' between 0 and 5.

SELECT products.name,products.id
FROM products
JOIN attribute_value_product on products.id = attribute_value_product.product_id
JOIN attribute_values on attribute_value_product.attribute_value_id = attribute_values.id
JOIN attributes on attribute_values.attribute_id = attributes.id AND attributes.name = 'length'
WHERE substring_index(attribute_values.value,'inch',1) + 0 BETWEEN 0 AND 5

Everything works fine however I am getting plenty of warnings:

Warning: #1292 Truncated incorrect DOUBLE value: 'black'
Warning: #1292 Truncated incorrect DOUBLE value: 'phat farm'
Warning: #1292 Truncated incorrect DOUBLE value: '2 kg'
Warning: #1292 Truncated incorrect DOUBLE value: 'roca wear'

it looks to me like that substring is going through all attribute_values in the database, even those that are not reffering to attribute 'length' - is it possible to change that ?


texts: MySQL/MariaDB stored procedure compiles with typos

texts:
I just asked a similar question to this, but that turned out to be because MySQL column names are case insenstive and I was creating an ambiguity. I took the advice given and prefixed all my params with p. The problem is that if I type the param value with a misspelling the SP still compiles, but then fails at run time. Is there something I can do about this?

Here is an example:

DELIMITER $$

USE `MobiFit_Dev` $$

DROP PROCEDURE IF EXISTS `User_Signup` $$

CREATE DEFINER = `root` @`localhost` PROCEDURE `User_Signup` (
    pEmail VARCHAR (250),
    pHashedPassword BINARY(60),
    pFirstName VARCHAR (100),
    pLastName VARCHAR (100),
    pGender ENUM ('Male', 'Female'),
    pDateOfBirth DATE,
    pHeight DECIMAL (3, 2),
    pCurrentWeight DECIMAL (4, 1)
    -- CALL User_Signup('lee@gmail.com', 'password', 'Lee', 'Brooks', NULL, NULL, NULL, NULL);
    -- CALL User_Signup('lee@gmail.com', 'password', 'Lee', 'Brooks', 'Male', CURRENT_DATE(), 1.85, 101.3);
)
BEGIN
    DECLARE vRoleId INT;

    SELECT
        id INTO vRoleId
    FROM
        Role
    WHERE `Code` = 'CUSTOMER' ;

    INSERT INTO USER (
        Email,
        HashedPassword,
        RoleId,
        FirstName,
        LastName,
        Gender,
        DateOfBirth,
        Height,
        CurrentWeight,
        CreatedAt
    )
    VALUES
        (
            pEmail,
            pHashedPassword,
            vRoleId,
            pFirstName,
            pLastName,
            pGender,
            pDateOfBirth,
            pHeight,
            pCurrentWeigh, << Note the typo, this still compiles
            UTC_TIMESTAMP()
        ) ;
END $$

DELIMITER ;


texts:
So I have a table that looks like this:

And here's my query for that table:

select b.Barcode,a.ItemCode,b.Color,b.Size,b.Class,d.Locationcode,c.price,
SUM(case when e.description = 'Selling Quantity' and d.LocationCode = e.LocationCode                 then 1 else 0 end) as 'Selling Qty',
SUM(case when e.description = 'Stock Quantity' then 1 else 0 end) as 'Stock Quantity'
from masterfile.item a
left join masterfile.itemdetail b on a.ItemCode = b.ItemCode
left join masterfile.itemprice c on a.ItemCode = c.Itemcode and
b.Color = c.ItemColor and b.Size = c.ItemSize and b.Class = c.ItemClass
left join masterfile.locationinv d on a.itemcode = d.itemcode and
c.ItemColor = d.Color and c.ItemSize = d.Size and c.ItemClass = d.Class
left join masterfile.location e on d.locationcode = e.locationcode
group by b.Barcode,a.itemcode,b.color,b.size,b.class,d.locationCode,c.price

What I want is to merge these two rows, so what will happen is that selling qty and stock qty will have both 1 value because the two rows were merged.

Locationcode 001 is for selling and 002 is for stock quantity.

I want to make it look like this:

   Barcode       ItemCode      Color Size Class LocCode1 LocCode2 Price  Sell Qty   Stock Qty
   888200000001 W09118-DP28U    N/A   29    A      001      002     200      1          1

I'm kinda clueless with merging stuffs like this, so thanks in advance!


texts: I have a three-node MongoDB test replica running normally. I want to add an Ops Manager to it also residing on these three nodes. Do I need to install another set of MongoDB binary for the Ops Manager's databases in addition to the current replica?


texts: Combine many table into one table in mysql table design

texts:
I'm grappling to design table relation. Version is Mysql 5.6.10.

My case is like below

bill table, payment table, contract table, recruitment tables... are existing.

Currently we're considering new function it will use bill or payment table. So I have designed new_function_table.

CREATE TABLE new_function_table`(
  id int unsigned not null auto_increment,
  bill_id int unsigned null default null,
  payment_id int unsigned null default null,
  primary key(id),
  index fk_new_function_table_bill_id_idx (bill_id asc),
  index fk_new_function_table_payment_id_idx (payment_id asc),
  constraint fk_new_function_table_bill_id foreign key (bill_id) references bill (id) on delete no action on update cascade,
  constraint fk_new_function_table_payment_id foreign key (payment_id) references payment (id) on delete no action on update cascade
)engine=innodb

Personally now is ok, but contract, recruitment, etc table be included into new_function_table in the future.

If new_function_table use 10 tables(bill table, payment table, contract table, recruitment tables...), then new_function_table has 10 index.
So i have designed like below It ignore data integrity.

CREATE TABLE new_function_table`(
  id int unsigned not null auto_increment,
  category tinyint unsigned not null comment '1: bill, 2: payment...',
  reference_table_id int unsigned not null comment 'bill_id or payment_id...';
)engine=innodb

What is the good design for this case? If you have an any idea please help me! Thank you.


texts: How to check if the current time is between two times?

texts:
postgresql documentation gives an example timerange definition:

CREATE FUNCTION time_subtype_diff(x time, y time) RETURNS float8 AS
'SELECT EXTRACT(EPOCH FROM (x - y))' LANGUAGE sql STRICT IMMUTABLE;

CREATE TYPE timerange AS RANGE (
  subtype = time,
  subtype_diff = time_subtype_diff
);

SELECT '[11:10, 23:00]'::timerange;

The problem is that when time crosses the day barrier, the range can no longer be used, e.g.

SELECT '[22:00, 06:00]'::timerange;

will produce an error:

ERROR:  range lower bound must be less than or equal to range upper bound
LINE 1: SELECT '[22:00, 06:00]'::timerange;

I need to be able to check if the current time is between 22:00 today and 06:00 tomorrow.


texts: Hash join spills to disk even though there is plenty of memory granted for the query

texts:
We are running SQL Server 2014 Enterprise Sp2 with 1.5TB of memory and 64 cores. There is close to 17 GB memory granted to this single threaded query, max memory used is 2.2gb, even though the hash join has plenty of memory available it seems like it's not using it and spilling to disk. Any idea why? Thank you in advance.

Same question posted in SQLPerformance. The Query plan and images are posted there

I want to understand the Memory fractions. It shows the hash join has 52.48% of the memory grant (total 17GB+) which is close to 9GB. But the maximum memory used of the plan is a lot less which is 2.2+GB and Hash join is the only spill in this query. Is there something I am missing about the memory fractions, are they not accurate?


texts: RMAN Performance Problems following Platinum Patching on Exadata DB

texts:
I backup my dev,tst and prod databases (9 in all) to ZFS on two RAC servers on an Exadata 1/8 rack machine. Prior to quarterly patching, our backups were set up in 30 minute intervals and each completed in that amount of time.

After Platinum Services patched our DB Homes and Grid Home, RMAN jobs slowed to 4.5 hours each. I had to cancel incremental backups on my three test databases. Even with that, RMAN backups run all day, 24 hours a day. They still run into each other. I opened a support ticket but response has been slow and not very helpful.

I have tried a few things to no avail.
1) I spaced the backups so that they would not run into each other and cancelled the incremental test backups.
2) I changed the parallel processes in RMAN configuration to 8.
3) A colleague and I looked at settings that existed before the patching and changed a couple of things. I would have to ask her for specific changes.

I am curious if anyone has experienced something similar and what you may have done to get these backups back to normal execution times.


texts:
I have an XML column that contains data with similar structure:

<Root>
    <Elements>
        <Element Code="1" Value="aaa"></Element>
        <Element Code="2" Value="bbb"></Element>
        <Element Code="3" Value="ccc"></Element>
    </Elements>
</Root>

How can I modify the data using SQL Server to change each Value attribute into an element?

<Root>
    <Elements>
        <Element Code="1">
            <Value>aaa</Value>
        </Element>
        <Element Code="2">
            <Value>bbb</Value>
        </Element>
        <Element Code="3">
            <Value>ccc</Value>
        </Element>
    </Elements>
</Root>

Update:

My XML looks more like this:

<Root attr1="val1" attr2="val2">
    <Elements>
        <Element Code="1" Value="aaa" ExtraData="extra" />
        <Element Code="2" Value="bbb" ExtraData="extra" />
        <Element Code="3" Value="ccc" ExtraData="extra" />
        <Element Code="4" Value="" ExtraData="extra" />
        <Element Code="5" ExtraData="extra" />
    </Elements>
    <ExtraData>
       <!-- Some XML is here -->
    </ExtraData>
</Root>

I would like only to move Value attribute and preserve all other attributes and elements.


texts:
I have two tables:

Table users

columns | type   | notes
--------|--------|-------
id      | bigint | PK
...     | ...    | ...

Table events

columns    | type    | notes
-----------|---------|------
id         | bigint  | PK
user_id    | bigint  | FK(`users`.`id`)
event_name | varchar | ...
...        | ...     | ...

Example of Table events

id | user_id | event_name | ...
---|---------|------------|-----
 1 |       1 | A          | ...
 2 |       1 | B          | ...
 3 |       2 | F          | ...
 4 |       3 | C          | ...
 5 |       1 | A          | ...
 6 |       2 | A          | ...

I want get the latest event of a user (e.g: user_id is 1). So far, I found two solutions:

SELECT events.*
    FROM events
    WHERE events.id = (SELECT MAX(events.id) FROM events WHERE (events.user_id = 1));

and

SELECT events.*
    FROM events
    WHERE events.user_id = 1
    ORDER BY events.id DESC
    LIMIT 0, 1;

Which one is the better solution for this case? Or maybe, is there any better solution?

FYI, I use MySQL 5.6.19 and InnoDB is the db engine.


texts: How to split XML array into separate rows (while upholding consistency)

texts:
I am working on the database dump of this exact stack exchange section. While I am working on it I have encountered one issue that I am currently unable to solve.

In the XML File Posts.xml the contents look like this

There are of course multiple rows, but that's how one looks like. There's already a Tags.xml file given in the dump, which makes it even more obvious that the "Tags" attribute in that picture is in fact supposed to be its separate table (many to many).

So right now I am trying to figure out a way how to extract the tags. Here's what I tried to do:

CREATE TABLE #TestingIdea (
Id int PRIMARY KEY IDENTITY (1,1),
PostId int NULL,
Tag nvarchar (MAX) NULL
)
GO

↑ The table I created to test out my code. I have already filled it with the Tags and PostIds

SELECT  T1.PostId,
        S.SplitTag
FROM (
    SELECT  T.PostId,
            cast('<X>'+ REPLACE(T.Tag,'>','</X><X>') + '</X>' as XML) AS NewTag
    FROM #TestingIdea AS T
    ) AS T1
CROSS APPLY (
    SELECT tData.value('.','nvarchar(30)') SplitTag
    FROM T1.NewTag.nodes('X') AS T(tData)
    ) AS S
GO

Yet this code returns this error

XML parsing: line 1, character 37, illegal qualified name character

After googling this error (including here), whatever people had (like extra " marks or different CHAR sets) I didn't have. So I am kind of stuck. Maybe I missed something extremely obvious from previous answers I found T_T In any case I appreciate any help and advice on how to tackle this. It's the last table I have yet to normalize.

Small Sample Data From the XML File https://pastebin.com/AW0Z8Be2
For anyone interested in the program I use to view XML files (so it's much easier to read like in that picture above). It's called FOXE XML Reader (Free XML Editor - First Object)


texts: GRANT permission to access to respective creator

texts:
Does the following command:

GRANT SELECT
ON EVENT
TO STUDENT;

grant student access to whole EVENT table? If yes, how to only give STUDENT access to EVENT(s) record created by respective STUDENT?


texts: Best way to speed up a linked server query - 49 columns plus two calculated columns and 170 mill rows

texts:
I am trying to speed up at linked server query. The source table has 170 mill rows, and we need to retrieve all of them.
Trying to insert this query in a table on destination server takes over 2 hours. And then the index takes between 30 min and one hour. It is to be used in a query, joining col51 with another table, and col50 is used in the where clause.

Is there any way we can speed this up or use a different approach?

Here is the query:

SELECT * into destination.table FROM OPENQUERY(linkedserver,'
SELECT
col1
col2
col3
col4
col5
col6
col7
col8
col9
col10
col11
col12
col13
col14
col15
col16
col17
col18
col19
col20
col21
col22
col23
col24
col25
col26
col27
col28
col29
col30
col31
col32
col33
col34
col35
col36
col37
col38
col39
col40
col41
col42
col43
col44
col45
col46
col47
col48
col49
   ,BINARY_CHECKSUM(
col2
col3
col4
col5
col6
col7
col8
col9
col10
col11
col12
col13
col14
col15
col16
col17
col18
col19
col20
col21
col22
col23
col24
col25
col26
col27
col28
col29
col34
col35
col36
col37
col38
col39
col40
col41
col42
col43
col44
col45
col46
col47
col48
col49
) AS col50
   ,CASE WHEN col1= 1  THEN CONVERT(BIGINT,CONCAT(1*100,col8))
         WHEN col1= 2  THEN CONVERT(BIGINT,CONCAT(2*100,col13))
         WHEN col1= 3  THEN CONVERT(BIGINT,CONCAT(3*100,col9))
         WHEN col1= 4  THEN CONVERT(BIGINT,CONCAT(4*100,col10))
         WHEN col1= 10 THEN CONVERT(BIGINT,CONCAT(10*100,col11))
         ELSE CONVERT(BIGINT,CONCAT(col1*100,col3))
        END AS col51
  from source_server.table')

After inserting to table, this query is the other part - which also takes a long time. Suggestions to enhance it?

SELECT
           ISNULL(A.Col1,B.Col1) Col1
          ,ISNULL(A.Col2,B.Col2) Col2
          ,ISNULL(A.Col3,B.Col3) Col3
       ...etc
         ,ISNULL(A.Col50,B.Col50) Col50
         ,ISNULL(A.Col51,B.Col51) Col51
  FROM Table1 A -- linked server table
  FULL OUTER JOIN Table2 B ON A.Col51=B.Col51 --local table
  WHERE (B.Col50 IS NULL AND A.Col50 IS NOT NULL)--new rows
       OR  (A.Col50 IS NULL AND B.Col50 IS NOT NULL)--deletes
       OR  (B.Col50 IS NOT NULL AND A.Col50 IS NOT NULL AND B.Col50 <> A.Col50) --updates


texts: Postgresql 10 There is no unique or exclusion constraint matching the ON CONFLICT specification

texts:
I currently have a table which looks like this:

CREATE TABLE "PDPC".collection
(
    col_no bigint NOT NULL DEFAULT nextval('"PDPC".collection_col_no_seq'::regclass),
    q1 character varying(10000) COLLATE pg_catalog."default",
    q2 character varying(10000) COLLATE pg_catalog."default",
    q3 character varying(10000) COLLATE pg_catalog."default",
    q4 character varying(10000) COLLATE pg_catalog."default",
    dg_fkey bigint,
    CONSTRAINT collection_pkey PRIMARY KEY (col_no),
    CONSTRAINT collection_dg_fkey_fkey FOREIGN KEY (dg_fkey)
        REFERENCES "PDPC".datagroup (dg_no) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE "PDPC".collection
    OWNER to postgres;

I am trying to execute an UPSERT statement in PHP using postgresql, but i received

  Fatal error: Uncaught PDOException: SQLSTATE[42P10]: Invalid column reference: 7 ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification in C:\Apache24\htdocs\consideration.php:77 Stack trace: #0 C:\Apache24\htdocs\consideration.php(77): PDOStatement->execute() #1 {main} thrown in C:\Apache24\htdocs\consideration.php on line 77

My web page currently has a form that takes in a user input of answers to four questions, and these questions will go into "PDPC".collection table. I want to run INSERT or UPDATE according to the dm_fkey, which is the foreign key I have set for this table.

This is the UPSERT statement that I used.

INSERT INTO "PDPC".collection (q1, q2, q3, q4, dg_fkey)
      VALUES (:q1, :q2, :q3, :q4, :dg_no)
      ON CONFLICT(dg_fkey) DO UPDATE
      SET q1=:q1, q2=:q2, q3=:q3, q4=:q4


texts: If no plans to uninstall, can Log and Update Cache folders in Setup Bootstrap folder be removed?

texts:
I have several versions of SQL Server, used for testing, installed on my laptop (2012, 2014, 2016, and 2017). I noticed the other day that there was a folder containing prior versions of files across updates (SPs, CUs). Across all versions there is actually quite a bit of space being taken up:

(in C:\Program Files (x86)\Microsoft SQL Server\)

110\Setup Bootstrap\Log - 91.8 MB (818 files)
110\Setup Bootstrap\Update Cache - 608 MB (2,382 files)

(all folders below reside within: C:\Program Files\Microsoft SQL Server\ )

110\Setup Bootstrap\Log - 1.18 GB (3,715 files)
110\Setup Bootstrap\Update Cache - 9.58 GB (14,766 files)

120\Setup Bootstrap\Log - 569 MB (2,963 files)
120\Setup Bootstrap\Update Cache - 5.74 GB (12,797 files)

130\Setup Bootstrap\Log - 448 MB (2,808 files)
130\Setup Bootstrap\Update Cache - 3.84 GB (8,579 files)

140\Setup Bootstrap\Log - 84.3 MB (687 files)
140\Setup Bootstrap\Update Cache - 937 MB (1,571 Files)

Totals for those folders are (MB have been normalized to GB):

Folder          GB          Files
----------     -----        ------
Log             2.35        10,991
Update Cache   20.66        40,095

TOTAL          23.01        51,086

Initially I figured that it should be safe to remove the Log and Update Cache folders, but then I reckoned it best to first check to see if others have asked this and if so, what the answer(s) had been. I found this KB article:

Description of the Update Cache folder in SQL Server

which states:

  What happens if you remove this folder or delete its contents?

  If the Update Cache folder or some patches are removed from this folder, you can no longer uninstall an update to your SQL Server instance and then revert to an earlier update build. In that situation, Add/Remove Programs entries point to non-existing binaries, and therefore the uninstall process does not work. Therefore, Microsoft strongly encourages you to keep the folder and its contents intact.

Ok. I have no plans to ever downgrade or uninstall an SP or CU, so I should be ok, right? Well, there are other references, such as this one:

Is it safe to delete the "Update Cache" folder?

in which someone referenced the following KB article:

How to restore the missing Windows Installer cache files and resolve problems that occur during a SQL Server update

which states (emphasis added):

  When you install SQL Server, the Windows Installer stores critical files in the Windows Installer Cache (default is C:\Windows\Installer). These files are required for uninstalling and updating applications.

Now, this particular article seems specific to the C:\Windows\Installer folder, and I am not talking about deleting from that folder. Still, due to the mention of the possibility of not being able to apply updates, I wanted more info first.

Has anyone removed these folders before, or at least just their contents? If so, were there any negative effects? These folders only ever increase. I just applied the "Spectre / Meltdown" patches for all four versions and the totals increased by 3.65 GB and 6,900 files (the end result being the figures shown in the chart above).

To be clear, I am not looking for should I or shouldn't I remove one or both of these folders; I am looking for can I or can't I remove one or both (without breaking anything more than the ability to uninstall / downgrade, which I already accept as a consequence).


texts:
I need to enable archivelog. But sometimes database can't connect archivelog destination (NFS disk) and shutdowns. I can set two archivelog destinations and one of them is not NFS disk (local disk).

How can I configure, if database can't connect to NFS disk (dest1), to just write on local disk (dest2) and continue to work?

Thank you.


texts:
I am trying to set up autovacuum in my postgresql 9.5 database, but table stats shows me no evidence of that.

In postgresql.conf, the autovacuum is already enabled:

  autovacuum = on

In settings, plus, I have (select * from pg_settings where category like 'Autovacuum')

autovacuum  on
autovacuum_analyze_scale_factor 0.1
autovacuum_analyze_threshold    50
autovacuum_freeze_max_age   200000000
autovacuum_max_workers  3
autovacuum_multixact_freeze_max_age 400000000
autovacuum_naptime  60
autovacuum_vacuum_cost_delay    20
autovacuum_vacuum_cost_limit    -1
autovacuum_vacuum_scale_factor  0.2
autovacuum_vacuum_threshold 50

If I execute this query:

SELECT schemaname, relname,
last_vacuum       ,
last_autovacuum   ,
last_analyze      ,
last_autoanalyze
FROM pg_stat_all_tables
where schemaname = 'cdi'

I get this results:

relname last_vacuum         last_autovacuum     last_analyze    last_autoanalyze
aa      11/11/2017 05:56    04/11/2017 03:24    13/11/2017 23:25
bb      09/10/2017 15:01    04/11/2017 03:55    09/10/2017 15:01
cc      13/09/2017 13:23    04/11/2017 03:55    11/11/2017 04:12
dd      14/09/2017 10:27    04/11/2017 03:55    12/11/2017 17:09
ee      14/09/2017 10:32    04/11/2017 03:00
ff      11/10/2017 09:20    04/11/2017 03:48    13/11/2017 22:19
gg      04/11/2017 03:55
hh      04/11/2017 03:55
ii      04/11/2017 03:16    11/11/2017 06:51
ll      04/11/2017 03:16
mm      04/11/2017 03:55
nn      04/11/2017 03:55
oo      04/11/2017 03:55
pp      04/11/2017 03:00

It doesn't appear to me that there is an efficient autovacuum

Can someone tell me a method to make autovacuum happen?

this is my table:

Sequential scans        156
Sequential tuples read  5448762930
Index scans             802121339750
Index tuples fetched    800736733206
Tuples inserted         21907385
Tuples updated          6190434
Tuples deleted          190337
Tuples HOT updated      3958397
Live tuples             59641911
Dead tuples             703334
Heap blocks read        13685226418
Heap blocks hit         786803454925
Index blocks read       5104418623
Index blocks hit        3208275818736
Toast blocks read       0
Toast blocks hit        0
Toast index blocks read 0
Toast index blocks hit  0
Last vacuum             2017-10-11 09:20:22.358935+02
Last autovacuum
Last analyze            2017-11-04 03:48:49.753917+01
Last autoanalyze        2017-11-13 22:19:08.47618+01
Vacuum counter          1
Autovacuum counter      0
Analyze counter         10
Autoanalyze counter     3
Table size              6778 MB
Toast table size        8192 bytes
Indexes size            3969 MB


texts: Methods to identify/recognise 'bad' parameter sniffing in SQL Server

texts:
There's lots of superb information out there about the certain approaches that can be used to mitigate bad parameter sniffing, but there's not a lot of information about how to go about identifying a bad parameter sniffing problem.

Assuming you're not using SQL Server 2016 so can't leverage Query Store and you don't have users complaining about a problematic stored procedure that intermittently performs poorly, what methods can be used to proactively identify a bad parameter sniffing problem?

Utilising Extended Events, RML Utilities and simply querying/polling the DMVs all seem like viable approaches, does anyone have a methodology using either of these or know of any good resources that describe how to go about doing this?


texts: Create a hierarchy chain (reverse path) for each node in a tree

texts:
Given a typical closure table, how would I write a query to get a list of ids back to the root?  Is there a way to do that for all the [unique] ids?  I'm using Microsoft SQL Server.

Given:

parent  child   depth
1       1       0
2       2       0
3       3       0
1       2       1
2       3       1
1       3       2

I'd like:

id    path
1     1
2     1,2
3     1,2,3

Does that make sense?


texts:
This is with respect to Data Migration activity where the historical data from the client database is migrated to vendor Postgres Database. There will be millions of transactions that need to be migrated as the Big Bang approach.
In oracle database, I used to use the below template of code for migration -
create or replace PROCEDURE PRC_TEST
AS
DECLARE
    CURSOR CUR
    IS
    SELECT ID,NAME  FROM test;
TYPE test_typ IS TABLE OF CUR%ROWTYPE INDEX BY PLS_INTEGER;
test_tbl test_typ;

BEGIN
OPEN CUR;
LOOP
FETCH cur BULK COLLECT INTO test_tbl LIMIT 1000;
DBMS_OUTPUT.PUT_LINE(test_tbl.COUNT);
     FORALL I IN 1..test_tbl.COUNT --SAVE EXCEPTIONS
                INSERT
                INTO test1(ID,NAME)
                VALUES
                (
                test_tbl(I).id,
                test_tbl(I).name
                );

       FORALL I IN 1..test_tbl.COUNT
                  UPDATE test1
                  SET name     = name||test_tbl(I).NAME
                  WHERE id =test_tbl(I).id;

                DBMS_OUTPUT.PUT_LINE('AFTER'|| test_tbl.COUNT);
        EXIT WHEN CUR%NOTFOUND ;
     END LOOP;
     commit;
 close cur;
 EXCEPTION
      WHEN OTHERS THEN
        FOR I IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
        LOOP
        dbms_output.put_line('error'||sqlerrm);
        END LOOP;
END;
End PRC_TEST;

Is there a plpgsql equivalent code available for the same?
What approach to be used in Postgres while handling such migration activity ? please provide some best practices to be followed for better performance and handling/storing the error records in Postgres.
Thanks..!


texts: SQL-Server login-error of SQL-Server Agent Account

texts:
I have to manage a configured SQL-Server version 2005 and have setup Database Mail successfully - test mail works.

Also I've changed the agent service to a domain user with (currently) domain admin and db admin privileges.

Now I'm getting numerous login errors for this user

  [298] SQL-Server error:18456 error logging on user domain\sqlagent, [SQLSTATE 28000]

when running jobs with notification. Any idea how that can happen, when the agent service can be restarted without problem?

I've searched the web extensively but the only answers I can find are related to wrong passwords.


texts: Most efficient way to add a serial column to a huge table

texts:
What's the fastest way to add a BIGSERIAL column to a huge table (~3 Bil. rows, ~ 174Gb)?

EDIT:

I want the column to be incremented values for existing rows (NOT NULL).
I didn't set a fillfactor (which looks like a bad decision in retrospect).
I don't have a problem with disk space, just want it to be as fast as possible.


texts:
Assume SQL Server 2012 Standard edition.

My database has a table with 500 million rows. The table has about a dozen columns, none of which are very wide (some varchar(100)'s and some ints).

The clustered index (also primary key) is an identity column.

The application using this table has a screen where the user can search on most of the columns. One search field on the screen, which is required, has the option to search starts with or contains, resulting in either

WHERE ABC LIKE 'something%' -- starts with

or

WHERE ABC LIKE '%something%' -- contains

The actual queries are parameterized, unlike my examples here.

The other search fields do a starts with search just like the first example above, but they are not required. So, any combination of these fields can be searched on resulting in a dynamic where clause.

Given this information, what indexes should be created for optimal performance?

Bearing in mind that I'm new to query performance tuning, my naive strategy for this scenario alone is to create a non-clustered index for each column and using full text search for the column that has the contains search option. I'd love to hear why or why not that's a bad idea and what a better approach would be.

Update

It's known to me that full text searching is how to optimize the case of a "contains" search.

I'm much more interested in the other aspect of the problem: how to optimize for the other search fields which may or may not be present in any given query predicate. The details surrounding the field that can benefit from a full text index are included in my question only to help paint a more complete picture of my particular situation.


texts: Recover accidentally deleted rows from a table

texts:
My client has accidentally deleted all the records of a table from a test database. This test database is in SQL Server 2005 Express and we don't have a backup.

Is it possible to restore the deleted rows from the transaction log (.ldf) file? If yes, how?


texts:
I'm trying to write a Microsoft SQL Server stored procedure which takes an account ID, and returns an authentication ticket and whether the ticket is expired.

Something like this:

SELECT Authentication.ticket, Authentication.TicketExpires < getDate()
FROM Authentication WHERE Authentication.accountID = @id

The purpose is to return a row with two columns - the ticket, and a bit field (0 or 1) telling me whether the ticket is expired. This bit field isn't part of the table, the ticketExpires column is SMALLDATETIME.

Does anyone know the correct syntax for this?


texts: Finding Conflicting Combinations Using SQL

texts:
Say I have table called "Everyone_Loves_Relationships" which contains three fields: Boy_Name, Girl_Name, Relationship_Status. The list goes like this:

Boy_Name Girl_Name Relationship_Status
  Tony     Carol            No
  Simon    Julia            Yes
  Frank    Carol            No
  Frank    Lucy             No
  Frank    Lucy             No
  Tony     Lucy             No
  Tony     Samantha         Yes
  Frank    Carol            Yes

Each combination can show multiple times.

You'll notice that Frank and Carol are showing twice, once with a Yes and once with a No.

How do I design a SQL request that checks for combinations of Boy_Name&Girl_Name that have both a Yes and a No?

Thank you!


texts: Need to change existing design to accomodate more detailed tracking of time

texts: Currently I have a relationship between contact and time_log (contact is a verb here - could be a meeting/phonecall/email).  Time_log holds both a date and time, each contact is associated with one date and amount of time spent on that contact.  I also have a list of several contact_services_provided at each contact.  This was fine until I learnt that time spent on each of the specific services provided at a contact was also required.  I thought of linking time_log to contact_services_provided instead of directly to contact, but then the date is repeated for each service provided which is not correct (there should be only one date per contact), so I'm not sure how to proceed.  Also, I've added a sad image of my user interface and associated database design which might help to visualize the problem (you'll have to zoom in...)


texts: Will database replication increase the size of the replicated database?

texts: We have a SQL Server database that was assigned to be replicated. the original size of the database was 5 GB for the datafile, not the log file. Now after the replication the data file is 4 times bigger around 19 GB. Any pointers to see what could happened will be appreciated. I know SQL as developer and some little management but nothing about replication. The performance of the database has been damaged very bad so I am trying to see what need to be done to improve it.


texts: How to Backup a Log-shipped Mirror Database without breaking log-shipping

texts:
I’ve currently got a slow connection between LIVE and DR data-centres (new pipe is 6 months away).

Databases are log-shipped every 15 minutes.

My problem is that at the weekend the FULL BACKUPS take 3 days to copy over to DR.

I’m trying to find a way to take the Weekly Full Backups directly off the log-shipping Mirror.

I’m limited to switching the Mirror databases between “No Recovery” & “Standby” and I can’t take the Mirror DB off-line.

Has anyone figured out how to do this without breaking Log-shipping? I’m guessing it needs to have the database in Standby Mode. I’ve experimented with “WITH COPY-ONLY” but this needed the database to be in “recovery”.

Is there a third party tool? Please help.


texts: SSIS and Script Component - WebReference problem

texts:
Within my SSIS package-dataflow I have a script component and within this component whenever I add a Web reference and close editor, script component gives me

  "Validation error. Data Flow Task Script Component [1]: The binary code for the script is not found. Please open the script in the designer by clicking Edit Script button and make sure it builds successfully."

I can successfully build from the editor, but error resides there.

How can I solve this problem.

Ps: I am using SSDT for VS2012.


texts: How do I prevent changes to my PostgreSQL database?

texts:
My steps:

run the final pg_dumpall on a server prod-server-old
Shut down prod-server-old for ever.
copy the output to a different server (prod-server-new)
Restore the db there.
All traffic goes to prod-server-new now

How can I avoid modification to the database during pg_dumpall (between step1 and step2), so that modifications during this time cannot be lost?

In my case it is 100% OK to have a small down time.

There are several databases in the "cluster" (btw, I do not like the word "cluster". I guess most people think a "cluster" is a group of several computers, but in this case it means one Postgres server, which holds several databases).

My question was marked as possible duplicate to "Make Postgres database temporarily read-only (for performing volume snapshots)". I don't think it is a duplicate, since in my case is different since I don't ask for a temporary read-only state.


texts: Query profiling shows "Waiting for query cache lock" but query_cache_size is 0

texts:
We have a statement based replication server that has been experiencing slowdowns and during the event SHOW FULL PROCESSLIST showed the replication query stuck on "Waiting for query cache lock" which was surprising because the server query_cache_size is set to 0.
Profiling the offending query does show this step in every update to the table.

Is this typical to see in the profile for an update even if the query_cache_size is 0? Is this really just the check for query caching and not the query waiting to obtain a true lock?


texts: Any way to track who has been executing what queries? who has been on my lawn?

texts:
I have a database in which most of tables are readable for users in one particular security group.

I know most of the users are from my team, but lately I found that it's been used by more and more people, and most of execution occurs from Excel and/or Access.

Out of curiosity, is there any way for me to track who has been tapping into the SQL server and what query they have been running?


texts: What is the fastest way to (session) lock table?

texts:
I have some triggers to log changes on a table to Log table.

On insert and delete I add row to the Log table and for update I add two rows.

The log table contain identity column, and I want the 2 update rows to be sequential ( by the the id = identity)

for example:
assuming the following table:

Create table t1 ([name] nvarchar(40) primary key, [value] nvarchar(max))

the log table is:

Create table t1_log
([log_id] identity(1,1),[log_ts] DateTime default GETDATE(),
  [log_action] varchar(20), log_session_id int default @@SPID,
  [name] nvarchar(40), value nvarchar(max))

And I have 3 triggers to update the log:

Create trigger t1_ins on t1 After Insert as
begin
    Insert into t1_log([log_action],[name],[value]) select 'insert', [name], [value] from inserted
end
Go
create trigger t1_del on t1 After delete as
begin
    Insert into t1_log([log_action],[name],[value]) select 'delete', [name], [value] from deleted
end
Go
create trigger t1_upd on t1 After update as
begin
    Insert into t1_log([log_action],[name],[value])
       select [log_action], [name], [value] from (
          (select ROW_NUMBER() OVER (ORDER BY (SELECT 0)) As ROW_ID, 'update from' as [log_action], [name], [value] from deleted)
          UNION
          (select ROW_NUMBER() OVER (ORDER BY (SELECT 0)) As ROW_ID, 'update to' as [log_action], [name], [value] from inserted)
        ) as temp_tbl
     Order By [temp_tbl].ROW_ID, [temp_tbl].[log_action]
end
Go

In this solution, when I do update from several sessions, there is a chance to several updates in the same time and it break the update sequence.
I can see 2 'update from' rows and then two 'update to' rows and I want to prevent it.

The only solution I can think of it to lock the t1_log table in the update trigger using :

Select * from t1_log with (TABLOCKX)

But what if the t1_log have many rows? I guess select * will be slow, and each update will return the selected *.

So I'm using the following:

create trigger t1_upd on t1 After update as
begin
    declare @tt
    Begin transaction

    select @tt=1 from t1_log with (TABLOCKX)

    Insert into t1_log([log_action],[name],[value])
       select [log_action], [name], [value] from (
          (select ROW_NUMBER() OVER (ORDER BY (SELECT 0)) As ROW_ID, 'update from' as [log_action], [name], [value] from deleted)
          UNION
          (select ROW_NUMBER() OVER (ORDER BY (SELECT 0)) As ROW_ID, 'update to' as [log_action], [name], [value] from inserted)
        ) as temp_tbl
     Order By [temp_tbl].ROW_ID, [temp_tbl].[log_action]

     Commit trasaction
end

this works better, but I still wonder if there is a fastest way to lock a table?


texts:
I have a existing table in which i am adding a column as NgoId whose datatype is int. I want to add as NOT NULL to the column .

When I'm adding this I am getting the following error:

  Unable to modify table.    Cannot insert the value NULL into column
  'NgoId', table 'CSR.dbo.Tmp_tbl_post_category'; column does not allow
  nulls. INSERT fails. The statement has been terminated.

Please help how to add the column with NOT NULL.


texts: Index rebuild not working with SQL Server 2016

texts:
I have some index with very high fragmentation, 85-100% and rebuilding does not help

The only clue I have is that it may not work if the data file is fragmented on disk and that could very well be the case. However, data is stored on a high performance SSD SAN. Disk fragmentation doesn't really mean anything.

I have the same problem with multiple index in the same database but the one I am most concerned with has 847653 pages and fillfactor 75%. Most other index have fill factor 90% and many of them has the same issue.

This is the script used

SELECT
    CASE WHEN MAX(ps.avg_fragmentation_in_percent) > 30 THEN
        'ALTER INDEX [' + i.name + '] ON [' + sc.NAME + '].[' + so.NAME + '] REBUILD With (maxdop = 1);'
    ELSE
        'ALTER INDEX [' + i.name + '] ON [' + sc.NAME + '].[' + so.NAME + '] REORGANIZE;'
    END AS [query], max( ps.avg_fragmentation_in_percent)
FROM
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED') AS ps
    INNER JOIN sys.indexes i
        ON ps.OBJECT_ID = i.OBJECT_ID
            AND ps.index_id = i.index_id
            AND ps.database_id = DB_ID()
            AND i.name is not NULL
            AND ps.avg_fragmentation_in_percent > 5 -- min % to return
            AND ps.page_count > 1000 -- otherwise table is to smal.
    INNER JOIN sys.objects so
        ON i.OBJECT_ID = so.OBJECT_ID
    INNER JOIN sys.schemas sc
        ON sc.SCHEMA_ID = so.SCHEMA_ID
GROUP BY sc.NAME, so.NAME, ps.index_id, i.name
ORDER BY sc.NAME, so.NAME, ps.index_id DESC;

Since the question is closed being too broad, I have edited it back to the original question.


texts:
I have a database that has multiple tables without any clustered index. All that I have found are small (< 500 rows). Specifically, PRIMARY KEY NONCLUSTERED with no other indexes at all.

At a very high level I understand the performance limitation of not having a clustered index. Perhaps more so I understand it isn't best practice, but before I aggressively start converting non-clustered primary keys to clustered ones, are there any caveats I should be aware of when converting these indexes?

The only one I can think of would be the time it would take to update an index on a large table, but again these are relatively small. Perhaps files & partitions, but I only have a single partition.

For reference, this is a 2008-compatible SQL Server database running on SQL Server 2016. This is in our primary OLTP system (these table are not involved in warehousing & ETL).


texts: Why there is no much change in the incremental backup size of level 0 and level 1

texts:
I'm implementing incremental backup in my database. As per my knowledge after level 0 backup, level 1 backup will take only the backups of changes that are done. But in my db after level 0 full backup, level 1 backup is also taking the almost same backup size of level 0.

On Friday, level 0 backup. Backup Size: 49.8 GB

BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG;

On Saturday, level 1 backup. Backup Size: 45.2 GB

BACKUP INCREMENTAL LEVEL 1 DATABASE PLUS ARCHIVELOG;

Is there any mistake which I have done here in the RMAN command. Please help.....


texts: Mysql Pivot rows into dynamic columns with unknown number of columns

texts:
I have an output of a query as follows:

bid  | code | amount        |
-----------------------------
2915 | BF   |   -10700.00   |
2915 | YQ   |   -300.00     |
2915 | YR   |   0.00        |
2915 | YM   |   0.00        |
2915 | WO   |   -153.00     |
2915 | IN   |   -329.00     |
2915 | K3   |   0.00        |
2915 | CUTE |   -50.00      |
-----------------------------

I need to convert the values in code column to individual columns with column name as corresponding code and amount shall be the value.

bid  | BF       | YQ     | ... | CUTE  |
----------------------------------------
2915 | -10700.00| -300.00| ... | -50.00|

The issue is that the result of my initial query has dynamic codes and hence need to generate columns accordingly.

Already tried the following logic but couldn't achieve the goal:

dynamic pivot table using mysql, mysql rows to columns, mysql transpose

All the above links use some expression or the other to generate the columns.

My original resulting rows are result of simple select on multiple joined tables, and has no calculation or expression scope.

Kindly suggest some pointer or hint on how to achieve this.

Many thanks in advance.


texts: Revision Tracking & Source Control for Azure SQL Data Warehouse

texts:
What is a good approaching for tracking incremental changes to database tables, stored procedures, etc for Azure SQL Data Warehouse?

I am in the process of moving a large database over to Azure SQL Data Warehouse. The prior approach for change tracking was using a 'Database Project' in Visual Studio 2015. This allows easy source control integration with TFS or Git or whatever. When you want to publish, you just target the destination database and it generates a change script.

This functionality does not at all work for Azure SQL Data Warehouse. Visual Studio (and the latest SSDT) simply can't target SQL DW. This means the process of publishing is extremely tedious, entirely manual and extremely error prone.

Is there another comparable approach you are using for this type of project?


texts: Can I disable the spyware in SQL Server?

texts:
There are two titles for the spyware in SQL Server,

Customer Experience Improvement Program (CEIP)
Telemetry

I'm wondering how I disable them. You can see them referenced here,

My SQL Server says in their new license_Eval_Linux.txt

  We collect data about how you interact with this software. This includes data about the performance of the services, any problems you experience with them, and the features you use. This also includes data about your device and the network you use to connect to our services, including IP address, device identifiers, regional and language settings. It includes information about the operating systems and other software installed on your device, including product keys. By using this software, you consent to Microsoft’s collection of usage and performance data related to your use of the software.

The text in the new license_Std_Linux.txt reads,

  IMPORTANT NOTICE:  AUTOMATIC UPDATES TO PREVIOUS VERSIONS OF SQL SERVER. If this software is installed on servers or devices running any supported editions of SQL Server prior to SQL Server 2017 (or components of any of them) this software will automatically update and replace certain files or features within those editions with files from this software.  This feature cannot be switched off.  Removal of these files may cause errors in the software and the original files may not be recoverable.  By installing this software on a server or device that is running such editions you consent to these updates in all such editions and copies of SQL Server (including components of any of them) running on that server or device.

  The software enables Microsoft to collect telemetry by default. You may turn off telemetry at the Server and client levels by following the instructions at http://go.microsoft.com/fwlink/?LinkID=733886. There are separate controls to turn off telemetry at the Server level and the client level.  If you turn off telemetry at the Server level only, Microsoft will collect telemetry through the client level unless you also turn off telemetry at the client level.

Older versions of v.Next, had the following text (/usr/share/doc/mssql-server/LICENSE.TXT.gz)

  Customer Experience Improvement Program (CEIP) This software
  uses CEIP. CEIP automatically sends Microsoft information about your hardware and how you use this software. We do not use this information to identify or contact you. To learn more about CEIP, see http://go.microsoft.com/fwlink/?LinkID=733654&clcid=0x409.
  That link seems to be dead. I got into looking through the docs on what exactly is being sent, and I can't find any information about it or how to disable it.


texts: How to insert numerous data into temporary table?

texts:
I'm building a program which generates a T-SQL query in following form:

DECLARE @In TABLE (Col CHAR(20))
INSERT INTO @In VALUES value1, value2... value1000
GO
INSERT INTO @In VALUES value1001, value1002...

but the second INSERT statement throws error:

  Msg 1087, Level 15, State 2, Line 1
  Must declare the table variable "@In".

What am I doing wrong?


texts: Double entry bookkeeping database design

texts:
I'm creating accounting software. I need to enforce double entry bookkeeping. I have the classical problem of one row per transaction versus two rows.

Let's take an example and see how it would be implemented in both scenarios.

Consider account Cash and account Rent. When I pay my monthly rent, I transfer $100 from my Cash account to me Rent account.

One row per Transaction

In a one row system, such transaction would be stored as:

transactions

 tx_id | posting_date
 1     | 23/05/2015

transaction_records

 id | tx_id | credit_account | debit_account | amount
 1  | 1     | Cash           | Rent          | 100.00

Two rows per Transaction

In a two row system, I'd have to mirror the same transaction record to create an opposite record that once I sum up both, I'd get zero balance.

transactions

 tx_id | posting_date
 1     | 23/05/2015

transaction_records

id  | tx_id | type   | account | amount
1   | 1     | credit | Cash    | 100.00
2   | 1     | debit  | Rent    | 100.00

The problem

First of all I'd like to note: the reason I have both transactions and transaction_records tables (instead of one table) is to be able to handle split transactions (a case where I transfer $100 from Cash account to two or more different accounts).

At first I tried to implement this with one row per transaction, but its a pain to calculate the account balance, and to actually retrieve the data.

I'm leaning towards the second scenario; however, it also has some issues:

How do I update a single record? Assuming I've made a mistake and instead of recording $100 for my rent, I've recorded $10. I now have 2 transaction_records - one for credit and one for debit, both with amount $10.
Now I do my reconciliation and I want to fix this typo. How would I fix this in the database? I don't know the connection between records, and in case of a split, one transaction can have more than 2 records. The only solution I came up with is to add some ref_id for each records pair that will uniquely identify those records as being the "opposite sides of each other" inside a context of a specific tx_id.

Which approach is better/simpler?

To simplify my question: I want to represent a movement of funds from account A to account B. The two scenarios I gave are both valid designs to store such transaction. As I also pointed out they both have cons & pros (first one: easier to save, harder to retrieve; second one the opposite).

They might have other pros/cons that I do not spot right now, hence I ask an opinion from more experienced people.


texts: Update stats as default or with a sample value

texts:
I have been reading many blogs on this and cannot come to a conclusion on update stats for VLDB;s

On SQL server 2012Sp4 and 2014Sp2 we have databases over 15 TB with 4-5 tables covering most of the sizes on avg 2-3 TB's

Now performing index maintenance is causing a lot of problems during the weekend window.

We are not doing ant index rebuilds/re-organize as of now
Update stats is done for those DB's every weekend once

For tables with over 10 billions of rows, we had update stats on indexes with sample of 20% and 30%. As per my knowledge, stats would not be going parallel operation unless its full scan or specified MAXDOP option which is available on higher versions of what i am currently on.

So the problem is update stats with that sample rate runs and almost increasing its run times on avg. 9-10 hours and that causes issues with other processes running due to window exceeding as assigned to DBA work for maintenance .

However if we just run update stats on index without any sample they will just complete in 10-20 mins. I see the rows sampled would be less here and can have some impact on performance.

So what are my best options as of now if i run without sample? Would that worse the performance of servers? Is there a better way of achieving this?

All databases have auto update stats ON [SYNC]

Thanks


texts: How to repair database consistency errors

texts:
I have a Microsoft Dynamics AX SQL Server database. I just run DBCC CHECKDB in order to check the integrity. After a few minutes I got at the end of the results the following:

  CHECKDB found 0 allocation errors and 4 consistency errors in database
  'AXPROD'. repair_rebuild is the minimum repair level for the errors
  found by DBCC CHECKDB (AXPROD). DBCC execution completed. If DBCC
  printed error messages, contact your system administrator.

Since I got 4 consistency errors I would like to know if there is a way to fix this, or a way to get more detailed information about these errors.


texts:
I know the title is a little weird but what I actually want to do is simple. In my Postgres db a user creates a profile and uploads an image.

user_id: 1, photo_id: 1

Now a second user makes a profile and uploads two images.

user_id: 2, photo_id: 1, photo_id: 2

In my photos table, how can a create a unique identifier out of the combination of user_id and photo_id? Neither are unique in the table but their combination should be.


texts: Meta Query - Select the SELECT expression as a string field

texts:
Sorry, not sure i am in the right place for this question, but anyway:
I was wondering if there is way in PostgreSQL or any other dbms to select the select statement itself? Smthn like this:
SELECT *, $QUERY_EXPRESSION FROM MYTABLE
?


texts: How do I get the GRANT statements for a specified schema?

texts:
Is there a way to generate the GRANT statements that have been run on a schema in PostgreSQL, for example if myUser has been granted usage on mySchema the script would generate

GRANT USAGE ON SCHEMA mySchema TO myUser;


texts: How to change page size in an existing sybase database

texts: I have an existing database with 1 kb page size i want to increase that to 16kb and increase cache to take advantage of my memory for best performance. sybase documentations said you can't change page size of database but you can create new empty database with preferred page size, so how can i move my data to from old db to new database with big page size ?


texts:
SQL server 2008 R2.

A user is part of an AD group. That AD group has a SQL account in this instance. That group has the following access to a Database:

db_datareader
db_datawriter
db_ddladmin
db_executor
db_owner
MSReplPal_7_1
public

When that user runs "Delete" on this database, it throws an error:

  The DELETE permission was denied on the object '', database '', schema 'dbo'.

User belongs to some other groups but none of those groups has permission to this DB in question.

This group does not have explicitly deny on the table in question.

What could the reason the user cannot delete on tables?

Also, I don't understand how this group has given this many access on this database. Doesn't having "db_owner" imply the group has maximum access to the database and other permission don't need to be added?? such as db_datareader, db_datawriter, etc.

Thanks for your time.


texts: Recover Postgres Streaming Replication Slave from Archived Wal Logs

texts:
I have set up a Postgres Hot Standby server by Streaming Replication. But My Standby server is asking for an old wal archive log which is currently not in Master's pg_xlog directory. But the file exists in the wal archive backup directory.

How can I configure Standby to read this file from backup directory? Or any way to manually copy this file to Standby Server ?

Any help will be appreciated.


texts: How to view the user limits like max_queries or max_updates in MySQL?

texts:
How to show that I've imposed limitations for queries per hour ?

Can't find anything online or notes. Would appreciate anybody's help on this.


texts:
Yesterday we have seen some production issue in my project. While many users accessing the reports in SQL Server, it got timed out and CPU went up to 100%. When we checked in the backend, some 60 sessions were open. We just decided to drop and create the stored procedure. We have dropped and recreate the stored procedure and CPU became 40% and the issue has been resolved.

Can you please let me know what might be the reason for the same. This has happened for the different stored procedure today but we have dropped and recreated the stored procedure immediately to resolve the issue.


texts: Creating external tables from SQL Server using JDBC Drivers

texts: I am trying to query Hive tables AND Kylin tables from SQL Server 2019. I was doing some research and found that MSSQL provide PolyBase as a form of interface to Hadoop storage. However, the examples mostly include creating tables and storing file formats into HDFS. All I need is to create an external table from MSSQL using JDBC drivers. Is this possible? I would like to avoid using ODBC drivers.


texts: Emulate a TSQL sequence via a stored procedure

texts:
I have a requirement to create a stored procedure which emulates a TSQL sequence.  That is it always gives an increasing distinct integer value on every call.  In addition, if an integer is passed in it should return that value if there has never been a result greater or the next highest integer available.  It goes without saying there can be multiple clients calling this SP at the same time.

Given a table MetaInfo with columns MetaKey varchar(max) and MeatValueLong bigInt.  It is expected the row with the MetaKey of 'Internal-ID-Last' will contain the last highest value assigned.  I created the following stored procedure:

CREATE PROCEDURE [dbo].[uspGetNextID]
(
  @inID bigInt
)
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRANSACTION

    UPDATE MetaInfo WITH (ROWLOCK)
      SET MetaValueLong = CASE
                            WHEN ISNULL(MetaValueLong,0) > @inID THEN MetaValueLong+1
                            ELSE @inID+1
                          END
    WHERE MetaKey = 'Internal-ID-Last'

    SELECT MetaValueLong
    FROM MetaInfo
    WHERE MetaKey = 'Internal-ID-Last'

    COMMIT TRANSACTION

END

My question is simply, does this stored procedure work as expected (all callers will be assigned a unique result)?


texts: Oracle SQL Developer: 'All Source Lines' in 'Find Database Object'

texts:
What's in 'Find Database Object' the difference between

Search for %pattern% AND

All Source Lines

In which area's does Search for %pattern% search for, if not in All Source Lines ...?


texts: Any SQL where clause performance similar to where 1=0

texts:
(i'm using SQL Server 2008R2 or later)

For centralize manage and consistent, there is a view definition as follow:

CREATE VIEW view_all_situation
(
  select null as src, tf.col1, sum(col2) as fee, sum(isnull(v1.col1, 0)) as fee1 ...
  from table_fee tf
  inner join parameter_table pt on tf.id = pt.id
  left join view1 v1 on ...
  left join view2 v2 on ...
  where pt.col1 = 0
  group by tf.col1

  UNION ALL

  select tfd.src, tfd.col1, sum(col2) as fee, sum(isnull(v1.col1, 0)) as fee1 ...
  from table_fee_detail tfd
  inner join parameter_table pt on tfd.id = pt.id
  left join view1 v1 on ...
  left join view2 v2 on ...
  where pt.col1 = 1
  group by tfd.col1
)
(Note: parameter_table only have 1 row)

so if the value in parameter_table.col1 = 1 then return table_fee_detail else return table_fee. (Note: parameter_table only have 1 row)

As the first part require 10 secs and second part require another 10 secs, finally, it costs 20 secs to return. If there any faster way to let SQL Server execute either one part of the UNION ALL base on the parameter_table value so that it returns in 10 secs?

As dynamic SQL is not applicable in my situation, i can't build it to get the result.

I've tried to use

where EXISTS (select 1 from parameter_table where col1 = 0) and
where EXISTS (select 1 from parameter_table where col1 = 1)

as the where clause, it cannot make the SQL Server to return in 10 secs. It still use 20 secs.

Is it possible to have SQL with result like

where 1=0

so that SQL Server return the result in 10 secs?

For example,
if parameter_table.col1 = 0, i get result from table_fee
Src     col1   fee   fee1
--------------------------------------------
null    USD    100   150
null    EUR    200   150
null    AUD    300   200

if parameter_table.col1 = 1, I get result from table_fee_detail
Src     col1   fee   fee1
--------------------------------------------
USA     USD    100   150
GER     EUR    200   150
AU      AUD    300   200


texts: What "fromRouter" option means on MongoDB aggregations?

texts:
Recently i notice that a parameter fromRouter" : true exists on mongoDB aggregations. I search the MongoDB documentation and google for it, but i didn't found anything about it. Can anyone explain what this parameter is, or share any useful resource for it?

"op" : "command",
"ns" : "database_name.$cmd",
"command" : {
    "aggregate" : "collection",
    "pipeline" : [...erase them to make the post smaller...],
    "fromRouter" : true,
    "$queryOptions" : {
        "$readPreference" : {
            "mode" : "primary"
        }
    }
},
"ntoreturn" : 1,
"keyUpdates" : 0,
"numYield" : 440,
"lockStats" : {
    "timeLockedMicros" : {
        "r" : NumberLong(3106252),
        "w" : NumberLong(0)
    },
    "timeAcquiringMicros" : {
        "r" : NumberLong(5302577),
        "w" : NumberLong(6145)
    }
},


texts:
I need to preform a rolling upgrade on my MongoDB.
I've been all over the Mongo documentation and I can't find a solution to my problem (if there is one)
Here's a simple sketch of the Architecture:

I have two Sites (A and B) (telecommunications site, not website).
When I'm executing a rolling upgrade in one site I need to upgrade everything associated to it.

I place my site B in contingency (there are no requests from outside arriving to that Site) and I start upgrading everything, but when I get to the config server I find a problem.

Config servers are always in sync, and this can't happen because all communication with the outside must be terminated.

Is there a way to isolate the config server on that site? (I know they're replicated)
I hope you guys understand my question, is basically how to preform a config server rolling upgrade in multi-host environment without completely stopping all 3 config servers (stopping both sites is not a valid solution)

Will upgrading my config server will have any impact in my service? (in the other site)

Can I shut down 1 config server, upgrade and restart it?

thanks


texts: SQL Server 2008 r2: What events/queries hit tempdb

texts:
We had a slowdown on a db server this morning. Checking the logs, the only thing that happened during the 2-minute slowdown worth noting was an object being created and deleted in tempdb over the span of about 3ms. I know that temporary tables and table variables would do this, but as far as I can tell none were used. Are there any other actions (e.g. nested selects) which might create a temporary table?

P.S. We've already figured out that it was most likely a log expansion that caused the slowdown, and the hit to tempdb was unrelated. I just wanted to ask for academic purposes and because I just cannot figure out what caused tempdb to be used.


texts:
What is wrong in this procedure?

I have a table family:

Name          Null?    Type
------------- -------- ------------
NAME          NOT NULL VARCHAR2(25)
NICK_NAME              VARCHAR2(15)
GENDER        NOT NULL CHAR(1)
AGE           NOT NULL NUMBER(3)
MARITL_STATUS NOT NULL VARCHAR2(15)
QUALIFICTN    NOT NULL VARCHAR2(20)
NO_OF_CHLDRN           NUMBER(2)

I have created a procedure to insert values in this table. The procedure is compiled and created successfully:

create or replace procedure "insert_family"
(NAME varchar2 ,
NICK_NAME varchar2 ,
GENDER VARCHAR2,
AGE number,
MARITL_STAUS varchar2,
QUALIFICTN varchar2,
NO_OF_CHLDRN number)
is
begin
insert into family values(NAME,NICK_NAME,GENDER,AGE,MARITL_STAUS,QUALIFICTN,NO_OF_CHLDRN);
end;

But when I try to call this procedure through:

BEGIN
insert_family('AYUSH','SHUKLA','MALE',12,'UNMARRIED','SCHOOL',0);
END;
/

It gives me this error:

  BEGIN
  insert_family('AYUSH','SHUKLA','MALE',12,'UNMARRIED','SCHOOL',0);
  END;
  Error report -
  ORA-06550: line 2, column 1:
  PLS-00201: identifier 'INSERT_FAMILY' must be declared
  ORA-06550: line 2, column 1:
  PL/SQL: Statement ignored
  06550. 00000 -  "line %s, column %s:\n%s"
  *Cause:    Usually a PL/SQL compilation error.
  *Action:

The problem seems to be in procedure as there is no parameter shown to
have values in debug mode:


texts: Azure: Many Failed Login Attempts Logged

texts:
I run a SQL Server on an Azure Virtual Machine. I have noticed in the Event Logs of the server lots of failed SQL Login attempts using a ton of different usernames (none of which exist):

  Login failed for user 'kisadminnew1'. Reason: Could not find a login
  matching the name provided. [CLIENT: 124.117.233.94]

  Login failed for user 'djapple'. Reason: Could not find a login
  matching the name provided. [CLIENT: 124.117.233.94]

  Login failed for user 'China'. Reason: Could not find a login matching
  the name provided. [CLIENT: 124.117.233.94]

  Login failed for user 'vice'. Reason: Could not find a login matching
  the name provided. [CLIENT: 124.117.233.94]

In all there's probably a few hundred random attempts all at the same time overnight.

Should I be concerned about this? Does it look like somebody is trying to get access to my SQL Server through brute force and is this common?


texts: Using MySQL EXPLAIN to analyze the performance of a function

texts: You can precede a SELECT statement in MySQL with EXPLAIN, but how would you explain a function? I have a function that does a lot of work and I would like to analyze the performance of the function as a whole.


texts: How to include values in a MySQL sum based on a conditional statement?

texts:
I have the following MySQL query which takes in the sum of columns 1-4, from the start of the current date, up to the current time.  My table has 5 columns, and I am trying to perform a conditional query based on a value in column_5.

I need to modify this query according to the following:

If column_5 > 0, then I don't want to include the values from column_1 in my sum, Total.
But if column_5=0, then I do want to include the values from column_1 in my sum Total.

At the moment, the values from column_1 are included in my sum, regardless of whether column_5 is greater than or equal to 0.

SELECT (column_1+column_2+column_3+column_4) at 'Total'

FROM myDB.table_1

WHERE TS >= CURDATE() AND TS < CURDATE() + INTERVAL 1 DAY ;


texts: Sql Server 2008 R2 DBCC SHRINKFILE Severe Error

texts:
I am trying to run the following command in Sql Server 2008 R2:

DBCC SHRINKFILE('filename')

When I try to run this command, I get the following error:

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

Can anyone tell me why I am getting this error and how to resolve this issue?

Thanks!


texts: Extract query fingerprints and tables from large log files using pt-query-digest

texts:
I tried to parse a large log file using pt-query-digest but it gives "Out of memory!" when I try to read the file. I have 3GB RAM, 40GB empty space on HDD, the file is 20GB. I did not found anything in the documentation about memory.

I reduce the command at:

pt-query-digest --group-by fingerprint --print --no-report /path/to/largeLogFile.log
pt-query-digest --table-access /path/to/largeLogFile.log

I have 2 questions:

How can I extract the query fingerprints from a large log file generated by general_log ?
How can I extract all the tables that were used ?

Edit:
Tried @DTest --sample option (with 2 and 1), but the result is the same. Just before it reaches 50% memory in htop it dies every time (see screnshoot):

I did an strace just before it dies I get:

read(3, "phone from teams as t ri"..., 4096) = 4096
mremap(0x5b27a000, 1543499776, 1543503872, MREMAP_MAYMOVE) = -1 ENOMEM (Cannot allocate memory)
mmap2(NULL, 1543503872, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = -1 ENOMEM (Cannot allocate memory)
brk(0x64c2d000)                         = 0x8c2e000
mmap2(NULL, 1543634944, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = -1 ENOMEM (Cannot allocate memory)
mmap2(NULL, 2097152, PROT_NONE, MAP_PRIVATE|MAP_ANONYMOUS|MAP_NORESERVE, -1, 0) = 0x5b07a000
munmap(0x5b07a000, 548864)              = 0
munmap(0x5b200000, 499712)              = 0
mprotect(0x5b100000, 135168, PROT_READ|PROT_WRITE) = 0
mmap2(NULL, 1543503872, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = -1 ENOMEM (Cannot allocate memory)
write(2, "Out of memory!\n", 15Out of memory!
)        = 15
munmap(0x5b27a000, 1543499776)          = 0
... [output removed] ...

The only option I can think so far is splitting the file "bite size" pieces (let's say 20 files of 1GB each), digest them and merge the result.


texts:
I'm looking to calculate the difference between two record sets in MySQL.

Say I have the following table:

colname type attrbutes
id      uInt PK
date    date PK
count   uInt

With this data:

id   date      count
1    2000-1-1  15
1    2000-2-1  20
2    2000-1-1  20
2    2000-2-1  30

I'm trying to get the following result (if the user selects dates 2000-1-1 and 2000-2-1):

id  count
1   5
2   10

What is a good way to do this?


texts: Query Cost when filtering with varchar vs nvarchar

texts:
Recently the application I'm working on encountered performance issue on a particular table. When I check the queries executing on this table, I noticed a warning about CONVERT_IMPLICIT on the predicate of the query. Apparently the data type of the column is varchar, but the query is passing in nvarchar, hence the warning.
Out of curiosity, I try to see how much difference would passing the correct data type improves query performance by checking the execution plan. I got the following result:

To my surprise, when I'm using the matching data type varchar, the query cost is double compare to using nvarchar, even though it seems like the second query is not using the most efficient execution plan.
So is the relative query cost to be trusted, or is there something I missed?
Here's the execution plan.
If even the actual execution plan is not reliable, what is a better way to compare the execution cost of 2 queries?


texts:
in an Oracle 11.2 Database, Enterprise Edition, we are running calculations divided into 3 different "modules". These modules can be started on their own, but not parallel, so in between users are able to confirm the calculations and proceed with the next step.

Now I want to find a proper way to "save" and "restore" the tables to allow "a rollback" to the definied start before the module.

I have read the Oracle-Documentation about (garantueed) restore points but i think its way too much. Within its time of usage the process is more or less exclusive, but not garantueed. So i cannot rollback the complete database.

I found some information about the OracleWorkspaceManager but I am not sure if this tool would be something I could use.

Finally, I simply want to save and maybe restore 6 or 7 different tables. Is there an "easy" way to do this? Alternatively I am thinking of materialized views to store the information "before" each step. But that would create dependencies into the workflow I cannot image right now.

Thank you for your help.