texts: Storage space requirements for a varchar(40) column to store 'abcd' with charset 'ucs2' and 'utf8mb4'?

texts:
I understand that if the length is > 255 it will require 2 bytes to store the length prefix. I just need to verify my assumptions. Please tell me the length prefix and the length of string('abcd') for charsets(latin1, ucs2, utf8mb4). For a varchar(20) column.
The documentaton says:

[...] For the string 'abcd', L is 4 and the storage requirement is five bytes. If the same column is instead declared to use the ucs2 double-byte character set, the storage requirement is 10 bytes: The length of 'abcd' is eight bytes and the column requires two bytes to store lengths because the maximum length is greater than 255 (up to 510 bytes). [...]

Reference: 11.7 Data Type Storage Requirements (Oracle | Docs)
I just don't understand why for 'abcd' with charset 'ucs2' it requires 2 byte length prefix even after it being less than 255.


texts: Oracle Monitoring RMAN: report need backup

texts:
Hello Oracle Database Admins!

In the last few months I came across that it would be a good idea to monitor rman backups and check if any file needs backup.

RMAN> report need backup;
--> Report ERROR if any records show up.

Aditionally I would like to monitor the following command:

RMAN> report unrecoverable;
--> Report ERROR if any records show up.

I do not want to parse the output of RMAN. So here is my question: Does anyone know a method to monitor these two commands using SQL and/or PL/SQL?

I know that RMAN just uses SQL and PL/SQL to get the result but an sqltrace just lead me into non-documented pl/sql funtions with encrypted code.


texts: T SQL Table Valued Function to Split a Column on commas

texts:
I wrote a Table Valued Function in Microsoft SQL Server 2008 to take a comma delimited column in a database to spit out separate rows for each value.

Ex: "one,two,three,four" would return a new table with only one column containing the following values:

one
two
three
four

Does this code look error prone to you guys? When I test it with

SELECT * FROM utvf_Split('one,two,three,four',',')

it just runs forever and never returns anything. This is getting really disheartening especially since there are no built in split functions on MSSQL server (WHY WHY WHY?!) and all the similar functions I've found on the web are absolute trash or simply irrelevant to what I'm trying to do.

Here is the function:

USE *myDBname*
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[utvf_SPlit] (@String VARCHAR(MAX), @delimiter CHAR)

RETURNS @SplitValues TABLE
(
    Asset_ID VARCHAR(MAX) NOT NULL
)

AS
BEGIN
            DECLARE @FoundIndex INT
            DECLARE @ReturnValue VARCHAR(MAX)

            SET @FoundIndex = CHARINDEX(@delimiter, @String)

            WHILE (@FoundIndex <> 0)
            BEGIN
                  DECLARE @NextFoundIndex INT
                  SET @NextFoundIndex = CHARINDEX(@delimiter, @String, @FoundIndex+1)
                  SET @ReturnValue = SUBSTRING(@String, @FoundIndex,@NextFoundIndex-@FoundIndex)
                  SET @FoundIndex = CHARINDEX(@delimiter, @String)
                  INSERT @SplitValues (Asset_ID) VALUES (@ReturnValue)
            END

            RETURN
END


texts: Inputting auto-incrementing, grouped, unique hashes?

texts:
Alright, here's what I've come up with so far:

SET @id=(SELECT IFNULL(MAX(id) + 1, 0) FROM leads);
UPDATE leads SET id=@id WHERE id IN (
    SELECT id FROM (SELECT * FROM leads) AS something WHERE hash IN ("hash1", "hash2")
);
INSERT IGNORE INTO leads (id, hash) VALUES (@id, "hash1"), (@id, "hash2");

Basically, I find the max id and add one, then update all indices if a hash is repeated. Finally, I insert the new hashes and ignore any duplicates.

Is this good? I have no idea. It works, but that's about as much as I know.

I've come up with a (hopefully) better way to save unique company data, which now consists of unique hashes for each entity. Basically, there are multiple hashes associated with a company:

hash1: ABC Corp
hash2: ABC Corp
...
hash6: XYZ Corp
hash7: XYZ Corp

Consequently, I'm trying to create a hashes table that looks something like this:

hash (unique primary key), company_id (auto increments with the group)

For example, if I inserted the above example information, it should be reflected in the hashes table like so:

hash   | company_id
-------|------------
hash1  | 1
hash2  | 1
hash6  | 2
hash7  | 2
hash8  | 2
hash13 | 3
hash14 | 3

From there, things get a bit more tricky:

Say I want to add some new hashes, all to the same company but I don't know if the company is in the tables or not. To decide that:

If none of the new hashes exist in the hashes table, I want to create a new company_id for that group and use that id for these hashes and in subsequent insert statements.
If, however, any of the new hashes already exist in the hashes table, I want to merge all of the company_id's into a new value in the hashes table and any other table that has rows referencing that id. Finally, I want to add any additional data using the new company_id.

Say (case 2) I have these hashes to add:

hash1  | ?
hash6  | ?
hash17 | ?
hash18 | ?

The result, after inserting them, should be:

hash   | company_id
-------|------------
hash13 | 3
hash14 | 3
hash2  | 4      -- company_id modified 1->4, hash2 appeared in the new hashes
hash6  | 4      -- company_id modified 2->4, hash6 appeared in the new hashes
hash1  | 4      -- company_id modified, (due to hash2)
hash7  | 4      -- company_id modified, (due to hash6)
hash8  | 4      -- company_id modified, (due to hash6)
hash17 | 4      -- added, new hash
hash18 | 4      -- added, new hash

I've done some research, it seems like a grouped auto-incrementing column is possible. Similarly, it seems like I can use the result of a previous statement in a subsequent query. I should also maybe look at transactions?

As of now, my workflow looks something like this:

Select all company_ids where the hash equals any of the existing hashes
Select the largest company_id from the hash table
If no company_id's exist from step 1, insert appropriate rows with an company_id of max(company_id) + 1
If one or more company_id exists, change all rows with those company_ids to an id of max(company_id) + 1
Perform steps 3 and 4 on any other tables that reference the company_id, writing most of the logic in PHP

As you can see, my method neglects MySQL almost entirely. I have a feeling I can use ON DUPLICATE KEY effectively here, and I'm wondering if there's a way to skip step 5. But regardless of if it's possible, does this database design make any sense? Part of me thinks I'm going to have to do a portion of this with PHP but I am trying to make this as efficient as possible (I will be handling a large amount of data with whatever method is chosen).


texts: How to "join" two tables without PRIMARY KEYs

texts:
I have two tables in which there are no primary nor foreign keys.

Table 1 (Persons):
name, region, department
Table 2 (Access):
name, access_level

How can I answer the following question:

  How many people in each department have data access?


texts: SQL Server Failover Cluster -- How to Fail Over Storage?

texts:
The picture helps explain what I am trying to do.
I know how to setup an SQL failover cluster, did it and it works perfectly.
Here is my situation : Node1 and Storage A is in one location (city) and Node 2 and Storage B is in a second location (different city).
Node 1 and Node 2 are connected to Storage A and Failover works perfectly.
NOW here is the catch.
How to setup so that if Storage A fails and I want to failover to Storage B.
I am wandering if its even possible since I have not found a single example on the net.


texts:
SQL newbie here. I currently have a 2-table database-"Customers" and "Bookings". This question concerns the "Booking" table, which has 3 attributes this question is about:

BookDate
BookStart
BookEnd

I'm trying to prevent the timeslots from overlapping. To do so, I've managed to bind the 3 attributes together into a composite key, which prevents identical entries from being entered. However, if 2 records like so are entered:

24/05/18 10:00am 10:15am

24/05/18 10:00am 10:30am

The records are accepted into the system. How do I prevent this? I'm trying to use data validation field right now (Help with preventing double bookings with variable lengths), but I'm not quite sure how to link 2 attributes together within it.

Thanks in advance!


texts:
I'm trying to find out if a particular index name "Test" exists in all databases using below code. The result I'm getting is always "NOT EXIST" for all databases in an instance. However, the index does indeed exist on few of the databases. Can someone please point out where the code is wrong?

DECLARE @Name AS VARCHAR(100)
DECLARE dbCur CURSOR FOR
SELECT name FROM SYS.databases where name not in ('master','msdb','model','tempdb')
OPEN dbCur
FETCH NEXT FROM dbCur
INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
--Check if index exist
   EXECUTE('USE [' + @Name + ']')
   If 1 = ((SELECT COUNT(*) as index_count
        FROM sys.indexes
        WHERE object_id = OBJECT_ID('dbo.TestTable')
        AND name = 'Test'))
    Print 'INDEX EXIST ' + @Name;
    ELSE
    Print 'INDEX DOES NOT EXIST ' + @Name;
   FETCH NEXT FROM dbCur
INTO @Name
END
CLOSE dbCur
DEALLOCATE dbCur

Result:

  INDEX DOES NOT EXIST DB1
  INDEX DOES NOT EXIST DB2
  INDEX DOES NOT EXIST DB3
  INDEX DOES NOT EXIST DB4


texts:
I'm looking for a way to make each SUM exclusive, i.e., it doesn't count one record more than once. You might think that with the way the date ranges are set up, it wouldn't count more than once, but in this particular database application the dates are very peculiar. TotalCount is correct, but I'm getting 2 additional records for Count1 and Count2. I have tried wrapping the SUMs in an outer CASE, but have not been able to get it to work. Any ideas?

select
 Sum(case   --'Before Admission'
 when ((EvalCheckboxDate < UnitArrivalTime) and (m_value<>'No' or m_value   is not null)) then 1 else 0
 end) as Count0,

 Sum(case   --'Within 24 Hours'
 when ((EvalCheckboxDate >= UnitArrivalTime and EvalCheckboxDate <= dateadd(hh,24,UnitArrivalTime)) and (m_value<>'No' or m_value is not null)) then 1 else 0
 end) as Count1,

 Sum(case  --'Between 24 and 48 Hours'
 when ((EvalCheckboxDate > dateadd(hh,24,UnitArrivalTime) and EvalCheckboxDate <= dateadd(hh,48,UnitArrivalTime)) and (m_value<>'No' or m_value is not null)) then 1 else 0
 end) as Count2,

 Sum(case   --'Greater than 48 Hours'
 when (EvalCheckboxDate > dateadd(hh,48,UnitArrivalTime) and (m_value<>'No' or m_value is not null)) then 1 else 0
 end) as Count3,

 Sum(case   --'Recorded as Not Done'
 when m_value='No' then 1 else 0
 end) as Count4,

 Sum(case   --'Not Recorded'
 when m_value is null then 1 else 0
 end) as Count5,
--
count(*) as TotalCount
from TFDataset


texts: Storing sports teams with multiple alternative names

texts:
I am designing a MySQL database to store some football match results and statistics in.
The data for populating the DB will be scraped from multiple sources and the names of the teams will be slightly different on each website. (Some have old names used before the team was rebranded and changed its name completely; some will have abbreviations such as United -> Utd; some will have FC in the name, some won't; other sources will have the names in a language different than English.)
I'm wondering what would be the best way to store that data.
My thinking is to use the TeamID in the main table and have an extra table as a dictionary for translating multiple variants of the team name into the ID. Of course it would be quite a hassle to populate such a "dictionary".
Example
TeamID | TeamName
-----------------------------
1      | Manchester United
1      | Man Utd
1      | MUFC
2      | PSG
2      | Paris Saint-Germain

This is actually a big problem with South American clubs, where one club can have five completely different names in various sources and it's not as easy as shortening United to Utd or abbreviating the name.
So I'll have a script scrape the name of a team, find it in the "dictionary" table, then write all the statistics and info into the main table using just the TeamID. That should also save some memory.
Please let me know if that's a good solution and if you have some different approaches for me.


texts:
React client is sending color to my server with this format
 {"r":1 "g":8 "b":5 "a":2}, for instance.

I'm not sure what is the best way to record color in MySQL.

I don't want to use this data in server. Just I want to send it to the client with the same format.


texts:
postgresql documentation says:

  All timezone-aware dates and times are stored internally in UTC. They
  are converted to local time in the zone specified by the timezone
  configuration parameter before being displayed to the client.

timezone parameter is a session parameter. When all TIMESTAMP WITH TIME ZONEs will be stored as UTC and the client connection will specify the session timezone, then what is the benefit of setting TimeZone parameter in postgresql.conf?


texts: Can I promote a slave node to be a master for insert/update/delete using Patroni once the original master goes down?


texts:
I need to trigger off 17 different columns and email the change from what it was to what it is changed to. The trigger below works but I feel like this is not the correct way to achieve this. Also, the email should only include fields that were changed. With this design all fields are in the email with "new" and "old" values even if the fields match. How do I remove if the values match?

ALTER TRIGGER [dbo].[Field_Change_Trigger]
ON [dbo].[Loan_Details]
after update
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @tableHTML NVARCHAR(MAX) = N'';
    DECLARE @subject varchar(255);
    DECLARE @recipients varchar(255);
    DECLARE @ID int;
    DECLARE @Status varchar(50);
    DECLARE @LoanAmount decimal(16,2);
    DECLARE @OldAmount decimal(16,2);
    DECLARE @APPDate date;
    DECLARE @OldApp date;
    DECLARE @FixedPymt decimal(16,2);
    DECLARE @OldPymt decimal(16,2);

    select  @LoanAmount = i.LOAN_AMOUNT, @OldAmount = d.LOAN_AMOUNT,
            @APPDate = i.DATE_OF_APPLICATION, @OldApp = d.DATE_OF_APPLICATION,
            @FixedPymt = i.FIXED_PYMT_AMOUNT, @OldPymt = d.FIXED_PYMT_AMOUNT
     from inserted i inner join deleted d on i.LOAN_ID = d.LOAN_ID

SET @tableHTML =

(SELECT   'Amount Changes:'   +  char(9) +'New: '+ convert(varchar(10),@LoanAmount)  + '  '  +  char(9) + 'Old: ' + CONVERT(varchar(10),@OldAmount) + '  ' + char(10) +
        + 'Application Date:' +  char(9) +'New: '+ CONVERT(varchar(50),@AppDate)     + '  '  +  char(9) + 'Old: ' + CONVERT(varchar(50),@OldApp)    + '  ' + char(10) +
        + 'Fixed Pymt Amount:'+  char(9) +'New: '+ CONVERT(varchar(50),@FixedPymt)   + '  '  +  char(9) + 'Old: ' + CONVERT(varchar(50),@OldPymt)   + '  ' + char(10))


texts:
In MySQL, hash indices are only supported by the MEMORY storage engine. By using this engine, data is stored in RAM, not on the hard disk, so it will be lost when MySQL is restarted.

Also, according to this link : hash index, it showed that hash indices are ineffective when data is stored on hard disk. But for practical usage for storing in RAM, Redis (other in-memory structures, .... ), may be a better choice than the MEMORY engine with MySQL.

So which use cases are valid for MEMORY storage engine and hash indices?


texts:
I need to apply a query hint (like NOWAIT or NOLOCK) to the CROSS APPLY in this query. How do I do it:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT *
FROM sys.objects obj WITH (NOLOCK)
INNER JOIN sys.stats stat WITH (NOLOCK) ON stat.object_id = obj.object_id
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) sp

I don't have access to the function contents (for obvious reasons.)
I went ahead and removed the repro from the question to make it more clear. I'm less concerned about the ability to reproduce the blocking. (It's also happening at customer environments, and in the First Responder Kit in issue # 2548.)


texts: Error on changing ownership of database from Files Page in database properties dialog box

texts:
I've restored a database and created a new user(same user from which I've logged in) as db_owner. Now when I wanted to see database diagram got an error

  Database Diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use files page of Database Properties dialog box or the Alter Authorization statement to set the database owner to a valid login, then add the database support objects

Now when I tried to change ownership from Files page of Database Properties I am getting error as

  Set Owner failed for Database 'ABC' . (Microsoft.SqlServer.SMO)
  Additional Info: an exception occurred in SMO
  The proposed new database owner James-PC\James is mapped as user James in this database.
  Parameter name: James-PC\James"

Please help me fix the issue so that I may be able to see database diagrams on this computer after restoring the database backup from another system.


texts:
As the title states, I'm getting the following error when attempting to use a ASP.NET webapp:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 192.168.x.x]

This is from the "Application" log on the SQL server. I currently have a clustered setup (using Microsoft Failover Cluster) although the second node has not been configured yet. I've tried configuring SPNs:

setspn -L myserviceaccount

Registered ServicePrincipalNames for <DN>:
        MSSQLSvc/*SQL.corp.*.local:1433
        MSSQLSvc/*SQL.corp.*.local

And indeed, SQL Server does register it correctly as per its logs:

Message
The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/my.sql.fqdn ] for the SQL Server service.

However, the query SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid ; returns NTLM and not Kerberos. I've restarted the server a few times to no avail. SQL Server runs under a domain user account with the appropriate delegation privileges.

Am I missing something?

UPDATE:

I was successfully able to get the SQL Server to use "Kerberos" as per the above query, but I'm still having no luck getting the IIS level to work.


texts: Error on data node startup in MySQL-cluster

texts:
Running a 24 core server (with HT enabled) I tried increasing MaxNoOfExecutionThreads to 24 to perform some benchmarks and got the following error:

2013-02-27 11:21:44 [ndbd] INFO     -- Trying to start 12 LQH workers with only 4 log parts, try initial node restart to be able to use more LQH workers.

Here are options from config.ini I find relevant:

NoOfFragmentLogFiles=64

FragmentLogFileSize=128M

It seems that Redo Log file number (not fragment) is always four and they are created in D9-D11 directory on the FS. I haven't been able to find a parameter to change this.

Version running is: 5.5.29-ndb-7.2.10

Any help or experience is welcome.

EDIT - solution:
The following option defines the number of Redo log files - NoOfFragmentLogParts

After setting it to 12, you get D8-D19 for Redo Logs and 24 as MaxNoOfExecutionThreads works fine.


texts: How to obtain a good execution plan for (LIKE @p) when the pattern is of the form 'prefix%'

texts:
I'm using an ORM that executes queries of the form:

SELECT Columns
FROM T
WHERE Col LIKE @p0 ESCAPE '~'

Col is indexed and the index is covering. The predicate is very selective. The tables has 500,000 rows.

I'm always querying for a string prefix (e.g. 'prefix%'). Clearly, this is not statically known to SQL Server here. But I know that SQL Server is basically capable of seeking based on a LIKE pattern with a prefix.

I would like SQL Server to discover at execution time the relevant range of the index to scan and only scan that range. The execution plan that I'm getting has an index scan, though.

The ORM does not support OPTION (RECOMPILE) and I'm not sure it would be a good option since the queries execute in about 20 milliseconds. (However I would like this query to go faster. With a seek this should be able to run in ~1ms).

Can I somehow make SQL Server perform a seek here?


texts:
Just trying to get some information on the best route to setup replication for this DB I've got. I'm  using MySQL 5.1

I found this and it seems like the idea we have in mind.

We're looking to have the Main Master be replicated by a Master/Slave and then a final machine replicating from the Master/Slave. That solution sounds wonderful and turning on --log-slave-updates on the Master/Slave machine sounds like what I want.

The question I have is when the Main Master(Master1) goes down we tell the services to write to Master/Slave(Master2) and then what happens with Master1? We rebuild it etc.. but where in the link does it go? Does Master/Slave(Master2) become the new Main Master? And does the Slave only machine get moved to Master/Slave?

Or does Master1 go down and gets rebuild and just becomes Master2(Master/Slave) keeping the Slave machine just that, a slave?

I feel like it's the latter of the 2 options. Just looking for some advice.

Thanks!


texts: Why is SQL Server 2012 working set memory low?

texts:
I have recently migrated a 100gb database for a busy website from:

MS SQL 2008 Standard Edition R2
Windows Server 2008
48gb RAM

to

MS SQL 2012 Web Edition
Windows Server 2012
64gb RAM

I have noticed that the usage of memory seems different in 2012, with the working set memory relatively low compared to the 2008 server (see screenshots below).

Question

Does this seem right, and should I be concerned? I want to be sure that MSSQL has access to as much memory as it needs. The usage profiles of the two servers, in terms of requests from the website they power, is the same, so the pattern or volume of queries has not changed.

SQL Server 2008

SQL Server 2012

SQL Server 2008 memory settings

SQL Server 2012 memory settings

Thanks for any tips.


texts: Is MySQL replication suitable for a Write Mostly application?

texts:
I'm considering adding another server to my one-server setup application with 600GB MySQL DB. The db and the apache are both running from the same server. In order to avoid backup downtime and improve performance, I'm considering to use the additional server as a replication of the db hoping that the write will happen to the master and all the reads will happen from the slave, make it work in parallel.

Is this suitable for an application with many writes? (70%)
I assume the write are more effective when done in bulks to the slave. Can I control this? How more effective are they?
Is there an easy option to do 'down-time' on one of the servers (e.g. backup) and return to the same system afterwards?


texts: Why isn't the transaction log getting truncated when taking a full backup?

texts:
In full recovery model, if we take a full backup, the log file is not truncated. It is done when we take a transaction log backup.

Any particular reason of why it is done this way?


texts: Select only those records that have same multiple values for a particular column if it's existing?

texts:
Below is an example of my pricing_supports table have more than 3 milions rows.
There some rows having same values in the first_origin_id, delivery_id fields.
If there're records with same first_origin_id, delivery_id but different source, I only want to select those records source = 0

ID   code first_origin_id    delivery_id    source
1      A       10                20              0
2      B       10                20              1
3      C       11                21              1
4      D       12                22              0
5      E       12                22              1

I would like result like that:

ID   code first_origin_id    delivery_id    source
1      A       10                20              0
3      C       11                21              1
4      D       12                22              0

How can i do for good performace?


texts: Command line export and import CSV in SQL Server 2008 R2

texts:
I have a database xyz and 100+ tables and has to perform the following operations on the command line:

I need to create CSV of all tables in one go.
CSV with column separator ,, fields enclosed by " " and lines terminated by \n.
Restore the all created CSV in one go.


texts: Unable to Connect to SQL Server 2005 through ODBC drivers from Windows 2012 server

texts:
We are unable to connect to a specific Database server which has SQL Server 2005 installed on Windows 2003 server from any of the Windows Server 2012. It used to work till last week, the only change we had on the 2012 server is the latest patches installed (KB4493451, KB4493450, & KB4493435). We have tried uninstalling these patches bit still no luck.

We are still able to connect from any Client system to Database server (SQL Server 2005) and also can login to SQL Server Management Studio which shows Client to Server communication is good, but the problem is with the Server (any Windows 2012 Server) to Database Server (SQL Server 2005).

We have SSL2.0, 3.0, & TLS 1.0 enabled on the DB Server and we have the default protocols SSL & TLS on Windows Server 2012 enabled.  We tried changing the values on the 2012 server for TLS, but still no luck.

  Connection failed: SQL State:'01000' SQL Server Error:1 [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (SECCreatedCredentails()). Connection failed: SQLState:'08001' SQL Server Error:18 [Microsoft][ODBC SQL Server Driver][[DBNETLIB]SSL Security error


texts: Using C#, is there a method to restore a database from a multi-file backup detecting which files are full vs diff?

texts:
I know there is functionality to restore databases in C#, however if I am given 2 or more files in a directory containing a full and differential backup files I would like to:

1) Determine which file is the full and which is the differential

2) Restore them in the correct order

Is there a straight-forward way to do this in C# .NET i.e. a function which accepts a list of files, automatically determines the order to restore them and does so?

Or do I have to create such functionality myself by reading the backup headers to determine the full vs diff and restore them that way.

I have seen articles like this:

https://stackoverflow.com/questions/25254269/using-smo-restore-class-when-there-are-multiple-backup-points-in-bak-file

And I can tell there should be a way to detect what kind of file the backup is:

https://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.backupsettype

But I just can't piece it all together.

Any ideas appreciated.
m


texts:
A frequent request I get from clients is to help figure out what queries or clients are making the largest contribution to SQL Server saturating an Ethernet link.

Ideally I'd look at some counters or DMVs showing network I/O aggregated by query, client, application, database, etc.

Since there doesn't appear to be a direct way of doing that, as a substitute I usually look at the *_rows columns in sys.dm_exec_query_stats (these columns exist in 2008 R2+ only, I think) and also look for queries that appear to involve BLOBs.

Is there a better way of approaching this problem?


texts: Concatenate one-to-many field in single result?

texts:
Say I have the following query:

SELECT *
FROM AppDetails, AppTags
WHERE AppDetails.AppID = '1'
  AND AppDetails.AppID = AppTags.AppID

Which gives the following results:

AppID    AppName        AppType    Tag
1        Application1   Utility    Test1
1        Application1   Utility    Test2
1        Application1   Utility    Test3

How can I modify the query to return something like this:

AppID    AppName        AppType    Tags
1        Application1   Utility    Test1,Test2,Test3


texts: How to merge rows based on how much data they have?

texts:
I am cleaning data and I formed a table something like this:

    file   |  product  |  color  | product_id
---------------------------------------------
  one.csv      pipe        blue       null
  one.csv      null        null       null
  one.csv      null        red        786
  one.csv      pipe        red        null
  two.csv      plate       null       null
  two.csv      pipe        null       null
  two.csv      plate       null       3333

In this table, null is a logical null, not a string 'null'.
From this table, I need to merge rows so that one row contain data from the other merged rows.

Here is some kind of algorithm:

Let's call the defined attribute for a single row, an attribute that has a value (is not null)
Let's call the common attributes for two rows those attributes that have the same value for each row, keeping in mind that null=null is null (they are not equal).

So, for the first two rows from the table, the common attribute is 'file'.
For the first and the fourth row are 'file' and 'product'

Two rows will be merged if one row have only common attributes, and the other one have more defined attributes (not null).

Result for merging the first and the second row is
('one.csv','pipe','blue',null).
Result for merging the fifth and the last row is
('two.csv','plate',null,'3333').
The third and the fourth row can not be merged, because they have other defined attributes beside the common attributes.

Final result is table:

    file   |  product  |  color  | product_id
---------------------------------------------
  one.csv      pipe        blue       null
  one.csv      null        red        786
  one.csv      pipe        red        null
  two.csv      pipe        null       null
  two.csv      plate       null       3333

Is there a query, a function or something else in PostgreSQL that I can use for my problem?

I know that there is a operator in database theory for that kind of operation, but I don't know how to search it because I am not native English speaker.


texts: Does a MyISAM table locks if I copy it while MySQL is running? (READ DISCLAIMER)

texts:
DISCLAIMER: I'm very aware this is not supposed to be done, but time consistency between tables aren't a concert here and Im trying whatever pops in my mind right now to have alternatives for punctual, on demand backups in complement to the more robust, scheduled ones.

Pretty much I want to know if there will be any reading problem while I copy a MyISAM table files (the .frm, .MYD, MYI) and it gets a transaction.

Most tables are small, so we could just take the risk, but there are a couple that worries me becuase of their size. I don't mind waiting for the transaction to get done, what worries me is getting a read error and failing to get a response from the query

That's about it, if you know something please let me know


texts: Why so many errors are displaying as "ERROR: current transaction is aborted, commands ignored until end of transaction block"

texts:
Production database has restarted unfortunately. When I gone through the logs, I have observed below error several times(More than 1,00,000 times).

"ERROR:  current transaction is aborted, commands ignored until end of transaction block"

How to avoid the above error?


texts:
I'm working in the following scenario: I'm creating an script that recreates an entire database.  The script involves destruction an recreation of the database and all its objects, as well as a great number of insertions from linked servers.
The idea is to be able to recreate the database at will until the development phase ends.  My problem is that the script generates high log activity, and soon a CHECKPOINT occurs.  I'm killing all connections in my script prior to drop the database, but as CHECKPOINT is a system process I can't kill it, and my script fails.
I don't need recovery for this database at this stage, my question is if I can disable the logs for this database to avoid the CHECKPOINT.


texts: Can SSIS packages call other SSIS packages in the connection string?

texts:
I am running an SSIS package that transfers data from one (SQL2008) server to another (SQL2000). However after P2V conversion the SQL2008 server cannot execute an SSIS package due to a user authentication error.
Lets say the package is called "Transfer-Go". In the connection string of that package can it call another SSIS package? In the SSIS library there is another package with the same that appears in the string (called Transfer-Now) name The string is below:
Data Source=<IP>;User ID=<user>;Initial Catalog=<db_name>;Provider=SQLNCLI10.1;Persist Security Info=True;OLE DB Services=-13;Auto Translate=False;Application Name=SSIS-<Transfer-Now-name of other SSIS package>-{8ABA18EE-637E-424F-A3F7-F7E4EA50DD9D}<IP.db_name.user>;

So is this SSIS package connection string calling that package?
And if the credentials are wrong in that package could that be why I am unable to authenticate?
Thanks for any input, not a DB/SQL guy at all so I apologize if I sound green here.


texts: Join select queries on an id in SQL Server

texts:
I have three tables that SELECT from and I'd need to join the results.

It feels like this should be a rather easy task but I struggle with the syntax.

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

What I came up with so far is..

TableA has id (int), name (varchar), comment (varchar) and extra (varchar).
TableB has id (int) and char (varchar)
TableC has eid (int) and info (varchar).

SQL query:

SELECT
    b.count, a.id, a.name, a.comment
FROM
    (SELECT *
     FROM TableA a
     WHERE id + 100 IN (SELECT distinct(eid)
                        FROM TableC
                        WHERE NOT eid IS NULL)
    )
JOIN
   (SELECT
        char, count(*) AS count
    FROM TableB b sel
    GROUP BY char) on a.id + 100 = b.char

What I'd like to have is the output of the top select (TableA) with the count of TableB where a.id+100 is b.char

Excuse my amateur-ish sql..


texts: sql server right join with condition returns rows that do not match condition

texts:
I'm having problem with my right join. Below is my query;

select a.maintitle as TITLE,
       a.id AS ID,
       a.a_auto_produ AS HOUSENO,
       c.CARRIERGUID,
       substring(d.FILENAME,1,15),
       d.CARRIERGUID
  from a
         inner join c on a.id = substring(c.FILENAME,1,15)
                         and  c.CARRIERGUID='xxx'
         right join d on a.id = substring(d.FILENAME,1,15)
                         and d.CARRIERGUID='production'  -- <<< here
;

with the condition indicated. I expect to have carrierguid = Production returned. However the returned rows also contain carrierguid other than production as you may see below;

really appreciate any help on this


texts: Who is using my worker threads? SQL Server 2014 - HADR

texts:
We had recently a problem on our SQL Server 2014 HADR environment, where one of the servers ran out of worker threads.

We got the message:

  The thread pool for AlwaysOn Availability Groups was unable to start a
  new worker thread because there are not enough avaiable worker
  threads.

I already opened another question, to get a statement which (I thought) should help me analyze the problem (Is it possible to see which SPID uses which scheduler (worker thread)?). Although I have now the query to find the threads which are using the system, I don't understand why that server ran out of worker threads.

Our environment is as follows:

4 Windows Server 2012 R2
SQL Server 2014 Enterprise
24 Processors --> 832 Worker threads
256 GB Ram
12 Availability Groups (overall)
642 Databases (overall)

So, the server which had the problem had the following configuration:

5 Availability Groups (3 Primary / 2 Secondary)
325 Databases (127 Primary / 198 Secondary)
MAXDOP = 8
Cost Threshold for Parallelism = 50
Power plan is set to "High performance"

To "resolve" the problem we manually failed one Availability Group over to the secondary server. The configuration of that server is now:

5 Availability Groups (2 Primary / 3 Secondary)
325 Databases (77 Primary / 248 Secondary)

I'm monitoring the available threads with this statement:

declare @max int
select @max = max_workers_count from sys.dm_os_sys_info

select
    @max as 'TotalThreads',
    sum(active_Workers_count) as 'CurrentThreads',
    @max - sum(active_Workers_count) as 'AvailableThreads',
    sum(runnable_tasks_count) as 'WorkersWaitingForCpu',
    sum(work_queue_count) as 'RequestWaitingForThreads' ,
    sum(current_workers_count) as 'AssociatedWorkers'
from
    sys.dm_os_Schedulers where status='VISIBLE ONLINE'

Normally the server has around 250 - 430 worker threads available, but when the issue started there were no workers left.

Today, out of nowhere, the available workers dropped from 327 down to 50, but only for a minute and went then back up to around 400.

I already saw the other question (HADR high worker thread usage) but it does not help me.

Our system ran stable for over a year without any problems. We haven't had any failover or other major change in the distribution of the databases.

We are using "Synchronous commit" between the replicas. From my understanding there is no compression involved, see Tune compression for availability group in the documentation.

Does any one have an idea what is using all the worker threads?

EDIT:
Found this page where there is a lot of information about exactly those issues
http://www.techdevops.com/Article.aspx?CID=24


texts: What is a good backup method for Azure SQL?

texts:
I maintain a Azure SQL database with the Basic plan. 7 days point in time restore is enough for our daily needs. What we are looking to improve is a daily backup outside Azure in the case that we lose access to our Azure subscription for whatever reason.

I have tried using SQLBackupAndFTP with snapshots enabled. Unfortunately it only works on the first backup and consecutive backups then fail. If I crate a new database I get the same behavior. I do not want to disable snapshots since I cannot guarantee that no write operations are done during the backup and I do not want the backup strategy to affect the availability of the web app using it.


texts: Order by query optimization for multi select data (enum)

texts:
We previously used to store multi select data (enum) using pipe separated characters, as shown below like this - which made ordering on that enum data very simple.

╔═══╦════════════╦═════════════╗
║   ║ ID         ║ Alphabets   ║
╠═══╬════════════╬═════════════╣
║ 1 ║ 1          ║ |A|B|C|     ║
║ 2 ║ 2          ║ |B|D|       ║
║ 3 ║ 3          ║ |A|C|D|     ║
║ 4 ║ 4          ║ |A|C|       ║
║ 5 ║ 5          ║ |A|B|D|     ║
╚═══╩════════════╩═════════════╝

This above structure was easier to sort on the stored "Alphabets" values but search was expensive since we had to search using %|A|%.

Now we are using the following structure to store the same above data since searching the data becomes much easier:

╔═══╦════════════╦═════════════╗
║   ║ ID         ║ Alphabets   ║
╠═══╬════════════╬═════════════╣
║ 1 ║ 1          ║ A           ║
║ 2 ║ 1          ║ B           ║
║ 3 ║ 1          ║ C           ║
║ 4 ║ 2          ║ B           ║
║ 5 ║ 2          ║ D           ║
║ 6 ║ 3          ║ A           ║
║ 7 ║ 3          ║ C           ║
║ 8 ║ 3          ║ D           ║
║ 9 ║ 4          ║ A           ║
║ 10║ 4          ║ C           ║
║ 11║ 5          ║ A           ║
║ 12║ 5          ║ B           ║
║ 13║ 5          ║ D           ║
╚═══╩════════════╩═════════════╝

Searching becomes easy now but when I try to sort the data it gives nightmares.
Can anyone give me a good idea to sort this above new data-structure using sql server so that I get the same sorted results as with the previous data-structure??


texts: Getting total number of counts for distinct entrys for the same id

texts:
I have a table which is like this.

CREATE TABLE `ad_analytics` (
  `id` int(90) NOT NULL,
  `ad_id` int(90) NOT NULL,
  `advertiser_id` int(90) NOT NULL,
  `publisher_id` int(90) NOT NULL,
  `visitor_ip` varchar(250) NOT NULL,
  `type_ad` varchar(90) NOT NULL,
  `impression` int(90) NOT NULL,
  `view` int(90) NOT NULL,
  `clicks` int(90) NOT NULL,
  `date_event` date NOT NULL,
  `placed_date` date NOT NULL,
  `ending_date` date NOT NULL,
  `cpc` int(60) NOT NULL,
  `cpv` int(60) NOT NULL,
  `cpi` int(60) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I want to count the number of impressions, views, and clicks for a particular id for distinct IP Aka if there are four impressions from the same IP then it should be counted as one impression for a particular IP. How can I do that?

Sample data.

INSERT INTO `ad_analytics` (`id`, `ad_id`, `advertiser_id`, `publisher_id`,
    `visitor_ip`, `type_ad`, `impression`, `view`, `clicks`,
    `date_event`, `placed_date`, `ending_date`, `cpc`, `cpv`, `cpi`)
VALUES
(34, 49, 113, 109, '::1', 'Video', 1, 0, 0, '2018-04-16', '0000-00-00', '0000-00-00', 0, 0, 0),
(35, 49, 113, 109, '::1', 'Video', 1, 0, 0, '2018-04-16', '0000-00-00', '0000-00-00', 0, 0, 0),
(36, 49, 113, 109, '::1', 'Video', 0, 0, 1, '2018-04-16', '0000-00-00', '0000-00-00', 0, 0, 0),
(37, 49, 113, 109, '::1', 'Video', 1, 0, 0, '2018-04-16', '0000-00-00', '0000-00-00', 0, 0, 0),
(38, 50, 113, 109, '::1', 'Video', 0, 0, 1, '2018-04-16', '0000-00-00', '0000-00-00', 0, 0, 0),
(38, 49, 113, 109, '::1', 'Video', 0, 0, 1, '2018-04-16', '0000-00-00', '0000-00-00', 0, 0, 0);

I am expecting the result like this.

ad_id  Impression views click
49       1         0      1
50       0         0      1


texts: How did this statement update 3 rows in Postgres?

texts:
I am curious how this statement updated 3 rows in Postgres. All the other times I ran it, it would update 0 or 1. Is there a way to find out which rows?

bestsales=# update keyword set revenue = random()*10 where id = cast(random()*99999 as int);
UPDATE 3

id is the primary key.

 id               | integer                        | not null default nextval('keyword_id_seq'::regclass)
    "keyword_pkey" PRIMARY KEY, btree (id)

I tried to run it as SELECT:

bestsales=# select * from keyword where id = cast(random()*99999 as int);
  id   |       keyword       | seed_id | source | search_count | country | language | volume | cpc  | competition | modified_on | google_violation | revenue | bing_violation
-------+---------------------+---------+--------+--------------+---------+----------+--------+------+-------------+-------------+------------------+---------+----------------
  6833 | vizio m190mv        |         | GOOGLE |            0 |         |          |     70 | 0.38 |        0.90 |             |                  |         |
 65765 | shiatsu massage mat |         | SPYFU  |            0 |         |          |    110 | 0.69 |             |             |                  |         |
 87998 | granary flour       |         | SPYFU  |            0 |         |          |     40 | 0.04 |             |             |                  |         |
(3 rows)

And sometimes it would return more than one. How is that possible?

PostgreSQL 9.5.3


texts: mysql trigger / stored procedure - update another table row by row

texts:
I've already, searched and read, many answers about this issue for 3 days, but couldn't get a clear answer on how to do this.

this will be a little long, be patient please.

First of all, I am trying to make a simple lottery app. I hope everyone of you know about the 6/49 lottery thing.

Well, according to game, first, for a week customers play lottery (lotteryTBL). Then 6 numbers between 1-49 are selected as lucky numbers (resultsTBL).

What I want to do is updating lotteryTBL after a new result row is inserted into resultsTBL.

Structure of lotteryTBL:

ID    num1  num2  num3  num4  num5  num6     draw_date      howmanyknew
1       5    6     8    15      18    25     08-21-2015         0
2       7    15    18   30      40    45     08-21-2015         0

Structure of resultsTBL:

ID    num1  num2  num3  num4  num5  num6     draw_date
1      15    18    35    38    40    47     08-21-2015

Now, here is the pseudocode of what i want:

after insert a new row into resultsTBL
   total <- 0
   for each row in the lotteryTBL where lotteryTBL.draw_date = resultsTBL.draw_date
       total <- calculate how many of them are the same with the resultsTBL's row.
   update lotteryTBL set howmanyknew = total where "some condition"

First, I tried to do it with trigger, some said "it is dynamic sql, you cannot use trigger", some said "use stored procedure". And I did it too. But, they never worked.

Anyway, Can someone type a good pseudocode for this? which method do I have to use? what is the logic here?


texts: Why is a BIGINT index larger than a CHAR 13 index?

texts:
I have a MySQL DB, with a MyISAM table with 5 million records, and several fields. 1 of the fields is a BIG INT, which I understand to be 8 bytes, and another is a CHAR 13, which I thought was a byte per char.

I notice that when adding a index to each field though, the index file increases by 51MB on the CHAR 13, and by 81MB on the BIGINT.

Based on byte site of each field I expected the BIG INT to be noticeably smaller, but in fact the difference is opposite and bigger than I expected.

Why is that?

CREATE TABLE `msisdns_key` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `key` mediumint(6) unsigned NOT NULL DEFAULT '0',
  `key2` smallint(5) unsigned NOT NULL DEFAULT '0',
  `msisdn_big` bigint(20) unsigned NOT NULL,
  `msisdn_bit` binary(20) NOT NULL,
  `msisdn` char(13) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `key` (`key`),
  KEY `key2` (`key2`),
  KEY `msisdn_bit` (`msisdn_bit`(3)),
  KEY `msisdn` (`msisdn`),
  KEY `msisdn_big` (`msisdn_big`)
) ENGINE=MyISAM AUTO_INCREMENT=6745305 DEFAULT CHARSET=latin1


texts: Is it okay to put dots in SQL Server database names?

texts: If I use a database name with a dot in it in SQL Server (2005 or 2008), something like "MyApp.Sales", will this cause any problems?


texts: Estimating bandwidth required for streaming replication

texts: I have an existing postgresql server that I would like to replicate to another location using streaming replication.  How can I best estimate the amount of bandwidth that will be required to keep the databases reasonably synchronized, using the actual workload I have now?  I've read through the pg_stat_* list but nothing jumps out at me for an overall transaction rate or amount of data written (except for pg_stat_bgwriter, which has totals since the server was last started).  Would the number of buffers (which I believe are 8kB each) written according to the checkpoint log be in the correct ballpark?


texts: Replication between two different SQL Server versions

texts:
We have mirroring configured (transactional replication) in our primary and secondary SQL Servers, which are running on Windows 2008 R2 SP2 OS with 2008 R2 SP3 as the SQL Server version.

If we first upgrade the secondary server, we have to figure out how the synchronization between the two servers has to be configured, as for some time they will be running with two different versions of SQL Server.

We want to upgrade them to SQL Server 2014.


texts: Is it better to separate a big query into multiple smaller queries?

texts:
There are situations which require having really big query joining several tables together with sub select statements in them to produce the desired results.

My question is, should we consider using multiple smaller queries and bring the logical operations into the application layer by querying the DB in more than one calls or it's better to have them all in one go?
For example consider the following query:

SELECT *
FROM   `users`
WHERE  `user_id` IN (SELECT f2.`friend_user_id`
                     FROM   `friends` AS f1
                            INNER JOIN `friends` AS f2
                              ON f1.`friend_user_id` = f2.`user_id`
                     WHERE  f2.`is_page` = 0
                            AND f1.`user_id` = "%1$d"
                            AND f2.`friend_user_id` != "%1$d"
                            AND f2.`friend_user_id` NOT IN (SELECT `friend_user_id`
                                                            FROM   `friends`
                                                            WHERE  `user_id` = "%1$d"))
       AND `user_id` NOT IN (SELECT `user_id`
                             FROM   `friend_requests`
                             WHERE  `friend_user_id` = "%1$d")
       AND `user_image` IS NOT NULL
ORDER  BY RAND()
LIMIT %2$d

What's the best way of doing it?


texts: SQL Server - Migration script testing - Refresh table definition manually without dropping it

texts:
How can a table definition be manually refreshed without also manually crafting a bunch of ALTER TABLE statements, or dropping and recreating the table?

This is normally handled by the publish process (e.g. SSDT), but during testing of schema alterations and their migration scripts, I need to revert the table definitions, and the managed rebuild/publish process through Visual Studio (and getting specific versions from TFS) takes quite a long time.

For example, if I have this new version of the table after updating the schema

CREATE TABLE [dbo].[MyAssociativeTable]
(
    [SurrogateID]   INT NOT NULL IDENTITY(1, 1)
    ,[ForeignID1]   INT NOT NULL FOREIGN KEY REFERENCES [MyOtherTable1]([ID])
    ,[Flag1]        BIT NOT NULL CONSTRAINT [DF_MyAssociativeTable_Flag1] DEFAULT(0)
    ,[ForeignID2]   INT NOT NULL FOREIGN KEY REFERENCES [MyOtherTable2]([ID])
    ,[Flag2]        BIT NOT NULL CONSTRAINT [DF_MyAssociativeTable_Flag2] DEFAULT(0)
    ,[DataColumn1]  REAL NULL CONSTRAINT [CK_MyAssociativeTable_Data1] CHECK(ISNULL([DataColumn1], 0) >= 0)
    ,[ExtraColumn1] VARCHAR(MAX) NULL
    ,CONSTRAINT [PK_MyAssociativeTable] PRIMARY KEY
    (
        [ForeignKey1]
        ,[ForeignKey2]
    )
    ,CONSTRAINT [CK_MyAssociativeTable_Case1] CHECK
    (
        [Flag1] = 0
        OR [Flag2] = 0
    )
)
GO
CREATE UNIQUE INDEX [AK_MyAssociativeTable_F1]
ON [dbo].[MyAssociativeTable]([ForeignID1])
WHERE [Flag1] = 1

and I want to revert it to the original version (removing ExtraColumn1 is ok) so I can insert invalid rows to test migration scripting on

CREATE TABLE [dbo].[MyAssociativeTable]
(
    [SurrogateID]   INT IDENTITY(1, 1)
    ,[ForeignID1]   INT
    ,[ForeignID2]   INT
    ,[Flag1]        BIT
    ,[Flag2]        BIT
    ,[DataColumn1]  REAL
    ,PRIMARY KEY([SurrogateID])
)

what are my options?

Note that columns are also being re-ordered. Handling this would be nice, but is unimportant.


texts: Monitoring decimal values on Perfmon using User Settables

texts:
I am currently using sp_user_counter1 to store database information on Perfmon, which can be recovered by AWS CloudWatch, but the values are Decimal, and the sp_user_counter1 parameter supplied must be an int.

So when I give the stored procedure a value of 12.34567 it report 12.00000 to the perfmon counter....

The sp code is:

    CREATE PROCEDURE sys.sp_user_counter1 @newvalue INT AS
    DBCC setinstance ('SQLServer:User Settable', 'Query', 'User counter 1', @newvalue)

Can I just call DBCC setinstance and give it the decimal value???
I never tried this because I didnt found anyone doing this, I dont know the consequences....

Can this be done?? Is this secure??

Here is the User Setabble documentation:
https://msdn.microsoft.com/en-us/library/ms187480.aspx


texts:
I am wanting to perform the below query that essentially shows where a calculation is >= .70 or <= -.70 However, I get an error of

  Msg 147, Level 15, State 1, Line 8
  An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Here is sample DDL - what is the appropriate way to write this query?

    Declare @Hamburger Table (empName varchar(200), e float, a float, saleid varchar(10))
Insert Into @Hamburger (empName, e,a, saleid) Values
('Jake', 362, 233, 'E111'), ('Jake', 1431, 2702, 'E112'), ('Blue', 849, 280, 'R222'), ('Blue', 1418, 299, 'R390')

Select empName, e, a, saleid, AmtGained = Sum(Coalesce(e,0))-Sum(Coalesce(a,0)),
DiscountPercent = Sum(Coalesce(e,0)-Coalesce(a,0))/NullIf(Sum(Coalesce(e,0)),0)
From @Hamburger
where ((Sum(Coalesce(e,0)-Coalesce(a,0))/NullIf(Sum(Coalesce(e,0)),0) >= .70)
OR (Sum(Coalesce(e,0)-Coalesce(a,0))/NullIf(Sum(Coalesce(e,0)),0) <= -.70))
Group By empName, saleid, e, a
Order By empName ASC


texts: Does PostgreSQL benefit from more cores or faster cores for READ heavy applications?

texts:
I have been trying to figure out how to go about configuration of a PostgreSQL instance that will have 64GB ram (which is far larger than the database) and if I should opt for more cores or faster cores.(Assuming passmark total is within 15-20% difference)

Some extra details:
The most intensive READ will be JSONB through lots of UNION. The OS in this case is CentOS 6.


texts:
What are the considerations needed when converting myisam to innodb.

I have a table around 80 GB with engine myisam. I would like to convert into innodb. What are the considerations needed to convert.

I'm using percona  MySQL. I checked the table indexes and there is no full text searching indexes for the above table.

I plan to use percona online scheme change.

What are the considerations/pre-requisite to keep in mind to do this activity?


texts:
I have the following tables

R1(A, B, C) with 1.000 rows

R2(C, D, E) with 1.500 rows

R3(E, F) with 750 rows

where a bold letter denotes a primary key.

I need to estimate the number of rows of the natural join R1 |x| R2 |x| R3.

My textbook has proposed the following solution

The Natural join of R1, R2 and R3 will be the same no matter which way we join them (join is both associative and commutative). Size can be estimated using the strategy of first joining R1 and R2, and then joining the result with R3.
Joining R1 with R2 will yield a table of at most 1.000 rows, since C is a key for R2. Likewise, joining that result with R3 will yield a table of at most 1.000 rows because E is a key for R3. Therefore the final relation will have at most 1.000 rows!

I would have thought that the final relation will have at most 750 rows, since R3 only has 750 rows.

Is the textbook solution incorrect, or am I missing something?


texts:
I perform oracle database backup using the following rman command:

RUN {
    ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT 'C:\Windows\TEMP\%u';
    ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT 'C:\Windows\TEMP\%u';
    BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG TAG MyTAG;
}

But unfortunately MyTag is applied only for archive logs BS.
The query list backup; return 6 rows:

BS of archive logs;
BS of data files;
BS of data files;
BS of data spfile;
BS of data controlfile;
BS of archive logs;

The query list backup summary tag 'MyTag'; return only 2 rows

BS of archive logs;
BS of archive logs;

How to apply TagName for all backup files ?


texts: Is there a way to ensure that a SQL Server trigger will be executed?

texts: My supervisor is hesitant to use a trigger in a process because if there is a network interruption at just the right time, the process would not complete.  Does SQL Server include the trigger in the calling procedure's transaction?  If not, what is the best way to implement this?


texts:
I have a given topic and I want to retrieve its similar topics based on topic keywords.

The schema is as follows:

CREATE TABLE `topic_features` (
  `topic_id` int(11) NOT NULL,
  `feature_id` int(11) NOT NULL,
  `weight` float DEFAULT '0',
  PRIMARY KEY (`topic_id`,`feature_id`),
  KEY `feature_id_cover_idx` (`feature_id`,`topic_id`,`weight`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

topic_features table contains topic keywords and their weights

Given a topic id I follow this scenario :

I retrive the top 50 keywords of the given topic ordered by weight.
Then I retrieve all the topics sharing at least one keyword of the
previous keywords.
Calculate the similarity between each topic and the given one, based
on the common keywords between the two topics, the formula is as
follows :

SIMILARITY(t,tx) = SUM_FOR_EACH_COMMON_FEATURE(feature_weight(t) *  feature_weight(tx)) where tx and t share a feature

The query I use currently and which is performming bad is :

SELECT
  tf1.topic_id,
  SUM(tf1.weight * tf2.weight) similarity
FROM topic_features tf1
  JOIN
  (
    -- get only top 50 features for the current topic
    SELECT
      feature_id,
      weight
    FROM topic_features
    WHERE topic_id = 124897
    ORDER BY weight DESC
    LIMIT 50
  ) tf2
  USING (feature_id)
 GROUP BY tf1.topic_id
  ORDER BY similarity DESC
LIMIT 20;

Query Explain Plan

+----+-------------+---------------------+------+----------------------+----------------------+---------+----------------+-----------+---------------------------------+

| id | select_type | table               | type | possible_keys        | key                  | key_len | ref            | rows      | Extra                           |

+----+-------------+---------------------+------+----------------------+----------------------+---------+----------------+-----------+---------------------------------+

|  1 | PRIMARY     | <derived2>          | ALL  | NULL                 | NULL                 | NULL    | NULL           |        50 | Using temporary; Using filesort |

|  1 | PRIMARY     | tf1                 | ref  | feature_id_cover_idx | feature_id_cover_idx | 4       | tf2.feature_id |         5 | Using index                     |

|  2 | DERIVED     | topic_features      | ALL  | PRIMARY              | PRIMARY              | 4       |                | 462221416 | Using where; Using filesort     |

+----+-------------+---------------------+------+----------------------+----------------------+---------+----------------+-----------+---------------------------------

Is there a way to improve the performance of this query? I was thinking about creating another table which will hold the sum of the weights between each two topics and which will be updated periodically, but I don't know if it is a good idea as I have about 2M topics and 300M topic_features and the update will take a lot of time.


texts: SQLNCLI10.x vs 11.x when connecting SSIS 2008R2 to a SQL Server 2012 database

texts:
Starting from the question and working backwards.

My question: is it OK to use SQLNCLI10.1 as a provider for SSIS 2008 R2 to connect to SQL Server 2012 database(s)?

I have a SSIS box running SQL Server & SSIS 2008 R2. The SSIS packages connect variously to different data sources including different SQL Server databases, and some of these databases are on SQL Server 2012. In line with the general principle that you would use the right provider for the source you are connecting to, I would expect that the "right" choices are SQLNCLI11 to connect to SQL Server 2012, and SQLNCLI10 to connect to SQL Server 2008 & 2008 R2.

However using SQLNCLI10.1 connects fine to SQL Server 2012 and appears to be fully functional and bug free.

Couple this with these other factors:

The SSIS box I refer to is one element of the dev lifecycle so to change this box means also installing v11 on several other boxes.
The added complexity of having to choose the right provider for the right DB Connection instead of a single SQLNCLI (there are no SQL Server 2000 / 2005 sources to worry about).  Especially since the ConnMgr doesn't allow you to change it once set.
Issues such as this which imply there are greater risks in using 11 over 10.

I realise open or discussion type questions are frowned upon, so, Q&A:

Are there any RISKS in staying using SQLNCLI10 to connect from SQL Server 2008 R2 (SSIS) to SQL Server 2012?
Would there be any performance loss for the same, over using v11 instead?
Am I losing functionality or risking breakage by using 10.1 instead of 11.x?

Thanks.


texts:
I have created a few events in my db. One is to daily create partition. Another is to summarize data on every second from another table. I notice everything is not running. What would have caused the scheduler to stop? The db is fine working on other stuff without issue.

Below is my mysqladmin debug results.

Status information:

Current dir: /var/lib/mysql/
Running threads: 2  Stack size: 262144
Current locks:
lock: 0x7f71f0014570:

lock: 0x7f71f001f400:

lock: 0x7f71f800c530:

lock: 0x7f71f800da40:

lock: 0x7f71f8016dc0:

lock: 0x7f71f8014310:

lock: 0x7f71f80116f0:

lock: 0x7f71f802f6c0:

lock: 0x7f71f802f060:

lock: 0x7f71f8021000:

lock: 0x7f71f8026c60:

lock: 0x7f71f80251a0:

lock: 0x7f71f80226a0:

lock: 0x338adb8:

lock: 0x3380088:

lock: 0x33754e8:

lock: 0x336c5a8:

lock: 0x3361de8:

lock: 0x3352908:

lock: 0x334ca48:

lock: 0x3344da8:

Key caches:
default
Buffer_size:       8384512
Block_size:           1024
Division_limit:        100
Age_limit:             300
blocks used:             2
not flushed:             0
w_requests:              0
writes:                  0
r_requests:           2560
reads:                   2

handler status:
read_key:         3670
read_next:       56069
read_rnd            82
read_first:          5
write:             952
delete               0
update:           1649

Table status:
Opened tables:         29
Open tables:           22
Open files:            17
Open streams:           0

Alarm status:
Active alarms:   0
Max used alarms: 2
Next alarm time: 0

Begin safemalloc memory dump:

End safemalloc memory dump.


texts: When to use stored procedure / user-defined function?

texts:
I'm confused about usage of SP (stored procedures) and UDF (user defined functions). Typically, it is also possible to write code in programs outside of the database.
Is there any general advice to decide when to use them?


texts: Select consecutive number of rows starting from some number

texts:
What I need is to select 100 consecutive rows from a table, starting at some defined point without ordering data.

As far as I can observe, inserted data to a table is stored in no order but is retrieved by identical SELECT statements in the same order.

I could get use of 100 random rows, but random rows are not useful because I always need to retrieve the same data from the database in order to compare results of the algorithm I'm developing.

I don't want to order data by any field because otherwise I'd be biasing the selected data.

The sql statement should be something like:

SELECT field1, field2 FROM table1 WHERE row_number > 70000 LIMIT 100;

or probably even better (as I think LIMIT doesn't prevent the database from outputting every record above row number 70000):

SELECT field1, field2 FROM table1 WHERE row_number > 70000 AND row_number <= 70100;

What SELECT statement should I use?


texts: The built in SA login on a server has full access too all databases. Is there a way to have a login, that has read-only access to all databases, current and future databases? So like sa, but with readonly access?


texts:
I run a bad query on my production server and I need to perform a restore of my database.

I had a full backup (20160828) and a diff backup (29,30,31 and 01/09) and I had transaction log backup.

Is it necessary to restore all the diff backups or only the last one?

Is  it true that all restore operation I have to do must be in non recovery (the full, all the diff and all the transactionlog) except the last transaction log backup which must be restore with recovery?

Thanks in advance


texts:
I have been using AlwaysON AG for a long time now and currently have about 10TB of data across 120 databases and 3 AG groups for any application that is on SQL 2012 with great success. Each AG group is running on patch level 11.0.5058.0 with 2 synchronous replica(on different SANS) in Primary Data center and 1 ASYNC replica in DR. Migration has been a non-issue because none of the databases were substantial enough that I could not fit into my maintenance window which is 12-4AM on SAT morning.

My issue is that my last application to migrate to 2012 includes a 4TB TDE encrypted databases database which is about 10x larger than any of the previous ones I have migrated. The database takes 4 hours to backup after tuning extensively(I hate TDE!!)

The restore to the primary replica is instant because of seeding incremental but the issue comes from having to backup the database before adding to the availability group. 4 hours is my exact outage window and I can't get any more.

My plan to migrate application is to -

First Outage Window

Restore Database from 2008 to 2012 Primary Replica
Change application ARECORD(or cname not sure which) to Primary replica
Run database on single node until next outage window

Week Later :

Add database to availability group
Change ARECORD/CNAME to listener

What I don't like about this is I am going an entire week with 1 node instead of 3 which is worrisome. If anyone else has any better ideas on how to accomplish this I would love to hear from you or any type of comment from people who have worked with VLDB in availability groups and what you like/hate/loved about doing it. I am trying to go all in on this software and have loved it so far but getting worried when it comes to the VLDB migration.


texts: Convert seconds into HH:MM:SS format in MySQL

texts:
I have a query where I'm pulling data from the Zultys VoIP Database, about employees and their call in/call out numbers, and the time they spent over the phone: Time in/Time out in seconds, and their phone extensions as well.

The query is below:

SELECT
    IFNULL(mxuser.EXTENSION, mxuser_1.EXTENSION) AS Ext,
    SUM(IF(TRUNKGROUPNAME2 != 'itsp.Nextiva',
        IF(SESSIONIDTRANSFEREDTO IS NULL,
            Duration,
            HoldTimeSecs),
        0)) AS TimeIn,
    SUM(IF(TRUNKGROUPNAME2 = 'itsp.Nextiva',
        IF(SESSIONIDTRANSFEREDTO IS NULL,
            Duration,
            HoldTimeSecs),
        0)) AS TimeOut,
    SUM(IF(TRUNKGROUPNAME2 != 'itsp.Nextiva',
        1,
        0)) AS CallIn,
    SUM(IF(TRUNKGROUPNAME2 = 'itsp.Nextiva',
        1,
        0)) AS CallOut
FROM
    ((session
    INNER JOIN callsummary ON session.NOTABLECALLID = callsummary.NOTABLECALLID
        AND DATEDIFF(CURDATE(), STARTTIME) = 1)
    LEFT JOIN mxuser ON session.EXTENSIONID1 = mxuser.EXTENSIONID)
        LEFT JOIN
    mxuser AS mxuser_1 ON session.EXTENSIONID2 = mxuser_1.EXTENSIONID
WHERE
    SESSIONTYPE = 1 AND ANSWERED = 1
        AND (DIALPLANNAME NOT LIKE 'Local %'
        AND TRUNKGROUPNAME1 LIKE 'dev.%'
        AND TRUNKGROUPNAME2 = 'itsp.Nextiva')
        OR (TRUNKGROUPNAME1 = 'itsp.Nextiva'
        AND TRUNKGROUPNAME2 LIKE 'dev.%'
        AND CALLINGPARTYNO NOT LIKE '404%'
        AND CALLINGPARTYNO NOT LIKE '678%'
        AND CALLINGPARTYNO NOT LIKE '770%')
GROUP BY IFNULL(mxuser.EXTENSION, mxuser_1.EXTENSION)
HAVING Ext IS NOT NULL;

When I run this query above I get this result:

What I'm trying to achieve is to add TimeIn and TimeOut as total time and to convert seconds to this format HH:MM:SS.

Any thoughts?


texts:
Is there any general official Microsoft guide for planning SQL Server hardware? For instance, if you plan to have 500 concurrent сonnections you should have minimum 4 cores, 256GB of RAM, 15K HDD.

All I have managed to find on MS website is specific for products like SharePoint and so on...

Will appreciate valuable links or critic.


texts: Do I want an index if all I care to query is field not null

texts:
In SQL, do all the common considerations about indexes apply if everything I'll ever want to query (in the where condition) is when a field is not null?

Is there a special configuration of the index I can apply to cover this specific case?

Eg:

select id
from Accesses
where token is not null;

Is it worth to add the index on token as much as it would be if I had to search for specific token values?

My database is Postgres.


texts: "Diamond Shaped Relationship" What's the formal term?

texts:
Consider the following "Diamond-Shaped" entity relationship diagram:

I'm trying to find some formal readings on why this is a bad design - but Googling "Diamond Shaped Table Relationship" only turns up the ERD symbol.

What is this relationship called? Why is it a bad table design (if it is)?


texts: SQL Server one-to-many and index fragmentation

texts:
I'm currently writing code to update an entities child collection (one-to-many relationship), and while thinking about how to write code to determine which entities have been added/removed/modified, I realized that not only is it easier to just  recreate the whole list, but probably better for SQL server performance?

Let's say I have a table Student with a one-to-many relationship to Course, and I have there 2 courses Math and Physics, with primary keys 1 and 2 respectively, and obviously a foreign key to Student which is 1.

If I one day decide to update the first course, remove the other, and add a new one, I would end up with the following entities

Math (upated), primary key is: 1
Physics (deleted), primary key was: 2
History (added), primary key is: 5000

Now the 2 courses are no longer next to each other, and this probably causes big performance problems in the long run since my keys are now fragmented, is this correct?

A related question is whether I should have a primary key on the Course table at all? I usually add it by default to all of my (except association) tables, but in this case I never query courses individually, but always in the context of a student. Does it make sense to keep the primary key, even if I know I'll never reference it from anywhere? Would the lack of a primary key hurt performance?

EDIT: I've decided to keep the primary key, in-case in the future I do need to query this child table without the parent.

My primary keys are integers and have a clustered index (default on SQL Server) and I'm using SQL Server 2016, and Entity Framework 6 as my ORM.

UPDATE:

Here's my CREATE TABLE from SSMS. I'm using EF Code-First so I didn't write any of this by hand.

CREATE TABLE [dbo].[Courses](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Description] [nvarchar](max) NOT NULL,
    [StudentId] [uniqueidentifier] NOT NULL,
    [Fk1Id] [int] NOT NULL,
    [Fk2Id] [int] NOT NULL,
    [Fk3Id] [int] NULL,
 CONSTRAINT [PK_dbo.Courses] PRIMARY KEY CLUSTERED
 (
     [Id] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Courses]  WITH CHECK ADD  CONSTRAINT [FK_dbo.Courses_dbo.Students_StudentId] FOREIGN KEY([StudentId])
REFERENCES [dbo].[Students] ([Id])
GO

ALTER TABLE [dbo].[Courses] CHECK CONSTRAINT [FK_dbo.Courses_dbo.Students_StudentId]
GO
--Omitted other alter tables for Fk1Id,Fk2Id, and Fk3Id

StudentId is the foreign key that I will always use to fetch these rows, they are not relevant in any other context (at least for now).

I have renamed the table and column names for privacy reasons, and removed 3 foreign key columns for simplicity! I hope they're not too relevant.


texts: SQL filter only if each unique value has more than N records

texts:
Here is my sample SQL statement:

SELECT DAY,
       name,
       value
FROM my_table
WHERE DAY = '${date}'
GROUP BY DAY
         name,
         value
ORDER BY name ASC

For example, 3 unique names in 'name' column: Alice, Bob, Clark.

Alice has 5 rows, Bob has 9 rows, Clark has 12 rows.

I want to add a filter if rows with same value > 10 rows. 'Clark' satisfies in this case.

How to add this? under WHERE?


texts:
I've been running a pretty high pop website for a few months new and as its growing we've been having some problems lately.

Its a 2 core Xeon, 8GB, SSD server.
We're pulling +- 150k people a day.

Its a lightning fast wordpress stack and up to about 450 concurrent (200 pageviews a minute) it remains lightning fast.

If it goes beyond that, the load goes INSANE, from 1.5 to 20 or 30!
I've been trying to fine tune my.cfg but I'm about to give up, there is LOADS of information which is very varying, so I'm starting to get confused.

Here is my MySQLTuner from the last 4 days.

As you will see, it tells me to increase query cache, doing so however absolutely destroys my CPU even more and a lot earlier than when it remains at 8MB...

We also converted all tables to InnoDB and saw a big performance gain that way in the past.

I hope I can get some insight from professionals. I would even pay for some one on one time if needed.

Thank you!


texts: VS 2012 SSIS Data Flow Designer Source Assistant Error: [SSIS.Pipeline]: Component ", clsid {4CAC ... Could not be created

texts:
I am creating my first SSIS package to support the E of our ETL from an external Oracle database to our internal SQL Server 2012 databse.
Using Visual Studio 2012 and I have installed the Microsoft Oracle Connection Manager by Attunity 2.0
I had this working, then renamed and deleted some files, and tried to build it again when I ran into this problem.
When running Source Assistant, I receive the Visual Studio Error:

The component could not be added to the Data Flow task.  Could not initialize the component.  There is a potential problem in the ProvideComponentProperties method.

There is an "Additional Information" section that references:

Error at TERM [SSIS.Pipeline]: Component ", clsid {4CAC6073-BCA7-430E-BD29-68A9F0012C6D}" could not be created and returned error code 0x80070005 "Access is denied.", Make sure that the component is registered correctly.
Error at TERM [ [3]]: The component is missing, not registered, not upgradeable, or missing required interfaces.  The contact information for this copmonent is "".

Note: "TERM" is the name of the Data Flow task (named after the table I want to copy with SSIS.
This clearly looks like registry damage to me, so I had uninstalled the Attunity copmnent and re-installed it; and I ran a repair on Visual Studio 2012 (in that order); rebooted, etc.
No luck so far.
Anyone ever seen this before and/or have any ideas how to get back to a working SSIS development?
I am working on a development box, my Oracle Connection tests fine, and I can also connect fine to my SQL Server 2012 instance as well.


texts: How to backup an Oracle database from SQL Developer

texts:
New to Oracle, usually work with SQL Server.

Is there an easy way to backup an Oracle database from SQL Developer.

I've read these links Database Backup and Recovery User's Guide and RMAN and it's a CRAZY involved process!  There's gotta be an easier way, right?


texts: Issue with table naming policy with PBM - can't create it passed in the condition or not

texts:
I used PBM to create a condition to blocking create tables if nomenclature no haven't the prefix tbl:

EXEC msdb.dbo.sp_syspolicy_update_condition @condition_id=24, @description=N'Necessário acrescentar o prefixo ''tbl''', @facet=N'IMultipartNameFacet', @expression=N'<Operator>
  <TypeClass>Bool</TypeClass>
  <OpType>LIKE</OpType>
  <Count>2</Count>
  <Attribute>
    <TypeClass>String</TypeClass>
    <Name>Name</Name>
  </Attribute>
  <Constant>
    <TypeClass>String</TypeClass>
    <ObjType>System.String</ObjType>
    <Value>tbl%</Value>
  </Constant>
</Operator>', @is_name_condition=2, @obj_name=N'tbl%'
GO

I applied this condition into my tables in the DB "cadastro"

EXEC msdb.dbo.sp_syspolicy_update_target_set @target_set_id=65, @enabled=False
EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=65, @type_skeleton=N'Server/Database/Sequence', @condition_name=N''
EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=65, @type_skeleton=N'Server/Database', @condition_name=N''

...

EXEC msdb.dbo.sp_syspolicy_update_target_set @target_set_id=72, @enabled=False
EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=72, @type_skeleton=N'Server/Database/XmlSchemaCollection', @condition_name=N''
EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=72, @type_skeleton=N'Server/Database', @condition_name=N''

GO

EXEC msdb.dbo.sp_syspolicy_update_policy @policy_id=37, @execution_mode=1, @schedule_uid=N'00000000-0000-0000-0000-000000000000'

GO

I setted Evaluation Mode like On change: prevent.

If I try execute this script:

create table tblteste(
col1 int
);

SSMS will return this error:

Policy 'NomeDeTabela' has been violated by 'SQLSERVER:\SQL\XXX-XXX-XX\DEFAULT\Databases\cadastro\Tables\dbo.tblteste'.
This transaction will be rolled back.
Policy condition: '@Name LIKE 'tbl%''
Policy description: ''
Additional help: 'Ausência de prefixo 'tbl' no nome da tabela' : ''
Statement: 'create table tblteste(
col1 int
)'.
Msg 515, Level 16, State 2, Procedure msdb.sys.sp_syspolicy_execute_policy, Line 69 [Batch Start Line 0]
Cannot insert the value NULL into column 'target_query_expression', table 'msdb.dbo.syspolicy_policy_execution_history_details_internal'; column does not allow nulls. INSERT fails.
The statement has been terminated.

The nomenclature of table meets established policy.

I followed step-by-step from Microsoft SQL Server documentation (https://docs.microsoft.com/en-us/sql/relational-databases/policy-based-management/lesson-2-create-and-apply-a-naming-standards-policy?view=sql-server-ver15) but the problem persist.

Name and version information:

Microsoft SQL Server Management Studio                      14.0.17289.0
Microsoft Analysis Services Client Tools                    14.0.1016.283
Microsoft Data Access Components (MDAC)                     10.0.14393.0
Microsoft MSXML                                             3.0 6.0
Microsoft Internet Explorer                                 9.11.14393.0
Microsoft .NET Framework                                    4.0.30319.42000
Operating System                                            6.3.14393

Anywhone pass this problem before?


texts:
I am getting error ORA-03113 at the time of startup database. I removed some old archive log files as per solution I got on google. But still I am unable to startup db. But, I am able to connect other database (dbname: CTS) on same server.
Please suggest solution on this

Please find below current OS and DB configuration details and received errors.

**DB version is : 11g
Linux version : Linux ctsvm.oraclexpert.com 2.6.18-194.el5PAE #1 SMP Mon Mar 29 20:19:03 EDT 2010 i686 i686 i386 GNU/Linux**

Error I received is as below:-
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@ctsvm dbs]$ cd
[oracle@ctsvm ~]$ export ORACLE_SID=DBAYOG
[oracle@ctsvm ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.2.0 Production on Sun Oct 27 00:52:39 2013
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup
ORACLE instance started.
Total System Global Area  422670336 bytes
Fixed Size                  1344616 bytes
Variable Size             339741592 bytes
Database Buffers           75497472 bytes
Redo Buffers                6086656 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 4293
Session ID: 1 Serial number: 5


texts: Linked Server Login Timeout but SqlCmd works

texts:
I've got a SQL 2005 SP4 server that connects to a 2008 SP3 instance via linked servers using the SQL Server server type. Every once in a while, one of those linked servers will start throwing login timeouts. To rule out firewalls, I can RDP to the server and run sqlcmd and get in just fine, even making sure to use the same login. I'm thinking that SQL has somehow cached something that prevents it finding the right address. The remote servername is defined in that machine's host file. So far, only a reboot fixes the issue.

linked server is setup using remote sql login.
OLE DB provider "SQLNCLI" for linked server "myserver" returned message "Login timeout expired".
Bouncing the service does not fix the issue.

Any ideas?


texts: Sql Server pivot string results into columns

texts:
I am trying to transpose a dataset of string values, into columns, depending on the value of an integer
example:
create table imgText (
rowindex int,
imgName varchar(50)
)

insert into imgText (rowindex,imgName) values (0,'dog')
insert into imgText (rowindex,imgName) values (1,'plant')
insert into imgText (rowindex,imgName) values (0,'cat')
insert into imgText (rowindex,imgName) values (1,'tree')
insert into imgText (rowindex,imgName) values (0,'mouse')

My desired output would look like this

index0
index1

dog
plant

cat
tree

mouse
null

I tried doing a pivot, but a pivot requires a function (sum, max, average etc)
e.g.
WITH pivot_data AS
(
select rowindex,imgName from imgText
)
select [index0],[index1]
from pivot_data
PIVOT ( max(imgName) for rowindex IN ([index0],[index1]) )  as p;

but my error is
Error converting data type nvarchar to int.

I assume because I am trying to aggregate a varchar
note: rowindex can ONLY be 0 and 1


texts: LIQUIBASE Locking our DB with duplicate IDs post migration - handling changesets

texts: I apologize in advance if this is the wrong thread category but we recently migrated a DB and our liquibase configuration is seemingly not recognizing that there is a new path so Liquibase seems to be creating duplicate keys in our DB and it fails and locks up our DB and services. I think this is due to Liquibase trying to rectify the old Changesets with old path and can't adjust to having a changeset with new db path? Anyone have any issues with migrating DBs and dealing with Liquibase changeset configuration that doesn't lock up DBs?


texts: About single threaded versus multithreaded databases performance

texts:
H2 is a single threaded database with a good reputation regarding performance. Other databases are multi-threaded.

My question is: when does a multi-thread database become more interesting than an single thread database? How many users? How many processes? What is the trigger? Anyone has experience to share?

Summary

The usual bottleneck is disk access
SSD's are fast, but fragile (failure procedure is a must)
One long query on a single thread system will block all others
Configuring multi-threading system can be tricky
Multithreaded databases are beneficial even on single core systems


texts: Restore postgresql database from remote sql file

texts:
I would like to know if it's possible to restore a database from a remote sql file.
The problem:
- Working with PostgreSQL 9.2.4
- I have an small remote server with 10GB left in storage
- I have a compressed sql file which is 2GB (decompressed, it takes 7GB)
- If I upload the zipped sql file to the server and extract it, I'm using 2+7GB, removing the zip file, it reminds 3GB free in the server, which is not enough for the database which needs 7GB.

Questions:
Is there any way to restore my remote database from a local sql file?
If yes, how the command should look like?

Thank you!

Regards
Alejandro


texts: Is it possible to get a history of when a cluster failed over and which node became the active node?


texts: Remove the log shipping configuration on the secondary server, when the primary is no longer available

texts:
My primary server with SQL Server 2005 broke down, so I've started up the log-shipped database on the secondary server. The database is live on the secondary server, and I just need to remove the log shipping settings for it, while the primary server is no longer accessible.

It seems that the sp_delete_log_shipping_secondary_database stored procedure should be executed on the secondary server to do this, which "removes a secondary database and removes the local history and remote history", as it is stated on msdn.

Would this also remove the database itself? Or only the log shipping settings for it? And what will it do when the primary server is not accessible?

I must not loose the database on the secondary server, as it is already in use.


texts:
I'm studying B+ Tree and B Tree and I would like to understand two things about it, if someone can clarify it to me I would appreciate:

Why can I store more search keys on an B+ Tree Index? My guess would be that the reason is because the nodes of an B+ Tree point out to sub-trees instead of data.
Is there any type of comparison of data that will not work with an B+ Tree index or can I use all of them (=, >=, !=, <, <> ...) ?


texts: How can I 'further' improve my table and queries

texts:
This question is an extension of this question. Where Rick James kindly provided a list of things I should change in my table and query.

I also changed my application so that unused filters weren't included in the query at all.

Now my table looks like this:

CREATE TABLE `transaction_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `txn_id` varchar(32) NOT NULL,
  `nationality_id` smallint(6) NOT NULL,
  `tdate` date NOT NULL,
  `destination` varchar(80) NOT NULL,
  `sku` int(11) NOT NULL,
  `sales` decimal(8,2) NOT NULL,
  `units` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `tdate` (`tdate`),
  KEY `datenat` (`tdate`,`nationality_id`),
  KEY `dest` (`destination`),
  KEY `txnid` (`txn_id`),
  KEY `cov` (`sku`,`nationality_id`,`tdate`)
) ENGINE=InnoDB AUTO_INCREMENT=3236519 DEFAULT CHARSET=utf8;

Unforutnately, this actually increased the query time quite vastly (it took 100 seconds) and here is the explain. Note I have also removed the join on nationalities in the subquery as it was redundant.

Query

SELECT `products`.`brand`, COUNT(DISTINCT transaction_data.txn_id) AS numOrders
                                FROM `products`
                                INNER JOIN `transaction_data` ON `transaction_data`.sku=`products`.sku
                                INNER JOIN `nationalities` ON `transaction_data`.`nationality_id`=`nationalities`.`nationality_id`
                                INNER JOIN (SELECT DISTINCT `transaction_data`.`txn_id`
                                FROM `transaction_data`
                                INNER JOIN `products` USING (sku)
                                WHERE brand = '<brand>') AS tmp_txns ON tmp_txns.txn_id=`transaction_data`.txn_id
                                WHERE brand <> '<brand>' AND
                                 tdate >= '2014-01-01'
                                 AND tdate < '2014-01-01' + INTERVAL 1 YEAR
                                GROUP BY brand
                                ORDER BY numOrders DESC, brand ASC
                                LIMIT 10

I don't understand how it's using less rows but taking way longer.

I have also set my innodb_buffer_pool_size to a low "60M" (as per RickJames suggestion for a t2.micro amazon RDS).

Also, to add to previous.  Unfortunately setting up summary tables isn't a great idea. While it is correct that they re suitable for my use.  I can't guarantee that a user in the future won't try to add data either non-sequentially or even try to swap data out.

Edit

Further to Rick James' answer below

W.O.W - Hit the nail on the head with the date range in the inner query. I was basically running the wrong query the whole time (too many other =brand results).  It now runs in 2.4 seconds!

id = auto incremement, integer
txn_id = non-unique  'basket' identifier.

'1', '7662_20120101_PC  672_1_12:25:00',
'2', '7662_20120101_PC  672_1_12:25:00',
'3', '7662_20120101_PC  672_1_12:25:00',
'4', '7660_20120101_KL  867_1_12:23:00',
'5', '7662_20120101_PC  672_1_12:25:00',
'6', '7660_20120101_KL  867_1_12:23:00',
'7', '7658_20120101_KL  1211_1_12:12:0', // only sku in basket


texts:
I see some discrepancies between two views in postgres as to replication and lag time.

By this query, clearly replication is up:

9.5.6.11

postgres=# select client_addr, sent_location, replay_location from pg_stat_replication where application_name = 'walreceiver';
  client_addr   | sent_location | replay_location
----------------+---------------+-----------------
 172.yyy.yyy.213  | 1C/2B5732F8   | 1C/2B5732F8
 10.xxx.xxx.195 | 1C/2B5732F8   | 1C/2B5732F8
 10.xxx.xxx.196 | 1C/2B5732F8   | 1C/2B5732F8

10.1

postgres=# select client_addr, sent_lsn, replay_lsn from pg_stat_replication where application_name = 'walreceiver';
 client_addr  |  sent_lsn  | replay_lsn
--------------+------------+------------
 10.qqq.qqq.44 | 1/8C000140 | 1/8C000140

But when look for the lag time:

9.5.6.11

postgres-# SELECT pg_last_xlog_receive_location() ,  pg_last_xlog_replay_location() ,  now() ,  pg_last_xact_replay_timestamp();
 pg_last_xlog_receive_location | pg_last_xlog_replay_location |              now              | pg_last_xact_replay_timestamp
-------------------------------+------------------------------+-------------------------------+-------------------------------
                               |                              | 2018-05-10 04:19:38.079161-04 |

10.1

postgres=# SELECT pg_last_wal_receive_lsn() ,  pg_last_wal_replay_lsn() ,  now() ,  pg_last_xact_replay_timestamp();
 pg_last_wal_receive_lsn | pg_last_wal_replay_lsn |              now              | pg_last_xact_replay_timestamp
-------------------------+------------------------+-------------------------------+-------------------------------
                         |                        | 2018-05-10 04:29:21.730089-04 |

There are no info..
What am I missing?


texts: Similar data with fields redundant in one table

texts:
I need four different plans to be stored in one or more tables. All four plans share the fields however some plans have extra fields.

All plans fit into the plans-table:

PLANS(id, provider_id, plan_type, name, desc, price, link)

However based on the plan type it requires more fields to be added and/or some fields will be empty.

As an example it may be that if plan type A is chosen there are no empty fields but if plan type B is chosen there will a requirement of two additional fields like:

PLANS(id, provider_id, plan_type, name, desc, price, link, strength, acceptable)

In other cases plan C requires all fields except strength field.

Leaving empty fields means it's not fully normalised, but my reasons for leaving it like so is because I want to use one QUERY to find all the different types of plans for a provider using a GROUP BY with a server side script.

I could use an additional table to store the additional fields with a 1:1 relationship or have 4 separate tables?

If I do decide to have 3 additional tables (with only the extra fields for each plan) then I could just do a JOIN when needed.

Here's what it could possibly look like (I've omitted some fields):

Is this a good approach?


texts: Why are event sourcing (CQRS) databases not popular?

texts:
The idea of a database storing events and showing the net result of these events is realistic to the real world (and what actually happened).

Given the flexibility event sourcing databases offer (e.g. view the database at different points in time), why are such databases not more popular?

For example the only two databases I am aware of, that do event sourcing are Datomic and Event Store.


texts: How does Automatic Seeding work - Always On Availability Group

texts:
I found only these two statements from BOL regarding this Initial Data Synchronization method:
Link 1

Automatic seeding uses the log stream transport to stream the backup
using VDI to the secondary replica for each database of the
availability group using the configured endpoints.

Link 2

During automatic seeding, SQL Server performs a backup over the
network for initialization.

Looking into SQL Server Error logs of both primary and secondary replicas, these two logs caught my attention:
On primary replica

On secondary replica

My question is related to the working nature of automatic seeding, namely where does taken backup is stored before restoring on the secondary replica?


texts: Update column of a large set of records with a calculated value

texts:
I am trying to figure out what a good way would be to write the pseudo query below in TSQL taking into account that there will be about 50 actualResult records and 100.000 prediction records per actual result involved.

update prediction
set points = (
    @points = 0
    if(prediction.valueA = actualResult.valueA AND prediction.valueB = actualResult.valueB)
        @points = @points + 3
    if (prediction.valueA = actualResult.valueA OR prediction.valueB = actualResult.valueB)
        @points = @points + 1
    select @points
)
join actualResult on prediction.actualid = actualResult.id
where actualResult.id in (some subquery)

what I am wondering about is:

Is there a way of doing the calculation which in reality is more complex inline? Right now I can only get it to work if I put it in a function but from what I understand this means it's constantly re-calculted even if the input is the same (which I quite likely in allot of the predictions)
Would this way lock the entire range of records? And if so is there a way to prevent this? For example by copying it all to a table variable and then doing the calculation on that and then updating the records once all calculations are done?

am I on the right track here or would you write it in a different way?


texts: Always Encrypted: How to insert or update encrypted columns?

texts:
I have configured 'Always Encrypted' on one of the columns of a table of my SQL Server database. I am able to select and view data from client SSMS after passing the 'Column Encryption Setting = Enabled' option.

But when I am trying to insert data into the table, following error comes:

  Msg 206, Level 16, State 2, Line 1 Operand type clash: varchar is
  incompatible with varchar(8000) encrypted with (encryption_type =
  'RANDOMIZED', encryption_algorithm_name =
  'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name =
  'CEK_Auto1', column_encryption_key_database_name = 'TEST')
  collation_name = 'SQL_Latin1_General_CP1_CI_AS'

I am querying simple insert TSQL statement here. What changes should I need to make for it to work?

Also, what changes need to be made to the application or application code if, we want to update or insert the encrypted column via application?


texts: Patching FileShareWitness for AlwaysOn AG

texts:
Situation:
We have an AlwaysOn High Availability group with 4 replicas (2 for DR) a listener and an FileShareWitness. I've been tasked with creating a set of instructions for patching all of the servers in the environment.

Problem:
The plan has been based on Pinal Daves article (with some modifications). here

The problem is that when patching the FSW it switches back and forth between replicas.

A colleague has suggested removing the possible failover value from the ip (not from AOAG) to avoid the failover and then putting it back afterwards.

Please advise if this is good idea, or if there is a better way of stopping the AG replica from switching back and forth?

SQL is 2016 Enterprise SP1 running on WS2012R2 Datacenter 6.3 with Hypervisor

Any help will be much appreciated.


texts: How to change the hierarchical data to join 4 tables if only one table is common?

texts:
To practice sql, I try to create a music database with the following tables:

Song table: released songs (as a file)

+----+----------+-----------+--------------+----------+----------+
| ID | album_id | artist_id | extension_id | SongName |   Date   |
+----+----------+-----------+--------------+----------+----------+

Album table: the albums of the songs

+----------+------------+
| album_id | album_name |
+----------+------------+

Artist table: the artists of the songs and albums

+-----------+-------------+
| artist_id | artist_name |
+-----------+-------------+

Extension table: the extensions of the files (mp3,m4a,wav...)

+--------------+-----------+
| extension_id | extension |
+--------------+-----------+

My SQL query:

SELECT * FROM Song as s
    INNER JOIN Album as a
        ON s.album_id = a.album_id
    INNER JOIN Artist as b
        ON s.artist_id = b.artist_id
    INNER JOIN Extension as e
        ON s.extension_id = e.extension_id
    ORDER BY Date DESC

Unfortunately the query doubles the result (maybe cause of "Cartesian product").
How should I change the query/hierarchical data?


texts:
I am using Microsft SQL Server 2016. I have an SQL table with 140000 rows. It is used only for selecting data.

I am trying to increase it's selection performance by making it In-Memory table (it is worth to mention that this is my first time using In-Memory tables).

The results I am getting in SQL Server profiler are rather dissatisfying. Compared data of 'CPU', 'Reads' and 'Duration' columns: SELECT from In-Memory table has 'CPU' a bit higher, 'Reads' up to 10 times lower but unfortunately 'Duration' stays pretty much the same.

I've tried generating In-Memory OLTP migration checklist - the results were successful.

Could You please tell me if this is a good use of SQL server In-Memory tables?

Maybe I am doing something wrong since duration stays the same?

Thank You in advance!


texts: MySQL binary log reporting execution time as UINT_MAX

texts:
Looking through the MySQL Binlogs, I see some data that just does not make sense.  There are queries that say they have been running for 4294967295 seconds (which also happens to be UINT_MAX on this system).

The queries that are reported with these execution times vary, but they are typically updates to small tables. There is no way that these queries have been running for that long.

Percona has a blog stating that the exec_time value can be different than one would expect if the server is a MySQL replication slave, but this is not the case.

Are these strange values for exec_time in the binary log expected, or is this a bug in MySQL?


texts: visual studio 2012 ssrs report preview error failed to load assembly could not load file reportingservices

texts:
I had used visual studio 2010 data tools earlier. Since i couldn't find it on Microsoft site, had to install visual studio 2012 data tools on my new laptop. When i click preview, i get the error failed to load expression host assembly could not load file or assembly Microsoft reporting services processing core,version 11.0 Failed to grant permission to execute. Could someone help to fix this?
I have tried uninstalling an reinstalling once but now i am not able to fully uninstall and reinstall
It deploys report without issues and the report runs fine on the ssrs server


texts: Is it possible to have xid before and after wrap around with the same timestamp?

texts:
I want to create sync API in which users can get the changes since their last sync request, the API exposes cursor-based pagination (sync token)
A possible cursor may be to use updatedAt column along with xmin column (I need the xmin because many rows may have the same timestamp).
My only concern is if it's possible to have rows with same (updatedAt) timestamp and wrapped around xmin:
+---------------------+------------+
| timestamp           | xmin       |
+---------------------+------------+
| 2011-01-01 00:00:00 | 4556455456 | -> first row
+---------------------+------------+
| 2011-01-01 00:00:00 | 6          | -> second row, wrapped around xid
+---------------------+------------+

Within a specific timestamp (moment), I can be sure xmin will behave like a simple sequence, right?