Migration from SSRS 2012 to SSRS 2017 Our company is moving from SSRS 2012 to SSRS 2017. I would like to know if anyone has done a similar migration. If yes, could you please guide me through the steps?

From what I read about the migration, it looks like its a very tiring process since I will have to download each report from 2012 server and upload it into 2017 server. or use PowerShell scripting to automate the same.

If anyone knows a better way to do it, I am all ears.

Thanks

Here's the process I use to migrate to a new report server. This is assuming you are not already using source control and you don't have a Visual Studio solution for your reports. If you do, skip to #4. If you have the database permissions, you can use the Microsoft guide Moving the Report Server Databases.
1. Backup deployed reports

Remote to the current report server.

%systemroot%/system32/mstsc.exe

Then save the following procedure as a .rss file and run it with the parameter parentFolder="" as a zero string to save the entire folder structure with all the reports.

Command Line:
rs -s http://localhost/reportserver -i D:\Scripts\Backup_Reports.rss -e Mgmt2010 -v backupFolder="D:\Scripts\BackupReports" -v parentFolder=""
Report Backup Procedure:
Public Sub Main()
    '--------------------------------------------------------------------------------------------------------------------
    ' Purpose:   Script to backup reports from a folder on ReportServer
    '            Save file as .rss extension and run using rs.exe from command line.
    ' Reference: http://bhushan.extreme-advice.com/back-up-of-ssrs-reports-using-rs-utility/
    '            https://docs.microsoft.com/en-us/sql/reporting-services/tools/rs-exe-utility-ssrs?view=sql-server-2017
    ' Example:   rs -s http://localhost/reportserver -i D:\Scripts\Backup_Reports.rss -e Mgmt2010 -v backupFolder="D:\Scripts\BackupReports" -v parentFolder="/IndividualReportFolderNameHere"
    '            rs -s http://localhost/reportserver -i D:\Scripts\Backup_Reports.rss -e Mgmt2010 -v backupFolder="D:\Scripts\BackupReports" -v parentFolder=""
    '--------------------------------------------------------------------------------------------------------------------
    Try
        rs.Credentials = System.Net.CredentialCache.DefaultCredentials
        Dim items As CatalogItem() = Nothing

        If String.IsNullOrEmpty(parentFolder) Then
            items = rs.ListChildren("/", True)
        Else
            items = rs.ListChildren(parentFolder, False)
        End If

        Console.WriteLine()
        Console.WriteLine("...Reports Back Up Started...")

        For Each item As CatalogItem In items
            If item.TypeName = "Report" Then
                Console.WriteLine(item.Path)
                Dim reportPath As String = item.Path
                parentFolder = Path.GetDirectoryName(item.Path) ' comment out this line to save the reports in one folder
                Dim reportDefinition As Byte() = rs.GetItemDefinition(item.Path)
                Dim rdlReport As New System.Xml.XmlDocument
                Dim Stream As New MemoryStream(reportDefinition)
                Dim backupPath As String = Path.Combine(backupFolder, Date.Now().ToString("yyyy.MM.dd") + "\" + parentFolder)

                If (Not System.IO.Directory.Exists(backupPath)) Then
                    System.IO.Directory.CreateDirectory(backupPath)
                End If

                rdlReport.Load(Stream)
                rdlReport.Save(Path.Combine(backupPath, item.Name + ".rdl"))

                Console.WriteLine(item.Name + ".rdl")
            End If
        Next

        Console.WriteLine("...Reports Back Up Completed...")
        Console.WriteLine()

    Catch e As Exception
        Console.WriteLine(e.Message)

    End Try

End Sub

2. Create a Visual Studio solution

Make a report project per report folder under one solution.
Add the reports from the backup .rdl files
Add Shared Data Sources
Add Shared Datasets

3. Use source control with Microsoft Azure DevOps Services

It's free for teams of 5 or less
You can pick a site name with your organization in the url. e.g. https://yourcompany.visualstudio.com
url: https://azure.microsoft.com/en-au/services/devops/

4. Setup deployment configuration
Setup the target deployment settings for each report project using the Configuration Manager.
Report Project Configuration

Configuration Manager

5. Build the solution

I only use this option for the initial build. After that, you can deploy individual reports by right clicking them and selecting Deploy.


COPY command only works as command line argument, not as part of script No rows are being copied when I try to copy a CSV from stdin. To be more specific, this works when writing the command on the cli, but not when read as part of a file. Why is reading SQL commands from a file different from executing them directly as an argument for -c?
cat extract.csv  | psql postgres -f copy-user.sql
Timing is on.
Line style is unicode.
Border style is 2.
Null display is "[NULL]".
Expanded display is used automatically.
COPY 0
Time: 41.685 ms

$ cat extract.csv  | psql postgres -c "$(cat copy-user.sql)"
Timing is on.
Line style is unicode.
Border style is 2.
Null display is "[NULL]".
Expanded display is used automatically.
COPY 10
Time: 31.685 ms

copy-user.sql
copy to_update from stdin (delimiter ';', format csv);

extract.csv
bfb92e29-1d2c-45c4-b9ab-357a3ac7ad13;test@test90239023783457843.com;x
aeccc3ea-cc1f-43ef-99ff-e389d5d63b22;tester@testerkjnaefgjnwerg.no;x
9cec13ae-c880-4371-9b1c-dd201f5cf233;bloblo@gmail.com;x
aeada2bc-a362-4f3e-80f2-06a717206802;veaat@gmail.com;x
fb85ddd8-7d17-4d41-8bc3-213b1e469506;navnnavnesen@foo.com;x
528e1f2e-1baa-483b-bc8c-85f993014696;kklk@hotmail.com;x
dbc8a9c1-56cf-4589-8b2c-cf1a2e0832ed;ghiiii@hotmail.com;x
fbf23553-baa2-410a-8f96-32b5c4deb0c7;lala@lala.no;x
e22ec0de-06f9-428a-aa3e-171c38f9a1f7;x2aa@gmail.com;x
8e8d0f73-8eb7-43b4-8019-b79042731b97;mail@mail.com;x

table definition for to_update
create table to_update(id text, email text, text char);

The answer was to be found in this pgsql-sql mailing list archive:

psql's interpretation of "stdin" when reading from a file
is that the COPY data is to come from that same file (look at the script
produced by pg_dump for an example).  So it reads to the end of the
file, which is right away --- otherwise you'd get some incorrect-data
errors.  The data sourced from the cat command is never noticed at all.

The thread also lists 4 solutions for achieving the same thing:
1) using COPY FROM STDIN
cat event.csv | psql -c "$(cat event.sql)"

2) using COPY FROM STDIN
psql -f  <(cat event.sql event.csv)

3) using \COPY FROM PSTDIN
cat event.csv | psql -f event.sql

4) using \COPY FROM STDIN
psql -f  <(cat event.sql event.csv <(echo "\."))


Oracle Express edition on Ubuntu - control file missing I have installed the Oracle Express edition on Ubuntu as mentioned here.

I am facing issues when I try to create a sample table.

Started oracle

$ sudo service oracle-xe start
Starting Oracle Database 11g Express Edition instance.

Started sqlplus

$ sqlplus / as sysdba

Executed the CREATE command

SQL> CREATE TABLE SAMPLE (ID NUMBER);
CREATE TABLE SAMPLE (ID NUMBER)
*** ERROR at line 1: ORA-01109: database not open**

After a series of research on web, I tried to shutdown and restart oracle:

Shutdown command

SQL> shutdown
ORA-01507: database not mounted
ORACLE instance shut down.

Started the oracle instance

SQL> startup

ORACLE instance started.
Total System Global Area  688959488 bytes Fixed Size
2229688 bytes Variable Size             411044424 bytes Database
Buffers          272629760 bytes Redo Buffers                3055616
bytes ORA-00205: error in identifying control file, check alert log
for more info

I realized that the control file is missing at /u01/app/oracle/oradata/XE. XE is the DB name.

So I tried to create the control file as follows:

SQL> CREATE CONTROlFILE SET DATABASE XE RESETLOGS;

Control file created.

Tried to create the sample table again

SQL> CREATE TABLE SAMPLE(ID NUMBER);
CREATE TABLE SAMPLE(ID NUMBER)

ERROR at line 1: ORA-01109: database not open

So I tried to issue the following command

SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS

ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent**
ORA-01110: data file 1: '/u01/app/oracle/product/11.2.0/xe/dbs/dbs1XE.dbf'

What should be done next? I am clueless as I am not a database guy.

Note:

Output of

$ lsnrctl services

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 04-JAN-2013 09:15:37

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))

Services Summary...

Service "PLSExtProc" has 1 instance(s).
 Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "XE" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully

It seems that something went wrong on the oracle-xe configure step.  Since this is a new install, I would follow the instructions for uninstalling and then the instructions for installing.


Converting Varchar to Int error I came across an issue today, and while I was able to fix it, I am unsure what caused the issue to happen.

I have a column that is Varchar(20).  It is used to store just year data (2010, 2011, etc.)  it is being converted to an Int data type, but when the command below is ran, an error code happens.

ALTER TABLE mytable ALTER COLUMN1 int NULL

The error displayed is: Conversion failed when converting the varchar value '2010.          ' to data type int.

There are 3 rows in the table with the value of 2010 in Column1, but NO rows with '2010.          ' (period and 10 spaces) as a value.  I ran

select len(Column1) from MyTable where Column1 like '%2010%'

I got 2 rows with a length of 4, and 1 with a length of 5.  After updating the row with 5 length to have 2010 as the value, the table could be altered.

I am curious where the period and spaces came from here.  Also curious how 1 character length increase results in a period and 10 spaces.  I am thinking something along the lines of a carriage return or line feed, but I can't find any data to back that up, its just a hunch.

LEN() in most SQL distributions do not include spaces.  You would want to use something like DATALENGTH() for that.
https://stackoverflow.com/questions/1151693/len-vs-datalength-in-sql-server-2005
As for where the '.' and spaces came from, we would need more information like how is that field being populated?  What app, directly via SQL, etc.
Also, it would be helpful to know what distribution you are using.  I assumed SQL Server.


How to get different metrics for same table with one query? I have a lot of queries to the same table. All queries looking like this

SELECT COUNT(*) FROM <sameTable> WHERE <whereClause> GROUP BY <groupBy>

Let's the amount of such queries is 40. So the table is iterates 40 times, I'm trying to reduce amount of iterations. I have tried different approaches and finished with this:

SELECT
        SUM(CASE WHEN ((p.statusId = 9)) THEN 1 ELSE 0 END) as metric1
        ,SUM(CASE WHEN ((p.statusId IN (10, 1088))) THEN 1 ELSE 0 END) as metric2
        ,SUM(CASE WHEN ((p.statusId = 11)) THEN 1 ELSE 0 END) as metric3
        ,SUM(CASE WHEN ((p.statusId = 20)) THEN 1 ELSE 0 END) as metric4
        ,(SELECT TOP 1 COUNT(DISTINCT p.CaseId) FROM vw_DashboardWorkbench p
LEFT JOIN vw_DashboardCaseStatusHistory history on p.CaseId = history.CaseId
LEFT JOIN vw_EzProviderUser provider on p.SecondaryPhysicianAdvisorId = provider.ProviderId
WHERE p.statusId = 12
GROUP BY history.AssignedByUser ORDER BY COUNT(DISTINCT p.CaseId) DESC) as metric5
FROM
    vw_DashboardWorkbench p
LEFT JOIN vw_DashboardCaseStatusHistory history on p.CaseId = history.CaseId
LEFT JOIN vw_EzProviderUser provider on p.SecondaryPhysicianAdvisorId = provider.ProviderId

There are two issues:

Summ works on all records, but some metrics need unique count of ids matching the expression.
The metric5 is subquery, I have to use it because I have failed to use aggregation function to get max value of cases in status 12 by user.

Another way of writing the query (without using the  vw_EzProviderUser provider table because it is unused in the example  would be:

;WITH CTE AS
(
SELECT COUNT(DWB.CaseId) as CountingCaseId, DWB.CaseId,DWB.statusId
FROM
dbo.DashboardWorkbench DWB
GROUP BY DWB.CaseId,DWB.statusId
),
CTE2 AS
(
SELECT  COUNT(DISTINCT DWB.CaseId) as CountingCaseIdDistinct,
        DWB.statusId
FROM dbo.DashboardWorkbench DWB
LEFT JOIN dbo.DashboardCaseStatusHistory DCSH on DWB.CaseId = DCSH.CaseId
GROUP BY DCSH.AssignedByUser,DWB.statusId
)
SELECT  SUM(CASE WHEN CTE.statusId = 9 THEN CountingCaseId ELSE 0 END) as metric1,
        SUM(CASE WHEN CTE.statusId IN (10, 1088) THEN CountingCaseId ELSE 0 END) as metric2,
        SUM(CASE WHEN CTE.statusId = 11 THEN CountingCaseId ELSE 0 END) as metric3,
        SUM(CASE WHEN CTE.statusId = 20 THEN CountingCaseId ELSE 0 END) as metric4,
        (SELECT MAX(CountingCaseIdDistinct) FROM CTE2
        WHERE CTE2.statusId = 12) as metric5,
        (SELECT MAX(CountingCaseIdDistinct) FROM CTE2
        WHERE CTE2.statusId = 10) as metric6
FROM CTE;

Performance

As of performance this could help depending on your data due to the early grouping on DWB.CaseId,DWB.statusId.

YMMV

There should be better solutions as to make it more performant such as columnstore indexes / better rewrites / indexing / ....

Temp tables

I would advise storing the result of CTE2 (or your subquery) in a temporary table if you are calling it multiple times.

This way, you are only reading from the resultset, not evaluating the same query each time.

(Metric5 and Metric6 in the example).

Testing

This was tested on SQL Server 2017

When adding some indexes to cover all options:

CREATE INDEX IX_statusId_CaseId
ON dbo.DashboardWorkbench(statusId,CaseId);
CREATE INDEX IX_CaseId_statusId
ON dbo.DashboardWorkbench(CaseId,statusId);

CREATE INDEX IX_AssignedByUser_CaseId
ON dbo.DashboardCaseStatusHistory(AssignedByUser,CaseId);
CREATE INDEX IX_CaseId_AssignedByUser
ON dbo.DashboardCaseStatusHistory(CaseId,AssignedByUser);

The only real 'benefit' compared to your plan is not having the compute scalar operator
or the multiple SUM(CASE WHEN StatusId = 9 THEN 1 ELSE 0), statusid = 10, ... in your query plan on all data.

In my rewrite, it is only doing that on the grouped count of DWB.CaseId & DWB.statusId.

Part of the plan of your query on my test YMMV

The compute scalar from the part above (second from the right)

Wheras there is earlier grouping in my plan + count(DWB.CaseId)

This part of the plan represents the T-SQL statements in CTE (1)

SELECT COUNT(DWB.CaseId) as CountingCaseId, DWB.CaseId,DWB.statusId
FROM
dbo.DashboardWorkbench DWB
GROUP BY DWB.CaseId,DWB.statusId

the computes are done afterwards, on the result of the above query:

The rest of the plan is virtually the same.

Extra info

There will definetely be better solutions, but somebody might be able to use the DB<>Fiddle below and test for something better.

DB<>Fiddle with test data, execution plans etc.

Temp tables can help if CTE2 or your subquery is evaluated multiple times, indexes matter. And as mentioned in the comments there are other options to explore, but we would need more information.


Things in the select are returned only if there are rows returned in the FROM statement.

First, let's think of it conceptually.

Query 1 is like:

  "Go find all of the Ferraris in your garage. For each Ferrari, give me
  the license plate number, or if it doesn't have a plate number, give
  me 'NO FERRARIS FOUND.'"

The query would come back with no rows - because there wasn't a Ferrari in the garage. (At least, there weren't any rows found in my own garage.)

Query 2 is different:

  "Go to the garage. IF you find a license plate on a Ferrari, give me
  that - otherwise, give me 'NO FERRARIS FOUND.'"

That's why the coalesce has to be outside of the search operation: you need it to happen even when there's no rows in the result set.

Now, let's look at your query.

I'm going to take the subquery out on its own, and I'm going to hard-code values for one of the rows where you want the COALESCE to work, but it can't:

SELECT TOP 1 COALESCE(t2.DateTest, 'NO FERRARIS FOUND')
     FROM         #TEST_COALESCE t2
     WHERE        t2.Id = 1
     AND          t2.DateTest > '2017-02-03 00:00:00.000'
     ORDER BY     t2.Id, t2.DateTest

In the WHERE clause, I've hard-coded Id = 1 and DateTest > '2017-02-03 00:00:00.000'. When this query runs, it returns no results:

That's why the COALESCE doesn't work: there were no rows in this result set, and no Ferraris in your garage. Master that concept, and you will have Ferraris in your...wait a minute...I've mastered that concept, and there are no Ferraris in my garage...


How to see whether db was hit or not when I run query in my app Sorry if the question is stupid, but I have some strange behaviour in my app, and I want to see what is queried in mysql db in real time. Is that possible? Or not in real time, some logs with time with seconds will work as well.
Thanks a lot beforehand.

Realtime -- I don't think so.

Log -- Turn on the "general log".  (The specifics depend on the version of MySQL you are running.)  That will write all queries to a file or table for later inspection.  Be sure to turn off the General log after you are finished; it could fill up disk rather fast.


Amazon rds MySql -> concurrent connections deadlocks I normally hang around in StackOverflow but I think this issue is better suited here.

I've built an application in C#.NET that uses MySql as a database.
My application inserts and updates data in the database in a using the Parallel API (so multithreaded). For reference, the queries that are being fired multithreaded would look like:

Update User set IsActive = 1 where UserID = 1 --each thread updates a different user id

This has always worked on servers that I manage and development machines.

We recently migrated the MySql database to Amazon RDS, however. Since then, if I run this query multithreaded (even with as low as two concurrent threads), it throws an exception:

Deadlock found when trying to get lock; try restarting transaction

Any experience with this issue? Are there some settings in Amazon RDS which are configured differently from a default MySql installation?

Thanks in advance!

The short answer is no: I can't think of a difference from MySQL defaults that would cause this.

The longer answer is that it is possible to reduce some locking with READ-COMMITTED as the isolation level + Row-based Replication. To which RDS does not support switching to Row-based Replication :(

Whether or not this reduction in locking will help you depends on your schema.  See my blog post here under "Write scalability of certain statements".


SSIS - SSAS: Missing data handling I have a solution in SSDT with a SSIS and a SSAS project, with missing values in both numeric and text columns.

Is it a good practice converting all the missing values of these columns to NULL during SSIS and then handle in SSAS?

Is there a way to ignore all NULL entries during every query in a SSAS OLAP Cube, besides defining calculated measures etc. for each possible query?

The way you handle null or missing data depends on where you have your missing data.

If you have missing data in fact tables in your measure columns you should probably leave them as null so they don't get considered in the NONEMPTYCROSSJOIN queries sent by most reporting applications.

If you have missing data in your foreign keys in your fact table (i.e. when your ETL process looks up the surrogate keys in your dimension table but the related row doesn't exist in your dimension) you need to decide based on your user requirements, but the general approach is to convert them to blank, 'unknown, invalid or whatever your requirements are.

If you have blank attribute values you should probably leave it as blank as to not confuse the users.

One thing to keep in mind specific to SSAS is that using error configurations in SSAS to let SSAS handle missing dimension keys has a huge impact on processing performance so you should probably solve that in your ETL process. The (unknown) dimension member is also very confusing for your end users as they don't know what it means when seen on a report and often forget to include it in filters (they have to select the unknown member on every dimension or they will be missing data).

As usual Kimball has a best practice on handling missing data in your dimensional model: Design Tip #128 Selecting Default Values for Nulls

So in your case I wouldn't convert them to NULL but to something meaningful when it comes to dimension keys in your fact table (just insert a row in your dimension with a surrogate key and a meaningful value such as -1, 'missing') and leave them as null or convert them to null in fact table measures so they play well with aggregates and NONEMPTY queries.


sorting groups of related rows by average values while keeping the groups together I'm using PostgreSQL 8.4, but would like a standard SQL solution if possible.
Consider the following table.

corrmodel=# SELECT * from data limit 1;
   id    | datasubgroup_id | datafile_id |           sequence           | index | seqindex | margstat | pvalue
---------+-----------------+-------------+------------------------------+-------+----------+----------+--------
 1033473 |               3 |          10 | GGTGACCCCAAGCTCAGGGCTGACCTGC | 19042 |          |  70.7634 |      0

I want to return the query that has the following properties.

All rows with the same datafile_id and index are grouped
together.
The groups are sorted first by average pvalue descending, then by average
margstat, where the averages are across each group.

The two tables I am doing this query on have 2.2 million and 3.1 million rows, so I'd like something reasonably efficient. Each group consists of 5 rows. This solution by @Lamak
works, but I had some trouble wrapping my head around it, and I think that a solution using window functions might be something I could actually understand. The following is close, but not correct, since the group is not preserved in this case.

SELECT datafile_id,
       index,
       pvalue,
       margstat,
       Avg(pvalue)
         OVER (
           partition BY datafile_id, index) AS avg_pval,
       Avg(margstat)
         OVER (
           partition BY datafile_id, index) AS avg_margstat
FROM   data
ORDER  BY avg_pval DESC,
          avg_margstat;

Here is the first 10 rows of the query result for one of my data sets. I'd like something like this, but correct.

datafile_id | index | pvalue | margstat | avg_pval | avg_margstat
-------------+-------+--------+----------+----------+--------------
          30 |   781 |      1 |  13.1568 |    0.998 |     12.52546
          30 |   781 |      1 |  12.3585 |    0.998 |     12.52546
          30 |   781 |      1 |  12.3495 |    0.998 |     12.52546
          30 |   781 |   0.99 |  11.9554 |    0.998 |     12.52546
          30 |   781 |      1 |  12.8071 |    0.998 |     12.52546
          23 |  1428 |   0.99 |  12.1711 |    0.998 |      12.6777
          23 |  1428 |      1 |  12.6451 |    0.998 |      12.6777
          23 |  1428 |      1 |  12.8814 |    0.998 |      12.6777
          23 |  1428 |      1 |  12.8969 |    0.998 |      12.6777
          23 |  1428 |      1 |   12.794 |    0.998 |      12.6777

As @ypercube pointed out in the comments, my query is quite close to the correct answer. The sort by avg_pval DESC, avg_margstat is actually close to the correct sort, only incorrect if the (avg_pval, margstat) tuple happens to have ties. So one can sort again, for a fixed (avg_pval, margstat), on datafile_id, index which will bring the groups back together. Finally, one can optionally sort within the groups, by pvalue DESC, margstat, Putting that all together, one gets

SELECT datafile_id,
       index,
       pvalue,
       margstat,
       Avg(pvalue)
         OVER (
           partition BY datafile_id, index) AS avg_pval,
       Avg(margstat)
         OVER (
           partition BY datafile_id, index) AS avg_margstat
FROM   data
ORDER  BY avg_pval DESC,
          avg_margstat,
          datafile_id,
          index,
          pvalue DESC,
          margstat;


MS SQL :Export data from query output to excel which has a column with huge data I have a problem where when the data being exported from query output to excel, data is extending to multiple rows in the excel sheet as the number of characters are more, Is there anyway to handle this.

I have tried exporting the data using the export option in SSMS and save as CSV file option as well along with simple copy paste option. All having the same output .Ie single column data per row in SQL will be extended to multiple rows in the excel , instead of single row in same column.
This could be due to the spaces in the SQL data as well,
Any leads will be helpfull

Welcome to hell. I've found that the most reliable way to get data from SQL Server to Excel is to use the querying capability from within Excel itself. The location of this function has moved around in recent releases, but right now, it's under Data, Get Data, From Database. This usually gets the best results for various data types, long data, etc.


mysql client gives no output when trying to connect to a remote DB I am trying to connect from a Raspbian shell to a remote MySQL database (Windows).

I freshly installed mysql-client and mysql-server (version 5.5.55) and now try following command:

mysql -v -h hostname -uroot -pmypass db_name

I get no response at all. Program does not exit or print anything just keeps hanging.

Connecting to a local DB works fine with mysql -uroot -pmypass.

I am quite confused why the client does not print anything even with the verbose option being set on.

Thanks!

Check your my.cnf settings.
Make sure the bind-address variable is commented or its value is 0.0.0.0

Make sure port 3306 (MySQL Default Port) is open (Both on the server and on the client).


Why transaction blocking simple insert in MySQL? transaction Query
    mysqli_begin_transaction($conn);
    $sql = "UPDATE foldertable SET trashed = 1 WHERE serverToken = (SELECT serverToken from servertoken where userToken = ? limit 1) and

    (folderId = ?
    or
    RootFolderPath LIKE
                CONCAT (
                    (SELECT RootFolderPath FROM foldertable WHERE serverToken = (SELECT serverToken from servertoken where userToken = ? ) AND folderid = ? limit 1)
                    ,'/'
                    , ?
                    ,'%'

                )
    )";
    $stmt =  mysqli_stmt_init($conn);
    mysqli_stmt_prepare($stmt, $sql);
    mysqli_stmt_bind_param($stmt, "sisii", $data["userToken"], $id, $data["userToken"], $id, $id);
    if (!mysqli_stmt_execute($stmt)) {
        echo mysqli_stmt_error($stmt);
        $serverError = true;
        return;
    }
    // echo "\n\n affected rows " . mysqli_affected_rows($conn);
    if (mysqli_affected_rows($conn) > 0) {
        sleep(15);
        // Insert into bin folder
        $sql = "INSERT INTO binfolder (serverToken , folderId) value ( (SELECT serverToken from servertoken where userToken = ? limit 1) , ?)";
        $stmt =  mysqli_stmt_init($conn);
        mysqli_stmt_prepare($stmt, $sql);
        mysqli_stmt_bind_param($stmt, "si", $data["userToken"], $id);
        if (!mysqli_stmt_execute($stmt)) {
            echo mysqli_stmt_error($stmt);
            $serverError = true;
            return;
        }
        if (mysqli_affected_rows($conn) > 0) {
            $success = true;
            mysqli_commit($conn);
        }

    }

Insert Query
INSERT INTO foldertable(serverToken) SELECT (123456)

Problem
if execute the insert query  after transaction started but the insert does not have to wait for transaction complete . Because the update query on transaction does lock row involved with insert query.
What I wanted
the Insert query will run immediately if it does not contain the same serverToken needed in Insert query.
Thank You in Advance.

For transaction blocking, the first line of defense is to speed up the query.  Though this does not completely eliminate the possibly of block/delay/deadlock, it will often make the situation more tolerable.
Re the LIMIT 1 -- Is there only 1?  If so, why not do a JOIN.  Or might there be more than one?  If so, which one?  That is, don't you need an ORDER BY?
servertoken needs INDEX(userToken, serverToken)
Would a single INSERT ... ON DUPLICATE KEY UPDATE ... suffice for your lengthy set of queries?  If so, it would probably be a lot faster.
I see an OR in the long query.  This is likely to prevent usage of an index.  (I don't understand the intent of the query well enough to suggest an alternative.)
"RootFolderPath LIKE CONCAT (..." -- This smells like something that should be redesigned.  Consider putting path parts in separate columns?  Or something else.
Is folderId the PRIMARY KEY of folderTable?
Plese provide SHOW CREATE TABLE (as text, not image) for each relevant table.


Join on nested obj attribute in JSONB column along and a varchar column I have table connections where both columns are varchar:
+---------+---------------+
| user_id | other_user_id |
+=========+===============+
| foo     | bar           |
+---------+---------------+
| baz     | asdf          |
+---------+---------------+

And table events where user_id references connections.user_id:
+---------+----------------+
| user_id | payload        |
+=========+================+
| foo     | {              |
|         |   otherUser: { |
|         |     id: "bar"  |
|         |   }            |
|         | }              |
+---------+----------------+
| baz     | {              |
|         |   otherUser: { |
|         |     id: "asdf" |
|         |   }            |
|         | }              |
+---------+----------------+

I would like to JOIN the tables on connections.user_id = events.user_id and connections.other_user_id = events.payload.otherUser->>id
Though, I'm not sure how to access the id field in the nested otherUser object in the JSONB column.
This is the most I've come up with:
SELECT * FROM events ev
JOIN connections con ON con.user_id = ev.user_id AND con.other_user_id = ev.payload->otherUser->>id

also tried con.other_user_id = (ev.payload)::jsonb->otherUser->>id
for these ^ two options I get "column otheruser doesn't exist".
Then also tried:
con.other_user_id = text(jsonb_extract_path(ev.payload::jsonb, 'otherUser', 'id'))
Which throws no error but also no rows are returned.
But I get error about the syntax.

The keys for the -> or ->> operators need to be supplied as strings:
SELECT *
FROM events ev
  JOIN connections con
    ON con.user_id = ev.user_id
   AND con.other_user_id = ev.payload -> 'otherUser' ->> 'id'

Or use the #>> operator and pass an array:
SELECT *
FROM events ev
  JOIN connections con
    ON con.user_id = ev.user_id
   AND con.other_user_id = ev.payload #>> '{otherUser,id}';

Which is basically the same as using jsonb_extract_path_text
SELECT *
FROM events ev
  JOIN connections con
    ON con.user_id = ev.user_id
   AND con.other_user_id = jsonb_extract_path_text(ev.payload, 'otherUser','id');

Online example


SQL Server service unresponsive I am unable to connect to the SQL Server instance. Logging into the box remotely I can see both CPU and MEMORY are maxed out. I have tried to restart the SQL Server service which has resulted it in "change pending" state which just seems to hang.
There are 2 SQL instances on this server, the other is operating fine. There are also Windows error logs for this instance which read

There was a memory allocation failure during connection establishment. Reduce nonessential memory load, or increase system memory. The connection has been closed.
SQL Server was unable to run a new system task, either because there is insufficient memory or the number of configured sessions exceeds the maximum allowed in the server. Verify that the server has adequate memory. Use sp_configure with option 'user connections' to check the maximum number of user connections allowed. Use sys.dm_exec_sessions to check the current number of sessions, including user processes.

Any ideas?

Need more info like SELECT @@VERSION, do you use CLR procs, have you set max memory on both the instances? Were you able to look at which spids were taking most resources and why?

To catch the intermittent cpu spikes, you may have to setup a trace and here is one such script.

http://www.sqlsoldier.com/wp/sqlserver/catchingtransientcpuspikesusingsqltrace


SQL Server 2008 and Management Studio - Intellisense is Patchy I'm working off an SQL 2008 instance that sits on our intranet. All is fine except that sometimes intellisense is there and working and sometimes it isn't. Its a pretty fast network so I don't imagine its a case that the RDBMS is reactively switching intellisense off because of latency issues etc.

Is there some other mechanism that can be used to ensure that intellisense always works?

It should revive when you refresh it's cache. In the menu is Edit - Intellisense - Refresh Local Cache (ctrl+shift+r). Or you can use another auto complete client like SQL Prompt or SQL Complete. First one has only paid version, while the second has also a free version.


How do I create logins for all database users in a database that was just restored for a different server? I was recently tasked with moving some databases to a new server. I created backups then restored the databases to the new server and it looks like the database users were retained, however there's no corresponding logins, prohibiting the users from accessing the new server. All of the users are domain users. Is there any way to iterate over the existing users in a database and create a login for each?

Google for sp_help_revlogin and find the version for your version of SQL (the SQL 2008 version works on SQL 2008 and up).  Install this sp on the old server.  Run sp_help_revlogin on the old server.  Take the output and put it in a query window on the new server, and run the query to create all the logins.


connect two sql server and import to CSV file (ETL) I have two servers, both has a sql server . Let's say srv1 has a sql-srv1 and srv2 with a sql-srv2 , i want to get some informations within sql-srv2 but making requests from sql-srv1.

my project is to create a script which could export data within sql-srv2 , in creating a bat files and to execute the script every day with task scheduler but first of all i have to connect the 2 sql-server and i don't know guys how to connect it, i'm a beginner.
I just want to create a script like

  sqlcmd -S . -d demo -E -s";" -w 700 -Q "select * from etudiant" > C:\troisiemetest%date%.csv

which can run everyday on the 2 sql servers linked.
Thank you

Welcome to the group....You can also use linked server, below link could help you

https://stackoverflow.com/questions/1144051/selecting-data-from-two-different-servers-in-sql-server

As per the comments, adding the details from the thread mentioned

Querying across 2 different databases is a distributed query. Here is a list of some techniques plus the pros and cons:

Linked servers: Provide access to a wider variety of data sources than SQL Server replication provides

Linked servers: Connect with data sources that replication does not support or which require ad hoc access

Linked servers: Perform better than OPENDATASOURCE or OPENROWSET

OPENDATASOURCE and OPENROWSET functions: Convenient for retrieving data from data sources on an ad hoc basis. OPENROWSET has BULK facilities as well that may/may not require a format file which might be fiddly

OPENQUERY: Doesn't support variables
All are T-SQL solutions. Relatively easy to implement and set up
All are dependent on connection between source and destionation which might affect performance and scalability

Follow these steps to create a Linked Server:
1. Server Objects -> Linked Servers -> New Linked Server
2. Provide Remote Server Name.
3. Select Remote Server Type (SQL Server or Other).
4. Select Security -> Be made using this security context and provide login and password of remote server.


Should I put common fields in each specific tables or one common table then join when data selecting? Assuming there are a few categories: blog, video, gallery. Each of them contains post data like id, title, content, author_id, created_at, but different category have few more specific field: video will have links, gallery will have imgURL.

Besides, user can leave comment. Comment have identical fields with Content too: id, title, content, author_id, created_at, and original_post_id.

My question is: blog, video, gallery, comment have common field, should I create a table called CommonContent: id, title, content, author_id, created_at?

CommonContent and Blog/ Video/ Comment are in 1:1 relationship. So should I still create a CommonContent table? My concern is if all common fields put in one table, it creates bottleneck when selecting data.

I think the benefit of having a common table for blog, video, gallery, comment  is that you will have unique id for each one of these, but that is really not needed.

And It seems that all these share some common attributes, so we need to have common table to save those, but from application point of view they will be different and needs to be save in different table.

Saving all of them in common table will be problem in maintainance, and also while fetching those entry to show in UI, So it would be better to have different table for all these four.

Then there comes thing is how to store comments, Well comments can be on video or blog or gallery, and with time it can be very large in numbers so you can choose one comment table for each category.


mysqldump drop create use case sensitivity Dumped sql from MySQL is showing inconsistencies in the name of the database.

Using the following bog standard method to dump a database:

mysqldump --add-drop-database -u someuser --databases Geo-ASty

I get the following output:

...
/*!40000 DROP DATABASE IF EXISTS `Geo-ASty`*/;

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `geo-asty` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;

USE `Geo-ASty`;
...

Note the case for the database which changes for the CREATE DATABASE statement. I kid you not.

I wouldn't care if it worked but importing this sql back in causes errors:

ERROR 1049 (42000) at line 26: Unknown database 'Geo-ASty'

I'm using MySQL 5.5 and exporting from a Mac.

Set lower_case_table_names=1 on the Linux server before importing, this will convert all databases to lower case and new queries will use lower case, too. You can see it here.

Your problem arises from the fact that the default value on Mac is 2 (names are not case sensitive when queried, but they are when stored) but 0 on Unix/Linux (names are always case sensitive). This is because entities like tables and databases names depend on ON files and directories, which have different properties on different systems.

To avoid further problems I would recommend you following a standard (usually, only using lower case identifiers for tables, databases and aliases -column names are always case insensitive) and stick to it.


Restore database to a instance in network We are creating a application which requires sql database as backend to store data. So when user install my application he chooses a sql instance and its details. There we plan to restore the database which is required for storing the users data etc.

So now if the user selects a sql instance of a another pc in the network then, how i should restore the database? Giving network path in backup database t-sql command is not a good idea. So in this cases how the database is restored to a network path? What is the best practice for this.

So are there multiple backups that may be selected?  I'm having a hard time following that portion of the question.

If there are a large number of backups that you don't want to port with the application installation, then you would be storing them on a network share accessible by all client machines that would be installing your application.  If there was only one backup, and it was reasonably sized, or multiple backups that are reasonably sized, why wouldn't you just have those ship with the installation?

Provided that's not an option, what you could do is create logic to first copy the backup file from the share to a local directory.  Think of it like this:

Copy backup file from "\yourShareName\Dir1\backup_file.bak" to "C:\Some\Local\Dir\backup_file.bak"
Restore your database from the local copy of the backup file


Is it dangerous to have duplicated entries in a table? My table have many duplicated entries, is it dangerous if I don't fix this? My table is structured like this:

Music_id | Music_title      | Music_author | Music_link
1          Look at me now     Chris Brown
2          Look at me now     Jay-z

This is just an example, but that is the table I have. Look at the Music title. There's duplicate entries already. Sometimes the music_title will reach 10 duplicates

So I plan to make a look up Music table

Music
id | music_title
1    Look at me now

My table
id | music | music_author | Music_link
1     1      chris brown
2     1      jay-z

But my concern is, the 1 in the music field is also duplicated. Which is better anyway? I'm confused.

In this case, I would keep the single table and allow repeated song titles.

Lookup tables are used for finite sets (artist, status, city etc). Moving song titles to a lookup table will result in a lookup table nearly as large as the main table. If anything, artist should be a separate lookup table because this will be common attributes for several song entities.

A table will generally store an object or entity, in this case a song. The common attributes for a song can be moved to a lookup table, but song title isn't one of these common attributes. The same song title is usually a coincidence: you'd probably be better with "CoverOf" and "ReReleaseOf" columns to deal with these cases.

The fact of storing the same text in several rows isn't breaking any rules here because it isn't repeated text in the lookup sense

Also see: Proper Use of Lookup Tables


Replication error on SQL Server 2008 I'm doing merge replication between two SQL Server 2008 machine. But, when I attempt to do any modification on subscriber system, I get the following error.

  The merge process could not enumerate changes at the 'Subscriber'.
  When troubleshooting, restart the synchronization with verbose
  history logging and specify an output file to which to write.
  (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200999)

Regards,

You'll want to log onto the distributor and stop the merge agent.  Then open a command line window and navigate to the folder which has the replication binaries in it (c:\Program Files\Microsoft SQL Server\100\COM\ by default).  Run replmerg.exe and paste in all the values from step 2 of the job which is the replication agent.  Then change the -OutputVerboseLevel from 0 to 1 (the parameter may not be there if it isn't just add it was an value of 1).

This should give you the actual error message so you can see what's going on.


MySQL warm procedure How do you usually warm up your database ?

I run a similar queries to this for every table in DB:

SELECT * FROM ip_log ORDER BY ID;
SELECT ip, member_id FROM ip_log ORDER BY ip, member_id;

Is that the best way, or there is something cooler ?

If you would like to warm your MyISAM Key Buffer you could run this:
SELECT DISTINCT CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb,' ORDER
BY ',ndxcollist,';') SelectQueryToLoadCache
FROM (SELECT engine,table_schema db,table_name tb,index_name,
GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
FROM (SELECT
B.engine,A.table_schema,A.table_name,A.index_name,A.column_name,A.seq_in_index
FROM information_schema.statistics A INNER JOIN
(SELECT engine,table_schema,table_name
FROM information_schema.tables
WHERE engine='MyISAM' AND table_schema
NOT IN ('information_schema','mysql')) B
USING (table_schema,table_name)
WHERE A.index_type <> 'FULLTEXT'
ORDER BY table_schema,table_name,index_name,seq_in_index) A
GROUP BY table_schema,table_name,index_name) AA
ORDER BY db,tb
;

Here is the output:
+----------------------------------------------------------------------+
| SelectQueryToLoadCache                                               |
+----------------------------------------------------------------------+
| SELECT start_time FROM annarbor.sq_20110722 ORDER     BY start_time; |
| SELECT id FROM junk.category ORDER     BY id;                        |
| SELECT id FROM junk.mytabletolimit ORDER     BY id;                  |
| SELECT parent_id FROM junk.observations ORDER     BY parent_id;      |
| SELECT id FROM junk.observations ORDER     BY id;                    |
| SELECT parent_id FROM junk.pctable ORDER     BY parent_id;           |
| SELECT id FROM junk.pctable ORDER     BY id;                         |
| SELECT id FROM junk.products ORDER     BY id;                        |
| SELECT id FROM junk.subcategory ORDER     BY id;                     |
| SELECT id FROM test.acties ORDER     BY id;                          |
| SELECT id FROM test.deletekeys ORDER     BY id;                      |
| SELECT email FROM test.emailtable ORDER     BY email;                |
| SELECT id FROM test.emailtable ORDER     BY id;                      |
| SELECT id FROM test.ft_test ORDER     BY id;                         |
| SELECT id_key FROM test.id_key_table ORDER     BY id_key;            |
| SELECT id_key FROM test.id_key_table_keys ORDER     BY id_key;       |
| SELECT id FROM test.mytabletodeletefrom ORDER     BY id;             |
| SELECT NGRAM_ID FROM test.ngram_key ORDER     BY NGRAM_ID;           |
| SELECT NGRAM FROM test.ngram_key ORDER     BY NGRAM;                 |
| SELECT NGRAM_ID FROM test.ngram_rec ORDER     BY NGRAM_ID;           |
| SELECT num FROM test.notforeverdata_matches ORDER     BY num;        |
| SELECT id FROM test.pamela ORDER     BY id;                          |
| SELECT id FROM test.rolando ORDER     BY id;                         |
| SELECT num FROM test.rolando2 ORDER     BY num;                      |
| SELECT CourseName FROM test.tab ORDER     BY CourseName;             |
| SELECT CourseName FROM test.tab_to_zap ORDER     BY CourseName;      |
| SELECT academy FROM test.under99color ORDER     BY academy;          |
| SELECT id FROM test.under99color ORDER     BY id;                    |
| SELECT id_key FROM test.weekly_batch ORDER     BY id_key;            |
| SELECT id FROM test.worktable ORDER     BY id;                       |
+----------------------------------------------------------------------+
30 rows in set (0.81 sec)

mysql>

This displays every possible query you could run against MyISAM tables selecting index column fields only. Running those queries will populate the MyISAM Key Buffer.
Here is a similar query to make queries that preload the InnoDB Buffer Pool
SELECT DISTINCT
    CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb,
    ' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
    FROM
    (
        SELECT
            engine,table_schema db,table_name tb,
            index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
        FROM
        (
            SELECT
                B.engine,A.table_schema,A.table_name,
                A.index_name,A.column_name,A.seq_in_index
            FROM
                information_schema.statistics A INNER JOIN
                (
                    SELECT engine,table_schema,table_name
                    FROM information_schema.tables WHERE
                    engine='InnoDB'
                ) B USING (table_schema,table_name)
            WHERE B.table_schema NOT IN ('information_schema','mysql')
            ORDER BY table_schema,table_name,index_name,seq_in_index
        ) A
        GROUP BY table_schema,table_name,index_name
    ) AA
ORDER BY db,tb
;

Here is a similar query to make queries that preload the InnoDB Buffer Pool and the MyISAM Key Buffer (I posted this back in November 2011)
SELECT DISTINCT
    CONCAT('SELECT ',ndxcollist,' FROM ',
    db,'.',tb,' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
FROM (
    SELECT
        engine,table_schema db,table_name tb,index_name,
        GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
    FROM (
        SELECT
            B.engine,A.table_schema,A.table_name,
            A.index_name,A.column_name,A.seq_in_index
        FROM
            information_schema.statistics A INNER JOIN
            (
                SELECT engine,table_schema,table_name
                FROM information_schema.tables
                WHERE engine IN ('InnoDB','MyISAM')
            ) B USING (table_schema,table_name)
        WHERE
            B.table_schema NOT IN ('information_schema','mysql')
            AND A.index_type <> 'FULLTEXT'
        ORDER BY
            table_schema,table_name,index_name,seq_in_index
        ) A
    GROUP BY
        table_schema,table_name,index_name
) AA
ORDER BY
    engine DESC,db,tb
;

If you see the same query appear more than once in a display, this indicates the presence of redundant indexes.
Give it a Try !!!
UPDATE 2012-02-04 21:53 EDT
While one can create caches dedicated to MyISAM via as follows:
SET GLOBAL authentication_cache.key_buffer_size = 1024 * 1024 * 8;
CACHE INDEX mydb.users INTO authentication_cache;
LOAD INDEX INTO CACHE mydb.users;

you must still decide to load all indexes or specific ones. The script I provided simply shows you the list of all possible indexes you can load. You are not obligated to load all indexes, just the ones you know are used the most. If you have the hardware and budget, you load as much as you like into the dedicated MyISAM caches.
Here is something else to consider: All the queries mentioned are actually accessing covering indexes. Running those queries will only access the MyISAM pages of the .MYI file and only the index pages of an InnoDB tables. Table data is never retrieved. In light of this, all or selected queries populated index pages regardless of storage engine.
More Links on the Principles of Covering Indexes

http://www.mysqlperformanceblog.com/2006/11/23/covering-index-and-prefix-indexes/
http://ronaldbradford.com/blog/tag/covering-index/
http://shallop.com/2011/04/covering-indexes-in-mysql/
http://www.mysqlconf.com/mysql2009/public/schedule/detail/6796

UPDATE 2012-02-04 22:05 EDT
Quick note about InnoDB vs MyISAM. You can cache data pages for InnoDB. MyISAM no.


MySQL 5.6 DESTDIR not work propely I'm try compiling MySQL 5.6 from source in Ubuntu 12.04 x64.

But when I execute make install DESTDIR="/etc/mysql", not work was expected.

MySQL is installed in /etc/mysql/usr/local/mysql.

What's wrong?

Sorry for my english

DESTDIR is a variable inserted at the beginning of each target file

If you wanted to install MySQL in the /usr/local/mysql, you could use the following command sequence

cd /name/of/source/directory
make clean
./configure --prefix=/usr/local/mysql
make && make install

MySQL would thus be installed in the /usr/local/mysql directory.

The ./configure allows you to control and define how your program will be installed and what options will be included or excluded.

MySQL Source Configuration Options


Schema design for storing item combination with order What I need:

Three types of items, and user can add/delete items.
Use will select some items from those three packs, and arrange an order according to his/her preference.
The number of records is not big (generally below 20).
The tools I'm using are python, SQLAlchemy, and MySQL. (This might not be so important, anyway)

And this is my initial design idea:

One table is enough for those three types of items as they share the same fields and only need to use a 'item_type' field to differentiate. Let's call the table 'Item'.
Use another table called 'Preference' to save user's preference/priority on selected item. Cascade delete is set, so when user delete a row on Item table, it will also disappear from Preference table.

Example:

Use has added 5 items, and selected 4 of them into 'Preference'

[Item]
id  item_name  item_type  preference_id (foreign key reference on Preference.id)
1       xxx         A            1
2       ooo         B            2
3       aaa         A           NULL
4       bbb         C            3
5       ccc         C            4

[Preference]
id   priority
1      1
2      2
3      4
4      3

Now the sorting is ['xxx','ooo','ccc','bbb'], say the user delete 'ooo' from Item table, the status would become:

[Item]
id  item_name  item_type  preference_id
1       xxx         A            1
3       aaa         A           NULL
4       bbb         C            3
5       ccc         C            4

[Preference]
id   priority
1      1
3      4
4      3

and the sorting become ['xxx','ccc','bbb'] now.

It looks simple when delete or add new items to the Preference. However, if I need to put an item in the middle of sorting, I might need to re-update the priority field for many rows. For example, make 'aaa' become the top priority would require update the priority column of all other existing 3 items in Preference table, the design doesn't loo very elegant for this scenario.

I would be appreciated if anyone has better ideas, or notice some other flaws on this design. Thanks!

The design doesn't seem awful, but I think you could solve your update issue by changing how you are assigning a priority number. Instead of using 1, 2, 3, 4, ..., you could use a higher range between the values, such as 10, 20, 30, 40, .... You might find you need an even larger set like 50, 100, 150, 200, ....

By increasing the range, you have some flexibility when a user changes the ordering of a given item. Instead of having to update all of the records, you could now use an available number between the existing two and save yourself the headache. For example:

AAA -> 10
BBB -> 20
CCC -> 30

We want to move CCC to be the second item (in between AAA and BBB), so we change 30 to 15. So now:

AAA -> 10
CCC -> 15
BBB -> 20

Of course, you'll still have to re-assign all the priorities if the user changes too many of the items, as you'll eventually run out of empty numbers. When you're doing your selection, just remember to order by the priority.


Database architecture I am in the early stages of designing a database, and I have little (none) database design/planning experience. The database is part of a job tracking engine..

As jobs are created, they are assigned a job number, description, client, and a number of keywords that are selected from a list. My questions relates to choosing the most efficient (runtime speed) way of linking the selected keywords to the jobs.

I see a number of way to do this, some less conventional and maybe more dangerous than others...

Option A - limit the maximum number of keywords that can be assigned jobs to value 'N' (likely 5) and have the following table columns..

  [id] [job number] [description] [client] [keyword 1] .. [keyword N]
    1     123           zyx          kk        word           test
    2     183           tyx          ff        test           -
    3     214           xyx          tt        bleh           -

Option B - Have a keyword table with a column for every keyword. The keywords in the entire database is administered manually, but there could be any arbitrary number of keywords, lets say X (likely in excess of 200). As job are created, a new row is added to the table and the job number, or id (link from another table) entered in each of the columns where the keyword has been selected. Eg

[word]     [test]       [bleh]   ....... [keyword X-1]     [keyword X]

 1            -            1                      1             -
 -            2            -                      -             -
 -            -            3                      3             3

Option C - Similar to 'Option B', except keywords are given their own table. But that would just mean there are X number of tables in my database...

At this stage option A is the front runner, but I don't particularly like the hard limit on the number of keywords that can be assigned to a job. Can anyone suggest an alternative design, so that I don't have a ridiculous number of rows/tables,  and so I don't have to limit the number of keywords assigned to a job.

Thanks

A is not good

a table for the keywords

ID  PK
Keyword

then have a join table

jobID       PK FK
keywordID   PK FK

with FK to the two tables


Behaviour of Bulk Insert in pgsql or any Database I'm using PostgreSQL 9.2.4.

I'm working with multiple tables with large number of data.

I have two tables Table_One which  has one of the field with varchar(250) and Table_Two with one of the field varchar(200).

Lets say, Table_One has 20 records in which 2 records has field with 250 characters.

I'm doing a bulk insert by some query like below

insert into Table_Two(id, INSTANCEwithVarchar, MIN, MAX)(SELECT id, INSTANCEwithVarchar, MIN, MAX from Table_One)

I will get an exception while inserting the record of Table_One which has varchar 250.

My question is,

Will Table_Two has 18 records inserted?

Or bulk insert into Table_Two will fail completely even if one record among the 20 gets violate the constraints?

If not, say if Table_Two has 18 records added, then in what order the failure will happen? if the first record of Table_One has varchar 250, then will the whole process fail or pgsql itself ignores failures and add remaining records successfully?

(Using PostgreSQL 9.2)

Your statement runs in a transaction. If there is not already a transaction open, a transaction is created just for this statement and automatically committed at the end if the statement succeeds.

If the statement encounters an error, the transaction is rolled back automatically, so it has no effect.

Either the whole statement takes effect or none of it does.

(Note that you could've just tried it to determine this).


See if an JSON array in MySQL contains an object whose key holds a specific date i'm trying to find out if there is a row which contains a specific date inside a JSON array
Let's say my data looks like this:
Table applications:
id | application_id | data

# Rows
1 | 1 | [{"data" : ["some", "data#1"], "date": "2016-04-21"}, {"data" : ["other", "data#1"], "date" : "2016-04-22"}]
2 | 2 | [{"data" : ["some", "data#2"], "date": "2016-04-21"}, {"data" : ["other", "data#2"], "date" : "2016-04-26"}]
3 | 1 | [{"data" : ["some", "data#3"], "date": "2016-04-22"}, {"data" : ["other", "data#3"], "date" : "2016-04-26"}]
4 | 3 | [{"data" : ["some", "data#4"], "date": "2016-04-26"}]

How can I find all the applications whose data contains the date '2016-04-26'?
So basically I can do this:
select id, json_extract(`data`, "$[*].date") from applications

Which returns:
1 | ["2016-04-21", "2016-04-22"]
2 | ["2016-04-21", "2016-04-26"]
3 | ["2016-04-22", "2016-04-26"]
4 | ["2016-04-26"]

But if try to use json_extract in the WHERE clause I can only use it if I explicitly tell the array's key in the json_extract's path argument, like so:
select * from applications where json_extract(`data`, "$[0].date") = "2016-04-26"

which correctly returns the row with id 4.
But if I try to use a wildcard in the path then it no longer works:
select * from applications where json_extract(`data`, "$[*].date") = "2016-04-26"

this should return the rows 2, 3, 4.
I tried many other options/variations but I can't seem to find a way to structure the query correctly.
Is something like this even possible with the current implementation of MySQL JSON?

One solution as provided by Morgan Tucker - @morgo is to use json_contains like so:

select * from applications where json_contains(`data`, '{"date" : "2016-04-26"}')

For now the answer is OK, but i believe it can have some performance issues and feels a bit hackish to me (see the next query) - but I will deal with those when i get there :)

If I would need to query on a date range (from 2016-04-24 to 2016-04-26) I would need to add an invididual json_contains for each day in the time span like so:

select * from applications where json_contains(`data`, '{"date" : "2016-04-26"}') or json_contains(`data`, '{"date" : "2016-04-25"}') or json_contains(`data`, '{"date" : "2016-04-24"}')

And this would return invalid data if I would have an date key nested somewhere else

So if you have an different solution, I would like to know


PostgreSQL "freeze"/"unfreeze" command equivalents In Derby (an embedded database written in Java which is mostly used for testing or prototyping) there are "freeze" and "unfreeze" commands which can be used during an online backup. "Freeze" simply causes all database accesses to block until "unfreeze" is called. This is useful for backing up using an external program, which you might do if the external program is much faster than using Derby's internal backup solution. For my use case, I can take a snapshot almost instantaneously using some built-in filesystem utilities, so it is a constant-time operation (not O(length of DB files)).

I'm migrating an application which has outgrown Derby to PostgreSQL, and I was wondering if there's anything comparable there which I can use to quiesce all connections. Also, I'd prefer to know what my serialization point is from inside my application so that I don't get caught in some awkward state, so being able to pause/resume all other accesses is really nice-to-have for me.

Since PostgreSQL has a transaction log, I could just take a snapshot without "freeze"ing, but the snapshot would need to be run through PostgreSQL's recovery mechanism before I can use it because otherwise what's stored on disk would be the same as if I pulled the plug on a normal filesystem. This solution is not ideal.

EDIT I learned that pg_start_backup() is close, but it doesn't cause incoming transactions to block until a matching call to pg_stop_backup(), forcing me to do a point-in-time-recovery back to the transaction id pg_start_backup() returns from a filesystem snapshot. It would be nice not to have to actually shutdown PostgreSQL to get this (perhaps there is a pseudo-shutdown command that keeps connections open?).

There's no exact equivalent to what you want. Options include:

Atomic file-system snapshot

If you're using an atomic file system snapshot you don't need to freeze the database. It might make recovery a little faster if you force a CHECKPOINT first, but that's about it. When you take a filesystem snapshot and copy it, then start the copy, to PostgreSQL it's as if it crashed and is starting back up. That's perfectly fine, it's crash safe and designed to work that way.

This approach is only valid if the snapshot is atomic - it's all at the same virtual instant. You can't get an atomic snapshot across multiple file systems (on Linux, at least), so if your DB is split across multiple tablespaces or WAL is on a separate disk to the heap you cannot use this approach.

pg_start_backup / pg_stop_backup

If you can't do an atomic snapshot you can enable WAL archiving, run pg_start_backup, copy the DB, run pg_stop_backup and capture the last WAL archives generated.

It's a bit more complicated, but it gives you a consistent backup without stopping writes and without needing file-system level atomic snapshots.

pg_basebackup

An alternative to using pg_start_backup and pg_stop_backup is to use pg_basebackup to do a streaming copy of the DB over the PostgreSQL replication protocol with --xlog-method=stream. This requires only a PostgreSQL replication connection, doesn't require the DB to be stopped, and is pretty seamless.

--xlog-method=stream was only added in pretty recent versions, and pg_basebackup its self is fairly new.

pg_dump

I didn't initially mention it because you were looking for external tools, but there's always pg_dump, which gets a SERIALIZABLE snapshot of the database and dumps it. The DB keeps running like normal (it can still accept writes) and the dump is entirely internally consistent from the time you started the dump.

Write quiescence

Stopping all incoming transactions won't stop PostgreSQL writing. It'll still have VACUUM work to do with autovacuum, checkpoints to perform, stats to write, etc.

There's no feature in Pg to stop all writes at this point. It might be nice to add, but I'm not aware of anyone working on it.

Some file systems, like XFS, support write freezing at the file system level; this causes all writes to block until the freeze is released. It's safe to freeze all file systems then copy all file systems.


Is UDPLOCK using Row-locks? I have a UPDATE statement that is using a UPDLOCK inside a DTCTransaction to avoid deadlocks.

Now I was wondering. is it locking on a Row-level or on a Table-level?

Now I was wondering if it is locking on a Row-level or on a Table-level?

From BOL

UPDLOCK
Specifies that update locks are to be taken and held until the transaction completes. UPDLOCK takes update locks for read operations only at the row-level or page-level. If UPDLOCK is combined with TABLOCK, or a table-level lock is taken for some other reason, an exclusive (X) lock will be taken instead.
When UPDLOCK is specified, the READCOMMITTED and READCOMMITTEDLOCK isolation level hints are ignored. For example, if the isolation level of the session is set to SERIALIZABLE and a query specifies (UPDLOCK, READCOMMITTED), the READCOMMITTED hint is ignored and the transaction is run using the SERIALIZABLE isolation level.

Also have a look at Designing Transactions and Optimizing Locking

WITH (UPDLOCK) This hint is used in DML statements to cause SQL Server to acquire update locks instead of shared locks. The update locks acquired will be held for the remainder of the transaction. Update locks are compatible with shared locks but are not compatible with other update locks. Therefore, SELECT statements that acquire update locks will be blocked, while SELECT statements that only intend to read the data, and not change it, are allowed. WITH (UPDLOCK) is usually specified if data is to be read first and updated later. Because of this, it can be referred to as a “SELECT WITH INTENT TO UPDATE”.


How do I identify functional dependencies and normalize a table based on the attributes? I'm having trouble identifying the combination of attributes which make up Functional Dependencies(FDs) of a particular table. While I understand how Normalization process works when given the FDs, I'm unable to translate and identify FDs for a given table.
I've been tasked to create a database of a company which tracks all expenses of sales/packages etc. Naturally that means storing Customer information for the company to identify all consumers.
CREATE TABLE Customers (
    id        integer primary key,
    address   text not null,
    name      text not null,
    email     text unique not null,
    phone     text unique not null,
    unique(name, address)
);

I've chosen to represent the info for the customer table as such, given that each phone number and email is uniquely tied to the customer. The unique constraint allows for multiple people from the same household address to be present in the table.
I want to see if I can normalize this table further, however I am unable to identify the attributes to determine the FDs needed.

A set of attributes Y is functionally dependent from another set X if and only if Y is uniquely determined by X, that is you cannot have two different values of Y associated to the same value of X. So, for instance, assuming that a phone number is tied always to only one person, and that a person has always only one address, you can say that the functional dependency phone -> address holds, since, given a certain phone number, only the address of the person with that number can appear together with it.
So a functional dependency specifies in a formal way an important fact of the reality that is modeled in your database.
A problem with functional dependencies is that you can have a lot of them, even for small relations. A clever way of solving this problem is to define only a small set of dependencies, sufficient to “capture” all the essential information about a certain reality, since we can derive all the other dependencies from this set in a mechanical way (and there are programs that can do this, for instance by applying the well-known “Armstrong’s axioms”). However, sometimes it is not so easy to define such “essential” set of functional dependencies and we must look carefully at the specification of the problem that we are modeling.
Usually we start from the so-called “candidate keys”, that is the attributes or the sets of attributes that are “naturally” unique inside a relation. Since they are unique, we know that they determine all the other attributes. In your example, you have already identified four candidate keys, id (the primary key), (name, address), phone and email (declared unique), so we can already say that:
id -> name, address, email, phone
name, address -> id
phone -> id
email -> id

(note that is enough to say that an attribute determines a candidate key, because from this fact we can derive that it determines all the other attributes, that is that it uniquely determines a person).
Then we can see if some other attribute of combination of attributes uniquely determines either other attribute(s), or a candidate key, (which is equivalent to determines all the other attributes).
There are other (non-trivial, interesting) functional dependencies in your relation? Probably no, given the obvious semantics of the data (for instance two different persons can have the same address, so an address does not “uniquely determines” anything, or two different persons can have the same name, etc.). So we could have a certain degree of confidence that the four FDs are an “essential” set of FDs (technically this is called a “cover” of the set of FDs of the relation).
And so from this cover we can start to normalize the relation (that is already normalized, in this case).


SQL Server 2008 R2 DB stuck on restoring I've disabled the witness server and now side 1 is stuck in restoring state and I am unable to get it out of that state. I've tried to restore the DB and now I'm getting the following error. Any suggestions? I've made a backup of the side 2 db and before I just do a restore with that I wanted to see if any of you have come across a more graceful fix.

Thanks!

RESTORE DATABASE DBNAME WITH RECOVERY;

Error message:

  Msg 4303, Level 16, State 1, Line 1
  The roll forward start point is now at log sequence number (LSN) ###700006. Additional roll forward past LSN ###900001 is required to complete the restore sequence.

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

I ended dropping the database and restoring the backup:

DROP DATABASE DBNAME;

Then Restored database with SQL Studio.


my phpMyAdmin data is hashed or hexed? I have a mySQL db setup in MAMP in which I connected mySQL Workbench to administer the design of the database.

When I inserted some data today from within myPhpAdmin (localhost:8888), I noticed the data was being displayed with hashed or hex values.

Here is a screen of the table view which you can see the odd display and text format offering a link to download (never seen this).

If I edit a record, the data is properly displayed.

But if I export the data, the values look to be hexadecimal.

Is my install hosed? Or did mySQL Workbench do something?
Thanks for any answers here.

Rich

From here:

  "varchar fields were set to utf8_bin and needed to be changed to
  utf8_general_ci"


Select Query slow for Non-clustered index I am using SQL Server 2008 R2. Select query is slow when non cluster index is used in the table  compared to table without index.
Query Used:
select * from table order by customer_name

It is  too slow (more than 1 minute for 40000 rows)
Error was due to non-clustered index , without index  query executed in 5 sec.
How did indexing caused  problem?
The query plan:
For without index is : https://www.brentozar.com/pastetheplan/?id=rJZtQfltv
For with index is : https://www.brentozar.com/pastetheplan/?id=Bkrg8zgKD
Queries with Different conditions are executed and results updated.
Query plan  for Query  with order by : https://www.brentozar.com/pastetheplan/?id=ByOxkbpYP
Query plan  for Query  without order by : https://www.brentozar.com/pastetheplan/?id=S1WB1bpYD
Summery :

Column [sl_no] is clustered index
Column [Customer_Name] is non-clustered index
For the queries column with large text used
The elapsed time for  :

With Order by Query1 - 12ms
With Order by  Query2- 1sec
Without Order by Query1-0ms
Without Order by Query2- 3sec
Query with order by with non clustered index on column -5 sec

Because when your query using a non-clustered index (not covering every column), SQL Server finds your data with key lookup. This is more work than scan the entire clustered index.
SQL Server has a cost-based optimization. So, it compares two execution plans and chooses the less coster. But sometimes (depends on your statistics), it does wrong chooses.
Also, check out the tipping point good blog post from Brent Ozar.
If you change your query with a less SELECT col1,col2 than SELECT * and add columns on the non-clustered index as an include which are selecting, you can get rid of from key lookup operation. But, in your situation in which there is no condition, actually no need for the non clustered index.
I create table,index and write a query just like you. As you can see the cost of queries, a non-clustered index+key lookup more than the clustered index scan.

First Query:

SQL Server Execution Times:    CPU time = 32 ms,  elapsed time = 241
ms. SQL Server parse and compile time:     CPU time = 0 ms, elapsed
time = 0 ms.

Second Query:

SQL Server Execution Times:    CPU time = 125 ms,  elapsed time = 245
ms. SQL Server parse and compile time:


Need help on UPDATE query This is my table tracks

id | art | time

id is an auto-increment primary key
art is a varchar(512)
time is a timestamp not null predefined as current_timestamp and on update current_timestamp

It seems really a dumb question but after one hour of tries, someone could please tell me why this simple query doesn't UPDATE the rows with empty art field but instead UPDATE only the time to 0000-00-00 00:00:00?

UPDATE `tracks` SET `time` = `time` AND `art` = 'default.jpg' WHERE `art` = '';

UPDATE

I ran SELECT * FROM tracks WHERE art = ''; and it founds right rows, same using WHERE TRIM(art) = '' .
Instead WHERE art IS NULL is empty.

UPDATE2

This query works but it updates the time and I don't want it.

UPDATE `tracks` SET `art` = 'default1.jpg' WHERE `art` = '';

The initial syntax was incorrect

UPDATE `tracks` SET `time` = `time` AND `art` = 'default.jpg' WHERE `art` = '';

AND does not separate the columns to update. They should be separated by commas (,)

What you probably wanted was

UPDATE `tracks` SET `time` = `time`,`art` = 'default.jpg' WHERE `art` = '';


In PostgreSQL URL I can't use a password containing special characters I have a postgresql database in Azure

The passwd string has special characters like these:

)mvd99/iyH_=ag=Por/W}%%aKY^ygt+,sC7%%P?APOU

psql --dbname=postgresql://db_user@mydemoserver:)mvd99/iyH_=ag=Por/W}%%aKY^ygt+,sC7%%P?APOU@mydemoserver.postgres.database.azure.com:5432/mydb

when executing the psql command, it shows me in following error:

psql: invalid percent-encoded token: "iyH_=ag=Por/W}%%aKY^ygt+,sC7%%P"

I already tried putting the passwd string in quotation marks but it does not work

What other options do I have?
I can not change the passwd

I'm interested in using the psql client, because I have to do very large backups

You have to use URI escapes for all problematic characters.

For this user:

CREATE ROLE "weird@name" PASSWORD '\/ @&?' LOGIN;

You can login like this:

psql postgresql://weird%40name:%5C%2F%20%40%26%3F@localhost:5432/test

The documentation has it:

  Percent-encoding may be used to include symbols with special meaning in any of the URI parts, e.g. replace = with %3D.


Handling modifications of fields within a document I have a basic web app where with the following hierarchy being persisted

>User1
  >Client1
    >ID
    >Name
    >Profession
  >Client2
    >ID
    >Name
    >Profession

>User2
  >Client1
    >Favorite Meal
    >Favorite TV Show
    >Favorite Animal
  >Client2
    >Favorite Meal
    >Favorite TV Show
    >Favorite Animal

So basically, each users have clients and each user's clients have different fields that I want to persist. Under a single user, all the clients will have the same structure. So what I'm doing is persisting each user with nested clients in it's own document within a Clients collection.

What bugs me now is I do not know what design would be good for the following feature :

I want my Users to be able to change the fields that are persisted. So if I take the example above, User1 could remove the ID field if he doesn't need it anymore and replace it with anything else or simply not replace it at all.

1 - Should I allow my user to be able to modify the design or is that a bad idea from the get go ?

2 - If it is not a bad idea, how should I handle these changes when they are made? Should I simply (I know I'm referring to columns but these are my old relational habits)

Delete a column when a column is deleted
Rename a column when is is renamed
Add a column when it is added and let all the old entries have null as a value for this new column

Or should I create a new document every time a modification is made to the data of a user and try to move all the data that fits from the old document into the new one. Then delete the old document.

Are there any other good design patterns that could apply.

I have rarely seen a use case where nested documents were a valid way to go. Sooner or later, any combination of the following the problems occur:

The BSON size limit of 16MB / doc is reached.
Exceeding the padding size of the documents leads to frequent (costly) document migrations.
The more complicated a model gets, the more complicated data manipulation gets - leading to longer maintenance and development times.

My suggestion would be to have independent user and client documents and look them from the client side. This way, a user can even have billions of clients and you still won't run into one of they mentioned problems.

Allowing the user to modify the persisted fields is pretty much a nightmare from the developers point of view. How do you display a decent input view? Or the output? You'd either have to parse the user specific structure and construct both views accordingly or you'd have to store meta information. I'd probably go with the approach to allow them to store additional fields in independent documents like this (which ironically is a use case for embedding documents ;) ):

{
  _id: new ObjectId(),
  user: owningUser,
  // If you want different fields for different customers
  customer: appliedTo,
  fields:[
    {name:"Date of Birth", type:"Date", value:ISODate("2015-05-05T03:46:00-01:00")},
    {name:"discount", type:"Float", value: 7.5}
  ]
}

This way, you'd have the user limited to some 16MB of additional data per customer and you have an idea for how to parse the data with a rather simple model. Setting indices on user and customer should make this efficient, too.


What is most efficient way to store one or two chars? In Postgres table with ca million rows I have a column bookingtype (character varying(20)), which default value is null and besides that it may have 7 distinct values (1-2 chars). Current situation:
 count  | bookingtype
--------+-------------
 389408 | w
 143780 | [null]
  18240 | i
  29496 | a
 356323 | m
    959 | e
   7936 | sl
     55 | kr
(8 rows)

I have btree index for the column, but I am not sure, is it optimal for such situation, where is so few distinct values.
I need to filter rows based on the value of bookingtype and it is always equality filtering I use.
Does special enum-type with 7 possible values have advantage before varchar? Does it need indexing?
What is the best way to implement such column with few values in many rows?
Postgres 9.6

It doesn't matter much: text (= varchar) would be a little smaller, and an enum type would be a little faster (to compare).
Make the decision based on usability: if you might ever need to delete one of the values, don't use an enum. The best is probably to stick with what you have now.
As for indexing, that might just about make sense for 8 different values, but you would have to check if any of your queries might actually benefit. Without knowing the queries, it is impossible to say for sure.


Backup SQL Server to Google Drive I have Microsoft SQL Server 2014 on an Azure VM (Windows Server 2012 R2) with one database. Is it possible to configure an automatic backup to my Google Drive folder? Maybe a special tool or somethings else?

Download the Google Drive desktop application.
When backing up SQL Server, set the destination to the "My Drive" folder created by Google Drive (something like C:\Users\<user>\Google Drive by default)
Once a backup is placed in that folder, it will automatically start to sync to Google Drive.

Also worth nothing, as per Shawn Melton's comment:

You can adjust the folder location for the drive application, so it does not have to reside in a user folder. Any Administrator can actually remove a user profile and you just lost all your backups (locally at least).


How to create a backup schema script including System Tables in Sql Server 2008 R2? I need to back up my entire DB including one table that's allocated in "Tables -> System Tables". When I try to generate the sql scripts to back up my DB this table isn't mapped in this scripts. Do I need to do something more to map this table to the scripts?

Creating scripts of objects usually isn't the best backup method.

There shouldn't be any actual tables listed under Tables > System Tables.  Those should all be catalog views unless you've hacked the system objects to put one of yours tables in there.


In postgres, is using a lock better than transaction when reading data? I have a race condition in the code for my database, and I'm wondering how I can prevent this using either locks or transactions? (And which is best)

I'm using an ORM to update values, however the ORM doesn't support either locks or transactions (though it does support native queries so I'll have to do some of that).

But the code can be summarised to the following:

  I wish to update (of an object A) the reference to table X, from id 1 to 2. - The orm keeps these as simplistic numerical ids, no foreign keys.

  The maximum number of references to an id in table X is based on the corresponding "spots" column in that table.

  So before updating first the amount of free spots is checked, then some further checks are done (calculatedPrimeNumber(1000)). And finally the field is updated.

Or in real terms: I have a tables entries which have a reference to events.

Whenever I wish to update the event of an entry, I check how many free spots there are in the event. (based on events.spots and the amount of entries that have the reference to said entry).

I do some further checks. And then update it.

Now the problem starts when there are multiple requests to update the event data at once: suddenly it can happen that two requests succeed, even though there is only a single spot in the event.

What is the best way to prevent this? Using transactions or how should i lock the database?

So far I can only find the ability to have locks within a single transaction/command. However this would not allow for server level checks as in:

entries = rawQuery('SELECT * FROM public."entry" WHERE id=' + newEventId)
events = rawQuery('SELECT * FROM public."event" WHERE id=' + newEventId)
if (events[0].spots < entries.length) {
    //stop handling, return error that updating cannot happen
}
//DO SOMEFURTHER CHECKS
rawQuery('UPDATE public."entry" SET "event"=' + newEventId + ' WHERE id=' + entry.id)

I think that you can prevent this problem by putting FOR UPDATE at the end of your SELECT statements. I am not sure because your description is somewhat confusing.

To avoid deadlocks, add an ORDER BY id to the queries as well.


How to get Column Names list if row IS NOT NULL in PostgreSQL can i get  'colNew_name' from a select query like table below?

original table:

category_name | col1_name  | col2_name  |
:------------ | :--------- | :----------|
postgres      | 1          | null       |
SAM           | null       | 1          |
MariaDB       | 1          | 1          |

result table:

category_name | col1_name  | col2_name  |      colNew_name   |
:------------ | :--------- | :----------| :------------------|
postgres      | 1          | null       |     col1_name      |
SAM           | null       | 1          |     col2_name      |
MariaDB       | 1          | 1          | col1_name,col2_name|

If your list of columns is small and fixed you could always do:

SELECT *,
       CONCAT_WS(',',
                 CASE WHEN col1_name IS NULL THEN NULL ELSE 'col1_name' END,
                 CASE WHEN col2_name IS NULL THEN NULL ELSE 'col2_name' END)
       AS "colNew_name"
FROM table1

See http://sqlfiddle.com/#!15/a88af8/2


Is there any reason to stop transaction log backups during a maintenance window? We have recently had an issue where the transaction log grew and maxed out the storage space. This happened during a maintenance window (Ola Hallengren index Optimize scripts are being used.)  For some reason, someone in the past has set the transaction log backups to stop for 3 hours, it seems to be while the indexing jobs are running (they are set to run for 3 hours).

My organization recently migrated to sql server 2016 from 2008r2. The person who worked on the migration changed the window of not doing transaction logs to be 30 min shorter, so they now start at 5:30am.  I think he believed they were being stopped for the full backup.  But I believe they were stopped to let the index maintenance run.  Is that necessary in either case?  It seems like stopping tlog back ups would be doing more harm than helping.

The sql server 2008r2 schedule was:

3:00am: Transaction log backups are stopped
3:00am: Weekly Index
Optimize Job starts (this failed after 2 hours 30 min due to log
space)
4:00am: Full backup
6:00am/Changed to 5:30am: Transaction log backups start (but the log remains full)

Additional info:

My organization has recently upgraded to sql server 2016 from 2008r2 in 2008r2 we had never experience this (both are enterprise edition).  Could it just be a coincidence or could the upgrade have changed something? Can any one offer advice on what to check for as to the cause? The Server admin has set the jobs back to the original schedule; no tran log back ups from 3am to 6am believing that the overlap of index maintenance and transaction log backups is causing the issue, could that be true? The more research I do on the subject of transaction logs, the more it seems like we should actually be doing the log backups during this maintenance window not shutting it off.  I'm a bit confused as to why they are being stopped in the first place, the original Admin is no longer with the company. I'm even more confused as to why it seems to be an issue after migration.

You are absolutely correct. There is no reason to stop Transaction log backup during index maintenance window. It will only make things worse as you already experienced.  I had cases where I was doing maintenance on smaller size indexes I even took transaction log backup more frequently than my regular schedule.

In case if you are rebuilding large indexes you need to make sure that you have enough storage for transaction log to grow. Even you take transaction log backup during index rebuild logs will not be truncated when you have a long and active transaction.  That is not the case when you reorganize indexes because logs can be truncated in between while reorganizing a single index.

Even in the Frequently Asked Questions page of Ola Hallengren this is mentioned:

The transaction log is growing very large when I run the IndexOptimize job. What should I do?

  Make sure that the transaction log backup job is running as it should.

  Check that the transaction log has the storage that it needs. You
  should not shrink the transaction log file. Doing so costs resources
  to shrink and later to regrow the file.

During full backup there is no need to stop transaction log backup.  Read this article by By Erik Darling.

What happens to transaction log backups during full backups?


Student who got MAX marks in subject I am facing one issue in getting student name who scored MAX marks in each subject.

Here are my tables below

First one is Student table having STUD_ID , STUD_NAME & Second one is MARKS table having STUD_ID, SUBJECT, MARKS.

Can you help me in getting query to retrieve STUD_NAME, SUBJECT, MAX(MARKS).
I having tried below but facing issue in getting STUD_NAME.

select SUBJECT, MAX(MARKS) as Highest
from MARKS inner join STUDENT
ON STUDENT.STUD_ID = MARKS.STUD_ID
GROUP BY SUBJECT

You can use CTE with RANK() function. Read more here:

Example:

WITH ordermarks AS (
    SELECT [STUD_ID],[SUBJECT],[MARKS], RANK() OVER (PARTITION BY [SUBJECT] ORDER BY [MARKS] DESC) AS rn
    FROM marks
)
SELECT [STUD_NAME],[SUBJECT],[MARKS]
FROM ordermarks om
JOIN Students s ON s.[STUD_ID]=om.[STUD_ID] AND rn=1


PostgreSQL (big data): search by tags and order by timestamp We need to add a search feature on a large table (200M+ rows):

item_id | tags                          | created_at          | ...
-------------------------------------------------------------------
1       | ['tag1', 'bar2']              | 2020-01-06 12:43:32 |
2       | ['example5', 'tag9', 'foo2']  | 2020-01-10 10:40:00 |
3       | ['test1', 'tag5']             | 2020-01-11 12:43:32 |
...

The queries would be similar to this one:

SELECT * FROM items
WHERE tags @> ARRAY['t2', 't5']::varchar[]
ORDER BY created_at DESC
LIMIT 100;

Basically it's like searching some logs by tags and ordering them by timestamp. Seems a common scenario...

What index should we use? Have you ever tested something similar in production?

Example 1: create a GIN index on tags. The problem is that the search may return millions of results and in order to apply order / limit you need to make millions of reads from the table on disk (in order to get the created_at value for each row).
Example 2: add the btree_gin extension and create a composite index on created_at and tags. The problem is the same as above: I think that PostgreSQL cannot use ordering since the index is declared as a GIN index and not as a btree.
Example 3: create a btree index on created_at and tags. PostgreSQL needs to scan the whole index, since btree doesn't support array operators. I also fear that due to the SELECT * PostgreSQL will not use an index-only scan, thus resulting in millions of reads from disk (that would be actually useless since it only needs 100 reads from disk).

There are two approaches:

create an index on the array:

CREATE INDEX ON items USING gin (tags);

That allows the database to quickly find the matching rows, but then it has to perform a top-n sort.
create a B-tree index on created_at:

CREATE INDEX ON items (created_at);

That will allow the database to avoid the sort, but it has to scan and discard the rows that don't match the condition.

Unfortunately, the two strategies are mutually exclusive, and which is best depends on the data. You'll have to experiment.


why archived log size is small but fast recovery area still has a lot free space when recovery database I met a strange problem,my recovery script as follow:

RUN
{
  SET UNTIL TIME 'DEC 25 2013 09:00:00' ;
  RESTORE DATABASE;
  RECOVER DATABASE;
}

but rman report as follow:

...........
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/home/u01/app/oracle/oradata/ghsjdb/system01.dbf'

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/27/2013 10:46:53
RMAN-06557: unable to restore archived log of thread 1 with sequence 742
RMAN-06558: archived log size of 37788 kb is bigger than available space of 6664 kb

then I check fast recovery area at once

SQL> select SPACE_USED/SPACE_LIMIT from v$recovery_file_dest;

SPACE_USED/SPACE_LIMIT
----------------------
            .200951743

SQL> show parameter recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /home/u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size           big integer 20G

there all still 80% free space, is about 20g * 0.8 =16g ,why rman report no available space?

that issue was caused due to lack of space in FRA(Flashback recovery area) if configured or the archive log destination.

option 1.
Restore the archive log to an alternate location which has space

example:

RMAN> run {
set ARCHIVELOG DESTINATION TO '<new location with sufficient free space>';
recover database until time "to_date('2013-12-25 09:00:00', 'YYYY-MM-DD HH24:MI:SS')";
}

option 2.
Set MAXSIZE limit

example:

RMAN> run {
recover database until time "to_date('2013-12-25 09:00:00', 'YYYY-MM-DD HH24:MI:SS')" delete archivelog maxsize 50 K ; }


Statistics Update Time is missing in Actual execution plan I remember that I have seen somewhere in the past that when I saw the XML of the actual execution plan, it had the details about when the stats were updated on the indexes on a table and what was the number of rows sampled. I am not able to find that info now. Is there any knob that needs to be turned on or actual plan never had that kind of information in actual execution plan?

It is indeed in the execution plan, for those stats that were used.
There are two possible reasons why you don't see it:

You use an older version of the database engine, where this info wasn't present in the XML.

You use an older version of SSMS, which doesn't expose the info in the XML.

The answer to which of the above you have is by looking in the XML and see if the info is there. I'm on SQL Server 2019 and SSMS 18.5.
And you need to look at the right place, as well. You probably have to select the SELECT (outermost) operator, perhaps twice with something else in between, and view the Properties window:


Conflict resolution in versioned tables I'm trying to merge two workspaces that contain version-enabled tables. I can't figure out how to resolve conflicts, because I have a lot of rows with the same ID value. They differ only in time interval. If I execute something like this:

DBMS_WM.ResolveConflicts('W_CHILD', 'SOME_TABLE', 'ID=1', 'CHILD');

Which row will be used? How can I specify concrete row?

Conflicts are automatically detected when a merge or refresh operation is requested, and they are presented to the user in conflict views. There is one conflict view for each table.Each conflict view has a name in the form table_name_CONF.
To keep the rows that you want, first of all let me show you the syntax of ResolveConflicts procedure.
DBMS_WM.ResolveConflicts(
   workspace     IN VARCHAR2,
   table_name    IN VARCHAR2,
   where_clause  IN VARCHAR2,
   keep          IN VARCHAR2);

You can use 'keep' parameter to decide on which(Parent, Child or Base) to resolve the conflicts.

In your case you have define 'CHILD' for keep parameter. And where_clause(ID=1 in your case) identifies the rows to be refreshed . Your procedure resolves conflicts involving rows in the SOME_TABLE table in W_CHILD where ID is 1, and uses the values in the child workspace to resolve all such conflicts.
For details visit Oracle documentation:DBMS_WM Package: Reference


What system information is stored in the primary file in SQL Server? Suppose I have created a new database in SQL Server with a file group FG1 and marked this as the default. All my user tables etc are now created in FG1. However I also created a file that is part of the PRIMARY file group. Apparently the system objects are stored in this primary file. I was wondering

What kind of system objects are these? Can I inspect them? Do they appear in the 'System' Database?

Apparently there is some reliability gain to doing things this way. Suppose the file in my primary file group got corrupted, can I still recover my database?

The system objects are the objects which store the schema for all your objects.  The stored procedures, table schemas, etc. are all stored in these system objects.

If the system objects become corrupt you have to restore the primary filegroup.

You can view the data within the system objects by querying the system catalog views such as sys.tables, sys.procedures, etc.

  Do they appear in the 'System' Database?

Not sure what you are talking about, there's no database called "System".  The system databases are master, msdb, model (to some extent) and tempdb.  There is no user database data stored within the system databases.  (Things like logins, jobs, SSIS packages not withstanding.)


Check data is the same after restore SQL I've backed up, transferred to another location and restored a database. Is there anyway to check that the data is the exact same?

Would I be best using the CHECKSUM and then the VERIFYONLY method?

Thanks.

First, CHECKSUM and VERIFYONLY do not do what you think they do.  Backing up with CHECKSUM merely verifies that the page checksums (if there and valid) are valid when writing the backup file.  VERIFYONLY only verifies that the backup file is complete and readable.

But none of this will protect you from corruption in the source database, it is entirely possible for there to be corrupt pages, have them backed up and then restore them into the new database.

What and why are you trying to do the verificaton?  You can do row counts for each table and compare that way.  RedGate has a comparison tool (paid, but with a trial) that can compare and display the differences.