texts:
I have a query to get all the comments from threads where a given user has commented and not yet seen the comment. So if user 1 commented to a topic, then user 2 commented after his comment, then user 1 gets a notification and the notification is gone when he saw the new comment (p1.date>p2.last_seen) checks this.

It all works fine, but multiple group by is slow. It's better if I have separate queries for each type (tooic,news,pictures), as seen below.

If I have only 1 group by, the query is quick (0,02 sec or so).

    SELECT SQL_NO_CACHE p1.* FROM comment p1 INNER JOIN
      (SELECT max(seen) last_seen, max(id) MaxId,
      user_id, topic_id, picture_id, news_id, date
      FROM comment WHERE user_id='1' and deleted=0
      GROUP BY topic_id) p2
   ON p1.topic_id = p2.topic_id
   WHERE p1.date > p2.last_seen and p1.id > p2.MaxId and deleted=0
   GROUP BY p1.topic_id ORDER BY p1.date DESC

But it's very slow (~1,5 sec) with multiple group by (creating temp table takes ~1,2 sec).

    SELECT SQL_NO_CACHE p1.* FROM comment p1 INNER JOIN
      (SELECT max(seen) last_seen, max(id) MaxId,
      user_id, topic_id, picture_id, news_id, date
      FROM comment WHERE user_id='1' and deleted=0
      GROUP BY topic_id, picture_id, news_id) p2
   ON p1.topic_id = p2.topic_id and p1.picture_id= p2.picture_id
   and p1.news_id= p2.news_id
   WHERE p1.date > p2.last_seen and p1.id > p2.MaxId and deleted=0
   GROUP BY p1.topic_id, p1.picture_id, p1.news_id ORDER BY p1.date DESC

I'm using no cache for testing purposes.

Schema

Id is primary, other indexes are user_id, topic_id, picture_id, news_id, deleted, date and seen

CREATE TABLE `comment` (
`id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`topic_id` int(11) NOT NULL,
`picture_id` int(11) NOT NULL,
`news_id` int(11) NOT NULL,
`text` text COLLATE utf8_hungarian_ci NOT NULL,
`date` datetime NOT NULL,
`reply_id` int(11) NOT NULL,
`comment_vote` int(11) NOT NULL,
`comment_hit` int(11) NOT NULL,
`moderated` int(11) NOT NULL,
`seen` datetime NOT NULL,
`deleted` tinyint(4) NOT NULL,
`ip` text COLLATE utf8_hungarian_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
COLLATE=utf8_hungarian_ci;
ALTER TABLE `comment`
ADD PRIMARY KEY (`id`),
ADD KEY `topic_id` (`topic_id`),
ADD KEY `picture_id` (`picture_id`),
ADD KEY `news_id` (`news_id`),
ADD KEY `deleted` (`deleted`),
ADD KEY `user_id` (`user_id`),
ADD KEY `date` (`date`),
ADD KEY `seen` (`seen`);

Explain

1(id)   PRIMARY(select_type)    <derived2>(table) ALL(type) NULL(possible keys) NULL(key)   NULL(key_len)   NULL(ref)   105(rows)   Using temporary; Using filesort(extra)
1(id)   PRIMARY(select_type)    p1(table)   ref(type)   PRIMARY,topic_id,deleted,date,picture_id,news_id(possible keys)  topic_id(key)  4(key_len)  p2.topic_id(ref)    5(rows) Using where(extra)
2(id)   DERIVED(select_type)    p2(table)   index_merge(type)   deleted,user_id(possible_keys)  user_id(key)    4,1(key_len)    NULL(ref)   169(rows)   Using intersect(user_id,deleted); Using where; Using filesort(extra)


texts: How to query status changes in time series?

texts:
Given

create table rating (
    ID       int identity(1,1) primary key,
    PersonID int,
    Ratingdate date,
    rating   varchar(2)
);

insert into rating values ( 1, '2010-08-04' , 'A3');
insert into rating values ( 1, '2010-08-14' , 'A1');

insert into rating values ( 2, '2010-08-04' , 'G2');
insert into rating values ( 2, '2010-08-14' , 'G1');

insert into rating values ( 3, '2010-08-04' , 'G1');
insert into rating values ( 3, '2010-08-10' , 'G1');
insert into rating values ( 3, '2010-08-14' , 'G3');
insert into rating values ( 2, '2010-08-20' , 'G4');

I want a query, which returns all rows, where the rating changes for a given person.
For example here I want to exclude the row  ( 3, '2010-08-10' , 'G1') because the rating didn't change  since ( 3, '2010-08-04' , 'G1').

I know a solution using a cursor, but I wonder, if it could be done without cursors.
I tried a bit with analytical functions, but didn't found a solution.

Besides the solution, please improve the terms with which I describe the problem. I guess it is some standard task.

Edit:

Here is the link to connect asking to add the lag and lead functions to SQL Server.

Please vote.


texts: ORDER BY items must appear in the select list [...]

texts:
Using Microsoft SQL Server 2008, I get the following error.

  Msg 104, Level 16, State 1, Line 43
   ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

The query is I am using is kind of complex, but the CASE statement in side of the ORDER BY clause can not see the aliased column name, here is a brief example.

SELECT 1 AS foo, 2 AS bar
UNION ALL
SELECT 10 AS foo, 20 AS bar
ORDER BY CASE WHEN foo = 2 THEN 1 END;

In my production query the left-query needs to be ordered by the column [360_set] found in the table, and the right-query needs to be ordered as if [360_set] was null.

How do I fix this error, and why does this syntax generate an error?

Here is the version info,

Microsoft SQL Server Management Studio     10.0.5512.0
Microsoft Analysis Services Client Tools   10.0.5500.0
Microsoft Data Access Components (MDAC)    6.1.7601.17514
Microsoft MSXML                            3.0 6.0
Microsoft Internet Explorer                9.10.9200.16635
Microsoft .NET Framework                   2.0.50727.5472
Operating System                           6.1.7601


texts: Truncating TEMP table before insert leads to big amount of logical reads on TEMP TABLE

texts:
Setup:
We have a complex set of queries, that runs quite often (about 3000 times per hour) for different users.
At the start of the process, .NET application creates a set of temp tables
during the process, .NET application calls a set of SQL queries, they look like this:

truncate #QuerySpecificTable

insert into #QuerySpecificTable
select PrimaryKey
  from Production.QuerySpecificTable
  join ...
 where ...

Issue i came across while performance tuning one specific query. ~139000 records get inserted into the temp table. This causes ~300k logical reads on the #QuerySpecificTable.

When i remove the truncate, it drops to ~300 reads on #QuerySpecificTable.
CPU statistics show drop in CPU time (~900->300ms so not sure about precision and reliability.)

The issue is, when trying to do this in SSMS, i cannot 100% reproduce the behaviour and make sure and demonstrate removing the truncate (even through it wouldnt be a problem) is a good thing to do. The biggest issue is NOT KNOWING why and what is happening here

WITH TRUNCATE (NOT ALWAYS, thats the weird part)

truncate table #test
insert into #test select top 150000 PrimKey from dbo.test

Table '#test_______________________________________________________________________ ________________________________________000000000141'. Scan count 0, logical reads 319955, physical reads 0, read-ahead reads 217, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Test'. Scan count 1, logical reads 246, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(150000 rows affected)

DROP/CREATE

drop table if exists #test
create table #test (PrimKey integer primary key)
truncate table #test
insert into #test select top 150000 PrimKey from dbo.test

Table 'Test'. Scan count 1, logical reads 246, physical reads 0, read-ahead reads 232, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(150000 rows affected)


texts: Any general reasonable performance tests for DBs on application server and DBs on separate server?

texts:
My company are planning to move the DBs (SQL Server 2008 R2) from the application server to another server (random distance). All servers are Windows 2003 x86.

Are there any general performance tests in SQL Server I can run to find out if under three different circumstances (no load, normal load, full load) this configuration is performing better or worse than the original configuration where application and DBs were located on the same server?

(I do not have more details yet.)


texts: Best way to designate unit of weight in PostgreSQL

texts:
I am working with a simple database with tables of users, products and orders, but previously the products table was not very well designed in my opinion in that for the weights column, it just had a number, but its unclear whether its in pounds or ounces.
I have tried to find documentation on how to designate pounds and ounces as a unit of weight for a table, but I have not been successful. Can anyone suggest a way to do this? Again the column is simply called weight, how do I make it clear that it starts at 2 ounces up to 1 pound as a unit of weight inside of Postgres?


texts:
please, can you clarify this?
My configuration is RAC 12c on ASM on SLES 11 SP 4 on SystemZ.

The nodes rac11 and rac12 are the server nodes of the primary database, named "primdb", uniq_name is "primdb". The instance names are prim1 and prim2 correspondingly.

The nodes rac21 and rac22 are nodes of the standby database named "primdb", uniq name is "stdbydb". The instance names are stdby1 and stdby2 correspondingly.

So, I'm trying to create physical standby using RMAN active duplication.

After configuring such parameters as log_archive_dest, dump_dest, and so on I copied password file to the standby server and changed init-file.
I added static entry to the listener on rac21 (not SCAN-address) with service name of the stdby1 instance and was able to connect to "nomount" database as SYS from any of my servers.

$>su - oracle
$> . oraenv
    ORACLE_SID = [oracle] ? stdby1
    ORACLE_HOME = [/home/oracle] ? /u01/app/oracle_home
    The Oracle base has been set to /u01/app/oracle_base
$>sqlplus / as sysdba
SQL>startup nomount pfile='/tmp/initstdby1.ora'

after that i perform the following:

$> rman target sys/q1@primdb auxiliary sys/q1@stdby1

  connected to target database: PRIMDB (DBID=636407725)
    connected to auxiliary database: PRIMDB (not mounted)

$>duplicate target database for standby from active database nofilenamecheck;

And on the step of executing memory script (sorry, i can't paste all RMAN output here) a wild error appears

  RMAN-00571:
  =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS
  =============== RMAN-00571: =========================================================== RMAN-00554: initialization of internal recovery manager package failed
  RMAN-04006: error from auxiliary database: ORA-00600: internal error
  code, arguments: [kzsrsea_ext], [], [], [], [], [], [], [], [], [],
  [], []
  ------------------------------------------->

After that i'm not able to connect to this nomount database using sql*Net,
the same error ORA-600 appears  .
The only way to connect to this database instance is to connect locally

sqlplus / as sysdba

And the interesting moment - if i copy the passwordfile again from the primary database to standby, i can connect to this standby database instance.

So i think, that RMAN changes my passwordfile on the standby server. I searched for the same error on MOS (Doc ID:1136834.1), but they suggest to use backups as the workaround, which is not suitable for me.

Any help would be much appreciated..


texts:
I've created a unique constraint to my table containing employee account details in an attempt to stop duplicated data inserts.

ALTER TABLE emp ADD CONSTRAINT uq_ UNIQUE (USERNAME, EMP_NAME);

However my problem comes when it comes inserting the test data:

----this first insert should work fine
INSERT INTO table_name (id, username, emp_name, regdate)
VALUES (1, 'test', 'test1', sysdate);

What I'm trying to prevent is an instance like this were the data about an employee is inserted twice into the table:

INSERT INTO table_name (id, username, emp_name, regdate)
VALUES (2, 'test', 'test2', sysdate);

As at the moment the insert runs and adds to the table which I'm trying to prevent it from doing.

Is there a way to combat this, would a trigger be a better option to use?


texts:
Can I create a dblink to a SQL Server from Oracle, in the same I would if I was creating a dblink from Oracle to Oracle?
I've tried, but get a message saying -

  connection has closed as partner has disconnected.

Thanks in advance

K


texts: Permissions required to execute UDF Scalar Function inside a trigger

texts: I have a scalar function that user don't have execute permissions on. However the users have select , insert, update , delete permissions on a table that has a trigger and the trigger calls the scalar function. The user gets an error when they try to run the function saying that they don't have execute permissions on the function which is fine. But when the user runs a update query on the table that fires off the trigger, the functions runs fine . How does that work?


texts:
I am filtering my data with:

$sel_query="Select * from details where account_id=".$_SESSION['admin_id'];

I want to display records in descending order by using query:

$sel_query="Select * from details where account_id=".$_SESSION['admin_id']
ORDER BY time DESC;

Both queries working separately. When I write two conditions, the query is not working.
What do I need to change here?


texts: check constraint where values must be a table name

texts:
Is there a way to write a check constraint such that values in a column are limited to table names currently in the schema?  It appears subqueries are not allowed (ORA-02251), but logically I am looking to do:

CONSTRAINT ck_table_name
     CHECK (src_table IN (select table_name from user_tables))


texts:
I used to access the root user in MySQL just fine. But recently, I am no longer able to.

I am able to login fine :

 mysql -u root -p

Here is the mysql status after login :

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.5.28, for debian-linux-gnu (i686) using readline 6.2

Connection id:      37
Current database:
Current user:       root@localhost
SSL:            Not in use
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.5.28-0ubuntu0.12.04.3 (Ubuntu)
Protocol version:   10
Connection:     Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:        /var/run/mysqld/mysqld.sock
Uptime:         4 min 16 sec

Threads: 1  Questions: 112  Slow queries: 0  Opens: 191
Flush tables: 1  Open tables:  6  Queries per second avg: 0.437
--------------

But when I want to do any action, such as :

mysql> CREATE DATABASE moyennegenerale;
ERROR 1044 (42000): Access denied for user 'root'@'%' to database 'moyennegenerale'

I understand % is used to signify any host , but my status clearly states localhost. Does somebody have an idea of what might be going on?


texts:
set @sqlt = concat("select", @rowname_date," from  syslog_data.syslogs_index_1 ");
prepare stmt from @sqlt;
execute stmt;
deallocate prepare stmt;

gives me a eeror 1064 in the prepare statement?
Any advise please.
Thanks


texts: How Wide-columns databases achieve dynamic columns on the storage layout?

texts:
As known, in relational databases, when adding a new column, data must be reallocated (https://stackoverflow.com/questions/463677/alter-table-without-locking-the-table) to maintain a single row contiguous on disk.

I would like to understand how this is achieved on wide-columns storages such as Cassandra, which are sparses and can handle lots of dynamic columns insertions (http://www.datastax.com/dev/blog/thrift-to-cql3 (Dynamic Column family))

Thanks!


texts: SQL Server Management Studio Error Access Denied Editing Job Step

texts:
I recently changed the owner of a job from my user to sa. After the ownership change I am able to edit the Schedules and Notifications for the job. However all attempts to to create New, Insert, Edit, or Delete job steps result in the window closing with the error 'Access Denied'.
I tried changing the owner back to my user and also granted my user the SQLAgentOperatorRole in msdb, but the 'Access Denied' error persists when changing job steps.

This error also occurs when creating a new job. It's impossible to Create a job step.

Any idea how to resolve this?

Thanks


texts: SQL Group Execution Rights to Read only SPs and functions - By not changing objects' schemas

texts:
We have a SQL Developer Group.. We want to give them access to execute functions and sps but not to any that amends data in any way..
We currently only have the DBO schema and all objects sits on that schema.. We have a db_execute role. This role currently have execution rights on our objects on dbo...
We have tried creating a different role and tested by giving execution rights to one particular SP and assigning the dev group to the new role.. but when we gave access to the role.. it gave them rights to all objects that are executable again...
Is it possible to create another role, just for developers that will allow only retrieving of data and no amendments by not having to move the objects to a different schema...
Any help will be highly appreciated!


texts:
I am going to begin transitioning several of our databases to FULL recovery from SIMPLE.  I asked a question last week regarding deployment strategies and I have one additional question.

Consider this:

Sunday (midnight) - Run FULL backup
Monday-Saturday (midnight) - Run differentials
Hourly - Transaction log backup

Are the differential backups basically eliminating the need for the transaction logs for that day?

So, if we have a catastrophic failure at 10pm on Tuesday, we would:

Run tail log backup on affected DB(s)
Restore FULL backup from Sunday night
Restore DIFF backup from Monday night
Restore using log chain from the day on Tuesday up to disaster time.

If this is the case, then could we simply remove the transaction log backups from Monday as part of the maintenance plan, since we have a differential from Monday night?  Would it be smarter to keep them for redundancy in case the DIFF backup fails?


texts: Trying to design database for eSports Teams - Issue with dependencies

texts:
I am trying to design a database in Access for an eSport league (specifically League of Legends North America). I will post what I have and then explain my issue.

My issue comes when I try to normalize TEAMROSTER and PLAYERNAME. Each player can only belong to one team, but each team has 5 players in the 5 positions that you see I have listed in TEAMROSTER. I am not sure if that will be allowed when I try to create the relational schema. I will post the schema that I have created.

I am not sure if this is correct or not, as I am trying to relate the tables together and just truly don't understand how I should do this. This is the first database that I have tried to create.


texts: Timeout expired error while using copy database wizard

texts:
Microsoft made me believe that SQL Server 2012 Copy Database Wizard is the most optimal way of copying a SQL Server 2000 database to SQL Server 2012. After struggling a few hours, I was able to get past some issues and I was able to import small to medium size SQL Server 2000 databases into SQL Server 2012.

However, the wizard constantly fails for a 30GB database with these errors:

  Message: An error occurred while transferring data. See the inner exception for details.
  StackTrace: at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
  at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()
  InnerException --> Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
  CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
  StackTrace:
  at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
  Boolean breakConnection)
  at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
  at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream,
  BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject
  stateObj)

I have double checked, it is not a filepath, permission or disk space issue. I think the CREATE DATABASE step takes 2 minutes or so and the wizard assumes that the operation timed out. I manually created an empty database with same filepath and filesize using SQL, that worked. Interestingly, a 1GB database copy failed with same errors and succeeded on 2nd try.

Please help.


texts: How to restore a SQL Server database along with a standalone application?

texts:
I am new to Microsoft SQL Server. I mostly use MySQL and Firebird databases in my projects.

I have a client who needs to restore some software after his computer crashed.  All that is left is a pen drive with the application and database backup.

I have discovered that this application used Microsoft SQL Server 7 and it comes with three files: the executable, an .MDF and a .LDF file.

I installed the trial version of SQL Server 2012 and defined c:\mssql7\data as the database folder in order to match the original files. I also set the user interface to work with Windows accounts, because I do not know if that application programmer changed the database default user and password.

But when I run the application, it displays this message:

  [DBNETLIB][ConnectionOpen (Connect()).]SQL Server do not exist or
  access denied.

What should I do to restore the database and get it to work with the application?  The original developer cannot be located.

Perhaps the login and password I am using does not match the ones in the restored database files?  Perhaps I have a misconfiguration somewhere, for instance maybe I cannot simply copy the files into the data folder?  Perhaps I need to register the database somehow?

Edit 1: The only thing I found by reading the contents of the executable file was:

Provider=SQLOLEDB
Itegrated Security=SSPI
Persiste Security Info=False
User ID=usuario
Initial Catalog=FVBraganca
DataSource=SCFRANGOS

If you guys want to see the original file, you can download it from my website:

http://alcantara.net.br/MS_SQL_APP_BUGGY.rar
http://alcantara.net.br/MS_SQL_APP_BUGGY.zip


texts:
I tried to give it a chance without thinking much and this is what I came up with:

Room
----

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

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

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


texts:
I would like to unserstand why I am not able to drop a database from cmd:
I execute the command: drop database if exists <db_name> but the result is as below:
postgres=# drop database testV5;

ERROR:  database "testV5" does not exist

knowing that the database exists:
postgres=# \l
                                  Liste des bases de donnÚes

   Nom     | PropriÚtaire | Encodage |  Collationnement   |    Type caract.    |    Droits d'accÞs
  testV4   | postgres     | UTF8     | French_Canada.1252 | French_Canada.1252 |
  testV5   | postgres     | UTF8     | French_Canada.1252 | French_Canada.1252 |
  postgres | postgres     | UTF8     | French_Canada.1252 | French_Canada.1252 |
  template0| postgres     | UTF8     | French_Canada.1252 | French_Canada.1252 |
  template1| postgres     | UTF8     | French_Canada.1252 | French_Canada.1252 |

Can anyone explain that please ?
Thanks in advance.


texts: SQL Pull replication stops on unintialized subscription

texts:
For a new project we want to test pull replication in mssql to replicate database (transactional replication) to multiple db server. I setup the pull replication by wizard in MSSMS and but the replication stucks on uninitialized subscription in the replication monitor.
Steps I have done:

Created a publication on publisher by wizard (Local Publications > New Publication...).
Created a subscription by wizard (Local Subscriptions > New Subscriptions...)

Publisher and distributor are on the same host. Subscriber uses SQLExpress. Authentication is done by sql authentication. SQL user used for authentication is dbo on Distributor DB.
What else is needed to initialize the subscription?


texts: AlwaysOn - Availability Group Listener in a multi-subnet cluster

texts:
I'm running into some difficulty getting the AG Listener created for a 3 node cluster where node 3 is on a separate subnet.

I'm encountering an error that states

Create failed for Availability Group Listener 'AO-AGL'
None of the IP addresses configured for the availability group listener can be hosted by the server
'NODE3'. Either configure a public cluster network on which one of the specified IP addresses can
be hosted, or add another listener IP address which can be hosted on a public cluster network for
this server. (Microsoft SQL Server, Error: 19456)

How do I handle this situation.  This blog post (http://mohdsohail.wordpress.com/2013/02/14/adding-seconday-replica/) is the only thing I could find for reference and it isn't very clear on the solution.  He states adding a second IP address.  However, what exactly does this look like in DNS?  Do I have one DNS hostname with two IP addresses?


texts:
I have a query

create table film_actor (
    actor_id integer,
    film_id integer);
create table film (
    film_id integer,
    title varchar);
create table actor (
    actor_id integer,
    first_name varchar,
    last_name varchar);

INSERT INTO film_actor (actor_id, film_id) VALUES (1, 1);
INSERT INTO film_actor (actor_id, film_id) VALUES (1, 2);
INSERT INTO film_actor (actor_id, film_id) VALUES (1, 3);
INSERT INTO film_actor (actor_id, film_id) VALUES (2, 1);
INSERT INTO film_actor (actor_id, film_id) VALUES (2, 2);
INSERT INTO film_actor (actor_id, film_id) VALUES (2, 3);
INSERT INTO film_actor (actor_id, film_id) VALUES (3, 1);
INSERT INTO film (film_id, title) VALUES (1, 'First');
INSERT INTO film (film_id, title) VALUES (2, 'Second');
INSERT INTO film (film_id, title) VALUES (3, 'Third');
INSERT INTO film (film_id, title) VALUES (4, 'Fourth');
INSERT INTO actor (actor_id, first_name, last_name) VALUES (1, 'John', 'Snow');
INSERT INTO actor (actor_id, first_name, last_name) VALUES (2, 'Spider', 'Man');
INSERT INTO actor (actor_id, first_name, last_name) VALUES (3, 'Mike', 'Kameron');

select max(actors_comparing.counta)
from (
  SELECT fa1.actor_id first_actor, fa2.actor_id second_actor, count(*) counta
  FROM film_actor fa1
  join film_actor fa2
  using (film_id)
  where fa1.actor_id != fa2.actor_id
  group by (fa1.actor_id, fa2.actor_id)
) actors_comparing;

I need to add information about pair (fa1.actor_id, fa2.actor_id), but I don't know how.

And I always get error like must appear in the GROUP BY clause or be used in an aggregate function

I need something like

select actors_comparing.first_actor, actors_comparing.second_actor, max(actors_comparing.counta)
from (
  SELECT fa1.actor_id first_actor, fa2.actor_id second_actor, count(*) counta
  FROM film_actor fa1
  join film_actor fa2
  using (film_id)
  where fa1.actor_id != fa2.actor_id
  group by (fa1.actor_id, fa2.actor_id)
) actors_comparing


texts: SSIS Bulk Insert Task Operating system error code 1311

texts:
I have been running SSIS 2008 R2 Packages for a long time and when ever my Packages trough SQL Server Job accesses FTP Server which is on the same domain it uses correct credentials ,e.g. domain\domainuser. Note: SQL Server is DC and FTP Server is not.

Now when I run Bulk Insert Task, which in addition uses a Format File, which is located on the same FTP Server, it uses ftpservername\guest credentials instead of domain\domainuser and throws back an error:

  [Bulk Insert Task] Error: An error occurred with the following error
  message: "Cannot bulk load because the file "\ftpserveripadress\FTP
  Access\client\From client\a_file_that_exists.txt" could not
  be opened. Operating system error code 1311(There are currently no
  logon servers available to service the logon request.).".

I have no clue why this particular task is not using correct credentials, and I had no success finding anything related between Bulk Insert Task and Operating system error code 1311.

Note: If this is not the correct place to post a question like this, please advise me with appropriate place to post this.


texts: What's the best solution for offline db syncing

texts:
I'm building a system which requires a master db (BIG master) that contains things like accounts, balances, actions, etc.

This db then needs to be accessible at a physical place, like a fairground say by point of sale systems. The accounts are used by customers at the fair to buy things with their balance.

The network connectivity locally is fine, i.e. we can create a wifi network and have a copy of the master db available locally (SMALL master) . The issue is that network connectivity to the outside is patchy/non-existent for hours.

My thinking is that I need master to master replication because people could be signing up online (to BIG master) and then head to the fair. People will also be spending their balance at the fair (SMALL master) and obviously we can't have people spending their balance in 2 places (at the fair and online)

I'm using Postgres (latest) and was thinking of using bucardo for the replication.

Is this the best solution for this problem? Is there another solution to patchy db sync?


texts: CHECK constraint ignored during INSERT SELECT

texts:
I have the following 2 tables (simplified):

CREATE TABLE dbo.[StagingTable] (
  HostName [nvarchar] (max) NULL
  ,UserName [nvarchar] (max) NULL
  ,AccountType [nvarchar] (max) NULL
)

CREATE TABLE dbo.[FinalDestination] (
  HostName [nvarchar] (128) NOT NULL
  ,UserName [nvarchar] (128) NOT NULL
  ,AccountType [nvarchar] (16) NULL
  CONSTRAINT [PK-FinalDestination] PRIMARY KEY (HostName, UserName)
  ,CONSTRAINT [CK-FinalDestination-AccountType] CHECK ([AccountType] IN ('Personal', 'Shared', 'Service'))
)

The check constraints work just fine for statements like the following where it wouldn't allow the transaction to complete as 'XXX' is not a valid 'AccountType'

INSERT INTO FinalDestination (HostName, UserName, AccountType)
VALUES
('host1', 'user1', 'Personal'),
('host1', 'user2', 'XXX')

But the check constraint is apparently ignored in the following scenario:

INSERT INTO StagingTable (HostName, UserName, AccountType)
VALUES
('host1', 'user1', 'Personal'),
('host1', 'user2', 'XXX')

INSERT INTO FinalDestination (HostName, UserName, AccountType)
SELECT * FROM StagingTable

And I end up with 'XXX' as an account type on 'FinalDestination'.

Is there any way to enforce the CHECK constraint on my INSERT INTO ... SELECT statement? I already have the following options set to Yes:

Check Existing Data On Creation Or Re-Enabling = Yes
Enforce For INSERTs AND UPDATEs = Yes
Enforce For Replication = Yes

Thanks!


texts:
I've read the documentation on the MSDN, but it doesn't answer what I'm after.  If I create an index on specified columns using a WHERE clause for certain IDs, this will limit the index to those IDs and new IDs will insert into this index.  That is all fine and dandy.  But, in my WHERE clause, I'd like to specify a list of previous IDs and also include anything that is greater than the highest ID in the table e.g.

WHERE CampaignID IN (1,2,10,14,16,20,26)
  AND CampaignID > 26

Is this advisable?  The goal I'm trying to achieve is to index all of the data that is presently used, and ignore all other data.  The table is over 10Gb and it's in constant use so I can't remove these old records as it locks the table and stops data being inserted.


texts: Plpgsql (Postgres) function for sanity check

texts:
While starting up my app, I need to do a sanity check within my database.
In a table sensor, there's a column seconds. Now the function should take an argument id.

It then should simply do something like:

select STARTTIME,SECONDS from sensors where SECONDS = 0;

The rows are not allowed to be 0, which only could happen if the app crashed. So if this function finds some rows, it needs to repair them by checking another table:

select "timestamp" from secure_sanity where id = *id*;

and then it should calculate STARTTIME - TIMESTAMP (from secure_sanity) and set this value to seconds.

I have no idea how to do this as I am completely new to PL/pgSQL. I would rather do this from within the app, but it feels like it would be much more efficient to do this within a function.

Could you give me a clue?


texts: User is not showing for abc database under security folder but when we query from sys.databases it showing as a db owner

texts:
Below Steps I Did

EXEC sp_changedbowner 'domainname\user'

... to change the principal owner.

It was executing for a long time so i cancelled it. While closing the tab it asked me to save the uncommitted transactions, so I saved them.

Later when I checked under the database security folder the old user name was not showing any more.

But when I ran the query

select database_id, name, suser_sname(owner_sid) from sys.databases
where suser_sname(owner_sid) = 'domain\olduser'

...it returns one row which shows that the old user is still the db owner.

If I check the database properties from SSMS it shows the old user as the owner as well. But under the database users I am not able to see the old user any more.

Please suggest how I can change the db_owner...


texts: Will auto_increment always grow in master-master setup?

texts:
Let's suppose I have mysql master-master replication setup with only one server active at a time (i.e. all writes going to one server), and
I have ability to manually switch to another master.

Next, I have auto_increment_increment and auto_increment_offset setup:

Master1

auto_increment_increment = 2
auto_increment_offset = 1

Master2

auto_increment_increment = 2
auto_increment_offset = 2

Master1 is now active master, and I have a table "users", and insert few recrods:
1,3,5,7

then I switch to Master2, all writes going to Master2.

Question is: will next inserted record to users have auto_increment ID "2" or ID "8" ? Where is this documented?


texts: Detect when a CREATE INDEX CONCURRENTLY is finished in PostgreSQL

texts:
If I create an index CONCURRENTLY in PostgreSQL, how can I see when it is finished?

I am attempting to rebuild indexes to solve index bloat, and I need to keep the old index around for a while until the new one has finished, so I need to know when it's finished.

This is PostgreSQL 9.2/3ish


texts: Inserting data into a table with one value as clob and blob

texts:
My table has one clob column and one blob column. I need to insert data into it through insert statements.

The length of data to be inserted is more than 4000 chars.

When I do export of insert statements is sql navigator I get empty values.

INSERT INTO mytable VALUES('REQUEST',EMPTY_CLOB(),EMPTY_BLOB());

I also tried to do insert it data like this.I have added only example data to be inserted.The actual data to be inserted is large.

INSERT INTO mytable VALUES('REQUEST2','{"customer"asxcbasjbab....:}',NULL);

Can some one tell me best way to insert data into table in these scenario.

Or anyway to do export and import this data will also help.


texts:
Our previous DBA left and didn't note the root password down, and I've been pressganged into doing part of his job until we can hire someone.

So I create a file :

/root/mysql.password

Inside that file, I have:

UPDATE mysql.user SET Password=PASSWORD('newpassword') WHERE User='root';

So I do a clean shutdown of the database, and then:

mysqld_safe --init-file=/root/mysql.password

I get 'starting mysqld' ... and then it exits with code 1.

I don't get it.  What's wrong with that file?

Using ..

mysqld_safe --skip-grant-tables &

.. isn't an option, unfortunately, as it's a public-facing production box.

Thanks.


texts:
I've been asked to provide my input on best practices to improve tempdb performance. I realise that this is a case by case basis and tempdb in one estate may have different requirements to another due to workload volume and type however I was hoping to get an idea of best practices beyond my current suggestions.

At the moment we have 1 data file which I will increase to 4 to match the number of logical cores. My tempdb files currently reside on the same drives as other database files, they will be moved to their own to stop IO contention.
 I'm also to implement trace flags  1117,1118 to grow all files equally in a file group and solely use uniform extents. Finally, a further change I'll look to make is enabling Instant File Initialisation.

Beyond what I have above, are there any best practices I've missed?
Also, it suggested that storing tempdb on it's own drive is a best practice but should all tempdb files be stored on the same drive or should .ldf,.mdf
& .ndf be stored on their own volumes.

Thanks


texts:
I have two very similar database questions related to changes tracking.

There are a lot of automation tests for an application, all of them use one database backup. Currently before each test execution the backup is restored.  It's needed as these tests might change data, so if several tests were executed in a row without restoring database, they might fail. Restoring a database is rather slow operation and I want to avoid it. Is there a way to restore a database to the previous state, before executing the test? I want the following scenario:

The database is restored
Test 1 is executed (it might change data)
The database is restored to previous state
Test 2 is executed and so on

I think about wrapping up all tests operation into a transaction and using rollback when a test finishes. But probably there is a better way to do so? Can someone at least point me to the right direction?
I have rather large database which I use for performance testing. Typically, I add experimental indexes, change stored procedures, functions and so on. What I want is to track my changes: which indexes were added, which stored procedures were changed and so on.
How can I do it? I want to be able to restore the original database state to compare performance with and without changes.


texts:
I have a problem to make a postgres query with join from data of json.

I have 2 tables

The first one is media:

id as integer
path as string

and an other table playlist

id as integer
media as string (with my json inside)

For example

Media:

1   /img/a.jpg
2   /img/b.jpg
3   /img/c.jpg
4   /img/d.jpg
5   /img/e.jpg

Playlist:

1   ["1", "2", "5"]
2   ["4", "2", "3", "1"]

I want to make an SELECT on playlist and get back all path ideally as json or if not possible something like (example for playlist 1):

1   /img/a.jpg
2   /img/b.jpg
3   /img/e.jpg
...


texts: mirroring on sql server 2012 on different windows server 2012

texts:
I have 2 servers *.203 and  *.205

I have sql server 2012 on each of them

I tried to make mirroring between them
every thing goes successfuly but
when I tried to start mirroring
it gives me exception as image below


texts: Try_Convert Datetime showing '1/1/1900', want to show space

texts:
How do I get try_convert datetime to display NULL when the input is space?

I am getting two different results here. My intention is to show 'space' number query as Null also, just like first query.

select try_convert(datetime,'abcd')   ---> Null

select try_convert(datetime,'')    ---> '1/1/1900'


texts: SQL Server Standard Edition - Limitation

texts:
The below SQLServer License Edition comparison URL does NOT mention any limitation about the number of databases supported within an AlwaysOn Failover Cluster Instance - for standard edition. Is it safe to conclude that there is no enforced limits on number of databases supported within a single AlwaysOn FCI for Standard Edition License?
I get that for standard Edition there is limitation of 2 nodes within a single SQLServer FCI. I also understand for Standard Edition's Basic Availability groups there is limitation of ONE Database per Availability group.
https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2017?view=sql-server-ver15
Just to give a little background info, Our application & database design is such that, we have one database per client. we put multiple small clients' DBs (say 10 of them) into a single SQL Server instance.
Our present goal is just HA and there is no need for DR scenario and there is no need for ReadOnly secondary copy of database which AGs provide.
I certainly do not want to manage, say 10 different Basic availability groups (for 10 databases) within the same SQL Server instance for achieving HA goal. Hence avoiding the popular AGs and exploring SQL Server FailOver Cluster instance.

This is second question...
Additionally, what if we bring Software Assurance into the mix. The SQLServer 2019 Licensing guide
says, with Software Assurance we get HA, DR (and an Azure) copy for free but there is no mentioning of Ent or Std edition license in this context.
Does the Standard edition's - 2 nodes cluster limitation still applies for AlwaysOn Failover Cluster Instance, even with Software Assurance? Can we have a 3 node SQL FCI cluster with Std edition license if we also buy Software Assurance? If so, that would be icing on the cake (Primary, HA & DR nodes within same SQL FCI !)
Any help here is greatly appreciated. Thanks!


texts: Get rows inserted and updated dynamically in SSIS

texts:
I need to get the rows inserted and updated in each table through my SSIS package.
Is there any way to do it except using the row count transformation component  before each insert or update component.

Is there any option in SSIS that provide that information without doing it manually?


texts: Database server down: file /dev/vda1 used up all diskspace

texts:
My database server went down last night because there was no diskspace available. I checked what used up diskspace and it was 28GB by /dev/vda1

I have no clue what this is. The database all together will be a maximum of 1 GB diskspace, what is using up all this space?

When I start mysql, I get:

  error: 'Can't connect to local MySQL server through socket
  '/var/run/mysqld/mysqld.sock' (2)'

This is probably also because of the diskspace issue.

Hopefully someone can help me shortly. Many thanks in advance!


texts: Select Distinct on subset of columns, name different set of columns to return

texts:
I am using PostgreSQL and have two tables:

Table_A
colA | colB | colC | colD | colE | colF | colG | colH | colI | colJ

Table_B
colA | colB | colC | columnD | colE | columnF | colG | colH | colI | colJ

I am trying to insert a number of rows from table_B into table_A. My problem is that Table_A has a primary key based on colA, colB, colC, colD, and colE. Table_B does not have this restriction, which means a simple insert won't work:

INSERT INTO Table_A (colA, colB, colC, colD, colE, colF, colG, colH, colI, colJ)
SELECT colA, colB, colC, columnD, colE, columnF, colG, colH, colI, colJ FROM Table_B;

I am trying to work around this by using DISTINCT in my selection from Table_B. However, I cannot determine the correct syntax to both select distinct on the 5 primary key columns used in Table_A, and select all ten columns to be inserted. I have tried

INSERT INTO Table_A (colA, colB, colC, colD, colE)
SELECT DISTINCT colA, colB, colC, columnD, colE FROM Table_B;

Which correctly pulls unique entries but does not populate columns F-J, and I have tried

INSERT INTO Table_A (colA, colB, colC, colD, colE, colF, colG, colH, colI, colJ)
SELECT DISTINCT(colA, colB, colC, columnD, colE) colA, colB, colC, columnD, colE, columnF, colG, colH, colI, colJ FROM Table_B;

But this fails, as the first column entry is a wrapped version of the 5 unique columns, and fails the insert due to column length restrictions - the SELECT DISTINCT in parens returns '' which obviously doesn't fit:

  ERROR: Value is too long for type character varying(12)

My goal would be form a query which gets all the unique combinations of colA, colB, colC, columnD, colE from Table_B, and inserts those full rows, including columnF, colG, colH, colI, colJ into Table_A.


texts: PostgreSQL doesn't use all the RAM it could

texts:
I'm having performance issues with my PostgreSQL 9.4.1 server. I have tuned the server using the usual best practices (pgtune + google). Here's the relevant config:

# <snip> the default config above

default_statistics_target = 50
maintenance_work_mem = 960MB
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 11GB
work_mem = 96MB
wal_buffers = 8MB
checkpoint_segments = 16
shared_buffers = 4GB
max_connections = 200

autovacuum = on
log_autovacuum_min_duration = 10000
autovacuum_max_workers = 5
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 25
autovacuum_vacuum_scale_factor = 0.2
autovacuum_analyze_scale_factor = 0.1
#autovacuum_freeze_max_age = 200000000
autovacuum_vacuum_cost_delay = 20ms
autovacuum_vacuum_cost_limit = -1

#log_statement='mod'
#log_statement='all'

logging_collector = on

Yet, query performance is very bad at times of high load. At those same times, the total RAM usage on the server is 4GB (out of 16GB available).

I can't help to think that these two issues are related. And that performance would increase if pg would only utilize more of the available RAM.

The server is Intel(R) Xeon(R) CPU E3-1240 v3 @ 3.40GHz, 16GB RAM. We have 2 disks (WDC WD1003FBYZ-0) but no RAID. The DB with performance issues is split between these disks using tablespaces. OS is Debian 7.5.

So, anything obvious I have missed? Am I barking up the wrong tree? Or is there truly something fishy here?


texts: Save multiple dynamic filter for aggregation in Postgresql

texts:
What is the most efficient way to store multiple dynamic filters which can be created by users on given data and us the filter for aggregating data?

Lets assume I have a table called t_filter which will store some data filter/queries for given data t_data.

The user can build each filter completely dynamic and can save it to create some kind of dashboard (list of the saved filter by filter_name and some aggregation function on the data wich is filtered by the conditions). Each filter can have different conditions and operators (>=, <=, !=, ==, IS NOT NULL, IS NULL AND, OR, BETWEEN etc) on the different fields of given entries in t_data. The user can modify its filter: create/update/delete and the entries in t_datacan grow through imports or shrink through deletes, so everything is kind of dynamic.

I solved this problem by creating dynamic SQL based on the created filter from the user, UNION ALL every of the existing dynamic SQL entries in t_filter and creating a Materialized View out of the result (just the foreign key information: which filter_id belongs to which data_id). This solution works but is not that handy because i need to check for filter changes and regenerate the Materialized View as well as for new or deleted entries in t_data. For every additional filter entry the time to generate the View is taking longer because of the extra SQL which needs to be executed.

Is there a better way to solve that problem?


texts: Database schema for a variable-level hierarchy with variable types?

texts:
A client is asking for a database structure for a medical translation app that involves narrowing down a health issue and then asking specific questions on it.  The tricky part of representing this in a database is that there may be anywhere from 2-5 hierarchical "filters" to narrow down the health issue, then a hierarchy of questions to ask the patient.  The same filters and questions can be re-used in any level of the hierarchy.  The questions must also be translated into several languages if dealing with a non-English speaking patient.  For example:

-> Symptoms

 -->Cardiovascular

    -> Chest Pain

      "Have you experienced any chest pain recently?"
        "On a scale of one to ten, how severe is the pain?"
        "Is the onset of the pain sudden or gradual?"
        "Is the pain related to exertion?"

      "Do you ever suffer from shortness of breath?"

    -> Palpitations

      "Are you ever aware of your heartbeat? What is it like?"
        "Can you tap out the rhythm with your finger?"

-> Discharge

  --> Chest Pain

    "We recommend the following lifestyle changes to improve your condition."
      "Avoid taking too much aspirin for pain."

I can use a string hack to make this work, but am looking for something that can scale better and is searchable.  Also it will be maintained by a non-technical person in the future, so preference to something simple to comprehend vs. better performance like nested sets.  (Sorry for the formatting, everything I tried kept auto-block quoting stuff.)  Thank you to anybody who can assist!


texts:
I'm writing query to select students with their Program, Country and Teacher. So far, I have the following query:

SELECT H.STUDENT_CODE,
       H.FIRSTNAME,
       H.LASTNAME,
       H.PROGRAM,
       H.TEACHER_CODE,
       H.COUNTRY_CODE,
       AC.COUNTRY_NAME,
       P.PROGRAM_NAME,
       T.TEACHER_NAME
    FROM HTUD H,
         PROGRAM P,
         TEACHER T,
         AP_COUNTRY AC
    WHERE H.FIRSTNAME LIKE '%ark%'
        AND H.PROGRAM = P.PROGRAM
        AND H.TEACHER_CODE = T.TEACHER_CODE
        AND H.COUNTRY_CODE = AC.COUNTRY_CODE
    ORDER BY H.STUDENT_CODE ASC

These two columns H.TEACHER_CODE and H.COUNTRY_CODE have some empty values. If I run the query above, it returns only students that have data in these two columns H.TEACHER_CODE and H.COUNTRY_CODE.

However, I want to select all students wether they have data in these two columns or not. If they have data in H.TEACHER_CODE and H.COUNTRY_CODE column, then return Teacher and Country also, if they don't have, just return students without Teacher or Country.

So, how do I select all students with or without Teacher or Country?


texts: Postgresql Disk Allocation in Begin End Transaction

texts:
I have a loop in transaction block that creates a temporary table,updates,exports to txt file and drops table at the end of the block.
After some time I noticed that disk size was getting smaller each time loop continued.When I cancelled transaction block,disk size returned to its initial state.

How can I stop loop for allocating the disk on the following transaction ?

do
$$declare
  --variables
begin
     FOR r IN (SELECT ...) LOOP
        CREATE TEMPORARY TABLE TEST .. ;
        --UPDATE STATEMENTS
        --EXPORT (COPIES TABLE INTO TXT FILE)
        DROP TABLE TEST CASCADE;
     END LOOP;
end$$;


texts: Designing a scenario where a primary key could be of two types

texts:
I have a table: all_info, where it holds the users identification. The id of this table can be of two types (just one or another, never both), suppose: int and varchar, but one table can't have two primary keys, and a composite one wouldn't solve my problem.

So, I can't do this:

+--------------------+
| all_info           |
+--------------------+
| PK id1 varchar(50) |
| PK id2 int         |
| ...                |
+--------------------+

Then, I created two others tables: unique_info1 and unique_info2 with the primary keys of the types that I needed, add some informations for the specific types of users, and made relations with the table: all_info, that holds the rest of the users informations (that both types share).

With this scheme, I could relate unique_info1 and unique_info2 with every other table, but I would need to create two columns in every one of them to establish that relationship. To solve this, I had created an artificial primary key in all_info to make all posterior relationships.

Now, it looks like this:

Obs: FK uniq1_id varchar(50) and FK uniq2_id int(10) are unique and nullable.

+--------------------+    +--------------------+    +-------------------------+    +-------------------------+
| unique_info1       |    | unique_info2       |    | all_info                |    | other_table             |
+--------------------+    +--------------------+    +-------------------------+    +-------------------------+
| PK id varchar(50)  |    | PK id int(10)      |    | PK id int(10)           |    | ...                     |
| ...                |    | ...                |    | ...                     |    | ...                     |
| ...                |    | ...                |    | FK uniq1_id varchar(50) |    | ...                     |
| ...                |    | ...                |    | FK uniq2_id int(10)     |    | FK all_id int(10)       |
+--------------------+    +--------------------+    +-------------------------+    +-------------------------+

The thing is: that's the best approach, or should I change the plan?

For examples:

Choose other information to be the id of the users, where all will have the same type, and add all specific info of the users to that hole table?

This would result in a lot of null columns for each user.

Create two completely different tables for the two types of users?

This would result in redundant info.


texts: 50% CPU usage on MySQL RDS instance (db.m3.medium) with 0 connections

texts:
We have a MySQL RDS instance which is still utilizing ~50% CPU after stopping our applications and terminating all connections (e.g. from MySQL workbench).

The mysql-error-running.log is showing:

2017-08-03T11:24:05.711179Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4813ms. The settings might not be optimal. (flushed=5623 and evicted=0, during the time.)
2017-08-03T11:26:11.415416Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 11095ms. The settings might not be optimal. (flushed=11538 and evicted=0, during the time.)
2017-08-03T11:33:07.901110Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 15667ms. The settings might not be optimal. (flushed=15298 and evicted=0, during the time.)
2017-08-03T11:33:29.458855Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5004ms. The settings might not be optimal. (flushed=4931 and evicted=0, during the time.)

Can anyone advise on what is causing the persistent CPU usage here? Our applications have been disconnected from the RDS for over 40 minutes and CPU is still averaging 50% usage.

show process list is empty (aside from the connections used when connecting via MySQL workbench)

UPDATE: the RDS was rebooted and CPU dropped back down to expected levels.


texts: What is the difference between "ALTER DATABASE mydb SET search_path" and "SET search_path"?

texts: PostgreSQL 9.6. I just found both of these commands in a flyway migration, right next to each other. I feel like they are probably redundant but how do these statements differ? Is there a reason to use both? Apologies if this is a duplicate; I couldn't find an answer.


texts: How to Implement Multiple Options for the Same ID?

texts:
I'm working on a personal project, and run across the following situation, and am not sure how to proceed.  I am attempting to handle some cards from a board game (Gloomhaven, if you're familiar with it!) and am working on the Monster Groups in the game.  Each monster group can have normal and elite versions of monsters, and occasionally a boss monster.  Right now, my MonsterGroups table looks like this:

MonsterGroups
int ID
Varchar(250) Name
int MaxSize

I also have a table that will (hopefully) represent their combat statistics:

MonsterCombatStats
int ID
int MaxHealth
int Movement
int damage
int range
varchar(max) StaticAbilities

Since there can be multiple profiles for a monster, and these profiles always show up together, how should i represent the relationship between a MonsterGroup and their three possible profiles?

The best I could come up with is putting an identifier in the association table, telling me 1/2/3 which profile it is:

MonsterGroup_CombatStats
int ID
int MonsterGroupID
int MonsterCombatStatsID
int ProfileType

I'm concerned about hooking this into my database framework in the code, since it doesn't generally handle this sort of addition well.  Any advice?


texts: How to set cache on windows 10 pro 64bit machine

texts:
When running Mongod I get the following WARNING: The file system cache of this machine is configured to be greater than 40% of the total memory. This can lead to increased memory pressure and poor performance. This is on a windows 10 pro 64 bit machine.  I have 16 gigs of ram.  I have a 225 gig ssd drive c: and 2 1 terabyte drives d: and e:

I've set the virtual memory as follows:

drive     initial size       max
c:         800               1000
d:         400                800
e:         400                800

it says space available is 134538 MB

The mogod reference says the following:

Check the Windows file system cache limit. Having a too high or unbound
upper limit can impact performance. Specifically, having a file system cache
that uses 40% or more of the total memory can lead to increased memory
pressure and poor performance. Use SetSystemFileCacheSize to limit the
amount of memory that the file system cache can use.

Could someone provide directions on how to do that?  The answer from @Md Haidar Ali Khan does not work


texts:
I've try use MySql function ST_Distance_Spheroid for calculating distance but got following error :

  ST_Distance_Spheroid does not exist.

Is something missing?


texts: Find out if two rows came from same transaction in Oracle

texts:
I am using Oracle 11.1 and would like to figure out if two rows in one table where inserted by the same transaction.

Is there an easy sql statement to map a row to an transactions id? The table is an insert only table.

What are my options? Some baisc solution would do, I don't really want some extra tool to analyze the log files.

Thanks


texts:
I run this code as root:

/usr/local/bin/postgres -D /usr/local/pgsql/data

because it wanted me to run it.

However it gives this error;

"root" execution of the PostgreSQL server is not permitted.
The server must be started under an unprivileged user ID to prevent
possible system security compromise. See the documentation for
more information on how to properly start the server.

How can I login into PostgreSQL? Isn't it possible to use it with root user?

Some notes: This is PostgreSQL 9.4 (beta, but 9.3 gives me same error) on FreeBSD 10. PostgreSQL was installed via Ports. I think I have a "pgsql" user because it said

===> Registering installation for postgresql94-server-9.4.b3
===> Creating users and/or groups.
Using existing group 'pqsql'.
Using existing user 'pqsql'.


texts:
Pardon for being naive. This is a simple simple question but i am unable to figure out how to query this

Query :

I have 2 tables

kknagar_data & kknagar_rates

I am trying to query out in such a way that

get current record from kknagar_data  - i was able to achieve this
get kk_egg,kk_cash of previous record from current in kknagar_data
get rates from kknagar_rates by joining kk_rate_id in kk_nagar_rates & kk_nagar_data -  I was able to achieve this

What i am trying to achieve

I want all the above 3 points in a single query.

I was able to do point 1 & 3 and i queried like this

SELECT kk_data_id,kk_date,kk_egg,kk_cash,kk_in,kk_out,kk_expenditure,..... from kknagar_data INNER JOIN kknagar_rates on kknagar_data.kk_rate_id = kknagar_rates.kk_rate_id"

but i am unable add point 2 to the above query so that i get all the data in a single shot

What i tried :

I tried with Unions but i get error because the columns doesnt match

What i am using now :

Currently i use 2 different queries to achieve the above

SELECT kk_data_id,kk_date,kk_egg,kk_cash,kk_in,kk_out,kk_expenditure,..... from kknagar_data INNER JOIN kknagar_rates on kknagar_data.kk_rate_id = kknagar_rates.kk_rate_id"

and

Select kk_egg,kk_cash FROM kknagar_data WHERE kk_data_id < 3 ORDER BY kk_data_id DESC LIMIT 1

It gives me the result but i am trying to change this to make it more efficient. Another reason is when i json out the values, i find it difficult to decode multiple objects.

Goal :

Get current record with 2 fields of previous record and join with kk_nagar_rates & kk_nagar_data using rate_id

Any guindance will be much helpful.

Thanks in Advance


texts: Multi-Subnet Always On with Availability Group for DR

texts:
I researched a lot of mutli-geographical DR - Disaster Recoververy solution architectures for MSSQL and most of the proposed solutions I came across recommends HA using 2 nodes Failover clustering in one site and DR node in another location through the use of Availability Group such as the image below shows:

We currently have three physical machines and I came up with the following architectural.  It employs multi-subnet clustering for fail over and Availability Group for data replication between two sites.

My logic of doing this is that I can achieve DR solution with two nodes while I still can use my DR node for failover.  My third node - NODE3 is a read only used for Reporting so it is not suitable for DR nor a fail-over node.

My question is, is it okay or is it a best practice to have two nodes in a fail over cluster with one is a DR nodes?


texts:
I'm having some issues creating a replica set of my MongoDB.

I have server A. Currently, it is setup as a local standalone replica.

rs0:PRIMARY> rs.status()
{
        "set" : "rs0",
        "date" : ISODate("2014-06-02T19:01:23Z"),
        "myState" : 1,
        "members" : [
                {
                        "_id" : 0,
                        "name" : "mongo-server-a:27017",
                        "health" : 1,
                        "state" : 1,
                        "stateStr" : "PRIMARY",
                        "uptime" : 14986,
                        "optime" : Timestamp(1401735683, 1),
                        "optimeDate" : ISODate("2014-06-02T19:01:23Z"),
                        "self" : true
                }
        ],
        "ok" : 1
}

On another server, server B, I would like to replicate my data.

To do this, I run the following:

> rs.status()
{
        "set" : "rs0",
        "date" : ISODate("2014-06-02T19:05:40Z"),
        "myState" : 1,
        "members" : [
                {
                        "_id" : 0,
                        "name" : "mongo-replica:27017",
                        "health" : 1,
                        "state" : 1,
                        "stateStr" : "PRIMARY",
                        "uptime" : 35,
                        "optime" : Timestamp(1401735918, 1),
                        "optimeDate" : ISODate("2014-06-02T19:05:18Z"),
                        "electionTime" : Timestamp(1401735918, 2),
                        "electionDate" : ISODate("2014-06-02T19:05:18Z"),
                        "self" : true
                }
        ],
        "ok" : 1
}
rs0:PRIMARY>

Then I add server A

rs0:PRIMARY> rs.add("10.0.3.40:27017")
{ "ok" : 1 }
rs0:PRIMARY>

Then the log file tells the following:

2014-06-02T19:06:50.201+0000 [conn1] replSet replSetReconfig config object parses ok, 2 members specified
2014-06-02T19:06:50.202+0000 [conn1] replSet replSetReconfig [2]
2014-06-02T19:06:50.202+0000 [conn1] replSet info saving a newer config version to local.system.replset: { _id: "rs0", version: 2, members: [ { _id: 0, host: "mongo-replica:27017" }, { _id: 1, host: "10.0.3.40:27017" } ] }
2014-06-02T19:06:50.202+0000 [conn1] replSet saveConfigLocally done
2014-06-02T19:06:50.202+0000 [conn1] replSet info : additive change to configuration
2014-06-02T19:06:50.202+0000 [conn1] replSet replSetReconfig new config saved locally
2014-06-02T19:06:50.202+0000 [rsHealthPoll] replSet member 10.0.3.40:27017 is up
2014-06-02T19:06:50.202+0000 [rsHealthPoll] replSet member 10.0.3.40:27017 is now in state PRIMARY
2014-06-02T19:06:50.202+0000 [rsMgr] replSet total number of votes is even - add arbiter or give one member an extra vote
2014-06-02T19:06:50.202+0000 [rsMgr] another PRIMARY detected but it should step down since it was elected earlier than me

My problem is I have no clue as to how I should continue.


texts: Updating records in MS Access linked table will activate triggers?

texts:
I have a simple yet undocumented (or I could not find the documentation to solve my uneasiness) question, I hope I have come to the right place:

I am using MS Access to act as a poor front end for SQL Server. In this Access file I have some linked tables that will undergo some editing at various points by some users, be it via forms or directly accessing the table in spreadsheet view (I advised against this but, well...).

My concern is: the server has some procedures set on this table that are coded to trigger on insert/update. Will they be triggered when doing these edits via Access front end?


texts: Error 41145 after joining database to availability group

texts:
I keep getting from my SQL monitoring service the following alert:

Error: 41145, Severity: 16, State: 1. Cannot join database to
availability group. The database has already joined the
availability group. This is an informational message. No user action
is required.

This error is costantly coming approximately every 2 hours but I can't understand why.
Also, in the SQL error log, I have thousands of this log entry that I've never noticed on any of my other SQL Server installations:

This is on a new SQL Server installation that will become my production environment, so I want to make sure that there's nothing wrong with it before we perform the switch of the servers.


texts: Issue with Ñ character in name (migrated data from MySQL to PostgreSQL)

texts:
I'm currently running a test migration of data from MySQL to PostgreSQL. I'm using psycopg2 to perform the task.

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

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


texts: Merging two backups form a postgresql database

texts:
I have a kind of big postgresql database and two backup files (each uncompressed dump file ~56 mb) representing 2 states of the data in time.

1st state - backup from a couple days ago
2nd state - backup current state of db, minus deleted records and plus the new data added in those two days

this is the same database, and the records have a lot of relations to be handled manually

so I want to join this two backups in order to get a third snapshot of my DB that has the same data as the first state plus the extra data found in state 2.

Anyone can suggest a tool to do this? or a way to do this?

Is a rails project with a psql db.

I already try to merge the two files using the file merge mac app, but because of the size of the files is a really slow process, and there are a lot of differences (a lot of rows added and deleted) to manually pick each conflict.


texts: How to change db owner after the associated AD account is disabled

texts:
The new Senior DBA requested that the previous Senior DBA's Active Directory account be disabled. He forgot to check, however, for databases where the previous DBA was the owner. We now have ~60 databases with an orphaned owner. Is there a way to fix this without temporarily re-enabling the disabled AD Account?

Note: I'm aware of best practices regarding not setting the owner to a user. My previous boss probably was too but neglected to make the change everywhere (60 out of thousands isn't as bad as it could have been).


texts: Attach a psql session to a running transaction

texts:
Is it possible to open a psql session inside a currently existing transaction?

I'd like to be able to do this for debugging purposes. The idea would be to break into a debugger in my program, attach psql to the transaction that the program is currently in, and interactively inspect the state of the database as seen by the program at that point.

I can't find any way to do anything like this so I suspect it might be impossible, but it would be nice to have a definitive answer.


texts: Optimal mySQL design for 500k+ products with up to 50 specs

texts:
I facing a problem where I have to design a MySQL database capable of containing +500k products in approx. 1.500 categories, that can hold up to 50 specs. (like size, weight, speed, color, etc).
My goal is to find/use a design that is optimal (fast) for selecting products base on the input of up to 50 specs. Later on, I may implement precached on often use search patterns (specs), when I have collected statistics on that.
As my raw data is in Python (dicts/lists) I was initially thinking of using JSON-objects, but as I see it, this would result in huge overhead and maybe bad search performance. (Actually, I have no experience in using JSON in MySQL)
BTW The easiest would have been using MongoDB, but this is a no-go. I'm also trying to find a way to design it as generic as possible without having product-specific tables and like.
Based on the information above... What design/schema would be the optimal/best, regarding fast search in specs. ?
Is EAV (Entity-Attribute-Value) the way to go or is the a better way ?
If anybody knows if this question is answered somewhere, please link to it :-)
Thanks


texts: MySQL Scheduled Event fails to create table despite being able to do so when I manually run the query

texts:
I have a scheduled event designed to drop a table if it exists, then recreate it using a query. I'm doing this because the parent tables are very large and so I'm normalizing some data into a dedicated table to make it easier and more performant to query. Why not use a View you might ask? Because the version of MySQL is 5.x and it doesn't allow me to create a view using variables, which are part of this query. The problem is that I can run this query and it creates the table, no problem and I don't get any errors when I setup the event initially. However, when I run it as a daily event, the drop table command works but the resulting query doesn't (re)create the table. Alas, the table is gone the following day. My solve is to rewrite the query to simply delete the data and INSERT new data (not recreate the table) but before I go through that effort I'm wondering if anyone has any ideas why this might be happening. Here's my SQL:
CREATE EVENT `create_jira_issue_status_change_gt`
ON SCHEDULE EVERY 1 DAY
DO
DROP TABLE IF EXISTS jira_issue_status_change_gt;
CREATE TABLE jira_issue_status_change_gt
  SELECT `key`,parent_id,last_viewed,assignee,reporter,issue_type,project,resolved,issue_description,summary,due_date,priority,remaining_estimate,current_status_id,creator,time_spent,work_ratio,created,resolution,environment,issue_type_name,issue_type_description,subtask,field_id,`value`,t1.time AS start_status, t2.time AS end_status
    FROM (
        SELECT *, @rownum1 := @rownum1 + 1 AS timeId
        FROM
         jira_issue_status_change  , (SELECT @rownum1 := 0) r
        ORDER BY
         jira_issue_status_change.id ASC, jira_issue_status_change.time ASC
    ) AS t1
    LEFT OUTER JOIN (
        SELECT id, time, @rownum2 := @rownum2 + 1 AS timeId
        FROM
         jira_issue_status_change  , (SELECT @rownum2 := 0) r
        ORDER BY
         jira_issue_status_change.id ASC, jira_issue_status_change.time ASC
    ) AS t2
      ON t1.timeId + 1 = t2.timeId AND t1.id = t2.id;


texts: How to use the below expression in my SQL Query

texts:
I have this expression for sorting. I need to use this expression in my SQL code for the sorting purpose. Can anyone help me with this??

I tried case when stmt but it fails.

Expression:

if ([All Accounts].[ea_end_of_activity] is missing or
[All Accounts].[ea_end_of_activity] is null)
then (
if   ([Japanese Accounts].[aco_ac_ea_id] is missing)
then ('1')
else ('0')
)
else ('2')
+
[All Accounts].[aco_acc_short_name]

CODE:

SELECT DISTINCT
    First.aco_ac_ea_id,
    Sec.aco_acc_short_name,
    Sec.aco_acc_marketing,
    Sec.ea_spark_number,
    Sec.ea_end_of_activity
FROM
    (
    SELECT aco_ac_ea_id
    FROM CD.dbo.MAC
    WHERE
    MAC.aco_locale = 'ja' AND
    MAC.aco_acc_marketing_status = 'VALIDATED'
    ) as First
RIGHT OUTER JOIN
    (
    SELECT
     aco_ac_ea_id,
     aco_acc_short_name,
     aco_acc_marketing,
     ea_spark_number,
    ea_end_of_activity
    FROM
    CD.dbo.MAC,
    CD.dbo.MEA
    WHERE
    MAC.aco_locale ='en-us' and
    MAC.aco_ac_ea_id = MEA.ea_id
    ) as Sec
ON First.aco_ac_ea_id=Sec.aco_ac_ea_id


texts: Are there RDBMS which can count rows fast?

texts:
If I use query this in MySQL:

SELECT * FROM table WHERE some_col=something ORDER BY some_col LIMIT 10000, 10

It will scan 10010 rows, because it is not able to find row number 10000 without it. Are there RDBMS that can perform such tasks faster, straight getting to 10000 row without scanning all 10000 (with WHERE clauses like in the example above)?


texts: Innodb issue is preventing MySQL from starting

texts:
For some reason mysqld refused to start after I rebooted my server. Now when I try to start it I will get the following error:

  [root@vps1 ~]# service mysqld restart  ERROR! MySQL server PID file
  could not be found! Starting MySQL. ERROR! The server quit without
  updating PID file (/var/lib/mysql/vps1.xxx.eu.pid).

When  I checked the mysql error log I found the following

140923 05:23:02 mysqld_safe mysqld from pid file /var/lib/mysql/vps1.xxx.eu.pid ended
140923 05:23:07 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2014-09-23 05:23:08 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2014-09-23 05:23:08 4024 [Note] Plugin 'FEDERATED' is disabled.
2014-09-23 05:23:08 4024 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014-09-23 05:23:08 4024 [Note] InnoDB: The InnoDB memory heap is disabled
2014-09-23 05:23:08 4024 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-09-23 05:23:08 4024 [Note] InnoDB: Memory barrier is not used
2014-09-23 05:23:08 4024 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-09-23 05:23:08 4024 [Note] InnoDB: Using Linux native AIO
2014-09-23 05:23:08 4024 [Note] InnoDB: Using CPU crc32 instructions
2014-09-23 05:23:08 4024 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-09-23 05:23:08 4024 [Note] InnoDB: Completed initialization of buffer pool
2014-09-23 05:23:08 4024 [Note] InnoDB: Highest supported file format is Barracuda.
2014-09-23 05:23:08 4024 [Note] InnoDB: The log sequence numbers 0 and 0 in ibdata files do not match the log sequence number 63528574 in the ib_logfiles!
2014-09-23 05:23:08 4024 [Note] InnoDB: Database was not shutdown normally!
2014-09-23 05:23:08 4024 [Note] InnoDB: Starting crash recovery.
2014-09-23 05:23:08 4024 [Note] InnoDB: Reading tablespace information from the .ibd files...
2014-09-23 05:23:08 4024 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace mysql/slave_worker_info uses space ID: 5 at filepath: ./mysql/slave_worker_info.ibd. Cannot open tablespace datumklok_web/wp3_postmeta which uses space ID: 5 at filepath: ./datumklok_web/wp3_postmeta.ibd
2014-09-23 05:23:08 7f667c224720  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
InnoDB: Error: could not open single-table tablespace file ./datumklok_web/wp3_postmeta.ibd
InnoDB: We do not continue the crash recovery, because the table may become
InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.
InnoDB: To fix the problem and start mysqld:
InnoDB: 1) If there is a permission problem in the file and mysqld cannot
InnoDB: open the file, you should modify the permissions.
InnoDB: 2) If the table is not needed, or you can restore it from a backup,
InnoDB: then you can remove the .ibd file, and InnoDB will do a normal
InnoDB: crash recovery and ignore that table.
InnoDB: 3) If the file system or the disk is broken, and you cannot remove
InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf
InnoDB: and force InnoDB to continue crash recovery here.
140923 05:23:08 mysqld_safe mysqld from pid file /var/lib/mysql/vps1.xxx.eu.pid ended

Now unfortunately because of this I have no acces to the mysql console.

I did create user backups through Directadmin daily BUT if I delete all mysql content I assume DirectAdmin itself will no longer be functional. (Which I found out when fixing Roundcube :P).

So I really have no clue on how to fix this. Anyone out there some experience with these issues?


texts: How can I monitor my server’s status before changing the “Cost threshold for parallelism” settings

texts:
Our production server is running SQL Server 2008 SP3.

I’ve read a post on “cost threshold for parallelism” - http://www.sqlskills.com/blogs/jonathan/tuning-cost-threshold-for-parallelism-from-the-plan-cache/
And I have a few questions.

We have a relatively high amount of CXPACKET wait events on our production server (in average of 85%).
The server has default values for “cost threshold for parallelism” and “Max Degree of Parallelism”

I started to explore the possibility of increasing the value of “cost threshold for parallelism”.
I suspect the indeed the value is too low, but I wanted to get as much information as possible before I change anything and not just change it to 25 or 50 because that’s what the some of the internet posts suggested.

I’ve read this nice post about how to determine the value:
http://sqlknowitall.com/determining-a-setting-for-cost-threshold-for-parallelism/

The median cost in my server was 63.

How can I monitor my server’s status “before” and “after” I change to settings, in order to verify the effect of the value on the system’s throughput?

Thanks in advance,
Roni.


texts:
I am trying to create a function wrapping change_tracking_min_valid_version(object_id), to simplify queries when using synonyms instead of table names. Something like this:

CREATE FUNCTION MY_CHANGE_TRACKING_MIN_VALID_VERSION(@synonym VARCHAR(100))
RETURNS BIGINT
BEGIN
    RETURN (SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(base_object_name))
    FROM sys.synonyms WHERE name = @synonym);
END

I have tried getting the base object name separately, to be able to drop the select part of the return statement, i.e.

set @retval = CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(@table_name))

MS SQL Server Management Studio produces this error:

Msg 443, Level 16, State 1, Procedure MY_CHANGE_TRACKING_MIN_VALID_VERSION, Line 5
Invalid use of a side-effecting operator 'change_tracking_min_valid_version' within a function.

I do understand that I can't use operations with side-effects in functions, but since I can't seem to find any information about (or understand why there would be any) side-effects of this specific function, my question is:

What are the side-effects of CHANGE_TRACKING_MIN_VALID_VERSION?

(I am using SQL Server 2012.)


texts: Migrating from SQL Server 2005 to SQL Server 2008 R2 on a different box

texts:
We currently have a SQL Server 2005 instance hosting a lot of application specific databases (ArchiveManager, SolarWinds, Kaspersky etc). We are planning to deploy a new SQL Server 2008 R2 instance on a new server and slowly migrate across to this instance. All of the applications have fairly straightforward migration plans for changing the database location normally revolving around stopping services, detaching, attaching and rerunning installers.

All looks good so far. My only concern is with SQL Server Agent jobs and logins. Initially I was under the impression that these were all stored in the system databases and that I could move these. I understand now that this is not the case.  Microsft KB 918992
suggest running a script to generate a stored procedure and then calling this and running the generated script on the new instance. There seem to be a lot of caveats to it though.  Another option would be to use SSIS to generate a "Transfer Logins" and "Transfer Jobs" package and run that (both editions are Standard) but I am unversed in SSIS so am a touch scared to use that.

I suppose my question boils down to this:

Is the Microsft KB the best way to migrate logins to the new SQL instance?
Is the only way to migrate tasks to the new instance via SSIS?

I am trying to get it as clear in my head as possible for my planning documentation so am very appreciative of any replies/guidance.


texts: Will a SQL Server 2008 R2 MDF file work on SQL Server 2016 with Windows Server 2016?

texts:
We release our database as a .MDF file taken from a SQL Server 2008 R2 server. I have a client who is planning to move to SQL Server 2016 on a Windows OS 2016 and is asking if our .MDF can be restored as a database on their SQL Server 2016 and will it work with Windows OS 2016.

I know that SQL Server 2016's lowest compatibility mode is 100 which is SQL Server 2008 but will it work on Windows OS 2016?

This article makes me feel that it wont https://www.sqlskills.com/blogs/glenn/operating-system-support-for-sql-server-versions/


texts: Mysqlimport very slow when importing two smaller files

texts:
I am trying to import a large file, it was really big and it did not seem to be working so I broke it into pieces. The first piece takes 2 minutes, and the second one takes almost an hour!!! How can that be???

bash-3.2$ wc -l allelePiecesaa
2575063 allelePiecesaa
bash-3.2$ cp allelePiecesaa allele.txt
bash-3.2$ time /usr/bin/mysqlimport -h bluemoon-mgmt1a -u jbond --delete --local --fields_escaped_by=\\ MBSR -L allele.txt
MBSR.allele: Records: 2575064  Deleted: 0  Skipped: 0  Warnings: 6
real    1m50.285s
user    0m0.006s
sys     0m0.103s
bash-3.2$ cp allelePiecesab allele.txt
bash-3.2$ wc -l allele.txt
2545972 allele.txt
bash-3.2$ time /usr/bin/mysqlimport -h bluemoon-mgmt1a -u jbond --local --fields_escaped_by=\\ MBSR -L allele.txt
MBSR.allele: Records: 2545973  Deleted: 0  Skipped: 0  Warnings: 7
real    57m17.279s
user    0m0.030s
sys     0m0.097s

my configuration is:

[client]
user=X
host=Y
password=Z
port=3306

[mysqld]
init_connect='SET autocommit=0'
init_connect='SET foreign_key_checks = 0'
init_connect='SET unique_checks = 0'
log_slow_queries = 0

after I do SHOW CREATE TABLE allele;
I get:

mysql> SHOW CREATE TABLE allele

CREATE TABLE `allele` (
    `allele_id` int(11) NOT NULL auto_increment,
    `variation_id` int(11) unsigned default NULL,
    `subsnp_id` int(11) unsigned default NULL,
    `allele_code_id` int(11) unsigned default NULL,
    `population_id` int(11) unsigned default NULL,
    `frequency` float unsigned default NULL,
    `count` int(11) unsigned default NULL,
    `frequency_submitter_handle` int(10) default NULL,
    PRIMARY KEY  (`allele_id`),
    KEY `variation_idx` (`variation_id`),
    KEY `subsnp_idx` (`subsnp_id`),
    KEY `population_idx` (`population_id`)
) ENGINE=MyISAM
AUTO_INCREMENT=1082088650 DEFAULT CHARSET=latin1;


texts: Compatibility level 100 on SQL Server 2008 R2

texts:
I'm upgrading my databases to compatibility level 100 on SQL Server 2008 R2.
My next step is to check if my stored procedures, functions, views, triggers are still working properly. Running each of these is not an option.

How should I check?


texts: How to define partition according to Date?

texts:
I am currently using MySQL 5.6.

In my database, I have a log table called paramlog_ems. It has 2M rows

I want like to partition this log table according to Date.

Here is the table structure:

CREATE TABLE paramlog_ems (
  SiteIndex smallint(5) unsigned NOT NULL,
  RegionIndex smallint(5) unsigned NOT NULL,
  OrganizationIndex smallint(5) unsigned NOT NULL,
  DeviceSlaveId smallint(5) unsigned NOT NULL,
  UserIndex smallint(5) unsigned NOT NULL,
  ParameterID smallint(5) unsigned NOT NULL,
  ParamValue double(20,10) NOT NULL,
  DeviceName varchar(50) NOT NULL,
  LogDate datetime NOT NULL,
  Category tinyint(3) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I want to do this using MySQL Workbench.


texts: A tuple relational calculus equivalent for the given SQL query

texts:
Well, here's a SQL query:

select Employee.Name,Department.Name
from Employee,Department
where Employee.Dept_no=Department.No

I have tried a tuple relational calculus on it as:

{t|Ǝ s ∈ Employee (s[Name]=t[Name] ^
Ǝ u ∈ Department(u[Name]=s[Name]^u[No]=s[No]))}

Is my approach correct? if not, then can anyone help me understand?


texts: Composite Primary Key on partitioned tables, and Foreign Keys

texts:
We have a database where several tables are partitioned. The primary key (PK for brevity) for these tables is of the form

transaction_date, id

Where transaction_date is the partitioning key, and id is a unique integer column. If we were not partitioning the table, then we would only use id as the PK.

We have several other tables which need foreign keys (FKs) to reference these partitioned tables. I cannot decide whether to use composite FKs, referencing both columns of the PK, or single column FKs, referencing the id column supported by a unique index.

Are there any advantages/disadvantages of either approach? So far the only factors I can think of (and I may even be wrong on these) are:

Single column FK: Reduces app development complexity.
Composite FK: Allows partition elimination when joining on FK columns.


texts: Join static file xml with ID field with Database table ID field

texts:
I am using php to display a table with different columns. The table is build from a static xml file with several hundreds of entries. Each entry has also an ID field.
I have to look up that ID field with some other table's ID field inside a Mysql Database..
What would be the most performant way to do this? Because as I see it, I have to iterate through each of the array entries id field and join that with the table id field... which would result in extremely much queries...
Is there another approach how to do it?
Maybe do one query for each of the complete array's entries, and then get back an array from the mysql table query with the corresponding joined id result (with the same matching indexes as the array's entries) in a list?


texts: How are limits in mysql on size of columns etc calculated

texts:
From the mysql docs,
1 - on joins,

The maximum number of tables that can be referenced in a single join is 61. This also applies to the number of tables that can be referenced in the definition of a view.

2 - row size

Every table (regardless of storage engine) has a maximum row size of 65,535 bytes

3 - on number of columns

There is a hard limit of 4096 columns per table
InnoDB permits up to 1000 columns.]

Is there an explanation of how these numbers have been calculated, or are these all constants assumed during system design?


texts: How to properly perform database maintenance on a live website

texts: I'm using WordPress to host a website, and for years, I've been using plugins to perform maintenance (delete revisions, expired transits, optimizing table, etc.) on the msyqls database, but I've been doing it while the site was live. So, I would like to know if it's OK to perform database maintenance while the site is live, or what's the standard procedure?


texts: Is it possible to give PDB name to a procedure and select from it?

texts:
The following example is not working, I just trying to show what I'm trying to do.
Given table:
-- this table is located at the CDB
create table t1 (tab_name varchar2(35))
/
-- this proc is located at the CDB
Create or replace procedure FindTables(vPdb in varchar2, vOwner in varchar2)
BEGIN
  insert into t1(tab_name) select TABLE_NAME from ALL_TABLES@vPdb where OWNER=vOwner;
END;
/

Is there a way to create a temporary DB link or something like that? or any other way?
EDIT
I need also a solution for user tables.


texts: SELECT only the rows with a unique value on certain columns

texts:
I have a table with multiple records by each employee, i need to find all the records by employee where any of the columns values changes. for example i have the following data for George:

HOME
CURRENT
EFFECTIVEDATE
NOTTOEXCEEDDATE
FLAG
GRADE
POSITION
LASTPAYPERIODPAID

GEORGE
610422
610422
NULL
NULL
N
15
SUPERVISOR  ACCOUNTANT

GEORGE
610422
630100
11/10/2019
11/6/2021
Y
15
SUPERVISOR  ACCOUNTANT

GEORGE
610422
610422
NULL
NULL
N
15
SUPERVISOR  ACCOUNTANT

GEORGE
610422
630100
11/10/2019
11/6/2021
Y
15
SUPERVISOR  ACCOUNTANT

GEORGE
610422
630100
11/10/2019
11/6/2021
Y
14
ACCOUNTANT

GEORGE
610124
630100
11/10/2019
11/6/2021
Y
14
ACCOUNTANT

GEORGE
610124
630100
11/10/2019
11/6/2021
Y
14
ACCOUNTANT

GEORGE
610422
630100
11/10/2019
11/6/2021
Y
14
ACCOUNTANT

GEORGE
610422
630100
11/10/2019
11/6/2021
Y
14
ACCOUNTANT

GEORGE
610124
630100
11/10/2019
11/6/2021
Y
14
ACCOUNTANT

GEORGE
610124
630100
11/10/2019
11/6/2021
Y
14
ACCOUNTANT

GEORGE
610422
630100
11/10/2019
11/6/2021
Y
15
SUPERVISOR  ACCOUNTANT

GEORGE
610422
610422
NULL
NULL
N
15
SUPERVISOR  ACCOUNTANT

GEORGE
610124
630100
11/10/2019
11/6/2021
Y
14
ACCOUNTANT

GEORGE
610422
630100
11/10/2019
11/6/2021
Y
14
ACCOUNTANT

GEORGE
610422
610422
NULL
NULL
N
15
SUPERVISOR  ACCOUNTANT

GEORGE
610422
630100
11/10/2019
11/6/2021
Y
14
ACCOUNTANT

GEORGE
610422
630100
11/10/2019
11/6/2021
Y
14
ACCOUNTANT

GEORGE
610422
630100
11/10/2019
11/6/2021
Y
15
SUPERVISOR  ACCOUNTANT

How do i get the following result data set ?

HOME
CURRENT
EFFECTIVEDATE
NOTTOEXCEEDDATE
FLAG
GRADE
POSITION
LASTPAYPERIODPAID

GEORGE
610124
630100
11/10/2019
11/6/2021
Y
14
ACCOUNTANT

GEORGE
610422
630100
11/10/2019
11/6/2021
Y
15
SUPERVISOR  ACCOUNTANT

GEORGE
610422
610422
NULL
NULL
N
15
SUPERVISOR  ACCOUNTANT

so far i can only get the result data set by doing a Select Distinct without the LAST_PAY_PERIOD_PAID column. as soon as i add this column i get the first table all over again.
any help is greatly appreciated


texts:
I'm trying to work out the schema for a system where users will be able to order items in an (unlimited length) list by drag and drop. Is there any kind of standard way of storing this ordering information, without updating every record with every change?

So far I've come up with two possibilities:

Using a float field, with default value for adding to the end of the list being higher than the last item in the list, and updating the any moved element to have a value halfway between the one above and the one below - the problem here is it'd be possible for ever-more precise values to cause problems as they reach the limits of the field. Advantages are it's easy to sort.
Having a before_id (with the topmost element set to null), set to the id of the preceding element. Sorting in this case would take an extra step or joins.

Is there a better way of doing this I'm missing?


texts:
Client has setup logshipping one of the database. Out of 3 steps in logshipping - backup and copy works without any issues, but restore fails intermittently giving 'out of sync' error.
When I pulled backup/restore history using query, I am getting some entry with 'NUL' for physical device name as shown below.

Is that is causing issue? How to resolve this issue?
Thanks
Naveen


texts: MySQL: which innodb parameters should I tune if disk I/O is slow

texts:
Which InnoDB parameters should I tune if disk I/O is slow?

Please give your suggestions.

I have another question regarding innodb_log_file :-
What happen if innodb_log_file become full ? so what is mysql next action on this situation ?


texts: Migrating reports from SSRS 2005 to SSRS 2014 and SSAS 2005 cube to SSAS 2014?

texts:
As SQL Server 2005 end of life is getting closer I'm facing inevitable job of migrating databases, SSIS packages, SSRS reports and SSAS cubes. Since newest version of SQL Server is SQL Server 2014 we are probably need to switch to it.
I have read few texts on  migrating from SQL Server 2008 to SQL Server 2014 but i have not found anything about migrating from SQL Server 2005.

Have somebody else done something similar and can it be done in first place?


texts: How do I insert a value into table2 from a trigger on table1

texts:
I have 2 tables with a column name WHO, and I'm using these 2 tables to create a view where FSwho.who = FStasks.who.

If I enter a new record into FStasks where FStasks.who = LIZ, but there's no record on my FSwho.who table, then the trigger should insert LIZ into my table.

With the code below, the error I get is

  The multi-part identifier "fs_tasks.WHO" could not be bound. (I've also tried inserted.who)

I've scoured the interwebs and most of the solutions I've come across are about changing values on the same table. How do I go about inserting values to a completely different table?

ALTER TRIGGER [dbo].[FS_TASKS_TRIG]
   ON  [dbo].[FS_TASKS]
   AFTER INSERT,UPDATE
AS
BEGIN
    SET NOCOUNT ON;

DECLARE @Action as char(1);
    SET @Action = (CASE
        WHEN EXISTS(SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED) THEN 'U'  -- Set Action to Updated.
        WHEN EXISTS(SELECT * FROM INSERTED) THEN 'I'  -- Set Action to Insert.
        WHEN EXISTS(SELECT * FROM DELETED) THEN 'D'  -- Set Action to Deleted.
        ELSE NULL -- Skip. It may have been a "failed delete".
    END)

    if @Action in ('I','U')
    begin

        IF NOT EXISTS(SELECT * FROM fs_who where who = WHO) BEGIN
            INSERT INTO FS_WHO (WHO)
            VALUES (fs_tasks.WHO)
        END

    end

END


texts: Creation of a simple field failed in MySql

texts:
CREATE TABLE bdt.transactions (
`transaction no`     int UNSIGNED NOT NULL  AUTO_INCREMENT,
data                 date    ,
by                   int  NOT NULL  ,
hours                int  NOT NULL  ,
for                  int  NOT NULL  ,
`transaction type`   set('normale','corso')   NOT NULL DEFAULT 'normale' ,
activity             text  NOT NULL  ,
CONSTRAINT pk_transactions PRIMARY KEY ( `transaction no` ) ) engine=InnoDB

Error:

  You have an error in your SQL syntax; check the manual that
  corresponds to your MySQL server version for the right syntax to use
  near  'by                   int  NOT NULL  ,  hours                int
  NOT NULL  ,  for                  int  NOT NULL    at line 4

With backquotes is now OK


texts: Is there a faster way to get UNION ALL behavior in Postgres?

texts:
I have a table (t0), in my Postgres DB, with data that looks something like this:

t1_id  t2_id
1      1
2      1
2      1
4      null
4      null
5      null

And I have a query to return my desired results of:

t1_id  t2_id
1      1
4      null
5      null

My query looks something like this:

(
  SELECT DISTINCT ON (t2_id) t1_id, t2_id
  FROM t0
  WHERE t2_id IS NOT NULL
)
UNION ALL
(
  SELECT DISTINCT ON (t1_id) t1_id, t2_id
  FROM t0
  WHERE t2_id IS NULL
)

Is there a faster way to do an operation like this? It's not too bad, but I'm doing it in several places (with joins) and all these repeated queries seems to slow stuff down a bit. Seems like there must be a better way.

Here's the query in fiddle form: http://sqlfiddle.com/#!15/d41d8/3603


texts: Is there a good way to find out which user and process owns a given user created temp table in tempdb?


texts: How to correct SQL-Server installation with wrong language?

texts:
What is the best way to re-install a SQL Server that was installed using a wrong language without losing any configuration?

Should I install a second instance parallel to the default instance - here I do not know if this is anyhow possible at all if using a different language?
Or should I install a second installation of Sql Server (as if it were two different Versions)?
Alternatively I could of course uninstall the old and install a new Sql Server using the correct language.

Independent of the way to go, as you can imagine, I definitely want to avoid doing all the maintenance work for creation of Logins, Users, Permissions, Alerts, Operators, Jobs etc. again. Is there a good approach to achieve this?


texts:
i'm working on a 5.7 Mysql server with MyISAM tables.

All my tables ares used by internal software for my customers. (and the tables, defined indexes, and storage type are imposed by the software)

I have create somes of request for generate reports. My reports work with multiples joined tables and lot of conditions. A request can make 5 minutes for display a result.

My problem is not the time ( because it's really due to the complexity of request). But during the request, all connexions are freezed and the users can't works.

  Have you some idea for change this situation ( i have already
  optimised my requests, and y can't work more on this side) ?

i search a solution about concurrent access, or other solutions.

note : my request are "read only".

thanks for help, and sorry for my lack of english.


texts: Manual Fulltext index population time in SQL Server 2012

texts:
I would like to know if there is a way to see how much time did it take SQL Server 2012 to index all the data in a table?

In my example I create the table, create the index and set the population to be manual. Then I execute

ALTER FULLTEXT INDEX ON table_name START UPDATE POPULATION;

The query executes immediately but as I know, the population process is actually performed in the background. How can I find out what's the total time it took to index the whole table?


texts: Is there a way to find all Logins, and all database users this login have?

texts:
I'm trying to join, every login and permissions tables. Looking over the internet but I just can't find something to list like:

LOGIN  |  USER  | DATABASE_THE_USER_HAS_ACCESS

This is because I would like to find all users with not a single permission on any database ( Login with no users ).

sys.server_principals AS log
LEFT OUTER JOIN sys.syslanguages AS l ON l.name = log.default_language_name
LEFT OUTER JOIN sys.server_permissions AS sp ON sp.grantee_principal_id = log.principal_id and sp.type = N'COSQ'
LEFT OUTER JOIN sys.credentials AS c ON c.credential_id = log.credential_id
LEFT OUTER JOIN master.sys.certificates AS cert ON cert.sid = log.sid
LEFT OUTER JOIN master.sys.asymmetric_keys AS ak ON ak.sid = log.sid
LEFT OUTER JOIN sys.sql_logins AS sqllog ON sqllog.principal_id = log.principal_id
WHERE

I'm looking at these tables but I can't find a pattern to join them,.