Create triggers on slave only in mysql replication with a slave with different schema I have a mysql replication setup with one master reporting to one slave. The schema on the slave is the same than the master but for each table I have an additional synchronized column only on the slave which I use for sync with a third system. I need the following behavior:

By default, the synchronized column is set to 0
I have an external python script that copies each row with synchronized=0 to another system (with another schema) and then sets the synchronized field to 1
If the row is updated again by the master through replication, I want the synchronized field to be set to 2 (probably through a trigger on update) so that my python script can detect updated rows and update them in the third system.

To simplify, let's say we have a master table like this :

People
id (int)
name (varchar)

and the corresponding table on slave

People
id (int)
name (varchar)
synchronized (tinyint)

If I insert
I already tried to set up a trigger on update on the slave to change the synchronized field to 2. It works when I update a row manually but not when it is updated through replication.

I understood that in row-based replication triggers are not replicated from the master but I am not trying to replicate a trigger from the master but rather create a trigger on slave whenever a row is updated through replication.

Right now my replication is row-based but i could change it to statement based or mixed if needed.
Hope it makes sense and that everything is clear, any suggestions are welcome,

Thanks

@Mati when your Master/Slave is row based replication the replication thread  replicate a copy of the row into the slave instead of ruining the "insert into xx" statement into the slave.

therefore it'll never run your trigger.

however in your case i suggest you the following solution:
1- create synchronized field on the master with default value 0, and never call populate this field during an insert statement from the master
2- create a trigger on the master for each update to set synchronized to 2 when an update occur
3- remove the trigger from the slave

because the "synchronized" field already exists your replication will fail when you create the field on the master at step #1 above. you can tell your slave to skip temporary the error code related to field already exists below:

slave-skip-errors = xxx (xxx is the error code )


Pros and cons of row level security Microsoft SQL Server What are the pros and cons of row level security?

We are porting a database from a desktop app to a web app and want to know the best way to make sure sensitive data is secured.

For our application there will be a limited number of users logging onto the system, e.g. trusted contractors rather then members of the public.

We could of course write our queries to check permissions on a case by case basis, or set up rows so that only contractors from that organization can see that row.

What criteria are used to decide what is best practice?

Pros

Row level security (RLS) reduces your application development complexity. Without RLS, you'd generally create filters with your queries or through ORM, or pass on filtering criteria to your procs. Once you have RLS in place, user1 sees what you desire user1 to see without changing your application much.

With RLS, security is at the DB level. If another application was reading data in a different language from a different platform, RLS still allows user1 to see what you want user1 to see. If someone is trying to run/test SQL queries to SSMS, RLS shows data according to your setup.

Your DB backup and restore will keep RLS intact.

Cons

The disadvantage is more DB-centric work and small amount of performance penalty. If you have web developers who aren't strong on the DB side, troubleshooting RLS might take them a bit of time. On the contrary, if application layer was filtering information through an ORM, you could debug and write auditing on the application or middle-ware side.

As far as the overheads of row level security go, here's an article that details some challenges: https://www.mssqltips.com/sqlservertip/4005/sql-server-2016-row-level-security-limitations-performance-and-troubleshooting/ and https://www.mssqltips.com/sqlservertip/4778/performance-impact-of-sql-server-2016-rowlevel-security/.

To use RLS or not?

If you are comfortable with RLS, I'd recommend you try it out. Microsoft has listed some RLS-related best practices here: https://docs.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-2017.

If your DB is well protected and access to data is through application layer only, you can opt to build filtering within your application layer. It's not a lot of work to do security checks at the application layer AND use RLS. There isn't a one size fits all answer. For most simpler applications, I tend to use the application layer for filtering since it has served me well with auditing/logging/debugging.


Advanced search engine or server for relational database In my current project we are storing big volume of data in relational database. One of the recent key requirements is to enrich application by adding some advanced search capabilities.

In the Project performance is one of important factors due to very large tables (10+ milions of records) with parent-children relations (for example: multi-level parent-child relationship, where I am looking for all parents with specific children). The search engine should also be able to check these references for hits.

I have found some potential engines on stack overflow, however it looks like that all of them are dedicated rather for text search than relational db and hosted on linux os:

Lucine
Solr
Sphinx

As I understand some of them use documents as a source of searching, but is it possible/ efficient to create programmaticaly documents based on my relational data? As I am not familiar with all of their features/capabilities can anyone please make some recommendations or propose some different solution?

To summarize my requirements:

framework/engine to search relational database including decendants.
support for Microsoft SQL Server
can be used in .NET applications
preferably hosted on Windows systems

Does any of mentioned above are able to solve my problem? do you know any better solution?

Stack Overflow search is now 81% less crappy, thanks to Lucene. Whether this, or any of the other document oriented search servers, would be a good fit is difficult to tell from the detail you've posted.

Lucene/Solr and their ilk are built to search textual data. It sounds like you should be considering a graph database. High Scalability has an interesting article on Neo4j which discusses some of the concepts well.


When I have 6 cores and they do a table scan and merge afterwards, does every core scan the whole table? I remember that I once had a missing index on a table and one query was taking ages. In the execution plan I saw that a table scan and a merge was done. Statistics from IO showed me that every core did a table scan, or better that I got 6 table scans.
Now I wonder if every core does a full scan, or does every core roughly do 1/6 table scan?
I know for sure that if I had a proper index, I either just get a seek or it would be split for every core.

I hope you understand what I ment here, unfortunately I can't provide any information, since the question just came up and my problem was solved years ago.

To be upfront with you, I'm not exactly sure what you mean by "a merge was done". Are you talking about a merge join? Perhaps you mean a parallelism operator? At the very least I can answer the question about parallel table scans.

Statistics from IO showed me that every core did a table scan, or better that I got 6 table scans.

I assume what you mean by this is that you ran SET STATISTICS IO ON before running your query and part of the output included something like this:

Table 'your_table'. Scan count 6, ...

The label "scan count" is a bit misleading. You should not conclude that if STATISTICS IO reported 6 scans that all of the rows in the table were scanned 6 times. Considering the following simple example query on a heap table called heap_table:
SELECT TOP 1 *
FROM heap_table
OPTION (MAXDOP 1);

For that query STATISTICS IO should report a scan count of 1, right? But SQL Server clearly didn't need to read all of the rows in the table. Looking at the definition for the scan count label is also helpful:

Number of seeks/scans started after reaching the leaf level in any direction to retrieve all the values to construct the final dataset for the output.
...
Scan count is N when N is the number of different seek/scan started towards the left or right side at the leaf level after locating a key value using the index key.

So if your query did a parallel scan I would expect to see a scan count of at least 6, but that doesn't necessarily imply that all of the rows from the table were read six times. How can you tell how the rows were distributed amongst your CPU cores?
The easiest way is to just look at an actual execution plan. If you look at the details for a parallel scan SQL Server will show you how many rows were processed by each CPU thread. Below is a picture of what you might see borrowed from Paul White's article Parallel Execution Plans – Branches and Threads:

As you said you encountered this query years ago so that method won't help you. Instead we need to look at the techniques available to SQL Server for parallel plan processing. Craig Freedman has a series of blog posts on the subject. From the Parallel Scan article:

How does parallel scan work?
The threads that compose a parallel scan work together to scan all of the rows in a table.  There is no a-priori assignment or rows or pages to a particular thread.  Instead, the storage engine dynamically hands out pages to threads.  A parallel page supplier coordinates access to the pages of the table.  The parallel page supplier ensures that each page is assigned to exactly one thread and, thus, is processed exactly once.

Well, there you have it. Like I said earlier you can easily test this by running a query with a parallel scan and checking out the details for the parallel scan operator in the actual execution plan.
For another way to look at it, try thinking of a scenario in which it's beneficial for SQL Server to do a full table scan per core.
Suppose that you just so happened to have a UNION ALL query that referenced your table six times. In principle, SQL Server could do each table scan with one core independently and combine the results at the end. However, SQL Server will not do this because it will not do pipeline parallelism. Even if it could, I personally can't think of an advantage of doing that here, other than avoiding some of the overhead associated with parallelism.
You could read about the broadcast type of parallel execution and wonder if in that scenario SQL Server could do six complete scans of a table, one with each core. For the broadcast type of exchange SQL Server sends all rows to all consumer threads. However, this can be accomplished with a serial scan on the table followed by a Distribute Streams type of parallel exchange. Indeed, that is what you see in the hash join example. I can't really think of a benefit to doing that scan in parallel, especially when the broadcast type is only used for relatively small tables.
One case in which I suppose this could happen is if you had a parallel nested loop join with an outer table that contained 6 rows and with a table scan on the inner side of the join. In that case, I believe that the table scans will be completed by independent serial threads, so effectively you would get each core doing its own table scan. Of course, such a query probably performs very poorly and is not something to aim for, especially when the outer table has more than six rows.


Getting "not a single-group group function" Below is the query that I am running, but I am getting not a single-group group function error? Please explain why is this error coming and a possible solution to this.

SELECT 1                          AS "Contract",
  COUNT(DISTINCT(cmv.mdl_ver_id)) AS "Number Of Scenarios",
  COUNT(DISTINCT(cmvs.site_id))   AS "Number Of Sites",
  CASE
    WHEN cmve.driver_equip_id is null
    THEN COUNT(distinct(cmve.equip_mdl_id))
  END                             AS "Number of Driven",
  CASE
    WHEN cmve.driver_equip_id is not null
    THEN COUNT(distinct(cmve.equip_mdl_id))
  END                             AS "Number of Driver"
 FROM cost_contract_mdl ccm,
  cost_model_ver cmv,
  cost_mdl_ver_site cmvs,
  cost_mdl_ver_equip cmve
WHERE ccm.mdl_id   =cmv.mdl_id
AND cmv.mdl_ver_id =cmvs.mdl_ver_id
AND cmv.mdl_ver_id =4281021;

If you don't use GROUP BY, then you can only use constant, and aggregate functions is the SELECT clause.

This is wrong:
CASE
    WHEN cmve.driver_equip_id is null
    THEN COUNT(distinct(cmve.equip_mdl_id))
  END

CASE
    WHEN cmve.driver_equip_id is not null
    THEN COUNT(distinct(cmve.equip_mdl_id))
  END

count (distinct case ... end) is probably what you need.

SELECT 1                          AS "Contract",
  COUNT(DISTINCT cmv.mdl_ver_id) AS "Number Of Scenarios",
  COUNT(DISTINCT cmvs.site_id)   AS "Number Of Sites",
  count (distinct CASE
    WHEN cmve.driver_equip_id is null
    THEN cmve.equip_mdl_id
  END)                             AS "Number of Driven",
  count (distinct CASE
    WHEN cmve.driver_equip_id is not null
    THEN cmve.equip_mdl_id
  END)                             AS "Number of Driver"
 FROM cost_contract_mdl ccm,
  cost_model_ver cmv,
  cost_mdl_ver_site cmvs,
  cost_mdl_ver_equip cmve
WHERE ccm.mdl_id   =cmv.mdl_id
AND cmv.mdl_ver_id =cmvs.mdl_ver_id
AND cmv.mdl_ver_id =4281021;

P.s.

DISTINCT is not a function, it is a keyword, which makes the parentheses meaningless.

Instead of COUNT(DISTINCT(x))
It should be COUNT(DISTINCT x)


Designing hotel room schema I tried to give it a chance without thinking much and this is what I came up with:

Room
----

| ID | No.  | Type    | Price   | Beds | Hotel| Size  | TV          | Minibar | Bathroom | Images | Booked |
| ---|------|---------|---------|----- | ---  | ----  | --------    | -----   | ------   | -----  | -----  |
| 1  | 101  | Deluxe  | 5000.00 | 2    | A    | 31m^2 | 32inch(hdtv)| yes     | Private  |
| 2  | 102  | Economy | 4000.00 | 1    | A    | 25m^2 | 30inch(flat)| no      |
| 1  | 103  | Deluxe  | 5000.00 | 1    | A    | 31m^2 |             | yes     |
| 2  | 104  | Suite   | 9000.00 | 2    | A    | 50m^2 | 70inch(LED) | yes     |

I see that there are many facilities which belongs to a room and I tried to create a column for each but it started looking hairy. Also, Hotel column will be a many2one field as well as Images column too.

I know that the booking information shouldn't belong to Room instead it should go to Booking table. I am thinking where should the booking time and checkout time go? in Booking table?

Probably better to work with a metadata table. This, because the combination between different rooms and facilities can be quite hefty.

Have a look at this approach. I created an entity called Establishment so you can have Hotels, Hostels, B&B, etc under the same table, the difference comed from the type defined on your EstablishmentType. Specific details of addressing, for example, can be stored on another table that can be shared with the Individuals Entity (not represented in this example).

Anyway, test schema:

Note that I choose to store the datatype on the Facilities table, this is probably discouraged because its associated with the concept of the God Table, if all you want to do is present a table with the room facilities (frontend-wise) just forget about it.

This approach will allow you to have any number of facilities per room without crowding a single table with NULL values. The model allows, also, any number of Establishments and Rooms types. You could also add another table in between and give the option of separating rooms between different buildings, branches, etc.

Alternatively you could choose to store XML or JSON on the RoomFacilities and let the frontend do the magic and deserialize the content of that table.

{
    "facilities" : {
        "number" : "1"
        "beds" : "2",
        "tv" : "22inch",
        "size" : "20m^2"
        }
}

About the images: You should probably keep them stored on a file server and reference them from within and additional media table.


Can't mysql autocast encoding in query data? i am getting error "Illegal mix of collations"

i know either i need to change db tables to utf8 or convert user input to latin1 or ansii

so i want to know, can mysql do this automatically so i won't need to change table encoding and also no need to modify my exiting queries with COLLATE expression.

There are good explanations why MySQL can't do it automatically:

https://stackoverflow.com/a/12260863/1261575

https://stackoverflow.com/a/21061305/1261575


SQL queries to find tempdb bloating I am trying to find out the queries which would have run and caused bloat to TempDB.

I am able to pull data like TempDB bloating on one particular day where it almost ate upto 300 GB of space, was somewhere in middle of last week.

As per post How to find the SQL statements that caused tempdb growth?, i can find below data but not sure how to interpret this as i can find host and login, but not the actual queries.

status  CPU TIME (in milisec)   Total Scheduled TIME (in milisec)   Elapsed TIME (in milisec)   Memory USAGE (in KB)    SPACE Allocated FOR USER Objects (in KB)    SPACE Deallocated FOR USER Objects (in KB)  SPACE Allocated FOR Internal Objects (in KB)    SPACE Deallocated FOR Internal Objects (in KB)  SESSION Type    ROW COUNT
sleeping    4428523 4618020 5418422 24  0   648 38349248    38351104    user session    1
sleeping    4411334 4578428 5389351 24  0   672 41238720    41240448    user session    1
sleeping    4601627 4820643 5631623 24  0   720 40119104    40121280    user session    1
sleeping    12887505    13667397    15209924    24  0   3528    46007808    46013184    user session    1
sleeping    4589595 4792873 5835456 24  0   528 39140800    39142528    user session    1
sleeping    6416738 6767615 7836356 24  0   1088    40406400    40408768    user session    1
sleeping    8626603 9072173 10416679    24  0   1488    43888640    43891584    user session    1
sleeping    4346345 4554241 5389904 24  0   976 39358208    39360128    user session    1
sleeping    11817344    12427608    13494857    24  0   3208    46888512    46894208    user session    1
sleeping    5182063 5435636 6242939 24  0   680 37975040    37977024    user session    1

Is there a way i can really find queries which caused almost 300 GB bloat last week.

Yes,  am aware of 3rd party tools which would have save the info. But we do not have one on this. How can i find one if possible?

By default, SQL Server does not track and record queries that causing growth of TempDB. Moreover, even running SQL Profiler session would not help in this case.

However, extended events is an option. Brent Ozar has a blog post with an example:

Tracking tempdb growth using Extended Events

XE sessions are more lightweight than SQL Profiler traces. Moreover, your servers, perhaps, already have two or three running now, by default.

XE sessions can be setup to target a ringbuffer, so they can be completely in memory and catch queries that exceed some threshold, for instance 20 GB or more. In this case, risk that they will cause any issues are minimal.


export SQL statements from Oracle 11g database via command line I'd like to export an Oracle 11g database as a series of SQL statements via the command line.  I'd like the functionality of SQL Developer (the process described here https://dba.stackexchange.com/a/10071/7660) but via the command line.

Note that the data must be in a flat file and not a binary format (as with imp/exp or impdp/expdb (Datapump)).

Is this possible?  If so, how?

Depends what you mean by 'from the command line', but you can generate the DDL via the dbms_metadata package (see here for example).

If you want the data as well then you'd then have to roll your own, unless you find a generic script somewhere on the web. Automating the script with dynamic SQL and the data disctionary is certainly possible - it's what SQL*Developer must do ultimtately - it will just need some work to get right, and to handle things like embedded quotes. No quick fix I'm afraid.


The offset 0 is invalid for log file .xel Specify an offset that exists in the log file First of all, I am not DBA but I have to perform some DBA jobs. I am trying to read an extended file using,

  SELECT [object_name],[event_data],[file_name],[file_offset],[Event_Time]
  FROM OPENQUERY(MyServer,'master.dbo.READ_AUDIT @P_RootFile_Name=''Path\DDL*.xel'',@P_File_Name=''Path\file.xel'',@P_Offsit=0')

I am getting this error,

The offset 0 is invalid for log file .xel  Specify an offset that exists in the log file

How do I calculate the offset of a xel file?

ALTER proc [dbo].[READ_AUDIT] @P_RootFile_Name NvarChar(260),@P_File_Name NvarChar(260)=NULL,@P_Offsit bigint =NULL
as

Select * from
(
select *,

DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), CONVERT (XML, event_data).value('(/event/@timestamp)[1]', 'datetime2')) Event_Time
from sys.fn_xe_file_target_read_file(@P_RootFile_Name, null, @P_File_Name, @P_Offsit)

) as T

order by T.Event_Time ASC

From this post https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-xe-file-target-read-file-transact-sql?view=sql-server-ver15

I have found out that initial_file_name and initial_offset should be null initially


Non-deterministic sum of floats Let me state the obvious fist: I completely understand that floating point types cannot accurately represent decimal values. This is not about that! Nevertheless, floating point calculations are supposed to be deterministic.

Now that this is out of the way, let me show you the curious case I observed today. I have a list of floating-point values, and I want to sum them up:

CREATE TABLE #someFloats (val float);
INSERT INTO #someFloats (val) VALUES (1), (1), (1.2), (1.2), (1.2), (3), (5);

SELECT STR(SUM(#someFloats.val), 30, 15) FROM #someFloats;

DROP TABLE #someFloats;

-- yields:
--   13.600000000000001

So far, so good - no surprises here. We all know that 1.2 can't be represented exactly in binary representation, so the "imprecise" result is expected.

Now the following strange thing happens when I left-join another table:

CREATE TABLE #A (a int);
INSERT INTO #A (a) VALUES (1), (2);

CREATE TABLE #someFloats (val float);
INSERT INTO #someFloats (val) VALUES (1), (1), (1.2), (1.2), (1.2), (3), (5);

SELECT #A.a, STR(SUM(#someFloats.val), 30, 15)
  FROM #someFloats LEFT JOIN #A ON 1 = 1
 GROUP BY #A.a;

DROP TABLE #someFloats;
DROP TABLE #A;

-- yields
--   1   13.600000000000001
--   2   13.599999999999998

(sql fiddle, you can also see the execution plan there)

I have the same sum over the same values, but a different floating-point error. If I add more rows to table #A, we can see that the value alternates between those two values. I was only able to reproduce this issue with a LEFT JOIN; INNER JOIN works as expected here.

This is inconvenient, because it means that a DISTINCT, GROUP BY or PIVOT sees them as different values (which is actually how we discovered this issue).

The obvious solution is to round the value, but I'm curious: Is there a logical explanation for this behavior?

Actually, the link you're referring to does not say that floating point arithmetic calculations are always deterministic. In fact, in one of the answers it's mentioned that addition is not associative (meaning (a + b) + c does not necessarily equal a + (b + c)), which is also said in this answer.

If stream aggregation happens to process rows of each group in different order -- which SQL Server is usually free to do; if there is no ORDER BY in the appropriate clause, then the optimizer will choose whatever scan or seek or other query operator will be fastest, regardless of what order that performs the additions in -- then this could explain the behaviour you observe.

Addition is always deterministic: you put in the same two floats, you get the same float out.  But adding floats together in a different order can give a different result.


Very slow SQL Server database recovery We have a very large DB (6TB) that is taking a long time in recovery, after we rebooted the server.

After doing some research we understand that it is because of the large number of VLFs. Right now, it is a waiting game.

In the SQL Server log, we see that the ETA is 24 hours! This is for the first of the three phases!

We have announced everyone that DB will be available in 24 hours, not knowing there are 2 more phases.

Does anyone know, how long the other 2 phases take? Just a ballbark number would do. By any chance are the next 2 phases shorter than the first one?

Here's what we found:

Even though the SQL LOG claimed an ETA of 24 hours, the DB recovered in 12 hours. So the numbers were grossly over-estimated.

Even though SQL LOG was showing percentage recovery completion, it didn't go all the way up to 100%. At around 27%, SQL log said everything okay and recovery complete.

In our case 2 things caused this issue:

Large number of VLFs (697 in our case, for a 70GB log. Although, 697 doesn't seem that large to me)
In our case 800,000+ transactions were required to roll forward. So that could have contributed significantly too.

The answer to my original question: It seems the phase 1 is the slowest; 2 and 3 went very quickly.

We got an answer from Microsoft. Here are some notes from them:

  There are 3 phases of the recovery process

  Analysis: In this phase the transaction log is analyzed and records the information about the last checkpoint and creates on Dirty Page Table, this might capture all the dirty pages details.

  Redo: It’s a roll forward phase. When this phase completes database becomes online so the database was online

  Undo: It’s rollback phase. It holds the list of the active transaction from the analysis phase basically undoing the transactions.

  In Summary:
  This is normal for SQL Server when there is an unexpected shutdown when there is an execution of long running transaction. A database goes through 3 phases of recovery and then comes online – Enterprise edition allows the database to come online after second phase.  This means that there is a rollback running at the moment; need to wait for this to complete. Let the server finish the rollback process if a long-running transaction was terminated. Terminating the server process during the rollback of long-running transaction results in long recovery time.

This might look counteractive; I'm going to post it anyway. If I have to refer in the future, or for folks who might be a victim of this scenario.

It took 13 hours to recover the db. Here are the rough numbers.

Total Recovery time (about 13 hours) = 11 hours analysis + 1 hour redo + 0 hours undo


SQL Server logs in Gmail First off, I'm using SQL Server 2008 R2.

When I set up my maintenance plan and got to the "Select Report Options" step, I selected to email the report to the Operator I already have set up.  This does not allow me to alter the subject, which I believe Gmail uses when it collapses emails into conversations.

Is there a way to trick Gmail to separating them out based on the database?  Or is there a better way from within SQL Server Management Studio to email the reports out?  I have multiple instances, with their own Operator, each Operator is using the same email address (no_reply@noneofyourbusiness.com), but the name is different....but since the subject is the same, Gmail still collapses them into one big conversation.

Does anyone else have to deal with this issue?

The easiest way would be to send the email with differing subject lines. If you just want different database in different conversations make sure the database name is included in the subject line. If you want a new conversation for each email sent, include a timestamp in the subject line.


Resource Governor not limiting CPU / RAM I have an application that is consuming a lot of resources, actually this application drives CPU to. 99% which makes the server unusable (I think there is nothing I can do about this as is a third-party application).

So I decided to create a Resource Pool specific to this Application.

After creating the Resource Pool I limited this application to 2 cores and 25% CPU / RAM, it worked perfectly, CPU of the server was at 25-26% all the time when before it was at 99%...

Then I decided to remove the MAXDOP for the Resource Group and then the application went to 99% again... I have seen that the application query opened 8 parallel processes so I assumed that puting the resouce pool at MAX 10% would do the job (10*8 = 80% CPU) but if I don't limit the Cores then I'm getting again a 99% CPU consumption...

What am I doing wrong?...

If you want to limit the CPU of a resource pool then you need to use the CAP_CPU_PERCENT setting:

  This settings is a hard cap limit on the CPU bandwidth for all
  requests in the resource pool. Workloads associated with the pool can
  use CPU capacity above the value of MAX_CPU_PERCENT if it is
  available, but not above the value of CAP_CPU_PERCENT.

The MAX_CPU_PERCENT setting is designed to limit the average CPU usage. If there isn't contention on the server then it can be ignored:

  If there is available CPU capacity, the workload uses it up to 100
  percent. The maximum value only applies when there is contention for
  CPU resources.

You can read more about different resource pool settings in the documentation.


Does cancelling (stopping early) a CLUSTER command cause a full rollback in Postgres 9.5? Let's say I want to CLUSTER a table.  There's no way to know how long it will take so I just run it.  After a couple hours, I need the data so I cancel the CLUSTER command.  Does CLUSTERing work with temp tables/files such that if I cancel before it's finished, all of that is erased and the hours it ran are wasted or does it make changes "live" such that canceling after a few hours isn't a full waste of time?

If you called CLUSTER without parameters you cannot ROLLBACK at all since that is only applicable to transactions. Quoting the manual:

  This form of CLUSTER cannot be executed inside a transaction block.

But you talk about "CLUSTER a table", so assuming you mean the form:

CLUSTER table_name [ USING index_name ]

Which is transactional. Cancelling this command should not do any harm. Postgres builds the new table (and indexes) in the background and only updates references to physical files when done. The manual again:

  When an index scan is used, a temporary copy of the table is created
  that contains the table data in the index order. Temporary copies of
  each index on the table are created as well. Therefore, you need free
  space on disk at least equal to the sum of the table size and the
  index sizes.

  When a sequential scan and sort is used, a temporary sort file is also
  created, so that the peak temporary space requirement is as much as
  double the table size, plus the index sizes. [...]

Bold emphasis mine. But the work that went into the temp tables is lost when you cancel the command before it can finish.

CLUSTER does not even update table statistics, which is why you should probably run ANALYZE on the table after CLUSTER.

Concerning:

  I need the data so I cancel the CLUSTER command.

You might be interested in pg_repack, which can do the same as CLUSTER without exclusive locks, so you can use it without painting yourself into a corner - or even when you cannot afford downtime at all.

The extension is not shipped with Postgres, though. You have to install it yourself. It's not available on many cloud services.

Related:

Is a clustered index on column A the same as creating a table ordered by A?


Placing Transaction Log on Separate Volume [solid state]? Transaction logs are often isolated on a separate volume. The rationale for this practice, as I understand it, is that the transaction log's data is written sequentially -- and hard drives can execute write operations with far greater speed sequentially as opposed to randomly. This is due to the little needle inside the drive which has to move a much shorter distance when writing sequential blocks of data, as opposed to random writes.

(Sorry for the naive interpretation. Just trying to make sense of what I've read.)

With this in mind… It occurs to me that solid state drives don't have little needles and platters and stuff moving around inside of them. If my database and transaction log are both located on a single RAID 5 of eight solid state drives, is there really any upside to moving the transaction log onto its own separate volume? If the supposed efficiency boost is based upon the premise of sequential writes reducing the distance that needles move and platters spin, and a solid state drive has none of these moving parts, what do I gain by isolating the log?

Short answer, use a single array, there is unlikely to be any performance gain from separating logs from data across 8 SSD drives. See SQL on SSDs: Hot and Crazy Love for a more detailed (and entertaining) commentary on SSDs. Pay particular attention to the notes on correlated failures of SSDs.

Separating logs from data on SSDs is more an RPO (recovery point objective) than performance issue. The notion being that you could reduce your RPO by separating logs from data such that in the event of the data array failing, your log array should/could remain accessible. The cautious would consider a different make/model of drive in each of the two arrays to mitigate the correlated failure issue if RPO was critical.

The comments regarding bus bandwidth are irrelevant. If you need to shift that much IO, you've got bigger issues to worry about.


Issue with Ñ character in name (migrated data from MySQL to PostgreSQL) I'm currently running a test migration of data from MySQL to PostgreSQL. I'm using psycopg2 to perform the task.

I was able to migrate the person's information but when I check the data for comparison, some lastnames with Ñ are showing as "?" in psql while some show as Ñ. I checked both server and client encoding in PostgreSQL and they are set to UTF8.

Where should I start troubleshooting this? I'm new to this kind of task.

You can start troubleshooting this by looking at the bytes behind the characters, to check if there are invalid UTF-8 contents independently of the display layer.

An UTF-8 string can be converted into its series of bytes in SQL with

SELECT convert_to(value, 'UTF-8');

You may also split a suspicious string character by character to look at each codepoint individually, with the character, the Unicode codepoint, and the UTF-8 representation

SELECT c, to_hex(ascii(c)), convert_to(c, 'UTF-8') FROM
   regexp_split_to_table('El Niño', '') AS c;

 c | to_hex | convert_to
---+--------+------------
 E | 45     | \x45
 l | 6c     | \x6c
   | 20     | \x20
 N | 4e     | \x4e
 i | 69     | \x69
 ñ | f1     | \xc3b1
 o | 6f     | \x6f

I would try this on the  fields where some characters display as ? and compare with the fields where the characters display correctly. The goal is to identify what's exactly behind the ?.


Define recursive composite types Is it possible to define recursive composite types in Postgres 13?
create type "t" as (
  "a" int,
  "b" t
);

I am aware that I can use a table with a parent-child relationship. The only disadvantage is that I will need to query each nested level instead of directly accessing the nested data.
I am also aware that I can use jsonb, but I am not sure if it can access the nested data directly without querying each nested level?
What are the other alternatives using PL/pgSQL only?
I need to access each nested level separately.

No, "recursive" type definitions are not possible in Postgres.
You cannot reference a type in CREATE TYPE that is not registered, yet.
(What would terminate the recursion anyway?)


calling a function with parameters in insert Is it possible in SQL ellegantly achieve the following:

insert into my_table (the_geom) VALUES
    geomfromwkb('\x010100000058b4b610a73a27408144b7147f194840',4326)),
    geomfromwkb('\x010100000058b4b610a73a27408144b7147f194840',4326)),
    .....

which involves invoking an external function geomfromwkb with two parameters?

So far I have a un-elegant solution. The first solution is of this type:

insert into mytable (the_geom) select
    geomfromwkb('\x010100000058b4b610a73a27408144b7147f194840',4326)) union
    geomfromwkb('\x010100000058b4b610a73a27408144b7147f194840',4326)) union
    .....

The second one uses the prepare statement:

PREPARE my_insert AS INSERT INTO geom_example (the_geom)
    VALUES (geomfromwkb($1,4326));
EXECUTE my_insert ('\x010100000058b4b610a73a27408144b7147f194840');
EXECUTE my_insert ('\x010100000058b4b610a73a27408144b7147f194840');
statement: DEALLOCATE my_insert;

Question 1: Is there anything more ellegant than this?

Question 2: What does the DB do when it sees a bunch of unions?

Question 3: does the prepare really speed up things compared to one fat insert?

Question 4: the byte sequence is of the geometry type. It is possible to skip the consistency checks (to insure that this is indeed the geometry byte sequence) to speed up inserts?

You can build a 'table' using the VALUES clause and build the function call on top of it:

INSERT INTO wgs84 (wkb_geometry, wkb_geography)
SELECT st_transform(st_setsrid(st_makepoint(col1, col2), col3), col4)
  FROM (
        VALUES (694274.6998, 5341745.2959, 25832, 4326),
               ( 69425.6998, 5341754.23,   25834, 4327)
       ) t (col1, col2, col3, col4);

This is easier to read, to write, and to modify than the repeated function calls.  You can even align your data which sometimes makes finding errors easier.

A version of this can be putting the VALUES into a WITH query.  In some cases it is better to put (preferably using the COPY command) the data into a temporary table and selecting from there in the INSERT statement - this comes handy when the input size gets bigger.


How can I change the data source of a linked report? I'm in a situation where multiple "customers" will be running the same report, but against a different database (with the exact same structure).

What I've done so far is created a generic "Deploy" folder, and deployed all reports to it. I then create "customer-specific" folders, and add linked reports to the source folder. However, I don't seem to be able to make the reports use different data sources (there's no option to change it on the linked report). Is there any way to do this?

Alternatively, how could I handle this situation if I can't use different data sources? I want to avoid re-deploying the same report 10 different times to ten different places only to change the datasource.

If all the databases are on the same instance you could have a central database (named reports, admin or whatever you like). Your report could use that database for your reports datasource.

You could then have a stored procedure that made use of dynamic SQL and a parameter to determine which client database the query should read from.

So your report connects to the reports database and runs the stored procedure with the parameter of 'ClientA', this is passed into the dynamic SQL and executed.

If the databases are in separate instances you would need to use linked servers for this method to work which could cause some security issue so I would look into that before enabling it.


Should I create a new table or save "static" data in all the tables? I'm using Postgres as the database management system.

Currently, I have  a countries table, which has only four countries, i.e. four rows (might add one or two more countries, i.e. rows, later).

--Table 1: countries

id
country_name

I have many tables, referencing to this countries table.

--Table 2: events
name
country_id
date

--Table 3: servers
server_name
country_id

--Table 4: packages
package_name
country_id

The problem is whenever I want a country name, I wanted to join the countries table with other table and make the query to get the country_name value(s).

As the countries table is "static" with only four country rows, should I create a column named country_name in the tables 2, 3, 4 and save the country value as a string, as this will get rid of the countries table and also will get rid of all Joins in my query?

Kindly advice what is the correct approach when dealing with "static" names.

There is another design you could consider. country_name would be considered the natural key for table countries, but it is not the only natural key. Every country has unique codes defined by ISO. These values are as fixed as your country_id would be. They have the advantage of being somewhat human-readable, however. For example the code for the United States of America is "USA".

You could use one of these ISO codes as the key of countries. The ISO code will also be the foreign key in your other tables. Reports and queries will return the ISO codes and humans can read them directly. Only when the country's full name is required will a join be written.

This is discussed further at this question.

I would not duplicate non-key columns across referencing tables just for the sake of it. The opportunities for inconsistencies is just to great. The overhead of an additional join to a four-row table is slight. If, in the future, you achieve great scale and it can be shown categorically that this join is the bottleneck then I would consider denormalizing, having put robust code testing and reconciliation procedure in place.


Applying MySQL 5.1 bin log to MySQL 5.5 Is it possible to apply MySQL 5.1 bin log to MySQL 5.5.

I am assuming that bin log is portable between two machines (same OS, Architechture but different machines running same version of MySQL)

It is possible to replicate from an older version 'master' to a new version master for the next higher major version (5.1 to 5.5 for example): http://dev.mysql.com/doc/refman/5.5/en/replication-compatibility.html

Of course bin logs are portable to the same architecture running on different machines, or replication would be almost useless (there are difficulties with running on different OSes, though).

Since your question only asked about applying a bin log to the second server, I might mention that if you don't have the 'master position' of when the second server should begin reading the binary log, you might have inconsistencies in data. Especially if it has been a long time since you've taken a full backup and no longer have the relevant log entries.

But at the heart of it, Binary Logs from an older major version to the next major version is supported.


Making Production database as Publisher and Distributor for Snasphot Replication In our environment we are using Log Shipping as DR. Our primary database is a  clustered instance. My requirement is to set up a snapshot replication for reporting purposes.

My plan is as follows:

Make the production database as the distributor and publisher.
The subscriber will be a read only for reporting.

My question is:

Will there be any problem if the production database acts both as distributor and subscriber?

Since your database is 100GB, you can use backup and restore method with compression (make sure Instant file Initialization is enabled).

Another approach will be to use logshipping with delaying the restoring of log files on the standby server and having the database in standby read mode ( WITH STANDBY).

The way you do is -

take your regular log backups on your primary (in your case every 15 mins)
keep the copy job as is to copy log files to network share.
Choose the option to delay the restoring of logs as per your threshold.
To speed up restoring the logs - change the setting of logshipping to norecovery
once done restoring the logs, change it back to standby.

If you want to automate the entire process, use dbatools - Log Shipping functions/cmdlets


CONVERT() returns garbage with CLOBs in Oracle I am looking to repair a table of data in Oracle, ideally via unprivileged SQL, which has had UTF-8 data inserted into a UTF-8 database, using the Latin-1 character set by mistake.

The symbol β GREEK SMALL LETTER BETA should have gone into the database, but instead the two characters Î² have gone in...as the two UTF-8 characters
Î LATIN CAPITAL LETTER I WITH CIRCUMFLEX followed by ² SUPERSCRIPT TWO.

This example code demonstrates the problem and the fix, however it only works with VARCHAR columns.  As soon as a CLOB is used, the conversion fails:

-- This must return AL32UTF8 for this example to be valid
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';

CREATE TABLE EXAMPLE (T VARCHAR2(20));
INSERT INTO EXAMPLE (T) VALUES ('Example Î²');
SELECT T FROM EXAMPLE; -- Should return 'Example Î²'
SELECT CONVERT(T, 'WE8ISO8859P1') FROM EXAMPLE;
UPDATE EXAMPLE SET T=CONVERT(T, 'WE8ISO8859P1');
SELECT T FROM EXAMPLE; -- Should return 'Example β', problem fixed
DROP TABLE EXAMPLE;

However if the VARCHAR2(20) is changed to CLOB then this no longer works.  CONVERT() returns garbage characters.  I can use TO_CHAR() to work around the problem, but eventually I get an error that the CLOB is longer than 4000 chars so TO_CHAR() fails.

Is there a way to get the above example working, when using a CLOB column that's longer than 4000 chars?

DBMS_LOB package has conversion functions as well. Unfortunately there is no support for converting CLOB to CLOB and change characterset in one step, so the data is first converted to BLOB, then back to CLOB.

CREATE TABLE EXAMPLE (T CLOB);
INSERT INTO EXAMPLE (T) VALUES ('Example Î² ');

begin
  for i in 1..12
  loop
    update example set t = t || t;
  end loop;
  commit;
end;
/

select dbms_lob.getlength(t) from example;

DBMS_LOB.GETLENGTH(T)
---------------------
                45056

Now the conversion part:

create or replace function repair_data (p_clob clob) return clob as
  l_blob blob;
  l_dest_offset number := 1;
  l_src_offset number := 1;
  l_lang_context number := 0;
  l_warning number;
  l_result clob;
begin
  dbms_lob.createtemporary(l_blob, true, dbms_lob.call);
  dbms_lob.createtemporary(l_result, true, dbms_lob.call);
  dbms_lob.converttoblob(l_blob, p_clob, dbms_lob.lobmaxsize, l_dest_offset, l_src_offset,
                         nls_charset_id('WE8ISO8859P1'), l_lang_context, l_warning);
  l_dest_offset := 1;
  l_src_offset := 1;
  l_lang_context := 0;
  dbms_lob.converttoclob(l_result, l_blob, dbms_lob.lobmaxsize, l_dest_offset, l_src_offset,
                         nls_charset_id('AL32UTF8'), l_lang_context, l_warning);
  return l_result;
end;
/

select repair_data(T) from example;

REPAIR_DATA(T)
-----------------------------
Example β Example β Example β ...

update example set t = repair_data(t);
...


How export a database based in a primary key of a table? So, I'm trying to export all the DB structure of my MySQL db.

Attached is my DB structure, I have more then this, but I want these tables only.

Basically Tools relate to Factsheets and Factsheets to Content and Images, then to everything else.

I need to export based on TOOL ID. Which is only defined on the Factsheets table as a FK and PK only on the Tools Table.

Any ideas?

I can do command line, mysql workbence or PHP-my admin.

UPDATE #1:

Ok to clear some stuff up, lets forget about glossary. A tool is a collection of factsheets, factsheets are collections of factsheetcontent (1 factsheet to many contents) and each piece of factsheetcontent is related to one factsheettitle.

Basically, I'm needing to query based on the TOOLID, ex the Citrus Pests tool with ID of 2, to get all the factsheets which then get all the content under them.

Relations:

Tool to Factsheet: 1 to Many

Factsheet to FactsheetContent: 1 to Many

FactsheetContent to FactsheetTitle: 1 to Many

Factsheet to FactsheetImages: 1 to many

FactsheetImages to image: 1 to 1

Starting from the tool I need to get factsheets, then all things under them. I need the Database structure. Otherwise I'd do a Select with a bunch of joins and export to a CSV

This is what I need, we can drop the glossary as thats not needed. This is what I'm after, using the command line mysqldump works great for the 2 table that has the toolid, after that it quickly fails and blows apart--starts to throw lots of errors.

Any ideas?

Error I get using RolandoMySQLDBA suggestion:

./getDbtablesBasedOnToolID

: command not foundnToolID: line 6:

ERROR 1045 (28000): Access denied for user '-p'@'localhost' (using password: NO)

ERROR 1045 (28000): Access denied for user '-p'@'localhost' (using password: NO)

ERROR 1045 (28000): Access denied for user '-p'@'localhost' (using password: NO)

ERROR 1045 (28000): Access denied for user '-p'@'localhost' (using password: NO)
: command not foundnToolID: line 22:
: command not foundnToolID: line 32:
'@'localhost' (using password: NO) when trying to connect
: command not foundnToolID: line 39:
'@'localhost' (using password: NO) when trying to connect
: command not foundnToolID: line 46:
'@'localhost' (using password: NO) when trying to connect
: command not foundnToolID: line 53:
'@'localhost' (using password: NO) when trying to connect
: command not foundnToolID: line 60:
'@'localhost' (using password: NO) when trying to connect

1) GUI option

You can use Toad for MySQL Freeware from here.

To take an export that creates inserts for a specified set of data, after creating a connection to your mysql database, you can choose Tools->Export->Export Wizard

Click Next, then Add Button

Choose query as the Export category and then type your SQL statement for one of the tables

SELECT col1, col2 ...
  FROM table_name
 WHERE tool_id='xyz'

Then Click next - choose SQL SCRIPT any other relevant options

Choose file as the output

Repeat this for as many tables as required

Then execute it to create a set of insert scripts.

You can take a structure export either using TOAD or phpmyadmin and the combination should give you what you are looking for which i assume is a cut down set of data for a test environment.

2) Command line option

Use mysqldump at the command line and list all tables that have the tool_id column

  mysqldump -t -u [username] -p test mytable anothertable --where="tool_id = 1"

This should generate a set of insert statements and as above, if you load these scripts into an empty schema exported with phpmyadmin using structure only, it should give you what you need.


Way to set all queries to run through SSEM to READ UNCOMMITTED? We have a hosted application where clients do not get SQL Access.  We have a new client that needs access to SSMS to write queries for reports.  We are installing SSMS on a utility box for the client to log into and use.  The clients accounts will be in the data_reader group.  I want to force all queries to run READ UNCOMMITTED
So not to cause blocking of the application with badly written queries

If by SSEM you mean SQL Server Management Studio (SSMS), there is an option you can set when logged in as this user to run as READ UNCOMMITTED by default:

But they could easily override that by unchecking the box, setting their own isolation level manually, or calling stored procedures that override. Features like Resource Governor can control a lot of things, but I don't think there's a way to hard-code isolation level for all queries a user submits...

Though, this post might be useful:

Can I force a user to use WITH NOLOCK?


Risks when running a MySQL Galera Cluster in production without binlogs? First of all: I assume that this is no real good idea.

However, this Bug in MariaDB 10.1.xx crashes single cluster nodes.

https://jira.mariadb.org/browse/MDEV-9510

One of the workarounds is to disable the binlogs.

I personally think, that this comes with a lot of other risks, like inconsistent databases or not being able to get the database into a consistent state after restarting.

I need to understand the risks and drawbacks before eventually taking the decision to disable the binlogs.

As per as best practices for setting up MySQL Galera cluster , it is compulsory to enable binary logs and binlog format as ROW.

Not sure why you are interested in disabling binlogs , few are the disadvantages:

unable to debug queries
unable to setup a replica server if required.


How can I detect the availability of sp_addextendedproperty on sql server How can I find out if sp_addextendedproperty is available before trying to use it in a sql script?

I gather it's not available on Azure.

(I'm working on this bug, if you're interested).

Use OBJECT_ID to see of the object exists.  The function will return NULL if not:

IF OBJECT_ID(N'sp_addextendedproperty', 'P') IS NOT NULL
    EXEC sp_addextendedproperty ...


SQL Server Windows NT 64 bit Sudden High CPU Spike I am facing a strange behavior of my SQL Server 2016 web edition. SQL Server Windows NT 64 bit suddenly jump to use 90% of the CPU and then it goes down after 5 sec, then the spike comes again after 3 to 5 mins (normal usage is just aroud 2% to 5% of cpu)

My server specs

Windows Server 2012 R2 Standard Evaluation (9600 build)
MS SQL Server 2016 Web
Intel Xeon E3-1245v5
64GB DDR4 ECC (2400 MHz)
3x480 GB SSD (Micron)

Server is up to date with latest updates installed and MS SQL Server is on default settings.
How can I find what is causing this. Please help me. Thanks

Please check the screenshots

There are a few ways to approach this.

Download sp_WhoIsActive and run it when you notice a CPU spike, or log it to a table and query the table looking at the CPU column (maybe ORDER BY CPU DESC).
Examine the plan cache using a free script -- I co-author one called sp_BlitzCache. By default, it will return the top 10 CPU consuming queries in your server's plan cache.
Buy a monitoring tool like SentryOne Performance Advisor and use it to examine your server during a spike.


Table attribute for sorting I have 3 Tables, a User table, a Tracks table and a Likes table. Users can like as many tracks as they want and one track can be liked by multiple users. In my Likes table I have two foreign keys to the User and to the Tracks table.

I'm getting the data from an API, that sends it in the correct order, latest likes sent first. But there's no field like a date when it was liked on. It's only the order. Now I want to put the likes in my database in the correct order, so I made a third column in my Likes table that is named liked_order. It's an AUTO_INCREMENTED value, therefore my foreign keys and the liked_order attribute are my primary keys for this table. Unfortunately it is now possible for a user to like a track twice. If the AUTO_INCREMENT value wouldn't have to be a primary key everything would be fine.

I've ran out of ideas...

EDIT: I have to use MySQL

Without seeing the table schema, I see two options:

Adjust the schema so that the Foreign Keys(FK) are not part of the Primary Key(PK); only the AUTO_INCREMENT column is the PK. The Foreign Keys can still be Unique Indexes to prevent multiple likes of a particular track by the same user. This won't work of course if the FKs are part of a partitioning scheme.
Take out the AUTO_INCREMENT column and add a TIMESTAMP(0) DEFAULT CURRENT_TIME column. The timestamp column will take up as much space as an INT AUTO_INCREMENT column. The downside to this is all rows before adding the timestamp column would not have an order.


Is my index too expensive to maintain? I just indexed a view that returns 126 millions rows.

There's a process that uses this view to update rows using a trigger and it's by far the most expensive process in my database.

I'm monitoring this new index usage DMVs and I'm getting a strong feeling that there's too much processing power involved in maintaining this index to balance it's usefulness.

Running the query bellow, it tells me that every 2-5 seconds there's one user seek on this index. During the same timeframe there will have been around 250-1000 user updates. Since it's creation 15 minutes ago, USER_SEEKS=386 while USER_UPDATES=102 876.

Is there a magic ratio where you can safely say it's not worth it to maintain such an index ?

SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
       I.[NAME] AS [INDEX NAME],
       USER_SEEKS,
       USER_SCANS,
       USER_LOOKUPS,
       USER_UPDATES
FROM   SYS.DM_DB_INDEX_USAGE_STATS AS S
       INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID
WHERE  OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
       AND S.database_id = DB_ID()
       and I.NAME ='<index_name_goes_here>'

Is there a magic ratio where you can safely say it's not worth it to maintain such an index ?

There is not a magic number that exist. You might have an index that is used rarely (compare to number of updates) but supporting that query is important for your business/users.

It is a call that only you can make based on your business/user requirement.

If you want to test, do not drop the index. You can disable the index that way your metadata about the index is preserved. Here is a good post about that.

Disabling vs. Dropping Indexes by Jes Borland.


Match all in a full text search Using PostgreSQL 9.4, is there anyway that we can specify the tsquery to match all?

I have tried:

SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector @@ '*'::tsquery;

But it doesn't seem to work. It returns false.

Wrap you expression in a CASE statement:

SELECT * FROM tbl
WHERE  CASE $1 WHEN '*' THEN TRUE
       ELSE some_tsvector_column @@ $1::tsquery END;

This way you can pass '*' to disable the filter or any other valid tsquery string to actually filter.


Alter table Alter column Performance We have a table with more than 10million rows.

We are altering its column type like this:

ALTER TABLE MY_TABLE ALTER COLUMN MY_COLUMN VARCHAR(3)

What would be the fastest way to do this? Alter column needs to touch EVERY  row right? Is there a way to make this faster?

According to questions and answers here, I'm thinking about, create a new table and migrate data, or create a new column with VARCHAR(3) and migrate only the data inside this column and drop the old column.

Obs. It's taking more than 3hrs.

Referencing Aaron Bertrand's answer to a similar question, he says:

  Depending on what change you are making, sometimes it can be easier to
  take a maintenance window. During that window (where nobody should be
  able to change the data in the table) you can:

  1). drop any indexes/constraints pointing to the old column, and
  disable triggers

  2). add a new nullable column with the new data type (even if it is
  meant to be NOT NULL)

  3). update the new column setting it equal to the old column's value
  (and you can do this in chunks of individual transactions (say,
  affecting 10000 rows at a time using UPDATE TOP (10000) ... SET newcol
  = oldcol WHERE newcol IS NULL) and with CHECKPOINT to avoid overrunning your log)

  4). once the updates are all done, drop the old column

  5). rename the new column (and add a NOT NULL constraint if
  appropriate)

  6). rebuild indexes and update statistics

  The key here is that it allows you to perform the update incrementally
  in step 3, which you can't do in a single ALTER TABLE command.

  This assumes the column is not playing a major role in data integrity
  - if it is involved in a bunch of foreign key relationships, there are more steps.


How to reinstall SQL Server Integration Services I've deinstalled from a Production server by mistake In deinstalling a named instance of SQL Server 2012 from a Production server, inadvertantly, I selected SSIS as a product to deinstall.  Now, I need to reinstall it with the least disruption possible.

Run the installer again.

Installing/Configuring SSIS and SSRS to existing SQL Server 2008 database services

    Run SQL Server setup program and on the “Feature Selection” page click the checkboxes associated with the features you would like to have installed. When finish, reapply any service pack the instance have applied before.


Having DB Mirroring and Replication together Can anybody help me understand why do one go combination of Database Mirroring + Replication.
Why can't I just have Database mirroring for high availability of my database. What is the need of combining replication as well.
In what scenario's we choose combining both these technologies.?
Can anyone give an example of combining these 2 technologies ?
I am not sure if people a make things complex combining all high availability solutions. ( Clustering + db mirroring + replication)

When you have Mirroring and Replication you usually have two different problems that you are trying to solve.  You are using the mirroring to solve a high availability problem and you are using replication to give yourself a scale out solution probably for reporting.


Is there a difference between an xtrabackup taken with and without --safe-slave-backup? Currently I'm studying Percona's xtrabackup. In the manual in the paragraph "Taking Backups in Replication Environments" it says that using the --safe-slave-backup
option is always recommended and I understand the reasons behind it.

I'm just wondering now, if there is actually a difference in the result when I do not use this option. After applying the logs on a backup, I don't see why there should be a difference between a backup taken with or without this option.

I'm asking, because in our production environment we do not use this option. The backup runs at midnight, but it failed this night. The backup is taken on a slave and I'd have a bad feeling about stopping the SQL thread now to take the backup.

As the manual says:

  this option stops the slave SQL thread and wait to start backing up until Slave_open_temp_tables in SHOW STATUS is zero. [...] the SQL thread will be started and stopped until there are no open temporary tables

The reason for this is because Percona Xtrabackup basically mimics a controlled crash/shutdown of the server, and temporary tables can make the slave inconsistent, as you can see on the MySQL manual. This is not a problem of consistency per se (buckups will be consistent with the given timestamp/binlog), but it may make a slave lose some transactions when resynchronized with a master (typical usage- cloning a slave for creating another).

This does not happen if you use ROW-based replication, so I would recommend you using that. But some people cannot or do not want to use it, so this is the way to be 100%  sure that new slaves work well. On a typical replication scenario, using --safe-slave-backup may not be very problematic, assuming there are not many temporary tables being created, but that is the workaround (and typically these options are added because someone had a problem in the past).

What I would recommend you is to use always --slave-info unless you are using GTID replication.

As the own manual says, using pt-table-checksum to test the backups is a good piece of advise.


Query to find missing status events I'm hitting a road block as the best way to query a related table. I have two tables: main and status_history.

Right now, I can identify records from main with no status_history at all, but I need to query records from main that did not make it to the delivery status.

Current query is:

SELECT
   m.id
   ,m.date_completed
   ,s.status_message
FROM
   main m
   LEFT JOIN status_history s ON s.main_id on m.id
WHERE
   m.date_completed IS NOT NULL and s.status_message is null;

That will show me everything that has been completed but has no status_history at all. The problem is, that a typical delivery has multiple status histories (let's say for simplicity: new,confirmed and delivered).

I want to find all records that are missing the 'delivered' status history (hopefully this won't make a difference, but in the real DB, I'm looking for a dozen different messages that are equivalent of delivered). I'm guessing this is a simple sub-query but I'm having a mental block right now.

Examle schema for the above is pared down to:

main table:
id (Unique Key)
date_completed (datetime)

status_history table:
id (unique key)
main_id (INT)
status_message (varchar)

For example, main may look like:

+----+---------------------+
| id | complete_date       |
+----+---------------------+
|  1 | 2016-05-09 09:12:09 |
|  2 | 2015-05-21 14:39:16 |
|  3 | NULL                |
|  4 | 2015-05-21 13:57:05 |
|  5 | 2015-05-21 14:43:17 |
|  6 | 2015-05-26 08:49:17 |
|  7 | 2016-05-09 14:20:06 |
|  8 | 2015-05-26 14:07:53 |
|  9 | 2015-05-21 15:16:08 |
| 10 | 2015-05-21 15:34:02 |
+----+---------------------+

And status_history would have this (plus a datetime stamp column):

+----+---------+----------------+
| id | main_id | status_message |
+----+---------+----------------+
| 1  |   1 | New                |
| 2  |   1 | Confirmed          |
| 3  |   1 | Delivered          |
| 4  |   3 | New                |
| 5  |   4 | New                |
| 6  |   3 | Confirmed          |
| 7  |   4 | Confirmed          |
| 8  |   5 | New                |
| 9  |   5 | Confirmed          |
| 10 |   4 | Delivered          |
+-------+---------+-------------+

So, if the above was the entire output of both tables, I want to find that m.id's 2,5-10 are missing "Delivered" status messages.

@spthorn's answer is correct but IN gives sometimes unexpected results when the columns involved are nullable.

If you want to avoid such surprises, it's better to use NOT EXISTS or the LEFT JOIN / IS NULL structure you already have. The change you need is minimal. Just add the s.status_message = 'Delivered' condition in the joining ON clause:

SELECT
    m.id,
    m.date_completed
    -- ,s.status_message      -- no need for this either
FROM
   main m
   LEFT JOIN status_history s ON  s.main_id on m.id
                              AND s.status_message = 'Delivered'
WHERE
    m.date_completed IS NOT NULL
    AND s.main_id IS NULL ;

The NOT EXISTS version would be:

SELECT
    m.id,
    m.date_completed
FROM
   main m
WHERE
    m.date_completed IS NOT NULL
    AND NOT EXISTS
        ( SELECT *
          FROM status_history s
          WHERE s.main_id = m.id
            AND s.status_message = 'Delivered'
        ) ;


PostgreSQL Role Configuration I am using PostgreSQL 9.3. I am working with a friend of mine on a project on which both of us would need access to the same database and all of the permissions on every table.

So I created a Role (Group) assigning all of the permissions over said database.

Then I created two roles (users) for me and my friend and added them to the Group Role and Inherited all of the Group Role's permissions. So no problem whatsoever.

The problem comes when I create a new table, because the ownership of the table is set to me instead of the group role. Which causes that my friend can't edit the tables I create, and I can't edit the ones he creates.

I did some research and found options like removing the INHERIT flag from my user roles and using a SET ROLE GROUP_ROLE before executing any query. This works but I am looking for a more automated process. We work on Navicat, so we create all our tables visually, fetching the sql, pasting it into the query builder, and adding the SET ROLE line on each query so that ownership is set to GROUP_ROLE makes the process a bit sluggish.

With further research I found out about DEFAULT PRIVILEGES, which I tried but had no success. I didn't understood this option quite well.

EDIT

What I tried was the following commands:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO usergroup;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO user1;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO user2;

But still, when user1 creates a new table, user2 doesn't have enough privileges to edit it.

Is there any way that we (me and my friend) can share the same database with ALL the privileges and that if my friend creates a new table I automatically have the permissions to edit it and vice versa?

Is there an instruction so that PostgreSQL sets the ownership automatically to the GROUP_ROLE instead of the user executing the query?

Or maybe changing the default privileges so that when my_user creates a new table, all of the users under my_group share the same permissions?

PD. I'm running Ubuntu 14. And when I was trying to do the DEFAULT PRIVILEGES configuration I was using the default user I guess.

I just log in to my server via SSH and do the following,

sudo su postgres
psql
/c my_database
ALTER DEFAULT PRIVILEGES .... ... ... ..

Thanks,

The PostgreSQL 9.3 Documentation has outlined how to alter default permissions. Follow this link to learn more!

Here is an excerpt that demonstrates how one would change the default permissions for a GROUP:

  ALTER DEFAULT PRIVILEGES
      [ FOR { ROLE | USER } target_role [, ...] ]
      [ IN SCHEMA schema_name [, ...] ]
      abbreviated_grant_or_revoke

  where abbreviated_grant_or_revoke is:

  GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
      [, ...] | ALL [ PRIVILEGES ] }
      ON TABLES
      TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

You may also benefit from learning more about ROLES (follow link to documentation here). That would make it easier if you ever needed to re-assign permissions to other people to.

  It is frequently convenient to group users together to ease management of privileges: that way, privileges can be granted to, or revoked from, a group as a whole. In PostgreSQL this is done by creating a role that represents the group, and then granting membership in the group role to individual user roles.

A good place to do some practice/tutorial work on permissions, groups, roles, etc. is a site called Tutorials Point. They've got examples that will help you work through setting up permissions.


Tracking Down a Problem User How do I track down a user that may be running a relatively quick query, but running it excessively?  For examples, they may:

be running a report over and over and over again
have a book set on their keyboard
have a refresh interval set very low

Other users may be running the exact same queries and reports, but their load on the system is minuscule.

I know how to track down problem queries, waits, etc., but I'm looking for a cumulative user-specific problem.

Apart from sp_whoisactive as mentioned in earlier answer, i can suggest running extended events for the type of query you want to track.

You can use the events and action as mentioned in the log Tracing Query Performance with Extended Events

I really like the extended events for the amount of data you can capture out and its not that expensive depending upon how much you capture.

Additionally you can use sp_blitzwho Forget sp_Who and sp_Who2:
sp_BlitzWho tells you what’s really happening.


Optimize simple query in SQL Server This should be a quite easy query, but I honestly think its execution time can be improved.

select idTag,MAX(pctimestamp) AS PCTIMESTAMP,getdate() AS NOW, datediff(SECOND,MAX(pctimestamp),getdate()) AS DELAY
from ValuesTagsOPC
group by IdTag

This query returns 1386 rows from 'ValuesTagsOPC' table, that contains about 40 million rows, and has the following structure, retrieved by the create script:

CREATE TABLE [dbo].[ValuesTagsOPC](
    [IdTag] [int] NOT NULL,
    [TTimeStamp] [datetime] NOT NULL,
    [PCTimeStamp] [datetime] NOT NULL,
    [Value] [nvarchar](50) NOT NULL,
    [Quality] [int] NOT NULL,
 CONSTRAINT [PK_ValuesTagsOPC] PRIMARY KEY CLUSTERED
(
    [IdTag] ASC,
    [PCTimeStamp] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Obviously, there is a clustered index on its primary key.
Time and IO statistics from SQL Server are the following (sorry, it's in spanish):

(1386 filas afectadas)
Tabla 'ValuesTagsOPC'. Recuento de exámenes 5, lecturas lógicas 224612, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

 Tiempos de ejecución de SQL Server:
   Tiempo de CPU = 9578 ms, tiempo transcurrido = 3019 ms.

I've checked that the estimated execution plan is the same as the real one, and it tells me that 94 % of the cost comes from the cluster index scan. What I don't understand is why it is not performing a seek instead of a scan, since all required fields in the query are included in the clustered index....

Thanks in advance!!

It has no WHERE clause so it must process and aggregate all 40 million rows. SQL Server will not take advantage of the index order and skip scan ahead to the next IdTag once it has found the MAX for the current group but will continue processing the other rows in that group. Each group has an average of about 30,000 rows.

As you have another table that lists the 1,386 distinct IdTag types then you could try the following instead.

SELECT D.IdTag,
       V.PCTimeStamp,
       V.Now,
       datediff(SECOND, V.PCTimeStamp, V.Now) AS DELAY
FROM   DescriptionTagsOPC D
       CROSS APPLY (SELECT TOP 1 *,
                                 getdate() AS Now
                    FROM   ValuesTagsOPC V
                    WHERE  D.IdTag = V.IdTag
                    ORDER  BY PCTimeStamp DESC) V

To replace the scan of 40 million rows with  1,386 seeks.

If that table was not available then a recursive CTE could be used to achieve similar results.

WITH    RecursiveCTE
AS      (
        SELECT TOP 1 IdTag, PCTimeStamp
        FROM ValuesTagsOPC
        ORDER BY IdTag DESC, PCTimeStamp DESC
        UNION   ALL
        SELECT  R.IdTag, R.PCTimeStamp
        FROM    (
                SELECT  V.*,
                        rn = ROW_NUMBER() OVER (ORDER BY V.IdTag DESC, V.PCTimeStamp DESC)
                FROM    ValuesTagsOPC V
                JOIN    RecursiveCTE R
                        ON  V.IdTag < R.IdTag
                ) R
        WHERE   R.rn = 1
        )
SELECT  IdTag,
        PCTimeStamp,
        getdate()                                 AS NOW,
        datediff(SECOND, PCTimeStamp, getdate()) AS DELAY
FROM    RecursiveCTE
OPTION  (MAXRECURSION 0);


Is MySQL replication okay after writing to slave then making identical write to master? I'm running MySQL 5.5.31 and I have a master/slave setup. I accidentally ran a mysql UPDATE on the slave, a simple one-row update like:

UPDATE thetable SET somefield = 'asdf' WHERE id = 1234

I realized my mistake quickly and then ran the same exact statement on master. I probably should have looked into possible issues after the update to slave before the update to master, but I wasn't thinking clearly, so I don't know what the state was like between these operations.

Anyway, after that, worried that I may have caused a sync problem, I did a SHOW SLAVE STATUS and the replication seemed to be working fine, fully caught up with master, and with no error messages. I also did a CHECKSUM TABLE thetable on both master and slave, and the value returned was identical.

So everything seems to be running fine, but I want to avoid running into any potential problems. Given what I described, should everything be okay here on out? Is something about this resulting state going to pop up and bite me? In general, if you make an update to slave followed immediately by an identical update to master, will sync problems be avoided?

Since the end result on both master and slave was identical, I doubt you'll have any real issues. Though it's definitely best to avoid this whenever possible, since your next query might not be so benign...


Why does my SELECT query not return null values? I have the following SQL script:
CREATE temporary table if not EXISTS the_values (
 key SERIAL,
 value INTEGER NULL
);

insert into the_values(value) values (null),(1),(null),(2),(3),(4),(5),(6),(10),(null),(null);

select *
from the_values
where value not in (1,2,3,4,5,6,10);

And I noticed that the query:
select *
from the_values
where value not in (1,2,3,4,5,6,10);

Does not return the rows having value NULL, and that caught my attention. Therefore, I want to know why that happens. I am interested more about the technical aspect of this phenomenon rather that the obvious solution:
select *
from the_values
where value not in (1,2,3,4,5,6,10)
   or value IS NULL;

Generally, avoid NOT IN when NULL values can be involved on either side. The Postgres Wiki suggests as much. And avoid NOT IN (SELECT ...) in any case. The Wiki has the explanation. And also happens to have the perfect answer to your core question:

NOT IN behaves in unexpected ways if there is a null present:

select * from foo where col not in (1,null); -- always returns 0 rows

select * from foo where col not in (select x from bar);   -- returns 0 rows if any value of bar.x is null

This happens because col IN (1,null) returns TRUE if col=1, and NULL
otherwise (i.e. it can never return FALSE). Since NOT (TRUE) is FALSE,
but NOT (NULL) is still NULL, there is no way that NOT (col IN (1,null)) (which is the same thing as col NOT IN (1,null)) can return
TRUE under any circumstances.

Here is a maybe not so obvious solution:
SELECT *
FROM   the_values
WHERE  value IN (1,2,3,4,5,6,10) IS NOT TRUE;

Or:
..
WHERE  value = ANY('{1,2,3,4,5,6,10}') IS NOT TRUE;

It's shorter and typically faster than your "obvious" one.
For long lists, consider switching to a different (faster) technique:

Select rows which are not present in other table


Accidentally imported data tables into Master database — Any cleanup needed beyond deleting? We accidentally imported some data tables into our Master database with a CREATE TABLE and BULK INSERT script.  It was probably about 20 GB of data before we caught it.

We immediately deleted all of the tables.

What other cleanup or diagnostic tools should we run?  Should we be on the lookout for anything?

If you have already deleted the Imported Tables then you are fine. You will not be required to do anything else.

This can happen and to get rid of this Use the Import/Export Wizard or Properly use the databasemame.Schema.Table.


Integrate MySql Full Text searches in CMS I'm developing my own CMS and using MySql v5.5 DB to save the content items records.

Due to a technical limitations, I can't upgrade my database to a newer version that support Full-Text searches on InnoDB tables.

Would it be a good practise to use MyISAM tables for textual searches while the "real" data is saved in InnoDB storage engine?

Example:

The user create content item and save it to the database
All the content item fields are saved to an InnoDB table.
All textual fields are STACKED together and saved in a single field inside a MyISAM table which also include additional ID field with reference to the original content item.

When the user perform a textual based search, I'll query against the MyISAM table using a Full-Text search query which will give me references to all the matching records in my InnoDB table.

The bottom line - Is that considered to be a good solution performance-wise and for improving the searching capabilities of my CMS, or should I stick with the good old LIKE operator and query using only the InnoDB table?

It works.  It performs reasonably well.

You have two tables, one is InnoDB and contains most of the attributes of an entity.  The other is MyISAM, is 1:1 with the first table, and contains a TEXT field, plus FULLTEXT index.

The relevant query looks something like:

SELECT ...
   FROM inno_tbl i
   JOIN ft_tbl f ON i.id = f.id
   WHERE i.stuff...
     AND MATCH (f.text) AGAINST (...);

I think that the MATCH will always occur first, even though the tests on i might be more selective.  That is the nature of FULLTEXT.

jkavalik mentioned some consistency issues; but these can mostly be avoided by carefully picking the order in which you INSERT into the two tables, and whether you use REPLACE or IODKU instead of a plain INSERT for one of the `INSERTs.

(I believe I have done what you describe in one or two projects.  I have since measured that InnoDB's FULLTEXT seems to be faster.)

Bottom line: Go ahead and do it.

Addendum  How to order the statements to minimize data integrity problems.

BEGIN;
INSERT into InnoDB table
$id = SELECT LAST_INSERT_ID(); -- assuming you are using an AUTO_INCREMENT
INSERT INTO MyISAM_table
    (id, text)  VALUES  ($id, '$escaped_text')
    ON DUPLICATE KEY UPDATE
        text = '$escaped_text';
COMMIT;

Cases...

There is no integrity problem if both INSERTs succeed or both fail.
If there is an error on the MyISAM INSERT, you should catch it and ROLLBACK instead of COMMIT.  Hence good integrity.
If the MyISAM succeeds but the COMMIT fails, there will be an extra row in the MyISAM Table, for which there is no InnoDB row.  Two cases...

If a FULLTEXT search hits that row, then the JOIN to the InnoDB table will fail, thereby getting the 'right' answer (at a minor cost).

If you come along later and reuse that id to re-insert the row (or insert a different row), then the IODKU will "do the right thing".  All is well.

Note (aimed at other readers):  This technique of mixing an InnoDB table with a non-transactional INSERT works in other cases.  Consider putting an image (.jpg) in a file, while putting the image's 'meta data' in an InnoDB row.  At worst, you might have an extra or duplicate image stored in the file system.


MySQL proxy and SHOW PROCESSLIST query time I have MySQL replication currently occurring between two masters bidirectionally, one in a data center locally, another over at Amazon EC2.

Everything seems to function normally replication wise, no issues per se, except for the occasional query that causes a collision but those are few and far between. Recently I set up mysql-proxy to attempt to load balance the two servers, the load balancing really on kicks in after the local machine receives 40 or more connections at which point subsequent database connections get shuffled off to the EC2 machine.

Something we noticed recently is that mysql proxy will notify us that there are 41 connections and then start its balancing. However when I connect to the local machine and do a SHOW PROCESSLIST; it may only provide me with 30 connections.

Anyone have any idea why this maybe?

In addition to that as a result of issuing the SHOW PROCESSLIST; command I've noticed that there are a great many queries running on both machines that state they have been running in excess of 5000 seconds. I'm pretty sure these are "zombie" queries but does anyone know why they are created in the first place?

FYI We're running mysql version 5.1.54 on the latest versions of ubuntu and debian.

Any ideas would be extremely helpful.

[Addendum]

Turns out we're not using mysql_pconnect and are infact using the mysqli libraries. I still have been unable to find out why this happening and will report back once I find out.

I am against writing to both Masters in a dual-master setup.  There are too many things that can go wrong, and they can be messy to fix -- AUTO_INCREMENTs, other duplicate keys, etc.

Hundreds of "Sleep" connections is virtually no impact on a server, so limiting to 40 is not useful.  10 or more active connections (non-Sleep) can be an issue.  In that case I would look at the queries.  Usually optimizing the queries is the best answer.

Note also, that every write (INSERT, UPDATE, etc) that is done on one Master must be done on all the other Masters and Slaves.  So, you can't really "spread" writes around.

If you have processes that do only reads (SELECTs), then they should go to Slaves and/or the backup Master, not the live, writable, Master.  This will help.

Be aware of the "critical write" problem.  Example:  A user posts a blog comment, then looks at his comments, but it is missing.  This can happen if the write went to one machine, but the read hit another, and replication is "behind".

(My comments apply to all versions, and all APIs, not just 5.1 and PHP's mysqli.)

I stay away from mysql_pconnect (and other connection pooling mechanisms).  Connection startup/teardown is very fast in MySQL.  Pooled connections may have issues with @variables, transaction modes, sql_modes, etc.


Assistance with MySQL statement So I have a database of information that is imported from a separate application. This table contains any number of 'rule' results, each rule result is of a certain type ('rule_1', 'rule_2', etc).  This table could contain multiple rule results with the same type.  Each rule result has a rule result value of P (passed), V (violation) or W (warning).

I'm trying to get a count of how many rules (not rule results) are of each rule result value (P, V or W) for a given code (1.2) with the following understanding...If any of the 'rule_1' records are a result of V then the count would be increased for V regardless of if 1 rule result value was V or if 100 are.

In simple terms I'm summarizing the V, W, P counts for the code 1.2, with V > W > P in terms of weighting.

+---------+--------+------+
| rule_id | result | code |
+---------+--------+------+
  RULE_1      V      1.2
  RULE_1      W      1.2
  RULE_1      P      1.2
  RULE_2      W      1.2
  RULE_2      P      1.2

Expected result would be

+---------+---------+---------+
| v_count | w_count | p_count |
+---------+---------+---------+
    1          1         0

Currently I'm using the following SQL statement, my question is, is this the way to do it or am I missing something simple, this statement seems very convaluted.

SELECT  DISTINCT
    (
        SELECT  COUNT(DISTINCT rule_id)
            from  ruleresult
            WHERE  code = "1.2"
              AND  result = "V"
    ) as v_count,
    (
        SELECT  COUNT(DISTINCT rule_id)
            from  ruleresult
            WHERE  code = "1.2"
              AND  result = "W"
              AND  rule_id NOT IN (
                SELECT  rule_id
                    FROM  ruleresult
                    WHERE  code = "1.2"
                      AND  result IN ("V"))
    ) as w_count,
    (
        SELECT  COUNT(DISTINCT rule_id)
            from  ada_ruleresult
            WHERE  code = "1.2"
              AND  result = "P"
              AND  rule_id NOT IN (
                SELECT  rule_id
                    FROM  ruleresult
                    WHERE  code = "1.2"
                      AND  result IN ("V", "W"))
    ) as p_count
    from  ruleresult;

Your basic query is correct, and I don't really see how you could make it simpler, given the definition of what you want to count.

You can simplify it slightly. You don't need the first DISTINCT; and you actually don't need a main FROM:

SELECT
   (SELECT COUNT(DISTINCT rule_id)
      FROM ruleresult
     WHERE code = '1.2' AND result = 'V') as v_count,
   (SELECT COUNT(DISTINCT rule_id)
      FROM ruleresult
     WHERE code = '1.2'
        AND result = 'W'
        AND rule_id NOT IN
        (SELECT rule_id
           FROM ruleresult
          WHERE code = '1.2'
            AND result IN ('V'))) as w_count,
    (SELECT COUNT(DISTINCT rule_id)
       FROM ruleresult
      WHERE code = '1.2'
         AND result = 'P'
         AND rule_id NOT IN
         (SELECT rule_id
            FROM ruleresult
           WHERE code = '1.2' AND result IN ('V', 'W'))) as p_count ;

| v_count | w_count | p_count |
|---------|---------|---------|
|       1 |       1 |       0 |

Check it at SQLFiddle.

You can also have a different approach:

You first PIVOT your rules
You then decide, for every rule, whether the result that counts is V, W or P
You sum how many of them you have.

All together, this would be:

SELECT
    sum(is_v) AS v_count,
    sum(is_w) AS w_count,
    sum(is_p) AS p_count
FROM
(
    -- Computing whether we should consider V, W or P
    SELECT
        rule_id,
        case when count_v0 > 0 then 1 else 0 end as is_v,
        case when count_w0 > 0 and count_v0 = 0 then 1 else 0 end as is_w,
        case when count_p0 > 0 and count_w0 = 0 and count_v0 = 0 then 1 else 0 end as is_p
    FROM
    (
        -- Pivoting V, W, P
        SELECT
            rule_id,
            count(case when result='V' then 1 end) AS count_v0,
            count(case when result='W' then 1 end) AS count_w0,
            count(case when result='P' then 1 end) AS count_p0
        FROM
            ruleresult
        WHERE
            code = '1.2'
        GROUP BY
            rule_id
    ) AS s0
) AS s1;

You can check this second option at RexTester. This second approach is (most probably) more efficient if your table is big, because it will only scan it once...


How use cascade delete for array field in PostgreSQL? How can i remove the value from array field?
I need to delete the user id from the university table's user_id array field if exists when i delete any users. so can i do this by using delete cascade  or any other method? please help me on this

Table:

CREATE TABLE IF NOT EXISTS users
    (_id SERIAL NOT NULL UNIQUE PRIMARY KEY,
    "userName" VARCHAR NOT NULL,
     active BOOLEAN NOT NULL);

CREATE TABLE IF NOT EXISTS university
    (_id SERIAL NOT NULL UNIQUE PRIMARY KEY,
    "universityName" VARCHAR NOT NULL,
    user_id integer[]);

User:

| _id | userName | active |
===========================
| 1   |  Abc     |   true |
| 2   |  vty     |   true |
| 7   |  hyu     |   true |
| 9   |  Agc     |   true |

University:

| _id | universityName    | user_id |
=====================================
| 1   |  HGV              |  {1,9}  |
| 2   |  CPC              |   {2}   |

1) if i delete user 2 then entire row should get deleted from university
2) if i delete user 1 then only value 1 should be removed from the user_id field from university table

First of all you need to create gin index on array field:

CREATE INDEX index_university_user_id ON university USING gin (user_id);

Then you need to add trigger to process deleting users:

CREATE FUNCTION trg_d_users() RETURNS TRIGGER AS $f$
BEGIN
    UPDATE university
       SET user_id = array_remove(user_id, OLD.id)
     WHERE user_id = OLD.id;
    RETURN OLD;
END
$f$ LANGUAGE plpgsql;

CREATE TRIGGER trg_d_users BEFORE DELETE ON users FOR EACH ROW EXECUTE PROCEDURE trg_d_users()

If you want to remove university without users, you can add into function after UPDATE:

DELETE FROM university WHERE user_id = '{}';

And also I would reccomend to rename user_id field to user_ids. It very useful to see that field is array without checking schema.


Looking for application which will provide to determine project database growth and cost per day or month I am using SQL Server 2008 for product development

Currently application is live with 20 users now if I want to check how my database is growing and how much it will grow in month or year projection to determine the cost of hardware. Is there any application or SQL Server built-in function?

Using SQL Server profiler I am able to monitor database queries and fine tuning.

You can use a third party tool Idera Diagnostic Manager to identify the growth and cost. There is a report inbuilt which forecast database and table growth depending upon how much historical data it has.
You can also set up this with the help of combination of TSQL + Powershell as described Database Space Capacity Planning by chad. It will help you in analyzing the database growth.
You can also see the Brent Ozar's answer on this as a solution as well

Estimate projected database growth


SQL Server - Logical Reads lowered, Execution time remained the same A view executed Select columns from view where col= "date range" (view contains calculation in it Col1+Col2*col3 AS Col4). It took 7 seconds for 500K rows to return, logical reads some 144k.

Added covering index and now after execution it says: logical reads = 8365 (some 17x times less) but the time of execution remained the same, some 7 seconds for 500k to return.

I would expect that reduction of logical reads would lower execution time too, or I am doing something wrong? Any advice?

Thanks.

@Craig, I tired what you told me and execution plan shows me that it was ignoring hints in both cases. It was index seek every time (index I added to help time execution). When I tried it without doing freeing cache it executed in almost the same time for all three of them and with no physical reads for the first execution. No need since all data was in the memory already.
Here is the output of what you told me to try:

Warning: Index hints supplied for view 'Name of the view' will be ignored.
Warning: Index hints supplied for view 'Name of the view' will be ignored.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

do not use any indexes

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
(100000 row(s) affected)
Table 'Name'. Scan count 2, logical reads 2137, physical reads 7,
read-ahead reads 6510, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 156 ms,  elapsed time = 939 ms.

let mssql choose what index to use

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
(100000 row(s) affected)
Table 'Name'. Scan count 2, logical reads 2137, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 94 ms,  elapsed time = 799 ms.

force index name_of_index

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
(100000 row(s) affected)
Table 'Name'. Scan count 2, logical reads 2137, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 769 ms.

some 7 seconds for 500k to return, and also a lot of time to render the grid you're likely displaying the results in.

You are waiting 7 seconds because that's how much it takes for SQL to push 500k rows to your client. Look at client statistics in your SSMS, see Database Engine Query Editor:

Include Client Statistics: Includes a Client Statistics window that contains statistics about the query and about the network packets, and the elapsed time of the query.
SQL Server Execution Times: CPU time = 47 ms, elapsed time = 769 ms.

Your actual query executes in 47ms. Elapsed time is much longer (still under 1 second) because of network waits. You can confirm this using wait stats analysis, read How to analyse SQL Server performance for details, including how to capture the query wait stats.
Ultimately, the problem is returning 500k rows to the client. There cannot be any reason for such operation, no human user can comprehend half a million rows. Process data on the back end.

Half a million rows is used since it is easier to notice speed changes with more than less data

Well, in this case you have sent yourself on a snipe hunt. There is no problem, other that one of your own doing in marshaling and rendering 500k rows. Is a completely bogus scenario, no app should retrieve 500k rows. And processing of 500k rows(eg. aggregates) should tests... the processing, including the aggregates.


Trying to replicate 2 or more Postgres instances to one standby host My setup has 2 Postgres hosts:

host SRV1 as primary with 2 instances and IP 192.168.1.241

Instance A listening on port 5432
Instance B listening on port 5433

host SRV2 as standby with instance C and IP 192.168.1.243 using default port.

I'd configured sucessfully replication from instace A to instance C using repmgr.

I'm using PostgreSQL 10 and rempgr 4.

How could I configure replication from new instances, e.g. B, to a new instance on host SRV2 (standby) using repmgr?

You can do that , just treat your instances as if they where separate nodes.  In your case you will end up with 3 instances on the standby server.

In repmgr you will need to create a cluster for A, B and C.  Let's call them CA, CB and CC, then you start by adding the nodes A and B and C as a primary node.  Your layout on SRV2 will have to allow those instances to live next to eachother.  Since you don't really mention what OS/flavor you are using it's hard to give specific advice there.

afaik you will most probably need to run several repmgr daemons if you use them in conjunction with different repmgr.conf config files which is probably the biggest hassle.

CORRECTIONS:

I seem to have misunderstood it a bit, you seem to have 2 clusters, with 4 instances (3 existing and 1 pending), Instances SRV1/A + SRV2/C make up cluster CA , then you just need to add instance D to cluster SRV1/B , making SRV1B + SRV2/D cluster number 2.  In any case, you need 2 repmgr.conf files, I've checked the docs and it never talks about multiple cluster support.  But nothing would stop you from running 2 repmgrd deamons with different config files.


Access denied for user 'root'@'localhost' (using password yes) I was trying to install MySQL 5.6.15 to my computer and got this error:

  unable to update security settings. access denied for user
  'root'@'localhost' (using password yes)

I guess current root password is wrong but I do not remember what it was. So how can I fix this problem?

If you installed MySQL 5.6 from the command line, the easiest way to find the temporary password is to go to the Linux command line do the following:

cd
cat `ls -la | grep mysql | grep secret | awk '{print $9}'` | awk '{print $18}'

This will quickly print the temporary password.

Try to login using that password

cd
OLDPASSFILE=`ls -la | grep mysql | grep secret | awk '{print $9}'`
PASSWORD=`cat ${OLDPASSFILE} | awk '{print $18}'
mysql -uroot -p${PASSWORD}

If the temporary password is no longer available, then you will have do the following:

NEWPASS="whateveriwant"
service mysql stop
service mysql start --skip-grant-tables
mysql -ANe"update mysql.user set password=PASSWORD('${NEWPASS}') where user='root'"
service mysql restart

Give it a Try !!!


Error during mysqldump on performance_schema.query_cache_info On a production server MariaDB 10.2.13, I actived the plugin query_cache_info.
Suddendly for 5 days, during the daily batch doing dumps (with mysqldump), I have this error :

mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `QUERY_CACHE_INFO`': Character set '#4294967295' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file (22)

I did

reset query cache

But the problem come back. I don't find the query which could be the problem.
I don't know how resolve this problem.

Do you have any idea ?
Thanks for your help.

Do not dump any tables of performance_schema, information_schema or mysql.  The first two are not 'real' tables, and they cannot be reloaded anyway.

95% of Production servers are not helped by, or even hurt by, turning on the Query cache.  Please elaborate on why you are even attempting to use the QC.