Encountered Error: ORA-00979: not a GROUP BY expression 00979. 00000 - "not a GROUP BY expression" I want to join 3 table together and display the output as below.But i encountered the error when i tried to use group by and order by together. Is there any solution can solve this error? Below is my Oracle query. Appreciate if someone could help on this error. Thank you.

The database data is like below:
This is my 1st table (RMSTMP_PNG.ota_activity_lotinfo):

       ID                           KEY               VALUE
AD9FBCCC49F06446E050280A2         Quantity            100
AD9FBCCC49F06446E050280A2        PackageLeads         BN1140
AD9FBCCC49F06446E050280A2        TestProgram          DL_FP5_SLT
AD9FBCCC49F06446E050280A2          TestRev            3.0.0
AD9FBCCC49F06446E050280A2           IDV               9651AA43

Second Table as below (RMSTMP_PNG.ota_activity):
EQP_ID  LOT_ID   ACTIVITY  MODIFIED BY  MODIFIED DATE              ID
LAS43   9KA2909  LOAD_LOT  jratnasa     18-AUG-20       AD9FBCCC49F06446E050280A2

Third Table (RMSTMP_PNG.ota_activity_rescreen)
       ID                        RESCREEN TYPE
AD9FBCCC49F06446E050280A2           FRESH

The output i want is group all same ID as below and display in gridview but i am not sure is it possible to do that?

The final output should be like this:
EQP_ID   MODIFIED DATE         ID                            name
LAS43    18-AUG-20       AD9FBCCC49F06446E050280A2        Quantity:100,
                                                          PackageLeads:BN1140,
                                                          TestProgram:DL_FP5_SLT,
                                                          TestRev:3.0.0,
                                                          IDV:9651AA43

i come up with this query, but seem like it does not working.

SELECT RMSTMP_PNG.ota_activity.EQP_ID,
       RMSTMP_PNG.ota_activity.MODIFIED_DATE,
       RMSTMP_PNG.ota_activity_lotinfo.ID,
       LISTAGG(RMSTMP_PNG.ota_activity_lotinfo.KEY)
              WITHIN GROUP (ORDER BY RMSTMP_PNG.ota_activity_lotinfo.KEY) as "names"
from RMSTMP_PNG.ota_activity
INNER JOIN RMSTMP_PNG.ota_activity_rescreen
    ON RMSTMP_PNG.ota_activity.ID = RMSTMP_PNG.ota_activity_rescreen.ID
INNER JOIN RMSTMP_PNG.ota_activity_lotinfo
    ON RMSTMP_PNG.ota_activity.ID = RMSTMP_PNG.ota_activity_lotinfo.ID
WHERE RMSTMP_PNG.ota_activity.MODIFIED_DATE BETWEEN'01-Jan-2020'AND '30-Sep-2020'
GROUP BY RMSTMP_PNG.ota_activity_lotinfo.ID,
         RMSTMP_PNG.ota_activity_lotinfo.KEY,
         RMSTMP_PNG.ota_activity.EQP_ID
order by RMSTMP_PNG.ota_activity.MODIFIED_DATE ASC

Try below. Check your SELECT and GROUP BY statements always with the ORA-00979: not a GROUP BY
Here some other usages of LISTAGG in case you need them.
  SELECT RMSTMP_PNG.ota_activity.EQP_ID,
         RMSTMP_PNG.ota_activity.MODIFIED_DATE,
         RMSTMP_PNG.ota_activity_lotinfo.ID,
         LISTAGG (RMSTMP_PNG.ota_activity_lotinfo.KEY)
            WITHIN GROUP (ORDER BY RMSTMP_PNG.ota_activity_lotinfo.KEY)
            AS "names"
    FROM RMSTMP_PNG.ota_activity
         INNER JOIN RMSTMP_PNG.ota_activity_rescreen
            ON RMSTMP_PNG.ota_activity.ID = RMSTMP_PNG.ota_activity_rescreen.ID
         INNER JOIN RMSTMP_PNG.ota_activity_lotinfo
            ON RMSTMP_PNG.ota_activity.ID = RMSTMP_PNG.ota_activity_lotinfo.ID
   WHERE RMSTMP_PNG.ota_activity.MODIFIED_DATE BETWEEN '01-Jan-2020'
                                                   AND '30-Sep-2020'
GROUP BY RMSTMP_PNG.ota_activity_lotinfo.ID,
         RMSTMP_PNG.ota_activity.MODIFIED_DATE,
         RMSTMP_PNG.ota_activity.EQP_ID
ORDER BY RMSTMP_PNG.ota_activity.MODIFIED_DATE ASC

As your intended output try below. I did not know what which id you need and where is the VALUE column comes from. I know you can manage though.
 SELECT RMSTMP_PNG.OTA_ACTIVITY.EQP_ID AS ID,
         LISTAGG (RMSTMP_PNG.OTA_ACTIVITY_LOTINFO.KEY || ':' || VALUE, ',')
            WITHIN GROUP (ORDER BY RMSTMP_PNG.OTA_ACTIVITY.MODIFIED_DATE)
            AS "names"
    FROM RMSTMP_PNG.OTA_ACTIVITY
         INNER JOIN RMSTMP_PNG.OTA_ACTIVITY_RESCREEN
            ON RMSTMP_PNG.OTA_ACTIVITY.ID = RMSTMP_PNG.OTA_ACTIVITY_RESCREEN.ID
         INNER JOIN RMSTMP_PNG.OTA_ACTIVITY_LOTINFO
            ON RMSTMP_PNG.OTA_ACTIVITY.ID = RMSTMP_PNG.OTA_ACTIVITY_LOTINFO.ID
   WHERE RMSTMP_PNG.OTA_ACTIVITY.MODIFIED_DATE BETWEEN '01-Jan-2020'
                                                   AND '30-Sep-2020'
GROUP BY RMSTMP_PNG.OTA_ACTIVITY_LOTINFO.ID
ORDER BY RMSTMP_PNG.OTA_ACTIVITY.MODIFIED_DATE


How do shrink *right* when required? I've been reading all the articles (and comments) linked from Stop Shrinking Your Database Files. Seriously. Now. I understand that shrinking is bad.

What I have a hard time figuring out is how to do it right when its actually required.

The backstory is that I have inherited this archive DB with a handful of tables containing PDF's that should have been expired after 5 years, but the procedures to do this on a schedule was never set up. I don't have the row counts at the moment but it's a few million, and about 3TB. I guesstimate about half of this will be deleted and never come back.

I've been talking to our infrastructure providers DBA, but I find the advice they give (delete, reindex then shrink) to be highly dubious. I'm a system consultant not a DBA, but it seems I get to play one this time :-/

Paul Randal recommends two approaches

1

  Create a new filegroup
  Move all affected tables and indexes into the new filegroup
  using the CREATE INDEX … WITH (DROP_EXISTING = ON) ON syntax, to move the
  tables and remove fragmentation from them at the same time
  Drop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)

Unfortunately I don't have access to the actual server just SSMS, and this is quite a bit outside my comfort zone anyway. I'm not actually a DBA..

2

  DBCC INDEXDEFRAG or ALTER INDEX … REORGANIZE.

Yes but how.. there are quite a few parameters to choose from, and this seems optimized to get the size absolutely minimal, and not what I need which is to get rid of the bloat while keeping the DB as healthy as possible.

In other words I don't care if the DB grows some, it needs space to operate and it will grow some as document production increases year by year, but the nightly, weekly or monthly purging will now keep this in check.

I should also add that I can take this DB offline at night without much trouble.

The recommendation from your Providers DBA is sufficient for your needs.  I would add the additional step of checking to make sure you don't have any heaps, however.  When you delete records from Heaps the space is not always made available and you won't have a good way of reorganizing it.  If you can, add a clustered index to the heap before deleting (or after, dealers choice).  If you cannot, I would delete, add a clustered index and then remove it.

SELECT T.name AS TableName
    , I.* FROM sys.indexes AS I
    INNER JOIN sys.tables AS T ON T.object_id = I.object_id
WHERE I.type_desc = 'HEAP'

So, my steps would be:

Check for Heaps and decide on course of action.
purge data (preferably in chunks), check-pointing or backing up the log as
applicable.
defragment indexes (Ola Hallengren's scripts will help
here if you don't have your own).  Do this first so that pages can be readjusted to the correct fill factor.  Otherwise, space used estimate will be off since a partially used page is counted as a full page and you will probably have lots of partially full pages that can be consolidated without impact.
Look at current database file size
used.  Shrink to appropriate size (I like to leave at least 20% empty
space in the file.
Defragment your indexes again since the shrink moved everything around.
Take the time to evaluate file growth settings
to make sure they make sense for your environment.  Usually growing
in fixed size rather than as a percentage and make sure you have
instant file initialization turned on.

This will tell you how much space is actually being used in the file.

SELECT DB_NAME() AS DatabaseName
, F.name
, F.type_desc
, F.state_desc
, F.file_id
, F.growth
, F.max_size
, F.is_percent_growth
, F.physical_name
, F.size/128.0 AS CurrentSize_MB
, FILEPROPERTY(F.name, 'SpaceUsed')/128.0 AS SpaceUsed_MB
 FROM sys.database_files F


Save dataset within another table best practice I want to save one dataset within another table, this dataset should be persistent.

For example

Table1: FIELDS (A,B,C)
Table2: FIELDS (D, DuplicateTable1)

I can think of 2 possiblities:

Have Fields A,B,C in Table2
Make an invisible copy (for example by adding a "hidden" Field in table1) and us it in Table2 as Foreign Key.

I feel both are not very clean. How would you solve this problem ?

Edit: More Specific example:

CREATE TABLE `Bill` (
    `billId` INT(11) NOT NULL AUTO_INCREMENT,
    `to` INT(11) NOT NULL,
    `value` INT(11) NOT NULL,
    PRIMARY KEY (`billId`),
    INDEX `PersonKey` (`to`),
    CONSTRAINT `PersonKey` FOREIGN KEY (`to`) REFERENCES `Person` (`personId`)
)
ENGINE=InnoDB;

CREATE TABLE `Person` (
    `personId` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NULL DEFAULT NULL,
    `address` VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY (`personId`)
)
ENGINE=InnoDB;

In this example the problem is that the bill shouldn't change when the person changes name or address. And I'm looking for a nice way to fix this. I could add all my fields in Person to my Bill table (for example Bill.person_address). But if I add a new field to Person i would also have to change the structure of Table (wich makes the software less scaleable). Or the other way i could think off, is adding a new field "locked" to Person like

`locked` TINYINT(1) NULL

And when i save a bill I copy the row from Person with locked = 1, then i would change the application that all the locked persons don't show up anywhere else.

If I understand well, you need the bill to still have the person's data when it was created. And if the person changed it's data, like address, you need the bill to keep having the original address where that person was billed.

In other words, you will have different 'versions' of the person.
What I would is the following:

Divide person table into two tables with master-details (or one to many) relationship. In the master table, have the fields that do not change, like ID, Name. In the details table, have a primary key field and the ID from master and other fields that may change, like address.

Person(ID primary key, name , ...)
Person_det(ID PK, Person_id referencing person.id, address, ...)

'Bill' table now would have "person_det.id" as a foreign key, instead of person.id.


sp_testlinkedserver output verbose I've built a script/Agent job that goes and polls our linked servers every 10 minutes to make sure they are online.  Now, when one goes offline, It generates an email and notifies me. See below:

Now, right now it just uses a pass/fail.  What I want to do is, when communication between servers goes offline, to generate not only that it's offline, but also the reason why.  Similar to the read out you get from SSMS when you test a linked server and it's offline:

Is this possible?  I've searched for any additional verbose parameters that may help me accomplish this but haven't been able to find any.  The information gets generated in SSMS so that leads me to believe that there is some way to get it, I'm just not sure how.

Thanks as usual guys!

UPDATED
Had to add some system specific functionality to this as well as my own SQL that I had already built but error reporting is online.  Works with our SQL servers and our iSeries/400 environments.  Here's what it spits out now:

Thanks again for all the help on this one.  Might have to post this out to SSC so others can use it.

As with the linked answer, you should be able to do this by using TRY/CATCH; the catch (no pun intended) with the other answer is that, because the name of the linked server is validated at compile time, in the event the test fails, the batch doesn't execute. So instead, you can put the linked server test inside dynamic SQL:

DECLARE @srv SYSNAME = N'linked_server_name';

BEGIN TRY
  EXEC master.sys.sp_executesql
      N'EXEC master.sys.sp_testlinkedserver @srv;',
      N'@srv SYSNAME', @srv;
END TRY
BEGIN CATCH
  SELECT ERROR_NUMBER(), '|', ERROR_MESSAGE();
END CATCH

This should allow you to capture a variety of errors, including:

If @srv is not populated:

  214 | Procedure expects parameter '@servername' of type 'sysname'.

If @srv is not a defined linked server:

  7202 | Could not find server 'whatever' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

If a connection to @srv failed at the time of linked server creation:

  7303 | Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "whatever".

If a connection to @srv1 times out (e.g. the server is offline) - this was using a local linked server, so the text may be a bit different when using named pipes or TCP/IP:

  2 | Shared Memory Provider: Could not open a connection to SQL Server [2].

(With TCP/IP, Kin says the error message is:

  TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.

If @srv is not configured for data access:

  7411 | Server 'whatever' is not configured for DATA ACCESS.

If the linked server was configured with the wrong username/password (or passes the current credentials and they fail):

  18456 | Login failed for user 'foo'.

You can also start the batch with EXECUTE AS if you are trying to test the linked server under specific credentials.

If the test of the linked server succeeds, you could continue inside the TRY block to do other things, like OPENQUERY, queries using 4-part names, etc., and there should be a whole slew of other errors you could capture and report on verbosely...


SQL Log Messages - FILESTREAM and xp_cmdshell I was perusing the SQL logs today and found the following:

  FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.

  Configuration option 'xp_cmdshell' changed from 1 to 0. Run the RECONFIGURE statement to install.

These messages are peppered throughout every day during the past week, sometimes in blocks of 30+.  Also, when they happen, they appear in blocks 10-20 seconds.  I don't really see this as many times in the other servers I comb.  Is this anything to be alarmed about?

Really not sure what else aside from PBM might be set up to try so forcibly to disable xp_cmdshell, but it definitely sounds like something set up by IT in some way. I don't think this is really causing any problems, so you could just wait until the rest of your team is available and bring it up to them. If you want to get more information about it, you could set up a server-side trace that captures all calls to sp_configure - this will at least tell you the host name, application name, user name etc.

declare @rc int, @TraceID int, @maxsize BIGINT = 5;

exec @rc = sp_trace_create @TraceID output, 0, N'C:\myfolder\mytrace', @maxsize, NULL
if (@rc != 0) goto error

declare @on bit = 1;
exec sp_trace_setevent @TraceID, 42, 1, @on;
exec sp_trace_setevent @TraceID, 42, 6, @on;
exec sp_trace_setevent @TraceID, 42, 7, @on;
exec sp_trace_setevent @TraceID, 42, 8, @on;
exec sp_trace_setevent @TraceID, 42, 10, @on;
exec sp_trace_setevent @TraceID, 42, 11, @on;
exec sp_trace_setevent @TraceID, 42, 14, @on;
exec sp_trace_setevent @TraceID, 42, 34, @on;
exec sp_trace_setevent @TraceID, 42, 41, @on;
exec sp_trace_setevent @TraceID, 42, 64, @on;

exec sp_trace_setfilter @TraceID, 34, 0, 6, N'sp_configure';
exec sp_trace_setstatus @TraceID, 1;
select TraceID=@TraceID;
goto finish

error:
select ErrorCode=@rc

finish:
go

Probably more event columns there than necessary, but it's not like you're going to be running this for a long time. You should be able to turn it off as soon as you see another message pop up in the log (because I doubt this is coming from multiple sources).


Cannot start recv'ing chunk, sharding is not enabled and no config server was provided I config a cluster in MongoDB as following:

1 Config Server
2 Shard Servers,

and this is Sharding status:

--- Sharding Status ---
  sharding version: {
        "_id" : 1,
        "minCompatibleVersion" : 5,
        "currentVersion" : 6,
        "clusterId" : ObjectId("59c333c5d92439c17b08a330")
}
  shards:
        {  "_id" : "rs1",  "host" : "rs1/ShardServer1:27018",  "state" : 1,  "tags" : [ "siteGroup1" ] }
        {  "_id" : "rs2",  "host" : "rs2/ShardServer2:27018",  "state" : 1,  "tags" : [ "siteGroup2" ] }
  active mongoses:
        "3.4.9" : 2
 autosplit:
        Currently enabled: yes
  balancer:
        Currently enabled:  yes
        Currently running:  no
                Balancer lock taken at Thu Sep 21 2017 05:39:18 GMT+0200 (W. Europe Standard Time) by ConfigServer:Balancer
        Failed balancer rounds in last 5 attempts:  0
        Migration Results for the last 24 hours:
                195 : Failed with error 'aborted', from rs2 to rs1
  databases:
        {  "_id" : "DataCollector",  "primary" : "rs2",  "partitioned" : true }
                DataCollector.EMessageData
                        shard key: { "SiteId" : 1 }
                        unique: false
                        balancing: true
                        chunks:
                                rs2     5
                        { "SiteId" : { "$minKey" : 1 } } -->> { "SiteId" : 1 } on : rs2 Timestamp(1, 1)
                        { "SiteId" : 1 } -->> { "SiteId" : 10 } on : rs2 Timestamp(1, 2)
                        { "SiteId" : 10 } -->> { "SiteId" : 11 } on : rs2 Timestamp(1, 4)
                        { "SiteId" : 11 } -->> { "SiteId" : 20 } on : rs2 Timestamp(1, 5)
                        { "SiteId" : 20 } -->> { "SiteId" : { "$maxKey" : 1 } } on : rs2 Timestamp(1, 6)
                         tag: siteGroup1  { "SiteId" : 1 } -->> { "SiteId" : 10 }
                         tag: siteGroup2  { "SiteId" : 11 } -->> { "SiteId" : 20 }

When I move a chunk from a Shard Server to another and got the error message:
"cannot start recv'ing chunk, sharding is not enabled and no config server was provided"

Could you please help me to resolve this problem?

Thank you in advanced!

Updated:
Config Server log says:

2017-09-22T03:48:32.803+0200 I SHARDING [Balancer] distributed lock 'DataCollector.EMessageData' acquired for 'Migrating chunk(s) in collection DataCollector.EMessageData', ts : 59c46aefbd78955b52702afc
2017-09-22T03:48:32.809+0200 W SHARDING [conn5] cannot start recv'ing chunk, sharding is not enabled and no config server was provided
2017-09-22T03:48:32.817+0200 I SHARDING [NetworkInterfaceASIO-ShardRegistry-0] distributed lock with ts: '59c46aefbd78955b52702afc' and _id: 'DataCollector.EMessageData' unlocked.
2017-09-22T03:48:32.820+0200 I SHARDING [Balancer] Balancer move DataCollector.EMessageData: [{ SiteId: 1.0 }, { SiteId: 10.0 }), from rs2, to rs1 failed :: caused by :: UnknownError: cannot start recv'ing chunk, sharding is not enabled and no config server was provided
2017-09-22T03:48:32.820+0200 I SHARDING [Balancer] about to log metadata event into actionlog: { _id: "ELVN-LAP02-2017-09-22T03:48:32.820+0200-59c46bf0bd78955b52702dcf", server: "ELVN-LAP02", clientAddr: "", time: new Date(1506044912820), what: "balancer.round", ns: "", details: { executionTimeMillis: 31, errorOccured: false, candidateChunks: 1, chunksMoved: 0 } }

Log file of nodes:
https://github.com/jvanhien/mongodbcluster

Shutdown all mongod and mongos (those two) processes, your shards and that config server. When nothing is running, first start shardServers when those are up, start config server and last mongos. Connect to mongos with mongo.

I noticed that with your tags, you haven't define where $minkey --> 1, SiteId: 10 or SiteId:20 --> $maxKey goes. So those chunks can be anywhere.

When chunkMove happens, what config servers log says?


Create a quick report for a client I have an Amazon RDS (postgres) database and an client asked me to have access to certain data stored in this database preferably in the form of a comma separated file. If the data changes, the client would like his report to represent the new state of the data.

What would be the text book way of providing this?

I was thinking about creating a view and granting them access to only this view.

Since you mentioned your client wanted a comma separated file as a report, you could always do something like:

psql -U myuser -h my.rds.external.address -c "\copy (SELECT * FROM mytable WHERE foo = 'bar') TO 'my_report.csv' WITH CSV HEADER NULL AS ''"

If you run that from a command line or a cron job from another machine (once you've allowed that IP address in your RDS instance configuration), you can just compress it and make it available to your client based on their needs.

A view would also work if you wanted to give them realtime access to the data instead of a comma separated report.


Set random value from set I need to put some random values into database, but I don't want to end up with completely randomized text (like 7hfg43d3). Instead I would like to randomly pick one of values supplied by myself.

Nice idea. I suggest two minor simplifications:

('{Foo,Bar,Poo}'::text[])[ceil(random()*3)]

Simpler syntax using an array literal ('{Foo,Bar,Poo}'::text[])
Shortens the string for longer lists.
Additional benefit: explicit type declaration works for any type, not just for text. Your original idea happens to output text, because that's the default type for string literals.
Use ceil() instead of floor() + 1. Same result.

OK, theoretically, the lower border could be 0 precisely, as hinted in your comment, since random() produces (quoting the manual here):

  random value in the range 0.0 <= x < 1.0

However, I have never seen that happen. Run a couple of million tests:

SELECT count(*)
FROM   generate_series(1,1000000)
WHERE  ceil(random())::int = 0;

-> SQLfiddle

To be perfectly safe, though, you can use Postgres custom array subscripts and still avoid the extra addition:

('[0:2]={Foo,Bar,Poo}'::text[])[floor(random()*3)]

Details under this related question on SO.

Or better yet, use trunc(), that's a bit faster.

('[0:2]={Foo,Bar,Poo}'::text[])[trunc(random()*3)]


Can mongorestore replay oplog via mongos? I'm using MongoDB 3.6.2 and configured to sharded cluster.
I used mongodump to dump oplog from every shard and then restored by mongorestore via mongos, but I got below error.

  Failed: no oplog file to replay; make sure you run mongodump with --oplog

And then I restored by mongod that works.

Is it possible to restore oplog via mongos? If yes, how to do?

You cannot replay oplogs through a mongos process: oplog entries can only be applied via a mongod that is the primary of a replica set.

Please see the MongoDB documentation for tutorials on Supported Backup Methods including Backing up a Sharded Cluster with Database Dumps.


Help Outputing MySQL General Log to Named Pipe I have a cPanel, Centos 7 webserver using MySQL 5.6, in which I am trying to output the MySQL general log to a named pipe I have created. I have not had any issues creating the pipe and can write to other replacement log files in the MySQL directory without issues.

However when I create a named pipe and set this as the log file (How do I output MySQL logs to syslog?) I get this error in my errorlog on a restart:

[ERROR] Could not open /var/lib/mysql/server1.log for logging (error 2). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.

I have set the owner and group of the named pipe to the mysql user and the permissions to 660 (and even 777 to be sure). These same permissions seem to work fine for a normal log file.

Any suggestions as to what I have done wrong?

Every step in /var/lib/ needs to have at least 755 permissions.  That is, each directory needs to be readable and x (searchable) by user mysql.  (Technically, none of those steps should be owned by mysql, so 005 is sufficient.  However, you may find 750, which won't work.)

/var/lib/mysql/, itself needs to be owned and writable by mysql (700 or better).


SQL Server Designers, Failed Saves, and Generated Scripts I am a big fan of the simple diagramming tool that comes with SSMS, and use it frequently.  When I save changes to the model, I have it configured to automatically generate the change scripts that go along with the save.  I then save (and source control) the resulting change script.  This works great and an important piece of the process my team(s) uses.

What occasionally happens is that a save fails, and I still get the option to save my change script.  I then fix the problem and save again (which results in another change script).

I'm never clear what I need to do at this point to maintain a consistent set of change scripts.  There seems to be overlap between the two scripts (the failed and the successful), but they are not identical.

If I want to continue to use this feature, what should I be doing with the resulting script as soon as I get a failed save of the model?

I do not use the designer for making changes, so I have no particular experience with script problems.  I did some years ago find that the designers were prone to errors, so I am not surprised that you have some problems.  Nonetheless, you find the tools valuable.

From your description, you wind up with two scripts, but they are not identical.  So the problem is how to treat the scripts.  Or how to avoid any need to deal with the issue.

Database backups or database snapshots are a couple of tools that can help.

If you are using SQL Server Enterprise or Developer editions, you have the ability to create database snapshots. So this solution depends on your SQL Server edition and you rights on that server and how many people are sharing that database.

Scenario using a snapshot database and assuming that you have full rights to do whatever you want to with the design database.

First, create a SNAPSHOT database (examples from the SQL Server documenation):

CREATE DATABASE AdventureWorks_dbss1800 ON
( NAME = AdventureWorks_Data, FILENAME =
'D:\Data\AdventureWorks_data_1800.ss' )
AS SNAPSHOT OF AdventureWorks;

Once you have the snapshot created, make your modifications in the designer and then save your scripts.  If all is well, you get rid of the snapshot:

DROP DATABASE AdventureWorks_dbss1800;

However, if the designer glitched you would then use the snapshot to revert the database back to the point in time of the CREATE...SNAPSHOT.

USE master;
-- Reverting AdventureWorks to AdventureWorks_dbss1800
RESTORE DATABASE AdventureWorks from
DATABASE_SNAPSHOT = 'AdventureWorks_dbss1800';

Because you reverted, you can repeat the designer steps with the database in exactly the same state as the first effort.

If you are not using a version that supports SNAPSHOT databases, you can do the same thing (perhaps more slowly) using BACKUP DATABASE and RESTORE DATABASE.


Test MySQL credentials from Linux command line? How do you test MySQL credentials from the command line on a Linux server?

mysql -h host -u user -p<whatever> -e"quit"

This allows you to use the same connection string that you use for programmatically sending queries to the server. You can add || exit 1 to the end to auto exit on invalid arguments. You may also want to redirect stderr to /dev/null if you do not like the auto generated MySQL error message.


In shard cluster, if 2 replica set nodes are offline leaving only 1 secondary..where do writes go? This is probably a simple question that i can't get definitive answer too.

If i have 10 shards, each being a 3 node replica set. If i loose 2 servers in a replica set causing the primary to change to a read-only secondary. Where does the mongos write new chunks to that the hash key would have sent to this shards primary?

Does the config server detect the shard being read only and inform the mongos that writes should be redirected to another shard?

Also, assuming the above is correct..when the shard gets fixed and has a primary again. Will the chunks be re-balanced to it?

thank you
fLo

If i have 10 shards, each being a 3 node replica set. If i loose 2 servers in a replica set causing the primary to change to a read-only secondary. Where does the mongos write new chunks to that the hash key would have sent to this shards primary?

Each shard contains a subset of data for the sharded cluster. If the replica set backing a shard becomes read-only you will get exceptions trying to write new data to that shard. The mechanics of failover and high availability for an individual shard are governed by your Replica Set configuration and deployment.

  Does the config server detect the shard being read only and inform the mongos that writes should be redirected to another shard?

No. In order to achieve this, the existing data would have to be migrated to another shard. The config servers and mongos do not monitor whether a shard is read-only or attempt to work around this case. When write operations are directed to that shard, they will generate exceptions.

If you do have an outage for a shard, one of the steps you should take is to disable the balancer. This will prevent attempts to migrate or rebalance data while you work on restoring that shard.

  when the shard gets fixed and has a primary again. Will the chunks be re-balanced to it?

Once the shard has a primary, normal operation can resume. You should also remember to re-enable the balancer if you disabled it during your outage.


how to get multiple rows from multiple tables concatenated into a single string in a stored procedure in Oracle I have a table A that has 7 different 1-N relationships. Given an A id, now I am running 7 simple queries to get the N side rows. I suspect this could be sped up.

Is it possible to:

create a stored procedure, with only input the A id
the sp runs the 7 queries, at each step concatenating the result to a single string. Rows/Columns should be separated in some way to allow automatic parsing later (not sure what is the best practice here)
the sp returns the single string, and in my code I only do a round-trip to Oracle

Is this possible? Is my gut right that this would be faster? Any pointers?

thanks

So you want to concatenate multiple rows and columns into a single string, just to parse it later. Overhead 1.

Next, you want to write PL/SQL for something that can be easily done in plain SQL. Overhead 2.

Something like this (I just used random views for the 7 data source):

select listagg(x, '|') within group (order by null) from
(
  select 'COL11' || '-' || 'COL12' as x from v$database where rownum = 1 union all
  select 'COL21' || '-' || 'COL22'      from v$instance where rownum = 1 union all
  select 'COL31' || '-' || 'COL32'      from v$logfile  where rownum = 1 union all
  select 'COL41' || '-' || 'COL42'      from v$log      where rownum = 1 union all
  select 'COL51' || '-' || 'COL52'      from v$datafile where rownum = 1 union all
  select 'COL61' || '-' || 'COL62'      from v$tempfile where rownum = 1 union all
  select 'COL71' || '-' || 'COL72'      from v$version  where rownum = 1
);

You want to minimize round trips. You do not need all this hassle for that, you can fetch multiple rows at a time.


'Invalid utf8 character string' warning when importing into AWS RDS MySQL I am trying to migrate osTicket from physical Windows machine to AWS RDS, and I get a bunch of warnings while doing so. I am quite a DB noob, so I am not sure what is the issue.

Source: Windows, MySQL 5.6.10, backup created with mysqldump -u root -p --default-character-set=utf8 osTicket > file.sql

mysql> show variables like 'character_set%';
+--------------------------+---------------------------------------------------+
| Variable_name            | Value                                             |
+--------------------------+----------------------------------------------------
| character_set_client     | cp850                                             |
| character_set_connection | cp850                                             |
| character_set_database   | utf8                                              |
| character_set_filesystem | binary                                            |
| character_set_results    | cp850                                             |
| character_set_server     | utf8                                              |
| character_set_system     | utf8                                              |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.6\share\char|
|sets\                                                                         |

Destination: AWS RDS, MySQL 5.6.27-log, import with mysql> source file.sql. I've created a custom Parameter Group to change character_set_database to utf8 since default on RDS is latin1

mysql> show variables like 'character_set%';
+--------------------------+---------------------------------------------------+
| Variable_name            | Value                                             |
+--------------------------+----------------------------------------------------
| character_set_client     | cp850                                             |
| character_set_connection | cp850                                             |
| character_set_database   | utf8                                              |
| character_set_filesystem | binary                                            |
| character_set_results    | cp850                                             |
| character_set_server     | utf8                                              |
| character_set_system     | utf8                                              |
| character_sets_dir       | /rdsdbbin/mysql-5.6.27.R1/share/charsets/         |

I get warnings like these:

Warning (Code 1300): Invalid utf8 character string: 'FFD8FF'
Warning (Code 1300): Invalid utf8 character string: 'FFD8FF'
Query OK, 6 rows affected, 6 warnings (0.06 sec)
Records: 6  Duplicates: 0  Warnings: 6

Warning (Code 1300): Invalid utf8 character string: 'FFD8FF'
Warning (Code 1300): Invalid utf8 character string: 'FFD8FF'
Warning (Code 1300): Invalid utf8 character string: 'FFD8FF'
Warning (Code 1300): Invalid utf8 character string: 'E2E3CF'
Warning (Code 1300): Invalid utf8 character string: 'D0CF11'
Warning (Code 1300): Invalid utf8 character string: 'E2E3CF'
Query OK, 20 rows affected, 20 warnings (0.06 sec)
Records: 20  Duplicates: 0  Warnings: 20

I tried to set character_set_client=cp850 and the same for set_connection and set_results before import, but didn't help me.

If anyone could be so kind to help me what I am doing wrong here, it would be really great.

Thanks in advance!

Judging by your initial post, where you said you used default options for mysqldump, it means you didn't use the --hex-blob option. I've played around with osTicket some time ago, and you should definitely used --hex-blob option to create your dump. It will give you much bigger .sql file (and of course, the import to RDS will take more time) but in that way you won't get the UTF8 warnings you're getting now, and your attachments will be available once you deploy osTicket to new host.

One more advice, update to newer versions (1.9 or 1.10) and move those files out of DB, you can use S3 or even EFS now, or just store it under your EBS volume


Finding longest matching prefix I have two tables:

Sources:

(SName)  (SId)
Apple1   100
Apple2   200
Banks    300
BanksB   400
Bankerly 500

Prefixes:

(PName) (PId)
App     1
Bank    2
Banker  3

My goal: To find the longest prefix that matches each of sources. The results would look like this:

(SName)  (SId) (PName) (PId)
Apple1   100   App     1
Apple2   200   App     1
Banks    300   Bank    2
BanksB   400   Bank    2
Bankerly 500   Banker  3

Constraints: I am using SQL Server 2000 and cannot upgrade. I know how to solve this problem using analytic functions, but they are not available in SQL Server 2000, as far as I know.

One way (SQL Fiddle)

SELECT SName,
       SId,
       LEFT(SName, MAX(LEN(PName)))                                          AS PName,
       CAST(SUBSTRING(MAX(STR(LEN(PName), 10) + LTRIM(PId)), 11, 10) AS INT) AS PId
FROM   Sources S
       JOIN Prefixes P
         ON S.SName LIKE P.PName + '%'
GROUP  BY SId,
          SName
ORDER BY SId

I am assuming that none of the prefixes are allowed to contain characters such as % that would mess up a LIKE search.

It uses a variant of the method in listing 5 in this article on TOP N Per Group Queries to bundle the PId along with the MAX(length). Once the MAX(length) is known it can be used to re-calculate what the prefix must have been so there is no need to add PName in to the concatenation.


How to Parse a query in Azure Data Studio? In Sql Server Management Studio, we can click a "Parse" button (or Ctrl+F5) to verify that a SQL statement is syntactically correct (without executing it).

How can I do it the same in Azure Data Studio?

There is no key mapped but you can map it.

Go to:
File -> Preferences -> Keyboard Shortcuts
or
Ctrl+K Ctrl+S

Use the search feature or scroll down to "Parse Query" and setup a key binding


Postgres Initial Setup and User Login Full disclosure, new to postgres and new to linux. I am having trouble understanding the user management side of postgresql. I installed on a Ubuntu 19.04 based os using

sudo apt update
 sudo apt install postgresql postgresql-contrib

Ive been able to switch to the default user postgres and can successfully call psql amongst other commands. When trying to connect via pgadminIII i am prompted for a password when using the same user name. A couple searches reveal that you should be able to leave this blank. I get an error stating no password supplied.

Additionally, I would like to make my own user name an admin and use my linux password. I suppose this would be similiar to windows authentiaction on microsoft sql. After searching the internet it appeared that all i need to do is createuser *MyNameHere* and declare it a superuser. However when atempting to connect form command line it acts like there should also be a database named the same as my user. Secondly when using the pgadmin it actively rejects my credentials.

I guess my question is this:

Can someone explain to me the basic concepts of roles/usernames and how they relate to the linux user names. And secondly, what is going wrong in the above.

Users, roles, and permissions can get kind of confusing in PostgreSQL. To control who can try to connect to the database, Postgres uses the pg_hba.conf file. In Ubuntu, it's buried in /etc/postgres/<#>/main.

From inside the data base there are roles which control permissions each user has inside the database.

An easy, but probably insecure way to connect is to set up trust from localhost connections in pg_hba.conf.


Mongodb data sync between primary and secondary node even when port is closed I have configured mongodb replicaset in aws using 3 ec2's with one primary and 2 secondary nodes. When I insert data on primary node it is getting synced to the secondary nodes which it should. But when I close the port via security group still the data is syncing to the secondary node whose port is closed even though the state in rs.status() in the primary node is stateStr" : "(not reachable/healthy)" -> for the secondary node(whose port is closed).

I tried this with my secondary node in different subnet public as well as private but its still getting synced.I am new to mongodb and might be doing something wrong. Could someone please suggest something.

Have you closed that path both ways...

If primary cannot "ping" secondary, it's not reachable/healthy, but if secondary can connect primary, it can replicate opLog...


Monthly Date and Hour wise report generation using PHP MYSQL Attendance Portal in PHP

I am facing problem at Final Attendance Report Generation

I'm stuck at the final stage of the Project:

I have a Table- attendance, If a student is Present I mark it as 1 if absent 0. we have a date, day order, hour (we are following Day Order Time Table)

 Table
    dayorder:
        id day
        1 day1
        2 day2
        3 day3
        4 day4
        5 day5
        6 SAT
        7 day6
Table
Hour:
   id
    1
    2
    3
    4
    5
    6
    7

Table attendance:

I must generate a Monthly report like a register as below

Code I have Tried:

SELECT student_id,deptno,Month, Year_c,
branch.description as bdesc,
course.coursecode as ccd,
users.firstname as ufn,
users.lastname as uln,
course.description as ccdes,schedules.hour as hhour,
h,dayorder,AttdDate,
CONCAT(AttdDate,h,dayorder) AS fate,
IF(Day_c=1, p, " ") AS '1',
IF(Day_c=2, p, " ") AS '2',
IF(Day_c=3, p, " ") AS '3',
IF(Day_c=4, p, " ") AS '4',
IF(Day_c=5, p, " ") AS '5',
IF(Day_c=6, p, " ") AS '6',
IF(Day_c=7, p, " ") AS '7',
IF(Day_c=8, p, " ") AS '8',
IF(Day_c=9, p, " ") AS '9',
IF(Day_c=10, p, " ") AS '10',
IF(Day_c=11, p, " ") AS '11',
IF(Day_c=12, p, " ") AS '12',
IF(Day_c=13, p, " ") AS '13',
IF(Day_c=14, p, " ") AS '14',
IF(Day_c=15, p, " ") AS '15',
IF(Day_c=16, p, " ") AS '16',
IF(Day_c=17, p, " ") AS '17',
IF(Day_c=18, p, " ") AS '18',
IF(Day_c=19, p, " ") AS '19',
IF(Day_c=20, p, " ") AS '20',
IF(Day_c=21, p, " ") AS '21',
IF(Day_c=22, p, " ") AS '22',
IF(Day_c=23, p, " ") AS '23',
IF(Day_c=24, p, " ") AS '24',
IF(Day_c=25, p, " ") AS '25',
IF(Day_c=26, p, " ") AS '26',
IF(Day_c=27, p, " ") AS '27',
IF(Day_c=28, p, " ") AS '28',
IF(Day_c=29, p, " ") AS '29',
IF(Day_c=30, p, " ") AS '30',
IF(Day_c=31, p, " ") AS '31'
FROM
(SELECT *,DAY(date) AS Day_c,
MONTHNAME(date) AS Month,
Year(date) AS Year_c,
date(date) AS AttdDate,hour as h, day as dayorder,
(CASE  WHEN present = 1
    THEN 'P'
    WHEN present = 0
    THEN 'A'
    WHEN present is null
    THEN ' '
END) AS p
FROM attendance a
WHERE date between '$from' AND '$to' And branch = $branchid AND coursecode = $courseid AND batch = $batchid
GROUP BY student_id
ORDER BY student_rollno ASC
)
as report
LEFT JOIN branch on branch.id = report.branch
LEFT JOIN course on course.id = report.coursecode
LEFT JOIN users on users.id = report.user
LEFT JOIN schedules on schedules.id = report.hour
ORDER BY Month DESC, Year_c DESC

OUTPUT in PHPMYADMIN:

But I am not able to show up as a HTML Page using PHP and MY SQL.
How to get data without dates which are not recorded?

How to get data without dates which are not recorded?

This is a common problem.  The generic solution is

Build a table with all possible dates (at least in the range needed).
LEFT JOIN from that table to your table.  (Or, if it is a complex query, then use a derived table:  LEFT JOIN (SELECT ...) AS x  ON dates.day = x.day.
If desired, use COALESCE(...) to turn NULLs into 0s or blanks.


Loop through key/value pairs of a jsonb object in postgresql function I am trying to create a function in Postgres that can loop through each key/value pair in a given jsonb object.

create or replace function myFunction
(input jsonb)
returns jsonb as $$
BEGIN

    // foreach(key in input)
    //       do some math operation on its corresponding value

    returns input;

END; $$

The argument input is expected to be a jsonb object, such as {"a":1, "b":2, "c":3}.

I want to loop through each key/value pair in the object. As you can tell from my comment, I used to write backend code with more general-purpose programming languages like c, java, etc. So I am not good at SQL. I have tried to search online, but they talk about how to loop through jsonb array instead of object. So really stuck here.

Use jsonb_each(jsonb) or jsonb_each_text(jsonb) in a FOR loop like:

CREATE OR REPLACE FUNCTION my_function(input jsonb)
  RETURNS jsonb
  LANGUAGE plpgsql AS  -- language declaration required
$func$
DECLARE
   _key   text;
   _value text;
BEGIN
    FOR _key, _value IN
       SELECT * FROM jsonb_each_text($1)
    LOOP
       -- do some math operation on its corresponding value
       RAISE NOTICE '%: %', _key, _value;
    END LOOP;

    RETURN input;
END
$func$;

Call:

SELECT my_function('{"a":1, "b":2, "c":3}');

Related:

Declare variable of table type in PL/pgSQL

Note that for many problems where people used to programming languages like c, java, etc. would tend to use a loop, there is a superior set-based solution in SQL around the corner. Side-by-side code examples:

Postgres FOR LOOP
Syntax error in a Postgres statement with procedural code


How to pull number code from strings buried in parentheses? I have string data (ProductDescription) that includes product names and ID numbers and need to pull the ID numbers for cross-referencing...

Data Samples:

Widget (1234)
Different Widget (123)
Many Widgets (2x) (4567)

I am trying to do this with a rather messy CASE statement:

CASE
    WHEN
        ISNUMERIC(
        SUBSTRING(
            ProductDescription
            ,CHARINDEX('(',ProductDescription)+1
            ,CASE
                WHEN
                    CHARINDEX(')',ProductDescription)
                        -CHARINDEX('(',ProductDescription)-1 > 0
                    THEN CHARINDEX(')',ProductDescription)
                        -CHARINDEX('(',ProductDescription)-1
                ELSE 0 END
            )) = 1
    THEN
        SUBSTRING(
            ProductDescription
            ,CHARINDEX('(',ProductDescription)+1
            ,CASE
                WHEN
                    CHARINDEX(')',ProductDescription)
                        -CHARINDEX('(',ProductDescription)-1 > 0
                    THEN CHARINDEX(')',ProductDescription)
                        -CHARINDEX('(',ProductDescription)-1
                ELSE 0 END
            )
    ELSE 0 END

In cases where there is a parenthetic insertion in the product name (as in the "Many Widgets (2x)" example), I end up returning the "2x" value, which is not what I want.

How can I look for only numeric values contained in parentheses and discard the contents of others? Is there an easier/cleaner way to do the simple CHARINDEX work I'm currently attempting?

Thanks!

If the data you need is always at the end of the string, would something like this work?

declare @test varchar(200);

set @test= 'Many Widgets (2x) (4567)';

select substring(@test,
                 (len(@test) - charindex('(',reverse(@test))) + 2,
                 charindex('(',reverse(@test)) - 2
                );

We can reverse the string, find the first open paren (which is really the last open paren), and go from there.


SQL Server - What is the difference between BULKADMIN and ADMINISTER BULK OPERATIONS To grant user ability to run Bulk Insert T-SQL command, login/user needs to be granted:

BULKADMIN server role - or - ADMINISTER BULK OPERATIONS server-level permission
connect on target database
insert on target table

But what is the difference between BULKADMIN server role and ADMINISTER BULK OPERATIONS server-level permission ?

If we look at the SQL Server Permissions Poster (2017 edition):

It appears as though ADMINISTER BULK OPERATIONS covers bulkadmin in addition to OPENROWSET which allows access to external sources.


CASE statement multiple conditions I've got a simple table below,

Select * from message_log where  Message = 'BUILD' order by 1;

+------------+-------------+
|  **Message |     Log**   |
+------------+-------------+
|    Build   |  Day Start  |
+------------+-------------+
|    Build   |  Day End    |
+------------+-------------+
|    Build   |  Dusk Start |
+------------+-------------+
|    Build   |  Dusk End   |
+------------+-------------+
|    Build   |  Night Start|
+------------+-------------+
|    Build   |  Night End  |
+------------+-------------+

If I for example write a CASE statement

SELECT DISTINCT CASE WHEN (Log = 'Day Start') THEN 'RUNNING' END AS A
from message_log where  Message = 'BUILD' order by 1;

It'll return a Null value and a 'RUNNING' value, can I write this in such a way that it doesn't include the null row?

Just add a WHERE condition.  And if you are only displaying one value, RUNNING, then there is no reason for a CASE. Otherwise you will want to evaluate each condition in the CASE including what should display in the event none of the conditions is met; a default value.

So here is the code to your original question:

SELECT 'RUNNING' AS A
from message_log
where  Message = 'BUILD'
    AND Log = 'Day Start'
order by 1;

And here is the code for a multi-condition CASE:

SELECT CASE
    WHEN (Log = 'Day Start') THEN 'RUNNING'
    WHEN (Log = 'Day End')   THEN 'NOT RUNNING'
    ELSE 'UNKNOWN' END AS A
from message_log
    where  Message = 'BUILD'
order by 1;


Regarding GIN Index in Postgresql I am facing an issue that Even after creating a GIN INDEX execution plan is taking much time.

select email from customer where lower(email) like '%gmail';
CREATE INDEX idx1  ON customer USING gin  (lower(email) gin_trgm_ops);.

Index created as  "idx1" gin (lower(email::text) gin_trgm_ops)

explain plan gives as below

###
 Bitmap Heap Scan on customer  (cost=168.51..367.36 rows=194 width=22) (actual time=381.844..1807.673 rows=931488 loops=1)
   Recheck Cond: (lower((email)::text) ~~ '%gmail.com'::text)
   Rows Removed by Index Recheck: 556
   Heap Blocks: exact=53254
   ->  Bitmap Index Scan on idx1  (cost=0.00..168.46 rows=194 width=0) (actual time=372.263..372.263 rows=933383 loops=1)
         Index Cond: (lower((email)::text) ~~ '%gmail.com'::text)
 Planning time: 0.741 ms
 Execution time: 1988.646 ms
(8 rows)

Please let me know if this can be fine-tuned further

You are returning 1 million rows.  Returning 1 million rows is going to take some time.  Especially when it has to do a pattern matching recheck of each row.  How fast do you expect this to be?

Your best bet is fixing things on the application side.  Why do you need a million rows?  What are doing with them?  And if you do need to return that many, why do you need to do it so often that it matters if it takes an extra second to do?  But beyond that:

Get a faster computer.
Upgrade to a recent version of PostgreSQL and see if you can get parallel execution (that would depend on what percentage of your table is being returned by this query)
Get rid of the "lower" function and use "ilike" rather than "like".  Indexes based on gin_trgm_ops support "ilike" queries naturally with no special steps needed.
Use an ordinary btree index on reverse(lower(email)) so that the wild card is at the end rather than the beginning of the query string.  Better yet, store the email in that form to start with, so expression indexes are not needed.


for xml missing null columns When we use for xml clause to create xml from a row like as follows

select * from Menus where MenuID=100 for xml path('')

The xml will miss the columns which have NULL as the value.

I have a found method like

select * from Menus where MenuID=100  for xml path('') , ELEMENTS XSINIL

But the xml will create as follows only

<MenuID xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">100</MenuID>
<MenuOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">1</MenuOrder>

Is there any way to create like as follows with NULL columns

<MenuID>100</MenuID>
<MenuOrder>1</MenuOrder>

If you don't want to use xsi:nil you have to decide what value you want to have in the XML when the value in the table is null. For strings it might be appropriate with an empty string. For integers you might want to use 0 or -1 or perhaps convert the column data to a string first and use an empty string for integers as well. You have to decide what value you want and use isnull and cast to get the output you want.

Example:

declare @T table
(
  MenuID int,
  MenuOrder int,
  MenuName varchar(10)
)

insert into @T default values

select -- 0 instead of null
       isnull(MenuID, 0) as MenuID,
       -- empty string instead of null
       isnull(cast(MenuOrder as varchar(11)), '') as MenuOrder,
       -- The text value NULL instead of null
       isnull(MenuName, 'NULL') as MenuName
from @T as T
for xml path('')

Result:

<MenuID>0</MenuID>
<MenuOrder></MenuOrder>
<MenuName>NULL</MenuName>

If you want to do this dynamically where you have the table name as parameter and you decide that empty string is the way to go regardless of data type you can do something like this.

-- Table parameter
declare @TableName varchar(128) = 'Menus';
-- Column to use in where clause
declare @ColumnName varchar(128) = 'MenuID';
-- Value to filter by
declare @ColumnValue int = 100;

declare @S nvarchar(max);

set @S = '
select '+
  stuff((
        select ', isnull(cast('+quotename(C.name)+' as nvarchar(max)), '''') as '+quotename(C.name)
        from sys.columns as C
        where C.object_id = object_id(@TableName)
        for xml path('')
        ), 1, 2, '')+'
from '+quotename(@TableName)+'
where '+quotename(@ColumnName)+' = @ColumnValue
for xml path('''')';

exec sp_executesql @S, N'@ColumnValue int', @ColumnValue;

You obviously need to parameterize what column and value you want to use as well if the table name is a parameter.

The dynamically generated query:

select isnull(cast([MenuID] as nvarchar(max)), '') as [MenuID], isnull(cast([MenuOrder] as nvarchar(max)), '') as [MenuOrder]
from [Menus]
where [MenuID] = @ColumnValue
for xml path('')


Which MySQL queries are causing this error message? I found this Stack Overflow Q & A:

mysql: see all open connections to a given database?

But by the moments that is the limit of connections, I have not access to my server, which displays error messages:

ERROR: Zend_Db_Adapter_Exception: SQLSTATE[08004] [1040]
 Too many connections (Abstract.php:144)

ERROR: PleskDBException: Unable to connect to database:
 mysql_connect(): Too many connections (Error code: 1040) (Abstract.php:69)

How to know which queries were being made at the time that my server was unavailable?

I have a single site on the server and does not have much movement. There is something causing numerous unnecessary processes and I need to figure out what it is.

This issue, Too Many Connection error occurs when the max_connections variable reaches its maximum threshold value.
Usually, the value for max_connections is defined at the configuration file. By increasing this variable value, this error will resolve.
To see the current value, try the following command:
mysql> show global variables like '%max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 300   |
+-----------------+-------+
1 row in set (0.02 sec)

When the defined limit reaches, all users will get the error, this is not user specific. However, MySQL allows you to define the user specific values.
After login as root or user with SUPER privilege, you can run the below command to identify the currently processing queries:
mysql> show full processlist;

Note: even if you get Too Many Connections error, you can still login to server, using the root or user with SUPER privilege.  MySQL by default will consider maximum allowed connection as max_connection + 1 for SUPER user to fix this issue.
By increasing this variable value, this issue will be resolved.
mysql> SET global max_connections = 1000;

If you wish this changes to be permanent, edit the configuration file (my.ini), and change the value for max_connections variable under [mysqld] section. This approach requires a server restart.
[mysqld]
max_connections = 1000;

Changing this variable value to a higher value in a system with limited resource may lead to performance issues related to memory. Check this page for how to fix MySQL Too Many Connections and other workarounds. I hope this answer will help you.


PostgreSQL query with max and min date plus associated id per row I have the following table:

CREATE TABLE trans (
    id SERIAL PRIMARY KEY,
    trans_date date,
    trans_time time
);

I want to have the following view

CREATE OR REPLACE VIEW daily_trans AS
SELECT trans_date,
    max(trans_time) as first,
    min(trans_time) as last,
    calculate_status(min(trans_time), max(trans_time)) as status
GROUP BY trans_date

with columns that specify the ids of the max and min trans_time.

How do I do that?

You can calculate this in one step with window functions:

CREATE OR REPLACE VIEW daily_trans AS
SELECT DISTINCT
       trans_date
     , first_value(trans_time) OVER w AS first_time
     , first_value(id)         OVER w AS first_id
     , last_value(trans_time)  OVER w AS last_time
     , last_value(id)          OVER w AS last_id
     , calculate_status(min(trans_time) OVER w
                      , max(trans_time) OVER w) AS status
FROM   trans
WINDOW w AS (PARTITION BY trans_date ORDER BY trans_time, id
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
ORDER  BY 1;

I added id as secondary ORDER column in the window clause, to make the sort order stable in case of identical times per day.

If you are not familiar with window functions, be sure to read this chapter of the manual.

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING is a not so commonly used clause to adjust the window frame, because we want last_value() to always return the last value of the whole partition (the date), not the last value up to the current (including peers) according to the sort order.

How you to combine DISTINCT with window functions:

PostgreSQL: running count of rows for a query 'by minute'


Get Nested/Hierarchical Data from SQL Table I have a table relationship between 'Tag' and 'Task'. It is M:M. The relationship is mapped in the table 'TagTaskMapping'.

The TagAssignment table stores the relationship between a Tag and a date. So a tag can be mapped to a single date(period).

I want to output a nested hierarchy from sql.

SQL Tables:

-- tag assigned to a specific date
CREATE TABLE [dbo].[TagAssignment](
    [TagAssignmentID] [int] IDENTITY(1,1) NOT NULL,
    [TagID] [int] NOT NULL,
    [Period] [date] NOT NULL
);

-- task(s) mapped to tag(s)
CREATE TABLE [dbo].[TagTaskMapping](
    [TagID] [int] NOT NULL,
    [TaskID] [int] NOT NULL
);

-- tag table
CREATE TABLE [dbo].[Tag](
    [TagID] [int] IDENTITY(1,1) NOT NULL,
    [TagName] [nvarchar](150) NOT NULL
)

TagAssignment mapping table data:

TagAssignmentID TagID   Period
24                3    31/05/2017
14                2    31/05/2017

TagTaskMapping table data:

TagID   TaskID
  2       1
  2       2
  2       3
  3       1
  3       3

Here is my query...

DECLARE @Period datetime = '2017-05-31'
;WITH CTE_TagAssignment
AS
(
    -- GET TAG(S) Assigned to selected PERIOD
    SELECT
         ta.TagID
        ,t.TagName
        ,null as 'Task'
    FROM dbo.TagAssignment ta
    INNER JOIN
        dbo.Tag t
         ON t.TagID = ta.TagID
    WHERE ta.Period = @Period

    UNION ALL

    /**USING RECURSION!!!!**/
    -- foreach above tag assigned to a period, get it's associated task(s)
    SELECT
        ttm.TagID
        ,null AS 'TagName'
        ,ttm.TaskID as 'Task'
    FROM CTE_TagAssignment cta
    INNER JOIN
        dbo.TagTaskMapping ttm
        ON cta.TagID = ttm.TagID
)
SELECT *
FROM CTE_TagAssignment
OPTION (MAXRECURSION 100);

However, I get this error: The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

This is the hierarchy output I would like...

TagID   TagName   Task
2       Level 5
                   1
                   2
                   3
3       Level 3
                   1
                   3

IMHO you don't need a recursive solution, you can get it by using a simple JOIN.

DECLARE @Period datetime = '20170531';

SELECT     t.TagID, t.TagName, tm.TaskID
FROM       TagAssignment ta
INNER JOIN TagTaskMapping tm
ON         tm.TagID = ta.TagID
INNER JOIN Tag t
ON         t.TagID = tm.TagID
WHERE      ta.Period = @period
ORDER BY   tm.TagID, tm.TaskID;
GO

TagID | TagName | TaskID
----: | :------ | -----:
    2 | Level 5 |      1
    2 | Level 5 |      2
    2 | Level 5 |      3
    3 | Level 3 |      1
    3 | Level 3 |      3

dbfiddle here


Sql Server Backup to URL (Azure) ERROR I'm trying to take advantage of the new backup to url function in SQL Server.  I have created a valid cert and tried a simple backup, but I get the same error every time no matter what I try.
I have also tried creating a new managed backup.  With the managed backup I see a new storage container but get no new blobs.  When I look through the logs it is getting the same error.

example:

BACKUP DATABASE [mydb]
TO  URL = 'https://mybackups.blob.core.windows.net/sqltest-mssqlserver/backup_2014_08_08.bak'
WITH
CREDENTIAL = N'AzureCreds' ,
STATS = 1
GO

Message:

Msg 3293, Level 16, State 1, Line 1
An error occurred while Backup/Restore to URL was initializing. Error message: Internal engine error, invalid parameters caused fatal error.

Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

You might have an issue with your credentials. I recently used the following command successfully to back up to SQL Azure:

BACKUP DATABASE [MyDatabaseName] TO URL = N'https://mystorageaccount.blob.core.windows.net/mycontainer/MyDatabaseName_backup_2014_12_19_124300_8473463.bak'
WITH CREDENTIAL = N'MyCredentialName',
    NOFORMAT, NOINIT,
    NAME = N'MyDatabaseName_backup_2014_12_19_124300_8473463',
    SKIP, REWIND, NOUNLOAD,
    COMPRESSION, STATS = 10;

The credential was set up with MyCredentialName as the 'Name', the storage account name as the 'Identity', and primary or secondary access key as the 'Password'.

I ran into problems with the credential originally because I was trying to use the credential format supported for using Azure storage as the file location for the database (i.e. url prefix for 'Name', "SHARED ACCESS SIGNATURE" for Identity, and everything after the "?" from shared access signature url for the password), and that apparently does not work with the BACKUP command.


Business Continuity Plan for web application and database I have been asked by work to set up a business continuity/disaster recovery plan for two similarly implemented web applications and associated databases.

Not something I'm familiar with doing.

What are the best practice procedures for creating plans of this nature?

Business Continuity can be a complicated beast and you'll need to involve affected departments/users to make sure everyone knows the 'plan'.  The main two things you'll tend to see over and over are: RTO (Recovery Time Objective) and RPO (Recovery Point Objective)

RTO basically is the maximum time allowed to get things back to 'normal' or at least functioning.  You would need to take into consideration small problems (server went down) to big problems (the building just exploded).

RPO basically is, how much data could we 'possibly' lose should a bad problem happen?  5 minutes, 1 hour, 1 day, NONE?

There are lots of good stuff on the Internet about this topic - I think it would be time well spent doing some internet searches for IT Business Continuity Plan.

Here are a couple of good articles right off the bat:

http://www.cio.com/article/2381021/best-practices/how-to-create-an-effective-business-continuity-plan.html

https://www.brentozar.com/archive/2014/05/new-high-availability-planning-worksheet/

https://www.brentozar.com/archive/2011/12/letters-that-get-dbas-fired/

https://www.brentozar.com/archive/2015/09/why-rpo-and-rto-are-actually-performance-metrics-too/


mariabackup from remote mariadb10.3 galera | '/var/lib/mysql/' (Errcode: 2 "No such file or directory") The following setup applies:

3 node galera cluster in docker
backup container that should run mariabackup to get the data out of the galera cluster
network is fine. backup container can ping galera
mariadb/mariabackup version 10.3
the backup container only has mariabackup installed.

The command that i used

mariabackup --backup --host=<service_name> --port=3306 --user=root --password=<pass> --target-dir=/backup

When trying to backup from galera-cluster from within the backup-container i get the following error

00] 2019-09-15 23:38:34 Connecting to MySQL server host: mdb_mariadb, user: root, password: set, port: 3306, socket: /var/run/mysqld/mysqld.sock
[00] 2019-09-15 23:38:34 Using server version 10.3.18-MariaDB-1:10.3.18+maria~bionic-log
[00] 2019-09-15 23:38:34 Warning: option 'datadir' points to nonexistent directory '/var/lib/mysql/'
[00] 2019-09-15 23:38:34 Warning: MySQL variable 'datadir' points to nonexistent directory '/var/lib/mysql/'
[00] 2019-09-15 23:38:34 Warning: option 'datadir' has different values:
  '/var/lib/mysql/' in defaults file
  '/var/lib/mysql/' in SHOW VARIABLES
mariabackup based on MariaDB server 10.3.18-MariaDB debian-linux-gnu (x86_64)
[00] 2019-09-15 23:38:34 uses posix_fadvise().
mariabackup: Can't change dir to '/var/lib/mysql/' (Errcode: 2 "No such file or directory")
[00] 2019-09-15 23:38:34 my_setwd() failed , /var/lib/mysql/

What i tried.

researching various gists for clues
researching the mariadb scripts provided by the server install
trying different option combinations, but always the same error.

Question:

Has anyone made remote backups and can tell me how you succeeded?
Is the approach wrong and is there a better way to make hot backups once per day and incremental during the day?

Wrong approach - mariabackup isn't designed to work remotely. See also MDEV-15075.

So you need to run mariabackup locally on one of the nodes in the cluster.

To prevent the backup from slowing down the cluster, you can prevent the node from engaging Flow Control and allow it to fall behind the other nodes by doing:

SET GLOBAL wsrep_desync=ON;

Once the backup is completed, you can do:

SET GLOBAL wsrep_desync=OFF;

(See wsrep_desync in the MariaDB knowledge-base.)

Obviously, don't direct any database traffic at the node while you're running the backup.


How to profile Postgresql instance? My Postgresql server is using 100% cpu. I am looking to figure out whether individual queries are slow or I am simply hammering the server with too many requests to begin with.

Is there some sort of profiler I could attach that would let me know:

What queries trigger the most of the CPU time, across multiple connections?
For the above queries, what is the average runtime per query?

If you are on Linux or relatives, you can use top to see what the top processes are.  If you hit 'c' within top it should show you the process titles, which will tell you if you have selects, updates, inserts, or if it is other maintenance tasks taking up the CPU (or if you have been hacked and are now mining cryptocurrencies for someone, which seems to be common lately).  You can also see if there is a small number of processes each using about a single CPU each, or a large number all fighting over their slice of the CPU.

You can use the pg_stat_statements to keep track of how many times each query was run and how long it took to run.  This reports the wall-clock time, not the CPU time.  If you turn on track_io_timing, you can also get the time spent reading from disk, which is good to know but still doesn't give you CPU time as some time could go to waiting on locks, or waiting for a slice of CPU time.

If the problem is more intermittent and hard to catch in the act, then auto_explain and log_min_duration_statement could help.

For more advanced profiling, you can use system tools like perf top or strace -y -p <pid> and maybe even gdb.  These usually require quite a bit of knowledge of the PostgreSQL guts in order to interpret them.  These are usually used to investigate ways to improve PostgreSQL itself (i.e. for future versions), but I often find they can help diagnose tuning problems as well.  You would have to install the debug symbols to get maximum use of perf or gdb.


Report Builder does not connect for a remote reporting Server Current Environment

I am in a bit of trouble here, I have a Reporting Services installed on one server and SQL Server database engine installed on another server.

The users are connecting to Reports Manager which obviously points to the server where Reporting Services are installed.

User use a generic login (a local admin account on the Report Server) to connect to the reports manager.

But when the users click on the Report Builder button on the Reports Manager, It initially failed to launch complaining `"Application cannot retrieve the files. Authentication failed."

After a lot of research I found out that, even though users entered the credentials of a Local account when connecting to the Reports Manager, but when they clicked on the Report Builder button, the credentials from their windows account were picked up hence the ClickOnce application failed to Authenticate the user and failed to Launch.

I changed this behavior by allowing Basic Authentication on Reporting Server and Allowing Anonymous Authentication on Reports Builder.

Now when the users click on the Report Builder instead of report builder just picking the credentials from the current user windows account it prompts for the credentials , the  user passes the credentials of the Local Admin account on the reporting server and the ClickOnce Application is downloaded as expected.

Problem

Now the report builder is launched fine and I can right click the The datasources folder in the left pane of the designer, browse for the available data sources , test connection (shows tested successfully) . But as soon as I click on the DataSet and try to add a new dataset it throws an error saying "Authentication failed, cannot connect to the datasources."

Even though in the last step the connection test was successful but at this stage it is failing.

Important Note

The SQL Server has no external IP, it can only be seen from the reporting server. Could this be the reason that the connection is failing?

I mean does the report builder try to obtain a direct connection to the SQL Server when launched?

Or is there anything else that I have missed out?

Any pointer any suggestions are much appreciated , Thank you.

After a lot of digging around, I didn’t get anywhere, then eventually I got Microsoft involved to help me with the problem.

MS solution architect confirmed that the Report Builder requires a direct connection to SQL Server when launched hence the working with data sets in the report builder failed. Apparently this is BY DESIGN and it is not going to change.

This was in SQL Server 2008 R2 but the same is true for SQL Server 2012, 2014 and 2016. Not sure about 2017 but I doubt it very much if that has changed in 2017.

We ended up providing the client a machine on the network in DMZ to which end users connected remotely, only to work with the Report builder, it is a poor solution but it worked at that time and we needed a quick fix for it at that time.


Can I improve my duplicate-detection queries? I have a DB table with US domestic flight data which I am in the process of sorta-kinda-de-duplicating. The schema appears here, but it is probably not very important. There are no perfectly-duplicate records; rather, I have a several fields which almost form a unique key: year_, month_, dayofmonth, uniquecarrier, flightnum, origin; and under 0.1% of the records share the same values on these fields with other records.
What I want to do is isolate these "pseudo-duplicates" or key-violators, i.e. pairs of records which have the same values on these columns.
Here's what I'm currently doing:
CREATE TEMPORARY TABLE dupe_keys AS (
    SELECT year_, month_, dayofmonth, uniquecarrier, flightnum, origin
    FROM (
        SELECT count(*) AS cnt, year_, month_, dayofmonth, uniquecarrier, flightnum, origin
        FROM ontime
        GROUP BY year_, month_, dayofmonth, uniquecarrier, flightnum, origin
    ) AS t WHERE cnt > 1
) ON COMMIT PRESERVE ROWS;

This query takes quite a lot of time (relative to an ideal possible implementation outside of a DBMS based on sorting the key columns). I then run:
CREATE TABLE dupes AS (
    SELECT  o.year_,o.quarter,o.month_,o.dayofmonth,o.dayofweek,o.flightdate,o.uniquecarrier,o.airlineid,o.carrier,o.tailnum,bunch_of,other_fields,go_here
    FROM ontime AS o, dupe_keys AS dk
    WHERE
            o.year_         = dk.year_
        AND o.month_        = dk.month_
        AND o.dayofmonth    = dk.dayofmonth
        AND o.uniquecarrier = dk.uniquecarrier
        AND o.flightnum     = dk.flightnum
        AND o.origin        = dk.origin
    ORDER BY o.year_ ASC, o.month_ ASC, o.dayofmonth ASC, o.uniquecarrier ASC, o.flightnum ASC, o.origin ASC
);

which obviously takes less time.
I would like to achieve the same, faster, with better queries. Alternatively, I wouldn't mind something like a pair of tables of the same size, each with one of the two dupes out of a dupe set.
Notes:

You may assume that no combination of key-column values appears more than twice. It's either once or twice.
I'm using MonetDB, a columnar in-memory DBMS. hence the syntax specifics.
I hope my question is not DBMS-specific, but in case it is - answers based on the behavior of other DBMSes are still relevant, just please state which DBMS you're assuming.
This query is run when the data set is loaded, rather than repeatedly, So pre-computation which takes even more time is not relevant.

In your query to create the temporary table, it's possible to do that in standard SQL without a sub-query, using a HAVING clause, e.g.:
CREATE TEMPORARY TABLE dupe_keys AS (
    SELECT count(*) AS cnt, year_, month_, dayofmonth, uniquecarrier, flightnum, origin
    FROM ontime
    GROUP BY year_, month_, dayofmonth, uniquecarrier, flightnum, origin
    HAVING cnt > 1
);

I don't know if that will speed up the temporary table creation, but it might be worth a try.


SQL Server Error - "Cannot execute as the database principal because the principal [name]" does not exist" I've searched for an answer to this, but nothing I've tried so far has worked. So what happened was my DBA created a copy of a SQL Server database for me. I have several SQL Server Reporting Services (SSRS) reports in a project (in my Visual Studio solution) and they all must connect to the new database, via pre-defined credentials.

The credentials in question  consist of a username and a password. If the the database name is "DbName" and the username in question is "dbUserName", the error I get is this:

  Cannot open database 'DbName' requested by login. The login failed. Login failed for user 'dbUserName'.

When looking at the properties for database "DbName" in SQL Server Management Studio (SSMS), under the "Permissions" menu, I do see an entry for 'dbUserName' as a User. I see it's been granted a Connect permission, but when I click on tab "Effective", I get the following error:

  Cannot execute as the database principal because the principal "dbUserName" does not exist, this type of principal cannot be impersonated, or you do not have permission.

Also, one more piece of information that might be useful. If, in SSMS, I go in the Security folder for database "DbName" and then expand Users, I do see user 'dbUserName' there. Looking at its properties reveals its user type as a "SQL user without login", while in a different database (basically a test copy of 'DbName') the user type is "SQL user with login".

Any idea what's causing the errors in question?

It sounds like the login did not get mapped to the database user following the restore. Try running EXEC sp_change_users_login 'Update_One','dbUserName','dbUserName' pointing to the database and then see if you still receive the error.  For reference - sp_change_users_login


Replicate Postgres to Cockroach We are planning to replicate our Postgres database to Cockroach. We are looking for a relatively simpler solution to achieve that.

Currently, we are working on a Stream based replication (something of this sort) is there any better alternative than this.

CockroachDB PM here. I don't have a very satisfying answer for you, unfortunately; Postgres logical decoding is difficult to implement correctly, and I don't know of anything offhand that satisfies your needs.

Postgres links to a number of off-the-shelf logical decoding plugins, but in my experience these are of varying quality. If you use one of these, you'll want to verify that it properly handles columns that use Postgres's TOAST mechanism -- last time I looked at these, they generally did not. After you get your data streaming out of Postgres, you can consume them and upsert them into your Cockroach database on a separate SQL connection.

If a zero-downtime migration is not required, you could use CockroachDB's IMPORT PGDUMP functionality: use Postgres's own dump functionality to dump your data to a file, and then import it into CockroachDB.


ORA-12516 but max_utilization not reaching limit Our app began to raise ORA-12516, TNS:listener could not find available handler with matching protocol stack errors, and I'm trying to diagnose why.

The workload has not changed, so I think we might have introduced a bug in the application code, but I can't see why. The most obvious would be a connection leak, or unclosed idle transactions, but:

we are using a jdbc pool (tomcat-jdbc-pool), and the max active value is 73, below the 150 processes Oracle is configured to use. So I would have thought the pool would throw an error before Oracle hits its own limits.
do select * from gv$resource_limit; here is what I get:

NST_ID RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
---------- ------------------------------ ------------------- --------------- ------------------ -----------
         1 processes                                       32              40        150                150
         1 sessions                                        32              45        248                248
...

So it seems max utilization never hits the limit.

Does anyone have a tip on how to track this down?

EDIT: I made some progress on this. It turns out that tomcat-jdbc-pool has a query to check connections health. This query was configured as select 1 from dual;. Notice the semi-column, as this is the culprit: removing it fixes the app. I still have to find out what really happens with the ; at the end of the check query. Any advice is welcome.

I think select 1 from dual; is a syntax error. Here's what happens on the wire:

With select 1 from dual a connection's lifecycle is:

Initial establishment
First query (validation does not yet happen)
Return to the pool
Validation: the pool sends select 1 from dual
The database answers: ORA-01403: no data found
Next query

and so on. The connection is normally not closed.

With select 1 from dual; however, the following happens:

Initial establishment
First query
Return to the pool
Validation: the pool sends select 1 from dual;
The database answers: ORA-00911: invalid character
The pool terminates the connection with a normal FIN packet.

The next query then begins with a freshly established connection. Now, as long as the load stays low, you might not even notice this. But under high load, all this reconnecting must exhaust some database resource. Which one, i don't know...


Could I realistically use SQL Server Express as a distributor database for transactional replication? We have a group of users within my company that are running reports directly against our production database.  It has gotten to the point where they are hammering it enough to cause performance issues for the rest of the company.

I'd like to setup a reporting database for them within our local data center.  Our production database is located in a different data center across the country.  Due to the costs I cannot spin up another SQL Server database in the data center that hosts our production database.

In doing my research it seems the recommended approach would be to have the Distributor on a different server than the Publisher DB?

I was wondering this running an instance of SQL Server Express would suffice for the role of a the Distributor database?  Is this remotely feasible?

I was wondering this running an instance of SQL Server Express would suffice for the role of a the Distributor database? Is this remotely feasible?

SQL Server Express cannot serve as a Publisher or Distributor.

SQL Express can only be Subscriber. Refer to : Replication Considerations (SQL Server Express) for more details.

EDIT : To make my answer more meaningful, I am adding more details

Test any scenario that you are going to implement to avoid any surprises !!

  In doing my research it seems the recommended approach would be to have the Distributor on a different server than the Publisher DB?

This is true up-to certain extent only. Its a best practice, but depending on how much data you are replicating and how busy (in terms of activity or transactions) is your publication database and the network latency between publisher-distributor-subscriber, this setup will affect you.

In your scenario, if your database is --

having less or moderate activity then

a local distributor would be sufficient and you can just have a publisher-distributor on one server running Enterprise edition and another subscriber running EE or standard edition (choice is yours). This way you save in terms of hardware and possibly licensing costs.

highly active then

a remote distributor will be a good choice. Suggest you to refer Enhance Transactional Replication Performance

Other thoughts:

Since you are already using Enterprise Edition, why not use Database Snapshots - with caution !!.

From Looking at Database Snapshot Performance

  When using database snapshots, even in SQL Server 2012, there is an overhead associated with the additional writes required for copying data pages to the sparse files for the snapshots. If using database snapshots is a part of your general configuration, I would really be careful about planning the I/O subsystem to meet the workload requirements for concurrent I/O activity to the database snapshot sparse files.

So database mirroring is another option as well and you can create snapshots on the mirrored database and have it as reporting database.

Also, if your business does not want real time data, then a custom solution can be designed that can Extract, Transform and Load the data to another server using SSIS (or any tool of your choice) and that can be used as reporting.


Can I move the undo log outside of ibdata1 in MySQL 5.6 on an existing server? I've been growing concerned about the large size of ibdata1 that can never shrink even when using file-per-table on innodb

Moving the undo log files outside seemed logical but this procedure seems rather complicated:

http://dev.mysql.com/doc/refman/5.6/en/tablespace-splitting-undo.html

Does anyone have hands-on experience accomplishing this on a live server?

This line makes it sound like it is impossible once mysql is already installed and running:

this feature can only be enabled when initializing a MySQL instance

So too late now?

While @ohlin's answer has a decent workaround, it still requires setting up an entire MySQL instance with the undo logs configured the way you want. Then, you have to failover the application to the new MySQL Instance. That still introduces a few minutes of logical downtime from the perspective of the application because of changing the IP addresses in the code or changing DNS.
Your Original Question

Can I move the undo log outside of ibdata1 in MySQL 5.6 on an existing server?

The answer is definitely NO. Why ? Given the following diagram

Look at the following options for the location of the undo logs
OPTION #1 : Undo Logs Inside System Tablespace
By default, the undo logs reside in the system tablespace file known as ibdata1. Undo logs has been the cause of the greatest amount of uncontrolled growth in the face of many transactions (See my post How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?)
To relocate ibdata1, you must configure innodb_data_home_dir and innodb_data_file_path. They are global variables, but they are not dynamic. Since a mysql restart would be required to change these settings, the undo logs buried inside ibdata1 cannot be moved for a live MySQL instance.
OPTION #2 : Undo Logs Outside System Tablespace
There are options to configure the undo logs on different disks:

innodb_undo_logs  : Although you can increase or decrease how many rollback segments are used within a transaction, the number of rollback segments physically present in the system never decreases. Thus you might start with a low value for this parameter and gradually increase it, to avoid allocating rollback segments that are not needed later (MySQL Documentation)
innodb_undo_tablespaces
innodb_undo_directory

Both innodb_undo_tablespaces and innodb_undo_directory are global variables, but they are not dynamic. Since a mysql restart would be required to change these settings, the undo logs outside ibdata1 cannot be moved for a live MySQL instance.
EPILOGUE
When I read this question, it reminded me of a blog from a FaceBook DB Engineer that configured InnoDB on different disks.
I referred to his blog in my post How do I determine how much data is being written per day through insert, update and delete operations?
Even with this idea, it requires configuring the undo logs in a different disk location from the very beginning. Doing this after mysql is running is simply not possible.


query to select data between todays date as well as tomorrows date time upto specific time I have database "raj" with table "pagination"

In table pagination have "id", "actualtime" and "created" field

id - auto_increment
name- varchar
created- datetime

I want to display all rows which is created on todays date display upto yesterdays 06:00 pm.after that the content will be refreshed based on based on a DATETIME field called 'created' that holds the date and time of each record's creation.

this is my query to fetch rows but it is not working properly...plz help me......

$sql = "
    SELECT name
    FROM pagination where created >= DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY)
       ";

This is the expression for 6:00 PM yesterday

SELECT DATE(NOW()) - INTERVAL 6 HOUR;

Craft the SELECT with that expression

SELECT * FROM pagination WHERE
created >= DATE(NOW()) - INTERVAL 6 HOUR;

Give it a Try !!!


Copy every entry from TableA to TableB where the ID is in a textfile containing a list of IDs I have a text file (or an xls) with a 35k lines, each containing a single ID.
I'm required to copy every entry of TableA to TableB where the ID is in said list.

Would it be best if I'd create a new table, with the IDs, in order to use this table TableC in the query as WHERE ID IN TableC? If so, whats the easiest way to fill the new table with the IDs from the txt file?

Or is there a way to use a text file or the list itself in the query?

I thought something like this query should be appropriate, but I have no clue how to use the text file.

SELECT * INTO TableB
FROM TableA
WHERE ID IN text_file

Working on Microsoft SQL Server Enterprise Edition v9.00.3042.00, i.e. SQL Server 2005 Service Pack 2

The easiest way I know of is creating a table in tempdb with a single column (id for instance), then expand the tempdb node in SSMS and find the table. Right click and select "Edit top 200 rows".

A grid will appear. Select the whole first row with the row header on the left, then paste the whole contents of the file.

BCP is an alternative way to load the file contents to the destination table. In this case I don't find it any way easier than copying and pasting the file contents in SSMS.

You could also use OPENROWSET or other techniques to reference the file directly in your query, but that requires copying the file to the server or messing with permissions: too much hassle for a one-off task.


Indexing for OR statement I have the following SQL statement
select id
from table
where  (   p1_user_id='username'
        or p2_user_id='username'
        or p3_user_id='username'
        or p4_user_id='username')
  and turn=0
  and timestamp > 20200101

There are indexes on all the fields mentioned in the where clause (for reasons other than this query).
When this query runs, it doesn't use the indexes. Using explain, it shows the possible indexes, but then doesn't select one.
For testing purposes I added an additional index which is a combination of p1_user_id, p2_user_id, p3_user_id, p4_user_id.
testing this provided the same result, no index actually used.
How do I either index this in a way that will be more efficient , as reading the whole table which is what it is doing now, is obviously not efficient.
Alternatively, is there a way to rewrite the query that would be more efficient ?
edit: Would using 4 union all statements be more efficient way to do this?

The best approach is "normalize your data".

If you cannot or don't want by some reason then try to use 4 separate queries and combine their outputs using UNION:
    select id
    from table
    where  p1_user_id='username'
      and turn=0
      and timestamp > 20200101
UNION
    select id
    from table
    where  p2_user_id='username'
      and turn=0
      and timestamp > 20200101
UNION
    select id
    from table
    where  p3_user_id='username'
      and turn=0
      and timestamp > 20200101
UNION
    select id
    from table
    where  p4_user_id='username'
      and turn=0
      and timestamp > 20200101

Each separate query can use index ((pX_user_id, turn, timestamp) or (turn, pX_user_id, timestamp) are the most suitable), i.e. for total improvement you need to create 4 separate indices. Of course final UNION  will cause filesort, but, if the final amount of output rows is not too huge then you may have an improvement totally (but may not - be ready).

When this query runs, it doesn't use the indexes. Using explain, it shows the possible indexes, but then doesn't select one.

Server can use the index only as compact table version, not as an index - but for this the index must be covering, i.e. it must look similar to (turn, timestamp, p1_user_id, p2_user_id, p3_user_id, p4_user_id, id) - but I doubt that this is reasonable.


SQL Server column type = Date not accepting dates as varchar in insert statemant In T-SQL, I am trying to insert values in a date table, US & French dates. When table is created with columns as DATE, the insert does not work, it errors.

When I switch to VARCHAR(12) instead of DATE for those columns, INSERT works fine

My server is windows 2012, english Microsoft SQL Server 2016 (SP2-CU10) (KB4524334) - 13.0.5492.2 (X64), regional settings France
I googled but not able to find a solution.

Here are 3 scripts, 1st one is the table with columns with DATE type (with which insert statement fails)

CREATE TABLE [dbo].[DimDateTest](
[ShortDate] [date] NULL,
[FullDate] [varchar](20) NULL,
[MonthShortName] [varchar](10) NULL,
[MonthLongName] [varchar](15) NULL,
[FrenchShortYear] [date] NULL,
[FrenchUSLongYear] [date] NULL,
[FrenchLongYear] [date] NULL,
[DateAge] [varchar](20) NULL
) ON [PRIMARY]
GO

Here is the insert statement:

INSERT dbo.DimDateTest
( ShortDate, FullDate, MonthShortName, MonthLongName, FrenchShortYear, FrenchUSLongYear, FrenchLongYear, DateAge)
SELECT CAST(DATEADD(dd, Number.n-1, '1/1/2015') AS DATE)
,CAST(DATENAME(mm, DATEADD(dd, Number.n-1, '1/1/2015')) AS VARCHAR(10)) + ' ' + CAST(DATEPART(dd, DATEADD(dd, Number.n-1, '1/1/2015')) AS VARCHAR(2)) + ', ' + CAST(YEAR(DATEADD(dd, Number.n-1, '1/1/2015')) AS VARCHAR(4))
,CAST(DATENAME(mm, DATEADD(dd, Number.n-1, '1/1/2015')) AS VARCHAR(10))
,CAST(DATENAME(mm, DATEADD(dd, Number.n-1, '1/1/2015')) AS VARCHAR(10)) + ' ' + CAST(YEAR(DATEADD(dd, Number.n-1, '1/1/2015')) AS VARCHAR(4))
, CONVERT(date, CAST(DATEADD(dd, Number.n-1, '1/1/2015') as datetime), 5) as FrenchShortYear
, FORMAT( CAST(DATEADD(dd, Number.n-1, '1/1/2015') as date), 'dd/MM/yyyy', 'fr-FR' ) AS 'FrenchUSLongYear'
, CONVERT(VARCHAR(12), CAST(DATEADD(dd, Number.n-1, '1/1/2015') as date), 7) as 'FrenchLongYear'
,
CASE WHEN DATEADD(dd, Number.n-1, '1/1/2015') BETWEEN '1/1/2015' AND '3/31/2015' THEN 'Age of Goats'
WHEN DATEADD(dd, Number.n-1, '1/1/2015') BETWEEN '4/1/2015' AND '4/30/2015' THEN 'Age of Copper'
ELSE 'Age of the Unknown'
END
FROM dbo.Number
WHERE DATEADD(dd, Number.n-1, '1/1/2015') <= '4/30/2020'
ORDER BY Number.n

and here is the error I am getting

  Msg 241, Level 16, State 1, Line 13 Conversion failed when converting
  date and/or time from character string.

When I drop and recreate the table by switching Columns type from DATE to varchar(12), insert works fine, here is the script for the table

CREATE TABLE [dbo].[DimDateTest](
[ShortDate] [date] NULL,
[FullDate] [varchar](20) NULL,
[MonthShortName] [varchar](10) NULL,
[MonthLongName] [varchar](15) NULL,
[FrenchShortYear] [varchar](12) NULL,
[FrenchUSLongYear] [varchar](12) NULL,
[FrenchLongYear] [varchar](12) NULL,
[DateAge] [varchar](20) NULL
) ON [PRIMARY]
GO

  NOTE : dbo.Number is a number table populated by numbers from 1 to
  10000

What am I doing writing wrong?
Thanks for your help

SQL Server, internally, stores dates in a binary representation. All dates from whatever source are in the same representation. So a date is a date is a date whether it originates in Paris, London or Stockholm.

Since humans aren't great at communicating in binary, by and large, we have to pass values to SQL Server in a form we can understand. This is generally text. The insert statement you show has a great many strings representing dates. Some are parameters to DATEADD, some are predicates. To be able to calculate and compare correctly the server must convert these strings and dates to a common representation. The rules of precedence say that will be the internal date representation.

In order to translate a string to a date SQL Server must know which part of the string to interpret as years, months, days etc. To do that it uses the culture of the current session. This information can be found from a DMV

select language, date_format
from sys.dm_exec_sessions
where session_id = @@spid;

For me this returns

language   date_format
---------- -----------
us_english mdy

I have no problem running your insert statement. However, if I change it thus

set language British;

language  date_format
--------- -----------
British   dmy

I get your error. The same happens for set language French; because they share the dmy format. This leads to colaring the culprit for your error as the upper bounds on the WHERE and BETWEEN clauses. Either change the session settings to match your query (above or DATEFORMAT only) or vice versa, or use CONVERT with styles to tell SQL Server how to parse your strings.

I couldn't get it to work by changing the date columns to varchar. Don't know what's going on there.

There is a deeper point here, however. You jump through a great many hoops to get your insert data just right. This is wasted effort. No matter how the data is presented it is stored internally in a single way.

In particular those three "French" columns are all defined as date. Date is an internal representation. When you eventually select these rows back you'll find they're all presented in the same way.

What you're trying to achieve is date formatting not date storage. That is properly the responsibility of the UI or report-printing software. SQL Server does not store dates in any format. It has a binary representation.

That said, it does offer the CONVERT and FORMAT functions for when they must be used. You can use this and computed columns to achieve your outcome.

drop table if exists #t;

create table #t
(
    d date,
    FullDate as FORMAT(d, 'MMMM dd, yyyy'),
    MonthShortName as FORMAT(d, 'MMMM'),
    FrenchLongYear as FORMAT(d, 'MMM dd, yy')
);

insert #t(d) select GETDATE();

select * from #t;

d           FullDate        MonthShortName  FrenchLongYear
----------  --------------  --------------  --------------
2020-04-02  April 02, 2020  April           Apr 02, 20


Oracle expdp et impdp : order of schemas? When I try to export and then import many schemas from one Oracle DB to another one, I get errors because grants and synonyms cannot be recreated in the target DB; the reason is probably that the schemas are not imported in the right order.

So :

With impdp, is it possible to define the order in which the schemas are to be imported ?
If not (and I think that impdp simply uses the order in which the schemas were found in the dump file) : is it possible during the expdp to define the order in which the schemas are to be exported ?

You have no direct control over the order on objects creation. Anyway, you can try to perform a full export, and then, run an impdp only of the schemas you need.
For example:

expdp system/**** full=y directory=<dir> dumpfile=<dumpfile>

and then import all the schemas you want (in the order you need):

impdp system/**** directory=<dir> dumpfile=<dumpfile> schemas="schema1, schema2, .. , schemaN"

I have not done a test, but you can give it a try.

Hope it helps.


Install MariaDB without password prompt in Ubuntu I need to install MariaDB server in Ubuntu without password prompt. For this purpose, I execute the following commands, but it doesn't gave me any progress. It again shows password prompt.

I used the following commands :

  export DEBIAN_FRONTEND=noninteractive
  echo mariadb-server-5.5 mariadb-server/root_password password mypass | debconf-set-selections
  echo mariadb-server-5.5 mariadb-server/root_password_again password mypass | debconf-set-selections
  sudo apt-get -y install mariadb-server

I also check some links in stack overflow, but it doesnot worked:

https://stackoverflow.com/questions/8138636/install-mysql-on-ubuntu-natty-without-password-prompt-and-using-shell-variable-a

https://stackoverflow.com/questions/7739645/install-mysql-on-ubuntu-without-password-prompt

Please help me on this regard. Have any wrong with my code?

A mixture of the mysql/mariadb-Nameing has worked for me:

export DEBIAN_FRONTEND=noninteractive
debconf-set-selections <<< 'mariadb-server-5.5 mysql-server/root_password password rootpass'
debconf-set-selections <<< 'mariadb-server-5.5 mysql-server/root_password_again password rootpass'