texts: Adding foreign key that references a column which is not Primary or Unique key

texts:
I got into trouble when I want to limit values of Table A's column to be taken only from a set of optional values in Table B's relevant column.

Problem is table B's relevant column isn't a foreign key.
And if I will do that I guess that the correct state where I can have the same values multiple time for different columns values will be impossible- and this is not the behavior I look for.

So what can be done?


texts: Dual site active-active highly available database

texts:
I have an application that is being run on two separate sites. Ideally they would share a database, because each application needs to see what the other is writing to the database.

However, the sites are geographically separate and each needs to be able to operate if the other one goes down.

There also needs to be high availability within each site.

Does MS SQL AlwaysOn provide this functionality?
From what I've read it seems to only support a read only secondary site.

I see that a question similar to this was already asked, but it's from before AlwaysOn was released:
Is it possible to have 2 or more databases active, and synchronized between them?


texts: User switching accounts, can't log into MS SQL Server anymore

texts:
First a bit of background, for good or bad, we don't keep one user account attached to one person. The user accounts are matched to their position. For instance the CEO would have a userid of CEO. (Not a real profile, just used as an example).

We have an application that uses the SQL server authentication to restrict what they can access in the application. We use AD groups to control what they can see to make user management easier. We found out in the past we can't rename a user as they can't log into this account. We had to delete the old account and completely create a new different account in order to have them log in.

Now we have a similar, but different problem. The old "CEO" left and we updated the CEO profile with the new CEO's information, now that account can't log into the application. We have deleted the groups they we have for the application and re-added them. They still can't log in. Based on this article, when you rename a user, the meta data doesn't match so you need to do a little ALTER USER script to fix the user. The suggestion to prevent this in the future would be to use groups much like we are doing now.

Is there a cache of users that might be causing problems, even if we don't authorize by users? How would I flush that cache if that is the case? Is this a problem with SQL Server 2008 as well? We are upgrading to that soon. What else can I look at?

Here is how the user is set up: CREATE LOGIN [DOMAIN\cgUSERS] FROM WINDOWS WITH DEFAULT_DATABASE = [master]

Here is the exact error from the application:

  You don't have proper security rights to read the Cartegraph system tables. Access the the system tables are required to run the application. Please contact your System Administrator.

Another site mentions rebooting the service and/or server. I think I might try that once to see if that fixes our issue.


texts: MySQL Master-Slave-Slave Configuration -error 1236

texts:
I currently have a Master (5.5) - Slave (5.6) set up right now and I would like to add another slave that will replicate from the existing slave.

On the existing slave:

1. STOP SLAVE;
2. SHOW SLAVE STATUS;
3. Note these values Master_Log_File: mysql-bin.018098 & Read_Master_Log_Pos: 185753950
4. FLUSH TABLES WITH READ LOCK;
5. Service mysql stop
4. Took snapshot of mysql data vol and restore it on new mysql slave server, also I changed the server-id in my.cnf on the new mysql slave.
5. service mysql start.
6. UNLOCK TABLES;

on the new mysql slave server (should replicate from the existing slave):

1. changed server-id on my.cnf
2. remove /mnt/data/mysql/auto.cnf
3. service mysql start --skip-slave-start

4.
CHANGE MASTER TO MASTER_HOST='existing slave',
MASTER_USER='xxx',
MASTER_PASSWORD='xxx',
MASTER_LOG_FILE='mysql-bin.018098',
MASTER_LOG_POS=185753950;

5. execute start slave

got an error:
2016-11-18 16:39:50 5736 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file (server_errno=1236)
2016-11-18 16:39:50 5736 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file', Error_code: 1236
2016-11-18 16:39:50 5736 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.018098', position 185753950

Thanks !!


texts: Postgres anonymous block execution of generated dynamic sql to drop databases

texts:
I have backup databases I wish to drop. I can generate a list :

select 'drop database ' || datname || ' ;' from pg_database where datname like 'bak_%'

getting me

drop database bak_20180101 ;
drop database bak_20180102 ;

etc

but when I try

Do $$Begin execute 'drop database $1 ;' Using  'bak_20180404_2014_12' ; End$$ ;

that gets me syntax error at or near "$1"

and when I try

Do $$Begin execute 'drop database bak_20180404_2014_12' ; End$$ ;

that gets me DROP DATABASE cannot be executed from a function or multi-command string

Is there a simple way to drop a generated list of databases?
(and what was wrong with my Execute Using)


texts: Order result by number of occurence of a word in a text

texts:
I am working with sqlite3/python .

I would like to order results by the number of occurence of %@% in text.

For exemple

Jimmy 10 // Jimmy wrote 10 strings like %@%
James 2
Sophia 0
..etc

The code could be something like this :

cursor = db.execute("SELECT name, text
                       FROM MyTable
                      WHERE text LIKE '%@%'
                      ORDER BY someMagic DESC")

I am looking for someMagic if it exists :-)


texts: Always Encrypted on Sql 2017 Express Edition

texts:
I am so tentative to decide to use Always Encrypted tool, however I have a question → I'm working on a project with SQL Server 2017 - 14 Express Edition.

And I'm pretty sure they aren't going to buy the Enterprise Edition.

Even so, Can I use this tool? Or is there any limitation related to the final version of SQL? I didn't find any limitation at the official documentation from Microsoft

Thanks a lot.


texts: How can I query data from a linked server, and pass it parameters to filter by?

texts:
I have a really big query that needs to be run on multiple databases, and the results appended to a temp table and returned.

The basic syntax looks something like this:

INSERT INTO #tmpTable (Id, ...)

SELECT T1.Id, ...
FROM Server.Database.dbo.Table1 as T1
INNER JOIN #tmpIds as T ON T1.Id = T.Id

INNER JOIN Server.Database.dbo.Table2 as T2 ON T1.Id = T2.Id
INNER JOIN Server.Database.dbo.Table3 as T3 ON T2.Id = T3.Id
LEFT OUTER JOIN Server.Database.dbo.Table4 as T4 ON T3.Id = T4.Id
LEFT OUTER JOIN Server.Database.dbo.Table5 as T5 ON T4.Id = T5.Id
LEFT OUTER JOIN Server.Database.dbo.Table6 as T6 ON T5.Id = T6.Id

The query runs quickly if run locally on the the individual servers, however it takes a long time to run if it's run from a linked server using the 4-part names like above.

The problem appears to be it's querying the linked server for the unfiltered result set first, then joining it to the #tmpIds table on the local server afterwards, which makes the query take an very long time to run.

If I hardcode the Ids to filter the result set on the linked server, such as

SELECT T1.Id, ...
FROM Server.Database.dbo.Table1 as T1
-- INNER JOIN #tmpIds as T ON T1.Id = T.Id
INNER JOIN Server.Database.dbo.Table2 as T2 ON T1.Id = T2.Id
INNER JOIN Server.Database.dbo.Table3 as T3 ON T2.Id = T3.Id
LEFT OUTER JOIN Server.Database.dbo.Table4 as T4 ON T3.Id = T4.Id
LEFT OUTER JOIN Server.Database.dbo.Table5 as T5 ON T4.Id = T5.Id
LEFT OUTER JOIN Server.Database.dbo.Table6 as T6 ON T5.Id = T6.Id

WHERE T1.Id IN (1, 2, 3)

it runs quickly in just a few seconds.

Is there a way to run this query so it filters the result set of the query from the linked server by the #tmpId table first, before returning the result set to the local server?

Some things to note

The query is very large and complex, and Dynamic SQL is not a viable option for it due to the maintenance nightmare that causes.

I would be open to suggestions about how to use Dynamic SQL for something else though, such as running a stored procedure or UDF if there's a way to do that over a linked server (tried a few different methods such as sp_executeSQL, OPENROWSET, and OPENQUERY, but those all failed).
Because it uses the 4-part naming convention, I cannot use a UDF on the remote server
Distributed Transactions are disabled, so the following does not work

INSERT INTO #table
EXEC Server.Database.dbo.StoredProcedure @ids


texts:
I have a database where i am using inline TVFs (table value functions) instead of views.  For example, I might have two tables called [car model] and [car manufacturer] that I'm joining together inside the TVF [fnCarBrands].

These TVFs are then called by other TVFs to do further processing and reporting.  So I might take my function [fnCarBrands] and join to the table [Purchase Year] to form a function [fnCarBrandHistory].  And so on for several layers of TVFs.

I could probably get the same functionality using views, since my inline TVFs are really just joins of tables and other TVFs.

How does the performance of inline TVFs written in this way compare with views?


texts: Mysterious procedure calls without parameters - but no exceptions generated

texts:
I've been tracing a handful of stored procedures with EventClass RPC:Completed that are intermittently called from .Net SqlClient Data Provider without required parameters - just "exec SprocName".

If I manually call them as they appear in the trace's TextData, they generate an exception - but no exceptions are being generated in the original trace.  The Error column for these "parameterless" calls does show "1 - Error," but I don't understand how the call is not generating an exception.

At first I suspected it was a glitch with profiler, that "1 - Error" caused parameters to be truncated from the TextData, but I see the same results in an extended events session.

Has anyone seen anything like this in their profiler traces or event sessions?  The app is almost certainly making these calls as part of some edge-case error, but why is sql server not throwing an exception?


texts: connecting to Postgres running inside docker container 'posgres' does not exist

texts:
I want to connect to Postgres (running in a container) using psql

What I've done:

docker pull postgres
docker run -p5432:5432 --name mydb -e POSTGRES_PASSWORD=pass123 -d postgres

Trying to connect via PSQL command
psql -h localhost -U postgres

But I keep getting psql: FATAL:  role "docker" does not exist
(I tried different variations of the command like with -d postgres or with -W , even tried with PgAdmin ...still same error)

However, when I jump into the container docker exec -it mydb bash then psql -U postgres I can see the following:

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(3 rows)


texts:
We are on SQL Server 2008 R2 and have DBs compatibility level set to SQL 2000 or 2005.

What are differences in compatibility level across the versions ?

Are there any advantages changing it to 2008 or higher?


texts: Using a `mysql_query` to insert category values

texts:
Ok I'm importing direct from a csv to MySQL, using a mysql_query to insert values.
How do you insert category terms into the Wordpress database structure assigning them to a post. Below are two import queries- you will see INSERT INTO wp_terms which I know is not correct, but need help fixing. The rest of the code works beautifully.

<?php
    set_time_limit(0);
    ini_set('memory_limit', '2048M');

    $cn = mysql_connect('localhost','root','1');
    mysql_select_db('bitnami_wordpress',$cn);
    $path = "hotels.csv"; // SET YOUR CSV FILE PATH HERE
    $row = 1;
    if (($handle = fopen($path, "r")) !== FALSE) {
        while (($data = fgetcsv($handle, 100000000, ";")) !== FALSE) {
            $row++;
            $data_entries[] = $data ;
        }
        fclose($handle);
    }
    $final_data = array();
    $count = 0 ;
    array_shift($data_entries);
    foreach ($data_entries as $value) {
        $temp = implode('', $value);
        $tempArr = explode('|', $temp);
        $final_data[] = $tempArr;
        $ins_str = "INSERT INTO wp_posts values('','','','','".mysql_real_escape_string($tempArr[27])."','".mysql_real_escape_string($tempArr[1])."','','".mysql_real_escape_string($tempArr[18])."','','','','".mysql_real_escape_string($tempArr[2])."','','','','','','','','','".mysql_real_escape_string($tempArr[17])."','','')";
        mysql_query($ins_str,$cn);
        $post_id = mysql_insert_id($cn);

        $ins_str = "INSERT INTO wp_postmeta VALUES('','".$post_id."','list-id','".$tempArr[0]."')";
        mysql_query($ins_str,$cn);
        $ins_str = "INSERT INTO wp_postmeta VALUES('','".$post_id."','airport_code','".$tempArr[3]."')";
        mysql_query($ins_str,$cn);
        $ins_str = "INSERT INTO wp_postmeta VALUES('','".$post_id."','location','".$tempArr[4]."')";
        mysql_query($ins_str,$cn);
        $ins_str = "INSERT INTO wp_terms VALUES('','".$post_id."','job_listing_region','".$tempArr[5]."')";
        mysql_query($ins_str,$cn);
        $ins_str = "INSERT INTO wp_postmeta VALUES('','".$post_id."','low_rate','".$tempArr[13]."')";
        mysql_query($ins_str,$cn);
        $ins_str = "INSERT INTO wp_postmeta VALUES('','".$post_id."','high_rate','".$tempArr[14]."')";
        mysql_query($ins_str,$cn);
        $ins_str = "INSERT INTO wp_postmeta VALUES('','".$post_id."','confidence','".$tempArr[15]."')";
        mysql_query($ins_str,$cn);
        $ins_str = "INSERT INTO wp_terms VALUES('','".$post_id."','job_listing_category','".$tempArr[16]."')";
        mysql_query($ins_str,$cn);

    }
    echo "<pre>";
    print_r($final_data);
?>

Thanks in advance, Murray


texts: export data from SQl to text file/MongoDB

texts:
I have some data in multiple tables (ACTTAB, APTTAB, ...)

How do I export this data from SQL and insert into a text file?
How can I import this data into MongoDB?


texts: Create table from stored procedure result

texts:
I am new to this field if you can please tell me if it is possible to do this it will be very help full. I have SQL Server 2008 R2.

What I want to do is from my table I want to take first row and try to use it as a column name to create another table in a stored procedure. The reason why I want to use those particular column name is which will come in as data in first row is, it will keep changing every week since those are dates.

So my plan is if it is possible to extract that first line from data which is ([Employee Name], [10/18/2013], [10/19/2013]..) and create another table which will use that extracted data as "column name" and create another SP will fill data as required. But I have no clue if SQL will create table and use "column name" which extracted from another data.

Further information in response to the answer:

What I am trying to do is. I have data coming in as time sheet every week. my plan is once I will get time sheet in every week as excel I will some how import into SQL as Import/export. the only problem is that Time Sheet have columns start from [Last Name], [First Name], [10/21/2013],[10/22/2013],[10/23/2013],...,[10/24/2013]. and I need those date columns in my data so I can use those in my later store procedure. in order for it do, I have included those columns in my data and not in headers when I import, so I can use those data. after doing that i have wrote one simple store procedures, which only read first row which will be first name, last name, 10/21/2013, 10/22/2013 and so on to extract header from the data. and from their i want to execute a store procedure to create new table every week(since date will be change every week) and will have first name, last name, 10/21/2013, hours of work, Taxonomy( will have 3 characters from fist name, last name, date and proj name) in different table. once the time push for that week at the end i will delete that table.

I apologize if that sounds confusing. Do you think I am going on right direction. if you know any links that explain this type of case, it will be so helpful since i am new to this field.


texts: Attempting to restore SQL Server 2005 database to a SQL Server 2008 R2 instance gives reason: 15105 error

texts:
I have a SQL Server 2005 database I am attempting to restore a SQL Server 2008 R2 SP2 machine using this statement:

RESTORE DATABASE [Db]
FROM  DISK = N'C:\temp\sql\DB.bak' WITH  FILE = 1,
MOVE N'DB' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\NewDB.mdf',
MOVE N'DB_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\NewDB.ldf',
REPLACE,  STATS = 10
GO

But It keeps giving this error.

  Msg 3634, Level 16, State 1, Line 1
  The operating system returned the error '5(failed to retrieve text for this error. Reason: 15105)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\NewDB.mdf'.
  Msg 3156, Level 16, State 5, Line 1
  File 'Db' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\NewDB.mdf'. Use WITH MOVE to identify a valid location for the file.
  Msg 3634, Level 16, State 1, Line 1
  The operating system returned the error '5(failed to retrieve text for this error. Reason: 15105)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\NewDB.ldf'.
  Msg 3156, Level 16, State 5, Line 1
  File 'Db_Log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\NewDB.ldf'. Use WITH MOVE to identify a valid location for the file.
  Msg 3119, Level 16, State 1, Line 1
  Problems were identified while planning for the RESTORE statement. Previous messages provide details.
  Msg 3013, Level 16, State 1, Line 1
  RESTORE DATABASE is terminating abnormally.

I am logged into SQL Server as SA. I have Admin permissions on the system.

I have also tried to remove the restore location to another path (that has full permission) with the same error.


texts:
In MySQL I've two tables:

Monitors:
    Id: INT PRIMARY KEY NOT NULL AUTOINCREMENT
    GroupId: INT NULL
    ....

MonitorGroups:
    Id: INT PRIMARY KEY NOT NULL AUTOINCREMENT
    Name: VARCHAR(50)

I need to define this constraint:

ALTER TABLE Monitors ADD CONSTRAINT monitor_group_fk FOREIGN KEY (GroupId) REFERENCES MonitorGroups (Id);

But I get the following error:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`zm`.`#sql-475_1c237`, CONSTRAINT `monitor_group_fk` FOREIGN KEY (`GroupId`) REFERENCES `MonitorGroups` (`Id`))

At the moment there's no record in MonitorGroups table and GroupId of rows of table Monitors are not set. I need it to be legal. In another words, I need MySQL to keep track of the field GroupId of table Monitor only if it's set. I would like to define the constraint as:

ALTER TABLE Monitors ADD CONSTRAINT monitor_group_fk FOREIGN KEY (GroupId) REFERENCES MonitorGroups (Id) ON UPDATE CASCADE ON DELETE SET NULL;

so that if a group id (Id field) changed in table MonitorGroups, MySQL change it automatically in table Monitors and if a group was deleted from table MonitorGroups, MySQL automatically set GroupId of corresponding rows in table Monitors to null.
What's wrong and how to reach this goal?


texts: Are cross-database queries expensive in SQL Server? All of the databases are in the same instance.


texts: PostgreSQL SQL syntax - JOIN and substrings

texts:
I have a single table of hostnames and NFS exports, some but not all of which are related.
Example entries:

    HOST   VOLUME
    -----  ----------
    host1  vol1
    host1  vol2
    host1  vol3
    host2  bkvol1_DR
    host2  bkvol2_DR
    host2  bkvol3_DR

I'm trying to create a query which will give me the following:

   SRCHOST  DSTHOST  SRCVOL    DSTVOL
   -------  -------  ------    ------
   host1    host2    vol1      bkvol1_DR
   host1    host2    vol2      bkvol2_DR
   host1    host2    vol3      bkvol3_DR

I've tried various incantations of substring(), trim(), and join clauses, and I'm getting nowhere. This is on PostgreSQL 9.2. In non-functional code, this is what I want:

select a.host, b.host, a.volume, b.volume from table as a, table as b
    where a.volume is a substring of b.volume


texts: Oracle regular expression replace in string ignoring first and last characters

texts:
regular expression replace in string ignoring first and last characters if they matched

my try

SELECT REGEXP_REPLACE ('TLYU TK0T23T', '[^(\A.)]T[^(.\Z)]', 'Qq' ) contain FROM dual;

result

TLYUQqQq3T

expected result

TLYU QqK0Qq23T

Thanks in advance


texts: Is it possible to spin up a free development / test instance of an AWS Redshift server to run test queries on?

texts: I have a traditional MS SQL Server background and am interested in looking into AWS Redshift. Microsoft offers a free download of their development version installer of SQL Server, and I was wondering if there's an equivalent for AWS Redshift so that I can run some test queries and check out the performance.


texts: How does MySql(InnoDB) update secondary non unique index?

texts:
Let's say we have a table with the following structure:
CREATE TABLE Persons (
    PersonID int NOT NULL PRIMARY KEY,
    LastName varchar(255),
    FirstName varchar(255)
);
CREATE INDEX Lastname ON Persons (LastName);

Also, you can notice we have a secondary index called Lastname.
From the documentation of MySql(InnoDB engine), it is clear that the secondary index is referencing the primary key.
So my questions are:

what happens if we update a primary key? How does InnoDB quickly update all secondary index entries? Is
there a reference from the Primary key index page to all secondary index pages?
what happens if we update the field "LastName"? As the secondary index is nonunique, how does MySQL search for the page to be updated in the secondary index? It looks like there should be a reference from PK index to all secondary indexes pages of the given row. But I can not find explicit mention of this in the documentation of MySql.


texts:
I have a MySQL 5.7 database. I was looking in statistics in server status that shows type of queries / commands / tasks executed in the database. It shows that the query command "change db" by far was the most frequent one.

What does the "change db" value mean in Server > Status > Statistics? I only have one database (except system databases) why is the query running so often, almost twice select?


texts:
I'm creating a webpage for placing bets on all matches of the upcoming Euro 2012 football tournament. Need some help deciding what approach to take for the knockout phase.

I have created a mockup below, which i'm pretty satisfied with when it comes to storing the results of all "known" group stage matches. This design makes it very easy to check if a user has placed a correct bet or not.

But what is the best way to store the quarter and semi finals? Those matches depend on the outcome in the group stage.

One approach I thought of was adding ALL matches to the matches table, but assign different variables or identifiers to the home/away teams for the matches in the knockout phase. And then have some other table with those identifiers mapped to teams... This could work, but does not feel right.


texts:
I have list of items that are belongs to orders. Each item has fee_percentage field. and the sum of items is total_price in orders table.

What I want to achieve?
I want to update the fee field of last associated items.

_________________________      __________________________________________
| order ID | total_price|      | id | order_id | fee_percentage |  fee  |
-------------------------      ------------------------------------------
|   334    |     425    |      |  1 |   334    |      50        |  212  |
  # The `fee` should be 213 => |  2 |   334    |      50        |  212  |

As you can see the sum of items will be 424. I want to add the remaining fee to the last item fee. (So the fee of item id 2 should be 213).

Any help please?

This is what I have written so far

UPDATE items AS i
LEFT JOIN orders AS o ON o.order_id = b.order_id
LEFT JOIN (
    SELECT order_id, SUM(fee) AS sum_fee
    FROM items
    GROUP BY order_id
    WHERE deleted = 0
) AS b1 ON b1.order_id = b.order_id
SET b.fee = b.fee + (o.total_price - b1.sum_fee)
WHERE b.deleted = 0;


texts: Very different query plans with and without LIMIT in PostgreSQL.

texts:
I have the following query which I am using to create vector tiles for a web map. I'm pulling together just the geometries within the selected tile using a CTE, then joining it to get additional attribute information.

WITH lots AS (
  SELECT pams_pin, shape
    FROM parcels
   WHERE ST_Transform(shape,3857) && TileBBox($z, $x, $y)
)
SELECT p.pams_pin, v.property_location, v.property_class
     , v.owner_name, v.owner_address, v.owner_city, v.owner_zip
     , ST_AsGeoJSON(ST_Transform(p.shape,4326), 7) as shape
  FROM lots p
  LEFT JOIN mv_modiv_sr1a v ON p.pams_pin = v.pams_pin
;

This is incredibly slow, taking several seconds to return a result. However, if I take this same query and add LIMIT 500 to the end, it returns results almost immediately.

Here's the output from EXPLAIN ANALYZE with the limit and without the limit.

Why does the query planner take such a different path when a LIMIT clause is added? Is there any way to force this more efficient plan without using LIMIT? While in this case, LIMIT 500 should not result in any missing records, I'd like a better understanding of why this is occuring and if there's a way to provide a hint to the planner to use the smaller set within the CTE instead of driving the results from the ~3 million record table and then throwing a massive amount of data away.


texts: sql job cancel triggers failure notification

texts:
When I cancel a sql agent job running an ssis package, I get a failure email because I set up the notifications to email when the Job Fails.

Does anyone know why this is considered a 'failure'?


texts: How do you SUM a column without having its name?

texts:
I'm stuck in a way that how to do sum on a column let's say first column of a table (result from another query) without knowing the column name with something like column position id.
it's something like this
select sum(what?), employID from
    ( select count(*), employID from  table1...
           union all
      select count(*), employID from  table2...
           union all
      select count(*), employID from  table3...
)

Or if it's in single query (single simple select query with using sum() ) like:
select employName, sum(what?), employID from tableX

How do I tell SUM() function to sum based on column position index in table like SUM(2)?
Note: I don't want to use column alias, any possibility of doing SUM not based on column name?
I know I can use column-name or aliases, but I really want to know the possibility of not using only these which is why I'm asking this question, if no possible ways, I will accept "No" as correct answer then.


texts: Restore of a database failed - Table does not contain an identity column

texts:
I've created a local SQL Server instance (using SQL Server 2017). I'm trying to restore a backup of a database created on a SQL Server 2012 server, for testing purposes.

The problem is that it gives me an error when I try to restore it:

  TITLE: Microsoft SQL Server Management Studio

  Restore of database 'XXXX' failed.
  (Microsoft.SqlServer.Management.RelationalEngineTasks)

  ------------------------------ ADDITIONAL INFORMATION:

  System.Data.SqlClient.SqlError: 'MyTable' does not contain an identity
  column. (Microsoft.SqlServer.SmoExtended)

Does anybody have any idea of what can be done to restore it? Does this may have something to do with server configurations? Thanks a lot.

@Max Vernon, I checked the log following your suggestion and I was surpried do see that, although message said "Restore failed", the log says something a bit different: Error: 3165, Severity: 16, State: 1. Database 'XXXX' was restored, however an error was encountered while replication was being restored/removed. The database has been left offline. See the topic MSSQL_ENG003165 in SQL Server Books Online.

So, I refreshed the Databases node just to find out that the DB was really there, offline. I've just brought it online, changed from "single user" to "multi user" and it's working fine :) About the actual error (3165..) I don't now why it happened, but te DB is just for tests and it's working, so I'll not worry about it for now. Thanks a lot for your help. All these "stack exchanges" communities are amazing.


texts: Giving a user access to a new database you don't have access to

texts:
I have a scenario where I have a SQL Server login (let's call it myuser) with the following roles: dbcreator and securityadmin. That user has access to a single database and needs to create other databases and create more logins and give users access to the new databases. Code would look something like this:

RESTORE DATABASE MYDB FROM DISK='c:\\mybackup.bak'
WITH MOVE 'MYDB_Data' TO 'C:\\Program Files\\Microsoft SQL Server\\MSSQL11.SQLEXPRESS\\MSSQL\\DATA\\MYDB.mdf',
     MOVE 'MYDB_Log' TO 'C:\\Program Files\\Microsoft SQL Server\\MSSQL11.SQLEXPRESS\\MSSQL\\DATA\\MYDB_log.ldf'
GO

CREATE LOGIN mydb_user WITH PASSWORD = '123'
GO

USE MYDB
CREATE USER [mydb_user] FOR LOGIN [mydb_user]
EXEC sp_addrolemember N'db_owner', N'mydb_user'

Problem I'm running into is that the "USE MYDB" statement fails because myuser doesn't have access to MYDB. Technically, I don't need myuser to have access to any data, I just need it to be able to create the user and add the tole to the DB, but not sure if that can be achieved without access to the DB itself. And if access to the DB is required to give access to other users, how would I go about giving it. I would think that if the user actually created the DB, he should be able to grant himself access to it?


texts: FBI on function with concatenated string

texts:
I have an index in place (from days of old) that I'm curious about. It's on a basic person table and the function is

upper("LAST_NAME"||','||"FIRST_NAME"||"MIDDLE_NAME"||"SUFFIX_NAME")

When trying to search on this index, I wind up with a full table scan. Any idea why? And if this is just completely broken like I think it is, would you suggest a column index on these four columns?

EDIT

Sorry about not providing the query. Yes, the query is a like, and the columns are all nullable. So I have

select *
  from person p
  where UPPER("LAST_NAME"||','||"FIRST_NAME"||"MIDDLE_NAME"||"SUFFIX_NAME")
    like replace(upper('<search string here>'), '*', '%') || '%'

Any thoughts? Thanks for the quick answer.


texts: TDE prep: key/certificate backup for restores

texts:
I am working in a dev environment to better understand TDE encryption. I have it working along with backups and restores on another server. Had a few questions, I know I need to backup the certificate with the corresponding private key.

USE master;
GO
BACKUP CERTIFICATE Test
TO FILE = 'C:\Test.cer'
WITH PRIVATE KEY
(FILE = 'C:\Test.pvk',
ENCRYPTION BY PASSWORD = 'Example12#')

These need to be moved/restored on the new server in the event of a failure. Is there anything else that I need to backup from the source server that would be needed in the event of needing to restore to another server?

Also any suggestions on private key storage ? My thought at the moment is to backup the certificate, private key, and password to a KeePass database that is backed up separately and replicated off-site.

That leaves the question though of where to backup the KeePass private key to ?


texts: There are some rows in the result of sp_who2 which their Login column value is null (see image below) while the login_name column of sys.dm_exec_sessions is not blank and this sometimes happens conversely, what is the reason of this behavior?


texts:
I have a binary blob holding the results of a SHA256 hash that I XOR with a script generated SHA256 hash. For testing I am using the values usertokens and methodtoken for the test hashes.

hash1: string(64) "439c366749b149e0238c249682e015603efa46660a3c647d08bce69a184a7ab2"
hash2: string(64) "c0196f85f4d764213504089251c6bc0726610bcf5e62fc2c1cbd7ee3aebe2d43" >
script combo: string(64) "838559e2bd662dc116882c04d326a967189b4da9545e985114019879b6f457f1"
mysql combo:  string(3) "439"

PHP:

$combo = bin2hex($hash1 xor $hash2);

MYSQL:

SELECT `hash1` ^ `hash2` as combo

As far as I can tell, the issue is that bit-wise XOR is limited to 64bit values, but I can't find a straightforward way to bi-wise XOR larger values.


texts:
I have a users table. Users can belong to a group. For them I have a group table, with id, title and description.

Now, any changes in the group table must be approved by an admin. So, I need a (preferably fast & easy) way to handle both live and pending group data.

I thought about having a status field in the group table and adding a new record whenever a group is edited. But what do I do when the edit is approved? Copy the title and description back to the live record and delete the pending one? If I delete the old record and mark the new one as live, then I need to update all users records that use the old group_id to the new one. Also, all pending records would need to reference the live records...

Is there a better solution? Part of me just want to say 'forget it' and simply duplicate the fields - have a live_title and a pending_title.

I'm using MySQL. I prefer a solution that is fast and easy to implement (I need to build an API for it).


texts: How to remove an element from a col with a CSV of ints in PostgreSQL 9.2?

texts:
I have PostgreSQL 9.2 database and a table:

id integer,
allowed_types character varying(255)`

Sample data as below:

id  allowed_types
1   3,4,5,13,14

How I can remove 4 and 5 from allowed_types which is a comma separated varchar list?

After removing, the result should be allowed_types = 3,13,14.

There are many records on that table, each allowed_types could contain different numbers, separated by comma.

I considered string_to_array() and array_remove(), but array_remove() is not in version 9.2, yet.


texts: Should I opt for a new database or adding more tables?

texts:
this is a question around best practice that I'm currently dealing with.
I have a database (SQL Server 2008 R2) with a heap of tables (around 40)
with data, now the need has risen to have logging of which Changes are made
not from a technical standpoint but for instance if a row gets updated some
users needs to have a "tracking view" in the application in which they can see
which fields were changed and when.

So to my question, should I add a new table and store all this or is it better
to add a new database to handle all history stuff in? What are the pros and cons?


texts: What does enable mean in Oracle DDL constraint?

texts:
My understanding is the different between

NOT NULL

and

NOT NULL ENABLE

is that the later will also be be applied to existing data in the table.  If then you are starting from scratch is there any point in doing

NOT NULL ENABLE

Thanks


texts: A good dataset to experiment NoSQL databases

texts:
I need to do some experiments in HBase and Cassandra and to do that I need an adequate dataset.

The dataset I'm looking for has to be large enough (i.e. more than 2GB) and the data in it has to be sufficiently unstructured to be representative of the kind of problems that relational technology can't cope. Maybe data derived from social networks, and so on.

Does anyone have that kind of dataset or knows where can I find such a dataset?


texts:
I'm trying to get postgresql notifications to work across an openvpn connection and am not having much luck.
I have a simple nodjs listener written
const pg = require('pg');

require('dotenv').config();

// declare variable to hold database connection
let pgClient;

pgClient = new pg.Client(process.env.POSTGRES_CONNECTION_URL);
console.log("Made connection");

pgClient.connect()
console.log('Got client');
const query = pgClient.query('LISTEN message');
pgClient.on('notification', async (data) => {
    const payload = JSON.parse(data.payload);
    console.log('row added', payload);
});

which, when run on the database server itself, works fine.
However, when I run this on a client connected via a vpn, I sometimes get one notification and then nothing else, or no notifications at all seem to make it through.
I could use socket.io or some other mechanism to push this data to the second client, but I can't for the life of me work out why this wouldn't work over openvpn (connecting to the db itself via psql works fine).
Any ideas? Should this work, or do I need to find another way


texts: Method (Policy Based Management, DDL Trigger, whatever) to prevent logins with sysadmin from granting sysadmin to other logins

texts:
I have scoured the internet looking for something even close to what I'm wanting to do, and all I can find is how to get around security controls to be able to grant sysadmin to a login.  (!!!)
Here's the situation; I'm a veteran DBA at a new company that has never had a DBA before, so everyone and their brother's cousin has sysadmin. That's fine, I have a plan to clean it up and reduce the number of logins that have sysadmin permission. However, now that I've started taking that permission set away from some logins, "someone" (and that's the trouble, I don't know who) is granting sysadmin to other logins.
I need a way to either report or (ideally) prevent logins that have sysadmin permission from granting sysadmin to other logins.
Of course, this is not going to be easy, because a DDL trigger for "ALL SERVER" is not going to work, because from time to time, I will need to elevate certain logins to have sysadmin (upgrade/config change/new app/etc), so my login still has to have this ability. But, all other logins, with either sysadmin or securityadmin need to be blocked from granting sysadmin (and I suppose securityadmin) to any other login...newly created or existing.

Does anyone have any idea how to go about doing this? PBM may be asking too much, because I've never been able to do anything useful with it, outside of a training class. Perhaps using a DDL trigger(s) would get the job done (???).

Thanks in advance for any suggestions and/or advice.

P.S. I need to be able to log/prevent this for both sp_addsrvrolemember and ALTER SERVER ROLE, as we have 2008 R2 instances along with 2012+ instances.


texts: Does a SQL Server update nvarchar statement overwrite the same address on disk if the new value is the same size?

texts:
Is it possible to overwrite the same disk address in a T-SQL update statement? A use case would be preventing recovery of the original text on disk for security or anonymity reasons.
CREATE TABLE Test(
  Id int,
  Message nvarchar(1000))
GO

INSERT INTO Test (Id, Message) VALUES (1, 'Hello!')
GO

So at this point, 'Hello!' is written to disk. If an update statement is run, can it be guaranteed (or at least highly likely) to overwrite the same location on disk?
UPDATE Test SET Message = '000000' WHERE Id = 1
GO

In the SQL Server implementation, what is the probability that the original 'Hello!' value will no longer be on disk?
I'm assuming the new value would be the same size. My guess is that if the new value were of a different size, then SQL Server would write the updated value to a new location, leaving the 'Hello!' in the original location, but now marked as free. I also assume that a delete does nothing to the original value on disk, but just marks the location as free.
If this is not the case, or not guaranteed, or at least highly-likely, Is there another way to remove the value from disk?
The requirement here is not to the level of keeping government secrets safe. It's more of a marketing claim that deleted items are truly gone and can't be recovered.
I understand there are a lot of caveats here. I know the SQL language does not address this. I'm asking specifically about SQL Server's implementation. The answer could be that it's unknowable. Just wondering if there's documentation, or common knowledge, or if someone has tested implementation specifics.
We're using a hosting service that doesn't encrypt the SQL Server disk. We do encrypt the value before writing to the db. Just looking into a 'belt and braces' solution.


texts: I need to execute update querys routinely over a Postgres table. Is it possible to program an automatic execution of the query, let's say, every day at 15:00 hs? The Postgres version is 9.5, I'm working on Windows 7.


texts: Application specific users table or mysql.users

texts:
I am using a mysql installation that handles an application with multiple users. These users have varying permissions for table access, but also need to be referenced within tables in the application database. For example, I have a manufacturer table that stores the person who manufactured a batch, but different manufacturers have different access to the tables.
If I weren't referencing them within the application tables I would use the mysql.users table to handle permissions, and if I weren't giving users variable permissions then I would use a local users table.
Is there a standard and secure way of handling this sort of situation?


texts: Overview of leaked/unclosed cursors/statements on DB2 LUW server?

texts:
We're encountering a "too many open cursors issue" on DB2/LUW.

Is there any way to get a (database-server-side) list of the corresponding SELECT statements related to these open cursors?

We're doing that on Oracle all the time.

I know we could review the application logic but that is an awful lot harder in our particular case. So I wanted to get this question out first. Thanks.


texts: Create hot standby on top of Barman streaming backup

texts:
I set up a Barman server that uses pg_basebackup and pg_receivewal for streaming replication. It is pretty much the same setup as described in the Barman documentation (scenario 1).
Is there a straight-forward way to build a hot standby based on this setup? I would like to query the standby for data analyses.
I am using Barman 2.11 and PostgreSQL 12.4.


texts: Is there some drawback to define SPARSE columns?

texts: I have a table which has a lot of NULL values in columns. But some columns don't contain NULLs at all (although nullable). Is there some drawback to declare all of these columns as SPARSE?


texts: Storing arrays of integers in database (for efficient select)

texts:
I am creating a database that will store 100.000 (and probably more in the future) users. While this obviously happens in a table with 1 row per user, every user can (and will) store hundreds of items. In programming language this would mean the user has 2 arrays (or one 2-dimensional array) of integers: a column for the itemid's and a column for the amounts.

My instincts tell me to create a table to hold all these items, with rows like (userid, itemid, amount). However this would result in a huge table. 200.000 users with 250 items each... that's 50 million entries in one table. This, plus the fact that the table will undergo continuous and rapid change, frightens me. (How rapid? I estimate up to 100 modifications per second.)

Typically there will be anywhere between 100 and 2000 users, all adding and removing items, and modifying amounts. These actions can and will happen in programming code. It would go as follows:

User starts session, program loads all the users items from the database
User modifies the item list
Every few minutes, the changes are saved into the database
When the user ends the session, it is also saved into the database

It is worth noting that there is a maximum to the number of items a user can store.

Are there any alternatives to using a separate table? Perhaps save the values in a formatted text string? Or is this one of the instances where using a MySQL database is actually a Bad Idea™?

Thank you for your time and insights.


texts:
Let's say I have a database with a single .mdf and .ldf file.

The database is detached, there are no transactions and SQL Server is stopped.

I am presuming that all the data is already in the .mdf file?

The .ldf file is essentially empty? If the .ldf file is not empty what does it contain?

If I delete the .ldf file and manually create a new 0 bytes long .ldf file with same name as before, I would be able to attach the database again with no data loss?


texts: Database design for a lot of rows with data archive

texts:
i'm new to database design on larger scale. I have some knowledge and experience with MySQL but now i have some performance issues.

Currently i'm using MySQL with 3 databases:

History
Snapshot
Static

In history database i'm inserting data. For one user there are approx 5mio rows on month (users are slowly growing every week). As my select queries have gotten slow probbably due to large amount of data i have triggers that copy inserts into snapshot database and delete older entires. Select statements are very quick on snapshot database. But as i add more triggers, mysql seems to fail to whitstand the load. The static database is just users table and some metadata.

So my question is, how can i achieve push/pop with my tables using MySQL or any other database technology?

I want to insert data into snapshot database and all data that has timestamp older than 12 hours should be moved to history (for later data analysis - once or twice a week) and deleted from snapshot so it keeps minimal amount of data to work with. Is this achievable with MySQL or something else? What would be recommended hardware setup for such database design?

As of writing and reading from database i do most of writing from python with pymysql, also some reading and most of reading with NodeJS mysql plugin.

I hope there is some not too complicated solution for my issue. But i will be happy with any advice or guide direction on that to use.


texts: How can I delete duplicates records in a table efficiently

texts:
I know there are several options to delete duplicate rows,  eg:

ROWID
Analytical fn's like Rank(), row_number()

What would be the most efficient way?


texts: How to calculate streaks of consecutive dates for a user

texts:
Assume a results table like so:

date|user_id

A user can have one or more result per day..

I need to calculate a) users who have entered a result in 30 or more consecutive days b) preferably the longest current streak for a specific user.

The results table is large (2 million results) with approx 50k users.

Alternatively, if this is too difficult, any suggestions of a different way of working this out?

(e.g. create a 'streak' table with user_id, streak number, start date etc and then update that on insert into results, but you'd still need to know if somebody entered a result whether that was part of a streak etc)


texts: Replacing SQL Server TDE soon expiring certificate

texts: What is the best way to replace a soon expiring certificate? Downtime should be avoided. Databases size is 100GB.


texts: How does functional index in MySQL 8.0.13+ improve query performance?

texts:
Quoting from 13.1.15 CREATE INDEX Syntax:

  MySQL 8.0.13 and higher supports functional key parts that index expression values rather than column or column prefix values.

See "Functional Key Parts" section of that manual page for detail. Basically, it enables us to create an index consisting of complex expression values derived from columns rather than mere column values (or their prefixes).

However, on a following part, that page also states:

  Functional indexes are implemented as hidden virtual generated columns [...]

In 13.1.20.8 CREATE TABLE and Generated Columns, it says:

  The VIRTUAL or STORED keyword indicates how column values are stored, which has implications for column use:

  VIRTUAL: Column values are not stored, but are evaluated when rows are read, immediately after any BEFORE triggers. A virtual column takes no storage.

  [...]
  STORED: Column values are evaluated and stored when rows are inserted or updated. A stored column does require storage space and can be indexed.

So it looks like (at least to me) the functional index is not stored at all, and only built on the fly during executing a query. Is this how functional index is used? If this is true, how could this "virtual" index improve query performance (I mean even the index itself has to be built during query)?


texts: SQL Server 2012 Browser Service doesn't appear

texts:
I've installed SQL Server 2012 and Management Studio Express. I attached the database that belongs my previous Project in Visual Studio. But I am having SqlException Error , I think the reason of the error is SQL Server. Because when I look at active services, I only see SQL Server VSS Writer. I can't see SQL Server Browser Service and other SQL Server services. How can I solve this problem? Thanks in advance.

Log file:

  Overall summary:
  Final result:                  Passed
  Exit code (Decimal):           0
  Start time:                    2014-07-27 21:42:30
  End time:                      2014-07-27 21:45:33
  Requested action:              Patch

Machine Properties:
  Machine name:                  KEREM
  Machine processor count:       8
  OS version:                    Future Windows Version
  OS service pack:
  OS region:                     Türkiye
  OS language:                   Türkçe (Türkiye)
  OS architecture:               x64
  Process architecture:          64 Bit
  OS clustered:                  No

Product features discovered:
  Product              Instance             Instance ID                    Feature                                  Language             Edition              Version         Clustered
  SQL Server 2012                                                          Management Tools - Basic                 1033                 Express Edition      11.0.2100.60    No
  SQL Server 2012                                                          LocalDB                                  1033                 Express Edition      11.1.3000.0     No

Package properties:
  Description:                   Microsoft SQL Server 2012 Service Pack 1
  ProductName:                   SQL Server 2012
  Type:                          RTM
  Version:                       11
  SPLevel:                       1
  KBArticle:                     KB2674319
  KBArticleHyperlink:            http://support.microsoft.com/?kbid=2674319
  PatchType:                     SP
  AssociatedHotfixBuild:         0
  Platform:                      x64
  PatchLevel:                    11.1.3000.0
  ProductVersion:                11.0.2100.60
  GDRReservedRange:              10.0.1000.0:10.0.1099.0;10.0.3000.0:10.0.3099.0
  PackageName:                   SQLServer2012-KB2674319-x64.exe


texts: How can I prevent users from overwriting databases when restoring?

texts: I don't want users to restore (with overwrite) on some specific databases. How can I inhibit from restoring over these databases?


texts: How do I fix SQL Server assembly optimization errors and memory exhaustion?

texts:
The .NET Runtime Optimization Service (mscorsvw) keeps consuming all my workstation's memory since I installed SQL Server 2012 Service Pack 1.

It consumes about 1.6 GB of memory before exhausting all available memory:

I can kill the process, but it happens again when I restart my computer or wait long enough.

Techdows suggests using the .NET Native Image Generator (ngen) to solve the problem.

I oipened a Command Prompt and ran commands like this:

cd C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319.1
ngen executeQueuedItems

The memory consumption of mscorsvw dropped to about 20-30 MB, and ngen consumed all the remaining memory.

ngen generated only error messages until I killed the process. The first few lines of output looked like this:

Microsoft (R) CLR Native Image Generator - Version 4.0.30319.1
Copyright (c) Microsoft Corporation.  All rights reserved.
Failed to load dependency Microsoft.DataWarehouse of assembly
Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91 because of the following error : The system
cannot find the file specified. (Exception from HRESULT: 0x80070002)
Failed to load dependency Microsoft.VisualStudio.DataTools.Interop of assembly
Microsoft.DataWarehouse, Version=11.0.0.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91 because of the following error : The system
cannot find the file specified. (Exception from HRESULT: 0x80070002)
Failed to load dependency Microsoft.Data.ConnectionUI of assembly
Microsoft.DataWarehouse, Version=11.0.0.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91 because of the following error : The located
assembly's manifest definition does not match the assembly reference.
(Exception from HRESULT: 0x80131040)

All the messsages say either that the system could not find an assembly, or that a manifest definition does not match a reference.

As a workaround, I have disabled the Windows Services clr_optimization_v4.0.30319_32 and clr_optimization_v4.0.30319_64.

This hides the problem rather than solving it, and stops all .NET assemblies from being optimized.

What else can I try to solve this problem?


texts:
When I create a stored procedure in SQL Server 2008 by using the template, the comments specify a hot-key combination that no longer works:

-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.

I can use the keyboard combination ALT+Q, S to get the same results. How can I update the template so that it references the working combination rather than the deprecated one?

I sometimes go for long stretches between working with stored procedures, and I don't always remember the correct combination. It would be nice not to have to search for it each time this happens.

EDIT: I tried Jon's suggestion of opening up the template from the template explorer. I was surprised to find the correct combination listed in the template. Is there a way to force SSMS to use the correct template?


texts: Need urgent help with recovering data from dropped database

texts:
I just made a huge mistake by doing a rails command on my production web  server which reset the postgres database - I believe the database is dropped and reset with just the schema

I have a backup from a day ago, but there's still a lot of critical data I'll be missing from the last 24 hours including transaction data. I'm really confused on what to do. I can't recover everything by just going through the HTTP logs as there are some background jobs that ran as well.

I'm running postgres 9.6, I've looked at past stackoverflow posts, but I don't know if the data is still there somewhere.

I stopped postgres as soon as I found out(maybe 10 mins after I issued the command)

There are some WAL files in the "pg_xlog" directory, is it possible to recover from them?

I also saw this post: https://dba.stackexchange.com/a/49118 that says there's a way to inspecting the pageinspect module - is that worth looking into?

Is there any way to get a sql transaction history or something from the last 24 hours?

I wanted to ask this question first before restarting postgres and maybe killing whatever chance I had.

I already asked the question here https://stackoverflow.com/questions/42962303/dropped-database-with-rake-dbschemaload-any-way-to-recover-data and suggested to post here.

There is no pg_base_backup. Simply a pg_dump from 24 hours ago.


texts: Optimizing MySQL query with some LEFT OUTER JOINS

texts:
I have a classifieds website and want to optimize the most intensive query in my server. I don´t have so much experience with MySQL (or any other DB).

This is the complete listing ads:

SELECT
    `category`.`id` AS `category_id`,
    `category`.`subcat` AS `category_name`,
    `category`.`desc` AS `category_desc`,
    `category`.`name` AS `category_pretty_name`,
    `ads`.`id`,
    `ads`.`header`,
    `ads`.`price`,
    `ads`.`oldprice`,
    `ads`.`sellfast`,
    `ads`.`foto1`,
    `ads`.`subcat_id`,
    `ads`.`updated`,
    `ads`.`created`,
    `ads`.`name`,
    `ads`.`phone`,
    `ads`.`hits`,
    `rating_ad`.`num_rates` AS `num_rates`,
    `rating_ad`.`average_rating` AS `average_rating`,
    SUBSTR(ads.body, 1, 160) AS body,
    `ads`.`user_id`,
    `ads`.`hash`,
    `ads`.`hidden`,
    `ads`.`email`,
    `promo`.`promotype` AS `promo_type`,
    `promo`.`end_promo`,
    `supercategory`.`name` AS `supercategory_name`,
    `supercategory`.`id` AS `supercategory_id`
FROM
    `ads`
JOIN `category` ON `category`.`id` = `ads`.`subcat_id`
JOIN `supercategory` ON `supercategory`.`id` = `category`.`cat`
LEFT OUTER JOIN `promo` ON `promo`.`ads_id` = `ads`.`id`
LEFT OUTER JOIN (
    SELECT
        ad_id,
        TRUNCATE (AVG(rating), 2) AS average_rating,
        COUNT(user_id) AS num_rates
    FROM
        ads_rating
    GROUP BY
        ad_id
    HAVING
        COUNT(user_id) >= 1
) AS rating_ad ON `rating_ad`.`ad_id` = `ads`.`id`
WHERE
    `ads`.`sellfast` = '1'
AND `ads`.`subcat_id` = '19'
AND `recycle_bin` = 0
AND `hidden` = 0
ORDER BY
    `promo_type` DESC,
    `updated` DESC
LIMIT 100;

When I do the EXPLAIN Query, this is the result:

So, I see in the Slow queries logs this is consuming about 0.350 secs. Is this optimizable?

This is the SHOW CRETE TABLE:

CREATE TABLE `ads` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subcat_id` mediumint(2) NOT NULL,
  `province_id` mediumint(2) NOT NULL,
  `price` float(12,2) NOT NULL,
  `oldprice` float(12,2) NOT NULL,
  `type` enum('1','2','3') NOT NULL,
  `header` varchar(200) CHARACTER SET latin1 NOT NULL,
  `body` text CHARACTER SET latin1 NOT NULL,
  `email` varchar(150) CHARACTER SET latin1 NOT NULL,
  `public_email` varchar(150) NOT NULL,
  `name` varchar(50) CHARACTER SET latin1 NOT NULL,
  `phone` varchar(30) CHARACTER SET latin1 NOT NULL,
  `user_id` int(6) NOT NULL,
  `created` datetime NOT NULL,
  `foto1` varchar(150) CHARACTER SET latin1 DEFAULT NULL,
  `foto2` varchar(150) CHARACTER SET latin1 DEFAULT NULL,
  `foto3` varchar(150) CHARACTER SET latin1 DEFAULT NULL,
  `foto4` varchar(150) CHARACTER SET latin1 DEFAULT NULL,
  `foto5` varchar(150) CHARACTER SET latin1 DEFAULT NULL,
  `updated` datetime NOT NULL,
  `hash` varchar(32) CHARACTER SET latin1 NOT NULL,
  `hits` int(6) NOT NULL,
  `new` tinyint(1) DEFAULT 0,
  `sellfast` tinyint(1) DEFAULT 0,
  `website` varchar(250) CHARACTER SET latin1 DEFAULT NULL,
  `tags` varchar(250) DEFAULT NULL,
  `brand_id` smallint(2) NOT NULL,
  `hidden` smallint(1) NOT NULL,
  `recycle_bin` smallint(1) NOT NULL,
  PRIMARY KEY (`id`,`hash`),
  KEY `header` (`header`) USING BTREE,
  KEY `tags` (`tags`) USING BTREE,
  KEY `phone` (`phone`) USING BTREE,
  KEY ` subcat_id` (`subcat_id`) USING BTREE,
  KEY `province_id` (`province_id`) USING BTREE,
  KEY `hidden` (`hidden`) USING BTREE,
  KEY `recycle_bin` (`recycle_bin`) USING BTREE,
  KEY `price` (`price`) USING BTREE,
  KEY `email` (`email`) USING BTREE,
  KEY `created` (`created`) USING BTREE,
  KEY `updated` (`updated`) USING BTREE,
  KEY `user_id` (`user_id`),
  KEY `new` (`new`),
  KEY `sellfast` (`sellfast`)
) ENGINE=MyISAM AUTO_INCREMENT=839759 DEFAULT CHARSET=utf8

Which task most do to made it?


texts: multi-condition left join conditions in MS access

texts:
Is there another way to write the following poorly performing in Access (Jet) SQL?

SELECT *
FROM
  Det left join
  (select * from Inv inner join Dep on Inv.SN=Dep.SN) as mass ON
    (Det.RecSN = mass.SelfID) and (Det.DDTime between mass.Start and mass.End)

eliminating the sub-select and doing a direct join does not work, giving a JOIN expression not supported Error in the following snippet

SELECT *
FROM
  Det left join
  (Inv inner join Dep on Inv.SN=Dep.SN) ON
    (Det.RecSN = Inv.SelfID) and (Det.DDTime between Dep.Start and Dep.End)

The following produces a result, but suppresses Det records that don't match up to one or the other of the inner tables

SELECT *
FROM
  Det inner join
  (Inv inner join Dep on Inv.SN=Dep.SN) ON
    (Det.RecSN = Inv.SelfID) and (Det.DDTime between Dep.Start and Dep.End)

adding another set of parentheses doesn't help other than helping Access highlight the join clause that's problematic

SELECT *
FROM
  Det left join
  (Inv inner join Dep on Inv.SN=Dep.SN) ON
    ((Det.RecSN = Inv.SelfID) and (Det.DDTime between Dep.Start and Dep.End))

dropping the between clause to a where condition results in no left join characteristics (DDTime isn't between NULL and NULL) and can lead to too many entries being held in memory while other joins are being performed to this whole mess, and can make it more logically difficult to add other where conditionals.

SELECT *
FROM
  Det left join
  (Inv inner join Dep on Inv.SN=Dep.SN) ON
    Det.RecSN = Inv.SelfID
WHERE
  Det.DDTime between Dep.Start and Dep.End

Adding a clause to make left join behavior re-emerge (but only for the portion in the ON clause) makes the world slow to a crawl, I think because Access doesn't know how to handle OR clauses without essentially running a union all in the background for each of the sides of the OR clause with the rest of the query.

SELECT *
FROM
  Det left join
  (Inv inner join Dep on Inv.SN=Dep.SN) ON
    Det.RecSN = Inv.SelfID
WHERE
  Det.DDTime between Dep.Start and Dep.End
  OR Dep.Start is NULL

My current work-around is to use the inner join method, then have another query that queries this one ("qCombo") and compares against the Det table for entries in Det, but not in qCombo using either a left join or where not exists. But this solution is just as inefficient (if not more so) than the sub-select method at top.

SELECT 'qCombo' as QueryName, *
FROM
  Det inner join
  (Inv inner join Dep on Inv.SN=Dep.SN) ON
    (Det.RecSN = Inv.SelfID) and (Det.DDTime between Dep.Start and Dep.End)

Select Det.*
FROM
  Det LEFT JOIN
  qCombo ON
    (Det.DDTime=qCombo.DDTime) and
    (Det.OtherFieldsInUniqueIdx=qCombo.OtherFieldsInUniqueIdx)
WHERE
  qCombo.QueryName IS NULL

Other possibilities?


texts: Restore databases from prod to test server with data over X days

texts:
We've got a list from app owners to restore say 20 databases from a total of 100 available production server databases onto a test server on weekly basis.

Moreover, they require it in a way that backup that would be occurring should contain data of last 60-90 days for all the above 20 databases.

I am not sure if that can be achieved. If yes, kindly give suggestions how can I go around this to automate the backups as said above.

For automating the restore process, should I go with Log Shipping or Transactional replication? We have space issues on test and the client does not want to invest much!


texts: Good practice for hiding primary keys from a client

texts:
Let's say I have a table like this:

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);

Later on in my client, I may fetch a RESTful endpoint like this:

GET /v1/users/1

The query to retrieve the users would look like this:

SELECT * FROM user WHERE id = 1;

I want to avoid the approaches above so I can hide primary keys from user-facing web apps.

I thought I could do something like this instead to solve that problem:

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    _id VARCHAR(20) NOT NULL UNIQUE
);

The HTTP call now becomes something like this:

GET /v1/users/a129fjf24f0jwef94f

...and the query looks like this:

SELECT * FROM user WHERE _id = a129fjf24f0jwef94f;

What are the downsides to such an implementation at the database level, if any, other than excess storage? I would also have to use the _id on queries where I involve joins of other tables, etc.


texts: mysql gtid_executed and gtid_purged variables

texts: I would like to know where the gtid_executed and gtid_purged variables are stored in the database engine. If these are just runtime variables how exactly are they initialized?


texts:
I installed MySQL Workbench the other day, accessed my company's database and made myself a table to work with. So far so good. The problem is, I noticed my auto_increment is incrementing 2 by 2. For example:

ID    NAME
1     Paul
3     Jack
5     Louis
7     John
...

When I do SHOW VARIABLES LIKE 'auto_inc%' I get this:

'auto_increment_increment', '2'
'auto_increment_offset', '1'

So I tried setting auto_increment_increment to 1 with:

SET @@auto_increment_increment=1

And after verifying again with SHOW VARIABLES LIKE 'auto_inc%' I confirmed it "worked" with the result:

'auto_increment_increment', '1'
'auto_increment_offset', '1'

But my ID's are still being incremented in 2 by 2.

The first time I did it, it worked well and then I closed MySQL Workbench to realize that when I opened it again, auto_increment_increment was set to 2 again. Now I'm trying to do it again, but it doesn't even seem to work anymore.

Can anyone help me with this, please?

Thanks guys.


texts: Do I need to perform db.fsyncLock() when running a replica set without journal enabled

texts:
I'm running MongoDB 3.2.9 with WiredTiger on 3 EC2 instances.  When backing up the database volumes using elastic block store snapshots, do I need to perform db.fsyncLock() when running a 3 node replica set where all nodes are running without journal enabled?  Additionally, for reference, my storage.dbPath maps to a dedicated EBS volume for /data on each mongod instance on each EC2 server.  There are also separate dedicated volumes for logs.

I'm working off of the below documentation for reference, but it's not clear how these steps map from single instance deployments to replica set deployments:
https://docs.mongodb.com/ecosystem/tutorial/backup-and-restore-mongodb-on-amazon-ec2

If I do need to run db.fsyncLock(), does the lock apply to all instances on all EC2 servers?  The documentation says that it locks the 'entire' mongod instance, but it's not clear to me whether the instance in this case refers to an instance on a single EC2 server, or if this applies to all instances in the replica set spread over all EC2 servers (3 EC2 servers in my case).


texts: How should permissions for anonymous users be modelled?

texts:
I'm designing a web application. There will be users who log into the site, but also anonymous, non-authenticated users, i.e. any member of the public who accesses the site. Users will be assigned to groups, and those groups will be assigned permissions. Some site content will be accessible only to authenticated users, while other content may be marked as publicly accessible. I'm considering how best to model facts such as "this item is publicly accessible". Options that occur to me so far:

Create a special group that a special user "Anonymous" belongs to. Assign permissions to the "Anonymous" group, and pretend that unauthenticated users are all the Anonymous user.
On each content entity, include an attribute such as is_publicly_accessible.

Any thoughts on which is the right way to go? The first option feels like a more consistent approach to permissions, but the notion of some users and groups being special/fake/dummy doesn't feel right.


texts: Not able to login to MySQL server through MySQL workbench

texts:
I have installed MySQL 5.7 server on Linux machine and am able to login to the database within the server, but not able to connect to it from Workbench.

I have disabled Iptables. Port 3306 is listening.

**root@master:~#** lsof -i TCP:3306
COMMAND  PID  USER   FD   TYPE  DEVICE SIZE/OFF NODE NAME
mysqld  2563 mysql   27u  IPv4 1905031      0t0  TCP localhost:mysql (LISTEN)

**root@master:~#** service ufw status
* ufw.service - Uncomplicated firewall
   Loaded: loaded (/lib/systemd/system/ufw.service; enabled; vendor preset: enabled)
   Active: inactive (dead) since Mon 2017-02-06 11:28:42 UTC; 9min ago
  Process: 2947 ExecStop=/lib/ufw/ufw-init stop (code=exited, status=0/SUCCESS)
  Process: 2814 ExecStart=/lib/ufw/ufw-init start quiet (code=exited, status=0/SUCCESS)
 Main PID: 2814 (code=exited, status=0/SUCCESS)

Feb 06 11:28:01 movimento-hadoop-master systemd[1]: Starting Uncomplicated firewall...
Feb 06 11:28:01 movimento-hadoop-master systemd[1]: Started Uncomplicated firewall.
Feb 06 11:28:42 movimento-hadoop-master systemd[1]: Stopping Uncomplicated firewall...
Feb 06 11:28:42 movimento-hadoop-master ufw-init[2947]: Skip stopping firewall: ufw (not enabled)
Feb 06 11:28:42 movimento-hadoop-master systemd[1]: Stopped Uncomplicated firewall

Could anyone please help me?


texts: Designing a database tables for the following scenario

texts:
I just got following entities Employee, Client and Company. Where Employee and Client have one to many relationship with Company  ie A single employee may be mapped to attend to many companies and same for clients too. How would i design a optimized table for this situation.
I had thought of below

Employee:

Id

Name

CompanyId

but since it is one to many companyid would have to be saved as comma seperated company id's. What do i do in this situation.


texts:
I have the following query to track what tables are modified in the current date:

SELECT TABLE_OWNER,
       TABLE_NAME,
       INSERTS,
       UPDATES,
       DELETES,
       TIMESTAMP AS `LAST_CHANGE`
FROM  ALL_TAB_MODIFICATIONS
WHERE TO_CHAR(TIMESTAMP,'DD/MM/YYYY') = TO_CHAR(sysdate,'DD/MM/YYYY')
ORDER BY LAST_CHANGE DESC;`

Result

As you can see the query works fine , but I want to add more complexity like  adding a field  "LAST_CHANGE_TYPE" showing what was the last transanction type (addded, deleted or modified) in the lastest process.

It would be good too another query that can be usefull to monitoring my DW.

Like monitor all posible actions , to track what user made for example:

drop constraints if its posible specify what kind of constraint
Modify Packages Body

When the actions are listed, its necessary to see the user and date of each action.

| Object     |Type   | Source   | Operation Type  | User  | Date      |

| Constraint | PK    | Table A  |  Drop           | Scott | 11/11/2011|

| Table      | Table | Table B  |  Alter          | HR    | 05/05/2002|

Currently open to all suggestions


texts:
We are experiencing issues when creating a database in the SSMS GUI in that sometimes it times out complaining that it could not get an exclusive lock on the Model database.  Other times the database gets created but does seem to be taking a long time.

I have also added the Service Account to the Perform Volume Maintenance local security policy.

This is a brand new clustered SQL server with no other processes running on it at the moment.

I have queried the wait types and the largest is LCK_M_S

I have queried sys.dm_tran_locks during the time when it does get created and can see two locks

Is this correct, does anyone have any advice why the DB creation may be taking so long?

Same issues when using code, being the following:

CREATE DATABASE [TestTom]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'TestTom', FILENAME = N'G:\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestTom.mdf' , SIZE = 1048576KB , FILEGROWTH = 1048576KB )
 LOG ON
( NAME = N'TestTom_log', FILENAME = N'F:\MSSQL11.MSSQLSERVER\MSSQL\Data\TestTom_log.ldf' , SIZE = 1048576KB , FILEGROWTH = 1048576KB )
GO
ALTER DATABASE [TestTom] SET COMPATIBILITY_LEVEL = 110
GO
ALTER DATABASE [TestTom] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [TestTom] SET ANSI_NULLS OFF
GO
ALTER DATABASE [TestTom] SET ANSI_PADDING OFF
GO
ALTER DATABASE [TestTom] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [TestTom] SET ARITHABORT OFF
GO
ALTER DATABASE [TestTom] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [TestTom] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [TestTom] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [TestTom] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [TestTom] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [TestTom] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [TestTom] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [TestTom] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [TestTom] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [TestTom] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [TestTom] SET  DISABLE_BROKER
GO
ALTER DATABASE [TestTom] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [TestTom] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [TestTom] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [TestTom] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [TestTom] SET  READ_WRITE
GO
ALTER DATABASE [TestTom] SET RECOVERY SIMPLE
GO
ALTER DATABASE [TestTom] SET  MULTI_USER
GO
ALTER DATABASE [TestTom] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [TestTom] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO
USE [TestTom]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [TestTom] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO


texts: How to create column group in WiredTiger engine in mongodb 2.8?

texts:
MongoDB 2.8 supports WiredTiger strorage engine, and WiredTiger supports column grouping.

How we can create a collection in mongodb 2.8 with grouped columns?


texts: SQL Server - Analyzing Multiple Stored Procedure Statistics

texts:
I'm trying to analyze 1000+ stored procedures and I need to find out the:

Elapsed Time in Sec (AVG based on # of iterations)
CPU (AVG based on # of iterations)
Logical Reads

At first I'm using SQL Query Stress, but it will take me so much time to test-run each stored procedure and analyzing test parameters.

So my question is, is there an easier way to get what I needed?


texts: get result of search in database postgresql and work with it every time

texts: I have a query who spend alot of time because there is thousand of ligne in the database, so I need if it's exist a method to put the result of the query some where and work with them the time i want. thanks


texts: How to add a constraint to a table using a partial index?

texts:
I have a simple table:

create table item (
  lan      text not null,
  disabled date
);

My aim is to ensure uniqueness for the lan column over all rows, where disabled is null.

I created a partial index:

create unique index lan_idx on item (lan) where disabled is not null;

Now I tried to add the index to the table, but I am struggling with the syntax. (I took the code from this tutorial)

alter table item add constraint lan_idx using index lan_idx;

This throws an syntax error before "using".

I also tried the following:

alter table item add constraint lan_idx unique using index lan_idx;

But this throws the error, that a unique constraint can not be created with a partial index.

Can anybody tell me what I did wrong?


texts: Lack of cached execution plans when using `IN` clause

texts:
Microsoft's documentation on Caching Mechanisms contains a description of Autoparameterization. As I understand it, Autoparameterization allows SQL Server (under certain circumstances) to use a previously cached execution plan for a query even if the specific parameters for that instance of the query are unique.

However, the documentation on that page states that "there are many query constructs that normally disallow autoparameterization". One of these constructs is the IN clause.

I have several queries in my application that use the IN clause. Each of these queries could have an arbitrary number of values in the IN clause. If I understand the documentation referenced above, then the execution plans for these queries will not be cached.

Questions:

1) Is there a recommended way to write these sorts of queries in order to bypass this limitation and take advantage of cached execution plans (other than using forced parameterization at a database level)?

2) How can I evaluate the impact that these queries are having on the overall performance of the database (e.g., how can I answer quantitatively whether or not it is worth my time to worry about the lack of cached execution plans)?

Thank you in advance for any insight you can provide.


texts: MySQL DB dynamic ranking or "static" separately saved

texts:
I have a table where I store the points with the connected categories and game-types of each user. There are different ways to get points. However everytime when the points need to get displayed in a ranking I get them in an SELECT statement, where I group and add them up.

I was wondering if it is more efficient to store and update the points of a user in a table everytime the points change. (Since I need the individual points and game-stats aswell I would still store them in the table I mentioned above.) But the SELECT statement would be simpler and less complicated.
However I think the way I am doing this is the favorable one but since I did not find any post about that I wanted to seek opinion of others.

I hope this is understandable.

Thank you.


texts: Best relational database structure for this data

texts:
I'm in the process of creating a database scheme for the following scenario:

There are users
Users have roles (such as "Developer" or "CEO")
Roles have applications (such as "Topdesk")
Applications have permissions (such as "Update Knowledgebase")
A role can have permissions, if the role already has access to the application

Assuming no high-performance environment (no need to optimize for speed), what would be the best way to implement this schema? The database environment can be MySQL, MSSQL... it's more about the relational database design.

I myself have come up with the following:

The part I'm most uncertain about is of course the Applications_Permissions_Roles table. It is a linking table on top of another linking table. I've never used or seen this before. Another way to do it would be to replace it with a linking table between Roles and Permissions, and then use code or constrains to ensure the required relations... but that doesn't seem like a good solution to me. These things should be enforced on database-level if at all possible (and it seems possible), not on code-level.

Secondly, is the link between Permissions.Application and Applications.Id required? I use it because there may not be any rows in Roles_Applications (such as when you've just added a new application) and then it's not possible to work out which permissions belong to which application. It also is a single point of reference to lookup to which application a permission belongs. I guess this is right, but it also makes a circle in the database design. MSSQL errors on it when trying to set ON_DELETE or ON_UPDATE to cascade.

Any suggestions, or is this how it's supposed to be done? Any other suggestions regarding naming convention and such are also welcome by the way (perhaps as comment).

Thanks,
Luc

Edit: Changed title, hopefully making it clearer. The previous one was more comprehensive, but probably too convoluted.


texts: Columnstore index query performance based on number of columns in SELECT statement

texts:
I have a table 'FactsTable1' which is horizontally partitioned on basis of date & it also has clustered columnstore index on it. I am running following queries on it.

Query 1 :

SELECT
    SUM([Column1]),
    SUM([Column2]),
    SUM([Column3]),
    SUM([Column4]),
    SUM([Column5]),
    SUM([Column6])
FROM [FactsTable1]

Query 2 :

SELECT
    SUM([Column1])
FROM [FactsTable1]

When I ran above 2 queries with 'STATISTICS IO,TIME ON', I received following as output.

Query 1 :

SQL Server parse and compile time:
   CPU time = 94 ms, elapsed time = 95 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.

(1 row(s) affected)
Table 'FactsTable1'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 1184, lob physical reads 31, lob read-ahead reads 0.
Table 'FactsTable1'. Segment reads 22, segment skipped 0.

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

Query 2 :

SQL Server parse and compile time:
   CPU time = 46 ms, elapsed time = 57 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.

(1 row(s) affected)
Table 'FactsTable1'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 198, lob physical reads 0, lob read-ahead reads 0.
Table 'FactsTable1'. Segment reads 22, segment skipped 0.

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

I can see some difference always in CPU time when checked multiple times but I dont see any difference in query cost. Its always 50-50%.

So I want to know, does number of columns affect performance of a query for a partitioned table with clustered columnstore index? If yes, then is there any way to measure performance difference?

Additional information :

FactsTable1 contains 400000+ rows & 240 columns
Its partitioned on date column
It is heavily queried for analytic data.
@@version : Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)


texts:
Can anyone give me a complete guide to verifying/repairing a SQL Server database?

At the moment our backup is failing on the Solarwinds database saying the database NetPerfMon is corrupted (we are using Backup Exec 12.5 to perform the backups). I have SQL Server 2005 Management Studio Express installed if that helps other than that I don't have much of an idea what to do with SQL other than little bits of dabbling with it.


texts:
I am following the training kit for 70-462(Administering Microsoft SQL Server 2012 Databases), and stuck on Auditing SQL Server instances lesson. I have created new user in domain controller, assign it Generate Security audits and Log on as a service rights. Then I installed SQL Server instance on one of the machine in the domain with this account. I checked installed instance service and it is started under this account. I have also followed the steps here http://www.sqlskills.com/blogs/jonathan/resolving-error-33204-sql-server-audit-could-not-write-to-the-security-log/.

But when I am enabling the audit it logs the error:

  Sql Server audit could not write to the security log

Version: SQL Server 2012 - 11.0.2100.60(X64) Enterprise Edition (64-bit) on Windows NT 6.1 <X64>(Build 7601: Service Pack 1) (Hypervisor)

What else can I check?


texts: Naming Convention for Login, User and Roles?

texts:
What are the Naming Conventions for a Login, A database User and a Database role?

Is there any best practice to follow?


texts: Replay (re-execute) MySQL SELECT queries from a log file

texts:
MySQL Benchmarking

I would like to evaluate the performance of a different MySQL instance by re-executing real queries from log files. I am aware of tools like mysqlslap, that produces random and autogenerated queries, but I would rather like to test the system with realistic queries.

In order to benchmark the new system, I would ideally collect all queries either using the slow log or the general log and then replay the queries to the new system.

It should be possible to filter queries, for instance I would like to replay SELECT statements only. I am wondering if there exists an actively maintained procedure or a best practice that how to achieve warm-up or benchmark functionality.

Requirements

Replay (re-execute) real queries collected from a running MySQL instance
Filter statements by statement type (INSERT, UPDATE, DELETE) and DML
Get statistics (how long did the query run before, after the migration etc)

Problem: Tools are outdated

Percona offered several tools that seemed to be designed for that purpose.    All of them have been removed from the current Percona Toolkit. These tools were:

pt-log-player
The replay option for pt-query-digest
query-playback

Potential solutions

Using a Debian Wheezy docker container gives easy access to an older Percona Toolkit version where the pt-log-player is available. Running a container with docker run -it --network="host" --name wheezy debian:wheezy /bin/bash and installing the legacy versions inside works:

apt-get update
apt-get install percona-toolkit mysql-client

I also tried percona-playback, but it does not seem to be very actively maintained either. That would be a great tool, but it is hardly documented and I could not find a method for filtering SELECT statements only, as I do not want to run other DML statements. I only saw that Percona Server offers a flag for enabling a read only option, but this is not available with vanilla MySQL or AWS RDS.

However I would rather like to use a procedure that does not rely on unmaintained tools.

Imagined Workflow

Enable slow query log or general log
Parse and analyze log file
Filter relevant queries
Re-execute queries
Compare results

Are there any tools how I could achieve this? What is the best way to filter the slow query log? How to replay the queries sanely?


texts: Why does it take so long to calculate an Execution Plan?

texts:
One of our customers has just upgraded to a new server.

For a particular stored procedure the first time you execute it, is taking over three minutes to run. Subsequent runs are less than 1 second.

This leads me to believe that the initial three minutes are primarily taken up calculating the execution plan. Subsequent runs then just use the cached plan and run instantaneously.

On our test databases it is taking about 5 seconds to calculate the plan for the same procedure.

I don't see anything terrible in the plan itself - although I don't imagine its relevant as the plan shows how long it takes to run the query, not calculate itself.

The server is a 16 core with 24 gb memory. No heavy CPU or memory load occurs.

What is it that could be causing such a slow calculation only on a particular database?

What steps can I take to find the cause of the problem?

Edit

So I have managed to access the server and have run the query with SET SHOWPLAN_XML ON.

I can confirm that the CompileTime for the query is taking up 99% of the query execution time. The StatementOptmEarlyAbortReason is "TimeOut", on our test database with a copy of their database the reason is MemoryLimitExceeded.


texts: Does the Always Encrypted certificates get copied over in availability groups?

texts: We have a column set up with Always Encrypted, and we are adding it to an AG.  We'd like for the encryption to keep working if/when we have to fail over to a secondary server.  Will the Availability Group copy over the certificates, or do we need to install them by hand?


texts:
SELECT IH.InvoiceNo,
    C.Name AS NameofCustomer,
    IP.Amount AS CashAmount,
    0 CreditNote,
    0 ChequeAmount,
    0 Credit,
    IP.Amount AS SalesValue
  FROM InvoiceHeader IH
LEFT JOIN Customer C ON IH.CustomerId = C.Id
LEFT JOIN InvoiceDetail ID ON ID.InvoiceNo = IH.InvoiceNo
LEFT JOIN DistributionCenter DC ON DC.Id = ID.DistributionCenterId
LEFT JOIN InvoicePayment IP ON IP.InvoiceNo = IH.InvoiceNo
WHERE DC.Id = 1 AND IP.PaymentTypeId=1 AND IP.InvoiceDate
BETWEEN'2018/05/01' AND '2018/06/19' AND IH.Deleted = 0
  UNION
SELECT  IH.InvoiceNo,
     C.Name AS NameofCustomer,
     0 CashAmount,
     IP.Amount AS CreditNote,
     0 ChequeAmount,
     0 Credit,
     IP.Amount AS SalesValue
FROM InvoiceHeader IH
LEFT JOIN Customer C ON IH.CustomerId = C.Id
LEFT JOIN InvoiceDetail ID ON ID.InvoiceNo = IH.InvoiceNo
LEFT JOIN DistributionCenter DC ON DC.Id = ID.DistributionCenterId
LEFT JOIN InvoicePayment IP ON IP.InvoiceNo = IH.InvoiceNo
WHERE DC.Id = 1 AND IP.PaymentTypeId=2 AND IP.InvoiceDate BETWEE'2018/05/01'
AND '2018/06/19' AND IH.Deleted = 0
 UNION
    SELECT  IH.InvoiceNo,
     C.Name AS NameofCustomer,
     0 CashAmount,
     0 CreditNote,
     IP.Amount AS ChequeAmount,
     0 Credit,
     IP.Amount AS SalesValue
   FROM InvoiceHeader IH
   LEFT JOIN Customer C ON IH.CustomerId = C.Id
   LEFT JOIN InvoiceDetail ID ON ID.InvoiceNo = IH.InvoiceNo
   LEFT JOIN DistributionCenter DC ON DC.Id = ID.DistributionCenterId
   LEFT JOIN InvoicePayment IP ON IP.InvoiceNo = IH.InvoiceNo
   WHERE DC.Id = 1 AND IP.PaymentTypeId=3 AND IP.InvoiceDate BETWEEN
  '2018/05/01' AND '2018/06/19' AND IH.Deleted = 0
   UNION
 SELECT  IH.InvoiceNo,
     C.Name AS NameofCustomer,
     0 CashAmount,
     0 CreditNote,
     0 ChequeAmount,
     IP.Amount AS Credit,
     IP.Amount AS SalesValue
 FROM InvoiceHeader IH
 LEFT JOIN Customer C ON IH.CustomerId = C.Id
 LEFT JOIN InvoiceDetail ID ON ID.InvoiceNo = IH.InvoiceNo
 LEFT JOIN DistributionCenter DC ON DC.Id = ID.DistributionCenterId
 LEFT JOIN InvoicePayment IP ON IP.InvoiceNo = IH.InvoiceNo
 WHERE DC.Id = 1 AND IP.PaymentTypeId=4 AND IP.InvoiceDate BETWEEN
 '2018/05/01' AND '2018/06/19' AND IH.Deleted = 0
 ORDER BY IH.InvoiceNo

Amount has values that paid and PaymentTypeId has the payment type. I am trying to retrieve data into 4 colomns with condition of PaymentTypeId = 1,2,3 or 4  this is the query i got. Are there any simple way to this task?


texts: pg_dump and pg_dumpall tools backup create zero size backup file from linux putty terminal

texts:
I have installed postgres database version 9.6, 10 on OS Centos 7 64 bit. I'm trying to take a backup with this command:

-bash-4.2$ pg_dump -h localhost -Fc backupdb > /var/lib/pgsql/niger_pgbackup/nigerpg_bkp.sql

but the problem is the backup file size is zero. I have tried many times. Kindly help  where I have gone wrong.

-bash-4.2$ pg_dump -h localhost -Fc backupdb > /var/lib/pgsql/niger_pgbackup/nig erpg_bkp.sql
-bash-4.2$ cd niger_pgbackup/
-bash-4.2$ ls -ltr
total 0
-rwxrwxrwx 1 postgres postgres 0 Oct 7 23:38 nigerpg_bkp.sql


texts:
I have a MySQL master-master replication with second master in read-only mode.
My questions reg backup are:

I have 10 databases so should I take daily backup as one dump containing all the databases, if yes when restoring will I be able to restore each database from the full dump which has all the databases? Or taking one db at a time is the good way to go?
Should I take backup from active master or read-only master? If read-only master is the case, then what if before the replication had broken and the backup has been taken from read-only - it will not have the updated data until the replication is active again right? So backing up from active master is fine?
How to go about backing binary logs since im not aware of it much.? What is the process to take binary log backup.?

Thanks!!


texts:
The following 2 queries are working as expected and they show the count.
Is it possible to merge them into single query?
I need sum_hits columns to be part of the rest of the column shown in the second query.

SELECT date_time, SUM(diac.requests) AS sum_hits
FROM ox_data_summary_ad_hourly diac
JOIN ox_zones z ON (z.zoneid = diac.zone_id)
JOIN ox_affiliates p ON (p.affiliateid = z.affiliateid AND p.agencyid = '3')
WHERE diac.date_time BETWEEN '2012-07-01 00:00:00' AND '2012-07-01 23:59:59'
AND p.agencyid = '3'
AND z.zone_type in ('application')
AND z.delivery in (8)
GROUP BY date_time

SELECT s.date_time AS date_time,
SUM(s.requests) AS sum_requests,
SUM(s.impressions) AS sum_views,
SUM(s.clicks) AS sum_clicks,
SUM(s.conversions) AS sum_conversions,
SUM(if(d.network_type like '%_user' and d.is_network=1,impressions,0)) AS sum_effimp,
SUM(s.total_revenue) AS sum_revenue,
SUM(s.total_revenue) AS sum_spend,
SUM(s.total_basket_value) AS sum_bv,
SUM(s.total_num_items) AS sum_num_items,
SUM(s.total_pubrevenue) AS sum_pubrevenue
FROM ox_data_summary_ad_hourly AS s
INNER JOIN ox_banners AS d ON (d.bannerid=s.ad_id)
INNER JOIN ox_campaigns AS m ON (m.campaignid=d.campaignid)
INNER JOIN ox_clients AS a ON (a.clientid=m.clientid)
LEFT JOIN ox_zones AS z ON (z.zoneid=s.zone_id)
LEFT JOIN ox_affiliates AS p ON (p.affiliateid=z.affiliateid)
WHERE a.agencyid = 3
AND z.zone_type = 'application'
AND z.delivery = 8
AND s.date_time>='2012-07-01 00:00:00' AND s.date_time<='2012-07-01 23:59:59'
GROUP BY date_time


texts:
This is my table with data:

I want to select it as:

Where 0=office, 1=home,2 = telemedice

The time will always be a multiple of 5.

How can i do that?

I have tried this:

WITH CTE
AS (SELECT fromDate AS [Time],
           1 AS RLevel,
           id,
           CASE appointMode
               WHEN 0 THEN
                   'office'
               WHEN 1 THEN
                   'home'
               ELSE
                   'tele'
           END AS mode
    FROM tbl_provider_available_schedule
    UNION ALL
    SELECT DATEADD(MINUTE, 15, [Time]),
           RLevel + 1,
           id,
           CTE.mode
    FROM CTE
    WHERE RLevel < 4)
SELECT id,
       [Time],
       CTE.RLevel,
       mode
FROM CTE
WHERE [Time] <=
(
    SELECT MAX(toDate) FROM dbo.tbl_provider_available_schedule
)
ORDER BY [Time];

Result that i get is:

My table script is:

CREATE TABLE [dbo].[tbl_provider_available_schedule]
(
[id] [int] NOT NULL IDENTITY(1, 1),
[practiceid] [int] NULL,
[providerid] [int] NULL,
[providerlocationid] [int] NULL,
[fromDate] [datetime] NULL,
[toDate] [datetime] NULL,
[appointMode] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbl_provider_available_schedule] ADD CONSTRAINT [PK_tbl_provider_available_schedule] PRIMARY KEY CLUSTERED ([id]) ON [PRIMARY]
GO

My data script is:

INSERT INTO dbo.tbl_provider_available_schedule
(
    practiceid,
    providerid,
    providerlocationid,
    fromDate,
    toDate,
    appointMode
)
VALUES
(1, 1, 2, N'2019-09-19T11:00:00', N'2019-09-19T11:45:00', 0),
(1, 1, 2, N'2019-09-19T11:00:00', N'2019-09-19T12:00:00', 2),
(1, 1, 2, N'2019-09-19T11:30:00', N'2019-09-19T11:45:00', 1);

How can i achieve the above mentioned required results?

I have tried this:

WITH CTE
AS (SELECT fromDate AS [Time],
           1 AS RLevel,
           id,
           CASE appointMode
               WHEN 0 THEN
                   'office'
               WHEN 1 THEN
                   'home'
               ELSE
                   'tele'
           END AS mode
    FROM tbl_provider_available_schedule
    UNION ALL
    SELECT DATEADD(MINUTE, 15, [Time]),
           RLevel + 1,
           id,
           CTE.mode
    FROM CTE
    WHERE RLevel < 4)
SELECT id,
       [Time],
       CTE.RLevel,
       mode
FROM CTE
WHERE [Time] <=
(
    SELECT MAX(toDate) FROM dbo.tbl_provider_available_schedule
)
ORDER BY [Time];

Result that i get is:

My table script is:

CREATE TABLE [dbo].[tbl_provider_available_schedule]
(
[id] [int] NOT NULL IDENTITY(1, 1),
[practiceid] [int] NULL,
[providerid] [int] NULL,
[providerlocationid] [int] NULL,
[fromDate] [datetime] NULL,
[toDate] [datetime] NULL,
[appointMode] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbl_provider_available_schedule] ADD CONSTRAINT [PK_tbl_provider_available_schedule] PRIMARY KEY CLUSTERED ([id]) ON [PRIMARY]
GO

My data script is:

INSERT INTO dbo.tbl_provider_available_schedule
(
    practiceid,
    providerid,
    providerlocationid,
    fromDate,
    toDate,
    appointMode
)
VALUES
(1, 1, 2, N'2019-09-19T11:00:00', N'2019-09-19T11:45:00', 0),
(1, 1, 2, N'2019-09-19T11:00:00', N'2019-09-19T12:00:00', 2),
(1, 1, 2, N'2019-09-19T11:30:00', N'2019-09-19T11:45:00', 1);

How can i achieve the above mentioned required results?


texts: I used the "Oracle-imp", where are my tables?

texts:
I'm really losing my hair with a question, I'm trying to import a database from a client at my machine, but I'm not getting it.

I create a new user to make db import, and the import was successful.

My doubt is the next, where is my tables that came with the import?

I tried to use a command that I found in some searches.
For example: select table_name from all_tables;

That selects a lot of tables, but the tables of the import doesn't.

I made another test to try to find the problem (I suppose that is not a problem, it's just me that don't have a know-how enough), I created at the SQL Developer a new table, and I executed the same select I used before, and the table doesn't exist =).

So I suppose that I'm not at the correct local to use the commands.

Can you give some information about that?


texts: Is there a better way to DENY SYS schema and INFORMATION-SCHEMA to DBO user?

texts:
I could fix this issue with the following steps:

Create login
Create DBO user and assign it to a specific database
ADD this user to the MASTER database ( create user x for login x)
DENY SELECT On SCHEMA::sys To x
DENY SELECT On SCHEMA::INFORMATION_SCHEMA To x

But since there's a lot of logins in the server, I don't want to add ALL of them in the master database to deny this permission. For now, ALL users have DBO role, because both dev and the user uses the same login (yeah..I know...I really know ). It would be hard to track future issues.

Is there a better way to do this? (To deny select on sys tables and information_schema).

And one more question. WHY ReadOnly has access to sys tables (sys.databases for example )?


texts:
I'm having issues with getting ALLOW FILTERING to work. In our Cassandra 1.2 database, we have a table like this:

CREATE TABLE some_table
    partition_key text,
    other_key     text,
    non_key       text,
    PRIMARY KEY (partition_key, other_key)

Based on the documentation, this query ought to work:

SELECT *
FROM some_table
WHERE
    partition_key = 'foo'
    AND other_key > 'bar'
    AND non_key = 'baz'
LIMIT 100
ALLOW FILTERING

However, I get this error:

  Unable to execute CQL script on 'Your Keyspace': No indexed columns present in by-columns clause with Equal operator

Even this simpler query fails with the same error:

SELECT *
FROM some_table
WHERE
    partition_key = 'foo'
    AND other_key = 'bar'
    AND non_key = 'baz'
LIMIT 100
ALLOW FILTERING

Yes, I'm aware that you can add secondary indexes to tables, but I thought the whole point of ALLOW FILTERING is to enable exceptions when you're ok with the fact that a potentially large amount of scanning is possible.

It seems like ALLOW FILTERING is completely broken. Why they heck doesn't this work?