How to backup databases to ensure data recovery I have 15 customer databases that have minimum replication at different sites.  I currently do nightly back-ups and transfer the database back-up nightly to our main server via 7zip.  We recently had a hard drive crash at 6PM which means we lost all data in the database from 2AM - 6PM that day.  How can I back-up our databases throughout the day without compromising the functionality of the database during the day? We do over 1,000 transactions daily so we are constantly writing to the database.  My current method is not realistic for hourly back-ups as performance would suffer on the machine.

I am noting the other answers here, but I don't believe any of them (at this moment) is a complete answer / solution to your problem.  Yes, you can backup to a remote location, but that is not always the best way to do it.  Backing up to a network location can be murderous to your bandwidth.

Transaction Log backups is very important and is the right way to go, and they can be off-setted with differentials.  But there is usually some theory and planning that needs to go into your implementation thereof.

Differential and Transaction Log backups should cause a lot less performance issue for you, as the backups are a lot smaller.  Differential Backups only contain changes made since the last full backup, and Transaction Log Backups only contain transactions done since the last full, differential or transaction log backup (normally just a few MBs).

Based on the details you have provided, there are over 1000 transactions done per day.  On a normal 8 hour work day, this calculates to just over 2 transactions a minute.

Before you implement any backup plan, you need to determine (as a business) what your Recovery Time Objective (RTO) and Recovery Point Objective (RPO) is.

RTO relates to the maximum amount of time you are willing to lose during a disaster, to restore normal operations.

RPO relates to the maximum amount of data that you willing to lose during a disaster.

In most cases, the lower the RTO, the higher the RPO and vice versa.

With the metrics above, you should then decide what the best backup plan is to implement.

For e.g.:

PLAN A: (Low RPO, i.e. minimal data loss)

Daily Full Backups
4 Hourly Differential Backups
Transaction Log Backups every 10 minutes

No more than 10 minutes of data will be lost, but the restoration process might be complexed and therefore time-consuming (close to 30 backup files to restore from)

PLAN B: (Low RTO, i.e. minimal downtime)

Daily Full Backups
12 Hourly Differential Backups
4 Hourly Transaction Log Backups

Up to 4 hours of data could be lost, but the restoration process would be faster, with only a max of 4 files to restore

Importantly, you should note that if you back up your databases to the same drive as where your data is, and you have a catastrophic drive loss, you are likely to lose the backups too.  Always have your backups on a separate drive from data and logs.

Suggestions to improve / edit this answer is welcome!


Reaching a Microsoft SQL Server 2017 cluster from another device Let say I have a python service in one instance. This service needs to reach a MSSQL cluster of 5 nodes that is in an other location, with different IP addresses. Cluster creds:
user: my_user
password: my_password
Database: my_database
What is the IP address that I should provide to my python code so that it can reach this cluster? Indeed, there are many different IP addresses in this cluster (one for each node).
In many storage engines, there are the notions of master/primary and slave/secondary, but I don't know much more, especially for Microsoft SQL Server.

Assuming the MSSQL cluster is setup on windows, it will have a virtual IP address which is different than the IP addresses assigned to each node. This virtual IP address will always point to the active node. The application can connect to the SQL server using this virtual IP address.


Denormalize Design Modification Our DBA is on medical leave, I am just a developer trying to do my part. Initially, the bean counters were going to use quickbooks. However, after seeing how much time they could save with custom development they have creeped my scope.

Rather than having a quantity of a particular type of shoe in a given size (ShoeInventory.Quantity) each individual pair of shoes needs to be accounted for and thus be given an Id, LeatherCost, ManufacturingCost, InventoryCost and TotalCost.

My question: Is there any foreseeable problem if I just move LeatherCost,  ManufacturingCost, InventoryCost and TotalCost  from the Shoe table to the ShoeInventory table?

When an order of shoes arrives from a ShoeOrder I will then just add a record to ShoeInventory for each pair ordered rather than updating a quantity?

I have no reason to think that this is a bad idea but it seems to easy to actually be the right answer.

If you move these additional costs to ShoeInventory then they will be associated with your ShoeInventory entity. This entity also has a relationship with ShoeSize. If I understand your schema (and I might not, so correct me if I'm wrong), the implication of this is that different sizes for the same shoe type could potentially have different sub-costs. This might give you extra flexibility, or it might make things more complicated. If you want to separate this data from shoes and don't want it attached to any other table (in case it doesn't make sense), you could create a shoeCost table:

shoeCost
---------
  id (PK)
  shoe_id (FK to shoe.shoeId)
  leather_cost
  manufacturing_cost
  inventory_cost
  total_cost (this could be a computed column of
              leather_cost + manufacturing_cost + inventory_cost,
              if it makes sense to do that)


Relational database field name design Are there design patterns for naming table fields? For example, I've always used user (table)> email (field), but I see other well-used modes like user (table)> user_email (field). Are there any rules / conventions to follow?

It doesn't really matter what naming convention you use, however it is really important to have one and stick to it. As long as you're strict about using it, you'll have a clean/readable database.

A couple of useful tips:

Using tbl_xx or v_ for tables/views might be a bad idea, cause tomorrow, you might change the view to a table or visa versa. Which could end up being really confusing a couple of years from now.
Try to give meaningful names to columns, for example an email field should not be named user_e, but user_email, or just email.
Correct use of underscores or capitalization, bigfieldbuthardtoread vs BigFieldButEasyToRead vs Big_Field_But_Easy_To_Read
Don't prefix procedures with sp_, can cause some performance issues (it will first look in the master for that sp)

If you don't want to make up your own system, you can always try to find one online and make that your own, for example this one


generate array of unique uuid in postgreSQL I would like to insert array of unique uuid values in table with uuid column as primary key. Table structure is

CREATE TABLE Organisation (uuid id PRIMARY KEY DEFAULT uuid_generate_v4());

e.g. I have 2 organisations, so I am trying to implement it with query looks like

INSERT INTO Organisation (uuid) VALUES (unnest(array_fill(uuid_generate_v4()::uuid, ARRAY[2]))) RETURNING uuid as uuid_org;

It fails with

ERROR: duplicate key value violates unique constraint "organisation_pkey"

because query fairly tries to insert two duplicated not-unique values, e.g.

{711ec909-fc2c-45aa-8912-39207788768e,711ec909-fc2c-45aa-8912-39207788768e}

My trick works when RETURNING ids or uuids while inserting in tables with not the only column - I pass the uuid column and query automatically generate unique uuids that I can aggregate (see below for table TimeSlice).

Generally, my idea is to insert simultaneously in several tables using WITH:

WITH ins_ts as (INSERT INTO TimeSlice (validTimeBegin) VALUES
             (unnest(array_fill('2020-01-02'::datetype, ARRAY[2])))
             RETURNING id as id_ts),
     ins_org AS (INSERT INTO Organisation (uuid) ... ---my fail query ),
INSERT INTO OrganisationTimeSlice (idTimeSlice, uuid, name)
            VALUES (unnest(array(select array_agg(id_ts) from ins_ts)),
    (unnest(array(select array_agg(uuid_org) from ins_org))),
                     unnest(array['name1', 'name2']));

Where I am wrong?

Assuming you don't actually want them in an array, since you are unnesting it right away, here's how to generate a table of any amount of UUIDs:

WITH RECURSIVE genx(n, uu) AS (
  VALUES (1, uuid_generate_v4())
  UNION ALL
  SELECT n + 1, uuid_generate_v4()
  FROM genx WHERE n < 30
) SELECT uu FROM genx;

Change the 30 to as many as you want!


DB2 : Alter table: CLOB to VARCHAR I have a Table which some nut head had created. The  data in it is not more than varchar(100) but he has created the column as CLOB.

Need to change it to Varchar (100). The below query does not seem to work.

ALTER TABLE test alter Column VARCHAR_CLOB set data type VARCHAR(100);

it gives the following error

ALTER TABLE "BASE.test" specified attributes for column "VARCHAR_CLOB" that are not compatible with the existing column.. SQLCODE=-190, SQLSTATE=42837, DRIVER=4.18.60

You cannot change a LOB type to a non LOB type and vice versa (I assume LUW here):

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000888.html

[...]
SET DATA TYPE altered-data-type
Start of changeSpecifies the new data type of the column. The new data type must be castable from the existing data type of the column (SQLSTATE 42837). A LOB column cannot be altered to a different data type (SQLSTATE 42837). A non-LOB column cannot be altered to a LOB data type (SQLSTATE 42837).End of change.

One way is to unload the data with export:

db2 "export to t.ixf of ixf select ..., cast(varchar_clob as varchar(100)), ... from t"

then drop all foreign key pointing to this table, drop the table, create the new table, load the data back to the table and finally add the foreign keys that where dropped.

An alternative approach is to drop the foreign keys, rename the table, create the new table and use load from cursor. When done drop the renamed table


i am trying to get total amount from this query SELECT ( ( `salary_log`.`emp_id`
          ,`salary_log`.`basic`+`salary_log`.`da`+`salary_log`.`allowance`)
        -( (`salary_log`.`deduction`-`provident_fund_info`.`pf_own_value`)
          +`salary_log`.`adjustment`+`salary_log`.`fine`
          +`salary_log`.`tax`+`provident_fund_info`.`pf_own_value`
         )
       )
FROM `salary_log`
       INNER JOIN `provident_fund_info`
          ON `provident_fund_info`.`emp_id`=`salary_log`.`emp_id`
WHERE `salary_log`.`generate_date`='2017-12-01'
  AND `salary_log`.`statement_type`='salary';

  1 queries executed, 0 success, 1 errors, 0 warnings

  Query:

SELECT ( ( `salary_log`.`emp_id`
          ,`salary_log`.`basic`+`salary_log`.`da`+`salary_log`.`allowance`)
        -( (`salary_log`.`deduction`-`pro...

  Error Code: 1241

  Operand should contain 1 column(s)

Error occurred due to a column which is not part of the equation is inside the parenthesis.

Try this:

SELECT
    s.emp_id,
      (s.[basic] + s.da + s.allowance) -
        ((s.deduction - p.pf_own_value) +
          s.adjustment + s.fine + s.tax +
           p.pf_own_value) AS solution
FROM salary_log s
INNER JOIN provident_fund_info p ON p.emp_id = s.emp_id
WHERE s.generate_date = '2017-12-01'
    AND s.statement_type = 'salary';


Things to consider with a bigger table I have a table that has around 12 billion rows (row size 32 B) which grows each month by 300 million rows. I have setup partitions for each month and each month has 8 data files (since I currently have only 8 CPU's), to store the data I have a SAN attached.
The machine (W2008R2 64bit with MSSQL2008R2) has 192 GB RAM but I still feel it is slow when retrieving data while using the clustered index. I plan later on to use this data with SSRS. What should I consider to increase performance ?

You mentioned that you have the table (technically clustered index) partitioned.

Does your query use the clustered key(s) as part of the search predicate?  If not, you are going to get scans, as opposed to seeks, against the partitioned index.

Do you have any nonclustered indexes?  Are they also partition-aligned?

Have you investigated your query execution plan to see what is happening under the hood of your poorly performing query?

Could you post your DDL, sample data, and your query, so we can better help you?


Disadvantages to using ON DELETE CASCADE on every foreign key? Our application currently has "soft delete" built-in to most tables with a BIT "deleted" column.
There is now a requirement for "hard delete" functionality.
Are there any disadvantages, gotchas, or things to bear in mind when adding ON UPDATE CASCADE ON DELETE CASCADE to every single foreign key in the database?

Are there any disadvantages, gotchas, or things to bear in mind when adding ON UPDATE CASCADE ON DELETE CASCADE to every single foreign key in the database?

One reason not to : Oops!!
If you accidentally delete the wrong record, then you wipe out everything that references on that record.  If that record happens to be the one at the root of your entire company, then everything goes "Poof!", everybody starts creaming at you and you're into a major, high-profile, high-pressure Recovery exercise.
Personally, I advocate never cascading deletes.
You want to get rid of data?  You need to know what you're doing! (Or some, centrally managed, piece of code needs to know how to do so on your behalf).


Find which account SQL Server service is running BACKUP under Our DBA is out and I'm trying to back up our (remote) dev database to a UNC path. I'm running the back up operation while logged into Management Studio as a particular Windows domain account (e.g. CORP\myUser). I've checked that this domain account has FULL CONTROL rights the UNC path \\nameOfMachine\backup\.

When I attempt to execute the following as CORP\myUser

BACKUP DATABASE [DEV] TO  DISK = N'\\nameOfMachine\backup\dev.bak' WITH COPY_ONLY, INIT;

I receive the error

Cannot open backup device '\\nameOfMachine\backup\dev.bak'.
Operating system error 5(Access is denied.).

Which seems to indicate that whatever account SQL Server is using to run the backup command does not have rights to that UNC path.

I do not have a way to RDP to the dev database server and find out which account SQL Server is using to execute the backup command above. Is there a way to find this out from Management Studio using e.g. a dynamic management view?

BACKUP will access the UNC under an impersonation context. This is a 'double-hop' situation and requires Kerberos constrained delegation to be configured.

This is already answered at https://stackoverflow.com/questions/18749224/restore-database-from-a-shared-folder


AWS Postgres RDS dumping data into sql format My team has hosted a django project in AWS with a Postgres RDS instance. What is the proper way to get export the database into SQL or pg_dump?

There is an interesting thing in RDS postgresql.  I had the same issue, normal pg_dump won't work. Use the below command

pg_dump -Fc -v -h [endpoint of instance] -U [master username] [database] > [database].dump

Refer link: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Procedural.Importing.html


OLEDB provider for linked server reported change of schema version error I am trying to run a report but its giving this message:

An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'ParamYear'. (rsErrorExecutingCommand)
For more information about this error navigate to the report server on the local server machine, or enable remote errors

I checked in Visual studio and I get this error:

    OLEDB provider for linked server sql10 reported change of schema version between
compile time("182424452472301") and run time ("182454520418731")
for table dbo.stud

Query:
SELECT DISTINCT Funding_Type
FROM            Destinations_1415_1516_Union
WHERE        (Year IN (@Year)) AND (AgeBand IN (@AgeBand)) AND (Completion_Status IN (2, 3))

Kindly let me know how to resolve this

What build of 2008 r2?  Have you looked at this connect?
https://connect.microsoft.com/SQLServer/feedback/details/672153/the-ole-db-provider-sqlncli10-for-linked-server-x-reported-a-change-in-schema-version-between-compile-time-and-run-time
Have you tried the workaround listed?
Workaround (From above connect):

I solved my problem with DBCC FREEPROCCACHE (ref http://msdn.microsoft.com/pt-br/library/ms174283.aspx)
Execute the first scenario where you delete the cache for the query that is causing the issue.


Train Direct Route Query Let's say I've a table trainStations with the following:

 startStation  | endStation
 --------------+--------------
 SF            | Palo Alto
 Palo Alto     | SF
 Mountain View | Redwood City
 SF            | Palo Alto
 Palo Alto     | Mountain View
 Mountain View | SF

Now, I'm trying to learn how to do a round trip query.
For example, to query SF --> PA I'd use:

SELECT *
FROM   trainStations
WHERE  startStation = 'SF'
AND    endStation = 'Palo Alto'`

But how do I query for round trip (with no additional stops)?

For example: SF--> Palo Alto --> SF

It should return the two lines:

 startStation  | endStation
 --------------+--------------
 SF            | Palo Alto
 Palo Alto     | SF

Only SF -> Palo Alto followed by Palo Alto -> SF rows are valid (direct route). The last three rows (in the table above) will not be selected because it's not a direct route (stop in Mountain View).

If this was code I'd do something like:

if (startStation[row num] == endStation[row num+1])
    && (endStation[row num] == startStation[row num +1):
then print "found direct route"

I'd suggest you another approach:

create table TrainStations
(
    RouteId int,
    OrderId int,
    StartStation varchar(50),
    EndStation varchar(50)
);

By adding Route and Order (Stops), you can get the whole journey of no matter what route.

    SELECT RouteId,
           OrderId,
           StartStation,
           EndStation
      FROM TrainStations
     WHERE RouteId = 3
  ORDER BY RouteId, OrderId;

ROUTEID | ORDERID | STARTSTATION  | ENDSTATION
------: | ------: | :------------ | :------------
      3 |       1 | SF            | Palo alto
      3 |       2 | Palo alto     | Mountain View
      3 |       3 | Mountain View | SF

dbfiddle here

Or if you want a single line:

    SELECT CONCAT(CONCAT (t1.StartStation, ', '),
                  (SELECT LISTAGG(t2.EndStation, ', ') WITHIN GROUP (ORDER BY t2.OrderId)
                   FROM TrainStations t2
                   WHERE t2.RouteId = t1.RouteId)) Route
    FROM   TrainStations t1
    WHERE  t1.RouteId = 3
    AND    t1.OrderID = 1;

| ROUTE                            |
| :------------------------------- |
| SF, Palo alto, Mountain View, SF |

dbfiddle here


Electricity off while shrinking database, how can I recover my database? While i execute shrink database, the electricity has been gone down, I can't access my database now, it is (In Recovery), how can i recover it?? Note: it is a very huge database: 25 GB.

EDIT:

My question on serverfault.com: https://serverfault.com/questions/395067/electricity-gone-off-while-shrinking-database-how-can-i-recover-my-database

Wait patiently for it to recover.  This is what is supposed to happen.  While waiting, try to track down your latest backup, just in case.  I suggest a small investment in a UPS will avoid this problem in the future.


Building test site, update dates (back date records) I have 3 tables that all connect and I need to back date them in an MS SQL 2008 setting.
I was thinking about how to do this and not sure if I am on the correct path.  The big problem I have is I have columns that are null, and I want to keep them null if they are.  Otherwise I want to adjust the date by 1,2,3 days depending on value....
  Here is what I am trying, it adjusts the 2nd and 3rd tables, but the first I assume is failing and I am not understanding why.

    BEGIN TRANSACTION
declare @NumberOfDays int = -1
declare @Subject varchar(70) = 'Daily Nurse Rounding -1'
declare @newSubject varchar(70) = 'Daily Nurse Rounding'
UPDATE hdIssues
SET hdIssues.IssueDate = IsNull(hdIssues.IssueDate,DATEADD(day,@NumberOfDays,hdIssues.IssueDate)),
hdIssues.StartDate = IsNull(hdIssues.StartDate, DATEADD(day,@NumberOfDays,hdIssues.StartDate )),
hdIssues.ResolvedDate = IsNull(hdIssues.ResolvedDate, DATEADD(day,@NumberOfDays,hdIssues.StartDate )),
hdIssues.LastUpdated = IsNull(hdIssues.LastUpdated, DATEADD(day,@NumberOfDays,hdIssues.StartDate )),
hdIssues.DueDate = IsNull(hdIssues.DueDate, DATEADD(day,@NumberOfDays,hdIssues.StartDate )),
hdIssues.AcknowledgeDate = IsNull(hdIssues.AcknowledgeDate, DATEADD(day,@NumberOfDays,hdIssues.StartDate ))
Where
hdIssues.Subject = @Subject

UPDATE hdComments
SET hdComments.CommentDate = DATEADD(day,@NumberOfDays,hdComments.CommentDate)
WHERE hdComments.IssueID in(select hdIssues.IssueID from hdIssues where hdIssues.Subject = @Subject)

UPDATE hdStatusChangeHistory
SET hdStatusChangeHistory.ModifiedDateTime = DATEADD(day,@NumberOfDays,hdStatusChangeHistory.ModifiedDateTime)
WHERE hdStatusChangeHistory.IssueID in(select hdIssues.IssueID from hdIssues where hdIssues.Subject = @Subject)

UPDATE hdIssues
SET hdIssues.Subject = @newSubject
where hdIssues.Subject = @Subject

I think I got it.....

SET hdIssues.IssueDate =  COALESCE(DATEADD(day,@NumberOfDays,hdIssues.IssueDate),null),
hdIssues.StartDate =  COALESCE( DATEADD(day,@NumberOfDays,hdIssues.StartDate ),null),
hdIssues.ResolvedDate =  COALESCE(DATEADD(day,@NumberOfDays,hdIssues.ResolvedDate ),null),
hdIssues.LastUpdated =  COALESCE( DATEADD(day,@NumberOfDays,hdIssues.LastUpdated ),null),
hdIssues.DueDate =  COALESCE( DATEADD(day,@NumberOfDays,hdIssues.DueDate ),null),
hdIssues.AcknowledgeDate =  COALESCE( DATEADD(day,@NumberOfDays,hdIssues.AcknowledgeDate ),null)

Coalesce seemed to work for me, I had no idea what coalesce was before but saw it in this example and read up on it here

Anyone know of any drawbacks to this approach?


Create incremental/differential backups in ProgressDB Is there a way to create incremental/differential backups in ProgressDB?

Look here for the Progress docs: http://communities.progress.com/pcom/docs/DOC-16074

The manual you're looking for is the "Database Administration", and search for "incremental backups"


Measuring IOPS in SQL 2014 We have a LANDEsk migration going on where several servers are being consolidated into one SQL 2014 Cluster. What would be the best way to calculate IOPS for such a scenario? The data is still being migrated.

I am also following Brent's url here which is very good in cleaning up perfmon data.

You have 2 options, perfmon or get the information directly from SQL. Perfmon is point-in-time, SQL's stats are aggregated since the last service restart.

For Perfmon, you'll want to use Physical Disk and provide them the following information:

Disk Reads/Sec + Disk Writes/Sec = IOPS

Disk Reads Bytes/Sec + Disk Writes Bytes/Sec = Throughput

Make sure you have enough data to fully represent your workload. For instance, if you have off-hours processes that run, like a big ETL process that runs overnight or on the weekend, make sure those are captured as well. Your storage team can't spec for something if they don't know the need exists.

You can also get this information directly from SQL. Remember this aggregated, so you'll need to take periodic snapshots and calculate the diffs. The one advantage with this method is you can break down the Stats by Database. You may find that one database has much higher IOPS/Throughput needs and this will give your Storage Admins more information in case they want to deal with it differently.

This article is a good starting point on how to capture snapshots of virtual_io_stats:  https://www.mssqltips.com/sqlservertip/1416/gather-io-statistics-down-to-the-sql-server-database-file-level/


Slow query log is not storing slow queries Using 5.6.35 MySQL Community Server (GPL). I have setup the local MySQL instance as follows

show variables like '%slow%query%log%'

slow_query_log  ON
slow_query_log_file /usr/local/mysql/data/MacBook-Pro-slow.log

general_log ON

but I don't see any queries being logged in

SELECT * FROM mysql.slow_log;

SELECT * FROM mysql.general_log

Both the tables are empty. I am running queries that are running for more than 20 seconds, and my threshold is set fine. Any advice?

There are two ways to collect general and slow logs:

File (this is the default)
Table (this needs to be specifically enabled)

Based on the info you provided, it looks like MySQL is using the default value for log_output which is "Log output goes to file(s) defined by general_log_file and slow_query_log_file". You can verify this by looking at these files (look in folder /usr/local/mysql/data/ and see what *.log files are there)

If you want the logs to go to database, on a MySQL client when logged in as a privileged user e.g. root:

SET GLOBAL log_output='TABLE';
SET GLOBAL general_log=ON;
SET GLOBAL slow_query_log=ON;
SET GLOBAL long_query_time=0;

The above will send global and slow logs to mysql.general_log and mysql.slow_log tables.

Note that I have set long_query_time to 0 seconds i.e. every query goes to slow_log(that is lot of output into the table). You can change this to another value that suits your use case.

Also, general_log produces a huge amounts of logs so you should only enable it for certain amount of time.


Incremental Backup. Base backup creation frequency? So I'm trying to understand how Continuous Archiving and Point-In-Time-Recovery works.

From what i've read there are some steps involved for the incremental backup and some steps for the recovery:

Summary of PostgreSQL Backup Steps

Modify postgresql.conf to support archive log
Make a base backup (full database backup)
Copy base backup to remote storage.
Backup WAL (archive log files) to remote storage (continuous process)

Summary of PostgreSQL Point-in-time Recovery Steps

Extract files from base backup
Copy files from pg_xlog folder
Create recovery.conf file
Start Recover

So my questions is: Is it safe to stick with this base backup forever (plus all the wal files) until an error appears, and then apply the PITR steps?  Or should I re-generate the base backup periodically (each week, month?) in order to minimize any type of error when trying to recover the database?

Thanks in advance.

Your step 2 in PITR should not be there.  You might want to remove the files from the restored copy of the base backup's pg_xlog.  You don't want to copy them.  (And there really shouldn't be anything to copy/move/remove, as you shouldn't be backing up that directory to start with).

If you only take one base backup and keep it forever (or until you upgrade to the next major version of PostgreSQL), then doing PITR will take longer and longer as you accumulate more archived log files.  This is the driving force in how often to take base backups: how long you are willing to wait for recovery.  Also, the more log files you need to replay during recovery, the more likely it is that one of them is going to somehow get lost or corrupted.

If you do decide not to take frequent base backups (or even if do take them frequently), make sure you test your PITR occasionally onto a test server.  This will both verify all your log files are intact, and give you evidence of how long it will take should you need to do a real recovery.


Delete unassigned shards in Elasticsearch I had an elasticsearch server, which ran in single node mode.
When dataset reached 1TB, I added second node and relocated couple of shards with reroute api.
Now second node has 2 of 5 shards, but first node still holds all 5 shards and space is not reclaimed.
_cat/shards?v command shows:
new_messages                    3     p      STARTED    974698739 256.6gb 5.188.130.61  el01
new_messages                    3     r      UNASSIGNED

I've found some "solutions" like stop ES and delete files by hand, but I don't like them.

Probably the solution is to delete shards with replica status.
curl -XPUT "localhost:9200/myindex/_settings?pretty=1" -H 'Content-Type: application/json' -d' {"index" : { "number_of_replicas": 0 } }'


What is the best way to find out how many records do not have a proper phone number in SQL Server? I have a field that people historically could use to freely supply a value. I need to identify the records which do not have an appropriate value so I can clean them.

I've been looking around in SSIS for this. My inclination is to use a script component with a regex expression in some C# code, followed by a redirect. Still, I was wondering if there was a way in SSIS to do this without resorting to C#.

I haven't had a lot of luck finding a way to find out how many records do not have appropriate phone numbers.

It is one time task.
Write a small application or just select script, use C#, VB.NET, T-SQL...and analyze all patterns manually.
Maybe you will find out common patterns by operators who inputed this data. Add an algorithm for every pattern. Apply your patterns to real data in a database. Remove all "wrong" records.

Luck.

ADDED:

As option, you can use database of telephone numbers to check person2number validity if it's possible.


MongoDB: Is it possible to search for two fields whose array sizes don't match? I have a collection that looks something like the following (I have simplified it a lot). I need to query any record that doesn't have the same number of customersAccepted entries as customersInvited.

/* 1 */
{
    "_id" : ObjectId("5d7b8ab0f1e55a2e4b6ccfa2"),
    "title" : "Service AT19",
    "customersInvited" : [
        429409,
        429410,
        900
    ],
    "customersAccepted" : [
        429409,
        429410,
        900
    ],
    "groupId" : null,
    "deleted" : true,
    "deletedDate" : ISODate("2019-10-12T05:59:08.135Z"),
}

Is this possible with the $size operator? I'm familiar with it when looking for a fixed number, so my Plan B will be to add two separate fields called numAccepted and numInvited, then update that when the record is modified and do something like:

db.collection.find( { "numInvited": { $ne: "numAccepted" } } );

I'd really rather not maintain additional fields I don't need if it is possible to compare array size directly though. Any advice you can give on this would be greatly appreciated.

Is this possible with the $size operator? ... so my Plan B will be to
  add two separate fields called numAccepted and numInvited ...

The $size operator matches any array with the number of elements specified by the argument. In this case you have to compare the size of two array fields within the same document. You cannot use MongoDB Query Language (MQL) operators for this purpose.

The aggregation $size array operator returns the length of an array. To use aggregation operators within a find query, you have to use the $expr operator. Here is how it is done:

db.collection.find( { $expr: { $ne: [ { $size: "$customersInvited" }, { $size: "$customersAccepted" } ]  } } )

Note the $size and the $ne used in the above query are aggregation operators.


Galera, MariaDB and multiple datacenter Looking into using Galera and MariaDB across 3 data centers. We have a metroE comnection between the sites. Connectivity is pretty stable, but my concern is if a WAN link goes down at one of the sites, and I am writing to the local nodes.

How does it handle that situation?

How many nodes at minimum at each site is recommended?

This question was similar, but different:
 https://stackoverflow.com/questions/27777134/galera-cluster-replication-between-two-data-centers/27793927#27793927

If the WAN link breaks, the cluster will split into 2 clusters. The bigger cluster will be a PRIMARY cluster, meaning that it will work properly. The other partition won't accept any write until it re-joins with the PRIMARY cluster and the data in the nodes are updated.

Note that it is very important to set a good value for evs.suspect_timeout. Before this timeout occurs, the whole cluster will wait a response from unreachable node. After that timeout occurs, the cluster is partitioned. If the value is too low, slow nodes may cause a cluster partitioning. If the timeout is too high, working nodes may wait uselessly for a response from unreachable nodes.


Delete and Recreate Indexes in MySQL to reduce table size for copying the db to a new server? I have to move a given database of 100gb from server a to server b and later this year to server c with a copy for testing to server d.

So all servers are Unix (mostly Debian) with MySQL.
The databases are MyISAM.

My biggest table, which holds 99% off all data, is 47gb of data (MID) with additional 50gb of indexes (MIY).

My idea is to:

1) Tar the database files
2) copy to new server
3) un-tar it
4) copy it to new mysql folder

My question:

Can I delete the index-file and recreate the indexes on the new server to reduce the copy and tar-time by 50%?

You asked

  Can I delete the index-file and recreate the indexes on the new server to reduce the copy and tar-time by 50%?

Yes, you can. You copy just the .MYD into the tar file. You will need a blank .MYI file.

SUGGESTION

Suppose you are moving mydata.mytable. You will have

/var/lib/mysql/mydata/mytable.frm
/var/lib/mysql/mydata/mytable.MYD
/var/lib/mysql/mydata/mytable.MYI

STEP 01 : Make Copy of Table Structure

CREATE DATABASE IF NOT EXISTS myjunk;
CREATE TABLE myjunk.mytable LIKE mydata.mytable;

STEP 02 : Copy Real Data and Blank Index File

COPY S1:/var/lib/mysql/mydata/mytable.frm ->  S2:/var/lib/mysql/mydata/mytable.frm
COPY S1:/var/lib/mysql/mydata/mytable.MYD ->  S2:/var/lib/mysql/mydata/mytable.MYD
COPY S1:/var/lib/mysql/myjunk/mytable.MYI ->  S2:/var/lib/mysql/mydata/mytable.MYI

STEP 03 : Rebuild Index File on Target Server

REPAIR TABLE mydata.mytable;

GIVE IT A TRY !!!


Ms-access ODBC calls for linked table behaviour I'm curious if anyone knows how ms-access goes about generating ODBC calls for linked tables.

The reason I ask is that I'm trying to make the front-end of a system super efficient and too minimise the number of ODBC calls. I'm using MySql and have been performing some tests and looking at the general log from the MySql server. I was surprised at how many calls were made by the front-end.

a very simple example

Create the following test data in MySQL:

CREATE TABLE table1 (table1_id int(11) NOT NULL,
    column1 int(11) DEFAULT NULL,
    column2 int(11) DEFAULT NULL,
    column3 int(11) DEFAULT NULL,
    PRIMARY KEY (table1_id)
) ENGINE=InnoDB;

INSERT INTO sales.table1 (table1_id) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11);

Now create a linked table in ms-access to table1 and open it. This is what appears in the general log file:

2011-05-29 16:58:00, SELECT `table1`.`table1_id` FROM `table1`
2011-05-29 16:58:00, SELECT `table1_id`,`column1`,`column2`,`column3`  FROM `table1`  WHERE `table1_id` = 1
2011-05-29 16:58:00, SELECT `table1_id`,`column1`,`column2`,`column3`  FROM `table1`  WHERE `table1_id` = 1 OR `table1_id` = 2 OR `table1_id` = 3 OR `table1_id` = 4 OR `table1_id` = 5 OR `table1_id` = 6 OR `table1_id` = 7 OR `table1_id` = 8 OR `table1_id` = 9 OR `table1_id` = 10
2011-05-29 16:58:00, SELECT `table1_id`,`column1`,`column2`,`column3`  FROM `table1`  WHERE `table1_id` = 11 OR `table1_id` = 11 OR `table1_id` = 11 OR `table1_id` = 11 OR `table1_id` = 11 OR `table1_id` = 11 OR `table1_id` = 11 OR `table1_id` = 11 OR `table1_id` = 11 OR `table1_id` = 11

Rather than retrieve all the data in one hit which I could do with a stored procedure it seems to require 4 steps:

1) get a list of the primary keys from table1
2) Request the 1st 10 records by explicitly selecting them in the where clause.
3) Retrieve the next 10 records by explicitly selecting them in the where clause.
4) Because the 1st record has the focus upon opening the table, requery the 1st record again

Notice the odd behavior in step 3 where the criteria table1_id=11 is repeated 10 times.

The only way to get complete control over the query for ODBC is to create them as pass-through queries.

In that mode, Access will not touch them and the SQL will be passed verbatim to the server (so your queries will need to be written in that server's particular SQL syntax or you'll get errors).

In ODBC mode, Access will decompose queries that are bound to controls into batches so it never returns more results than it need to display (that is what you see: it will decompose queries based on the primary key to return specific records only).

So you have to be careful of what you wish: if you optimise your queries using pass-through, you will get less calls, but all the data will be returned.

On the other hand, if you leave Access do the querying for you, it may be chattier, but the net performance may be better since less data may be returned more quickly.

You need to test both and choose what works best depending on the exact query and how it is used.


MySQL stored procedure updates twice I have this stored procedure:

DELIMITER $$
CREATE PROCEDURE updateTrendContentStats(IN mongoid VARCHAR(255),IN numericid INT,IN points INT,IN ctype INT,IN activity INT,IN created INT)
 BEGIN
   DECLARE row_count INT(1) default 0;
   SELECT count(id) into row_count FROM trendContentMaster WHERE pid=numericid;
   IF row_count = 0 THEN
     INSERT INTO tMaster(tci,pid,tct,tcd,tcf,tcm,tcf,s)
       VALUES(mongoid,numericid,ctype,points,points,points,points,1);
   ELSE
     UPDATE tMaster
       SET tcd = tcd + points,tcw = tcw + points,tcm = tcm + points,tcf = tcf + points
       WHERE pid=numericid;
   END IF;
   INSERT INTO tTransaction(tci,pid,tct,act,acp,cd)
     VALUES(mongoid,numericid,ctype,activity,points,created);
 END $$
DELIMITER

Which works fine, except it updates tMaster field twice. For example existing value of tcd, tcw, tcm, tcf is 30 and points value is 20 rather than 50 it updates value to 70.

What am I doing wrong in the increment operation?

If the table tMaster have the same pid inserted but is not in the table trendContentMaster it will update 20*(rows) depending how many times pid is inserted in tMaster table. This should work for you:

    DELIMITER $$
    CREATE PROCEDURE updateTrendContentStats(
    IN mongoid VARCHAR(255),
    IN numericid INT,
    IN points INT,
    IN ctype INT,
    IN activity INT,
    IN created INT)
     BEGIN
     DECLARE vCount INT(1) default 0;
     DECLARE vCount02 INT(1) default 0;

     SET vCount=(SELECT count(*) FROM trendContentMaster WHERE pid=numericid);
     SET vCount02=(SELECT count(*) FROM tMaster WHERE pid=numericid);

     IF (vCount = 0 && vCount02 = 0) THEN
        INSERT INTO tMaster(tci,pid,tct,tcd,tcf,tcm,tcf,s) VALUES(mongoid,numericid,ctype,points,points,points,points,1);
     ELSE
        UPDATE tMaster
        SET tcd = tcd + points,
            tcw = tcw + points,
            tcm = tcm + points,
            tcf = tcf + points WHERE pid=numericid;
     END IF;
     INSERT INTO tTransaction(tci,pid,tct,act,acp,cd) VALUES(mongoid,numericid,ctype,activity,points,created);
     END $$
    DELIMITER

PD: You should make a Foreing key in tMaster.pid.


Why is sql_text "select @@trancount" in open but sleeping transaction? Using SQL Server Management Studio, open a query window and execute

BEGIN TRAN

In another window execute

select [text],
from sys.sysprocesses
cross apply sys.dm_exec_sql_text(sql_handle)
where status = 'sleeping'
and open_tran = 1

I see select @@trancount

What's up with this? Who's counting transactions? Is this a Management Studio thing?

This is a SQL Server Management Studio thing. At least SSMS version 13.0.15500.91. This version of SSMS will count the number of open transactions on your behalf using the same connection as your query window.

Here's how I found out,

I connected to another server running 2012 and also saw SELECT @@TRANCOUNT
Using a 2012 version of SSMS, I connected to a variety of servers and saw BEGIN TRAN
I used profiler and saw that SSMS 2016 executes SELECT @@TRANCOUNT after every execution.
Incidentally, SSMS 2016 seems to execute SELECT SERVERPROPERTY('EngineEdition') AS DatabaseEngineEdition before every execution.
BEGIN TRAN turns out to be a bit of a red herring. SSMS 2016 executes these extra queries no matter what gets executed in the query window... even if your batch is just whitespace.
I coded an c# application that uses ado.net and profiled it. It does not execute these extra queries.


How to deny database acess to windows autentication users We have a Database that is attached on the client server, but we don´t want to let anyone besides our user to acess that database, is there a way to do this? I´m using the current script to create our user, 'automacao'. And I tried to modify it to not allow anyone besides 'automacao' to acess it, but they're using windows autentication to acess it, there´s a way to deny their view permission?

DECLARE @USERNAME VARCHAR(MAX) = N'automacao'
DECLARE @TABLEVISIVEL INT = 0 -- 1 SIGNIFICA QUE AS TABELAS E SP ESTARÃO VISIVEIS, 0 SIGNIFICA QUE NÃO ESTARÃO VISIVEIS

IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = @USERNAME)
BEGIN
    EXEC('DROP USER '+@USERNAME)
END

EXEC('CREATE USER '+ @USERNAME +' FOR LOGIN '+ @USERNAME +' WITH DEFAULT_SCHEMA=[guest]')

EXEC sp_addrolemember N'db_datawriter',@USERNAME

EXEC sp_addrolemember N'db_backupoperator', @USERNAME

EXEC sp_addrolemember N'db_datareader', @USERNAME

DECLARE @DENYTAKEOWNERSHIP VARCHAR(MAX) = 'DENY TAKE OWNERSHIP ON ? TO '+ @USERNAME
DECLARE @DENYALTER VARCHAR(MAX) = 'DENY ALTER ON ? TO '+ @USERNAME
DECLARE @GRANTSELECT VARCHAR(MAX) = 'GRANT SELECT ON ? TO ' + @USERNAME
DECLARE @GRANTUPDATE VARCHAR(MAX) = 'GRANT UPDATE ON ? TO ' + @USERNAME
DECLARE @GRANTDELETE VARCHAR(MAX) = 'GRANT DELETE ON ? TO ' + @USERNAME
DECLARE @GRANTINSERT VARCHAR(MAX) = 'GRANT INSERT ON ? TO ' + @USERNAME

EXEC sp_MSforeachtable @DENYTAKEOWNERSHIP
EXEC sp_MSforeachtable @DENYALTER
EXEC sp_MSforeachtable @GRANTSELECT
EXEC sp_MSforeachtable @GRANTUPDATE
EXEC sp_MSforeachtable @GRANTDELETE
EXEC sp_MSforeachtable @GRANTINSERT

DECLARE @StoredProcedureName nvarchar(250);
DECLARE @CMDEXEC1 nvarchar(2000);

DECLARE db_cursor CURSOR FOR
SELECT  SPECIFIC_NAME
FROM    information_schema.routines
WHERE   routine_type = 'PROCEDURE'
    and Left(Routine_Name, 3) NOT IN ('sp_', 'xp_', 'ms_')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @StoredProcedureName
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @CMDEXEC1 = 'DENY TAKE OWNERSHIP ON [' + @StoredProcedureName + '] TO '+ @USERNAME + ';'
    SET @CMDEXEC1 = @CMDEXEC1 + 'DENY ALTER ON [' + @StoredProcedureName + '] TO '+ @USERNAME + ';'
    SET @CMDEXEC1 = @CMDEXEC1 + 'GRANT EXECUTE ON [' + @StoredProcedureName + '] TO '+ @USERNAME + ';'
    EXEC(@CMDEXEC1)
    FETCH NEXT FROM db_cursor INTO @StoredProcedureName
END
CLOSE db_cursor
DEALLOCATE db_cursor
IF(@TABLEVISIVEL = 0)
BEGIN
    DECLARE @CMD1 VARCHAR(MAX) = 'DENY VIEW DEFINITION TO '+@USERNAME+';'
    EXEC(@CMD1)
END
ELSE IF(@TABLEVISIVEL = 1)
BEGIN
    DECLARE @CMD0 VARCHAR(MAX) = 'GRANT VIEW DEFINITION TO '+@USERNAME+';'
    EXEC(@CMD0)
END
EXEC('GRANT EXECUTE TO '+@USERNAME)
GO

If the users are administrators, there is no way to keep them out.  This could be sysadmin on the server, dbo on the database, etc.

The easiest way to keep other (non-Administrator) logins out of your database is to never grant them rights or (as one part of your code suggests) drop any users that are not 'automacao' or some other needed service logins.

Also, rather than cycling through every securable, you can use (assuming SQL Server 2005 and forward) is to grant at the database level. E.g.

 GRANT EXECUTE ON DATABASE::YourDB TO automacao


Rolling sum / count / average over date interval In a database of transactions spanning 1,000s of entities over 18 months, I would like to run a query to group every possible 30-day period by entity_id with a SUM of their transaction amounts and COUNT of their transactions in that 30-day period, and return the data in a way that I can then query against. After a lot of testing, this code accomplishes much of what I want:
SELECT id, trans_ref_no, amount, trans_date, entity_id,
    SUM(amount) OVER(PARTITION BY entity_id, date_trunc('month',trans_date) ORDER BY entity_id, trans_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS trans_total,
    COUNT(id)   OVER(PARTITION BY entity_id, date_trunc('month',trans_date) ORDER BY entity_id, trans_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS trans_count
  FROM transactiondb;

And I will use in a larger query structured something like:
SELECT * FROM (
  SELECT id, trans_ref_no, amount, trans_date, entity_id,
      SUM(amount) OVER(PARTITION BY entity_id, date_trunc('month',trans_date) ORDER BY entity_id, trans_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS trans_total,
      COUNT(id)   OVER(PARTITION BY entity_id, date_trunc('month',trans_date) ORDER BY entity_id, trans_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS trans_count
    FROM transactiondb ) q
WHERE trans_count >= 4
AND trans_total >= 50000;

The case that this query doesn't cover is when the transaction counts would span multiple months, but still be within 30 days of each other. Is this type of query possible with Postgres? If so, I welcome any input. Many of the other topics discuss "running" aggregates, not rolling.
Update
The CREATE TABLE script:
CREATE TABLE transactiondb (
    id integer NOT NULL,
    trans_ref_no character varying(255),
    amount numeric(18,2),
    trans_date date,
    entity_id integer
);

Sample data can be found here. I'm running PostgreSQL 9.1.16.
Ideal output would include SUM(amount) and COUNT() of all transactions over a rolling 30-day period. See this image, for example:

The green date highlighting indicates what's being included by my query. The yellow row highlighting indicates records what I would like to become part of the set.
Previous reading:

Postgresql Window Function Partition by Comparison
Group by
Data Intervals
Best way to count records by arbitary time
intervals...
Moving Average in Postgresql

The query you have
You could simplify your query using a WINDOW clause, but that's just shortening the syntax, not changing the query plan.
SELECT id, trans_ref_no, amount, trans_date, entity_id
     , SUM(amount) OVER w AS trans_total
     , COUNT(*)    OVER w AS trans_count
FROM   transactiondb
WINDOW w AS (PARTITION BY entity_id, date_trunc('month',trans_date)
             ORDER BY trans_date
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);

Also using the slightly faster count(*), since id is certainly defined NOT NULL?
And you don't need to ORDER BY entity_id since you already PARTITION BY entity_id

You can simplify further, though:
Don't add ORDER BY to the window definition at all, it's not relevant to your query. Then you don't need to define a custom window frame, either:
SELECT id, trans_ref_no, amount, trans_date, entity_id
     , SUM(amount) OVER w AS trans_total
     , COUNT(*)    OVER w AS trans_count
FROM   transactiondb
WINDOW w AS (PARTITION BY entity_id, date_trunc('month',trans_date);

Simpler, faster, but still just a better version of what you have, with static months.
The query you might want
... is not clearly defined, so I'll build on these assumptions:
Count transactions and amount for every 30-day period within the first and last transaction of any entity_id. Exclude leading and trailing periods without activity, but include all possible 30-day periods within those outer bounds.
SELECT entity_id, trans_date
     , COALESCE(sum(daily_amount) OVER w, 0) AS trans_total
     , COALESCE(sum(daily_count)  OVER w, 0) AS trans_count
FROM  (
   SELECT entity_id
        , generate_series (min(trans_date)::timestamp
                         , GREATEST(min(trans_date), max(trans_date) - 29)::timestamp
                         , interval '1 day')::date AS trans_date
   FROM   transactiondb
   GROUP  BY 1
   ) x
LEFT JOIN (
   SELECT entity_id, trans_date
        , sum(amount) AS daily_amount, count(*) AS daily_count
   FROM   transactiondb
   GROUP  BY 1, 2
   ) t USING (entity_id, trans_date)
WINDOW w AS (PARTITION BY entity_id ORDER BY trans_date
             ROWS BETWEEN CURRENT ROW AND 29 FOLLOWING);

This lists all 30-day periods for each entity_id with your aggregates and with trans_date being the first day (incl.) of the period. To get values for each individual row join to the base table once more ...
The basic difficulty is the same as discussed here:

Referencing current row in FILTER clause of window function

The frame definition of a window cannot depend on values of the current row.
And rather call generate_series() with timestamp input:

Generating time series between two dates in PostgreSQL

The query you actually want
After question update and discussion:
Accumulate rows of the same entity_id in a 30-day window starting at each actual transaction.
Since your data is distributed sparsely, it should be more efficient to run a self-join with a range condition, all the more since Postgres 9.1 does not have LATERAL joins, yet:
SELECT t0.id, t0.amount, t0.trans_date, t0.entity_id
     , sum(t1.amount) AS trans_total, count(*) AS trans_count
FROM   transactiondb t0
JOIN   transactiondb t1 USING (entity_id)
WHERE  t1.trans_date >= t0.trans_date
AND    t1.trans_date <  t0.trans_date + 30  -- exclude upper bound
-- AND    t0.entity_id = 114284  -- or pick a single entity ...
GROUP  BY t0.id  -- is PK!
ORDER  BY t0.trans_date, t0.id

SQL Fiddle.
A rolling window could only make sense (with respect to performance) with data for most days.
This does not aggregate duplicates on (trans_date, entity_id) per day, but all rows of the same day are always included in the 30-day window.
For a big table, a covering index like this could help quite a bit:
CREATE INDEX transactiondb_foo_idx
ON transactiondb (entity_id, trans_date, amount);

The last column amount is only useful if you get index-only scans out of it. Else drop it.
But it's not going to be used while you select the whole table anyway. It would support queries for a small subset.


Backup throws permission error I'm trying to backup SQL Server database to upload it to my database server. I am using Microsoft SQL Server Management Studio, Database -> Tasks -> Back Up...
And I got the following error:

  An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
  VIEW SERVER STATE permission was denied on object 'server', database 'master'. (Microsoft SQL Server, Error: 300)

Any idea of what's happening?

You should make sure that for the database you're trying to back up, you have at least the db_backupoperator database role assigned to the user that's trying to run the backup.
That should be sufficient.


Replication of a remote MySQL database to local MySQL database I'm the developer of my employers corporate private website, a HTML5/PHP/CSS powered site for company reports etc. The site, hosted on a shared hosting package, connects to a remote read-only MySQL DB server for authentication and report data queries. This remote DB is mainly used by a separate windows exe.

The remote DB goes down frequently which of course causes the private website to bomb out on a MySQL connection error. I've thought about replicating the remote DB to the site's local hosted MySQL DB and using the local DB as the site's primary and only DB connection.

Is there a way of synchronising the remote DB to the site's shared hosting DB in a simple and pain free way? Like every hour it updates or something?

Or is there a function where the site's hosting package MySQL DB functionality can cache the remote DB data and use that as a fallback.

Thanks in advance.

Check here (http://drcharlesbell.blogspot.cl/2011/06/comparing-databases-with-mysqldbcompare.html)

It explain how to Compare-Copy a DB to another DB, my case is the same as yours.

Depending on your access level you can:

Use a external client as middle man (to make de compare-copy)
Script in PHP to check new lines in the original DB and copy them
Use one of the many tools of MySQL to copy

MySQLDBCompare
MySQLDiff
MySQLDBCopy


Setting PostgreSQL data_directory in postgresql.conf not working I'm attempting to configure PostgreSQL 9.3 on a Centos 6.6 box to keep the configuration files (postgresql.conf, pg_hba.conf, pg_ident.conf) in a different directory than the data directory. According to the documentation, I should be able to set the PGDATA variable to the directory that contains postgresql.conf, and then specify the data_directory from within postgresql.conf to point to the directory that contains my data.

I installed PostgreSQL 9.3 via these instructions.

Steps I followed to configure Postgres (/data is a different partition from the OS):

mkdir -p /data/pgsql/data && mkdir /data/pgsql/config && sudo chown -R postgres:postgres /data/pgsql
sudo su postgres -c '/usr/pgsql-9.3/bin/initdb -D /data/pgsql/data'
sudo mv /data/pgsql/data/postgresql.conf /data/pgsql/config/ && sudo mv /data/pgsql/data/pg_hba.conf /data/pgsql/config/ && sudo mv /data/pgsql/data/pg_ident.conf /data/pgsql/config/
Edit /data/pgsql/config/postgresql.conf, making sure these variables are set as so:

hba_file = '/data/pgsql/config/pg_hba.conf'`
ident_file = '/data/pgsql/config/pg_ident.conf'
data_directory = '/data/pgsql/data'

sudo sh -c 'echo "export PGDATA=/data/pgsql/config" > /etc/sysconfig/pgsql/postgresql-9.3'
sudo service postgresql-9.3 start

This results in the error message:

/data/pgsql/config is missing. Use "service postgresql-9.3 initdb" to initialize the cluster first.

I looked at the init script (/etc/init.d/postgresql-9.3) and noticed this:

# Check for the PGDATA structure
if [ -f "$PGDATA/PG_VERSION" ] && [ -d "$PGDATA/base" ]
then
    ...
else
    # No existing PGDATA! Warn the user to initdb it.

    echo
    echo "$PGDATA is missing. Use \"service $NAME initdb\" to initialize the cluster first."
    echo_failure
    echo
    exit 1
fi

I added an echo and PGDATA is set to /data/pgsql/config, which I would expect, but  I'm not sure how setting PGDATA and data_directory could even work, because the init script isn't checking postgresql.conf to get the data_directory like the documentation says it should. Am I doing something wrong? Is the documentation wrong?

You are not doing anything wrong per se, and the documentation is not wrong. But the initscript from CentOS doesn't support you doing things this way, so you have to change the initscripts to not check for these files, since PostgreSQL will check for it itself and just try to start the database even if these files are not there.

So to test it out, comment out the if, then and the whole else block and just let it run the ... part of the script. PostgreSQL should read the settings correctly and start itself without a problem. And you might of course inform the people responsible for the CentOS package that they should do it differently. If it doesn't run, you should get a reasonable error message from PostgreSQL itself.


Rewriting query to move subquery out of FROM clause Suppose I have an entries table that looks something like this:
| id | category | text | user_id |
|----|----------|------|---------|
| 1  | A        | z    | 22      |
| 2  | B        | y    | 23      |
| 3  | B        | x    | 24      |
| 4  | C        | w    | 25      |

id is unique; none of the other columns are
I want to get the row for specified category(ies) that have the highest ID.  So in this example if I'm search for category B I'd expect it to return 3,B,x,24
There are several more columns in the table, but only those three are shown for brevity.  Other columns exist which have to be joined, e.g. to get user info.
I have the following query, which works:
SELECT *
FROM (
         SELECT MAX(id) as id
         FROM entries
         WHERE category = 'B'
         GROUP BY category
     ) as tmp
         LEFT JOIN entries e ON e.id = tmp.id
         LEFT JOIN users u ON (e.user_id = u.id)

An OR condition can be added to the WHERE clause when I need to select multiple categories, and that works.
However my ORM doesn't support subqueries in the FROM clause.  I managed to get it to work with WHERE ANY, but the query is 4.5 times slower:
SELECT *
FROM entries
WHERE id = ANY
    (
        SELECT MAX(id) as id
        FROM entries
        WHERE category = 'B'
        GROUP BY category
    );

Replacing ANY with ALL yields the same data but ALL takes at least 5 times longer than ANY.
Is there another syntax that runs as fast as the first but with some other syntax?

SELECT *
FROM entries
WHERE category = 'B'
ORDER BY id DESC LIMIT 1


SSIS AND XLSM load time issue Visual Studio 2010 I am having the following issue of SSIS AND XLSM load time issue Visual Studio 2010

I want to speed this up

I am reading a .xlsm as my excel source

I have noticed its loading / reading every row of the excel...even though they are blank

e.g I only have 300 + rows of data at the moment, but it loading every row even though they are blank e.g going pass the 400 row where there is no data yet

I don't want it reading 200,000+ rows before it actually loads the 300

how do I stop this please team

I have resolved this by using an sql command in the excel properties, and only import where ID IS NOT NULL x

thank team for ideas x


Recover a dropped MySQL database with its procedures I had some databases on a server and MySQL server on this server has been uninstalled accidentally by someone.

I had some backups on this server and could recover all tables and their data successfully, but the problem is that the events and procedures are lost.

How can I recover the lost events and procedures? I searched a lot to know if it is possible or not.

If you are lucky, you won't need the backups.  (Oops, you have already gone beyond this step.)

If MySQL's data directory was left behind by the uninstall, then simply reinstall.

Was the backup taken using mysqldump (either explicitly, or under-the hood)?  If so, perhaps it failed to have the arguments asking to save "stored routines", etc.

I fear you are out of luck.


Check users not being audited I need to confirm that actions by privileged users are being audited. As far as I can tell from views such as dba_stmt_audit_opts, the statements by the privileged users I want to be audited are being audited indiscriminately, so that seems good.

I know that NOAUDIT can be used to stop auditing a statement for a specific user. How would I check whether someone in the past has done this for a privileged user?

After reading some more on NOAUDIT, I think I understand my confusion. NOAUDIT does not necessarily cease auditing for a specific action-user pairing, but cancels a matching AUDIT statement. So NOAUDIT select table BY user1 would only cancel a previously made statement of AUDIT select table BY user1. If the AUIDT statement had never been made and all SELECT TABLE statements are already being audited, the NOAUDIT statement would have no effect.
The relevant documentation:

Use the NOAUDIT statement to stop auditing operations previously enabled by the AUDIT statement.
The NOAUDIT statement must have the same syntax as the previous AUDIT statement. Further, it reverses the effects only of that particular statement. For example, suppose one AUDIT statement A enables auditing for a specific user. A second statement B enables auditing for all users. A NOAUDIT statement C to disable auditing for all users reverses statement B. However, statement C leaves statement A in effect and continues to audit the user that statement A specified.


SQL select with unique_list - losing an item I have an oracle database and I have the following problem:
If I make an select like this:

SELECT fk_proddes_id,fk_id_cod_deseu,
     LISTAGG (TO_CHAR (cod_eliminare), ',')
                    WITHIN GROUP (ORDER BY cod_eliminare)
     AS cod_eliminare
FROM sim_sd.proddes_ag_elim
GROUP BY fk_proddes_id, fk_id_cod_deseu

I get the following result:

FK_PRODDES_ID   FK_ID_COD_DESEU COD_ELIMINARE
53              112             D 11,D 11,D 7
301             506             D 1,D 15

I need the unique code, so if I put:

SELECT fk_proddes_id,fk_id_cod_deseu,
    unique_list ( LISTAGG (TO_CHAR (cod_eliminare), ',')
                    WITHIN GROUP (ORDER BY cod_eliminare))
     AS cod_eliminare
FROM sim_sd.proddes_ag_elim
GROUP BY fk_proddes_id, fk_id_cod_deseu

the result is:

FK_PRODDES_ID   FK_ID_COD_DESEU COD_ELIMINARE
53              112             D 11,D 7
301             506             D 15

so, I "lose" the D1 - don't know why...

If I put

SELECT fk_proddes_id,fk_id_cod_deseu,
    unique_list ( LISTAGG (TO_CHAR (cod_eliminare), ',')
                    WITHIN GROUP (ORDER BY cod_eliminare DESC))
     AS cod_eliminare
FROM sim_sd.proddes_ag_elim
GROUP BY fk_proddes_id, fk_id_cod_deseu

the result is:

FK_PRODDES_ID   FK_ID_COD_DESEU COD_ELIMINARE
53              112             D 7,D 11
301             506             D 15,D 1

But I need D1,D15 and D11,D7. What's wrong with the second query?  Why do I "lose" D1 ???

Thanks!

There are two ways: first - using regexp_replace like here - Eliminate duplicates in ListAgg (Oracle), second - use distinct inside a subquery:

select fk_proddes_id,fk_id_cod_deseu,
       listagg(to_char(cod_eliminare), ',') within group (order by cod_eliminare desc)
  from (select distinct fk_proddes_id,fk_id_cod_deseu, cod_eliminare
          from sim_sd.proddes_ag_elim)
 group by fk_proddes_id, fk_id_cod_deseu


Create tables under a different schema by default instead of [dbo] I'm trying to get a user to create tables in a schema that is set as their default schema and they are set as the owner of the schema.
When I run this command, even though their default schema isn't [dbo], it still creates the table under the  [dbo] schema
EXECUTE AS USER = 'test'

CREATE TABLE TEST (T VARCHAR(10))

REVERT

The reason I'm looking to do this, is because the program that we're using, by default, uses the [dbo] schema to replicate data. It states in the documentation that tables will use the schema prefix of which user is running the stored procedure.
Is this possible? Any new tables created by this user, I want it to use their default schema instead of [dbo]

This is possible - POC below shown from SQL Server 2019
USE [Test]
GO

CREATE USER [TestSchemaUser]
WITHOUT LOGIN
GO

ALTER ROLE [db_ddladmin]
ADD MEMBER [TestSchemaUser]
GO

CREATE SCHEMA [TestSchema]
AUTHORIZATION [TestSchemaUser]
GO

ALTER USER [TestSchemaUser] WITH DEFAULT_SCHEMA=[TestSchema]
GO

EXECUTE AS USER = 'TestSchemaUser'
GO

CREATE TABLE T1([C1] INT)
GO

REVERT

SELECT S.name [Schema], T.name [Table]
FROM sys.tables T
    JOIN sys.schemas S on T.schema_id = S.schema_id
WHERE T.name = 'T1'


How do I specify that a column should be auto-incremented in pgAdmin? I have started to learn pgAdmin III to manage a PostgreSQL database. But it wasn't an easy to use application.

If I create or have created a table with pgAdmin III, how can I add "auto-increment"-functionality on a column id that has type integer?

two options: Use the "datatype" SERIAL or create a sequence and use this sequence as a default value for your integer:

CREATE SEQUENCE your_seq;
CREATE TABLE foo(
  id int default nextval('your_seq'::regclass),
  other_column TEXT
);
INSERT INTO foo(other_column) VALUES ('bar') RETURNING *;


Query returns repeating arrows when getting sum of a column grouped by some others columns I have table Inventory and I want to get the sum(quantity) grouped by the columns gid, version, warehouse

+----------+----------+-----------+----------+--------------+
|   gid    | version  |  warehouse| quantity | description  |
+----------+----------+-----------+----------+--------------+
|   gid1   +    1     +    1      +    100   +    name 1    +
|   gid1   +    1     +    1      +    200   +    name 1    +
|   gid2   +    1     +    2      +    200   +    name 2    +
+----------+----------+-----------+----------+--------------+

The following query works, as it returns 2 records with the sum quantity:

SELECT gid, version, warehouse, SUM(quantity) as total
FROM Inventory
GROUP BY gid, version, warehouse;

+----------+----------+-----------+----------+
|   gid    | version  |  warehouse| quantity |
+----------+----------+-----------+----------+
|   gid1   +    1     +    1      +    300   +
|   gid2   +    1     +    2      +    200   +
+----------+----------+-----------+----------+

I tried to add also the column description in the records:

SELECT gid, version, warehouse, SUM(quantity) as total, description
FROM Inventory
GROUP BY gid, version, warehouse;

but I got an error message:

  Column 'description' is invalid in the select list because it is not
  contained in either an aggregate function or the GROUP BY clause

I searched it and I ended up with this query:

SELECT T1.gid
      ,T1.description
      ,T1.version
      ,T1.warehouse
      ,T2.quantity
FROM Inventory T1
INNER JOIN (
    SELECT gid, version, warehouse, SUM(quantity) AS quantity
    FROM Inventory
    GROUP BY gid, version, warehouse
) T2
ON T1.gid = T2.gid

It shows the correct sum, but it returns 3 records :

+----------+----------+-----------+----------+--------------+
|   gid    | version  |  warehouse| quantity | description  |
+----------+----------+-----------+----------+--------------+
|   gid1   +    1     +    1      +    300   +    name 1    +
|   gid1   +    1     +    1      +    300   +    name 2    +
|   gid2   +    1     +    2      +    200   +    name 2    +
+----------+----------+-----------+----------+--------------+

How can I get only the 2 arrows? The following is the desired output:

+----------+----------+-----------+----------+--------------+
|   gid    | version  |  warehouse| quantity | description  |
+----------+----------+-----------+----------+--------------+
|   gid1   +    1     +    1      +    300   +    name 1    +
|   gid2   +    1     +    2      +    200   +    name 2    +
+----------+----------+-----------+----------+--------------+

My database is in SQL Server Express 2008.

Three rows are returned because Inventory has three rows. Inner joining the aggregated table won't filter out the three rows. To do that, you can use DISTINCT or simply group by description as well. See this fiddle.

Distinct

SELECT DISTINCT T1.gid
      ,T1.description
      ,T1.version
      ,T1.warehouse
      ,T2.quantity
FROM Inventory T1
INNER JOIN (
    SELECT gid, version, warehouse, SUM(quantity) AS quantity
    FROM Inventory
    GROUP BY gid, version, warehouse
) T2
ON T1.gid = T2.gid

Group By Description

SELECT gid, version, warehouse, description, SUM(quantity) as total
FROM Inventory
GROUP BY gid, version, warehouse, description;


DB Design - 1 column for each dataset OR dataset_name AND dataset_value? I am creating a database table for storing numeric time-series data sets regarding cities that will be accessed via a web app.  Primarily annual, quarterly and monthly data will be stored in it.  There will be hundreds, potentially a couple thousand data sets, for each city.  Some data sets may have data on a monthly, quarterly AND annual basis while other data sets may only be available on a monthly, quarterly OR annual basis.

I am trying to decide between 2 different designs for the table's columns:

Option #1

city_id year subyear dataset_name dataset_value dataset_unit_type dataset_source

Option #2

city id year subyear dataset1-PROPERTY_TAXES dataset2-POPULATION dataset3-ETC ... datasetX

With Option #1, a hypothetical row look like:

12345 _ 2012 _ 'Q4' _ 'Property Taxes Collected' _ 950000 _ 'USD' _ 'city govt'

OR with monthly data:

12345 _ 2012 _ 'M8' _ 'Property Taxes Collected' _ 330000 _ 'USD' _ 'city govt'

With Option #2, a hypothetical row look like:

12345 _ 2012 _ 'Q4' _ 950000 _ 1400500 _ 234.53 ... XXXX

OR with monthly data:

12345 _ 2012 _ 'M8' _ 330000 _ 1400500 _ 120.12 ... XXXX

If I do #1, I will have hundreds (or maybe a couple thousand?) of columns and many rows will have a lot of 'blank' spaces because a lot of data sets will not have data for each row (e.g. some data that is only available quarterly will always be 'blank' in columns for rows describing monthly data).  Also, I think there will need to be a corresponding table for describing the sources and units (e.g. U.S. Dollars, Square Miles, etc.) for each dataset column.  I am concerned that the large number of columns and blank spaces may be problematic.

Alternatively, if I do #2, I will have very few columns but millions (potentially billions?) of rows and will have many queries with a WHERE clause searching to see if the dataset_name column equals a certain string (e.g. SELECT * WHERE dataset_name='Property_Taxes_Collected').  Also, since all types of numeric values are stored in the dataset_value column, the data type here will have to be pretty flexible (Maybe something like DECIMAL 16,4?).   I'm primarily concerned this may become slow with a lot of data.

I am using MySQL.

My Question:
Is possible to say which of these 2 general table designs is most likely the better design choice if my primary concern is query speed?  Or is it fairly clear that I should be approaching this in a completely different manner?

Upon realization that I had unwittingly reinvented a modified EAV model with Option #1 and would likely be headed into a world of hurt with this option, I decided to go with Option #2.

Option #2 should preserve many of the benefits of RDBMS and I think MySQL/InnoDB can support the row sizes I currently expect.  While I will have a quantity of NULL entries that some might find disagreeable, I expect the DB will perform queries quickly, which is my main concern.

At some future point, should I hit constraints/performance issues, I may consider a NoSQL option as it appears the data I am working with doesn't necessarily correspond so well to RDBMS.


SSIS 2012 VS_ISBROKEN on Derived Column I have converted a SSIS package built in VS2008 to open in VS2010.

I have a Derived Column component that fails validation. It works fine in VS2008.
Here is the Expression used in Derived Column...

FINDSTRING(
    SecurityDescription,"\n",1) > 0
    ? SUBSTRING(SecurityDescription, 1,
                FINDSTRING(SecurityDescription,"\n",1) - 1)
) : SecurityDescription

Below are the errors when I run the package...

Error is:

  [Derived Column [36]] Error:
  Attempt to parse the expression
  "[FINDSTRING](#102,"\n",1) > 0 ? [SUBSTRING](#11898,1,[FINDSTRING](#11898,"\n",1) - 1) : #102" failed and returned error code 0xC00470A3.
  The expression cannot be parsed. It might contain invalid elements or it might not be well-formed. There may also be an out-of-memory error.

I have double checked and SecurityDescription is a valid column. It works fine if I just add SecurityDescription. Not sure what is wrong with the FINDSTRING code.

First of all, you have an additional parenthesis before : character, expression is:

FINDSTRING(
SecurityDescription,"\n",1) > 0
? SUBSTRING(SecurityDescription, 1,
            FINDSTRING(SecurityDescription,"\n",1) - 1)
 : SecurityDescription

Lineage ID Error

"Lineage ID is a property of the component or transformation used in the data flow task. It contains an integer value that will work as buffer pointer. Each column in the data flow task will be assigned a lineage ID." Read about lineage ID in this Microsoft TechNet article

LINEAGE ID Error implies that a Source metadata was changed, just re-validate source (connection and component) by double click on the the derived column transformation and close it , then check the columns metadata (using the advanced editor). (Note that when double-clicking on a component that contains errors it will prompt to fix it)

Or you can try removing Derived Column transformation and adding it again (if previous solution doesn't works)

Also it is good to revalidate Source columns metadata

Analyzing the exception

If you take a look at the exception thrown you will see that SecurityDescription has different lineage id in the same expression which is not right.

  "[FINDSTRING](#102,"\n",1) > 0 ? [SUBSTRING](#11898,1,[FINDSTRING](#11898,"\n",1) - 1) : #102"

So it is clear that metadata needs to be updated.

Also from the error screenshot it looks like the expression is using SecurityDescriptionNoLineFeeds not
SecurityDescription.

These errors maybe caused during the package upgrade process

Side Note: check that SecurityDescription is not defined multiple time in differents components. (Ex: Source.SecurityDescription and Script.SecurityDescription)

Try dropping derived column transformation and re-creating it or add a new derived column and drop the old one


Is it possible to access column by index instead of it's name/alias in query? I want do something like this:

SELECT *
FROM
(
  SELECT 1, 2, 3
) AS subquery
WHERE subquery[0] = 1 -- I want to reference cloumn by its index in row

Nope. In query you need to address columns explicit.
But you can do this inside your application.


Firebird query pivot I have a Firebird sql query that returns 3 fields: Surname, Name and date as (YYYY-MM-DD HH:mm:ss)

select Surname, Name, "WHEN" from users
left join attendant on (users.id = attendant.userid);

This is the result:

Surname Name    2018-04-11 08:35:37
Surname Name    2018-04-11 12:52:30
Surname Name    2018-04-11 13:44:18
Surname Name    2018-04-11 17:08:05
Surname Name    2018-04-12 08:31:17
Surname Name    2018-04-12 12:36:07
Surname Name    2018-04-12 13:28:30
Surname Name    2018-04-12 17:44:55

What I want to achieve is to put the date field of the same day (YYYY-MM-DD) in the same row.

Surname Name    2018-04-11 08:35:37 2018-04-11 12:52:30 2018-04-11 13:44:18 2018-04-11 17:08:05
Surname Name    2018-04-12 08:31:17 2018-04-12 12:36:07 2018-04-12 13:28:30 2018-04-12 17:44:55

Is this possible with Firebird?

A pivot transposes rows to columns based on a discriminator column. Your example data doesn't show a discriminator column, and the data also seems to indicate that this could lead to a variable number of columns (depending on the attendance per date), which is not possible in Firebird. Instead it looks like you want to aggregate those dates in a single column.

Assuming that is what you want, you can use LIST combined by a group by on the date part of the timestamp, for example:

select surname, name, list("WHEN", ' ')
from users
left join attendant on users.id = attendant.userid
group by surname, name, cast("WHEN" as date)

Be aware: LIST does not guarantee an order. You may need to use an intermediate order by on "WHEN" in a sub-query as a work around, but that trick does not always work (especially as in this case we're grouping on a derivative of "WHEN").


How to enable force ssl on AWS Aurora Postgres? In AWS's direct postgres offering RDS Postgres, you can require SSL by setting the parameter group item rds.force_ssl to 1.

This is as per the RDS postgres documentation:  https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.SSL

However Aurora Postgres does not have this parameter item, and there is no ability to add new parameters either.  How do you turn on require ssl for an Aurora instance?

I can see in the Aurora version that it supports SSL:

create extension sslinfo;
select ssl_is_used();
select ssl_cipher();

And I can optionally connect via ssl:

psql -h my-ssl-test1.cwzhlddlylx.us-east-1.rds.amazonaws.com -p 5432
-U myuser -d mydb sslrootcert=rds-ca-2015-root.pem sslmode=verify-full

For Aurora Postgres, there's two relevant cluster-level parameters (note they're not instance-level parameters): rds.force_ssl and ssl. I haven't tested this myself but you should be able to modify them in the usual way using DB Parameter Groups.


Composite foreign key not verify all columns Table1 (Num [PK NOT NULL], Dat [PK NOT NULL], ...)

Table2 (ID [PK], Num, Dat, ...)

I added this FK:

ALTER TABLE [dbo].[Table2]  WITH CHECK ADD FOREIGN KEY([Num], [Dat])
REFERENCES [dbo].[Table1] ([Num], [Dat])

But when I try to insert into Table2 the values [ Num = 1, Dat = NULL ], SQL don't give me an error and the values are inserted.

How I force SQL Server to check all columns of the foreign key?

A constraint of FOREIGN KEY will never forbid you from entering NULL values in nullable columns. The constraint is simply ignored for such rows.

You can make the columns not nullable, if you want to enforce this. Based on the comments though, you probably just need to add a CHECK constraint on the table, so the two columns are either null or not null, together:

ALTER TABLE dbo.Table2
  ADD CONSTRAINT Num_and_Dat_are_Null_Together
  CHECK ( Num IS NULL AND Dat IS NULL
       OR Num IS NOT NULL AND Dat IS NOT NULL
        ) ;


What are "nonremovable row versions"? I did full vacuum on postgresql-9.4 database and I have a lot of tables with nonremovable row versions. I would like to know what are these or where I can read about them.

INFO:  vacuuming "public.profile_generals"
INFO:  "profile_generals": found 0 removable, 654 nonremovable row versions in 51 pages
DETAIL:  0 dead row versions cannot be removed yet.

They are your data. You probably don't want to remove them ;)

"nonremovable row versions" includes the current, active row versions containing the data you care about. It also includes row versions made obsolete by a delete or update, but still visible to the snapshot of one or more running transactions.

You should only be concerned if there are lots of nonremovable dead row versions. That is not the case here. To learn more, read the manual on routine vacuuming, the chapter on concurrency and MVCC, and read about table bloat.


'mysql' Recognized by cmd but not PowerShell? I'm trying to run a very simple login script through powershell.

I have already added the default directory "C:\Program Files\MySQL\MySQL Server 5.7\bin" to the PATH variable. Now I was able to call mysql from command line prompt.

However when I tried to run the same script from PowerShell it showed that " The name 'mysql' is not recognized as the name of cmdlet, function, script file......"

My computer is running on Windows 10 64bit.

So as I am reading about powershell now a days and I find this interesting and wanted to know answer myself, on some googling here is what I found:

Considering that you are using  similar command as given below to fire the Mysql

  PS C:\Program Files\mysql\MySQL Server 5.7\bin> mysql

Here is the full version of error that you should be getting :

  mysql : The term 'mysql' is not recognized as the name of a cmdlet,
  function, script file, or operable program.  Check the spelling of the
  name, or if a path was included, verify that the     path is correct
  and try again. At line:1 char:1+ mysql
  + ~~~~~
          + CategoryInfo          : ObjectNotFound: (mysql:String) [],     CommandNotFoundException
      + FullyQualifiedErrorId : CommandNotFoundException

  Suggestion [3,General]: The command mysql was not found, but     does
  exist in the current location. Windows PowerShell does  not load
  commands from the current location by default. If you      trust this
  command, instead type: ".\mysql". See "get-he lp
  about_Command_Precedence" for more details.

If we see the Suggestion part it says Windows PowerShell does not load commands from the current location by default. If you trust this command, instead type: ".\mysql".

So try using .\mysql instead of just mysql after setting the current directory to C:\Program Files\mysql\MySQL Server 5.7\bin> but this might lead you to another error which most probably will say :

ERROR 1045 (28000): Access denied for user 'ODBC'@'localhost' (usingpassword: NO)

which you can than resolve changing the syntax to mysql -u root -p

So it looks like kinda security thing that powersehll is imposing.

Source:1

Note: Try running $env:Path in powershell to see if the path is correctly set.


Fact table Primary Key: Surrogate Key or multi-column Natural Key? What's the best practice when choosing a fact table's Primary Key (PK for brevity): Should we have an auto-incremented Surrogate Key (SK), or should we use Natural Key's (NKs) columns?

The advantage I see in using a SK is INSERT performance, which is very relevant when we have almost a million rows inserted daily, which is my case. I'm expecting almost 20M rows per day, 200M rows per month, and the table must sustain data for decades to come without needing to be split.

But query performance should be better if we have a NK as PK, and the order of the PK is well designed.

Of course we can create non-clustered indexes for queries, but that eats storage and makes INSERT even slower.

Considering that INSERT and DELETE (yes, we have that too, a lot, because data is reprocessed and new files versions must be "updated") performance is very relevant, almost as much as query performance, what's best?

Other issue to consider is that when rows are deleted a SK count doesn't decrease. With 200M rows per month, I'll have 2,4G rows per year, which int datatype won't hold, so I'll need to use bigint for PK...

What's the context?  It sounds like you're talking about a data warehouse.  If so, I strongly recommend creating a synthetic key.  I can think of three good reasons, offhand:

It will be narrower than the business key.  While adding a column to a fact table should not be done lightly, because it will make your other indices narrower it may actually reduce the total size of your database.
Natural keys get dirty.  Sooner or later someone will reuse a product code for something unrelated, or you'll need to start tracking sub-customers.  If you have a mapping process between your natural keys and your synthetic keys, it's simple to account for these kinds of things.
You can use it to store synthetic values: -1 for "Not applicable", 0 for "Unknown", 1 for "Missing", 2 for "Illegible", etc.  Depending on your natural keys, you might be able to do the same there, but there would always be a risk of collision.

48+ billion records is a lot.  If you haven't already, you should consider aggregating data, perhaps after the current month.  Synthetic keys could make this simpler too: perhaps values 1,000,000,001..1,999,999,999 are reserved for 2017/08 details, and in October the August data gets collapsed into a single record with ID 1,000,000,000 (this probably wouldn't be a good idea, it's just an example of how you may need to disconnect business keys from warehouse keys).

One last note: don't forget that your choices for PK and for clustering key by no means need to be the same!  Each should be evaluated on its own merits.


SQL Server database recovery By mistake one of my colleague in the office has restored .mdf and .ldf files of another database to production with existing database name. So existing database was overwritten with new backup.

Is there any chance to recover that db back?

xyz database - .mdf & .ldf - restored as abc database (but already existing in production)

SQL Server recovery tools cannot help here, they can only recover lost information from database files (mdf, ldf, ndf, bak, trn). You should search for a way to recover overwritten files in Windows

One of the solutions is to use the File History option in Windows. Your Windows version must support it and it had to be enabled at the time when the files were overwritten and

"File History saves copies of your files so you can get them back if they are lost or damaged"

Restore files or folders using File History


Get percentage free space on database volumes I am currently using SQL Server 2005 and (undocumented I believe) master..xp_fixeddrives to get free space on my database volumes as part of my monitoring.

However, this only gives me an absolute number of MB free. What I really need is percentage free.

Is there another way in SQL Server 2005 to get this?  If not, is there some other light-weight way to get it?  If I can, I want to avoid installing a Java JRE, or Perl, or Python on my database server. Perhaps vbscript, or a small Windows executable on the file system?

Yes, I know I can Google this, and I have.  It looks like there are a few ways to accomplish it, and I'm curious how my DBA brethren have handled this.

Personally, I don't know why you need to do this from within SQL Server. You can get this as often as you want and fairly straightforward using PowerShell, or C#, or even VBScript in a scheduled task of some kind, and then have that program update a table if you really need this stored in the database (and frequency would be dictated by how volatile your disks are and how current you need the data to be). I recommend against shelling out to the OS to collect this information while an end user is waiting.

That said, in SQL Server 2012 (and 2008 R2 SP1+) there is a new DMV called sys.dm_os_volume_stats. This DMV has total size and available size so you can perform any resulting calculations from there. I haven't tested how current this data is - it may be seconds behind or more.


Statistics on temp tables Quick and simple... why is this sp failing when I attempt to retrieve statistics for one of the columns on the temp table?

CREATE PROCEDURE dbo.Demo
AS
BEGIN
SET NOCOUNT ON
-- Declare table variable
CREATE TABLE #temp_table (ID INT)
DECLARE @I INT = 0

-- Insert 10K rows
    WHILE @I < 100
    BEGIN
        INSERT INTO #temp_table VALUES (@I)

        SET @I=@I+1
    END

-- Display all rows and output execution plan (now the EstimateRow is just fine!)
SELECT * FROM #temp_table

-- Is the object there
SELECT OBJECT_ID('tempdb..#temp_table')

-- How about statistics
DBCC SHOW_STATISTICS ('tempdb..#temp_table', 'id')
END;

I don't understand, I get a message saying that there is no statistics created on column id

Could not locate statistics 'id' in the system catalogs.

Having said that, I have seen an article by Paul White where this technique is used and does indeed work.

https://sqlkiwi.blogspot.com/2012/08/temporary-tables-in-stored-procedures.html

Any ideas?

You need to explicitly create a statistics object called id

CREATE PROCEDURE dbo.Demo
AS
BEGIN
SET NOCOUNT ON
-- Declare table variable
CREATE TABLE #temp_table (ID INT)
DECLARE @I INT = 0

CREATE STATISTICS id ON #temp_table (ID)

-- Insert 10K rows
    WHILE @I < 100
    BEGIN
        INSERT INTO #temp_table VALUES (@I)

        SET @I=@I+1
    END

-- Display all rows and output execution plan (now the EstimateRow is just fine!)
SELECT * FROM #temp_table

-- Is the object there
SELECT OBJECT_ID('tempdb..#temp_table')

-- How about statistics
DBCC SHOW_STATISTICS ('tempdb..#temp_table', 'id')
END;

EXEC dbo.Demo

When left to its own devices, the statistics objects created by the system have odd names like _WA_Sys_00000002_5F141958

Just note that when you create the statistics matters -- you'll need to move the CREATE STATISTICS command to after you populate the temp table with data if you want it to show anything, or possibly change your query to need to update stats, like SELECT * FROM #temp_table WHERE ID > 0.


Is it possible to delegate responsibility for specific indexes for a collection to specific shards in a mongoDB database? Is it possible to delegate responsibility for specific indexes for a collection to specific shards in a mongoDB database?

For instance, say I have a collection called 'books':

{ title: <string>, pages_in_book: <number>, price: <number>, author: <string> }

And say I want 3 indexes for this collection: one for the price, one for pages_in_book, and one for author.

Also say I have 3 shards.

Can I have each of these shards be responsible for updating one index each when a new document is inserted into the 'books' collection?

I want to do this because I plan to make a write-heavy application. User actions would initiate the creation of multiple documents at once and each document would need to be added to multiple indices like above (but five or six indices in my real case). I could distribute the writes using a shard key (with luck here, the documents' writes and it's writes to the collection's indices would be distributed evenly to the different shards but I could get unlucky if no good shard keys) or distribute the writes so that each shard is assigned an equal amount of indexes to be responsible for (extremely more likely for there to be a near even distribution of writes this way???)

As Antonios wrote, you cannot distribute different indexes to different shards. To distribute load, you should select good shard key and this case key what is used most (best situation is if all queries would always use "some" key) it should be "hashed". ( {"some":"hashed"} )

Check hased sharding from here!


why are my Database backups occasionally failing? Part of our back up routine, on our main production server, I nightly take a full backup of all of the databases.  These databases get restored on several dev severs and data  is obfuscated.  These are for Developers to work on, test on, and our staging/QA environment.    These are mostly sql server 2016 (a couple of the dev environments are 2019 as we prepare to make that switch.)  The code which does these backups and restores has not changed in months (close to a year).  These instances are spread over three different servers.

About two weeks ago I started getting errors that  the lower environments were getting errors trying to restore some of  the backup files. I have scoured the logs and found nothing. The restore is done in an agent job in a try- catch block. The try is “restore” , the catch is “send email to dba”.  That email is the only thing I could find.  However when I looked at those dbs, they indeed did not restore.
this is the error message I get when trying to restore them manually :
 Msg 3013, Level 16, State 1, Line 21
RESTORE DATABASE is terminating abnormally.

When a backup for a database failed, it failed for all of the instances to where it was going to get restored. Obviously a bad backup.  I scoured the log files on the production server and found nothing.

I could take a new backup of the offending databases and restore them in the lower environments with no issue.

There are only about 10 databases (A B C D E F G H I J)  that are affected.  Each day between 1 and 4 of these databases fails to back up. It is random how many and which databases.
Day one could be A G J,  day 2 E  day 3 B C G H  day 4 B E C I  etc.    There are about 50 databases on the server.  The ten that take turns failing are ten of the  smallest of the 50 databases.  two of the ten are relatively new (maybe a year old) while the others are ancient.

Now as part of the backup process I have changed it so that after each database backs up I run a restore with verify only with checksum on the database.  If the verify fails, it sends me an email and redoes the backup.  This has taken care of the problem of the restores and everything is working smoothly.  However I am still getting the emails saying that the verify of the backup has failed.  I only get the email once.  I would get the email for each time the verify fails. So the second attempt is always working.

Whereas checking the backups has solved the restoring problem,  I have not solved the problem  The problem reamains that bad backups are being taken.  I am worried that something is wrong with our prod server in General or those databases in particular.  I am afraid that my work around will only hide the problem and that we have a train wreck coming down the line.

I run DBCC CHECKDB on the entire server daily, and I have run it on those specific databases and results say that all is normal.  There is plenty of space on all of the lower level instances so space contention  is not an issue.

I have not solved the problem The problem reamains that bad backups
  are being taken. I am worried that something is wrong with our prod
  server in General or those databases in particular. I am afraid that
  my work around will only hide the problem and that we have a train
  wreck coming down the line.

Yes.  You should be afraid, and proceed on the assumption that your storage solution is not reliable.  So you should immediately ensure that you are landing good backups on a reliable storage device, and proceed with troubleshooting/support of your production database's main storage and/or backup storage solution.


Primary Key Constraint Violation in SSIS with Quotation Mark Using SSIS Data Flow Task to transfer records from table in 1 SQL Server database to an IDENTICAL table in another SQL Server (2012) and getting "Violation of PRIMARY KEY constraint 'FLG_APVN_KEY_0'. Cannot insert duplicate key in object 'dbo.FLG_APVN'. The duplicate key value is ("ARIZ0010   ).".
In the Source table (FLG_APVN), there are two records with the following Primary Key values:   "ARIZ0010    and    ARIZ0010
Obviously similar but definitely distinct. The quotation mark at the beginning of the first PK Value seems to be getting dropped off by SSIS in the insert causing a primary key violation. I need both records in the destination table with their primary keys intact. How do I do this? (SQL Server 2012, SSIS from Visual Studio 2012).

Solved. See Comment above. Turns out that the quotation mark wasn't the problem.


Searching SSIS packages for the use of specific tables I have a number of SSIS packages, how can I search them to find the use of specific tables? I need to make tables changes and I am trying to assess the impact of these.

SSIS code behind is actually a XML value and all components of package will be represented as XML node values within it.This XML can be parsed to identify if it has a reference for our searched table or column.

Finding SSIS Packages having References to a Table or Column will help you.


Oracle connection through visual studio I installed oracle client 11.2.0 version in my machine and also installed Oracle data access providers for visual studio.I also installed Oracle Data Access 11.2.0.3.20. I am trying to connect to the database through the visual studio i am getting the below exception

ORA -12504 : TNS: listner was not given the SERVICE_NAME in CONNECT_DATA**

The tnsnames.ora file contains the folloing code

creds=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = aaa.cedd.net)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = creds)
    )
  )

Just alter SID to SERVICE_NAME:

creds=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = aaa.cedd.net)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = creds)
    )
  )


ALTER TABLE engine from MyISAM to InnoDB: why does mysqldump --order-by-primary help me? I found an interesting mysqldump option in MySQL 5.7 Reference Manual. In internet there are no further details and explanations.

--order-by-primary

  Dump each table's rows sorted by its primary key, or by its first
  unique index, if such an index exists. This is useful when dumping a
  MyISAM table to be loaded into an InnoDB table, but makes the dump
  operation take considerably longer.

Can you explain me why this helps in migrating MyISAM to InnoDB? How this works?

MyISAM table is stored as a heap, rows are being placed inside the data file into an empty place they can fit. If you are only inserting then that means appending new rows, but when the rows are being updated or deleted, some gaps are created and then later filled by other rows so the order of full table scan (which is what dump does by default) is not defined in any way.

InnoDB table is organized differently. The rows are stored as items inside a B-Tree "indexed" by the PRIMARY KEY. So entire table is clustered by the PRIMARY index defined. (As a side note InnoDB table always has a PK, if you do not define one, it picks one for you or creates artifical one.) That means that the full table scan is actually executed by scanning all data pages of the PRIMARY index and so the rows are returned ordered by that.

If you dump MyISAM table ordered by PRIMARY, the dump may be a bit slower than the unordered one, but a dump is usually the faster part of migrating so thats mostly a small penalty.

When you insert data into InnoDB table, the B-Tree index dynamically balances itself to keep the logarithmic depth. That often means some page splits are done.

If you insert the data in random order, each row may go to a different part of the tree, hitting different pages on-disk and initiating random splits. That incurs some IO performance penalty and it can lead to suboptimal fill factor (percentage of empty space in each page), as full page is split to two pages only 50% full which means that in bad case your data storage may be 2 times greater than needed (and all queries have to read twice as much data). Another possible outcome is fragmentation of the pages - when logically consecutive pages of the the lowest level of the B-Tree are stored in random separate locations of the disk drive (but this state is not easily identifiable and seems to be a theoretical issue, in practice encountered only rarely if ever).

But if you insert the data ordered by the PRIMARY KEY, the new rows are being "appended" to the table. Only the "last" pages on each level need to be taken care of (and if I understand it correctly - the suboptimal fill factor can be avoided as InnoDB can identify that "appending" is happening and add new pages more effectively). And the pages may be allocated more sequentially too.

So insertion in the PK order leads to faster execution and probably better storage usage in InnoDB tables.