Function To Perform Calculations Based Off Variable I saw an answer by @ypercubeᵀᴹ that gave a great answer on how to calculate what appeared to be a quantity ordered by 3 and give whole number answers. Which that got me thinking, how would you handle a situation if there was a variable number of times to split?

Meaning this was the OP where you would always split by 3 (Original Poster

Create Table #Orders
(
    id int IDENTITY(1,1) PRIMARY KEY NOT NULL
    ,partid varchar(100) NOT NULL
    ,qtyordered int DEFAULT '0'
    ,orderedby varchar(100) NOT NULL
    ,ordereddate date DEFAULT GETDATE()
) ;

Insert Into #Orders (partid, qtyordered, orderedby) VALUES
('SS100', 10, 'James'), ('RR200', 5, 'Bob'), ('NN300', 3, 'Jake'), ('OO400', 5, 'Blue') ;

SELECT
   partid,
   qtyordered,
   [First],
   [Second],
   [Third]
FROM
    #Orders
  CROSS APPLY
    ( SELECT [Third]  = (qtyordered)                      / 3 )  AS q3
  CROSS APPLY
    ( SELECT [Second] = (qtyordered - [Third])            / 2 )  AS q2
  CROSS APPLY
    ( SELECT [First]  = (qtyordered - [Third] - [Second]) / 1 )  AS q1;

dbfiddle here

However, what if instead of always splitting by 3, you had an int variable that stated how many times to split, say same DDL but instead of splitting by 3 you use

Declare @TTS int = 5

And now you split each scenario 5 ways instead of 3.  Basically a re-usable function that can "on-the-fly" split based off a variable?

Basically, we're just looking to spread the remainder from the division out over the values. This procedure does exactly that; it will return a table with the result vals from the split. I assume we do not want to return a variable number of columns (depending on the divisors we receive), and I decided that actually generating the text "First", "Second", etc. wasn't really a part of the question, so I'm returning each value in its own row.

CREATE PROCEDURE split_into_ints(@dividend int, @divisor int)
AS
BEGIN
    WITH result_rows AS
         (SELECT TOP (@divisor) ROW_NUMBER() OVER (ORDER BY o1.object_id)
                 as result_num
            FROM sys.objects o1 CROSS JOIN sys.objects o2
         )
        ,base_vals AS
         (SELECT @dividend / @divisor as base_result
                ,@dividend % @divisor as remainder
         )
    SELECT r.result_num
          ,CASE WHEN r.result_num <= remainder THEN 1 ELSE 0 END
           + v.base_result as result_val
      FROM base_vals v CROSS JOIN result_rows r
     ORDER BY result_num
END;
GO

The following:

EXECUTE split_into_ints 10,3;
EXECUTE split_into_ints 49,6;
EXECUTE split_into_ints 2000,11;

Yields these results:

result_num           result_val
-------------------- -----------
1                    4
2                    3
3                    3

result_num           result_val
-------------------- -----------
1                    9
2                    8
3                    8
4                    8
5                    8
6                    8

result_num           result_val
-------------------- -----------
1                    182
2                    182
3                    182
4                    182
5                    182
6                    182
7                    182
8                    182
9                    182
10                   181
11                   181

Alternately, you could make this an inline table-valued function:

CREATE FUNCTION fn_split_into_ints(@dividend int, @divisor int)
RETURNS TABLE
AS
RETURN
(
    WITH result_rows AS
         (SELECT TOP (@divisor) ROW_NUMBER() OVER (ORDER BY o1.object_id)
                 as result_num
            FROM sys.objects o1 CROSS JOIN sys.objects o2
         )
        ,base_vals AS
         (SELECT @dividend / @divisor as base_result
                ,@dividend % @divisor as remainder
         )
    SELECT r.result_num
          ,CASE WHEN r.result_num <= remainder THEN 1 ELSE 0 END
           + v.base_result as result_val
      FROM base_vals v CROSS JOIN result_rows r
);
GO

You run the same queries as above thusly:

SELECT * FROM fn_split_into_ints(10,3);
SELECT * FROM fn_split_into_ints(49,6);
SELECT * FROM fn_split_into_ints(2000,11);

Results are the same. To answer one comment: this would allow you to pull result_val into a variable:

SELECT @result_val = result_val
  FROM fn_split_into_ints(49,6)
 WHERE result_num = 6
;

EDIT: Changed function name, so function and procedure can co-exist (if you'd want them to, for some reason).


Does MYSQL join before LIMIT? Imagine that I want to populate the last 10 questions of stackoverflow. I have a table that keep the questions list, a table to keep the question info, a table to list question's asker.

Now imagine that I want to get the last ten questions, so I have to join some tables and get the last items by ORDER BY ... DESC LIMIT 0 , 10. This gets me the last ten questions without any problems, but I think that there may be one issue.

MySQL may be first joining all tables and then pick the last ten items, so if I have to join 1 million rows to 1 million rows, it gets really bad performance.

I want to know if MySQL does do this, and if yes, how can I go around this issue?.

The only way around it is to refactor the query so as to alter the order of the EXPLAIN plan.
EXAMPLE : On May 16, 2011, I once answered a very rough question on StackOverflow
Fetching a Single Row from Join Table

Question : I had to join tables together and return only the 40 most recent results.
My Answer : I proved that you can do a JOIN on tables with large rows and get results fast.
How I did it : I had applied principles of refactoring queries I learned from a Youtube Video.

Basically, you have to construct the query so that JOIN clauses are done last. Consequently, this implies that you must get the LIMIT executed beforehand.


Need help with ASYNC_NETWORK_IO We have a SQL Server 2005 server that is serving up 20 websites, one of which is our custom CMS, and a few other micro sites. I am new to this and just started looking at the databases here.

The problem is that we are having issues with the SQL Server slowing down after a few days which causes us to restart the service. I did some stats with the SQL Profiler but after review I didn't see anything that stuck out. This is why I am here now. I looked around online and came across ASYNC_NETWORK_IO. So, I am trying to follow this track now.

This is what I've done:

I cleared out the wait stats table.

DBCC SQLPERF ('sys.dm_os_wait_stats', 'CLEAR')

Results for the last 15 minutes:

ASYNC_NETWORK_IO    46798     34953     812     1953
CMEMTHREAD          28300     42328     1187    6437
CXPACKET            361571    7048515   38984   48859

From what I've read ASYNC_NETWORK_IO has to do with SQL waiting for the client application.

2 questions:

1) Is my assumption correct that these numbers seem high for 15 minutes of monitoring?

2) Where do I go from here? What should be my next step?

I'm trying to learn how this stuff works and I understand this is something I'm going to have to keep digging in to.

A push in the right direction would be great.

Reboot the offending client. Simple.

ASYNC_NETWORK_IO causes the server to slow down because the client can't consume data quick enough: the network buffers fill up.

FYI: CXPACKET is expected with parallelism. Not sure of current state but IIRC excess of there can be caused by hyperthreading.

Have you rebooted the client yet...? :-)


script all triggers in a database I have a few databases involved in replication as subscribers and they have triggers and are used by a different set of applications.

Every time I need to reinitialise those database I need to script the triggers, permissions and indexes, apply the snapshot and then re-apply those triggers, indexes and permissions.

Is there a script that I can use to script all the triggers of a current database?

This seems a whole lot simpler than what you have:

CREATE TABLE #tmp
(
  db          sysname,
  sch         sysname,
  obj         sysname,
  name        sysname,
  is_disabled bit,
  def         nvarchar(max)
);
GO

INSERT #tmp SELECT DB_NAME(),
  s.name, o.name, t.name,
  t.is_disabled, m.definition
FROM sys.triggers AS t
INNER JOIN sys.sql_modules AS m
ON t.object_id = m.object_id
INNER JOIN sys.objects AS o
ON t.parent_id = o.object_id
INNER JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
WHERE parent_class = 1;

When you're ready to create them again:

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += def
  + CHAR(13) + CHAR(10) + N'GO'
  + CHAR(13) + CHAR(10)
FROM #tmp;

SELECT @sql += N'DISABLE TRIGGER '
  + QUOTENAME(sch) + N'.' + QUOTENAME(name)
  + N' ON '
  + QUOTENAME(sch) + N'.' + QUOTENAME(obj) + N';'
FROM #tmp WHERE is_disabled = 1;

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

Yes, you won't be able to validate the entire script is there, because SSMS won't output the entire value of @sql. See this post for workarounds.

Also, I don't know why you need the database as part of the dynamic script if you always know the database you're affecting, but there's a much safer way to make the database name dynamic without concatenating values and inviting SQL injection:

DECLARE @db sysname = N'AdventureWorks';

DECLARE @exec nvarchar(max), @sql nvarchar(max);

SET @exec = QUOTENAME(@db) + N'.sys.sp_executesql';

SET @sql = N'SELECT DB_NAME();';

EXEC @exec @sql;


Deny Delete to An Active Directory Account in Sql Server I want to deny an AD account to delete any record in sql server. How can i do that?
DENY DELETE TO [usergorup\username] did not work. Is there another way?

I solved my issue by executing "DENY DELETE TO [UserGroup\UserName]" statement one by one in every databases on the server. It will solve your issue if you have same situation like this.


Convert seconds into HH:MM:SS format in MySQL I have a query where I'm pulling data from the Zultys VoIP Database, about employees and their call in/call out numbers, and the time they spent over the phone: Time in/Time out in seconds, and their phone extensions as well.

The query is below:

SELECT
    IFNULL(mxuser.EXTENSION, mxuser_1.EXTENSION) AS Ext,
    SUM(IF(TRUNKGROUPNAME2 != 'itsp.Nextiva',
        IF(SESSIONIDTRANSFEREDTO IS NULL,
            Duration,
            HoldTimeSecs),
        0)) AS TimeIn,
    SUM(IF(TRUNKGROUPNAME2 = 'itsp.Nextiva',
        IF(SESSIONIDTRANSFEREDTO IS NULL,
            Duration,
            HoldTimeSecs),
        0)) AS TimeOut,
    SUM(IF(TRUNKGROUPNAME2 != 'itsp.Nextiva',
        1,
        0)) AS CallIn,
    SUM(IF(TRUNKGROUPNAME2 = 'itsp.Nextiva',
        1,
        0)) AS CallOut
FROM
    ((session
    INNER JOIN callsummary ON session.NOTABLECALLID = callsummary.NOTABLECALLID
        AND DATEDIFF(CURDATE(), STARTTIME) = 1)
    LEFT JOIN mxuser ON session.EXTENSIONID1 = mxuser.EXTENSIONID)
        LEFT JOIN
    mxuser AS mxuser_1 ON session.EXTENSIONID2 = mxuser_1.EXTENSIONID
WHERE
    SESSIONTYPE = 1 AND ANSWERED = 1
        AND (DIALPLANNAME NOT LIKE 'Local %'
        AND TRUNKGROUPNAME1 LIKE 'dev.%'
        AND TRUNKGROUPNAME2 = 'itsp.Nextiva')
        OR (TRUNKGROUPNAME1 = 'itsp.Nextiva'
        AND TRUNKGROUPNAME2 LIKE 'dev.%'
        AND CALLINGPARTYNO NOT LIKE '404%'
        AND CALLINGPARTYNO NOT LIKE '678%'
        AND CALLINGPARTYNO NOT LIKE '770%')
GROUP BY IFNULL(mxuser.EXTENSION, mxuser_1.EXTENSION)
HAVING Ext IS NOT NULL;

When I run this query above I get this result:

What I'm trying to achieve is to add TimeIn and TimeOut as total time and to convert seconds to this format HH:MM:SS.

Any thoughts?

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

see

GET_FORMAT()
SEC_TO_TIME()
TIME_FORMAT()

and possibly others.

Not knowing the datatype of your values, I can't be more specific.


MySQL Update Trigger that updates multiple columns from values in another table I have a table PRODUCTS with columns id, name, sku, packaging_type, width, height, weight, case_count.

I want a trigger to fire when packaging_type is updated.  packaging_type will be an integer value.

There is a second table PACK_TYPES with columns id, width, height, weight, case_count.  This is where I need to get the values from.

So PRODUCTS.packaging_type gets updated, how do I update PRODUCTS.width, PRODUCTS.height, PRODUCTS.weight, and PRODUCTS.case_count with the corresponding values where PRODUCTS.packaging_type = PACK_TYPES.id?

If you want to get right down to it, that's not the best approach from a theoretical perspective, because you're copying data around in your database that you really should be deriving.

CREATE ALGORITHM=MERGE VIEW products_with_packaging_info AS
SELECT p.*,
       pt.width as packaging_width,
       pt.height as packaging_height,
       pt.weight as packaging_weight,
       pt.case_count AS packaging_case_count
  FROM PRODUCTS p
  JOIN PACK_TYPES pt ON pt.id = p.packaging_type;

Done.  SELECT queries against this view work exactly the same as queries against either table individually, as long as every product has a pack type.  Queries against this view can still take advantage of the indexes on the base tables, and there's no overhead involved with copying the attributes from one table to another, which always has the potential for update anomalies.

You might even be surprised to find that the columns in the view can actually be updated as if it were a table, with updates propagating down into the base tables.

I offer this suggestion because a well-designed database should be such that it is impossible to get two different answers to the same question.  For example, if a PACK_TYPES row is changed because an error is found, how do its new values propagate backwards into products?

But if you really want to take the trigger approach, that looks something like this:

DELIMITER $$

DROP TRIGGER IF EXISTS PRODUCTS_bu $$
CREATE TRIGGER PRODUCTS_bu BEFORE UPDATE ON PRODUCTS FOR EACH ROW
BEGIN

  IF NOT (NEW.packaging_type <=> OLD.packaging_type) THEN
    BEGIN
      DECLARE my_width INT DEFAULT NULL;      -- using
      DECLARE my_height INT DEFAULT NULL;     -- the
      DECLARE my_weight INT DEFAULT NULL;     -- appropriate
      DECLARE my_case_count INT DEFAULT NULL; -- data types here
      SELECT width, height, weight, case_count
        FROM PACK_TYPES
       WHERE id = NEW.packaging_type
        INTO my_width, my_height, my_weight, my_case_count;
      SET NEW.width = my_width, NEW.height = my_height, NEW.weight = my_weight, NEW.case_count = my_case_count;
    END;
  END IF;

END $$
DELIMITER ;

The <=> "spaceship" is the "null-safe equality operator" which constrains "NOT [possibly null] = [possibly null]" to always be either TRUE or FALSE; this is needed because [possibly null] != [possibly null] will never be true if either expression is NULL. This is the case because, logically, "NOT (FALSE)" is "TRUE" while "NOT (NULL)" is "NULL."

I could have declared the variables at the beginning and avoided the inner BEGIN/END  but it seems optimal to avoid that work until we know we actually need to execute the inner logic in the first place, which is avoided whenever 'packaging_type' hasn't actually changed on a row for a given update query.  Within a block, declarations have to precede other statements, so delaying the declarations requires the addition of the inner BEGIN/END.

You would also want a similar trigger for BEFORE INSERT which would be identical except you'd remove the 4 lines starting with IF ... BEGIN ... END ... END IF from the body of the procedure, use a new trigger name, and change BEFORE UPDATE to BEFORE INSERT.

It's BEFORE -- not AFTER -- in both cases, because the trigger fires BEFORE the newly-inserted or newly-updated row is written to the database.


Update view using derived key in Database Is it possible to update this view by specifying the MessageTech and the txtmessageID? (txtmessageID and MessageId are the primary keys from the respective base tables.)

(SELECT
    txtmessageID,
    'S' AS MessageTech,
    MessageType,
    Message,
    MessageDate,
    MessageMobile,
    MessageRead,
    MessageSender
FROM
    MessageCentre)

UNION ALL

(SELECT
    MessageId,
    'E' AS MessageTech,
    Type,
    Text,
    Date,
    Address,
    [Read],
    Sender
FROM
    MessageCentreEmail)

The with some modification the view should be updateable, but not in its current form.
https://msdn.microsoft.com/en-au/library/ms187956.aspx gives the rules for Partitioned Views

Some things you would need to change:

MessageTech column would need to be part of the underlying tables
MessageTech needs to be part of the primary key of each underlying table
MessageTech needs to have constraints on it in each table such that a given value could not possibly refer to more than one table e.g.

ALTER TABLE MessageCentre Add
    MessageTech char(1) Not Null Default 'S',
    Constraint chk_MessageTechIs_S Check (MessageTech = 'S');

There are some other conditions too, which you can read about in that link, but those are the obvious changes that would need to occur.


Oracle Trace File Interpretation: enq: TX - index contention Background

I've got an application which is doing a large number of concurrent inserts into an Oracle table. The Top Activity view is reporting a lot of index contention and I am attempting to reduce this to improve performance.

The table in question has several indexes, but my suspicion is that the problem is leaf-block contention on the PK which is a monotonically increasing sequence. I know about reverse key indexes, but I'd like to be able to pin-point the problem before I dive in with guesswork fixes.

Trace File Contents

I've extracted a trace from the database when under load. I've included a chunk of it at the bottom, but one part in particular is puzzling me. The largest "enq: TX - index contention" entry accounts for 94% of the total time:

WAIT #47156432773600: nam='enq: TX - index contention' ela= 113183 name|mode=1415053316 usn<<16 | slot=4259845 sequence=43226 obj#=-1 tim=1329387810694927

I don't understand how to interpret this line. Googling has yielded that name|mode=1415053316 means Transaction in share mode (hex TX04), and that obj# would reference the object in SYS.OBJ$.

For the other waits where obj# is specified (see below), it points to the PK index I have suspicions about, but in the largest wait, the obj#=-1.

So, my questions are:

What does it mean for obj# to be -1 in this wait?
What does the usn<<16 | slot=4259845 parameter mean? Is it useful to me?
Does the sequence=43226 tell me anything useful?

More trace detail

PARSING IN CURSOR #47156432773600 len=654 dep=0 uid=41 oct=2 lid=41 tim=1329387810581386 hv=3365236844 ad='2977ccca8' sqlid='31r3hm349aw3c'
insert into AnonymousTable (a, b, c ...) values (:1, :2, :3 ...)
END OF STMT
PARSE #47156432773600:c=0,e=51,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1329387810581384
WAIT #47156432773600: nam='enq: TX - index contention' ela= 113183 name|mode=1415053316 usn<<16 | slot=4259845 sequence=43226 obj#=-1 tim=1329387810694927
WAIT #47156432773600: nam='latch: ges resource hash list' ela= 3958 address=11249153232 number=91 tries=0 obj#=-1 tim=1329387810698944
WAIT #47156432773600: nam='enq: TX - index contention' ela= 1891 name|mode=1415053316 usn<<16 | slot=655387 sequence=48341 obj#=-1 tim=1329387810700949
WAIT #47156432773600: nam='buffer busy waits' ela= 162 file#=32 block#=618823 class#=1 obj#=28134 tim=1329387810701223
WAIT #47156432773600: nam='buffer busy waits' ela= 1572 file#=32 block#=618823 class#=1 obj#=28134 tim=1329387810702840
WAIT #47156432773600: nam='enq: TX - index contention' ela= 1809 name|mode=1415053316 usn<<16 | slot=5767194 sequence=85968 obj#=28134 tim=1329387810704709
WAIT #47156432773600: nam='buffer busy waits' ela= 2254 file#=32 block#=618823 class#=1 obj#=28134 tim=1329387810707018
WAIT #47156432773600: nam='enq: TX - index contention' ela= 15306 name|mode=1415053316 usn<<16 | slot=4784132 sequence=53965 obj#=28134 tim=1329387810722436
EXEC #47156432773600:c=1999,e=142776,p=0,cr=50,cu=364,mis=0,r=28,dep=0,og=1,plh=0,tim=1329387810724203
STAT #47156432773600 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  (cr=50 pr=0 pw=0 time=142726 us)'
WAIT #47156432773600: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=28134 tim=1329387810724288
WAIT #47156432773600: nam='SQL*Net message from client' ela= 363 driver id=1413697536 #bytes=1 p3=0 obj#=28134 tim=1329387810724667
CLOSE #47156432773600:c=0,e=7,dep=0,type=1,tim=1329387810724701

OBJ#=-1 means either means that the object number is missing or it's not related to an object.
The best idea is to go into V$active_session_history and find the SQL_ID then get the SQL text via the SQL_ID and look at the object sin the SQL and what the SQL was doing.

As far as people suggesting INIT TRANS, that is unlikely as there is a separate wait event for INIT TRANS.

USN, SLOT and SEQUENCE  map the UNDO entry for the transaction.
They could be used to map back to activity in the UNDO but it's probably easier to get the transaction id the XID and use VERSIONS_XID on the objects involved.

see http://www.slideshare.net/khailey/ukoug-oracle-transaction-locks

Example ASH query

col object for A15
col otype for A10
select
      substr(event,0,20)    lock_name,
      ash.session_id        waiter,
      mod(ash.p1,16)        lmode,
      --ash.p2                p2,
      --ash.p3                p3,
      o.object_name           object,
      o.object_type           otype,
      CURRENT_FILE#           filen,
      CURRENT_BLOCK#          blockn,
      ash.SQL_ID             waiting_sql,
      BLOCKING_SESSION        blocker
      --,ash.xid
from
        v$active_session_history ash,
        all_objects o
where
          event like 'enq: TX - index contention'
  and o.object_id (+)= ash.CURRENT_OBJ#
/


Will certain Database Compatibility cause performance degradation? I'm running a variety of SQL Servers from 2005 to 2012 across several machines. I have many of these databases running in SQL Server 2000 compatibility mode.

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

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

All the compatibility level does is tell SQL Server to use the older version of the T-SQL language that was available in that version.  For example if within a SQL 2008 R2 server with the database running in SQL 2000 compatibility mode if you try and access some of the new syntax it won't work.

For example

select *
from sys.dm_db_index_physical_stats (db_id(), null, null, null, 'limited')

will throw an error in a SQL 2000 compatibility mode database.

As for query execution, that won't be changed by the compatibility mode.


storing files on mongodb I am developing an app where user will upload multiple images along with some other data like name address etc.

I am using MEAN stack for development, So I have some questions regarding this:

How should I store image data in my db, like what information should I save in my db ?
I have read that we can use GridFS by mongodb. But my images would be of max 5MB in size. So what should I use simple fs by node or gridfs by mongodb ?
What is the need of storing file data on amazon s3, can't I just save base64 string into my database ?

I have googled more and found out that storing images on storage is more beneficial than storing them directly into db. So I think I will go with s3, but then how can I refer to the images stored in s3 from the db ?

my app consist of 200 users and each user can upload upto 5 images and upto 5mb of size, and yes it is performance critical also.

I've worked on a project where all files where stored inside the database (SQL Server). The decision was made because the system used 2 machines with Mirroring for High Availability and saving the files to the file disk would be troublesome to handle.

The engineer that decided this at the time didn't know how easy, fast and cheap is to store files in the Cloud and we end up with a 11 GB database where 10 GB consists only of files. It was expensive to save backups and the db restore process (for testing or disaster recovery) was much slower than needed.

Using MongoDB GridFS is quite easy and you can start by following the documentation here or with this tutorial, but I would not recommend it. There are many discussions of pros and cons of using the DB to store files and you may agree with me after reading these links:

Storing Images in DB - Yea or Nay?
Storing a file in a database as opposed to the file system?
What are the pros and cons of storing files in a database?

The two major pros are:

Performance (that is critical for your use case)
Cost (DB storage is much more expensive than Cloud storage)

If you store the image in S3, you will end up with a link similar to this:
https://s3-sa-east-1.amazonaws.com/mybucketname/myimage.png

This is a very lightweight info that you can store in your Mongo document. To retrieve this file, you have two common options:

If its a file URL that's open to the world, you can give the URL to the client browser and make the Angular app to download it. If you use Amazon CloudFront (CDN service), the low latency server will be a real boost for your user.
If the file is private, you can use your Node.js app to download it and send a copy to your user.


Rebuilding a single corrupt table with dual master replication I have a replication setup with two MariaDB 10.2 instances set up in a dual-master configuration. On host A, a table has been corrupted somehow; whenever I try to read from it (including CHECK TABLE tablename) the process crashes.
May 05 13:04:05 hostA.my.internal mysqld[2666]: 2021-05-05 13:04:05 0x7f8b781c1700  InnoDB: Assertion failure in file /home/buildbot/buildbot/padding_for_CPACK_RPM_BUILD_SOURCE_DIRS_PREFIX/mariadb-10.2.36/storage/innobase/btr/btr0cur.cc line 376
May 05 13:04:05 hostA.my.internal mysqld[2666]: InnoDB: Failing assertion: btr_page_get_next(get_block->frame) == page_get_page_no(page)

I'm planning to take host A offline, and use innochecksum to diagnose the problem.
If, as I suspect, I need to rebuild this table, what is the quickest and safest way to go about it? I know I could repopulate host A's entire database from a dump of host B and rebuild replication from scratch, but I am hoping to avoid the trouble if it's possible.
If I get a dump of the table from host B and try importing it on host A, then DROP TABLE and all those INSERT statements (about 1.6 million rows) will be replicated, causing a lot of traffic and interfering with live services accessing host B.
Tagging as MySQL as well, since this seems like it should be applicable to either.

Ensure host B is your active host in the dual master
Rest of instructions are on host A
As reading from A.tablename will probably crash it, try this with another tablename to be familiar with what its doing. pt-table-sync --dry-run --print --execute --sync-to-master h=hostB,D=db,t=sometablename
look closely at the above.
SET GLOBAL innodb_file_per_table=1 (if not already)
STOP SLAVE
To have a new table idb file: set session sql_log_bin=0; RENAME TABLE tablename TO tablename_orig
quick populate table from B without replicating: ( echo 'set session sql_log_bin=0;' ; mysqldump --single-transaction --host hostB db tablename ) | mysql --host A db
start slave
pt-table-sync --print --execute --sync-to-master h=hostB,D=db,t=tablename

Please read pt-table-sync carefully. Its been a few years since I've used this. Note the bits about it locking tables briefly on the master for small periods of time to read data in small chunks. This, and all the other interactions should be obvious on from the --dry-run --print.


mongodb 2.6 mongos start up failed I'm trying to run mongodb 2.6, everything followed my 2.4 version setup steps.
but when I start mongos I got the following problems and just cannot make it.

2014-04-24T03:38:31.001+0800 [mongosMain] SyncClusterConnection connecting to [192.168.0.1:27018]
2014-04-24T03:38:31.002+0800 [mongosMain] SyncClusterConnection connecting to [192.168.0.2:27018]
2014-04-24T03:38:31.002+0800 [mongosMain] SyncClusterConnection connecting to [192.168.0.3:27018]
2014-04-24T03:38:31.066+0800 [mongosMain] scoped connection to 192.168.0.1:27018,192.168.0.2:27018,192.168.0.3:27018 not being returned to the pool
2014-04-24T03:38:42.183+0800 [mongosMain] waited 11s for distributed lock configUpgrade for upgrading config database to new format v5
2014-04-24T03:38:53.288+0800 [mongosMain] waited 22s for distributed lock configUpgrade for upgrading config database to new format v5
2014-04-24T03:39:04.394+0800 [mongosMain] waited 33s for distributed lock configUpgrade for upgrading config database to new format v5
2014-04-24T03:39:15.500+0800 [mongosMain] waited 44s for distributed lock configUpgrade for upgrading config database to new format v5
2014-04-24T03:39:26.604+0800 [mongosMain] waited 55s for distributed lock configUpgrade for upgrading config database to new format v5
2014-04-24T03:39:37.709+0800 [mongosMain] waited 66s for distributed lock configUpgrade for upgrading config database to new format v5

Waited for new format v5?
What's that?

There was a full set of instructions for the 2.6 upgrade that you don't seem to indicated that you have followed.
In particular are the instructions that indicate you need to process the --upgrade option by launching a single mongos instance while the balancer is switched off:

Start a single 2.6 mongos instance with the configDB pointing to the cluster’s config servers and with the --upgrade option.
To run a mongos with the --upgrade option, you can upgrade an existing mongos instance to 2.6, or if you need to avoid reconfiguring a production mongos instance, you can use a new 2.6 mongos that can reach all the config servers.

mongos --configdb <config servers> --upgrade

As noted there, on completion the following messages should be emitted:
upgrade of config server to v5 successful
Config database is at version v5

So there is an intended upgrade process for the config servers that it would appear you have not followed yet. The full process is in the link at the top of this answer.
Also have a look at the compatibility changes list which has some additional considerations with replica sets and sharding that might possibly apply in your situation.


Question about a two-node MariaDB Galera Cluster Hypothetical situation:

Node1 is where data gets written to by the app server ... it was where the cluster was bootstrapped from. Node 2 is the second node in the cluster. Both these nodes run in the same data center.

Let's say there was a network event; maybe a sysadmin pulled out a switch he wasn't supposed to pull ... we don't know!

Now, both nodes are momentarily offline and both nodes come back online again after say a 5 min downtime where the sysadmin realizes his mistake and hastily puts the switch back in.

Now since both nodes were affected and came back online about the same time ... the cluster automatically corrected itself after spewing several messages like this:

WSREP: Quorum: No node with complete state

re-bootstrapping prim from partitioned components

WSREP: Full re-merge of primary 9j421w44-0e4c-12r5-7fa2-3b63f2c92b0p found

Nobody notices this slight hiccup and everyone assumes everything is fine.

Now after a few days one of the devs begins noticing that data being written to node1 is being overwritten by older data. They pore through app logs and finally through the mysql logs and see the ^above messages.

They run this query on both nodes:

SHOW STATUS LIKE 'wsrep_last_committed';

They find that the value for node 1 lags behind node 2 and come to the conclusion that node2 is overwriting data from node1.

The database admin says "HOGWASH! THIS IS NOT POSSIBLE IN A SYNCHRONOUS REPLICATION CLUSTER SETUP!!"

My questions - is this hypothetical scenario possible? If it is possible, could the Dev be correct in implying that node 2 is overwriting data from node 1?

Asking for a friend (who knows that having a 2 node cluster is just asking for split brain) ...

I'd say highly unlikely. During the network partition, when switch was pulled, (on an equally weighted quorum calculation) quorum cannot be achieved and the two node cluster loses primary component status. Exactly the reason not to have an equally weighted two node setup. When there's no prim component, there's no DML. After the situation is fixed the nodes find each other to form prim component and continue synchronous replication.

Highly unlikely that they get out of sync this way.


Identify the version of a collation from ICU in Postgres Postgres 10 and later incorporates the International Components for Unicode (ICU) library for text-handling and other internationalization issues.

Changes happen to human languages, such as sorting rules evolving. Therefore collation definitions are versioned in ICU.

➥ How can I identify the version number of a collation?

I know Postgres will report version number as part of a collation version mismatch error message. Is there a way to proactively query for the version rather than wait for an error message?

TL;DR

Check out the pg_collation_actual_version() function.

DETAILS

The PostgreSQL documentation for ALTER COLLATION seems to discuss this issue in the "Notes" section:

  When using collations provided by the ICU library, the ICU-specific version of the collator is recorded in the system catalog when the collation object is created. When the collation is used, the current version is checked against the recorded version, and a warning is issued when there is a mismatch...

  A change in collation definitions can lead to corrupt indexes and other problems because the database system relies on stored objects having a certain sort order. ...

  The following query can be used to identify all collations in the current database that need to be refreshed and the objects that depend on them:

SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS "Collation",
       pg_describe_object(classid, objid, objsubid) AS "Object"
  FROM pg_depend d JOIN pg_collation c
       ON refclassid = 'pg_collation'::regclass AND refobjid = c.oid
  WHERE c.collversion <> pg_collation_actual_version(c.oid)
  ORDER BY 1, 2;

You will notice that the given query makes use of the pg_collation_actual_version() function, which is defined as:

  pg_collation_actual_version returns the actual version of the collation object as it is currently installed in the operating system. If this is different from the value in pg_collation.collversion, then objects depending on the collation might need to be rebuilt.


Local Admins on SQL Server systems I need to monitor the local admins on my SQL Server systems for auditing. Is there a way to use a stored procedure with powershell to accomplish that?

You don't need to use Powershell for this, which would require xp_cmdshell in a stored procedure. Just use xp_logininfo. As long as you add the group to SQL Server (you don't have to give it any permissions), you can query the members of it.

EXEC master.dbo.xp_logininfo 'Builtin\Administrators', 'members'


Agent job will not run I created a JOB to run a simple package that runs a query and saves the results to an Excel file. If I try to run the job In SQL Management Studio I get an error saying:

  SQLServerAgent is not currently running so it cannot be notified of
  this action. (Microsoft SQL Server, Error: 22022).

I made sure that SQLServerAgent was in fact running and tried running the job again (from SQL Managment Studio) but still got the same message.

I found this knowledge base article which is telling me that I have to edit the Registry in order to resolve the problem.

Before I go in and start messing with the registry which is something I really don't want to do I was wondering if anyone else has experienced this problem and if they had to edit their registry to fix it?

Turns out I was connecting to a Load Balancer which redirects me to either one of 2 sql servers which are synchronized ( for better performance) the SQLServer Agent was turned on on one of the servers but it was off on the other server. I needed to create my job on the main server (which has the agent service running). I didn't know we had 2 SQL servers that were identical and that I was connecting to a different one every other time. Sorry about that, but thanks again for the helpful comments, I found some interesting things in the error logs that helped me figure this all out. Thanks again!


Invalid number error Hi I'm doing a dimensional modeling exercise in college and I'm trying to convert the total_price entry in two of the database tables to euro so they will be in the dimensional table as euro. The two tables are tournament1 and tournament2 and the dimensional table is dim_tournament. Here is the code I am trying to run:

insert into Dim_tournament (Tournament_SK, total_price, Tournament_DESC)
Select
  seq_Tournament.nextVal,
  total_price,
  t_descriprion
From (
       select
         t_id,
         cast(to_char(total_price, 'C9,999,999.00')as integer),
         t_descriprion
       from Tournament1
       union all
       select
         t_id,
         cast(to_char(total_price, 'C9,999,999.00')as integer),
         t_descriprion from Tournament2
     );

This is the error I keep getting when running this:

Error report -
SQL Error: ORA-01722: invalid number
01722. 00000 -  "invalid number"
*Cause:    The specified number was invalid.
*Action:   Specify a valid number.

Any help would be very much appreciated.

This:

  cast(to_char(total_price, 'C9,999,999.00')as integer),

is wrong.

Assuming total_price is defined as number the result of converting e.g. 1234.56 will be:

EUR1,234.56

(the exact output depends on your NLS settings).

Now the string EUR1,234.56 is not a valid number any more (at least not to a computer). So your cast to an integer fails.

If you only want to display the total price as a formatted string, only to_char() is required. Remove the cast()


How do I replicate Mysql DB server to new server? Let me give you a preface by stating I am fairly new in Mysql environment. I am trying to replicate my production server to a new server (with better hardware configuration).

I want to replicate every bit of data on production server to new server keeping permissions, users and privileges intact. I just learned about the mysqldump -u root -p databasename > file.sql command but I am not sure how it is going to affect the permissions and users.

Please suggest what is the best way to replicate database in Mysql by keeping the integrity of the data. Help is greatly appreciated.

If you are comfortable with a maintenance window, here is what you can do:

Step 01) You need to add this line to my.cnf on Server1

[mysqld]
log-bin-mysql-bin
expire-logs-days=7

Step 02) Restart mysql so that no one else can login to Server1

service mysql restart --skip-networking

Step 03) Dump everything and gzip it

DUMPFILE=/root/MySQLData.sql.gz
mysqldump --master-data=2 --single-transaction --flush-privileges -uroot -p --routines --triggers --all-databases | gzip > ${DUMPFILE}

Step 04) Restart mysql on Server1 to allow normal connectivity

service mysql restart

Step 05) Move the ZIP file to the new server (Server2) in /root

Step 06) Unzip the file on Server2

gzip -d /root/MySQLData.sql

Step 07) Login into mysql on Server2

mysql -uroot -p

Step 08) Load the data from the mysql prompt on Server2

mysql> source /root/MySQLData.sql

That's it. That makes all the data as of that dump available on Server2

If you wanted to establish MySQL Replication so that everything written on the Master immediately gets done on the Slave, the steps are a little different. For this example, you will need the Private IP address of Server1. You can get that with ip addr show. Let's suppose the Private IP address of Server1 is 10.1.2.20.

Step 01) You need to add this line to my.cnf on Server1

[mysqld]
log-bin-mysql-bin
expire-logs-days=7

Step 02) Restart mysql so that no one else can login to Server1

service mysql restart --skip-networking

Step 03) Create a Replication Username in mysql client:

mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO repluser@'10.1.2.20' IDENTIFIED BY 'replpass';

Step 04) Dump everything and gzip it

DUMPFILE=/root/MySQLData.sql.gz
mysqldump --master-data=1 --single-transaction --flush-privileges -uroot -p --routines --triggers --all-databases | gzip > ${DUMPFILE}

Step 05) Restart mysql on Server1 to allow normal connectivity

service mysql restart

Step 06) Move the ZIP file to the new server (Server2) in /root

Step 07) Unzip the file on Server2

gzip -d /root/MySQLData.sql

Step 08) Login into mysql on Server2

mysql -uroot -p

Step 09) Setup replication in mysql client:

CHANGE MASTER TO
MASTER_HOST='10.1.2.20',
MASTER_PORT=3306,
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mysql-bin.000000',
MASTER_LOG_POS=2;

Don't worry about log file and position. They are bogus. The mysqldump has the correct log file and position at line 22. In order to see run this:

head -22 source /root/MySQLData.sql | tail -1

Step 10) Load the data from the mysql prompt on Server2

mysql> source /root/MySQLData.sql

Step 11) Start replication

START SLAVE; SELECT SLEEP(3); SHOW SLAVE STATUS\G

If Slave_IO_Running=Yes and Slave_SQL_Running=Yes, you are done.


What's the optimal server configuration for fastest inserting in a MySql database in this scenario? I have a MyISAM table partitioned by range with 90 partitions representing the last 90 days. I expect this table to receive somewhere between 500 million to 1 billion records before i start rotating partitions (dropping old ones and creating new ones).

What server configuration options would you put in your .my.conf file to ensure that the fastest possible insertion into this table was possible at all times?

Any other tips would also be appreciated.

EDIT: By 'fastest possible' i mean best possible insert speed and being sustained once it contains 100's of millions of rows.

There are two major things you can change. One involves the table and one involves a config setting.
TABLE CHANGE
You can increase SELECT and INSERT speed by changing the MyISAM table's row format to FIXED. In order to do do, you would run
ALTER TABLE mydb.mytable ROW_FORMAT=Fixed;

There is a drawback to this: This could potentially increase the table size 80-100%
I have discussed changing MyISAM row format before

May 10, 2011 : What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?
Aug 12, 2011 : Which DBMS is good for super-fast reads and a simple data structure?
Jul 06, 2012 : MySQL table with 100,000 records queried often
Sep 26, 2012 : Choosing MyISAM over InnoDB for these project requirements; and long term options

CONFIG SETTING
MyISAM supports a special protocol called a concurrent insert. It works by appending new rows to a MyISAM as long as there are no gaps as a result of DELETEs.
Here is some good news: you can disable the gap check for concurrent insert
[mysqld]
concurrent_insert = 2

According to the MySQL Documentation on concurrent_insert, here is what happens for value 2:

Enables concurrent inserts for all MyISAM tables, even those that have holes. For a table with a hole, new rows are inserted at the end of the table if it is in use by another thread. Otherwise, MySQL acquires a normal write lock and inserts the row into the hole.

If the table is being heavily read, new INSERTs just get appended. When you are the only DB connection with a file handle to the table, then a normal gap check is done.
See the MySQL Documentation on Concurrent Inserts
EPILOGUE
These two changes combined may produce surprising performance results. Why? Using ROW_FORMAT=Dynamic would trigger a search for a gap large enough to hold a new row. If the table has ROW_FORMAT=Fixed, all gaps are the same size. A new row insertion would be as quick as possible because the first gap row searched is used immediately for the insertion.


Run Powershell Script From Agent As Administrator I need to recycle an AppPool in IIS on my server when the SQL Server Service starts.

The route I am taking is to have a startup stored procedure which runs an Agent job  that has a Powershell job step.

I have created / grabbed from the Internet a Powershell script which recycles the app pool:

# Load IIS module:
Import-Module WebAdministration
# Set a name of the site we want to recycle the pool for:
$site = "Default Web Site"
# Get pool name by the site name:
$pool = (Get-Item "IIS:\Sites\$site"| Select-Object applicationPool).applicationPool
# Recycle the application pool:
Restart-WebAppPool $pool

This works at the OS level but only when Powershell is run as an administrator (Even though the account I am logged into Windows as is in the Admin group)

Sure enough, if I create the job in agent with a Powershell step containing the above code, upon execution I get the error

  A job step received an error at line 6 in a PowerShell script. The
  corresponding line is '$pool = (Get-Item "IIS:\Sites\$site"|
  Select-Object applicationPool).applicationPool  '. Correct the script
  and reschedule the job. The error information returned by PowerShell
  is: 'Cannot retrieve the dynamic parameters for the cmdlet. Filename:
  redirection.config  Error: Cannot read configuration file due to
  insufficient permissions      '.  Process Exit Code -1.  The step
  failed.

So I need a way to "Run as Administrator" from SQL Server Agent

I had success at the OS level creating a new script which runs the original script with elevated permissions:

If (-NOT ([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole]::Administrator))
{
  # Relaunch as an elevated process:
  Start-Process powershell.exe "-File",('"{0}"' -f $MyInvocation.MyCommand.Path) -Verb RunAs
  exit
}
# Now running elevated so launch the script:
& "d:\path\to\my\script.ps1"

which allows me to run the new script from either a non elevated CMD prompt or non elevated Powershell prompt. However, when i try to run from agent as either a Powershell step or CMD step I get

  The machine-default permission settings do not grant Local Activation
  permission for the COM Server application with CLSID  {longguidhere}
  and APPID  {longguidhere}  to the user NT SERVICE\SQLSERVERAGENT SID
  (longguidhere) from address LocalHost (Using LRPC) running in the
  application container Unavailable SID (Unavailable). This security
  permission can be modified using the Component Services administrative
  tool.

I have also tried

Start-Process powershell -Verb runAs -FilePath "d:\path\to\my\script.ps1"

which again, at the OS level worked but when run from Agent gives me the same error.

How can I get either

a: recycle the app pool without requiring elevated priveleges

or

b: run the script to recycle the app pool with elevated priveleges from SQL Server Agent?

Create a PowerShell proxy in SQL Server agent that uses a privileged Windows account via a SQL Server credential.

CREATE CREDENTIAL docs.

How to create a proxy

That will allow you to run PowerShell as an administrator, directly from SQL Server.

Creating a credential is the first step, and consists of running a T-SQL statement like this:

CREATE CREDENTIAL MyPowerShellCredential
WITH IDENTITY = 'DOMAIN\AdministratorAccount'
    , SECRET = 'AdministratorAccountPassword'

Once the credential is created, you need to create a SQL Server PowerShell Proxy.  This should work:

USE msdb;
EXEC dbo.sp_add_proxy @proxy_name = N'MyPowerShellProxy'
    , @enabled=1
    , @description = N'Proxy used to run PowerShell as an administrator'
    , @credential_name = N'MyPowerShellCredential';

After creating the proxy, you need to enable it for use by the PowerShell subsystem.  This code does that:

USE msdb;
EXEC dbo.sp_grant_proxy_to_subsystem @proxy_name = N'MyPowerShellProxy'
     , @subsystem_name = N'PowerShell'

Once you have the proxy created, you'll need a SQL Server Agent Jobstep configured to run PowerShell, which will automatically make use of the PowerShell proxy.

This is a sample SQL Server Agent Job that will run your code:

USE [msdb]
GO
DECLARE @jobId binary(16);

EXEC  msdb.dbo.sp_add_job @job_name = N'ResetAppPool'
        , @enabled = 1
        , @notify_level_eventlog = 0
        , @notify_level_email = 2
        , @notify_level_page = 2
        , @delete_level = 0
        , @category_name = N'[Uncategorized (Local)]'
        , @owner_login_name = N'sa'
        , @job_id = @jobId OUTPUT;

EXEC msdb.dbo.sp_add_jobserver @job_name = N'ResetAppPool'
    , @server_name = N'(LOCAL)';

EXEC msdb.dbo.sp_add_jobstep @job_name = N'ResetAppPool'
    , @step_name = N'Step1'
    , @step_id = 1
    , @cmdexec_success_code = 0
    , @on_success_action = 1
    , @on_fail_action = 2
    , @retry_attempts = 0
    , @retry_interval = 0
    , @os_run_priority = 0
    , @subsystem = N'PowerShell'
    , @command = N'# Load IIS module:
Import-Module WebAdministration
# Set a name of the site we want to recycle the pool for:
$site = "Default Web Site"
# Get pool name by the site name:
$pool = (Get-Item "IIS:\Sites\$site"| Select-Object applicationPool).applicationPool
# Recycle the application pool:
Restart-WebAppPool $pool'
    , @database_name = N'master'
    , @flags = 0;

EXEC msdb.dbo.sp_update_job @job_name = N'ResetAppPool'
    , @enabled = 1
    , @start_step_id = 1
    , @notify_level_eventlog = 0
    , @notify_level_email = 2
    , @notify_level_page = 2
    , @delete_level = 0
    , @description = N''
    , @category_name = N'[Uncategorized (Local)]'
    , @owner_login_name = N'sa'
    , @notify_email_operator_name = N''
    , @notify_page_operator_name = N''
GO

You could modify the job to send an email to you when it runs, just so you know it worked.

Regarding this error you're seeing:

  The machine-default permission settings do not grant Local Activation permission for the COM Server application with CLSID {longguidhere} and APPID {longguidhere} to the user NT SERVICE\SQLSERVERAGENT SID (longguidhere) from address LocalHost (Using LRPC) running in the application container Unavailable SID (Unavailable). This security permission can be modified using the Component Services administrative tool.

That indicates User Account Control is running in Admin Approval Mode, requiring every action that requires elevation to ask the user for consent.   Since SQL Server Agent is running as a service, there is no chance for the elevation prompt to run, hence the failure.  You can assign the NT SERVICE\SQLSERVERAGENT account rights to locally activate PowerShell in elevated mode via the Component Services applet in Control Panel without requiring the prompt.

Alternately, you can configure Windows so it never requires elevation for members of the local Administrators group.  Do that via the Local Security Policy applet.  Set the following settings:

Open the Security Settings\Local Policies\Security Options folder.
Set "User Account Control: Admin Approval Mode for the Built-in Administrator Account" to "Disabled"
Set "User Account Control: Behavior of the elevation prompt for administrators in Admin Approval Mode" to "Elevate without Prompting"
Set "User Account Control: Run all administrators in Admin Approval Mode" to "Disabled"


MySQL Cluster node utf8 I have configured MySQL Cluster (5.6) with two data nodes and managment node.
Problem is that I cannot put special chars - looks like encoding is not set to utf8/utf16.

I logged into each node and set server encoding, database encding etc. to UTF8, but this not helped (it helped on standard MySQL server). Tables are also created with utf8.

Is there any other settings (maybe on managment node?) that should I set up to utf8 (or utf16)?

Show us what you did to (1) use utf8 in the client(s), (2) how you established the charset from client to server, and (3) SHOW CREATE TABLE.

If you left out any one of those, that that is likely to be the problem.

Do not use utf16, only use utf8.  (Unless you have some good argument to the contrary.)

If you have INSERTed some data, let's see SELECT col, HEX(col)... to see if it was garbled on the way in.


How to open queries into a selected database in SQL Server Management Studio 2005 My situation:

Using Microsoft SQL Server Management Studio 2005, I connect to a server -- I’ll call it Prod -- using Windows Authentication.

Within this Prod server there are multiple databases: dbA, dbB and dbC.

On a previous computer, if I had the ‘Tables’ folder for a particular database (e.g. dbB) selected and opened an existing query (.sql file) from a local or network directory, the existing query would open “connected” to the selected database (dbB).   On my replacement computer, existing queries always open connected to 'master' on the SQL Editor toolbar instead of the expected dbB database.

A minor inconvenience, yes, but if there is a simple setting I can modify or update to restore this preferred behavior (queries open into previously selected database), I'd appreciate learning how to do it.

Thanks in advance for any suggestions.

Some options that you have:

pointed already by gbn (change current connection)
click first in the databases tree and then click 'new query' or 'open file' - they will be open in the scope of the previously selected database
choose a preferred (default) database for the user (either in Security - user properties or in connection preferences)
always use USE in your queries and not rely on Management Studio :-)
add to the previous option the sqlcmd mode of Management Studio (tools - options - query execution - use sqlcmd mode) and this allows you to make use in your scripts of the :connect command which will allow you to run a specific script only in the context of a specific server


How To Import Binary Dump File To SQL Server I am trying to import a dump file which is in binary format to SQL Server using sqlcmd command. After running the import command, I am getting this error, Msg 2812, Level 16, State 62 I'm suspecting that it is because the file is in binary format. Is there a way to directly import binary dump file to SQL Server? I tried using the -u flag but I'm getting the same error.

Sorry, forgot to mention, I am using SQL Server 2017 on Ubuntu 16.04

Command: sqlcmd -S localhost -d TestDB -U SA -u -i /home/user1/Downloads/myspecialpresentforyou

Error Message: Msg 2812, Level 16, State 62, Server user1, Line 1
Could not find stored procedure 'TAPE'.

The problem of tango ward is that he tries to pass full backup filee as a script file to sqlcmd.
I understood it as he wrote the error is

  Could not find stored procedure 'TAPE'.

The first bytes of a backup file even if it's disk file are 'TAPE':

To make a simplest restore from the bak file without restoring it to another database and moving the files you still can use sqlcmd, but you should pass your file as a backup file location, i.e. use this command:

restore database MyDB from disk = '/home/user1/Downloads/myspecialpresentforyou'

Here is simple example of doing it in sqlcmd:

First I launch sqlcmd (without parameters I'll connect to my
default instance with win authentication, but you should use your
sa user and password as you did)
Once launched sqlcmd you'll see 1> it's a prompt to enter your
command, now enter there RESTORE...(the whole command, see above) and
after entering it (press ENTER) you'll see 2>
Now enter GO to send your command

Here is how I restored my database abc from full backup Z:\Anna\backups\default\abc_full.bak

If you restore to another database you should first know logical file names using restore filelistonly and then move the files to new destination.

If it's your case you'd better use SSMS to see these files as sqlcmd output can be combersome if your db has many files.

Here the example in SSMS:


Setting Audit Events in SQL Server 2012 We have to do information assurance remediation on our SQL Server 2012 and came across the following finding: Check to see that all required events are being audited.  So I ran these queries:

select DISTINCT traceid from ::fn_trace_getinfo('0')

Select DISTINCT(eventid) FROM ::fn_trace_geteventinfo('1')

This returned about 34 different eventids that I assume are being used for audit purposes.  Most of the items returned are NOT to be audited and should be replaced with others.  However, I have no idea how to remove eventids and how to add new ones.  For instance I am missing 14,15,112,113,114,118,128,129 and many others.  So my question is how to I remove these audits and add news ones?  A way to script it would be best or at least run it as a query.

This sample might help in order to script events. To capture the specific events, you need to create a Server audit specification first - The SQL Server Audit object collects a single instance of server or database-level actions and groups of actions to monitor:

USE master;
GO
CREATE SERVER AUDIT ServerAudit
TO FILE (FILEPATH = 'c:\audits\', MAXSIZE = 2 GB)
WITH (ON_FAILURE = CONTINUE);
GO
ALTER SERVER AUDIT ServerAudit
WITH (STATE = ON);

The next step is to create a Database audit specification at the database level. The audit group in this example is is SCHEMA_OBJECT_CHANGE_GROUP

USE [ACMEDB];
GO
CREATE DATABASE AUDIT SPECIFICATION schema_change
FOR SERVER AUDIT ServerAudit
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON);
GO

Finally, for example, query the previously created audit, by using the LIKE operator to narrow down captured entries to the ones related to enable/disable triggers:

SELECT
   event_time AS [Time],
   server_principal_name AS [User],
   object_name AS [Object name],
   Statement
  FROM sys.fn_get_audit_file('c:\audits\ServerAudit*', NULL, NULL)
WHERE
   database_name
   =
   'ACMEDB'
AND (
   Statement LIKE '%DISABLE%TRIGGER%'
OR Statement LIKE '%ENABLE%TRIGGER%')ORDER BY
                                      [Time] DESC;


Connection AlwaysOn AG Listener Problem I'm a newbie for microsoft technologies and I'm having problems in connectivity part for SQL Server 2014. This is probably an easy issue but I'm stuck.

I've 4 servers. One of them is the Active Directory Domain Controller, one is the application server and the others are being used for SQL Servers. Since it's a connectivity issue, IP's for servers are;
-AD DC : 10.6.0.100 ( Also the DNS Server )
-APP   : 10.6.0.110
-SQL 1 : 10.6.0.120
-SQL 2 : 10.6.0.121

I've successfully created a failover cluster (DBCLUSTER) and set the IP address of the FC as 10.6.0.130 ( which is not an actual server IP, I really don't get this part.. ).

Later, I've created an AlwaysOn Availability Group for sql servers. I've successfully created the AG without listener. I could connect to servers from each other, database syncs without problem. Then, I've created a listener (AG-LISTENER) and set it's ip as 10.6.0.131 ( which is not an actual server IP, again? ) and set it's port to 5525. It gave no problems.

So I wanted to test connectivity. When I wanted to make connections from APP server to directly SQL 1 or SQL 2, I can connect without problem. But when I try to connect to AG-LISTENER, it cannot find it on network. When I check the DNS records. I can see it as it's being hosted on 10.6.0.131.

When I tried to ping to AG-LISTENER from AD-DC, APP, SQL 2 servers, it responds that the destination host is unreachable (it's pinging 10.6.0.131 but the response comes from the IP's of AD-DC, APP and SQL 2 servers). It can connect from SQL 1 server, which is the primary for AG.

I've checked the firewalls, there's no problem. But I think this is an network issue which I have no clue.

PS: Servers are operating on Windows Server 2012 and are not being hosted on Azure.

SOLUTION

Cause of the problem was the network topology of the service provider. It's found that IP's 10.6.0.0/32 could be used by other servers of other customers. So they've allocated us another IP block which only we have access to and it worked like a charm.

It can connect from SQL 1 server, which is the primary for AG.

By "connect", do you mean it can ping AG-LISTENER from SQL1?
It sounds like what your problem might be is with the port number you chose for your listener.  By choosing 5525, you are selecting a non-default port (1433 would be the default).
So when you try to connect to the listener, what does your connection string look like?  I'm guessing it looks something like this:
data source = ag-listener; initial catalog = ...

You have two options here.  You can either be explicit with your listener's port number:
data source = ag-listener,5525; initial catalog = ...

Likewise, if you're testing this out with SQL Server Management Studio (SSMS), then for the Connect to Server dialog box, instead of putting in ag-listener for the Server name text box, put in ag-listener,5525.
Or you can change the port that your listener is listening on to 1433 (read the below BOL reference before considering this change):
alter availability group YourAvailabilityGroupName
modify listener 'AG-LISTENER'
(
    port = 1433
);

It is worth noting when you can use the default port (1433).  Take a look at this reference on BOL explaining when you can and can't use 1433 for the listener (excerpt copy/pasted below for reference):

You can configure the default port to 1433 in order to allow for simplicity of the client connection strings. If using 1433, you do not need to designate a port number in a connection string. Also, since each availability group listener will have a separate virtual network name, each availability group listener configured on a single WSFC can be configured to reference the same default port of 1433.
(portions omitted for brevity)
If you use the default port of 1433 for availability group listener VNNs, you will still need to ensure that no other services on the cluster node are using this port; otherwise this would cause a port conflict.

EDIT: If the above isn't your problem (as seen from your comment below) then, after you look through the logs, I'd say the next best course of action is to start looking at the network traffic to see what is (and isn't) happening.  You can use a network monitoring tool like netmon to accomplish this.
Another thing I'd do, and I realize you said the firewall isn't a problem, but I'd see if the port is actually listening (my favorite tool for this is portqry).


Schema Design with "Seasonal" Data I am developing a SaaS web application for School Management. What I need help is this:

The whole application is purposed for the current year that the School is in. However, some data and statistics should be able to be accessed from passed years.

For example: It is extremely important to keep old students contact details or grades etc. However I don't think its a good idea to keep all the old and current students on the same table, because I will have to manually use "WHERE" clauses everywhere (Grades, evaluations, comments etc.) and that might be a bit "messy".
So, another approach would be to use a different database perhaps for each year. Keep in mind i intend to use a single-tenant approach.

Any ideas on these two approaches or perhaps something else?

How large do you expect the data to grow? No harm in keeping older data in the same table. You could plan to use partitioning and implement date range partitions (per year). Using WHERE statements is completely natural in SQL so don't consider this as "messy" at all.


Why do my backup policies fail when a database's guest user is disabled? I'm attempting to implement Policy Based Management on a SQL Server 2012 instance also acting as a Central Management Server using the Enterprise Policy Management Framework. The job running the Powershell script has a login on all servers allowing it to connect and view all databases. It also has all related msdb roles on the CMS. However, the policy only executes against a database if its guest user is enabled. Instances with no databases with an enabled guest just return "No Target Found".

Am I missing something obvious, or do I have to give the agent account a user on every database on every instance?

I've spent most of today bashing my head against this, and didn't come up with anything. I tried:

Messing with msdb roles on the CMS
Messing with msdb roles on the target servers
Messing with server roles on the CMS and target servers
Petitioning the gods of old
Trying something convoluted involving credentials and proxies

In the end, nothing worked. I believe this is possible on SQL Server 2014, which has a "CONNECT ALL DATABASES" server-level permission, but 2012 has no equivalent that isn't also a much larger security risk than I'm willing to accept.

I decided to bite the bullet and add the account as a user on every database. Here's what I did:

exec sp_MSforeachdb 'if ''?'' in (''master'',''msdb'',''tempdb'') return; use [?]; CREATE USER [domain\account] FOR LOGIN [domain\account];'

Executing this script against the CMS added the user to every database on every instance except for master, msdb, and tempdb. I left model in so that when we create databases in the future, they'll already have the user added.

If anyone has a better way that doesn't involve mapping to every database, feel free to share, but at this point I'm not convinced it can be done (short of making the account sysadmin, which I doubt would make it into production).


MySQL 5.5 - Innodb - innodb_log_file_size higher than 4GB combined? When reading online about innodb_log_file_size, it says

  The combined size of the log files must be less than 4GB

So what happens if it needs to be larger?

In books and online, it says to set the innodb_log_file_size to at least an hour's
worth of writes.

Currently I have 2x 1GB log files that when calculating Innodb_os_log_written for 1 hour, needs at least 10GB.  2GB of log files can only do 20 minutes of writes on our peak load.

tia!

Years ago, I would set innodb_log_file_size to 2047M. This is the biggest number allowed to create transaction log files without surpassing InnoDB's 4GB limit. I believe it still applies to the current version (MySQL 5.5). Keep in mind that InnoDB was designed to work in a 32-bit environment, thus the 4GB limit.
GOOD NEWS
Percona Server binaries actually surpass this limitation. I believe I have seen 4GB as innodb_log_file_size in one installation. That's right, I said a 4GB ib_logfile0 and a 4GB ib_logfile1, ** totaling 8GB**. Here is a current listing from the DB Server of one of my employer's clients:
[root@*** ~]$ ls -l /var/lib/mysql/ib*
-rw-rw---- 1 mysql mysql 2283798528 Jan 16 22:53 /var/lib/mysql/ibdata1
-rw-rw---- 1 mysql mysql 4294967296 Jan 16 22:52 /var/lib/mysql/ib_logfile0
-rw-rw---- 1 mysql mysql 4294967296 Jan 16 22:53 /var/lib/mysql/ib_logfile1

When I login to mysql, I see this Welcome message:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 177401833
Server version: 5.1.45-51-log Percona SQL Server (GPL), XtraDB 10

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

So, Percona Server 5.1.45 lets you make huge InnoDB Transaction Logs. SO, I am very sure Percona Server 5.5 (which uses XtraDB) will let you create InnoDB Log File > 2GB.
RECOMMENDATION
If you really need log files that big (> 2G), please upgrade to Percona Server with the XtraDB storage engine. It is an excellent drop-in replacement for InnoDB.
WARNING
The larger the log files, the longer the shutdown process may become. I would recommend the following:
[mysqld]
innodb_fast_shutdown=0

That way, any uncommitted transactions left in ibdata1 get committed upon shutdown rather than upon MySQL's crash recovery during startup.
UPDATE 2013-01-17 11:40 EDT
From your last two comments, your question to me is

What is the downside of having innodb_log_file_size too low for the load - less than 1 hours worth?

I would say frequent checkpoints having to be written to disk. That could make it very annoying for InnoDB to handle MVCC for Transaction Isolation.
You could properly size the InnoDB Log files for whatever transaction rate you peak. I wrote about this before : Proper tuning for 30GB InnoDB table on server with 48GB RAM (under the Section Log File Size). Here is an excerpt from my earlier post:
Log File Size
5MB is the default size for innodb_log_file_size. Percona's mysqlperformanceblog.com gave two good articles on computing the right size for your particular MySQL instance:

http://www.mysqlperformanceblog.com/2006/07/03/choosing-proper-innodb_log_file_size/
http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/

Basically, the blog recommends measuring how many bytes are written to the InnoDB Log Files in one hour. This is what I run to figure that out
SET @TimeInterval = 3600;
SELECT variable_value INTO @num1 FROM information_schema.global_status
WHERE variable_name = 'Innodb_os_log_written';
SELECT SLEEP(@TimeInterval);
SELECT variable_value INTO @num2 FROM information_schema.global_status
WHERE variable_name = 'Innodb_os_log_written';
SET @ByteWrittenToLog = @num2 - @num1;
SET @KB_WL_HR = @ByteWrittenToLog / POWER(1024,1) * 3600 / @TimeInterval;
SET @MB_WL_HR = @ByteWrittenToLog / POWER(1024,2) * 3600 / @TimeInterval;
SET @GB_WL_HR = @ByteWrittenToLog / POWER(1024,3) * 3600 / @TimeInterval;
SELECT @KB_WL_HR,@MB_WL_HR,@GB_WL_HR;

Whatever number comes back for @MB_WL_HR, take half of it and resize innodb_log_file_size to it : See my post How to safely change MySQL innodb variable 'innodb_log_file_size'?


Does having no brackets at "WHERE" produce same results as having brackets? Does having no brackets at "WHERE" produce same results as having brackets? I have same result, just wanted to confirm, because from my memory it always best to use rounded brackets.

SELECT `document`.*, `document_info`.`name`
FROM `document`
LEFT JOIN `document_info` ON `document_info`.`document_id` = `document`.`id`
WHERE `document`.`user_id` = 4358 AND document.status = 4 OR document_info.status > 0

vs

SELECT `document`.*, `document_info`.`name`
FROM `document`
LEFT JOIN `document_info` ON `document_info`.`document_id` = `document`.`id`
WHERE `document`.`user_id` = 4358 AND (document.status = 4 OR document_info.status > 0)

In the order of operations, AND is always evaluated first. So in this case it is the same as:

WHERE (`document`.`user_id` = 4358 AND document.status = 4) OR document_info.status > 0

It is always best practice to just use parentheses anyways. Even if the logic works out the way you want it to, it is much easier to read if you use them (and also properly placed indentation).


Page Writes too high but normal values on PLE and BCHR? Today I was just taking a look on my performance counters and found this...

Average Batch Request per second was 0,6
Average Page Reads per second was 12,5
Average Page Writes per second was ~15000
Average Checkpoint Pages per second was ~15000
Average Page Splits per second was 325

Buffer Cache hit ratio at 99%

Page Life Expectancy at 37256

After searching a bit I found that more than 90 Page Reads and Writes per second could indicate memory pressure but how Page Writes could be an indicator of memory pressure if for example I'm loading data in tables constantly?

Thanks!

Page write is just an indicator of logical writes as well as physical writes.
Logical write is an operation that happens when the page is being written in the buffer(dirty buffer) and physical write is an operation that happens when the same dirty buffer is converted to clean buffer and is being written on the physical disk.

Definitely, this is not a single metric to judge memory pressure. You need to combine different metrics for coming to a conclusion.

But in your case, it doesn't seem to be a problem as your page life expectancy is quite high which indicates a page will remain in the buffer for around 37k secs.
So you don't need to worry about memory pressure I think.


insert ... returning where X hi i have an issue im trying to solve using the following to do a insert statement

begin;
with t1 as (values (2),(3),(4))

,in1 as (insert into table123 (id,val,num)
select nextval('table123_id_seq'),t1.val,42
from t1
on conflict do nothing returning *)

,ins2 as (insert into table456 (id,num,fkey_id)
select nextval('table234_id_seq'),333, (select table123.id from table123 where val = 2)
from in1
on conflict do nothing returning *)
;commit;

what I get is 3 entries into table456 where I want only one

is there a clever way to do returning where X or something similar to avoid the duplication?

currently i workround just divide the insert through 2 queries ins1a with val=2 and ins1b with t1.v

There's an awful lot to be said for keeping things simple:

begin;

insert into table123 ( id, val, num ) values ( nextval('table123seq'), 2, 42 );
insert into table123 ( id, val, num ) values ( nextval('table123seq'), 3, 42 );
insert into table123 ( id, val, num ) values ( nextval('table123seq'), 4, 42 );

insert into table456 ( id, num, fkey_id )
   select next( 'table546seq' ), 333, id
   from table123
   where table213.num = 2 ;

select * from table456 ;
commit ;

Alternatively, make your id columns auto-increment and forget all about them entirely.

If that's not what you're after, then a plain-English description of the Problem you're trying to solve - not the difficulties with your current attempt at solving it - would be a good place to start.


Writing a simple SELECT Stored Procedure in Oracle PL/SQL This is my SQL Server Stored Procedure which is below:

CREATE PROCEDURE passenger_details
AS
BEGIN
SELECT Full_Name, Age, Nationality, Category, Airline_Name, Class_Type
FROM Passenger, Ticket, Airline, Class
WHERE Passenger.Passenger_No=Ticket.Passenger_No AND Airline.Airline_No=Ticket.Airline_No AND Class.Class_No=Ticket.Class_No
END

EXECUTE passenger_details

The above stored procedure in SQL Server works successfully.

Then I tried to execute the same stored procedure in Oracle PL/SQL which is shown below:

CREATE OR REPLACE PROCEDURE passenger_details
(p_passenger_details OUT SYS_REFCURSOR)
AS
BEGIN
OPEN p_passenger_details FOR
SELECT Full_Name, Age, Nationality, Category, Airline_Name, Class_Type
FROM Passenger, Ticket, Airline, Class
WHERE Passenger.Passenger_No=Ticket.Passenger_No AND Airline.Airline_No=Ticket.Airline_No AND Class.Class_No=Ticket.Class_No;
END passenger_details;

The above stored procedure in Oracle PL/SQL is compiled successfully.

Then I tried to execute it which is shown below:

SET SERVEROUTPUT ON;
EXECUTE passenger_details;

While trying to execute the stored procedure, I'm getting the following error message which is shown below:

Error starting at line : 12 in command -
BEGIN passenger_details; END;
Error report -
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'PASSENGER_DETAILS'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

You need to pass in a REFCURSOR for the procedure to use as its output (OUT) parameter.

Quick procedure to test with:

CREATE OR REPLACE PROCEDURE passenger_details
(p_passenger_details OUT SYS_REFCURSOR)
AS
BEGIN
OPEN p_passenger_details FOR
SELECT 'test test' as Full_Name, 10 as Age, 'alien' as Nationality, 'foo' as Category,
'Name' as Airline_Name, 'wobble' as Class_Type
FROM dual;
END passenger_details;
/

Test in SQL*Plus:

SQL> variable mycursor refcursor;
SQL> exec passenger_details ( :mycursor );

PL/SQL procedure successfully completed.

SQL> print mycursor;

FULL_NAME    AGE NATIO CAT AIRL CLASS_
--------- ---------- ----- --- ---- ------
test test     10 alien foo Name wobble

SQL>


Search a string across all columns of a query result I have a query, which has around 50 columns and it is subject to change.

I need to modify my SQL-DML to search a specific string across all 50 columns.

How is this possible?

select
  *
from
  messages a
  FULL OUTER JOIN  customers b ON a.customer_id = b.customer_id
  FULL OUTER JOIN orders c ON c.customer_id = b.customer_id
  FULL OUTER JOIN delivery_templates d ON c.billing_template_id = d.delivery_template_id

where
  product_id != '0'
  and a.hide_question = '0'
  and a.seller_id in ('2', '3', '1', '6')
  AND a.market_place_id IN (
    SELECT
      MAX(market_place_id)
    FROM
      messages
    GROUP BY
      product_id
  )
order by
  a.market_place_id desc

So far the best alternative seem to be

SELECT *
FROM MyTable
WHERE Col1 LIKE '%foo%' OR
      Col2 LIKE '%foo%' OR
      Col3 LIKE '%foo%' OR
      Col4 LIKE '%foo%' OR
      Col5 LIKE '%foo%' OR
      Col6 LIKE '%foo%'

A database is not a fancy mechanism to grep. This is a horrible idea don't do this ever. That said you can convert the row object to a tsvector using Full Text Search

SELECT to_tsvector(to_jsonb(t)) @@ 'bar'
FROM ( VALUES ('foo','bar','baz') ) AS t(a,b,c);

or you can search it as if it was text.

SELECT t::text LIKE '%bar%'
FROM ( VALUES ('foo','bar','baz') ) AS t(a,b,c);


Database design for a blogging site I would like to know if this is a good database for a blog:

This is a data model for a blog system that has the following characteristics:

Users can register
Users can have different roles, eg. author, administrator, commentator
Blog posts can be established with eg. headline, content, picture
Blog posts can belong to a number of categories
Blog posts can be marked with a number of tags
Blog posts can have different states, eg. drafts, published, archived, hidden

The data model (the design) has to include:

Entities with specification of attributes and keys
Relations with specification of cardinality and participation

This is for a school project, I am a beginner.

I have a few observations and suggestions:

Do not create a DATE table when all you need is an element (column) for the date of the POST.  Generally speaking, a table with nothing but a meaningless ID and one other column is a good indication that you need to look hard at whether all you really need is the non-key column.

Consider CATEGORY, this also has just its ID and the category description.  Is this a good candidate for an independent table?  There are two things to think about:

Are there likely to be other attributes that depend on the category?  In other words, should category be normalized out of posts?
Is it possible that categories could exist that have zero posts?  In other words, is the category list something that needs to be managed by an administrator or someone other than the person who creates posts?

On another subject:
Your model indicates that a POST can be written by many USERs.  I'm not sure that this is really possible in your system, since you don't say anything about collaborative authorship.  However, one thing that is almost certainly a problem is that each user can only ever author one post.  I'm sure that's not what you meant.

To fix this you either need to move the foreign key in the relationship between USER and POST to the post side or you need to create an intersection entity if posts can have multiple authors.

Similarly...
Your business rules state that posts can have multiple CATEGORY and TAG assignments, however, your model doesn't reflect this.  In your model each post can have exactly one of each of these.

To fix this you need to implement a many-to-many relationship for each of tags and categories.  This will result in an intersection table in each case.

Lastly:
Why is some of your user information segregated into another table with a many-to-many relationship to the main user table?  There may be good reasons to keep password information in a separate table from other user information, but I don't see any reason stated in your business rules.  Also, it isn't likely that password will be many-to-many with your user record.


SQL Server 2008R2 - how to install replication components I am trying to install the Replication components for SQL Server 2008R2, on technet website it says:

On the computer that is running IIS, insert the SQL Server 2008 R2, SQL Server 2008, or SQL Server 2005 CD and run Setup.exe.
In the SQL Server Installation Wizard, on the Components to Install page, click Advanced.
On the Feature Selection page, click Client Components, and then click Connectivity Components to install the SQL Server replication components

But the CD is lost, is there anyway to download the components or the setup files online?

Select @@version

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

You might get away with using the eval ISO.

Try download that, burn it to a disk (or mount it by other means) and see if it will cooperate. It'll certainly contain everything, assuming it is as happy to be used to update your licensed install as it is an eval one.

EDit: I've removed the link as it was incorrect. It looks from trying to search on their site that SQL 2008r2 eval isn't available direct from MS any more. You might be able to find it elsewhere though.


Execute a series of commands in PSQL I am looking for a solution to execute multiple commands in PSQL.

Lets say I have a file with 2 name for database.

1. slave1
2. slave2

I already have a master template named "master_db".

If I would do it manually, I will do the following:

CREATE DATABASE slave1 TEMPLATE master_db;
CREATE DATABASE slave2 TEMPLATE master_db;

If there were only 2, it would be very easy but in my case there are more than 30, hence I would like to know if we can create a script. I did try to search but couldn't find a solution.

The databases are in the same cluster, if that matters.

If you are using psql 9.6+, there is a very convenient command called \gexec:

  Sends the current query input buffer to the server, then treats each
  column of each row of the query's output (if any) as a SQL statement
  to be executed. [...] The generated queries are executed in the order
  in which the rows are returned, and left-to-right within each row if
  there is more than one column. NULL fields are ignored. The generated
  queries are sent literally to the server for processing, so they
  cannot be psql meta-commands nor contain psql variable references.

So, your script could look like:

SELECT format('create database slave%s template master_db', l_num)
  FROM generate_series(1, 30) t(l_num);

\gexec

Note that you can use the newer psql version, even if the server version is lower.  There might be some rare cases of incompatibility (when using \d* commands, because some system views may change between server versions), but I haven't yet seen such a case.

If you are still stuck on an earlier client version, you can just use the SELECT and copy the output manually to a script file.

If your database names are in a file, you have multiple options.  Either treat that file with an editor (something like sed -i -e 's/^/CREATE\ DATABASE\ /' -e 's/$/\ TEMPLATE\ master_db' in any OS where sed exists), and then run the resulting script.

Alternatively, you can also do something like this:

CREATE TEMPORARY TABLE db_names (db text);

\copy db_names FROM 'path/to/your/file'

SELECT format('create database slave%s template master_db', db)
  FROM db_names;

\gexec

Important note: only use \gexec when you absolutely trust the source you are working with.  The above approach is prone to SQL injection (which, interestingly, not so easy when one of the commands on the same line is CREATE DATABASE, but it's always better to be careful than sorry later).


Refresh a PostgreSQL materialized view automatically without using triggers I created a materialized view named view_table_A on a foreign data wrapper table table_A. What I want is for the view to be automatically updated after every new insert in table_A. I tried to do this with triggers but it didn't work.

Is it possible to refresh a materialized view automatically without using triggers?

As a_horse_with_no_name said in a comment:

  No, that's not possible. You need some kind of scheduler that runs refresh materialized view e.g. pg_cron or something on the operating system level – a_horse_with_no_name

Alternatively, if you need a MATERIALIZED VIEW that refreshes when you run SELECT, just remove MATERIALIZED and use a regular VIEW. Materialization only adds a periodic cache. It's only needed when the query itself is prohibitively slow or hot.


Can the Data Export Service in Microsoft Dynamics export data to an Azure Synapse Data Warehouse? I am able to use the Data Export Service (DES) to export data to an Azure SQL Database. When I try to create a new profile in DES that connects to a Data Warehouse (aka Azure Synapse Analytics) on the same Azure SQL Server using the same user (db_owner member), I keep getting the message 'Error validating profile registration'. Note that the Key Vault Secrets used for each connection are virtually the same except the 'Initial Catalog' and the DES wizard is able to validate the connection to the Key Vault.

The answer is no. The reason is that an Azure Synapse Data Warehouse does not allow you to create user data types and DES creates data types to use in it's stored procedures that help merge the incremental data into the SQL tables.


Finding opening and closing price using sqlite GROUP BY statement (accessing main query alias in subquery) I want to preface this post by saying that I am a SQL(ite) newbie. My main goal is to find the opening/closing/high/low/average prices of each 5 minute interval of these stock prices. I can get the high/low working with min/max and the group by query but I am running into problems with the opening/closing. My database looks something like this

SYMBOL | DATETIME           |PRICE
AAPL   | 1999-06-01 09:30:01|45.0
AAPL   | 1999-06-01 09:30:03|44.9375
AAPL   | 1999-06-01 09:30:04|44.9375
AAPL   | 1999-06-01 09:30:05|44.9375
AAPL   | 1999-06-01 09:30:06|44.9375
AAPL   | 1999-06-01 09:30:07|45.0
AAPL   | 1999-06-01 09:30:08|45.0
AAPL   | 1999-06-01 09:30:09|44.9375
AAPL   | 1999-06-01 09:30:11|45.0
AAPL   | 1999-06-01 09:30:12|44.96875

The following query

SELECT SYMBOL, MIN(DATETIME) AS MINDT, MAX(DATETIME) AS MAXDT, AVG(PRICE)
FROM stocks
WHERE DATETIME < '1999-06-02 00:00:00'
GROUP BY strftime('%s', DATETIME)/(60*5) LIMIT 4;

results in

AAPL|1999-06-01 09:30:01|1999-06-01 09:34:41|44.9269724822695
AAPL|1999-06-01 09:35:22|1999-06-01 09:39:58|44.8615196078431
AAPL|1999-06-01 09:40:03|1999-06-01 09:44:58|44.9800531914894
AAPL|1999-06-01 09:45:05|1999-06-01 09:49:52|44.9397321428571

whereas

SELECT SYMBOL, MIN(DATETIME) AS MINDT, MAX(DATETIME) AS MAXDT, AVG(PRICE),
(SELECT PRICE FROM stocks WHERE DATETIME=MINDT),
(SELECT PRICE FROM stocks WHERE DATETIME=MAXDT)
FROM stocks
WHERE DATETIME < '1999-06-02 00:00:00'
GROUP BY strftime('%s', DATETIME)/(60*5);

throws this error in SQL

Error: near "MINDT": syntax error

and in R

Error in sqliteSendQuery(con, statement, bind.data) :
  error in statement: no such column: MINDT

I have googled around and I think I found what is wrong with my query: you can not acces an alias made in main query in a sub query (i.e. reference MINDT in the subquery). But my I lack SQL knowledge to find a fix to this. Is there anyone that could help me out here?

You first query is OK although not entirely correct.

You need to be careful with GROUP BY and not have any column in the SELECT list that is not also in the GROUP BY list. So, add the symbol in the group by list.
Next, the LIMIT needs an ORDER BY so you are 100% certain that you get always the same results. SQLite (and many other DBMS) may return the same results all the time but that "all the time" is not guaranteed. You never know when you'll get a different execution plan and weird result.
If you want more aggregate results (like minimum or maximum price) over the windows, you can add them in the list.

The query becomes:

SELECT symbol,
       MIN(datetime) AS mindt,
       MAX(datetime) AS maxdt,
       AVG(price)    AS avg_price,
       MIN(price)    AS min_price,
       MAX(price)    AS max_price
FROM stocks
WHERE datetime < '1999-06-02 00:00:00'
GROUP BY symbol, strftime('%s', DATETIME)/(60*5)
ORDER BY symbol, strftime('%s', DATETIME)/(60*5)
LIMIT 4 ;

Now to get also the first and the last price ("opening" and "closing" in finance terminology) in these 5-minutes windows, you'll either need window functions or correlated subqueries or to wrap this query in a derived table and then join again to the original table. I'll write the 3rd option.

It assumes there is a UNIQUE constraint on (symbol, datetime) or (even without a constraint) that you don't have 2 rows with same symbol and datetime:

SELECT
    s.*,
    s1.price AS opening_price,
    s2.price AS closing_price
FROM
  ( SELECT symbol,
           MIN(datetime) AS mindt,
           MAX(datetime) AS maxdt,
           AVG(price)    AS avg_price,
           MIN(price)    AS min_price,
           MAX(price)    AS max_price
    FROM stocks
    WHERE datetime < '1999-06-02 00:00:00'
    GROUP BY symbol, strftime('%s', DATETIME)/(60*5)
    ORDER BY symbol, strftime('%s', DATETIME)/(60*5)
    LIMIT 4
  ) AS s
  LEFT JOIN
    stocks AS s1
      ON  s.symbol = s1.symbol
      AND s.mindt  = s1.datetime
  LEFT JOIN
    stocks AS s2
      ON  s.symbol = s2.symbol
      AND s.maxdt  = s2.datetime ;


How to force SQL developer to use imp/exp command while import/export While importing or exporting oracle data dump using SQL Developer by default it is using impdp command for import and expdp command for export. But I want to import a dump which is exported using exp command, and for that i need to force SQL developer to use imp command instead of impdp command. And same for export.

Pleas help me if anyone knows how to in-force SQL developer to use imp for import instead of impdp and exp for export instead of expdp

Not going to happen

The Original exp utilitie became DESUPPORTED as of 11.1.  Additionally, the 11.1 version of the database is no longer supported either.  SQL Developer isn't going to be "patched" to support unsupported Database version or unsupported database features.

For more information you can read the Compatibility Matrix for exp/imp which has Oracle Doc ID 132904.1 NOTE : You must have a valid support contract (and account) to access the Document. (copy+pasting information is against Oracle's term of use)

The imp utility seems to still be around for importing old exports.

Workaround

Learn to use the command line.


Ensuring Postgresql BDR replication My understanding, reading between the lines, is that BDR replicates row by row, starting after the COMMIT.  I have a requirement to be able to then wait until some nodes have actually replicated my latest commit.  If not, I will report the problem upstream.  And maybe mark the transaction as dubious on the local db, which will hopefully also get replicated.

bdr.bdr_node_slots seems to tell me a pg_lsn of the latest replication of each node.  But I need to know the pg_lsn of my last transaction.  Knowing the current state of the db now that transaction committed would be close enough.

I could only find pg_current_xlog_insert_location(), but it looks different.

My understanding, reading between the lines, is that BDR replicates row by row, starting after the COMMIT.

Right - but it replicates transactions with the same transaction boundaries as on the original node. So they only become visible at commit time on the downstream.

e.g.

BEGIN;
INSERT INTO x (a) VALUES (1), (2), (3);
INSERT INTO x (a) VALUES (4);
COMMIT;
BEGIN;
INSERT INTO x (a) VALUES (42);
COMMIT;

replicates as

BEGIN;
INSERT INTO x (a) VALUES (1);
INSERT INTO x (a) VALUES (2);
INSERT INTO x (a) VALUES (3);
INSERT INTO x (a) VALUES (4);
COMMIT;
BEGIN;
INSERT INTO x (a) VALUES (42);
COMMIT;

Note, however, that conflict resolution is row-by-row. So if your downstream already had a row with a = 4, and a was the PRIMARY KEY, that INSERT would get ignored but the rest would proceed normally, and the whole xact would commit as an insert of 1, 2, and 4.

  But I need to know the pg_lsn of my last transaction.

Unfortunately, PostgreSQL does not report commit LSNs. I've tried to get optional support for reporting them at commit time, but without success so far. Doing it "right" requires extending the wire protocol, and a few people objected to the backward-compatible way of doing so that I proposed.

The best you can do right now is wait until replay has passed your commit, by doing:

BEGIN;
... do stuff ...;
COMMIT;
SELECT pg_current_xlog_insert_location();

and waiting until replication passes that point. But since this may contain other unimportant work after your commit by the time you call it, and the walsender doesn't rush to replicate unimportant bookkeeping to peers, sometimes it can take a while to pass that LSN. Not usually a problem in production, but can cause stalls in tests and small scale setups.

There's not really an alternative right now though. You can perform a dummy xact after the real one you're interested in to make sure you replicate immediately, at the cost of burning xids, but it must be a real write xact otherwise it'll be silently discarded.


How to left join and count two tables where the rows (strings) are different PostgreSQL 9.5.6
I have two tables - t1, t2.
t1 has a title column with titles separated by spaces.
t1.title
--------
hello kitty workd
gone wind forever

t2 has a path column with paths separated by dashes and prefixed 'path/'
t2.path
-------
path/hello-kitty-world
path/gone-wind-forever

GOAL: FOR EACH t1.title COUNT THE NUMBER OF TIMES IT SHOWS UP IN t2
Course of action: parse t2.path so that it looks like t1.title in order to do a left join and count**
I'm very new to this, if you have a better approach altogether I would appreciate any suggestions
So far I have:
select t1.title, count(t2.path) as num
from t1 left join t2 on t1.title = substring(log.path from 6)
where t1.title like '%'||split_part(substring(log.path from 6),'-',1)||'%'
group by articles.title;

@ypercube said in question comments:

You don't need to split the strings. If there is always a single space between word, you can replace space with - and then join: ON 'path/' || replace(t1.title, ' ', '-') = t2.path
Or the reverse:  ON t1.title = replace(substring(t2.path, 6), '-', '')

You can use replace() function in the ON clause to compare both strings and get the desired result:

select     title, path
from       t1
inner join t2
on         'path/' || replace(t1.title, ' ', '-') = t2.path;

title             | path
:---------------- | :---------------------
gone wind forever | path/gone-wind-forever

select     title, path
from       t1
inner join t2
on         t1.title = replace(substring(t2.path, 6), '-', ' ');

title             | path
:---------------- | :---------------------
gone wind forever | path/gone-wind-forever

dbfiddle here


View with parameter IN I'm creating a view in which I have a where with an in clause and I want it to be a parameter. Unfortunately views can't be created with parameters, so I tried replacing it with functions, but functions only return a string.

Is there a way to create a view that accepts multiple options? example:

SELECT name, surname from people WHERE name in (@names)

I'm using MySQL 5.5

oNare's answer shows the principle way to achieve this. (He gets a +1 !!!)
He set the variable @names as follows:
SET @names="'oNare','Another'";

Then, his algorithm worked perfectly.
SUGGESTION
Instead of putting single quotes in @names, do it without the single quotes.
Do the delimiting before processing the query with this
SET @names='oNare,Another';
SET @names=CONCAT('''',REPLACE(REPLACE(@names,'''',''''''),',',''','''),'''');

What does @names look like now ???
mysql> SELECT @names;
+-------------------+
| @names            |
+-------------------+
| 'oNare','Another' |
+-------------------+
1 row in set (0.00 sec)

mysql>

I loaded his sample data and tried it out
mysql> SET @names='oNare,Another';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @names=CONCAT('''',REPLACE(REPLACE(@namelist,'''',''''''),',',''','''),'''');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @names;
+-------------------+
| @names            |
+-------------------+
| 'oNare','Another' |
+-------------------+
1 row in set (0.01 sec)

mysql> SET @query=CONCAT("SELECT
    ">                             fName,
    ">                             lName
    ">                         FROM user
    ">                         WHERE fName IN (",@names,")");
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt FROM @query;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE stmt;
+---------+-------+
| fName   | lName |
+---------+-------+
| oNare   | Test  |
| Another | Test  |
+---------+-------+
2 rows in set (0.00 sec)

mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)


INSERT statements in a transaction and locking a range of rows I have a table that is concurrently accessed by many users.

Typically, INSERTs to the table are made one at a time.  Occassionaly, users will add a "batch" of rows at the same time.  The batch is usually small, perhaps 30-50 rows.

The Identity for these rows that are added in batches must be consecutive, in order to match up with an external process that prints receipts for the added range.

I have a stored procedure that does the INSERTs.  It has a transaction wrapped around the INSERT INTO... statements.

This is essentially how the stored procedure is designed:

BEGIN TRANSACTION
WHILE @loopCounter <= @ReceiptCount
 BEGIN
  SELECT  @pinNbr =  CAST(RAND() * 1000 AS INT);
  INSERT INTO MyTable (values)
  SELECT @insertedId = @@Identity;
  UPDATE MyTable SET externalId = CONVERT(VARCHAR,@pinNbr), 3)
         + RIGHT('000000000' + CONVERT(VARCHAR,@insertedId), 9);
  SET @loopCounter = @loopCounter + 1;
 END
COMMIT TRAN

What is happening, is that on occasion, some other users' typical INSERT process will insert a single row into the middle of the batch, messing up the consecutive range of IDs required.

I thought the transaction would lock the table and prevent this from happening.

How do I prevent the table from being updated while the batch process is adding records?

If you want to prevent messing up the sequence, you can lock the tables involved when you are doing the inserts. You will have to assess whether the duration of this locking is acceptable or not.

The command would be... Insert into table WITH (TABLOCK) (values).....

The scope of the transaction described in your question would not prevent external inserts from happening. Hence my suggestion of using table locks.