Is there a way to run mysqldumpslow against slow queries logged to a table? AWS RDS by default logs slow queries to a database table.

Is there a way to run mysqldumpslow against that table, without exporting it to a file first?  Or is there a better way to get a report of slow queries?

mysqldumpslow only works on text files, so you need to forget about using it.

The table mysql.slow_log is a  CSV table by default.

mysql> show create table mysql.slow_log\G
*************************** 1. row ***************************
       Table: slow_log
Create Table: CREATE TABLE `slow_log` (
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
1 row in set (0.00 sec)

mysql>

You do not want to query against a CSV table as it will do full table scans every time.

What you can do is create a copy of the table into another database and index start_time

DROP DATABASE IF EXISTS my_slow_log;
CREATE DATABASE my_slow_log;
CREATE TABLE my_slow_log.slow_log LIKE mysql.slow_log;
ALTER TABLE my_slow_log.slow_log ADD INDEX (start_time);
ALTER TABLE my_slow_log.slow_log DISABLE KEYS;
INSERT INTO my_slow_log.slow_log
    SELECT * FROM mysql.slow_log
;
ALTER TABLE my_slow_log.slow_log ENABLE KEYS;

Then, you can query against my_slow_log.slow_log by datetime values

I have discussed this before

Nov 18, 2011 : Create a custom log table in MySQL
Mar 30, 2012 : MySQL slow log configuration

Give it a Try !!!

CAVEAT: You would have to reload the data every time you want the latest slow logs.

In that event, if you want just want slow logs from midnight 3 days ago, do this:

TRUNCATE TABLE my_slow_log.slow_log;
ALTER TABLE my_slow_log.slow_log DISABLE KEYS;
INSERT INTO my_slow_log.slow_log
    SELECT * FROM mysql.slow_log
    WHERE start_time >= DATE(NOW() - INTERVAL 3 DAY) + INTERVAL 0 SECOND;
;
ALTER TABLE my_slow_log.slow_log ENABLE KEYS;

or just in the last 3 hours

TRUNCATE TABLE my_slow_log.slow_log;
ALTER TABLE mysql.slow_log DISABLE KEYS;
INSERT INTO my_slow_log.slow_log
    SELECT * FROM mysql.slow_log
    WHERE start_time >= (NOW() - INTERVAL 3 HOUR);
;
ALTER TABLE my_slow_log.slow_log; ENABLE KEYS;

UPDATE 2014-12-11 14:50 EST

Your comment

  This makes querying the data a bit quicker, but I'm interested in how to provide a summary of slow queries that I can then pass along to our developers

You could present this to your developers by requesting slow logs entries as follows:

Today's 20 worst performing queries

SELECT query_time,sql_text
FROM my_slow_log.slow_log
WHERE start_time >= DATE(NOW()) + INTERVAL 0 SECOND
ORDER BY query_time DESC
LIMIT 20;

Count of Slow Queries By the Hour over the Last 2 Weeks

SELECT hr,COUNT(1) SlowQueries FROM
(
    SELECT (unix_timestamp(start_time) - MOD(unix_timestamp(start_time),3600)) hr
    FROM my_slow_log.slow_log
    WHERE start_time >= DATE(NOW() - INTERVAL 2 WEEK) + INTERVAL 0 SECOND
) A GROUP BY hr;

Count of Slow Queries By the Minute over the Last 6 Hours

SELECT mn,COUNT(1) SlowQueries FROM
(
    SELECT (unix_timestamp(start_time) - MOD(unix_timestamp(starttime),60)) mn
    FROM my_slow_log.slow_log
    WHERE start_time >= ( NOW() - INTERVAL 6 HOUR )
) A GROUP BY mn;

Queries like these can give you stats when slow queries run and what the actual queries were.

You can send these queries to a text file and send them to the developers

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT query_time,sql_text FROM my_slow_log.slow_log"
SQL="${SQL} WHERE start_time >= DATE(NOW()) + INTERVAL 0 SECOND"
SQL="${SQL} ORDER BY query_time DESC LIMIT 20"
mysql ${MYSQL_CONN} -ANe"${SQL}" > Slow_Queries_Report.txt


Can't run query using "localhost\sqlexpress" I am trying dynamically build an SQL Statement that includes linked server. That works. When I use "localhost" to reference the current local machine SQL Server, that doesn't work.

For example:

Select * From [LOCALHOST\SQLEXPRESS].[My_Database].[dbo].[My_Table]

However, if I specify my machine name instead, it will work.

Select * From [My_Machine\SQLEXPRESS].[My_Database].[dbo].[My_Table]

Why can't I reference Localhost?

You can only reference servers that are listed under Server Objects -> Linked Servers as well as the local server via what you get back from @@SERVERNAME. Four part naming does not trigger a NETBIOS / DNS lookup. If you are referencing the local machine anyway, why not just use three part naming?


Postgres - count of GROUP BY I am trying to determine the total number of contributors in an image database who have contributed at least five images. The following query will give me counts of images by contributor, but that's only part of the puzzle. I'm sure this is simple to someone who has more knowledge than me :)

SELECT count(*) AS i_count,contributor_id FROM images GROUP BY contributor_id ORDER BY i_count DESC

To count how many contributors have contributed 5 images or more:

SELECT COUNT(*) AS number_of_contributors
FROM
  ( SELECT 1
    FROM images
    GROUP BY contributor_id
    HAVING COUNT(*) >= 5
  ) AS t ;

It could be written without the derived table but it's obfuscated:

SELECT COUNT(*) OVER () AS number_of_contributors
FROM images
GROUP BY contributor_id
HAVING COUNT(*) >= 5
LIMIT 1 ;

(there is a slight difference between the two queries. If there are no contributors with 5+ images, the first query will return one row with 0. The second query will return no rows.)


I'm trying to create a database for a hotel company with multiple hotels. I'm stuck on how to reserve a room? I'm using Xampp phpmyadmin for the first time...
So I've got the basics of the database. Customers, hotels, rooms etc.
The customer can chose a check-in and check-out date.
An admin can class a date as available/not.
My question, is how would I get the database to automatically update the dates as unavailable?
When I've tried, it makes that date unavailable for every room in every hotel.
(Still a newbie at all this, so sorry if this doesn't make any sense!)
Attached a rough draught of my ERD for reference.

I started making a comment but noted that it would be too long. I'm not sure what parts of your ERD are given, and what parts you have created. Anyhow, here are some reflections
A room exists in a hotel, no? Assume your hotel H1 have 10 rooms and hotel H2 have 20 rooms. What prevents someone from booking room 11 in H1?
I don't understand your entity for availibility. If there are no bookings, nothing is availible? I would say that availibility is defined as (All dates x All rooms) - Bookings. Question is, do you need to store this somewhere, or can you derive it?


SSISDB on AlwaysOn Group running package on Secondary Replica I have sql server 2016 Enterprise Edition. I have created AlwaysOn Group. and added SSISDB. I do have couple SSIS packages running on primary replica with no errors. but when failing over to second replica, the jobs fails.

Asking to create master Key or certificate.

so my question is this: is there a way to automate this and whenever FaillOver happen the jobs should be running as they are on Primary replica?

Thank you all.

Rashid

... but when failing over to second replica, the jobs fails.
Asking to create master Key or certificate.

This is due to how security works with keys in SQL Server. There are various methods around this.

is there a way to automate this and whenever FaillOver happen the jobs should be running as they are on Primary replica?

For the first part, yes, see the second link above. For the second part, IMHO you shouldn't be executing any SSIS jobs on a SQL Server because they don't play well together in terms of sharing resources. Period. You're asking for performance issues when doing this and I'd stay far, far away from it.


SQL query using an adjacent field value as a criterium 3 tables in a database. Table1 is tabCategory, Table2 is tabSub, Table3 is Parts.
Table3, Parts, is populated with 3 columns, multiple entries, thousands of them.   Column1 is the part number,   column2 is the Category   and column3 is the Type from tabSub.
The user must fill column1 (PartNumber) according to the part number it wants to add in the DB.
He must also enter the Category (PartType) for his part, wich is limited to a list form Table1, tabCategory.   He must also enter the SubCategory limited by a list from Table2, tabSub. Table2 is built with some 40 subcategories to which correspond only 10 different categories.
For example: part: DC23, Category:Capacitor, SubCategory:Mylar...if the part was a Transistor the Catgory would be a Semiconductor and the SubCategory could not be Mylar.
The Challenge: How can I make an SQL query that would deliver a list of all the available SubCategory using as a criteria the adjacent field Category chosen by the user?

Something like this (which does not work):
SELECT tabSub.Category, Amplifier.Category
FROM tabSub INNER JOIN Amplifier ON tabSub.Type = Amplifier.PartType
WHERE (((tabSub.Category)=[Amplifier].[Category]));
The goal being to limit the available user choices based on his entry in the Category field for that particular record. This Query result would be used to limit his list of choices.
Thanks for your suggestions. Cheers

You just need to make your tables have the proper Foreign Key Constraints

CREATE TABLE categories
(
  /* The category name is actually its natural primary key */
  category CHARACTER(100) PRIMARY KEY
) ;

CREATE TABLE subcategories
(
  /* All subcategories have a 'parent' category and a 'subcategory' itself */
  /* REFERENCES is one way to define a Foreign Key constraint */
  category CHARACTER(100) NOT NULL REFERENCES categories(category),
  subcategory CHARACTER(100) NOT NULL,

  /* The primary key is actually the 2 columns */
  PRIMARY KEY (category, subcategory)
) ;

CREATE TABLE parts
(
  part_number CHARACTER(10) PRIMARY KEY,
  category CHARACTER(100),
  subcategory CHARACTER(100),
  part CHARACTER VARYING (255),

  /* The pair (category, subcategory) must appear on the 'subcategories' table */
  /* This is actually a second way of defining a Foreign Key Constraint */
  CONSTRAINT
      FOREIGN KEY              (category, subcategory)
      REFERENCES subcategories (category, subcategory)
) ;

Some sample data:

INSERT INTO
  categories (category)
VALUES
  ('Capacitor'),
  ('Resistor') ;

INSERT INTO
  subcategories (category, subcategory)
VALUES
  ('Capacitor', 'Mylar'),
  ('Capacitor', 'Ceramic'),
  ('Resistor', 'Carbon'),
  ('Resistor', 'Film'),
  ('Resistor', 'Mylar' /* if it ever made sense */) ;

INSERT INTO
  parts
  (part_number, category, subcategory, part)
VALUES
  ('DC23', 'Capacitor', 'Mylar',   'High voltage capacitor'),
  ('DC24', 'Capacitor', 'Ceramic', 'Higher voltage capacitor') ;

You can check it at SQLFiddle

The SQL that gets the list of all possible subcategories, given one category, is straightforward:

SELECT
     subcategory
FROM
     subcategories
WHERE
     category = 'Resistor'
ORDER BY
     subcategory ;


Optimizing query in PostgreSQL that tries to match a string and matches a timestamp range I am building a database in PostgreSQL for financial data, where the table looks like this:

create table fin_data(
    brokerid text,
    stock int,
    holding bigint,
    stake float,
    value bigint,
    price float,
    holding_time tstzrange,
    unique(brokerid, stock, holding, holding_time));

This seems to already create a default index called: fin_data_brokerid_stock_holding_holding_time_key

How the data is organized: Each 'stock' has anywhere from 100-500 'brokers' holding a certain # of shares per day. I have around 95 million rows.

Example of my main query is:

select *
from fin_data
where brokerid='C00019'
AND holding_time @> '2015-09-28 00:00:00+08'::timestamp with time zone;

Which basically means that for a specified broker, I want to find out how many shares a certain broker holds on a certain day.

Unfortunately, this is not a very fast query, as my explain analyze looks something like this:

Bitmap Heap Scan on fin_data
    (cost=115762.08..1362596.53 rows=14079 width=64)
    (actual time=571.648..1729.416 rows=1840 loops=1)
  Recheck Cond: (brokerid = 'C00019'::text)
  Rows Removed by Index Recheck: 116323
Filter: (holding_time @> '2015-09-28 00:00:00+08'::timestamp with time zone)
  Rows Removed by Filter: 2054316
->  Bitmap Index Scan on fin_data_brokerid_stock_holding_holding_time_key
  (cost=0.00..115758.56 rows=1982116 width=0)
  (actual time=569.477..569.477 rows=2056156 loops=1)
         Index Cond: (brokerid = 'C00019'::text)
Total runtime: 1729.933 ms (8 rows)

Runtime is 1.73 seconds.

This could easily go up to 2-3 seconds for certain brokers, and is somewhat acceptable but I want it to be under 1 second if possible.

The strange thing is, when I later made an index on (stock, holding_time) only, some of those queries would be in the sub-100ms range, usually 100-300 ms. The analysis might look like:

Index Scan using stock_date_index on fin_data
    (cost=0.57..790795.62 rows=3136 width=64)
    (actual time=3.825..411.985 rows=644 loops=1)
    Index Cond: (stock = 5)
    Filter: (holding_time @> '2015-09-28 00:00:00+08'::timestamp with time zone)
    Rows Removed by Filter: 447426
  Total runtime: 412.123 ms (5 rows)

Which is much more desirable.

A hard fact about the data is that each stock might only have up to 500 brokers, but some brokers could have up to 1000+ stocks. Is that the only reason my queries are so much slower? How do I optimize this?

Default B-Tree indexes can't operate very intelligently on range data types.  What you want is a GiST index on the range, so something like:

create index on fin_data using gist (holding_time);

For your main query, this would probably give a BitmapAnd plan which would use one index to get the list of row pointers satisfying broker_id and the other to get the list satisfying holding_time, and then take the intersection of the lists before visiting the table.  This could be quite a bit faster.  This is the most flexible case, because that gist index can be used in combination with any other indexes you have.  If that is not fast enough, you could try create extension btree_gist and then making the index:

create index on fin_data using gist (broker_id, holding_time);

This would be less flexible for other query variants, but ideal for the query you identified as your main query.

Note that GiST indexes take a lot longer to initially build than B-Tree indexes.  They are also are slower to maintain when new rows are inserted, so if your system struggles to keep up with insertion rate, you would want to benchmark it first.

Another idea which doesn't involve making more indexes, is to select just the columns you need rather than '*' in your queries.  That might allow index-only scans to be used where they otherwise cannot be.


Mongo Super User won't connect to another database other than admin Why is that?

$ mongo admin -u admin -p password --host 192.168.99.100
MongoDB shell version: 3.2.9
connecting to: 192.168.99.100:27017/admin
> show users
{
    "_id" : "admin.admin",
    "user" : "admin",
    "db" : "admin",
    "roles" : [
        {
            "role" : "userAdminAnyDatabase",
            "db" : "admin"
        },
        {
            "role" : "dbAdminAnyDatabase",
            "db" : "admin"
        },
        {
            "role" : "readWriteAnyDatabase",
            "db" : "admin"
        }
    ]
}
> db^C
bye
cesco@laptop: ~/code/go/src/bitbucket.org/cescoferraro/cluster/containers on develop [!$]
$ mongo iot -u admin -p password --host 192.168.99.100
MongoDB shell version: 3.2.9
connecting to: 192.168.99.100:27017/iot
2016-09-25T22:22:36.829-0300 E QUERY    [thread1] Error: Authentication failed. :
DB.prototype._authOrThrow@src/mongo/shell/db.js:1441:20
@(auth):6:1
@(auth):1:2

exception: login failed

Although the admin user you've created has global permissions through *AnyDatabase roles, the user credentials only exist in the admin database. As per your example, you will fail to authenticate if matching user credentials don't exist in the database you are trying to access.

To authenticate with the mongo shell using credentials stored in a different database from the one which you are trying to access (admin vs iot) you can either:

1) Specify the database with user credentials using --authenticationDatabase:

 mongo iot --authenticationDatabase admin -u admin -p password --host 192.168.99.100

2) Specify the default database as /admin and then change to the desired target database:

 $ mongo -u admin -p password 192.168.99.100/admin

 MongoDB shell version: 3.2.9
 connecting to: 192.168.99.100/admin

 > use iot
 switched to db iot

The first example is the most typical approach.

NOTE: If you want to specify host and database as per the second example, you have to provide the database connection string without using the --host parameter.


SQL Server 2008 R2 : LDF file size not increasing I am using SQL Server 2008 R2. I have a table statDB of 100 GB in primary filegroup.

I have created a secondary filegroup in same database (Lab1) and created a table copyStatDB.

Now I start copy table data from primary file group to secondary file group.

I have noticed that none of my TempDB size change and not my .LDF file size change.

I am surprised to see that because as per my understanding when we execute a Insert statement it should increase .LDF file size increase first then copy data to my .NDF file.

I wouldn't expect a copy operation to increase file sizes unless the file size was too small to begin with.  SQL Server will first use unallocated space within data files before growing the file.  Similarly, if the log file is large enough for the operation, that file shouldn't grow either.

Also regarding log space usage, some operations can be minimally logged to reduce logging requirements.  Whether or not your INSERT...SELECT is fully logged depends on the database recovery model and indexes on the target table.


Parent Child Relation in same Table . Select Query to get only parent records and Records not comes under parent child relation I have a table with Parent-Child Relation in the same Table. Some Sample Data

  id │ name                 │ parent │ is_active
 ════╪══════════════════════╪════════╪═══════════
  1  │ Company A            │ null   │ true
  2  │ Child of A           │ 1      │ true
  3  │ 2Child of A          │ 1      │ true
  4  │ 3Child of A          │ 1      │ true
  5  │ 0 Single Company     │ null   │ true
  6  │ 1 Single Company     │ null   │ true
  7  │ 2 Single Company     │ null   │ true
  8  │ Parent Company B     │ null   │ true
  9  │ 0 Child Company of B │ 8      │ true
  10 │ 1 Child Company of B │ 8      │ true
  11 │ 2 Child Company of B │ 8      │ true
  12 │ 3 Child Company of B │ 8      │ true

I want to write two queries

get all the parent records (only parent records)
Example: id 1,8
Get all the single records (only records does not come under parent-child)
Example: 5,6,7

Please help to write select queries.

SELECT t1.*
FROM "table" t1
WHERE t1.parent IS NULL
  AND /* NOT */ EXISTS (SELECT 1
                        FROM "table" t2
                        WHERE t1.id = t2.parent)

EXISTS - for parent records.

NOT EXISTS - for single records.


I have a database backup (.bak) from SQL Server v7, how do I convert it for load into v10? A client has given me a backup (.bak) from a SQL Server v 7 database.  I am trying to load it into version 10 which is incompatible.  Are there any tools that will restore or prepare an old version backup to be restored into version 10?

EDIT: Per request, here is the text from the error dialog

  The database was backed up on a server running version 7.00.1063. That version is incompatible with this server, which is running version 10.00.1600. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3169)

You can restore a SQL 7 backup on a SQL 2005 instance, then backup/restore or detach/attach to SQL 2012 or 2014.  "version 10" is SQL 2008, so you wouldn't want to stop there, probably.  You can get old installs of SQL from MSDN Subscriber Downloads, or the Express Edition is still available for download.


Using transactional replication to roll back from SQL server 2014 to 2008R2 I know it sounds crazy. However its just a thought given by DBA,s and management to check on.

We are going to use LS to do migration of around 10 TB Db from 2008R2 to SQl 2014. This DB is 24*7 in used and an OLTP in nature.

In case we need a revert due to XYZ issues, we are thinking to use tran replication from 2014 to 2008R2 , the old server so as to make sure we don't loose any activity since migration. Also on 2014 db will be configured for mirroring .

I am not sure how much above is helpful. Please provide your thoughts if this can be achieved or for any better way of doing this.

I have recently implemented your scenario for one of my company's critical - ordering & sales system's migration from 2008 to 2014.

You have 2 options for rollback depending on the downtime that you are allowed -

Upgrade :

Follow the pre-migration steps
setup DB mirroring from 2008r2 to 2014 in async mode.
Before cutover, change the mirroring to sync mode and make sure that mirroring queues are caught up before doing failover.
Perform mirroring failover. This is one way . You cannot failover to lower version.
disable all the application logins on both the servers (script out disable and enable). This is very important since you want to make sure that both the copies (2008r2 and 2014) are exactly same while you are configuring rollback options.

Rollback plan (Going backwards comes with a price that you need to agree with the business and application stake holders) :

Option 1 : Use transactional replication if you can accept downtime. The downtime will be the amount of time it takes to take a full backup of your 10TB database.
You can setup distribution database on 2014 ahead of time.
During backup, no data changes should be done on either side.
Once the backup is done, the replication will just work fine without doing snapshot as the LSNs are same on both sides.
I did not go with this !

-

Option 2 : Use Peer-to-peer replication if you need very minimal downtime.
The downtime here is equal to the time it takes to create the peer and related jobs which depending on the size of tables (article) will vary. In my case for a 700GB database, it took 4 mins. I scripted entire replication and used .bat scripts to fire replication scripts on both sides 2014 and 2008.
P2P needs to have both dbs exactly same. Since you disable all the logins and not activity is happening, this is my preferred (and now a defacto) option wherein I have to provide rollback option.

Once you configure rollback option (either T-rep or P2P), if you run into major issues, you can just point your application to the 2008R2. What we did is we use Failover Partner, this way we just shutdown the 2014 server and application will automatically redirect to 2008 server.

Important Notes:

I used P2P replication as downtime had to be less than 15 mins.
Above both options requires that all the tables have primary keys defined. Tables without PKs e.g. staging tables needs to be discussed with stake holders and excluded.
We kept replication for only 1 day as fallback/rollback plan. If we did not see any issues after migration (done during weekend) and survive 1 business day (monday) we tear down replication and setup DBs into AG in 2014.
We did not change the compatibility level to 2014. We kept it same as 2008. I would highly recommend to do that unless you have thoroughly tested your application on 2014 in downstream environments. Even if you change the   compatibility level to 2014 and you see query regressions you can enable TF 9481 to tell optimizer to use legacy CE.

Fair Warning: I have implemented above in production with full success - but I did extensive testing and invested time in automating the entire process and hence I can confidently give the process and timings. Highly suggest you to death test the process & document it before you implement it in PROD.


Which problems arise, declaring the size of all varchar parameters as max in stored proc? Looking at this question, it seems, that other sites too have the problem of increasing column sizes in the process of time.

Increasing just the size of a column in a table is a rather simple task.

But when your shop adheres to the rule that the length of stored procedures parameters has to match the length of the corresponding table columns, an avalanche of changes through your stored procedures goes down hill.

Locking at Oracle shows, that procedures don't need to know about the length of varchar2 parameters.

What are the pros and cons, when you just declare each varchar parameter as varchar(max) or nvarchar(max)?

Obviously you can forget the maintenance nightmare of having to adjust your procedures each time you increase your column sizes.

Answer to SQLRockStar:
I want to keep a rather conservative table design, where fields, with a known limited length are modeled according to the current restrictions without reserve for possible future extensions.

Many of my tables contain an id int, a code varchar(30) and a description column varchar(255).

The code field has to reflect local naming conventions of different customers.

Some costumers need this field increased to 50.

When I do not code the length into the procedure parameters, I need only adjust the table column of this single costumer.

When I insist to code the length into the parameters, then I have either to support different versions of procedures for different customers, or I have to change and roll out the change  to all customers.

Example

-- SQL Server
Create Table dbo.SQLServerTable (
    id int identity(1,1) primary key not null,
    code varchar(30) not null,
    description varchar(255) not null
);
go
Create Procedure WrapInsertSQLServer1 (
    @code varchar(30),
    @description varchar(255)
) as
INSERT INTO SQLServerTable values (@code, @description);
go

Create Procedure WrapInsertSQLServer2 (
    @code varchar(100),
    @description varchar(1000)
) as
INSERT INTO SQLServerTable values (@code, @description);
go

Note: When you increase the length in SQLServerTable by some character, you have to adjust WrapInsertSQLServer1, but WrapInsertSQLServer2 doesn't need to be changed.

-- Oracle
Create Table OracleTable (
    id NUMBER(10) primary key not null,
    code varchar2(30) not null,
    description varchar2(255) not null
)
/
CREATE SEQUENCE S_OT START WITH 1
/

Create OR Replace Procedure WrapInsertOracle (
    p_code varchar2,
    p_description varchar2
) as
begin
INSERT INTO OracleTable values (S_OT.NEXTVAL, p_code, p_description);
end;
/

Here the parameters of a stored procedure never have a length.

If you say WrapInsertSQLServer2 isn't OK => you are saying Oracle is inherently wrong

Since your talking about the proc params:

CREATE PROCEDURE myProc
  @var1 varchar(30),
  @var2 varchar(max)
AS
...

If the parameters are defined as the correct length, SQL will throw an error when calling the procedure. If you always define as (max) then you'll have to manually handle passed parameters that are too long for the destination table column before any processing in the proc happens.

So in a nutshell, more work on your end since you'll have to add error handling to the proc you didn't need before.


DBCC CHECKDB - SQL Log Says Completed, SQL Server Agent Says Failed I run a nightly Integrity Check (set up via Maintenance Plans) on all of my databases. For the last two nights, the final CHECKDBCC has failed... at least according to the SQL Server Agent Job.

  Progress: 2019-01-25 03:51:01.15     Source: Check Database Integrity      Executing query "USE [CellTestData_QC]  ".: 50% complete  End Progress
  Error: 2019-01-25 05:14:43.79     Code: 0xC002F210     Source: Check Database Integrity Execute SQL Task     Description: Executing the query "DBCC CHECKDB(N'CellTestData_QC')  WITH NO_INFOMSGS..." failed with the following error: "A severe error occurred on the current command.  The results if any should be disca...  The package execution fa...  The step failed.

But if I check the SQL Server Logs, I get two conflicting messages.

First I get:

  01/25/2019 05:00:39,spid105,Unknown,DBCC CHECKDB (CellTestData_QC) WITH no_infomsgs executed by sa terminated abnormally due to error state 6. Elapsed time: 1 hours 9 minutes 38 seconds.

But then I get:

  01/25/2019 05:32:06,spid110,Unknown,CHECKDB for database 'CellTestData_QC' finished without errors on 2019-01-24 23:01:46.507 (local time). This is an informational message only; no user action is required.

So what exactly is going on here? Is my process completing? Or erroring out?

For a bit more context...

We restore [CellTestData_QC] every morning at 5AM (likely why I'm getting the SQL Server Log message of 'terminated abnormally at 5AM).
Additionally, my backups of [CellTestData] and integrity checks of [CellTestData] and [CellTestData_QC] have been taking longer over the last few days - possibly because of issues with our SAN, which is why we're getting the IntegrityCheck of [CellTestData_QC] beginning to run into the restore of [CellTestData_QC]
Integrity checks run every night at 11PM
DB backups (full and partial) run nightly
Transaction Log backups run every 15min
Currently working with 8 databases, that range in size from 4MB to 100GB, with a total of 330GB of databases
I'm running SQL Server Standard 2012 on a 64-bit Windows Server VM (using vSphere) with 64GB RAM, 1TB SAN storage, 175GB NAS storage, 4CPU, 4.9GHz

Any ideas what might be going on here?

There is no mystery, the 2 messagges for CellTestData_QC reference 2 different databases CellTestData_QC.

The first one is your database CellTestData_QC on which DBCC CHECKDB did not complete because you began a restore.

The second message refers to the restored database CellTestData. It's not DBCC CHECKDB that was running on 25/01, it's just an info that is stored within CellTestData, and it reports the last date when DBCC CHECKDB completed without errors on this database.

Every time the database is opened (goes online), the last known good dbcc checkdb date is reported in eroor log. So the second message tells you that DBCC CHECKDB completed successfully on your database CellTestData on 2019-01-24 23:01:46.507. This message appears in error log as soon as restore is completed.

You can see this on every server restart. SQL Server opens every database that should go online and within every database it reads the date called last known good time and reports it in errorlog. This does not mean that checkdb runs on every restart, in fact, the date reported is always earlier than the current date.

For example, if you run checkdb every Sunday, and the restart occurs on Saturday, you'll see these messages reporting previous Sunday date in errorlog as it was the last date when dbcc checkdb completed successfully.

Here CHECKDB From Every Angle: When did DBCC CHECKDB last run successfully? you can read more on last-known good time.


Questions on updating statistics and index maintenance jobs Since my question Index and statistics optimization scripts duration and log bloat problem. Looking for good strategy? has been closed because it was not focused enough I've three questions:
For statistics and index optimization I want to use Ola Hallengreens scripts.

If I update statistics only or rebuild/reorganize indexes: Is there anyting dangerous by cancelling the job right in the middle of it if I see logfile becomes too big or the job takes too long and slows down daily work?

If not there is nothing I need to worry about and I can run it maybe on any evening or on a saturday and watch how it works and cancel it with no problem if some of the mentioned problems occur. Is this true?

If I use Olas script configured like this (no offline index rebuild):

FragmentationLevel1 = 50%
FragmentationLevel2 = 80%
FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE’
FragmentationHigh = ‘INDEX_REBUILD_ONLINE’
@UpdateStatistics nvarchar(max) = 'ALL',
@OnlyModifiedStatistics nvarchar(max) = 'Y',

Can I use this safely during worktime when people are working on the database?

Is there any dangerous by doing such maintenance jobs (statistics update/index rebuild/index reorganize) which could damage the database if I use Ola with those parameters? The only thing I've seen where growing a large log file. Or can I install this with no worries and if anything occurs just cancel it (see question 1)?

For question 1 - Cancelling or stopping the job after it is doing work will not roll back the work that has already been done, so there is no harm to stop it or kill the execution.
For question 2 - Keep in mind that online index rebuilds do still take some locks and there are limitations involved as to whether an online index can be performed. I would look here for a discussion of those limitations. I probably wouldn't run this script during the day, even if you can get the online index rebuilds to happen. Index maintenance can be resource intensive.
For question 3 - Ola's scripts aren't going to do anything that is going to damage the database.
When I looked at your other question, I had to wonder whether the index maintenance routine is causing the log file growth.
You say, "Because of the problem with the log partition running full after every database update I've received a different script which where I can set Proportion of maximum indexes to be maintained per database. But I think this script is not as powerful as olas script, maybe because it doesn't update statistics or doesn't rebuild enough indexes? I've inserted a lot of new data recently and some querys are really slow since then and it's up to the database."
It seems to me that part of your complaint is things run slow after you update and change a large amount of data and people suggested updating the statistics. You could do that with a separate Ola job with T-SQL like the below, which is on his site.
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL'


Setup MySQL Master to Master replication/automatic failover when each Master also has a Slave Thanks in advance.

I have always worked with SQL Server and never really had any exposure to MySQL. I now have to determine the preferred setup for MySQL when there are four servers involved. Ideally, the solution needs to allow for Master to Master replication/automatic failover with each Master replicated to its own Slave. The Slave does not need to be promoted to a Master in any scenario, but it does need to be updated from its Master.

In .Net, and with SQL Server, it was always easy to set up mirroring/failover with a witness and then simply incorporating the "failover" server directly in the configuration file's connection string. MySQL seems to be much more involved.

I've read a good bit about MySQL replication and the potential pitfalls it may include and I would like to avoid those if possible. I've also started looking into MySQL clustering, but I'm not sure that is the best solution here since the servers are setup to be separate from each other in a primary/secondary set (with the Master in each being replicated).

Ultimately, it is just Primary Master <-> Secondary Master and then a Slave behind each Master. If there is a better solution such as using all four servers (even though separated by Primary/Secondary domains) with clustering etc. please reply also - just trying to determine the best setup.

I should note that this is for installation on Windows Server 2012 R2.

It is difficult to find this setup anywhere through searching!

Any help is greatly appreciated!

MHA orchestrates the reconnection of slaves as needed.
Orchestrator orchestrates the reconnection of slaves as needed.
Galera (PXC, MariaDB) eliminates the need for failover by having all nodes writable, plus automatic repair of nodes that were offline.
Fabric is another way.
Doing the task yourself is possible, but complex, risky, etc, etc.


How to execute MySQL query after 1 minute I have a query, but I want to call it now and wish it to be executed after few mins and seconds. Please note: I don't want to use SLEEP(), as it 'freezes' the entire database. I want one of the queries to be delayed while I perform the others queries in a default manner.

Small example that doesn't works:

WAITFOR DELAY '00:00:10'
update users set 'id' = 10 where `id` > 5

WAITFOR DELAY is a SQL Server command

How do I solve this problem?

You could create an event.

Rather than SLEEP in the same statement as an update try: 'SELECT SLEEP(360); UPDATE ....'.


Is indexing the following columns efficient? I have the table: orders with the following columns

(orderNumber, orderDate, shippedDate, status, users_userId)

I indexed orderDate, shippedDate, status for performing similar queries to the following:

SELECT orderNumber FROM orders WHERE status='shipped'
ORDER BY orderDate, shippedDate;

Since there are only about 14 possible statutes for an order which I thought  would speed up the search whenever I need to select range of rows of orders with a specific status.

Also since indexes are sorted,creating an index on the orderDate and shippedDate it allows a query's ORDER BY specification to be met without a separate sorting step.

I just want to know if this sounds correct and if am I really optimizing my query in this way?

Here is the EXPALIN output of my query:

id: 1
select_type: SIMPLE
table: orders
type: ref
possible_keys: composite_index
key: composite_index
key_len: 138
ref: const
rows: 1
filtered: 100.00
Extra: Using where; Using index

For this query:

SELECT `orderNumber`
FROM orders
WHERE `status` = 'shipped’
ORDER BY orderDate, shippedDate;

Try a composite index on orders(status, orderDate, shippedDate, orderNumber).


How to total additional results from alias column results in Oracle SQL? I'm attempting to display the same number of invoices that have the same number of days between two specific dates (e.g. issuance date and payment date).  I can ascertain a segment of the aforementioned with the following query:

SELECT INVOICE_DUE_DATE - INVOICE_DATE AS "Difference in Days" FROM customer.invoices;

which displays the number days between those specified column-dates, but to clarify, I would like to create an additional column displaying how many instances occur between the number of days calculated from the above, preferably as another alias column- say "Number of Instances".  I suspect the solution might involve the ROLLUP operator or perhaps the CUBE operator but I'm unable to effect.  Here is my last unsuccessful attempt:

SELECT INVOICE_DUE_DATE - INVOICE_DATE AS "Difference in Days",
     COUNT(INVOICE_DUE_DATE - INVOICE_DATE) AS "Number of Instances"
FROM customer.invoices;

I'm also open to alternatives and I'd appreciate any insight with respect syntax and structure as well.  Thank you!

UPDATE

As requested, I've included a below screen-shot of the first query cited; so, in addition to the column below, I would like to create another alias column as "Number of Instances" counting the number of instances.  Thank you.

This is simply GROUP BY on the first column:

SELECT INVOICE_DUE_DATE - INVOICE_DATE AS "Difference in Days",
     COUNT(INVOICE_DUE_DATE - INVOICE_DATE) AS "Number of Instances"
FROM customer.invoices
GROUP BY INVOICE_DUE_DATE - INVOICE_DATE;


How to convert numeric values to text in sql for select query I have string values like this

'[123, 124]'

I could trim the value using below command

select trim('[123, 124]', '[]'); --returning '123, 124' as text

I want to pass the value above as

select *
from mytable
where numeric_column in (select trim('[123, 124]', '[]'));

I can understand, numeric_column is numeric. But the inner query is returning the data as text. I could not convert the inner query to numeric since it has a comma.  If I want to convert the result to '123', '124', I could run the following command and get the expected result:

select *
from mytable
where numeric_column::text in (
    select func_to_change(select trim('[123, 124]', '[]'))
);

How can I achieve this? What is the func_to_change logic I need to write?

Note: I am using Postgresql 9.1.

Once you have removed the [ and ] you can use string_to_array() to convert the list to an array of integers. This can be used directly in the where clause:

select *
from mytable
where numeric_column = ANY(string_to_array(trim('[123, 124]', '[]'),',')::numeric[])


Connecting to local Oracle Server via MacOS SQL Developer I am new to Oracle SQL, but I want to give it a shot and play around with the HR schema.
I don't want to use a VM, because I had a lot of issues with them.
Setup:
Oracle Server setup on WIN64 PC. HR and SYS works fine. (10.0.0.10 --> to understand later on)
Trying to connect via MacOS and SQL Developer (10.0.0.23).
On my Mac I am able to ping 10.0.0.10 and the other way around (10.0.0.10 (PC) to 10.0.0.23 (MAC)).
Ping from Mac to Pc
So then I setup my listener (listener.ora) and my tnsnames.ora in the way oracle wants me to do (examples folder).
Listener Setup
tnsnames setup
After this setup I rebooted the server.
Now I tried to connect as HR to my 10.0.0.10 machine.
(On my win pc I can connect and use HR and sys schema.)
connectionHR
After trying to connect, a "Testing connection" window opens and closes after 1 minute.
Status: No success: I/O-Error: The Network Adapter could not establish the connection

Ive also tried to import the tnsnames.ora file and start from this file, but this wouldn't work either.
EDIT:
I updated my listener.
Anmeldung bei (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS des LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 19.0.0.0.0 - Production
Startdatum                27-DEZ-2020 00:11:42
Uptime                    0 Tage 0 Std. 3 Min. 14 Sek.
Traceebene               off
Sicherheit                ON: Local OS Authentication
SNMP                      OFF
Parameterdatei des Listener C:\Users\Ben\Desktop\db_home\network\admin\listener.ora
Logdatei des Listener    D:\benOracle\diag\tnslsnr\DESKTOP-FT8E4S1\listener\alert\log.xml
Zusammenfassung Listening-Endpunkte...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=DESKTOP-FT8E4S1)(PORT=5500))(Security=(my_wallet_directory=D:\BENORACLE\admin\orcl\xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services ▄bersicht...
Dienst "52448234712340b69f274bcc790ecfe0" hat 1 Instanzen.
  Instanz "orcl", Status READY, hat 1 Handler f³r diesen Dienst...
Dienst "CLRExtProc" hat 1 Instanzen.
  Instanz "CLRExtProc", Status UNKNOWN, hat 1 Handler f³r diesen Dienst...
Dienst "cfc353defce0494a8a440e7d1f817c2d" hat 1 Instanzen.
  Instanz "orcl", Status READY, hat 1 Handler f³r diesen Dienst...
Dienst "orcl" hat 1 Instanzen.
  Instanz "orcl", Status READY, hat 1 Handler f³r diesen Dienst...
Dienst "orclXDB" hat 1 Instanzen.
  Instanz "orcl", Status READY, hat 1 Handler f³r diesen Dienst...
Dienst "orclpdb" hat 1 Instanzen.
  Instanz "orcl", Status READY, hat 1 Handler f³r diesen Dienst...
Der Befehl wurde erfolgreich ausgef³hrt.

Host= Localhost
Why is host still the local ?
--> This is in German. Comment if translation is needed.

All connections on the port 1521:

Your listener.Ora file is set to listen on localhost rather than your 10.0.0.10 ip. Change that, then restart the listener and make sure your instance is getting registered with it. Note that your tnsnames.Ora Isn’t involved here as you’re specifying everything in your sql developer connection, but you also need to correct the host there.


MIN() results are not correct I'm trying to find the the lowest street address number for every street in my area.  My results return the wrong value, so I need help understanding why it's returning the wrong value and how to correct it so I get the desired results.

I'm using SQLServer 2012

TABLE (A) columns:

ZONE_NUMBER (int, null),
Street_NUMBER (char(10), null),
Street_Name (varchar(255), not null),
Street_Type (char(5), null)

TABLE (A) values:

ZONE_NUMBER | STREET_NUM | STREET_NAME | STREET TYPE |
13             1502         COTTONWOOD    DR
13             1544         COTTONWOOD    DR
13             1564         COTTONWOOD    DR
13             720          COTTONWOOD    DR

Query:

SELECT
   zone_number,
   min(street_number) as street_number
FROM
   A
GROUP by ZONE_NUMBER

Expected Results:

ZONE_NUMBER | STREET_NUMBER
13            720

Actual Results:

ZONE_NUMBER | STREET_NUMBER
13            1502

If I change from min() to max(), I get my desired results:

ZONE_NUMBER | STREET_NUMBER
13            720

Why?  Is there something I'm missing?

Also, if '0' Street_number had it's own record, how can I skip '0' and get the next lowest result?

Thanks for any considerations for answering this problem.

Since you are using 2012, you can use TRY_CONVERT as Lamak alluded to if you can't change the street number data type. Note, if you have something other an an INT in this column, then your results will not contain these rows

SELECT
   zone_number,
   min(try_convert(int,street_number)) as street_number
FROM
   A
GROUP by ZONE_NUMBER


SQL Server - Error 8623 Severity 16 State 1 Fist of all, let me clarify that I'm brand new to this whole DBA world so I apologize in advance.

One of the databases I'm (since a couple of days ago) in charge of is throwing an Error 8623: "The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information."

This particular instance is running SQL Server 2014 SP2.

How do I find which query is causing this? I've been having a hard time finding documentation on this.

Thanks!

You can use Extended Events to capture failing queries.
Here is a link to a detailed howto article.
https://www.brentozar.com/archive/2013/08/what-queries-are-failing-in-my-sql-server/

TSQL to create xevents and start:

CREATE EVENT SESSION what_queries_are_failing
ON SERVER
ADD EVENT sqlserver.error_reported
(
ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.database_id, sqlserver.username)
WHERE ([severity]> 15)
)
ADD TARGET package0.asynchronous_file_target
(set filename = 'C:\XEventSessions\what_queries_are_failing.xel' ,
metadatafile = 'C:\XEventSessions\what_queries_are_failing.xem',
max_file_size = 5,
max_rollover_files = 5)
WITH (MAX_DISPATCH_LATENCY = 5SECONDS)
GO

-- Start the session
ALTER EVENT SESSION what_queries_are_failing
ON SERVER STATE = START
GO

-- Trigger Level 16 error
select * from Person.Person where ModifiedDate='20aaa';

--Query the logfile
;with events_cte as(
select
DATEADD(mi,DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP),
xevents.event_data.value('(event/@timestamp)[1]', 'datetime2')) AS [err_timestamp],
xevents.event_data.value('(event/data[@name="severity"]/value)[1]', 'bigint') AS [err_severity],
xevents.event_data.value('(event/data[@name="error_number"]/value)[1]', 'bigint') AS [err_number],
xevents.event_data.value('(event/data[@name="message"]/value)[1]', 'nvarchar(512)') AS [err_message],
xevents.event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS [sql_text],
xevents.event_data
from sys.fn_xe_file_target_read_file
('C:\XEventSessions\what_queries_are_failing*.xel',
'C:\XEventSessions\what_queries_are_failing*.xem',null, null)
cross apply (select CAST(event_data as XML) as event_data) as xevents
)
SELECT *
from events_cte
order by err_timestamp;


Innodb: When bulk inserting or updating - how to disable transactions? When doing a mass insert like INSERT INTO SELECT FROM the speed benefit of doing that in chunks of 10k records can be enormous, in my tests a query that was running for 5 days took only 2 hours using the chunk method.

To make things worse: if you let such an operation run for a few hours and KILL the job (or crash) then it can take days or weeks until ROLLBACK is finished.
Rollback is significantly slower than insert, no optimizations here.

The same counts for doing mass UPDATEs.

My question:
How can I completely disable transaction log for such a transactions ?

I tested this:    SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
But it showed no difference, when killing the job transactions start rolling back.

I really did my homework and tried to find anything about it, no luck.

P.S.
Of course I am aware that importing without transactions does not give a unique snapshot to rely on if anything is changed in meantime.

You cannot turn off transactions.

It is assumed that ROLLBACK is the exception, not the rule.  Are you trying to make it the "normal" path?

5 days vs 2 hours -- this is far more extreme than I would expect.  Perhaps you are running out of RAM?  Or swapping?

Please provide more details on the task; we may be able to help you 'solve' the problem in a way other than "turning off transactions".


Testing Geometry Polygon Data is Valid Using a Stored Procedure We are using a SQL Server Spatial database with the following table setup:

SRID of 27700
geometry_column of ogr_geometry
geometry_type of POLYGON

I am using a SQL Server stored procedure to perform the following test:

geometry::STGeomFromText(@poly, @srid).STIsValid();

When tested the using the following it correctly returns true:

.Parameters.Append .CreateParameter("@poly ", 200, 1, 50, "POLYGON((1 1, 3 3, 3 1, 1 1))")
.Parameters.Append .CreateParameter("@srid ", 3, 1, 10, 0)

When I run the live data (held in the recordset rs) it always returns false even though I suspect about half the polygons should be true:

.Parameters.Append .CreateParameter("@poly ", 200, 1, 1000000000, rs("ogr_geometry"))
.Parameters.Append .CreateParameter("@srid ", 3, 1, 10, 27700)

Am I doing anything that is obviously wrong or could it be the case that although some of the geometry works in our system none of it is strictly valid?

I have prepared a ZIP file with an example polygon that works and one that does not. Both are being flagged by SQL Server as invalid.

This issue was being caused because the ogr_geometry coming from the recordset had not been cast to a string.

When I changed the field in the recordset to ogr_geometry.ToString() AS sGeometry and used sGeometry instead of ogr_geometry all worked as expected.


Replication chaining with PostgreSQL We have an unusual replication scenario and I am not sure which tool would be appropriate for that. Basically, we have a legacy non-normalized database that we want to refactor into a normalized database. Unfortunately, this has to be done over a long period of time (one or two years), hence the need for replication: legacy applications would use the old format until they are replaced by newer applications.

We would like to have three DBs:

Legacy -> Mirror -> New DB

Everything done on the legacy db would be replicated to a mirror, then using triggers and conversion scripts, we would transform the data into a normalized format. I'm not concerned about the conversion scripts (it's a different problem), but I'm not sure how to do the replication.

The load of the DB is mostly read with a few writes each day that need to be propagated in a matter of minutes (it is not acceptable to wait 15 minutes to see the result of an insertion on the mirror/new db).

We have around 50 tables divided in four databases on one server that we want to replicate.

The conversion scripts cannot be executed on the legacy db because we want to keep the replication cost to a minimum on the legacy db server.

I looked at the built-in replication in postgresql 9.1 but from my understanding, I cannot add a trigger on the slave that is not present on the master (both dbs must be identical copies).

Is there any way that I could use the built-in replication for our scenario? Otherwise, which replication strategy would be appropriate?

You are right, PostgreSQL's built-in replication (aka Hot Standby) replicates whole cluster - so it's not suitable in your case.

You will need some trigger-based solution. For example,

Slony-I - most mature, written in C, flexible, good for complex setups
SkyTools pgq+londiste - C+Python, more lighweight than Slony-I
RubyRep - Ruby/JRuby, simple, easy to setup, not so mature

Please note that triggers always cause some extra load, and also initial sync will be equivalent of dumping all replicated tables.

Hope this helps, I'd propose to come back with more detailed questions if you have problems.

Good luck!


Why does this partitioning helper view give an 'Input string was not in a correct format' error? A database I'm investigating has defined in it a view admin.View_PartitionRanges, which is heavily based on the view PartitionRanges to be found in this MSDN blogpost from 2009. The view definition there is follows:

CREATE VIEW [dbo].[PartitionRanges] AS
select pf.name as [partition_function],
       ps.name as [partition_scheme],
       1 as [partition_number],
       case when prv.value is null then NULL else '<' end as [relation],
       prv.value as [boundary_value],
       type_name(pp.system_type_id) as [type],
       fg.name as [filegroup],
       case when ps.name is null then NULL else N'IN USE' end as [status]
  from sys.partition_functions pf
  join sys.partition_parameters pp on pp.function_id = pf.function_id
  left join sys.partition_schemes ps on ps.function_id = pf.function_id
  left join sys.destination_data_spaces dds
    on dds.partition_scheme_id = ps.data_space_id and dds.destination_id = 1
  left join sys.filegroups fg on fg.data_space_id = dds.data_space_id
  left join sys.partition_range_values prv
    on prv.function_id = pf.function_id and prv.parameter_id = 1 and
       prv.boundary_id = 1
 where pf.boundary_value_on_right = 1
 union all
select pf.name as [partition_function],
       ps.name as [partition_scheme],
       prv.boundary_id + cast(pf.boundary_value_on_right as int) as [partition_number],
       case when pf.boundary_value_on_right = 0 then '<=' else '>=' end as [relation],
       prv.value as [boundary_value],
       type_name(pp.system_type_id) as [type],
       fg.name as [filegroup],
       case when ps.name is null then NULL else N'IN USE' end as [status]
  from sys.partition_functions pf
  join sys.partition_range_values prv on
       prv.function_id = pf.function_id and prv.parameter_id = 1
  join sys.partition_parameters pp on pp.function_id = pf.function_id
  left join sys.partition_schemes ps on ps.function_id = pf.function_id
  left join sys.destination_data_spaces dds
    on dds.partition_scheme_id = ps.data_space_id and
       dds.destination_id = prv.boundary_id + cast(pf.boundary_value_on_right as int)
  left join sys.filegroups fg on fg.data_space_id = dds.data_space_id
 union all
select pf.name as [partition_function],
       ps.name as [partition_scheme],
       pf.fanout as [partition_number],
       case when prv.value is null then NULL else '>' end as [relation],
       prv.value as [boundary_value],
       type_name(pp.system_type_id) as [type],
       fg.name as [filegroup],
       case when ps.name is null then NULL else N'IN USE' end as [status]
  from sys.partition_functions pf
  join sys.partition_parameters pp on pp.function_id = pf.function_id
  left join sys.partition_schemes ps on ps.function_id = pf.function_id
  left join sys.destination_data_spaces dds
    on dds.partition_scheme_id = ps.data_space_id and dds.destination_id = pf.fanout
  left join sys.filegroups fg on fg.data_space_id = dds.data_space_id
  left join sys.partition_range_values prv
    on prv.function_id = pf.function_id and prv.parameter_id = 1 and
       prv.boundary_id = pf.fanout - 1
 where pf.boundary_value_on_right = 0
 union all
select pf.name as [partition_function],
       ps.name as [partition_scheme],
       NULL, NULL, NULL, NULL,
       fg.name as [filegroup],
       case when dds.destination_id = pf.fanout + 1
            then N'NEXT USED' else N'NOT USED'
       end as [status]
  from sys.partition_functions pf
  join sys.partition_schemes ps on ps.function_id = pf.function_id
  join sys.destination_data_spaces dds
    on dds.partition_scheme_id = ps.data_space_id and
       dds.destination_id > pf.fanout
  join sys.filegroups fg on fg.data_space_id = dds.data_space_id

However, when I perform a SELECT * on this view in SSMS, I get the error

An error occurred while executing batch. Error message is:
Input string was not in a correct format.

What's wrong?

The problem lies down in one of the sources used by that view, the system view sys.partition_range_values. The Value column of this view is of type SQL_VARIANT, which can lead to problems with indiscriminate SELECT *s, because SQL has to pick a specific type for the resultset, which all the values then need to be coerced to (Thanks to this blog post for this insight).

You can see for yourself if this is what's causing your problem, by doing

SELECT
    function_id
    , boundary_id
    , parameter_id
    , SQL_VARIANT_PROPERTY(Value, 'BaseType') AS DataType
FROM
    sys.partition_range_values
;

If you have multiple different DataTypes, and you can imagine there might be problems casting values between them, then that's the problem.

To fix, you can update your helper view so that instead of

prv.value as [boundary_value]

instead you have

CASE SQL_VARIANT_PROPERTY(prv.Value,'BaseType')
WHEN 'datetimeoffset' THEN CAST(CAST(prv.Value AS datetimeoffset) AS varchar(30))
WHEN 'smallint' THEN CAST(CAST(prv.Value AS smallint) AS varchar(30))
-- etc as necessary for the types used to partition on in your database
ELSE SQL_VARIANT_PROPERTY(prv.Value,'BaseType')
ELSE 'Value of type ' + CAST(SQL_VARIANT_PROPERTY(prv.Value,'BaseType') AS varchar(15))
END
    AS boundary_Value

This will give you appropriate textual representations of the partition range values.


Why performance degradation with query hint FIRST_ROWS(1000)? I have a query with user supplied arguments that can produce a very high amount of rows depending on the user's input.

In case the user supplies very unselective arguments, the query takes a lot of time to complete. In case the query would return more than 1000 rows, the user should be supplied with an error message.

The query does not have an order by clause, but just joins three (large) tables.

The idea now was to use the query hint FIRST_ROWS(1000) to speed up. Tests in SQL developer showed the cost metric is considerably reduced (factor 10).

The query is executed through the Entitymanager using a native query.

However, the behavior within the application is as follows: duration of OraclePreparedStatement.executeQuery is reduced by 60%. However, now a lot more of time is spent in EJBQueryImpl.getResultList which before took less than 100ms creating a high performance degradation compared with leaving out the query hint.

Code is essentially:

Query query = em.createNativeQuery(sqlQuery); // sqlQuery has query hint "FIRST_ROWS(" + (maxResults + 1) + ")"

query.setMaxResults(maxResults + 1);

List<Object[]> resultList = query.getResultList();

if (maxResults != 0 && resultList.size() > maxResults) {
    resultSetTruncated = true;
}

Query is:

SELECT ... FROM
    T1
LEFT OUTER JOIN T2 on T1.pk1 = T2.pk1
        AND T1.pk2 = T2.pk2
        AND T1.colx = T2.pk3
LEFT OUTER JOIN
    T3
        ON T2.pk1 = T3.pk1
        AND T2.coly = T3.pk1
WHERE
    T1.pk1 IN ('081111')
    AND EXISTS (
        SELECT 1 FROM T4 WHERE
            T1.PK1 = T4.PK1
            AND T2.PL2 = T4.PK2
            AND T4.KIND = 'N'
            AND T4.NORMALIZED LIKE 'DA%'
    )

Table T3 is very large and the specified conditions in the case of interest are not very selective.

Query optimization isn't an exact science. Oracle cannot always find the best execution plan and it certainly can't do any magic. So here are some points that might or might not help:

With the FIRST_ROWS hint, Oracle favors execution plans with low cost for the first few rows over execution plans with minimal cost for retrieving the whole result set. It can also mean that retrieving the whole set takes longer with the hint than without. So what you are observing might be the intended behavior.
I don't how whether maxResults is a constant. If it's not, then you create a lot of queries that are different from Oracle's view even though you just slightly vary the hint. As a consequence, Oracle will spend a lot of time parsing the query and finding an execution plan. The cached execution plans won't help because they are for a slightly differnt query. So you better use a fixed value for your hint, like FIRST_ROWS(200). That should be good enough.
Oracle execution plan is derived from cost estimations. The cost estimations are based on statistical data about your tables. Please check whether your table statistics are up-to-date. Otherwise Oracle is likely to construct bad execution plans.

Please provide more information about your query so that we can give you more specific help. What WHEREconditions does it contain? Does it contain a clause like WHERE ROWNUM <= 1000? How many numbers of rows do you normally expect?


PSQL cte delete seem have have ambiguity when select same column from delete table Both these two sql can be execute , the first one delete all , the second one is logic right.
the first one
with d0 as (
    delete from seller_create_request
        where seller_id in
              (
                  select id
                  from seller
                  where email in
                        ('xx@xx.com')
              )
        returning seller_create_request.seller_id
)
delete from seller
where id in (select id from d0)
returning  seller.id;

the second one
with d0 as (
    delete from seller_create_request
        where seller_id in
              (
                  select id
                  from seller
                  where email in
                        ('xx@xx.com')
              )
        returning seller_create_request.seller_id
)
delete from seller
where id in (select d0.seller_id from d0)
returning  seller.id
;

The first one select id from d0 it seem equal to select id from seller.
My original intention is select seller_id from d0 but have spell wrong. It don't cause
column id does not exist since it from d0.
So I am strange why psql has this behavior, what I am missing from simple understanding?

Your second query will fail, because d0 only has a column seller_id.
Your first query deletes all rows because the id in select id from d0 cannot refer to a column from d0, so it is taken as a column from the outer query, and the whole thing effectively becomes
WITH d0 AS (...)
DELETE FROM seller
WHERE seller.id IN (SELECT seller.id FROM d0)

and that condition will be true whenever d0 is not empty.


Returning values for Stored Procedures in PostgreSQL I was reading this on PostgreSQL Tutorials:

In case you want to return a value from a stored procedure, you can use output parameters. The final values of the output parameters will be returned to the caller.

And then I found a difference between function and stored procedure at DZone:

Stored procedures do not return a value, but stored functions return a single value

Can anyone please help me resolve this.
If we can return anything from stored procedures, please also let me know how to do that from a SELECT statement inside the body.
If I am wrong somewhere please inform.

Procedure are a new thing in Postgresql as of version 11,   there are two big differences between Procedures and Functions

One Procedures can issue a  commit or rollback and keep processing,  Functions can not issue a commit or rollback.

Functions create an implicit transaction any exception that occurs will cause a rollback, unless there is  an Exception Block

Procedure can return a value via INOUT argument   see create functions how that works

create procedure increase(inout i integer )
LANGUAGE 'plpgsql'
AS $$
begin
    i = i+1;
    return;
end ;
$$;

call increase(1);

2nd biggest difference is

select increase () -- fails with an error
-- must use CALL to use a procedure
call increase (3) ;

Unless there is a need to commit or rollback use Functions, there are more features for calling and return values...


How to modify maintenance plan using T-SQL script in SQL Server 2008 How can I modify a maintenance plan in SQL Server 2008 using a T-SQL script?

I want to change the schedule of a maintenance plan and change the path of the .bak file .

All these changes should be done using a T-SQL script.

There are 2 parts to this question, I'll treat them one by one.

Modifying the schedule

Modifying the schedule isn't really changing the maintenance plan as much as changing the SQL Agent schedule.

Have a look at sp_update_schedule for this need.

Modifying the Maintenance plan itself

This will be hard using pure T-SQL as the maintenance plan is stored as an SSIS package. You will have to resort to CLR integration for this.

If you want to do that you would have to export the package from the SSIS package store. (see here) and then modify them through c#.

See Building Packages Programmatically and Modifying an SSIS Package through code


M:M Table Design - Use of PK I have a many-to-many table that contains id's from 2 related tables.

For creating a primary key on this table, is it better to create another integer column and have that as a surrogate PK?

Or should I create the PK on both id columns?

What are the benefits of either method?

Here is the table design with the surrogate key (CodeGroupMappingID)

CREATE TABLE [dbo].[CodeGroupMappings](
    [CodeGroupMappingID] [int] IDENTITY(1,1) NOT NULL,
    [CodeID] [int] NOT NULL,
    [GroupID] [int] NOT NULL,
     CONSTRAINT [PK_CodeGroupMappings_CodeGroupMappingID] PRIMARY KEY CLUSTERED ([CodeGroupMappingID] ASC)
)

Here's the canonical design for a Many-to-Many linking table.  You generally want an index to support traversal in each direction, and (at least for SQL Server) it doesn't matter whether you use a unique constraint, unique index or non-unique non-clustered index for the reverse key index: you end up with the same data structure.

CREATE TABLE CodeGroupMappings
(
    CodeID int NOT NULL,
    GroupID int NOT NULL,
    CONSTRAINT PK_CodeGroupMappings PRIMARY KEY CLUSTERED (CodeID, GroupID),
    CONSTRAINT AK_CodeGroupMappings UNIQUE (GroupID, CodeID),
    CONSTRAINT FK_CodeGroupMappings_Code FOREIGN KEY (CodeID) REFERENCES Code(CodeID),
    CONSTRAINT FK_CodeGroupMappings_Group FOREIGN KEY (GroupID) REFERENCES [Group](GroupID),
)


Query Spatial Data for Nearest Neighbor for each group I want to query spatial data for nearest neighbor. I am using this article and the following query works perfectly:

SELECT TOP 1 [Location].STDistance(@Location)
FROM [DS1]
WHERE [Location].STDistance(@Location) IS NOT NULL
ORDER BY [Location].STDistance(@Location);

The issue is I need to calculate this distance for each MSPID. I have try several things (using cross apply, creating separate function, etc) but nothing worked. So, I decided to create a loop which will calculate the distance for each MSPID.

SELECT TOP 1 [Location].STDistance(@Location)
FROM [DS1]
WHERE [Location].STDistance(@Location) IS NOT NULL
    AND @CurrentMSPID = [MSPID]
ORDER BY [Location].STDistance(@Location);

The problem is that the above statement does not use the index. If I replace the @CurrentMSPID with a number the index is used, but when a variable is used, the clustered index is used instead the spatial one.

I have try a lot of options and the only one that works is:

OPTION (OPTIMIZE FOR ( @CurrentMSPID  = 1001 ))

or when the value is hard-coded. I cannot leave it that way because in the future this id could even not exists and it is sure that the number of rows which it matches will be changed.

The [DS1] table has primary key on [MSPID] column and spatial index on the [Location] column.

How can I help the engine to generate better execution plan and use the spatial index without using the OPTIMIZE FOR option?

It seems when other data is extracted from the table (we are filtering by MSPID now) the engine is not able to use the spatial index and performs clustered index seek instead.

I'm going to go out on a bit of a limb here and guess what you are trying to achieve.

I suspect that you want to find the nearest neighbour from within the DS1 table for all the rows in DS1.

For a test set I created the following randomly filled table.

-- Test table
CREATE TABLE DS1 (
  MSPID INTEGER IDENTITY(1,1) NOT NULL PRIMARY KEY,
  Location Geometry NOT NULL
);

-- 1 million random points
INSERT INTO DS1 (Location)
SELECT TOP 1000000
  Geometry::Point(
    RAND(CAST(NEWID() AS VARBINARY))*100000,
    RAND(CAST(NEWID() AS VARBINARY))*100000,
    0) Location
FROM TALLY;

CREATE SPATIAL INDEX DS1_SIDX ON DS1 (Location)
USING GEOMETRY_AUTO_GRID
WITH (BOUNDING_BOX =(-15000, -15000, 2015000, 2015000));

I a version of your initial query for a quick test, but unfortunately on my desktop the optimiser decide that parallel was better and ignored the spatial index resulting in a 3 second query.  Restricting it to a single core caused it to use the index and returned in milliseconds.

-- Nearest neighbour test
DECLARE @Location Geometry = Geometry::Point(50000,50000,0);

/* Ignored index
 SQL Server Execution Times:
   CPU time = 21781 ms,  elapsed time = 3805 ms.
*/
SELECT TOP 1
  MSPID,
  Location.STDistance(@location) Distance
FROM DS1
WHERE Location.STDistance(@location) IS NOT NULL
ORDER BY Location.STDistance(@location)

/* Used index
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 10 ms.
*/
SELECT TOP 1
  MSPID,
  Location.STDistance(@location) Distance
FROM DS1
WHERE Location.STDistance(@location) IS NOT NULL
ORDER BY Location.STDistance(@location)
OPTION (MAXDOP 1);

To achieve what you want for a single row from DS1 and assuming that you want the nearest row from DS1 to the one you've picked, the following uses the index for me and again works in milliseconds.

-- For a specific row using cross apply
DECLARE @MSPID INTEGER = 500000;

SELECT
  a.MSPID FromMSPID,
  b.MSPID ToMSPID,
  b.Distance
FROM DS1 a
  CROSS APPLY (
    SELECT TOP 1
      MSPID,
      c.Location.STDistance(a.Location) Distance
    FROM DS1 c
    WHERE c.Location.STDistance(a.Location) IS NOT NULL AND
      c.MSPID <> a.MSPID
    ORDER BY c.Location.STDistance(a.Location)
    ) b
WHERE a.MSPID = @MSPID
OPTION (MAXDOP 1);

This can be made to go through the entire table, but it will take some time to complete as it needs to find the nearest neighbour for each one.  It took ~1 minute for 10,000, so for the full million that would take almost 2 hours.  I have found that restricting this query to a single core isn't required and allowing parallism will improve performance.  For me it halved the time.  I would also recommend that you have a look at this question and it's answer for other performance considerations.

/* For all (well 10,000) using cross apply
 SQL Server Execution Times:
   CPU time = 60641 ms,  elapsed time = 64545 ms.
*/
SELECT TOP 10000
  a.MSPID FromMSPID,
  b.MSPID ToMSPID,
  b.Distance
FROM DS1 a
  CROSS APPLY (
    SELECT TOP 1
      MSPID,
      c.Location.STDistance(a.Location) Distance
    FROM DS1 c
    WHERE c.Location.STDistance(a.Location) IS NOT NULL AND
      c.MSPID <> a.MSPID
    ORDER BY c.Location.STDistance(a.Location)
    ) b
OPTION (MAXDOP 1);


Why am I getting LOG_BACKUP as log reuse wait reason? I have a few databases all in full recovery mode and with mirroring activated and I do transaction backups every 60 minutes.

When I query sys.databases a few of my databases has LOG_REUSE_WAIT = Nothing but some has LOG_BACKUP and it never seems to change.

There is nothing to be alarmed here. Even if you are taking periodic transaction log backup you will see this Log_Backup under log_reuse_wait_desc because last transaction log was not able to clear any VLF (in other word - VLF was not marked for reuse) due to low activity and not many transactions are being logged.

Here is the precise explanation
http://www.sqlskills.com/blogs/paul/why-is-log_reuse_wait_desc-saying-log_backup-after-doing-a-log-backup/


How to format a phone number in a SELECT statement? I have a database with table name student.

I would like to display the register_number and phone_number of the student.

The phone_number should be in the following format:

+91-123-456-7890

and if the phone_number is NULL, then it should display N/A.

The table looks like:

R_NO      |     STUDENT_NAME    |  PHONE_NUMBER
-------------------------------------------------------
  1       |      Rajesh         |  9632545123
  2       |      Sridevi        |  9512647359
  3       |      Shiva          |  9632155862
  4       |      HariHaran      |  8426911231
  5       |      Ravi           |  9111558899
  6       |      Pauline        |  NULL

Different areas have differing number of digits in their prefix (wikipedia's list):

COALESCE(CONCAT('+',
  CASE
    WHEN LEFT(phone_number, 1) IN ('1', '7')
      THEN INSERT(INSERT(INSERT(phone_number, 2, 0, '-'), 6, 0, '-'), 10, 0, '-')
    WHEN CONVERT(LEFT(phone_number, 2), SIGNED) IN (21, 22, 23, 24, 25, 26, 29, 35, 37, 38, 42, 50, 59,
                                                    67, 68, 69, 80, 85, 87, 88, 96, 97, 99)
      THEN INSERT(INSERT(INSERT(phone_number, 4, 0, '-'), 8, 0, '-'), 12, 0, '-')
    ELSE   INSERT(INSERT(INSERT(phone_number, 3, 0, '-'), 7, 0, '-'), 11, 0, '-') END), 'N/A')

With the query above, all phone numbers will show as +Country_code-XXX-XXX-XXXX although each area has specific formatting rules, which vary greatly, so that formatting will look funny for certain regions, such as Australia where formatting is +54-X-XXXX-XXXX.


Sql availability query error I have a query to fetch the available rooms from 2 tables, roomtypes and reservations, as follows:

Roomtypes has the following columns

id,
product_id,
type,
total_no_of_rooms,
created_at,
updated_at

Reservation has the following columns

id,
roomtype_id,
customer_id,
check_in,
check_out,
no_of_rooms,
created_at,
updated_at,

This is my sql query:

SELECT rt.id, rt.type, rt.total_no_of_rooms,
    SUM(r.no_of_rooms) as "reserved rooms",
    rt.total_no_of_rooms - SUM(r.no_of_rooms) as "rooms available"
FROM Roomtypes rt
    LEFT OUTER JOIN Reservations r ON r.roomtype_id = rt.id
        AND '2018-04-11 10:00:00' >= r.check_in
        AND '2018-04-12 09:59:59' <= r.check_out
GROUP BY rt.id, rt.type, rt.total_no_of_rooms

When there is a reserved room in the given date, the column shows values to rooms available and rooms reserved. But if no rooms are reserved, the column shows null as values where as I need to get the rooms available as number of rooms available and reserved rooms as 0. How can I get this?

Try using the following query (Considering you are using mysql):

SELECT rt.id, rt.type,
    rt.total_no_of_rooms,
    if(SUM(r.no_of_rooms) IS NULL,0,SUM(r.no_of_rooms)) as "reserved rooms",
    (rt.total_no_of_rooms - if(SUM(r.no_of_rooms) IS NULL,0,SUM(r.no_of_rooms))) as "rooms available"
FROM Roomtypes rt LEFT OUTER JOIN Reservations r
    ON r.roomtype_id = rt.id
    AND '2018-04-11 10:00:00' >= r.check_in
    AND '2018-04-12 09:59:59' <= r.check_out
GROUP BY rt.id, rt.type, rt.total_no_of_rooms

I have just added condition to check NULL value. If you are using PostgreSQL, use COALESCE function.


mysql cluster table full error I installed a new mysql cluster on windows (for evaluation). I created a table using command below. After I insert just over 1 million records into the table it gives me the "table datadump is full" exception. I have plenty of hard disk space and ram free, and 1 million records is well below the max number of rows for the table. Also the MySql RAm usage has not increased during inserts. I am assuming that MySql has an internal cache limit for the table that was hit. is there a config.ini change I can make to disable caching of rows in cluster table or a modification to the create table script to do the same?

create table datadump (name varchar(30) not null) engine=ndb;

Here is the MySQL Documentation FAQ on MySQL Cluster that addresses your question

The cause is very likely to be that your setup does not provide sufficient RAM for all table data and all indexes, including the primary key required by the NDB storage engine and automatically created in the event that the table definition does not include the definition of a primary key.
It is also worth noting that all data nodes should have the same amount of RAM, since no data node in a cluster can use more memory than the least amount available to any individual data node. For example, if there are four computers hosting Cluster data nodes, and three of these have 3GB of RAM available to store Cluster data while the remaining data node has only 1GB RAM, then each data node can devote at most 1GB to MySQL Cluster data and indexes.
In some cases it is possible to get Table is full errors in MySQL client applications even when ndb_mgm -e "ALL REPORT MEMORYUSAGE" shows significant free DataMemory. You can force NDB to create extra partitions for MySQL Cluster tables and thus have more memory available for hash indexes by using the MAX_ROWS option for CREATE TABLE. In general, setting MAX_ROWS to twice the number of rows that you expect to store in the table should be sufficient.
For similar reasons, you can also sometimes encounter problems with data node restarts on nodes that are heavily loaded with data. In MySQL Cluster NDB 7.1 and later, the addition of the MinFreePct parameter helps with this issue by reserving a portion (5% by default) of DataMemory and IndexMemory for use in restarts. This reserved memory is not available for storing NDB tables or data.

Based on the 3rd paragraph, you should run the create table with max_rows:
create table datadump (name varchar(30) not null) engine=ndb max_rows=2000000;


I have noticed the same thing in last week. Sometimes, the mongo shell returns any empty array:

Try running the currentOp() many times to get the progress.
Use use db command and then perform db.currentOp()


PostgresSQL dynamic execute with argument values in array I'm wondering if this is possible in Postgres:

Best explained using a contrived example:

create or replace function test_function(filter_param1 varchar default null
                                       , filter_param2 varchar default null)
  returns integer as
$$
declare
  stmt text;
  args varchar[];
  wher varchar[];
  retid integer;
begin
  if filter_param1 is not null then
    array_append(args, filter_param1);
    array_append(wher, 'parameter_name = $1');
  end if;
  if filter_param2 is not null then
    array_append(args, filter_param2);
    array_append(wher, 'parameter_name = $2');
  end if;

  stmt := 'select id from mytable where ' || array_to_string(wher, ' or ');
  execute stmt into retid using args;

  return retid;
end;
$$ language plpgsql;

In Python there is *args - perhaps PostgreSQL has a similar mechanism?

EDIT for Erwin Brandstetter questions:

All filter parameters will be applied to different columns, but should be AND'ed.
Returning setof makes much more sense here.
All parameters can be of the same column type (ie. varchar).

Either way, that's totally possible, given that all your parameters are of the same data type.
EXECUTE ... USING happily takes an array, which is treated as a single argument. Access elements with array subscripts.
create or replace function test_function(_filter1 text = null
                                       , _filter2 text = null
                                       , OUT retid int) as
$func$
declare
   _args text[] := ARRAY[_filter1, _filter2];
   _wher text[];
begin
   if _filter1 is not null then
      _wher := _wher || 'parameter_name = $1[1]'; -- note array subscript
   end if;

   if _filter2 is not null then
      _wher := _wher || 'parameter_name = $1[2]'; -- assign the result!
   end if;

   IF _args  IS NULL         -- check whether all params are NULL
      RAISE EXCEPTION 'At least one parameter required!';
   END IF;

   execute 'select id from mytable where ' -- cover case with all params NULL
         || array_to_string(_wher, ' or ')
         || ' ORDER BY id LIMIT 1';   -- For a single value (???)
   into  retid
   using _args;
end
$func$  language plpgsql;
This is just a proof of concept and needlessly complicated. It would be an interesting option for actual array input, for instance with a [VARIADIC function][2]. Example:

How to do WHERE x IN (val1, val2,...) in plpgsql

For the case at hand, use instead:
CREATE OR REPLACE FUNCTION test_function(_filter1 text = null
                                       , _filter2 text = null)
  RETURNS SETOF int AS
$func$
DECLARE
   _wher text := concat_ws(' OR '
             , CASE WHEN _filter1 IS NOT NULL THEN 'parameter_name = $1' END
             , CASE WHEN _filter2 IS NOT NULL THEN 'parameter_name = $2' END);
BEGIN
   IF _wher = ''   -- check whether all params are NULL
      RAISE EXCEPTION 'At least one parameter required!';
   END IF;

   RETURN QUERY EXECUTE 'SELECT id FROM mytable WHERE ' || _wher
   USING  $1, $2;
   -- USING  _filter1 , filter2; -- alternatively use func param names
END
$func$  LANGUAGE plpgsql;
Explain

List all values that can possibly be referenced in the dynamic query in the USING clause in their order of appearance. If not all of them will be referenced in the dynamic query, there's no harm in that. But we need to keep ordinal positions intact.

Note in particular that $n inside the dynamic query references given values of the USING clause by ordinal number, while $n in the USING clause references function parameters. Same syntax, different scope!
In my example $2 references $2 for simplicity. But one could reorder values in the USING clause in any fashion, so that (for instance) $2 in the dynamic query references $1 at the 2nd position in the USING clause, which references the 1st function parameter.

This allows for any number of parameters with any (heterogeneous) data types.

Returning a set of integer in this example(RETURNS SETOF int), which better fits the example - using RETURN QUERY EXECUTE accordingly.

concat_ws() is particularly handy to assemble a list of OR'ed or AND'ed predicates conditionally.


How do I know that I am connected to a config server I need a simple check for identifying that I am connected to a MongoDB config server.

I can check the process to differentiate between mongod and mongos:

> db.serverStatus().process;
mongod

Now I make an assumption that config server is the one without a replica set name, but it would not always hold true. I check that below returns a NULL value.

> db.serverStatus().repl.setName;

What is a better way to identify config server from mongod in a simple manner? This would be useful for custom prompt function based on the connection.

I found one way to do it.

On config server:

> db.serverCmdLineOpts().parsed.sharding.clusterRole
configsvr

On shard server:

> db.serverCmdLineOpts().parsed.sharding.clusterRole
shardsvr

Have to be careful as db.serverCmdLineOpts().parsed.sharding.clusterRole is not always defined.


DBeaver: Access denied for user 'root'@'localhost' (using password: YES) Context: I am creating a new Schema and looking to import .sql file generated from mysql dump. I have server running: MySQL 8.0.19.

I am using DBeaver client Version 7.0.2 and have setup a new MySQL 8+ Database connection. However, when I attempt to connect to the DB, I run into the following error:

Access denied for user 'root'@'localhost' (using password: YES)

A root user exist (verified through mysql shell). Here are a few things I have attempted, however, haven't been able to resolve the issue yet:

Create new user with password and full privileges as described here:
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'the_password_you_wish_here';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

...and login with this user for further usages.


Should this be a foreign key? I'm working on a school project and I need some help.

I want to create three tables which should contain these fields:

User
UserID (AI, PK),
UserEmail,
UserPassword

WorkGroupMember
GroupID(PK, FK WorkGroup)
UserID (PK, FK User)

WorkGroup
GroupID (AI, PK),
GroupFacilitator,
GroupName

The question is, GroupFacilitator in WorkGroup is actually UserID, can or should it be a foreign key?

Thank You

First you should clarify the relationships between your entities by asking these questions:

Can a facilitator of a group be a user that is not a member of that group?
Or a facilitator must always be a member of that group?
Do groups always have a facilitator? (your design indicates: yes)
Does every group have one facilitator only? (your design indicates: yes)
Or some groups may have more than one?

If the answer to the first question is "Yes", then the foreign key constraint should be referencing User (and you have no circular path, only multiple paths in your design.)

If the answer is "No", then the foreign key constraint should be referencing WorkGroupMember, with a composite foreign key (and then you have a circular path):

FOREIGN KEY (GroupID, GroupFacilitatorID)
  REFERENCES WorkGroupMember (GroupID, UserID)

The circular design makes it difficult to be implemented in many DBMS (because the constaints need to be DEFERRABLE and that is not implemented in many of them.)

Another solution would be (as proposed by Steve Pettifer in his answer), to add a IsGroupFacilitator column in the WorkGroupMember and a partial (filtered) unique constraint, to enforce that each group have at most one facilitator:

UNIQUE (GroupID)
  WHERE (IsGroupFacilitator = 1)

This way however, you cannot enforce that all groups have a facilitator (not with constraints at least, you can still enforce it by taking care of it in the INSERT/UPDATE/DELETE statements)


Why is count(*) slow, when explain knows the answer? This query: select count(*) from planner_event takes a very long time to run - so long, I gave up and killed it before it finished. However, when I run explain select count(*) from planner_event, I can see a column in the output with the number of rows (14m).

How come explain can get the number of rows instantly, but count(*) takes a long time to run?

Explain is using previously gathered statistics (used by the query optimizer). Doing a select count(*) reads EVERY data block.
Here's a cheap way to get an estimated row count:
SELECT table_rows
FROM information_schema.tables
WHERE table_name='planner_event';

Even if you did select count(id), it might still take a very long time, unless you have a secondary index on id (also assuming id is a PRIMARY KEY). Because all data (including Row Data) is stored in B-Tree indexes, performing a select count(PK_COLUMN) is still a considerable amount of IO (needs to reads all data pages).  If you have a secondary index on the PK field, it will be able to perform less IO to perform a count.


Repopulating PostgreSQL database with data directory from another database Instance I have had to delete PostgreSQL for an issue and reinstall it, however before uninstalling I did not execute the command pg_dump. I still have the data directory from this instance. After reinstalling PostgreSQL, I receive all the same tables and schemas from my prior database, but not the same data. The data directories are two separate, but I want to combine the previous data directory into the new one. How would I go about such a process, if possible?

Assumptions

I'm assuming you have two instances each one with a different data directory but with the same schema for the same version of postgres installed.
I'm also assuming when the server starts is using your newly created, possibly empty, instance

What to do

You should try to start a new instance of the server pointing to your backup data directory...
Somenthing like

pg_ctl -D /path/to/backup/dir -l /path/to/log start -o "-p 5433"

If this works, your backup instance is running on port 5433.
Check if everything is in place and the data is consistent.

If so then you could simply make arrangements for your server to always start pointing to your backup directory and safely delete your newly created data directory.


Does setting column property to NOT NULL affect table size? I have a bunch of columns in my MySQL database table that store booleans (0 or 1) as tinyints. They are 0 by default and are not meant to be null. I am thinking about setting the column property to NOT NULL so that there is another validation check just in case. I wonder though whether it will come at a cost of increased size of the table down the road. My fear is that, perhaps, NOT NULL property adds another bit to every data entry?

My database is new and practically empty so when I try to check the size of the table it always gives me 16kb.

From the MySQL documentation (emphasis mine):

  Declare columns to be NOT NULL if possible. It makes SQL operations faster, by enabling better use of indexes and eliminating overhead for testing whether each value is NULL. You also save some storage space, one bit per column. If you really need NULL values in your tables, use them. Just avoid the default setting that allows NULL values in every column.

So, on the contrary, NOT NULL saves disk space, rather than costing more.


Are there any outstanding critical security issues for Sql Server 2000? Are there any outstanding (unpatched) security issues for Sql Server 2000?

This isn't really for me: I moved on from Sql Server 2000 long ago. However, when answering questions here and (more likely) on Stack Overflow, I'm in the habit of leaving a comment like the one at this question whenever coming across a Sql Server 2000 question. If you don't want to follow the link, it goes something like this:

  Sql Server 2000 is end of life. It no longer gets any patches... not even critical security patches. It's irresponsible to continue using it. Convincing management to upgrade is job #1.

Aide from it being, well, Sql Server 2000, how much an issue is this really? In the year-plus since Sql Server 2000 went EOL, has anything big come up I could point to as a more concrete issue?

I'd look at official documentation, but as this is effectively abandonware I haven't found anything published directly by MS.

Are there any outstanding critical security issues for Sql Server 2000?

Yes, there are no patches available for newly discovered issues like meltdown/spectre

Microsoft only releases security patches for SQL versions that are still supported. Also notice that if you are running a supported SQL version with an unsupported service pack, you MUST upgrade to a supported service pack to have an available security patch.

4057122 Description of the security update for SQL Server 2017 GDR: January 3, 2018
4058562 Description of the security update for SQL Server 2017 RTM CU3: January 3, 2018
4058561 Description of the security update for SQL Server 2016 SP1 CU7: January 3, 2018
4057118 Description of the security update for SQL Server 2016 GDR SP1: January 3, 2018
4058559 Description of the security update for SQL Server 2016 CU: January 6, 2018
4058560 Description of the security update for SQL Server 2016 GDR: January 6, 2018
4057117 Description of the security update for SQL Server 2014 SP2 CU10: January 16, 2018
4057120 Description of the security update for SQL Server 2014 SP2 GDR: January 16, 2018
4057116 Description of the security update for SQL Server 2012 SP4 GDR: January 12, 2018
4057115 Description of the security update for SQL Server 2012 SP3 GDR: January, 2018
4057121 Description of the security update for SQL Server 2012 SP3 CU: January, 2018
4057114 Description of the security update for SQL Server 2008 SP4 GDR: January 6, 2018
4057113 Description of the security update for SQL Server 2008 R2 SP3 GDR: January 6, 2018

Protect SQL Server from attacks on Spectre and Meltdown side-channel vulnerabilities


Conditionally limit MySQL SELECT query My goal is to select rows from a mysql table, but according to certain column. Normal SELECT would be:

        SELECT
        ti.id, ti.userid, ti.date, ti.duedate, ti.datepaid, ti.subtotal, ti.taxrate, ti.paymentmethod, ti.notes AS invoicenotes,
        tii.invoiceid, tii.type, tii.description, tii.amount,
        tc.id, tc.firstname, tc.lastname, tc.companyname, tc.email, tc.address1, tc.address2, tc.city, tc.state,
        tc.postcode, tc.country, tc.phonenumber, tc.currency, tc.defaultgateway, tc.credit, tc.taxexempt,
        tc.latefeeoveride, tc.overideduenotices, tc.separateinvoices, tc.disableautocc, tc.datecreated,
        tc.notes AS tcnotes,
          (SELECT GROUP_CONCAT(value) FROM tblcustomfieldsvalues WHERE relid=tc.id) AS vatid
        FROM tblinvoices ti
        LEFT JOIN tblinvoiceitems tii
        ON tii.invoiceid=ti.id
        LEFT JOIN tblclients tc
        ON tc.id=tii.userid
        WHERE ti.status='Paid'
        AND ti.infakt_no IS NULL
        AND ti.paymentmethod='transferuj'
        ORDER BY ti.userid AND tii.id

But that kind of SELECT would split rows with same userid value. How to SELECT certain amount of rows and keep from separating userid?

What i understand from your question is you need to group your result on the basis of user_id for that you can try this one

SELECT user_id,GROUP_CONCAT(id),GROUP_CONCAT(content) FROM tbl WHERE sth='sth' GROUP BY user_id LIMIT 10;

If you need anything else please comment and update in your question.


Financial database model I am completely new to database modelling (except for some academic knowledge from a long time ago) but for my new project I want to build a database for the following application.
I am collecting different income figures from different companies from different product categories. I calculate the aggregate of these figures and calculate some metrics such as averages and growth of the figures over the years/over product categories and over the sectors of these companies. I wanted to build a database to accomplish this and already made the following draft. It is based on a simple star model but I would like to get your input on what can be better.
Looking forward to your input!

I would like to get your input on what can be better.

There is no way to judge the design of a Dimensional Data Model without an understanding of the business context and the kinds of questions it's intended to answer.
So make a list of the questions you would like to answer from the data model, providing a specific example for each type of question.
Translate each question to a SQL query against your model.
The design is good to the extent the SQL queries are simple for a human to write and understand, and for an RDBMS to execute.


Build dynamic text search on table columns With no success, I am trying to build a dynamic search text on a table with two columns (Id & Number, both columns are unique).

The Number column should be grouped and start a new section of search (with OR). The Id column should be separated with "," (with IN).

Example (Please ignore the namings, I simplified my case and changed the names):

CREATE TABLE #MyTable
(
    Id INT,
    Number INT
);

INSERT #MyTable VALUES (6, 3), (7, 3), (8, 3), (16,15), (20, 15);

Expected result: @Query VARCHAR that will contain this:

'
    SELECT  Id, Number
    FROM    #MyTable
    WHERE   (
                Number = 3
                AND Id IN (6, 7, 8)
            )
            OR
            (
                Number = 15
                AND Id IN (16, 20)
            )
'

Note: The reason I am doing it dynamically because I do a dynamic filters search.

I guess the solution should use FOR XML PATH('') and STUFF() methods and probably also a GROUP BY, but I am not managing to do it (I succeeded to build a dynamic WHERE on one column but facing lots of difficulties in doing it with 2 columns. Actually, I can build it with a loop but I guess it could be done much better and therefore I ask you for a help.

Thanks in advance.

One way to do this using a set-based approach:

IF OBJECT_ID(N'tempdb..#MyTable', N'U') IS NOT NULL
DROP TABLE #MyTable;
CREATE TABLE #MyTable
(
    Id INT,
    Number INT
);

INSERT #MyTable VALUES (6, 3), (7, 3), (8, 3), (16,15), (20, 15);

DECLARE @cmd nvarchar(max);
SET @cmd = '';

SELECT @cmd = @cmd + CASE WHEN @cmd = '' THEN '' ELSE ' OR ' END + N'(
    Number = ' + CONVERT(nvarchar(max), mt.Number) + N'
    AND Id IN (' + STUFF((SELECT ', ' + CONVERT(nvarchar(max), mt1.Id) FROM #MyTable mt1 WHERE mt1.Number = mt.Number FOR XML PATH ('')), 1, 2, '') + N')
    )'
FROM #MyTable mt
GROUP BY mt.Number

SET @cmd = N'SELECT Id, Number
FROM #MyTable
WHERE ' + @cmd + ';
';

PRINT @cmd;

The results:

SELECT Id, Number
    FROM #MyTable
    WHERE (
        Number = 3
        AND Id IN (6, 7, 8)
        ) OR (
        Number = 15
        AND Id IN (16, 20)
        );