texts: how to generate scripts for database objects specific to an application?

texts:
There was a application which, earlier, pointed to a database. It used a specific set of tables. Those tables had triggers, constraints, etc. There were also stored procs which insert data into these tables.

There was a new application built on the same lines (with minimal changes), but to make that application work in our test box, we had to backup the existing database, and restore that one with a different name. The new app database pointed to this newly restored db.

But the restored db has objects which were totally unnecessary.

Now I am faced with the task of creating deployment scripts that should run in our production box, and create the database (with the required objects for the new app to work).

Are there any tools that would make this job easier?

PS: My test box has a sql server 2005 db. Where as the production box has a sql server 2000 db.


texts: Cloning a SQL Server Virtual Machine with TDE

texts: When a virtual machine with a SQL Server database that uses TDE is copied or cloned, will the database be readable on the new instance?


texts: Why do we need to backup Oracle archive logs along with datafiles

texts:
Oracle® Database Backup and Recovery Basics 10g Release 2 (10.2) says that

  Archived redo logs are the key to
  successful media recovery. Back them
  up regularly.

But, I wonder, why backups of archive logs is so important.
Would it be possible to do point-in-time recovery just using regular RMAN full and
incremental datafile backups.


texts: SQL Server - is it ok to store link table as a heap?

texts:
I'm trying to understand if it's a good practice to have non-clustered PK on link table:

CREATE TABLE table1_table2(table1_id INT NOT NULL, table2_id INT NOT NULL,
CONSTRAINT PK_T1_T2 PRIMARY KEY NONCLUSTERED(table1_id, table2_id),
CONSTRAINT FK_T1_T2_T1ID FOREIGN KEY(table1_id) REFERENCES table1(id),
CONSTRAINT FK_T1_T2_T2ID FOREIGN KEY(table2_id) REFERENCES table2(id)
);
CREATE INDEX IDX_T1_T2 ON table1_table2 (table2_id);

My point here that heap is ok because

No particular order during INSERT, so in case of clustered PK I'll have many page splits.
All SELECTs will use just index anyway

In my understanding, adding identity clustered PK will add more overhead for such tables (absolutely no data except foreign keys).

Could you please clarify if that makes sense ?

Thanks.


texts: Is it possible to generate table with generated data without manual INSERT statements?

texts:
Is it possible to use SQLCMD in SQL Server 2008 to create a table and generate data and insert into the table, without manually typing the INSERT statement for each row?

Example:

Suppose there are 10 weeks (1, 2, 3 ... 10) and 2 timings (11 am, 12 pm) and 5 venues (Room 1, Room 2 ... Room 5). Thus there will be a total of 10 x 2 x 5 = 100 rows.

So, is it possible to have a SQL statement that will generate these 100 rows when given the 3 data parameters (week, timing and venue)?

+--------------------------------+
| Week    |   Time   |   Venue   |
+--------------------------------+
| 1       |  11 am   |  Room 1   |
| 1       |  12 pm   |  Room 1   |
| 1       |  11 am   |  Room 1   |
|                                |
| 10      |  12 pm   |  Room 5   |
+--------------------------------+


texts: Concatenating rows into a single string query running for 5 hrs and counting

texts:
I have a table with 2.6m records. It looks like this:

email                           prject_name
rafael.nadal@xyz.com              lab1
rafael.nadal@xyz.com              lab2
rafael.nadal@xyz.com              lab3
TEST@TEST.COM                     shift1
TEST@TEST.COM                     shift2

But I want my table to look like this:

email                     project_name
rafael.nadal@xyz.com     lab1, lab2, lab3
TEST@TEST.COM            shift1, shift2, shift3

I have used this query

select distinct email ,
STUFF((Select ','+project_name
from dbo.[UMG sent 2016] as  T1
where T1.email=T2.email
FOR XML PATH('')),1,1,'') from dbo.[UMG sent 2016] as T2;

It has been running for 5 hours already.
How do I speed up the process?


texts: Better diagnostics when starting ndb nodes?

texts:
I have an ubuntu linux server that uses mysql cluster 7.2.5 x64.

Whenever I start an ndb node and check with ndb_mgm -e show, it stays in "starting" state for a long time. When I use top to check the process it takes 70-90% CPU which means it's doing something.

Is there  a way to check what it's doing and estimate when it's going to finish?


texts: Query to compare 2 table results and filter extra records found in a table

texts:
I have two queries that I want to compare and get the extra rows found in in the first query that are not in the second plus 1. I need the DocEntryCN to perform some work.

E.g.
Query 1

SELECT DocEntry AS "DocEntryCN" , Comments, JrnlMemo, DocTotal
FROM ORIN WHERE DocDate BETWEEN '10-27-2012' AND '10-29-2012' AND Comments IS NOT NULL
ORDER BY DocTotal ASC, Comments

Query 2

SELECT DocEntry AS "DocEntryINV" , Comments, DocTotal
FROM OINV WHERE DocDate BETWEEN '10-27-2012' AND '10-29-2012' AND Comments IS NOT NULL
ORDER BY DocTotal ASC, Comments

Sample Results:
Query 1

DocEntryCN    Comments    JrnlMemo      DocTotal
67            reverse     kaka-19       500
69            reverse     kaka-19       500
70            reverse     kaka-19       500
71            reverse     kaka-19       500
74            reverse     kaka-19       500
75            reverse     Rob-23        750
77            reverse     Rob-23        750
78            reverse     Rob-23        750
79            reverse     Rob-23        750
84            reverse     Paul-17       1250
86            reverse     Paul-17       1250
87            reverse     Paul-17       1250

Sample Results: Query 2

DocEntryINV   Comments       DocTotal
1             kaka-19        500
3             kaka-19        500
5             kaka-19        500
6             kaka-19        500
7             Rob-23         750
9             Rob-23         750
10            Alex-09        1000
11            Olma-08        1150
12            Paul-17        1250
13            Paul-17        1250
16            Paul-17        1250
17            Rita-99        1300
19            Rita-99        1300

Desired Results

DocEntryCN    Comments    JrnlMemo      DocTotal
67            reverse     kaka-19       500
69            reverse     kaka-19       500
75            reverse     Rob-23        750
77            reverse     Rob-23        750
78            reverse     Rob-23        750
84            reverse     Paul-17       1250
90            reverse     Rita-99       1300

In the first query, there are 5 duplicates for "kaka-19". In the second query, there are 4 duplicates for the same "kaka-19". I would like my results be as above where (5-4)+1 = 2 for that "kaka-19" and so forth.

NB. Comments in query 2 stores Invoice numbers that should be unique. If any two comments in the OINV table are the same, it means there is a duplicate error. The JrnlMemo field in the ORIN table stores the OINV invoice numbers. For any invoice repetitions no. k, the correct number of credit notes to reverse should be k - 1.

How do I build a query for the desired results?


texts: How do I skip an offline database over the network in MySQL import?

texts:
I am doing a MySQL import from an *.sql file to multiple database location over the network. I'm using cmd batch file to run the command and it's working as I expect it to be. However I notice that the import took longer times on any IP that is offline compared to the ones that is online. Below are the duration it took for both processes:

If online : 7-8 secs average/process
If offline : 40 secs average/process

Each of the process is performing two command, one is to import an *.sql file and the other is referring a *.txt file to update a table in the same location I imported the *.sql file.

Command 1:
mysql.exe -h 192.xxx.xxx.xxx -P 3306 -u root -pxxxx db < C:\xxx\data\xxx.sql

Command 2:
mysql.exe -h 192.xxx.xxx.xxx -P 3306 -u root -pxxxx db < update.txt

These commands is repeated about 20 times in one batch file and the possibility of getting an offline database is high. If I'm lucky, I'll get only one or two offline location but to get all database online at once is highly unlikely. The *.sql file is just about 45MB in size so import is actually quick.

My question is, how do I skip the offline location?


texts:
Can mariadb-galera cluster be configured without data replication but simply in failover mode? I envision such configuration as one active master server and one backup server, both using the same mounted ScaleIO volume ( already with built-in data redundancy)...Is such scenario possible?
Thank you,


texts: How to get a breakdown of CPU usage at each point in time?

texts:
I have a SQL Server RDS instance, running on a db.m5.xlarge (4 vCPU, 16GB RAM) with Multi AZ enabled.

I am trying to determine the cause of a constant 25%-30% CPU load on the instance at a time when we know that there is very little client activity hitting the database. We've taken into account our most expensive queries and feel that they are not the cause. Regular DB maintenance such as index and statistics rebuilds has been done.

The image below is a graph on a CloudWatch dashboard, monitoring database connections and CPU usage. Is there a way I can query or visualise the breakdown of CPU usage at each data point in the graph? (I'm happy to dive in and run manual queries if that is what is suggested).


texts:
I"m looping through a table trying to create a dynamic sql query. It appears to work fine except when I'm concatenating results to make the actual statement. Psudo code follows

WHILE (Data is in the table)
BEGIN
SELECT TOP 1 @result = result from table

@sql = @sql + ', ' + @result

END

If I look at @sql in each loop I see the expected results, but when the loop is finished @sql is empty. Can someone explain why?


texts:
Is it possible to attach metadata to a stored procedure in Postgres 11?
In particular, I have database migrations with version numbers. So when my migration script runs and might override the current stored proc with CREATE OR REPLACE PROCEDURE, I'd like to be able to attach metadata to the stored proc, with the a newer version number.
Also, how can this be queried afterwards?


texts: I followed this link to install SQL Server 2014. When I clicked on "New installation or add features to an existing installation" instead of proper box, I have to choose "Browse For Folder". When I choose folder where my installation is, nothing happens. Any idea why is this happening?


texts: SSIS: To run the package outside of SQL Server Data Tools you must install Integration services or higher

texts:
I try to launch a dtsx package from Cmd using "dtexec".

The process begins and no error encounted. But at the end I have the error message

  SSIS: To run the package outside of SQL Server Data Tools you must install expression task of integration services or higher

But I  checked the "Integration Services" component before installing SSDT.
So I don't understand why I have still this error.

When i launch the process from Visual studio, everything is fine.

Could you advice please?

Thanks in advance.


texts: Can I use Azure Data Lake in place of Data Warehouse to separate Compute and Storage of RDBMS analytics

texts:
I am from a telemetry data engineering background and am looking for some help on a use-case I am not familiar with as to whether my proposed architecture is practically feasible in Azure. The use-case is that I am trying to build some intelligence from a relational database that is updated regularly.
Currently, a data warehouse is in place which uses ADF to copy database tables in 3NF to some staging tables. The staging tables are then used to upsert to their respective tables in the 'Data Warehouse'. No denormalisation has been done. This has been pushed to the end-user to do in PowerBI. I inherited this.
The solution is expensive and places high demand on the end-user analysts. I wanted to use a Kimball style approach and have a fact table with some dimension tables. In order to reduce costs, I want to do this in Azure Data Lake and then use DataBricks to carry out the denormalisation and store the resulting outputs in a separate zone in Data Lake. I would then take the daily snapshots of the database tables and update the fact and dimension tables where data has changed/been added. (I still need to determine how to determine the daily changes without a watermark in the source tables).
I do not see any reference architectures where this is done. It seems the data is always put into Synapse or SQLDB, even if it does make a stop in Data Lake. In order to reduce the costs would my approach be ok where I use Data Lake and DataBricks for everything (Staging, denormalisation, and serving the fact/dim tables)? The analysts would then have a much simpler model to connect up to PowerBI and use Dataflows to carry out their aggregations.
Any advice would be greatly appreciated.


texts: using Change Tracking functions from another database

texts:
If I turn on change tracking on a database called Production, can I use the functions to get the change data from another database?

For example: CHANGE_TRACKING_CURRENT_VERSION();

I have tried the following, and also tried replacing the schema with sys but I don't think this approach is working:

Use [Analysis]
GO
SELECT Production.dbo.CHANGE_TRACKING_CURRENT_VERSION()

I have worked around it by creating a stored procedure on Production that calls this function. Then I can call that stored procedure from other databases on that SQL Server. But would like to know if there is a more direct way.

I am trying to migrate data from one system to another (over a period of a week or two, then shut down the old system). Change tracking is enabled on the other database. I am limited by the version of SQL Server, CDC requires Enterprise, Developer or Evaluation.


texts: Use single table with foreign key or two twin tables for text entries?

texts:
I have a table answers that has several columns including the content iself such as

ID | q_id | list_id | user_module_id | content | updated_timestamp

The content column is a varchar since we want to be able to search through the answer content quickly.

However, we also want to be able to store full text as answers. There are two approaches:

A.  Using two "twin" table (with the same columns names) : a table with the column content of type varchar and a table with the column content of type text.

B. Using a single answer table and storing in the content column a hash key such as q_id7user_module_id362 and use another table that contains only the text entry:

hashkey | content

This approach seems cleaner, however it means doing two UPDATEs AND INSERTs for each entry modification and a JOIN on the two table to pull out the information.

In general is this bad practice to have "twin" tables in a database?

Thanks!


texts: Can a TVF use multiple CTE statements internally?

texts:
I'm trying to make a multi-statement table valued function and failing due to what I think is a limitation of functions and CTEs.

The intended function defines an input parameter(@Param) and and output table(@ResultTable).

It then executes a complex insert statement involving a CTE into that table variable which is (of necessity) terminated by a semicolon.

I then attempt another complex update statement to that table variable involving a CTE and receive an error saying that "Must declare the scalar variable "@ResultTable". Apparently it has dropped out of scope somehow.

I have used this sort of pattern in the past, so my only thought is that the CTEs seem to limit the scope in some way. Is this a known limitation?


texts: What are the performance implications of using uuid as primary key in Postgres 10.12? (need canonical answer)

texts:
I'm at a crossroads where I need to decide if I'm going to stick with bigserial as my primary key, or change to uuid (non-auto-generating—my API server will generate the ID using uuid v4 and insert it).
I've spent hours researching bigserial vs uuid primary keys, and it seems no one can agree on what the disadvantages of uuid would be (if any). My database isn't that complicated: it's a series of tables with pretty basic relationships, I'm typically only inserting one row at a time, I have a handful of jsonb fields I'm using here and there. Write speed/frequency will only be significantly high on one table in particular.
The reason I started looking into UUIDs wasn't because I think I'll ever run out of bigint keys (9 quintillion, if I recall), but more from a standpoint of obfuscation. Right now I'm having to hash the IDs on the front-end to avoid showing the user DB IDs in the URL (e.g. /things/2732). Using Hashids, I can instead have URLs like /things/To2jZP13dG. But I thought I could go a step further and just use UUIDs which don't give any clues as to # of records. What I don't like is the added effort of having to encode IDs before passing them to the back end and decoding them there, and then when querying batches of 50-100 items to return to the client, having to mass encode all those IDs before returning them to the client.
One argument for random UUIDs (uuid v4) was:

If your primary key is an incrementing ID, those are stored physically next to each other. That database page can have contention as many people are writing to it.  Random IDs prevent contention by spreading the writes all over the DB

But then I found a contradictory statement here:

Regular random UUIDs are distributed uniformly over the whole range of possible values. This results in poor locality when inserting data into indexes - all index leaf pages are equally likely to be hit, forcing the whole index into memory. With small indexes that's fine, but once the index size exceeds shared buffers (or RAM), the cache hit ratio quickly deteriorates.

I know the folks at Heroku are fans of using UUIDs as primary keys. For whatever it's worth, I wasn't planning on having Postgres auto-generate the IDs at all. Instead, my API server would generate a v4 UUID and pass that to the database (this would allow my API server & front end client to be more efficient and not have to always use RETURNING id statements in my queries).
Does anyone have a canonical answer on the true cost of INSERT statements when using uuid as the primary key?


texts: ORA-04020: deadlock detected while trying to lock object - Oracle Package

texts:
I have an Oracle Package (Schema1) that is calling another Oracle Package (Schema2) over a DB link. There are no recent code changes.

When I try to compile the package in (Schema1) it throws the following errors.

(replaced the actual schema names with 1 and 2 for clarity)

  ORA-04052: error occurred when looking up remote object
  SCHEMA2.PKG_NAMEHERE@SCHEMA2.LINKNAME.INT@SCHEMA1

  ORA-04020: deadlock detected while trying to lock object
  SCHEMA2.PKG_NAMEHERE@SCHEMA2.LINKNAME.INT@SCHEMA1

What we have tried

Killed all user sessions
Recompiled the package in SCHEMA2 even so SCHEMA1 that calls it over the DBLINK failes with "ORA-04020: deadlock detected"

We have 4 Database regions, and this is only happening in two of the regions.

Notes:

SCHEMA1: Oracle 11g

SCHEMA2: Oracle 12c (on ExaData)


texts: Unable to view "Maintenance Plan" folder under Maintenance in SQL Server 2008 R2 Standard Edition

texts:
SQL Server version is as follows :

  Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)
  Copyright (c) Microsoft Corporation
  Standard Edition on Windows NT 5.2  (Build 3790: Service Pack 2)

In SQL Server Management Studio, under the Management node, I am unable to view the "Maintenence Folder" node.  I am logged in as sa, and the account does have the sysadmin server role.

How do I get access to the "Maintenance Folder"?


texts: Selecting the same column from Different rows Based on Different Criteria

texts:
My table creates a row for each customer name for the unique customer number.

CREATE TABLE #src(Number int, name varchar(32), seq bit);

INSERT #src(Number,name,seq) VALUES
(12345,'Mickey Mouse',0),
(12345,'Minnie Mouse',1),
(45678,'Donald Duck',0),
(45678,'Daphney Duck',1),
(245678,'Pluto Dog',0);

I need to be able to return a single row with multiple name columns based on the "Seq" number. It will always either be a 0 or a 1 and the Seq 1 can sometimes be blank.


texts: How can i detect whom a person reports to using a self-referencing employee table?

texts:
I have a typical self-referencing employee table. The data is hierarchical and I use the following UDF to encapsulate a simple CTE to derive an output of a particular person's direct and indirect reports. That is to say I can pass in a single employee and get back a listing of them and everyone under them.

I want to create a similar function that would allow me to take a boss and employee param and detect if the employee reports into the boss (or the boss param is indeed the boss - direct or indirect of the employee)

whats the best way to accomplish this logic using the DB structure outlined below as what I have today?

CREATE TABLE [dbo].[Employees](
    [EmployeeId] [int] NOT NULL,
    [managerId] [int] NULL,
    [FirstName] [nvarchar](50) NOT NULL,
    [LastName] [nvarchar](50) NOT NULL,
    [FullName] [nvarchar](100) NOT NULL,
    [CorpEmailAddress] [nvarchar](510) NULL
) ON [PRIMARY]

ALTER FUNCTION [dbo].[fnGetEmployeeHierarchy]
(
  @EmployeeId int = null
)
RETURNS TABLE
AS
RETURN
(
  WITH yourcte AS
  (
    SELECT EmployeeId, ManagerID, AMRSNTID, FullName--, Name
    FROM Employees
    WHERE EmployeeId = isnull(@EmployeeId,EmployeeId)
    UNION ALL
    SELECT e.EmployeeId, e.ManagerID, e.AMRSNTID, e.FullName--, e.Name
    FROM Employees e
    JOIN yourcte y ON e.ManagerID = y.EmployeeId
  )
SELECT EmployeeId, ManagerID, AMRSNTID, FullName--, Name
FROM yourcte
)


texts: Starting postgresql service and specifying data directory

texts:
I have recently installed pg 8.4 on Ubuntu 10.0.4 using apt-get install.

It installed pg as a service which starts ehenever the machine starts. However I have created a db cluster in a specific location that I want postgres to use.

This is the command line output of how I created the cluster:

username@jupiter:~$ sudo /usr/lib/postgresql/8.4/bin/initdb --encoding=UTF8 --pgdata=/mydata/pgdbdata/
initdb: cannot be run as root
Please log in (using, e.g., "su") as the (unprivileged) user that will
own the server process.
username@jupiter:~$ /usr/lib/postgresql/8.4/bin/initdb --encoding=UTF8 --pgdata=/mydata/pgdbdata/
The files belonging to this database system will be owned by user "username".
This user must also own the server process.

The database cluster will be initialized with locale en_US.utf8.
The default text search configuration will be set to "english".

fixing permissions on existing directory /mydata/pgdbdata ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 24MB
creating configuration files ... ok
creating template1 database in /mydata/pgdbdata/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the -A option the
next time you run initdb.

Success. You can now start the database server using:

    /usr/lib/postgresql/8.4/bin/postgres -D /mydata/pgdbdata
or
    /usr/lib/postgresql/8.4/bin/pg_ctl -D /mydata/pgdbdata -l logfile start

username@jupiter:~$

Now, I try to start the postgresql service:

Here is the command line output:

username@jupiter:~$ /usr/lib/postgresql/8.4/bin/pg_ctl start -D /mydata/pgdbdata/
server starting
username@jupiter:~$ FATAL:  could not create lock file "/var/run/postgresql/.s.PGSQL.5432.lock": Permission denied

So I have two questions:

How can I start the service and specify a data location (what am I doing wrong?)
I want the service started when the machine starts/reboots, to use the new data directory. Which file do I need to edit to ensure that I always have the service using the correct data folder?


texts: How to write a wrapper for a heterogeneous VARIADIC function like jsonb_build_object()?

texts:
I am just wondering how to write a wrapper function in PostgreSQL (as of 12.x) for a heterogeneous VARIADIC function such as jsonb_build_object?

Most SQL examples of VARIADIC functions I can find are not heterogeneous. The VARIADIC part is essentially an array of elements of the same type. But I need to write a wrapper for jsonb_build_object to do some post-processing. Here, the parameter types in the variadic part (key-value list) are not the same.

Currently, I write:

my_func(jsonb_build_object(k1, v1, k2, v2, ...))

But this boilerplate is becoming boring ...


texts:
I need to manually run a job on more than 150 sql server instances (sql server 2000, remote) from a sql server 2005 instance (the local server). The job is the same on all these instances. The job just calls a stored procedure without parameter, which is also the same across all the instances. These jobs are on a schedule. But now they want me to manually run the job for all the instance or for specified instances upon request.

What is the best practice for this? I have tried openrowset to call the remote stored procedure. But each run of the job takes couple of minutes, so if I use a loop to run all these jobs, it will run one by one and that's a long time. Ideally, it should be able to run the stored procedure on each instance without waiting for it to finish. More ideally, it should be able to run the job on each instance without waiting for it to finish, so it can leave a record in the job history on each instance.

And the stored procedure is from a third party so it can't be altered.

update:

since the 'people' ask this to be initialised from a SSRS report, use SSRS to call some T-SQL/proc on the server would be most appropriate. The problem I got now is when calling msdb.dbo.sp_start_job on remote SQL Server 2000 instances from local server using OPENQUERY or OPENROWSET I got Cannot following error.

process the object "exec msdb.dbo.sp_start_job @job_name = 'xxx' ". The OLE DB provider "SQLNCLI" for linked server "xxx" indicates that either the object has no columns or the current user does not have permissions on that object.

I guess this may because the sp_start_job doesn't return anything because I can use OPENQUERY/OPENROWSET to call other remote proc without problem. So any workaround?

update:

I have found it actually pretty simple in t-sql.

EXEC [linkedServerName].msdb.dbo.sp_start_job @job_name = 'test2'

So I don't need to use OPENROWSET/OPENQUERY atually since all the remote SQL Server 2000 instances are already added as remote servers.


texts: I have user table in my DB. I have 1 excel sheet having list of some users (not all) with changes. How can I Update the values given in excel sheet to my user table? is it possible with import data option?


texts: Availability groups multiple instance, multiple listeners

texts:
In my work environment, I have two database machines:

DB01
DB02

Each database machine has three SQL Server instances:

MSSQLSERVER
FBG
Central

I've managed to set up availability groups with SQL Server listener for the 1st SQL Server instance on both machines, and created SQL Server replication and it's working fine.
Now, I would like to set up a new availability group for the second SQL Server instance FBG on both servers, and I did, but the problem is when I create the listener for the second availability group it goes for the first availability group, I must type listener name / instance name to get it working.
I wish to type only the availability group listener name. For example:

the first availability group name is AVG01 and its listener AvgList01
the second availability group name is AVG02 and its listener AvgList02

When I connect to AVG02 using the name AvgList02 it connects me to AVG01
although I'm not using its listener name.
I must use it this way to get it working AvgList02\FBG where the FBG is the name of the SQL Server instance.
The question
Where is the issue, and how to fix it?


texts: Create a Function in sql server without specifying return_data_type

texts:
I am trying to create a Scalar-valued function in SQL server, without specifying the return_data_type

i want to be able to return any datatype based on a parameter sent by the function caller

e.g.

CREATE FUNCTION GenerateRandomData
(
    @Datatype varchar(20)
)
RETURNS <Return any datatype specified by @Datatype parameter>
AS
BEGIN
    -- Declare the return variable here
    IF @Datatype in ('varchar','char','nchar','nvarchar','text','ntext')
        Begin
            RETURN 'String'
        end
    Else
    IF @Datatype in ('int','bigint','smallint')
        Begin
            RETURN 100
        end
    Else
    IF @Datatype in ('datetime','smalldatetime')
    Begin
        RETURN getdate()
    end

END


texts: MariaDB 10.1.38 - Specified key was too long; max key length is 767 bytes

texts:
I have a table that has 3 columns:

id(int 11)  | user_id(int 4) | title(varchar 512)
____________|________________|___________________
     1      |        3       | Thing X
     2      |        3       | Something Else
     3      |        5       | Thing X

And i need to make a unique combination between user_id and title. For that i'm using this simple query:

ALTER TABLE posts ADD UNIQUE `unique_post`(`user_id`, `title`)

The charset for the title column is utf8mb4_unicode_ci. The database was created using the charset utf8mb4:

CREATE DATABASE learning_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

And the table type is InnoDB.

I've tried to set a few globals:

SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=on;
SET GLOBAL innodb_large_prefix=on;

But i'm getting the same error 1071:

Specified key was too long; max key length is 767 bytes

Is there anything else that i might be able to do?

P.S: if i set to index only the first 191 characters of the title i don't get that error:

ALTER TABLE posts ADD UNIQUE `unique_post`(`user_id`, `title`(191))

...but i have titles related to the same user that might be different only at the very last letter(aka last 4 bytes considering my charset)

P.S.S: i'm on localhost (using xampp).


texts: MySQL constraint to prevent combinations of columns

texts:
I have a table that represents files in directories.
dir   | file
----------------
dir_A | file_0
dir_A | file_1
dir_A | file_1
dir_B | file_2
dir_C | file_3
...   | ...

I want to put a constraint on that table to make sure a file can never be in 2 directories. But the same file can be in the same directory multiple times.
Example of permitted condition:
dir_A | file_1
dir_A | file_1
dir_B | file_2

Example of failed condition:
dir_A | file_1
dir_B | file_1
-- should be prevented, because `dir_A file_1` exists
-- same file, but different directory

The question has been asked for PostgreSQL with solution:
CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE table_name
    ADD CONSTRAINT only_one_dir_per_file
        EXCLUDE USING gist ( file WITH =, dir WITH <> );

I was wondering if the same could be done with MySQL?


texts: SQL session that is 'never end', always stay in a running status but does not return a result set (Until I kill it)

texts:
I am a beginner SQL DBA and I experienced a weird thing in SQL server.

Symptom:

Session is in Running state (not runnable, not suspended).
CPU time is zero, or if it is not, after a while it stops growing.
Reads\Writes\Logical Reads are zero, or if is not, after a while they stop growing as well.
No result set that is returned.
They will run until I kill all these sessions. (These can be days, but these queries run within 3 mins usually)

More info:

This is static database.
Indexes are not fragmented.
Statistics are updated
CPU is not under stress, used 10% only
Memory is more than enough
SQL Server 2016 is used

Is there any idea what can be the reason for this behaviour?

I'm very grateful for all the answers.


texts: How to set SSIS flat file connection row delimiter to blank line?

texts:
I have a text file with the following format:

DataColumn1
DataColumn2
DataColumn3

DataColumn1
DataColumn2
DataColumn3

DataColumn1
DataColumn2
DataColumn3

Each row with text in the file delimits a column of data, and each row with no data delimits a row.

I'm trying to import this data into an MS SQL database table using SSIS, but I'm struggling with the flat file connection manager being able to understand this delimiter formatting the upstream process I'm depending on gives me.


texts: Does MySQL Workbench lock database during migration process?

texts: I am doing a migration of my database from one server (RDS) to another (localhost) via the MySQL workbench tool. I want to know if during this process my database will be locked for write and read operations? The type of my tables is InnoDB.


texts: How to create trigger to update timestamp when record is updated or inserted in another table

texts:
I have a table t_task and a table t_taskaction with a foreign key to t_task.
I would like to update timestamp from t_task that is refering the foreign key form t_taskaction with every update in action.

It doesn't get any udpate in t_task table, and I'm not getting any error.

USE `myTask`;
DELIMITER $$
CREATE TRIGGER `t_taskaction_AUPD` AFTER UPDATE ON `t_taskaction` FOR EACH ROW
BEGIN
    DECLARE idTask INT(11);
    DECLARE idTaskState INT(11);

    set @idTask = NEW.idTask;

    set @idTask = (SELECT idTaskState
    FROM t_task WHERE idTask = @idTask);

    UPDATE t_task
    SET idTaskState = @idTaskState
    WHERE idTask = @idTask;
END$$
DELIMITER ;

Tables

CREATE TABLE IF NOT EXISTS `myTask`.`t_task` (
  `idTask` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `task` VARCHAR(100) NOT NULL,
  `idTaskState' INT(11) UNSIGNED NOT NULL DEFAULT 1,
  `description` LONGTEXT NOT NULL,
  `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`idTask`)
)

CREATE TABLE IF NOT EXISTS `myTask`.`t_taskAction` (
  `idTaskAction` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `idTask` INT(11) NOT NULL,
  `fecha` DATETIME NOT NULL,
  `action` VARCHAR(45) NOT NULL,
  `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`idTaskAction`, `idTask`),
  CONSTRAINT `t_ifocTareaAccion2t_ifocTarea`
    FOREIGN KEY (`idTaskAction`)
    REFERENCES `myTask`.`t_task` (`idTask`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)

CREATE TABLE IF NOT EXISTS `myTask`.`a_taskState` (
  `idTaskState` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `order` INT(11) NULL DEFAULT NULL,
  `state` VARCHAR(45) NOT NULL,
  `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`idTaskState`))


texts:
If i join two/more complex views (views that are made up by joining two/more tables); would it result in multiple iteration of reads on the base tables?

Would it not be more efficient just to have the join conditions on the base tables itself?

This is a problem i've analysed in our Production Oracle 10g R2 (ASM disk groups) environment, the sql queries are dynamically generated on the fly by the application itself and instead of using Tables to extract results, composite views are used.Moreover, conditional joins are frequently implemented on these composite views.

Can such joins on the complex views unnecessarily cause high cpu utilization and also eat up the precious buffer cache?


texts: Two kinds of products and one order table

texts:
I have  the following tables: tblCustomer, tblProductA and tblProductB. I have also an order table
tblOrder. Each order consists of a customer and one or more products from tblProductA and one or more products tblProductB.

The standard case is when we have only one table for products, and we usually use a join table to connect orders and products.

However, in the case I am bringing, it is mandatory to have two tables for products (tblProductA and tblProductB). How do I model this case?


texts: How to define and query in case of table with Mutually exclusive one-to-many relationships

texts:
Hi I'm the following situation.

I have 5 tables

Entity
------------
idEntity
entityName
...
...
idCountry_fkey
idRegion_fkey
idAggregate_fkey

Country
---------------
idCountry
countryName

Region
---------------
idCountry_fkey
idRegion
RegionName

Aggregate
---------------
idAggregate
AggregateName

Aggregate_to_Country
----------------------
idAC
idCountry_fkey
idAggregate_fkey

I enforced mutual exclusivity with the following constraint con the Entity Table

    CHECK (
    (("idAggregate" IS NULL) AND ("idRegion" IS NULL) AND ("idCountry" IS NOT NULL))
    OR
    (("idAggregate" IS NULL) AND ("idRegion" IS NOT NULL) AND ("idCountry" IS NULL))
    OR
    (("idAggregate" IS NOT NULL) AND ("idRegion" IS NULL) AND ("idCountry" IS NULL))
);

I've the following questions:

  is this the proper way to handle this situation
  How do I query in order to have:

index | entityname | Aggregate | Country | Region | Value
----------------------------------------------------------
case1 |  A         |  Eurozone |  null   | null   | 1
----------------------------------------------------------
case2  |  B        |    LATAM  |  Brazil | null   | 22
----------------------------------------------------------
case3  |  C        |  Europe   |  Italy  | Lazio | 44

Case 1) when Entity is related to Aggregate
Case 2) When Entity is
related to Country, and the Country is in an Aggregate Case
Case 3) When Entity is related to Region, and the Region is Related To a Country, and the Country is in an Aggregate


texts: Sqlserver cannot write large text to excelfile with ACE, using IMEX=1, result=object is read-only

texts:
I have a table Test with column id (int) and column label (varchar(512)) and an excelfile test.xlsx with sheet test and the two columns. I use the ACE-provider to write from sqlserver to the excelfile. There are values longer than 255 characters in the column "label".

Scenario 1

IMEX=0, no values longer than 255 characters; I trunc label to 255 characters

insert into OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;Database=D:\test\test.xlsx;HDR=NO;IMEX=0;ReadOnly=FALSE',
    'SELECT * FROM [test$]')
SELECT id,
       cast(label as varchar(255)) label
FROM   Test

This works fine.

Scenario 2

IMEX=0, values longer than 255 characters

insert into OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;Database=D:\test\test.xlsx;HDR=NO;IMEX=0;ReadOnly=FALSE',
    'SELECT * FROM [test$]')
SELECT id,
       label
FROM   Test

Since there are values longer then 255 characters in label I get the message

  String or binary data would be truncated.

This is normal for IMEX=0.

Scenario 3

IMEX=1, no values longer than 255 characters

insert into OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;Database=D:\test\test.xlsx;HDR=NO;IMEX=1;ReadOnly=FALSE',
    'SELECT * FROM [test$]')
SELECT id,
       cast(label as varchar(255)) label
FROM   Test

I get this message:

  OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"
  returned message "Cannot update. Database or object is read-only.".
  Msg 7399, Level 16, State 1, Line 5 The OLE DB provider
  "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an
  error. The provider indicates that the user did not have the
  permission to perform the operation. Msg 7343, Level 16, State 2, Line
  5 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server
  "(null)" could not INSERT INTO table "[Microsoft.ACE.OLEDB.12.0]".

Scenario 4

IMEX=1, values longer than 255 characters

insert into OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;Database=D:\test\test.xlsx;HDR=NO;IMEX=1;ReadOnly=FALSE',
    'SELECT * FROM [test$]')
SELECT id,
       label
FROM   Test

I get the same message:

  OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"
  returned message "Cannot update. Database or object is read-only."
  etc.

Now I made sure the excel-file has all the persmission for the users (sqlserver-service-user, user running the script, ...) involved (otherwise it wouldn't write any lines in the very first scenario with imex=0), I even tried with "everyone", no succes.

My guess is, if IMEX=1 is involved, there is another folder or file that needs acces?

Note that I already gave read/write permission to the user running the query on "C:\Users\userThatRunsServiceOfSqlServer\AppData\Local\Temp".

Any suggestions?

Sql server 2012 sp3 / Excel 2010


texts:
I have two tables

ID  Task
1   1
2   2
3   3
4   4

Col1  depend
2     3
2     4
3     1
4     2

ID and Col1 are related through FK constraint. I want to find out all circular references. Here ID and Col1 is just for combining rows from 2 tables, e.g.:

Task 1 can start anytime.
Task 2 can start only after completion of 3, 4 etc

1 –
2 – 3, 4, 1, 2   -- for 2 there is circular dependency
3 – 1
4 – 2, 3, 4      -- also circular dependency

Case 2:

Col1  depend
2     3
2     4
3     1
4     5
5     2

ID  Task
1   1
2   2
3   3
4   4
5   5

1
2 – 3, 4, 1, 5, 2     -- circular reference
3 – 1
4 – 5, 2, 3, 4        -- circular reference
5 – 2, 3, 4, 5        -- circular reference

Circular references may be available at any recursion level. How to find such circular references?
We have tried a recursive query but we went in infinite loop. How to write recursive query for this?


texts: Transactional Replication - table does not have the identity property

texts:
I have a one way transactional replication set up between two 2005 MS Sql servers.  Recently I have started to receive the following message in the replication monitor from the subscriber:

  Table 'dbo.tblname' does not have the identity property. Cannot perform SET operation. (Source: MSSQLServer, Error number: 8106)

Will adding the identity column to 'dbo.tblname' on the subscriber resolve this error or do I need to drop and re-add the subscription?


texts: Trigger working on more than 50 million records

texts:
I have a table with more than 50,000,000 records. The table doesn't have any partition. The table is in PROD.

I need to create a trigger there which will update previous entries flag to false for each upcoming latest entry. Each upcoming latest entry will have true value on flag.

The table has around 1300 inserts per minute. One of the column of that table contain json data as well.

Here is the trigger script:

CREATE TRIGGER trig_updaterow
before insert on tab
    for each row execute procedure updaterow();

CREATE OR REPLACE FUNCTION updaterow()
RETURNS trigger AS
$$
BEGIN

    update tab
    set    flag ='0'
    where  s = new.s
           and p = new.p
           and c = new.c
           and m = new.m
           and e = new.e
           and flag = '1';

    RETURN NEW;

END;
$$
LANGUAGE plpgsql;

I need to check in case of large number of inserts if the table will going to face any lock because of this trigger. I have tested it will bulk inserts on prod copy. However, still I'm not sure about this.
Also, I have observed a strange thing, when I'm inserting a sample 50,000 records via a pgsql loop, it's placing a lock on any other insert(blocking). However, when I'm inserting same number of records manually(manually writing 50,000 inserting statement and run that file in 1 batch)-it's not blocking any new insert. Why is that difference?


texts: SQL Server 2019 update CU8 to CU9 - lots of messages in errorlog file... DBID: [9] No DEK update for File Id [4] while existing DEK is null

texts:
This is SQL Server 2019 CU9 Developer edition on my 16GB laptop:
I updated from CU8 to CU9:
I ran checkDB on all of my DBs no issues.
Lots of these messages in my SQL Server Log file viewer:

DBID: [9] No DEK update for File Id: [4] while existing DEK is null
DBID: [9] File Header for File Id:[4] was read with state = 0 Refresh
DEK for DBID: [9] File ID: [4] - Copying encryption state DBID: [9] No
DEK update for File Id: [3] while existing DEK is null DBID: [9] File
Header for File Id:[3] was read with state = 0 Refresh DEK for DBID:
[9] File ID: [3] - Copying encryption state blah blah.. DBID: [2] No
DEK update for File Id: [6] while existing DEK is null DBID: [2] File
Header for File Id:[6] was read with state = 0 Refresh DEK for DBID:
[2] File ID: [6] - Copying encryption state

I don't even have a DBID of 9?
select * from master.dbo.sysdatabases

name    dbid sid mode status status2 crdate reserved category cmptlevel filename version
master  1 0x01 0 65544 1090520064 2003-04-08 09:13:36.390 1900-01-01 00:00:00.000 0 140 C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\master.mdf 904
tempdb  2 0x01 0 65544 1090520064 2021-02-18 16:24:25.070 1900-01-01 00:00:00.000 0 150 C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb.mdf 904
model   3 0x01 0 65536 1090519040 2003-04-08 09:13:36.390 1900-01-01 00:00:00.000 0 150 C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\model.mdf 904
msdb    4 0x01 0 65544 1627390976 2017-08-22 19:39:22.887 1900-01-01 00:00:00.000 0 150 C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf 904
ETRACKSystem74_SQL 5  0x010500000000000515000000E187A991DF25ABE0CE633A3C72040000 0 16 553648128 2018-11-08 21:47:31.427 1900-01-01 00:00:00.000 0 140 C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\ETRACKSystem74_SQL_Data.mdf 904
TEST2   6 0x010500000000000515000000E187A991DF25ABE0CE633A3C72040000 0 16 553648128 2019-05-22 11:02:08.600 1900-01-01 00:00:00.000 0 140 C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\TEST2_Data.mdf 904
TEST3   7 0x010500000000000515000000E187A991DF25ABE0CE633A3C72040000 0 16 553648128 2019-05-22 11:05:49.800 1900-01-01 00:00:00.000 0 140 C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\TEST3_Data.mdf 904
FortisTKCSDB 8 0x010500000000000515000000E187A991DF25ABE0CE633A3C72040000 0 65544 553648128 2020-04-10 08:42:23.933 1900-01-01 00:00:00.000 0 120 C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\FortisTKCSDB.mdf 904


texts: Stored Procedures doesn't show up in MySQL WorkBench

texts:
I have a MySQL database which has a few stored procedures, functions, and tables.

The problem I face is when I try to load the MySQL Database in MySQL WorkBench 6.3, it shows only Tables. Views, Stored Procedures, Triggers, and functions are not visible.

When I searched online for this, a few people suggested changing the value of lower_case_table_names to 1. I did that too, but still, it doesn't load any stored procedures or functions.

One more thing: I tried to connect to the same MySQL database using SQLYog. There it shows all the stored procedures and functions. Can anyone tell me the reason for this cause and a possible fix?

Here are the screenshots of MySQL Workbench and SQL Yog respectively.


texts: Final Unique Index for Person and Companies?

texts:
I am creating a persons schema where the the person table must be very simple because it will be father of other tables that completes it, but a person can be fisical or legal, so I wont put any identification numbers right in this table; for that there will be tables like person_documents, person_addresses, person_contacts etc.

The problem is that how to make this simple table works without unique numbers (like social security) that avoids the duplicate of a person.

So I think that I should create an Unique Index that holds for example both name and birth-date columns.

Should I have more columns to get 100% secure? After all there could be more than one person with the same name and same birth date right?

Maybe I could add a birth-place column, but then how would it impact on company person records...

create table person_base (
    id           bigserial,
    name         varchar(99),
    birthdate    date,
    _type        enum('human', 'company'),
    -- maybe birth_place    int references places(id)
);


texts:
MySQL has a lot of different highly available solutions, I would like to know what are the differences between all the current implementations, here are the implementations that look currently up to date :

MySQL Group Replication (by Oracle)
MySQL NDB Cluster (by Oracle)
Galera Cluster (by Galera)
XtraDB Cluster (by Percona)
MySQL Fabric Cluster (by Oracle)
MySQL Orchestrator (by Github)

What would be the recommended options (or any others) for a high available setup with automatic failover ? (For something equivalent of the SQL Server DB Mirroring with synchronous replication)


texts: Mysql query - Event to delete rows by its value

texts:
need help here

I want a query to delete all rows in which column value is older than today.
One of the columns is 'check_in' (values in timestamp format) and I want the query to identify the row that has values where the timestamp is older than today (midnight oriented ex: 1588809600 ) and delete them.

Try this but select all rows :

DELETE FROM events WHERE timestamp < (NOW() - INTERVAL 1 DAY)

Maybe something like this but it give me sintax erros :

DELETE FROM events WHERE check_in = timestamp < (NOW() - INTERVAL 1 DAY)

Basically, i want to create an event that every day delete rows where check_in value in older than today.

Can you help?


texts: Initial recovery Postgres with backup in sync commit mode

texts:
I've got a master and a slave postgres 9.1.3 database. I would like to init the backup database for the first time.

I did the following steps:

Stopped master
Copied all files to Slave
Create recory.conf on slave
started master
started slave

This process seems to work, but I've got the feeling something is wrong, because it's been a few hours now and there are still postgres: wal receiver process   streaming 1/1FAA88 and postgres: startup process   recovering 000000010000000100000000 processes on the slave.

The slave should be up after a few seconds because it already consists of all needed data...

I don't archive my WALs.

Do I have to use pg_start_backup before copying the files to the slave?

I tried this:

Disable sync commits
restart master
master pg_start_backup
stop master
copy files
start master
master pg_stop_backup (docs say "don't do that when sync commit = on", I guessed the master resends all files in pg_xlog when it starts?)
Enable sync commit
restart master
create recovery file on slave
start slave

But then I get an error on he slave about some missing WALs.

I found alot of tutorials with using WAL archive, but I don't have a WAL archive.

Can anyone give me the correct steps?


texts: SQL Server 2016 TempDB physical file size is different than SQL System Catalog And Proportional Fill Algorithm is not working as expected

texts:
When I see size of file physically on drive, it is different for one of the tempdb secondary data file when compared to size mentioned in system catalog. What can the reason for it?

In tempdb properties we are also seeing same thing as we say via Query. So query should not be an issue.

SELECT f.name AS [File Name] ,
       f.physical_name AS [Physical Name],
       CAST((f.size/128.0) AS DECIMAL(15,2)) AS [Total Size in MB],
       CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2)) AS [Available Space In MB],
       [file_id],
       fg.name AS [Filegroup Name],
       (f.growth*8)/1024 AS [AutoGrowth In MB]
FROM sys.database_files AS f WITH (NOLOCK)
LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK)
  ON f.data_space_id = fg.data_space_id
OPTION (RECOMPILE);

Also another major issue is proportional fill algorithm is not working as expected as couple of files are having more size when compared to other files.
What can be the reason for it?


texts: SQL Error: Must be GROUPed or used in aggregate function

texts:
I have just created a SQL statement in Crystal Reports using the command feature, but I am having issues with displaying the data. The error reads:

  Attribute FUND_ACCT_REG_VIEW.FUND._ACCT_NO must be GROUPed or used in an aggregate function.

Here is the code:

SELECT substr(work_item.wi_name, 1 + instr(work_item.wi_name, '['), 6) p2
    ,substr(work_item.wi_name, 1, 4) wi_name
    ,substr(pr_line_item.prac_no, 7, 16) prac_no
    ,moa_code
    ,resource_code
    ,nvl(certified_us_amt, 0) - nvl(deob_amt, 0) + nvl(obli_excess_commit_amt, 0) + nvl(pr_interest_amt, 0) COMMITTED
    ,nvl(certified_us_amt, 0) - nvl(deob_amt, 0) + nvl(obli_excess_commit_amt, 0) - nvl(unoblig_us_bal_amt, 0) + nvl(pr_interest_amt, 0) obligated
    ,unoblig_us_bal_amt unoblig
    ,max(receive_org_code) receive
    ,substr(org_name, 1, 13) org_name
    ,fund_acct_reg_view.fund_acct_no fund_acct
    ,decode(fund_acct_reg_view.fund_acct_no, 'J177777', 'SA', 'J177778', 'SA', 'J177779', 'SA', 'J177768', 'OTHER CWE', 'J176716', 'OTHER CWE', 'J176717', 'OTHER CWE', 'J177780', 'CONST', 'DESIGN') type
FROM pr_line_item
    ,work_item
    ,pr_amend
    ,wm_organization
    ,fund_acct_reg_view
WHERE ((substr(work_item.wi_name, 1 + instr(work_item.wi_name, '['), 6) = '%153719%'))
    AND work_item.wi_code = pr_line_item.wi_code
    AND pr_line_item.prac_no = pr_amend.prac_no
    AND fund_acct_reg_view.fund_acct_no NOT LIKE 'OA%'
    AND (
        ABS(nvl(certified_us_amt, 0) - nvl(deob_amt, 0) + nvl(obli_excess_commit_amt, 0)) > 0
        OR nvl(obli_excess_commit_amt, 0) > 0
        )
    AND fund_acct_reg_view.fund_acct_no NOT LIKE 'J169479'
    AND receive_org_code = org_code
    AND pr_line_item.fund_acct_no = fund_acct_reg_view.fund_acct_no
GROUP BY substr(work_item.wi_name, 1 + instr(work_item.wi_name, '['), 6)
    ,substr(pr_line_item.prac_no, 7, 16)
    ,nvl(certified_us_amt, 0) - nvl(deob_amt, 0) + nvl(obli_excess_commit_amt, 0) + nvl(pr_interest_amt, 0)
    ,nvl(certified_us_amt, 0) - nvl(deob_amt, 0) + nvl(obli_excess_commit_amt, 0) - nvl(unoblig_us_bal_amt, 0) + nvl(pr_interest_amt, 0)
    ,unoblig_us_bal_amt
    ,resource_code
    ,pr_line_item.fund_acct_no
    ,decode(fund_acct_reg_view.fund_acct_no, 'J177777', 'SA', 'J177778', 'SA', 'J177779', 'SA', 'J177768', 'OTHER CWE', 'J176716', 'OTHER CWE', 'J176717', 'OTHER CWE', 'J177780', 'CONST', 'DESIGN')
    ,substr(org_name, 1, 13)
    ,substr(work_item.wi_name, 1, 4)
    ,moa_code
ORDER BY type
    ,fund_acct
    ,moa_code
    ,resource_code
    ,receive

After testing the code multiple times, I keep having the issued with either this field or another field. I am hoping that the issue it at the tail end of my code and someone may know a simple quick fix. I have spent almost all day trying to read up on this error, but I can't see how other solution apply to my situation.


texts: What is the most efficient way to count the number of rows in a table?

texts:
I am using Postgres with the following query:

select count(*) from image;

The primary key on this table is non-incrementing; it's a unique serial number for the images stored in the table. Our app often attempts to ingest images that have already been recorded in the database, so the primary key/serial number ensures they are only recorded once.

Now we are wondering if we should have gone with an incrementing primary key instead. We have 1,259,369 images in the database and it takes about 7 minutes for the count query to run.

Our app will never delete images from this table - so an incrementing primary key would allow us to check the value of the last ID which would equal the number of rows in the table.


texts: Is there a way to tell optimizer not to use index for reads?

texts:
Of course I can just drop and recreate or disable and rebuild it.
But for large tables it can take time, and for critical tables it can cause issues.
Is there any "more sophisticated" way to tell sql to keep index data(+ handle updates\inserts) without using it?
So if needed it can be "enabled" immediately?


texts: PostgreSQL ignores implicit casts in some situations

texts:
The setup (tried only on PostgreSQL 9.6):
CREATE TYPE MY_ENUM AS ENUM ('a', 'b');
CREATE CAST (CHARACTER VARYING AS MY_ENUM) WITH INOUT AS IMPLICIT;
CREATE TABLE t (x MY_ENUM);
INSERT INTO t VALUES ('a'::MY_ENUM), ('b'::MY_ENUM);

These work just fine, as expected:
INSERT INTO t VALUES ('a');
SELECT * FROM t WHERE x = 'a';

But these don't:
PREPARE p(CHARACTER VARYING) AS SELECT * FROM t WHERE x = $1;
;; error: operator does not exist: my_enum = character varying

CREATE FUNCTION f(ix CHARACTER VARYING, OUT ox MY_ENUM) AS
$$
    SELECT * FROM t WHERE x = ix
$$ LANGUAGE sql;
;; error: operator does not exist: my_enum = character varying

CREATE FUNCTION f(ix CHARACTER VARYING) RETURNS VOID AS
$$
BEGIN
    SELECT * FROM t WHERE input_type = ix;
END;
$$ LANGUAGE plpgsql;

SELECT f('piano');
;; error: operator does not exist: my_enum = character varying

According to the documentation:

If the cast is marked AS IMPLICIT then it can be invoked implicitly in any context, whether assignment or internally in an expression.

So why the errors?


texts: SQL Server: sp_describe_first_result_set with more than one select in a stored procedure

texts:
I'm using sp_describe_first_result_set to retrive metadata from a stored procedure and having the following error:
Msg 11512, Level 16, State 1, Procedure dbo.sp_describe_first_result_set, Line 1 [Batch Start Line 0]
The metadata could not be determined because the statement 'SELECT 1' in procedure 'test' is not compatible with the statement 'SELECT 'str'' in procedure 'test'.

This SP can return different datasets depending on a parameter:
ALTER PROCEDURE [dbo].[test]
    @test NVARCHAR(50) = 'test'
AS
BEGIN
    SET NOCOUNT ON;

    IF (@test = 'test')
    BEGIN
        SELECT 1
    END
    ELSE
    BEGIN
        SELECT 'str'
    END
END

Looks like sp_describe_first_result_set does an UNION of the queries because if both the queries returns the same col model, the procedure works.
I've even tried to pass @parameter like this:
DECLARE @test NVARCHAR(50) = 'test2'
EXEC [dbo].[sp_describe_first_result_set]
    @tsql = N'test @test = @test'
    ,@params = N'@test NVARCHAR(50)'

but it doesn't work.
Is there a way to make it work?


texts:
Just as the topic states.

Is it possible to issue an INSERT / UPDATE / DELETE and specify for it not to be relayed to attached slaves in mysql?


texts: Is my ERD valid for airports,cities and airlines database?

texts:
My objective is to make a database of airports, cities and the airlines which provide coverage to them.

I asked the question before on stackoverflow and the person told me to break up the many-to-many relationship between airport and airline(I  agree with that). Moreover, he/she raised objection on relationship between airline and country.

I thought an airline belongs to a country so i made a relationship between two. Kindly, give your suggestions on the ERD and how i can improve it?


texts:
I'm using PGAdmin4, and ever since updating to version 4.10 I've had a random white box on the screen.

I've discovered that I can minimise this box, but there doesn't seem to be any way to close it.

How can I remove this rather annoying thing?!


texts: Need to optimize simple SELECT query for InnoDB table with 2million rows

texts:
I'd like to make this query the fastest possible, since it's called really often.

SELECT login_events.id
FROM login_events
WHERE (
  DATE(created_at) >= DATE(CURRENT_DATE)
  AND person_id = 1
)
LIMIT 1

It's running on a 157.7mb InnoDB table (says navicat), with ~2million rows and indexed on [created_at, person_id].

Using EXPLAIN I see it's using the index, but it says "Using where; Using index;". What can I do to make this the fastest possible? Would switching to MyISAM gain me anything?


texts: ANSI NULL OFF By Default in database option

texts:
I have a weird behaviour here when creating database in sql server. The default ANSI values is set to false when creating database.

ALTER DATABASE [NewDB] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [NewDB] SET ANSI_NULLS OFF
GO
ALTER DATABASE [NewDB] SET ANSI_PADDING OFF
GO
ALTER DATABASE [NewDB] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [NewDB] SET ARITHABORT OFF
GO

However, when i execute the query after database is created in query editor

SELECT * FROM [dbo].[TableName] WHERE [ColumnName] = NULL

It is obvious that the actual setting is ANSI_NULLS ON since no row returns.

==========================================================================

My question is:

1) Why is this so? The database option is ANSI_NULLS OFF but query result actually show it is ANSI_NULLS ON. This is also applicable to all other ANSI settings, where it actually resembles ANSI_NULLS ON while the database option is set to OFF.

2) Why is the default database option is ANSI_NULLS OFF instead of ANSI_NULLS ON. (Similar applicable to other ANSI options as well, why is it default to OFF since ON is the one being recommended?)

I have tested this from SQL Server 2012 up to 2017 and all version results in same behaviour. I am quite confused with this.


texts:
I have one query by which I am getting duplicate ComapnyID and I want to filter that and remove duplicate ComapnyID but don't want change the order by report.

table 1 reports

Table 2 Company

Here is the query

SELECT c.CompanyID,c.`CategoryID`,c.`CompanyName`,  r.`CreatedDate`,r.`ReportID` FROM `company`c
JOIN reports r ON r.CompanyID = c.CompanyID
WHERE r.ProductServiceID= 14 AND c.RegionID=1 AND c.IsActive = 1
ORDER BY r.`ReportID` DESC ;

Result 1

I want to remove the 2nd duplicate ComapnyID in result

I have tried these queries

Distinct
Inner join
Group by with having

if I change the query and get distinct data than the order gets change which I don't want.

Result 2

What am I missing here? Can any of you resolve this issue?

Thanks


texts: SQL YTD calculations adding periods without postings

texts:
I have a query which give me the YTD figures per company (client), account, IC and period for the periods in which financial postings have been performed. The table where I get this from is AAGCONSO which is a balance table of all the accounts, per client, period and IC.
SELECT        * FROM  (SELECT        client, account, IC, period, SUM(amount) OVER (PARTITION BY client, account, IC, LEFT(period, 4)
                         ORDER BY client, account, IC, period) AS YTD, ROW_NUMBER() OVER (PARTITION BY client, account, IC, period ORDER BY client, account, IC, period ASC) AS RowYTD FROM            (SELECT        t .client AS client, t .dim1 AS account, CASE WHEN t .dim2 IN ('10', '11', '30', '31', '32', '33', '34', '35', '36', '50', '60') THEN t .dim2 ELSE '' END AS IC, t .dim2, t .period AS period, SUM(t .amount)
                                                AS amount, ROW_NUMBER() OVER (PARTITION BY t .client, t .dim1, LEFT(period, 4)
                      ORDER BY t .client, t .dim1, LEFT(period, 4) DESC) AS Row FROM            aagconso t WHERE        t .period >= 201853 AND client IN ('10', '11', '30', '31', '32', '33', '34', '35', '36', '50', '60') AND t .dim1 = '111110' GROUP BY t .client, t .dim1, t .dim2, period, t .amount HAVING        SUM(t .amount) <> 0) AS CTE2) X WHERE        RowYTD = 1 strong text ORDER BY 1 DESC Offset 0 Rows

This gives me the YTD amounts in the following table

The totals are correct, however I want to have the periods in which no postings have been performed included with the YTD amount of previous period.
I have another table with all possible accounting periods
SELECT acc_period FROM dbo.PBAP_GL_PERIODS

Which gives me this :

How do I get a full YTD table including the periods from table B for which there are no postings in table A? I probably need to use a cross apply or join but I really do not see where I get this ...
So for example Table A for account 111110 in client 10 gives me this

Client
Account
Period
Amount
RowYTD

10
111110
202012
1325925.000
1

And I want to have this

Client
Account
Period
Amount
RowYTD

10
111110
202000
0.000
1

10
111110
202001
0.000
1

10
111110
202002
0.000
1

10
111110
202003
0.000
1

10
111110
202004
0.000
1

10
111110
202005
0.000
1

10
111110
202006
0.000
1

10
111110
202007
0.000
1

10
111110
202008
0.000
1

10
111110
202009
0.000
1

10
111110
202010
0.000
1

10
111110
202011
0.000
1

10
111110
202012
1325925.000
1

Thanks in advance for any help!


texts: Cronjob to check when Oracle Materialized View was last updated?

texts:
I have two database users in my Oracle DB and want to create MV's in User B which get their data from User A through a database link.

I plan to autorefresh the MV's every night but want to make sure the MV's are always up to date if something doesn't work with the autorefresh.

I found two kind of meta-data about the MV's: "LAST_REFRESH_TYPE" and "LAST_REFRESH_DATE".

Does the "LAST_REFRESH_DATE" always contain the date when my data was successfully refreshed the last time or just the date when a refresh was started (and perhaps failed)?


texts: update variable from multiple tables in one row

texts:
I have two databases, and I need to UPDATE variable 'birthday' from one row to another checking 'name' (from db1 to db2).

The problem is, that 'name' variable in second db is located in another table (table2).
It is possible to do this without changing DB structure using 'id' variable from db2 to synchronizing?

UPDATE `db1`.`table1`, `db2`.`table2` SET `db2`.`table1`.`birthday` = `db1`.`table1`.`birthday` WHERE `db2`.`table1`.`name` ...

Scheme:

 db1-> table1 -> name,birthday
 db2-> table1 -> name,id
       table2 -> birthday,id


texts: Why is my database still fragmented after I rebuilt and reindexed everything?

texts:
I have a database which I tried to defragment all the tables at once by running this T-SQL:

SELECT
        'ALTER INDEX all ON ' + name + ' REORGANIZE;' + CHAR(10) +
        'ALTER INDEX all ON ' + name + ' REBUILD;'
    FROM sys.tables

And then copying and pasting the output to a new query window and running that. I got no errors, but I still have fragmentation. I tried running both commands separately too and still have fragmentation. Note: I have been made aware that REORGANIZE is unnecessary by Aaron, and I'm aware I could use dynamic sql to automate this.

I ran this to determine I still have fragmentation:

SELECT * FROM
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL)
WHERE avg_fragmentation_in_percent > 0

And I got:

database_id object_id   index_id    partition_number    index_type_desc alloc_unit_type_desc    index_depth index_level avg_fragmentation_in_percent    fragment_count  avg_fragment_size_in_pages  page_count  avg_page_space_used_in_percent  record_count    ghost_record_count  version_ghost_record_count  min_record_size_in_bytes    max_record_size_in_bytes    avg_record_size_in_bytes    forwarded_record_count  compressed_page_count
85  171147655   1   1   CLUSTERED INDEX IN_ROW_DATA 2   0   36.3636363636364    5   2.2 11  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
85  421576540   1   1   CLUSTERED INDEX IN_ROW_DATA 2   0   75  7   1.14285714285714    8   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
85  965578478   1   1   CLUSTERED INDEX IN_ROW_DATA 2   0   14.7058823529412    6   5.66666666666667    34  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
85  1061578820  1   1   CLUSTERED INDEX IN_ROW_DATA 2   0   40  4   1.25    5   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
85  1109578991  1   1   CLUSTERED INDEX IN_ROW_DATA 2   0   30.7692307692308    5   2.6 13  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
85  1205579333  2   1   NONCLUSTERED INDEX  IN_ROW_DATA 2   0   50  5   1.6 8   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
85  1493580359  1   1   CLUSTERED INDEX IN_ROW_DATA 2   0   50  6   1.66666666666667    10  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL

I know I am missing something real basic, but I don't know what.


texts:
This may be a very simple but it's still not clear to me. I'm writing a SQL Agent job to generate output in html format.

I need to get results of a query SELECT COUNT(*) FROM TABLE_NAME. I'm able to get output generated with actual data in html but I just need count from table.

What is the easiest way to do it?

Here is part of my query:

SET @tableHTML = N'
' +
        N'' +
        N'
COLUMN_NAME	' +
        N'
' +
        ISNULL(CAST ( ( SELECT  td = [COLUMN_NAME],''
                        FROM dbo..TABLE_NAME
                    FOR XML PATH('tr'), TYPE
        ) AS NVARCHAR(MAX) ),'') +
        N'' +
        N'
'

Can someone please help me to get output as count instead of individual row in output.

I have couple staging tables for a process and need to get notification for those tables with data in it. First table I only need row count. There is only one column in it. And second table has 4 Columns and I got expected output for it in sql job. Actually I just need to add one line with total rows from table to output in mail. I hope this will help to understand the question better.

It doesn't need to be in HTML but my second table needs to be included in output makes it easy to generate proper table output. I'm not much familiar with sql stuff yet. Still learning.


texts: Created user can access all databases in PostgreSQL without any grants

texts:
I must be missing something with regards to setting up PostgreSQL. What I'd like to do is create multiple databases and users that are isolated from each other so that a specific user only has access to the databases I specify. However, from what I can determine, any created user has access to all databases without any specific grants being given.

Here is what I do on an Ubuntu Server 12.04:

apt-get install postgresql
sudo -u postgres createuser -DRSP mike1
(Specifying the password for the new user)
sudo -u postgres createdb data1
psql -h localhost -U mike1 data1
(Specifying the password for the user mike1 to login)

It seems that new user "mike1" has no problem connecting to database "data1" and creating tables etc. And this without running any GRANT command at all (and the owner of "data1" is "postgres" since I didn't specify an owner in step 3). Is this really how it is supposed to work?

What I'd like to do is grant mike1 full access to data1 and then repeat this for more users and databases, making sure that the users only have access to one (or possibly several) databases of my choice.


texts:
I still playing with my own DB trying to learn and saw this:

I could change the root password without any problem at all... If I'm in the server I can create an algorithm to start testing password and and someday I will find it, I mean:

Web-Services-iMac-2:~ jbolivar$ mysqladmin -utest1 -p**SOME_THING_HERE** password test1.
is it ok to change password using this???:

update table mysql.user set password=PASSWORD('test')  where user='test1';

beside that if I create a dictionary table (a table with all possible words) and apply PASSWORD("word") I can make a join and find the value of any pass, right?.
Can you give me your opinion about my analysis?


texts: How to create a SQL Server user that can restore databases to a database server?

texts:
I want to create a new user that can create new databases on my database server.

I've tried:

CREATE LOGIN [my_db_restore_user] WITH PASSWORD = 'passwordhere';
GRANT ALTER ON ROLE::[dbcreator] TO [my_db_restore_user] WITH GRANT OPTION

Which results in: Cannot find the object 'dbcreator', because it does not exist or you do not have permission.

I've also tried:

ALTER SERVER ROLE [dbcreator] ADD MEMBER [my_db_restore_user];

GRANT ALTER ON SERVER ROLE::dbcreator TO my_db_restore_user ;

but all result in the same error.

How can I create a server level user that can create databases on the server for restoring purposes. The user will also need to create users on the new databases too.

Any help on this issue would be greatly appreciated.


texts: Which version is the right one to begin with Oracle

texts:
I see many versions of oracle 8i , 11g and 12c.

I am familiar with SQL Server 2012 and SQL.

Which version of Oracle should I start with?


texts: Is there any way to find how much CPU is getting consumed by a query running for long time in Database?


texts:
I've been tasked to stress test our MSSQL Server and MySQL Server. I would like to know if there are any tools or scripts which I could use on our current systems and the new system to compare performance?

I would like to measure read / write to disk and processor performance. Anything else that may be useful would be great as well.

Thanks!


texts: How to speed up execution time in a SQL Server 2016 database which has restored from 2008 R2?

texts:
I have restored the backup of the database from SQL Server 2008 R2 to SQL Server 2016 and now facing the performance issue.
How can the issue be solved without changing database Compatibility?


texts: A performing monotonic UUID variant, does it make sense?

texts:
Because of supporting offline clients I unfortunately have to replace some of my integer Primary Key's for UUID's (but not necessarily the 16 byte (128-bit) RFC 4122 version). I'm on Postgres 12 (could upgrade to 13, but there seems nothing in the changelog related to this). What would be the best performing option? I have some thoughts written below. Any insights/answers?
Length.
To improve overall performance, I am assuming that shorter is better for the PK. I'm aiming for a shorter 8 byte key.
Monotony.
As I have a degree of control over the application I can have a key that is shorter than the typical UUID, which at 16 bytes, feels a bit long to me.
I have been informing myself on Postgres PK's, and learned that they are B+-Tree's, and may (if I understand it correctly) benefit from monotonically increasing values.
I'm thinking of making a 8 byte key composed of three parts:

The Unix epoch in (4 bytes, monotonic)
Depending on if unsigned ints are available in Postgres, this would work up to the year 2038 (18y) or 2106 (85y) for resp. available size for 2147483647/4294967294. I could even take it one step further by establishing that we're not counting from 1-1-1970 but from 1-1-2021, which gives resp. 68 and 136 years of seconds.

..followed by a 1 byte counter (0-255) in case more than one object is created by the client at the same time.

..followed by a 3 byte random number (16,777,216), of which I am not too sure is long enough.

The resulting key will thus not be entirely monotonous, but still somewhat.
Pretty much all online sources I read state that monotony is good for b-tree's, although the exact reason still eludes me somewhat. To my surprise in an article by Jimmy Nilsson put the sequence on the end of the key. https://www.informit.com/articles/article.aspx?p=25862&seqNum=10
Question: Does it matter, and if so, what is better, to put the monotonous part of the key on the beginning or on the end of the value?
Data type.
The key is thus an unsigned 8-byte number. Unfortunately, PostgreSQL does not seem to have an uint8, although there seems to be extensions (such as https://github.com/petere/pguint), but I am wary of potential side effects of using such an extension (should I be?).
However, looking at the number, it did gave me an idea: The max number of a signed BIGINT is 9,223,372,036,854,775,807. What if I approach this like this:
|9,223,372,036|,8|54,775,807|
| timestamp   |ct| random   |

And instead of allowing the full maximum, cap it at:
|8,999,999,999|,9|99,999,999|
| timestamp   |ct| random   |

A range of 9,223,372,036 for the timestamp, gives space till the year 2262, but 8,999,999,999 is still a whopping year 2255. The counter is only allowing for 10 objects per second (which is ok) but the random number is now a better 99.9mln (which must now of course be 0-padded).
Other things.
Security: A benefit of UUID's is that they cannot be guessed. My application has no need of that.
MAC address: As proposed in the comments, won't fly, as my clients will mostly be browser tabs which have (luckily) no access the MAC. Browsers (rightfully) take precautions against fingerprinting, therefore I am discounting client fingerprints entirely at the moment in favour of a time+random approach.
Edit: I rewrote the question after the initial comment from a_horse_with_no_name and reading https://www.2ndquadrant.com/en/blog/sequential-uuid-generators/ including the UUIDv6 approach.


texts: MariaDB allowing select only user to create databases

texts:
I'm baffled. I have a select only user, but somehow, the user can create a new database, and create table inside the new database. It can't create tables inside other db. It's really odd.

This is MariaDB 5.5.28.

$ mysql -upermtest -pxxx -h mysqlhost
...
Your MySQL connection id is 4355 to server version: 5.5.28-MariaDB-log
...
mysql> show grants;
+----------------------------------------------------------------------------------------------------------+
| Grants for permtest@%                                                                                |
+----------------------------------------------------------------------------------------------------------+
| GRANT SELECT ON *.* TO 'permtest'@'%' IDENTIFIED BY PASSWORD '*XXX' |
+----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create database test_ddb_again;
Query OK, 1 row affected (0.00 sec)

mysql> use test_ddb_again;
Database changed

mysql> create table hello ( a int );
Query OK, 0 rows affected (0.01 sec)

mysql> use previously_existing_db;
Database changed

mysql> create table hello ( a int );
ERROR 1142 (42000): CREATE command denied to user 'permtest'@'10.30.25.51' for table 'hello'
mysql>

I'm sure there is a good explanation for this.


texts: How to find all invalid document based on jsonSchema validator?

texts:
One of the new feature in MongoDB 3.6 is jsonSchema validator. I can create a collection with an validator like:
{
  "validator": {
    "$jsonSchema": {
      "properties": {
        "name": {
          "bsonType": "string",
          "description": "name"
        },
        "age": {
          "bsonType": "int",
          "minimum": 10,
          "maximum": 30
        }
      },
      "required": ["name", "age"],
      "bsonType": "object"
    }
  }
}

But what if the collection already existed with some documents, that don't fullfill the validation schema? How can I found out all invalid documents based on the validator?


texts: How do you combine multiple update statements for the same row using MySQL trigger

texts:
Background:
Each time a column is modified, I need to update the associated column (which has the same name) in a second table. This is my first attempt at using a trigger.
Code:
Here's a simplified example of what I'm trying to do, which does its job fine, but inefficiently:
DROP TRIGGER IF EXISTS update_second_table;
DELIMITER //
CREATE TRIGGER update_second_table
  BEFORE UPDATE ON first_table
  FOR EACH ROW
BEGIN
  /* putting IF statements on one line so it's easier to see what's happening */
  IF NOT(OLD.firstname <=> NEW.firstname)   THEN UPDATE second_table SET firstname  = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
  IF NOT(OLD.middlename <=> NEW.middlename) THEN UPDATE second_table SET middlename = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
  IF NOT(OLD.lastname <=> NEW.lastname)     THEN UPDATE second_table SET lastname   = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
  IF NOT(OLD.nickname <=> NEW.nickname)     THEN UPDATE second_table SET nickname   = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
  IF NOT(OLD.dob <=> NEW.dob)               THEN UPDATE second_table SET dob        = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
  IF NOT(OLD.email <=> NEW.email)           THEN UPDATE second_table SET email      = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
  IF NOT(OLD.address <=> NEW.address)       THEN UPDATE second_table SET address    = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
  IF NOT(OLD.city <=> NEW.city)             THEN UPDATE second_table SET city       = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
  IF NOT(OLD.state <=> NEW.state)           THEN UPDATE second_table SET state      = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
  IF NOT(OLD.zip <=> NEW.zip)               THEN UPDATE second_table SET zip        = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
  IF NOT(OLD.phone <=> NEW.phone)           THEN UPDATE second_table SET phone      = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;
END;
//
DELIMITER;

The problem:
As you can see, depending on how many columns are updated in `first_table`, there can be as many as 11 update statements on the same row in `second_table`.
The question:
Is there any way to combine the update statements into one?


texts: Efficiently moving Data from CSV to SQL 2014

texts:
I am working on a requirement to transfer almost 1 TB of data from a CSV file to SQL server. Which is the fastest way to do this? I am considering bulk copy and came across this link which actually has a power shell script for the same

https://gallery.technet.microsoft.com/scriptcenter/Import-Large-CSVs-into-SQL-216223d9

Are there any other considerations which I am missing? The source DB is a PeopleSoft Oracle database.

Another option I considered was using the SSMA tool for Oracle, copy the entire schema to SQL Server and then query the required data from the database. Is this a better option? Please suggest

Thanks!


texts: How to get one MAX() between SUMs of several columns in SQL?

texts:
I need to select MAX() between SUM(Math), SUM(English), SUM(Physics)

Result must be:

19 | English


texts: Reviewing all new MySql queries on staging environment

texts:
The problem I am trying to solve: It's well known that not all developers know what is EXPLAIN in MySql, and not every knows how to use it. Large part of them consider MySql as a black box, no matter what is the query I write, I expect maximal response time and right result. I think you recognize this situation. Of course more experienced developers should educate them, but I am looking for best practices how to prevent the situation when bad, stucking query goes over QA team, and takes down production environment.

Approaches to solution: we might have few approaches, the first one, do review of the code of the next deployment. It's not to convenient, might take a lot of time, but very effective. The second one, try to identify slow queries on staging environment by looking at slow_logs, this approach is very easy, but not to efficient, data on staging is not the same data as data on production, therefore slow  query on production can be completely good query on staging.

What's your opinion, how to prevent bad written, slow query to go to production environment?


texts: Checkdb giving error for master database

texts:
When i run dbcc checkdb on master database i get error like

Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.

When i searched for it i got lot of links stating that it happens when database has read-only file group or file is in FAT32 file system. But when i checked the master database was not in readonly file group. But mdf file was in FAT32 file system. So solution was to use TABLOCK with checkdb command. So i tried dbcc checkdb With Tablock . But i am hetting same error message. So what is the reason for this error. I am using sql server 2008 R2 express(service pack 2).


texts:
I have a database named mydbname that I created manually.
I also created a user myuser for which I granted all privileges on mydbname.

I dumped my database using pg_dump this way:

$> pg_dump --host=myhost --port=myport--username=super --format=c --file=myfile.dump mydbname

The dump was successful.
But then, I tried to test a full restoration (schema and data) using  pg_restore like this:

$> pg_restore --host=myhost --port=myport--username=super -d mydbname myfile.dump

This failed (no restoration done) and I got errors like this:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 173; 1259 25566 TABLE auth_group myuser
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "auth_group" already exists
pg_restore: [archiver (db)] Error from TOC entry 189; 1259 25613 TABLE django_migrations myuser
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "django_migrations" already exists
    Command was: CREATE TABLE django_migrations (
    id integer NOT NULL,
    app character varying(255) NOT NULL,
    name character varyin...
pg_restore: [archiver (db)] Error from TOC entry 190; 1259 25619 SEQUENCE django_migrations_id_seq myuser
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "django_migrations_id_seq" already exists
    Command was: CREATE SEQUENCE django_migrations_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
Command was: ALTER TABLE ONLY participer_commande
ADD CONSTRAINT participer_commande_produit_id_fbecbd48_fk_porter_produit_id FOREIGN...
pg_restore: [archiver (db)] Error from TOC entry 2981; 2606 25829 FK CONSTRAINT participer_fileinfor_campagne_id_715122f1_fk_porter_ca jbpuser1
pg_restore: [archiver (db)] could not execute query: ERROR:  constraint "participer_fileinfor_campagne_id_715122f1_fk_porter_ca" for relation "participer_fileinformations" already exists
WARNING: errors ignored on restore: 118

Could you please help me how to restore the whole database (recreate the whole schema, relations and data)? What is the best way pg_restore should be used?


texts: postgresql query performance issue with timestamp and order by filter

texts:
I have table definition as following.

CREATE TABLE monmgr_pg.order_staged_events (
  event_id varchar(64) NOT NULL,
  order_number varchar(40) NOT NULL,
  oms_order_key varchar(64) NOT NULL,
  event_name varchar(55) NOT NULL,
  event_timestamp timestamp DEFAULT NOW(),
  order_type varchar(35) NOT NULL,
  event_dto text NOT NULL,
  sequence_number NUMERIC(3,0) NOT NULL,
  sub_sequence_number NUMERIC(3,0) NOT NULL,
  "version" NUMERIC(38,0) NOT NULL DEFAULT 0,
  error_code varchar(35) NULL,
  processed_count  NUMERIC(38,0) DEFAULT 0,
  create_timestamp timestamp DEFAULT NOW()
)

I am  firing following query on this table.

SELECT DISTINCT OMS_ORDER_KEY
FROM (
   SELECT OMS_ORDER_KEY
   FROM ORDER_STAGED_EVENTS
   WHERE SEQUENCE_NUMBER = 101
    AND SUB_SEQUENCE_NUMBER IN (1,2,3,4,5,6)
    AND EVENT_TIMESTAMP > (now()-INTERVAL '12 hours')
    AND PROCESSED_COUNT <= 4
    ORDER BY EVENT_TIMESTAMP ASC
    FETCH NEXT 10000 ROWS only
) X;

This table will have 4-5 million records for last 12 hours and total entries in table might cross 20-30 million.I have tried to create different combination of indexes like combined index on (sequence_number, sub_sequence_number, event_timestamp) and separate indexes on columns but still query execution time is reaching up-to 20-30 seconds when no of records increases.processed_count column value is updated after successful processing.

Can someone please help me deciding correct indexes on this table to improve query performance.


texts:
I am setting up a multi user environment for shared hosting. Since phpMyAdmin 4.1.0 it's possible to create user groups, add users to them and define which functions they may use.

Now this is a great feature, but it would be better if new MySQL users get a default user group when they login to phpMyAdmin for the first time. I haven't seen this setting in phpma (so far), so I wanted to ask you if you know whether this is possible in phpma or that I maybe can set a default user group in the php files of phpma?

Thanks in advance!


texts:
I have a vague recollection that Firebird's isql utility will in some situations make an exclusive connection to the database, meaning that any other connection attempts will be refused.

I fail to find information about this.

Can anyone explain in which situations this happens, and in particular how I can make sure it does NOT happen?

My intended usage is to execute some commands/queries in a scheduled task, and I don't want to run the risk of blocking other database work while this task is executing.

From a few simple tests it seems like I get an exclusive connection, i.e. no other connections are allowed, when there are no other connections present. Conversely, if there are other connections when isql connects, isql's connection doesn't block other connections.

I tested this with Firebird 3.0.4 in superserver mode and FlameRobin, using the same database alias for both isql and FlameRobin connections. The alias is defined with an absolute local file path.

Should I include host and port to ensure that isql always connects through the superserver (allowing other connections) rather than opening the file directly (blocking other connections)?


texts: Advice on fine-tuning query and index for performance

texts:
Currently my query is as below:

select id_request,username_request
  from darkhast_follower
 where darkhast_follower.id_request != '9762952594'
   AND darkhast_follower.status =0
   AND NOT exists ( select null
               from log_follow
               where log_follow.other_id = darkhast_follower.id_request
               AND log_follow.id= '9762952594' LIMIT 1)
 LIMIT 1

Table log_follow has 30 milion rows and darkhast_follower has 1000 rows respectively.

log_follow is indexed on column (other_id) and (id), darkhast_follower is indexed on column (id_request) and (status).

The performance seems to be slow and looking for expert advice on fine-tuning the query and index suggestion. Currently I have 40k active users.

-- Server version: 5.7.23-log
-- PHP Version: 5.6.31

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

-- --------------------------------------------------------

--
-- Table structure for table `darkhast_follower`
--

CREATE TABLE `darkhast_follower` (
  `IDD` int(11) NOT NULL,
  `id` varchar(20) NOT NULL,
  `username` varchar(65) NOT NULL,
  `id_request` varchar(20) NOT NULL,
  `username_request` varchar(65) NOT NULL,
  `token_id` text NOT NULL,
  `darkhasti` int(5) NOT NULL,
  `daryafti` int(5) NOT NULL,
  `date` text NOT NULL,
  `status` tinyint(1) NOT NULL,
  `gsm_sender` tinyint(1) NOT NULL,
  `error_report` tinyint(3) NOT NULL,
  `bot` tinyint(1) NOT NULL,
  `back` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `darkhast_follower`
--
ALTER TABLE `darkhast_follower`
  ADD PRIMARY KEY (`IDD`),
  ADD KEY `status` (`status`),
  ADD KEY `id_request` (`id_request`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `darkhast_follower`
--
ALTER TABLE `darkhast_follower`
  MODIFY `IDD` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

 -- --------------------------------------------------------

--
-- Table structure for table `log_follow`
--

CREATE TABLE `log_follow` (
  `IDD` int(11) NOT NULL,
  `id` varchar(20) NOT NULL,
  `username` varchar(65) NOT NULL,
  `other_user` varchar(65) NOT NULL,
  `other_id` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `log_follow`
--
ALTER TABLE `log_follow`
  ADD PRIMARY KEY (`IDD`),
  ADD KEY `id` (`id`),
  ADD KEY `other_id` (`other_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `log_follow`
--
ALTER TABLE `log_follow`
  MODIFY `IDD` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;


texts: How to quickly startup/shutdown Oracle 11?

texts:
I am wondering what is the quickest way to properly startup/shutdown a Oracle DB daemon (Oracle 11.2, installed on a test machine).

I need it for C/C++ programs that use the OCI/Pro*C API.

I want this because I am used to the startup speed of PostgreSQL, and because the daemon runs in a virtual machine which is only started (on-demand) for test cases.

Currently I script it like this - startup:

sqlplus /nolog <<EOF
connect / as sysdba
startup
quit
EOF
lsnrctl start
emctl start dbconsole

And shutdown:

emctl stop dbconsole
lsnrctl stop
sqlplus /nolog <<EOF
connect / as sysdba
shutdown
quit
EOF

This works - programs work as expected - but this procedure is quite slow.

The Oracle DB runs on CentOS 6.3, it is the free (as-in-beer) available 'standard version'.


texts: How can I do a query that shows any row that have more than twice in the same table without using GROUP BY or COUNT

texts:
I want to select all same item that occurs in the same table twice without GROUP BY or COUNT sentence. How to write the query?

For example, if the table is:

City
---------
New York
Los Angeles
London
Washington
London
Peking
London
San Fransisco
New York
Tokyo
Dubai

The result set should be like that:

City
---------
New York
London

(because 'New York' occurs 3 times and 'London' occurs twice in the original table)


texts: How to separate a part of a string from other parts in Oracle SQL using regular_expression

texts:
I have a string like this :
string := ' (sum(x) = 12 and
             sum(y) = 13 and
             sum(z) = 14
            ) and
             w = 'p'   AND
             q = 'p'   AND
             l = 'p''

and I need to separate all sum(...) from the rest of the query:
where_Clause:=   w = 'p'   AND
                 q = 'p'   AND
                 l = 'p'

having_Clause :=  sum(x) = 12 and
                  sum(y) = 13 and
                  sum(z) = 14 and

I'm sure there is a way to do that by regular_expression but cause I'm very new to oracle pl-sql , I need some help. I was wondering if you could help me with that.


texts: Table partition in aws MYSQL RDS master slave setup

texts: we are looking to do a table partition in MYSQL RDS running 5.6, table size is large and takes around 6 hours to partition. Can we partition on rds slave and then later promote it to master. is this do able, if not what is best way to partition this large table


texts: Can I change CITEXT columns to VARCHAR without encountering any surprise difficulties?

texts:
I went overboard on CITEXT columns on a particularly large table in my application. I would like to back some of these out as it's confusing how to trigger lookups on the desired indexes.

My question is, can I do this without encountering any major difficulties? And if I change this, do I need to rebuild any indexes on those fields?

Will there be any space gains from moving in this direction?

Case-insensitive queries are not necessary on these columns.

I have counts against this table that are predicated on 2 columns, and these counts are taking over an hour. The table has 60 columns.

I am using Postgres 10.6.

I'm primarily interested in whether indexes need to be rebuilt if they contain a column that has changed from CITEXT to VARCHAR.


texts: How to create a database user for oracle 11g on windows 8?

texts:
I am new to oracle database and therefore is trying to follow the office guide. (link to the guide) However, it seems that the guide is for Windows 7, so in step 2 - creating database user, it says:

  Display the SQL command prompt window. For example, on Windows, click Start, then Programs (or All Programs), then Oracle Database 11g
  Express Edition, and then Run SQL Command Line.

And in windows 8, there is not start button. I try to search the menu using keyword oracle but nothing with similar name is found. I tried to go to the Program Files directly but also no similar file found. So, how should I create a database user on Windows 8?


texts: The PostgreSQL database cannot start after modifying the IP

texts:
The following is the operation process:
[root@freelab ~]# su - postgres
Last login: Sat Jul  4 10:16:58 EDT 2020 on pts/1
-bash-4.2$ /usr/pgsql-12/bin/pg_ctl -D /postgres -l logfile start
waiting for server to start.... stopped waiting
pg_ctl: could not start server
Examine the log output.
-bash-4.2$
-bash-4.2$ date
Sat Jul  4 10:35:13 EDT 2020

But the log does not seem to be updated accordingly
[root@freelab log]# tail -f postgresql-Sat.log
2020-07-04 09:23:14.930 EDT [1832] LOG:  background worker "logical replication launcher" (PID 1840) exited with exit code 1
2020-07-04 09:23:14.930 EDT [1835] LOG:  shutting down
2020-07-04 09:23:14.943 EDT [1832] LOG:  received SIGHUP, reloading configuration files
2020-07-04 09:23:14.943 EDT [1832] LOG:  parameter "data_directory" cannot be changed without restarting the server
2020-07-04 09:23:14.944 EDT [1832] LOG:  configuration file "/postgres/postgresql.conf" contains errors; unaffected changes were applied
2020-07-04 09:23:15.052 EDT [1832] LOG:  database system is shut down


texts: Force replication to statement for INSERT to a table to fire trigger on slave

texts:
We have a PROD DB which replicates into a slave DB using mixed replication. We want to add a trigger so that a row is added to our DW when a row is INSERTed into table_a (on master). The issue is that this INSERT is coming through using Row-based replication and the trigger (which is on table_a on slave) is not firing. We need to have the trigger on the slave table as that is where our DW is.

Looking around online it looks like this should work if statement-based replication is used. Is it possible to force the INSERT to table A to be processed as statement-based replication? Or is there any other way we can achieve this?

The INSERT itself is deterministic as is the trigger. We are using MySQL 5.6.

If you need any other information please let me know.