texts: How to filter out the records with the latest date?

texts:
I have a table with id, task_id, created_taskaction (= rec.cr.date).

I would like to get the latest record for e.g. task_id 0001.

In the wordpress framework I tried this:

> $last_task_action2=$wpdb->get_row("SELECT * FROM $wpdb->task_actions
> NATURAL JOIN ( SELECT id_task, MAX(created_taskaction) AS datetime
> FROM $wpdb->task_actions GROUP BY id_task) mostrecent ON
> $wpdb->task_actions.id_task = $wpdb->mostrecent.id_task  AND
> $wpdb->task_actions.datetime = $wpdb->mostrecent.datetime");

But it doesn't work. I tried copying from this thread, but it's still confusing:
https://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql

Even these two don't show ANY data...

$last_task_actiond = $wpdb->get_row("SELECT MAX (created_taskaction) AS 'Max' FROM $wpdb->task_actions");
echo $last_task_actiond;

and

$last_task_actiond = $wpdb->get_row("SELECT MAX (created_taskaction) AS 'Max' FROM wp_task_actions");
echo $last_task_actiond;

Nothing shows up...


texts: What are the cons of having a database server in a Docker container for production purposes?

texts: MySQL/Oracle/Postgres installed on a Docker container, in production, being referenced by other apps. What are the cons on doing this?


texts: Why does my database structure and SELECT operations generate NULLs?

texts:
I'm not too experienced with database problems, so please excuse the wall of text, I believe the context is important to understand the problem and goal.

I receive a CSV file from another system every day that I want to retain in a database running on Microsoft SQL Server. To accomplish this, I have a PowerShell-script scheduled daily that uses the bcp utility to "load" the CSV (accounts.csv) into a table (accounts_stage) that has the "exact" layout of the CSV file. In addition, I "write" the timestamp into another table (import_stage). The accounts_stage table has a trigger set, and bcp is configured to fire that trigger.

After the import is finished, I want to "normalize" the data. For a basic example, assume this concerns only the contract number (in the CSV file a field called "Vnr"), which is a string. I have another table (contracts), which gets filled by the trigger:

INSERT INTO dzp.contractid(vnr)
SELECT DISTINCT
    a.Vnr
FROM dzp.accounts_stage a
WHERE NOT EXISTS(
    SELECT id.vnr
    FROM dzp.contractid id
)

This should, and seems to accomplish, add all contract numbers that are ever imported to the table contractids. contractids.contractId is a BIGINT PRIMARY KEY, IDENTITY, and auto increment.

I add the import-metadata to my table like this:

   CREATE TABLE #IMPORTMETADATA
            ([importid] bigint
            ,[generated_timestamp] datetime
            ,[imported_timestamp] datetime
            )
    INSERT INTO #IMPORTMETADATA
        SELECT TOP 1 [importid], [timestamp_import], [generated_timestamp]
    FROM
            [QGTAA].[dzp].[import]
        ORDER BY
            [timestamp_import] DESC

Next, the trigger should INSERT the data FROM accounts_stage INTO accounts, such that the contractId is replaced (from the original string representation to the PRIMARY KEY of the contracts table).

INSERT INTO [dzp].[accounts]
      ([Udnr]
      ,[ContractId]
      -- snip
      ,[generated_timestamp]
      ,[importid]
      )
SELECT
    a.[Udnr],
    cid.contractId,
    -- snip
    meta.[generated_timestamp],
    meta.[importid]
FROM [QGTAA].[dzp].[accounts_stage] a
LEFT JOIN dzp.contractid cid ON cid.vnr = a.Vnr
CROSS JOIN #IMPORTMETADATA meta

However, this last step fails, because the SELECT operation in the last block yields rows where contractId IS NULL, which I did not expect. Now I believe the issue is somewhere with the LEFT JOIN, but I'm not sure why.

Questions

Can someone please point me in the right direction on this specific problem?
I am not too experienced with databases, is this even a sane way of accomplishing what I intend, or is this a completely insane approach? Is there a better way?

Thank you!


texts: Migrate 7TB of Database for Colo Migration

texts:
We are going to migrate our data center from one state to another in next 3 months. I wanted to know, what is the best possible way to migrate one of our biggest database (i.e. ~7TB in size) as quickly as possible. Here is the information on the server and database:

Existing Data-Center: Georgia
New Data-Center: New Jersey
Server OS: Win 2008 R2 Datacenter
SQL Server: 2008 SP1 Enterprise Edition
Data Storage: XIO System (Which we are not going to get it at new data-center)
Database Size: ~7TB
How data gets into this database: via Transactional Replication from other 8 cluster servers.
How 8 cluster is getting data: via Merge Replication from all our 2000 store servers every 30mins.

We will have a network pipe between our existing and new data-center, but it is not going to be that big. It is also going to get consumed by all the VM's our infrastructure team is going to transfer on daily basis.

We ran a Full Backup (i.e. Ideara SQLSafe with iSpeed settings) on this ~7TB of database, it took ~9 hours and size was ~630 GB. We checked with the vendor who is managing our colo migration and network pipe between two data-center and they said, it won't be possible to Copy over 640 GB file faster to new data-center and we would have to do Full + Differential + TLOG to get up to date.

Our issue is, data we are getting from all our store systems every 30 mins (via Merge Replication to Home Office), it generates lot of data change. If we try to do Full + Differential + TLog, we will be having same sizing issue and lot of downtime to copy over Differential and also we would have to deal with the Log file getting full, which we have limited space on our existing database server (as we are not getting XIO Storage anymore).
We were thinking about SAN Replication, but, as our SAN is going to be different at the new data-center, that won't work for us.

What approach you guys think we should take to migrate this ~7 TB of database from our existing data-center to new data-center with minimum downtime?

**Note:Please let me know if I miss anything from the information side.

Thank you,

HP


texts: How to get for a percentage here in below query?

texts:
A small change: report for each year the percentage of movies in that year with only female actors, and the total number of movies made that year.

SELECT Year,
       COUNT(*),
       (COUNT(*)*100/(count(Movie.mid)*1.0))
FROM Movie
WHERE TRIM(MID) IN (SELECT TRIM(MID)
                    FROM M_Cast
                    WHERE TRIM(PID) IN (SELECT TRIM(PID)
                                        FROM PERSON
                                        WHERE Gender ='Female'
                                       )
                   )
GROUP BY Movie.Year
ORDER BY COUNT(*) DESC

What should I change in this to get the correct percentage?


texts:
We are currently trialling ManageEngine  SQLDBManager Plus. On the dashboard for a server it shows connection time in milliseconds (currently 15ms).

Can anyone tell me what Connection Time represents and/or what I could use this metric for?

We are monitoring a MS SQL Server 2005 database if that makes any difference.


texts: How can I run a proc against all records in a temp table?

texts: I have 4000 records in a temp table with one column. The column is a customerid that I need to update a record for. As of right now the only thing that comes to mind is running an exec statement for all 4000 records. Is there a more efficient way to do this without the use of a cursor?


texts: How to migrate data from SQL database to MySQL including flat files?

texts: One of my friends asked some asp developer to make online application where user can upload files also. jpg, doc, pdf and all these files are saved in SQL database as flat file. Now, my friend asked me to do everything in PHP and he want to migrate all the data which he has in SQL database to MySQL database. I can do it. text can be migrated easily but the files which are saved in SQL database can be migrated to MySQL as flat files. Is it possible?


texts: Is this many to many relationship correct?

texts:
I'm reading this tutorial, and I have question about it.

Look at this picture:

'Reading' the logical Relationship I read that a Project must have one or more Employee. But the physical implementation has that a Project could have zero or more ProjectEmployee.

Or maybe I am wrong and the physical implementation is correct.

What do you think?


texts:
Scenario:

I am trying to understand a strange situation. I have a SQL Server 2012 having DB engine and SQL Server agent configured using same domain account. That domain account isn't added to SQL Server logins. Checked using xp_logininfo 'Domain account', 'all' and didn't get any output. I have a SQL server job which updates a table in a DB. That job is running fine. Till now I was under the impression that SQL server agent account has to be defined in SQL Server login in-order to run the job. However, in this case the job was running fine even though SQL Server agent login is not defined in SQL Server logins. So trying to understand how it is possible. Also, since the job updates a table don't know which user/login it uses to update the table. As a test, I changed the job owner to 'sa' and non 'sa' login in both the cases the job is working fine.

So my questions are:

Is it mandatory to have SQL Server agent account defined in SQL server logins to run the job? If yes, then how come my update job is running even though there is no login present for SQL Server agent account in SQL Server logins. If no, how it internally works? Like which account it uses to start the SQL job.
What user/login my update job is using in order to update the table?


texts:
I've been searching everywhere but I have not been able to find anything about it, seems like everyone is concerned only with WHERE and CASE. (and I haven't a SQL Server at hand reach to verify it by myself)

So, simple question: is SQL Server short circuiting JOIN based on the JOIN condition? If I write:

TableA LEFT OUTER JOIN TableB ON 1=0 AND TableA.ID = TableB.A_ID

will SQL Server skip the join at all?

Edit

Ok, someone correctly pointed out that this is solved at compile time, so I'd better expand on the question.

The above was just intended as an example, but it turned out to be a bad one. What I wanted to know is if SQL Server shorts circuit a JOIN when that condition happens at runtime, as a conseguence of a @parameter passed to a stored procedure.

I'm asking 'cause I'm sure to have used something like that back in the years, but I wanted to be 100% sure before talking about it with some colleagues. So, will SQL Server short circuit this, then:

TableA LEFT OUTER JOIN TableB ON @parameter IS NOT NULL AND TableA.ID = TableB.A_ID

if it's inside an SP and it's called with @parameter set to NULL? Will it make any difference if the store procedure is WITH RECOMPILE?


texts: MySQL 'generator' not closing connections

texts:
I have the following function which is leaving connections open but I can't figure out why.

The Cursor class creates a python-generator type function, allowing me to iterate over millions of rows.

connection=MySQLdb.connect("", "", "", "",
                           cursorclass = MySQLdb.cursors.SSCursor)
cursor=connection.cursor()
cursor.execute("select id, ...", (profile_id, ...))
try:
    for row in cursor.fetchall():
        yield row
except:
    pass
finally:
    connection.close()

Where am I going wrong?


texts: Create DB snapshot without specifying the FILENAME attribute

texts:
I create DB snapshot as one of the tasks in SSIS package. The package works with 20 different DBs. Thus, each DB will have a snapshot with hard-coded FILENAME attribute.
CREATE DATABASE Sales_snap ON
(
  NAME = SalesData,
  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\data\SalesData_snap.ss'
),
(
  NAME = SalesData1,
  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\data\SalesData1_snap.ss'
),
(
  NAME = SalesData2,
  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\mssql\data\SalesData2_snap.ss'
)
AS SNAPSHOT OF Sales

Is there a way to use some default path for FILENAME instead of copy-pasting the same string 20 x 3 times? Alternatively, is there any way to specify base path only once and then reuse it in other SSIS tasks?


texts: MySQL replication - create two slaves but later make one of them master

texts:
We're migrating the DB to a new DataCenter. As a result we need to create a new master and slave on the new DC.
I have the dump from the Old Datacenter master DB (still in production use). I need to use this dump to create two databases. Both will be initially slaves but one of them will become the master and then the other slave needs to replicate from new master.

DB_old_dc     - Master DB
DB_new_dc_001 - Slave replicating from DB_old_dc - Later this will become master.
DB_new_dc_002 - Slave replicating from DB_old_dc but later needs to replicate from DB_new_dc_001.

Can I just change the master IP on DB_new_dc_002 to point to DB_new_dc_001 and will it start working?
Or Do I have to wait till DB_new_dc_001  becomes master, then take a backup and create DB_new_dc_002   slave?


texts: Is it impossible to store 2^266 records on a database of some kind?

texts:
Each records weighs 52 bytes, is it possible in today's technology if someone had the storage needed? What kind of db would hold the data and will make it possible to retrieve it.

An example record:

(5HpHagT65TZzG1PH3CSu63k8DbpvD8s5ip4nEB3kEsreAbuatmU
,1MsHWS1BnwMc3tLE8G35UXsS58fKipzB7a
,1Q1pE5vPGEEMqRcVRMbtBK842Y6Pzo6nK9)

The number of records is 115792089237316195423570985008687907853269984665640564039457584007913129639936


texts: MySQL query for 3 consecutive integers between records

texts:
Given a table where each record has a year integer value, how do I query to see if there are 3 or more consecutive years?

| id | year |
+----+------+
| 1  | 1990 |
| 1  | 1991 |
| 1  | 1992 |
| 2  | 1973 |
| 3  | 1993 |
| 3  | 1994 |
| 3  | 1995 |
| 3  | 1996 |

Given the above table, the resulting records returned by the query would be:

| id |
+----+
| 1  |
| 3  |

The query would return ids 1 and 3 since those two records contain have consecutive years. How would I write such a query?


texts: SQL Server 2008 Full-Text Search installation

texts:
I have full version of SQL Server 2008 R2 on my machine. Only problem is when my computer was created (by the IT Tech's at my work) they did not add the Full-Text Search feature to the installation. I would like to know if there is anywhere that you can download this feature from online. And if there is how do i install it?

Thanks.


texts: Should arrays of IDs stored in Postgres use varchar, int, or another type?

texts:
What is proper convention for creating a field that will hold an array of foreign keys? In this particular case, I'm creating a field called "owners" that will hold an array of user IDs. I want to make sure I choose the right type to maximize speed and follow convention.

Thanks.


texts: MongoDB Explain plan - elapsed time breakdown for executionStats

texts:
I have run an explain plan for a query, and am a bit confused with the timings of each stage, and how they aggregate to the total elapsed time.

Following command was run to generate the statistics, verbosity mode set to executionStats Mode.

expStats = db.testdb.explain("executionStats")

expStats.aggregate([
    {"$match": {
        "Id": {"$in": ["AAA", "BBB"]},
        "testType":"TEST",
        }
    },
    {"$group": {
        "_id": {"Id":"$Id", "testType": "$testType", "testSubType":"$testSubType"},
        "count": {"$sum": 1}
        }
        }
    ])

A summary of the executionStats as follows:

"executionStats" : {
    "executionSuccess" : true,
    "nReturned" : 6355196,
    "executionTimeMillis" : 17575,
    "totalKeysExamined" : 6355196,
    "totalDocsExamined" : 0,
    "executionStages" : {
        "stage" : "PROJECTION_COVERED",
        "nReturned" : 6355196,
        "executionTimeMillisEstimate" : 3464,
        "works" : 6355197,
        "advanced" : 6355196,
        "needTime" : 0,
        "needYield" : 0,
        "saveState" : 50390,
        "restoreState" : 50390,
        "isEOF" : 1,
        "transformBy" : {
            "Id" : 1,
            "testSubType" : 1,
            "testType" : 1,
            "_id" : 0
        },
        "inputStage" : {
            "stage" : "IXSCAN",
            "nReturned" : 6355196,
            "executionTimeMillisEstimate" : 3251,
            "works" : 6355197,
            "advanced" : 6355196,
            "needTime" : 0,
            "needYield" : 0,
            "saveState" : 50390,
            "restoreState" : 50390,
            "isEOF" : 1,
            "keyPattern" : {
                        "Id" : 1,
                        "testType" : 1,
                        "eventTimestamp" : -1,
                        "testSubType" : 1
                },
                "indexName" : "clientEvents",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                        "Id" : [ ],
                        "testType" : [ ],
                        "eventTimestamp" : [ ],
                        "testSubType" : [ ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "keysExamined" : 6355196,
                "seeks" : 1,
                "dupsTested" : 0,
                "dupsDropped" : 0
            }
        }
    }
},
{
"$group" : {
    "_id" : {
            "Id" : "$Id",
            "testType" : "$testType",
            "testSubType" : "$testSubType"
    },
    "count" : {
            "$sum" : {
                    "$const" : 1
            }
    }
}
}

The total execution time time = "executionTimeMillis" : 17575.

STAGE 1 (PROJECTION_COVERED) estimated execution time = "executionTimeMillisEstimate" : 3464.

STAGE 2 (IXSCAN) estimated execution time = "executionTimeMillisEstimate" : 3251.

So 6715 ms spent on the 2 STAGES.

Where was the remaining 10860 ms execution time spent?  I cannot work it out, and the execution stats don't tell me either

This is mongodb version 4.2, running on Linux


texts: How is possible to configure TLSv1.1 protocol for SSL connection in PostgreSQL?

texts:
I need to configure TLS 1.1 protocol for SSL connection in PostgreSQL.

I was not able to see protocol setting in the PostgreSQL configuration

It is required to disable SSL protocols and TLSv1 and enable only TLSv1.1 (or TLSv1.2)

Added

PCI DSS v3.1 requires that fallback to SSL and TLSv1 will be disabled.

Is it is possible to configure PostgreSQL to negotiate using TLSv1.1 only?

If you know that it is impossible please share this information

Added 2

Unfortunately, the configuration of ssl_ciphers is not enough since you can use same ciphers for different protocols.  It is required to configure SSL_METHOD as described here:
https://www.openssl.org/docs/manmaster/ssl/ssl.html.

My conclusion that the configuration of SSL_METHOD (or SSL_PROTOCOL) is missed from the PostgreSQL configuration and it can not be complaint to PCI DSS 3.1.

Please correct me if I am wrong.


texts:
is there any other way beside creating a trigger in MSDB system tables to invoke a particular job when any job failed?

Basically, I want a particular job ran automatically only if any of the other jobs fails.


texts: Using Ubuntu, `ogrinfo` fails to open an mdb file

texts:
Whenever I try to open a sample-mdb file, I'm getting

ogrinfo Campaign_Template.mdb
ERROR 1: PGeo: MDB Tools driver not found!

Warning 1: Unable to install MDB driver for ODBC, MDB access may not supported.

ERROR 1: Unable to initialize ODBC connection to DSN for DRIVER=Microsoft Access Driver (*.mdb, *.accdb);DBQ=Campaign_Template.mdb,
[unixODBC][Driver Manager]Can't open lib 'Microsoft Access Driver (*.mdb, *.accdb)' : file not found
ERROR 1: PGeo: MDB Tools driver not found!

Warning 1: Unable to install MDB driver for ODBC, MDB access may not supported.

ERROR 1: Unable to initialize ODBC connection to DSN for DRIVER=Microsoft Access Driver (*.mdb, *.accdb);DBQ=Campaign_Template.mdb,
[unixODBC][Driver Manager]Can't open lib 'Microsoft Access Driver (*.mdb, *.accdb)' : file not found
FAILURE:
Unable to open datasource `Campaign_Template.mdb' with the following drivers.

I'm seeing the driver only mentioned in the docs,

apt-file search drv_pgeo
libgdal-doc: /usr/share/doc/libgdal-doc/gdal/drv_pgeo.html

However, the current package of libgdal20 does not have drv_pgeo what happened to it, and where can I find it?


texts: sp_setapprole and creating users / logins

texts:
We have set up SQL Server and our Application in such a way that an SQL Server login has … well no permissions when accessing the database using any tool, except our application. This is needed because some people might fire up access (or SQL Server Management Studio) and would be able to manipulate the data in that way.

So we set up an Application Role and gave that Application Role the necessary permissions. Everything works nicely, users can't access our tables unless they use our Application.

Now comes the problem. In our application it should be possible to Create additional logins / users, or set some specific users as being Administrators (only admins can do user related stuff). I'm not 100% certain but in that case I think the user needs SecurityAdmin role. So we have some code in our application which looks like this :

IF NOT EXISTS ( SELECT  name
                FROM    sys.server_principals
                WHERE   name = @LoginName )
    BEGIN
        SET @SQL = 'CREATE LOGIN [' + @LoginName + '] WITH PASSWORD = '''
            + @Password + ''', DEFAULT_DATABASE=[' + @DBNAME
            + '], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF';
        EXECUTE(@SQL);
    END

IF NOT EXISTS ( SELECT  name
                FROM    sys.database_principals
                WHERE   name = @LoginName )
    BEGIN
        SET @SQL = 'CREATE USER [' + @LoginName + '] FOR LOGIN ['
            + @LoginName + ']';
        EXECUTE(@SQL);
    END

IF EXISTS ( SELECT  name
            FROM    sys.server_principals
            WHERE   name = @LoginName )
    BEGIN
        EXEC sp_addsrvrolemember @LoginName, 'securityadmin'
    END

The problem is of course that the server_principals is in the Master Database, and there is no way I can give my Application Role those permissions. Is there any way to work-around this problem or an easy way to solve this ?

Well … actually my problem is I will need a good example on how to set this up I guess. For now I think I can set everything up with an AppUser application role and give that role all insert / select / update / execute rights for an application.

But what permissions should I give to the AppAdmin then in order to allow it to Create LOGINs and USERs on the SQL Server and allow them to set others as Admins and change other users passwords. Any suggestions on that, since that involves some stuff in the Master database which is outside of the scope of my Application Role. Or am I missing something?

With my current setup I'm getting : Msg 15151, Level 16, State 1, Line 1 Cannot alter the login 'test.gebruiker', because it does not exist or you do not have permission.


texts:
What is the alternate name of Data Dictionary?
► Index
► Metadata
► Data
► System Catalog

What I know already is, data dictionaries store all the various schema and file specifications and their locations. They also contain information about which programs use which data and which users are interested in which reports. But what is the alternative name of data dictionary?


texts: How to check memory consumption per PG SQL process on Linux

texts:
So I tried ps aux but the output seems to make no sense. When I calculate sum for "%MEM" column I get 800%. This doesn't make sense to me. Total on RSS column gives me more then 260 GB which is much more then shown as a used memory by top. And total on VSZ gives me 3 TB so that makes no sens whatsoever. And this only processes by postgres (for which command is in format: postgress: user databse remoteIP(123)).

So how do I interpret those values? Is VSZ and RSS really in KiB?

Or how do I calculate memory actually needed by each process/connection of PG SQL?

Using PG SQL 9.3 on that particular server (if this is important).


texts: SQL Management Studio 2016 account disabled

texts:
I have 2 instances, sqlexpress and mssqlserver. I made fool mistake, I disabled the login account that I used it all of the time to login in mssqlserver using windows authentication. And I don't remember the 'sa' password because never use it to log in.

I have read and practice some tips about this problem, and Connect to SQL Server When System Administrators Are Locked Out but my problem still isn't solved.

So, my questions. Does uninstall then reinstall can give me access again? If yes, which should I uninstall-reinstall? My sql server or ssms?
I'm not worried about databases, I just want the access to this instance again for learning.


texts: I've been stuck on the following for quite a while, it seems like it should be relatively simple but I can't quite get my head round it.


texts: Mixed based replication between two databases on the same server

texts:
I have two apps with a current configuration as follows:

App1

DB on server A/MySQL Instance A

App2

DB on server B/MySQL Instance B

Students can apply for internship using App1 and those applications are stored on App1 DB. Because we want to be able to review those applications on App2 and update their statuses. Master-Master replication is used and we are able to get
the apps. Now requirements have changed and we want both Apps and DBs to be on server B with a presume config as follows

Server B

App1
App2
MySQL Instance B contain DB1 and DB2

How can I configure Master-Master replication between two databases on the same instance or is it better to have just one DB now and make both apps use the one DB?


texts: Concatenate a string value from table A to a text field in table B, in a scalable way

texts:
On a daily basis, I am adding values from the following table (table1):

Id    Name      Value
1     'Name 1'  789
2     'Name 2'  789
3     'Name 3'  789

To the text field (Values) in the following table (table2), with a comma:

Id    Name      Values
1     'Name 1'  '123,456,789'
2     'Name 2'  '123,456,789'
3     'Name 3'  '123,456,789'

(So on day one, '123' was added. On day two, '456' was added et.c.)

This is to have a daily history of values, over the last year. The idea of having a "flat" history field (rather than a table with an entry per day) is to have fast reads:

I'm reading a lot from that table
I need all 360 days for every read
I'm writing once a day
I don't need to query the values

I currently have ~32 million rows in each of the two tables above.

Question #1: is this a good way at all of solving my problem of having to display daily values over a year, with fast reads and slower writes?

Question #2: assuming this is an acceptable way of solving the problem - what is the most efficient way of executing the above concatenation? My current solution is to do a MERGE that selects all entries from table1 as a source:

MERGE table2 AS [Target]
USING(SELECT Name, Value FROM table1) AS [Source] ON [Target].Name = [Source].Name
WHEN MATCHED THEN UPDATE SET [Target].Values = [Target].Values + CONVERT(varchar(12), [Source].Value) + ',' + ','
WHEN NOT MATCHED THEN INSERT(Name, Value) VALUES([Source].Name, CONVERT(varchar(12), [Source].Value) + ',');

The reason I am asking these questions is that this takes several hours to run and I'm hitting cannot obtain a LOCK resource at this time errors.

UPDATE #1:
With the very useful comments I got on this question, I learned that the "flat" model I'm using will probably not have any effect on performance as SQL Server reads per page and not per row. Based on that, I'll re-model everything into a more traditional relational structure: one row per entry.

I had to get things working ASAP though, so as a band aid I wrote a stored procedure that updates the flat model without locking. In stead of using a MERGE, it iterates over each row in table1, and for each row, upserts (UPDATE followed by a IF (@@ROWCOUNT = 0) with an INSERT) the corresponding table2 entry. It takes the same amount of time to run, but uses half as much CPU (interesting) and doesn't lock.

Thanks everyone for your inputs.

The stored procedure (Name, Value and Values will have to be renamed):

-- row ID for iteration
declare @id int
select @id = min(id) from table1

-- field variables
declare @name varchar(128),
        @value int

-- iterate through all table1 entries
while @id is not null
begin

    -- select next table1 entry
    select @name = Name, @value = Value
    from table1
    where id = @id;

    -- update corresponding table2 entry
    update table2
    set Updated = getdate(),
        Values = Values + CONVERT(varchar(12), @value) + ','
    where Name = @name

    -- if no rows were updated, there is entry for this name; insert it instead
    if (@@rowcount = 0)
        begin
            insert into table2 (Created, Updated, Name, Values)
             values (
                getdate(),
                getdate(),
                @name,
                CONVERT(varchar(12), @value) + ',')
        end

    -- update @id to select next row
    select @id = min(id) from table1 where id > @id
end


texts: How create a table inside the selected database in PostgreSQL?

texts:
This is the command to create a new database,

CREATE DATABASE mydb;

This is the command to create a new table,

CREATE TABLE IF NOT EXISTS mytable ( int a PRIMARY KEY );

But this does not add the mytable inside the mydb. How to add the table inside the database mydb?


texts: How to indicate what will be measured with a NONCLUSTERED COLUMNSTORE INDEX?

texts:
I am new to columnstore INDEXes.  But I am fairly sure I need one now.
Here is my setup.  I have a table that has the columns ApplicationId, GroupId and TimeInMilliseconds.
I am going to want to run queries like what is the average time for a given ApplicationId or what is the total time for a given ApplicationId and GroupId.
My column store INDEX looks like this:
 Create NONCLUSTERED COLUMNSTORE INDEX CsIdx_PerformanceData
 ON perf.PerformanceData
 (TimeInMilliseconds, ApplicationId, PerformanceGroupId, PerformanceSubGroupId)

But I am confused on what this is saying.  It seems there is no distinction between dimensions and measures here.
Meaning, it seems like I could really be wanting to get the SUM of ApplicationIds when TimeInMilliseconds is 200 (which makes no sense at all).
Question
How do I set this up so that I can do the math on TimeInMilliseconds using the other columns?  (Or am I confusing the issue here?  Do I need a cube for what I am thinking?)


texts: Counting number of occurences in a time period

texts:
I have a view that has 4 columns, an order date, machine reference number, item number, and quantity. How would I generate a list of machines that have consumed an item more than once in a given time period?

For example, I have 5 cars. on car #1, I changed the spark plugs on the 21st and again on the 29th, meaning I changed them more than once in a given period (2 weeks for this example). car 3 had its plugs changed on the 1st and then on the 21st, but the previous month it had its plugs changed on the 1st and the 4th, so it shows up on the list.

order_date    machine_reference_number    item_number    quantity
-----------------------------------------------------------------
'2016-05-21'     1                          21              1
'2016-05-29'     1                          21              2
'2016-04-01'     3                          13              4
'2016-04-04'     3                          13              1
'2016-05-01'     3                          13              4
'2016-05-21'     3                          13              4

I'm fairly certain this will have to be a stored procedure that compares each row, but I was hoping there would be a way to do it in plain-jane SQL without all the loops.


texts: Export Materialized View from postgresql as a table?

texts: I have a materialized view I created in Postgresql. I was wondering is there a way to pg_dump the materialized view as if they were tables, so when I import them into another database it sees it as tables rather than a view created from other tables?


texts:
I'm writing a powershell to create a duplicate table(in another schema) for the purposes of partition switching*.  I'm having problems adding the columns and the clustered column store index.

Here's what I have so far($schemaName is the destination schema).

<snip for parameters getting passed in>

$database = $server.Databases[$dbName]

$table = new-object Microsoft.SqlServer.Management.Smo.Table ($database, $tblName)

$table.Schema = $schemaName

# Next two lines are not working
foreach ($col in $tblname.columns) { $table.columns.Add($col) }
# $table.Columns.CopyTo($tblName.Columns, 0)

## NEED TO ADD CLUSTERED COLUMN STORE INDEX TO TABLE

Write-Output $table.Columns.Count # Getting zero column count
# This isn't working
# $table.Create()

Any ideas on how to add the columns/clustered column store index?  Or a better approach?

= PartitionManager, over on codeplex, doesn't support SQL Server 2014(clustered column store indexes), so I have to roll my own code.

edit title


texts: How to fix MySQL anonymous account security issue?

texts:
How to fix this security issue on MySQL?

[!!] User '@localhost' is an anonymous account.


texts: Tips for catching changes in table data over time interval for export to Data Warehouse

texts:
I've recently been tasked with exporting data from one of our databases to a central Data Warehouse. The export is supposed to be incremental since a full load every 24 hours would be a massive waste of resources, bandwidth and time.

And therein lies my question, what is the best way to catch both new rows as well as changed rows in the database? I've seen recommendations on everything from Timestamps to creating a DateTime column and update it manually on each insert/update, and now I'm at a complete loss.

If I were to go with the DateAndTime column, I figure that there MUST be a way to create for instance a trigger or similar to catch all inserts and updates, or am I wrong?

I know that this question isn't quite as down to the nitty gritty as many other questions, but I'm really stuck and will appreciate all help and/or hints of where to look. Thanks in advance. :)


texts:
How do I retrieve data from one MySQL table and store the data into another database table on the same server. I have tried this code but keep getting errors:

CREATE PROCEDURE `ProfusionTest` ()
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY DEFINER
BEGIN

DELETE FROM `Profusion`.`source_cdr`;

INSERT INTO
    `dest_table`.`create_info`
    SELECT * FROM `Profusion`.`source_cdr`;

END


texts: Is there a way to pass the password more securely while scheduling backups in MySQL?

texts:
I run the batch file like this:
mysqldump.exe --user=uuu --password=pswd --host=localhost --port=3306 --result-file="c:\mydata\backups\db_backup.%date:~0,2%-%date:~3,2%-%date:~6,4%_%time:~0,2%.%time:~3,2%.%time:~6,2%.sql" --default-character-set=utf8 --single-transaction=TRUE --databases "mydb"

in Windows Task Scheduler, but I don't like the fact that I pass the mysql user's password like this. Is there a way to keep the password but pass it more securely?


texts: PostgreSQL: Check the structure of foreign tables

texts: How can I check if the structure of a foreign table was changed in the source database in order to know when to re import it. Foreign table was imported by foreign-data wrapper postgres_fdw (PostgreSQL 11.2).


texts: MongoDB merge two collections and drop documents with same value in field

texts:
In my database I have two collections, but some documents were added (possibly) at different times to both collections.

I could use mongodump and then mongorestore for merging. But then I have the same documents that were added to both collections as duplicates in my new collection. mongorestore --drop does not help neither, because the documents not necessarily have the same _id.

How to drop a document when a document with userid exists already?


texts: Execution plan for staging In-Memory tables estimate row count =1

texts:
We are using In-Memory tables (durability schema only) as temporary staging tables for part of the ETL process.
the nature of these staging tables is empty most of the time.
The ETL process executed per organization asynchronicity.

Problem:
The execution plan is generated with estimate row number = 1 (probably the table was empty for first execution)

Manually update statistics will not fix the issue since:
the table may be empty or the no record for an organization that ETL will execute first.

I add OPTION recompile to query but still it not stable

I'm looking for correct pattern working with in-memory tables as the Staging table

MS SQL 2017 CU 13
Standard edition

In-Memory table schema:

CREATE TABLE [dbo].[process_aggregation_close] ( [organization_id] [bigint] NOT NULL, ...... INDEX [ix_process_aggregation_memory_computer_id_process_id_test] NONCLUSTERED ( [organization_id] ASC, [computer_id] ASC, [process_id] ASC ) )WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY )

Destination Clustered Column Store (disk base)

CREATE TABLE [dbo].[process_aggregation_5m]( [organization_id] [bigint] NOT NULL, ....... ) ON [ps_process_aggregation_close]([partkey]) GO CREATE CLUSTERED COLUMNSTORE INDEX [IX_process_aggregation_5m] ON [dbo].[process_aggregation_5m] WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0, DATA_COMPRESSION = COLUMNSTORE) ON [ps_process_aggregation_close]([partkey])

Thank you
Yuri


texts: FK to table with non-unique values via a on commit refreshing materialized view?

texts:
I would like to create a FK to a table without unique values. So I cannot create a simple FK. [to clarify, the parent table has duplicates - dont ask why, not in my control ]

I tried creating a materialized view:

CREATE MATERIALIZED VIEW UniqueView
REFRESH ON COMMIT
AS SELECT distinct currency from currencies

.. and then creating a FK to UniqueView. This works however, I cannot use ON COMMIT refresh because "distinct" function is used.

I would like the materialized view to be always up to date with the underlying table.

Any other methods of achieving this?
Many thanks in advance!


texts: SQL Server : auditing AD Users - how can we identity AD User account from AD Groups

texts:
I would like to identity which accounts on our servers belong to AD Users.

Currently, I find a plethora of accounts from users that should in fact be part of AD Groups registered on the same SQL Server. In effect these AD User accounts are redundant (or they should be).

Question is: how can we identify these AD users and AD groups in SQL Server?

Thanks,


texts: SQL Server 2000 Backup fails with Status = 33

texts:
I am trying to take a Full Backup of a database size about 75GB.

It is SQL Server 2000 - 8.00.2055 x86  version. I have plenty of disk space where the backup is being created. So disk space isn't an issue.

The backup process starts fine but half way through it errors out just saying with status = 33.

I have been looking for any information online but couldn't find anything....  Any suggestions any solutions any pointers in the direct direction is much appreciated.......


texts: Running SSRS and SSAS from different server than the primary database instance(SQL Server Engine)

texts:
Right now both SSRS, SSAS  and SQL Server Engine are on the same server.  We are using 2008R2 Enterprise. We are thinking to upgrade to SQL Server 2012 or 2014. and We want put on separate serve the SQL Engine and SSRS,SSAS on SQL Server 2012 or 2014.

What we want is:

  Server 1 for SQL Server Engine

  Server 2 for SSRS and SSAS

My questions are:

How they talk each other(the two Servers)?
Where the cube for SSAS stored?
Where the report builder build report?
Can you give my high level explanation how server 1 and server 2 interact or direct me some document I can read?


texts: Load-balanced MySQL cluster without load balancer

texts:
I'm looking to create a load-balanced MySQL cluster, but without the actual load-balancer, in order not to add another point of failure or complexity.

What I was thinking was to have the following:

Have a master-master setup for MySQL
On every client, place a simple round-robin proxy which would rotate the requests between servers.

Is this possible? Or there are better ways to achieve this?


texts:
I have been creating some reports in SSRS 2008 R2. When I need to make borders that show up for all rows, both on the top and bottom of the report, as well as all the rows in between I can tell that the inner borders of the report are thicker because it is actually 2 borders side by side.

Is there a way to collapse borders that are side by side?


texts: How to use an alias name that has RANK() OVER in a WHERE clause

texts:
I have a query that has a RANK() OVER function inside it but I would like to use the results displayed on this column in the WHERE clause that follows. How do I write that as all the other questions I have looked at did not have RANK() OVER and seemed easier to do. Here is the statement:

USE SMSResults

SELECT Student_No,Result,Module_Name,Semester,Year,RANK() OVER (PARTITION BY Student_No ORDER BY Semester  DESC) AS Rnk
FROM tbl_results
WHERE Student_No = '201409'

ORDER BY Year DESC

I would like to use the Rnk column in the WHERE clause


texts: how to improve performance by using or not using table variables

texts:
I have a table variable:

DECLARE @to_process TABLE
(
    [Id] [bigint] NOT NULL,
    [SequenceId] [bigint] NOT NULL,
...
)

INSERT INTO @to_process
   (  Id
    , SequenceId
...
   )
  SELECT
    TOP (@recordsToProcess)
      Id
    , SequenceId
...

in my stored procedure. I have investigated that insert into it spends about 66% of total execution time.

How can I improve or optimize my code to speed up my sp execution?

ADDED:


texts:
Is [database_xml_deadlock_report] the same as [xml_deadlock_report] just with extra fields?

I'm creating a basic deadlock and blocking capture and see both.  Most examples online show [xml_deadlock_report], but [database_xml_deadlock_report] has extra fields that may be useful.


texts: Firebird query performance with text search, also some numbers

texts:
I have situation as follows: I'm having a table OFERTY of 10k+ records, and when I select * from it, I get

PLAN (OFERTY NATURAL)
Executing...
Done.
656 fetches, 0 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 0 index, 312 seq.
Delta memory: -95312 bytes.
Total execution time: 0.188s
Script execution finished.

This is all fine, but a problem manifests when I need to present data from many joined tables on a listview, and allow filtering on text fields using starting with clause. I get these numbers:

PLAN JOIN (JOIN (OFERTY NATURAL, KL INDEX (KONTRAHENT_PK), PL INDEX (KONTRAHENT_PK),
PERSONEL INDEX (RDB$PRIMARY28), STATUSY INDEX (RDB$PRIMARY34), KOLORYSTYKA INDEX
(KOLORYSTYKA_PK), MODELE INDEX (MODELE_PK), A INDEX (IDX_SLOWNIKI_NUM_DZ), B INDEX
(IDX_SLOWNIKI_NUM_DZ), C INDEX (IDX_SLOWNIKI_NUM_DZ), RKOL INDEX (REJESTR_PK), RSZY
INDEX (REJESTR_PK)), KLIENCI_PROJEKTANCI INDEX (RDB$PRIMARY53))
Executing...
Done.
67370 fetches, 0 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 11199 index, 10669 seq.
Delta memory: -20 bytes.
Total execution time: 2.610s
Script execution finished.

As you can see, query time is skyrocketing. I think it reads ALL the records instead of first-some and I don't know why. There are indexes everywhere there's a need for them (as far as I know). The very same query, but without following text search clause:

/*kl.kt_nazwa is from table KONTRAHENCI which has alias KL and is joined to OFERTY*/
and kl.kt_nazwa starting with 'strefa'

has exactly the same plan, but performs as follows:

Executing...
Done.
10801 fetches, 0 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 2444 index, 200 seq.
Delta memory: -1036 bytes.
Total execution time: 0.203s
Script execution finished.

That's a HUGE difference. It doesn't need to read ALL the records from table KONTRAHENCI. So I have two questions here:

Is there a way of text search in firebird that does not mandate reading ALL records from table (in this case: KONTRAHENCI)?
there's also awful lot of fetches. Please explain what they are?


texts: intermittent TNS-12533: TNS:illegal ADDRESS parameters

texts:
I am running a windows XP (SP3) VM in virtualbox, on which I host Oracle XE.

It has been running fine like this for a couple of years, but recently I am seeing some strange problems.

My client software fails, and the log files say :
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

So I log on to the DB, and try running sqlplus / as sysdba;

which gives me the following:
ora-12560 tns protocol adapter error

So following the debugging steps from several websites, I try this:
tnsping db
and the result is :
TNS-12533: TNS:illegal ADDRESS parameters

So I decide to test another SID:
tnsping xe
which works fine.

As you can see from the tnsnames.ora file, those two SIDs are configured identically.

I've not touched that file in a couple of years and don't intend to do so now.

So I restart the windows machine, and now everything works.

Why did the error message indicate a problem with the file, when upon restart it doesn't complain?

Why would this suddenly stop working?

I checked the alert log and found this:

ORA-27300: OS system dependent operation:CreateThread failed with status: 8
ORA-27301: OS failure message: Not enough storage is available to process this command.
ORA-27302: failure occurred at: ssthrddcr
Process J000 died, see its trace file
kkjcre1p: unable to spawn jobq slave process

I've tried wiping some files from the hard disk (there was 1GB free space, which seems like plenty) but it hasn't worked.

Any ideas?

The contents of my tnsnames.ora file include:

XE =
  (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = windows-virtual)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = XE)
  )
)

DB=
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = windows-virtual)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = DB)
   )
 )


texts: Oracle SCN_TO_TIMESTAMP (ORA_ROWSCN) fails

texts:
Trying to run the following SQL which is basically an exact copy of an example I have seen on multiple sites, showing how to do this (I just change the tablename)

SELECT
SCN_TO_TIMESTAMP(ORA_ROWSCN) AS scn_time, ...
FROM
TABLE_ONE
WHERE
...;

But when I run it, it returns this error:

ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1
08181. 00000 -  "specified number is not a valid system change number"
*Cause:    supplied scn was beyond the bounds of a valid scn.
*Action:   use a valid scn.

What can I change to get this working? I am just looking to find the date/time a row was last updated.

Cheers in advance

K


texts: SQL Server: join two tables with "valid-to" dates


texts: Cannot shutdown old postmaster when upgrading to Postgres 9.2

texts:
I'm upgrading to Postgres 9.2.2 (from 9.1.4). When I try to upgrade the DBs using:

pg_upgrade -b /usr/local/Cellar/postgresql/9.1.4/bin -B /usr/local/Cellar/postgresql/9.2.2/bin -d /usr/local/var/postgres91 -D /usr/local/var/postgres

I get the following error message:

Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories                 ok

There seems to be a postmaster servicing the old cluster.
Please shutdown that postmaster and try again.
Failure, exiting

I've trying stopping the server, but cannot get the upgrade command to work. How to I shutdown the old postmaster?


texts: MySQL switch between data directories for individual databases

texts:
I'm in the middle of designing a solution to an issue we're currently experiencing while writing to certain large MyISAM tables. The database in question basically stores information regarding two 'things', users and articles. The majority of the user tables are small but are read from and written to regularly. The majority of the article tables are very large but are pretty much only read from. Lots of the tables are linked through foreign keys (between the user tables, between the article tables and between user and article tables), and there are several views. The server is fairly hefty and has no problem serving the data under normal conditions.

The issue arises when we need to update the article data (which is now required every few days) and on doing so causes a backlog of writes to a few of the larger tables.
I had thought about separating the user data and article data into separate databases and then running the article update on a cloud server then migrating the updated data files over to the database server and switching MySQL over to the updated data. I know it's possible to specify the data directory in the my.cnf files all data but haven't been able to find out whether it's possible to specify data directories for specific databases. If not then this idea becomes pretty useless.
Any information on either whether this is possible (or not) or any other solutions would be great.

Thanks

p.s. We're running on MySQL 5.0.77


texts: How to grant permissions for User defined type in Oracle

texts:
This is probably easy but i can't seem to figure it out. I created my user-defined type as CO_ADMIN. However, the application using the type runs using GS_USER database account.

I have granted permissions to GS_USER as CO_ADMIN but yet anytime my application attempts to use the user defined type, i always get:

: type ""."DOCPARAMETERTYPE_TABLE" not found    at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck)
   at Oracle.DataAccess.Types.OracleUdtDescriptor..ctor(OracleConnection con, String schemaName, String typeName)

Here are the statements i have tried so far. All say "Grant succeeded" after running them but it's not true or perhaps i am using the wrong statements. Please help:

GRANT all on "CO_ADMIN"."DOCPARAMETERTYPE" to "GS_USER" with grant option;
GRANT all on "CO_ADMIN"."DOCPARAMETERTYPE_TABLE" to "GS_USER" with grant option;

GRANT EXECUTE ON "CO_ADMIN"."DOCPARAMETERTYPE" TO "GS_USER";
GRANT EXECUTE ON "CO_ADMIN"."DOCPARAMETERTYPE_TABLE" TO "GS_USER";

Here are the definitions for the types:

create or replace type DOCPARAMETERTYPE as object (
docID varchar2(20),
system varchar(50)
);

create or replace type DOCPARAMETERTYPE_TABLE AS TABLE of DOCPARAMETERTYPE;


texts: Count consecutive null rows from a joined table

texts:
I have the following tables:

members:
----------------------------------------------
| member_id | member_name | member_join_date |
----------------------------------------------

events:
-------------------------
| event_id | event_name |
-------------------------

event_dates:
--------------------------------------
| event_id | event_date | event_week |
--------------------------------------

attendance:
------------------------------------------------------------
| member_id | event_id | attendance_date | attendance_week |
------------------------------------------------------------

So I have members who joined the organization at a certain time and they may attend weekly events (the occurrences of which are monitored by the event_dates table)

What I want to do is get all members that have at least 3-consecutive absences

I did all I could and I think I have each part of the query but don't know how to merge them together.

Get all events that they could've attended:

    SELECT
      m.member_id AS id,
      ed.event_id,
      ed.event_week
    FROM
      members AS m
      LEFT JOIN
        event_dates AS ed
        ON
          ed.event_date >= m.member_join_date

Get all the events they actually attended:

    SELECT
      m.member_id AS id,
      a.event_id
    FROM
      members AS m
      LEFT JOIN
        attendance AS a
        ON
          a.member_id = m.member_id

And probably the answer to this question if I manage to merge the two queries into one:

https://stackoverflow.com/questions/1321670/count-of-consecutive-not-null-values

But this time I want to count NULL values


texts:
I just found that on the SQL Server 2012 MSDN documentation for binary and varbinary types, they mention the following at the end of the article:

Conversions between any data type and the binary data types are not
guaranteed to be the same between versions of SQL Server.

I noticed that this does not exist in the previous versions of the article for 2005, 2008, and 2008 R2. I searched around online but didn't find any exact explanations that satisfied my question of "WHY?". (Which is the question I have here.)
The project I am working on needs to store data as varbinary(max), but based on this little note, I am afraid if I need to move the binary data and then convert it on another SQL Server version that it will not match the original data converted to binary, so answering this question more in depth would be a great help.
Thanks!


texts: Migrating ssrs reports between different domains

texts:
I have an SSRS server in Domain A and i was asked to copy all the folders and reports from domain A to an SSRS server in domain B.

The network team has opened the ports between the two servers in the different domain and i can connect to the web browser of each SSRS from the opposite server.

When i copy reports between server in the same domain i usually user rs service commands. i tried doing the same thing between the two domains and recieved this error:

  could not connect to server http://localhost/[sourceserver]/reportservice2010.asmx

for reference:
server A - is the source server in domain A
server B - is the destination in domain B

this is the command i'm running on CMD:

     rs.exe -i ssrs_migration.rss -e Mgmt2010 -s http://[server A IP]/reportserver -u Domain A\username -p domain A password -v f="/foldername" -v ts="http://[server B ip]/Reportserver" -v tu="Domain B\username" -v tp="Domain B password" -v tf="/foldername" -v security="True"

also important to mention the the destination server already has reports on it so i cant restore the reportserver DB to it.

what can i do to solve this?
Thanks


texts: In Oracle, where can I see the history of DBFs autoextending? Or get the timestamp that a DBF last autoextended, and by how much?


texts: Correct data model for users with clients

texts:
I have an application where users are granted access. Part of the system allows users to have their own 'clients'.

I have a users table

********************************
 users
********************************
 PK user_id    int
    username   string
    password   string(salted)
********************************

and clients table

********************************
 clients
********************************
 PK client_id  int
 FK user_id    int
    username   string
    password   string(salted)
********************************

What i want is that each user should be able to have a client with the same name. The case could be that two individuals choose to go with a different user, but by chance choose the same username. Therefore, there can be multiple entries for a username in the clients table, but against different user_ids.

The problem I have is that although the username field is not unique in the clients table, it should be unique for each user_id i.e. a user cannot have two or more clients with the same username.

How would I model this?


texts: Join tables based on variable value contained in column

texts:
I have two tables with dates as the primary column to join. But, I also need to join based on the value from one column that is partially contained in a column from the other table.

Table 1: date, audience

Table 2: date, channel

'audience' in table 1 has a value that is partially contained in 'channel' in table 2 like this:

audience = "oldpeople"

channel = "oldpeople_email"

There are hundreds of potential 'audience' values and 'channel' values, so I cannot name the values, it has to work dynamically. This is what I have tried that has not worked:

SELECT * FROM table1
left JOIN table2 ON ((table2.Channel like '%table1.audience%')
     AND (table2.date = table1.date) )


texts: What is the language of my SQL Server setup files?

texts:
Im trying to install SQL Server in my Windows Server in english . But got this error.

I look for a solution and say I have to change the setting on Control Panel to match my media.

The problem is not sure what is the lenguaje of my sql server install disk.

How can I know what is the lenguaje of my Setup Disk?


texts: Seeting up a PostgreSQL database on another hard drive than my system one on OS X?

texts:
I am on OS X 10.8.3, I've install PostgreSQL from macports, but I've realized that I have not enough space on my system drive for the DB I am about to populate with data.

Is there a way to create new database on another drive/partition (not the system one, where postgres is installed)?

How can I do that?


texts:
This is going to be a long story I'm afraid ;-(

I have to read some text-files into a SQL database. This database is on a SQL 2008 R2 x64 database server. I want to use a linked server so after some research I found that I had to install the Microsoft.ACE.OLEDB.12.0 x64 provider. I did this on my local machine and after I created my linked server I had some trouble reading text files.

The provider for the linked server was set to use Microsoft.ACE.OLEDB.12.0, the datasource pointed to the correct folder and the providerstring was set to "TEXT", but I could not test the connection. It was throwing this error:

  Cannot initialize the data source object of OLE DB provider
  "Microsoft.ACE.OLEDB.12.0" for linked server "XXXXXXXXX". OLE DB
  provider "Microsoft.ACE.OLEDB.12.0" for linked server "XXXXXXX"
  returned message "Unspecified error". (Microsoft SQL Server, Error:
  7303)

After some research, I found that the SQL Service was running under the NETWORKING SERVICE-account. When I changed this to Local Account, everything was working fine.

Note: I also had to change some settings for the provider, "Allow inprocess" is checked, otherwise it was not possible to browse the files in the configure datasource folder.

But now I try to implement this solution on my customers network and I cannot get it to work. It is also a SQL 2008 R2 x64 Db-server, Microsoft.ACE.OLEDB.12.0 provider is installed, but it still throwing the mentioned error. So I asked them which account is used to run the SQL Service. This is an AD-account belonging tot the local Administrator group of this server and therefore it had enough privileges as far as I can see. The user who is testing is connected to the database engine with a windows AD account, also belonging to the administrator group. But when he tries to test the connection, it throwing the same error.

An other thing I tried is using the OPENROWSET, this also give an unspecified error. And yes I have set the "Ad hoc distributed query" using the next statement:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

So here I got my development box, where everything is working fine. But when I deploy to the test-server it has these connection-problems. Actually I don't know what to do or check anymore. I have been working and searching for several days now. Does anyone have some other suggestions I can try?


texts: Are composite primary keys bad practice?

texts:
I want to know if composite primary keys are bad practice and if not, in which scenarios is their use beneficial?
My question is based on this article

Note the part about composite primary keys:

Bad Practice No. 6: Composite Primary Keys
This is sort of a controversial point, since many database designers talk nowadays about using an integer ID auto-generated field as the primary key instead of a composite one defined by the combination of two or more fields. This is currently defined as the “best practice” and, personally, I tend to agree with it.

However, this is just a convention and, of course, DBEs allow the definition of composite primary keys, which many designers think are unavoidable. Therefore, as with redundancy, composite primary keys are a design decision.
Beware, though, if your table with a composite primary key is expected to have millions of rows, the index controlling the composite key can grow up to a point where CRUD operation performance is very degraded. In that case, it is a lot better to use a simple integer ID primary key whose index will be compact enough and establish the necessary DBE constraints to maintain uniqueness.


texts: modeling advice for table which may have 2 father tables

texts:
I am developing a ERP for a small cooperative with special needs (that'y why they need a non-general solution).

I'd like to model the following:
A "receipt" can either be incoming or outgoing
For that I'd like to have three tables:
collection | payment | receipt
So a receipt can belong to a Collection or a Payment

I'd like to model the Receipt table
with id, collection_id (fk), payment_id (fk)
so there would be a 0,1:n relation from both father tables to the receipt table.

Does that make sense from a DB development view?
Or is there another recommended way to model multiple inheritance?

The same question applies to a a document (image, PDF, etc.) - any uploadable resource:
A document can either belong to an activity, a receipt, a project, etc.


texts: Granting SA privileges for Developers on the development box

texts:
In spite of our vehement protests, our management has decided that the development team must be granted 'sa' rights on the development server. The catch is that we, the DB support group are still responsible for maintaining this box.

We have now been entrusted the task of coming up with a list of Dos and Don'ts for the development teams with these enhanced privileges.

Please add to this list:

DO --
    confine activities to the DB under development

DO NOT --

change any SQL instance settings
sp_configure (including cmdshell)
add/change/delete any security settings
add/change/delete database objects
add/change/delete server objects like backup devices and linked servers
add/change/delete replication
add/change/delete maintenance plans
touch any database that doesn't belong to your team

Any pointers to tools available for tracking these users activities will be greatly appreciated.


texts: is it possible to see the rows MySQL 5.7 locked

texts:
I am using this sql to update, but now I amd not sure how much rows it locked.
UPDATE r_room_seat s
        SET s.status = 1,
        s.room_play_id = (SELECT @update_id := max(room_play_id)),
        s.user_id = (
            case when s.seat_num = 1
            then 63182
            when s.seat_num = 2
            then 16472
            else -1 end
        ),
        s.robot_flag = (
            case when s.seat_num = 1
            then 1
            when s.seat_num = 2
            then 0
            else 0 end
        )
        WHERE s.status = 0
        and s.online = 1
        and s.room_play_id in (
            select room_play_id
            from (
                select room_play_id as room_play_id
                from r_room_seat
                where room_id = 166
                and status = 0
                group by room_play_id
                having count(*) = 2
                limit 1
            ) a
        )
        and s.room_id = 166
        LIMIT 2

and which rows locked, is it possible to see the locked record? For example, let the MySQL hang on locked state. Now I am sure it using RECOED lock, not a table lock.


texts: Performance issue with update in postgres

texts:
I've got a pretty simple update statement in my application in one of the APIs.
UPDATE "table" SET "count" = "count" + 1 WHERE "id" = $1

The problem is when this API incurs a sudden load it starts to fail. There is no increase in DB CPU only the number of active sessions shoot up. When checking the RDS performance insights this is what I see.
The number of updates to this table doesn't shoot up more than 300. I don't think postgres should be behaving this way with such low number of updates (time spent in waits). It'll be helpful if I could have suggestions what might be going wrong here? I understand what does wait in tuple and transactionid mean but how can I reduce these waits?
Thanks


texts:
I have a view as shown below.The View is good running.When I attempt to run view,I got the following the error.What might the problem?

Incorrect syntax near the keyword 'Declare'

SQL Query:

create View [dbo].StokMaliyet
AS
Declare @Dolar decimal(18,3),@Euro decimal(18,3)

set @Dolar=(select Dolar from GunlukDoviz
set @Euro=(select Euro from GunlukDoviz)

 select Stok_Kodu,
 (case
 when dovtip='3' then net_maliyet
 when dovtip='2' then ((net_maliyet)*@Dolar)/@Euro
 When dovtip='0' then (net_maliyet)/@Euro
 end )  as Maliyet
 from ib_mlyt


texts:
I have a query like

Select * from tbl1

WHERE
  ( price > 10
AND qty > 200
AND condition=1
AND name like '%abc%')

My question is how MySQL will process this? My concern is that I want to run query in this way that results should be reduced on basis of conditions.

For example

( price > 10 AND qty > 200 AND condition=1 And name like '%abc%')

First, MySQL should filter records having price > 10 and apply next condtion qty > 200 on resultant dataset and at the end, it should put this condition AND name like '%abc%' on it.

How should I write query to achieve this?

Another question is: Does MySQL start reading conditions from start to end or from end to start?


texts: Process was intentionally killed - now the process is still showing in "show processlist "

texts:
We had a MariaDB long-running process (40 hours), so I killed the thread. But, the thread is still there, with:

showing Command as Killed.
a State of Unlocking tables.
Info is insert into ...

What does this mean? More importantly, how do I get rid of it? It's been over 31 hours since the thread entered the "Killed" state.

UPDATE: We restarted the container running MariaDB. There was no indication of anything wrong but... Would it make sense to recast this thread to be now about how to check for errors in the database (My MariaDB skills are rudimentary) or should I start a new thread?


texts: Is there an equivalent of "OPTION(RECOMPILE)" or "WITH RECOMPILE" for an entire connection?

texts:
I'm curious.  Is there any way to prevent SQL query execution plans from being cached for an entire connection's duration?

Can I run a command that basically says, "use OPTION(RECOMPILE) for all subsequent commands until I tell you to stop doing so?"

I'm acutely aware of all the performance trade-offs here, and I know this is not a step to be taken lightly.  However, I'm in a unique situation where this behavior may be advantageous.

UPDATE: I found trace flag 253, but cannot find official mention of what exactly it does: https://stackoverflow.com/questions/2596587/what-does-sql-server-trace-flag-253-do


texts: SQL Server Replication Administrative Password not working

texts:
I have been struggling for the past one week to setup the replication in our environment and has been stuck on Administrative Password issue when adding Distributor on Publisher Server.

Important Note: All of our SQL Server environment is on EC2 AWS Cloud instances. I hope it is not Amazon that is blocking this feature.

Replication Configuration I'm using:

Distributor: Remote Distributor(as we do not want any performance impact on our prod)
SQL Server Version: Is same on all servers (Distributor, Publisher, and Subscriber)
Instance: Default instance (not named)
No firewall in our environment.
All servers are fully connected (Can connect to each other via SSMS).
Remote access is enabled.

Troubleshooting I have performed:

Reset the Distributor password from Distributor Properties. (https://social.technet.microsoft.com/Forums/azure/en-US/dadf963d-9246-44ed-841a-10d27a6cdc4e/not-able-to-connect-to-remote-distributor-from-publisher-using-administrative-link-password?forum=sqlreplication)
Removed publishers and added it back and reset the password.
Deleted the whole distributor and reconfigured.
Changed distributor_admin password from Security (GUI) and using the script method (sp_changedistributor_password [ @password = ] 'password')
Tried the configuration on another server as distributor.
Ensured password policy is met, even removed password policy enforcement.
Removed distributor, changed SSMS version and configured the distributor again.
Configured the whole setup using scripts only.
Checked the Error log of target Server (No login or repl related errors)
Searched for the issue exhaustively on forums.

No solution has worked so far, any input will be appreciated.

Additional Info:

When I configured the Distributor on remote machine it created two Linked Servers (repl_distributor and Second as servername of the publisher server). When I tested the connection of both servers I got error as below screenshot. Online search suggest enabling MSDTC which is did but no luck with resolving the original issue or even the Linked Server issue.


texts: MySQL "Statement may not be safe to log in statement format" warning

texts:
What is the meaning of the following MySQL warning:

Statement may not be safe to log in statement format.

I have some tens of thousands of these statements every day in my mysqld.err file, and I'm not sure where should I begin my investigation.


texts:
This is the 5th time. It happens once a week (Tuesday or Wednesday within 03:00-07:00 UTC+0). On the console, it shows available but inaccessible. We try to wait if the instance will recover itself, after ~30 min nothing happens. So I reboot it manually, then it came online again after rebooting (~5 min).

It would be helpful to know what actually went wrong. This is only a dev server with few users and records.

Engine: Aurora MySQL 5.7.12
DB instance class: db.t2.small
Backup time: 16:00-16:30 UTC+0
Maintenance time: sun:17:00-sun:17:30 UTC+0

Below is the only list of available logs after rebooting the instance.

error/mysql-error-running.log.2018-07-24.03 Tue Jul 24 11:14:06 GMT+800 2018    11.8 kB
error/mysql-error-running.log.2018-07-24.04 Tue Jul 24 11:30:00 GMT+800 2018    285.5 kB
error/mysql-error-running.log.2018-07-24.05 Tue Jul 24 12:30:00 GMT+800 2018    31.1 kB
error/mysql-error-running.log.2018-07-24.06 Tue Jul 24 13:30:00 GMT+800 2018    31.8 kB
error/mysql-error-running.log.2018-07-24.07 Tue Jul 24 14:30:00 GMT+800 2018    32.9 kB
error/mysql-error-running.log.2018-07-24.08 Tue Jul 24 15:30:00 GMT+800 2018    29 kB
error/mysql-error-running.log.2018-07-24.09 Tue Jul 24 16:30:00 GMT+800 2018    32.1 kB
error/mysql-error-running.log.2018-07-24.10 Tue Jul 24 17:30:00 GMT+800 2018    27.5 kB
error/mysql-error-running.log.2018-07-24.11 Tue Jul 24 18:30:00 GMT+800 2018    31.7 kB
error/mysql-error-running.log.2018-07-24.12 Tue Jul 24 19:30:00 GMT+800 2018    27.1 kB
error/mysql-error-running.log.2018-07-24.13 Tue Jul 24 20:30:00 GMT+800 2018    22.4 kB
error/mysql-error-running.log.2018-07-24.14 Tue Jul 24 21:30:00 GMT+800 2018    22.8 kB
error/mysql-error-running.log.2018-07-24.15 Tue Jul 24 22:30:00 GMT+800 2018    24.7 kB
error/mysql-error-running.log.2018-07-24.16 Tue Jul 24 23:30:00 GMT+800 2018    24.7 kB
error/mysql-error.log   Wed Jul 25 00:34:45 GMT+800 2018    2.6 kB
external/mysql-external.log Wed Jul 25 00:30:00 GMT+800 2018    7.6 kB

external/mysql-external.log

/rdsdbbin/oscar/bin/mysqld, Version: 5.7.12 (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time,ServerHost,User,UserHost,Command,Payload
/rdsdbbin/oscar/bin/mysqld, Version: 5.7.12 (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time,ServerHost,User,UserHost,Command,Payload
/rdsdbbin/oscar/bin/mysqld, Version: 5.7.12 (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time,ServerHost,User,UserHost,Command,Payload
----------------------- END OF LOG ----------------------

error/mysql-error-running.log.2018-07-24.03 shows: https://pastebin.com/ywmXLR5g.

error/mysql-error-running.log.2018-07-24.04 shows: https://pastebin.com/g1dkR6rj.

error/mysql-error-running.log.2018-07-24.18 shows: https://pastebin.com/g0aAXfaT.

All other logs shows nothing(see photo).

Event Logs

July 24, 2018 at 11:14:14 AM UTC+8  DB instance restarted
July 24, 2018 at 11:13:31 AM UTC+8  Error restarting mysql: Engine bootstrap failed with no mysqld process running...
July 24, 2018 at 11:12:01 AM UTC+8  Recovery of the DB instance is complete.
July 24, 2018 at 11:04:26 AM UTC+8  Recovery of the DB instance has started. Recovery time will vary with the amount of data to be recovered.

CPU Utilization (07-24-2018)

CPU Utilization (07-11-2018 to 07-24-2018)


texts: Creating Conditional Rules for Column Values

texts:
I am currently working on the following table in SQL Server

Table: order_status_logs
    log_id      INT NOT NULL IDENTITY(1,1)
    order_id    INT NOT NULL
    status_id   INT NOT NULL
    log_date    DATE NOT NULL
    expected_by DATE NULL

In this table for the status_id column there are the following values possible:

[1] - Pending
[2] - Ordered
[3] - Backordered
[4] - Received

I would like to set up my table so that if a new record is trying to be created, expected_by goes from NULL to NOT NULL if the status_id is not equal to 4.  Essentially unless the order has been received, the table will always need data for the expected_by column.

Is it possible to do this with a CONSTRAINT?  Or is this something I'd need to do via a Procedure where I return an error message or something if I don't provide that column data?


texts:
I'm learning SQL.

When I try to create a type in Oracle SQL:

CREATE TYPE boolean_typ AS OBJECT
(valor char(5) CHECK (VALUE IN ('true', 'false', NULL)) );
/

I get this error:

Warning: Type created with compilation errors.

It gives no explanation on what is the error. May you help me?


texts: Is it possible for a trigger to update a row in a table using a value that was deleted?

texts:
I have a trigger which is updating a column in a different table after the row is inserted:

mysql>delimiter $$

mysql> create trigger status

 after insert on table2

 FOR EACH ROW

 BEGIN update

 table1 inner join table2

 ON table1.id=table2.id

 set table1.state=0 ;

Now I want to reset the table1.state=1 whenever the same value gets deleted. Is this possible?


texts:
I have created a database and have my username which is part of the rds_superuser group. I granted a friend access to my database and whenever he creates a new table I am unable to view his table and get an access denied error code [42501].

Also, If I try to change any privileges to my user account, I get an error that I don't have permission on his tables. How do I fix this?


texts:
I have several hundred IoT devices pushing data to my server every minute. The data is structured and it has following columns:

Site ID (String)
Event Start time (Date Time)
Event End Time (Date Time)
Data (String)

Data once created, does not change or update. The primary purpose of this database is going to run Analytics.

I started working with MongoDB, but its performance degraded after a few hundred thousand of rows. Someone suggested that as my data is structured, I do not need NoSQL. I moved to RDBMS (Postgres). It performed better than the earlier solutions but it is getting to its knees after a few million rows. Now someone else suggested that as I am not planning to use any features of a transactional database, an RDBMS is not a suitable solution. I probably need an Analytical Database.

I researched a bit for a better solution and found that Elastic Search, Vertica, HBase etc. can be a solution to my problem. There are probably a dozen of other solutions as well and I have zero experience with any of these.

My question is, how should I proceed? The learn, experiment, repeat loop seems to be too long for me.


texts:
I found in this article the formula that MySQL use to ranking in FTS

w = (log(dtf)+1)/sumdtf * U/(1+0.0115*U) * log((N-nf)/nf)

Where

dtf     is the number of times the term appears in the document sumdtf
is the sum of (log(dtf)+1)'s for all terms in the same document U
is the number of Unique terms in the document N       is the total
number of documents nf      is the number of documents that contain
the term

But I wonder that what is 0.0115?


texts: Should I use an int column to represent date?

texts:
I am designing a Invoice table and would like assistance deciding on a couple indexes and the Primary Key.

For the Primary Key - using GUID may sound like a good idea to me, but does this mean the child tables need to have this very GUID as a foreign key as well?
Invoices are always filtered on creation date using a date range. So instead of a datetime column, I plan to have an extra int column which only contain the date information like '20160101' because I assume comparing the int is much faster than datetime and would benefit me. Since the creation time will be ever increasing, I also plan to apply the Cluster Index on this column. Is this a good idea?
Invoices may also be queried based on customer as well, should I put another index on CustomerID or compose with the date column?


texts:
I've just installed MySQL and wondering if SHOW TABLES output can be combined. Btw, I'm not a DBA, please be patient if this question doesn't make sense to you. I just want to know how SQL query works, and what not.

E.g.

mysql> SHOW TABLES;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| KEY_COLUMN_USAGE                      |
| PROFILING                             |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| STATISTICS                            |
| TABLES                                |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
+---------------------------------------+
17 rows in set (0.00 sec)

mysql>

Certain column can be filtered with LIKE

mysql> SHOW TABLES LIKE 'SCHEMATA';
+-----------------------------------------+
| Tables_in_information_schema (SCHEMATA) |
+-----------------------------------------+
| SCHEMATA                                |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql>

Instead of typing this one by one ...

mysql> SHOW TABLES LIKE 'TABLES';
+---------------------------------------+
| Tables_in_information_schema (TABLES) |
+---------------------------------------+
| TABLES                                |
+---------------------------------------+
1 row in set (0.00 sec)

mysql>

I was wondering if SHOW TABLES output can be filtered with more than 1 parameter.

Probably something like SHOW TABLES LIKE 'SCHEMATA' or 'TABLES';, but this query doesn't work

Desired Output

+-----------------------------------------+
| Tables_in_information_schema            |
+-----------------------------------------+
| SCHEMATA                                |
| TABLES                                  |
+-----------------------------------------+

Update:

I've just noticed that SHOW TABLES also can be produced with SELECT statement

mysql> SELECT TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA = 'information_schema';
+---------------------------------------+
| TABLE_NAME                            |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| KEY_COLUMN_USAGE                      |
| PROFILING                             |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| STATISTICS                            |
| TABLES                                |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
+---------------------------------------+
17 rows in set (0.01 sec)

mysql>

Still didn't work

mysql> SELECT TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA = 'information_schema' and TABLE_NAME = 'SCHEMATA' or 'TABLES';
+------------+
| TABLE_NAME |
+------------+
| SCHEMATA    |
+------------+
1 row in set, 1 warning (0.00 sec)

mysql>


texts: How to model a user/group relationship where users are assigned an unique number within the group?

texts:
I am working on a web-server applications that deals with 2 entities:

users
groups

A user can belong to one, several or no group at all. Fairly usual, I assume.

However, I have additional constraints: each user should be able to receive a unique number (unique within the same group) in a "stable" and "deterministic" way, that is:

If a user is added, removed or updated, the existing users must keep their actual assigned numbers in every group.
The number should not be automatically assigned (not like an id. The number should be set explicitly by the web-server administrator when managing groups membership).

I haven't done a lot of Data Modelling and maybe this is dumb simple, by I can't decide which way to go and how to design my tables.

Do you guys have a suggestion ?


texts: Oracle DBLINK to SQL Server Cluster 2000 Fail when Database Failover to node-2

texts:
We have a DBLINK from Oracle database that connects to SQL Server Database version 2000 using SQL Authentication. Whenever the database that DBLINK uses failover to node-2 of the cluster (physical) it can't connect or fail. Any idea where I should start looking?
I checked the tcp ports on both servers and it is the same. I wonder if it has anything to do with the SID?


texts: Schedule Maintenance Plan - Request For a Good Sequence

texts:
I have installed Ola Hallengren's MaintenanceSolution.sql script.

So what I'll get out-of-the-box are a lot of jobs:

My questions is:

What schedules should I implement based on the jobs that are provided with the script?

The SQL-Full Backup starts on Saturday at 11:00 pm. So I thought I would use the following schedules:

Job                           Schedule
----------------------------- ------------------------
IndexOptimize                 Saturday at 10:00 pm
Database IntegrityCheck       Saturday at 09:00 pm
CommandLog Cleanup            Sunday at 03:00 am
Output File Cleanup           Sunday at 03:15 am
SP_delte_backuphistory        Sunday at 03:20 am
SP_purge_Jobhistory           Sunday at 03:25 am

What's your advice?

Every comment is welcome.


texts: creating new table partition with values already existing in the default partition

texts:
I have the following table:

CREATE TABLE orders
(
  info_date  date,
  country_code  VARCHAR,
  order_total   int,
  CONSTRAINT orders_pk PRIMARY KEY (info_date, country_code)
) PARTITION BY LIST (country_code);

CREATE TABLE orders_def PARTITION OF orders DEFAULT;

I insert some rows  with country_code 'foo' and they end up in the default partition.

After some time i decide that 'foo' deserves it's own partition, how would i do it?

As according to the doc:

  If a DEFAULT partition exists, a new partition cannot be added if
  there are any rows in DEFAULT partition such that they would otherwise
  fit in the new partition being added.

this my attempt:

begin;
CREATE TEMP TABLE temp_table ON COMMIT DROP AS
SELECT * FROM orders where country_code = 'foo';
DELETE FROM orders where country_code = 'foo';
CREATE TABLE orders_foo PARTITION OF orders FOR VALUES IN ('foo');
INSERT INTO orders_foo select * from temp_table;
commit;

however it seems like a hack.


texts: Use surrogate or natural PK if a row is uniquely identified by two or more columns?

texts:
I am having a hard time choosing between natural and surrogate PK for my database table.

The database is for a ranking system for an MOBA game. Here is more or less what I would do if I were to go the natural route.

Player table

 - PlayerID Int AI NN UQ
 - PlayerName Varchar PK
 - ServerName Varchar PK
 - Registered date, level, hero etc misc columns

Rank table

 - PlayerName FK references player
 - ServerName FK references player
 - RankID Int AI NN UQ
 - PlayerRank Int NN UQ

The thing is, each row in the player table is uniquely identified by the pair of PlayerName and ServerName. I thought using a surrogate key in this case is not really appropriate but I would like to hear suggestions on this.


texts:
Several days ago I saw this in master log:

 replica 2017-02-06 16:10:55 UTC ERROR:  requested WAL segment 000000030000096D00000052 has already been removed

This message was repeating untill I stopped slave.

Checking pg_stat_activity on prod showed autovacuum task on table ~250GB, I decided that it produced huge amount of WALs. Checking them confirmed my assumption - all wal_keep_segments=500 were last minute. So I thought that network bandwith was not sufficient to send WALs fast enough to slave to replay them. Master has archive_command = cd ., so no way back.

No panic, I recreate replication. I:

delete all data files on slave
pg_start_backup() on prod
rsync -zahv data
pg_start slave
pg_stop_backup() on prod and check logs.

Imagine my desperation to see that slave has this:

-bash-4.2$ head -n 20 /pg/data93/pg_log/postgresql-2017-02-09_121514.log
LOG:  could not open usermap file "/pg/data93/pg_ident.conf": No such file or directory
LOG:  database system was shut down in recovery at 2017-02-09 12:15:07 UTC
LOG:  entering standby mode
LOG:  redo starts at 982/39074640
FATAL:  the database system is starting up
FATAL:  the database system is starting up
FATAL:  the database system is starting up
LOG:  consistent recovery state reached at 982/3AEC60F8
LOG:  unexpected pageaddr 97C/83EC8000 in log segment 00000003000009820000003A, offset 15499264
LOG:  database system is ready to accept read only connections
LOG:  started streaming WAL from primary at 982/3A000000 on timeline 3
ERROR:  requested WAL segment 000000030000096D00000052 has already been removed
ERROR:  requested WAL segment 000000030000096D00000052 has already been removed
ERROR:  requested WAL segment 000000030000096D00000052 has already been removed
ERROR:  requested WAL segment 000000030000096D00000052 has already been removed
ERROR:  requested WAL segment 000000030000096D00000052 has already been removed
ERROR:  requested WAL segment 000000030000096D00000052 has already been removed

and so on...
3 days passed! That segment has gone, but why is it looking for it???

ATM I'm kind of ignoring the ERROR and doing pg_dump (25% already done) to read all tables pages to make sure I can consume data. Because replication is happening - data populated on master streams to slave at once.

I have checked this and other posts in mail list, so I'm not asking why it checks 97C after "consistent recovery state reached at 982". I'm asking what should I do about this error? I cant change much in master setup - it is prod, but I'm thankful for any advise.


texts: Query plan for a dynamically built `query_int` using `format()`

texts:
To make things simple here is a statically built version of the query that runs perfectly fast:

SELECT * FROM media WHERE tag_ids @@ '123&321'::query_int ORDER BY rating DESC LIMIT 20;

The catch is that the tag IDs are kept in another table and of course the app's UI accepts tags as strings. So the actual query looks something like this:

SELECT * FROM media WHERE tag_ids
  @@ (SELECT format('%s', (
    SELECT ARRAY_TO_STRING((
      SELECT ARRAY(
        WITH tag_ids as (SELECT id FROM tags WHERE tag IN ('kittens', 'puppies')
      )
      SELECT id FROM tag_ids
      UNION
      SELECT 0 FROM tag_ids WHERE NOT EXISTS (SELECT 1 FROM tag_ids)
    )), '&')
  )))::query_int
ORDER BY rating DESC LIMIT 20;

So the problem is that the query planner always predicts that the tag_ids @@ ... clause will return around 32k rows. Considering that there is a LIMIT of 20 then an index scan on rating, followed by a sequential scan makes total sense. But! Some, indeed most, tag filters return way less than that, making an index scan problematically slow, eg:

->  Index Scan using media_rating_index on media  (cost=0.43..2741321.93 rows=32509 width=11) (actual time=70.816..24270.968 rows=20 loops=1)
         Filter: (tag_ids @@ ($5)::query_int)
         Rows Removed by Filter: 1565931
 Planning Time: 0.514 ms
 Execution Time: 24271.155 ms

Of course when using a statically built tag_ids @@ ... clause for tags that are known to have a low occurrence a tag-based index is chosen by the query planner, eg:

Limit  (cost=9163.00..9163.05 rows=20 width=11) (actual time=3.838..3.841 rows=3 loops=1)
   ->  Sort  (cost=9163.00..9168.96 rows=2384 width=11) (actual time=3.834..3.836 rows=3 loops=1)
         Sort Key: rating DESC
         Sort Method: quicksort  Memory: 25kB
         ->  Bitmap Heap Scan on media  (cost=38.48..9099.57 rows=2384 width=11) (actual time=2.780..3.796 rows=3 loops=1)
               Recheck Cond: (tag_ids @@ '654&321'::query_int)
               Heap Blocks: exact=3
               ->  Bitmap Index Scan on media_tag_ids_gin__int_ops_index  (cost=0.00..37.88 rows=2384 width=0) (actual time=2.023..2.023 rows=3 loops=1)
                     Index Cond: (tag_ids @@ '654&321'::query_int)
 Planning Time: 2.218 ms
 Execution Time: 3.952 ms

One way to solve this is to just send 2 queries to the DB from the application, then the format() can even be done in the application code. But that just feels a bit like giving up. The other thing is an SQL function, but that seems overkill. Is there another more idiomatic way to give the query planner the tag IDs so it can better predict the number of returned rows and thus plan a better query?


texts: MySQL InnoDB stop locking when large sql is executed

texts:
We face a issue that the InnoDB table is locked, when a sql file that is executed is large than a certain file size.

The problem does not appear when the file size is much lower, for example 25MB. But when the file increase to something like 45+ MB the InnoDB table locks, when the sql is on 70% of running.

Is this something that we can prevent by changing our my.cnf or something else?

Our current my.cnf:

[mysqld]
sql_mode=""

bind-address = 127.0.0.1
local-infile=1
innodb_file_per_table=1
innodb_file_format=barracuda

skip-name-resolve
performance_schema=ON

slow_query_log = 1
slow_query_log_file=/var/log/mysql-log-slow-queries.log

# key_buffer_size = 390M
max_allowed_packet = 200M
# sort_buffer_size = 16M
# read_buffer_size = 16M
read_rnd_buffer_size = 2M
# myisam_sort_buffer_size = 64M
tmp_table_size = 300M
query_cache_type = 1
query_cache_size = 650M
query_cache_limit = 2M
query_cache_min_res_unit=512
thread_cache_size = 100
max_connections = 225
wait_timeout = 900
connect_timeout = 120
innodb_buffer_pool_size = 8G
max_heap_table_size =290M
innodb_log_buffer_size = 36M
# join_buffer_size = 32M
innodb_buffer_pool_instances = 8

long_query_time = 5
table_definition_cache = 4K
open_files_limit = 60K
table_open_cache = 50767
innodb_log_file_size= 256M
innodb_lock_wait_timeout = 1000
innodb_lru_scan_depth = 100
innodb_flush_neighbors = 0
innodb_io_capacity_max = 10000
innodb_io_capacity = 5000

innodb_purge_threads = 4
innodb_flushing_avg_loops = 10

Small part of the sql file:

SET NAMES 'utf8';
SET CHARACTER SET utf8;
/************** SYSTEM ******************/
UPDATE `catalog_product_entity` SET  `updated_at` = '2018-09-24 08:45:15',  `updated_by` = '23'   WHERE `entity_id` = '158700';
UPDATE `catalog_product_entity` SET  `updated_at` = '2018-09-24 08:45:15',  `updated_by` = '23'   WHERE `entity_id` = '158701';


texts:
I wanted to know if DBMA_REDEFINITON package allows a WHERE clause to filter contents before migration.

I have a partitioned table and wants to copy data and constraint to another table using DBMA_REDEFINITON but while copying contents, I do not want to copy a particular partition from the original table. Is it possible to drop this partition using WHERE clause.

The question came from following information given on Oracle Tips site

Online table redefinition:  You can drop large numbers of rows from a table by adding a WHERE clause predicate to filter-out unwanted rows when you copy the table.

My requirement is from the example below, can a new table have only partitions Part_2 and Part3. (Dropping partition Part_1 using WHERE clause)

-- Existing Table
CREATE TABLE ORIGINAL
(
  ID                INT NOT NULL PRIMARY KEY,
  TEXT              VARCHAR2(255 CHAR) NOT NULL
)PARTITION BY RANGE
  (
    ID
  )
  (
    PARTITION part_1 VALUES LESS THAN(2),
    PARTITION part_2 VALUES LESS THAN(3),
    PARTITION part_3 VALUES LESS THAN(MAXVALUE)
  )
/
-- some data in the table
insert into ORIGINAL values (1, 'test');

-- some index on the table
create index idx_ORIGINAL on ORIGINAL(TEXT);

--INTERIM table for REDEFINITION (has all partitions right now)
CREATE TABLE ORIGINAL_INTERIM
(
  ID                INT NOT NULL PRIMARY KEY,
  TEXT              VARCHAR2(255 CHAR) NOT NULL
)
PARTITION BY RANGE
  (
    ID
  )
  (
    PARTITION part_1 VALUES LESS THAN(2),
    PARTITION part_2 VALUES LESS THAN(3),
    PARTITION part_3 VALUES LESS THAN(MAXVALUE)
  )
/

-- Start Redefinition
BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE('TSPACE1','ORIGINAL','ORIGINAL_INTERIM');
END;
/

-- Copy objects
DECLARE
  num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS ('TSPACE1','ORIGINAL','ORIGINAL_INTERIM',
    DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
/

-- Sync
BEGIN
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE ('TSPACE1','ORIGINAL','ORIGINAL_INTERIM');
END;
/

-- finish
BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE ('TSPACE1','ORIGINAL','ORIGINAL_INTERIM');
END;
/

-- Drop interim table
drop table ORIGINAL_INTERIM cascade constraints purge;


texts:
I want to create a MySQL NDB cluster on Hyper-V for testing so I have some questions in this regard.

Can I use Windows XP 32 bit? (because I already have a VM) If not then can I use Windows 7 or 10?
Do I only need one VM where both nodes will be installed (I don't know if MySQL supports multiple instances or something like that) or do I need at least two VMs?
I read that NDB cluster require load balancer. Can I do it without load balancer? I mean I will manually run statements (Insert/Update/Delete) on server1 to check if it goes to server2 or not instead of using some PHP application.
Do I need free version of MySQL or some enterprise version?
Do I only need MySQL or some other software also needed to setup cluster?


texts: Installing oracle 9i in RHEL5 ... error during the process

texts:
Currently I am installing oracle 9i in RHEL5, but I have found that during the installation process at 5% completion it throws this error:

  Error in writing to file /path of oracle home/ctx/lib32/libvs_zip.so

I'm using this guide:
https://sites.google.com/site/bantisandy/oracle/oracle-9i-in-rhel5

Has it happened to someone?

Please someone knows how to fix it?


texts: What are the implications of creating an Integration Services Catalog (SSISDB) where msdb-deployed packages exist?

texts:
I have inherited a production 2016 SQL Server with SSIS installed, which has many SSIS packages deployed to msdb (mostly warehouse ETL workloads that run nightly).
I would like create a new Integration Services Catalog and deploy future SSIS projects to SSISDB. Can these two deployment methods and package locations coexist? In other words, if I create the new catalog and start using the project deployment model for new SSIS projects, will that have any effect on the dtsx packages already in msdb?


texts:
I am trying to create a schema.

But I am getting this error :

mysql> create schema myBase;
ERROR 13 (HY000): Can't get stat of './myBase' (Errcode: 13 -
Permission denied)

When I do a ls -l on /var/lib I am getting :

drw-rw-rw- 8 mysql         mysql         4096 juin   6 14:41 mysql

I don't know what is wrong here.
I anyone knows..


texts: creating deadlock for distributed database in mysql-(Testing purpose)

texts:
I need to create a deadlock  for distributed database in MySQL for testing purpose with a simple sql query(without front end), can anyone good with MySQL query help me out in this with sample query. code can be with stored procedure, trigger or with MySQL query will be helpful.

Regards,

ACUBE.


texts: Should I run SQL Server services on multiple servers under different AD accounts?

texts:
I am looking at changing all of our SQL Server instances to use AD accounts for the running services.  I plan to use a different AD account for each service type, e.g. DB, SSIS, SSRS, etc.  My question is whether I should create an account for each type and use those accounts across all the instances in the environment or create accounts for each service per server.

For example, if I have 2 servers A and B both running a database instance and SSIS:

Scenario 1 (single AD account across domain)

SQLDB (database service)
SQLSSIS (SSIS service)

or

Scenario 2 (multiple AD accounts)

Server A accounts

SVRA_DB (database service on server A)
SVRA_SSIS (SSIS service on server A)
SVRB_DB (database service on server B)
SVRB_SSIS (SSIS service on server B)

I know scenario 2 won't work for failover clusters and does pose a potential headache in terms of maintenance if the AD accounts are set to expire.  Just wondering if there are any other gotchas to either approach and what best practices might be?

As an idea of our environment, we have about ~75 servers running various versions of SQL Server from 2005 up to 2014.

Thanks in advance for any help or insight!

Cheers,

Chris