texts:
Let there are two tables: "parent" and "child" related by 1:n relation (to every "parent" row there correspond any number of "child" rows).

Suppose we need to fetch a subset of parent table with all corresponding childs.

I can devise three methods to do it:

Prepare two SELECT handles: one for parent and one for child. Do M+1 SELECT queries (where M is the number of relevant "parent" rows), with one query for the parent and M queries for the child with parent row ID being a bind parameter. This variant requires too much queries and thus may be inefficient.
Make one SELECT query with JOIN. This would fetch parent data repeatedly, even though it is needed just once.
Make first SELECT query for the parent. Then make SELECT query for the child with ParentID IN (...) WHERE condition based on the parent IDs fetched in the first SELECT. If there are too many parent rows, this second query may overflow query buffer size.

Please describe advantages and disadvantages of every three methods. Which method to use in practice? (The primary criterion is performance.) I'd stick with the third method (almost always), but want to hear about its possible disadvantages.

Note that we use MySQL.


texts:
I am creating a database that references images. Keywords and captions will be part of the metadata. These captions and keywords are not necessarily added to the photos with the intent of facilitating quality searches.

I would like to effectively convert the information in the captions into keywords. Obviously some of the words in the captions will not be useful (e.g. "the","he","she","of",etc.) and I would exclude them, but other words will be very useful, especially if they appear in combination with other words.

I'm trying to figure out the best way to associate these keywords and words from captions with the images. Would it be best to create a table of images (filenames, path to file, size, etc.) and a table of words, where each word has an ID, and then an associative table that references the words to the images? Or perhaps two associative tables, one to link caption words and the other to link keywords?

Is this a good approach?


texts: Receiving "Query processor could not produce a query plan because of the hints defined in this query" when using FORCESEEK but can use FORCESCAN?

texts:
I have a fairly large table (500 million rows, 30 columns wide, about 130 GB of data total).
One of the columns in that table is a DateTime data type, and I need to repeatedly select records from that table within a certain date range.
Seems like a good candidate for a filtered index to me.
This is my filtered index:
CREATE NONCLUSTERED INDEX IX_Filtered_Table1_DateField1
ON Table1 (DateField1, PrimaryKeyField)
WHERE (DateField1 >= '2/24/20' AND DateField1 < '4/14/20')

This is my query:
DECLARE @MinPrimaryKeyId BIGINT = 2854868995

SELECT TOP 500000 PrimaryKeyField
INTO #Results
FROM Table1 WITH(INDEX(IX_Filtered_Table1_DateField1))
WHERE DateField1 >= '2/24/20'
and DateField1 < '4/14/20'
and PrimaryKeyField > @MinPrimaryKeyId
ORDER BY PrimaryKeyField ASC

When checking out the execution plan, I notice it's using my filtered index but it's defaulting to an Index Scan operation instead of an Index Seek.
If I try try to use the FORCESEEK query hint (in addition to my index hint), I get the classic error:

Query processor could not produce a query plan because of the hints
defined in this query

Why would the query processor be unable to generate an execution plan using a Seek operation on this index when it can do a Scan operation with it? (If I switch out my FORCESEEK query hint with a FORCESCAN it works, which I know is no different than not using a hint at all here.)


texts: Optimal table design for a medical center rental service and recurrent shifts

texts:
I'm trying to figure out the optimal design for the following situation I'm being presented. I'm developing a medical web application with MySQL as db engine.
A medical center has 14-16 offices/rooms that are rented (monthly rate) to doctors from
various medical specialties or areas: oncologists, physical
therapists, general practitioners, psychologists, etc.

All the areas have their session time assignment: oncol. -> 30 minutes, psycho. -> 45 minutes and so on.
All the patients do long treatments, so 99.9% of assigned shifts are recurring through time

So, the question would be:
How could I build the optimal design so I can store:

room assignment for each proffesional from monday to friday. eg:

John (oncologist): MON 15:00 to 20:00, TU: 09:00 to 12:00, WED: 08:00 to 13:00
Mary (psychologist): MON 09:00 to 12:00 and 16:00 to 19:00, THU: 08:00 to 13:00
Michael (general): FRI: 10:00 to 16:00
Henry (dentist): TU: 09:00 to 12:00 and 14:00 to 19:00, WED: 09:00 to 12:00

Each proffesional can change their assigned days (in case there's an office available at the desired day/time) through the months, so I'd be able to keep historical data like:
Which patients had Dr. Phillips treated in Nov. 2013, on which rooms he worked, and which days/hours had been rented to him on that time, etc.

Thanks a lot in advance


texts: SQL Server 2005 datacube processing in high transaction system

texts:
We currently have a highly transactional SQL 2005 database which is growning by around 1.2 million rows weekly. On top of this we have a SSAS datacube which is set to process every two hours for reporting.

Over the last couple of months we have been having increasing failures of the datacube processing due to the underlying measures and dimensions changing as the cube processes. Given this we are looking to re-architect the system in order to remove the problem by moving the data transactionally over to a separate reporting database and then processing the datacube on the reporting database.

Can someone with experience of this comment on if it will fix the problem or will the underlying highly transactional database still cause problems in the replication of the measures/dimensions.


texts: In MySQL if column X has unique values what's the difference between UNIQUE index and B-Tree index

texts:
Let's assume that I have table data:

CREATE  TABLE `test`.`data` (
`row_id` INT NOT NULL ,
`data_id` INT NOT NULL ,
PRIMARY KEY (`row_id`) );

Now, let's assume that data_id has unique values. Is there any difference(space, performance, memory) between having B-Tree index on data_id and UNIQUE index on it? (besides the case when InnoDB is used as storage engine and UNIQUE index can be used as clustering key so row_id is uneccesary)


texts:
I have a question on disks.
How much I/O can a normal disk and a SAN disk can handle?
I have heard about SAN disks should be able to handle upto 200-300 mb/sec, how much does a normal disk can handle or do we need to decide this based on baseline values?

I wanted to know this because while troubleshooting sql server I/O issues , I want to isolate if SQL Server is posting too much of that a Disk is not able to handle that much load or a disk (I/O path) itself is bad so that I an engage storage team.

Thanks in advance.


texts: what is correct way of adding a user in admin with role userAdminAnyDatabase doing upgrade to 2.6

texts:
I am running a mongodb 2.4 with auth enabled  and want to upgrade to 2.6

Instructions in upgrade 2.6 says:

  Before beginning the upgrade process for a deployment that uses authentication and authorization:

  Ensure that at least one user exists in the admin database with the role userAdminAnyDatabase.
  then upgrade binaries
  then run athorization schema format if needed

So I have to add user with the role userAdminAnyDatabase in 2.4  before copies binaries of 2.6 ?

in current 2.4 shell added user with role userAdminAnyDatabase and then schema upgrade with userAdminAnyDatabase role after coping binaries of 2.6

db.addUser( { user: "a", pwd: "pass", roles: [ "userAdminAnyDatabase" ] } )

Is this the right way? Do i need to add read write access as well?

I think i might not get the error below if I go into admin database with role userAdminAnyDatabase

> show collections
2016-02-22T18:51:10.765-0500 error: {
  "$err" : "not authorized for query on oasis.system.namespaces",
  "code" : 13
} at src/mongo/shell/query.js:131


texts: SQL Server Index Scan when using 'OR' operator

texts:
We implemented a Google-style search where SQL queries are run after a debounce is triggered from the front-end. (We know SQL is probably the wrong technology for this, but I'm knee-deep in startup-chaos here.) The query:
SELECT
    TOP(50) [Name], [Surname]
FROM
    [dbo].[Clients]
WHERE
    [Name] LIKE @SearchTerm + '%' OR
    [Surname] LIKE @SearchTerm + '%'

It is a sizable table, so I added two non-clustered indexes on both columns to help speed things up:
CREATE NONCLUSTERED INDEX [IX_Patients_Name] ON [dbo].[Clients]
(
    [Name] ASC
)
INCLUDE([Surname]);

CREATE NONCLUSTERED INDEX [IX_Patients_Surname] ON [dbo].[Clients]
(
    [Surname] ASC
)
INCLUDE([Name]);

My thinking was that SQL would do an index seek on both columns, but it seems the query optimizer decides to use an index scan.

This might not be a real issue for this simple use-case, but we have more complex versions of this with multiple joins, etc.
Is there any way to optimize this query to uses seeks?


texts: DBCC CHECKIDENT ('table name', RESEED, 20000) not working

texts:
When I run the script

DBCC CHECKIDENT ('table name', RESEED, 20000)

the output of the message is

  Checking identity information: current identity value '200000', current
   column value '200000'. DBCC Execution completed. If DBCC printed error
   messages, contact your system administrator.

When I query the identity value by running

SELECT IDENT_CURRENT('table_name')

it is showing up as 200000 as expected, however, when the developer inserts records into the table, it's not using the new reseeded value.

Is there something obvious I'm missing?


texts: How To insert records in SqlExpress Database tables through sqlscript while installing setup?

texts:
I'm creating a database and all of my tables using SQL Server Express through SQL scripts.  Now, I want to insert some records in tables like States (all states names), city (all city names).

How do I make a SQL script for this?  I want to add this code to my sqlscripts after creating the database.  Alternatively, is there a way to restore database tables by sql scripts?


texts: PARALLEL_MIN_TIME_THRESHOLD parameter when AUTO or MANUAL mode

texts:
From How Parallel Execution Works:

The following is a summary of parallel statement processing when parallel degree policy is set to automatic.
A SQL statement is issued.
The statement is parsed and the optimizer determines the execution plan.
The threshold limit specified by the PARALLEL_MIN_TIME_THRESHOLD initialization parameter is checked.

If the execution time is less than the threshold limit, the SQL statement is run serially.

If the execution time is greater than the threshold limit, the statement is run in parallel based on the DOP that the optimizer calculates.

Suppose the parallel_degree_policy is MANUAL and the SQL statement contains a parallel hint or a defined parallel DDL value. Will the Oracle optimizer still check PARALLEL_MIN_TIME_THRESHOLD?
Also, is the estimated execution or the actual execute time used in the calculations around the threshold limit?


texts: We would like to enable SQL Audit on a SQL Server 2014 database. This database is replicated to a read replica via AlwaysOn. We don't need, or want, the replica audited. If we enable audit on the main database, will it automatically get replicated to our replica? If so, is there a way to prevent this?


texts:
I'm using a jsonb column, where the JSON documents contain large integers (PostgreSQL 9.5). I've noticed that when the value stored has too many significant digits, it gets truncated.

As an example, I insert this into my table:

{"value": 7598786232076607106}

And when I select back the same row, I get:

{"value": 7598786232076607000}

According to the documentation, numeric fields in jsonb are stored using the standard numeric type from postgres, and numeric normally allows:

  up to 131072 digits before the decimal point; up to 16383 digits after the decimal point

Is it possible to actually get this behavior with jsonb, instead of truncation?


texts: How to use the covariance and variance in PostgreSQL

texts:
I am going through the documentation:
https://www.postgresql.org/docs/9.5/static/functions-aggregate.html

for the aggregate functions: corr(Y, X), covar_pop(Y, X) but I cannot find any valid example.

Can you please show me how to use these two functions or pointing me to some extra doc/examples?


texts: How to transfer data from denormalized table to normalized tables in Oracle?

texts:
Hi I currently have a database set up with a table like this:

Grades: student name|class name|overall|hw|test|quiz|project|val

where student name and class name are type varchar2 with the rest being type number. The categories overall,hw,test and quiz are expected to be 1 or 0 and val is the actual grade out of 100. So a sample row with a student getting a 95 on her test would look like this:

Grades: Sally | Biology | 0 | 0 | 1 | 0 | 0 | 95

This structure is clearly suboptimal, so I decided to normalize this database with help from this question here to :

Student: id|firstname | lastname (pk = id)

Class: id|name|room num (pk = id)

Categories: id|name|weight (pk = (id, name, weight))

Grades: student_id (fk)| class_id (fk)| category_id (fk) | name | grade_val

ClassEnrollment: student_id (fk)| class_id (fk) | overall_grade (pk = student_id, class_id)

My main question is how to get the data from three tables to fill out the class enrollment table?

I can select all the overall grade using:

SELECT val
FROM grades_old
WHERE overall = 1;

And I can select the student id in a number of steps :

INSERT INTO temp(student, class)
SELECT student_name, class_name
FROM grades_old;

SELECT temp.student_name, students.name, students.id
INNER JOIN students
ON temp.student_name = students.name

However, I'm not sure how to put all this together to get the overall grade, student_id and class_id in one query.

Edit:

I have manually filled out the Category database since there is only 5 categories in the system right now (overall, hw, test, quiz and project). I have a name column in the student table for now for purposes of matching, I figured I could worry about splitting it later then drop that column once I have split the names.

So I guess my focused question is for now how can I select the overall grade, student_id and class_id from the original grades table to insert it into the class Enrollment table?


texts:
I am writing a log analysis program in bash. As part of it, I am compiling a list of known errors, so that I can search and reference those errors as part of the known list. I originally was going to just write them in a .sh file and use source to reference it. Then I realised that I have hundreds of thousands of error messages and that's going to be REALLY hard to do without killing myself.

So. I want to use a small and simple database where I can store error messages, some data associated with them (bug report, who found it, how to fix it, etc.), and it has to be easily searchable. I don't feel like dealing with dynamic queries and all that jazz, I want to be able to use static queries.

I know I can use Access, Excel, and other things like that, but this is CLI Linux. There are just so many options out there that I'm a little overwhelmed as to what would be perfect for something like this. I don't even know what type of database I should use, I just want it to be lightweight and common so it can be supported after I eventually move out of my current role.

What database engine should I use?

PS: I know Python, Perl, Ruby, C (and it's variants), and pretty much anything else would be better than bash for this. My hands are tied.


texts: Can't find stored procedure for updating after creating it

texts:
I'm trying to create a procedure to update my table

create proc UPDATE_PRODUCT
@ID_CAT INT,
@ID_PRODUCT VARCHAR(30),
@LABEL NVARCHAR(30),
@QTE INT,
@PRICE VARCHAR(50),
@IMG IMAGE
AS
UPDATE [Product_DB].[dbo].[PRODUCTS]
   SET [ID_CAT] = @ID_CAT
      ,[LABEL_PRODUCT] = @LABEL
      ,[QTE_IN_STOCK] = @QTE
      ,[PRICE] = @PRICE
      ,[IMAGE_PRODUCT] = @IMG
 WHERE ID_PRODUCT = @ID_PRODUCT

When I run this code the first time, it says "completed successfully"
However, I can't find it in Stored Procedure folder. I tried to do multiple refreshes and restarts but still the same.

When I try to create a procedure for other purpose like selecting or deleting, it works with no problems.

What's the solution?


texts: Merge Table Row Values to Header of Another Table


texts:
I couldn't find any answer for my question so I'm writing.

Are there any restrictions for updating strings in SQLite database ?
I'm wondering if any new value is written into database file there could be problem for strings of different length. Isn't it ?
I anticipate that I update strings in database and their length will be different.
In such situation should I store them into files and store only file names into database ?
Please give me some optimization tips.


texts: How To Remove Spaces Between Characters In PostgreSQL?

texts:
I want to match two tables based on a specific column which is a card number, there are spaces between characters in some of card numbers in the second table like below:

'NES 123_4_5'
'MTS 678_9_0'

What i want is to remove the space between the characters and numbers and change it to following form:

'NES123_4_5'
'MTS678_9_0'

What would be the SQL query of function to do this?


texts:
I have a 30GB innoDB-only database that keeps having Lost connection to MySQL server during query when dumping  when running mysqldump -u root -p --compress --max_allowed-packet=1G  --net-buffer-length=32704  --single-transaction largedb> /home/backup/largedb.sql.gz from the source server

I have net read and write timeouts set to 31536000 in my.cnf, and beyond this I cannot find any other information on why it would be losing connection.

So I decided that I'll just turn off mysql and rsync them. For tables under 1GB this worked out perfectly.
I first created the database and the table struct then ran ALTER TABLE {table} DISCARD TABLESPACE move the .frm and .ibd files into the database folder and then ran ALTER TABLE {table} IMPORT TABLESPACE which fixed all the tables except the main one.

The main table is 30GB  and when importing the tablespace it says Error in query (1034): Incorrect key file for table 'main'; try to repair it There is no additional information in error.log. I googled and it seems to suggest if you get this error but its an innoDB then you might be running out of diskspace in your tmpdir. My tmpdir is /tmp and I have 400GB free.

ANALYZE on table results in

InnoDB: Tablespace has been discarded for table 'main'
InnoDB: Trying to get the free space for table `db`.`main` but its tablespace has been discarded or the .ibd file is missing. Setting the free space to zero. (errno: 11 - Resource temporarily unavailable)
Trying to get the free space for table `db`.`main` but its tablespace has been discarded or the .ibd file is missing. Setting the free space to zero. (errno: 11 - Resource temporarily unavailable)
Operation failed

CHECK on table results in

db.main: InnoDB: Tablespace has been discarded for table 'main'
db.main: Tablespace has been discarded for table 'main'
db.main: Corrupt

Can anyone help me either get mysqldump to stop losing connection or how I can repair the innoDB table?
I am about to turn the table into MyISAM and rsync it

I am running Percona Mysql 8 (based off mysql 8).


texts: How can I export just the structure of a MySQL database?

texts:
I use Ubuntu 14.10 and PhpMyAdmin.

I have a 'big' MySQL database, the size is 900MB. I need just the structure of this database, to use it on my local machine. How can I export the structure only?


texts: Which schools are known for graduation level database research?

texts: Which schools across the world are best known for research being carried out in database field in recent times?


texts: Backup SSRS Encryption Keys Using Powershell

texts:
I have made the following script from google for backing up the SSRS Encryption keys:

cls
$pwd = "sa@123@123"
$SSRSClass = Get-Wmiobject -namespace "root\microsoft\sqlserver\reportserver\rs_BPSSRS\v10\admin" -class "MSReportServer_ConfigurationSetting"

$key = $SSRSClass.BackupEncryptionKey($pwd)
$stream = [System.IO.File]::Create("c:\\SSRS.snk", $key.KeyFile.Length)
$stream.Write($key.KeyFile, 0, $key.KeyFile.Length)
$stream.Close()

But I'm getting the following errors:

Method invocation failed because [System.Object[]] doesn't contain a method named 'BackupEn
cryptionKey'.
At line:5 char:38
+ $key = $SSRSClass.BackupEncryptionKey <<<< ($results)
    + CategoryInfo          : InvalidOperation: (BackupEncryptionKey:String) [], RuntimeEx
   ception
    + FullyQualifiedErrorId : MethodNotFound

Exception calling "Create" with "2" argument(s): "Positive number required.
Parameter name: bufferSize"
At line:6 char:35
+ $stream = [System.IO.File]::Create <<<< ("c:\\SSRS.snk", $key.KeyFile.Length)
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

You cannot call a method on a null-valued expression.
At line:7 char:14
+ $stream.Write <<<< ($key.KeyFile, 0, $key.KeyFile.Length)
    + CategoryInfo          : InvalidOperation: (Write:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At line:8 char:14
+ $stream.Close <<<< ()
    + CategoryInfo          : InvalidOperation: (Close:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

I'm using powershell v2. I tried finding about this but no luck. There are around 50+ SSRS servers in our environment and doing backup manually is tiresome. Hence, we came up with this automation. Kindly provide your comments.

Thanks


texts:
Does doing a COALESCE on an indexed columns in a WHERE clause e.g. WHERE COALESCE(table1.indexed_column, '9999-01-01') > table2.other_date_column affect indexing in a bad way?
I think that it should but in this case how should I convert my where clause?


texts: Need some help with this query or some ideas for change it to better model

texts:
I've a dilemma in my application and I don't get how to solve it at DB level. The idea is that a user can be subscribed to one or more services but services are show depending on user type. This can sound tricky but it does not. So I have a form where I pick which type of user I'm registering but then from services table I need to show only the services allowed for that type of user. I made this table:

CREATE TABLE "nomencla"."service" (
    "id" int4 NOT NULL,
    "name" varchar(80) COLLATE "default" NOT NULL,
    "active" bool,
    "cedulabenefsigesp" varchar(10) COLLATE "default" NOT NULL,
    "user_type" text COLLATE "default",
    CONSTRAINT "tipo_servicio_pkey" PRIMARY KEY ("id")
) WITH (OIDS=FALSE);

ALTER TABLE "nomenclator"."service" OWNER TO "postgres";
COMMENT ON COLUMN "nomenclator"."service"."user_type" IS '(DC2Type:array)';

In user_type column, I stored, as serialized values, the allowed user type for each service, ex:

1   Service1    t   0000000039  a:1:{i:0;s:1:"1";}
2   Service2    t   0000000040  a:3:{i:0;s:1:"2";i:2;s:1:"3";i:3;s:1:"4";}

For row 1 it means: Service1 will be available for users of type 1 and Service2 will be available for users of type 2,3 and 4 only so in register form for users of type 1 I should only show the Service1 option and for users of type 2,3 or 4 then Service2 will be showed, I need some help building a query for get that data or help to change my model with better solution to that problem, any?


texts: Cumulative Sum by distinct contact_ID by year-month

texts:
I've seen some posts here regarding the SUM OVER function to calculate the cumulative sum. But the issue which I have is slightly different. I need to keep a running total of distinct inactive clients by year-month. So far I've created the query to give the result as:

date       InactiveClients  TotalInactiveClients
2016-04    91954             91954
2016-03    90065             182019 <-- sums 2016-04 and 2016-03
2016-02    84517             266536 <-- but in past 3 months i had around 80k of inactivity
2016-01    101561            368097
2015-12    113953            482050
2015-11    85425             567475
2015-10    95681             663156
2015-09    80503             743659
2015-08    82327             825986
2015-07    96672             922658
2015-06    84375             1007033
2015-05    83740             1090773
2015-04    96933             1187706
2015-03    68572             1256278

The query is which gives me the result is here:

SELECT
      Date_FORMAT(pq.date, "%Y-%m") date,
      pq.InactiveClients as 'InactiveClients',
      @runBal := @runBal + pq.InactiveClients as TotalInactiveClients
  FROM ( SELECT
           distinct ss1.time date,
           COUNT(DISTINCT c1.id) as InactiveClients
         FROM `contacts` c1, `statengineSent` ss1
           LEFT JOIN `statengineViews` AS sv1
             ON (sv1.email = ss1.email
             AND sv1.dispatchID = ss1.dispatchID
             AND sv1.email is NULL)
         WHERE ss1.email = c1.email
           AND ss1.time > DATE_SUB(now(), interval 13 MONTH)
         GROUP BY YEAR(date), MONTH(date)
         ORDER BY date desc ) pq,
      ( select @runBal := 0 ) sqlvars

PS: in case you are wondering with the following LEFT JOIN I am excluding all the active contacts.

LEFT JOIN `statengineViews` AS sv1
  ON (sv1.email = ss1.email
  AND sv1.dispatchID = ss1.dispatchID
  AND sv1.email is NULL)
WHERE ss1.email = c1.email
  AND ss1.time > DATE_SUB(now(), interval 13 MONTH)

I need help with the following:
Could anyone help me to make the result of TotalInactiveClients equal the sum of previous months unique contact IDs?
(For example 2015-03 TotalInactiveClients = uniqe count of contactIDs inbetween 2015-03 upto 2016-04. So for 2015-10 - i need all the contactID's which occurred from 2016-04 to 2015-10 (including 2015-10)).


texts:
I am still new to using databases, but currently I am using a mysql database and attempting to make my bash script unbreakable because it might be used in several different environments (dev, qa, etc). I know it will run correctly without this one line.

CREATE TABLE IF NOT EXISTS backupfiles (fileName VARCHAR(20), archiveId VARCHAR(500), checkSum VARCHAR(100), glacierVault VARCHAR(100), timeStamp date);

I also know that if I put that exact line into my database when that table does not exist then it runs perfectly and if it does exist already it gives me a warning, but does not break.

+-------+------+------------------------------------+
| Level | Code | Message                            |
+-------+------+------------------------------------+
| Note  | 1050 | Table 'backupfiles' already exists |
+-------+------+------------------------------------+

When I run the line above and there is no table named backupfiles it works perfectly, but when there is it hangs(when I say hangs it sits there and does nothing) 95% of the time and 5% of the time works. Has anyone run into a problem like this? I am using AWS RDS(Amazon Web serices Relational Database Service) and the mysql server is  5.5.27

Here is all of my code that I relates to the mysql database

mysql -h portal-rds -u $user --password=$mysqlpw <<QUERY_INPUT
CREATE DATABASE IF NOT EXISTS $DATABASE;
use $DATABASE;
CREATE TABLE IF NOT EXISTS backupfiles (fileName VARCHAR(20), archiveId VARCHAR(500), checkSum VARCHAR(100), glacierVault VARCHAR(100), timeStamp date);
INSERT INTO backupfiles VALUES ('$archive_file_name', '$archiveID', '$CURRENTVAULT', '$checkSum', CURDATE());
COMMIT;
QUERY_INPUT


texts: How to see tables and column names in exact case which is created and not by default uppercase?

texts:
In toad, whenever I create an object such as a table, If I name columns in PascalCase when it created if I open the table, then toad displays all the column names in uppercase.

Is there any option in toad which prevents this default behavior and let us see the object names in the exact case which we create them?


texts: Why *not* ERROR: index row size xxxx exceeds maximum 2712 for index "foo"?

texts:
We have repeatedly seen failing attempts to index columns with values exceeding a maximum size. Postgres 10 has this kind of error message for it:

ERROR:  index row size xxxx exceeds maximum 2712 for index "foo_idx"
HINT:  Values larger than 1/3 of a buffer page cannot be indexed.
       Consider a function index of an MD5 hash of the value, or use full text indexing.

Examples:

Character varying index overhead & length limit
Index max row size error

Etc.

Now, a_horse_with_no_name demonstrated a case with much larger text values (10000 characters) that still seems to work with a UNIQUE index in Postgres 9.6. Quoting his test case:

create table tbl (col text);
create unique index on tbl (col);

insert into tbl
values (rpad(md5(random()::text), 10000, md5(random()::text)));

select length(val) from x;  -- 10000

No error, and the column value indeed tested with a length of 10000 characters.

Have there been recent changes or how is this possible?


texts: PostgreSQL query very slow when subquery added

texts:
I have a relatively simple query on a table with 1.5M rows:
SELECT mtid FROM publication
WHERE mtid IN (9762715) OR last_modifier=21321
LIMIT 5000;

EXPLAIN ANALYZE output:

Limit  (cost=8.84..12.86 rows=1 width=8) (actual time=0.985..0.986 rows=1 loops=1)
  ->  Bitmap Heap Scan on publication  (cost=8.84..12.86 rows=1 width=8) (actual time=0.984..0.985 rows=1 loops=1)
        Recheck Cond: ((mtid = 9762715) OR (last_modifier = 21321))
        ->  BitmapOr  (cost=8.84..8.84 rows=1 width=0) (actual time=0.971..0.971 rows=0 loops=1)
              ->  Bitmap Index Scan on publication_pkey  (cost=0.00..4.42 rows=1 width=0) (actual time=0.295..0.295 rows=1 loops=1)
                    Index Cond: (mtid = 9762715)
              ->  Bitmap Index Scan on publication_last_modifier_btree  (cost=0.00..4.42 rows=1 width=0) (actual time=0.674..0.674 rows=0 loops=1)
                    Index Cond: (last_modifier = 21321)
Total runtime: 1.027 ms

So far so good, fast and uses the available indexes.
Now, if I modify a query just a bit, the result will be:
SELECT mtid FROM publication
WHERE mtid IN (SELECT 9762715) OR last_modifier=21321
LIMIT 5000;

The EXPLAIN ANALYZE output is:

Limit  (cost=0.01..2347.74 rows=5000 width=8) (actual time=2735.891..2841.398 rows=1 loops=1)
  ->  Seq Scan on publication  (cost=0.01..349652.84 rows=744661 width=8) (actual time=2735.888..2841.393 rows=1 loops=1)
        Filter: ((hashed SubPlan 1) OR (last_modifier = 21321))
        SubPlan 1
          ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)
Total runtime: 2841.442 ms

Not so fast, and using seq scan...
Of course, the original query run by the application is a bit more complex, and even slower, and of course the hibernate-generated original is not (SELECT 9762715), but the slowness is there even for that (SELECT 9762715)! The query is generated by hibernate, so it is quite a challenge to change them, and some features are not available (e.g. UNION is not available, which would be fast).
The questions

Why cannot the index be used in the second case? How could they be used?
Can I improve query performance some other way?

Additional thoughts
It seems that we could use the first case by manually doing a SELECT, and then putting the resulting list into the query. Even with 5000 numbers in the IN() list it is four times faster than the second solution. However, it just seems WRONG (also, it could be 100 times faster :) ). It is completely incomprehensible why the query planner uses a completely different method for these two queries, so I would like to find a nicer solution to this problem.


texts: cannot create a procedure with error [1064] [42000]

texts:
I am using mysql 5.7 and I have some code which works. But when I try to create procedure with the code, it reports error [1064] [42000]. I try to update the content of an old procedure with my current code and it works. Does someone know why this happens?

Here is how I try to create a procedure.

create procedure dispatch_remaintimecal_12division (in lgtid VARCHAR(20))
begin
my code;
end


texts:
I have table with varchar column. Some rows are empty, some have content.
While preparing SELECT statement, I accidentaly run query like that:

SELECT column_x FROM table_x WHERE column_x;

It gave me all rows where column_x content begins with numbers. How WHERE condition works in this case and why it selects that values but not values which begins with letters or special signs?


texts:
In this example Query,

SELECT [Account Transactions].ID, [Account Transactions].Transaction_Amount, *
FROM [Account Transactions]
GROUP BY [Account Transactions].ID, [Account Transactions].Transaction_Amount;

This Idea is to Group Account Transaction.ID's and capture the Sum of tracaction_amount for all transaction of the same ID.

The code was generated by Access 2016 and when executed I get:

  Can not group on Fields with '*'

The interesting thing is that I can generate a report with the very same grouping and it works perfectly.


texts: Why would an application use one central metadata table to hold metadata for all other tables?

texts:
I've started working with an application that has an usual design:
In addition to the tables that contain the actual data, there is one metadata table that contains all the information about the other tables: their names, their columns, which columns link them to which other tables, and so on... all in a single table.
The application mostly queries this table to figure out how to query for other entities, how to query or update related tables, how to insert, delete.
The result of this is that the application's data access code is quite generic. Most of the data access functions just take a few parameters which represent the names of things (concrete tables and columns) to look up in the metadata table.
The main drawback, as far as I can tell is that when the schema changes, the concrete structures and the content of the metadata table need to be kept in sync or the application could break, although it seems that the metadata table is generated from a custom-written script that also creates/alters the other tables. Maintenance of that script and the original data model is another concern.
I also have to consider that this data access component may either replaced or preserved for new applications. If it were to be replaced, it would probably be with something more "normal" like hibernate or some other framework. I'm quite torn on which way to go since the current code, while a little odd-looking, seems to work fine.
Are there any hidden advantages/disadvantages that I might have missed that might help me make a decision?
Actually, what is this design pattern even called, in case I want to look up other implementations of it? I don't think I've ever seen anything like it before, I don't really know what to call it or how to research it.


texts: Running SQL job: The login is from an untrusted domain and cannot be used with Windows authentication

texts:
I have two databases in different servers (server1 , server2)

server1:
Login : login1 /  password :123 /member of sysadmin (user user1,password:pass)
server2 :
Login :login1 /   password :123 /member of sysadmin (user user1,password:pass)

when I try to connect to server1's linked server from server2 , I can do it without issue in stored procedures.but when I try to run sp in sql jobs in server2, this issue happens:

  The job failed.The Job was invoked by User user1.
  Executed as user: Server2\Administrator. Login failed. The login is
  from an untrusted domain and cannot be used with Windows
  authentication. [SQLSTATE 28000] (Error 18452).  The step failed.

(the owner of sql job is Login1)

Whats the point?Thank you for any help.


texts: Unable To Restore Database From .BAK File

texts:
I am trying to restore my .bak file but I keep getting the error

  Restore of database 'DataWarehouse' failed.  (Microsoft.SqlServer.Management.RelationalEngineTasks)System.Data.SqlClient.SqlError: Restore Database is terminating abnormally (Microsoft.SqlServer.SmoExtended)

Is this a corrupt .bak file I am trying to restore from?

The steps I took were

  1) Right clicked on the server name and chose restore
  2) Navigated to the location of the .bak file
  3) Clicked Ok


texts: Difference between functional dependency and join dependency

texts:
By definition, a functional dependency is defined as

  Let X and Y be subsets of attributes of a relation R. A functional dependency X → Y holds over R if and only if for any instance r of R,
  whenever two tuples t1 and t2 of r agree on the attributes X, they
  also agree on the attributes Y. That is,

  t1.X = t2.X ⇒ t1.Y = t2.Y

A join dependency, meanwhile,

  A table T is subject to a join dependency if T can always be recreated by joining multiple tables each having a subset of the attributes of T.

I could be wrong, but I was wondering if there are any relation between the concept of functional dependency and join dependency.


texts: How to create multiple container databases in 12c R1?

texts: As multiple databases can be created under the same/different Oracle home(s) in Oracle 11g on a host, please advise me on creating multiple container databases to create multiple pluggable databases under them to overcome monolithicity of Oracle 12c.


texts: How to detect bad execution plans automatically?

texts:
I want to automate a way to detect when a stored procedure plan changed for worse. I am planning to do it using a list of critical SPs and checking when their plans changed. Then compare the execution metrics with the previous executions collected in extended events.

Does anyone have any recommendations to monitor plans without using a lot of server resources?


texts:
Dim rs4 As Recordset = B1Connections.diCompany.GetBusinessObject(BoObjectTypes.BoRecordset)
Dim qry4 As String = "SELECT Name FROM [@CCS_LOGINDET]"
rs4 = obgn.Return_RecordSet(qry4)
Dim constr As String = "Data Source=" + B1Connections.theAppl.Company.ServerName + ";initial catalog=" + B1Connections.theAppl.Company.DatabaseName + ";User Id=" + B1Connections.diCompany.DbUserName + ";Password=" + CType(rs4.Fields.Item(0).Value, String)
Dim con As SqlConnection = New SqlConnection(constr)
con.Open()
Dim cmd As SqlCommand = New SqlCommand("CCS_SP_PYRL_BONUS_CALCULATION_AGAPPE", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@EMPID", txt_empid.Value.Trim)
cmd.Parameters.AddWithValue("@DATE", txt_cdate.Value.Trim)
Dim outParam As New Data.SqlClient.SqlParameter("@NETBONUS", AMOUNT)
outParam.Direction = Data.ParameterDirection.Output
cmd.Parameters.Add(outParam)
bonusamount = cmd.ExecuteNonQuery()
txt_bonus.Value = bonusamount

This code always returns -1.00.
The procedure works correctly in backend and it returns (numeric(19,6)).  I think the problem is with my front end...  Can anyone help me?


texts: How do I detect execute permission granted to a role (when no ON clause was used)

texts:
I just spent hours trying to figure out how an app had privilege to execute stored procedures.

TIL from StackExchange: Turns out the ON clause is optional for GRANT, allowing a role to be granted execute on everything. I had to laugh at the very bottom of this MSDN page after I had tried my best to validate the syntax.

GRANT Object Permissions (Transact-SQL)

CREATE ROLE Test
GRANT EXECUTE TO Test

Ok, so that's cool and all (not very principle-of-least-privilege-esque).

However: How am I to discover or detect that grant?

I tried looking through all the SQL Server Management Studio login, role, user, and schema privilege GUIs and don't see this type of Grant identified anywhere.

While debugging, I found questions about enumerating privileges in a query and while those are related, this scenario is neither mentioned nor covered by the answers there:

How do I determine using TSQL what roles are granted execute permissions on a specific stored procedure?
List all permissions for a given role?
can exec permissions be granted to a role in SQL Server 2008 / 2012

I'm looking for the most reliable way of identifying this method of granting (or denying) privileges.


texts:
I have a simple join query below :

select jobs.location_id
from companies,
     jobs
where jobs.company_id = companies.id
and companies.name = 'name';

it doesn't seem to get the job location of company has that name, but somehow all location_id in jobs table.

Meanwhile these two separate queries do the job:

select id from companies where name = 'name';
select jobs.location_id from jobs where jobs.company_id = [resulted id from query above];

What's wrong with the first query ?


texts: How to apply many UPDATE statements in one network call

texts:
Can we apply many UPDATE statements in a single network call?
Figure many UPDATE requests on a JSONB field of one record.
I would like to update many places in the JSONB field.
All updates apply on the same record.
Today I SELECT/UPDATE the entire JSONB field, but I am looking for a more performant solution.


texts:
I want to use GUIDs since they are portable and makes it easier to use several database. But since there are performance issues it might not be a good idea? Although I've found a MS blog entry which states otherwise: http://blogs.msdn.com/b/cbiyikoglu/archive/2012/05/17/id-generation-in-federations-identity-sequences-and-guids-uniqueidentifier.aspx

I've read about an alternative where a datetime2 column is used as the clustered index and the Guid as a PK. As most of my tables have a CreatedAtUtc column that should be a viable option for me. But do it really help?

So my questions is whether I should use GUIDs to get portability or if there are better solutions?

(Using GUIDs I can also create the Id in my code and do more stuff before committing). I've also created a COMB generator which should work better with MSSQL than regular GUIDs.


texts: Split varchar2 variable based on vertical line delimiter

texts:
I have following plsql code

DECLARE
   v_personal_info VARCHAR2(100);
   v_name VARCHAR2(100);
   v_signature VARCHAR2(100);
   CURSOR c_personal_info
   IS
     select personal_info from users where name is not null;
BEGIN
  FOR lr_row IN c_personal_info
  LOOP
    v_personal_info := lr_row.personal_info;
    -- Need to split v_personal_info based on |
  END LOOP;
END;
/

Typical values of v_personal_info will look like 'Aniket Thakur | athakur' and I need to split this to different variables like

v_name := 'Aniket Thakur'
v_signature := 'athakur'

How should I do that. Is there any in built function that does a split based on predefined delimiter?


texts: Efficient method to update character length of columns in large table

texts:
I have a table with 80+ million records. There are 2 columns which are currently varchar(MAX), which I would like to update to varchar(255) to save space. It is not an issue if data will be truncated. This table has 3 non-clustered indexes on it.

SQL Server version: 2012 Web Edition

A solution that would require table locking or taking the database offline would be acceptable.

What I have tried:

ALTER COLUMN: SSMS does not let me use the ALTER COLUMN statement because there is an index that uses that column.
Updating in batches: does not work efficiently (or at all) since the unique identifier (or date field) that would be needed to keep track of which records were updated is not indexed and therefore the queries just hang. Also I read that updates would generate a lot of logs.
Adding a new table and inserting in batches: does not work because of same reason above.
SSMS Export Wizard: Creating a new table with the correct column data types and exporting from original works within a few hours ONLY IF I don't create the indexes on the table. If I first create the indexes and then run the export, the process gets stuck at about 25/30 million. The first few million imports fairly quickly but gets slower as it goes on. I can confirm this after I had it run for 12 hours. In the former case, I am unsure how to build these indexes as I am concerned about how much memory/cpu/space it will end up taking on my server
Do work on test server and re-attach: This last point wasn't tried yet but is next on list if I don't get another idea: Transfer/copy database (via file transfer) to test server, try applying the change there without having to worry about performance costs and then re-attaching to original server. (I can also see how long and how much space an index rebuild would take, but then again that wouldn't offer me accurate information for the production environment.)

Any ideas or comments on how to accomplish this?


texts: How do I determine the time period to which the SQL Server i/o warning is referring?

texts:
SQL Server log shows batches of warnings, of the type:

  SQL Server has encountered 18757 occurrence(s) of I/O requests taking
  longer than 15 seconds to complete on file [T:\..

  SQL Server has encountered 951 occurrence(s) of I/O requests taking
  longer than 15 seconds to complete on file [M:\..

Our server is very busy in the hours before these warnings. Are they related to very recent requests or all requests since the last time a similar warning occurred?
I am trying to tie in the warnings to identify the jobs that might have caused them.
We have a long running job that finishes 10 minutes before these warnings occur. Are warnings collected and expressed on reaching a request threshold or on schedules? Can anyone explain the rules behind this warning log?


texts: How to determine if it is necessary to increase the sga and the pga in Oracle database

texts:
How to determine if it is necessary to increase the sga and the pga in a database oracle 12c, is there any procedure, any specific view where can I be guided that indicates to me that it is necessary to increase the pga or the sga?
Thank you


texts: How to get rows containing ONLY certain strings?

texts:
I have these words in my words table in a MySQL db:

kaki

koku

kao

kakeru

How can I get all rows that contains ONLY 'ka','ki','ku','ke','ko'?

I want to get kaki and koku but not kao and kakeru, because although kao contains ka, it also has o, and although kakeru contains ka and ke it also contains ru.

I have tried:

select *
from words
where (
    word like '%ka%'
    or word like '%ki%'
    or word like '%ku%'
    or word like '%ke%'
    or word like '%ko%'
    );

but I don't get the result that I want.

EDIT: I can't comment, but yes I want to break it into 2 syllables.


texts: SQL Server - MERGE join condition with NULLable Columns

texts:
I am trying to merge two of my tables (identical definition).

MERGE INTO xx.dbo.acc_s AS Target
 USING yy.dbo.acc_s AS Source
 ON (Target.acc_id= Source.acc_id AND Target.s_id= Source.s_id AND a_code= Source.a_code)
WHEN NOT matched BY Target THEN
INSERT (acc_id,s_id,a_code)
VALUES (Source.acc_id,Source.s_id,Source.a_code);

What I expected is that "If Target doesn't have the row, INSERT it" - Nothing Else.

I got a "Violation of PRIMARY KEY" error on one of the rows. If it's a matching row BY TARGET, it shouldn't have attempted to insert it in the first place.

What I did after that was:

MERGE INTO xx.dbo.acc_s AS Target
 USING yy.dbo.acc_s AS Source
 ON (Target.acc_id= Source.acc_id AND Target.s_id= Source.s_id AND ISNULL(a_code, '')= ISNULL(Source.a_code, '')
WHEN NOT matched BY Target THEN
INSERT (acc_id,s_id,a_code)
VALUES (Source.acc_id,Source.s_id,Source.a_code);

And it picked up correctly. From this, I am thinking that when I am merging, if I have two NULL columns joining - SQL Server cannot resolve this correctly. Have I encountered an expected behaviour here?

I know that I should probably rather use

WHEN matched THEN
UPDATE SET a_code = Source.a_code

But in that case I might have to change quite a lot of things as I am trying to find a general way to update other tables by creating a template query. However, I understand that it might not be practical.


texts:
I have a table of mostly-static objectives and another to track users' completions of those objectives. They can associate the completion with one or more records entered in another table and/or a text note. I'd like to format all of this together into a single entry for displaying in a table (i.e. one row per objective).

Here's an example of what Completion might look like:

ID    userID    objectiveID    recordID    text
1     4         8              500         NULL
2     4         8              NULL        "Lorem ipsum..."
3     4         8              750         NULL

I've gotten this far:

SELECT objectiveID,
   GROUP_CONCAT(recordID SEPARATOR ',') AS records,
   GROUP_CONCAT(text SEPARATOR ',') AS text
FROM Completion AS c
GROUP BY objectiveID;

Which returns:

objectiveID    records    text
8              "500,750"  "Lorem ipsum..."

What I'd actually like to display, however, is an attribute of the code being referenced by recordID... Suppose that this is the Record table:

ID    userID    codeID
500   4         1111
750   4         2222

And that this is the Code table:

ID    description
1111  dolor
2222  sit amet

My desired output would be:

objectiveID    records            text
8              "dolor, sit amet"  "Lorem ipsum..."

What's the best approach to get the other values merged in?


texts:
I'm trying to upgrade one of our applications and we have to create the database again due to the app now requiring case sensitive collation.  I've created the new DB and run the table creation scripts, but now they give us a SQL to run to compare the table configs between the old and new DB.  I keep getting the error below from using that SQL. I can't figure out if I'm missing something or if there's just a problem with the way I'm running it (SQL Server Management Studio). Can anyone explain what I'm missing?

SELECT
src.TABLE_NAME,
src.COLUMN_NAME,
src.DATA_TYPE,
src.CHARACTER_MAXIMUM_LENGTH,
dst.TABLE_NAME,
dst.COLUMN_NAME,
dst.DATA_TYPE,
dst.CHARACTER_MAXIMUM_LENGTH
FROM originalDB.INFORMATION_SCHEMA.COLUMNS src
INNER JOIN newDB.INFORMATION_SCHEMA.COLUMNS dst
ON dst.TABLE_NAME COLLATE DATABASE_DEFAULT = src.TABLE_NAME COLLATE DATABASE_DEFAULT
AND dst.COLUMN_NAME COLLATE DATABASE_DEFAULT = src.COLUMN_NAME COLLATE DATABASE_DEFAULT
WHERE dst.CHARACTER_MAXIMUM_LENGTH &lt; src.CHARACTER_MAXIMUM_LENGTH
OR dst.DATA_TYPE COLLATE DATABASE_DEFAULT &lt;&gt; src.DATA_TYPE COLLATE DATABASE_DEFAULT;

Msg 4145, Level 15, State 1, Line 14
An expression of non-boolean type specified in a context where a condition is expected, near ';'.


texts:
I'm loading data into a data warehouse. Customers/transactions are coming from the ERP and from Salesforce. Product codes do not match up between the systems.

Is there a standard pattern for solving this problem?

I'm trying to be careful to avoid a big opinion-based question/answer. I'm really just looking for the fact of how this problem is normally addressed. Maybe it's as simple as, "Put some cross reference tables in the warehouse or transform the product codes in your ETL jobs." But maybe warehouse experts could share their experience.


texts: Prevent record from being inserted in the table in After Trigger

texts:
I have a scenario where I have to insert a record in TableA only if the RecordId exists in TableB.

I have written an Instead of Trigger on TableA for Insert, in which I am checking if the RecordId exists in TableB, and if it exists then I am allowing the Insert in TableA by explicitly inserting the record in TableA in that trigger.

I want to know can I write an After Trigger on TableA, in which I do the same check, and in case if RecordId is not found in TableB then I delete the inserted record from TableA.

My question is If I fire delete query on TableA for the same record which I am inserting will it work, and prevent the record from being inserted from the TableA actually.


texts: Postgres 9.6.1 Full Text Search dictionaries for most spoken languages

texts:
Using Mac OSX Yosemite 10.10.5, I am trying to run full text search operations, such as to_tsvector, to_tsquery, etc and have a need for dictionaries in about 80+ languages.

Postgres seems to only come with 16 language configurations, with an additional two I am testing for Chinese (jiebacfg and testzhcg aka ZHParse). I'm looking for documentation or a repository of other languages beyond these.

mydatabase=# \dF

               List of text search configurations
   Schema   |    Name    |              Description
------------+------------+---------------------------------------
 pg_catalog | danish     | configuration for danish language
 pg_catalog | dutch      | configuration for dutch language
 pg_catalog | english    | configuration for english language
 pg_catalog | finnish    | configuration for finnish language
 pg_catalog | french     | configuration for french language
 pg_catalog | german     | configuration for german language
 pg_catalog | hungarian  | configuration for hungarian language
 pg_catalog | italian    | configuration for italian language
 pg_catalog | norwegian  | configuration for norwegian language
 pg_catalog | portuguese | configuration for portuguese language
 pg_catalog | romanian   | configuration for romanian language
 pg_catalog | russian    | configuration for russian language
 pg_catalog | simple     | simple configuration
 pg_catalog | spanish    | configuration for spanish language
 pg_catalog | swedish    | configuration for swedish language
 pg_catalog | turkish    | configuration for turkish language
 public     | jiebacfg   | configuration for jieba
 public     | testzhcfg  |
(18 rows)

I've installed Ispell Dictionary in attempt to get more dictionaries, and then tried installing a few of the dictionaries from the list, but many are quite arduous to install, don't have the necessary .dict files, are for Windows only, or are simply unavailable.

I'm looking for perhaps updated ways to install language dictionaries for Postgres 9.6.1.

Will need support for: (the one's with lang. next to it means it's already pre-installed with PG 9.6.1)

French  french
Español spanish
Afrikaans
Albanian
العربية
Armenian
Azerbaijani
Basque
Belarusian
Bengali
Bosnian
Bulgarian
Catalan
Cebuano
Chichewa
中文（简体)
中文（繁體)
Croatian
Czech
Danish  danish
Dutch   dutch
Esperanto
Estonian
Filipino
Finnish finnish
Galician
Georgian
Deutsch german
Greek
Gujarati
Haitian Creole
Hausa
Hebrew
Hindi
Hmong
Hungarian   hungarian
Icelandic
Igbo
Indonesian
Irish
Italian italian
Japanese
Javanese
Kannada
Kazakh
Khmer
Korean
Lao
Latin
Latvian
Lithuanian
Macedonian
Malagasy
Malay
Malayalam
Maltese
Maori
Marathi
Mongolian
Myanmar
Nepali
Norwegian   norwegian
Persian
Polish
Portuguese  portuguese
Punjabi
Romanian    romanian
Russian russian
Serbian
Sesotho
Sinhala
Slovak
Slovenian
Somali
Sudanese
Swahili
Swedish swedish
Tajik
Tamil
Telugu
Thai
Turkish turkish
Ukrainian
Urdu
Uzbek
Vietnamese
Welsh
Yiddish
Yoruba
Zulu
English english


texts:
Is it possible to increase the WAL segment size in an existing PostgreSQL 11 database? What are the disadvantages of having larger WAL segment sizes?

The reason for wanting to increase it from 16 MB is to increase the speed of transferring WAL backups when using WAL-E. 16 MB WAL files were transferring at a speed of about 20 MB/s (using SSDs in striped mirrored pairs) which is really slow for database sizes larger than 1 TB.

After increasing the WAL segment size, do we typically want to change the values for min_wal_size and max_wal_size?


texts:
I have a basic question about PostgreSQL:
When connecting to my database server via pgadmin3, I can see that there are multiple databases.
For example, let's say I can see "test", "test1", and "postgres".

From there, I can choose which database I want to connect to.

However, from the command line, I'm doing the following to connect:

psql -U adminusername

This brings me to a postgres=# prompt.

Once connected, I tried to do a \l command to see all databases, but the list I get back is different from what I see using pgadmin3.

Just wondering what I might be doing wrong.


texts: pt-query-digest slowlog output format looses query cache hit information?

texts:
I'm using pt-query-digest (v2.2.20) slowlog output format to filter out queries based on their timestamp, to run further digests on the filtered slowlog :

ptqd 11-21.slow.log --output slowlog --no-report --filter '($event->{timestamp} > 1483957800) && ($event->{timestamp} < 1483959600)' > 11-21.halfhour.log

The point is my source log 11-21.slow.log has query cache hit info, for instance :

# Time: 170109 12:08:43
# User@Host: pidf_user[pidf_user] @ [10.226.13.132]
# Thread_id: 32829731 Schema: webmbpidf QC_hit: No
# Query_time: 0.015387 Lock_time: 0.000038 Rows_sent: 0 Rows_examined: 0

Whereas the filtered output 11-21.halfhour.log doesn't :

# Time: 170109 11:59:59
# User@Host: pest_user[pest_user] @ 10.226.13.13 []
# Thread_id: 32805954
# Query_time: 0.000221 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0

l lose schema information as well but it's less a problem in my situation.

Is there a way to keep the query cache info in the slowlog output ?

Any help would me much appreciated.


texts: What are the necessary course requirements to become a DBA?

texts:
Before some of you say it is related to other posts, let me tell you that I am more interested about schooling and getting my Master's degree not necessary learn SQL and such.

I just graduated with a Bachelor's of Science degree in Computer Science and am now working in the automotive field. It's not too bad, but I'm just not fond of the automotive industry. I mainly got this job because I had experience with automotive software as an intern.

Anyway, one of the courses I had to take as an undergrad was Intro to Database Systems and I found it very interesting. I would like to get a master's degree in Database Administration or Architecture. Where I am currently located, I have very limited options. The university that is close to me is Oakland University, Michigan and here is the degree information and requirements.

My question is, this program doesn't really seem like it is geared towards a specific topic but rather a few general topics. It states that I need to take a total of 16 credits from the specialty group courses (which is in the link). I'm assuming Database Systems 1 and 2 is a must, but what other courses would benefit me? As I was saying, they have project planning, networking, and other courses which (to me) seem unrelated to what I need to get a job in this field in my future.

If someone experienced could briefly explain what their education was like that helped them get into the DBA field that would be great too and I would really appreciate it.

Thank you.
(I apologize for not adding the correct tags because I'm not sure which one to use besides dba, so feel free to add more if necessary)

EDIT: Forgot to mention, if you can tell me the necessary certificates for this field as well that would be great!!


texts: What is background job queue in sql server? How to create a background job?

texts:
While exploring the SQL server I came up with following views:

sys.dm_exec_background_job_queue;
sys.dm_exec_background_job_queue_stats;

Official site tells that

It Returns a row for each query processor job that is scheduled for asynchronous (background) execution.

So how can I create a background (Asynchronous) jobs what will populate these system views to see queued jobs in these tables or somewhere else?

PS: I am new to SQL Server I don't know if there is something like background job! Correct me if I am wrong.


texts: How can I rectify an "arithmetic overflow occurred error" when trying to merge these two queries?

texts:
Let us consider the first query:

SELECT r.cnum as cnum, c.name as name, r.assets as assets
FROM research..complex c, research..retirement_simple_ira r
where r.date = '9/30/2016'
and c.cnum = r.cnum
ORDER BY cnum

This gives us a result set as follows:

Here is my second query:

SELECT cf.cnum, SUM(rs.dh_simple + rs.ha_simple) as sums
FROM retirement_survey as rs
INNER JOIN currentfunds as cf
ON rs.fundid = cf.fundid
WHERE rs.date  = '9/30/2016'
GROUP BY cf.cnum
HAVING SUM(rs.dh_simple + rs.ha_simple) > 0

I attempt to merge these two queries so that I would get a result set which lists 4 columns:

cnum | name | assets | sums

Here is my attempt:

SELECT cf.cnum as cnum, c.name as name, r.assets as assets, SUM(rs.dh_simple + rs.ha_simple) as sums
FROM research..complex c, research..retirement_simple_ira r, retirement_survey as rs
INNER JOIN currentfunds as cf
ON rs.fundid = cf.fundid
WHERE rs.date = '9/30/2016'
HAVING SUM(rs.dh_simple + rs.ha_simple) > 0

and that is when I receive the error. What am I doing incorrectly?


texts:
I have two tables that I need to pull data from.  I need to produce an order total in the query.  Should be straight forward, but I get inaccurate results for anyone who has multiple orders.  This is my DDL

Create Table #first
(
  userid varchar(100)
  ,orderqty int
  ,orderprice float
  ,orderdesc varchar(500)
  ,orderdate datetime
)
Create Table #second
(
  userid varchar(100)
  ,orderstatus varchar(100)
  ,orderreviewstatus varchar(100)
)

Insert Into #first VALUES
('emp12', '14', '10', 'firstorder', '2016-08-11 13:59:23.363')
,('bla', '1', '20', 'red socks', '2016-08-11 13:59:23.363')
,('gra23', '2', '4', 'black coat', '2016-08-11 13:59:23.363')
,('meh33', '5', '2', 'orange socks', '2016-08-11 13:59:23.363')
,('meh33', '3', '1', 'blue trousers', '2016-08-13 13:59:23.363')

Insert Into #second VALUES
('emp12', 'Shipped', 'Reviewed')
,('bla', 'Shipped', 'Reviewed')
,('gra23', 'Shipped', 'Reviewed')
,('meh33', 'Shipped', 'Reviewed')
,('meh33', NULL, NULL)

In this example only userid meh33 has two orders, and this is my query I am using

Select
a.userid
,a.orderqty
,a.orderprice
,SUM(a.orderqty*a.orderprice) As OrderTotal
,a.orderdesc
,a.orderdate
,b.orderstatus
,b.orderreviewstatus
FROM #first a
INNER JOIN #second b
ON a.userid = b.userid
GROUP BY a.userid, a.orderqty, a.orderprice, a.orderdesc, a.orderdate,     b.orderstatus, b.orderreviewstatus

This is the output that this query produces (notice the multiple entries for meh33:

userid  orderqty    orderprice  OrderTotal  orderdesc   orderdate   orderstatus orderreviewstatus
bla 1   20  20  red socks   2016-08-11 13:59:23.363 Shipped Reviewed
emp12   14  10  140 firstorder  2016-08-11 13:59:23.363 Shipped Reviewed
gra23   2   4   8   black coat  2016-08-11 13:59:23.363 Shipped Reviewed
meh33   3   1   3   blue trousers   2016-08-13 13:59:23.363 NULL    NULL
meh33   3   1   3   blue trousers   2016-08-13 13:59:23.363 Shipped Reviewed
meh33   5   2   10  orange socks    2016-08-11 13:59:23.363 NULL    NULL
meh33   5   2   10  orange socks    2016-08-11 13:59:23.363 Shipped Reviewed

And this is my desired output: (one row for each order)

userid  orderqty    orderprice  OrderTotal  orderdesc   orderdate   orderstatus orderreviewstatus
bla 1 20 20 red socks 2016-08-11 13:59:23.363 Shipped Reviewed
emp12 14 10 140 firstorder 2016-08-11 13:59:23.363 Shipped Reviewed
gra23 2 4 8 black coat 2016-08-11 13:59:23.363 Shipped Reviewed
meh33 5 2 10 orange socks 2016-08-11 13:59:23.363 Shipped Reviewed
meh33 3 1 3 blue trousers 2016-08-13 13:59:23.363

What must I alter in my query in order to get my desired result set returned?


texts: How to convert DB2 9.5 to DB2 10.5 online (Linux) ? This can be done with IBM InfoSphere Data Replication? Has anyone done this or have any idea how to do?


texts:
I have a spreadsheet I'm importing into the database raw, a SQL Server 2016 database.  It has records in what is known as the "Lockbox format", used by banks apparently.  It looks something like this generically:

Batch1 details
child record
another child record
summary for batch
Header Row for Batch2

I have a fiddle set up here.

What's important to note is:

Column 1 indicates the "type" of record the row is.  It's the only "universal" column.
Every other column changes in nature based on the type of record.
The only thing tying child records to a parent is that they appear after a parent and before the next parent.  ID's are not shared between rows.
There are a variable number of child rows following a batch (not predictable).

Is there a way to query this so that I can easily group e.g. a batch and it's child + footer records?  One of the things I have to do is indicate that the footer records contain the right aggregates (taken from the child records; including the # of child records for a batch, which is part of what I need to verify).


texts: How to GRANT Permission on many views? Azure SQL

texts:
We have to grant permissions to alter 6 Views to one user.
Is there any way to do it in one query, or do I have to make one query for each different view?

I have no problem doing it one by one because there's only six of them, but if I had to do it for 100 or 200, it would became impossible.


texts: Finding out the exact size of the database after inserting records?

texts:
I have this query:

SELECT
  table_schema "db.name"
, Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
FROM
  information_schema.tables
GROUP BY
  table_schema;

Does this query show accurate results?


texts: Set method to change StartMode property using SqlServer.Management.Smo.Wmi.Service

texts:
I'm trying to change SQL server Configuration manager settings using Microsoft.SqlServer.Management.Smo.Wmi.Service. Microsoft provided with a list of methods & properties here. I used the following code to change the Service account of a particular service:

$MyServerObject.SetServiceAccount($UName, $PWord)

because there exists a method called SetServiceAccount. But I do not understand/know how to set StartMode which a property. I know that currently my service says it is automatic,coz I can verify it using this line of code

$MyServerObject.StartMode

and the result is: Auto

Anybody knows how to set this property?

If someone wants to see what I'm talking about run these lines in PowerShell (Run as Administrator) on a computer where SQL Server is installed:

#Load assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null

#Create Server Object
$SMOWmiserver = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer')

#Query Server object
$SMOWmiserver.Services |
    select name, type, ServiceAccount, DisplayName, Properties, StartMode, StartupParameters |
        Format-List

#Select a service
$MyServerObject=$SMOWmiserver.Services | where {$_.name -eq "MSSQLSERVER"}
$MyServerObject.StartMode


texts: Add a total quantity column for groups of rows

texts:
I have an Items table and an Inventory table.  An Item can belong to multiple Inventory records.  I'm trying to return a list of ALL Inventory records but with their quantity in a new column.  For instance:

Items

ItemID     ItemDescription
103        Headphones
115        Speakers
230        Wireless Adapter
275        20' Network Cable

Inventory

InventoryID        ItemID        WarrantyDate       Status
1                  103           12/22/2010         Available
2                  103           05/15/2012         Available
3                  103           02/24/2015
4                  275           01/01/2010
5                  275           01/01/2011

If I try to COUNT the ItemID and GROUP BY ItemID, like so:

SELECT ItemID, COUNT(ItemID) AS Quantity
FROM Inventory
GROUP BY ItemID

I get:

ItemID  Quantity
103     3
275     2

But what I really want is this:

InventoryID        ItemID        WarrantyDate       Status       Quantity
1                  103           12/22/2010         Available    3
2                  103           05/15/2012         Available    3
3                  103           02/24/2015                      3
4                  275           01/01/2010                      2
5                  275           01/01/2011                      2

Any suggestions/ideas are appreciated.


texts: MySQL Error 1114 "table is full" Using MyISAM Engine

texts:
When executing the following query, we run into the error The table '/var/lib/mysql/temp/#sql_2d22_1' is full (actual table name varies). I've done a fair amount of research on this; most of the answers seem to hinge on there not being enough drive space or details related to the InnoDB engine. However, I've verified that we have (what seems like) enough free space and are using the MyISAM storage engine.

select a.* from some_table a,
   (select Account, Date, Amount, count(*) as Dupes from some_table
    GROUP BY Account, Date, Amount HAVING Dupes > 1) b
WHERE a.Account = b.Account and a.Date = b.Date and a.Amount = b.Amount;

Here are some details:

We're using MySQL version 5.5.36 on a CentOS 6 server with 6GB of RAM
The drive hosting MySQL's temp directory is 120GB in size, about 35 of which are in use
The table has about 70 million records and is about 3.2GB in size
big-tables is set to 1
max_heap_table_size is set to 4096M
tmp_table_size is also 4096M
myisam_data_pointer_size is 5

What could cause this error? I'd very much appreciate any suggestions, as I don't know that much about database configuration and dealing with large tables.

Update: I noticed yesterday that the temp directory does not reach capacity; as the query runs, the directory's size increases slowly by about 200MB and then I get the error.


texts: Recover MySQL database from data folder without ibdata1 from ibd files

texts:
My WAMP directory accidentally get deleted by another user. Only data folder in MySQLis available. And, in that only database-folders (folders in "\bin\mysql\mysql5.6.12\data\" with name of databases) are available. All files including "ibdata1" in root of "\bin\mysql\mysql5.6.12\data\" are also deleted.

The database folders contains files with below extensions only.

*.frm, *.ibd

and "db.opt" file.

How the databases can be recovered?

I already have tried to recover bdata1. But, unable to get it back. And, some database contains MYISAM also.


texts: How to go about modeling the main object of relationship?

texts:
I'm using MySQL and MyISAM tables.

I have a bunch of objects that I want to group together

1 object belongs to 1 and only 1 group
1 group can have many objects
1 group has 1 required main object that i will choose
manually

So far, I’ve build a model that I believe is the right one.

But I don’t know how to go about modeling the main object in the group. What is the perfect solution?

group_object
----------------
group_object_id (PRIMARY KEY)
name

object
----------------
object_id (PRIMARY KEY)
group_object_id (FOREIGN KEY)
name

UPDATED MY QUESTION.. see bold


texts:
What I'm trying to implement is to generate equal-sized time series for each customer. One some days he may have some activity hence some retention value indicator, on other dates he may not hence 0.

CustRetention table is the following:

cust_id, date, intensity
...
1,2018-02-02,4
1,2018-02-06,5
...
2,2018-05-05,7
3,2018-02-06,4

What is the sql to generate equal time series (say for 240 days) for each cust_id?
I tried the following, but it didn't work

 drop table if exists retention_weekly_intensity_balanced;

 create table retention_weekly_intensity_balanced as
 select
 coalesce(t.cust_id, 'unidentified') as cust,
 d.date_actual,
 coalesce(t.size_of_increased_intensity) as size_of_increased_intensity
 from  retention_weekly_intensity t
 left join d_date d
ON
 d.date_actual = t.plaindate
WHERE d.date_actual>='2018-01-01'
AND d.date_actual <= '2018-09-01'
 ;

d_date is a simple calendar table


texts:
I have a read-only database user that I do not want to be able to run the EXEC command. I ran the following:

DENY EXECUTE to db_USERROLE_deny

That completed successfully; however, when I sign in as that user and run the following it runs and gives me output:

EXEC sp_columns table1

How can I deny this user the ability to run EXEC command on the database he connects to?


texts: What program to use to change settings on db servers?

texts:
Having decided to set up a Master-Slave replication I found this article, which seems exactly what I want.

So I fired up the MySQL Console in Navicat to give everything a try, and the commands do nothing. So it seems one of these is the problem:

The MySQL Console is not the tool I want
The MySQL Console on Navicat is imperfect
I am doing it wrong

This is the first step in the article I linked:

  /sbin/ifconfig

Does nothing on the remote server connection (using the Console in Navicat) but works fine on the MySQL Console on the WAMP Server on my local machine.

UPDATE:

next day - clear head - realize what I was trying to do is probably not possible and not necessary. Web site is hosted on some rackspace servers in California, and I doubt they want me messing around with their server settings.

I was trying to set up separate databases so that the write-heavy backend did not interfere with reads in the front end. This morning I reworked the calls which were causing table locks & now the slow backend queries should not lock any table required by the front end.

So basically problem solved. This why I should only post on here in the morning, not after 8-10 hours coding.


texts:
I have a few tables that need to be converted into a single new table.  I'm positive (un)pivot is the solution to the problem.  However, I'm uncertain what the best way is to unpivot data from multiple tables into a single table with a single query.

The tables look roughly like this:

Sales:
Pk, Year, Week1, Week2, Week3,...Week52

Quantity:
Pk, Year, Week1, Week2, Week3,...Week52

Orders:
Pk, Year, Week1, Week2, Week3,...Week52

... And so on

What I want is a table in the format

Aggregate table: Pk, Year_Week, Sales, Quantity, Orders

I've already learned from a previous question (Single year column and multiple weeks columns into single year/week column with PIVOT) how to do this for a single table.  Which looks roughly like this:

SELECT Pk, Sales FROM
    (SELECT Pk, Wk1, Wk2, ..., Wk52
        FROM Sales_Table) source
    UNPIVOT
    (
         FOR Sales IN Wk1, Wk2, ..., Wk52
    ) upt

How can I do this for multiple tables in one query?


texts:
We're currently in the process of moving our hosting services to something which will allow us to scale our infrastructure and requirements.

Essentially we're using MySQL to:

Run our websites.
Run our blog
Store millions of rows which will require some heavy queries, but will be cached afterwards.

Our current idea is to setup two MySQL servers, on a master to master replication, with HAProxy on top to load-balance the requests.

As we've never dealt with replication before our main concerns are the following:

How quickly does replication work?
What happens if one of the servers go down? Would we change HAProxy to use just the one server?
How does replication deal with loading and replicating millions of rows each day? This would happen once a day.

Thanks for any help!


texts: Use expression in window function that references columns from the current row

texts:
Suppose I have the following query that uses window function:

SELECT id
     , var
     , num
     , SUM(var * num) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS calc
FROM (VALUES
    (1, 0.1, 7),
    (2, 0.7, 1),
    (3, 0.3, 9),
    (4, 0.9, 5),
    (5, 0.5, 3)
) AS t(id, var, num)

And the following result:

id | var | num | calc | explanation
1  | 0.1 | 7   | 0.7  | 0.1*7
2  | 0.7 | 1   | 1.4  | 0.1*7 + 0.7*1
3  | 0.3 | 9   | 4.1  | 0.1*7 + 0.7*1 + 0.3*9
4  | 0.9 | 5   | 7.9  | 0.7*1 + 0.3*9 + 0.9*5
5  | 0.5 | 3   | 8.7  | 0.3*9 + 0.9*5 + 0.5*3

Is is possible to reference the var column from the outside inside the SUM() OVER ()? For example:

id | var | num | calc | sum of f(r.var, w.var, w.num)
1  | 0.1 | 7   | ...  | iif(0.1<=0.1,0.1,0.1)*7
2  | 0.7 | 1   | ...  | iif(0.7<=0.1,0.7,0.1)*7 + iif(0.7<=0.7,0.7,0.7)*1
3  | 0.3 | 9   | ...  | iif(0.3<=0.1,0.3,0.1)*7 + iif(0.3<=0.7,0.3,0.7)*1 + iif(0.3<=0.3,0.3,0.3)*9
4  | 0.9 | 5   | ...  | iif(0.9<=0.7,0.9,0.7)*1 + iif(0.9<=0.3,0.9,0.3)*9 + iif(0.9<=0.9,0.9,0.9)*5
5  | 0.5 | 3   | ...  | iif(0.5<=0.3,0.5,0.3)*9 + iif(0.5<=0.9,0.5,0.9)*5 + iif(0.5<=0.5,0.5,0.5)*3

Basically I want to calculate running sum of an expression that references var column in the current row and var column in the current window. iif and multiplication is just an example.

The solution should work in SQL Server but generic answer would be best.

Here is a db<>fiddle where I was able to achieve the result with correlated queries but I want to use window functions.


texts: SQL Server 2012 - How to Remove Partition on Publisher but not Subscriber?

texts:
Is it possible to setup partitioning on a SQL Server 2012 publisher table, so that older data can be removed without affecting the same table on a subscriber table?

Table exists on Database A (SQL Server 2012 - app DB) to store a varchar(max) field that is populated by a web app, and never referenced again. Table is replicated to Database B (SQL Server 2012 - data mining DB) where it will be used for reporting/data mining.

Would like to partition table on both DBs by quarter and only have data from current quarter on Database A (Database B will have historical quarters).


texts:
I am about to write a little program to process database dump files created by mysqldump and I would like to make sure that my expectations about the »anatomy« of mysqldump's output are right. So please check the statements below and correct the if they are wrong.

When dumping text mysqldump uses a single quote (') to wrap the text to insert.*
Inside these quotes mysqldump escapes single quotes, double quotes and Backslashes (',",\) with a Backslash.*
»newline« characters are dumped as: \n, \r\n
the value of each INSERT of a String is only in one line of the dump-file.

*if not specified differently in command-line arguments

In General my problem is that I could not find any documentation or resource about the »anatomy« of the generated output, I know that these are SQL statements, but especially the way String are handled is the part I am interested in, so if there exists any good Stuff read out there dealing with that, please let me know.

Thanks in ahead!


texts: How to use trigger to perform addition from one table columns into another table column where id matches Sqlite

texts:
Below is my SQLite Database with two tables.
import sqlite3

conn=sqlite3.connect('calc.db')
conn.execute("""CREATE TABLE IF NOT EXISTS sum
        (id TEXT UNIQUE,
    name    TEXT,
    total   TEXT
    )""")

conn.execute("""CREATE TABLE IF NOT EXISTS newtable
        (id TEXT,
    name    TEXT,
    num TEXT
    )""")

conn.execute("""INSERT INTO sum(id, name, total) \
    VALUES('001', 'name1', '')""")
conn.execute("""INSERT INTO sum(id, name, total) \
    VALUES('002', 'name2', '')""")
#
conn.execute("""INSERT INTO newtable(id, name, num) \
    VALUES('001', 'name1', '1000')""")
conn.execute("""INSERT INTO newtable(id, name, num) \
    VALUES('002', 'name2', '2000')""")
conn.execute("""INSERT INTO newtable(id, name, num) \
    VALUES('001', 'name1', '4000')""")

conn.commit()
conn.close()

Sum table has unique id and newtable id is not unique. I want to perform addition in sum table in total column that should be taken the addition value from newtable column when inserting and if id matches. Hoe to do. My columns are text type. If not posible in TEXT type i can change it into integer but it is better to continue in TEXT type if possible.  How to do.


texts: MySQL max_connections setting - per client or global?

texts:
So I just need to clear this up.

For a test, I set max_connections=10 in my.cnf.

I then ran 10 scripts concurrently that each take over 20 seconds to finish.

I monitored my threads and noticed that when I hit 10 the Too many connections error fired.

That's fine, but I'm only one person. If there were multiple clients connected would they have 10 connected threads each, or between them?

Thanks


texts: SQLServer - Detect who is connecting via an AG listener

texts:
We have multiple generations of applications connecting to SQL Server, some of which we know [their connection libraries] do not support using the MultiSubnetFailover=True connection parameter. Fine.

For the apps that "say" they are capable of using the AG listener, is there any way to verify this from the SQL side once they are connected? Unfortunately, our role (DBAs) does not grant us (nor would we want) the liberty of validating all kinds of application strings to ensure they configured it correctly. These older generation applications have historically used the literal server name, but we're trying to migrate all over to listeners.

First post; please let me know what info I might be missing from my question. Thanks!


texts: Infinite scrolling with full text search in PostgreSQL

texts:
I am building a service that allows full-text searches and I would like to paginate the results.

I don't want to use OFFSET, so I opted for infinite scrolling with the keyset pagination, as outlined here.

My search query looks like this:

SELECT *, ts_rank_cd(t.tsv, plainto_tsquery($1)) AS rank
  FROM profiles AS t, plainto_tsquery($1) AS q
  WHERE (tsv @@ q) AND
    (rank, n, id) < ($2, $3, $4)
  ORDER BY rank DESC, n DESC, id DESC
  LIMIT 50

The tsv column contains tsvector objects and it is indexed with GIN. The search results are first ordered by rank, which is computed by PostgreSQL, and then by another column which is not relevant here and finally by id.

It seems correct to me, but I get this error: column "rank" does not exist. It works if I replace rank with its expression in the WHERE clause. Will that impact performance?

A second question: how can I make keyset pagination work if I wanted to sort id ASC instead of id DESC? I cannot split the expression in the WHERE clause like this (rank, n) < ($2, $3) AND id > $4 because then I lose the ordering priority (id must be used only to break ties).


texts: My Procedure isn't working right. It keeps giving me an error

texts:
create or replace PROCEDURE CHECK_BLACKLIST
(
  P_EMPLOYEE_USERNAME IN VARCHAR2
, P_ROWCOUNT OUT NUMBER
) AS
BEGIN
  SELECT COUNT(*) INTO P_ROWCOUNT FROM boe_safegaurd WHERE UPPER(employee_username) = UPPER(P_EMPLOYEE_USERNAME);
END CHECK_BLACKLIST;

Errors

I entered joe@example.com when I ran the procedure.

ORA-06550: line 5, column 26:
PLS-00352: Unable to access another database 'example.com'
ORA-06550: line 5, column 26:
PLS-00201: identifier 'joe@eample.com' must be declared
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored
Process exited.

when I enter "joe@example.com", I get a similar error

ORA-06550: line 5, column 27:
PLS-00201: identifier 'joe@eample.com' must be declared
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored


texts: Are stored procedure query texts stored in any tables?

texts:
I don't readily see anything in the sys schema objects, other than the DM_EXEC_SQL_TEXT function that requires a plan handle, but the procedures I'm trying to get their query texts from aren't currently running. (Maybe there's a way through their cached plans?...though the ones I'm looking for might not have a cached plan.)

Are the query texts of stored procedures stored anywhere that's queryable?

I see sp_helptext might be an option: https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/view-the-definition-of-a-stored-procedure?view=sql-server-ver15#TsqlProcedure


texts:
I have dedicated server, CPU is too high (80%-100%) and memory only use 9gb

RAM 32 gb and CPU Dual Xeon E5-2670(8core and 16 thread)

I have 25000 users that use my android application

My database table only innodb. What is best config?

sorry for bad English

mysql5.7


texts:
This is what I typically use to import csv data into my postgres database..

\COPY master_list FROM '/root/raw/file.txt' DELIMITER ',' CSV;

Usually it's into a blank table, but in this case, I need to append the data into a table with existing data.

Will that same command suffice, or should I make changes since it's an append?


texts:
I try to take a backup of a huge MySQL database via mysqldump. The database has millions of rows with a total size of 1.3TB.

CREATE TABLE PageContents
(
ID int(11) unsigned,
Added date,
Text MEDIUMTEXT,
FOREIGN KEY(ID) REFERENCES AllPages(ID) ON DELETE CASCADE,
PRIMARY KEY(ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci KEY_BLOCK_SIZE=1;

but I receive an error after 8 hours of dumping:

mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `PageContents` at row: 20946782

I had increased max_allowed_packet in mysql.cnf

max_allowed_packet  = 16G


texts: Merge join turns to hash match when adding a 2nd join

texts:
I would like to educate myself on what goes on under the hood in SQL Server, so I'm about to delve into Grant Fritchey's book Dissecting SQL Server Execution Plans. As it's 181 pages I just wanted to ask this simple question, which is what first got me interested in this in the first place - hopefully it will pique my interest (plus I'm too impatient to wait until I've waded through this tome to get some kind of answer!)

I am using this miniature version of Northwind to run this simple query:

SELECT Orders.OrderID
  ,Orders.OrderDate
  ,[Order Details].UnitPrice
FROM Orders
JOIN [Order Details]
  ON Orders.OrderID = [Order Details].OrderID

Which gives the following execution plan:

Which seems sensible, given both tables have indexing / sorting on the joining fields. But if I add another table (products) like so:

SELECT Orders.OrderID
  ,Orders.OrderDate
  ,[Order Details].UnitPrice
  ,Products.ProductName
FROM Orders
JOIN [Order Details]
  ON Orders.OrderID = [Order Details].OrderID
JOIN Products
  ON Products.ProductID = [Order Details].ProductID

Suddenly I have Hash matches. This seemed odd to me (remember I'm just beginning to learn this stuff!), as I thought hash matches were for large, unsorted joins. Why would SQL think one join type is okay in the first query and not the second, even though surely they are joining the same number of rows / same indexes etc between Orders and Order Details in both queries?

Orders has 830 rows, Order Details 2155 and Products 77.

OrderID has a clustered indexes on Orders and Order Details, and ProductID has a non-clustered index on Order Details and clustered on Products.

Thanks


texts: What is the syntax to add a constraint on an already-created table?

texts:
PostgreSQL exclusion constraints are documented briefly under

DDL-Constraints
CREATE TABLE .. CONSTRAINT .. EXCLUDE As linked in ddl-constraint

But, the syntax for ALTER TABLE does not mention /EXCLUDE/ anywhere. How do I add one if one is not already present.

I can in psql, TAB-complete

ALTER TABLE foo ADD

However subsequent tabs do not provide CONSTRAINT which the documentation mentions nor anything that comes after.


texts:
I am using bcp to export query results to CSV file. The query works perfectly as below:

declare @sqlcmd varchar(6500)

set @sqlcmd='bcp "SET QUOTED_IDENTIFIER OFF SELECT * FROM OPENQUERY(LinkedServerName, '
           +'''SELECT [column1] FROM Hist '
           +'WHERE DateTime >= DateAdd(hh,-24,GetDate()) '
           +'AND DateTime <= GetDate()'')" '
           +'queryout C:\Temp.csv -T -c -t,'

exec xp_cmdshell @sqlcmd

After I added quotename function to column1, the below query doesn't work:

declare @sqlcmd varchar(6500)

set @sqlcmd='bcp "SET QUOTED_IDENTIFIER OFF SELECT * FROM OPENQUERY(LinkedServerName, '
           +'''SELECT quotename([column1]) FROM Hist '
           +'WHERE DateTime >= DateAdd(hh,-24,GetDate()) '
           +'AND DateTime <= GetDate()'')" '
           +'queryout C:\Temp.csv -T -c -t,'

exec xp_cmdshell @sqlcmd

Result:

Error Message Incorrect syntax near '('

I would appreciate it if anyone can give me some advise. Thanks


texts: What are the type of drivers for Communication with Java?

texts:
I am developing one application using core java and Mysql Database.
As i read in Most of the site, they suggest that use type 4 driver for communication using JDBC.

In JDBC Connection i have use mysql-connector-java-5.1.24-bin.jar from Connector J 5.1.20.0 folder of Mysql 5.6.

My questions are.

1) mysql-connector-java-5.1.24-bin.jar this one is which type of Driver.?

2) I want to use only for development purpose not to deploy so which one more suitable for me?

3) from where i'll get that drivers for development?


texts: Real-time merge of two databases on different servers

texts:
One of the requirements our customer has given is to merge two databases running on two different machines in real time. This means that we have to build some sync policy (or use SQL Server tools to manage that). But I have some doubts:

How can I be sure that my databases are totally merged? I mean, we are in a real time environment with n/sec web and local requests, it's impossible to share table updates/merges with the same speed.
Is this a good architecture in spite of a master/slaves one (with an unique DB) topology? I'm pretty sure sync both databases each other will cost too much since our tables store a lot of data in terms of rows and information.
Can anyone provide a good solution to build the best architecture possible?


texts: Error in partition switching in SQL Server 2014 when migrating a database from SQL Server 2005

texts:
I am migrating an old database from SQL Server 2005 to SQL Server 2014. This database has a partitioned table; a scheduled job periodically moves old data from the "online" table to the the historical table.

The job fails when I try to move the partition with the following code:

ALTER TABLE Warehouse.TBW_MY_DATA
SWITCH PARTITION 1
TO Warehouse.TBW_MY_DATA_STAGING

Here is the error message:

  Msg 4939, Level 16, State 1, Line 4
  ALTER TABLE SWITCH statement failed. index 'MY_DATABASE.Warehouse.TBW_MY_DATA_STAGING.PKW_MY_DATA_STAGING' is in filegroup 'FG_EDRN_2009123100085' and partition 1 of index 'MY_DATABASE.Warehouse.TBW_MY_DATA.PKW_MY_DATA' is in filegroup 'FG_EDRN_2007033100002'.

Actual screenshot of the error:

MY_DATABASE.Warehouse.TBW_MY_DATA_STAGING.PKW_MY_DATA_STAGING and MY_DATABASE.Warehouse.TBW_MY_DATA.PKW_MY_DATA are the clustered indexes of the primary keys.

What I tried:

To isolate the problem I run the job with different compatibility levels.

The error only occurs using compatibility level 120 (SQL Server 2014). Everything works fine with compatibility level 100 and 110 (respectively SQL Server 2008 and SQL Server 2012).
So probably something changed in partition handling from SQL Server 2012 to SQL Server 2014.
I read the article about "Transferring Data Efficiently by Using Partition Switching" on TechNet, but no deprecation date/version is ever mentioned even if I noticed that the article is available only for SQL Server 2005 and 2008.
I checked the list of deprecated features and the list of breaking changes in SQL Server 2014, but I found nothing concerning partition switching.


texts: Where can I read database compression properties after the creation of an oracle database / tables? Is there a special view where the info is stored and can be retieved?


texts:
I have a trouble with the following CTE query:

  WITH RECURSIVE najdi_tot(sj) AS (
    SELECT nad FROM s_vztah WHERE pod=581
    UNION
    SELECT pod FROM s_vztah WHERE nad=581
    UNION
    SELECT sv.nad FROM najdi_tot n, s_vztah sv
    WHERE sv.pod = n.sj
    UNION
    SELECT sv.pod FROM najdi_tot n, s_vztah sv
    WHERE sv.nad = n.sj
  )
  SELECT array_agg(sj) FROM najdi_tot

Following error occurs:

ERROR:  recursive reference to query "najdi_tot" must not appear within its non-recursive term
LINE 6:     SELECT sv.nad FROM najdi_tot n, s_vztah sv
                               ^

I made a google search ( postgresql "recursive reference to query" "must not appear within its non-recursive term" ) and found nothing but maillists where I failed to find the solution (perhaps it is there, but hidden in discussion).

What should my query do is to find all rows linked from a table connecting two rows in the same table (nad and pod).


texts: How to index of filter extended event files in Sql Server

texts:
I am collecting errors data using the error_reported event and saving the data on XEL files. The problem is that there are hundreds of thousands of errors over there and querying them can take a lot of time.

One option is to prevent errors from being logged twice if they happened on the same object in the same timestamp. I really don't think the second option is possible.

Second option is to index the file in a way that it will be quick to filter errors by the error text and the object that caused them.

So far I thought of creating a separate process that will run in the background and writes filtered values from the files to an indexed table, but its a bad solution. That process would need maintenance and could be expensive on IO resources.

So far I didn't find a way to solve it on the extended event level.

Does anyone have a better idea?


texts: SSMS 18.0 switching between tabs problem

texts:
Does anybody have the same problem with new SSMS 18.0?

I have opened more tabs with some code inside. When switching between tabs the window (code inside the window) is rendered slow and the code is mostly overlaped with a code from previous tab. See image for example:

I wonder whether it's just mine or general bug? Where would you recommend me to report this?

UPDATE

You can vote for this to be repaired at Azure Feedback site here: https://feedback.azure.com/forums/908035-sql-server/suggestions/37474042-ssms-18-0-redraw-issues-when-switching-between-mul


texts: How to cope with SQL Server 2012 vs SQL (Azure) Database sql files

texts:
I'm trying to port my system to SQL Database (Azure instance) from SQL Server 2012. I'm using Visual Studio 2013 and I have my .sqlproj with all the definition of my database inside.

Given that a number of SQL statements are not available in SQL Database (like "ON [PRIMARY]", filegroups, etc.), I should change a huge number of *.index.sql , *.pkey.sql, *.table.sql files. Unfortunately I still need to cope with SQL Server 2012 installations (some customers are still on that infrastructure), so I would like to have instead a simple way to switch between the "SQLAzure" vs. "OLD-2012" syntax.

E.g. something like Compilation Symbols would be useful, or similar tricks.

Anyone has a brilliant idea on how to manage such an issue?

Thank you very much!

cghersi


texts: Oracle losing connection ( ORA-03114 ) after some time not using the software

texts:
After some time idle, if users try to use the our system, we receive the error:

ORA-03114 - not connected to Oracle.

I know there's a parameter called SQLNET.EXPIRE_TIME.

the recomendation is SQLNET.EXPIRE_TIME=10 but I didn't find what this 10 means.

I'm a SQL Server DBA trying to help our Oracle senior dba.

Our sqlnet.ora file is like this:

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES = (NTS)

Shoud I add **SQLNET.EXPIRE_TIME=10** on it?

To simulate the error, if we open the software and leave it idle for 1hr, we receive the error.

IDLE_TIME is unlimited too on dba_profiles.


texts:
I'm looking into accepting an API token from a user, and I would like to prevent timing attacks. The conventional wisdom seems to be that one should hash the token server-side in order to make the comparison non-deterministic. If you have some other identifier you can look up the record by that and then do a constant-time in-memory comparison, as with Rails secure_compare. In my case, though, I was planning on using the token to look up the user. I'm wondering if, by any chance, Postgres has some facility for doing constant-time comparisons when looking up records. Something that might look like:
SELECT * FROM users WHERE secure_compare(token, 'abcdef')


texts: Building a custom SQL Server 2017/2019 container on a windows server without internet

texts:
I am in bit of a predicament,and would appreciate any help to move forward from my current situation
I have been given a VM running on Windows Server 2016 with Docker Enterprise installed. My management wants to have SQL Server 2019 with docker on windows containers running within this VM. This is a POC project. The VM does not have any internet connection, and therefore, I am unable to pull the base image from the docker hub. I know that there are SQL Server 2017 developer editions available. What can be done to make sure that I can go ahead with having a container in this situation? Especially when the VM does not have any internet connection?
What I tried doing was to download a SQL Server 2017 Developer edition on my local machine running Windows 10Pro, and then moved the container image onto the VM. But when I tried to load the container, I got an error message saying that "Host OS does not match". That is when I found the website below
https://docs.microsoft.com/en-us/virtualization/windowscontainers/deploy-containers/version-compatibility?tabs=windows-server-2004%2Cwindows-10-2004
Please suggest as to how I can create an  image in such a situation


texts: Fastest way to insert 30 thousand rows in SQL Server

texts:
I have to insert 30000 ids in a temporary table ##MyTempTable that has only one column (ID int) and I would like to know which way I could do it fastest.

I have tried 30000 normal inserts

insert into ##MyTempTable (ID) ...

I have tried

insert into ##MyTempTable (ID) (
 select 1002000 union all
 select 1002001 union all
 select 1002002 ...

The fastest way so far has been:

insert into ##MyTempTable (ID)
values
(1002000),(1002001),(1002002),(1002003) ...

Because this type of insert only accepts a maximum of one thousand rows, so then I repeated that insert 30 times.

Would you be able to help me?