texts: Why can't I view the plan for view creation...?

texts:
mysql> EXPLAIN EXTENDED
    create algorithm =temptable view fisgen_temptable(inter,phone)
    as select intercom1,phone from fisgen where fid='f105';

I am getting an error like this:

  ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
  corresponds to your MySQL server version for the right syntax to use near 'create
  algorithm =temptable view fisgen_temptable(inter,phone)
  as select interc' at line 2


texts:
I cause a 2016 Server Standard Database to AlwaysOn Auto failover (its a test DB single table), I have to wait up to 60 seconds to connect again via the listenser to the standby.

No data is lost. But 100% up time is not.

What can I tweak to speed it up?


texts: How to model a relationship with different roles of users

texts:
I am creating a new project and currently modeling the relevant business domain. I am hesitating to choose one solution over another.

Consider the following case.

There is an order, the order has a customer who made it, an order will have an assigned manager after some state changes and next order will be assigned to a delivery guy.

I don't know how to model this relationship, not strictly in SQL, but in general from the business domain perspective.

I have tried to trace these changes, however figured out that any redux dispatch action causes this issue.

We can have "fields" customer_id, manager_id, courier_id in the order "model" (table).
Use pivot tables with additional information of relationship type

order_id = 31
user_id = 22
rel_type  = "customer"

This will be quite tricky to query such a relationship.

Have multiple "pivot" tables like customer_order, manager_order, courier_order.

Please suggest the best option to choose for such a business domain.

Thanks.


texts: Can't create indexes on really large table!

texts:
I have a table with over 300 Million rows in it.  When I try to create an index on the table I get the error message below.

  Msg 1105, Level 17, State 2, Line 3 Could not allocate space for
  object 'dbo.SORT temporary run storage:  422212472471552' in database
  'MSD' because the 'PRIMARY' filegroup is full. Create disk space by
  deleting unneeded files, dropping objects in the filegroup, adding
  additional files to the filegroup, or setting autogrowth on for
  existing files in the filegroup.

When I look at the files, the one using the PRIMARY file group is set to grow by 10% and is unrestricted for max size.  My hard drive still has 500 gigs of space left on it.  Does anyone know why I am hitting this problem and how I can get around it?


texts: check for dependencies on other servers in SQL server 2000

texts:
I have an old server running SQL Server 2000.

I need to perform some maintenance on the server and one step is to check for dependencies on other servers.

The SQL Server 2000 machine has database A, while database B is running on a different server.  DB A has stored procedures that will modify some table fields on DB B and vice-versa.  I want to know what cross DB/Server references there are, what should I do?


texts: return continuous set of IDs based on two other columns

texts:
Given a table such as the one below, how can I ask PostgreSQL to return the first set of n rows in which one column has a certain value (code=0) and the values of the second column (named segment) are all the same, and the sequence of IDs is not broken.

For n=4, the result should be id's 12, 13, 14, 15 (i.e. the first four id's where code=0 and the values of segment are the same - in this case 2).

CREATE TABLE foo
AS
  SELECT *
  FROM ( VALUES
    (  1, 0, 0 ),
    (  2, 0, 0 ),
    (  3, 0, 0 ),
    (  4, 0, 1 ),
    (  5, 0, 1 ),
    (  6, 0, 1 ),
    (  7, 1, 1 ),
    (  8, 0, 1 ),
    (  9, 1, 2 ),
    ( 10, 0, 2 ),
    ( 11, 1, 2 ),
    ( 12, 0, 2 ), -- HERE
    ( 13, 0, 2 ), -- HERE
    ( 14, 0, 2 ), -- HERE
    ( 15, 0, 2 ), -- HERE
    ( 16, 0, 3 )
  ) AS t(id,code,segment);


texts: Having two data folder in my postgresql setup- Postgresql

texts:
I am using centos 6 and installed Postgresql_9.4 using the following commands

yum  install postgresql postgresql-contrib postgresql-client pgadmin3
yum install postgresql-server

After that i verified my config file or "Show data_directory" command to verify that data folder path.
Its showing /var/lib/pgsql/9.4/data but am also having the another data folder in this location /var/lib/pgsql/data.

data path 1 --> /var/lib/pgsql/9.4/data

data path 2 --> /var/lib/pgsql/data

My question is which is my original data folder ??.
And Also my exact data folder is configured in config file means what is the use of another data folder ?


texts: "Access denied for user" error when creating a procedure

texts:
When I request my database server (MySQL), with "show grants for current_user"

I notice that I have the grant to execute procedure:

SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, LOCK TABLES, EXECUTE

But when I execute a sql file that contains those instructions :

DROP FUNCTION IF EXISTS
DROP FUNCTION IF EXISTS
DROP PROCEDURE IF EXISTS
CREATE FUNCTION
CREATE PROCEDURE
DECLARE
CALL

I have this error :

ERROR 1044 (42000) at line 16: Access denied for user

The line 16 contains : CREATE FUNCTION function_name( .. )

Should I have other grants ?


texts: update one column based on query containing another (joined) table

texts:
Consider the following select query:

SELECT s.id, s.event, s.column, e.oldcolumn
FROM section as s
INNER JOIN event as e
ON s.event = e.id
WHERE s.id = 1

Now I wish to update the section table so that the values are equal to the oldcolumn from the event table.

I tried:

UPDATE public."section"
SET public."section".column = public."event".oldcolumn
FROM public."section"
INNER JOIN public."event"
on public."section".event = public."event".id
WHERE s.id = 1

However this, on postgresql, returned the following error:

ERROR:  table name "section" specified more than once
SQL state: 42712

How would I do such a thing, using joins inside an update query?


texts:
Following the execution of

pg_dump -Fc

the follow-up pg_restore is generating errors.

Under

pg_restore -c -v -1 -d

the following error is generated Error while PROCESSING TOC: The motive is:

relation "public.channels_structures" does not exist
Command was: ALTER TABLE ONLY public.channels_strutturas DROP CONSTRAINT fk_rails_fed130752f; (under constraint "FK CONSTRAINT fk_rails_fed130752f")

The upstream rails application initially had an HABTM (has_and_belongs_to_many) relationship set up, which was then modified to has_many through and the join table explicitly created a channels_structures table - I've steered away from relying on HABTM for convenience and future potential reasons.

Probably iterated with some pg_dump in incorrect manner and left some pollutant lying around.

It would be preferable to clean up the database.  How can the clean-up of the DB be accomplished? the "channels_structures" seems to be malformed.
a pg_restore without "clean" could be run. wWhat pitfalls could that entail aside from the above type of case?

Update

pg_restore -c -d database_name -Fc local_file.sql has however executed succesfully.

So the questions change to a single one [sorry!] and I feel it is best posted here:

verbose and 1 (run in a single command) do not appear to be
heavy culprits. d database name even less so.  So what magic is
there when stating the Format is custom when the manual
explicitely states It is not necessary to specify the format, since
pg_restore will determine the format automatically


texts: Is it safe to copy the binlogs from a running mysql server

texts:
I want to backup a tokudb database.

I want to be able to restore it to the most recent state.
Which I am writing to a file at backup.

My guess would be to run something like this

mysql -e "SHOW MASTER STATUS; SHOW SLAVE STATUS; FLUSH LOGS" > mylogposition.log

Which will rotate the log files after saving the position?


texts: When to Converge Dimensions in a Data Warehouse When There Are Few Common Attributes

texts:
I have two StudentDetails dimension tables in my data warehouse and I want to know whether I should converge them, considering that they have limited attributes in common (keeping in mind Kimball DW design principles).

Table 1 is called DimStudentDetails and virtually all of its data comes from an ETL from another small departmental database. Table 2 is called DimUniStudentDetails and all of its data comes from an ETL from a large enterprise wide system (I work in a large university).

There are a very large number of students in Table 2 that don't exist in Table 1. There are a small number of students in Table 1 that don't exist in Table 2.

There is no current attribute cross-over between the tables, except for a common identifier for many students in Table 1 that also exists in Table 2. If I converge them as they stand presently, there would be a large number of Unknown or NA text values in the table.

Most of my fact tables do not require access to the two different data sets. One of my fact tables does. Presently, I would have to have two different StudentDetails FK relationships for this fact table - this feels wrong.

Equally, it feels wrong to have a dimension table where most of the attributes have Unknown or NA values.

It would be much appreciated if someone could advise me based on their experience of DW projects whether converging fairly disparate dimensions (in terms of attributes), albeit conceptually similar ones (things about students), makes sense. Should conceptual purity be promoted in spite of a somewhat unintuitive result?


texts:
I have enabled Query Store on a database. I have a specific query which I want to track. I have a lot of details about the query from sp_BlitzCache (like SQL Text, SQL Handle, SQL Hash, Plan Cache Handle/Hash, etc...).

Am I able to search Query Store with the info from sp_BlitzCache to track down the query there? I want to force a specific execution plan because the query suffers parameter sniffing issues.


texts: Snowflake/S3 Pipeline: ETL architecture Questions

texts:
I am trying to build a pipeline which is sending data from Snowflake to S3 and then from S3 back into Snowflake (after running it through a production ML model on Sagemaker). I am new to Data Engineering, so I would love to hear from the community what the recommended path is. The pipeline requirements are the following:

I am looking to schedule a monthly job. Do I specify such in AWS or on the Snowflake side?
The monthly pulls should get the last full month (since this should be a monthly pipeline).
All monthly data pulls should be stored in own S3 subfolder like this query_01012020,query_01022020,query_01032020 etc.
The data load from S3 (query_01012020,query_01022020,query_01032020) back to a specified Snowflake table should be triggered after the ML model has successfully scored the data in Sagemaker.
I want to monitor the performance of the ML model in production overtime to catch if the model is decreasing its accuracy (some calibration-like graph perhaps).
I want to get any error notifications in real-time when issues in the pipeline occur.

I hope you are able to guide me on what components the pipeline should include. Any relevant documentation/tutorials for this effort are truly appreciated.

Thank you very much.


texts:
I have these 2 queries below that I use to calculate net profits from Jan to Dec.

Query 1

/* Profit for the Period 13 */
SELECT * FROM
(
SELECT MONTH(T0.RefDate) AS Month, '311999' AS 'SAP Code', 'Period Profit' AS 'Description', SUM (T0.[Credit] - T0.[Debit]) AS 'Amount',
'311200' AS 'FatherNum', 12 AS 'Group', 'Liabilities' [Liabilities]
FROM JDT1 T0 WHERE (T0.RefDate BETWEEN DATEADD(yy, DATEDIFF(yy,0,'20151231'), 0) AND '20151231')
AND T0.[ContraAct] <> '311201'
GROUP BY T0.RefDate) AS q
PIVOT
(
SUM(Amount)
FOR [Month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS query

Results:

Query 2

/* Profit for the Period 13 */
SELECT * FROM
(
SELECT MONTH(T1.RefDate) AS Month, '311999' AS 'SAP Code', 'Period Profit' AS 'Description', SUM (T1.[Credit] - T1.[Debit]) AS 'Amount',
'311200' AS 'FatherNum', 12 AS 'Group', 'Liabilities' [Liabilities]
FROM OACT T0 INNER JOIN JDT1 T1 ON T0.[AcctCode] = T1.[Account]
WHERE (T1.RefDate BETWEEN DATEADD(yy, DATEDIFF(yy,0,'20151231'), 0) AND '20151231')
AND T0.GroupMask > 3
GROUP BY T1.RefDate) AS q
PIVOT
(
SUM(Amount)
FOR [Month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS query

Results:

The difference between the 2 comes in month 12. Query 1 accurately gets the Dec profit because it is an account that stores data after a process of closing the 2015 year in December.

How do I combine the two into one query such that I get all results of query 2 except for month 12 instead of -4764758.550000 I get 3280335.630000?


texts: What happens when using SCCM I deploy SQL server patch to a server with a different SQL version?

texts:
I have about 80 SQL servers that I want to patch. I have decided to use SCCM to get this done by preparing the following:

Create a device collection with all SQL servers.
Create a software update group with all the SQL patches from SQL server 2008 to 2019

Now I want to deploy the updates from the update group to the device collection. I want to know if this will cause an issue. For instance, will SQL server 2019 updates attempt to get installed on SQL server 2012 server given that it is within the device collection?


texts: How to limit inserts to one row per user per month?

texts:
I would like to make a monthly contest. I have Users and Products. Each user can vote for products he/she likes the most this month. My vote table looks like this:

+---------+-----------+------------+
| user    | product   | date       |
+---------+-----------+------------+
| 1       | 1         | 2018-09-03 |
| 1       | 2         | 2018-09-03 |
| 1       | 2         | 2018-08-28 |
+---------+-----------+------------+

A user can like the same product but not in the same month.

My problem is that I can't figure out a valid Primary Key to prevent duplicate votes.

If I use PK (user,product), a user cannot vote more than one time for the same product. If I use PK (user,product,date), a user can vote the same product once a day.

How can I prevent duplicate votes within the same month?

Note: date should be a valid DateTime column.


texts: MySQL: So many prepared statements in general log file

texts: One of my clients complained serious performance issues in a web-application written in Java. I was suspecting that there might be some unoptimized queries which are causing the problem. I've enabled mysql general logging to figure out what queries are running behind a problematic application function. There were around 11840 'Prepare' statements in the log whereas 'Execute' statements were only 9 in number - I couldn't understand what does this mean, can somebody assist? Thanks


texts: Cannot connect from computer to server database

texts:
I'm trying to connect from my computer to a server which has a SQL Server 2008 instance installed. My computer and the server are in the same domain and the name of the server is "SERVER". The instance name is "SZSQL". If I try to connect on the server in SQL Management Studio 2008 I'm able to login using instance SERVER\SZSQL, user sa and the admin password. When I try using these login data in MS Visual Studio 2012 Server-Explorer, I get an error (translation below):

In English:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified).

Like I told you before, the instance name, username and password are correct when I try to login on the server itself. Just the connection from my computer to the server is not working.

Any suggestions? :)

EDIT: The Server is running on Windows Server 2008 Standard


texts:
I'm trying to find a good alternative to SQL Developer for performing queries and scripts on Oracle instances.

I find DBeaver to be an interesting tool, it has a lot of great features, BUT it seems to lack an essential element: The possibility to launch scripts from the filesystem of the client machine in a batch-like way.

For example, in SQL Developer I simply put a file called delta_script.sql that contains:

@script1.sql
@script2.sql
@script3.sql

I execute it as a script and the software executes all of the files in the correct order (provided that they are in the same directory as the delta_script.sql file).

On DBeaver, this does not happen, instead I get a [900] ORA-00900: invalid SQL statement..

How can I manage this to work as expected?

I have tried the following in the delta_script.sql file with no luck:

@@ scriptN.sql
@/tmp/folder/scriptN.sql
@"/tmp/folder/scriptN.sql"
@"scriptN.sql"

They're all giving the same error, so I think there's a problem with the "@".
I have tried from SQLPlus with the synthax I use on SQL Developer without errors.


texts: Why is my SQL statement sometimes slow and sometimes fast?

texts:
I'm using MySQL version 5.5.45 on Windows Server 2016.  I'm executing the following statement in MySQL Workbench version 6.3.

delete FROM childattendance WHERE date < DATE_SUB(NOW(),INTERVAL 2 YEAR) limit 100000;

Essentially I'm deleting any records older than 2 years. I'm not worried about which records get deleted as long as they're older than 2 years.  I actually want to delete all records older than 2 years but I'm limiting to 100,000 rows at a time so that the statement actually finishes before losing connection.

The weird thing is that sometimes this statement times out, and other times it executes within a few seconds.

See this screenshot for the result, running the same query five times:

The first time it ran for 11 seconds before losing connection (I don't know what's causing that either) and the second time I ran it and it executed successfully in 2 seconds.  Ten minutes later I ran it again, failing twice and succeeding once in between.

I've run the same statement limiting to 30000 rows and had the same issue.  Sometimes it goes for 11 seconds and times out.  Other times it executes in 2 seconds.  And I've seen the same thing with other queries.

Coincidentally mysqldump also loses connection to the server when trying to backup this very same table, although it lasts 13 seconds before dying, and it happens 100% of the time.  I don't know if that's related.

This table has approximately 6.4 million rows in 471 MB.

What causes this?


texts: In which order does a SQL Replication write the data

texts:
I use a unidirectional Replication of a database.
In the Database I have a column with the datatype "timestamp" (not datetime).
When the replication is initiated it writes table after table to the subscriber.
My question is: Is the data within a table writen to the subscriber in the same order as it was created in the published database?
So is the timestamp in a table of the subscriber also usable as a sort order as it was on the publisher e.g. for last inserted and updated records? Becouse unfortunatly I do not have a column like EditDateTime.


texts: MongoDB Disk Usage with Capped Collections

texts:
I am using MongoDB using C#.

I am  currently using only a single server.

Since I have to create Table events for MongoDB collections I have created a  single replica set(primary). And using oplog.rs table.

I have created only one Database :  MyDatabase
I also have a capped collection in this Database, and some 5 or 6 tables with very less data.

The Issue here MongoDB size become so huge. There is not much data present , the  data wont be more than 500MB but my MongoDb took 20GB data of my Harddisk.

When I checked inside data\db folder

there is 6 copies of my database MyDatabase has been created automatically. Each of which consuming 2GB

MyDatabase.0
MyDatabase.1
MyDatabase.2
MyDatabase.3
MyDatabase.4
MyDatabase.5

and

local.0
local.1
local.2
local.3
local.4
local.5

In the Overview it shows understorage
the oplog allocated 8.5GB
and the capped collection 4.7 GB

The confusion here is No data more than 500MB is present in the Database but still mongoDB took 20GB.

Am I doing something wrong here with the settings. Please help me in this

Thanks


texts: How can I bring a database offline safely?

texts:
Recently I took the database offline with the option Drop all active connections checked.
After that I couldn't bring it back online due to error Could not redo log record for transaction id on page database. I was able to recover the database with CHECKDB but I want to avoid such a scenario in future
As far as I understand an uncommitted transaction was interrupted which corrupted databases's integrity.
I wonder how can I disable new upcoming connections while letting the active transactions to finish?


texts: MongoDB insert performance rapidly dropping

texts:
I'm currently experiencing a rapid decrease of the insert Performance.

I have a rather simple data model

{
    "Id": "ObjectId",
    "SomeProp": "xy",
    "SomeTags": [
        {
            "key": "x",
            "value": "y"
        }
   ]
}

Indices are created on of course the mongodb ("Id"), ("SomeProp") Ascending and ("SomeTags.key", "SomeTags.value") Ascending. The two custom indices also have background:true.
(Also tried with Background:false but got worse results)

I use the new unordered bulk api to perform the inserts, this seemed to be slightly faster than batchinsert.

At the beginning (e.g. the first 100M rows) it'll insert constantly ~20-30k/sec. But then the speed suddenly drops to below 1k/sec.

Is there anything I can do, to keep the speed constant?

Below you can find the stats from MMS.

View Large

View large


texts: MySQL trigger - delete data from multiple tables

texts:
I would like to delete data in multiple tables, from a trigger but it seem like my syntax is incorrect.

I got the error: "you've got a error in your MySQL syntax please check the manual".

This is the SQL for the trigger:

CREATE TRIGGER `delete_organisation`
BEFORE DELETE ON `organisations`
FOR EACH ROW
DELETE FROM contacts WHERE OLD.id = organisation_id ;
DELETE FROM orders WHERE OLD.id = orders.organisation_id;
DELETE from organisations_products where OLD.id = organisation_id ;
DELETE FROM organisations WHERE OLD.id = organisations.parent ;

Any idea what I'm doing wrong?


texts: Delete rows from report based upon specific field value

texts:
I have found how to hide rows based upon a specific field value.
But is it possible to actually delete the rows from the report and not just hide them?
Steve


texts: SQL Server Concurrent Updlock Serializable vs Try Catch

texts:
We import multiple records per second from different flat files. Sometimes we encounter a racing condition, and duplicate Unique error constraint. We are inserting and retrieving records,

I hear there are two methods to handle this.
Which is the better way, I heard UPDLOCK, SERIALIZABLE is the standard way. However, try catch prevents checking the additional If statement. Are both ways full proof, and will stop duplicate inserts? What is best coding practice wise, and performs better?

CREATE TABLE dbo.Customer
(
    RowId bigint IDENTITY(1,1) NOT NULL,
    CustomerId guid NOT NULL,
    Name varchar(255) NOT NULL,
    CONSTRAINT PK_RowId PRIMARY KEY CLUSTERED([RowId] ASC)
)
create unique nonclustered index [UN_CustomerId] ON [dbo].[Customer] ([CustomerId] ASC) include (Name)
create nonclustered index [UN_Name] ON [dbo].[Customer] ([Name] ASC) include (CustomerId)

Method 1:

IF NOT EXISTS
(
    SELECT *
    FROM dbo.Customer WITH (UPDLOCK, SERIALIZABLE)
    WHERE Name = @Name
)
BEGIN
    INSERT INTO dbo.Customer(CustomerId, Name) VALUES (@CustomerId, @Name)
    SELECT @CustomerId
END
ELSE
BEGIN
    SELECT CustomerId FROM dbo.Customer WHERE Name = @Name
END

Method 2:

BEGIN TRY
    INSERT INTO dbo.Customer(CustomerId, Name) VALUES (@CustomerId, @Name)
    SELECT @CustomerId
END TRY
BEGIN CATCH
    SELECT CustomerId FROM dbo.Customer WHERE Name = @Name
END CATCH


texts: How to handle a results in a field where they could be NVARCHAR, FLOAT, or INT

texts:
I have a table that holds results. Based on what the ResultTypeId is these could be either NVARCHAR, FLOAT, or INT. In order to handle all these types the Value field is NVARCHAR

I've found that when handling the results I get problems. For example when trying to sum multiple FLOAT results I need to cast them to FLOAT first.

I think this is the wrong approach to storing the data. The only alternative I can think of is to create a value field for each data type, e.g.:
ValueNumeric ValueText.

What is the recommended approach for this problem?


texts: PostgreSQL is running locally but I cannot connect. Why?

texts:
Recently updated my machine from Mac OS X Lion (10.7.4) to Mountain Lion (10.8) and I think it borked my PostgreSQL installation. It was installed originally via Homebrew. I'm not a DBA, but hoping someone can tell me how to troubleshoot this.

I am unable to connect (but was able to before pre-Mountain Lion):

$ psql -U rails -d myapp_development
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

But Postgres is still clearly running:

$ ps aux | grep postgres
meltemi          2010   0.0  0.0  2444124   5292   ??  Ss   Wed01PM   0:00.02 postgres: rails myapp_development [local] idle
meltemi           562   0.0  0.0  2439312    592   ??  Ss   Wed12PM   0:02.28 postgres: stats collector process
meltemi           561   0.0  0.0  2443228   1832   ??  Ss   Wed12PM   0:01.57 postgres: autovacuum launcher process
meltemi           560   0.0  0.0  2443096    596   ??  Ss   Wed12PM   0:02.89 postgres: wal writer process
meltemi           559   0.0  0.0  2443096   1072   ??  Ss   Wed12PM   0:04.01 postgres: writer process
meltemi           466   0.0  0.0  2443096   3728   ??  S    Wed12PM   0:00.85 /usr/local/bin/postgres -D /usr/local/varpostgres -r /usr/local/var/postgres/server.log

And it's responding to queries (both to a test db and the development db) from a local Rails app

  User Load (0.2ms)  SELECT "users".* FROM "users"
  Rendered users/index.html.haml within layouts/application (1.3ms)

There appears to be no /var/pgsql_socket/ directory, let alone the /var/pgsql_socket/.s.PGSQL.5432 socket file mentioned above!?! Maybe the install of Mountain Lion wiped that out?

$ ls -l /var/ | grep pg
drwxr-x---   2 _postgres  _postgres    68 Jun 20 16:39 pgsql_socket_alt

How can I troubleshoot this?


texts: Select only one duplicate with different time column

texts:
So I have data that is structured like this:

|ID | Reference| Name | Datetime |
 1       abc     John      2016-02-08 08:25:35
 2       abc     John      2016-02-08 08:25:37
 3       bnm     Eliza     2016-02-08 08:25:39
 4       abc     John      2016-02-08 08:25:42
.......
 351     cdf     Eliza     2016-06-01 15:25:42
 352     abc     John      2016-06-02 15:25:42

Due to an error in a logging system, which for the time being is outside my reach, I get several rows of data for what should just be one. I can have up to 20-30 duplicates within a 20 second timeframe. I want to select only one record of those duplicates where the time only differs by 20 seconds, but at the same time I want to include records, such as id 352, which would be excluded if I used a group by clause. I also have other records inside the that 20 second timeframe that I want to include, such as id 3, so I cant directly filter out records that are too close.

Is an option to make a kind of clustered column that combine Reference, name and datetime down to f.ex 20 second precision, and group by that?

I want to keep the duplicate with the earliest starttime.


texts: what is mongodb telling me when it logs an update?

texts:
After several minutes of a large program that pushes a lot of data into mongo, my log starts to show a message for (I guess) each update. This seems awfully noisy, is there some special reason it feels the need to do this?

Tue Jun  5 14:32:37 [conn3] update benchmark.entity
 query: { corefEntityId: "45-LOCATION" }
 update: { $set: { corefEntityId: "45-LOCATION", type: "Location" },
 $push: { indocs: { docid: "cfcc403b-714f-4c5d-8507-ccb5b6354654",
   ordinal: 26, label: "United States", mentions: [ "United States" ] } },
   $addToSet: { allMentions: { $each: [ "United States" ] } },
   $inc: { documentCount: 1 } } 116ms

As a perhaps more answerable footnote, I wonder: is an 'upsert' that adds to some internal document faster or slower than a plain insert into some other collection of the internal document as a top-level document?


texts: When is the modify_date of an object updated?

texts:
As part of the daily checks I'm setting up, I'm restoring the nightly full backups to our test server and checking the modified date of objects. This is not for any kind of precise change tracking or version control, more for proof that something has\hasn't been changed when somebody says something's different.

I've put a table on the test server and after initially populating it, a stored proc queries sys.objects for each restored database to get the modify_date for all the objects.  This date is compared to the modified date I first recorded. If the date in the table doesn't match that from the restored database then it flags. This produces the correct result in 99.9% of cases but a weird little problem has appeared. There are seven functions in our CRM database that show a modify_date of the date and time that the restore took place rather than a 'modified date'.

I can't see any reason for this at all.  It only happens for a tiny fraction of the objects I'm testing across all our databases.  I can't find any triggers that could be updating the functions.  The databases are on SQL Server 2014 so CREATE OR ALTER isn't a possibility.  I've also checked the production servers and the modify_date for these objects matches the last time a restore was performed.

What could be altering, or appearing to alter, functions during a database restore?


texts:
I have two SQL Servers 2014 in AlwaysOn AG setup (OS - WSFC2012R2) with secondary acting as a synchronous replica. I'm facing an issue in the role switchover on daily basis.

Every day morning around 7 or 8AM, due to some reason Primary fails over to the secondary. Ideally, this happens due to network glitch. But i'm unable to find the root cause behind this failover.

This happens on daily basis. Is there any way to narrow down the issue which causes the failover?


texts: Postgresql improve select performance/parallelism in huge table

texts:
I have a huge table (> 3000 million rows, totaling 3 TB) using Postgres 9.5 defined as follows:

CREATE TABLE user_events
(
    user_id VARCHAR NOT NULL,
    datetime BIGINT NOT NULL,
    field1 VARCHAR,
    field2 VARCHAR,
    field3 VARCHAR,
    field4 VARCHAR,
    field5 VARCHAR,
    CONSTRAINT user_events_user_id_datetime_pk PRIMARY KEY (user_id, datetime)
);

alter table user_events set (autovacuum_vacuum_cost_delay=10);

I need to query this table roughly 2000 times per second but I'm not being able to parallelize enough (I'm reaching approx half that).

Using the following prepared statement, I get a reasonable (I suppose) response time:

PREPARE getact AS select * from user_events where user_id = $1 order by datetime limit 1000;

explain analyze EXECUTE getact('xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx');

                                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.70..1495.10 rows=367 width=878) (actual time=0.028..0.044 rows=4 loops=1)
   ->  Index Scan using user_events_user_id_datetime_pk on user_events  (cost=0.70..1495.10 rows=367 width=878) (actual time=0.026..0.034 rows=4 loops=1)
         Index Cond: ((user_id)::text = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'::text)
 Execution time: 0.073 ms

Some additional notes:

The content wasn't loaded in an orderly manner so datetime won't be
physically ordered increasingly but from now on it will.
The table is mostly append only
We expect the table to grow at most a %50 more in the long term.

At this point I'm looking for any configuration suggestion. I know I had scaling options like a readonly replica or sharding, but would like to avoid that as much as possible for now.

Would executing CLUSTER work in this case? It would seems like the most part of the cost is in the index scanning, so I'd guess it won't, but I don't know (and testing it is really expensive).

Some more information:

There are multiple threads writing (mostly appending) at the same rate that needs to be read
Though the times described with the prepared statement are real, I've seen results ranging from < 1 ms to > 100ms

EXPLAIN (ANALYZE, BUFFERS) (slowest) result

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM user_events WHERE user_id = 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX';

                                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using user_events_user_id_datetime_pk on user_events  (cost=0.70..1495.10 rows=367 width=878) (actual time=3.336..1162.936 rows=1518 loops=1)
   Index Cond: ((user_id)::text = 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX'::text)
   Buffers: shared hit=5 read=1532
 Planning time: 0.071 ms
 Execution time: 1164.926 ms

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM user_events WHERE user_id = 'yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy';

                                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using user_events_user_id_datetime_pk on user_events  (cost=0.70..1495.10 rows=367 width=878) (actual time=18.890..953.809 rows=1269 loops=1)
   Index Cond: ((user_id)::text = 'yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy'::text)
   Buffers: shared hit=5 read=1282 dirtied=107
 Planning time: 0.105 ms
 Execution time: 955.590 ms
(5 rows)


texts: What effect will reducing the size of a varchar column have on the database file?

texts:
We have a number of tables in our database that have VARCHAR(MAX) columns where a VARCHAR(500) (or something much smaller than max) will suffice. Naturally I want to clean these up, and get the sizes down to more reasonable levels. The 'how' to do this I understand: my question is what will altering these columns do to the pages and extants on disk? (There's lot of info out there about what happens when you grow a column, but having trouble finding info on what happens when you shrink one.)

Some of the tables have a very small row count, so I'm not worried about the cost of the change, but some are quite large, and I'm worried about them potentially being reorganized and causing a lot of blocking/downtime. In practical terms, I just want a way to estimate a maintenance window. In general, I'd like to understand better how the database engine behaves in this case.

Thanks in advance!

EDIT:

I have 20 tables I'm looking at, though only half of them have row counts greater than 1,000. The biggest has almost a million rows. The worst offender is a table with 350,000 rows and four VARCHAR(MAX) columns that can shrunk to the VARCHAR(500) level.


texts: Upgrading MariaDB Galera cluster from 5.5 to 10.0 - rolling upgrade possible?

texts:
I've had a good search around and cannot find much on the topic. We want to upgrade our live MariaDB 5.5 Galera cluster to the MariaDB 10.0 branch, ideally without any downtime. We installed originally (and upgrade on the 5.5 branch) using the official APT repos (on Ubuntu).

I've found the this helpful article on upgrading from 5.5 to 10.0 but it does not mention Galera anywhere. Does anyone know if we can do a rolling upgrade from the 5.5 branch to the 10.0 branch?

Thanks for your help.


texts: Removing duplicate rows with two or more left join tables

texts:
With a query like this

SELECT a.id, a.name,
       COALESCE( json_agg(b.*), '[]'::json ),
       COALESCE( json_agg(c.*), '[]'::json ),
  FROM a
  LEFT JOIN b ON a.id = b.a_id
  LEFT JOIN c ON a.id = c.a_id
 GROUP BY a.id, a.name;

When executed, both c and b will be multiplied by each other and produce duplicated entries in the JSON array object.

I tried changing the query to use 2 subqueries instead, but I get all sorts of errors and warning, such as "Subquery must return only one column", etc.

I also tried using LEFT OUTER JOIN, but I guess I don't yet master how joining tables work as it only applies to b and c is still multiplied and contain duplicates.

Edit: using DISTINCT errors with "could not identify an equality operator for type json" on the COALESCE functions.

How can I fix this query and only aggregate unique rows?

Edit 2

I need to specify that both table b and c are actually VIEWs, and they both have at least one json_agg column as well, so I cannot just use json_agg(DISTINCT b.*). This would've been way too easy.

Edit 3

Here is a small snippet to reproduce the issue :

--DROP TABLE IF EXISTS tbl_a CASCADE;
--DROP TABLE IF EXISTS tbl_b CASCADE;
--DROP TABLE IF EXISTS tbl_c CASCADE;

CREATE TABLE tbl_a (
  id bigserial NOT NULL,
  name character varying(16),
  CONSTRAINT "PK_tbl_a" PRIMARY KEY (id)
) WITH ( OIDS=FALSE );

CREATE TABLE tbl_b (
  a_id bigint NOT NULL,
  foo json NOT NULL DEFAULT '{}'::json,
  CONSTRAINT "FK_tbl_b_a" FOREIGN KEY (a_id)
      REFERENCES tbl_a (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
) WITH ( OIDS=FALSE );

CREATE TABLE tbl_c (
  a_id bigint NOT NULL,
  bar json NOT NULL DEFAULT '{}'::json,
  CONSTRAINT "FK_tbl_c_a" FOREIGN KEY (a_id)
      REFERENCES tbl_a (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
) WITH ( OIDS=FALSE );

INSERT INTO tbl_a (id,name) VALUES (1, 'Test');
INSERT INTO tbl_b (a_id, foo) VALUES (1, '{"foo":"Hello"}'::json), (1, '{"foo":"World"}'::json);
INSERT INTO tbl_c (a_id, bar) VALUES (1, '{"bar":"abc"}'::json), (1, '{"bar":"def"}'::json);

SELECT tbl_a.id, tbl_a.name,
       COALESCE(json_agg(tbl_b.*), '{}'::json),
       COALESCE(json_agg(tbl_c.*), '{}'::json)
  FROM tbl_a
  LEFT JOIN tbl_b ON tbl_a.id = tbl_b.a_id
  LEFT JOIN tbl_c ON tbl_a.id = tbl_c.a_id
 GROUP BY tbl_a.id, tbl_a.name;

Returns

id  name    coalesce                              coalesce
--  ------  ------------------------------------  ----------------------
 1  "Test"  "[{"a_id":1,"foo":{"foo":"World"}},   "[{"a_id":1,"bar":{"bar":"abc"}},
             {"a_id":1,"foo":{"foo":"Hello"}},    {"a_id":1,"bar":{"bar":"abc"}},
             {"a_id":1,"foo":{"foo":"World"}},    {"a_id":1,"bar":{"bar":"def"}},
             {"a_id":1,"foo":{"foo":"Hello"}}]"   {"a_id":1,"bar":{"bar":"def"}}]"


texts: Converting an XML field to nvarchar causes warning on main SELECT in explain plan

texts:
I'm getting a warning on my main SELECT on one field in the explain/execution plan of my query on the following:

SELECT
    ISNULL(CAST(p.loadDiagnostic as nvarchar(4000)),'''')
FROM myTable p

This select to convert the loadDiagnostic field which is in XML format coming from myTable, is part of a source T-SQL data flow in an SSDT 2017 DTSX package which flows to a destination table in which the target field is mapped to a DT_WSTR type (a null-terminated Unicode character string with a maximum length of 4000 characters).

The warning in the explain plan is telling me the following:

  Type conversion in expression (CONVERT(nvarchar(4000),[p].[loadDiagnostic],0)) may affect "CardinalityEstimate" in query plan choice, The query memory grant detected "ExcessiveGrant", which may impact the reliability. Grant size: Initial 6368 KB, Final 6368 KB, Used 96 KB.

Other than removing the CAST type conversion altogether to resolve the warning, I'm looking for an alternate method of handling implicit conversions of XML source fields in SSDT 2017 in order to address this warning.

I've read that adding a TYPE directive on XML fields can sometimes address this issue, but I'm not sure how to handle this in my T-SQL source SELECT or what the impact would be.

Hopefully, I've provided enough details. Any help would be great!


texts: Is a Secondary Index in MySQL the Same Thing As a Non-Clustered Index in SQL Server?

texts:
I am assuming that a secondary index in MySQL is the same thing as a non-clustered index in SQL Server.

Is this true?


texts: DBCC CHECKDB unfixable corruption: Indexed view contains rows that were not produced by the view definition

texts:
TL;DR: I've got an unfixable corruption in an indexed view. Here are the details:

Running

DBCC CHECKDB([DbName]) WITH EXTENDED_LOGICAL_CHECKS, DATA_PURITY, NO_INFOMSGS, ALL_ERRORMSGS

on one of my databases produces the following error:

  Msg 8907, Level 16, State 1, Line 1 The spatial index, XML index or
  indexed view 'ViewName' (object ID 784109934) contains
  rows that were not produced by the view definition.  This does not
  necessarily represent an integrity issue with the data in this
  database. (...)

  CHECKDB found 0 allocation errors and 1 consistency errors in table 'ViewName'.

  repair_rebuild is the minimum repair level (...).

I do understand that this message indicates that the materialized data of the indexed view 'ViewName' is not identical with what the underlying query produces. However, manually verifying the data does not turn up any discrepancies:

SELECT * FROM ViewName WITH (NOEXPAND)
EXCEPT
SELECT ...
from T1 WITH (FORCESCAN)
join T2 on ...

SELECT ...
from T1 WITH (FORCESCAN)
join T2 on ...
EXCEPT
SELECT * FROM ViewName WITH (NOEXPAND)

NOEXPAND was used to force use of the (only) index on ViewName. FORCESCAN was used to prevent indexed view matching from happening. The execution plan confirms both measures to be working.

No rows are being returned here, meaning that the two tables are identical. (There are only integer and guid columns, collations do not come into play).

The error cannot be fixed by recreating the index on the view or by running DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS. Repeating the fixes also did not help. Why does DBCC CHECKDB report this error? How to get rid of it?

(Even if rebuilding fixed it my question would still stand - why is an error reported although my data checking queries run successfully?)

Update: The bug has been fixed in some releases. I can no longer reproduce it in SQL Server 2014 SP2 CU 5. The 2014 SP2 KB contains a fix without KB article: Creating non-clustered index causes DBCC CheckDB With Extended_Logical_Checks to raise corruption error. The two connect bugs about this have been closed:

https://connect.microsoft.com/SQLServer/feedback/details/847233/creating-non-clustered-index-causes-dbcc-checkdb-with-extended-logical-checks-to-raise-corruption-error
https://connect.microsoft.com/SQLServer/feedback/details/795478/unfixable-dbcc-checkdb-error-that-is-also-a-false-positive-and-otherwise-strange


texts: SQL statement timing from MySQL 5.7 client when using a script

texts:
When I run a SQL statement interactively in a MySQL 5.7 client it tells me how long the query ran:

mysql> select now();
--------------
select now()
--------------

+---------------------+
| now()               |
+---------------------+
| 2018-03-09 14:27:42 |
+---------------------+
1 row in set (0.07 sec)

In this case it ran for 0.07 seconds.

But when I run the same SQL statement through a script the output does not include the time the statement took.

>mysql -f -v --table -u myuser -pmypassword -h myhost mydb < test.sql

mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
select now()
--------------

+---------------------+
| now()               |
+---------------------+
| 2018-03-09 14:31:00 |
+---------------------+

How can I run a SQL statement from a script and still get back the length of time that it ran using the MySQL 5.7 client?

Thanks!
Bobby


texts: What is the best way to upgrade a MySQL version in a Linux Server

texts:
I have database around 14 GB running with 8 -15 GB ram(diffrent deployments) .At present MySQL version is 5.5 and I’m planning to move to 5.6 . I did similar activity in past for 5.1 to 5.5 upgrade .

Steps I followed is 1. Resolve any syntactical issue that can be faced in upgrader version of Mysql in code base prior to upgrade.

at the day of upgrade 2. Shut down the MySQL with application; take two backups (one with data and without data) 3. Uninstall and remove old MySQL instance and install new MySQL instance. 4. Import backup without data first and with data second and go live again.

I experience that during above process, it took very long time for importing backup file to database as some of our production servers run with less memory and during this remotely increase the risk as well. This is the only best way I know, is there a best approach I can fallow to upgrade MySQL version in future with very less time . Appreciate your help . Please note that I do not have facility to get another machine to do this, so I have to do in same production server where justifiable downtime is acceptable.


texts: Cannot SUM the value of a sales amount field - various criteria, sales rep id and date ranges

texts:
I wonder if someone can help me with this SQL Server 2016 query.
I have two tables with following data
TblSalesRep
This is the table holding the history of the territories that the sales rep looked after and the date range that they looked after it.

TerritoryId
SalesRep
FromDate
ToDate

15
Anne
2020-01-01
2020-06-02

15
Dave
2020-06-03
2020-06-16

15
Anne
2020-06-17
2020-06-22

18
Anne
2020-06-23
2999-12-31

tblSales
This is the table that holds the sales made by territory

TerritoryId
TransactionId
TransactionDate
ProductCode
Amount

15
1
2020-04-15
Bags
50

15
1
2020-04-15
Bags
50

15
2
2020-06-02
Bag
25

15
3
2020-06-03
Shoes
60

15
4
2020-06-07
Shoes
10

15
5
2020-06-17
Bags
15

15
6
2020-06-18
Bags
25

15
7
2020-06-25
Coat
100

18
8
2020-06-27
Bags
20

I’m trying to produce a report that displays the SalesRep and the total sold between two dates for a given SalesRep.
For example, if I provided a SalesRep = ‘Anne’ and a date range of ‘1 Jun 2020’ to ‘30 Jun 2020’ I would expect a result back of :
SalesRep    Sum
Anne        185

This is because it should exclude any transactions made between 03 Jun 2020 and 16 Jun 2020 as the territory was looked after by Dave for a couple of weeks and he conducted that business between those dates.
I’ve tried using the SUM() function whilst joining to TblSalesRep but the figures are not what I’m expecting.


texts: Mysql memory_global_total is smaller than memory usage in top

texts:
When I try top in my ubuntu the result

top - 16:03:09 up 16 days, 21:24,  2 users,  load average: 3.53, 2.72, 3.46
Tasks: 395 total,   1 running, 282 sleeping,   0 stopped,   0 zombie
%Cpu(s): 10.0 us,  0.5 sy,  0.0 ni, 87.7 id,  1.7 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem : 16089320 total,   277676 free, 15396176 used,   415468 buff/cache
KiB Swap: 31231996 total, 30194232 free,  1037764 used.   364724 avail Mem

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
 3529 mysql     20   0 20.627g 0.013t   8084 S 168.2 89.2 533:45.83 mysqld
 5361 root      20   0       0      0      0 I   2.0  0.0   0:01.08 kworker/u32:2
 1234 user      20   0   43052   3280   2324 S   0.7  0.0   2:57.46 top
 5299 user      20   0   43048   3380   2420 R   0.7  0.0   0:14.83 top
 1305 root      20   0 2690104   1992   1256 S   0.3  0.0   2:02.85 snapd
 2355 root       0 -20       0      0      0 I   0.3  0.0

but when I execute

SELECT * FROM sys.`memory_by_host_by_current_bytes`

the result is

"total_allocated"
"313.61 MiB"

I am using Mysql 5.7.25 running on ubuntu 18.04.01 LTS


texts: Need to maintain transactional consistency of the data across both servers, need to achieve these goals without manual intervention

texts:
I got this question about MS SQL Server 2012 administration, two production servers in the same data center. You need to ensure that database remains available if a catastrophic server failure or a disk failure occurs.

You need to maintain transactional consistency of the data across both servers. You need to achieve these goals without manual intervention.

The correct answer for the above question is

>>
Two servers configured on the same subnet
SQL Server Availability Group configured in Synchronous-Commit Availability Mode
<<

But I think the correct answer should be

    Two servers configured in Windows Failover Cluster in the same data center SQL Server configured as a clustered instance<<


texts: Any way to speed up reorganize partition of a very large table in MySQL?

texts:
So we have this massive 1.5TB+ table with billions of records that has been neglected for the past few months and needs to have new partitions added.
The partitions are based off a timestamp which are created by month and the last partition is from March. So we have about 4 month's worth of data in the pMax partition which is nearing 700GB. This table is of course business critical and we can't have much downtime.

I have a smaller table that had about 80G in the pMax partition and it took about 6 hours to reorganize and pull out 13G of data for one month. So it would easily take around 3 days to fix up the big table.

This is what I'm using to reorganize and add partitions:

alter table db.table
reorganize partition pMax
into (partition p20150201 values less than ('2015-03-01'),
partition p20150301 values less than ('2015-04-01'),
partition pMax values less than (maxvalue));

Would it be faster to create a new table with partitions created for the next year, then insert data from the current table and rename once it's completed? I'm still thinking this would take forever given the size of the table... but don't know what other options I have, if any....

Has anyone use pt-online-schema-change to reorganize a partition? Sounds like I should be able to do it online.. just need to double our storage mount.


texts: Disk sizing considerations for 'tempdb' - Growing substantially two times a year

texts:
I have an HR software on which an employee of the HR department runs long and complex analyses and calculations about two times in a year. Because of this the tempdb is growing to 500GB and even more.

What is a good disk sizing solution? Because the rest of the year tempdb is not so big.

Details

The SQL Server version and edition of relevance are 2017 Standard.
tempdb and databases are on the same partition D: and Log files are on a different partion E:
The HR database itself has around 78GB.


texts: Is it possible to have both a subquery and a function in a WITH clause?

texts:
In Oracle 12c, we can include subqueries in the WITH clause:

WITH dept_count AS (
  SELECT deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT e.ename AS employee_name,
       dc.dept_count AS emp_dept_count
FROM   emp e
       JOIN dept_count dc ON e.deptno = dc.deptno;

We can also include functions in the WITH clause:

WITH
  FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN p_id;
  END;
SELECT with_function(id)
FROM   t1
WHERE  rownum = 1

Is it possible to include both a subquery and a function in the WITH clause of a single query?

This is just a hypothetical question that came to mind. I don't have any real-world examples, but would be interested to see how this might work. I'm not really experienced enough to come up with an example that's useful.


texts: Identity in a view treated as ordinary int - migrating from SQL Server 2000 to 2012

texts:
I'm migrating a very old platform from a SQL Server 2000 backend and MS Access 2003 as its frontend to SQL Server 2012 backend and MS Access 2010 as the frontend. This obviously means a bit of headache, but at the moment I'm stuck at a "limitation" of the new database.

Apparently (according to this: http://jagbarcelo.blogspot.se/2006/09/identity-columns-in-view-treated-as.html) any compatibility level above 80 in SQL Server will treat identities as ordinary ints in relational views. This creates problems in my frontend as no new records are created (exists in both 2003 and 2010 version of Access).

My question is: is there any way to fix this in the database (backend) rather than changing the whole frontend?

Update: I'm still curious about this so an update from further testing..

Apparently my above situation is the result of a view containing relational tables. In a compatibility level of 80 it exposed a natural identity column (is_identity = true), but for later compatibility levels no identity was exposed.

Can this be forced in some way, by some sort of annotation perhaps?


texts: Fulltext search limiting users possibilities

texts:
We have a web site with a search box. The search uses a fulltext index column. However, we just cant feed the text the user has input into this stored procedure.
CREATE PROCEDURE [dbo].[SearchPages]
    @Term varchar(200)
AS
BEGIN
    SELECT pc.SearchData, from PageContent pc
    where contains(pc.SearchData, @Term)
END

Searches with space in them fails, also there is a pletora of sql functions we do not want to expose to the users like
NEAR((bike,control), 10, TRUE)

and the like or binary operators like AND or OR.
So we need to escape the term in some way.
One way that immediately comes in mind is to put an AND between every word.
However, it doesnt feel acceptable.
So is there any better suggestions?


texts: Process to change collation on a database

texts:
I've been asked to change the collation on a database that has been in production for a few years as it was not set correctly at the time of deployment.

So my research shows that in order change collation on all of the columns you have to drop constraints and what-not, and it looked to get ugly quickly.  So I thought about just scripting out all objects, find/replace all references to the old collation, create the new database, and then use dbatools to copy the data:

Get-DbaDbTable -SqlInstance SourceServer -Database SourceDb |
 foreach-object
    { Copy-DbaDbTableData -DestinationDatabase TargetDb -DestinationTable $_.Name }

Is this a valid approach to changing collation on everything?  At first blush it seems to have worked out rather swimmingly (i.e., got through the migration of data with no errors).

Going from SQL_Latin1_General_CP1_CI_AS to SQL_Latin1_General_CP437_CI_AI if that matters.


texts: Attaching a database (created in SQL Server 2012) to an instance of SQL Server 2014 without upgrading it

texts:
I've a database in a production server (SQL Server 2012) and I've a development server (SQL Server 2014).

I need to attach the database in the development server but I've realized that once attached it's automatically upgraded to the version 2014 so I can't attach it again in the production server.

Is it possible to attach the database in the development server without upgrading it?

We are planning to make changes in dev, detach, and attach it back to production.


texts: Need help in restore some rows into mySQL database frm myd myi

texts:
I am using one mySQL database for several sections of the website and one of the section is not working, when I go to the link of that section it gives the error "1146 Error @ Line 39: Table 'database_name.table_name' doesn't exist"

I logged into PHPmyAdmin and in the list of raws the "table_name" is missing, seems like someone has hacked into it and dropped it.

I have got the following files from the backup server :

table_name.frm
table_name.myd
table_name.myi

I opened all these files into notepad to see the data, frm and myi is understandable may be because they are structures but myd is understandable as it has data.

Now I want to restore the lost data through these 3 files but I don't know how.


texts: Will certain Database Compatibility cause performance degradation?

texts:
I'm running a variety of SQL Servers from 2005 to 2012 across several machines. I have many of these databases running in SQL Server 2000 compatibility mode.

Will using an older compatibility in a newer version of SQL Server cause any performance issues?

What is the different between these databases, other than older versions of SQL Server being able to access them?


texts:
Please let me preface by saying that I understand poorly formed reports can cause poor SSRS performance and I have people looking at that issue separately.
I have an SSRS server that is seeing incredible high IOPS at certain points in the day.  The ability to quickly run reports pretty much stops until the IOPS return to normal.  The MSSQL database is on a separate AWS instance (two systems running, SSRS and MSSQL).  The SSRS server has more than enough RAM and CPU, those never spike.  It is only the C:\ (where MSSQL SSRS is installed) that is seeing the issue.  When I say that there are IOPS spikes, I am seeing IOPS writes from from ~20k to ~32k.  Those spikes occur while the queue length also increases.
Can someone suggest configuration changes to make SSRS use a secondary drive for whatever it is doing to cause the high IO?  Another possibly important piece of information, in the logs I often see the message "Call to CleanBatch()"; it often lines up with the IOPS write spikes.
Thank you


texts: Should we create a schema per "project"?

texts:
This is a database where users create a 'project' and work on it. We have a table for project and various other tables that contain different attributes for project (multiple rows for each project from 2-30K rows). All of them contains projectID as link to project.  Currently the database is 400GB.

We are trying to create a schema for each project wherein each schema would contain all attribute tables. Whenever a project is created, it would get a new schema.  This would be mean each table would contain at max 30K rows which would improve select performance. We would be using dynamic SQL in our queries to read/insert.

I feel like this would not be proper application of database but how do I prove to my team that this a bad approach?


texts: What are the OMem and 1Mem columns in the plan output?

texts:
The plan output, for example

SELECT *  FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR( '' ,NULL,'ALLSTATS  LAST'));

has the columns OMem and 1Mem. What is their meaning? I could not find any reference or documentation about them, just this single web page, claiming (or asking):

  OMem:- Estimated optimal mem (PGA) size, to execute the operation in memory.

  1Mem:- Estimated size to execute the operation in single pass to disk?


texts: Call Powershell script from another Powershell script

texts:
I have a problem.
I develop some powershell scripts to perform actions on my Databases.
In theory this PS should execute when them are called by the main powershell like the calls in .bat files.

My idea is the team support have in one folder all PS1 scripts and they only run the main PS1 and provide the credentials and other values requires to run the scripts.

I tried using the Invoke-expression or Invoke-command to call the others PS1 but the main script find the files but don't execute any action.
I would like to know if you know what cause the others scripts don't run

I have the main PS1 named CREATE_CNFMGR_SCHEMA.ps1 within this PS1 I call another PS1 named for example testing.ps1 or FileStreamAccess_Validation.ps1
The script code find the PS1 files but not perform anything.

This is the code to call the others PS1 in my main PS1:

param(
[String] $pathfolder,
[Parameter(Position=0, Mandatory=$true)]
[string] $svrinstance,
[Parameter(Position=1, Mandatory=$true)]
[string] $dbuser,
[Parameter(Position=2, Mandatory=$true)]
[string] $dbusupassw,
[Parameter(Position=3, Mandatory=$true)]
[string] $dbname
)

$sqlinstance    = $svrinstance
$userdb         = $dbuser
$passdb         = $dbusupassw
$namedb         = $dbname

try
{
 Write-Output "Baseline Script to Set Up Confirmation Manager Database"
    $pathfolder = Split-Path -Parent $MyInvocation.MyCommand.Definition

if($pathfolder -ne $null)
{
    Write-Host $pathfolder
    [string]$psfile = "c:\temp\log\FileStreamAccess_Validation.ps1"
    Write-Host $psfile
    Invoke-Command -filepath $psfile
    Invoke-Expression $psfile
 }

}
catch
{

}


texts: sqlplus startup - failure in processing system parameters

texts:
I am trying to do the startup command

This is what I have done:

set oracle_sid=ORCL
sqlplus /nolog
SQL> conn /as sysdba
startup

It shows this error message:

  failure in processing system parameters
  could not open parameter file "C:\Program\11.2.0\DBhome_1\Database\initorcl.ora"

I checked and found out that "initoracl.ora" doesn't exist inside this path:

C:\Program\11.2.0\DBhome_1\Database

I found a init.ora file in:

C:\Program\11.2.0\dbhome_1\dbs

I tried doing this command:

startup pfile="C:\Program\11.2.0\dbhome_1\dbs\init.ora"

It shows this error:

  invalid value given for the diagnostic_dest init.ora parameter
  the specified ADR Base Directory does not existed [C:\program\11.2.0\dbhome_1\database\]
  specific directory does not exist
  additional error information
   the filename, directory name, or volume syntax is incorrect.

What should I do?


texts: Logging of incorrect row in SQL Server Integration Services

texts:
I newbie to SQL Server Integration Services and I have a problem:

Data loaded from file looks like this:

1 'a' 'b'
2 'a' 'b'
1 'c' 'd'

And this rows would be loaded in table like this:

CREATE TABLE T (
   ID NOT NULL PRIMARY KEY,
    A nvarchar NOT NULL,
    B nvarchar NOT NULL
);

Cause of ID is primary key third row in input data could not be loaded, and I want to load correct rows (first and second or third and second) and send to log incorrect row.


texts: Can I add an Arbiter node in the same server of my Config-Server?

texts:
I'm using MongoDB and I want to know if I can add an Arbiter node in the same server that I'm hosting my Config-Server. Is it possible?

Let's say that I have 3 sharded clusters with 6 servers (2 per shard), 3 config-servers (each one will have arbiter for each shard) and 1 server as mongos (router). Is this good enough?

Also, what is the suggested specs for those Config-Arbiter servers would be?


texts: Is a chain of foreign keys better than duplicating data?

texts:
I'm new to database designing for real-world applications. I have an idea for an application where each student could leave an opinion about a university at which one was on an exchange (I will use Django + PostgreSQL if that helps). While I was thinking about the database design I came up with this schema:

The reason for such a design was that there will be a lot of information to store. The user, however, should be able to look for an opinion giving just his e.g. destination university. He should also be able to narrow down the scope of the search by providing additional information.
However, when I think of the schema right now I'm unsure whether I should add separate FKs to the Opinion table to avoid such FKs chain like in the image, or is it just fine?


texts: Msg 15404, Level 16, State 10, Procedure xp_logininfo - missing permissions

texts:
I have a server based in the USA, where I am not a domain admin, and my permissions are limited to sql server only - where I am sysadmin.

after a migration of a few databases to a new server, I noticed that I needed to go back to the old server to get one of my logins and permissions related.

when I run the following command:

EXEC xp_logininfo 'gplondon';

I get:

  Msg 15404, Level 16, State 10, Procedure xp_logininfo, Line 62 [Batch
  Start Line 6] Could not obtain information about Windows NT group/user
  'gplondon', error code 0xffff0002.

I understand I have all the permissions inside sql server to run xp_logininfo

But what other permissions are missing that have disabled me (or the sql server service on that server) to interrogate the active directory?

in other words, everything was working fine before, what permissions are missing now?


texts:
I'm new to the database world and have recently started working with a large database with several tables with mainly varchar text and integers.  The two largest tables are of ~50 million and 25~million rows.  The database contains about 350,000 ID numbers for people and I often need to retrieve information about all individuals that involves joins to both of my very large tables (they're one to many relationships).  These queries are also temporal in nature and involve  the between operator to determine events that happen without a certain time frame.  It will often take 10-15 minutes for some of these queries to run (I'm still learning and try new indexes to see if I can improve performance.  After running out of ram running a particular query I had to my computer froze and I had to reboot.  Even after restarting I was unable to detach, drop connections and delete my log files to delete my database (which was in recovery mode).

I booted into safe mode to delete the mdf and ldf files and saw that my log file was twice the size of the data file.  If I routinely need to run queries that will return aggregate, temporal information on tables of the above-specified size, is there anything I can do to prevent log bloat?  Also, I know SQL Server eats resources for lunch, but what type of specs would a computer need to have to run a query like the following with the table sizes listed above? (it takes an hour on my local machine)

EDIT: this database is static in nature and will not have anything added to it.  it is also only unavailable to one user, me.  I'm not sure what type of recovery it had, I don't have that PC in front of me at the moment.

query:

--my real database field names are not this generic
;with x
as
(   --the first CTE finds the number of different classes a person qualified for and lists a number for a given date
    select  rx.pat_id
           ,c.cal_date
           ,COUNT(distinct rx.class) as distinctClass
           from rx,Calendar as c
           where c.cal_date between rx.fill_date and rx.script_end_date
           group by rx.pat_id,c.cal_date

),y
as
(   --the second CTE gives interval number to the date so the max and min date values can be grouped by grp_nbr to give a time span
    select x.pat_id
          ,x.cal_date
          ,c2.id-ROW_NUMBER() over(partition by x.pat_id,x.distinctClass
                 order by x.cal_date) as grp_nbr
          ,x.distinctClass
          from x
          inner join Calendar as c2
          on c2.cal_date=x.cal_date
)
--this groups the max and min dates with a particular grp_nbr and the number of different classes a person qualifies for
select y.pat_id,min(cal_date),max(cal_date),MIN(distinctClass)
    from y
    group by y.pat_id,grp_nbr
    order by MIN(distinctClass) desc


texts: AWE to allocate memory in SQL Server 2008 Express with Advanced Services

texts:
I am running SQL Server Express Edition with Advanced Services on Windows Server 2008 Enterprise 32 bit OS.

I have enabled "Use AWE to allocate memory" on the SQL Server, but I am not sure if this will help at all as in the Microsoft Documentation it says that AWE is not supported for SQL Server 2008 Express.

Why then there is check box for a option that supposedly is not supported?

I would go with the Standard edition of SQL Server if I know for sure that AWE is not supported in SQL Server Express.


texts: How can we decide FILLFACTOR of tables on production database?

texts:
I am using SQL Server 2008 R2 E2 x64. on production machine having 8 core processor, 32 GB RAM.

My database is being used commonly for OLAP & OLTP both.

There are some constraints from Infra, "we can not change disk, RAID configuration and can't move database to another server.

tempdb, T-Logs & .mdf are located on seperate partition.

Then I thought to do, what may be possible at database end.

I retrived some information about indexes using below query.

Now the question is;-

Does proper FILLFACTOR value of Index helps to reduce disk IO?
If Yes, then how can we decide FILLFACTOR value based on the data I get using this query or Experts query (will be appreciated)?

Select
            a.[Table Name],a.[Index Name],a.[Index Type],a.[Index Columns],a.[Row Count]
            ,ips.avg_fragmentation_in_percent,a.[Fill Factor],a.user_seeks,a.user_scans
            ,a.user_lookups,a.[Index Read],a.[Index Writes],a.[Last Used (Read)],a.[Last Used (Write)]
            ,[Last Index Re-Built/Re-Organize]
from
(select Top 10  t.name [Table Name]
            ,i.name [Index Name]
            ,i.type_desc [Index Type]
            ,c.name [Index Columns]
            ,p.rows [Row Count]
            ,i.fill_factor [Fill Factor]
            ,ius.user_seeks
            ,ius.user_scans
            ,ius.user_lookups
            ,(ius.user_seeks+ius.user_scans+ius.user_lookups) [Index Read]
            ,ius.user_updates [Index Writes]
            ,COALESCE(ius.last_user_seek,ius.last_user_scan,ius.last_user_lookup) [Last Used (Read)]
            ,ius.last_user_update [Last Used (Write)]
            ,STATS_DATE(s.object_id, s.stats_id) [Last Index Re-Built/Re-Organize]
            ,i.index_id
            ,t.object_id

from        sys.tables t
inner join  sys.columns c
on          t.object_id=c.object_id
inner join  sys.indexes i
on          t.object_id=i.object_id
inner join  sys.index_columns ic
on          t.object_id=ic.object_id and i.index_id=ic.index_id and c.column_id=ic.column_id
inner join  sys.dm_db_index_usage_stats ius
on          t.object_id=ius.object_id and i.index_id=ius.index_id
inner join  sys.stats s
on          t.object_id=s.object_id and i.index_id=s.stats_id
inner join  sys.partitions p
on          p.object_id=i.object_id and p.index_id=i.index_id
Order by ius.user_lookups desc  -- Use to see by CPU, Resd, Writes, No. of Records.
) a
cross apply
            sys.dm_db_index_physical_stats(db_id(),a.object_id,a.index_id,0,DEFAULT) ips


texts: Searching for a Data Dictionary for SQL or Oracle

texts:
I am looking for metadata specifications (data dictionary) for SQL Server and Oracle databases.

By metadata map, I mean a definitive description of what objects make up database model and their relationships (i.e. a server has 0:n databases, a table has 1:n columns, a column has 0:n constraints).

Any versions of these DBMS platforms would be useful.


texts: migrating to a bigger server - how to copy over differences

texts:
We have a very large database, one table (logs) contains the majority of the data (around 13 billion rows) total database size is approx 6.5TB and I have 900gb of space remaining before server is full.
We have a delete script for old data, but it doesn't effect size, as indexes are fragmented, it would be nice to reclaim this but i know it will introduce a huge downtime if i rebuild the one big index.
The system is always on, we do have quiet periods like sunday night, but any downtime needs to be max 2 hours.
I was planning on copying over the 1TB .bak file to new server, restore, rebuild indexes, then I somehow need to look at the differences between what changes, and update the new server with these differences (new or updates records)
My question is:
Is there a free tool built into sql server that can detect differences, and copy over new/changed data if I "link" the servers? (the one big table does not allow updates, only inserts/deletes)
We are using sql server 2017 ent
As a side note and for full picture, we do not use compression (row or page, this is a different route we are looking at too - if we can do without down time)


texts: Whay does the SQL Server Service account has the ""Impersonate a client after authentication" Right?

texts: Our database is running on SQL Server 2008 hosted on a Windows 2008 Server. We have found that the security policy “impersonate a Client after authentication” is enabled for the SQL Server service account. Will removing “impersonate a Client after authentication” cause any issues? is this URA required for SQL to function correctly. The database hosted on this instance is a client-server application.


texts: Monitoring replication status from the subscriber

texts:
I have two databases that are populated by our vendor using replication from their databases. I do not have access to their distributor db. But I want to monitor from our end whether the data has been refreshed as per schedule.

Is there a way to do this? All the blogs/posts I have seen require access to the distribution db.


texts: Unexpected behavior from ltrim in postgres

texts:
select ltrim('Test Thing', 'Test ');

Expected: 'Thing'
Actual Result: 'hing'

Why does the 'T' in 'Test' match and trim the 'T' from 'Thing'?


texts: Oracle Database 19.3 on Windows: Howto impdp or expdp from a UNC path in Windows?

texts:
With oracle database release 18.3 it is possible to use a unc path as a directory in windows and pump a database pump from this directory with impdp.

Example:

C:\>sqlplus system/passwd@exampledb
C:\>create directory DUMPS as '\\storage\dir';
C:\>exit
C:\>impdp system/passwd@exampledb directory=DUMPS dumpfile=example.dmp logfile=example-imp.log
... just works as expected

With oracle database release 19.3, the same impdp instruction produces the following output:

c:\>impdp system/passwd@exampledb directory=DUMPS dumpfile=example.dmp logfile=example-imp.log

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39155: error expanding dump file name "\\storage\dir\example.dmp"
ORA-48128: opening of a symbolic link is disallowed

I use the same account to run the oracle database service in the example for 18.3 and 19.3. Therefore, I assume that network or credential issues are not an explanation for my observation.

Is this a changed behaviour between Oracle Database Release 18.3 and 19.3, and did I miss the description of this changed behaviour in the release notes?
Or am I missing a setting introduced with 19.3 to enable the use of UNC shares in a DIRECTORY? Or something else I did wrong?

I tried to use the share as drive letter as follows:

C:\>net use p: \\storage\dir /persistent:yes /user:myuser
dir p:
... list of files // network access works well

C:\>sqlplus system/passwd@exampledb
create directory DUMPS as 'P:\';
exit
C:\>impdp system/passwd@exampledb directory=DUMPS dumpfile=example.dmp logfile=example-imp.log

... ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation

Now impdp complains about the log file. The problem also appears without using the impdp logfile= parameter.


texts: An uncommitted transaction updates a column; how can a different transaction see the old column value?

texts:
Let's assume the following case.

There is a students table:

id | name
-----------------
1  | Mateus

A "User A" executes the following statement without committing the transaction:

update students set name = 'Gustavo' where id = 1

Then a "User B" executes the following query:

select * from students with (nolock) where id = 1

User B will get the name Gustavo, that's the expected behavior. The DB will return the uncommitted value because of the with(nolock) instruction.

Is there a way to get the old name, Mateus (the previously committed value), even when there's an uncommitted transaction?


texts: Consistency errors for a SharePoint database

texts:
When I check my database (SQL Server 2008 R2, Windows Server 2008 R2, SharePoint 2007 DB) with the command:

DBCC CheckDB

I get 12 consistency errors (see below).

Is the only way to fix it with repair_allow_data_loss?

Is there also a way to get the filename and delete the file in my SharePoint?

Msg 8928, Level 16, State 1, Line 1
Object ID 293576084, index ID 1, partition ID 72057599643549696, alloc unit ID 72057594044940288 (type LOB data): Page (1:5718) could not be processed.  See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 293576084, index ID 1, partition ID 72057599643549696, alloc unit ID 72057594044940288 (type LOB data), page (1:5718). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12584969 and -4.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 293576084, index ID 1, partition ID 72057599643549696, alloc unit ID 72057594044940288 (type LOB data). The off-row data node at page (1:5718), slot 0, text ID 410976256 is referenced by page (1:5682), slot 0, but was not seen in the scan.
Msg 8928, Level 16, State 1, Line 1
Object ID 293576084, index ID 1, partition ID 72057599643549696, alloc unit ID 72057594044940288 (type LOB data): Page (1:62999) could not be processed.  See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 293576084, index ID 1, partition ID 72057599643549696, alloc unit ID 72057594044940288 (type LOB data), page (1:62999). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12584969 and -4.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 293576084, index ID 1, partition ID 72057599643549696, alloc unit ID 72057594044940288 (type LOB data). The off-row data node at page (1:62999), slot 0, text ID 1788739584 is referenced by page (1:62231), slot 0, but was not seen in the scan.
Msg 8928, Level 16, State 1, Line 1
Object ID 293576084, index ID 1, partition ID 72057599643549696, alloc unit ID 72057594044940288 (type LOB data): Page (1:72779) could not be processed.  See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 293576084, index ID 1, partition ID 72057599643549696, alloc unit ID 72057594044940288 (type LOB data), page (1:72779). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12584969 and -4.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 293576084, index ID 1, partition ID 72057599643549696, alloc unit ID 72057594044940288 (type LOB data). The off-row data node at page (1:72779), slot 0, text ID 1898315776 is referenced by page (1:72782), slot 0, but was not seen in the scan.
Msg 8929, Level 16, State 1, Line 1
Object ID 293576084, index ID 1, partition ID 72057599643549696, alloc unit ID 72057599818924032 (type In-row data): Errors found in off-row data with ID 1898315776 owned by data record identified by RID = (1:72370:23)
Msg 8929, Level 16, State 1, Line 1
Object ID 293576084, index ID 1, partition ID 72057599643549696, alloc unit ID 72057599818924032 (type In-row data): Errors found in off-row data with ID 410976256 owned by data record identified by RID = (1:72375:20)
Msg 8929, Level 16, State 1, Line 1
Object ID 293576084, index ID 1, partition ID 72057599643549696, alloc unit ID 72057599818924032 (type In-row data): Errors found in off-row data with ID 1788739584 owned by data record identified by RID = (1:72448:16)
CHECKDB found 0 allocation errors and 12 consistency errors in table 'AllDocStreams' (object ID 293576084).
CHECKDB found 0 allocation errors and 12 consistency errors in database 'DatabaseName'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (DatabaseName).


texts: Insert multiple rows into a table with only an IDENTITY column

texts:
I have a table called dbo.Groups defined like this:

CREATE TABLE dbo.Groups
(
  GroupID int NOT NULL IDENTITY (1,1) PRIMARY KEY
);

The table really consists of just the one IDENTITY column.

Sometimes I want to insert into it multiple rows at once and get the generated IDs. (I already have a pre-defined table variable called @output with a single ID column to be used in the OUTPUT clause.)

Now I know how I would proceed if it was a single row:

INSERT INTO
  dbo.GroupID
OUTPUT
  inserted.GroupID INTO @output (ID)
DEFAULT VALUES
;

But I want to be able to insert two or more in one go. The actual number is determined by the number of rows returned by this query:

SELECT
  *
FROM
  dbo.MySource
;

So if the query returns one row, I want to insert one row into dbo.Groups and return the generated GroupID. If it is a hundred rows, then I would expect a hundred rows inserted and a hundred IDs generated and returned at once.

One obvious method is to insert one row at a time in a loop. I would like to avoid that and use a set-based approach instead, something along the lines of

INSERT INTO
  dbo.GroupID
OUTPUT
  inserted.GroupID INTO @output (ID)
SELECT
  ...  -- what?
FROM
  dbo.MySource
;

Is there a way to insert multiple rows into a table with just an IDENTITY column in (preferably) a single statement?


texts: Which database engines will allow me to GRANT/REVOKE on a specific column?

texts:
If I have a table with a single column of sensitive data, and I want to grant broad use of the table without exposing that one column, I know that I can create a VIEW that gives them access to all the non-sensitive columns. However, PostgreSQL allows you to grant column-level permissions in the form of

grant select (col1, ...coln) on table to role;

Are there other engines which provide this capability?


texts: How to get the Sum of all column values in the last row of a resultset without using UNION?

texts:
I need to get the sum of all column values of a result set in the last row.
Here is my SQL query.

SELECT  CS_YEAR AS YEAR,
        CS_MNTH AS MONTH,
        CS_WK AS WEEK_NO,
        'Total' AS COST_CARRIER,
        'Total' AS COST_CARRIER_CD,
        SUM(CS_WG_CST) AS WAGE_COST,
        SUM(CS_PART_CST) AS MATERIAL_COST,
        SUM(CS_DH_CST) AS DH_SUBLET_COST,
        SUM(CS_TOTAL_CST) AS TOTAL_COST

FROM    ASPECT.WR_CD_CC_RPT_SUMM
WHERE
        CS_CNTRY_CD = '81930' AND
        CS_YEAR = 2016 AND
        CS_MNTH = 1 AND
        CS_WK = 2 AND
        CS_MFC_CD = 'CBU'
GROUP BY CS_YEAR, CS_MNTH, CS_WK

UNION ALL

SELECT  CS_YEAR AS YEAR,
        CS_MNTH AS MONTH,
        CS_WK AS WEEK_NO,
        CS_CC_KIND AS COST_CARRIER,
        CS_CC_CD AS COST_CARRIER_CD,
        CS_WG_CST AS WAGE_COST,
        CS_PART_CST AS MATERIAL_COST,
        CS_DH_CST AS DH_SUBLET_COST,
        CS_TOTAL_CST AS TOTAL_COST

FROM    ASPECT.WR_CD_CC_RPT_SUMM
WHERE
        CS_CNTRY_CD = '81930' AND
        CS_YEAR = 2016 AND
        CS_MNTH = 1 AND
        CS_WK = 2 AND
        CS_MFC_CD = 'CBU'

I am getting the result like this-

is there any alternate way to get the total of all the cost on the basis of year, month and week no without using UNION.


texts: insert multiple authors only if it exists in MySQL

texts:
I have a problem . I am doing a PHP -My SQL project . I got to store author/s of a conference paper in a database . But sometimes its just 1 and sometimes it can be 2 or 3 or 10 . So how do i store this without creating rows like author1 ,author 2, ... author n . As for each conference papaer the number of authors will be different .How do i design the table , please help

conference paper table

authors table


texts: Use PL/pgSQL in PostgreSQL outside of a stored procedure or function?

texts: I'd really like to use an IF, ELSE construct and execute multiple statements within the IF and ELSE blocks, but I don't wish to save my commands to the database as a stored procedure or function. Can I do this in PostgreSQL?


texts: Is it possible to log ship from on-premise SQL server to Azure SQL Managed Instance?

texts:
From the docs, it would seem that restoring a full database backup file is possible, but can one also restore transaction logs?

(the idea is to minimize downtime in a migration from on-prem to MI).

Thanks.


texts: Can I do an impdp with a file on my local computer

texts: I was given a dump file which is currently on my local computer.  I want to import this to our Oracle 11G DB which is hosted on a server.  Do I need to move this file to the server to do the impdp or is there a way that I can do the impdp while referencing that the file is on my local PC?


texts: Is there a way to determine what kind of data is sitting in the Redo Queue for an AlwaysOn AG Secondary Replica?

texts: Do any of the DMVs or is there another way to expose what kind of data is in the Redo Queue that's in process of being synced up to a secondary replica? (E.g. is it table data and which table, or is it index changes and which indexes, etc?)


texts: How to improve Postgres performance using Prepare?

texts:
In this query, an inner join is repeated every time,
imagine billions of orders and hundreds of stores...

PREPARE findNextId(uuid) AS(
    WITH producedNextIds AS(
         WITH ordersOfStore AS (
        SELECT
             "order"."id", "order"."createdAt"
         From
             "order"
         INNER JOIN "site" ON "order"."storeId" = "site"."id"
        )
        SELECT "id" , "createdAt",
            LEAD("id",1) OVER (
                ORDER BY "createdAt"
                ) next_id
        FROM
            ordersOfStore
    )
        SELECT
            "id" , "createdAt", next_id
        FROM producedNextIds
        WHERE
            "id" = ($1)
);
EXECUTE findNextId('cb5c5bf0-f781-46e1-9ae1-68c875bb3a56');

does PREPARE make Postgres cache the join until the session ends up?
is there a more performant way of doing the query?


texts: Get the last 5 distinct values for each ID


texts:
I have the following query:

 SELECT
    RowNumber = ROW_NUMBER() OVER (ORDER BY ID DESC)
    , ID
    , Name
 FROM
    Clients
  WHERE
    CONTAINS (Name, 'Jose')

My client table has a single record with the name "Jose", so this query returns only one record. When I try to execute the query I get a timeout:

SELECT
    *
FROM
    (
        SELECT
            RowNumber = ROW_NUMBER() OVER (ORDER BY ID DESC)
            , ID
            , Name
        FROM
            Clients WITH(NOLOCK)
        WHERE
            CONTAINS (Name, 'Jose')
    ) AS RowConstrainedResult
WHERE
    RowNumber >= 1 AND RowNumber <= 2

However, if I change the WHERE clause as below, it works:

SELECT
    *
FROM
    (
        SELECT
            RowNumber = ROW_NUMBER() OVER (ORDER BY ID DESC)
            , ID
            , Name
        FROM
            Clients WITH(NOLOCK)
        WHERE
            CONTAINS (Name, 'Jose')
    ) AS RowConstrainedResult
WHERE
    RowNumber >= 1 AND RowNumber <= 1

I concluded that if the number of records returned by the query is less than the range I need, I will have problems. Does anyone know why? Is my conclusion is right?


texts: Losing customization capability with managed servers (AWS or Azure)?

texts:
We're currently hosting our SQL Server instances on AWS VMs. I was recently hired as a data architect and DBA. I'd like to move our instances to either managed services using AWS RDS or Azure SQL. I was told by our current CTO the following:

After having actually managed all this for several years now, I disagree [about the benefits of moving to a Platform-as-a-Service (PaaS) model]. I actually have experience with both "cloud managed" (on both AWS and Azure) and "self managed" and I have real concerns about performance (mostly I/O) on PaaS platforms. The level of flexibility and ability to tailor storage exactly how we need is something we just can't do with the PaaS services. With the ease of use, you lose almost all customization capability.

For those of you with experience using AWS RDS or Azure SQL, do we actually lose almost all customization capability? Most of our databases are under 1TB. In fact, I think our largest is currently at 680GB. Also, as an FYI, my colleague's greatest concern seems to be the performance gap between a managed environment and an instance sitting on a VM. The ease with which you can scale resources would seem to alleviate this concern.

Also, if this isn't the appropriate forum for a question like this, I would be glad to move it, if someone can tell me a more appropriate forum.


texts: SELECT NOT IN two foreign keys from other table

texts:
I am trying to find a SELECT query that returns all of tableA.tba_id that are not exists on tableB (note that are two different foreign keys - tbb_tbAID1 and tbb_tbAID2 between data.
You can find DB Model here

What I can do so far
This only tests one column

SELECT tba_id FROM tableA
WHERE tba_id NOT IN
  (SELECT tbb_tbAID1 FROM tableB
  WHERE tbb_date > '$date2')

I get a MySQL error when trying to test on both foreign keys

  #1241 - Operand should contain 1 column(s)

Query:

SELECT tba_id FROM tableA
WHERE tba_id NOT IN
  (SELECT tbb_tbAID1,tbb_tbAID2 FROM tableB
  WHERE tbb_date > '$date2')

Try 2:

SELECT tba_id FROM tableA
WHERE tba_id NOT EXISTS
    (SELECT 1 FROM tableB
    WHERE (tba_id = tbb_tbAID1 OR tba_id = tbb_tbAID2)
  AND tbb_date > '$date2')

But gets more results than the first try (that suggests that it's extremely wrongly done)

Try3:

SELECT tba_id
FROM tableA
  JOIN tableB ON tba_id!=tbb_tbAID1 AND tba_is!=tbb_tbAID2
WHERE tbb_data > '$data'

This results on enormous set of data, far away from first method.


texts:
Let's say for instance that I want to parse these addresses for the Chicken Ranch

Chicken Ranch
10511 Homestead Rd
Pahrump, NV 89061

Chicken Ranch
1600 Pennsylvania Avenue
NW Washington, D.C. 20500

In both of these cases, I'd like to get rid of Rd and Avenue. For instance in the first case, I'd like to get "Homestead", and in the second "Pennsylvania". Not every address has a designation like this though.


texts: Entity Relationship Design; The best scenario

texts:
TLDR; What would be the best design choice for the scenario below and how would each design react at extremely large data volume?
I have experience working with large government database systems servicing 24/7 data collection, processing and reporting. It was fun to poke around all the differently designed schemas and get an idea of how all these features designed by all these different people somehow mishmashed into a working solution. Like I'm sure a few of you know, fun poking times is something of a luxury and most cycles were spent pragmatically, keeping the system alive rather than improving the design.
I have been modelling a new database system and would like to get some thoughts on how you would go about relating these entities.

Scenario: Clients, employers and practitioners require phone numbers.
We have four entities:

CLIENT
EMPLOYER
PRACTITIONER
PHONE

Phone numbers are classified by the technology used to receive calls, technology used informs data format constraints.

Landline
Fax
Mobile

A description field indicates the primary usage of the phone; Home, work, etc.

Ways to relate these entities
1. Denormalize PHONE into CLIENT, EMPLOYER, PRACTITIONER
Let's start with bad design and go from there. De-normalize all the phone numbers!

3 entities, no relationships
Must create new columns if a new phone number is required; Leading to CLIENT.Phone1Number, EMPLOYER.Phone2Type, PRACTITIONER.Phone3Description;
or limit the number of phone numbers that can be entered for each entity
Redundancy, lots of overhead
Doing bulk-updates or maintenance would become tedious

Conclusion: select * from 'no_thanks';

2. Build the nth Bridge; Rename the database Pittsburgh
For each entity that can have a phone number related, create a bridge entity to bring them together.

7 entities, 6 relationships
Data redundancy; PhoneNumber populated in multiple locations
Can use bridge table if only PhoneNumber is required; Does not require JOIN to PHONE
If a new entity, FAMILYMEMBER, requires a phone number, a new bridging table must be created

Conclusion: Maybe, depends on the difficulty of maintaining the relationships

3. A Quasi "Star Schema"
Modify PHONE to include Foreign Keys of CLIENT.ClientID, EMPLOYER.EmployerID, PRACTITIONER.PractitionerID.

4 entities, 3 relationships
If one of these keys is populated, the others cannot be populated. (No case when a client, employer, practitioner would have the same phone number)
If a new entity, FAMILYMEMBER, requires a phone number, a new attribute must be created in PHONE
All FK attributes with NULL by default

Conclusion: Seems like the strongest design in my opinion


texts: Can a database restore fail after it has begun?

texts:
I've noticed that a lot of times if a database restore is going to fail, it will fail before it even starts restoring in SQL Server 2008 Management Studio (displaying an error about space or something similar). I need to set up a schedule to restore a database overnight, but am not sure if I'll need to monitor it overnight or not. The hardware is stable enough that I believe this won't be a point of failure.

A better question might be: What checks are done against a backup prior to the restore process beginning?


texts: Get the unmatched user clockin vs user table

texts:
The user table contains users' information. The clock_in table contains the clockin of the users every day. I have been trying to check who is absent or not yet clocked in.

For example by 9:00 am, I should be able to check who has not clocked in yet. I have came up with the MySQL query and this what I have come up with so far after about 7 days now searching. The idea is to compare the user table to the users clockin today, if there is an unmatched (IS NULL) then that user has not clocked in today. Here is my updated query so far.

SELECT users.*
FROM users
LEFT OUTER JOIN timecard
  ON users.id = timecard.employment_id
AND timecard.date='".$today."'
WHERE timecard.employment_id IS NULL and user.location = '".$location."'
ORDER BY users.dept DESC

Please, I know Left Outer Join may be the right one according to the results of my search, need your advice


texts: mongorestore Failed: an inserted document is too large

texts:
I have my remote mongodb database backed up and downloaded it to my local machine at /var/www/mongobackups3/

So I do this to insert/ restore it to the mongodb in my local machine:

$ mongorestore /var/www/mongobackups3/ --batchSize=10

But I got this error:

Failed: iotdb.datasets: error restoring from /var/www/mongobackups3/iotdb/datasets.bson: an inserted document is too large

I tried the solution from this question by adding --batchSize=10 but still no luck.

Any ideas what else I can do?

The full output:

2019-08-31T01:27:16.781+0200    preparing collections to restore from
2019-08-31T01:27:16.782+0200    reading metadata for iotdb.datasets from /var/www/mongobackups3/iotdb/datasets.metadata.json
2019-08-31T01:27:16.782+0200    reading metadata for iotdb.weathers from /var/www/mongobackups3/iotdb/weathers.metadata.json
2019-08-31T01:27:16.790+0200    restoring iotdb.datasets from /var/www/mongobackups3/iotdb/datasets.bson
2019-08-31T01:27:16.793+0200    reading metadata for iotdb.streams from /var/www/mongobackups3/iotdb/streams.metadata.json
2019-08-31T01:27:16.793+0200    reading metadata for iotdb.sessions from /var/www/mongobackups3/iotdb/sessions.metadata.json
2019-08-31T01:27:16.801+0200    restoring iotdb.weathers from /var/www/mongobackups3/iotdb/weathers.bson
2019-08-31T01:27:16.808+0200    restoring iotdb.sessions from /var/www/mongobackups3/iotdb/sessions.bson
2019-08-31T01:27:16.821+0200    restoring iotdb.streams from /var/www/mongobackups3/iotdb/streams.bson
2019-08-31T01:27:16.826+0200    restoring indexes for collection iotdb.streams from metadata
2019-08-31T01:27:16.831+0200    finished restoring iotdb.streams (93 documents, 0 failures)
2019-08-31T01:27:16.831+0200    reading metadata for iotdb.users from /var/www/mongobackups3/iotdb/users.metadata.json
2019-08-31T01:27:16.841+0200    restoring iotdb.users from /var/www/mongobackups3/iotdb/users.bson
2019-08-31T01:27:16.842+0200    restoring indexes for collection iotdb.users from metadata
2019-08-31T01:27:16.871+0200    finished restoring iotdb.users (2 documents, 0 failures)
2019-08-31T01:27:16.942+0200    restoring indexes for collection iotdb.sessions from metadata
2019-08-31T01:27:16.954+0200    finished restoring iotdb.sessions (2484 documents, 0 failures)
2019-08-31T01:27:17.141+0200    restoring indexes for collection iotdb.weathers from metadata
2019-08-31T01:27:17.196+0200    finished restoring iotdb.weathers (896 documents, 0 failures)
2019-08-31T01:27:17.836+0200    finished restoring iotdb.datasets (12 documents, 0 failures)
2019-08-31T01:27:17.837+0200    Failed: iotdb.datasets: error restoring from /var/www/mongobackups3/iotdb/datasets.bson: an inserted document is too large
2019-08-31T01:27:17.837+0200    3487 document(s) restored successfully. 0 document(s) failed to restore.

Notes:

I am on mongo v4.2.0. Ubuntu 19.04.
datasets.bson has 1.1 GB in size.
each document in datasets collection is 15MB max only.
I have 16 RAM in my local machine. Swap space is 2 GB.

What else I could have done wrong?


texts: Postgres extremely slow after upgrade from Version 10 to 11

texts:
Related to this:PostgreSQL extremely slow after upgrade from 9.3 to 9.4
but trying to understand better...

My queries was slow after upgrading to Postgres Version 11.5 from 10.7
I first ran ANALYZE on the DB but still was slow!
so I can VACUUM ANALYZE on the DB, and was still slow...

as a last resort, I am running VACUUM FULL ANALYZE on a table by table basis using psycopg2 with autocommit on (http://initd.org/psycopg/docs/connection.html#connection.autocommit) to be able to run it in psycopg2

one table is taking hours to do ...
while I wait, I can't help but wonder what went wrong.
The reduction of the query performance was mainly from UPSERT.


texts: Creating a function for a simple select statement?

texts:
I'm writing an application and I'll be running the following query:

SELECT Job_name,EMP,Lastname,FirstName from employe E
inner join JOBS F ON F.CODE_JOB=  E.CODE_JOB
inner join EMPS S ON S.Code_str = E.CODE_STR
where USER_ID = X

Does it make sense to actually make a function for it and send the USER_ID as a parameter or should I just run the query in the application? I'm just looking to know what are the best practices here.